public static List <Period> GetPeriods(GetPeriodsRequest request)
{
if (request.PeriodTypeID <= 0)
{
throw new ArgumentException("periodTypeID is not given", nameof(request.PeriodTypeID));
}
var periods = GetAllPeriods(request.PeriodTypeID).AsQueryable();
// (optional) Filter by the customer.
// If the customer is provided, only periods the customer was a part of will be returned.
if (request.CustomerID.HasValue)
{
var customerCreatedDate = GetCustomerCreatedDate(request.CustomerID.Value);
periods = periods.Where(c => c.EndDate >= customerCreatedDate);
}
// (optional) Filter by requested period IDs
if (request.PeriodIDs != null && request.PeriodIDs.Any())
{
periods = periods.Where(p => request.PeriodIDs.Contains(p.PeriodID));
}
// (optional) Filter by periods that have been accepted
if (request.OnlyAcceptedPeriods)
{
periods = periods.Where(p => p.AcceptedDate != null);
}
return(periods.ToList());
}
public static IEnumerable <Period> GetPeriods(GetPeriodsRequest request)
{
var periods = new List <Period>();
using (var context = Exigo.Sql())
{
periods = context.Query <Period>(@"
SELECT p.PeriodTypeID
, p.PeriodID
, p.PeriodDescription
, p.StartDate
, p.EndDate
, p.AcceptedDate
FROM Periods p
WHERE p.PeriodTypeID = @PeriodTypeID
AND p.PeriodID IN @PeriodID
", new
{
PeriodTypeID = request.PeriodTypeID,
PeriodID = request.PeriodIDs.ToList()
}).ToList();
}
// Optionally filter by the customer.
// If the customer is provided, only periods the customer was a part of will be returned.
if (request.CustomerID != null)
{
var customer = new Customer();
using (var context = Exigo.Sql())
{
customer = context.Query <Customer>(@"
SELECT CreatedDate
FROM Customers
WHERE CustomerID = @CustomerID
", new
{
CustomerID = request.CustomerID
}).FirstOrDefault();
}
if (customer != null)
{
periods = periods.Where(c => c.EndDate >= customer.CreatedDate).ToList();
}
}
if (periods == null)
{
return(null);
}
return(periods);
}
public static IEnumerable <Period> GetPeriods(GetPeriodsRequest request)
{
var context = Exigo.OData();
// Setup the query
var query = context.Periods
.Where(c => c.PeriodTypeID == request.PeriodTypeID);
if (request.PeriodIDs.Length > 0)
{
query = query.Where(request.PeriodIDs.ToList().ToOrExpression <Common.Api.ExigoOData.Period, int>("PeriodID"));
}
// Optionally filter by the customer.
// If the customer is provided, only periods the customer was a part of will be returned.
if (request.CustomerID != null)
{
var customer = context.Customers
.Where(c => c.CustomerID == (int)request.CustomerID)
.Select(c => new { c.CreatedDate })
.FirstOrDefault();
if (customer != null)
{
query = query.Where(c => c.EndDate >= customer.CreatedDate);
}
}
// Get the data
var periods = new List <Common.Api.ExigoOData.Period>();
int lastResultCount = 50;
int callsMade = 0;
while (lastResultCount == 50)
{
var results = query.Select(c => c)
.Skip(callsMade * 50)
.Take(50)
.Select(c => c)
.ToList();
results.ForEach(c => periods.Add(c));
callsMade++;
lastResultCount = results.Count;
}
foreach (var period in periods)
{
yield return((Period)period);
}
}
public static IEnumerable <Period> GetPeriods(GetPeriodsRequest request)
{
#region ODATA Query
//var context = Exigo.OData();
//// Setup the query
//var query = context.Periods
// .Where(c => c.PeriodTypeID == request.PeriodTypeID);
//if (request.PeriodIDs.Length > 0)
//{
// query = query.Where(request.PeriodIDs.ToList().ToOrExpression<Common.Api.ExigoOData.Period, int>("PeriodID"));
// PeriodIDs = string.Join(",", request.PeriodIDs);
//}
//// Optionally filter by the customer.
//// If the customer is provided, only periods the customer was a part of will be returned.
//if (request.CustomerID != null)
//{
// CustomerID = (int)request.CustomerID;
// var customer = context.Customers
// .Where(c => c.CustomerID == (int)request.CustomerID)
// .Select(c => new { c.CreatedDate })
// .FirstOrDefault();
// if (customer != null)
// {
// query = query.Where(c => c.EndDate >= customer.CreatedDate);
// }
//}
//// Get the data
//var periods = new List<Common.Api.ExigoOData.Period>();
//int lastResultCount = 50;
//int callsMade = 0;
//while (lastResultCount == 50)
//{
// var results = query.Select(c => c)
// .Skip(callsMade * 50)
// .Take(50)
// .Select(c => c)
// .ToList();
// results.ForEach(c => periods.Add(c));
// callsMade++;
// lastResultCount = results.Count;
//}
//foreach (var period in periods)
//{
// yield return (Period)period;
//}
#endregion
#region SQL Procedure
string PeriodIDs = string.Empty;
int CustomerID = 0;
if (request.PeriodIDs.Length > 0)
{
PeriodIDs = string.Join(",", request.PeriodIDs);
}
if (request.CustomerID != null)
{
CustomerID = (int)request.CustomerID;
}
// Get the data
List <Period> periods = new List <Period>();
using (var context2 = Exigo.Sql())
{
string sqlProcedure = string.Format("GetPeriodsByPeriodTypeID {0},'{1}',{2}", request.PeriodTypeID, PeriodIDs, CustomerID);
periods = context2.Query <Period>(sqlProcedure).ToList();
}
return(periods);
#endregion
}
