apsbackend.git - Gitblit

			@@ -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>