<?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="org.jeecg.modules.system.mapper.SysOperateRelMapper">

    <insert id="add">
        replace
        into sys_operate_rel(
        assistant_id,
        operate_id,
        stat_month,
        create_by
        )
        values
            (
        #{rel.assistantId},
        #{rel.operateId},
        #{rel.statMonth},
        #{rel.createBy}
        )
    </insert>

    <select id="getOne" resultType="org.jeecg.modules.system.entity.SysOperateRel">
        select assistant_id, operate_id, stat_month
        from sys_operate_rel
        where assistant_id = #{assistantId}
        order by create_time desc limit 1
    </select>

    <select id="getMore" resultType="org.jeecg.modules.system.entity.SysOperateRel">
        select assistant_id, operate_id, stat_month
        from sys_operate_rel
        where assistant_id = #{userId}
          AND stat_month >= DATE_FORMAT(#{startDate}, '%Y-%m')
          AND stat_month &lt;= DATE_FORMAT(#{endDate}, '%Y-%m')
        order by stat_month
    </select>

    <select id="getMoreByOperatorId" resultType="org.jeecg.modules.system.entity.SysOperateRel">
        select assistant_id, operate_id, stat_month
        from sys_operate_rel
        where operate_id = #{userId}
          AND stat_month >= DATE_FORMAT(#{startDate}, '%Y-%m')
          AND stat_month &lt;= DATE_FORMAT(#{endDate}, '%Y-%m')
        order by stat_month
    </select>

    <select id="queryPageList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.assistant_id as 'assistantId', t2.realname as 'assistantName', t1.operate_id as 'operateId', t3.realname as 'operateName', t1.stat_month as 'time', t1.create_by as 'createBy '
        FROM `sys_operate_rel` t1
                 LEFT JOIN sys_user t2 ON t1.assistant_id = t2.id
                 LEFT JOIN sys_user t3 ON t1.operate_id = t3.id
    </select>

</mapper>