是这样吗-- =================================================================================================
-- SQL SCRIPT FOR DAILY OUTPUT PLAN PROCESSING
-- Description: This script refactors the business logic from '日产出目标.py' into a single,
-- comprehensive SQL query. It processes work orders, corrects schedule times based on
-- resource constraints, and calculates the daily output plan for the next 7 days.
-- Target Table: daily_output_plan_final
-- =================================================================================================
-- Step 0.1: Create the final output table if it doesn't exist
CREATE TABLE IF NOT EXISTS daily_output_plan_final (
"批次" TEXT NOT NULL,
"指令" TEXT,
"线体" TEXT,
"单板代码" TEXT,
"单板名称" TEXT,
"计划数量" REAL,
"产出数量" REAL,
"剩余数量" REAL,
"计划开始时间" TEXT,
"计划结束时间" TEXT,
"实际开始时间" TEXT,
"修正后计划开始时间" TEXT,
"修正后计划结束时间" TEXT,
"主工序" TEXT,
"工序序号" INTEGER,
"入库工序" TEXT,
"入库时间" TEXT,
"产品大类" TEXT,
"工厂" TEXT,
"责任人" TEXT,
"day_1_output" REAL,
"day_2_output" REAL,
"day_3_output" REAL,
"day_4_output" REAL,
"day_5_output" REAL,
"day_6_output" REAL,
"day_7_output" REAL
);
-- Step 0.2: Clean up the final table before insertion
DELETE FROM daily_output_plan_final;
-- Step 1: Use a CTE to prepare and pre-calculate data, similar to the initial Python DataFrame setup.
WITH PreparedData AS (
SELECT
wo.prodplanId AS "批次",
wo.workOrderNo AS "指令",
wo.lineName AS "线体",
wo.itemNo AS "单板代码",
wo.itemName AS "单板名称",
CAST(wo.taskQty AS REAL) AS "计划数量",
wo.scheduleStartDate AS "计划开始时间",
wo.scheduleEndDate AS "计划结束时间",
wo.actualStartDate AS "实际开始时间",
wo.craftSection AS "主工序",
wo.order_index AS "顺序号",
CAST(wo.remark AS INTEGER) AS "工序序号",
CAST(wo.outputQty AS REAL) AS "产出数量",
wo.externalType AS "产品大类",
wo.factoryName AS "工厂",
-- Calculate remaining quantity, ensuring it's not negative
MAX(0, CAST(wo.taskQty AS REAL) - CAST(wo.outputQty AS REAL)) AS "剩余数量",
-- Merge craft data with defaults for calculation
COALESCE(cd.unitPerHour, 60) AS "UPH",
COALESCE(cd.operationEfficiency, 1) AS "作业效率",
COALESCE(cd.transferTime, 0.5) AS "转机时间",
COALESCE(cd.endingTime, 0.5) AS "收尾时间",
-- Calculate production time in hours
(MAX(0, CAST(wo.taskQty AS REAL) - CAST(wo.outputQty AS REAL)) / NULLIF(COALESCE(cd.unitPerHour, 60) * COALESCE(cd.operationEfficiency, 1), 0)) AS production_time,
-- Determine if it's the last operation in the batch to mark for warehousing
CASE
WHEN wo.remark = MAX(wo.remark) OVER (PARTITION BY wo.prodplanId) THEN '是'
ELSE '否'
END AS "入库工序"
FROM
work_orders wo
LEFT JOIN
craft_data cd ON wo.itemNo = cd.itemNo AND wo.craftSection = cd.mainProcess
WHERE
-- Filter out completed or near-completed orders
(CAST(wo.taskQty AS REAL) > 0 AND (CAST(wo.outputQty AS REAL) / CAST(wo.taskQty AS REAL)) <= 0.9)
),
-- Step 2: Rank operations to establish a processing order for the recursive CTE
RankedData AS (
SELECT
*,
-- Rank within the same batch
ROW_NUMBER() OVER (PARTITION BY "批次" ORDER BY "工序序号") AS op_rank_in_batch,
-- Global rank across all data for sequential processing
ROW_NUMBER() OVER (ORDER BY "线体", "批次", "工序序号") AS global_rank,
-- Pre-calculate total hours for this operation
("转机时间" + production_time + "收尾时间") AS total_hours
FROM
PreparedData
),
-- Step 3: Recursive CTE to correct schedule times, simulating the Python loop
CorrectedSchedule AS (
-- Anchor Member: Start with the first operation (global_rank = 1)
SELECT
rd.global_rank,
rd."批次",
rd."线体",
rd."单板代码",
rd."主工序",
rd.op_rank_in_batch,
rd.total_hours,
-- Determine the initial start time
COALESCE(rd."实际开始时间", rd."计划开始时间", STRFTIME('%Y-%m-%d %H:%M:%S', 'now', 'localtime')) AS corrected_start_time,
-- Calculate the initial end time
STRFTIME('%Y-%m-%d %H:%M:%S', COALESCE(rd."实际开始时间", rd."计划开始时间", STRFTIME('%Y-%m-%d %H:%M:%S', 'now', 'localtime')), '+' || rd.total_hours || ' hours') AS corrected_end_time,
-- Initialize resource last-end-time trackers
CASE WHEN rd."主工序" <> 'Test' THEN STRFTIME('%Y-%m-%d %H:%M:%S', COALESCE(rd."实际开始时间", rd."计划开始时间", STRFTIME('%Y-%m-%d %H:%M:%S', 'now', 'localtime')), '+' || rd.total_hours || ' hours') ELSE NULL END AS line_last_end,
CASE WHEN rd."主工序" = 'Test' THEN STRFTIME('%Y-%m-%d %H:%M:%S', COALESCE(rd."实际开始时间", rd."计划开始时间", STRFTIME('%Y-%m-%d %H:%M:%S', 'now', 'localtime')), '+' || rd.total_hours || ' hours') ELSE NULL END AS item_test_last_end
FROM
RankedData rd
WHERE
rd.global_rank = 1
UNION ALL
-- Recursive Member: Process subsequent operations one by one
SELECT
rd.global_rank,
rd."批次",
rd."线体",
rd."单板代码",
rd."主工序",
rd.op_rank_in_batch,
rd.total_hours,
-- Determine the corrected start time based on dependencies
(
SELECT MAX(ts)
FROM (
-- Dependency 1: Previous operation in the same batch
SELECT cs.corrected_end_time FROM CorrectedSchedule cs WHERE cs."批次" = rd."批次" AND cs.op_rank_in_batch = rd.op_rank_in_batch - 1
UNION ALL
-- Dependency 2: Resource availability (line or test)
SELECT CASE
WHEN rd."主工序" = 'Test' THEN (SELECT MAX(cs2.item_test_last_end) FROM CorrectedSchedule cs2 WHERE cs2."单板代码" = rd."单板代码")
ELSE (SELECT MAX(cs2.line_last_end) FROM CorrectedSchedule cs2 WHERE cs2."线体" = rd."线体")
END
UNION ALL
-- Dependency 3: Its own original start time (or now if null)
SELECT COALESCE(rd."实际开始时间", rd."计划开始时间", STRFTIME('%Y-%m-%d %H:%M:%S', 'now', 'localtime'))
) AS times(ts)
) AS corrected_start_time,
-- Calculate the new end time based on the corrected start time
STRFTIME('%Y-%m-%d %H:%M:%S',
(SELECT MAX(ts) FROM (
SELECT cs.corrected_end_time FROM CorrectedSchedule cs WHERE cs."批次" = rd."批次" AND cs.op_rank_in_batch = rd.op_rank_in_batch - 1
UNION ALL
SELECT CASE WHEN rd."主工序" = 'Test' THEN (SELECT MAX(cs2.item_test_last_end) FROM CorrectedSchedule cs2 WHERE cs2."单板代码" = rd."单板代码") ELSE (SELECT MAX(cs2.line_last_end) FROM CorrectedSchedule cs2 WHERE cs2."线体" = rd."线体") END
UNION ALL
SELECT COALESCE(rd."实际开始时间", rd."计划开始时间", STRFTIME('%Y-%m-%d %H:%M:%S', 'now', 'localtime'))
) AS times(ts)),
'+' || rd.total_hours || ' hours'
) AS corrected_end_time,
-- Update resource trackers for the next iteration
CASE WHEN rd."主工序" <> 'Test' THEN STRFTIME('%Y-%m-%d %H:%M:%S', (SELECT MAX(ts) FROM (SELECT cs.corrected_end_time FROM CorrectedSchedule cs WHERE cs."批次" = rd."批次" AND cs.op_rank_in_batch = rd.op_rank_in_batch - 1 UNION ALL SELECT CASE WHEN rd."主工序" = 'Test' THEN (SELECT MAX(cs2.item_test_last_end) FROM CorrectedSchedule cs2 WHERE cs2."单板代码" = rd."单板代码") ELSE (SELECT MAX(cs2.line_last_end) FROM CorrectedSchedule cs2 WHERE cs2."线体" = rd."线体") END UNION ALL SELECT COALESCE(rd."实际开始时间", rd."计划开始时间", STRFTIME('%Y-%m-%d %H:%M:%S', 'now', 'localtime'))) AS times(ts)), '+' || rd.total_hours || ' hours') ELSE (SELECT MAX(cs2.line_last_end) FROM CorrectedSchedule cs2 WHERE cs2."线体" = rd."线体") END,
CASE WHEN rd."主工序" = 'Test' THEN STRFTIME('%Y-%m-%d %H:%M:%S', (SELECT MAX(ts) FROM (SELECT cs.corrected_end_time FROM CorrectedSchedule cs WHERE cs."批次" = rd."批次" AND cs.op_rank_in_batch = rd.op_rank_in_batch - 1 UNION ALL SELECT CASE WHEN rd."主工序" = 'Test' THEN (SELECT MAX(cs2.item_test_last_end) FROM CorrectedSchedule cs2 WHERE cs2."单板代码" = rd."单板代码") ELSE (SELECT MAX(cs2.line_last_end) FROM CorrectedSchedule cs2 WHERE cs2."线体" = rd."线体") END UNION ALL SELECT COALESCE(rd."实际开始时间", rd."计划开始时间", STRFTIME('%Y-%m-%d %H:%M:%S', 'now', 'localtime'))) AS times(ts)), '+' || rd.total_hours || ' hours') ELSE (SELECT MAX(cs2.item_test_last_end) FROM CorrectedSchedule cs2 WHERE cs2."单板代码" = rd."单板代码") END
FROM
RankedData rd
JOIN
CorrectedSchedule cs ON rd.global_rank = cs.global_rank + 1
),
-- Step 4: Combine final schedule with other data and calculate daily outputs
-- 修改FinalData：增加实际生产开始时间和计算生产持续时间
FinalData AS (
SELECT
rd.*,
cs.corrected_start_time,
cs.corrected_end_time,
-- 计算实际生产开始时间（取当前时间或修正后开始时间中较晚者）
MAX(STRFTIME('%Y-%m-%d %H:%M:%S', 'now', 'localtime'), cs.corrected_start_time) AS actual_production_start,
-- 计算生产持续时间（分钟）
(JULIANDAY(cs.corrected_end_time) - JULIANDAY(cs.corrected_start_time)) * 24 * 60 AS total_duration_minutes,
CASE WHEN rd."入库工序" = '是' THEN DATE(cs.corrected_end_time) ELSE NULL END AS "入库时间"
FROM
RankedData rd
JOIN
CorrectedSchedule cs ON rd.global_rank = cs.global_rank
),
-- 重新设计每日产出计算
DailyOutputCalculation AS (
SELECT
f.*,
(SELECT "计调员" FROM part_category pc WHERE pc."单板代码" LIKE f."单板代码" || '%' AND pc."工厂" = f."工厂" LIMIT 1) AS "责任人",
-- 计算每天的生产时间比例和产出
-- Day 1: 今天剩余时间
CASE WHEN f."入库工序" = '是'
THEN ROUND(f."剩余数量" *
(MAX(0, MIN(JULIANDAY(DATE('now', '+1 day')), JULIANDAY(f.corrected_end_time)) -
MAX(JULIANDAY(f.actual_production_start), JULIANDAY('now')))
/ (JULIANDAY(f.corrected_end_time) - JULIANDAY(f.actual_production_start)))
ELSE 0
END AS day_1_output,
-- Day 2: 完整的一天
CASE WHEN f."入库工序" = '是'
THEN ROUND(f."剩余数量" *
(MAX(0, MIN(JULIANDAY(DATE('now', '+2 day')), JULIANDAY(f.corrected_end_time)) -
MAX(JULIANDAY(f.actual_production_start), JULIANDAY(DATE('now', '+1 day'))))
/ (JULIANDAY(f.corrected_end_time) - JULIANDAY(f.actual_production_start)))
ELSE 0
END AS day_2_output,
-- 重复相同逻辑计算day3-day7...
...
FROM
FinalData f
)
-- Final Step: Insert the processed data into the permanent table
INSERT INTO daily_output_plan_final (
"批次", "指令", "线体", "单板代码", "单板名称", "计划数量", "产出数量", "剩余数量",
"计划开始时间", "计划结束时间", "实际开始时间", "修正后计划开始时间", "修正后计划结束时间",
"主工序", "工序序号", "入库工序", "入库时间", "产品大类", "工厂", "责任人",
"day_1_output", "day_2_output", "day_3_output", "day_4_output", "day_5_output", "day_6_output", "day_7_output"
)
SELECT
"批次",
"指令",
"线体",
"单板代码",
"单板名称",
"计划数量",
"产出数量",
"剩余数量",
"计划开始时间",
"计划结束时间",
"实际开始时间",
corrected_start_time AS "修正后计划开始时间",
corrected_end_time AS "修正后计划结束时间",
"主工序",
"工序序号",
"入库工序",
"入库时间",
"产品大类",
"工厂",
"责任人",
day_1_output,
day_2_output,
day_3_output,
day_4_output,
day_5_output,
day_6_output,
day_7_output
FROM
DailyOutputCalculation;
扫一扫
2581
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
