public UnitOfWorkAccountSearch(GlyticsDbContext context) : base(context)
{
Account = new AccountRepository(context);
}
/// <summary>
/// this method return the total rows
/// </summary>
/// <param name="beginDate"></param>
/// <param name="endDate"></param>
/// <param name="companyId"></param>
/// <param name="sortExpression"></param>
/// <param name="startRowIndex"></param>
/// <param name="maximumRows"></param>
/// <returns></returns>
private Int32 GetOpenInvoicesCount(Int32 companyId, IAccountSearch accountSearch, string sortExpression,
int startRowIndex, int maximumRows)
{
return
GetOpenInvoices(companyId, accountSearch, sortExpression, startRowIndex, maximumRows).Cast<IQueryable>()
.Count();
}
/// <summary>
/// this is the count method of GetExpiredInvoicesInPeriod
/// </summary>
/// <param name="companyId"></param>
/// <param name="dateTimeInterval"></param>
/// <param name="sortExpression"></param>
/// <param name="startRowIndex"></param>
/// <param name="maximumRows"></param>
/// <returns></returns>
public Int32 GetExpiredInvoicesInPeriodCount(Int32 companyId, IAccountSearch accountSearch,
String sortExpression, Int32 startRowIndex, Int32 maximumRows)
{
return
GetExpiredInvoicesInPeriod(companyId, accountSearch, sortExpression, startRowIndex, maximumRows).Cast
<IQueryable>().Count();
}
//Expiring
/// <summary>
/// return all expiring invoices(Current month)
/// </summary>
/// <param name="companyId"></param>
/// <param name="sortExpression"></param>
/// <param name="startRowIndex"></param>
/// <param name="maximumRows"></param>
/// <returns></returns>
private IQueryable GetExpiringInvoices(Int32 companyId, IAccountSearch accountSearch, string sortExpression,
int startRowIndex, int maximumRows)
{
accountSearch.dateTimeInterval =
new DateTimeInterval(DateTime.Parse("01" + DateTime.Now.ToString("/MM/yyyy")),
DateTime.Now.AddMonths(1).AddDays(DateTime.Now.Day * -1));
// return GetInvoices(companyId, accountSearch, sortExpression, startRowIndex, maximumRows);
return GetAllInvoices(companyId, accountSearch, false, sortExpression, startRowIndex, maximumRows);
}
//Open
/// <summary>
/// this method return all open invoices
/// </summary>
/// <param name="beginDate"></param>
/// <param name="endDate"></param>
/// <param name="companyId"></param>
/// <param name="sortExpression"></param>
/// <param name="startRowIndex"></param>
/// <param name="maximumRows"></param>
/// <returns></returns>
private IQueryable GetOpenInvoices(Int32 companyId, IAccountSearch accountSearch, string sortExpression,
int startRowIndex, int maximumRows)
{
return GetAllInvoices(companyId, accountSearch, false, sortExpression, startRowIndex, maximumRows);
}
/// <summary>
/// this is the Generic Method
/// </summary>
/// <param name="accountPlanId"></param>
/// <param name="costCenterId"></param>
/// <param name="companyId"></param>
/// <param name="dateTimeInterval"></param>
/// <param name="parcelStatus"></param>
/// <param name="sortExpression"></param>
/// <param name="startRowIndex"></param>
/// <param name="maximumRows"></param>
/// <returns></returns>
public IQueryable GetInvoices(Int32 companyId, IAccountSearch accountSearch, string sortExpression,
int startRowIndex, int maximumRows)
{
IQueryable query = null;
switch (accountSearch.ParcelStatus)
{
case ParcelStatus.OPEN:
query = GetOpenInvoices(companyId, accountSearch, sortExpression, startRowIndex, maximumRows);
break;
case ParcelStatus.CLOSED:
query = GetClosedInvoices(companyId, accountSearch, sortExpression, startRowIndex, maximumRows);
break;
case ParcelStatus.EXPIRED:
query = GetExpiredInvoices(companyId, accountSearch, sortExpression, startRowIndex, maximumRows);
break;
case ParcelStatus.EXPIRING:
query = GetExpiringInvoices(companyId, accountSearch, sortExpression, startRowIndex, maximumRows);
break;
default:
query = GetAllInvoices(companyId, accountSearch, null, sortExpression, startRowIndex, maximumRows);
break;
}
return query;
}
/// <summary>
/// this is the count method of GetInvoices
/// </summary>
/// <param name="accountPlanId"></param>
/// <param name="costCenterId"></param>
/// <param name="companyId"></param>
/// <param name="dateTimeInterval"></param>
/// <param name="parcelStatus"></param>
/// <param name="sortExpression"></param>
/// <param name="startRowIndex"></param>
/// <param name="maximumRows"></param>
/// <returns></returns>
public Int32 GetInvoicesCount(Int32 companyId, IAccountSearch accountSearch, bool closed, string sortExpression,
int startRowIndex, int maximumRows)
{
return
GetAllInvoices(companyId, accountSearch, null, sortExpression, startRowIndex, maximumRows).Cast
<IQueryable>().Count();
}
/// <summary>
/// this is the Generic method used to execute all others queries
/// </summary>
/// <param name="companyId"></param>
/// <param name="accountPlanId"></param>
/// <param name="costCenterId"></param>
/// <param name="dateTimeInterval"></param>
/// <param name="closed"></param>
/// <param name="sortExpression"></param>
/// <param name="startRowIndex"></param>
/// <param name="maximumRows"></param>
/// <returns></returns>
private IQueryable GetInvoices(Int32 companyId, IAccountSearch accountSearch, Boolean? closed,
string sortExpression, int startRowIndex, int maximumRows)
{
IQueryable<Invoice> invoices;
if (accountSearch.AccountPlanId.HasValue)
invoices = GetInvoicesByAccountingPlan(companyId, Convert.ToInt32(accountSearch.AccountPlanId));
else
invoices = GetInvoicesByCompany(companyId);
var query = from invoice in invoices
join parcel in DbContext.Parcels on invoice.InvoiceId equals parcel.InvoiceId
orderby parcel.DueDate
select new
{
invoice.AccountingPlanId,
invoice.InvoiceId,
invoice.CompanyId,
invoice.EntryDate,
invoice.Description,
invoice.CustomerId,
invoice.CostCenterId,
DueDate = parcel.DueDate,
Desc = parcel.Description,
Amount = parcel.EffectedAmount ?? parcel.Amount,
parcel.EffectedAmount,
parcel.EffectedDate,
parcel.AccountId,
parcel.IdentificationNumber,
customerName =
(invoice.Customer.LegalEntityProfile.CompanyName ?? "") +
(invoice.Customer.Profile.Name ?? "")
};
if (accountSearch != null)
{
// Interval of date
if (accountSearch.dateTimeInterval != null && accountSearch.dateTimeInterval.BeginDate != null)
query = query.Where(inv => inv.DueDate >= accountSearch.dateTimeInterval.BeginDate);
if (accountSearch.dateTimeInterval != null && accountSearch.dateTimeInterval.EndDate != null)
query = query.Where(inv => inv.DueDate <= accountSearch.dateTimeInterval.EndDate);
//Cost Center
if (accountSearch.CostCenterId.HasValue && accountSearch.CostCenterId.Value != Decimal.Zero)
query = query.Where(inv => inv.CostCenterId == accountSearch.CostCenterId);
//Customer/Supplier name
if (!String.IsNullOrEmpty(accountSearch.Name))
query = query.Where(inv => inv.customerName.Contains(accountSearch.Name));
//Identification
if (!String.IsNullOrEmpty(accountSearch.Identification))
query = query.Where(inv => inv.IdentificationNumber.Contains(accountSearch.Identification));
//Account
if (accountSearch.AccountId.HasValue)
query = query.Where(inv => inv.AccountId.Equals(accountSearch.AccountId));
//ParcelValue
if (accountSearch.ParcelValue.HasValue)
query =
query.Where(
inv =>
inv.Amount.Equals(accountSearch.ParcelValue) ||
inv.EffectedAmount.Equals(accountSearch.ParcelValue));
if (accountSearch.ParcelStatus == (int?)ParcelStatus.EXPIRED)
query =
query.Where(
inv =>
inv.DueDate < DateTime.Now && inv.EntryDate > accountSearch.dateTimeInterval.BeginDate);
if (accountSearch.ParcelStatus == (int?)ParcelStatus.EXPIRING)
query = query.Where(inv => inv.DueDate.Month == DateTime.Now.Month && inv.DueDate >= DateTime.Now);
//if closed is not null then compares IsOpen with Closed
if (!closed.HasValue)
query = query.AsQueryable();
else if (closed.Value)
query = query.Where(p => p.EffectedAmount > 0 && p.EffectedDate.HasValue);
else
query = query.Where(p => p.EffectedDate == null || p.EffectedAmount == null);
}
return query.SortAndPage(sortExpression, startRowIndex, maximumRows, "DueDate").AsQueryable();
}
/// <summary>
/// this is the Generic method used to execute all others queries
/// </summary>
/// <param name="companyId"></param>
/// <param name="accountPlanId"></param>
/// <param name="costCenterId"></param>
/// <param name="dateTimeInterval"></param>
/// <param name="closed"></param>
/// <param name="sortExpression"></param>
/// <param name="startRowIndex"></param>
/// <param name="maximumRows"></param>
/// <returns></returns>
#warning Este método está duplicado
private IQueryable GetAllInvoices(Int32 companyId, IAccountSearch accountSearch, Boolean? closed,
string sortExpression, int startRowIndex, int maximumRows)
{
return GetInvoices(companyId, accountSearch, closed, sortExpression, startRowIndex, maximumRows);
}
public Int32 GetBillsCount(Int32 companyId, IAccountSearch accountSearch, string sortExpression,
int startRowIndex, int maximumRows)
{
return
GetBills(companyId, accountSearch, sortExpression, startRowIndex, maximumRows).Cast<IQueryable>().Count();
}
private IQueryable GetBills(Int32 companyId, IAccountSearch accountSearch, Boolean? closed,
string sortExpression, int startRowIndex, int maximumRows)
{
IQueryable<Bill> bills;
if (accountSearch.AccountPlanId.HasValue)
bills = GetBillsByAccountingPlan(companyId, Convert.ToInt32(accountSearch.AccountPlanId));
else
bills = GetBillByCompany(companyId);
var query = from bill in bills
join parcel in DbContext.Parcels on bill.BillId equals parcel.BillId
orderby parcel.DueDate
select new
{
bill.AccountingPlanId,
bill.DocumentType,
bill.BillId,
bill.CompanyId,
bill.DocumentNumber,
bill.EntryDate,
bill.Description,
bill.Supplier,
bill.CostCenterId,
parcel.DueDate,
Desc = parcel.Description,
parcel.AccountId,
Amount = parcel.EffectedAmount ?? parcel.Amount,
parcel.EffectedAmount,
parcel.EffectedDate,
parcel.IdentificationNumber,
supplierName =
(bill.Supplier.LegalEntityProfile.CompanyName ?? String.Empty) +
(bill.Supplier.Profile.Name ?? String.Empty)
};
if (accountSearch != null)
{
// Interval of date
if (accountSearch.dateTimeInterval != null && accountSearch.dateTimeInterval.BeginDate != null)
query = query.Where(bill => bill.DueDate >= accountSearch.dateTimeInterval.BeginDate);
if (accountSearch.dateTimeInterval != null && accountSearch.dateTimeInterval.EndDate != null)
query = query.Where(bill => bill.DueDate <= accountSearch.dateTimeInterval.EndDate);
//Cost Center
if (accountSearch.CostCenterId.HasValue && accountSearch.CostCenterId.Value != Decimal.Zero)
query = query.Where(bill => bill.CostCenterId == accountSearch.CostCenterId);
//Customer/Supplier name
if (!String.IsNullOrEmpty(accountSearch.Name))
query = query.Where(bill => bill.supplierName.Contains(accountSearch.Name));
//Identification
if (!String.IsNullOrEmpty(accountSearch.Identification))
query = query.Where(bill => bill.IdentificationNumber.Contains(accountSearch.Identification));
//Account
if (accountSearch.AccountId.HasValue)
query = query.Where(bill => bill.AccountId.Equals(accountSearch.AccountId));
//ParcelValue
if (accountSearch.ParcelValue.HasValue)
query =
query.Where(
bill =>
bill.Amount.Equals(accountSearch.ParcelValue) ||
bill.EffectedAmount.Equals(accountSearch.ParcelValue));
if (accountSearch.ParcelStatus == ParcelStatus.EXPIRED)
query =
query.Where(
bill =>
bill.DueDate < DateTime.Now && bill.EntryDate > accountSearch.dateTimeInterval.BeginDate);
if (accountSearch.ParcelStatus == ParcelStatus.EXPIRING)
query = query.Where(bill => bill.DueDate.Month == DateTime.Now.Month && bill.DueDate >= DateTime.Now);
}
/* This structure was used 'cause the importance level of each conditional.
*
*
* The first one is about All values, if closed hasn't value, Return the raw result.This statement
* doesn't require any execution, just return raw results therefore return them
*
*
* The Second one has an else statement 'cause each state is important. For instance, is necessary
* execute others instuctions if closed is false or is true. Therefore each statement(if/else) has the same importance
* When both states of if statements have the same importance if/else is used.
*
* RefactorName: Substituir condição aninhada por cláusula guarda - kent Beck
*/
//if closed is not null then compares IsOpen with Closed
if (!closed.HasValue)
query = query.AsQueryable();
else if (closed.Value)
query = query.Where(p => p.EffectedAmount > Decimal.Zero && p.EffectedDate.HasValue);
else
query = query.Where(p => !p.EffectedDate.HasValue || !p.EffectedAmount.HasValue);
return query.SortAndPage(sortExpression, startRowIndex, maximumRows, "DueDate").AsQueryable();
}
//Closed
/// <summary>
/// this method returns closed bills
/// </summary>
/// <param name="companyId"></param>
/// <param name="accountPlanId"></param>
/// <param name="costCenterId"></param>
/// <param name="dateTimeInterval"></param>
/// <param name="sortExpression"></param>
/// <param name="startRowIndex"></param>
/// <param name="maximumRows"></param>
/// <returns></returns>
private IQueryable GetClosedBills(Int32 companyId, IAccountSearch accountSearch, string sortExpression,
int startRowIndex, int maximumRows)
{
return GetBills(companyId, accountSearch, true, sortExpression, startRowIndex, maximumRows);
}
