Actual vs. budget (Query select)

//변수 선언 S
//예산 조회 클레스
BudgetCalculateBalance  budgetCalculateBalance = new BudgetCalculateBalance();
//Dimension 필터 클레스
DimensionProvider       dimensionProvider = new DimensionProvider();
//쿼리 클레스
Query                   budgetTransactionQuery;
QueryFilter             filter;
QueryBuildDataSource    qbdsHeader;
QueryBuildDataSource    qbdsLine;
//쿼리 Run 클레스
SysQueryRun             sysQueryRun;
//예산 Tmp 테이블
//.스텐다드 변수
BudgetTmpBalance        budgetTmpBalanceLocal;
BudgetTmpBalance        _budgetTmpBalance;
//.값을 보기위한 Add 변수
budgetTmpBalance        budgetTmpBalance;

//회계달력 변수 선언 및 회계달력 선택 값으로 회계달력 테이블 찾아오기
FiscalCalendar fiscalCalendarLocal = FiscalCalendar::findByCalendarId("FiscalCalendar name");
//변수 선언 E

//사용 쿼리 데이터소스 클리어 및 값 전달 받기 S
//쿼리 데이터소스 범위 클리어
budgetTransactionQuery = new Query(queryStr(BudgetCalculateBalance));
budgetTransactionQuery.dataSourceNo(1).clearRanges();
budgetTransactionQuery.dataSourceNo(2).clearRanges();
//쿼리 데이터소스 클레스로 값 받아오기
qbdsHeader = budgetTransactionQuery.dataSourceTable(tableNum(BudgetTransactionHeader));
qbdsLine = budgetTransactionQuery.dataSourceTable(tableNum(BudgetTransactionLine));
//사용 쿼리 데이터소스 클리어 및 값 전달 받기 E

//예산조회 스텐다드 조건 필드 S
// Add a query filter for the TransactionStatus field.
filter = budgetTransactionQuery.addQueryFilter(qbdsHeader, fieldStr(BudgetTransactionHeader, TransactionStatus));
filter.value(int2str(enum2int(BudgetTransactionStatus::Completed)));

// Add a query filter for the Ledger field.
filter = budgetTransactionQuery.addQueryFilter(qbdsHeader, fieldStr(BudgetTransactionHeader, PrimaryLedger));

// Hide the query filter for the Ledger field.
// The Ledger field is not exposed to end users because the value is always set to the RecId of the current Ledger and cannot be changed.
filter.status(RangeStatus::Hidden);

// Add a query filter for the BudgetTransactionType field.
filter = budgetTransactionQuery.addQueryFilter(qbdsHeader, fieldStr(BudgetTransactionHeader, BudgetTransactionType));

// Hide the query filter for the BudgetTransactionType field.
// The BudgetTransactionType field is not exposed to end users because the value is always set to all
// types except Encumbrance and PreEncumbrance.
filter.status(RangeStatus::Hidden);

// Add a query filter for the BudgetModelId field.
filter = budgetTransactionQuery.addQueryFilter(qbdsHeader, fieldStr(BudgetTransactionHeader, BudgetModelId));

// Hide the query filter for the BudgetModelId field.
// The BudgetModelId field is not exposed to end users because the value is set by the user on the form.
filter.status(RangeStatus::Hidden);

// Add a query filter for the header Date field.
filter = budgetTransactionQuery.addQueryFilter(qbdsHeader, fieldStr(BudgetTransactionHeader, Date));

// Hide the query filter for the Date field.
// The Date field is not exposed to end users because the value is set by the user on the form.
filter.status(RangeStatus::Hidden);

// Add a query filter for the line Date field.
filter = budgetTransactionQuery.addQueryFilter(qbdsLine, fieldStr(BudgetTransactionLine, Date));

// Hide the query filter for the Date field.
// The Date field is not exposed to end users because the value is set by the user on the form.
filter.status(RangeStatus::Hidden);

// Don't filter budget amounts by budget type.
budgetCalculateBalance.parmFilterByBudgetType(NoYes::No);

// Always calculate ledger account actual balances
budgetCalculateBalance.parmCalculateLedgerAmounts(NoYes::Yes);
//예산조회 스텐다드 조건 필드 E

//예산조회 추가 조건 필드 및 Actual vs. budget inquiry 화면 조회 값 S
//Dimension.Value(MainAccount) 조회
dimensionProvider.addAttributeRangeToQuery(
budgetTransactionQuery,
qbdsLine.name(),
fieldStr(BudgetTransactionLine, LedgerDimension),
DimensionComponent::DimensionAttribute,
"MainAccount value",
SSBP_MyFinanceFunction::findTxt_MainAccount(),
true);

//Dimension.Value(CostCenter) 조회
dimensionProvider.addAttributeRangeToQuery(
budgetTransactionQuery,
qbdsLine.name(),
fieldStr(BudgetTransactionLine, LedgerDimension),
DimensionComponent::DimensionAttribute,
"CostCenter value",
SSBP_MyFinanceFunction::findTxt_CostCenter(),
true);

//쿼리 실행
sysQueryRun = new SysQueryRun(budgetTransactionQuery);
budgetTransactionQuery = sysQueryRun.query();

//기간(년도) 조회
// Set the fiscal calendar and fiscal year parameters
budgetCalculateBalance.parmFiscalCalendarRecId(fiscalCalendarLocal.RecId);
budgetCalculateBalance.parmStartDate(FiscalCalendarYear::findByCalendarYearName(fiscalCalendarLocal.RecId, "2013").StartDate);
budgetCalculateBalance.parmEndDate(FiscalCalendarYear::findByCalendarYearName(fiscalCalendarLocal.RecId, "2013").EndDate);

//예산 모델 조회
// Set the budget model parameters
budgetCalculateBalance.parmBudgetModelId("Budget model id");

// The control is disabled; set the parm to No.
budgetCalculateBalance.parmIncludeSubModels(NoYes::No);
//예산조회 추가 조건 필드 및 Actual vs. budget inquiry 화면 조회 값 E

//조회값 받아오기 및 데이터 가공(sum) S
budgetCalculateBalance.parmQuery(budgetTransactionQuery);
_budgetTmpBalance.setTmpData(budgetCalculateBalance.calculatePeriodBalances());

// <PubSect>
while select sum(OriginalAmount), sum(RevisedAmount), sum(LedgerAmount), sum(Variance)
, sum(PreliminaryAmount), sum(ApportionmentAmount)
  from _budgetTmpBalance
group by BudgetModelId, DimensionFocus, Period
order by Period
{
if (isConfigurationkeyEnabled(configurationKeyNum(PublicSector)) && !BudgetControlConfiguration::findActiveByPrimaryLedger(Ledger::current()).SumPreliminaryBudget)
{
  _budgetTmpBalance.RevisedAmount = _budgetTmpBalance.RevisedAmount - _budgetTmpBalance.PreliminaryAmount;
  _budgetTmpBalance.Variance = _budgetTmpBalance.Variance - _budgetTmpBalance.PreliminaryAmount;
}

buf2Buf(_budgetTmpBalance, budgetTmpBalanceLocal);
budgetTmpBalanceLocal.insert();
}
// </PubSect>
//조회값 받아오기 및 데이터 가공(sum) E

//가공 데이터 받아와서 print 변수로 화면에 표시 S
budgetTmpBalance.setTmpData(budgetTmpBalanceLocal);

while
select * from budgetTmpBalance
{
   print strFmt("%1 , %2 , %3 , %4 , %5",
budgetTmpBalance.Period,
budgetTmpBalance.OriginalAmount,
budgetTmpBalance.RevisedAmount,
budgetTmpBalance.LedgerAmount,
budgetTmpBalance.Variance);
}
pause;
//가공 데이터 받아와서 print 변수로 화면에 표시 E