<?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.ruoyi.dataanalysis.mapper.DataAnalysisMapper">

    <select id="customerCharts" resultType="com.ruoyi.dataanalysis.model.result.CustomerChartsResult">
        SELECT
            t.id,
            t.nickname,
            t.`name`,
            t.mobile,
            t.account,
            t.create_time,
            (
                SELECT
                    IFNULL(sum(o.amount), 0)
                FROM
                    t_order o
                WHERE
                    o.customer_id = t.id
                  AND o.first_status = 2
            ) orderAmount,
            (
                SELECT
                    IFNULL(
                            sum(
                                    CASE
                                        WHEN o.`status` = 4 THEN
                                            o.amount
                                        ELSE
                                            o.first_price
                                        END
                            ),
                            0
                    )
                FROM
                    t_order o
                WHERE
                    o.customer_id = t.id
                  AND o.first_status = 2
            ) payAmount,
            (
                SELECT
                    count(*)
                FROM
                    t_order o
                WHERE
                    o.customer_id = t.id
                  AND o.first_status = 2
            ) orderNum
        FROM
            t_customer t
        WHERE t.del_flag = 1
        <if test="param!=null and param.beginTime != null ">
            and  t.create_time >= #{param.beginTime}

        </if>
        <if test="param!=null and param.endTime != null ">
            and  t.create_time &lt; #{param.endTime}
        </if>
        <if test="param!=null and param.nickname != null  and param.nickname != ''">
            and  t.nickname like concat('%', #{param.nickname}, '%')
        </if>
        <if test="param!=null and param.name != null  and param.name != ''">
            and  t.name like concat('%', #{param.name}, '%')
        </if>
        ORDER BY
        <foreach item="orderSort" collection="param.orderSortParamList" open=" " separator="," close=" ">
           ${orderSort.column} ${orderSort.sort}
        </foreach>
    </select>

    <select id="storeCharts" resultType="com.ruoyi.dataanalysis.model.result.StoreChartsResult">
        SELECT
        t.id,
        t.nickname,
        t.`name`,
        t.mobile,
        t.account,
        t.create_time,
        t.star,
        (
        SELECT
        a.id
        FROM
        t_auth a
        WHERE
        a.store_id = t.id
        AND a.result = 2
        AND a.type = 1
        AND a.del_flag = 1
        ) id2,
        CONCAT(
        ifnull(
        (
        SELECT
        1
        FROM
        t_auth a
        WHERE
        a.store_id = t.id
        AND a.result = 2
        AND a.type = 1
        AND a.del_flag = 1
        ),
        ''
        ),
        ifnull(
        (
        SELECT
        2
        FROM
        t_highend_auth a
        WHERE
        a.store_id = t.id
        AND a.`status` > 3
        AND a.del_flag = 1
        ),
        ''
        ),
        ifnull(
        (
        SELECT
        3
        FROM
        t_auth a
        WHERE
        a.store_id = t.id
        AND a.result = 2
        AND a.type = 3
        AND a.del_flag = 1
        ),
        ''
        )
        ) authType,
        (
        SELECT
        IFNULL(sum(o.amount), 0)
        FROM
        t_order o
        WHERE
        o.store_id = t.id
        AND o.`status` = 4
        ) orderAmount,
        (
        SELECT
        IFNULL(sum(o.amount), 0)
        FROM
        t_order o
        WHERE
        o.store_id = t.id
        AND o. STATUS = 4
        ) hasReceivedAmount,
        (
        SELECT
        count(*)
        FROM
        t_order o
        WHERE
        o.store_id = t.id
        AND o.first_status = 2
        ) orderNum
        FROM
        t_store t
        WHERE t.del_flag = 1
        <if test="param!=null and param.beginTime != null ">
            and t.create_time >= #{param.beginTime}
        </if>
        <if test="param!=null and param.endTime != null ">
            and t.create_time &lt; #{param.endTime}
        </if>
        <if test="param!=null and param.nickname != null  and param.nickname != ''">
            and t.nickname like concat('%', #{param.nickname}, '%')
        </if>
        <if test="param!=null and param.name != null  and param.name != ''">
            and t.name like concat('%', #{param.name}, '%')
        </if>
        <if test="param!=null and param.authTypeList != null and param.authTypeList.size()>0">
            AND(
            <foreach item="authSin" collection="param.authTypeList" open=" " separator="OR" close=" ">
                <if test="authSin ==1">
                    EXISTS (
                    SELECT
                    1
                    FROM
                    t_auth a
                    WHERE
                    a.store_id = t.id
                    AND a.result = 2
                    AND a.type = 1
                    AND a.del_flag = 1
                    )
                </if>
                <if test="authSin ==2">
                    EXISTS (
                    SELECT
                    1
                    FROM
                    t_highend_auth a
                    WHERE
                    a.store_id = t.id
                    AND a.`status` > 3
                    AND a.del_flag = 1
                    )
                </if>
                <if test="authSin ==3">
                    EXISTS (
                    SELECT
                    1
                    FROM
                    t_auth a
                    WHERE
                    a.store_id = t.id
                    AND a.result = 2
                    AND a.type = 3
                    AND a.del_flag = 1
                    )
                </if>
            </foreach>
            )
        </if>
        ORDER BY
        <foreach item="orderSort" collection="param.orderSortParamList" open=" " separator="," close=" ">
            ${orderSort.column} ${orderSort.sort}
        </foreach>
    </select>
    <select id="saleCharts" resultType="com.ruoyi.dataanalysis.model.result.SaleChartsResult">
        SELECT
        t.id,
        t.nickname,
        t.mobile,
        t.create_time,
        t.income commission,
        t.invite_num customerNum
        FROM
        t_sale t
        WHERE  t.del_flag = 1
        <if test="param!=null and param.beginTime != null ">
            and t.create_time >= #{param.beginTime}
        </if>
        <if test="param!=null and param.endTime != null ">
            and t.create_time &lt; #{param.endTime}
        </if>
        <if test="param!=null and param.nickname != null  and param.nickname != ''">
            and t.nickname like concat('%', #{param.nickname}, '%')
        </if>
        ORDER BY
        <foreach item="orderSort" collection="param.orderSortParamList" open=" " separator="," close=" ">
            ${orderSort.column} ${orderSort.sort}
        </foreach>
    </select>
</mapper>