重工电子论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
热搜: 活动 交友 discuz
查看: 2004|回复: 8
打印 上一主题 下一主题

[其他] GW上传相关信息

[复制链接]

304

主题

696

帖子

8774

积分

学生管理组

Rank: 8Rank: 8

积分
8774
跳转到指定楼层
楼主
发表于 2024-11-21 10:06:50 | 只看该作者 |只看大图 回帖奖励 |倒序浏览 |阅读模式
本帖最后由 李维强-15级 于 2025-4-1 15:05 编辑

在线问题提交:
https://docs.qq.com/sheet/DZE9xaktsU0xIVHVB?tab=BB08J2
--------------------------------------------------------------------------------------------------------
24-11-14
平台最近上线了质量评价申请功能,目前更新工单实际完成时间的方式已无法触发二次质量评价,如需更新工单评分可通过质量评价申请功能来实现。
1.申请路径:质量评价-->质量评价申请
2.使用角色:供应商及监理均可发起重新评价申请。
3.列表页空白:质量评价申请提交后,列表页如出现下图数据空白的情况,可点击右上角“列表设置”,将相关字段添加至右侧,保存即可。
4.审批跟进:质量评价申请提交后由采购订单项目单位即省公司质量监督管理员的角色来操作审核,质量评价申请提交未审核的话,供应商或监理老师可联系该订单对应项目单位,来跟进质量评价申请的审核进度。
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

微信图片_20250310111807.jpg (33.6 KB, 下载次数: 74)

微信图片_20250310111807.jpg

微信图片_20250310111807.jpg (33.6 KB, 下载次数: 72)

微信图片_20250310111807.jpg
分享到:  QQ好友和群QQ好友和群 QQ空间QQ空间 腾讯微博腾讯微博 腾讯朋友腾讯朋友
收藏收藏
回复

使用道具 举报

304

主题

696

帖子

8774

积分

学生管理组

Rank: 8Rank: 8

积分
8774
沙发
 楼主| 发表于 2024-12-16 17:26:48 | 只看该作者
本帖最后由 李维强-15级 于 2025-3-3 17:56 编辑

[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





[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
('4100161987'),
('4100161980');



SELECT 
b.gw_sn,
a.gw_order_sn AS "采购订单号",  
a.contract_sn AS "合同号",  
a.item_id AS "行项目ID",  
CASE WHEN a.original_bidder='1000014692'  THEN '顺泰' when a.original_bidder='1000091718'  THEN '瑜煌' END AS '中标主体',
a.product_name AS "塔类型",  
a.project_name AS "工程名称",  
a.short_name AS "工程简称",  
a.delivery_date AS "合同交货期",  
a.job_sn AS "生产单号",  
a.release_sn AS "生产工单",  
a.release_date AS "下达时间",  
a.plan_start_date AS "工单-计划开始时间",  
a.plan_finish_date AS "工单-计划结束时间",  
a.actual_start_date AS "工单-实际开始时间",  
a.actual_finish_date AS "工单-实际结束时间",  
a.loftingPlanStartDate AS "理论计划开始时间",  
a.delivery_date AS "理论计划完成时间",  
a.nested_material_date AS "理论实际开始时间",
a.tower_sns AS "杆塔号",
case when a.GTXQ_pushed  = 1 THEN '已上传' when  a.GTXQ_pushed = 0 THEN '未传' END AS "参数规范-杆塔详情",
case when a.bom_eip_status = 1 THEN '已上传' when  a.bom_eip_status = 0 THEN '未传' END AS "参数规范-技术加工",  
a.eip_pushed as "参数规范-工程原材料",
case when a.YCL_pushed = 1 THEN '已上传' when  a.bom_eip_status = 0 THEN '未传' END AS "原材料数据",  
case when a.YCL_WZGL = 0 THEN '未关联' when  a.YCL_WZGL = 1 THEN '未完全匹配' when a.YCL_WZGL = 2 THEN '完整关联' END AS "原材料数据关联",  
a.SCSYGC_isComplete as "生产实验过程-完整性",
a.SCSYGC_isUpload as "生产实验过程-上传",
a.dxwd_isUpload as "镀锌温度",
case when a.BZ_exist = '未生成' then '未生成'   when a.BZ_exist is NULL then null ELSE '已生成' END  as "成品数据-生成",
a.CP_isUpload as "成品数据-上传"


-- 计划开始时间 若没有,则取 lofting_job.plan_start_date,
-- 计划完成时间 若没有,则取 contract.delivery_date,
-- 实际开始时间 若没有,则取 release.nested_material_date,
FROM OPENQUERY(FD_MES_DB,'


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)
        SUBSTRING(tower_sns, 1, 4000) AS 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 (
				
				
"4100161987",
"4100161980"
				)
) 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

') a RIGHT JOIN #TempGWSN b on a.gw_order_sn=b.gw_sn
ORDER BY gw_sn
DROP TABLE #TempGWSN;
 
END







回复 支持 反对

使用道具 举报

304

主题

696

帖子

8774

积分

学生管理组

Rank: 8Rank: 8

积分
8774
板凳
 楼主| 发表于 2025-1-16 12:01:00 | 只看该作者
沈工,请教一个问题。
我们在上传“参数规范-技术加工”数据的时候,会包含“生产工单号”、“质量追溯码”这两个数据。
因为生产当中合并加工的原因,他们会在生产某几基塔的公共段时,建一个生产工单。在生产腿段时,又建一个生产工单。最终的结果就是在传"参数规范-技术加工”的数据之时存在“质量追溯码”相同,而“生产工单号”不同的情况。
对于这种情况,国网平台会根据“质量追溯码”的信息,关联到某一基塔的“参数规范-技术加工”数据么?
回复 支持 反对

使用道具 举报

304

主题

696

帖子

8774

积分

学生管理组

Rank: 8Rank: 8

积分
8774
地板
 楼主| 发表于 2025-3-4 11:09:36 | 只看该作者
本帖最后由 李维强-15级 于 2025-3-5 11:09 编辑

[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

('0101043170'),
('0101057386'),
('0101043505'),
('0101043506'),
('0101043509'),
('0101043510'),
('0101043511');
  
 
SELECT
b.gw_sn,
a.gw_order_sn AS "采购订单号",  
a.contract_sn AS "合同号",  
a.item_id AS "行项目ID",  
a.customer as "客户名称",
CASE WHEN a.original_bidder='1000014692'  THEN '顺泰' when a.original_bidder='1000091718'  THEN '瑜煌' END AS '中标主体',
a.product_name AS "塔类型",  
a.project_name AS "工程名称",  
a.short_name AS "工程简称",  
a.delivery_date AS "合同交货期",  
a.job_sn AS "生产单号",  
a.release_sn AS "生产工单",  
CONVERT(VARCHAR(100), a.release_date, 120)  AS "下达时间",  
case when a.GTXQ_pushed  = 1 THEN '已上传' when  a.GTXQ_pushed = 0 THEN '未传' END AS "参数规范-杆塔详情",
case when a.bom_eip_status = 1 THEN '已上传' when  a.bom_eip_status = 0 THEN '未传' END AS "参数规范-技术加工",  
case when a.eip_pushed =1 THEN '已上传'  when a.eip_pushed is NULL THEN NULL ELSE '未传' END  as "参数规范-工程原材料",
case when a.YCL_pushed = 1 THEN '已上传' when  a.bom_eip_status = 0 THEN '未传' END AS "原材料数据",  
case when a.YCL_WZGL = 0 THEN '未关联' when  a.YCL_WZGL = 1 THEN '未完全匹配' when a.YCL_WZGL = 2 THEN '完整关联' END AS "原材料数据关联",  
a.SCSYGC_isComplete as "生产实验过程-完整性",
a.SCSYGC_isUpload as "生产实验过程-上传",
a.dxwd_isUpload as "镀锌温度",
case when a.BZ_exist = '未生成' then '未生成'   when a.BZ_exist is NULL then null ELSE '已生成' END  as "成品数据-生成",
a.CP_isUpload as "成品数据-上传",
a.plan_start_date AS "工单-计划开始时间",  
a.plan_finish_date AS "工单-计划结束时间",  
a.actual_start_date AS "工单-实际开始时间",  
a.actual_finish_date AS "工单-实际结束时间",  
a.loftingPlanStartDate AS "理论计划开始时间",  
a.delivery_date AS "理论计划完成时间",  
a.nested_material_date AS "理论实际开始时间",
a.tower_sns AS "杆塔号"
 
 
 
-- 计划开始时间 若没有,则取 lofting_job.plan_start_date,
-- 计划完成时间 若没有,则取 contract.delivery_date,
-- 实际开始时间 若没有,则取 release.nested_material_date,

--   SELECT release_id,MIN(eip_pushed) AS min_eip,MAX(eip_pushed) AS max_eip FROM eip_tower GROUP BY release_id
FROM OPENQUERY(FD_MES_DB,'
 
 
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.customer,
        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,
				c.lofting_id,
        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)
        SUBSTRING(tower_sns, 1, 4000) AS 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.release_sn = d.release_sn
    WHERE
        b.type = 1 
        AND a.is_gw_order = 1 
        AND a.delete_state = 0 
        AND a.gw_order_sn IN (
                         
                         
"0101043170",
"0101057386",
"0101043505",
"0101043506",
"0101043509",
"0101043510",
"0101043511"
 
                        )
) AS a
LEFT JOIN (
 
	SELECT a.lofting_id,MIN(b.eip_pushed) AS min_eip, MAX(b.eip_pushed) AS max_eip FROM tower_lofting a INNER JOIN eip_tower b on a.tower_id=b.tower_id GROUP BY lofting_id 	
		
) AS b ON a.lofting_id = b.lofting_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
 
') a RIGHT JOIN #TempGWSN b on a.gw_order_sn=b.gw_sn
ORDER BY gw_sn
DROP TABLE #TempGWSN;
  
END




回复 支持 反对

使用道具 举报

304

主题

696

帖子

8774

积分

学生管理组

Rank: 8Rank: 8

积分
8774
5#
 楼主| 发表于 2025-3-6 21:00:30 | 只看该作者
本帖最后由 李维强-15级 于 2025-3-6 22:02 编辑

BEGIN TRY
    DROP TABLE #TempGWSN;
END TRY
BEGIN CATCH
    -- 忽略表不存在错误‌:ml-citation{ref="1" data="citationList"}
END CATCH

CREATE TABLE #TempGWSN (
    id INT IDENTITY(1,1) PRIMARY KEY,
    gw_sn VARCHAR(255)
);

INSERT INTO #TempGWSN (gw_sn)
VALUES
('0101043170'),
('0101057386'),
('0101043505'),
('0101043506'),
('0101043509'),
('0101043510'),
('0101043511');

-- 动态拼接订单号列表
DECLARE @OrderList NVARCHAR(MAX);
SELECT @OrderList = STUFF(
    (SELECT ',' + QUOTENAME(gw_sn, '""')
     FROM #TempGWSN
     ORDER BY id
     FOR XML PATH('')
    ), 1, 1, ''
);

-- 将动态生成的订单号嵌入OPENQUERY查询
SELECT
    b.gw_sn,
    a.gw_order_sn AS "采购订单号",
    ... (其他字段保持不变)
FROM OPENQUERY(FD_MES_DB, '
    SELECT
        a.*,
        IF(b.min_eip = 1 AND b.max_eip = 1, 1, 0) AS GTXQ_pushed,
        ... (其他子查询逻辑保持不变)
    WHERE
        b.type = 1
        AND a.is_gw_order = 1
        AND a.delete_state = 0
        AND a.gw_order_sn IN (' + @OrderList + ')  -- 替换为动态拼接的订单号列表
') AS a
INNER JOIN #TempGWSN b ON a.gw_order_sn = b.gw_sn;

回复 支持 反对

使用道具 举报

304

主题

696

帖子

8774

积分

学生管理组

Rank: 8Rank: 8

积分
8774
6#
 楼主| 发表于 2025-3-7 09:32:02 | 只看该作者
本帖最后由 李维强-15级 于 2025-3-7 15:14 编辑

只输入一次采购订单版本
[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
 


('4500844755'),
('5000002642'),
('4500838263'),
('4500846064'),
('4500846082'),
('4500848101'),
('4500853530'),
('4500856325'),
('4500858055'),
('4500858867'),
('4500865012'),
('4500865013'),
('4500872020'),
('4500872442'),
('4500873311'),
('4500873312'),
('4500873313'),
('4500873314'),
('4500873315'),
('4500878463'),
('4500878470'),
('4500879452'),
('5000002592'),
('5000002619'),
('5000002996');



  
-- 动态拼接订单号列表
DECLARE @OrderList NVARCHAR(MAX);
SELECT @OrderList = STUFF(
    (SELECT ',' + QUOTENAME(gw_sn, '""')
     FROM #TempGWSN
     ORDER BY id
     FOR XML PATH('')
    ), 1, 1, ''
);	 	
	
	
DECLARE @txt nvarchar(max);	
SELECT @txt=
'SELECT
b.gw_sn,
a.gw_order_sn AS "采购订单号",  
a.contract_sn AS "合同号",  
a.item_id AS "行项目ID",  
a.customer as "客户名称",
CASE WHEN a.original_bidder=''1000014692''  THEN ''顺泰'' when a.original_bidder=''1000091718''  THEN ''瑜煌'' END AS ''中标主体'',
a.product_name AS "塔类型",  
a.project_name AS "工程名称",  
a.short_name AS "工程简称",  
a.delivery_date AS "合同交货期",  
a.job_sn AS "生产单号",  
a.release_sn AS "生产工单",  
CONVERT(VARCHAR(100), a.release_date, 120)  AS "下达时间",  
case when a.GTXQ_pushed  = 1 THEN ''已上传'' when  a.GTXQ_pushed = 0 THEN ''未传'' END AS "参数规范-杆塔详情",
case when a.bom_eip_status = 1 THEN ''已上传'' when  a.bom_eip_status = 0 THEN ''未传'' END AS "参数规范-技术加工",  
case when a.eip_pushed =1 THEN ''已上传''  when a.eip_pushed is NULL THEN NULL ELSE ''未传'' END  as "参数规范-工程原材料",
case when a.YCL_pushed = 1 THEN ''已上传'' when  a.bom_eip_status = 0 THEN ''未传'' END AS "原材料数据",  
case when a.YCL_WZGL = 0 THEN ''未关联'' when  a.YCL_WZGL = 1 THEN ''未完全匹配'' when a.YCL_WZGL = 2 THEN ''完整关联'' END AS "原材料数据关联",  
a.SCSYGC_isComplete as "生产实验过程-完整性",
a.SCSYGC_isUpload as "生产实验过程-上传",
a.dxwd_isUpload as "镀锌温度",
case when a.BZ_exist = ''未生成'' then ''未生成''   when a.BZ_exist is NULL then null ELSE ''已生成'' END  as "成品数据-生成",
a.CP_isUpload as "成品数据-上传",
a.plan_start_date AS "工单-计划开始时间",  
a.plan_finish_date AS "工单-计划结束时间",  
a.actual_start_date AS "工单-实际开始时间",  
a.actual_finish_date AS "工单-实际结束时间",  
a.loftingPlanStartDate AS "理论计划开始时间",  
a.delivery_date AS "理论计划完成时间",  
a.nested_material_date AS "理论实际开始时间",
a.tower_sns AS "杆塔号"
  
  
  
-- 计划开始时间 若没有,则取 lofting_job.plan_start_date,
-- 计划完成时间 若没有,则取 contract.delivery_date,
-- 实际开始时间 若没有,则取 release.nested_material_date,
 
--   SELECT release_id,MIN(eip_pushed) AS min_eip,MAX(eip_pushed) AS max_eip FROM eip_tower GROUP BY release_id
FROM OPENQUERY(FD_MES_DB,''
  
  
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.customer,
        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,
                        c.lofting_id,
        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)
        SUBSTRING(tower_sns, 1, 4000) AS 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.release_sn = d.release_sn
    WHERE
        b.type = 1 
        AND a.is_gw_order = 1 
        AND a.delete_state = 0 
        AND a.gw_order_sn IN ('+@OrderList+ ')
) AS a
LEFT JOIN (
  
      SELECT release_sn,MIN(eip_pushed) AS min_eip, MAX(eip_pushed) AS max_eip FROM  eip_tower GROUP BY release_sn 
             
) AS b ON a.release_sn = b.release_sn
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
  
'') a RIGHT JOIN #TempGWSN b on a.gw_order_sn=b.gw_sn
ORDER BY gw_sn';
EXEC(@txt);
DROP TABLE #TempGWSN;
END




回复 支持 反对

使用道具 举报

9

主题

20

帖子

193

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
193
7#
发表于 2025-3-11 11:36:39 | 只看该作者
修复 杆塔详情版

[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

('4500856325');
 
 
 
   
-- 动态拼接订单号列表
DECLARE @OrderList NVARCHAR(MAX);
SELECT @OrderList = STUFF(
    (SELECT ',' + QUOTENAME(gw_sn, '""')
     FROM #TempGWSN
     ORDER BY id
     FOR XML PATH('')
    ), 1, 1, ''
);          
       
       
DECLARE @txt nvarchar(max);   
SELECT @txt=
'
BEGIN TRY
    DROP TABLE #tempT1;
    DROP TABLE #tempT2;
    DROP TABLE #SplitIDS;
END TRY
BEGIN CATCH
END CATCH

select * into #tempT1 from (
SELECT
a.tower_ids,
b.gw_sn,
a.gw_order_sn AS "采购订单号",  
a.contract_sn AS "合同号",  
a.item_id AS "行项目ID",  
a.customer as "客户名称",
CASE WHEN a.original_bidder=''1000014692''  THEN ''顺泰'' when a.original_bidder=''1000091718''  THEN ''瑜煌'' END AS ''中标主体'',
a.product_name AS "塔类型",  
a.project_name AS "工程名称",  
a.short_name AS "工程简称",  
a.delivery_date AS "合同交货期",  
a.job_sn AS "生产单号",  
a.job_sn,  
a.release_sn AS "生产工单",  
CONVERT(VARCHAR(100), a.release_date, 120)  AS "下达时间",  
-- case when a.GTXQ_pushed  = 1 THEN ''已上传'' when  a.GTXQ_pushed = 0 THEN ''未传'' END AS "参数规范-杆塔详情",
case when a.bom_eip_status = 1 THEN ''已上传'' when  a.bom_eip_status = 0 THEN ''未传'' END AS "参数规范-技术加工",  
case when a.eip_pushed =1 THEN ''已上传''  when a.eip_pushed is NULL THEN NULL ELSE ''未传'' END  as "参数规范-工程原材料",
case when a.YCL_pushed = 1 THEN ''已上传'' when  a.bom_eip_status = 0 THEN ''未传'' END AS "原材料数据",  
case when a.YCL_WZGL = 0 THEN ''未关联'' when  a.YCL_WZGL = 1 THEN ''未完全匹配'' when a.YCL_WZGL = 2 THEN ''完整关联'' END AS "原材料数据关联",  
a.SCSYGC_isComplete as "生产实验过程-完整性",
a.SCSYGC_isUpload as "生产实验过程-上传",
a.dxwd_isUpload as "镀锌温度",
case when a.BZ_exist = ''未生成'' then ''未生成''   when a.BZ_exist is NULL then null ELSE ''已生成'' END  as "成品数据-生成",
a.CP_isUpload as "成品数据-上传",
a.plan_start_date AS "工单-计划开始时间",  
a.plan_finish_date AS "工单-计划结束时间",  
a.actual_start_date AS "工单-实际开始时间",  
a.actual_finish_date AS "工单-实际结束时间",  
a.loftingPlanStartDate AS "理论计划开始时间",  
a.delivery_date AS "理论计划完成时间",  
a.nested_material_date AS "理论实际开始时间",
a.tower_sns AS "杆塔号"
   
   
   
-- 计划开始时间 若没有,则取 lofting_job.plan_start_date,
-- 计划完成时间 若没有,则取 contract.delivery_date,
-- 实际开始时间 若没有,则取 release.nested_material_date,
  
--   SELECT release_id,MIN(eip_pushed) AS min_eip,MAX(eip_pushed) AS max_eip FROM eip_tower GROUP BY release_id
FROM OPENQUERY(FD_MES_DB,''
   
   
SELECT
    a.*,
    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.customer,
        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,
        c.lofting_id,
        c.tower_ids,
        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)
        SUBSTRING(tower_sns, 1, 4000) AS 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.release_sn = d.release_sn
    WHERE
        b.type = 1 
        AND a.is_gw_order = 1 
        AND a.delete_state = 0 
        AND a.gw_order_sn IN ('+@OrderList+ ')
) AS a
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
   
'') a RIGHT JOIN #TempGWSN b on a.gw_order_sn=b.gw_sn ) ss;


select * into #tempT2 from (
  select * FROM OPENQUERY(FD_MES_DB,''
  SELECT a.id,a.tower_id,b.eip_pushed FROM tower_lofting a INNER JOIN eip_tower b on a.tower_id=b.tower_id''
  )
) s2

-- select * from #tempT1;    -- 得到总表
-- select * from #tempT2;    -- 得到杆塔表

CREATE TABLE #SplitIDS (
    job_sn varchar(255),
    id VARCHAR(255)
);

-- 拆分 tempT1 的 IDS 字段并插入到临时表
INSERT INTO #SplitIDS (job_sn, id)
SELECT 
    job_sn,
    value AS id
FROM 
    #tempT1
CROSS APPLY 
    STRING_SPLIT(tower_ids, '','');

-- select * from #SplitIDS

-- 继续后续查询
WITH JoinedData AS (
    SELECT 
        s.job_sn,
        s.id,
        t.eip_pushed
    FROM 
        #SplitIDS s
    LEFT JOIN 
        #tempT2 t ON s.id = t.id
),
GroupedData AS (
    SELECT 
        job_sn,
        CASE 
            WHEN COUNT(*) = SUM(eip_pushed) THEN ''已上传'' ELSE ''未上传''
        END AS GTXQ_up
    FROM 
        JoinedData
    GROUP BY 
        job_sn
)

-- 最终结果
SELECT 
    t.gw_sn as "国网订单号",
    t.采购订单号,
    t.合同号,
    t.行项目ID,
    t.客户名称,
    t.中标主体,
    t.塔类型,
    t.工程名称,
    t.工程简称,
    t.合同交货期,
    t.生产单号,
    t.生产工单,
    t.下达时间,
    g.GTXQ_up as "参数规范-杆塔详情",
    t."参数规范-技术加工",
    t."参数规范-工程原材料",
    t.原材料数据,
    t.原材料数据关联,
    t."生产实验过程-完整性",
    t."生产实验过程-上传",
    t.镀锌温度,
    t."成品数据-生成",
    t."成品数据-上传",
    t."工单-计划开始时间",
    t."工单-计划结束时间",
    t."工单-实际开始时间",
    t."工单-实际结束时间",
    t.理论计划开始时间,
    t.理论计划完成时间,
    t.理论实际开始时间,
    t.杆塔号

    
FROM 
    #tempT1 t
LEFT JOIN 
    GroupedData g ON t.job_sn = g.job_sn;



DROP TABLE #tempT1;
DROP TABLE #tempT2;
DROP TABLE #SplitIDS;
';
EXEC(@txt);

DROP TABLE #TempGWSN;
END



回复 支持 反对

使用道具 举报

304

主题

696

帖子

8774

积分

学生管理组

Rank: 8Rank: 8

积分
8774
8#
 楼主| 发表于 2025-3-12 15:19:51 | 只看该作者
本帖最后由 李维强-15级 于 2025-3-12 17:27 编辑

修复成品
[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
 
('4500858055');
  
  
  
    
-- 动态拼接订单号列表
DECLARE @OrderList NVARCHAR(MAX);
SELECT @OrderList = STUFF(
    (SELECT ',' + QUOTENAME(gw_sn, '""')
     FROM #TempGWSN
     ORDER BY id
     FOR XML PATH('')
    ), 1, 1, ''
);          
        
        
DECLARE @txt nvarchar(max);   
SELECT @txt=
'
BEGIN TRY
    DROP TABLE #tempT1;
    DROP TABLE #tempT2;
    DROP TABLE #SplitIDS;
END TRY
BEGIN CATCH
END CATCH
 
select * into #tempT1 from (
SELECT
a.tower_ids,
b.gw_sn,
a.gw_order_sn AS "采购订单号",  
a.contract_sn AS "合同号",  
a.item_id AS "行项目ID",  
a.customer as "客户名称",
CASE WHEN a.original_bidder=''1000014692''  THEN ''顺泰'' when a.original_bidder=''1000091718''  THEN ''瑜煌'' END AS ''中标主体'',
a.product_name AS "塔类型",  
a.project_name AS "工程名称",  
a.short_name AS "工程简称",  
a.delivery_date AS "合同交货期",  
a.job_sn AS "生产单号",  
a.job_sn,  
a.release_sn AS "生产工单",  
CONVERT(VARCHAR(100), a.release_date, 120)  AS "下达时间",  
-- case when a.GTXQ_pushed  = 1 THEN ''已上传'' when  a.GTXQ_pushed = 0 THEN ''未传'' END AS "参数规范-杆塔详情",
case when a.bom_eip_status = 1 THEN ''已上传'' when  a.bom_eip_status = 0 THEN ''未传'' END AS "参数规范-技术加工",  
case when a.eip_pushed =1 THEN ''已上传''  when a.eip_pushed is NULL THEN NULL ELSE ''未传'' END  as "参数规范-工程原材料",
case when a.YCL_pushed = 1 THEN ''已上传'' when  a.bom_eip_status = 0 THEN ''未传'' END AS "原材料数据",  
case when a.YCL_WZGL = 0 THEN ''未关联'' when  a.YCL_WZGL = 1 THEN ''未完全匹配'' when a.YCL_WZGL = 2 THEN ''完整关联'' END AS "原材料数据关联",  
a.SCSYGC_isComplete as "生产实验过程-完整性",
a.SCSYGC_isUpload as "生产实验过程-上传",
a.dxwd_isUpload as "镀锌温度",
case when a.BZ_exist = ''未生成'' then ''未生成''   when a.BZ_exist is NULL then null ELSE ''已生成'' END  as "成品数据-生成",
a.CP_isUpload as "成品数据-上传",
a.plan_start_date AS "工单-计划开始时间",  
a.plan_finish_date AS "工单-计划结束时间",  
a.actual_start_date AS "工单-实际开始时间",  
a.actual_finish_date AS "工单-实际结束时间",  
a.loftingPlanStartDate AS "理论计划开始时间",  
a.delivery_date AS "理论计划完成时间",  
a.nested_material_date AS "理论实际开始时间",
a.tower_sns AS "杆塔号"
    
    
    
-- 计划开始时间 若没有,则取 lofting_job.plan_start_date,
-- 计划完成时间 若没有,则取 contract.delivery_date,
-- 实际开始时间 若没有,则取 release.nested_material_date,
   
--   SELECT release_id,MIN(eip_pushed) AS min_eip,MAX(eip_pushed) AS max_eip FROM eip_tower GROUP BY release_id
FROM OPENQUERY(FD_MES_DB,''
    
    
SELECT
    a.*,
    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_sn,"未生成") as BZ_exist,        -- 包装是否存在
            g.CP_isUpload                                         -- 包装是否上传
                                  
FROM (
    SELECT
        a.gw_order_sn,
        a.contract_sn,
        a.item_id,
        a.customer,
        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,
        c.lofting_id,
        c.tower_ids,
        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)
        SUBSTRING(tower_sns, 1, 4000) AS 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.release_sn = d.release_sn
    WHERE
        b.type = 1 
        AND a.is_gw_order = 1 
        AND a.delete_state = 0 
        AND a.gw_order_sn IN ('+@OrderList+ ')
) AS a
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 release_sn,
      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 eip_product GROUP BY release_sn 
) as g on a.release_sn=g.release_sn   
'') a RIGHT JOIN #TempGWSN b on a.gw_order_sn=b.gw_sn ) ss;
 
 
select * into #tempT2 from (
  select * FROM OPENQUERY(FD_MES_DB,''
  SELECT a.id,a.tower_id,b.eip_pushed FROM tower_lofting a INNER JOIN eip_tower b on a.tower_id=b.tower_id''
  )
) s2
 
-- select * from #tempT1;    -- 得到总表
-- select * from #tempT2;    -- 得到杆塔表
 
CREATE TABLE #SplitIDS (
    job_sn varchar(255),
    id VARCHAR(255)
);
 
-- 拆分 tempT1 的 IDS 字段并插入到临时表
INSERT INTO #SplitIDS (job_sn, id)
SELECT 
    job_sn,
    value AS id
FROM 
    #tempT1
CROSS APPLY 
    STRING_SPLIT(tower_ids, '','');
 
-- select * from #SplitIDS
 
-- 继续后续查询
WITH JoinedData AS (
    SELECT 
        s.job_sn,
        s.id,
        t.eip_pushed
    FROM 
        #SplitIDS s
    LEFT JOIN 
        #tempT2 t ON s.id = t.id
),
GroupedData AS (
    SELECT 
        job_sn,
        CASE 
            WHEN COUNT(*) = SUM(eip_pushed) THEN ''已上传'' ELSE ''未上传''
        END AS GTXQ_up
    FROM 
        JoinedData
    GROUP BY 
        job_sn
)
 
-- 最终结果
SELECT 
    t.gw_sn as "国网订单号",
    t.采购订单号,
    t.合同号,
    t.行项目ID,
    t.客户名称,
    t.中标主体,
    t.塔类型,
    t.工程名称,
    t.工程简称,
    t.合同交货期,
    t.生产单号,
    t.生产工单,
    t.下达时间,
    g.GTXQ_up as "参数规范-杆塔详情",
    t."参数规范-技术加工",
    t."参数规范-工程原材料",
    t.原材料数据,
    t.原材料数据关联,
    t."生产实验过程-完整性",
    t."生产实验过程-上传",
    t.镀锌温度,
    t."成品数据-生成",
    t."成品数据-上传",
    t."工单-计划开始时间",
    t."工单-计划结束时间",
    t."工单-实际开始时间",
    t."工单-实际结束时间",
    t.理论计划开始时间,
    t.理论计划完成时间,
    t.理论实际开始时间,
    t.杆塔号
 
     
FROM 
    #tempT1 t
LEFT JOIN 
    GroupedData g ON t.job_sn = g.job_sn order by ''采购订单号'',''生产工单''
 
 
DROP TABLE #tempT1;
DROP TABLE #tempT2;
DROP TABLE #SplitIDS;
';
EXEC(@txt);
 
DROP TABLE #TempGWSN;
END




回复 支持 反对

使用道具 举报

304

主题

696

帖子

8774

积分

学生管理组

Rank: 8Rank: 8

积分
8774
9#
 楼主| 发表于 2025-3-14 15:52:00 | 只看该作者
0101043509
程序搜索出来7个,实际只有5个,
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

Archiver|手机版|小黑屋|cqutlab ( 渝ICP备15004556号

GMT+8, 2025-4-4 05:18 , Processed in 0.174218 second(s), 30 queries .

Powered by Discuz! X3.1

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表