<?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.StatisticReportMapper">

    <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="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"/>
        <collection  property="personnels"   javaType="java.util.List" resultMap="StatisticReportPersonnelResult" />
    </resultMap>

    <resultMap type="com.boman.domain.entity.StatisticReportPersonnel" id="StatisticReportPersonnelResult">
        <id property="id" column="psersonnel_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"/>
    </resultMap>

    <resultMap type="com.boman.domain.entity.vo.StatisticReportVo" id="StatisticReportVoResult">
        <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="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>



    <sql id="selectStatisticReportVo">
        select id, report_name, report_id, template_name, start_time, end_time, report_status, status, rep_upload_file, remark, create_by, create_time, update_by, update_time, is_del
        from statistic_report
    </sql>

    <select id="getReportInfo" parameterType="Long" resultMap="StatisticReportVoResult">
        <include refid="selectStatisticReportVo"/>
        where id = #{id} and is_del = "N"
    </select>
    <resultMap type="com.boman.domain.dto.StatisticReportDto" id="ReportDtoResult">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
    </resultMap>

    <sql id="selectReportVo">
        select * from statistic_report
    </sql>

    <select id="getDetailById" parameterType="Long" resultMap="StatisticReportResult">
        select t.*, p.id as psersonnel_id, p.dept_id, p.dept_name, p.user_id, p.user_name
        from statistic_report t left join statistic_report_personnel p on t.id = p.statistic_id
        where t.id = #{id}
    </select>

    <select id="selectList" parameterType="com.boman.domain.entity.StatisticReport" resultMap="StatisticReportResult">
        <include refid="selectReportVo"/>
        <where>
            <if test="id != null and id != ''">
                AND id = #{id}
            </if>
            <if test="createBy != null and createBy != ''">
                AND create_by = #{createBy}
            </if>
            <if test="reportName != null and reportName != ''">
                AND lower(report_name) like lower(concat('%', #{reportName}, '%'))
            </if>
            <if test="sTime != null and sTime != ''">
                AND date_format(start_time, '%Y-%m-%d') &lt;= #{sTime}
            </if>
            <if test="eTime != null and eTime != ''">
                AND date_format(end_time, '%Y-%m-%d') &gt;= #{eTime}
            </if>
            <if test="status != null and status != ''">
                AND status = #{status}
            </if>
        </where>
        order by create_time DESC
    </select>

    <insert id="insert" parameterType="com.boman.domain.entity.StatisticReport" useGeneratedKeys="true" keyProperty="id">
        insert into statistic_report
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="reportName != null and reportName != ''">report_name,</if>
            <if test="reportId != null">report_id,</if>
            <if test="templateName != null">template_name,</if>
            <if test="startTime != null">start_time,</if>
            <if test="endTime != null">end_time,</if>
            <if test="reportStatus != null">report_status,</if>
            <if test="status != null">status,</if>
            <if test="repUploadFile != null and repUploadFile != ''">rep_upload_file,</if>
            <if test="remark != null">remark,</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="reportId != null">#{reportId},</if>
            <if test="templateName != null">#{templateName},</if>
            <if test="startTime != null">#{startTime},</if>
            <if test="endTime != null">#{endTime},</if>
            <if test="reportStatus != null">#{reportStatus},</if>
            <if test="status != null">#{status},</if>
            <if test="repUploadFile != null and repUploadFile != ''">#{repUploadFile},</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>
            <if test="isDel != null">#{isDel},</if>
        </trim>
    </insert>

    <update id="update" parameterType="com.boman.domain.entity.StatisticReport">
        update statistic_report
        <trim prefix="SET" suffixOverrides=",">
            <if test="reportName != null and reportName != ''">report_name = #{reportName},</if>
            <if test="reportId != null">report_id = #{reportId},</if>
            <if test="templateName != null">template_name = #{templateName},</if>
            <if test="startTime != null">start_time = #{startTime},</if>
            <if test="endTime == null">end_time = #{endTime},</if>
            <if test="reportStatus != null">report_status = #{reportStatus},</if>
            <if test="status != null">status = #{status},</if>
            <if test="repUploadFile != null">rep_upload_file = #{repUploadFile},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="isDel != null">is_del = #{isDel},</if>
        </trim>
        where id = #{id}
    </update>

    <select id="selectReportInfo" resultMap="ReportDtoResult" parameterType="java.util.Map">
        select id, name from  jimu_report where
             create_by = #{userName}
            <if test="name != null and name != ''">
                AND lower(name) like lower(concat('%', #{name}, '%'))
            </if>
            order by create_by desc
    </select>
</mapper>