USE husseinkhamisdb;

DELIMITER $$
DROP PROCEDURE IF EXISTS sp_BankReconcile;
$$

CREATE PROCEDURE sp_BankReconcile (
	IN bankReconcileId INT,
	IN companyId INT,
	IN bankLinkAcctCode INT,
	IN reconcileDate INT,
	IN reconcileBalance DOUBLE,
	IN concileDebit DOUBLE,
	IN concileCredibt DOUBLE,
	IN glBalance DOUBLE,
	IN actionBy INT,
	IN action INT
)
BEGIN
	IF action = 0 THEN #SELECT
		SELECT * FROM `comp_bank_reconcile` WHERE `ID` = bankReconcileId;
	ELSEIF action = 1 THEN #INSERT
		INSERT INTO `comp_bank_reconcile` (
			`companyId`,
			`bankLinkAcctCode`,
			`reconcileDate`,
			`reconcileBalance`,
			`concileDebit`,
			`concileCredit`,
			`glBalance`,
			`createdBy`
		) VALUES (
			companyId,
			bankLinkAcctCode,
			reconcileDate,
			reconcileBalance,
			concileDebit,
			concileCredit,
			glBalance,
			actionBy
		);
		
		SELECT * FROM `comp_bank_reconcile` WHERE `ID` = LAST_INSERT_ID();
	ELSEIF action = 2 THEN #UPDATE
		UPDATE `comp_bank_reconcile`
		SET
			`companyId` = companyId,
			`bankLinkAcctCode` = bankLinkAcctCode,
			`reconcileDate` = reconcileDate,
			`reconcileBalance` = reconcileBalance,
			`concileDebit` = concileDebit,
			`concileCredit` = concileCredit,
			`glBalance` = glBalance,
			`updatedBy` = actionBy
		WHERE
			`ID` = bankReconcileId;
	ELSEIF action = 3 THEN #DELETE
		DELETE FROM `comp_bank_reconcile` WHERE `ID` = bankReconcileId;
		DELETE FROM `comp_bank_reconcile_track_back` WHERE `reconID` = bankReconcileId;
		UPDATE `comp_cash_sales` SET `bankReconcileID` = 0 WHERE `bankReconcileID` = bankReconcileId;
		UPDATE `comp_receive_payment` SET `bankReconcileID` = 0 WHERE `bankReconcileID` = bankReconcileId;
		UPDATE `comp_receive_payment_others` SET `bankReconcileID` = 0 WHERE `bankReconcileID` = bankReconcileId;
		UPDATE `comp_payment` SET `bankReconcileID` = 0 WHERE `bankReconcileID` = bankReconcileId;
		UPDATE `comp_payment_others` SET `bankReconcileID` = 0 WHERE `bankReconcileID` = bankReconcileId;
		UPDATE `comp_journal_detail` SET `bankReconcileID` = 0 WHERE `bankReconcileID` = bankReconcileId;
		UPDATE `comp_unpresented_check` SET `bankReconcileID` = 0 WHERE `bankReconcileID` = bankReconcileId;
	END IF;
	
	SELECT ROW_COUNT();
END
$$