<?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.njzscloud.dispose.cst.order.mapper.OrderMapper">
    <!--
     SELECT a.id
FROM (SELECT a.id
      FROM cst_order a
               LEFT JOIN cst_project f ON f.id = a.project_id
               INNER JOIN cst_org j ON j.id = a.trans_org_id
      WHERE 1 = 1
        AND EXISTS (SELECT 1 FROM cst_order_trans b WHERE b.order_id = a.id AND b.trans_status = 'DaiPaiDan')
        AND EXISTS (SELECT 1 FROM cst_order_trans b WHERE b.order_id = a.id AND b.trans_status IN ('YiJieDan', 'YunShuZhong', 'YiJinChang', 'YiChuChang'))
        AND EXISTS (SELECT 1 FROM cst_order_trans b WHERE b.order_id = a.id AND b.trans_status = 'YiWanCheng')
        AND EXISTS (SELECT 1 FROM cst_order_trans b WHERE b.order_id = a.id AND b.trans_status = 'YiQuXiao')

        AND a.order_category IN (?)

        AND a.sn = ?
        AND a.station_name = ?
        AND a.contacts = ?
        AND a.phone = ?
        AND j.org_name = ?
        AND f.project_name = ?
        AND a.order_time = ?


      ORDER BY a.create_time DESC, a.id DESC
      LIMIT 5, 10) temp
         INNER JOIN cst_order a ON a.id = temp.id
         INNER JOIN cst_order_trans b ON b.order_id = a.id
         LEFT JOIN cst_order_cargo_place c ON c.order_id = a.id
         LEFT JOIN cst_order_expense_items d ON d.order_id = a.id
         LEFT JOIN cst_order_payment_record e ON e.order_id = a.id
         LEFT JOIN cst_project f ON f.id = a.project_id
         LEFT JOIN (SELECT a.*
                    FROM cst_driver a
                             INNER JOIN cst_customer b ON b.id = a.customer_id
                             INNER JOIN cst_org c ON c.id = a.org_id) g ON g.id = b.driver_id
         LEFT JOIN (SELECT a.*
                    FROM cst_truck a
                             INNER JOIN cst_customer b ON b.id = a.customer_id
                             INNER JOIN cst_org c ON c.id = a.org_id) h ON h.id = b.truck_id
         INNER JOIN cst_station i ON i.id = a.station_id
         -->
    <select id="paging" resultType="com.njzscloud.dispose.cst.order.pojo.result.SearchOrderResult">
        SELECT a.id,
        a.sn,
        a.project_id,
        a.user_id,
        a.customer_id,
        a.contacts,
        a.phone,
        a.order_time,
        a.order_category,
        a.order_status,
        a.finish_time,
        a.trans_org_id,
        a.trans_customer_id,
        a.assignment_trans_time,
        a.station_id,
        a.station_name,
        a.trans_distance,
        a.estimated_quantity,
        a.estimated_train_num,
        a.goods_id,
        a.goods_name,
        j.picture goods_picture,
        a.unit,
        a.customer_memo,
        a.create_time
        FROM (SELECT a.id
        FROM cst_order a
        INNER JOIN cst_order_trans b ON b.order_id = a.id
        LEFT JOIN cst_project f ON f.id = a.project_id
        INNER JOIN cst_org j ON j.id = a.trans_org_id
        <if test="ew.customSqlSegment != null and ew.customSqlSegment != ''">
            ${ew.customSqlSegment}
        </if>
        ) temp
        INNER JOIN cst_order a ON a.id = temp.id
        INNER JOIN gds_goods j ON j.id = a.goods_id
    </select>
    <select id="detail" resultType="com.njzscloud.dispose.cst.order.pojo.result.SearchOrderResult">
        SELECT id,
               sn,
               project_id,
               user_id,
               customer_id,
               contacts,
               phone,
               order_time,
               order_category,
               order_status,
               finish_time,
               trans_org_id,
               trans_customer_id,
               assignment_trans_time,
               station_id,
               station_name,
               trans_distance,
               estimated_quantity,
               estimated_train_num,
               goods_id,
               goods_name,
               unit,
               customer_memo,
               create_time
        FROM cst_order
        WHERE id = #{id}
    </select>

    <select id="miniDetail" resultType="com.njzscloud.dispose.cst.order.pojo.result.OrderResult">
        SELECT
            co.id,
            co.sn,
            co.create_time,
            gg.goods_name,
            gg.picture,
            co.estimated_quantity,
            cfco.org_name AS cf_org_name,
            co.contacts,
            co.phone,
            CONCAT(cocp.province_name,cocp.city_name,cocp.area_name,cocp.town_name,cocp.address) AS cf_address,
            tco.org_name AS trans_org_name,
            tcc.customer_name AS trans_contacts,
            tcc.phone AS trans_phone,
            CONCAT(tco.province_name,tco.city_name,tco.area_name,tco.town_name,tco.address) AS trans_address,
            co.station_name,
            scc.customer_name AS station_contacts,
            scc.phone AS station_phone,
            CONCAT(cs.province_name,cs.city_name,cs.area_name,cs.town_name,cs.address) AS station_address
        FROM
            cst_order co
            LEFT JOIN gds_goods gg ON gg.id = co.goods_id
            LEFT JOIN cst_customer cfcc ON cfcc.id = co.customer_id
            LEFT JOIN cst_org cfco ON cfco.id = cfcc.org_id
            LEFT JOIN cst_order_cargo_place cocp ON cocp.order_id = co.id
            LEFT JOIN cst_customer tcc ON tcc.id = co.trans_customer_id
            LEFT JOIN cst_org tco ON tco.id = co.trans_org_id
            LEFT JOIN cst_station cs ON cs.id = co.station_id
            LEFT JOIN cst_customer scc ON scc.org_id = cs.org_id and scc.manager = 1 and scc.deleted = 0
        WHERE co.id = #{id}
    </select>

    <select id="getPaymentRecordByOrderId" resultType="com.njzscloud.dispose.cst.order.pojo.result.OrderPaymentRecordResult">
        SELECT
            coei.expense_item_name,
            SUM(copr.settle_money) AS total_settle_money
        FROM
            cst_order_payment_record copr
            LEFT JOIN cst_order_expense_items coei ON coei.id = copr.expense_item_id
        WHERE copr.order_id = #{orderId}
        GROUP BY coei.origin_expense_items_id
    </select>

</mapper>


