public async Task <IActionResult> Get([FromQuery] AreaQueryFilter filter,
[FromQuery] AreaQuerySort sort,
[FromQuery] AreaQueryProjection projection,
[FromQuery] AreaQueryPaging paging,
[FromQuery] AreaQueryOptions options)
{
var validationResult = _service.ValidateGetAreas(
User, filter, sort, projection, paging, options);
if (!validationResult.Valid)
{
return(BadRequest(validationResult.Result));
}
var result = await _service.QueryAreaDynamic(
projection, options, filter, sort, paging);
if (options.single_only)
{
if (result == null)
{
return(NotFound(new AppResultBuilder().NotFound()));
}
return(Ok(new AppResultBuilder().Success(result.SingleResult)));
}
return(Ok(new AppResultBuilder().Success(result)));
}
public ValidationResult ValidateGetAreas(
ClaimsPrincipal principal,
AreaQueryFilter filter,
AreaQuerySort sort,
AreaQueryProjection projection,
AreaQueryPaging paging,
AreaQueryOptions options)
{
return(ValidationResult.Pass());
}
public QueryResult QueryAreaList(AreaQueryFilter filter)
{
int totalCount;
QueryResult returnResult = new QueryResult()
{
Data = ObjectFactory <IAreaDA> .Instance.QueryAreaList(filter, out totalCount)
};
returnResult.TotalCount = totalCount;
return(returnResult);
}
public ValidationData ValidateGetAreas(
AreaQueryFilter filter,
AreaQuerySort sort,
AreaQueryProjection projection,
AreaQueryPaging paging,
AreaQueryOptions options)
{
var validationData = new ValidationData();
return(validationData);
}
/// <summary>
/// 查询市级以上的地域
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryAreaNoDistrictList(AreaQueryFilter filter, out int totalCount)
{
totalCount = 0;
CustomDataCommand customCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetAreaList");
PagingInfoEntity pagingInfo = ToPagingInfo(filter.PagingInfo);
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(customCommand.CommandText, customCommand, pagingInfo, "SysNo DESC"))
{
if (filter.DistrictSysNumber.HasValue)
{//精确查询区
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SysNo", DbType.String, "@SysNo", QueryConditionOperatorType.Equal, filter.DistrictSysNumber);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CitySysNo", DbType.String, "@CitySysNo", QueryConditionOperatorType.Equal, filter.CitySysNumber);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ProvinceSysNo", DbType.String, "@ProvinceSysNo", QueryConditionOperatorType.Equal, filter.ProvinceSysNumber);
}
else
if (filter.CitySysNumber.HasValue)
{ //查询出市以及其下属的区
using (GroupCondition g = new GroupCondition(sqlBuilder, QueryConditionRelationType.AND))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SysNo", DbType.String, "@SysNo", QueryConditionOperatorType.Equal, filter.CitySysNumber);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ProvinceSysNo", DbType.String, "@ProvinceSysNo", QueryConditionOperatorType.Equal, filter.ProvinceSysNumber);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "CitySysNo", DbType.String, "@CitySysNo", QueryConditionOperatorType.Equal, filter.CitySysNumber);
}
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ProvinceSysNo", DbType.String, "@ProvinceSysNo", QueryConditionOperatorType.Equal, filter.ProvinceSysNumber);
}
else if (filter.ProvinceSysNumber.HasValue)
{ //查询出省以及其下属的市和区
using (GroupCondition g = new GroupCondition(sqlBuilder, QueryConditionRelationType.AND))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SysNo", DbType.String, "@SysNo", QueryConditionOperatorType.Equal, filter.ProvinceSysNumber);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "ProvinceSysNo", DbType.String, "@ProvinceSysNo", QueryConditionOperatorType.Equal, filter.ProvinceSysNumber);
//paramValues 设置为filter.ProvinceSysNumber是为了通过构造验证没有实际意义
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "CitySysNo", DbType.String, "", QueryConditionOperatorType.IsNull, filter.ProvinceSysNumber);
}
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.Status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Rank", DbType.String, "@Rank", QueryConditionOperatorType.Equal, filter.Rank);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "WeightLimit", DbType.Int32, "@WeightLimit", QueryConditionOperatorType.Equal, filter.WeightLimit);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOAmountLimit", DbType.Decimal, "@SOAmountLimit", QueryConditionOperatorType.Equal, filter.SOAmtLimit);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, "8601");
sqlBuilder.ConditionConstructor.AddNullCheckCondition(QueryConditionRelationType.AND, "[DistrictName]", QueryConditionOperatorType.IsNull);
sqlBuilder.ConditionConstructor.AddNullCheckCondition(QueryConditionRelationType.AND, "[CityName]", QueryConditionOperatorType.IsNotNull);
customCommand.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = customCommand.ExecuteDataTable(new EnumColumnList {
{ "Status", typeof(AreaStatus) }
});
totalCount = int.Parse(customCommand.GetParameterValue("TotalCount").ToString());
return(dt);
}
}
public void QueryAreaList(AreaQueryVM req, int PageSize, int PageIndex, string SortField, EventHandler <RestClientEventArgs <dynamic> > callback)
{
AreaQueryFilter filter = new AreaQueryFilter()
{
ProvinceSysNumber = req.ProvinceSysNumber,
CitySysNumber = req.CitySysNumber,
DistrictSysNumber = req.DistrictSysNumber,
SysNo = req.DistrictSysNumber,
PagingInfo = new QueryFilter.Common.PagingInfo()
{
PageIndex = PageIndex,
PageSize = PageSize,
SortBy = SortField
}
};
string relativeUrl = "/Area/QueryAreaNoDistrictList";
restClient.QueryDynamicData(relativeUrl, filter, callback);
}
public async Task <IActionResult> Get([FromQuery][QueryObject] AreaQueryFilter filter,
[FromQuery] AreaQuerySort sort,
[FromQuery] Business.Models.AreaQueryProjection projection,
[FromQuery] AreaQueryPaging paging,
[FromQuery] AreaQueryOptions options)
{
var validationData = _service.ValidateGetAreas(
filter, sort, projection, paging, options);
if (!validationData.IsValid)
{
return(BadRequest(AppResult.FailValidation(data: validationData)));
}
var result = await _service.QueryAreaDynamic(
projection, validationData.TempData, filter, sort, paging, options);
if (options.single_only && result == null)
{
return(NotFound(AppResult.NotFound()));
}
return(Ok(AppResult.Success(data: result)));
}
public static DynamicSql SqlFilter(
this DynamicSql query, AreaQueryFilter filter)
{
query = DynamicSql.DeepClone(query);
var listFilters = new List <string>();
if (filter.id != null)
{
var paramName = query.AddAutoIncrParam(filter.id);
listFilters.Add($"{nameof(Area)}.{nameof(Area.Id)}=@{paramName}");
}
if (filter.floor_id != null)
{
var paramName = query.AddAutoIncrParam(filter.floor_id);
listFilters.Add($"{nameof(Area)}.{nameof(Area.FloorId)}=@{paramName}");
}
if (filter.loc_id != null)
{
var paramName = query.AddAutoIncrParam(filter.loc_id);
listFilters.Add($"{nameof(Area)}.{nameof(Area.LocationId)}=@{paramName}");
}
if (filter.name_contains != null)
{
var paramName = query.AddAutoIncrParam(filter.name_contains);
listFilters.Add($"CHARINDEX(@{paramName}, {nameof(Area)}" +
$".{nameof(Area.Name)}) > 0");
}
if (filter.archived != 2)
{
var paramName = query.AddAutoIncrParam(filter.archived);
listFilters.Add($"{nameof(Area)}.{nameof(Area.Archived)}=@{paramName}");
}
if (listFilters.Any())
{
var whereClause = "WHERE " + string.Join(" AND ", listFilters);
query.DynamicForm = query.DynamicForm.Replace(DynamicSql.FILTER, whereClause);
}
return(query);
}
public static IQueryable <BuildingArea> Filter(this IQueryable <BuildingArea> query, AreaQueryFilter model,
IDictionary <string, object> tempData)
{
return(query);
}
public async Task <QueryResult <AreaQueryRow> > QueryArea(
AreaQueryFilter filter = null,
AreaQuerySort sort = null,
AreaQueryProjection projection = null,
AreaQueryPaging paging = null,
AreaQueryOptions options = null)
{
var conn = context.Database.GetDbConnection();
var openConn = conn.OpenAsync();
var query = AreaQuery.CreateDynamicSql();
#region General
if (filter != null)
{
query = query.SqlFilter(filter);
}
if (projection != null)
{
query = query.SqlJoin(projection);
}
DynamicSql countQuery = null; int?totalCount = null; Task <int> countTask = null;
if (options != null && options.count_total)
{
countQuery = query.SqlCount("*");
}
if (projection != null)
{
query = query.SqlProjectFields(projection);
}
#endregion
await openConn;
if (options != null && !options.single_only)
{
#region List query
if (sort != null)
{
query = query.SqlSort(sort);
}
if (paging != null && (!options.load_all || !AreaQueryOptions.IsLoadAllAllowed))
{
query = query.SqlSelectPage(paging.page, paging.limit);
}
#endregion
#region Count query
if (options.count_total)
{
countTask = conn.ExecuteScalarAsync <int>(
sql: countQuery.PreparedForm,
param: countQuery.DynamicParameters);
}
#endregion
}
var queryResult = await conn.QueryAsync(
sql : query.PreparedForm,
types : query.GetTypesArr(),
map : (objs) => ProcessMultiResults(query, objs),
splitOn : string.Join(',', query.GetSplitOns()),
param : query.DynamicParameters);
if (options != null && options.single_only)
{
var single = queryResult.FirstOrDefault();
return(new QueryResult <AreaQueryRow>
{
SingleResult = single
});
}
if (options != null && options.count_total)
{
totalCount = await countTask;
}
return(new QueryResult <AreaQueryRow>
{
Results = queryResult,
TotalCount = totalCount
});
}
public async Task <QueryResult <IDictionary <string, object> > > QueryAreaDynamic(
AreaQueryProjection projection,
IDictionary <string, object> tempData = null,
AreaQueryFilter filter = null,
AreaQuerySort sort = null,
AreaQueryPaging paging = null,
AreaQueryOptions options = null)
{
var query = BuildingAreas.AsNoTracking();
if (filter != null)
{
query = query.Filter(filter, tempData);
}
int?totalCount = null; Task <int> countTask = null;
var countQuery = query;
query = query.Project(projection);
if (options != null && !options.single_only)
{
#region List query
if (sort != null)
{
query = query.Sort(sort);
}
if (paging != null && (!options.load_all || !AreaQueryOptions.IsLoadAllAllowed))
{
query = query.SelectPage(paging.page, paging.limit);
}
#endregion
#region Count query
if (options.count_total)
{
countTask = countQuery.CountAsync();
}
#endregion
}
if (options != null && options.count_total)
{
totalCount = await countTask;
}
var queryResult = await query.ToListAsync();
if (options != null && options.single_only)
{
var single = queryResult.FirstOrDefault();
if (single == null)
{
return(null);
}
var singleResult = GetBuildingAreaDynamic(single, projection, options);
return(new QueryResult <IDictionary <string, object> >()
{
SingleResult = singleResult
});
}
var results = GetBuildingAreaDynamic(queryResult, projection, options);
return(new QueryResult <IDictionary <string, object> >()
{
Results = results,
TotalCount = totalCount
});
}
