BEGIN
select Count(DT.ID) from DueTermLimits DT where
(:DueTermLimitSelID = '' or DT.ID in (select OBJ_ID from SELDAT where SEL_ID = :DueTermLimitSelID))
into :CountLimits;
--Zkonstruuje se where cast dotazu
SQLWhere = '';
SQLDueDate = '';
SQLVATDate1 = '';
SQLVATDate2 = '';
SQLAccDate = '';
--Firm_ID
SQLWhere = SQLWhere || '( ';
SQLWhere = SQLWhere || 'II.Firm_ID = ''' || :FirmOID || ''' or ';
Select AResult from SYS$CONCATSELECT('ID', 'Firms where Firm_ID = ''' || :FirmOID || '''') into :SQLConc;
SQLWhere = SQLWhere || 'II.Firm_ID in (' || :SQLConc || ')';
SQLWhere = SQLWhere || ')';
--DocDate$Date
SQLWhere = SQLWhere || ' and II.DocDate$Date >= ' || CAST(:DocDateFrom AS VARCHAR(100)) || ' and II.DocDate$Date < ' || CAST(:DocDateTo AS VARCHAR(100));
--DocQueue_ID
if (:DocQueueSelID <> '') then begin
Select AResult from SYS$CONCATSELECT('OBJ_ID', 'SELDAT where SEL_ID = ''' || :DocQueueSelID || '''') into :SQLConc;
SQLWhere = SQLWhere || ' and II.DocQueue_ID in (' || :SQLConc || ')';
end
--OrdNumber
if (:OrdNumberUsed = 'A') then
SQLWhere = SQLWhere || ' and II.OrdNumber between ' || :OrdNumberFrom || ' and ' || :OrdNumberTo;
--VATDate$Date
if (:VATDateUsed = 'A') then begin
SQLVATDate1 = ' and II.VATDate$Date >= ' || CAST(:VATDateFrom AS VARCHAR(100)) || ' and II.VATDate$Date < ' || CAST(:VATDateTo AS VARCHAR(100));
SQLVATDate2 = ' and II.DOCDate$Date >= ' || CAST(:VATDateFrom AS VARCHAR(100)) || ' and II.DOCDate$Date < ' || CAST(:VATDateTo AS VARCHAR(100));
end
--DueDate$Date !!! posledni select tuto podminku nebere, proto je v extra promenne !!!
if (:DueDateUsed = 'A') then
SQLDueDate = ' and II.DueDate$Date >= ' || CAST(:DueDateFrom AS VARCHAR(100)) || ' and II.DueDate$Date < ' || CAST(:DueDateTo AS VARCHAR(100));
--AccDate$Date !!! podminka neni pouzita u zalohovych listu, protoze namaji datum uctovani !!!
if (:AccDateUsed = 'A') then
SQLAccDate = ' and II.AccDate$Date >= ' || CAST(:AccDateFrom AS VARCHAR(100)) || ' and II.AccDate$Date < ' || CAST(:AccDateTo AS VARCHAR(100));
--CreatedBy_ID
if (:CreatedBySelID <> '') then begin
Select AResult from SYS$CONCATSELECT('OBJ_ID', 'SELDAT where SEL_ID = ''' || :CreatedBySelID || '''') into :SQLConc;
SQLWhere = SQLWhere || ' and II.CreatedBy_ID in (' || :SQLConc || ')';
end
--CorrectedBy_ID
if (:CorrectedBySelID <> '') then begin
Select AResult from SYS$CONCATSELECT('OBJ_ID', 'SELDAT where SEL_ID = ''' || :CorrectedBySelID || '''') into :SQLConc;
SQLWhere = SQLWhere || ' and II.CorrectedBy_ID in (' || :SQLConc || ')';
end
/* Faktury vydane */
SQLStat = 'select II.ID, II.DueDate$Date, II.VATDate$Date, II.LocaLAmount, II.Firm_ID from IssuedInvoices II where ' || SQLWhere || SQLVATDate1 || SQLDueDate || SQLAccDate;
for EXECUTE STATEMENT SQLStat
into :ID_ID, :ID_DueDate, :ID_VATDate, :II_LocaLAmount, :Firm_ID
do begin
LAmount = 0;
if (WithoutDue = 'N') then begin
select count(LocaLAmount) from CalcAmountsByLimits('03', :ID_ID, :MainDate, :II_LocaLAmount, :DueTermLimitSelID, :ID_DueDate)
where (LocaLAmount <> 0)
into :LAmount;
end
if ((WithoutDue = 'A') or (LAmount <> 0)) then
for select DT_ID, LocaLAmount, DT_DueTermLimit from CalcAmountsByLimits('03', :ID_ID, :MainDate, :II_LocaLAmount, :DueTermLimitSelID, :ID_DueDate)
into :DT_ID, :LocaLAmount, :DT_DueTermLimit
do begin
Suspend;
end
end
/* zalohove listy vydane */
if (IssuedDInvoicesUsed = 'A') then begin
SQLStat = 'select II.ID, II.DueDate$Date, II.DocDate$Date as VATDate$Date, II.LocaLAmount, II.Firm_ID from IssuedDInvoices II where ' || SQLWhere || SQLVATDate2 || SQLDueDate;
for EXECUTE STATEMENT SQLStat
into :ID_ID, :ID_DueDate, :ID_VATDate, :II_LocaLAmount, :Firm_ID
do begin
LAmount = 0;
if (WithoutDue = 'N') then begin
select count(LocaLAmount) from CalcAmountsByLimits('10', :ID_ID, :MainDate, :II_LocaLAmount, :DueTermLimitSelID, :ID_DueDate)
where (LocaLAmount <> 0)
into :LAmount;
end
if ((WithoutDue = 'A') or (LAmount <> 0)) then
for select DT_ID, LocaLAmount, DT_DueTermLimit from CalcAmountsByLimits('10', :ID_ID, :MainDate, :II_LocaLAmount, :DueTermLimitSelID, :ID_DueDate)
into :DT_ID, :LocaLAmount, :DT_DueTermLimit
do begin
Suspend;
end
end
end
/* Dobropisy danovych zalohovych listu prijatych */
if (VATCreditNotesUsed = 'A') then begin
SQLStat = 'select II.ID, II.DocDate$Date, II.VATDate$Date, II.LocaLAmount, II.Firm_ID from VATReceivedDCreditNotes II where ' || SQLWhere || SQLVATDate1 || SQLAccDate;
for
EXECUTE STATEMENT SQLStat
into :ID_ID, :ID_DueDate, :ID_VATDate, :II_LocaLAmount, :Firm_ID
do begin
LAmount = 0;
if (WithoutDue = 'N') then begin
select count(LocaLAmount) from CalcAmountsByLimits('65', :ID_ID, :MainDate, :II_LocaLAmount, :DueTermLimitSelID, :ID_DueDate)
where (LocaLAmount <> 0)
into :LAmount;
end
if ((WithoutDue = 'A') or (LAmount <> 0)) then
for select DT_ID, LocaLAmount, DT_DueTermLimit from CalcAmountsByLimits('65', :ID_ID, :MainDate, :II_LocaLAmount, :DueTermLimitSelID, :ID_DueDate)
into :DT_ID, :LocaLAmount, :DT_DueTermLimit
do begin
Suspend;
end
end
end
end;