//public string GetCountByZip()
//{
// var getPostalCodeKeySql = new Sql();
// getPostalCodeKeySql.Append("SELECT [Key] FROM uLocate_LocationTypeProperty WHERE Alias = 'PostalCode'");
// var result = Repositories.ThisDb.Fetch<PostalCodeAlias>(getPostalCodeKeySql);
// var key = result[0].Key;
// var sql = new Sql();
// sql.Append("SELECT dataNvarchar as postalCode, count (dataNvarchar) as locationCount FROM uLocate_LocationPropertyData WHERE LocationTypePropertyKey = '" + key + "' GROUP BY dataNvarchar");
// var finalResult = Repositories.ThisDb.Fetch<string>(sql).ToString();
// return finalResult;
//}
public List <JsonLocation> GetPaged(long PageNumber, long ItemsPerPage, string WhereClause)
{
Sql sql = new Sql();
sql.Append(
"SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY [Key]) AS Num, * FROM uLocate_Location) AS uLocateTable");
sql.Append(
"WHERE Num BETWEEN ((@PageNumber - 1) * @RowspPage + 1) AND (@PageNumber * @RowspPage)",
new { PageNumber = PageNumber, RowspPage = ItemsPerPage });
sql.Append("ORDER BY [Key];");
CurrentCollection.Clear();
var dtoResult = Repositories.ThisDb.Fetch <LocationDto>(sql).ToList();
var converter = new DtoConverter();
CurrentCollection.AddRange(converter.ToLocationEntity(dtoResult));
FillChildren();
var ReturnList = new List <JsonLocation>();
foreach (var loc in CurrentCollection)
{
ReturnList.Add(new JsonLocation(loc));
}
return(ReturnList);
}
protected override IEnumerable <Location> PerformGetAll(params Guid[] Keys)
{
//TODO: Fix this - use cache + Dto
List <Location> Result = new List <Location>();
IEnumerable <LocationDto> dtoResults;
if (Keys.Any())
{
foreach (var key in Keys)
{
Result.Add(Get(key));
}
}
else
{
var sql = new Sql();
sql.Select("*").From <LocationDto>();
dtoResults = Repositories.ThisDb.Fetch <LocationDto>(sql).ToList();
var converter = new DtoConverter();
foreach (var result in dtoResults)
{
Result.Add(converter.ToLocationEntity(result));
}
}
return(Result);
}
internal IEnumerable <Location> GetByCustomQuery(Sql SqlQuery)
{
CurrentCollection.Clear();
var dtoResult = Repositories.ThisDb.Query <LocationDto>(SqlQuery).ToList();
var converter = new DtoConverter();
CurrentCollection.AddRange(converter.ToLocationEntity(dtoResult));
FillChildren();
return(CurrentCollection);
}
internal IEnumerable <Location> GetNearestLocations(double SearchLat, double SearchLong, int QuantityReturned, Guid FilterByLocationTypeKey)
{
CurrentCollection.Clear();
var sql = new Sql();
sql.Select(string.Format("TOP({0}) *", QuantityReturned))
.From <LocationDto>()
.Append(GeographyHelper.GetGeoNearestSql(SearchLat, SearchLong, FilterByLocationTypeKey));
var dtoResult = Repositories.ThisDb.Query <LocationDto>(sql).ToList();
var converter = new DtoConverter();
CurrentCollection.AddRange(converter.ToLocationEntity(dtoResult));
FillChildren();
return(CurrentCollection);
}
internal IEnumerable <Location> GetByGeoSearch(double SearchLat, double SearchLong, int MilesDistance, Guid FilterByLocationTypeKey)
{
CurrentCollection.Clear();
var sql = new Sql();
sql.Select("*")
.From <LocationDto>()
.Append(GeographyHelper.GetGeoSearchSql(SearchLat, SearchLong, MilesDistance, FilterByLocationTypeKey));
var dtoResult = Repositories.ThisDb.Query <LocationDto>(sql).ToList();
var converter = new DtoConverter();
CurrentCollection.AddRange(converter.ToLocationEntity(dtoResult));
FillChildren();
return(CurrentCollection);
}
internal IEnumerable <Location> GetByType(Guid LocationTypeKey)
{
CurrentCollection.Clear();
var sql = new Sql();
sql.Select("*")
.From <LocationDto>()
.Where <LocationDto>(n => n.LocationTypeKey == LocationTypeKey);
var dtoResult = Repositories.ThisDb.Fetch <LocationDto>(sql).ToList();
var converter = new DtoConverter();
CurrentCollection.AddRange(converter.ToLocationEntity(dtoResult));
FillChildren();
return(CurrentCollection);
}
public IEnumerable <Location> GetByName(string LocationName)
{
CurrentCollection.Clear();
var sql = new Sql();
sql.Select("*")
.From <LocationDto>()
.Where <LocationDto>(n => n.Name == LocationName);
var dtoResult = Repositories.ThisDb.Fetch <LocationDto>(sql).ToList();
var converter = new DtoConverter();
CurrentCollection.AddRange(converter.ToLocationEntity(dtoResult));
FillChildren();
return(CurrentCollection);
}
protected override Location PerformGet(Guid Key)
{
var sql = new Sql();
sql
.Select("*")
.From <LocationDto>()
.Where <LocationDto>(n => n.Key == Key);
var dtoResult = Repositories.ThisDb.Fetch <LocationDto>(sql).FirstOrDefault();
if (dtoResult == null)
{
return(null);
}
var converter = new DtoConverter();
var entity = converter.ToLocationEntity(dtoResult);
return(entity);
}
