DROP PROCEDURE IF EXISTS `sp_FixBadDebtFormNo`;

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_FixBadDebtFormNo`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
	DECLARE Done BOOL DEFAULT FALSE;
	DECLARE CompanyID INT DEFAULT 0;
	DECLARE DocumentID INT DEFAULT 0;
	DECLARE JournalNo VARCHAR(128);
	DECLARE SerialNo INT DEFAULT 0;
	DECLARE Context TEXT;
	
	DECLARE FormControlToFixByCompanyID CURSOR FOR SELECT DISTINCT comp_form_control.companyID FROM comp_form_control;
	DECLARE InputReliefByID CURSOR FOR SELECT ID FROM comp_sales_bad_debt;
	DECLARE InputRecoverByID CURSOR FOR SELECT ID FROM comp_sales_debt_recover;
	DECLARE OutputReliefByID CURSOR FOR SELECT ID FROM comp_purchase_bad_debt;
	DECLARE OutputRecoverByID CURSOR FOR SELECT ID FROM comp_purchase_debt_recover;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = TRUE;
	
	DROP TEMPORARY TABLE IF EXISTS message;
	CREATE TEMPORARY TABLE message (
		CreatedOn TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Content TEXT
	);
	
	/* UPDATE master_code INPUT BAD DEBT RELIEF */
	INSERT INTO master_code (
		`ID`,
		`masterID`,
		`parentFilterID`,
		`code`,
		`name`,
		`shortName`,
		`description`,
		`orderNo`,
		`createdBy`,
		`createdTS`,
		`is_delete`
	) VALUES (
		160,
		18,
		NULL,
		'IRLF',
		'Input Bad Debt Relief',
		'IRLF',
		'Bad Debt Relief for Supplier Form',
		320,
		1,
		CURRENT_TIMESTAMP,
		0
	) ON DUPLICATE KEY
	UPDATE
		`code` = 'IRLF',
		`name` = 'Input Bad Debt Relief',
		`shortName` = 'IRLF',
		`description` = 'Bad Debt Relief for Supplier Form',
		`orderNo` = 320;
	INSERT INTO Message (Content) VALUES ('UPDATE master_code INPUT BAD DEBT RELIEF');
	/* UPDATE master_code INPUT BAD DEBT RECOVER */
	INSERT INTO master_code (
		`ID`,
		`masterID`,
		`parentFilterID`,
		`code`,
		`name`,
		`shortName`,
		`description`,
		`orderNo`,
		`createdBy`,
		`createdTS`,
		`is_delete`
	) VALUES (
		161,
		18,
		NULL,
		'IRCV',
		'Input Bad Debt Recovered',
		'IRCV',
		'Bad Debt Recovered for Supplier Form',
		330,
		1,
		CURRENT_TIMESTAMP,
		0
	) ON DUPLICATE KEY
	UPDATE
		`code` = 'IRCV',
		`name` = 'Input Bad Debt Recovered',
		`shortName` = 'IRCV',
		`description` = 'Bad Debt Recovered for Supplier Form',
		`orderNo` = 330;
	INSERT INTO Message (Content) VALUES ('UPDATE master_code INPUT BAD DEBT RECOVERED');
	/* UPDATE master_code OUTPUT BAD DEBT RELIEF */ 
	INSERT INTO master_code (
		`ID`,
		`masterID`,
		`parentFilterID`,
		`code`,
		`name`,
		`shortName`,
		`description`,
		`orderNo`,
		`createdBy`,
		`createdTS`,
		`is_delete`
	) VALUES (
		162,
		18,
		NULL,
		'ORLF',
		'Output Bad Debt Relief',
		'ORLF',
		'Bad Debt Relief for Customer Form',
		340,
		1,
		CURRENT_TIMESTAMP,
		0
	) ON DUPLICATE KEY
	UPDATE
		`code` = 'ORLF',
		`name` = 'Output Bad Debt Relief',
		`shortName` = 'ORLF',
		`description` = 'Bad Debt Relief for Customer Form',
		`orderNo` = 340;
	INSERT INTO Message (Content) VALUES ('UPDATE master_code OUTPUT BAD DEBT RELIEF');
	/* UPDATE master_code OUTPUT BAD DEBT RECOVER */
	INSERT INTO master_code (
		`ID`,
		`masterID`,
		`parentFilterID`,
		`code`,
		`name`,
		`shortName`,
		`description`,
		`orderNo`,
		`createdBy`,
		`createdTS`,
		`is_delete`
	) VALUES (
		163,
		18,
		NULL,
		'ORCV',
		'Output Bad Debt Recovered',
		'ORCV',
		'Bad Debt Recovered for Customer Form',
		350,
		1,
		CURRENT_TIMESTAMP,
		0
	) ON DUPLICATE KEY
	UPDATE
		`code` = 'ORCV',
		`name` = 'Output Bad Debt Recovered',
		`shortName` = 'ORCV',
		`description` = 'Bad Debt Recovered for Customer Form',
		`orderNo` = 350;
	INSERT INTO Message (Content) VALUES ('UPDATE master_code OUTPUT BAD DEBT RECOVERED');
	/* UPDATE TABLE comp_sales_bad_debt */
	IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_sales_bad_debt' AND COLUMN_NAME = 'serialNo') THEN
		ALTER TABLE comp_sales_bad_debt ADD serialNo INT NULL AFTER ID;
	END IF;
	IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_sales_bad_debt' AND COLUMN_NAME = 'formNo') THEN
		ALTER TABLE comp_sales_bad_debt ADD formNo VARCHAR(32) NULL AFTER serialNo;
	END IF;
	IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_sales_bad_debt' AND COLUMN_NAME = 'is_delete') THEN
		ALTER TABLE comp_sales_bad_debt ADD is_delete BOOL NOT NULL DEFAULT 0 AFTER updateAble;
	END IF;
	IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_sales_bad_debt' AND COLUMN_NAME = 'custID') THEN
		ALTER TABLE comp_sales_bad_debt CHANGE custID customerID INT;
	END IF;
	IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_sales_bad_debt' AND COLUMN_NAME = 'valueBadDebt') THEN
		ALTER TABLE comp_sales_bad_debt CHANGE valueBadDebt valueBadDebt DECIMAL(15,5);
	END IF;
	IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_sales_bad_debt' AND COLUMN_NAME = 'gstBadDebt') THEN
		ALTER TABLE comp_sales_bad_debt CHANGE gstBadDebt gstBadDebt DECIMAL(15,5);
	END IF;
	INSERT INTO Message (Content) VALUES ('UPDATE TABLE comp_sales_bad_debt');
	
	/* UPDATE TABLE comp_purchase_bad_debt */
	IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_purchase_bad_debt' AND COLUMN_NAME = 'serialNo') THEN
		ALTER TABLE comp_purchase_bad_debt ADD serialNo INT NULL AFTER ID;
	END IF;
	IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_purchase_bad_debt' AND COLUMN_NAME = 'formNo') THEN
		ALTER TABLE comp_purchase_bad_debt ADD formNo VARCHAR(32) NULL AFTER serialNo;
	END IF;
	IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_purchase_bad_debt' AND COLUMN_NAME = 'is_delete') THEN
		ALTER TABLE comp_purchase_bad_debt ADD is_delete BOOL NOT NULL DEFAULT 0 AFTER updateAble;
	END IF;
	IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_purchase_bad_debt' AND COLUMN_NAME = 'valueBadDebt') THEN
		ALTER TABLE comp_purchase_bad_debt CHANGE valueBadDebt valueBadDebt DECIMAL(15,5);
	END IF;
	IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_purchase_bad_debt' AND COLUMN_NAME = 'gstBadDebt') THEN
		ALTER TABLE comp_purchase_bad_debt CHANGE gstBadDebt gstBadDebt DECIMAL(15,5);
	END IF;
	INSERT INTO Message (Content) VALUES ('UPDATE TABLE comp_purchase_bad_debt');
	
	/* UPDATE TABLE comp_sales_debt_recover */
	IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_sales_debt_recover' AND COLUMN_NAME = 'serialNo') THEN
		ALTER TABLE comp_sales_debt_recover ADD serialNo INT NULL AFTER ID;
	END IF;
	IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_sales_debt_recover' AND COLUMN_NAME = 'formNo') THEN
		ALTER TABLE comp_sales_debt_recover ADD formNo VARCHAR(32) NULL AFTER serialNo;
	END IF;
	IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_sales_debt_recover' AND COLUMN_NAME = 'is_delete') THEN
		ALTER TABLE comp_sales_debt_recover ADD is_delete BOOL NOT NULL DEFAULT 0 AFTER updateAble;
	END IF;
	IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_sales_debt_recover' AND COLUMN_NAME = 'custID') THEN
		ALTER TABLE comp_sales_debt_recover CHANGE custID customerID INT;
	END IF;
	IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_sales_debt_recover' AND COLUMN_NAME = 'valueDebtRecover') THEN
		ALTER TABLE comp_sales_debt_recover CHANGE valueDebtRecover valueDebtRecover DECIMAL(15,5);
	END IF;
	IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_sales_debt_recover' AND COLUMN_NAME = 'gstDebtRecover') THEN
		ALTER TABLE comp_sales_debt_recover CHANGE gstDebtRecover gstDebtRecover DECIMAL(15,5);
	END IF;
	INSERT INTO Message (Content) VALUES ('UPDATE TABLE comp_sales_debt_recover');
	
	/* UPDATE TABLE comp_purchase_debt_recover */
	IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_purchase_debt_recover' AND COLUMN_NAME = 'serialNo') THEN
		ALTER TABLE comp_purchase_debt_recover ADD serialNo INT NULL AFTER ID;
	END IF;
	IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_purchase_debt_recover' AND COLUMN_NAME = 'formNo') THEN
		ALTER TABLE comp_purchase_debt_recover ADD formNo VARCHAR(32) NULL AFTER serialNo;
	END IF;
	IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_purchase_debt_recover' AND COLUMN_NAME = 'is_delete') THEN
		ALTER TABLE comp_purchase_debt_recover ADD is_delete BOOL NOT NULL DEFAULT 0 AFTER updateAble;
	END IF;
	IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_purchase_debt_recover' AND COLUMN_NAME = 'suppID') THEN
		ALTER TABLE comp_purchase_debt_recover CHANGE suppID supplierID INT;
	END IF;
	IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_purchase_debt_recover' AND COLUMN_NAME = 'valueDebtRecover') THEN
		ALTER TABLE comp_purchase_debt_recover CHANGE valueDebtRecover valueDebtRecover DECIMAL(15,5);
	END IF;
	IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_purchase_debt_recover' AND COLUMN_NAME = 'gstDebtRecover') THEN
		ALTER TABLE comp_purchase_debt_recover CHANGE gstDebtRecover gstDebtRecover DECIMAL(15,5);
	END IF;
	INSERT INTO Message (Content) VALUES ('UPDATE TABLE comp_purchase_debt_recover');
	
	/* UPDATE TABLE comp_historical_customer_invoice */
	IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_historical_customer_invoice' AND COLUMN_NAME = 'badDebtID') THEN
		ALTER TABLE comp_historical_customer_invoice ADD `badDebtID` INT NOT NULL DEFAULT '0' AFTER `updateAble`;
	END IF;
	INSERT INTO Message (Content) VALUES ('UPDATE TABLE comp_historical_customer_invoice');
	
	/* UPDATE TABLE comp_historical_supplier_invoice */
	IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_historical_supplier_invoice' AND COLUMN_NAME = 'badDebtID') THEN
		ALTER TABLE comp_historical_supplier_invoice ADD `badDebtID` INT NOT NULL DEFAULT '0' AFTER `updateAble`;
	END IF;
	IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_historical_supplier_invoice' AND COLUMN_NAME = 'badDebtID') THEN
		ALTER TABLE comp_historical_supplier_invoice ADD `taxID` INT NULL AFTER `supplierInvoiceNo`;
	END IF;
	INSERT INTO Message (Content) VALUES ('UPDATE TABLE comp_historical_supplier_invoice');
	
	/* UPDATE TABLE comp_sales_creditnote */
	IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_sales_creditnote' AND COLUMN_NAME = 'debtRecoverID') THEN
		ALTER TABLE comp_sales_creditnote ADD `debtRecoverID` INT NOT NULL DEFAULT '0' AFTER `journalNo`;
	END IF;
	INSERT INTO Message (Content) VALUES ('UPDATE TABLE comp_sales_creditnote');
	
	/* UPDATE TABLE comp_supplier_debitnote */
	IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'comp_supplier_debitnote' AND COLUMN_NAME = 'debtRecoverID') THEN
		ALTER TABLE comp_supplier_debitnote ADD `debtRecoverID` INT NOT NULL DEFAULT '0' AFTER `journalNo`;
	END IF;
	INSERT INTO Message (Content) VALUES ('UPDATE TABLE comp_supplier_debitnote');
	
	OPEN FormControlToFixByCompanyID;
	
	comp_form_control_loop: LOOP
		FETCH FormControlToFixByCompanyID INTO CompanyID;
		IF Done THEN
			CLOSE FormControlToFixByCompanyID;
			LEAVE comp_form_control_loop;
		END IF;
		
		/* OUTPUT BAD DEBT RELIEF */
		IF EXISTS(SELECT * FROM comp_form_control WHERE comp_form_control.companyID = CompanyID AND comp_form_control.formCode = 'SDR') THEN
			SET Context = CONCAT('CustomerBadDebtRelief = ', 'Available,Update Form;');
			UPDATE comp_form_control SET formCode = 'ORLF' WHERE comp_form_control.formCode = 'SDR';
		ELSEIF EXISTS(SELECT * FROM comp_form_control WHERE comp_form_control.companyID = CompanyID AND comp_form_control.formCode = 'ORLF') THEN
			SET Context = CONCAT('CustomerBadDebtRelief = ', 'Available;');
		ELSE
			SET Context = CONCAT('CustomerBadDebtRelief = ', 'Not Available;');
			INSERT INTO comp_form_control (
				`companyID`,
				`formID`,
				`formCode`,
				`formYear`,
				`formMonth`,
				`formDay`,
				`formInitial`,
				`formInitialNo`,
				`createdBy`,
				`createdTS`,
				`orderNo`
			)
			SELECT
				CompanyID,
				ID,
				code,
				0,
				0,
				0,
				'',
				1,
				1,
				CURRENT_TIMESTAMP,
				orderNo
			FROM master_code
			WHERE master_code.ID = 162;
		END IF;
		
		/* OUTPUT BAD DEBT RECOVER */
		IF EXISTS(SELECT * FROM comp_form_control WHERE comp_form_control.companyID = CompanyID AND comp_form_control.formCode = 'SBD') THEN
			SET Context = CONCAT(Context, 'CustomerBadDebtRecover = ', 'Available,Update Form;');
			UPDATE comp_form_control SET formCode = 'ORCV' WHERE comp_form_control.formCode = 'SBD';
		ELSEIF EXISTS(SELECT * FROM comp_form_control WHERE comp_form_control.companyID = CompanyID AND comp_form_control.formCode = 'ORCV') THEN
			SET Context = CONCAT(Context, 'CustomerBadDebtRecover = ', 'Available;');
		ELSE
			SET Context = CONCAT(Context, 'CustomerBadDebtRecover = ', 'Not Available;');
			INSERT INTO comp_form_control (
				`companyID`,
				`formID`,
				`formCode`,
				`formYear`,
				`formMonth`,
				`formDay`,
				`formInitial`,
				`formInitialNo`,
				`createdBy`,
				`createdTS`,
				`orderNo`
			)
			SELECT
				CompanyID,
				ID,
				code,
				0,
				0,
				0,
				'',
				1,
				1,
				CURRENT_TIMESTAMP,
				orderNo
			FROM master_code
			WHERE master_code.ID = 163;
		END IF;
		
		/* INPUT BAD DEBT RELIEF */
		IF EXISTS(SELECT * FROM comp_form_control WHERE comp_form_control.companyID = CompanyID AND comp_form_control.formCode = 'BD') THEN
			SET Context = CONCAT(Context, 'SupplierBadDebtRelief = ', 'Available,Update Form;');
			UPDATE comp_form_control SET formCode = 'IRLF' WHERE comp_form_control.formCode = 'BD';
		ELSEIF EXISTS(SELECT * FROM comp_form_control WHERE comp_form_control.companyID = CompanyID AND comp_form_control.formCode = 'IRLF') THEN
			SET Context = CONCAT(Context, 'SupplierBadDebtRelief = ', 'Available;');
		ELSE
			SET Context = CONCAT(Context, 'SupplierBadDebtRelief = ', 'Not Available;');
			INSERT INTO comp_form_control (
				`companyID`,
				`formID`,
				`formCode`,
				`formYear`,
				`formMonth`,
				`formDay`,
				`formInitial`,
				`formInitialNo`,
				`createdBy`,
				`createdTS`,
				`orderNo`
			)
			SELECT
				CompanyID,
				ID,
				code,
				0,
				0,
				0,
				'',
				1,
				1,
				CURRENT_TIMESTAMP,
				orderNo
			FROM master_code
			WHERE master_code.ID = 160;
		END IF;
		
		/* INPUT BAD DEBT RECOVER */
		IF EXISTS(SELECT * FROM comp_form_control WHERE comp_form_control.companyID = CompanyID AND comp_form_control.formCode = 'BDR') THEN
			SET Context = CONCAT(Context, 'SupplierBadDebtRecover = ', 'Available,Update Form;');
			UPDATE comp_form_control SET formCode = 'IRCV' WHERE comp_form_control.formCode = 'BDR';
		ELSEIF EXISTS(SELECT * FROM comp_form_control WHERE comp_form_control.companyID = CompanyID AND comp_form_control.formCode = 'IRCV') THEN
			SET Context = CONCAT(Context, 'SupplierBadDebtRecover = ', 'Available;');
		ELSE
			SET Context = CONCAT(Context, 'SupplierBadDebtRecover = ', 'Not Available;');
			INSERT INTO comp_form_control (
				`companyID`,
				`formID`,
				`formCode`,
				`formYear`,
				`formMonth`,
				`formDay`,
				`formInitial`,
				`formInitialNo`,
				`createdBy`,
				`createdTS`,
				`orderNo`
			)
			SELECT
				CompanyID,
				ID,
				code,
				0,
				0,
				0,
				'',
				1,
				1,
				CURRENT_TIMESTAMP,
				orderNo
			FROM master_code
			WHERE master_code.ID = 161;
		END IF;
		
		INSERT INTO Message (Content) VALUES (CONCAT(Context, 'CompanyID = ', CompanyID, ';'));
	END LOOP;
	
	/* CREATE VIEW view_serial_comp_sales_bad_debt */
	CREATE OR REPLACE VIEW view_serial_comp_sales_bad_debt AS
	SELECT
		company.ID AS companyID,
		IFNULL(
			((SELECT MAX(comp_sales_bad_debt.serialNo) FROM comp_sales_bad_debt WHERE comp_sales_bad_debt.companyID = company.ID) + 1),
			IFNULL(
				(SELECT comp_form_control.formInitialNo FROM comp_form_control WHERE comp_form_control.companyID = company.ID AND comp_form_control.formID = 162),
				1
			)
		) AS nextSerialNo
	FROM company;
	INSERT INTO Message (Content) VALUES ('CREATE VIEW view_serial_comp_sales_bad_debt');
	
	/* CREATE VIEW view_serial_comp_sales_debt_recover */
	CREATE OR REPLACE VIEW view_serial_comp_sales_debt_recover AS
	SELECT
		company.ID AS companyID,
		IFNULL(
			((SELECT MAX(comp_sales_debt_recover.serialNo) FROM comp_sales_debt_recover WHERE comp_sales_debt_recover.companyID = company.ID) + 1),
			IFNULL(
				(SELECT comp_form_control.formInitialNo FROM comp_form_control WHERE comp_form_control.companyID = company.ID AND comp_form_control.formID = 163),
				1
			)
		) AS nextSerialNo
	FROM company;
	INSERT INTO Message (Content) VALUES ('CREATE VIEW view_serial_comp_sales_debt_recover');
	
	/* CREATE VIEW view_serial_comp_purchase_bad_debt */
	CREATE OR REPLACE VIEW view_serial_comp_purchase_bad_debt AS
	SELECT
		company.ID AS companyID,
		IFNULL(
			((SELECT MAX(comp_purchase_bad_debt.serialNo) FROM comp_purchase_bad_debt WHERE comp_purchase_bad_debt.companyID = company.ID) + 1),
			IFNULL(
				(SELECT comp_form_control.formInitialNo FROM comp_form_control WHERE comp_form_control.companyID = company.ID AND comp_form_control.formID = 160),
				1
			)
		) AS nextSerialNo
	FROM company;
	INSERT INTO Message (Content) VALUES ('CREATE VIEW view_serial_comp_purchase_bad_debt');
	
	/* CREATE VIEW view_serial_comp_purchase_debt_recover */
	CREATE OR REPLACE VIEW view_serial_comp_purchase_debt_recover AS
	SELECT
		company.ID AS companyID,
		IFNULL(
			((SELECT MAX(comp_purchase_debt_recover.serialNo) FROM comp_purchase_debt_recover WHERE comp_purchase_debt_recover.companyID = company.ID) + 1),
			IFNULL(
				(SELECT comp_form_control.formInitialNo FROM comp_form_control WHERE comp_form_control.companyID = company.ID AND comp_form_control.formID = 161),
				1
			)
		) AS nextSerialNo
	FROM company;
	INSERT INTO Message (Content) VALUES ('CREATE VIEW view_serial_comp_purchase_debt_recover');
	
	/* UPDATE comp_sales_bad_debt */
	SET Done = FALSE;
	OPEN InputReliefByID;
	comp_sales_bad_debt_loop: LOOP
		FETCH InputReliefByID INTO DocumentID;
		IF Done THEN
			CLOSE InputReliefByID;
			LEAVE comp_sales_bad_debt_loop;
		END IF;
		
		IF EXISTS(SELECT * FROM comp_sales_bad_debt WHERE comp_sales_bad_debt.formNo IS NULL AND comp_sales_bad_debt.serialNo IS NULL AND comp_sales_bad_debt.ID = DocumentID) THEN
			UPDATE comp_sales_bad_debt a LEFT JOIN comp_form_control b
			ON b.companyID = a.companyID AND b.formID = 162 LEFT JOIN view_serial_comp_sales_bad_debt c
			ON c.companyID = a.companyID
			SET a.formNo = b.formCode, a.serialNo = c.nextSerialNo
			WHERE a.ID = DocumentID;
			INSERT INTO Message (Content) VALUES (CONCAT('UPDATE comp_sales_bad_debt;ID = ', DocumentID));
		END IF;
		
		IF EXISTS(SELECT * FROM comp_sales_bad_debt WHERE comp_sales_bad_debt.journalNo IS NOT NULL AND comp_sales_bad_debt.ID = DocumentID) THEN
			SELECT comp_sales_bad_debt.journalNo, comp_sales_bad_debt.companyID INTO JournalNo, CompanyID FROM comp_sales_bad_debt WHERE comp_sales_bad_debt.ID = DocumentID;
			UPDATE comp_journal LEFT JOIN comp_sales_bad_debt ON comp_sales_bad_debt.journalNo = comp_journal.journalID
			SET comp_journal.formNo = CONCAT(comp_sales_bad_debt.formNo, '[', lpad(comp_sales_bad_debt.serialNo, 8, '0'), ']')
			WHERE comp_journal.journalID = JournalNo AND comp_journal.companyID = CompanyID;
		END IF;
	END LOOP;
	
	/* UPDATE comp_sales_debt_recover */
	SET Done = FALSE;
	OPEN InputRecoverByID;
	comp_sales_debt_recover_loop: LOOP
		FETCH InputRecoverByID INTO DocumentID;
		IF Done THEN
			CLOSE InputRecoverByID;
			LEAVE comp_sales_debt_recover_loop;
		END IF;
		
		IF EXISTS(SELECT * FROM comp_sales_debt_recover WHERE comp_sales_debt_recover.formNo IS NULL AND comp_sales_debt_recover.serialNo IS NULL AND comp_sales_debt_recover.ID = DocumentID) THEN
			UPDATE comp_sales_debt_recover a LEFT JOIN comp_form_control b
			ON b.companyID = a.companyID AND b.formID = 163 LEFT JOIN view_serial_comp_sales_debt_recover c
			ON c.companyID = a.companyID
			SET a.formNo = b.formCode, a.serialNo = c.nextSerialNo
			WHERE a.ID = DocumentID;
			INSERT INTO Message (Content) VALUES (CONCAT('UPDATE comp_sales_debt_recover;ID = ', DocumentID));
		END IF;
		
		IF EXISTS(SELECT * FROM comp_sales_debt_recover WHERE comp_sales_debt_recover.journalNo IS NOT NULL AND comp_sales_debt_recover.ID = DocumentID) THEN
			SELECT comp_sales_debt_recover.journalNo, comp_sales_debt_recover.companyID INTO JournalNo, CompanyID FROM comp_sales_debt_recover WHERE comp_sales_debt_recover.ID = DocumentID;
			UPDATE comp_journal LEFT JOIN comp_sales_debt_recover ON comp_sales_debt_recover.journalNo = comp_journal.journalID
			SET comp_journal.formNo = CONCAT(comp_sales_debt_recover.formNo, '[', lpad(comp_sales_debt_recover.serialNo, 8, '0'), ']')
			WHERE comp_journal.journalID = JournalNo AND comp_journal.companyID = CompanyID;
		END IF;
	END LOOP;
	
	/* UPDATE comp_purchase_bad_debt */
	SET Done = FALSE;
	OPEN OutputReliefByID;
	comp_purchase_bad_debt_loop: LOOP
		FETCH OutputReliefByID INTO DocumentID;
		IF Done THEN
			CLOSE OutputReliefByID;
			LEAVE comp_purchase_bad_debt_loop;
		END IF;
		
		IF EXISTS(SELECT * FROM comp_purchase_bad_debt WHERE comp_purchase_bad_debt.formNo IS NULL AND comp_purchase_bad_debt.serialNo IS NULL AND comp_purchase_bad_debt.ID = DocumentID) THEN
			UPDATE comp_purchase_bad_debt a LEFT JOIN comp_form_control b
			ON b.companyID = a.companyID AND b.formID = 160 LEFT JOIN view_serial_comp_purchase_bad_debt c
			ON c.companyID = a.companyID
			SET a.formNo = b.formCode, a.serialNo = c.nextSerialNo
			WHERE a.ID = DocumentID;
			INSERT INTO Message (Content) VALUES (CONCAT('UPDATE comp_purchase_bad_debt;ID = ', DocumentID));
		END IF;
		
		IF EXISTS(SELECT * FROM comp_purchase_bad_debt WHERE comp_purchase_bad_debt.journalNo IS NOT NULL AND comp_purchase_bad_debt.ID = DocumentID) THEN
			SELECT comp_purchase_bad_debt.journalNo, comp_purchase_bad_debt.companyID INTO JournalNo, CompanyID FROM comp_purchase_bad_debt WHERE comp_purchase_bad_debt.ID = DocumentID;
			UPDATE comp_journal LEFT JOIN comp_purchase_bad_debt ON comp_purchase_bad_debt.journalNo = comp_journal.journalID
			SET comp_journal.formNo = CONCAT(comp_purchase_bad_debt.formNo, '[', lpad(comp_purchase_bad_debt.serialNo, 8, '0'), ']')
			WHERE comp_journal.journalID = JournalNo AND comp_journal.companyID = CompanyID;
		END IF;
	END LOOP;
	
	/* UPDATE comp_purchase_debt_recover */
	SET Done = FALSE;
	OPEN OutputRecoverByID;
	comp_purchase_debt_recover_loop: LOOP
		FETCH OutputRecoverByID INTO DocumentID;
		IF Done THEN
			CLOSE OutputRecoverByID;
			LEAVE comp_purchase_debt_recover_loop;
		END IF;
		
		IF EXISTS(SELECT * FROM comp_purchase_debt_recover WHERE comp_purchase_debt_recover.formNo IS NULL AND comp_purchase_debt_recover.serialNo IS NULL AND comp_purchase_debt_recover.ID = DocumentID) THEN
			UPDATE comp_purchase_debt_recover a LEFT JOIN comp_form_control b
			ON b.companyID = a.companyID AND b.formID = 161 LEFT JOIN view_serial_comp_purchase_debt_recover c
			ON c.companyID = a.companyID
			SET a.formNo = b.formCode, a.serialNo = c.nextSerialNo
			WHERE a.ID = DocumentID;
			INSERT INTO Message (Content) VALUES (CONCAT('UPDATE comp_purchase_debt_recover;ID = ', DocumentID));
		END IF;
		
		IF EXISTS(SELECT * FROM comp_purchase_debt_recover WHERE comp_purchase_debt_recover.journalNo IS NOT NULL AND comp_purchase_debt_recover.ID = DocumentID) THEN
			SELECT comp_purchase_debt_recover.journalNo, comp_purchase_debt_recover.companyID INTO JournalNo, CompanyID FROM comp_purchase_debt_recover WHERE comp_purchase_debt_recover.ID = DocumentID;
			UPDATE comp_journal LEFT JOIN comp_purchase_debt_recover ON comp_purchase_debt_recover.journalNo = comp_journal.journalID
			SET comp_journal.formNo = CONCAT(comp_purchase_debt_recover.formNo, '[', lpad(comp_purchase_debt_recover.serialNo, 8, '0'), ']')
			WHERE comp_journal.journalID = JournalNo AND comp_journal.companyID = CompanyID;
		END IF;
	END LOOP;
	
	/* UPDATE _sys_menu */
	INSERT INTO Message (Content) VALUES (CONCAT('UPDATE _sys_menu'));
	IF EXISTS(SELECT * FROM _sys_menu WHERE code = 'GSTHOME') THEN
		UPDATE _sys_menu SET urls = '#' WHERE code = 'GSTHOME';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE code IN ('GSBADRLF', 'CUSRLFTX')) THEN
		UPDATE
			_sys_menu
		SET
			code = 'GSTRLF',
			name = 'BadDebtRelief',
			urls = 'gstReport/BadDebtRelief',
			seq = 13,
			access = NULL,
			icons = 'icon-active.png'
		WHERE
			code IN ('GSBADRLF', 'CUSRLFTX');
		UPDATE user_module_access_details SET menuCode = 'GSTRLF' WHERE menuCode IN ('GSBADRLF', 'CUSRLFTX');
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE code IN ('BADBTRLC', 'CUSRLFCF')) THEN
		UPDATE
			_sys_menu
		SET
			code = 'GSTRLFCF',
			name = 'BadDebtReliefConfirm',
			urls = 'gstReport/BadDebtReliefConfirm',
			seq = 14,
			access = NULL,
			icons = 'icon-active.png'
		WHERE
			code IN ('BADBTRLC', 'CUSRLFCF');
		UPDATE user_module_access_details SET menuCode = 'GSTRLFCF' WHERE menuCode IN ('BADBTRLC', 'CUSRLFCF');
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE code IN ('GSBADBTR', 'CUSCVRTX')) THEN
		UPDATE
			_sys_menu
		SET
			code = 'GSTRCV',
			name = 'BadDebtRecovered',
			urls = 'gstReport/BadDebtRecovered',
			seq = 15,
			access = NULL,
			icons = 'icon-active.png'
		WHERE
			code IN ('GSBADBTR', 'CUSCVRTX');
		UPDATE user_module_access_details SET menuCode = 'GSTRCV' WHERE menuCode IN ('GSBADBTR', 'CUSCVRTX');
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE code IN ('BDEBRCCO', 'CUSCVRCF')) THEN
		UPDATE
			_sys_menu
		SET
			code = 'GSTRCVCF',
			name = 'BadDebtRecoveredConfirm',
			urls = 'gstReport/BadDebtRecoveredConfirm',
			seq = 16,
			access = NULL,
			icons = 'icon-active.png'
		WHERE
			code IN ('BDEBRCCO', 'CUSCVRCF');
		UPDATE user_module_access_details SET menuCode = 'GSTRCVCF' WHERE menuCode IN ('BDEBRCCO', 'CUSCVRCF');
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'SupplierBadDebtRelief') THEN
		DELETE FROM _sys_menu WHERE name = 'SupplierBadDebtRelief';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name IN ('SupplierBadDebtReliefConfirm', 'suppbadDebtreliefconfirm')) THEN
		DELETE FROM _sys_menu WHERE name IN ('SupplierBadDebtReliefConfirm', 'suppbadDebtreliefconfirm');
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name IN ('SupplierBadDebtRecover', 'suppbadDebtRecover')) THEN
		DELETE FROM _sys_menu WHERE name IN ('SupplierBadDebtRecover', 'suppbadDebtRecover');
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name IN ('SupplierBadDebtRecoverConfirm', 'suppbadDebtRecoverConfirm')) THEN
		DELETE FROM _sys_menu WHERE name IN ('SupplierBadDebtRecoverConfirm', 'suppbadDebtRecoverConfirm');
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'baddebtrecoverrpt') THEN
		DELETE FROM _sys_menu WHERE name = 'baddebtrecoverrpt';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'baddebtreliefrpt') THEN
		DELETE FROM _sys_menu WHERE name = 'baddebtreliefrpt';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'CustomerBadDebtReliefReport') THEN
		DELETE FROM _sys_menu WHERE name = 'CustomerBadDebtReliefReport';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'CustomerBadDebtRecoverReport') THEN
		DELETE FROM _sys_menu WHERE name = 'CustomerBadDebtRecoverReport';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'SupplierBadDebtReliefReport') THEN
		DELETE FROM _sys_menu WHERE name = 'SupplierBadDebtReliefReport';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'SupplierBadDebtRecoverReport') THEN
		DELETE FROM _sys_menu WHERE name = 'SupplierBadDebtRecoverReport';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'BadDebtMonitoringReport') THEN
		DELETE FROM _sys_menu WHERE name = 'BadDebtMonitoringReport';
	END IF;
	
	IF NOT EXISTS(SELECT * FROM _sys_menu WHERE code = 'GSTRLFRP') THEN
		INSERT INTO _sys_menu
		(`code`,`name`,`parents`,`module`,`submenu`,`urls`,`access`,`seq`,`display`,`icons`,`language`)
		VALUES
		('GSTRLFRP', 'BadDebtReliefReport', 103, 6, 0, 'gstReport/BadDebtReliefReport', NULL, 34, 1, 'icon-active.png', 'EN');
	END IF;
	
	IF NOT EXISTS(SELECT * FROM _sys_menu WHERE code = 'GSTRCVRP') THEN
		INSERT INTO _sys_menu
		(`code`,`name`,`parents`,`module`,`submenu`,`urls`,`access`,`seq`,`display`,`icons`,`language`)
		VALUES
		('GSTRCVRP', 'BadDebtRecoveredReport', 103, 6, 0, 'gstReport/BadDebtRecoveredReport', NULL, 35, 1, 'icon-active.png', 'EN');
	END IF;
	
	IF NOT EXISTS(SELECT * FROM _sys_menu WHERE code = 'GSTMTRRP') THEN
		INSERT INTO _sys_menu
		(`code`,`name`,`parents`,`module`,`submenu`,`urls`,`access`,`seq`,`display`,`icons`,`language`)
		VALUES
		('GSTMTRRP', 'BadDebtMonitoring', 103, 6, 0, 'gstReport/BadDebtMonitoring', NULL, 36, 1, 'icon-active.png', 'EN');
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'gstjournal') THEN
		UPDATE _sys_menu SET name = 'GSTJournal', seq = 11 WHERE name = 'gstjournal';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'gstjournalconfirm') THEN
		UPDATE _sys_menu SET name = 'GSTJournalConfirm', seq = 12 WHERE name = 'gstjournalconfirm';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'gstReport') THEN
		UPDATE _sys_menu SET name = 'GSTReport', seq = 30 WHERE name = 'gstReport';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'gstReportSummary') THEN
		UPDATE _sys_menu SET name = 'GSTReportSummary', seq = 31 WHERE name = 'gstReportSummary';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'gst03') THEN
		UPDATE _sys_menu SET name = 'GST03', seq = 32 WHERE name = 'gst03';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'gst03Summary') THEN
		UPDATE _sys_menu SET name = 'GST03Summary', seq = 33 WHERE name = 'gst03Summary';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'Gsttable') THEN
		UPDATE _sys_menu SET name = 'TaxTableList', seq = 50 WHERE name = 'Gsttable';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'taxclass') THEN
		UPDATE _sys_menu SET name = 'TaxClassList', seq = 51 WHERE name = 'taxclass';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'taxgroup') THEN
		UPDATE _sys_menu SET name = 'TaxGroupList', seq = 52 WHERE name = 'taxgroup';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'taxtype') THEN
		UPDATE _sys_menu SET name = 'TaxTypeList', seq = 53 WHERE name = 'taxtype';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'gstpreference') THEN
		UPDATE _sys_menu SET name = 'GSTPreference', seq = 54 WHERE name = 'gstpreference';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'gsttaxmapping') THEN
		UPDATE _sys_menu SET name = 'GSTTaxMapping', seq = 55 WHERE name = 'gsttaxmapping';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'GSTHOME') THEN
		UPDATE _sys_menu SET seq = 1 WHERE name = 'GSTHOME';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'GSTTRANS') THEN
		UPDATE _sys_menu SET seq = 2 WHERE name = 'GSTTRANS';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'GSTRPT') THEN
		UPDATE _sys_menu SET seq = 3 WHERE name = 'GSTRPT';
	END IF;
	
	IF EXISTS(SELECT * FROM _sys_menu WHERE name = 'GSTSETUP') THEN
		UPDATE _sys_menu SET seq = 4 WHERE name = 'GSTSETUP';
	END IF;
	
	SELECT * FROM message;
END
//

CALL sp_FixBadDebtFormNo();;