/// <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); }