|
public GetVariables ( 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<VariablesModel> GetVariables(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<VariablesModel>();
if (context.Variables.Count() != null)
{
totalRecordCount = context.Variables.Count();
searchRecordCount = totalRecordCount;
}
else
{
totalRecordCount = searchRecordCount = 0;
}
if (!string.IsNullOrWhiteSpace(searchString))
{
var allItems = context.Variables.ToList();
var rst = allItems.
Where(c =>
c.VariableCode != null && c.VariableCode.ToLower().Contains(searchString.ToLower())
|| c.VariableName != null && c.VariableName.ToLower().Contains(searchString.ToLower())
|| c.Speciation != null && c.Speciation.Contains(searchString.ToLower())
|| c.Unit != null && c.VariableUnitsID.ToString().Contains(searchString.ToLower())
|| c.SampleMedium != null && c.SampleMedium.ToLower().Contains(searchString.ToLower())
|| c.ValueType != null && c.ValueType.ToLower().Contains(searchString.ToLower())
|| c.IsRegular != null && c.IsRegular.ToString().ToLower().Contains(searchString.ToLower())
|| c.TimeSupport != null && c.TimeSupport.ToString().ToLower().Contains(searchString.ToLower())
|| c.Unit != null && c.Unit.UnitsName.ToLower().Contains(searchString.ToLower())
|| c.DataType != null && c.DataType.ToLower().Contains(searchString.ToLower())
|| c.GeneralCategory != null && c.GeneralCategory.ToLower().Contains(searchString.ToLower())
|| c.NoDataValue != null && c.NoDataValue.ToString().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)
searchRecordCount = rst.Count();
foreach (var item in rst)
{
var model = Mapper.Map<Variable, VariablesModel>(item);
model.VariableUnitsName = context.Units
.Where(a => a.UnitsID == item.VariableUnitsID)
.Select(a => a.UnitsName)
.FirstOrDefault();
model.TimeUnitsName = context.Units
.Where(a => a.UnitsID == item.TimeUnitsID)
.Select(a => a.UnitsName)
.FirstOrDefault();
result.Add(model);
}
}
else
{
List<Variable> sortedItems = null;
foreach (var sortedColumn in sortedColumns)
{
switch (sortedColumn.PropertyName.ToLower())
{
//case "0":
// if (sortedColumn.Direction.ToString().ToLower() == "ascending")
// { sortedItems = context.Variables.OrderBy(a => a.VariableID).Skip(startIndex).Take(pageSize).ToList(); }
// else
// { sortedItems = context.Variables.OrderByDescending(a => a.VariableCode).Skip(startIndex).Take(pageSize).ToList(); }
// break;
case "0":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Variables.OrderBy(a => a.VariableCode).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Variables.OrderByDescending(a => a.VariableCode).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "1":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Variables.OrderBy(a => a.VariableName).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Variables.OrderByDescending(a => a.VariableName).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "2":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Variables.OrderBy(a => a.Speciation).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Variables.OrderByDescending(a => a.Speciation).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "3":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Variables.OrderBy(a => a.Unit1.UnitsName).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Variables.OrderByDescending(a => a.Unit1.UnitsName).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "4":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Variables.OrderBy(a => a.SampleMedium).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Variables.OrderByDescending(a => a.SampleMedium).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "5":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Variables.OrderBy(a => a.ValueType).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Variables.OrderByDescending(a => a.ValueType).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "6":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Variables.OrderBy(a => a.IsRegular).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Variables.OrderByDescending(a => a.IsRegular).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "7":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Variables.OrderBy(a => a.TimeSupport).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Variables.OrderByDescending(a => a.TimeSupport).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "8":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Variables.OrderBy(a => a.Unit1.UnitsName).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Variables.OrderByDescending(a => a.Unit1.UnitsName).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "9":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Variables.OrderBy(a => a.DataType).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Variables.OrderByDescending(a => a.DataType).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "10":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Variables.OrderBy(a => a.GeneralCategory).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Variables.OrderByDescending(a => a.GeneralCategory).Skip(startIndex).Take(pageSize).ToList(); }
break;
case "11":
if (sortedColumn.Direction.ToString().ToLower() == "ascending")
{ sortedItems = context.Variables.OrderBy(a => a.NoDataValue).Skip(startIndex).Take(pageSize).ToList(); }
else
{ sortedItems = context.Variables.OrderByDescending(a => a.NoDataValue).Skip(startIndex).Take(pageSize).ToList(); }
break;
}
}
if (sortedItems == null) sortedItems = context.Variables.OrderByDescending(a => a.VariableCode).Skip(startIndex).Take(pageSize).ToList();
//map models
foreach (var item in sortedItems)
{
var model = Mapper.Map<Variable, VariablesModel>(item);
//model.LatLongDatumSRSName = from r in context.SpatialReferences
// where r.SpatialReferenceID == item.LatLongDatumID
// select r.SRSName.ToString()
// .FirstOrDefault();
model.VariableUnitsName = context.Units
.Where(a => a.UnitsID == item.VariableUnitsID)
.Select(a => a.UnitsName)
.FirstOrDefault();
model.TimeUnitsName = context.Units
.Where(a => a.UnitsID == item.TimeUnitsID)
.Select(a => a.UnitsName)
.FirstOrDefault();
result.Add(model);
}
}
return result;
}
public JsonResult VariablesSearch(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 repository = new VariablesRepository();
var items = repository.GetVariables(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.VariableID,
c.VariableCode,
c.VariableName,
c.Speciation,
c.VariableUnitsName,
c.SampleMedium,
c.ValueType,
c.IsRegular,
c.TimeSupport,
c.TimeUnitsName,
c.DataType,
c.GeneralCategory,
c.NoDataValue
};
return this.DataTablesJson(items: result,
totalRecords: totalRecordCount,
totalDisplayRecords: searchRecordCount,
sEcho: jQueryDataTablesModel.sEcho);
}
