DROP PROCEDURE IF EXISTS sp_srvcPaymntVoucherPosting;

CREATE PROCEDURE sp_srvcPaymntVoucherPosting()
BEGIN

    /* Create temporary table to fetch all service that are required for voucher entry */
    DROP TEMPORARY TABLE IF EXISTS TMP_SRV_PAY_LIST;

	CREATE TEMPORARY TABLE TMP_SRV_PAY_LIST(
		REF_ID INT DEFAULT 0,
		PSC_ID INT DEFAULT 0,
		PMODE VARCHAR(2) NOT NULL DEFAULT 'C',
        ADMT_ID INT NULL DEFAULT '0',
        P_TYP VARCHAR(2) NOT NULL DEFAULT 'P',
        AMT DECIMAL(15,2) NOT NULL DEFAULT 0,
        BNK INT DEFAULT NULL,
        NOTE VARCHAR(300) DEFAULT NULL,
        OTHR_DTLS VARCHAR(1000) DEFAULT NULL,
        MODULE INT NOT NULL
	);

    INSERT INTO TMP_SRV_PAY_LIST(
		REF_ID,
		PSC_ID,
		PMODE,
        ADMT_ID,
        P_TYP,
        AMT,
        BNK,
        NOTE,
        OTHR_DTLS,
        MODULE
	)
    SELECT
        PP.id REF_ID,
        PP.patient_service_charge_id PSC_ID,
        PP.payment_mode_id PMODE,
        PP.admission_id ADMT_ID,
        PP.payment_type P_TYP,
        PP.paid_amount AMT,
        PP.receiving_bank BNK,
        PP.note NOTE,
        PP.other_payment_dtls OTHR_DTLS,
        PSC.billing_module_id 
    FROM
        patient_payments PP 
    JOIN patient_service_charges PSC ON ( 
                PP.patient_service_charge_id != 0 
                AND PP.patient_service_charge_id = PSC.id 
            ) 
            OR (
                PP.admission_id = PSC.service_source_id 
                AND PSC.billing_module_id = 6
            )
    WHERE
        PP.charge_source = 'Service' 
        AND PP.is_voucher_posted = 'N'
        AND PP.payment_type IN('P','A','R');

    
    /* DEFAULT CASH CODE FOR HOSPITAL */
    SELECT default_account_code INTO @HOSP_CASH_ACC_CD FROM account_service_item_map WHERE account_service_map_code = 'CASH';

    /* GETTING DEFAULT RECEIVABLE CODE ID */
    SELECT default_account_code INTO @HOSP_REVBL_ACC_CD FROM account_service_item_map WHERE account_service_map_code = 'HOSPITAL_RECEIVABLE';

    /* GETTING DEFAULT RECEIVABLE CODE ID */
    SELECT default_account_code INTO @TPA_RECIEVABL FROM account_service_item_map WHERE account_service_map_code = 'TPA_RECIEVABLES';

    SET @COUNTER = 1; /* LOOP COUNTER */
    SELECT COUNT(1) INTO @MAX_COUNT FROM TMP_SRV_PAY_LIST; /* LOOP'S MAX ITERATION COUNTER */

    /* LOOP FOR INSERTING VOUCHER FOR EACH ROW AND UPDATING MAIN TABLE FOR VOUCHER INSERTED STATUS */
    ACC_SRV_PAY_ITRTR:LOOP
        IF @COUNTER > @MAX_COUNT THEN
            LEAVE ACC_SRV_PAY_ITRTR;
        END IF;

        PREPARE stmt FROM "SELECT 
		                        REF_ID,
		                        PSC_ID,
		                        PMODE,
                                ADMT_ID,
                                P_TYP,
                                AMT,
                                BNK,
                                NOTE,
                                OTHR_DTLS,
                                MODULE
                            INTO 
		                        @REF_ID,
		                        @PSC_ID,
		                        @PMODE,
                                @ADMT_ID,
                                @P_TYP,
                                @AMT,
                                @BNK,
                                @NOTE,
                                @OTHR_DTLS,
                                @MODULE
                            FROM TMP_SRV_PAY_LIST 
                            LIMIT ?, 1;";
        EXECUTE stmt USING @COUNTER;
        DEALLOCATE PREPARE stmt;

        SET @MOD_Id = @MODULE;
        SET @ACC_CD = NULL;
        
        IF @PMODE != 'F' AND (@P_TYP = 'P' OR @P_TYP = 'A') THEN /* PAYMENT TYPE : PAY */

            
            SET @ACC_CD = @HOSP_CASH_ACC_CD; /* DEFAULT CASH */
            IF (@PMODE = 'Q' OR @PMODE = 'T' OR @PMODE = 'D' OR @PMODE = 'W') AND @BNK IS NOT NULL THEN
                SELECT
                    ASIMD.account_map_code_id INTO @ACC_CD 
                FROM
                    account_service_item_map_details ASIMD
                    JOIN account_service_item_map ASIM ON ASIM.id = ASIMD.account_service_item_map_id 
                    AND ASIM.account_map_type = 'M' 
                    AND ASIM.account_map_module_id = 24 
                WHERE
                    ASIMD.module_wise_category_id = @BNK;
            END IF;


            IF @ACC_CD IS NOT NULL THEN 

                /* INSERTING INTO VOUCHER NARRATION */
                INSERT INTO voucher_narration(
                    voucher_date,
                    voucher_number,
                    narration,
                    transaction_type,
                    voucher_status,
                    reference_id,
                    reference_table,
                    created_at,
                    created_by
                ) VALUES(CURDATE(), fn_get_curdate_vid(CURDATE()), fn_get_v_narration(@MOD_Id,'PA',@REF_ID), 'Tr', 'A', @REF_ID, 'patient_payments', CURRENT_TIMESTAMP,0);

                SELECT LAST_INSERT_ID() INTO @LAST_INSERT_ID; /* FETCHING LAST INSERTED ID    */

                /* INSERTING INTO VOUCHER DETAILS FOR INCOME CREDIT AND RECIEVABLE PROVISION DEBIT */
                INSERT INTO voucher_details(
                    voucher_narration_id,
                    account_head_id,
                    voucher_amount,
                    debit_credit_flag,
                    created_at,
                    created_by
                )
                VALUES
                (@LAST_INSERT_ID, @ACC_CD, @AMT, 'D', CURRENT_TIMESTAMP, 0),
                (@LAST_INSERT_ID, @HOSP_REVBL_ACC_CD, @AMT, 'C', CURRENT_TIMESTAMP, 0);

                UPDATE patient_payments SET is_voucher_posted = 'Y' WHERE id = @REF_ID; /* updating patient payment table */

                INSERT INTO TMP_AUTO_VOUCH_MESSAGE(TYP,VAL) VALUES('SUCCESS',@LAST_INSERT_ID);

            ELSE

                INSERT INTO TMP_AUTO_VOUCH_MESSAGE(TYP,VAL) VALUES('ERROR',"I'M NOT MAPPED");

            END IF;
        ELSEIF @P_TYP = 'R' THEN  /* FOR REFUNDS */

            SET @ACC_CD = @HOSP_CASH_ACC_CD; /* DEFAULT CASH */
            IF (@PMODE = 'Q' OR @PMODE = 'T' OR @PMODE = 'D' OR @PMODE = 'W') AND @BNK IS NOT NULL THEN
                SELECT
                    ASIMD.account_map_code_id INTO @ACC_CD 
                FROM
                    account_service_item_map_details ASIMD
                    JOIN account_service_item_map ASIM ON ASIM.id = ASIMD.account_service_item_map_id 
                    AND ASIM.account_map_type = 'M' 
                    AND ASIM.account_map_module_id = 24 
                WHERE
                    ASIMD.module_wise_category_id = @BNK;
            END IF;

            IF @ACC_CD IS NOT NULL THEN
                /* INSERTING INTO VOUCHER NARRATION */
                INSERT INTO voucher_narration(
                    voucher_date,
                    voucher_number,
                    narration,
                    transaction_type,
                    voucher_status,
                    reference_id,
                    reference_table,
                    created_at,
                    created_by
                ) VALUES(CURDATE(), fn_get_curdate_vid(CURDATE()), fn_get_v_narration(@MOD_Id,'RA',@REF_ID), 'Tr', 'A', @REF_ID, 'patient_payments', CURRENT_TIMESTAMP,0);

                SELECT LAST_INSERT_ID() INTO @LAST_INSERT_ID; /* FETCHING LAST INSERTED ID    */

                /* INSERTING INTO VOUCHER DETAILS FOR INCOME CREDIT AND RECIEVABLE PROVISION DEBIT */
                INSERT INTO voucher_details(
                    voucher_narration_id,
                    account_head_id,
                    voucher_amount,
                    debit_credit_flag,
                    created_at,
                    created_by
                )
                VALUES
                (@LAST_INSERT_ID, @ACC_CD, @AMT, 'D', CURRENT_TIMESTAMP, 0),
                (@LAST_INSERT_ID, @HOSP_REVBL_ACC_CD, @AMT, 'C', CURRENT_TIMESTAMP, 0);

                UPDATE patient_payments SET is_voucher_posted = 'Y' WHERE id = @REF_ID; /* updating patient payment table */

                INSERT INTO TMP_AUTO_VOUCH_MESSAGE(TYP,VAL) VALUES('SUCCESS',@LAST_INSERT_ID);
            ELSE

                INSERT INTO TMP_AUTO_VOUCH_MESSAGE(TYP,VAL) VALUES('ERROR',"I'M NOT MAPPED");
            END IF;
        ELSEIF @P_TYP = 'P' && @PMODE = 'F' THEN  /* FOR TPA SETTLEMENTS */
            SET @ACC_CD = @TPA_RECIEVABL; /* DEFAULT tpa recievable */
            IF @ACC_CD IS NOT NULL THEN
                /* INSERTING INTO VOUCHER NARRATION */
                INSERT INTO voucher_narration(
                    voucher_date,
                    voucher_number,
                    narration,
                    transaction_type,
                    voucher_status,
                    reference_id,
                    reference_table,
                    created_at,
                    created_by
                ) VALUES(CURDATE(), fn_get_curdate_vid(CURDATE()), fn_get_v_narration(@MOD_Id,'TPY',@REF_ID), 'Tr', 'A', @REF_ID, 'patient_payments', CURRENT_TIMESTAMP,0);

                SELECT LAST_INSERT_ID() INTO @LAST_INSERT_ID; /* FETCHING LAST INSERTED ID    */

                /* INSERTING INTO VOUCHER DETAILS FOR INCOME CREDIT AND RECIEVABLE PROVISION DEBIT */
                INSERT INTO voucher_details(
                    voucher_narration_id,
                    account_head_id,
                    voucher_amount,
                    debit_credit_flag,
                    created_at,
                    created_by
                )
                VALUES
                (@LAST_INSERT_ID, @ACC_CD, @AMT, 'D', CURRENT_TIMESTAMP, 0),
                (@LAST_INSERT_ID, @HOSP_REVBL_ACC_CD, @AMT, 'C', CURRENT_TIMESTAMP, 0);

                UPDATE patient_payments SET is_voucher_posted = 'Y' WHERE id = @REF_ID; /* updating patient payment table */

                INSERT INTO TMP_AUTO_VOUCH_MESSAGE(TYP,VAL) VALUES('SUCCESS',@LAST_INSERT_ID);
            ELSE

                INSERT INTO TMP_AUTO_VOUCH_MESSAGE(TYP,VAL) VALUES('ERROR',"I'M NOT MAPPED");
            END IF;
        END IF;

        SET @COUNTER = @COUNTER + 1;
    END LOOP ACC_SRV_PAY_ITRTR;

END;