Beispiel #1
0
        public static List <FacilityCSV> GetFacilityListCSV(FacilitySearchFilter filter)
        {
            if (filter == null)
            {
                return(null);
            }

            // db handler
            DataClassesFacilityDataContext db = getDataContext();

            // Create basic expression
            Expression <Func <FACILITYSEARCH_ALL, bool> > lambda = getLambdaExpression(filter);

            //get data for correct type
            IQueryable <FACILITYSEARCH_MAINACTIVITY> distinctFacilities = getDistinctFacilities(db, lambda);
            IEnumerable <FacilityCSV> dataDistinct = distinctFacilities.orderBy("FacilityName", false).Select <FACILITYSEARCH_MAINACTIVITY, FacilityCSV>(v =>
                                                                                                                                                         new FacilityCSV(v.ReportingYear,
                                                                                                                                                                         v.FacilityReportID,
                                                                                                                                                                         v.NationalID,
                                                                                                                                                                         v.FacilityID,
                                                                                                                                                                         v.FacilityName,
                                                                                                                                                                         v.ParentCompanyName,
                                                                                                                                                                         v.PostalCode,
                                                                                                                                                                         v.Address,
                                                                                                                                                                         v.City,
                                                                                                                                                                         v.IAActivityCode,
                                                                                                                                                                         v.CountryCode,
                                                                                                                                                                         v.RiverBasinDistrictCode,
                                                                                                                                                                         v.NUTSLevel2RegionCode,
                                                                                                                                                                         v.ConfidentialIndicator));

            return(dataDistinct.ToList());
        }
Beispiel #2
0
        //D30 START 16/05/2013
        /// <summary>
        /// Select distinct FacilityIDs
        /// </summary
        public static IEnumerable <int> GetFacilityDetailsID()
        {
            DataClassesFacilityDataContext db = getDataContext();
            IEnumerable <int> data            = db.FACILITYSEARCH_ALLs.Select(p => p.FacilityID).Distinct();

            return(data);
        }
Beispiel #3
0
        /// <summary>
        /// Determine whether its a an EPER year
        /// </summary>
        /// <param name="facilityReportId"></param>
        /// <returns>True if given facility report id belongs to an EPER year, otherwise False</returns>
        public static bool IsEPERReportingYear(int facilityReportId)
        {
            DataClassesFacilityDataContext db       = new DataClassesFacilityDataContext();
            FACILITYDETAIL_DETAIL          facility = db.FACILITYDETAIL_DETAILs.Where(x => x.FacilityReportID == facilityReportId).FirstOrDefault();

            return(facility != null ? (facility.ReportingYear < 2007) : false);
        }
Beispiel #4
0
        /// <summary>
        /// get pollutant transfers for a specific facility report
        /// </summary>
        public static IEnumerable <FACILITYDETAIL_POLLUTANTTRANSFER> GetPollutantTransfers(int reportID)
        {
            DataClassesFacilityDataContext db = getDataContext();
            IEnumerable <FACILITYDETAIL_POLLUTANTTRANSFER> data = db.FACILITYDETAIL_POLLUTANTTRANSFERs.Where(p => p.FacilityReportID == reportID).OrderBy(x => x.PollutantCode);

            return(data);
        }
Beispiel #5
0
        private static DataClassesFacilityDataContext getFacilityDataContext()
        {
            DataClassesFacilityDataContext db = new DataClassesFacilityDataContext();

            db.Log = new DebuggerWriter();
            return(db);
        }
Beispiel #6
0
        public static IEnumerable <FACILITYDETAIL_COMPETENTAUTHORITYPARTY> GetFacilityCompetentAuthority(int facilityReportId)
        {
            DataClassesFacilityDataContext db = getDataContext();

            IEnumerable <FACILITYDETAIL_COMPETENTAUTHORITYPARTY> data = db.FACILITYDETAIL_COMPETENTAUTHORITYPARTies.Where(p => p.FacilityReportID == facilityReportId);

            return(data);
        }
Beispiel #7
0
        /// <summary>
        /// get facility ID and Facility name (is for the service of the Xquery)
        /// </summary>
        /// <param name="facilityReportId"></param>
        /// <param name="facilityName"></param>
        /// <returns></returns>
        public static IEnumerable <VALIDATION_FACILITY> GetFacilityID(string facilityNationalId, int Year, string countryCode)
        {
            DataClassesFacilityDataContext db = getDataContext();

            IEnumerable <VALIDATION_FACILITY> data = db.VALIDATION_FACILITYes.Where(p => p.NationalID == facilityNationalId && p.ReportingYear == Year && p.Code == countryCode);

            return(data);
        }
Beispiel #8
0
        /// <summary>
        /// get waste transfers for a specific facility report and wste type
        /// </summary>
        public static IEnumerable <FACILITYDETAIL_WASTETRANSFER> GetWasteTransfers(int reportID, WasteTypeFilter.Type type)
        {
            string wasteType = EnumUtil.GetStringValue(type);
            DataClassesFacilityDataContext             db   = getDataContext();
            IEnumerable <FACILITYDETAIL_WASTETRANSFER> data = db.FACILITYDETAIL_WASTETRANSFERs.Where(c => c.FacilityReportID == reportID && c.WasteTypeCode.Equals(wasteType));

            return(data);
        }
Beispiel #9
0
        public static IEnumerable <FACILITYDETAIL_ACTIVITY> GetActivities(int facailityReportID)
        {
            DataClassesFacilityDataContext db = getDataContext();

            IEnumerable <FACILITYDETAIL_ACTIVITY> data = db.FACILITYDETAIL_ACTIVITies.Where(c => c.FacilityReportID == facailityReportID).OrderBy(c => c.RankingNumeric);

            return(data);
        }
Beispiel #10
0
        // ----------------------------------------------------------------------------------
        // FacilityDetails
        // ----------------------------------------------------------------------------------
        #region FacilityDetails
        public static IEnumerable <FACILITYDETAIL_DETAIL> GetFacilityDetails(int facilityReportId)
        {
            DataClassesFacilityDataContext db = getDataContext();

            IEnumerable <FACILITYDETAIL_DETAIL> data = db.FACILITYDETAIL_DETAILs.Where(p => p.FacilityReportID == facilityReportId);

            return(data);
        }
Beispiel #11
0
        //D30 END 16/05/2013
        /// <summary>
        /// Get pollutant releases for a specific facility report and medium
        /// </summary>
        public static IEnumerable <FACILITYDETAIL_POLLUTANTRELEASE> GetPollutantReleases(int reportID, MediumFilter.Medium medium)
        {
            string pollutantTo = EnumUtil.GetStringValue(medium);

            DataClassesFacilityDataContext db = getDataContext();
            IEnumerable <FACILITYDETAIL_POLLUTANTRELEASE> data = db.FACILITYDETAIL_POLLUTANTRELEASEs.Where(p => p.FacilityReportID == reportID && p.PollutantTo.Equals(pollutantTo)).OrderBy(x => x.PollutantCode);

            return(data);
        }
Beispiel #12
0
        private static IQueryable <int> getDistinctFacilityReportIds(DataClassesFacilityDataContext db, Expression <Func <FACILITYSEARCH_ALL, bool> > lambda)
        {
            //prepare inner query for distinct facilityreportids.
            IQueryable <int> facilityReportIds = db.FACILITYSEARCH_ALLs.
                                                 Where(lambda).
                                                 Select <FACILITYSEARCH_ALL, int>(d => d.FacilityReportID).Distinct <int>();

            return(facilityReportIds);
        }
Beispiel #13
0
        /// <summary>
        /// Returns basic inforamtion about a facility given by facilityId and reportingyear
        /// </summary>
        public static FacilityBasic GetFacilityBasic(int facilityId, int reportingYear)
        {
            DataClassesFacilityDataContext db = getDataContext();

            IEnumerable <FacilityBasic> data = from d in db.FACILITYDETAIL_DETAILs
                                               where d.FacilityID == facilityId && d.ReportingYear == reportingYear
                                               select new FacilityBasic(d.FacilityReportID, d.FacilityID, d.NationalID, d.FacilityName, d.Address, d.City, d.PostalCode, d.CountryCode, d.ConfidentialIndicator, d.ReportingYear, d.Published, d.ConfidentialIndicatorPollutantRelease, d.ConfidentialIndicatorPollutantTransfer, d.ConfidentialIndicatorWaste);

            return(data.FirstOrDefault());
        }
Beispiel #14
0
        //RRP START 18-04-2013
        /// <summary>
        /// Returns Max Year of the facilityId
        /// </summary>
        public static int GetMaxYearFacilityId(int facilityId)
        {
            DataClassesFacilityDataContext db = getDataContext();

            IEnumerable <int> data = from d in db.FACILITYDETAIL_DETAILs
                                     where d.FacilityID == facilityId
                                     select db.FACILITYDETAIL_DETAILs.Max(x => x.ReportingYear);

            return(data.FirstOrDefault());
        }
Beispiel #15
0
        public static IEnumerable <FacilityReportingYear> GetReportingYears(int facilityId)
        {
            DataClassesFacilityDataContext      db  = getDataContext();
            IEnumerable <FacilityReportingYear> res = from r in db.FACILITYDETAIL_DETAILs
                                                      where r.FacilityID == facilityId //&& (r.ReportingYear != 2001 && r.ReportingYear != 2004)
                                                      select new FacilityReportingYear(r.ReportingYear, r.FacilityReportID, facilityId);

            res = res.OrderBy(p => p.ReportingYear);
            return(res);
        }
Beispiel #16
0
        // ----------------------------------------------------------------------------------
        // Confidentiality
        // ----------------------------------------------------------------------------------
        #region Confidentiality
        public static bool IsAffectedByConfidentiality(FacilitySearchFilter filter)
        {
            DataClassesFacilityDataContext db    = getDataContext();
            ParameterExpression            param = Expression.Parameter(typeof(FACILITYSEARCH_ALL), "s");
            Expression exp = LinqExpressionBuilder.GetLinqExpressionFacilitySearchConfidential(filter, param);

            // create lambda according to filters
            Expression <Func <FACILITYSEARCH_ALL, bool> > lambda = Expression.Lambda <Func <FACILITYSEARCH_ALL, bool> >(exp, param);

            // return true if found any
            return(getDistinctFacilityReportIds(db, lambda).Any());
        }
Beispiel #17
0
        // ----------------------------------------------------------------------------------
        // FacilityList
        // ----------------------------------------------------------------------------------
        #region FacilityList

        /// <summary>
        /// Return facility list
        /// </summary>
        public static List <FacilityRow> FacilityList(FacilitySearchFilter filter, string column, bool descending, int startRowIndex, int maxRows)
        {
            // Create basic expression
            Expression <Func <FACILITYSEARCH_ALL, bool> > lambda = getLambdaExpression(filter);

            // get number of distinct facilities for this search
            DataClassesFacilityDataContext db = getDataContext();

            filter.Count = getDistinctFacilityReportIds(db, lambda).Count();

            IEnumerable <FacilityRow> dataDistinct = null;

            if (filter.Count > 0)
            {
                //get data of correct type
                IQueryable <FACILITYSEARCH_MAINACTIVITY> distinctFacilities = getDistinctFacilities(db, lambda);
                dataDistinct = distinctFacilities.orderBy(column, descending)
                               .Skip(startRowIndex).Take(maxRows)
                               .Select <FACILITYSEARCH_MAINACTIVITY, FacilityRow>(v => new FacilityRow(v.FacilityID,
                                                                                                       v.FacilityReportID,
                                                                                                       v.FacilityName,
                                                                                                       v.PostalCode,
                                                                                                       v.Address,
                                                                                                       v.City,
                                                                                                       v.IAActivityCode,
                                                                                                       v.CountryCode,
                                                                                                       v.ConfidentialIndicator,
                                                                                                       v.ReportingYear));
            }

            //add rows to result. Speedup paging by adding empty rows at the start and end of list.
            List <FacilityRow> result = new List <FacilityRow>();

            for (int i = 0; i < startRowIndex; i++)
            {
                result.Add(null);
            }

            if (dataDistinct != null)
            {
                result.AddRange(dataDistinct);
            }

            int addcount = result.Count;

            for (int i = 0; i < filter.Count - addcount; i++)
            {
                result.Add(null);
            }

            return(result);
        }
Beispiel #18
0
        /// <summary>
        /// Returns the number of facilities corresponding to the filter. Always use FACILITY_MAIN table, since it has the fewest records.
        /// </summary>
        public static int GetFacilityCount(IndustrialActivitySearchFilter filter)
        {
            DataClassesFacilityDataContext db    = getFacilityDataContext();
            ParameterExpression            param = Expression.Parameter(typeof(FACILITYSEARCH_MAINACTIVITY), "s");
            Expression exp = LinqExpressionBuilder.GetLinqExpressionIndustrialActivitySearch(filter, param);
            Expression <Func <FACILITYSEARCH_MAINACTIVITY, bool> > lambda = Expression.Lambda <Func <FACILITYSEARCH_MAINACTIVITY, bool> >(exp, param);

            //find total no. of facilities. Table only have one record per facility, so distinct is not needed.
            int count = db.FACILITYSEARCH_MAINACTIVITies
                        .Where(lambda)
                        .Count();

            return(count);
        }
Beispiel #19
0
        /// <summary>
        /// returns the MapFilter (sql and layers) corresponding to the filter.
        /// </summary>
        public static MapFilter GetMapFilter(IndustrialActivitySearchFilter filter)
        {
            DataClassesFacilityDataContext db    = getFacilityDataContext();
            ParameterExpression            param = Expression.Parameter(typeof(FACILITYSEARCH_MAINACTIVITY), "s");
            Expression exp = LinqExpressionBuilder.GetLinqExpressionIndustrialActivitySearch(filter, param);
            Expression <Func <FACILITYSEARCH_MAINACTIVITY, bool> > lambda = Expression.Lambda <Func <FACILITYSEARCH_MAINACTIVITY, 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);
        }
Beispiel #20
0
        /// <summary>
        /// Return the number of countries having reported per year for the area filter given
        /// </summary>
        /// <param name="areaFilter"></param>
        /// <returns></returns>
        public static IEnumerable <ReportingCountries> GetReportingCountries(AreaFilter areaFilter)
        {
            DataClassesFacilityDataContext db = getDataContext();

            ParameterExpression param = Expression.Parameter(typeof(FACILITYSEARCH_MAINACTIVITY), "s");
            Expression          exp   = LinqExpressionBuilder.GetLinqExpressionArea(areaFilter, param);

            // create lambda according to filters
            Expression <Func <FACILITYSEARCH_MAINACTIVITY, bool> > lambda = Expression.Lambda <Func <FACILITYSEARCH_MAINACTIVITY, bool> >(exp, param);

            IEnumerable <ReportingCountries> years = db.FACILITYSEARCH_MAINACTIVITies.Where(lambda)
                                                     .GroupBy(f => f.ReportingYear)
                                                     .Select(s => new  ReportingCountries(
                                                                 s.Key,
                                                                 s.Select(x => x.CountryCode).Distinct().Count()));

            return(years);
        }
Beispiel #21
0
        private static IQueryable <FACILITYSEARCH_MAINACTIVITY> getDistinctFacilities(DataClassesFacilityDataContext db, Expression <Func <FACILITYSEARCH_ALL, bool> > lambda)
        {
            //prepare inner query for distinct facilityreportids.
            IQueryable <int> facilityReportIds = getDistinctFacilityReportIds(db, lambda);

            //get data - using inner query
            //IQueryable<FACILITYSEARCH_MAINACTIVITY> dataDistinct = db.FACILITYSEARCH_MAINACTIVITies.
            //                                        Where(d => facilityReportIds.Contains(d.FacilityReportID));

            //get data - using inner join
            IQueryable <FACILITYSEARCH_MAINACTIVITY> dataDistinct = from f in db.FACILITYSEARCH_MAINACTIVITies
                                                                    join fid in facilityReportIds on f.FacilityReportID equals fid
                                                                    select f;

            return(dataDistinct);
        }