DROP PROCEDURE IF EXISTS `sp_GetBadDebtRecovered`;

DELIMITER //

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_GetBadDebtRecovered`(
	IN `Type` VARCHAR(50),
	IN `CompanyID` INT,
	IN `StartDate` DATE,
	IN `EndDate` DATE,
	IN `UpdateAble` BOOL
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	DROP TEMPORARY TABLE IF EXISTS Results;
	IF `Type` = '' THEN
		SET `Type` = 'customer';
	END IF;
	IF `Type` = 'customer' THEN
		CREATE TEMPORARY TABLE IF NOT EXISTS Results AS (
		SELECT
			comp_sales_debt_recover.ID,
			CONCAT(comp_sales_debt_recover.formNo, '[', LPAD(comp_sales_debt_recover.serialNo, 8, '0'), ']') AS BadDebtNo,
			DATE_FORMAT(comp_sales_debt_recover.transDate, '%d-%m-%Y') AS BadDebtDate,
			comp_sales_debt_recover.valueDebtRecover AS AmountPaid,
			comp_sales_debt_recover.gstDebtRecover AS BadDebtAmount,
			customer.customerName AS Name,
			customer.phoneNumber1 AS Contact,
			customer.email AS Email
		FROM
			comp_sales_debt_recover LEFT JOIN customer
			ON customer.ID = comp_sales_debt_recover.customerID AND customer.companyID = comp_sales_debt_recover.companyID AND customer.is_delete = 0
		WHERE
			comp_sales_debt_recover.companyID = `CompanyID` AND
			comp_sales_debt_recover.updateAble = `UpdateAble` AND
			comp_sales_debt_recover.transDate >= `StartDate` AND
			comp_sales_debt_recover.transDate <= `EndDate` AND
			comp_sales_debt_recover.is_delete = 0
		ORDER BY
			comp_sales_debt_recover.transDate DESC, customer.customerName ASC);
	ELSEIF `Type` = 'supplier' THEN
		CREATE TEMPORARY TABLE IF NOT EXISTS Results AS (
		SELECT
			comp_purchase_bad_debt.ID,
			CONCAT(comp_purchase_bad_debt.formNo, '[', LPAD(comp_purchase_bad_debt.serialNo, 8, '0'), ']') AS BadDebtNo,
			DATE_FORMAT(comp_purchase_bad_debt.transDate, '%d-%m-%Y') AS BadDebtDate,
			comp_purchase_bad_debt.valueBadDebt AS OutstandingAmount,
			comp_purchase_bad_debt.gstBadDebt AS BadDebtAmount,
			supplier.supplierName AS Name,
			supplier.phoneNumber1 AS Contact,
			supplier.email AS Email
		FROM
			comp_purchase_bad_debt LEFT JOIN supplier
			ON supplier.ID = comp_purchase_bad_debt.supplierID AND supplier.companyID = comp_purchase_bad_debt.companyID AND supplier.is_delete = 0
		WHERE
			comp_purchase_bad_debt.companyID = `CompanyID` AND
			comp_purchase_bad_debt.updateAble = `UpdateAble` AND
			comp_purchase_bad_debt.transDate >= `StartDate` AND
			comp_purchase_bad_debt.transDate <= `EndDate` AND
			comp_purchase_bad_debt.is_delete = 0
		ORDER BY
		comp_purchase_bad_debt.transDate DESC, supplier.supplierName ASC);
	END IF;
	SELECT * FROM Results;
END

//