<?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.basicservice.mapper.HealthcareServiceBizMapper">

    <select id="getHealthcareServicePage" resultType="com.openhis.web.basicservice.dto.HealthcareServiceDto">
        SELECT
            T3.id,
            T3.tenant_id,
            T3.active_flag,
            T3.offered_org_id,
            T3.category_code,
            T3.type_code,
            T3.specialty_code,
            T3.location_id,
            T3.NAME,
            T3.COMMENT,
            T3.extra_details,
            T3.contact,
            T3.appointment_required_flag,
            T3.definition_id,
            T3.charge_name,
            T3.price,
            T3.activity_price
        FROM
            (
                SELECT
                    T1.ID,
                    T1.tenant_id,
                    T1.active_flag,
                    T1.offered_org_id,
                    T1.category_code,
                    T1.type_code,
                    T1.specialty_code,
                    T1.location_id,
                    T1.NAME,
                    T1.COMMENT,
                    T1.extra_details,
                    T1.contact,
                    T1.appointment_required_flag,
                    T2.ID AS definition_id,
                    T2.charge_name,
                    T2.price,
                    COALESCE ( activity_sum.price_sum, 0 ) AS activity_price
                FROM
                    adm_healthcare_service AS T1
                        LEFT JOIN adm_charge_item_definition AS T2 ON T2.instance_id = T1.ID
                        AND T2.instance_table = #{tableName}
                        LEFT JOIN (
                        SELECT
                            tad.item_no,
                            SUM ( acid.price ) AS price_sum
                        FROM
                            tmp_activity_device AS tad
                                JOIN adm_charge_item_definition AS acid ON acid.instance_id = tad.dev_act_id
                                AND acid.delete_flag = '0'
                        WHERE
                            tad.delete_flag = '0'
                          AND tad.dev_act_table = #{activityTableName}
                        GROUP BY
                            tad.item_no
                    ) AS activity_sum ON activity_sum.item_no = CAST ( T1.ID AS TEXT )
                WHERE
                    T1.delete_flag = '0'
                ORDER BY
                    T1.create_time DESC
            ) AS T3
            ${ew.customSqlSegment}
    </select>

</mapper>