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);
}
