begin
mCrLf = '
';
mSQLJoin = '';
mSQLWhere = '';
-- AStoreBatchSelID podmínka na vybrané skladové šarže/sér.č.
if (:AStoreBatchSelID <> '') then
begin
mSQLJoin = mSQLJoin || ' join Seldat SLDSB on SLDSB.SEL_ID = ''' || :AStoreBatchSelID || ''' and SLDSB.OBJ_ID = A.StoreBatch_ID';
end
-- AStoreBatch_IDrd_ID podmínka za jednu skladovou šarži/sér.č
if (:AStoreBatch_ID <> '') then
begin
if (mSQLWhere <> '') then
mSQLWhere = mSQLWhere || ' and ';
mSQLWhere = mSQLWhere || 'A.StoreBatch_ID = ''' || :AStoreBatch_ID || '''';
end
-- AStoreCardSelID podmínka na vybrané skladové karty
if (:AStoreCardSelID <> '') then
begin
if (mSQLJoin <> '') then
mSQLJoin = mSQLJoin || mCrLf;
mSQLJoin = mSQLJoin || ' join Seldat SLDSC on SLDSC.SEL_ID = ''' || :AStoreCardSelID || ''' and SLDSC.OBJ_ID = SD2.StoreCard_ID';
end
-- AStoreCard_ID podmínka za jednu skladovou kartu
if (:AStoreCard_ID <> '') then
begin
if (mSQLWhere <> '') then
mSQLWhere = mSQLWhere || ' and ';
mSQLWhere = mSQLWhere || 'SD2.StoreCard_ID = ''' || :AStoreCard_ID || '''';
end
-- AStoreSelID podminka na vybrané sklady
if (:AStoreSelID <> '') then
begin
Select AResult from SYS$CONCATSELECT('OBJ_ID', 'SELDAT where SEL_ID = ''' || :AStoreSelID || '''') into :mSQLConc;
if (mSQLWhere <> '') then
mSQLWhere = mSQLWhere || ' and ';
mSQLWhere = mSQLWhere || 'SD2.Store_ID || '''' in (' || :mSQLConc || ')';
end
-- AStore_ID podmínka za jeden vybraný sklad
if (:AStore_ID <> '') then
begin
if (mSQLWhere <> '') then
mSQLWhere = mSQLWhere || ' and ';
mSQLWhere = mSQLWhere || 'SD2.Store_ID = ''' || :AStore_ID || '''';
end
-- AFirmSelID podmínka za vybranou firmu a její předchůdce
if (:AFirmSelID <> '') then
begin
if (mSQLWhere <> '') then
mSQLWhere = mSQLWhere || ' and ';
mSQLWhere = mSQLWhere || mCrLf || '(';
-- vybraná ID
Select AResult from SYS$CONCATSELECT('OBJ_ID', 'SELDAT where SEL_ID = ''' || :AFirmSelID || '''') into :mSQLConc;
mSQLWhere = mSQLWhere || mCrLf ||' SD.Firm_ID || '''' in (' || :mSQLConc || ')';
-- ID předchůdců
Select AResult from SYS$CONCATSELECT('distinct F.ID', 'SELDAT SLD join Firms F on F.Firm_ID = SLD.OBJ_ID where SLD.SEL_ID = ''' || :AFirmSelID || '''') into :mSQLConc;
mSQLWhere = mSQLWhere || mCrLf ||' or SD.Firm_ID || '''' in (' || :mSQLConc || ')';
mSQLWhere = mSQLWhere || mCrLf ||')';
end
-- ADivisionSelID střediska
if (:ADivisionSelID <> '') then
begin
-- Pokud jsou ve výběru všechna neskrytá střediska nebudeme vůbec omezovat za střediska
Select
first 1 D.ID
From
Divisions D
left join SELDAT SD on (SEL_ID = :ADivisionSelID) and (D.ID = SD.Obj_ID)
Where
D.Hidden = 'N' and
SD.Sel_ID is null
Into
:mNotSelectedDivisionID;
if (IB_ISNOTNULL(mNotSelectedDivisionID) = 'A') then
begin
if (mSQLWhere <> '') then
mSQLWhere = mSQLWhere || ' and ';
mSQLWhere = mSQLWhere || mCrLf || '(';
-- vybraná ID
Select AResult from SYS$CONCATSELECT('OBJ_ID', 'SELDAT where SEL_ID = ''' || :ADivisionSelID || '''') into :mSQLConc;
mSQLWhere = mSQLWhere || mCrLf ||' SD2.Division_ID ||'''' in (' || :mSQLConc || ')';
-- podřízené ID
if (:ADivisionsWithChilds in ('1', 'A')) then
begin
Select AResult from SYS$CONCATSELECT('distinct SD2.ID', 'SELDAT SLD join SYS$Divisions2 SD2 on SD2.Superior_ID = SLD.OBJ_ID where SLD.SEL_ID = ''' || :ADivisionSelID || '''') into :mSQLConc;
mSQLWhere = mSQLWhere || mCrLf ||' or SD2.Division_ID ||'''' in (' || :mSQLConc || ')';
end
mSQLWhere = mSQLWhere || mCrLf ||' ) ';
end
end
-- ABusOrderSelID
if (:ABusOrderSelID <> '') then
begin
if (mSQLWhere <> '') then
mSQLWhere = mSQLWhere || ' and ';
mSQLWhere = mSQLWhere || mCrLf || '(';
-- vybraná ID
Select AResult from SYS$CONCATSELECT('OBJ_ID', 'SELDAT where SEL_ID = ''' || :ABusOrderSelID || '''') into :mSQLConc;
mSQLWhere = mSQLWhere || mCrLf ||' SD2.BusOrder_ID ||'''' in (' || :mSQLConc || ')';
-- podřízené ID
if (:ABusOrdersWithChilds in ('1', 'A')) then
begin
Select AResult from SYS$CONCATSELECT('distinct SB2.ID', 'SELDAT SLD join SYS$BusOrders2 SB2 on SB2.Superior_ID = SLD.OBJ_ID where SLD.SEL_ID = ''' || :ABusOrderSelID || '''') into :mSQLConc;
mSQLWhere = mSQLWhere || mCrLf ||' or SD2.BusOrder_ID ||'''' in (' || :mSQLConc || ')';
end
mSQLWhere = mSQLWhere || mCrLf ||' ) ';
end
--BusTransaction_ID
if (:ABusTransactionSelID <> '') then
begin
mSQLWhere = mSQLWhere || mCrLf ||' and ( ';
-- vybraná ID
Select AResult from SYS$CONCATSELECT('OBJ_ID', 'SELDAT where SEL_ID = ''' || :ABusTransactionSelID || '''') into :mSQLConc;
mSQLWhere = mSQLWhere || mCrLf ||' SD2.BusTransaction_ID ||'''' in (' || :mSQLConc || ')';
-- podřízené ID
if (:ABusTransactionsWithChilds in ('1', 'A')) then
begin
Select AResult from SYS$CONCATSELECT('distinct SB2.ID', 'SELDAT SLD join SYS$BusTransactions2 SB2 on SB2.Superior_ID = SLD.OBJ_ID where SLD.SEL_ID = ''' || :ABusTransactionSelID || '''') into :mSQLConc;
mSQLWhere = mSQLWhere || mCrLf ||' or SD2.BusTransaction_ID ||'''' in (' || :mSQLConc || ')';
end
mSQLWhere = mSQLWhere || mCrLf ||' ) ';
end
--BusProject_ID projekty
if (:ABusProjectSelID <> '') then
begin
if (mSQLWhere <> '') then
mSQLWhere = mSQLWhere || ' and ';
mSQLWhere = mSQLWhere || mCrLf || '(';
-- vybraná ID
Select AResult from SYS$CONCATSELECT('OBJ_ID', 'SELDAT where SEL_ID = ''' || :ABusProjectSelID || '''') into :mSQLConc;
mSQLWhere = mSQLWhere || mCrLf ||' SD2.BusProject_ID ||'''' in (' || :mSQLConc || ')';
-- podřízené ID
if (:ABusProjectsWithChilds in ('1', 'A')) then
begin
Select AResult from SYS$CONCATSELECT('distinct SB2.ID', 'SELDAT SLD join SYS$BusProjects2 SB2 on SB2.Superior_ID = SLD.OBJ_ID where SLD.SEL_ID = ''' || :ABusProjectSelID || '''') into :mSQLConc;
mSQLWhere = mSQLWhere || mCrLf ||' or SD2.BusProject_ID ||'''' in (' || :mSQLConc || ')';
end
mSQLWhere = mSQLWhere || mCrLf ||' ) ';
end
-- Výsledný dotaz
SQ =
'SELECT' || mCrLf ||
' SD2.Store_ID, SD2.StoreCard_ID, A.StoreBatch_ID,' || mCrLf ||
' CASE WHEN SD.DocDate$DATE >=' || :ADateFrom || ' THEN SD2.FlowType ELSE ' || mCrLf ||
' CASE WHEN SD2.FlowSign = 1 THEN ''AA'' ELSE ''BB'' END END AS DocumentType,' || mCrLf ||
' SUM(A.Quantity) AS Quantity' || mCrLf ||
'FROM StoreDocuments2 SD2' || mCrLf ||
'JOIN PMStates PMS ON SD2.PMState_ID = PMS.ID' || mCrLf ||
'JOIN StoreCards SC ON SD2.StoreCard_ID = SC.ID' || mCrLf ||
'JOIN DocRowBatches A ON A.Parent_ID = SD2.ID' || mCrLf;
if (mSQLJoin <> '') then
SQ = SQ || mSQLJoin || mCrLf;
SQ = SQ ||
'LEFT JOIN StoreDocuments SD on SD.ID = SD2.Parent_ID' || mCrLf ||
'WHERE SD.DocDate$DATE < ' || :ADateTo || ' and SD2.RowType = 3' || mCrLf ||
' and PMS.SystemState = 3 and SC.NonStockType = ''N'' ' || mCrLf;
if (mSQLWhere <> '') then
SQ = SQ || 'and ' || mSQLWhere || mCrLf;
SQ = SQ ||
'GROUP BY SD2.Store_ID, SD2.StoreCard_ID, A.StoreBatch_ID, DocumentType' || mCrLf ||
'ORDER BY 1, 2, 3';
--
suspend;
end