public async Task <IEnumerable <ContractsToInvoiceDto> > SearchContractToPurchaseInvoiceAsync(string company, EntitySearchRequest searchRequest) { var grideCode = "invoicePurcGoodsSelectionGrid"; var grid = await _gridQueries.GetGrid(grideCode, company); if (grid == null) { throw new AtlasTechnicalException($"No grid configuration found for {grideCode}."); } if (grid.MaxNumberOfRecords.HasValue && grid.MaxNumberOfRecords < searchRequest.Limit) { searchRequest.Limit = grid.MaxNumberOfRecords; } var dynamicQueryDefinition = _mapper.Map <DynamicQueryDefinition>(searchRequest); var columnConfiguration = _mapper.Map <List <ColumnConfiguration> >(grid.Columns); var companyDate = await _systemDateTimeService.GetCompanyDate(company); var dataVersionId = searchRequest.DataVersionId ?? await _systemDateTimeService.GetCompanyCurrentDataVersionId(company); var userDepartments = (await _userService.GetUserByIdAsync(_identityService.GetUserAtlasId(), false)).Permissions .FirstOrDefault(permission => permission.CompanyId == company) .Departments.Select(department => department.DepartmentId).ToList(); var buildQueryResult = DynamicQueryBuilder.BuildQuery(company, _identityService.GetUserName(), dynamicQueryDefinition, "[Configuration].[Vw_PurchaseGoodsContractsListAndSearch]", columnConfiguration, companyDate, dataVersionId, userDepartments); var contractsResults = await ExecuteDynamicQueryAsync <ContractsToInvoiceDto>(buildQueryResult.Sql, buildQueryResult.Parameters); return(contractsResults.ToList()); }
private DynamicQuery BuildDynamicQuery() { DynamicQueryBuilder builder = DynamicQueryBuilder.Create(true); var child1 = builder.ParamGroupBuilder.CreateChildAndGroup(); child1.ParamBuilder.Any("Extras", sub => { sub.ParamBuilder.Equal("Guest", "Andy"); }); var child2 = builder.ParamGroupBuilder.CreateChildAndGroup() .ParamBuilder .Contains("test_address", "chengdu") .EndsWith("test_address", "lnk") .Equal("Disabled", false) .GreaterThan("Amout", 10) .GreaterThanOrEqual("Price", 100) .In("Drink", "mileshake,coffee") .LessThan("Count", 10) .LessThanOrEqual("Total", 100) .StartsWith("Url", "Http://"); builder.OrderBy("test_address", ListSortDirection.Ascending) .Take(10); var dynamicQuery = builder.Build(); return(dynamicQuery); }
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline. public void Configure(IApplicationBuilder app, IHostingEnvironment env) { if (env.IsDevelopment()) { app.UseDeveloperExceptionPage(); } app.Run(async(context) => { DynamicQueryBuilder builder = DynamicQueryBuilder.Create(true); var child1 = builder.ParamGroupBuilder.CreateChildAndGroup(); child1.ParamBuilder.Any("Extra", sub => { sub.ParamBuilder.Equal("Guest", "Andy"); }); var child2 = builder.ParamGroupBuilder.CreateChildOrGroup() .ParamBuilder .Contains("Address", "chengdu") .EndsWith("Address", "lnk") .Equal("Disabled", false) .GreaterThan("Amout", 10) .GreaterThanOrEqual("Price", 100) .In("Drink", "mileshake,coffee") .LessThan("Count", 10) .LessThanOrEqual("Total", 100) .StartsWith("Url", "Http://"); builder.OrderBy("Amout", ListSortDirection.Ascending) .Select("Guest").Take(10).Skip(10); var dynamicQuery = builder.Build(); SqlBuilderSample(dynamicQuery, context.RequestServices.GetRequiredService <ISelectBuilder>()); }); }
public virtual IQueryable <T> Search(T p, IQueryable <T> dt) { var props = p.GetType().GetProperties(); foreach (var propertyInfo in props) { var val = propertyInfo.GetValue(p); if (!(val is IModel) && !propertyInfo.PropertyType.IsArray && !propertyInfo.PropertyType.IsInterface && !propertyInfo.PropertyType.IsGenericType && !propertyInfo.PropertyType.IsAbstract) { if (val != null && val.ToString() != "0") { Expression <Func <T, bool> > whereClause; whereClause = DynamicQueryBuilder.Equal <T>(propertyInfo.Name, val); dt = dt.Where(whereClause); } } } return(dt); }
public async Task <IEnumerable <ReferentialBulkEditDto> > SearchBulkEditReferentialCounterPartyListAsync(string company, EntitySearchRequest searchRequest) { var grideCode = "bulkEditGrid"; var grid = await _gridQueries.GetGrid(grideCode, company); if (grid == null) { throw new AtlasTechnicalException($"No grid configuration found for {grideCode}."); } if (grid.MaxNumberOfRecords.HasValue && grid.MaxNumberOfRecords < searchRequest.Limit) { searchRequest.Limit = grid.MaxNumberOfRecords; } var dynamicQueryDefinition = AutoMapper.Mapper.Map <DynamicQueryDefinition>(searchRequest); var columnConfiguration = AutoMapper.Mapper.Map <List <ColumnConfiguration> >(grid.Columns); var companyDate = await _systemDateTimeService.GetCompanyDate(company); var buildQueryResult = DynamicQueryBuilder.BuildQuery(company, _identityService.GetUserName(), dynamicQueryDefinition, "[Configuration].[Vw_BulkEditCounterPartyListAndSearch]", columnConfiguration, companyDate); var referentialCounterPartySearchResult = await ExecuteDynamicQueryAsync <ReferentialBulkEditDto>(buildQueryResult.Sql, buildQueryResult.Parameters); return(referentialCounterPartySearchResult.ToList()); }
protected virtual async Task <IEnumerable <T> > SearchAsync <T>(string company, EntitySearchRequest searchRequest, string gridCode, string viewName, bool skipPaginationLimit = false) { var grid = await _gridQueries.GetGrid(gridCode, company); if (grid == null) { throw new AtlasTechnicalException($"No grid configuration found for {gridCode}."); } if (!skipPaginationLimit && grid.MaxNumberOfRecords.HasValue && grid.MaxNumberOfRecords < searchRequest.Limit) { searchRequest.Limit = grid.MaxNumberOfRecords; } var dynamicQueryDefinition = _mapper.Map <DynamicQueryDefinition>(searchRequest); var columnConfiguration = _mapper.Map <List <ColumnConfiguration> >(grid.Columns); var companyDate = await _systemDateTimeService.GetCompanyDate(company); var userDepartments = (await _userService.GetUserByIdAsync(_identityService.GetUserAtlasId(), false)).Permissions .FirstOrDefault(permission => permission.CompanyId == company) ?.Departments.Select(department => department.DepartmentId).ToList(); var dataVersionId = searchRequest.DataVersionId ?? await _systemDateTimeService.GetCompanyCurrentDataVersionId(company); var buildQueryResult = DynamicQueryBuilder.BuildQuery(company, _identityService.GetUserName(), dynamicQueryDefinition, viewName, columnConfiguration, companyDate, dataVersionId, userDepartments); var results = await ExecuteDynamicQueryAsync <T>(buildQueryResult.Sql, buildQueryResult.Parameters); return(results.ToList()); }
public async Task <IEnumerable <SectionSummaryDto> > GetTradesForAllocationAsync(string company, EntitySearchRequest searchRequest) { var grideCode = "tradeForTradeAllocationList"; var grid = await _gridQueries.GetGrid(grideCode, company); if (grid == null) { throw new AtlasTechnicalException($"No grid configuration found for {grideCode}."); } if (grid.MaxNumberOfRecords.HasValue && grid.MaxNumberOfRecords < searchRequest.Limit) { searchRequest.Limit = grid.MaxNumberOfRecords; } var dynamicQueryDefinition = _mapper.Map <DynamicQueryDefinition>(searchRequest); var columnConfiguration = _mapper.Map <List <ColumnConfiguration> >(grid.Columns); var companyDate = await _systemDateTimeService.GetCompanyDate(company); var dataVersionId = searchRequest.DataVersionId ?? await _systemDateTimeService.GetCompanyCurrentDataVersionId(company); var buildQueryResult = DynamicQueryBuilder.BuildQuery(company, _identityService.GetUserName(), dynamicQueryDefinition, "[Configuration].[Vw_TradeForTradeAllocationListAndSearch]", columnConfiguration, companyDate, dataVersionId); var tradeAllocationResult = await ExecuteDynamicQueryAsync <SectionSummaryDto>(buildQueryResult.Sql, buildQueryResult.Parameters); return(tradeAllocationResult.ToList()); }
private void LoadQuery() { DateTime dteDateTimeCutoff = DateTime.Now; string strSQL = DynamicQueryBuilder.GetDynamicManagedServiceOrdersQuery(dteDateTimeCutoff, dteDateTimeCutoff, dteDateTimeCutoff, dteDateTimeCutoff); strSQL = strSQL.Replace(Environment.NewLine, "<br/>"); lblQuery.Text = strSQL; }
public void Test_GetRecsFromDB_Changeset_DYNAMIC() { DateTime dteDateTimeCutoff = new DateTime(2018, 04, 01, 13, 59, 59); string strSQL = DynamicQueryBuilder.GetDynamicManagedServiceOrdersQuery(dteDateTimeCutoff, dteDateTimeCutoff, dteDateTimeCutoff, dteDateTimeCutoff); DAL.CDWOrderAddressRecord objCDWChangesetQuery = new CDWOrderAddressRecord(); List <CDWOrderAddressRecord> lstResults = objCDWChangesetQuery.GetRecords(strSQL); Assert.IsTrue(lstResults.Count > 0); }
public void TestChargeHistory() { var query = new Model.ChargeHostory[] { new Model.ChargeHostory(), new Model.ChargeHostory(), }.AsQueryable(); var builder = new DynamicQueryBuilder(true); builder.Take(20).Skip(10).ParamGroupBuilder.ParamBuilder.Equal("charge_id", 1); var converter = new QueryConverter(typeof(Model.ChargeHostory), builder.Build(), true); var model = converter.Converter(); }
public async Task <IEnumerable <SectionAssignedToCharterDto> > SearchCharterAssignmentsAsync(string company, EntitySearchRequest searchRequest, GridDto grid) { var dynamicQueryDefinition = _mapper.Map <DynamicQueryDefinition>(searchRequest); var columnConfiguration = _mapper.Map <List <ColumnConfiguration> >(grid.Columns); var companyDate = await _systemDateTimeService.GetCompanyDate(company); var dataVersionId = searchRequest.DataVersionId ?? await _systemDateTimeService.GetCompanyCurrentDataVersionId(company); var buildQueryResult = DynamicQueryBuilder.BuildQuery(company, _identityService.GetUserName(), dynamicQueryDefinition, "[Configuration].[Vw_CharterSectionAssignmentListAndSearch]", columnConfiguration, companyDate, dataVersionId); var charterAssignmentSections = await ExecuteDynamicQueryAsync <SectionAssignedToCharterDto>(buildQueryResult.Sql, buildQueryResult.Parameters); return(charterAssignmentSections.ToList()); }
public void Generate_Postgre_Query_With_Nothing_Test() { // Arrange string query = @"Select * From Person"; DynamicQueryBuilder postgreDynamicQueryBuilder = new DynamicQueryBuilder(); // Act DynamicQuery dynamicQuery = postgreDynamicQueryBuilder .Init( query, new List <LetPortal.Portal.Models.DynamicLists.FilledParameter> { }) .Build(); // Assert Assert.NotNull(dynamicQuery.CombinedQuery); }
public async Task <IEnumerable <CounterpartyDto> > GetBankBrokerAsync(string company, EntitySearchRequest searchRequest, GridDto grid) { var dynamicQueryDefinition = AutoMapper.Mapper.Map <DynamicQueryDefinition>(searchRequest); var columnConfiguration = AutoMapper.Mapper.Map <List <ColumnConfiguration> >(grid.Columns); BuildQueryResult buildQueryResult; var companyDate = await _systemDateTimeService.GetCompanyDate(company); var dataVersionId = await _systemDateTimeService.GetCompanyCurrentDataVersionId(company); buildQueryResult = DynamicQueryBuilder.BuildQuery(company, _identityService.GetUserName(), dynamicQueryDefinition, "[Configuration].[Vw_BankBrokerCounterPartyListAndSearch]", columnConfiguration, companyDate, null, null); var bankResults = await ExecuteDynamicQueryAsync <CounterpartyDto>(buildQueryResult.Sql, buildQueryResult.Parameters); return(bankResults.ToList()); }
protected virtual async Task <List <string> > GenerateFormatedClauses(string company, EntitySearchRequest searchRequest, string grideCode) { var grid = await _gridQueries.GetGrid(grideCode, company); var dynamicQueryDefinition = _mapper.Map <DynamicQueryDefinition>(searchRequest); var columnConfiguration = _mapper.Map <List <ColumnConfiguration> >(grid.Columns); List <string> formatedClauses = new List <string>(); foreach (var clause in dynamicQueryDefinition.Clauses.Clauses) { var formatedClause = DynamicQueryBuilder.FormatClause(clause, columnConfiguration); formatedClauses.Add(formatedClause); } return(formatedClauses); }
public async Task <int> Handle(CreateReportCriteriasCommand request, CancellationToken cancellationToken) { _unitOfWork.BeginTransaction(); try { var grid = await _gridQueries.GetGrid(request.ReportCriterias.GridName, request.Company); if (grid == null) { throw new Exception($"No grid configuration found for {request.ReportCriterias.GridName}."); } var dynamicQueryDefinition = Mapper.Map <DynamicQueryDefinition>(request.ReportCriterias); var columnConfiguration = Mapper.Map <List <ColumnConfiguration> >(grid.Columns); var companyDate = await _systemDateTimeService.GetCompanyDate(request.Company); var criterias = DynamicQueryBuilder.BuildReportFilters(dynamicQueryDefinition, columnConfiguration, companyDate); ReportPredicate predicate = new ReportPredicate { CompanyId = request.Company, Criterias = criterias.Select(c => new ReportCriteria { Value = c.Item1, TableAlias = c.Item2 }) }; var predicateId = await _reportRepository.CreateReportCriteriasAsync(predicate); _unitOfWork.Commit(); _logger.LogInformation("New report criterias created with id {Atlas_PredicateId}.", predicateId); return(predicateId); } catch { _unitOfWork.Rollback(); throw; } }
/// <summary> /// Returns the list of trades for bulk edit /// </summary> /// <param name="company"> The company code</param> /// <param name="searchRequest">List and search request</param> /// <param name="grid"> the grid details</param> public async Task <IEnumerable <SectionBulkEditDto> > GetContractsForBulkEdit(string company, EntitySearchRequest searchRequest, GridDto grid) { var dynamicQueryDefinition = _mapper.Map <DynamicQueryDefinition>(searchRequest); var columnConfiguration = _mapper.Map <List <ColumnConfiguration> >(grid.Columns); var companyDate = await _systemDateTimeService.GetCompanyDate(company); var userDepartments = (await _userService.GetUserByIdAsync(_identityService.GetUserAtlasId(), false)).Permissions .FirstOrDefault(permission => permission.CompanyId == company) .Departments.Select(department => department.DepartmentId).ToList(); var dataVersionId = searchRequest.DataVersionId ?? await _systemDateTimeService.GetCompanyCurrentDataVersionId(company); var buildQueryResult = DynamicQueryBuilder.BuildQuery(company, _identityService.GetUserName(), dynamicQueryDefinition, "[Configuration].[Vw_TradeListAndSearch]", columnConfiguration, companyDate, dataVersionId, userDepartments); var bulkEditResults = await ExecuteDynamicQueryAsync <SectionBulkEditDto>(buildQueryResult.Sql, buildQueryResult.Parameters); return(bulkEditResults.ToList()); }
public void Generate_Postgre_Query_With_Filled_Params_And_Search_Test() { // Arrange string query = @"Select * From Person Where Name={{data.name}}"; DynamicQueryBuilder postgreDynamicQueryBuilder = new DynamicQueryBuilder(); // Act DynamicQuery dynamicQuery = postgreDynamicQueryBuilder .Init( query, new List <LetPortal.Portal.Models.DynamicLists.FilledParameter> { new LetPortal.Portal.Models.DynamicLists.FilledParameter { Name = "data.name", Value = "aaa" } }) .Build(); // Assert Assert.NotEmpty(dynamicQuery.Parameters); }