<?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.servicewaycontent.ServiceWayContentMapper">

    <resultMap type="ServiceWayContent" id="ServiceWayContentResult">
        <result property="id" column="id"/>
        <result property="serviceWayName" column="service_way_name"/>
        <result property="serviceWayCode" column="service_way_code"/>
        <result property="serviceType" column="service_type"/>
        <result property="serviceWayId" column="service_way_id"/>
        <result property="serviceContent" column="service_content"/>
        <result property="serviceContentId" column="service_content_id"/>
        <result property="serviceFrequencyType" column="service_frequency_type"/>
        <result property="serviceFrequencyText" column="service_frequency_text"/>
        <result property="serviceFrequencyStart" column="service_frequency_start"/>
        <result property="serviceFrequencyEnd" column="service_frequency_end"/>
        <result property="serviceSort" column="service_sort"/>
        <result property="serviceRemark" column="service_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 id="ServiceContentAndFrequencyVOMap" type="com.xinelu.manage.vo.servicewaycontent.ServiceWayContentVO">
        <id property="id" column="id"/>
        <result column="service_content" property="serviceContent"/>
        <result column="service_way_id" property="serviceWayId"/>
        <collection property="serviceWayFrequencyList"
                    ofType="com.xinelu.manage.vo.servicefrequency.ServiceFrequencyVO">
            <id column="serviceFrequencyId" property="id"/>
            <result column="service_frequency_type" property="serviceFrequencyType"/>
            <result column="service_frequency_text" property="serviceFrequencyText"/>
            <result column="service_frequency_start" property="serviceFrequencyStart"/>
            <result column="service_frequency_end" property="serviceFrequencyEnd"/>
            <result column="serviceFrequencySort" property="serviceFrequencySort"/>
        </collection>
    </resultMap>

    <sql id="selectServiceWayContentVo">
        select id,
               service_way_name,
               service_way_code,
               service_type,
               service_way_id,
               service_content,
               service_content_id,
               service_frequency_type,
               service_frequency_text,
               service_frequency_start,
               service_frequency_end,
               service_sort,
               service_remark,
               create_by,
               create_time,
               update_by,
               update_time
        from service_way_content
    </sql>

    <select id="selectServiceWayContentList" parameterType="ServiceWayContent" resultMap="ServiceWayContentResult">
        <include refid="selectServiceWayContentVo"/>
        <where>
            <if test="serviceWayName != null  and serviceWayName != ''">
                and service_way_name like concat('%',
                #{serviceWayName},
                '%'
                )
            </if>
            <if test="serviceWayCode != null  and serviceWayCode != ''">
                and service_way_code =
                #{serviceWayCode}
            </if>
            <if test="serviceType != null  and serviceType != ''">
                and service_type =
                #{serviceType}
            </if>
            <if test="serviceWayId != null ">
                and service_way_id =
                #{serviceWayId}
            </if>
            <if test="serviceContent != null  and serviceContent != ''">
                and service_content =
                #{serviceContent}
            </if>
            <if test="serviceContentId != null ">
                and service_content_id =
                #{serviceContentId}
            </if>
            <if test="serviceFrequencyType != null  and serviceFrequencyType != ''">
                and service_frequency_type =
                #{serviceFrequencyType}
            </if>
            <if test="serviceFrequencyText != null  and serviceFrequencyText != ''">
                and service_frequency_text =
                #{serviceFrequencyText}
            </if>
            <if test="serviceFrequencyStart != null ">
                and service_frequency_start =
                #{serviceFrequencyStart}
            </if>
            <if test="serviceFrequencyEnd != null ">
                and service_frequency_end =
                #{serviceFrequencyEnd}
            </if>
            <if test="serviceSort != null ">
                and service_sort =
                #{serviceSort}
            </if>
            <if test="serviceRemark != null  and serviceRemark != ''">
                and service_remark =
                #{serviceRemark}
            </if>
        </where>
    </select>

    <select id="selectServiceWayContentById" parameterType="Long"
            resultMap="ServiceWayContentResult">
        <include refid="selectServiceWayContentVo"/>
        where id = #{id}
    </select>
    <select id="selectListNum" resultType="com.xinelu.manage.vo.servicewaycontent.ServiceWayContentAndNumVO">
        SELECT
        swc1.id,
        swc1.service_way_name,
        COUNT(swc3.id) AS serviceContentNum
        FROM
        service_way_content swc1
        LEFT JOIN
        service_way_content swc2 ON swc1.id = swc2.service_way_id AND swc2.service_type = 'SERVICE_CONTENT'
        LEFT JOIN
        service_way_content swc3 ON swc2.id = swc3.service_content_id AND swc3.service_type = 'SERVICE_FREQUENCY'
        <where>
            swc1.service_type = 'SERVICE_WRY'
            <if test="id != null">
                and swc1.id =
                #{id}
            </if>
            <if test="serviceWayName != null  and serviceWayName != ''">
                and swc1.service_way_name like concat('%',
                #{serviceWayName},
                '%'
                )
            </if>
        </where>
        Group By swc1.id
    </select>

    <select id="selectServiceWayContentListV1"
            resultMap="ServiceContentAndFrequencyVOMap">
        SELECT swc1.id,swc1.service_content,
        swc1.service_way_id,
        swc2.id AS serviceFrequencyId,
        swc2.service_frequency_type,
        swc2.service_frequency_text,
        swc2.service_frequency_start,
        swc2.service_frequency_end,
        swc2.service_sort AS 'serviceFrequencySort'
        FROM service_way_content swc1
        JOIN service_way_content swc2 on swc1.id = swc2.service_content_id
        <where>
            swc1.service_type = 'SERVICE_CONTENT'
            and swc2.service_type = 'SERVICE_FREQUENCY'
            <if test="serviceWayId != null ">
                and swc1.service_way_id =
                #{serviceWayId}
            </if>
            <if test="serviceContent != null  and serviceContent != ''">
                and swc1.service_content like concat('%',
                #{serviceContent},
                '%'
                )
            </if>
        </where>
        order by swc1.service_sort,swc1.create_time DESC,swc2.create_time DESC
    </select>

    <select id="selectServiceWayContentDetailByFrequencyId"
            resultType="com.xinelu.manage.vo.servicewaycontent.ServiceWayContentAndFrequencyVO"
            parameterType="java.lang.Long">
        SELECT swc2.id AS serviceContentId,
               swc2.service_content,
               swc2.service_way_id,
               swc1.id AS serviceFrequencyId,
               swc1.service_frequency_type,
               swc1.service_frequency_text,
               swc1.service_frequency_start,
               swc1.service_frequency_end,
               swc1.service_sort AS 'serviceFrequencySort'
        FROM service_way_content swc1
                 LEFT JOIN service_way_content swc2 ON swc1.service_content_id = swc2.id
        WHERE swc1.service_type = 'SERVICE_FREQUENCY'
          AND swc2.service_type = 'SERVICE_CONTENT'
          AND swc1.id = #{id}

    </select>
    <select id="countByServiceName" resultType="java.lang.Integer" parameterType="java.lang.String">
        select count(1)
        from service_way_content swc
        where swc.service_way_name = #{serviceWayName}
          and swc.service_type = 'SERVICE_WRY'
    </select>

    <select id="selectServiceWayById" resultType="java.lang.String" parameterType="java.lang.Long">
        select swc.service_way_name
        from service_way_content swc
        where swc.service_type = 'SERVICE_WRY'
          and swc.id = #{id}
    </select>
    <select id="selectList" resultType="com.xinelu.manage.vo.servicewaycontent.ServiceWayContentAndNumVO">
        select
        from service_way_content swc
        <where>
            swc.service_type = 'SERVICE_WRY'
            <if test="id != null">
                and swc.id =
                #{id}
            </if>
            <if test="serviceWayName != null  and serviceWayName != ''">
                and swc.service_way_name like concat('%',
                #{serviceWayName},
                '%'
                )
            </if>
        </where>
    </select>
    <select id="countByEditDTO" resultType="java.lang.Integer"
            parameterType="com.xinelu.manage.dto.servicewaycontent.ServiceWayContentEditDTO">
        select count(*)
        from service_way_content swc1
        left join
        service_way_content swc2 on swc1.id = swc2.service_way_id
        left join service_way_content swc3 on swc2.id = swc3.service_content_id
        <where>
            swc1.service_type = 'SERVICE_WRY'
            and swc2.service_type = 'SERVICE_CONTENT'
            and swc3.service_type = 'SERVICE_FREQUENCY'
            and swc1.id = #{DTO.id} and swc2.id = #{id}
            <if test="serviceFrequencyType != null  and serviceFrequencyType != ''">
                and service_frequency_type =
                #{serviceFrequencyType}
            </if>
            <if test="serviceFrequencyText != null  and serviceFrequencyText != ''">
                and service_frequency_text =
                #{serviceFrequencyText}
            </if>
            <if test="serviceFrequencyStart != null ">
                and service_frequency_start =
                #{serviceFrequencyStart}
            </if>
            <if test="serviceFrequencyEnd != null ">
                and service_frequency_end =
                #{serviceFrequencyEnd}
            </if>
        </where>
    </select>

    <select id="countByWayIdAndContent" resultType="com.xinelu.manage.vo.servicewaycontent.ServiceWayContentVO">
        select swc.id, swc.service_content
        from service_way_content swc
        where swc.service_type = 'SERVICE_CONTENT'
          and swc.service_way_id = #{serviceWayId}
          and swc.service_content = #{serviceContent}
    </select>

    <select id="countByServiceFrequencyDTO" resultType="java.lang.Integer"
            parameterType="com.xinelu.manage.dto.servicefrequency.ServiceFrequencyDTO">
        select count(*) from service_way_content
        <where>
            service_type = 'SERVICE_FREQUENCY'
            and service_content_id = #{serviceContentId}
            <if test="serviceFrequencyType != null  and serviceFrequencyType != ''">
                and service_frequency_type =
                #{serviceFrequencyType}
            </if>
            <if test="serviceFrequencyText != null  and serviceFrequencyText != ''">
                and service_frequency_text =
                #{serviceFrequencyText}
            </if>
            <if test="serviceFrequencyStart != null ">
                and service_frequency_start =
                #{serviceFrequencyStart}
            </if>
            <if test="serviceFrequencyEnd != null ">
                and service_frequency_end =
                #{serviceFrequencyEnd}
            </if>
        </where>
    </select>

    <select id="selectServiceContentByName" resultType="java.lang.Integer">
        select count(*)
        from service_way_content
        where service_way_id = #{serviceWayId}
          and service_content = #{serviceContent}
    </select>

    <select id="isServiceFrequencyTextDuplicate" resultType="java.lang.Boolean">
        SELECT CASE WHEN COUNT(*) > 0 THEN true ELSE false END
        FROM service_way_content
        WHERE service_content_id = #{serviceWayContentEditDTO.serviceContentId}
          AND service_frequency_text = #{serviceWayContentEditDTO.serviceFrequencyText}
          AND id != #{serviceWayContentEditDTO.id}
    </select>

    <select id="isServiceFrequencyDigitDuplicate" resultType="java.lang.Boolean">
        SELECT CASE WHEN COUNT(*) > 0 THEN true ELSE false END
        FROM service_way_content
        WHERE service_content_id = #{serviceWayContentEditDTO.serviceContentId}
          AND service_frequency_start = #{serviceWayContentEditDTO.serviceFrequencyStart}
          AND service_frequency_end = #{serviceWayContentEditDTO.serviceFrequencyEnd}
          AND id != #{serviceWayContentEditDTO.id}
    </select>


    <insert id="insertServiceWayContent" parameterType="ServiceWayContent" useGeneratedKeys="true"
            keyProperty="id">
        insert into service_way_content
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="serviceWayName != null">service_way_name,
            </if>
            <if test="serviceWayCode != null">service_way_code,
            </if>
            <if test="serviceType != null">service_type,
            </if>
            <if test="serviceWayId != null">service_way_id,
            </if>
            <if test="serviceContent != null">service_content,
            </if>
            <if test="serviceContentId != null">service_content_id,
            </if>
            <if test="serviceFrequencyType != null">service_frequency_type,
            </if>
            <if test="serviceFrequencyText != null">service_frequency_text,
            </if>
            <if test="serviceFrequencyStart != null">service_frequency_start,
            </if>
            <if test="serviceFrequencyEnd != null">service_frequency_end,
            </if>
            <if test="serviceSort != null">service_sort,
            </if>
            <if test="serviceRemark != null">service_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="serviceWayName != null">#{serviceWayName},
            </if>
            <if test="serviceWayCode != null">#{serviceWayCode},
            </if>
            <if test="serviceType != null">#{serviceType},
            </if>
            <if test="serviceWayId != null">#{serviceWayId},
            </if>
            <if test="serviceContent != null">#{serviceContent},
            </if>
            <if test="serviceContentId != null">#{serviceContentId},
            </if>
            <if test="serviceFrequencyType != null">#{serviceFrequencyType},
            </if>
            <if test="serviceFrequencyText != null">#{serviceFrequencyText},
            </if>
            <if test="serviceFrequencyStart != null">#{serviceFrequencyStart},
            </if>
            <if test="serviceFrequencyEnd != null">#{serviceFrequencyEnd},
            </if>
            <if test="serviceSort != null">#{serviceSort},
            </if>
            <if test="serviceRemark != null">#{serviceRemark},
            </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="updateServiceWayContent" parameterType="ServiceWayContent">
        update service_way_content
        <trim prefix="SET" suffixOverrides=",">
            <if test="serviceWayName != null">service_way_name =
            #{serviceWayName},
            </if>
            <if test="serviceWayCode != null">service_way_code =
            #{serviceWayCode},
            </if>
            <if test="serviceType != null">service_type =
            #{serviceType},
            </if>
            <if test="serviceWayId != null">service_way_id =
            #{serviceWayId},
            </if>
            <if test="serviceContent != null">service_content =
            #{serviceContent},
            </if>
            <if test="serviceContentId != null">service_content_id =
            #{serviceContentId},
            </if>
            <if test="serviceFrequencyType != null">service_frequency_type =
            #{serviceFrequencyType},
            </if>
            <if test="serviceFrequencyText != null">service_frequency_text =
            #{serviceFrequencyText},
            </if>
            <if test="serviceFrequencyStart != null">service_frequency_start =
            #{serviceFrequencyStart},
            </if>
            <if test="serviceFrequencyEnd != null">service_frequency_end =
            #{serviceFrequencyEnd},
            </if>
            <if test="serviceSort != null">service_sort =
            #{serviceSort},
            </if>
            <if test="serviceRemark != null">service_remark =
            #{serviceRemark},
            </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="updateServiceWay" parameterType="com.xinelu.manage.domain.servicewaycontent.ServiceWayContent">
        update service_way_content
        <trim prefix="SET" suffixOverrides=",">
            <if test="serviceWayName != null">service_way_name =
            #{serviceWayName},
            </if>
            <if test="serviceWayCode != null">service_way_code =
            #{serviceWayCode},
            </if>
            <if test="serviceType != null">service_type =
            #{serviceType},
            </if>
            <if test="serviceWayId != null">service_way_id =
            #{serviceWayId},
            </if>
            <if test="serviceContent != null">service_content =
            #{serviceContent},
            </if>
            <if test="serviceContentId != null">service_content_id =
            #{serviceContentId},
            </if>
            <if test="serviceFrequencyType != null">service_frequency_type =
            #{serviceFrequencyType},
            </if>
            <if test="serviceFrequencyText != null">service_frequency_text =
            #{serviceFrequencyText},
            </if>
            <if test="serviceFrequencyStart != null">service_frequency_start =
            #{serviceFrequencyStart},
            </if>
            <if test="serviceFrequencyEnd != null">service_frequency_end =
            #{serviceFrequencyEnd},
            </if>
            <if test="serviceSort != null">service_sort =
            #{serviceSort},
            </if>
            <if test="serviceRemark != null">service_remark =
            #{serviceRemark},
            </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} and service_type = 'SERVICE_WRY'
    </update>

    <update id="updateServiceFrequency"
            parameterType="com.xinelu.manage.domain.servicewaycontent.ServiceWayContent">
        update service_way_content
        <trim prefix="SET" suffixOverrides=",">
            <if test="serviceWayName != null">service_way_name =
            #{serviceWayName},
            </if>
            <if test="serviceWayCode != null">service_way_code =
            #{serviceWayCode},
            </if>
            <if test="serviceType != null">service_type =
            #{serviceType},
            </if>
            <if test="serviceWayId != null">service_way_id =
            #{serviceWayId},
            </if>
            <if test="serviceContent != null">service_content =
            #{serviceContent},
            </if>
            <if test="serviceContentId != null">service_content_id =
            #{serviceContentId},
            </if>
            <if test="serviceFrequencyType != null">service_frequency_type =
            #{serviceFrequencyType},
            </if>
            service_frequency_text = #{serviceFrequencyText},
            service_frequency_start = #{serviceFrequencyStart},
            service_frequency_end = #{serviceFrequencyEnd},
            <if test="serviceSort != null">service_sort =
            #{serviceSort},
            </if>
            <if test="serviceRemark != null">service_remark =
            #{serviceRemark},
            </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} and service_type = 'SERVICE_FREQUENCY'
    </update>

    <delete id="deleteServiceWayContentById" parameterType="Long">
        delete
        from service_way_content
        where id = #{id}
    </delete>

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

    <delete id="deleteServiceWayById" parameterType="java.lang.Long">
        delete
        from service_way_content
        where service_type = 'SERVICE_WRY'
          and id = #{id}
    </delete>
    <delete id="deleteByFrequencyId" parameterType="java.lang.Long">
        delete
        from service_way_content
        where id = #{id}
          and service_type = 'SERVICE_FREQUENCY'
    </delete>
</mapper>