BEGIN
/* FB */
GroupFirm_ID = NULL;
mFullAccGroup = '__________';
AccGroup_ID = mFullAccGroup;
CreditAmount = 0;
DebetAmount = 0;
FOR
SELECT X.AccGroup_ID, X.AccDate$DATE, X.DebetAmount, X.CreditAmount
FROM (
/* DEBIT */
SELECT
D.AccGroupDebit_ID AccGroup_ID,
D.AccDate$DATE AccDate$DATE,
D.Amount DebetAmount,
0 CreditAmount
FROM
GeneralLedger D
JOIN Accounts AC ON AC.ID = D.DebitAccount_ID
WHERE
D.IsRequest='N' and
(D.AccDate$DATE >= :AccDateFrom AND D.AccDate$DATE < :AccDateTo) and
(AC.Code like :AccountCode ESCAPE '~') and
((:AccountSelID = '') or (D.DebitAccount_ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID))) and
(:TextUsed <> 'A' or D.Text like :Text ESCAPE '~') and
(:AmountUsed <> 'A' or D.Amount BETWEEN :AmountFrom and :AmountTo) and
(:AmountInCurrencyUsed <> 'A' or D.AmountInCurrency BETWEEN :AmountInCurrencyFrom and :AmountInCurrencyTo) and
(:OrdNumberUsed <> 'A' or D.OrdNumber BETWEEN :OrdNumberFrom and :OrdNumberTo) and
(D.Audited like :Audited) and
(:DocumentTypeSelID = '' or
D.AccDocQueue_ID IN (SELECT ID FROM AccDocQueues AD WHERE
AD.DocumentType in (select OBJ_ID from SELDAT where SEL_ID=:DocumentTypeSelID))) and
(:AccDocQueueSelID = '' or
D.AccDocQueue_ID in (select OBJ_ID from SELDAT where SEL_ID=:AccDocQueueSelID)) and
(:PeriodSelID = '' or
D.Period_ID in (select OBJ_ID from SELDAT where SEL_ID=:PeriodSelID)) and
(:FirmSelID = '' or
((D.Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :FirmSelID))
or
(D.Firm_ID in (SELECT ID FROM Firms WHERE Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :FirmSelID))))) and
(:CurrencySelID = '' or
D.Currency_ID in (select OBJ_ID from SELDAT where SEL_ID=:CurrencySelID)) and
( (:DivisionSelID = '') OR
(D.DebitDivision_ID IN (SELECT Bx.ID FROM Divisions Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :DivisionSelID)) OR
((:ADivisionsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$Divisions2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :DivisionSelID)))))) )
) AND
( (:BusOrderSelID = '') OR
(D.DebitBusOrder_ID IN (SELECT Bx.ID FROM BusOrders Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :BusOrderSelID)) OR
((:ABusOrdersWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusOrders2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusOrderSelID)))))) )
) AND
( (:BusTransactionSelID = '') OR
(D.DebitBusTransaction_ID IN (SELECT Bx.ID FROM BusTransactions Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :BusTransactionSelID)) OR
((:ABusTransactionsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusTransactions2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusTransactionSelID)))))) )
) AND
( (:BusProjectSelID = '') OR
(D.DebitBusProject_ID IN (SELECT Bx.ID FROM BusProjects Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :BusProjectSelID)) OR
((:ABusProjectsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusProjects2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusProjectSelID)))))) )
)
UNION ALL
/* CREDIT */
SELECT
D.AccGroupCredit_ID AccGroup_ID,
D.AccDate$DATE AccDate$DATE,
0 DebetAmount,
D.Amount CreditAmount
FROM
GeneralLedger D
JOIN Accounts AC ON AC.ID = D.CreditAccount_ID
WHERE
D.IsRequest='N' and
(D.AccDate$DATE >= :AccDateFrom AND D.AccDate$DATE < :AccDateTo) and
(AC.Code like :AccountCode ESCAPE '~') and
((:AccountSelID = '') or (D.CreditAccount_ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID))) and
(:TextUsed <> 'A' or D.Text like :Text ESCAPE '~') and
(:AmountUsed <> 'A' or D.Amount BETWEEN :AmountFrom and :AmountTo) and
(:AmountInCurrencyUsed <> 'A' or D.AmountInCurrency BETWEEN :AmountInCurrencyFrom and :AmountInCurrencyTo) and
(:OrdNumberUsed <> 'A' or D.OrdNumber BETWEEN :OrdNumberFrom and :OrdNumberTo) and
(D.Audited like :Audited) and
(:DocumentTypeSelID = '' or
D.AccDocQueue_ID IN (SELECT ID FROM AccDocQueues AD WHERE
AD.DocumentType in (select OBJ_ID from SELDAT where SEL_ID=:DocumentTypeSelID))) and
(:AccDocQueueSelID = '' or
D.AccDocQueue_ID in (select OBJ_ID from SELDAT where SEL_ID=:AccDocQueueSelID)) and
(:PeriodSelID = '' or
D.Period_ID in (select OBJ_ID from SELDAT where SEL_ID=:PeriodSelID)) and
(:FirmSelID = '' or
((D.Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :FirmSelID))
or
(D.Firm_ID in (SELECT ID FROM Firms WHERE Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :FirmSelID))))) and
(:CurrencySelID = '' or
D.Currency_ID in (select OBJ_ID from SELDAT where SEL_ID=:CurrencySelID)) and
( (:DivisionSelID = '') OR
(D.CreditDivision_ID IN (SELECT Bx.ID FROM Divisions Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :DivisionSelID)) OR
((:ADivisionsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$Divisions2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :DivisionSelID)))))) )
) AND
( (:BusOrderSelID = '') OR
(D.CreditBusOrder_ID IN (SELECT Bx.ID FROM BusOrders Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :BusOrderSelID)) OR
((:ABusOrdersWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusOrders2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusOrderSelID)))))) )
) AND
( (:BusTransactionSelID = '') OR
(D.CreditBusTransaction_ID IN (SELECT Bx.ID FROM BusTransactions Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :BusTransactionSelID)) OR
((:ABusTransactionsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusTransactions2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusTransactionSelID)))))) )
) AND
( (:BusProjectSelID = '') OR
(D.CreditBusProject_ID IN (SELECT Bx.ID FROM BusProjects Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :BusProjectSelID)) OR
((:ABusProjectsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusProjects2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusProjectSelID)))))) )
)
) X
ORDER BY
X.AccGroup_ID ASC, X.AccDate$DATE ASC
INTO
mAccGroup_ID, mAccDate$DATE, mDebetAmount, mCreditAmount
DO BEGIN
IF (AccGroup_ID = mFullAccGroup or ((AccGroup_ID = mAccGroup_ID) or (AccGroup_ID is null and mAccGroup_ID is null))) THEN
BEGIN
CreditAmount = CreditAmount + mCreditAmount;
DebetAmount = DebetAmount + mDebetAmount;
AccGroup_ID = mAccGroup_ID;
AccDate$DATE = mAccDate$DATE;
END
ELSE
BEGIN
FOR
SELECT ID
FROM GeneralLedgerGrps2A1(:AccDateFrom, :AccDateTo, :AccountCode,
:AccountSelID, :WholeGroup, :AccGroup_ID, :PeriodSelID, :TextUsed, :Text,
:AmountUsed, :AmountFrom, :AmountTo, :AmountInCurrencyUsed, :AmountInCurrencyFrom,
:AmountInCurrencyTo, :OrdNumberUsed, :OrdNumberFrom, :OrdNumberTo, :Audited,
:DocumentTypeSelID, :AccDocQueueSelID, :CurrencySelID, :FirmSelID, :DivisionSelID,
:ADivisionsWithChilds, :BusOrderSelID, :ABusOrdersWithChilds, :BusTransactionSelID,
:ABusTransactionsWithChilds, :BusProjectSelID, :ABusProjectsWithChilds)
INTO :ID
DO BEGIN
IF (ShowGroups = '0') THEN
SUSPEND;
IF (ShowGroups = '1' AND
((CreditAmount <= DebetAmount AND DebetAmount - CreditAmount >= 0.01) OR
(CreditAmount > DebetAmount AND CreditAmount - DebetAmount >= 0.01)) )
THEN
SUSPEND;
IF (ShowGroups = '2' AND
((CreditAmount <= DebetAmount AND DebetAmount - CreditAmount < 0.01) OR
(CreditAmount > DebetAmount AND CreditAmount - DebetAmount < 0.01)) )
THEN
SUSPEND;
END
CreditAmount = mCreditAmount;
DebetAmount = mDebetAmount;
AccGroup_ID = mAccGroup_ID;
AccDate$DATE = mAccDate$DATE;
END
END
/* Tady je ještě potřeba provést pro poslední data !!!! */
IF (AccGroup_ID <> mFullAccGroup) THEN
BEGIN
FOR
SELECT ID
FROM GeneralLedgerGrps2A1(:AccDateFrom, :AccDateTo, :AccountCode,
:AccountSelID, :WholeGroup, :AccGroup_ID, :PeriodSelID, :TextUsed, :Text,
:AmountUsed, :AmountFrom, :AmountTo, :AmountInCurrencyUsed, :AmountInCurrencyFrom,
:AmountInCurrencyTo, :OrdNumberUsed, :OrdNumberFrom, :OrdNumberTo, :Audited,
:DocumentTypeSelID, :AccDocQueueSelID, :CurrencySelID, :FirmSelID, :DivisionSelID,
:ADivisionsWithChilds, :BusOrderSelID, :ABusOrdersWithChilds, :BusTransactionSelID,
:ABusTransactionsWithChilds, :BusProjectSelID, :ABusProjectsWithChilds)
INTO :ID
DO BEGIN
IF (ShowGroups = '0') THEN
SUSPEND;
IF (ShowGroups = '1' AND
((CreditAmount <= DebetAmount AND DebetAmount - CreditAmount >= 0.01) OR
(CreditAmount > DebetAmount AND CreditAmount - DebetAmount >= 0.01)) )
THEN
SUSPEND;
IF (ShowGroups = '2' AND
((CreditAmount <= DebetAmount AND DebetAmount - CreditAmount < 0.01) OR
(CreditAmount > DebetAmount AND CreditAmount - DebetAmount < 0.01)) )
THEN
SUSPEND;
END
END
END