DROP PROCEDURE IF EXISTS `sp_GetBadDebtReport`;

DELIMITER //

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_GetBadDebtReport`(
	IN `Type` VARCHAR(50),
	IN `Category` VARCHAR(50),
	IN `CompanyID` INT,
	IN `StartDate` DATE,
	IN `EndDate` DATE,
	IN `EntityID` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	DECLARE ENTITY_ID INT;
	DECLARE ENTITY_NAME VARCHAR(256) DEFAULT '';
	DECLARE BAD_DEBT_ID INT;
	DECLARE BAD_DEBT_DATE DATE;
	DECLARE INVOICE_ID INT;
	DECLARE INVOICE_TOTAL_AMOUNT DECIMAL(15,5) DEFAULT 0;
	DECLARE INVOICE_TOTAL_TAX_AMOUNT DECIMAL(15,5) DEFAULT 0;
	DECLARE PAYMENT_ID INT;
	DROP TEMPORARY TABLE IF EXISTS Reports;
	CREATE TEMPORARY TABLE Reports (
		EntityID INT DEFAULT 0,
		COL1 VARCHAR(256) DEFAULT NULL,
		COL2 VARCHAR(256) DEFAULT NULL,
		COL3 VARCHAR(256) DEFAULT NULL,
		COL4 VARCHAR(256) DEFAULT NULL,
		COL5 VARCHAR(256) DEFAULT NULL,
		COL6 VARCHAR(256) DEFAULT NULL,
		COL7 VARCHAR(256) DEFAULT NULL,
		COL8 VARCHAR(256) DEFAULT NULL,
		COL9 VARCHAR(256) DEFAULT NULL,
		COL10 VARCHAR(256) DEFAULT NULL,
		COL11 VARCHAR(256) DEFAULT NULL,
		COL12 VARCHAR(256) DEFAULT NULL,
		IsInvoice INT DEFAULT FALSE,
		IsBadDebt INT DEFAULT FALSE,
		IsTitle INT DEFAULT FALSE
	);
	IF `Type` = 'customer' THEN
		BLOCK_ENTITY: BEGIN
			DECLARE DONE_GET_ENTITY INT DEFAULT FALSE;
			DECLARE CURSOR_GET_ENTITY CURSOR FOR
			SELECT
				customer.ID, customer.customerName
			FROM
				customer
			WHERE
				customer.companyID = CompanyID AND
				customer.is_delete = 0 AND
				customer.ID IN (
					SELECT DISTINCT
						comp_sales_bad_debt.customerID
					FROM
						comp_sales_bad_debt LEFT JOIN comp_journal
						ON comp_journal.journalID = CONVERT(comp_sales_bad_debt.journalNo USING UTF8) AND
						comp_journal.companyID = comp_sales_bad_debt.companyID
					WHERE
						comp_sales_bad_debt.is_delete = 0 AND
						comp_sales_bad_debt.companyID = CompanyID AND
						(comp_sales_bad_debt.transDate BETWEEN `StartDate` AND `EndDate`) AND
						(comp_sales_bad_debt.updateAble = 0 AND comp_sales_bad_debt.is_delete = 0) AND
						(comp_journal.`status` = 1 AND comp_journal.is_delete = 0)
				) AND
				(0 = `EntityID` OR customer.ID = `EntityID`)
			ORDER BY
				customer.customerName ASC;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE_GET_ENTITY = TRUE;
			
			OPEN CURSOR_GET_ENTITY;
			FETCH CURSOR_GET_ENTITY INTO ENTITY_ID, ENTITY_NAME;
			comp_customer_loop: LOOP
				IF DONE_GET_ENTITY THEN
					LEAVE comp_customer_loop;
				END IF;
				
				INSERT Reports (
					EntityID,
					COL2,
					IsTitle
				)
				SELECT
					ENTITY_ID,
					ENTITY_NAME,
					1
				FROM
					customer
				WHERE
					customer.ID = ENTITY_ID;
				
				IF Category = 'relief' OR Category = 'recovered' THEN
				 	BLOCK_BAD_DEBT: BEGIN
						DECLARE DONE_GET_BAD_DEBT INT DEFAULT FALSE;
						DECLARE CURSOR_GET_BAD_DEBT CURSOR FOR
						SELECT
							comp_sales_bad_debt.ID, comp_sales_bad_debt.transDate
						FROM
							comp_sales_bad_debt LEFT JOIN comp_journal
							ON comp_journal.journalID = CONVERT(comp_sales_bad_debt.journalNo USING UTF8) AND
							comp_journal.companyID = comp_sales_bad_debt.companyID
						WHERE
							comp_sales_bad_debt.companyID = `CompanyID` AND
							comp_sales_bad_debt.customerID = ENTITY_ID AND
							comp_sales_bad_debt.transDate BETWEEN `StartDate` AND `EndDate` AND
							(comp_sales_bad_debt.updateAble = 0 AND comp_sales_bad_debt.is_delete = 0) AND
							(comp_journal.`status` = 1 AND comp_journal.is_delete = 0)
						ORDER BY
							comp_sales_bad_debt.transDate ASC;
						DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE_GET_BAD_DEBT = TRUE;
						
						OPEN CURSOR_GET_BAD_DEBT;
						FETCH CURSOR_GET_BAD_DEBT INTO BAD_DEBT_ID, BAD_DEBT_DATE;
						comp_sales_bad_debt_loop: LOOP
							IF DONE_GET_BAD_DEBT THEN
								LEAVE comp_sales_bad_debt_loop;
							END IF;
							
							INSERT Reports (
								EntityID,
								COL1, -- ID
								COL2, -- Entity Name
								COL3, -- Form No
								COL4, -- Date
								COL5, -- Base Amount
								COL6, -- Bad Debt Amount
								IsBadDebt
							)
							SELECT
								ENTITY_ID,
								comp_sales_bad_debt.ID,
								ENTITY_NAME,
								CONCAT(comp_sales_bad_debt.formNo, '[', LPAD(comp_sales_bad_debt.serialNo, 8, '0'), ']'),
								DATE_FORMAT(comp_sales_bad_debt.transDate, '%d-%m-%Y'),
								CAST(comp_sales_bad_debt.gstBadDebt * 100 / 6 AS DECIMAL(15,5)),
								comp_sales_bad_debt.gstBadDebt,
								1
							FROM
								comp_sales_bad_debt
							WHERE
								comp_sales_bad_debt.ID = BAD_DEBT_ID;
							
							BLOCK_INVOICE: BEGIN
								DECLARE TOTAL_TAX_PAID DECIMAL(15,5) DEFAULT 0;
								DECLARE DONE_GET_INVOICE INT DEFAULT FALSE;
								DECLARE CURSOR_GET_INVOICE CURSOR FOR
								SELECT
									comp_sales_invoice.ID, comp_sales_invoice.totalamount, comp_sales_invoice.totalTaxAmount
								FROM
									comp_sales_invoice LEFT JOIN comp_journal
									ON comp_journal.journalID = CONVERT(comp_sales_invoice.journalNo USING UTF8) AND
									comp_journal.companyID = comp_sales_invoice.companyID
								WHERE
									comp_sales_invoice.badDebtID = BAD_DEBT_ID AND
									(comp_sales_invoice.updateAble = 0 AND comp_sales_invoice.is_delete = 0) AND
									(comp_journal.`status` = 1 AND comp_journal.is_delete = 0)
								ORDER BY
									comp_sales_invoice.salesInvoiceDate ASC;
								DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE_GET_INVOICE = TRUE;
								
								OPEN CURSOR_GET_INVOICE;
								FETCH CURSOR_GET_INVOICE INTO INVOICE_ID, INVOICE_TOTAL_AMOUNT, INVOICE_TOTAL_TAX_AMOUNT;
								comp_sales_invoice_loop: LOOP
									IF DONE_GET_INVOICE THEN
										LEAVE comp_sales_invoice_loop;
									END IF;
									
									SET TOTAL_TAX_PAID = 0;
									
									IF Category = 'recovered' THEN
										INSERT Reports (
											EntityID,
											COL1, -- ID
											COL2, -- Entity Name
											COL3, -- Form No
											COL4, -- Date
											COL5, -- Base Amount
											COL6, -- Bad Debt Amount
											COL7, -- Outstanding Amount
											COL8, -- Document
											IsInvoice
										)
										SELECT
											ENTITY_ID,
											comp_sales_invoice.ID,
											ENTITY_NAME,
											CONCAT(comp_sales_invoice.formNo, '[', LPAD(comp_sales_invoice.serialNo, 8, '0'), ']'),
											DATE_FORMAT(comp_sales_invoice.salesInvoiceDate, '%d-%m-%Y'),
											IF(
												comp_sales_invoice.totalReceivable > 0,
												CAST(CAST(comp_sales_invoice.totalReceivable / comp_sales_invoice.totalamount * comp_sales_invoice.totalTaxAmount AS DECIMAL(15,5)) * 100 / 6 AS DECIMAL(15,5)),
												CAST(comp_sales_invoice.totalTaxAmount * 100 / 6 AS DECIMAL(15,5))
											),
											IF(
												comp_sales_invoice.totalReceivable > 0,
												CAST(comp_sales_invoice.totalReceivable / comp_sales_invoice.totalamount * comp_sales_invoice.totalTaxAmount AS DECIMAL(15,5)),
												comp_sales_invoice.totalTaxAmount
											),
											comp_sales_invoice.totalReceivable,
											'Invoice',
											1
										FROM
											comp_sales_invoice
										WHERE
											comp_sales_invoice.ID = INVOICE_ID;
									END IF;
										
									BLOCK_PAYMENT: BEGIN
										DECLARE DONE_GET_PAYMENT INT DEFAULT FALSE;
										DECLARE CURSOR_GET_PAYMENT CURSOR FOR
										SELECT
											comp_receive_payment_detail.ID
										FROM
											comp_receive_payment_detail LEFT JOIN comp_receive_payment
											ON comp_receive_payment.ID = comp_receive_payment_detail.receivePaymentID LEFT JOIN comp_journal
											ON comp_journal.journalID = CONVERT(comp_receive_payment.journalNo USING UTF8) AND
											comp_journal.companyID = comp_receive_payment.companyID
										WHERE
											comp_receive_payment_detail.invoiceID = INVOICE_ID AND
											comp_receive_payment.receivePaymentDate >= BAD_DEBT_DATE AND
											(comp_receive_payment.updateAble = 0 AND comp_receive_payment.is_delete = 0) AND
											(comp_journal.`status` = 1 AND comp_journal.is_delete = 0)
										ORDER BY
											comp_receive_payment.receivePaymentDate ASC;
										DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE_GET_PAYMENT = TRUE;
										
										OPEN CURSOR_GET_PAYMENT;
										FETCH CURSOR_GET_PAYMENT INTO PAYMENT_ID;
										comp_receive_payment_loop: LOOP
											IF DONE_GET_PAYMENT THEN
												LEAVE comp_receive_payment_loop;
											END IF;
											
											SET TOTAL_TAX_PAID = TOTAL_TAX_PAID + COALESCE((
												SELECT
													CAST(INVOICE_TOTAL_TAX_AMOUNT / INVOICE_TOTAL_AMOUNT * comp_receive_payment_detail.amountApplied AS DECIMAL(15,5))
												FROM
													comp_receive_payment_detail LEFT JOIN comp_receive_payment
													ON comp_receive_payment.ID = comp_receive_payment_detail.receivePaymentID
												WHERE
													comp_receive_payment_detail.ID = PAYMENT_ID
											), 0);
											
											IF Category = 'recovered' THEN
												INSERT Reports (
													EntityID,
													COL1, -- ID
													COL2, -- Entity Name
													COL3, -- Form No
													COL4, -- Date
													COL5, -- Base Amount
													COL6, -- Bad Debt Amount
													COL7, -- Amount Applied
													COL8, -- Document
													COL9, -- Bad Debt Recovered Form No
													COL10, -- Bad Debt Recovered Date
													COL11 -- Confirm Bad Debt Recovered
												)
												SELECT
													ENTITY_ID,
													comp_receive_payment_detail.ID,
													ENTITY_NAME,
													CONCAT(comp_receive_payment.formNo, '[', LPAD(comp_receive_payment.serialNo, 8, '0'), ']'),
													DATE_FORMAT(comp_receive_payment.receivePaymentDate, '%d-%m-%Y'),
													CAST(CAST(INVOICE_TOTAL_TAX_AMOUNT / INVOICE_TOTAL_AMOUNT * comp_receive_payment_detail.amountApplied AS DECIMAL(15,5)) * 100 / 6 AS DECIMAL(15,5)),
													CAST(INVOICE_TOTAL_TAX_AMOUNT / INVOICE_TOTAL_AMOUNT * comp_receive_payment_detail.amountApplied AS DECIMAL(15,5)),
													comp_receive_payment_detail.amountApplied,
													'Payment',
													IF(
														comp_sales_debt_recover.updateAble = 1,
														NULL,
														CONCAT(comp_sales_debt_recover.formNo, '[', LPAD(comp_sales_debt_recover.serialNo, 8, '0'), ']')
													),
													IF(
														comp_sales_debt_recover.updateAble = 1,
														NULL,
														DATE_FORMAT(comp_sales_debt_recover.transDate, '%d-%m-%Y')
													),
													comp_sales_debt_recover.updateAble
												FROM
													comp_receive_payment_detail LEFT JOIN comp_receive_payment
													ON comp_receive_payment.ID = comp_receive_payment_detail.receivePaymentID LEFT OUTER JOIN comp_sales_debt_recover
													ON comp_sales_debt_recover.ID = comp_receive_payment_detail.debtRecoverID LEFT JOIN comp_journal
													ON comp_journal.journalID = CONVERT(comp_sales_debt_recover.journalNo USING UTF8) AND
													comp_journal.companyID = comp_sales_debt_recover.companyID AND
													(comp_journal.`status` = 1 AND comp_journal.is_delete = 0)
												WHERE
													comp_receive_payment_detail.ID = PAYMENT_ID;
											END IF;
										
										FETCH CURSOR_GET_PAYMENT INTO PAYMENT_ID;
										END LOOP comp_receive_payment_loop;
										CLOSE CURSOR_GET_PAYMENT;
									END BLOCK_PAYMENT;
									
									BLOCK_CREDIT_NOTE: BEGIN
										DECLARE DONE_GET_PAYMENT INT DEFAULT FALSE;
										DECLARE CURSOR_GET_PAYMENT CURSOR FOR
										SELECT
											comp_sales_creditnote.ID
										FROM
											comp_sales_creditnote LEFT JOIN comp_journal
											ON comp_journal.journalID = CONVERT(comp_sales_creditnote.journalNo USING UTF8) AND
											comp_journal.companyID = comp_sales_creditnote.companyID
										WHERE
											comp_sales_creditnote.salesInvoiceID = INVOICE_ID AND
											comp_sales_creditnote.creditNoteDate >= BAD_DEBT_DATE AND
											(comp_sales_creditnote.updateAble = 0 AND comp_sales_creditnote.is_delete = 0) AND
											(comp_journal.`status` = 1 AND comp_journal.is_delete = 0)
										ORDER BY
											comp_sales_creditnote.creditNoteDate ASC;
										DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE_GET_PAYMENT = TRUE;
										
										OPEN CURSOR_GET_PAYMENT;
										FETCH CURSOR_GET_PAYMENT INTO PAYMENT_ID;
										comp_sales_creditnote_loop: LOOP
											IF DONE_GET_PAYMENT THEN
												LEAVE comp_sales_creditnote_loop;
											END IF;
											
											SET TOTAL_TAX_PAID = TOTAL_TAX_PAID + COALESCE((
												SELECT
													comp_sales_creditnote.totalTaxAmount
												FROM
													comp_sales_creditnote
												WHERE
													comp_sales_creditnote.ID = PAYMENT_ID
											), 0);
											
											IF Category = 'recovered' THEN
												INSERT Reports (
													EntityID,
													COL1, -- ID
													COL2, -- Entity Name
													COL3, -- Form No
													COL4, -- Date
													COL5, -- Base Amount
													COL6, -- Bad Debt Amount
													COL7, -- Amount Applied
													COL8, -- Document
													COL9, -- Bad Debt Recovered Form No
													COL10, -- Bad Debt Recovered Date
													COL11 -- Confirm Bad Debt Recovered
												)
												SELECT
													ENTITY_ID,
													comp_sales_creditnote.ID,
													ENTITY_NAME,
													CONCAT(comp_sales_creditnote.formNo, '[', LPAD(comp_sales_creditnote.serialNo, 8, '0'), ']'),
													DATE_FORMAT(comp_sales_creditnote.creditNoteDate, '%d-%m-%Y'),
													CAST(comp_sales_creditnote.totalTaxAmount * 100 / 6 AS DECIMAL(15,5)),
													comp_sales_creditnote.totalTaxAmount,
													comp_sales_creditnote.totalAmount,
													'Credit Note',
													IF(
														comp_sales_debt_recover.updateAble = 1,
														NULL,
														CONCAT(comp_sales_debt_recover.formNo, '[', LPAD(comp_sales_debt_recover.serialNo, 8, '0'), ']')
													),
													IF(
														comp_sales_debt_recover.updateAble = 1,
														NULL,
														DATE_FORMAT(comp_sales_debt_recover.transDate, '%d-%m-%Y')
													),
													comp_sales_debt_recover.updateAble
												FROM
													comp_sales_creditnote LEFT OUTER JOIN comp_sales_debt_recover
													ON comp_sales_debt_recover.ID = comp_sales_creditnote.debtRecoverID LEFT JOIN comp_journal
													ON comp_journal.journalID = CONVERT(comp_sales_debt_recover.journalNo USING UTF8) AND
													comp_journal.companyID = comp_sales_debt_recover.companyID AND
													(comp_journal.`status` = 1 AND comp_journal.is_delete = 0)
												WHERE
													comp_sales_creditnote.ID = PAYMENT_ID;
											END IF;
										
										FETCH CURSOR_GET_PAYMENT INTO PAYMENT_ID;
										END LOOP comp_sales_creditnote_loop;
										CLOSE CURSOR_GET_PAYMENT;
									END BLOCK_CREDIT_NOTE;
									
									IF Category = 'relief' THEN
										INSERT Reports (
											EntityID,
											COL1, -- ID
											COL2, -- Entity Name
											COL3, -- Form No
											COL4, -- Date
											COL5, -- Base Amount
											COL6, -- Bad Debt Amount
											COL8, -- Document
											IsInvoice
										)
										SELECT
											ENTITY_ID,
											comp_sales_invoice.ID,
											ENTITY_NAME,
											CONCAT(comp_sales_invoice.formNo, '[', LPAD(comp_sales_invoice.serialNo, 8, '0'), ']'),
											DATE_FORMAT(comp_sales_invoice.salesInvoiceDate, '%d-%m-%Y'),
											CAST((TOTAL_TAX_PAID + CAST(comp_sales_invoice.totalReceivable / INVOICE_TOTAL_AMOUNT * INVOICE_TOTAL_TAX_AMOUNT AS DECIMAL(15,5))) * 100 / 6 AS DECIMAL(15,5)),
											CAST(TOTAL_TAX_PAID + CAST(comp_sales_invoice.totalReceivable / INVOICE_TOTAL_AMOUNT * INVOICE_TOTAL_TAX_AMOUNT AS DECIMAL(15,5)) AS DECIMAL(15,5)),
											'Invoice',
											1
										FROM
											comp_sales_invoice
										WHERE
											comp_sales_invoice.ID = INVOICE_ID;
									-- ELSE
										-- UPDATE
											-- Reports
										-- SET
											-- COL5 = CAST(COL5 AS DECIMAL(15,5)) + COALESCE(CAST(TOTAL_TAX_PAID * 100 / 6 AS DECIMAL(15,5)), 0),
											-- COL6 = CAST(COL6 AS DECIMAL(15,5)) + COALESCE(TOTAL_TAX_PAID, 0)
										-- WHERE
											-- COL1 = INVOICE_ID AND
											-- IsInvoice = 1 AND
											-- COL8 = 'Invoice' AND
											-- CAST(COL7 AS DECIMAL(15,5)) > 0;
									END IF;
									
								FETCH CURSOR_GET_INVOICE INTO INVOICE_ID, INVOICE_TOTAL_AMOUNT, INVOICE_TOTAL_TAX_AMOUNT;
								END LOOP comp_sales_invoice_loop;
								CLOSE CURSOR_GET_INVOICE;
							END BLOCK_INVOICE;
							
							BLOCK_HISTORICAL_INVOICE: BEGIN
								DECLARE DONE_GET_INVOICE INT DEFAULT FALSE;
								DECLARE CURSOR_GET_INVOICE CURSOR FOR
								SELECT
									comp_historical_customer_invoice.ID, comp_historical_customer_invoice.totalAmount, comp_historical_customer_invoice.totalTaxAmount
								FROM
									comp_historical_customer_invoice
								WHERE
									comp_historical_customer_invoice.badDebtID = BAD_DEBT_ID AND
									comp_historical_customer_invoice.is_delete = 0
								ORDER BY
									comp_historical_customer_invoice.salesInvoiceDate ASC;
								DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE_GET_INVOICE = TRUE;
								
								OPEN CURSOR_GET_INVOICE;
								FETCH CURSOR_GET_INVOICE INTO INVOICE_ID, INVOICE_TOTAL_AMOUNT, INVOICE_TOTAL_TAX_AMOUNT;
								comp_sales_invoice_loop: LOOP
									IF DONE_GET_INVOICE THEN
										LEAVE comp_sales_invoice_loop;
									END IF;
									
									IF Category = 'recovered' THEN
										INSERT Reports (
											EntityID,
											COL1, -- ID
											COL2, -- Entity Name
											COL3, -- Form No
											COL4, -- Date
											COL5, -- Base Amount
											COL6, -- Bad Debt Amount
											COL7, -- Outstanding Amount
											COL8, -- Document
											IsInvoice
										)
										SELECT
											ENTITY_ID,
											comp_historical_customer_invoice.ID,
											ENTITY_NAME,
											comp_historical_customer_invoice.customerPoNo,
											DATE_FORMAT(comp_historical_customer_invoice.salesInvoiceDate, '%d-%m-%Y'),
											IF(
												comp_historical_customer_invoice.totalReceivable > 0,
												CAST(comp_historical_customer_invoice.totalReceivable / comp_historical_customer_invoice.totalAmount * comp_historical_customer_invoice.totalTaxAmount * 100 / 6 AS DECIMAL(15,5)),
												CAST(comp_historical_customer_invoice.totalTaxAmount * 100 / 6 AS DECIMAL(15,5))
											),
											IF(
												comp_historical_customer_invoice.totalReceivable > 0,
												CAST(comp_historical_customer_invoice.totalReceivable / comp_historical_customer_invoice.totalAmount * comp_historical_customer_invoice.totalTaxAmount AS DECIMAL(15,5)),
												comp_historical_customer_invoice.totalTaxAmount
											),
											CAST(comp_historical_customer_invoice.totalReceivable AS DECIMAL(15,5)),
											'Historical Invoice',
											1
										FROM
											comp_historical_customer_invoice
										WHERE
											comp_historical_customer_invoice.ID = INVOICE_ID;
									END IF;
										
									BLOCK_PAYMENT: BEGIN
										DECLARE DONE_GET_PAYMENT INT DEFAULT FALSE;
										DECLARE CURSOR_GET_PAYMENT CURSOR FOR
										SELECT
											comp_receive_payment_detail_historical_invoice.ID
										FROM
											comp_receive_payment_detail_historical_invoice LEFT JOIN comp_receive_payment
											ON comp_receive_payment.ID = comp_receive_payment_detail_historical_invoice.paymentID LEFT JOIN comp_journal
											ON comp_journal.journalID = CONVERT(comp_receive_payment.journalNo USING UTF8) AND
											comp_journal.companyID = comp_receive_payment.companyID
										WHERE
											comp_receive_payment_detail_historical_invoice.historicalinvoiceID = INVOICE_ID AND
											comp_receive_payment.receivePaymentDate >= BAD_DEBT_DATE AND
											(comp_receive_payment.updateAble = 0 AND comp_receive_payment.is_delete = 0) AND
											(comp_journal.`status` = 1 AND comp_journal.is_delete = 0)
										ORDER BY
											comp_receive_payment.receivePaymentDate ASC;
										DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE_GET_PAYMENT = TRUE;
										
										OPEN CURSOR_GET_PAYMENT;
										FETCH CURSOR_GET_PAYMENT INTO PAYMENT_ID;
										comp_receive_payment_loop: LOOP
											IF DONE_GET_PAYMENT THEN
												LEAVE comp_receive_payment_loop;
											END IF;
											
											IF Category = 'recovered' THEN
												INSERT Reports (
													EntityID,
													COL1, -- ID
													COL2, -- Entity Name
													COL3, -- Form No
													COL4, -- Date
													COL5, -- Base Amount
													COL6, -- Bad Debt Amount
													COL7, -- Amount Applied
													COL8, -- Document
													COL9, -- Bad Debt Recovered Form No
													COL10, -- Bad Debt Recovered Date
													COL11 -- Confirm Bad Debt Recovered
												)
												SELECT
													ENTITY_ID,
													comp_receive_payment_detail_historical_invoice.ID,
													ENTITY_NAME,
													CONCAT(comp_receive_payment.formNo, '[', LPAD(comp_receive_payment.serialNo, 8, '0'), ']'),
													DATE_FORMAT(comp_receive_payment.receivePaymentDate, '%d-%m-%Y'),
													CAST(CAST(INVOICE_TOTAL_TAX_AMOUNT / INVOICE_TOTAL_AMOUNT * comp_receive_payment_detail_historical_invoice.amountApplied AS DECIMAL(15,5)) * 100 / 6 AS DECIMAL(15,5)),
													CAST(INVOICE_TOTAL_TAX_AMOUNT / INVOICE_TOTAL_AMOUNT * comp_receive_payment_detail_historical_invoice.amountApplied AS DECIMAL(15,5)),
													comp_receive_payment_detail_historical_invoice.amountApplied,
													'Payment',
													IF(
														comp_sales_debt_recover.updateAble = 1,
														NULL,
														CONCAT(comp_sales_debt_recover.formNo, '[', LPAD(comp_sales_debt_recover.serialNo, 8, '0'), ']')
													),
													IF(
														comp_sales_debt_recover.updateAble = 1,
														NULL,
														DATE_FORMAT(comp_sales_debt_recover.transDate, '%d-%m-%Y')
													),
													comp_sales_debt_recover.updateAble
												FROM
													comp_receive_payment_detail_historical_invoice LEFT JOIN comp_receive_payment
													ON comp_receive_payment.ID = comp_receive_payment_detail_historical_invoice.paymentID LEFT OUTER JOIN comp_sales_debt_recover
													ON comp_sales_debt_recover.ID = comp_receive_payment_detail_historical_invoice.debtRecoverID LEFT JOIN comp_journal
													ON comp_journal.journalID = CONVERT(comp_sales_debt_recover.journalNo USING UTF8) AND
													comp_journal.companyID = comp_sales_debt_recover.companyID AND
													(comp_journal.`status` = 1 AND comp_journal.is_delete = 0)
												WHERE
													comp_receive_payment_detail_historical_invoice.ID = PAYMENT_ID;
											END IF;
										
										FETCH CURSOR_GET_PAYMENT INTO PAYMENT_ID;
										END LOOP comp_receive_payment_loop;
										CLOSE CURSOR_GET_PAYMENT;
									END BLOCK_PAYMENT;
									
									IF Category = 'relief' THEN
										INSERT Reports (
											EntityID,
											COL1, -- ID
											COL2, -- Entity Name
											COL3, -- Form No
											COL4, -- Date
											COL5, -- Base Amount
											COL6, -- Bad Debt Amount
											COL8, -- Document
											IsInvoice
										)
										SELECT
											ENTITY_ID,
											comp_historical_customer_invoice.ID,
											ENTITY_NAME,
											comp_historical_customer_invoice.customerPoNo,
											DATE_FORMAT(comp_historical_customer_invoice.salesInvoiceDate, '%d-%m-%Y'),
											CAST(comp_historical_customer_invoice.totalTaxAmount * 100 / 6 AS DECIMAL(15,5)),
											comp_historical_customer_invoice.totalTaxAmount,
											'Historical Invoice',
											1
										FROM
											comp_historical_customer_invoice
										WHERE
											comp_historical_customer_invoice.ID = INVOICE_ID;
									END IF;
									
								FETCH CURSOR_GET_INVOICE INTO INVOICE_ID, INVOICE_TOTAL_AMOUNT, INVOICE_TOTAL_TAX_AMOUNT;
								END LOOP comp_sales_invoice_loop;
								CLOSE CURSOR_GET_INVOICE;
							END BLOCK_HISTORICAL_INVOICE;
							
							FETCH CURSOR_GET_BAD_DEBT INTO BAD_DEBT_ID, BAD_DEBT_DATE;
						END LOOP comp_sales_bad_debt_loop;
						CLOSE CURSOR_GET_BAD_DEBT;
					END BLOCK_BAD_DEBT;
				END IF;
				
				FETCH CURSOR_GET_ENTITY INTO ENTITY_ID, ENTITY_NAME;
			END LOOP comp_customer_loop;
			CLOSE CURSOR_GET_ENTITY;
		END BLOCK_ENTITY;

	END IF;
	
	SELECT * FROM Reports;
END
//