begin
AccountCode = Null;
AccountName = Null;
AccountType = Null;
DebitAmount = 0;
CreditAmount = 0;
DivisionCode = Null;
DivisionName = Null;
AccDate$Date = 0;
--podminky pro obraty
select mRet from DSQL$GetAccountsCondition('', :InAccDateFrom, :InAccDateTo,
:InAllowRequest, :InAudited, :InDivisionSelID, :InDivisionsWithChilds,
:InBusOrderSelID, :InBusOrdersWithChilds, :InBusTransactionSelID,
:InBusTransactionsWithChilds, :InBusProjectSelID, :InBusProjectsWithChilds,
:InFirmSelID, :InAmountFrom, :InAmountTo,
:InBusOrdersWithNull, :InBusTransactionsWithNull, :InBusProjectsWithNull)
into :mSQLTurnsWhere;
--skupinovani obratu
if (InGroupOption = 0) then
mSQLTurnsGroup = 'Account_ID';
else if (InGroupOption = 1) then
mSQLTurnsGroup = 'Account_ID, AccDate$Date';
else if (InGroupOption = 2) then
mSQLTurnsGroup = 'Account_ID, Division_ID';
else if (InGroupOption = 3) then
mSQLTurnsGroup = 'Account_ID, Division_ID, AccDate$Date';
else
Exception ErrParameterNotInRange;
--sablona sql obratu
mSQLTurns =
'select ' || mSQLTurnsGroup || ', ' ||
' SUM(GLV.DebitAmount) as DebitTurnOverSum, ' ||
' SUM(GLV.CreditAmount) as CreditTurnOverSum ' ||
' from ' ||
' GeneralLedgerSingle_VIEW GLV ';
--tahle podminka je v tomto miste kvuli rychlosti, jinak tu byt nemusi
if (InAccountSelID is not Null and InAccountSelID <> '') then
mSQLTurns = mSQLTurns ||
' join Seldat SLD on SLD.Obj_ID = GLV.Account_ID and SLD.Sel_ID = ''' || InAccountSelID || ''' ';
if (mSQLTurnsWhere <> ' ') then
mSQLTurns = mSQLTurns || ' where ' || mSQLTurnsWhere || ' ';
mSQLTurns = mSQLTurns ||
'group by ' || mSQLTurnsGroup;
--podminky pro hlavni SQL
mSQLWhere = 'where';
if (InAccountSelID is not Null and InAccountSelID <> '') then
mSQLWhere = mSQLWhere || ' and exists(select 1 from SELDAT where OBJ_ID = A.ID and SEL_ID = ''' || InAccountSelID || ''')';
if (InAccountCode is not Null and InAccountCode <> '') then
mSQLWhere = mSQLWhere || ' and A.Code LIKE ''' || InAccountCode || ''' ESCAPE ''~''';
if (InAllowRequest = 'N') then
mSQLWhere = mSQLWhere || ' and A.IsIncomplete = ''N''';
if (InOnlyNonZeroAccounts = 'A') then
mSQLWhere = mSQLWhere || ' and (TRO.DebitTurnOverSum <> 0.0 or TRO.CreditTurnOverSum <> 0.0)';
if (mSQLWhere = 'where') then
mSQLWhere = ' ';
else
mSQLWhere = ib_string_replace(mSQLWhere, 'where and', 'where ') || ' ';
--hlavni SQL
mSQL =
'select ' ||
' A.Code, ' ||
' A.Name, ' ||
' A.AccountType, ' ||
' TRO.DebitTurnOverSum, ' ||
' TRO.CreditTurnOverSum ';
if (InGroupOption in (1,3)) then
mSQL = mSQL || ', TRO.AccDate$Date ';
else
mSQL = mSQL || ', cast(null as float) as AccDate$Date ';
if (InGroupOption in (2,3)) then
mSQL = mSQL || ', D.Code, D.Name ';
else
mSQL = mSQL || ', cast(null as varchar(5)) as Code, cast(null as varchar(40)) as Name ';
mSQL = mSQL ||
'from ' ||
' Accounts A ' ||
' left join (' || mSQLTurns || ') TRO on TRO.Account_ID = A.ID ';
if (InGroupOption in (2,3)) then
mSQL = mSQL ||
' join Divisions D on D.ID = TRO.Division_ID ';
mSQL = mSQL ||
mSQLWhere ||
'order by ' ||
' A.Code, ' || mSQLTurnsGroup;
--vykonani dynamicky sestaveneho mSQL
for EXECUTE STATEMENT mSQL
into AccountCode, AccountName, AccountType, DebitAmount, CreditAmount,
AccDate$Date, DivisionCode, DivisionName
do begin
suspend;
end
END