<?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.department.DepartmentMapper">

    <resultMap type="Department" id="DepartmentResult">
        <result property="id" column="id"/>
        <result property="parentDepartmentId" column="parent_department_id"/>
        <result property="agencyId" column="agency_id"/>
        <result property="agencyName" column="agency_name"/>
        <result property="departmentName" column="department_name"/>
        <result property="departmentCode" column="department_code"/>
        <result property="departmentType" column="department_type"/>
        <result property="departmentAbbreviation" column="department_abbreviation"/>
        <result property="departmentPersonId" column="department_person_id"/>
        <result property="departmentPersonName" column="department_person_name"/>
        <result property="nodeType" column="node_type"/>
        <result property="provideServiceCategory" column="provide_service_category"/>
        <result property="subdivisionCategoryId" column="subdivision_category_id"/>
        <result property="subdivisionCategoryName" column="subdivision_category_name"/>
        <result property="normDepartmentCompareId" column="norm_department_compare_id"/>
        <result property="normDepartmentCompareName" column="norm_department_compare_name"/>
        <result property="prepareBedsCount" column="prepare_beds_count"/>
        <result property="departmentPhone" column="department_phone"/>
        <result property="departmentMail" column="department_mail"/>
        <result property="establishDate" column="establish_date"/>
        <result property="revokeDate" column="revoke_date"/>
        <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>

    <sql id="selectDepartmentVo">
        select id,
               parent_department_id,
               agency_id,
               agency_name,
               department_name,
               department_code,
               department_type,
               department_abbreviation,
               department_person_id,
               department_person_name,
               node_type,
               provide_service_category,
               subdivision_category_id,
               subdivision_category_name,
               norm_department_compare_id,
               norm_department_compare_name,
               prepare_beds_count,
               department_phone,
               department_mail,
               establish_date,
               revoke_date,
               create_by,
               create_time,
               update_by,
               update_time
        from department
    </sql>

    <select id="selectDepartmentList" parameterType="Department" resultMap="DepartmentResult">
        <include refid="selectDepartmentVo"/>
        <where>
            <if test="parentDepartmentId != null ">
                and parent_department_id =#{parentDepartmentId}
            </if>
            <if test="agencyId != null ">
                and agency_id =#{agencyId}
            </if>
            <if test="agencyName != null  and agencyName != ''">
                and agency_name like concat('%',#{agencyName},'%')
            </if>
            <if test="departmentName != null  and departmentName != ''">
                and department_name like concat('%',#{departmentName},'%')
            </if>
            <if test="departmentCode != null  and departmentCode != ''">
                and department_code =#{departmentCode}
            </if>
            <if test="departmentType != null  and departmentType != ''">
                and department_type =#{departmentType}
            </if>
            <if test="departmentAbbreviation != null  and departmentAbbreviation != ''">
                and department_abbreviation =#{departmentAbbreviation}
            </if>
            <if test="departmentPersonId != null ">
                and department_person_id =#{departmentPersonId}
            </if>
            <if test="departmentPersonName != null  and departmentPersonName != ''">
                and department_person_name like concat ('%',#{departmentPersonName},'%')
            </if>
            <if test="nodeType != null  and nodeType != ''">
                and node_type =#{nodeType}
            </if>
            <if test="provideServiceCategory != null  and provideServiceCategory != ''">
                and provide_service_category =#{provideServiceCategory}
            </if>
            <if test="subdivisionCategoryId != null ">
                and subdivision_category_id =#{subdivisionCategoryId}
            </if>
            <if test="subdivisionCategoryName != null  and subdivisionCategoryName != ''">
                and subdivision_category_name like concat('%',#{subdivisionCategoryName},'%')
            </if>
            <if test="normDepartmentCompareId != null ">
                and norm_department_compare_id =#{normDepartmentCompareId}
            </if>
            <if test="normDepartmentCompareName != null  and normDepartmentCompareName != ''">
                and norm_department_compare_name like concat('%',#{normDepartmentCompareName},'%')
            </if>
            <if test="prepareBedsCount != null ">
                and prepare_beds_count =#{prepareBedsCount}
            </if>
            <if test="departmentPhone != null  and departmentPhone != ''">
                and department_phone =#{departmentPhone}
            </if>
            <if test="departmentMail != null  and departmentMail != ''">
                and department_mail =#{departmentMail}
            </if>
            <if test="establishDate != null ">
                and establish_date =#{establishDate}
            </if>
            <if test="revokeDate != null ">
                and revoke_date =#{revokeDate}
            </if>
        </where>
    </select>

    <select id="selectDepartmentById" parameterType="Long"
            resultMap="DepartmentResult">
        <include refid="selectDepartmentVo"/>
        where id = #{id}
    </select>

    <select id="selectDepartment" resultType="com.xinelu.manage.vo.department.DepartmentListVO">
        select id,
               dt.parent_department_id,
               agency_id,
               agency_name,
               department_name,
               department_code,
               department_type,
               department_abbreviation,
               department_person_id,
               department_person_name,
               node_type,
               provide_service_category,
               subdivision_category_id,
               subdivision_category_name,
               norm_department_compare_id,
               norm_department_compare_name,
               prepare_beds_count,
               department_phone,
               department_mail,
               establish_date,
               revoke_date,
               create_by,
               create_time,
               update_by,
               update_time,
               (select department_name from department where id = dt.parent_department_id) parentDepartmentName
        from department dt
        where id = #{id}
    </select>

    <select id="selectDepartmentListScriptNum" resultType="com.xinelu.manage.vo.department.DepartmentVO">
        select d.id,
        d.department_name,
        d.department_code,
        count(si.id) AS countNum
        from department d left join script_info si on d.id = si.department_id
        <where>
            <if test="departmentCode != null  and departmentCode != ''">
                and d.department_code = #{departmentCode}
            </if>
            <if test="departmentName != null  and departmentName != ''">
                and d.department_name like concat('%', #{departmentName}, '%' )
            </if>
            <if test="scriptStatus != null  and scriptStatus != ''">
                and si.script_status = #{scriptStatus}
            </if>
        </where>
        GROUP BY d.id,
        d.department_name,
        d.department_code
    </select>

    <select id="selectDepartmentListOperationNum" resultType="com.xinelu.manage.vo.department.DepartmentVO">
        select d.id,
        d.department_name,
        d.department_code,
        count(oi.id) AS countNum
        from department d left join operation_info oi on d.id = oi.department_id
        <where>
            <if test="departmentCode != null  and departmentCode != ''">
                and d.department_code =
                #{departmentCode}
            </if>
            <if test="departmentName != null  and departmentName != ''">
                and d.department_name like concat('%',
                #{departmentName},
                '%'
                )
            </if>
        </where>
        GROUP BY d.id,
        d.department_name,
        d.department_code
    </select>
    <select id="selectDepartmentListWechatTemplateNum"
            resultType="com.xinelu.manage.vo.department.DepartmentVO">
        select d.id,
        d.department_name,
        d.department_code,
        count(wt.id) AS countNum
        from department d left join wechat_template wt on d.id = wt.department_id
        <where>
            <if test="departmentCode != null  and departmentCode != ''">
                and d.department_code =#{departmentCode}
            </if>
            <if test="departmentName != null  and departmentName != ''">
                and d.department_name like concat('%',#{departmentName},'%')
            </if>
            <if test="templateSource != null  and templateSource != ''">
                and wt.template_source =#{templateSource}
            </if>
        </where>
        GROUP BY d.id,
        d.department_name,
        d.department_code
    </select>
    <select id="selectDepartmentListMessageNum" resultType="com.xinelu.manage.vo.department.DepartmentVO">
        select d.id,
        d.department_name,
        d.department_code,
        count(tm.id) AS countNum
        from department d left join text_message tm on d.id = tm.department_id
        <where>
            <if test="departmentCode != null  and departmentCode != ''">
                and d.department_code = #{departmentCode}
            </if>
            <if test="departmentName != null  and departmentName != ''">
                and d.department_name like concat('%',#{departmentName},'%')
            </if>
            <if test="textMessageStatus != null  and textMessageStatus != ''">
                and tm.text_message_status = #{textMessageStatus}
            </if>
        </where>
        GROUP BY d.id,
        d.department_name,
        d.department_code
    </select>

    <select id="getAllDepartmentInfo" resultType="com.xinelu.manage.domain.department.Department">
        select id,
        parent_department_id,
        agency_id,
        agency_name,
        department_name,
        department_code,
        department_type,
        department_abbreviation,
        department_person_id,
        department_person_name,
        node_type,
        provide_service_category,
        subdivision_category_id,
        subdivision_category_name,
        norm_department_compare_id,
        norm_department_compare_name,
        prepare_beds_count,
        department_phone,
        department_mail,
        establish_date,
        revoke_date
        from department
        <where>
            <if test="departmentNames != null and departmentNames.size() > 0">
                and department_name in
                <foreach item="departmentName" collection="departmentNames" open="(" separator="," close=")">
                    #{departmentName}
                </foreach>
            </if>
        </where>
    </select>

    <select id="selectListServicePackageNum" resultType="com.xinelu.manage.vo.department.DepartmentVO"
            parameterType="com.xinelu.manage.dto.department.DepartmentDTO">
        select d.id,
        d.department_name,
        d.department_code,
        count(sp.id) AS countNum
        from department d left join service_package sp on d.id = sp.department_id
        <where>
            <if test="departmentCode != null  and departmentCode != ''">
                and d.department_code = #{departmentCode}
            </if>
            <if test="departmentName != null  and departmentName != ''">
                and d.department_name like concat('%',#{departmentName},'%')
            </if>
        </where>
        GROUP BY d.id,
        d.department_name,
        d.department_code
    </select>

    <insert id="insertDepartment" parameterType="Department" useGeneratedKeys="true"
            keyProperty="id">
        insert into department
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="parentDepartmentId != null">parent_department_id,
            </if>
            <if test="agencyId != null">agency_id,
            </if>
            <if test="agencyName != null">agency_name,
            </if>
            <if test="departmentName != null">department_name,
            </if>
            <if test="departmentCode != null">department_code,
            </if>
            <if test="departmentType != null">department_type,
            </if>
            <if test="departmentAbbreviation != null">department_abbreviation,
            </if>
            <if test="departmentPersonId != null">department_person_id,
            </if>
            <if test="departmentPersonName != null">department_person_name,
            </if>
            <if test="nodeType != null">node_type,
            </if>
            <if test="provideServiceCategory != null">provide_service_category,
            </if>
            <if test="subdivisionCategoryId != null">subdivision_category_id,
            </if>
            <if test="subdivisionCategoryName != null">subdivision_category_name,
            </if>
            <if test="normDepartmentCompareId != null">norm_department_compare_id,
            </if>
            <if test="normDepartmentCompareName != null">norm_department_compare_name,
            </if>
            <if test="prepareBedsCount != null">prepare_beds_count,
            </if>
            <if test="departmentPhone != null">department_phone,
            </if>
            <if test="departmentMail != null">department_mail,
            </if>
            <if test="establishDate != null">establish_date,
            </if>
            <if test="revokeDate != null">revoke_date,
            </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="parentDepartmentId != null">#{parentDepartmentId},
            </if>
            <if test="agencyId != null">#{agencyId},
            </if>
            <if test="agencyName != null">#{agencyName},
            </if>
            <if test="departmentName != null">#{departmentName},
            </if>
            <if test="departmentCode != null">#{departmentCode},
            </if>
            <if test="departmentType != null">#{departmentType},
            </if>
            <if test="departmentAbbreviation != null">#{departmentAbbreviation},
            </if>
            <if test="departmentPersonId != null">#{departmentPersonId},
            </if>
            <if test="departmentPersonName != null">#{departmentPersonName},
            </if>
            <if test="nodeType != null">#{nodeType},
            </if>
            <if test="provideServiceCategory != null">#{provideServiceCategory},
            </if>
            <if test="subdivisionCategoryId != null">#{subdivisionCategoryId},
            </if>
            <if test="subdivisionCategoryName != null">#{subdivisionCategoryName},
            </if>
            <if test="normDepartmentCompareId != null">#{normDepartmentCompareId},
            </if>
            <if test="normDepartmentCompareName != null">#{normDepartmentCompareName},
            </if>
            <if test="prepareBedsCount != null">#{prepareBedsCount},
            </if>
            <if test="departmentPhone != null">#{departmentPhone},
            </if>
            <if test="departmentMail != null">#{departmentMail},
            </if>
            <if test="establishDate != null">#{establishDate},
            </if>
            <if test="revokeDate != null">#{revokeDate},
            </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>

    <insert id="insertDepartmentList">
        insert into department(
        parent_department_id,
        agency_id,
        agency_name,
        department_name,
        department_code,
        department_type,
        department_abbreviation,
        department_person_id,
        department_person_name,
        node_type,
        provide_service_category,
        subdivision_category_id,
        subdivision_category_name,
        norm_department_compare_id,
        norm_department_compare_name,
        prepare_beds_count,
        department_phone,
        department_mail,
        establish_date,
        revoke_date,
        create_by,
        create_time
        ) values
        <foreach item="Department" index="index" collection="list" separator=",">
            (
            #{Department.parentDepartmentId},
            #{Department.agencyId},
            #{Department.agencyName},
            #{Department.departmentName},
            #{Department.departmentCode},
            #{Department.departmentType},
            #{Department.departmentAbbreviation},
            #{Department.departmentPersonId},
            #{Department.departmentPersonName},
            #{Department.nodeType},
            #{Department.provideServiceCategory},
            #{Department.subdivisionCategoryId},
            #{Department.subdivisionCategoryName},
            #{Department.normDepartmentCompareId},
            #{Department.normDepartmentCompareName},
            #{Department.prepareBedsCount},
            #{Department.departmentPhone},
            #{Department.departmentMail},
            #{Department.establishDate},
            #{Department.revokeDate},
            #{Department.createBy},
            #{Department.createTime}
            )
        </foreach>
    </insert>

    <update id="updateDepartment" parameterType="Department">
        update department
        <trim prefix="SET" suffixOverrides=",">
            <if test="parentDepartmentId != null">parent_department_id =
            #{parentDepartmentId},
            </if>
            <if test="agencyId != null">agency_id =
            #{agencyId},
            </if>
            <if test="agencyName != null">agency_name =
            #{agencyName},
            </if>
            <if test="departmentName != null">department_name =
            #{departmentName},
            </if>
            <if test="departmentCode != null">department_code =
            #{departmentCode},
            </if>
            <if test="departmentType != null">department_type =
            #{departmentType},
            </if>
            <if test="departmentAbbreviation != null">department_abbreviation =
            #{departmentAbbreviation},
            </if>
            <if test="departmentPersonId != null">department_person_id =
            #{departmentPersonId},
            </if>
            <if test="departmentPersonName != null">department_person_name =
            #{departmentPersonName},
            </if>
            <if test="nodeType != null">node_type =
            #{nodeType},
            </if>
            <if test="provideServiceCategory != null">provide_service_category =
            #{provideServiceCategory},
            </if>
            <if test="subdivisionCategoryId != null">subdivision_category_id =
            #{subdivisionCategoryId},
            </if>
            <if test="subdivisionCategoryName != null">subdivision_category_name =
            #{subdivisionCategoryName},
            </if>
            <if test="normDepartmentCompareId != null">norm_department_compare_id =
            #{normDepartmentCompareId},
            </if>
            <if test="normDepartmentCompareName != null">norm_department_compare_name =
            #{normDepartmentCompareName},
            </if>
            <if test="prepareBedsCount != null">prepare_beds_count =
            #{prepareBedsCount},
            </if>
            <if test="departmentPhone != null">department_phone =
            #{departmentPhone},
            </if>
            <if test="departmentMail != null">department_mail =
            #{departmentMail},
            </if>
            <if test="establishDate != null">establish_date =
            #{establishDate},
            </if>
            <if test="revokeDate != null">revoke_date =
            #{revokeDate},
            </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>

    <update id="updateDepartmentById" parameterType="Department">
        update department
        <trim prefix="SET" suffixOverrides=",">
            parent_department_id = #{parentDepartmentId},
            agency_id = #{agencyId},
            agency_name = #{agencyName},
            <if test="departmentName != null">department_name = #{departmentName},</if>
            department_code =#{departmentCode},
            department_type =#{departmentType},
            department_abbreviation =#{departmentAbbreviation},
            department_person_id =#{departmentPersonId},
            department_person_name =#{departmentPersonName},
            node_type =#{nodeType},
            provide_service_category =#{provideServiceCategory},
            subdivision_category_id =#{subdivisionCategoryId},
            subdivision_category_name =#{subdivisionCategoryName},
            norm_department_compare_id =#{normDepartmentCompareId},
            norm_department_compare_name =#{normDepartmentCompareName},
            prepare_beds_count =#{prepareBedsCount},
            department_phone = #{departmentPhone},
            department_mail = #{departmentMail},
            establish_date = #{establishDate},
            revoke_date =#{revokeDate},
            <if test="updateBy != null">update_by =
                #{updateBy},
            </if>
            <if test="updateTime != null">update_time =
                #{updateTime},
            </if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteDepartmentById" parameterType="Long">
        delete
        from department
        where id = #{id}
    </delete>

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

    <select id="selectDepartmentByParentId" resultType="com.xinelu.manage.domain.department.Department">
        <include refid="selectDepartmentVo"/>
        where parent_department_id =
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
</mapper>