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()); }
//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); }
/// <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); }
/// <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); }
private static DataClassesFacilityDataContext getFacilityDataContext() { DataClassesFacilityDataContext db = new DataClassesFacilityDataContext(); db.Log = new DebuggerWriter(); return(db); }
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); }
/// <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); }
/// <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); }
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); }
// ---------------------------------------------------------------------------------- // 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); }
//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); }
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); }
/// <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()); }
//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()); }
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); }
// ---------------------------------------------------------------------------------- // 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()); }
// ---------------------------------------------------------------------------------- // 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); }
/// <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); }
/// <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); }
/// <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); }
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); }