<?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="vip.xiaonuo.biz.modular.consumptionrecord.mapper.ConsumptionRecordMapper">

    <select id="getPageList" resultType="vip.xiaonuo.biz.modular.consumptionrecord.entity.ConsumptionRecord">
        select
            cr.id,
            su.name userName,
            cr.consumption_type,
            IFNULL(cr.voucher_money,0) voucher_money,
            IFNULL(cr.account_money,0) account_money,
            cr.consumption_time,
            so.name orgName,
            cr.consumption_remark,
            cr.consumption_operate,
            IFNULL(cr.consumption_money,0) consumption_money,
            IFNULL(cr.voucher_balance,0) voucher_balance,
            IFNULL(cr.account_balance,0) account_balance,
            cr.adjust_type,
            su.PHONE,
            cr.new_account_balance newAccountBalance,
            cr.new_voucher_balance newVoucherBalance,
            su.USER_REFERRAL_CODE userCode
        from biz_consumption_record cr
        left join SYS_USER su on cr.user_id = su.id
        left join SYS_ORG so on so.id = cr.consumption_org
        <where>
            ${ew.sqlSegment}
        </where>
    </select>

    <select id="getRecordTotal" resultType="vip.xiaonuo.biz.modular.consumptionrecord.param.ConsumptionResult">
        select
            count(*) orderCount,
            ifnull(sum(bcr.consumption_money),0) orderMoney
        from biz_consumption_record bcr
        <where>
            ${ew.sqlSegment}
        </where>
    </select>

    <select id="queryBalanceTotal"
            resultType="vip.xiaonuo.biz.modular.consumptionrecord.param.UserBalanceResult">
        select
            count(*) userCount,
            ifnull(sum(su.ACCOUNT_BALANCE),0) accountBalance,
            ifnull(sum(su.VOUCHER_BALANCE),0) voucherBalance
        from SYS_USER su
        <where>
            ${ew.sqlSegment}
        </where>
    </select>

    <select id="queryConsumptionChart" resultType="java.util.Map">
        SELECT
            DATE_FORMAT( t1.dateTime, '%m-%d' ) dateTime,
            IFNULL( t2.consumptionMoney, 0 ) AS consumptionMoney,
            IFNULL( t2.consumptionCount, 0 ) consumptionCount,
            IFNULL( t3.userCount, 0 ) userCount
        FROM
            (
                SELECT
                    CURDATE() AS dateTime UNION ALL
                SELECT
                    DATE_SUB( CURDATE(), INTERVAL 1 DAY ) AS dateTime UNION ALL
                SELECT
                    DATE_SUB( CURDATE(), INTERVAL 2 DAY ) AS dateTime UNION ALL
                SELECT
                    DATE_SUB( CURDATE(), INTERVAL 3 DAY ) AS dateTime UNION ALL
                SELECT
                    DATE_SUB( CURDATE(), INTERVAL 4 DAY ) AS dateTime UNION ALL
                SELECT
                    DATE_SUB( CURDATE(), INTERVAL 5 DAY ) AS dateTime UNION ALL
                SELECT
                    DATE_SUB( CURDATE(), INTERVAL 6 DAY ) AS dateTime
            ) t1
                LEFT JOIN (
                SELECT LEFT
                    ( bcr.consumption_time, 10 ) AS dayTime,
                    IFNULL( SUM( bcr.consumption_money ), 0 ) consumptionMoney,
                    count(*) consumptionCount
                FROM
                    biz_consumption_record bcr
                WHERE
                    bcr.delete_flag = 'NOT_DELETE'
                and bcr.consumption_operate in('3','4')
                GROUP BY
                    LEFT ( bcr.consumption_time, 10 )
                ORDER BY
                    dayTime DESC
            ) t2 ON t1.dateTime = t2.dayTime
                LEFT JOIN (
                SELECT LEFT
                    ( bcr.create_time, 10 ) AS userDate,
                    count(*) userCount
                FROM
                    SYS_USER bcr
                WHERE
                    bcr.delete_flag = 'NOT_DELETE'
                GROUP BY
                    LEFT ( bcr.create_time, 10 )
                ORDER BY
                    userDate DESC
            ) t3 ON t1.dateTime = t3.userDate
        ORDER BY
            t1.dateTime
    </select>

    <select id="queryEachStore" resultType="java.util.Map">
        SELECT
            so.`name`,
            so.CODE code,
            ifnull(bcr.account_money,0) accountMoney,
            ifnull(bcr.orderCount,0) orderCount,
            ifnull(bcr.voucher_money,0) voucherMoney,
            ifnull(su.userCount,0) userCount
        FROM
            SYS_ORG so
            LEFT JOIN (
                SELECT
                    count(*) orderCount,
                    IFNULL( sum( account_money ), 0 ) account_money,
                    IFNULL( sum( voucher_money ), 0 ) voucher_money,
                    bcr.consumption_org
                FROM
                    biz_consumption_record bcr
                where bcr.DELETE_FLAG = 'NOT_DELETE'
                and bcr.consumption_operate in ('3','4')
                <if test="orgIds != null and orgIds.size > 0">
                    AND bcr.consumption_org IN
                    <foreach collection="orgIds" item="item" separator="," open="(" close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="startTime !=null and endTime !=null and startTime!='' and endTime != '' ">
                    and bcr.consumption_time between #{startTime} and #{endTime}
                </if>
                GROUP BY
                    bcr.consumption_org
            ) bcr ON bcr.consumption_org = so.id
            LEFT JOIN ( SELECT count(*) userCount, su.ORG_ID FROM SYS_USER su
                where su.DELETE_FLAG = 'NOT_DELETE'
                <if test="orgIds != null and orgIds.size > 0">
                    AND su.ORG_ID IN
                    <foreach collection="orgIds" item="item" separator="," open="(" close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="startTime !=null and endTime !=null and startTime!='' and endTime != '' ">
                    and su.create_time between #{startTime} and #{endTime}
                </if>
                GROUP BY su.ORG_ID ) su ON su.ORG_ID = so.id
            where so.PARENT_ID!=0
            and so.DELETE_FLAG = 'NOT_DELETE'
    </select>

    <select id="getWarnPageList" resultType="vip.xiaonuo.biz.modular.user.result.BizMemberUserResult">
        SELECT
            a.userId id,
            su.NAME name,
            su.PHONE phone,
            su.ACCOUNT account,
            su.USER_STATUS,
            a.count
        FROM
            (
                SELECT
                    a.REFERRAL_USER userId,
                    ifnull( sum( a.count ), 0 ) count
                FROM
                    (
                    SELECT
                    count(*) count,
                    bcr.user_id,
                    su.REFERRAL_USER
                    FROM
                    biz_consumption_record bcr
                    LEFT JOIN SYS_USER su ON bcr.user_id = su.id
                    WHERE
                    bcr.consumption_operate = '2'
                    and bcr.DELETE_FLAG = 'NOT_DELETE'
                    AND bcr.adjust_type = '2'
                    GROUP BY
                    bcr.user_id
                    ) a
                GROUP BY
                    a.REFERRAL_USER
            ) a
                LEFT JOIN SYS_USER su ON su.id = a.userId
        <where>
            ${ew.sqlSegment}
        </where>
    </select>

</mapper>
