<?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.jumeirah.common.mapper.StrokeMapper">

    <!-- 分页列表查询结果列 -->
    <sql id="Page_Column_List">
        s.id,
        mu.id as merchantId,
        city_outset_id,
        city_arrive_id,
        people_num,
        plain_type_id,
        outset_time,
        return_time,
        type,
        STATUS,
        money,
        user_id,
        mu.username AS merchantName,
        arrive_plain_type_id,
        back_outset_airport_name,
        back_arrive_airport_name,
        payment_status,
        audit_status,
        user_recharge_bank,
        user_recharge_name,
        is_discount,
        user_recharge_bank_number

   </sql>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id,
        city_outset_id,
        deleted,
        city_outset_name,
        city_arrive_id,
        city_arrive_name,
        people_num,
        plain_type_id,
        outset_time,
        return_time,
        type,
        status,
        create_time,
        update_time,
        goods_name,
        goods_size,
        goods_weight,
        disease_name,
        instruments,
        medical_persons,
        remarks, money,
        user_id,
        outset_airport_name,
        arrive_airport_name,
        arrive_plain_type_id,
        back_outset_airport_name,
        back_arrive_airport_name,
        payment_status,audit_status,
        user_recharge_money,
        user_recharge_bank,
        user_recharge_name,
        user_recharge_bank_number,
        is_discount,
        user_recharge_credentials_url
    </sql>

    <select id="getStrokeById" resultType="com.jumeirah.common.vo.StrokeDetailVo">
        select
        <include refid="Base_Column_List"/>
        from stroke where id = #{id}
    </select>

    <select id="getStrokePageList" parameterType="com.jumeirah.common.param.StrokePageParam"
            resultType="com.jumeirah.common.vo.StrokeQueryVo">
        select
            ctco.city_name_cn as city_outset_name,
            ctco.airport_name_cn as outset_airport_name,
            ctca.city_name_cn as city_arrive_name,
            ctca.airport_name_cn as arrive_airport_name,
            <include refid="Page_Column_List"/>
        from stroke s
        INNER JOIN merchant_user mu ON mu.id = s.mc_id
        INNER JOIN city_three_code ctco ON ctco.id = s.city_outset_id
        INNER JOIN city_three_code ctca ON ctca.id = s.city_arrive_id
        <where>
            and s.user_id = #{userId}
            and s.deleted = 0

            <if test="param.status != -1">
                -- 查询全部 = -1
                and s.`status` = #{param.status}
            </if>
        </where>

    </select>

    <!--    已完成订单-->
    <select id="getStrokePageListWithFinish" parameterType="com.jumeirah.common.param.StrokePageParam"
            resultType="com.jumeirah.common.vo.StrokeQueryVo">
        select
            ctco.city_name_cn as city_outset_name,
            ctco.airport_name_cn as outset_airport_name,
            ctca.city_name_cn as city_arrive_name,
            ctca.airport_name_cn as arrive_airport_name,
            <include refid="Page_Column_List"/>
        from stroke s
        INNER JOIN merchant_user mu ON mu.id = s.mc_id
        INNER JOIN city_three_code ctco ON ctco.id = s.city_outset_id
        INNER JOIN city_three_code ctca ON ctca.id = s.city_arrive_id
        <where>
            and s.user_id = #{userId}
            and s.deleted = 0
            and s.`status` = #{param.status}
            or s.`status` = 99
        </where>

    </select>
    <select id="getMcStrokePageList" parameterType="com.jumeirah.common.param.McStrokePageParam"
            resultType="com.jumeirah.common.vo.McStrokeQueryVo">
        SELECT
            s.*,
            ctco.city_name_cn as city_outset_name,
            ctco.airport_name_cn as outset_airport_name,
            ctca.city_name_cn as city_arrive_name,
            ctca.airport_name_cn as arrive_airport_name,
            CONCAT( au.surname, au.`name` ) AS applicant,
            pt.name AS plain_type_name,
            au.phone AS phone_number
        FROM
            stroke s
            LEFT JOIN app_user au ON au.id = s.user_id
            LEFT JOIN plain_type pt ON pt.id = s.plain_type_id
            INNER JOIN city_three_code ctco ON ctco.id = s.city_outset_id
            INNER JOIN city_three_code ctca ON ctca.id = s.city_arrive_id
            <where>
                <if test="mcStrokePageParam.status != null and mcStrokePageParam.status != -1">
                    AND s.status = #{mcStrokePageParam.status}
                </if>
                <if test="mcStrokePageParam.type != null and mcStrokePageParam.type != -1">
                    AND s.type = #{mcStrokePageParam.type}
                </if>
                <if test="mcStrokePageParam.id != null and mcStrokePageParam.id != 0">
                    AND s.id = #{mcStrokePageParam.id}
                </if>
                <if test="mcStrokePageParam.startTime != null and mcStrokePageParam.startTime != ''">
                    AND s.create_time &gt;= #{mcStrokePageParam.startTime}
                </if>
                <if test="mcStrokePageParam.endTime != null and mcStrokePageParam.endTime != ''">
                    AND s.create_time &lt;= #{mcStrokePageParam.endTime}
                </if>
                <if test="mcStrokePageParam.applicant != null and mcStrokePageParam.applicant != ''">
                    AND CONCAT( au.surname, au.`name` ) LIKE CONCAT('%',#{mcStrokePageParam.applicant},'%')
                </if>
            </where>
            ORDER BY s.create_time DESC
    </select>

    <select id="getPaymentPageList" parameterType="com.jumeirah.common.param.McStrokePaymentPageParam"
            resultType="com.jumeirah.common.vo.McStrokePaymentQueryVo">
        SELECT
            s.id,
            s.type,
            s.user_recharge_time,
            s.payment_status,
            s.user_recharge_money,
            s.payment_channel,
            CONCAT( au.surname, au.`name` ) AS applicant,
            au.phone AS phone_number
        FROM
            stroke s
        LEFT JOIN app_user au ON au.id = s.user_id
        <where>
            <if test="param.paymentStatus != null and param.paymentStatus != -1">
                AND s.status = #{param.paymentStatus}
            </if>
            <if test="param.id != null and param.id != 0">
                AND s.id = #{param.id}
            </if>
            <if test="param.startTime != null and param.startTime != ''">
                AND s.create_time &gt;= #{param.startTime}
            </if>
            <if test="param.endTime != null and param.endTime != ''">
                AND s.create_time &lt;= #{param.endTime}
            </if>
        </where>
        ORDER BY s.create_time DESC
    </select>

</mapper>
