<?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.ruoyi.project.invoice.mapper.TInvoiceBookingworkticketMapper">

    <resultMap type="TInvoiceBookingworkticket" id="TInvoiceBookingworkticketResult">
        <result property="id" column="id"/>
        <result property="workUnit" column="work_unit"/>
        <result property="workArea" column="work_area"/>
        <result property="unitNumber" column="unit_number"/>
        <result property="floorLocation" column="floor_location"/>
        <result property="workStartTime" column="work_start_time"/>
        <result property="workEndTime" column="work_end_time"/>
        <result property="delFlag" column="del_flag"/>
        <result property="createrCode" column="creater_code"/>
        <result property="createdate" column="createdate"/>
        <result property="updaterCode" column="updater_code"/>
        <result property="updatedate" column="updatedate"/>
        <result property="contact" column="contact"/>
        <result property="phonenumber" column="phonenumber"/>
        <result property="status" column="status"/>
        <result property="deptId" column="dept_id"/>
        <result property="userMg" column="user_mg"/>
        <result property="deptName" column="dept_name"/>
        <result property="reservationNumber" column="reservation_number"/>
        <result property="filename" column="filename"/>
        <result property="userUnit" column="user_unit"/>
        <!--关联属性的映射关系-->
        <!-- column: 对应的外键。  -->
        <!--  <collection property="tInvoiceWorkcontentList" ofType="TInvoiceWorkcontent" column="id"
                      select="getWorkcontentList"></collection>-->

    </resultMap>

    <resultMap type="TInvoiceBookingworkticketVo" id="TInvoiceBookingworkticketResultVo">
        <result property="workUnit" column="work_unit"/>
        <result property="workArea" column="work_area"/>
        <result property="floorLocation" column="floor_location"/>
        <result property="workStartTime" column="work_start_time"/>
        <result property="workEndTime" column="work_end_time"/>
        <result property="contact" column="contact"/>
        <result property="phonenumber" column="phonenumber"/>
        <result property="workType" column="work_type"/>
        <result property="riskLevel" column="risk_level"/>
        <result property="workDescription" column="work_description"/>
        <result property="workPeopleNumber" column="work_people_number"/>
        <result property="estimateWorktime" column="estimate_worktime"/>
        <result property="status" column="status"/>
        <result property="bookingticketId" column="id"/>
    </resultMap>

    <resultMap type="TInvoiceWorkcontent" id="TInvoiceWorkcontentResult">
        <result property="id" column="id"/>
        <result property="delFlag" column="del_flag"/>
        <result property="createrCode" column="creater_code"/>
        <result property="createdate" column="createdate"/>
        <result property="updaterCode" column="updater_code"/>
        <result property="updatedate" column="updatedate"/>
        <result property="workType" column="work_type"/>
        <result property="riskLevel" column="risk_level"/>
        <result property="workDescription" column="work_description"/>
        <result property="workPeopleNumber" column="work_people_number"/>
        <result property="hpjb" column="hpjb"/>
        <result property="estimateWorktime" column="estimate_worktime"/>
        <result property="bookingticketId" column="bookingticket_id"/>
        <result property="deptId" column="dept_id"/>
        <result property="deptName" column="dept_name"/>
        <result property="reservationNumber" column="reservation_number"/>
        <result property="whgzxkzh" column="whgzxkzh"/>
        <result property="dhzyxkzh" column="dhzyxkzh"/>
        <result property="xzkjxkzh" column="xzkjxkzh"/>
        <result property="mbzyxkzh" column="mbzyxkzh"/>
        <result property="gczyxkzh" column="gczyxkzh"/>
        <result property="gczyjb" column="gczyjb"/>
        <result property="addStatus" column="add_status"/>
    </resultMap>

    <sql id="selectTInvoiceBookingworkticketVo">
        select d.id,
               d.work_unit,
               d.work_area,
               d.unit_number,
               d.floor_location,
               d.work_start_time,
               d.work_end_time,
               d.del_flag,
               d.creater_code,
               d.createdate,
               d.updater_code,
               d.updatedate,
               d.contact,
               d.phonenumber,
               d.status,
               d.dept_id,
               d.user_mg,
               d.user_unit,
               s.dept_name,
               d.filename,
               d.reservation_number
        from t_invoice_bookingworkticket d
                 left join sys_dept s on s.dept_id = d.dept_id
    </sql>

    <sql id="selectTInvoiceWorkcontentVo">
        select d.id,
               d.del_flag,
               d.creater_code,
               d.createdate,
               d.updater_code,
               d.updatedate,
               d.work_type,
               d.risk_level,
               d.work_description,
               d.work_people_number,
               d.hpjb,
               d.estimate_worktime,
               d.bookingticket_id,
               d.dept_id,
               d.reservation_number,
               s.dept_name,
               d.whgzxkzh,
               d.dhzyxkzh,
               d.xzkjxkzh,
               d.mbzyxkzh,
               d.gczyxkzh,
               d.gczyjb,
               d.tag,
               d.yqxkzh,
               d.xpxp,
               d.add_status
        from t_invoice_workcontent d
                 left join sys_dept s on s.dept_id = d.dept_id
                 left join t_invoice_bookingworkticket b on d.bookingticket_id = b.id
    </sql>

    <select id="selectTInvoiceBookingworkticketList" parameterType="TInvoiceBookingworkticket"
            resultMap="TInvoiceBookingworkticketResult">
        <include refid="selectTInvoiceBookingworkticketVo"/>
        <where>
            <if test="workUnit != null and workUnit != ''">and work_unit like concat(concat('%', #{workUnit}), '%')</if>
            <if test="workArea != null and workArea != ''">and work_area = #{workArea}</if>
            <if test="unitNumber != null and unitNumber != ''">and unit_number = #{unitNumber}</if>
            <if test="floorLocation != null and floorLocation != ''">and floor_location = #{floorLocation}</if>
            <if test="workStartTime != null ">and work_start_time = #{workStartTime}</if>
            <if test="workEndTime != null ">and work_end_time = #{workEndTime}</if>
            <if test="createrCode != null and createrCode != ''">and creater_code = #{createrCode}</if>
            <if test="createdate != null ">and createdate = #{createdate}</if>
            <if test="updaterCode != null and updaterCode != ''">and updater_code = #{updaterCode}</if>
            <if test="updatedate != null ">and updatedate = #{updatedate}</if>
            <if test="contact != null  and contact != ''">and contact = #{contact}</if>
            <if test="phonenumber != null  and phonenumber != ''">and phonenumber = #{phonenumber}</if>
            <if test="status != null ">and status = #{status}</if>
            <if test="deptId != null ">and dept_id = #{deptId}</if>
            <if test="userMg != null ">and user_mg = #{userMg}</if>
            <if test="userUnit != null ">and user_unit = #{userUnit}</if>
            <if test="reservationNumber != null ">and reservation_number = #{reservationNumber}</if>
            <if test="filename != null  and filename != ''">and filename like concat(concat('%', #{filename}), '%')</if>
            <if test="workType != null ">and b.work_type = #{workType}</if>
            and d.del_flag = 0
        </where>
        <!-- 数据范围过滤 -->
        ${params.dataScope}
    </select>

    <!-- 新的list方法 -->
    <select id="selectTInvoiceWorkcontentList" parameterType="TInvoiceBookingworkticket"
            resultMap="TInvoiceWorkcontentResult">
        <include refid="selectTInvoiceWorkcontentVo"/>
        <where>
            <if test="createrCode != null ">and creater_code = #{createrCode}</if>
            <if test="createdate != null ">and to_char(b.createdate, 'yyyy-mm-dd') = to_char(#{createdate},
                'yyyy-mm-dd')
            </if>
            <if test="updaterCode != null ">and updater_code = #{updaterCode}</if>
            <if test="updatedate != null ">and updatedate = #{updatedate}</if>
            <if test="workType != null ">and FIND_IN_SET (#{workType},d.work_type) >0</if>
            <!--   &lt;!&ndash; <if test="riskLevel != null "> and risk_level = #{riskLevel}</if>&ndash;&gt;
                <if test="workDescription != null  and workDescription != ''"> and work_description = #{workDescription}</if>
                <if test="workPeopleNumber != null "> and work_people_number = #{workPeopleNumber}</if>
                <if test="guardianUnit != null  and guardianUnit != ''"> and guardian_unit = #{guardianUnit}</if>
                <if test="estimateWorktime != null  and estimateWorktime != ''"> and estimate_worktime = #{estimateWorktime}</if>
                <if test="bookingticketId != null "> and bookingticket_id = #{bookingticketId}</if>
                <if test="reservationNumber != null  and reservationNumber != ''"> and reservation_number = #{reservationNumber}</if>
                <if test="deptId != null "> and dept_id = #{deptId}</if>-->
            <if test="workUnit != null and workUnit != ''">and work_unit like concat(concat('%', #{workUnit}), '%')</if>
            <if test="workArea != null and workArea != ''">and b.work_area = #{workArea}</if>
            <if test="floorLocation != null and floorLocation != ''">and FIND_IN_SET (#{floorLocation},b.floor_location)
                >0
            </if>
            <if test="workStartTime != null ">and b.work_start_time = #{workStartTime}</if>
            <if test="workEndTime != null ">and b.work_end_time = #{workEndTime}</if>
            <if test="contact != null  and contact != ''">and b.contact = #{contact}</if>
            <if test="phonenumber != null  and phonenumber != ''">and b.phonenumber = #{phonenumber}</if>
            <if test="whgzxkzh != null  and whgzxkzh != ''">and whgzxkzh = #{whgzxkzh}</if>
            <if test="dhzyxkzh != null  and dhzyxkzh != ''">and dhzyxkzh = #{dhzyxkzh}</if>
            <if test="xzkjxkzh != null  and xzkjxkzh != ''">and xzkjxkzh = #{xzkjxkzh}</if>
            <if test="mbzyxkzh != null  and mbzyxkzh != ''">and mbzyxkzh = #{mbzyxkzh}</if>
            <if test="gczyxkzh != null  and gczyxkzh != ''">and gczyxkzh = #{gczyxkzh}</if>
            <if test="tag != null  and tag != ''">and tag = #{tag}</if>
            <if test="allNo != null  and allNo != ''">
                and
                (whgzxkzh like concat(concat('%', #{allNo}), '%')
                or dhzyxkzh like concat(concat('%', #{allNo}), '%')
                or xzkjxkzh like concat(concat('%', #{allNo}), '%')
                or mbzyxkzh like concat(concat('%', #{allNo}), '%')
                or gczyxkzh like concat(concat('%', #{allNo}), '%')
                or yqxkzh like concat(concat('%', #{allNo}), '%')
                )
            </if>
            and d.del_flag = 0
        </where>
        <!-- 数据范围过滤 -->
        ${params.dataScope}
        order by b.work_start_time desc ,d.bookingticket_id desc
    </select>

    <select id="selectDetailById" parameterType="Long" resultMap="TInvoiceBookingworkticketResultVo">
        select a.work_unit
             , a.work_area
             , a.floor_location
             , a.work_start_time
             , a.work_end_time
             , a.status
             , b.work_type
             , b.risk_level
             , b.work_description
             , b.work_people_number
             , b.estimate_worktime
             , a.contact
             , a.phonenumber
             , a.id
             , b.whgzxkzh
             , b.dhzyxkzh
             , b.xzkjxkzh
             , b.mbzyxkzh
             , b.gczyxkzh
             , b.gczyjb
             , b.tag
             , b.yqxkzh
             , b.xpxp
        from t_invoice_bookingworkticket a,
             t_invoice_workcontent b
        where a.id = b.bookingticket_id
          and a.id = #{id}
          and a.del_flag = 0
          and b.del_flag = 0
    </select>

    <select id="selectBookingworkticketById" parameterType="Long" resultMap="TInvoiceBookingworkticketResult">
        <include refid="selectTInvoiceBookingworkticketVo"/>
        where d.id = #{id}
    </select>

    <select id="selectTInvoiceBookingworkticketById" parameterType="Long" resultMap="TInvoiceBookingworkticketResult">
        select d.id
             , d.work_unit
             , d.work_area
             , d.unit_number
             , d.floor_location
             , d.work_start_time
             , d.work_end_time
             , d.del_flag
             , d.creater_code
             , d.createdate
             , d.updater_code
             , d.updatedate
             , d.contact
             , d.phonenumber
             , d.status
             , d.dept_id
             , d.user_mg
             , d.user_unit
             , s.dept_name
             , d.filename
             , d.reservation_number
             , su.user_id    as userSupId
             , su.nick_name    as userSupName
             , su.PHONENUMBER  as userSupPhone
             , su2.nick_name   as userMgName
             , su2.PHONENUMBER as userMgPhone
        from t_invoice_bookingworkticket d
                 left join sys_dept s on s.dept_id = d.dept_id
                 left join t_approve_reserve_invoice r on r.invoice_id = d.id
                 left join sys_user su on r.DEV_SUP_ID = su.user_id
                 left join sys_user su2 on d.user_mg = su2.user_id
        where d.id = #{id}
          and d.del_flag = 0
          and r.status != -1
        and rownum = 1
    </select>

    <!--   <select id="getWorkcontentList" resultType="TInvoiceWorkcontent" parameterType="int">
           SELECT
               b.id ,b.work_description, b.work_type , b.bookingticket_id
           FROM t_invoice_workcontent b
           <where>
              b.bookingticket_id = #{id}
               <if test="workType != null "> and work_type = #{workType}</if>
           </where>
       </select>-->

    <insert id="insertTInvoiceBookingworkticket" parameterType="TInvoiceBookingworkticket" useGeneratedKeys="true"
            keyProperty="id">
        <selectKey keyProperty="id" resultType="long" order="BEFORE">
            SELECT t_bookingworkticket_seq.NEXTVAL as id FROM DUAL
        </selectKey>
        insert into t_invoice_bookingworkticket
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="workUnit != null">work_unit,</if>
            <if test="workArea != null">work_area,</if>
            <if test="unitNumber != null">unit_number,</if>
            <if test="floorLocation != null">floor_location,</if>
            <if test="workStartTime != null">work_start_time,</if>
            <if test="workEndTime != null">work_end_time,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="createrCode != null">creater_code,</if>
            <if test="updaterCode != null">updater_code,</if>
            <if test="updatedate != null">updatedate,</if>
            <if test="contact != null">contact,</if>
            <if test="phonenumber != null">phonenumber,</if>
            <if test="status != null">status,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="userMg != null">user_mg,</if>
            <if test="userUnit != null">user_unit,</if>
            <if test="reservationNumber != null">reservation_number,</if>
            <if test="filename != null">filename,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="workUnit != null">#{workUnit},</if>
            <if test="workArea != null">#{workArea},</if>
            <if test="unitNumber != null">#{unitNumber},</if>
            <if test="floorLocation != null">#{floorLocation},</if>
            <if test="workStartTime != null">#{workStartTime},</if>
            <if test="workEndTime != null">#{workEndTime},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="createrCode != null">#{createrCode},</if>
            <if test="updaterCode != null">#{updaterCode},</if>
            <if test="updatedate != null">#{updatedate},</if>
            <if test="contact != null">#{contact},</if>
            <if test="phonenumber != null">#{phonenumber},</if>
            <if test="status != null">#{status},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="userMg != null">#{userMg},</if>
            <if test="userUnit != null">#{userUnit},</if>
            <if test="reservationNumber != null">#{reservationNumber},</if>
            <if test="filename != null">#{filename},</if>
        </trim>
    </insert>

    <update id="updateTInvoiceBookingworkticket" parameterType="TInvoiceBookingworkticket">
        update t_invoice_bookingworkticket
        <trim prefix="SET" suffixOverrides=",">
            <if test="workUnit != null">work_unit = #{workUnit},</if>
            <if test="workArea != null">work_area = #{workArea},</if>
            <if test="unitNumber != null">unit_number = #{unitNumber},</if>
            <if test="floorLocation != null">floor_location = #{floorLocation},</if>
            <if test="workStartTime != null">work_start_time = #{workStartTime},</if>
            <if test="workEndTime != null">work_end_time = #{workEndTime},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="createrCode != null">creater_code = #{createrCode},</if>
            <if test="createdate != null">createdate = #{createdate},</if>
            <if test="updaterCode != null">updater_code = #{updaterCode},</if>
            <if test="updatedate != null">updatedate = #{updatedate},</if>
            <if test="contact != null">contact = #{contact},</if>
            <if test="phonenumber != null">phonenumber = #{phonenumber},</if>
            <if test="status != null">status = #{status},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="userMg != null">user_mg = #{userMg},</if>
            <if test="userUnit != null">user_unit = #{userUnit},</if>
            <if test="reservationNumber != null">reservation_number = #{reservationNumber},</if>
            <if test="filename != null">filename = #{filename},</if>
        </trim>
        where id = #{id}
    </update>

    <update id="deleteTInvoiceBookingworkticketById" parameterType="Long">
        update t_invoice_bookingworkticket
        set del_flag = 2
        where id = #{id}
    </update>

    <update id="deleteTInvoiceBookingworkticketByIds" parameterType="String">
        update t_invoice_bookingworkticket set del_flag = 2 where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>
    <update id="cancelTInvoiceBookingworkticketById" parameterType="Long">
        update t_invoice_bookingworkticket
        set status = 7
        where id = #{id}
    </update>



</mapper>
