using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5CommonFunction;
using GB5Shared.GB5Exception;
using GB5Shared.PickListGenerator;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using PayRollDAL.CustomeCode.OULevelSetting;
using PayRollDAL.DTO.DailyAttendance;
using PayRollDAL.Query.DailyAttendance;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using static GB5Shared.DTO.Framework.Enum.FrameworkEnumDTO;

namespace PayRollDAL.CustomeCode.DailyAttendance
{
    public class DailyAttendanceDAL : IDailyAttendanceDAL
    {
        private readonly IQueryExecutor _queryexecutor;
        private readonly IGB5CommonFunction _IGB5CommonFunction;
        private readonly IPickListExecutor pickListExecutor;
        private readonly IOULevelSettingDAL _ouLevelSettingDAL;

        public DailyAttendanceDAL(IQueryExecutor IQueryExecutor, IGB5CommonFunction IGB5CommonFunction, IPickListExecutor IPickListExecutor, IOULevelSettingDAL OULevelSettingDAL)
        {
            _queryexecutor = IQueryExecutor;
            _IGB5CommonFunction = IGB5CommonFunction;
            pickListExecutor = IPickListExecutor;
            _ouLevelSettingDAL = OULevelSettingDAL;
        }

        public async Task<string> GetDailyAttendanceEmployeeDetail(
            int EmployeeId,
            CriteriaDTO CriteriaDTO,
            LoginDTO LoginDTO)
        {
            try
            {
                int withErrorLog = 1;
                string periodFromDate = string.Empty;
                string periodToDate = string.Empty;

                if (CriteriaDTO?.SectionCriteriaList != null)
                {
                    foreach (var section in CriteriaDTO.SectionCriteriaList)
                    {
                        foreach (var attr in section?.AttributesCriteriaList ?? new())
                        {
                            string field = attr?.FieldName?.ToLower() ?? string.Empty;
                            string value = attr?.FieldValue?.ToString() ?? string.Empty;

                            switch (field)
                            {
                                case "witherrorlog":
                                    int.TryParse(value, out withErrorLog);
                                    break;

                                case "periodfromdate":
                                    periodFromDate = ParseDateValue(value)
                                        .ToString("dd-MMM-yyyy");
                                    break;

                                case "periodtodate":
                                    periodToDate = ParseDateValue(value)
                                        .ToString("dd-MMM-yyyy");
                                    break;
                            }
                        }
                    }
                }

                var parameters = new DynamicParameters();
                parameters.Add("employeeid", EmployeeId);

                string sql = DailyAttendanceQB.GET_DAILYATTENDANCESINGLE_SQL;

                ApplyDailyEmployeeDetailSql(
                    ref sql,
                    CriteriaDTO,
                    parameters,
                    "");

                ApplyDailyEmployeeDetailSql(
                    ref sql,
                    LoginDTO.UserCriteriaDTO,
                    parameters,
                    "_u");

                sql += "\r\n ORDER BY dailyatten0_.attendancedate";

                var dailyAttendanceDTOs =
                    (await _queryexecutor.QueryAsync<DailyAttendanceDTO>(
                        LoginDTO,
                        sql,
                        parameters))
                    .ToList();

                if (withErrorLog == 0)
                {
                    if (string.IsNullOrWhiteSpace(periodFromDate) ||
                        string.IsNullOrWhiteSpace(periodToDate))
                    {
                        throw new MethodNotAllowedException(
                            "From Date and To Date must be given for errorlog.");
                    }
                }

                var empParams = new DynamicParameters();
                empParams.Add("employeeid", EmployeeId);

                var empDTOs =
                    (await _queryexecutor.QueryAsync<DailyAttendanceDTO>(
                        LoginDTO,
                        DailyAttendanceQB.GET_EMPLOYEESINGLE,
                        empParams))
                    .ToList();

                dailyAttendanceDTOs.AddRange(empDTOs);

                return JsonConvert.SerializeObject(dailyAttendanceDTOs);
            }
            catch
            {
                throw;
            }
        }

        private static DateTime ParseDateValue(string value)
        {
            if (string.IsNullOrWhiteSpace(value))
                throw new ArgumentException("Date value cannot be empty.");

            // New format: yyyy-MM-dd
            if (DateTime.TryParse(value, out DateTime dt))
                return dt;

            // Old GB4 format: Unix milliseconds
            if (long.TryParse(value, out long milliseconds))
                return new DateTime(1970, 1, 1).AddMilliseconds(milliseconds);

            throw new ArgumentException($"Invalid date format: {value}");
        }

        // epoch > 9_999_999_999 → 13-digit milliseconds; otherwise 10-digit seconds.
        // Matches the same threshold used in MMReportsDAL.FromEpoch.
        private static DateTime FromEpoch(long epoch)
        {
            return epoch > 9_999_999_999L
                ? DateTimeOffset.FromUnixTimeMilliseconds(epoch).UtcDateTime
                : DateTimeOffset.FromUnixTimeSeconds(epoch).UtcDateTime;
        }

        // GB4: ApplyDailyEmployeeDetailSql — maps PeriodFromDate/PeriodToDate criteria
        // to TDAILYATTENDANCE.ATTENDANCEDATE range filter.
        // suffix prevents DynamicParameters name collision when called twice
        // (once for CriteriaDTO, once for LoginDTO.UserCriteriaDTO).
        private void ApplyDailyEmployeeDetailSql(
            ref string sql,
            CriteriaDTO criteriaDTO,
            DynamicParameters parameters,
            string suffix,
            string dateColumn = "dailyatten0_.attendancedate")
        {
            if (criteriaDTO?.SectionCriteriaList == null)
                return;

            DateTime? fromDate = null;
            DateTime? toDate = null;

            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null)
                    continue;

                foreach (var attr in section.AttributesCriteriaList)
                {
                    string field = attr?.FieldName?.ToLower() ?? string.Empty;
                    string value = attr?.FieldValue?.ToString() ?? string.Empty;

                    if (string.IsNullOrWhiteSpace(value))
                        continue;

                    switch (field)
                    {
                        case "periodfromdate":
                            fromDate = ParseDateValue(value);
                            break;

                        case "periodtodate":
                            toDate = ParseDateValue(value);
                            break;
                    }
                }
            }

            if (fromDate.HasValue)
            {
                string pFrom = $"PeriodFromDate{suffix}";

                sql += $@"
AND {dateColumn} >= @{pFrom}";

                parameters.Add(pFrom, fromDate.Value.Date);
            }

            if (toDate.HasValue)
            {
                string pTo = $"PeriodToDate{suffix}";

                sql += $@"
AND {dateColumn} <= @{pTo}";

                parameters.Add(pTo, toDate.Value.Date);
            }
        }

        public async Task<short> GetEmployeeShift(int employeeId, LoginDTO loginDTO)
        {
            string sql = @"
                SELECT MS.SHIFTMINUTES
                FROM MEMPLOYEE ME
                JOIN MSHIFT MS ON ME.SHIFTID = MS.SHIFTID
                WHERE ME.EMPLOYEEID = @EmployeeId";
            short shiftMinutes = await _queryexecutor.QuerySingleAsync<short>(loginDTO, sql, new { EmployeeId = employeeId });
            return shiftMinutes;
        }

        public async Task<byte> GetLeaveIncludeHoliday(int leaveId, LoginDTO loginDTO)
        {
            return await _queryexecutor.QuerySingleAsync<byte>(
                loginDTO, DailyAttendanceQB.GET_LEAVE_INCLUDEHOLIDAY, new { leaveid = leaveId });
        }

        // GB4 parity: ATTENDANCE_DAY_TYPE_FOR_EMPLOYEE + ApplyLeaveLoadGrid (PeriodFromDate/PeriodToDate range)
        // + PREVIOUSDAYTYPE exclusion (2 = Holiday, 9 = Week-off) when the leave does not include holidays.
        public async Task<List<PayRollDAL.DTO.Leave.LeaveRequestGridLoadDTO>> GetAttendanceDayTypeForEmployee(int employeeId, CriteriaDTO CriteriaDTO, LoginDTO loginDTO, bool includeHoliday)
        {
            string sql = DailyAttendanceQB.ATTENDANCE_DAY_TYPE_FOR_EMPLOYEE;

            var parameters = new DynamicParameters();
            parameters.Add("employeeid", employeeId);
            parameters.Add("ouid", loginDTO.WorkOUId);

            ApplyDailyEmployeeDetailSql(ref sql, CriteriaDTO, parameters, "", "a.DETAILDATE");

            if (!includeHoliday)
            {
                sql += "\r\n AND ISNULL(b.PREVIOUSDAYTYPE, 0) NOT IN (2,9)";
            }

            var result = await _queryexecutor.QueryAsync<PayRollDAL.DTO.Leave.LeaveRequestGridLoadDTO>(loginDTO, sql, parameters);
            return result.ToList();
        }

        public async Task<List<DailyAttendanceReportDTO>> GetDailyAttendanceReport(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool Count = false)
        {
            try
            {
                string sql = "";
                String Type = ""; //0-Daily Absent,1-Late Arrival,2 - Early Departure;
                String AbsentDays = "";
                String EmployeeCount = "";
                string PeriodFrom = "";
                string PeriodTo = "";
                String LateType = "";
                String EarlyType = "";
                String HourReplace = ""; //0-LateIn,1-EarlyOut,2-ExtraHours,3-ShortHours,4-WorkingHours,5-OTHOURS,6-BUSHOURS
                string InnerReplace = "";
                string OuterReplace = "";
                string HoursType = "0"; //0-LateIn,1-EarlyOut,2-ExtraHours,3-ShortHours,4-WorkingHours,5-OTHOURS,6-BUSHOURS
                string IncludeGraceMinutes = "1";
                string LateArrivalDetailReport = "1"; //0 -Yes,1-No
                string OUId = "";
                string IncludeShiftTime = "1";
                String ShiftType = "0";
                string PayPeriodId = "-1";
                string ReportType = "0";
                string SHORTHOURS = "";
                if (CriteriaDTO != null)
                {
                    for (int i = 0; i < CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList.Count; i++)
                    {
                        if ((CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldName).ToLower()! == ("type").ToLower())
                        {
                            Type = Convert.ToString(CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue)!;
                        }

                        if ((CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldName).ToLower()! == ("absentdays").ToLower())
                        {
                            AbsentDays = Convert.ToString(CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue)!;
                        }
                        if ((CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldName).ToLower()! == ("ouid").ToLower()
                            ||
                            (CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldName).ToLower()! == ("workingorganizationunit").ToLower()
                            )
                        {
                            OUId = Convert.ToString(CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue)!;
                        }
                        if (CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldName.ToLower()! == "periodfrom")
                        {
                            DateTime TempDate = await _IGB5CommonFunction.FromEpoch(Convert.ToInt64(CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue));
                            PeriodFrom = TempDate.ToString("yyyy/MM/dd");
                        }
                        if (CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldName.ToLower()! == "periodto")
                        {
                            DateTime TempDate = await _IGB5CommonFunction.FromEpoch(Convert.ToInt64(CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue));
                            PeriodTo = TempDate.ToString("yyyy/MM/dd");
                        }
                        if ((CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldName).ToLower()! == ("latetype").ToLower())
                        {
                            LateType = Convert.ToString(CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue)!;
                        }
                        if ((CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldName).ToLower()! == ("earlytype").ToLower())
                        {
                            EarlyType = Convert.ToString(CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue)!;
                        }
                        if ((CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldName).ToLower()! == ("hourreplace").ToLower())
                        {
                            HourReplace = Convert.ToString(CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue)!;
                        }
                        if ((CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldName).ToLower()! == ("innerreplace").ToLower())
                        {
                            InnerReplace = Convert.ToString(CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue)!;
                        }
                        if ((CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldName).ToLower()! == ("outerreplace").ToLower())
                        {
                            OuterReplace = Convert.ToString(CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue)!;
                        }
                        if ((CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldName).ToLower()! == ("hourstype").ToLower())
                        {
                            HoursType = Convert.ToString(CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue)!;
                        }
                        if ((CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldName).ToLower()! == ("includegraceminutes").ToLower())
                        {
                            IncludeGraceMinutes = Convert.ToString(CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue)!;
                        }
                        if ((CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldName).ToLower()! == ("latearrivaldetailreport").ToLower())
                        {
                            LateArrivalDetailReport = Convert.ToString(CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue)!;
                        }
                        if ((CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldName).ToLower()! == ("includeshifttime").ToLower())
                        {
                            IncludeShiftTime = Convert.ToString(CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue)!;
                        }
                        if ((CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldName).ToLower()! == ("shifttype").ToLower())
                        {
                            ShiftType = Convert.ToString(CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue)!;
                        }
                        if ((CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldName).ToLower()! == ("payperiodid").ToLower())
                        {
                            PayPeriodId = Convert.ToString(CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue)!;
                        }
                        if ((CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldName).ToLower()! == ("reporttype").ToLower())
                        {
                            ReportType = Convert.ToString(CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue)!;
                        }
                        if ((CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldName).ToLower()! == ("shorthours").ToLower())
                        {
                            SHORTHOURS = Convert.ToString(CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue)!;
                        }
                    }
                }
                if (ReportType == "0")
                {
                    if (PeriodFrom == "1899/01/01")
                    {
                        throw new NotFoundException("Posting not done for this period.. Please select correct Date");
                    }
                    if (PeriodTo == "9999/12/31 00:00:00")
                    {
                        throw new NotFoundException("Posting not done for this period.. Please select correct Date");
                    }
                }
                else
                {
                    if (PayPeriodId == "")
                    {
                        throw new NotFoundException("Please Select PayPeriod from Filter");
                    }
                }
                #region LateArrivalDetail Fields Redmine 19767

                if (HoursType == "0")
                {
                    if (IncludeGraceMinutes == "0")
                    {
                        HourReplace = "case when (datt.InTime-shift.StartTime)-shift.graceminutes > 0 then datt.INTIME/60 +    CAST(  cast((datt.INTIME-(datt.INTIME/60)*60) as numeric(18,2))  /100 as numeric(18,2)) else 0 end as Time, " + "\r\n";
                    }
                    else
                    {
                        HourReplace = "case when (datt.InTime-shift.StartTime) > 0 then datt.INTIME/60 +    CAST(  cast((datt.INTIME-(datt.INTIME/60)*60) as numeric(18,2))  /100 as numeric(18,2)) else 0 end as Time, " + "\r\n";
                    }
                }
                if (HoursType == "1")
                {
                    HourReplace = "case when shift.EndTime-datt.OutTime >0  then CAST(  cast((datt.OutTime-(datt.OutTime/60)*60) as numeric(18,2))  /100 as numeric(18,2)) else 0 end as Time," + "\r\n";
                }
                if (HoursType == "2")
                {
                    HourReplace = "case when datt.EXTRAHOURS >0  then CONVERT(NUMERIC(18, 2),cast(  (datt.ExtraHours / 60)  AS varchar(10))+'.'+ cast((datt.ExtraHours % 60) AS varchar(10) )) else 0 end as Time," + "\r\n";
                }
                if (HoursType == "3")
                {
                    HourReplace = "case when datt.SHORTHOURS >0  then CONVERT(NUMERIC(18, 2),cast(  (datt.SHORTHOURS / 60)  AS varchar(10))+'.'+ cast((datt.SHORTHOURS % 60) AS varchar(10) )) else 0 end as Time," + "\r\n";
                }
                if (HoursType == "4")
                {
                    HourReplace = "case when datt.WORKEDHOURS >0  then CONVERT(NUMERIC(18, 2),cast(  (datt.WORKEDHOURS / 60)  AS varchar(10))+'.'+ cast((datt.WORKEDHOURS % 60) AS varchar(10) )) else 0 end as Time," + "\r\n";
                }
                if (HoursType == "5")
                {
                    HourReplace = "case when datt.OTHOURS >0  then CONVERT(NUMERIC(18, 2),cast(  (datt.OTHOURS / 60)  AS varchar(10))+'.'+ cast((datt.OTHOURS % 60) AS varchar(10) )) else 0 end as Time," + "\r\n";
                }
                if (HoursType == "6")
                {
                    HourReplace = "case when datt.BUSHOURS >0  then CONVERT(NUMERIC(18, 2),cast(  (datt.BUSHOURS / 60)  AS varchar(10))+'.'+ cast((datt.BUSHOURS % 60) AS varchar(10) )) else 0 end as Time," + "\r\n";
                }
                if (HoursType == "0")
                {
                    if (IncludeGraceMinutes == "0")
                    {
                        InnerReplace = "   left outer join (select  datt.employeeid, count (case when (datt.InTime-shift.StartTime)-shift.graceminutes > 0 then datt.ATTENDANCEDATE else 0 end) AS NoofTime,SUM(datt.InTime-shift.StartTime-shift.graceminutes) as TotalMinutes" + "\r\n";
                    }
                    else
                    {
                        InnerReplace = "   left outer join (select  datt.employeeid, count (case when (datt.InTime-shift.StartTime) > 0 then datt.ATTENDANCEDATE else 0 end) AS NoofTime,SUM(datt.InTime-shift.StartTime) as TotalMinutes" + "\r\n";
                    }
                    if (IncludeGraceMinutes == "0")
                    {
                        OuterReplace = "from TDAILYATTENDANCE datt,MSHIFT shift where 1=1 and datt.SHIFTID =shift.SHIFTID and datt.ATTENDANCEDATE >=':periodfrom' and datt.ATTENDANCEDATE <=':periodto' and (datt.InTime-shift.StartTime-shift.graceminutes) > 0 group by datt.EMPLOYEEID  ) late on (late.EMPLOYEEID =datt.employeeid)" + "\r\n";
                    }
                    else
                    {
                        OuterReplace = "from TDAILYATTENDANCE datt,MSHIFT shift where 1=1 and datt.SHIFTID =shift.SHIFTID and datt.ATTENDANCEDATE >=':periodfrom' and datt.ATTENDANCEDATE <=':periodto' and (datt.InTime-shift.StartTime) > 0 group by datt.EMPLOYEEID  ) late on (late.EMPLOYEEID =datt.employeeid)" + "\r\n";
                    }
                }
                if (HoursType == "1")
                {
                    InnerReplace = "   left outer join (select  datt.employeeid, count (case when (shift.EndTime-datt.OutTime) > 0 then datt.ATTENDANCEDATE else 0 end) AS NoofTime,SUM(shift.EndTime-datt.OutTime) as TotalMinutes" + "\r\n";
                    OuterReplace = "from TDAILYATTENDANCE datt,MSHIFT shift where 1=1 and datt.SHIFTID =shift.SHIFTID and datt.ATTENDANCEDATE >=':periodfrom' and datt.ATTENDANCEDATE <=':periodto' and (shift.EndTime-datt.OutTime) > 0 group by datt.EMPLOYEEID  ) late on (late.EMPLOYEEID =datt.employeeid)" + "\r\n";
                }
                if (HoursType == "2")
                {
                    InnerReplace = "   left outer join (select  datt.employeeid, count (case when (datt.EXTRAHOURS) > 0 then datt.ATTENDANCEDATE else 0 end) AS NoofTime,SUM(datt.EXTRAHOURS) as TotalMinutes" + "\r\n";
                    OuterReplace = "from TDAILYATTENDANCE datt,MSHIFT shift where 1=1 and datt.SHIFTID =shift.SHIFTID and datt.ATTENDANCEDATE >=':periodfrom' and datt.ATTENDANCEDATE <=':periodto' and (datt.EXTRAHOURS) > 0 group by datt.EMPLOYEEID  ) late on (late.EMPLOYEEID =datt.employeeid)" + "\r\n";
                }
                if (HoursType == "3")
                {
                    InnerReplace = "   left outer join (select  datt.employeeid, count (case when (datt.SHORTHOURS) > 0 then datt.ATTENDANCEDATE else 0 end) AS NoofTime,SUM(datt.SHORTHOURS) as TotalMinutes" + "\r\n";
                    OuterReplace = "from TDAILYATTENDANCE datt,MSHIFT shift where 1=1 and datt.SHIFTID =shift.SHIFTID and datt.ATTENDANCEDATE >=':periodfrom' and datt.ATTENDANCEDATE <=':periodto' and (datt.SHORTHOURS) > 0 group by datt.EMPLOYEEID  ) late on (late.EMPLOYEEID =datt.employeeid)" + "\r\n";
                }
                if (HoursType == "4")
                {
                    InnerReplace = "   left outer join (select  datt.employeeid, count (case when (datt.WORKEDHOURS) > 0 then datt.ATTENDANCEDATE else 0 end) AS NoofTime,SUM(datt.WORKEDHOURS) as TotalMinutes" + "\r\n";
                    OuterReplace = "from TDAILYATTENDANCE datt,MSHIFT shift where 1=1 and datt.SHIFTID =shift.SHIFTID and datt.ATTENDANCEDATE >=':periodfrom' and datt.ATTENDANCEDATE <=':periodto' and (datt.WORKEDHOURS) > 0 group by datt.EMPLOYEEID  ) late on (late.EMPLOYEEID =datt.employeeid)" + "\r\n";
                }
                if (HoursType == "5")
                {
                    InnerReplace = "   left outer join (select  datt.employeeid, count (case when (datt.OTHOURS) > 0 then datt.ATTENDANCEDATE else 0 end) AS NoofTime,SUM(datt.OTHOURS) as TotalMinutes" + "\r\n";
                    OuterReplace = "from TDAILYATTENDANCE datt,MSHIFT shift where 1=1 and datt.SHIFTID =shift.SHIFTID and datt.ATTENDANCEDATE >=':periodfrom' and datt.ATTENDANCEDATE <=':periodto' and (datt.OTHOURS) > 0 group by datt.EMPLOYEEID  ) late on (late.EMPLOYEEID =datt.employeeid)" + "\r\n";
                }
                if (HoursType == "6")
                {
                    InnerReplace = "   left outer join (select  datt.employeeid, count (case when (datt.BUSHOURS) > 0 then datt.ATTENDANCEDATE else 0 end) AS NoofTime,SUM(datt.BUSHOURS) as TotalMinutes" + "\r\n";
                    OuterReplace = "from TDAILYATTENDANCE datt,MSHIFT shift where 1=1 and datt.SHIFTID =shift.SHIFTID and datt.ATTENDANCEDATE >=':periodfrom' and datt.ATTENDANCEDATE <=':periodto' and (datt.BUSHOURS) > 0 group by datt.EMPLOYEEID  ) late on (late.EMPLOYEEID =datt.employeeid)" + "\r\n";
                }
                #endregion

                if (ReportType == "0")
                {
                    sql = DailyAttendanceQB.GET_DAILYATTENDANCEREPORT;
                }
                else
                {
                    sql = DailyAttendanceQB.GET_DAILYATTENDANCEREPORT_PAYPERIOD_TEMP;

                    sql = await GetDailyAttendanceAnalysisPayPeriod(sql, CriteriaDTO!, LoginDTO);
                    sql = await GetDailyAttendanceAnalysisPayPeriod(sql, LoginDTO.UserCriteriaDTO, LoginDTO);
                    await _queryexecutor.ExecuteAsync(LoginDTO, sql, null!);

                    sql = DailyAttendanceQB.GET_DAILYATTENDANCEREPORT_PAYPERIOD_BASED;
                }

                if (Type == "0")
                {
                    sql = sql + "and datt.DayType in(1,8,5,4)" + "\r\n";

                    if (IncludeShiftTime == "0")
                    {
                        sql = sql + "and shift.STARTTIME<=datepart(minute,GETDATE())+  datepart(HOUR,GETDATE())*60" + "\r\n";
                    }
                }
                if (Type == "1")
                {
                    sql = sql + "and (datt.InTime-shift.StartTime) > 0 " + "\r\n";
                }
                if (Type == "2")
                {
                    sql = sql + "and (shift.EndTime-datt.OutTime) > 0 " + "\r\n";
                    sql = sql + "and datt.EARLYOUT > 0 " + "\r\n";
                }

                if (LateType == "0")
                {
                    if (IncludeGraceMinutes == "0")
                    {
                        sql = sql + "and (datt.LATEIN - shift.graceminutes ) > 0" + "\r\n";
                    }
                    else
                    {
                        sql = sql + "and (datt.LATEIN ) > 0" + "\r\n";
                    }
                }
                if (EarlyType == "0")
                {
                    sql = sql + "and (datt.EARLYOUT) > 0" + "\r\n";
                }
                if (LateArrivalDetailReport == "0")
                {
                    sql = sql + "and late.TotalMinutes <>0" + "\r\n";
                }

                if (OUId != "")
                {
                    sql = sql + "and datt.OUId IN(" + OUId + ")";
                }
                if (ShiftType == "1")
                {
                    sql = sql + "and emp.SHIFTID<>-1" + "\r\n";
                }
                if (ReportType == "0")
                {
                    if (SHORTHOURS == "0")
                    {
                        sql = sql + "and datt.SHORTHOURS-shift.GRACEMINUTES > 0" + "\r\n";
                    }
                }

                sql = await GetDailyAttendanceAnalysisReport(sql, CriteriaDTO!, LoginDTO);
                sql = await GetDailyAttendanceAnalysisReport(sql, LoginDTO.UserCriteriaDTO, LoginDTO);

                if (AbsentDays != "")
                {
                    EmployeeCount = " and  datt.employeeid in(select daily.EmployeeId from tDailyAttendance daily ";
                    EmployeeCount += "where daily.ATTENDANCEDATE >=':periodfrom' and daily.ATTENDANCEDATE <=':periodto' and daily.DayType in(8) group by daily.EMPLOYEEID having Count(*)=" + AbsentDays + ")";
                }
                sql = sql.Replace(":employeecount", EmployeeCount);
                sql = sql.Replace(":periodfrom", PeriodFrom);
                sql = sql.Replace(":periodto", PeriodTo);
                sql = sql.Replace(":hourreplace", HourReplace);
                sql = sql.Replace(":innerreplace", InnerReplace);
                sql = sql.Replace(":outerreplace", OuterReplace);

                if (Type == "0")
                {
                    sql = sql + "order by" + "\r\n";
                    sql = sql + "dept.departmentName,datt.AttendanceDate" + "\r\n";
                }
                else
                {
                    sql = sql + "order by" + "\r\n";
                    sql = sql + "datt.AttendanceDate,dept.departmentName,datt.Location,emp.SortOrder" + "\r\n";
                }

                var paramNames = new
                {
                    periodfrom = PeriodFrom,
                    periodto = PeriodTo,
                    ouid = OUId,
                    includeshifttime = IncludeShiftTime,
                    includegraceminutes = IncludeGraceMinutes,
                    shifttype = ShiftType,
                    reporttype = ReportType
                };

                if (Count == false)
                {
                    sql += $@"
                            OFFSET {FirstNumber} ROWS
                            FETCH NEXT {MaxResult} ROWS ONLY
                            ";

                    var result = await _queryexecutor.SessionQueryAsync<DailyAttendanceReportDTO>(
                        LoginDTO,
                        sql,
                        paramNames
                    );

                    List<DailyAttendanceReportDTO> DailyAttendanceReportDTOs = result.ToList();

                    for (int i = 0; i < DailyAttendanceReportDTOs.Count; i++)
                    {
                        DailyAttendanceReportDTOs[i].DailyAttendanceCalInTime = MinutesIntoTime(Convert.ToInt32(DailyAttendanceReportDTOs[i].DailyAttendanceInTime));
                        DailyAttendanceReportDTOs[i].DailyAttendanceCalOutTime = MinutesIntoTime(Convert.ToInt32(DailyAttendanceReportDTOs[i].DailyAttendanceOutTime));
                        DailyAttendanceReportDTOs[i].DailyAttendanceCalEarlyIn = MinutesIntoTime(Convert.ToInt32(DailyAttendanceReportDTOs[i].DailyAttendanceEarlyIn));
                        DailyAttendanceReportDTOs[i].DailyAttendanceCalEarlyOut = MinutesIntoTime(Convert.ToInt32(DailyAttendanceReportDTOs[i].DailyAttendanceEarlyOut));
                        DailyAttendanceReportDTOs[i].DailyAttendanceCalOtHours = MinutesIntoTime(Convert.ToInt32(DailyAttendanceReportDTOs[i].DailyAttendanceOtHours));
                        DailyAttendanceReportDTOs[i].DailyAttendanceCalExtraHours = MinutesIntoTime(Convert.ToInt32(DailyAttendanceReportDTOs[i].DailyAttendanceExtraHours));
                        DailyAttendanceReportDTOs[i].DailyAttendanceCalLateIn = MinutesIntoTime(Convert.ToInt32(DailyAttendanceReportDTOs[i].DailyAttendanceLateIn));
                        DailyAttendanceReportDTOs[i].DailyAttendanceCalLateOut = MinutesIntoTime(Convert.ToInt32(DailyAttendanceReportDTOs[i].DailyAttendanceLateOut));
                        DailyAttendanceReportDTOs[i].DailyAttendanceCalBusHours = MinutesIntoTime(Convert.ToInt32(DailyAttendanceReportDTOs[i].DailyAttendanceBusHours));
                        DailyAttendanceReportDTOs[i].DailyAttendanceCalPerHours = MinutesIntoTime(Convert.ToInt32(DailyAttendanceReportDTOs[i].DailyAttendancePerHours));
                        DailyAttendanceReportDTOs[i].DailyAttendanceCalLateOut = MinutesIntoTime(Convert.ToInt32(DailyAttendanceReportDTOs[i].DailyAttendanceLateOut));
                        DailyAttendanceReportDTOs[i].ShiftCalEndTime = MinutesIntoTime(Convert.ToInt32(DailyAttendanceReportDTOs[i].ShiftEndTime));
                        DailyAttendanceReportDTOs[i].ShiftCalStartTime = MinutesIntoTime(Convert.ToInt32(DailyAttendanceReportDTOs[i].ShiftStartTime));
                        DailyAttendanceReportDTOs[i].DailyAttendanceCalWorkedHours = MinutesIntoTime(Convert.ToInt32(DailyAttendanceReportDTOs[i].DailyAttendanceWorkedHours));
                        DailyAttendanceReportDTOs[i].DailyAttendanceCallDuration = MinutesIntoTime(Convert.ToInt32(DailyAttendanceReportDTOs[i].DailyAttendanceWorkedHours));
                        DailyAttendanceReportDTOs[i].DailyAttendanceCalTotalWorkedHrs = MinutesIntoTime(Convert.ToInt32(DailyAttendanceReportDTOs[i].TotalWorkedHrs));
                        DailyAttendanceReportDTOs[i].CallTime = MinutesIntoTime(Convert.ToInt32(DailyAttendanceReportDTOs[i].Time));
                    }
                    return DailyAttendanceReportDTOs;
                }
                else
                {
                    sql = DailyAttendanceQB.GET_DAILYATTENDANCEREPORT_COUNT;
                    sql = await GetDailyAttendanceAnalysisReport(sql, CriteriaDTO!, LoginDTO);
                    sql = await GetDailyAttendanceAnalysisReport(sql, LoginDTO.UserCriteriaDTO, LoginDTO);
                    sql = sql.Replace(":employeecount", EmployeeCount);
                    sql = sql.Replace(":periodfrom", PeriodFrom);
                    sql = sql.Replace(":periodto", PeriodTo);
                    sql = sql.Replace(":hourreplace", HourReplace);
                    sql = sql.Replace(":innerreplace", InnerReplace);
                    sql = sql.Replace(":outerreplace", OuterReplace);

                    var paramNames1 = new
                    {
                        periodfrom = PeriodFrom,
                        periodto = PeriodTo,
                        ouid = OUId,
                        includeshifttime = IncludeShiftTime,
                        includegraceminutes = IncludeGraceMinutes,
                        shifttype = ShiftType,
                        reporttype = ReportType,
                        latetype = LateType
                    };

                    if (Type == "0")
                    {
                        sql = sql + "and datt.DayType in(1,8,5,4)" + "\r\n";

                        if (IncludeShiftTime == "0")
                        {
                            sql = sql + "and shift.STARTTIME<=datepart(minute,GETDATE())+  datepart(HOUR,GETDATE())*60" + "\r\n";
                        }
                    }
                    if (Type == "1")
                    {
                        sql = sql + "and (datt.InTime-shift.StartTime) > 0 " + "\r\n";
                    }
                    if (Type == "2")
                    {
                        sql = sql + "and (shift.EndTime-datt.OutTime) > 0 " + "\r\n";
                    }

                    if (LateType == "0")
                    {
                        if (IncludeGraceMinutes == "0")
                        {
                            sql = sql + "and (datt.LATEIN - shift.graceminutes ) > 0" + "\r\n";
                        }
                        else
                        {
                            sql = sql + "and (datt.LATEIN ) > 0" + "\r\n";
                        }
                    }
                    if (EarlyType == "0")
                    {
                        sql = sql + "and (datt.EARLYOUT) > 0" + "\r\n";
                    }
                    if (LateArrivalDetailReport == "0")
                    {
                        sql = sql + "and late.TotalMinutes <>0" + "\r\n";
                    }

                    if (OUId != "")
                    {
                        sql = sql + "and datt.OUId IN(" + OUId + ")";
                    }
                    if (ShiftType == "1")
                    {
                        sql = sql + "and emp.SHIFTID<>-1" + "\r\n";
                    }
                    if (ReportType == "0")
                    {
                        if (SHORTHOURS == "0")
                        {
                            sql = sql + "and datt.SHORTHOURS-shift.GRACEMINUTES > 0" + "\r\n";
                        }
                    }

                    List<DailyAttendanceReportDTO> DailyAttendanceReportDTOs = new List<DailyAttendanceReportDTO>();

                    int Total = await _queryexecutor.TotalCountSql(sql, LoginDTO, true);
                    DailyAttendanceReportDTO DailyAttendanceReportDTO = new DailyAttendanceReportDTO();
                    DailyAttendanceReportDTO.NumberOfRecords = Total;
                    DailyAttendanceReportDTOs.Add(DailyAttendanceReportDTO);
                    return DailyAttendanceReportDTOs;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static string MinutesIntoTime(int minutes)
        {
            if (minutes <= 0)
                return "00:00";

            int hours = minutes / 60;
            int remainingMinutes = minutes % 60;

            return $"{hours:D2}:{remainingMinutes:D2}";
        }

        public async Task<String> GetDailyAttendanceAnalysisPayPeriod(String SQL, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                if (CriteriaDTO != null)
                {
                    string[,] CriteriaWithQueryField = new string[,]
                    {
                        {"PayPeriodId", "p.PayPeriodId"},
                    };

                    SQL = await pickListExecutor.ApplyCriteria(SQL, CriteriaWithQueryField, CriteriaDTO, LoginDTO);
                }
                return SQL;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<String> GetDailyAttendanceAnalysisReport(String SQL, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                if (CriteriaDTO != null)
                {
                    string[,] CriteriaWithQueryField = new string[,]
                    {
                        {"EmployeeId", "datt.EMPLOYEEID"},
                        {"ShiftId", "datt.SHIFTID"},
                        {"DepartmentId", "datt.DEPARTMENTID"},
                        {"WorkTypeId", "datt.WORKTYPEID"},
                        {"PeriodFrom", "datt.AttendanceDate"},
                        {"PeriodTo", "datt.AttendanceDate"},
                        {"DayType", "datt.DayType"},
                        {"OUId", "datt.ouid"},
                        {"EmployeeTypeId", "emp.EMPLOYEETYPEID"},
                        {"StaffTypeId", "emp.STAFFTYPEID"},
                        {"LEAVEID", "datt.LEAVEID"},
                        {"PayGroupId", "emp.PAYGROUPID"},
                        {"CodeDefineId", "emp.CODEDEFINEID"},
                        {"UnitId","emp.UNITID"},
                        {"LocationId","emp.LOCATIONID"},
                        {"ReportingtoEmployeeId", "emp.REPORTINGTOEMPLOYEEID"},
                        {"AdminReportingTo", "emp.ADMINREPORTINGTOID"},
                        {"TextDayType", "datt.TEXTDAYTYPE"},
                        {"PayConfigurationId", "emp.PAYCONFIGURATIONID"},
                        {"PartyId", "head.PartyId"},
                        {"PartyBranchId", "head.PartyBranchId"},
                        {"DivisionId", "ouunit.DIVISIONID"},
                        {"BranchId", "ouunit.BRANCHID"},
                        {"EmployeeStatus", "emp.EmployeeStatus"},
                        {"CurrentOUId","emp.CURRENTOUID"},
                    };

                    SQL = await pickListExecutor.ApplyCriteria(SQL, CriteriaWithQueryField, CriteriaDTO, LoginDTO);
                }
                return SQL;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<IList<DailyAttendanceLoadFromPunchDTO>> LoadDailyAttendanceFromPunch(
            DailyAttendanceFilterDTO DailyAttendanceFilterDTO, LoginDTO LoginDTO,
            int FirstNumber, int MaxResult, bool Count)
        {
            try
            {
                if (DailyAttendanceFilterDTO == null)
                {
                    // GB4 parity: throw new NotFoundException(ExceptionMessages.UNID0157);
                    throw new NotFoundException();
                }

                string AttendanceDate = "";
                if (DailyAttendanceFilterDTO.AttendanceDate != 0)
                {
                    DateTime TempFromDate = await _IGB5CommonFunction.FromEpoch(DailyAttendanceFilterDTO.AttendanceDate);
                    AttendanceDate = TempFromDate.ToString("yyyy/MM/dd");
                }
                if (AttendanceDate == "")
                {
                    // GB4 parity: throw new NotFoundException(ExceptionMessages.UNID0158);
                    throw new NotFoundException();
                }

                IList<DailyAttendanceLoadFromPunchDTO> DailyAttendanceLoadFromPunchDTOs =
                    new List<DailyAttendanceLoadFromPunchDTO>();

                if (Count == false)
                {
                    string SqlPunchSite = DailyAttendanceQB.GET_PUNCH_BASED_ON_SITE;
                    SqlPunchSite = SqlPunchSite.Replace(":attendancedate", AttendanceDate);
                    SqlPunchSite = SqlPunchSite.Replace(":ouid", LoginDTO.WorkOUId.ToString());

                    string SqlMmDetail = DailyAttendanceQB.GET_MMDETAIL_AND_POSTINGPOSITIONID;

                    string SqlLoadFromPunch = DailyAttendanceQB.LOAD_DAILY_ATTENDANCE_FROM_PUNCH;

                    var parameters = new DynamicParameters();
                    string ReplaceFields = "";
                    string InnerReplaceFields = "";

                    void AddFilter(string column, int value, string paramName)
                    {
                        if (value == -1) return;
                        ReplaceFields += $" and {column}=@{paramName}\r\n";
                        InnerReplaceFields += $" and {column}=@{paramName}\r\n";
                        parameters.Add(paramName, value);
                    }

                    AddFilter("emp.EmployeeId", DailyAttendanceFilterDTO.EmployeeId, "employeeid");
                    AddFilter("emp.WorkOUID", DailyAttendanceFilterDTO.OuId, "ouid");
                    AddFilter("emp.DepartMentId", DailyAttendanceFilterDTO.DepartmentId, "departmentid");
                    AddFilter("emp.EMPLOYEETYPEID", DailyAttendanceFilterDTO.EmployeeTypeId, "employeetypeid");
                    AddFilter("emp.PAYGROUPID", DailyAttendanceFilterDTO.PayGroupId, "paygroupid");
                    AddFilter("emp.STAFFTYPEID", DailyAttendanceFilterDTO.StaffTypeId, "stafftypeid");
                    AddFilter("emp.UNITID", DailyAttendanceFilterDTO.UnitId, "unitid");
                    AddFilter("emp.RELIGIONID", DailyAttendanceFilterDTO.ReligionId, "religionid");
                    AddFilter("emp.DIVISIONID", DailyAttendanceFilterDTO.DivisionId, "divisionid");
                    AddFilter("emp.EMPLOYEEBANKBRANCHID", DailyAttendanceFilterDTO.OurBranchId, "ourbranchid");
                    AddFilter("emp.PAYCONFIGURATIONID", DailyAttendanceFilterDTO.PayConfigurationId, "payconfigurationid");

                    if (DailyAttendanceFilterDTO.ShiftId != -1)
                    {
                        ReplaceFields += " and case when shifdailyatt.shiftid is null then shiftemp.SHIFTID else shifdailyatt.shiftid end=@shiftid\r\n";
                        parameters.Add("shiftid", DailyAttendanceFilterDTO.ShiftId);
                    }

                    // Determine sort once, reuse for both SqlLoadFromPunch (emp.*) and SqlFinal (a.*)
                    string finalOrderByColumn;

                    if (DailyAttendanceFilterDTO.EmployeeCodeSort == 1 && DailyAttendanceFilterDTO.EmployeeNameSort == 1)
                    {
                        SqlLoadFromPunch += " Order By emp.SORTORDER \r\n";
                        finalOrderByColumn = "a.EmployeeId";
                    }
                    else if (DailyAttendanceFilterDTO.EmployeeCodeSort == 0 && DailyAttendanceFilterDTO.EmployeeNameSort == 1)
                    {
                        bool asc = DailyAttendanceFilterDTO.EmployeeCodeSortASCDesc == 0;
                        SqlLoadFromPunch += asc
                            ? " Order By emp.EMPLOYEECODE asc\r\n"
                            : " Order By emp.EMPLOYEECODE desc\r\n";
                        finalOrderByColumn = asc ? "a.EmployeeCode asc" : "a.EmployeeCode desc";
                    }
                    else if (DailyAttendanceFilterDTO.EmployeeCodeSort == 1 && DailyAttendanceFilterDTO.EmployeeNameSort == 0)
                    {
                        bool asc = DailyAttendanceFilterDTO.EmployeeNameSortASCDesc == 0;
                        SqlLoadFromPunch += asc
                            ? " Order By emp.EMPLOYEENAME asc\r\n"
                            : " Order By emp.EMPLOYEENAME desc\r\n";
                        finalOrderByColumn = asc ? "a.EmployeeName asc" : "a.EmployeeName desc";
                    }
                    else
                    {
                        finalOrderByColumn = "a.EmployeeId"; // safe default when no sort flags are set
                    }

                    SqlLoadFromPunch = SqlLoadFromPunch.Replace(":replacefields", ReplaceFields);
                    SqlLoadFromPunch = SqlLoadFromPunch.Replace(":innerreplacefields", InnerReplaceFields);
                    SqlLoadFromPunch = SqlLoadFromPunch.Replace(":attendancedate", AttendanceDate);
                    SqlLoadFromPunch = SqlLoadFromPunch.Replace(":ouid", LoginDTO.WorkOUId.ToString());
                    SqlLoadFromPunch = SqlLoadFromPunch.Replace(":periodid", LoginDTO.WorkPeriodId.ToString());
                    SqlLoadFromPunch = SqlLoadFromPunch.Replace(":replacetemptable", " into #tempdaily");

                    string SqlFinal = DailyAttendanceQB.GET_FINAL_MULTI_EMPLOYEE_LOAD_ATTENDNANCE;
                    SqlFinal += $@"
                                ORDER BY {finalOrderByColumn}
                                OFFSET {FirstNumber} ROWS
                                FETCH NEXT {MaxResult} ROWS ONLY
                                ";

                    // Single batch on one connection — #TEMP / #TEMP1 / #tempdaily all survive
                    // because they're created and consumed within the same command execution.
                    string BatchSql =
                        SqlPunchSite + ";\r\n" +
                        SqlMmDetail + ";\r\n" +
                        SqlLoadFromPunch + ";\r\n" +
                        SqlFinal;

                    var result = await _queryexecutor.QueryAsync<DailyAttendanceLoadFromPunchDTO>(
                        LoginDTO, BatchSql, parameters);

                    DailyAttendanceLoadFromPunchDTOs = result.ToList();
                }
                else
                {
                    string Sql = DailyAttendanceQB.LOAD_DAILY_ATTENDANCE_FROM_PUNCH_COUNT;
                    Sql = Sql.Replace(":attendancedate", AttendanceDate);
                    Sql = Sql.Replace(":ouid", LoginDTO.WorkOUId.ToString());

                    int Total = await _queryexecutor.TotalCountSql(Sql, LoginDTO, isCountQuery: true);
                    DailyAttendanceLoadFromPunchDTOs.Add(new DailyAttendanceLoadFromPunchDTO { Count = Total });
                }

                return DailyAttendanceLoadFromPunchDTOs;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task LoadAndUpdateDailyAttendanceFromPunch(
    DailyAttendanceFilterDTO DailyAttendanceFilterDTO, LoginDTO LoginDTO)
        {
            try
            {
                if (DailyAttendanceFilterDTO == null)
                {
                    throw new NotFoundException();
                }

                int diff = 1;
                DateTime attendanceFromDate = DateTime.Now;
                DateTime tempFromDate = DateTime.Now;
                string attendanceDateStr = "";

                if (DailyAttendanceFilterDTO.AttendanceFromDate != 0)
                {
                    attendanceFromDate = await _IGB5CommonFunction.FromEpoch(DailyAttendanceFilterDTO.AttendanceFromDate);

                    var lastDayOfFromMonth = new DateTime(
                        attendanceFromDate.Year, attendanceFromDate.Month,
                        DateTime.DaysInMonth(attendanceFromDate.Year, attendanceFromDate.Month));

                    if (DailyAttendanceFilterDTO.AttendanceToDate != 0)
                    {
                        // Frontend sent explicit toDate — use it, but cap to month-end
                        tempFromDate = await _IGB5CommonFunction.FromEpoch(DailyAttendanceFilterDTO.AttendanceToDate);
                        if (tempFromDate.Date > lastDayOfFromMonth.Date)
                            tempFromDate = lastDayOfFromMonth;
                    }
                    else if (DailyAttendanceFilterDTO.AttendanceDate != 0)
                    {
                        // Legacy: frontend sent AttendanceDate as toDate — cap to month-end
                        tempFromDate = await _IGB5CommonFunction.FromEpoch(DailyAttendanceFilterDTO.AttendanceDate);
                        if (tempFromDate.Date > lastDayOfFromMonth.Date)
                            tempFromDate = lastDayOfFromMonth;
                    }
                    else
                    {
                        // Neither toDate field sent — default to last day of fromDate's month
                        tempFromDate = lastDayOfFromMonth;
                    }

                    attendanceDateStr = tempFromDate.ToString("yyyy/MM/dd");

                    TimeSpan t = tempFromDate - attendanceFromDate;
                    diff = Convert.ToInt32(t.TotalDays) + 1;
                }

                // Only use AttendanceDate alone (no range) when AttendanceFromDate was NOT provided.
                // When AttendanceFromDate IS provided, tempFromDate and attendanceDateStr were already
                // set (with month-cap) in the block above — do not overwrite them here.
                if (DailyAttendanceFilterDTO.AttendanceDate != 0
                    && DailyAttendanceFilterDTO.AttendanceFromDate == 0)
                {
                    tempFromDate = await _IGB5CommonFunction.FromEpoch(DailyAttendanceFilterDTO.AttendanceDate);
                    attendanceDateStr = tempFromDate.ToString("yyyy/MM/dd");
                }
                if (attendanceDateStr == "")
                {
                    throw new NotFoundException();
                }

                // GB4 parity — exact message, including the double-period.
                if (diff > 31)
                {
                    throw new NotFoundException("Bulk Updated can not be more than 31 days..");
                }

                #region Pay-process-already-done guard (start + end checked in parallel)
                {
                    string startDate = DailyAttendanceFilterDTO.AttendanceFromDate != 0
                        ? attendanceFromDate.ToString("yyyy/MM/dd")
                        : attendanceDateStr;

                    string payPeriodStart = DailyAttendanceQB.CHECK_PAY_PROCESS_ALREADY_DONE
                        .Replace(":ouid", LoginDTO.WorkOUId.ToString())
                        .Replace(":attendancedate", startDate);
                    string payPeriodEnd = DailyAttendanceQB.CHECK_PAY_PROCESS_ALREADY_DONE
                        .Replace(":ouid", LoginDTO.WorkOUId.ToString())
                        .Replace(":attendancedate", attendanceDateStr);

                    var startParams = new DynamicParameters();
                    var endParams   = new DynamicParameters();
                    if (DailyAttendanceFilterDTO.PayConfigurationId != -1)
                    {
                        payPeriodStart += "\r\n and c.PAYCONFIGURATIONID=@payconfigurationid";
                        payPeriodEnd   += "\r\n and c.PAYCONFIGURATIONID=@payconfigurationid";
                        startParams.Add("payconfigurationid", DailyAttendanceFilterDTO.PayConfigurationId);
                        endParams.Add("payconfigurationid", DailyAttendanceFilterDTO.PayConfigurationId);
                    }
                    if (DailyAttendanceFilterDTO.EmployeeId != -1)
                    {
                        payPeriodStart += "\r\n and a.EMPLOYEEID=@employeeid";
                        payPeriodEnd   += "\r\n and a.EMPLOYEEID=@employeeid";
                        startParams.Add("employeeid", DailyAttendanceFilterDTO.EmployeeId);
                        endParams.Add("employeeid", DailyAttendanceFilterDTO.EmployeeId);
                    }

                    // Both are read-only checks on independent date ranges — run in parallel
                    var startTask = _queryexecutor.TotalCountSql(payPeriodStart, LoginDTO, startParams, isCountQuery: true);
                    var endTask   = _queryexecutor.TotalCountSql(payPeriodEnd,   LoginDTO, endParams,   isCountQuery: true);
                    await Task.WhenAll(startTask, endTask);

                    if (startTask.Result > 0 || endTask.Result > 0)
                        throw new FoundException();
                }
                #endregion

                // Pre-fetch OU settings and formula cache once for all days in the loop.
                var ouPrefetch = await PreFetchOUDataAsync(LoginDTO);

                #region Per-day punch-to-attendance write — all days run in parallel
                // Each day gets its own SQL batch on its own DB connection (IQueryExecutor opens
                // one connection per ExecuteAsync call), so #TEMP / #TEMP1 temp tables are
                // connection-scoped and never collide between days.
                var dayTasks = Enumerable.Range(0, diff).Select(i =>
                {
                    string dayAttendanceDate = DailyAttendanceFilterDTO.AttendanceFromDate != 0
                        ? attendanceFromDate.AddDays(i).ToString("yyyy/MM/dd")
                        : attendanceDateStr;

                    // Each day's task is self-contained: build SQL, fire batch, run update pass.
                    return Task.Run(async () =>
                    {
                        // #TEMP / #TEMP1 are connection-scoped — no DROP needed when every day
                        // gets its own fresh connection from ExecuteAsync.
                        string sqlPunchSite = DailyAttendanceQB.GET_PUNCH_BASED_ON_SITE
                            .Replace(":attendancedate", dayAttendanceDate)
                            .Replace(":ouid", LoginDTO.WorkOUId.ToString());

                        string sqlMmDetail = DailyAttendanceQB.GET_MMDETAIL_AND_POSTINGPOSITIONID;

                        string sqlUpdateMinus1 = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_WHERE_MMDETAILID_IS_MINUS_1_TO_TOP_1_RECORD
                            .Replace(":attendancedate", dayAttendanceDate);
                        string sqlUpdateFound = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_WHERE_MMDETAILID_IS_FOUND;
                        string sqlInsert = DailyAttendanceQB.INSERT_INTO_TDAILYATTENANCE_IF_ENTRY_FOUND
                            .Replace(":autoid", "0")
                            .Replace(":userid", LoginDTO.UserId.ToString());

                        string sqlLoadFromPunch = DailyAttendanceQB.LOAD_DAILY_ATTENDANCE_FROM_PUNCH
                            .Replace(":replacetemptable", "");

                        var parameters = new DynamicParameters();
                        string replaceFields = "";
                        string innerReplaceFields = "";

                        if (DailyAttendanceFilterDTO.EmployeeId != -1)
                        {
                            replaceFields      += " and emp.EmployeeId=@employeeid\r\n";
                            innerReplaceFields += " and emp.EmployeeId=@employeeid\r\n";
                            parameters.Add("employeeid", DailyAttendanceFilterDTO.EmployeeId);
                        }
                        if (DailyAttendanceFilterDTO.DepartmentId != -1)
                        {
                            replaceFields      += " and emp.DepartMentId=@departmentid\r\n";
                            innerReplaceFields += " and emp.DepartMentId=@departmentid\r\n";
                            parameters.Add("departmentid", DailyAttendanceFilterDTO.DepartmentId);
                        }
                        if (DailyAttendanceFilterDTO.PayConfigurationId != -1)
                        {
                            replaceFields      += " and emp.PAYCONFIGURATIONID=@payconfigurationid\r\n";
                            innerReplaceFields += " and emp.PAYCONFIGURATIONID=@payconfigurationid\r\n";
                            parameters.Add("payconfigurationid", DailyAttendanceFilterDTO.PayConfigurationId);
                        }
                        if (DailyAttendanceFilterDTO.ShiftId != -1)
                        {
                            replaceFields += " and case when shifdailyatt.shiftid is null then shiftemp.SHIFTID else shifdailyatt.shiftid end=@shiftid\r\n";
                            parameters.Add("shiftid", DailyAttendanceFilterDTO.ShiftId);
                        }
                        if (DailyAttendanceFilterDTO.OuId != -1)
                        {
                            if (DailyAttendanceFilterDTO.OuId == -2100000000)
                            {
                                replaceFields      += " and emp.WorkOUID<>-1\r\n";
                                innerReplaceFields += " and emp.WorkOUID<>-1\r\n";
                            }
                            else
                            {
                                replaceFields      += " and emp.WorkOUID=@ouid\r\n";
                                innerReplaceFields += " and emp.WorkOUID=@ouid\r\n";
                                parameters.Add("ouid", DailyAttendanceFilterDTO.OuId);
                            }
                        }

                        sqlLoadFromPunch = sqlLoadFromPunch
                            .Replace(":replacefields",      replaceFields)
                            .Replace(":innerreplacefields", innerReplaceFields)
                            .Replace(":attendancedate",     dayAttendanceDate)
                            .Replace(":ouid",               LoginDTO.WorkOUId.ToString())
                            .Replace(":periodid",           LoginDTO.WorkPeriodId.ToString());

                        string finalWriteSql =
                            DailyAttendanceQB.UPDATE_LOAD_DAILYATTENDNACE.Replace(":userid", LoginDTO.UserId.ToString()) +
                            " " + sqlLoadFromPunch + " " +
                            DailyAttendanceQB.UPDATE_LOAD_DAILYATTENDNACE2;

                        // Build the masterBatch SQL inline (same logic as UpdateDailyattendanceforSameAndAcrossDate)
                        // and append it directly — merges 2 ExecuteAsync calls into 1 per day.
                        string masterBatchSql = BuildSameAndAcrossDateBatchSql(
                            dayAttendanceDate, DailyAttendanceFilterDTO, LoginDTO, ouPrefetch);

                        string batchSql =
                            sqlPunchSite    + ";\r\n" +
                            sqlMmDetail     + ";\r\n" +
                            sqlUpdateMinus1 + ";\r\n" +
                            sqlUpdateFound  + ";\r\n" +
                            sqlInsert       + ";\r\n" +
                            finalWriteSql   + ";\r\n" +
                            masterBatchSql;

                        await _queryexecutor.ExecuteAsync(LoginDTO, batchSql, parameters);

                        // OT/break calls need DateTime + fire per-OU — run after the main batch.
                        var dayFilter = new DailyAttendanceFilterDTO
                        {
                            EmployeeId         = DailyAttendanceFilterDTO.EmployeeId,
                            OuId               = DailyAttendanceFilterDTO.OuId,
                            DepartmentId       = DailyAttendanceFilterDTO.DepartmentId,
                            ShiftId            = DailyAttendanceFilterDTO.ShiftId,
                            PayConfigurationId = DailyAttendanceFilterDTO.PayConfigurationId,
                            AttendanceFromDate = DailyAttendanceFilterDTO.AttendanceFromDate,
                            IsProcessAsynch    = DailyAttendanceFilterDTO.IsProcessAsynch,
                            AttendanceDate     = ToEpoch(DateTime.Parse(dayAttendanceDate).AddMinutes(LoginDTO.ServiceOffSet)),
                        };
                        await RunOTAndBreakTimeAsync(dayFilter, LoginDTO, ouPrefetch);
                    });
                }).ToList();

                await Task.WhenAll(dayTasks);
                #endregion

                // GB4 behaviour: after the per-day loop, when processing a date range,
                // run the fine-tuned range pass (OT cap, break-time cross-day, half-leave span).
                if (diff > 1)
                {
                    await UpdateDailyattendanceforSameAndAcrossDateFineTuned(
                        attendanceFromDate, tempFromDate, DailyAttendanceFilterDTO, LoginDTO);
                }

                #region Previous/next-day weekoff recalculation
                // BLOCKING: needs DailyAttendanceQB.UPDATE_DAILYATTENDANCE_PREVIOUS_NEXT_DAY SQL.
                {
                    string fromAttDate, toAttDate;
                    if (DailyAttendanceFilterDTO.AttendanceFromDate == 0)
                    {
                        fromAttDate = tempFromDate.ToString("dd-MMM-yyyy");
                        toAttDate = fromAttDate;
                    }
                    else
                    {
                        fromAttDate = attendanceFromDate.ToString("dd-MMM-yyyy");
                        toAttDate = tempFromDate.ToString("dd-MMM-yyyy");
                    }

                    // QB comment: all 5 params use :param colon notation — must use string.Replace,
                    // NOT Dapper @binding, because :fromdate/:todate are pre-quoted in the SQL template.
                    string sqlPrevNext = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_PREVIOUS_NEXT_DAY
                        .Replace(":payconfigurationid", DailyAttendanceFilterDTO.PayConfigurationId.ToString())
                        .Replace(":fromdate", fromAttDate)
                        .Replace(":todate", toAttDate)
                        .Replace(":ouid", (LoginDTO.WorkOUId == -2100000000 ? -1 : LoginDTO.WorkOUId).ToString())
                        .Replace(":employeeid", DailyAttendanceFilterDTO.EmployeeId.ToString());

                    await _queryexecutor.ExecuteAsync(LoginDTO, sqlPrevNext);
                }
                #endregion

                #region Single/double-punch out-time correction
                // BLOCKING: needs DailyAttendanceQB.UPDATE_OUT_TIME_DURING_BULK_UPDATE SQL.
                {
                    string fromAttDate, toAttDate;
                    if (DailyAttendanceFilterDTO.AttendanceFromDate == 0)
                    {
                        fromAttDate = tempFromDate.ToString("dd-MMM-yyyy");
                        toAttDate = fromAttDate;
                    }
                    else
                    {
                        fromAttDate = attendanceFromDate.ToString("dd-MMM-yyyy");
                        toAttDate = tempFromDate.ToString("dd-MMM-yyyy");
                    }

                    string replaceFields = " and a.ATTENDANCEDATE>=@fromdate\r\n and a.ATTENDANCEDATE<=@todate\r\n";
                    var outTimeParams = new DynamicParameters();
                    outTimeParams.Add("fromdate", fromAttDate);
                    outTimeParams.Add("todate", toAttDate);

                    if (DailyAttendanceFilterDTO.EmployeeId != -1)
                    {
                        replaceFields += " and b.EmployeeId=@employeeid\r\n";
                        outTimeParams.Add("employeeid", DailyAttendanceFilterDTO.EmployeeId);
                    }
                    if (DailyAttendanceFilterDTO.DepartmentId != -1)
                    {
                        replaceFields += " and b.DepartMentId=@departmentid\r\n";
                        outTimeParams.Add("departmentid", DailyAttendanceFilterDTO.DepartmentId);
                    }
                    if (DailyAttendanceFilterDTO.PayConfigurationId != -1)
                    {
                        replaceFields += " and b.PAYCONFIGURATIONID=@payconfigurationid\r\n";
                        outTimeParams.Add("payconfigurationid", DailyAttendanceFilterDTO.PayConfigurationId);
                    }
                    if (DailyAttendanceFilterDTO.OuId != -1)
                    {
                        if (DailyAttendanceFilterDTO.OuId == -2100000000)
                        {
                            replaceFields += " and b.WorkOUID<>-1\r\n";
                        }
                        else
                        {
                            replaceFields += " and b.WorkOUID=@ouid\r\n";
                            outTimeParams.Add("ouid", DailyAttendanceFilterDTO.OuId);
                        }
                    }

                    string sqlOutTime = DailyAttendanceQB.UPDATE_OUT_TIME_DURING_BULK_UPDATE
                        .Replace(":replacefields", replaceFields);

                    if (DailyAttendanceFilterDTO.ShiftId != -1)
                    {
                        sqlOutTime += "\r\n and Tdailyattendance.shiftid=@shiftid\r\n";
                        outTimeParams.Add("shiftid", DailyAttendanceFilterDTO.ShiftId);
                    }

                    await _queryexecutor.ExecuteAsync(LoginDTO, sqlOutTime, outTimeParams);
                }
                #endregion
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task UpdateMMDetailAndPostingPostion(string AttendanceDate, LoginDTO LoginDTO)
        {
            try
            {
                string sqlPunchSite = DailyAttendanceQB.GET_PUNCH_BASED_ON_SITE;
                sqlPunchSite = sqlPunchSite.Replace(":attendancedate", AttendanceDate);
                sqlPunchSite = sqlPunchSite.Replace(":ouid", LoginDTO.WorkOUId.ToString());

                string sqlMmDetail = DailyAttendanceQB.GET_MMDETAIL_AND_POSTINGPOSITIONID;

                // BLOCKING: needs DailyAttendanceQB.UPDATE_DAILYATTENDANCE_WHERE_MMDETAILID_IS_MINUS_1_TO_TOP_1_RECORD
                string sqlUpdateMinus1 = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_WHERE_MMDETAILID_IS_MINUS_1_TO_TOP_1_RECORD;
                sqlUpdateMinus1 = sqlUpdateMinus1.Replace(":attendancedate", AttendanceDate);

                // BLOCKING: needs DailyAttendanceQB.UPDATE_DAILYATTENDANCE_WHERE_MMDETAILID_IS_FOUND
                string sqlUpdateFound = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_WHERE_MMDETAILID_IS_FOUND;

                // All six statements share one connection: #TEMP and #TEMP1 are local
                // temp tables that only survive within the connection that created them.
                // AutoNumber is not wired in yet — using 0 as the start offset so the
                // INSERT uses ROW_NUMBER() starting from 1 (BLOCKING: wire AutoNumber
                // service to get the real start ID before shipping to production).
                string sqlInsert = DailyAttendanceQB.INSERT_INTO_TDAILYATTENANCE_IF_ENTRY_FOUND
                    .Replace(":autoid", "0")
                    .Replace(":userid", LoginDTO.UserId.ToString());

                string batchSql =
                    sqlPunchSite + ";\r\n" +
                    sqlMmDetail + ";\r\n" +
                    sqlUpdateMinus1 + ";\r\n" +
                    sqlUpdateFound + ";\r\n" +
                    sqlInsert;

                await _queryexecutor.ExecuteAsync(LoginDTO, batchSql, null!);
            }
            catch (Exception)
            {
                throw;
            }
        }
        // Consolidates GB4's repeated "if OuId != -1 { -2100000000 ? <>-1 : =X }" pattern
        // that appears ~15 times across this method — same semantics, less repetition.
        private static void AddOuFilter(ref string target, string alias, int ouId, DynamicParameters parameters, string paramName)
        {
            if (ouId == -1) return;
            if (ouId == -2100000000)
            {
                target += $" and {alias}.WorkOUID<>-1\r\n";
            }
            else
            {
                target += $" and {alias}.WorkOUID=@{paramName}\r\n";
                parameters.Add(paramName, ouId);
            }
        }

        public async Task UpdateDailyattendanceforSameAndAcrossDateFineTuned(
            DateTime FromDate, DateTime ToDate,
            DailyAttendanceFilterDTO DailyAttendanceFilterDTO, LoginDTO LoginDTO)
        {
            try
            {
                string fromAttendanceDate = FromDate.ToString("yyyy/MM/dd");
                string toAttendanceDate = ToDate.ToString("yyyy/MM/dd");

                var ouLevelSettings = await _ouLevelSettingDAL.GetOULevelSetting(LoginDTO.WorkOUId, LoginDTO);
                if (ouLevelSettings.Count == 0)
                    throw new MethodNotAllowedException("There is no oulevelsetting created for login ou..");
                int otEligableMinutes          = ouLevelSettings[0].OULevelSettingOTEligableMinutes;
                int maximumOtInMonth           = ouLevelSettings[0].OULevelSettingMaximumOTInMonth;
                int maxAllowedOtForMale        = ouLevelSettings[0].OULevelSettingMaxAllowedOTForMale;
                int maxAllowedOtForFemale      = ouLevelSettings[0].OULevelSettingMaxAllowedOTForFemale;
                int maleOtHoursInEvenDays      = ouLevelSettings[0].OULevelSettingMaleOTHoursInEvenDays;
                int femaleOtHoursInEvenDays    = ouLevelSettings[0].OULevelSettingFemaleOTHoursInEvenDays;
                int maleOtHoursInOddDays       = ouLevelSettings[0].OULevelSettingMaleOTHoursInOddDays;
                int femaleOtHoursInOddDays     = ouLevelSettings[0].OULevelSettingFemaleOTHoursInOddDays;
                int batchOtPiRunDays           = Convert.ToInt32(ouLevelSettings[0].OULevelSettingBatchOTPIRunDays);
                int otExpressionFormulaId      = ouLevelSettings[0].OTExpressionFormulaId;
                int breakTimeExpressionFormulaId = ouLevelSettings[0].BreakTimeExpressionFormulaId;

                #region Bug #9744 — base field sync
                {
                    var parameters = new DynamicParameters();
                    string replaceFields = "";
                    replaceFields += " and Tdailyattendance.ATTENDANCEDATE>=@fromdate\r\n and Tdailyattendance.ATTENDANCEDATE<=@todate\r\n";
                    parameters.Add("fromdate", fromAttendanceDate);
                    parameters.Add("todate", toAttendanceDate);

                    if (DailyAttendanceFilterDTO.EmployeeId != -1)
                    {
                        replaceFields += " and Tdailyattendance.EmployeeId=@employeeid\r\n";
                        parameters.Add("employeeid", DailyAttendanceFilterDTO.EmployeeId);
                    }
                    if (DailyAttendanceFilterDTO.ShiftId != -1)
                    {
                        replaceFields += " and Tdailyattendance.shiftid=@shiftid\r\n";
                        parameters.Add("shiftid", DailyAttendanceFilterDTO.ShiftId);
                    }
                    AddOuFilter(ref replaceFields, "emp", DailyAttendanceFilterDTO.OuId, parameters, "ouid");
                    if (DailyAttendanceFilterDTO.DepartmentId != -1)
                    {
                        replaceFields += " and emp.DepartMentId=@departmentid\r\n";
                        parameters.Add("departmentid", DailyAttendanceFilterDTO.DepartmentId);
                    }

                    // BLOCKING: DailyAttendanceQB.UPDATE_DAILYATTENDANCE_FIELD
                    string sql = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_FIELD.Replace(":replacefields", replaceFields);
                    await _queryexecutor.ExecuteAsync(LoginDTO, sql, parameters);
                }
                #endregion

                #region Worked hours from TPUNCH
                {
                    var whParams = new DynamicParameters();
                    string replaceFields = "";
                    replaceFields += " and TDAILYATTENDANCE.ATTENDANCEDATE>=@fromdate\r\n and TDAILYATTENDANCE.ATTENDANCEDATE<=@todate\r\n";
                    whParams.Add("fromdate", fromAttendanceDate);
                    whParams.Add("todate", toAttendanceDate);
                    if (DailyAttendanceFilterDTO.EmployeeId != -1)
                    {
                        replaceFields += " and TDAILYATTENDANCE.EmployeeId=@employeeid\r\n";
                        whParams.Add("employeeid", DailyAttendanceFilterDTO.EmployeeId);
                    }
                    AddOuFilter(ref replaceFields, "emp", DailyAttendanceFilterDTO.OuId, whParams, "ouid");
                    if (DailyAttendanceFilterDTO.DepartmentId != -1)
                    {
                        replaceFields += " and emp.DepartMentId=@departmentid\r\n";
                        whParams.Add("departmentid", DailyAttendanceFilterDTO.DepartmentId);
                    }
                    if (DailyAttendanceFilterDTO.PayConfigurationId != -1)
                    {
                        replaceFields += " and emp.PAYCONFIGURATIONID=@payconfigurationid\r\n";
                        whParams.Add("payconfigurationid", DailyAttendanceFilterDTO.PayConfigurationId);
                    }

                    // sql1 creates #PUNCH, sql2 reads #PUNCH and creates #punch2, sql3 reads #punch2.
                    // All three must run in one batch on the same connection — temp tables don't
                    // survive across separate ExecuteAsync calls (each opens a new connection).
                    string sql1 = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_WORKEDHOURS_FROM_PUNCH_1
                        .Replace(":fromattendancedate", fromAttendanceDate)
                        .Replace(":toattendancedate", toAttendanceDate)
                        .Replace(":employeeid", DailyAttendanceFilterDTO.EmployeeId != -1 ? DailyAttendanceFilterDTO.EmployeeId.ToString() : "-1");
                    string sql2 = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_IN_TIME_OUT_TIME_BASED_ON_SHIFT_WORK_METHOD
                        .Replace(":replacefields", replaceFields);
                    string sql3 = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_WORKEDHOURS_FROM_PUNCH_2
                        .Replace(":replacefields", replaceFields);

                    await _queryexecutor.ExecuteAsync(LoginDTO, sql1 + ";\r\n" + sql2 + ";\r\n" + sql3, whParams);
                }
                #endregion

                #region Same-date attendance type
                {
                    var parameters = new DynamicParameters();
                    string replaceFields = "", innerReplace = "";
                    replaceFields += " and Tdailyattendance.ATTENDANCEDATE>=@fromdate\r\n and Tdailyattendance.ATTENDANCEDATE<=@todate\r\n";
                    innerReplace += " and a.ATTENDANCEDATE>=@fromdate\r\n and a.ATTENDANCEDATE<=@todate\r\n";
                    parameters.Add("fromdate", fromAttendanceDate);
                    parameters.Add("todate", toAttendanceDate);
                    if (DailyAttendanceFilterDTO.EmployeeId != -1)
                    {
                        replaceFields += " and Tdailyattendance.EmployeeId=@employeeid\r\n";
                        innerReplace += " and a.EmployeeId=@employeeid\r\n";
                        parameters.Add("employeeid", DailyAttendanceFilterDTO.EmployeeId);
                    }
                    if (DailyAttendanceFilterDTO.ShiftId != -1)
                    {
                        replaceFields += " and Tdailyattendance.shiftid=@shiftid\r\n";
                        parameters.Add("shiftid", DailyAttendanceFilterDTO.ShiftId);
                    }
                    AddOuFilter(ref replaceFields, "emp", DailyAttendanceFilterDTO.OuId, parameters, "ouid");
                    if (DailyAttendanceFilterDTO.DepartmentId != -1)
                    {
                        replaceFields += " and emp.DepartMentId=@departmentid\r\n";
                        parameters.Add("departmentid", DailyAttendanceFilterDTO.DepartmentId);
                    }

                    // BLOCKING: DailyAttendanceQB.GENERATE_TEMP_RECORD_FOR_PUNCH_FOR_ATTENDANCEDATE
                    string sqlTemp = DailyAttendanceQB.GENERATE_TEMP_RECORD_FOR_PUNCH_FOR_ATTENDANCEDATE
                        .Replace(":innerreplace", innerReplace);
                    // BLOCKING: DailyAttendanceQB.UPDATE_DAILYATTENDANCE_IN_CASE_OF_SAME_DATE_ATTENDANCETYPE
                    string sqlUpdate = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_IN_CASE_OF_SAME_DATE_ATTENDANCETYPE
                        .Replace(":replacefields", replaceFields)
                        .Replace(":innerreplace", innerReplace);

                    // NOTE: GENERATE_TEMP_RECORD_... likely populates a temp table that
                    // sqlUpdate reads from — batch these together (same connection-lifetime
                    // rule as everywhere else in this port) once the SQL text confirms it.
                    string batch = sqlTemp + ";\r\n" + sqlUpdate;
                    await _queryexecutor.ExecuteAsync(LoginDTO, batch, parameters);
                }
                #endregion

                #region Across-date attendance type
                {
                    var parameters = new DynamicParameters();
                    string replaceFields = "", innerReplace = "";
                    replaceFields += " and Tdailyattendance.ATTENDANCEDATE>=@fromdate\r\n and Tdailyattendance.ATTENDANCEDATE<=@todate\r\n";
                    parameters.Add("fromdate", fromAttendanceDate);
                    parameters.Add("todate", toAttendanceDate);
                    if (DailyAttendanceFilterDTO.EmployeeId != -1)
                    {
                        replaceFields += " and Tdailyattendance.EmployeeId=@employeeid\r\n";
                        innerReplace += " and a.EmployeeId=@employeeid\r\n";
                        parameters.Add("employeeid", DailyAttendanceFilterDTO.EmployeeId);
                    }
                    if (DailyAttendanceFilterDTO.ShiftId != -1)
                    {
                        replaceFields += " and Tdailyattendance.shiftid=@shiftid\r\n";
                        parameters.Add("shiftid", DailyAttendanceFilterDTO.ShiftId);
                    }
                    AddOuFilter(ref replaceFields, "emp", DailyAttendanceFilterDTO.OuId, parameters, "ouid");
                    if (DailyAttendanceFilterDTO.DepartmentId != -1)
                    {
                        replaceFields += " and emp.DepartMentId=@departmentid\r\n";
                        parameters.Add("departmentid", DailyAttendanceFilterDTO.DepartmentId);
                    }

                    // BLOCKING: DailyAttendanceQB.UPDATE_DAILYATTENDANCE_IN_CASE_OF_ACROSS_DATE_ATTENDANCETYPE
                    string sql = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_IN_CASE_OF_ACROSS_DATE_ATTENDANCETYPE
                        .Replace(":replacefields", replaceFields)
                        .Replace(":innerreplace", innerReplace);
                    await _queryexecutor.ExecuteAsync(LoginDTO, sql, parameters);
                }
                #endregion

                #region Flexible shift pattern (in-based + out-based)
                {
                    var parameters = new DynamicParameters();
                    string replaceFields = "", flexiReplaceFields = "";
                    replaceFields += " and Tdailyattendance.ATTENDANCEDATE>=@fromdate\r\n and Tdailyattendance.ATTENDANCEDATE<=@todate\r\n";
                    flexiReplaceFields += " and a.ATTENDANCEDATE>=@fromdate\r\n and a.ATTENDANCEDATE<=@todate\r\n";
                    parameters.Add("fromdate", fromAttendanceDate);
                    parameters.Add("todate", toAttendanceDate);
                    if (DailyAttendanceFilterDTO.EmployeeId != -1)
                    {
                        replaceFields += " and Tdailyattendance.EmployeeId=@employeeid\r\n";
                        flexiReplaceFields += " and a.EmployeeId=@employeeid\r\n";
                        parameters.Add("employeeid", DailyAttendanceFilterDTO.EmployeeId);
                    }
                    AddOuFilter(ref replaceFields, "emp", DailyAttendanceFilterDTO.OuId, parameters, "ouid");
                    if (DailyAttendanceFilterDTO.OuId != -1)
                    {
                        string bAlias = DailyAttendanceFilterDTO.OuId == -2100000000 ? " and b.WorkOUID<>-1\r\n" : " and b.WorkOUID=@ouid\r\n";
                        flexiReplaceFields += bAlias;
                    }
                    if (DailyAttendanceFilterDTO.DepartmentId != -1)
                    {
                        replaceFields += " and emp.DepartMentId=@departmentid\r\n";
                        flexiReplaceFields += " and b.DepartMentId=@departmentid\r\n";
                        parameters.Add("departmentid", DailyAttendanceFilterDTO.DepartmentId);
                    }

                    // All 4 steps share #temp/#temp2/#temp3 — must run in one connection
                    string sqlFlexi1 = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_SHIFTID_IN_CASE_OF_FLEXIBLE_SHIFTPATTERN_NEW_1
                        .Replace(":replacefields", flexiReplaceFields);
                    string flexiBatch =
                        sqlFlexi1 + ";\r\n" +
                        DailyAttendanceQB.UPDATE_DAILYATTENDANCE_SHIFTID_IN_CASE_OF_FLEXIBLE_SHIFTPATTERN_2 + ";\r\n" +
                        DailyAttendanceQB.UPDATE_DAILYATTENDANCE_SHIFTID_IN_CASE_OF_FLEXIBLE_SHIFTPATTERN_3 + ";\r\n" +
                        DailyAttendanceQB.UPDATE_DAILYATTENDANCE_SHIFTID_IN_CASE_OF_FLEXIBLE_SHIFTPATTERN_4;
                    await _queryexecutor.ExecuteAsync(LoginDTO, flexiBatch, parameters);

                    // BLOCKING: UPDATE_DAILYATTENDANCE_FIELD_FOR_FLEXISHIFT_EMPLOYEE
                    string sqlFlexiField = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_FIELD_FOR_FLEXISHIFT_EMPLOYEE
                        .Replace(":replacefields", replaceFields);
                    await _queryexecutor.ExecuteAsync(LoginDTO, sqlFlexiField, parameters);

                    // Out-based: all 4 steps share #temp/#temp2/#temp3 — must run in one connection
                    string sqlOutBased1 = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_SHIFTID_IN_CASE_OF_FLEXIBLE_SHIFTPATTERN_NEW_OUTBASED_1
                        .Replace(":replacefields", flexiReplaceFields);
                    string outBasedBatch =
                        sqlOutBased1 + ";\r\n" +
                        DailyAttendanceQB.UPDATE_DAILYATTENDANCE_SHIFTID_IN_CASE_OF_FLEXIBLE_SHIFTPATTERN_OUT_BASED_2 + ";\r\n" +
                        DailyAttendanceQB.UPDATE_DAILYATTENDANCE_SHIFTID_IN_CASE_OF_FLEXIBLE_SHIFTPATTERN_OUT_BASED_3 + ";\r\n" +
                        DailyAttendanceQB.UPDATE_DAILYATTENDANCE_SHIFTID_IN_CASE_OF_FLEXIBLE_SHIFTPATTERN_OUT_BASED_4;
                    await _queryexecutor.ExecuteAsync(LoginDTO, outBasedBatch, parameters);
                    await _queryexecutor.ExecuteAsync(LoginDTO, sqlFlexiField, parameters);
                }
                #endregion

                #region Two-half leave handling
                {
                    string fromDateStr = FromDate.ToString("dd-MMM-yyyy");
                    string toDateStr   = ToDate.ToString("dd-MMM-yyyy");

                    // replaceFields — used by TO_NONE (has "from MEMPLOYEE emp") and LEAVEID (has "from MEMPLOYEE emp")
                    // Both have emp alias in their outer scope, so emp.* filters are valid.
                    string replaceFields = " and Tdailyattendance.ATTENDANCEDATE>=@fromdate\r\n and Tdailyattendance.ATTENDANCEDATE<=@todate\r\n";
                    if (DailyAttendanceFilterDTO.EmployeeId != -1)
                        replaceFields += " and EMP.EmployeeId=@employeeid\r\n";
                    if (DailyAttendanceFilterDTO.OuId != -1)
                        replaceFields += DailyAttendanceFilterDTO.OuId == -2100000000
                            ? " and emp.WorkOUID<>-1\r\n"
                            : " and emp.WorkOUID=@ouid\r\n";
                    if (DailyAttendanceFilterDTO.DepartmentId != -1)
                        replaceFields += " and emp.DepartMentId=@departmentid\r\n";
                    if (DailyAttendanceFilterDTO.PayConfigurationId != -1)
                        replaceFields += " and emp.PAYCONFIGURATIONID=@payconfigurationid\r\n";

                    // outerReplaceFields — used by FIRST_HALF and SECOND_HALF whose outer WHERE has
                    // only TDAILYATTENDANCE and SUB in scope — no emp alias exists there.
                    string outerReplaceFields = " and Tdailyattendance.ATTENDANCEDATE>=@fromdate\r\n and Tdailyattendance.ATTENDANCEDATE<=@todate\r\n";
                    if (DailyAttendanceFilterDTO.EmployeeId != -1)
                        outerReplaceFields += " and TDAILYATTENDANCE.EMPLOYEEID=@employeeid\r\n";

                    // innerReplace — used inside the subquery of FIRST_HALF/SECOND_HALF (MEMPLOYEE EMP alias exists there)
                    string innerReplace = " and b.LEAVEDATE>=@fromdate\r\n and b.LEAVEDATE<=@todate\r\n";
                    if (DailyAttendanceFilterDTO.EmployeeId != -1)
                        innerReplace += " and EMP.EmployeeId=@employeeid\r\n";
                    if (DailyAttendanceFilterDTO.OuId != -1)
                        innerReplace += DailyAttendanceFilterDTO.OuId == -2100000000
                            ? " and EMP.WorkOUID<>-1\r\n"
                            : " and EMP.WorkOUID=@ouid\r\n";
                    if (DailyAttendanceFilterDTO.DepartmentId != -1)
                        innerReplace += " and EMP.DepartMentId=@departmentid\r\n";

                    // Single DynamicParameters covering all @params used across all 4 statements
                    var allParams = new DynamicParameters();
                    allParams.Add("fromdate", fromDateStr);
                    allParams.Add("todate", toDateStr);
                    if (DailyAttendanceFilterDTO.EmployeeId != -1)
                        allParams.Add("employeeid", DailyAttendanceFilterDTO.EmployeeId);
                    if (DailyAttendanceFilterDTO.OuId != -1 && DailyAttendanceFilterDTO.OuId != -2100000000)
                        allParams.Add("ouid", DailyAttendanceFilterDTO.OuId);
                    if (DailyAttendanceFilterDTO.DepartmentId != -1)
                        allParams.Add("departmentid", DailyAttendanceFilterDTO.DepartmentId);
                    if (DailyAttendanceFilterDTO.PayConfigurationId != -1)
                        allParams.Add("payconfigurationid", DailyAttendanceFilterDTO.PayConfigurationId);

                    string sqlToNone = DailyAttendanceQB.FIRST_SECOND_HALF_LEAVE_UPDATE_IN_DAILYATTENDNACE_TO_NONE
                        .Replace(":replacefields", replaceFields);

                    // FIRST_HALF / SECOND_HALF: outer WHERE uses outerReplaceFields (TDAILYATTENDANCE only),
                    // inner subquery uses innerReplace (EMP alias exists inside).
                    string sqlFirstHalf = DailyAttendanceQB.FIRST_HALF_LEAVE_UPDATE_IN_DAILYATTENDNACE
                        .Replace(":replacefields", outerReplaceFields).Replace(":innerreplace", innerReplace).Replace(":ouid", LoginDTO.WorkOUId.ToString());
                    string sqlSecondHalf = DailyAttendanceQB.SECOND_HALF_LEAVE_UPDATE_IN_DAILYATTENDNACE
                        .Replace(":replacefields", outerReplaceFields).Replace(":innerreplace", innerReplace).Replace(":ouid", LoginDTO.WorkOUId.ToString());
                    string sqlLeaveId = DailyAttendanceQB.LEAVEID_BASEDON_TWO_HALF_LEAVE_UPDATE_IN_DAILYATTENDNACE
                        .Replace(":replacefields", replaceFields);

                    await _queryexecutor.ExecuteAsync(LoginDTO,
                        sqlToNone + ";\r\n" + sqlFirstHalf + ";\r\n" + sqlSecondHalf + ";\r\n" + sqlLeaveId, allParams);
                }
                #endregion

                #region Day type 5/10 correction
                {
                    var parameters = new DynamicParameters();
                    string innerReplace = "";
                    innerReplace += " and a.ATTENDANCEDATE>=@fromdate\r\n and a.ATTENDANCEDATE<=@todate\r\n";
                    parameters.Add("fromdate", fromAttendanceDate);
                    parameters.Add("todate", toAttendanceDate);
                    if (DailyAttendanceFilterDTO.EmployeeId != -1)
                    {
                        innerReplace += " and d.EmployeeId=@employeeid\r\n";
                        parameters.Add("employeeid", DailyAttendanceFilterDTO.EmployeeId);
                    }
                    if (DailyAttendanceFilterDTO.ShiftId != -1)
                    {
                        innerReplace += " and b.shiftid=@shiftid\r\n";
                        parameters.Add("shiftid", DailyAttendanceFilterDTO.ShiftId);
                    }
                    if (DailyAttendanceFilterDTO.OuId != -1)
                    {
                        innerReplace += " and d.WorkOUID=@ouid\r\n";
                        parameters.Add("ouid", DailyAttendanceFilterDTO.OuId);
                    }
                    if (DailyAttendanceFilterDTO.DepartmentId != -1)
                    {
                        innerReplace += " and d.DepartMentId=@departmentid\r\n";
                        parameters.Add("departmentid", DailyAttendanceFilterDTO.DepartmentId);
                    }
                    parameters.Add("ouid2", LoginDTO.WorkOUId);

                    // BLOCKING: UPDATE_DAILYATTENDANCE_DAY_TYPE_5_OR_10
                    string sql = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_DAY_TYPE_5_OR_10
                        .Replace(":innerreplace", innerReplace).Replace(":ouid", LoginDTO.WorkOUId.ToString());
                    await _queryexecutor.ExecuteAsync(LoginDTO, sql, parameters);
                }
                #endregion

                // NOTE: GB4 explicitly skips "Update Full Day Half Day Policy" in this
                // FineTuned/range variant (commented out — "Not Required To Call In case
                // of Single Employee"). Correctly omitted here too.

                #region Late in / early out (timeslip + leave reconciliation)
                {
                    string tempTimeslipReplace = "";
                    string leaveReplace = "";
                    string innerReplace = "";
                    innerReplace += " and b.ATTENDANCEDATE>='" + fromAttendanceDate + "'\r\n and b.ATTENDANCEDATE<='" + toAttendanceDate + "'\r\n";
                    tempTimeslipReplace += " and a.ATTENDANCEDATE>='" + fromAttendanceDate + "'\r\n and a.ATTENDANCEDATE<='" + toAttendanceDate + "'\r\n";
                    leaveReplace += " and a.LeaveDate>='" + fromAttendanceDate + "'\r\n and a.LeaveDate<='" + toAttendanceDate + "'\r\n";

                    if (DailyAttendanceFilterDTO.EmployeeId != -1)
                        innerReplace += " and d.EmployeeId='" + DailyAttendanceFilterDTO.EmployeeId + "'\r\n";
                    if (DailyAttendanceFilterDTO.ShiftId != -1)
                        innerReplace += " and b.shiftid=" + DailyAttendanceFilterDTO.ShiftId + "\r\n";
                    if (DailyAttendanceFilterDTO.OuId != -1)
                        innerReplace += " and d.WorkOUID=" + DailyAttendanceFilterDTO.OuId + "\r\n";
                    if (DailyAttendanceFilterDTO.DepartmentId != -1)
                        innerReplace += " and d.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n";

                    // BLOCKING (6 constants): GET_PUNCH_BEFORE_FINDING_EARLY_AND_LATE_IN,
                    // GET_MIN_MAX_TIMESLIP_BASED_ON_START_END_TIME_IN_TIMESLIP,
                    // ..._OUT_TIMESLIP, GET_LEAVE_FOR_FINDING_LATEIN_EARLYOUT,
                    // ..._LATE_IN, ..._EARLY_OUT, plus the final
                    // UPDATE_DAILYATTENDANCE_EARLY_IN_LATE_OUT_BASED_ON_TIMESLIP_AND_LEAVE_REQUEST.
                    // These build a chain of temp tables consumed by the final UPDATE —
                    // must batch on one connection once the SQL text is available.

                    string sqlPunch = DailyAttendanceQB.GET_PUNCH_BEFORE_FINDING_EARLY_AND_LATE_IN
                        .Replace(":fromattendancedate", fromAttendanceDate)
                        .Replace(":toattendancedate", toAttendanceDate)
                        .Replace(":employeeid", DailyAttendanceFilterDTO.EmployeeId != -1 ? DailyAttendanceFilterDTO.EmployeeId.ToString() : "-1");

                    string sqlInTimeslip = DailyAttendanceQB.GET_MIN_MAX_TIMESLIP_BASED_ON_START_END_TIME_IN_TIMESLIP.Replace(":temptimeslipreplace", tempTimeslipReplace);
                    string sqlOutTimeslip = DailyAttendanceQB.GET_MIN_MAX_TIMESLIP_BASED_ON_START_END_TIME_OUT_TIMESLIP.Replace(":temptimeslipreplace", tempTimeslipReplace);
                    string sqlLeave = DailyAttendanceQB.GET_LEAVE_FOR_FINDING_LATEIN_EARLYOUT.Replace(":leavereplace", leaveReplace);
                    string sqlLateIn = DailyAttendanceQB.GET_MIN_MAX_TIMESLIP_BASED_ON_START_END_TIME_LATE_IN.Replace(":temptimeslipreplace", tempTimeslipReplace);
                    string sqlEarlyOut = DailyAttendanceQB.GET_MIN_MAX_TIMESLIP_BASED_ON_START_END_TIME_EARLY_OUT.Replace(":temptimeslipreplace", tempTimeslipReplace);

                    var finalParams = new DynamicParameters();
                    finalParams.Add("ouid", LoginDTO.WorkOUId);
                    string sqlFinal = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_EARLY_IN_LATE_OUT_BASED_ON_TIMESLIP_AND_LEAVE_REQUEST
                        .Replace(":innerreplace", innerReplace)
                        .Replace(":ouid", LoginDTO.WorkOUId.ToString());

                    string batch = sqlPunch + ";\r\n" + sqlInTimeslip + ";\r\n" + sqlOutTimeslip + ";\r\n" +
                                   sqlLeave + ";\r\n" + sqlLateIn + ";\r\n" + sqlEarlyOut + ";\r\n" + sqlFinal;
                    await _queryexecutor.ExecuteAsync(LoginDTO, batch, finalParams);
                }
                #endregion

                #region Cutoff-minute half-day (in-based)
                {
                    string innerReplace = "";
                    innerReplace += " and a.ATTENDANCEDATE>='" + fromAttendanceDate + "'\r\n and a.ATTENDANCEDATE<='" + toAttendanceDate + "'\r\n";
                    if (DailyAttendanceFilterDTO.EmployeeId != -1) innerReplace += " and d.EmployeeId='" + DailyAttendanceFilterDTO.EmployeeId + "'\r\n";
                    if (DailyAttendanceFilterDTO.ShiftId != -1) innerReplace += " and b.shiftid=" + DailyAttendanceFilterDTO.ShiftId + "\r\n";
                    if (DailyAttendanceFilterDTO.OuId != -1) innerReplace += " and d.WorkOUID=" + DailyAttendanceFilterDTO.OuId + "\r\n";
                    if (DailyAttendanceFilterDTO.DepartmentId != -1) innerReplace += " and d.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n";

                    // BLOCKING: UPDATE_DAILYATTENDANCE_DAY_TIME_BASED_ON_IN_TIME_AND_CUTOFFMINUTES_IN_SHIFT
                    string sql = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_DAY_TIME_BASED_ON_IN_TIME_AND_CUTOFFMINUTES_IN_SHIFT
                        .Replace(":innerreplace", innerReplace).Replace(":ouid", LoginDTO.WorkOUId.ToString());
                    await _queryexecutor.ExecuteAsync(LoginDTO, sql, null!);
                }
                #endregion

                #region Cutoff-minute half-day (out-based)
                {
                    string innerReplace = "";
                    innerReplace += " and a.ATTENDANCEDATE>='" + fromAttendanceDate + "'\r\n and a.ATTENDANCEDATE<='" + toAttendanceDate + "'\r\n";
                    if (DailyAttendanceFilterDTO.EmployeeId != -1) innerReplace += " and d.EmployeeId='" + DailyAttendanceFilterDTO.EmployeeId + "'\r\n";
                    if (DailyAttendanceFilterDTO.ShiftId != -1) innerReplace += " and b.shiftid=" + DailyAttendanceFilterDTO.ShiftId + "\r\n";
                    if (DailyAttendanceFilterDTO.OuId != -1) innerReplace += " and d.WorkOUID=" + DailyAttendanceFilterDTO.OuId + "\r\n";
                    if (DailyAttendanceFilterDTO.DepartmentId != -1) innerReplace += " and d.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n";

                    // BLOCKING: UPDATE_DAILYATTENDANCE_DAY_TIME_BASED_ON_OUT_TIME_AND_CUTOFFMINUTES_IN_SHIFT
                    string sql = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_DAY_TIME_BASED_ON_OUT_TIME_AND_CUTOFFMINUTES_IN_SHIFT
                        .Replace(":innerreplace", innerReplace).Replace(":ouid", LoginDTO.WorkOUId.ToString());
                    await _queryexecutor.ExecuteAsync(LoginDTO, sql, null!);
                }
                #endregion

                #region Short hours
                {
                    var parameters = new DynamicParameters();
                    string replaceFields = "";
                    replaceFields += " and Tdailyattendance.ATTENDANCEDATE>=@fromdate\r\n and Tdailyattendance.ATTENDANCEDATE<=@todate\r\n";
                    parameters.Add("fromdate", fromAttendanceDate);
                    parameters.Add("todate", toAttendanceDate);
                    if (DailyAttendanceFilterDTO.EmployeeId != -1)
                    {
                        replaceFields += " and Tdailyattendance.EmployeeId=@employeeid\r\n";
                        parameters.Add("employeeid", DailyAttendanceFilterDTO.EmployeeId);
                    }
                    if (DailyAttendanceFilterDTO.ShiftId != -1)
                    {
                        replaceFields += " and Tdailyattendance.shiftid=@shiftid\r\n";
                        parameters.Add("shiftid", DailyAttendanceFilterDTO.ShiftId);
                    }
                    AddOuFilter(ref replaceFields, "emp", DailyAttendanceFilterDTO.OuId, parameters, "ouid");
                    if (DailyAttendanceFilterDTO.DepartmentId != -1)
                    {
                        replaceFields += " and emp.DepartMentId=@departmentid\r\n";
                        parameters.Add("departmentid", DailyAttendanceFilterDTO.DepartmentId);
                    }

                    // BLOCKING: UPDATE_DAILYATTENDANCE_FIELD_SHORT_HOURS
                    string sql = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_FIELD_SHORT_HOURS.Replace(":replacefields", replaceFields);
                    await _queryexecutor.ExecuteAsync(LoginDTO, sql, parameters);
                }
                #endregion

                #region PI hours (per-day loop across the range, only when no OT expression formula)
                if (otExpressionFormulaId == -1)
                {
                    string replaceFields = "";
                    replaceFields += " and Tdailyattendance.ATTENDANCEDATE>='" + fromAttendanceDate + "'\r\n and Tdailyattendance.ATTENDANCEDATE<='" + toAttendanceDate + "'\r\n";
                    if (DailyAttendanceFilterDTO.EmployeeId != -1) replaceFields += " and Tdailyattendance.EmployeeId=" + DailyAttendanceFilterDTO.EmployeeId + "\r\n";
                    if (DailyAttendanceFilterDTO.ShiftId != -1) replaceFields += " and Tdailyattendance.shiftid=" + DailyAttendanceFilterDTO.ShiftId + "\r\n";
                    if (DailyAttendanceFilterDTO.OuId != -1) replaceFields += " and emp.WorkOUID=" + DailyAttendanceFilterDTO.OuId + "\r\n";
                    if (DailyAttendanceFilterDTO.DepartmentId != -1) replaceFields += " and emp.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n";

                    DateTime monthStartDate = new DateTime(FromDate.Year, FromDate.Month, 1);
                    int daysInMonth = DateTime.DaysInMonth(monthStartDate.Year, monthStartDate.Month);
                    DateTime endDateOfMonth = new DateTime(monthStartDate.Year, monthStartDate.Month, daysInMonth);

                    var now = DateTime.Now;
                    DateTime lastDayLastMonth = new DateTime(now.Year, now.Month, 1).AddDays(-1);
                    TimeSpan diffSpan = ToDate.Subtract(FromDate);

                    for (int i = 0; i < diffSpan.TotalDays + 1; i++)
                    {
                        DateTime dayDate = FromDate.AddDays(i);
                        if (DateTime.Now.AddDays(batchOtPiRunDays * -1) >= dayDate || dayDate <= lastDayLastMonth)
                        {
                            // UPDATE_DAILYATTENDANCE_FIELD_PI uses colon-notation (:ouid, :attendancedate …)
                            // — these are string.Replace tokens, NOT Dapper @params.
                            // Replace :maxallowedotforfemale BEFORE :maxallowedotformale to avoid
                            // prefix-collision that would leave a raw ':' in the SQL.
                            string sql = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_FIELD_PI
                                .Replace(":ouid", LoginDTO.WorkOUId.ToString())
                                .Replace(":replacefields", replaceFields)
                                .Replace(":attendancedate", dayDate.ToString("yyyy/MM/dd"))
                                .Replace(":startdateofmonth", monthStartDate.ToString("yyyy/MM/dd"))
                                .Replace(":enddateofmonth", endDateOfMonth.ToString("yyyy/MM/dd"))
                                .Replace(":oteligableminutes", otEligableMinutes.ToString())
                                .Replace(":maxallowedotforfemale", maxAllowedOtForFemale.ToString())
                                .Replace(":maxallowedotformale", maxAllowedOtForMale.ToString())
                                .Replace(":maximumotinmonth", maximumOtInMonth.ToString())
                                .Replace(":femaleothoursinevendays", femaleOtHoursInEvenDays.ToString())
                                .Replace(":femaleothoursinodddays", femaleOtHoursInOddDays.ToString())
                                .Replace(":maleothoursinevendays", maleOtHoursInEvenDays.ToString())
                                .Replace(":maleothoursinodddays", maleOtHoursInOddDays.ToString());
                            await _queryexecutor.ExecuteAsync(LoginDTO, sql, null!);
                        }
                    }
                }
                #endregion

                #region Trigger customized day type
                {
                    int trgOuId = -1, trgEmployeeId = -1;
                    if (DailyAttendanceFilterDTO.OuId != -1)
                        trgOuId = DailyAttendanceFilterDTO.OuId == -2100000000 ? -1 : DailyAttendanceFilterDTO.OuId;
                    if (DailyAttendanceFilterDTO.EmployeeId != -1)
                        trgEmployeeId = DailyAttendanceFilterDTO.EmployeeId;

                    // UPDATE_DAILYATTENDANCE_DAY_TYPE_FOR_TRGGER_CUSTOMIZED uses colon-notation
                    // (:payconfigurationid, :fromdate …) — must use string.Replace, NOT Dapper params.
                    string sqlTrg = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_DAY_TYPE_FOR_TRGGER_CUSTOMIZED
                        .Replace(":payconfigurationid", "-1")
                        .Replace(":fromdate", fromAttendanceDate)
                        .Replace(":todate", toAttendanceDate)
                        .Replace(":ouid", trgOuId.ToString())
                        .Replace(":employeeid", trgEmployeeId.ToString());
                    await _queryexecutor.ExecuteAsync(LoginDTO, sqlTrg);
                }
                #endregion

                #region TextDayType sync
                {
                    var parameters = new DynamicParameters();
                    string replaceFields = "";
                    replaceFields += " and Tdailyattendance.ATTENDANCEDATE>=@fromdate\r\n and Tdailyattendance.ATTENDANCEDATE<=@todate\r\n";
                    parameters.Add("fromdate", fromAttendanceDate);
                    parameters.Add("todate", toAttendanceDate);
                    if (DailyAttendanceFilterDTO.EmployeeId != -1)
                    {
                        replaceFields += " and Tdailyattendance.EmployeeId=@employeeid\r\n";
                        parameters.Add("employeeid", DailyAttendanceFilterDTO.EmployeeId);
                    }
                    if (DailyAttendanceFilterDTO.ShiftId != -1)
                    {
                        replaceFields += " and Tdailyattendance.shiftid=@shiftid\r\n";
                        parameters.Add("shiftid", DailyAttendanceFilterDTO.ShiftId);
                    }
                    if (DailyAttendanceFilterDTO.OuId != -1)
                    {
                        replaceFields += " and emp.WorkOUID=@ouid\r\n";
                        parameters.Add("ouid", DailyAttendanceFilterDTO.OuId);
                    }
                    if (DailyAttendanceFilterDTO.DepartmentId != -1)
                    {
                        replaceFields += " and emp.DepartMentId=@departmentid\r\n";
                        parameters.Add("departmentid", DailyAttendanceFilterDTO.DepartmentId);
                    }

                    // BLOCKING: UPDATE_DAILYATTENDANCE_TEXT_DATA_TYPE
                    string sql = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_TEXT_DATA_TYPE
                        .Replace(":replacefields", replaceFields)
                        .Replace(":fromdate", fromAttendanceDate)
                        .Replace(":todate", toAttendanceDate);
                    await _queryexecutor.ExecuteAsync(LoginDTO, sql, parameters);
                }
                #endregion

                #region Zero-out fields when shift is -1
                {
                    var parameters = new DynamicParameters();
                    string replaceFields = "";
                    replaceFields += " and Tdailyattendance.ATTENDANCEDATE>=@fromdate\r\n and Tdailyattendance.ATTENDANCEDATE<=@todate\r\n";
                    parameters.Add("fromdate", fromAttendanceDate);
                    parameters.Add("todate", toAttendanceDate);
                    if (DailyAttendanceFilterDTO.EmployeeId != -1)
                    {
                        replaceFields += " and Tdailyattendance.EmployeeId=@employeeid\r\n";
                        parameters.Add("employeeid", DailyAttendanceFilterDTO.EmployeeId);
                    }
                    if (DailyAttendanceFilterDTO.ShiftId != -1)
                    {
                        replaceFields += " and Tdailyattendance.shiftid=@shiftid\r\n";
                        parameters.Add("shiftid", DailyAttendanceFilterDTO.ShiftId);
                    }
                    if (DailyAttendanceFilterDTO.OuId != -1)
                    {
                        replaceFields += " and emp.WorkOUID=@ouid\r\n";
                        parameters.Add("ouid", DailyAttendanceFilterDTO.OuId);
                    }
                    if (DailyAttendanceFilterDTO.DepartmentId != -1)
                    {
                        replaceFields += " and emp.DepartMentId=@departmentid\r\n";
                        parameters.Add("departmentid", DailyAttendanceFilterDTO.DepartmentId);
                    }
                    if (DailyAttendanceFilterDTO.PayConfigurationId != -1)
                    {
                        replaceFields += " and emp.PAYCONFIGURATIONID=@payconfigurationid\r\n";
                        parameters.Add("payconfigurationid", DailyAttendanceFilterDTO.PayConfigurationId);
                    }

                    // BLOCKING: UPDATE_DAILYATTENDANCE_FIELD_ALL_ZERO_IF_SHIFT_IS_MINUS_1
                    string sql = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_FIELD_ALL_ZERO_IF_SHIFT_IS_MINUS_1.Replace(":replacefields", replaceFields);
                    await _queryexecutor.ExecuteAsync(LoginDTO, sql, parameters);
                }
                #endregion

                await UpdateBreakTimeHoursAsync(DailyAttendanceFilterDTO.OuId, FromDate, ToDate, DailyAttendanceFilterDTO, breakTimeExpressionFormulaId, LoginDTO);
                await UpdateOTHoursAsync(FromDate, ToDate, DailyAttendanceFilterDTO, otExpressionFormulaId, LoginDTO);
                await UpdateBreakTimeHoursExpressionBasedAsync(FromDate, ToDate, DailyAttendanceFilterDTO, breakTimeExpressionFormulaId, LoginDTO);

                #region Single/double-punch out-time correction (must run last)
                {
                    var parameters = new DynamicParameters();
                    string replaceFields = "";
                    replaceFields += " and a.ATTENDANCEDATE>=@fromdate\r\n and a.ATTENDANCEDATE<=@todate\r\n";
                    parameters.Add("fromdate", fromAttendanceDate);
                    parameters.Add("todate", toAttendanceDate);
                    if (DailyAttendanceFilterDTO.EmployeeId != -1)
                    {
                        replaceFields += " and b.EmployeeId=@employeeid\r\n";
                        parameters.Add("employeeid", DailyAttendanceFilterDTO.EmployeeId);
                    }

                    // Already have this constant from earlier in this port.
                    string sql = DailyAttendanceQB.UPDATE_OUT_TIME_DURING_BULK_UPDATE.Replace(":replacefields", replaceFields);
                    await _queryexecutor.ExecuteAsync(LoginDTO, sql, parameters);
                }
                #endregion
            }
            catch (Exception)
            {
                throw;
            }
        }
        private record OuIdRow(int OrganizationUnitId);

        private class FormulaRow
        {
            public int FormulaType { get; set; }
            public int DataType    { get; set; }
            public string Expression   { get; set; } = "";
            public string FunctionName { get; set; } = "";
        }

        private record OuPrefetchContext(
            List<OuIdRow> AllOUs,
            List<PayRollDAL.DTO.OULevelSetting.OULevelSettingDTO> AllOULevelSettings,
            PayRollDAL.DTO.OULevelSetting.OULevelSettingDTO DefaultOUSettings,
            Dictionary<int, FormulaRow> FormulaCache
        );

        private async Task<OuPrefetchContext> PreFetchOUDataAsync(LoginDTO loginDTO)
        {
            const string GET_ALL_OU = "SELECT OUID AS OrganizationUnitId FROM MORGANIZATIONUNIT WHERE OUID<>-1 AND STATUS=1 ORDER BY OUID";

            // OU list and OULevelSettings are independent reads — fetch in parallel
            var ouTask       = _queryexecutor.QueryAsync<OuIdRow>(loginDTO, GET_ALL_OU, null!);
            var settingsTask = _ouLevelSettingDAL.GetOULevelSetting(-1, loginDTO);
            await Task.WhenAll(ouTask, settingsTask);

            var allOUs = ouTask.Result.ToList();
            if (loginDTO.WorkOUId != -2100000000)
                allOUs = allOUs.Where(o => o.OrganizationUnitId == loginDTO.WorkOUId).ToList();

            var allOULevelSettings = settingsTask.Result.Where(s => s.Id != -1).ToList();

            var defaultOUSettings = allOULevelSettings.FirstOrDefault(s => s.Id == loginDTO.WorkOUId);
            if (defaultOUSettings == null)
                throw new MethodNotAllowedException("There is no oulevelsetting created for login ou..");

            // collect unique formula IDs (both OT and BreakTime) and fetch MFORMULA in one shot
            var uniqueFormulaIds = allOULevelSettings
                .SelectMany(s => new[] { s.OTExpressionFormulaId, s.BreakTimeExpressionFormulaId })
                .Where(id => id != -1)
                .Distinct()
                .ToList();

            var formulaCache = new Dictionary<int, FormulaRow>();
            if (uniqueFormulaIds.Count > 0)
            {
                string inList = string.Join(",", uniqueFormulaIds);
                string formulaSql = $"SELECT FORMULAID AS FormulaId, CAST(FORMULATYPE AS INT) AS FormulaType, CAST(DATATYPE AS INT) AS DataType, ISNULL(EXPRESSION,'') AS Expression, ISNULL(FUNCTIONNAME,'') AS FunctionName FROM MFORMULA WHERE FORMULAID IN ({inList})";
                var formulaRows = await _queryexecutor.QueryAsync<FormulaRowWithId>(loginDTO, formulaSql, null!);
                foreach (var row in formulaRows)
                    formulaCache[row.FormulaId] = new FormulaRow { FormulaType = row.FormulaType, DataType = row.DataType, Expression = row.Expression, FunctionName = row.FunctionName };
            }

            return new OuPrefetchContext(allOUs, allOULevelSettings, defaultOUSettings, formulaCache);
        }

        private class FormulaRowWithId
        {
            public int    FormulaId    { get; set; }
            public int    FormulaType  { get; set; }
            public int    DataType     { get; set; }
            public string Expression   { get; set; } = "";
            public string FunctionName { get; set; } = "";
        }

        private async Task UpdateOTHoursAsync(DateTime fromDate, DateTime toDate, DailyAttendanceFilterDTO filter, int otExpressionFormulaId, LoginDTO loginDTO, Dictionary<int, FormulaRow>? formulaCache = null)
        {
            if (otExpressionFormulaId == -1) return;

            FormulaRow? formulaResult = null;
            if (formulaCache != null && formulaCache.TryGetValue(otExpressionFormulaId, out var cached))
                formulaResult = cached;
            else
                formulaResult = (await _queryexecutor.QueryAsync<FormulaRow>(loginDTO,
                    "SELECT CAST(FORMULATYPE AS INT) AS FormulaType, CAST(DATATYPE AS INT) AS DataType, ISNULL(EXPRESSION,'') AS Expression, ISNULL(FUNCTIONNAME,'') AS FunctionName FROM MFORMULA WHERE FORMULAID=@formulaId",
                    new DynamicParameters(new { formulaId = otExpressionFormulaId }))).FirstOrDefault();
            if (formulaResult == null) return;

            string finalExpression = BuildFinalExpression(formulaResult, "OTHOURS");

            string replaceFields = "";
            replaceFields += " and   TDAILYATTENDANCE.ATTENDANCEDATE>='" + fromDate.ToString("dd-MMM-yyyy") + "'\r\n";
            replaceFields += " and   TDAILYATTENDANCE.ATTENDANCEDATE<='" + toDate.ToString("dd-MMM-yyyy") + "'\r\n";
            if (filter.EmployeeId != -1) replaceFields += " and   TDAILYATTENDANCE.EmployeeId=" + filter.EmployeeId + "\r\n";
            if (filter.ShiftId    != -1) replaceFields += " and   TDAILYATTENDANCE.shiftid=" + filter.ShiftId + "\r\n";
            if (filter.OuId       != -1) replaceFields += " and   MEMPLOYEE.WorkOUID=" + filter.OuId + "\r\n";
            if (filter.DepartmentId != -1) replaceFields += " and   MEMPLOYEE.DepartMentId=" + filter.DepartmentId + "\r\n";

            string sql = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_OT_HOURS_FORMULA_BASED
                .Replace(":replacefields", replaceFields)
                .Replace(":expression", finalExpression);
            await _queryexecutor.ExecuteAsync(loginDTO, sql, null!);
        }

        private async Task UpdateBreakTimeHoursAsync(int ouId, DateTime fromDate, DateTime toDate, DailyAttendanceFilterDTO filter, int breakTimeExpressionFormulaId, LoginDTO loginDTO)
        {
            string breakTimeReplace = "";
            breakTimeReplace += " and   a.ATTENDANCEDATE>='" + fromDate.ToString("dd-MMM-yyyy") + "'\r\n";
            breakTimeReplace += " and   a.ATTENDANCEDATE<='" + toDate.ToString("dd-MMM-yyyy") + "'\r\n";
            if (filter.EmployeeId != -1) breakTimeReplace += " and   a.EmployeeId=" + filter.EmployeeId + "\r\n";
            if (ouId != -1)
                breakTimeReplace += ouId == -2100000000
                    ? " and   b.WorkOUID<>-1\r\n"
                    : " and   b.WorkOUID=" + ouId + "\r\n";
            if (filter.DepartmentId != -1) breakTimeReplace += " and   b.DepartMentId=" + filter.DepartmentId + "\r\n";

            string sqlWorked = DailyAttendanceQB.GET_EMPLOYEE_WORKED_TOTAL_WORKED_HRS.Replace(":breaktimereplace", breakTimeReplace);
            string sqlUpdate = DailyAttendanceQB.GET_EMPLOYEE_FINAL_BREAKTIME_UPDATE;
            await _queryexecutor.ExecuteAsync(loginDTO, sqlWorked + ";\r\n" + sqlUpdate, null!);
        }

        private async Task UpdateBreakTimeHoursExpressionBasedAsync(DateTime fromDate, DateTime toDate, DailyAttendanceFilterDTO filter, int breakTimeExpressionFormulaId, LoginDTO loginDTO, Dictionary<int, FormulaRow>? formulaCache = null)
        {
            if (breakTimeExpressionFormulaId == -1) return;

            FormulaRow? formulaResult = null;
            if (formulaCache != null && formulaCache.TryGetValue(breakTimeExpressionFormulaId, out var cached))
                formulaResult = cached;
            else
                formulaResult = (await _queryexecutor.QueryAsync<FormulaRow>(loginDTO,
                    "SELECT CAST(FORMULATYPE AS INT) AS FormulaType, CAST(DATATYPE AS INT) AS DataType, ISNULL(EXPRESSION,'') AS Expression, ISNULL(FUNCTIONNAME,'') AS FunctionName FROM MFORMULA WHERE FORMULAID=@formulaId",
                    new DynamicParameters(new { formulaId = breakTimeExpressionFormulaId }))).FirstOrDefault();
            if (formulaResult == null) return;

            string finalExpression = BuildFinalExpression(formulaResult, "BREAKTIME");

            string replaceFields = "";
            replaceFields += " and   TDAILYATTENDANCE.ATTENDANCEDATE>='" + fromDate.ToString("dd-MMM-yyyy") + "'\r\n";
            replaceFields += " and   TDAILYATTENDANCE.ATTENDANCEDATE<='" + toDate.ToString("dd-MMM-yyyy") + "'\r\n";
            if (filter.EmployeeId != -1) replaceFields += " and   TDAILYATTENDANCE.EmployeeId=" + filter.EmployeeId + "\r\n";
            if (filter.ShiftId    != -1) replaceFields += " and   TDAILYATTENDANCE.shiftid=" + filter.ShiftId + "\r\n";
            if (filter.OuId       != -1) replaceFields += " and   MEMPLOYEE.WorkOUID=" + filter.OuId + "\r\n";
            if (filter.DepartmentId != -1) replaceFields += " and   MEMPLOYEE.DepartMentId=" + filter.DepartmentId + "\r\n";
            if (filter.PayConfigurationId != -1) replaceFields += " and   MEMPLOYEE.PAYCONFIGURATIONID=" + filter.PayConfigurationId + "\r\n";

            string sql = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_BREAKTIME_HOURS_FORMULA_BASED
                .Replace(":replacefields", replaceFields)
                .Replace(":expression", finalExpression);
            await _queryexecutor.ExecuteAsync(loginDTO, sql, null!);
        }

        private static string BuildFinalExpression(FormulaRow formula, string fallback)
        {
            if (formula.FormulaType == 0)
            {
                string expr = formula.Expression.Replace("{", "(").Replace("}", ")");
                return "ISNULL(" + expr + ",0)\r\n";
            }
            if (formula.FormulaType == 1)
                return formula.DataType == 1
                    ? "ISNULL(" + formula.FunctionName + ",'')\r\n"
                    : "ISNULL(" + formula.FunctionName + ",0)\r\n";
            return fallback;
        }

        // Symmetric counterpart to the private FromEpoch above. Matches the same
        // seconds-based convention FromEpoch treats as its "small number" branch.
        private static long ToEpoch(DateTime date)
        {
            return new DateTimeOffset(date, TimeSpan.Zero).ToUnixTimeSeconds();
        }

        // Builds all UPDATE SQL for one day into a single string — no DB calls.
        // Called both from the per-day parallel loop (merged into one ExecuteAsync)
        // and from UpdateDailyattendanceforSameAndAcrossDate (standalone path).
        private string BuildSameAndAcrossDateBatchSql(
            string attendanceDate,
            DailyAttendanceFilterDTO DailyAttendanceFilterDTO,
            LoginDTO LoginDTO,
            OuPrefetchContext prefetch)
        {
            var defaultOUSettings    = prefetch.DefaultOUSettings;
            int otExpressionFormulaId      = defaultOUSettings.OTExpressionFormulaId;
            int otEligableMinutes          = defaultOUSettings.OULevelSettingOTEligableMinutes;
            int maximumOtInMonth           = defaultOUSettings.OULevelSettingMaximumOTInMonth;
            int maxAllowedOtForMale        = defaultOUSettings.OULevelSettingMaxAllowedOTForMale;
            int maxAllowedOtForFemale      = defaultOUSettings.OULevelSettingMaxAllowedOTForFemale;
            int maleOtHoursInEvenDays      = defaultOUSettings.OULevelSettingMaleOTHoursInEvenDays;
            int femaleOtHoursInEvenDays    = defaultOUSettings.OULevelSettingFemaleOTHoursInEvenDays;
            int maleOtHoursInOddDays       = defaultOUSettings.OULevelSettingMaleOTHoursInOddDays;
            int femaleOtHoursInOddDays     = defaultOUSettings.OULevelSettingFemaleOTHoursInOddDays;

            var masterBatch = new System.Text.StringBuilder();

            #region Bug #9744 — base field sync
            {
                string replaceFields = " and Tdailyattendance.ATTENDANCEDATE='" + attendanceDate + "'\r\n";
                if (DailyAttendanceFilterDTO.EmployeeId != -1)
                    replaceFields += " and Tdailyattendance.EmployeeId=" + DailyAttendanceFilterDTO.EmployeeId + "\r\n";
                if (DailyAttendanceFilterDTO.ShiftId != -1)
                    replaceFields += " and Tdailyattendance.shiftid=" + DailyAttendanceFilterDTO.ShiftId + "\r\n";
                if (DailyAttendanceFilterDTO.OuId != -1)
                    replaceFields += DailyAttendanceFilterDTO.OuId == -2100000000
                        ? " and emp.WorkOUID<>-1\r\n"
                        : " and emp.WorkOUID=" + DailyAttendanceFilterDTO.OuId + "\r\n";
                if (DailyAttendanceFilterDTO.DepartmentId != -1)
                    replaceFields += " and emp.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n";
                masterBatch.Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_FIELD.Replace(":replacefields", replaceFields)).Append(";\r\n");
            }
            #endregion

            #region Worked hours from TPUNCH
            {
                string replaceFields = " and TDAILYATTENDANCE.ATTENDANCEDATE='" + attendanceDate + "'\r\n";
                if (DailyAttendanceFilterDTO.EmployeeId != -1)
                    replaceFields += " and TDAILYATTENDANCE.EmployeeId=" + DailyAttendanceFilterDTO.EmployeeId + "\r\n";
                if (DailyAttendanceFilterDTO.OuId != -1)
                    replaceFields += DailyAttendanceFilterDTO.OuId == -2100000000
                        ? " and emp.WorkOUID<>-1\r\n" : " and emp.WorkOUID=" + DailyAttendanceFilterDTO.OuId + "\r\n";
                if (DailyAttendanceFilterDTO.DepartmentId != -1)
                    replaceFields += " and emp.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n";
                if (DailyAttendanceFilterDTO.PayConfigurationId != -1)
                    replaceFields += " and emp.PAYCONFIGURATIONID=" + DailyAttendanceFilterDTO.PayConfigurationId + "\r\n";
                masterBatch
                    .Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_WORKEDHOURS_FROM_PUNCH_1
                        .Replace(":fromattendancedate", attendanceDate).Replace(":toattendancedate", attendanceDate)
                        .Replace(":employeeid", DailyAttendanceFilterDTO.EmployeeId != -1 ? DailyAttendanceFilterDTO.EmployeeId.ToString() : "-1")).Append(";\r\n")
                    .Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_IN_TIME_OUT_TIME_BASED_ON_SHIFT_WORK_METHOD.Replace(":replacefields", replaceFields)).Append(";\r\n")
                    .Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_WORKEDHOURS_FROM_PUNCH_2.Replace(":replacefields", replaceFields)).Append(";\r\n");
            }
            #endregion

            #region Same-date attendance type
            {
                string replaceFields = " and Tdailyattendance.ATTENDANCEDATE='" + attendanceDate + "'\r\n";
                string innerReplace  = " and a.ATTENDANCEDATE='" + attendanceDate + "'\r\n";
                if (DailyAttendanceFilterDTO.EmployeeId != -1)
                {
                    replaceFields += " and Tdailyattendance.EmployeeId=" + DailyAttendanceFilterDTO.EmployeeId + "\r\n";
                    innerReplace  += " and a.EmployeeId='" + DailyAttendanceFilterDTO.EmployeeId + "'\r\n";
                }
                if (DailyAttendanceFilterDTO.ShiftId != -1)
                    replaceFields += " and Tdailyattendance.shiftid=" + DailyAttendanceFilterDTO.ShiftId + "\r\n";
                if (DailyAttendanceFilterDTO.OuId != -1)
                    replaceFields += DailyAttendanceFilterDTO.OuId == -2100000000
                        ? " and emp.WorkOUID<>-1\r\n" : " and emp.WorkOUID=" + DailyAttendanceFilterDTO.OuId + "\r\n";
                if (DailyAttendanceFilterDTO.DepartmentId != -1)
                    replaceFields += " and emp.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n";
                masterBatch
                    .Append(DailyAttendanceQB.GENERATE_TEMP_RECORD_FOR_PUNCH_FOR_ATTENDANCEDATE.Replace(":innerreplace", innerReplace)).Append(";\r\n")
                    .Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_IN_CASE_OF_SAME_DATE_ATTENDANCETYPE
                        .Replace(":innerreplace", innerReplace).Replace(":replacefields", replaceFields)).Append(";\r\n");
            }
            #endregion

            #region Across-date attendance type
            {
                string replaceFields = " and Tdailyattendance.ATTENDANCEDATE='" + attendanceDate + "'\r\n";
                string innerReplace  = "";
                if (DailyAttendanceFilterDTO.EmployeeId != -1)
                {
                    replaceFields += " and Tdailyattendance.EmployeeId=" + DailyAttendanceFilterDTO.EmployeeId + "\r\n";
                    innerReplace  += " and a.EmployeeId='" + DailyAttendanceFilterDTO.EmployeeId + "'\r\n";
                }
                if (DailyAttendanceFilterDTO.ShiftId != -1)
                    replaceFields += " and Tdailyattendance.shiftid=" + DailyAttendanceFilterDTO.ShiftId + "\r\n";
                if (DailyAttendanceFilterDTO.OuId != -1)
                    replaceFields += DailyAttendanceFilterDTO.OuId == -2100000000
                        ? " and emp.WorkOUID<>-1\r\n" : " and emp.WorkOUID=" + DailyAttendanceFilterDTO.OuId + "\r\n";
                if (DailyAttendanceFilterDTO.DepartmentId != -1)
                    replaceFields += " and emp.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n";
                masterBatch.Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_IN_CASE_OF_ACROSS_DATE_ATTENDANCETYPE
                    .Replace(":innerreplace", innerReplace).Replace(":replacefields", replaceFields)).Append(";\r\n");
            }
            #endregion

            #region Flexible shift pattern (in-based + out-based)
            {
                string replaceFields     = " and Tdailyattendance.ATTENDANCEDATE='" + attendanceDate + "'\r\n";
                string flexiReplaceFields = " and a.ATTENDANCEDATE='" + attendanceDate + "'\r\n";
                if (DailyAttendanceFilterDTO.EmployeeId != -1)
                {
                    replaceFields      += " and Tdailyattendance.EmployeeId=" + DailyAttendanceFilterDTO.EmployeeId + "\r\n";
                    flexiReplaceFields += " and a.EmployeeId=" + DailyAttendanceFilterDTO.EmployeeId + "\r\n";
                }
                if (DailyAttendanceFilterDTO.OuId != -1)
                {
                    if (DailyAttendanceFilterDTO.OuId == -2100000000)
                    { replaceFields += " and emp.WorkOUID<>-1\r\n"; flexiReplaceFields += " and b.WorkOUID<>-1\r\n"; }
                    else
                    { replaceFields += " and emp.WorkOUID=" + DailyAttendanceFilterDTO.OuId + "\r\n"; flexiReplaceFields += " and b.WorkOUID=" + DailyAttendanceFilterDTO.OuId + "\r\n"; }
                }
                if (DailyAttendanceFilterDTO.DepartmentId != -1)
                {
                    replaceFields      += " and emp.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n";
                    flexiReplaceFields += " and b.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n";
                }
                string sqlFlexiField = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_FIELD_FOR_FLEXISHIFT_EMPLOYEE.Replace(":replacefields", replaceFields);
                masterBatch
                    .Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_SHIFTID_IN_CASE_OF_FLEXIBLE_SHIFTPATTERN_NEW_1.Replace(":replacefields", flexiReplaceFields)).Append(";\r\n")
                    .Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_SHIFTID_IN_CASE_OF_FLEXIBLE_SHIFTPATTERN_2).Append(";\r\n")
                    .Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_SHIFTID_IN_CASE_OF_FLEXIBLE_SHIFTPATTERN_3).Append(";\r\n")
                    .Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_SHIFTID_IN_CASE_OF_FLEXIBLE_SHIFTPATTERN_4).Append(";\r\n")
                    .Append(sqlFlexiField).Append(";\r\n")
                    .Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_SHIFTID_IN_CASE_OF_FLEXIBLE_SHIFTPATTERN_NEW_OUTBASED_1.Replace(":replacefields", flexiReplaceFields)).Append(";\r\n")
                    .Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_SHIFTID_IN_CASE_OF_FLEXIBLE_SHIFTPATTERN_OUT_BASED_2).Append(";\r\n")
                    .Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_SHIFTID_IN_CASE_OF_FLEXIBLE_SHIFTPATTERN_OUT_BASED_3).Append(";\r\n")
                    .Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_SHIFTID_IN_CASE_OF_FLEXIBLE_SHIFTPATTERN_OUT_BASED_4).Append(";\r\n")
                    .Append(sqlFlexiField).Append(";\r\n");
            }
            #endregion

            #region Two-half leave handling
            {
                string replaceFields = " and Tdailyattendance.ATTENDANCEDATE='" + attendanceDate + "'\r\n";
                if (DailyAttendanceFilterDTO.EmployeeId != -1)
                    replaceFields += " and EMP.EmployeeId='" + DailyAttendanceFilterDTO.EmployeeId + "'\r\n";
                if (DailyAttendanceFilterDTO.ShiftId != -1)
                    replaceFields += " and Tdailyattendance.shiftid=" + DailyAttendanceFilterDTO.ShiftId + "\r\n";
                if (DailyAttendanceFilterDTO.OuId != -1)
                    replaceFields += " and emp.WorkOUID=" + DailyAttendanceFilterDTO.OuId + "\r\n";
                if (DailyAttendanceFilterDTO.DepartmentId != -1)
                    replaceFields += " and emp.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n";
                if (DailyAttendanceFilterDTO.PayConfigurationId != -1)
                    replaceFields += " and emp.PAYCONFIGURATIONID=" + DailyAttendanceFilterDTO.PayConfigurationId + "\r\n";

                string outerReplaceFields = " and Tdailyattendance.ATTENDANCEDATE='" + attendanceDate + "'\r\n";
                if (DailyAttendanceFilterDTO.EmployeeId != -1)
                    outerReplaceFields += " and TDAILYATTENDANCE.EMPLOYEEID=" + DailyAttendanceFilterDTO.EmployeeId + "\r\n";
                if (DailyAttendanceFilterDTO.ShiftId != -1)
                    outerReplaceFields += " and Tdailyattendance.shiftid=" + DailyAttendanceFilterDTO.ShiftId + "\r\n";

                string innerReplace = " and B.LEAVEDATE='" + attendanceDate + "'\r\n";
                if (DailyAttendanceFilterDTO.EmployeeId != -1)
                    innerReplace += " and EMP.EmployeeId='" + DailyAttendanceFilterDTO.EmployeeId + "'\r\n";
                if (DailyAttendanceFilterDTO.OuId != -1)
                    innerReplace += " and emp.WorkOUID=" + DailyAttendanceFilterDTO.OuId + "\r\n";
                if (DailyAttendanceFilterDTO.DepartmentId != -1)
                    innerReplace += " and emp.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n";

                masterBatch
                    .Append(DailyAttendanceQB.FIRST_SECOND_HALF_LEAVE_UPDATE_IN_DAILYATTENDNACE_TO_NONE.Replace(":replacefields", replaceFields)).Append(";\r\n")
                    .Append(DailyAttendanceQB.FIRST_HALF_LEAVE_UPDATE_IN_DAILYATTENDNACE.Replace(":replacefields", outerReplaceFields).Replace(":innerreplace", innerReplace)).Append(";\r\n")
                    .Append(DailyAttendanceQB.SECOND_HALF_LEAVE_UPDATE_IN_DAILYATTENDNACE.Replace(":replacefields", outerReplaceFields).Replace(":innerreplace", innerReplace)).Append(";\r\n")
                    .Append(DailyAttendanceQB.LEAVEID_BASEDON_TWO_HALF_LEAVE_UPDATE_IN_DAILYATTENDNACE.Replace(":replacefields", replaceFields)).Append(";\r\n");
            }
            #endregion

            #region Day type 5/10
            {
                string innerReplace = " and a.ATTENDANCEDATE='" + attendanceDate + "'\r\n";
                if (DailyAttendanceFilterDTO.EmployeeId != -1) innerReplace += " and d.EmployeeId='" + DailyAttendanceFilterDTO.EmployeeId + "'\r\n";
                if (DailyAttendanceFilterDTO.ShiftId != -1)    innerReplace += " and b.shiftid=" + DailyAttendanceFilterDTO.ShiftId + "\r\n";
                if (DailyAttendanceFilterDTO.OuId != -1)       innerReplace += " and d.WorkOUID=" + DailyAttendanceFilterDTO.OuId + "\r\n";
                if (DailyAttendanceFilterDTO.DepartmentId != -1) innerReplace += " and d.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n";
                if (DailyAttendanceFilterDTO.PayConfigurationId != -1) innerReplace += " and d.PAYCONFIGURATIONID=" + DailyAttendanceFilterDTO.PayConfigurationId + "\r\n";
                masterBatch.Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_DAY_TYPE_5_OR_10.Replace(":innerreplace", innerReplace)).Append(";\r\n");
            }
            #endregion

            #region Full day / half day policy
            {
                string innerReplace = " and a.ATTENDANCEDATE='" + attendanceDate + "'\r\n";
                if (DailyAttendanceFilterDTO.EmployeeId != -1) innerReplace += " and d.EmployeeId='" + DailyAttendanceFilterDTO.EmployeeId + "'\r\n";
                if (DailyAttendanceFilterDTO.ShiftId != -1)    innerReplace += " and b.shiftid=" + DailyAttendanceFilterDTO.ShiftId + "\r\n";
                if (DailyAttendanceFilterDTO.OuId != -1)       innerReplace += " and d.WorkOUID=" + DailyAttendanceFilterDTO.OuId + "\r\n";
                if (DailyAttendanceFilterDTO.DepartmentId != -1) innerReplace += " and d.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n";
                masterBatch.Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_FULL_DAY_HALF_DAY_FOR_EMPLOYEE
                    .Replace(":innerreplace", innerReplace)
                    .Replace(":ouid", LoginDTO.WorkOUId.ToString())
                    .Replace(":attedancedate", attendanceDate)).Append(";\r\n");
            }
            #endregion

            #region Late in / early out
            {
                string tempTimeslipReplace = " and a.ATTENDANCEDATE='" + attendanceDate + "'\r\n";
                string leaveReplace        = " and a.LeaveDate='" + attendanceDate + "'\r\n";
                string innerReplace        = " and b.ATTENDANCEDATE='" + attendanceDate + "'\r\n";
                if (DailyAttendanceFilterDTO.EmployeeId != -1) innerReplace += " and d.EmployeeId='" + DailyAttendanceFilterDTO.EmployeeId + "'\r\n";
                if (DailyAttendanceFilterDTO.ShiftId != -1)    innerReplace += " and b.shiftid=" + DailyAttendanceFilterDTO.ShiftId + "\r\n";
                if (DailyAttendanceFilterDTO.OuId != -1)       innerReplace += " and d.WorkOUID=" + DailyAttendanceFilterDTO.OuId + "\r\n";
                if (DailyAttendanceFilterDTO.DepartmentId != -1) innerReplace += " and d.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n";
                masterBatch
                    .Append(DailyAttendanceQB.GET_PUNCH_BEFORE_FINDING_EARLY_AND_LATE_IN
                        .Replace(":fromattendancedate", attendanceDate).Replace(":toattendancedate", attendanceDate)
                        .Replace(":employeeid", DailyAttendanceFilterDTO.EmployeeId != -1 ? DailyAttendanceFilterDTO.EmployeeId.ToString() : "-1")).Append(";\r\n")
                    .Append(DailyAttendanceQB.GET_MIN_MAX_TIMESLIP_BASED_ON_START_END_TIME_IN_TIMESLIP.Replace(":temptimeslipreplace", tempTimeslipReplace)).Append(";\r\n")
                    .Append(DailyAttendanceQB.GET_MIN_MAX_TIMESLIP_BASED_ON_START_END_TIME_OUT_TIMESLIP.Replace(":temptimeslipreplace", tempTimeslipReplace)).Append(";\r\n")
                    .Append(DailyAttendanceQB.GET_LEAVE_FOR_FINDING_LATEIN_EARLYOUT.Replace(":leavereplace", leaveReplace)).Append(";\r\n")
                    .Append(DailyAttendanceQB.GET_MIN_MAX_TIMESLIP_BASED_ON_START_END_TIME_LATE_IN.Replace(":temptimeslipreplace", tempTimeslipReplace)).Append(";\r\n")
                    .Append(DailyAttendanceQB.GET_MIN_MAX_TIMESLIP_BASED_ON_START_END_TIME_EARLY_OUT.Replace(":temptimeslipreplace", tempTimeslipReplace)).Append(";\r\n")
                    .Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_EARLY_IN_LATE_OUT_BASED_ON_TIMESLIP_AND_LEAVE_REQUEST.Replace(":innerreplace", innerReplace)).Append(";\r\n");
            }
            #endregion

            #region Cutoff-minute half-day (in-based + out-based)
            {
                string innerReplaceIn  = " and a.ATTENDANCEDATE='" + attendanceDate + "'\r\n";
                string innerReplaceOut = " and a.ATTENDANCEDATE='" + attendanceDate + "'\r\n";
                if (DailyAttendanceFilterDTO.EmployeeId != -1) { innerReplaceIn += " and d.EmployeeId='" + DailyAttendanceFilterDTO.EmployeeId + "'\r\n"; innerReplaceOut += " and d.EmployeeId='" + DailyAttendanceFilterDTO.EmployeeId + "'\r\n"; }
                if (DailyAttendanceFilterDTO.ShiftId != -1)    { innerReplaceIn += " and b.shiftid=" + DailyAttendanceFilterDTO.ShiftId + "\r\n"; innerReplaceOut += " and b.shiftid=" + DailyAttendanceFilterDTO.ShiftId + "\r\n"; }
                if (DailyAttendanceFilterDTO.OuId != -1)       { innerReplaceIn += " and d.WorkOUID=" + DailyAttendanceFilterDTO.OuId + "\r\n"; innerReplaceOut += " and d.WorkOUID=" + DailyAttendanceFilterDTO.OuId + "\r\n"; }
                if (DailyAttendanceFilterDTO.DepartmentId != -1) { innerReplaceIn += " and d.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n"; innerReplaceOut += " and d.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n"; }
                masterBatch
                    .Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_DAY_TIME_BASED_ON_IN_TIME_AND_CUTOFFMINUTES_IN_SHIFT.Replace(":innerreplace", innerReplaceIn)).Append(";\r\n")
                    .Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_DAY_TIME_BASED_ON_OUT_TIME_AND_CUTOFFMINUTES_IN_SHIFT.Replace(":innerreplace", innerReplaceOut)).Append(";\r\n");
            }
            #endregion

            #region Short hours
            {
                string replaceFields = " and Tdailyattendance.ATTENDANCEDATE='" + attendanceDate + "'\r\n";
                if (DailyAttendanceFilterDTO.EmployeeId != -1) replaceFields += " and Tdailyattendance.EmployeeId=" + DailyAttendanceFilterDTO.EmployeeId + "\r\n";
                if (DailyAttendanceFilterDTO.ShiftId != -1)    replaceFields += " and Tdailyattendance.shiftid=" + DailyAttendanceFilterDTO.ShiftId + "\r\n";
                if (DailyAttendanceFilterDTO.OuId != -1)       replaceFields += " and emp.WorkOUID=" + DailyAttendanceFilterDTO.OuId + "\r\n";
                if (DailyAttendanceFilterDTO.DepartmentId != -1) replaceFields += " and emp.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n";
                masterBatch.Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_FIELD_SHORT_HOURS.Replace(":replacefields", replaceFields)).Append(";\r\n");
            }
            #endregion

            #region PI hours (only when no OT expression formula)
            if (otExpressionFormulaId == -1)
            {
                string replaceFields = " and Tdailyattendance.ATTENDANCEDATE='" + attendanceDate + "'\r\n";
                if (DailyAttendanceFilterDTO.EmployeeId != -1) replaceFields += " and Tdailyattendance.EmployeeId=" + DailyAttendanceFilterDTO.EmployeeId + "\r\n";
                if (DailyAttendanceFilterDTO.ShiftId != -1)    replaceFields += " and Tdailyattendance.shiftid=" + DailyAttendanceFilterDTO.ShiftId + "\r\n";
                if (DailyAttendanceFilterDTO.OuId != -1)
                    replaceFields += DailyAttendanceFilterDTO.OuId == -2100000000
                        ? " and emp.WorkOUID<>-1\r\n" : " and emp.WorkOUID=" + DailyAttendanceFilterDTO.OuId + "\r\n";
                if (DailyAttendanceFilterDTO.DepartmentId != -1)   replaceFields += " and emp.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n";
                if (DailyAttendanceFilterDTO.PayConfigurationId != -1) replaceFields += " and emp.PAYCONFIGURATIONID=" + DailyAttendanceFilterDTO.PayConfigurationId + "\r\n";

                DateTime attDay = DateTime.ParseExact(attendanceDate, "yyyy/MM/dd", null);
                DateTime monthStart  = new DateTime(attDay.Year, attDay.Month, 1);
                DateTime monthEnd    = new DateTime(attDay.Year, attDay.Month, DateTime.DaysInMonth(attDay.Year, attDay.Month));
                DateTime lastDayLastMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddDays(-1);
                int batchOtPiRunDays = Convert.ToInt32(defaultOUSettings.OULevelSettingBatchOTPIRunDays);
                if (DateTime.Now.AddDays(batchOtPiRunDays * -1) >= attDay || attDay <= lastDayLastMonth)
                {
                    masterBatch.Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_FIELD_PI
                        .Replace(":ouid",                  LoginDTO.WorkOUId.ToString())
                        .Replace(":replacefields",         replaceFields)
                        .Replace(":attendancedate",        attendanceDate)
                        .Replace(":startdateofmonth",      monthStart.ToString("yyyy/MM/dd"))
                        .Replace(":enddateofmonth",        monthEnd.ToString("yyyy/MM/dd"))
                        .Replace(":oteligableminutes",     otEligableMinutes.ToString())
                        .Replace(":maxallowedotforfemale", maxAllowedOtForFemale.ToString())
                        .Replace(":maxallowedotformale",   maxAllowedOtForMale.ToString())
                        .Replace(":maximumotinmonth",      maximumOtInMonth.ToString())
                        .Replace(":femaleothoursinevendays", femaleOtHoursInEvenDays.ToString())
                        .Replace(":femaleothoursinodddays",  femaleOtHoursInOddDays.ToString())
                        .Replace(":maleothoursinevendays",   maleOtHoursInEvenDays.ToString())
                        .Replace(":maleothoursinodddays",    maleOtHoursInOddDays.ToString())).Append(";\r\n");
                }
            }
            #endregion

            #region Trigger customized + TextDayType + Zero-shift-1
            {
                int trgOuId = DailyAttendanceFilterDTO.OuId != -1
                    ? (DailyAttendanceFilterDTO.OuId == -2100000000 ? -1 : DailyAttendanceFilterDTO.OuId)
                    : -1;
                int trgEmployeeId = DailyAttendanceFilterDTO.EmployeeId != -1 ? DailyAttendanceFilterDTO.EmployeeId : -1;
                string textReplaceFields = " and Tdailyattendance.ATTENDANCEDATE='" + attendanceDate + "'\r\n";
                if (DailyAttendanceFilterDTO.EmployeeId != -1)   textReplaceFields += " and Tdailyattendance.EmployeeId=" + DailyAttendanceFilterDTO.EmployeeId + "\r\n";
                if (DailyAttendanceFilterDTO.ShiftId != -1)      textReplaceFields += " and Tdailyattendance.shiftid=" + DailyAttendanceFilterDTO.ShiftId + "\r\n";
                if (DailyAttendanceFilterDTO.OuId != -1)         textReplaceFields += " and emp.WorkOUID=" + DailyAttendanceFilterDTO.OuId + "\r\n";
                if (DailyAttendanceFilterDTO.DepartmentId != -1) textReplaceFields += " and emp.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n";
                string zeroReplaceFields = textReplaceFields;
                if (DailyAttendanceFilterDTO.PayConfigurationId != -1) zeroReplaceFields += " and emp.PAYCONFIGURATIONID=" + DailyAttendanceFilterDTO.PayConfigurationId + "\r\n";
                masterBatch
                    .Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_DAY_TYPE_FOR_TRGGER_CUSTOMIZED
                        .Replace(":payconfigurationid", "-1").Replace(":fromdate", attendanceDate)
                        .Replace(":todate", attendanceDate).Replace(":ouid", trgOuId.ToString())
                        .Replace(":employeeid", trgEmployeeId.ToString())).Append(";\r\n")
                    .Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_TEXT_DATA_TYPE
                        .Replace(":replacefields", textReplaceFields)
                        .Replace(":fromdate", attendanceDate).Replace(":todate", attendanceDate)).Append(";\r\n")
                    .Append(DailyAttendanceQB.UPDATE_DAILYATTENDANCE_FIELD_ALL_ZERO_IF_SHIFT_IS_MINUS_1.Replace(":replacefields", zeroReplaceFields)).Append(";\r\n");
            }
            #endregion

            #region Out-time correction (last step)
            {
                string replaceFields = " and a.ATTENDANCEDATE>='" + attendanceDate + "'\r\n and a.ATTENDANCEDATE<='" + attendanceDate + "'\r\n";
                if (DailyAttendanceFilterDTO.EmployeeId != -1)
                    replaceFields += " and b.EmployeeId=" + DailyAttendanceFilterDTO.EmployeeId + "\r\n";
                masterBatch.Append(DailyAttendanceQB.UPDATE_OUT_TIME_DURING_BULK_UPDATE.Replace(":replacefields", replaceFields)).Append(";\r\n");
            }
            #endregion

            return masterBatch.ToString();
        }

        // Runs OT + break-time expression per OU in parallel — separate from the main batch
        // because these need a resolved DateTime and fire per-OU connections.
        private async Task RunOTAndBreakTimeAsync(
            DailyAttendanceFilterDTO DailyAttendanceFilterDTO, LoginDTO LoginDTO, OuPrefetchContext prefetch)
        {
            DateTime attDate = await _IGB5CommonFunction.FromEpoch(DailyAttendanceFilterDTO.AttendanceDate);
            int breakTimeExpressionFormulaId = prefetch.DefaultOUSettings.BreakTimeExpressionFormulaId;

            await UpdateBreakTimeHoursAsync(LoginDTO.WorkOUId, attDate, attDate, DailyAttendanceFilterDTO, breakTimeExpressionFormulaId, LoginDTO);

            var ouSettingPairs = prefetch.AllOUs.Select(ou =>
            {
                var s = prefetch.AllOULevelSettings.FirstOrDefault(x => x.Id == ou.OrganizationUnitId)
                    ?? throw new MethodNotAllowedException("There is no oulevelsetting created for login ou..");
                return (ou, s);
            }).ToList();

            var ouTasks = ouSettingPairs.Select(pair =>
            {
                var (ou, ouSettings) = pair;
                var ouFilter = new DailyAttendanceFilterDTO
                {
                    EmployeeId         = DailyAttendanceFilterDTO.EmployeeId,
                    OuId               = ou.OrganizationUnitId,
                    DepartmentId       = DailyAttendanceFilterDTO.DepartmentId,
                    ShiftId            = DailyAttendanceFilterDTO.ShiftId,
                    PayConfigurationId = DailyAttendanceFilterDTO.PayConfigurationId,
                    AttendanceDate     = DailyAttendanceFilterDTO.AttendanceDate,
                    AttendanceFromDate = DailyAttendanceFilterDTO.AttendanceFromDate,
                };
                var ouLogin = new LoginDTO
                {
                    WorkOUId            = ou.OrganizationUnitId,
                    UserId              = LoginDTO.UserId,
                    WorkPeriodId        = LoginDTO.WorkPeriodId,
                    UserPrimaryMailId   = LoginDTO.UserPrimaryMailId,
                    ServiceOffSet       = LoginDTO.ServiceOffSet,
                    DatabaseName        = LoginDTO.DatabaseName,
                    ServerConfigId      = LoginDTO.ServerConfigId,
                    DatabaseType        = LoginDTO.DatabaseType,
                    DatabaseOffset      = LoginDTO.DatabaseOffset,
                    FinalOffSetValue    = LoginDTO.FinalOffSetValue,
                    ServerConfigOffset  = LoginDTO.ServerConfigOffset,
                    ServerConfigMaxValue= LoginDTO.ServerConfigMaxValue,
                    UserCriteriaDTO     = LoginDTO.UserCriteriaDTO,
                };
                return Task.WhenAll(
                    UpdateOTHoursAsync(attDate, attDate, ouFilter, ouSettings.OTExpressionFormulaId, ouLogin, prefetch.FormulaCache),
                    UpdateBreakTimeHoursExpressionBasedAsync(attDate, attDate, ouFilter, ouSettings.BreakTimeExpressionFormulaId, ouLogin, prefetch.FormulaCache)
                );
            }).ToList();
            await Task.WhenAll(ouTasks);
        }

        private async Task UpdateDailyattendanceforSameAndAcrossDate(
    DailyAttendanceFilterDTO DailyAttendanceFilterDTO, LoginDTO LoginDTO, OuPrefetchContext prefetch)
        {
            try
            {
                string attendanceDate = "";
                if (DailyAttendanceFilterDTO.AttendanceDate != 0)
                {
                    DateTime tempDate = await _IGB5CommonFunction.FromEpoch(DailyAttendanceFilterDTO.AttendanceDate);
                    attendanceDate = tempDate.ToString("yyyy/MM/dd");
                }

                string batchSql = BuildSameAndAcrossDateBatchSql(attendanceDate, DailyAttendanceFilterDTO, LoginDTO, prefetch);
                await _queryexecutor.ExecuteAsync(LoginDTO, batchSql, null!);
                await RunOTAndBreakTimeAsync(DailyAttendanceFilterDTO, LoginDTO, prefetch);
            }
            catch (Exception) { throw; }
        }

        // GB4 parity: DailyAttendanceBLL.DayTypeUpdateInAttendance.
        // Type: 0 = Full Day, 1 = Week Off, 2 = Holiday.
        public async Task<string> UpdateAttendanceDayType(int Type, DailyAttendanceFilterDTO DailyAttendanceFilterDTO, LoginDTO LoginDTO)
        {
            try
            {
                if (DailyAttendanceFilterDTO == null)
                    throw new NotFoundException();

                if (Type != 0 && Type != 1 && Type != 2)
                    throw new MethodNotAllowedException("Type not supported..");

                string attendanceFromDate = "";
                string attendanceToDate = "";
                DateTime tempFromDate = DateTime.Now;
                DateTime tempToDate = DateTime.Now;

                if (DailyAttendanceFilterDTO.AttendanceFromDate != 0)
                {
                    tempFromDate = await _IGB5CommonFunction.FromEpoch(DailyAttendanceFilterDTO.AttendanceFromDate);
                    attendanceFromDate = tempFromDate.ToString("dd-MMM-yyyy");
                }

                if (DailyAttendanceFilterDTO.AttendanceDate != 0)
                {
                    tempToDate = await _IGB5CommonFunction.FromEpoch(DailyAttendanceFilterDTO.AttendanceDate);
                    attendanceToDate = tempToDate.ToString("dd-MMM-yyyy");
                }

                if (attendanceFromDate == "")
                    throw new NotFoundException("Attendance From Date to be given for updating day type..");

                if (attendanceToDate == "")
                    throw new NotFoundException("Attendance To Date to be given for updating day type..");

                TimeSpan diffSpan = tempToDate - tempFromDate;
                int diff = Convert.ToInt32(diffSpan.TotalDays) + 1;
                if (diff > 31)
                    throw new MethodNotAllowedException("Bulk Updated can not be more than 31 days..");

                int ouId = DailyAttendanceFilterDTO.OuId == -1 ? -2100000000 : DailyAttendanceFilterDTO.OuId;

                // GB4 parity: block the update if payroll has already processed the pay
                // period covering either the from-date or the to-date for this OU.
                foreach (string checkDate in new[] { attendanceFromDate, attendanceToDate })
                {
                    string payProcessSql = DailyAttendanceQB.CHECK_PAY_PROCESS_ALREADY_DONE
                        .Replace(":ouid", ouId.ToString())
                        .Replace(":attendancedate", checkDate);

                    if (DailyAttendanceFilterDTO.PayConfigurationId != -1)
                        payProcessSql += "\r\n and c.PAYCONFIGID=" + DailyAttendanceFilterDTO.PayConfigurationId;

                    if (DailyAttendanceFilterDTO.EmployeeId != -1)
                        payProcessSql += "\r\n and a.EMPLOYEEID=" + DailyAttendanceFilterDTO.EmployeeId;

                    int payProcessCount = await _queryexecutor.TotalCountSql(payProcessSql, LoginDTO);
                    if (payProcessCount > 0)
                        throw new FoundException("Pay process already done for the selected period..");
                }

                string replaceFields = "";
                string textDayTypeReplaceFields = "";

                replaceFields += " and TDAILYATTENDANCE.ATTENDANCEDATE>='" + attendanceFromDate + "'\r\n";
                replaceFields += " and TDAILYATTENDANCE.ATTENDANCEDATE<='" + attendanceToDate + "'\r\n";
                textDayTypeReplaceFields += " and TDAILYATTENDANCE.ATTENDANCEDATE>='" + attendanceFromDate + "'\r\n";
                textDayTypeReplaceFields += " and TDAILYATTENDANCE.ATTENDANCEDATE<='" + attendanceToDate + "'\r\n";

                if (DailyAttendanceFilterDTO.PayConfigurationId != -1)
                {
                    replaceFields += " and b.PAYCONFIGURATIONID=" + DailyAttendanceFilterDTO.PayConfigurationId + "\r\n";
                    textDayTypeReplaceFields += " and emp.PAYCONFIGURATIONID=" + DailyAttendanceFilterDTO.PayConfigurationId + "\r\n";
                }
                if (DailyAttendanceFilterDTO.EmployeeId != -1)
                {
                    replaceFields += " and b.EmployeeId=" + DailyAttendanceFilterDTO.EmployeeId + "\r\n";
                    textDayTypeReplaceFields += " and emp.EmployeeId=" + DailyAttendanceFilterDTO.EmployeeId + "\r\n";
                }
                if (DailyAttendanceFilterDTO.ShiftId != -1)
                {
                    replaceFields += " and TDAILYATTENDANCE.SHIFTID=" + DailyAttendanceFilterDTO.ShiftId + "\r\n";
                    textDayTypeReplaceFields += " and TDAILYATTENDANCE.SHIFTID=" + DailyAttendanceFilterDTO.ShiftId + "\r\n";
                }
                if (DailyAttendanceFilterDTO.OuId != -1 && DailyAttendanceFilterDTO.OuId != -2100000000)
                {
                    replaceFields += " and TDAILYATTENDANCE.OUID=" + DailyAttendanceFilterDTO.OuId + "\r\n";
                    textDayTypeReplaceFields += " and TDAILYATTENDANCE.OUID=" + DailyAttendanceFilterDTO.OuId + "\r\n";
                }
                if (DailyAttendanceFilterDTO.DepartmentId != -1)
                {
                    replaceFields += " and b.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n";
                    textDayTypeReplaceFields += " and emp.DepartMentId=" + DailyAttendanceFilterDTO.DepartmentId + "\r\n";
                }

                string updateSql = Type switch
                {
                    0 => DailyAttendanceQB.UPDATE_HOLIDAY_IN_TDAILYATTENDANCE_FULLDAY,
                    1 => DailyAttendanceQB.UPDATE_HOLIDAY_IN_TDAILYATTENDANCE_WEEKOFF,
                    _ => DailyAttendanceQB.UPDATE_HOLIDAY_IN_TDAILYATTENDANCE_HOLIDAY,
                };
                updateSql = updateSql.Replace(":remarks", DailyAttendanceFilterDTO.Remarks).Replace(":replacefields", replaceFields);

                await _queryexecutor.ExecuteAsync(LoginDTO, updateSql, null!);

                string textDayTypeSql = DailyAttendanceQB.UPDATE_DAILYATTENDANCE_TEXT_DATA_TYPE
                    .Replace(":fromdate", attendanceFromDate)
                    .Replace(":todate", attendanceToDate)
                    .Replace(":replacefields", textDayTypeReplaceFields);

                await _queryexecutor.ExecuteAsync(LoginDTO, textDayTypeSql, null!);

                return "Holiday update in attendance is over.";
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<bool> IsPayProcessDoneFromDate(int employeeId, DateTime effectiveFrom, LoginDTO loginDTO, CancellationToken ct)
        {
            var parameters = new
            {
                EmployeeId = employeeId,
                EffectiveFrom = effectiveFrom,
                OuId = loginDTO.WorkOUId
            };

            int count = await _queryexecutor.ExecuteScalarAsync<int>(loginDTO,
                DailyAttendanceQB.CHECK_PAYPROCESS_EXISTS_FOR_EMPLOYEE_FROM_DATE, parameters, cancellationToken: ct).ConfigureAwait(false);

            return count > 0;
        }

        public async Task<int> DeleteDailyAttendanceFromDate(int employeeId, DateTime effectiveFrom, LoginDTO loginDTO, CancellationToken ct)
        {
            var parameters = new
            {
                EmployeeId = employeeId,
                EffectiveFrom = effectiveFrom
            };

            return await _queryexecutor.ExecuteAsync(loginDTO,
                DailyAttendanceQB.DELETE_DAILYATTENDANCE_FROM_DATE, parameters, cancellationToken: ct).ConfigureAwait(false);
        }

        // GB4 parity: DailyAttendanceDAL.ApplyDailyMultilEmployeeDetail's CriteriaWithQueryField map.
        private static readonly (string FieldName, string Column)[] MultipleEmployeeCriteriaFields =
        {
            ("employeetypeid", "employee.EMPLOYEETYPEID"),
            ("paygroupid", "employee.PAYGROUPID"),
            ("stafftypeid", "employee.STAFFTYPEID"),
            ("unitid", "employee.UNITID"),
            ("religionid", "employee.RELIGIONID"),
            ("divisionid", "employee.DIVISIONID"),
            ("payconfigurationid", "employee.PAYCONFIGURATIONID"),
            ("shiftid", "employee.SHIFTID"),
            ("departmentid", "employee.DEPARTMENTID"),
            ("reportingtoid", "employee.REPORTINGTOEMPLOYEEID"),
            ("ourbranchid", "employee.OURBRANCHID"),
            ("daytype", "dailyatt.DAYTYPE"),
            ("employeeid", "employee.EMPLOYEEID")
        };

        public async Task<(List<DailyAttendanceLoadFromPunchDTO> Items, int Total)> GetDailyAttendanceMultipleEmployee(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            int singlePunch = -1;
            DateTime? periodFromDate = null;
            DateTime? periodToDate = null;
            var whereClauses = new List<string>();
            var parameters = new DynamicParameters();
            parameters.Add("OuId", loginDTO.WorkOUId);
            parameters.Add("FirstNumber", firstNumber < 0 ? 0 : firstNumber);
            parameters.Add("MaxResult", maxResult < 0 ? int.MaxValue : maxResult);

            void ApplyCriteria(CriteriaDTO criteria)
            {
                if (criteria?.SectionCriteriaList == null)
                    return;

                foreach (var section in criteria.SectionCriteriaList)
                {
                    foreach (var attr in section?.AttributesCriteriaList ?? new())
                    {
                        string field = attr?.FieldName?.ToLower() ?? string.Empty;
                        string value = attr?.FieldValue?.ToString() ?? string.Empty;

                        if (field == "singlepunch")
                        {
                            int.TryParse(value, out singlePunch);
                            continue;
                        }
                        if (field == "periodfromdate")
                        {
                            periodFromDate = FromEpoch(Convert.ToInt64(value));
                            continue;
                        }
                        if (field == "periodtodate")
                        {
                            periodToDate = FromEpoch(Convert.ToInt64(value));
                            continue;
                        }
                        if (field == "punchtime")
                        {
                            whereClauses.Add("dailyatt.INTIME = @PunchTime");
                            parameters.Add("PunchTime", Convert.ToInt32(value));
                            continue;
                        }

                        foreach (var (fieldName, column) in MultipleEmployeeCriteriaFields)
                        {
                            if (field != fieldName)
                                continue;

                            string paramName = $"Crit_{fieldName}";
                            whereClauses.Add($"{column} = @{paramName}");
                            parameters.Add(paramName, Convert.ToInt32(value));
                            break;
                        }
                    }
                }
            }

            ApplyCriteria(criteriaDTO);
            ApplyCriteria(loginDTO.UserCriteriaDTO);

            if (periodFromDate.HasValue && periodToDate.HasValue)
            {
                whereClauses.Add("dailyatt.ATTENDANCEDATE >= @PeriodFromDate AND dailyatt.ATTENDANCEDATE <= @PeriodToDate");
                parameters.Add("PeriodFromDate", periodFromDate.Value);
                parameters.Add("PeriodToDate", periodToDate.Value);
            }

            if (singlePunch != -1)
                whereClauses.Add("dailyatt.INTIME = dailyatt.OUTTIME");

            string extraWhere = whereClauses.Count > 0
                ? " AND " + string.Join(" AND ", whereClauses)
                : string.Empty;

            string sql = DailyAttendanceQB.GET_DAILYATTENDANCE_MULTIPLE_EMPLOYEE.Replace(":replacefields", extraWhere);
            string countSql = DailyAttendanceQB.GET_DAILYATTENDANCE_MULTIPLE_EMPLOYEE_COUNT.Replace(":replacefields", extraWhere);

            var items = (await _queryexecutor.QueryAsync<DailyAttendanceLoadFromPunchDTO>(
                loginDTO, sql, parameters, cancellationToken: ct).ConfigureAwait(false)).ToList();

            int total = await _queryexecutor.ExecuteScalarAsync<int>(
                loginDTO, countSql, parameters, cancellationToken: ct).ConfigureAwait(false);

            foreach (var item in items)
            {
                item.InTimeInHrs = MinutesIntoTime(item.DailyAttendanceInTime);
                item.OutTimeInHrs = MinutesIntoTime(item.DailyAttendanceOutTime);
            }

            return (items, total);
        }
    }
}