﻿
--------------------------------------- M.NEGM  2024-11-20 ----------------

update SystemMenu set ControllerName = N'/GenerlLedger/SupplierGroup' WHERE SystemMenuCode = 360
GO
update SystemMenu set ParentID =584 WHERE SystemMenuCode = 360
GO
update SystemMenu set Sequence =10 WHERE SystemMenuCode = 360
GO
UPDATE SystemMenuModule SET AppModuleCode= 18 WHERE SystemMenuCode=360
GO

update SystemMenu set ControllerName = N'/GenerlLedger/Supplier' WHERE SystemMenuCode = 367
GO
update SystemMenu set ParentID =584 WHERE SystemMenuCode = 367
GO
update SystemMenu set Sequence =11 WHERE SystemMenuCode = 367
GO
UPDATE SystemMenuModule SET AppModuleCode= 18 WHERE SystemMenuCode=367
GO

update SecurityPrivilegeActions set AreaName=N'GenerlLedger' where SecurityPrivilegeID in (14018,14019,14020,14021,14022,14023)
Go 


update SecurityPrivilegeActions set AreaName=N'GenerlLedger'  where SecurityPrivilegeID in (14060,14061,14062,14063,14064,14065)
Go 

update SecurityPrivilegeActions set ActionName = N'AccountsChartLevel_Read' where id = 2124
Go

update systemmenu set Arabic_Name = N'الموردين'  where  SystemMenuCode = 367
update systemmenu set Arabic_Name = N'مجموعات الموردين'  where  SystemMenuCode = 360

update systemmenu set Name = N'Suppliers '  where  SystemMenuCode = 367
update systemmenu set Name = N'Suppliers Groups'  where  SystemMenuCode = 360




  INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1121,N'Customers',N'العملاء',584,N'/GenerlLedger/Customer',N'Index','fa fa-thumb-tack',N'get',12,1,1)
Go

INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1121,18)
Go

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(23155,N' العملاء ',N'Customers ',1121,1)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(23156,N'عرض  العملاء ',N'View Customers ',1121,2)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(23157,N'إضافة  العملاء ',N'Add Customers ',1121,2)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(23158,N'تعديل  العملاء ',N'Edit Customers ',1121,2)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(23159,N'حذف  العملاء ',N'Delete Customers ',1121,2)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(23160,N'حفظ  العملاء ',N'Save Customers ',1121,2)
Go



INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(2172,N'GenerlLedger',N'Customer',N'Index',0,N'get',23155)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(2173,N'GenerlLedger',N'Customer',N'Customer_Read',0,N'post',23156)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(2174,N'GenerlLedger',N'Customer',N'Save',1,N'get',23157)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(2175,N'GenerlLedger',N'Customer',N'Save',2,N'get',23158)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(2176,N'GenerlLedger',N'Customer',N'Delete',0,N'post',23159)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(2177,N'GenerlLedger',N'Customer',N'Save',0,N'post',23160)


INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1619,N'GenerlLedger', NULL,N'Customer',N'GetCustomerSearch',N'post')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1620,N'GenerlLedger', NULL,N'Customer',N'Put',N'Put')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1621,N'GenerlLedger', NULL,N'Customer',N'post',N'post')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1622,N'GenerlLedger', NULL,N'Customer',N'Delete',N'Delete')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1623,N'GenerlLedger', NULL,N'Customer',N'Get',N'Get')
Go


INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1973,1619,23156, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1974,1620,23158, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1975,1621,23160, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1976,1622,23159, NULL)
Go

INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1977,1623,23156, NULL)
Go




INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1122,N'Customers Groups',N'مجموعات العملاء ',584,N'/GenerlLedger/CustomerGroup',N'Index','fa fa-thumb-tack',N'get',11,1,1)
Go

INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1122,18)
Go

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(23161,N'  مجموعات العملاء ',N'Customers Groups ',1122,1)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(23162,N'عرض   مجموعات العملاء ',N'View Customers Groups ',1122,2)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(23163,N'إضافة مجموعات العملاء ',N'Add Customers Groups',1122,2)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(23164,N'تعديل مجموعات العملاء ',N'Edit Customers Groups',1122,2)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(23165,N'حذف  مجموعات العملاء ',N'Delete Customers Groups',1122,2)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(23166,N'حفظ مجموعات العملاء ',N'Save Customers Groups',1122,2)
Go



INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(2178,N'GenerlLedger',N'CustomerGroup',N'Index',0,N'get',23161)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(2179,N'GenerlLedger',N'CustomerGroup',N'CustomerGroup_Read',0,N'post',23162)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(2180,N'GenerlLedger',N'CustomerGroup',N'Save',1,N'get',23163)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(2181,N'GenerlLedger',N'CustomerGroup',N'Save',2,N'get',23164)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(2182,N'GenerlLedger',N'CustomerGroup',N'Delete',0,N'post',23165)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(2183,N'GenerlLedger',N'CustomerGroup',N'Save',0,N'post',23166)


INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1624,N'GenerlLedger', NULL,N'CustomerGroup',N'GetSearchCustomerGroup',N'post')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1625,N'GenerlLedger', NULL,N'CustomerGroup',N'Put',N'Put')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1626,N'GenerlLedger', NULL,N'CustomerGroup',N'post',N'post')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1627,N'GenerlLedger', NULL,N'CustomerGroup',N'Delete',N'Delete')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1628,N'GenerlLedger', NULL,N'CustomerGroup',N'Get',N'Get')
Go


INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1978,1624,23162, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1979,1625,23164, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1980,1626,23166, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1981,1627,23165, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1982,1628,23162, NULL)
Go


--------------------------------------- End  2024-11-20 ----------------
--------------------------------------- Hammad  2024-11-21 -------------

CREATE OR ALTER VIEW [dbo].[ArchiveWFRequestView]
AS
WITH   RequestFiles
AS     (SELECT   RequestId,
                 RequestTypeCode,
                 STRING_AGG([FileName], '|') WITHIN GROUP (ORDER BY [FileName]) AS CombinedFileNames
        FROM     WF_RequestsFile
        GROUP BY RequestId, RequestTypeCode),
       MasterRequestFiles
AS     (SELECT   MasterRequestCode,
                 STRING_AGG([FileName], '|') WITHIN GROUP (ORDER BY [FileName]) AS CombinedFileNames
        FROM     WF_MasterRequestsFiles
        GROUP BY MasterRequestCode),
       ArchiveWFRequest
AS     (SELECT COALESCE (request.[FileName], RequestFiles.CombinedFileNames) AS FileName,
               request.UnitNumber AS RequestId,
               request.AddDate AS CreationDate,
               request.DepartmentApproval,
               request.EmployeeId AS EmployeeCode,
               11 AS RequestTypeCode,
               request.FromDate,
               request.ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr
        FROM   WF_VacationRequest AS request
               INNER JOIN
               VacationType AS requestType
               ON requestType.VacationCode = request.VacationTypeId
               LEFT OUTER JOIN
               RequestFiles
               ON request.UnitNumber = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 11
        WHERE  request.DepartmentApproval = 1
               AND (UnitNumber IN (SELECT RequestId
                                   FROM   RequestFiles
                                   WHERE  RequestTypeCode = 11)
                    OR request.[FileName] IS NOT NULL)
               AND request.VacationRequestId IN (SELECT VacationRequestId
                                                 FROM   WF_VacationRequestSignature AS sig
                                                 WHERE  sig.VacationRequestId = request.VacationRequestId)
        UNION ALL
        SELECT COALESCE (request.[AttachementFilePath], RequestFiles.CombinedFileNames) AS FileName,
               PermissionRequestId AS RequestId,
               AddDate AS CreationDate,
               DepartmentApproval,
               EmployeeId AS EmployeeCode,
               2 AS RequestTypeCode,
               request.PermissionsDate AS FromDate,
               request.PermissionsDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr
        FROM   WF_PermissionRequest AS request
               INNER JOIN
               TimePermissionsTypes AS requestType
               ON requestType.TimePermissionCode = request.TimePermissionCode
               LEFT OUTER JOIN
               RequestFiles
               ON request.PermissionRequestId = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 2
        WHERE  (PermissionRequestId IN (SELECT RequestId
                                        FROM   RequestFiles
                                        WHERE  RequestTypeCode = 2)
                OR request.[AttachementFilePath] IS NOT NULL)
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], RequestFiles.CombinedFileNames) AS FileName,
               OverTimeRequestId AS RequestId,
               RecordAddDate AS CreationDate,
               DepartmentApproval,
               EmployeeId AS EmployeeCode,
               3 AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDateTo AS ToDate,
               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
               LEFT OUTER JOIN
               RequestFiles
               ON request.OverTimeRequestId = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 3
        WHERE  (OverTimeRequestId IN (SELECT RequestId
                                      FROM   RequestFiles
                                      WHERE  RequestTypeCode = 3)
                OR request.[FileName] IS NOT NULL)
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], RequestFiles.CombinedFileNames) AS FileName,
               AirTicketRequestCode AS RequestId,
               CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               4 AS RequestTypeCode,
               request.TicketFromDate AS FromDate,
               request.TicketToDate AS ToDate,
               '' AS TypeName,
               '' AS TypeNameAr
        FROM   WF_AirTicketRequest AS request
               LEFT OUTER JOIN
               RequestFiles
               ON request.AirTicketRequestCode = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 4
        WHERE  (AirTicketRequestCode IN (SELECT RequestId
                                         FROM   RequestFiles
                                         WHERE  RequestTypeCode = 4)
                OR request.[FileName] IS NOT NULL)
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], RequestFiles.CombinedFileNames) AS FileName,
               FingerPrintRequestId AS RequestId,
               RecordAddDate AS CreationDate,
               DepartmentApproval,
               EmployeeId AS EmployeeCode,
               6 AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               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
               LEFT OUTER JOIN
               RequestFiles
               ON request.FingerPrintRequestId = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 6
        WHERE  (FingerPrintRequestId IN (SELECT RequestId
                                         FROM   RequestFiles
                                         WHERE  RequestTypeCode = 6)
                OR request.[FileName] IS NOT NULL)
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[AttachementFilePath], RequestFiles.CombinedFileNames) AS FileName,
               ResignationRequestCode AS RequestId,
               CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               7 AS RequestTypeCode,
               request.ResignationDate AS FromDate,
               request.ResignationDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr
        FROM   WF_ResignationRequest AS request
               INNER JOIN
               ResignationReasons AS requestType
               ON requestType.ResignationCode = request.ResignationCode
               LEFT OUTER JOIN
               RequestFiles
               ON request.ResignationCode = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 7
        WHERE  (ResignationRequestCode IN (SELECT RequestId
                                           FROM   RequestFiles
                                           WHERE  RequestTypeCode = 7)
                OR request.[AttachementFilePath] IS NOT NULL)
               AND DepartmentApproval = 1
        UNION ALL
        SELECT RequestFiles.CombinedFileNames AS FileName,
               LoanRequestId AS RequestId,
               RecordAddDate AS CreationDate,
               DepartmentApproval,
               EmployeeId AS EmployeeCode,
               9 AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               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
               LEFT OUTER JOIN
               RequestFiles
               ON request.LoanRequestId = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 9
        WHERE  (LoanRequestId IN (SELECT RequestId
                                  FROM   RequestFiles
                                  WHERE  RequestTypeCode = 9))
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], RequestFiles.CombinedFileNames) AS FileName,
               PromotionRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               10 AS RequestTypeCode,
               request.EffectDate AS FromDate,
               request.EffectDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr
        FROM   WF_PromotionRequest AS request
               INNER JOIN
               PromotionReasons AS requestType
               ON requestType.PromotionReasonCode = request.PromotionReasonCode
               LEFT OUTER JOIN
               RequestFiles
               ON request.PromotionRequestCode = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 10
        WHERE  (PromotionRequestCode IN (SELECT RequestId
                                         FROM   RequestFiles
                                         WHERE  RequestTypeCode = 10)
                OR request.[FileName] IS NOT NULL)
               AND DepartmentApproval = 1
        UNION ALL
        SELECT RequestFiles.CombinedFileNames AS FileName,
               PenaltyRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               13 AS RequestTypeCode,
               request.PenaltyDate AS FromDate,
               request.PenaltyDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr
        FROM   WF_PenaltyRequest AS request
               INNER JOIN
               PenaltiesList AS requestType
               ON requestType.PenaltyCode = request.PenaltyCode
               INNER JOIN
               RequestFiles
               ON request.PenaltyRequestCode = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 13
        WHERE  (PenaltyRequestCode IN (SELECT RequestId
                                       FROM   WF_RequestsFile
                                       WHERE  RequestTypeCode = 13))
               AND DepartmentApproval = 1
        UNION ALL
        SELECT RequestFiles.CombinedFileNames AS FileName,
               ChangeDayoffRequestId AS RequestId,
               request.RecordAddDate AS CreationDate,
               DepartmentApproval,
               EmployeeId AS EmployeeCode,
               18 AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               '' AS TypeName,
               '' AS TypeNameAr
        FROM   WF_ChangeDayoffRequest AS request
               INNER JOIN
               RequestFiles
               ON request.ChangeDayoffRequestId = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 18
        WHERE  (ChangeDayoffRequestId IN (SELECT RequestId
                                          FROM   WF_RequestsFile
                                          WHERE  RequestTypeCode = 18))
               AND DepartmentApproval = 1
        UNION ALL
        SELECT RequestFiles.CombinedFileNames AS FileName,
               CompensationRequestForADayOfRestId AS RequestId,
               request.RecordAddDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               21 AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               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
               INNER JOIN
               RequestFiles
               ON request.CompensationRequestForADayOfRestId = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 21
        WHERE  (CompensationRequestForADayOfRestId IN (SELECT RequestId
                                                       FROM   WF_RequestsFile
                                                       WHERE  RequestTypeCode = 21))
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_GeneralsRequest AS generalRequest
               ON generalRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               WF_GeneralRequestType AS requestType
               ON requestType.GeneralRequestTypeCode = generalRequest.GeneralRequestTypeCode
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND DepartmentApproval = 1
               AND RequestTypeCode = 19
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_BonusesRequest AS bonusesRequest
               ON bonusesRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               WF_BonusType AS requestType
               ON requestType.BonusTypeCode = bonusesRequest.BonusTypeCode
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND DepartmentApproval = 1
               AND RequestTypeCode = 15
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_MonthlyEffectsRequest AS monthlyEffectsRequest
               ON monthlyEffectsRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               WF_MonthlyEffectType AS requestType
               ON requestType.MonthlyEffectTypeCode = monthlyEffectsRequest.MonthlyEffectTypeCode
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND DepartmentApproval = 1
               AND RequestTypeCode = 27
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_ResidenceRequest AS residenceRequest
               ON residenceRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               WF_ResidenceTypes AS requestType
               ON requestType.ResidenceTypeCode = residenceRequest.ResidenceTypeCode
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND RequestTypeCode = 29
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_FinancialPaymentRequest AS financialPaymentRequest
               ON financialPaymentRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               WF_FinancialPaymentType AS requestType
               ON requestType.FinancialPaymentTypeCode = financialPaymentRequest.FinancialPaymentTypeCode
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND RequestTypeCode = 30
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_IntroductionletterRequest AS introductionletterRequest
               ON introductionletterRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               WF_letterTypes AS requestType
               ON requestType.letterTypeCode = introductionletterRequest.letterTypeCode
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND RequestTypeCode = 50
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_TimeSheetRequestDetail AS timeSheetRequest
               ON timeSheetRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               WF_MonthlyEffectType AS requestType
               ON requestType.MonthlyEffectTypeCode = timeSheetRequest.MonthlyEffectTypeCode
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND RequestTypeCode = 55
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_AssignmentRequests AS assignmentRequest
               ON assignmentRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               Contract AS requestType
               ON requestType.ContractCode = assignmentRequest.SubContractCode
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND RequestTypeCode = 59
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_RenewContractRequests AS renewContractRequest
               ON renewContractRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               Contract AS requestType
               ON requestType.ContractCode = renewContractRequest.ContractCode
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND RequestTypeCode = 60
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_TransferRequest AS transferRequest
               ON transferRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               PromotionReasons AS requestType
               ON requestType.PromotionReasonCode = transferRequest.TransferType
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND RequestTypeCode = 62
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_SocialCareRequest AS socialCareRequest
               ON socialCareRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               WF_SocialCareTypes AS requestType
               ON requestType.SocialCareTypeCode = socialCareRequest.SocialCareTypeCode
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND RequestTypeCode = 65
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               '' AS TypeName,
               '' AS TypeNameAr
        FROM   WF_MasterRequests AS request
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND DepartmentApproval = 1
               AND RequestTypeCode NOT IN (19, 15, 27, 29, 50, 55, 59, 60, 62, 65))
SELECT ArchiveWFRequest.*,
       emp.Name AS EmployeeNameEn,
       emp.Arabic_Name AS EmployeeNameAR,
       requestType.RequestTypeNameEn AS RequestTypeNameEn,
       requestType.RequestTypeName AS RequestTypeNameAR
FROM   ArchiveWFRequest
       INNER JOIN
       Employees AS emp
       ON emp.EmployeeCode = ArchiveWFRequest.EmployeeCode
       INNER JOIN
       WF_RequestType AS requestType
       ON requestType.RequestTypeId = ArchiveWFRequest.RequestTypeCode;

GO




CREATE OR ALTER PROCEDURE [dbo].[GetPaginatedArchiveRequests]
@PageNumber INT=1, @PageSize INT=10, @StartDate DATETIME=NULL, @EndDate DATETIME=NULL, @RequestTypeCode INT=NULL, @EmployeeCode INT=NULL
AS
BEGIN
    SET NOCOUNT ON;
    WITH     PaginatedResults
    AS       (SELECT *,
                     COUNT(*) OVER () AS TotalRecords,
                     CEILING(COUNT(*) OVER () * 1.0 / @PageSize) AS TotalPages,
                     ROW_NUMBER() OVER (ORDER BY CreationDate DESC) AS RowNum
              FROM   ArchiveWFRequestView
              WHERE  (@StartDate IS NULL
                      OR CreationDate >= @StartDate)
                     AND (@EndDate IS NULL
                          OR CreationDate <= @EndDate)
                     AND (@RequestTypeCode IS NULL
                          OR RequestTypeCode = @RequestTypeCode)
                     AND (@EmployeeCode IS NULL
                          OR EmployeeCode = @EmployeeCode))
    SELECT   *
    FROM     PaginatedResults
    WHERE    RowNum BETWEEN ((@PageNumber - 1) * @PageSize + 1) AND (@PageNumber * @PageSize)
    ORDER BY CreationDate DESC;
END

GO


INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (9045, N'عرض وثائق طلبات الموظف', N'Show Employee Requests Files', 504, 2)
GO

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES (2184, N'Archive', N'ArcEmpRequest', N'Index', 0, N'get', 9045)
GO
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES (2185, N'Archive', N'ArcEmpRequest', N'EmployeeRequest_Read', 0, N'post', 9045)
GO
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES (1629, NULL, NULL, N'ArcEmpRequest', N'GetArcEmpRequest', N'Post')
GO

INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES (1983, 1629, 9045, NULL)
GO

--------------------------------------- End  2024-11-21 -------------