begin
Taxableincome = 0;
Taxdeductableexpense = 0;
Depreciationwriteoff = 0;
Nontaxableincomes = 0;
Nontaxdeductableexpense = 0;
Outstandinginvoices = 0;
Liabilitiesinvoices = 0;
Throughitem = 0;
StoreValue = 0;
StoreGoods = 0;
StoreMaterial = 0;
Smallassetvalue = 0;
Cashbeginning = 0;
Cashend = 0;
Bankbeginning = 0;
Bankend = 0;
AssetRemainderPrice = 0;
AssetRemainderPrice0 = 0;
AssetRemainderPrice1 = 0;
/*Datumy z obdobi*/
--
IF (:APeriod_ID IS NULL OR :APeriod_ID = '0000000000') THEN
EXIT;
SELECT P.Datefrom$Date, P.Dateto$Date
FROM Periods P
WHERE P.ID = :APeriod_ID
INTO
:mDateFrom$Date, :mDateTo$Date;
/*V datu "do" je uloženo datum začátku následujícího období -> upravím proměnnou na poslední datum vypisovaného období*/
mDateTo$Date = mDateTo$Date - 1;
/* Je-li zadana podminka ADateTo a je v mezich Periody tak se DatumDo nastavi na ni */
IF ((ADateTo < mDateTo$Date) AND (ADateTo >= mDateFrom$Date)) THEN BEGIN
mDateTo$Date = :ADateTo;
END
/*Počátek Bank*/
SELECT
CASE WHEN SUM(A.BeginningLocal) IS NULL THEN CAST(0 AS NUMERIC(15,2)) ELSE SUM(A.BeginningLocal) END AS BankCredit
FROM BankAccounts2 A WHERE
A.Period_ID = :APeriod_ID
INTO :BankBeginning;
/*Počátek Pokladen*/
SELECT
CASE WHEN SUM(A.BeginningLocal) IS NULL THEN CAST(0 AS NUMERIC(15,2)) ELSE SUM(A.BeginningLocal) END AS CashCredit
FROM CashDesks2 A WHERE
A.Period_ID = :APeriod_ID
INTO :CashBeginning;
/*Drobný majetek*/
SELECT
CASE WHEN SUM(SAC.Purchaseprice) IS NULL THEN CAST(0 AS NUMERIC(15,2)) ELSE SUM(SAC.Purchaseprice) END
FROM SmallAssetCards SAC
WHERE ((SAC.Discarddate$Date > :mDateTo$Date) or (SAC.Discarddate$Date = 0) or (SAC.Discarddate$Date is Null)) and
(SAC.Purchasedate$Date <= :mDateTo$Date)
INTO :SmallAssetValue;
/*Odpisy za období*/
/*tohle se řeší ne pomocí period_id ale musí se to brát Rok,Mesic*/
mFirstYear = Ib_Decodedate_Year(mDateFrom$Date);
mFirstMonth = Ib_Decodedate_Month(mDateFrom$Date);
mLastYear = Ib_Decodedate_Year(mDateTo$Date);
mLastMonth = Ib_Decodedate_Month(mDateTo$Date);
SELECT
CASE WHEN SUM(AD.Taxdepreciation) IS NULL THEN CAST(0 AS NUMERIC(15,2)) ELSE SUM(AD.Taxdepreciation) END
FROM AssetDepreciations AD
WHERE ((AD.D_Year*100+AD.D_Month) >= (:mFirstYear*100+:mFirstMonth)) and
((AD.D_Year*100+AD.D_Month) <= (:mLastYear*100+:mLastMonth))
INTO :DEPRECIATIONWRITEOFF;
SELECT
CASE WHEN SUM(AC.TaxDeprecAccountedInLastYear) IS NULL THEN CAST(0 AS NUMERIC(15,2)) ELSE SUM(AC.TaxDeprecAccountedInLastYear) END
FROM AssetCards AC
WHERE (AC.FileInDate$Date >= :mDateFrom$Date) and (AC.FileInDate$Date <= :mDateTo$Date)
INTO :mAmount;
IF (mAmount <> 0) then
DEPRECIATIONWRITEOFF = DEPRECIATIONWRITEOFF + mAmount;
/* Zustatková cena majetku podle typu */
FOR
SELECT TangibleType,
SUM(OutTaxRemainderPrice) as TaxRemainderPrice,
SUM(OutSumTaxDepreciation) as TaxDepreciation
FROM AssetBookBackForDate(
'', '',
'', '',
'', '',
'', '',
'', '',
'', '',
'', '',
'', '',
0, 0,
'', '',
:mDateTo$DATE,:mLastYear,:mLastMonth,0,0,
0,0, 0)
A
LEFT JOIN AssetCards AC ON AC.ID=A.ID
LEFT JOIN AssetTypes ATYPE ON ATYPE.ID=AC.AssetType_ID
GROUP by 1
INTO :mTangibleType, :mAssetRemainderPrice, :mAssetTaxDepreciation
DO BEGIN
IF (mTangibleType=0) THEN
AssetRemainderPrice0=AssetRemainderPrice0 + :mAssetRemainderPrice;
ELSE
IF (mTangibleType=1) THEN
AssetRemainderPrice1=AssetRemainderPrice1 + :mAssetRemainderPrice;
AssetRemainderPrice=AssetRemainderPrice + :mAssetRemainderPrice;
END
/*
LEFT JOIN AssetLocations AL ON AL.ID=AC.AssetLocation_ID
LEFT JOIN TaxDepreciationGroups TDG ON TDG.ID=AC.TaxDepreciationGroup_ID
LEFT JOIN AssetResponsibles AR ON AR.ID=AC.Responsible_ID
LEFT JOIN Persons PR ON PR.ID=AR.Person_ID
LEFT JOIN Divisions DV ON DV.ID=AC.EvidenceDivision_ID
*/
/*Pohledávky ve fakturách*/
SELECT SUM(A.LocalAmount - (SELECT COALESCE(SUM(LocalPAmount),0) FROM PaymentsForDocument('03', A.ID, :mDateTo$Date)))
FROM IssuedInvoices A
WHERE (A.Docdate$Date <= :mDateTo$Date)
INTO OUTSTANDINGINVOICES;
IF (OUTSTANDINGINVOICES IS NULL) THEN BEGIN
OUTSTANDINGINVOICES = 0;
END
/*Minus Dobropisy*/
SELECT SUM(A.LocalAmount - (SELECT COALESCE(SUM(LocalPAmount),0) FROM PaymentsForDocument('60', A.ID, :mDateTo$Date)))
FROM IssuedCreditNotes A
WHERE (A.Docdate$Date <= :mDateTo$Date)
INTO mAmount;
IF ((mAmount IS NOT NULL) AND (mAmount <> 0)) THEN BEGIN
OUTSTANDINGINVOICES = OUTSTANDINGINVOICES - mAmount;
END
/*Závazky ve fakturách*/
SELECT SUM(A.LocalAmount - (SELECT COALESCE(SUM(LocalPAmount),0) FROM PaymentsForDocument('04', A.ID, :mDateTo$Date)))
FROM ReceivedInvoices A
WHERE (A.Docdate$Date <= :mDateTo$Date)
INTO LIABILITIESINVOICES;
IF (LIABILITIESINVOICES IS NULL) THEN BEGIN
LIABILITIESINVOICES = 0;
END
/*Minus Dobropisy*/
SELECT SUM(A.LocalAmount - (SELECT COALESCE(SUM(LocalPAmount),0) FROM PaymentsForDocument('61', A.ID, :mDateTo$Date)))
FROM ReceivedCreditNotes A
WHERE (A.Docdate$Date <= :mDateTo$Date)
INTO mAmount;
IF ((mAmount IS NOT NULL) AND (mAmount <> 0)) THEN BEGIN
LIABILITIESINVOICES = LIABILITIESINVOICES - mAmount;
END
/*Stav skladu*/
SELECT SUM(A.BegLocalAmount + A.LocalAmount) FROM
StoreFlowsByDates(-1, :mDateTo$Date+1, :AStoreSelID, '',
'', '', '', '', '', '', '', '', '', '', '') A
LEFT JOIN StoreCards SC ON SC.ID=A.StoreCard_ID
WHERE (:AExclStoreCardCategorySelID = '') OR
(SC.StoreCardCategory_ID NOT IN (SELECT OBJ_ID FROM Seldat SLD WHERE SLD.SEL_ID=:AExclStoreCardCategorySelID))
INTO :StoreValue;
IF (AStoreCardCategoryGoodsSelID <> '') THEN
BEGIN
SELECT SUM(A.BegLocalAmount + A.LocalAmount) FROM
StoreFlowsByDates(-1, :mDateTo$Date+1, :AStoreSelID, '',
'', '', '', '', '', '', '', '', '', '', '') A
LEFT JOIN StoreCards SC ON SC.ID=A.StoreCard_ID
WHERE (SC.StoreCardCategory_ID in (SELECT OBJ_ID FROM Seldat SLD WHERE SLD.SEL_ID=:AStoreCardCategoryGoodsSelID))
INTO StoreGoods;
END
IF (AStoreCardCategoryMaterSelID <> '') THEN
BEGIN
SELECT SUM(A.BegLocalAmount + A.LocalAmount) FROM
StoreFlowsByDates(-1, :mDateTo$Date+1, :AStoreSelID, '',
'', '', '', '', '', '', '', '', '', '', '') A
LEFT JOIN StoreCards SC ON SC.ID=A.StoreCard_ID
WHERE (SC.StoreCardCategory_ID in (SELECT OBJ_ID FROM Seldat SLD WHERE SLD.SEL_ID=:AStoreCardCategoryMaterSelID))
INTO StoreMaterial;
END
BankEnd = BankBeginning;
CashEnd = CashBeginning;
SELECT MIN(P.Datefrom$Date)
FROM Periods P
INTO :mDateFrom;
/*musim vzit denik od pocatku aby se dobře spočítala průběžná položka*/
FOR
SELECT
A.Category,
(Case when A.Category=0 then
IT.Category
else
ET.Category
end) AS IECategory,
A.Docdate$Date,
Max(A.Error) as Error,
CASE WHEN Sum(A.BankCredit) IS NULL THEN CAST(0 AS NUMERIC(15,2)) ELSE Sum(A.BankCredit) END as BankCredit,
CASE WHEN Sum(A.BankDebit) IS NULL THEN CAST(0 AS NUMERIC(15,2)) ELSE Sum(A.BankDebit) END as BankDebit,
CASE WHEN Sum(A.CashCredit) IS NULL THEN CAST(0 AS NUMERIC(15,2)) ELSE Sum(A.CashCredit) END as CashCredit,
CASE WHEN Sum(A.CashDebit) IS NULL THEN CAST(0 AS NUMERIC(15,2)) ELSE Sum(A.CashDebit) END as CashDebit,
CASE WHEN Sum(A.ThroughItemCredit) IS NULL THEN CAST(0 AS NUMERIC(15,2)) ELSE Sum(A.ThroughItemCredit) END as ThroughItemCredit,
CASE WHEN Sum(A.ThroughItemDebit) IS NULL THEN CAST(0 AS NUMERIC(15,2)) ELSE Sum(A.ThroughItemDebit) END as ThroughItemDebit,
CASE WHEN Sum(A.VATCredit) IS NULL THEN CAST(0 AS NUMERIC(15,2)) ELSE Sum(A.VATCredit) END as VATCredit,
CASE WHEN Sum(A.VATDebit) IS NULL THEN CAST(0 AS NUMERIC(15,2)) ELSE Sum(A.VATDebit) END as VATDebit,
CASE WHEN Sum(A.Amount) IS NULL THEN CAST(0 AS NUMERIC(15,2)) ELSE Sum(A.Amount) END as Amount
FROM SimpleCashBook(:mDateFrom, :mDateTo$Date + 1) A
LEFT JOIN IncomeTypes IT ON IT.ID = A.IncomeType_ID
LEFT JOIN ExpenseTypes ET ON ET.ID = A.ExpenseType_ID
GROUP BY 1,2,3
INTO :mCategory, :mIECategory, :mDocDate, :mError, :mBankCredit, :mBankDebit, mCashCredit, mCashDebit,
:mThroughItemCredit, :mThroughItemDebit, mVATCredit, mVATDebit, mAmount
DO BEGIN
ThroughItem = ThroughItem + (mThroughItemCredit - mThroughItemDebit);
IF (mDocDate >= mDateFrom$Date) THEN BEGIN
/*Spadá do vybranného období*/
IF (mCategory = 0) THEN BEGIN
/*Příjmy*/
IF ((mIECategory = 0) or (mIECategory = 1)) THEN BEGIN
/*Příjmy podléhající dani z příjmu*/
TAXABLEINCOME = TAXABLEINCOME + mAmount;
NONTAXABLEINCOMES = NONTAXABLEINCOMES + mVatCredit;
END ELSE BEGIN
/*Příjmy nepodléhající dani z příjmu*/
NONTAXABLEINCOMES = NONTAXABLEINCOMES + mAmount + mVatCredit;
END
END ELSE BEGIN
/*Výdaje*/
IF ((mIECategory=0) OR (mIECategory=1) OR (mIECategory=2) OR (mIECategory=3)) THEN BEGIN
/*Výdaje snižující základ daně*/
TAXDEDUCTABLEEXPENSE = TAXDEDUCTABLEEXPENSE + mAmount;
NONTAXDEDUCTABLEEXPENSE = NONTAXDEDUCTABLEEXPENSE + mVatDebit;
END ELSE BEGIN
/*Výdaje nesnižující základ daně*/
NONTAXDEDUCTABLEEXPENSE = NONTAXDEDUCTABLEEXPENSE + mAmount + mVatDebit;
END
END
CashEnd = CashEnd + (mCashCredit - mCashDebit);
BankEnd = BankEnd + (mBankCredit - mBankDebit);
END
END
SUSPEND;
end