DROP PROCEDURE IF EXISTS `sp_GetBadDebtEntity`;

DELIMITER //

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_GetBadDebtEntity`(
	IN `Type` VARCHAR(50),
	IN `Category` VARCHAR(50),
	IN `CompanyID` INT,
	IN `StartDate` DATE
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	DROP TEMPORARY TABLE IF EXISTS Results;
	CREATE TEMPORARY TABLE Results (
		ID INT UNSIGNED NOT NULL PRIMARY KEY,
		entityName VARCHAR(128),
		gstRegNo VARCHAR(16)
	);
	IF `Type` = 'customer' THEN
		BLOCK1: BEGIN
			DECLARE Done INT DEFAULT FALSE;
			DECLARE EntityID INT;
			DECLARE CURSOR_GetEntity CURSOR FOR
			SELECT
				customer.ID
			FROM
				customer
			WHERE
				customer.companyID = CompanyID AND
				customer.is_delete = 0;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = TRUE;
			
			OPEN CURSOR_GetEntity;
			FETCH CURSOR_GetEntity INTO EntityID;
			read_loop: LOOP
				IF Done THEN
					LEAVE read_loop;
				END IF;
				
				IF Category = 'relief' THEN
					IF EXISTS(
						SELECT
							comp_sales_invoice.*
						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.companyID = CompanyID AND
							comp_sales_invoice.customerID = EntityID AND
							comp_sales_invoice.salesInvoiceDate <= StartDate AND
							comp_sales_invoice.totalReceivable > 0 AND
							comp_sales_invoice.totalTaxAmount > 0 AND
							comp_sales_invoice.badDebtID = 0 AND
							(comp_sales_invoice.updateAble = 0 AND comp_sales_invoice.is_delete = 0) AND
							(comp_journal.`status` = 1 AND comp_journal.is_delete = 0)
					) THEN
						INSERT INTO Results
						SELECT
							customer.ID,
							customer.customerName,
							customer.gstRegNo
						FROM
							customer
						WHERE
							customer.ID = EntityID;
					END IF;
					
					IF EXISTS(
						SELECT
							comp_historical_customer_invoice.*
						FROM
							comp_historical_customer_invoice
						WHERE
							comp_historical_customer_invoice.companyID = CompanyID AND
							comp_historical_customer_invoice.customerID = EntityID AND
							comp_historical_customer_invoice.salesInvoiceDate <= StartDate AND
							comp_historical_customer_invoice.totalReceivable > 0 AND
							comp_historical_customer_invoice.totalTaxAmount > 0 AND
							comp_historical_customer_invoice.badDebtID = 0 AND
							comp_historical_customer_invoice.is_delete = 0
					) THEN
						INSERT INTO Results
						SELECT
							customer.ID,
							customer.customerName,
							customer.gstRegNo
						FROM
							customer
						WHERE
							customer.ID = EntityID
						ON DUPLICATE KEY UPDATE Results.ID = customer.ID, Results.entityName = customer.customerName, Results.gstRegNo = customer.gstRegNo;
					END IF;
				ELSE
					IF EXISTS(
						SELECT
							comp_receive_payment.*
						FROM
							comp_receive_payment_detail LEFT JOIN comp_receive_payment
							ON comp_receive_payment.ID = comp_receive_payment_detail.receivePaymentID LEFT JOIN comp_sales_invoice
							ON comp_sales_invoice.ID = comp_receive_payment_detail.invoiceID LEFT JOIN comp_sales_bad_debt
							ON comp_sales_bad_debt.ID = comp_sales_invoice.badDebtID LEFT JOIN comp_journal j1
							ON j1.journalID = CONVERT(comp_receive_payment.journalNo USING UTF8) AND j1.companyID = comp_receive_payment.companyID LEFT JOIN comp_journal j2
							ON j2.journalID = CONVERT(comp_sales_invoice.journalNo USING UTF8) AND j2.companyID = comp_sales_invoice.companyID LEFT JOIN comp_journal j3
							ON j3.journalID = CONVERT(comp_sales_bad_debt.journalNo USING UTF8) AND j3.companyID = comp_sales_bad_debt.companyID
						WHERE
							comp_receive_payment.companyID = CompanyID AND
							comp_receive_payment.customerID = EntityID AND
							comp_sales_bad_debt.transDate <= StartDate AND
							(comp_receive_payment.receivePaymentDate >= comp_sales_bad_debt.transDate AND comp_receive_payment.receivePaymentDate <= StartDate) AND
							comp_receive_payment_detail.debtRecoverID = 0 AND
							(comp_receive_payment.updateAble = 0 AND comp_receive_payment.is_delete = 0) AND
							(comp_sales_invoice.updateAble = 0 AND comp_sales_invoice.is_delete = 0) AND
							(comp_sales_bad_debt.updateAble = 0 AND comp_sales_bad_debt.is_delete = 0) AND
							(j1.`status` = 1 AND j1.is_delete = 0) AND
							(j2.`status` = 1 AND j2.is_delete = 0) AND
							(j3.`status` = 1 AND j3.is_delete = 0)
					) THEN
						INSERT INTO Results
						SELECT
							customer.ID,
							customer.customerName,
							customer.gstRegNo
						FROM
							customer
						WHERE
							customer.ID = EntityID;
					END IF;
					
					IF EXISTS(
						SELECT
							comp_sales_creditnote.*
						FROM
							comp_sales_creditnote LEFT JOIN comp_sales_invoice
							ON comp_sales_invoice.ID = comp_sales_creditnote.salesInvoiceID LEFT JOIN comp_sales_bad_debt
							ON comp_sales_bad_debt.ID = comp_sales_invoice.badDebtID LEFT JOIN comp_journal j1
							ON j1.journalID = CONVERT(comp_sales_creditnote.journalNo USING UTF8) AND j1.companyID = comp_sales_creditnote.companyID LEFT JOIN comp_journal j2
							ON j2.journalID = CONVERT(comp_sales_invoice.journalNo USING UTF8) AND j2.companyID = comp_sales_invoice.companyID LEFT JOIN comp_journal j3
							ON j3.journalID = CONVERT(comp_sales_bad_debt.journalNo USING UTF8) AND j3.companyID = comp_sales_bad_debt.companyID
						WHERE
							comp_sales_creditnote.companyID = CompanyID AND
							comp_sales_creditnote.customerID = EntityID AND
							comp_sales_bad_debt.transDate <= StartDate AND
							(comp_sales_creditnote.creditNoteDate >= comp_sales_bad_debt.transDate AND comp_sales_creditnote.creditNoteDate <= StartDate) AND
							comp_sales_creditnote.debtRecoverID = 0 AND
							(comp_sales_creditnote.updateAble = 0 AND comp_sales_creditnote.is_delete = 0) AND
							(comp_sales_invoice.updateAble = 0 AND comp_sales_invoice.is_delete = 0) AND
							(comp_sales_bad_debt.updateAble = 0 AND comp_sales_bad_debt.is_delete = 0) AND
							(j1.`status` = 1 AND j1.is_delete = 0) AND
							(j2.`status` = 1 AND j2.is_delete = 0) AND
							(j3.`status` = 1 AND j3.is_delete = 0)
					) THEN
						INSERT INTO Results
						SELECT
							customer.ID,
							customer.customerName,
							customer.gstRegNo
						FROM
							customer
						WHERE
							customer.ID = EntityID
						ON DUPLICATE KEY UPDATE Results.ID = customer.ID, Results.entityName = customer.customerName, Results.gstRegNo = customer.gstRegNo;
					END IF;
					
					IF EXISTS(
						SELECT
							comp_receive_payment.*
						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_historical_customer_invoice
							ON comp_historical_customer_invoice.ID = comp_receive_payment_detail_historical_invoice.historicalinvoiceID LEFT JOIN comp_sales_bad_debt
							ON comp_sales_bad_debt.ID = comp_historical_customer_invoice.badDebtID LEFT JOIN comp_journal j1
							ON j1.journalID = CONVERT(comp_receive_payment.journalNo USING UTF8) AND j1.companyID = comp_receive_payment.companyID LEFT JOIN comp_journal j2
							ON j2.journalID = CONVERT(comp_sales_bad_debt.journalNo USING UTF8) AND j2.companyID = comp_sales_bad_debt.companyID
						WHERE
							comp_receive_payment.companyID = CompanyID AND
							comp_receive_payment.customerID = EntityID AND
							comp_sales_bad_debt.transDate <= StartDate AND
							(comp_receive_payment.receivePaymentDate >= comp_sales_bad_debt.transDate AND comp_receive_payment.receivePaymentDate <= StartDate) AND
							comp_receive_payment_detail_historical_invoice.debtRecoverID = 0 AND
							(comp_receive_payment.updateAble = 0 AND comp_receive_payment.is_delete = 0) AND
							(comp_historical_customer_invoice.is_delete = 0) AND
							(j1.`status` = 1 AND j1.is_delete = 0) AND
							(j2.`status` = 1 AND j2.is_delete = 0)
					) THEN
						INSERT INTO Results
						SELECT
							customer.ID,
							customer.customerName,
							customer.gstRegNo
						FROM
							customer
						WHERE
							customer.ID = EntityID
						ON DUPLICATE KEY UPDATE Results.ID = customer.ID, Results.entityName = customer.customerName, Results.gstRegNo = customer.gstRegNo;
					END IF;
				END IF;
				
				FETCH CURSOR_GetEntity INTO EntityID;
			END LOOP read_loop;
			CLOSE CURSOR_GetEntity;
			SET Done = FALSE;
		END BLOCK1;
	ELSE
		BLOCK1: BEGIN
			DECLARE Done INT DEFAULT FALSE;
			DECLARE EntityID INT;
			DECLARE CURSOR_GetEntity CURSOR FOR
			SELECT
				supplier.ID
			FROM
				supplier
			WHERE
				supplier.companyID = CompanyID AND
				supplier.is_delete = 0;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = TRUE;
			
			OPEN CURSOR_GetEntity;
			FETCH CURSOR_GetEntity INTO EntityID;
			read_loop: LOOP
				IF Done THEN
					LEAVE read_loop;
				END IF;
				
				IF Category = 'relief' THEN
					IF EXISTS(
						SELECT
							comp_supplier_invoice.*
						FROM
							comp_supplier_invoice LEFT JOIN comp_journal
							ON comp_journal.journalID = CONVERT(comp_supplier_invoice.journalNo USING UTF8) AND comp_journal.companyID = comp_supplier_invoice.companyID
						WHERE
							comp_supplier_invoice.companyID = CompanyID AND
							comp_supplier_invoice.supplierID = EntityID AND
							comp_supplier_invoice.invoiceDate <= StartDate AND
							comp_supplier_invoice.totalPayable > 0 AND
							comp_supplier_invoice.totalTaxAmount > 0 AND
							comp_supplier_invoice.badDebtID = 0 AND
							(comp_supplier_invoice.updateAble = 0 AND comp_supplier_invoice.is_delete = 0) AND
							(comp_journal.`status` = 1 AND comp_journal.is_delete = 0)
					) THEN
						INSERT INTO Results
						SELECT
							supplier.ID,
							supplier.supplierName,
							supplier.gstRegNo
						FROM
							supplier
						WHERE
							supplier.ID = EntityID;
					END IF;
					
					IF EXISTS(
						SELECT
							comp_historical_supplier_invoice.*
						FROM
							comp_historical_supplier_invoice
						WHERE
							comp_historical_supplier_invoice.companyID = CompanyID AND
							comp_historical_supplier_invoice.supplierID = EntityID AND
							comp_historical_supplier_invoice.invoiceDate <= StartDate AND
							comp_historical_supplier_invoice.totalPayable > 0 AND
							comp_historical_supplier_invoice.totalTaxAmount > 0 AND
							comp_historical_supplier_invoice.badDebtID = 0 AND
							comp_historical_supplier_invoice.is_delete = 0
					) THEN
						INSERT INTO Results
						SELECT
							supplier.ID,
							supplier.supplierName,
							supplier.gstRegNo
						FROM
							supplier
						WHERE
							supplier.ID = EntityID
						ON DUPLICATE KEY UPDATE Results.ID = supplier.ID, Results.entityName = supplier.supplierName, Results.gstRegNo = supplier.gstRegNo;
					END IF;
				ELSE
					IF EXISTS(
						SELECT
							comp_payment.*
						FROM
							comp_payment_detail LEFT JOIN comp_payment
							ON comp_payment.ID = comp_payment_detail.paymentID LEFT JOIN comp_supplier_invoice
							ON comp_supplier_invoice.ID = comp_payment_detail.purchaseInvoiceID LEFT JOIN comp_purchase_bad_debt
							ON comp_purchase_bad_debt.ID = comp_supplier_invoice.badDebtID LEFT JOIN comp_journal j1
							ON j1.journalID = CONVERT(comp_payment.journalNo USING UTF8) AND j1.companyID = comp_payment.companyID LEFT JOIN comp_journal j2
							ON j2.journalID = CONVERT(comp_supplier_invoice.journalNo USING UTF8) AND j2.companyID = comp_supplier_invoice.companyID LEFT JOIN comp_journal j3
							ON j3.journalID = CONVERT(comp_purchase_bad_debt.journalNo USING UTF8) AND j3.companyID = comp_purchase_bad_debt.companyID
						WHERE
							comp_payment.companyID = CompanyID AND
							comp_payment.supplierID = EntityID AND
							comp_purchase_bad_debt.transDate <= StartDate AND
							(comp_payment.paymentDate >= comp_purchase_bad_debt.transDate AND comp_payment.paymentDate <= StartDate) AND
							comp_payment_detail.debtRecoverID = 0 AND
							(comp_payment.updateAble = 0 AND comp_payment.is_delete = 0) AND
							(comp_supplier_invoice.updateAble = 0 AND comp_supplier_invoice.is_delete = 0) AND
							(comp_purchase_bad_debt.updateAble = 0 AND comp_purchase_bad_debt.is_delete = 0) AND
							(j1.`status` = 1 AND j1.is_delete = 0) AND
							(j2.`status` = 1 AND j2.is_delete = 0) AND
							(j3.`status` = 1 AND j3.is_delete = 0)
					) THEN
						INSERT INTO Results
						SELECT
							supplier.ID,
							supplier.supplierName,
							supplier.gstRegNo
						FROM
							supplier
						WHERE
							supplier.ID = EntityID;
					END IF;
					
					IF EXISTS(
						SELECT
							comp_supplier_debitnote.*
						FROM
							comp_supplier_debitnote LEFT JOIN comp_supplier_invoice
							ON comp_supplier_invoice.ID = comp_supplier_debitnote.supplierInvoiceID LEFT JOIN comp_purchase_bad_debt
							ON comp_purchase_bad_debt.ID = comp_supplier_invoice.badDebtID LEFT JOIN comp_journal j1
							ON j1.journalID = CONVERT(comp_supplier_debitnote.journalNo USING UTF8) AND j1.companyID = comp_supplier_debitnote.companyID LEFT JOIN comp_journal j2
							ON j2.journalID = CONVERT(comp_supplier_invoice.journalNo USING UTF8) AND j2.companyID = comp_supplier_invoice.companyID LEFT JOIN comp_journal j3
							ON j3.journalID = CONVERT(comp_purchase_bad_debt.journalNo USING UTF8) AND j3.companyID = comp_purchase_bad_debt.companyID
						WHERE
							comp_supplier_debitnote.companyID = CompanyID AND
							comp_supplier_debitnote.supplierID = EntityID AND
							comp_purchase_bad_debt.transDate <= StartDate AND
							(comp_supplier_debitnote.debitNoteDate >= comp_purchase_bad_debt.transDate AND comp_supplier_debitnote.debitNoteDate <= StartDate) AND
							comp_supplier_debitnote.debtRecoverID = 0 AND
							(comp_supplier_debitnote.updateAble = 0 AND comp_supplier_debitnote.is_delete = 0) AND
							(comp_supplier_invoice.updateAble = 0 AND comp_supplier_invoice.is_delete = 0) AND
							(comp_purchase_bad_debt.updateAble = 0 AND comp_purchase_bad_debt.is_delete = 0) AND
							(j1.`status` = 1 AND j1.is_delete = 0) AND
							(j2.`status` = 1 AND j2.is_delete = 0) AND
							(j3.`status` = 1 AND j3.is_delete = 0)
					) THEN
						INSERT INTO Results
						SELECT
							supplier.ID,
							supplier.supplierName,
							supplier.gstRegNo
						FROM
							supplier
						WHERE
							supplier.ID = EntityID
						ON DUPLICATE KEY UPDATE Results.ID = supplier.ID, Results.entityName = supplier.supplierName, Results.gstRegNo = supplier.gstRegNo;
					END IF;
					
					IF EXISTS(
						SELECT
							comp_payment.*
						FROM
							comp_payment_detail_historical_invoice LEFT JOIN comp_payment
							ON comp_payment.ID = comp_payment_detail_historical_invoice.paymentID LEFT JOIN comp_historical_supplier_invoice
							ON comp_historical_supplier_invoice.ID = comp_payment_detail_historical_invoice.historicalinvoiceID LEFT JOIN comp_purchase_bad_debt
							ON comp_purchase_bad_debt.ID = comp_historical_supplier_invoice.badDebtID LEFT JOIN comp_journal j1
							ON j1.journalID = CONVERT(comp_payment.journalNo USING UTF8) AND j1.companyID = comp_payment.companyID LEFT JOIN comp_journal j2
							ON j2.journalID = CONVERT(comp_purchase_bad_debt.journalNo USING UTF8) AND j2.companyID = comp_purchase_bad_debt.companyID
						WHERE
							comp_payment.companyID = CompanyID AND
							comp_payment.supplierID = EntityID AND
							comp_purchase_bad_debt.transDate <= StartDate AND
							(comp_payment.paymentDate >= comp_purchase_bad_debt.transDate AND comp_payment.paymentDate <= StartDate) AND
							comp_payment_detail_historical_invoice.debtRecoverID = 0 AND
							(comp_payment.updateAble = 0 AND comp_payment.is_delete = 0) AND
							(comp_historical_supplier_invoice.updateAble = 0 AND comp_historical_supplier_invoice.is_delete = 0) AND
							(j1.`status` = 1 AND j1.is_delete = 0) AND
							(j2.`status` = 1 AND j2.is_delete = 0)
					) THEN
						INSERT INTO Results
						SELECT
							supplier.ID,
							supplier.customerName,
							supplier.gstRegNo
						FROM
							supplier
						WHERE
							supplier.ID = EntityID
						ON DUPLICATE KEY UPDATE Results.ID = supplier.ID, Results.entityName = supplier.supplierName, Results.gstRegNo = supplier.gstRegNo;
					END IF;
				END IF;
				
				FETCH CURSOR_GetEntity INTO EntityID;
			END LOOP read_loop;
			CLOSE CURSOR_GetEntity;
			SET Done = FALSE;
		END BLOCK1;
	END IF;
	SELECT * FROM Results ORDER BY entityName ASC;
END
//