<?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.boman.report.mapper.StatisticReportPersonnelMapper">

    <resultMap type="com.boman.domain.entity.StatisticReportPersonnel" id="StatisticReportPersonnelResult">
        <id property="id" column="id"/>
        <result property="reportName" column="report_name"/>
        <result property="statisticId" column="statistic_id"/>
        <result property="deptId" column="dept_id"/>
        <result property="deptName" column="dept_name"/>
        <result property="userId" column="user_id"/>
        <result property="userName" column="user_name"/>
        <result property="status" column="status"/>
        <result property="uploadFile" column="upload_file"/>
        <result property="isDel" column="is_del"/>
        <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 type="com.boman.domain.entity.StatisticReport" id="StatisticReportResult">
        <id property="id" column="id"/>
        <result property="reportName" column="report_name"/>
        <result property="reportId" column="report_id"/>
        <result property="templateName" column="template_name"/>
        <result property="startTime" column="start_time"/>
        <result property="endTime" column="end_time"/>
        <result property="reportStatus" column="report_status"/>
        <result property="status" column="status"/>
        <result property="repUploadFile" column="rep_upload_file"/>
        <result property="personnelId" column="personnel_id" />
        <result property="tableName" column="table_name" />
        <result property="deptId" column="dept_id"/>
        <result property="deptName" column="dept_name"/>
        <result property="userId" column="user_id"/>
        <result property="userName" column="user_name"/>
        <result property="remark" column="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"/>
        <result property="isDel" column="is_del"/>
    </resultMap>

    <select id="selectList" parameterType="com.boman.domain.entity.StatisticReportPersonnel" resultMap="StatisticReportResult">
        SELECT  r.*, p.id AS personnel_id, db.db_code as table_name,
               p.dept_id,p.dept_name,p.user_id,p.user_name
        FROM
            statistic_report_personnel p
        JOIN statistic_report r ON r.id = p.statistic_id
        JOIN jimu_report t on t.id = r.report_id
        JOIN jimu_report_db db on db.jimu_report_id = t.id
        AND r.is_del = 'N'
        AND r.`status` = '2'
        <where>
            <if test="createBy != null and createBy != ''">
                AND p.create_by = #{createBy}
            </if>
            <if test="reportName != null and reportName != ''">
                AND lower(r.report_name) like lower(concat('%', #{reportName}, '%'))
            </if>
            <if test="userName != null and userName != ''">
                AND p.user_name = #{userName}
            </if>
            <if test="sTime != null and sTime != ''">
                AND date_format(r.start_time, '%Y-%m-%d') &lt;= #{sTime}
            </if>
            <if test="eTime != null and eTime != ''">
                AND date_format(r.end_time, '%Y-%m-%d') &gt;= #{eTime}
            </if>
            <if test="status != null and status != ''">
                AND r.status = #{status}
            </if>
        </where>
    </select>

    <delete id="deleteByStatisticIds" parameterType="Long">
        delete from statistic_report_personnel where statistic_id in
        <foreach collection="array" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <update id="updateStatus" parameterType="com.boman.domain.entity.StatisticReportPersonnel">
        update statistic_report_personnel set status = #{status},upload_file=#{uploadFile} where id = #{id}
    </update>

    <insert id="insert" parameterType="com.boman.domain.entity.StatisticReportPersonnel">
        insert into statistic_report_personnel
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="reportName != null and reportName != ''">report_name,</if>
            <if test="statisticId != null">statistic_id,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="deptName != null">dept_name,</if>
            <if test="userId != null">user_id,</if>
            <if test="userName != null">user_name,</if>
            <if test="isDel != null">is_del,</if>
            <if test="status != null">status,</if>
            <if test="createBy != null">create_by,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="reportName != null and reportName != ''">#{reportName},</if>
            <if test="statisticId != null">#{statisticId},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="deptName != null">#{deptName},</if>
            <if test="userId != null">#{userId},</if>
            <if test="userName != null">#{userName},</if>
            <if test="isDel != null">#{isDel},</if>
            <if test="status != null">#{status},</if>
            <if test="remark != null">#{remark},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
        </trim>
    </insert>
    <sql id="selectStatisticReportPersonnelVo">
        select id, report_name, statistic_id, dept_id, dept_name, user_id, user_name,upload_file, is_del, status, create_by, create_time, update_by, update_time
        from statistic_report_personnel
    </sql>

    <select id="selectByStatisticId" parameterType="Long" resultMap="StatisticReportPersonnelResult">
        <include refid="selectStatisticReportPersonnelVo"/>
        where statistic_id = #{id} and is_del = "N"
    </select>
</mapper>