public static string RecreateQueryString(Query query)
{
StringBuilder sb = new StringBuilder("?");
if (query.Filters != null)
{
string[] filters = query.Filters.Select(f => String.Concat("filter=", f.Column.UrlEncode(), ":", f.Value.ToString().UrlEncode())).ToArray();
sb.Append(String.Join("&", filters));
sb.Append("&");
}
if (query.Sorts != null)
{
string[] sorts = query.Sorts.Select(s => String.Concat("sort=", s.Column.UrlEncode(), s.Direction == QuerySortDirection.Descending ? ":desc" : null)).ToArray();
sb.Append(String.Join("&", sorts));
sb.Append("&");
}
if (query.Searches != null)
{
string[] searches = query.Searches.Select(s => String.Concat("search=", s.UrlEncode())).ToArray();
sb.Append(String.Join("&", searches));
sb.Append("&");
}
sb.Append("count=");
sb.Append(query.Count);
return sb.ToString();
}
public static QueriedIssuesViewModel QueryIssues(Guid userGuid, Query query)
{
SqlBuilder sql = new SqlBuilder();
Dictionary<string, object> parameters = new Dictionary<string, object>();
if (query.Filters != null)
{
foreach (var filter in query.Filters)
{
ProcessFilter(userGuid, filter, sql, parameters);
}
}
else
{
ProcessFilter(userGuid, new QueryFilterColumn("status:Open"), sql, parameters);
}
if (query.Sorts != null)
{
foreach (var sort in query.Sorts)
{
ProcessSort(sort, sql, parameters);
}
}
else
{
ProcessSort(new QuerySortColumn("id:desc"), sql, parameters);
}
if (query.Searches != null)
{
ProcessSearch(String.Join(" ", query.Searches), sql, parameters);
}
// Prepare the SQL statements.
var countTemplate = sql.AddTemplate(IssueCountSqlQueryTemplate);
var issuesTemplate = sql.AddTemplate(IssueSqlQueryTemplate);
string rawSql = issuesTemplate.RawSql + " LIMIT " + query.Count;
if (query.Page > 1)
{
rawSql += " OFFSET " + (query.Page - 1) * query.Count;
}
using (var db = DataService.Connect())
{
int total = db.SqlScalar<int>(countTemplate.RawSql, parameters);
return new QueriedIssuesViewModel()
{
Issues = db.Query<IssueViewModel>(rawSql, parameters),
Total = total,
};
}
}
