<?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="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" resultType="com.winsun.bean.Order">
        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>

</mapper>