private List<Orders_Versions> GetOrdersVersions(CheckingAccountFilter filter)
{
using (BubisDEntities db = new BubisDEntities())
{
var orders = db.Orders_Versions.Include("Orders").Where(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.VERS_Active);
if (filter.CLT_Id.HasValue)
orders = orders.Where(l => l.CLT_Id == filter.CLT_Id.Value);
if (filter.COMP_Id.HasValue)
orders = orders.Where(l => l.Orders.Orders_Shipments.Any(ship=>ship.ArticlesByCompanies.COMP_Id == filter.COMP_Id.Value));
if (filter.OrderDate_From.HasValue)
orders = orders.Where(l => l.Orders.ORDR_Date >= filter.OrderDate_From.Value);
if (filter.OrderDate_To.HasValue)
orders = orders.Where(l => l.Orders.ORDR_Date <= filter.OrderDate_To.Value);
if (filter.DontShowOrders)
{
System.DateTime month = System.DateTime.Now.AddMonths(6);
var except = orders.Where(l => l.Orders.ORDR_Date >= month &&
l.Orders.Orders_Versions.FirstOrDefault(v => v.VERS_IsVerify && v.VERS_IsCurrent && v.VERS_Active).ORDR_Balance == 0);
orders = orders.Except(except);
}
return orders.ToList();
}
}
public IEnumerable<CheckingAccount_Payment> GetPayments(CheckingAccountFilter filter)
{
var ov = this.GetPaymentsVersions2(filter);
return ov.Select(l => new CheckingAccount_Payment()
{
PYM_Date = l.PYM_Date,
PYM_ConfirmationDate = l.PYM_ConfirmationDate,
PYM_Id = l.PYM_Id,
PYM_Amount = l.PYM_Amount
});
}
public IEnumerable<CheckingAccount_Order> GetOrders(CheckingAccountFilter filter)
{
var ov = this.GetOrdersVersions(filter);
return ov.Select(l => new CheckingAccount_Order()
{
ORDR_Date = l.Orders.ORDR_Date,
ORDR_Code = l.Orders.ORDR_Code,
ORDR_Bill = l.ORDR_Bill,
//OPI = (l.ORDR_IsShipped.Value) ? l.ORDR_SHIP_QuantityShippedForOPI : l.ORDR_SHIP_QuantityOrderedForOPI,
OPE = (l.ORDR_IsShipped.Value) ? l.ORDR_SHIP_QuantityShippedForOPE : l.ORDR_SHIP_QuantityOrderedForOPE,
ORDR_Total = l.ORDR_Total
});
}
public IEnumerable<CheckingAccount_AppliedPayment> GetAppliedPayments(CheckingAccountFilter filter)
{
var po = this.GetPaymentsOrders(filter);
return po.Select(l => new CheckingAccount_AppliedPayment()
{
ORDR_Date = l.Orders.ORDR_Date,
ORDR_Code = l.Orders.ORDR_Code,
ORDR_Total = l.Orders.Orders_Versions.FirstOrDefault(v => v.VERS_IsVerify && v.VERS_IsCurrent && v.VERS_Active).ORDR_Total,
PYM_Id = l.PYM_Id,
PYM_ORDR_Number = l.PYM_ORDR_Number,
PYM_ORDR_Amount = l.PYM_ORDR_Amount,
PYM_ORDR_BalanceOrder = l.PYM_ORDR_BalanceOrder
}).OrderBy(l=>l.ORDR_Code);
}
public List<CheckingAccount> Get(CheckingAccountFilter filter)
{
IEnumerable<Payments_Versions> payments_versions = this.GetPaymentsVersions(filter);
IEnumerable<CheckingAccount> accounts = payments_versions.Select(l => new CheckingAccount()
{
CLT_Id = l.CLT_Id,
CLT_Name = l.Clients.CLT_Name,
CLT_Group = l.Clients.Clients_Versions.FirstOrDefault(c => c.VERS_IsVerify && c.VERS_IsCurrent && c.CLT_Active).CLT_Group,
COMP_Id = l.COMP_Id,
COMP_Name = l.Companies.COMP_Name,
COMP_Group = l.Companies.Companies_Versions.FirstOrDefault(c => c.VERS_IsVerify && c.VERS_IsCurrent && c.COMP_Active).COMP_Group
}
);
return accounts.DistinctBy(p => new { p.CLT_Id, p.COMP_Id }).OrderBy(l => l.CLT_Name).ToList();
}
private List<Payments_Versions> GetPaymentsVersions2(CheckingAccountFilter filter)
{
using (BubisDEntities db = new BubisDEntities())
{
var payments = db.Payments_Versions.Include("Clients.Clients_Versions").Include("Companies.Companies_Versions").Include("Payments.Payments_Orders.Orders.Orders_Versions")
.Where(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.VERS_Active);
if (filter.CLT_Id.HasValue)
payments = payments.Where(l => l.CLT_Id == filter.CLT_Id.Value);
if (filter.COMP_Id.HasValue)
payments = payments.Where(l => l.COMP_Id == filter.COMP_Id.Value);
if (filter.PaymentDate_From.HasValue)
payments = payments.Where(l => l.PYM_Date >= filter.PaymentDate_From.Value);
if (filter.PaymentDate_To.HasValue)
payments = payments.Where(l => l.PYM_Date <= filter.PaymentDate_To.Value);
if (filter.DontShowPayments)
{
System.DateTime month = System.DateTime.Now.AddMonths(6);
var except = payments.Where(l => l.PYM_BalanceApplied == 0 && l.Payments.Payments_Orders.Any(po => po.Orders.ORDR_Date >= month &&
po.Orders.Orders_Versions.FirstOrDefault(v => v.VERS_IsVerify && v.VERS_IsCurrent && v.VERS_Active).ORDR_Balance == 0));
payments = payments.Except(except);
}
return payments.ToList();
}
}
private List<Payments_Orders> GetPaymentsOrders(CheckingAccountFilter filter)
{
IEnumerable<Payments_Versions> payments_versions = this.GetPaymentsVersions(filter);
List<Payments_Orders> payments_orders = new List<Payments_Orders>();
foreach (Payments_Versions item in payments_versions)
payments_orders.AddRange(item.Payments.Payments_Orders);
return payments_orders;
}
