<?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.dgtis.dorm.manager.db.dao.ZhllConsultingComplaintsMapper">
    
    <resultMap type="com.dgtis.dorm.manager.db.domain.ZhllConsultingComplaints" id="ZhllConsultingComplaintsResult">
        <result property="id"    column="id"    />
        <result property="type"    column="type"    />
        <result property="userId"    column="user_id"    />
        <result property="deptUnid"    column="dept_unid"    />
        <result property="serviceUnid"    column="service_unid"    />
        <result property="title"    column="title"    />
        <result property="content"    column="content"    />
        <result property="answer"    column="answer"    />
        <result property="isAnswer"    column="is_answer"    />
        <result property="addTime"    column="add_time"    />
        <result property="addUser"    column="add_user"    />
        <result property="modifyTime"    column="modify_time"    />
        <result property="modifyUser"    column="modify_user"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="consultantName"    column="consultant_name"    />
        <result property="consultantPhone"    column="consultant_phone"    />
        <result property="serviceName"    column="SERVICE_NAME"    />
        <result property="deptName"    column="deptName"    />
    </resultMap>
	
	<sql id="selectZhllConsultingComplaintsVo">
        select
        z.id,
        z.type,
        z.user_id,
        z.dept_unid,
        z.service_unid,
        z.title,
        z.content,
        z.answer,
        z.is_answer,
        z.consultant_name,
        z.consultant_phone,
        DATE_FORMAT(z.add_time,'%Y-%m-%d %H:%i') add_time,
        z.add_user,
        DATE_FORMAT(z.modify_time,'%Y-%m-%d %H:%i') modify_time,
        z.modify_user,
        z.del_flag,
        p.SERVICE_NAME,
        d.deptName
        from zhll_consulting_complaints z
        left JOIN pre_service p on p.unid = z.service_unid
        left JOIN department_info d on d.DEPT_UNID = z.dept_unid
    </sql>
	
    <select id="selectZhllConsultingComplaintsList" parameterType="com.dgtis.dorm.manager.db.domain.ZhllConsultingComplaints" resultMap="ZhllConsultingComplaintsResult">
        <include refid="selectZhllConsultingComplaintsVo"/>
        <where>
            z.del_flag = '0'
            <if test="id != null  and id != '' "> and z.id = #{id}</if>
             <if test="type != null  and type != '' "> and z.type = #{type}</if>
             <if test="userId != null  and userId != '' "> and z.user_id = #{userId}</if>
             <if test="deptUnid != null  and deptUnid != '' "> and z.dept_unid = #{deptUnid}</if>
             <if test="serviceUnid != null  and serviceUnid != '' "> and z.service_unid = #{serviceUnid}</if>
             <if test="title != null  and title != '' "> and z.title = #{title}</if>
             <if test="content != null  and content != '' "> and z.content = #{content}</if>
             <if test="answer != null  and answer != '' "> and z.answer = #{answer}</if>
             <if test="isAnswer != null  and isAnswer != '' "> and z.is_answer = #{isAnswer}</if>
             <if test="addTime != null "> and z.add_time = #{addTime}</if>
             <if test="addUser != null  and addUser != '' "> and z.add_user = #{addUser}</if>
             <if test="modifyTime != null "> and z.modify_time = #{modifyTime}</if>
             <if test="modifyUser != null  and modifyUser != '' "> and z.modify_user = #{modifyUser}</if>
            <if test="consultantName != null "> and z.consultant_name = #{consultantName}</if>
            <if test="consultantPhone != null  and consultantPhone != '' "> and z.consultant_phone = #{consultantPhone}</if>
         </where>
        ORDER BY modify_time desc
    </select>
    
    <select id="selectZhllConsultingComplaintsById" parameterType="String" resultMap="ZhllConsultingComplaintsResult">
        <include refid="selectZhllConsultingComplaintsVo"/>
        where z.del_flag = '0' and z.id = #{id}
    </select>
        
    <insert id="insertZhllConsultingComplaints" parameterType="com.dgtis.dorm.manager.db.domain.ZhllConsultingComplaints">
        insert into zhll_consulting_complaints
		<trim prefix="(" suffix=")" suffixOverrides=",">
            add_time,
            modify_time,
            del_flag,
			<if test="id != null  and id != ''  ">id,</if>
			<if test="type != null  and type != ''  ">type,</if>
			<if test="userId != null  and userId != ''  ">user_id,</if>
			<if test="deptUnid != null  and deptUnid != ''  ">dept_unid,</if>
			<if test="serviceUnid != null  and serviceUnid != ''  ">service_unid,</if>
			<if test="title != null  and title != ''  ">title,</if>
			<if test="content != null  and content != ''  ">content,</if>
			<if test="answer != null  and answer != ''  ">answer,</if>
			<if test="isAnswer != null  and isAnswer != ''  ">is_answer,</if>
			<if test="addUser != null  and addUser != ''  ">add_user,</if>
			<if test="modifyUser != null  and modifyUser != ''  ">modify_user,</if>
            <if test="consultantName != null  and consultantName != ''  ">consultant_name,</if>
            <if test="consultantPhone != null  and consultantPhone != ''  ">consultant_phone,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            now(),
            now(),
            0,
			<if test="id != null  and id != ''  ">#{id},</if>
			<if test="type != null  and type != ''  ">#{type},</if>
			<if test="userId != null  and userId != ''  ">#{userId},</if>
			<if test="deptUnid != null  and deptUnid != ''  ">#{deptUnid},</if>
			<if test="serviceUnid != null  and serviceUnid != ''  ">#{serviceUnid},</if>
			<if test="title != null  and title != ''  ">#{title},</if>
			<if test="content != null  and content != ''  ">#{content},</if>
			<if test="answer != null  and answer != ''  ">#{answer},</if>
			<if test="isAnswer != null  and isAnswer != ''  ">#{isAnswer},</if>
			<if test="addUser != null  and addUser != ''  ">#{addUser},</if>
			<if test="modifyUser != null  and modifyUser != ''  ">#{modifyUser},</if>
            <if test="consultantName != null  and consultantName != ''  ">#{consultantName},</if>
            <if test="consultantPhone != null  and consultantPhone != ''  ">#{consultantPhone},</if>
         </trim>
    </insert>
	 
    <update id="updateZhllConsultingComplaints" parameterType="com.dgtis.dorm.manager.db.domain.ZhllConsultingComplaints">
        update zhll_consulting_complaints
        <trim prefix="SET" suffixOverrides=",">
            modify_time = now(),
            <if test="type != null  and type != ''  ">type = #{type},</if>
            <if test="userId != null  and userId != ''  ">user_id = #{userId},</if>
            <if test="deptUnid != null  and deptUnid != ''  ">dept_unid = #{deptUnid},</if>
            <if test="serviceUnid != null  and serviceUnid != ''  ">service_unid = #{serviceUnid},</if>
            <if test="title != null  and title != ''  ">title = #{title},</if>
            <if test="content != null  and content != ''  ">content = #{content},</if>
            <if test="answer != null  and answer != ''  ">answer = #{answer},</if>
            <if test="isAnswer != null  and isAnswer != ''  ">is_answer = #{isAnswer},</if>
            <if test="modifyUser != null  and modifyUser != ''  ">modify_user = #{modifyUser},</if>
            <if test="delFlag != null  and delFlag != ''  ">del_flag = #{delFlag},</if>
            <if test="consultantName != null "> consultant_name = #{consultantName},</if>
            <if test="consultantPhone != null "> consultant_phone = #{consultantPhone},</if>
        </trim>
        where id = #{id}
    </update>
    <select id="queryList" parameterType="com.dgtis.dorm.manager.db.domain.ZhllConsultingComplaints" resultMap="ZhllConsultingComplaintsResult">
        <include refid="selectZhllConsultingComplaintsVo"/>
        <where>
            z.del_flag = '0'
            <if test="id != null  and id != '' "> and z.id = #{id}</if>
            <if test="type != null  and type != '' "> and z.type = #{type}</if>
            <if test="userId != null  and userId != '' "> and z.user_id = #{userId}</if>
            <if test="deptUnid != null  and deptUnid != '' "> and z.dept_unid = #{deptUnid}</if>
            <if test="serviceUnid != null  and serviceUnid != '' "> and z.service_unid = #{serviceUnid}</if>
            <if test="content != null  and content != '' "> and (z.content like CONCAT('%',#{content},'%') or z.title like concat('%',#{content},'%')
                or z.answer like concat('%',#{content},'%') or p.SERVICE_NAME  like concat('%',#{content},'%')) </if>
            <if test="isAnswer != null  and isAnswer != '' "> and z.is_answer = #{isAnswer}</if>
            <if test="consultantName != null "> and z.consultant_name like concat('%',#{consultantName},'%')</if>
            <if test="consultantPhone != null  and consultantPhone != '' "> and z.consultant_phone = #{consultantPhone}</if>
        </where>
        ORDER BY add_time desc
    </select>
    <select id="queryByTimeListPage" parameterType="map" resultMap="ZhllConsultingComplaintsResult">
        <include refid="selectZhllConsultingComplaintsVo"/>
        <where>
            z.del_flag = '0'
            <if test="zhllCC.id != null  and zhllCC.id != '' "> and z.id = #{zhllCC.id}</if>
            <if test="zhllCC.type != null  and zhllCC.type != '' "> and z.type = #{zhllCC.type}</if>
            <if test="zhllCC.userId != null  and zhllCC.userId != '' "> and z.user_id = #{zhllCC.userId}</if>
            <if test="zhllCC.deptUnid != null  and zhllCC.deptUnid != '' "> and z.dept_unid = #{zhllCC.deptUnid}</if>
            <if test="zhllCC.serviceUnid != null  and zhllCC.serviceUnid != '' "> and z.service_unid = #{zhllCC.serviceUnid}</if>
            <if test="zhllCC.content != null  and zhllCC.content != '' "> and (z.content like CONCAT('%',#{zhllCC.content},'%') or z.title like concat('%',#{zhllCC.content},'%')
                or z.answer like concat('%',#{content},'%') or p.SERVICE_NAME  like concat('%',#{content},'%')) </if>
            <if test="zhllCC.isAnswer != null  and zhllCC.isAnswer != '' "> and z.is_answer = #{zhllCC.isAnswer}</if>
            <if test="zhllCC.consultantName != null "> and z.consultant_name like concat('%',#{zhllCC.consultantName},'%')</if>
            <if test="zhllCC.consultantPhone != null  and zhllCC.consultantPhone != '' "> and z.consultant_phone = #{zhllCC.consultantPhone}</if>
            <if test="startDate!=null">
                and z.add_time &gt;= Date(#{startDate})
            </if>
            <if test="endDate!=null">
                and z.add_time &lt;= Date(#{endDate})
            </if>
        </where>
        ORDER BY add_time desc
    </select>
    
</mapper>