DROP PROCEDURE IF EXISTS `sp_getSupplierAging`;

DELIMITER //

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_getSupplierAging`(
	IN `CompanyID` INT,
	IN `EndDate` DATE
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	DROP TEMPORARY TABLE IF EXISTS Results;
	CREATE TEMPORARY TABLE IF NOT EXISTS Results (
		SupplierID INT,
		SupplierName VARCHAR(128),
		Current DECIMAL(15,5),
		Month1 DECIMAL(15,5),
		Month2 DECIMAL(15,5),
		Month3 DECIMAL(15,5),
		Month4 DECIMAL(15,5),
		Month5 DECIMAL(15,5),
		Month6 DECIMAL(15,5),
		Month7 DECIMAL(15,5),
		Month8 DECIMAL(15,5),
		Month9 DECIMAL(15,5),
		Month10 DECIMAL(15,5),
		Month11 DECIMAL(15,5)
	);
	BLOCK1: BEGIN
		DECLARE DONE_BLOCK1 INT DEFAULT 0;
		DECLARE SupplierID INT;
		DECLARE SupplierName CHAR(9);
		DECLARE CURSOR_SupplierID CURSOR FOR
			SELECT
				supplier.ID, supplier.supplierName
			FROM
				supplier
			WHERE
				supplier.companyID = CompanyID AND
				supplier.is_delete = 0;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE_BLOCK1 = 1;
		
		OPEN CURSOR_SupplierID;
		REPEAT
		FETCH CURSOR_SupplierID INTO SupplierID, SupplierName;
		
		INSERT INTO Results (
			SupplierID,
			SupplierName,
			Current
		)
		SELECT
		(
			SELECT COALESCE(SUM(comp_supplier_invoice.totalAmount), 0)
			FROM comp_supplier_invoice
			WHERE comp_supplier_invoice.companyID = a.companyID AND
			comp_supplier_invoice.supplierID = a.ID AND
			PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM "'.$selected_date.'"), EXTRACT(YEAR_MONTH FROM comp_supplier_invoice.invoiceDate)) = 0 AND
			comp_supplier_invoice.updateAble = 0 AND
			comp_supplier_invoice.is_delete = 0
		)
		+
		(
			SELECT COALESCE(SUM(comp_supplier_creditnote.totalAmount), 0)
			FROM  comp_supplier_creditnote
			WHERE comp_supplier_creditnote.companyID = a.companyID AND
			comp_supplier_creditnote.supplierID = a.ID AND
			PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM "'.$selected_date.'"), EXTRACT(YEAR_MONTH FROM comp_supplier_creditnote.creditNoteDate)) = 0 AND
			comp_supplier_creditnote.updateAble = 0 AND
			comp_supplier_creditnote.is_delete = 0
		)
		+
		(
			SELECT COALESCE(SUM(comp_historical_supplier_invoice.totalAmount), 0)
			FROM comp_historical_supplier_invoice
			WHERE comp_historical_supplier_invoice.companyID = a.companyID AND
			comp_historical_supplier_invoice.supplierID = a.ID AND
			PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM "'.$selected_date.'"), EXTRACT(YEAR_MONTH FROM comp_historical_supplier_invoice.invoiceDate)) = 0 AND
			comp_historical_supplier_invoice.updateAble = 0 AND
			comp_historical_supplier_invoice.is_delete = 0
		)
		-
		(
			(
				SELECT COALESCE(SUM(k.amountApplied), 0)
				FROM comp_payment_detail k INNER JOIN comp_payment l
				ON l.ID = k.paymentID
				WHERE
				k.purchaseInvoiceID IN (
					SELECT comp_supplier_invoice.ID
					FROM comp_supplier_invoice
					WHERE comp_supplier_invoice.companyID = a.companyID AND
					comp_supplier_invoice.supplierID = a.ID AND
					comp_supplier_invoice.updateAble = 0 AND
					comp_supplier_invoice.is_delete = 0
				) AND
				l.is_delete = 0 AND
				l.updateAble = 0 AND
				PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM "'.$selected_date.'"), EXTRACT(YEAR_MONTH FROM l.paymentDate)) = 0
			)
			+
			(
				SELECT COALESCE(SUM(m.amountApplied), 0)
				FROM comp_payment_detail_creditnote m INNER JOIN comp_payment n
				ON n.ID = m.paymentID
				WHERE
				m.creditnoteID IN (
					SELECT comp_supplier_creditnote.ID
					FROM  comp_supplier_creditnote
					WHERE comp_supplier_creditnote.companyID = a.companyID AND
					comp_supplier_creditnote.supplierID = a.ID AND
					comp_supplier_creditnote.updateAble = 0 AND
					comp_supplier_creditnote.is_delete = 0
				) AND
				n.is_delete = 0 AND
				n.updateAble = 0 AND
				PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM "'.$selected_date.'"), EXTRACT(YEAR_MONTH FROM n.paymentDate)) = 0
			)
			+
			(
				SELECT COALESCE(SUM(o.amountApplied), 0)
				FROM comp_payment_detail_historical_invoice o INNER JOIN comp_payment p
				ON p.ID = o.paymentID
				WHERE
				o.historicalinvoiceID IN (
					SELECT comp_historical_supplier_invoice.ID
					FROM comp_historical_supplier_invoice
					WHERE comp_historical_supplier_invoice.companyID = a.companyID AND
					comp_historical_supplier_invoice.supplierID = a.ID AND
					comp_historical_supplier_invoice.updateAble = 0 AND
					comp_historical_supplier_invoice.is_delete = 0
				) AND
				p.is_delete = 0 AND
				p.updateAble = 0 AND
				PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM "'.$selected_date.'"), EXTRACT(YEAR_MONTH FROM p.paymentDate)) = 0
			)
		)
		AS `current`,

		UNTIL DONE_BLOCK1 END REPEAT;
		CLOSE CURSOR_SupplierID;
	END BLOCK1;
	
	SELECT * FROM Results ORDER BY SupplierName ASC;
END

//