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

    <resultMap type="com.younike.system.domain.OrderStat" id="OrderStatResult">
        <result property="orderId" column="order_id"/>
        <result property="merchantId" column="merchant_id"/>
        <result property="goodsId" column="goods_id"/>
        <result property="serverId"    column="server_id"/>
        <result property="score" column="score"/>
        <collection property="goodsCommentDimensionList" ofType="com.younike.system.domain.GoodsCommentDimension">
            <id property="commentId" column="comment_id"/>
            <result property="dimensionId" column="dimension_id"/>
            <result property="score" column="dscore"/>
        </collection>
    </resultMap>

    <resultMap type="OrderVo" id="OrderListResult">
        <result property="orderId"    column="order_id"    />
        <result property="orderIndex"    column="order_index"    />
        <result property="memberId"    column="member_id"    />
        <result property="merchantId"    column="merchant_id"    />
        <result property="merchantName"    column="merchant_name"    />
        <result property="orderType"    column="order_type"    />
        <result property="orderState"    column="order_state"    />
        <result property="commentState"    column="comment_state"    />
        <result property="appointmentStartTime"    column="appointment_start_time"    />
        <result property="appointmentEndTime"    column="appointment_end_time"    />
        <result property="serviceStartTime"    column="service_start_time"    />
        <result property="serviceEndTime"    column="service_end_time"    />
        <result property="serviceLongitude"    column="service_longitude"    />
        <result property="serviceLatitude"    column="service_latitude"    />
        <result property="shopAmount"    column="shop_amount"    />
        <result property="isSettle"    column="is_settle"    />
        <result property="orderAmount"    column="order_amount"    />
        <result property="beanAmount"    column="bean_amount"    />
        <result property="memberCouponId"    column="member_coupon_id"    />
        <result property="couponAmount"    column="coupon_amount"    />
        <result property="totalAmount"    column="total_amount"    />
        <result property="addAmount"    column="add_amount"    />
        <result property="payWay"    column="pay_way"    />
        <result property="memo"    column="memo"    />
        <result property="orderVerificationCode"    column="order_verification_code"    />
        <result property="groupTimes"    column="group_times"    />
        <result property="groupRemainingTimes"    column="group_remaining_times"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="loginIp"    column="login_ip"    />
        <result property="loginDate"    column="login_date"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="column1"    column="column1"    />
        <result property="column2"    column="column2"    />
        <result property="column3"    column="column3"    />
        <result property="merchantNameJoin" column="merchant_name_join"/>
        <result property="goodsId" column="goods_id"/>
        <result property="goodsName"  column="goods_name"/>
        <result property="categoryName"  column="category_name"/>
        <result property="serverPhone" column="server_phone"/>
        <result property="serverName" column="server_name"/>
        <result property="memberPhone" column="member_phone"/>
        <result property="merchantType" column="merchant_type"/>
        <result property="merchantIndex" column="merchant_index"/>
        <result property="statusName" column="status_name"/>
        <result property="goodsServerPrice" column="goods_server_price"/>
        <result property="goodsCounts" column="goods_counts"/>
        <result property="adminId" column="admin_id"/>
        <result property="goodsIndex" column="goods_index"/>
        <result property="specificationDesc" column="specification_desc"/>
        <result property="verificationCodeEffectiveDate" column="verification_code_effective_date"/>
        <result property="specificationCurrentPrice" column="current_price"/>
        <result property="status" column="status"/>


    </resultMap>

    <select id="orderList" resultMap="OrderListResult">
        select o.*, og.name as goods_name,og.goods_id, su.phone as server_phone, su.nike_name as server_name,
               merchant.merchant_name as merchant_name_join, merchant.merchant_type, merchant.merchant_index, merchant.admin_id, c.category_name, m.phone as member_phone,
               sc.decode_value as status_name, ogs.goods_server_price, ogs.goods_counts, g.goods_index,gs.specification_desc,gs.current_price,oor.status
        from `order` o
        left join order_goods og on o.order_id = og.order_id and og.del_flag = 0
        LEFT JOIN goods_specification gs on og.goods_specification_id = gs.goods_specification_id
        left join goods g on g.goods_id = og.goods_id and g.del_flag = 0
        left join category c on og.cate_id = c.category_id and c.del_flag = 0
        left join order_goods_server ogs on o.order_id = ogs.order_id and ogs.del_flag = 0
        left join server_user su on ogs.server_id = su.server_id and su.del_flag = 0
        left join merchant merchant on o.merchant_id = merchant.merchant_id and merchant.del_flag = 0
        left join `member` m on o.member_id = m.member_id and m.del_flag = 0
        left join sys_code sc on o.order_state = sc.decode_id and code_id = '003'
        left join order_refund oor on o.order_id = oor.order_id and oor.is_del = 0
        where o.del_flag = 0
        <if test="orderIndex != null and orderIndex != ''">and o.order_index like concat('%', #{orderIndex}, '%')</if>
        <if test="orderType != null and orderType != ''">and o.order_type = #{orderType}</if>
        <if test="orderState != null and orderState != ''">and o.order_state = #{orderState}</if>
        <if test="merchantId != null and merchantId != ''">and o.merchant_id = #{merchantId}</if>
        <if test="merchantIndex != null and merchantIndex != ''">and merchant.merchant_index like concat('%',
            #{merchantIndex}, '%')
        </if>
        <if test="merchantName != null  and merchantName != ''">and merchant.merchant_name like concat('%',
            #{merchantName}, '%')
        </if>
        <if test="goodsId != null and goodsId != ''">and og.goods_id = #{goodsId}</if>
        <if test="goodsName != null and goodsName != ''">and og.name like concat('%', #{goodsName}, '%')</if>
        <if test="serverPhone != null  and serverPhone != ''">and su.phone like concat('%', #{serverPhone}, '%')</if>
        <if test="serverName != null  and serverName != ''">and su.nike_name like concat('%', #{serverName}, '%')</if>
        <if test="memberPhone != null  and memberPhone != ''">and m.phone like concat('%', #{memberPhone}, '%')</if>
        <if test="categoryId != null  and categoryId != ''">and og.cate_id = #{categoryId}</if>
        <if test="merchantType != null">and merchant.merchant_type = #{merchantType}</if>
        <!--        <if test="isAdd != null and isAdd == 1"> and o.add_amount is not null </if>-->
        <!--        <if test="isAdd != null and isAdd == 0"> and o.add_amount is null </if>-->
        <if test="status != null  and status != ''">and o.order_state = #{status}</if>
        <if test="beginTime != null and beginTime != ''"><!-- 开始时间检索 -->
            and date_format(o.create_time,'%y%m%d') &gt;= date_format(#{beginTime},'%y%m%d')
        </if>
        <if test="endTime != null and endTime != ''"><!-- 结束时间检索 -->
            and date_format(o.create_time,'%y%m%d') &lt;= date_format(#{endTime},'%y%m%d')
        </if>
        <if test="homeAdd != null  and homeAdd != '' and homeAdd == 1"> and og.add_counts > 0 </if>
        <if test="homeAdd != null  and homeAdd != '' and homeAdd == 0"> and (og.add_counts = 0 or og.add_counts is null)</if>
        <if test="repairAdd != null  and repairAdd != '' and repairAdd == 1"> and o.maintenance_amount > 0</if>
        <if test="repairAdd != null  and repairAdd != '' and repairAdd == 0"> and (o.maintenance_amount = 0 or o.maintenance_amount is null)</if>
        <if test="orderVerificationCode != null and orderVerificationCode != ''">and o.order_verification_code = #{orderVerificationCode}</if>
        ORDER BY o.create_time DESC
    </select>

    <update id="updateOrderCustom" parameterType="com.younike.system.domain.Order">
        update `order`
        <trim prefix="SET" suffixOverrides=",">
            <if test="orderIndex != null">order_index = #{orderIndex},</if>
            <if test="memberId != null">member_id = #{memberId},</if>
            <if test="merchantId != null">merchant_id = #{merchantId},</if>
            <if test="merchantName != null">merchant_name = #{merchantName},</if>
            <if test="orderType != null and orderType != ''">order_type = #{orderType},</if>
            <if test="orderState != null and orderState != ''">order_state = #{orderState},</if>
            <if test="appointmentStartTime != null">appointment_start_time = #{appointmentStartTime},</if>
            <if test="appointmentEndTime != null">appointment_end_time = #{appointmentEndTime},</if>
            <if test="serviceStartTime != null">service_start_time = #{serviceStartTime},</if>
            <if test="serviceEndTime != null">service_end_time = #{serviceEndTime},</if>
            <if test="serviceLongitude != null">service_longitude = #{serviceLongitude},</if>
            <if test="serviceLatitude != null">service_latitude = #{serviceLatitude},</if>
            <if test="shopAmount != null">shop_amount = #{shopAmount},</if>
            <if test="isSettle != null">is_settle = #{isSettle},</if>
            <if test="orderAmount != null">order_amount = #{orderAmount},</if>
            <if test="maintenanceAmount != null">maintenance_amount = #{maintenanceAmount},</if>
            <if test="maintenanceRemark != null">maintenance_remark = #{maintenanceRemark},</if>
            <if test="addAmount != null">add_amount = #{addAmount},</if>
            <if test="addPayStatus != null">add_pay_status = #{addPayStatus},</if>
            <if test="addPayWay != null">add_pay_way = #{addPayWay},</if>
            <if test="beanAmount != null">bean_amount = #{beanAmount},</if>
            <if test="memberCouponId != null">member_coupon_id = #{memberCouponId},</if>
            <if test="couponAmount != null">coupon_amount = #{couponAmount},</if>
            <if test="totalAmount != null">total_amount = #{totalAmount},</if>
            <if test="payWay != null">pay_way = #{payWay},</if>
            <if test="memo != null">memo = #{memo},</if>
            <if test="delFlag != null and delFlag != ''">del_flag = #{delFlag},</if>
            <if test="loginIp != null">login_ip = #{loginIp},</if>
            <if test="loginDate != null">login_date = #{loginDate},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="column1 != null">column1 = #{column1},</if>
            <if test="column2 != null">column2 = #{column2},</if>
            <if test="column3 != null">column3 = #{column3},</if>
            <if test="addShopAmount != null">add_shop_amount = #{addShopAmount},</if>
            <if test="orderVerificationCode != null and orderVerificationCode != ''">order_verification_code =
                #{orderVerificationCode},
            </if>
            <if test="groupTimes != null">group_times = #{groupTimes},</if>
            <if test="groupRemainingTimes != null">group_remaining_times = #{groupRemainingTimes},</if>

        </trim>
        where order_id = #{orderId}
    </update>

    <select id="listLastOrders" resultMap="OrderStatResult">
        SELECT o.order_id, o.merchant_id, og.goods_id, IFNULL(og.server_id,0) as server_id , gc.score,
               gcd.comment_id, gcd.dimension_id, gcd.score as dscore
        FROM `order` o
         LEFT JOIN order_goods og ON og.order_id = o.order_id
         LEFT JOIN goods_comment gc ON gc.order_id = o.order_id
         LEFT JOIN goods_comment_dimension gcd ON gcd.comment_id = gc.commont_id
        WHERE o.order_state IN (${orderStates})
        AND DATE_SUB(CURDATE(), INTERVAL ${days} DAY) &lt;= date(o.create_time)
    </select>

    <select id="statisticGoodsServerDimension" resultType="com.younike.system.domain.GoodsServerDimension">
        SELECT gc.goods_id as goodsId, gc.server_id as serverId, gcd.dimension_id as dimensionId, round(avg(gcd.score), 2) as score
        FROM `order` o
                 LEFT JOIN goods_comment gc ON gc.order_id = o.order_id
                 LEFT JOIN goods_comment_dimension gcd ON gcd.comment_id = gc.commont_id
        where gcd.score is not null
          AND o.order_state IN (${orderStates})
          AND DATE_SUB(CURDATE(), INTERVAL ${days} DAY) &lt;= date(o.create_time)
        group by gc.goods_id, gc.server_id, gcd.dimension_id
    </select>

    <select id="listOrderedOrderByServerId" resultMap="OrderResult">
        SELECT
            o.order_id,
            o.order_state,
            o.appointment_start_time,
            o.appointment_end_time
        FROM
            `order` AS o
                LEFT JOIN order_goods og ON og.order_id = o.order_id
        WHERE
            o.del_flag = '0'
          AND (
                o.order_state = '000'
                OR o.order_state = '001'
            )
          AND og.server_id = #{serverId}
          AND date_add(now(),interval 1 day) &lt; o.appointment_start_time
    </select>
    <!--查询会员端订单分页列表-->
    <select id="listMemberOrderByOrderState" resultType="com.younike.system.domain.vo.MemberOrderVo">
        SELECT
        o.order_id,
        o.order_index,
        o.member_id,
        o.merchant_id,
        o.merchant_name,
        o.order_type,
        o.order_state,
        o.comment_state,
        o.appointment_start_time,
        o.appointment_end_time,
        o.service_start_time,
        o.service_end_time,
        o.order_amount,
        o.total_amount,
        o.add_amount,
        o.create_time,
        o.update_time,
        o.maintenance_amount,
        o.maintenance_remark,
        o.add_pay_status,
        o.group_times,
        o.coupon_amount,
        o.bean_amount,
        ogs.server_name,
        ogs.server_avatar,
        og.goods_counts,
        og.add_counts,
        og.name AS goodsName,
        og.price AS goodsServerPrice,
        og.goods_unit,
        og.other_unit,
        og.goods_id,
        og.server_id,
        og.cate_id,
        og.publish_id,
        oa.address,
        oa.detailed,
        su.rate,
        su.server_level,
        su.server_age,
        sdd.dict_label AS goodsUnitName,
        gs.specification_desc,
        gs.scribing_price,
        m.avatar as merchantAvatar,
        gss.server_img_url as serverImgUrl
        FROM `order` AS o
        LEFT JOIN order_goods_server ogs ON ogs.order_id = o.order_id
        LEFT JOIN order_goods og ON og.order_id = o.order_id
        LEFT JOIN goods_specification gs ON gs.goods_specification_id = og.goods_specification_id
        LEFT JOIN order_address oa ON oa.order_id = o.order_id
        LEFT JOIN server_user su ON su.server_id = og.server_id
        LEFT JOIN goods_server gss ON gss.goods_id = og.goods_id and gss.server_id = og.server_id and gss.del_flag =0
        LEFT JOIN sys_dict_data sdd ON sdd.dict_value = og.goods_unit AND sdd.dict_type = 'goods_unit'
        LEFT JOIN merchant m ON m.merchant_id =  o.merchant_id
        WHERE o.del_flag = '0'
        AND o.member_id = #{order.memberId}
        <if test="order.orderState != null and order.orderState != ''">
            AND o.order_state IN
            <foreach item="orderState" collection="orderStates" open="(" separator="," close=")">
                #{orderState}
            </foreach>
        </if>
        ORDER BY o.create_time DESC
    </select>

    <!--查询会员端订单详情-->
    <select id="getMemberOrderByOrderId" resultType="com.younike.system.domain.vo.MemberOrderDetailVo">
        SELECT
            o.order_id,
            o.order_index,
            o.member_id,
            o.merchant_id,
            o.merchant_name,
            o.order_type,
            o.order_state,
            o.comment_state,
            o.appointment_start_time,
            o.appointment_end_time,
            o.service_start_time,
            o.service_end_time,
            o.order_amount,
            o.total_amount,
            o.add_amount,
            o.coupon_amount,
            o.bean_amount,
            o.memo,
            o.create_time,
            o.update_time,
            o.maintenance_amount,
            o.maintenance_remark,
            o.add_pay_status,
            o.order_verification_code,
            o.group_times,
            o.group_remaining_times,
            o.verification_code_effective_date as verificationCodeEffectiveDate,
            o.is_settle,
            ogs.server_name,
            ogs.server_avatar,
            og.goods_counts,
            og.add_counts,
            og.goods_id,
            og.name AS goodsName,
            og.price AS goodsServerPrice,
            og.cate_id,
            og.goods_unit,
            og.other_unit,
            og.server_id,
            og.single_time,
            og.cate_id,
            og.publish_id as publishId,
            oa.address,
            oa.detailed,
            oa.name AS memberName,
            oa.phone,
            su.rate,
            su.server_level,
            su.server_age,
            p.publish_state AS publishState,
            sdd.dict_label AS goodsUnitName,
            gs.specification_desc,
            gs.scribing_price,
            gs.times,
            gs.current_price,
            gss.server_img_url as serverImgUrl
        FROM `order` AS o
                 LEFT JOIN order_goods_server ogs ON ogs.order_id = o.order_id
                 LEFT JOIN order_goods og ON og.order_id = o.order_id
                 LEFT JOIN goods_specification gs ON og.goods_specification_id = gs.goods_specification_id
                 LEFT JOIN publish p ON p.publish_id = og.publish_id and p.del_flag=0
                 LEFT JOIN order_address oa ON oa.order_id = o.order_id
                 LEFT JOIN server_user su ON su.server_id = og.server_id
                 LEFT JOIN goods_server gss ON gss.goods_id = og.goods_id and gss.server_id = og.server_id and gss.del_flag =0
                 LEFT JOIN sys_dict_data sdd ON sdd.dict_value = og.goods_unit AND sdd.dict_type = 'goods_unit'
        WHERE o.del_flag = '0'
          AND o.order_id = #{orderId}
    </select>

    <select id="selectPreSettleOrders" resultMap="OrderResult">
        SELECT
        o.*
        FROM
        `order` o
        LEFT JOIN merchant m ON m.merchant_id = o.merchant_id
        WHERE
        (
            o.order_state = '003'
            OR o.order_state = '004'
            OR o.order_state = '007'
        )
        AND o.is_settle = 0
        AND o.service_start_time &lt; #{settleDate}
    </select>

    <select id="statisticsToday" resultType="com.younike.system.domain.vo.MerchantDashboardVO">
        select
            count(*) as orderNum, ifnull(sum(order_amount), 0) as orderAmount, ifnull(sum(total_amount), 0) as actualAmount
        from
             `order`
        where
          merchant_id = #{merchantId}
          and to_days(create_time) = to_days(now())
          and del_flag = 0
          and order_state not in ('000', '006', '099')
    </select>

    <select id="statisticsByMonth" resultType="com.younike.system.domain.vo.OrderStatisticsVO">
        select
            DATE_FORMAT(create_time, '%Y-%m-%d') as `date`, count(*) as orderNum, sum(total_amount) as orderAmount, sum(shop_amount) as actualAmount
        from
            `order`
        where
            merchant_id = #{merchantId}
            and DATE_SUB(CURDATE(), INTERVAL 30 DAY) <![CDATA[ <= ]]> create_time
            and del_flag = 0
            and order_state not in ('000', '006', '099')
        group by DATE_FORMAT(create_time, '%Y-%m-%d')
        order by create_time asc
    </select>

    <select id="getTopServerUser" resultType="com.younike.system.domain.vo.ServerUserVo">
        select
            count(*) as orderNum, og.server_id as serverId, sum(o.total_amount) as orderAmount,
            sum((og.goods_counts + og.add_counts) * og.single_time) as serverTime,
            su.real_name as realName, su.phone
        from
            `order_goods` og
            left join `order` o on o.order_id = og.order_id
            left join server_user su on og.server_id = su.server_id
         where
            o.merchant_id = #{merchantId}
            and su.merchant_id = #{merchantId}
            and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <![CDATA[ <= ]]> o.create_time
            and o.del_flag = 0
            and og.del_flag = 0
           and o.order_state not in ('000', '006', '099')
        group by og.server_id
        order by orderNum desc
        limit 5
    </select>

    <!-- 查询未支付订单 -->
    <select id="listMemberOrderOfPendingPayment" resultType="com.younike.system.domain.Order">
        SELECT
        o.order_id,
        o.member_id,
        o.merchant_id,
        o.merchant_name,
        o.order_type,
        o.order_state,
        o.comment_state,
        o.appointment_start_time,
        o.appointment_end_time,
        o.service_start_time,
        o.service_end_time,
        o.order_amount,
        o.total_amount,
        o.add_amount,
        o.create_time
        FROM `order` AS o
        WHERE o.del_flag = '0'
        AND o.order_state = #{orderState}
        ORDER BY o.create_time
    </select>

    <update id="updateBatchById">
        <foreach collection="list" item="item" index="index" separator=";">
            update merchant_settle_record SET is_settle = #{item.isSettle} WHERE order_id = #{item.orderId}
        </foreach>
    </update>

    <!--更新订单状态-->
     <update id="updateOrderState">
        <foreach collection="orderList" item="item" index="index" separator=";">
            update `order` SET order_state = #{item.orderState} WHERE order_id = #{item.orderId}
        </foreach>
    </update>

    <!--查询今日订单数据-->
    <select id="getTodayOrderData" resultType="com.younike.system.domain.vo.TodayOrderVO">
        select
        count(*) AS orderCount,
        IFNULL(SUM(total_amount), 0) AS orderAmount,
        IFNULL(SUM(shop_amount), 0) AS paidInAmount
        FROM `order`
        WHERE del_flag = 0
        AND to_days(create_time) = to_days(now())
        AND order_state NOT IN ('000', '006', '099')
    </select>

    <select id="getTopFiveMerchantOrderData" resultType="com.younike.system.domain.vo.MerchantOrderOfWeekVO">
        SELECT
        count(*) AS orderCount,
        SUM(o.total_amount) AS orderAmount,
        CASE m.merchant_type
        WHEN 0 THEN  m.merchant_name
        WHEN 1 THEN  su.nick_name
        END AS merchantName,
        m.merchant_phone AS phone
        FROM `order` o
        LEFT JOIN merchant m ON m.merchant_id = o.merchant_id
        LEFT JOIN sys_user su ON su.user_id = m.admin_id
        WHERE o.del_flag = '0'
        AND o.order_state NOT IN ('000', '006', '099')
        AND DATE_SUB(CURDATE(), INTERVAL 7 DAY) <![CDATA[ <= ]]> o.create_time
        GROUP BY o.merchant_id
        ORDER BY orderCount DESC
        LIMIT 5
    </select>

    <!--运营首页查询近30天订单数据-->
    <select id="getOrderDataByMonth" resultType="com.younike.system.domain.vo.OrderStatisticsVO">
        SELECT
        DATE_FORMAT(create_time, '%Y-%m-%d') AS `date`,
        count(*) AS orderNum,
        sum(total_amount) AS orderAmount,
        sum(shop_amount) AS actualAmount
        FROM `order`
        WHERE del_flag = '0'
        AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) <![CDATA[ <= ]]> create_time
        AND del_flag = 0
        AND order_state NOT IN ('000', '006', '099')
        GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d')
        ORDER BY create_time ASC
    </select>

    <select id="selectServerBookedOrder" resultType="com.younike.system.domain.Order">
        SELECT
        o.*
        FROM `order` AS o
        LEFT JOIN order_goods og ON og.order_id = o.order_id
        <where>
            o.del_flag = '0'
            and o.order_state in ('000', '001')
            and og.server_id = #{serverId}
            and o.appointment_start_time >= DATE_ADD(CURDATE(),INTERVAL 1 DAY)
        </where>
    </select>

    <select id="selectPublishInfo" resultMap="OrderVoResult" parameterType="Long">
         select o.order_id, o.order_state,og.goods_id,og.publish_id
         from  `order` o
        LEFT JOIN order_goods og ON o.order_id = og.order_id and og.del_flag=0
        where o.order_id = #{orderId}
        and o.del_flag=0
    </select>
<!--  取消收取维修配件费数据变更 -->
    <update id="cancelMaintenanceServer" parameterType="com.younike.system.domain.Order">
        update `order`
         SET
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            maintenance_amount = null,
            maintenance_remark = null,
            add_amount = null,
            add_shop_amount =null,
            add_pay_status = null,
            add_pay_way =null,
            add_pay_status =null
        where order_id = #{orderId}
    </update>

    <select id="listPersonalMerchantSettleOrder" resultMap="OrderResult">
        SELECT
            o.*
        FROM
            `order` o
                LEFT JOIN merchant m ON m.merchant_id = o.merchant_id
        WHERE
            (
                    o.order_state = '004'
                    OR o.order_state = '007'
                )
          AND m.merchant_type = '1'
          AND o.service_start_time >= #{settleStartDate}
          AND o.service_start_time &lt;= #{settleEndDate}
    </select>
    <!--    根据服务id查询是否有订单非已取消与已退款状态-->
    <select id="adminDeleteGoods" resultMap="OrderResult" resultType="Long">
      select o.* from  `order` o
      LEFT JOIN order_goods og ON o.order_id = og.order_id and  og.del_flag =0
      LEFT JOIN goods g ON og.goods_id =g.goods_id and g.del_flag = 0
        where o.del_flag = 0
        and og.goods_id = #{goodsId}
        and g.one_off = 0
        and o.order_state in ('000', '001','002','003','004','005','007')
         ORDER BY o.create_time ASC
    </select>
    <!--   根据服务人员id查询服务人员是否有未完成的订单 -->
    <select id="merchanServerUserId" parameterType="String" resultMap="OrderResult">
        select o.* from `order` o
        LEFT JOIN order_goods og ON o.order_id = og.order_id and og.del_flag =0
        LEFT JOIN goods_server gs ON gs.goods_id = og.goods_id and gs.del_flag =0
        where gs.server_id in
        <foreach item="serverId" collection="array" open="(" separator="," close=")">
            #{serverId}
        </foreach>
        and o.del_flag = 0
        and o.order_state in ('000', '001','002','003','004','005','007')
    </select>
    <!--    查询该用户购买该服务的次数-->
    <select id="getCountOrder" resultMap="OrderResult" parameterType="com.younike.system.param.OrderGroupParam">
      select o.* from  `order` o
      LEFT JOIN order_goods og ON o.order_id = og.order_id and  og.del_flag =0
        where o.del_flag = 0
        and og.goods_id = #{order.goodsId}
        and o.member_id = #{order.memberId}
        and o.order_state in ('000', '001','002','003','004','005','006','007')
    </select>
    <!--    根据服务id以及服务人员id查询订单-->
    <select id="goodsVerify" resultMap="OrderResult" parameterType="com.younike.system.domain.vo.GoodsVo">
      select o.* from  `order` o
      LEFT JOIN order_goods og ON o.order_id = og.order_id and  og.del_flag =0
      LEFT JOIN order_goods_server ogs ON o.order_id = ogs.order_id and  ogs.del_flag =0
        where o.del_flag = 0
        and og.goods_id = #{order.goodsId}
        and ogs.server_id = #{order.serverId}
        and o.order_state in ('000', '001','002','003','004','005','007')
    </select>

    <!-- 查询待确认订单 -->
    <select id="listMemberOrderOfPendingConfirm" resultType="com.younike.system.domain.Order">
        SELECT
            o.order_id,
            o.member_id,
            o.merchant_id,
            o.merchant_name,
            o.order_type,
            o.order_state,
            o.comment_state,
            o.appointment_start_time,
            o.appointment_end_time,
            o.service_start_time,
            o.service_end_time,
            o.order_amount,
            o.total_amount,
            o.add_amount,
            o.create_time
        FROM `order` AS o
        WHERE o.del_flag = '0'
          AND o.service_end_time IS NOT NULL
          AND o.order_state = #{orderState}
        ORDER BY o.create_time
    </select>
<!--    查询预定订单结算信息-->
    <select id="listMerchantReserveSettleOrder" resultMap="OrderResult">
        SELECT
            o.*
        FROM
            `order` o
        WHERE
            (
                    o.order_state = '004'
                    OR o.order_state = '007'
                )
          AND ( o.order_type = '001' OR o.order_type='002')
          AND date_format(o.service_start_time,'%y%m%d') >= date_format(#{settleStartDate},'%y%m%d')
          AND date_format(o.service_start_time,'%y%m%d')  &lt;=  date_format(#{settleEndDate},'%y%m%d')
    </select>
<!--    查询团购订单结算信息-->
    <select id="listMerchantGroupSettleOrder" resultMap="OrderVoResult">
         SELECT
            o.*,
            count(ovr.order_verification_record_id) as groupTimes,
            ( o.total_amount/o.group_times*ifNull(count(ovr.order_verification_record_id),0)) as groupIncome
        FROM
            `order` o
            LEFT JOIN  order_verification_record ovr ON ovr.order_id = o.order_id
        WHERE
            o.order_type = '003'
          and ovr.del_flag=0
          AND date_format( ovr.appointment_time,'%y%m%d') >= date_format(#{settleStartDate},'%y%m%d')
          AND date_format( ovr.appointment_time,'%y%m%d')  &lt;=   date_format(#{settleEndDate},'%y%m%d')
          GROUP BY o.order_id
    </select>
<!-- 查询超过三天未处理的订单数据，包括商家以及商家联系电话    -->
    <select id="overtimeOrder" resultType="com.younike.system.domain.Order" resultMap="OvertimeOrderVoResult">
        select count(o.order_id) as orderCount,o.merchant_id,
        m.server_phone ,o.update_time,o.* from `order` o
        LEFT JOIN merchant m on m.merchant_id=o.merchant_id
        where o.order_state ='005'
        <if test="updateTime != null">
            AND date_format( o.update_time,'%y%m%d')  &lt;=   date_format(#{updateTime},'%y%m%d')
        </if>
        and o.del_flag =0
        and m.del_flag = 0
        GROUP BY o.merchant_id
    </select>

    <!-- 查询已过期的待服务团购订单列表 -->
    <select id="listExpiredGroupOrderOfPendingService" resultType="com.younike.system.domain.Order">
        SELECT
            o.order_id,
            o.member_id,
            o.merchant_id,
            o.merchant_name,
            o.order_type,
            o.order_state,
            o.comment_state,
            o.appointment_start_time,
            o.appointment_end_time,
            o.service_start_time,
            o.service_end_time,
            o.order_amount,
            o.total_amount,
            o.add_amount,
            o.create_time,
            o.verification_code_effective_date
        FROM `order` AS o
        WHERE o.del_flag = '0'
          AND o.verification_code_effective_date IS NOT NULL
          AND UNIX_TIMESTAMP(o.verification_code_effective_date) &lt; UNIX_TIMESTAMP(NOW())
          AND o.order_state = #{orderState}
        ORDER BY o.verification_code_effective_date
    </select>

<!--    查询所有「已退款，部分退款」预定订单，以商家进行分组-->
    <select id="listMerchantReserveRefundSettleOrder" resultMap="OrderResult">
        SELECT
         o.order_id,
         o.order_index,
         o.member_id,
         o.merchant_id,
         o.merchant_name,
         o.order_state,
         (o.total_amount-orr.refund_money) as total_amount
        FROM
            `order` o
            Left JOIN order_refund orr on o.order_id = orr.order_id
        WHERE
            o.order_state = '006'
            and orr.is_del =0
            and orr.refund_money &lt; o.total_amount
            and (orr.status ='002' OR  orr.status ='004')
          AND ( o.order_type = '001' OR o.order_type='002')
          AND date_format(o.service_start_time,'%y%m%d') >= date_format(#{settleStartDate},'%y%m%d')
          AND date_format(o.service_start_time,'%y%m%d')  &lt;=  date_format(#{settleEndDate},'%y%m%d')
    </select>
</mapper>
