begin
/*
kvuli rychlosti prisla rovnez zmena neresit to v cyklu pres vsehny sklady, ale pouzit
jeden cyklus (db engine totiz v kazdem cyklu pracuje s tim samym bagem dat)
pokud je ale mUseClosing nastavene na 1, pouzije se stary zpusob pruchodu pres jednotlive
sklady - tam to jinak poresit nejde !!!
Bacha - vykonani pruchodu je pak stejne v obou vetvich, pokud se tam neco bude v budoucnu upravovat
*/
--FB ma tohle v jadru az od 2.1
--mCrLf = ASCII_CHAR(13) || ASCII_CHAR(10);
mCrLf = '
';
/* kvuli rychlosti prepsano do dynamickeho SQL */
--podminka na StoreCard_ID do sekce FROM
mSQLCondStoreCardJoin = '';
if (:AStoreCardSelID <> '') then begin
mSQLCondStoreCardJoin = ' join Seldat SLDSC on SLDSC.SEL_ID = ''' || :AStoreCardSelID || ''' and SLDSC.OBJ_ID = A.StoreCard_ID';
end
--obecne podminky do sekce WHERE(zamerne odcloneno pro optimalizator - problemy v 1.5 i 2 FB,
--nicmene v pripade potreby postelovat pro konkretne zadana omezeni - napr jen strediska, tak pustit index ...)
mSQLWhere = '';
mSQLWhereForClosing = '';
mSQLWhereForStoreAndCard = '';
--
--podminka na Store_ID
if (:AStoreSelID <> '') then begin
Select AResult from SYS$CONCATSELECT('OBJ_ID', 'SELDAT where SEL_ID = ''' || :AStoreSelID || '''') into :mSQLConc;
mSQLWhereForStoreAndCard = ' and A.Store_ID || '''' in (' || :mSQLConc || ')';
mSQLWhereForClosing = ' where B.Store_ID || '''' in (' || :mSQLConc || ')';
end
--AStore_ID (filtr za prave 1 sklad)
if (:AStore_ID <> '') then begin
mSQLWhereForStoreAndCard = mSQLWhereForStoreAndCard || ' and A.Store_ID = ''' || :AStore_ID || '''';
if (mSQLWhereForClosing = '') then
mSQLWhereForClosing = ' where B.Store_ID = ''' || :AStore_ID || '''';
else
mSQLWhereForClosing = mSQLWhereForClosing || ' and B.Store_ID = ''' || :AStore_ID || '''';
end
--AStoreCard_ID (filtr za prave 1 skl. kartu)
if (:AStoreCard_ID <> '') then begin
mSQLWhereForStoreAndCard = mSQLWhereForStoreAndCard || ' and A.StoreCard_ID = ''' || :AStoreCard_ID || '''';
if (mSQLWhereForClosing = '') then
mSQLWhereForClosing = ' where A.StoreCard_ID = ''' || :AStoreCard_ID || '''';
else
mSQLWhereForClosing = mSQLWhereForClosing || ' and A.StoreCard_ID = ''' || :AStoreCard_ID || '''';
end
--Firm_ID
if (:AFirmSelID <> '') then begin
mSQLWhere = mSQLWhere || mCrLf ||' and ( ';
--regulerni ID
Select AResult from SYS$CONCATSELECT('OBJ_ID', 'SELDAT where SEL_ID = ''' || :AFirmSelID || '''') into :mSQLConc;
mSQLWhere = mSQLWhere || mCrLf ||' SD.Firm_ID || '''' in (' || :mSQLConc || ')';
--ID predchudcu
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
--Division_ID
if (:ADivisionSelID <> '') then begin
-- Test, zda ve vyberu jsou uplne vsechna strediska, ktera nejsou skryta
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
mSQLWhere = mSQLWhere || mCrLf ||' and ( ';
--regulerni ID
Select AResult from SYS$CONCATSELECT('OBJ_ID', 'SELDAT where SEL_ID = ''' || :ADivisionSelID || '''') into :mSQLConc;
mSQLWhere = mSQLWhere || mCrLf ||' A.Division_ID ||'''' in (' || :mSQLConc || ')';
--podrizene strediska
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 A.Division_ID ||'''' in (' || :mSQLConc || ')';
end
mSQLWhere = mSQLWhere || mCrLf ||' ) ';
mAreDivisionsRestricted = 'A';
end
else
begin
mAreDivisionsRestricted = 'N';
end
end
--BusOrder_ID
if (:ABusOrderSelID <> '') then begin
mSQLWhere = mSQLWhere || mCrLf ||' and ( ';
--regulerni ID
Select AResult from SYS$CONCATSELECT('OBJ_ID', 'SELDAT where SEL_ID = ''' || :ABusOrderSelID || '''') into :mSQLConc;
mSQLWhere = mSQLWhere || mCrLf ||' A.BusOrder_ID ||'''' in (' || :mSQLConc || ')';
--podrizene strediska
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 A.BusOrder_ID ||'''' in (' || :mSQLConc || ')';
end
mSQLWhere = mSQLWhere || mCrLf ||' ) ';
end
--BusTransaction_ID
if (:ABusTransactionSelID <> '') then begin
mSQLWhere = mSQLWhere || mCrLf ||' and ( ';
--regulerni ID
Select AResult from SYS$CONCATSELECT('OBJ_ID', 'SELDAT where SEL_ID = ''' || :ABusTransactionSelID || '''') into :mSQLConc;
mSQLWhere = mSQLWhere || mCrLf ||' A.BusTransaction_ID ||'''' in (' || :mSQLConc || ')';
--podrizene strediska
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 A.BusTransaction_ID ||'''' in (' || :mSQLConc || ')';
end
mSQLWhere = mSQLWhere || mCrLf ||' ) ';
end
--BusProject_ID
if (:ABusProjectSelID <> '') then begin
mSQLWhere = mSQLWhere || mCrLf ||' and ( ';
--regulerni ID
Select AResult from SYS$CONCATSELECT('OBJ_ID', 'SELDAT where SEL_ID = ''' || :ABusProjectSelID || '''') into :mSQLConc;
mSQLWhere = mSQLWhere || mCrLf ||' A.BusProject_ID ||'''' in (' || :mSQLConc || ')';
--podrizene projekty
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 A.BusProject_ID ||'''' in (' || :mSQLConc || ')';
end
mSQLWhere = mSQLWhere || mCrLf ||' ) ';
end
--pomocne promenne ovlivnujici podobu vysledneho dotazu (mAddDummyFields jsou do selectu pocatku, vyrovnaji polozky tak, aby to pasovalo do celeho unionu)
--pro AResultKind = 1 se rovnez pohyby negrupuji a dotaz vraci vic polozek, jinak serazenych
if (:AResultKind = 1) then begin
--pro StoreFlowsWIthRealization (pocatky stejne, ale pohyby se negrupuji a vraci vic polozek)
mAddFields = 'A.Store_ID, A.StoreCard_ID, A.Quantity, A.LocalTAmount, CAST(A.FlowType as Char(2)), case when A.ClosingOrder > 0 then 0 else 1 end,' ||
'SD.Firm_ID, A.ID, A.Parent_ID, A.Division_ID, A.BusOrder_ID, A.BusTransaction_ID, A.BusProject_ID, SD.MasterDocument_ID, SD.MasterDocClsID';
mAddDummyFields = ', '''', '''', '''', '''', '''', '''', '''', null, ''00000000000000000000000000''';
mTotalOrderBy = '2,1,7';
end else begin
--pro StoreFlowsWithDates - vrati stejny dotaz jako pred upravou
mAddFields = 'A.Store_ID, A.StoreCard_ID, sum(A.Quantity), sum(A.LocalTAmount), CAST(A.FlowType as Char(2)), sum(case when A.ClosingOrder > 0 then 0 else 1 end)';
mAddDummyFields = ' ';
mTotalOrderBy = '1,2';
end
--sestaveni kostry SQL prikazu pro natahnuti dat (preneseni ze StoreFlowsByDates2 a StoreFlowsByDates3)
SQ = '';
if (AFirmSelID <> '' or mAreDivisionsRestricted = 'A' or
ABusOrderSelID <> '' or ABusTransactionSelID <> '' or ABusProjectSelID <> '') then begin
mUseClosing = 0;
end else begin
if (:ADateFrom = -1) then begin
-- Bude se pocitat od posledni uzaverky za obdobi vzhledem k ADateTo
mUseClosing = 1;
mDateForClosing = ADateTo;
end else begin
-- Pocatecni stavy se napocitaj od posledni uzaverky za obdobi vzhledem k ADateFrom, zbyle pohyby se dopocitaji
mUseClosing = 2;
mDateForClosing = ADateFrom;
end
end
if (:mUseClosing = 1 or :mUseClosing = 2) then begin
--Ted po novu se to dela tak, ze se ke kazde skladove karte dohleda jeji posledni zaverka ZA OBDOBI vzhledem k zadanemu datumu pro sklad
--Pokud tam karta neni, tak mela v dobe uzaverky akorat nulove mnozstvi i castku (pro uzaverky za obdobi neexistujuji neocenene pohyby)
--stavy k posledni uzaverce skladu za obdobi vzhledem k datumu do
--skl. karty, ktere tam nejsou, vysly nulove - takove se do uzaverky skladu nezapisi
--pochopitelne tam nejsou ani skl. karty, ktere nemaji zadny pohyb
SQ = SQ || ' select ' || mCrLf;
SQ = SQ || ' B.Store_ID, A.StoreCard_ID, A.Quantity, A.LocalAmount, ''00'' as Ident, 0 ' || mCrLf;
SQ = SQ || mAddDummyFields || mCrLf;
SQ = SQ || ' from ' || mCrLf;
SQ = SQ || ' StoreClosings B ' || mCrLf;
SQ = SQ || ' join StoreClosings2 A on A.Parent_ID = B.ID ' || mCrLf;
SQ = SQ || mSQLCondStoreCardJoin || mCrLf;
SQ = SQ || ' join ' || mCrLf;
SQ = SQ || ' (select ' || mCrLf;
SQ = SQ || ' Max(SC.ClosingOrder) as MCO, SC.Store_ID ' || mCrLf;
SQ = SQ || ' from ' || mCrLf;
SQ = SQ || ' StoreClosings SC ' || mCrLf;
SQ = SQ || ' where ' || mCrLf;
SQ = SQ || ' SC.Period_ID is not null and ' || mCrLf;
SQ = SQ || ' SC.ClosingDate$DATE < ' || :mDateForClosing || mCrLf;
SQ = SQ || ' group by ' || mCrLf;
SQ = SQ || ' SC.Store_ID ' || mCrLf;
SQ = SQ || ' ) CO on CO.Store_ID = B.Store_ID ' || mCrLf;
SQ = SQ || ' and B.ClosingOrder = CO.MCO';
SQ = SQ || mSQLWhereForClosing || mCrLf;
SQ = SQ || 'union all' || mCrLf;
--pohyby od posledni uzaverky skladu za obdobi do datumu do
--pozor, tady se musi pripojit posledni uzaverka skladu za obdobi vzhledem k datumu do,
--a to za vsechny sklady, pokud takova uzaverka neni, vrati se uzaverka (ClosingOrder)
--jako -1, aby to pekne a nekomplikovane sedlo do podminky where + pohyby s ClosingOrder=0, tedy nezpracovane uzaverkou
SQ = SQ || 'select ' || mCrLf;
SQ = SQ || ' A.Store_ID, A.StoreCard_ID, ' || mCrLf;
if (:AResultKind = 0) then
SQ = SQ || ' sum(A.Quantity), sum(A.LocalTAmount), ' || mCrLf;
else
SQ = SQ || ' A.Quantity, A.LocalTAmount, ' || mCrLf;
SQ = SQ || ' case when A.FlowSign = 1 then ''AA'' else ''BB'' end, ' || mCrLf;
if (AResultKind = 0) then
SQ = SQ || ' Sum(case when A.ClosingOrder > 0 then 0 else 1 end)' || mCrLf;
else
SQ = SQ || ' case when A.ClosingOrder > 0 then 0 else 1 end' || mCrLf;
SQ = SQ || mAddDummyFields || mCrLf;
SQ = SQ || ' from ' || mCrLf;
SQ = SQ || ' StoreDocuments2 A ' || mCrLf;
SQ = SQ || ' join PMStates PMS on A.PMState_ID = PMS.ID ' || mCrLf;
SQ = SQ || ' join StoreCards SC on A.StoreCard_ID = SC.ID ' || mCrLf;
SQ = SQ || ' join StoreDocuments SD on A.Parent_ID = SD.ID ' || mCrLf;
SQ = SQ || mSQLCondStoreCardJoin || mCrLf;
SQ = SQ || ' join ' || mCrLf;
SQ = SQ || ' (select ' || mCrLf;
SQ = SQ || ' ST.ID as Store_ID, coalesce(CO.MCO, -1) as MCO ' || mCrLf;
SQ = SQ || ' from ' || mCrLf;
SQ = SQ || ' Stores ST ' || mCrLf;
SQ = SQ || ' left join ' || mCrLf;
SQ = SQ || ' (select ' || mCrLf;
SQ = SQ || ' Max(SC.ClosingOrder) as MCO, SC.Store_ID ' || mCrLf;
SQ = SQ || ' from ' || mCrLf;
SQ = SQ || ' StoreClosings SC ' || mCrLf;
SQ = SQ || ' where ' || mCrLf;
SQ = SQ || ' SC.Period_ID is not null and ' || mCrLf;
SQ = SQ || ' SC.ClosingDate$DATE < ' || :mDateForClosing || mCrLf;
SQ = SQ || ' group by ' || mCrLf;
SQ = SQ || ' SC.Store_ID ' || mCrLf;
SQ = SQ || ' ) CO on CO.Store_ID = ST.ID ' || mCrLf;
SQ = SQ || ' ) LastCO on LastCO.Store_ID = A.Store_ID ' || mCrLf;
SQ = SQ || ' where ' || mCrLf;
SQ = SQ || ' A.RowType = 3 ' || mCrLf;
SQ = SQ || ' and PMS.SystemState = 3 ' || mCrLf;
SQ = SQ || ' and SC.NonStockType = ''N'' ' || mCrLf;
SQ = SQ || ' and (A.ClosingOrder = 0 or A.ClosingOrder > LastCO.MCO) ' || mCrLf;
SQ = SQ || ' and SD.DocDate$DATE < ' || :mDateForClosing || mCrLf;
SQ = SQ || mSQLWhereForStoreAndCard || mCrLf;
if (:AResultKind = 0) then
SQ = SQ || 'group by A.Store_ID, A.StoreCard_ID, A.FlowSign' || mCrLf;
end
if (:mUseClosing = 0 or :mUseClosing = 2) then begin
--v teto vetvi se mUseClosing nepouziva, proto se pripoji podminka na sklad ke standartnim podminkam
mSQLWhere = mSQLWhere || mCrLf || mSQLWhereForStoreAndCard;
if (:mUseClosing = 0) then begin
SQ = SQ || 'select A.Store_ID, A.StoreCard_ID, ' ||
'sum(A.Quantity), sum(A.LocalTAmount), ' ||
'case when A.FlowSign = 1 then ''AA'' else ''BB'' end, ' ||
'sum(case when A.ClosingOrder > 0 then 0 else 1 end)' || mCrLf ||
mAddDummyFields || mCrLf ||
' from StoreDocuments2 A' || mCrLf ||
mSQLCondStoreCardJoin || mCrLf ||
' left join StoreDocuments SD on SD.ID=A.Parent_ID' || mCrLf ||
' join PMStates PMS on A.PMState_ID=PMS.ID ' || mCrLf ||
' join StoreCards SC on A.StoreCard_ID=SC.ID ' || mCrLf ||
' where SD.DocDate$DATE < ' || :ADateFrom || ' and A.RowType = 3 ' || mCrLf ||
' and PMS.SystemState = 3 and SC.NonStockType = ''N'' ' || mCrLf;
SQ = SQ || mSQLWhere;
SQ = SQ || ' group by A.Store_ID, A.StoreCard_ID, A.FlowSign' || mCrLf;
end
SQ = SQ || ' union all ' || mCrLf;
--Teprve pro pohyby zbozi to budu rozpadavat na dilci pripady protoze, pro pocatky a pocatecni obraty to nemuselo byt,
--tam nas rozpady po typech dokladu nezajimaji
SQ = SQ || 'select ' || mAddFields || mCrLf ||
' from StoreDocuments2 A' || mCrLf ||
mSQLCondStoreCardJoin || mCrLf ||
' left join StoreDocuments SD on SD.ID=A.Parent_ID' || mCrLf ||
' join PMStates PMS on A.PMState_ID=PMS.ID ' || mCrLf ||
' join StoreCards SC on A.StoreCard_ID=SC.ID ' || mCrLf ||
' where SD.DocDate$DATE < ' || :ADateTo || ' and SD.DocDate$DATE >= ' || :ADateFrom || mCrLf ||
' and A.RowType = 3' || mCrLf ||
' and PMS.SystemState = 3 and SC.NonStockType = ''N'' ' || mCrLf;
SQ = SQ || mSQLWhere || mCrLf;
if (:AResultKind = 0) then
SQ = SQ || ' group by A.Store_ID, A.StoreCard_ID, A.FlowType' || mCrLf;
end
SQ = SQ || ' order by ' || mTotalOrderBy;
suspend;
end