public void HRDeserializerGetFieldOrdersWithNullModelExpectEmptyList()
{
IEnumerable <(String, String)> retour = HRSortingParamModelDeserializer.GetFieldOrders(null);
Assert.NotNull(retour);
Assert.NotNull(retour.GetEnumerator());
Assert.False(retour.GetEnumerator().MoveNext());
}
public void HRDeserializerGetFieldOrdersWitSortingParamsQueryEmptyExpectEmptyList()
{
IEnumerable <(String, String)> retour = HRSortingParamModelDeserializer.GetFieldOrders(new HRSortingParamModel()
{
OrderBy = String.Empty
});
Assert.NotNull(retour);
Assert.NotNull(retour.GetEnumerator());
Assert.False(retour.GetEnumerator().MoveNext());
}
/// <summary>
/// 1- Process PagingInParameter if not supplied
/// 2- Get the HRBorders from service
/// 3- Paginate previous result
/// !Strange we have to âss from query even for an "internal" method ... to untderstand.
/// </summary>
/// <param name="pageModel">The Paging Model</param>
/// <returns>(http Status Code, PagingParameterOutModel)</returns>
public async Task <(int, PagingParameterOutModel <HRBorder>)> GetFromPaging(
[FromQuery] PagingParameterInModel pageModel,
[FromQuery] HRSortingParamModel orderBy)
{
if (_borderService != null)
{
if (orderBy != null && orderBy.IsInitialised())
{
if (!_borderService.IsSortable())
{
return(StatusCodes.Status400BadRequest, null);
}
else if (!HRSortingParamModelDeserializer.IsValid(orderBy))
{
return(StatusCodes.Status400BadRequest, null);
}
}
//1-
if (pageModel == null)
{
pageModel = GetDefaultPagingInParameter();
}
//!Add tu on this
if (pageModel.PageSize > _maxPageSize)
{
return(StatusCodes.Status413PayloadTooLarge, null);
}
try
{
//2-
Task <PagingParameterOutModel <HRBorder> > bordersAction = _borderService.GetBordersAsync(pageModel, orderBy);
await bordersAction;
//3-
return(StatusCodes.Status200OK, bordersAction.Result);
}
catch (IndexOutOfRangeException)
{
//!Add tu on this
return(StatusCodes.Status416RequestedRangeNotSatisfiable, null);
}
catch (Exception)
{
return(StatusCodes.Status500InternalServerError, null);
}
}
else
{
return(StatusCodes.Status500InternalServerError, null);
}
}
public void HRDeserializerGetFieldOrdersWith1FieldAnd1OrderCompliantExpectReturnOK()
{
String query = "FIELD1;AsC";
IEnumerable <(String, String)> result = HRSortingParamModelDeserializer.GetFieldOrders(new HRSortingParamModel()
{
OrderBy = query
});
List <(String, String)> list = new List <(string, string)>(result);
Assert.NotNull(list);
Assert.True(list.Count == 1);
Assert.True(list[0].Item1 == "FIELD1");
Assert.True(list[0].Item2 == "ASC");
}
public void HRDeserializerGetFieldOrdersWitSortingParamsQueryWithLowerCaseOrderingArgumentExpectReturnOK()
{
String query = "FIELD1;AsC;FIELD2;DeSc";
IEnumerable <(String, String)> result = HRSortingParamModelDeserializer.GetFieldOrders(new HRSortingParamModel()
{
OrderBy = query
});
List <(String, String)> list = new List <(string, string)>(result);
Assert.NotNull(list);
Assert.True(list.Count == 2);
Assert.True(list[0].Item1 == "FIELD1");
Assert.True(list[0].Item2 == "ASC");
Assert.True(list[1].Item1 == "FIELD2");
Assert.True(list[1].Item2 == "DESC");
}
/// <summary>
/// Method use to know if all objects used have necessary skills to sort.
/// </summary>
/// <param name="orderBy">The ORderBy clause</param>
/// <param name="service">The ISortable servcice</param>
/// <returns></returns>
public bool CanOrder(HRSortingParamModel orderBy, ISortable service)
{
bool retour = true;
if (orderBy != null && orderBy.IsInitialised())
{
if (service != null)
{
if ((!service.IsSortable()) || !HRSortingParamModelDeserializer.IsValid(orderBy))
{
retour = false;
}
}
else
{
retour = false;
}
}
return(retour);
}
public void HRDeserializerGetFieldOrdersWitSortingParamsQueryWithWrongOrderingArgumentExpectArgumentOutOfRangeException()
{
String query = "FIELD1;ASC;FIELD2;DESK";
bool catched = false;
try
{
HRSortingParamModelDeserializer.GetFieldOrders(new HRSortingParamModel()
{
OrderBy = query
});
}
catch (ArgumentOutOfRangeException)
{
catched = true;
}
catch (Exception)
{
//Dummy.
}
Assert.True(catched);
}
/// <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());
}
