<?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.bonus.canteen.core.report.mapper.AccReportMapper">
    <select id="selectRechargeRecord" resultType="com.bonus.canteen.core.report.domain.RechargeRecordVO">
        select
        DATE_FORMAT(atwd.trade_time , '%Y-%m-%d') AS statistic_date,
        count(IF(atwd.trade_type = 10, 1, NULL)) AS recharge_num,
        count(IF(atwd.trade_type = 20, 1, NULL)) AS sub_num,
        SUM(IF(atwd.trade_type = 10 and at2.pay_type = 2, atwd.amount, 0)) AS cash_recharge_amount,
        SUM(IF(atwd.trade_type = 10 and at2.pay_type = 18, atwd.amount, 0)) AS ali_recharge_amount,
        SUM(IF(atwd.trade_type = 20, atwd.amount, 0)) AS sub_recharge_amount
        from
        account_trade_wallet_detail atwd
        Left join account_trade at2 on
        atwd.trade_id = at2.trade_id
        where
        atwd.trade_type in (10, 20)
        and atwd.trade_time <![CDATA[ >= ]]> #{param.startDateTime}
        and atwd.trade_time <![CDATA[ <= ]]> #{param.endDateTime}
        group by
        statistic_date
        order by
        statistic_date desc
    </select>

    <select id="selectWithdrawRecord" resultType="com.bonus.canteen.core.report.domain.WithdrawRecordVO">
        select
            atwd.user_id,
            su.nick_name,
            sd.dept_id,
            sd.dept_name,
            sd.dept_full_name,
            count(1) AS withdraw_num,
            SUM(IFNULL( atwd.amount, 0)) AS withdraw_amount
        from account_trade_wallet_detail atwd
                 LEFT JOIN sys_user su on atwd.user_id = su.user_id
                 LEFT JOIN sys_dept sd on su.dept_id = sd.dept_id
        where atwd.trade_type in (30)
          and atwd.trade_time <![CDATA[ >= ]]> #{param.startDateTime}
          and atwd.trade_time <![CDATA[ <= ]]> #{param.endDateTime}
        <if test="param.searchValue != null and param.searchValue != ''">
            and (su.nick_name like CONCAT('%',#{param.searchValue},'%')
            or su.user_id like CONCAT('%',#{param.searchValue},'%')
            )
        </if>
        group by user_id
        order by user_id
    </select>

    <select id="selectDeptIncomeOutcome" resultType="com.bonus.canteen.core.report.domain.DeptIncomeOutcomeVO">
        SELECT
        c.dept_id,
        c.dept_name,
        c.dept_full_name,
        c.wallet_bal_dept as wallet_bal,
        b.income,
        b.outcome,
        (c.wallet_bal_dept + b.outcome - b.income) as last_wallet_bal,
        0 as other_income,
        0 as other_outcome
        from
        (
        select
        sd.dept_id,
        SUM(IFNULL(CASE
        WHEN at2.trade_type in (10, 20, 130) then
        at2.actual_amount
        end, 0)) as income,
        SUM(IFNULL(CASE
        WHEN at2.trade_type in (30, 100, 110, 120) then
        at2.actual_amount
        end, 0)) as outcome
        from
        account_trade at2
        left join sys_user su on
        at2.user_id = su.user_id
        left join sys_dept sd on
        su.dept_id = sd.dept_id
        where
        at2.pay_state = 3
        <if test="param.deptIdList != null and param.deptIdList.size() > 0">
            and su.dept_id in
            <foreach collection="param.deptIdList" item="deptId" separator="," open="(" close=")">
                #{deptId}
            </foreach>
        </if>
        and at2.trade_time <![CDATA[ >= ]]> #{param.startDateTime}
        and at2.trade_time <![CDATA[ <= ]]> #{param.endDateTime}
        GROUP BY
        sd.dept_id
        ) b
        left join
        (
        select
        IFNULL(sum(a.wallet_bal), 0) as wallet_bal_dept ,
        su.dept_id,
        sd.dept_name,
        sd.dept_full_name
        from
        (
        select
        awi.user_id,
        IFNULL(sum(awi.wallet_bal), 0) as wallet_bal
        from
        account_wallet_info awi
        group by
        awi.user_id ) a
        left join sys_user su on
        a.user_id = su.user_id
        left join sys_dept sd on
        su.dept_id = sd.dept_id
        where
        1 = 1
        <if test="param.deptIdList != null and param.deptIdList.size() > 0">
            and su.dept_id in
            <foreach collection="param.deptIdList" item="deptId" separator="," open="(" close=")">
                #{deptId}
            </foreach>
        </if>
        group by
        su.dept_id ) c
        on
        c.dept_id = b.dept_id
        order by c.dept_id
    </select>

    <select id="selectUserIncomeOutcome" resultType="com.bonus.canteen.core.report.domain.UserIncomeOutcomeVO">
        SELECT
        b.user_id,
        b.phonenumber,
        b.dept_id,
        b.dept_name,
        b.dept_full_name,
        b.nick_name,
        c.wallet_bal_user as wallet_bal,
        ((d.income - d.outcome) + (b.income - b.outcome)) as wallet_bal_now,
        b.income,
        b.outcome,
        (d.income - d.outcome) as last_wallet_bal
        from
        (
        select
        su.user_id,
        su.nick_name,
        su.phonenumber,
        sd.dept_id,
        sd.dept_name,
        sd.dept_full_name,
        SUM(IFNULL(CASE
        WHEN at2.trade_type in (10, 20, 130) then
        at2.actual_amount
        end, 0)) as income,
        SUM(IFNULL(CASE
        WHEN at2.trade_type in (30, 100, 110, 120) then
        at2.actual_amount
        end, 0)) as outcome
        from
        sys_user su
        left join account_trade at2 on
        at2.user_id = su.user_id
        and at2.trade_time <![CDATA[ >= ]]> #{param.startDateTime}
        and at2.trade_time <![CDATA[ <= ]]> #{param.endDateTime}
        and at2.pay_state = 3
        left join sys_dept sd on
        su.dept_id = sd.dept_id
        where
        1 = 1
        <if test="param.deptIdList != null and param.deptIdList.size() > 0">
            and su.dept_id in
            <foreach collection="param.deptIdList" item="deptId" separator="," open="(" close=")">
                #{deptId}
            </foreach>
        </if>
        GROUP BY
        su.user_id
        ) b
        left join
        (
        select
        su.user_id,
        su.nick_name,
        su.phonenumber,
        sd.dept_id,
        sd.dept_name,
        sd.dept_full_name,
        SUM(IFNULL(CASE
        WHEN at2.trade_type in (10, 20, 130) then
        at2.actual_amount
        end, 0)) as income,
        SUM(IFNULL(CASE
        WHEN at2.trade_type in (30, 100, 110, 120) then
        at2.actual_amount
        end, 0)) as outcome
        from
        sys_user su
        left join account_trade at2 on
        at2.user_id = su.user_id
        and at2.trade_time <![CDATA[ < ]]> #{param.startDateTime}
        and at2.pay_state = 3
        left join sys_dept sd on
        su.dept_id = sd.dept_id
        where
        1 = 1
        <if test="param.deptIdList != null and param.deptIdList.size() > 0">
            and su.dept_id in
            <foreach collection="param.deptIdList" item="deptId" separator="," open="(" close=")">
                #{deptId}
            </foreach>
        </if>
        GROUP BY
        su.user_id
        ) d on b.user_id = d.user_id
        left join
        (
        select
        IFNULL(sum(a.wallet_bal), 0) as wallet_bal_user ,
        su.user_id
        from
        sys_user su
        left join
        (
        select
        awi.user_id,
        IFNULL(sum(awi.wallet_bal), 0) as wallet_bal
        from
        account_wallet_info awi
        group by
        awi.user_id ) a
        on a.user_id = su.user_id
        where
        1 = 1
        <if test="param.deptIdList != null and param.deptIdList.size() > 0">
            and su.dept_id in
            <foreach collection="param.deptIdList" item="deptId" separator="," open="(" close=")">
                #{deptId}
            </foreach>
        </if>
        group by
        a.user_id) c
        on
        c.user_id = b.user_id
        where
        1 = 1
        <if test="param.searchValue != null and param.searchValue != ''">
            and (b.nick_name like CONCAT('%',#{param.searchValue},'%')
            or b.phonenumber = #{encryptedSearchValue}
            or c.user_id like CONCAT('%',#{param.searchValue},'%')
            )
        </if>
        order by outcome desc
    </select>
</mapper>