|
public GetSites ( string connectionString, int startIndex, int pageSize, System |
||
| connectionString | string | |
| startIndex | int | |
| pageSize | int | |
| sortedColumns | System |
|
| totalRecordCount | int | |
| searchRecordCount | int | |
| searchString | string | |
| return | List |
|
public List<SiteModel> GetSites(string connectionString, int startIndex, int pageSize, System.Collections.ObjectModel.ReadOnlyCollection<jQuery.DataTables.Mvc.SortedColumn> sortedColumns, out int totalRecordCount, out int searchRecordCount, string searchString)
{
var context = new ODM_1_1_1EFModel.ODM_1_1_1Entities(connectionString);
var result = new List<SiteModel>();
if (context.Sites.Count() != 0)
{
totalRecordCount = context.Sites.Count();
searchRecordCount = totalRecordCount;
}
else
{
totalRecordCount = searchRecordCount = 0;
}
//var test = DatatablesHelper.FilterSitesTable(context, searchString, pageSize);
if (!string.IsNullOrWhiteSpace(searchString))
{
var allItems = context.Sites.ToList();
var rst = allItems.
Where(c =>
c.SiteCode != null && c.SiteCode.ToLower().Contains(searchString.ToLower())
|| c.SiteName != null && c.SiteName.ToLower().Contains(searchString.ToLower())
|| c.Latitude.ToString().ToLower().Contains(searchString.ToLower())
|| c.Longitude.ToString().ToLower().Contains(searchString.ToLower())
|| c.SpatialReference.SRSName != null && c.SpatialReference.SRSName.ToLower().Contains(searchString.ToLower())
|| c.Elevation_m != null && c.Elevation_m.ToString().ToLower().Contains(searchString.ToLower())
|| c.LocalX != null && c.LocalX.ToString().ToLower().Contains(searchString.ToLower())
|| c.LocalY != null && c.LocalY.ToString().ToLower().Contains(searchString.ToLower())
|| c.VerticalDatum != null && c.VerticalDatum.ToString().ToLower().Contains(searchString.ToLower())
//|| c.LocalProjectionID != null && c.LocalProjectionID.ToString().Contains(searchString.ToLower())
|| c.PosAccuracy_m != null && c.PosAccuracy_m.ToString().ToLower().Contains(searchString.ToLower())
|| c.State != null && c.State.ToLower().Contains(searchString.ToLower())
|| c.County != null && c.County.ToLower().Contains(searchString.ToLower())
|| c.Comments != null && c.Comments.ToLower().Contains(searchString.ToLower())
|| c.SiteType != null && c.SiteType.ToLower().Contains(searchString.ToLower())
);
if (rst == null) return result;
//count
searchRecordCount = rst.Count();
//take only top x
var finalrst = rst.Take(pageSize).ToList();
foreach (var item in finalrst)
{
var model = Mapper.Map<Site, SiteModel>(item);
model.LatLongDatumSRSName = context.SpatialReferences
.Where(a => a.SpatialReferenceID == item.LatLongDatumID)
.Select(a => a.SRSName)
.FirstOrDefault();
result.Add(model);
}
}
else
{
List<Site> sortedItems = null;
foreach (var sortedColumn in sortedColumns)
{
switch (sortedColumn.PropertyName.ToLower())
{
case "0":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Sites.OrderBy(a => a.SiteCode).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Sites.OrderByDescending(a => a.SiteCode).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "1":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Sites.OrderBy(a => a.SiteName).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Sites.OrderByDescending(a => a.SiteName).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "2":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Sites.OrderBy(a => a.Latitude).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Sites.OrderByDescending(a => a.Latitude).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "3":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Sites.OrderBy(a => a.Longitude).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Sites.OrderByDescending(a => a.Longitude).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "4":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Sites.OrderBy(a => a.SpatialReference.SRSName).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Sites.OrderByDescending(a => a.SpatialReference.SRSName).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "5":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Sites.OrderBy(a => a.Elevation_m).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Sites.OrderByDescending(a => a.Elevation_m).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "6":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Sites.OrderBy(a => a.VerticalDatum).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Sites.OrderByDescending(a => a.VerticalDatum).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "7":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Sites.OrderBy(a => a.LocalX).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Sites.OrderByDescending(a => a.LocalX).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "8":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Sites.OrderBy(a => a.LocalY).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Sites.OrderByDescending(a => a.LocalY).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "9":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Sites.OrderBy(a => a.LocalProjectionID).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Sites.OrderByDescending(a => a.LocalProjectionID).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "10":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Sites.OrderBy(a => a.PosAccuracy_m).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Sites.OrderByDescending(a => a.PosAccuracy_m).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "11":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Sites.OrderBy(a => a.State).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Sites.OrderByDescending(a => a.State).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "12":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Sites.OrderBy(a => a.County).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Sites.OrderByDescending(a => a.County).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "13":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Sites.OrderBy(a => a.Comments).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Sites.OrderByDescending(a => a.Comments).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "14":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Sites.OrderBy(a => a.SiteType).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Sites.OrderByDescending(a => a.SiteType).Skip(startIndex).Take(pageSize).ToList(); }
break;
}
}
if (sortedItems == null) sortedItems = context.Sites.OrderByDescending(a => a.SiteCode).Skip(startIndex).Take(pageSize).ToList();
//map models
foreach (var item in sortedItems)
{
var model = Mapper.Map<Site, SiteModel>(item);
//model.LatLongDatumSRSName = from r in context.SpatialReferences
// where r.SpatialReferenceID == item.LatLongDatumID
// select r.SRSName.ToString()
// .FirstOrDefault();
model.LatLongDatumSRSName = context.SpatialReferences
.Where(a => a.SpatialReferenceID == item.LatLongDatumID)
.Select(a => a.SRSName)
.FirstOrDefault();
result.Add(model);
}
}
return result;
}
public JsonResult Search(JQueryDataTablesModel jQueryDataTablesModel, string identifier)
{
int totalRecordCount;
int searchRecordCount;
//Get Connection string
var entityConnectionString = HydroServerToolsUtils.BuildConnectionStringForUserName(HttpContext.User.Identity.Name);
//var entityConnectionString = HydroServerToolsUtils.GetDBEntityConnectionStringByName(connectionName);
if (String.IsNullOrEmpty(entityConnectionString))
{
ModelState.AddModelError(String.Empty, Ressources.HYDROSERVER_USERLOOKUP_FAILED);
}
var sitesRepository = new SitesRepository();
var items = sitesRepository.GetSites(entityConnectionString, startIndex: jQueryDataTablesModel.iDisplayStart,
pageSize: jQueryDataTablesModel.iDisplayLength, sortedColumns: jQueryDataTablesModel.GetSortedColumns(),
totalRecordCount: out totalRecordCount, searchRecordCount: out searchRecordCount, searchString: jQueryDataTablesModel.sSearch);
var result = from c in items
select new[] {
c.SiteCode,
c.SiteName,
c.Latitude,
c.Longitude,
c.LatLongDatumSRSName,
c.Elevation_m,
c.VerticalDatum,
c.LocalX,
c.LocalY,
c.LocalProjectionSRSName,
c.PosAccuracy_m,
c.State,
c.County,
c.Comments,
c.SiteType
};
return this.DataTablesJson(items: result,
totalRecords: totalRecordCount,
totalDisplayRecords: searchRecordCount,
sEcho: jQueryDataTablesModel.sEcho);
}
