﻿----------------------------------------- Hammad 2025-12-25 --------------------


CREATE  Or Alter      VIEW [dbo].[WFRequestSigAutoApproveView]
AS
WITH   RequestAutoApproved
AS  (
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,
       Sig.UserId,Sig.SignatureDate
FROM   WF_VacationRequest AS request
       INNER JOIN VacationType AS requestType
       ON requestType.VacationCode = request.VacationTypeId
       INNER JOIN WF_VacationRequestSignature AS Sig
       ON request.VacationRequestId = Sig.VacationRequestId
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

SELECT request.PermissionRequestId AS RequestId,
       AddDate AS CreationDate,
       DepartmentApproval,
       EmployeeId AS EmployeeCode,
       2 AS RequestTypeCode,
       request.PermissionsDate AS RequestDate,
       requestType.Name AS TypeName,
       requestType.Arabic_Name AS TypeNameAr,
       Sig.UserId,Sig.SignatureDate
FROM   WF_PermissionRequest AS request
       INNER JOIN TimePermissionsTypes AS requestType
       ON requestType.TimePermissionCode = request.TimePermissionCode
       INNER JOIN WF_PermissionRequestSignature AS Sig
       ON request.PermissionRequestId = Sig.PermissionRequestId
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

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,
       Sig.UserId,Sig.SignatureDate
FROM   WF_OverTimeRequest AS request
       INNER JOIN WF_OvertimeType AS requestType
       ON requestType.OvertimeTypeCode = request.OvertimeTypeCode
       INNER JOIN WF_OverTimeRequestSignature AS Sig
       ON request.OverTimeRequestId = Sig.OverTimeRequestId
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

SELECT request.AirTicketRequestCode AS RequestId,
       request.CreationDate AS CreationDate,
       request.DepartmentApproval,
       request.EmployeeCode AS EmployeeCode,
       4 AS RequestTypeCode,
       request.TicketFromDate AS RequestDate,
       '' AS TypeName,
       '' AS TypeNameAr,
        Sig.UserCode UserId,Sig.SignatureDate
FROM   WF_AirTicketRequest AS request
       INNER JOIN WF_AirTicketRequestSignature AS Sig
       ON request.AirTicketRequestCode = Sig.AirTicketRequestCode
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

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,
       Sig.UserId,Sig.SignatureDate
FROM   WF_FingerPrintRequest AS request
       INNER JOIN WF_FingerPrintType AS requestType
       ON requestType.FingerPrintTypeCode = request.FingerPrintTypeCode
       INNER JOIN WF_FingerPrintRequestSignature AS Sig
       ON request.FingerPrintRequestId = Sig.FingerPrintRequestId
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

SELECT request.ResignationRequestCode AS RequestId,
       request.CreationDate AS CreationDate,
       request.DepartmentApproval,
       request.EmployeeCode AS EmployeeCode,
       7 AS RequestTypeCode,
       request.ResignationDate AS RequestDate,
       requestType.Name AS TypeName,
       requestType.Arabic_Name AS TypeNameAr,
        Sig.UserCode UserId,Sig.SignatureDate
FROM   WF_ResignationRequest AS request
       INNER JOIN ResignationReasons AS requestType
       ON requestType.ResignationCode = request.ResignationCode
       INNER JOIN WF_ResignationRequestSignature AS Sig
       ON request.ResignationRequestCode = Sig.ResignationRequestCode
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

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,
       Sig.UserId,Sig.SignatureDate
FROM   WF_LoanRequest AS request
       INNER JOIN WF_LoanType AS requestType
       ON requestType.LoanTypeCode = request.LoanTypeCode
       INNER JOIN WF_LoanRequestSignature AS Sig
       ON request.LoanRequestId = Sig.LoanRequestId
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

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,
        Sig.UserCode UserId,Sig.SignatureDate
FROM   WF_PromotionRequest AS request
       INNER JOIN PromotionReasons AS requestType
       ON requestType.PromotionReasonCode = request.PromotionReasonCode
       INNER JOIN WF_PromotionRequestSignature AS Sig
       ON request.PromotionRequestCode = Sig.PromotionRequestCode
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

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,
        Sig.UserCode UserId,Sig.SignatureDate 
FROM   WF_PenaltyRequest AS request
       INNER JOIN PenaltiesList AS requestType
       ON requestType.PenaltyCode = request.PenaltyCode
       INNER JOIN WF_PenaltyRequestSignature AS Sig
       ON request.PenaltyRequestCode = Sig.PenaltyRequestCode
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

SELECT request.ChangeDayoffRequestId AS RequestId,
       request.RecordAddDate AS CreationDate,
       request.DepartmentApproval,
       request.EmployeeId AS EmployeeCode,
       18 AS RequestTypeCode,
       request.RequestDate AS RequestDate,
       '' AS TypeName,
       '' AS TypeNameAr,
       Sig.UserId,Sig.SignatureDate
FROM   WF_ChangeDayoffRequest AS request
       INNER JOIN WF_ChangeDayoffRequestSignature AS Sig
       ON request.ChangeDayoffRequestId = Sig.ChangeDayoffRequestId
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

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,
       Sig.UserId,Sig.SignatureDate
FROM   WF_CompensationRequestForADayOfRest AS request
       INNER JOIN WF_CompensationAllowanceTypes AS requestType
       ON requestType.CompensationAllowanceTypeCode = request.CompensationAllowanceTypeCode
       INNER JOIN WF_CompensationRequestForADayOfRestSignature AS Sig
       ON request.CompensationRequestForADayOfRestId = Sig.CompensationRequestForADayOfRestId
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

SELECT request.MasterRequestCode AS RequestId,
       request.CreationDate AS CreationDate,
       request.DepartmentApproval,
       request.EmployeeCode AS EmployeeCode,
       request.RequestTypeCode AS RequestTypeCode,
       request.RequestDate AS RequestDate,
       '' AS TypeName,
       '' AS TypeNameAr,
        Sig.UserCode UserId,Sig.SignatureDate
FROM   WF_MasterRequests AS request
       INNER JOIN WF_MasterRequestsSignature AS Sig
       ON request.MasterRequestCode = Sig.MasterRequestCode
WHERE Sig.SignatureComment = 'Auto Approved By System'
)


SELECT RequestAutoApproved.*,
       emp.Name AS EmployeeNameEn,
       emp.Arabic_Name AS EmployeeNameAR,
       requestType.RequestTypeNameEn AS RequestTypeNameEn,
       requestType.RequestTypeName AS RequestTypeNameAR,
	          SecurityUser.Name AS SecurityUserNameEn,
       SecurityUser.Arabic_Name AS SecurityUserNameAR,
        SecurityUser.UserName
FROM   RequestAutoApproved
       INNER JOIN
       Employees AS emp
       ON emp.EmployeeCode = RequestAutoApproved.EmployeeCode
       INNER JOIN
       WF_RequestType AS requestType
       ON requestType.RequestTypeId = RequestAutoApproved.RequestTypeCode
	    INNER JOIN
       SecurityUser 
       ON SecurityUser.SecurityUserCode = RequestAutoApproved.UserId;
	   
GO



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

GO


INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) 
VALUES (721, N'Request Signatures Auto Approved', N' إعتمادات لها موافقة تلقائية ', 108, N'/WorkFlow/RequestSigAutoApproved', N'Index', N'fas fa-file-signature', N'get', 34, 1, 1)
GO
INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (15604, N'إعتمادات لها موافقة تلقائية ', N'Request Signatures Auto Approved', 721, 1)
GO
INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (15605, N'عرض إعتمادات لها موافقة تلقائية ', N'View Request Signatures Auto Approved', 721, 2)
GO
INSERT [dbo].[SystemMenuModule] ([SystemMenuCode], [AppModuleCode]) VALUES (721, 1)
GO


-----------------------------------------END Hammad 2025-12-25 --------------------
----------------------------------------- AK 2025-12-25 --------------------
UPDATE dbo.LookupTables SET DataValueField=N'PersonCode' WHERE LookupTableId IN (16,17) 
GO
-----------------------------------------END AK 2025-12-25 --------------------
-----------------------------------------Hammad 2025-12-31 --------------------
INSERT [dbo].[ConfigParameter] ([ConfigKey], [Name], [Arabic_Name], [ConfigValue], [ConfigCategoryCode], [OrderNo], [ConfigType], [IsVisible], [ModifiedBy], [ModificationDate], [RequestTypeId], [ConfigDescription]) 
VALUES (N'WFCalloutOverTimeTypeCode', N' Callout OverTime Type Code', N' كود نوع الاستدعاء للعمل الإضافي  ', N'0', 2, 26, 2, 1, NULL, NULL, 3, N' كود نوع الاستدعاء للعمل الإضافي  ')
GO
-----------------------------------------End 2025-12-31 --------------------