<?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.xxfc.platform.order.mapper.OrderReceivedStatisticsMapper">

    <!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.xxfc.platform.order.entity.OrderReceivedStatistics" id="orderReceivedStatisticsMap">
        <result property="id" column="id"/>
        <result property="year" column="year"/>
        <result property="month" column="month"/>
        <result property="date" column="date"/>
        <result property="weekOfYear" column="week_of_year"/>
        <result property="breakRulesRegulationAmount" column="break_rules_regulation_amount"/>
        <result property="companyName" column="company_name"/>
        <result property="depositAmount" column="deposit_amount"/>
        <result property="depositRefundAmount" column="deposit_refund_amount"/>
        <result property="extraAmount" column="extra_amount"/>
        <result property="lateFeeAmount" column="late_fee_amount"/>
        <result property="lossSpecifiedAmount" column="loss_specified_amount"/>
        <result property="memberAmount" column="member_amount"/>
        <result property="travelAmount" column="travel_amount"/>
        <result property="rentVehicleAmount" column="rent_vehicle_amount"/>
        <result property="noDeductibleAmount" column="no_deductible_amount"/>
        <result property="noDeductibleRefundAmount" column="no_deductible_refund_amount"/>
        <result property="orderRefundAmount" column="order_refund_amount"/>
        <result property="totalAmount" column="total_amount"/>
        <result property="totalQuantity" column="total_quantity"/>
        <result property="orderOrigin" column="order_origin"/>
        <result property="payWay" column="pay_way"/>
        <result property="companyId" column="company_id"/>
        <result property="crtTime" column="crt_time"/>
    </resultMap>

    <select id="selectOrderReceivedStatisticsDateList"
            resultType="com.xxfc.platform.order.entity.OrderReceivedStatistics">
        select `year`,`date`,IFNULL(sum(`total_amount`-`order_refund_amount`),0) as
        `totalAmount`,IFNULL(sum(`total_quantity`),0) as `totalQuantity` from `order_received_statistics` where 1=1
        <if test="orderState!=null">
            and `has_pay`=#{orderState}
        </if>
        <if test="orderOrigin!=null">
            and `order_origin`=#{orderOrigin}
        </if>
        <if test="payWay!=null">
            and `pay_way`=#{payWay}
        </if>

        <if test="startDate!=null and endDate!=null">
            and `date` between #{startDate} and #{endDate}
        </if>
        <if test="startDate!=null and endDate==null">
            and <![CDATA[
                `date` >= #{startDate}
            ]]>
        </if>
        <if test="startDate==null and endDate!=null">
            and <![CDATA[
                `date` <= #{endDate}
            ]]>
        </if>
        group by `year`, `date`
        order by null
    </select>

    <select id="selectOrderReceivedStatisticsMonthList"
            resultType="com.xxfc.platform.order.entity.OrderReceivedStatistics">
        select `year`,`month`,IFNULL(sum(`total_amount`-`order_refund_amount`),0) as
        `totalAmount`,IFNULL(sum(`total_quantity`),0) as `totalQuantity` from `order_received_statistics` where 1=1
        <if test="orderState!=null">
            and `has_pay`=#{orderState}
        </if>
        <if test="orderOrigin!=null">
            and `order_origin`=#{orderOrigin}
        </if>
        <if test="payWay!=null">
            and `pay_way`=#{payWay}
        </if>

        <if test="startDate!=null and endDate!=null">
            and `date` between #{startDate} and #{endDate}
        </if>
        <if test="startDate!=null and endDate==null">
            and <![CDATA[
                `date` >= #{startDate}
            ]]>
        </if>
        <if test="startDate==null and endDate!=null">
            and <![CDATA[
                `date` <= #{endDate}
            ]]>
        </if>
        group by `year`, `month`
        order by null
    </select>

    <select id="selectOrderReceivedStatisticsWeekOfYearList"
            resultType="com.xxfc.platform.order.entity.OrderReceivedStatistics">
        select `year`,`week_of_year`,IFNULL(sum(`total_amount`-`order_refund_amount`),0) as
        `totalAmount`,IFNULL(sum(`total_quantity`),0) as `totalQuantity` from `order_received_statistics` where 1=1
        <if test="orderState!=null">
            and `has_pay`=#{orderState}
        </if>
        <if test="orderOrigin!=null">
            and `order_origin`=#{orderOrigin}
        </if>
        <if test="payWay!=null">
            and `pay_way`=#{payWay}
        </if>

        <if test="startDate!=null and endDate!=null">
            and `date` between #{startDate} and #{endDate}
        </if>
        <if test="startDate!=null and endDate==null">
            and <![CDATA[
                `date` >= #{startDate}
            ]]>
        </if>
        <if test="startDate==null and endDate!=null">
            and <![CDATA[
                `date` <= #{endDate}
            ]]>
        </if>
        group by `year`, `week_of_year`
        order by null
    </select>

    <!--按日统计-->
    <select id="selectCompanyPerformanceWithDay" resultType="com.xxfc.platform.order.bo.CompanyPerformanceBo">
        SELECT
        ors.*,
        IFNULL(ovss.departureNum,0) as `departureNum`,
        IFNULL(ovss.rentDays,0) as `rentDays`,
        IFNULL(ovss.arrivalNum,0) as `arrivalNum`
        FROM
        (
        SELECT
        `company_id` AS `companyId`,
        `year`,
        `date`,
        SUM(`member_amount`-`member_refund_amount`) AS `memberAmount`,
        SUM(`travel_amount`-`travel_refund_amount` ) AS `travelAmount`,
        SUM(`rent_vehicle_amount`-`rent_vehicle_refund_amount`) AS `rentVehilceAmount`,
        SUM(`no_deductible_amount` - `no_deductible_refund_amount`) as `noDeductibleAmount`,
        SUM(`deposit_amount` - `deposit_refund_amount`) as `depositAmount`,
        SUM(`deposit_refund_amount`) as `depositRefundAmount`,
        SUM(`loss_specified_amount`) as `lossSpecifiedAmount`,
        SUM(`late_fee_amount`)  as `lateFeeAmount`
        FROM
        `order_received_statistics` WHERE `has_pay`=1
        <if test="companyName!=null and companyName!=''">
            AND `company_name` LIKE CONCAT('%',#{companyName},'%')
        </if>
        <if test="companyId!=null">
            AND `company_id`=#{companyId}
        </if>
        <if test="startDate!=null and endDate!=null">
            AND `date` BETWEEN #{startDate} AND #{endDate}
        </if>

        <if test="startDate!=null and endDate==null">
            AND <![CDATA[
                `date` >= #{startDate}
             ]]>
        </if>

        <if test="startDate==null and endDate!=null">
            AND <![CDATA[
                `date` <= #{startDate}
             ]]>
        </if>
        GROUP BY
        `company_id` ,
        `year`,
        `date`
        order by `company_id`,`date` desc
        <if test="page!=null and limit!=null">
            limit #{startIndex},#{endInex}
        </if>
        ) AS `ors`
        LEFT JOIN (
        SELECT
        `company_id` AS `cyid`,
        `count_date`,
        IFNULL(SUM( `departure_num` ),0) AS `departureNum`,
        IFNULL(SUM( `arrival_num` ),0) AS `arrivalNum`,
        IFNULL(SUM( `rent_num` ),0) AS `rentDays`
        FROM
        `order_vehicle_service_statistics` WHERE 1=1
        <if test="companyId!=null">
            AND `company_id`=#{companyId}
        </if>
        <if test="startDate!=null and endDate!=null">
            AND `count_date` BETWEEN #{startDate} AND #{endDate}
        </if>

        <if test="startDate!=null and endDate==null">
            AND <![CDATA[
                `count_date` >= #{startDate}
             ]]>
        </if>
        <if test="startDate==null and endDate!=null">
            AND <![CDATA[
                `count_date` <= #{startDate}
             ]]>
        </if>
        GROUP BY
        `company_id`,
        `count_year`,
        `count_date`
        ) AS `ovss` ON ovss.cyid = ors.companyId
        AND ovss.count_date = ors.date
        <!-- order by ors.rentVehilceAmount desc,ors.memberAmount desc,ors.travelAmount desc,ors.depositAmount desc,ors.noDeductibleAmount desc ,rentDays desc,departureNum desc, arrivalNum desc-->
    </select>

    <!--按月统计-->
    <select id="selectCompanyPerformanceWithMonth"
            resultType="com.xxfc.platform.order.bo.CompanyPerformanceBo">
        SELECT
        ors.*,
        IFNULL(ovss.departureNum,0) as `departureNum`,
        IFNULL(ovss.rentDays,0) as `rentDays`,
        IFNULL(ovss.arrivalNum,0) as `arrivalNum`
        FROM
        (
        SELECT
        company_id AS `companyId`,
        `year`,
        `month`,
        SUM(`member_amount`-`member_refund_amount`) AS `memberAmount`,
        SUM(`travel_amount`-`travel_refund_amount` ) AS `travelAmount`,
        SUM(`rent_vehicle_amount`-`rent_vehicle_refund_amount`) AS `rentVehilceAmount`,
        SUM(`no_deductible_amount` - `no_deductible_refund_amount`) as `noDeductibleAmount`,
        SUM(`deposit_amount` - `deposit_refund_amount`) as `depositAmount`,
        SUM(`deposit_refund_amount`) as `depositRefundAmount`,
        SUM(`loss_specified_amount`) as `lossSpecifiedAmount`,
        SUM(`late_fee_amount`)  as `lateFeeAmount`
        FROM
        `order_received_statistics` WHERE `has_pay`=1
        <if test="companyName!=null and companyName!=''">
            AND `company_name` LIKE CONCAT('%',#{companyName},'%')
        </if>
        <if test="companyId!=null">
            AND `company_id`=#{companyId}
        </if>
        <if test="startDate!=null and endDate!=null">
            AND `date` BETWEEN #{startDate} AND #{endDate}
        </if>

        <if test="startDate!=null and endDate==null">
            AND <![CDATA[
                `date` >= #{startDate}
             ]]>
        </if>

        <if test="startDate==null and endDate!=null">
            AND <![CDATA[
                `date` <= #{startDate}
             ]]>
        </if>
        GROUP BY
        company_id,
        `year`,
        `month`
        order by `company_id`, `month` desc
        <if test="page!=null and limit!=null">
            limit #{startIndex},#{endInex}
        </if>
        ) AS `ors`
        LEFT JOIN (
        SELECT
        `company_id` AS `cyid`,
        `count_month`,
        COALESCE(SUM( `departure_num` ),0) AS `departureNum`,
        COALESCE(SUM( `arrival_num` ),0) AS `arrivalNum`,
        COALESCE(SUM( `rent_num` ),0) AS `rentDays`
        FROM
        `order_vehicle_service_statistics` WHERE 1=1
        <if test="companyId!=null">
            AND `company_id`=#{companyId}
        </if>
        <if test="startDate!=null and endDate!=null">
            AND `count_date` BETWEEN #{startDate} AND #{endDate}
        </if>

        <if test="startDate!=null and endDate==null">
            AND <![CDATA[
                `count_date` >= #{startDate}
             ]]>
        </if>
        <if test="startDate==null and endDate!=null">
            AND <![CDATA[
                `count_date` <= #{startDate}
             ]]>
        </if>
        GROUP BY
        `company_id`,
        `count_year`,
        `count_month`
        ) AS `ovss` ON ovss.cyid = ors.companyId
        AND ovss.count_month = ors.month
        <!--order by ors.rentVehilceAmount desc,ors.memberAmount desc,ors.travelAmount desc,ors.depositAmount desc,ors.noDeductibleAmount desc ,rentDays desc,departureNum desc, arrivalNum desc-->
    </select>

    <!--按周统计-->
    <select id="selectCompanyPerformanceWithWeek" resultType="com.xxfc.platform.order.bo.CompanyPerformanceBo">
        SELECT
        ors.*,
        IFNULL(ovss.departureNum,0) as `departureNum`,
        IFNULL(ovss.rentDays,0) as `rentDays`,
        IFNULL(ovss.arrivalNum,0) as `arrivalNum`
        FROM
        (
        SELECT
        company_id AS `companyId`,
        `year`,
        `week_of_year` AS `weekOfYear`,
        SUM(`member_amount`-`member_refund_amount`) AS `memberAmount`,
        SUM(`travel_amount`-`travel_refund_amount` ) AS `travelAmount`,
        SUM(`rent_vehicle_amount`-`rent_vehicle_refund_amount`) AS `rentVehilceAmount`,
        SUM(`no_deductible_amount` - `no_deductible_refund_amount`) as `noDeductibleAmount`,
        SUM(`deposit_amount` - `deposit_refund_amount`) as `depositAmount`,
        SUM(`deposit_refund_amount`) as `depositRefundAmount`,
        SUM(`loss_specified_amount`) as `lossSpecifiedAmount`,
        SUM(`late_fee_amount`)  as `lateFeeAmount`
        FROM
        `order_received_statistics` WHERE `has_pay`=1
        <if test="companyName!=null and companyName!=''">
            AND `company_name` LIKE CONCAT('%',#{companyName},'%')
        </if>
        <if test="companyId!=null">
            AND `company_id`=#{companyId}
        </if>
        <if test="startDate!=null and endDate!=null">
            AND `date` BETWEEN #{startDate} AND #{endDate}
        </if>

        <if test="startDate!=null and endDate==null">
            AND <![CDATA[
                `date` >= #{startDate}
             ]]>
        </if>

        <if test="startDate==null and endDate!=null">
            AND <![CDATA[
                `date` <= #{startDate}
             ]]>
        </if>
        GROUP BY
        company_id,
        `year`,
        `week_of_year`
        order by `company_id`,`week_of_year` desc
        <if test="page!=null and limit!=null">
            limit #{startIndex},#{endInex}
        </if>
        ) AS `ors`
        LEFT JOIN (
        SELECT
        `company_id` AS `cyid`,
        `count_week`,
        IFNULL(SUM( `departure_num` ),0) AS `departureNum`,
        IFNULL(SUM( `arrival_num` ),0) AS `arrivalNum`,
        IFNULL(SUM( `rent_num` ),0) AS `rentDays`
        FROM
        `order_vehicle_service_statistics`
        WHERE 1=1
        <if test="companyId!=null">
            AND `company_id`=#{companyId}
        </if>
        <if test="startDate!=null and endDate!=null">
            AND `count_date` BETWEEN #{startDate} AND #{endDate}
        </if>

        <if test="startDate!=null and endDate==null">
            AND <![CDATA[
                `count_date` >= #{startDate}
             ]]>
        </if>
        <if test="startDate==null and endDate!=null">
            AND <![CDATA[
                `count_date` <= #{startDate}
             ]]>
        </if>
        GROUP BY
        `company_id`,
        `count_year`,
        `count_week`
        ) AS `ovss` ON ovss.cyid = ors.companyId
        AND ovss.count_week = ors.weekOfYear
        <!-- order by ors.rentVehilceAmount desc,ors.memberAmount desc,ors.travelAmount desc,ors.depositAmount desc,ors.noDeductibleAmount desc ,rentDays desc,departureNum desc, arrivalNum desc-->
    </select>

    <select id="selectCountByDate" resultType="int">
        select count(*) from (SELECT `date` FROM `order_received_statistics` WHERE `has_pay`=1
        <if test="companyName!=null and companyName!=''">
            AND `company_name` LIKE CONCAT('%',#{companyName},'%')
        </if>
        <if test="companyId!=null">
            AND `company_id`=#{companyId}
        </if>
        <if test="startDate!=null and endDate!=null">
            AND `date` BETWEEN #{startDate} AND #{endDate}
        </if>
        <if test="startDate!=null and endDate==null">
            AND <![CDATA[
                `date` >= #{startDate}
             ]]>
        </if>
        <if test="startDate==null and endDate!=null">
            AND <![CDATA[
                `date` <= #{startDate}
             ]]>
        </if>
        GROUP BY
        company_id,
        `year`,
        `date`
        ) as `ds`
    </select>

    <select id="selectCountByMonth" resultType="int">
        select count(*) from (SELECT `month` FROM `order_received_statistics` WHERE `has_pay`=1
        <if test="companyName!=null and companyName!=''">
            AND `company_name` LIKE CONCAT('%',#{companyName},'%')
        </if>
        <if test="companyId!=null">
            AND `company_id`=#{companyId}
        </if>
        <if test="startDate!=null and endDate!=null">
            AND `date` BETWEEN #{startDate} AND #{endDate}
        </if>
        <if test="startDate!=null and endDate==null">
            AND <![CDATA[
                `date` >= #{startDate}
             ]]>
        </if>
        <if test="startDate==null and endDate!=null">
            AND <![CDATA[
                `date` <= #{startDate}
             ]]>
        </if>
        GROUP BY
        company_id,
        `year`,
        `month`
        ) as `ds`
    </select>

    <select id="selectCountByWeekOfYear" resultType="int">
        select count(*) from (SELECT `week_of_year` FROM `order_received_statistics` WHERE `has_pay`=1
        <if test="companyName!=null and companyName!=''">
            AND `company_name` LIKE CONCAT('%',#{companyName},'%')
        </if>
        <if test="companyId!=null">
            AND `company_id`=#{companyId}
        </if>
        <if test="startDate!=null and endDate!=null">
            AND `date` BETWEEN #{startDate} AND #{endDate}
        </if>
        <if test="startDate!=null and endDate==null">
            AND <![CDATA[
                `date` >= #{startDate}
             ]]>
        </if>
        <if test="startDate==null and endDate!=null">
            AND <![CDATA[
                `date` <= #{startDate}
             ]]>
        </if>
        GROUP BY
        company_id,
        `year`,
        `week_of_year`
        ) as `ds`
    </select>

    <select id="findOrderReceivedStatisticsByDate" resultType="com.xxfc.platform.order.entity.OrderReceivedStatistics">
       select
        `company_id` as `companyId`,`date`,
        sum((`total_amount`+`deposit_amount`+`no_deductible_amount`)) as `totalAmount`,
        sum(`total_quantity`) as `totalQuantity`,
        sum((`rent_vehicle_amount`+`deposit_amount`+`no_deductible_amount`)) as `rentVehicleAmount`,
        sum(`rent_vehicle_quantity`) as `rentVehicleQuantity`,
        `pay_way` as `payWay`,
        `company_name` as `companyName` from `order_received_statistics` where `has_pay`=1 and `pay_way`=1
        and `date` between #{startDate} and #{endDate} group by `company_id`,`date`
        union all
        select
        `company_id` as `companyId`,`date`,
        sum((`total_amount`+`deposit_amount`+`no_deductible_amount`)) as `totalAmount`,
        sum(`total_quantity`) as `totalQuantity`,
        sum((`rent_vehicle_amount`+`deposit_amount`+`no_deductible_amount`)) as `rentVehicleAmount`,
        sum(`rent_vehicle_quantity`) as `rentVehicleQuantity`,
        `pay_way` as `payWay`,
        `company_name` as `companyName` from `order_received_statistics` where `has_pay`=1 and `pay_way`=2
        and `date` between #{startDate} and #{endDate} group by `company_id`,`date`
    </select>
</mapper>