private static List <JObject> GetData(string mixDatabaseName, string whereSql, string pagingSql, MixCmsContext context, IDbContextTransaction transaction)
{
var database = MixDatabases.UpdateViewModel.Repository.GetSingleModel(m => m.Name == mixDatabaseName, context, transaction).Data;
string commandText = $"SELECT * FROM {MixConstants.CONST_MIXDB_PREFIX}{mixDatabaseName} WHERE {whereSql} {pagingSql}";
var result = EFCoreHelper.RawSqlQuery(commandText, context);
foreach (var col in database.Columns.Where(c => c.DataType == MixDataType.Reference))
{
foreach (var item in result)
{
var refData = MixDatabaseDatas.Helper.DecodeRefData(item.Value <string>(col.Name));
if (refData == null)
{
item[col.Name] = new JArray();
continue;
}
var dbName = refData.Value <string>("ref_table_name");
var children = refData.Value <JArray>("children").Select(m => m["DataId"]).ToList();
if (children.Count > 0)
{
string where = $"id IN ({string.Join(",", children.Select(m => $"'{m}'"))})";
item[col.Name] = JArray.FromObject(GetData(dbName, where, null, context, transaction));
}
else
{
item[col.Name] = new JArray();
}
}
}
return(result);
}
public static OrmHelper Create <T>(IQueryable <T> queryable, object dataContext)
{
if (dataContext is DbContext dbContext)
{
var efCoreHelper = new EFCoreHelper(dbContext.Database.ProviderName);
efCoreHelper.Initialize(queryable);
return(efCoreHelper);
}
var queryType = queryable.GetType();
var dataQueryType = queryType.Assembly.GetType("System.Data.Linq.DataQuery`1");
var tableQueryType = queryType.Assembly.GetType("System.Data.Linq.Table`1");
var queryGenericType = queryType.GetGenericTypeDefinition();
if (queryGenericType == dataQueryType || queryGenericType == tableQueryType)
{
var contextField = queryType.GetField("context", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.GetField);
var context = contextField?.GetValue(queryable);
if (context != null)
{
var linqToSqlHelper = new LinqToSqlHelper(context);
linqToSqlHelper.Initialize(queryable);
return(linqToSqlHelper);
}
}
return(null);
}
public static PaginationModel <JObject> GetListData <T>(HttpRequest request, string culture = null)
{
culture = culture ?? MixService.GetAppSetting <string>("DefaultCulture");
string mixDatabaseName = request.Query["mixDatabaseName"].ToString().Trim();
var orderBy = "Id";
Enum.TryParse(request.Query["direction"], out Heart.Enums.DisplayDirection direction);
Enum.TryParse(request.Query["filterType"], out MixCompareOperatorKind filterType);
bool.TryParse(request.Query["isGroup"], out bool isGroup);
int.TryParse(request.Query["pageIndex"], out int pageIndex);
var isPageSize = int.TryParse(request.Query["pageSize"], out int pageSize);
bool isFromDate = DateTime.TryParse(request.Query["fromDate"], out DateTime fromDate);
bool isToDate = DateTime.TryParse(request.Query["toDate"], out DateTime toDate);
bool isStatus = Enum.TryParse(request.Query["status"], out MixContentStatus status);
var fieldQueries = !string.IsNullOrEmpty(request.Query["query"]) ? JObject.Parse(request.Query["query"]) : new JObject();
string whereSql = GetQueryString(MixRequestQueryKeywords.Specificulture, filterType, culture);
whereSql = whereSql.AndAlsoIf(
isFromDate,
GetQueryString(
MixQueryColumnName.CreatedDateTime,
MixCompareOperatorKind.GreaterThanOrEqual,
fromDate.ToString()));
whereSql = whereSql.AndAlsoIf(
isToDate,
GetQueryString(
MixQueryColumnName.CreatedDateTime,
MixCompareOperatorKind.LessThanOrEqual,
toDate.ToString()));
whereSql = whereSql.AndAlsoIf(
isStatus,
GetQueryString(
MixQueryColumnName.Status,
MixCompareOperatorKind.Equal,
status.ToString()));
// if filter by field name or keyword => filter by attr value
if (fieldQueries.Count > 0)
{
// filter by all fields if have keyword
if (fieldQueries != null && fieldQueries.Properties().Count() > 0) // filter by specific field name
{
foreach (JProperty property in fieldQueries.Properties())
{
whereSql = whereSql.AndAlso(GetQueryString(property.Name, filterType, fieldQueries.Value <string>(property.Name)));
}
}
}
using (var context = new MixCmsContext())
{
using (var transaction = context.Database.BeginTransaction())
{
var database = MixDatabases.UpdateViewModel.Repository.GetSingleModel(m => m.Name == mixDatabaseName, context, transaction).Data;
string countSql = $"SELECT COUNT(*) as total FROM {MixConstants.CONST_MIXDB_PREFIX}{mixDatabaseName} WHERE {whereSql}";
string pagingSql = $" ORDER BY {orderBy} {direction} LIMIT {pageSize} OFFSET {pageIndex * pageSize}";
var count = EFCoreHelper.RawSqlQuery(countSql, context)[0].Value <int>("total");
var result = GetData(mixDatabaseName, whereSql, pagingSql, context, transaction);
return(new PaginationModel <JObject>()
{
Items = result,
PageSize = pageSize,
PageIndex = pageIndex,
TotalItems = result.Count,
TotalPage = (count / pageSize) + (count % pageSize > 0 ? 1 : 0)
});
}
}
}
