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

    <select id="selectExpensesPage" resultType="cn.iocoder.yudao.module.bpm.controller.admin.oa.vo.expenses.BpmOAExpensesPageRespVO">
        SELECT
            a.expenses_id AS expensesId,
            a.type as type,
            a.cost_section AS costSection,
            a.dept_id AS deptId,
            d.short_name AS deptName,
            a.total_money AS amount,
            a.detail AS detail,
            b.user_id AS userId,
            u.nickname AS userName,
            b.bank_id AS bankId,
            ub.nickname AS payeeName,
            ub.bank_no AS bankNo,
            ub.bank_name AS bankName,
            b.total_money AS totalMoney,
            b.process_instance_id AS processInstanceId,
            c.end_time AS endTime,
            b.status AS status
        FROM bpm_oa_expenses b
        JOIN bpm_oa_expenses_item a ON a.expenses_id = b.id
        JOIN bpm_process_instance_ext c ON c.process_instance_id = b.process_instance_id
        LEFT JOIN sf_factory_info d ON d.id = a.dept_id
        LEFT JOIN system_users u ON u.id = b.user_id
        LEFT JOIN system_bank ub ON ub.id = b.bank_id
        WHERE
            a.deleted = 0
            AND b.deleted = 0
            AND b.result = 2
            <if test="pageReqVO.userId != null">
                AND b.user_id = #{pageReqVO.userId}
            </if>
            <if test="pageReqVO.factoryId != null">
                AND a.dept_id = #{pageReqVO.factoryId}
            </if>
            <if test="pageReqVO.type != null">
                AND a.type = #{pageReqVO.type}
            </if>
            <if test="pageReqVO.costSection != null">
                AND a.cost_section = #{pageReqVO.costSection}
            </if>
            <if test="pageReqVO.payeeName != null">
                AND ub.nickname LIKE CONCAT('%', #{pageReqVO.payeeName}, '%')
            </if>
            <if test="pageReqVO.endTime != null and pageReqVO.endTime.length > 0">
                <if test="pageReqVO.endTime[0] != null">
                    and DATE_FORMAT(c.end_time, '%Y-%m-%d') &gt;= #{pageReqVO.endTime[0]}
                </if>
                <if test="pageReqVO.endTime[1] != null">
                    and DATE_FORMAT(c.end_time, '%Y-%m-%d') &lt;= #{pageReqVO.endTime[1]}
                </if>
            </if>
        ORDER BY c.end_time DESC, a.expenses_id
    </select>

    <select id="selectTotal" resultType="cn.iocoder.yudao.module.bpm.controller.admin.oa.vo.expenses.BpmOAExpensesTotal">
        SELECT
            SUM(a.total_money) AS payableAmount,
            SUM(a.amount_paid) AS amountPaid,
            SUM(a.total_money) - SUM(a.amount_paid) AS remainingPayable
        FROM
            bpm_oa_expenses a
            LEFT JOIN bpm_process_instance_ext c ON c.process_instance_id = a.process_instance_id
            LEFT JOIN system_bank ub ON ub.id = a.bank_id
        WHERE
            a.deleted = 0
            AND a.result = 2
            <if test="pageReqVO.userId != null">
                AND a.user_id = #{pageReqVO.userId}
            </if>
            AND EXISTS (
                SELECT
                    1
                FROM
                    bpm_oa_expenses_item b
                WHERE
                    b.expenses_id = a.id
                    <if test="pageReqVO.factoryId != null">
                        AND b.dept_id = #{pageReqVO.factoryId}
                    </if>
                    <if test="pageReqVO.type != null">
                        AND b.type = #{pageReqVO.type}
                    </if>
                    <if test="pageReqVO.costSection != null">
                        AND b.cost_section = #{pageReqVO.costSection}
                    </if>
            )
            <if test="pageReqVO.payeeName != null">
                AND ub.nickname LIKE CONCAT('%', #{pageReqVO.payeeName}, '%')
            </if>
            <if test="pageReqVO.endTime != null and pageReqVO.endTime.length > 0">
                <if test="pageReqVO.endTime[0] != null">
                    and DATE_FORMAT(c.end_time, '%Y-%m-%d') &gt;= #{pageReqVO.endTime[0]}
                </if>
                <if test="pageReqVO.endTime[1] != null">
                    and DATE_FORMAT(c.end_time, '%Y-%m-%d') &lt;= #{pageReqVO.endTime[1]}
                </if>
            </if>
    </select>

    <update id="oneClickPayment">
        UPDATE bpm_oa_expenses e
            JOIN bpm_process_instance_ext b ON e.process_instance_id = b.process_instance_id
            SET e.STATUS = 1, e.amount_paid = e.total_money
        WHERE
            e.result = 2
            AND e.STATUS != 2
            AND DATE_FORMAT( b.end_time, '%Y-%m' ) = #{month}
    </update>
</mapper>
