/* 
 * MOD_Id : MODULE ID
 * TYP : TYPE OF VOUCHER POSTING
 * CA = CHARGE ADD
 * DA = DISCOUNT ADJUST
 * PA = PAYMENT ADD
 * RA = REFUND ADD
 * CR = CHARGE REVART
 * PU = PURCHASE
 * TPY = PAYMENT LOGGED AGAINST TPA
 */

DROP FUNCTION IF EXISTS `fn_get_v_narration`;
CREATE FUNCTION fn_get_v_narration(MOD_Id INT,TYP CHAR(3), REF_ID INT) RETURNS TEXT(500)
DETERMINISTIC
BEGIN

    SET @NARR = '';

    IF MOD_Id = 3 AND TYP = 'PA' THEN /* FOR OPD */  /* PAYMENT ADD */

        SELECT
            patient_payments.paid_amount,
            patient_service_charges.bill_no 
        INTO 
            @AMT,
            @BILL
        FROM
            patient_payments
            JOIN patient_service_charges ON ( 
                patient_payments.patient_service_charge_id != 0 
                AND patient_payments.patient_service_charge_id = patient_service_charges.id 
            ) 
            OR (
                patient_payments.admission_id = patient_service_charges.service_source_id 
                AND patient_service_charges.billing_module_id = 6
            )
        WHERE 
            patient_payments.id = REF_ID;

        SET @NARR = CONCAT('Being amount of ', @AMT, ' has been debited against asset due to payment recieved against bill no: ', @BILL, ' in module : OPD');

    ELSEIF MOD_Id = 3 AND TYP = 'RA' THEN /* FOR OPD */  /* REFUND ADD */

        SELECT
            patient_payments.paid_amount,
            patient_service_charges.bill_no 
        INTO 
            @AMT,
            @BILL
        FROM
            patient_payments
            JOIN patient_service_charges ON ( 
                patient_payments.patient_service_charge_id != 0 
                AND patient_payments.patient_service_charge_id = patient_service_charges.id 
            ) 
            OR (
                patient_payments.admission_id = patient_service_charges.service_source_id 
                AND patient_service_charges.billing_module_id = 6
            )
        WHERE 
            patient_payments.id = REF_ID;

        SET @NARR = CONCAT('Being amount of ', @AMT, ' has been credited against asset due to refundable cleared against bill no: ', @BILL, ' in module : OPD');

    ELSEIF MOD_Id = 6 AND TYP = 'PA' THEN /* FOR IPD */  /* PAYMENT ADD */

        SELECT
            patient_payments.paid_amount,
            patient_service_charges.bill_no 
        INTO 
            @AMT,
            @BILL
        FROM
            patient_payments
            JOIN patient_service_charges ON ( 
                patient_payments.patient_service_charge_id != 0 
                AND patient_payments.patient_service_charge_id = patient_service_charges.id 
            ) 
            OR (
                patient_payments.admission_id = patient_service_charges.service_source_id 
                AND patient_service_charges.billing_module_id = 6
            )
        WHERE 
            patient_payments.id = REF_ID;

        SET @NARR = CONCAT('Being amount of ', @AMT, ' has been debited against asset due to payment recieved against bill no: ', @BILL, ' in module : IPD');

    ELSEIF MOD_Id = 6 AND TYP = 'RA' THEN /* FOR IPD */  /* REFUND ADD */

        SELECT
            patient_payments.paid_amount,
            patient_service_charges.bill_no 
        INTO 
            @AMT,
            @BILL
        FROM
            patient_payments
            JOIN patient_service_charges ON ( 
                patient_payments.patient_service_charge_id != 0 
                AND patient_payments.patient_service_charge_id = patient_service_charges.id 
            ) 
            OR (
                patient_payments.admission_id = patient_service_charges.service_source_id 
                AND patient_service_charges.billing_module_id = 6
            )
        WHERE 
            patient_payments.id = REF_ID;

        SET @NARR = CONCAT('Being amount of ', @AMT, ' has been credited against asset due to refundable cleared against bill no: ', @BILL, ' in module : IPD');

    ELSEIF MOD_Id = 6 AND TYP = 'TPY' THEN /* FOR IPD */  /* PATIENNT RECIEVABLE TO TPA RECIEVABLE */

        SELECT
            PP.paid_amount,
            PSC.bill_no,
            IA.admission_no,
            CONCAT(TP.policy_name," - ",TP.policy_code),
            CONCAT(TPO.organisation_name," - ",TPO.org_code)
        INTO 
            @AMT,
            @BILL,
            @ADMTNO,
            @TPA_POL,
            @TPA_ORG
        FROM
            patient_payments PP 
            JOIN patient_service_charges PSC ON PP.patient_service_charge_id = PSC.id
            JOIN ipd_admission IA ON PSC.admission_no = IA.admission_no
            JOIN tpa_policies TP ON IA.policy_id = TP.id
            JOIN tpa_organisations TPO ON TP.tpa_id = TPO.id
        WHERE
            PP.id = REF_ID;
        
        SET @NARR = CONCAT('Being amount of ', @AMT, ' has been credited against provisioning for bill no: ', @BILL, ', admission no: ', @ADMTNO, ' and debited against TPA RECIEVABLE for the policy ', @TPA_POL, ' of tpa organization ',@TPA_ORG);

    ELSEIF MOD_Id = 19 AND TYP = 'PA' THEN /* FOR EMERGENCY */  /* PAYMENT ADD */

        SELECT
            patient_payments.paid_amount,
            patient_service_charges.bill_no 
        INTO 
            @AMT,
            @BILL
        FROM
            patient_payments
            JOIN patient_service_charges ON ( 
                patient_payments.patient_service_charge_id != 0 
                AND patient_payments.patient_service_charge_id = patient_service_charges.id 
            ) 
            OR (
                patient_payments.admission_id = patient_service_charges.service_source_id 
                AND patient_service_charges.billing_module_id = 6
            )
        WHERE 
            patient_payments.id = REF_ID;

        SET @NARR = CONCAT('Being amount of ', @AMT, ' has been debited against asset due to payment recieved against bill no: ', @BILL, ' in module : EMERGENCY');

    ELSEIF MOD_Id = 19 AND TYP = 'RA' THEN /* FOR EMERGENCY */  /* REFUND ADD */

        SELECT
            patient_payments.paid_amount,
            patient_service_charges.bill_no 
        INTO 
            @AMT,
            @BILL
        FROM
            patient_payments
            JOIN patient_service_charges ON ( 
                patient_payments.patient_service_charge_id != 0 
                AND patient_payments.patient_service_charge_id = patient_service_charges.id 
            ) 
            OR (
                patient_payments.admission_id = patient_service_charges.service_source_id 
                AND patient_service_charges.billing_module_id = 6
            )
        WHERE 
            patient_payments.id = REF_ID;

        SET @NARR = CONCAT('Being amount of ', @AMT, ' has been credited against asset due to refundable cleared against bill no: ', @BILL, ' in module : EMERGENCY');

    ELSEIF MOD_Id = 19 AND TYP = 'TPY' THEN /* FOR EMERGENCY */  /* PATIENNT RECIEVABLE TO TPA RECIEVABLE */

        SELECT
            PP.paid_amount,
            PSC.bill_no,
            IA.admission_no,
            CONCAT(TP.policy_name," - ",TP.policy_code),
            CONCAT(TPO.organisation_name," - ",TPO.org_code)
        INTO 
            @AMT,
            @BILL,
            @ADMTNO,
            @TPA_POL,
            @TPA_ORG
        FROM
            patient_payments PP 
            JOIN patient_service_charges PSC ON PP.patient_service_charge_id = PSC.id
            JOIN ipd_admission IA ON PSC.admission_no = IA.admission_no
            JOIN tpa_policies TP ON IA.policy_id = TP.id
            JOIN tpa_organisations TPO ON TP.tpa_id = TPO.id
        WHERE
            PP.id = REF_ID;
        
        SET @NARR = CONCAT('Being amount of ', @AMT, ' has been credited against provisioning for bill no: ', @BILL, ', admission no: ', @ADMTNO, ' and debited against TPA RECIEVABLE for the policy ', @TPA_POL, ' of tpa organization ',@TPA_ORG);

    ELSEIF MOD_Id = 12 AND TYP = 'PA' THEN /* FOR OT */  /* PAYMENT ADD */

        SELECT
            patient_payments.paid_amount,
            patient_service_charges.bill_no 
        INTO 
            @AMT,
            @BILL
        FROM
            patient_payments
            JOIN patient_service_charges ON ( 
                patient_payments.patient_service_charge_id != 0 
                AND patient_payments.patient_service_charge_id = patient_service_charges.id 
            ) 
            OR (
                patient_payments.admission_id = patient_service_charges.service_source_id 
                AND patient_service_charges.billing_module_id = 6
            )
        WHERE 
            patient_payments.id = REF_ID;

        SET @NARR = CONCAT('Being amount of ', @AMT, ' has been debited against asset due to payment recieved against bill no: ', @BILL, ' in module : OT');

    ELSEIF MOD_Id = 12 AND TYP = 'RA' THEN /* FOR OT */  /* REFUND ADD */

        SELECT
            patient_payments.paid_amount,
            patient_service_charges.bill_no 
        INTO 
            @AMT,
            @BILL
        FROM
            patient_payments
            JOIN patient_service_charges ON ( 
                patient_payments.patient_service_charge_id != 0 
                AND patient_payments.patient_service_charge_id = patient_service_charges.id 
            ) 
            OR (
                patient_payments.admission_id = patient_service_charges.service_source_id 
                AND patient_service_charges.billing_module_id = 6
            )
        WHERE 
            patient_payments.id = REF_ID;

        SET @NARR = CONCAT('Being amount of ', @AMT, ' has been credited against asset due to refundable cleared against bill no: ', @BILL, ' in module : OT');

    
    ELSEIF MOD_Id = 7 AND TYP = 'PA' THEN /* FOR REGISTRATION */  /* PAYMENT ADD */

        SELECT
            patient_payments.paid_amount,
            patient_service_charges.bill_no 
        INTO 
            @AMT,
            @BILL
        FROM
            patient_payments
            JOIN patient_service_charges ON ( 
                patient_payments.patient_service_charge_id != 0 
                AND patient_payments.patient_service_charge_id = patient_service_charges.id 
            ) 
            OR (
                patient_payments.admission_id = patient_service_charges.service_source_id 
                AND patient_service_charges.billing_module_id = 6
            )
        WHERE 
            patient_payments.id = REF_ID;

        SET @NARR = CONCAT('Being amount of ', @AMT, ' has been debited against asset due to payment recieved against bill no: ', @BILL, ' in module : REGISTRATION');

    ELSEIF MOD_Id = 8 AND TYP = 'CA' THEN /* FOR LAB */ /* FOR CHARGE ADDITION */

        SELECT
            PSCD.net_amount,
            LAM.analyte_name,
            PSC.bill_no,
            PSC.admission_no,
            CONCAT(PT.patient_name,"(",PT.patient_unique_id,")"),
            PSCD.discount_amount
        INTO 
            @amt,
            @test,
            @inv,
            @admno,
            @p_dtls,
            @disc
        FROM
            patient_service_charge_details PSCD
            JOIN patient_service_charges PSC ON PSCD.patient_service_charge_id = PSC.id 
            JOIN lab_analyte_master LAM ON LAM.charge_id = PSCD.service_rate_id
            JOIN patients PT ON PT.id = PSC.patient_id
        WHERE
            PSCD.id = REF_ID;

        SET @NARR = CONCAT('Being amount of Rs. ', @amt, ' provisioned for ', @test, ' availed against lab case number: ', @admno, ', invoice number: ', @inv, ' by patient ', @p_dtls);


    ELSEIF MOD_Id = 8 AND TYP = 'DA' THEN /* FOR LAB */ /* DISCOUNT ADD */

        SELECT
            PSCD.net_amount,
            LAM.analyte_name,
            PSC.bill_no,
            PSC.admission_no,
            CONCAT(PT.patient_name,"(",PT.patient_unique_id,")"),
            PSCD.discount_amount
        INTO 
            @amt,
            @test,
            @inv,
            @admno,
            @p_dtls,
            @disc
        FROM
            patient_service_charge_details PSCD
            JOIN patient_service_charges PSC ON PSCD.patient_service_charge_id = PSC.id 
            JOIN lab_analyte_master LAM ON LAM.charge_id = PSCD.service_rate_id
            JOIN patients PT ON PT.id = PSC.patient_id
        WHERE
            PSCD.id = REF_ID;

        SET @NARR = CONCAT('Being amount of ', @disc, ' been debited against expense and adjusted from asset provisoning for discount lab case number: ', @admno, ', invoice number: ', @inv, ' by patient ', @p_dtls);

    ELSEIF MOD_Id = 8 AND TYP = 'CR' THEN /* FOR LAB */ /* CHARGE REVERT */

        SELECT
            PSCD.net_amount,
            LAM.analyte_name,
            PSC.bill_no,
            PSC.admission_no,
            CONCAT(PT.patient_name,"(",PT.patient_unique_id,")"),
            PSCD.discount_amount
        INTO 
            @amt,
            @test,
            @inv,
            @admno,
            @p_dtls,
            @disc
        FROM
            patient_service_charge_details PSCD
            JOIN patient_service_charges PSC ON PSCD.patient_service_charge_id = PSC.id 
            JOIN lab_analyte_master LAM ON LAM.charge_id = PSCD.service_rate_id
            JOIN patients PT ON PT.id = PSC.patient_id
        WHERE
            PSCD.id = REF_ID;

        SET @NARR = CONCAT('Being amount of ', @amt, ' been credited from provisioning due to deletion of ', @test, ' against lab case number: ', @admno, ', invoice number: ', @inv, ' by patient ', @p_dtls);

    ELSEIF MOD_Id = 8 AND TYP = 'PA' THEN /* FOR LAB */ /* PAYMENT ADD */

        SELECT
            patient_payments.paid_amount,
            patient_service_charges.bill_no 
        INTO 
            @AMT,
            @BILL
        FROM
            patient_payments
            JOIN patient_service_charges ON ( 
                patient_payments.patient_service_charge_id != 0 
                AND patient_payments.patient_service_charge_id = patient_service_charges.id 
            ) 
            OR (
                patient_payments.admission_id = patient_service_charges.service_source_id 
                AND patient_service_charges.billing_module_id = 6
            )
        WHERE 
            patient_payments.id = REF_ID;

        SET @NARR = CONCAT('Being amount of ', @AMT, ' has been debited against asset due to payment recieved against bill no: ', @BILL, ' in module : LAB');

    ELSEIF MOD_Id = 8 AND TYP = 'RA' THEN /* FOR LAB */ /* REFUND ADD */

        SELECT
            patient_payments.paid_amount,
            patient_service_charges.bill_no 
        INTO 
            @AMT,
            @BILL
        FROM
            patient_payments
            JOIN patient_service_charges ON ( 
                patient_payments.patient_service_charge_id != 0 
                AND patient_payments.patient_service_charge_id = patient_service_charges.id 
            ) 
            OR (
                patient_payments.admission_id = patient_service_charges.service_source_id 
                AND patient_service_charges.billing_module_id = 6
            )
        WHERE 
            patient_payments.id = REF_ID;

        SET @NARR = CONCAT('Being amount of ', @AMT, ' has been credited against asset due to refundable cleared against bill no: ', @BILL, ' in module : LAB');

    ELSEIF MOD_Id = 10 AND TYP = 'PU' THEN /* FOR purchase */ /* PURCHASE RECORDD ADD */

            SELECT
                purchase_order_receipt_items.net_price,
                purchase_order_receipt_items.cgst,
                purchase_order_receipt_items.sgst,
                purchase_order_receipts.bill_no
            INTO 
                @nt_amt,
                @cgst,
                @sgst,
                @bill_no
            FROM
                purchase_order_receipt_items
                JOIN purchase_order_receipts ON purchase_order_receipt_items.po_receipt_id = purchase_order_receipts.id 
            WHERE 
                purchase_order_receipt_items.id = REF_ID;

            SET @amt = @nt_amt - (@cgst + @sgst);
            SET @NARR = CONCAT('Being amount of ', @amt, ' been credited against payable for the purchase of ', @bill_no);

    ELSEIF MOD_Id = 10 AND TYP = 'PUR' THEN /* FOR purchase */  /* FOR PURCHASE RETURN */

        SELECT
            purchase_order_return_items.net_price,
            purchase_order_return_items.cgst,
            purchase_order_return_items.sgst,
            purchase_order_returns.return_slipnumber
        INTO 
            @nt_amt,
            @cgst,
            @sgst,
            @bill_no
        FROM
            purchase_order_return_items
            JOIN purchase_order_returns ON purchase_order_return_items.purchase_order_return_id = purchase_order_returns.id 
        WHERE 
            purchase_order_return_items.id = REF_ID;

        SET @amt = @nt_amt - (@cgst + @sgst);
        SET @NARR = CONCAT('Being amount of ', @amt, ' been debited against payable(Liability) for the return slip of ', @bill_no);

    ELSEIF MOD_Id = 10 AND TYP = 'PA' THEN 

        SELECT
            VP.paid_amount,
            POR.bill_no
        INTO
            @amt,
            @bill_no
        FROM
            vendor_payments VP	
        JOIN purchase_order_receipts POR ON VP.data_source_id = POR.id
        WHERE
            VP.id = REF_ID;

        SET @NARR = CONCAT('Being amount of ', @amt, ' been debited against payable(Liability) for the payament done for BILL of ', @bill_no);

    END IF;

    RETURN @NARR;
    
END;