--------- Stored Procefure script----------

----- AttendanceAdjustedHours------

--Mail Notification Eligibile Updates----------
	DECLARE @Getdate NVARCHAR(20)
	Declare @MailSentTime Nvarchar(20)
	Declare @WeekDayName Nvarchar(20)

	Declare @WeekStartDate nvarchar(20)

	SET @Getdate = @SHIFTDATE
	set @MailSentTime='23:00'

	-- Set Monday as first day of week
	SET DATEFIRST 1;

	-- Day name
	SELECT @WeekDayName= DATENAME(WEEKDAY, CAST(@Getdate AS DATE));

	-- Week start (Monday)
	SELECT @WeekStartDate=DATEADD(DAY, 1 - DATEPART(WEEKDAY, CAST(@Getdate AS DATE)), CAST(@Getdate AS DATE))

	if(@WeekDayName='Thursday' and (Select Count(*) From TA_CompressedWorkHoursApplication where Employee_Id=@EmployeeCodeId and ToDate=@SHIFTDATE and Status='Approved' and CancelledMailTrigger!=1)>0)
	Begin
		
		update TA_CompressedWorkHoursApplication set CancellationReason='System Rejection',CancelledMailTrigger=0,ModifiedDate=GETDATE()  where
		Employee_Id=@EmployeeCodeId and @SHIFTDATE between FromDate And ToDate  and CancelledMailTrigger is nUll and ModifiedDate is Null and
		FromDate=@WeekStartDate

	End

	if(@WeekDayName='Friday' and (Select Count(*) From TA_CompressedWorkHoursApplication where Employee_Id=@EmployeeCodeId and ToDate=@SHIFTDATE and Status='Approved' and (CancelledMailTrigger=0 or CancelledMailTrigger is Null))>0)
	begin

		Update TA_Tbl_ProcessedData set Attendance='Approved Compressed work Hours' where Employee_Code_Id=@EmployeeCodeId and Date=@SHIFTDATE

	End


	Update TA_Tbl_ProcessedData set C_WH=Case when Cast(@HrsWork as time)>Cast('10:00' AS time) then '10:00' Else @HrsWork End where Date=@SHIFTDATE and Employee_Code_Id=@EmployeeCodeId

------ End------------
------- Script 2----------
-- ============================================================
-- Proc_TA_GetCWHSanctionDetailsForSuperAdmin
-- SuperAdmin variant — mirrors Proc_TA_GetManualEntrySanctionDetailsForSuperAdmin
-- exec Proc_TA_GetCWHSanctionDetailsForSuperAdmin @Employee_ID=1,@LevelId='1',@Status='Pending',@EmployeeId='',@EmployeeName='',@EmployeeCategory=0,@EmployeeType=0,@CWH_FromDate='1/1/2024',@CWH_ToDate='12/31/2024',@Isdeleted=0,@EmpSwipeCard='',@UserGroupName='SuperAdmin',@ParentID=1
-- exec Proc_TA_GetCWHSanctionDetailsForSuperAdmin @Employee_ID=1,@LevelId='1',@Status='Pending',@EmployeeId='',@EmployeeName='',@EmployeeCategory=0,@EmployeeType=0,@CWH_FromDate='01/Mar/2026',@CWH_ToDate='21/Mar/2026',@Isdeleted=0,@EmpSwipeCard='',@UserGroupName='SuperAdmin',@ParentID=1
-- ============================================================
ALTER PROCEDURE [dbo].[Proc_TA_GetCWHSanctionDetailsForSuperAdmin]
    @Employee_ID        INT,
    @LevelId            VARCHAR(2000),
    @Status             VARCHAR(20)     = NULL,
    @EmployeeId         VARCHAR(100),
    @EmployeeName       VARCHAR(50),
    @EmployeeCategory   INT,
    @EmployeeType       INT,
    @CWH_FromDate       VARCHAR(20)     = NULL,
    @CWH_ToDate         VARCHAR(20)     = NULL,
    @Isdeleted          BIT             = 0,
    @EmpSwipeCard       VARCHAR(20),
    @UserGroupName      VARCHAR(250),
    @ParentID           INT
AS
BEGIN
    SET NOCOUNT ON;

    -- ─── Step 1: Read Active Date Format from Master Table ───────────────────
    -- Expects TA_Tbl_DateandTimeFormat to have columns:
    --   DateFormat VARCHAR  (e.g. 'dd-MMM-yyyy', 'dd/MM/yyyy', 'mm-dd-yyyy')
    --   IsActive   BIT
    DECLARE @DateFormat     VARCHAR(50)
    DECLARE @SqlDateStyle   INT         -- SQL CONVERT style number

    SELECT TOP 1
        @DateFormat = LTRIM(RTRIM(DateFormat))
    FROM TA_Tbl_DateandTimeFormat

    -- ─── Step 2: Map DateFormat string → SQL CONVERT style ──────────────────
    --   'dd-MMM-yyyy'  →  106  (e.g. 15-Jan-2025)
    --   'dd/MM/yyyy'   →  103  (e.g. 15/01/2025)
    --   'mm-dd-yyyy'   →  110  (e.g. 01-15-2025)
    --   fallback       →  101  (MM/DD/YYYY)
    SET @SqlDateStyle = CASE UPPER(@DateFormat)
                            WHEN 'DD-MMM-YYYY' THEN 106
                            WHEN 'DD/MM/YYYY'  THEN 103
                            WHEN 'MM-DD-YYYY'  THEN 110
                            ELSE 101   -- default: MM/DD/YYYY
                        END

    -- ─── Step 3: Parse Input Date Strings using the detected style ───────────
    DECLARE @FromDate DATETIME
    DECLARE @ToDate   DATETIME

    -- Primary attempt: parse using the configured style
    SET @FromDate = TRY_CONVERT(DATETIME, @CWH_FromDate, @SqlDateStyle)
    SET @ToDate   = TRY_CONVERT(DATETIME, @CWH_ToDate,   @SqlDateStyle)

    -- Fallback chain: try other known styles if primary fails
    IF @FromDate IS NULL
        SET @FromDate = COALESCE(
            TRY_CONVERT(DATETIME, @CWH_FromDate, 106),   -- dd-MMM-yyyy
            TRY_CONVERT(DATETIME, @CWH_FromDate, 103),   -- dd/MM/yyyy
            TRY_CONVERT(DATETIME, @CWH_FromDate, 110),   -- mm-dd-yyyy
            TRY_CONVERT(DATETIME, @CWH_FromDate, 101),   -- MM/dd/yyyy
            TRY_CONVERT(DATETIME, @CWH_FromDate, 112),   -- yyyyMMdd
            TRY_CONVERT(DATETIME, @CWH_FromDate)         -- auto-detect
        )

    IF @ToDate IS NULL
        SET @ToDate = COALESCE(
            TRY_CONVERT(DATETIME, @CWH_ToDate, 106),
            TRY_CONVERT(DATETIME, @CWH_ToDate, 103),
            TRY_CONVERT(DATETIME, @CWH_ToDate, 110),
            TRY_CONVERT(DATETIME, @CWH_ToDate, 101),
            TRY_CONVERT(DATETIME, @CWH_ToDate, 112),
            TRY_CONVERT(DATETIME, @CWH_ToDate)
        )

    -- Normalize: FromDate = start of day, ToDate = end of day
    IF @FromDate IS NOT NULL
        SET @FromDate = CONVERT(DATETIME, CONVERT(DATE, @FromDate))

    IF @ToDate IS NOT NULL
        SET @ToDate = DATEADD(SECOND, 86399, CONVERT(DATETIME, CONVERT(DATE, @ToDate)))  -- 23:59:59

    -- ─── Step 4: Build Display Format string for Dynamic SQL OUTPUT ──────────
    -- This controls how dates are shown in SELECT results
    -- Mapped to CONVERT(VARCHAR, dateCol, style)
    DECLARE @DisplayStyle INT
    SET @DisplayStyle = CASE UPPER(@DateFormat)
                            WHEN 'DD-MMM-YYYY' THEN 106
                            WHEN 'DD/MM/YYYY'  THEN 103
                            WHEN 'MM-DD-YYYY'  THEN 110
                            ELSE 101
                        END

    -- ─── Temp Tables ────────────────────────────────────────────────────────
    CREATE TABLE #EmpLevel    (EmployeeId INT, LevelId INT,    EffectiveDate DATETIME)
    CREATE TABLE #EmpType     (EmployeeId INT, TypeId INT,     EffectiveDate DATETIME)
    CREATE TABLE #EmpCategory (EmployeeId INT, CategoryId INT, EffectiveDate DATETIME)
    CREATE TABLE #LevelRights (LevelId INT)
    CREATE TABLE #EmployeeIds (EmpAutoId INT)

    -- ─── Level Rights ────────────────────────────────────────────────────────
    INSERT INTO #LevelRights
        SELECT * FROM [dbo].[fn_TA_GetParentChild](@UserGroupName, @ParentID) AS LevelId

    DECLARE @isReportingto AS INT
    SELECT @isReportingto = ReportsBasedOn
    FROM   TA_Tbl_Usergroup
    WHERE  groupName = @UserGroupName

    INSERT INTO #EmployeeIds
        SELECT * FROM [dbo].[fn_TA_GetMappedEmployees_ReportingTo](@Employee_ID)   AS EmpAutoId
    INSERT INTO #EmployeeIds
        SELECT * FROM [dbo].[fn_TA_GetMappedEmployees_SancAuthority](@Employee_ID) AS EmpAutoId

    -- ─── Indexes ─────────────────────────────────────────────────────────────
    CREATE NONCLUSTERED INDEX [IDX_EmpLevel_Effective]   ON [dbo].[#EmpLevel]    ([EffectiveDate] ASC) ON [PRIMARY]
    CREATE NONCLUSTERED INDEX [IDX_EmpLevel_LevelId]     ON [dbo].[#EmpLevel]    ([LevelId]       ASC) ON [PRIMARY]
    CREATE NONCLUSTERED INDEX [IDX_EmpLevel_EmployeeId]  ON [dbo].[#EmpLevel]    ([EmployeeId]    ASC) ON [PRIMARY]
    CREATE NONCLUSTERED INDEX [IDX_EmpType_Effective]    ON [dbo].[#EmpType]     ([EffectiveDate] ASC) ON [PRIMARY]
    CREATE NONCLUSTERED INDEX [IDX_EmpType_TypeId]       ON [dbo].[#EmpType]     ([TypeId]        ASC) ON [PRIMARY]
    CREATE NONCLUSTERED INDEX [IDX_EmpType_EmployeeId]   ON [dbo].[#EmpType]     ([EmployeeId]    ASC) ON [PRIMARY]
    CREATE NONCLUSTERED INDEX [IDX_EmpCat_Effective]     ON [dbo].[#EmpCategory] ([EffectiveDate] ASC) ON [PRIMARY]
    CREATE NONCLUSTERED INDEX [IDX_EmpCat_CategoryId]    ON [dbo].[#EmpCategory] ([CategoryId]    ASC) ON [PRIMARY]
    CREATE NONCLUSTERED INDEX [IDX_EmpCat_EmployeeId]    ON [dbo].[#EmpCategory] ([EmployeeId]    ASC) ON [PRIMARY]

    -- ─── Populate Temp Tables ────────────────────────────────────────────────
    DECLARE @Today DATETIME = CONVERT(DATETIME, CONVERT(DATE, GETDATE()))

    INSERT INTO #EmpLevel
        SELECT ELevel.EmployeeId, EL.LevelId, ELevel.EffectiveDate
        FROM (
            SELECT MAX(el.EffectiveDate) AS EffectiveDate, el.EmployeeId
            FROM   TA_Tbl_EmployeeLevel el
            INNER JOIN TA_Tbl_Employee  emp ON el.EmployeeId = emp.Employee_Id
            WHERE  el.EmployeeId != @Employee_ID AND emp.Isdeleted = 0
            GROUP BY el.EmployeeId
        ) ELevel
        INNER JOIN TA_Tbl_EmployeeLevel EL
            ON  EL.EffectiveDate = ELevel.EffectiveDate
            AND EL.EmployeeId    = ELevel.EmployeeId
            AND EL.EffectiveDate <= @Today
            AND EL.LevelId IS NOT NULL


    INSERT INTO #EmpType
        SELECT EmpType.EmployeeId, EType.TypeId, EmpType.EffectiveDate
        FROM (
            SELECT MAX(et.EffectiveDate) AS EffectiveDate, et.EmployeeId
            FROM   TA_Tbl_EmployeeType et
            INNER JOIN TA_Tbl_Employee emp ON et.EmployeeId = emp.Employee_Id
            WHERE  emp.Isdeleted = 0
            GROUP BY et.EmployeeId
        ) EmpType
        INNER JOIN TA_Tbl_EmployeeType EType
            ON  EType.EffectiveDate = EmpType.EffectiveDate
            AND EType.EmployeeId    = EmpType.EmployeeId
            AND EType.EffectiveDate <= @Today
            AND EType.TypeId IS NOT NULL

    INSERT INTO #EmpCategory
        SELECT EmpCategory.EmployeeId, ECategory.CategoryId, EmpCategory.EffectiveDate
        FROM (
            SELECT MAX(ec.EffectiveDate) AS EffectiveDate, ec.EmployeeId
            FROM   TA_Tbl_EmployeeCategory ec
            INNER JOIN TA_Tbl_Employee emp ON ec.EmployeeId = emp.Employee_Id
            WHERE  emp.Isdeleted = 0
            GROUP BY ec.EmployeeId
        ) EmpCategory
        INNER JOIN TA_Tbl_EmployeeCategory ECategory
            ON  ECategory.EffectiveDate = EmpCategory.EffectiveDate
            AND ECategory.EmployeeId    = EmpCategory.EmployeeId
            AND ECategory.EffectiveDate <= @Today
            AND ECategory.CategoryId IS NOT NULL

    -- ─── Dynamic SQL ─────────────────────────────────────────────────────────
    DECLARE @sqlQuery AS NVARCHAR(MAX) = N''
    -- Inject the display style into the SELECT so output dates respect the
    -- active format from TA_Tbl_DateandTimeFormat
    SET @sqlQuery = @sqlQuery +
        N'SELECT ' +
        N'    CWH.CWH_App_Id, ' +
        N'    ld.name                                                                         AS Department, ' +
        N'    E.Employee_Code_Id                                                              AS EmployeeID, ' +
        N'    (E.Employee_FirstName + '' '' + ISNULL(E.Employee_LastName, ''''))              AS Employee_Name, ' +
        N'    CONVERT(VARCHAR, CWH.ApplicationDate, ' + CAST(@DisplayStyle AS NVARCHAR(3)) + N')  AS ApplicationDate, ' +
        N'    CONVERT(VARCHAR, CWH.FromDate,         ' + CAST(@DisplayStyle AS NVARCHAR(3)) + N')  AS FromDate, ' +
        N'    CONVERT(VARCHAR, CWH.ToDate,            ' + CAST(@DisplayStyle AS NVARCHAR(3)) + N')  AS ToDate, ' +
        N'    CWH.CompressedWorkHour, ' +
        N'    CWH.Reason, ' +
        N'    CWH.Status, ' +
        N'    ''0''                                                                           AS SelectedRecords ' +
        N'FROM  TA_Tbl_Employee E '

    SET @sqlQuery = @sqlQuery + N' INNER JOIN TA_CompressedWorkHoursApplication CWH  ON  E.Employee_Code_Id      = CWH.Employee_Id '
    SET @sqlQuery = @sqlQuery + N' INNER JOIN #EmpLevel                               ON  #EmpLevel.EmployeeId    = E.Employee_Id '
    SET @sqlQuery = @sqlQuery + N' INNER JOIN #EmpType                                ON  #EmpType.EmployeeId     = E.Employee_Id '
    SET @sqlQuery = @sqlQuery + N' INNER JOIN #EmpCategory                            ON  #EmpCategory.EmployeeId = E.Employee_Id '
    SET @sqlQuery = @sqlQuery + N' INNER JOIN #LevelRights                            ON  #LevelRights.LevelId    = #EmpLevel.LevelId '
    SET @sqlQuery = @sqlQuery + N' INNER JOIN Ta_tbl_leveldetails ld                  ON  #EmpLevel.LevelId       = ld.id '
    SET @sqlQuery = @sqlQuery + N' INNER JOIN TA_Tbl_YearSetting  ys                  ON  ys.YearSettings_Id      = CWH.YearSettings_Id '

    SET @sqlQuery = @sqlQuery + N' WHERE E.Isdeleted = 0 AND CWH.IsDeleted = 0 AND ys.IsActive = 1 '

    -- ─── Filter: Employee Name ────────────────────────────────────────────────
    IF LTRIM(RTRIM(ISNULL(@EmployeeName, ''))) <> ''
    BEGIN
        SET @sqlQuery = @sqlQuery +
            N' AND (E.Employee_FirstName + '' '' + ISNULL(E.Employee_LastName, '''') LIKE ''' +
            CAST(LTRIM(RTRIM(@EmployeeName)) + '%' AS NVARCHAR(100)) + N''')'
    END

    -- ─── Filter: Reporting-To ─────────────────────────────────────────────────
    IF (@isReportingto > 0)
    BEGIN
        SET @sqlQuery = @sqlQuery + N' AND (E.Employee_ID IN (SELECT * FROM #EmployeeIds)) '
    END

    -- ─── Filter: Swipe Card ───────────────────────────────────────────────────
    IF LTRIM(RTRIM(ISNULL(@EmpSwipeCard, ''))) <> ''
    BEGIN
        SET @sqlQuery = @sqlQuery + N' AND E.SwipeCardNo = ''' + @EmpSwipeCard + N''' '
    END

    -- ─── Filter: Category ────────────────────────────────────────────────────
    IF (@EmployeeCategory <> 0)
    BEGIN
        SET @sqlQuery = @sqlQuery + N' AND #EmpCategory.CategoryId = ' + CAST(@EmployeeCategory AS NVARCHAR(8))
    END

    -- ─── Filter: Type ────────────────────────────────────────────────────────
    IF (@EmployeeType <> 0)
    BEGIN
        SET @sqlQuery = @sqlQuery + N' AND #EmpType.TypeId = ' + CAST(@EmployeeType AS NVARCHAR(8))
    END

    -- ─── Filter: Employee Code ───────────────────────────────────────────────
    IF LTRIM(RTRIM(ISNULL(@EmployeeId, ''))) <> ''
    BEGIN
        SET @sqlQuery = @sqlQuery + N' AND E.Employee_Code_Id = ''' + CAST(LTRIM(RTRIM(@EmployeeId)) AS NVARCHAR(50)) + N''' '
    END

    -- ─── Filter: Status ──────────────────────────────────────────────────────
    IF NOT (@Status IS NULL OR LTRIM(RTRIM(@Status)) = '' OR @Status = '0' OR UPPER(@Status) = 'ALL')
    BEGIN
        SET @sqlQuery = @sqlQuery + N' AND (CWH.Status IN (''' + @Status + N'''))'
    END

    -- ─── Filter: Date Range ───────────────────────────────────────────────────
    -- Dates are passed as typed DATETIME parameters via sp_executesql,
    -- completely bypassing any format mismatch issue
    --IF @FromDate IS NOT NULL AND @ToDate IS NOT NULL
    --BEGIN
    --    SET @sqlQuery = @sqlQuery +
    --        N' AND (' +
    --        N'    (ISNULL(CWH.FromDate, ''19000101'') BETWEEN @p_FromDate AND @p_ToDate)' +
    --        N'    OR' +
    --        N'    (ISNULL(CWH.ToDate,   ''19000101'') BETWEEN @p_FromDate AND @p_ToDate)' +
    --        N' ) '
    --END

    SET @sqlQuery = @sqlQuery + N' ORDER BY CWH.ApplicationDate DESC '

    -- ─── Execute with parameterized dates ────────────────────────────────────
    EXEC sp_executesql
        @sqlQuery,
        N'@p_FromDate DATETIME, @p_ToDate DATETIME',
        @p_FromDate = @FromDate,
        @p_ToDate   = @ToDate

    -- ─── Cleanup ──────────────────────────────────────────────────────────────
    DROP TABLE #EmpLevel
    DROP TABLE #EmpType
    DROP TABLE #EmpCategory
    DROP TABLE #LevelRights
    DROP TABLE #EmployeeIds

END
--------- Script 3---------------
USE [TawazunCusIII]
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_SelectCompressedWorkHoursApplicationByEmpId]    Script Date: 3/24/2026 11:13:09 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- exec Proc_TA_SelectCompressedWorkHoursApplicationByEmpId @EmployeeCodeId='TT9996',@EmpFirstName=NULL,@EmpCodeId=NULL,@StartDate='01/01/1900',@EndDate='01/01/1900',@UserGroupName='IndividualUsers',@UserLevels='1',@GradeLevel='4',@EmpAutoId='5',@ParentId=0

ALTER PROCEDURE [dbo].[Proc_TA_SelectCompressedWorkHoursApplicationByEmpId]
    @EmployeeCodeId VARCHAR(50)  = NULL,
    @EmpFirstName   VARCHAR(100) = NULL,
    @EmpCodeId      VARCHAR(50)  = NULL,
    @StartDate      VARCHAR(20)  = '01/01/1900',
    @EndDate        VARCHAR(20)  = '01/01/1900',
    @UserGroupName  VARCHAR(50)  = NULL,
    @UserLevels     VARCHAR(500) = NULL,
    @GradeLevel     VARCHAR(500) = NULL,
    @EmpAutoId      VARCHAR(20)  = NULL,
    @ParentId       INT          = 0
AS
BEGIN
    SET NOCOUNT ON;

    -- ── Date / Time format ────────────────────────────────────────────────────
    DECLARE @dateFormat AS INT
    DECLARE @TimeFormat AS NVARCHAR(25)
    SELECT  @dateFormat = dateformat,
            @TimeFormat = timeformat
    FROM    Func_TA_GetDateTimeFormat()

    -- ── Dynamic SQL ───────────────────────────────────────────────────────────
    DECLARE @SqlQuery  AS VARCHAR(MAX) = ''
    DECLARE @SqlQuery2 AS VARCHAR(MAX) = ''

    -- SELECT columns
    SET @SqlQuery = @SqlQuery +
        'SELECT
             c.CWH_App_Id,
             c.Employee_Id AS EmployeeID,
             ISNULL(e.Employee_FirstName, '''') + '' '' + ISNULL(e.Employee_LastName, '''') AS Employee_Name,
             case when ' + CAST(@dateFormat AS VARCHAR(100)) + ' = 106 then
                  REPLACE(CONVERT(VARCHAR, c.ApplicationDate, ' + CAST(@dateFormat AS VARCHAR(100)) + '), '' '', ''-'')
                  else CONVERT(VARCHAR, c.ApplicationDate, ' + CAST(@dateFormat AS VARCHAR(100)) + ')
             end
             --+ '' '' + LEFT(CONVERT(VARCHAR(8), c.ApplicationDate, 108), 5)
             --+ '' '' + CASE WHEN DATEPART(HOUR, c.ApplicationDate) >= 12 THEN ''PM'' ELSE ''AM'' END
                                                                          AS ApplicationDate,
             case when ' + CAST(@dateFormat AS VARCHAR(100)) + ' = 106 then
                  REPLACE(CONVERT(VARCHAR, c.FromDate, ' + CAST(@dateFormat AS VARCHAR(100)) + '), '' '', ''-'')
                  else CONVERT(VARCHAR, c.FromDate, ' + CAST(@dateFormat AS VARCHAR(100)) + ')
             end                                                          AS FromDate,
             case when ' + CAST(@dateFormat AS VARCHAR(100)) + ' = 106 then
                  REPLACE(CONVERT(VARCHAR, c.ToDate, ' + CAST(@dateFormat AS VARCHAR(100)) + '), '' '', ''-'')
                  else CONVERT(VARCHAR, c.ToDate, ' + CAST(@dateFormat AS VARCHAR(100)) + ')
             end                                                          AS ToDate,
             c.Status,
             c.Reason,
             e.Employee_Id AS EmpID '

    -- FROM + JOINs
    SET @SqlQuery = @SqlQuery + 'FROM TA_CompressedWorkHoursApplication c '
    SET @SqlQuery = @SqlQuery + 'INNER JOIN TA_Tbl_Employee e ON e.Employee_Code_Id = c.Employee_Id '
    SET @SqlQuery = @SqlQuery + 'AND c.IsDeleted = 0 AND e.IsDeleted = 0 '

    -- Base WHERE
    SET @SqlQuery = @SqlQuery +
        'WHERE e.Employee_Code_Id = c.Employee_Id
           AND e.Employee_Status = 1 '

    -- ── Conditional filters → @SqlQuery2 ─────────────────────────────────────

    -- Filter: Employee Code Id (primary filter for ByEmpId)
    IF NOT (@EmployeeCodeId = '' OR @EmployeeCodeId IS NULL)
        SET @SqlQuery2 = @SqlQuery2 + ' AND c.Employee_Id = ''' + CAST(@EmployeeCodeId AS VARCHAR(50)) + ''''

    -- Filter: Employee Code (secondary, same as All version)
    IF NOT (@EmpCodeId = '' OR @EmpCodeId IS NULL)
        SET @SqlQuery2 = @SqlQuery2 + ' AND e.Employee_Code_Id = ''' + CAST(@EmpCodeId AS VARCHAR(50)) + ''''

    -- Filter: Employee Name
    IF NOT (@EmpFirstName = '' OR @EmpFirstName IS NULL)
    BEGIN
        DECLARE @Tempname NVARCHAR(200)
        SET @Tempname = '(e.Employee_FirstName + '' '' + ISNULL(e.Employee_LastName, ''''))'
        SET @SqlQuery2 = @SqlQuery2 + ' AND ' + @Tempname + ' = ''' + CAST(@EmpFirstName AS VARCHAR(100)) + ''''
    END

    -- Filter: Date range on ApplicationDate (ByEmpId style — open/close boundary)
    IF NOT (@StartDate = '01/01/1900' AND @EndDate = '01/01/1900')
    BEGIN
        IF NOT (@StartDate = '01/01/1900' OR @StartDate IS NULL)
            SET @SqlQuery2 = @SqlQuery2 +
                ' AND c.ApplicationDate >= CONVERT(DATE, ''' + @StartDate + ''', 103) '

        IF NOT (@EndDate = '01/01/1900' OR @EndDate IS NULL)
            SET @SqlQuery2 = @SqlQuery2 +
                ' AND c.ApplicationDate < DATEADD(DAY, 1, CONVERT(DATE, ''' + @EndDate + ''', 103)) '
    END

    SET @SqlQuery2 = @SqlQuery2 + ' ORDER BY c.CWH_App_Id DESC'

    -- ── Execute ───────────────────────────────────────────────────────────────
    EXEC (@SqlQuery + @SqlQuery2)

END
---- Script4-------

USE [TawazunCusIII]
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_GetCWHSanctionDetails]    Script Date: 3/24/2026 11:16:51 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ============================================================
-- Proc_TA_GetCWHSanctionDetails
-- Sanction authority view — mirrors Proc_TA_GetManualEntrySanctionDetails
-- structure with temp tables, indexes, dynamic SQL, Level/Type/Category
-- filters, ReportingTo + SancAuthority mapping, and SuperAdmin support.
-- ============================================================
ALTER PROCEDURE [dbo].[Proc_TA_GetCWHSanctionDetails]
    @Employee_ID        INT,
    @LevelId            VARCHAR(2000),
    @Status             VARCHAR(20)     = NULL,
    @EmployeeId         VARCHAR(100),
    @EmployeeName       VARCHAR(50),
    @EmployeeCategory   INT,
    @EmployeeType       INT,
    @CWH_FromDate       VARCHAR(20)     = NULL,
    @CWH_ToDate         VARCHAR(20)     = NULL,
    @Isdeleted          BIT             = 0,
    @EmpSwipeCard       VARCHAR(20),
    @UserGroupName      VARCHAR(250),
    @ParentID           INT
AS
BEGIN
    SET NOCOUNT ON;

    -- ─── Temp Tables ────────────────────────────────────────────────────────
    CREATE TABLE #EmpLevel    (EmployeeId INT, LevelId INT,    EffectiveDate DATETIME)
    CREATE TABLE #EmpType     (EmployeeId INT, TypeId INT,     EffectiveDate DATETIME)
    CREATE TABLE #EmpCategory (EmployeeId INT, CategoryId INT, EffectiveDate DATETIME)
    CREATE TABLE #LevelRights (LevelId INT)
    CREATE TABLE #EmployeeIds (EmpAutoId INT)

    -- ─── Level Rights ────────────────────────────────────────────────────────
    INSERT INTO #LevelRights
        SELECT * FROM [dbo].[fn_TA_GetParentChild](@UserGroupName, @ParentID) AS LevelId

    -- ─── Reporting-To flag ───────────────────────────────────────────────────
    DECLARE @isReportingto AS INT
    SELECT @isReportingto = ReportsBasedOn
    FROM   TA_Tbl_Usergroup
    WHERE  groupName = @UserGroupName

    -- ─── Mapped Employees (Reporting-To + Sanction Authority) ───────────────
    INSERT INTO #EmployeeIds
        SELECT * FROM [dbo].[fn_TA_GetMappedEmployees_ReportingTo](@Employee_ID)  AS EmpAutoId
    INSERT INTO #EmployeeIds
        SELECT * FROM [dbo].[fn_TA_GetMappedEmployees_SancAuthority](@Employee_ID) AS EmpAutoId

    -- ─── #EmpLevel Indexes ───────────────────────────────────────────────────
    CREATE NONCLUSTERED INDEX [IDX_EmpLevel_Effective]   ON [dbo].[#EmpLevel] ([EffectiveDate] ASC) ON [PRIMARY]
    CREATE NONCLUSTERED INDEX [IDX_EmpLevel_LevelId]     ON [dbo].[#EmpLevel] ([LevelId]       ASC) ON [PRIMARY]
    CREATE NONCLUSTERED INDEX [IDX_EmpLevel_EmployeeId]  ON [dbo].[#EmpLevel] ([EmployeeId]    ASC) ON [PRIMARY]

    -- ─── #EmpType Indexes ────────────────────────────────────────────────────
    CREATE NONCLUSTERED INDEX [IDX_EmpType_Effective]    ON [dbo].[#EmpType]  ([EffectiveDate] ASC) ON [PRIMARY]
    CREATE NONCLUSTERED INDEX [IDX_EmpType_TypeId]       ON [dbo].[#EmpType]  ([TypeId]        ASC) ON [PRIMARY]
    CREATE NONCLUSTERED INDEX [IDX_EmpType_EmployeeId]   ON [dbo].[#EmpType]  ([EmployeeId]    ASC) ON [PRIMARY]

    -- ─── #EmpCategory Indexes ────────────────────────────────────────────────
    CREATE NONCLUSTERED INDEX [IDX_EmpCat_Effective]     ON [dbo].[#EmpCategory] ([EffectiveDate] ASC) ON [PRIMARY]
    CREATE NONCLUSTERED INDEX [IDX_EmpCat_CategoryId]    ON [dbo].[#EmpCategory] ([CategoryId]    ASC) ON [PRIMARY]
    CREATE NONCLUSTERED INDEX [IDX_EmpCat_EmployeeId]    ON [dbo].[#EmpCategory] ([EmployeeId]    ASC) ON [PRIMARY]

    -- ─── Populate #EmpLevel ──────────────────────────────────────────────────
    INSERT INTO #EmpLevel
        SELECT ELevel.EmployeeId, EL.LevelId, ELevel.EffectiveDate
        FROM (
            SELECT MAX(el.EffectiveDate) AS EffectiveDate, el.EmployeeId
            FROM   TA_Tbl_EmployeeLevel el
            INNER JOIN TA_Tbl_Employee  emp ON el.EmployeeId = emp.Employee_Id
            WHERE  el.EmployeeId != @Employee_ID
              AND  emp.Isdeleted  = 0
            GROUP BY el.EmployeeId
        ) ELevel
        INNER JOIN TA_Tbl_EmployeeLevel EL
            ON  EL.EffectiveDate = ELevel.EffectiveDate
            AND EL.EmployeeId    = ELevel.EmployeeId
            AND EL.EffectiveDate <= CONVERT(DATETIME, CAST(GETDATE() AS NVARCHAR(50)), 102)
            AND EL.LevelId IS NOT NULL

    -- ─── Populate #EmpType ───────────────────────────────────────────────────
    INSERT INTO #EmpType
        SELECT EmpType.EmployeeId, EType.TypeId, EmpType.EffectiveDate
        FROM (
            SELECT MAX(et.EffectiveDate) AS EffectiveDate, et.EmployeeId
            FROM   TA_Tbl_EmployeeType et
            INNER JOIN TA_Tbl_Employee emp ON et.EmployeeId = emp.Employee_Id
            WHERE  emp.Isdeleted = 0
            GROUP BY et.EmployeeId
        ) EmpType
        INNER JOIN TA_Tbl_EmployeeType EType
            ON  EType.EffectiveDate = EmpType.EffectiveDate
            AND EType.EmployeeId    = EmpType.EmployeeId
            AND EType.EffectiveDate <= CONVERT(DATETIME, CAST(GETDATE() AS NVARCHAR(50)), 102)
            AND EType.TypeId IS NOT NULL

    -- ─── Populate #EmpCategory ───────────────────────────────────────────────
    INSERT INTO #EmpCategory
        SELECT EmpCategory.EmployeeId, ECategory.CategoryId, EmpCategory.EffectiveDate
        FROM (
            SELECT MAX(ec.EffectiveDate) AS EffectiveDate, ec.EmployeeId
            FROM   TA_Tbl_EmployeeCategory ec
            INNER JOIN TA_Tbl_Employee emp ON ec.EmployeeId = emp.Employee_Id
            WHERE  emp.Isdeleted = 0
            GROUP BY ec.EmployeeId
        ) EmpCategory
        INNER JOIN TA_Tbl_EmployeeCategory ECategory
            ON  ECategory.EffectiveDate = EmpCategory.EffectiveDate
            AND ECategory.EmployeeId    = EmpCategory.EmployeeId
            AND ECategory.EffectiveDate <= CONVERT(DATETIME, CAST(GETDATE() AS NVARCHAR(50)), 102)
            AND ECategory.CategoryId IS NOT NULL

    -- ─── Dynamic SQL ─────────────────────────────────────────────────────────
    DECLARE @sqlQuery AS VARCHAR(8000)
    SET @sqlQuery = ''

    -- SELECT columns — mirrors ManualEntry structure; adds CWH-specific columns
    SET @sqlQuery = @sqlQuery +
        'SELECT ' +
        '    CWH.CWH_App_Id, ' +
        '    ld.name                                                                         AS Department, ' +
        '    E.Employee_Code_Id                                                              AS EmployeeID, ' +
        '    (E.Employee_FirstName + '' '' + ISNULL(E.Employee_LastName, ''''))              AS Employee_Name, ' +
        '    CONVERT(VARCHAR, CWH.ApplicationDate, 101)
                --+ '' '' + LEFT(CONVERT(VARCHAR(8), CWH.ApplicationDate, 108), 5)
                --+ '' '' + CASE WHEN DATEPART(HOUR, CWH.ApplicationDate) >= 12 THEN ''PM'' ELSE ''AM'' END
                                                                                             AS ApplicationDate, ' +
        '    CONVERT(VARCHAR, CWH.FromDate, 101)                                             AS FromDate, ' +
        '    CONVERT(VARCHAR, CWH.ToDate,   101)                                             AS ToDate, ' +
        '    CWH.CompressedWorkHour, ' +
        '    CWH.Reason, ' +
        '    CWH.Status, ' +
        '    ''0''                                                                           AS SelectedRecords ' +
        'FROM  TA_Tbl_Employee E '

    -- JOINs — same temp-table join chain as ManualEntrySanction SP
    SET @sqlQuery = @sqlQuery + ' INNER JOIN TA_CompressedWorkHoursApplication CWH  ON  E.Employee_Code_Id     = CWH.Employee_Id '
    SET @sqlQuery = @sqlQuery + ' INNER JOIN #EmpLevel                               ON  #EmpLevel.EmployeeId   = E.Employee_Id '
    SET @sqlQuery = @sqlQuery + ' INNER JOIN #EmpType                                ON  #EmpType.EmployeeId    = E.Employee_Id '
    SET @sqlQuery = @sqlQuery + ' INNER JOIN #EmpCategory                            ON  #EmpCategory.EmployeeId= E.Employee_Id '
    SET @sqlQuery = @sqlQuery + ' INNER JOIN #LevelRights                            ON  #LevelRights.LevelId   = #EmpLevel.LevelId '
    SET @sqlQuery = @sqlQuery + ' INNER JOIN Ta_tbl_leveldetails ld                  ON  #EmpLevel.LevelId      = ld.id '
    SET @sqlQuery = @sqlQuery + ' INNER JOIN TA_Tbl_YearSetting  ys                  ON  ys.YearSettings_Id     = CWH.YearSettings_Id '

    -- Base WHERE
    SET @sqlQuery = @sqlQuery + ' WHERE E.Isdeleted = 0 AND CWH.IsDeleted = 0 AND ys.IsActive = 1 '

    -- ── Employee Name filter ─────────────────────────────────────────────────
    IF NOT (@EmployeeName = '')
    BEGIN
        SET @sqlQuery = @sqlQuery +
            ' AND (E.Employee_FirstName + '' '' + ISNULL(E.Employee_LastName, '''') LIKE ''' +
            CAST(@EmployeeName + '%' AS NVARCHAR(100)) + ''')'
    END

    -- ── ReportingTo restriction ──────────────────────────────────────────────
    IF (@isReportingto > 0)
    BEGIN
        SET @sqlQuery = @sqlQuery + ' AND (E.Employee_ID IN (SELECT * FROM #EmployeeIds)) '
    END

    -- ── Swipe Card filter ────────────────────────────────────────────────────
    IF NOT (@EmpSwipeCard = '')
    BEGIN
        SET @sqlQuery = @sqlQuery + ' AND E.SwipeCardNo = ''' + @EmpSwipeCard + ''' '
    END

    -- ── Employee Category filter ─────────────────────────────────────────────
    IF (@EmployeeCategory <> 0)
    BEGIN
        SET @sqlQuery = @sqlQuery + ' AND #EmpCategory.CategoryId = ' + CAST(@EmployeeCategory AS NVARCHAR(8))
    END

    -- ── Employee Type filter ─────────────────────────────────────────────────
    IF (@EmployeeType <> 0)
    BEGIN
        SET @sqlQuery = @sqlQuery + ' AND #EmpType.TypeId = ' + CAST(@EmployeeType AS NVARCHAR(8))
    END

    -- ── Employee Code filter ─────────────────────────────────────────────────
    IF NOT (@EmployeeId = '')
    BEGIN
        SET @sqlQuery = @sqlQuery + ' AND E.Employee_Code_Id = ''' + CAST(@EmployeeId AS NVARCHAR(50)) + ''' '
    END

    -- ── Status filter ────────────────────────────────────────────────────────
    IF NOT (@Status IS NULL OR @Status = '' OR @Status = '0' OR @Status = 'All')
    BEGIN
        SET @sqlQuery = @sqlQuery + ' AND (CWH.Status IN (''' + @Status + '''))'
    END

    -- ── Date range filter — FromDate leg ────────────────────────────────────
    IF NOT (CAST(@CWH_FromDate AS NVARCHAR(11)) = '' OR CAST(@CWH_FromDate AS NVARCHAR(11)) = '0')
    BEGIN
        SET @sqlQuery = @sqlQuery +
            ' AND (( ISNULL(CWH.FromDate, ''' + CAST('1990/01/01' AS NVARCHAR(11)) + ''') BETWEEN ''' +
            CAST(@CWH_FromDate AS NVARCHAR(11)) + ''' AND ''' +
            CAST(@CWH_ToDate   AS NVARCHAR(11)) + ''') '
    END

    -- ── Date range filter — ToDate leg ──────────────────────────────────────
    IF NOT (CAST(@CWH_ToDate AS NVARCHAR(11)) = '' OR CAST(@CWH_ToDate AS NVARCHAR(11)) = '0')
    BEGIN
        SET @sqlQuery = @sqlQuery +
            ' OR  ( ISNULL(CWH.ToDate, ''' + CAST('1990/01/01' AS NVARCHAR(11)) + ''') BETWEEN ''' +
            CAST(@CWH_FromDate AS NVARCHAR(11)) + ''' AND ''' +
            CAST(@CWH_ToDate   AS NVARCHAR(11)) + ''')) '
    END

    -- ── ORDER BY ─────────────────────────────────────────────────────────────
    SET @sqlQuery = @sqlQuery + ' ORDER BY CWH.ApplicationDate DESC '

    EXEC(@sqlQuery)

    -- ─── Cleanup ─────────────────────────────────────────────────────────────
    DROP TABLE #EmpLevel
    DROP TABLE #EmpType
    DROP TABLE #EmpCategory
    DROP TABLE #LevelRights
    DROP TABLE #EmployeeIds

END

------ Script5----------

USE [TawazunCusIII]
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_ApproveCWHApplication]    Script Date: 3/24/2026 11:18:01 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Proc_TA_ApproveCWHApplication]
    @CWH_App_Id  INT,
    @ApprovedBy  INT
AS
BEGIN
    SET NOCOUNT ON;

    -- SRS: Approval allowed only on Monday (WEEKDAY=2) and Tuesday (WEEKDAY=3)
    IF DATEPART(WEEKDAY, GETDATE()) NOT IN (2, 3)
    BEGIN
        RAISERROR('Approval is allowed only on Monday and Tuesday as per company policy.', 16, 1)
        RETURN
    END

    UPDATE TA_CompressedWorkHoursApplication
    SET    Status = 'Approved'
    WHERE  CWH_App_Id = @CWH_App_Id
      AND  Status     = 'Pending'
      AND  IsDeleted  = 0
END
---------Script6------
USE [TawazunCusIII]
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_RejectCWHApplication]    Script Date: 3/24/2026 11:19:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Proc_TA_RejectCWHApplication]
    @CWH_App_Id  INT,
    @RejectedBy  INT
AS
BEGIN
    SET NOCOUNT ON;

    -- SRS: Rejection allowed only on Monday (WEEKDAY=2) and Tuesday (WEEKDAY=3)
    IF DATEPART(WEEKDAY, GETDATE()) NOT IN (2, 3)
    BEGIN
        RAISERROR('Rejection is allowed only on Monday and Tuesday as per company policy.', 16, 1)
        RETURN
    END

    UPDATE TA_CompressedWorkHoursApplication
    SET    Status = 'Rejected'
    WHERE  CWH_App_Id = @CWH_App_Id
      AND  Status     = 'Pending'
      AND  IsDeleted  = 0
END

------- Script7--------
USE [TawazunCusIII]
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_SelectCWHApplicationsForApproval]    Script Date: 3/24/2026 11:19:15 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- exec Proc_TA_SelectCWHApplicationsForApproval @EmpFirstName=NULL,@EmpCodeId=NULL,@StartDate='01/01/1900',@EndDate='01/01/1900',@UserGroupName='SuperAdmin',@UserLevels='1',@GradeLevel='0',@EmpAutoId='1',@ParentId=0,@StatusFilter='Pending'
ALTER PROCEDURE [dbo].[Proc_TA_SelectCWHApplicationsForApproval]
    @EmpFirstName   VARCHAR(100),
    @EmpCodeId      VARCHAR(50),
    @StartDate      VARCHAR(20),
    @EndDate        VARCHAR(20),
    @UserGroupName  VARCHAR(50),
    @UserLevels     VARCHAR(500),
    @GradeLevel     VARCHAR(500),
    @EmpAutoId      VARCHAR(20),
    @ParentId       INT,
    @StatusFilter   VARCHAR(20)     -- Pass 'Pending' for pending-only, '' for all
AS
BEGIN
    SET NOCOUNT ON;

    -- Resolve approver's employee code from EmpAutoId
    DECLARE @ApproverEmpCode VARCHAR(50)
    SELECT @ApproverEmpCode = Employee_Code_Id
    FROM   TA_Tbl_Employee
    WHERE  Employee_Id = @EmpAutoId

    SELECT
        c.CWH_App_Id,
        c.Employee_Id                                                             AS EmployeeID,
        ISNULL(e.Employee_FirstName,'') + ' ' + ISNULL(e.Employee_LastName,'')  AS Employee_Name,
        CONVERT(VARCHAR(10), c.ApplicationDate, 103)
            + ' ' + LEFT(CONVERT(VARCHAR(8), c.ApplicationDate, 108), 5)
            + ' ' + CASE WHEN DATEPART(HOUR, c.ApplicationDate) >= 12 THEN 'PM' ELSE 'AM' END
                                                                                  AS ApplicationDate,
        CONVERT(VARCHAR(10), c.FromDate, 103)                                     AS FromDate,
        CONVERT(VARCHAR(10), c.ToDate,   103)                                     AS ToDate,
        c.Status,
        e.Employee_Id                                                               AS EmpID
    FROM  TA_CompressedWorkHoursApplication c
    INNER JOIN TA_Tbl_Employee e ON c.Employee_Id = e.Employee_Code_Id
    WHERE c.IsDeleted = 0
      AND (@StatusFilter = '' OR c.Status = @StatusFilter)
      AND (@EmpFirstName = '' OR e.Employee_FirstName LIKE '%' + @EmpFirstName + '%')
      AND (@EmpCodeId    = '' OR c.Employee_Id LIKE '%' + @EmpCodeId + '%')
      AND (@StartDate    = '01/01/1900' OR c.ApplicationDate >= CONVERT(DATE, @StartDate, 103))
      AND (@EndDate      = '01/01/1900' OR c.ApplicationDate <  DATEADD(DAY, 1, CONVERT(DATE, @EndDate, 103)))
      AND
      (
          -- SuperAdmin sees all
          @UserGroupName = 'SuperAdmin'
          OR
          -- Line Manager sees employees who report to them
          e.Employee_Code_Id IN
          (
              SELECT emp.Employee_Code_Id
              FROM   TA_Tbl_Employee emp
              WHERE  emp.Leave_Sanc_Authority = @ApproverEmpCode
                 OR  emp.Reporting_To     = @ApproverEmpCode
          )
      )
    ORDER BY c.CWH_App_Id DESC
END

-------- Script8---------

USE [TawazunCusIII]
GO
/****** Object:  StoredProcedure [dbo].[usp_CWH_GetMinWeeklyCompressedHours]    Script Date: 3/24/2026 11:20:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_CWH_GetMinWeeklyCompressedHours]
    @CompanyId INT = 1      -- Pass from Session["CompanyId"] if multi-company
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP 1
        ISNULL(MinWeeklyCompressedHours, 0) AS MinWeeklyCompressedHours
    FROM
        TA_Tbl_ApprovalSettings
    WHERE
        CompanyId = @CompanyId

END
------ Script9---------
USE [TawazunCusIII]
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_DeleteCompressedWorkHoursApplication]    Script Date: 3/24/2026 11:22:04 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Proc_TA_DeleteCompressedWorkHoursApplication]
    @CWH_App_Id INT,
    @UserId     INT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE TA_CompressedWorkHoursApplication
    SET    IsDeleted = 1
    WHERE  CWH_App_Id = @CWH_App_Id
END
----------Script10-------
USE [TawazunCusIII]
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_InsertCompressedWorkHoursApplication]    Script Date: 3/24/2026 11:22:29 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Proc_TA_InsertCompressedWorkHoursApplication]
    @Employee_Id      VARCHAR(50),
    @ApplicationDate  DATETIME,
    @FromDate         VARCHAR(20),
    @ToDate           VARCHAR(20),
    @Status           VARCHAR(20),
    @CreatedBy        INT,
    @YearSettings_Id  VARCHAR(20),
    @Reason           NVARCHAR(250),
	@CompressedWorkHours float
AS
BEGIN
    SET NOCOUNT ON;

    -- Parse FromDate
    DECLARE @ParsedFromDate DATE = TRY_CONVERT(DATE, @FromDate, 103)
    IF @ParsedFromDate IS NULL SET @ParsedFromDate = TRY_CONVERT(DATE, @FromDate, 101)
    IF @ParsedFromDate IS NULL SET @ParsedFromDate = TRY_CONVERT(DATE, @FromDate, 120)

    -- Parse ToDate
    DECLARE @ParsedToDate DATE = TRY_CONVERT(DATE, @ToDate, 103)
    IF @ParsedToDate IS NULL SET @ParsedToDate = TRY_CONVERT(DATE, @ToDate, 101)
    IF @ParsedToDate IS NULL SET @ParsedToDate = TRY_CONVERT(DATE, @ToDate, 120)

    IF @ParsedFromDate IS NULL OR @ParsedToDate IS NULL
    BEGIN
        RAISERROR('Invalid date format for FromDate or ToDate. Expected dd/MM/yyyy.', 16, 1);
        RETURN;
    END

    -- ── DUPLICATE CHECK ──────────────────────────────────────────────────────
    -- Block if same employee already has an active (non-rejected, non-deleted)
    -- entry whose date range overlaps the requested period.
    IF EXISTS (
        SELECT 1
        FROM   TA_CompressedWorkHoursApplication
        WHERE  Employee_Id      = @Employee_Id
          AND  IsDeleted        = 0
          AND  Status          <> 'Rejected'
          AND  @ParsedFromDate <= ToDate        -- overlap condition
          AND  @ParsedToDate   >= FromDate
    )
    BEGIN
        -- Return -1 so the C# layer can show a user-friendly message
        SELECT -1 AS CWH_Id;
        RETURN;
    END
    -- ────────────────────────────────────────────────────────────────────────

    INSERT INTO TA_CompressedWorkHoursApplication
    (
        Employee_Id, ApplicationDate, FromDate, ToDate,
        Status, CreatedBy, CreatedDate, YearSettings_Id, IsDeleted, Reason,CompressedWorkHour
    )
    VALUES
    (
        @Employee_Id, @ApplicationDate, @ParsedFromDate, @ParsedToDate,
        @Status, @CreatedBy, GETDATE(), @YearSettings_Id, 0, @Reason,@CompressedWorkHours
    );

    SELECT SCOPE_IDENTITY() AS CWH_Id;
END
-----Script11----------
USE [TawazunCusIII]
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_SelectCompressedWorkHoursApplicationAll]    Script Date: 3/24/2026 11:22:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- exec Proc_TA_SelectCompressedWorkHoursApplicationAll @EmpFirstName=NULL,@EmpCodeId=NULL,@StartDate='1900-01-01 00:00:00',@EndDate='1900-01-01 00:00:00',@EmpSwipeCard=NULL,@UserGroupName=N'SuperAdmin',@UserLevels='1',@GradeLevel=N'0',@EmpAutoId='1',@ParentID=0
ALTER PROCEDURE [dbo].[Proc_TA_SelectCompressedWorkHoursApplicationAll]
    @EmpFirstName  NVARCHAR(100) = NULL,
    @EmpCodeId     NVARCHAR(50)  = NULL,
    @StartDate     DATETIME      = '01/01/1900',
    @EndDate       DATETIME      = '01/01/1900',
    @EmpSwipeCard  VARCHAR(100)  = NULL,
    @UserGroupName NVARCHAR(250) = NULL,
    @UserLevels    VARCHAR(8000) = NULL,
    @GradeLevel    NVARCHAR(20)  = NULL,
    @EmpAutoId     VARCHAR(10)   = NULL,
    @ParentID      INT           = 0
AS
BEGIN
    SET NOCOUNT ON;

    -- ── Level Rights ──────────────────────────────────────────────────────────
    CREATE TABLE #LevelRights (LevelId INT)
    CREATE NONCLUSTERED INDEX [IDX_LevelId]
        ON [dbo].[#LevelRights]([LevelId] ASC) ON [PRIMARY]

    INSERT INTO #LevelRights
        SELECT * FROM [dbo].[fn_TA_GetParentChild](@UserGroupName, @ParentID) AS LevelId

    -- ── Reporting-To flag ─────────────────────────────────────────────────────
    DECLARE @isReportingto AS INT
    SELECT @isReportingto = ReportsBasedOn
    FROM   TA_Tbl_Usergroup
    WHERE  groupName = @UserGroupName

    -- ── Mapped Employees (UNION to avoid duplicates) ──────────────────────────
    CREATE TABLE #EmployeeIds (EmpAutoId INT)

    INSERT INTO #EmployeeIds
        SELECT ParentChildId FROM [dbo].[fn_TA_GetMappedEmployees_ReportingTo](CAST(@EmpAutoId AS INT))
        UNION
        SELECT ParentChildId FROM [dbo].[fn_TA_GetMappedEmployees_SancAuthority](CAST(@EmpAutoId AS INT))

    -- ── Date / Time format ────────────────────────────────────────────────────
    DECLARE @dateFormat AS INT
    DECLARE @TimeFormat AS NVARCHAR(25)
    SELECT  @dateFormat = dateformat,
            @TimeFormat = timeformat
    FROM    Func_TA_GetDateTimeFormat()

    -- ── Dynamic SQL ───────────────────────────────────────────────────────────
    DECLARE @SqlQuery  AS VARCHAR(MAX) = ''
    DECLARE @SqlQuery2 AS VARCHAR(MAX) = ''

    -- SELECT columns
    SET @SqlQuery = @SqlQuery +
        'SELECT
             c.CWH_App_Id,
             c.Employee_Id  AS EmployeeID,
             ISNULL(e.Employee_FirstName, '''') + '' '' + ISNULL(e.Employee_LastName, '''') AS Employee_Name,
             CASE WHEN ' + CAST(@dateFormat AS VARCHAR(100)) + ' = 106 THEN
                  REPLACE(CONVERT(VARCHAR, c.ApplicationDate, ' + CAST(@dateFormat AS VARCHAR(100)) + '), '' '', ''-'')
                  ELSE CONVERT(VARCHAR, c.ApplicationDate, ' + CAST(@dateFormat AS VARCHAR(100)) + ')
             END                                                          AS ApplicationDate,
             CASE WHEN ' + CAST(@dateFormat AS VARCHAR(100)) + ' = 106 THEN
                  REPLACE(CONVERT(VARCHAR, c.FromDate, ' + CAST(@dateFormat AS VARCHAR(100)) + '), '' '', ''-'')
                  ELSE CONVERT(VARCHAR, c.FromDate, ' + CAST(@dateFormat AS VARCHAR(100)) + ')
             END                                                          AS FromDate,
             CASE WHEN ' + CAST(@dateFormat AS VARCHAR(100)) + ' = 106 THEN
                  REPLACE(CONVERT(VARCHAR, c.ToDate, ' + CAST(@dateFormat AS VARCHAR(100)) + '), '' '', ''-'')
                  ELSE CONVERT(VARCHAR, c.ToDate, ' + CAST(@dateFormat AS VARCHAR(100)) + ')
             END                                                          AS ToDate,
             c.Status,
			 C.CompressedWorkHour,
             c.Reason,
             e.Employee_Id AS EmpID '

    -- FROM
    SET @SqlQuery = @SqlQuery + 'FROM TA_CompressedWorkHoursApplication c '

    -- JOINs
    SET @SqlQuery = @SqlQuery + 'INNER JOIN TA_Tbl_YearSetting ys ON (ys.YearSettings_Id = c.YearSettings_Id AND ys.isActive = 1) '
    SET @SqlQuery = @SqlQuery + 'INNER JOIN TA_Tbl_Employee e ON e.Employee_Code_Id = c.Employee_Id AND c.IsDeleted = 0 AND e.IsDeleted = 0 '

    -- FIX #3: Use EXISTS for level check instead of JOIN to prevent row multiplication
    SET @SqlQuery = @SqlQuery +
        'INNER JOIN (
             SELECT DISTINCT el.EmployeeId
             FROM   TA_Tbl_EmployeeLevel el
             INNER JOIN Ta_tbl_leveldetails ld ON el.LevelId = ld.id
             INNER JOIN #LevelRights lr         ON lr.LevelId = ld.id
         ) AS LevelFilter ON LevelFilter.EmployeeId = e.Employee_Id '

    -- FIX #1: Move MAX(EffectiveDate) filter into the JOIN ON clause
    SET @SqlQuery = @SqlQuery +
        'LEFT OUTER JOIN TA_Tbl_EmployeeDesignation EDesig
             ON EDesig.EmployeeID = e.Employee_Id
             AND EDesig.EffectiveDate = (
                 SELECT MAX(EffectiveDate)
                 FROM   TA_Tbl_EmployeeDesignation
                 WHERE  EmployeeId = e.Employee_Id
             ) '

    SET @SqlQuery = @SqlQuery + 'LEFT OUTER JOIN TA_Tbl_Title T ON T.TitleId = EDesig.DesignationId '
    SET @SqlQuery = @SqlQuery + 'LEFT OUTER JOIN TA_Tbl_Grade ON TA_Tbl_Grade.Grade_ID = T.GradeId '

    -- Base WHERE (removed EDesig.EffectiveDate filter from here)
    SET @SqlQuery = @SqlQuery +
        'WHERE e.Employee_Code_Id = c.Employee_Id
           AND e.Employee_Status = 1 '

    -- ── Conditional filters → @SqlQuery2 ─────────────────────────────────────

    -- Filter: Employee Code
    IF NOT (@EmpCodeId = '' OR @EmpCodeId IS NULL)
        SET @SqlQuery2 = @SqlQuery2 + ' AND e.Employee_Code_Id = ''' + CAST(@EmpCodeId AS NVARCHAR(50)) + ''''

    -- Filter: Reporting-To
    IF (@isReportingto > 0)
        SET @SqlQuery2 = @SqlQuery2 + ' AND (e.Employee_ID IN (SELECT EmpAutoId FROM #EmployeeIds)) '

    -- Filter: Swipe Card
    IF NOT (@EmpSwipeCard = '' OR @EmpSwipeCard IS NULL)
        SET @SqlQuery2 = @SqlQuery2 + ' AND e.SwipeCardNo = ''' + @EmpSwipeCard + ''' '

    -- Filter: Employee Name
    IF NOT (@EmpFirstName = '' OR @EmpFirstName IS NULL)
    BEGIN
        DECLARE @Tempname NVARCHAR(200)
        SET @Tempname = '(e.Employee_FirstName + '' '' + ISNULL(e.Employee_LastName, ''''))'
        SET @SqlQuery2 = @SqlQuery2 + ' AND ' + @Tempname + ' = ''' + CAST(@EmpFirstName AS NVARCHAR(100)) + ''''
    END

    -- Filter: Date range on FromDate / ToDate
    IF NOT (@StartDate = '01/01/1900' AND @EndDate = '01/01/1900')
    BEGIN
        SET @SqlQuery2 = @SqlQuery2 +
            ' AND (((CONVERT(DATETIME, ''' + CAST(@StartDate AS NVARCHAR(50)) + ''', 102) BETWEEN c.FromDate AND c.ToDate)
                 OR (CONVERT(DATETIME, ''' + CAST(@EndDate   AS NVARCHAR(50)) + ''', 102) BETWEEN c.FromDate AND c.ToDate))
                 OR (c.FromDate BETWEEN CONVERT(DATETIME, ''' + CAST(@StartDate AS NVARCHAR(50)) + ''', 102) AND CONVERT(DATETIME, ''' + CAST(@EndDate AS NVARCHAR(50)) + ''', 102))
                 OR (c.ToDate   BETWEEN CONVERT(DATETIME, ''' + CAST(@StartDate AS NVARCHAR(50)) + ''', 102) AND CONVERT(DATETIME, ''' + CAST(@EndDate AS NVARCHAR(50)) + ''', 102)))'
    END

    -- ORDER BY
    SET @SqlQuery2 = @SqlQuery2 + ' ORDER BY c.CWH_App_Id DESC'

    -- ── Execute & cleanup ─────────────────────────────────────────────────────
    EXEC (@SqlQuery + @SqlQuery2)

    DROP TABLE #LevelRights
    DROP TABLE #EmployeeIds

END
------------- Script12--------
USE [TawazunCusIII]
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_SelectCompressedWorkHoursApplicationByEmpId]    Script Date: 3/24/2026 11:23:31 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- exec Proc_TA_SelectCompressedWorkHoursApplicationByEmpId @EmployeeCodeId='TT9996',@EmpFirstName=NULL,@EmpCodeId=NULL,@StartDate='01/01/1900',@EndDate='01/01/1900',@UserGroupName='IndividualUsers',@UserLevels='1',@GradeLevel='4',@EmpAutoId='5',@ParentId=0

ALTER PROCEDURE [dbo].[Proc_TA_SelectCompressedWorkHoursApplicationByEmpId]
    @EmployeeCodeId VARCHAR(50)  = NULL,
    @EmpFirstName   VARCHAR(100) = NULL,
    @EmpCodeId      VARCHAR(50)  = NULL,
    @StartDate      VARCHAR(20)  = '01/01/1900',
    @EndDate        VARCHAR(20)  = '01/01/1900',
    @UserGroupName  VARCHAR(50)  = NULL,
    @UserLevels     VARCHAR(500) = NULL,
    @GradeLevel     VARCHAR(500) = NULL,
    @EmpAutoId      VARCHAR(20)  = NULL,
    @ParentId       INT          = 0
AS
BEGIN
    SET NOCOUNT ON;

    -- ── Date / Time format ────────────────────────────────────────────────────
    DECLARE @dateFormat AS INT
    DECLARE @TimeFormat AS NVARCHAR(25)
    SELECT  @dateFormat = dateformat,
            @TimeFormat = timeformat
    FROM    Func_TA_GetDateTimeFormat()

    -- ── Dynamic SQL ───────────────────────────────────────────────────────────
    DECLARE @SqlQuery  AS VARCHAR(MAX) = ''
    DECLARE @SqlQuery2 AS VARCHAR(MAX) = ''

    -- SELECT columns
    SET @SqlQuery = @SqlQuery +
        'SELECT
             c.CWH_App_Id,
             c.Employee_Id AS EmployeeID,
             ISNULL(e.Employee_FirstName, '''') + '' '' + ISNULL(e.Employee_LastName, '''') AS Employee_Name,
             case when ' + CAST(@dateFormat AS VARCHAR(100)) + ' = 106 then
                  REPLACE(CONVERT(VARCHAR, c.ApplicationDate, ' + CAST(@dateFormat AS VARCHAR(100)) + '), '' '', ''-'')
                  else CONVERT(VARCHAR, c.ApplicationDate, ' + CAST(@dateFormat AS VARCHAR(100)) + ')
             end
             --+ '' '' + LEFT(CONVERT(VARCHAR(8), c.ApplicationDate, 108), 5)
             --+ '' '' + CASE WHEN DATEPART(HOUR, c.ApplicationDate) >= 12 THEN ''PM'' ELSE ''AM'' END
                                                                          AS ApplicationDate,
             case when ' + CAST(@dateFormat AS VARCHAR(100)) + ' = 106 then
                  REPLACE(CONVERT(VARCHAR, c.FromDate, ' + CAST(@dateFormat AS VARCHAR(100)) + '), '' '', ''-'')
                  else CONVERT(VARCHAR, c.FromDate, ' + CAST(@dateFormat AS VARCHAR(100)) + ')
             end                                                          AS FromDate,
             case when ' + CAST(@dateFormat AS VARCHAR(100)) + ' = 106 then
                  REPLACE(CONVERT(VARCHAR, c.ToDate, ' + CAST(@dateFormat AS VARCHAR(100)) + '), '' '', ''-'')
                  else CONVERT(VARCHAR, c.ToDate, ' + CAST(@dateFormat AS VARCHAR(100)) + ')
             end                                                          AS ToDate,
             c.Status,
             c.Reason,
             e.Employee_Id AS EmpID '

    -- FROM + JOINs
    SET @SqlQuery = @SqlQuery + 'FROM TA_CompressedWorkHoursApplication c '
    SET @SqlQuery = @SqlQuery + 'INNER JOIN TA_Tbl_Employee e ON e.Employee_Code_Id = c.Employee_Id '
    SET @SqlQuery = @SqlQuery + 'AND c.IsDeleted = 0 AND e.IsDeleted = 0 '

    -- Base WHERE
    SET @SqlQuery = @SqlQuery +
        'WHERE e.Employee_Code_Id = c.Employee_Id
           AND e.Employee_Status = 1 '

    -- ── Conditional filters → @SqlQuery2 ─────────────────────────────────────

    -- Filter: Employee Code Id (primary filter for ByEmpId)
    IF NOT (@EmployeeCodeId = '' OR @EmployeeCodeId IS NULL)
        SET @SqlQuery2 = @SqlQuery2 + ' AND c.Employee_Id = ''' + CAST(@EmployeeCodeId AS VARCHAR(50)) + ''''

    -- Filter: Employee Code (secondary, same as All version)
    IF NOT (@EmpCodeId = '' OR @EmpCodeId IS NULL)
        SET @SqlQuery2 = @SqlQuery2 + ' AND e.Employee_Code_Id = ''' + CAST(@EmpCodeId AS VARCHAR(50)) + ''''

    -- Filter: Employee Name
    IF NOT (@EmpFirstName = '' OR @EmpFirstName IS NULL)
    BEGIN
        DECLARE @Tempname NVARCHAR(200)
        SET @Tempname = '(e.Employee_FirstName + '' '' + ISNULL(e.Employee_LastName, ''''))'
        SET @SqlQuery2 = @SqlQuery2 + ' AND ' + @Tempname + ' = ''' + CAST(@EmpFirstName AS VARCHAR(100)) + ''''
    END

    -- Filter: Date range on ApplicationDate (ByEmpId style — open/close boundary)
    IF NOT (@StartDate = '01/01/1900' AND @EndDate = '01/01/1900')
    BEGIN
        IF NOT (@StartDate = '01/01/1900' OR @StartDate IS NULL)
            SET @SqlQuery2 = @SqlQuery2 +
                ' AND c.ApplicationDate >= CONVERT(DATE, ''' + @StartDate + ''', 103) '

        IF NOT (@EndDate = '01/01/1900' OR @EndDate IS NULL)
            SET @SqlQuery2 = @SqlQuery2 +
                ' AND c.ApplicationDate < DATEADD(DAY, 1, CONVERT(DATE, ''' + @EndDate + ''', 103)) '
    END

    SET @SqlQuery2 = @SqlQuery2 + ' ORDER BY c.CWH_App_Id DESC'

    -- ── Execute ───────────────────────────────────────────────────────────────
    EXEC (@SqlQuery + @SqlQuery2)

END

---- Script13------------

USE [TawazunCusIII]
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_SelectCompressedWorkHoursforMail]    Script Date: 3/24/2026 11:24:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	Select Compressed Work Hours details for mail trigger
-- Proc_TA_SelectCompressedWorkHoursforMail 'TT9998','20240801','','',''
-- exec Proc_TA_SelectCompressedWorkHoursforMail @CompressedWorkHours_EmployeeId=N'TT9998',@CompressedWorkHours_Date='2024-09-12 00:00:00',@ScheduleIn=N'',@ScheduleOut=N'',@Type=N''
-- =============================================
ALTER PROCEDURE [dbo].[Proc_TA_SelectCompressedWorkHoursforMail]  
	@CompressedWorkHours_EmployeeId  NVARCHAR(20)  = 'T001',
	@CompressedWorkHours_Date        DATETIME      = '2024',
	@ScheduleIn                      NVARCHAR(15),  
	@ScheduleOut                     NVARCHAR(15),
	@Type                            NVARCHAR(15)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @LevelID NVARCHAR(10)

	-- -------------------------------------------------------
	-- Branch 1: Fetch by CompressedWorkHours_Id (Type passed)
	-- -------------------------------------------------------
	IF (@Type != '' OR @Type != NULL)
	BEGIN
		SELECT @LevelID = levelId 
		FROM [dbo].[Func_TA_EmpLevel](GETDATE(), @CompressedWorkHours_EmployeeId)

		SELECT 
			CWH.Employee_Id,
			CWH.CWH_App_Id,
			EMP.EmailId,
			EMP.Employee_FirstName + ' ' + EMP.Employee_MiddleName + ' ' + EMP.Employee_LastName AS Employeename,
			[dbo].[Func_TA_GetParentIds](@LevelID)                                               AS ParentID,
			CWH.ApplicationDate,
			CWH.FromDate,
			CWH.ToDate,
			CWH.CompressedWorkHour,
			CWH.Status,
			CWH.Remarks
		FROM TA_CompressedWorkHoursApplication CWH
		INNER JOIN TA_Tbl_Employee EMP 
			ON EMP.Employee_Code_Id = CWH.Employee_Id
		WHERE CWH.CWH_App_Id = @Type
		  AND CWH.Isdeleted = 0
	END

	-- -------------------------------------------------------
	-- Branch 2: Fetch by Employee + Date + ScheduleIn/Out
	-- -------------------------------------------------------
	ELSE
	BEGIN
		SELECT @LevelID = levelId 
		FROM [dbo].[Func_TA_EmpLevel](GETDATE(), @CompressedWorkHours_EmployeeId)

		SELECT 
			CWH.Employee_Id,
			CWH.CWH_App_Id,
			EMP.EmailId,
			EMP.Employee_FirstName + ' ' + EMP.Employee_MiddleName + ' ' + EMP.Employee_LastName AS Employeename,
			[dbo].[Func_TA_GetParentIds](@LevelID)                                               AS ParentID,
			CWH.ApplicationDate,
			CWH.Fromdate,
			CWH.ToDate,
			CWH.CompressedWorkHour,
			CWH.Status,
			CWH.Remarks
		FROM TA_CompressedWorkHoursApplication CWH
		INNER JOIN TA_Tbl_Employee EMP 
			ON EMP.Employee_Code_Id = CWH.Employee_Id
		WHERE CWH.Employee_Id = @CompressedWorkHours_EmployeeId
		  AND CWH.ApplicationDate        = @CompressedWorkHours_Date
		  AND CWH.Isdeleted                       = 0
		  AND CWH.Status      != 'Deleted'
		  AND (CWH.FromDate = @ScheduleIn OR CWH.Todate = @ScheduleOut)
	END
END

-----Script14--------
USE [TawazunCusIII]
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_SendCompressedWorkHoursMail]    Script Date: 3/24/2026 11:25:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- exec Proc_TA_SendCompressedWorkHoursMail @MailSentTime='23:00:00',@InputDate='2026-03-19 00:00:00'

ALTER PROCEDURE [dbo].[Proc_TA_SendCompressedWorkHoursMail]
(
    @InputDate DATE,
    @MailSentTime TIME
)
AS
BEGIN
    SET NOCOUNT ON;
    SET DATEFIRST 1;

    DECLARE 
        @WeekDayName NVARCHAR(20),
        @WeekStartDate DATE,
        @DateFormat NVARCHAR(50),
        @TimeFormat NVARCHAR(50),
        @FullFormat NVARCHAR(100),
        @MailDateTime DATETIME;

    ------------------------------------------------------------
    -- Get Format from Table
    ------------------------------------------------------------
    SELECT TOP 1 
        @DateFormat = DateFormat,
        @TimeFormat = TimeFormat
    FROM TA_Tbl_DateandTimeFormat;

    SET @FullFormat = @DateFormat + ' ' + @TimeFormat;

    ------------------------------------------------------------
    -- Get weekday & week start
    ------------------------------------------------------------
    SELECT @WeekDayName = DATENAME(WEEKDAY, @InputDate);

    SELECT @WeekStartDate = DATEADD(DAY, 1 - DATEPART(WEEKDAY, @InputDate), @InputDate);

    ------------------------------------------------------------
    -- Mail Trigger DateTime (Next Day Handling)
    ------------------------------------------------------------
  --  SET @MailDateTime = 
  --      DATEADD(DAY, 1, CAST(@InputDate AS DATETIME)) 
  --      + CAST(@MailSentTime AS DATETIME);
		
		--Select @MailDateTime
    ------------------------------------------------------------
    -- Execute only on Thursday
    ------------------------------------------------------------
    IF (@WeekDayName = 'Thursday')
    BEGIN

        ------------------------------------------------------------
        -- Temp Table
        ------------------------------------------------------------
        DECLARE @TempTable TABLE 
        (
            Id INT IDENTITY(1,1),
			CID int,
            EmployeeId NVARCHAR(20),
            EmployeeName NVARCHAR(200),
            Fromdate DATETIME,
            Todate DATETIME,
            Status NVARCHAR(50),
            CompressedWorkHours FLOAT,
            ToMailId NVARCHAR(100),
            CC_MailId NVARCHAR(200),
            Shift_EndDateTime DATETIME,
            TriggerAfter10Hours DATETIME,
            ActualWorkedSeconds BIGINT,
            Reason NVARCHAR(250),
            Remarks NVARCHAR(250)
        );

        ------------------------------------------------------------
        -- Work Hours CTE
        ------------------------------------------------------------
        ;WITH WorkHoursCTE AS
        (
            SELECT 
                PD.Employee_Code_Id,
                SUM(DATEDIFF(SECOND, '00:00:00', CAST(PD.C_WH AS TIME))) AS TotalWorkedSeconds
            FROM TA_Tbl_ProcessedData PD
            WHERE 
                PD.Date BETWEEN @WeekStartDate AND @InputDate
                AND PD.Attendance = 'Present'
            GROUP BY PD.Employee_Code_Id
        )

        ------------------------------------------------------------
        -- Insert Data
        ------------------------------------------------------------
        INSERT INTO @TempTable
        (
            EmployeeId,
			CID,
            EmployeeName,
            Fromdate,
            Todate,
            Status,
            CompressedWorkHours,
            Reason,
            Remarks,
            ToMailId,
            CC_MailId,
            Shift_EndDateTime,
            TriggerAfter10Hours,
            ActualWorkedSeconds
        )
        SELECT 
            Emp.Employee_Code_Id,
			CA.CWH_App_Id,
            Emp.Employee_FirstName + ' ' + Emp.Employee_LastName,
            CA.FromDate,
            CA.ToDate,
            CA.Status,
            CA.CompressedWorkHour,
            CA.Reason,
            CA.Remarks,
            Emp.EmailId,

            CASE 
                WHEN Emp.Reporting_To = Emp.Leave_Sanc_Authority 
                THEN dbo.Func_TA_EmployeeGetMailID(Emp.Leave_Sanc_Authority)
                ELSE 
                    dbo.Func_TA_EmployeeGetMailID(Emp.Leave_Sanc_Authority) + ';' +
                    dbo.Func_TA_EmployeeGetMailID(Emp.Reporting_To)
            END,

            ------------------------------------------------------------
            -- Shift End DateTime (Handles Night Shift)
            ------------------------------------------------------------
    DATEADD(HOUR, 10,
                CAST(PD.Date AS DATETIME) + CAST(PD.Shift_StartTime AS DATETIME)
            ),
            ------------------------------------------------------------
            -- ✅ Trigger Time = Shift Start + 10 Hours
            ------------------------------------------------------------
            DATEADD(HOUR, 10,
                CAST(PD.Date AS DATETIME) + CAST(PD.Shift_StartTime AS DATETIME)
            ),

            WH.TotalWorkedSeconds

        FROM TA_CompressedWorkHoursApplication CA 
        INNER JOIN TA_Tbl_Employee Emp 
            ON Emp.Employee_Code_Id = CA.Employee_Id
        INNER JOIN WorkHoursCTE WH
            ON WH.Employee_Code_Id = Emp.SwipeCardNo
        INNER JOIN TA_Tbl_ProcessedData PD 
            ON PD.Employee_Code_Id = Emp.SwipeCardNo
            
        WHERE 
            CA.FromDate = @WeekStartDate 
            AND CA.Status = 'Approved' 
            AND CA.CancelledMailTrigger = 0
            AND (CA.CompressedWorkHour * 3600) > WH.TotalWorkedSeconds
            AND PD.Date = @InputDate;

        ------------------------------------------------------------
        -- Final Output
        ------------------------------------------------------------
        SELECT 
            EmployeeId,
			CID,
            EmployeeName,
            FORMAT(Fromdate, @FullFormat) AS FromDate,
            FORMAT(Todate, @FullFormat) AS Todate,
            Status,
           [dbo].[ConvertMinsToHours]((CompressedWorkHours * 3600)/60) As CompressedWorkHours,
           [dbo].[ConvertMinsToHours](ActualWorkedSeconds/60) AS ActualWorkedHours,
            Remarks,
            Reason,
            ToMailId,
            CC_MailId,
            FORMAT(GETDATE(), @FullFormat) AS StrRejectionDate,
            Shift_EndDateTime,
            TriggerAfter10Hours

        FROM @TempTable
        WHERE 
            TriggerAfter10Hours <= GETDATE();

    END
END
-------Script15--------
USE [TawazunCusIII]
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_UpdateCompressedWorkHoursApplicationSanction_Group]    Script Date: 3/24/2026 11:25:44 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
declare @p4 xml
set @p4=convert(xml,N'<DocumentElement><Table><CWH_App_Id>10</CWH_App_Id><Department>Corporate Support</Department><CompressedWorkHour>40</CompressedWorkHour></Table></DocumentElement>')
exec Proc_TA_UpdateCompressedWorkHoursApplicationSanction_Group @Status=N'Approved',@Remarks=N'test',@auEmpId=1,@xml=@p4


*/
ALTER PROCEDURE [dbo].[Proc_TA_UpdateCompressedWorkHoursApplicationSanction_Group] 
( 
	@Status NVARCHAR(250), 
	@Remarks NVARCHAR(250),
	@auEmpId INT,
	@xml xml 
)
AS 
BEGIN
SET NOCOUNT ON;
	DECLARE @Index int
	SET @Index = 0 
	EXEC sp_xml_preparedocument @Index OUTPUT, @xml
	DECLARE @UpdateCount int

	-- Updates the status and required work hours of the compressed work hours record if not yet processed
	UPDATE TA_CompressedWorkHoursApplication 
	SET 
		Status   = @Status,
		Remarks  = @Remarks,
		CompressedWorkHour            = xm.compressedWorkHour,
		SanctionEMP                       = CAST(@auEmpId AS NVARCHAR(20))
	FROM OpenXML(@Index, '/DocumentElement/Table')
	WITH 
	(
		CWH_App_Id  BIGINT       'CWH_App_Id',
		CompressedWorkHour       NVARCHAR(50) 'CompressedWorkHour'
	) AS xm
	INNER JOIN TA_CompressedWorkHoursApplication 
		ON xm.CWH_App_Id = TA_CompressedWorkHoursApplication.CWH_App_Id
	--WHERE Clk_Process = 'N'

	SET @UpdateCount = @@ROWCOUNT
	EXEC sp_xml_removedocument @Index

	SET @Index = 0 
	EXEC sp_xml_preparedocument @Index OUTPUT, @xml
	DECLARE @ProcessedDataCount INT 

	-- Selects the compressed work hours records which are already processed
	SET @ProcessedDataCount = 
	(
		SELECT COUNT(TA_CompressedWorkHoursApplication.CWH_App_Id)
		FROM OpenXML(@Index, '/DocumentElement/Table')
		WITH 
		(
			CWH_App_Id BIGINT 'CWH_App_Id'
		) AS xm
		INNER JOIN TA_CompressedWorkHoursApplication 
			ON xm.CWH_App_Id = TA_CompressedWorkHoursApplication.CWH_App_Id
		--WHERE Clk_Process = 'Y'
	)

	SELECT @UpdateCount AS UpdateCount, @ProcessedDataCount AS ProcessedDataCount
	EXEC sp_xml_removedocument @Index 
END


--Alter TAble TA_CompressedWorkHoursApplication Add Remarks Nvarchar(250),SanctionEMP int
--Select * From TA_CompressedWorkHoursApplication

--update TA_CompressedWorkHoursApplication set Status='Pending' where CWH_App_Id=10

-------Script16--------
Alter PROCEDURE Proc_UpdateCancelledMailTrigger
(
    @CWH_App_Id INT
)
AS
BEGIN
    UPDATE TA_CompressedWorkHoursApplication
    SET 
        CancelledMailTrigger = 1,
        ModifiedDate = GETDATE()
    WHERE 
        CWH_App_Id = @CWH_App_Id
        AND ISNULL(CancelledMailTrigger, 0) = 0;
END

------Script17-------
USE [TawazunCusIII]
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_GetDateAndWeekDay]    Script Date: 24-03-2026 13:17:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Proc_TA_GetDateAndWeekDay]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE 
        @WeekDayName NVARCHAR(20),
        @WeekStartDate DATE,
        @InputDate DATE = '2026-03-19';  -- ✅ Use DATE datatype

    ------------------------------------------------------------
    -- Set Monday as first day of week
    ------------------------------------------------------------
    SET DATEFIRST 1;

    ------------------------------------------------------------
    -- Get weekday name
    ------------------------------------------------------------
    SELECT @WeekDayName = DATENAME(WEEKDAY, @InputDate);

    ------------------------------------------------------------
    -- Check Thursday (case-safe)
    ------------------------------------------------------------
    IF (@WeekDayName = 'Thursday')
    BEGIN
        --------------------------------------------------------
        -- Get Monday (Week Start Date)
        --------------------------------------------------------
        SELECT  
            DATEADD(DAY, 1 - DATEPART(WEEKDAY, @InputDate), @InputDate) AS WeekStartDate,
            @InputDate AS CurrentDate,
            '23:00' AS ScheduleTime;
    END
END
-------- Script18----
