/// <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);
}
public void Linq_QueryBuilder_select_data_test_with_nHibernate_NotEager()
{
JarsJob jj = new JarsJob();
var _repository = _repFactory.GetDataRepository <IGenericEntityRepositoryBase <JarsJob, IDataContextNhJars> >();
Expression <Func <JarsJob, bool> > query = null;
bool hasWhere = false;
int Id = 1;
string ExtRefId = "11";
//Id
if (Id != 0)
{
query = LinqExpressionBuilder.True <JarsJob>().And(j => j.Id == Id);
hasWhere = true;
}
//ExtRefId
if (ExtRefId != "0")
{
if (!hasWhere)
{
query = LinqExpressionBuilder.True <JarsJob>().And(j => j.ExtRefId == ExtRefId);
hasWhere = true;
}
else
{
query.And(j => j.ExtRefId == ExtRefId);
}
}
var res = _repository.Where(query);
Assert.IsNotNull(res);
int[] ids = new[] { 1, 2, 3 };
query = LinqExpressionBuilder.True <JarsJob>().And(j => ids.ToList().Contains(j.Id));
var rIn = _repository.Where(query);
Assert.IsNotNull(rIn);
query = LinqExpressionBuilder.True <JarsJob>().And(j => j.LineOfWork.Like("MU%"));
var rLike = _repository.Where(query);
Assert.IsNotNull(rLike);
}
// ----------------------------------------------------------------------------------
// 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);
}
private static Expression <Func <POLLUTANTRELEASE, bool> > getLambdaExpression(PollutantReleasesTimeSeriesFilter filter, MediumFilter.Medium medium)
{
// apply filter
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTRELEASE), "p");
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantReleases(filter, param);
//apply medium condition
Expression expMedium = LinqExpressionBuilder.GetLinqExpressionMediumRelease(medium, param);
exp = LinqExpressionBuilder.CombineAnd(exp, expMedium);
Expression <Func <POLLUTANTRELEASE, bool> > lambda = Expression.Lambda <Func <POLLUTANTRELEASE, bool> >(exp, param);
return(lambda);
}
public ReadOnlyClassAutoProperty(ClassAutoProperty property)
{
this.property = property;
propertyType = new ReadOnlyTypeReference(property.Type);
if (property.EmptyAccessors != null)
{
getAccessor = new ReadOnlyClassAccessor(property.EmptyAccessors.GetAccessorVisibility);
setAccessor = new ReadOnlyClassAccessor(property.EmptyAccessors.SetAccessorVisibility);
}
if (property.InitialValue != null)
{
initialValue = LinqExpressionBuilder.BuildExpression(property.InitialValue);
}
}
/// <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>
/// 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);
}
/// <summary>
/// A helper method used for building the query, depending on if the LoadLazy option was set.
/// </summary>
/// <typeparam name="T">A query over object required by the QueryOver Method on the repository</typeparam>
/// <param name="request">The same request passed into the service</param>
/// <returns></returns>
private Expression <Func <JarsJob, bool> > BuildQuery(FindJarsJobs request)
{
Expression <Func <JarsJob, bool> > query = LinqExpressionBuilder.True <JarsJob>();
//Id
if (request.Id != 0)
{
query = LinqExpressionBuilder.True <JarsJob>().And(j => j.Id == request.Id);
}
//ExtRefID
if (request.ExtRefID != "0")
{
query = query.And(j => j.ExtRefId == request.ExtRefID);
}
//ResourceID
if (request.ResourceId != 0)
{
query = query.And(j => j.ResourceId == request.ResourceId);
}
//Location
if (request.Location != null)
{
query = query.And(j => j.Location == request.Location);
}
//CompletionDate
if (request.ProgressStatus != null)
{
query = query.And(j => j.ProgressStatus == request.ProgressStatus);
}
//StartDateTime
if (request.StartDate != DateTime.MinValue)
{
query = query.And(j => j.StartDate >= request.StartDate);
}
//EndDateTime
if (request.EndDateTime != DateTime.MinValue)
{
query = query.And(j => j.EndDate <= request.EndDateTime);
}
return(query);
}
public static MapFilter GetMapJavascriptFilter(PollutantReleasesTimeSeriesFilter filter)
{
//parameter must be "p" to match map config file
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTRELEASE), "p");
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantReleases(filter, param);
Expression <Func <POLLUTANTRELEASE, bool> > lambda = Expression.Lambda <Func <POLLUTANTRELEASE, bool> >(exp, param);
// create sql and sectors to map. Do not remove parameter prefix.
MapFilter mapFilter = new MapFilter();
mapFilter.SqlWhere = LinqExpressionBuilder.GetSQLJavascript(lambda.Body, null);
mapFilter.SqlWhere = "FacilityReportID IN (select p.FacilityReportID from POLLUTANTRELEASE p where " + mapFilter.SqlWhere + ")";
mapFilter.SetLayers(filter.ActivityFilter);
return(mapFilter);
}
public static MapFilter GetMapJavascriptFilter(WasteTransferTimeSeriesFilter filter)
{
//parameter must be "p" to match map config file
ParameterExpression param = Expression.Parameter(typeof(WASTETRANSFER), "p");
Expression exp = LinqExpressionBuilder.GetLinqExpressionWasteTransfer(filter, param, false);
Expression <Func <WASTETRANSFER, bool> > lambda = Expression.Lambda <Func <WASTETRANSFER, bool> >(exp, param);
// create sql and sectors to map. Do not remove parameter prefix.
MapFilter mapFilter = new MapFilter();
mapFilter.SqlWhere = LinqExpressionBuilder.GetSQLJavascript(lambda.Body, null);
mapFilter.SqlWhere = "FacilityReportID IN (select p.FacilityReportID from WASTETRANSFER p where " + mapFilter.SqlWhere + ")";
mapFilter.SetLayers(filter.ActivityFilter);
return(mapFilter);
}
/// <summary>
/// A helper method used for building the query, depending on if the LoadLazy option was set.
/// </summary>
/// <typeparam name="T">A query over object required by the QueryOver Method on the repository</typeparam>
/// <param name="request">The same request passed into the service</param>
/// <param name="hasWhere">a bool value indicating if there is a where string</param>
/// <returns></returns>
private Expression <Func <T, bool> > BuildQuery <T>(FindJarsRules request) where T : JarsRule
{
Expression <Func <T, bool> > query = LinqExpressionBuilder.True <T>();
if (!request.TargetEntityTypeName.IsNullOrEmpty())
{
query = query.And(o => o.TargetTypeName == request.TargetEntityTypeName);
}
if (!request.SourceEntityTypeName.IsNullOrEmpty())
{
query = query.And(o => o.SourceTypeName == request.SourceEntityTypeName);
}
return(query);
}
public static IQueryable <T> Filter <T>(this IQueryable <T> query, EntityFilter filter)
{
if (filter == null)
{
return(query);
}
var builder = new LinqExpressionBuilder();
builder.Build(filter);
var predicate = builder.Expression;
var parameters = builder.Parameters.ToArray();
return(query.Where(predicate, parameters));
}
/// <summary>
/// Return all AnnexI activities within the ids given
/// </summary>
public static IEnumerable <LOV_ANNEXIACTIVITY> GetAnnexIActivities(List <int> lovAnnexIActivityIds)
{
if (lovAnnexIActivityIds.Count() > 0)
{
ParameterExpression param = Expression.Parameter(typeof(LOV_ANNEXIACTIVITY), "s");
Expression exp = LinqExpressionBuilder.GetInExpr(param, "LOV_AnnexIActivityID", lovAnnexIActivityIds);
Expression <Func <LOV_ANNEXIACTIVITY, bool> > lambda = Expression.Lambda <Func <LOV_ANNEXIACTIVITY, bool> >(exp, param);
IEnumerable <LOV_ANNEXIACTIVITY> result = getLovAnnexIActivityCache().AsQueryable <LOV_ANNEXIACTIVITY>().Where(lambda);
return(result);
}
else
{
return(new List <LOV_ANNEXIACTIVITY>());
}
}
public void FilterNormal()
{
var entityFilter = new EntityFilter {
Name = "Rank", Value = 7
};
var builder = new LinqExpressionBuilder();
builder.Build(entityFilter);
builder.Expression.Should().NotBeEmpty();
builder.Expression.Should().Be("Rank == @0");
builder.Parameters.Count.Should().Be(1);
builder.Parameters[0].Should().Be(7);
}
/// <summary>
/// return total list of areas
/// </summary>
public static IEnumerable <IAReleasesTreeListRow> GetPollutantReleases(IndustrialActivitySearchFilter filter)
{
DataClassesPollutantReleaseDataContext db = getPollutantReleaseDataContext();
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTRELEASE), "s");
PollutantReleaseSearchFilter filterRelease = FilterConverter.ConvertToPollutantReleaseSearchFilter(filter);
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantReleases(filterRelease, param);
Expression <Func <POLLUTANTRELEASE, bool> > lambda = Expression.Lambda <Func <POLLUTANTRELEASE, bool> >(exp, param);
int facilitiesCount = 0;
List <IAReleasesTreeListRow> pollutants = getReleases(db, lambda, out facilitiesCount).ToList <IAReleasesTreeListRow>();
filter.Count = facilitiesCount;
return(pollutants);
}
/// <summary>
/// Find a BOSRecord by specifying values for the properties available in the request.
/// Date values: Start date will go from date forward, end date will be from end date back, and if both has values,
/// it will be used as from (between) start to end
/// </summary>
/// <param name="request">The request used for building the find parameters</param>
/// <returns>If LoadLazy was true, then a list of JarsBOSRecordBase items, otherwise a list of fully loaded JarsBOSRecords</returns>
public virtual BOSEntitiesResponse Any(FindBOSEntities request)
{
BOSEntitiesResponse response = new BOSEntitiesResponse();
if (request != null)
{
Expression <Func <BOSEntity, bool> > query = LinqExpressionBuilder.True <BOSEntity>();
//Id
if (request.ResourceId != null)
{
query = query.And(a => a.ResourceId == int.Parse(request.ResourceId));
}
//StartDateTime
if (request.StartDate.HasValue && request.StartDate != DateTime.MinValue)
{
query = query.And(a => a.StartDate.Date >= request.StartDate.Value.Date);
}
//EndDateTime (unlike the mobile version we dont set the end date = start date if the end date is empty)
if (request.EndDate.HasValue && request.EndDate != DateTime.MinValue)
{
query = query.And(a => a.EndDate.Date <= request.EndDate.Value.Date);
}
//statuses
if (!request.Statuses.IsNullOrEmpty())
{
//if the status value contains a , then split it, otherwise just use as is
if (request.Statuses.Contains(','))
{
string[] arrStatus = request.Statuses.Split(new[] { ',' });
query = query.And(a => arrStatus.Contains(a.ProgressStatus));
}
else
{
query = query.And(a => a.ProgressStatus == request.Statuses);
}
}
IGenericEntityRepositoryBase <BOSEntity, IDataContextBOS> _repository = _DataRepositoryFactory.GetDataRepository <IGenericEntityRepositoryBase <BOSEntity, IDataContextBOS> >();
response.BOSEntities = _repository.Where(query, true).ConvertAllTo <BOSEntityDto>().ToList();
}
return(response);
}
/// <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);
}
public void FilterContains()
{
var entityFilter = new EntityFilter
{
Name = "Name",
Operator = "Contains",
Value = "Berry"
};
var builder = new LinqExpressionBuilder();
builder.Build(entityFilter);
builder.Expression.Should().NotBeEmpty();
builder.Expression.Should().Be("Name.Contains(@0)");
builder.Parameters.Count.Should().Be(1);
builder.Parameters[0].Should().Be("Berry");
}
/// <summary>
/// A helper method used for building the query, depending on if the LoadLazy option was set.
/// </summary>
/// <typeparam name="T">A query over object required by the QueryOver Method on the repository</typeparam>
/// <param name="request">The same request passed into the service</param>
/// <param name="hasWhere">a bool value indicating if there is a where string</param>
/// <returns></returns>
private Expression <Func <ApptLabel, bool> > BuildQuery <T>(FindApptLabels request)
{
Expression <Func <ApptLabel, bool> > query = LinqExpressionBuilder.True <ApptLabel>();
//DisplayName
if (request.ViewType != null && request.ViewType != string.Empty)
{
query = query.And(j => j.ViewName == request.ViewType);
}
//IsActive
if (request.LabelName != null && request.LabelName != string.Empty)
{
query = query.And(o => o.LabelName.Like($"{request.LabelName}%"));
}
return(query);
}
public void FilterNotEndsWith()
{
var entityFilter = new EntityFilter
{
Name = "Name",
Operator = "!EndsWith",
Value = "berry"
};
var builder = new LinqExpressionBuilder();
builder.Build(entityFilter);
builder.Expression.Should().NotBeEmpty();
builder.Expression.Should().Be("!Name.EndsWith(@0)");
builder.Parameters.Count.Should().Be(1);
builder.Parameters[0].Should().Be("berry");
}
public void FilterIn()
{
var entityFilter = new EntityFilter
{
Name = "Name",
Operator = "in",
Value = new [] { "Test", "Tester" }
};
var builder = new LinqExpressionBuilder();
builder.Build(entityFilter);
builder.Expression.Should().NotBeEmpty();
builder.Expression.Should().Be("it.Name in @0");
builder.Parameters.Count.Should().Be(1);
builder.Parameters[0].Should().BeOfType <string[]>();
}
// ---------------------------------------------------------------------------------------------------
// Count method
// ---------------------------------------------------------------------------------------------------
#region count
/// <summary>
/// Returns the number of facilities corresponding to the filter, per waste type
/// Overloaded with TimeSeries filter for ease of use.
/// </summary>
public static QueryLayer.WasteTransfers.FacilityCountObject GetCountFacilities(WasteTransferTimeSeriesFilter tsFilter)
{
// conversion removes all year span information
var filter = FilterConverter.ConvertToWasteTransferSearchFilter(tsFilter);
ParameterExpression param = Expression.Parameter(typeof(WASTETRANSFER_TREATMENT), "s");
Expression exp = LinqExpressionBuilder.GetLinqExpressionWasteTransferSearch(filter, param, true);
// Exclude EPER reporting years
Expression prop = Expression.Property(param, "ReportingYear");
Expression val = Expression.Constant(2007);
Expression yearExp = Expression.GreaterThanOrEqual(prop, val);
exp = Expression.AndAlso(yearExp, exp);
Expression <Func <WASTETRANSFER_TREATMENT, bool> > lambda = Expression.Lambda <Func <WASTETRANSFER_TREATMENT, bool> >(exp, param);
return(WasteTransfers.GetFacilityCounts(lambda));
}
// ---------------------------------------------------------------------------------------------------
// Facility couting
// ---------------------------------------------------------------------------------------------------
#region Facility counting
/// <summary>
/// Returns the number of facilities for each medium type.
/// </summary>
public static PollutantReleases.FacilityCountObject GetFacilityCounts(PollutantReleasesTimeSeriesFilter tsFilter)
{
// removes all year span information
var filter = FilterConverter.ConvertToPollutantReleaseSearchFilter(tsFilter);
ParameterExpression param = Expression.Parameter(typeof(POLLUTANTRELEASE), "POLLUTANTRELEASE");
Expression exp = LinqExpressionBuilder.GetLinqExpressionPollutantReleases(filter, param);
// Exclude EPER reporting years
Expression prop = Expression.Property(param, "ReportingYear");
Expression val = Expression.Constant(2007);
Expression yearExp = Expression.GreaterThanOrEqual(prop, val);
exp = Expression.AndAlso(yearExp, exp);
Expression <Func <POLLUTANTRELEASE, bool> > lambda = Expression.Lambda <Func <POLLUTANTRELEASE, bool> >(exp, param);
return(PollutantReleases.GetFacilityCounts(lambda, filter.MediumFilter));
}
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);
}
/// <summary>
/// A helper method used for building the query, depending on if the LoadLazy option was set.
/// </summary>
/// <typeparam name="T">A query over object required by the QueryOver Method on the repository</typeparam>
/// <param name="request">The same request passed into the service</param>
/// <param name="hasWhere">a bool value indicating if there is a where string</param>
/// <returns></returns>
private Expression <Func <T, bool> > BuildQuery <T>(FindResources request) where T : Entities.JarsResource
{
Expression <Func <T, bool> > query = LinqExpressionBuilder.True <T>();
//Id
if (request.Id != 0)
{
query = query.And(o => o.Id == request.Id);
}
//ExternalRef
if (request.ExternalRef != null && request.ExternalRef != string.Empty)
{
query = query.And(j => j.ExtRef == request.ExternalRef);
}
//ExternalRef1
if (request.ExternalRef1 != null && request.ExternalRef1 != string.Empty)
{
query = query.And(O => O.ExtRef1 == request.ExternalRef1);
}
//ExternalRef2
if (request.ExternalRef2 != null && request.ExternalRef2 != string.Empty)
{
query = query.And(O => O.ExtRef2 == request.ExternalRef2);
}
//DisplayName
if (request.DisplayName != null && request.DisplayName != string.Empty)
{
query = query.And(j => j.DisplayName == request.DisplayName);
}
//IsActive
if (request.IsActive.HasValue)
{
query = query.And(o => o.IsActive == request.IsActive.Value);
}
return(query);
}
public ApptStatusesResponse Any(FindApptStatuses request)
{
return(ExecuteFaultHandledMethod(() =>
{
ApptStatusesResponse response = new ApptStatusesResponse();
if (request != null)
{
Expression <Func <ApptStatus, bool> > query = LinqExpressionBuilder.True <ApptStatus>();
//DisplayName
if (!request.ViewType.IsNullOrEmpty())
{
query = query.And(j => j.ViewName == request.ViewType);
}
//IsActive
if (!request.StatusName.IsNullOrEmpty())
{
query = query.And(o => o.StatusName.Like($"{request.StatusName}%"));
}
//var _repository = _DataRepositoryFactory.GetDataRepository<IApptStatusRepository>();
var _repository = _DataRepositoryFactory.GetDataRepository <IGenericEntityRepositoryBase <ApptStatus, IDataContextNhJars> >();
IList <ApptStatus> resList = _repository.Where(query).ToList();
if (resList.Count == 0 && request.ViewType != "" && request.StatusName == null)
{
CreateDefaultRecord(request, _repository);
response.Statuses = _repository.Where(query).ToList().ConvertAll(s => s.ConvertTo <ApptStatusDto>());
}
else
{
response.Statuses = resList.ToList().ConvertAll(s => s.ConvertTo <ApptStatusDto>());
}
//call the extension method to remove any sub child parent references.
//response.UpdateChildReferences();
}
return response;
}));
}
// ----------------------------------------------------------------------------------
// 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);
}
/// <summary>
/// A helper method used for building the query, depending on if the LoadLazy option was set.
/// </summary>
/// <typeparam name="T">A query over object required by the QueryOver Method on the repository</typeparam>
/// <param name="request">The same request passed into the service</param>
/// <param name="hasWhere">a bool value indicating if there is a where string</param>
/// <returns></returns>
private Expression <Func <T, bool> > BuildQuery <T>(FindStandardAppointments request) where T : StandardAppointment
{
Expression <Func <T, bool> > query = LinqExpressionBuilder.True <T>();
//the default value of loading recurring appointments will always be added.
query = query.Or(a => a.RecurrenceInfo != null);
//StartDateTime
if (request.FromStartDate.HasValue && request.FromStartDate != DateTime.MinValue)
{
query = query.And(a => a.StartDate >= request.FromStartDate);
}
//EndDateTime
if (request.ToEndDate.HasValue && request.ToEndDate != DateTime.MinValue)
{
query = query.And(a => a.EndDate <= request.ToEndDate);
}
//resourcelist
if (!request.InCalendarForResources.IsNullOrEmpty())
{
string[] resIds = request.InCalendarForResources.Split(',');
query = query.And(a => resIds.Contains(a.ResourceId.ToString()));
}
//with description like
if (request.WithAppointmentDescription != null && request.WithAppointmentDescription != string.Empty)
{
query = query.And(a => a.Subject.Contains(request.WithAppointmentDescription) || a.Description.Contains(request.WithAppointmentDescription));
}
//Id
if (request.Id != 0)
{
query = query.And(a => a.Id == request.Id);
}
return(query);
}
public static IQueryable <T> Filter <T>(this IQueryable <T> query, EntityFilter filter)
{
if (filter == null)
{
return(query);
}
var builder = new LinqExpressionBuilder();
builder.Build(filter);
var predicate = builder.Expression;
var parameters = builder.Parameters.ToArray();
// nothing to filter
if (string.IsNullOrWhiteSpace(predicate))
{
return(query);
}
return(query.Where(predicate, parameters));
}
///<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);
}
