CREATE PROCEDURE [dbo].[计划排产_系统类任务查询_导出] @订单号 int=0, @物料类型 nvarchar(50)='', --自制件、装配件 @合同号 nvarchar(50)='', @产品编码 nvarchar(50)='', @产品名称 nvarchar(50)='', @工序名称 nvarchar(50)='', @要求完工日期1 nvarchar(50)='', @要求完工日期2 nvarchar(50)='', @计划开始 nvarchar(50)='', @计划完成 nvarchar(50)='', @排产状态 nvarchar(50)='全部' , --全部，已完成、未完成 @指派对象 nvarchar(50)='全部', @辅助配件 nvarchar(50)='不含' AS BEGIN SET NOCOUNT ON; SELECT '系统类任务_' + REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(19), GETDATE(), 21), ':', ''), '-', ''), ' ', '-') AS tablename, '15' AS tabletype; SELECT 256*18 AS 钣金预达时间, 256*14 AS 销售订单, 256*10 AS 生产订单, 256*24 AS 物料编码, 256*24 AS 物料名称, 256*10 AS 计划数, 256*8 AS 齐套, 256*12 AS 发料齐套, 256*18 AS 计划开始, 256*12 AS 派工时间, 256*20 AS 二次派工, 256*14 AS 预计送检日期, 256*14 AS 销售要求日期, 256*14 AS 发货要求日期, 256*40 AS 异常报警信息, 256*40 AS 装配进行中, 256*40 AS 质检进行中, 256*14 AS 任务装配工时, 256*14 AS 任务质检工时, 256*14 AS 合同装配工时, 256*14 AS 合同质检工时, 256*24 AS 装配工序说明, 256*24 AS 质检工序说明, 256*14 AS 要求完工日期, 256*12 AS 工序名称, 256*10 AS 计划号, 256*18 AS 指派对象, 256*12 AS 指派数量, 256*10 AS 优先级, 256*20 AS 未完成说明, 256*20 AS 派工特殊备注, 256*14 AS 占用情况, 256*14 AS 最后编辑人, 256*20 AS 最后编辑时间; SELECT 0 AS 钣金预达时间, 0 AS 销售订单, 0 AS 生产订单, 0 AS 物料编码, 0 AS 物料名称, 0 AS 计划数, 0 AS 齐套, 0 AS 发料齐套, 0 AS 计划开始, 0 AS 派工时间, 0 AS 二次派工, 0 AS 预计送检日期, 0 AS 销售要求日期, 0 AS 发货要求日期, 0 AS 异常报警信息, 0 AS 装配进行中, 0 AS 质检进行中, 0 AS 任务装配工时, 0 AS 任务质检工时, 0 AS 合同装配工时, 0 AS 合同质检工时, 0 AS 装配工序说明, 0 AS 质检工序说明, 0 AS 要求完工日期, 0 AS 工序名称, 0 AS 计划号, 0 AS 指派对象, 0 AS 指派数量, 0 AS 优先级, 0 AS 未完成说明, 0 AS 派工特殊备注, 0 AS 占用情况, 0 AS 最后编辑人, 0 AS 最后编辑时间; DECLARE @selectStr nvarchar(max); DECLARE @selectStrWhere nvarchar(max)=' where [物料类型]=''pit_Resource'' and [自制件属性] like ''%装配%'' and 物料描述 like ''%系统%'' and isnull(任务状态,0)<4'; IF(@物料类型='自制件') SET @selectStr=' SELECT CONVERT(varchar(10), v.钣金预达时间, 120) AS 钣金预达时间, v.[合同号] AS 销售订单, v.[订单编号] AS 生产订单, v.[物料编号] AS 物料编码, v.[物料描述] AS 物料名称, TRIM(''.'' FROM TRIM(''0'' FROM CAST(v.[计划数量] AS varchar(30)))) AS 计划数, CASE WHEN v.齐套 = 1 THEN ''是'' ELSE ''否'' END AS 齐套, CONVERT(varchar(10), v.齐套时间, 120) AS 发料齐套, CONVERT(varchar(10), v.计划开始时间, 120) AS 计划开始, CONVERT(varchar(10), v.派工时间, 120) AS 派工时间, [二次派工] AS 二次派工, CONVERT(varchar(10), DATEADD(DAY, 3, CONVERT(date, v.齐套时间)), 120) AS 预计送检日期, CONVERT(varchar(10), s.交货日期理论齐套日期, 120) AS 销售要求日期, CONVERT(varchar(10), v.要求发货日期, 120) AS 发货要求日期, ISNULL(( SELECT STRING_AGG(CONVERT(nvarchar(max), lc.流程描述), N'','') WITHIN GROUP (ORDER BY lc.流程ID) FROM [YL_MESDB].[dbo].[流程表] lc WHERE lc.已关联计划号 = v.[订单编号] ), '''') AS 异常报警信息, ISNULL(( SELECT STRING_AGG(CONVERT(nvarchar(max), jxz.UserName + '': '' + CONVERT(varchar(19), jxz.StartTime, 120)), N'','') WITHIN GROUP (ORDER BY jxz.StartTime DESC) FROM ( SELECT DISTINCT t.操作人 AS UserName, t.开始时间 AS StartTime FROM [dbo].[YL_加工中心_操作记录表] t LEFT JOIN [dbo].[MES_接口_生产计划_生产任务] pt ON t.TaskAID = pt.AID INNER JOIN [YL_MESDB].[dbo].[登录基础数据_人员信息] ry ON t.操作人 = ry.Personnel_Name WHERE t.操作类别 = ''开始加工'' AND t.结束时间 IS NULL AND t.操作数值 IS NOT NULL AND pt.订单编号 = v.[订单编号] AND ry.班组 = ''装配'' ) jxz ), '''') AS 装配进行中, ISNULL(( SELECT STRING_AGG(CONVERT(nvarchar(max), jxz.UserName + '': '' + CONVERT(varchar(19), jxz.StartTime, 120)), N'','') WITHIN GROUP (ORDER BY jxz.StartTime DESC) FROM ( SELECT DISTINCT t.操作人 AS UserName, t.开始时间 AS StartTime FROM [dbo].[YL_加工中心_操作记录表] t LEFT JOIN [dbo].[MES_接口_生产计划_生产任务] pt ON t.TaskAID = pt.AID INNER JOIN [YL_MESDB].[dbo].[登录基础数据_人员信息] ry ON t.操作人 = ry.Personnel_Name WHERE t.操作类别 = ''开始加工'' AND t.结束时间 IS NULL AND t.操作数值 IS NOT NULL AND pt.订单编号 = v.[订单编号] AND ry.班组 = ''质检'' ) jxz ), '''') AS 质检进行中, CAST(ISNULL(zp.装配工时, 0) / 60.0 AS decimal(18,2)) AS 任务装配工时, CAST(ISNULL(zj1.质检工时, 0) / 60.0 AS decimal(18,2)) AS 任务质检工时, CAST(ISNULL(htzp.合同装配工时, 0) / 60.0 AS decimal(18,2)) AS 合同装配工时, CAST(ISNULL(htzj.合同质检工时, 0) / 60.0 AS decimal(18,2)) AS 合同质检工时, ISNULL(zp.装配工序说明, '''') AS 装配工序说明, ISNULL(zj1.质检工序说明, '''') AS 质检工序说明, CONVERT(varchar(10), [计划完成], 120) AS 要求完工日期, [工序名称] AS 工序名称, v.计划号 AS 计划号, 指派对象 AS 指派对象, TRIM(''.'' FROM TRIM(''0'' FROM CAST([指派数量] AS varchar(30)))) AS 指派数量, [优先级] AS 优先级, [未完成说明] AS 未完成说明, [派工特殊说明] AS 派工特殊备注, [占用情况] AS 占用情况, [最后编辑人] AS 最后编辑人, CONVERT(varchar(19), [最后编辑时间], 120) AS 最后编辑时间 FROM [View_生产订单_MES] v LEFT JOIN ( SELECT gs.订单号, SUM(gs.总时长) AS 装配工时, MAX(gs.工序说明) AS 装配工序说明 FROM [dbo].[View_生产工时视图] gs INNER JOIN [dbo].[登录基础数据_人员信息] ry ON gs.操作人 = ry.Personnel_Name WHERE ry.班组 = ''装配'' GROUP BY gs.订单号 ) zp ON v.[订单编号] = zp.订单号 LEFT JOIN ( SELECT gs.订单号, SUM(gs.总时长) AS 质检工时, MAX(gs.工序说明) AS 质检工序说明 FROM [dbo].[View_生产工时视图] gs INNER JOIN [dbo].[登录基础数据_人员信息] ry ON gs.操作人 = ry.Personnel_Name WHERE ry.班组 = ''质检'' GROUP BY gs.订单号 ) zj1 ON v.[订单编号] = zj1.订单号 LEFT JOIN ( SELECT gs.合同号, SUM(gs.总时长) AS 合同装配工时 FROM [dbo].[View_生产工时视图] gs INNER JOIN [dbo].[登录基础数据_人员信息] ry ON gs.操作人 = ry.Personnel_Name WHERE ry.班组 = ''装配'' GROUP BY gs.合同号 ) htzp ON v.[合同号] = htzp.合同号 LEFT JOIN ( SELECT gs.合同号, SUM(gs.总时长) AS 合同质检工时 FROM [dbo].[View_生产工时视图] gs INNER JOIN [dbo].[登录基础数据_人员信息] ry ON gs.操作人 = ry.Personnel_Name WHERE ry.班组 = ''质检'' GROUP BY gs.合同号 ) htzj ON v.[合同号] = htzj.合同号 LEFT JOIN SAP.SBO_YL.dbo.UBT_MES_ORDROPEN2 s ON v.合同号 = s.项目号 AND v.物料编号 = s.物料号'; IF(@订单号>0) SET @selectStrWhere=@selectStrWhere + ' and v.[订单编号]='+CONVERT(varchar(10),@订单号); IF(@合同号<>'') SET @selectStrWhere=@selectStrWhere + ' and [合同号] like ''%'+@合同号+'%'''; IF(@产品编码<>'') SET @selectStrWhere=@selectStrWhere + ' and [物料编号] like ''%'+@产品编码+'%'''; IF(@产品名称<>'') SET @selectStrWhere=@selectStrWhere + ' and [物料描述] like ''%'+@产品名称+'%'''; IF(@辅助配件 = '不含') SET @selectStrWhere=@selectStrWhere + ' and [物料描述] not like ''%软管总成%'' and [物料描述] not like ''%发货部件%'''; IF(@工序名称<>'') SET @selectStrWhere=@selectStrWhere + ' and [工序名称] = '''+@工序名称+''''; IF(@要求完工日期1 <>'') SET @selectStrWhere=@selectStrWhere + ' and [计划完成] >= '''+@要求完工日期1+''''; IF(@要求完工日期2 <>'') SET @selectStrWhere=@selectStrWhere + ' and [计划完成] <= '''+@要求完工日期2+''''; IF(@计划开始 <>'') SET @selectStrWhere=@selectStrWhere + ' and [计划开始时间] >= '''+@计划开始+''''; IF(@计划完成 <>'') SET @selectStrWhere=@selectStrWhere + ' and [计划完成时间] <= '''+@计划完成+''''; IF(@排产状态='未完成') SET @selectStrWhere=@selectStrWhere + ' and isnull([排产状态],0) = 0'; ELSE IF(@排产状态='已完成') SET @selectStrWhere=@selectStrWhere + ' and [排产状态] = 1'; IF(@物料类型='自制件') SET @selectStrWhere=@selectStrWhere + ' and [不装配] <> ''否'''; IF(@物料类型='装配件') SET @selectStrWhere=@selectStrWhere + ' and [不装配] = ''否'''; IF(@指派对象<>'全部') SET @selectStrWhere=@selectStrWhere + ' and [指派对象] in (SELECT value FROM STRING_SPLIT('''+@指派对象+''', '',''))'; SET @selectStrWhere = @selectStrWhere + ' ORDER BY CASE WHEN 钣金预达时间 IS NULL THEN 1 ELSE 0 END, 钣金预达时间, v.计划开始时间'; SET @selectStr=@selectStr+@selectStrWhere; PRINT @selectStr; EXEC sp_executesql @selectStr; END