<?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.servicepackage.ServicePackageMapper">

    <resultMap type="ServicePackage" id="ServicePackageResult">
        <result property="id" column="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="packageName" column="package_name"/>
        <result property="packageIntroduction" column="package_introduction"/>
        <result property="packageVersion" column="package_version"/>
        <result property="packageTerm" column="package_term"/>
        <result property="packageTermUnit" column="package_term_unit"/>
        <result property="packagePrice" column="package_price"/>
        <result property="packageRemark" column="package_remark"/>
        <result property="hardwareType" column="hardware_type"/>
        <result property="whetherRelease" column="whether_release"/>
        <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="selectServicePackageVo">
        select id,
               department_id,
               department_name,
               disease_type_id,
               disease_type_name,
               package_name,
               package_introduction,
               package_version,
               package_term,
               package_term_unit,
               package_price,
               package_remark,
               hardware_type,
               whether_release,
               create_by,
               create_time,
               update_by,
               update_time
        from service_package
    </sql>

    <resultMap id="ServicePackageVOMap" type="com.xinelu.manage.vo.servicepackage.ServicePackageDetailVO">
        <id property="id" column="id"/>
        <result column="disease_type_id" property="diseaseTypeId"/>
        <result column="disease_type_name" property="diseaseTypeName"/>
        <result column="package_name" property="packageName"/>
        <result column="package_term" property="packageTerm"/>
        <result column="package_term_unit" property="packageTermUnit"/>
        <result column="package_price" property="packagePrice"/>
        <result column="hardware_type" property="hardwareType"/>
        <collection property="voList" ofType="com.xinelu.manage.vo.servicepackagecontent.ServicePackageContentVO">
            <id column="spc1Id" property="id"/>
            <result column="service_way_name" property="serviceWayName"/>
            <result column="spc2Id" property="serviceContentId"/>
            <result column="service_content" property="serviceContent"/>
            <result column="spc3Id" property="serviceFrequencyId"/>
            <result column="service_frequency_text" property="serviceFrequencyText"/>
            <result column="service_frequency_start" property="serviceFrequencyStart"/>
            <result column="service_frequency_end" property="serviceFrequencyEnd"/>
        </collection>
    </resultMap>

    <select id="selectServicePackageList" parameterType="com.xinelu.manage.mapper.servicepackage.ServicePackageMapper"
            resultMap="ServicePackageResult">
        <include refid="selectServicePackageVo"/>
        <where>
            <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="packageName != null  and packageName != ''">
                and package_name like concat('%',
                #{packageName},
                '%'
                )
            </if>
            <if test="packageIntroduction != null  and packageIntroduction != ''">
                and package_introduction =
                #{packageIntroduction}
            </if>
            <if test="packageVersion != null  and packageVersion != ''">
                and package_version =
                #{packageVersion}
            </if>
            <if test="packageTerm != null ">
                and package_term =
                #{packageTerm}
            </if>
            <if test="packageTermUnit != null  and packageTermUnit != ''">
                and package_term_unit =
                #{packageTermUnit}
            </if>
            <if test="packagePrice != null ">
                and package_price =
                #{packagePrice}
            </if>
            <if test="packageRemark != null  and packageRemark != ''">
                and package_remark =
                #{packageRemark}
            </if>
            <if test="hardwareType != null  and hardwareType != ''">
                and hardware_type =
                #{hardwareType}
            </if>
            <if test="whetherRelease != null ">
                and whether_release =
                #{whetherRelease}
            </if>
        </where>
    </select>

    <select id="selectServicePackageById" parameterType="Long"
            resultMap="ServicePackageResult">
        <include refid="selectServicePackageVo"/>
        where id = #{id}
    </select>

    <select id="selectServicePackagesById"
            resultType="com.xinelu.manage.vo.servicepackage.ServicePackageDetailVO">
        select sp.id,
               sp.department_id,
               sp.department_name,
               sp.disease_type_id,
               sp.disease_type_name,
               sp.package_name,
               sp.package_introduction,
               sp.package_version,
               sp.package_term,
               sp.package_term_unit,
               sp.package_price,
               sp.package_remark,
               sp.hardware_type
        from service_package sp
        where sp.id = #{id}
    </select>


    <select id="selectServicePackageLists" resultType="com.xinelu.manage.vo.servicepackage.ServicePackageVO"
            parameterType="com.xinelu.manage.domain.servicepackage.ServicePackage">
        select id,
        disease_type_id,
        disease_type_name,
        package_name,
        package_introduction,
        package_version,
        package_term,
        package_term_unit,
        package_price,
        package_remark,
        whether_release
        from service_package
        <where>
            <if test="departmentId != null ">
                and department_id =
                #{departmentId}
            </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="packageName != null  and packageName != ''">
                and package_name like concat('%',
                #{packageName},
                '%'
                )
            </if>
            <if test="packageVersion != null  and packageVersion != ''">
                and package_version like concat('%',
                #{packageVersion},
                '%'
                )
            </if>
            <if test="packagePrice != null ">
                and package_price =
                #{packagePrice}
            </if>
            <if test="whetherRelease != null ">
                and whether_release =
                #{whetherRelease}
            </if>
        </where>
        order by create_time DESC
    </select>
    <select id="selectServicePackageVoListById"
            resultType="com.xinelu.manage.vo.servicepackagecontent.ServicePackageContentVO">
        select spc1.id,
               spc1.service_way_name,
               spc2.id AS serviceContentId,
               spc2.service_content,
               spc3.id AS serviceFrequencyId,
               spc3.service_frequency_text,
               spc3.service_frequency_start,
               spc3.service_frequency_end
        from service_package_content spc1
                 join service_package_content spc2
                      on spc1.id = spc2.service_way_id
                 join service_package_content spc3
                      on spc2.id = spc3.service_content_id
        where spc1.service_package_id = #{id}
    </select>

    <select id="existNameCount" resultType="java.lang.Integer">
        select count(*)
        from service_package
        where department_id = #{departmentId}
          and package_name = #{packageName}
    </select>

    <select id="countByPackageNameExcludingId" resultType="java.lang.Integer">
        select count(1)
        from service_package
        where department_id = #{departmentId}
          and package_name = #{packageName}
          and id != #{id}
    </select>

    <insert id="insertServicePackage" parameterType="ServicePackage" useGeneratedKeys="true"
            keyProperty="id">
        insert into service_package
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <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="packageName != null">package_name,
            </if>
            <if test="packageIntroduction != null">package_introduction,
            </if>
            <if test="packageVersion != null">package_version,
            </if>
            <if test="packageTerm != null">package_term,
            </if>
            <if test="packageTermUnit != null">package_term_unit,
            </if>
            <if test="packagePrice != null">package_price,
            </if>
            <if test="packageRemark != null">package_remark,
            </if>
            <if test="hardwareType != null">hardware_type,
            </if>
            <if test="whetherRelease != null">whether_release,
            </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="departmentId != null">#{departmentId},
            </if>
            <if test="departmentName != null">#{departmentName},
            </if>
            <if test="diseaseTypeId != null">#{diseaseTypeId},
            </if>
            <if test="diseaseTypeName != null">#{diseaseTypeName},
            </if>
            <if test="packageName != null">#{packageName},
            </if>
            <if test="packageIntroduction != null">#{packageIntroduction},
            </if>
            <if test="packageVersion != null">#{packageVersion},
            </if>
            <if test="packageTerm != null">#{packageTerm},
            </if>
            <if test="packageTermUnit != null">#{packageTermUnit},
            </if>
            <if test="packagePrice != null">#{packagePrice},
            </if>
            <if test="packageRemark != null">#{packageRemark},
            </if>
            <if test="hardwareType != null">#{hardwareType},
            </if>
            <if test="whetherRelease != null">#{whetherRelease},
            </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="updateServicePackage" parameterType="ServicePackage">
        update service_package
        <trim prefix="SET" suffixOverrides=",">
            <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="packageName != null">package_name =
            #{packageName},
            </if>
            <if test="packageIntroduction != null">package_introduction =
            #{packageIntroduction},
            </if>
            <if test="packageVersion != null">package_version =
            #{packageVersion},
            </if>
            <if test="packageTerm != null">package_term =
            #{packageTerm},
            </if>
            <if test="packageTermUnit != null">package_term_unit =
            #{packageTermUnit},
            </if>
            <if test="packagePrice != null">package_price =
            #{packagePrice},
            </if>
            <if test="packageRemark != null">package_remark =
            #{packageRemark},
            </if>
            <if test="hardwareType != null">hardware_type =
            #{hardwareType},
            </if>
            <if test="whetherRelease != null">whether_release =
            #{whetherRelease},
            </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="editReleaseStatus">
        update service_package
        set whether_release =#{whetherRelease}
        where id = #{id}
    </update>

    <delete id="deleteServicePackageById" parameterType="Long">
        delete
        from service_package
        where id = #{id}
    </delete>

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