DROP PROCEDURE IF EXISTS `sp_GetSupplierStatementByDate`;

DELIMITER //

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_GetSupplierStatementByDate`(
	IN `CompanyID` INT,
	IN `SupplierID` 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 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) NOT NULL DEFAULT '',
		Reference VARCHAR(128) NOT NULL DEFAULT '',
		Description VARCHAR(128) NOT NULL DEFAULT '',
		Debit DECIMAL(15,5) NOT NULL DEFAULT 0,
		Credit DECIMAL(15,5) NOT NULL DEFAULT 0,
		Posted BIT NOT NULL DEFAULT 0
	);
	
	BLOCK_HISTORICAL_INVOICE: BEGIN
		DECLARE DONE_GET_HISTORICAL_INVOICE INT DEFAULT FALSE;
		DECLARE CURSOR_GET_HISTORICAL_INVOICE CURSOR FOR
		SELECT
			comp_historical_supplier_invoice.invoiceDate,
			CONCAT(comp_historical_supplier_invoice.formNo, '[', LPAD(comp_historical_supplier_invoice.serialNo, 8, '0'), ']'),
			comp_historical_supplier_invoice.supplierInvoiceNo,
			CAST(comp_historical_supplier_invoice.totalAmount AS DECIMAL(15,5))
		FROM
			comp_historical_supplier_invoice
		WHERE
			comp_historical_supplier_invoice.companyID = `CompanyID` AND
			comp_historical_supplier_invoice.supplierID = `SupplierID` AND
			comp_historical_supplier_invoice.is_delete = 0 AND
			comp_historical_supplier_invoice.invoiceDate BETWEEN `StartDate` AND `EndDate`
		ORDER BY
			comp_historical_supplier_invoice.invoiceDate 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_supplier_invoice_loop: LOOP
			IF DONE_GET_HISTORICAL_INVOICE THEN
				LEAVE comp_historical_supplier_invoice_loop;
			END IF;
			
			INSERT INTO Statements (
				TransDate,
				FormNo,
				Reference,
				Description,
				Credit,
				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_supplier_invoice_loop;
		CLOSE CURSOR_GET_HISTORICAL_INVOICE;
	END BLOCK_HISTORICAL_INVOICE;
	
	BLOCK_INVOICE: BEGIN
		DECLARE DONE_GET_INVOICE INT DEFAULT FALSE;
		DECLARE CURSOR_GET_INVOICE CURSOR FOR
		SELECT
			comp_supplier_invoice.invoiceDate,
			CONCAT(comp_supplier_invoice.formNo, '[', LPAD(comp_supplier_invoice.serialNo, 8, '0'), ']'),
			'',
			CAST(comp_supplier_invoice.totalAmount AS DECIMAL(15,5)),
			COALESCE(comp_journal.`status`, 0)
		FROM
			comp_supplier_invoice LEFT OUTER 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 = `SupplierID` AND
			(comp_supplier_invoice.updateAble = 0 AND comp_supplier_invoice.is_delete = 0) AND
			(comp_journal.is_delete = 0) AND
			comp_supplier_invoice.invoiceDate BETWEEN `StartDate` AND `EndDate`
		ORDER BY
			comp_supplier_invoice.invoiceDate 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_supplier_invoice_loop: LOOP
			IF DONE_GET_INVOICE THEN
				LEAVE comp_supplier_invoice_loop;
			END IF;
			
			INSERT INTO Statements (
				TransDate,
				FormNo,
				Reference,
				Description,
				Credit,
				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_supplier_invoice_loop;
		CLOSE CURSOR_GET_INVOICE;
	END BLOCK_INVOICE;
	
	BLOCK_DEBIT_NOTE: BEGIN
		DECLARE DONE_GET_DEBIT_NOTE INT DEFAULT FALSE;
		DECLARE CURSOR_GET_DEBIT_NOTE CURSOR FOR
		SELECT
			comp_supplier_debitnote.debitNoteDate,
			CONCAT(comp_supplier_debitnote.formNo, '[', LPAD(comp_supplier_debitnote.serialNo, 8, '0'), ']'),
			IF(
				comp_supplier_debitnote.isHistoricalInvoice = 1,
				CONCAT(comp_historical_supplier_invoice.formNo, '[', LPAD(comp_historical_supplier_invoice.serialNo, 8, '0'), ']'),
				CONCAT(comp_supplier_invoice.formNo, '[', LPAD(comp_supplier_invoice.serialNo, 8, '0'), ']')
			),
			CAST(comp_supplier_debitnote.totalAmount AS DECIMAL(15,5)),
			COALESCE(comp_journal.`status`, 0)
		FROM
			comp_supplier_debitnote LEFT OUTER JOIN comp_journal
			ON comp_journal.journalID = CONVERT(comp_supplier_debitnote.journalNo USING utf8) AND
			comp_journal.companyID = comp_supplier_debitnote.companyID LEFT OUTER JOIN comp_supplier_invoice
			ON comp_supplier_invoice.ID = comp_supplier_debitnote.supplierInvoiceID LEFT OUTER JOIN comp_historical_supplier_invoice
			ON comp_historical_supplier_invoice.ID = comp_supplier_debitnote.supplierInvoiceID
		WHERE
			comp_supplier_debitnote.companyID = `CompanyID` AND
			comp_supplier_debitnote.supplierID = `SupplierID` AND
			(comp_supplier_debitnote.updateAble = 0 AND comp_supplier_debitnote.is_delete = 0) AND
			(comp_journal.is_delete = 0) AND
			comp_supplier_debitnote.debitNoteDate BETWEEN `StartDate` AND `EndDate`
		ORDER BY
			comp_supplier_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_supplier_debitnote_loop: LOOP
			IF DONE_GET_DEBIT_NOTE THEN
				LEAVE comp_supplier_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_supplier_debitnote_loop;
		CLOSE CURSOR_GET_DEBIT_NOTE;
	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_supplier_creditnote.creditNoteDate,
			CONCAT(comp_supplier_creditnote.formNo, '[', LPAD(comp_supplier_creditnote.serialNo, 8, '0'), ']'),
			CONCAT(comp_supplier_invoice.formNo, '[', LPAD(comp_supplier_invoice.serialNo, 8, '0'), ']'),
			CAST(comp_supplier_creditnote.totalAmount AS DECIMAL(15,5)),
			COALESCE(comp_journal.`status`, 0)
		FROM
			comp_supplier_creditnote LEFT OUTER JOIN comp_journal
			ON comp_journal.journalID = CONVERT(comp_supplier_creditnote.journalNo USING utf8) AND
			comp_journal.companyID = comp_supplier_creditnote.companyID LEFT JOIN comp_supplier_invoice
			ON comp_supplier_invoice.ID = comp_supplier_creditnote.supplierInvoiceID
		WHERE
			comp_supplier_creditnote.companyID = `CompanyID` AND
			comp_supplier_creditnote.supplierID = `SupplierID` AND
			(comp_supplier_creditnote.updateAble = 0 AND comp_supplier_creditnote.is_delete = 0) AND
			(comp_journal.is_delete = 0) AND
			comp_supplier_creditnote.creditNoteDate BETWEEN `StartDate` AND `EndDate`
		ORDER BY
			comp_supplier_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_supplier_creditnote_loop: LOOP
			IF DONE_GET_CREDIT_NOTE THEN
				LEAVE comp_supplier_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_supplier_creditnote_loop;
		CLOSE CURSOR_GET_CREDIT_NOTE;
	END BLOCK_CREDIT_NOTE;
	
	BLOCK_PAYMENT: BEGIN
		DECLARE DONE_GET_PAYMENT INT DEFAULT FALSE;
		DECLARE CURSOR_GET_PAYMENT CURSOR FOR
		SELECT
			comp_payment.paymentDate,
			CONCAT(comp_payment.formNo, '[', LPAD(comp_payment.serialNo, 8, '0'), ']'),
			comp_payment.referenceNo,
			CAST(comp_payment.totalPaid AS DECIMAL(15,5)),
			COALESCE(comp_journal.`status`, 0)
		FROM
			comp_payment LEFT OUTER JOIN comp_journal
			ON comp_journal.journalID = CONVERT(comp_payment.journalNo USING utf8) AND
			comp_journal.companyID = comp_payment.companyID
		WHERE
			comp_payment.companyID = `CompanyID` AND
			comp_payment.supplierID = `SupplierID` AND
			(comp_payment.updateAble = 0 AND comp_payment.is_delete = 0) AND
			(comp_journal.is_delete = 0) AND
			comp_payment.paymentDate BETWEEN `StartDate` AND `EndDate`
		ORDER BY
			comp_payment.paymentDate 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,
				Debit,
				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;
	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 supplier
				ON supplier.linkedAPAccountID = comp_chart_of_acct.ID
			WHERE
				comp_journal_detail.companyID = `CompanyID` AND
				supplier.ID = `SupplierID` AND
				comp_journal_detail.journalID LIKE 'JL%' AND
				(comp_journal_detail.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;
		END BLOCK_JOURNAL_DETAIL;
	END IF;
	
	SELECT *, DATE_FORMAT(TransDate, '%d-%m-%Y') AS TransactionDate FROM Statements ORDER BY TransDate ASC;
END
//