using Dapper;
using Dapr.Client.Autogen.Grpc.v1;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.DBObject;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.Report;
using GB5Shared.GB5CommonFunction;
using GB5Shared.GB5Exception;
using GB5Shared.PickListGenerator;
using GB5Shared.QueryExecutor;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using PayRollDAL.CustomeCode.DailyAttendance;
using PayRollDAL.CustomeCode.PayConfiguration;
using PayRollDAL.CustomeCode.PayPeriod;
using PayRollDAL.DTO;
using PayRollDAL.DTO.DailyAttendance;
using PayRollDAL.DTO.Employee;
using PayRollDAL.DTO.Leave;
using PayRollDAL.DTO.PayConfiguration;
using PayRollDAL.DTO.PayPeriod;
using PayRollDAL.DTO.PayProcess;
using PayRollDAL.DTO.PayRevision;
using PayRollDAL.DTO.Periodic;
using PayRollDAL.Query.Address;
using PayRollDAL.Query.PayProcess;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.Common;
using System.Diagnostics;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Text.Json;
using System.Threading;
using System.Threading.Tasks;
using static GB5Shared.GB5Constant.Constant;

namespace PayRollDAL.CustomeCode.PayProcess
{
    public class PayProcessDAL : IPayProcessDAL
    {
        private readonly IDailyAttendanceDAL _DailyAttendanceDAL;
        private readonly IGB5CommonFunction _IGB5CommonFunction;
        private readonly IQueryExecutor _queryExecutor;
        private readonly IPickListExecutor _pickListExecutor;
        private readonly IPayConfigurationDAL _PayConfigurationDAL;
        private readonly IPayPeriodDAL _payPeriodDAL;
        private readonly ILogger<PayProcessDAL> _logger;

        public PayProcessDAL(DailyAttendanceDAL DailyAttendanceDAL, IGB5CommonFunction IGB5CommonFunction, IQueryExecutor IQueryExecutor,
            IPickListExecutor IPickListExecutor, IPayConfigurationDAL IPayConfigurationDAL, IPayPeriodDAL IPayPeriodDAL, ILogger<PayProcessDAL> logger)
        {
            _DailyAttendanceDAL = DailyAttendanceDAL;
            _IGB5CommonFunction = IGB5CommonFunction;
            _queryExecutor = IQueryExecutor;
            _pickListExecutor = IPickListExecutor;
            _PayConfigurationDAL = IPayConfigurationDAL;
            _payPeriodDAL = IPayPeriodDAL;
            _logger = logger;
        }


        // ── PayProcess/AdditionDeductionPicklist ─────────────────────────────
        // GB4 parity: PayProcessBLL.GetAdditionDeductionPicklist

        public async Task<string> GetAdditionDeductionPicklist(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            string id = null!;
            string searchText = null!;

            foreach (var section in CriteriaDTO?.SectionCriteriaList ?? Enumerable.Empty<SectionCriteriaDTO>())
            {
                foreach (var attr in section.AttributesCriteriaList ?? new List<AttributesCriteriaDTO>())
                {
                    var field = attr.FieldName?.Trim().ToLower();
                    switch (field)
                    {
                        case "id": id = GetStringValue(attr.FieldValue); break;
                        case "code":
                        case "name": searchText = GetStringValue(attr.FieldValue); break;
                    }
                }
            }

            var parameters = new DynamicParameters();
            parameters.Add("id", string.IsNullOrWhiteSpace(id) ? null : $"%{id}%");
            parameters.Add("searchtext", string.IsNullOrWhiteSpace(searchText) ? null : $"%{searchText}%");

            if (IsCount)
            {
                string countSql = LoginDTO.DatabaseType switch
                {
                    DBType.SQL => PayProcessQB.GET_ADDITION_DEDUCTION_PICKLIST_COUNT_SQL,
                    DBType.PostGre => PayProcessQB.GET_ADDITION_DEDUCTION_PICKLIST_COUNT_PG,
                    _ => throw new Exception("Unsupported database type")
                };

                var countRows = await _queryExecutor.QueryAsync<int>(LoginDTO, countSql, parameters).ConfigureAwait(false);
                return countRows.FirstOrDefault().ToString();
            }

            string sql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => PayProcessQB.GET_ADDITION_DEDUCTION_PICKLIST_SQL,
                DBType.PostGre => PayProcessQB.GET_ADDITION_DEDUCTION_PICKLIST_PG,
                _ => throw new Exception("Unsupported database type")
            };

            parameters.Add("firstnumber", FirstNumber);
            parameters.Add("maxresult", MaxResult);

            var result = await _queryExecutor.QueryAsync<AdditionDeductionPicklistDTO>(LoginDTO, sql, parameters).ConfigureAwait(false);
            return JsonConvert.SerializeObject(result);
        }

        // ── PayProcess/Addon/Selectlist ──────────────────────────────────────
        // GB4 parity: PayProcessBLL.PayprocessAddonPicklist

        public async Task<string> GetPayProcessAddonPicklist(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            if (CriteriaDTO == null || CriteriaDTO.SectionCriteriaList == null || CriteriaDTO.SectionCriteriaList.Count == 0)
                throw new NotFoundException("Criteria must be supplied");

            int payConfigId = 0;
            foreach (var attr in CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList ?? new List<AttributesCriteriaDTO>())
            {
                if (string.Equals(attr.FieldName, "payconfigid", StringComparison.OrdinalIgnoreCase))
                    payConfigId = GetIntValue(attr.FieldValue);
            }

            if (payConfigId == 0)
                throw new NotFoundException("PayConfigId must be supplied");

            string sql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => PayProcessQB.GET_PAYPROCESS_ADDON_PICKLIST_SQL,
                DBType.PostGre => PayProcessQB.GET_PAYPROCESS_ADDON_PICKLIST_PG,
                _ => throw new Exception("Unsupported database type")
            };

            var result = await _queryExecutor.QueryAsync<PayProcessAddonPickListDTO>(LoginDTO, sql, new { payconfigid = payConfigId }).ConfigureAwait(false);
            return JsonConvert.SerializeObject(result);
        }

        public async Task<List<PaySlipReportDTO>> GetPaySlipReport(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool Count = false, int Type = 0)
        {
            List<DailyAttendanceReportDTO> DailyAttendanceReportDTOs = null!;
            try
            {
                int PayPeriodId = 0;
                int PayConfigurationId = 0;
                string EmployeeId = "";
                string DepartmentId = "";
                string PayGroupId = "";
                string StaffTypeId = "";
                string AddonType = "";
                int OrderBy = -1;
                string PeriodFrom = "";
                string PeriodTo = "";
                int MonthlyWage = -1;
                string EmployeeType = "";
                string WokingDivision = "";
                string SalaryGrade = "";
                string WorkingUnit = "";
                string WorkType = "";
                string Location = "";
                string Segment = "";
                string WorkingOrganizationUnit = "";
                string OUId = "";
                string Branch = "";
                int IsPaidLeave = 0;//0 Yes 1 No  
                string InnerReplace = "";
                string ReplaceFilter = "";
                string LeaveReplace = "";
                string ExpiredPeriodTo = "";
                string AllotedToDateTo = "";
                string DailyOUOuterReplace = "";
                int PaySlipStatus = -1;
                string LeaveExpiredPeriodFrom = "";
                string LeaveExpiredPeriodTo = "";
                int IsMusterDetailsRequired = 1;//0 Yes 1 No 

                if (CriteriaDTO != null)
                {
                    if (CriteriaDTO == null)
                        throw new NotFoundException("Pay Configuration has to be passed..");

                    if (CriteriaDTO.SectionCriteriaList.Count == 0)
                        throw new NotFoundException("Supply atleast one section for Report");

                    var attributes = CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList;

                    for (int i = 0; i < attributes.Count; i++)
                    {
                        var attr = attributes[i];
                        var field = attr.FieldName?.ToLowerInvariant();

                        if (field == "payperiodid")
                        {
                            PayPeriodId = GetIntValue(attr.FieldValue);
                        }
                        else if (field == "payconfigurationid")
                        {
                            PayConfigurationId = GetIntValue(attr.FieldValue);
                        }
                        else if (field == "employeeid")
                        {
                            EmployeeId = GetStringValue(attr.FieldValue);
                        }
                        else if (field == "departmentid")
                        {
                            DepartmentId = GetStringValue(attr.FieldValue);
                        }
                        else if (field == "paygroupid")
                        {
                            PayGroupId = GetStringValue(attr.FieldValue);
                        }
                        else if (field == "stafftypeid")
                        {
                            StaffTypeId = GetStringValue(attr.FieldValue);
                        }
                        else if (field == "addontype")
                        {
                            AddonType = GetStringValue(attr.FieldValue);
                        }
                        else if (field == "orderby")
                        {
                            OrderBy = GetIntValue(attr.FieldValue);
                        }
                        else if (field == "periodfrom")
                        {
                            long epoch = GetLongValue(attr.FieldValue);
                            PeriodFrom = DateTimeOffset
                                            .FromUnixTimeMilliseconds(epoch)
                                            .DateTime
                                            .ToString("dd-MMM-yyyy");

                            LeaveExpiredPeriodFrom = PeriodFrom;
                        }
                        else if (field == "periodto")
                        {
                            long epoch = GetLongValue(attr.FieldValue);
                            PeriodTo = DateTimeOffset
                                            .FromUnixTimeMilliseconds(epoch)
                                            .DateTime
                                            .ToString("dd-MMM-yyyy");

                            ExpiredPeriodTo = PeriodTo;
                            AllotedToDateTo = PeriodTo;
                            LeaveExpiredPeriodTo = PeriodTo;
                        }
                        else if (field == "monthlywage")
                        {
                            MonthlyWage = GetIntValue(attr.FieldValue);
                        }
                        else if (field == "employeetype")
                        {
                            EmployeeType = GetStringValue(attr.FieldValue);
                        }
                        else if (field == "wokingdivision")
                        {
                            WokingDivision = GetStringValue(attr.FieldValue);
                        }
                        else if (field == "salarygrade")
                        {
                            SalaryGrade = GetStringValue(attr.FieldValue);
                        }
                        else if (field == "workingunit")
                        {
                            WorkingUnit = GetStringValue(attr.FieldValue);
                        }
                        else if (field == "worktype")
                        {
                            WorkType = GetStringValue(attr.FieldValue);
                        }
                        else if (field == "location")
                        {
                            Location = GetStringValue(attr.FieldValue);
                        }
                        else if (field == "segment")
                        {
                            Segment = GetStringValue(attr.FieldValue);
                        }
                        else if (field == "workingorganizationunit")
                        {
                            WorkingOrganizationUnit = GetStringValue(attr.FieldValue);
                        }
                        else if (field == "branch")
                        {
                            Branch = GetStringValue(attr.FieldValue);
                        }
                        else if (field == "ouid")
                        {
                            OUId = GetStringValue(attr.FieldValue);

                            InnerReplace += $" and TLEAVE.OUId in ({OUId})";
                            ReplaceFilter += $" and emp.WORKOUID in ({OUId})";
                            LeaveReplace += $" and l.OUId in ({OUId})";
                            DailyOUOuterReplace += $" and TLEAVE.OUId in ({OUId})";
                        }
                        else if (field == "ispaidleave")
                        {
                            IsPaidLeave = GetIntValue(attr.FieldValue);
                        }
                        else if (field == "payslipstatus")
                        {
                            PaySlipStatus = GetIntValue(attr.FieldValue);
                        }
                        else if (field == "ismusterdetailsrequired")
                        {
                            IsMusterDetailsRequired = GetIntValue(attr.FieldValue);
                        }
                    }

                }
                else
                {
                    throw new NotFoundException("Pay Configuration has to be passed..");
                }


                if (MonthlyWage == 0)
                {
                    if (PeriodFrom == "")
                    {
                        throw new NotFoundException("Supply Criteria doest not have PeriodFrom Filter or value is Empty please check ");
                    }
                    if (PeriodTo == "")
                    {
                        throw new NotFoundException("Supply Criteria doest not have PeriodTo Filter or value is Empty please check ");
                    }

                }
                else
                {
                    if (PayPeriodId == 0)
                    {
                        throw new NotFoundException("Supply Criteria doest not have PayPeriodId Filter or value is 0 please check");
                    }

                }
                if (PayConfigurationId == 0)
                {
                    throw new NotFoundException("Supply Criteria doest not have PayConfigurationId Filter or value is 0 please check ");
                }

                string UserOUId = "";

                string Sql1 = "";
                if (LoginDTO.DatabaseType == DBTYPE.SQL)
                {
                    Sql1 = PayProcessQB.USER_OU_ACCESS_RIGHTS_REPORT;
                }
                else if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                {
                    Sql1 = PayProcessQB.USER_OU_ACCESS_RIGHTS_REPORT_PG;
                }

                var parameterUserOU = new
                {
                    userid = LoginDTO.UserId
                };

                List<UserAccessOUReportDTO> UserAccessOUReportDTOs = (await _queryExecutor.QueryAsync<UserAccessOUReportDTO>(LoginDTO, Sql1, parameterUserOU!)).ToList();

                for (int i = 0; i < UserAccessOUReportDTOs.Count; i++)
                {
                    if (i == UserAccessOUReportDTOs.Count - 1)
                    {
                        UserOUId = UserOUId + UserAccessOUReportDTOs[i].OUId + "";
                    }
                    else
                    {
                        UserOUId = UserOUId + UserAccessOUReportDTOs[i].OUId + ",";
                    }

                }

                #region Employee and PayProcessDetail
                List<PayProcessReportDTO> PayProcessReports = new List<PayProcessReportDTO>();
                string HqlTOSQL = "";

                // Select the correct SQL based on Type
                if (Type == 0) // Normal
                {
                    if (LoginDTO.DatabaseType == DBTYPE.SQL)
                    {
                        HqlTOSQL = PayProcessQB.PAY_PROCESS_REPORT;
                    }
                    else if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                    {
                        HqlTOSQL = PayProcessQB.PAY_PROCESS_REPORT_PG;
                    }
                }
                else if (Type == 1) // Resigned Employee
                {
                    if (LoginDTO.DatabaseType == DBTYPE.SQL)
                    {
                        HqlTOSQL = PayProcessQB.PAY_PROCESS_REPORT_FOR_RESIGNED_EMPLOYEE;
                    }
                    else if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                    {
                        HqlTOSQL = PayProcessQB.PAY_PROCESS_REPORT_FOR_RESIGNED_EMPLOYEE_PG;
                    }
                }

                // Apply OU filter
                HqlTOSQL += " AND tp.OUID IN(" + UserOUId + ")";

                if (string.IsNullOrEmpty(OUId))
                {
                    HqlTOSQL += " AND tp.OUID IN(" + LoginDTO.WorkOUId + ")";
                }
                else
                {
                    HqlTOSQL += " AND tp.OUID IN(" + OUId + ")";
                }

                // Apply additional criteria
                HqlTOSQL = await ApplyPayProcessCriteria(HqlTOSQL, CriteriaDTO, LoginDTO);
                HqlTOSQL = await ApplyPayProcessCriteria(HqlTOSQL, LoginDTO.UserCriteriaDTO, LoginDTO);

                // Add ORDER BY using real table columns
                if (OrderBy == 0) // Monthly OverTime Report
                {
                    HqlTOSQL += "\r\n ORDER BY md.DEPARTMENTCODE";
                }
                else if (OrderBy == 1) // Monthly Wages Employee-wise Report
                {
                    HqlTOSQL += "\r\n ORDER BY tp.PAYPERIODID, md.DEPARTMENTCODE, me.EMPLOYEECODE";
                }
                else // Default
                {
                    HqlTOSQL += "\r\n ORDER BY MP.SORTORDER";
                }

                // Add pagination (SQL Server syntax)
                if (!Count)
                {
                    HqlTOSQL += "\r\n OFFSET " + FirstNumber + " ROWS FETCH NEXT " + MaxResult + " ROWS ONLY";
                }

                // Set query parameters
                var parameter = new
                {
                    ouid = OUId,
                    userouid = UserOUId,
                    payslipstatus = PaySlipStatus
                };

                // Execute query
                if (!Count)
                {
                    PayProcessReports = (await _queryExecutor.QueryAsync<PayProcessReportDTO>(LoginDTO, HqlTOSQL, parameter)).ToList();
                }
                else
                {
                    int Total = await _queryExecutor.TotalCountSql(HqlTOSQL, LoginDTO);
                    //return Total.ToString();
                }
                #endregion



                #region PyaConfigurationDetail
                string AddonReplaceMent = "";
                string ActualReplaceMent = "";
                PayConfigurationDTO PayConfigurationDTO = await _PayConfigurationDAL.GetPayConfiguration(PayConfigurationId, LoginDTO);
                AddonReplaceMent = "SUM(case PCD.Fieldcode " + "\r\n";
                ActualReplaceMent = "case PCD.ACTUALFIELDNAME " + "\r\n";
                for (int i = 0; i < PayConfigurationDTO.PayConfigurationDetailArray.Count; i++)
                {


                    if (PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailFieldType == 0)
                    {
                        AddonReplaceMent = AddonReplaceMent + " when " + "'" + PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailFieldCode + "' then PPA." + PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailFieldCode;
                    }
                    else if (PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailFieldType == 1)
                    {
                        AddonReplaceMent = AddonReplaceMent + " when " + "'" + PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailFieldCode + "' then PPA.F_" + PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailFieldCode;
                    }
                    else if (PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailFieldType == 3)
                    {
                        AddonReplaceMent = AddonReplaceMent + " when " + "'" + PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailFieldCode + "' then PPA.A_" + PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailFieldCode;
                    }

                }
                if (PayConfigurationDTO.PayConfigurationDetailArray.Count > 0)
                {
                    AddonReplaceMent = AddonReplaceMent + " end) AS FieldValue";
                }

                for (int i = 0; i < PayConfigurationDTO.PayConfigurationDetailArray.Count; i++)
                {



                    if (PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailActualFieldType == 0)
                    {
                        if (PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailActualFieldName == "" || PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailActualFieldName == null)
                        {
                            ActualReplaceMent = ActualReplaceMent + " when " + "'" + PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailActualFieldName + "' then 0 ";
                        }
                        else
                        {
                            ActualReplaceMent = ActualReplaceMent + " when " + "'" + PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailActualFieldName + "' then PPA." + PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailActualFieldName;
                        }
                    }
                    else if (PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailActualFieldType == 1)
                    {
                        if (PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailActualFieldName == "" || PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailActualFieldName == null)
                        {
                            ActualReplaceMent = ActualReplaceMent + " when " + "'" + PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailActualFieldName + "' then 0";
                        }
                        else
                        {
                            ActualReplaceMent = ActualReplaceMent + " when " + "'" + PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailActualFieldName + "' then PPA.F_" + PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailActualFieldName;
                        }
                    }
                    else if (PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailActualFieldType == 3)
                    {
                        if (PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailActualFieldName == "" || PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailActualFieldName == null)
                        {
                            ActualReplaceMent = ActualReplaceMent + " when " + "'" + PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailActualFieldName + "' then  0";
                        }
                        else
                        {
                            ActualReplaceMent = ActualReplaceMent + " when " + "'" + PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailActualFieldName + "' then PPA.A_" + PayConfigurationDTO.PayConfigurationDetailArray[i].PayConfigurationDetailActualFieldName;
                        }

                    }

                }
                if (PayConfigurationDTO.PayConfigurationDetailArray.Count > 0)
                {
                    ActualReplaceMent = ActualReplaceMent + " end as Numeric(18,4))  ) AS ActualFieldValue";
                }

                HqlTOSQL = PayProcessQB.GET_PAYSLIP_REPORT_ADDON_DETAIL;

                if (OUId == "")
                {
                    HqlTOSQL = HqlTOSQL + "and PP.OUId IN(" + LoginDTO.WorkOUId + ")";
                }
                else
                {
                    HqlTOSQL = HqlTOSQL + "and PP.OUId IN(" + OUId + ")";
                }

                HqlTOSQL = await ApplyPayProcessSqlCriteria(HqlTOSQL, CriteriaDTO, LoginDTO);
                HqlTOSQL = await ApplyPayProcessSqlCriteria(HqlTOSQL, LoginDTO.UserCriteriaDTO, LoginDTO);

                if (LoginDTO.DatabaseType == DBTYPE.SQL)
                {
                    HqlTOSQL = HqlTOSQL + "  group by E.EmployeeId,E.EmployeeCode,PCD.slno,PCD.FIELDTYPE,PCD.OPERATIONTYPE , " + "\r\n" +
                                  "case when PCD.FIELDTYPE=0 then addded.FIELDNAME  " + "\r\n" +
                                  "else  case when PCD.FIELDTYPE=1 then F.FormulaName " + "\r\n" +
                                  "else case when PCD.FIELDTYPE=2 then PCD.FIELDCODE " + "\r\n" +
                                  "else  case when PCD.FIELDTYPE=3 then adv.gcmname" + "\r\n" +
                                  "else PCD.FIELDCODE end  end   end  end," + "\r\n" +
                                  "case when PCD.FIELDTYPE=0 then addded.DISPLAYNAME" + "\r\n" +
                                  "else  case when PCD.FIELDTYPE=1 then F.PRINTINGNAME " + "\r\n" +
                                  "else case when PCD.FIELDTYPE=2 then PCD.ACTUALFIELDNAME " + "\r\n" +
                                  "else  case when PCD.FIELDTYPE=3 then adv.gcmname" + "\r\n" +
                                  "else PCD.FIELDCODE end  end   end  end,PCD.PrintRequired" + "\r\n";
                }
                else if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                {
                    HqlTOSQL = HqlTOSQL + @" 
                                        GROUP BY 
                                            E.EmployeeId,
                                            E.EmployeeCode,
                                            PCD.SlNo,
                                            PCD.FieldType,
                                            PCD.OperationType,

                                            CASE 
                                                WHEN PCD.FieldType = 0 THEN addded.FieldName
                                                WHEN PCD.FieldType = 1 THEN F.FormulaName
                                                WHEN PCD.FieldType = 2 THEN PCD.FieldCode
                                                WHEN PCD.FieldType = 3 THEN adv.gcmname
                                                ELSE PCD.FieldCode
                                            END,

                                            CASE 
                                                WHEN PCD.FieldType = 0 THEN addded.DisplayName
                                                WHEN PCD.FieldType = 1 THEN F.PrintingName
                                                WHEN PCD.FieldType = 2 THEN PCD.ActualFieldName
                                                WHEN PCD.FieldType = 3 THEN adv.gcmname
                                                ELSE PCD.FieldCode
                                            END,

                                            PCD.PrintRequired
                                        ";
                }

                var ParameterPayConfigurationDetail = new
                {
                    ouid = OUId,
                    payconfigid = PayConfigurationId,
                    payperiodid = PayPeriodId,
                    // AddOnValuesReplacePart = AddonReplaceMent,
                    // actualreplacement = "max(cast( " + ActualReplaceMent
                };

                HqlTOSQL = HqlTOSQL.Replace("@AddOnValuesReplacePart", AddonReplaceMent);
                HqlTOSQL = HqlTOSQL.Replace("@actualreplacement", "max(cast( " + ActualReplaceMent);

                HqlTOSQL = HqlTOSQL + "Order by E.EmployeeCode";
                List<EmployeePayConfigurationDetailReportDTO> EmployeePayConfigurationDetailReportDTOs = (await _queryExecutor.QueryAsync<EmployeePayConfigurationDetailReportDTO>(LoginDTO, HqlTOSQL, ParameterPayConfigurationDetail)).ToList();

                #endregion
                string jsontest = JsonConvert.SerializeObject(EmployeePayConfigurationDetailReportDTOs);
                #region Getting the Record From PayPeriod For PeriodId
                PayPeriodDTO PayPeriodDTO = await _payPeriodDAL.GetPeriod(PayPeriodId, LoginDTO);
                DateTime FromDate = PayPeriodDTO.PayPeriodFromDate;
                DateTime ToDate = PayPeriodDTO.PayPeriodToDate;
                int NumberofDays = ToDate.Subtract(FromDate).Days + 1;

                string FromPeriodDate = String.Format("{0:dd-MMM-yyyy}", FromDate);
                string ToPeriodDate = String.Format("{0:dd-MMM-yyyy}", ToDate);

                PeriodFrom = String.Format("{0:dd-MMM-yyyy}", FromDate);
                PeriodTo = String.Format("{0:dd-MMM-yyyy}", ToDate);
                AllotedToDateTo = String.Format("{0:dd-MMM-yyyy}", ToDate);
                #endregion

                #region Leave Detail


                #region AddonRegion

                ArrayList FieldNames = new ArrayList();

                #region Leave Addon
                string LeaveTypeQuery = "";
                if (LoginDTO.DatabaseType == DBTYPE.SQL)
                {
                    LeaveTypeQuery = PayProcessQB.GET_LEAVETYPE;
                }
                else if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                {
                    LeaveTypeQuery = PayProcessQB.GET_LEAVETYPE_PG;
                }
                List<LeaveDTO> Leaves = (await _queryExecutor.QueryAsync<LeaveDTO>(LoginDTO, LeaveTypeQuery, null!)).ToList();

                Leaves = Leaves.FindAll(
                delegate (LeaveDTO LeaveDTO)
                {
                    return LeaveDTO.LeaveLeaveType == 1;  //Paid Leave Only

                }
                );





                //String LeaveTakenFields = "  sum (";

                string LeaveTakenFields = "  sum ( Case leave.LEAVECODE " + "\r\n";
                for (int LeaveCount = 0; LeaveCount < Leaves.Count; LeaveCount++)
                {
                    if (Leaves.Count - 1 == LeaveCount)
                    {
                        //Last Case 
                        LeaveTakenFields = LeaveTakenFields + " when  '" + Leaves[LeaveCount].LeaveCode + "' then  monthattendaddon.L_" + Leaves[LeaveCount].LeaveCode + "\r\n" + " else 0 end )";
                    }
                    else
                    {
                        LeaveTakenFields = LeaveTakenFields + " when  '" + Leaves[LeaveCount].LeaveCode + "' then  monthattendaddon.L_" + Leaves[LeaveCount].LeaveCode + "\r\n";
                    }

                }
                string LeaveOpeningFields = " sum ( Case leave.LEAVECODE \n";

                for (int i = 0; i < Leaves.Count; i++)
                {
                    string yearStartExpr;

                    if (LoginDTO.DatabaseType == DBTYPE.SQL)
                    {
                        yearStartExpr = "DATEADD(yy, DATEDIFF(yy,0,@fromdate), 0)";
                    }
                    else
                    {
                        yearStartExpr = "date_trunc('year', @fromdate)";
                    }

                    if (i == Leaves.Count - 1)
                    {
                        LeaveOpeningFields +=
                            $" when '{Leaves[i].LeaveCode}' then " +
                            $" case when payperiod.FROMDATE = {yearStartExpr} " +
                            $" then monthattendaddon.L_{Leaves[i].LeaveCode}OP else 0 end \n" +
                            $" else 0 end )";
                    }
                    else
                    {
                        LeaveOpeningFields +=
                            $" when '{Leaves[i].LeaveCode}' then " +
                            $" case when payperiod.FROMDATE = {yearStartExpr} " +
                            $" then monthattendaddon.L_{Leaves[i].LeaveCode}OP else 0 end \n";
                    }
                }
                #endregion

                string Sql = "";

                string AllotedLeaveFields = "  sum ( Case leave.LEAVECODE " + "\r\n";
                for (int LeaveCount = 0; LeaveCount < Leaves.Count; LeaveCount++)
                {

                    if (Leaves.Count - 1 == LeaveCount)
                    {
                        AllotedLeaveFields = AllotedLeaveFields + " when '" + Leaves[LeaveCount].LeaveCode + "' then  leavedet.NUMBEROFDAYS" + "\r\n" + " else 0 end )" + "\r\n";
                    }
                    else
                    {
                        AllotedLeaveFields = AllotedLeaveFields + " when '" + Leaves[LeaveCount].LeaveCode + "' then  leavedet.NUMBEROFDAYS" + "\r\n" + "\r\n";
                    }
                }
                #endregion

                string OUReplace = "";

                if (OUId == "")
                {
                    OUReplace = OUReplace + "\r\n" + "and leave.ouid  IN(" + LoginDTO.WorkOUId + ")";
                }
                else
                {
                    OUReplace = OUReplace + "\r\n" + "and leave.ouid  IN(" + OUId + ")";
                }
                if (LoginDTO.DatabaseType == DBTYPE.SQL)
                {
                    Sql = PayProcessQB.LEAVE_STATUS_REPORT_PAY_SLIP;
                }
                else if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                {
                    Sql = PayProcessQB.LEAVE_STATUS_REPORT_PAY_SLIP_PG;
                }
                Sql = Sql
                    .Replace("{LeaveTakenFields}", LeaveTakenFields)
                    .Replace("{LeaveOpeningFields}", LeaveOpeningFields)
                    .Replace("@replaceispaidfield",
                        IsPaidLeave == 0 ? " AND leave.LeaveType = 1" : "");

                if (OUId == "")
                {
                    HqlTOSQL = HqlTOSQL + "and monthattend.ouid IN(" + LoginDTO.WorkOUId + ")";
                }
                else
                {
                    HqlTOSQL = HqlTOSQL + "and monthattend.ouid IN(" + OUId + ")";
                }

                //Sql = Sql.Replace("@allotedleavefields", AllotedLeaveFields)
                //    .Replace("@ouid", OUId)
                //    .Replace("@payperiodid", PayPeriodId.ToString())
                //    .Replace("@fromdate", FromPeriodDate)
                //    .Replace("@todate", ToDate.ToString())
                //    .Replace("@oureplace", OUReplace);

                Sql = Sql
    .Replace("@allotedleavefields", AllotedLeaveFields)
    .Replace("@ouid", OUId)
    .Replace("@payperiodid", PayPeriodId.ToString())
    .Replace("@oureplace", OUReplace);

                //var ParameterLeaveDetail = new
                //{
                //    //leavetakenfields = LeaveTakenFields,
                //    //leaveopeningfields = LeaveOpeningFields,
                //    //allotedleavefields = AllotedLeaveFields,
                //    //ouid = OUId,
                //    //payperiodid = PayPeriodId,
                //    fromdate = FromPeriodDate,
                //    //addonreplacement = "",
                //    todate = ToPeriodDate,
                //    //replaceispaidfield = Replaceispaidfield,
                //    //oureplace = OUReplace
                //};
                var ParameterLeaveDetail = new
                {
                    fromdate = FromDate,   // ✅ DateTime
                    todate = ToDate        // ✅ DateTime
                };
                List<EmployeeLeaveDetailReportDTO> EmployeeLeaveDetailReportDTOs = (await _queryExecutor.QueryAsync<EmployeeLeaveDetailReportDTO>(LoginDTO, Sql, ParameterLeaveDetail!)).ToList();

                #endregion

                #region AdvanceDetail
                if (LoginDTO.DatabaseType == DBTYPE.SQL)
                {
                    HqlTOSQL = PayProcessQB.GET_PAYSLIP_ADVANCE_DETAILS;
                }
                else if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                {
                    HqlTOSQL = PayProcessQB.GET_PAYSLIP_ADVANCE_DETAILS_PG;
                }
                HqlTOSQL = await ApplyPayProcessSqlCriteria(HqlTOSQL, CriteriaDTO, LoginDTO);
                HqlTOSQL = await ApplyPayProcessSqlCriteria(HqlTOSQL, LoginDTO.UserCriteriaDTO, LoginDTO);

                if (OUId == "")
                {
                    HqlTOSQL = HqlTOSQL + "and PP.OUId IN(" + LoginDTO.WorkOUId + ")";
                }
                else
                {
                    HqlTOSQL = HqlTOSQL + "and PP.OUId IN(" + OUId + ")";
                }
                var ParameterAdvance = new
                {
                    ouid = OUId,
                    payconfigid = PayConfigurationId,
                    payperiodid = PayPeriodId,
                };

                HqlTOSQL = HqlTOSQL + "Order by E.EmployeeCode";
                List<EmployeeAdvanceDetailReportDTO> EmployeeAdvanceDetailReportDTOs = (await _queryExecutor.QueryAsync<EmployeeAdvanceDetailReportDTO>(LoginDTO, HqlTOSQL, ParameterAdvance)).ToList();





                #endregion

                #region EmployeeAddress
                List<AddressDTO> AddressDTOs = new List<AddressDTO>();
                if (LoginDTO.DatabaseType == DBTYPE.SQL)
                {
                    HqlTOSQL = AddressQB.GET_ADDRESS_DETAIL_FOR_FULL_EMPLOYEE;
                } else if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                {
                    HqlTOSQL = AddressQB.GET_ADDRESS_DETAIL_FOR_FULL_EMPLOYEE_PG;
                }
                AddressDTOs = (await _queryExecutor.QueryAsync<AddressDTO>(LoginDTO, HqlTOSQL, null!)).ToList();
                #endregion


                // #region Leave Detail New added for 35544
                string OUFilterReplace = "";
                if (OUId == "")
                {
                    OUFilterReplace = OUFilterReplace + "\r\n" + "and a.ouid  IN(" + LoginDTO.WorkOUId + ")";
                }
                else
                {
                    OUFilterReplace = OUFilterReplace + "\r\n" + "and a.ouid  IN(" + OUId + ")";
                }

                var parameterValidTillDateLeave = new
                {
                    periodfrom = PeriodFrom,
                    periodto = PeriodTo,
                    ouid = OUId,
                    expiredperiodto = ExpiredPeriodTo,
                    replacefilter = ReplaceFilter,
                    innerreplace = InnerReplace,
                    // leavereplace = LeaveReplace,
                    allotedtodateto = AllotedToDateTo,
                    oufilterreplace = OUFilterReplace
                };
                if (LoginDTO.DatabaseType == DBTYPE.SQL)
                {
                    Sql = PayProcessQB.GET_VALID_TILL_DATE_LEAVE;
                } else if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                {
                    Sql = PayProcessQB.GET_VALID_TILL_DATE_LEAVE_PG;
                }
                Sql = Sql
                    .Replace("{leavereplace}", LeaveReplace);
                await _queryExecutor.ExecuteAsync(LoginDTO, Sql, parameterValidTillDateLeave!);

                //                string newSql = @"
                //" + PayProcessQB.OPENING_EXPIRED_LEAVE + @"
                //" + PayProcessQB.WIHIN_DATE_EXPIRED_LEAVE + @"
                //" + PayProcessQB.GET_LEAVE_STATUS_REPORT_WITH_OR_WITHOUT_DEBIT_CLASS_USED_IN_LEAVE_STATUS_REPORT;
                //                newSql = newSql.Replace("{leavereplace}", LeaveReplace);

                //                newSql = newSql.Replace("{oufilterreplace}", OUFilterReplace);

                //                var ParameterLeaveStatus = new
                //                {
                //                    periodfrom = PeriodFrom,
                //                    periodto = PeriodTo,
                //                    ouid = OUId,
                //                    expiredperiodto = ExpiredPeriodTo,
                //                    //replacefilter = ReplaceFilter,
                //                    innerreplace = InnerReplace,
                //                   // leavereplace = LeaveReplace,
                //                    dailyououterreplace = DailyOUOuterReplace,
                //                    allotedtodateto = AllotedToDateTo,
                //                    leaveexpiredperiodfrom = LeaveExpiredPeriodFrom,
                //                    leaveexpiredperiodto = LeaveExpiredPeriodTo,
                //                    cutoffdate = "31-Dec-9999",
                //                    //oufilterreplace = OUFilterReplace
                //                };
                //                newSql = await ApplyLeaveStatusNewReportCriteria(newSql, CriteriaDTO!, LoginDTO);
                //                newSql = await ApplyLeaveStatusNewReportCriteria(newSql, LoginDTO.UserCriteriaDTO, LoginDTO);

                //                //Task #37737 Biztransactionclass Filter is needed in Leave status report Done by Vikash on 03rd Jan 2022
                //                int TOIncludeDebitClass = 0;//
                //                if (TOIncludeDebitClass == 0)//Debit class Included 
                //                {
                //                    string withorwithoutdebitclass = "Left outer join  MBIZTRANSACTIONTYPE C on (  A.BIZTRANSACTIONTYPEID = C.BIZTRANSACTIONTYPEID   AND C.BIZTRANSACTIONCLASSID IN( -1399999898, -1399999816, -1399999955 ))";
                //                    newSql = newSql.Replace("@withorwithoutdebitclassfilter", withorwithoutdebitclass);
                //                }
                //                else
                //                {
                //                    //Removed  1399999816 Debit class 
                //                    string withorwithoutdebitclass = "Left outer join  MBIZTRANSACTIONTYPE C on (  A.BIZTRANSACTIONTYPEID = C.BIZTRANSACTIONTYPEID   AND C.BIZTRANSACTIONCLASSID IN( -1399999898, -1399999955 ))";
                //                    newSql = newSql.Replace("@withorwithoutdebitclassfilter", withorwithoutdebitclass);
                //                }
                //                newSql = newSql + "\r\n" + PayProcessQB.GET_LEAVE_STATUS_REPORT_GROUP_BY;
                //                newSql = newSql.Replace("@replacefilter", LeaveReplace);
                //                //await _queryExecutor.ExecuteAsync(LoginDTO, OpeningExpiredLeave, ParameterLeaveStatus);
                //                //await _queryExecutor.ExecuteAsync(LoginDTO, WithExpiredLeave, ParameterLeaveStatus);
                //                //IList<LeaveStatusDTO> LeaveStatusDTOs = (await _queryExecutor.QueryAsync<LeaveStatusDTO>(LoginDTO, Sql, ParameterLeaveStatus!)).ToList();
                //                #endregion

                //                IList<LeaveStatusDTO> LeaveStatusDTOs =
                //    (await _queryExecutor.QueryAsync<LeaveStatusDTO>(
                //        LoginDTO,
                //        newSql,
                //        ParameterLeaveStatus
                //    )).ToList();

                // string OUFilterReplace;
                if (string.IsNullOrWhiteSpace(OUId))
                {
                    OUFilterReplace = "\r\n AND A.OUID IN (" + LoginDTO.WorkOUId + ")";
                }
                else
                {
                    OUFilterReplace = "\r\n AND A.OUID IN (" + OUId + ")";
                }

                // ---------- BUILD SQL (ONE BATCH) ----------
                string newSql = "";
                if (LoginDTO.DatabaseType == DBTYPE.SQL)
                {
                    newSql =
                    PayProcessQB.OPENING_EXPIRED_LEAVE + "\r\n"
                  + PayProcessQB.WIHIN_DATE_EXPIRED_LEAVE + "\r\n"
                  + PayProcessQB.GET_LEAVE_STATUS_REPORT_WITH_OR_WITHOUT_DEBIT_CLASS_USED_IN_LEAVE_STATUS_REPORT;
                } else if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                {
                    newSql =
                    PayProcessQB.OPENING_EXPIRED_LEAVE_PG + "\r\n"
                  + PayProcessQB.WIHIN_DATE_EXPIRED_LEAVE_PG + "\r\n"
                  + PayProcessQB.GET_LEAVE_STATUS_REPORT_WITH_OR_WITHOUT_DEBIT_CLASS_USED_IN_LEAVE_STATUS_REPORT_PG;
                }

                // ---------- STRING REPLACEMENTS ----------
                newSql = newSql.Replace("{leavereplace}", LeaveReplace);
                newSql = newSql.Replace("{oufilterreplace}", OUFilterReplace);

                // ---------- APPLY DYNAMIC CRITERIA ----------
                newSql = await ApplyLeaveStatusNewReportCriteria(newSql, CriteriaDTO!, LoginDTO);
                newSql = await ApplyLeaveStatusNewReportCriteria(newSql, LoginDTO.UserCriteriaDTO, LoginDTO);

                // ---------- BIZ TRANSACTION CLASS FILTER ----------
                int TOIncludeDebitClass = 0; // 0 = Include Debit Class

                string withorwithoutdebitclass = "";

                if (LoginDTO.DatabaseType == DBTYPE.SQL)
                {
                    if (TOIncludeDebitClass == 0)
                    {
                        // Debit + Credit
                        withorwithoutdebitclass =
                            "LEFT OUTER JOIN MBIZTRANSACTIONTYPE C " +
                            "ON A.BIZTRANSACTIONTYPEID = C.BIZTRANSACTIONTYPEID " +
                            "AND C.BIZTRANSACTIONCLASSID IN (-1399999898, -1399999816, -1399999955)";
                    }
                    else
                    {
                        // Credit only
                        withorwithoutdebitclass =
                            "LEFT OUTER JOIN MBIZTRANSACTIONTYPE C " +
                            "ON A.BIZTRANSACTIONTYPEID = C.BIZTRANSACTIONTYPEID " +
                            "AND C.BIZTRANSACTIONCLASSID IN (-1399999898, -1399999955)";
                    }
                }
                else if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                {
                    if (TOIncludeDebitClass == 0)
                    {
                        // Debit + Credit
                        //withorwithoutdebitclass =
                        //    "LEFT JOIN \"mbiztransactiontype\" C " +
                        //    "ON A.\"biztransactiontypeid\" = C.\"biztransactiontypeid\" " +
                        //    "AND C.\"biztransactionclassid\" IN (-1399999898, -1399999816, -1399999955)";
                        withorwithoutdebitclass =
    "LEFT JOIN \"mbiztransactiontype\" bt " +
    "ON bt.\"biztransactiontypeid\" = a.\"biztransactiontypeid\" " +
    "AND bt.\"biztransactionclassid\" IN (-1399999898, -1399999816, -1399999955)";
                    }
                    else
                    {
                        // Credit only
                        //withorwithoutdebitclass =
                        //    "LEFT JOIN \"mbiztransactiontype\" C " +
                        //    "ON A.\"biztransactiontypeid\" = c.\"biztransactiontypeid\" " +
                        //    "AND C.\"biztransactionclassid\" IN (-1399999898, -1399999955)";
                        withorwithoutdebitclass =
    "LEFT JOIN \"mbiztransactiontype\" bt " +
    "ON bt.\"biztransactiontypeid\" = a.\"biztransactiontypeid\" " +
    "AND bt.\"biztransactionclassid\" IN (-1399999898,   -1399999955)";
                    }
                }

                newSql = newSql.Replace("@withorwithoutdebitclassfilter", withorwithoutdebitclass);



                // ---------- FINAL FILTER ----------
                if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                {
                    newSql = newSql.Replace("@replacefilter",
                        " AND emp.\"workouid\" IN (-1500000000) ");
                }
                else if (LoginDTO.DatabaseType == DBTYPE.SQL)
                {
                    newSql = newSql.Replace("@replacefilter",
                        " AND emp.workouid IN (-1500000000) ");
                }
                // ---------- SQL PARAMETERS (ONLY REAL PARAMETERS) ----------
                var ParameterLeaveStatus = new
                {
                    periodfrom = DateTime.Parse(PeriodFrom),
                    periodto = DateTime.Parse(PeriodTo),
                    allotedtodateto = DateTime.Parse(AllotedToDateTo),

                    leaveexpiredperiodfrom = string.IsNullOrWhiteSpace(PeriodFrom)
                        ? (DateTime?)null
                        : DateTime.Parse(PeriodFrom),

                    leaveexpiredperiodto = string.IsNullOrWhiteSpace(PeriodTo)
                        ? (DateTime?)null
                        : DateTime.Parse(PeriodTo),

                    cutoffdate = new DateTime(9999, 12, 31)
                };
                // ---------- GROUP BY ----------
                if (LoginDTO.DatabaseType == DBTYPE.SQL)
                {

                    newSql += "\r\n" + PayProcessQB.GET_LEAVE_STATUS_REPORT_GROUP_BY;
                }
                else if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                {
                    newSql += "\r\n" + PayProcessQB.GET_LEAVE_STATUS_REPORT_GROUP_BY_PG;
                }
                // ---------- EXECUTION ----------
                IList<LeaveStatusDTO> LeaveStatusDTOs =
                (
                    await _queryExecutor.QueryAsync<LeaveStatusDTO>(
                        LoginDTO,
                        newSql,
                        ParameterLeaveStatus
                    )
                ).ToList();


                //try
                //{
                //    // 1️⃣ Start SAME SESSION
                //    await _queryExecutor.BeginTransactionAsync(LoginDTO);

                //    // 2️⃣ Create #openingexpired / #openingexpired1
                //    await _queryExecutor.SessionExecuteAsync(
                //        LoginDTO,
                //        OpeningExpiredLeave,
                //        ParameterLeaveStatus
                //    );

                //    // 3️⃣ Create #withindateexpired / #withindateexpired1
                //    await _queryExecutor.SessionExecuteAsync(
                //        LoginDTO,
                //        WithExpiredLeave,
                //        ParameterLeaveStatus
                //    );

                //    // 4️⃣ Final SELECT (USES TEMP TABLES)
                //    LeaveStatusDTOs =
                //        (await _queryExecutor.SessionQueryAsync<LeaveStatusDTO>(
                //            LoginDTO,
                //            Sql,
                //            ParameterLeaveStatus
                //        )).ToList();

                //    // 5️⃣ Commit SAME SESSION
                //    await _queryExecutor.SameSessionCommitAsync();
                //}
                //catch
                //{
                //    // ❌ Rollback if anything fails
                //    await _queryExecutor.SameSessionRollbackAsync();
                //    throw;
                //}


                #region Final DTO
                List<PaySlipReportDTO> PaySlipReportDTOs = new List<PaySlipReportDTO>();

                for (int i = 0; i < PayProcessReports.Count; i++)
                {


                    PaySlipReportDTO PaySlipReportDTO = new PaySlipReportDTO();

                    #region By Vikash on 03 Sep 2021
                    List<PayProcessReportDTO> TempEmployeeGroupRecords = PayProcessReports.FindAll(
                      delegate (PayProcessReportDTO payprocess)
                      {
                          return payprocess.EmployeeId == PayProcessReports[i].EmployeeId;

                      }
                      );

                    decimal TotalEarnings = 0;
                    decimal TotalDeductions = 0;
                    decimal TotalNetSalary = 0;
                    for (int yy = 0; yy < TempEmployeeGroupRecords.Count; yy++)
                    {
                        TotalEarnings = TotalEarnings + Convert.ToDecimal(TempEmployeeGroupRecords[yy].TotalEarnings);
                        TotalDeductions = TotalDeductions + Convert.ToDecimal(TempEmployeeGroupRecords[yy].TotalDeductions);
                        TotalNetSalary = TotalNetSalary + Convert.ToDecimal(TempEmployeeGroupRecords[yy].NetSalary);
                    }

                    PaySlipReportDTO.TotalEarnings = TotalEarnings;
                    PaySlipReportDTO.TotalDeductions = TotalDeductions;
                    PaySlipReportDTO.TotalNetSalary = TotalNetSalary;
                    PaySlipReportDTO.TotalNetSalaryInWords = ChangeToWords(Convert.ToString(TotalNetSalary), "INR");
                    #endregion


                    PaySlipReportDTO.PayprocessReport = PayProcessReports[i];
                    PaySlipReportDTO.EmployeePayconfigurationDetailReportDTOs = EmployeePayConfigurationDetailReportDTOs.FindAll(
                       delegate (EmployeePayConfigurationDetailReportDTO DTO)
                       {
                           return DTO.EmployeeId == PayProcessReports[i].EmployeeId;

                       }
                       );

                    PaySlipReportDTO.EmployeeLeaveDetailReportDTOs = EmployeeLeaveDetailReportDTOs.FindAll(
                      delegate (EmployeeLeaveDetailReportDTO DTO)
                      {
                          return DTO.EmployeeId == PayProcessReports[i].EmployeeId;

                      }
                      );

                    PaySlipReportDTO.EmployeeAdvanceDetailReportDTOs = EmployeeAdvanceDetailReportDTOs.FindAll(
                      delegate (EmployeeAdvanceDetailReportDTO DTO)
                      {
                          return DTO.EmployeeId == PayProcessReports[i].EmployeeId;

                      }
                      );

                    List<AddressDTO> TempAddressDTOs = AddressDTOs.FindAll(
                    delegate (AddressDTO DTO)
                    {
                        return DTO.AddressObjectId == PayProcessReports[i].EmployeeId;

                    }
                    );
                    if (TempAddressDTOs.Count > 0)
                    {
                        PaySlipReportDTO.AddressDTO = TempAddressDTOs[0];
                    }
                    else
                    {
                        PaySlipReportDTO.AddressDTO = new AddressDTO();
                    }


                    List<LeaveStatusDTO> TempLeaveStatusDTOs = ((List<LeaveStatusDTO>)LeaveStatusDTOs).FindAll(
                    delegate (LeaveStatusDTO DTO)
                    {
                        return DTO.EmployeeId == PayProcessReports[i].EmployeeId;

                    }
                    );
                    if (TempLeaveStatusDTOs.Count > 0)
                    {
                        PaySlipReportDTO.LeaveStatusDTOs = TempLeaveStatusDTOs;
                    }
                    else
                    {
                        PaySlipReportDTO.LeaveStatusDTOs = new List<LeaveStatusDTO>();
                    }



                    PaySlipReportDTOs.Add(PaySlipReportDTO);
                }

                #region Added by Vikash on 06 June 2025 for Redmine Task #40494
                if (IsMusterDetailsRequired == 0)
                {

                    //in Case of Yes Only 
                    AttributesCriteriaDTO AttributesCriteriaDTO = new AttributesCriteriaDTO();
                    AttributesCriteriaDTO.FieldName = "ReportType";
                    AttributesCriteriaDTO.OperationType = CriteriaDTO.OperationType.Equal;
                    AttributesCriteriaDTO.FieldValue = 0;

                    if (CriteriaDTO != null)
                    {
                        if (CriteriaDTO.SectionCriteriaList != null)
                        {
                            if (CriteriaDTO.SectionCriteriaList.Count > 0)
                            {
                                if (CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList != null)
                                {
                                    if (CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList.Count > 0)
                                    {
                                        CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList.Add(AttributesCriteriaDTO);
                                    }
                                }
                            }
                        }
                    }
                    DailyAttendanceReportDTOs = await _DailyAttendanceDAL.GetDailyAttendanceReport(-1, -1, CriteriaDTO!, LoginDTO);
                    for (int iii = 0; iii < PaySlipReportDTOs.Count; iii++)
                    {

                        PaySlipReportDTOs[iii].DailyAttendanceReportDTOs = DailyAttendanceReportDTOs.FindAll(
                        delegate (DailyAttendanceReportDTO DTO)
                        {
                            return DTO.EmployeeId == PaySlipReportDTOs[iii].PayprocessReport.EmployeeId;

                        }
                        );
                    }
                }
                #endregion
                string Json = JsonConvert.SerializeObject(PaySlipReportDTOs);

                List<object> result = new List<object>();

                #endregion
                string PayConfigQuery = "";
                if (LoginDTO.DatabaseType == DBTYPE.SQL)
                {
                    PayConfigQuery = PayProcessQB.PAY_REVISON_EFFECTIVE_FROM;
                }
                else if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                {
                    PayConfigQuery = PayProcessQB.PAY_REVISON_EFFECTIVE_FROM_PG;
                }

                //var PayConfigParameter = new
                //{
                //    ouid = -1,
                //    payconfigid = PayConfigurationId,
                //    fromdate = FromPeriodDate,
                //    todate = ToPeriodDate,
                //};
                var PayConfigParameter = new
                {
                    ouid = -1,
                    payconfigid = PayConfigurationId,
                    fromdate = DateTime.Parse(FromPeriodDate),
                    todate = DateTime.Parse(ToPeriodDate)
                };
                List<PayRevisionDTO> PayRevisionDTOs = (await _queryExecutor.QueryAsync<PayRevisionDTO>(LoginDTO, PayConfigQuery, PayConfigParameter!)).ToList();

                for (int zz = 0; zz < PaySlipReportDTOs.Count; zz++)
                {
                    List<PayRevisionDTO> Temp = PayRevisionDTOs
                     .FindAll(
                    delegate (PayRevisionDTO DTO)
                    {
                        return DTO.EmployeeId == PaySlipReportDTOs[zz].PayprocessReport.EmployeeId;


                    }

                    );

                    if (Temp.Count > 0)
                    {
                        //PaySlipReportDTOs[zz].PayprocessReport.EffectiveFrom = Temp[0].PayRevisionEffectiveFrom;
                        //PaySlipReportDTOs[zz].PayprocessReport.EffectiveTo = Temp[0].PayRevisionEffectiveTo;
                    }
                    else
                    {
                        PaySlipReportDTOs[zz].PayprocessReport.EffectiveFrom = Convert.ToDateTime("1800-01-01 00:00:00.000");
                        PaySlipReportDTOs[zz].PayprocessReport.EffectiveTo = Convert.ToDateTime("1800-01-01 00:00:00.000");
                    }
                }
                var trans = await _queryExecutor.BeginTransactionAsync(LoginDTO);

                try
                {
                    #region To Add all Payprocess Addon Fields with the Same DTO for Redmine Task #25282 on 16th May 2019
                    if (Count == false && PayProcessReports.Count > 0 && Type == 0)
                    {
                        string Fields = "";
                        ArrayList AllAddonFields = new ArrayList();
                        string AllAddonQuery = "";
                        if (LoginDTO.DatabaseType == DBTYPE.SQL)
                        {
                            AllAddonQuery = PayProcessQB.ALL_PAYPROCSS_FIELDS;
                        }
                        else if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                        {
                            AllAddonQuery = PayProcessQB.ALL_PAYPROCSS_FIELDS_PG;
                        }
                        IList<DBObjectFieldsDTO> DBObjectFieldsDTOs = (await _queryExecutor.QueryAsync<DBObjectFieldsDTO>(LoginDTO, AllAddonQuery, null!)).ToList();
                        for (int i = 0; i < DBObjectFieldsDTOs.Count; i++)
                        {
                            AllAddonFields.Add(DBObjectFieldsDTOs[i].DBObjectFieldsName);
                            if (i == DBObjectFieldsDTOs.Count - 1)
                            {
                                Fields = Fields + DBObjectFieldsDTOs[i].DBObjectFieldsName;
                            }
                            else
                            {
                                Fields = Fields + DBObjectFieldsDTOs[i].DBObjectFieldsName + ",";
                            }
                        }

                        HqlTOSQL = "";
                        if (LoginDTO.DatabaseType == DBTYPE.SQL)
                        {
                            HqlTOSQL = PayProcessQB.CREATE_TEMP_PAYPROCESS_ADDON;
                        }
                        else if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                        {
                            HqlTOSQL = PayProcessQB.CREATE_TEMP_PAYPROCESS_ADDON_PG;
                        }
                        await _queryExecutor.ExecuteAsync(LoginDTO, HqlTOSQL, null!, trans);



                        #region INSERT INTO TEMP_PAYPROCESS                  
                        for (int i = 0; i < PaySlipReportDTOs.Count; i++)
                        {
                            if (LoginDTO.DatabaseType == DBTYPE.SQL)
                            {
                                HqlTOSQL = PayProcessQB.INSERT_INTO_TABLE;
                            } else if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                            {
                                HqlTOSQL = PayProcessQB.INSERT_INTO_TABLE_PG;
                            }
                            HqlTOSQL = HqlTOSQL.Replace(":payprocessId", PaySlipReportDTOs[i].PayprocessReport.PayProcessId.ToString());
                            HqlTOSQL = HqlTOSQL.Replace(":slno", i.ToString());
                            HqlTOSQL = HqlTOSQL.Replace(":payslipstatus", PaySlipReportDTOs[i].PayprocessReport.PaySlipStatus!.ToString());
                            await _queryExecutor.ExecuteAsync(LoginDTO, HqlTOSQL, null!, trans);
                        }



                        #endregion
                        #endregion

                        if (LoginDTO.DatabaseType == DBTYPE.SQL)
                        {
                            HqlTOSQL = PayProcessQB.CREATE_TEMP_PAYPROCESS_ADDON;
                        }
                        else if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                        {
                            HqlTOSQL = PayProcessQB.CREATE_TEMP_PAYPROCESS_ADDON_PG;
                        }
                        await _queryExecutor.ExecuteAsync(LoginDTO, HqlTOSQL, null!, trans);

                        // 2️⃣ INSERT DATA
                        for (int i = 0; i < PaySlipReportDTOs.Count; i++)
                        {
                            if (LoginDTO.DatabaseType == DBTYPE.SQL)
                            {
                                HqlTOSQL = PayProcessQB.INSERT_INTO_TABLE
                                .Replace(":payprocessId", PaySlipReportDTOs[i].PayprocessReport.PayProcessId.ToString())
                                .Replace(":slno", i.ToString())
                                .Replace(":payslipstatus", PaySlipReportDTOs[i].PayprocessReport.PaySlipStatus!.ToString());

                            }
                            else if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                            {
                                HqlTOSQL = PayProcessQB.INSERT_INTO_TABLE_PG
                                .Replace(":payprocessId", PaySlipReportDTOs[i].PayprocessReport.PayProcessId.ToString())
                                .Replace(":slno", i.ToString())
                                .Replace(":payslipstatus", PaySlipReportDTOs[i].PayprocessReport.PaySlipStatus!.ToString());

                            }
                            await _queryExecutor.ExecuteAsync(LoginDTO, HqlTOSQL, null!, trans);
                        }

                        // 3️⃣ BUILD SELECT QUERY
                        if (LoginDTO.DatabaseType == DBTYPE.SQL)
                        {
                            HqlTOSQL = PayProcessQB.GET_DETAIL_FROM_PAYPROCESS;
                        }
                        else if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                        {
                            HqlTOSQL = PayProcessQB.GET_DETAIL_FROM_PAYPROCESS_PG;
                        }

                        HqlTOSQL = await ApplyPayProcessAddonSqlCriteria(HqlTOSQL, CriteriaDTO!, LoginDTO);
                        HqlTOSQL = await ApplyPayProcessAddonSqlCriteria(HqlTOSQL, LoginDTO.UserCriteriaDTO, LoginDTO);

                        if (string.IsNullOrWhiteSpace(Fields))
                            HqlTOSQL = HqlTOSQL.Replace(":fields", "");
                        else
                            HqlTOSQL = HqlTOSQL.Replace(":fields", "," + Fields);

                        // 4️⃣ EXECUTE QUERY
                        IList addonDetail =
                            (await _queryExecutor.QueryAsync<object>(LoginDTO, HqlTOSQL, null!, trans))
                            .ToList();

                        // 5️⃣ MERGE RESULT


                        for (int i = 0; i < PaySlipReportDTOs.Count; i++)
                        {
                            JObject addonObj = JObject.FromObject(addonDetail[i]!);
                            int payProcessId = -1;
                            if (LoginDTO.DatabaseType == DBTYPE.SQL)
                            {
                                payProcessId = addonObj.Value<int>("PayProcessId");
                            }
                            else if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                            {
                                payProcessId = addonObj.Value<int>("payprocessid");
                            }

                            if (payProcessId == PaySlipReportDTOs[i].PayprocessReport.PayProcessId)
                            {
                                JObject headerObj = JObject.FromObject(PaySlipReportDTOs[i]);

                                foreach (string field in AllAddonFields)
                                {
                                    if (addonObj.TryGetValue(field, out JToken? value))
                                        headerObj[field] = value?.Type == JTokenType.Null
                                            ? JValue.CreateNull()
                                            : value;
                                    else
                                        headerObj[field] = JValue.CreateNull();
                                }

                                result.Add(headerObj);
                            }
                        }

                        Json = JsonConvert.SerializeObject(result);
                        result.Add(PaySlipReportDTOs);
                        // 6️⃣ COMMIT
                        await _queryExecutor.CommitAsync(trans);
                    }
                }
                catch (Exception)
                {
                    await _queryExecutor.RollbackAsync(trans);
                    throw;
                }
                return PaySlipReportDTOs;
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static int GetIntValue(object? value)
        {
            if (value == null) return 0;

            if (value is int i) return i;
            if (value is long l) return (int)l;

            if (value is JsonElement je)
            {
                if (je.ValueKind == JsonValueKind.Number && je.TryGetInt32(out int result))
                    return result;

                if (je.ValueKind == JsonValueKind.String &&
                    int.TryParse(je.GetString(), out result))
                    return result;
            }

            return 0;
        }

        private static long GetLongValue(object? value)
        {
            if (value == null) return 0;

            if (value is long l) return l;
            if (value is int i) return i;

            if (value is JsonElement je)
            {
                if (je.ValueKind == JsonValueKind.Number && je.TryGetInt64(out long result))
                    return result;

                if (je.ValueKind == JsonValueKind.String &&
                    long.TryParse(je.GetString(), out result))
                    return result;
            }

            return 0;
        }

        private static string GetStringValue(object? value)
        {
            if (value == null) return string.Empty;

            if (value is string s) return s;

            if (value is JsonElement je)
            {
                return je.ValueKind switch
                {
                    JsonValueKind.String => je.GetString() ?? string.Empty,
                    JsonValueKind.Number => je.GetRawText(),
                    _ => string.Empty
                };
            }

            return value.ToString() ?? string.Empty;
        }


        private async Task<String> ApplyLeaveStatusNewReportCriteria(String Sql, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {

                if (CriteriaDTO != null)
                {
                    string[,] CriteriaWithQueryField = new string[,]
                {
                   {"LeaveTypeId", "E.LEAVEID"},
                   {"IsApplicableForLeave", "E.ISAPPLICABLEFORLEAVEAPPLICATION"},
                   {"LeaveTypeId", "E.LEAVEID"},
                   {"DepartmentId", "emp.DepartmentId"},
                   {"EmployeeId","emp.EmployeeId"},
                   {"EmployeeTypeId","emp.EMPLOYEETYPEID"},
                   {"StaffTypeId","emp.STAFFTYPEID"},
                   {"PayGroupId","emp.PAYGROUPID"},
                   {"PayConfigurationId","emp.PAYCONFIGURATIONID"},
                  // {"OUId","A.OUId"},
                   //{"PeriodFrom","B.LEAVEDATE"},
                   //{"PeriodTo","B.LEAVEDATE"},
                   {"LeaveCode","E.LEAVECODE"},
                   {"EmployeeStatus","emp.EmployeeStatus"},
                };

                    Sql = await _pickListExecutor.ApplyCriteria(Sql, CriteriaWithQueryField, CriteriaDTO, LoginDTO);
                }
                return Sql;
            }
            catch (Exception)
            {
                throw;
            }
        }
        private static string ChangeToWords(string number, string currency)
        {
            if (decimal.TryParse(number, out decimal amount))
            {
                long integerPart = (long)Math.Floor(amount);
                int fractionPart = (int)((amount - integerPart) * 100);

                string words = NumberToWords(integerPart);
                if (fractionPart > 0)
                {
                    words += " and " + NumberToWords(fractionPart) + " Paise";
                }

                return words + " " + currency;
            }
            return "Zero " + currency;
        }

        private static string NumberToWords(long number)
        {
            if (number == 0)
                return "Zero";

            if (number < 0)
                return "Minus " + NumberToWords(Math.Abs(number));

            string words = "";

            if ((number / 10000000) > 0)
            {
                words += NumberToWords(number / 10000000) + " Crore ";
                number %= 10000000;
            }

            if ((number / 100000) > 0)
            {
                words += NumberToWords(number / 100000) + " Lakh ";
                number %= 100000;
            }

            if ((number / 1000) > 0)
            {
                words += NumberToWords(number / 1000) + " Thousand ";
                number %= 1000;
            }

            if ((number / 100) > 0)
            {
                words += NumberToWords(number / 100) + " Hundred ";
                number %= 100;
            }

            if (number > 0)
            {
                if (words != "")
                    words += "and ";

                string[] unitsMap = { "Zero", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten",
                              "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen",
                              "Seventeen", "Eighteen", "Nineteen" };
                string[] tensMap = { "Zero", "Ten", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety" };

                if (number < 20)
                    words += unitsMap[number];
                else
                {
                    words += tensMap[number / 10];
                    if ((number % 10) > 0)
                        words += " " + unitsMap[number % 10];
                }
            }

            return words.Trim();
        }


        public async Task<String> ApplyPayProcessCriteria(String SQL, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {

                if (CriteriaDTO != null)
                {
                    string[,] CriteriaWithQueryField = new string[,]
                    {
                   {"PayPeriodId", "TP.PayPeriodId"},
                   {"PayConfigurationId", "TP.PayConfigId"},
                   {"EmployeeId", "TP.EmployeeId"},
                   {"DepartmentId", "TP.Employee.Department.Id"},
                   {"PayGroupId", "TP.Employee.PayGroup.Id"},
                   {"StaffTypeId", "TP.Employee.StaffType.Id"},
                   {"PeriodFrom", "MP.FromDate"},
                   {"PeriodTo", "MP.ToDate"},

                   {"EmployeeType", "TP.Employee.EmployeeType.Id"},
                   {"WokingDivision", "TP.Employee.Division.Id"},
                   {"SalaryGrade", "TP.Employee.SalaryGrade.Id"},
                   {"WorkingUnit", "TP.Employee.Unit.Id"},
                   {"WorkType", "TP.Employee.WorkType.Id"},
                   {"Location", "TP.Employee.Location.Id"},
                   {"Segment", "TP.Employee.Segment.Id"},
                   {"WorkingOrganizationUnit", "TP.Employee.WorkOUId"},
                   {"Branch", "TP.Employee.Branch.Id"},
                   {"OUId", "TP.OUId"},
                   {"PaySlipStatus", "TP.PaySlipStatus"},
                   {"EmployeeStatus", "TP.Employee.EmployeeStatus"},





                    };

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


        public async Task<String> ApplyPayProcessSqlCriteria(String SQL, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                if (CriteriaDTO != null)
                {
                    string[,] CriteriaWithQueryField = new string[,]
                    {
                   {"OUId", "PP.ouid"},
                   {"PayPeriodId", "PP.PayPeriodId"},
                   {"PayConfigurationId", "PP.PAYCONFIGID"},
                   {"EmployeeId", "E.EmployeeId"},
                   {"DepartmentId", "E.DEPARTMENTID"},
                   {"PayGroupId", "E.PAYGROUPID"},
                   {"StaffTypeId", "E.STAFFTYPEID"},

                   {"EmployeeType", "E.EMPLOYEETYPEID"},
                   {"WokingDivision", "E.DIVISIONID"},
                   {"SalaryGrade", "E.SALARYGRADEID"},
                   {"WorkingUnit", "E.UNITID"},
                   {"WorkType", "E.WORKTYPEID"},
                   {"Location", "E.LOCATIONID"},
                   {"Segment", "E.SEGMENTID"},
                   {"Branch", "E.OURBRANCHID"},
                   {"PaySlipStatus", "PP.PaySlipStatus"},

                    };

                    SQL = await _pickListExecutor.ApplyCriteria(SQL, CriteriaWithQueryField, CriteriaDTO, LoginDTO);
                }
                return SQL;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<String> ApplyPayProcessAddonSqlCriteria(String SQL, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                if (CriteriaDTO != null)
                {
                    string[,] CriteriaWithQueryField = new string[,]
                    {

                     {"PaySlipStatus", "b.PaySlipStatus"},

                    };

                    SQL = await _pickListExecutor.ApplyCriteria(SQL, CriteriaWithQueryField, CriteriaDTO, LoginDTO);
                }
                return SQL;

            }
            catch (Exception)
            {
                throw;
            }
        }



















































        /// <summary>
        /// Get period details by work period ID
        /// </summary>
        public async Task<IList<PeriodDTO>> GetPeriodAsync(
            int workPeriodId,
            LoginDTO loginDTO,
            CancellationToken cancellationToken = default)
        {
            if (loginDTO == null) throw new ArgumentNullException(nameof(loginDTO));

            try
            {
                string sql = PayProcessQB.GET_YEAR_START_AND_END_DATE;

                _logger.LogInformation($"Executing GetPeriodAsync for WorkPeriodId: {workPeriodId}");

                var periodList = (await _queryExecutor.QueryAsync<PeriodDTO>(
                    loginDTO,
                    sql,
                    new { periodid = workPeriodId },
                    cancellationToken: cancellationToken
                )).ToList();

                foreach (var item in periodList)
                {
                    item.PeriodCode ??= string.Empty;
                    item.PeriodName ??= string.Empty;
                    item.PeriodCreatedByName ??= string.Empty;
                    item.PeriodModifiedByName ??= string.Empty;
                }

                _logger.LogInformation($"Fetched {periodList.Count} period(s) for WorkPeriodId {workPeriodId}");
                return periodList;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"Error fetching period for WorkPeriodId {workPeriodId}");
                throw new Exception("Error fetching period details", ex);
            }
        }

        /// <summary>
        /// Runs all pre-processing steps in a SINGLE connection
        /// so temp tables are visible to the main report query.
        /// Supports multiple OUIds.
        /// </summary>
        public async Task ExecuteLeaveStatusPreProcessingAsync(
    DateTime periodFrom,
    DateTime periodTo,
    string ouIds,
    LoginDTO loginDTO,
    CancellationToken cancellationToken = default)
        {
            if (loginDTO == null) throw new ArgumentNullException(nameof(loginDTO));

            try
            {
                DateTime from = TruncateToSeconds(periodFrom);
                DateTime to = TruncateToSeconds(periodTo);

                // Parse multiple OUIds into SQL IN clause
                string ouFilter = string.Empty;
                var ouList = ouIds?
                    .Split(',', StringSplitOptions.RemoveEmptyEntries)
                    .Select(o => int.TryParse(o, out var id) ? id : (int?)null)
                    .Where(x => x.HasValue)
                    .Select(x => x.Value)
                    .ToList();

                if (ouList != null && ouList.Count > 0)
                    ouFilter = $"AND l.OUId IN ({string.Join(",", ouList)})";

                // Use tuples instead of anonymous objects
                var steps = new List<(string SqlTemplate, DynamicParameters Params)>
        {
            (PayProcessQB.GET_VALID_TILL_DATE_LEAVE, new DynamicParameters(new { periodfrom = from })),
            (PayProcessQB.OPENING_EXPIRED_LEAVE, new DynamicParameters(new { leaveexpiredperiodfrom = from, leaveexpiredperiodto = to })),
            (PayProcessQB.WIHIN_DATE_EXPIRED_LEAVE, new DynamicParameters(new { leaveexpiredperiodfrom = from, leaveexpiredperiodto = to }))
        };

                foreach (var step in steps)
                {
                    string sql = step.SqlTemplate.Replace("{leavereplace}", ouFilter);

                    _logger.LogInformation($"Executing pre-process step for OUIds {ouIds}");
                    await _queryExecutor.ExecuteAsync(loginDTO, sql, step.Params, cancellationToken: cancellationToken);
                }

                _logger.LogInformation($"All pre-processing completed for OUIds {ouIds}");
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"Error in leave pre-processing for OUIds {ouIds}");
                throw new Exception($"Error in leave status pre-processing for OUIds {ouIds}", ex);
            }
        }

        /// <summary>
        /// Main leave status report query — temp tables must exist in the same session.
        /// Supports all GB4 filters.
        /// </summary>
        public async Task<IList<LeaveStatusDTO>> GetLeaveStatusReportAsync(
    DateTime periodFrom,
    DateTime periodTo,
    string ouIds,
    int toIncludeDebitClass,
    LoginDTO loginDTO,
    int? employeeId = null,
    bool isScreenPeriod = false,
    int? departmentId = null,
    int? leaveTypeId = null,
    int? employeeTypeId = null,
    int? staffTypeId = null,
    int? payGroupId = null,
    CancellationToken cancellationToken = default)
        {
            if (loginDTO == null) throw new ArgumentNullException(nameof(loginDTO));

            try
            {
                DateTime from = ToSqlSafeDate(periodFrom);
                DateTime to = ToSqlSafeDate(periodTo);

                // GB4 parity: Type == -2 ("screen" case) leaves the TLEAVEDETAIL join to
                // B.LeaveDate <= cutoffdate unbounded ('31-Dec-9999'); otherwise cutoffdate = periodTo.
                DateTime cutOffDate = isScreenPeriod ? new DateTime(9999, 12, 31) : to;

                // ── OU filter ─────────────────────────
                string ouFilter = string.Empty;

                var ouList = ouIds?
                    .Split(',', StringSplitOptions.RemoveEmptyEntries)
                    .Select(o => int.TryParse(o, out var id) ? id : (int?)null)
                    .Where(x => x.HasValue)
                    .Select(x => x.Value)
                    .ToList();

                if (ouList != null && ouList.Count > 0)
                    ouFilter = $" AND emp.WorkOUID IN ({string.Join(",", ouList)})";

                // GB4 parity: TOIncludeDebitClass == 1 ("No") drops the Leave Debit class
                // (-1399999816) from the bt join entirely; default (0, "Yes") includes it.
                string debitClassFilter = toIncludeDebitClass == 1
                    ? " AND bt.BIZTRANSACTIONCLASSID IN (-1399999898,-1399999955) "
                    : " AND bt.BIZTRANSACTIONCLASSID IN (-1399999898,-1399999816,-1399999955) ";

                // ── SQL ────────────────────────────────
                var sqlBuilder = new StringBuilder(PayProcessQB.GET_LEAVE_STATUS_REPORT);

                // OU filter
                sqlBuilder.Replace("@OUFILTER", ouFilter);

                // Debit-class toggle
                sqlBuilder.Replace("@DEBITCLASSFILTER", debitClassFilter);

                // Employee filter
                string employeeFilter = employeeId.HasValue
                    ? " AND emp.EMPLOYEEID = @EmployeeId "
                    : string.Empty;
                sqlBuilder.Replace("@EMPLOYEEFILTER", employeeFilter);

                // ── Extra filters (GB4 ApplyLeaveStatusNewReportCriteria parity) ──
                var parameters = new DynamicParameters();
                var extraFilters = new StringBuilder();

                if (departmentId.HasValue)
                {
                    extraFilters.Append(" AND d.DEPARTMENTID = @DepartmentId ");
                    parameters.Add("@DepartmentId", departmentId.Value);
                }
                if (leaveTypeId.HasValue)
                {
                    extraFilters.Append(" AND e.LEAVEID = @LeaveTypeId ");
                    parameters.Add("@LeaveTypeId", leaveTypeId.Value);
                }
                if (employeeTypeId.HasValue)
                {
                    extraFilters.Append(" AND emp.EMPLOYEETYPEID = @EmployeeTypeId ");
                    parameters.Add("@EmployeeTypeId", employeeTypeId.Value);
                }
                if (staffTypeId.HasValue)
                {
                    extraFilters.Append(" AND emp.STAFFTYPEID = @StaffTypeId ");
                    parameters.Add("@StaffTypeId", staffTypeId.Value);
                }
                if (payGroupId.HasValue)
                {
                    extraFilters.Append(" AND emp.PAYGROUPID = @PayGroupId ");
                    parameters.Add("@PayGroupId", payGroupId.Value);
                }

                sqlBuilder.Replace("@EXTRAFILTERS", extraFilters.ToString());

                // ── Parameters ─────────────────────────
                parameters.Add("@PeriodFrom", from);
                parameters.Add("@PeriodTo", to);
                parameters.Add("@LeaveExpiredPeriodFrom", from);
                parameters.Add("@LeaveExpiredPeriodTo", to);
                parameters.Add("@CutOffDate", cutOffDate);
                parameters.Add("@AllotedToDateTo", to);
                if (employeeId.HasValue)
                    parameters.Add("@EmployeeId", employeeId.Value);

                _logger.LogInformation($"Executing GetLeaveStatusReportAsync for OUIds {ouIds}");

                var leaveStatusList = (await _queryExecutor.QueryAsync<LeaveStatusDTO>(
                    loginDTO,
                    sqlBuilder.ToString(),
                    parameters,
                    cancellationToken: cancellationToken
                )).ToList();

                // ── Null safety ────────────────────────
                foreach (var leave in leaveStatusList)
                {
                    leave.EmployeeCode ??= string.Empty;
                    leave.EmployeeName ??= string.Empty;
                    leave.OUCode ??= string.Empty;
                    leave.OUName ??= string.Empty;
                }

                _logger.LogInformation($"Fetched {leaveStatusList.Count} leave status records for OUIds {ouIds}");
                return leaveStatusList;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"Error fetching leave status report for OUIds {ouIds}");
                throw;
            }
        }

        /// <summary>
        /// Truncate DateTime to seconds — strips sub-millisecond ticks that cause SQL datetime precision errors.
        /// </summary>
        private static DateTime TruncateToSeconds(DateTime dt)
        {
            return new DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second, 0, dt.Kind);
        }
        private static DateTime ToSqlSafeDate(DateTime date)
        {
            return date < new DateTime(1753, 1, 1)
                ? new DateTime(1753, 1, 1)
                : date;
        }

        // ════════════════════════════════════════════════════════════════
        // PayProcessForSepration — GB4 PayProcessBLL.PayProcessForSepration.
        // All calls run within the caller-supplied transaction (single
        // transaction spans undo + insert + Payparsing/TotalEarning/C2C).
        // ════════════════════════════════════════════════════════════════

        public async Task<int> GetSeparationDuplicateCount(int ouId, int payConfigurationId, int employeeId, LoginDTO loginDTO, DbTransaction transaction)
        {
            return await _queryExecutor.ExecuteScalarAsync<int>(
                loginDTO, PayProcessQB.SEPARATION_DUPLICATE_CHECK_COUNT,
                new { OuId = ouId, PayPeriodId = PayProcessQB.SEPARATION_PAY_PERIOD_ID, PayConfigurationId = payConfigurationId, EmployeeId = employeeId },
                transaction).ConfigureAwait(false);
        }

        public async Task UndoSeparationPayProcess(int ouId, int payConfigurationId, int employeeId, LoginDTO loginDTO, DbTransaction transaction)
        {
            var parameters = new { OuId = ouId, PayPeriodId = PayProcessQB.SEPARATION_PAY_PERIOD_ID, PayConfigurationId = payConfigurationId, EmployeeId = employeeId };

            // Order matches GB4: addon rows -> unlink relieving settlement -> header rows.
            await _queryExecutor.ExecuteAsync(loginDTO, PayProcessQB.SEPARATION_UNDO_PAYPROCESSADDON, parameters, transaction).ConfigureAwait(false);
            await _queryExecutor.ExecuteAsync(loginDTO, PayProcessQB.SEPARATION_UNLINK_RELIEVINGSETTLEMENT, parameters, transaction).ConfigureAwait(false);
            await _queryExecutor.ExecuteAsync(loginDTO, PayProcessQB.SEPARATION_UNDO_PAYPROCESS, parameters, transaction).ConfigureAwait(false);
        }

        public async Task<int> InsertSeparationPayProcessHeader(
            int startNumber, int payPeriodId, int payConfigurationId, int ouId,
            decimal numberOfDays, int totalDays, DateTime fromDate, DateTime toDate, int weeklyOff, int employeeId,
            LoginDTO loginDTO, DbTransaction transaction)
        {
            var parameters = new
            {
                StartNumber = startNumber,
                PayPeriodId = payPeriodId,
                PayConfigurationId = payConfigurationId,
                OuId = ouId,
                NumberOfDays = numberOfDays,
                TotalDays = totalDays,
                FromDate = ToSqlSafeDate(fromDate).Date,
                WeeklyOff = weeklyOff,
                EmployeeId = employeeId
            };
            return await _queryExecutor.ExecuteAsync(loginDTO, PayProcessQB.SEPARATION_INSERT_PAYPROCESS_HEADER, parameters, transaction).ConfigureAwait(false);
        }

        public async Task UpdateSeparationDailyAttendanceSum(int ouId, int payConfigurationId, int employeeId, LoginDTO loginDTO, DbTransaction transaction)
        {
            await _queryExecutor.ExecuteAsync(
                loginDTO, PayProcessQB.SEPARATION_UPDATE_DAILYATTENDANCE_SUM,
                new { OuId = ouId, PayConfigurationId = payConfigurationId, EmployeeId = employeeId },
                transaction).ConfigureAwait(false);
        }

        public async Task<IList<(string Code, int ApplicableType, int Nature, int FieldDataType)>> GetFixedAdditionDeductionFields(LoginDTO loginDTO, DbTransaction transaction)
        {
            var rows = await _queryExecutor.QueryAsync<AddonMetadataRow>(loginDTO, PayProcessQB.GET_FIXED_ADDITIONDEDUCTION_FIELDS, null, transaction).ConfigureAwait(false);
            return rows.Select(r => (r.Code, r.ApplicableType, r.Nature, r.FieldDataType)).ToList();
        }

        public async Task<IList<(string Code, int ApplicableType, int Nature, int FieldDataType)>> GetVariableAdditionDeductionFields(LoginDTO loginDTO, DbTransaction transaction)
        {
            var rows = await _queryExecutor.QueryAsync<AddonMetadataRow>(loginDTO, PayProcessQB.GET_VARIABLE_ADDITIONDEDUCTION_FIELDS, null, transaction).ConfigureAwait(false);
            return rows.Select(r => (r.Code, r.ApplicableType, r.Nature, r.FieldDataType)).ToList();
        }

        public async Task<IList<string>> GetCreditLeaveCodes(LoginDTO loginDTO, DbTransaction transaction)
        {
            var rows = await _queryExecutor.QueryAsync<string>(loginDTO, PayProcessQB.GET_CREDIT_LEAVE_CODES, null, transaction).ConfigureAwait(false);
            return rows.ToList();
        }

        public async Task InsertSeparationPayProcessAddon(
            int startNumber, int payConfigurationId, int ouId, DateTime fromDate, DateTime toDate, int employeeId,
            string dynamicColumns, string dynamicValues, LoginDTO loginDTO, DbTransaction transaction)
        {
            var sql = PayProcessQB.BuildSeparationAddonInsertSql(dynamicColumns, dynamicValues);
            var parameters = new
            {
                StartNumber = startNumber,
                PayPeriodId = PayProcessQB.SEPARATION_PAY_PERIOD_ID,
                PayConfigurationId = payConfigurationId,
                OuId = ouId,
                FromDate = ToSqlSafeDate(fromDate).Date,
                EmployeeId = employeeId
            };
            await _queryExecutor.ExecuteAsync(loginDTO, sql, parameters, transaction).ConfigureAwait(false);
        }

        public async Task<IList<(string Code, int Phase, int FormulaType, string Expression, string FunctionName, int DataType)>> GetFormulaFields(LoginDTO loginDTO, DbTransaction transaction)
        {
            var rows = await _queryExecutor.QueryAsync<FormulaMetadataRow>(loginDTO, PayProcessQB.GET_FORMULA_FIELDS, null, transaction).ConfigureAwait(false);
            return rows.Select(r => (r.Code, r.Phase, r.FormulaType, r.Expression, r.FunctionName, r.DataType)).ToList();
        }

        public async Task<int> GetMaxFormulaPhase(LoginDTO loginDTO, DbTransaction transaction)
        {
            return await _queryExecutor.ExecuteScalarAsync<int>(loginDTO, PayProcessQB.GET_MAX_FORMULA_PHASE, null, transaction).ConfigureAwait(false);
        }

        public async Task UpdatePayProcessAddonByFormula(int payPeriodId, int payConfigurationId, int employeeId, string strUpdate, LoginDTO loginDTO, DbTransaction transaction)
        {
            var sql = PayProcessQB.BuildFormulaUpdateSql(strUpdate);
            await _queryExecutor.ExecuteAsync(loginDTO, sql, new { PayPeriodId = payPeriodId, PayConfigurationId = payConfigurationId, EmployeeId = employeeId }, transaction).ConfigureAwait(false);
        }

        public async Task UpdateTotalEarningDeduction(int payConfigurationId, int payPeriodId, int ouId, string totalEarningExpr, string totalDeductionExpr, string totalAdditionExpr, LoginDTO loginDTO, DbTransaction transaction)
        {
            var sql = PayProcessQB.BuildTotalEarningDeductionSql(totalEarningExpr, totalDeductionExpr, totalAdditionExpr);
            await _queryExecutor.ExecuteAsync(loginDTO, sql, new { PayConfigurationId = payConfigurationId, PayPeriodId = payPeriodId, OuId = ouId }, transaction).ConfigureAwait(false);
        }

        public async Task UpdateNetSalary(int payConfigurationId, int payPeriodId, int ouId, LoginDTO loginDTO, DbTransaction transaction)
        {
            await _queryExecutor.ExecuteAsync(loginDTO, PayProcessQB.SEPARATION_UPDATE_NETSALARY, new { PayConfigurationId = payConfigurationId, PayPeriodId = payPeriodId, OuId = ouId }, transaction).ConfigureAwait(false);
        }

        public async Task UpdateC2C(int payConfigurationId, int payPeriodId, int ouId, string replaceFieldsExpr, LoginDTO loginDTO, DbTransaction transaction)
        {
            var sql = PayProcessQB.BuildC2CUpdateSql(replaceFieldsExpr);
            await _queryExecutor.ExecuteAsync(loginDTO, sql, new { PayConfigurationId = payConfigurationId, PayPeriodId = payPeriodId, OuId = ouId }, transaction).ConfigureAwait(false);
        }

        private sealed class AddonMetadataRow
        {
            public string Code { get; set; } = string.Empty;
            public int ApplicableType { get; set; }
            public int Nature { get; set; }
            public int FieldDataType { get; set; }
        }

        private sealed class FormulaMetadataRow
        {
            public string Code { get; set; } = string.Empty;
            public int Phase { get; set; }
            public int FormulaType { get; set; }
            public string Expression { get; set; } = string.Empty;
            public string FunctionName { get; set; } = string.Empty;
            public int DataType { get; set; }
        }
    }
}