using AdminDAL.CustomCode.AdvanceRights;
using AdminDAL.DTO.AdvanceRights;
using AdminDAL.DTO.Allocation;
using AdminDAL.DTO.BIZTransactionType;
using AdminDAL.DTO.InventorySetting;
using AdminDAL.DTO.LockSetting;
using AdminDAL.DTO.OrganizationUnit;
using AdminDAL.DTO.RoleVsMenu;
using AdminDAL.Query.AllocationType;
using AdminDAL.Query.BIZTransaction;
using AdminDAL.Query.BIZTransactionType;
using Dapper;
using GB5Shared.CriteriaHandler;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5Exception;
using GB5Shared.Query.FrameWork.User;
using GB5Shared.QueryExecutor;
using AdminDAL.DTO.Charge;
using AdminDAL.Query.Charge;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection.Metadata;
using System.Security.Cryptography;
using System.Text;
using System.Text.Json.Serialization;
using System.Threading.Tasks;
using static AdminDAL.DTO.BIZTransactionType.BIZTransactionTypeDTO;
using static GB5Shared.GB5Constant.Constant;

namespace AdminDAL.CustomCode.BIZTransactionType
{
    public class BIZTransactionTypeDAL : IBIZTransactionTypeDAL
    {
        private readonly IAdvanceRightsDAL AdvanceUserRights;
        private readonly IQueryExecutor queryExecutor;

        public BIZTransactionTypeDAL(IAdvanceRightsDAL advanceUserRights , IQueryExecutor IQueryExecutor)
        {
            AdvanceUserRights = advanceUserRights;
            queryExecutor = IQueryExecutor;
        }
        public async Task<FormLoadSettingsDTO> GetFormSettings(int MenuId, int BIZTransactionTypeId, LoginDTO LoginDTO)
        { 
            try
            {
                FormLoadSettingsDTO FormLoadSettingsDTO = await GetBackDateBasedSettings(BIZTransactionTypeId, LoginDTO);
                FormLoadSettingsDTO.BizTransactionTypeId = BIZTransactionTypeId;
                IList<FormLoadSettingsDTO> FormLoadSettingsDTOs = new List<AdminDAL.DTO.BIZTransactionType.FormLoadSettingsDTO>();
                FormLoadSettingsDTOs = (await GetBizTransactionTypeClassDetailsNew(BIZTransactionTypeId, LoginDTO)).ToList();
                if (FormLoadSettingsDTOs.Count != 0)
                {
                    FormLoadSettingsDTO.BizTransactionTypeCode = FormLoadSettingsDTOs[0].BizTransactionTypeCode;
                    FormLoadSettingsDTO.BizTransactionTypeName = FormLoadSettingsDTOs[0].BizTransactionTypeName;
                    FormLoadSettingsDTO.BizTransactionId = FormLoadSettingsDTOs[0].BizTransactionId;
                    FormLoadSettingsDTO.BizTransactionCode = FormLoadSettingsDTOs[0].BizTransactionCode;
                    FormLoadSettingsDTO.BizTransactionName = FormLoadSettingsDTOs[0].BizTransactionName;
                    FormLoadSettingsDTO.BizTransactionClassId = FormLoadSettingsDTOs[0].BizTransactionClassId;
                    FormLoadSettingsDTO.BizTransactionClassCode = FormLoadSettingsDTOs[0].BizTransactionClassCode;
                    FormLoadSettingsDTO.BizTransactionClassName = FormLoadSettingsDTOs[0].BizTransactionClassName;
                    FormLoadSettingsDTO.BizTransactionSubClassId = FormLoadSettingsDTOs[0].BizTransactionSubClassId;
                    FormLoadSettingsDTO.BizTransactionSubClassCode = FormLoadSettingsDTOs[0].BizTransactionSubClassCode;
                    FormLoadSettingsDTO.BizTransactionSubClassName = FormLoadSettingsDTOs[0].BizTransactionSubClassName;

                }
                #region Get Role Rights Based Settings Add Adding to DTO
                List<RoleVsMenuDTO> RoleVsMenuDTOs =  await GetRoleVsMenuBasedOnMenuIdAndRoleId(MenuId, LoginDTO.RoleId, LoginDTO);
                if (RoleVsMenuDTOs.Count == 1)
                {
                    FormLoadSettingsDTO.RoleId = RoleVsMenuDTOs[0].RoleVsMenuRoleId;
                    FormLoadSettingsDTO.RoleName = RoleVsMenuDTOs[0].RoleVsMenuRoleName;
                    FormLoadSettingsDTO.MenuId = RoleVsMenuDTOs[0].RoleVsMenuMenuId;
                    FormLoadSettingsDTO.MenuName = RoleVsMenuDTOs[0].RoleVsMenuMenuName;
                    FormLoadSettingsDTO.Allow = RoleVsMenuDTOs[0].RoleVsMenuAllow;
                    FormLoadSettingsDTO.View = RoleVsMenuDTOs[0].RoleVsMenuView;
                    FormLoadSettingsDTO.Insert = RoleVsMenuDTOs[0].RoleVsMenuInsert;
                    FormLoadSettingsDTO.Update = RoleVsMenuDTOs[0].RoleVsMenuUpdate;
                    FormLoadSettingsDTO.Delete = RoleVsMenuDTOs[0].RoleVsMenuDelete;
                    FormLoadSettingsDTO.Print = RoleVsMenuDTOs[0].RoleVsMenuPrint;
                    FormLoadSettingsDTO.Export = RoleVsMenuDTOs[0].RoleVsMenuExport;
                    FormLoadSettingsDTO.EntityId = RoleVsMenuDTOs[0].RoleVsMenuMenuEntityId;
                    FormLoadSettingsDTO.EntityCode = RoleVsMenuDTOs[0].RoleVsMenuMenuEntityCode!;
                    FormLoadSettingsDTO.EntityName = RoleVsMenuDTOs[0].RoleVsMenuMenuEntityName!;


                    //Entity Entity = Generic.Get<Entity>(RoleVsMenuDTOs[0].RoleVsMenuMenuEntityId, LoginDTO);
                    //FormLoadSettingsDTO.EntityCode = Entity.Code;
                    //FormLoadSettingsDTO.EntityName = Entity.Name;
                    FormLoadSettingsDTO.URIParameterValue = RoleVsMenuDTOs[0].RoleVsMenuMenuURIParameterValue;

                    #region Get Advance Rights
                    if (FormLoadSettingsDTO.BizTransactionClassId != -1)
                    {
                        IEnumerable<AdvanceRightsDTO> AdvanceUserRightsCheckDTOs = await AdvanceUserRights.GetAdvanceUserRights(MenuId, FormLoadSettingsDTO.BizTransactionClassId, BIZTransactionTypeId, LoginDTO);
                        AdvanceRightsDTO rights = AdvanceUserRightsCheckDTOs.FirstOrDefault()!;
                        if (rights != null)
                        {
                            FormLoadSettingsDTO.Allow = rights.AdvanceRightsAllow;
                            FormLoadSettingsDTO.View = rights.AdvanceRightsView;
                            FormLoadSettingsDTO.Insert = rights.AdvanceRightsInsert;
                            FormLoadSettingsDTO.Update = rights.AdvanceRightsUpdate;
                            FormLoadSettingsDTO.Delete = rights.AdvanceRightsDelete;
                            FormLoadSettingsDTO.Print = rights.AdvanceRightsPrint;
                        }
                    }
                    #endregion

                }
                else if (RoleVsMenuDTOs.Count > 1)
                {
                    FormLoadSettingsDTO.Allow = "333333";
                }
                #endregion

                #region Get Date For Date Open & Close
                if (BIZTransactionTypeId != -1) //For Transactions Only
                {
                    List<BIZTransactionTypeDTO> BIZTransactionTypes = (await GetBizTransactionTypeNew(BIZTransactionTypeId, LoginDTO)).ToList();
                    if (BIZTransactionTypes.Count != 0)
                    {
                        DateTime Dt = await GetBasicLockDateForOpenAndClose(BIZTransactionTypes[0].BIZTransactionTypeId, LoginDTO, 1);
                        byte Status = 0; DateTime RetDt;
                        if (Dt == Convert.ToDateTime("01/01/1800"))
                        {
                            Status = 0;
                            RetDt = Dt;
                        }
                        else if (Dt == Convert.ToDateTime("01/01/1899"))
                        {
                            Status = 1;
                            RetDt = Dt;
                        }
                        else
                        {
                            Status = 2;
                            RetDt = Dt;
                        }
                        FormLoadSettingsDTO.LockOpenCloseDate = RetDt;
                        FormLoadSettingsDTO.LockOpenCloseStatus = Status;
                    }
                }
                #endregion
                //string Json = JsonConvert.SerializeObject(FormLoadSettingsDTO);
                return FormLoadSettingsDTO;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<List<RoleVsMenuDTO>> GetRoleVsMenuBasedOnMenuIdAndRoleId(
     int menuId,
     int roleId,
     LoginDTO loginDTO)
        {
            try
            {
                string sql = null!;

                if (loginDTO.DatabaseType == DBType.PostGre)
                {
                    sql = BIZTransactionTypeQB.GET_ROLEVSMENU_PG;
                }
                if (loginDTO.DatabaseType == DBType.SQL)
                {
                    sql = BIZTransactionTypeQB.GET_ROLEVSMENU;
                }

                var parameters = new
                {
                    menuid = menuId,
                    roleid = roleId
                };

                var result = await queryExecutor
                    .QueryAsync<RoleVsMenuDTO>(loginDTO, sql, parameters);

                return result.ToList();
            }
            catch
            {
                throw;
            }
        }


        public async Task<DateTime> GetBasicLockDateForOpenAndClose(int BIZTransactionId, LoginDTO LoginDTO, int CallFrom = 0)
        {
            try
            {
                DateTime VDate = Convert.ToDateTime("01/01/1800");
                string Sql = BIZTransactionQB.GET_LOCKSETTING_BASED_ON_BIZTRANSACTIONCLASS_OU_ID;
                var parameter = new { biztransactionid = BIZTransactionId, ouid = LoginDTO.WorkOUId };

                List<LockSettingDTO> LockSettings = (await queryExecutor.QueryAsync<LockSettingDTO>(LoginDTO, Sql, parameter)).ToList();

                if (LockSettings.Count == 0)
                {
                    return Convert.ToDateTime("01/01/1800");
                }

                for (int i = 0; i < LockSettings.Count; i++)
                {
                    if (LockSettings[i].LockId != -1)
                    {
                        string Sql2 = BIZTransactionQB.GET_DAYLOCK_BASED_ON_LOCK_OU_PERIOD_ID;
                        var Parameters = new
                        {
                            lockid = LockSettings[i].LockId,
                            ouid = LoginDTO.WorkOUId,
                            periodid = LoginDTO.WorkPeriodId
                        };

                        List<DayLockDTO> DayLocks = (await queryExecutor.QueryAsync<DayLockDTO>(LoginDTO, Sql2, Parameters)).ToList();

                        if (DayLocks.Count == 0)
                        {
                            if (CallFrom == 0)
                            {
                                throw new MethodNotAllowedException("Day Lock Entry Is Not Found For the LockId: " + LockSettings[i].LockId +
                                                                    ", OUId: " + LoginDTO.WorkOUId +
                                                                    ", PeriodId: " + LoginDTO.WorkPeriodId);
                            }
                            else if (CallFrom == 1)
                            {
                                return Convert.ToDateTime("01/01/1899");
                            }
                        }

                        for (int j = 0; j < DayLocks.Count; j++)
                        {
                            if (DayLocks[j].DayLockOpenDate == Convert.ToDateTime("01/01/1899") &&
                                DayLocks[j].DayLockCloseDate == Convert.ToDateTime("01/01/1899"))
                            {
                                if (CallFrom == 0)
                                {
                                    throw new MethodNotAllowedException("Open & Close Date Operation is Not Started. Please Open the Date in DayLock, For the LockId: " +
                                                                        LockSettings[i].LockId +
                                                                        ", OUId: " + LoginDTO.WorkOUId +
                                                                        ", PeriodId: " + LoginDTO.WorkPeriodId);
                                }
                                else if (CallFrom == 1)
                                {
                                    return Convert.ToDateTime("01/01/1899");
                                }
                            }
                            else if (DayLocks[j].DayLockOpenDate == Convert.ToDateTime("01/01/1899") &&
                                     DayLocks[j].DayLockCloseDate != Convert.ToDateTime("01/01/1899"))
                            {
                                if (CallFrom == 0)
                                {
                                    throw new MethodNotAllowedException("Lock Not Opened, For the LockId: " +
                                                                        LockSettings[i].LockId +
                                                                        ", OUId: " + LoginDTO.WorkOUId +
                                                                        ", PeriodId: " + LoginDTO.WorkPeriodId + ". Contact Administrator.");
                                }
                                else if (CallFrom == 1)
                                {
                                    return Convert.ToDateTime("01/01/1899");
                                }
                            }

                            VDate = DayLocks[j].DayLockOpenDate;
                        }
                    }
                }

                return VDate;
            }
            catch (Exception)
            {
                throw;
            }
        }

        //public List<RoleVsMenuDTO> GetRoleVsMenuBasedOnMenuIdAndRoleId(int MenuId, int RoleId, LoginDTO LoginDTO)
        //{ 
        //    try
        //    {
        //        string Hql = "";
        //        GenericDal = new GenericNhibernateLazyLoadingFrameDAL(LoginDTO, false, true);
        //        Hql = RoleVsMenuQueryBuilder.GET_ROLE_VS_MENU_BASED_ON_ROLEID_AND_MENUID;
        //        String[] paramNames = { "menuid", "roleid" };
        //        Object[] values = { MenuId, RoleId };
        //        Hql = CommonFunctionFrameDAL.ChangedByNamedParam(Hql, paramNames, values);
        //        IList<Unisoft.FrameworkDAL.Poco.Menu.RoleVsMenu> RoleVsMenus = GenericDal.GetGenericCriteriaLoadQuery<Unisoft.FrameworkDAL.Poco.Menu.RoleVsMenu>(Hql, LoginDTO);
        //        List<Unisoft.FrameworkDAL.DTO.Menu.RoleVsMenuDTO> RoleVsMenuDTOs = CommonFunctionFrameDAL.GenericConversionPocoListToDToList<Unisoft.FrameworkDAL.DTO.Menu.RoleVsMenuDTO>(RoleVsMenus);
        //        GenericDal.CloseConnection();
        //        return RoleVsMenuDTOs;
        //    }
        //    catch (Exception Error)
        //    { 
        //        throw Error;
        //    } 
        //}

        public async Task<FormLoadSettingsDTO> GetBackDateBasedSettings(int BIZTransactionTypeId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = "";
                object Parameter = null!;
                FormLoadSettingsDTO FormLoadSettingsDTO = new FormLoadSettingsDTO();
                DateTime CompareDt = DateTime.UtcNow;
                DateTime SaveDt;
                DateTime UpdateDt;
                DateTime DeleteDt;
                DateTime ForwardDt;
                DateTime OverAllLockDt = Convert.ToDateTime("01/Jan/1800");

                if (BIZTransactionTypeId != -1)
                {
                    List<BIZTransactionTypeDTO> BIZTransactionTypes = (await GetBizTransactionTypeNew(BIZTransactionTypeId, LoginDTO)).ToList();

                    if (BIZTransactionTypes.Count != 0)
                    {
                        var GetEntityIdParameter = new { biztransactiontypeclassid = BIZTransactionTypes[0].BIZTransactionTypeClassId };
                        string SQLGetEntityId = BIZTransactionQB.GET_ENTITYID_BASED_ON_BIZTRANSACTIONCLASS;
                        int EntityId = await queryExecutor.QuerySingleAsync<int>(LoginDTO,SQLGetEntityId, GetEntityIdParameter);
                        
                        if (BIZTransactionTypes[0].BIZTransactionTypeBackDateCheckType == 0) //LastTransaction Based
                        {
                            Sql = BIZTransactionQB.GET_MAX_DATE_BASED_ON_TABLE_NAME;
                            #region Find Hql Based on EntityId
                            if (EntityId == EntityConstant.OBJECTMMHEAD)
                            {
                                Parameter = FindMaxDateParameter("TMMHead", "DOCUMENTDATE", BIZTransactionTypeId, LoginDTO.WorkOUId);
                            }
                            else if (EntityId == EntityConstant.OBJECTPROCESSGROUP)
                            {
                                Parameter = FindMaxDateParameter( "TProcessGroup", "PROCESSGROUPDATE", BIZTransactionTypeId, LoginDTO.WorkOUId);
                            }
                            else if (EntityId == EntityConstant.OBJECTINDENT)
                            {
                                Parameter = FindMaxDateParameter( "TIndent", "INDENTDATE", BIZTransactionTypeId, LoginDTO.WorkOUId);
                            }
                            else if (EntityId == EntityConstant.OBJECTWORKPLAN)
                            {
                                Parameter = FindMaxDateParameter( "TWorkPlan", "WORKPLANDATE", BIZTransactionTypeId, LoginDTO.WorkOUId);
                            }
                            else if (EntityId == EntityConstant.OBJECTPRODUCTIONPLAN)
                            {
                                Parameter = FindMaxDateParameter( "TProductionPlan", "PRODUCTIONPLANDATE", BIZTransactionTypeId, LoginDTO.WorkOUId);
                            }
                            else if (EntityId == EntityConstant.OBJECTWARPINGPRODUCTION)
                            {
                                Parameter = FindMaxDateParameter("TWarpingProduction", "WARPINGPRODUCTIONDATE", BIZTransactionTypeId, LoginDTO.WorkOUId);
                            }
                            else if (EntityId == EntityConstant.OBJECTSIZINGPRODUCTION)
                            {
                                Parameter = FindMaxDateParameter("TSizingProduction", "SIZINGPRODUCTIONDATE", BIZTransactionTypeId, LoginDTO.WorkOUId);
                            }
                            else if (EntityId == EntityConstant.OBJECTJOBCARD)
                            {
                                Parameter = FindMaxDateParameter("JobCard", "JobCardDate", BIZTransactionTypeId, LoginDTO.WorkOUId);
                            }
                            else if (EntityId == EntityConstant.OBJECTBEAMGAITING)
                            {
                                Parameter = FindMaxDateParameter("BeamGaiting", "BeamGaitingDate", BIZTransactionTypeId, LoginDTO.WorkOUId);
                            }
                            else if (EntityId == EntityConstant.OBJECTPROCESSINGPRODUCTION)
                            {
                                Parameter = FindMaxDateParameter("ProcessingProduction", "ProcessingProductionDate", BIZTransactionTypeId, LoginDTO.WorkOUId);
                            }
                            else if (EntityId == EntityConstant.OBJECTINSPECTION)
                            {
                                Parameter = FindMaxDateParameter("Inspection", "Date", BIZTransactionTypeId, LoginDTO.WorkOUId);
                            }
                            else if (EntityId == EntityConstant.OBJECTWEAVINGPRODUCTION)
                            {
                                Parameter = FindMaxDateParameter("WeavingProduction", "Date", BIZTransactionTypeId, LoginDTO.WorkOUId);
                            }
                            else if (EntityId == EntityConstant.OBJECTVOUCHER)
                            {
                                Parameter = FindMaxDateParameter("Voucher", "VoucherDate", BIZTransactionTypeId, LoginDTO.WorkOUId);
                             }
                            else if (EntityId == -1)
                            {
                                throw new InvalidOperationException("EntityId = -1. Please check the BizTransactionClass table Entity field.");
                            }
                            else
                            {
                                throw new InvalidOperationException("EntityId not handled. Contact administrator.");
                            }
                            #endregion

                            List<LastTransactionBasedDTO> LastTransactionBasedDTOs = (await queryExecutor.QueryAsync<LastTransactionBasedDTO>(LoginDTO, Sql, Parameter)).ToList();

 
                            if (LastTransactionBasedDTOs.Count > 0)
                            {
                                if (LastTransactionBasedDTOs[0].MaxDate == Convert.ToDateTime("01/01/0001"))
                                {
                                    CompareDt = Convert.ToDateTime(DateTime.UtcNow.ToString("yyyy/MM/dd"));
                                }
                                else
                                {
                                    CompareDt = Convert.ToDateTime(LastTransactionBasedDTOs[0].MaxDate.ToString("yyyy/MM/dd"));
                                }
                            }
                        }
                        else //System Based
                        {
                            CompareDt = Convert.ToDateTime(DateTime.UtcNow.ToString("yyyy/MM/dd"));
                        }

                        SaveDt = CompareDt.AddDays((BIZTransactionTypes[0].BIZTransactionTypeBackDateEntryDay * -1));
                        UpdateDt = CompareDt.AddDays((BIZTransactionTypes[0].BIZTransactionTypeBackDateUpdateDay * -1));
                        DeleteDt = CompareDt.AddDays((BIZTransactionTypes[0].BIZTransactionTypeBackDateDeleteDay * -1));
                        ForwardDt = CompareDt.AddDays(BIZTransactionTypes[0].BIZTransactionTypeForwardEntryDay);

                        FormLoadSettingsDTO.BackDateCheckType = BIZTransactionTypes[0].BIZTransactionTypeBackDateCheckType;
                        FormLoadSettingsDTO.BackDateDeleteDay = DeleteDt;
                        FormLoadSettingsDTO.BackDateEntryDay = SaveDt;
                        FormLoadSettingsDTO.BackDateUpdateDay = UpdateDt;
                        FormLoadSettingsDTO.ForwardEntryDate = ForwardDt;
                    }
                }

                #region Find OverAllLockDate  
                string SQLLocalDate = null!;

                if (LoginDTO.DatabaseType == DBType.PostGre)
                {
                    SQLLocalDate = BIZTransactionQB.GET_OVER_ALL_LOCK_DATE_PG;
                }
                if (LoginDTO.DatabaseType == DBType.SQL)
                {
                    SQLLocalDate = BIZTransactionQB.GET_OVER_ALL_LOCK_DATE_SQL;
                }

                var parameter = new { ouid = LoginDTO.WorkOUId };
                  
                IList<OrganizationUnitDTO> OrganizationUnits = (await queryExecutor.QueryAsync<OrganizationUnitDTO>(LoginDTO, SQLLocalDate, parameter)).ToList();

                if (OrganizationUnits.Count != 0)
                {
                    OverAllLockDt = OrganizationUnits[0].OrganizationUnitOverLockDate;
                    FormLoadSettingsDTO.OverAllLockDate = OverAllLockDt;
                }
                #endregion 

                return FormLoadSettingsDTO;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<IEnumerable<BIZTransactionTypeDTO>> GetBIZTransactionType(int BIZTransactionTypeId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = LoginDTO.DatabaseType == DBType.PostGre
                    ? BIZTransactionTypeQB.PG_GET_BIZTRANSACTIONTYPE_DETAILS_SQL
                    : BIZTransactionTypeQB.GET_BIZTRANSACTIONTYPE_DETAILS_SQL;

                var Parameter = new { biztransactiontypeid = BIZTransactionTypeId };
                return await queryExecutor.QueryAsync<BIZTransactionTypeDTO>(LoginDTO, Sql, Parameter);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<IEnumerable<BIZTransactionTypeDTO>> GetBizTransactionTypeNew(int BIZTransactionTypeId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = LoginDTO.DatabaseType == DBType.PostGre
                    ? BIZTransactionQB.GET_BIZTRANSACTIONTYPE_BASED_ON_ID_PG
                    : BIZTransactionQB.GET_BIZTRANSACTIONTYPE_BASED_ON_ID_SQL;

                var Parameter = new
                {
                    biztransactiontypeid = BIZTransactionTypeId
                };
                IEnumerable<BIZTransactionTypeDTO> BIZTransactionTypes = await queryExecutor.QueryAsync<BIZTransactionTypeDTO>(LoginDTO,Sql, Parameter);
                return BIZTransactionTypes;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<IEnumerable<FormLoadSettingsDTO>> GetBizTransactionTypeClassDetailsNew(
    int bizTransactionTypeId,
    LoginDTO loginDTO)
        {
            try
            {
                string sql = null!;

                if (loginDTO.DatabaseType == DBType.PostGre)
                {
                    sql = BIZTransactionQB.GET_BIZTRANSACTION_TYPE_CLASS_SUBCLASS_PG;
                }
                if (loginDTO.DatabaseType == DBType.SQL)
                {
                    sql = BIZTransactionQB.GET_BIZTRANSACTION_TYPE_CLASS_SUBCLASS_SQL;
                }

                var parameter = new
                {
                    biztransactiontypeid = bizTransactionTypeId
                };

                IEnumerable<FormLoadSettingsDTO> result =
                    await queryExecutor.QueryAsync<FormLoadSettingsDTO>(
                        loginDTO,
                        sql,
                        parameter
                    );

                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }



        private object FindMaxDateParameter(string PocoName, string ColumnName, int BizTransactionTypeId, int OUId)
        { 
            try
            {
                return new
                {
                    poconame = PocoName,
                    columnname = ColumnName,
                    biztransactiontypeid = BizTransactionTypeId,
                    ouid = OUId
                };
            }
            catch (Exception)
            {
                throw;
            }
        }





        // ─── Service Method ────────────────────────────────────────────────────────────

        // Maps the UI-facing filter field names to the real MBIZTRANSACTIONTYPE
        // (or joined) columns, and doubles as a whitelist: any FieldName not
        // listed here is dropped before CriteriaBuilder runs, since it
        // interpolates FieldName directly into the SQL column reference
        // (not parameterized).
        private static readonly Dictionary<string, string> _rightsFilterableFields = new(StringComparer.OrdinalIgnoreCase)
        {
            ["Id"] = "BIZTRANSACTIONTYPEID",
            ["Code"] = "BIZTRANSACTIONTYPECODE",
            ["Name"] = "BIZTRANSACTIONTYPENAME",
            ["BIZTransactionTypeClassId"] = "BIZTRANSACTIONCLASSID",
            ["BIZTransactionSubClassId"] = "BIZTRANSACTIONSUBCLASSID",
            ["BIZTransactionId"] = "BIZTRANSACTIONID",
            ["PeriodId"] = "PERIODID",
            ["ModuleId"] = "MODULEID",
        };

        private static void ApplyRightsFieldNameWhitelist(CriteriaDTO? criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return;

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

                section.AttributesCriteriaList = section.AttributesCriteriaList
                    .Where(attr => attr?.FieldName != null && _rightsFilterableFields.ContainsKey(attr.FieldName))
                    .ToList();

                foreach (var attr in section.AttributesCriteriaList)
                    attr.FieldName = _rightsFilterableFields[attr.FieldName];
            }
        }

        public async Task<string> GetSelectListBIZTransactionTypeBasedOnRights(
    CriteriaDTO criteriaDTO,
    LoginDTO loginDTO)
        {
            try
            {
                // OrgUnit still resolved explicitly (rather than through the dynamic
                // criteria path) because it also feeds the MADVANCERIGHTS join and
                // the OUTYPE visibility check, both fixed business rules — not a
                // caller-optional filter.
                string orgUnit = "";

                if (criteriaDTO?.SectionCriteriaList != null)
                {
                    foreach (var section in criteriaDTO.SectionCriteriaList)
                    {
                        foreach (var attr in section.AttributesCriteriaList ?? new())
                        {
                            if (attr.FieldName?.ToLower() == "organizationunitid" || attr.FieldName?.ToLower() == "ouid")
                                orgUnit = Convert.ToString(attr.FieldValue);
                        }
                    }
                }

                if (string.IsNullOrEmpty(orgUnit))
                    orgUnit = Convert.ToString(loginDTO.WorkOUId);

                ApplyRightsFieldNameWhitelist(criteriaDTO);

                var parameters = new DynamicParameters();
                parameters.Add("OrgUnitId", orgUnit);
                parameters.Add("UserId", loginDTO.UserId);

                string baseSql = loginDTO.DatabaseType switch
                {
                    DBType.SQL => BIZTransactionTypeQB.GET_SELECTLIST_BIZTRANSACTIONTYPE_RIGHTS,
                    DBType.PostGre => BIZTransactionTypeQB.PG_SELECTLIST_BIZTRANSACTIONTYPE_RIGHTS,
                    _ => throw new NotSupportedException(
                             $"Database type '{loginDTO.DatabaseType}' is not supported.")
                };

                var (whereClause, _) = CriteriaBuilder.Build(criteriaDTO, parameters, baseSql);
                string sql = baseSql.Replace("/*CRITERIA*/", whereClause);

                var result = await queryExecutor
                    .QueryAsync<BIZTransactionTypePicklistDTO>(loginDTO, sql, parameters);

                var ordered = result.OrderBy(x => x.SortOrder).ToList();

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

        // ─── Private helper: mirrors the OrgUnit-extraction loop in the old service ───
        private static string ResolveOrgUnitId(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            if (criteriaDTO?.SectionCriteriaList != null)
            {
                foreach (var section in criteriaDTO.SectionCriteriaList)
                {
                    foreach (var attr in section.AttributesCriteriaList)
                    {
                        if (string.Equals(
                                attr.FieldName,
                                "organizationunitid",
                                StringComparison.OrdinalIgnoreCase))
                        {
                            string val = Convert.ToString(attr.FieldValue);
                            if (!string.IsNullOrEmpty(val))
                                return val;
                        }
                    }
                }
            }

            // Fall back to the logged-in user's working OU
            return Convert.ToString(loginDTO.WorkOUId);
        }

        // Maps the UI-facing filter field names to the real MBIZTRANSACTIONTYPE
        // columns, and doubles as a whitelist: any FieldName not listed here
        // is dropped before CriteriaBuilder runs, since it interpolates
        // FieldName directly into the SQL column reference (not parameterized).
        private static readonly Dictionary<string, string> _listFilterableFields = new(StringComparer.OrdinalIgnoreCase)
        {
            ["Id"] = "BIZTRANSACTIONTYPEID",
            ["Code"] = "BIZTRANSACTIONTYPECODE",
            ["Name"] = "BIZTRANSACTIONTYPENAME",
            ["BIZTransactionTypeClassId"] = "BIZTRANSACTIONCLASSID",
            ["BIZTransactionClassId"] = "BIZTRANSACTIONCLASSID",
            ["BIZTransactionSubClassId"] = "BIZTRANSACTIONSUBCLASSID",
            ["BIZTransactionId"] = "BIZTRANSACTIONID",
            ["PeriodId"] = "PERIODID",
        };

        private static void ApplyListFieldNameWhitelist(CriteriaDTO? criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return;

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

                section.AttributesCriteriaList = section.AttributesCriteriaList
                    .Where(attr => attr?.FieldName != null && _listFilterableFields.ContainsKey(attr.FieldName))
                    .ToList();

                foreach (var attr in section.AttributesCriteriaList)
                    attr.FieldName = _listFilterableFields[attr.FieldName];
            }
        }

        public async Task<string> GetBizTransactionTypeList(
     CriteriaDTO criteriaDTO,
     int organizationUnitId,
     LoginDTO loginDTO)
        {
            try
            {
                ApplyListFieldNameWhitelist(criteriaDTO);

                var parameters = new DynamicParameters();
                parameters.Add("ouid", organizationUnitId);

                string baseSql = loginDTO.DatabaseType == DBType.PostGre
                    ? BIZTransactionTypeQB.PG_GET_BIZTRANSACTIONTYPE_LIST_BY_CRITERIA
                    : BIZTransactionTypeQB.GET_BIZTRANSACTIONTYPE_LIST_BY_CRITERIA;

                var (whereClause, _) = CriteriaBuilder.Build(criteriaDTO, parameters, baseSql);
                string sql = baseSql.Replace("/*CRITERIA*/", whereClause);

                // ✅ Slim list DTO — matches SQL column aliases exactly
                var result = await queryExecutor
                    .QueryAsync<BIZTransactionTypeListDTO>(loginDTO, sql, parameters);

                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetBizTransactionTypeConfig(int BizTransactionTypeId, int StoreId, int TariffId, LoginDTO LoginDTO)
        {
            try
            {
                var settings = await FetchBizTransactionTypeSettingsAsync(BizTransactionTypeId, LoginDTO);
                if (settings == null)
                    return "No Data";
                return JsonConvert.SerializeObject(settings);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<InventorySettingsDTO?> GetBizTransactionTypeSettings(int BizTransactionTypeId, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                return await FetchBizTransactionTypeSettingsAsync(BizTransactionTypeId, LoginDTO);
            }
            catch (Exception)
            {
                throw;
            }
        }

        private async Task<InventorySettingsDTO?> FetchBizTransactionTypeSettingsAsync(int BizTransactionTypeId, LoginDTO LoginDTO)
        {
            string sql = LoginDTO.DatabaseType == DBType.PostGre
                ? BIZTransactionTypeQB.PG_GET_BIZTRANSACTIONTYPE_DETAILS_SQL
                : BIZTransactionTypeQB.GET_BIZTRANSACTIONTYPE_DETAILS_SQL;
            var parameter = new { biztransactiontypeid = BizTransactionTypeId };

            var inventorySettings = (await queryExecutor.QueryAsync<InventorySettingsDTO>(LoginDTO, sql, parameter)).ToList();

            if (inventorySettings.Count == 0)
                return null;

            var settings = inventorySettings[0];

            string dbLevelSql = LoginDTO.DatabaseType == DBType.PostGre
                ? BIZTransactionTypeQB.PG_GET_DBLEVELSETTING
                : BIZTransactionTypeQB.GET_DBLEVELSETTING;

            var dbLevelSettings = (await queryExecutor.QueryAsync<DBLevelSettingDTO>(LoginDTO, dbLevelSql, null!)).ToList();

            if (dbLevelSettings.Count != 0)
            {
                settings.IsCancelApplicable = dbLevelSettings[0].DBLevelSettingIsCancelApplicable;
                settings.IsCancelDeleteOnlyForPending = dbLevelSettings[0].DBLevelSettingIsCancelDeleteOnlyForPending;
                settings.D1Label = dbLevelSettings[0].D1Label;
                settings.D2Label = dbLevelSettings[0].D2Label;
                settings.D3Label = dbLevelSettings[0].D3Label;
                settings.D4Label = dbLevelSettings[0].D4Label;
                settings.D5Label = dbLevelSettings[0].D5Label;
            }

            if (settings.AdditionalChargeId != 0 && settings.AdditionalChargeId != -1)
            {
                var charge = await FetchChargeAsync(settings.AdditionalChargeId, LoginDTO);
                if (charge != null)
                    settings.AdditionalChargeArray = new List<ChargeDTO> { charge };
            }

            if (settings.OtherChargeId != 0 && settings.OtherChargeId != -1)
            {
                var charge = await FetchChargeAsync(settings.OtherChargeId, LoginDTO);
                if (charge != null)
                    settings.OtherChargeArray = new List<ChargeDTO> { charge };
            }

            if (settings.TotalChargeId != 0 && settings.TotalChargeId != -1)
            {
                var charge = await FetchChargeAsync(settings.TotalChargeId, LoginDTO);
                if (charge != null)
                    settings.TotalChargeArray = new List<ChargeDTO> { charge };
            }

            return settings;
        }

        private async Task<ChargeDTO?> FetchChargeAsync(int chargeId, LoginDTO LoginDTO)
        {
            var chargeDict = new Dictionary<int, ChargeDTO>();
            await queryExecutor.QueryMultiMapAsync<ChargeDTO, ChargeDetailDTO, ChargeDTO>(
                LoginDTO,
                ChargeQB.GET_CHARGE,
                (parent, child) =>
                {
                    if (!chargeDict.TryGetValue(parent.ChargeId, out var existing))
                    {
                        existing = parent;
                        existing.ChargeDetailArray = new List<ChargeDetailDTO>();
                        chargeDict[parent.ChargeId] = existing;
                    }
                    if (child != null && child.ChargeDetailId != 0)
                        existing.ChargeDetailArray.Add(child);
                    return existing;
                },
                new { ChargeId = chargeId },
                splitOn: "ChargeDetailId");
            return chargeDict.Values.FirstOrDefault();
        }

        // ── SaveBIZTransactionType ───────────────────────────────────────────────────
        //
        // Ported from GB4 BIZTransactionTypeBLL.SaveBIZTransactionTypeNew. SQL Server only —
        // GB4's own duplicate-check/insert/update queries here have no PostGre variant either.

        public async Task<int> CheckBizTransactionTypeExists(int bizTransactionId, int ouId, int periodId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            return await queryExecutor.ExecuteScalarAsync<int>(
                loginDTO, BIZTransactionTypeQB.CHECK_BIZTRANSACTIONTYPE_EXISTS,
                new { BizTransactionId = bizTransactionId, OUId = ouId, PeriodId = periodId }, transaction);
        }

        public async Task<int> SaveBIZTransactionType(BIZTransactionTypeDTO dto, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            await queryExecutor.ExecuteAsync(loginDTO, BIZTransactionTypeQB.INSERT_BIZTRANSACTIONTYPE, dto, transaction);
            return dto.BIZTransactionTypeId;
        }

        public async Task<int> UpdateBIZTransactionType(BIZTransactionTypeDTO dto, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            await queryExecutor.ExecuteAsync(loginDTO, BIZTransactionTypeQB.UPDATE_BIZTRANSACTIONTYPE, dto, transaction);
            return dto.BIZTransactionTypeId;
        }
    }
}