begin
--FB ma tohle v jadru az od 2.1
--mCrLf = ASCII_CHAR(13) || ASCII_CHAR(10);
mCrLf = '';
--kostra dotazu do POSu
execute statement 'SELECT count(*) FROM RDB$RELATIONS WHERE UPPER(RDB$RELATION_NAME) = UPPER(''possummareddocuments5'')' into :mPOSExist;
if (:mPOSExist = 1) then begin
mSQLPos = 'select ' || mCrLf ||
'sum(psdR.LocalTAmount), ' || mCrLf ||
'sum(psdR.LocalTAmountWithoutVAT), ' || mCrLf ||
'sum(psdR.Quantity) ' || mCrLf ||
'from ' || mCrLf ||
'possummareddocuments5 psd5 ' || mCrLf ||
' join StoreDocuments SD on SD.ID = psd5.Document_ID ' || mCrLf ||
' join :RowTable psdR on psdR.Parent_ID = psd5.Parent_ID ' || mCrLf ||
--tyhle parametry jsou predane z jedne skladove radky
' and psd5.DocumentType = :DocumentType and psd5.Document_ID = :Document_ID' || mCrLf ||
' and psdR.Store_ID = :Store_ID' || mCrLf ||
' and psdR.StoreCard_ID = :StoreCard_ID' || mCrLf ||
' and :Col_Firm_ID = :Firm_ID' || mCrLf ||
' and psdR.Division_ID = :Division_ID' || mCrLf ||
' and psdR.BusOrder_ID :BusOrder_ID' || mCrLf ||
' and psdR.BusTransaction_ID :BusTransaction_ID' || mCrLf ||
' and psdR.BusProject_ID :BusProject_ID';
end
--
LocalAmount = 0;
Quantity = 0;
BegLocalAmount = 0;
BegQuantity = 0;
UnClosedRows = 0;
QuantityPurchase = 0;
LocalAmountPurchase = 0;
QuantityStoreSell = 0;
LocalAmountStoreSell = 0;
LocalAmountSell = 0;
LocalAmountWithoutVATSell = 0;
LocalAmountMatDistr = 0;
LocalAmountInvShort = 0;
LocalAmountInvOver = 0;
LocalAmountProdRecept = 0;
--vyznam to ma v nespustini suspendu pri prvnim pruchodu
Store_ID = NULL;
StoreCard_ID = NULL;
Firm_ID = NULL;
mLastParent_id = '';
--
--natahnem SQL pohybu (je to razene StoreCard_ID, Store_ID, Firm_ID)
Select SQ from StoreFlowsByDatesSQLQuery(:DateFrom, :DateTo, :StoreSelID, :StoreCardSelID, :FirmSelID, :DivisionSelID,
:BusOrderSelID, :BusTransactionSelID, :BusProjectSelID,
:ADivisionsWithChilds, :ABusOrdersWithChilds,
:ABusTransactionsWithChilds, :ABusProjectsWithChilds,
'', '', 1) into :mSQL;
--a dotaz probehneme a zpracujeme
--EXCEPTION ERRPERIODS mSQL; --pro ladeni
mRecordsExist = 0;
for EXECUTE STATEMENT mSQL
into mStore_ID, mStoreCard_ID, mQuantity, mLocalAmount, DocType, mUnClosedRows,
mFirm_ID, mID, mParent_ID, mDivision_ID, mBusOrder_ID, mBusTransaction_ID, mBusProject_ID,
mMasterdocument_ID, mMasterDocClsID
do begin
mRecordsExist = 1;
IF (mQuantity IS NULL) THEN
mQuantity = 0;
IF (mLocalAmount IS NULL) THEN
mLocalAmount = 0;
if ((mStoreCard_ID <> StoreCard_ID) or (mStore_ID <> Store_ID) or (mFirm_ID <> Firm_ID)) then begin
suspend;
BegLocalAmount = 0;
BegQuantity = 0;
Quantity = 0;
LocalAmount = 0;
UnClosedRows = 0;
QuantityPurchase = 0;
LocalAmountPurchase = 0;
QuantityStoreSell = 0;
LocalAmountStoreSell = 0;
LocalAmountSell = 0;
LocalAmountWithoutVATSell = 0;
LocalAmountMatDistr = 0;
LocalAmountInvShort = 0;
LocalAmountInvOver = 0;
LocalAmountProdRecept = 0;
end
-- predpripravime dotaz pro hodnoty z POSu
if (
(:mPOSExist = 1) and
((:DocType = '20') or (:DocType = '21') or (:DocType = '23') or (:DocType = '25') or (:DocType = '26')) and
((ib_isnotnull(mMasterDocument_ID) = 'A') or (:DocType = '25'))
) then begin
mLastParent_id = mParent_id;
mSQLPosRun = mSQLPos;
if ((:DocType = '21') or (:DocType = '26')) then begin
mSQLPosRun = ib_string_replace(mSQLPosRun, ':RowTable', 'POSSummaredDocuments2');
iPOSReturn = 1;
end else begin
mSQLPosRun = ib_string_replace(mSQLPosRun, ':RowTable', 'POSSummaredDocuments3');
iPOSReturn = -1;
end
mSQLPosRun = ib_string_replace(mSQLPosRun, ':DocumentType', '''' || :DocType || '''');
mSQLPosRun = ib_string_replace(mSQLPosRun, ':Document_ID', '''' || :mParent_ID || '''');
mSQLPosRun = ib_string_replace(mSQLPosRun, ':Store_ID', '''' || :mStore_ID || '''');
mSQLPosRun = ib_string_replace(mSQLPosRun, ':StoreCard_ID', '''' || :mStoreCard_ID || '''');
if ((:DocType = '25') or (:DocType = '26')) then begin
mSQLPosRun = ib_string_replace(mSQLPosRun, ':Col_Firm_ID', '1');
mSQLPosRun = ib_string_replace(mSQLPosRun, ':Firm_ID', '1');
end else begin
mSQLPosRun = ib_string_replace(mSQLPosRun, ':Col_Firm_ID', 'SD.Firm_ID');
mSQLPosRun = ib_string_replace(mSQLPosRun, ':Firm_ID', '''' || :mFirm_ID || '''');
end
mSQLPosRun = ib_string_replace(mSQLPosRun, ':Division_ID', '''' || :mDivision_ID || '''');
if ((:mBusOrder_ID is null) or (:mBusOrder_ID = '0000000000')) then
mSQLPosRun = ib_string_replace(mSQLPosRun, ':BusOrder_ID', 'is null');
else
mSQLPosRun = ib_string_replace(mSQLPosRun, ':BusOrder_ID', '=''' || :mBusOrder_ID || '''');
if ((:mBusTransaction_ID is null) or (:mBusTransaction_ID = '0000000000')) then
mSQLPosRun = ib_string_replace(mSQLPosRun, ':BusTransaction_ID', 'is null');
else
mSQLPosRun = ib_string_replace(mSQLPosRun, ':BusTransaction_ID', '=''' || :mBusTransaction_ID || '''');
if ((:mBusProject_ID is null) or (:mBusProject_ID = '0000000000')) then
mSQLPosRun = ib_string_replace(mSQLPosRun, ':BusProject_ID', 'is null');
else
mSQLPosRun = ib_string_replace(mSQLPosRun, ':BusProject_ID', '=''' || :mBusProject_ID || '''');
execute statement :mSQLPosRun into mLocalAmountSell, mLocalAmountWithoutVATSell, mQuantitySell;
-- Prepocitani ceny ze souhrnne uctenky podle mnozstvi uvedenem na dodacim liste
if (((mQuantitySell * iPOSReturn) <> mQuantity) and (mQuantitySell <> 0)) then begin
mLocalAmountSell = (coalesce(mLocalAmountSell, 0) / (mQuantitySell * iPOSReturn)) ;
mLocalAmountSell = mLocalAmountSell * mQuantity;
mLocalAmountWithoutVATSell = (coalesce(mLocalAmountWithoutVATSell, 0) / (mQuantitySell * iPOSReturn));
mLocalAmountWithoutVATSell = mLocalAmountWithoutVATSell * mQuantity;
end
if (DocType = '20') then begin
if ((mLocalAmountSell is not null) and (mLocalAmountWithoutVATSell is not null)) then begin
--naslo se neco v POSU - uvazuju to jako vratku
LocalAmountSell = LocalAmountSell + coalesce(mLocalAmountSell, 0);
LocalAmountWithoutVATSell = LocalAmountWithoutVATSell + coalesce(mLocalAmountWithoutVATSell, 0);
LocalAmountStoreSell = LocalAmountStoreSell - mLocalAmount;
QuantityStoreSell = QuantityStoreSell - mQuantity;
end
else begin
--Prijemka, ktena neni v POS se povazuje za Nakup do skladu.
LocalAmountPurchase = LocalAmountPurchase + mLocalAmount;
QuantityPurchase = QuantityPurchase + mQuantity;
end
end
else begin
LocalAmountSell = LocalAmountSell + coalesce(mLocalAmountSell, 0);
LocalAmountWithoutVATSell = LocalAmountWithoutVATSell + coalesce(mLocalAmountWithoutVATSell, 0);
end
end else if
(
(:mPOSExist = 1) and
(DocType = '20') and
(ib_isnotnull(mMasterDocument_ID) = 'N')
)
then begin
--Prijemka, ktera neni v POS se povazuje za Nakup do skladu
LocalAmountPurchase = LocalAmountPurchase + mLocalAmount;
QuantityPurchase = QuantityPurchase + mQuantity;
end
--
-- zda se jedna o vydeje nebo prijmy
if (DocType = 'BB' or DocType = '22' or DocType = '21' or DocType = '26' or DocType = '27' or DocType = '30' or DocType = '36' or DocType = '38') then
i = -1;
else
i = 1;
/* zda se jedna o pocatky nebo obraty */
if (DocType = '00' or DocType = 'AA' or DocType = 'BB') then begin
BegLocalAmount = BegLocalAmount + i * mLocalAmount;
BegQuantity = BegQuantity + i * mQuantity;
end
else begin
LocalAmount = LocalAmount + i * mLocalAmount;
Quantity = Quantity + i * mQuantity;
/* obrat nakupu na sklad */
if (DocType = '22' or DocType = '24' or DocType = '28' or DocType = '30') then begin
LocalAmountPurchase = LocalAmountPurchase + i * mLocalAmount;
QuantityPurchase = QuantityPurchase + i * mQuantity;
-- rozsireni o samotné PHV
if (DocType = '28') then begin
LocalAmountProdRecept = LocalAmountProdRecept + mLocalAmount;
end
/* obrat prodeje ze skladu */
end else if (DocType = '21') then begin
/* vzhledem ke skladovym dokladum */
LocalAmountStoreSell = LocalAmountStoreSell + mLocalAmount;
QuantityStoreSell = QuantityStoreSell + mQuantity;
/* obrat prodeje vzhledem k vystupnim dokladum */
--FV
select
Sum(II2.LocalTAmount), Sum(II2.LocalTAmountWithoutVAT)
from
IssuedInvoices2 II2
where
II2.ProvideRow_ID = :mID
into
mLocalAmountSell, mLocalAmountWithoutVATSell;
LocalAmountSell = LocalAmountSell + coalesce(mLocalAmountSell, 0);
LocalAmountWithoutVATSell = LocalAmountWithoutVATSell + coalesce(mLocalAmountWithoutVATSell, 0);
--PP
select
Sum(CR2.LocalTAmount), Sum(CR2.LocalTAmountWithoutVAT)
from
CashReceived2 CR2
where
CR2.ProvideRow_ID = :mID
into
mLocalAmountSell, mLocalAmountWithoutVATSell;
LocalAmountSell = LocalAmountSell + coalesce(mLocalAmountSell, 0);
LocalAmountWithoutVATSell = LocalAmountWithoutVATSell + coalesce(mLocalAmountWithoutVATSell, 0);
/* obrat prodeje na sklad - vraceni zbozi */
end else if (DocType = '23') then begin
LocalAmountStoreSell = LocalAmountStoreSell - mLocalAmount;
QuantityStoreSell = QuantityStoreSell - mQuantity;
/* vzhledem k prodejnim dokladum - nascitam je z dokladu pripojenych ke skl. dokladu*/
--DV
select
Sum(ICN2.LocalTAmount), Sum(ICN2.LocalTAmountWithoutVAT)
from
IssuedCreditNotes2 ICN2
where
ICN2.ProvideRow_ID = :mID
into
mLocalAmountSell, mLocalAmountWithoutVATSell;
LocalAmountSell = LocalAmountSell - coalesce(mLocalAmountSell, 0);
LocalAmountWithoutVATSell = LocalAmountWithoutVATSell - coalesce(mLocalAmountWithoutVATSell, 0);
--vraceni PP
select
Sum(RCR2.LocalTAmount), Sum(RCR2.LocalTAmountWithoutVAT)
from
RefundedCashReceived2 RCR2
where
RCR2.ProvideRow_ID = :mID
into
mLocalAmountSell, mLocalAmountWithoutVATSell;
LocalAmountSell = LocalAmountSell - coalesce(mLocalAmountSell, 0);
LocalAmountWithoutVATSell = LocalAmountWithoutVATSell - coalesce(mLocalAmountWithoutVATSell, 0);
-- prijemka - ta muze byt normalni nebo z posu fungovat stejne jako vratka
end else if (DocType = '20') then begin
if (:mPOSExist = 0) then begin
-- Nejedna se o POS
LocalAmountPurchase = LocalAmountPurchase + mLocalAmount;
QuantityPurchase = QuantityPurchase + mQuantity;
end
end else if (DocType = '27') then begin
LocalAmountMatDistr = LocalAmountMatDistr + mLocalAmount;
end else if (DocType = '25') then begin
LocalAmountInvOver = LocalAmountInvOver + mLocalAmount;
end else if (DocType = '26') then begin
LocalAmountInvShort = LocalAmountInvShort + mLocalAmount;
end
end
Store_ID = mStore_ID;
StoreCard_ID = mStoreCard_ID;
Firm_ID = mFirm_ID;
UnCLosedRows = UnCLosedRows + mUnClosedRows;
end
--
if (mRecordsExist = 1) then begin
suspend;
end
end