/****** Object: StoredProcedure [dbo].[PEMMZRecord_QueryDoMethod] Script Date: 2022/11/3 10:41:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: < QIUBB>
-- Create date: <2022.11.01>
-- Description: 故障维修记录数据查询
-- =============================================
ALTER PROCEDURE [dbo].[PEMMZRecord_QueryDoMethod]
@I_Sender nvarchar(200)='', --客户端执行按钮
@I_ReturnMessage nvarchar(max)='' output, --返回的信息,支持多语言
@I_ExceptionFieldName nvarchar(100)='' output, --向客户端报告引起冲突的字段
@I_LanguageId char(1)='1', --客户端传入的语言ID
@I_PlugInCommand varchar(5)='', --插件命令
@I_OrBitUserId char(12)='', --用户ID
@I_OrBitUserName nvarchar(100)='', --用户名
@I_ResourceId char(12)='', --资源ID(如果资源不在资源清单中，那么它将是空的)
@I_ResourceName nvarchar(100)='', --资源名
@I_PKid char(12) ='', --主键
@I_ParentPKId char(12)='', --父级主键
@I_Parameter nvarchar(100)='', --插件参数
--以上变量为系统服务固定接口参数，必须在每一个DoEvent过程中实现.
--@pageIndex int, -- 第几页
--@pageSize int , -- 每页包含的记录数
@ExceptionLocationId CHAR(12)='',
@ExceptionNumber CHAR(12)='',
@AssetName NVARCHAR(100)='',
@WorkcenterId NVARCHAR(100)='',
@ExceptionTypeName NVARCHAR(100)='',
@EAM_EqAssetId CHAR(12)='',
@ExcpTypeItemName NVARCHAR(100)='',
@ProductionExceptionManagementId CHAR(12)='',
@XCWaitTime NVARCHAR(50)='', ----现场等待时长
@MaintenanceDuration NVARCHAR(50)='', ---- 维修时长
@FailureTime DATETIME='',
@CreateUserId CHAR(12)='',
@SheBHandler NVARCHAR(50)='', ----设备部签到人
@HandleTime DATETIME='', --- 签到确认时间
@Diagnostician NVARCHAR(50)='', --故障诊断人
@DiagnosisTime DATETIME ='', --故障诊断时间
@Maintainer NVARCHAR(50)='', --维修人
@MaintenanceTime DATETIME='',-- 维修时间
@XCHandler NVARCHAR(50)='', ----现场确认人
@XCHandleTime DATETIME='', --- 现场确认时间
@WorkcenterDescription NVARCHAR(200)='', --生产线
@SiteName NVARCHAR(100)='', ---场地
@SiteId CHAR(12)='',--- 场地ID
@Minute NVARCHAR(20)='', ---分钟
@StartDate DATETIME=null,
@EndDate DATETIME=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @SDate NVARCHAR(50) = CONVERT(NVARCHAR(25),@StartDate,25) +' 00:00:00.000 '
DECLARE @EDate NVARCHAR(50) = CONVERT(NVARCHAR(25),@EndDate,25) +' 23:59:59.559 '
SET @SDate = Convert(varchar,@StartDate,25)
SET @EDate = Convert(varchar,@EndDate,25)
-- 检查查询条件
--IF ISNULL(@SiteName,'')=''
--BEGIN
-- SET @I_ReturnMessage = 'ServerMessage:请选择场地!'
-- RETURN -1
--END
----产线不能为空
--IF ISNULL(@WorkcenterDescription,'')=''
--BEGIN
-- SET @I_ReturnMessage = 'ServerMessage:请选择生产线!'
-- RETURN -1
--END
----开始日期不能为空
--IF ISNULL(@StartDate,'')=''
--BEGIN
-- SET @I_ReturnMessage = 'ServerMessage:请选择开始日期!'
-- RETURN -1
--END
----结束日期不能为空
--IF ISNULL(@EndDate,'')=''
--BEGIN
-- SET @I_ReturnMessage = 'ServerMessage:请选择结束日期!'
-- RETURN -1
--END
---创建临时表
--------创建故障维修记录临时表-------------------------------
IF OBJECT_ID('tempdb..#TB') IS NOT NULL
DROP TABLE #TB
CREATE TABLE #TB(
SiteName NVARCHAR(100), ---场地
WorkcenterDescription NVARCHAR(200), --生产线
AssetName NVARCHAR(200),
ExceptionTypeName NVARCHAR(100),
ExcpTypeItemName NVARCHAR(100),
CreateUserId NVARCHAR(100),
FailureTime DATETIME,
SheBHandler NVARCHAR(100), ----设备部签到人
HandleTime DATETIME, --- 签到确认时间
Diagnostician NVARCHAR(100), --故障诊断人
DiagnosisTime DATETIME, --故障诊断时间
Maintainer NVARCHAR(100), --维修人
MaintenanceTime DATETIME,-- 维修时间
XCHandler NVARCHAR(50), ----现场确认人
XCHandleTime DATETIME, --- 现场确认时间
ExceptionNumber NVARCHAR(20),--异常单号
XCWaitTime NVARCHAR(50), ----现场等待时长
MaintenanceDuration NVARCHAR(50) ---- 维修时长
)
----定义游标---
DECLARE PemID_cursor CURSOR LOCAL
FOR
SELECT ProductionExceptionManagementId FROM dbo.ProductionExceptionManagement WHERE ExceptionStatusId<>'' ---状态不能为空
FOR READ ONLY
---打开游标
OPEN PemID_cursor
---读取数据
FETCH NEXT FROM PemID_cursor
INTO @ProductionExceptionManagementId
WHILE @@FETCH_STATUS=0
BEGIN
SELECT
@ExceptionLocationId = ExceptionLocationId
FROM ExceptionLocation Ex LEFT OUTER JOIN dbo.ProductionExceptionManagement Pd ON Ex.FactoryId=pd.FactoryId AND Ex.SiteId=Pd.SiteId
AND EX.WorkcenterId=Pd.WorkcenterId WHERE Ex.ExceptionTypeId=Pd.ExceptionTypeId AND ProductionExceptionManagementId=@ProductionExceptionManagementId
-----查找单号-----------------
SET @ExceptionNumber=(SELECT ExceptionNumber FROM dbo.ProductionExceptionManagement WHERE ProductionExceptionManagementid=@ProductionExceptionManagementId)
----异常类型------------------
SET @ExceptionTypeName=(SELECT EXT.ExceptionTypeName FROM dbo.ProductionExceptionManagement PRT
LEFT JOIN ExceptionTypeItem EXM ON PRT.ExceptionTypeItemId=EXM.ExceptionTypeItemId
LEFT JOIN ExceptionType EXT ON EXM.ExceptionTypeId=EXT.ExceptionTypeId
WHERE PRT.ProductionExceptionManagementId=@ProductionExceptionManagementId)
-----查找生产线----
SET @WorkcenterDescription=(SELECT DISTINCT
--Workcenter.WorkcenterId ,
--Workcenter.WorkcenterName,
Workcenter.WorkcenterDescription
--Workcenter.SiteId,
--[Site].SiteName
--[Site].FactoryId,
--Factory.FactoryName
FROM dbo.Workcenter
INNER JOIN dbo.UserCode AS WorkcenterType ON Workcenter.WorkcenterTypeId = WorkcenterType.UserCodeId
INNER JOIN dbo.UserCode ON WorkcenterType.ParentUserCodeId = UserCode.UserCodeId
INNER JOIN dbo.[Site] ON [Site].SiteId = Workcenter.SiteId
INNER JOIN dbo.Factory ON [Site].FactoryId = Factory.FactoryId
INNER JOIN dbo.SysUser ON SysUser.SiteId = [Site].SiteId
INNER JOIN dbo.ProductionExceptionManagement pem ON pem.WorkcenterId=dbo.Workcenter.WorkcenterId
WHERE Workcenter.[Activation] = 1
AND pem.ProductionExceptionManagementId=@ProductionExceptionManagementId
AND Workcenter.WorkcenterTypeId = 'URC100000AAE'
AND (Workcenter.SiteId = 'SIT1000000U7'
OR (Workcenter.SiteId = 'SIT1000000U8')
OR (Workcenter.SiteId = 'SIT1000000U6' AND Workcenter.WorkcenterId IN ('WKC1000001IQ', 'WKC1000001IR', 'WKC1000001IS', 'WKC1000001IT', 'WKC1000001IU', 'WKC1000001IV', 'WKC1000001IW', 'WKC1000001IX', 'WKC1000001IY', 'WKC1000001IZ', 'WKC1000001J0', 'WKC1000001J1', 'WKC1000001JV', 'WKC1000001JW','WKC1000001P2', 'WKC1000001P1','WKC1000001J2'))))
---查找工作场地---
SET @SiteName =(SELECT DISTINCT
--Workcenter.WorkcenterId ,
--Workcenter.WorkcenterName,
--Workcenter.WorkcenterDescription
--Workcenter.SiteId,
[Site].SiteName
--[Site].FactoryId,
--Factory.FactoryName
FROM dbo.Workcenter
INNER JOIN dbo.UserCode AS WorkcenterType ON Workcenter.WorkcenterTypeId = WorkcenterType.UserCodeId
INNER JOIN dbo.UserCode ON WorkcenterType.ParentUserCodeId = UserCode.UserCodeId
INNER JOIN dbo.[Site] ON [Site].SiteId = Workcenter.SiteId
INNER JOIN dbo.Factory ON [Site].FactoryId = Factory.FactoryId
INNER JOIN dbo.SysUser ON SysUser.SiteId = [Site].SiteId
INNER JOIN dbo.ProductionExceptionManagement pem ON pem.WorkcenterId=dbo.Workcenter.WorkcenterId
WHERE Workcenter.[Activation] = 1
AND pem.ProductionExceptionManagementId=@ProductionExceptionManagementId
AND Workcenter.WorkcenterTypeId = 'URC100000AAE'
AND (Workcenter.SiteId = 'SIT1000000U7'
OR (Workcenter.SiteId = 'SIT1000000U8')
OR (Workcenter.SiteId = 'SIT1000000U6' AND Workcenter.WorkcenterId IN ('WKC1000001IQ', 'WKC1000001IR', 'WKC1000001IS', 'WKC1000001IT', 'WKC1000001IU', 'WKC1000001IV', 'WKC1000001IW', 'WKC1000001IX', 'WKC1000001IY', 'WKC1000001IZ', 'WKC1000001J0', 'WKC1000001J1', 'WKC1000001JV', 'WKC1000001JW','WKC1000001P2', 'WKC1000001P1','WKC1000001J2'))))
--查找工作中心ID--
SELECT @WorkcenterId=(SELECT WorkcenterId FROM dbo.ProductionExceptionManagement WHERE ProductionExceptionManagementId=@ProductionExceptionManagementId)
----查找资产名称ID-----
SELECT @EAM_EqAssetId=(SELECT EAM_EqAssetId FROM dbo.ProductionExceptionManagement WHERE ProductionExceptionManagementId=@ProductionExceptionManagementId)
---故障项目-----------------
SET @ExcpTypeItemName=(SELECT EXM.ExcpTypeItemName FROM dbo.ProductionExceptionManagement PRT
LEFT JOIN ExceptionTypeItem EXM ON PRT.ExceptionTypeItemId=EXM.ExceptionTypeItemId
LEFT JOIN ExceptionType EXT ON EXM.ExceptionTypeId=EXT.ExceptionTypeId
WHERE PRT.ProductionExceptionManagementId=@ProductionExceptionManagementId)
---故障机台------------------
SET @AssetName=(SELECT IIF(FAssetNumber='',IIF(AssetsAbbr='',AssetName,AssetsAbbr),AssetsAbbr+' '+FAssetNumber) AS AssetName FROM EAM_EqAsset
WHERE WorkcenterId=@WorkcenterId AND EAM_EqAssetId=@EAM_EqAssetId AND AssetTypeId IN (SELECT EAM_EqAssetTypeId FROM dbo.EAM_EqAssetType WHERE TypeLevel >0
AND RootAssetTypeId IN (SELECT EAM_EqAssetTypeId FROM EAM_EqAssetType WHERE TypeName IN ('铸造模具', '铸造设备', '机加设备', '海通设备'))))
---现场发起人---
SET @CreateUserId=(SELECT su.UserDescription FROM dbo.ProductionExceptionManagement pem INNER JOIN dbo.SysUser su ON pem.CreateUserId=su.UserId
WHERE ProductionExceptionManagementId=@ProductionExceptionManagementId )
---故障发起时间---
SET @FailureTime=(SELECT CreateDate FROM dbo.ProductionExceptionHandle WHERE ProductionExceptionManagementId=@ProductionExceptionManagementId AND StatusId='BLST000001EL' )
---设备部签到处理人--
--SET @SheBHandler=(SELECT su.UserDescription FROM dbo.ProductionExceptionHandle ph INNER JOIN dbo.SysUser su ON ph.Handler=su.UserId
--WHERE ProductionExceptionManagementId=@ProductionExceptionManagementId AND ph.StatusId='BLST000001EN')
SET @SheBHandler=(SELECT Handler FROM dbo.ProductionExceptionHandle WHERE ProductionExceptionManagementId=@ProductionExceptionManagementId AND StatusId='BLST000001EN')
---签到确认时间
SET @HandleTime=(SELECT DISTINCT CreateDate FROM dbo.ProductionExceptionHandle WHERE ProductionExceptionManagementId=@ProductionExceptionManagementId AND StatusId='BLST000001EN')
---故障诊断人
SET @Diagnostician =(SELECT Diagnostician FROM dbo.ProductionExceptionHandle WHERE ProductionExceptionManagementId=@ProductionExceptionManagementId AND StatusId='BLST000001EO' )
---故障诊断时间
SET @DiagnosisTime=(SELECT CreateDate FROM dbo.ProductionExceptionHandle WHERE ProductionExceptionManagementId=@ProductionExceptionManagementId AND StatusId='BLST000001EO' )
--维修人-
SET @Maintainer=(SELECT top 1 Maintainer FROM dbo.ProductionExceptionHandle WHERE ProductionExceptionManagementId=@ProductionExceptionManagementId AND StatusId='BLST000001GQ' ORDER BY CreateDate desc)
--维修到场时间--
SET @MaintenanceTime=(SELECT top 1 CreateDate FROM dbo.ProductionExceptionHandle WHERE ProductionExceptionManagementId=@ProductionExceptionManagementId AND StatusId='BLST000001GQ' ORDER BY CreateDate desc)
----现场确认人
SET @XCHandler =(SELECT su.UserDescription FROM dbo.ProductionExceptionHandle ph INNER JOIN dbo.SysUser su ON ph.Handler=su.UserId
WHERE ProductionExceptionManagementId=@ProductionExceptionManagementId AND ph.StatusId='BLST000001ER')
--- 现场确认时间
SET @XCHandleTime =(SELECT DISTINCT HandleTime FROM dbo.ProductionExceptionHandle WHERE ProductionExceptionManagementId=@ProductionExceptionManagementId AND StatusId='BLST000001ER')
--- XCWaitTime AS 现场恢复等待时长---
--SET @XCWaitTime=(SELECT DATEDIFF(MINUTE,@FailureTime,@XCHandleTime))
SET @XCWaitTime=CAST ( CAST ( DATEDIFF ( ss, @FailureTime, @XCHandleTime ) / ( 60 * 60 * 24 ) AS INT ) AS VARCHAR ) + '天' + CAST ( CAST ( DATEDIFF ( ss, @FailureTime, @XCHandleTime ) % 86400 / 3600 AS INT ) AS VARCHAR ) + '小时' + CAST ( CAST ( DATEDIFF ( ss, @FailureTime, @XCHandleTime ) % 3600 / 60 AS INT ) AS VARCHAR ) + '分' + CAST ( CAST ( DATEDIFF ( ss, @FailureTime, @XCHandleTime ) % 60 AS INT ) AS VARCHAR ) + '秒'
---现场恢复等待时长----
---MaintenanceDuration AS 修护课维修时长---
---SET @MaintenanceDuration=(SELECT DATEDIFF(MINUTE,@HandleTime,@XCHandleTime))
SET @MaintenanceDuration=CAST ( CAST ( DATEDIFF ( ss, @HandleTime, @XCHandleTime ) / ( 60 * 60 * 24 ) AS INT ) AS VARCHAR ) + '天' + CAST ( CAST ( DATEDIFF ( ss, @HandleTime, @XCHandleTime ) % 86400 / 3600 AS INT ) AS VARCHAR ) + '小时' + CAST ( CAST ( DATEDIFF ( ss, @HandleTime, @XCHandleTime ) % 3600 / 60 AS INT ) AS VARCHAR ) + '分' + CAST ( CAST ( DATEDIFF ( ss, @HandleTime, @XCHandleTime ) % 60 AS INT ) AS VARCHAR ) + '秒'
-----修护课维修时长------
--------临时表插入值-------------------------------
INSERT INTO #TB (
SiteName ,
WorkcenterDescription ,
AssetName ,
ExceptionTypeName ,
ExcpTypeItemName ,
CreateUserId ,
FailureTime ,
SheBHandler ,
HandleTime ,
Diagnostician ,
DiagnosisTime ,
Maintainer ,
MaintenanceTime ,
XCHandler ,
XCHandleTime ,
ExceptionNumber,
XCWaitTime ,
MaintenanceDuration
)
SELECT
@SiteName ,
@WorkcenterDescription ,
@AssetName ,
@ExceptionTypeName ,
@ExcpTypeItemName ,
@CreateUserId ,
@FailureTime ,
@SheBHandler ,
@HandleTime ,
@Diagnostician ,
@DiagnosisTime ,
@Maintainer,
@MaintenanceTime,
@XCHandler,
@XCHandleTime,
@ExceptionNumber,
@XCWaitTime,
@MaintenanceDuration
--进入下一行数据--
FETCH NEXT FROM PemID_cursor
INTO @ProductionExceptionManagementId
END
---关闭游标--------------
CLOSE PemID_cursor
---释放游标------------------
DEALLOCATE PemID_cursor
----查询故障维修记录----
SELECT
SiteName AS 场地,
WorkcenterDescription AS 生产线,
AssetName AS 故障机台,
ExceptionTypeName AS 设备异常类型,
ExcpTypeItemName AS 故障项目,
XCWaitTime AS 现场恢复等待时长,
MaintenanceDuration AS 修护课维修时长,
CreateUserId AS 现场发起人,
FailureTime AS 故障发起时间,
SheBHandler AS 修护课签到人,
HandleTime AS 签到处理时间,
Diagnostician AS 故障诊断人,
DiagnosisTime AS 诊断时间,
Maintainer AS 故障维修人,
MaintenanceTime AS 维修处理时间,
XCHandler AS 恢复生产确认人,
XCHandleTime AS 现场恢复生产时间,
ExceptionNumber AS 异常单号
FROM #TB where
-- WorkcenterDescription=@WorkcenterDescription
--AND SiteName=@SiteName
CONVERT(varchar, FailureTime, 25) >= @StartDate
AND CONVERT(varchar, FailureTime, 25) <= @EndDate
ORDER BY FailureTime desc
----删除临时表------
DROP TABLE #TB
END
869
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
