-----------------------------------------------------------
---------Added by Mehul on 13-July-2016
-----------------------------------------------------------

/****** Object:  StoredProcedure [dbo].[proc_IncidentInjuryDetails_SelectByIncidentID]    Script Date: 07/13/2016 18:47:54 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_IncidentInjuryDetails_SelectByIncidentID]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[proc_IncidentInjuryDetails_SelectByIncidentID]
GO

/****** Object:  StoredProcedure [dbo].[proc_Incident_GetByID]    Script Date: 07/13/2016 18:47:54 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_Incident_GetByID]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[proc_Incident_GetByID]
GO

/****** Object:  StoredProcedure [dbo].[proc_Incident_GetByIDForDocumentTemplate]    Script Date: 07/13/2016 18:47:54 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_Incident_GetByIDForDocumentTemplate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[proc_Incident_GetByIDForDocumentTemplate]
GO



/****** Object:  StoredProcedure [dbo].[proc_IncidentInjuryDetails_SelectByIncidentID]    Script Date: 07/13/2016 18:47:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--[proc_IncidentInjuryDetails_SelectByIncidentID] 6723,1
CREATE Procedure [dbo].[proc_IncidentInjuryDetails_SelectByIncidentID] --5442,1
(
 @IncidentID int,
 @LanguageId int
)
AS
BEGIN

OPEN SYMMETRIC KEY InjurdPerson_Key11
DECRYPTION BY CERTIFICATE InjuredPerson;

 Select
  IID.*,
  Case When QL.AllowEncryption=1 Then [dbo].[DecryptNVarchar](A.EncryptAnswer) Else A.Answer End as JobTitle,
  ISNULL(ICL.LupValue,InjuryClass.LupValue) InjuryClassificationName,
  ISNULL(PCL.LupValue,PC.LupValue) PersonClassName,
  Case U.Gender when 'M' Then 'Male' when 'F' Then 'Female' End as Gender,
  Case when isnull([dbo].[DecryptInt](IID.InjuredUserID),0) =0 Then
   [dbo].[DecryptNVarchar](IID.FirstName)
   Else
    LTRIM(RTRIM(CASE WHEN ISNULL(U.FirstName,'')='' THEN
   case when ISNULL(U.LastName,'')='' Then
    Case When ISNULL(U.Alias,'')='' then
     U.UserEmail
    else
     U.Alias
    End
   else
    U.LastName
   end
    Else
  ISNULL(U.FirstName,'') + ' ' + ISNULL(U.LastName,'')
    END))
   End PersonFirstName,
  IH.DaysLost,IH.DaysRestricted,
  I.ReportNum,
  ISNULL(U.UserEmail,'') as UserEmail,
  [dbo].[GetIndividualScoreByInjuryID](IID.IncidentInjuryID) as Score,
  U.PID AS UserID,
  ISNULL(CWL.LupValue,CW.LupValue) as WorkRelated,
  ISNULL(IFOL.LupValue,IFO.LupValue) as InsuranceForms,
  ISNULL(IITL.LupValue,IIT.LupValue) TypeName,
  [dbo].[DecryptNVarchar](IID.MedicalTreatment) as DecryptMedicalTreatment,
  UDS.FirstName + ' '+ISNULL(UDS.LastName,'') as DirectSupervisorName,
  ISNULL(IICL.LupValue,IIC.LupValue) InjuryIllnessCateName,
  ISNULL(IISCL.LupValue,IISC.LupValue) InjuryIllnessSubCateName,
  Case When S.ShiftCode Is Not Null Then S.ShiftName+' ('+S.ShiftCode+') '
  else S.ShiftName end as ShiftNameWithCode,
  ISNULL(OCL.LupValue,OC.LupValue) OshaSeverityClassName,
  Case When ISNULL(IID.IsDiffLocation,0)=0 Then 'No' Else 'Yes' End DiffLocation
 from
  tIncidentInjuryDetails IID
 Inner Join tIncident I ON IID.IncidentID = I.IncidentID
 LEFT Join tClientLup PC ON PC.ClientId = I.ClientID And PC.LupKey = 'PersonClass' And PC.LupId = IID.PersonClassID
 LEFT Join tClientLupLang PCL ON PCL.ClientId = PC.ClientId And PCL.LupKey = 'PersonClass' And PCL.LupId = PC.LupId AND PCL.LanguageId=@LanguageId
 Left Join tClientLup InjuryClass On InjuryClass.ClientId = I.ClientID And InjuryClass.LupKey = 'IncidentInjuryClass' And InjuryClass.LupId = IID.InjuryClassificationID
 LEFT JOIN tClientLupLang ICL ON ICL.ClientId = InjuryClass.ClientId AND ICL.LupKey = 'IncidentInjuryClass' AND ICL.LupId = InjuryClass.LupId AND ICL.LanguageId=@LanguageId
 Left Join tUsers U On U.ClientId = I.ClientID And U.UserID = [dbo].[DecryptInt](IID.InjuredUserID)
 Left Join tClientLup IFO on IFO.ClientId=I.ClientID AND IFO.LupKey='InsuranceCompany' AND IFO.LupId=IID.InsuranceCompanyId
 left join tClientLupLang IFOL On IFOL.ClientId=IFO.ClientId AND IFOL.LupKey='InsuranceCompany' AND IFOL.LupId=IFO.LupId AND IFOL.LanguageId=@LanguageId
 left join tQuestionLup QL on QL.ClientID=I.ClientID and QL.LupKey='OSHAQuestions' and QL.OshaReportID=21
 Left join tAnswerLup A on A.QuestionID=QL.QuestionID and A.Source='Incident' and A.SourceID=@IncidentID and A.SubSource='Injury' and A.SubSourceID=IID.IncidentInjuryID
	and A.ClientID=QL.ClientID
 Left Join tClientLup CW ON CW.LupId=IID.ISRelatedToWork AND CW.ClientId=I.ClientID AND CW.LupKey='WorkRelated'
 Left Join tClientLupLang CWL ON CWL.LupId=CW.LupId AND CWL.ClientId=CW.ClientId AND CWL.LupKey=CW.LupKey AND CWL.LanguageId=@LanguageId
 Left join tUsers UDS on UDS.ClientId=I.ClientID And UDS.UserID=IID.DirectSupervisor
 Left join tClientLup IIC on IIC.LupId=IID.InjIllCategoryID And IIC.ClientId=I.ClientID and IIC.LupKey='InjuryIllnessCategory'
 Left join tClientLupLang IICL on IICL.LupId=IIC.LupId and IICL.ClientId=IIC.ClientId and IICL.LupKey=IIC.LupKey And IICL.LanguageId=@LanguageId
 Left join tClientLup IISC on IISC.LupId=IID.InjIllSubCategoryID And IISC.ClientId=I.ClientID And IISC.LupKey='InjuryIllnessSubCategory'
 Left join tClientLupLang IISCL on IISCL.LupId=IISC.LupId And IISCL.ClientId=IISC.ClientId And IISCL.LupKey=IISC.LupKey And IISCL.LanguageId=@LanguageId
 Left Join tDLocationShift DS on DS.DLocationShiftID=IID.DLocationShiftID
 Left join tShift S on S.ShiftId=DS.ShiftID
 Left Join tClientLup OC on OC.ClientId = I.ClientID And OC.LupKey = 'OshaSeverityClass' And OC.LupId = IID.OshaClassificationID
 Left Join tClientLupLang OCL on OCL.ClientId = OC.ClientId And OCL.LupKey = 'OshaSeverityClass' And OCL.LupId = OC.LupId And OCL.LanguageId = @LanguageId
 Outer APPLY (
 --select SUM(DATEDIFF(day,StartDate,ISNULL(EndDate,GETUTCDATE()))) as DaysLost,
 --SUM(DATEDIFF(day,StartDate,ISNULL(EndDate,GETUTCDATE()))) as DaysRestricted
 --from tInjuryClassificationHistory
 --where IncidentInjuryID=IID.IncidentInjuryID and NewClassificationID=IID.InjuryClassificationID
 select SUM(case ISNULL(CL.DataColumn2,'') when 'LTC'
 then datediff(day,ICH.StartDate,isnull(ICH.EndDate,GETUTCDATE())) else 0 end) as DaysLost,
 SUM(case ISNULL(CL.DataColumn2,'') when 'RWC'
 then datediff(day,ICH.StartDate,isnull(ICH.EndDate,GETUTCDATE())) else 0 end) as DaysRestricted
 from tInjuryClassificationHistory ICH
 Inner join tClientLup CL On CL.ClientId = I.ClientID And ICH.NewClassificationID = CL.LupId And CL.LupKey = 'IncidentInjuryClass'
 where ICH.IncidentInjuryID=IID.IncidentInjuryID
 ) IH
 Inner Join tClientLup IIT ON IIT.ClientId = I.ClientID And IIT.LupKey = 'InjuryIllnessType' And IIT.LupId = IID.Type
 LEFT Join tClientLupLang IITL ON IITL.ClientId = IIT.ClientId And IITL.LupKey = 'InjuryIllnessType' And IITL.LupId = IIT.LupId AND IITL.LanguageId=@LanguageId
 where
  IID.IncidentID =@IncidentID
 Order by IID.InjuryNum asc
END
GO

/****** Object:  StoredProcedure [dbo].[proc_Incident_GetByID]    Script Date: 07/13/2016 18:47:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--[proc_Incident_GetByID] 6723,1
CREATE Procedure [dbo].[proc_Incident_GetByID]
(
 @incidentId int,
 @LanguageId int
)
AS
BEGIN

 OPEN SYMMETRIC KEY InjurdPerson_Key11
 DECRYPTION BY CERTIFICATE InjuredPerson;


 declare @OverDueDate datetime
 SET @OverDueDate = CONVERT(varchar(10),GETDATE(),101)

 select
  I.*,
  ISNULL(I.ISPenaltyLoss,0) PenltyLoss,
  ISNULL(I.WasFatigueOccured,0) FatigueOccured,
  Convert(varchar(15),I.IncidentDate,106) IncDate,
  Convert(varchar(15),I.IncidentDate,108) IncidentTime,
  Convert(varchar(15),I.LocalIncidentDate,108) IncidentLocalTime,
  UP.FirstName +' '+ ISNULL(UP.LastName,'') UpdatedByName,
  C.FirstName +' '+ ISNULL(C.LastName,'') CreatedByName,
  ISNULL(LILL.LupValue,LIL.LupValue) IncidentLevelName,
  ISNULL(LIAL.LupValue,LIA.LupValue) IncidentActivityName,
  ISNULL(LICCL.LupValue,LICC.LupValue) IncidentComplaintCategoryName,
  ISNULL(LICML.LupValue,LICM.LupValue) IncidentInvestigationMethodName,
  dbo.ufn_LocationHierarchy(I.DLocationID) Location,
  dbo.GetShiftNameByIncidentID(@incidentId) as Shift,
  SUP.FirstName +' '+ ISNULL(SUP.LastName,'') Supervisor,
  SUP.UserID SupervisorID,
  (select COUNT(CASE WHEN CompDate is NOT NULL THEN A.ActionID ELSE NULL END)
  From tActionItem A where A.Source = 'Incident' ANd A.SourceID = I.IncidentID )as ActionCompleted,
  (select COUNT(CASE WHEN CompDate is NULL AND TargetDate >= @OverDueDate  THEN A.ActionID ELSE NULL END)
  From tActionItem A where A.Source = 'Incident' ANd A.SourceID = I.IncidentID) as ActionPending,
  (Select COUNT(CASE WHEN CompDate is NULL AND TargetDate < @OverDueDate  THEN A.ActionID ELSE NULL END)
  From tActionItem A where A.Source = 'Incident' ANd A.SourceID = I.IncidentID) as ActionOverdue,
  ISNULL(LRCL.LupValue,LRC.LupValue) RamClassificationName,
  ISNULL(CLLIL.LupValue,CLIL.LupValue) IncidentInvestigationLevel,
   Case When Q.AllowEncryption=1 Then  ISNULL(cast([dbo].[DecryptFloat](A.EncryptValueNumber) as varchar),'')
   Else ISNULL(A.ValueNumber,'') End as PenaltyAmount,
  U.UnitName as CurrencyCode,
  T.WindowsTimeZoneName,
  CU.CustomerName
 from
  tIncident I
 LEFT JOIN tUsers C ON C.UserID = I.Created_UserID
 LEFT JOIN tUsers UP ON UP.UserID = I.LC_UserID
 Left Join tUsers SUP On SUP.UserID=I.WorkSupID
 Left Outer Join tClientLup LIL ON LIL.ClientId = I.ClientID And LIL.LupKey = 'IncidentLevel' And LIL.LupId = I.IncidentLevelID
 LEFT Outer JOIN tClientLupLang LILL ON LILL.ClientId = LIL.ClientId AND LILL.LupKey = 'IncidentLevel' AND LILL.LupId = LIL.LupId AND LILL.LanguageId=@LanguageId
 Left Outer join tClientLup LIA ON LIA.ClientId = I.ClientID And LIA.LupKey = 'IncidentActivity' And LIA.LupId = I.IncidentActivityID
 LEFT Outer JOIN tClientLupLang LIAL ON LIAL.ClientId = LIA.ClientId AND LIAL.LupKey = 'IncidentActivity' AND LIAL.LupId = LIA.LupId AND LIAL.LanguageId=@LanguageId
 Left Outer join tClientLup LICC ON LICC.ClientId = I.ClientID And LICC.LupKey = 'IncidentComplaintCategory' And LICC.LupId = I.ComplaintCategoryID
 Left Outer join tClientLupLang LICCL ON LICCL.ClientId = LICC.ClientId And LICCL.LupKey = 'IncidentComplaintCategory' And LICCL.LupId = LICC.LupId AND LICCL.LanguageId=@LanguageId
 Left Outer join tClientLup LICM ON LICM.ClientId = I.ClientID And LICM.LupKey = 'IncidentInvestigationMethod' And LICM.LupId = I.InvestigationMethodologyID
 Left Outer join tClientLupLang LICML ON LICML.ClientId = LICM.ClientId And LICML.LupKey = 'IncidentInvestigationMethod' And LICML.LupId = LICM.LupId AND LICML.LanguageId=@LanguageId
 Left Outer join tClientLup LRC ON LRC.ClientId = I.ClientID And LRC.LupKey = 'RAMClass' And LRC.LupId = I.RamClassId
 Left Outer join tClientLupLang LRCL ON LRCL.ClientId = LRC.ClientId And LRCL.LupKey = 'RAMClass' And LRCL.LupId = LRC.LupId AND LRCL.LanguageId=@LanguageId
 LEFT JOIN tClientSettings CS ON CS.ClientID = I.ClientID
 Left Outer join tClientLup CLIL ON CLIL.ClientId = I.ClientID And CLIL.LupKey = 'InvestigationLevel' And CLIL.LupId = I.InvestigationLevelID
 Left Outer join tClientLupLang CLLIL ON CLLIL.ClientId = CLIL.ClientId And CLLIL.LupKey = 'InvestigationLevel' And CLLIL.LupId = CLIL.LupId AND CLLIL.LanguageId=@LanguageId
 Left Outer join tQuestionLup Q on Q.ClientID=I.ClientID AND Q.LupKey='LossQuestion' AND Q.OshaReportID=1
 Left Outer join tAnswerLup A on A.QuestionID=Q.QuestionID AND A.Source='Incident' AND A.SourceID=I.IncidentID
 Left Outer join tUnits U on U.UnitID=Case When Q.AllowEncryption=1 Then [dbo].[DecryptInt](A.EncryptUnitID)Else A.UnitID End
 Left join tTimeZone T on T.TimeZoneID =I.TimeZoneID
 Left join tIncidentLinkOther ILO on ILO.Source='Customer' and ILO.IncidentId=I.IncidentID
 Left join tCustomers CU on CU.ClientID=I.ClientID and CU.CustomerId=ILO.SourceID
 where I.IncidentID=@incidentId
END
GO

/****** Object:  StoredProcedure [dbo].[proc_Incident_GetByIDForDocumentTemplate]    Script Date: 07/13/2016 18:47:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE Procedure [dbo].[proc_Incident_GetByIDForDocumentTemplate]
(
 @incidentId int,
 @LanguageId int
)
AS
BEGIN

	OPEN SYMMETRIC KEY InjurdPerson_Key11
	DECRYPTION BY CERTIFICATE InjuredPerson;


	declare @OverDueDate datetime
	SET @OverDueDate = CONVERT(varchar(10),GETDATE(),101)

	 select
	 I.IncidentID
	,I.ClientID
	,I.ReportNum
	,I.InvestigationMethodologyID
	,I.IncidentCategoryID
	,I.WhatHappened
	,I.How
	,I.Witness
	,I.WhatOccurBefore
	,I.WhereEvent
	,I.Object
	,I.OpsResumed
	,I.ProblemStatement
	,I.Created_UserID
	,I.Created_TS
	,I.LC_UserID
	,I.LC_TS
	,I.Title
	,I.Description
	,I.DLocationID
	,I.IncidentDate
	,I.IncidentLevelID
	,I.IncidentActivityID
	,I.ComplaintCategoryID
	,I.Status
	,I.ActionPrevention
	,I.WasFatigueOccured
	,I.InvestigationLevelID
	,I.WorkSupID
	,I.ISPenaltyLoss
	,I.WHYHappened
	,I.CorrectiveActions
	,I.SubmittedOn
	,I.LastModifiedOn
	,I.ScoringperformedOn
	,I.SubmittedForApprovalOn
	,I.ChangesRequestedOn
	,I.ApprovedOn
	,I.ReOpenedOn
	,I.ReviewNotRequired
	,I.SubmittedForReviewOn
	,I.ReviewedOn
	,I.IsInjuryOccured
	,I.IncidentInformedDate
	,I.MarkConfidential
	,I.PreliminaryInvCompleted
	,I.PreInvCompletedDate
	,I.IsAutoConfidentialChecked
	,I.RamClassId
	,I.MigrationID
	,I.ReferenceNumber
	,I.UrlField
	,I.TimeZoneID
	,I.StateOfMindLeft
	,I.StateOfMindRight
	,I.LocalIncidentDate
	,Case When ISNULL(I.ISPenaltyLoss,0)=0 then 'No' Else 'Yes' End MonetaryLoss,
	Case When ISNULL(I.WasFatigueOccured,0)=0 then 'No' Else 'Yes' End FatigueOccured,
	Convert(varchar(15),I.LocalIncidentDate,108) IncidentLocalTime,
	UP.FirstName +' '+ ISNULL(UP.LastName,'') UpdatedByName,
	C.FirstName +' '+ ISNULL(C.LastName,'') CreatedByName,
	ISNULL(LILL.LupValue,LIL.LupValue) IncidentLevelName,
	ISNULL(LIAL.LupValue,LIA.LupValue) IncidentActivityName,
	ISNULL(LICCL.LupValue,LICC.LupValue) IncidentComplaintCategoryName,
	ISNULL(LICML.LupValue,LICM.LupValue) IncidentInvestigationMethodName,
	dbo.ufn_LocationHierarchy(I.DLocationID) Location,
	dbo.GetShiftNameByIncidentID(@incidentId) as Shift,
	SUP.FirstName +' '+ ISNULL(SUP.LastName,'') Supervisor,
	SUP.UserID SupervisorID,
	(select COUNT(CASE WHEN CompDate is NOT NULL THEN A.ActionID ELSE NULL END)
	From tActionItem A where A.Source = 'Incident' ANd A.SourceID = I.IncidentID )as ActionCompleted,
	(select COUNT(CASE WHEN CompDate is NULL AND TargetDate >= @OverDueDate  THEN A.ActionID ELSE NULL END)
	From tActionItem A where A.Source = 'Incident' ANd A.SourceID = I.IncidentID) as ActionPending,
	(Select COUNT(CASE WHEN CompDate is NULL AND TargetDate < @OverDueDate  THEN A.ActionID ELSE NULL END)
	From tActionItem A where A.Source = 'Incident' ANd A.SourceID = I.IncidentID) as ActionOverdue,
	ISNULL(LRCL.LupValue,LRC.LupValue) RamClassificationName,
	ISNULL(CLLIL.LupValue,CLIL.LupValue) IncidentInvestigationLevel,
	Case When Q.AllowEncryption=1 Then  ISNULL(cast([dbo].[DecryptFloat](A.EncryptValueNumber) as varchar),'')
	Else ISNULL(A.ValueNumber,'') End as PenaltyAmount,
	U.UnitName as CurrencyCode,
	T.WindowsTimeZoneName,
	CU.CustomerName,
	(Select Case when COUNT(*) > 0 Then 'Yes' Else 'No' ENd
	from tIncidentInjuryDetails where IncidentID =I.IncidentID And [Type] =3) As ISNearMiss,
	(Select ISNULL(QAL.Answer,QA.Answer) from tQuestionLup Q
	Inner Join tAnswerLup A on A.ClientID=Q.ClientID AND A.QuestionID=Q.QuestionID
	AND A.Source='Incident' AND A.SourceID=@incidentId
	Inner Join tQuestionLupAnswers QA on QA.QuestionLupAnswerID=
		Case When Q.AllowEncryption=1 Then [dbo].[DecryptInt](A.EncryptAnswerId) Else A.AnswerId End
	Left Join tQuestionLupAnswersLang QAL on QAL.QuestionLupAnswerID=QA.QuestionLupAnswerID AND QAL.LanguageId=@LanguageId
	Where Q.QuestionID=13101) as SWOEnforced,
	Case When ISNULL(I.ISPenaltyLoss,0)=1 Then 'CAR Number: '+ ISNULL(
	(Select Case When Q.AllowEncryption=1 Then [dbo].[DecryptNVarchar](A.EncryptAnswer) Else A.Answer End
	 from tQuestionLup Q
	Inner Join tAnswerLup A on A.ClientID=Q.ClientID AND A.QuestionID=Q.QuestionID
	AND A.Source='Incident' AND A.SourceID=@incidentId
	Where Q.QuestionID=13145),'') Else '' End as CARCreated
	from
	tIncident I
	LEFT JOIN tUsers C ON C.UserID = I.Created_UserID
	LEFT JOIN tUsers UP ON UP.UserID = I.LC_UserID
	Left Join tUsers SUP On SUP.UserID=I.WorkSupID
	Left Outer Join tClientLup LIL ON LIL.ClientId = I.ClientID And LIL.LupKey = 'IncidentLevel' And LIL.LupId = I.IncidentLevelID
	LEFT Outer JOIN tClientLupLang LILL ON LILL.ClientId = LIL.ClientId AND LILL.LupKey = 'IncidentLevel' AND LILL.LupId = LIL.LupId AND LILL.LanguageId=@LanguageId
	Left Outer join tClientLup LIA ON LIA.ClientId = I.ClientID And LIA.LupKey = 'IncidentActivity' And LIA.LupId = I.IncidentActivityID
	LEFT Outer JOIN tClientLupLang LIAL ON LIAL.ClientId = LIA.ClientId AND LIAL.LupKey = 'IncidentActivity' AND LIAL.LupId = LIA.LupId AND LIAL.LanguageId=@LanguageId
	Left Outer join tClientLup LICC ON LICC.ClientId = I.ClientID And LICC.LupKey = 'IncidentComplaintCategory' And LICC.LupId = I.ComplaintCategoryID
	Left Outer join tClientLupLang LICCL ON LICCL.ClientId = LICC.ClientId And LICCL.LupKey = 'IncidentComplaintCategory' And LICCL.LupId = LICC.LupId AND LICCL.LanguageId=@LanguageId
	Left Outer join tClientLup LICM ON LICM.ClientId = I.ClientID And LICM.LupKey = 'IncidentInvestigationMethod' And LICM.LupId = I.InvestigationMethodologyID
	Left Outer join tClientLupLang LICML ON LICML.ClientId = LICM.ClientId And LICML.LupKey = 'IncidentInvestigationMethod' And LICML.LupId = LICM.LupId AND LICML.LanguageId=@LanguageId
	Left Outer join tClientLup LRC ON LRC.ClientId = I.ClientID And LRC.LupKey = 'RAMClass' And LRC.LupId = I.RamClassId
	Left Outer join tClientLupLang LRCL ON LRCL.ClientId = LRC.ClientId And LRCL.LupKey = 'RAMClass' And LRCL.LupId = LRC.LupId AND LRCL.LanguageId=@LanguageId
	LEFT JOIN tClientSettings CS ON CS.ClientID = I.ClientID
	Left Outer join tClientLup CLIL ON CLIL.ClientId = I.ClientID And CLIL.LupKey = 'InvestigationLevel' And CLIL.LupId = I.InvestigationLevelID
	Left Outer join tClientLupLang CLLIL ON CLLIL.ClientId = CLIL.ClientId And CLLIL.LupKey = 'InvestigationLevel' And CLLIL.LupId = CLIL.LupId AND CLLIL.LanguageId=@LanguageId
	Left Outer join tQuestionLup Q on Q.ClientID=I.ClientID AND Q.LupKey='LossQuestion' AND Q.OshaReportID=1
	Left Outer join tAnswerLup A on A.QuestionID=Q.QuestionID AND A.Source='Incident' AND A.SourceID=I.IncidentID
	Left Outer join tUnits U on U.UnitID=Case When Q.AllowEncryption=1 Then [dbo].[DecryptInt](A.EncryptUnitID)Else A.UnitID End
	Left join tTimeZone T on T.TimeZoneID =I.TimeZoneID
	Left join tIncidentLinkOther ILO on ILO.Source='Customer' and ILO.IncidentId=I.IncidentID
	Left join tCustomers CU on CU.ClientID=I.ClientID and CU.CustomerId=ILO.SourceID
	where I.IncidentID=@incidentId

END
GO

-----------------------------------------------------------
---------Added by Mehul on 15-July-2016
-----------------------------------------------------------


Update tApprovalsNew SET ApprovalStatus='Sent For Approval'
from tApprovalsNew A
Inner Join tRiskHazard H on H.HazardID=A.SourceID AND H.Status='Approvals In Progress'
Where A.SourceType='Risk' AND A.ApprovalStatus='Pending'
GO

---------------------

Update tApprovalsNew SET ApprovalStatus='Sent For Approval'
from tApprovalsNew A
Inner Join t_Audit_Audit AU on AU.AuditId=A.SourceID AND AU.Status='Approvals In Progress'
Where A.SourceType='Audit' AND A.ApprovalStatus='Pending'
GO

---------------------

Update tApprovalsNew SET ApprovalStatus='Sent For Approval'
from tApprovalsNew A
Inner Join tLegalComplianceGeneralInfo L on L.LegalComplianceID=A.SourceID AND L.Status='Approvals In Progress'
Where A.SourceType='Legal' AND A.ApprovalStatus='Pending'
GO

-------------------------------------



/****** Object:  UserDefinedFunction [dbo].[ufn_Users_GetEnumUsersCommon]    Script Date: 07/15/2016 17:25:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufn_Users_GetEnumUsersCommon]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ufn_Users_GetEnumUsersCommon]
GO



/****** Object:  UserDefinedFunction [dbo].[ufn_Users_GetEnumUsersCommon]    Script Date: 07/15/2016 17:25:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--Select * from [dbo].[ufn_Users_GetEnumUsersCommon](8,6,0,'Incident',6724,-6,10,1)
CREATE Function [dbo].[ufn_Users_GetEnumUsersCommon]
(
	@EmailTemplateID int,
	@EnumID int,
	@LevelNo int,
	@Source varchar(50),
	@SourceID int,
	@SourceId2 int,
	@UserID int,
	@ClientID int
)
RETURNS @oUsers TABLE(UserID int)
As
BEGIN

	IF(@EnumID=1)
	Begin
		Insert Into @oUsers(UserID)
		Select UserID from [dbo].[ufn_Users_GetEnumUsersofIncident](@EmailTemplateID,@EnumID,@LevelNo,@Source,@SourceId,@SourceId2)
	End
	Else If (@EnumID=2)
	Begin
		Insert Into @oUsers(UserID)
		select
		U.UserID
		from tEditors E
		INNER JOIN tUsers U on E.EditorID=U.UserID AND U.IsActive=1 AND U.IsFileOnlyUser=0
		where E.SourceID=@SourceId and E.Source=@Source
	End
	Else If(@EnumID=3)
	Begin

		IF(@Source='Incident')
		Begin

			Insert Into @oUsers(UserID)
			Select tUsers.UserID from tIncident I
			Inner Join tUsers On I.Created_UserID= tUsers.UserID AND tUsers.IsActive=1 AND tUsers.IsFileOnlyUser=0
			Where I.IncidentID=@SourceId

		End
		Else IF(@Source='Audit')
		Begin
			Insert Into @oUsers(UserID)
			Select tUsers.UserID
			from t_Audit_Audit A
			Inner Join tUsers On A.CreatedBy= tUsers.UserID AND tUsers.IsActive=1 AND tUsers.IsFileOnlyUser=0
			Where A.AuditId=@SourceId
		End
		Else IF(@Source='Risk')
		Begin
			Insert Into @oUsers(UserID)
			Select tUsers.UserID
			from tRiskHazard RH
			Inner Join tUsers On RH.CreatedBy= tUsers.UserID AND tUsers.IsActive=1 AND tUsers.IsFileOnlyUser=0
			Where RH.HazardID=@SourceId
		End
		Else IF(@Source='DMS')
		Begin
			Insert Into @oUsers(UserID)
			Select tUsers.UserID
			from tDoc D
			Inner Join tUsers On D.CreatedByID= tUsers.UserID AND tUsers.IsActive=1 AND tUsers.IsFileOnlyUser=0
			Where D.DocID=@SourceId
		End
		Else IF(@Source='OTour')
		Begin
			Insert Into @oUsers(UserID)
			Select tUsers.UserID
			from tObservationTour OT
			Inner Join tUsers On OT.CreatedBy= tUsers.UserID AND tUsers.IsActive=1 AND tUsers.IsFileOnlyUser=0
			Where OT.SoTourId=@SourceId
		End
		Else IF(@Source='Legal')
		Begin
			Insert into @oUsers(UserID)
			select tUsers.UserID
			from tLegalComplianceGeneralInfo LCI
			Inner Join tUsers On LCI.Created_UserID= tUsers.UserID AND tUsers.IsActive=1 AND tUsers.IsFileOnlyUser=0
			where LCI.LegalComplianceID=@SourceId
		End

	End
	Else if(@EnumID=4)
	Begin

		Insert Into @oUsers(UserID)
		Select UserID from [dbo].[ufn_Users_GetEnumUsersofIncident](@EmailTemplateID,@EnumID,@LevelNo,@Source,@SourceId,@SourceId2)
	End
	Else If(@EnumID=5)
	Begin
		Insert Into @oUsers(UserID)
		Select UserID from [dbo].[ufn_Users_GetEnumUsersofIncident](@EmailTemplateID,@EnumID,@LevelNo,@Source,@SourceId,@SourceId2)
	End
	Else If(@EnumID=6)
	Begin

		Declare @ApprovalID int
		Declare @LevelId int

		SET @ApprovalID=@SourceId2
		SET @LevelId=0

		IF(@Source='DMS')
		Begin

			Insert Into @oUsers(UserID)
			 SELECT
			  U.UserID
			 FROM tDocApprovals DA
			 INNER JOIN tUsers U ON DA.UserID=U.UserID AND U.IsActive=1 AND U.IsFileOnlyUser=0
			 WHERE DA.DocID=@SourceId and DA.Archive='N'
		End
		Else IF(@Source='Incident')
		Begin

			 IF (@ApprovalID < 0 AND @ApprovalID <> -5 AND @ApprovalID <> -6)
			 Begin

				 SET @LevelId = (Select Abs(@ApprovalID));
				 SET @ApprovalID = 0;
			 End

			 IF(@ApprovalID=-5)
			 Begin

				SET @ApprovalID = 0;
				IF EXISTS(Select ClientSettingID from tClientSettings Where ClientID=@ClientId AND IsSequentialApproval=1)
				Begin
					SET @LevelId =1
				End

			 End

			 If(@ApprovalID=-6)
			 Begin
				 SET @ApprovalID = 0
				 SET @LevelId = (Select ISNULL(Min(LevelNo),0) from tApprovalsNew Where SourceType=@Source AND SourceID=@SourceID AND LevelNo<>0)
			 End

		    Insert Into @oUsers(UserID)
			SELECT
			U.UserID
			from
			tApprovalsNew an
			Inner Join tUsers u On u.UserID = an.ApproverID AND u.IsActive=1 AND u.IsFileOnlyUser=0
			where
			an.SourceType = @Source And an.SourceID = @SourceID
			AND ((@ApprovalID=0 AND ((ISNULL(LevelNo,0)=@LevelId OR ISNULL(LevelNo,0)=0) OR @LevelId=0)) OR ApprovalID=@ApprovalID)
			AND ((ApprovalStatus='Pending' OR @LevelId=0 OR @LevelId=1) AND ApprovalStatus<>'Delegated')

		End
		Else
		Begin
			Insert Into @oUsers(UserID)
			SELECT
			U.UserID
			from
			tApprovalsNew an
			Inner Join tUsers u On u.UserID = an.ApproverID AND u.IsActive=1 AND u.IsFileOnlyUser=0
			where
			an.SourceType = @Source And an.SourceID = @SourceID
			AND ((@ApprovalID=0 OR ApprovalID=@ApprovalID) AND ApprovalStatus<>'Delegated')

		End

	End
	Else If(@EnumID=7)
	Begin
		Insert Into @oUsers(UserID)
		Select U.UserID
		from tClientSettings C
		Inner Join tUsers U on U.ClientId=@ClientId AND U.UserID=C.ComplianceSupervisorId AND U.IsActive=1 AND U.IsFileOnlyUser=0
		Where C.ClientID=@ClientId
	End
	Else If(@EnumID=9)
	Begin
		Insert Into @oUsers(UserID)
		Select UserID from [dbo].[ufn_Users_GetEnumUsersofAudit](@EnumID,@Source,@SourceId)
	End
	Else If(@EnumID=10)
	Begin
		Insert Into @oUsers(UserID)
		Select UserID from [dbo].[ufn_Users_GetEnumUsersofAudit](@EnumID,@Source,@SourceId)
	End
	Else If(@EnumID=11)
	Begin
		Insert Into @oUsers(UserID)
		Select UserID from [dbo].[ufn_Users_GetEnumUsersofAudit](@EnumID,@Source,@SourceId)
	End
	Else If(@EnumID=13)
	Begin
		Insert Into @oUsers(UserID)
		Select UserID from [dbo].[ufn_Users_GetEnumUsersofIncident](@EmailTemplateID,@EnumID,@LevelNo,@Source,@SourceId,@SourceId2)
	End
	Else If(@EnumID=14)
	Begin
		Insert Into @oUsers(UserID)
		Select UserID from [dbo].[ufn_Users_GetEnumUsersofIncident](@EmailTemplateID,@EnumID,@LevelNo,@Source,@SourceId,@SourceId2)
	End
	Else If(@EnumID=15)
	Begin
		Insert Into @oUsers(UserID)
		Select UserID from [dbo].[ufn_Users_GetEnumUsersofObservation](@EnumID,@SourceId)
	End
	Else If(@EnumID=16)
	Begin
		Insert Into @oUsers(UserID)
		Select UserID from [dbo].[ufn_Users_GetEnumUsersofIncident](@EmailTemplateID,@EnumID,@LevelNo,@Source,@SourceId,@SourceId2)
	End
	Else If(@EnumID=17)
	Begin
		Insert Into @oUsers(UserID)
		Select UserID from [dbo].[ufn_Users_GetEnumUsersofIncident](@EmailTemplateID,@EnumID,@LevelNo,@Source,@SourceId,@SourceId2)
	End
	Else If(@EnumID=18)
	Begin
		Insert Into @oUsers(UserID)
		Select UserID from [dbo].[ufn_Users_GetEnumUsersofIncident](@EmailTemplateID,@EnumID,@LevelNo,@Source,@SourceId,@SourceId2)
	End
	Else If(@EnumID=19)
	Begin
		Insert Into @oUsers(UserID)
		Select UserID from [dbo].[ufn_Users_GetEnumUsersofIncident](@EmailTemplateID,@EnumID,@LevelNo,@Source,@SourceId,@SourceId2)
	End
	Else If(@EnumID=20)
	Begin
		Insert Into @oUsers(UserID)
		Select UserID from [dbo].[ufn_Users_GetEnumUsersofDMS](@EnumID,@SourceId)
	End
	Else If(@EnumID=21)
	Begin
		Insert Into @oUsers(UserID)
		Select UserID from [dbo].[ufn_Users_GetEnumUsersofDMS](@EnumID,@SourceId)
	End
	Else If(@EnumID=22)
	Begin
		Insert Into @oUsers(UserID)
		Select UserID from [dbo].[ufn_Users_GetEnumUsersofDMS](@EnumID,@SourceId)
	End
	Else If(@EnumID=23)
	Begin
		Insert Into @oUsers(UserID)
		Select UserID from [dbo].[ufn_Users_GetEnumUsersofDMS](@EnumID,@SourceId)
	End
	Else If(@EnumID=24)
	Begin
		Insert Into @oUsers(UserID)
		Select UserID from tUsers U Where U.UserID=@UserID AND U.IsActive=1 AND U.IsFileOnlyUser=0
	End
	Else If(@EnumID=25)
	Begin
		Insert Into @oUsers(UserID)
		Select UserID from [dbo].[ufn_Users_GetEnumUsersofRisk](@EnumID,@SourceId)
	End

RETURN
END
GO


----------------------------------



/****** Object:  StoredProcedure [dbo].[proc_ApprovalsNew_SelectAllApproversBySourceID]    Script Date: 07/15/2016 17:28:14 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_ApprovalsNew_SelectAllApproversBySourceID]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[proc_ApprovalsNew_SelectAllApproversBySourceID]
GO

/****** Object:  StoredProcedure [dbo].[proc_ApprovalsNew_ResetAllApprovalStatus]    Script Date: 07/15/2016 17:28:14 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_ApprovalsNew_ResetAllApprovalStatus]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[proc_ApprovalsNew_ResetAllApprovalStatus]
GO

/****** Object:  StoredProcedure [dbo].[proc_ApprovalsNew_DeleteByID]    Script Date: 07/15/2016 17:28:14 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_ApprovalsNew_DeleteByID]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[proc_ApprovalsNew_DeleteByID]
GO



/****** Object:  StoredProcedure [dbo].[proc_ApprovalsNew_SelectAllApproversBySourceID]    Script Date: 07/15/2016 17:28:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--[proc_ApprovalsNew_SelectAllApproversBySourceID] 'Audit',1136,16296
CREATE PROCEDURE [dbo].[proc_ApprovalsNew_SelectAllApproversBySourceID]
(
 @SourceType varchar(50),
 @SourceID int,
 @CurrentUserID int
)
AS SET NOCOUNT ON

Declare @SourceStatus as varchar(50)

IF(@SourceType = 'Audit')
 Select @SourceStatus = [Status] From t_Audit_Audit where AuditId = @SourceID
ELSE IF(@SourceType = 'Incident')
 Select @SourceStatus = [Status] From tIncident where IncidentID = @SourceID
ELSE IF(@SourceType = 'Risk')
 Select @SourceStatus = [Status] From tRiskHazard where HazardID = @SourceID
ELSE IF(@SourceType = 'Legal')
 Select @SourceStatus = [Status] From tLegalComplianceGeneralInfo where LegalComplianceID = @SourceID

;with cte1 as
(
SELECT
 an.*,
 u.UserEmail,
 CASE WHEN u.NotificationEmail IS NULL OR u.NotificationEmail='' THEN u.UserEmail ELSE u.NotificationEmail END NotificationEmail,
 u.IsActive,
 u.FirstName + ' ' + u.LastName UserName,
 Cast(
 Case --when @CurrentUserID = an.ApproverID And SourceType='Legal' And an.ApprovalStatus='Pending' And @SourceStatus = 'Changes Required' Then 1
   when @CurrentUserID = an.ApproverID And an.ApprovalStatus = 'Sent For Approval' And @SourceStatus = 'Approvals In Progress' Then 1
   Else 0 End as bit
  ) As ShowSignOff,U.UserID
from
 tApprovalsNew an
Inner Join tUsers u On u.UserID = an.ApproverID
where
 an.SourceType = @SourceType And an.SourceID = @SourceID
 )

 select cte1.* from cte1
 left Join tIncident I on I.IncidentID=@SourceId And @SourceType='Incident'
 Inner Join tUsers On cte1.UserID = tUsers.UserID
 LEFT Join tUserModule M On M.UserId=tUsers.UserID And M.ModuleId=2
 LEFT join tRole R On R.RoleID=M.RoleID
where isnull(I.MarkConfidential,0) = 0 or R.I_AllowIncidentCFReporting = 1
GO

/****** Object:  StoredProcedure [dbo].[proc_ApprovalsNew_ResetAllApprovalStatus]    Script Date: 07/15/2016 17:28:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCedure [dbo].[proc_ApprovalsNew_ResetAllApprovalStatus]
(
 @SourceType VARCHAR(50),
    @SourceID INT,
    @ApprovalStatus VARCHAR(50),
    @LC_UserID INT,
    @isReopen BIT
)
AS
BEGIN

 IF (@isReopen=1)
 BEGIN

  INSERT INTO tApprovalsNewHistory (SourceType,SourceID,ApproverID,ApprovalStatus,Comments,
        LastNotifiedDate,SignedDate,Created_UserID,Created_TS,LC_UserID,LC_TS,GroupId,GroupType)
  SELECT SourceType,SourceID,ApproverID,ApprovalStatus,Comments,LastNotifiedDate,SignedDate,
  Created_UserID,Created_TS,LC_UserID,LC_TS,GroupId,GroupType
  FROM tApprovalsNew WHERE SourceType = @SourceType And SourceID =@SourceID
  And ApprovalStatus <> 'Delegated' And ApprovalStatus <> 'Pending'

 END

 UPDATE tApprovalsNew
 SET ApprovalStatus = @ApprovalStatus,LC_UserID =@LC_UserID ,LC_TS = GETUTCDATE(),SignedDate = null,Comments=''
 WHERE   SourceType = @SourceType And SourceID =@SourceID  And ApprovalStatus <> 'Delegated'

END
GO

/****** Object:  StoredProcedure [dbo].[proc_ApprovalsNew_DeleteByID]    Script Date: 07/15/2016 17:28:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCedure [dbo].[proc_ApprovalsNew_DeleteByID]
(
	@ApprovalID int
)
AS

declare @SourceType varchar(50), @SourceID int, @Status	nvarchar(50)
SELECT @SourceType = SourceType, @SourceID = SourceID FROM tApprovalsNew where ApprovalID =@ApprovalID

DELETE from tApprovalsNew where ApprovalID =@ApprovalID

IF @SourceType = 'Incident'
BEGIN
	SELECT @Status=[Status] FROM tIncident WHERE IncidentID = @SourceID
	IF @Status = 'Approvals In Progress'
	BEGIN
		IF Not EXISTS(SELECT * FROM tApprovalsNew WHERE SourceType = @SourceType AND SourceID = @SourceID AND ApprovalStatus IN('Pending','Sent For Approval'))
			AND EXISTS(SELECT * FROM tApprovalsNew WHERE SourceType = @SourceType AND SourceID = @SourceID AND ApprovalStatus ='Approved')
		BEGIN
			UPDATE tIncident SET [Status] = 'Approved',ApprovedOn=GETUTCDATE() WHERE IncidentID = @SourceID
			select 1
		END
		Else
		Begin
			select 0
		End
	END
	Else
	Begin
		select 0
	End
END
Else
BEGIN
	select 0
END


GO


--------------------------------------------------------
--Added by Jatin On 15-Jul-2016 Task 368: Fixed performance issue with new env report
--------------------------------------------------------


/****** Object:  UserDefinedFunction [dbo].[ufn_IndicatorChildren]    Script Date: 07/14/2016 19:27:50 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufn_IndicatorChildren]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ufn_IndicatorChildren]
GO


/****** Object:  UserDefinedFunction [dbo].[ufn_IndicatorChildren]    Script Date: 07/14/2016 19:27:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[ufn_IndicatorChildren]
(
 @IndicatorGroupID int
)
RETURNS @otTemp TABLE(IndicatorGroupID int,IndicatorID int, ParentIndicatorGroupID int)
AS
BEGIN

Declare @ClientID int
Select @ClientID = ClientID From tIndicatorGroups where IndicatorGroupID =@IndicatorGroupID

;WITH ChildIndicators (ParentIndicatorGroupID, IndicatorID,ParentGroupID)
AS
(
-- Anchor member definition
    SELECT e.IndicatorGroupID, e.ChildIndicatorID,e.ParentGroupID
    FROM tIndicatorGroups AS e
    WHERE ClientID = @ClientID And ParentGroupID = @IndicatorGroupID
    UNION ALL
-- Recursive member definition
    SELECT e.IndicatorGroupID, e.ChildIndicatorID,e.ParentGroupID
    FROM tIndicatorGroups AS e
    INNER JOIN ChildIndicators AS d
        ON e.ClientID = @ClientID And e.ParentGroupID = d.ParentIndicatorGroupID
)

INSERT INTO @otTemp(IndicatorGroupID,IndicatorID,ParentIndicatorGroupID)
SELECT ParentIndicatorGroupID, IndicatorID,ParentGroupID FROM ChildIndicators
UNION ALL
SELECT IndicatorGroupID, ChildIndicatorID,ParentGroupID  From tIndicatorGroups
where IndicatorGroupID  = @IndicatorGroupID


RETURN
END


GO


/****** Object:  Index [NCL_IndicatorGroups]    Script Date: 07/14/2016 19:24:39 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tIndicatorGroups]') AND name = N'NCL_IndicatorGroups')
DROP INDEX [NCL_IndicatorGroups] ON [dbo].[tIndicatorGroups] WITH ( ONLINE = OFF )
GO


/****** Object:  Index [NCL_IndicatorGroups]    Script Date: 07/14/2016 19:24:51 ******/
CREATE NONCLUSTERED INDEX [NCL_IndicatorGroups] ON [dbo].[tIndicatorGroups]
(