/// <summary>
/// return true if confidentiality might effect result
/// </summary>
public static bool IsAffectedByConfidentiality(WasteTransferTimeSeriesFilter filter, WasteTypeFilter.Type wasteType)
{
Expression <Func <WASTETRANSFER_CONFIDENTIAL, bool> > lambda = getLambdaExpressionConfidential(filter, wasteType);
DataClassesWasteTransferDataContext db = getDataContext();
return(db.WASTETRANSFER_CONFIDENTIALs.Any(lambda));
}
private static DataClassesWasteTransferDataContext getWasteTransferDataContext()
{
DataClassesWasteTransferDataContext db = new DataClassesWasteTransferDataContext();
db.Log = new DebuggerWriter();
return(db);
}
// ---------------------------------------------------------------------------------------------------
// Time series
// ---------------------------------------------------------------------------------------------------
#region timeseries
/// <summary>
/// return timeseries
/// </summary>
public static List <TimeSeriesClasses.WasteTransfer> GetTimeSeries(WasteTransferTimeSeriesFilter filter, WasteTypeFilter.Type wastetype)
{
DataClassesWasteTransferDataContext db = getDataContext();
// apply filter
Expression <Func <WASTETRANSFER, bool> > lambda = getLambdaExpression(filter, wastetype);
// get data and group by year (which get assigned to x.Key by link)
IQueryable <IGrouping <int, WASTETRANSFER> > group = db.WASTETRANSFERs.Where(lambda).GroupBy(p => p.ReportingYear).OrderBy(p => p.Key);
// lookup wastetype. Table has only one row per faciltiy report
IEnumerable <TimeSeriesClasses.WasteTransfer> data = null;
switch (wastetype)
{
case WasteTypeFilter.Type.HazardousCountry:
data = group.Select(x => new TimeSeriesClasses.WasteTransfer(x.Key, x.Count(), WasteTypeFilter.Type.HazardousCountry, x.Sum(p => p.QuantityTotalHWIC), x.Sum(p => p.QuantityRecoveryHWIC), x.Sum(p => p.QuantityDisposalHWIC), x.Sum(p => p.QuantityUnspecHWIC)));
break;
case WasteTypeFilter.Type.HazardousTransboundary:
data = group.Select(x => new TimeSeriesClasses.WasteTransfer(x.Key, x.Count(), WasteTypeFilter.Type.HazardousTransboundary, x.Sum(p => p.QuantityTotalHWOC), x.Sum(p => p.QuantityRecoveryHWOC), x.Sum(p => p.QuantityDisposalHWOC), x.Sum(p => p.QuantityUnspecHWOC)));
break;
case WasteTypeFilter.Type.NonHazardous:
data = group.Select(x => new TimeSeriesClasses.WasteTransfer(x.Key, x.Count(), WasteTypeFilter.Type.NonHazardous, x.Sum(p => p.QuantityTotalNONHW), x.Sum(p => p.QuantityRecoveryNONHW), x.Sum(p => p.QuantityDisposalNONHW), x.Sum(p => p.QuantityUnspecNONHW)));
break;
default:
throw new ArgumentOutOfRangeException("wastetype", String.Format("Illegal wastetype: {0}", wastetype.ToString()));
}
//add information about no. of reporting countries
IEnumerable <Facility.ReportingCountries> years = Facility.GetReportingCountries(filter.AreaFilter).ToList();
IEnumerable <TimeSeriesClasses.WasteTransfer> res = from l in data.ToList()
join r in years on l.Year equals r.Year
select new TimeSeriesClasses.WasteTransfer(
l.Year,
l.Facilities,
l.WasteType,
l.QuantityTotal,
l.QuantityRecovery,
l.QuantityDisposal,
l.QuantityUnspec,
r.Countries);
return(res.OrderBy(p => p.Year).ToList());
}
/// <summary>
/// return timeseries
/// </summary>
public static List <TimeSeriesClasses.WasteTransfer> GetTimeSeries(int facilityID, WasteTypeFilter.Type wasteType)
{
DataClassesWasteTransferDataContext db = getDataContext();
//table has only one row per facility per year with aggregated data.
IEnumerable <TimeSeriesClasses.WasteTransfer> data = db.WASTETRANSFERs.Where(s => s.FacilityID == facilityID)
.OrderBy(s => s.ReportingYear)
.Select(s => new TimeSeriesClasses.WasteTransfer(
s.ReportingYear,
1,
wasteType,
LinqFunctionsWaste.QuantityTotal(wasteType)(s),
LinqFunctionsWaste.QuantityRecovery(wasteType)(s),
LinqFunctionsWaste.QuantityDisposal(wasteType)(s),
LinqFunctionsWaste.QuantityUnspec(wasteType)(s)
));
return(data.ToList());
}
public static List <TimeSeriesClasses.ConfidentialityWaste> GetCountConfidentialFacilities(WasteTransferTimeSeriesFilter filter, WasteTypeFilter.Type wasteType)
{
DataClassesWasteTransferDataContext db = getDataContext();
Expression <Func <WASTETRANSFER_CONFIDENTIAL, bool> > lambda = getLambdaExpressionConfidential(filter, wasteType);
//count all confidential claims
//table has one row per facility per wastetype so distinct is needed
List <TimeSeriesClasses.ConfidentialityWaste> confidential = db.WASTETRANSFER_CONFIDENTIALs.Where(lambda)
.GroupBy(v => v.ReportingYear)
.OrderBy(v => v.Key)
.Select(v => new TimeSeriesClasses.ConfidentialityWaste
{
Year = v.Key,
CountConfTotal = v.Select(x => x.FacilityReportID).Distinct().Count(),
CountConfQuantity = v.Where(x => (bool)x.ConfidentialityOnQuantity).Select(x => x.FacilityReportID).Distinct().Count(),
CountConfTreatment = v.Where(x => (bool)x.ConfidentialityOnTreatmant).Select(x => x.FacilityReportID).Distinct().Count()
}).ToList();
if (confidential.Count() > 0)
{
List <TimeSeriesClasses.WasteTransfer> all = GetTimeSeries(filter, wasteType);
foreach (TimeSeriesClasses.WasteTransfer wt in all)
{
TimeSeriesClasses.ConfidentialityWaste conf = confidential.SingleOrDefault(c => c.Year.Equals(wt.Year));
if (conf != null)
{
conf.CountTotal = wt.Facilities;
}
else
{
confidential.Add(new TimeSeriesClasses.ConfidentialityWaste {
Year = wt.Year, CountTotal = wt.Facilities
});
}
}
return(confidential.OrderBy(c => c.Year).ToList());
}
return(new List <TimeSeriesClasses.ConfidentialityWaste>());
}
// ---------------------------------------------------------------------------------------------------
// confidentiality
// ---------------------------------------------------------------------------------------------------
#region Confidentiality
public static IEnumerable <TimeSeriesClasses.ConfidentialityWaste> GetConfidentiality(int facilityId, WasteTypeFilter.Type wasteType)
{
DataClassesWasteTransferDataContext db = getDataContext();
// apply filter
Expression <Func <WASTETRANSFER, bool> > lambda = getLambdaExpression(wasteType);
//table have only one record per facility, so no aggregation is needed.
IEnumerable <TimeSeriesClasses.ConfidentialityWaste> data = db.WASTETRANSFERs.Where(lambda)
.Where(v => v.FacilityID == facilityId)
.OrderBy(v => v.ReportingYear)
.Select(v => new TimeSeriesClasses.ConfidentialityWaste
{
Year = v.ReportingYear,
CountTotal = 1,
CountConfTotal = Convert.ToInt32(LinqFunctionsWaste.ConfidentialityIndicator(wasteType)(v)),
CountConfQuantity = Convert.ToInt32(LinqFunctionsWaste.ConfidentialityIndicatorQuantity(wasteType)(v))
});
return(data);
}
// ---------------------------------------------------------------------------------------------------
// Comparison
// ---------------------------------------------------------------------------------------------------
#region comparison
/// <summary>
/// GetComparisonTimeSeries
/// </summary>
public static TimeSeriesClasses.ComparisonWasteTransfer GetComparisonTimeSeries(WasteTransferTimeSeriesFilter filter, int yearFrom, int yearTo, WasteTypeFilter.Type wasteType)
{
// Create lambda with pollutant release filter
Expression <Func <WASTETRANSFER, bool> > lambda = getLambdaExpression(filter, wasteType);
DataClassesWasteTransferDataContext db = new DataClassesWasteTransferDataContext();
// group by reporting year, get from and to data
IQueryable <IGrouping <int, WASTETRANSFER> > groupFrom = db.WASTETRANSFERs.Where(lambda).Where(p => p.ReportingYear == yearFrom).GroupBy(p => p.ReportingYear);
IQueryable <IGrouping <int, WASTETRANSFER> > groupTo = db.WASTETRANSFERs.Where(lambda).Where(p => p.ReportingYear == yearTo).GroupBy(p => p.ReportingYear);
// Facility IDs when year is 'yearTo'
var vTo = db.WASTETRANSFERs.Where(lambda).Where(p => p.ReportingYear == yearTo).Select(p => p.FacilityID).Distinct();
IQueryable <IGrouping <int, WASTETRANSFER> > groupDataFromBoth = db.WASTETRANSFERs.Where(lambda).Where(p => p.ReportingYear == yearFrom && vTo.Contains(p.FacilityID)).GroupBy(p => p.ReportingYear);
// Facility IDs when year is 'yearFrom'
var vFrom = db.WASTETRANSFERs.Where(lambda).Where(p => p.ReportingYear == yearFrom).Select(p => p.FacilityID).Distinct();
IQueryable <IGrouping <int, WASTETRANSFER> > groupDataToBoth = db.WASTETRANSFERs.Where(lambda).Where(p => p.ReportingYear == yearTo && vFrom.Contains(p.FacilityID)).GroupBy(p => p.ReportingYear);
// result lists
IEnumerable <TimeSeriesClasses.TsWasteCompare> dataFrom = null, dataTo = null;
IEnumerable <TimeSeriesClasses.TsWasteCompare> dataFromBoth = null, dataToBoth = null;
switch (wasteType)
{
case WasteTypeFilter.Type.HazardousCountry:
dataFrom = groupFrom.Select(x => new TimeSeriesClasses.TsWasteCompare(x.Count(), x.Sum(p => p.QuantityTotalHWIC), x.Sum(p => p.QuantityRecoveryHWIC), x.Sum(p => p.QuantityDisposalHWIC), x.Sum(p => p.QuantityUnspecHWIC)));
dataTo = groupTo.Select(x => new TimeSeriesClasses.TsWasteCompare(x.Count(), x.Sum(p => p.QuantityTotalHWIC), x.Sum(p => p.QuantityRecoveryHWIC), x.Sum(p => p.QuantityDisposalHWIC), x.Sum(p => p.QuantityUnspecHWIC)));
dataFromBoth = groupDataFromBoth.Select(x => new TimeSeriesClasses.TsWasteCompare(x.Count(), x.Sum(p => p.QuantityTotalHWIC), x.Sum(p => p.QuantityRecoveryHWIC), x.Sum(p => p.QuantityDisposalHWIC), x.Sum(p => p.QuantityUnspecHWIC)));
dataToBoth = groupDataToBoth.Select(x => new TimeSeriesClasses.TsWasteCompare(x.Count(), x.Sum(p => p.QuantityTotalHWIC), x.Sum(p => p.QuantityRecoveryHWIC), x.Sum(p => p.QuantityDisposalHWIC), x.Sum(p => p.QuantityUnspecHWIC)));
break;
case WasteTypeFilter.Type.HazardousTransboundary:
dataFrom = groupFrom.Select(x => new TimeSeriesClasses.TsWasteCompare(x.Count(), x.Sum(p => p.QuantityTotalHWOC), x.Sum(p => p.QuantityRecoveryHWOC), x.Sum(p => p.QuantityDisposalHWOC), x.Sum(p => p.QuantityUnspecHWOC)));
dataTo = groupTo.Select(x => new TimeSeriesClasses.TsWasteCompare(x.Count(), x.Sum(p => p.QuantityTotalHWOC), x.Sum(p => p.QuantityRecoveryHWOC), x.Sum(p => p.QuantityDisposalHWOC), x.Sum(p => p.QuantityUnspecHWOC)));
dataFromBoth = groupDataFromBoth.Select(x => new TimeSeriesClasses.TsWasteCompare(x.Count(), x.Sum(p => p.QuantityTotalHWOC), x.Sum(p => p.QuantityRecoveryHWOC), x.Sum(p => p.QuantityDisposalHWOC), x.Sum(p => p.QuantityUnspecHWOC)));
dataToBoth = groupDataToBoth.Select(x => new TimeSeriesClasses.TsWasteCompare(x.Count(), x.Sum(p => p.QuantityTotalHWOC), x.Sum(p => p.QuantityRecoveryHWOC), x.Sum(p => p.QuantityDisposalHWOC), x.Sum(p => p.QuantityUnspecHWOC)));
break;
case WasteTypeFilter.Type.NonHazardous:
dataFrom = groupFrom.Select(x => new TimeSeriesClasses.TsWasteCompare(x.Count(), x.Sum(p => p.QuantityTotalNONHW), x.Sum(p => p.QuantityRecoveryNONHW), x.Sum(p => p.QuantityDisposalNONHW), x.Sum(p => p.QuantityUnspecNONHW)));
dataTo = groupTo.Select(x => new TimeSeriesClasses.TsWasteCompare(x.Count(), x.Sum(p => p.QuantityTotalNONHW), x.Sum(p => p.QuantityRecoveryNONHW), x.Sum(p => p.QuantityDisposalNONHW), x.Sum(p => p.QuantityUnspecNONHW)));
dataFromBoth = groupDataFromBoth.Select(x => new TimeSeriesClasses.TsWasteCompare(x.Count(), x.Sum(p => p.QuantityTotalNONHW), x.Sum(p => p.QuantityRecoveryNONHW), x.Sum(p => p.QuantityDisposalNONHW), x.Sum(p => p.QuantityUnspecNONHW)));
dataToBoth = groupDataToBoth.Select(x => new TimeSeriesClasses.TsWasteCompare(x.Count(), x.Sum(p => p.QuantityTotalNONHW), x.Sum(p => p.QuantityRecoveryNONHW), x.Sum(p => p.QuantityDisposalNONHW), x.Sum(p => p.QuantityUnspecNONHW)));
break;
default: return(null);
}
TimeSeriesClasses.ComparisonWasteTransfer result = new TimeSeriesClasses.ComparisonWasteTransfer(yearFrom, yearTo);
var res = dataFrom.SingleOrDefault();
if (res != null)
{
result.SetFrom(res.Count, res.Quantity, res.Recovery, res.Disposal, res.Unspecified);
}
res = dataTo.SingleOrDefault();
if (res != null)
{
result.SetTo(res.Count, res.Quantity, res.Recovery, res.Disposal, res.Unspecified);
}
res = dataFromBoth.SingleOrDefault();
if (res != null)
{
result.SetBothFrom(res.Count, res.Quantity, res.Recovery, res.Disposal, res.Unspecified);
}
res = dataToBoth.SingleOrDefault();
if (res != null)
{
result.SetBothTo(res.Count, res.Quantity, res.Recovery, res.Disposal, res.Unspecified);
}
return(result);
}
