<?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.gxwebsoft.glt.mapper.GltTicketOrderMapper">

    <!-- 关联查询sql -->
    <sql id="selectSql">
        SELECT a.*, b.name as storeName, b.address as storeAddress, b.phone as storePhone, b.lng_and_lat as storeLngAndLat,
        w.name as warehouseName, w.address as warehouseAddress, w.phone as warehousePhone, w.lng_and_lat as warehouseLngAndLat,
        c.real_name as riderName, c.mobile as riderPhone,
        u.nickname, u.phone, u.avatar,
        d.name as receiverName, d.phone as receiverPhone,
        d.province as receiverProvince, d.city as receiverCity, d.region as receiverRegion,
        d.address as receiverAddress, d.full_address as receiverFullAddress, d.lat as receiverLat, d.lng as receiverLng,
        COALESCE(o.order_no, f.order_no) as orderNo, o.order_status as orderStatus
        FROM glt_ticket_order a
        LEFT JOIN shop_store b ON a.store_id = b.id
        LEFT JOIN shop_store_warehouse w ON a.warehouse_id = w.id
        LEFT JOIN shop_store_rider c ON a.rider_id = c.user_id
        LEFT JOIN gxwebsoft_core.sys_user u ON a.user_id = u.user_id
        LEFT JOIN shop_user_address d ON a.address_id = d.id
        LEFT JOIN glt_user_ticket f ON a.user_ticket_id = f.id
        LEFT JOIN shop_order o ON f.order_id = o.order_id AND f.tenant_id = o.tenant_id AND o.deleted = 0

        <where>
            <if test="param.id != null">
                AND a.id = #{param.id}
            </if>
            <if test="param.userTicketId != null">
                AND a.user_ticket_id = #{param.userTicketId}
            </if>
            <if test="param.storeId != null">
                AND a.store_id = #{param.storeId}
            </if>
            <if test="param.riderId != null">
                <if test="param.riderId == 0">
                    AND (a.rider_id IS NULL OR a.rider_id = 0)
                </if>
                <if test="param.riderId != 0">
                    AND a.rider_id = #{param.riderId}
                </if>
            </if>
            <if test="param.deliveryStatus != null">
                AND a.delivery_status = #{param.deliveryStatus}
            </if>
            <if test="param.warehouseId != null">
                AND a.warehouse_id = #{param.warehouseId}
            </if>
            <if test="param.addressId != null">
                AND a.address_id = #{param.addressId}
            </if>
            <if test="param.address != null">
                AND a.address LIKE CONCAT('%', #{param.address}, '%')
            </if>
            <if test="param.buyerRemarks != null">
                AND a.buyer_remarks LIKE CONCAT('%', #{param.buyerRemarks}, '%')
            </if>
            <if test="param.price != null">
                AND a.price = #{param.price}
            </if>
            <if test="param.totalNum != null">
                AND a.total_num = #{param.totalNum}
            </if>
            <if test="param.userId != null">
                AND a.user_id = #{param.userId}
            </if>
            <if test="param.sortNumber != null">
                AND a.sort_number = #{param.sortNumber}
            </if>
            <if test="param.comments != null">
                AND a.comments LIKE CONCAT('%', #{param.comments}, '%')
            </if>
            <if test="param.status != null">
                AND a.status = #{param.status}
            </if>
            <if test="param.tenantId != null">
                AND a.tenant_id = #{param.tenantId}
            </if>
            <if test="param.deleted != null">
                AND a.deleted = #{param.deleted}
            </if>
            <if test="param.deleted == null">
                AND a.deleted = 0
            </if>
            <if test="param.createTimeStart != null">
                AND a.create_time &gt;= #{param.createTimeStart}
            </if>
            <if test="param.createTimeEnd != null">
                AND a.create_time &lt;= #{param.createTimeEnd}
            </if>
            <if test="param.orderNo != null">
                AND (a.id = #{param.orderNo} OR COALESCE(o.order_no, f.order_no) = #{param.orderNo})
            </if>
            <if test="param.phone != null">
                AND u.phone = #{param.phone}
            </if>
            <if test="param.keywords != null">
                AND (
                    a.address LIKE CONCAT('%', #{param.keywords}, '%')
                    OR a.buyer_remarks LIKE CONCAT('%', #{param.keywords}, '%')
                    OR a.comments LIKE CONCAT('%', #{param.keywords}, '%')
                    OR b.name LIKE CONCAT('%', #{param.keywords}, '%')
                    OR u.nickname LIKE CONCAT('%', #{param.keywords}, '%')
                    OR u.phone LIKE CONCAT('%', #{param.keywords}, '%')
                )
            </if>
        </where>
    </sql>

    <!-- 分页查询 -->
    <select id="selectPageRel" resultType="com.gxwebsoft.glt.entity.GltTicketOrder">
        <include refid="selectSql"></include>
    </select>

    <!-- 查询全部 -->
    <select id="selectListRel" resultType="com.gxwebsoft.glt.entity.GltTicketOrder">
        <include refid="selectSql"></include>
    </select>

</mapper>
