SpGetPayables.sql

CREATE DEFINER=`tenziDB_admin`@`%` PROCEDURE `Sp_Get_Payables`(
    businessId varchar(100),
    date_now varchar(100),
    date_30 varchar(100),
    date_60 varchar(100),
    date_90 varchar(100)
)
BEGIN
    -- 30 days
    SELECT
        SUM(c.CreditAmount) AS CREDIT, SUM(c.DebitAmount) AS DEBIT,
        SUM(c.CreditAmount) - SUM(c.DebitAmount) AS NETPayables
    FROM purchaseorders p
             INNER JOIN purchasetransactions c
                        ON p.Id = c.PurchaseId
    WHERE p.CustomerCustId = businessId
      AND p.DeliveredStatus = 0
      AND p.CreatedDate BETWEEN  date_30 AND date_now
      AND p.Deleted = false
    UNION ALL

    -- 60 days
    SELECT
        SUM(c.CreditAmount) AS CREDIT, SUM(c.DebitAmount) AS DEBIT,
        SUM(c.CreditAmount) - SUM(c.DebitAmount) AS NETPayables
    FROM purchaseorders p
             INNER JOIN purchasetransactions c
                        ON p.Id = c.PurchaseId
    WHERE p.CustomerCustId = businessId
      AND p.DeliveredStatus = 0
      AND p.CreatedDate BETWEEN  date_60 AND date_now
      AND p.Deleted = false
    UNION ALL

    -- 90 days
    SELECT
        SUM(c.CreditAmount) AS CREDIT, SUM(c.DebitAmount) AS DEBIT,
        SUM(c.CreditAmount) - SUM(c.DebitAmount) AS NETPayables
    FROM purchaseorders p
             INNER JOIN purchasetransactions c
                        ON p.Id = c.PurchaseId
    WHERE p.CustomerCustId = businessId
      AND p.DeliveredStatus = 0
      AND p.CreatedDate BETWEEN  date_90 AND date_now
      AND p.Deleted = false;

END