USE [ICDAS2_1]
GO
/****** Object:  StoredProcedure [dbo].[EquipmentMasterAlarmAuditTrail_Manage]    Script Date: 09/29/2020 14:42:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Exec EquipmentMasterAlarmAuditTrail_Manage 1001,'2018-08-24 14:52:40.000','','200','test',0
ALTER PROCEDURE [dbo].[EquipmentMasterAlarmAuditTrail_Manage]
	@EquipmentMasterId as BigInt=0,
	@LogDateTime as Datetime,
	@AlarmEvent as varchar(1000)='',
	@AlarmCode as varchar(10)='',
	@ReturnMessage as varchar(255)='' output,
	@ReturnCode As Integer=0 output
AS
--set nocount on;
Begin
	
	Declare @EquipmentTypeGroup as varchar(10),@EquipmentAccessUserName as varchar(1000)
	Declare @AlarmId as Bigint,@AlarmAlertEmail as Bigint,@AlarmAlertSMS as Bigint
	Declare @ver as Bigint
	Declare @TempratureLowerLowerLimit as nvarchar(10)
	Declare @TempratureLowerLimit as nvarchar(10)
	Declare @TempratureSetValue as nvarchar(10)
	Declare @TempratureUpperLimit as nvarchar(10)
	Declare @TempratureUpperUpperLimit as nvarchar(10)
		
	Declare @HumidityLowerLowerLimit as nvarchar(10)
	Declare @HumidityLowerLimit as nvarchar(10)
	Declare @HumiditySetValue as nvarchar(10)
	Declare @HumidityUpperLimit as nvarchar(10)
	Declare @HumidityUpperUpperLimit as nvarchar(10)
	
	Declare @ParameterType1 as nvarchar(5)
	Declare @ParameterType2 as nvarchar(5)
	
	Declare @AlarmInfo as nvarchar(500)
	Declare @ParameterNo as Tinyint
	
	Declare @EquipmentLastLogDateTime as datetime,@AlarmLastLogDatetime as datetime,@AlarmLastLogDatetime1 as datetime

	Declare @EmailSystemApp as nvarchar(10)
	Declare @SMSSystemApp as nvarchar(10)
	Declare @EmailSystemBranch as nvarchar(10)
	Declare @SMSSystemBranch as nvarchar(10)
	--declare @type as nvarchar(10)
	
	Set @AlarmId=0
	Set @AlarmAlertEmail=0
	Set @AlarmAlertSMS=0
	Set @AlarmInfo=''
	Set @ParameterNo=0
	Set @ParameterType1=''
	Set @ParameterType2=''
	
	Select top 1 @AlarmLastLogDatetime = AlarmLogDateTime from EquipmentMasterAlarmAuditTrail
	where EquipmentMasterId = @EquipmentMasterId
	order by EquipmentMasterAlarmAuditTrailId desc
	
	Select top 1 @AlarmLastLogDatetime1 = AlarmLogDateTime from EquipmentMasterAlarmAuditTrail
	where EquipmentMasterId = @EquipmentMasterId and AlarmEvent <> 'COMMUNICATION FAILURE'
	order by EquipmentMasterAlarmAuditTrailId desc
	
	--IF DateDiff(s, @LogDateTime,@AlarmLastLogDatetime1) > 0
	--	Begin
	--		update EquipmentMasterAlarmAuditTrail
	--		set EquipmentMasterId = @EquipmentMasterId
	--		where EquipmentMasterId = @EquipmentMasterId
	--		return
	--	End
	
	--IF DateDiff(day,@LogDateTime,GETDATE()) > 0
	--	Begin
	--		update EquipmentMasterAlarmAuditTrail
	--		set EquipmentMasterId = @EquipmentMasterId
	--		where EquipmentMasterId = @EquipmentMasterId
	--		return
	--	End
	
	if @AlarmEvent = 'COMMUNICATION FAILURE'
		Begin
		
			Select @AlarmId = count(EquipmentMasterId) 
			From EquipmentMaster 
			Where EquipmentMasterId = @EquipmentMasterId
			AND DATEDIFF(n,EquipmentLastLogDateTime,getdate()) >=60
			AND DATEDIFF(n,(select top 1 (AlarmLogDateTime) from EquipmentMasterAlarmAuditTrail where AlarmEvent='COMMUNICATION FAILURE' and EquipmentMasterId = @EquipmentMasterId order by EquipmentMasterAlarmAuditTrailId desc),getdate()) > 60
			
			IF @AlarmId = 0
				Begin
					--Set @AlarmAlertEmail=0
					--SET @AlarmAlertSMS=0
					SET @AlarmId = 1
				END
			Else
				Begin
					SET @AlarmId = 0
				END
		End
	Else
		Begin
		
			Select @EquipmentTypeGroup=Isnull(EquipmentTypeGroup,'') ,@EquipmentAccessUserName=EM.EquipmentAccessUserName
			,@ver=Isnull([Version],0)
			
			,@TempratureLowerLowerLimit=Isnull(TempratureLowerLowerLimit,'0')
			,@TempratureLowerLimit=Isnull(TempratureLowerLimit,'0')
			,@TempratureSetValue=Isnull(TempratureSetValue,'0')
			,@TempratureUpperLimit=Isnull(TempratureUpperLimit,'0')
			,@TempratureUpperUpperLimit=Isnull(TempratureUpperUpperLimit,'0')
			
			,@HumidityLowerLowerLimit=Isnull(HumidityLowerLowerLimit,'0')
			,@HumidityLowerLimit=Isnull(HumidityLowerLimit,'0')
			,@HumiditySetValue=Isnull(HumiditySetValue,'0')
			,@HumidityUpperLimit=Isnull(HumidityUpperLimit,'0')
			,@HumidityUpperUpperLimit=Isnull(HumidityUpperUpperLimit,'0')
			
			From EquipmentMaster as EM inner join EquipmentType as ET on EM.EquipmentType = ET.EquipmentType
			Where EM.EquipmentMasterId=@EquipmentMasterId
			
			
			Select Distinct @ParameterType1=ParameterType From EquipmentMasterParameter Where EquipmentMasterId=@EquipmentMasterId and ParameterAxis=0
			Select Distinct @ParameterType2=ParameterType From EquipmentMasterParameter Where EquipmentMasterId=@EquipmentMasterId and ParameterAxis=1
	--set @type =(select et.EquipmentTypeGroup from EquipmentMaster em inner join EquipmentType et on em.EquipmentType = et.EquipmentType where EquipmentMasterId = @EquipmentMasterId)
	
			Select 	@AlarmEvent=( 
									Case 
									When @AlarmCode=59 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,1),'')
									When @AlarmCode=60 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,2),'')
									When @AlarmCode=61 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,3),'')
									When @AlarmCode=62 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,4),'')
									When @AlarmCode=63 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,5),'')
									When @AlarmCode=64 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,6),'')
									When @AlarmCode=84 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,1),'')
									When @AlarmCode=85 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,2),'')
									When @AlarmCode=86 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,3),'')
									When @AlarmCode=87 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,4),'')
									When @AlarmCode=88 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,5),'')
									When @AlarmCode=89 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,6),'')
									When @AlarmCode=201 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,1),'')
									When @AlarmCode=202 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,2),'')
									When @AlarmCode=203 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,3),'')
									When @AlarmCode=204 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,4),'')
									When @AlarmCode=205 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,5),'')
									When @AlarmCode=206 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,6),'')
									When @AlarmCode=207 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,7),'')
									When @AlarmCode=208 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,8),'')
									When @AlarmCode=209 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,9),'')
									When @AlarmCode=210 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,10),'')
									When @AlarmCode=211 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,11),'')
									When @AlarmCode=212 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,12),'')
									When @AlarmCode=213 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,13),'')
									When @AlarmCode=214 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,14),'')
									When @AlarmCode=215 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,15),'')
									When @AlarmCode=216 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,16),'')
									When @AlarmCode=217 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,17),'')
									When @AlarmCode=218 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,18),'')
									When @AlarmCode=219 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,19),'')
									When @AlarmCode=220 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,20),'')
									When @AlarmCode=221 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,21),'')
									When @AlarmCode=222 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,22),'')
									When @AlarmCode=223 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,23),'')
									When @AlarmCode=224 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,24),'')
									When @AlarmCode=225 Then 'DOOR ACCESS BY ' + ISNULL(dbo.GetDoorAccessUserName(@EquipmentAccessUserName,25),'')
									
									else
									ACL.AlarmDescription  END
								)
			,@AlarmAlertEmail=isnull(EACEmail.EquipmentAlarmConfigId,0)
			,@AlarmAlertSMS=isnull(EACSMS.EquipmentAlarmConfigId,0)
			,@ParameterNo=ParameterNo
			from AlarmCodeLookUp as ACL Left join EquipmentAlarmConfig as EACEmail on ACL.AlarmCodeLookUpId=EACEmail.AlarmCodeLookUpId and EACEmail.AlarmType='E' and EACEmail.EquipmentMasterId=@EquipmentMasterId
			Left join EquipmentAlarmConfig as EACSMS on ACL.AlarmCodeLookUpId=EACSMS.AlarmCodeLookUpId and EACSMS.AlarmType='S' and EACSMS.EquipmentMasterId=@EquipmentMasterId
			Where AlarmCode=@AlarmCode  and EquipmentTypeGroup like '%' + @EquipmentTypeGroup + '%'
			
				
			Select @AlarmId=EquipmentMasterAlarmAuditTrailId From EquipmentMasterAlarmAuditTrail  
			Where EquipmentMasterId=@EquipmentMasterId and AlarmLogDateTime=@LogDateTime and AlarmEvent=@AlarmEvent
			
			if @ParameterNo >0--********************************************
				Begin
					if @ParameterType1='T1'
						Begin
							Set @AlarmInfo=@AlarmInfo + '~Chamber 1'
						End
					Else if @ParameterType1='T'
						Begin
							Set @AlarmInfo=@AlarmInfo + '~Temperature'
						End
					
					Set @AlarmInfo=@AlarmInfo + '~SV=' + @TempratureSetValue
						
					IF @ParameterType1 = 'T' AND (@EquipmentTypeGroup = 'CI' OR @EquipmentTypeGroup = 'PS' OR @EquipmentTypeGroup = 'TC')
						BEGIN
						  Set @AlarmInfo=@AlarmInfo + '~PV1=0' + Cast ((@ParameterNo) as nvarchar(25))
						END			
					else if Len(Cast((@ParameterNo * 2) as nvarchar(25))) = 1
						Begin	
							Set @AlarmInfo=@AlarmInfo + '~PV1=0' + Cast (((@ParameterNo * 2) -1) as nvarchar(25))
						End
					else
						Begin	
							Set @AlarmInfo=@AlarmInfo + '~PV1=' + Cast (((@ParameterNo * 2) -1) as nvarchar(25))
						End
					
					if charindex('D',@EquipmentTypeGroup) > 0 or charindex('H',@EquipmentTypeGroup) > 0
						Begin 
						
							if @ParameterType2='T2'
								Begin
									Set @AlarmInfo=@AlarmInfo + '~Chamber 2'
								End
							Else if @ParameterType2='H'
								Begin
									Set @AlarmInfo=@AlarmInfo + '~Humidity'
								End
							Else if @ParameterType2='T'
								Begin
									Set @AlarmInfo=@AlarmInfo + '~Temperature'
								End
									
							Set @AlarmInfo=@AlarmInfo + '~SV=' + @HumiditySetValue
						
							if Len(Cast ((@ParameterNo *2) as nvarchar(25))) =1
								Begin	
									Set @AlarmInfo=@AlarmInfo + '~PV2=0' + Cast ((@ParameterNo *2) as nvarchar(25))
								End
							else
								Begin	
									Set @AlarmInfo=@AlarmInfo + '~PV2=' + Cast ((@ParameterNo *2) as nvarchar(25))
								End
						
						End
				End--********************************************
			
		End
		
		print(@AlarmId)
		
		
	if @AlarmId = 0 and @AlarmEvent<>''
		Begin
			if @AlarmAlertEmail =0 
				Begin
					Set @AlarmAlertEmail=3
				End
			Else
				Begin
					Set @AlarmAlertEmail=0
				End
				
			if @AlarmAlertSMS =0 
				Begin
					Set @AlarmAlertSMS=3
				End
			Else
				Begin
					Set @AlarmAlertSMS=0
				End
			if @AlarmEvent<>'COMMUNICATION FAILURE'
				BEGIN 
					Insert into EquipmentMasterAlarmAuditTrail(EquipmentMasterId,AlarmLogDateTime,AlarmEvent,EmailStatus,SMSStatus,AlarmInfo) 
					values (@EquipmentMasterId,@LogDateTime,Upper(@AlarmEvent),@AlarmAlertEmail,@AlarmAlertSMS,@AlarmInfo)
				End
			Else
				Begin
					--select @EmailSystemApp=EmailSystem,@SMSSystemApp=SMSSystem from ApplicationSetup
					--select @EmailSystemApp=EmailSystem,@SMSSystemApp=SMSSystem from ApplicationSetup
					select @EmailSystemBranch=EmailSystem,@SMSSystemBranch=SMSSystem from BranchMaster
			
						If @AlarmEvent = 'COMMUNICATION FAILURE' and @SMSSystemBranch = 0 and @EmailSystemBranch = 0
						Begin
								Insert into EquipmentMasterAlarmAuditTrail(EquipmentMasterId,AlarmLogDateTime,AlarmEvent,EmailStatus,SMSStatus,AlarmInfo) 
								values (@EquipmentMasterId,@LogDateTime,Upper(@AlarmEvent),'0','0',@AlarmInfo)
						End
						If @AlarmEvent = 'COMMUNICATION FAILURE'  and @SMSSystemBranch <> 0	and @EmailSystemBranch <> 0
						Begin
								Insert into EquipmentMasterAlarmAuditTrail(EquipmentMasterId,AlarmLogDateTime,AlarmEvent,EmailStatus,SMSStatus,AlarmInfo) 
								values (@EquipmentMasterId,@LogDateTime,Upper(@AlarmEvent),'3','3',@AlarmInfo)
						End
						If @AlarmEvent = 'COMMUNICATION FAILURE' and @SMSSystemBranch = 0 and @EmailSystemBranch = 1
						Begin
								Insert into EquipmentMasterAlarmAuditTrail(EquipmentMasterId,AlarmLogDateTime,AlarmEvent,EmailStatus,SMSStatus,AlarmInfo) 
								values (@EquipmentMasterId,@LogDateTime,Upper(@AlarmEvent),'3','0',@AlarmInfo)
						End
						If @AlarmEvent = 'COMMUNICATION FAILURE'  and @SMSSystemBranch = 1	and @EmailSystemBranch = 0
			
								Insert into EquipmentMasterAlarmAuditTrail(EquipmentMasterId,AlarmLogDateTime,AlarmEvent,EmailStatus,SMSStatus,AlarmInfo) 
								values (@EquipmentMasterId,@LogDateTime,Upper(@AlarmEvent),'0','3',@AlarmInfo)
					
			End

			
			Set @ReturnCode=-9
			Set @ReturnMessage='Alarm Log Saved Successfully'
			
		End
	Else
		Begin
			Set @ReturnCode=-3
			Set @ReturnMessage='Alarm Log Saving Failed'
		End

	if @AlarmId <> 0
		Begin
			update EquipmentMasterAlarmAuditTrail
			set 
			--AlarmLogDateTime = @LogDateTime
			AlarmEvent=Upper(@AlarmEvent)
			where EquipmentMasterAlarmAuditTrailId=@AlarmId 
			
			IF @@rowcount > 0 
				
				BEGIN 
					SET @ReturnMessage = 'Alarm Log Updated Successfully  !!'
					SET @ReturnCode = -9
				END 
				
			ELSE
				BEGIN 
					SET @ReturnMessage = 'Alarm Log Updation Failed !! '
					SET @ReturnCode = -3
				END
		End
		
End
--Set nocount off;
