Esempio n. 1
0
        /// <summary>
        /// Return all subactivities (level 2) that fullfill search criteria.
        /// If activityCodes are null, all activities will be returned. If activityCodes is empty no activities will be returned.
        /// Notice that this will include one subactivity "Not specified on this level" for each activity, even if the activity do not have any subactivities at all!
        /// </summary>
        public static IEnumerable <ActivityTreeListRow> GetSubActivities(PollutantTransfersSearchFilter filter, List <string> activityCodes)
        {
            if (activityCodes != null && activityCodes.Count() == 0)
            {
                return(new List <ActivityTreeListRow>());
            }

            DataClassesPollutantTransferDataContext      db     = getPollutantTransferDataContext();
            Expression <Func <POLLUTANTTRANSFER, bool> > lambda = getActivityAreaLambda(filter);

            //add activities to expression
            if (activityCodes != null)
            {
                ParameterExpression param       = lambda.Parameters[0];
                Expression          activityExp = LinqExpressionBuilder.GetInExpr(param, "IAActivityCode", activityCodes);

                Expression exp = LinqExpressionBuilder.CombineAnd(lambda.Body, activityExp);
                lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
            }

            //find data for sub-activity level, this level never has children.
            IEnumerable <ActivityTreeListRow> subactivities = db.POLLUTANTTRANSFERs.Where(lambda)
                                                              .GroupBy(p => new { SectorCode = p.IASectorCode, ActivityCode = p.IAActivityCode, SubActivityCode = p.IASubActivityCode, PollutantCode = p.PollutantCode })
                                                              .Select(x => new ActivityTreeListRow(
                                                                          x.Key.SectorCode,
                                                                          x.Key.ActivityCode,
                                                                          !x.Key.SubActivityCode.Equals(null) ? x.Key.SubActivityCode : ActivityTreeListRow.CODE_UNSPECIFIED,
                                                                          x.Key.PollutantCode,
                                                                          x.Count(),
                                                                          x.Sum(p => p.Quantity),
                                                                          false));

            return(subactivities);
        }
Esempio n. 2
0
        /// <summary>
        /// return all activities (level 1) that fullfill search criteria within the sectorCodes given.
        /// If sectorCodes are null, all activities will be returned. If sectorCodes is empty no activities will be returned.
        /// </summary>
        public static IEnumerable <ActivityTreeListRow> GetActivities(PollutantTransfersSearchFilter filter, List <string> sectorCodes)
        {
            if (sectorCodes != null && sectorCodes.Count() == 0)
            {
                return(new List <ActivityTreeListRow>());
            }

            DataClassesPollutantTransferDataContext      db     = getPollutantTransferDataContext();
            Expression <Func <POLLUTANTTRANSFER, bool> > lambda = getActivityAreaLambda(filter);

            if (sectorCodes != null)
            {
                ParameterExpression param     = lambda.Parameters[0];
                Expression          sectorExp = LinqExpressionBuilder.GetInExpr(param, "IASectorCode", sectorCodes);

                Expression exp = LinqExpressionBuilder.CombineAnd(lambda.Body, sectorExp);
                lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
            }

            //find data for activity level
            IEnumerable <ActivityTreeListRow> activities = db.POLLUTANTTRANSFERs.Where(lambda)
                                                           .GroupBy(p => new { SectorCode = p.IASectorCode, ActivityCode = p.IAActivityCode, PollutantCode = p.PollutantCode })
                                                           .Select(x => new ActivityTreeListRow(
                                                                       x.Key.SectorCode,
                                                                       x.Key.ActivityCode,
                                                                       null,
                                                                       x.Key.PollutantCode,
                                                                       x.Count(),
                                                                       x.Sum(p => p.Quantity),
                                                                       x.First(p => !p.IASubActivityCode.Equals(null)) != null));

            return(activities);
        }
Esempio n. 3
0
        /// <summary>
        /// return total list confidential reasons
        /// </summary>
        public static IEnumerable <TransfersConfidentialRow> GetConfidentialReason(PollutantTransfersSearchFilter filter)
        {
            DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext();

            // apply filter for confidential, do not include the pollutant itself
            ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s");
            Expression          exp   = LinqExpressionBuilder.GetLinqExpressionPollutantTransfersConfidential(filter, param, false);
            Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);

            var reason0 = db.POLLUTANTTRANSFERs.Where(lambda);
            var reason1 = from p in reason0 group p by new { p.ConfidentialCode };
            var reason2 = from p in reason1
                          select new
            {
                code  = p.Select(x => x.ConfidentialCode).First(),
                count = p.Count()
            };

            // build result
            List <TransfersConfidentialRow> result = new List <TransfersConfidentialRow>();

            foreach (var v in reason2)
            {
                result.Add(new TransfersConfidentialRow(v.code, v.count));
            }
            return(result.OrderBy(x => x.Code));
        }
Esempio n. 4
0
        // ---------------------------------------------------------------------------------------------------
        // 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());
        }
Esempio n. 5
0
        /// <summary>
        /// return true if confidentiality might effect pollutant transfer result
        /// </summary>
        public static bool IsPollutantTransferAffectedByConfidentiality(IndustrialActivitySearchFilter filter)
        {
            DataClassesPollutantTransferDataContext      db     = getPollutantTransferDataContext();
            Expression <Func <POLLUTANTTRANSFER, bool> > lambda = getPollutantTransfersConfidentialLambda(db, filter, false);

            return(db.POLLUTANTTRANSFERs.Any(lambda));
        }
Esempio n. 6
0
        // ---------------------------------------------------------------------------------------------------
        // Data context
        // ---------------------------------------------------------------------------------------------------
        #region datacontext
        /// <summary>
        /// creates a new DataCotext and add logger
        /// </summary>
        private static DataClassesPollutantTransferDataContext getDataContext()
        {
            DataClassesPollutantTransferDataContext db = new DataClassesPollutantTransferDataContext();

            db.Log = new DebuggerWriter();
            return(db);
        }
Esempio n. 7
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);
        }
Esempio n. 8
0
        /// <summary>
        /// return total list confidential pollutants
        /// </summary>
        public static IEnumerable <TransfersConfidentialRow> GetConfidentialPollutant(PollutantTransfersSearchFilter filter)
        {
            DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext();

            // apply filter for confidential, include pollutants
            ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s");
            Expression          exp   = LinqExpressionBuilder.GetLinqExpressionPollutantTransfersConfidential(filter, param, true);
            Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);

            // sum up facilities
            var pollutant0 = db.POLLUTANTTRANSFERs.Where(lambda);
            var pollutant1 = from p in pollutant0 group p by new { p.PollutantCode };
            var pollutant2 = from p in pollutant1
                             select new
            {
                code  = p.Select(x => x.PollutantCode).First(),
                count = p.Count()
            };

            // build result
            List <TransfersConfidentialRow> result = new List <TransfersConfidentialRow>();

            foreach (var v in pollutant2)
            {
                result.Add(new TransfersConfidentialRow(v.code, v.count));
            }
            return(result);
        }
Esempio n. 9
0
        /// <summary>
        /// return all countries that fullfill search criteria
        /// </summary>
        public static IEnumerable <AreaTreeListRow> GetCountries(PollutantTransfersSearchFilter filter)
        {
            DataClassesPollutantTransferDataContext      db     = getPollutantTransferDataContext();
            Expression <Func <POLLUTANTTRANSFER, bool> > lambda = getActivityAreaLambda(filter);


            //find data for country level. Country level always has children.
            IEnumerable <AreaTreeListRow> countries = db.POLLUTANTTRANSFERs.Where(lambda)
                                                      .GroupBy(p => new { CountryCode = p.CountryCode, PollutantCode = p.PollutantCode })
                                                      .Select(x => new AreaTreeListRow(
                                                                  x.Key.CountryCode,
                                                                  null,
                                                                  filter.AreaFilter.TypeRegion,
                                                                  x.Key.PollutantCode,
                                                                  x.Count(),
                                                                  x.Sum(p => p.Quantity),
                                                                  true));


            List <AreaTreeListRow> result = countries.ToList(); //make sure sql is executed now to do it only once

            List <AreaTreeListRow> totals = result.GroupBy(p => new { PollutantCode = p.PollutantCode })
                                            .Select(x => new AreaTreeListRow(
                                                        AreaTreeListRow.CODE_TOTAL,
                                                        null,
                                                        filter.AreaFilter.TypeRegion,
                                                        x.Key.PollutantCode,
                                                        x.Sum(p => p.Facilities),
                                                        x.Sum(p => p.Quantity),
                                                        false)).ToList();


            //only add total to result if more than one sector.
            if (result.Select(r => r.CountryCode).Distinct().Count() > 1)
            {
                result.AddRange(totals);
            }

            //find facility count
            int facilityCount = 0;

            if (result.Count == 1)
            {
                facilityCount = result.Single().Facilities;
            }
            else if (totals.Count == 1)
            {
                facilityCount = totals.Single().Facilities;
            }
            else
            {
                facilityCount = GetFacilityCount(filter);
            }

            filter.Count = facilityCount;

            return(result);
        }
Esempio n. 10
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);
        }
Esempio n. 11
0
        // 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);
        }
Esempio n. 12
0
        /// <summary>
        /// Find all reported pollutant codes fullfilling the filter
        /// </summary>
        public static List <string> GetPollutantCodes(PollutantTransfersSearchFilter filter)
        {
            DataClassesPollutantTransferDataContext      db     = getPollutantTransferDataContext();
            Expression <Func <POLLUTANTTRANSFER, bool> > lambda = getActivityAreaLambda(filter);

            //distinct pollutants
            List <string> codes = db.POLLUTANTTRANSFERs.Where(lambda)
                                  .Select(r => r.PollutantCode).Distinct().ToList();

            return(codes);
        }
Esempio n. 13
0
        /// <summary>
        /// return true if confidentiality might effect result
        /// </summary>
        public static bool IsAffectedByConfidentiality(PollutantTransfersSearchFilter filter)
        {
            DataClassesPollutantTransferDataContext db = getPollutantTransferDataContext();

            // apply filter for confidential, do not include the pollutant itself
            ParameterExpression param = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s");
            Expression          exp   = LinqExpressionBuilder.GetLinqExpressionPollutantTransfersConfidential(filter, param, false);
            Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);

            return(db.POLLUTANTTRANSFERs.Any(lambda));
        }
Esempio n. 14
0
        // ---------------------------------------------------------------------------------------------------
        // 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));
        }
Esempio n. 15
0
        /// <summary>
        /// Gets Confidential Transfers Reason
        /// </summary>
        /// <param name="filter"></param>
        /// <returns>IEnumerable<ConfidentialTransfersRow></returns>
        public static IEnumerable <ConfidentialTransfersRow> GetConfidentialTransfersReason(IndustrialActivitySearchFilter filter)
        {
            DataClassesPollutantTransferDataContext      db     = getPollutantTransferDataContext();
            Expression <Func <POLLUTANTTRANSFER, bool> > lambda = getPollutantTransfersLambda(db, filter);

            //reason = ConfidentialityID
            var pollutantBase = db.POLLUTANTTRANSFERs.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 <ConfidentialTransfersRow> finaltmp = new List <ConfidentialTransfersRow>();

            // Air count facilities
            var air1 = from p in pollutantConf where (p.ConfidentialCode != null) group p by new { p.PollutantGroupCode, p.ConfidentialCode };
            var air2 = from p in air1
                       select new
            {
                pollutantGroupCode = p.Select(x => x.PollutantGroupCode).First(),
                reason             = p.Select(x => x.ConfidentialCode).First(),
                facilities         = p.Count(x => x.Quantity != null && x.Quantity >= 0)
            };

            foreach (var v in air2)
            {
                finaltmp.Add(new ConfidentialTransfersRow(v.pollutantGroupCode, v.reason, v.facilities));
            }


            List <ConfidentialTransfersRow> final = new List <ConfidentialTransfersRow>();
            var finalOrdered = finaltmp.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);
        }
Esempio n. 16
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);
        }
Esempio n. 17
0
        /// <summary>
        /// Get timeseries on facility level
        /// </summary>
        public static List <TimeSeriesClasses.PollutantTransfers> GetTimeSeries(int facilityid, string pollutantCode)
        {
            DataClassesPollutantTransferDataContext db = getDataContext();


            // get data and group by year (which get assigned to x.Key by link)
            IEnumerable <TimeSeriesClasses.PollutantTransfers> data = db.POLLUTANTTRANSFERs.Where(f => f.FacilityID == facilityid && f.PollutantCode == pollutantCode)
                                                                      .GroupBy(p => p.ReportingYear)
                                                                      .OrderBy(p => p.Key)
                                                                      .Select(x => new TimeSeriesClasses.PollutantTransfers(
                                                                                  x.Key,
                                                                                  x.Sum(p => p.Quantity)));

            return(data.ToList());
        }
Esempio n. 18
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);
        }
Esempio n. 19
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);
        }
Esempio n. 20
0
        /// <summary>
        /// get transfers
        /// </summary>
        private static IEnumerable <IATransfersTreeListRow> getTransfers(DataClassesPollutantTransferDataContext db, Expression <Func <POLLUTANTTRANSFER, bool> > lambda, out int facilitiesCount)
        {
            // count number of distinct facilities for this filter
            facilitiesCount = db.POLLUTANTTRANSFERs.Where(lambda).Select(x => x.FacilityReportID).Distinct().Count();

            //Count facilities per group (level = 0).
            IEnumerable <IATransfersTreeListRow> groupsx = db.POLLUTANTTRANSFERs.Where(lambda)
                                                           .GroupBy(s => s.PollutantGroupCode)
                                                           .Select(v => new IATransfersTreeListRow(
                                                                       v.Key,
                                                                       v.Key,
                                                                       v.Select(x => x.FacilityReportID).Distinct().Count(),
                                                                       0,
                                                                       0,
                                                                       v.Where(x => x.ConfidentialIndicator == false).Select(x => x.PollutantCode).Distinct().Count(),
                                                                       true)
                                                                   );

            //collect pollutants and aggregate data (level = 1).
            IEnumerable <IATransfersTreeListRow> pollutantsx = db.POLLUTANTTRANSFERs.Where(lambda)
                                                               .GroupBy(s => new { s.PollutantGroupCode, s.PollutantCode })
                                                               .Select(v => new IATransfersTreeListRow(
                                                                           v.Key.PollutantCode,
                                                                           v.Key.PollutantGroupCode,
                                                                           v.Count(),
                                                                           v.Sum(x => x.Quantity),
                                                                           1,
                                                                           0,
                                                                           false)
                                                                       );


            //create result with both groups and pollutants in.
            IEnumerable <IATransfersTreeListRow> result = groupsx.Union(pollutantsx)
                                                          .OrderBy(s => s.GroupCode)
                                                          .ThenBy(s => s.Level)
                                                          .ThenBy(s => s.ConfidentialIndicator)
                                                          .ThenBy(s => s.Code);

            return(result);
        }
Esempio n. 21
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);
        }
Esempio n. 22
0
        ///<summary>
        ///Return all subareas (level 1) that fullfill search criteria.
        ///If countryCodes are null, all subareas will be returned. If countryCodes is empty no subareas will be returned.
        ///</summary>
        public static IEnumerable <AreaTreeListRow> GetSubAreas(PollutantTransfersSearchFilter filter, List <string> countryCodes)
        {
            if (countryCodes != null && countryCodes.Count() == 0)
            {
                return(new List <AreaTreeListRow>());
            }

            DataClassesPollutantTransferDataContext      db     = getPollutantTransferDataContext();
            Expression <Func <POLLUTANTTRANSFER, bool> > lambda = getActivityAreaLambda(filter);

            //add countries to expression
            if (countryCodes != null)
            {
                ParameterExpression param      = lambda.Parameters[0];
                Expression          countryExp = LinqExpressionBuilder.GetInExpr(param, "CountryCode", countryCodes);

                Expression exp = LinqExpressionBuilder.CombineAnd(lambda.Body, countryExp);
                lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);
            }

            //find data for sub-activity level, this level never has children.

            AreaFilter.RegionType regionType = filter.AreaFilter.TypeRegion;
            bool isRbd = AreaFilter.RegionType.RiverBasinDistrict.Equals(regionType);

            IEnumerable <AreaTreeListRow> subareas = db.POLLUTANTTRANSFERs.Where(lambda)
                                                     .GroupBy(p => new { CountryCode = p.CountryCode, SubAreaCode = isRbd ? p.RiverBasinDistrictCode : p.NUTSLevel2RegionCode, PollutantCode = p.PollutantCode })
                                                     .Select(x => new AreaTreeListRow(
                                                                 x.Key.CountryCode,
                                                                 !x.Key.SubAreaCode.Equals(null) ? x.Key.SubAreaCode : AreaTreeListRow.CODE_UNKNOWN,
                                                                 regionType,
                                                                 x.Key.PollutantCode,
                                                                 x.Count(),
                                                                 x.Sum(p => p.Quantity),
                                                                 false));

            return(subareas);
        }
Esempio n. 23
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);
        }
Esempio n. 24
0
        /// <summary>
        /// Get list for confidential transfers
        /// </summary>
        public static IEnumerable <ConfidentialTransfersRow> GetConfidentialTransfersFacility(IndustrialActivitySearchFilter filter)
        {
            DataClassesPollutantTransferDataContext      db     = getPollutantTransferDataContext();
            Expression <Func <POLLUTANTTRANSFER, bool> > lambda = getPollutantTransfersLambda(db, filter);
            var pollutantAll          = db.POLLUTANTTRANSFERs.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(),
                facilities     = p.Count(x => x.Quantity != null && x.Quantity >= 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(),
                facilities     = p.Count(x => x.Quantity != null && x.Quantity >= 0),
            };

            // merge results
            List <ConfidentialTransfersRow> final = new List <ConfidentialTransfersRow>();

            foreach (var v1 in conf2)
            {
                foreach (var v2 in all2)
                {
                    if (v1.pollutantGroup.Equals(v2.pollutantGroup))
                    {
                        ConfidentialTransfersRow cr = new ConfidentialTransfersRow(v1.pollutantGroup);
                        cr.FacilitiesConfidential = v1.facilities; // set confidential
                        cr.Facilities             = v2.facilities; // set not confidential (all)
                        final.Add(cr);
                        break;
                    }
                }
            }

            return(final);


            //var pollutantConfOn = from c in pollutantConfAll where (c.ConfidentialIndicator != null && c.ConfidentialIndicator == true) select c;
            //var pollutantConfOff = from c in pollutantConfAll where c.ConfidentialIndicator == false select c;

            // select distinct list of pollutnat codes

            /*IEnumerable<string> pollutantcodes = (from p in pollutantConfAll select p.PollutantGroupCode).Distinct();
             *
             * // loop through all pollutants
             * List<ConfidentialTransfersRow> final = new List<ConfidentialTransfersRow>();
             * foreach (string s in pollutantcodes)
             * {
             *  ConfidentialTransfersRow ct = new ConfidentialTransfersRow(s);
             *  ct.FacilitiesConfidential = (from c in pollutantConfOn where c.PollutantGroupCode == s && c.Quantity != null select c).Count();
             *  ct.Facilities = (from c in pollutantConfOff where c.PollutantGroupCode == s && c.Quantity != null select c).Count();
             *  // add confidential to the total count of facilities
             *  ct.Facilities += ct.FacilitiesConfidential;
             *  final.Add(ct);
             * }
             *
             * return final;
             */
        }
Esempio n. 25
0
        /// <summary>
        /// get lambda for confidential pollutant transfers
        /// </summary>
        private static Expression <Func <POLLUTANTTRANSFER, bool> > getPollutantTransfersConfidentialLambda(DataClassesPollutantTransferDataContext db, IndustrialActivitySearchFilter filter, bool includePollutant)
        {
            ParameterExpression            param           = Expression.Parameter(typeof(POLLUTANTTRANSFER), "s");
            PollutantTransfersSearchFilter filterTransfers = FilterConverter.ConvertToPollutantTransfersSearchFilter(filter);
            Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantTransfersConfidential(filterTransfers, param, includePollutant);
            Expression <Func <POLLUTANTTRANSFER, bool> > lambda = Expression.Lambda <Func <POLLUTANTTRANSFER, bool> >(exp, param);

            return(lambda);
        }