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);
}
public static DynamicSql SqlFilter(
this DynamicSql query, ResourceTypeQueryFilter filter)
{
query = DynamicSql.DeepClone(query);
var listFilters = new List <string>();
if (filter.id != null)
{
var paramName = query.AddAutoIncrParam(filter.id);
listFilters.Add($"{nameof(ResourceType)}.{nameof(ResourceType.Id)}=@{paramName}");
}
if (filter.name_contains != null)
{
var paramName = query.AddAutoIncrParam(filter.name_contains);
listFilters.Add($"CHARINDEX(@{paramName}, {nameof(ResourceTypeContent)}" +
$".{nameof(ResourceTypeContent.Name)}) > 0");
}
if (filter.archived != 2)
{
var paramName = query.AddAutoIncrParam(filter.archived);
listFilters.Add($"{nameof(ResourceType)}.{nameof(ResourceType.Archived)}=@{paramName}");
}
if (listFilters.Any())
{
var whereClause = "WHERE " + string.Join(" AND ", listFilters);
query.DynamicForm = query.DynamicForm.Replace(DynamicSql.FILTER, whereClause);
}
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 SqlJoin(
this DynamicSql query, ResourceTypeQueryProjection model,
ResourceTypeQueryFilter filter)
{
query = DynamicSql.DeepClone(query);
var joins = model.GetFieldsArr()
.Where(f => ResourceTypeQueryProjection.Joins.ContainsKey(f))
.Select(f => ResourceTypeQueryProjection.Joins[f]);
if (joins.Any())
{
var joinClause = string.Join('\n', joins);
query.DynamicForm = query.DynamicForm
.Replace(DynamicSql.JOIN, joinClause);
if (filter != null)
{
var contentFilters = new List <string>();
if (filter.lang != null)
{
var paramName = query.AddAutoIncrParam(filter.lang);
var postContentLang = $"{nameof(ResourceTypeContent)}.{nameof(ResourceTypeContent.Lang)}";
contentFilters.Add($"{postContentLang}=@{paramName}");
}
if (contentFilters.Any())
{
var whereClause = "WHERE " + string.Join(" AND ", contentFilters);
query.DynamicForm = query.DynamicForm
.Replace(ResourceTypeQueryPlaceholder.RES_TYPE_CONTENT_FILTER, whereClause);
}
}
}
return(query);
}
public static DynamicSql SqlFilter(
this DynamicSql query, AppUserQueryFilter filter)
{
query = DynamicSql.DeepClone(query);
var listFilters = new List <string>();
if (filter.id != null)
{
var paramName = query.AddAutoIncrParam(filter.id);
listFilters.Add($"{AppUser.TBL_NAME}.{nameof(AppUser.Id)}=@{paramName}");
}
if (listFilters.Any())
{
var whereClause = "WHERE " + string.Join(" AND ", listFilters);
query.DynamicForm = query.DynamicForm.Replace(DynamicSql.FILTER, whereClause);
}
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);
}
public static DynamicSql SqlExtras(
this DynamicSql query, ResourceQueryProjection model,
ResourceQueryFilter filter)
{
query = DynamicSql.DeepClone(query);
var extras = model.GetFieldsArr()
.Where(f => ResourceQueryProjection.Extras.ContainsKey(f))
.Select(f => ResourceQueryProjection.Extras[f]);
if (extras.Any())
{
var extraSqls = string.Join(';', extras);
var originalQuery = query.PreparedViewForm;
query.DynamicForm += ";\n" + extraSqls;
query.DynamicForm = query.DynamicForm
.Replace(ResourceQueryPlaceholder.RES_SUB_QUERY, originalQuery);
if (filter != null)
{
if (model.fields.Contains(ResourceQueryProjection.CATEGORIES))
{
var contentFilters = new List <string>();
if (filter.lang != null)
{
var paramName = query.AddAutoIncrParam(filter.lang);
var ecLang = $"{nameof(EntityCategoryContent)}.{nameof(EntityCategoryContent.Lang)}";
contentFilters.Add($"{ecLang}=@{paramName}");
}
if (contentFilters.Any())
{
var whereClause = "WHERE " + string.Join(" AND ", contentFilters);
query.DynamicForm = query.DynamicForm
.Replace(ResourceQueryPlaceholder.CATE_CONTENT_FILTER, whereClause);
}
}
}
}
return(query);
}