<?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.BaseOrderMapper">

    <!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.xxfc.platform.order.entity.BaseOrder" id="baseOrderMap">
        <result property="id" column="id"/>
        <result property="no" column="no"/>
        <result property="type" column="type"/>
        <result property="detailId" column="detail_id"/>
        <result property="status" column="status"/>
        <result property="productAmount" column="product_amount"/>
        <result property="orderAmount" column="order_amount"/>
        <result property="detailJson" column="detail_json"/>
        <result property="thirdType" column="third_type"/>
        <result property="outTradeNo" column="out_trade_no"/>
        <result property="crtTime" column="crt_time"/>
        <result property="crtUser" column="crt_user"/>
        <result property="crtName" column="crt_name"/>
        <result property="crtHost" column="crt_host"/>
        <result property="updTime" column="upd_time"/>
        <result property="updUser" column="upd_user"/>
        <result property="updName" column="upd_name"/>
        <result property="updHost" column="upd_host"/>
    </resultMap>

    <resultMap type="com.xxfc.platform.order.pojo.order.OrderPageVO" id="orderPageMap">
        <result javaType="Integer" column="type" property="type"></result>
        <discriminator javaType="Integer" column="type">
            <case value="1" resultType="com.xxfc.platform.order.pojo.order.OrderPageVO">
                <association column="{id=detail_id}" property="orderRentVehicleDetail"
                             select="com.xxfc.platform.order.mapper.OrderRentVehicleDetailMapper.pageByParm"
                             javaType="com.xxfc.platform.order.entity.OrderRentVehicleDetail"></association>
            </case>
            <case value="2" resultType="com.xxfc.platform.order.pojo.order.OrderPageVO">
                <association column="detail_id" property="orderTourDetail"
                             select="com.xxfc.platform.order.mapper.OrderTourDetailMapper.selectByPrimaryKey"
                             javaType="com.xxfc.platform.order.entity.OrderTourDetail"></association>
            </case>
            <case value="3" resultType="com.xxfc.platform.order.pojo.order.OrderPageVO">
                <association column="detail_id" property="orderMemberDetail"
                             select="com.xxfc.platform.order.mapper.OrderMemberDetailMapper.selectByPrimaryKey"
                             javaType="com.xxfc.platform.order.entity.OrderMemberDetail"></association>
            </case>
        </discriminator>
    </resultMap>


    <resultMap type="com.xxfc.platform.order.pojo.order.OrderListVo" id="orderListMap">
        <result javaType="Integer" column="type" property="type"></result>
        <discriminator javaType="Integer" column="type">
            <case value="1" resultType="com.xxfc.platform.order.pojo.order.OrderListVo">
                <association column="{id=detail_id}" property="orderRentVehicleDetail"
                             select="com.xxfc.platform.order.mapper.OrderRentVehicleDetailMapper.pageByParm"
                             javaType="com.xxfc.platform.order.entity.OrderRentVehicleDetail"></association>
            </case>
            <case value="2" resultType="com.xxfc.platform.order.pojo.order.OrderListVo">
                <association column="detail_id" property="orderTourDetail"
                             select="com.xxfc.platform.order.mapper.OrderTourDetailMapper.selectByPrimaryKey"
                             javaType="com.xxfc.platform.order.entity.OrderTourDetail"></association>
            </case>
            <case value="3" resultType="com.xxfc.platform.order.pojo.order.OrderListVo">
                <association column="detail_id" property="orderMemberDetail"
                             select="com.xxfc.platform.order.mapper.OrderMemberDetailMapper.selectByPrimaryKey"
                             javaType="com.xxfc.platform.order.entity.OrderMemberDetail"></association>
            </case>
        </discriminator>
    </resultMap>

    <select id="pageByParm" parameterType="Map" resultMap="orderPageMap">
        select *
        from base_order
        <where>
            <if test="crtUser != null">
                and crt_user = #{crtUser}
            </if>
            <if test="userId != null">
                and user_id = #{userId}
            </if>
            <if test="crtCompanyId != null">
                and crt_company_id = #{crtCompanyId}
            </if>
            <if test="status != null">
                and status = #{status}
            </if>
            <if test="no != null">
                and no = #{no}
            </if>
            <if test="type != null">
                and type = #{type}
            </if>
            <if test="hasMemberRight != null">
                and has_member_right = #{hasMemberRight}
            </if>
            <if test="multiStatus != null">
                and
                (
                <foreach collection="multiStatus.split(',')" index="sIndex" item="statusEntity" separator=" or ">
                    status = #{statusEntity}
                </foreach>
                )
            </if>
            <if test="multiTypes != null">
                and
                (
                <foreach collection="multiTypes.split(',')" index="tIndex" item="typeEntity" separator=" or ">
                    type = #{typeEntity}
                </foreach>
                )
            </if>
        </where>
        order by crt_time desc
    </select>

    <select id="listOrder" parameterType="Map" resultMap="orderListMap">
        select b.*
        <if test="type==1">
            ,i.detail  as carArticlesJson
        </if>
        from base_order b
        LEFT JOIN order_rent_vehicle_detail r on r.order_id = b.id
        LEFT JOIN order_tour_detail t on t.order_id = b.id
        LEFT JOIN order_member_detail m on m.order_id = b.id
        <if test="type==1">
            LEFT JOIN (select * from  order_item where type = 104) i on  b.id = i.order_id
        </if>
        <where>
            <if test="crtUser != null">
                and b.crt_user = #{crtUser}
            </if>
            <if test="userIds != null and userIds.size() > 0">
                and b.user_id in
                <foreach collection="userIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>

            </if>
            <if test="userId != null">
                and b.user_id = #{userId}
            </if>
            <if test="status != null">
                and b.status = #{status}
            </if>
            <if test="type != null">
                and b.type = #{type}
            </if>
            <if test="memberLevel != null and memberLevel != ''">
                and m.member_level = #{memberLevel}
            </if>
            <if test="no != null and no != '' ">
                and no like CONCAT ("%", #{no}, "%")
            </if>
            <if test="name != null">
                and b.name like CONCAT ("%", #{name}, "%")
            </if>
            <if test="startTime != null">
                and (r.start_time between #{startTime} and #{endTime}
                or t.start_time between #{startTime} and #{endTime})
            </if>
            <if test="vehicleIds != null and vehicleIds.size() > 0">
                AND r.vehicle_id IN
                <foreach collection="vehicleIds" item="vehicleId" open="(" close=")" separator=",">
                    #{vehicleId}
                </foreach>
            </if>
            <if test="companyIds != null and companyIds.size > 0">
                and (r.start_company_id in
                <foreach collection="companyIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
                or t.start_company_id in
                <foreach collection="companyIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
                )
            </if>
            <if test="zoneIds != null and zoneIds.size > 0">
                and (r.start_zone_id in
                <foreach collection="zoneIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
                or t.start_zone_id in
                <foreach collection="zoneIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
                )
            </if>
        </where>
        order by b.crt_time desc
    </select>

    <select id="getRentVehicle" parameterType="Map" resultMap="orderPageMap">
        select b.*
        from base_order b
        LEFT JOIN order_rent_vehicle_detail r on r.order_id = b.id
        where b.type = 1
        <if test="userIds != null and userIds.size > 0">
            and b.user_id in
            <foreach collection="userIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
        <if test="status != null and status == -1">
            AND b.status = -1
            and b.refund_status in (0,2)
        </if>
        <if test="status != null and status != -1">
            and b.status = #{status}
        </if>
        <if test="no != null and no != ''">
            and no like CONCAT ("%", #{no}, "%")
        </if>
        <if test="startTime != null and status == 4">
            and r.start_time between #{startTime} and #{endTime}
        </if>
        <if test="startTime != null and (status == 5 || status == 6 || status == -1)">
            and r.end_time between #{startTime} and #{endTime}
        </if>
        <if test="companyIds != null and companyIds.size > 0">
            and (r.start_company_id in
            <foreach collection="companyIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
            or
            r.end_company_id in
            <foreach collection="companyIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
            )
        </if>
        <if test="status == 4">
            order by r.start_time
        </if>
        <if test="status == 5 || status == -1">
            order by r.end_time
        </if>

    </select>
    <select id="getAllOrderList" parameterType="Map" resultType="com.xxfc.platform.order.pojo.bg.BgOrderListVo">
        SELECT * from order_list_info
        <where>
            <if test="userIds != null and userIds.size() > 0">
                and userId in
                <foreach collection="userIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="phone != null and phone != ''">
                and telephone like CONCAT ("%", #{phone}, "%")
            </if>
            <if test="realName != null and realName != ''">
                and username like CONCAT ("%", #{realName}, "%")
            </if>
            <if test="userId != null">
                and userId = #{userId}
            </if>
            <if test="status != null">
                and status = #{status}
            </if>
            <if test="type != null">
                and type = #{type}
            </if>
            <if test="no != null and no != '' ">
                and orderNo like CONCAT ("%", #{no}, "%")
            </if>
            <if test="plateNumber != null and plateNumber != '' ">
                and numberPlate like CONCAT ("%", #{plateNumber}, "%")
            </if>
            <if test="vehicleCode != null">
                and vehicleCode = #{vehicleCode}
            </if>
            <if test="startTime != null">
                and startTime between #{startTime} and #{endTime}
            </if>
            <if test="companyIds != null and companyIds.size > 0">
                and (startCompanyId in
                <foreach collection="companyIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
                or
                endCompanyId in
                <foreach collection="companyIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
                )
            </if>
            <if test="zoneId != null">
                and (startZoneId = #{zoneId} or endZoneId = #{zoneId})
            </if>
        </where>
        order by crtTime desc
    </select>

    <select id="getTourList" parameterType="Map" resultMap="orderPageMap">
        select b.*
        from base_order b
        LEFT JOIN order_tour_detail r on r.order_id = b.id
        where b.type = 2
        <if test="userId != null">
            and b.user_id = #{userId}
        </if>
        <if test="status != null">
            and b.status = #{status}
        </if>
        <if test="no != null">
            and no like CONCAT ("%", #{no}, "%")
        </if>
        <if test="startTime != null">
            and r.start_time between #{startTime} and #{endTime}
        </if>
        <if test="startCompanyId != null">
            and r.start_company_id = #{startCompanyId}
        </if>
        order by b.crt_time desc
    </select>

    <select id="selectAllCountOrder" parameterType="Map" resultMap="orderPageMap">
        SELECT bo.* from base_order bo
        LEFT JOIN order_rent_vehicle_detail orv on bo.id = orv.order_id
        where bo.type = 1 and (bo.status >= 4 or bo.status = -1)
        and
        (
        (
        orv.start_time &gt; #{startTime}
        AND orv.start_time &lt; #{startTime}
        AND orv.end_time &gt; #{endTime}
        )
        OR (
        orv.start_time &lt; #{startTime}
        AND orv.end_time &gt; #{endTime}
        )
        OR (
        orv.start_time &lt; #{startTime}
        AND orv.end_time &gt; #{endTime}
        AND orv.end_time &lt; #{endTime}
        )
        OR (
        orv.start_time &gt; #{startTime}
        AND orv.end_time &lt; #{endTime}
        )
        )
    </select>

    <select id="getOrderDetail" parameterType="java.lang.String" resultMap="orderPageMap">
        select *
        from base_order
        where no = #{no}
    </select>

    <select id="selectAllRentVehicleOrder" parameterType="Map" resultMap="orderPageMap">
        select b.*
        from base_order b
        LEFT JOIN order_rent_vehicle_detail r on r.order_id = b.id
        where b.type = 1 and (b.status &gt;= 4 or b.status = -1)
        <if test="startTime != null and status == 1">
            and r.start_time between #{startTime} and #{endTime}
        </if>
        <if test="startTime != null and status == 2">
            and r.start_time between #{startTime} and #{endTime}
        </if>
    </select>

    <select id="selectAllTourOrder" parameterType="Map" resultMap="orderPageMap">
        select b.*
        from base_order b
        LEFT JOIN order_tour_detail r on r.order_id = b.id
        where b.type = 2 and b.status &gt;= 4
        <if test="startTime != null">
            and r.start_time between #{startTime} and #{endTime}
        </if>
    </select>

    <select id="findMemberOrders" resultType="com.xxfc.platform.order.pojo.dto.MemberOrderBo">
    select bo.*,omd.member_level AS `memberLevel` from (
        SELECT
        `id`,
        `no` AS `orderNo`,
        `name`,
        `status`,
        `goods_amount` AS `goodsAmount`,
        `real_amount` AS `realAmount`,
        `order_amount` AS `orderAmount`,
        `crt_time` AS `creatTime`,
        `pay_time` AS `payTime`,
        `coupon_amount` AS `couponAmount`,
        `user_id` AS `userId`,
        `has_pay` AS `hasPay`,
        `facilitate_id` AS `facilitateId`,
        `facilitate_phone` AS `facilitatePhone`
        FROM
        `base_order`
        WHERE
        type = 3
        <if test="orderNo!=null and orderNo!=''">
            and `no`=#{orderNo}
        </if>
        <if test="state!=null">
            and `status`=#{state}
        </if>
        <if test="facilitatePhone!=null and facilitatePhone!=''">
            and `facilitate_phone`=#{facilitatePhone}
        </if>
        <if test="startOrderTime!=null and endOrderTime!=null">
            and `crt_time` between #{startOrderTime} and #{endOrderTime}
        </if>
        <if test="startOrderTime!=null and endOrderTime==null">
            and `crt_time` <![CDATA[>=#{startOrderTime}]]>
        </if>
        <if test="endOrderTime!=null and startOrderTime==null">
            and `crt_time` <![CDATA[<=#{endOrderTime}]]>
        </if>
        <if test="startPayTime!=null and endPayTime!=null">
            and `pay_time` between #{startPayTime} and #{endPayTime}
        </if>
        <if test="startPayTime!=null and endPayTime==null">
            and `pay_time` <![CDATA[>=#{startPayTime}]]>
        </if>
        <if test="endPayTime!=null and startPayTime==null">
            and `pay_time` <![CDATA[<=#{endPayTime}]]>
        </if>
        <if test="userIds!=null and userIds.size()>0">
            and `user_id` IN
            <foreach collection="userIds" item="userId" separator="," open="(" close=")">
                #{userId}
            </foreach>
        </if>
        ) AS `bo`
        INNER JOIN (select  * from `order_member_detail` where  1=1
        <if test="level!=null">
          and  `member_level`=#{level}
        </if>
        ) AS `omd` ON omd.order_id=bo.id
        ORDER BY bo.`creatTime` DESC
    </select>

    <select id="selectOrdersByTypeAndTime" resultType="com.xxfc.platform.order.pojo.dto.OrderDTO">
        select bo.id,bo.type,bo.status,bo.order_amount,bo.real_amount,bo.order_origin,bo.parent_user_id as `userId`,bo.parent_position_id as `postionId`,
        bo.has_pay,bo.pay_way,omd.memberLevel,IFNULL(IFNULL(orvd.start_company_id,otd.start_company_id),`parent_user_company_id`) AS `companyId`,
        orvd.deposit,orvd.cost_detail AS `costDetail`
        from (select * from `base_order` where 1=1
         <if test="hasPay!=null">
           and   `has_pay`=#{hasPay}
         </if>
      and  (`crt_time` between #{startDate} and #{endDate} or `pay_time` between #{startDate} and #{endDate} ) and `type` IN <foreach collection="types"
                                                                                                      item="type"
                                                                                                      open="(" close=")"
                                                                                                      separator=",">
        #{type}
    </foreach> ) AS `bo`
        LEFT JOIN (select `order_id`,`start_company_id`,`deposit`,`cost_detail` from `order_rent_vehicle_detail`) AS `orvd` ON
        orvd.order_id=bo.id
        LEFT JOIN (select `order_id`,`start_company_id` from `order_tour_detail`) AS `otd` ON otd.order_id=bo.id
        LEFT JOIN (select `order_id`,`member_level` AS `memberLevel` from `order_member_detail`) AS `omd` ON
        omd.order_id=bo.id;
    </select>

    <select id="selectBaeOrderByOrderIds" resultType="com.xxfc.platform.order.pojo.dto.OrderDTO">
        select `id`,`parent_user_company_id` as `companyId` from `base_order` where id in
        <foreach collection="orderIds" item="orderId" open="(" close=")" separator=",">
            #{orderId}
        </foreach>
    </select>


    <select id="selectTotalStatistical" parameterType="com.xxfc.platform.order.pojo.QueryCriteria"
            resultType="com.xxfc.platform.order.pojo.Achievement">
        select
        crt_time,no,type,order_amount,real_amount,realname,username,parentRealname,positionName,parentUsername,companyName,pay_way,company_id,(order_amount-real_amount) as favorablePrice
        from order_user_position_company
        <where>
            <if test="startDate != null">
                and crt_time <![CDATA[>=]]>  #{startDate}
            </if>
            <if test="endDate != null">
                and crt_time <![CDATA[<=]]>  #{endDate}
            </if>
            <if test="paymentMethod !=null">
                and pay_way = #{paymentMethod}
            </if>
            <if test="jurisdiction != null and jurisdiction.size() !=0 ">
                and company_id in
                <foreach collection="jurisdiction" index="index" item="companyId" open="(" separator="," close=")">
                    #{companyId}
                </foreach>
            </if>
            <if test="companyId != null ">
                and company_id =#{companyId}
            </if>
            <if test="orderType != null">
                and type = #{orderType}
            </if>
        </where>
        order by crt_time DESC
    </select>

</mapper>