Created
January 3, 2024 10:41
-
-
Save otienoelvis/43aba66349adfe1cc18134833226b4a5 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment