using FrameworkDAL.DTO.DrillDown;
using FrameworkDAL.DTO.Menu;
using FrameworkDAL.DTO.Portlet;
using FrameworkDAL.DTO.ReportViewer;
using FrameworkDAL.DTO.ReportViewFields;
using FrameworkDAL.DTO.WebService;
using FrameworkDAL.Query.Menu;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5Exception;
using GB5Shared.Query.FrameWork.CriteriaConfig;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Framework;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Omu.ValueInjecter;
using System.Collections;


namespace FrameworkDAL.CustomCode.Menu
{
    public class MenuDAL : IMenuDAL
    {
        private static readonly JsonSerializerSettings _gb5JsonSettings = new JsonSerializerSettings
        {
            DateTimeZoneHandling = DateTimeZoneHandling.Utc,
            DateFormatHandling   = DateFormatHandling.IsoDateFormat
        };

        private static readonly JsonSerializerSettings _gb4JsonSettings = new JsonSerializerSettings
        {
            DateFormatHandling = DateFormatHandling.MicrosoftDateFormat
        };

        private readonly IQueryExecutor _queryExecutor;
        public MenuDAL(IQueryExecutor IQueryExecutor)
        {
            _queryExecutor = IQueryExecutor;
        }
        public async Task<IEnumerable<LoadMenuListDTO>> GetMenuForUserModule(int UserId, int ModuleId, LoginDTO LoginDTO)
        {
            try
            { 
                string Sql = "";
                string Replace = ""; 
                if (LoginDTO.DatabaseType == 0)//Sql
                {
                    Sql = MenuQB.GET_MENU_TREE_FOR_USER_MODULE;
                }
                if (LoginDTO.DatabaseType == 1)//Oracle
                {
                   //Sql = MenuQB.GET_MENU_TREE_FOR_USER_MODULE_ORACLE;
                    Replace = " from dual ";
                }
                if (LoginDTO.DatabaseType == 2)//Postgre
                {
                    Sql = MenuQB.GET_MENU_TREE_FOR_USER_MODULE_POSTGRE;
                }
                if (LoginDTO.DatabaseType == 3)//Mysql
                {
                   // Sql = MenuQB.GET_MENU_TREE_FOR_USER_MODULE;
                }
                Sql = Sql.Replace("{replace}", Replace);

                var parameters = new
                {
                    userid = UserId,
                    moduleid = ModuleId,
                    clientid = LoginDTO.ClientId,
                    feversion = LoginDTO.FEVersion
                };
                //String[] paramNames = { "replace" };
                //Object[] values = { Replace };
                //Sql = CommonFunctionFrameDAL.ChangedByNamedParam(Sql, paramNames, values);  
                //string[,] CriteriaWithQueryField = new string[,]
                //{
                //       {"userid","int",UserId.ToString()},
                //       {"moduleid","int",ModuleId.ToString()},
                //       {"clientid","int",LoginDTO.ClientId.ToString()},
                //       {"feversion","decimal",LoginDTO.FEVersion.ToString()} 

                //};

                IEnumerable<LoadMenuListDTO> LoadMenuListDTOs = await _queryExecutor.QueryAsync<LoadMenuListDTO>(
                    LoginDTO,
                    Sql,
                    new { userid = UserId, moduleid = ModuleId, clientid = LoginDTO.ClientId, feversion = LoginDTO.FEVersion }
                    ,null!,true
                );

                //List<LoadMenuListDTO> LoadMenuListDTOs = (List<LoadMenuListDTO>)GenericFrameDAL.ExceuteSelectQueryParameter<LoadMenuListDTO>(Sql, CriteriaWithQueryField, LoginDTO);
                //for (int pp = 0; pp < LoadMenuListDTOs.Count; pp++)
                //{
                //    if (LoadMenuListDTOs[pp].MenuWebFormObjectModelIds != "-1")
                //    {
                //        string[] ObjectModelIds = LoadMenuListDTOs[pp].MenuWebFormObjectModelIds.Split(',');
                //        for (int i = 0; i < ObjectModelIds.Length; i++)
                //        {
                //            if (Convert.ToString(ObjectModelIds[i]) != "")
                //            {
                //                Unisoft.FrameworkDAL.Poco.File.File ObjectModel = GenericFrameDAL.Get<Unisoft.FrameworkDAL.Poco.File.File>(Convert.ToInt32(ObjectModelIds[i]), LoginDTO);
                //                LoadMenuListDTOs[pp].MenuWebFormObjectModelNames = LoadMenuListDTOs[pp].MenuWebFormObjectModelNames + "," + ObjectModel.Name;
                //            }
                //        }
                //        if (LoadMenuListDTOs[pp].MenuWebFormObjectModelNames.Length > 1)
                //        {
                //            LoadMenuListDTOs[pp].MenuWebFormObjectModelNames = LoadMenuListDTOs[pp].MenuWebFormObjectModelNames.Substring(1);
                //        }
                //    }
                //    else
                //    {
                //        LoadMenuListDTOs[pp].MenuWebFormObjectModelNames = "NONE";
                //    } 
                //    if (LoadMenuListDTOs[pp].DefaultWebFormObjectModelIds != "-1")
                //    {
                //        string[] ObjectModelIds = LoadMenuListDTOs[pp].DefaultWebFormObjectModelIds.Split(',');
                //        for (int i = 0; i < ObjectModelIds.Length; i++)
                //        {
                //            if (Convert.ToString(ObjectModelIds[i]) != "")
                //            {
                //                Unisoft.FrameworkDAL.Poco.File.File ObjectModel = GenericFrameDAL.Get<Unisoft.FrameworkDAL.Poco.File.File>(Convert.ToInt32(ObjectModelIds[i]), LoginDTO);
                //                LoadMenuListDTOs[pp].DefaultWebFormObjectModelNames = LoadMenuListDTOs[pp].DefaultWebFormObjectModelNames + "," + ObjectModel.Name;
                //            }
                //        }
                //        if (LoadMenuListDTOs[pp].DefaultWebFormObjectModelNames.Length > 1)
                //        {
                //            LoadMenuListDTOs[pp].DefaultWebFormObjectModelNames = LoadMenuListDTOs[pp].DefaultWebFormObjectModelNames.Substring(1);
                //        }
                //    }
                //    else
                //    {
                //        LoadMenuListDTOs[pp].DefaultWebFormObjectModelNames = "NONE";
                //    } 
                //}
                return LoadMenuListDTOs;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetMenu(int MenuId, LoginDTO LoginDTO)
        {
            try
            { 
                string SQL = MenuQB.GET_MENU;
                var MenuDTO =  await _queryExecutor.QuerySingleAsync<MenuDTO>(LoginDTO, SQL, new { menuid = MenuId });
                string Json = JsonConvert.SerializeObject(MenuDTO);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Stored MWEBSERVICE templates use the literal pattern "http://{BaseURI}/..." — {BaseURI} must
        // resolve to a scheme-less host[:port], since the scheme is already in the template text.
        // LoginDTO.BaseUri is built from Request.Scheme + Request.Host and DOES include a scheme,
        // so it can't be substituted in as-is or the result doubles up ("http://http://...").
        private static string GetBaseUriAuthority(string baseUri)
        {
            return Uri.TryCreate(baseUri, UriKind.Absolute, out var uri) ? uri.Authority : baseUri;
        }

        public async Task<string> GetReportCriteriaForMenu(int UserId, int MenuId, int IsFromScreen, LoginDTO LoginDTO)
        {
            try
            {
                int ReportId;
                int WebServiceId;
                int ReportViewId;
                string Sql;
                string Json;
                Sql = "";
                Json = "";
                Sql = MenuQB.GET_MENU_FOR_REPORT_CONSIOLIDATED;
                Sql = Sql.Replace(":baseuri", GetBaseUriAuthority(LoginDTO.BaseUri));
                Sql = Sql.Replace(":partybranchid", LoginDTO.WorkPartyBranchId.ToString());
                var Parameters = new
                {
                    userid = UserId, menuid = MenuId 
                }; 
                List<ReportMenuLoadingFlatQueryNewDTO> ReportMenuLoadingFlatQueryNewDTOs = new List<ReportMenuLoadingFlatQueryNewDTO>();
                List<ReportMenuLoadingFlatQueryPostGreyDTO> ReportMenuLoadingFlatQueryPostGreyDTOs = new List<ReportMenuLoadingFlatQueryPostGreyDTO>();
                List<MenuListWithReportCriteriaDTO> MenuDetailDTOs = new List<MenuListWithReportCriteriaDTO>();
                List<MenuListWithReportCriteriaPostGreDTO> MenuDetailPostGreDTOs = new List<MenuListWithReportCriteriaPostGreDTO>();
               
                if (IsFromScreen == 1)  //For redmine Feature 16764(If report filter called from screen,then no need to check allow condition in rolevsmenu)
                {
                    Sql = Sql.Replace("and rolevsmenu.Allow like '%0%'", "");
                }

                await foreach (var item in _queryExecutor.StreamAsync<ReportMenuLoadingFlatQueryNewDTO>(LoginDTO, Sql, Parameters))
                {
                    ReportMenuLoadingFlatQueryNewDTOs.Add(item);
                }


                string json = JsonConvert.SerializeObject(ReportMenuLoadingFlatQueryNewDTOs);
                if (ReportMenuLoadingFlatQueryNewDTOs.Count != 0)
                {
                    for (int i = 0; i < ReportMenuLoadingFlatQueryNewDTOs.Count; i++)
                    {
                        string TempUri = "";
                        string inputvalue = ReportMenuLoadingFlatQueryNewDTOs[i].URIParameterValue;
                        TempUri = ReportMenuLoadingFlatQueryNewDTOs[i].WebServiceUriTemplate;
                        string[] TempSplit = inputvalue.Split(',');
                        for (int ii = 0; ii < TempSplit.Length; ii++)
                        {
                            string[] TempSplitNextLevel = TempSplit[ii].Split('=');
                            if (TempSplitNextLevel.Length == 2)
                            {
                                TempUri = TempUri.Replace(TempSplitNextLevel[0], TempSplitNextLevel[1]);
                                TempUri = TempUri.Replace("##UserCode##", LoginDTO.UserCode);
                            }
                        }
                         
                        if (TempUri != "")
                        {
                            ReportMenuLoadingFlatQueryNewDTOs[i].WebServiceUriTemplate = TempUri;
                        }
                    }
                }
                
                List<ReportMenuLoadingFlatQueryNewDTO> TempReportMenuLoadingFlatQueryNewDTOs = ReportMenuLoadingFlatQueryNewDTOs.GroupBy(Current => Current.MenuId).Select(EmployeeRecords => EmployeeRecords.First()).ToList();
                MenuDetailDTOs = TempReportMenuLoadingFlatQueryNewDTOs.Select(x => new MenuListWithReportCriteriaDTO().InjectFrom(x)).Cast<MenuListWithReportCriteriaDTO>().ToList();
                 
                List<MenuCriteriaDTO> MenuCriteriaDTOs = new List<MenuCriteriaDTO>();

                TempReportMenuLoadingFlatQueryNewDTOs = ReportMenuLoadingFlatQueryNewDTOs
                                        .GroupBy(Current => new { Current.ReportCriteriaReportId, Current.WebServiceId, Current.WebServiceCriteriaSlNo })
                                        .Select(EmployeeRecords => EmployeeRecords.First()).ToList();

                MenuCriteriaDTOs = TempReportMenuLoadingFlatQueryNewDTOs.Select(x => new MenuCriteriaDTO().InjectFrom(x)).Cast<MenuCriteriaDTO>().ToList();

                #region Resolve Variable Fields in Criteria
                {
                    DateTime vToday    = DateTime.UtcNow;
                    DateTime vYesterday = vToday.AddDays(-1);

                    var varMap = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
                    {
                        { "{WorkPartyBranchID}", LoginDTO.WorkPartyBranchId.ToString() },
                        { "{WorkOUID}",          LoginDTO.WorkOUId.ToString() },
                        { "{WorkStoreId}",       LoginDTO.WorkStoreId.ToString() },
                        { "{WorkPeriodId}",      LoginDTO.WorkPeriodId.ToString() },
                        { "{TodayFrom}",         SqlDate(vToday.Date) },
                        { "{TodayTo}",           vToday.Date.ToString("yyyy-MM-dd") + " 23:59:59.000" },
                        { "{TodayTimeStamp}",    SqlDate(vToday) },
                        { "{YesterdayFrom}",     SqlDate(vYesterday.Date) },
                        { "{YesterdayTo}",       vYesterday.Date.ToString("yyyy-MM-dd") + " 23:59:59.000" },
                        { "{YesterdayTimeStamp}", SqlDate(vYesterday) },
                        { "{WeekFrom}",          SqlDate(vToday.AddDays(-7).Date) },
                        { "{MonthFrom}",         SqlDate(vToday.AddMonths(-1).Date) },
                        { "{YearFrom}",          SqlDate(vToday.AddYears(-1).Date) },
                        { "{CurrentMonthFrom}",  SqlDate(new DateTime(vToday.Year, vToday.Month, 1)) },
                        { "{CurrentYearFrom}",   SqlDate(new DateTime(vToday.Year, 1, 1)) },
                        { "{CurrentYearTo}",     new DateTime(vToday.Year, 12, 31).ToString("yyyy-MM-dd") + " 23:59:59.000" },
                    };

                    foreach (var criteria in MenuCriteriaDTOs)
                    {
                        if (!string.IsNullOrEmpty(criteria.CriteriaAttributeVariableField)
                            && criteria.CriteriaAttributeVariableField != "NONE")
                        {
                            foreach (var kv in varMap)
                                criteria.CriteriaAttributeVariableField =
                                    criteria.CriteriaAttributeVariableField.Replace(kv.Key, kv.Value);
                        }
                    }
                }
                #endregion

                #region  Report View Vs Fields
                if (ReportMenuLoadingFlatQueryNewDTOs.Count != 0)
                {
                    ReportViewId = ReportMenuLoadingFlatQueryNewDTOs[0].DefaultReportViewId;
                }
                else
                {
                    ReportViewId = -1;
                }
                List<ReportViewFieldsLoadingDTO> ReportViewFieldsLoadingDTOs = await GetReportViewFieldsForMenu(UserId, MenuId, IsFromScreen, ReportViewId, LoginDTO);
                #endregion

                #region  Report Vs Format

                List<MenuReportVsFormatsDTO> MenuReportVsFormatsDTOs = new List<MenuReportVsFormatsDTO>(); 
                TempReportMenuLoadingFlatQueryNewDTOs = ReportMenuLoadingFlatQueryNewDTOs
                                       .GroupBy(Current => new { Current.ReportvsFormatReportId, Current.ReportFormatId })
                                       .Select(EmployeeRecords => EmployeeRecords.First()).ToList();

                MenuReportVsFormatsDTOs = TempReportMenuLoadingFlatQueryNewDTOs.Select(x => new MenuReportVsFormatsDTO().InjectFrom(x)).Cast<MenuReportVsFormatsDTO>().ToList();
                #endregion

                #region Report Vs Views 
                List<MenuReportVsViewsDTO> MenuReportVsViewsDTOs = new List<MenuReportVsViewsDTO>();

                TempReportMenuLoadingFlatQueryNewDTOs = ReportMenuLoadingFlatQueryNewDTOs
                                     .Where(
                                      Current => Current.ReportViewType != 100) // Exclude Jasper print views (not used in GB5)
                                     .GroupBy(Current => new { Current.ReportViewReportId, Current.ReportViewId })
                                     .Select(EmployeeRecords => EmployeeRecords.First()).ToList();


                MenuReportVsViewsDTOs = TempReportMenuLoadingFlatQueryNewDTOs.Select(x => new MenuReportVsViewsDTO().InjectFrom(x)).Cast<MenuReportVsViewsDTO>().ToList();

                #region PagePortlet 
                //if (MenuReportVsViewsDTOs.Count != 0)
                //{
                //    List<PortletListWithReportCriteriaDTO> PortletListWithReportCriteriaDTOs = new List<PortletListWithReportCriteriaDTO>();
                //    for (int kk = 0; kk < MenuReportVsViewsDTOs.Count; kk++)
                //    {
                //        if (MenuReportVsViewsDTOs[kk].ReportViewType == 4)
                //        {
                //            if (MenuReportVsViewsDTOs[kk].PageId != -1)
                //            {
                //                IPortletBLL PagePortlet = new PortletBLL();
                //                string PortletListPage = PagePortlet.GetPortletListWithReportCriteria(UserId, MenuReportVsViewsDTOs[kk].PageId, LoginDTO);
                //                PortletListWithReportCriteriaDTOs = CommonFunctionDAL.JsonDeserializerString<List<PortletListWithReportCriteriaDTO>>(PortletListPage);
                //                MenuReportVsViewsDTOs[kk].PagePortletArray = PortletListWithReportCriteriaDTOs;
                //            }
                //        }
                //    }
                //}
                #endregion 

                #endregion
                #region Criteriaconfig
                string CriteraOutPut = await GetCriteriaConfigForMenu(UserId, MenuId, LoginDTO);
                List<MenuConfigDTO> MenuConfigDTOs = JsonConvert.DeserializeObject<List<MenuConfigDTO>>(CriteraOutPut)!;
                #endregion

                if (MenuCriteriaDTOs.Count != 0)
                {
                    ReportId = MenuCriteriaDTOs[0].MenuReportId;
                    WebServiceId = MenuCriteriaDTOs[0].WebServiceId;
                }
                else
                {
                    ReportId = -1;
                    WebServiceId = -1;
                }

                #region WebService
                string WebServiceSettingOutPut = await GetWebServiceSettingForMenu(UserId, WebServiceId, MenuId, ReportId, LoginDTO);
                List<WebServiceSettingDTO> WebServiceSettingDTOs = JsonConvert.DeserializeObject<List<WebServiceSettingDTO>>(WebServiceSettingOutPut)!;//WebService Setting
                #endregion
                #region DrillDown

                Sql = MenuQB.GET_MENU_FOR_REPORT_CONSIOLIDATED_DRILLDOWN_ARRAY; //Correction need to be done for screen purpose
                var DrillDownParamter = new
                {
                    userid = UserId,
                    menuid = MenuId,
                    baseuri = GetBaseUriAuthority(LoginDTO.BaseUri),
                    partybranchid = LoginDTO.WorkPartyBranchId
                };

                IList<DrillDownMenuDTO> DrillDownMenuDTOs =new List<DrillDownMenuDTO>();

                DrillDownMenuDTOs = await _queryExecutor.StreamAsync<DrillDownMenuDTO> (LoginDTO, Sql, DrillDownParamter).ToListAsync();

                #endregion 
                var validViewIds = new HashSet<int>(MenuReportVsViewsDTOs.Select(v => v.ReportViewId));

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

                    #region Report Criteria

                    MenuDetailDTOs[i].ReportCriteriaArray = MenuCriteriaDTOs.FindAll(
                                                            delegate (MenuCriteriaDTO DTO)
                                                            {
                                                                return DTO.MenuId == MenuDetailDTOs[i].MenuId;

                                                            }
                                                            );

                    if (!string.IsNullOrWhiteSpace(LoginDTO.TempBaseURI))
                    {
                        foreach (var criteria in MenuDetailDTOs[i].ReportCriteriaArray)
                        {
                            if (!string.IsNullOrWhiteSpace(criteria.CriteriaAttributePicklistURI)
                                && criteria.CriteriaAttributePicklistURI.StartsWith("http", StringComparison.OrdinalIgnoreCase))
                            {
                                try
                                {
                                    var originalUri = new Uri(criteria.CriteriaAttributePicklistURI);
                                    var baseUri = new Uri(LoginDTO.TempBaseURI.TrimEnd('/'));
                                    criteria.CriteriaAttributePicklistURI =
                                        baseUri.GetLeftPart(UriPartial.Authority) + originalUri.PathAndQuery;
                                }
                                catch { }
                            }
                        }
                    }

                    #endregion

                    #region Report Vs Fields

                    MenuDetailDTOs[i].ReportVsFieldsArray = ReportViewFieldsLoadingDTOs.FindAll(
                                                          delegate (ReportViewFieldsLoadingDTO DTO)
                                                          {
                                                              return DTO.ReportViewId == MenuDetailDTOs[i].DefaultReportViewId;
                                                                  //&& validViewIds.Contains(DTO.ReportViewId);
                                                          }
                                                          );

                    //if (validViewIds.Count > 0 && !validViewIds.Contains(MenuDetailDTOs[i].DefaultReportViewId))
                    //    MenuDetailDTOs[i].DefaultReportViewId = MenuReportVsViewsDTOs.First(v => v.MenuReportId == MenuDetailDTOs[i].MenuReportId).ReportViewId;

                    #endregion

                    #region CriteriaConfig
                    List<MenuConfigDTO> TempMenuConfigDTOs = new List<MenuConfigDTO>();
                    TempMenuConfigDTOs = MenuConfigDTOs.FindAll(
                                                        delegate (MenuConfigDTO DTO)
                                                        {
                                                            return DTO.MenuId == MenuDetailDTOs[i].MenuId;

                                                        }
                                                        );

                    MenuDetailDTOs[i].CriteriaConfigArray = TempMenuConfigDTOs;

                    if (TempMenuConfigDTOs.Count > 0)
                    {
                        MenuDetailDTOs[i].WebServiceUriTemplate = ChangedUri(MenuDetailDTOs[i].WebServiceUriTemplate, TempMenuConfigDTOs[0].CriteriaConfigRecordsPerPage);

                    }
                    #endregion

                    #region Report Formats
                    MenuDetailDTOs[i].ReportVsFormatsArray = MenuReportVsFormatsDTOs.FindAll(
                                                        delegate (MenuReportVsFormatsDTO DTO)
                                                        {
                                                            return DTO.MenuReportId == MenuDetailDTOs[i].MenuReportId;

                                                        }
                                                        );
                    #endregion

                    #region WebService Setting
                    MenuDetailDTOs[i].WebServiceSettingArray = WebServiceSettingDTOs.FindAll(
                                                        delegate (WebServiceSettingDTO DTO)
                                                        {
                                                            return DTO.MenuId == MenuDetailDTOs[i].MenuId;

                                                        }
                                                        );
                    #endregion

                    #region Report Vs View Array

                    MenuDetailDTOs[i].ReportVsViewsArray = MenuReportVsViewsDTOs.FindAll(
                                                       delegate (MenuReportVsViewsDTO DTO)
                                                       {
                                                           return DTO.MenuReportId == MenuDetailDTOs[i].MenuReportId;

                                                       }
                                                       );
                    #endregion

                    MenuDetailDTOs[i].DrillDownArray = DrillDownMenuDTOs;   

                    #region WebService GB5 Uri Template
                    if (!string.IsNullOrWhiteSpace(MenuDetailDTOs[i].SecondURITemplate))
                    {
                        MenuDetailDTOs[i].WebServiceUriTemplate =
                            MenuDetailDTOs[i].SecondURITemplate;
                        MenuDetailDTOs[i].WebServiceUriTemplateFrom = "GB5";
                    }
                    else
                    {
                        MenuDetailDTOs[i].WebServiceUriTemplateFrom = "GB4";
                        if (!string.IsNullOrWhiteSpace(LoginDTO.TempBaseURI)
                            && !string.IsNullOrWhiteSpace(MenuDetailDTOs[i].WebServiceUriTemplate))
                        {
                            try
                            {
                                var originalUri = new Uri(MenuDetailDTOs[i].WebServiceUriTemplate);
                                var baseUri = new Uri(LoginDTO.TempBaseURI.TrimEnd('/'));
                                var replaced = baseUri.GetLeftPart(UriPartial.Authority)
                                               + originalUri.PathAndQuery;
                                MenuDetailDTOs[i].WebServiceUriTemplate = replaced;
                            }
                            catch { }
                        }
                    }

                    if (MenuDetailDTOs[i].WebServiceUriTemplateFrom == "GB4")
                    {
                        foreach (var config in MenuDetailDTOs[i].CriteriaConfigArray)
                        foreach (var section in config.ConfigSection)
                        foreach (var attr in section.ConfigAttribute)
                        {
                            if (attr.CriteriaAttributeType == 4)
                            {
                                attr.CriteriaConfigAttributeFieldValue =
                                    ToEpochString(attr.CriteriaConfigAttributeFieldValue);
                                attr.CriteriaConfigAttributeFieldValueIn =
                                    ToEpochString(attr.CriteriaConfigAttributeFieldValueIn);
                            }
                        }
                    }
                    else
                    {
                        // CRITERIAFIELDVALUE is stored as epoch seconds (GB4 convention) even for
                        // GB5-sourced criteria, while WebServiceSettingDetailCriteriaFieldValue for
                        // the same attribute is a plain date string. Convert here so first-load
                        // Criteria FieldValue matches the format GB5 web services expect.
                        foreach (var config in MenuDetailDTOs[i].CriteriaConfigArray)
                        foreach (var section in config.ConfigSection)
                        foreach (var attr in section.ConfigAttribute)
                        {
                            if (attr.CriteriaAttributeType == 4)
                            {
                                attr.CriteriaConfigAttributeFieldValue =
                                    FromEpochString(attr.CriteriaConfigAttributeFieldValue);
                                attr.CriteriaConfigAttributeFieldValueIn =
                                    FromEpochString(attr.CriteriaConfigAttributeFieldValueIn);
                            }
                        }
                    }
                    #endregion
                }
                var sb = new System.Text.StringBuilder("[");
                for (int i = 0; i < MenuDetailDTOs.Count; i++)
                {
                    if (i > 0) sb.Append(',');
                    var settings = MenuDetailDTOs[i].WebServiceUriTemplateFrom == "GB5"
                        ? _gb5JsonSettings
                        : _gb4JsonSettings;
                    sb.Append(JsonConvert.SerializeObject(MenuDetailDTOs[i], settings));
                }
                sb.Append(']');
                Json = sb.ToString();
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<List<ReportViewFieldsLoadingDTO>> GetReportViewFieldsForMenu(int UserId, int MenuId, int IsFromScreen, int ReportViewId, LoginDTO LoginDTO)
        {

            string Sql = ""; 
            Sql = MenuQB.GET_REPORTVIEWFIELDS_FOR_MENU_AND_USER_SQL;
            if (IsFromScreen == 1)  //For redmine Feature 16764
            {
                 Sql = Sql.Replace("and rolevsmenu.Allow like '%0%'", "");
            }
            var Parameter = new
            {
                 userid = UserId,
                 menuid = MenuId
            };
             
            List<ReportViewFieldsLoadingDTO> ReportViewFieldsLoadingDTOs = await _queryExecutor
            .StreamAsync<ReportViewFieldsLoadingDTO>(LoginDTO, Sql, Parameter)
            .ToListAsync();
            return ReportViewFieldsLoadingDTOs;
        }

        public async Task<string> GetCriteriaConfigForMenu(int UserId, int MenuId, LoginDTO LoginDTO)
        { 
            try
            {
                string Sql = "";
                int WorkPartyBranchId = LoginDTO.WorkPartyBranchId;
                int WorkOUId = LoginDTO.WorkOUId;
                int WorkStoreId = LoginDTO.WorkStoreId;
                int WorkPeriodId = LoginDTO.WorkPeriodId;
                Sql = MenuQB.GET_CRITERIA_CONFIG_FOR_MENU_AND_USER;

                List<MenuConfigQueryDTO> MenuConfigQueryDTOs = new List<MenuConfigQueryDTO>();

                var Parameter = new {
                    userid = UserId,
                    menuid = MenuId,
                    workpartybranchid = WorkPartyBranchId,
                    workouid = WorkOUId,
                    workstoreid = WorkStoreId,
                    workperiodid = WorkPeriodId,
                    roleid = LoginDTO.RoleId
                };

                MenuConfigQueryDTOs = await _queryExecutor.StreamAsync<MenuConfigQueryDTO>(LoginDTO, Sql, Parameter).ToListAsync();
                
                object TempFilterObjectList = await MultiLevelDTO<MenuConfigQueryDTO>(MenuConfigQueryDTOs, typeof(MenuConfigDTO), typeof(MenuConfigSectionDTO), "ConfigSection", typeof(List<MenuConfigSectionDTO>), "CriteriaConfigId");
                string Json = JsonConvert.SerializeObject(TempFilterObjectList);
                List<MenuConfigDTO> MenuConfigDTOs = JsonConvert.DeserializeObject<List<MenuConfigDTO>>(Json)!;

                object TempFilterObjectList1 = await MultiLevelDTO<MenuConfigQueryDTO>(MenuConfigQueryDTOs, typeof(MenuConfigSectionDTO), typeof(MenuConfigAttributeDTO), "ConfigAttribute", typeof(List<MenuConfigAttributeDTO>), "CriteriaConfigSectionId");
                string Json1 = JsonConvert.SerializeObject(TempFilterObjectList1);
                List<MenuConfigSectionDTO> MenuConfigSectionDTOs = JsonConvert.DeserializeObject<List<MenuConfigSectionDTO>>(Json1)!;

                for (int i = 0; i < MenuConfigDTOs.Count; i++)
                {
                    List<MenuConfigSectionDTO> TempMenuConfigSectionDTOs = MenuConfigSectionDTOs.FindAll(
                       delegate (MenuConfigSectionDTO DTO)
                       {
                           return DTO.CriteriaConfigId == MenuConfigDTOs[i].CriteriaConfigId;

                       }
                       );
                    MenuConfigDTOs[i].ConfigSection = TempMenuConfigSectionDTOs;
                }
                string Final = JsonConvert.SerializeObject(MenuConfigDTOs);
                return Final;
            }
            catch (Exception)
            {
                throw;
            } 
        }

        public async Task<object> MultiLevelDTO<T>(List<T> Data, Type HeaderObjectType, Type TailObjectType, string TailObjectName, Type TailObjectListType, string FilterField = "", IEnumerable SecondDataList = null!, string SecondTailObjectName = null!, Type SecondTailObjectListType = null, bool MergeRequired = false, string SecondLinkFilterField = "")
        {
            try
            {
                IList<object> ListChildResult = new List<object>();
                IList<object> ListSecondChildResult = new List<object>();
                object ParentObject = Activator.CreateInstance(HeaderObjectType)!;

                if (string.IsNullOrEmpty(FilterField))
                {
                    ParentObject = ParentObject.InjectFrom(Data[0]);

                    foreach (var item in Data)
                    {
                        object TempChild = Activator.CreateInstance(TailObjectType)!;
                        TempChild = TempChild.InjectFrom(item);
                        ListChildResult.Add(TempChild);
                    } 
                    var targetProp = ParentObject.GetType().GetProperty(TailObjectName);

                    if (targetProp == null)
                        throw new NotFoundException("Attribute Name Not Found");

                    object FinalResultTail = await ChangeObjectToExpectedObject(ListChildResult, TailObjectListType.FullName!);
                    targetProp.SetValue(ParentObject, FinalResultTail);
                }
                else if (SecondDataList != null)
                {
                    string SecondFilterField = !string.IsNullOrEmpty(SecondLinkFilterField) ? SecondLinkFilterField : FilterField;
                    IList<object> ListParentResult = new List<object>();
                     
                    List<T> DifferentItems = Data
                        .GroupBy(p => p?.GetType().GetProperty(FilterField)?.GetValue(p, null))
                        .Select(g => g.First())
                        .ToList();


                    if (!MergeRequired)
                    {
                        foreach (var diffItem in DifferentItems)
                        {
                            ListChildResult = new List<object>();
                            var tempData = Data.Where(d =>
                                        d?.GetType().GetProperty(FilterField)?.GetValue(d)?.ToString() ==
                                        diffItem?.GetType().GetProperty(FilterField)?.GetValue(diffItem)?.ToString()
                            );

                            ParentObject = Activator.CreateInstance(HeaderObjectType)!;
                            bool isFirst = true;

                            foreach (var item in tempData)
                            {
                                if (isFirst)
                                {
                                    ParentObject = ParentObject.InjectFrom(item);
                                    isFirst = false;
                                }
                                object TempChild = Activator.CreateInstance(TailObjectType)!;
                                TempChild = TempChild.InjectFrom(item);
                                ListChildResult.Add(TempChild);
                            }

                            var targetPropParent = ParentObject.GetType().GetProperty(TailObjectName);
                            if (targetPropParent == null)
                                throw new NotFoundException("Attribute Name Not Found");

                            object FinalResultTail = await ChangeObjectToExpectedObject(ListChildResult, TailObjectListType.FullName!);
                            targetPropParent.SetValue(ParentObject, FinalResultTail);
                            ListParentResult.Add(ParentObject);
                        }
                        ParentObject = ListParentResult;
                    }
                    else
                    {
                        foreach (var dataItem in Data)
                        {
                            object parent = dataItem!;
                            ListSecondChildResult = new List<object>();

                            foreach (var o in SecondDataList)
                            {
                                var dataItemValue = dataItem?.GetType().GetProperty(FilterField)?.GetValue(dataItem)?.ToString();
                                var oValue = o?.GetType().GetProperty(SecondFilterField)?.GetValue(o)?.ToString();

                                if (dataItemValue == oValue)
                                {
                                    ListSecondChildResult.Add(o);
                                }
                            }

                            var secondTargetProp = parent?.GetType().GetProperty(SecondTailObjectName);

                            if (secondTargetProp == null)
                                throw new NotFoundException("Seocnd Attribute Name Not Found");

                            object secondFinalResultTail = await ChangeObjectToExpectedObject(ListSecondChildResult, SecondTailObjectListType.FullName!);
                            secondTargetProp.SetValue(parent, secondFinalResultTail);
                            ListParentResult.Add(parent!);
                        }
                        ParentObject = ListParentResult;
                    }
                }
                else if (!string.IsNullOrEmpty(FilterField))
                {
                    IList<object> ListParentResult = new List<object>();

                    List<T> DifferentItems = Data
                          .GroupBy(p => p?.GetType().GetProperty(FilterField)?.GetValue(p))
                          .Select(g => g.First())
                          .ToList();

                    foreach (var diffItem in DifferentItems)
                    {
                        ListChildResult = new List<object>();

                        var diffItemValue = diffItem?.GetType().GetProperty(FilterField)?.GetValue(diffItem)?.ToString();

                        var tempData = Data.Where(d =>
                            d?.GetType().GetProperty(FilterField)?.GetValue(d)?.ToString() == diffItemValue
                        );
                        ParentObject = Activator.CreateInstance(HeaderObjectType)!;
                        bool isFirst = true;

                        foreach (var item in tempData)
                        {
                            if (isFirst)
                            {
                                ParentObject = ParentObject.InjectFrom(item);
                                isFirst = false;
                            }
                            object TempChild = Activator.CreateInstance(TailObjectType)!;
                            TempChild = TempChild.InjectFrom(item);
                            ListChildResult.Add(TempChild);
                        }

                        var targetPropParent = ParentObject?.GetType().GetProperty(TailObjectName);
                        if (targetPropParent == null)
                            throw new NotFoundException("Attribute Name Not Found");

                        object FinalResultTail = await ChangeObjectToExpectedObject(ListChildResult, TailObjectListType.FullName!);
                        targetPropParent.SetValue(ParentObject, FinalResultTail);
                        ListParentResult.Add(ParentObject!);
                    }
                    ParentObject = ListParentResult;
                }
                return ParentObject;
            }
            catch
            {
                throw;
            }
        }

        public async Task<object> ChangeObjectToExpectedObject(object Obj, string TargetType)
        {
            try
            {
                var target = Type.GetType(TargetType); 
                var json = JsonConvert.SerializeObject(Obj);
                var result = JsonConvert.DeserializeObject(json, target!)!;
                return await Task.FromResult(result);
            }
            catch (Exception)
            {
                throw ;
            }
        }
        //public string GetWebServiceSettingForMenu(int UserId, int WebServiceId, int MenuId, int ReportId, LoginDTO LoginDTO)
        //{
        //    try
        //    {

        //        List<WebServiceSettingQueryDTO> WebServiceSettingQueryDTOs = MenuDal.GetWebServiceSettingForMenu(UserId, WebServiceId, MenuId, ReportId, LoginDTO);

        //        object TempFilterObjectList = CommonFunctionDAL.MultiLevelDTO<WebServiceSettingQueryDTO>(WebServiceSettingQueryDTOs, typeof(WebServiceSettingDTO), typeof(WebServiceSettingDetailDTO), "WebServiceSettingDetailArray", typeof(List<WebServiceSettingDetailDTO>), "WebServiceSettingId");
        //        string Json = CommonFunctionDAL.SerializeJson(TempFilterObjectList);

        //        return Json;
        //    }
        //    catch (Exception Error)
        //    {
        //        throw Error;
        //    }
        //}
        public async Task<string> GetWebServiceSettingForMenu(int UserId, int WebServiceId, int MenuId, int ReportId, LoginDTO LoginDTO)
        {
            List<WebServiceSettingQueryDTO> WebServiceSettingQueryDTOs = new List<WebServiceSettingQueryDTO>();
            try
            {
                string Sql = "";
                int WorkPartyBranchId = LoginDTO.WorkPartyBranchId;
                int WorkOUId = LoginDTO.WorkOUId;
                int WorkStoreId = LoginDTO.WorkStoreId;
                int WorkPeriodId = LoginDTO.WorkPeriodId;

                #region LocalAndSqlDateTime
                DateTime today     = DateTime.UtcNow;
                DateTime yesterday = today.AddDays(-1);

                string todayFromStr      = SqlDateOnly(today.Date);
                string todayToStr        = SqlDateOnly(today.Date);
                string todayTimeStampStr = SqlDate(today);

                string yesterdayFromStr      = SqlDateOnly(yesterday.Date);
                string yesterdayToStr        = SqlDateOnly(yesterday.Date);
                string yesterdayTimeStampStr = SqlDate(yesterday);

                string weekFromStr         = SqlDateOnly(today.AddDays(-7).Date);
                string monthFromStr        = SqlDateOnly(today.AddMonths(-1).Date);
                string yearFromStr         = SqlDateOnly(today.AddYears(-1).Date);
                string currentMonthFromStr = SqlDateOnly(new DateTime(today.Year, today.Month, 1));
                string currentYearFromStr  = SqlDateOnly(new DateTime(today.Year, 1, 1));
                string currentYearToStr    = SqlDateOnly(new DateTime(today.Year, 12, 31));
                #endregion

                Sql = MenuQB.GET_WEB_SERVICE_SETTING;
                var Parameter = new
                {
                    userid            = UserId,
                    webserviceid      = WebServiceId,
                    menuid            = MenuId,
                    reportid          = ReportId,
                    workpartybranchid = WorkPartyBranchId,
                    workouid          = WorkOUId,
                    workstoreid       = WorkStoreId,
                    workperiodid      = WorkPeriodId
                };

                WebServiceSettingQueryDTOs = await _queryExecutor.StreamAsync<WebServiceSettingQueryDTO>(LoginDTO, Sql, Parameter).ToListAsync();

                // Resolve {Token} placeholders in CriteriaFieldValue with actual SQL datetime strings
                var wsParamMap = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
                {
                    { "@workpartybranchid",  WorkPartyBranchId.ToString() },
                    { "@workouid",           WorkOUId.ToString() },
                    { "@workstoreid",        WorkStoreId.ToString() },
                    { "@workperiodid",       WorkPeriodId.ToString() },
                    { "@todayfrom",          todayFromStr },
                    { "@todayto",            todayToStr },
                    { "@todaytimestamp",     todayTimeStampStr },
                    { "@yesterdayfrom",      yesterdayFromStr },
                    { "@yesterdayto",        yesterdayToStr },
                    { "@yesterdaytimestamp", yesterdayTimeStampStr },
                    { "@weekfrom",           weekFromStr },
                    { "@monthfrom",          monthFromStr },
                    { "@yearfrom",           yearFromStr },
                    { "@currentmonthfrom",   currentMonthFromStr },
                    { "@currentyearfrom",    currentYearFromStr },
                    { "@currentyearto",      currentYearToStr },
                };
                foreach (var row in WebServiceSettingQueryDTOs)
                {
                    if (!string.IsNullOrEmpty(row.WebServiceSettingDetailCriteriaFieldValue)
                        && row.WebServiceSettingDetailCriteriaFieldValue != "null")
                    {
                        foreach (var kv in wsParamMap)
                            row.WebServiceSettingDetailCriteriaFieldValue =
                                row.WebServiceSettingDetailCriteriaFieldValue.Replace(kv.Key, kv.Value);
                    }
                }

                object TempFilterObjectList = MultiLevelDTO<WebServiceSettingQueryDTO>(WebServiceSettingQueryDTOs, typeof(WebServiceSettingDTO), typeof(WebServiceSettingDetailDTO), "WebServiceSettingDetailArray", typeof(List<WebServiceSettingDetailDTO>), "WebServiceSettingId");
                string Json = JsonConvert.SerializeObject(((JObject)JToken.FromObject(TempFilterObjectList))["Result"]); 
                return Json;
            }
            catch (Exception  )
            {
                throw ;
            }
        }

        private static string SqlDate(DateTime dt) =>
            dt.ToString("yyyy-MM-dd HH:mm:ss.fff");

        private static string SqlDateOnly(DateTime dt) =>
            dt.ToString("yyyy-MM-dd");

        private static string ToEpochString(string value)
        {
            if (string.IsNullOrWhiteSpace(value) || value == "NONE")
                return value;

            if (DateTime.TryParse(value, null,
                    System.Globalization.DateTimeStyles.AssumeUniversal |
                    System.Globalization.DateTimeStyles.AdjustToUniversal,
                    out DateTime dt))
            {
                return new DateTimeOffset(dt).ToUnixTimeMilliseconds().ToString();
            }

            return value;
        }

        private static string FromEpochString(string value)
        {
            if (string.IsNullOrWhiteSpace(value) || value == "NONE")
                return value;

            if (long.TryParse(value, out long epochSeconds))
            {
                return DateTimeOffset.FromUnixTimeSeconds(epochSeconds).UtcDateTime.ToString("yyyy-MM-dd");
            }

            return value;
        }

        private string ChangedUri(string Uri, int PageNumber)
        { 
            try
            {
                string FinalUri = "";
                if (PageNumber == 0)
                {
                    FinalUri = Uri;
                }
                else if (PageNumber == -1)
                {
                    #region Max Result Replace To -1
                    string[] stringSeparators = new string[] { "&MaxResult=" };
                    string[] parts = Uri.Split(stringSeparators, StringSplitOptions.RemoveEmptyEntries);
                    if (parts.Length > 1)
                    {
                        stringSeparators = new string[] { "&" };
                        string[] Tempparts = parts[1].Split(stringSeparators, StringSplitOptions.RemoveEmptyEntries);
                        if (Tempparts.Length > 1)
                        {
                            if (parts.Length > 1)
                            {
                                stringSeparators = new string[] { "&" };
                                Tempparts = parts[1].Split(stringSeparators, StringSplitOptions.RemoveEmptyEntries);
                                if (Tempparts.Length > 1)
                                {
                                    Uri = parts[0] + "&MaxResult=-1&";
                                    string TempAdd = "";
                                    for (int ii = 1; ii < Tempparts.Length; ii++)
                                    {
                                        TempAdd = TempAdd + Tempparts[ii] + "&";
                                    }
                                    Uri = Uri + TempAdd;

                                }
                            }
                        }
                        else
                        {
                            Uri = parts[0] + "&MaxResult=-1&";
                        }
                    }

                    Uri = Uri.Remove(Uri.Length - 1, 1);
                    FinalUri = Uri;

                    #endregion
                }
                else
                {
                    #region Max Result Replace To -1
                    string[] stringSeparators = new string[] { "&MaxResult=" };
                    string[] parts = Uri.Split(stringSeparators, StringSplitOptions.RemoveEmptyEntries);
                    if (parts.Length > 1)
                    {
                        stringSeparators = new string[] { "&" };
                        string[] Tempparts = parts[1].Split(stringSeparators, StringSplitOptions.RemoveEmptyEntries);
                        if (Tempparts.Length > 1)
                        {
                            if (parts.Length > 1)
                            {
                                stringSeparators = new string[] { "&" };
                                Tempparts = parts[1].Split(stringSeparators, StringSplitOptions.RemoveEmptyEntries);
                                if (Tempparts.Length > 1)
                                {
                                    Uri = parts[0] + "&MaxResult=" + PageNumber + "&";
                                    string TempAdd = "";
                                    for (int ii = 1; ii < Tempparts.Length; ii++)
                                    {
                                        TempAdd = TempAdd + Tempparts[ii] + "&";
                                    }
                                    Uri = Uri + TempAdd;

                                }
                            }
                        }
                        else
                        {
                            Uri = parts[0] + "&MaxResult=" + PageNumber + "&";
                        }
                    }

                    Uri = Uri.Remove(Uri.Length - 1, 1);
                    FinalUri = Uri;
                    #endregion
                }

                return FinalUri;
            }
            catch (Exception)
            {
                throw;
            } 
        }
        /// <summary>
        /// Retrieves a list of Menu records based on specified filter criteria.
        /// Typically used for populating dropdowns or selection lists.
        /// </summary>
        /// <param name="CriteriaDTO">Filter criteria for selecting Menus.</param>
        /// <param name="LoginDTO">The current user's login details.</param>
        /// <returns>A JSON string representing a list of Menus.</returns>
        public async Task<string> GetSelectListMenu(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                int clientId = LoginDTO.ClientId;
                int? moduleId = null;
                string code = null, name = null;

                if (CriteriaDTO?.SectionCriteriaList != null)
                {
                    foreach (var section in CriteriaDTO.SectionCriteriaList)
                    {
                        if (section?.AttributesCriteriaList == null) continue;
                        foreach (var attr in section.AttributesCriteriaList)
                        {
                            if (attr?.FieldName == null || attr.FieldValue == null) continue;
                            switch (attr.FieldName.ToLower())
                            {
                                case "clientid":
                                    if (int.TryParse(attr.FieldValue.ToString(), out int cid) && cid != -1)
                                        clientId = cid;
                                    break;
                                case "module":
                                    if (int.TryParse(attr.FieldValue.ToString(), out int mid) && mid != -1)
                                        moduleId = mid;
                                    break;
                                case "code":
                                    var codeVal = attr.FieldValue.ToString();
                                    if (!string.IsNullOrEmpty(codeVal)) code = codeVal;
                                    break;
                                case "name":
                                    var nameVal = attr.FieldValue.ToString();
                                    if (!string.IsNullOrEmpty(nameVal)) name = nameVal;
                                    break;
                            }
                        }
                    }
                }

                string sql = MenuQB.GET_SELECTLIST_MENU;
                var result = await _queryExecutor.QueryAsync<MenuPicklistDTO>(LoginDTO, sql,
                    new { ClientId = clientId, ModuleId = moduleId, Code = code, Name = name });
                string json = JsonConvert.SerializeObject(result);
                return json;
            }
            catch (Exception ex)
            {
                throw new Exception(FrameworkResource.SelectListFailed, ex);
            }
        }

        public async Task<IEnumerable<LoadMenuListDTO>> GetMenuListBasedMenuSetForTree(int ModuleId, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string sql = MenuQB.GET_MENU_LIST_BASED_MENUSET_TREE;
                var parameters = new
                {
                    clientid      = LoginDTO.ClientId,
                    moduleid      = ModuleId,
                    SearchKeyword = CriteriaDTO?.SearchKeyword
                };
                return await _queryExecutor.QueryAsync<LoadMenuListDTO>(LoginDTO, sql, parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<MenuAdminDTO?> GetMenuAdmin(int menuId, LoginDTO loginDTO)
            => await _queryExecutor.QuerySingleAsync<MenuAdminDTO>(loginDTO, MenuQB.GET_MENU_ADMIN, new { MenuId = menuId }).ConfigureAwait(false);

        public async Task SaveMenu(MenuAdminDTO dto, LoginDTO loginDTO)
            => await _queryExecutor.ExecuteAsync(loginDTO, MenuQB.INSERT_MENU, dto).ConfigureAwait(false);

        public async Task UpdateMenu(MenuAdminDTO dto, LoginDTO loginDTO)
            => await _queryExecutor.ExecuteAsync(loginDTO, MenuQB.UPDATE_MENU, dto).ConfigureAwait(false);

        public async Task DeleteMenu(int menuId, LoginDTO loginDTO)
            => await _queryExecutor.ExecuteAsync(loginDTO, MenuQB.DELETE_MENU, new { MenuId = menuId }).ConfigureAwait(false);
    }
}