<?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.bpm.dal.mysql.financialpayment.FinancialPaymentMapper">

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

    <select id="getFinancialPaymentPage"
            resultType="cn.iocoder.yudao.module.bpm.dal.dataobject.financialpayment.FinancialPaymentDO">
        select
        a.*,
        b.nickname as nickname,
        c.name as deptName,
        d.nickname as receiveUserNickName,
        case when ISNULL(a.company_id) THEN sf.short_name ELSE company.short_name END as companyName
        from bpm_financial_payment as a
        left join system_users as b on a.user_id = b.id
        left join system_dept as c on b.dept_id = c.id
        left join system_users as d on a.receive_user_id = d.id
        left join system_dept as company on company.id = a.company_id
        left join sf_factory_info as sf on sf.id = a.company_factory_id
        <where>
            a.deleted = 0
            <if test="vo.deptIds != null and vo.deptIds.size() > 0">
                AND c.id IN
                <foreach collection="vo.deptIds" item="deptId" open="(" separator="," close=")">
                    #{deptId}
                </foreach>
            </if>
            <if test="vo.nickname != null and vo.nickname != ''">
                and b.nickname like concat('%', #{vo.nickname}, '%')
            </if>
            <if test="vo.processInstanceId != null and vo.processInstanceId != ''">
                and a.process_instance_id = #{vo.processInstanceId}
            </if>
            <if test="vo.type != null">
                and a.type = #{vo.type}
            </if>
            <if test="vo.status != null">
                and a.status = #{vo.status}
            </if>
            <if test="vo.reason != null and vo.reason != ''">
                and a.reason like concat('%', #{vo.reason}, '%')
            </if>
            <if test="vo.financeName != null and vo.financeName != ''">
                and d.nickname like concat('%', #{vo.financeName}, '%')
            </if>
            <if test="vo.receiveType != null">
                <if test="vo.receiveType == 1">
                    and a.receive_user_id is null
                </if>
                <if test="vo.receiveType == 2">
                    and a.receive_user_id is not null
                </if>
                <if test="vo.receiveType == 3">
                    and a.receive_user_id = #{vo.receiveUserId}
                </if>
                <if test="vo.receiveType == 4">
                    and a.receive_user_id != #{vo.receiveUserId}
                </if>
            </if>
            <if test="vo.beginTime != null and vo.beginTime.length > 0">
                <if test="vo.beginTime[0] != null">
                    and a.begin_time &gt;= #{vo.beginTime[0]}
                </if>
                <if test="vo.beginTime[1] != null">
                    and a.begin_time &lt;= #{vo.beginTime[1]}
                </if>
            </if>
            <if test="vo.endTime != null and vo.endTime.length > 0">
                <if test="vo.endTime[0] != null">
                    and a.end_time &gt;= #{vo.endTime[0]}
                </if>
                <if test="vo.endTime[1] != null">
                    and a.end_time &lt;= #{vo.endTime[1]}
                </if>
            </if>
        </where>
        ORDER BY a.status asc,a.end_time DESC
        </select>
    <update id="cancel">
        update bpm_financial_payment set receive_user_id = null
        where id in
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
        and status = 0
    </update>
    <select id="getFinancialPayment"
            resultType="cn.iocoder.yudao.module.bpm.dal.dataobject.financialpayment.FinancialPaymentDO">
        select
        a.*,
        b.nickname as nickname,
        c.name as deptName,
        d.nickname as receiveUserNickName
        from bpm_financial_payment as a
        left join system_users as b on a.user_id = b.id
        left join system_dept as c on b.dept_id = c.id
        left join system_users as d on a.receive_user_id = d.id
        <where>
            a.id = #{id}
        </where>
    </select>

    <select id="selectPaymentTotal" resultType="cn.iocoder.yudao.module.bpm.dal.dataobject.financialpayment.FinancialPaymentDO">
        SELECT
            SUM(a.amount_payable) AS amountPayable,
            SUM(a.actual_payment) AS actualPayment
        FROM
            bpm_financial_payment a
            LEFT JOIN system_users b ON a.user_id = b.id
            LEFT JOIN system_dept as c on b.dept_id = c.id
            LEFT JOIN system_users as d on a.receive_user_id = d.id
        WHERE
            a.deleted = 0
            <if test="vo.deptIds != null and vo.deptIds.size() > 0">
                AND c.id IN
                <foreach collection="vo.deptIds" item="deptId" open="(" separator="," close=")">
                #{deptId}
                </foreach>
            </if>
            <if test="vo.nickname != null and vo.nickname != ''">
                and b.nickname like concat('%', #{vo.nickname}, '%')
            </if>
            <if test="vo.type != null">
                and a.type = #{vo.type}
            </if>
            <if test="vo.status != null">
                and a.status = #{vo.status}
            </if>
            <if test="vo.financeName != null and vo.financeName != ''">
                and d.nickname like concat('%', #{vo.financeName}, '%')
            </if>
            <if test="vo.receiveType != null">
                <if test="vo.receiveType == 1">
                    and a.receive_user_id is null
                </if>
                <if test="vo.receiveType == 2">
                    and a.receive_user_id is not null
                </if>
                <if test="vo.receiveType == 3">
                    and a.receive_user_id = #{vo.receiveUserId}
                </if>
                <if test="vo.receiveType == 4">
                    and a.receive_user_id != #{vo.receiveUserId}
                </if>
            </if>
            <if test="vo.beginTime != null and vo.beginTime.length > 0">
                <if test="vo.beginTime[0] != null">
                    and a.begin_time &gt;= #{vo.beginTime[0]}
                </if>
                <if test="vo.beginTime[1] != null">
                    and a.begin_time &lt;= #{vo.beginTime[1]}
                </if>
            </if>
            <if test="vo.endTime != null and vo.endTime.length > 0">
                <if test="vo.endTime[0] != null">
                    and a.end_time &gt;= #{vo.endTime[0]}
                </if>
                <if test="vo.endTime[1] != null">
                    and a.end_time &lt;= #{vo.endTime[1]}
                </if>
            </if>
    </select>
</mapper>
