<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.openhis.web.chargemanage.mapper.OutpatientRefundAppMapper">
    <resultMap id="encounterPatientRefundMap"
               type="com.openhis.web.chargemanage.dto.EncounterPatientRefundDto">
        <!--患者退费项目信息-->
        <result property="paymentId" column="payment_id"/>
        <result property="entererId" column="enterer_id"/>
        <result property="encounterId" column="encounter_id"/>
        <result property="patientId" column="patient_id"/>
        <result property="prescriptionNo" column="prescription_no"/>
        <result property="busNo" column="bus_no"/>
        <result property="contractNo" column="contract_no"/>
        <result property="billDate" column="bill_date"/>
        <result property="chargeItemIds" column="charge_item_ids"/>
        <result property="itemName" column="item_name"/>
        <result property="orgName" column="org_name"/>
        <result property="entererName" column="enterer_name"/>
        <result property="chargeStatus" column="charge_status"/>
        <result property="paymentStatus" column="payment_status"/>
        <result property="dispenseStatus" column="dispense_status"/>
        <result property="serviceStatus" column="service_status"/>
        <result property="quantityValue" column="quantity_value"/>
        <result property="dispenseQuantity" column="dispense_quantity"/>
        <result property="quantityUnit" column="quantity_unit"/>
        <result property="unitPrice" column="unit_price"/>
        <result property="totalPrice" column="total_price"/>

        <!--支付明细-->
        <collection property="paymentDetailList"
                    ofType="com.openhis.web.paymentmanage.dto.PaymentDetailDto">
            <!--唯一值，避免相同数据合并-->
            <id column="payment_detail_id"/>
            <result property="payEnum" column="pay_enum"/>
<!--            <result property="payLevelEnum" column="pay_level_enum"/>-->
            <result property="amount" column="amount"/>
            <result property="returnAmount" column="return_amount"/>
            <result property="chargeAmount" column="charge_amount"/>
        </collection>
    </resultMap>
    <select id="selectEncounterPatientPayment"
            resultType="com.openhis.web.chargemanage.dto.EncounterPatientPaymentDto">
        SELECT T1.id,
               T1.status_enum,
               T1.relation_id,
               T1.payment_no,
               T1.payment_enum,
               T1.location_id,
               T1.expiration_date,
               T1.tendered_amount,
               T1.returned_amount,
               T1.contract_no,
               T2.prescription_no,
               T2.account_id
        FROM fin_payment_reconciliation AS T1
            LEFT JOIN adm_charge_item AS T2
                -- 因为T2.id是long型，T1.charge_item_ids是例如 [1,2] 的string类型，所以需要先转类型再匹配
                ON T2.id::TEXT = ANY(string_to_array(T1.charge_item_ids, ','))
                    AND T2.delete_flag = '0'
        WHERE T1.encounter_id =#{encounterId}
            AND T1.status_enum IN (#{success})
            AND T1.delete_flag = '0'
    </select>
    <select id="selectRefundItem" resultType="com.openhis.web.chargemanage.dto.RefundItemDto">
        SELECT T1.id AS charge_item_id,
               T1.service_table,
               T1.service_id,
               T2.bus_no,
               T2.prescription_no,
               T2.quantity,
               T2.medication_id AS item_id,
               T2.unit_code,
               T1.unit_price,
               T1.total_price,
               T3.status_enum AS dispense_status,
               T3.not_performed_reason_enum AS not_performed_reason,
               0 AS service_status,
               T3.id AS dispense_id,
               T2.id AS request_id,
               T7.name AS item_name,
               T10.id AS payment_id,
               T1.status_enum AS refund_status
        FROM adm_charge_item AS T1
            LEFT JOIN med_medication_request AS T2
                ON T2.id = T1.service_id
                    AND T2.delete_flag = '0'
            LEFT JOIN med_medication_dispense AS T3
                ON T3.med_req_id = T2.id
                    AND T3.delete_flag = '0'
            LEFT JOIN med_medication_definition AS T7
                ON T1.product_id = T7.id
                    AND T7.delete_flag = '0'
        LEFT JOIN fin_payment_reconciliation AS T10
            ON T1.id::TEXT = ANY(string_to_array(T10.charge_item_ids, ','))
                AND T10.delete_flag = '0'
                AND T10.status_enum = 1
        WHERE T1.service_table = #{medMedicationRequest}
            AND T1.delete_flag = '0'
            AND T2.refund_medicine_id IS NULL
        <if test="chargeItemIdList != null and !chargeItemIdList.isEmpty()">
            AND T1.id IN
            <foreach collection="chargeItemIdList" item="chargeItemId" separator="," open="(" close=")">
                #{chargeItemId}
            </foreach>
        </if>
        UNION
        SELECT T1.id AS charge_item_id,
               T1.service_table,
               T1.service_id,
               T4.bus_no,
               T4.prescription_no,
               T4.quantity,
               T4.device_def_id AS item_id,
               T4.unit_code,
               T1.unit_price,
               T1.total_price,
               T5.status_enum AS dispense_status,
               T5.not_performed_reason_enum AS not_performed_reason,
               0 AS service_status,
               T5.id AS dispense_id,
               T4.id AS request_id,
               T8.name AS item_name,
               T10.id AS payment_id,
               T1.status_enum AS refund_status
        FROM adm_charge_item AS T1
            LEFT JOIN wor_device_request AS T4
                ON T4.id = T1.service_id
                    AND T4.delete_flag = '0'
            LEFT JOIN wor_device_dispense AS T5
                ON T5.device_req_id = T4.id
                    AND T5.delete_flag = '0'
            LEFT JOIN adm_device_definition AS T8
                ON T1.product_id = T8.id
                    AND T8.delete_flag = '0'
            LEFT JOIN fin_payment_reconciliation AS T10
                ON T1.id::TEXT = ANY(string_to_array(T10.charge_item_ids, ','))
                    AND T10.delete_flag = '0'
                    AND T10.status_enum = 1
        WHERE T1.service_table = #{worDeviceRequest}
            AND T4.refund_device_id IS NULL
            AND T1.delete_flag = '0'
        <if test="chargeItemIdList != null and !chargeItemIdList.isEmpty()">
            AND T1.id IN
            <foreach collection="chargeItemIdList" item="chargeItemId" separator="," open="(" close=")">
                #{chargeItemId}
            </foreach>
        </if>
        UNION
        SELECT T1.id AS charge_item_id,
               T1.service_table,
               T1.service_id,
               T6.bus_no,
               T6.prescription_no,
               T6.quantity,
               T6.activity_id AS item_id,
               T6.unit_code,
               T1.unit_price,
               T1.total_price,
               0 AS dispense_status,
               null AS not_performed_reason,
               T6.status_enum AS service_status,
               null AS dispense_id,
               T6.id AS request_id,
               T9.name AS item_name,
               T10.id AS payment_id,
               T1.status_enum AS refund_status
        FROM adm_charge_item AS T1
            LEFT JOIN wor_service_request AS T6
                ON T6.id = T1.service_id
                    AND T6.delete_flag = '0'
            LEFT JOIN wor_activity_definition AS T9
                ON T1.product_id = T9.id
                    AND T9.delete_flag = '0'
            LEFT JOIN fin_payment_reconciliation AS T10
                ON T1.id::TEXT = ANY(string_to_array(T10.charge_item_ids, ','))
                    AND T10.delete_flag = '0'
                    AND T10.status_enum = 1
        WHERE T1.service_table = #{worServiceRequest}
            AND T1.delete_flag = '0'
        <if test="chargeItemIdList != null and !chargeItemIdList.isEmpty()">
            AND T1.id IN
            <foreach collection="chargeItemIdList" item="chargeItemId" separator="," open="(" close=")">
                #{chargeItemId}
            </foreach>
        </if>
    </select>
    <select id="selectBilledEncounterPatientPage"
            resultType="com.openhis.web.chargemanage.dto.EncounterPatientPageDto">
        SELECT T8.encounter_id,
               T8.encounter_bus_no,
               T8.status_enum,
               T8.start_time,
               T8.patient_name,
               T8.patient_bus_no,
               T8.gender_enum,
               T8.birth_date,
               T8.id_card,
               T8.patient_py_str,
               T8.patient_wb_str,
               T8.type_code,
               T8.category_enum,
               T8.total_amount,
               T8.max_bill_date,
               T8.insurance_amount,
               T8.self_amount
        FROM (
                 SELECT T1.id AS encounter_id,
                        T1.bus_no AS encounter_bus_no,
                        T1.start_time,
                        T1.delete_flag,
                        T1.tenant_id,
                        T3.status_enum,
                        T2."name" AS patient_name,
                        T2.bus_no AS patient_bus_no,
                        T2.gender_enum,
                        T2.birth_date,
                        T2.id_card,
                        T2.py_str AS patient_py_str,
                        T2.wb_str AS patient_wb_str,
                        T4.type_code,
                        T5.category_enum,
                        SUM(T6.tendered_amount) AS total_amount,
                        MAX(T6.bill_date) AS max_bill_date,
                        CASE WHEN T7.account_code = #{insurance} THEN SUM(T7.amount) ELSE 0 END AS insurance_amount,
                        CASE WHEN T7.account_code != #{insurance} THEN SUM(T7.amount) ELSE 0 END AS self_amount
                 FROM adm_encounter AS T1
                      LEFT JOIN adm_patient AS T2
                        ON T1.patient_id = T2.id
                            AND T2.delete_flag = '0'
                      LEFT JOIN adm_charge_item AS T3
                        ON T3.encounter_id = T1.id
                            AND T3.delete_flag = '0'
                      LEFT JOIN adm_account AS T4
                        ON T4.id = T3.account_id
                            AND T4.delete_flag = '0'
                      LEFT JOIN fin_contract AS T5
                        ON T5.bus_no = T4.contract_no
                            AND T5.delete_flag = '0'
                      LEFT JOIN fin_payment_reconciliation AS T6
                        ON T6.encounter_id = T1.id
                            AND T6.status_enum = 1
                            AND T6.delete_flag = '0'
                      LEFT JOIN fin_payment_rec_detail AS T7
                        ON T7.reconciliation_id = T6.id
                            AND T7.delete_flag = '0'
                 WHERE T3.status_enum IN (#{billed},#{refunding},#{refunded},#{partRefund})
                    AND T3.refund_id IS NULL
                    AND T1.delete_flag = '0'
                 GROUP BY T1.id,
                          T1.bus_no,
                          T1.start_time,
                          T1.delete_flag,
                          T1.tenant_id,
                          T3.status_enum,
                          T2."name",
                          T2.bus_no,
                          T2.gender_enum,
                          T2.birth_date,
                          T2.id_card,
                          T2.py_str,
                          T2.wb_str,
                          T4.type_code,
                          T5.category_enum,
                          T7.account_code
             ) AS T8
            ${ew.customSqlSegment}
            ORDER BY T8.max_bill_date DESC
    </select>
    <select id="selectEncounterPatientRefund" resultMap="encounterPatientRefundMap">
        SELECT aci.encounter_id ,
               aci.bus_no ,
               aci.status_enum AS charge_status,
               aci.quantity_unit ,
               aci.quantity_value ,
               aci.unit_price ,
               aci.total_price ,
               mmr.prescription_no ,
               dis.status_enum AS dispense_status,
               0 AS service_status,
               dis.dispense_quantity ,
               mmd."name" AS item_name,
               fpr.id AS payment_id,
               fpr.enterer_id,
               fpr.patient_id,
               fpr.status_enum AS payment_status,
               fpr.contract_no,
               fpr.bill_date,
               fpr.charge_item_ids,
               fprd.id AS payment_detail_id,
               fprd.target_enum,
               fprd.account_id,
               fprd.account_code,
               fprd.pay_enum,
               fprd.pay_level_enum,
               fprd.amount ,
               fprd.return_amount,
               fprd.charge_amount,
               ao."name" AS org_name,
               ap."name" AS enterer_name
        FROM adm_charge_item aci
            LEFT JOIN med_medication_request mmr
                ON aci.service_id = mmr.refund_medicine_id
                    AND mmr.refund_medicine_id IS NOT NULL
                    AND mmr.delete_flag = '0'
            LEFT JOIN med_medication_dispense dis
                ON mmr.id = dis.med_req_id
                    AND dis.delete_flag = '0'
            INNER JOIN med_medication_definition mmd
                ON aci.product_id = mmd.id
                    AND mmd.delete_flag = '0'
            LEFT JOIN fin_payment_reconciliation fpr
                ON aci.id::TEXT = ANY(string_to_array(fpr.charge_item_ids, ','))
                    AND fpr.status_enum = 1
                    AND fpr.delete_flag = '0'
            LEFT JOIN fin_payment_rec_detail fprd
                ON fpr.id = fprd.reconciliation_id
                    AND fprd.delete_flag = '0'
            LEFT JOIN adm_organization ao
                ON ao.id = aci.org_id
                    AND ao.delete_flag = '0'
            LEFT JOIN adm_practitioner ap
                ON ap.id = fpr.enterer_id
                    AND ap.delete_flag = '0'
        WHERE aci.encounter_id = #{encounterId}
          AND aci.status_enum IN (#{partRefund},#{refunded},#{refunding})
          AND aci.service_table = #{medMedicationRequest}
          AND aci.refund_id IS NULL
          AND aci.delete_flag = '0'
        UNION
        SELECT aci.encounter_id ,
               aci.bus_no ,
               aci.status_enum AS charge_status,
               aci.quantity_unit ,
               aci.quantity_value ,
               aci.unit_price ,
               aci.total_price ,
               wdr.prescription_no ,
               wdd.status_enum AS dispense_status,
               0 AS service_status,
               wdd.dispense_quantity ,
               dev."name" AS item_name,
               fpr.id AS payment_id,
               fpr.enterer_id,
               fpr.patient_id,
               fpr.status_enum AS payment_status,
               fpr.contract_no,
               fpr.bill_date,
               fpr.charge_item_ids,
               fprd.id AS payment_detail_id,
               fprd.target_enum,
               fprd.account_id,
               fprd.account_code,
               fprd.pay_enum,
               fprd.pay_level_enum,
               fprd.amount ,
               fprd.return_amount,
               fprd.charge_amount,
               ao."name" AS org_name,
               ap."name" AS enterer_name
        FROM adm_charge_item aci
            LEFT JOIN wor_device_request wdr
                ON aci.service_id = wdr.refund_device_id
                    AND wdr.refund_device_id IS NOT NULL
                    AND wdr.delete_flag = '0'
            LEFT JOIN wor_device_dispense wdd
                ON wdr.id = wdd.device_req_id
                    AND wdd.delete_flag = '0'
            INNER JOIN adm_device_definition dev
                ON aci.product_id = dev.id
                    AND dev.delete_flag = '0'
            LEFT JOIN fin_payment_reconciliation fpr
                ON aci.id::TEXT = ANY(string_to_array(fpr.charge_item_ids, ','))
                    AND fpr.status_enum = 1
                    AND fpr.delete_flag = '0'
            LEFT JOIN fin_payment_rec_detail fprd
                ON fpr.id = fprd.reconciliation_id
            AND fprd.delete_flag = '0'
            LEFT JOIN adm_organization ao
                ON ao.id = aci.org_id
            AND ao.delete_flag = '0'
            LEFT JOIN adm_practitioner ap
                ON ap.id = fpr.enterer_id
            AND ap.delete_flag = '0'
        WHERE aci.encounter_id = #{encounterId}
          AND aci.status_enum IN (#{partRefund},#{refunded},#{refunding})
          AND aci.service_table = #{worDeviceRequest}
          AND aci.refund_id IS NULL
          AND aci.delete_flag = '0'
        UNION
        SELECT aci.encounter_id ,
               aci.bus_no ,
               aci.status_enum AS charge_status,
               aci.quantity_unit ,
               aci.quantity_value ,
               aci.unit_price ,
               aci.total_price ,
               wsr.prescription_no ,
               0 AS dispense_status,
               wsr.status_enum AS service_status,
               0 AS dispense_quantity,
               wad."name" AS item_name,
               fpr.id AS payment_id,
               fpr.enterer_id,
               fpr.patient_id,
               fpr.status_enum AS payment_status,
               fpr.contract_no,
               fpr.bill_date,
               fpr.charge_item_ids,
               fprd.id AS payment_detail_id,
               fprd.target_enum,
               fprd.account_id,
               fprd.account_code,
               fprd.pay_enum,
               fprd.pay_level_enum,
               fprd.amount ,
               fprd.return_amount,
               fprd.charge_amount,
               ao."name" AS org_name,
               ap."name" AS enterer_name
        FROM adm_charge_item aci
            LEFT JOIN wor_service_request wsr
                ON aci.service_id = wsr.id
                    AND wsr.refund_service_id IS NOT NULL
                    AND wsr.delete_flag = '0'
            INNER JOIN wor_activity_definition wad
                ON aci.product_id = wad.id
                    AND wad.delete_flag = '0'
            LEFT JOIN fin_payment_reconciliation fpr
                ON aci.id::TEXT = ANY(string_to_array(fpr.charge_item_ids,','))
                    AND fpr.status_enum = 1
                    AND fpr.delete_flag = '0'
            LEFT JOIN fin_payment_rec_detail fprd
                ON fpr.id = fprd.reconciliation_id
            AND fprd.delete_flag = '0'
            LEFT JOIN adm_organization ao
                ON ao.id = aci.org_id
            AND ao.delete_flag = '0'
            LEFT JOIN adm_practitioner ap
                ON ap.id = fpr.enterer_id
            AND ap.delete_flag = '0'
        WHERE aci.encounter_id = #{encounterId}
          AND aci.status_enum IN (#{partRefund},#{refunded},#{refunding})
          AND aci.service_table = #{worServiceRequest}
          AND aci.refund_id IS NULL
          AND aci.delete_flag = '0'
    </select>
</mapper>
