//[AuthorizeToken]
public async Task <IActionResult> Search([FromBody] SearchDataModel model)
{
#region Check user
Account account = null;
ApplicationUser user = null;
var userID = HttpContext.User.Identity.Name;
if (userID != null)
{
user = await _context.Set <ApplicationUser>().SingleOrDefaultAsync(item => item.UserName == userID);
account = _context.Set <Account>()
.Include(x => x.Searches)
.FirstOrDefault(x => x.ID == user.AccountID);
}
#endregion
try
{
var Sort = EnumHelper <SortingType> .Parse(model.SortingType);
var ads = _context.Set <AD>().Include(x => x.ADImages)
.Include(x => x.Currency)
.Include(x => x.Category)
.ThenInclude(x => x.Parent)
.ThenInclude(x => x.Parent)
.Where(x => x.IsDisabled.GetValueOrDefault() != true);
if (!string.IsNullOrWhiteSpace(model.SearchWord))
{
string searchWords = model.SearchWord.ToLower().Trim();
ads = ads.Where(x =>
x.Title.ToLower().Contains(searchWords) ||
x.Description.ToLower().Contains(searchWords) ||
x.Email.ToLower().Contains(searchWords) ||
(x.Category != null && x.Category.Name.ToLower().Contains(searchWords)) ||
x.Code.ToLower().Contains(searchWords) ||
x.Name.ToLower().Contains(searchWords) ||
x.Phone.ToLower().Contains(searchWords));
}
if (model.LowPrice != null)
{
ads = ads.Where(x => x.Price >= model.LowPrice);
}
if (model.HighPrice != null)
{
ads = ads.Where(x => x.Price <= model.HighPrice);
}
if (model.CategoryID != null)
{
ads = ads.Where(x =>
(x.CategoryID == model.CategoryID) ||
(x.Category != null && x.Category.ParentID == model.CategoryID) ||
(x.Category != null && x.Category.Parent != null && x.Category.Parent.ParentID == model.CategoryID));
}
switch (Sort)
{
case SortingType.None:
case SortingType.MostRecently:
default:
ads = ads.OrderByDescending(s => s.PublishedDate).Skip(model.Page * model.Count ?? 0).Take(model.Count ?? 10);
break;
case SortingType.HighToLowPrice:
ads = ads.OrderByDescending(s => s.Price).Skip(model.Page * model.Count ?? 0).Take(model.Count ?? 10);
break;
case SortingType.LowToHighPrice:
ads = ads.OrderBy(s => s.Price).Skip(model.Page * model.Count ?? 0).Take(model.Count ?? 10);
break;
}
var result = ads.ToList().Select(x => new ADDataModel
{
ID = x.ID,
Name = x.Name,
Likes = x.NumberViews,
PublishedDate = x.PublishedDate,
Views = x.ADViews,
Price = x.Price,
Title = x.Title,
MainImage = x.GetMainImageRelativePath(),
CategoryID = x.CategoryID,
Code = x.Code,
Category = x.Category?.Name,
Currency = x.Currency?.Code ?? "SP",
IsOwner = x.AccountID == account?.ID
});
return(Ok(result));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public SearchDataModel SearchData(SearchDataModel model)
{
return(_service.SearchData(model));
}
public IActionResult Index()
{
var model = SearchDataModel.GetStudentDetail();
return(View(model));
}
private SearchDataModel GetExportDatas(QueryModel model, QueryExcelModel keyModel)
{
SearchDataModel searchDataModel = new SearchDataModel();
List <QueryExcelModelNoData> resultKeyList = new List <QueryExcelModelNoData>();
List <ExportDataModel> list = new List <ExportDataModel>();
var result = SearchData.SearchResult(model.SearchModel);
if (result != null)
{
if (result.Response != null && result.Response.Docs.Count > 0)
{
foreach (var d in result.Response.Docs)
{
ExportDataModel edm = new ExportDataModel();
edm.DocID = d.Id;
if (model.Score > 0)
{
edm.Score = model.Score;
}
else
{
edm.Score = null;
}
edm.ScoreType = model.ScoreType;
edm.SearchKey = model.SearchModel.SearchKey;
edm.QueryType = model.QueryType;
list.Add(edm);
}
resultKeyList.Add(new QueryExcelModelNoData()
{
ErrorType = EnumErrorType.querySuccess.ToString(),
QueryString = keyModel.QueryString,
QueryID = keyModel.QueryID,
QueryType = keyModel.QueryType
});
}
else
{
resultKeyList.Add(new QueryExcelModelNoData()
{
ErrorType = EnumErrorType.SearchNoData.ToString(),
QueryString = keyModel.QueryString,
QueryID = keyModel.QueryID,
QueryType = keyModel.QueryType
});
}
}
else
{
resultKeyList.Add(new QueryExcelModelNoData()
{
ErrorType = EnumErrorType.SearchError.ToString(),
QueryString = keyModel.QueryString,
QueryID = keyModel.QueryID,
QueryType = keyModel.QueryType
});
}
searchDataModel.searchData = list;
searchDataModel.queryResultList = resultKeyList;
return(searchDataModel);
}
/// <summary>
/// Search Dynamic Data
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public IList <object> SearchData(SearchDataModel model)
{
// 1. Set Conditions
var conditionColumnIds = model.SearchConditions.Where(p => !string.IsNullOrEmpty(p.Value)).Select(s => s.ColumnId).ToArray();
// Delete conditions
var deleteConditions = _viewSearchConditionRep.Table.Where(p => !conditionColumnIds.Contains(p.ColumnId) &&
p.ViewId == model.QueryDataModel.ViewId &&
p.CreatedBy == model.QueryDataModel.UserId).ToList();
_viewSearchConditionRep.Delete(deleteConditions);
// Update conditions
var updateConditions = _viewSearchConditionRep.Table.Where(p => conditionColumnIds.Contains(p.ColumnId) &&
p.ViewId == model.QueryDataModel.ViewId &&
p.CreatedBy == model.QueryDataModel.UserId).ToList();
foreach (var item in updateConditions)
{
var updateCondition = model.SearchConditions.Single(s => s.ColumnId == item.ColumnId);
item.Operator = updateCondition.Operator;
item.Value = updateCondition.Value;
item.Condition = updateCondition.Condition;
}
_viewSearchConditionRep.Update(updateConditions);
// Add conditions
var updateConditionColumnIds = updateConditions.Select(s => s.ColumnId).ToArray();
var conditionsNeedToInsert = model.SearchConditions.Where(p => !updateConditionColumnIds.Contains(p.ColumnId)).ToList();
var insertConditions = _mapper.Map <List <CoreViewSearchCondition> >(conditionsNeedToInsert);
_viewSearchConditionRep.Insert(insertConditions);
// Commit
SaveChanges();
// 2. Start Search
// 2.1 Get Columns
var viewColumns = _viewColumnRep.Table.Where(p => p.ViewId == model.QueryDataModel.ViewId && p.IsActived).ToList();
var columnIds = viewColumns.Select(s => s.ColumnId).ToArray();
var columns = _columnRep.Include(i => i.DataType).Where(p => columnIds.Contains(p.Id)).ToList();
model.QueryDataModel.Columns = new List <Common.Models.DataColumn>();
foreach (var item in columns)
{
var viewColumn = viewColumns.Single(s => s.ColumnId == item.Id);
model.QueryDataModel.Columns.Add(new Common.Models.DataColumn
{
Id = item.Id,
Name = item.Name,
NameDict = item.NameDict,
EnumId = item.EnumId,
DataTypeCode = item.DataType.Code,
IsForeignKey = item.IsForeignKey,
IsPrimaryKey = item.IsPrimaryKey,
Position = viewColumn.Position,
Visible = viewColumn.Visible,
Searchable = item.Searchable,
Sortable = item.Sortable,
Width = viewColumn.Width,
TableAlias = item.TableAlias,
SqlName = StringHelper.ConverToCamelCase(item.SqlName)
});
}
// 2.2. Create SqlParameters
var moduleIdParam = new SqlParameter("moduleId", model.QueryDataModel.ModuleId);
var viewIdParam = new SqlParameter("viewId", model.QueryDataModel.ViewId);
var orderExpressionParam = model.QueryDataModel.OrderExpression != null ?
new SqlParameter("orderExpression", model.QueryDataModel.OrderExpression)
: new SqlParameter("orderExpression", DBNull.Value);
var pageIndexParam = new SqlParameter("pageIndex", model.QueryDataModel.PageIndex);
var pageSizeParam = new SqlParameter("pageSize", model.QueryDataModel.PageSize);
var currentUserIdParam = new SqlParameter("userId", model.QueryDataModel.UserId);
var totalRowParam = new SqlParameter
{
ParameterName = "total",
SqlDbType = SqlDbType.Int,
Value = DBNull.Value,
Direction = ParameterDirection.Output
};
var groupJsonParam = new SqlParameter
{
ParameterName = "groupJson",
SqlDbType = SqlDbType.NVarChar,
Size = 4000,
Value = DBNull.Value,
Direction = ParameterDirection.Output
};
var typeInfo = ObjectBuilder.CompileResultTypeInfo("QueryDataResult", "QueryDataResult", model.QueryDataModel.Columns);
// 2.3. Excute query
var result = _rep.ExcuteStoreProcedure(typeInfo,
SqlStoreProcedureConstant.SpSearchData,
moduleIdParam,
viewIdParam,
orderExpressionParam,
pageIndexParam,
pageSizeParam,
currentUserIdParam,
totalRowParam,
groupJsonParam);
model.QueryDataModel.TotalRecord = (int)totalRowParam.Value;
model.QueryDataModel.GroupJson = (string)groupJsonParam.Value;
//model.GroupByObjs = JsonConvert.DeserializeObject<List<GroupByObj>>((string)groupJsonParam.Value);
return(result.ToList());
}
