/*=====================================================================
  Proc_TA_RptOnDutyEntry  -  PERFORMANCE OPTIMISED VERSION
  Target: SQL Server 2008 / DB compatibility level < 110
          (no TRY_CONVERT, STRING_AGG, CONCAT, IIF, OFFSET-FETCH)

  exec Proc_TA_RptOnDutyEntry @employeeName=N'',@EmployeeId=N'',
       @fromDate=N'01/Sep/2026',@ToDate=N'30/Sep/2026',@levelId=N'0',
       @employeeCategory=0,@employeeType=0,@UserGroupName=N'UxHOD B001273',
       @GradeLevel=N'1',@Status=N'All',@EmpSwipeCard=N'',@ParentId=0,
       @DesignationID=0,@Gender=N'Both',@EmpAutoID=3,@ReasonCategory=N'',
       @SchoolCode=N''

  WHAT CHANGED AND WHY
  --------------------------------------------------------------------
  1.  SELECT DISTINCT over a 6-way join removed.
      #EmpType, #EmpCategory and #LevelRights contributed NO columns to
      the output; they were only filters. As INNER JOINs they multiplied
      rows (employee x type-row x category-row x level-right-row) and the
      DISTINCT then had to sort/hash the whole exploded set including the
      formatted date strings and NVARCHAR(MAX) school aggregate.
      They are now EXISTS semi-joins - no row multiplication, no sort.

  2.  Two-phase execution.
      Phase 1 resolves only the keys (Employee_Id, Onduty_ID, LevelId,
      DesignationId) that survive every filter.
      Phase 2 does the display formatting and the school aggregation on
      that small surviving set only.

  3.  The FOR XML school aggregate used to run once per row of the
      pre-DISTINCT (exploded) result. It now runs once per surviving
      Onduty_ID.

  4.  Temp table indexes are created AFTER the data is loaded, and are
      keyed on EmployeeId (the join column) instead of EffectiveDate.
      The original indexes were keyed EffectiveDate, LevelId, EmployeeId
      so they were never seekable for "ON #EmpLevel.EmployeeId = ...",
      and they had to be maintained during every INSERT..EXEC.

  5.  Redundant COLLATE removed from the Employee <-> OnDuty join.
      Employee_Code_Id and Onduty_EmployeeID both live in this database
      and therefore already carry the database collation; forcing
      COLLATE DATABASE_DEFAULT on both sides made the predicate
      non-SARGable and blocked index seeks on both tables.
      COLLATE is still applied where it is genuinely needed (temp tables
      live in tempdb, which has the instance collation) - but there it is
      declared once in CREATE TABLE instead of per-comparison.

  6.  CONVERT(VARCHAR(11), InActiveDate, 102) >= CONVERT(VARCHAR(11), ...)
      replaced with a DATEDIFF-based date truncation. Same result,
      no string conversion and no collation-aware string compare per row.

  7.  OPTION (RECOMPILE) on the main INSERT. This procedure is a classic
      "catch-all" (every filter is "ISNULL(@p,'')='' OR col=@p"), which
      on SQL 2008 produces one cached plan reused for wildly different
      parameter sets. RECOMPILE lets the optimiser fold the constants and
      build a plan per execution. The compile cost is tiny next to the
      current runtime.

  8.  ORDER BY now uses the raw Onduty_FromDate instead of the formatted
      string. NOTE: this is a behaviour change - and a bug fix. The old
      version sorted '05/Mar/2026' before '12/Jan/2026' because it was
      sorting text, not dates.

  9.  Soft-deleted OnDuty entries are now excluded. The original only
      filtered TA_Tbl_Employee.Isdeleted, so a deleted entry belonging to
      an active employee still appeared in the report. This is a result
      change, not just a speed change - expect the row count to drop.

  NOTE: @levelId and @EmpSwipeCard are accepted but never used in the
        body (unchanged from the original - left in place so the DAL
        signature still matches).
=====================================================================*/

ALTER PROCEDURE [dbo].[Proc_TA_RptOnDutyEntry]
(
    @employeeName      nvarchar(max),
    @employeeId        nvarchar(max),
    @fromdate          nvarchar(20),
    @toDate            nvarchar(20),
    @levelId           varchar(8000),
    @employeeCategory  int,
    @employeeType      int,
    @UserGroupName     varchar(250),
    @GradeLevel        varchar(20),
    @Status            varchar(25),
    @EmpSwipeCard      varchar(20),
    @ParentId          int,
    @DesignationID     int,
    @Gender            varchar(10),
    @EmpAutoID         int,
    @ReasonCategory    varchar(50) = NULL,
    @SchoolCode        nvarchar(20) = NULL
)
AS
BEGIN
    SET NOCOUNT ON;

    /*==============================================================
      Scalar variables
      Pre-computing these keeps the per-row work out of the WHERE.
    ==============================================================*/

    DECLARE @FromDateValue   DATETIME;
    DECLARE @ToDateValue     DATETIME;
    DECLARE @dateFormat      INT;
    DECLARE @isReportingto   INT;
    DECLARE @GradeLevelValue INT;
    DECLARE @IsSuperAdmin    BIT;
    DECLARE @FilterGender    BIT;

    SET @FromDateValue   = CONVERT(DATETIME, @fromdate, 102);
    SET @ToDateValue     = CONVERT(DATETIME, @toDate,   102);
    SET @GradeLevelValue = CONVERT(INT, ISNULL(NULLIF(@GradeLevel, ''), '0'));

    SET @IsSuperAdmin =
        CASE WHEN @UserGroupName = 'SuperAdmin' THEN 1 ELSE 0 END;

    SET @FilterGender =
        CASE WHEN ISNULL(@Gender, '') = '' OR @Gender = 'Both'
             THEN 0 ELSE 1 END;

    SELECT @dateFormat = DateFormat
    FROM Func_TA_GetDateTimeFormat();

    /*==============================================================
      Staging tables for the INSERT..EXEC results.
      Column list must stay exactly as-is: it has to match the
      result set shape of the called procedures.
      No indexes here - these are write-once, read-once.
    ==============================================================*/

    CREATE TABLE #EmpLevel
    (
        EmployeeId       INT,
        LevelId          INT,
        EffectiveDate    DATETIME,
        Employee_Code_Id VARCHAR(50),
        SwipeCard        VARCHAR(50)
    );

    CREATE TABLE #EmpDesignation
    (
        EmployeeId       INT,
        DesignationId    INT,
        EffectiveDate    DATETIME,
        Employee_Code_Id VARCHAR(50),
        SwipeCard        VARCHAR(50)
    );

    CREATE TABLE #EmpType
    (
        EmployeeId       INT,
        TypeId           INT,
        EffectiveDate    DATETIME,
        Employee_Code_Id VARCHAR(50),
        SwipeCard        VARCHAR(50)
    );

    CREATE TABLE #EmpCategory
    (
        EmployeeId       INT,
        CategoryId       INT,
        EffectiveDate    DATETIME,
        Employee_Code_Id VARCHAR(50),
        SwipeCard        VARCHAR(50)
    );

    INSERT INTO #EmpDesignation
    EXEC Proc_TA_EmpDesignation @ToDate, @EmployeeId, @DesignationID;

    INSERT INTO #EmpType
    EXEC Proc_TA_EmpType       @ToDate, @EmployeeId, @employeeType;

    INSERT INTO #EmpCategory
    EXEC Proc_TA_EmpCategory   @ToDate, @EmployeeId, @employeeCategory;

    INSERT INTO #EmpLevel
    EXEC Proc_TA_Emplevel      @ToDate, @EmployeeId;

    /*==============================================================
      Slim, de-duplicated working sets.

      Only #Lvl and #Desig carry a second column, because only those
      two feed output values (level name / designation name).
      #TypeEmp and #CatEmp collapse to a single key column, which is
      all a semi-join needs.
    ==============================================================*/

    CREATE TABLE #Lvl
    (
        EmployeeId INT NOT NULL,
        LevelId    INT NOT NULL,
        PRIMARY KEY CLUSTERED (EmployeeId, LevelId)
    );

    INSERT INTO #Lvl (EmployeeId, LevelId)
    SELECT DISTINCT EmployeeId, LevelId
    FROM   #EmpLevel
    WHERE  EmployeeId IS NOT NULL
      AND  LevelId    IS NOT NULL;

    CREATE TABLE #Desig
    (
        EmployeeId    INT NOT NULL,
        DesignationId INT NOT NULL,
        PRIMARY KEY CLUSTERED (EmployeeId, DesignationId)
    );

    INSERT INTO #Desig (EmployeeId, DesignationId)
    SELECT DISTINCT EmployeeId, DesignationId
    FROM   #EmpDesignation
    WHERE  EmployeeId    IS NOT NULL
      AND  DesignationId IS NOT NULL;

    CREATE TABLE #TypeEmp (EmployeeId INT NOT NULL PRIMARY KEY CLUSTERED);

    INSERT INTO #TypeEmp (EmployeeId)
    SELECT DISTINCT EmployeeId
    FROM   #EmpType
    WHERE  EmployeeId IS NOT NULL;

    CREATE TABLE #CatEmp (EmployeeId INT NOT NULL PRIMARY KEY CLUSTERED);

    INSERT INTO #CatEmp (EmployeeId)
    SELECT DISTINCT EmployeeId
    FROM   #EmpCategory
    WHERE  EmployeeId IS NOT NULL;

    /* staging no longer needed */
    DROP TABLE #EmpLevel;
    DROP TABLE #EmpDesignation;
    DROP TABLE #EmpType;
    DROP TABLE #EmpCategory;

    /*==============================================================
      Level rights

      NOTE: do NOT write "FROM dbo.fn_TA_GetParentChild(...) AS f(LevelId)".
      The inline column-alias list on a user-defined function call is
      rejected here (and the count must match the function's real column
      count exactly, which we cannot assume). Staging into a heap first
      reproduces the original "SELECT *" shape, which is known to work,
      and costs nothing - the row count is tiny.
    ==============================================================*/

    CREATE TABLE #LevelRightsRaw (LevelId INT NULL);

    INSERT INTO #LevelRightsRaw
    SELECT *
    FROM   dbo.fn_TA_GetParentChild(@UserGroupName, @ParentID);

    CREATE TABLE #LevelRights (LevelId INT NOT NULL PRIMARY KEY CLUSTERED);

    INSERT INTO #LevelRights (LevelId)
    SELECT DISTINCT LevelId
    FROM   #LevelRightsRaw
    WHERE  LevelId IS NOT NULL;

    DROP TABLE #LevelRightsRaw;

    /*==============================================================
      Reporting based on
    ==============================================================*/

    SELECT @isReportingto = ReportsBasedOn
    FROM   TA_Tbl_Usergroup
    WHERE  groupName COLLATE DATABASE_DEFAULT =
           @UserGroupName COLLATE DATABASE_DEFAULT;

    /*==============================================================
      Reporting-to / sanction-authority employees.

      The two function calls used to be UNION ALL'd into a heap with
      no key, then probed with "IN (SELECT ...)" per row.
      A de-duplicated keyed table turns that into a seek.
      Only populated when it will actually be used.
    ==============================================================*/

    CREATE TABLE #EmployeeIds (EmpAutoId INT NOT NULL PRIMARY KEY CLUSTERED);

    IF ISNULL(@isReportingto, 0) > 0
    BEGIN
        /* Same rule as above: plain SELECT * into a heap, no column
           alias list on the function call. */
        CREATE TABLE #EmployeeIdsRaw (EmpAutoId INT NULL);

        INSERT INTO #EmployeeIdsRaw
        SELECT *
        FROM   dbo.fn_TA_GetMappedEmployees_ReportingTo(@EmpAutoID);

        INSERT INTO #EmployeeIdsRaw
        SELECT *
        FROM   dbo.fn_TA_GetMappedEmployees_SancAuthority(@EmpAutoID);

        INSERT INTO #EmployeeIds (EmpAutoId)
        SELECT DISTINCT EmpAutoId
        FROM   #EmployeeIdsRaw
        WHERE  EmpAutoId IS NOT NULL;

        DROP TABLE #EmployeeIdsRaw;
    END;

    /*==============================================================
      Employee name filter.

      The collation is declared ONCE, in the column definition, so the
      comparison below stays SARGable and can seek the clustered key.
    ==============================================================*/

    CREATE TABLE #EmployeeName
    (
        EmployeeID VARCHAR(25) COLLATE DATABASE_DEFAULT NOT NULL PRIMARY KEY CLUSTERED
    );

    IF ISNULL(@EmployeeName, '') <> ''
    BEGIN
        INSERT INTO #EmployeeName (EmployeeID)
        SELECT DISTINCT [Value]
        FROM   dbo.SplitUpEmployeeName(@EmployeeName, ',')
        WHERE  ISNULL([Value], '') <> '';
    END;

    /*==============================================================
      PHASE 1
      Resolve the surviving key set only. No formatting, no string
      building, no XML - so the optimiser can use hash/merge semi-joins
      on narrow integer keys.
    ==============================================================*/

    CREATE TABLE #Result
    (
        Onduty_ID        INT         NOT NULL,
        Employee_Id      INT         NOT NULL,
        LevelId          INT         NOT NULL,
        DesignationId    INT         NOT NULL,
        Employee_Code_Id VARCHAR(50) COLLATE DATABASE_DEFAULT NULL,
        Onduty_FromDate  DATETIME    NULL
    );

    INSERT INTO #Result
    (
        Onduty_ID,
        Employee_Id,
        LevelId,
        DesignationId,
        Employee_Code_Id,
        Onduty_FromDate
    )
    SELECT DISTINCT
        od.Onduty_ID,
        e.Employee_Id,
        l.LevelId,
        d.DesignationId,
        e.Employee_Code_Id,
        od.Onduty_FromDate

    FROM TA_Tbl_OndutyEntry AS od

    /* no COLLATE: both columns are in this database and already share
       the database collation. Forcing it blocked seeks on both sides. */
    INNER JOIN TA_Tbl_Employee AS e
        ON e.Employee_Code_Id = od.Onduty_EmployeeID

    INNER JOIN #Lvl AS l
        ON l.EmployeeId = e.Employee_Id

    INNER JOIN #Desig AS d
        ON d.EmployeeId = e.Employee_Id

    /* Title/Grade stay as INNER JOINs: the original required both rows
       to exist, and Grade_Level is needed for the grade filter. */
    INNER JOIN TA_Tbl_Title AS t
        ON t.TitleId = d.DesignationId

    INNER JOIN TA_Tbl_Grade AS g
        ON g.Grade_ID = t.GradeId

    WHERE
        /*---- date range (SARGable, unchanged) ----*/
        od.Onduty_FromDate <= @ToDateValue
    AND od.Onduty_ToDate   >= @FromDateValue

        /*---- entry state ----
          Soft-deleted OnDuty entries must not appear in the report.
          ISNULL() covers legacy rows written before the column existed
          (or before it had a default), which carry NULL rather than 0.
          CONFIRM THE COLUMN NAME - see the check query at the bottom of
          this file. If it is Onduty_IsDeleted, rename it here. */
    AND ISNULL(od.IsDeleted, 0) = 0

        /*---- employee state ----*/
    AND e.Isdeleted      = 0
    AND e.report_exclude = 0
    AND
    (
        e.Employee_Status = 1
        OR
        (
            e.Employee_Status = 0
            AND DATEADD(DAY, DATEDIFF(DAY, 0, e.InActiveDate), 0)
                >=
                DATEADD(DAY, DATEDIFF(DAY, 0, od.Onduty_EntryDate), 0)
        )
    )

        /*---- filters that contribute no columns: semi-joins ----*/
    AND EXISTS (SELECT 1 FROM #LevelRights lr WHERE lr.LevelId    = l.LevelId)
    AND EXISTS (SELECT 1 FROM #TypeEmp     ty WHERE ty.EmployeeId = e.Employee_Id)
    AND EXISTS (SELECT 1 FROM #CatEmp      ct WHERE ct.EmployeeId = e.Employee_Id)

        /*---- employee name ----*/
    AND
    (
        ISNULL(@EmployeeName, '') = ''
        OR EXISTS
        (
            SELECT 1
            FROM   #EmployeeName EN
            WHERE  EN.EmployeeID = e.Employee_Code_Id
        )
    )

        /*---- gender ----*/
    AND
    (
        @FilterGender = 0
        OR e.Gender COLLATE DATABASE_DEFAULT = @Gender COLLATE DATABASE_DEFAULT
    )

        /*---- reporting to / sanction authority ----*/
    AND
    (
        ISNULL(@isReportingto, 0) <= 0
        OR EXISTS
        (
            SELECT 1
            FROM   #EmployeeIds ei
            WHERE  ei.EmpAutoId = e.Employee_ID
        )
    )

        /*---- grade level ----*/
    AND
    (
        @IsSuperAdmin = 1
        OR g.Grade_Level >= @GradeLevelValue
    )

        /*---- status ----*/
    AND
    (
        ISNULL(@Status, '') = ''
        OR @Status = 'All'
        OR od.Onduty_Status COLLATE DATABASE_DEFAULT =
           @Status COLLATE DATABASE_DEFAULT
    )

        /*---- reason category ----*/
    AND
    (
        ISNULL(@ReasonCategory, '') = ''
        OR od.ReasonCategory COLLATE DATABASE_DEFAULT =
           @ReasonCategory COLLATE DATABASE_DEFAULT
    )

        /*---- school code ----------------------------------------
          New multi-school rows  -> TA_Tbl_OndutyEntrySchool
          Legacy rows (no child) -> TA_Tbl_OndutyEntry.SchoolCode
        ---------------------------------------------------------*/
    AND
    (
        ISNULL(@SchoolCode, '') = ''

        OR EXISTS
        (
            SELECT 1
            FROM   TA_Tbl_OndutyEntrySchool ES
            WHERE  ES.Onduty_ID = od.Onduty_ID
              AND  ES.SchoolCode COLLATE DATABASE_DEFAULT =
                   @SchoolCode COLLATE DATABASE_DEFAULT
        )

        OR
        (
            od.SchoolCode COLLATE DATABASE_DEFAULT =
            @SchoolCode COLLATE DATABASE_DEFAULT

            AND NOT EXISTS
            (
                SELECT 1
                FROM   TA_Tbl_OndutyEntrySchool ES2
                WHERE  ES2.Onduty_ID = od.Onduty_ID
            )
        )
    )
    OPTION (RECOMPILE);

    CREATE CLUSTERED INDEX IX_Result ON #Result (Onduty_ID, LevelId, DesignationId);

    /*==============================================================
      School aggregation - now once per surviving OnDuty entry
      instead of once per exploded pre-DISTINCT row.
    ==============================================================*/

    CREATE TABLE #Schools
    (
        Onduty_ID INT NOT NULL PRIMARY KEY CLUSTERED,
        Codes     NVARCHAR(MAX) NULL
    );

    INSERT INTO #Schools (Onduty_ID, Codes)
    SELECT
        r.Onduty_ID,
        STUFF
        (
            (
                SELECT ', ' + ES.SchoolCode
                FROM   TA_Tbl_OndutyEntrySchool ES
                WHERE  ES.Onduty_ID = r.Onduty_ID
                ORDER BY ES.SchoolCode
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),
            1, 2, ''
        )
    FROM (SELECT DISTINCT Onduty_ID FROM #Result) AS r;

    /*==============================================================
      PHASE 2
      Formatting and output. No DISTINCT needed - #Result is already
      unique on the key set that drove it.
    ==============================================================*/

    SELECT

        /* Employee Name */
        ISNULL(e.Employee_FirstName, ' ')
        + SPACE(1)
        + ISNULL(e.Employee_LastName, ' ')          AS Employee_Name,

        /* Level Name */
        REPLACE(ld.name, '&amp;', '&')              AS name,

        r.Onduty_ID,

        r.Employee_Code_Id                          AS Onduty_Employee_Id,

        /* Entry Date */
        CASE WHEN @dateFormat = 106
             THEN REPLACE(CONVERT(VARCHAR(20), od.Onduty_EntryDate, @dateFormat), ' ', '-')
             ELSE CONVERT(VARCHAR(20), od.Onduty_EntryDate, @dateFormat)
        END                                         AS Onduty_EntryDate,

        /* From Date */
        CASE WHEN @dateFormat = 106
             THEN REPLACE(CONVERT(VARCHAR(20), od.Onduty_FromDate, @dateFormat), ' ', '-')
             ELSE CONVERT(VARCHAR(20), od.Onduty_FromDate, @dateFormat)
        END                                         AS Onduty_FromDate,

        od.Onduty_FromTime,

        /* To Date */
        CASE WHEN @dateFormat = 106
             THEN REPLACE(CONVERT(VARCHAR(20), od.Onduty_ToDate, @dateFormat), ' ', '-')
             ELSE CONVERT(VARCHAR(20), od.Onduty_ToDate, @dateFormat)
        END                                         AS Onduty_ToDate,

        od.Onduty_ToTime,

        /* Reason */
        REPLACE(
            ISNULL(od.ReasonCategory, od.Onduty_Reason),
            '&amp;', '&'
        )                                           AS Onduty_Reason,

        od.Onduty_Status,

        t.TitleName,

        e.Gender,

        ISNULL(s.Codes, od.SchoolCode)              AS SchoolCode,

        od.Destination,

        od.PurposeOfVisit

    FROM #Result AS r

    INNER JOIN TA_Tbl_OndutyEntry AS od
        ON od.Onduty_ID = r.Onduty_ID

    INNER JOIN TA_Tbl_Employee AS e
        ON e.Employee_Id = r.Employee_Id

    INNER JOIN Ta_Tbl_levelDetails AS ld
        ON ld.id = r.LevelId

    INNER JOIN TA_Tbl_Title AS t
        ON t.TitleId = r.DesignationId

    LEFT JOIN #Schools AS s
        ON s.Onduty_ID = r.Onduty_ID

    /* sort on the real date, not on the formatted text */
    ORDER BY
        r.Employee_Code_Id,
        r.Onduty_FromDate;

    /*==============================================================
      Cleanup
    ==============================================================*/

    DROP TABLE #Lvl;
    DROP TABLE #Desig;
    DROP TABLE #TypeEmp;
    DROP TABLE #CatEmp;
    DROP TABLE #LevelRights;
    DROP TABLE #EmployeeIds;
    DROP TABLE #EmployeeName;
    DROP TABLE #Result;
    DROP TABLE #Schools;

END;
GO



----------------------



/*===============================================================================================
  Proc_TA_RptMyCalendar  --  revised

  Changes from the previous version
  ---------------------------------
  1. COLLATION FIX (the reported SqlException)
     All character columns in every #temp table are now declared with
     COLLATE DATABASE_DEFAULT. Temp tables otherwise inherit tempdb's collation
     (SQL_Latin1_General_CP1_CI_AS) while base-table columns use the database
     collation (Latin1_General_CI_AI), which broke these equality joins:
        - #TEMPEMPLOYEE.EMPSWIPECARDNO      = TA_TBL_PROCESSEDDATA.EMPLOYEE_CODE_ID
        - #TEMPPROCESSEDDATA.EMPLOYEE_CODE_ID = TA_TBL_PROCESSEDDATA.EMPLOYEE_CODE_ID  (x4)
     Fixing it in the DDL rather than on the predicates keeps index seeks intact.

  2. PERFORMANCE: the row-by-row WHILE loop that built @OutputString has been replaced
     with a single set-based FOR XML PATH aggregation. STRING_AGG is deliberately NOT
     used (this instance is below compatibility level 110).
     The ", TYPE ... .value()" form is required so that '&' inside holiday descriptions
     round-trips correctly instead of coming back as '&amp;'.

  3. BUG: the loop's @STATUS / @STATUS2 variables were NVARCHAR(25) while the underlying
     columns are NVARCHAR(500), silently truncating holiday descriptions. Those variables
     are gone with the loop.

  4. BUG: the holiday-description lookup used
        Holiday_FromDate >= <date> AND Holiday_ToDate <= <date>
     which only matches a holiday whose From and To are both exactly the attendance date.
     Multi-day holidays returned NULL and the session went blank on screen.
     Now uses  Holiday_FromDate <= <date> AND Holiday_ToDate >= <date>  with TOP 1.

     BEHAVIOUR NOTE: when no holiday master row matches, the session now keeps the text
     'HOLIDAY' instead of becoming NULL/blank. Verify this is acceptable to the client
     before deploying; if a blank is required, drop the ISNULL() wrapper.

  5. The two UPDATE statements that guard on INNOTATION/OUTNOTATION = 'M' are logical
     no-ops (both CASE branches return the same column). They are left in place, flagged,
     in case the intended logic was lost in an earlier edit.


===============================================================================================*/

ALTER PROCEDURE [dbo].[Proc_TA_RptMyCalendar]
(   @USERGROUPNAME VARCHAR(250),
    @EmpAutoId     BIGINT
)
AS
BEGIN
	SET NOCOUNT ON;

	CREATE TABLE #TEMPEMPLOYEE
	(
		EMPAUTOID      BIGINT,
		EMPSWIPECARDNO VARCHAR(20) COLLATE DATABASE_DEFAULT
	)

	DECLARE @SQLTEMPPROCESSEDDATA  VARCHAR(8000)
	DECLARE @SQLTEMPPROCESSEDDATA1 VARCHAR(8000)
	DECLARE @FROMDAT DATETIME
	DECLARE @TODAT   DATETIME
	DECLARE @PARENTID INT
	SET @PARENTID = 0

	if( (select FromDate from ta_tbl_yearsetting where Isactive=1) is null)
	begin
		set @FROMDAT='01/01/1900'
		set @TODAT='01/01/1900'
	end
	else
	begin
		select @FROMDAT = FromDate from ta_tbl_yearsetting where Isactive=1
		if (@ParentId = 0 )
		begin
			select @TODAT= dateadd(day,(select max(NoofSuccessiveDaysinyear) from ta_tbl_shiftsettings) ,ToDate)
			from ta_tbl_yearsetting where Isactive=1
		end
		else
		begin
		 --if successive year is not defined means, leave application throws unwanted error message.To avoid that, we added below conditon
			 if exists(select NoofSuccessiveDaysinyear from ta_tbl_shiftsettings where companyId=@ParentId)
			 begin
				select @TODAT= dateadd(day,(select NoofSuccessiveDaysinyear from ta_tbl_shiftsettings
				where companyId=@ParentId),ToDate) from ta_tbl_yearsetting where Isactive=1
			 end
			 else
			 begin
				select @TODAT= dateadd(day,(select NoofSuccessiveDaysinyear from ta_tbl_shiftsettings
				where companyId=-1),ToDate) from ta_tbl_yearsetting where Isactive=1
			end
		end
	end

	--TEMPORARY TABLE'S CREATION
	CREATE TABLE #EMPLEVEL
	(
		EMPLOYEEID       INT,
		LEVELID          INT,
		EFFECTIVEDATE    DATETIME,
		EMPLOYEE_CODE_ID VARCHAR(50) COLLATE DATABASE_DEFAULT,
		SWIPECARD        VARCHAR(50) COLLATE DATABASE_DEFAULT
	)

	CREATE TABLE #EMPDESIGNATION
	(
		EMPLOYEEID       INT,
		DESIGNATIONID    INT,
		EFFECTIVEDATE    DATETIME,
		EMPLOYEE_CODE_ID VARCHAR(50) COLLATE DATABASE_DEFAULT,
		SWIPECARD        VARCHAR(50) COLLATE DATABASE_DEFAULT
	)

	CREATE TABLE #EMPTYPE
	(
		EMPLOYEEID       INT,
		TYPEID           INT,
		EFFECTIVEDATE    DATETIME,
		EMPLOYEE_CODE_ID VARCHAR(50) COLLATE DATABASE_DEFAULT,
		SWIPECARD        VARCHAR(50) COLLATE DATABASE_DEFAULT
	)

	CREATE TABLE #EMPCATEGORY
	(
		EMPLOYEEID       INT,
		CATEGORYID       INT,
		EFFECTIVEDATE    DATETIME,
		EMPLOYEE_CODE_ID VARCHAR(50) COLLATE DATABASE_DEFAULT,
		SWIPECARD        VARCHAR(50) COLLATE DATABASE_DEFAULT
	)

	CREATE TABLE #EMPPROCESSEDDATA
	(
		SWIPECARDNO   VARCHAR(50) COLLATE DATABASE_DEFAULT,
		PROCESSDATE   DATETIME,
		TOTALWORKMINS VARCHAR(50) COLLATE DATABASE_DEFAULT
	)

	CREATE TABLE #LEVELRIGHTS(LEVELID INT)

	CREATE TABLE #TEMPPROCESSEDDATA
	(
		EMPAUTOID                  BIGINT,
		DATE                       DATETIME,
		EMPLOYEE_CODE_ID           VARCHAR(20)  COLLATE DATABASE_DEFAULT,
		INTIME                     NVARCHAR(25) COLLATE DATABASE_DEFAULT,
		OUTTIME                    NVARCHAR(25) COLLATE DATABASE_DEFAULT,
		SHIFT_ID                   INT,
		HRSWORK                    NVARCHAR(10) COLLATE DATABASE_DEFAULT,
		SUPPOSEDWORKHOURS          NVARCHAR(10) COLLATE DATABASE_DEFAULT,
		STATUS                     NVARCHAR(50) COLLATE DATABASE_DEFAULT,
		ATTENDANCE                 NVARCHAR(20) COLLATE DATABASE_DEFAULT,
		ANATTENDANCECODE           NVARCHAR(15) COLLATE DATABASE_DEFAULT,
		FNATTENDANCECODE           NVARCHAR(15) COLLATE DATABASE_DEFAULT,
		PREVSHIFT_ID               INT,
		PROCESS_REC_ID             BIGINT,
		EXACTSHIFT_STARTTIME       NVARCHAR(25) COLLATE DATABASE_DEFAULT,
		SHIFT_SECONDHALF_STARTTIME DATETIME,
		LATEINMIN                  NVARCHAR(10) COLLATE DATABASE_DEFAULT,
		EARLYOUT                   NVARCHAR(10) COLLATE DATABASE_DEFAULT,
		LATEIN                     NVARCHAR(10) COLLATE DATABASE_DEFAULT,
		NORMOT                     NVARCHAR(10) COLLATE DATABASE_DEFAULT,
		OFFOT                      NVARCHAR(10) COLLATE DATABASE_DEFAULT,
		HOLOT                      NVARCHAR(10) COLLATE DATABASE_DEFAULT,
		OTMINS                     FLOAT,
		EARLYOUTMIN                INT,
		OVERTIMESTATUS             VARCHAR(20)  COLLATE DATABASE_DEFAULT,
		NIGHTORMORN                INT,
		INNOTATION                 CHAR(1)      COLLATE DATABASE_DEFAULT,
		OUTNOTATION                CHAR(1)      COLLATE DATABASE_DEFAULT,
		BREAKHOURS                 VARCHAR(20)  COLLATE DATABASE_DEFAULT,
		LUNCHHOURS                 VARCHAR(20)  COLLATE DATABASE_DEFAULT,
		BREAKHOURSMINS             INT,
		LUNCHHOURSMINS             INT
	)

	INSERT INTO  #LEVELRIGHTS SELECT * FROM [DBO].[FN_TA_GETPARENTCHILD](@USERGROUPNAME,0) AS LEVELID

	CREATE NONCLUSTERED INDEX [IDX_EMPLOYEE_CODE_ID] ON [DBO].[#TEMPPROCESSEDDATA]([DATE],[EMPLOYEE_CODE_ID]) ON [PRIMARY]

	CREATE NONCLUSTERED INDEX [IDX_LEVELID] ON [DBO].[#EMPLEVEL]([EFFECTIVEDATE],[EMPLOYEEID],[LEVELID]) ON [PRIMARY]

	CREATE NONCLUSTERED INDEX [IDX_EFFECTIVE] ON [DBO].[#EMPDESIGNATION]([EFFECTIVEDATE],[EMPLOYEEID],[DESIGNATIONID]) ON [PRIMARY]

	CREATE NONCLUSTERED INDEX [IDX_EFFECTIVE] ON [DBO].[#EMPTYPE]([EFFECTIVEDATE],[EMPLOYEEID],[TYPEID]) ON [PRIMARY]

	CREATE NONCLUSTERED INDEX [IDX_EFFECTIVE] ON [DBO].[#EMPCATEGORY]([EFFECTIVEDATE],[EMPLOYEEID],[CATEGORYID]) ON [PRIMARY]

	DECLARE @SQLEMPLEVEL NVARCHAR(4000)
	INSERT INTO #EMPDESIGNATION EXEC PROC_TA_EMPDESIGNATION @TODAT,'',0
	INSERT INTO #EMPTYPE        EXEC [PROC_TA_EMPTYPE]      @TODAT,'',0
	INSERT INTO #EMPCATEGORY    EXEC [PROC_TA_EMPCATEGORY]  @TODAT,'',0

	SET @SQLEMPLEVEL=
	'INSERT INTO #EMPLEVEL SELECT ELEVEL.EMPLOYEEID,EL.LEVELID,ELEVEL.EFFECTIVEDATE,EMPLOYEE_CODE_ID,SWIPECARDNO  FROM (
	SELECT MAX(EL.EFFECTIVEDATE) AS EFFECTIVEDATE,EL.EMPLOYEEID AS EMPLOYEEID,EMP.EMPLOYEE_CODE_ID,EMP.SWIPECARDNO FROM TA_TBL_EMPLOYEELEVEL EL
	INNER JOIN TA_TBL_EMPLOYEE EMP ON EL.EMPLOYEEID = EMP.EMPLOYEE_ID
	INNER JOIN #LEVELRIGHTS ON #LEVELRIGHTS.LEVELID=EL.LEVELID
	WHERE EMP.ISDELETED=0 GROUP BY EMPLOYEEID,EMPLOYEE_CODE_ID,EMP.SWIPECARDNO) ELEVEL
	INNER JOIN TA_TBL_EMPLOYEELEVEL EL ON EL.EFFECTIVEDATE=ELEVEL.EFFECTIVEDATE AND EL.EMPLOYEEID=ELEVEL.EMPLOYEEID
	INNER JOIN #LEVELRIGHTS ON #LEVELRIGHTS.LEVELID=EL.LEVELID
	AND EL.EFFECTIVEDATE <= CONVERT(DATETIME,'''+CAST(@TODAT AS NVARCHAR(50))+''', 102) AND EL.LEVELID IS NOT NULL'
	EXECUTE(@SQLEMPLEVEL)

	SET @SQLTEMPPROCESSEDDATA = 'INSERT INTO #TEMPEMPLOYEE SELECT EMPLOYEE_ID,SWIPECARDNO FROM TA_TBL_EMPLOYEE
	INNER JOIN #EMPLEVEL ON #EMPLEVEL.EMPLOYEEID=TA_TBL_EMPLOYEE.EMPLOYEE_ID '

	if(@EmpAutoId > 0)
		BEGIN
			set @SQLTEMPPROCESSEDDATA = @SQLTEMPPROCESSEDDATA + ' and (TA_TBL_EMPLOYEE.Employee_ID =' + CAST(@EmpAutoId AS NVARCHAR(8)) + ') '
		END
	SET @SQLTEMPPROCESSEDDATA = @SQLTEMPPROCESSEDDATA + ' WHERE SWIPECARDNO IS NOT NULL AND TA_TBL_EMPLOYEE.ISDELETED=0'

	EXECUTE(@SQLTEMPPROCESSEDDATA)

	SET @SQLTEMPPROCESSEDDATA1=
	'INSERT INTO #TEMPPROCESSEDDATA
	SELECT	#TEMPEMPLOYEE.EMPAUTOID,
			TA_TBL_PROCESSEDDATA.DATE,
			TA_TBL_PROCESSEDDATA.EMPLOYEE_CODE_ID,
			TA_TBL_PROCESSEDDATA.INTIME,
			TA_TBL_PROCESSEDDATA.OUTTIME,
			TA_TBL_PROCESSEDDATA.SHIFT_ID,
			TA_TBL_PROCESSEDDATA.HRSWORK,
			TA_TBL_PROCESSEDDATA.WORKHRS,
			TA_TBL_PROCESSEDDATA.STATUS,
			TA_TBL_PROCESSEDDATA.ATTENDANCE,
			TA_TBL_PROCESSEDDATA.ANATTENDANCECODE,
			TA_TBL_PROCESSEDDATA.FNATTENDANCECODE,
			TA_TBL_PROCESSEDDATA.SHIFT_ID,
			TA_TBL_PROCESSEDDATA.PROCESS_REC_ID,
			S.SHIFT_STARTTIME,
			CAST(S.SHIFT_SECONDHALF_STARTTIME AS DATETIME)
			,LATEINMINS,
			case when EARLYOUT=''24:00'' then ''00:00'' else EARLYOUT end,
			LATEIN,
			TA_TBL_PROCESSEDDATA.NORMOT,
			TA_TBL_PROCESSEDDATA.OFFOT,
			TA_TBL_PROCESSEDDATA.HOLOT,
			OTMINS,
			CASE WHEN CHARINDEX(''.'',CONVERT(VARCHAR(20),EARLYOUTMINS))!=0 THEN CONVERT(INT,SUBSTRING(CONVERT(VARCHAR(20),EARLYOUTMINS),0,CHARINDEX(''.'',CONVERT(VARCHAR(20),EARLYOUTMINS))))
			ELSE EARLYOUTMINS END,
			OVERTIMESTATUS,
			0,
			INNOTATION,
			OUTNOTATION,
			BREAKHRS,
			LUNCHHRS,
			BREAKHRSINMINS,
			LUNCHHRSINMINS
	FROM TA_TBL_PROCESSEDDATA INNER JOIN #TEMPEMPLOYEE ON #TEMPEMPLOYEE.EMPSWIPECARDNO=TA_TBL_PROCESSEDDATA.EMPLOYEE_CODE_ID
	LEFT OUTER JOIN TA_TBL_SHIFT S ON S.SHIFT_ID=TA_TBL_PROCESSEDDATA.SHIFT_ID
	WHERE PROCESSFLAG=1 AND (DBO.TA_TBL_PROCESSEDDATA.DATE >= CONVERT(DATETIME,'''+CAST( @FROMDAT AS NVARCHAR(50))+''', 102))
	AND (DBO.TA_TBL_PROCESSEDDATA.DATE <= CONVERT(DATETIME,'''+CAST(@TODAT AS NVARCHAR(50))+''', 102))'

	EXECUTE(@SQLTEMPPROCESSEDDATA1)

	UPDATE PD SET EXACTSHIFT_STARTTIME=CAST(CONVERT(VARCHAR(25),SHIFT_SECONDHALF_STARTTIME,108) AS DATETIME) FROM #TEMPPROCESSEDDATA PD
	INNER JOIN TA_TBL_PROCESSEDDATA OPD ON PD.PROCESS_REC_ID=OPD.PROCESS_REC_ID WHERE OPD.FNATTENDANCECODE='CL'

	------------------------------------------------------CURRENT DATE IS WEEKLY OFF, TO GET THE PREVIOUS DATE SHIFT--------------------------------------------------
	UPDATE #TEMPPROCESSEDDATA SET PREVSHIFT_ID=B.SHIFT_ID FROM #TEMPPROCESSEDDATA T INNER JOIN
	(
		SELECT P.EMPLOYEE_CODE_ID,A.DATE,P.SHIFT_ID FROM TA_TBL_PROCESSEDDATA P INNER JOIN
		(
			SELECT EMPLOYEE_CODE_ID,DATEADD(D,-1,DATE) AS PREVDATE,DATE
			FROM #TEMPPROCESSEDDATA WHERE SHIFT_ID=-1
		)
		AS A ON A.EMPLOYEE_CODE_ID=P.EMPLOYEE_CODE_ID AND P.DATE=A.PREVDATE
		WHERE
		(P.DATE >= CONVERT(DATETIME,CAST(@FROMDAT AS NVARCHAR(50)), 102))
		AND (P.DATE <= CONVERT(DATETIME,CAST(@TODAT AS NVARCHAR(50)), 102))
	)AS B ON T.EMPLOYEE_CODE_ID=B.EMPLOYEE_CODE_ID AND T.DATE=B.DATE

	--------------------------------------------------------AGAIN THE PREVIOUS DATE IS ALSO WEEKLY OFF MEANS, TO GET THE PREVIOUS DATE SHIFT---------------------------------------------------
	UPDATE #TEMPPROCESSEDDATA SET PREVSHIFT_ID=B.SHIFT_ID FROM #TEMPPROCESSEDDATA T INNER JOIN
	(
		SELECT P.EMPLOYEE_CODE_ID,A.DATE,P.SHIFT_ID FROM TA_TBL_PROCESSEDDATA P INNER JOIN
		(
			SELECT EMPLOYEE_CODE_ID,DATEADD(D,-2,DATE) AS PREVDATE,DATE
			FROM #TEMPPROCESSEDDATA WHERE PREVSHIFT_ID=-1
		)
		AS A ON A.EMPLOYEE_CODE_ID=P.EMPLOYEE_CODE_ID AND P.DATE=A.PREVDATE
		WHERE
		(P.DATE >= CONVERT(DATETIME,CAST(@FROMDAT AS NVARCHAR(50)), 102))
		AND (P.DATE <= CONVERT(DATETIME,CAST(@TODAT AS NVARCHAR(50)), 102))
	)AS B ON T.EMPLOYEE_CODE_ID=B.EMPLOYEE_CODE_ID AND T.DATE=B.DATE

	------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	UPDATE #TEMPPROCESSEDDATA SET NIGHTORMORN= CASE WHEN SUBSTRING(S.SHIFT_STARTTIME,7,8)='PM' AND SUBSTRING(S.SHIFT_ENDTIME,7,8)='AM' THEN 1
													WHEN SUBSTRING(S.SHIFT_STARTTIME,7,8)='AM' AND SUBSTRING(S.SHIFT_ENDTIME,7,8)='PM' AND PD.OUTTIME>'00:00 AM' THEN 1
													ELSE 0 END
	FROM #TEMPPROCESSEDDATA PD INNER JOIN TA_TBL_SHIFT S ON PD.PREVSHIFT_ID=S.SHIFT_ID

	CREATE TABLE #TEMPMONTHLYATTENDANCE
	(
		FIRSTSESSION            VARCHAR(50)   COLLATE DATABASE_DEFAULT,
		SECONDSESSION           VARCHAR(50)   COLLATE DATABASE_DEFAULT,
		EMPLOYEE_ID             VARCHAR(25)   COLLATE DATABASE_DEFAULT,
		EMPNAME                 VARCHAR(500)  COLLATE DATABASE_DEFAULT,
		BRANCHNAME              VARCHAR(500)  COLLATE DATABASE_DEFAULT,
		DESIGNATION             VARCHAR(250)  COLLATE DATABASE_DEFAULT,
		DATE                    DATETIME,
		INTIME                  VARCHAR(30)   COLLATE DATABASE_DEFAULT,
		OUTTIME                 VARCHAR(30)   COLLATE DATABASE_DEFAULT,
		HRSWORK                 VARCHAR(30)   COLLATE DATABASE_DEFAULT,
		SUPPOSEDWORKHOURS       VARCHAR(30)   COLLATE DATABASE_DEFAULT,
		STATUS                  VARCHAR(30)   COLLATE DATABASE_DEFAULT,
		TOTALMINSWORKED         VARCHAR(30)   COLLATE DATABASE_DEFAULT,
		SHIFT_SHORTNAME         VARCHAR(20)   COLLATE DATABASE_DEFAULT,
		SHIFT_TOTALWORKINGHOURS VARCHAR(20)   COLLATE DATABASE_DEFAULT,
		GENDER                  NVARCHAR(10)  COLLATE DATABASE_DEFAULT,
		EARLYOUT                NVARCHAR(10)  COLLATE DATABASE_DEFAULT,
		LATEIN                  NVARCHAR(10)  COLLATE DATABASE_DEFAULT,
		OVERTIME                VARCHAR(20)   COLLATE DATABASE_DEFAULT,
		NORMOTMIN               INT,
		OFFOTMIN                INT,
		HOLOTMIN                INT,
		NORMOT                  NVARCHAR(20)  COLLATE DATABASE_DEFAULT,
		OFFOT                   NVARCHAR(20)  COLLATE DATABASE_DEFAULT,
		HOLOT                   NVARCHAR(20)  COLLATE DATABASE_DEFAULT,
		LATEINMIN               INT,
		EARLYOUTMIN             INT,
		TOTDEFWORKHRS           INT,
		TOTSUPWORKHRS           INT,
		OVERTIMESTATUS          VARCHAR(20)   COLLATE DATABASE_DEFAULT,
		NIGHTORMORN             INT,
		LUNCHINTIME             NVARCHAR(20)  COLLATE DATABASE_DEFAULT,
		LUNCHOUTTIME            NVARCHAR(20)  COLLATE DATABASE_DEFAULT,
		INNOTATION              CHAR(1)       COLLATE DATABASE_DEFAULT,
		OUTNOTATION             CHAR(1)       COLLATE DATABASE_DEFAULT,
		BREAKHOURS              VARCHAR(20)   COLLATE DATABASE_DEFAULT,
		LUNCHHOURS              VARCHAR(20)   COLLATE DATABASE_DEFAULT,
		BREAKHOURSMINS          INT,
		LUNCHHOURSMINS          INT,
		SHIFT_STARTTIME         NVARCHAR(10)  COLLATE DATABASE_DEFAULT,
		SHIFT_ENDTIME           NVARCHAR(10)  COLLATE DATABASE_DEFAULT,
		CategoryName            NVARCHAR(100) COLLATE DATABASE_DEFAULT,
		WeekDay_WorkHrs         NVARCHAR(100) COLLATE DATABASE_DEFAULT,
		WeekNumber              INT
	)

	DECLARE @SQLQUERY AS VARCHAR(MAX)
	SET @SQLQUERY=''
	SET @SQLQUERY = 'SELECT PD.FNATTENDANCECODE AS FIRSTSESSION, PD.ANATTENDANCECODE AS SECONDSESSION,E.EMPLOYEE_CODE_ID AS EMPLOYEE_ID,ISNULL
	(E.EMPLOYEE_FIRSTNAME,'''') +  SPACE(1) + ISNULL(E.EMPLOYEE_LASTNAME,'''') AS EMPNAME,REPLACE(DBO.TA_TBL_LEVELDETAILS.NAME,''&AMP;'',''&'') AS BRANCHNAME,REPLACE(T.TITLENAME,''&AMP;'',''&'') AS DESIGNATION,CONVERT(VARCHAR(20),PD.DATE,101) AS DATE,
	PD.INTIME,PD.OUTTIME,PD.HRSWORK,CASE WHEN PD.FNATTENDANCECODE=''WEEKLY OFF'' AND ANATTENDANCECODE=''WEEKLY OFF'' THEN	''-''
	WHEN PD.FNATTENDANCECODE=''HOLIDAY''OR ANATTENDANCECODE=''HOLIDAY'' THEN ''-''
	ELSE PD.SUPPOSEDWORKHOURS END ''SUPPOSEDWORKHOURS'',(PD.ATTENDANCE +  CASE PD.STATUS WHEN ''BL'' THEN '' - '' ELSE '''' END + ISNULL(PD.STATUS,'''')) AS STATUS,((SUBSTRING(ISNULL(PD.HRSWORK,''00:00''),0,CHARINDEX('':'',PD.HRSWORK))*60) + (SUBSTRING(ISNULL(PD.HRSWORK,''00:00''),(LEN(PD.HRSWORK)-1),2))) AS TOTALMINSWORKED'
	SET @SQLQUERY = @SQLQUERY + ' ,(CASE PD.SHIFT_ID WHEN ''-1'' THEN ''WEEKLY OFF'' ELSE SHF.SHIFT_SHORTNAME END) AS SHIFT_SHORTNAME ,
	ISNULL(
				(
					CASE WHEN ((PD.FNATTENDANCECODE = ''HOLIDAY'') OR (PD.ANATTENDANCECODE = ''HOLIDAY'')) THEN ''-''
						 WHEN PD.SHIFT_ID=-1 THEN ''-''
						 ELSE SHF.SHIFT_TOTALWORKINGHOURS END
				),''00:00''
			) AS SHIFT_TOTALWORKINGHOURS,
	E.GENDER,
	PD.EARLYOUT,PD.LATEIN,
	CASE WHEN PD.NORMOT IS NOT NULL THEN CAST(OTMINS AS VARCHAR(50))
		 WHEN PD.OFFOT IS NOT NULL THEN CAST(OTMINS AS VARCHAR(50))
		 WHEN PD.HOLOT IS NOT NULL THEN CAST(OTMINS AS VARCHAR(50))
		 ELSE ''00:00'' END OVERTIME,
	CASE ISNULL(NORMOT,''00:00'') WHEN ''00:00'' THEN 0 ELSE OTMINS END AS ''NORMOTMINS'',
	CASE ISNULL(OFFOT,''00:00'') WHEN ''00:00'' THEN 0 ELSE OTMINS END AS ''WEEKOFFOTMINS'',
	CASE ISNULL(HOLOT,''00:00'') WHEN ''00:00'' THEN 0 ELSE OTMINS END AS ''HOLIDAYOTMINS'',
	PD.NORMOT AS HRSOT,PD.OFFOT,PD.HOLOT,LATEINMIN,EARLYOUTMIN,
	CASE WHEN PD.SHIFT_ID=-1 THEN 0
		 WHEN ((PD.FNATTENDANCECODE = ''HOLIDAY'') OR (PD.ANATTENDANCECODE = ''HOLIDAY'')) THEN 0
		 ELSE SUBSTRING(SHF.SHIFT_TOTALWORKINGHOURS,0,CHARINDEX('':'',SHF.SHIFT_TOTALWORKINGHOURS,0))*60+SUBSTRING(SHF.SHIFT_TOTALWORKINGHOURS,CHARINDEX('':'',SHF.SHIFT_TOTALWORKINGHOURS,0)+1,LEN(SHF.SHIFT_TOTALWORKINGHOURS))
		 END AS TOTDEFWORKHRS
	,CASE	WHEN PD.SHIFT_ID=-1 THEN 0
			WHEN ((PD.FNATTENDANCECODE = ''HOLIDAY'') OR (PD.ANATTENDANCECODE = ''HOLIDAY'')) THEN 0
			ELSE SUBSTRING(PD.SUPPOSEDWORKHOURS,0,CHARINDEX('':'',PD.SUPPOSEDWORKHOURS,0))*60+SUBSTRING(PD.SUPPOSEDWORKHOURS,CHARINDEX('':'',PD.SUPPOSEDWORKHOURS,0)+1,LEN(PD.SUPPOSEDWORKHOURS))
	END AS TOTSUPWORKHRS,CASE WHEN OVERTIMESTATUS IS NULL THEN ''-'' ELSE OVERTIMESTATUS END,NIGHTORMORN,''00:00'',''00:00'',INNOTATION,OUTNOTATION,BREAKHOURS,LUNCHHOURS,BREAKHOURSMINS,LUNCHHOURSMINS,
	SHIFT_STARTTIME,SHIFT_ENDTIME,CategoryName,WeekDay_WorkHrs,
	DATEPART(week,date)
	 '
	SET @SQLQUERY = @SQLQUERY + ' FROM TA_TBL_EMPLOYEE E '
	SET @SQLQUERY = @SQLQUERY + ' INNER JOIN #TEMPPROCESSEDDATA PD ON E.EMPLOYEE_ID=PD.EMPAUTOID'
	SET @SQLQUERY = @SQLQUERY + ' INNER JOIN #EMPLEVEL ON #EMPLEVEL.EMPLOYEEID = E.EMPLOYEE_ID '
	SET @SQLQUERY = @SQLQUERY + ' INNER JOIN TA_TBL_LEVELDETAILS ON #EMPLEVEL.LEVELID = TA_TBL_LEVELDETAILS.ID'
	SET @SQLQUERY = @SQLQUERY + ' INNER JOIN #EMPDESIGNATION EDESIG ON EDESIG.EMPLOYEEID = E.EMPLOYEE_ID '
	SET @SQLQUERY = @SQLQUERY + ' INNER JOIN TA_TBL_TITLE T ON EDESIG.DESIGNATIONID = T.TITLEID'
	SET @SQLQUERY = @SQLQUERY + ' INNER JOIN TA_TBL_GRADE ON TA_TBL_GRADE.GRADE_ID = T.GRADEID'
	SET @SQLQUERY = @SQLQUERY + ' LEFT JOIN TA_TBL_SHIFT SHF ON PD.PREVSHIFT_ID = SHF.SHIFT_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 Ta_tbl_Category cat ON #EMPCATEGORY.CATEGORYID = cat.CategoryId '

	SET @SQLQUERY = @SQLQUERY + ' WHERE E.ISDELETED=0 AND E.REPORT_EXCLUDE=0 AND
		((E.EMPLOYEE_STATUS=1)
		OR
		(E.EMPLOYEE_STATUS=0 AND CONVERT(NVARCHAR(11),E.INACTIVEDATE,102)>=CONVERT(NVARCHAR(11),PD.DATE,102)) )
		AND((PD.DATE >= CONVERT(DATETIME, ''' + CAST(@FROMDAT AS NVARCHAR(11)) + ''', 102) AND PD.DATE <= CONVERT(DATETIME, ''' + CAST(@TODAT AS NVARCHAR(11)) + ''', 102)))'
	if(@EmpAutoId > 0)
	BEGIN
		set @sqlQuery = @sqlQuery + ' and (e.Employee_ID =' + CAST(@EmpAutoId AS NVARCHAR(8)) + ') '
	END

	SET @SQLQUERY = @SQLQUERY + ' ORDER BY E.EMPLOYEE_FIRSTNAME,CAST(PD.DATE AS DATETIME)'

	INSERT INTO #TEMPMONTHLYATTENDANCE EXEC(@SQLQUERY)

	-- NOTE: the next two UPDATEs are logical no-ops (both CASE branches return the same
	-- column). Retained verbatim from the original in case the intended rule is restored.
	UPDATE #TEMPMONTHLYATTENDANCE SET FIRSTSESSION=CASE WHEN (FIRSTSESSION='PRESENT' OR FIRSTSESSION='*PRESENT' OR FIRSTSESSION='HOLIDAY' OR FIRSTSESSION='*HOLIDAY' OR FIRSTSESSION='WEEKLY OFF' OR FIRSTSESSION='*WEEKLY OFF') AND INNOTATION='M' THEN  FIRSTSESSION
	ELSE #TEMPMONTHLYATTENDANCE.FIRSTSESSION END FROM #TEMPMONTHLYATTENDANCE

	UPDATE #TEMPMONTHLYATTENDANCE SET SECONDSESSION=CASE
	WHEN (SECONDSESSION='PRESENT' OR SECONDSESSION = '*PRESENT' OR SECONDSESSION ='HOLIDAY' OR SECONDSESSION ='*HOLIDAY' OR SECONDSESSION ='WEEKLY OFF' OR SECONDSESSION ='*WEEKLY OFF')  AND OUTNOTATION='M' THEN  SECONDSESSION
	ELSE #TEMPMONTHLYATTENDANCE.SECONDSESSION END FROM #TEMPMONTHLYATTENDANCE

	--------------------------------------------------------------------------------------------
	-- Holiday description lookup (fixed range predicate + TOP 1 + no NULL fallback)
	--------------------------------------------------------------------------------------------
	UPDATE M
	SET FIRSTSESSION = ISNULL
	(
		(
			SELECT TOP 1 H.Holiday_Description
			FROM TA_Tbl_Holiday H
			WHERE DATEADD(DD, DATEDIFF(DD, 0, M.DATE), 0)
				  BETWEEN DATEADD(DD, DATEDIFF(DD, 0, H.Holiday_FromDate), 0)
				  AND     DATEADD(DD, DATEDIFF(DD, 0, H.Holiday_ToDate),   0)
			ORDER BY H.Holiday_FromDate
		), M.FIRSTSESSION
	)
	FROM #TEMPMONTHLYATTENDANCE M
	WHERE M.FIRSTSESSION IN ('HOLIDAY','*HOLIDAY')

	UPDATE M
	SET SECONDSESSION = ISNULL
	(
		(
			SELECT TOP 1 H.Holiday_Description
			FROM TA_Tbl_Holiday H
			WHERE DATEADD(DD, DATEDIFF(DD, 0, M.DATE), 0)
				  BETWEEN DATEADD(DD, DATEDIFF(DD, 0, H.Holiday_FromDate), 0)
				  AND     DATEADD(DD, DATEDIFF(DD, 0, H.Holiday_ToDate),   0)
			ORDER BY H.Holiday_FromDate
		), M.SECONDSESSION
	)
	FROM #TEMPMONTHLYATTENDANCE M
	WHERE M.SECONDSESSION IN ('HOLIDAY','*HOLIDAY')

	--------------------------------------------------------------------------------------------
	-- Build the calendar rows
	--------------------------------------------------------------------------------------------
	DECLARE @DATEFORMAT AS INT
	SET @DATEFORMAT = 126

	CREATE TABLE #TEMPMYCALENDAR
	(
		SINO            INT,
		EMPLOYEE_ID     NVARCHAR(100) COLLATE DATABASE_DEFAULT,
		DATE            NVARCHAR(20)  COLLATE DATABASE_DEFAULT,
		SHIFT_SHORTNAME NVARCHAR(20)  COLLATE DATABASE_DEFAULT,
		HRSWORK         NVARCHAR(25)  COLLATE DATABASE_DEFAULT,
		STATUS          NVARCHAR(500) COLLATE DATABASE_DEFAULT,
		STATUS2         NVARCHAR(500) COLLATE DATABASE_DEFAULT,
		EXPHRS          NVARCHAR(25)  COLLATE DATABASE_DEFAULT
	)

	INSERT INTO #TEMPMYCALENDAR
	SELECT  ROW_NUMBER() OVER (ORDER BY DATE),
			EMPLOYEE_ID,
			CASE	WHEN CAST(@DATEFORMAT AS VARCHAR(100))=106 THEN
						REPLACE(CONVERT(VARCHAR, DATE,@DATEFORMAT),' ','-')
					ELSE CONVERT(VARCHAR(10), DATE, @DATEFORMAT) END,
			CASE WHEN SHIFT_SHORTNAME='WEEKLY OFF' THEN 'OFF' ELSE #TEMPMONTHLYATTENDANCE.SHIFT_SHORTNAME END [SHIFT SHORTNAME],
			HRSWORK,
			case when STATUS='Holiday' then 'Holiday' else FIRSTSESSION end,
			case when STATUS='Holiday' then 'Holiday' else SECONDSESSION end,
			cast(dateadd(s, isnull(datediff(s, 0, case when convert(int,SUBSTRING(EARLYOUT,0,CHARINDEX(':',EARLYOUT)))>24 then '00:00' else EARLYOUT end), 0) +
			isnull(datediff(s, 0, case when convert(int,SUBSTRING(LATEIN,0,CHARINDEX(':',LATEIN)))>24 then '00:00' else LATEIN end), 0), 0) as time(0))
	FROM #TEMPMONTHLYATTENDANCE

	--------------------------------------------------------------------------------------------
	-- Flatten to the pipe-style output string.
	-- Replaces the original row-by-row WHILE loop (one singleton SELECT per calendar day).
	-- ", TYPE ... .value()" keeps '&' in holiday descriptions from being XML-escaped.
	--------------------------------------------------------------------------------------------
	DECLARE @OutputString NVARCHAR(MAX)

	SELECT @OutputString = ISNULL
	(
		(
			SELECT CAST(SINO AS NVARCHAR(25)) + ','
				 + ISNULL(EMPLOYEE_ID,'')     + ','
				 + ISNULL(DATE,'')            + ','
				 + ISNULL(SHIFT_SHORTNAME,'') + ','
				 + ISNULL(HRSWORK,'')         + ','
				 + ISNULL(STATUS,'')          + ','
				 + ISNULL(STATUS2,'')         + ','
				 + ISNULL(EXPHRS,'')          + '%'
			FROM #TEMPMYCALENDAR
			ORDER BY SINO
			FOR XML PATH(''), TYPE
		).value('.', 'NVARCHAR(MAX)'), ''
	)

	-- strip the trailing '%' separator
	IF LEN(@OutputString) > 0
		SET @OutputString = SUBSTRING(@OutputString, 1, LEN(@OutputString) - 1)

	SELECT @OutputString AS 'MyCalendarOutput'

	DROP TABLE #EMPLEVEL
	DROP TABLE #EMPDESIGNATION
	DROP TABLE #EMPTYPE
	DROP TABLE #EMPCATEGORY
	DROP TABLE #EMPPROCESSEDDATA
	DROP TABLE #LEVELRIGHTS
	DROP TABLE #TEMPPROCESSEDDATA
	DROP TABLE #TEMPEMPLOYEE
	DROP TABLE #TEMPMONTHLYATTENDANCE
	DROP TABLE #TEMPMYCALENDAR
END
GO


CREATE OR ALTER PROCEDURE [dbo].[Proc_TA_SelectWFHEntryForTable]
	@Employee_FirstName nvarchar(50) = NULL,
	@Employee_Code_Id nvarchar(15) = NULL,
	@StartDate date = NULL,
	@EndDate date = NULL,
	@EmpSwipeCard varchar(15) = NULL,
	@UserGroupName nvarchar(250),
	@UserLevels varchar(8000) = NULL,
	@GradeLevel nvarchar(20) = NULL,
	@EmpAutoId varchar(10),
	@ParentID int
AS
BEGIN
	SET NOCOUNT ON;

	CREATE TABLE #LevelRights_WFH (LevelId int);
	CREATE NONCLUSTERED INDEX IDX_LevelId_WFHList ON #LevelRights_WFH(LevelId);
	INSERT INTO #LevelRights_WFH SELECT * FROM dbo.fn_TA_GetParentChild(@UserGroupName, @ParentID) AS LevelId;

	DECLARE @isReportingto int;
	SELECT @isReportingto = ReportsBasedOn FROM TA_Tbl_Usergroup WHERE groupName = @UserGroupName;

	CREATE TABLE #EmployeeIds_WFH (EmpAutoId int);
	INSERT INTO #EmployeeIds_WFH SELECT * FROM dbo.fn_TA_GetMappedEmployees_ReportingTo(CAST(@EmpAutoId AS int)) AS EmpAutoId;
	INSERT INTO #EmployeeIds_WFH SELECT * FROM dbo.fn_TA_GetMappedEmployees_SancAuthority(CAST(@EmpAutoId AS int)) AS EmpAutoId;

	SELECT W.WFH_ID, W.Emp_Id, E.Employee_Code_Id,
		E.Employee_FirstName + ISNULL(' ' + E.Employee_MiddleName, '') + ' ' + ISNULL(E.Employee_LastName, '') AS Employee_Name,
		FirstDetail.WFHDate AS FromDate, LastDetail.WFHDate AS ToDate,
		FirstDetail.[Session] AS FromSession, LastDetail.[Session] AS ToSession,
		W.WFH_Reason, W.Remarks, W.WFH_Status, W.WarningGenerated, W.TimeStamp
	FROM TA_Tbl_WFHEntry W
	INNER JOIN TA_Tbl_Employee E ON E.Employee_Code_Id = W.WFH_EmployeeID AND E.IsDeleted = 0
	INNER JOIN TA_Tbl_EmployeeLevel el ON E.Employee_Id = el.EmployeeId
	INNER JOIN TA_Tbl_LevelDetails ld ON el.LevelId = ld.id
	INNER JOIN #LevelRights_WFH lr ON lr.LevelId = ld.id
	OUTER APPLY (
		SELECT TOP 1 D.WFHDate, D.[Session]
		FROM TA_Tbl_WFHEntryDetail D
		WHERE D.WFH_ID = W.WFH_ID
		ORDER BY D.WFHDate ASC
	) FirstDetail
	OUTER APPLY (
		SELECT TOP 1 D.WFHDate, D.[Session]
		FROM TA_Tbl_WFHEntryDetail D
		WHERE D.WFH_ID = W.WFH_ID
		ORDER BY D.WFHDate DESC
	) LastDetail
	WHERE W.Isdeleted = 0
		AND E.Employee_Status = 1
		AND (@isReportingto IS NULL OR @isReportingto = 0 OR E.Employee_Id IN (SELECT EmpAutoId FROM #EmployeeIds_WFH))
		AND (@Employee_Code_Id IS NULL OR @Employee_Code_Id = '' OR E.Employee_Code_Id = @Employee_Code_Id)
		AND (@Employee_FirstName IS NULL OR @Employee_FirstName = ''
			OR (E.Employee_FirstName + ' ' + ISNULL(E.Employee_LastName, '')) = @Employee_FirstName)
		AND (@EmpSwipeCard IS NULL OR @EmpSwipeCard = '' OR E.SwipeCardNo = @EmpSwipeCard)
		AND (@StartDate IS NULL OR @EndDate IS NULL OR (
			(FirstDetail.WFHDate BETWEEN @StartDate AND @EndDate) OR (LastDetail.WFHDate BETWEEN @StartDate AND @EndDate)
			OR (@StartDate BETWEEN FirstDetail.WFHDate AND LastDetail.WFHDate) OR (@EndDate BETWEEN FirstDetail.WFHDate AND LastDetail.WFHDate)
		))
	ORDER BY W.WFH_ID DESC
	OPTION (RECOMPILE);

	DROP TABLE #LevelRights_WFH;
	DROP TABLE #EmployeeIds_WFH;
END
GO

-- ----------------------------------------------------------------------------
-- Verification (run after applying).
-- ----------------------------------------------------------------------------
SELECT OBJECT_DEFINITION(OBJECT_ID('Proc_TA_SelectWFHEntryForTable')) AS Definition;
-- With real data: EXEC as a Dept Head's @EmpAutoId/@UserGroupName/@ParentID (same values PRO's
-- own List screen session-sources) and confirm it returns exactly that Dept Head's reportees'
-- WFH requests -- same employee set Proc_TA_SelectManualEntryOnDutyForTable returns for PRO,
-- cross-checked against the same login.

USE [Timecheck_CUS]
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_EmpLeavePolicy]    Script Date: 17-09-2026 10:12:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Proc_TA_EmpLeavePolicy] 
(
@PolicyID INT
)
as
Begin
SET NOCOUNT ON;
	select LP.Policy_Id,PolicyName,LT.LeaveType_Id,LeaveType,MaxDays_applicable,isnull(MaxDays_Month,'1.0') AS MaxDays_Month from TA_Tbl_LeavePolicyDetails LP INNER JOIN TA_Tbl_LeaveType LT ON LT.LeaveType_Id=LP.LeaveType_Id INNER JOIN TA_Tbl_LeavePolicy L ON L.Policy_Id=LP.Policy_Id where LP.policy_id=@PolicyID
 End




GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_InsertEmpLeavePolicydetails]    Script Date: 17-09-2026 10:22:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   procedure [dbo].[Proc_TA_InsertEmpLeavePolicydetails] 
	@EmployeeId INT,
	@EmpLeavepolicyID INT,
	@PolicyID INT,
	@LeaveType_Id INT,
	@No_Of_Days DECIMAL,
	@auEmpId INT,
	@Maxdays_month float
AS
BEGIN
SET NOCOUNT ON;
declare @sql as nvarchar(3000)
set @sql = 'INSERT INTO TA_Tbl_Emp_LeavePolicyDetails (EmpID,Emp_LeavePolicyId,Policy_ID,LeaveType_Id,No_Of_Days,Isdeleted,Emp_Id,Maxdays_month)
            VALUES ('+cast(@EmployeeId as nvarchar(25))+','+cast(@EmpLeavepolicyID as nvarchar(25))+','+cast(@PolicyID as nvarchar(25))+','+cast(@LeaveType_Id as nvarchar(25))+','+cast(@No_Of_Days as nvarchar(25))+',0,'+cast(@auEmpId as nvarchar(25))+','+Cast(@Maxdays_month As float)+')'
exec(@sql)
END
