// ---------------------------------------------------------------------------------------------------
// 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>
/// Search
/// </summary>
protected void btnSearch_Click(object sender, EventArgs e)
{
if (InvokeSearch != null)
{
PollutantTransferTimeSeriesFilter filter = PopulateFilter();
// start the search
InvokeSearch.Invoke(filter, e);
}
}
// 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>
/// Populate charts. Selected year in compare dropdown will be searchyear
/// </summary>
public void Populate(PollutantTransferTimeSeriesFilter filter, int?searchYear)
{
SearchFilter = filter;
SearchYear = searchYear;
initializeContentLinks();
//Only determine once and store in viewstate
ConfidentialityAffected = PollutantTransferTrend.IsAffectedByConfidentiality(filter);
// show timeseries as default
showContent(Sheets.TimeSeries.TimeSeries.ToString());
}
// ---------------------------------------------------------------------------------------------------
// 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>
/// query data to be displayed in the facility resul listview
/// </summary>
private void doSearch(object sender, EventArgs e)
{
((MasterSearchPage)this.Master).UpdateMode(true);
((MasterSearchPage)this.Master).ShowResultArea();
PollutantTransferTimeSeriesFilter filter = sender as PollutantTransferTimeSeriesFilter;
if (filter != null)
{
this.ucTsPollutantTransfersSheet.Populate(filter);
updateJavaScriptMap(filter);
}
}
/// <summary>
/// returns a dictionary with sheet headers <key, value> for pollutant transfers time series search
/// </summary>
public static Dictionary <string, string> GetTsPollutantTransfersSearchHeader(
PollutantTransferTimeSeriesFilter filter,
bool confidentialityAffected)
{
Dictionary <string, string> header = makeHeader();
addArea(header, filter.AreaFilter);
addActivity(header, filter.ActivityFilter);
addPollutant(header, filter.PollutantFilter);
addConfidentiality(header, confidentialityAffected);
return(header);
}
/// <summary>
/// load completed, perserve scroll
/// </summary>
protected override void OnLoadComplete(EventArgs e)
{
base.OnLoadComplete(e);
if (!IsPostBack)
{
//if filter is in request, search will be invoked from the start
if (LinkSearchBuilder.HasPollutantTransferTimeSeriesFilter(Request))
{
PollutantTransferTimeSeriesFilter filter = this.ucSearchOptions.PopulateFilter();
doSearch(filter, EventArgs.Empty);
}
}
}
/// <summary>
/// updateTimeSeries
/// </summary>
private void updateCompareChart(PollutantTransferTimeSeriesFilter filter)
{
dataFound(true);
// init year combo boxes
int year1 = this.ucYearCompare.Year1;
int year2 = this.ucYearCompare.Year2;
Color[] colors = new Color[] { Global.ColorWasteWater, Global.ColorWasteWater };
string[] legendTexts = new string[] { Resources.GetGlobal("Common", "FacilitiesBothYears"), Resources.GetGlobal("Common", "AllFacilities") };
ChartHatchStyle[] hatchStyles = new ChartHatchStyle[] { Global.HatchStyleBothYears, Global.HatchStyleTotal };
// same unit for all data
this.ucStackColumnCompare.Initialize(colors.Length, StackColumnType.Comparison, PollutantTransferTrend.CODE_KG, colors, hatchStyles, legendTexts);
// from and to bar
// get compare result compare char
TimeSeriesClasses.ComparisonPollutant result = PollutantTransferTrend.GetComparisonTimeSeries(filter, year1, year2);
bool foundData = (result.QuantityFrom != null || result.QuantityTo != null);
if (foundData)
{
TimeSeriesUtils.BarData dataFrom = new TimeSeriesUtils.BarData
{
Year = result.YearFrom,
Values = new double?[] { TimeSeriesUtils.RangeValue(result.BothQuantityFrom), TimeSeriesUtils.RangeValue(result.QuantityFrom) - TimeSeriesUtils.RangeValue(result.BothQuantityFrom) },
};
TimeSeriesUtils.BarData dataTo = new TimeSeriesUtils.BarData
{
Year = result.YearTo,
Values = new double?[] { TimeSeriesUtils.RangeValue(result.BothQuantityTo), TimeSeriesUtils.RangeValue(result.QuantityTo) - TimeSeriesUtils.RangeValue(result.BothQuantityTo) },
};
// from and to bar
this.ucStackColumnCompare.Add(new List <TimeSeriesUtils.BarData>()
{
dataFrom, dataTo
});
// update sinle table
updateTable(result);
}
else
{
dataFound(false);
}
}
// ----------------------------------------------------------------------------------
// 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);
}
public PollutantTransferTimeSeriesFilter PopulateFilter()
{
PollutantTransferTimeSeriesFilter filter = new PollutantTransferTimeSeriesFilter();
filter.PeriodFilter = new PeriodFilter();
filter.PeriodFilter.StartYear = 2007; //EPER years are not included.
filter.AreaFilter = this.ucAreaSearchOption.PopulateFilter();
filter.PollutantFilter = this.ucPollutantSearchOption.PopulateFilter();
filter.ActivityFilter = this.ucAdvancedActivitySearchOption.PopulateFilter();
// store settings in cookies
CookieStorage.SetFilter(Response, filter.AreaFilter);
return(filter);
}
private void toggleTimeseries(ListViewCommandEventArgs e, int rowindex)
{
ucTsPollutantTransfersSheet control = (ucTsPollutantTransfersSheet)this.lvPollutantTransfers.Items[rowindex].FindControl("ucTsPollutantTransfersSheet");
closeAllSubSheets(); // only allow 1 sheet open
control.Visible = !control.Visible;
Control div = this.lvPollutantTransfers.Items[rowindex].FindControl("subsheet");
div.Visible = !div.Visible;
if (control.Visible)
{
// create search filter and change activity filter
PollutantTransferTimeSeriesFilter filter = FilterConverter.ConvertToPollutantTransferTimeSeriesFilter(SearchFilter);
filter.ActivityFilter = getActivityFilter(e);
control.Populate(filter, SearchFilter.YearFilter.Year);
}
}
/// <summary>
/// Get confidential data for timeseries on aggregated level. If no confidentiality claims is found the list will be empty.
/// </summary>
public static List <TimeSeriesClasses.ConfidentialityPollutant> GetConfidentialTimeSeries(PollutantTransferTimeSeriesFilter filter)
{
//Find data for confidential in the group of the pollutant
PollutantTransferTimeSeriesFilter filterConf = filter.Clone() as PollutantTransferTimeSeriesFilter;
filterConf.PollutantFilter.PollutantID = filter.PollutantFilter.PollutantGroupID;
List <TimeSeriesClasses.PollutantTransfers> confidentialData = GetTimeSeries(filterConf);
if (confidentialData.Count() > 0)
{
//Find data for pollutant
List <TimeSeriesClasses.PollutantTransfers> pollutantData = GetTimeSeries(filter);
//merge the two lists and return.
return(mergeList(pollutantData, confidentialData));
}
return(new List <TimeSeriesClasses.ConfidentialityPollutant>());
}
public void Populate(PollutantTransferTimeSeriesFilter filter, bool hasConfidentialInformation)
{
SearchFilter = filter;
LOV_POLLUTANT pollutant = ListOfValues.GetPollutant(filter.PollutantFilter.PollutantID);
PollutantCode = pollutant != null ? pollutant.Code : null;
//set parentcode
ParentCode = null;
if (pollutant != null && pollutant.ParentID != null)
{
LOV_POLLUTANT pollutantGroup = ListOfValues.GetPollutant(pollutant.ParentID.Value);
ParentCode = pollutantGroup != null ? pollutantGroup.Code : null;
}
this.divConfidentialityInformation.Visible = hasConfidentialInformation;
this.divNoConfidentialityInformation.Visible = !hasConfidentialInformation;
showContent(filter);
}
/// <summary>
/// populate
/// </summary>
public void Populate(PollutantTransferTimeSeriesFilter filter)
{
// result from qyerylayer
List <TimeSeriesClasses.PollutantTransfers> data = PollutantTransferTrend.GetTimeSeries(filter);
// set data to table.
this.lvTimeSeriesTable.DataSource = data;
this.lvTimeSeriesTable.DataBind();
// init time series. Must be done after table databinding, because missing years are added.
this.ucStackColumnTime.Visible = (data != null && data.Count > 0);
if (this.ucStackColumnTime.Visible)
{
Color[] colors = new Color[] { Global.ColorWasteWater };
ChartHatchStyle[] hatchStyles = new ChartHatchStyle[] { ChartHatchStyle.None };
// init time series
this.ucStackColumnTime.Initialize(colors.Length, StackColumnType.TimeSeries, PollutantTransferTrend.CODE_KG, colors, hatchStyles, null);
//this.ucStackColumnTime.Initialize(colors.Length, StackColumnType.TimeSeries, PollutantTransferTrend.CODE_TNE, colors, null);
this.ucStackColumnTime.Width = 700;
List <TimeSeriesUtils.BarData> bars = new List <TimeSeriesUtils.BarData>();
// loop through data and create tooltip
foreach (var v in data)
{
string[] tip = new string[] { String.Format("{0}: {1}", Resources.GetGlobal("Common", "Year"), v.Year),
String.Format("{0}: {1}", Resources.GetGlobal("Common", "Quantity"), QuantityFormat.Format(v.Quantity, v.QuantityUnit)),
String.Format("{0}: {1}", Resources.GetGlobal("Common", "Facilities"), v.Facilities) };
TimeSeriesUtils.BarData cd = new TimeSeriesUtils.BarData
{
Year = v.Year,
Values = new double?[] { TimeSeriesUtils.RangeValue(v.Quantity) },
ToolTip = ToolTipFormatter.FormatLines(tip)
};
bars.Add(cd);
}
this.ucStackColumnTime.Add(TimeSeriesUtils.InsertMissingYearsTimeSeries(bars, false));
}
}
// --------------------------------------------------------------------------------------------------------------------
// PollutantTransferTimeSeries Filter
// --------------------------------------------------------------------------------------------------------------------
#region PollutantTransferTimeSeriesFilter
public static string SerializeToUrl(PollutantTransferTimeSeriesFilter filter)
{
string result = String.Empty;
if (filter != null)
{
// Area
result += SerializeToUrl(filter.AreaFilter);
// Year
result += SerializeToUrl(filter.PeriodFilter);
// Pollutant
result += SerializeToUrl(filter.PollutantFilter);
// Activity
result += SerializeToUrl(filter.ActivityFilter);
}
result = result.Remove(result.Length - 1);
return(result);
}
/// <summary>
/// Populate charts. Selected year in compare dropdown will be searchyear
/// </summary>
public void Populate(PollutantTransferTimeSeriesFilter filter, int?searchYear)
{
SearchFilter = filter;
this.ucYearCompare.Initialize(false, searchYear);
updateCompareChart(filter);
}
/// <summary>
/// returns a dictionary with sheet headers for time series pollutant transfer search
/// </summary>
public static Dictionary <string, string> GetTimeSeriesPollutantTransferHeader(PollutantTransferTimeSeriesFilter filter)
{
Dictionary <string, string> header = new Dictionary <string, string>();
addArea(header, filter.AreaFilter);
addActivity(header, filter.ActivityFilter);
addPollutant(header, filter.PollutantFilter);
return(header);
}
/// <summary>
/// populate. Selected year in compare dropdown will be the last report year
/// </summary>
public void Populate(PollutantTransferTimeSeriesFilter filter)
{
Populate(filter, null);
}
private void showContent(PollutantTransferTimeSeriesFilter filter)
{
this.lvConfidentiality.DataSource = PollutantTransferTrend.GetConfidentialTimeSeries(filter);
this.lvConfidentiality.DataBind();
}
/// <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>
/// update flash map
/// </summary>
/* private void updateFlashMap(PollutantTransferTimeSeriesFilter filter)
* {
* MapFilter mapfilter = QueryLayer.PollutantTransferTrend.GetMapFilter(filter);
* string header = MapPrintDetails.Build(SheetHeaderBuilder.GetTimeSeriesPollutantTransferHeader(filter));
* MapUtils.UpdateSmallMap(MasterSearchPage.MAPID, this, this.ClientID, mapfilter, header, Request.ApplicationPath);
* ((MasterSearchPage)this.Master).UpdateExpandedScript(mapfilter, header);
* }*/
private void updateJavaScriptMap(PollutantTransferTimeSeriesFilter filter)
{
MapFilter mapfilter = QueryLayer.PollutantTransferTrend.GetMapJavascriptFilter(filter);
MapJavaScriptUtils.UpdateJavaScriptMap(mapfilter, Page);
}
