<?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.lantone.mapper.med.KlConceptMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.lantone.entity.med.KlConcept">
        <id column="id" property="id"/>
        <result column="is_deleted" property="isDeleted"/>
        <result column="gmt_create" property="gmtCreate"/>
        <result column="gmt_modified" property="gmtModified"/>
        <result column="creator" property="creator"/>
        <result column="modifier" property="modifier"/>
        <result column="lib_id" property="libId"/>
        <result column="lib_name" property="libName"/>
        <result column="lib_type" property="libType"/>
        <result column="status" property="status"/>
    </resultMap>

    <select id="getLisDetaisByNames" parameterType="com.lantone.vo.med.KllisDetailVO"
            resultType="com.lantone.dto.med.KllisDetailDTO">
        SELECT
        t1.lib_name,
        t1.id,
        t2.min_value,
        t2.max_value,
        t2.type,
        t2.unit
        FROM
        `kl_concept` t1,
        kl_lis t2
        WHERE
        t1.is_deleted = "N"
        AND t2.is_deleted = "N"
        AND t1.id = t2.concept_id
        AND t1.lib_type = 108
        AND t1.`status` = 1
        <if test="names != null and names.size > 0">
            and t1.lib_name in
            <foreach item="name" collection="names" open="(" separator="," close=")">
                #{name}
            </foreach>
        </if>
    </select>


    <select id="indexByApproval" resultType="com.lantone.dto.med.IndexBatchDTO">
        SELECT
        t1.approvalNum,
        t1.approval,
        t1.id,
        t1.NAME,
        t1.regName,
        t1.form,
        t1.status,
        t2.id AS formConceptId
        FROM
        (
        SELECT
        REPLACE ( a.approval, '国药准字', '' )  AS approvalNum,
        a.approval AS approval,
        c.id AS id,
        c.lib_name AS NAME,
        a.NAME AS regName,
        a.form AS form,
        c.status AS status
        FROM
        kl_drug_register a,
        `kl_drug_mapping` b,
        kl_concept c
        WHERE
        a.id = b.register_id
        AND b.drug_concept = c.id
        AND a.is_deleted = 'N'
        AND b.is_deleted = 'N'
        AND c.is_deleted = 'N'
        <if test="status != null">
            AND c.STATUS = #{status}
        </if>
        <if test="approvalList != null and approvalList.size > 0">
            <foreach item="approval" collection="approvalList" open="and(" separator="or" close=")">
                a.approval  =  #{approval}
            </foreach>
        </if>
        ) t1
        LEFT JOIN ( SELECT * FROM kl_concept WHERE is_deleted = 'N' AND STATUS = 1 AND lib_type = 102 ) t2 ON t1.form = t2.lib_name
        <!--GROUP BY
        t1.approval-->
    </select>

    <select id="index" resultType="com.lantone.dto.med.IndexDTO">
        SELECT DISTINCT
        t.id as id,
        t.lib_name as name
        <if test="typeId!=null and typeId==100">
            ,t.code as code
        </if>
        <if test="typeId!=null and typeId==127">
            ,t.code as code
        </if>
        <if test="typeId!=null and typeId==128">
            ,t.code as code
        </if>
        <if test="typeId!=null and typeId==106">
            ,t.code as code
        </if>
        <if test="typeId!=null and typeId==108">
            ,
            t1.max_value as `maxValue`,
            t1.min_value as minValue,
            t1.unit as units,
            t1.type as `range`,
            t4.lib_name AS packName,
            t4.id as packId
        </if>
        FROM
        (
        SELECT DISTINCT
        b.id,
        b.lib_name
        <if test="typeId!=null and typeId==100">
            ,c.icd10_code as code
        </if>
        <if test="typeId!=null and typeId==127">
            ,c.code as code
        </if>
        <if test="typeId!=null and typeId==128">
            ,c.code as code
        </if>
        <if test="typeId!=null and typeId==106">
            ,c.operation_code as code
        </if>
        FROM
        kl_library_info a,
        kl_concept b
        <if test="typeId!=null and typeId==100">
            left join kl_disease c
            on c.is_deleted = 'N'
            and b.id = c.concept_id
        </if>
        <if test="typeId!=null and typeId==127">
            left join kl_tcm_disease c
            on c.is_deleted = 'N'
            and b.id = c.concept_id
        </if>
        <if test="typeId!=null and typeId==128">
            left join kl_tcm_syndrome c
            on c.is_deleted = 'N'
            and b.id = c.concept_id
        </if>
        <if test="typeId!=null and typeId==106">
            LEFT JOIN `kl_operation` c
            ON c.is_deleted = 'N'
            AND b.id = c.concept_id
        </if>
        WHERE
        a.is_deleted = 'N'
        AND b.is_deleted = 'N'
        AND a.concept_id = b.id
        AND a.id = b.lib_id
        AND b.status = 1
        <if test="inputStr!=null and inputStr!=''">
            AND ( a.`name` = #{inputStr} OR LOWER(a.spell) = LOWER(#{inputStr})
            <if test="typeId!=null and typeId==100">
                OR LOWER(c.icd10_code) = LOWER(#{inputStr})
            </if>
            <if test="typeId!=null and typeId==127">
                OR LOWER(c.code) = LOWER(#{inputStr})
            </if>
            <if test="typeId!=null and typeId==128">
                OR LOWER(c.code) = LOWER(#{inputStr})
            </if>
            <if test="typeId!=null and typeId==106">
                OR LOWER(c.operation_code) LIKE LOWER( concat('%',#{inputStr},'%'))
            </if>
            )
        </if>
        <if test="typeIds != null and typeIds.size > 0">
            <foreach item="typeId" collection="typeIds" open="and(" separator="or" close=")">
                a.type_id =#{typeId}
            </foreach>
        </if>
        <if test="notInIds != null and notInIds.size > 0">
            <foreach item="notInId" collection="notInIds" open="and(" separator="and" close=")">
                b.id != #{notInId}
            </foreach>
        </if>
        UNION
        SELECT DISTINCT
        b.id,
        b.lib_name
        <if test="typeId!=null and typeId==100">
            ,c.icd10_code as code
        </if>
        <if test="typeId!=null and typeId==127">
            ,c.code as code
        </if>
        <if test="typeId!=null and typeId==128">
            ,c.code as code
        </if>
        <if test="typeId!=null and typeId==106">
            ,c.operation_code as code
        </if>
        FROM
        kl_library_info a,
        kl_concept b
        <if test="typeId!=null and typeId==100">
            left join kl_disease c
            on c.is_deleted = 'N'
            and b.id = c.concept_id
        </if>
        <if test="typeId!=null and typeId==127">
            left join kl_tcm_disease c
            on c.is_deleted = 'N'
            and b.id = c.concept_id
        </if>
        <if test="typeId!=null and typeId==128">
            left join kl_tcm_syndrome c
            on c.is_deleted = 'N'
            and b.id = c.concept_id
        </if>
        <if test="typeId!=null and typeId==106">
            LEFT JOIN `kl_operation` c
            ON c.is_deleted = 'N'
            AND b.id = c.concept_id
        </if>
        WHERE
        a.is_deleted = 'N'
        AND b.is_deleted = 'N'
        AND a.concept_id = b.id
        AND a.id = b.lib_id
        AND b.status = 1
        <if test="inputStr!=null and inputStr!=''">
            AND ( a.`name` LIKE concat(#{inputStr},'%') OR LOWER(a.spell) LIKE LOWER(concat(#{inputStr},'%'))
            <if test="typeId!=null and typeId==100">
                OR LOWER(c.icd10_code) LIKE LOWER(concat(#{inputStr},'%'))
            </if>
            <if test="typeId!=null and typeId==127">
                OR LOWER(c.code) LIKE LOWER(concat(#{inputStr},'%'))
            </if>
            <if test="typeId!=null and typeId==128">
                OR LOWER(c.code) LIKE LOWER(concat(#{inputStr},'%'))
            </if>
            <if test="typeId!=null and typeId==106">
                OR LOWER(c.operation_code) LIKE LOWER( concat('%',#{inputStr},'%'))
            </if>
            )
        </if>
        <if test="typeIds != null and typeIds.size > 0">
            <foreach item="typeId" collection="typeIds" open="and(" separator="or" close=")">
                a.type_id =#{typeId}
            </foreach>
        </if>
        <if test="notInIds != null and notInIds.size > 0">
            <foreach item="notInId" collection="notInIds" open="and(" separator="and" close=")">
                b.id != #{notInId}
            </foreach>
        </if>
        UNION
        SELECT DISTINCT
        b.id,
        b.lib_name
        <if test="typeId!=null and typeId==100">
            ,c.icd10_code as code
        </if>
        <if test="typeId!=null and typeId==127">
            ,c.code as code
        </if>
        <if test="typeId!=null and typeId==128">
            ,c.code as code
        </if>
        <if test="typeId!=null and typeId==106">
            ,c.operation_code as code
        </if>
        FROM
        kl_library_info a,
        kl_concept b
        <if test="typeId!=null and typeId==100">
            left join kl_disease c
            on c.is_deleted = 'N'
            and b.id = c.concept_id
        </if>
        <if test="typeId!=null and typeId==127">
            left join kl_tcm_disease c
            on c.is_deleted = 'N'
            and b.id = c.concept_id
        </if>
        <if test="typeId!=null and typeId==128">
            left join kl_tcm_syndrome c
            on c.is_deleted = 'N'
            and b.id = c.concept_id
        </if>
        <if test="typeId!=null and typeId==106">
            LEFT JOIN `kl_operation` c
            ON c.is_deleted = 'N'
            AND b.id = c.concept_id
        </if>
        WHERE
        a.is_deleted = 'N'
        AND b.is_deleted = 'N'
        AND a.concept_id = b.id
        AND a.id = b.lib_id
        AND b.status = 1
        <if test="inputStr!=null and inputStr!=''">
            AND ( a.`name` LIKE concat('%',#{inputStr},'%') OR LOWER(a.spell) LIKE LOWER( concat('%',#{inputStr},'%'))
            <if test="typeId!=null and typeId==100">
                OR LOWER(c.icd10_code) LIKE LOWER( concat('%',#{inputStr},'%'))
            </if>
            <if test="typeId!=null and typeId==127">
                OR LOWER(c.code) LIKE LOWER( concat('%',#{inputStr},'%'))
            </if>
            <if test="typeId!=null and typeId==128">
                OR LOWER(c.code) LIKE LOWER( concat('%',#{inputStr},'%'))
            </if>
            <if test="typeId!=null and typeId==106">
                OR LOWER(c.operation_code) LIKE LOWER( concat('%',#{inputStr},'%'))
            </if>
            )
        </if>
        <if test="typeIds != null and typeIds.size > 0">
            <foreach item="typeId" collection="typeIds" open="and(" separator="or" close=")">
                a.type_id =#{typeId}
            </foreach>
        </if>
        <if test="notInIds != null and notInIds.size > 0">
            <foreach item="notInId" collection="notInIds" open="and(" separator="and" close=")">
                b.id != #{notInId}
            </foreach>
        </if>
        ) t
        <if test="typeId!=null and typeId==108">
            LEFT JOIN kl_lis t1 ON t.id = t1.concept_id
            AND t1.is_deleted = 'N'
            LEFT JOIN kl_relation t3 ON t.id = t3.end_id
            AND t3.relation_id = 600
            AND t3.is_deleted = 'N'
            LEFT JOIN kl_concept t4 ON t3.start_id = t4.id
            AND t4.lib_type = 107
            AND t4.is_deleted = 'N'
        </if>
        <if test="size!=null">
            LIMIT #{size}
        </if>
    </select>

</mapper>
