DROP PROCEDURE IF EXISTS sp_srvcAutoVoucherEntry;

CREATE PROCEDURE sp_srvcAutoVoucherEntry()
BEGIN

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

	CREATE TEMPORARY TABLE tmp_serv_charges_list_for_voucher(
		MOD_Id INT DEFAULT 0,
		AP_AMT DECIMAL(10,2) NULL DEFAULT '0',
		NT_AMT DECIMAL(10,2) NULL DEFAULT '0',
        DISC_AMT DECIMAL(10,2) NULL DEFAULT '0',
        CH_STAT CHAR(1) NOT NULL,
        REF_ID INT DEFAULT 0,
		##SRVC_ID INT DEFAULT 0,
		##SRVC_SB_ID INT DEFAULT 0,
		##SRVC_CATE_ID INT DEFAULT 0,
		##TST_ID INT DEFAULT 0,
		##TST_SB_ID INT DEFAULT 0,
		##TST_CATE_ID INT DEFAULT 0,
        DISC_CODE_ID  INT DEFAULT 0,
        ACC_CD INT DEFAULT 0,
        HAS_MAP ENUM('Y','N') NOT NULL,
        V_POSTING_STAT CHAR(1) NOT NULL,  /* VOUCHER POSTING STATUS */
        DV_POSTING_STAT CHAR(1) NOT NULL,  /* DISCOUNT VOUCHER POSTING STATUS */
        TRN_STAT CHAR(1) NOT NULL
	);

    INSERT INTO tmp_serv_charges_list_for_voucher (
        MOD_Id,
        AP_AMT,
        NT_AMT,
        DISC_AMT,
        CH_STAT,
        REF_ID,
        ##SRVC_ID,
        ##SRVC_SB_ID,
        ##SRVC_CATE_ID,
        ##TST_ID,
        ##TST_SB_ID,
        ##TST_CATE_ID,
        DISC_CODE_ID,
        ACC_CD,
        HAS_MAP,
        V_POSTING_STAT,
        DV_POSTING_STAT,
        TRN_STAT
    )
    SELECT
        PSCH.billing_module_id MOD_Id,
        PSCD.applicable_rate AP_AMT,
        PSCD.net_amount NT_AMT,
        PSCD.discount_amount DISC_AMT,
        PSCD.STATUS CH_STAT,
        PSCD.ID REF_ID,
        ##SI.id SI_ID,
        ##SSC.id SSC_ID,
        ##SC.id SC_ID,
        ##LAM.ID LAM_ID,
        ##LSD.id LSD_ID,
        ##LD.id LD_ID,
        MOD_DISC.default_account_code DISC_CODE_ID,
        (CASE WHEN ACC_MP.SPC_ACC_CODE = 0 OR ACC_MP.SPC_ACC_CODE IS NULL THEN ACC_MP.DFLT_ACC_CODE ELSE ACC_MP.SPC_ACC_CODE END) ACC_CD,
        (CASE WHEN ACC_MP.id IS NULL THEN 'N' ELSE 'Y' END) IS_MAPPED,
        PSCD.is_voucher_posted,
        PSCD.is_discount_vouch_posted,
        PSCD.status TRN_STAT

    FROM
        patient_service_charge_details AS PSCD
        INNER JOIN patient_service_charges AS PSC ON PSCD.patient_service_charge_id = PSC.id
        INNER JOIN patient_service_charge_header AS PSCH ON PSCH.patient_service_charge_id = PSC.id
        INNER JOIN service_items AS SI ON PSCD.service_id = SI.id
        INNER JOIN service_sub_categories AS SSC ON SI.service_sub_category = SSC.id
        INNER JOIN service_category AS SC ON SSC.category_id = SC.id
        LEFT JOIN lab_analyte_master AS LAM ON PSCD.service_rate_id = LAM.charge_id
        LEFT JOIN lab_sub_department AS LSD ON LAM.sub_department_id = LSD.id
        LEFT JOIN lab_department AS LD ON LSD.dept_id = LD.id
        LEFT JOIN account_service_item_map AS MOD_DISC ON PSCH.billing_module_id = MOD_DISC.account_map_module_id AND MOD_DISC.account_service_map_code LIKE '%_DISCOUNT' AND MOD_DISC.is_system = 'Y'
        LEFT JOIN ( SELECT
                    ASIM.id,
                    ASIM.account_map_module_id MOD_ID,
                    ASIM.account_map_lavel MAP_LVL,
                    ASIM.default_account_code DFLT_ACC_CODE, 
                    ASIMD.account_map_code_id SPC_ACC_CODE,
                    ASIMD.module_wise_category_id MAP_ID
                FROM
                    account_service_item_map AS ASIM
                    LEFT JOIN account_service_item_map_details AS ASIMD ON ASIM.id = ASIMD.account_service_item_map_id AND ASIMD.is_active = 'Y' 
                WHERE ASIM.is_active = 'Y' AND ASIM.account_service_map_code NOT LIKE '%DISCOUNT%'
        ) AS ACC_MP ON PSCH.billing_module_id = ACC_MP.MOD_ID 
            AND (
                ( ACC_MP.MAP_LVL = '4' )
                OR ( ACC_MP.MOD_ID = 8 
                    AND (
                        ACC_MP.MAP_LVL = '1' AND LD.id = ACC_MP.MAP_ID
                    )
                    OR (
                        ACC_MP.MAP_LVL = '2' AND LSD.id = ACC_MP.MAP_ID
                    )
                    OR (
                        ACC_MP.MAP_LVL = '3' AND LAM.id = ACC_MP.MAP_ID
                    )
                )
                OR (
                    ACC_MP.MOD_ID != 8 
                    AND (
                        ACC_MP.MAP_LVL = '1' AND SC.id = ACC_MP.MAP_ID
                    )
                    OR (
                        ACC_MP.MAP_LVL = '2' AND SSC.id = ACC_MP.MAP_ID
                    )
                    OR (
                        ACC_MP.MAP_LVL = '3' AND SI.id = ACC_MP.MAP_ID
                    )
                )
            )
    WHERE
	    PSCH.billing_module_id != 11 
	    AND (
            PSCD.status IN ('A','B') 
            AND (
                PSCD.is_voucher_posted = 'N' 
                OR (
                    PSCD.is_discount_vouch_posted = 'N' 
                    AND PSCD.discount_amount > 0
                )
            )
        )
        OR (
            PSCD.status NOT IN ('A','B')
            AND PSCD.is_voucher_posted = 'Y'
        );


    /* 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 DISCOUNT CODE */
    SELECT default_account_code INTO @HOSP_DFLT_DISC_ACC_CD FROM account_service_item_map  WHERE account_service_map_code = 'DEFAULT_DISCOUNT' AND is_system = 'Y';
    

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

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

        PREPARE stmt FROM "SELECT MOD_Id,AP_AMT,NT_AMT,DISC_AMT,ACC_CD,HAS_MAP,REF_ID,DISC_CODE_ID,V_POSTING_STAT,DV_POSTING_STAT,TRN_STAT INTO @MOD_Id,@AP_AMT,@NT_AMT,@DISC_AMT,@ACC_CD,@HAS_MAP,@REF_ID,@DISC_CODE_ID,@V_POSTING_STAT,@DV_POSTING_STAT,@TRN_STAT FROM tmp_serv_charges_list_for_voucher LIMIT ?, 1;";
        EXECUTE stmt USING @COUNTER;
        DEALLOCATE PREPARE stmt;

        IF @HAS_MAP = 'Y' THEN  /* IF MAPPING IS NOT MISSING */

            /* IF DISCOUNT FOR THE PERTICULAR MODULE IS NOT MAPPED, DEFAULT DISCOUNT CODE IS TO BE ACCOUNTED */
            IF @DISC_CODE_ID IS NULL OR @DISC_CODE_ID = '' THEN 
                SET @DISC_CODE_ID = @HOSP_DFLT_DISC_ACC_CD;
            END IF;

            IF @V_POSTING_STAT = 'N' AND @DV_POSTING_STAT = 'N' AND (@TRN_STAT != 'C' OR @TRN_STAT != 'D' OR @TRN_STAT != 'R') THEN  /* IF NO VOUCHER POSTED AGAINST THIS TRANSACTION */

                /* 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,'CA',@REF_ID), 'Tr', 'A', @REF_ID, 'patient_service_charge_details',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, @AP_AMT, 'C', CURRENT_TIMESTAMP, 0),
                (@LAST_INSERT_ID, @HOSP_REVBL_ACC_CD, @NT_AMT, 'D', CURRENT_TIMESTAMP, 0);

                IF @DISC_AMT IS NOT NULL AND @DISC_AMT > 0 THEN     /* IF DISC AMT IS GREATER THAN 0 */

                    INSERT INTO voucher_details(
                        voucher_narration_id,
                        account_head_id,
                        voucher_amount,
                        debit_credit_flag,
                        created_at,
                        created_by
                    )
                    VALUES
                    (@LAST_INSERT_ID, @DISC_CODE_ID, @DISC_AMT, 'D', CURRENT_TIMESTAMP, 0);

                    /* UPDATING VOUCHER POSTED IN TRANSACTION TABLE */
                    UPDATE patient_service_charge_details SET patient_service_charge_details.is_voucher_posted = 'Y' , patient_service_charge_details.is_discount_vouch_posted = 'Y' WHERE patient_service_charge_details.id = @REF_ID;
                
                ELSE 

                    /* UPDATING VOUCHER POSTED IN TRANSACTION TABLE */
                    UPDATE patient_service_charge_details SET patient_service_charge_details.is_voucher_posted = 'Y' WHERE patient_service_charge_details.id = @REF_ID;

                END IF;
            ELSEIF @V_POSTING_STAT = 'Y' AND @DV_POSTING_STAT = 'N' AND (@TRN_STAT != 'C' OR @TRN_STAT != 'D' OR @TRN_STAT != 'R') THEN  /* IF ONLY DISCOUNT VOUCHER IS NOT POSTED AGAINST THIS TRANSACTION */

                /* 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,'DA',@REF_ID), 'Tr', 'A', @REF_ID, 'patient_service_charge_details', 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, @HOSP_REVBL_ACC_CD, @DISC_AMT, 'C', CURRENT_TIMESTAMP, 0),
                (@LAST_INSERT_ID, @DISC_CODE_ID, @DISC_AMT, 'D', CURRENT_TIMESTAMP, 0);

                /* UPDATING VOUCHER POSTED IN TRANSACTION TABLE */
                UPDATE patient_service_charge_details SET patient_service_charge_details.is_discount_vouch_posted = 'Y' WHERE patient_service_charge_details.id = @REF_ID;
            
            ELSEIF @V_POSTING_STAT = 'Y' AND (@TRN_STAT = 'C' OR @TRN_STAT = 'D' OR @TRN_STAT = 'R') 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,'CR',@REF_ID), 'Tr', 'A', @REF_ID, 'patient_service_charge_details', 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, @AP_AMT, 'D', CURRENT_TIMESTAMP, 0),
                (@LAST_INSERT_ID, @HOSP_REVBL_ACC_CD, @NT_AMT, 'C', CURRENT_TIMESTAMP, 0);


                IF @DISC_AMT IS NOT NULL AND @DISC_AMT > 0 AND @DV_POSTING_STAT = 'Y' THEN     /* IF DISC AMT IS GREATER THAN 0 */

                    INSERT INTO voucher_details(
                        voucher_narration_id,
                        account_head_id,
                        voucher_amount,
                        debit_credit_flag,
                        created_at,
                        created_by
                    )
                    VALUES
                    (@LAST_INSERT_ID, @DISC_CODE_ID, @DISC_AMT, 'C', CURRENT_TIMESTAMP, 0);

                    /* UPDATING VOUCHER POSTED IN TRANSACTION TABLE */
                    UPDATE patient_service_charge_details SET patient_service_charge_details.is_voucher_posted = 'N' , patient_service_charge_details.is_discount_vouch_posted = 'N' WHERE patient_service_charge_details.id = @REF_ID;
                
                ELSE 

                    /* UPDATING VOUCHER POSTED IN TRANSACTION TABLE */
                    UPDATE patient_service_charge_details SET patient_service_charge_details.is_voucher_posted = 'N' WHERE patient_service_charge_details.id = @REF_ID;

                END IF;

            END IF;

            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; 

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