From c86ee10a9aff6c137fd230350c66941b90965362 Mon Sep 17 00:00:00 2001
From: zhanghl <253316343@qq.com>
Date: 星期三, 21 五月 2025 17:14:07 +0800
Subject: [PATCH] Merge remote-tracking branch 'origin/dev' into dev
---
aps-modules/aps-core/src/main/resources/mapper/core/ApsWeldSeamStatisticsV2Mapper.xml | 112 ++++++++++++++++++++++++++++----------------------------
1 files changed, 56 insertions(+), 56 deletions(-)
diff --git a/aps-modules/aps-core/src/main/resources/mapper/core/ApsWeldSeamStatisticsV2Mapper.xml b/aps-modules/aps-core/src/main/resources/mapper/core/ApsWeldSeamStatisticsV2Mapper.xml
index 7c44838..2e01184 100644
--- a/aps-modules/aps-core/src/main/resources/mapper/core/ApsWeldSeamStatisticsV2Mapper.xml
+++ b/aps-modules/aps-core/src/main/resources/mapper/core/ApsWeldSeamStatisticsV2Mapper.xml
@@ -153,95 +153,95 @@
</insert>
<select id="queryWeldSeamStatistics" resultMap="ApsWeldSeamStatisticsV2Result">
- WITH 宸ュ崟鐒婄紳璁＄畻 AS (
+ WITH order_weld_calc AS (
-- 璁＄畻宸ュ崟鐨勭剨缂濇暟閲�
SELECT
- mo.factory as 鐢熶骇鍩哄湴,
- mo.material_code as 鏂欏彿,
- EXTRACT(YEAR FROM mo.plan_end) as 骞翠唤,
- EXTRACT(MONTH FROM mo.plan_end) as 鏈堜唤,
- mo.quantity * COALESCE(ws.hup_qty, 0) as 宸ュ崟鐒婄紳鏁�,
- mgm.domain as 涓撲笟
+ mo.factory as production_base,
+ mo.material_code as material_no,
+ EXTRACT(YEAR FROM mo.plan_end) as year,
+ EXTRACT(MONTH FROM mo.plan_end) as month,
+ mo.quantity * COALESCE(ws.hup_qty, 0) as order_weld_count,
+ mgm.domain as domain
FROM aps_gas_pipeline_mo mo
LEFT JOIN aps_weld_seam_standard ws ON mo.material_code = ws.item_code AND ws.del_flag = 0
LEFT JOIN aps_material_product_group_management mgm ON mo.material_code = mgm.material_code AND mo.factory = mgm.factory
),
- 棰勬祴鐒婄紳璁＄畻 AS (
+ predict_weld_calc AS (
-- 璁＄畻棰勬祴鐨勭剨缂濇暟閲�
SELECT
- gp.factory as 鐢熶骇鍩哄湴,
- gp.material_code as 鏂欏彿,
- EXTRACT(YEAR FROM gp.predict_date) as 骞翠唤,
- EXTRACT(MONTH FROM gp.predict_date) as 鏈堜唤,
- gp.predict_quantity * COALESCE(ws.hup_qty, 0) as 棰勬祴鐒婄紳鏁�,
- mgm.domain as 涓撲笟
+ gp.factory as production_base,
+ gp.material_code as material_no,
+ EXTRACT(YEAR FROM gp.predict_date) as year,
+ EXTRACT(MONTH FROM gp.predict_date) as month,
+ gp.predict_quantity * COALESCE(ws.hup_qty, 0) as predict_weld_count,
+ mgm.domain as domain
FROM aps_gas_pipeline_prediction gp
LEFT JOIN aps_weld_seam_standard ws ON gp.material_code = ws.item_code AND ws.del_flag = 0
LEFT JOIN aps_material_product_group_management mgm ON gp.material_code = mgm.material_code AND gp.factory = mgm.factory
),
- 姹囨�绘暟鎹� AS (
+ summary_data AS (
SELECT
- 骞翠唤,
- 鏈堜唤,
- 鐢熶骇鍩哄湴,
+ year,
+ month,
+ production_base,
-- 绠¤矾璁㈠崟闇�姹�
COALESCE(SUM(CASE
- WHEN 涓撲笟 = 'GL' AND 绫诲瀷 = '宸ュ崟'
- THEN 鐒婄紳鏁� ELSE 0
- END), 0) as 绠¤矾璁㈠崟闇�姹�,
+ WHEN domain = 'GL' AND type = 'order'
+ THEN weld_count ELSE 0
+ END), 0) as piping_order_requirement,
-- 姘旀煖璁㈠崟闇�姹�
COALESCE(SUM(CASE
- WHEN 涓撲笟 = 'QG' AND 绫诲瀷 = '宸ュ崟'
- THEN 鐒婄紳鏁� ELSE 0
- END), 0) as 姘旀煖璁㈠崟闇�姹�,
+ WHEN domain = 'QG' AND type = 'order'
+ THEN weld_count ELSE 0
+ END), 0) as gas_order_requirement,
-- 绠¤矾棰勬祴闇�姹�
COALESCE(SUM(CASE
- WHEN 涓撲笟 = 'GL' AND 绫诲瀷 = '棰勬祴'
- THEN 鐒婄紳鏁� ELSE 0
- END), 0) as 绠¤矾棰勬祴闇�姹�,
+ WHEN domain = 'GL' AND type = 'predict'
+ THEN weld_count ELSE 0
+ END), 0) as piping_predict_requirement,
-- 姘旀煖棰勬祴闇�姹�
COALESCE(SUM(CASE
- WHEN 涓撲笟 = 'QG' AND 绫诲瀷 = '棰勬祴'
- THEN 鐒婄紳鏁� ELSE 0
- END), 0) as 姘旀煖棰勬祴闇�姹�
+ WHEN domain = 'QG' AND type = 'predict'
+ THEN weld_count ELSE 0
+ END), 0) as gas_predict_requirement
FROM (
SELECT
- 骞翠唤,
- 鏈堜唤,
- 鐢熶骇鍩哄湴,
- 宸ュ崟鐒婄紳鏁� as 鐒婄紳鏁�,
- '宸ュ崟' as 绫诲瀷,
- 涓撲笟
- FROM 宸ュ崟鐒婄紳璁＄畻
+ year,
+ month,
+ production_base,
+ order_weld_count as weld_count,
+ 'order' as type,
+ domain
+ FROM order_weld_calc
UNION ALL
SELECT
- 骞翠唤,
- 鏈堜唤,
- 鐢熶骇鍩哄湴,
- 棰勬祴鐒婄紳鏁� as 鐒婄紳鏁�,
- '棰勬祴' as 绫诲瀷,
- 涓撲笟
- FROM 棰勬祴鐒婄紳璁＄畻
- ) 鍚堝苟鏁版嵁
- GROUP BY 骞翠唤, 鏈堜唤, 鐢熶骇鍩哄湴
+ year,
+ month,
+ production_base,
+ predict_weld_count as weld_count,
+ 'predict' as type,
+ domain
+ FROM predict_weld_calc
+ ) merged_data
+ GROUP BY year, month, production_base
)
SELECT
- s.骞翠唤 as "year",
- s.鏈堜唤 as "month",
- s.鐢熶骇鍩哄湴 as "production_base",
- ROUND(s.绠¤矾璁㈠崟闇�姹�, 4) as "piping_order_requirement",
- ROUND(s.姘旀煖璁㈠崟闇�姹�, 4) as "gas_order_requirement",
- ROUND(s.绠¤矾棰勬祴闇�姹�, 4) as "piping_prediction_requirement",
- ROUND(s.姘旀煖棰勬祴闇�姹�, 4) as "gas_prediction_requirement",
+ s.year as "year",
+ s.month as "month",
+ s.production_base as "production_base",
+ ROUND(s.piping_order_requirement, 4) as "piping_order_requirement",
+ ROUND(s.gas_order_requirement, 4) as "gas_order_requirement",
+ ROUND(s.piping_predict_requirement, 4) as "piping_prediction_requirement",
+ ROUND(s.gas_predict_requirement, 4) as "gas_prediction_requirement",
0 as "reserve_emergency_order_output",
- ROUND(s.绠¤矾璁㈠崟闇�姹� + s.姘旀煖璁㈠崟闇�姹� + s.绠¤矾棰勬祴闇�姹� + s.姘旀煖棰勬祴闇�姹�, 4) as "total",
+ ROUND(s.piping_order_requirement + s.gas_order_requirement + s.piping_predict_requirement + s.gas_predict_requirement, 4) as "total",
0 as "days",
0 as "requirement_day_weld_seam",
0 as "production_day_weld_seam",
'0' as "is_satisfy",
'system' as "create_by",
CURRENT_TIMESTAMP as "create_time"
- FROM 姹囨�绘暟鎹� s
- ORDER BY s.骞翠唤, s.鏈堜唤, s.鐢熶骇鍩哄湴
+ FROM summary_data s
+ ORDER BY s.year, s.month, s.production_base
</select>
</mapper>
\ No newline at end of file
--
Gitblit v1.9.3