DROP PROCEDURE IF EXISTS `sp_getAccountBalanceSummary`;

DELIMITER //

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_getAccountBalanceSummary`(
	IN iCompanyID INT,
	IN iYear INT,
	IN iMonth INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	DROP TEMPORARY TABLE IF EXISTS Results;
	CREATE TEMPORARY TABLE IF NOT EXISTS Results (
		iID INT DEFAULT 0,
		sCode CHAR(9) DEFAULT NULL,
		sName VARCHAR(128) DEFAULT '',
		iGroupID INT DEFAULT 0,
		iType INT DEFAULT 0,
		bStatus BIT DEFAULT 0,
		bTitle BIT DEFAULT 0,
		dBalance DECIMAL(15,5) DEFAULT 0
	);
	BLOCK1: BEGIN
		DECLARE DONE_BLOCK1 INT DEFAULT 0;
		DECLARE COA_ID INT;
		DECLARE COA_Code CHAR(9);
		DECLARE COA_Name VARCHAR(128);
		DECLARE COA_GroupID INT;
		DECLARE COA_Type INT;
		DECLARE COA_Status BIT;
		DECLARE COA_Title BIT;
		DECLARE CURSOR_ChartOfAccount CURSOR FOR
			SELECT
				comp_chart_of_acct.ID,
				comp_chart_of_acct.acctCode,
				comp_chart_of_acct.acctName,
				_ref_acct_group.ID,
				COALESCE(_ref_acct_type.ID, 0),
				_ref_acct_group.`status`,
				_ref_acct_group.title
			FROM comp_chart_of_acct LEFT JOIN _ref_acct_group
			ON _ref_acct_group.ID = comp_chart_of_acct.acctGroupID LEFT JOIN _ref_acct_type
			ON _ref_acct_type.ID = _ref_acct_group.acctTypeID
			WHERE
				comp_chart_of_acct.companyID = iCompanyID AND
				comp_chart_of_acct.is_delete = 0;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE_BLOCK1 = 1;
		
		OPEN CURSOR_ChartOfAccount;
		REPEAT
		FETCH CURSOR_ChartOfAccount INTO COA_ID, COA_Code, COA_Name, COA_GroupID, COA_Type, COA_Status, COA_Title;
		
		INSERT INTO Results (
			iID,
			sCode,
			sName,
			iGroupID,
			iType,
			bStatus,
			bTitle
		) VALUES (
			COA_ID,
			COA_Code,
			COA_Name,
			COA_GroupID,
			COA_Type,
			COA_Status,
			COA_Title
		);
		
		BLOCK2: BEGIN
			DECLARE DONE_BLOCK2 INT DEFAULT 0;
			DECLARE CAL_FirstYear INT;
			DECLARE CAL_LastYear INT;
			DECLARE CAL_Year INT;
			DECLARE CAL_Period INT;
			DECLARE CAL_StartPeriod INT;
			DECLARE CAL_TotalPeriod INT;
			DECLARE CURSOR_FinancialYear CURSOR FOR
			SELECT
				(SELECT MIN(a.financialYear) FROM comp_financial_year a WHERE a.companyID = comp_financial_year.companyID),
				(SELECT MAX(a.financialYear) FROM comp_financial_year a WHERE a.companyID = comp_financial_year.companyID),
				comp_financial_year.financialYear,
				MAX(comp_financial_year.period),
				MIN(comp_financial_year.period),
				COUNT(comp_financial_year.period)
			FROM
				comp_financial_year
			WHERE
				comp_financial_year.companyID = iCompanyID AND
				comp_financial_year.is_delete = 0 AND
				comp_financial_year.financialYear <= iYear
			GROUP BY
				comp_financial_year.financialYear
			ORDER BY
				comp_financial_year.financialYear ASC;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE_BLOCK2 = 1;
			
			OPEN CURSOR_FinancialYear;
			REPEAT
			FETCH CURSOR_FinancialYear INTO CAL_FirstYear, CAL_LastYear, CAL_Year, CAL_Period, CAL_StartPeriod, CAL_TotalPeriod;
			
			-- Get opening balance except for current year earning (130000000)
			IF CAL_Year = CAL_FirstYear AND COA_Code <> '130000000' AND COA_Type NOT IN (3,4) THEN
				UPDATE Results
				SET
					dBalance = dBalance + CAST(COALESCE((
						SELECT
							comp_opening_balance.debit - comp_opening_balance.credit
						FROM
							comp_opening_balance
						WHERE
							comp_opening_balance.companyID = iCompanyID AND
							comp_opening_balance.accountID = COA_ID AND
							comp_opening_balance.year = CAL_FirstYear
					), 0) AS DECIMAL(15,5))
				WHERE
					iID = COA_ID;
			END IF;
			
			-- Get retained year earning (120000000)
			IF COA_Code = '120000000' AND iYear <> CAL_FirstYear AND CAL_Year <> iYear THEN
				UPDATE Results
				SET
					dBalance = dBalance + CAST(COALESCE((
						SELECT
							SUM(comp_journal_detail.amount_dr) - SUM(comp_journal_detail.amount_cr)
						FROM
							comp_journal_detail LEFT JOIN comp_chart_of_acct
							ON comp_chart_of_acct.ID = comp_journal_detail.acctCode LEFT JOIN _ref_acct_group
							ON _ref_acct_group.ID = comp_chart_of_acct.acctGroupID
						WHERE
							comp_journal_detail.companyID = iCompanyID AND
							comp_journal_detail.is_delete = 0 AND
							comp_journal_detail.`status` = 1 AND
							comp_journal_detail.period <= (CAL_StartPeriod + CAL_TotalPeriod - 1) AND
							comp_journal_detail.`year` = CAL_Year AND
							_ref_acct_group.acctTypeID IN (3, 4)
					), 0) AS DECIMAL(15,5))
				WHERE
					iID = COA_ID;
			END IF;
			
			-- Get journal detail
			IF CAL_StartPeriod <> iYear AND COA_Type NOT IN (3,4) THEN -- If year not the same as provided year, get balance for all period
				UPDATE Results
				SET
					dBalance = dBalance + CAST(COALESCE((
						SELECT
							SUM(comp_journal_detail.amount_dr) - SUM(comp_journal_detail.amount_cr)
						FROM
							comp_journal_detail
						WHERE
							comp_journal_detail.companyID = iCompanyID AND
							comp_journal_detail.is_delete = 0 AND
							comp_journal_detail.`status` = 1 AND
							comp_journal_detail.period <= (CAL_StartPeriod + CAL_TotalPeriod - 1) AND
							comp_journal_detail.`year` = CAL_Year AND
							comp_journal_detail.acctCode = COA_ID
					), 0) AS DECIMAL(15,5))
				WHERE
					iID = COA_ID;
			ELSE -- Get balance until provided period
				UPDATE Results
				SET
					dBalance = dBalance + CAST(COALESCE((
						SELECT
							SUM(comp_journal_detail.amount_dr) - SUM(comp_journal_detail.amount_cr)
						FROM
							comp_journal_detail
						WHERE
							comp_journal_detail.companyID = iCompanyID AND
							comp_journal_detail.is_delete = 0 AND
							comp_journal_detail.`status` = 1 AND
							comp_journal_detail.period <= iMonth AND
							comp_journal_detail.`year` = iYear AND
							comp_journal_detail.acctCode = COA_ID
					), 0) AS DECIMAL(15,5))
				WHERE
					iID = COA_ID;
			END IF;
			
			UNTIL DONE_BLOCK2 OR CAL_Year = iYear END REPEAT;
			CLOSE CURSOR_FinancialYear;
		END BLOCK2;

		UNTIL DONE_BLOCK1 END REPEAT;
		CLOSE CURSOR_ChartOfAccount;
	END BLOCK1;
	
	SELECT * FROM Results ORDER BY sCode ASC;
END

//