<?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.ylx.order.mapper.TOrderMapper">
    <resultMap id="getAllMap" type="com.ylx.order.domain.TOrder">
        <id column="c_id" property="cId"/>
        <result column="order_no" property="orderNo"/>
        <result column="atlas_add" property="atlasAdd"/>
        <result column="pay_type" property="payType"/>
        <result column="preferential" property="preferential"/>
        <result column="order_type" property="orderType"/>
        <result column="virtual_order_flag" property="virtualOrderFlag"/>
        <result column="virtual_order_allocation" property="virtualOrderAllocation"/>
        <result column="price_difference" property="priceDifference"/>
        <result column="c_js_id" property="cJsId"/>
        <result column="tape" property="tape"/>
        <result column="dept_id" property="deptId"/>
        <result column="distance" property="distance"/>
        <result column="dept_name" property="deptName"/>
        <result column="order_type" property="orderType"/>
        <result column="price_difference" property="priceDifference"/>
        <result column="new_js_id" property="newJsId"/>
        <result column="fare" property="fare"/>
        <result column="total_price" property="totalPrice"/>
        <result column="parent_no" property="parentNo"/>
        <result column="c_open_id" property="cOpenId"/>
        <result column="timeout_cause" property="timeoutCause"/>
        <result column="coupon_receive_id" property="couponReceiveId"/>
        <result column="c_goods" property="cGoods"
                typeHandler="com.baomidou.mybatisplus.extension.handlers.FastjsonTypeHandler"/>
        <result column="d_total_money" property="dTotalMoney"/>
        <result column="n_status" property="nStatus"/>
        <result column="c_address" property="cAddress"/>
        <result column="c_name" property="cName"/>
        <result column="c_phone" property="cPhone"/>
        <result column="c_note" property="cNote"/>
        <result column="c_time" property="cTime"/>
        <result column="name" property="name"/>
        <result column="address" property="address"/>
        <result column="longitude" property="longitude"/>
        <result column="latitude" property="latitude"/>
        <result column="arrival_longitude" property="arrivalLongitude"/>
        <result column="arrival_latitude" property="arrivalLatitude"/>
        <result column="depart_longitude" property="departLongitude"/>
        <result column="depart_latitude" property="departLatitude"/>
        <result column="arrival_photo" property="arrivalPhoto"/>
        <result column="depart_time" property="departTime"/>
        <result column="c_tj_open_id" property="cTjOpenId"/>
        <result column="old_js_id" property="oldJsId"/>
        <result column="dt_create_time" property="dtCreateTime"/>
        <result column="acceptance_time" property="acceptanceTime"/>
        <result column="reach_time" property="reachTime"/>
        <result column="start_time" property="startTime"/>
        <result column="pay_time" property="payTime"/>
        <result column="end_time" property="endTime"/>
        <result column="reason_refusal" property="reasonRefusal"/>

        <association property="js" javaType="com.ylx.massage.domain.TJs">
            <id column="jsc_id" property="cOpenId"/>
            <result column="jsc_bh_list" property="cBhList"/>
            <result column="jsc_img_list" property="cImgList"
                    typeHandler="com.baomidou.mybatisplus.extension.handlers.FastjsonTypeHandler"/>
            <result column="jsn_sex" property="nSex"/>
            <result column="jsc_nick_name" property="cNickName"/>
            <result column="jsc_portrait" property="cPortrait"/>
            <result column="jsc_name" property="cName"/>
            <result column="jsc_phone" property="cPhone"/>
            <result column="jsc_jianjie" property="cJianjie"/>
            <result column="jsn_star" property="nStar"/>
            <result column="jsn_bili" property="nBili"/>
            <result column="jsn_num" property="nNum"/>
            <result column="jsn_status" property="nStatus"/>
            <result column="jsn_status2" property="nStatus2"/>
            <result column="jsn_tong" property="nTong"/>
            <result column="jsc_time" property="cTime"/>
            <result column="jsn_b1" property="nB1"/>
            <result column="jsn_b2" property="nB2"/>
            <result column="jsn_b3" property="nB3"/>
            <result column="jsn_b3" property="nB3"/>
        </association>
        <association property="wxUser" javaType="com.ylx.massage.domain.TWxUser">
            <id column="wxUserc_openid" property="cOpenid"/>
            <result column="wxUsercc_icon" property="cIcon"/>
            <result column="wxc_nick_name" property="cNickName"/>
        </association>
    </resultMap>

    <!-- 查询所有订单(PC端 H5端) -->
    <select id="getAll" resultMap="getAllMap">
        select t_order.c_id,
        t_order.order_no,
        t_order.atlas_add,
        t_order.pay_type,
        t_order.c_js_id,
        t_order.order_type,
        t_order.virtual_order_flag,
        t_order.virtual_order_allocation,
        t_order.price_difference,
        t_order.tape,
        t_order.fare,
        t_order.total_price,
        t_order.parent_no,
        t_order.c_open_id,
        t_order.c_goods,
        t_order.d_total_money,
        t_order.n_status,
        t_order.c_address,
        t_order.c_name,
        t_order.c_phone,
        t_order.c_note,
        t_order.c_time,
        t_order.pay_time,
        t_order.timeout_cause,
        t_order.name,
        t_order.dept_id,
        t_order.dept_name,
        t_order.latitude,
        t_order.longitude,
        t_order.address,
        t_order.c_tj_open_id,
        t_order.dt_create_time,
        t_order.acceptance_time,
        t_order.reach_time,
        t_order.old_js_id,
        t_order.start_time,
        t_order.arrival_longitude,
        t_order.arrival_latitude,
        t_order.depart_longitude,
        t_order.depart_latitude,
        t_order.arrival_photo,
        t_order.depart_time,
        t_order.end_time,
        t_order.preferential,
        t_order.distance,
        t_order.reason_refusal,
        t_js.c_open_id as jsc_open_id,
        t_js.c_bh_list as jsc_bh_list,
        t_js.c_img_list as jsc_img_list,
        t_js.c_sfz_img as jsc_sfz_img,
        t_js.n_sex as jsn_sex,
        t_js.c_nick_name as jsc_nick_name,
        t_js.c_portrait as jsc_portrait,
        t_js.c_name as jsc_name,
        t_js.c_phone as jsc_phone,
        t_js.c_address as jsc_address,
        t_js.c_jianjie as jsc_jianjie,
        t_js.n_star as jsn_star,
        t_js.n_bili as jsn_bili,
        t_js.n_num as jsn_num,
        t_js.n_status as jsn_status,
        t_js.n_status2 as jsn_status2,
        t_js.n_tong as jsn_tong,
        t_js.c_time as jsc_time,
        t_js.n_b1 as jsn_b1,
        t_js.n_b2 as jsn_b2,
        t_js.n_b3 as jsn_b3,
        t_js.dt_create_time as jsdt_create_time,
        t_wx_user.c_openid as wxUserc_openid,
        t_wx_user.c_icon as wxUsercc_icon,
        t_wx_user.c_nick_name as wxc_nick_name
        from t_order
        left join
        t_js on
        t_order.c_js_id = t_js.id
        left join
        t_wx_user on
        t_wx_user.c_openid = t_order.c_open_id
        <where>
            t_order.is_delete = 0 and t_js.is_delete = 0 and t_wx_user.is_delete = 0
            and t_order.n_status != 8 and t_order.n_status != -2 and t_order.n_status != -3
            and t_order.n_status != -1
            <if test="param.cId != null and param.cId != ''">
                and t_order.c_id = #{ param.cId }
            </if>
            <if test="param.cName != null and param.cName != ''">
                and t_order.c_name like concat('%',#{param.cName},'%')
            </if>
            <if test="param.cNickName != null and param.cNickName != ''">
                and t_js.c_nick_name like concat('%',#{param.cNickName},'%')
            </if>
            <if test="param.jsName != null and param.jsName != ''">
                and t_js.c_name like concat('%',#{param.jsName},'%')
            </if>
            <if test="param.jsPhone != null and param.jsPhone != ''">
                and t_js.c_phone like concat('%',#{param.jsPhone},'%')
            </if>
            <if test="param.cPhone != null and param.cPhone != ''">
                and t_order.c_phone = #{param.cPhone}
            </if>
            <if test="param.orderNo != null and param.orderNo != ''">
                and t_order.order_no = #{ param.orderNo }
            </if>
            <!-- 订单状态(-1：待付款 0：待接单 1：已接单 6：已出发 2：已到达 3：服务中 4：待评价 5：已完成
                          7：退单待审核 8：退单审核通过  -2：已取消 -3：已拒绝) -->
            <!--<if test="param.nStatus != null and param.nStatus != 10 and param.nStatus != -10 and param.nStatus != 100">-->
            <!--    and t_order.n_status = #{ param.nStatus }-->
            <!--</if>-->
            <!--<if test="param.nStatus == 10">-->
            <!--    and t_order.n_status in(0,1,2,3)-->
            <!--</if>-->
            <!--<if test="param.nStatus == -10">-->
            <!--    and t_order.n_status in(-2,-3)-->
            <!--</if>-->

            <if test="param.nStatus != null">
                and t_order.n_status = #{ param.nStatus }
            </if>

            <if test="param.wStatus != null">
                and t_order.w_status = #{ param.wStatus }
            </if>

            <if test="param.cJsId != null and param.cJsId != ''">
                and t_order.c_js_id = #{ param.cJsId }
            </if>
            <if test="param.deptId != null and param.deptId != '' and param.deptId != 100">
                and t_order.dept_id = #{ param.deptId }
            </if>
            <if test="param.cOpenId != null and param.cOpenId != ''">
                and t_order.c_open_id = #{ param.cOpenId }
            </if>
            <if test="param.dtCreateTimeBegin != null and param.dtCreateTimeBegin != '' and param.dtCreateTimeTimeEnd != null and param.dtCreateTimeTimeEnd != ''">
                and t_order.dt_create_time between #{ param.dtCreateTimeBegin } and #{ param.dtCreateTimeTimeEnd }
            </if>
            <if test="param.payTimeBegin != null and param.payTimeBegin != '' and param.payTimeEnd != null and param.payTimeEnd != ''">
                and t_order.pay_time between #{param.payTimeBegin} and #{param.payTimeEnd}
            </if>

        </where>
        order by
        t_order.dt_create_time desc
    </select>


    <select id="getOrderNum" resultType="java.lang.Integer">
        SELECT count(1)
        FROM t_order
        WHERE is_delete = 0
          AND c_js_id = #{jsid}
          AND end_time BETWEEN #{startDate} AND #{endDate}
          AND n_status in (4, 5)
    </select>
    <select id="getAddNum" resultType="java.lang.Integer">
        SELECT count(1)
        FROM t_order
        WHERE is_delete = 0
          AND c_js_id = #{jsid}
          AND end_time BETWEEN #{startDate} AND #{endDate}
          AND n_status in (4, 5)
          and order_type = 1
    </select>
    <select id="getUpgradeNum" resultType="java.lang.Integer">
        SELECT count(1)
        FROM t_order
        WHERE is_delete = 0
          AND c_js_id = #{jsid}
          AND end_time BETWEEN #{startDate} AND #{endDate}
          AND n_status in (4, 5)
          and order_type = 2
    </select>
    <select id="getTurnover" resultType="java.math.BigDecimal">
        SELECT sum(total_price)
        FROM t_order
        WHERE is_delete = 0
          AND c_js_id = #{jsid}
          AND end_time BETWEEN #{startDate} AND #{endDate}
          AND n_status in (4, 5)
    </select>

    <select id="getBlock" resultType="com.ylx.massage.domain.vo.HomeBlock">
        SELECT
            DATE_FORMAT( end_time, '%Y-%m' ) AS month,
            SUM( total_price ) AS turnover,
            count(1) orderNum
        FROM
            t_order
        WHERE
            is_delete = 0
          AND end_time IS NOT NULL
          AND end_time BETWEEN #{start} AND #{end}
          AND n_status in (4, 5)
          <if test="deptId != 100 and deptId != ''">
            AND dept_id = #{deptId}
          </if>
        GROUP BY
            month
        ORDER BY
            month;

    </select>

    <select id="callAutoAccount" statementType="CALLABLE">
        {call updateUserBalance(#{hCount, mode=IN, jdbcType=INTEGER}, #{percent, mode=IN, jdbcType=DECIMAL})}
    </select>
</mapper>
