BEGIN
AccGroup_ID = '__________';
CreditAmount = 0;
DebetAmount = 0;
FOR
SELECT
Coalesce(D.DebitBusOrder_ID, '0000000000'),
D.AccDate$DATE,
D.Amount * (select Max(IValue) from SYS$DECIDETable where IValue = 0 or
D.DebitAccount_ID in (select AC.ID from Accounts AC where D.DebitAccount_ID = AC.ID and
AC.Code like :AccountCode ESCAPE '~' and (:AccountSelID = '' or
AC.ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID)))),
D.Amount * (select Max(IValue) from SYS$DECIDETable where IValue = 0 or
D.CreditAccount_ID in (select AC.ID from Accounts AC where D.CreditAccount_ID = AC.ID and
AC.Code like :AccountCode ESCAPE '~' and (:AccountSelID = '' or
AC.ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID))))
FROM
GeneralLedger D
JOIN Accounts DA ON DA.ID = D.DebitAccount_ID
JOIN Accounts CA ON CA.ID = D.CreditAccount_ID
WHERE
(D.AccDate$DATE >= :AccDateFrom AND D.AccDate$DATE < :AccDateTo) AND
(D.IsRequest = 'N') AND
( (DA.Code like :AccountCode ESCAPE '~') OR
(CA.Code like :AccountCode ESCAPE '~') ) AND
( (:AccountSelID = '') or
(D.CreditAccount_ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID)) or
(D.DebitAccount_ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID)) )
UNION ALL
SELECT
Coalesce(D.CreditBusOrder_ID, '0000000000'),
D.AccDate$DATE,
D.Amount * (select Max(IValue) from SYS$DECIDETable where IValue = 0 or
D.DebitAccount_ID in (select AC.ID from Accounts AC where D.DebitAccount_ID = AC.ID and
AC.Code like :AccountCode ESCAPE '~' and (:AccountSelID = '' or
AC.ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID)))),
D.Amount * (select Max(IValue) from SYS$DECIDETable where IValue = 0 or
D.CreditAccount_ID in (select AC.ID from Accounts AC where D.CreditAccount_ID = AC.ID and
AC.Code like :AccountCode ESCAPE '~' and (:AccountSelID = '' or
AC.ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID))))
FROM
GeneralLedger D
JOIN Accounts DA ON DA.ID = D.DebitAccount_ID
JOIN Accounts CA ON CA.ID = D.CreditAccount_ID
WHERE
(Coalesce(D.CreditBusOrder_id, '0000000000') <> Coalesce(D.DebitBusorder_ID, '0000000000')) and
(D.AccDate$DATE >= :AccDateFrom AND D.AccDate$DATE < :AccDateTo) AND
(D.IsRequest = 'N') AND
( (DA.Code like :AccountCode ESCAPE '~') OR
(CA.Code like :AccountCode ESCAPE '~') ) AND
( (:AccountSelID = '') or
(D.CreditAccount_ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID)) or
(D.DebitAccount_ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID)) )
ORDER BY 1
INTO
:mAccGroup_ID, :mAccDate$DATE, :mDebetAmount, :mCreditAmount
DO BEGIN
IF (AccGroup_ID = '__________' 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
-- V prípade, že volime vsechny pohyby, dotahneme ostatni zaznamy ke skupine
IF (:WholeGroup = 1) THEN
BEGIN
FOR
SELECT
GL.Amount * (select Max(IValue) from SYS$DECIDETable where IValue = 0 or
GL.DebitAccount_ID in (select AC.ID from Accounts AC where GL.DebitAccount_ID = AC.ID and
AC.Code like :AccountCode ESCAPE '~' and (:AccountSelID = '' or
AC.ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID)))),
GL.Amount * (select Max(IValue) from SYS$DECIDETable where IValue = 0 or
GL.CreditAccount_ID in (select AC.ID from Accounts AC where GL.CreditAccount_ID = AC.ID and
AC.Code like :AccountCode ESCAPE '~' and (:AccountSelID = '' or
AC.ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID))))
FROM GeneralLedger GL
WHERE Coalesce(GL.DebitBusOrder_ID, '0000000000') = :AccGroup_ID AND
(GL.IsRequest = 'N') AND
not (GL.AccDate$DATE >= :AccDateFrom AND GL.AccDate$DATE < :AccDateTo)
UNION ALL
SELECT
GL.Amount * (select Max(IValue) from SYS$DECIDETable where IValue = 0 or
GL.DebitAccount_ID in (select AC.ID from Accounts AC where GL.DebitAccount_ID = AC.ID and
AC.Code like :AccountCode ESCAPE '~' and (:AccountSelID = '' or
AC.ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID)))),
GL.Amount * (select Max(IValue) from SYS$DECIDETable where IValue = 0 or
GL.CreditAccount_ID in (select AC.ID from Accounts AC where GL.CreditAccount_ID = AC.ID and
AC.Code like :AccountCode ESCAPE '~' and (:AccountSelID = '' or
AC.ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID))))
FROM GeneralLedger GL
WHERE (Coalesce(GL.CreditBusOrder_id, '0000000000') <> Coalesce(GL.DebitBusorder_ID, '0000000000')) and Coalesce(GL.CreditBusOrder_ID, '0000000000') = :AccGroup_ID AND
(GL.IsRequest = 'N') AND
not (GL.AccDate$DATE >= :AccDateFrom AND GL.AccDate$DATE < :AccDateTo)
into :mDebetAmount2, mCreditAmount2
DO BEGIN
CreditAmount = CreditAmount + mCreditAmount2;
DebetAmount = DebetAmount + mDebetAmount2;
END
END
FOR
SELECT ID
FROM GeneralLedgerGroupsBOC1(:AccDateFrom, :AccDateTo, :AccountCode,
:AccountSelID, :WholeGroup, :AccGroup_ID)
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 <> '__________') THEN BEGIN
-- V prípade, že volime vsechny pohyby, dotahneme ostatni zaznamy ke skupine
IF (:WholeGroup = 1) THEN
BEGIN
FOR
SELECT
GL.Amount * (select Max(IValue) from SYS$DECIDETable where IValue = 0 or
GL.DebitAccount_ID in (select AC.ID from Accounts AC where GL.DebitAccount_ID = AC.ID and
AC.Code like :AccountCode ESCAPE '~' and (:AccountSelID = '' or
AC.ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID)))),
GL.Amount * (select Max(IValue) from SYS$DECIDETable where IValue = 0 or
GL.CreditAccount_ID in (select AC.ID from Accounts AC where GL.CreditAccount_ID = AC.ID and
AC.Code like :AccountCode ESCAPE '~' and (:AccountSelID = '' or
AC.ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID))))
FROM GeneralLedger GL
WHERE Coalesce(GL.DebitbusOrder_ID, '0000000000') = :AccGroup_ID AND
(GL.IsRequest = 'N') AND
not (GL.AccDate$DATE >= :AccDateFrom AND GL.AccDate$DATE < :AccDateTo)
UNION ALL
SELECT
GL.Amount * (select Max(IValue) from SYS$DECIDETable where IValue = 0 or
GL.DebitAccount_ID in (select AC.ID from Accounts AC where GL.DebitAccount_ID = AC.ID and
AC.Code like :AccountCode ESCAPE '~' and (:AccountSelID = '' or
AC.ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID)))),
GL.Amount * (select Max(IValue) from SYS$DECIDETable where IValue = 0 or
GL.CreditAccount_ID in (select AC.ID from Accounts AC where GL.CreditAccount_ID = AC.ID and
AC.Code like :AccountCode ESCAPE '~' and (:AccountSelID = '' or
AC.ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID))))
FROM GeneralLedger GL
WHERE (Coalesce(GL.CreditBusOrder_id, '0000000000') <> Coalesce(GL.DebitBusorder_ID, '0000000000')) and Coalesce(GL.CreditBusorder_ID, '0000000000') = :AccGroup_ID AND
not (GL.AccDate$DATE >= :AccDateFrom AND GL.AccDate$DATE < :AccDateTo)
into :mDebetAmount2, mCreditAmount2
DO BEGIN
CreditAmount = CreditAmount + mCreditAmount2;
DebetAmount = DebetAmount + mDebetAmount2;
END
END
FOR
SELECT ID
FROM GeneralLedgerGroupsBOC1(:AccDateFrom, :AccDateTo, :AccountCode,
:AccountSelID, :WholeGroup, :AccGroup_ID)
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;