public Task <IEnumerable <HRCountry> > GetOrderedsAsync(HRSortingParamModel orderBy)
{
throw new NotImplementedException();
}
public Task <PagingParameterOutModel <HRCountry> > GetOrderedAndPaginatedCountriesAsync(PagingParameterInModel pageModel, HRSortingParamModel orderBy)
{
throw new NotImplementedException();
}
/// <summary>
/// Generate SQLQuery with WHERE clause if necessary on borderID and create ORder BY Clause.
/// Where clause is protected with Parameter to avoid SQL Injection
/// Order By clause is checked against a White List of field available.
/// </summary>
/// <param name="isforDapper">true if the query has to be run by Dapper.</param>
/// <param name="borderID">borderID</param>
/// <returns>SQLQuery to be run or Exception if could not convert to a propoer SQL query (including revention agains t SQL Injection)</returns>
public string GetSQLQuery(bool isforDapper, String borderID = null, HRSortingParamModel orderBy = null, PagingParameterInModel pageInModel = null)
{
StringBuilder sb = new StringBuilder();
if (isforDapper)
{
sb.Append(SQLQUERYFORDAPPER);
}
else
{
sb.Append(SQLQUERY);
}
if (!String.IsNullOrEmpty(borderID) &&
borderID.Length == 2)
{
sb.Append("WHERE ISO2 = '");
//Cheat Code to avoid SQL injection. Indeed pbm with SQL Command and SQLParameters on GeometryColumn with postgis.
sb.Append(borderID.Substring(0, 2).ToUpper());
sb.Append("'");
}
if (pageInModel != null && orderBy == null)
{
//Set default order by as pagination required order by
orderBy = new HRSortingParamModel()
{
OrderBy = "ISO2;ASC"
};
}
if (orderBy != null)
{
IEnumerable <(String, String)> orders = HRSortingParamModelDeserializer.GetFieldOrders(orderBy);
if (orders != null)
{
List <(String, String)> ordersList = orders.ToList();
if (ordersList != null)
{
int itemCount = ordersList.Count;
for (int i = 0; i < itemCount; i++)
{
String fieldNamei = ordersList[i].Item1;
if (!String.IsNullOrEmpty(fieldNamei) &&
_whiteListOfAvaialbleFields != null &&
_whiteListOfAvaialbleFields.ContainsKey(fieldNamei.ToUpper()))
{
if (i == 0)
{
sb.Append(" ORDER BY ");
}
sb.Append(fieldNamei);
sb.Append(" ");
sb.Append(ordersList[i].Item2);
sb.Append(" ");
}
else
{
throw new InvalidOperationException("Field unknow to sort on : " + fieldNamei);
}
}
}
}
if (pageInModel != null)
{
sb.Append(" LIMIT ");
if (pageInModel.PageSize > 0)
{
sb.Append(pageInModel.PageSize.ToString());
}
else
{
sb.Append("ALL");
}
sb.Append(" OFFSET ");
sb.Append(pageInModel.PageNumber * pageInModel.PageSize);
sb.Append(" ");
}
}
return(sb.ToString());
}
/// <summary>
/// Apply query orederd and paginated on PostGIS DB.
/// </summary>
/// <param name="pageModel">the PageModel. Can not be null.</param>
/// <param name="orderBy">The order. Can be null, will be set to default to avoid a fuzzy Pagination.</param>
/// <returns>The corresponding PagingParameterOutModel</returns>
public async Task <PagingParameterOutModel <HRBorder> > GetOrderedAndPaginatedsAsync(PagingParameterInModel pageModel, HRSortingParamModel orderBy)
{
if (pageModel == null)
{
throw new ArgumentNullException();
}
if (_paginer == null)
{
throw new MemberAccessException("Paginer can not be null.");
}
PagingParameterOutModel <HRBorder> retour = null;
String cxString = _config.GetConnectionString(CONNECTION_STRING_KEY);
cxString = String.Format(cxString, _config[_DBUSER], _config[_DBPASSWORD]);
String queryString = GetSQLQuery(true, null, orderBy, pageModel);
using (var conn = new NpgsqlConnection(cxString))
{
conn.Open();
try
{
uint totalItemsCount = 0;
using (Task <int> totalCountTask = conn.ExecuteScalarAsync <int>("SELECT COUNT(*) FROM boundaries"))
{
await totalCountTask;
if (totalCountTask.Result >= 0 && totalCountTask.Result < Int32.MaxValue)
{
totalItemsCount = (uint)(totalCountTask.Result);
}
}
if (_paginer.IsValid(pageModel, totalItemsCount))
{
using (Task <IEnumerable <HRBorder> > queryTask = conn.QueryAsync <HRBorder>(queryString))
{
await queryTask;
retour = new PagingParameterOutModel <HRBorder>()
{
PageItems = queryTask.Result,
PageSize = pageModel.PageSize,
TotalItemsCount = totalItemsCount,
CurrentPage = pageModel.PageNumber
};
}
}
else
{
throw new IndexOutOfRangeException("Pagination out of existing range.");
}
}
catch (Exception ex)
{
if (_logger != null)
{
_logger.Error(ex.Message);
}
throw;
}
return(retour);
}
}
public async Task <PagingParameterOutModel <int> > GetOrderedAndPaginatedsAsync(PagingParameterInModel pageModel, HRSortingParamModel orderBy)
{
await Task.Delay(1);
return(new PagingParameterOutModel <int>()
{
CurrentPage = 42
});
}
public bool CanOrder(HRSortingParamModel orderBy, ISortable service)
{
return(CanOrderReturn);
}
/// <summary>
/// Return paginated items
/// </summary>
/// <param name="pageModel">pageModel, can not be null</param>
/// <param name="orderBy">orderBy clause, can be null</param>
/// <returns>The paginated items.</returns>
public async Task <PagingParameterOutModel <HRCountry> > GetCountriesAsync(PagingParameterInModel pageModel, HRSortingParamModel orderBy)
{
PagingParameterOutModel <HRCountry> retour = null;
if (_workflow == null)
{
if (_logger != null)
{
_logger.LogError("_workflow is null in CoreCountriesService:GetCountriesAsync");
}
throw new MemberAccessException();
}
if (pageModel == null)
{
if (_logger != null)
{
_logger.LogError("pageModel is null in CoreCountriesService:GetCountriesAsync");
}
throw new ArgumentNullException();
}
using (Task <PagingParameterOutModel <HRCountry> > retourTask = _workflow.GetQueryResultsAsync(pageModel, orderBy))
{
await retourTask;
retour = retourTask.Result;
}
return(retour);
}
/// <summary>
/// Method used to paginate results. The query in can be ordered or not.
/// 1- Check that context is consistant before processing.
/// 1.1- Internal member
/// 1.2- Input parameters
/// 2- Process query on repository
/// 2.1- if OrderBy is supplied
/// 2.1.1- Is repository is sortable
/// 2.1.1.1- If repository IsPaginable : Full Query Repository for Pagination and Order and return result
/// 2.1.1.2- Else : Process partial query on ordering repository capacity for internal pagination
/// 2.1.2- Else throw NotSupportedException
/// 2.2- else
/// 2.2.1- If repository is Paginable, Full Query and return result.
/// 2.2.2- else Partial repository Query for internal Pagination
/// 3- This step means that internal Pagination is needed
/// 3-1- Check that Pagination is valid
/// 3.2- Process pagination and return.
/// </summary>
/// <param name="pageModel">The Input Pagination, can not be null.</param>
/// <param name="orderBy">The ordering query. Can be null.</param>
/// <returns>The expected results.Can throw MemberAccessException, ArgumentNullException, NotSupportedException and InvalidOperationException.</returns>
public async Task <PagingParameterOutModel <T> > GetQueryResultsAsync(PagingParameterInModel pageModel, HRSortingParamModel orderBy)
{
//1.1-
if (_repository == null || _paginer == null)
{
throw new MemberAccessException();
}
//1.2-
if (pageModel == null)
{
throw new ArgumentNullException();
}
//2-
IEnumerable <T> internalPagination = null;
//2.1-
if (orderBy != null && orderBy.IsInitialised())
{
//2.1.1-
if (_repository.IsSortable())
{
//2.1.1.1-
if (_repository.IsPaginable())
{
PagingParameterOutModel <T> retourPaginable = null;
using (Task <PagingParameterOutModel <T> > itemsTask = _repository.GetOrderedAndPaginatedsAsync(pageModel, orderBy))
{
await itemsTask;
retourPaginable = itemsTask.Result;
}
return(retourPaginable);
}
//2.1.1.2-
else
{
using (Task <IEnumerable <T> > taskForInternalPagination = _repository.GetOrderedsAsync(orderBy))
{
await taskForInternalPagination;
internalPagination = taskForInternalPagination.Result;
}
}
}
//2.1.2-
else
{
throw new NotSupportedException("Linq orderBy is not yet implemented.");
}
}
//2.2-
else
{
//2.2.1-
if (_repository.IsPaginable())
{
PagingParameterOutModel <T> retourPaginable = null;
using (Task <PagingParameterOutModel <T> > bordersTask = _repository.GetPaginatedsAsync(pageModel))
{
await bordersTask;
retourPaginable = bordersTask.Result;
}
return(retourPaginable);
}
//2.2.2-
else
{
using (Task <IEnumerable <T> > taskForInternalPagination = _repository.GetFullsAsync())
{
await taskForInternalPagination;
internalPagination = taskForInternalPagination.Result;
}
}
}
//3-
//3.1-
if (!_paginer.IsValid(pageModel, internalPagination))
{
throw new InvalidProgramException();
}
//3.2-
else
{
return(_paginer.GetPaginationFromFullList(pageModel, internalPagination, _maxPageSize));
}
}
/// <summary>
/// TODO
/// </summary>
/// <param name="pageModel"></param>
/// <param name="orderBy"></param>
/// <returns></returns>
public async Task <PagingParameterOutModel <HRCountry> > GetCountriesAsync(PagingParameterInModel pageModel, HRSortingParamModel orderBy)
{
if (_workflow == null)
{
throw new MemberAccessException();
}
Task <PagingParameterOutModel <HRCountry> > retourTask = _workflow.GetQueryResultsAsync(pageModel, orderBy);
await retourTask;
return(retourTask.Result);
}
public async Task <PagingParameterOutModel <HRBorder> > GetBordersAsync(PagingParameterInModel pageModel, HRSortingParamModel orderBy)
{
await Task.Delay(1);
if (ThrowException)
{
throw new Exception("");
}
PagingParameterOutModel <HRBorder> retour = new PagingParameterOutModel <HRBorder>()
{
PageItems = _list,
TotalItemsCount = (uint)_list.Count,
PageSize = pageModel.PageSize
};
return(retour);
}
public async Task <PagingParameterOutModel <HRBorder> > GetOrderedAndPaginatedBordersAsync(PagingParameterInModel pageModel, HRSortingParamModel orderBy)
{
orderBy.ToString();
await Task.Delay(1);
PagingParameterOutModel <HRBorder> retour = new PagingParameterOutModel <HRBorder>()
{
CurrentPage = pageModel.PageNumber,
PageItems = _borders,
PageSize = pageModel.PageSize,
TotalItemsCount = (uint)_borders.Count
};
return(retour);
}