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