﻿-------------------------Ali 2023-09-17---------------------------

ALTER proc [dbo].[WF_GetOverTimeRequest] 
(
	@UserId int =null ,
	@MainType tinyint =null ,
	@FromDate datetime =null ,
	@ToDate datetime =null,
	@EmployeeId int =null,
	@SectionCode nvarchar(50),
	@DepartmentApproval tinyint=null ,
	@DeptAccepted tinyint=null ,
	@DeptRefused tinyint=null ,
	@DeptPending tinyint=null ,
	  @OverTimeFromDate datetime =null ,
	@OverTimeToDate datetime =null
)
AS
Declare @ReportIsSymphony bit 
select @ReportIsSymphony=CONVERT(bit, ConfigValue) from ConfigParameter where ConfigKey='WFReportOverTimeIsSymphony'
  
  if (@ReportIsSymphony=0)        
  begin
SELECT  WF_OverTimeRequest.OverTimeRequestId     ,[EmployeeId]
      ,[DepartmentApproval]
      ,[RequestDate] 
      ,[HRApproval]
      ,[RecordAddDate]
      ,[RequestReason]
      ,[MainType]
      ,round((MinutesCount/60),2)  as   MinutesCount
      ,WF_OverTimeRequest.[Closed]
      ,[MainType]
      ,dbo.MinutesToDuration(MinutesCount) as HoursMinutes
	  ,Employees.Name  as EmployeeNameEN
	  ,Employees.Arabic_Name  as EmployeeNameAR
      ,WF_RequestStatus.RequestStatusName  as DepartmentApprovalNameAR
	  ,WF_RequestStatus.RequestStatusNameEn  as DepartmentApprovalNameEN
	  ,Sections.Name  as SectionEnName
	  ,Sections.Arabic_Name  as SectionArName
	  ,WF_OverTimeRequestSignature.UserId
	  ,SecurityUser.EmployeeCode
	  ,(case when [DepartmentApproval]=0 then  empDeptPending.Arabic_Name else '' end) as UserPendingNameAr
	  ,(case when [DepartmentApproval]=0 then empDeptPending.Name else '' end) as UserPendingNameEN,
	  TimeFrom,	TimeTo,
	  	WF_OvertimeType.Name as OvertimeTypeNameEN ,
	  	WF_OvertimeType.Arabic_Name as OvertimeTypeNameAr ,
    	dbo.MinutesToDuration(MinutesCountAfterFactor) as   MinutesCountAfterFactor
		 ,round(Cost,2) as Cost
	  ,empDeptRefused.Arabic_Name as UserRefusedNameAr
	  ,empDeptRefused.Name as UserRefusedNameEN
		,round((MinutesCountAfterFactor/60),2)  as   TotalMinutesAfterFactor
		
  FROM WF_OverTimeRequest 
  inner join Employees on WF_OverTimeRequest.EmployeeId=Employees.EmployeeCode
  LEFT OUTER JOIN  WF_OvertimeType on WF_OverTimeRequest.OvertimeTypeCode=WF_OvertimeType.OvertimeTypeCode
  inner join Sections on Sections.SectionCode=Employees.SectionCode
  inner join WF_RequestStatus on WF_OverTimeRequest.DepartmentApproval=WF_RequestStatus.RequestStatusId
  LEFT OUTER JOIN WF_OverTimeRequestSignature on WF_OverTimeRequest.OverTimeRequestId=WF_OverTimeRequestSignature.OverTimeRequestId and WF_OverTimeRequestSignature.Turn=0 and WF_OverTimeRequestSignature.Approved=0
  LEFT OUTER JOIN SecurityUser on SecurityUser.SecurityUserCode=WF_OverTimeRequestSignature.UserId
  LEFT OUTER JOIN Employees as empDeptPending on empDeptPending.EmployeeCode =SecurityUser.EmployeeCode

  LEFT OUTER JOIN WF_OverTimeRequestSignature as SignatureRefuse on WF_OverTimeRequest.OverTimeRequestId=SignatureRefuse.OverTimeRequestId and SignatureRefuse.Turn=1 and SignatureRefuse.Approved=2
  LEFT OUTER JOIN SecurityUser  as UserRefused on UserRefused.SecurityUserCode=SignatureRefuse.UserId
  LEFT OUTER JOIN Employees as empDeptRefused on empDeptRefused.EmployeeCode =UserRefused.EmployeeCode
 
 where 

      EmployeeId = COALESCE(@EmployeeId, EmployeeId)
       And  MainType = COALESCE(@MainType, MainType)
	 AND (@FromDate IS NULL OR RecordAddDate >= @FromDate)
      AND (@ToDate IS NULL OR RecordAddDate <= @ToDate )
	   AND (@OverTimeFromDate IS NULL OR RequestDate >= @OverTimeFromDate)
      AND (@OverTimeToDate IS NULL OR RequestDate <= @OverTimeToDate )
     and   Employees.SectionCode = COALESCE(@SectionCode, Employees.SectionCode)
	 
       And( DepartmentApproval=  @DeptPending
	   or DepartmentApproval= @DeptAccepted
       or  DepartmentApproval= @DeptRefused)

end
else
--OverTime Symphony
begin
SELECT  WF_OverTimeRequest.OverTimeRequestId     ,[EmployeeId]
      ,[DepartmentApproval]
      ,[RequestDate] 
      ,[HRApproval]
      ,[RecordAddDate]
      ,[RequestReason]
      ,[MainType]
      ,round((MinutesCount/60),2)  as   MinutesCount
      ,WF_OverTimeRequest.[Closed]
      ,[MainType]
      ,dbo.MinutesToDuration(MinutesCount) as HoursMinutes
	  ,Employees.Name  as EmployeeNameEN
	  ,Employees.Arabic_Name  as EmployeeNameAR
      ,WF_RequestStatus.RequestStatusName  as DepartmentApprovalNameAR
	  ,WF_RequestStatus.RequestStatusNameEn  as DepartmentApprovalNameEN
	  ,Sections.Name  as SectionEnName
	  ,Sections.Arabic_Name  as SectionArName
	  ,WF_OverTimeRequestSignature.UserId
	  ,SecurityUser.EmployeeCode
	  ,(case when [DepartmentApproval]=0 then  empDeptPending.Arabic_Name else '' end) as UserPendingNameAr
	  ,(case when [DepartmentApproval]=0 then empDeptPending.Name else '' end) as UserPendingNameEN,
	  TimeFrom,	TimeTo,
	  	WF_OvertimeType.Name as OvertimeTypeNameEN ,
	  	WF_OvertimeType.Arabic_Name as OvertimeTypeNameAr ,
    	dbo.MinutesToDuration(MinutesCountAfterFactor) as   MinutesCountAfterFactor
		 ,round(Cost,2) as Cost
	  ,empDeptRefused.Arabic_Name as UserRefusedNameAr
	  ,empDeptRefused.Name as UserRefusedNameEN
		,round((MinutesCountAfterFactor/60),2)  as   TotalMinutesAfterFactor
		
  FROM  WF_OverTimeSymphonyRequest as WF_OverTimeRequest 
  inner join Employees on WF_OverTimeRequest.EmployeeId=Employees.EmployeeCode
  LEFT OUTER JOIN  WF_OvertimeType on WF_OverTimeRequest.OvertimeTypeCode=WF_OvertimeType.OvertimeTypeCode
  inner join Sections on Sections.SectionCode=Employees.SectionCode
  inner join WF_RequestStatus on WF_OverTimeRequest.DepartmentApproval=WF_RequestStatus.RequestStatusId
  LEFT OUTER JOIN WF_OverTimeSymphonyRequestSignature as WF_OverTimeRequestSignature on WF_OverTimeRequest.OverTimeRequestId=WF_OverTimeRequestSignature.OverTimeRequestId and WF_OverTimeRequestSignature.Turn=0 and WF_OverTimeRequestSignature.Approved=0
  LEFT OUTER JOIN SecurityUser on SecurityUser.SecurityUserCode=WF_OverTimeRequestSignature.UserId
  LEFT OUTER JOIN Employees as empDeptPending on empDeptPending.EmployeeCode =SecurityUser.EmployeeCode

  LEFT OUTER JOIN WF_OverTimeSymphonyRequestSignature as SignatureRefuse on WF_OverTimeRequest.OverTimeRequestId=SignatureRefuse.OverTimeRequestId and SignatureRefuse.Turn=1 and SignatureRefuse.Approved=2
  LEFT OUTER JOIN SecurityUser  as UserRefused on UserRefused.SecurityUserCode=SignatureRefuse.UserId
  LEFT OUTER JOIN Employees as empDeptRefused on empDeptRefused.EmployeeCode =UserRefused.EmployeeCode
 
 where 

      EmployeeId = COALESCE(@EmployeeId, EmployeeId)
       And  MainType = COALESCE(@MainType, MainType)
	 AND (@FromDate IS NULL OR RecordAddDate >= @FromDate)
      AND (@ToDate IS NULL OR RecordAddDate <= @ToDate )
	   AND (@OverTimeFromDate IS NULL OR RequestDate >= @OverTimeFromDate)
      AND (@OverTimeToDate IS NULL OR RequestDate <= @OverTimeToDate )
     and   Employees.SectionCode = COALESCE(@SectionCode, Employees.SectionCode)
	 
       And( DepartmentApproval=  @DeptPending
	   or DepartmentApproval= @DeptAccepted
       or  DepartmentApproval= @DeptRefused)
end	 
-------------------------End 2023-09-17---------------------------
-------------------------Hammad 2023-09-18------------------------

INSERT [dbo].[UserType] ([UserTypeCode], [Name], [Arabic_Name], [HaveEmployee], [OrganizationCategoryCode], [IsVisible]) 
VALUES (40, N'Employee', N' الموظف', 1, NULL, 1)
GO
-------------------------End 2023-09-18------------------------
-------------------------Ali 2023-09-18------------------------

 INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES (1040, 12, 3463, 26)
GO
-------------------------End 2023-09-18------------------------

------------------------------Negm  19-09-2023 -----------------------------

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) 
VALUES(1219,N'WorkFlow',N'ChangeEmployeeDataRequest',N'Index',0,N'get',15033)
Go

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) 
VALUES(1220,N'WorkFlow',N'ChangeEmployeeDataRequest',N'ChangeEmployeeDataRequest_Read',0,N'post',15034)
Go

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID])
VALUES(1221,N'WorkFlow',N'ChangeEmployeeDataRequest',N'Save',1,N'get',15035)
Go

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID])
VALUES(1222,N'WorkFlow',N'ChangeEmployeeDataRequest',N'Save',2,N'get',15036)
Go

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID])
VALUES(1223,N'WorkFlow',N'ChangeEmployeeDataRequest',N'Delete',0,N'post',15037)
Go

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) 
VALUES(1224,N'WorkFlow',N'ChangeEmployeeDataRequest',N'Save',0,N'post',15038)
Go

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID])
VALUES(1225,N'WorkFlow',N'ChangeEmployeeDataRequest',N'Cancel',0,N'post',15047)
Go

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID])
VALUES(1226,N'WorkFlow',N'ChangeEmployeeDataRequestSignature',N'Index',0,N'get',15039)
Go

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID])
VALUES(1227,N'WorkFlow',N'ChangeEmployeeDataRequestSignature',N'ChangeEmployeeDataRequestSignature_Read',0,N'post',15040)
Go

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID])
VALUES(1228,N'WorkFlow',N'ChangeEmployeeDataRequestSignature',N'Save',2,N'get',15041)
Go

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID])
VALUES(1229,N'WorkFlow',N'ChangeEmployeeDataRequestSignature',N'Save',0,N'post',15042)
Go
----------------------------------------- End ------------------------------