/// <summary>
/// Return all subactivities (level 2) that fullfill search criteria.
/// If activityCodes are null, all activities will be returned. If activityCodes is empty no activities will be returned.
/// Notice that this will include one subactivity "Not specified on this level" for each activity, even if the activity do not have any subactivities at all!
/// </summary>
public static IEnumerable <ActivityTreeListRow> GetSubActivities(PollutantTransfersSearchFilter filter, List <string> activityCodes)
{
if (activityCodes != null && activityCodes.Count() == 0)
{
return(new List <ActivityTreeListRow>());
}
DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext();
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = getActivityAreaLambda(filter);
//add activities to expression
if (activityCodes != null)
{
ParameterExpression param = lambda.Parameters[0];
Expression activityExp = LinqExpressionBuilder.GetInExpr(param, "IAActivityCode", activityCodes);
Expression exp = LinqExpressionBuilder.CombineAnd(lambda.Body, activityExp);
lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
}
//find data for sub-activity level, this level never has children.
IEnumerable <ActivityTreeListRow> subactivities = db.POLLUTANTTRANSFERs.Where(lambda)
.GroupBy(p => new { SectorCode = p.IASectorCode, ActivityCode = p.IAActivityCode, SubActivityCode = p.IASubActivityCode, PollutantCode = p.PollutantCode })
.Select(x => new ActivityTreeListRow(
x.Key.SectorCode,
x.Key.ActivityCode,
!x.Key.SubActivityCode.Equals(null) ? x.Key.SubActivityCode : ActivityTreeListRow.CODE_UNSPECIFIED,
x.Key.PollutantCode,
x.Count(),
x.Sum(p => p.Quantity),
false));
return(subactivities);
}
/// <summary>
/// return all activities (level 1) that fullfill search criteria within the sectorCodes given.
/// If sectorCodes are null, all activities will be returned. If sectorCodes is empty no activities will be returned.
/// </summary>
public static IEnumerable <ActivityTreeListRow> GetActivities(PollutantTransfersSearchFilter filter, List <string> sectorCodes)
{
if (sectorCodes != null && sectorCodes.Count() == 0)
{
return(new List <ActivityTreeListRow>());
}
DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext();
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = getActivityAreaLambda(filter);
if (sectorCodes != null)
{
ParameterExpression param = lambda.Parameters[0];
Expression sectorExp = LinqExpressionBuilder.GetInExpr(param, "IASectorCode", sectorCodes);
Expression exp = LinqExpressionBuilder.CombineAnd(lambda.Body, sectorExp);
lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
}
//find data for activity level
IEnumerable <ActivityTreeListRow> activities = db.POLLUTANTTRANSFERs.Where(lambda)
.GroupBy(p => new { SectorCode = p.IASectorCode, ActivityCode = p.IAActivityCode, PollutantCode = p.PollutantCode })
.Select(x => new ActivityTreeListRow(
x.Key.SectorCode,
x.Key.ActivityCode,
null,
x.Key.PollutantCode,
x.Count(),
x.Sum(p => p.Quantity),
x.First(p => !p.IASubActivityCode.Equals(null)) != null));
return(activities);
}
/// <summary>
/// return total list confidential reasons
/// </summary>
public static IEnumerable <TransfersConfidentialRow> GetConfidentialReason(PollutantTransfersSearchFilter filter)
{
DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext();
// apply filter for confidential, do not include the pollutant itself
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s");
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfersConfidential(filter, param, false);
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
var reason0 = db.POLLUTANTTRANSFERs.Where(lambda);
var reason1 = from p in reason0 group p by new { p.ConfidentialCode };
var reason2 = from p in reason1
select new
{
code = p.Select(x => x.ConfidentialCode).First(),
count = p.Count()
};
// build result
List <TransfersConfidentialRow> result = new List <TransfersConfidentialRow>();
foreach (var v in reason2)
{
result.Add(new TransfersConfidentialRow(v.code, v.count));
}
return(result.OrderBy(x => x.Code));
}
// ---------------------------------------------------------------------------------------------------
// Timeseries
// ---------------------------------------------------------------------------------------------------
#region Timeseries
/// <summary>
/// Get timeseries on aggregated level
/// </summary>
public static List <TimeSeriesClasses.PollutantTransfers> GetTimeSeries(PollutantTransferTimeSeriesFilter filter)
{
DataClassesPollutantTransferDataContext db = getDataContext();
// apply filter
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = getLambdaExpression(filter);
//get data from database
var data = db.POLLUTANTTRANSFERs.Where(lambda)
.GroupBy(p => p.ReportingYear)
.OrderBy(p => p.Key)
.Select(x => new {
Year = x.Key,
Quantity = x.Sum(p => p.Quantity),
Facilities = x.Count()
}).ToList();
//add information about no. of reporting countries
IEnumerable <Facility.ReportingCountries> years = Facility.GetReportingCountries(filter.AreaFilter).ToList();
IEnumerable <TimeSeriesClasses.PollutantTransfers> res = from l in data
join r in years on l.Year equals r.Year
select new TimeSeriesClasses.PollutantTransfers(
l.Year, l.Quantity, l.Facilities, r.Countries);
return(res.OrderBy(p => p.Year).ToList());
}
/// <summary>
/// return true if confidentiality might effect pollutant transfer result
/// </summary>
public static bool IsPollutantTransferAffectedByConfidentiality(IndustrialActivitySearchFilter filter)
{
DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext();
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = getPollutantTransfersConfidentialLambda(db, filter, false);
return(db.POLLUTANTTRANSFERs.Any(lambda));
}
// ---------------------------------------------------------------------------------------------------
// Data context
// ---------------------------------------------------------------------------------------------------
#region datacontext
/// <summary>
/// creates a new DataCotext and add logger
/// </summary>
private static DataClassesPollutantTransferDataContext getDataContext()
{
DataClassesPollutantTransferDataContext db = new DataClassesPollutantTransferDataContext();
db.Log = new DebuggerWriter();
return(db);
}
/// <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>
/// return total list confidential pollutants
/// </summary>
public static IEnumerable <TransfersConfidentialRow> GetConfidentialPollutant(PollutantTransfersSearchFilter filter)
{
DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext();
// apply filter for confidential, include pollutants
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s");
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfersConfidential(filter, param, true);
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
// sum up facilities
var pollutant0 = db.POLLUTANTTRANSFERs.Where(lambda);
var pollutant1 = from p in pollutant0 group p by new { p.PollutantCode };
var pollutant2 = from p in pollutant1
select new
{
code = p.Select(x => x.PollutantCode).First(),
count = p.Count()
};
// build result
List <TransfersConfidentialRow> result = new List <TransfersConfidentialRow>();
foreach (var v in pollutant2)
{
result.Add(new TransfersConfidentialRow(v.code, v.count));
}
return(result);
}
/// <summary>
/// return all countries that fullfill search criteria
/// </summary>
public static IEnumerable <AreaTreeListRow> GetCountries(PollutantTransfersSearchFilter filter)
{
DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext();
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = getActivityAreaLambda(filter);
//find data for country level. Country level always has children.
IEnumerable <AreaTreeListRow> countries = db.POLLUTANTTRANSFERs.Where(lambda)
.GroupBy(p => new { CountryCode = p.CountryCode, PollutantCode = p.PollutantCode })
.Select(x => new AreaTreeListRow(
x.Key.CountryCode,
null,
filter.AreaFilter.TypeRegion,
x.Key.PollutantCode,
x.Count(),
x.Sum(p => p.Quantity),
true));
List <AreaTreeListRow> result = countries.ToList(); //make sure sql is executed now to do it only once
List <AreaTreeListRow> totals = result.GroupBy(p => new { PollutantCode = p.PollutantCode })
.Select(x => new AreaTreeListRow(
AreaTreeListRow.CODE_TOTAL,
null,
filter.AreaFilter.TypeRegion,
x.Key.PollutantCode,
x.Sum(p => p.Facilities),
x.Sum(p => p.Quantity),
false)).ToList();
//only add total to result if more than one sector.
if (result.Select(r => r.CountryCode).Distinct().Count() > 1)
{
result.AddRange(totals);
}
//find facility count
int facilityCount = 0;
if (result.Count == 1)
{
facilityCount = result.Single().Facilities;
}
else if (totals.Count == 1)
{
facilityCount = totals.Single().Facilities;
}
else
{
facilityCount = GetFacilityCount(filter);
}
filter.Count = facilityCount;
return(result);
}
/// <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 string CODE_TNE = EnumUtil.GetStringValue(QuantityUnit.Tonnes);
// ---------------------------------------------------------------------------------------------------
// Comparison
// ---------------------------------------------------------------------------------------------------
#region comparison
/// <summary>
/// Returns data for comparison of two years corresponding to the filter given.
/// </summary>
/// <param name="filter"></param>
/// <param name="yearFrom"></param>
/// <param name="yearTo"></param>
/// <returns></returns>
public static TimeSeriesClasses.ComparisonPollutant GetComparisonTimeSeries(PollutantTransferTimeSeriesFilter filter, int yearFrom, int yearTo)
{
TimeSeriesClasses.ComparisonPollutant result = new TimeSeriesClasses.ComparisonPollutant(yearFrom, yearTo);
// Create lambda with pollutant release filter
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = getLambdaExpression(filter);
DataClassesPollutantTransferDataContext db = new DataClassesPollutantTransferDataContext();
// facilities
var dataFrom = db.POLLUTANTTRANSFERs.Where(lambda).Where(p => p.ReportingYear == yearFrom).GroupBy(p => p.ReportingYear)
.Select(p => new { count = p.Count(), quantity = p.Sum(x => x.Quantity) });
var dataTo = db.POLLUTANTTRANSFERs.Where(lambda).Where(p => p.ReportingYear == yearTo).GroupBy(p => p.ReportingYear)
.Select(p => new { count = p.Count(), quantity = p.Sum(x => x.Quantity) });
// reporting in both years
var vTo = db.POLLUTANTTRANSFERs.Where(lambda).Where(p => p.ReportingYear == yearTo).Select(p => p.FacilityID).Distinct();
var dataFromBoth = db.POLLUTANTTRANSFERs.Where(lambda)
.Where(p => p.ReportingYear == yearFrom && vTo.Contains(p.FacilityID)).GroupBy(p => p.ReportingYear)
.Select(p => new { count = p.Count(), quantity = p.Sum(x => x.Quantity) });
var vFrom = db.POLLUTANTTRANSFERs.Where(lambda).Where(p => p.ReportingYear == yearFrom).Select(p => p.FacilityID).Distinct();
var dataToBoth = db.POLLUTANTTRANSFERs.Where(lambda)
.Where(p => p.ReportingYear == yearTo && vFrom.Contains(p.FacilityID)).GroupBy(p => p.ReportingYear)
.Select(p => new { count = p.Count(), quantity = p.Sum(x => x.Quantity) });
var resFrom = dataFrom.SingleOrDefault();
if (resFrom != null)
{
result.FacilitiesFrom = resFrom.count;
result.QuantityFrom = resFrom.quantity;
}
var resTo = dataTo.SingleOrDefault();
if (resTo != null)
{
result.FacilitiesTo = resTo.count;
result.QuantityTo = resTo.quantity;
}
var resBothFrom = dataFromBoth.SingleOrDefault();
if (resBothFrom != null)
{
result.BothFacilities = resBothFrom.count;
result.BothQuantityFrom = resBothFrom.quantity;
}
var resBothTo = dataToBoth.SingleOrDefault();
if (resBothTo != null)
{
result.BothFacilities = resBothTo.count;
result.BothQuantityTo = resBothTo.quantity;
}
return(result);
}
/// <summary>
/// Find all reported pollutant codes fullfilling the filter
/// </summary>
public static List <string> GetPollutantCodes(PollutantTransfersSearchFilter filter)
{
DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext();
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = getActivityAreaLambda(filter);
//distinct pollutants
List <string> codes = db.POLLUTANTTRANSFERs.Where(lambda)
.Select(r => r.PollutantCode).Distinct().ToList();
return(codes);
}
/// <summary>
/// return true if confidentiality might effect result
/// </summary>
public static bool IsAffectedByConfidentiality(PollutantTransfersSearchFilter filter)
{
DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext();
// apply filter for confidential, do not include the pollutant itself
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s");
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfersConfidential(filter, param, false);
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
return(db.POLLUTANTTRANSFERs.Any(lambda));
}
// ---------------------------------------------------------------------------------------------------
// confidentiality
// ---------------------------------------------------------------------------------------------------
#region Confidentiality
/// <summary>
/// return true if confidentiality might effect result
/// </summary>
public static bool IsAffectedByConfidentiality(PollutantTransferTimeSeriesFilter filter)
{
//create new filter with confidential within group instead of pollutant itself
PollutantTransferTimeSeriesFilter filterConf = filter.Clone() as PollutantTransferTimeSeriesFilter;
filterConf.PollutantFilter.PollutantID = filter.PollutantFilter.PollutantGroupID;
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = getLambdaExpression(filterConf);
DataClassesPollutantTransferDataContext db = getDataContext();
return(db.POLLUTANTTRANSFERs.Any(lambda));
}
/// <summary>
/// Gets Confidential Transfers Reason
/// </summary>
/// <param name="filter"></param>
/// <returns>IEnumerable<ConfidentialTransfersRow></returns>
public static IEnumerable <ConfidentialTransfersRow> GetConfidentialTransfersReason(IndustrialActivitySearchFilter filter)
{
DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext();
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = getPollutantTransfersLambda(db, filter);
//reason = ConfidentialityID
var pollutantBase = db.POLLUTANTTRANSFERs.Where(lambda);
var pollutantConf = from c in pollutantBase where (c.ConfidentialIndicator != null && c.ConfidentialIndicator == true) select c;
// select distinct list of pollutnat codes
IEnumerable <string> pollutantGroupCodes = (from p in pollutantConf select p.PollutantGroupCode).Distinct();
// final result
List <ConfidentialTransfersRow> finaltmp = new List <ConfidentialTransfersRow>();
// Air count facilities
var air1 = from p in pollutantConf where (p.ConfidentialCode != null) group p by new { p.PollutantGroupCode, p.ConfidentialCode };
var air2 = from p in air1
select new
{
pollutantGroupCode = p.Select(x => x.PollutantGroupCode).First(),
reason = p.Select(x => x.ConfidentialCode).First(),
facilities = p.Count(x => x.Quantity != null && x.Quantity >= 0)
};
foreach (var v in air2)
{
finaltmp.Add(new ConfidentialTransfersRow(v.pollutantGroupCode, v.reason, v.facilities));
}
List <ConfidentialTransfersRow> final = new List <ConfidentialTransfersRow>();
var finalOrdered = finaltmp.OrderBy(x => x.PollutantCode);
string headCode = String.Empty;
foreach (var v in finalOrdered)
{
if (v.PollutantCode != headCode)
{
headCode = v.PollutantCode;
}
else
{
v.PollutantCode = String.Empty;
}
final.Add(v);
}
return(final);
}
/// <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);
}
/// <summary>
/// Get timeseries on facility level
/// </summary>
public static List <TimeSeriesClasses.PollutantTransfers> GetTimeSeries(int facilityid, string pollutantCode)
{
DataClassesPollutantTransferDataContext db = getDataContext();
// get data and group by year (which get assigned to x.Key by link)
IEnumerable <TimeSeriesClasses.PollutantTransfers> data = db.POLLUTANTTRANSFERs.Where(f => f.FacilityID == facilityid && f.PollutantCode == pollutantCode)
.GroupBy(p => p.ReportingYear)
.OrderBy(p => p.Key)
.Select(x => new TimeSeriesClasses.PollutantTransfers(
x.Key,
x.Sum(p => p.Quantity)));
return(data.ToList());
}
/// <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);
}
/// <summary>
/// get transfers
/// </summary>
private static IEnumerable <IATransfersTreeListRow> getTransfers(DataClassesPollutantTransferDataContext db, Expression <Func <POLLUTANTTRANSFER, bool> > lambda, out int facilitiesCount)
{
// count number of distinct facilities for this filter
facilitiesCount = db.POLLUTANTTRANSFERs.Where(lambda).Select(x => x.FacilityReportID).Distinct().Count();
//Count facilities per group (level = 0).
IEnumerable <IATransfersTreeListRow> groupsx = db.POLLUTANTTRANSFERs.Where(lambda)
.GroupBy(s => s.PollutantGroupCode)
.Select(v => new IATransfersTreeListRow(
v.Key,
v.Key,
v.Select(x => x.FacilityReportID).Distinct().Count(),
0,
0,
v.Where(x => x.ConfidentialIndicator == false).Select(x => x.PollutantCode).Distinct().Count(),
true)
);
//collect pollutants and aggregate data (level = 1).
IEnumerable <IATransfersTreeListRow> pollutantsx = db.POLLUTANTTRANSFERs.Where(lambda)
.GroupBy(s => new { s.PollutantGroupCode, s.PollutantCode })
.Select(v => new IATransfersTreeListRow(
v.Key.PollutantCode,
v.Key.PollutantGroupCode,
v.Count(),
v.Sum(x => x.Quantity),
1,
0,
false)
);
//create result with both groups and pollutants in.
IEnumerable <IATransfersTreeListRow> result = groupsx.Union(pollutantsx)
.OrderBy(s => s.GroupCode)
.ThenBy(s => s.Level)
.ThenBy(s => s.ConfidentialIndicator)
.ThenBy(s => s.Code);
return(result);
}
// ----------------------------------------------------------------------------------
// 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);
}
///<summary>
///Return all subareas (level 1) that fullfill search criteria.
///If countryCodes are null, all subareas will be returned. If countryCodes is empty no subareas will be returned.
///</summary>
public static IEnumerable <AreaTreeListRow> GetSubAreas(PollutantTransfersSearchFilter filter, List <string> countryCodes)
{
if (countryCodes != null && countryCodes.Count() == 0)
{
return(new List <AreaTreeListRow>());
}
DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext();
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = getActivityAreaLambda(filter);
//add countries to expression
if (countryCodes != null)
{
ParameterExpression param = lambda.Parameters[0];
Expression countryExp = LinqExpressionBuilder.GetInExpr(param, "CountryCode", countryCodes);
Expression exp = LinqExpressionBuilder.CombineAnd(lambda.Body, countryExp);
lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
}
//find data for sub-activity level, this level never has children.
AreaFilter.RegionType regionType = filter.AreaFilter.TypeRegion;
bool isRbd = AreaFilter.RegionType.RiverBasinDistrict.Equals(regionType);
IEnumerable <AreaTreeListRow> subareas = db.POLLUTANTTRANSFERs.Where(lambda)
.GroupBy(p => new { CountryCode = p.CountryCode, SubAreaCode = isRbd ? p.RiverBasinDistrictCode : p.NUTSLevel2RegionCode, PollutantCode = p.PollutantCode })
.Select(x => new AreaTreeListRow(
x.Key.CountryCode,
!x.Key.SubAreaCode.Equals(null) ? x.Key.SubAreaCode : AreaTreeListRow.CODE_UNKNOWN,
regionType,
x.Key.PollutantCode,
x.Count(),
x.Sum(p => p.Quantity),
false));
return(subareas);
}
// ----------------------------------------------------------------------------------
// 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);
}
/// <summary>
/// Get list for confidential transfers
/// </summary>
public static IEnumerable <ConfidentialTransfersRow> GetConfidentialTransfersFacility(IndustrialActivitySearchFilter filter)
{
DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext();
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = getPollutantTransfersLambda(db, filter);
var pollutantAll = db.POLLUTANTTRANSFERs.Where(lambda);
var pollutantConfidential = from c in pollutantAll where (c.ConfidentialIndicator != null && c.ConfidentialIndicator == true) select c;
// get list of confidential
var conf1 = from p in pollutantConfidential group p by new { p.PollutantGroupCode };
var conf2 = from p in conf1
select new
{
pollutantGroup = p.Select(x => x.PollutantGroupCode).First(),
facilities = p.Count(x => x.Quantity != null && x.Quantity >= 0),
};
// get list of not confidential (all)
var all1 = from p in pollutantAll group p by new { p.PollutantGroupCode };
var all2 = from p in all1
select new
{
pollutantGroup = p.Select(x => x.PollutantGroupCode).First(),
facilities = p.Count(x => x.Quantity != null && x.Quantity >= 0),
};
// merge results
List <ConfidentialTransfersRow> final = new List <ConfidentialTransfersRow>();
foreach (var v1 in conf2)
{
foreach (var v2 in all2)
{
if (v1.pollutantGroup.Equals(v2.pollutantGroup))
{
ConfidentialTransfersRow cr = new ConfidentialTransfersRow(v1.pollutantGroup);
cr.FacilitiesConfidential = v1.facilities; // set confidential
cr.Facilities = v2.facilities; // set not confidential (all)
final.Add(cr);
break;
}
}
}
return(final);
//var pollutantConfOn = from c in pollutantConfAll where (c.ConfidentialIndicator != null && c.ConfidentialIndicator == true) select c;
//var pollutantConfOff = from c in pollutantConfAll where c.ConfidentialIndicator == false select c;
// select distinct list of pollutnat codes
/*IEnumerable<string> pollutantcodes = (from p in pollutantConfAll select p.PollutantGroupCode).Distinct();
*
* // loop through all pollutants
* List<ConfidentialTransfersRow> final = new List<ConfidentialTransfersRow>();
* foreach (string s in pollutantcodes)
* {
* ConfidentialTransfersRow ct = new ConfidentialTransfersRow(s);
* ct.FacilitiesConfidential = (from c in pollutantConfOn where c.PollutantGroupCode == s && c.Quantity != null select c).Count();
* ct.Facilities = (from c in pollutantConfOff where c.PollutantGroupCode == s && c.Quantity != null select c).Count();
* // add confidential to the total count of facilities
* ct.Facilities += ct.FacilitiesConfidential;
* final.Add(ct);
* }
*
* return final;
*/
}
/// <summary>
/// get lambda for confidential pollutant transfers
/// </summary>
private static Expression <Func <POLLUTANTTRANSFER, bool> > getPollutantTransfersConfidentialLambda(DataClassesPollutantTransferDataContext db, IndustrialActivitySearchFilter filter, bool includePollutant)
{
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s");
PollutantTransfersSearchFilter filterTransfers = FilterConverter.ConvertToPollutantTransfersSearchFilter(filter);
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfersConfidential(filterTransfers, param, includePollutant);
Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
return(lambda);
}