[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode
BEGIN
BEGIN TRY
DROP TABLE #TempGWSN;
END TRY
BEGIN CATCH
-- 错误处理逻辑，这里可以不做任何事情，因为表可能不存在
-- 如果需要，可以通过 ERROR_NUMBER() 和 ERROR_MESSAGE() 获取错误信息
END CATCH
CREATE TABLE #TempGWSN (
id INT IDENTITY(1,1) PRIMARY KEY,
gw_sn VARCHAR(255)
);
INSERT INTO #TempGWSN (gw_sn)
VALUES
('4500872442'),
('4500873315'),
('4500873314'),
('4500873313'),
('4500872020'),
('4500873312'),
('4500873311'),
('4500865013'),
('4500865012'),
('5000002876'),
('4500858867'),
('4500858055'),
('4500856325'),
('4500877783'),
('4500878470'),
('4500878463'),
('5000002996'),
('4500879452');
SELECT b.gw_sn,a.* FROM OPENQUERY(FD_MES_DB,
'
SELECT gw_order_sn,gw_order_sn as "采购订单号",contract_sn as "内部合同号",project_name as "工程名称",
CASE WHEN bidder="1000014692" THEN "顺泰" when bidder="1000091718" THEN "瑜煌" END AS "中标主体",
item_id as "订单行项目ID",task_sn as "生产订单",release_sn as "生产工单号",release_date as "下达时间",
delivery_date as "合同交货日期",change_delivery_date as "到货期变更日期",
case when eip_tower_status=1 THEN "已上传" else "未上传" END as "参数-基塔信息",
case when bom_eip_status=0 THEN "已上传" else "未传" END as "参数-技术加工",
case when eip_contract_material_status>0 THEN "已上传" else "未传" END as "原材料",
case when eip_machine_base_release_status=11 THEN "部分关联已上传" when eip_machine_base_release_status=13 THEN "部分关联未上传" when eip_machine_base_release_status=0 then "待维护" END as "生产工单原材料",
case when production_inspection_status=5 THEN "已维护" else "等待维护" END as "生产过程检验",
case when prod_process_status=6 THEN "完整且上传" else "等待维护" END as "生产过程信息采集",
case when temperature_status=1 THEN "已上传" else "等待维护" END as "镀锌温度",
case when product_status=1 THEN "已上传" else "等待维护" END as "成品"
FROM eip_internet_things_warning
') a RIGHT JOIN #TempGWSN b on a.gw_order_sn=b.gw_sn
ORDER BY gw_sn,a.生产工单号
DROP TABLE #TempGWSN;
END
-- 计划开始时间 若没有，则取 lofting_job.plan_start_date,
-- 计划完成时间 若没有，则取 contract.delivery_date,
-- 实际开始时间 若没有，则取 release.nested_material_date,
SELECT
a.*,
IF(b.min_eip = 1 AND b.max_eip = 1, 1, 0) AS GTXQ_pushed, -- 参数规范-杆塔详情
IF(c.min_push = 1 AND c.max_push = 1, 1, 0) AS YCL_pushed, -- 生产工单原材料上传
d.YCL_WZGL, -- 生产工单原材料关联性
e.SCSYGC_isComplete,
e.SCSYGC_isUpload,
IFNULL(f.dxwd_isUpload,'未生成') as dxwd_isUpload,
IFNULL(g.release_sub_id,'未生成') as BZ_exist,
g.CP_isUpload
FROM (
SELECT
a.gw_order_sn,
a.contract_sn,
a.item_id,
a.original_bidder,
a.product_name,
a.project_name,
a.short_name,
a.delivery_date,
b.id AS release_sub_id,
b.job_id,
c.job_sn,
b.release_sn,
e.release_date,
b.plan_start_date,
b.plan_finish_date,
b.actual_start_date,
b.actual_finish_date,
c.plan_start_date AS loftingPlanStartDate,
e.nested_material_date,
b.bom_eip_status, -- 参数规范-技术加工(bom)
d.tower_sns,
d.eip_pushed -- 参数规范-工程原材料
FROM
contract a
INNER JOIN release_sub b
ON a.id = b.contract_id
INNER JOIN lofting_job c
ON b.job_id = c.ID
INNER JOIN release_release_sub_temp s
ON s.release_sub_id = b.id
INNER JOIN `release` e
ON s.release_id = e.id
LEFT JOIN eip_contract_material d
ON b.id = d.release_id
WHERE
b.type = 1
AND a.is_gw_order = 1
AND a.delete_state = 0
AND a.gw_order_sn IN ('5000849129')
) AS a
LEFT JOIN (
SELECT
release_id,
MIN(eip_pushed) AS min_eip,
MAX(eip_pushed) AS max_eip
FROM eip_tower
GROUP BY release_id
) AS b ON a.release_sub_id = b.release_id
LEFT JOIN (
SELECT
release_id,
MIN(push_flag) AS min_push,
MAX(push_flag) AS max_push
FROM eip_machine_base_release
GROUP BY release_id
) AS c ON a.release_sub_id = c.release_id
left JOIN (
SELECT
job_id,
CASE
WHEN count(*) = 0 THEN 0 -- 还没生成
WHEN COUNT(eq_test_no)<COUNT(*) THEN 1 -- 没有完全匹配
WHEN COUNT(eq_test_no) = COUNT(*) THEN 2 -- 完全匹配
ELSE -1
END AS YCL_WZGL
FROM
eip_machine_base_release_material
GROUP BY
job_id
) as d on a.job_id=d.job_id
left join (
SELECT
release_id,
CASE
WHEN COUNT(DISTINCT data_type) = 2 THEN '完整'
ELSE '不完整'
END AS SCSYGC_isComplete,
CASE
WHEN SUM(CASE WHEN eip_status != 1 THEN 1 ELSE 0 END) > 0 THEN '未上传'
ELSE '已上传'
END AS SCSYGC_isUpload
FROM
eip_prod_process
GROUP BY
release_id
) as e on a.release_sub_id=e.release_id
left join (
SELECT
release_id,
CASE
WHEN SUM(CASE WHEN push_status !=1 or push_status is null THEN 1 ELSE 0 END) > 0 THEN '未上传'
ELSE '已上传'
END AS dxwd_isUpload
FROM
eip_temperature
GROUP BY release_id
) as f on a.release_sub_id=f.release_id
left join (
SELECT a.release_sub_id,
case
when SUM(case WHEN eip_status !=1 or eip_status is null THEN 1 else 0 END) >0 then '未上传'
ELSE '已上传'
END AS CP_isUpload
FROM package_import a left join eip_product b on a.tower_id=b.tower_id
GROUP BY a.release_sub_id
) as g on a.release_sub_id=g.release_sub_id
发表于 2024-11-21 10:06:50
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
楼主