﻿--------------------------Shehab 2026-03-17---------------------------

update WF_RequestType set IsSmartRequest = 1 where RequestTypeId in (2,6,7,9,11,13,19)
Go

--------------------------ENd 2026-03-17---------------------------
--------------------------Ali 2026-03-18---------------------------
                ALTER TABLE [EmployeeFiltering]
ALTER COLUMN IsActive BIT NULL;
Go
--------------------------ENd 2026-03-18---------------------------
	--------------------------Ali 2026-03-24---------------------------
 INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1260,N'Collected reports',N'تقارير مجمعة',1259,N'/PMS/ReportSearch',N'index','fa fa-thumb-tack',N'Get',1,1,1)
Go

INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1260,8)
Go

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(26153,N'تقارير مجمعة',N'Collected reports',1260,1)
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(26154,N'عرض تقارير مجمعة',N'View Collected reports',1260,2)
Go


--------------------------ENd 2026-03-24---------------------------
--------------------------Hammad 2026-03-24------------------------
				
CREATE OR ALTER PROCEDURE [dbo].[GetSyncLogExceptionRequests]
    @RequestTypeCode INT = NULL,
	@PageNumber INT=1, 
	@PageSize INT=10,
	@language int = 1,
	@EmployeeCode int = Null
AS
BEGIN

SET NOCOUNT ON;
 WITH     PaginatedResults
 as
    (SELECT sync.SyncLogExceptionCode,requests.*, sync.Exception, case when @language = 1 then emp.Arabic_Name else emp.Name end as EmployeeName ,
	case when @language = 1 then reqType.RequestTypeName else reqType.RequestTypeNameEn end as RequestTypeName,

	COUNT(*) OVER () AS TotalRecords,
            CEILING(COUNT(*) OVER () * 1.0 / @PageSize) AS TotalPages,

            ROW_NUMBER() OVER (ORDER BY requests.CreationDate DESC) AS RowNum
    FROM (
        -- vacation request
        SELECT 
            request.UnitNumber AS RequestId,
            request.AddDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeId AS EmployeeCode,
            11 AS RequestTypeCode,
            request.FromDate AS RequestDate,
            requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_VacationRequest AS request
        INNER JOIN VacationType AS requestType
            ON requestType.VacationCode = request.VacationTypeId
        WHERE DepartmentApproval = 1 
          AND MainType = 0  
          AND request.IsSynced = 0 

        UNION ALL

		-- PermissionRequest
        SELECT 
            request.PermissionRequestId AS RequestId,
            request.AddDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeId AS EmployeeCode,
            2 AS RequestTypeCode,
            request.PermissionsDate AS RequestDate,
            requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_PermissionRequest AS request
        INNER JOIN TimePermissionsTypes AS requestType
            ON requestType.TimePermissionCode = request.TimePermissionCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		  -- ManpowerBudgetRequest
        SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            66 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			 '' AS TypeName,
            '' AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_ManpowerBudgetRequest AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		 UNION ALL

		    --BackToWorkRequest
        SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            49 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			 '' AS TypeName,
            '' AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_BackToWorkRequest AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		      --SocialCareRequest
        SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            64 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			socialType.Name AS TypeName,
            socialType.Arabic_Name AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_SocialCareRequest AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
			join WF_SocialCareTypes socialType on requestType.SocialCareTypeCode = socialType.SocialCareTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0
		 
		 UNION ALL

		 --AssignmentRequests
        SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            59 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			 '' AS TypeName,
            '' AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_AssignmentRequests AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		 UNION ALL

		  	 --TransferRequest
        SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            25 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			 '' AS TypeName,
            '' AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_TransferRequest AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		   --MonthlyEffectsRequest
		     SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            27 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			effectType.Name AS TypeName,
            effectType.Arabic_Name AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_MonthlyEffectsRequest AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
			join WF_MonthlyEffectType effectType on requestType.MonthlyEffectTypeCode = effectType.MonthlyEffectTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		  
		   --TimeSheetRequestDetail
		     SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            55 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			effectType.Name AS TypeName,
            effectType.Arabic_Name AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_TimeSheetRequestDetail AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
			join WF_MonthlyEffectType effectType on requestType.MonthlyEffectTypeCode = effectType.MonthlyEffectTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0
		 
		 UNION ALL

		   --WF_BonusesRequest
		     SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            15 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			bonusType.Name AS TypeName,
            bonusType.Arabic_Name AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_BonusesRequest AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
			join WF_BonusType bonusType on requestType.BonusTypeCode = bonusType.BonusTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		 UNION ALL

		     --WF_ChangeEmployeeDataRequest
		     SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            42 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			dataType.Name AS TypeName,
            dataType.Arabic_Name AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_ChangeEmployeeDataRequest AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
			join WF_EmployeeDataType dataType on requestType.EmployeeDataTypeCode = dataType.EmployeeDataTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		   --WF_RenewContractRequests
		     SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            60 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			cont.Name AS TypeName,
            cont.Arabic_Name AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_RenewContractRequests AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
			join Contract cont on requestType.ContractCode = cont.ContractCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		   --WF_NewJobRequest
		     SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            26 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_NewJobRequest AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		  	--WF_RehireEmployeeRequest
		     SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            69 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			cont.Name AS TypeName,
            cont.Arabic_Name AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_RehireEmployeeRequest AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
			join Contract cont on requestType.ContractCode = cont.ContractCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		  	  	--WF_OverTimeRequest
		     SELECT 
            request.OverTimeRequestId AS RequestId,
            request.RecordAddDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeId AS EmployeeCode,
            3 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_OverTimeRequest AS request
        INNER JOIN WF_OvertimeType AS requestType
            ON requestType.OvertimeTypeCode = request.OvertimeTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		    	  	--WF_OverTimeRequestFinal
		     SELECT 
            request.OverTimeRequestId AS RequestId,
            request.RecordAddDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeId AS EmployeeCode,
            20 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_OverTimeRequest AS request
        INNER JOIN WF_OvertimeType AS requestType
            ON requestType.OvertimeTypeCode = request.OvertimeTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		 UNION ALL

		    --WF_FingerPrintRequest
		     SELECT 
            request.FingerPrintRequestId AS RequestId,
            request.RecordAddDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeId AS EmployeeCode,
            6 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_FingerPrintRequest AS request
        INNER JOIN WF_FingerPrintType AS requestType
            ON requestType.FingerPrintTypeCode = request.FingerPrintTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		 UNION ALL

		  	 --WF_CompensationRequestForADayOfRest
		     SELECT 
            request.CompensationRequestForADayOfRestId AS RequestId,
            request.RecordAddDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            21 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_CompensationRequestForADayOfRest AS request
		INNER JOIN WF_CompensationAllowanceTypes AS requestType
            ON requestType.CompensationAllowanceTypeCode = request.CompensationAllowanceTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		   --WF_CompensationRequestForADayOfRestFinal
		     SELECT 
            request.CompensationRequestForADayOfRestId AS RequestId,
            request.RecordAddDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            22 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_CompensationRequestForADayOfRest AS request
		INNER JOIN WF_CompensationAllowanceTypes AS requestType
            ON requestType.CompensationAllowanceTypeCode = request.CompensationAllowanceTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		 UNION ALL 


		     --WF_LoanRequest
		     SELECT 
            request.LoanRequestId AS RequestId,
            request.RecordAddDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeId AS EmployeeCode,
            9 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_LoanRequest AS request
		INNER JOIN WF_LoanType AS requestType
            ON requestType.LoanTypeCode = request.LoanTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		  
		     --WF_PenaltyRequest
		     SELECT 
            request.PenaltyRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            13 AS RequestTypeCode,
            request.PenaltyDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_PenaltyRequest AS request
		INNER JOIN PenaltiesList AS requestType
            ON requestType.PenaltyCode = request.PenaltyCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		  	 --WF_PromotionRequest
		     SELECT 
            request.PromotionRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            10 AS RequestTypeCode,
            request.EffectDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_PromotionRequest AS request
		INNER JOIN PromotionReasons AS requestType
            ON requestType.PromotionReasonCode = request.PromotionReasonCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0


		  UNION ALL

		  	 --WF_AirTicketRequest
		     SELECT 
            request.AirTicketRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            4 AS RequestTypeCode,
            request.TicketFromDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_AirTicketRequest AS request
		INNER JOIN AirTicketCondition AS requestType
            ON requestType.AirTicketConditionCode = request.AirTicketConditionCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0
		  

		  UNION ALL

		  		  	 --WF_NewHireRequest
		     SELECT 
            request.NewHireRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            5 AS RequestTypeCode,
            request.CreationDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_NewHireRequest AS request
		INNER JOIN Contract AS requestType
            ON requestType.ContractCode = request.ContractCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		 UNION ALL

		    	 --WF_ResignationRequest
		     SELECT 
            request.ResignationRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            7 AS RequestTypeCode,
            request.CreationDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_ResignationRequest AS request
		INNER JOIN ResignationReasons AS requestType
            ON requestType.ResignationCode = request.ResignationCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

    ) AS requests
    left JOIN WF_SyncLogException sync
        ON sync.RequestId = requests.RequestId
       AND sync.RequestTypeCode = requests.RequestTypeCode
	   inner join Employees emp on requests.EmployeeCode = emp.EmployeeCode
	   inner join WF_RequestType reqType on requests.RequestTypeCode = reqType.RequestTypeId
    WHERE (@RequestTypeCode IS NULL OR requests.RequestTypeCode = @RequestTypeCode)
	And (@EmployeeCode IS NULL OR requests.EmployeeCode = @EmployeeCode)
	)
	SELECT   *
    FROM     PaginatedResults
    WHERE    RowNum BETWEEN ((@PageNumber - 1) * @PageSize + 1) AND (@PageNumber * @PageSize)
    ORDER BY RequestTypeCode DESC;
END
GO

--------------------------End 2026-03-24------------------------
--------------------------Shehab 2026-03-24------------------------

update WF_RequestType set IsSmartRequest = 1 where RequestTypeId in (2,6,7,9,11,13,19)
Go
--------------------------End 2026-03-24------------------------
--------------------------Shehab 2026-03-24---------------------------

update WF_RequestType set IsSmartRequest = 1 where RequestTypeId in (60,15)
Go

--------------------------ENd 2026-03-24---------------------------
				