From 65b88c68ed9955860a05b9480f17706d7c497806 Mon Sep 17 00:00:00 2001
From: gongzuming <gongzuming>
Date: 星期二, 29 十月 2024 11:34:06 +0800
Subject: [PATCH] 统计优化

---
 src/main/resources/mapper/payment/DeliveryOrderMapper.xml |   91 +++------------------------------------------
 1 files changed, 7 insertions(+), 84 deletions(-)

diff --git a/src/main/resources/mapper/payment/DeliveryOrderMapper.xml b/src/main/resources/mapper/payment/DeliveryOrderMapper.xml
index 73e81ed..7af2ae0 100644
--- a/src/main/resources/mapper/payment/DeliveryOrderMapper.xml
+++ b/src/main/resources/mapper/payment/DeliveryOrderMapper.xml
@@ -188,57 +188,20 @@
     </select>
 
     <select id="statisticStationList" resultType="com.mzl.flower.dto.response.flower.StationStatisticDTO">
-        select tt.*,
-        (SELECT
-        SUM( oi.num )
-        FROM
-        t_delivery_order o
-        LEFT JOIN t_delivery_order_item i ON o.id = i.delivery_id
-        LEFT JOIN t_order_item oi ON oi.id = i.order_item_id
-        LEFT JOIN t_supplier_info s ON s.id = o.supplier_id
-        LEFT JOIN t_station st ON st.id = s.station_id
-        LEFT JOIN t_order t ON t.id = i.order_id
-        WHERE o.deleted = 0 AND s.station_id = tt.stationId
-        AND o.`status` IN ( 'CHECKED' )
-        <if test="userId !=null and userId !=''">
-            AND st.user_ids LIKE concat('%', #{userId},'%')
-        </if>
-        <if test="name !=null and name !=''">
-            AND ( s.id LIKE concat('%', #{name},'%')  OR
-            s.contact_tel LIKE concat('%', #{name},'%') OR
-            s.`name` LIKE concat('%', #{name},'%') OR
-            st.`name` LIKE concat('%', #{name},'%') OR
-            t.warehouse_location_code LIKE concat('%', #{name},'%')
-            )
-        </if>
-        <if test="startDate != null">
-            AND o.create_time &gt; #{startDate}
-        </if>
-        <if test="endDate != null">
-            AND o.create_time &lt;= #{endDate}
-        </if>
-
-        <if test="statusList != null and statusList.size > 0">
-            AND o.status in
-            <foreach collection="statusList" item="status" open="(" separator="," close=")">
-                #{status}
-            </foreach>
-        </if>
-        GROUP BY s.station_id, st.`name`) checkedSupplierCount,
-            (
         SELECT
         s.station_id AS stationId,
         st.`name`,
-        SUM( oi.num ) AS totalNum
-        FROM
-        t_delivery_order o
+        SUM( oi.num ) AS totalNum,
+        SUM(CASE WHEN  o.`status` = 'CHECKED'  THEN oi.num ELSE 0 END) AS checkedSupplierCount,
+        SUM(CASE WHEN  o.`status` = 'PENDING' or o.`status`= 'ARRIVED' THEN oi.num ELSE 0 END) AS uncheckedSupplierCount
+        FROM t_delivery_order o
         LEFT JOIN t_delivery_order_item i ON o.id = i.delivery_id
         LEFT JOIN t_order_item oi ON oi.id = i.order_item_id
         LEFT JOIN t_supplier_info s ON s.id = o.supplier_id
         LEFT JOIN t_station st ON st.id = s.station_id
         LEFT JOIN t_order t ON t.id = i.order_id
-        WHERE o.deleted = 0 AND s.station_id = tt.stationId
-        AND o.`status` IN ( 'PENDING', 'ARRIVED')
+        WHERE o.deleted = 0
+        AND o.`status` IN ( 'PENDING', 'ARRIVED', 'CHECKED' )
         <if test="userId !=null and userId !=''">
             AND st.user_ids LIKE concat('%', #{userId},'%')
         </if>
@@ -263,47 +226,7 @@
                 #{status}
             </foreach>
         </if>
-        GROUP BY s.station_id, st.`name`) uncheckedSupplierCount
-        from (
-            SELECT
-                s.station_id AS stationId,
-                st.`name`,
-                SUM( oi.num ) AS totalNum
-            FROM
-            t_delivery_order o
-            LEFT JOIN t_delivery_order_item i ON o.id = i.delivery_id
-            LEFT JOIN t_order_item oi ON oi.id = i.order_item_id
-            LEFT JOIN t_supplier_info s ON s.id = o.supplier_id
-            LEFT JOIN t_station st ON st.id = s.station_id
-            LEFT JOIN t_order t ON t.id = i.order_id
-            WHERE o.deleted = 0
-            AND o.`status` IN ( 'PENDING', 'ARRIVED', 'CHECKED' )
-            <if test="userId !=null and userId !=''">
-                AND st.user_ids LIKE concat('%', #{userId},'%')
-            </if>
-            <if test="name !=null and name !=''">
-                AND ( s.id LIKE concat('%', #{name},'%')  OR
-                      s.contact_tel LIKE concat('%', #{name},'%') OR
-                      s.`name` LIKE concat('%', #{name},'%') OR
-                      st.`name` LIKE concat('%', #{name},'%') OR
-                      t.warehouse_location_code LIKE concat('%', #{name},'%')
-                    )
-            </if>
-            <if test="startDate != null">
-                AND o.create_time &gt; #{startDate}
-            </if>
-            <if test="endDate != null">
-                AND o.create_time &lt;= #{endDate}
-            </if>
-
-            <if test="statusList != null and statusList.size > 0">
-                AND o.status in
-                <foreach collection="statusList" item="status" open="(" separator="," close=")">
-                    #{status}
-                </foreach>
-            </if>
-            GROUP BY s.station_id, st.`name`
-        ) tt
+        GROUP BY s.station_id, st.`name`
     </select>
 
     <update id="setDeliveryOrderPending">

--
Gitblit v1.9.3