BEGIN
SumIIAmount = 0;
SumCRAmount = 0;
SumIIAmountWithoutVAT = 0;
SumCRAmountWithoutVAT = 0;
SumIICreditAmount = 0;
SumCRCreditAmount = 0;
SumRIAmount = 0;
TotalAmount = 0;
SumIICreditAmountWithoutVAT = 0;
SumCRCreditAmountWithoutVAT = 0;
TotalAmountWithoutVAT = 0;
mSumVDIAmount = 0;
mSumVDIAmountWithoutVAT = 0;
mSumVDIUsedAmount = 0;
mSumVDIUsedAmountWithoutVAT = 0;
mSumVDICreditAmount = 0;
mSumVDICreditAmountWithoutVAT = 0;
SumPSAmount = 0;
SumPSAmountWithoutVAT = 0;
/* Faktury vydane */
if (IssInvoiceType <> 2) then begin
select Sum(I2.LocalTAmount), Sum(ICN2.ICN2LTA), Sum(I2.LocalTAmountWithoutVAT), Sum(ICN2.ICN2LTAWV)
from IssuedInvoices II
left join IssuedInvoices2 I2 on II.ID=I2.Parent_ID
left join (Select RSource_ID, coalesce(sum(LocalTAmount), 0) as ICN2LTA, coalesce(sum(LocalTAmountWithoutVAT), 0) as ICN2LTAWV from IssuedCreditNotes2 group by RSource_ID) ICN2 on (ICN2.RSource_ID=I2.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
(:DueDateUsed <> 'A' or (II.DueDate$Date >= :DueDateFrom and II.DueDate$Date < :DueDateTo)) 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 :SumIIAmount, :SumIICreditAmount, :SumIIAmountWithoutVAT, :SumIICreditAmountWithoutVAT;
if (SumIIAmount IS NULL) then
SumIIAmount = 0;
if (SumIICreditAmount IS NULL) then
SumIICreditAmount = 0;
if (SumIIAmountWithoutVAT IS NULL) then
SumIIAmountWithoutVAT = 0;
if (SumIICreditAmountWithoutVAT IS NULL) then
SumIICreditAmountWithoutVAT = 0;
end
/*Prijate platby*/
if (CashRecType <> 2) then begin
select Sum(CR2.LocalTAmount), Sum(RCR2.RCR2LTA), Sum(CR2.LocalTAmountWithoutVAT), Sum(RCR2.RCR2LTAWV)
from CashReceived CR
left join CashReceived2 CR2 on CR2.Parent_ID=CR.ID
left join (Select RSource_ID, coalesce(sum(LocalTAmount), 0) as RCR2LTA, coalesce(sum(LocalTAmountWithoutVAT), 0) as RCR2LTAWV from REFUNDEDCASHRECEIVED2 group by RSource_ID) RCR2 on (RCR2.RSource_ID=CR2.ID)
where CR.Firm_ID = :AFirm_ID and (COALESCE(CR.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 (CR.DocDate$Date >= :DocDateFrom and CR.DocDate$Date < :DocDateTo)) and
(:AccDateUsed <> 'A' or (CR.AccDate$Date >= :AccDateFrom and CR.AccDate$Date < :AccDateTo)) and
(:DivisionSelID = '' or
(
CR2.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
(
CR2.Parent_ID=CR.ID and
CR2.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
(
CR2.Parent_ID=CR.ID and
CR2.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
(
CR2.Parent_ID=CR.ID and
CR2.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)))))
)
)
)
into :SumCRAmount, :SumCRCreditAmount, :SumCRAmountWithoutVAT, :SumCRCreditAmountWithoutVAT;
if (SumCRAmount IS NULL) then
SumCRAmount = 0;
if (SumCRCreditAmount IS NULL) then
SumCRCreditAmount = 0;
if (SumCRAmountWithoutVAT IS NULL) then
SumCRAmountWithoutVAT = 0;
if (SumCRCreditAmountWithoutVAT IS NULL) then
SumCRCreditAmountWithoutVAT = 0;
end
/*Souhrnne uctenky pokladniho prodeje*/
if (POSSummaredType <> 0) then begin
select SumPSAmount, SumPSAmountWithoutVAT
from SubscriberTurnoverFromRows2Pos(
:POSSummaredType, :AFirm_ID,
:DocDateUsed, :DocDateFrom, :DocDateTo,
:AccDateUsed, :AccDateFrom, :AccDateTo,
:BusOrderSelID, :ABusOrdersWithChilds,
:BusTransactionSelID, :ABusTransactionsWithChilds,
:BusProjectSelID, :ABusProjectsWithChilds,
:DivisionSelID, :ADivisionsWithChilds)
into
:SumPSAmount, SumPSAmountWithoutVAT;
end
/*Zalohove listy*/
if (IssDInv = 'A') then begin
select Sum((SI.LocalAmount - SI.LocalUsedAmount) * SI2.TAmount/SI.LocalAmount)
from IssuedDInvoices SI
left join IssuedDInvoices2 SI2 on SI2.Parent_ID=SI.ID
where (SI.LocalAmount <> 0) and SI.Firm_ID = :AFirm_ID and
(:DocDateUsed <> 'A' or (SI.DocDate$Date >= :DocDateFrom and SI.DocDate$Date < :DocDateTo)) and
(:DivisionSelID = '' or
(
SI2.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 (SI.DueDate$Date >= :DueDateFrom and SI.DueDate$Date < :DueDateTo)) and
(:BusOrderSelID = '' or
(
SI2.Parent_ID=SI.ID and
SI2.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
(
SI2.Parent_ID=SI.ID and
SI2.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
(
SI2.Parent_ID=SI.ID and
SI2.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)))))
)
)
)
into :SumRIAmount;
if (SumRIAmount IS NULL) then
SumRIAmount = 0;
end
/* DZV */
if (AVATIssDInv <> 3) then begin
select
Sum(I2.LocalTAmount),
Sum(I2.LocalCreditAmount),
Sum(I2.LocalTAmountWithoutVAT),
Sum(I2.LocalCreditAmountWithoutVAT),
Sum(I2.LocalUsedAmount),
Sum(I2.LocalUsedAmountWithoutVAT)
from
VATIssuedDInvoices II
left join VATIssuedDInvoices2 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 (IssInvoiceType = 0) then begin
SumIIAmount = SumIIAmount - SumIICreditAmount;
SumIIAmountWithoutVAT = SumIIAmountWithoutVAT - SumIICreditAmountWithoutVAT;
end
if (CashRecType = 0) then begin
SumCRAmount = SumCRAmount - SumCRCreditAmount;
SumCRAmountWithoutVAT = SumCRAmountWithoutVAT - SumCRCreditAmountWithoutVAT;
end
if (POSSummaredType <> 0) then begin
SumCRAmount = SumCRAmount + SumPSAmount;
SumCRAmountWithoutVAT = SumCRAmountWithoutVAT + SumPSAmountWithoutVAT;
end
if (AVATIssDInv = 0) then begin
SumIIAmount = SumIIAmount + mSumVDIAmount - mSumVDICreditAmount;
SumIIAmountWithoutVAT = SumIIAmountWithoutVAT + mSumVDIAmountWithoutVAT - mSumVDICreditAmountWithoutVAT;
end
if (AVATIssDInv = 1) then begin
SumIIAmount = SumIIAmount + mSumVDIAmount;
SumIIAmountWithoutVAT = SumIIAmountWithoutVAT + mSumVDIAmountWithoutVAT;
end
if (AVATIssDInv = 2) then begin
SumIIAmount = SumIIAmount + mSumVDIUsedAmount;
SumIIAmountWithoutVAT = SumIIAmountWithoutVAT + mSumVDIUsedAmountWithoutVAT;
end
SumIIAmount = SumIIAmount+ SumRIAmount;
SumIIAmountWithoutVAT = SumIIAmountWithoutVAT + SumRIAmount;
TotalAmount = SumIIAmount + SumCRAmount;
TotalAmountWithoutVAT = SumIIAmountWithoutVAT + SumCRAmountWithoutVAT;
Suspend;
end;