/// <summary>
/// GetAreaComparison
/// </summary>
public static List <AreaComparison> GetAreaComparison(PollutantTransfersSearchFilter filter)
{
DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext();
// apply filter
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s");
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfers(filter, param);
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
AreaFilter areaFilter = filter.AreaFilter;
IEnumerable <AreaComparison> data = db.POLLUTANTTRANSFERs.Where(lambda)
.GroupBy(areaFilter)
.Select(x => new AreaComparison(
x.Key.Code,
x.Sum(p => p.Quantity),
x.Count()));
//Make sure sql is executed now and ordered by size
List <AreaComparison> result = data.OrderBy(p => p.Quantity).ToList();
//Calculate percentages
double tot = result.Sum(p => p.Quantity);
if (tot > 0)
{
foreach (AreaComparison ac in result)
{
ac.Percent = (ac.Quantity / tot) * 100.0;
}
}
return(result);
}
/// <summary>
/// create a lambda expression from the filter given
/// </summary>
private static Expression <Func <POLLUTANTTRANSFER, bool> > getLambdaExpression(PollutantTransferTimeSeriesFilter filter)
{
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s");
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfers(filter, param);
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
return(lambda);
}
/// <summary>
/// get lambda for pollutant transfers
/// </summary>
private static Expression <Func <POLLUTANTTRANSFER, bool> > getPollutantTransfersLambda(DataClassesPollutantTransferDataContext db, IndustrialActivitySearchFilter filter)
{
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s");
PollutantTransfersSearchFilter filterTransfers = FilterConverter.ConvertToPollutantTransfersSearchFilter(filter);
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfers(filterTransfers, param);
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
return(lambda);
}
/// <summary>
/// get lambda for pollutant transfers
/// </summary>
private static Expression <Func <POLLUTANTTRANSFER, bool> > getActivityAreaLambda(PollutantTransfersSearchFilter filter)
{
DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext();
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s");
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfers(filter, param);
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
return(lambda);
}
public static MapFilter GetMapJavascriptFilter(PollutantTransfersSearchFilter filter)
{
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s");
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfers(filter, param);
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
// create sql and sectors to map
MapFilter mapFilter = new MapFilter();
mapFilter.SqlWhere = LinqExpressionBuilder.GetSQLJavascript(exp, param);
mapFilter.SqlWhere = "FacilityReportID IN (select FacilityReportID from dbo.POLLUTANTTRANSFER where " + mapFilter.SqlWhere + ")";
return(mapFilter);
}
// ----------------------------------------------------------------------------------
// Map filters
// ----------------------------------------------------------------------------------
#region Map
/// <summary>
/// returns the MapFilter (sql and layers) corresponding to the filter.
/// </summary>
public static MapFilter GetMapFilter(PollutantTransfersSearchFilter filter)
{
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s");
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfers(filter, param);
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
// create sql and sectors to map
MapFilter mapFilter = new MapFilter();
mapFilter.SqlWhere = LinqExpressionBuilder.GetSQL(exp, param);
mapFilter.SetLayers(filter.ActivityFilter);
return(mapFilter);
}
/// <summary>
/// Generates facility list for Pollutant Transfer search results
/// </summary>
/// <param name="filter">Holds the search criteria</param>
/// <param name="sortColumn">Specifies the column used for sorting</param>
/// <param name="descending">Indicate whether sorting is descending</param>
/// <param name="startRowIndex">Starting index for paging</param>
/// <param name="p"></param>
/// <returns></returns>
public static object FacilityList(PollutantTransfersSearchFilter filter, string sortColumn, bool descending, int startRowIndex, int pagingSize)
{
// add rows (pageing)
List <PollutantTransfers.ResultFacility> result = new List <PollutantTransfers.ResultFacility>();
for (int i = 0; i < startRowIndex; i++)
{
result.Add(null);
}
// create expression
DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext();
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s");
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfers(filter, param);
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
// apply lambda
IEnumerable <POLLUTANTTRANSFER> data = db.POLLUTANTTRANSFERs.Where(lambda).orderBy(sortColumn, descending);
// set the number of found rows for this filter
filter.Count = data.Count();
data = data.Skip(startRowIndex).Take(pagingSize);
// default unit for Pollutant Transfers
foreach (var v in data)
{
QuantityUnit unit = (v.UnitCode == QuantityUnit.Tonnes.ToString()) ? QuantityUnit.Tonnes : QuantityUnit.Kilo;
result.Add(new PollutantTransfers.ResultFacility(
v.IAActivityCode,
v.FacilityName,
v.Quantity,
v.CountryCode,
v.FacilityReportID,
unit,
v.ConfidentialIndicatorFacility,
v.ConfidentialIndicator,
v.FacilityID));
}
// add rows (pageing)
int addcount = result.Count;
for (int i = 0; i < filter.Count - addcount; i++)
{
result.Add(null);
}
return(result);
}
// ----------------------------------------------------------------------------------
// Map filters
// ----------------------------------------------------------------------------------
#region Map
/// <summary>
/// returns the MapFilter (sql and layers) corresponding to the filter.
/// </summary>
public static MapFilter GetMapFilter(PollutantTransferTimeSeriesFilter filter)
{
//parameter must be "p" to match map config file
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "p");
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfers(filter, param);
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
// create sql and sectors to map. Do not remove parameter prefix.
MapFilter mapFilter = new MapFilter();
mapFilter.SqlWhere = LinqExpressionBuilder.GetSQL(lambda.Body, null);
mapFilter.SetLayers(filter.ActivityFilter);
return(mapFilter);
}
/// <summary>
/// Returns the number of facilities corresponding to the filter. Always use POLLUTANTTRANSFER table, since it has the fewest records.
/// </summary>
public static int GetFacilityCount(PollutantTransfersSearchFilter filter)
{
DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext();
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s");
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfers(filter, param);
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
//find total no. of facilities.
int count = db.POLLUTANTTRANSFERs
.Where(lambda)
.Select <POLLUTANTTRANSFER, int>(d => (int)d.FacilityReportID).Distinct()
.Count();
return(count);
}
public static IEnumerable <IATransfersTreeListRow> GetPollutantTransfers(IndustrialActivitySearchFilter filter)
{
DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext();
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s");
PollutantTransfersSearchFilter filterTransfer = FilterConverter.ConvertToPollutantTransfersSearchFilter(filter);
filterTransfer.ActivityFilter = filter.ActivityFilter;
filterTransfer.AreaFilter = filter.AreaFilter;
filterTransfer.YearFilter = filter.YearFilter;
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfers(filterTransfer, param);
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
int facilitiesCount = 0;
List <IATransfersTreeListRow> pollutants = getTransfers(db, lambda, out facilitiesCount).ToList <IATransfersTreeListRow>();
filter.Count = facilitiesCount;
return(pollutants);
}
// ----------------------------------------------------------------------------------
// Summery
// ----------------------------------------------------------------------------------
#region summery
/// <summary>
/// Summery
/// </summary>
public static List <Summary.Quantity> Summery(PollutantTransfersSearchFilter filter)
{
DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext();
// apply filter
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s");
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfers(filter, param);
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
var summeryFiltered = db.POLLUTANTTRANSFERs.Where(lambda);
var summery0 = from s in summeryFiltered select new { s.IAActivityCode, s.Quantity };
var summery1 = from s in summery0 group s by new { s.IAActivityCode };
var summery2 = from s in summery1
select new
{
code = s.Select(x => x.IAActivityCode).First(),
count = s.Count(),
sumQuantity = s.Sum(x => x.Quantity)
};
double total = 0;
filter.Count = 0;
List <Summary.Quantity> final = new List <Summary.Quantity>();
foreach (var v in summery2)
{
if (v.sumQuantity > 0)
{
final.Add(new Summary.Quantity(v.code, v.sumQuantity));
total += v.sumQuantity;
filter.Count += v.count;
}
}
// Get top 10 list, sorted by quantity
final = Summary.GetTop10(final, total);
return(final);
}
// ----------------------------------------------------------------------------------
// Facilities - CSV
// ----------------------------------------------------------------------------------
#region Facilities - CSV
public static IEnumerable <PollutantTransfers.ResultFacilityCSV> GetFacilityListCSV(PollutantTransfersSearchFilter filter)
{
List <PollutantTransfers.ResultFacilityCSV> result = new List <PollutantTransfers.ResultFacilityCSV>();
// create expression
DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext();
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s");
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfers(filter, param);
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
// apply lambda
var data = db.POLLUTANTTRANSFERs.Where(lambda).OrderBy(x => x.FacilityName);
// default unit for Pollutant Transfers
foreach (var v in data)
{
QuantityUnit unit = (v.UnitCode == QuantityUnit.Tonnes.ToString()) ? QuantityUnit.Tonnes : QuantityUnit.Kilo;
var row = new PollutantTransfers.ResultFacilityCSV(
v.IAActivityCode,
v.FacilityName,
v.Quantity,
v.CountryCode,
v.FacilityReportID,
unit,
v.ConfidentialIndicatorFacility,
v.ConfidentialIndicator,
v.FacilityID,
v.MethodCode,
v.MethodTypeCode,
v.MethodTypeDesignation);
result.Add(row);
}
return(result);
}
