public static DynamicSql SqlFilter(
this DynamicSql query, ScheduleDetailQueryFilter filter)
{
query = DynamicSql.DeepClone(query);
var listFilters = new List <string>();
if (filter.id != null)
{
var paramName = query.AddAutoIncrParam(filter.id);
listFilters.Add($"{nameof(ScheduleDetail)}.{nameof(ScheduleDetail.Id)}=@{paramName}");
}
if (filter.name_contains != null)
{
var paramName = query.AddAutoIncrParam(filter.name_contains);
listFilters.Add($"CHARINDEX(@{paramName}, {nameof(ScheduleDetail)}" +
$".{nameof(ScheduleDetail.Name)}) > 0");
}
if (filter.schedule_id != null)
{
var paramName = query.AddAutoIncrParam(filter.schedule_id);
listFilters.Add($"{nameof(ScheduleDetail)}.{nameof(ScheduleDetail.ScheduleId)}=@{paramName}");
}
if (listFilters.Any())
{
var whereClause = "WHERE " + string.Join(" AND ", listFilters);
query.DynamicForm = query.DynamicForm.Replace(DynamicSql.FILTER, whereClause);
}
return(query);
}
private DeviceQueryRow ProcessMultiResults(DynamicSql query, object[] objs)
{
var row = new DeviceQueryRow();
for (var i = 0; i < query.MultiResults.Count; i++)
{
var r = query.MultiResults[i];
switch (r.Key)
{
case DeviceQueryProjection.INFO: row.Device = objs[i] as Device; break;
case DeviceQueryProjection.ACCOUNT: row.DeviceAccount = objs[i] as AppUserRelationship; break;
case DeviceQueryProjection.AREA: row.Area = objs[i] as AreaRelationship; break;
case DeviceQueryProjection.BUILDING: row.Building = objs[i] as BuildingRelationship; break;
case DeviceQueryProjection.FLOOR: row.Floor = objs[i] as FloorRelationship; break;
case DeviceQueryProjection.LOCATION: row.Location = objs[i] as LocationRelationship; break;
case DeviceQueryProjection.SCHEDULE: row.Schedule = objs[i] as ScheduleRelationship; break;
}
}
return(row);
}
public static DynamicSql SqlCount(
this DynamicSql query, string column)
{
query = DynamicSql.DeepClone(query);
query.DynamicForm = query.DynamicForm
.Replace(DynamicSql.PROJECTION, $"COUNT({column})");
return(query);
}
public static DynamicSql SqlSelectPage(
this DynamicSql query, int page, int limit)
{
query = DynamicSql.DeepClone(query);
page = page - 1;
var pName = query.AddAutoIncrParam(page * limit);
var lName = query.AddAutoIncrParam(limit);
query.DynamicForm = query.DynamicForm.Replace(DynamicSql.PAGING,
$"OFFSET @{pName} ROWS FETCH NEXT @{lName} ROWS ONLY");
return(query);
}
public static DynamicSql SqlFilter(
this DynamicSql query, PostQueryFilter filter)
{
query = DynamicSql.DeepClone(query);
var listFilters = new List <string>();
if (filter.id != null)
{
var paramName = query.AddAutoIncrParam(filter.id);
listFilters.Add($"{nameof(Post)}.{nameof(Post.Id)}=@{paramName}");
}
if (filter.type != null)
{
var paramName = query.AddAutoIncrParam(filter.type);
listFilters.Add($"{nameof(Post)}.{nameof(Post.Type)}=@{paramName}");
}
if (filter.loc_id != null)
{
var paramName = query.AddAutoIncrParam(filter.loc_id);
listFilters.Add($"{nameof(Post)}.{nameof(Post.LocationId)}=@{paramName}");
}
if (filter.not_eq_id != null)
{
var paramName = query.AddAutoIncrParam(filter.not_eq_id);
listFilters.Add($"{nameof(Post)}.{nameof(Post.Id)}!=@{paramName}");
}
if (filter.title_contains != null)
{
var paramName = query.AddAutoIncrParam(filter.title_contains);
listFilters.Add($"CHARINDEX(@{paramName}, {nameof(PostContent)}" +
$".{nameof(PostContent.Title)}) > 0");
}
if (filter.ids != null)
{
var listDataParams = query.AddAutoIncrSqlInParam(filter.ids);
listFilters.Add($"{nameof(Post)}.{nameof(Post.Id)} IN ({listDataParams.Placeholder})");
}
if (filter.archived != 2)
{
var paramName = query.AddAutoIncrParam(filter.archived);
listFilters.Add($"{nameof(Post)}.{nameof(Post.Archived)}=@{paramName}");
}
if (listFilters.Any())
{
var whereClause = "WHERE " + string.Join(" AND ", listFilters);
query.DynamicForm = query.DynamicForm.Replace(DynamicSql.FILTER, whereClause);
}
return(query);
}
private ConfigQueryRow ProcessMultiResults(DynamicSql query, object[] objs)
{
var row = new ConfigQueryRow();
for (var i = 0; i < query.MultiResults.Count; i++)
{
var r = query.MultiResults[i];
switch (r.Key)
{
case ConfigQueryProjection.INFO:
case ConfigQueryProjection.SELECT: row.Config = objs[i] as Config; break;
}
}
return(row);
}
private EntityCategoryQueryRow ProcessMultiResults(DynamicSql query, object[] objs)
{
var row = new EntityCategoryQueryRow();
for (var i = 0; i < query.MultiResults.Count; i++)
{
var r = query.MultiResults[i];
switch (r.Key)
{
case EntityCategoryQueryProjection.INFO: row.EntityCategory = objs[i] as EntityCategoryQueryResult; break;
case EntityCategoryQueryProjection.CONTENT: row.Content = objs[i] as EntityCategoryContentRelationship; break;
}
}
return(row);
}
private FloorQueryRow ProcessMultiResults(DynamicSql query, object[] objs)
{
var row = new FloorQueryRow();
for (var i = 0; i < query.MultiResults.Count; i++)
{
var r = query.MultiResults[i];
switch (r.Key)
{
case FloorQueryProjection.INFO:
case FloorQueryProjection.SELECT:
row.Floor = objs[i] as Floor; break;
case FloorQueryProjection.BUILDING: row.Building = objs[i] as BuildingRelationship; break;
}
}
return(row);
}
