<?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.xinelu.manage.mapper.specialdiseaseroute.SpecialDiseaseRouteMapper">

    <resultMap type="SpecialDiseaseRoute" id="SpecialDiseaseRouteResult">
        <result property="id" column="id"/>
	    <result property="parentRouteId" column="parent_route_id"/>
        <result property="departmentId" column="department_id"/>
        <result property="departmentName" column="department_name"/>
        <result property="diseaseTypeId" column="disease_type_id"/>
        <result property="diseaseTypeName" column="disease_type_name"/>
        <result property="routeName" column="route_name"/>
        <result property="routeCode" column="route_code"/>
        <result property="version" column="version"/>
        <result property="routeClassify" column="route_classify"/>
        <result property="releaseStatus" column="release_status"/>
        <result property="suitRange" column="suit_range"/>
        <result property="routeSort" column="route_sort"/>
        <result property="routeRemark" column="route_remark"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
    </resultMap>

    <resultMap type="com.xinelu.manage.vo.specialdiseaseroute.SpecialDiseaseRouteVO" id="SpecialDiseaseRouteVOResult">
        <result property="specialDiseaseRouteId" column="specialDiseaseRouteId"/>
        <result property="departmentId" column="department_id"/>
        <result property="departmentName" column="department_name"/>
        <result property="diseaseTypeId" column="disease_type_id"/>
        <result property="diseaseTypeName" column="disease_type_name"/>
        <result property="routeName" column="route_name"/>
        <result property="routeCode" column="route_code"/>
        <result property="version" column="version"/>
        <result property="routeClassify" column="route_classify"/>
        <result property="releaseStatus" column="release_status"/>
        <result property="suitRange" column="suit_range"/>
        <result property="routeSort" column="route_sort"/>
        <result property="agreeNumber" column="agreeNumber"/>
        <result property="totalNumber" column="totalNumber"/>
        <result property="routeRemark" column="route_remark"/>
        <collection property="triggerConditionList" javaType="java.util.List"
                    resultMap="SpecialDiseaseTriggerConditionResult"/>
        <collection property="routePackageList" javaType="java.util.List"
                    resultMap="SpecialDiseaseRoutePackageResult"/>
    </resultMap>
	<resultMap type="com.xinelu.manage.vo.specialdiseaseroute.SpecialDiseaseRouteInfoVo" id="SpecialDiseaseRouteInfoVOResult">
		<result property="specialDiseaseRouteId" column="specialDiseaseRouteId"/>
		<result property="departmentId" column="department_id"/>
		<result property="departmentName" column="department_name"/>
		<result property="diseaseTypeId" column="disease_type_id"/>
		<result property="diseaseTypeName" column="disease_type_name"/>
		<result property="routeName" column="route_name"/>
		<result property="routeCode" column="route_code"/>
		<result property="version" column="version"/>
		<result property="routeClassify" column="route_classify"/>
		<result property="releaseStatus" column="release_status"/>
		<result property="suitRange" column="suit_range"/>
		<result property="routeSort" column="route_sort"/>
		<result property="agreeNumber" column="agreeNumber"/>
		<result property="totalNumber" column="totalNumber"/>
		<collection
			property="childrenRouteList"
			ofType="com.xinelu.manage.vo.specialdiseaseroute.SpecialDiseaseRouteInfoVo"
			column="specialDiseaseRouteId"
			select="getChildList"
		/>
		<collection property="routePackageList" javaType="java.util.List"
			resultMap="SpecialDiseaseRoutePackageResult"/>
	</resultMap>

	<resultMap type="com.xinelu.manage.vo.specialdiseaseroute.SpecialDiseaseRouteAuditVo" id="SpecialDiseaseRouteAuditResult">
		<result property="id" column="id"/>
		<result property="diseaseTypeId" column="disease_type_id"/>
		<result property="diseaseTypeName" column="disease_type_name"/>
		<result property="routeName" column="route_name"/>
		<collection
			property="nodeList"
			ofType="com.xinelu.manage.vo.specialdiseasenode.SpecialDiseaseNodeAuditVo"
			column="id"
			select="com.xinelu.manage.mapper.specialdiseasenode.SpecialDiseaseNodeMapper.selectByRouteId"
		/>
		<collection property="childRouteList" javaType="java.util.List"
			column="id"
			ofType="com.xinelu.manage.vo.specialdiseasenode.SpecialDiseaseNodeAuditVo"
			select="selectAuditChildRoute"
			/>
	</resultMap>

	<resultMap type="com.xinelu.manage.vo.specialdiseaseroute.SpecialDiseaseChildRouteAuditVo" id="SpecialDiseaseChildRouteAuditResult">
		<result property="id" column="id"/>
		<result property="diseaseTypeId" column="disease_type_id"/>
		<result property="diseaseTypeName" column="disease_type_name"/>
		<result property="routeName" column="route_name"/>
		<collection
			property="nodeList"
			ofType="com.xinelu.manage.vo.specialdiseasenode.SpecialDiseaseNodeAuditVo"
			column="id"
			select="com.xinelu.manage.mapper.specialdiseasenode.SpecialDiseaseNodeMapper.selectByRouteId"
		/>
	</resultMap>

    <resultMap type="com.xinelu.manage.vo.specialdiseasetriggercondition.TriggerConditionVO"
               id="SpecialDiseaseTriggerConditionResult">
        <result property="triggerConditionId" column="triggerConditionId"/>
        <result property="routeId" column="route_id"/>
        <result property="routeName" column="route_name"/>
        <result property="triggerConditionCode" column="trigger_condition_code"/>
        <result property="triggerConditionName" column="trigger_condition_name"/>
        <result property="triggerConditionOperator" column="trigger_condition_operator"/>
        <result property="triggerConditionValue" column="trigger_condition_value"/>
        <result property="triggerConditionSort" column="trigger_condition_sort"/>
        <result property="triggerConditionRemark" column="trigger_condition_remark"/>
    </resultMap>

    <resultMap type="com.xinelu.manage.vo.specialdiseaseroutepackage.SpecialDiseaseRoutePackageVO" id="SpecialDiseaseRoutePackageResult">
        <result property="specialDiseaseRoutePackageId" column="id"/>
        <result property="routeId" column="route_id"/>
        <result property="servicePackageId" column="service_package_id"/>
        <result property="routeName" column="route_name"/>
        <result property="packageName" column="package_name"/>
    </resultMap>

    <sql id="selectSpecialDiseaseRouteVo">
        select id,
               parent_route_id,
               department_id,
               department_name,
               disease_type_id,
               disease_type_name,
               route_name,
               route_code,
               version,
               route_classify,
               release_status,
               suit_range,
               route_sort,
               route_remark,
               create_by,
               create_time,
               update_by,
               update_time
        from special_disease_route
    </sql>

    <select id="selectSpecialDiseaseRouteList" parameterType="SpecialDiseaseRoute"
            resultMap="SpecialDiseaseRouteResult">
        <include refid="selectSpecialDiseaseRouteVo"/>
        <where>
	        <if test="parentRouteId != null ">
		        and parent_route_id = #{parentRouteId}
	        </if>
            <if test="departmentId != null ">
                and department_id = #{departmentId}
            </if>
            <if test="departmentName != null  and departmentName != ''">
                and department_name like concat('%', #{departmentName}, '%')
            </if>
            <if test="diseaseTypeId != null ">
                and disease_type_id = #{diseaseTypeId}
            </if>
            <if test="diseaseTypeName != null  and diseaseTypeName != ''">
                and disease_type_name like concat('%', #{diseaseTypeName}, '%')
            </if>
            <if test="routeName != null  and routeName != ''">
                and route_name like concat('%', #{routeName}, '%')
            </if>
            <if test="routeCode != null  and routeCode != ''">
                and route_code = #{routeCode}
            </if>
            <if test="version != null  and version != ''">
                and version = #{version}
            </if>
            <if test="routeClassify != null  and routeClassify != ''">
                and route_classify = #{routeClassify}
            </if>
            <if test="releaseStatus != null  and releaseStatus != ''">
                and release_status = #{releaseStatus}
            </if>
            <if test="suitRange != null  and suitRange != ''">
                and suit_range = #{suitRange}
            </if>
            <if test="routeSort != null ">
                and route_sort = #{routeSort}
            </if>
            <if test="routeRemark != null  and routeRemark != ''">
                and route_remark = #{routeRemark}
            </if>
        </where>
    </select>

	<select id="selectRouteAndChildren" parameterType="Long"
		resultMap="SpecialDiseaseRouteResult">
		<include refid="selectSpecialDiseaseRouteVo"/>
		where id = #{id} or parent_route_id = #{id}
	</select>
    <select id="selectDiseaseRouteList"
            resultType="com.xinelu.manage.vo.specialdiseaseroute.SpecialDiseaseRouteVO">
        select sdr.id,
        sdr.department_id,
        sdr.department_name,
        sdr.disease_type_id,
        sdr.disease_type_name,
        sdr.route_name,
        sdr.route_code,
        sdr.version,
        sdr.route_classify,
        sdr.release_status,
        sdr.suit_range,
        sdr.route_sort,
        sdr.route_remark,
        sdr.create_by,
        sdr.create_time,
        sdr.update_by,
        sdr.update_time
        from special_disease_route sdr
        left join special_disease_route_package sdrp ON sdrp.route_id = sdr.id
        left join department d on d.id = sdr.department_id
        <where>
	        <if test="hospitalAgencyId != null ">
		        and d.hospital_agency_id = #{hospitalAgencyId}
	        </if>
            <if test="departmentId != null ">
                and sdr.department_id = #{departmentId}
            </if>
            <if test="departmentName != null  and departmentName != ''">
                and sdr.department_name like concat('%', #{departmentName}, '%')
            </if>
            <if test="diseaseTypeId != null ">
                and sdr.disease_type_id = #{diseaseTypeId}
            </if>
            <if test="diseaseTypeName != null  and diseaseTypeName != ''">
                and sdr.disease_type_name like concat('%', #{diseaseTypeName}, '%')
            </if>
	        <if test="parentRouteId == null">
		        and (sdr.parent_route_id = 0 or sdr.parent_route_id is null)
	        </if>
	        <if test="parentRouteId != null">
		        and sdr.parent_route_id = #{parentRouteId}
	        </if>
            <if test="routeName != null  and routeName != ''">
                and sdr.route_name like concat('%', #{routeName}, '%')
            </if>
            <if test="routeCode != null  and routeCode != ''">
                and sdr.route_code = #{routeCode}
            </if>
            <if test="version != null  and version != ''">
                and sdr.version = #{version}
            </if>
            <if test="routeClassify != null  and routeClassify != ''">
                and sdr.route_classify = #{routeClassify}
            </if>
            <if test="releaseStatus != null  and releaseStatus != ''">
                and sdr.release_status = #{releaseStatus}
            </if>
            <if test="suitRange != null  and suitRange != ''">
                and sdr.suit_range = #{suitRange}
            </if>
            <if test="routeSort != null ">
                and sdr.route_sort = #{routeSort}
            </if>
            <if test="routeRemark != null  and routeRemark != ''">
                and sdr.route_remark = #{routeRemark}
            </if>
            <if test="servicePackageId != null">
                and sdrp.service_package_id = #{servicePackageId}
            </if>
        </where>
        GROUP BY sdr.id order by create_time DESC,route_sort ASC
    </select>

	<select id="getRouteAuditInfo"
		resultType="com.xinelu.manage.vo.specialdiseaseroute.SpecialDiseaseRouteAuditVo"
		resultMap="SpecialDiseaseRouteAuditResult">
		select sdr.id,
		sdr.disease_type_id,
		sdr.disease_type_name,
		sdr.route_name
		from special_disease_route sdr
		where id = #{routeId}
	</select>
	<select id="selectAuditChildRoute"
		resultType="com.xinelu.manage.vo.specialdiseaseroute.SpecialDiseaseChildRouteAuditVo"
		resultMap="SpecialDiseaseChildRouteAuditResult">
		select sdr.id,
		       sdr.disease_type_id,
		       sdr.disease_type_name,
		       sdr.route_name
		from special_disease_route sdr
		where parent_route_id = #{parentRouteId}
	</select>

    <select id="selectSpecialDiseaseRouteById" parameterType="Long"
            resultMap="SpecialDiseaseRouteResult">
        <include refid="selectSpecialDiseaseRouteVo"/>
        where id = #{id}
    </select>

    <insert id="insertSpecialDiseaseRoute" parameterType="SpecialDiseaseRoute" useGeneratedKeys="true"
            keyProperty="id">
        insert into special_disease_route
        <trim prefix="(" suffix=")" suffixOverrides=",">
	        <if test="parentRouteId != null">parent_route_id,
	        </if>
            <if test="departmentId != null">department_id,
            </if>
            <if test="departmentName != null">department_name,
            </if>
            <if test="diseaseTypeId != null">disease_type_id,
            </if>
            <if test="diseaseTypeName != null">disease_type_name,
            </if>
            <if test="routeName != null">route_name,
            </if>
            <if test="routeCode != null">route_code,
            </if>
            <if test="version != null">version,
            </if>
            <if test="routeClassify != null">route_classify,
            </if>
            <if test="releaseStatus != null">release_status,
            </if>
            <if test="suitRange != null">suit_range,
            </if>
            <if test="routeSort != null">route_sort,
            </if>
            <if test="routeRemark != null">route_remark,
            </if>
            <if test="createBy != null">create_by,
            </if>
            <if test="createTime != null">create_time,
            </if>
            <if test="updateBy != null">update_by,
            </if>
            <if test="updateTime != null">update_time,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
	        <if test="parentRouteId != null">#{parentRouteId},
	        </if>
            <if test="departmentId != null">#{departmentId},
            </if>
            <if test="departmentName != null">#{departmentName},
            </if>
            <if test="diseaseTypeId != null">#{diseaseTypeId},
            </if>
            <if test="diseaseTypeName != null">#{diseaseTypeName},
            </if>
            <if test="routeName != null">#{routeName},
            </if>
            <if test="routeCode != null">#{routeCode},
            </if>
            <if test="version != null">#{version},
            </if>
            <if test="routeClassify != null">#{routeClassify},
            </if>
            <if test="releaseStatus != null">#{releaseStatus},
            </if>
            <if test="suitRange != null">#{suitRange},
            </if>
            <if test="routeSort != null">#{routeSort},
            </if>
            <if test="routeRemark != null">#{routeRemark},
            </if>
            <if test="createBy != null">#{createBy},
            </if>
            <if test="createTime != null">#{createTime},
            </if>
            <if test="updateBy != null">#{updateBy},
            </if>
            <if test="updateTime != null">#{updateTime},
            </if>
        </trim>
    </insert>

    <update id="updateSpecialDiseaseRoute" parameterType="SpecialDiseaseRoute">
        update special_disease_route
        <trim prefix="SET" suffixOverrides=",">
	        <if test="parentRouteId != null">parent_route_id=
		        #{parentRouteId},
	        </if>
            <if test="departmentId != null">department_id =
                #{departmentId},
            </if>
            <if test="departmentName != null">department_name =
                #{departmentName},
            </if>
            <if test="diseaseTypeId != null">disease_type_id =
                #{diseaseTypeId},
            </if>
            <if test="diseaseTypeName != null">disease_type_name =
                #{diseaseTypeName},
            </if>
            <if test="routeName != null">route_name =
                #{routeName},
            </if>
            <if test="routeCode != null">route_code =
                #{routeCode},
            </if>
            <if test="version != null">version =
                #{version},
            </if>
            <if test="routeClassify != null">route_classify =
                #{routeClassify},
            </if>
            <if test="releaseStatus != null">release_status =
                #{releaseStatus},
            </if>
            <if test="suitRange != null">suit_range =
                #{suitRange},
            </if>
            <if test="routeSort != null">route_sort =
                #{routeSort},
            </if>
            <if test="routeRemark != null">route_remark =
                #{routeRemark},
            </if>
            <if test="createBy != null">create_by =
                #{createBy},
            </if>
            <if test="createTime != null">create_time =
                #{createTime},
            </if>
            <if test="updateBy != null">update_by =
                #{updateBy},
            </if>
            <if test="updateTime != null">update_time =
                #{updateTime},
            </if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteSpecialDiseaseRouteById" parameterType="Long">
        delete
        from special_disease_route
        where id = #{id}
    </delete>

    <delete id="deleteSpecialDiseaseRouteByIds" parameterType="String">
        delete from special_disease_route where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="departmentRouteByDepartmentName" resultType="com.xinelu.manage.vo.department.DepartmentVO">
        select dt.id,
        dt.department_name,
        dt.department_code,
        ( SELECT COUNT(1)
        FROM special_disease_route a
        WHERE a.department_id = dt.id and (a.parent_route_id = 0 or a.parent_route_id is null)
        <if test="releaseStatus != null  and releaseStatus != ''">
            AND a.release_status= #{releaseStatus}
        </if>
        ) AS countNum
        from department dt left join special_disease_route sdr on dt.id = sdr.department_id
	    left join special_disease_route_package sdrp ON sdr.id = sdrp.route_id
        <where>
	        <if test="hospitalAgencyId != null ">
		        and dt.hospital_agency_id = #{hospitalAgencyId}
	        </if>
            <if test="departmentName != null  and departmentName != ''">
                and dt.department_name like concat('%',#{departmentName},'%')
            </if>
            <if test="releaseStatus != null  and releaseStatus != ''">
                AND sdr.release_status= #{releaseStatus}
            </if>
	        <if test="servicePackageId != null  and servicePackageId != ''">
		        AND sdrp.service_package_id= #{servicePackageId}
	        </if>
	        <if test="parentRouteId == null">
		        AND (sdr.parent_route_id = 0 or sdr.parent_route_id is null)
	        </if>
        </where>
        GROUP BY dt.id
    </select>

    <select id="selectSpecialDiseaseRouteAndTriggerById"
            resultType="com.xinelu.manage.vo.specialdiseaseroute.SpecialDiseaseRouteVO"
            resultMap="SpecialDiseaseRouteVOResult">
        select sdr.id                                   specialDiseaseRouteId,
               sdr.department_id,
               sdr.department_name,
               sdr.disease_type_id,
               sdr.disease_type_name,
               sdr.route_name,
               sdr.route_code,
               sdr.version,
               sdr.route_classify,
               sdr.release_status,
               sdr.suit_range,
               sdr.route_sort,
               sdr.route_remark,
               sdtc.id                                  triggerConditionId,
               sdtc.route_id,
               sdtc.route_name,
               sdtc.trigger_condition_code,
               sdtc.trigger_condition_name,
               sdtc.trigger_condition_operator,
               sdtc.trigger_condition_value,
               sdtc.trigger_condition_sort,
               sdtc.trigger_condition_remark,
               sdrp.id                                  specialDiseaseRoutePackageId,
               sdrp.route_id,
               sdrp.service_package_id,
               sdrp.route_name,
               sdrp.package_name,
               (select COUNT(1)
                from special_disease_node
                where route_id = specialDiseaseRouteId) totalNumber,
               (select COUNT(1)
                from special_disease_node
                where route_id = specialDiseaseRouteId
                  and route_check_status = 'AGREE')     agreeNumber
        from special_disease_route sdr
                 LEFT JOIN special_disease_node sdn ON sdn.route_id = sdr.id
                 LEFT JOIN special_disease_trigger_condition sdtc ON sdr.id = sdtc.route_id
                 LEFT JOIN special_disease_route_package sdrp ON sdr.id = sdrp.route_id
        where sdr.id = #{id}
    </select>

	<select id="getRouteAndChild" resultType="com.xinelu.manage.vo.specialdiseaseroute.SpecialDiseaseRouteInfoVo"
		resultMap="SpecialDiseaseRouteInfoVOResult">
		select sdr.id,
		       sdr.id  specialDiseaseRouteId,
			   sdr.parent_route_id,
		       sdr.department_id,
		       sdr.department_name,
		       sdr.disease_type_id,
		       sdr.disease_type_name,
		       sdr.route_name,
		       sdr.route_code,
		       sdr.version,
		       sdr.route_classify,
		       sdr.release_status,
		       sdr.suit_range,
		       sdr.route_sort,
		       sdr.route_remark,
		       sdrp.id  specialDiseaseRoutePackageId,
		       sdrp.route_id,
		       sdrp.service_package_id,
		       sdrp.route_name,
		       sdrp.package_name,
		       (select COUNT(1)
		        from special_disease_node
		        where route_id = specialDiseaseRouteId) totalNumber,
		       (select COUNT(1)
		        from special_disease_node
		        where route_id = specialDiseaseRouteId
			      and route_check_status = 'AGREE')     agreeNumber
		from special_disease_route sdr
	     	LEFT JOIN special_disease_route_package sdrp ON sdr.id = sdrp.route_id
		<where>
			<if test="id != null">
				and sdr.id = #{id}
			</if>
		</where>
	</select>

	<select id="getChildList" resultType="com.xinelu.manage.vo.specialdiseaseroute.SpecialDiseaseRouteInfoVo" resultMap="SpecialDiseaseRouteInfoVOResult">
		select
		sdr.id,
		sdr.id  specialDiseaseRouteId,
		sdr.parent_route_id,
		sdr.department_id,
		sdr.department_name,
		sdr.disease_type_id,
		sdr.disease_type_name,
		sdr.route_name,
		sdr.route_code,
		sdr.version,
		sdr.route_classify,
		sdr.release_status,
		sdr.suit_range,
		sdr.route_sort,
		sdr.route_remark,
		(select COUNT(1)
		from special_disease_node
		where route_id = specialDiseaseRouteId) totalNumber,
		(select COUNT(1)
		from special_disease_node
		where route_id = specialDiseaseRouteId
		and route_check_status = 'AGREE')     agreeNumber
		from special_disease_route sdr
		<where>
			sdr.parent_route_id = #{specialDiseaseRouteId}
		</where>
	</select>

    <select id="selectRouteCheckStatus" resultType="java.lang.String">
        select route_check_status
        from special_disease_node
        where route_id = #{routeId}
    </select>
</mapper>