product-inventory-management-after.git

			@@ -78,12 +78,25 @@
			</foreach>
			</delete>

			<sql id="productTypeTree">
			WITH RECURSIVE product_type AS (SELECT id,
			CASE
			WHEN product_name LIKE '%半成品%' THEN 1
			WHEN product_name LIKE '%成品%' THEN 2
			WHEN product_name LIKE '%原材料%' THEN 0
			END AS modelType
			FROM product
			WHERE parent_id IS NULL
			UNION ALL
			SELECT p.id,
			pt.modelType
			FROM product p
			INNER JOIN product_type pt ON p.parent_id = pt.id)
			</sql>

			<select id="getInspectStatistics" resultType="com.ruoyi.quality.dto.QualityInspectStatDto">
			SELECT CASE pp.product_name
			WHEN '原材料' THEN 0
			WHEN '半成品' THEN 1
			WHEN '成品' THEN 2
			END AS modelType,
			<include refid="productTypeTree"/>
			SELECT pt.modelType,

			IFNULL(SUM(qi.quantity), 0) AS totalCount,

			@@ -92,21 +105,20 @@
			ELSE 0
			END), 0) AS completedCount

			FROM product p
			INNER JOIN product pp
			ON p.parent_id = pp.id
			LEFT JOIN product_model pm
			ON pm.product_id = p.id
			FROM product_type pt
			INNER JOIN product_model pm
			ON pm.product_id = pt.id
			LEFT JOIN quality_inspect qi
			ON qi.product_model_id = pm.id

			WHERE pp.product_name IN ('原材料', '半成品', '成品')
			WHERE pt.modelType IS NOT NULL

			GROUP BY pp.product_name
			GROUP BY pt.modelType

			</select>

			<select id="getPassRateStatistics" resultType="com.ruoyi.quality.dto.QualityPassRateDto">
			<include refid="productTypeTree"/>
			SELECT t.modelType,

			COALESCE(SUM(qi.quantity), 0) AS totalCount,
			@@ -178,19 +190,12 @@
			SELECT 1
			UNION ALL
			SELECT 2) t
			LEFT JOIN product p
			ON 1 = 1
			LEFT JOIN product pp
			ON p.parent_id = pp.id
			LEFT JOIN product_type pt
			ON pt.modelType = t.modelType
			LEFT JOIN product_model pm
			ON pm.product_id = p.id
			ON pm.product_id = pt.id
			LEFT JOIN quality_inspect qi
			ON qi.product_model_id = pm.id
			AND (
			(pp.product_name = '原材料' AND t.modelType = 0) OR
			(pp.product_name = '半成品' AND t.modelType = 1) OR
			(pp.product_name = '成品' AND t.modelType = 2)
			)

			GROUP BY t.modelType
			ORDER BY t.modelType;
			@@ -211,7 +216,20 @@
			SELECT 2),
			base AS (SELECT m.month_num, t.modelType
			FROM months m
			CROSS JOIN types t)
			CROSS JOIN types t),
			product_type AS (SELECT id,
			CASE
			WHEN product_name LIKE '%半成品%' THEN 1
			WHEN product_name LIKE '%成品%' THEN 2
			WHEN product_name LIKE '%原材料%' THEN 0
			END AS modelType
			FROM product
			WHERE parent_id IS NULL
			UNION ALL
			SELECT p.id,
			pt.modelType
			FROM product p
			INNER JOIN product_type pt ON p.parent_id = pt.id)

			SELECT CASE b.month_num
			WHEN 1 THEN '一月'
			@@ -285,18 +303,13 @@
			) AS passRate

			FROM base b
			LEFT JOIN product p ON 1 = 1
			LEFT JOIN product pp ON p.parent_id = pp.id
			LEFT JOIN product_model pm ON pm.product_id = p.id
			LEFT JOIN product_type pt
			ON pt.modelType = b.modelType
			LEFT JOIN product_model pm ON pm.product_id = pt.id
			LEFT JOIN quality_inspect qi
			ON qi.product_model_id = pm.id
			AND YEAR(qi.check_time) = #{year}
			AND MONTH(qi.check_time) = b.month_num
			AND (
			(pp.product_name = '原材料' AND b.modelType = 0)
			OR (pp.product_name = '半成品' AND b.modelType = 1)
			OR (pp.product_name = '成品' AND b.modelType = 2)
			)

			GROUP BY b.month_num, b.modelType
			ORDER BY b.month_num, b.modelType;
			@@ -304,25 +317,19 @@
			</select>

			<select id="getYearlyPassRateStatistics" resultType="com.ruoyi.quality.dto.QualityPassRateDto">
			<include refid="productTypeTree"/>
			SELECT t.modelType,

			COALESCE(SUM(
			CASE
			WHEN pp.product_name = '原材料' AND t.modelType = 0 THEN qi.quantity
			WHEN pp.product_name = '半成品' AND t.modelType = 1 THEN qi.quantity
			WHEN pp.product_name = '成品' AND t.modelType = 2 THEN qi.quantity
			ELSE 0
			END
			), 0) AS totalCount
			COALESCE(SUM(qi.quantity), 0) AS totalCount

			FROM (SELECT 0 AS modelType
			UNION ALL
			SELECT 1
			UNION ALL
			SELECT 2) t
			LEFT JOIN product p ON 1 = 1
			LEFT JOIN product pp ON p.parent_id = pp.id
			LEFT JOIN product_model pm ON pm.product_id = p.id
			LEFT JOIN product_type pt
			ON pt.modelType = t.modelType
			LEFT JOIN product_model pm ON pm.product_id = pt.id
			LEFT JOIN quality_inspect qi
			ON qi.product_model_id = pm.id
			AND YEAR(qi.check_time) = #{year}
			@@ -340,7 +347,20 @@
			UNION ALL
			SELECT month_num + 1
			FROM months
			WHERE month_num < 12)
			WHERE month_num < 12),
			product_type AS (SELECT id,
			CASE
			WHEN product_name LIKE '%半成品%' THEN 1
			WHEN product_name LIKE '%成品%' THEN 2
			WHEN product_name LIKE '%原材料%' THEN 0
			END AS modelType
			FROM product
			WHERE parent_id IS NULL
			UNION ALL
			SELECT p.id,
			pt.modelType
			FROM product p
			INNER JOIN product_type pt ON p.parent_id = pt.id)
			SELECT CASE m.month_num
			WHEN 1 THEN '一月'
			WHEN 2 THEN '二月'
			@@ -359,7 +379,7 @@
			/* 原材料 */
			COALESCE(SUM(
			CASE
			WHEN pp.product_name = '原材料'
			WHEN pt.modelType = 0
			THEN qi.quantity
			ELSE 0
			END
			@@ -368,7 +388,7 @@
			/* 半成品 */
			COALESCE(SUM(
			CASE
			WHEN pp.product_name = '半成品'
			WHEN pt.modelType = 1
			THEN qi.quantity
			ELSE 0
			END
			@@ -377,16 +397,15 @@
			/* 成品 */
			COALESCE(SUM(
			CASE
			WHEN pp.product_name = '成品'
			WHEN pt.modelType = 2
			THEN qi.quantity
			ELSE 0
			END
			), 0) AS outgoingCount

			FROM months m
			LEFT JOIN product p ON 1 = 1
			LEFT JOIN product pp ON p.parent_id = pp.id
			LEFT JOIN product_model pm ON pm.product_id = p.id
			LEFT JOIN product_type pt ON 1 = 1
			LEFT JOIN product_model pm ON pm.product_id = pt.id
			LEFT JOIN quality_inspect qi
			ON qi.product_model_id = pm.id
			AND qi.inspect_state = 1
			@@ -399,20 +418,31 @@
			</select>

			<select id="getTopParameters" resultType="com.ruoyi.quality.dto.QualityParameterStatDto">
			WITH parameter_counts AS (SELECT qip.parameter_item AS name,
			WITH RECURSIVE product_type AS (SELECT id,
			CASE
			WHEN product_name LIKE '%半成品%' THEN 1
			WHEN product_name LIKE '%成品%' THEN 2
			WHEN product_name LIKE '%原材料%' THEN 0
			END AS modelType
			FROM product
			WHERE parent_id IS NULL
			UNION ALL
			SELECT p.id,
			pt.modelType
			FROM product p
			INNER JOIN product_type pt ON p.parent_id = pt.id),
			parameter_counts AS (SELECT qip.parameter_item AS name,
			COUNT(*) AS count
			FROM quality_inspect_param qip
			JOIN quality_inspect qi
			ON qip.inspect_id = qi.id
			JOIN product p
			ON qi.product_id = p.id
			JOIN product pp
			ON p.parent_id = pp.id
			JOIN product_type pt
			ON qi.product_id = pt.id
			WHERE qi.inspect_state = 1
			AND (
			(#{modelType} = 1 AND pp.product_name = '原材料')
			OR (#{modelType} = 2 AND pp.product_name = '半成品')
			OR (#{modelType} = 3 AND pp.product_name = '成品')
			(#{modelType} = 1 AND pt.modelType = 0)
			OR (#{modelType} = 2 AND pt.modelType = 1)
			OR (#{modelType} = 3 AND pt.modelType = 2)
			)
			GROUP BY qip.parameter_item),
			ranked AS (SELECT name,