<?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.cftech.showroom.appform.dao.AppFormMapper">

    <resultMap id="resultMap" type="com.cftech.showroom.appform.model.AppForm">
        <id column="id" property="id"/>
        <result column="number" property="number"/>
        <result column="name" property="name"/>
        <result column="department" property="department"/>
        <result column="reservation_date" property="reservationDate"/>
        <result column="reservation_begin_time" property="reservationBeginTime"/>
        <result column="reservation_end_time" property="reservationEndTime"/>
        <result column="visitor_category" property="visitorCategory"/>
        <result column="visitor_num" property="visitorNum"/>
        <result column="apply_content" property="applyContent"/>
        <result column="explainer" property="explainer"/>
        <result column="accounts_id" property="accountsId"/>
        <result column="del_flag" property="delFlag"/>
        <result column="status" property="status"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <result column="description" property="description"/>
        <result column="create_by" property="createBy"/>
        <result column="update_by" property="updateBy"/>
        <result column="email" property="email"/>
        <result column="openid" property="openid"/>
        <result column="ccmail" property="ccmail"/>
        <result column="tomail" property="tomail"/>
        <result column="explainer_status" property="explainerStatus"/>
        <result column="no" property="no"/>
        <result column="cancel_time" property="cancelTime"/>
        <result column="dname" property="dname"/>
        <result column="vname" property="vname"/>
        <result column="ename" property="ename"/>
        <result column="remarks" property="remarks"/>
    </resultMap>

    <sql id="sqlWhere">
        <if test="conds!=null">
            <trim prefix="WHERE" prefixOverrides="AND|OR">
                <foreach collection="conds.conds" index="index" item="cond">
                    ${cond.linkType}
                    <if test="cond.condType == 'EQUAL'">${cond.param} = #{cond.value}</if>
                    <if test="cond.condType == 'NOTEQUAL'">${cond.param} &lt;&gt; #{cond.value}</if>
                    <if test="cond.condType == 'GREATEQUAL'">${cond.param} &gt;= #{cond.value}</if>
                    <if test="cond.condType == 'GREATTHAN'">${cond.param} &gt; #{cond.value}</if>
                    <if test="cond.condType == 'LESSEQUAL'">${cond.param} &lt;= #{cond.value}</if>
                    <if test="cond.condType == 'LESSTHAN'">${cond.param} &lt; #{cond.value}</if>
                    <if test="cond.condType == 'BETWEEN'">${cond.param} BETWEEN #{cond.startValue} AND
                        #{cond.endValue}
                    </if>
                    <if test="cond.condType == 'ISNULL'">${cond.param} IS NULL</if>
                    <if test="cond.condType == 'NOTNULL'">${cond.param} IS NOT NULL</if>
                    <if test="cond.condType == 'LIKE'">${cond.param} LIKE #{cond.value}</if>
                    <if test="cond.condType == 'IN'">${cond.param} IN
                        <foreach item="item" index="index" collection="cond.value" open="(" separator="," close=")">
                            #{item}
                        </foreach>
                    </if>
                </foreach>
            </trim>
        </if>
    </sql>

    <sql id="sqlColumns">
                                    id,
                                                number,
                                                name,
                                                department,
                                                reservation_date,
                                                reservation_begin_time,
                                                reservation_end_time,
                                                visitor_category,
                                                visitor_num,
                                                apply_content,
                                                explainer,
                                                accounts_id,
                                                del_flag,
                                                status,
                                                create_time,
                                                update_time,
                                                description,
                                                create_by,
                                                update_by,email,openid,ccmail,tomail,explainer_status,no,cancel_time,dname,vname,ename,remarks
                        </sql>


    <insert id="save" parameterType="com.cftech.showroom.appform.model.AppForm" useGeneratedKeys="true"
            keyProperty="id">
        insert into wx_mp_showroom_appform
        (
        <include refid="sqlColumns"/>
        )
        values
        (
        #{id, jdbcType=BIGINT},
        #{number, jdbcType=VARCHAR},
        #{name, jdbcType=VARCHAR},
        #{department, jdbcType=BIGINT},
        #{reservationDate, jdbcType=TIMESTAMP},
        #{reservationBeginTime, jdbcType=TIMESTAMP},
        #{reservationEndTime, jdbcType=TIMESTAMP},
        #{visitorCategory, jdbcType=BIGINT},
        #{visitorNum, jdbcType=INTEGER},
        #{applyContent, jdbcType=CLOB},
        #{explainer, jdbcType=BIGINT},
        #{accountsId, jdbcType=BIGINT},
        #{delFlag, jdbcType=TINYINT},
        #{status, jdbcType=VARCHAR},
        now(),
        now(),
        #{description, jdbcType=VARCHAR},
        #{createBy, jdbcType=BIGINT},
        #{updateBy, jdbcType=BIGINT},
        #{email, jdbcType=VARCHAR},
        #{openid, jdbcType=VARCHAR},
        #{ccmail, jdbcType=VARCHAR},
        #{tomail, jdbcType=VARCHAR},
        #{explainerStatus, jdbcType=VARCHAR},
        #{no, jdbcType=VARCHAR},
        #{cancelTime, jdbcType=TIMESTAMP},
        #{dname, jdbcType=VARCHAR},
        #{vname, jdbcType=VARCHAR},
        #{ename, jdbcType=VARCHAR},
        #{remarks,jdbcType=VARCHAR}
        )
    </insert>

    <select id="fetchById" parameterType="java.lang.Long" resultMap="resultMap">
        SELECT
        t.*,se.name explainerName,sd.name departmentName,sv.name visitorCategoryName
        FROM wx_mp_showroom_appform t
        left join wx_mp_showroom_explainer se on t.explainer = se.id
        left join wx_mp_showroom_department sd on t.department = sd.id
        left join wx_mp_showroom_vicategory sv on t.visitor_category = sv.id
        WHERE t.id=#{id}
    </select>

    <select id="count" parameterType="java.util.Map" resultType="java.lang.Integer">
        SELECT COUNT(1) FROM wx_mp_showroom_appform t
        <include refid="sqlWhere"/>
        <if test="beginTime!=null or endTime!=null">
            and (t.reservation_begin_time &gt;= #{beginTime}
            and t.reservation_begin_time &lt;= #{endTime})
            OR (t.reservation_end_time &gt;= #{beginTime}
            and t.reservation_end_time &lt;= #{endTime})
        </if>
    </select>
    <select id="fetchSearchByPageMobile" parameterType="java.util.Map" resultMap="resultMap">
        SELECT
        t.*
        FROM wx_mp_showroom_appform t
        <include refid="sqlWhere"/>
    </select>

    <select id="fetchSearchByPageForList" parameterType="java.util.Map" resultType="com.cftech.showroom.appform.model.AppFormVo">
        SELECT
        t.*, se.id as evaluateId, se.brand as brand, se.media_device as mediaDevice, se.explain_major as explainMajor, se.clothing as clothing, se.proposal as proposal
        FROM wx_mp_showroom_appform t
        left join wx_mp_showroom_evaluate se  on t.id = se.showroom_id
        <include refid="sqlWhere"/>
        <if test="beginTime!=null or endTime!=null">
            and (t.reservation_begin_time &gt;= #{beginTime}
            and t.reservation_begin_time &lt;= #{endTime})
            OR (t.reservation_end_time &gt;= #{beginTime}
            and t.reservation_end_time &lt;= #{endTime})
        </if>
        <if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
        <if test="limit>0">limit #{offset},#{limit}</if>
    </select>

    <select id="fetchSearchByPage" parameterType="java.util.Map" resultMap="resultMap">
        SELECT
        t.*
        FROM wx_mp_showroom_appform t
        <include refid="sqlWhere"/>
        <if test="beginTime!=null or endTime!=null">
            and (t.reservation_begin_time &gt;= #{beginTime}
            and t.reservation_begin_time &lt;= #{endTime})
            OR (t.reservation_end_time &gt;= #{beginTime}
            and t.reservation_end_time &lt;= #{endTime})
        </if>
        <if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
        <if test="limit>0">limit #{offset},#{limit}</if>
    </select>


    <select id="isAppointment" parameterType="java.util.Map" resultType="java.lang.Integer">
        SELECT
        count(1)
        FROM
        wx_mp_showroom_appform t
        WHERE
        /*选择的开始时间在已有数据的结束时间后面，或者选择的结束时间已有数据的开始时间前面,为不冲突时间。反之为冲突*/
        NOT (
        (
        t.reservation_end_time &lt; #{beginTime}
        OR (t.reservation_begin_time &gt; #{endTime})
        )
        )
        and t.del_flag = 0 and t.status = 0;
    </select>


    <select id="getCalendar" parameterType="java.util.Map" resultType="com.cftech.showroom.appform.model.AppFormCalendarDto">
        select name title,reservation_begin_time start,reservation_end_time end
        from wx_mp_showroom_appform
        <include refid="sqlWhere"/>
    </select>

    <update id="update" parameterType="com.cftech.showroom.appform.model.AppForm">
        update wx_mp_showroom_appform
        <set>
            <if test="id != null">
                id = #{id, jdbcType=BIGINT},
            </if>
            <if test="number != null">
                number = #{number, jdbcType=VARCHAR},
            </if>
            <if test="name != null">
                name = #{name, jdbcType=VARCHAR},
            </if>
            <if test="department != null">
                department = #{department, jdbcType=BIGINT},
            </if>
            <if test="reservationDate != null">
                reservation_date = #{reservationDate, jdbcType=TIMESTAMP},
            </if>
            <if test="reservationBeginTime != null">
                reservation_begin_time = #{reservationBeginTime, jdbcType=TIMESTAMP},
            </if>
            <if test="reservationEndTime != null">
                reservation_end_time = #{reservationEndTime, jdbcType=TIMESTAMP},
            </if>
            <if test="visitorCategory != null">
                visitor_category = #{visitorCategory, jdbcType=BIGINT},
            </if>
            <if test="visitorNum != null">
                visitor_num = #{visitorNum, jdbcType=INTEGER},
            </if>
            <if test="applyContent != null">
                apply_content = #{applyContent, jdbcType=CLOB},
            </if>
            <if test="explainer != null">
                explainer = #{explainer, jdbcType=BIGINT},
            </if>
            <if test="accountsId != null">
                accounts_id = #{accountsId, jdbcType=BIGINT},
            </if>
            <if test="delFlag != null">
                del_flag = #{delFlag, jdbcType=TINYINT},
            </if>
            <if test="status != null">
                status = #{status, jdbcType=VARCHAR},
            </if>
            <if test="createTime != null">
                create_time = #{createTime, jdbcType=TIMESTAMP},
            </if>
            <if test="description != null">
                description = #{description, jdbcType=VARCHAR},
            </if>
            <if test="createBy != null">
                create_by = #{createBy, jdbcType=BIGINT},
            </if>
            <if test="updateBy != null">
                update_by = #{updateBy, jdbcType=BIGINT},
            </if>
            <if test="email != null">
                email = #{email, jdbcType=VARCHAR},
            </if>
            <if test="openid != null">
                openid = #{openid, jdbcType=VARCHAR},
            </if>
            <if test="ccmail != null">
                ccmail = #{ccmail, jdbcType=VARCHAR},
            </if>
            <if test="tomail != null">
                tomail = #{tomail, jdbcType=VARCHAR},
            </if>
            <if test="explainerStatus != null">
                explainer_status = #{explainerStatus, jdbcType=VARCHAR},
            </if>
            <if test="no != null">
                no = #{no, jdbcType=VARCHAR},
            </if>
            <if test="cancelTime != null">
                cancel_time = #{cancelTime, jdbcType=TIMESTAMP},
            </if>
            <if test="dname != null">
                dname = #{dname, jdbcType=VARCHAR},
            </if>
            <if test="vname != null">
                vname = #{vname, jdbcType=VARCHAR},
            </if>
            <if test="ename != null">
                ename = #{ename, jdbcType=VARCHAR},
            </if>
            <if test="remarks !=null">
                remarks = #{remarks,jdbcType=VARCHAR},
            </if>
        </set>
        where id=#{id,jdbcType=BIGINT}
    </update>

    <update id="delete" parameterType="java.lang.Long">
        update wx_mp_showroom_appform set del_flag=1 where id=#{id,jdbcType=BIGINT}
    </update>


    <select id="fetchGetName" parameterType="java.util.Map" resultType="java.lang.String">
        select ${columnName} from wx_mp_showroom_appform
       where del_flag = 0 and status =0
        group by ${columnName}
        UNION
        select name ${columnName} from ${tableName} where del_flag = 0
    </select>

    <select id="getRecordList" parameterType="java.util.Map" resultType="com.cftech.showroom.appform.model.ShowRoomRecord">
       SELECT
        t.visitor_num personNum,t.create_time createDate,t.reservation_begin_time reservationDate,t.ename personName,t.dname dept,t.vname type
        FROM wx_mp_showroom_appform t
        <include refid="sqlWhere"/>
    </select>
</mapper>