DROP PROCEDURE IF EXISTS `sp_GetCustomerStatementByDate`;

DELIMITER //

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_GetCustomerStatementByDate`(
	IN `CompanyID` INT,
	IN `CustomerID` INT,
	IN `StartDate` DATE,
	IN `EndDate` DATE,
	IN `JournalDetail` BIT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	DECLARE DOCUMENT_DATE DATE;
	DECLARE DOCUMENT_FORM_NO VARCHAR(128);
	DECLARE DOCUMENT_REFERENCE VARCHAR(128);
	DECLARE DOCUMENT_AMOUNT DECIMAL(15,5) DEFAULT 0;
	DECLARE BALANCE DECIMAL(15,5) DEFAULT 0;
	DECLARE JOURNAL_POSTED BIT DEFAULT 0;
	DECLARE DEBIT DECIMAL(15,5) DEFAULT 0;
	DECLARE CREDIT DECIMAL(15,5) DEFAULT 0;
	DROP TEMPORARY TABLE IF EXISTS Statements;
	CREATE TEMPORARY TABLE Statements (
		TransDate DATE NULL,
		FormNo VARCHAR(128) NULL,
		Reference VARCHAR(128) NULL,
		Description VARCHAR(128) NULL,
		Debit DECIMAL(15,5) NOT NULL DEFAULT 0,
		Credit DECIMAL(15,5) NOT NULL DEFAULT 0,
		Posted BIT NOT NULL DEFAULT 0,
		IsOpeningBalance BIT NOT NULL DEFAULT 0,
		OpeningBalance DECIMAL(15,5) DEFAULT 0
	);
	
	BLOCK_HISTORICAL_INVOICE: BEGIN
		DECLARE DONE_GET_HISTORICAL_INVOICE INT DEFAULT FALSE;
		DECLARE CURSOR_GET_HISTORICAL_INVOICE CURSOR FOR
		SELECT
			comp_historical_customer_invoice.salesInvoiceDate,
			CONCAT(comp_historical_customer_invoice.formNo, '[', LPAD(comp_historical_customer_invoice.serialNo, 8, '0'), ']'),
			comp_historical_customer_invoice.customerPoNo,
			CAST(comp_historical_customer_invoice.totalAmount AS DECIMAL(15,5))
		FROM
			comp_historical_customer_invoice
		WHERE
			comp_historical_customer_invoice.companyID = `CompanyID` AND
			comp_historical_customer_invoice.customerID = `CustomerID` AND
			comp_historical_customer_invoice.is_delete = 0 AND
			comp_historical_customer_invoice.salesInvoiceDate BETWEEN `StartDate` AND `EndDate`
		ORDER BY
			comp_historical_customer_invoice.salesInvoiceDate ASC;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE_GET_HISTORICAL_INVOICE = TRUE;
		
		OPEN CURSOR_GET_HISTORICAL_INVOICE;
		FETCH CURSOR_GET_HISTORICAL_INVOICE INTO DOCUMENT_DATE, DOCUMENT_FORM_NO, DOCUMENT_REFERENCE, DOCUMENT_AMOUNT;
		comp_historical_customer_invoice_loop: LOOP
			IF DONE_GET_HISTORICAL_INVOICE THEN
				LEAVE comp_historical_customer_invoice_loop;
			END IF;
			
			INSERT INTO Statements (
				TransDate,
				FormNo,
				Reference,
				Description,
				Debit,
				Posted
			)
			VALUES (
				DOCUMENT_DATE,
				DOCUMENT_FORM_NO,
				DOCUMENT_REFERENCE,
				'Historical Invoice',
				DOCUMENT_AMOUNT,
				1
			);
			
			SET DOCUMENT_AMOUNT = 0;
		
		FETCH CURSOR_GET_HISTORICAL_INVOICE INTO DOCUMENT_DATE, DOCUMENT_FORM_NO, DOCUMENT_REFERENCE, DOCUMENT_AMOUNT;
		END LOOP comp_historical_customer_invoice_loop;
		CLOSE CURSOR_GET_HISTORICAL_INVOICE;
		
		SET BALANCE = BALANCE + COALESCE((
			SELECT
				CAST(SUM(comp_historical_customer_invoice.totalAmount) AS DECIMAL(15,5))
			FROM
				comp_historical_customer_invoice
			WHERE
				comp_historical_customer_invoice.companyID = `CompanyID` AND
				comp_historical_customer_invoice.customerID = `CustomerID` AND
				comp_historical_customer_invoice.is_delete = 0 AND
				comp_historical_customer_invoice.salesInvoiceDate < `StartDate`
			GROUP BY
				comp_historical_customer_invoice.companyID, comp_historical_customer_invoice.customerID
		), 0);
	END BLOCK_HISTORICAL_INVOICE;
	
	BLOCK_INVOICE: BEGIN
		DECLARE DONE_GET_INVOICE INT DEFAULT FALSE;
		DECLARE CURSOR_GET_INVOICE CURSOR FOR
		SELECT
			comp_sales_invoice.salesInvoiceDate,
			CONCAT(comp_sales_invoice.formNo, '[', LPAD(comp_sales_invoice.serialNo, 8, '0'), ']'),
			comp_sales_invoice.customerPoNo,
			CAST(comp_sales_invoice.totalamount AS DECIMAL(15,5)),
			COALESCE(comp_journal.`status`, 0)
		FROM
			comp_sales_invoice LEFT OUTER 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 = `CustomerID` AND
			(comp_sales_invoice.updateAble = 0 AND comp_sales_invoice.is_delete = 0) AND
			(comp_journal.is_delete = 0) AND
			comp_sales_invoice.salesInvoiceDate BETWEEN `StartDate` AND `EndDate`
		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 DOCUMENT_DATE, DOCUMENT_FORM_NO, DOCUMENT_REFERENCE, DOCUMENT_AMOUNT, JOURNAL_POSTED;
		comp_sales_invoice_loop: LOOP
			IF DONE_GET_INVOICE THEN
				LEAVE comp_sales_invoice_loop;
			END IF;
			
			INSERT INTO Statements (
				TransDate,
				FormNo,
				Reference,
				Description,
				Debit,
				Posted
			)
			VALUES (
				DOCUMENT_DATE,
				DOCUMENT_FORM_NO,
				DOCUMENT_REFERENCE,
				'Invoice',
				DOCUMENT_AMOUNT,
				JOURNAL_POSTED
			);
			
			SET DOCUMENT_AMOUNT = 0;
		
		FETCH CURSOR_GET_INVOICE INTO DOCUMENT_DATE, DOCUMENT_FORM_NO, DOCUMENT_REFERENCE, DOCUMENT_AMOUNT, JOURNAL_POSTED;
		END LOOP comp_sales_invoice_loop;
		CLOSE CURSOR_GET_INVOICE;
		
		SET BALANCE = BALANCE + COALESCE((
			SELECT
				CAST(SUM(comp_sales_invoice.totalamount) AS DECIMAL(15,5))
			FROM
				comp_sales_invoice
			WHERE
				comp_sales_invoice.companyID = `CompanyID` AND
				comp_sales_invoice.customerID = `CustomerID` AND
				(comp_sales_invoice.updateAble = 0 AND comp_sales_invoice.is_delete = 0) AND
				comp_sales_invoice.salesInvoiceDate < `StartDate`
			GROUP BY
				comp_sales_invoice.companyID, comp_sales_invoice.customerID
		), 0);
	END BLOCK_INVOICE;
	
	BLOCK_DEBIT_NOTE: BEGIN
		DECLARE DONE_GET_DEBIT_NOTE INT DEFAULT FALSE;
		DECLARE CURSOR_GET_DEBIT_NOTE CURSOR FOR
		SELECT
			comp_sales_debitnote.debitNoteDate,
			CONCAT(comp_sales_debitnote.formNo, '[', LPAD(comp_sales_debitnote.serialNo, 8, '0'), ']'),
			CONCAT(comp_sales_invoice.formNo, '[', LPAD(comp_sales_invoice.serialNo, 8, '0'), ']'),
			CAST(comp_sales_debitnote.totalAmount AS DECIMAL(15,5)),
			COALESCE(comp_journal.`status`, 0)
		FROM
			comp_sales_debitnote LEFT OUTER JOIN comp_journal
			ON comp_journal.journalID = CONVERT(comp_sales_debitnote.journalNo USING utf8) AND
			comp_journal.companyID = comp_sales_debitnote.companyID LEFT JOIN comp_sales_invoice
			ON comp_sales_invoice.ID = comp_sales_debitnote.salesInvoiceID
		WHERE
			comp_sales_debitnote.companyID = `CompanyID` AND
			comp_sales_debitnote.customerID = `CustomerID` AND
			(comp_sales_debitnote.updateAble = 0 AND comp_sales_debitnote.is_delete = 0) AND
			(comp_journal.is_delete = 0) AND
			comp_sales_debitnote.debitNoteDate BETWEEN `StartDate` AND `EndDate`
		ORDER BY
			comp_sales_debitnote.debitNoteDate ASC;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE_GET_DEBIT_NOTE = TRUE;
		
		OPEN CURSOR_GET_DEBIT_NOTE;
		FETCH CURSOR_GET_DEBIT_NOTE INTO DOCUMENT_DATE, DOCUMENT_FORM_NO, DOCUMENT_REFERENCE, DOCUMENT_AMOUNT, JOURNAL_POSTED;
		comp_sales_debitnote_loop: LOOP
			IF DONE_GET_DEBIT_NOTE THEN
				LEAVE comp_sales_debitnote_loop;
			END IF;
			
			INSERT INTO Statements (
				TransDate,
				FormNo,
				Reference,
				Description,
				Debit,
				Posted
			)
			VALUES (
				DOCUMENT_DATE,
				DOCUMENT_FORM_NO,
				DOCUMENT_REFERENCE,
				'Debit Note',
				DOCUMENT_AMOUNT,
				JOURNAL_POSTED
			);
			
			SET DOCUMENT_AMOUNT = 0;
		
		FETCH CURSOR_GET_DEBIT_NOTE INTO DOCUMENT_DATE, DOCUMENT_FORM_NO, DOCUMENT_REFERENCE, DOCUMENT_AMOUNT, JOURNAL_POSTED;
		END LOOP comp_sales_debitnote_loop;
		CLOSE CURSOR_GET_DEBIT_NOTE;
		
		SET BALANCE = BALANCE + COALESCE((
			SELECT
				CAST(SUM(comp_sales_debitnote.totalAmount) AS DECIMAL(15,5))
			FROM
				comp_sales_debitnote
			WHERE
				comp_sales_debitnote.companyID = `CompanyID` AND
				comp_sales_debitnote.customerID = `CustomerID` AND
				(comp_sales_debitnote.updateAble = 0 AND comp_sales_debitnote.is_delete = 0) AND
				comp_sales_debitnote.debitNoteDate < `StartDate`
			GROUP BY
				comp_sales_debitnote.companyID, comp_sales_debitnote.customerID
		), 0);
	END BLOCK_DEBIT_NOTE;
	
	BLOCK_CREDIT_NOTE: BEGIN
		DECLARE DONE_GET_CREDIT_NOTE INT DEFAULT FALSE;
		DECLARE CURSOR_GET_CREDIT_NOTE CURSOR FOR
		SELECT
			comp_sales_creditnote.creditNoteDate,
			CONCAT(comp_sales_creditnote.formNo, '[', LPAD(comp_sales_creditnote.serialNo, 8, '0'), ']'),
			IF(
				comp_sales_creditnote.isHistoricalInvoice = 1,
				CONCAT(comp_historical_customer_invoice.formNo, '[', LPAD(comp_historical_customer_invoice.serialNo, 8, '0'), ']'),
				CONCAT(comp_sales_invoice.formNo, '[', LPAD(comp_sales_invoice.serialNo, 8, '0'), ']')
			),
			CAST(comp_sales_creditnote.totalAmount AS DECIMAL(15,5)),
			COALESCE(comp_journal.`status`, 0)
		FROM
			comp_sales_creditnote LEFT OUTER JOIN comp_journal
			ON comp_journal.journalID = CONVERT(comp_sales_creditnote.journalNo USING utf8) AND
			comp_journal.companyID = comp_sales_creditnote.companyID LEFT OUTER JOIN comp_sales_invoice
			ON comp_sales_invoice.ID = comp_sales_creditnote.salesInvoiceID LEFT OUTER JOIN comp_historical_customer_invoice
			ON comp_historical_customer_invoice.ID = comp_sales_creditnote.salesInvoiceID
		WHERE
			comp_sales_creditnote.companyID = `CompanyID` AND
			comp_sales_creditnote.customerID = `CustomerID` AND
			(comp_sales_creditnote.updateAble = 0 AND comp_sales_creditnote.is_delete = 0) AND
			(comp_journal.is_delete = 0) AND
			comp_sales_creditnote.creditNoteDate BETWEEN `StartDate` AND `EndDate`
		ORDER BY
			comp_sales_creditnote.creditNoteDate ASC;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE_GET_CREDIT_NOTE = TRUE;
		
		OPEN CURSOR_GET_CREDIT_NOTE;
		FETCH CURSOR_GET_CREDIT_NOTE INTO DOCUMENT_DATE, DOCUMENT_FORM_NO, DOCUMENT_REFERENCE, DOCUMENT_AMOUNT, JOURNAL_POSTED;
		comp_sales_creditnote_loop: LOOP
			IF DONE_GET_CREDIT_NOTE THEN
				LEAVE comp_sales_creditnote_loop;
			END IF;
			
			INSERT INTO Statements (
				TransDate,
				FormNo,
				Reference,
				Description,
				Credit,
				Posted
			)
			VALUES (
				DOCUMENT_DATE,
				DOCUMENT_FORM_NO,
				DOCUMENT_REFERENCE,
				'Credit Note',
				DOCUMENT_AMOUNT,
				JOURNAL_POSTED
			);
			
			SET DOCUMENT_AMOUNT = 0;
		
		FETCH CURSOR_GET_CREDIT_NOTE INTO DOCUMENT_DATE, DOCUMENT_FORM_NO, DOCUMENT_REFERENCE, DOCUMENT_AMOUNT, JOURNAL_POSTED;
		END LOOP comp_sales_creditnote_loop;
		CLOSE CURSOR_GET_CREDIT_NOTE;
		
		SET BALANCE = BALANCE - COALESCE((
			SELECT
				CAST(SUM(comp_sales_creditnote.totalAmount) AS DECIMAL(15,5))
			FROM
				comp_sales_creditnote
			WHERE
				comp_sales_creditnote.companyID = `CompanyID` AND
				comp_sales_creditnote.customerID = `CustomerID` AND
				(comp_sales_creditnote.updateAble = 0 AND comp_sales_creditnote.is_delete = 0) AND
				comp_sales_creditnote.creditNoteDate < `StartDate`
			GROUP BY
				comp_sales_creditnote.companyID, comp_sales_creditnote.customerID
		), 0);
	END BLOCK_CREDIT_NOTE;
	
	BLOCK_PAYMENT: BEGIN
		DECLARE DONE_GET_PAYMENT INT DEFAULT FALSE;
		DECLARE CURSOR_GET_PAYMENT CURSOR FOR
		SELECT
			comp_receive_payment.receivePaymentDate,
			CONCAT(comp_receive_payment.formNo, '[', LPAD(comp_receive_payment.serialNo, 8, '0'), ']'),
			comp_receive_payment.referenceNo,
			CAST(comp_receive_payment.totalReceived AS DECIMAL(15,5)),
			COALESCE(comp_journal.`status`, 0)
		FROM
			comp_receive_payment LEFT OUTER 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.companyID = `CompanyID` AND
			comp_receive_payment.customerID = `CustomerID` AND
			(comp_receive_payment.updateAble = 0 AND comp_receive_payment.is_delete = 0) AND
			(comp_journal.is_delete = 0) AND
			comp_receive_payment.receivePaymentDate BETWEEN `StartDate` AND `EndDate`
		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 DOCUMENT_DATE, DOCUMENT_FORM_NO, DOCUMENT_REFERENCE, DOCUMENT_AMOUNT, JOURNAL_POSTED;
		comp_receive_payment_loop: LOOP
			IF DONE_GET_PAYMENT THEN
				LEAVE comp_receive_payment_loop;
			END IF;
			
			INSERT INTO Statements (
				TransDate,
				FormNo,
				Reference,
				Description,
				Credit,
				Posted
			)
			VALUES (
				DOCUMENT_DATE,
				DOCUMENT_FORM_NO,
				DOCUMENT_REFERENCE,
				'Payment',
				DOCUMENT_AMOUNT,
				JOURNAL_POSTED
			);
			
			SET DOCUMENT_AMOUNT = 0;
		
		FETCH CURSOR_GET_PAYMENT INTO DOCUMENT_DATE, DOCUMENT_FORM_NO, DOCUMENT_REFERENCE, DOCUMENT_AMOUNT, JOURNAL_POSTED;
		END LOOP comp_receive_payment_loop;
		CLOSE CURSOR_GET_PAYMENT;
		
		SET BALANCE = BALANCE - COALESCE((
			SELECT
				CAST(SUM(comp_receive_payment.totalReceived) AS DECIMAL(15,5))
			FROM
				comp_receive_payment
			WHERE
				comp_receive_payment.companyID = `CompanyID` AND
				comp_receive_payment.customerID = `CustomerID` AND
				(comp_receive_payment.updateAble = 0 AND comp_receive_payment.is_delete = 0) AND
				comp_receive_payment.receivePaymentDate < `StartDate`
			GROUP BY
				comp_receive_payment.companyID, comp_receive_payment.customerID
		), 0);
	END BLOCK_PAYMENT;
	
	IF `JournalDetail` = 1 THEN
		BLOCK_JOURNAL_DETAIL: BEGIN
			DECLARE DONE_GET_JOURNAL_DETAIL INT DEFAULT FALSE;
			DECLARE CURSOR_GET_JOURNAL_DETAIL CURSOR FOR
			SELECT
				comp_journal.effective_date,
				comp_journal_detail.journalID,
				comp_journal_detail.description,
				comp_journal_detail.amount_dr,
				comp_journal_detail.amount_cr,
				comp_journal_detail.`status`
			FROM
				comp_journal_detail LEFT JOIN comp_journal
				ON comp_journal.journalID = comp_journal_detail.journalID AND
				comp_journal.companyID = comp_journal_detail.companyID LEFT JOIN comp_chart_of_acct
				ON	comp_chart_of_acct.ID = comp_journal_detail.acctCode LEFT JOIN customer
				ON customer.linkedAPAccountID = comp_chart_of_acct.ID
			WHERE
				comp_journal_detail.companyID = `CompanyID` AND
				customer.ID = `CustomerID` AND
				comp_journal_detail.journalID LIKE 'JL%' AND
				(comp_journal_detail.is_delete = 0 AND comp_journal.is_delete = 0) AND
				comp_journal.effective_date BETWEEN `StartDate` AND `EndDate`
			ORDER BY
				comp_journal.effective_date ASC;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE_GET_JOURNAL_DETAIL = TRUE;
			
			OPEN CURSOR_GET_JOURNAL_DETAIL;
			FETCH CURSOR_GET_JOURNAL_DETAIL INTO DOCUMENT_DATE, DOCUMENT_FORM_NO, DOCUMENT_REFERENCE, DEBIT, CREDIT, JOURNAL_POSTED;
			comp_journal_detail_loop: LOOP
				IF DONE_GET_JOURNAL_DETAIL THEN
					LEAVE comp_journal_detail_loop;
				END IF;
				
				INSERT INTO Statements (
					TransDate,
					FormNo,
					Reference,
					Description,
					Debit,
					Credit,
					Posted
				)
				VALUES (
					DOCUMENT_DATE,
					DOCUMENT_FORM_NO,
					DOCUMENT_REFERENCE,
					'Journal',
					DEBIT,
					CREDIT,
					JOURNAL_POSTED
				);
				
				SET DEBIT = 0;
				SET CREDIT = 0;
			
			FETCH CURSOR_GET_JOURNAL_DETAIL INTO DOCUMENT_DATE, DOCUMENT_FORM_NO, DOCUMENT_REFERENCE, DEBIT, CREDIT, JOURNAL_POSTED;
			END LOOP comp_journal_detail_loop;
			CLOSE CURSOR_GET_JOURNAL_DETAIL;
			
			SET BALANCE = BALANCE + COALESCE((
				SELECT
					CAST(SUM(comp_journal_detail.amount_dr) AS DECIMAL(15,5))
				FROM
					comp_journal_detail LEFT JOIN comp_journal
					ON comp_journal.journalID = comp_journal_detail.journalID AND
					comp_journal.companyID = comp_journal_detail.companyID LEFT JOIN comp_chart_of_acct
					ON	comp_chart_of_acct.ID = comp_journal_detail.acctCode LEFT JOIN customer
					ON customer.linkedAPAccountID = comp_chart_of_acct.ID
				WHERE
					comp_journal_detail.companyID = `CompanyID` AND
					customer.ID = `CustomerID` AND
					comp_journal_detail.journalID LIKE 'JL%' AND
					(comp_journal_detail.is_delete = 0 AND comp_journal.is_delete = 0) AND
					comp_journal.effective_date < `StartDate`
				GROUP BY
					customer.ID
			), 0) - COALESCE((
				SELECT
					COALESCE(CAST(SUM(comp_journal_detail.amount_cr) AS DECIMAL(15,5)), 0)
				FROM
					comp_journal_detail LEFT JOIN comp_journal
					ON comp_journal.journalID = comp_journal_detail.journalID AND
					comp_journal.companyID = comp_journal_detail.companyID LEFT JOIN comp_chart_of_acct
					ON	comp_chart_of_acct.ID = comp_journal_detail.acctCode LEFT JOIN customer
					ON customer.linkedAPAccountID = comp_chart_of_acct.ID
				WHERE
					comp_journal_detail.companyID = `CompanyID` AND
					customer.ID = `CustomerID` AND
					comp_journal_detail.journalID LIKE 'JL%' AND
					(comp_journal_detail.is_delete = 0 AND comp_journal.is_delete = 0) AND
					comp_journal.effective_date < `StartDate`
				GROUP BY
					customer.ID
			), 0);
		END BLOCK_JOURNAL_DETAIL;
	END IF;
	
	INSERT INTO Statements(IsOpeningBalance, OpeningBalance) VALUES(1, BALANCE);
	
	SELECT *, DATE_FORMAT(TransDate, '%d-%m-%Y') AS TransactionDate FROM Statements ORDER BY TransDate ASC;
END
//