/// <summary>
/// adds a new select item onto the InnerQuery Factory object.
/// and creates the new row called RowNumber
/// </summary>
/// <param name="orderBy"></param>
/// <param name="sortDesc"></param>
private void AddRowNumber(string orderBy, bool sortDesc = false)
{
if (string.IsNullOrWhiteSpace(orderBy))
{
throw new ArgumentException("You must set order by to a value in your table");
}
var f = string.Format("Row_Number() over (order by {0} {1}) ", orderBy, sortDesc ? "DESC" : "ASC");
InnerQuery.AddSelect(f, "RowNumber");
}
public IEnumerable <Major> GetAllMajors(int maxCount, int offset)
{
Dictionary <string, object> parameters = new Dictionary <string, object>();
_queryBuilder.AddSelect(@"*");
_queryBuilder.SetFrom("reg_opleidingen");
_queryBuilder.SetLimit("@limit");
_queryBuilder.SetOffset("@offset");
parameters.Add("@limit", maxCount);
parameters.Add("@offset", offset);
string query = _queryBuilder.BuildQuery();
var majors = _optionRepository.GetAllMajors(query, parameters);
return(_mapper.Map <IEnumerable <Major> >(majors));
}
public void AddLocationFilter(ref Dictionary <string, object> parameters, IMapAPIReadService mapAPIReadService, ref IQueryBuilder queryBuilder, char tablename, string columnname, string countryName = null,
string municipalityName = null, string cityName = null, int?locationRange = null)
{
if (!(cityName is null) && !(locationRange is null))
{
// Use Map API
Coordinates coordinates = mapAPIReadService.GetMapCoordinates(cityName, countryName, municipalityName);
if (!(coordinates is null))
{
parameters.Add("@latitude", coordinates.Latitude);
parameters.Add("@longitude", coordinates.Longitude);
parameters.Add("@rangeKm", locationRange);
queryBuilder.AddSelect(String.Format(@"(
6371 * acos(
cos(radians(@latitude))
* cos(radians({0}.{1}_breedtegraad))
* cos(radians({0}.{1}_lengtegraad) - radians(@longitude))
+ sin(radians(@latitude))
* sin(radians({0}.{1}_breedtegraad))
)) as distance", tablename, columnname));
queryBuilder.AddHaving("distance < @rangeKm");
}
