public static IEnumerable <Field> GetExtraValues(int PeopleId, string location = "default")
{
var fields = GetExtraValues();
var n = 1;
foreach (var f in fields)
{
f.order = n++;
f.peopleid = PeopleId;
if (f.location == null)
{
f.location = "default";
}
}
var exvalues = DbUtil.Db.PeopleExtras.Where(ee => ee.PeopleId == PeopleId).ToList();
var qfields = from f in fields
join v in exvalues on f.name equals v.Field into j
from v in j.DefaultIfEmpty()
where f.location == location || location == null
orderby f.order
select Field.AddField(f, v);
if (location == "default")
{
var qvalues = from v in exvalues
join f in fields on v.Field equals f.name into j
from f in j.DefaultIfEmpty()
where f == null
where !fields.Any(ff => ff.Codes.Any(cc => cc == v.Field))
orderby v.Field
select Field.AddField(f, v);
return(qfields.Concat(qvalues));
}
return(qfields);
}
/// <summary>
/// This function permits to get a ValueObject list by criteria in using SQL Raw.
/// </summary>
/// <remarks>
/// After tests, we have opted for SQL calls directly in the database.
/// </remarks>
/// <param name="datasetId"></param>
/// <param name="select"></param>
/// <param name="where"></param>
/// <param name="sort"></param>
/// <param name="grouping"></param>
/// <param name="page"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public async Task <IEnumerable <ValueObject> > Filter(
string[] select,
string[] where = null,
string[] sort_asc = null,
string[] sort_desc = null,
bool grouping = false,
int?page = null,
int?pageSize = null)
{
// Initialize the entity
DbSet <ValueObject> valueObjectEntity = UnitOfWork.GetDbContext()
.ValueObject;
IEnumerable <ValueObject> valueObjects = valueObjectEntity
.Include(i => i.DataSet)
.AsQueryable();
SqlRawBuilder builder = new SqlRawBuilder();
Field fieldGroup = builder.Select.AddFieldGroup(select);
LeftJoin leftJoin = new LeftJoin();
Table table = new Table();
// Grouping or not
if (grouping)
{
leftJoin = fieldGroup
.AddField("InitialValue", FieldFormatEnum.Sum)
.AddField("CurrentValue", FieldFormatEnum.Sum)
.AddField("FutureValue", FieldFormatEnum.Sum)
.AddLeftJoin("ValueObject", "DataSetId", "DataSet", "Id");
Having clause = leftJoin.AddGroupBy()
.AddFieldGroup(select)
.AddHaving();
BuildClause(clause, where);
}
else
{
leftJoin = fieldGroup
.AddField("InitialValue", FieldFormatEnum.normal)
.AddField("CurrentValue", FieldFormatEnum.normal)
.AddField("FutureValue", FieldFormatEnum.normal)
.AddLeftJoin("ValueObject", "DataSetId", "DataSet", "Id");
Where clause = leftJoin
.AddWhere();
BuildClause(clause, where);
}
// Sorting
if (sort_desc != null && sort_desc.Length > 0)
{
leftJoin.AddOrder(sort_desc, SortingEnum.Desc);
}
else if (sort_asc != null && sort_asc.Length > 0)
{
leftJoin.AddOrder(sort_asc, SortingEnum.Asc);
}
// Execute the script
valueObjects = await UnitOfWork.ValueObjectRepository.ExecuteReader(builder.GetSQL);
// Paging the results
if (page != null && page.Value > 0 && pageSize != null && pageSize.Value > 0)
{
valueObjects = valueObjects.Skip(pageSize.Value * page.Value).Take(pageSize.Value);
}
return(await Task.FromResult(valueObjects));
}
