Пример #1
0
        /// <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);
        }
Пример #2
0
        /// <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);
        }
Пример #3
0
        /// <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);
        }
Пример #4
0
        /// <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);
        }
Пример #5
0
        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);
        }
Пример #6
0
        // ----------------------------------------------------------------------------------
        // 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);
        }
Пример #7
0
        /// <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);
        }
Пример #8
0
        // ----------------------------------------------------------------------------------
        // 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);
        }
Пример #9
0
        /// <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);
        }
Пример #10
0
        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);
        }
Пример #11
0
        // ----------------------------------------------------------------------------------
        // 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);
        }
Пример #12
0
        // ----------------------------------------------------------------------------------
        // 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);
        }