/// <summary>
/// status queries
/// </summary>
/// <param name="statuses"></param>
public StatusQueries(List<StatusDTO> statuses)
{
try
{
this.Parameters = new List<string>();
this.PaginationStatus = null;
if (statuses != null)
{
foreach(StatusDTO status in statuses)
{
switch(status.action)
{
case "paging":
{
this.PaginationStatus = status;
break;
}
case "filter":
{
this.FilterQuery.Append(this.getFilterQuery(status, this.FilterQuery.ToString(), this.Parameters));
break;
}
case "sort":
{
this.SortQuery.Append(this.getSortQuery(status, this.Parameters));
break;
}
}
}
}
}
catch (Exception ex)
{
Logger.Error("StatusQueries: " + ex.Message + " " + ex.StackTrace);
}
}
/// <summary>
/// get filter query per status
/// </summary>
/// <param name="status">the status object</param>
/// <param name="prevQuery">previous query string</param>
/// <param name="parameters">unnamed parameters list</param>
/// <returns>query part</returns>
private string getFilterQuery(StatusDTO status, string prevQuery, List<string> parameters)
{
string query = "";
string filter = "";
try
{
if (status != null && status.data != null && !String.IsNullOrEmpty(status.name))
{
switch (status.name)
{
case "title-filter":
{
if (!String.IsNullOrEmpty(status.data.path) && !String.IsNullOrEmpty(status.data.value))
{
if (prevQuery.IndexOf("where") == -1)
{
query = "where title like @p" + (parameters.Count) + " ";
}
else
{
query = " and title like @p" + (parameters.Count) + " ";
}
parameters.Add("%" + status.data.value + "%");
}
break;
}
case "desc-filter":
{
if (!String.IsNullOrEmpty(status.data.path) && !String.IsNullOrEmpty(status.data.value))
{
if (prevQuery.IndexOf("where") == -1)
{
query = "where description like @p" + (parameters.Count) + " ";
}
else
{
query = " and description like @p" + (parameters.Count) + " ";
}
parameters.Add("%" + status.data.value + "%");
}
break;
}
case "themes":
{
if (status.data.pathGroup != null && status.data.pathGroup.Count > 0)
{
filter = this.getCheckboxGroupFilterQuery("keyword1", status.data.pathGroup, parameters);
if (!String.IsNullOrEmpty(filter))
{
if (prevQuery.IndexOf("where") == -1)
{
query = "where " + filter;
}
else
{
query = " and (" + filter + ")";
}
}
}
break;
}
case "colors":
{
if (status.data.pathGroup != null && status.data.pathGroup.Count > 0)
{
filter = this.getCheckboxGroupFilterQuery("keyword2", status.data.pathGroup, parameters);
if (!String.IsNullOrEmpty(filter))
{
if (prevQuery.IndexOf("where") == -1)
{
query = "where " + filter;
}
else
{
query = " and (" + filter + ")";
}
}
}
break;
}
}
}
}
catch (Exception ex)
{
Logger.Error("getFilterQuery: " + ex.Message + " " + ex.StackTrace);
}
return query;
}
/// <summary>
/// get sort query per status
/// </summary>
/// <param name="status">the status object</param>
/// <param name="parameters">unnamed parameters list</param>
/// <returns>query part</returns>
private string getSortQuery(StatusDTO status, List<string> parameters)
{
string query = "";
string order = "asc";
try
{
if (status != null && status.data != null && !String.IsNullOrEmpty(status.data.path))
{
switch (status.data.path)
{
case ".title":{
query = " order by title ";
break;
}
case ".desc":{
query = " order by description ";
break;
}
case ".like":{
query = " order by likes ";
break;
}
}
if (!String.IsNullOrEmpty(status.data.order))
{
order = status.data.order.ToLower() == "desc" ? "desc" : "asc";
if (!String.IsNullOrEmpty(query))
{
query += " " + order;
}
}
}
}
catch (Exception ex)
{
Logger.Error("getSortQuery: " + ex.Message + " " + ex.StackTrace);
}
return query;
}
/// <summary>
/// get pagination query
/// </summary>
/// <param name="status">the status object</param>
/// <param name="count">all items number (after the filters were applied)</param>
/// <param name="parameters">unnamed parameters list</param>
/// <returns></returns>
private string getPagingQuery(StatusDTO status, int count, List<string> parameters, string initialQuery)
{
StringBuilder query = new StringBuilder();
int startIndex, endIndex;
string order = " order by id ";
int numberInt;
try
{
if (status != null && status.data != null && Int32.TryParse(status.data.number, out numberInt) && count > numberInt)
{
startIndex = status.data.currentPage * numberInt;
endIndex = startIndex + numberInt;
if (!String.IsNullOrEmpty(this.SortQuery.ToString()))
{
order = this.SortQuery.ToString();
}
query.AppendLine(" SELECT * FROM ( SELECT *, ROW_NUMBER() OVER ( " + order + " ) as row FROM ItemWebsite " + this.FilterQuery.ToString() + ") a WHERE row > " + startIndex + " and row <= " + endIndex);
}
else
{
query.AppendLine(initialQuery);
}
/*
if (status != null && status.data != null && count > status.data.number)
{
startIndex = status.data.currentPage * status.data.number;
endIndex = startIndex + status.data.number;
if (!String.IsNullOrEmpty(this.SortQuery.ToString()))
{
order = this.SortQuery.ToString();
}
query.AppendLine(" SELECT * FROM ( SELECT *, ROW_NUMBER() OVER ( " + order + " ) as row FROM ItemWebsite " + this.FilterQuery.ToString() + ") a WHERE row > " + startIndex + " and row <= " + endIndex);
}
else
{
query.AppendLine(initialQuery);
}
* */
}
catch (Exception ex)
{
Logger.Error("getPagingQuery: " + ex.Message + " " + ex.StackTrace);
}
return query.ToString();
}