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
mPeriod_ID, mDateFrom;
if (mPeriod_ID IS NOT NULL) then
begin
--je treba sestavit dotaz, ktery dotahne pocakty uctu, obraty mezi pocatkem uctu a datumem od
-- a obraty mezi datumem od a datumem do
--podminky
--
--pocatky
mSQLBeginnings =
'SELECT ' ||
' Account_ID, ' ||
' SUM(DebitAmount) as DebitBeginningSum, ' ||
' SUM(CreditAmount) as CreditBeginningSum ' ||
'FROM ' ||
' AccountBeginnings ' ||
'WHERE ' ||
'Period_ID = ''' || :mPeriod_ID || '''' ||
' and Division_ID = ''' || 'Division_ID_To_Replace' || '''';
select mRet from DSQL$GetAccountsCondition('', null, null,
null, null, null, null,
: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';
--obraty mezi pocatkem uctu a datumem od
mSQLTurnsFromBeg =
'SELECT ' ||
' Account_ID, ' ||
' SUM(DebitAmount) as DebitTurnoverSum, ' ||
' SUM(CreditAmount) as CreditTurnoverSum ' ||
'FROM ' ||
' GeneralLedgerSingle_VIEW GLV ' ||
'WHERE ' ||
'Division_ID = ''' || 'Division_ID_To_Replace' || '''';
select mRet from DSQL$GetAccountsCondition('GLV', :mDateFrom, :InAccDateFrom,
:InAllowRequests, :InAudited, null, null,
:InBusOrderSelID, :InBusOrdersWithChilds, :InBusTransactionSelID,
:InBusTransactionsWithChilds, :InBusProjectSelID, :InBusProjectsWithChilds,
:InFirmSelID, null, null,
null, null, null)
into :mHlp;
if (mHlp <> ' ') then
mSQLTurnsFromBeg = mSQLTurnsFromBeg || ' and ' || mHlp;
mSQLTurnsFromBeg = mSQLTurnsFromBeg ||
' GROUP BY Account_ID';
--obraty mezi datumem od a datumem do
mSQLTurns =
'SELECT ' ||
' Account_ID, ' ||
' SUM(DebitAmount) as DebitTurnoverSum, ' ||
' SUM(CreditAmount) as CreditTurnoverSum ' ||
'FROM ' ||
' GeneralLedgerSingle_VIEW GLV ' ||
'WHERE ' ||
'Division_ID = ''' || 'Division_ID_To_Replace' || '''';
select mRet from DSQL$GetAccountsCondition('GLV', :InAccDateFrom, :InAccDateTo,
:InAllowRequests, :InAudited, null, null,
:InBusOrderSelID, :InBusOrdersWithChilds, :InBusTransactionSelID,
:InBusTransactionsWithChilds, :InBusProjectSelID, :InBusProjectsWithChilds,
:InFirmSelID, null, null,
null, null, null)
into :mHlp;
if (mHlp <> ' ') then
mSQLTurns = mSQLTurns || ' and ' || mHlp;
mSQLTurns = mSQLTurns ||
' GROUP BY Account_ID';
--hlavni SQL
mSQL =
'SELECT ' ||
' (case when (A.AccountType=''0'') then ' ||
' (case when (A.IsCostForProjectControl = ''A'') then ' ||
' ''C'' ' ||
' else ' ||
' (case when (A.IsRevenueForProjectControl = ''A'') then ' ||
' ''G'' ' ||
' else ' ||
' A.AccountType ' ||
' end ) ' ||
' end ) ' ||
' else ' ||
' A.AccountType ' ||
' end ) as AccountType, ' ||
' A.Code, ' ||
' A.Name, ' ||
' coalesce(BG.DebitBeginningSum, 0), ' ||
' coalesce(BG.CreditBeginningSum, 0), ' ||
' coalesce(TOB.DebitTurnoverSum, 0), ' ||
' coalesce(TOB.CreditTurnoverSum, 0),' ||
' coalesce(TOR.DebitTurnoverSum, 0), ' ||
' coalesce(TOR.CreditTurnoverSum, 0) ' ||
'FROM ' ||
' Accounts A ';
--join na dotahnuti pocatku
mSQL = mSQL ||
' left join ' ||
' (' || mSQLBeginnings || ') BG on BG.Account_ID = A.ID ';
--join na dotahnuti obratu od pocatku do data od
mSQL = mSQL ||
' left join ' ||
' (' || mSQLTurnsFromBeg || ') TOB on TOB.Account_ID = A.ID ';
--join na dotahnuti obratu od data od do data do
mSQL = mSQL ||
' left join ' ||
'(' || mSQLTurns || ') TOR on TOR.Account_ID = A.ID ';
--zbytek celeho sql
if (InType = 0) then
mSQL = mSQL || ' WHERE A.AccountType IN (''A'', ''P'', ''0'') ';
else if (InType = 1) then
if (INWithOffBalance = 'N') then
mSQL = mSQL || ' WHERE A.AccountType IN (''C'', ''G'') ';
else
mSQL = mSQL || ' WHERE A.AccountType IN (''C'', ''G'', ''0'') ';
else
Exception ErrParameterNotInRange;
if (INAllowRequests = 'N') then
mSQL = mSQL || ' and A.IsIncomplete = ''N''';
if (INOnlyNonZeroAccounts = 'A') then begin
mSQL = mSQL || ' and (BG.DebitBeginningSum <> 0.0 ' ||
' or BG.CreditBeginningSum <> 0.0 ' ||
' or TOB.DebitTurnoverSum <> 0.0 ' ||
' or TOB.CreditTurnoverSum <> 0.0 ' ||
' or TOR.DebitTurnoverSum <> 0 ' ||
' or TOR.CreditTurnoverSum <> 0 ' ||
')';
end
mSQL = mSQL ||
'ORDER BY ' ||
' A.AccountType, A.Code, A.ID';
/* Pro kazde vybrane stredisko se provede vyber uctu */
/* stredisko */
for
select A.ID, A.Code, A.Name
from Divisions A
where
((:InDivisionSelID = '')
or ((A.ID in (select Bx.ID from Divisions Bx where Bx.Hidden = 'N'
and (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :InDivisionSelID))
or ((:InDivisionsWithChilds in ('1', 'A'))
and (Bx.ID in (select ID from SYS$Divisions2 where Superior_ID in
(select OBJ_ID from SELDAT where SEL_ID = :InDivisionSelID))))))))
into :mDivision_ID, :Division_Code, :Division_Name
do begin
Division_ID = :mDivision_ID;
mSQLRun = REPLACE(mSQL, 'Division_ID_To_Replace', :mDivision_ID);
--vykonani dynamicky sestaveneho mSQL
for EXECUTE STATEMENT mSQLRun
into
:AccountType, :AccountCode, :AccountName,
:mDebitBeginning, :mCreditBeginning,
:mDebitBeginnigTurnover, :mCreditBeginnigTurnover,
:mDebitTurnover, :mCreditTurnover
do begin
if (AccountType in ('P', 'G')) then begin
OpeningBalance = mCreditBeginning - mDebitBeginning + mCreditBeginnigTurnover - mDebitBeginnigTurnover;
FinalBalance = OpeningBalance + mCreditTurnover - mDebitTurnover;
end
else
begin
OpeningBalance = mDebitBeginning - mCreditBeginning + mDebitBeginnigTurnover - mCreditBeginnigTurnover;
FinalBalance = OpeningBalance + mDebitTurnover - mCreditTurnover;
end
suspend;
end
end
end
end