codbex / codbex-olympus

Enterprise Resource Planing
Eclipse Public License 2.0
0 stars 0 forks source link

[Hestia] Add report for monthly Cashflow #10

Open MinaDoncheva opened 1 month ago

MinaDoncheva commented 1 month ago

Add report using this sql query:

SELECT 
    SUM(TRANSACTION_AMOUNT) AS CASHFLOW_NET,
    DATE_TRUNC('day', TRANSACTION_DATE) AS CASHFLOW_DATE
FROM (
    SELECT 
        SALESINVOICE_DATE AS TRANSACTION_DATE,
        SALESINVOICE_NET AS TRANSACTION_AMOUNT
    FROM CODBEX_SALESINVOICE
    UNION ALL
    SELECT 
        PURCHASEINVOICE_DATE AS TRANSACTION_DATE,
        -PURCHASEINVOICE_NET AS TRANSACTION_AMOUNT
    FROM CODBEX_PURCHASEINVOICE
) AS CombinedData
GROUP BY DATE_TRUNC('day', TRANSACTION_DATE)
ORDER BY CASHFLOW_DATE DESC;