<?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.statistics.order.summary.mapper.ReportSumMachineMapper">

    <sql id="dataPermission">
        <if test="'2'.toString() == authPO.roleType.toString()">
            and EXISTS (
            SELECT null
            FROM mgr_role_org it1
            LEFT JOIN mgr_role it2 ON it2.role_id = it1.role_id
            LEFT JOIN mgr_user_role it3 ON it1.role_id = it3.role_id
            WHERE it2.del_flag = 2
            AND it2.role_code = #{authPO.roleCode}
            AND it1.half_select = 1
            AND it3.user_id = #{authPO.userId}
            AND a.org_id = it1.org_id
            )
        </if>
        <if test="'3'.toString() == authPO.roleType.toString()">
            AND (
            EXISTS(
            SELECT NULL
            FROM mgr_role_canteen it1
            LEFT JOIN mgr_role it2 ON it2.role_id = it1.role_id
            LEFT JOIN mgr_user_role it3 ON it1.role_id = it3.role_id
            WHERE it2.del_flag = 2
            AND it2.role_code = #{authPO.roleCode}
            AND it1.half_select = 1
            AND it3.user_id = #{authPO.userId}
            AND (a.canteen_id = it1.data_id)
            )
            OR EXISTS (
            SELECT NULL
            FROM mgr_role_canteen it1
            LEFT JOIN mgr_role it2 ON it2.role_id = it1.role_id
            LEFT JOIN mgr_user_role it3 ON it1.role_id = it3.role_id
            WHERE it2.del_flag = 2
            AND it2.role_code = #{authPO.roleCode}
            AND it1.half_select = 1
            AND it3.user_id = #{authPO.userId}
            AND (a.stall_id = it1.data_id)
            )
            )
        </if>
        <if test="'2'.toString() == authPO.roleType.toString() or '1'.toString() == authPO.roleType.toString()">
            and EXISTS (
            SELECT null
            FROM mgr_role_area it1
            LEFT JOIN mgr_role it2 ON it2.role_id = it1.role_id
            LEFT JOIN mgr_user_role it3 ON it1.role_id = it3.role_id
            WHERE it2.del_flag = 2
            AND it2.role_code = #{authPO.roleCode}
            AND it1.half_select = 1
            AND it3.user_id = #{authPO.userId}
            AND c.area_id = it1.data_id
            )
        </if>
    </sql>

    <sql id="getCustNumParam">
        <where>
            <if test="param.startDate != null">
                <![CDATA[AND o.pay_time >= concat(#{param.startDate},' 00:00:00')]]>
            </if>
            <if test="param.endDate != null">
                <![CDATA[AND o.pay_time <= concat(#{param.endDate},' 23:59:59')]]>
            </if>
            <if test="param.ageTypes != null and param.ageTypes.size() > 0">
                AND o.age_type IN
                <foreach collection="param.ageTypes" item="ageType" separator="," open="(" close=")">
                    #{ageType}
                </foreach>
            </if>
            <if test="param.canteenStallIds != null and param.canteenStallIds.size() > 0">
                <if test="param.treeType == 1">
                    AND o.canteen_id IN
                    <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                        #{canteenStallId}
                    </foreach>
                </if>
                <if test="param.treeType == 2">
                    AND o.stall_id IN
                    <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                        #{canteenStallId}
                    </foreach>
                </if>
            </if>
            <if test="param.orgId != null">
                AND o.org_id = #{param.orgId}
            </if>
            <if test="param.orgIds != null and param.orgIds.size() > 0">
                and (
                <foreach collection="param.orgIds" item="orgId" separator=" or " open=""
                         close="">
                    g.org_full_id like concat('%/',#{orgId},'/%')
                </foreach>
                )
            </if>
            <if test="param.holidayTypeList != null and param.holidayTypeList.size() > 0">
                and EXISTS (
                SELECT
                1
                FROM
                mkt_holiday h
                WHERE
                DATE_FORMAT(o.pay_time,'%Y-%m-%d') = h.hol_date
                AND h.hol_type in
                <foreach collection="param.holidayTypeList" item="holidayType" separator="," open="(" close=")">
                    #{holidayType}
                </foreach>
                )
            </if>

            <if test="'2'.toString() == authPO.roleType.toString()">
                and EXISTS (
                SELECT null
                FROM mgr_role_org it1
                LEFT JOIN mgr_role it2 ON it2.role_id = it1.role_id
                LEFT JOIN mgr_user_role it3 ON it1.role_id = it3.role_id
                WHERE it2.del_flag = 2
                AND it2.role_code = #{authPO.roleCode}
                AND it1.half_select = 1
                AND it3.user_id = #{authPO.userId}
                AND o.org_id = it1.org_id
                )
            </if>
            <if test="'3'.toString() == authPO.roleType.toString()">
                AND (
                EXISTS(
                SELECT NULL
                FROM mgr_role_canteen it1
                LEFT JOIN mgr_role it2 ON it2.role_id = it1.role_id
                LEFT JOIN mgr_user_role it3 ON it1.role_id = it3.role_id
                WHERE it2.del_flag = 2
                AND it2.role_code = #{authPO.roleCode}
                AND it1.half_select = 1
                AND it3.user_id = #{authPO.userId}
                AND (o.canteen_id = it1.data_id)
                )
                OR EXISTS (
                SELECT NULL
                FROM mgr_role_canteen it1
                LEFT JOIN mgr_role it2 ON it2.role_id = it1.role_id
                LEFT JOIN mgr_user_role it3 ON it1.role_id = it3.role_id
                WHERE it2.del_flag = 2
                AND it2.role_code = #{authPO.roleCode}
                AND it1.half_select = 1
                AND it3.user_id = #{authPO.userId}
                AND (o.stall_id = it1.data_id)
                )
                )
            </if>
            <if test="param.areaIdList != null and param.areaIdList.size() > 0">
                AND c.area_id in
                <foreach collection="param.areaIdList" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
            <if test="'2'.toString() == authPO.roleType.toString() or '1'.toString() == authPO.roleType.toString()">
                and EXISTS (
                SELECT null
                FROM mgr_role_area it1
                LEFT JOIN mgr_role it2 ON it2.role_id = it1.role_id
                LEFT JOIN mgr_user_role it3 ON it1.role_id = it3.role_id
                WHERE it2.del_flag = 2
                AND it2.role_code = #{authPO.roleCode}
                AND it1.half_select = 1
                AND it3.user_id = #{authPO.userId}
                AND c.area_id = it1.data_id
                )
            </if>
        </where>

    </sql>

    <select id="listSummaryMealtime_COUNT" resultType="long">
        SELECT count(*) FROM
        (
            SELECT
                a.machine_sn,
                a.source_type

        FROM report_sum_mealtime a
            LEFT JOIN cust_org g on g.org_id = a.org_id
            <if test="(param.areaIdList != null and param.areaIdList.size() > 0)
                or ('2'.toString() == authPO.roleType.toString() or '1'.toString() == authPO.roleType.toString())">
                LEFT JOIN alloc_canteen c ON c.canteen_id = a.canteen_id
            </if>
            <where>
                <if test="param.startDate != null">
                    <![CDATA[AND a.statistic_date >= #{param.startDate}]]>
                </if>
                <if test="param.endDate != null">
                    <![CDATA[AND a.statistic_date <= #{param.endDate}]]>
                </if>
                <if test="param.ageTypes != null and param.ageTypes.size() > 0">
                    AND a.age_type IN
                    <foreach collection="param.ageTypes" item="ageType" separator="," open="(" close=")">
                        #{ageType}
                    </foreach>
                </if>
                <if test="param.canteenId != null">
                    AND a.canteen_id = #{param.canteenId}
                </if>
                <if test="param.stallId != null">
                    AND a.stall_id = #{param.stallId}
                </if>
                <if test="param.areaIdList != null and param.areaIdList.size() > 0">
                    AND c.area_id in
                    <foreach collection="param.areaIdList" item="item" open="(" close=")" separator=",">
                        #{item}
                    </foreach>
                </if>
                <if test="param.canteenStallIds != null and param.canteenStallIds.size() > 0">
                    <if test="param.treeType == 1">
                        AND a.canteen_id IN
                        <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                            #{canteenStallId}
                        </foreach>
                    </if>
                    <if test="param.treeType == 2">
                        AND a.stall_id IN
                        <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                            #{canteenStallId}
                        </foreach>
                    </if>
                </if>
                <if test="param.orgId != null">
                    AND a.org_id = #{param.orgId}
                </if>
                <if test="param.orgIds != null and param.orgIds.size() > 0">
                    and (
                    <foreach collection="param.orgIds" item="orgId" separator=" or " open=""
                             close="">
                        g.org_full_id like concat('%/',#{orgId},'/%')
                    </foreach>
                    )
                </if>
                <if test="param.machineName != null and param.machineName != ''">
                    AND m.device_name like concat('%', #{param.machineName}, '%')
                </if>
                <if test="param.sourceType != null">
                    AND a.source_type = #{param.sourceType}
                </if>
                <if test="param.holidayTypeList != null and param.holidayTypeList.size() > 0">
                    and EXISTS (
                    SELECT
                    1
                    FROM
                    mkt_holiday h
                    WHERE
                    a.statistic_date = h.hol_date
                    AND h.hol_type in
                    <foreach collection="param.holidayTypeList" item="holidayType" separator="," open="(" close=")">
                        #{holidayType}
                    </foreach>
                    )
                </if>

                <include refid="dataPermission"></include>
            </where>
        GROUP BY
        a.machine_sn,
        source_type
        ) t

    </select>

    <select id="listSummaryMealtimeByDay_COUNT" resultType="long">
        select count(*) from
        (
            SELECT
                daydate.statistic_date AS statistic_date,
                t.machine_sn,
                t.source_type
                FROM
            (
                SELECT
                    a.statistic_date,
                    a.machine_sn,
                    a.source_type

            FROM report_sum_mealtime a
                LEFT JOIN cust_org g on g.org_id = a.org_id
                <if test="(param.areaIdList != null and param.areaIdList.size() > 0)
                or ('2'.toString() == authPO.roleType.toString() or '1'.toString() == authPO.roleType.toString())">
                    LEFT JOIN alloc_canteen c ON c.canteen_id = a.canteen_id
                </if>
                <where>
                    <if test="param.startDate != null">
                        <![CDATA[AND a.statistic_date >= #{param.startDate}]]>
                    </if>
                    <if test="param.endDate != null">
                        <![CDATA[AND a.statistic_date <= #{param.endDate}]]>
                    </if>
                    <if test="param.ageTypes != null and param.ageTypes.size() > 0">
                        AND a.age_type IN
                        <foreach collection="param.ageTypes" item="ageType" separator="," open="(" close=")">
                            #{ageType}
                        </foreach>
                    </if>
                    <if test="param.canteenId != null">
                        AND a.canteen_id = #{param.canteenId}
                    </if>
                    <if test="param.stallId != null">
                        AND a.stall_id = #{param.stallId}
                    </if>
                    <if test="param.areaIdList != null and param.areaIdList.size() > 0">
                        AND c.area_id in
                        <foreach collection="param.areaIdList" item="item" open="(" close=")" separator=",">
                            #{item}
                        </foreach>
                    </if>
                    <if test="param.canteenStallIds != null and param.canteenStallIds.size() > 0">
                        <if test="param.treeType == 1">
                            AND a.canteen_id IN
                            <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                                #{canteenStallId}
                            </foreach>
                        </if>
                        <if test="param.treeType == 2">
                            AND a.stall_id IN
                            <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                                #{canteenStallId}
                            </foreach>
                        </if>
                    </if>
                    <if test="param.orgId != null">
                        AND a.org_id = #{param.orgId}
                    </if>
                    <if test="param.orgIds != null and param.orgIds.size() > 0">
                        and (
                        <foreach collection="param.orgIds" item="orgId" separator=" or " open=""
                                 close="">
                            g.org_full_id like concat('%/',#{orgId},'/%')
                        </foreach>
                        )
                    </if>
                    <if test="param.machineName != null and param.machineName != ''">
                        AND m.device_name like concat('%', #{param.machineName}, '%')
                    </if>
                    <if test="param.sourceType != null">
                        AND a.source_type = #{param.sourceType}
                    </if>
                    <if test="param.holidayTypeList != null and param.holidayTypeList.size() > 0">
                        and EXISTS (
                        SELECT
                        1
                        FROM
                        mkt_holiday h
                        WHERE
                        a.statistic_date = h.hol_date
                        AND h.hol_type in
                        <foreach collection="param.holidayTypeList" item="holidayType" separator="," open="(" close=")">
                            #{holidayType}
                        </foreach>
                        )
                    </if>

                    <include refid="dataPermission"></include>
                </where>
            GROUP BY
            statistic_date,
            a.machine_sn,
            source_type
            ) t
            RIGHT JOIN (
                    SELECT
                        date_add(
                            date_sub(#{param.startDate}, INTERVAL 1 DAY ), INTERVAL (CAST(custom_id AS signed INTEGER)) DAY) statistic_date

                            FROM
                                menu_custom_dict
                            WHERE
                                custom_id <![CDATA[ <= ]]> DATEDIFF(#{param.endDate},
                                date_sub( #{param.startDate}, INTERVAL 1 DAY ))
                ) daydate ON daydate.statistic_date = t.statistic_date
        ) t
    </select>

    <select id="listSummaryMealtimeByDay_COUNT" resultType="long" databaseId="kb">
        select count(*) from
        (
            SELECT
                daydate.statistic_date AS statistic_date,
                t.machine_sn,
                t.source_type
                FROM
            (
                SELECT
                    a.statistic_date,
                    a.machine_sn,
                    a.source_type

            FROM report_sum_mealtime a
                LEFT JOIN cust_org g on g.org_id = a.org_id
                <if test="(param.areaIdList != null and param.areaIdList.size() > 0)
                or ('2'.toString() == authPO.roleType.toString() or '1'.toString() == authPO.roleType.toString())">
                    LEFT JOIN alloc_canteen c ON c.canteen_id = a.canteen_id
                </if>
                <where>
                    <if test="param.startDate != null">
                        <![CDATA[AND a.statistic_date >= #{param.startDate}]]>
                    </if>
                    <if test="param.endDate != null">
                        <![CDATA[AND a.statistic_date <= #{param.endDate}]]>
                    </if>
                    <if test="param.ageTypes != null and param.ageTypes.size() > 0">
                        AND a.age_type IN
                        <foreach collection="param.ageTypes" item="ageType" separator="," open="(" close=")">
                            #{ageType}
                        </foreach>
                    </if>
                    <if test="param.canteenId != null">
                        AND a.canteen_id = #{param.canteenId}
                    </if>
                    <if test="param.stallId != null">
                        AND a.stall_id = #{param.stallId}
                    </if>
                    <if test="param.areaIdList != null and param.areaIdList.size() > 0">
                        AND c.area_id in
                        <foreach collection="param.areaIdList" item="item" open="(" close=")" separator=",">
                            #{item}
                        </foreach>
                    </if>
                    <if test="param.canteenStallIds != null and param.canteenStallIds.size() > 0">
                        <if test="param.treeType == 1">
                            AND a.canteen_id IN
                            <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                                #{canteenStallId}
                            </foreach>
                        </if>
                        <if test="param.treeType == 2">
                            AND a.stall_id IN
                            <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                                #{canteenStallId}
                            </foreach>
                        </if>
                    </if>
                    <if test="param.orgId != null">
                        AND a.org_id = #{param.orgId}
                    </if>
                    <if test="param.orgIds != null and param.orgIds.size() > 0">
                        and (
                        <foreach collection="param.orgIds" item="orgId" separator=" or " open=""
                                 close="">
                            g.org_full_id like concat('%/',#{orgId},'/%')
                        </foreach>
                        )
                    </if>
                    <if test="param.machineName != null and param.machineName != ''">
                        AND m.device_name like concat('%', #{param.machineName}, '%')
                    </if>
                    <if test="param.sourceType != null">
                        AND a.source_type = #{param.sourceType}
                    </if>
                    <if test="param.holidayTypeList != null and param.holidayTypeList.size() > 0">
                        and EXISTS (
                        SELECT
                        1
                        FROM
                        mkt_holiday h
                        WHERE
                        a.statistic_date = h.hol_date
                        AND h.hol_type in
                        <foreach collection="param.holidayTypeList" item="holidayType" separator="," open="(" close=")">
                            #{holidayType}
                        </foreach>
                        )
                    </if>

                    <include refid="dataPermission"></include>
                </where>
            GROUP BY
            statistic_date,
            a.machine_sn,
            source_type
            ) t
            RIGHT JOIN (
                    SELECT
                    (TO_DATE(#{param.startDate}, 'YYYY-MM-DD') + ROWNUM - 1) as statistic_date
                    FROM
                    DUAL
                    CONNECT BY
                    ROWNUM <![CDATA[<=]]> TO_DATE(#{param.endDate}, 'YYYY-MM-DD') - TO_DATE(#{param.startDate}, 'YYYY-MM-DD') + 1
                ) daydate ON daydate.statistic_date = t.statistic_date
        ) t
    </select>

    <select id="listSummaryMealtime" resultType="com.bonus.canteen.core.report.statistics.order.summary.vo.ReportSumMachineMealTimeVO">
        <include refid="baseMealtimeSummary"></include>
    </select>

    <sql id="baseMealtimeSummary">
        SELECT
            t.machine_sn
            , IFNULL(IFNULL(m.device_name, s.device_name), '') as consume_source
            , IFNULL(IFNULL(m.device_num, s.device_num), '') device_num
            , t.source_type
            , sum(if(t.mealtime_type = 1, t.consume_num, 0)) AS breakfast_consume_num
            , sum(if(t.mealtime_type = 1, t.total_amount, 0)) AS breakfast_real_amount

            , sum(if(t.mealtime_type = 1, t.payable_amount, 0)) AS breakfast_payable_amount
            , sum(if(t.mealtime_type = 1, t.discount_amount, 0)) AS breakfast_discount_amount
            , sum(if(t.mealtime_type = 1, t.refund_amount, 0)) AS breakfast_refund_amount


            , sum(if(t.mealtime_type = 2, t.consume_num, 0)) AS lunch_consume_num
            , sum(if(t.mealtime_type = 2, t.total_amount, 0)) AS lunch_real_amount

            , sum(if(t.mealtime_type = 2, t.payable_amount, 0)) AS lunch_payable_amount
            , sum(if(t.mealtime_type = 2, t.discount_amount, 0)) AS lunch_discount_amount
            , sum(if(t.mealtime_type = 2, t.refund_amount, 0)) AS lunch_refund_amount


            , sum(if(t.mealtime_type = 3, t.consume_num, 0)) AS afternoon_consume_num
            , sum(if(t.mealtime_type = 3, t.total_amount, 0)) AS afternoon_real_amount

            , sum(if(t.mealtime_type = 3, t.payable_amount, 0)) AS afternoon_payable_amount
            , sum(if(t.mealtime_type = 3, t.discount_amount, 0)) AS afternoon_discount_amount
            , sum(if(t.mealtime_type = 3, t.refund_amount, 0)) AS afternoon_refund_amount


            , sum(if(t.mealtime_type = 4, t.consume_num, 0)) AS dinner_consume_num
            , sum(if(t.mealtime_type = 4, t.total_amount, 0)) AS dinner_real_amount

            , sum(if(t.mealtime_type = 4, t.payable_amount, 0)) AS dinner_payable_amount
            , sum(if(t.mealtime_type = 4, t.discount_amount, 0)) AS dinner_discount_amount
            , sum(if(t.mealtime_type = 4, t.refund_amount, 0)) AS dinner_refund_amount


            , sum(if(t.mealtime_type = 5, t.consume_num, 0)) AS supper_consume_num
            , sum(if(t.mealtime_type = 5, t.total_amount, 0)) AS supper_real_amount

            , sum(if(t.mealtime_type = 5, t.payable_amount, 0)) AS supper_payable_amount
            , sum(if(t.mealtime_type = 5, t.discount_amount, 0)) AS supper_discount_amount
            , sum(if(t.mealtime_type = 5, t.refund_amount, 0)) AS supper_refund_amount


            , sum(if(t.mealtime_type = -1, t.consume_num, 0)) AS other_consume_num
            , sum(if(t.mealtime_type = -1, t.total_amount, 0)) AS other_real_amount

            , sum(if(t.mealtime_type = -1, t.payable_amount, 0)) AS other_payable_amount
            , sum(if(t.mealtime_type = -1, t.discount_amount, 0)) AS other_discount_amount
            , sum(if(t.mealtime_type = -1, t.refund_amount, 0)) AS other_refund_amount


            , sum(IFNULL(t.consume_num,0)) AS total_consume_num
            , sum(IFNULL(t.total_amount,0)) AS total_real_amount


            , sum(IFNULL(t.payable_amount,0)) AS total_payable_amount
            , sum(IFNULL(t.discount_amount,0)) AS total_discount_amount
            , sum(IFNULL(t.refund_amount,0)) AS total_refund_amount
        FROM (
        SELECT t.* FROM
        (
            SELECT
                a.source_type,
                a.machine_sn,
                a.mealtime_type,
                SUM(a.consume_num) consume_num,
                SUM(a.total_amount) total_amount,
                SUM(a.payable_amount) payable_amount,
                SUM(a.discount_amount) discount_amount,
                SUM(a.refund_amount) refund_amount
        FROM report_sum_mealtime a
            LEFT JOIN cust_org g on g.org_id = a.org_id
            <if test="(param.areaIdList != null and param.areaIdList.size() > 0)
                or ('2'.toString() == authPO.roleType.toString() or '1'.toString() == authPO.roleType.toString())">
                LEFT JOIN alloc_canteen c ON c.canteen_id = a.canteen_id
            </if>
            <where>
                <if test="param.startDate != null">
                    <![CDATA[AND a.statistic_date >= #{param.startDate}]]>
                </if>
                <if test="param.endDate != null">
                    <![CDATA[AND a.statistic_date <= #{param.endDate}]]>
                </if>
                <if test="param.ageTypes != null and param.ageTypes.size() > 0">
                    AND a.age_type IN
                    <foreach collection="param.ageTypes" item="ageType" separator="," open="(" close=")">
                        #{ageType}
                    </foreach>
                </if>
                <if test="param.canteenId != null">
                    AND a.canteen_id = #{param.canteenId}
                </if>
                <if test="param.stallId != null">
                    AND a.stall_id = #{param.stallId}
                </if>
                <if test="param.areaIdList != null and param.areaIdList.size() > 0">
                    AND c.area_id in
                    <foreach collection="param.areaIdList" item="item" open="(" close=")" separator=",">
                        #{item}
                    </foreach>
                </if>
                <if test="param.canteenStallIds != null and param.canteenStallIds.size() > 0">
                    <if test="param.treeType == 1">
                        AND a.canteen_id IN
                        <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                            #{canteenStallId}
                        </foreach>
                    </if>
                    <if test="param.treeType == 2">
                        AND a.stall_id IN
                        <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                            #{canteenStallId}
                        </foreach>
                    </if>
                </if>
                <if test="param.orgId != null">
                    AND a.org_id = #{param.orgId}
                </if>
                <if test="param.orgIds != null and param.orgIds.size() > 0">
                    and (
                    <foreach collection="param.orgIds" item="orgId" separator=" or " open=""
                             close="">
                        g.org_full_id like concat('%/',#{orgId},'/%')
                    </foreach>
                    )
                </if>
                <if test="param.machineName != null and param.machineName != ''">
                    AND m.device_name like concat('%', #{param.machineName}, '%')
                </if>
                <if test="param.sourceType != null">
                    AND a.source_type = #{param.sourceType}
                </if>
                <if test="param.holidayTypeList != null and param.holidayTypeList.size() > 0">
                    and EXISTS (
                    SELECT
                    1
                    FROM
                    mkt_holiday h
                    WHERE
                    a.statistic_date = h.hol_date
                    AND h.hol_type in
                    <foreach collection="param.holidayTypeList" item="holidayType" separator="," open="(" close=")">
                        #{holidayType}
                    </foreach>
                    )
                </if>

                <include refid="dataPermission"></include>
            </where>
        GROUP BY
        a.machine_sn,
        source_type,
        mealtime_type
        ) t
        ) t
        LEFT JOIN device_info m ON t.machine_sn = m.device_sn
        LEFT JOIN supermarket_device s ON t.machine_sn = s.device_sn
        group by
            t.machine_sn,
            consume_source,
            device_num,
            source_type
    </sql>

    <select id="listSummaryMealtimeByDay" resultType="com.bonus.canteen.core.report.statistics.order.summary.vo.ReportSumMachineMealTimeVO">
        SELECT
            daydate.statistic_date AS statistic_date,
            A.machine_sn,
            A.consume_source,
            IFNULL( A.device_num, '-' ) AS device_num,
            IFNULL( A.source_type, - 1 ) AS source_type,

            IFNULL( A.breakfast_consume_num, 0 ) AS breakfast_consume_num,
            IFNULL( A.breakfast_real_amount, 0 ) AS breakfast_real_amount,
            IFNULL( A.breakfast_payable_amount, 0 ) AS breakfast_payable_amount,
            IFNULL( A.breakfast_discount_amount, 0 ) AS breakfast_discount_amount,
            IFNULL( A.breakfast_refund_amount, 0 ) AS breakfast_refund_amount,


            IFNULL( A.lunch_consume_num, 0 ) AS lunch_consume_num,
            IFNULL( A.lunch_real_amount, 0 ) AS lunch_real_amount,
            IFNULL( A.lunch_payable_amount, 0 ) AS lunch_payable_amount,
            IFNULL( A.lunch_discount_amount, 0 ) AS lunch_discount_amount,
            IFNULL( A.lunch_refund_amount, 0 ) AS lunch_refund_amount,


            IFNULL( A.afternoon_consume_num, 0 ) AS afternoon_consume_num,
            IFNULL( A.afternoon_real_amount, 0 ) AS afternoon_real_amount,
            IFNULL( A.afternoon_payable_amount, 0 ) AS afternoon_payable_amount,
            IFNULL( A.afternoon_discount_amount, 0 ) AS afternoon_discount_amount,
            IFNULL( A.afternoon_refund_amount, 0 ) AS afternoon_refund_amount,


            IFNULL( A.dinner_consume_num, 0 ) AS dinner_consume_num,
            IFNULL( A.dinner_real_amount, 0 ) AS dinner_real_amount,
            IFNULL( A.dinner_payable_amount, 0 ) AS dinner_payable_amount,
            IFNULL( A.dinner_discount_amount, 0 ) AS dinner_discount_amount,
            IFNULL( A.dinner_refund_amount, 0 ) AS dinner_refund_amount,


            IFNULL( A.supper_consume_num, 0 ) AS supper_consume_num,
            IFNULL( A.supper_real_amount, 0 ) AS supper_real_amount,
            IFNULL( A.supper_payable_amount, 0 ) AS supper_payable_amount,
            IFNULL( A.supper_discount_amount, 0 ) AS supper_discount_amount,
            IFNULL( A.supper_refund_amount, 0 ) AS supper_refund_amount,


            IFNULL( A.other_consume_num, 0 ) AS other_consume_num,
            IFNULL( A.other_real_amount, 0 ) AS other_real_amount,
            IFNULL( A.other_payable_amount, 0 ) AS other_payable_amount,
            IFNULL( A.other_discount_amount, 0 ) AS other_discount_amount,
            IFNULL( A.other_refund_amount, 0 ) AS other_refund_amount,


            IFNULL( A.total_consume_num, 0 ) AS total_consume_num,
            IFNULL( A.total_real_amount, 0 ) AS total_real_amount,
            IFNULL( A.total_payable_amount, 0 ) AS total_payable_amount,
            IFNULL( A.total_discount_amount, 0 ) AS total_discount_amount,
            IFNULL( A.total_refund_amount, 0 ) AS total_refund_amount
        FROM
            (
            SELECT
                t.statistic_date
                , t.machine_sn
                , IFNULL(IFNULL(m.device_name, s.device_name), '') as consume_source
                , IFNULL(IFNULL(m.device_num, s.device_num), '') device_num
                , t.source_type
                , sum(if(t.mealtime_type = 1, t.consume_num, 0)) AS breakfast_consume_num
                , sum(if(t.mealtime_type = 1, t.total_amount, 0)) AS breakfast_real_amount

                , sum(if(t.mealtime_type = 1, t.payable_amount, 0)) AS breakfast_payable_amount
                , sum(if(t.mealtime_type = 1, t.discount_amount, 0)) AS breakfast_discount_amount
                , sum(if(t.mealtime_type = 1, t.refund_amount, 0)) AS breakfast_refund_amount


                , sum(if(t.mealtime_type = 2, t.consume_num, 0)) AS lunch_consume_num
                , sum(if(t.mealtime_type = 2, t.total_amount, 0)) AS lunch_real_amount

                , sum(if(t.mealtime_type = 2, t.payable_amount, 0)) AS lunch_payable_amount
                , sum(if(t.mealtime_type = 2, t.discount_amount, 0)) AS lunch_discount_amount
                , sum(if(t.mealtime_type = 2, t.refund_amount, 0)) AS lunch_refund_amount


                , sum(if(t.mealtime_type = 3, t.consume_num, 0)) AS afternoon_consume_num
                , sum(if(t.mealtime_type = 3, t.total_amount, 0)) AS afternoon_real_amount

                , sum(if(t.mealtime_type = 3, t.payable_amount, 0)) AS afternoon_payable_amount
                , sum(if(t.mealtime_type = 3, t.discount_amount, 0)) AS afternoon_discount_amount
                , sum(if(t.mealtime_type = 3, t.refund_amount, 0)) AS afternoon_refund_amount


                , sum(if(t.mealtime_type = 4, t.consume_num, 0)) AS dinner_consume_num
                , sum(if(t.mealtime_type = 4, t.total_amount, 0)) AS dinner_real_amount

                , sum(if(t.mealtime_type = 4, t.payable_amount, 0)) AS dinner_payable_amount
                , sum(if(t.mealtime_type = 4, t.discount_amount, 0)) AS dinner_discount_amount
                , sum(if(t.mealtime_type = 4, t.refund_amount, 0)) AS dinner_refund_amount


                , sum(if(t.mealtime_type = 5, t.consume_num, 0)) AS supper_consume_num
                , sum(if(t.mealtime_type = 5, t.total_amount, 0)) AS supper_real_amount

                , sum(if(t.mealtime_type = 5, t.payable_amount, 0)) AS supper_payable_amount
                , sum(if(t.mealtime_type = 5, t.discount_amount, 0)) AS supper_discount_amount
                , sum(if(t.mealtime_type = 5, t.refund_amount, 0)) AS supper_refund_amount


                , sum(if(t.mealtime_type = -1, t.consume_num, 0)) AS other_consume_num
                , sum(if(t.mealtime_type = -1, t.total_amount, 0)) AS other_real_amount

                , sum(if(t.mealtime_type = -1, t.payable_amount, 0)) AS other_payable_amount
                , sum(if(t.mealtime_type = -1, t.discount_amount, 0)) AS other_discount_amount
                , sum(if(t.mealtime_type = -1, t.refund_amount, 0)) AS other_refund_amount


                , sum(t.consume_num) AS total_consume_num
                , sum(t.total_amount) AS total_real_amount


                , sum(t.payable_amount) AS total_payable_amount
                , sum(t.discount_amount) AS total_discount_amount
                , sum(t.refund_amount) AS total_refund_amount
            FROM
                (
                SELECT t.* FROM
                (
                SELECT
                    a.statistic_date,
                    a.machine_sn,
                    a.source_type,
                    a.mealtime_type,
                    SUM(a.consume_num) consume_num,
                    SUM(a.total_amount) total_amount,
                    SUM(a.payable_amount) payable_amount,
                    SUM(a.discount_amount) discount_amount,
                    SUM(a.refund_amount) refund_amount
        FROM
                    report_sum_mealtime a
                    LEFT JOIN cust_org g on a.org_id = g.org_id
                    <if test="(param.areaIdList != null and param.areaIdList.size() > 0)
                        or ('2'.toString() == authPO.roleType.toString() or '1'.toString() == authPO.roleType.toString())">
                        LEFT JOIN alloc_canteen c ON c.canteen_id = a.canteen_id
                    </if>
                    <where>
                        <if test="param.startDate != null">
                            <![CDATA[AND a.statistic_date >= #{param.startDate}]]>
                        </if>
                        <if test="param.endDate != null">
                            <![CDATA[AND a.statistic_date <= #{param.endDate}]]>
                        </if>
                        <if test="param.ageTypes != null and param.ageTypes.size() > 0">
                            AND a.age_type IN
                            <foreach collection="param.ageTypes" item="ageType" separator="," open="(" close=")">
                                #{ageType}
                            </foreach>
                        </if>
                        <if test="param.canteenId != null">
                            AND a.canteen_id = #{param.canteenId}
                        </if>
                        <if test="param.stallId != null">
                            AND a.stall_id = #{param.stallId}
                        </if>
                        <if test="param.areaIdList != null and param.areaIdList.size() > 0">
                            AND c.area_id in
                            <foreach collection="param.areaIdList" item="item" open="(" close=")" separator=",">
                                #{item}
                            </foreach>
                        </if>
                        <if test="param.canteenStallIds != null and param.canteenStallIds.size() > 0">
                            <if test="param.treeType == 1">
                                AND a.canteen_id IN
                                <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                                    #{canteenStallId}
                                </foreach>
                            </if>
                            <if test="param.treeType == 2">
                                AND a.stall_id IN
                                <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                                    #{canteenStallId}
                                </foreach>
                            </if>
                        </if>
                        <if test="param.orgId != null">
                            AND a.org_id = #{param.orgId}
                        </if>
                        <if test="param.orgIds != null and param.orgIds.size() > 0">
                            and (
                            <foreach collection="param.orgIds" item="orgId" separator=" or " open=""
                                     close="">
                                g.org_full_id like concat('%/',#{orgId},'/%')
                            </foreach>
                            )
                        </if>
                        <if test="param.machineName != null and param.machineName != ''">
                            AND m.device_name like concat('%', #{param.machineName}, '%')
                        </if>
                        <if test="param.sourceType != null">
                            AND a.source_type = #{param.sourceType}
                        </if>
                        <if test="param.holidayTypeList != null and param.holidayTypeList.size() > 0">
                            and EXISTS (
                            SELECT
                            1
                            FROM
                            mkt_holiday h
                            WHERE
                            a.statistic_date = h.hol_date
                            AND h.hol_type in
                            <foreach collection="param.holidayTypeList" item="holidayType" separator="," open="(" close=")">
                                #{holidayType}
                            </foreach>
                            )
                        </if>

                        <include refid="dataPermission"></include>
                    </where>
                GROUP BY
                statistic_date,
                a.machine_sn,
                source_type,
                mealtime_type
            ) t

                    ) t
                LEFT JOIN device_info m ON t.machine_sn = m.device_sn
                LEFT JOIN supermarket_device s ON t.machine_sn = s.device_sn
                GROUP BY
                    statistic_date,
                    t.machine_sn,
                    consume_source,
                    device_num,
                    source_type
                ) A
                RIGHT JOIN (
                SELECT
                    date_add(
                        date_sub(#{param.startDate}, INTERVAL 1 DAY ), INTERVAL (CAST(custom_id AS signed INTEGER)) DAY) statistic_date

                        FROM
                            menu_custom_dict
                        WHERE
                            custom_id <![CDATA[ <= ]]> DATEDIFF(#{param.endDate},
                            date_sub( #{param.startDate}, INTERVAL 1 DAY ))
            ) daydate ON daydate.statistic_date = A.statistic_date
    </select>

    <select id="listSummaryMealtimeByDay" resultType="com.bonus.canteen.core.report.statistics.order.summary.vo.ReportSumMachineMealTimeVO" databaseId="kb">
        SELECT
        daydate.statistic_date AS statistic_date,
        A.consume_source,
        IFNULL( A.deviceNum, '-' ) AS deviceNum,
        IFNULL( A.source_type, - 1 ) AS source_type,

        IFNULL( A.breakfast_cust_num, 0 ) AS breakfast_cust_num,
        IFNULL( A.breakfast_consume_num, 0 ) AS breakfast_consume_num,
        IFNULL( A.breakfast_real_amount, 0 ) AS breakfast_real_amount,
        IFNULL( A.breakfast_payable_amount, 0 ) AS breakfast_payable_amount,
        IFNULL( A.breakfast_discount_amount, 0 ) AS breakfast_discount_amount,
        IFNULL( A.breakfast_refund_amount, 0 ) AS breakfast_refund_amount,


        IFNULL( A.lunch_cust_num, 0 ) AS lunch_cust_num,
        IFNULL( A.lunch_consume_num, 0 ) AS lunch_consume_num,
        IFNULL( A.lunch_real_amount, 0 ) AS lunch_real_amount,
        IFNULL( A.lunch_payable_amount, 0 ) AS lunch_payable_amount,
        IFNULL( A.lunch_discount_amount, 0 ) AS lunch_discount_amount,
        IFNULL( A.lunch_refund_amount, 0 ) AS lunch_refund_amount,


        IFNULL( A.afternoon_cust_num, 0 ) AS afternoon_cust_num,
        IFNULL( A.afternoon_consume_num, 0 ) AS afternoon_consume_num,
        IFNULL( A.afternoon_real_amount, 0 ) AS afternoon_real_amount,
        IFNULL( A.afternoon_payable_amount, 0 ) AS afternoon_payable_amount,
        IFNULL( A.afternoon_discount_amount, 0 ) AS afternoon_discount_amount,
        IFNULL( A.afternoon_refund_amount, 0 ) AS afternoon_refund_amount,


        IFNULL( A.dinner_cust_num, 0 ) AS dinner_cust_num,
        IFNULL( A.dinner_consume_num, 0 ) AS dinner_consume_num,
        IFNULL( A.dinner_real_amount, 0 ) AS dinner_real_amount,
        IFNULL( A.dinner_payable_amount, 0 ) AS dinner_payable_amount,
        IFNULL( A.dinner_discount_amount, 0 ) AS dinner_discount_amount,
        IFNULL( A.dinner_refund_amount, 0 ) AS dinner_refund_amount,


        IFNULL( A.supper_cust_num, 0 ) AS supper_cust_num,
        IFNULL( A.supper_consume_num, 0 ) AS supper_consume_num,
        IFNULL( A.supper_real_amount, 0 ) AS supper_real_amount,
        IFNULL( A.supper_payable_amount, 0 ) AS supper_payable_amount,
        IFNULL( A.supper_discount_amount, 0 ) AS supper_discount_amount,
        IFNULL( A.supper_refund_amount, 0 ) AS supper_refund_amount,


        IFNULL( A.other_cust_num, 0 ) AS other_cust_num,
        IFNULL( A.other_consume_num, 0 ) AS other_consume_num,
        IFNULL( A.other_real_amount, 0 ) AS other_real_amount,
        IFNULL( A.other_payable_amount, 0 ) AS other_payable_amount,
        IFNULL( A.other_discount_amount, 0 ) AS other_discount_amount,
        IFNULL( A.other_refund_amount, 0 ) AS other_refund_amount,


        IFNULL( A.total_cust_num, 0 ) AS total_cust_num,
        IFNULL( A.total_consume_num, 0 ) AS total_consume_num,
        IFNULL( A.total_real_amount, 0 ) AS total_real_amount,
        IFNULL( A.total_payable_amount, 0 ) AS total_payable_amount,
        IFNULL( A.total_discount_amount, 0 ) AS total_discount_amount,
        IFNULL( A.total_refund_amount, 0 ) AS total_refund_amount
        FROM
        (
        SELECT
        t.statistic_date,
        t.consume_source,
        t.deviceNum,
        t.source_type
        , sum(if(t.mealtime_type = 1, t.cust_num, 0)) AS breakfast_cust_num
        , sum(if(t.mealtime_type = 1, t.consume_num, 0)) AS breakfast_consume_num
        , sum(if(t.mealtime_type = 1, t.total_amount, 0)) AS breakfast_real_amount

        , sum(if(t.mealtime_type = 1, t.payable_amount, 0)) AS breakfast_payable_amount
        , sum(if(t.mealtime_type = 1, t.discount_amount, 0)) AS breakfast_discount_amount
        , sum(if(t.mealtime_type = 1, t.refund_amount, 0)) AS breakfast_refund_amount


        , sum(if(t.mealtime_type = 2, t.cust_num, 0)) AS lunch_cust_num
        , sum(if(t.mealtime_type = 2, t.consume_num, 0)) AS lunch_consume_num
        , sum(if(t.mealtime_type = 2, t.total_amount, 0)) AS lunch_real_amount

        , sum(if(t.mealtime_type = 2, t.payable_amount, 0)) AS lunch_payable_amount
        , sum(if(t.mealtime_type = 2, t.discount_amount, 0)) AS lunch_discount_amount
        , sum(if(t.mealtime_type = 2, t.refund_amount, 0)) AS lunch_refund_amount


        , sum(if(t.mealtime_type = 3, t.cust_num, 0)) AS afternoon_cust_num
        , sum(if(t.mealtime_type = 3, t.consume_num, 0)) AS afternoon_consume_num
        , sum(if(t.mealtime_type = 3, t.total_amount, 0)) AS afternoon_real_amount

        , sum(if(t.mealtime_type = 3, t.payable_amount, 0)) AS afternoon_payable_amount
        , sum(if(t.mealtime_type = 3, t.discount_amount, 0)) AS afternoon_discount_amount
        , sum(if(t.mealtime_type = 3, t.refund_amount, 0)) AS afternoon_refund_amount


        , sum(if(t.mealtime_type = 4, t.cust_num, 0)) AS dinner_cust_num
        , sum(if(t.mealtime_type = 4, t.consume_num, 0)) AS dinner_consume_num
        , sum(if(t.mealtime_type = 4, t.total_amount, 0)) AS dinner_real_amount

        , sum(if(t.mealtime_type = 4, t.payable_amount, 0)) AS dinner_payable_amount
        , sum(if(t.mealtime_type = 4, t.discount_amount, 0)) AS dinner_discount_amount
        , sum(if(t.mealtime_type = 4, t.refund_amount, 0)) AS dinner_refund_amount


        , sum(if(t.mealtime_type = 5, t.cust_num, 0)) AS supper_cust_num
        , sum(if(t.mealtime_type = 5, t.consume_num, 0)) AS supper_consume_num
        , sum(if(t.mealtime_type = 5, t.total_amount, 0)) AS supper_real_amount

        , sum(if(t.mealtime_type = 5, t.payable_amount, 0)) AS supper_payable_amount
        , sum(if(t.mealtime_type = 5, t.discount_amount, 0)) AS supper_discount_amount
        , sum(if(t.mealtime_type = 5, t.refund_amount, 0)) AS supper_refund_amount


        , sum(if(t.mealtime_type = -1, t.cust_num, 0)) AS other_cust_num
        , sum(if(t.mealtime_type = -1, t.consume_num, 0)) AS other_consume_num
        , sum(if(t.mealtime_type = -1, t.total_amount, 0)) AS other_real_amount

        , sum(if(t.mealtime_type = -1, t.payable_amount, 0)) AS other_payable_amount
        , sum(if(t.mealtime_type = -1, t.discount_amount, 0)) AS other_discount_amount
        , sum(if(t.mealtime_type = -1, t.refund_amount, 0)) AS other_refund_amount


        , sum(t.cust_num) AS total_cust_num
        , sum(t.consume_num) AS total_consume_num
        , sum(t.total_amount) AS total_real_amount


        , sum(t.payable_amount) AS total_payable_amount
        , sum(t.discount_amount) AS total_discount_amount
        , sum(t.refund_amount) AS total_refund_amount
        FROM
        (
        SELECT t.*,a.cust_num FROM
        (
        SELECT
        a.statistic_date,
        IFNULL(IFNULL(m.device_name, s.device_name),'') AS consume_source,
        IFNULL(IFNULL(m.device_num, s.device_num),'') AS deviceNum,
        a.source_type,
        a.mealtime_type,
        SUM(a.consume_num) consume_num,
        SUM(a.total_amount) total_amount,
        SUM(a.payable_amount) payable_amount,
        SUM(a.discount_amount) discount_amount,
        SUM(a.refund_amount) refund_amount
        FROM
        report_sum_mealtime a
        LEFT JOIN device_info m ON a.machine_sn = m.device_sn
        LEFT JOIN supermarket_device s on a.machine_sn = s.device_sn
        LEFT JOIN cust_org g on a.org_id = g.org_id
        LEFT JOIN alloc_canteen c ON c.canteen_id = a.canteen_id
        <where>
            <if test="param.startDate != null">
                <![CDATA[AND a.statistic_date >= #{param.startDate}]]>
            </if>
            <if test="param.endDate != null">
                <![CDATA[AND a.statistic_date <= #{param.endDate}]]>
            </if>
            <if test="param.ageTypes != null and param.ageTypes.size() > 0">
                AND a.age_type IN
                <foreach collection="param.ageTypes" item="ageType" separator="," open="(" close=")">
                    #{ageType}
                </foreach>
            </if>
            <if test="param.canteenId != null">
                AND a.canteen_id = #{param.canteenId}
            </if>
            <if test="param.stallId != null">
                AND a.stall_id = #{param.stallId}
            </if>
            <if test="param.areaIdList != null and param.areaIdList.size() > 0">
                AND c.area_id in
                <foreach collection="param.areaIdList" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
            <if test="param.canteenStallIds != null and param.canteenStallIds.size() > 0">
                <if test="param.treeType == 1">
                    AND a.canteen_id IN
                    <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                        #{canteenStallId}
                    </foreach>
                </if>
                <if test="param.treeType == 2">
                    AND a.stall_id IN
                    <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                        #{canteenStallId}
                    </foreach>
                </if>
            </if>
            <if test="param.orgId != null">
                AND a.org_id = #{param.orgId}
            </if>
            <if test="param.orgIds != null and param.orgIds.size() > 0">
                and (
                <foreach collection="param.orgIds" item="orgId" separator=" or " open=""
                         close="">
                    g.org_full_id like concat('%/',#{orgId},'/%')
                </foreach>
                )
            </if>
            <if test="param.machineName != null and param.machineName != ''">
                AND m.device_name like concat('%', #{param.machineName}, '%')
            </if>
            <if test="param.sourceType != null">
                AND a.source_type = #{param.sourceType}
            </if>
            <if test="param.holidayTypeList != null and param.holidayTypeList.size() > 0">
                and EXISTS (
                SELECT
                1
                FROM
                mkt_holiday h
                WHERE
                a.statistic_date = h.hol_date
                AND h.hol_type in
                <foreach collection="param.holidayTypeList" item="holidayType" separator="," open="(" close=")">
                    #{holidayType}
                </foreach>
                )
            </if>

            <include refid="dataPermission"></include>
        </where>
        GROUP BY
        statistic_date,
        consume_source,
        deviceNum,
        source_type,
        mealtime_type
        ) t LEFT JOIN
        (
        SELECT
        DATE_FORMAT(o.pay_time,'%Y-%m-%d') statistic_date,
        IFNULL(IFNULL(m.device_name, s.device_name),'') AS consume_source,
        IFNULL(IFNULL(m.device_num, s.device_num),'') AS deviceNum,
        o.source_type,
        o.mealtime_type,
        count(DISTINCT o.cust_id) cust_num
        FROM report_order_info o
        LEFT JOIN cust_org g on g.org_id = o.org_id
        LEFT JOIN device_info m on o.machine_sn = m.device_sn
        LEFT JOIN supermarket_device s on o.machine_sn = s.device_sn

        LEFT JOIN alloc_canteen c ON c.canteen_id = o.canteen_id
        <include refid="getCustNumParam"></include>
        GROUP BY
        statistic_date,
        consume_source,
        deviceNum,
        source_type,
        mealtime_type
        ) a ON a.consume_source = t.consume_source
        AND a.deviceNum = t.deviceNum
        AND a.source_type = t.source_type
        AND a.mealtime_type = t.mealtime_type
        AND a.statistic_date = t.statistic_date

        ) t
        GROUP BY
        statistic_date,
        consume_source,
        deviceNum,
        source_type
        ) A
        RIGHT JOIN (
        SELECT
        (TO_DATE(#{param.startDate}, 'YYYY-MM-DD') + ROWNUM - 1) as statistic_date
        FROM
        DUAL
        CONNECT BY
        ROWNUM <![CDATA[<=]]> TO_DATE(#{param.endDate}, 'YYYY-MM-DD') - TO_DATE(#{param.startDate}, 'YYYY-MM-DD') + 1
        ) daydate ON daydate.statistic_date = A.statistic_date
    </select>

    <select id="getSummaryMealtimeTotal" resultType="com.bonus.canteen.core.report.statistics.order.summary.vo.ReportSumMachineMealTimeVO">
        SELECT
             sum(breakfast_consume_num) AS breakfast_consume_num
            , sum(breakfast_real_amount) AS breakfast_real_amount
            , sum(lunch_consume_num) AS lunch_consume_num
            , sum(lunch_real_amount) AS lunch_real_amount
            , sum(afternoon_consume_num) AS afternoon_consume_num
            , sum(afternoon_real_amount) AS afternoon_real_amount
            , sum(dinner_consume_num) AS dinner_consume_num
            , sum(dinner_real_amount) AS dinner_real_amount
            , sum(supper_consume_num) AS supper_consume_num
            , sum(supper_real_amount) AS supper_real_amount
            , sum(other_consume_num) AS other_consume_num
            , sum(other_real_amount) AS other_real_amount

            ,sum(total_consume_num) total_consume_num,
            sum(total_real_amount) total_real_amount
        FROM
        (
        <include refid="baseMealtimeSummary"></include>
        ) t

    </select>


    <select id="getReportOrderSumForDevice" resultType="com.bonus.canteen.core.report.api.vo.ReportOrderSumVO">
        SELECT
            IFNULL( SUM( consume_num ), 0 ) AS totalCount,
            IFNULL( SUM( total_amount ), 0 ) AS totalAmount
        FROM
            report_sum_mealtime a
        WHERE
            a.statistic_date BETWEEN #{param.startDate} AND #{param.endDate}
        <if test="param.machineSn != null and param.machineSn.trim() != ''">
            AND a.machine_sn = #{param.machineSn}
        </if>
        <if test="param.mealtimeType != null">
            AND a.mealtime_type = #{param.mealtimeType}
        </if>

    </select>

    <select id="listSummary_COUNT" resultType="long">
        SELECT
            count(*)
        FROM
            (
            SELECT
                IFNULL( IFNULL( m.device_name, s.device_name ), '' ) AS device_name,
                IFNULL( IFNULL( m.device_num, s.device_num ), '' ) AS deviceNum,
                a.source_type
            FROM
                report_sum_mealtime a
                LEFT JOIN device_info m ON a.machine_sn = m.device_sn
                LEFT JOIN supermarket_device s ON a.machine_sn = s.device_sn
                LEFT JOIN cust_org g ON g.org_id = a.org_id
                LEFT JOIN alloc_canteen c ON c.canteen_id = a.canteen_id
        <where>
            <if test="param.startDate != null">
                <![CDATA[AND a.statistic_date >= #{param.startDate}]]>
            </if>
            <if test="param.endDate != null">
                <![CDATA[AND a.statistic_date <= #{param.endDate}]]>
            </if>
            <if test="param.ageTypes != null and param.ageTypes.size() > 0">
                AND a.age_type IN
                <foreach collection="param.ageTypes" item="ageType" separator="," open="(" close=")">
                    #{ageType}
                </foreach>
            </if>
            <if test="param.canteenId != null">
                AND a.canteen_id = #{param.canteenId}
            </if>
            <if test="param.stallId != null">
                AND a.stall_id = #{param.stallId}
            </if>
            <if test="param.areaIdList != null and param.areaIdList.size() > 0">
                AND c.area_id in
                <foreach collection="param.areaIdList" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
            <if test="param.canteenStallIds != null and param.canteenStallIds.size() > 0">
                <if test="param.treeType == 1">
                    AND a.canteen_id IN
                    <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                        #{canteenStallId}
                    </foreach>
                </if>
                <if test="param.treeType == 2">
                    AND a.stall_id IN
                    <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                        #{canteenStallId}
                    </foreach>
                </if>
            </if>
            <if test="param.orgId != null">
                AND a.org_id = #{param.orgId}
            </if>
            <if test="param.orgIds != null and param.orgIds.size() > 0">
                and (
                <foreach collection="param.orgIds" item="orgId" separator=" or " open=""
                         close="">
                    g.org_full_id like concat('%/',#{orgId},'/%')
                </foreach>
                )
            </if>
            <if test="param.machineName != null and param.machineName != ''">
                AND m.device_name like concat('%', #{param.machineName}, '%')
            </if>
            <if test="param.sourceType != null">
                AND a.source_type = #{param.sourceType}
            </if>
            <if test="param.keyword != null and param.keyword != ''">
                AND (
                m.device_name like concat('%', #{param.keyword}, '%')
                or m.device_num like concat('%', #{param.keyword},'%')
                or m.device_sn like concat('%', #{param.keyword},'%')
                )
            </if>
            <if test="param.holidayTypeList != null and param.holidayTypeList.size() > 0">
                and EXISTS (
                SELECT
                1
                FROM
                mkt_holiday h
                WHERE
                a.statistic_date = h.hol_date
                AND h.hol_type in
                <foreach collection="param.holidayTypeList" item="holidayType" separator="," open="(" close=")">
                    #{holidayType}
                </foreach>
                )
            </if>

            <include refid="dataPermission"></include>
        </where>
            GROUP BY
                device_name,
                deviceNum,
                source_type
            ) t
    </select>

    <select id="listSummaryByDay_COUNT" resultType="long">
        SELECT
            count(*)
        FROM
            (
            SELECT
                a.statistic_date,
                IFNULL( IFNULL( m.device_name, s.device_name ), '' ) AS device_name,
                IFNULL( IFNULL( m.device_num, s.device_num ), '' ) AS deviceNum,
                a.source_type
            FROM
                report_sum_mealtime a
                LEFT JOIN device_info m ON a.machine_sn = m.device_sn
                LEFT JOIN supermarket_device s ON a.machine_sn = s.device_sn
                LEFT JOIN cust_org g ON g.org_id = a.org_id
                LEFT JOIN alloc_canteen c ON c.canteen_id = a.canteen_id
        <where>
            <if test="param.startDate != null">
                <![CDATA[AND a.statistic_date >= #{param.startDate}]]>
            </if>
            <if test="param.endDate != null">
                <![CDATA[AND a.statistic_date <= #{param.endDate}]]>
            </if>
            <if test="param.ageTypes != null and param.ageTypes.size() > 0">
                AND a.age_type IN
                <foreach collection="param.ageTypes" item="ageType" separator="," open="(" close=")">
                    #{ageType}
                </foreach>
            </if>
            <if test="param.canteenId != null">
                AND a.canteen_id = #{param.canteenId}
            </if>
            <if test="param.stallId != null">
                AND a.stall_id = #{param.stallId}
            </if>
            <if test="param.areaIdList != null and param.areaIdList.size() > 0">
                AND c.area_id in
                <foreach collection="param.areaIdList" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
            <if test="param.canteenStallIds != null and param.canteenStallIds.size() > 0">
                <if test="param.treeType == 1">
                    AND a.canteen_id IN
                    <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                        #{canteenStallId}
                    </foreach>
                </if>
                <if test="param.treeType == 2">
                    AND a.stall_id IN
                    <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                        #{canteenStallId}
                    </foreach>
                </if>
            </if>
            <if test="param.orgId != null">
                AND a.org_id = #{param.orgId}
            </if>
            <if test="param.orgIds != null and param.orgIds.size() > 0">
                and (
                <foreach collection="param.orgIds" item="orgId" separator=" or " open=""
                         close="">
                    g.org_full_id like concat('%/',#{orgId},'/%')
                </foreach>
                )
            </if>
            <if test="param.machineName != null and param.machineName != ''">
                AND m.device_name like concat('%', #{param.machineName}, '%')
            </if>
            <if test="param.sourceType != null">
                AND a.source_type = #{param.sourceType}
            </if>
            <if test="param.keyword != null and param.keyword != ''">
                AND (
                m.device_name like concat('%', #{param.keyword}, '%')
                or m.device_num like concat('%', #{param.keyword},'%')
                or m.device_sn like concat('%', #{param.keyword},'%')
                )
            </if>
            <if test="param.holidayTypeList != null and param.holidayTypeList.size() > 0">
                and EXISTS (
                SELECT
                1
                FROM
                mkt_holiday h
                WHERE
                a.statistic_date = h.hol_date
                AND h.hol_type in
                <foreach collection="param.holidayTypeList" item="holidayType" separator="," open="(" close=")">
                    #{holidayType}
                </foreach>
                )
            </if>

            <include refid="dataPermission"></include>
        </where>
            GROUP BY
                a.statistic_date,
                device_name,
                deviceNum,
                source_type
            ) t
        RIGHT JOIN (
        SELECT
        date_add( date_sub( #{param.startDate}, INTERVAL 1 DAY ), INTERVAL (CAST(custom_id AS signed INTEGER)) DAY) statistic_date
        FROM
        menu_custom_dict
        WHERE
        custom_id <![CDATA[ <= ]]> DATEDIFF(
        #{param.endDate},
        date_sub( #{param.startDate}, INTERVAL 1 DAY ))
        ) daydate ON daydate.statistic_date = t.statistic_date
    </select>


    <select id="listSummaryByDay_COUNT" resultType="long" databaseId="kb">
        SELECT
            count(*)
        FROM
            (
            SELECT
                a.statistic_date,
                IFNULL( IFNULL( m.device_name, s.device_name ), '' ) AS device_name,
                IFNULL( IFNULL( m.device_num, s.device_num ), '' ) AS deviceNum,
                a.source_type
            FROM
                report_sum_mealtime a
                LEFT JOIN device_info m ON a.machine_sn = m.device_sn
                LEFT JOIN supermarket_device s ON a.machine_sn = s.device_sn
                LEFT JOIN cust_org g ON g.org_id = a.org_id
                LEFT JOIN alloc_canteen c ON c.canteen_id = a.canteen_id
        <where>
            <if test="param.startDate != null">
                <![CDATA[AND a.statistic_date >= #{param.startDate}]]>
            </if>
            <if test="param.endDate != null">
                <![CDATA[AND a.statistic_date <= #{param.endDate}]]>
            </if>
            <if test="param.ageTypes != null and param.ageTypes.size() > 0">
                AND a.age_type IN
                <foreach collection="param.ageTypes" item="ageType" separator="," open="(" close=")">
                    #{ageType}
                </foreach>
            </if>
            <if test="param.canteenId != null">
                AND a.canteen_id = #{param.canteenId}
            </if>
            <if test="param.stallId != null">
                AND a.stall_id = #{param.stallId}
            </if>
            <if test="param.areaIdList != null and param.areaIdList.size() > 0">
                AND c.area_id in
                <foreach collection="param.areaIdList" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
            <if test="param.canteenStallIds != null and param.canteenStallIds.size() > 0">
                <if test="param.treeType == 1">
                    AND a.canteen_id IN
                    <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                        #{canteenStallId}
                    </foreach>
                </if>
                <if test="param.treeType == 2">
                    AND a.stall_id IN
                    <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                        #{canteenStallId}
                    </foreach>
                </if>
            </if>
            <if test="param.orgId != null">
                AND a.org_id = #{param.orgId}
            </if>
            <if test="param.orgIds != null and param.orgIds.size() > 0">
                and (
                <foreach collection="param.orgIds" item="orgId" separator=" or " open=""
                         close="">
                    g.org_full_id like concat('%/',#{orgId},'/%')
                </foreach>
                )
            </if>
            <if test="param.machineName != null and param.machineName != ''">
                AND m.device_name like concat('%', #{param.machineName}, '%')
            </if>
            <if test="param.sourceType != null">
                AND a.source_type = #{param.sourceType}
            </if>
            <if test="param.keyword != null and param.keyword != ''">
                AND (
                m.device_name like concat('%', #{param.keyword}, '%')
                or m.device_num like concat('%', #{param.keyword},'%')
                or m.device_sn like concat('%', #{param.keyword},'%')
                )
            </if>
            <if test="param.holidayTypeList != null and param.holidayTypeList.size() > 0">
                and EXISTS (
                SELECT
                1
                FROM
                mkt_holiday h
                WHERE
                a.statistic_date = h.hol_date
                AND h.hol_type in
                <foreach collection="param.holidayTypeList" item="holidayType" separator="," open="(" close=")">
                    #{holidayType}
                </foreach>
                )
            </if>

            <include refid="dataPermission"></include>
        </where>
            GROUP BY
                a.statistic_date,
                device_name,
                deviceNum,
                source_type
            ) t
        RIGHT JOIN (
        SELECT
        (TO_DATE(#{param.startDate}, 'YYYY-MM-DD') + ROWNUM - 1) as statistic_date,
        0 as idno
        FROM
        DUAL
        CONNECT BY
        ROWNUM <![CDATA[<=]]> TO_DATE(#{param.endDate}, 'YYYY-MM-DD') - TO_DATE(#{param.startDate}, 'YYYY-MM-DD') + 1
        ) daydate ON daydate.statistic_date = t.statistic_date
    </select>

    <select id="listSummary" resultType="com.bonus.canteen.core.report.statistics.order.summary.vo.ReportSumMachineVO">
        <include refid="baseSummary"></include>
    </select>

    <select id="listSummaryByDay"
            resultType="com.bonus.canteen.core.report.statistics.order.summary.vo.ReportSumMachineVO">
        SELECT
            daydate.statistic_date AS statistic_date,
            IFNULL( A.deviceNum, '' ) AS deviceNum,
            IFNULL( A.device_name, '-' ) AS device_name,
            IFNULL( A.source_type, -1 ) AS source_type,
            IFNULL( A.consume_num, 0 ) AS consume_num,
            IFNULL( A.real_amount, 0 ) AS real_amount,
            ifnull( A.refund_amount, 0 ) AS refund_amount,
            ifnull( A.total_amount, 0 ) AS total_amount

            , IFNULL(A.payable_amount, 0) AS payable_amount
            , IFNULL(A.discount_amount, 0) AS discount_amount
        FROM
            (
        <include refid="baseSummary"></include>
        ) A
        RIGHT JOIN (
        SELECT
        date_add( date_sub( #{param.startDate}, INTERVAL 1 DAY ), INTERVAL (CAST(custom_id AS signed INTEGER)) DAY) statistic_date
        FROM
        menu_custom_dict
        WHERE
        custom_id <![CDATA[ <= ]]> DATEDIFF(
        #{param.endDate},
        date_sub( #{param.startDate}, INTERVAL 1 DAY ))
        ) daydate ON daydate.statistic_date = A.statistic_date
        order by daydate.statistic_date desc
    </select>

    <select id="listSummaryByDay"
            resultType="com.bonus.canteen.core.report.statistics.order.summary.vo.ReportSumMachineVO" databaseId="kb">
        SELECT
        daydate.statistic_date AS statistic_date,
        IFNULL( A.deviceNum, '' ) AS deviceNum,
        IFNULL( A.device_name, '-' ) AS device_name,
        IFNULL( A.source_type, -1 ) AS source_type,
        IFNULL( A.cust_num, 0 ) AS cust_num,
        IFNULL( A.consume_num, 0 ) AS consume_num,
        IFNULL( A.real_amount, 0 ) AS real_amount,
        ifnull( A.refund_amount, 0 ) AS refund_amount,
        ifnull( A.total_amount, 0 ) AS total_amount

        , IFNULL(A.payable_amount, 0) AS payable_amount
        , IFNULL(A.discount_amount, 0) AS discount_amount
        FROM
        (
        <include refid="baseSummary"></include>
        ) A
        RIGHT JOIN (
        SELECT
        (TO_DATE(#{param.startDate}, 'YYYY-MM-DD') + ROWNUM - 1) as statistic_date,
        0 as idno
        FROM
        DUAL
        CONNECT BY
        ROWNUM <![CDATA[<=]]> TO_DATE(#{param.endDate}, 'YYYY-MM-DD') - TO_DATE(#{param.startDate}, 'YYYY-MM-DD') + 1
        ) daydate ON daydate.statistic_date = A.statistic_date
        order by daydate.statistic_date desc
    </select>

    <select id="getSummaryTotal"
            resultType="com.bonus.canteen.core.report.statistics.order.summary.vo.ReportSumMachineVO">
        SELECT
            sum(t.consume_num) consume_num,
            sum(t.real_amount) real_amount,
            sum(t.refund_amount) refund_amount,
            sum(t.total_amount) total_amount,
            SUM(t.payable_amount) AS payable_amount,
            SUM(t.discount_amount) AS discount_amount
        FROM (
        <include refid="baseSummary"></include>
        ) t
    </select>

    <sql id="baseSummary">
        SELECT
            t.*
        FROM
            (
            SELECT
                <if test="param.sumType == 2">
                    a.statistic_date,
                </if>
                IFNULL( IFNULL( m.device_name, s.device_name ), '' ) AS device_name,
                IFNULL( IFNULL( m.device_num, s.device_num ), '' ) AS deviceNum,
                a.source_type,
                SUM( a.consume_num ) consume_num,
                SUM( a.total_amount ) total_amount,
                SUM( a.payable_amount ) payable_amount,
                SUM( a.real_amount ) real_amount,
                SUM( a.discount_amount ) discount_amount,
                SUM( a.refund_amount ) refund_amount
            FROM
                report_sum_mealtime a
                LEFT JOIN device_info m ON a.machine_sn = m.device_sn
                LEFT JOIN supermarket_device s ON a.machine_sn = s.device_sn
                LEFT JOIN cust_org g ON g.org_id = a.org_id
                LEFT JOIN alloc_canteen c ON c.canteen_id = a.canteen_id
        <where>
            <if test="param.startDate != null">
                <![CDATA[AND a.statistic_date >= #{param.startDate}]]>
            </if>
            <if test="param.endDate != null">
                <![CDATA[AND a.statistic_date <= #{param.endDate}]]>
            </if>
            <if test="param.ageTypes != null and param.ageTypes.size() > 0">
                AND a.age_type IN
                <foreach collection="param.ageTypes" item="ageType" separator="," open="(" close=")">
                    #{ageType}
                </foreach>
            </if>
            <if test="param.canteenId != null">
                AND a.canteen_id = #{param.canteenId}
            </if>
            <if test="param.stallId != null">
                AND a.stall_id = #{param.stallId}
            </if>
            <if test="param.areaIdList != null and param.areaIdList.size() > 0">
                AND c.area_id in
                <foreach collection="param.areaIdList" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
            <if test="param.canteenStallIds != null and param.canteenStallIds.size() > 0">
                <if test="param.treeType == 1">
                    AND a.canteen_id IN
                    <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                        #{canteenStallId}
                    </foreach>
                </if>
                <if test="param.treeType == 2">
                    AND a.stall_id IN
                    <foreach collection="param.canteenStallIds" item="canteenStallId" separator="," open="(" close=")">
                        #{canteenStallId}
                    </foreach>
                </if>
            </if>
            <if test="param.orgId != null">
                AND a.org_id = #{param.orgId}
            </if>
            <if test="param.orgIds != null and param.orgIds.size() > 0">
                and (
                <foreach collection="param.orgIds" item="orgId" separator=" or " open=""
                         close="">
                    g.org_full_id like concat('%/',#{orgId},'/%')
                </foreach>
                )
            </if>
            <if test="param.machineName != null and param.machineName != ''">
                AND m.device_name like concat('%', #{param.machineName}, '%')
            </if>
            <if test="param.sourceType != null">
                AND a.source_type = #{param.sourceType}
            </if>
            <if test="param.keyword != null and param.keyword != ''">
                AND (
                m.device_name like concat('%', #{param.keyword}, '%')
                or m.device_num like concat('%', #{param.keyword},'%')
                or m.device_sn like concat('%', #{param.keyword},'%')
                )
            </if>
            <if test="param.holidayTypeList != null and param.holidayTypeList.size() > 0">
                and EXISTS (
                SELECT
                1
                FROM
                mkt_holiday h
                WHERE
                a.statistic_date = h.hol_date
                AND h.hol_type in
                <foreach collection="param.holidayTypeList" item="holidayType" separator="," open="(" close=")">
                    #{holidayType}
                </foreach>
                )
            </if>

            <include refid="dataPermission"></include>
        </where>
            GROUP BY
                <if test="param.sumType == 2">
                    a.statistic_date,
                </if>
                device_name,
                deviceNum,
                source_type
            ) t
    </sql>

    <select id="getDeviceCustNum" resultType="int">
        <if test="param.ifTotal == 1">
            select sum( t.cust_num ) cust_num
            from (
        </if>
        SELECT
                <if test="param.ifTotal == 1">
                    <if test="param.sumType == 2">
                        DATE_FORMAT(o.pay_time,'%Y-%m-%d') statistic_date,
                    </if>
                    o.machine_sn,
                </if>
                count( DISTINCT o.cust_id ) cust_num
            FROM
                report_order_info o
                LEFT JOIN cust_org g ON g.org_id = o.org_id
                LEFT JOIN device_info m ON o.machine_sn = m.device_sn
                LEFT JOIN alloc_canteen c ON c.canteen_id = o.canteen_id
                LEFT JOIN supermarket_device s on o.machine_sn = s.device_sn
                <include refid="getCustNumParam"></include>
                <if test="param.machineName != null and param.machineName != ''">
                    AND (m.device_name = #{param.machineName} or s.device_name = #{param.machineName})
                </if>
                <if test="param.sourceType != null">
                    AND o.source_type = #{param.sourceType}
                </if>
                <if test="param.machineNum != null and param.machineNum != ''">
                    AND m.device_num = #{param.machineNum}
                </if>
                <if test="param.statisticDate != null">
                    <![CDATA[AND o.pay_time >= concat(#{param.statisticDate},' 00:00:00')]]>
                     <![CDATA[AND o.pay_time <= concat(#{param.statisticDate},' 23:59:59')]]>
                </if>
        <if test="param.ifTotal == 1">
            group by
            <if test="param.sumType == 2">
                statistic_date,
            </if>
            o.machine_sn
            ) t
        </if>
    </select>

    <select id="getDeviceMealtimeCustNum" resultType="com.bonus.canteen.core.report.statistics.order.summary.param.ReportMealtimeTypeCustNumDTO">
        <if test="param.ifTotal == 1">
            select t.mealtime_type, sum( t.cust_num ) cust_num
            from (
        </if>
        SELECT
            <if test="param.sumType == 2">
                DATE_FORMAT(o.pay_time,'%Y-%m-%d') statistic_date,
            </if>
            o.machine_sn,
            o.mealtime_type,
            count(DISTINCT o.cust_id) cust_num
            FROM report_order_info o
                 LEFT JOIN cust_org g on g.org_id = o.org_id
            <if test="(param.areaIdList != null and param.areaIdList.size() > 0) or
                    ('2'.toString() == authPO.roleType.toString() or '1'.toString() == authPO.roleType.toString())">
               LEFT JOIN alloc_canteen c ON c.canteen_id = o.canteen_id
            </if>
            <include refid="getCustNumParam"></include>
            <if test="param.ifTotal == 2">
                <if test="param.machineSnList != null and param.machineSnList.size() > 0">
                    AND o.machine_sn in
                    <foreach collection="param.machineSnList" item="machineSn" separator="," open="(" close=")">
                        #{machineSn}
                    </foreach>
                </if>
            </if>
            GROUP BY
            <if test="param.sumType == 2">
                statistic_date,
            </if>
            o.machine_sn,
            o.mealtime_type
        <if test="param.ifTotal == 1">
            ) t
            group by t.mealtime_type
        </if>
    </select>
</mapper>
