begin
-- odvozeni obdobi ze vstupnich datumu
select
P.ID, P.DateFrom$Date
from
Periods P
where
P.ID = (select RPeriod_ID from GetFirstperiodByDates(:InAccDateFrom, :InAccDateTo, ''))
into
Period_ID, PeriodFromDate;
if (Period_ID is not null) then begin
if (InOnlyNonZeroAccounts = 'A') then begin
mInOutputType = 0;
end else begin
mInOutputType = InOutputType;
end
--je treba sestavit dotaz, ktery dotahne pocakty uctu, obraty mezi pocatkem uctu a datumem od
-- a obraty mezi datumem od a datumem do
--sestaveni techto 3 casti do celkoveho dotazu ovlivnuje parametr mInOutputType, pokud se
--ale pouzije InOnlyNonZeroAccounts = 'A', pak se tam vlozi vsechny casti kvuli vyhodnoceni teto podminky
--
--pocatky uctu
if (mInOutputType in (0,1)) then begin
mSQLBeginnings =
'SELECT ' ||
' Account_ID, ' ||
' SUM(DebitAmount) as DebitBeginningSum, ' ||
' SUM(CreditAmount) as CreditBeginningSum ' ||
'FROM ' ||
' AccountBeginnings ' ||
'WHERE ';
mSQLBeginnings = mSQLBeginnings ||
'Period_ID = ''' || :Period_ID || '''';
select mRet from DSQL$GetAccountsCondition('', null, null,
null, null, :InDivisionSelID, :InDivisionsWithChilds,
:InBusOrderSelID, :InBusOrdersWithChilds, :InBusTransactionSelID,
:InBusTransactionsWithChilds, :InBusProjectSelID, :InBusProjectsWithChilds,
null, null, null,
null, null, null)
into :mHlp;
if (mHlp <> ' ') then
mSQLBeginnings = mSQLBeginnings || ' and ' || mHlp;
mSQLBeginnings = mSQLBeginnings ||
'GROUP BY ' ||
' Account_ID';
end
--obraty mezi pocatkem uctu a datumem od
if (mInOutputType in (0,2)) then begin
mSQLTurnsFromBeg =
'SELECT ' ||
' Account_ID, ' ||
' SUM(DebitAmount) as DebitTurnoverSum, ' ||
' SUM(CreditAmount) as CreditTurnoverSum ' ||
'FROM ' ||
' GeneralLedgerSingle_VIEW GLV ';
select mRet from DSQL$GetAccountsCondition('GLV', :PeriodFromDate, :InAccDateFrom,
:InAllowRequests, :InAudited, :InDivisionSelID, :InDivisionsWithChilds,
:InBusOrderSelID, :InBusOrdersWithChilds, :InBusTransactionSelID,
:InBusTransactionsWithChilds, :InBusProjectSelID, :InBusProjectsWithChilds,
null, null, null,
null, null, null)
into :mHlp;
if (mHlp <> ' ') then
mSQLTurnsFromBeg = mSQLTurnsFromBeg || ' where ' || mHlp;
mSQLTurnsFromBeg = mSQLTurnsFromBeg ||
'GROUP BY ' ||
' Account_ID';
end
--obraty mezi datumem od a datumem do
if (mInOutputType in (0,3)) then begin
mSQLTurns =
'SELECT ' ||
' Account_ID, ' ||
' Count(DebitAmount) as DebitTurnoverCount, ' ||
' Count(CreditAmount) as CreditTurnoverCount, ' ||
' SUM(DebitAmount) as DebitTurnoverSum, ' ||
' SUM(CreditAmount) as CreditTurnoverSum ' ||
'FROM ' ||
' GeneralLedgerSingle_VIEW GLV ';
select mRet from DSQL$GetAccountsCondition('GLV', :InAccDateFrom, :InAccDateTo,
:InAllowRequests, :InAudited, :InDivisionSelID, :InDivisionsWithChilds,
:InBusOrderSelID, :InBusOrdersWithChilds, :InBusTransactionSelID,
:InBusTransactionsWithChilds, :InBusProjectSelID, :InBusProjectsWithChilds,
:InFirmSelID, null, null,
null, null, null)
into :mHlp;
if (mHlp <> ' ') then
mSQLTurns = mSQLTurns || ' where ' || mHlp;
mSQLTurns = mSQLTurns ||
'GROUP BY ' ||
' Account_ID';
end
--hlavni SQL
mSQL =
'SELECT ' ||
' A.ID ';
if (mInOutputType in (0,1)) then begin
mSQL = mSQL ||
' , coalesce(BG.DebitBeginningSum, 0) ' ||
' , coalesce(BG.CreditBeginningSum, 0) ';
end else begin
mSQL = mSQL ||
' ,null ' ||
' ,null ';
end
if (mInOutputType in (0,2)) then begin
mSQL = mSQL ||
' , coalesce(TOB.DebitTurnoverSum, 0) ' ||
' , coalesce(TOB.CreditTurnoverSum, 0) ';
end else begin
mSQL = mSQL ||
' ,null ' ||
' ,null ';
end
if (mInOutputType in (0,3)) then begin
mSQL = mSQL ||
' ,coalesce(TOR.DebitTurnoverSum, 0) ' ||
' ,coalesce(TOR.CreditTurnoverSum, 0) ' ||
' ,coalesce(TOR.DebitTurnoverCount, 0) ' ||
' ,coalesce(TOR.CreditTurnoverCount, 0) ';
end else begin
mSQL = mSQL ||
' ,null ' ||
' ,null ' ||
' ,null ' ||
' ,null ';
end
mSQL = mSQL ||
'FROM ' ||
' Accounts A ';
--join na dotahnuti pocatku
if (mInOutputType in (0,1)) then
mSQL = mSQL ||
' left join ' ||
' (' || mSQLBeginnings || ') BG on BG.Account_ID = A.ID ';
--join na dotahnuti obratu od pocatku do data od
if (mInOutputType in (0,2)) then
mSQL = mSQL ||
' left join ' ||
' (' || mSQLTurnsFromBeg || ') TOB on TOB.Account_ID = A.ID ';
--join na dotahnuti obratu od data od do data do
if (mInOutputType in (0,3)) then
mSQL = mSQL ||
' left join ' ||
'(' || mSQLTurns || ') TOR on TOR.Account_ID = A.ID ';
--zbytek celeho sql
mSQL = mSQL || ' where 1=1 ';
if (InAccountSelID is not Null and InAccountSelID <> '') then
mSQL = mSQL || ' and exists(select 1 from SELDAT where OBJ_ID = A.ID and SEL_ID = ''' || InAccountSelID || ''')';
if (InAccountCode is not Null and InAccountCode <> '') then
mSQL = mSQL || ' and A.Code LIKE ''' || InAccountCode || ''' ESCAPE ''~''';
if (InAccount_ID is not Null and InAccount_ID <> '') then
mSQL = mSQL || ' and A.ID = ''' || InAccount_ID || '''';
if (InAllowRequests = 'N') then
mSQL = mSQL || ' and A.IsIncomplete = ''N''';
if (InOnlyNonZeroAccounts = 'A') then
mSQL = mSQL || ' and (BG.DebitBeginningSum <> 0.0 ' ||
' or BG.CreditBeginningSum <> 0.0 ' ||
' or TOB.DebitTurnoverSum <> 0.0 ' ||
' or TOB.CreditTurnoverSum <> 0.0 ' ||
' or TOR.DebitTurnoverCount <> 0 ' ||
' or TOR.CreditTurnoverCount <> 0 ' ||
')';
mSQL = mSQL ||
'ORDER BY ' ||
' A.AccountType, A.Code, A.ID';
--vykonani dynamicky sestaveneho mSQL
--insert into dbg(msg) values (:mSQL);
for execute statement mSQL
into :Account_ID,
:DebitBeginning, :CreditBeginning,
:DebitBeginnigTurnover, :CreditBeginnigTurnover,
:DebitTurnover, :CreditTurnover,
:DebitTurnoverCount, :CreditTurnoverCount
do begin
suspend;
end
end
end