/// <summary>
/// return true if confidentiality might effect pollutant release result
/// </summary>
public static bool IsPollutantReleaseAffectedByConfidentiality(IndustrialActivitySearchFilter filter)
{
DataClassesPollutantReleaseDataContext db = getPollutantReleaseDataContext();
Expression <Func <POLLUTANTRELEASE, bool> > lambda = getPollutantReleasesConfidentialLambda(db, filter, false);
return(db.POLLUTANTRELEASEs.Any(lambda));
}
private static DataClassesPollutantReleaseDataContext getPollutantReleaseDataContext()
{
DataClassesPollutantReleaseDataContext db = new DataClassesPollutantReleaseDataContext();
db.Log = new DebuggerWriter();
return(db);
}
/// <summary>
/// Get timeseries on facility level
/// </summary>
public static List <TimeSeriesClasses.PollutantReleases> GetTimeSeries(int facilityid, string pollutantCode, MediumFilter.Medium medium)
{
DataClassesPollutantReleaseDataContext db = getDataContext();
//apply medium condition
Expression <Func <POLLUTANTRELEASE, bool> > lambda = getLambdaExpression(medium);
// get data and group by year (which get assigned to x.Key by link)
IQueryable <IGrouping <int, POLLUTANTRELEASE> > group = db.POLLUTANTRELEASEs.Where(f => f.FacilityID == facilityid && f.PollutantCode == pollutantCode).Where(lambda).GroupBy(p => p.ReportingYear);
// lookup medium
IEnumerable <TimeSeriesClasses.PollutantReleases> data = null;
switch (medium)
{
case MediumFilter.Medium.Air:
data = group.Select(x => new TimeSeriesClasses.PollutantReleases(x.Key, x.Count(), x.Sum(p => p.QuantityAir), x.Sum(p => p.QuantityAccidentalAir)));
break;
case MediumFilter.Medium.Soil:
data = group.Select(x => new TimeSeriesClasses.PollutantReleases(x.Key, x.Count(), x.Sum(p => p.QuantitySoil), x.Sum(p => p.QuantityAccidentalSoil)));
break;
case MediumFilter.Medium.Water:
data = group.Select(x => new TimeSeriesClasses.PollutantReleases(x.Key, x.Count(), x.Sum(p => p.QuantityWater), x.Sum(p => p.QuantityAccidentalWater)));
break;
default:
throw new ArgumentOutOfRangeException("medium", String.Format("Illegal medium: {0}", medium.ToString()));
}
return(data.OrderBy(p => p.Year).ToList());
}
/// <summary>
/// return confidential realeases for facilities
/// </summary>
public static IEnumerable <ConfidentialReleasesRow> GetConfidentialReleasesFacility(IndustrialActivitySearchFilter filter)
{
DataClassesPollutantReleaseDataContext db = getPollutantReleaseDataContext();
Expression <Func <POLLUTANTRELEASE, bool> > lambda = getPollutantReleasesLambda(db, filter);
var pollutantAll = db.POLLUTANTRELEASEs.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(),
countAir = p.Count(x => x.QuantityAir != null && x.QuantityAir >= 0),
countWater = p.Count(x => x.QuantityWater != null && x.QuantityWater >= 0),
countSoil = p.Count(x => x.QuantitySoil != null && x.QuantitySoil >= 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(),
countAir = p.Count(x => x.QuantityAir != null && x.QuantityAir >= 0),
countWater = p.Count(x => x.QuantityWater != null && x.QuantityWater >= 0),
countSoil = p.Count(x => x.QuantitySoil != null && x.QuantitySoil >= 0)
};
// merge results
List <ConfidentialReleasesRow> final = new List <ConfidentialReleasesRow>();
foreach (var v1 in conf2)
{
foreach (var v2 in all2)
{
if (v1.pollutantGroup.Equals(v2.pollutantGroup))
{
ConfidentialReleasesRow cr = new ConfidentialReleasesRow(v1.pollutantGroup);
// set confidential
cr.FacilitiesAirConfidential = v1.countAir;
cr.FacilitiesWaterConfidential = v1.countWater;
cr.FacilitiesSoilConfidential = v1.countSoil;
// set not confidential (all)
cr.FacilitiesAir = v2.countAir;
cr.FacilitiesWater = v2.countWater;
cr.FacilitiesSoil = v2.countSoil;
final.Add(cr);
break;
}
}
}
return(final);
}
/// <summary>
/// return true if confidentiality might effect result
/// </summary>
public static bool IsAffectedByConfidentiality(PollutantReleasesTimeSeriesFilter filter, MediumFilter.Medium medium)
{
//create new filter with confidential within group instead of pollutant itself
PollutantReleasesTimeSeriesFilter filterConf = createConfidentialFilter(filter);
Expression <Func <POLLUTANTRELEASE, bool> > lambda = getLambdaExpression(filterConf, medium);
DataClassesPollutantReleaseDataContext db = getDataContext();
return(db.POLLUTANTRELEASEs.Any(lambda));
}
/// <summary>
/// get transfers
/// </summary>
private static IEnumerable <IAReleasesTreeListRow> getReleases(DataClassesPollutantReleaseDataContext db, Expression <Func <POLLUTANTRELEASE, bool> > lambda, out int facilitiesCount)
{
// count number of distinct facilities for this filter
facilitiesCount = db.POLLUTANTRELEASEs.Where(lambda).Select(x => x.FacilityReportID).Distinct().Count();
//Count facilities per group (level = 0).
IEnumerable <IAReleasesTreeListRow> groupsx = db.POLLUTANTRELEASEs.Where(lambda)
.GroupBy(s => s.PollutantGroupCode)
.Select(v => new IAReleasesTreeListRow(
v.Key,
v.Key,
v.Select(x => x.FacilityReportID).Distinct().Count(),
0, // Value never used, for this Level
null, null, null, null, null, null,
0,
v.Where(x => x.ConfidentialIndicator == false).Select(x => x.PollutantCode).Distinct().Count(),
true)
);
//collect pollutants and aggregate data (level = 1).
IEnumerable <IAReleasesTreeListRow> pollutantsx = db.POLLUTANTRELEASEs.Where(lambda)
.GroupBy(s => new { s.PollutantGroupCode, s.PollutantCode })
.Select(v => new IAReleasesTreeListRow(
v.Key.PollutantCode,
v.Key.PollutantGroupCode,
v.Count(),
v.Count(z => z.QuantityAccidentalAir > 0 || z.QuantityAccidentalSoil > 0 || z.QuantityAccidentalWater > 0),
v.Sum(x => x.QuantityAir),
v.Sum(x => x.QuantityAccidentalAir),
v.Sum(x => x.QuantityWater),
v.Sum(x => x.QuantityAccidentalWater),
v.Sum(x => x.QuantitySoil),
v.Sum(x => x.QuantityAccidentalSoil),
1, 0, false)
);
//create result with both groups and pollutants in.
IEnumerable <IAReleasesTreeListRow> result = groupsx.Union(pollutantsx)
.OrderBy(s => s.GroupCode)
.ThenBy(s => s.Level)
.ThenBy(s => s.ConfidentialIndicator)
.ThenBy(s => s.Code);
return(result);
}
/// <summary>
/// return total list of areas
/// </summary>
public static IEnumerable <IAReleasesTreeListRow> GetPollutantReleases(IndustrialActivitySearchFilter filter)
{
DataClassesPollutantReleaseDataContext db = getPollutantReleaseDataContext();
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTRELEASE), "s");
PollutantReleaseSearchFilter filterRelease = FilterConverter.ConvertToPollutantReleaseSearchFilter(filter);
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantReleases(filterRelease, param);
Expression <Func <POLLUTANTRELEASE, bool> > lambda = Expression.Lambda <Func <POLLUTANTRELEASE, bool> >(exp, param);
int facilitiesCount = 0;
List <IAReleasesTreeListRow> pollutants = getReleases(db, lambda, out facilitiesCount).ToList <IAReleasesTreeListRow>();
filter.Count = facilitiesCount;
return(pollutants);
}
//public static string CODE_TNE = EnumUtil.GetStringValue(QuantityUnit.Tonnes);
// ---------------------------------------------------------------------------------------------------
// Time series
// ---------------------------------------------------------------------------------------------------
#region timeseries
/// <summary>
/// Get timeseries on aggregated level
/// </summary>
public static List <TimeSeriesClasses.PollutantReleases> GetTimeSeries(PollutantReleasesTimeSeriesFilter filter, MediumFilter.Medium medium)
{
// apply filter
Expression <Func <POLLUTANTRELEASE, bool> > lambda = getLambdaExpression(filter, medium);
DataClassesPollutantReleaseDataContext db = getDataContext();
// get data and group by year (which get assigned to x.Key by link)
IQueryable <IGrouping <int, POLLUTANTRELEASE> > group = db.POLLUTANTRELEASEs.Where(lambda).GroupBy(p => p.ReportingYear).OrderBy(p => p.Key);
// lookup medium
IEnumerable <TimeSeriesClasses.PollutantReleases> data = null;
switch (medium)
{
case MediumFilter.Medium.Air:
data = group.Select(x => new TimeSeriesClasses.PollutantReleases(x.Key, x.Count(), x.Sum(p => p.QuantityAir), x.Sum(p => p.QuantityAccidentalAir)));
break;
case MediumFilter.Medium.Soil:
data = group.Select(x => new TimeSeriesClasses.PollutantReleases(x.Key, x.Count(), x.Sum(p => p.QuantitySoil), x.Sum(p => p.QuantityAccidentalSoil)));
break;
case MediumFilter.Medium.Water:
data = group.Select(x => new TimeSeriesClasses.PollutantReleases(x.Key, x.Count(), x.Sum(p => p.QuantityWater), x.Sum(p => p.QuantityAccidentalWater)));
break;
default:
throw new ArgumentOutOfRangeException("medium", String.Format("Illegal medium: {0}", medium.ToString()));
}
IEnumerable <Facility.ReportingCountries> years = Facility.GetReportingCountries(filter.AreaFilter).ToList();
IEnumerable <TimeSeriesClasses.PollutantReleases> res = from l in data.ToList()
join r in years on l.Year equals r.Year
select new TimeSeriesClasses.PollutantReleases(l.Year, l.Facilities, l.Quantity, l.QuantityAccidental, r.Countries);
return(res.OrderBy(p => p.Year).ToList());
}
/// <summary>
/// return confidential realeases for reason
/// </summary>
public static IEnumerable <ConfidentialReleasesRow> GetConfidentialReleasesReason(IndustrialActivitySearchFilter filter)
{
DataClassesPollutantReleaseDataContext db = getPollutantReleaseDataContext();
Expression <Func <POLLUTANTRELEASE, bool> > lambda = getPollutantReleasesLambda(db, filter);
//reason = ConfidentialityID
var pollutantBase = db.POLLUTANTRELEASEs.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 <ConfidentialReleasesRow> finalAir = new List <ConfidentialReleasesRow>();
List <ConfidentialReleasesRow> finalWater = new List <ConfidentialReleasesRow>();
List <ConfidentialReleasesRow> finalSoil = new List <ConfidentialReleasesRow>();
// Air count facilities
var air1 = from p in pollutantConf where (p.ConfidentialCodeAir != null) group p by new { p.PollutantGroupCode, p.ConfidentialCodeAir };
var air2 = from p in air1
select new
{
pollutantGroupCode = p.Select(x => x.PollutantGroupCode).First(),
reason = p.Select(x => x.ConfidentialCodeAir).First(),
countAir = p.Count(x => x.QuantityAir != null && x.QuantityAir >= 0)
};
foreach (var v in air2)
{
finalAir.Add(new ConfidentialReleasesRow(v.pollutantGroupCode, v.reason, v.countAir, 0, 0));
}
// Water count facilities
var water1 = from p in pollutantConf where (p.ConfidentialCodeWater != null) group p by new { p.PollutantGroupCode, p.ConfidentialCodeWater };
var water2 = from p in water1
select new
{
pollutantGroupCode = p.Select(x => x.PollutantGroupCode).First(),
reason = p.Select(x => x.ConfidentialCodeWater).First(),
countWater = p.Count(x => x.QuantityWater != null && x.QuantityWater >= 0)
};
foreach (var v in water2)
{
finalWater.Add(new ConfidentialReleasesRow(v.pollutantGroupCode, v.reason, 0, v.countWater, 0));
}
// Soil count facilities
var soil1 = from p in pollutantConf where (p.ConfidentialCodeSoil != null) group p by new { p.PollutantGroupCode, p.ConfidentialCodeSoil };
var soil2 = from p in soil1
select new
{
pollutantGroupCode = p.Select(x => x.PollutantGroupCode).First(),
reason = p.Select(x => x.ConfidentialCodeSoil).First(),
countSoil = p.Count(x => x.QuantitySoil != null && x.QuantitySoil >= 0)
};
foreach (var v in soil2)
{
finalSoil.Add(new ConfidentialReleasesRow(v.pollutantGroupCode, v.reason, 0, 0, v.countSoil));
}
// Merge water and soil lists into air list and return it
foreach (var f in finalAir)
{
foreach (var w in finalWater)
{
if (f.PollutantCode.Equals(w.PollutantCode) && f.ReasonCode.Equals(w.ReasonCode))
{
f.FacilitiesWater = w.FacilitiesWater;
break;
}
}
}
foreach (var f in finalAir)
{
foreach (var s in finalSoil)
{
if (f.PollutantCode.Equals(s.PollutantCode) && f.ReasonCode.Equals(s.ReasonCode))
{
f.FacilitiesSoil = s.FacilitiesSoil;
break;
}
}
}
List <ConfidentialReleasesRow> final = new List <ConfidentialReleasesRow>();
var finalOrdered = finalAir.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>
/// get lambda for confidential pollutant transfers
/// </summary>
private static Expression <Func <POLLUTANTRELEASE, bool> > getPollutantReleasesConfidentialLambda(DataClassesPollutantReleaseDataContext db, IndustrialActivitySearchFilter filter, bool includePollutant)
{
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTRELEASE), "s");
PollutantReleaseSearchFilter filterReleases = FilterConverter.ConvertToPollutantReleaseSearchFilter(filter);
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantReleasesConfidential(filterReleases, param, includePollutant);
Expression <Func <POLLUTANTRELEASE, bool> > lambda = Expression.Lambda <Func <POLLUTANTRELEASE, bool> >(exp, param);
return(lambda);
}
// ---------------------------------------------------------------------------------------------------
// Comparison
// ---------------------------------------------------------------------------------------------------
#region comparison
/// <summary>
/// GetComparisonTimeSeries
/// </summary>
public static TimeSeriesClasses.ComparisonPollutant GetComparisonTimeSeries(PollutantReleasesTimeSeriesFilter filter, int yearFrom, int yearTo, MediumFilter.Medium medium)
{
// Create lambda with pollutant release filter
DataClassesPollutantReleaseDataContext db = getDataContext();
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTRELEASE), "s");
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantReleases(filter, param);
// apply medium
Expression expMedium = LinqExpressionBuilder.GetLinqExpressionMediumRelease(medium, param);
if (exp != null && expMedium != null)
{
exp = Expression.And(exp, expMedium);
}
Expression <Func <POLLUTANTRELEASE, bool> > lambda = Expression.Lambda <Func <POLLUTANTRELEASE, bool> >(exp, param);
// group by reporting year, get from and to data
IQueryable <IGrouping <int, POLLUTANTRELEASE> > groupFrom = db.POLLUTANTRELEASEs.Where(lambda).Where(p => p.ReportingYear == yearFrom).GroupBy(p => p.ReportingYear);
IQueryable <IGrouping <int, POLLUTANTRELEASE> > groupTo = db.POLLUTANTRELEASEs.Where(lambda).Where(p => p.ReportingYear == yearTo).GroupBy(p => p.ReportingYear);
// Facility IDs when year is 'yearTo'
var vTo = db.POLLUTANTRELEASEs.Where(lambda).Where(p => p.ReportingYear == yearTo).Select(p => p.FacilityID).Distinct();
IQueryable <IGrouping <int, POLLUTANTRELEASE> > groupDataFromBoth = db.POLLUTANTRELEASEs.Where(lambda).Where(p => p.ReportingYear == yearFrom && vTo.Contains(p.FacilityID)).GroupBy(p => p.ReportingYear);
// Facility IDs when year is 'yearFrom'
var vFrom = db.POLLUTANTRELEASEs.Where(lambda).Where(p => p.ReportingYear == yearFrom).Select(p => p.FacilityID).Distinct();
IQueryable <IGrouping <int, POLLUTANTRELEASE> > groupDataToBoth = db.POLLUTANTRELEASEs.Where(lambda).Where(p => p.ReportingYear == yearTo && vFrom.Contains(p.FacilityID)).GroupBy(p => p.ReportingYear);
// result lists
IEnumerable <TimeSeriesClasses.TsPollutantCompare> dataFrom = null, dataTo = null;
IEnumerable <TimeSeriesClasses.TsPollutantCompare> dataFromBoth = null, dataToBoth = null;
switch (medium)
{
case MediumFilter.Medium.Air:
dataFrom = groupFrom.Select(x => new TimeSeriesClasses.TsPollutantCompare(x.Count(), x.Sum(p => p.QuantityAir), x.Sum(p => p.QuantityAccidentalAir)));
dataTo = groupTo.Select(x => new TimeSeriesClasses.TsPollutantCompare(x.Count(), x.Sum(p => p.QuantityAir), x.Sum(p => p.QuantityAccidentalAir)));
dataFromBoth = groupDataFromBoth.Select(x => new TimeSeriesClasses.TsPollutantCompare(x.Count(), x.Sum(p => p.QuantityAir), x.Sum(p => p.QuantityAccidentalAir)));
dataToBoth = groupDataToBoth.Select(x => new TimeSeriesClasses.TsPollutantCompare(x.Count(), x.Sum(p => p.QuantityAir), x.Sum(p => p.QuantityAccidentalAir)));
break;
case MediumFilter.Medium.Water:
dataFrom = groupFrom.Select(x => new TimeSeriesClasses.TsPollutantCompare(x.Count(), x.Sum(p => p.QuantityWater), x.Sum(p => p.QuantityAccidentalWater)));
dataTo = groupTo.Select(x => new TimeSeriesClasses.TsPollutantCompare(x.Count(), x.Sum(p => p.QuantityWater), x.Sum(p => p.QuantityAccidentalWater)));
dataFromBoth = groupDataFromBoth.Select(x => new TimeSeriesClasses.TsPollutantCompare(x.Count(), x.Sum(p => p.QuantityWater), x.Sum(p => p.QuantityAccidentalWater)));
dataToBoth = groupDataToBoth.Select(x => new TimeSeriesClasses.TsPollutantCompare(x.Count(), x.Sum(p => p.QuantityWater), x.Sum(p => p.QuantityAccidentalWater)));
break;
case MediumFilter.Medium.Soil:
dataFrom = groupFrom.Select(x => new TimeSeriesClasses.TsPollutantCompare(x.Count(), x.Sum(p => p.QuantitySoil), x.Sum(p => p.QuantityAccidentalSoil)));
dataTo = groupTo.Select(x => new TimeSeriesClasses.TsPollutantCompare(x.Count(), x.Sum(p => p.QuantitySoil), x.Sum(p => p.QuantityAccidentalSoil)));
dataFromBoth = groupDataFromBoth.Select(x => new TimeSeriesClasses.TsPollutantCompare(x.Count(), x.Sum(p => p.QuantitySoil), x.Sum(p => p.QuantityAccidentalSoil)));
dataToBoth = groupDataToBoth.Select(x => new TimeSeriesClasses.TsPollutantCompare(x.Count(), x.Sum(p => p.QuantitySoil), x.Sum(p => p.QuantityAccidentalSoil)));
break;
default: return(null);
}
TimeSeriesClasses.ComparisonPollutant result = new TimeSeriesClasses.ComparisonPollutant(yearFrom, yearTo);
var res = dataFrom.SingleOrDefault();
if (res != null)
{
result.SetFrom(res.Count, res.Quantity, res.Accidental);
}
res = dataTo.SingleOrDefault();
if (res != null)
{
result.SetTo(res.Count, res.Quantity, res.Accidental);
}
res = dataFromBoth.SingleOrDefault();
if (res != null)
{
result.SetBothFrom(res.Count, res.Quantity, res.Accidental);
}
res = dataToBoth.SingleOrDefault();
if (res != null)
{
result.SetBothTo(res.Count, res.Quantity, res.Accidental);
}
return(result);
}