<?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.winsun.mapper.OrderMapper">

    <resultMap id="broadBandOrder" type="com.winsun.bean.BroadBandOrder">
        <result column="order_number" property="orderNumber"/>
        <result column="xb_order_id" property="xbOrderId"/>
        <result column="order_id" property="orderId"/>
        <result column="order_status" property="orderStatus"/>
        <result column="hehuoren_id" property="hehuorenId"/>
        <result column="package_id" property="packageId"/>
        <result column="customer_name" property="customerName"/>
        <result column="business_number" property="businessNumber"/>
        <result column="contact_number" property="contactNumber"/>
        <result column="id_card" property="idCard"/>
        <result column="business_iccid" property="businessIccid"/>
        <result column="kapin" property="kapin"/>
        <result column="id_cardz" property="idCardz"/>
        <result column="id_cardf" property="idCardf"/>
        <result column="id_cardzs" property="idCardzs"/>
        <result column="studen_card" property="studenCard"/>
        <result column="create_time" property="createTime"/>
        <result column="success_time" property="successTime"/>
        <result column="address" property="address"/>
        <result column="remarks" property="remarks"/>
        <result column="site" property="site"/>
        <result column="msg" property="msg"/>
        <result column="send_type" property="sendType"/>
        <result column="now_package" property="nowPackage"/>
        <result column="business_package" property="businessPackage"/>
        <result column="user_bussiness_type" property="userBussinessType"/>
        <result column="user_type" property="userType"/>
        <result column="accept_result" property="acceptResult"/>
        <result column="accept_name" property="acceptName"/>
        <result column="accept_comment" property="acceptComment"/>
        <result column="accept_date" property="acceptDate"/>
        <result column="yj_count" property="yjCount"/>
        <result column="yj_type" property="yjType"/>
        <result column="tj_type" property="tjType"/>
        <result column="xb_type" property="xbType"/>
        <result column="net_number" property="netNumber"/>
        <result column="net_password" property="netPassword"/>
        <result column="userSchool" property="userSchool"/>
        <result column="check_fail" property="checkFail"/>
        <result column="expenses" property="expenses"/>
        <result column="identifying" property="identifying"/>
        <result column="kd_order_id" property="kdOrderId"/>
        <result column="gift_account" property="giftAccount"/>
        <result column="gift_password" property="giftCypher"/>
        <result column="hehuoren_area" property="hehuorenArea"/>
        <result column="hehuoren_name" property="hehuorenName"/>
        <result column="hehuoren_school" property="hehuorenSchool"/>
        <result column="hehuoren_phone" property="hehuorenPhone"/>
        <result column="supervisor_name" property="supervisorName"/>
        <result column="th_status" property="thStatus"/>
        <result column="rh_time" property="rhTime"/>
        <result column="orderSeq" property="orderSeq"/>
        <result column="productName" property="productName"/>
        <result column="order_set_meal" property="orderSetMeal"/>
        <result column="order_university_name" property="orderUniversityName"/>
        <result column="orderName" property="orderName"/>
        <result column="orderDate" property="orderDate"/>
        <result column="status" property="status"/>
        <result column="order_region" property="orderRegion"/>
        <result column="ipay" property="iPay"/>
        <result column="payType" property="payType"/>
        <result column="webOrderAmount" property="webOrderAmount"/>
        <result column="order_payment_time" property="orderPaymentTime"/>
        <result column="orderReqtranSeq" property="orderReqtranSeq"/>
        <result column="customeRid" property="customerId"/>
        <result column="orderPhone" property="orderPhone"/>
        <result column="order_customer_type" property="orderCustomerType"/>
        <result column="order_customer_student_id" property="orderCustomerStudentId"/>
        <result column="order_customer_remarks" property="orderCustomerRemarks"/>
        <result column="order_customer_account" property="orderCustomerAccount"/>
        <result column="order_customer_pwd" property="orderCustomerPwd"/>
        <result column="uptranseq" property="uptranseq"/>
        <result column="expenses" property="expenses"/>
        <result column="order_rf2" property="orderRf2"></result>
        <result column="orderSeqTHD" property="orderseqThd"/>
    </resultMap>


    <select id="selectBroadBandOrderList" resultMap="broadBandOrder">
        select
        ho.*,
        ov.productName,
        ov.order_set_meal,
        ov.order_university_name,
        ov.orderName,
        ov.ipay,
        ov.payType,
        ov.webOrderAmount,
        ov.order_payment_time,
        ov.orderReqtranSeq,
        ov.customeRid,
        ov.orderPhone,
        ov.order_customer_type,
        ov.order_customer_student_id,
        ov.order_customer_remarks,
        ov.order_customer_account,
        ov.order_customer_pwd,
        ov.uptranseq,
        ov.expenses,
        ov.order_rf2,
        ov.orderSeqTHD,
        ov.order_region,
        ov.orderDate
        from (
        select * from hhr_order
        where
        kd_order_id is not null
        and user_type in (3,5,9,10)
        <if test="createTimeStart != null and createTimeStart != ''">
            and create_time &gt;= #{createTimeStart}
        </if>
        <if test="createTimeEnd != null and createTimeEnd != ''">
            and create_time &lt;= #{createTimeEnd}
        </if>
        <if test="successTimeStart != null and successTimeStart != ''">
            and success_time &gt;= #{successTimeStart}
        </if>
        <if test="successTimeEnd != null and successTimeEnd != ''">
            and success_time &lt;= #{successTimeEnd}
        </if>
        <if test="userType != null and userType != ''">
            and user_type = #{userType}
        </if>
        <if test="hehuorenArea != null and hehuorenArea != ''">
            and hehuoren_area = #{hehuorenArea}
        </if>
        <if test="orderStatus != null and orderStatus != ''">
            and order_status = #{orderStatus}
        </if>
        <if test="hehuorenSchool != null and hehuorenSchool != ''">
            and hehuoren_school = #{hehuorenSchool}
        </if>
        <if test="orderNumber != null and orderNumber != ''">
            and order_number = #{orderNumber}
        </if>
        <if test="hehuorenName != null and hehuorenName != ''">
            and hehuoren_name = #{hehuorenName}
        </if>
        <if test="contactNumber != null and contactNumber != ''">
            and contact_number = #{contactNumber}
        </if>
        <if test="businessNumber != null and businessNumber != ''">
            and business_number = #{businessNumber}
        </if>
        <if test="customerName != null and customerName != ''">
            and customer_name = #{customerName}
        </if>
        <if test="orderId != null and orderId != ''">
            and order_id = #{orderId}
        </if>
        <if test="idCard != null and idCard != ''">
            and id_card = #{idCard}
        </if>
        <if test="netNumber != null and netNumber != ''">
            and net_number like concat(#{netNumber},'%')
        </if>
        <if test="hehuorenIds != null and hehuorenIds.size > 0">
            and hehuoren_id in
            <foreach collection="hehuorenIds" item="hehuorenId" open="(" close=")" separator=",">
                #{hehuorenId}
            </foreach>
        </if>
        ) ho,order_view ov
        where
        ho.kd_order_id = ov.orderSeq
        order by create_time desc
    </select>


    <select id="selectOrderIdByOrderNumber" parameterType="String" resultMap="broadBandOrder">
        select *
        from hhr_order
        where order_number = #{orderNumber}
    </select>

    <select id="selectRepeatOrder" parameterType="String" resultType="com.winsun.bean.Order">
        select *
        from hhr_order
        where user_type in ('3', '5', '9', '10')
          and order_status != '异常单'
          and id_card = #{idCard}
    </select>

    <select id="selectKdyOrder" parameterType="String" resultType="com.winsun.bean.Order">
        select *
        from hhr_order
        where user_type in ('3', '9', '10')
          and order_status != '异常单'
          and id_card = #{idCard}
    </select>

    <select id="selectBroadList" parameterType="String" resultType="HashMap">
        select *
        from hhr_kd_dmt
        where account_number = #{accountNumber}
    </select>


    <select id="selectReportOrder" parameterType="String" resultType="com.winsun.bean.OrderView">
        select *
        from order_view
        where orderSeq = #{orderSeq}
    </select>

    <select id="checkOldKdUser" parameterType="String" resultType="HashMap">
        select *
        from hhr_kd_dmt_1
        where account_number = #{accountNumber}
    </select>

    <select id="checkOldhhrOrder" parameterType="String" resultType="HashMap">
        select *
        from hhr_order
        where order_status != '异常单'
          and net_number = #{accountNumber}
    </select>

    <select id="selectCompleteOrder" resultType="com.winsun.bean.Order">
        select *
        from hhr_order
        WHERE
        WHERE order_status='已发货' AND TO_DAYS(NOW()) - TO_DAYS(create_time) > 7
    </select>
    <update id="updateCompleteOrder">
        UPDATE hhr_order
        SET order_status = '已完成'
        WHERE order_status = '已发货'
          AND TO_DAYS(NOW()) - TO_DAYS(create_time) > 7
    </update>

    <select id="selectKdOrder" parameterType="String" resultType="HashMap">
        select *
        from hhr_order
        where order_status != '异常单'
          and kd_order_id = #{KdOrderID}
    </select>

    <!-- 更新未下单并且融合时间小于当前时间的订单 -->
    <update id="updateNosendAndRhTimeLtNowOrder">
        update hhr_order
        set order_status = "待受理",
            remarks="融合时间已过当前时间"
        where rh_time &lt; #{now}
          and order_status = "未下单"
    </update>

    <!--     00：移动通报学校  -->
    <select id="orderConversionXX" parameterType="String" resultType="HashMap">
        SELECT order_by,sub_name,school,supervisor ,
        COUNT(o.id) orderCount ,
        COUNT(o.xb_order_id) xb_orderCount ,
        COUNT(CASE WHEN xb_order_id is NULL or xb_order_id = '' THEN o.id end) yz_orderCount ,
        count(case when o.order_status in( '待识别','重新下单','待选号','审核中','待受理','提交中') then o.id end) status_1,
        count(case when o.order_status = '待处理' then o.id end) status_2,
        count(case when o.order_status = '待配送' then o.id end) status_3,
        count(case when o.order_status in ('待活体','已发货') then o.id end) status_4,
        count(case when o.order_status = '已完成' then o.id end) status_5
        FROM hhr_report_conversion rc
        LEFT JOIN hhr_order o
        ON rc.hehuoren_school = o.hehuoren_school
        and rc.sub_name = o.hehuoren_area
        and user_type = 0
        and order_status != '异常单'
        and o.kapin != '天翼学生证'
        <if test="startTime != null and startTime != ''">
            and DATE_FORMAT(create_time,'%Y-%m-%d') &gt;= #{startTime}
        </if>
        <if test="endTime != null and endTime != ''">
            and DATE_FORMAT(create_time,'%Y-%m-%d') &lt;= #{endTime}
        </if>
        GROUP BY order_by,sub_name,school,supervisor
        HAVING order_by is not null
        ORDER BY order_by
    </select>
    <!--    01：移动通报县份  -->
    <select id="orderConversionXF" parameterType="String" resultType="HashMap">
        SELECT sub_id,sub_name ,
        COUNT(o.id) orderCount ,
        COUNT(o.xb_order_id) xb_orderCount ,
        COUNT(CASE WHEN xb_order_id is NULL or xb_order_id = '' THEN o.id end) yz_orderCount ,
        count(case when o.order_status in( '待识别','重新下单','待选号','审核中','待受理','提交中') then o.id end) status_1,
        count(case when o.order_status = '待处理' then o.id end) status_2,
        count(case when o.order_status = '待配送' then o.id end) status_3,
        count(case when o.order_status in ('待活体','已发货') then o.id end) status_4,
        count(case when o.order_status = '已完成' then o.id end) status_5
        FROM hhr_report_conversion rc
        LEFT JOIN hhr_order o
        ON rc.hehuoren_school = o.hehuoren_school
        and rc.sub_name = o.hehuoren_area
        and user_type = 0
        and order_status != '异常单'
        and o.kapin != '天翼学生证'
        <if test="startTime != null and startTime != ''">
            and DATE_FORMAT(create_time,'%Y-%m-%d') &gt;= #{startTime}
        </if>
        <if test="endTime != null and endTime != ''">
            and DATE_FORMAT(create_time,'%Y-%m-%d') &lt;= #{endTime}
        </if>
        GROUP BY sub_id,sub_name
        HAVING sub_name is not null and sub_name != 'null'
        ORDER BY sub_id
    </select>
    <!--    10：宽带通报学校  -->
    <select id="kdOrderConversionXX" parameterType="String" resultType="HashMap">
        SELECT order_by,sub_name,school,supervisor,
        COUNT(o.id) orderCount ,
        count(case when o.user_type ='5' then o.id end) status_5_1,
        count(case when o.user_type ='5' and order_status = '已完成' then o.id end) status_5_2,
        count(case when o.user_type ='5' and order_status = '异常单' then o.id end) status_5_3,
        count(case when o.user_type ='3' then o.id end) status_3_1,
        count(case when o.user_type ='3' and order_status = '已完成' then o.id end) status_3_2,
        count(case when o.user_type ='3' and order_status = '异常单' then o.id end) status_3_3,
        count(case when o.user_type ='10' then o.id end) status_10_1,
        count(case when o.user_type ='10' and order_status = '已完成' then o.id end) status_10_2,
        count(case when o.user_type ='10' and order_status = '异常单' then o.id end) status_10_3
        FROM hhr_report_conversion rc
        LEFT JOIN hhr_order o
        ON rc.hehuoren_school = o.hehuoren_school
        and rc.sub_name = o.hehuoren_area
        and user_type in ('3','5','10')
        <if test="startTime != null and startTime != ''">
            and DATE_FORMAT(create_time,'%Y-%m-%d') &gt;= #{startTime}
        </if>
        <if test="endTime != null and endTime != ''">
            and DATE_FORMAT(create_time,'%Y-%m-%d') &lt;= #{endTime}
        </if>
        GROUP BY order_by,sub_name,school,supervisor
        HAVING order_by is not null
        ORDER BY order_by
    </select>
    <!--    11：宽带通报学校-->
    <select id="kdOrderConversionXF" parameterType="String" resultType="HashMap">
        SELECT sub_id,sub_name,
        count(DISTINCT school) schoolCount,
        count(o.id) orderCount ,
        count(case when o.user_type ='5' then o.id end) status_5_1,
        count(case when o.user_type ='5' and order_status = '已完成' then o.id end) status_5_2,
        count(case when o.user_type ='5' and order_status = '异常单' then o.id end) status_5_3,
        count(case when o.user_type ='3' then o.id end) status_3_1,
        count(case when o.user_type ='3' and order_status = '已完成' then o.id end) status_3_2,
        count(case when o.user_type ='3' and order_status = '异常单' then o.id end) status_3_3,
        count(case when o.user_type ='10' then o.id end) status_10_1,
        count(case when o.user_type ='10' and order_status = '已完成' then o.id end) status_10_2,
        count(case when o.user_type ='10' and order_status = '异常单' then o.id end) status_10_3
        FROM hhr_report_conversion rc
        LEFT JOIN hhr_order o
        ON rc.hehuoren_school = o.hehuoren_school
        and rc.sub_name = o.hehuoren_area
        and user_type in ('3','5','10')
        <if test="startTime != null and startTime != ''">
            and DATE_FORMAT(create_time,'%Y-%m-%d') &gt;= #{startTime}
        </if>
        <if test="endTime != null and endTime != ''">
            and DATE_FORMAT(create_time,'%Y-%m-%d') &lt;= #{endTime}
        </if>
        GROUP BY sub_id,sub_name
        HAVING sub_id not in (14,15)
        ORDER BY sub_id
    </select>

    <select id="getUserSalesStatistic" parameterType="map" resultType="java.util.HashMap">
        SELECT
        hehuoren_name as name,
        count( hehuoren_id ) as num
        FROM
        hhr_order
        WHERE
        order_status = "已完成"
        <if test="area != null and area != ''">
            AND hehuoren_area = #{area}
        </if>
        <if test="school != null and school != ''">
            AND hehuoren_school = #{school}
        </if>
        GROUP BY
        hehuoren_id
        ORDER BY
        count( hehuoren_id ) DESC
        LIMIT 0, 10
    </select>

    <select id="getLzSalesStatistic" parameterType="java.util.HashMap" resultType="java.util.HashMap">
        SELECT
        supervisor_name as name,
        count( supervisor_name ) as num
        FROM
        hhr_order
        WHERE
        order_status = "已完成"
        AND supervisor_name IS NOT NULL
        <if test="area != null and area != ''">
            AND hehuoren_area = #{area}
        </if>
        <if test="school != null and school != ''">
            AND hehuoren_school = #{school}
        </if>
        GROUP BY
        supervisor_name
        ORDER BY
        count( supervisor_name ) DESC
        LIMIT 0, 10
    </select>

    <select id="getSchoolSaleStatistic" parameterType="java.util.HashMap" resultType="java.util.HashMap">
        SELECT
        hehuoren_school as name,
        count( hehuoren_school ) as num
        FROM
        hhr_order
        WHERE
        order_status = "已完成"
        AND hehuoren_school IS NOT NULL
        <if test="area != null and area != ''">
            AND hehuoren_area = #{area}
        </if>
        <if test="school != null and school != ''">
            AND hehuoren_school = #{school}
        </if>
        GROUP BY
        hehuoren_school
        ORDER BY
        count( hehuoren_school ) DESC
        LIMIT 0, 10
    </select>

    <select id="getProductTransferStatistic" parameterType="java.util.HashMap" resultType="java.util.HashMap">
        SELECT
        hp.package_name as name,
        round( complate.num / ( all_order.num + 1000 ) * 100, 2) as num
        FROM
        (
        SELECT
        package_id,
        count( package_id ) AS num
        FROM
        hhr_order
        WHERE
        order_status = "已完成"
        AND user_type = "0"
        AND package_id IS NOT NULL
        <if test="area != null and area != ''">
            AND hehuoren_area = #{area}
        </if>
        <if test="school != null and school != ''">
            AND hehuoren_school = #{school}
        </if>
        GROUP BY
        package_id
        ) complate
        RIGHT JOIN
        (
        SELECT
        package_id,
        count( package_id ) AS num
        FROM
        hhr_order
        WHERE
        user_type = "0"
        AND package_id IS NOT NULL
        <if test="area != null and area != ''">
            AND hehuoren_area = #{area}
        </if>
        <if test="school != null and school != ''">
            AND hehuoren_school = #{school}
        </if>
        GROUP BY package_id
        ) all_order
        ON
        complate.package_id = all_order.package_id
        LEFT JOIN hhr_package hp ON all_order.package_id = hp.id
        ORDER BY
        ( complate.num / ( all_order.num + 1000 ) ) DESC
        LIMIT 0, 10
    </select>

    <select id="getProductSalesStatistic" parameterType="java.util.HashMap" resultType="java.util.HashMap">
        SELECT
        hp.package_name as name,
        count( ho.id ) as num
        FROM
        hhr_package hp
        LEFT JOIN
        (
        select
        id, package_id
        from
        hhr_order
        <where>
            <if test="area != null and area != ''">
                AND hehuoren_area = #{area}
            </if>
            <if test="school != null and school != ''">
                AND hehuoren_school = #{school}
            </if>
        </where>
        )ho ON hp.id = ho.package_id
        AND ho.id IS NOT NULL
        GROUP BY
        hp.id
        ORDER BY
        count( ho.id ) DESC
        limit 0, 5
    </select>
    <select id="selectBroadBandOrderList2" resultType="java.util.Map">
        select
        ho.*,
        ov.productName,
        ov.orderSeq,
        ov.order_set_meal,
        ov.order_university_name,
        ov.orderName,
        ov.ipay,
        ov.payType,
        ov.webOrderAmount,
        ov.order_payment_time,
        ov.orderReqtranSeq,
        ov.customeRid,
        ov.orderPhone,
        ov.status,
        ov.order_customer_type,
        ov.order_customer_student_id,
        ov.order_customer_remarks,
        ov.order_customer_account,
        ov.order_customer_pwd,
        ov.uptranseq,
        ov.expenses,
        ov.order_rf2,
        ov.orderSeqTHD,
        ov.order_region,
        ov.orderDate
        from (
        select * from hhr_order
        where
        kd_order_id is not null
        and user_type in (3,5,9,10)
        <if test="createTimeStart != null and createTimeStart != ''">
            and create_time &gt;= #{createTimeStart}
        </if>
        <if test="createTimeEnd != null and createTimeEnd != ''">
            and create_time &lt;= #{createTimeEnd}
        </if>
        <if test="successTimeStart != null and successTimeStart != ''">
            and success_time &gt;= #{successTimeStart}
        </if>
        <if test="successTimeEnd != null and successTimeEnd != ''">
            and success_time &lt;= #{successTimeEnd}
        </if>
        <if test="userType != null and userType != ''">
            and user_type = #{userType}
        </if>
        <if test="hehuorenArea != null and hehuorenArea != ''">
            and hehuoren_area = #{hehuorenArea}
        </if>
        <if test="orderStatus != null and orderStatus != ''">
            and order_status = #{orderStatus}
        </if>
        <if test="hehuorenSchool != null and hehuorenSchool != ''">
            and hehuoren_school = #{hehuorenSchool}
        </if>
        <if test="orderNumber != null and orderNumber != ''">
            and order_number = #{orderNumber}
        </if>
        <if test="hehuorenName != null and hehuorenName != ''">
            and hehuoren_name = #{hehuorenName}
        </if>
        <if test="contactNumber != null and contactNumber != ''">
            and contact_number = #{contactNumber}
        </if>
        <if test="businessNumber != null and businessNumber != ''">
            and business_number = #{businessNumber}
        </if>
        <if test="customerName != null and customerName != ''">
            and customer_name = #{customerName}
        </if>
        <if test="orderId != null and orderId != ''">
            and order_id = #{orderId}
        </if>
        <if test="idCard != null and idCard != ''">
            and id_card = #{idCard}
        </if>
        <if test="hehuorenIds != null and hehuorenIds.size > 0">
            and hehuoren_id in
            <foreach collection="hehuorenIds" item="hehuorenId" open="(" close=")" separator=",">
                #{hehuorenId}
            </foreach>
        </if>
        ) ho,order_view ov
        where
        ho.kd_order_id = ov.orderSeq
        order by create_time desc
    </select>
    <select id="selectRHList" resultType="java.util.Map">
        SELECT * FROM hhr_order
        WHERE order_status = '未下单' and (user_type = 3 or user_type = 10) and rh_time &gt;= #{left} and rh_time &lt;= #{right}
    </select>
    <select id="selectNotRHList" resultType="java.util.Map">
        SELECT * FROM hhr_order
        WHERE order_status = '未下单' and rh_time is null and (user_type = 3 or user_type = 10)
    </select>
    <select id="selectForId" resultType="java.util.Map">
        select * from hhr_order where id = #{id}
    </select>


</mapper>