BEGIN
SumRIAmount = 0;
SumCPAmount = 0;
SumRIAmountWithoutVAT = 0;
SumCPAmountWithoutVAT = 0;
SumRICreditAmount = 0;
SumCPCreditAmount = 0;
SumIDIAmount = 0;
TotalAmount = 0;
SumRICreditAmountWithoutVAT = 0;
SumCPCreditAmountWithoutVAT = 0;
TotalAmountWithoutVAT = 0;
mSumVDIAmount = 0;
mSumVDIAmountWithoutVAT = 0;
mSumVDIUsedAmount = 0;
mSumVDIUsedAmountWithoutVAT = 0;
mSumVDICreditAmount = 0;
mSumVDICreditAmountWithoutVAT = 0;
/* Faktury prijate */
if (RecInvoiceType <> 2) then begin
select
Sum(I2.LocalTAmount), sum(RCN2.RCN2LTA), Sum(I2.LocalTAmountWithoutVAT), sum(RCN2.RCN2LTAWV)
from
ReceivedInvoices RI
left join ReceivedInvoices2 I2 on I2.Parent_ID=RI.ID
left join (Select RSource_ID, coalesce(sum(LocalTAmount), 0) as RCN2LTA, coalesce(sum(LocalTAmountWithoutVAT), 0) as RCN2LTAWV from ReceivedCreditNotes2 group by RSource_ID) RCN2 on (RCN2.RSource_ID=I2.ID)
where
RI.Firm_ID = :AFirm_ID and
(:DocDateUsed <> 'A' or (RI.DocDate$Date >= :DocDateFrom and RI.DocDate$Date < :DocDateTo)) and
(:AccDateUsed <> 'A' or (RI.AccDate$Date >= :AccDateFrom and RI.AccDate$Date < :AccDateTo)) and
(:DivisionSelID = '' or
(
I2.Division_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
(:DueDateUsed <> 'A' or (RI.DueDate$Date >= :DueDateFrom and RI.DueDate$Date < :DueDateTo)) and
(:BusOrderSelID = '' or
(
I2.Parent_ID=RI.ID and
I2.BusOrder_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 X.ID from Sys$BusOrders2 X where X.Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusOrderSelID)))))
)
)
) and
(:BusTransactionSelID = '' or
(
I2.Parent_ID=RI.ID and
I2.BusTransaction_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
(
I2.Parent_ID=RI.ID and
I2.BusProject_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 X.ID from Sys$BusProjects2 X where X.Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusProjectSelID)))))
)
)
) and
(('' = :PaidStatus) or
(('>' = :PaidStatus) AND (RI.Amount > RI.PaidAmount)) or
(('=' = :PaidStatus) AND (RI.Amount = RI.PaidAmount)) or
(('<=' = :PaidStatus) AND (RI.Amount <= RI.PaidAmount)) or
(('<>' = :PaidStatus) AND (RI.Amount <> RI.PaidAmount)) or
(('<' = :PaidStatus) AND (RI.Amount < RI.PaidAmount)))
group by
Firm_ID
into
:SumRIAmount, :SumRICreditAmount, :SumRIAmountWithoutVAT, :SumRICreditAmountWithoutVAT;
if (SumRIAmount IS NULL) then
SumRIAmount = 0;
if (SumRICreditAmount IS NULL) then
SumRICreditAmount = 0;
if (SumRIAmountWithoutVAT IS NULL) then
SumRIAmountWithoutVAT = 0;
if (SumRICreditAmountWithoutVAT IS NULL) then
SumRICreditAmountWithoutVAT = 0;
end
/*Vydane platby*/
if (CashPaidType <> 2) then begin
select
Sum(CP2.LocalTAmount), Sum(RCP2.RCP2LTA), Sum(CP2.LocalTAmountWithoutVAT), Sum(RCP2.RCP2LTAWV)
from
CashPaid CP
left join CashPaid2 CP2 on CP2.Parent_ID=CP.ID
left join (Select RSource_ID, coalesce(sum(LocalTAmount), 0) as RCP2LTA, coalesce(sum(LocalTAmountWithoutVAT), 0) as RCP2LTAWV
from REFUNDEDCASHPAID2 group by RSource_ID) RCP2 on (RCP2.RSource_ID=CP2.ID)
where
CP.Firm_ID = :AFirm_ID and (COALESCE(CP.PDocument_ID, '0000000000') = '0000000000') and -- Aby server použil index za sloupec Firm_ID a ne index za pole PDocument_ID použiváme funkci COALESCE.
(:DocDateUsed <> 'A' or (CP.DocDate$Date >= :DocDateFrom and CP.DocDate$Date < :DocDateTo)) and
(:AccDateUsed <> 'A' or (CP.AccDate$Date >= :AccDateFrom and CP.AccDate$Date < :AccDateTo)) and
(:DivisionSelID = '' or
(
CP2.Division_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
(
CP2.Parent_ID=CP.ID and
CP2.BusOrder_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 X.ID from Sys$BusOrders2 X where X.Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusOrderSelID)))))
)
)
) and
(:BusTransactionSelID = '' or
(
CP2.Parent_ID=CP.ID and
CP2.BusTransaction_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
(
CP2.Parent_ID=CP.ID and
CP2.BusProject_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 X.ID from Sys$BusProjects2 X where X.Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusProjectSelID)))))
)
)
)
group by
Firm_ID
into
:SumCPAmount, :SumCPCreditAmount, :SumCPAmountWithoutVAT, :SumCPCreditAmountWithoutVAT;
if (SumCPAmount IS NULL) then
SumCPAmount = 0;
if (SumCPCreditAmount IS NULL) then
SumCPCreditAmount = 0;
if (SumCPAmountWithoutVAT IS NULL) then
SumCPAmountWithoutVAT = 0;
if (SumCPCreditAmountWithoutVAT IS NULL) then
SumCPCreditAmountWithoutVAT = 0;
end
/*Zalohove listy*/
if (RecDInv = 'A') then begin
select
sum((RDI.LocalAmount - RDI.LocalUsedAmount) * RDI2.TAmount/RDI.LocalAmount)
from
ReceivedDInvoices RDI
left join ReceivedDInvoices2 RDI2 on RDI2.Parent_ID=RDI.ID
where
RDI.LocalAmount <> 0 and
RDI.Firm_ID = :AFirm_ID and
(:DocDateUsed <> 'A' or (RDI.DocDate$Date >= :DocDateFrom and RDI.DocDate$Date < :DocDateTo)) and
(:DivisionSelID = '' or
(
RDI2.Division_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
(:DueDateUsed <> 'A' or (RDI.DueDate$Date >= :DueDateFrom and RDI.DueDate$Date < :DueDateTo)) and
(:BusOrderSelID = '' or
(
RDI2.Parent_ID=RDI.ID and
RDI2.BusOrder_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 X.ID from Sys$BusOrders2 X where X.Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusOrderSelID)))))
)
)
) and
(:BusTransactionSelID = '' or
(
RDI2.Parent_ID=RDI.ID and
RDI2.BusTransaction_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
(
RDI2.Parent_ID=RDI.ID and
RDI2.BusProject_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 X.ID from Sys$BusProjects2 X where X.Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusProjectSelID)))))
)
)
)
group by
Firm_ID
into
:SumIDIAmount;
if (SumIDIAmount IS NULL) then
SumIDIAmount = 0;
end
/* DZP */
if (AVATRecDInv <> 3) then begin
select
Sum(I2.LocalTAmount),
Sum(I2.LocalCreditAmount),
Sum(I2.LocalTAmountWithoutVAT),
Sum(I2.LocalCreditAmountWithoutVAT),
Sum(I2.LocalUsedAmount),
Sum(I2.LocalUsedAmountWithoutVAT)
from
VATReceivedDInvoices II
left join VATReceivedDInvoices2 I2 on I2.Parent_ID=II.ID
where
II.Firm_ID = :AFirm_ID and
(:DocDateUsed <> 'A' or (II.DocDate$Date >= :DocDateFrom and II.DocDate$Date < :DocDateTo)) and
(:AccDateUsed <> 'A' or (II.AccDate$Date >= :AccDateFrom and II.AccDate$Date < :AccDateTo)) and
( (:DivisionSelID = '') or
(
I2.Division_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
(
I2.Parent_ID=II.ID and
I2.BusOrder_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
(
I2.Parent_ID=II.ID and
I2.BusTransaction_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
(
I2.Parent_ID=II.ID and
I2.BusProject_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)))))
)
)
) and
(('' = :PaidStatus) or
(('>' = :PaidStatus) AND (II.Amount > II.PaidAmount)) or
(('=' = :PaidStatus) AND (II.Amount = II.PaidAmount)) or
(('<=' = :PaidStatus) AND (II.Amount <= II.PaidAmount)) or
(('<>' = :PaidStatus) AND (II.Amount <> II.PaidAmount)) or
(('<' = :PaidStatus) AND (II.Amount < II.PaidAmount)))
into
:mSumVDIAmount,
:mSumVDICreditAmount,
:mSumVDIAmountWithoutVAT,
:mSumVDICreditAmountWithoutVAT,
:mSumVDIUsedAmount,
:mSumVDIUsedAmountWithoutVAT;
if (mSumVDIAmount IS NULL) then
mSumVDIAmount = 0;
if (mSumVDICreditAmount IS NULL) then
mSumVDICreditAmount = 0;
if (mSumVDIAmountWithoutVAT IS NULL) then
mSumVDIAmountWithoutVAT = 0;
if (mSumVDICreditAmountWithoutVAT IS NULL) then
mSumVDICreditAmountWithoutVAT = 0;
if (mSumVDIUsedAmount IS NULL) then
mSumVDIUsedAmount = 0;
if (mSumVDIUsedAmountWithoutVAT IS NULL) then
mSumVDIUsedAmountWithoutVAT = 0;
end
ID = AFirm_ID;
if (RecInvoiceType = 0) then begin
SumRIAmount = SumRIAmount - SumRICreditAmount;
SumRIAmountWithoutVAT = SumRIAmountWithoutVAT - SumRICreditAmountWithoutVAT;
end
if (CashPaidType = 0) then begin
SumCPAmount = SumCPAmount - SumCPCreditAmount;
SumCPAmountWithoutVAT = SumCPAmountWithoutVAT - SumCPCreditAmountWithoutVAT;
end
if (AVATRecDInv = 0) then begin
SumRIAmount = SumRIAmount + mSumVDIAmount - mSumVDICreditAmount;
SumRIAmountWithoutVAT = SumRIAmountWithoutVAT + mSumVDIAmountWithoutVAT - mSumVDICreditAmountWithoutVAT;
end
if (AVATRecDInv = 1) then begin
SumRIAmount = SumRIAmount + mSumVDIAmount;
SumRIAmountWithoutVAT = SumRIAmountWithoutVAT + mSumVDIAmountWithoutVAT;
end
if (AVATRecDInv = 2) then begin
SumRIAmount = SumRIAmount + mSumVDIUsedAmount;
SumRIAmountWithoutVAT = SumRIAmountWithoutVAT + mSumVDIUsedAmountWithoutVAT;
end
SumRIAmount = SumRIAmount + SumIDIAmount;
SumRIAmountWithoutVAT = SumRIAmountWithoutVAT + SumIDIAmount;
TotalAmount = SumRIAmount + SumCPAmount;
TotalAmountWithoutVAT = SumRIAmountWithoutVAT + SumCPAmountWithoutVAT;
Suspend;
end