<?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="cn.iocoder.yudao.module.smartfactory.dal.mysql.handlinggroupuseramount.HandlingGroupUserAmountMapper">

    <!--
        一般情况下，尽可能使用 Mapper 进行 CRUD 增删改查即可。
        无法满足的场景，例如说多表关联查询，才使用 XML 编写 SQL。
        代码生成器暂时只生成 Mapper XML 文件本身，更多推荐 MybatisX 快速开发插件来生成查询。
        文档可见：https://www.iocoder.cn/MyBatis/x-plugins/
     -->

    <select id="getHandlingGroupUserAmountPage"
            resultType="cn.iocoder.yudao.module.smartfactory.dal.dataobject.handlinggroupuseramount.HandlingGroupUserAmountDO">
        SELECT a.*,
               b.nick_name as userName
        FROM sf_handling_group_user_amount as a
        LEFT JOIN sf_staff as b on a.user_id = b.id
        <where>
            a.deleted = 0
            and b.deleted = 0
            <if test="vo.userId != null">
                AND a.user_id = #{vo.userId}
            </if>
            <if test="vo.handlingGroupId != null">
                AND a.handling_group_id = #{vo.handlingGroupId}
            </if>
            <if test="vo.dateStr != null and vo.dateStr != ''">
                and a.dateStr like concat('%', #{vo.dateStr}, '%')
            </if>
            <if test="vo.createTime != null and vo.createTime.length > 0">
                <if test="vo.createTime[0] != null">
                    and a.create_time &gt;= #{vo.createTime[0]}
                </if>
                <if test="vo.createTime[1] != null">
                    and a.create_time &lt;= #{vo.createTime[1]}
                </if>
            </if>
        </where>
    </select>
    <select id="staffWagesPage"
            resultType="cn.iocoder.yudao.module.smartfactory.controller.admin.handlinggroupuseramount.vo.StaffWagesPageVO">
        select
        a.id as userId,
        a.nick_name as userName,
        IFNULL(sum( b.amount ),0) AS totalAmount
        from sf_staff as a
        left join sf_handling_group_user_amount as b on a.id = b.user_id
        <where>
            a.deleted = 0
            and b.deleted = 0
            <if test="dto.userId != null">
                AND a.id = #{dto.userId}
            </if>
            <if test="dto.factoryId != null">
                AND a.factory_id = #{dto.factoryId}
            </if>
            <if test="dto.dateStr != null and dto.dateStr != ''">
                and b.date_str like concat('%', #{dto.dateStr}, '%')
            </if>
            <if test="dto.createTime != null and dto.createTime.length > 0">
                <if test="dto.createTime[0] != null">
                    and b.create_time &gt;= #{dto.createTime[0]}
                </if>
                <if test="dto.createTime[1] != null">
                    and b.create_time &lt;= #{dto.createTime[1]}
                </if>
            </if>
        </where>
        group by a.id
    </select>
    <select id="getListByMonth" resultType="java.util.Map">
        SELECT
        a.user_id as userId,
        b.nick_name as nickName,
        <foreach collection="dateList" item="date" separator=",">
            SUM(CASE WHEN a.date_str = #{date} THEN a.amount ELSE 0 END) AS `${date}`
        </foreach>,
        -- 继续添加该月所有日期...
        SUM(a.amount) AS totalAmount
        FROM
        sf_handling_group_user_amount as a
        left join sf_staff as b on a.user_id = b.id
        <where>
            a.deleted = 0
            <if test="id != null">
                and b.id = #{id}
            </if>
            <if test="factoryId != null">
                and b.factory_id = #{factoryId}
            </if>
            <if test="name != null and name != ''">
                and b.nick_name like concat('%', #{name}, '%')
            </if>
        </where>
        GROUP BY
        a.user_id;
    </select>
    <select id="getHandlingGroupUserAmountList"
            resultType="cn.iocoder.yudao.module.smartfactory.dal.dataobject.handlinggroupuseramount.HandlingGroupUserAmountDO">
        SELECT a.*,
        b.nick_name as userName
        FROM sf_handling_group_user_amount as a
        LEFT JOIN sf_staff as b on a.user_id = b.id
        <where>
            a.deleted = 0
            and b.deleted = 0
            <if test="vo.userId != null">
                AND a.user_id = #{vo.userId}
            </if>
            <if test="vo.factoryId != null">
                and a.factory_id = #{vo.factoryId}
            </if>
            <if test="vo.handlingGroupId != null">
                AND a.handling_group_id = #{vo.handlingGroupId}
            </if>
            <if test="vo.dateStr != null and vo.dateStr != ''">
                and a.date_str like concat('%', #{vo.dateStr}, '%')
            </if>
            <if test="vo.createTime != null and vo.createTime.length > 0">
                <if test="vo.createTime[0] != null">
                    and a.create_time &gt;= #{vo.createTime[0]}
                </if>
                <if test="vo.createTime[1] != null">
                    and a.create_time &lt;= #{vo.createTime[1]}
                </if>
            </if>
        </where>
    </select>

    <select id="selectAmountSum" resultType="cn.iocoder.yudao.module.smartfactory.dal.dataobject.handlinggroupuseramount.HandlingGroupUserAmountDO">
        SELECT
            user_id AS userId,
            SUM(amount) AS amount
        FROM
            sf_handling_group_user_amount
        WHERE
            deleted = 0
            AND status = 0
            AND factory_id = #{factoryId}
            AND date_str LIKE CONCAT('%', #{month}, '%')
        GROUP BY
            user_id
    </select>

    <select id="selectIsExists" resultType="java.lang.Long">
        SELECT
            COUNT(*)
        FROM
            sf_handling_group_amount_specifications a
        WHERE
            NOT EXISTS (
                SELECT 1
                FROM sf_handling_group_user_amount b
                WHERE a.factory_id = b.factory_id AND a.date_str = b.date_str
            )
            AND a.factory_id = #{factoryId}
            AND a.date_str LIKE CONCAT('%', #{month}, '%')
            AND a.deleted = 0
    </select>

    <select id="getByHandlingGroupIds"
            resultType="cn.iocoder.yudao.module.smartfactory.dal.dataobject.handlinggroupuseramount.HandlingGroupUserAmountDO">
        select a.*,
        b.nick_name as userName
        FROM sf_handling_group_user_amount as a
        LEFT JOIN sf_staff as b on a.user_id = b.id
        <where>
            a.deleted = 0
            and b.deleted = 0
            <if test="handlingGroupIds != null and handlingGroupIds.size() > 0">
                and a.handling_group_id in
                <foreach collection="handlingGroupIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>
    <select id="getByFactorIdAndDateStrAndHandlingGroupId"
            resultType="cn.iocoder.yudao.module.smartfactory.dal.dataobject.handlinggroupuseramount.HandlingGroupUserAmountDO">
        select a.*,
        b.nick_name as userName
        FROM sf_handling_group_user_amount as a
        LEFT JOIN sf_staff as b on a.user_id = b.id
        <where>
            a.deleted = 0
            and b.deleted = 0
            <if test="factoryIds != null and factoryIds.size() > 0 ">
                and a.factory_id in
                <foreach collection="factoryIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <if test="dateStrList != null and dateStrList.size() > 0 ">
                and a.date_str in
                <foreach collection="dateStrList" item="dateStr" open="(" separator="," close=")">
                    #{dateStr}
                </foreach>
            </if>
            <if test="handlingGroupIds != null and handlingGroupIds.size() > 0 ">
                and a.handling_group_id in
                <foreach collection="handlingGroupIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>
</mapper>
