<?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.yd.csf.service.dao.SalarySplitMapper">

    <select id="page" resultType="com.yd.csf.feign.dto.salarysplit.ApiSalarySplitPageDto">
        select ss.* from salary_split ss
        <where>
            and ss.fortune_account_biz_id = #{request.fortuneAccountBizId}
            and ss.is_deleted = 0
        </where>
    </select>

    <select id="summaryPage" resultType="com.yd.csf.feign.dto.salarysplit.ApiSalarySplitSummaryPageDto">
        select
        ss.salary_split_biz_id,
        ss.salary_split_no,
        ss.fortune_account_biz_id,
        ss.broker_name,
        ss.broker_biz_id,
        ss.internal_number,
        fa.team,
        ss.from_amount,
        ss.to_currency,
        ss.exchange_rate,
        ss.to_amount,
        fa.fortune_account_date,
        ss.bill_org,
        fa.status,
        fa.amount,
        fa.hkd_amount,
        fa.currency,
        DATE_FORMAT(fa.fortune_account_date, '%Y%m') as fortune_account_month,
        fa.business_no
        from salary_split ss
        inner join fortune_account fa
        on ss.fortune_account_biz_id = fa.fortune_account_biz_id
        and fa.is_deleted = 0
        <where>
            <if test="request.brokerBizIdList != null and request.brokerBizIdList.size > 0">
                and ss.broker_biz_id in
                <foreach collection="request.brokerBizIdList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>

            <!-- 开始月份：大于等于该月第一天（包含整个月开始） -->
            <if test="request.startMonth != null and request.startMonth != ''">
                and fa.fortune_account_date >= DATE(CONCAT(#{request.startMonth}, '-01'))
            </if>
            <!-- 结束月份：小于下月第一天（包含整个月结束） -->
            <if test="request.endMonth != null and request.endMonth != ''">
                and fa.fortune_account_date &lt; DATE_ADD(DATE(CONCAT(#{request.endMonth}, '-01')), INTERVAL 1 MONTH)
            </if>
            <if test="request.billOrg != null and request.billOrg != ''">
                and ss.bill_org like concat('%', #{request.billOrg}, '%')
            </if>
            and ss.is_deleted = 0
        </where>
    </select>

    <select id="queryList" resultType="java.lang.String">
        select ss.salary_split_biz_id
        from salary_split ss
        inner join fortune_account fa
        on ss.fortune_account_biz_id = fa.fortune_account_biz_id
        and fa.is_deleted = 0
        <where>
            <if test="request.brokerBizIdList != null and request.brokerBizIdList.size > 0">
                and ss.broker_biz_id in
                <foreach collection="request.brokerBizIdList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>

            <!-- 开始月份：大于等于该月第一天（包含整个月开始） -->
            <if test="request.startMonth != null and request.startMonth != ''">
                and fa.fortune_account_date >= DATE(CONCAT(#{request.startMonth}, '-01'))
            </if>
            <!-- 结束月份：小于下月第一天（包含整个月结束） -->
            <if test="request.endMonth != null and request.endMonth != ''">
                and fa.fortune_account_date &lt; DATE_ADD(DATE(CONCAT(#{request.endMonth}, '-01')), INTERVAL 1 MONTH)
            </if>
            <if test="request.billOrg != null and request.billOrg != ''">
                and ss.bill_org like concat('%', #{request.billOrg}, '%')
            </if>
            <if test="request.businessNo != null and request.businessNo != ''">
                and fa.business_no like concat('%', #{request.businessNo}, '%')
            </if>
            and ss.is_deleted = 0
        </where>
    </select>

    <select id="summaryList" resultType="com.yd.csf.feign.dto.salarysplit.ApiSalarySplitSummaryPageDto">
        select
        ss.salary_split_biz_id,
        ss.salary_split_no,
        ss.fortune_account_biz_id,
        ss.broker_name,
        ss.broker_biz_id,
        ss.internal_number,
        fa.team,
        ss.from_amount,
        ss.to_currency,
        ss.exchange_rate,
        ss.to_amount,
        fa.fortune_account_date,
        ss.bill_org,
        fa.status,
        fa.amount,
        fa.hkd_amount,
        fa.currency,
        DATE_FORMAT(fa.fortune_account_date, '%Y%m') as fortune_account_month,
        fa.business_no
        from salary_split ss
        inner join fortune_account fa
        on ss.fortune_account_biz_id = fa.fortune_account_biz_id
        and fa.is_deleted = 0
        <where>
            <if test="request.salarySplitBizIdList != null and request.salarySplitBizIdList.size > 0">
                and ss.salary_split_biz_id in
                <foreach collection="request.salarySplitBizIdList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="request.brokerBizIdList != null and request.brokerBizIdList.size > 0">
                and ss.broker_biz_id in
                <foreach collection="request.brokerBizIdList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>

            <!-- 开始月份：大于等于该月第一天（包含整个月开始） -->
            <if test="request.startMonth != null and request.startMonth != ''">
                and fa.fortune_account_date >= DATE(CONCAT(#{request.startMonth}, '-01'))
            </if>
            <!-- 结束月份：小于下月第一天（包含整个月结束） -->
            <if test="request.endMonth != null and request.endMonth != ''">
                and fa.fortune_account_date &lt; DATE_ADD(DATE(CONCAT(#{request.endMonth}, '-01')), INTERVAL 1 MONTH)
            </if>
            <if test="request.billOrg != null and request.billOrg != ''">
                and ss.bill_org like concat('%', #{request.billOrg}, '%')
            </if>
            and ss.is_deleted = 0
        </where>
    </select>
</mapper>
