public static List<Monitoring_Types> GetMonitoringTypes()
{
using (BubisDEntities db = new BubisDEntities())
{
return db.Monitoring_Types.ToList();
}
}
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 static void ChangePassword(string mail, string password)
{
using (BubisDEntities db = new BubisDEntities())
{
Users user = db.Users.FirstOrDefault(l => l.USER_Mail.ToLower() == mail.ToLower());
user.USER_Pass = password;
db.SaveChanges();
}
}
public static void SetMonitoringType(Monitoring_Types mnt_type)
{
using (BubisDEntities db = new BubisDEntities())
{
var mnt = db.Monitoring_Types.First(l => l.MNT_Type == mnt_type.MNT_Type);
mnt.MNT_Enabled = mnt_type.MNT_Enabled;
db.SaveChanges();
}
}
public static void VerifyOrderVersion(int id, bool isCurrent)
{
using (BubisDEntities db = new BubisDEntities())
{
Orders_Versions version = db.Orders_Versions.FirstOrDefault(l => l.VERS_Id == id);
version.VERS_IsVerify = true;
version.VERS_IsCurrent = isCurrent;
db.SaveChanges();
}
}
public static bool IsEmptyPassword(string mail)
{
using (BubisDEntities db = new BubisDEntities())
{
Users user = db.Users.FirstOrDefault(us => us.USER_Mail.ToLower() == mail.ToLower() && string.IsNullOrEmpty(us.USER_Pass));
if (user != null)
{
Users_Versions currentVersion = UsersManager.GetCurrentVersion(user);
return (currentVersion != null && currentVersion.USER_Active);
}
}
return false;
}
public static void VerifyOrdersVersions(IEnumerable<int> ids, bool isCurrent)
{
using (BubisDEntities db = new BubisDEntities())
{
var collAuths = db.Orders_Versions.Where(l => ids.Contains(l.VERS_Id));
foreach (Orders_Versions version in collAuths)
{
version.VERS_IsVerify = true;
version.VERS_IsCurrent = isCurrent;
}
db.SaveChanges();
}
}
public static Users AuthenticateUserAndPassword(string mail, string password)
{
using (BubisDEntities db = new BubisDEntities())
{
Users user = db.Users.FirstOrDefault(us => us.USER_Mail.ToLower() == mail.ToLower() && us.USER_Pass == password);
if (user != null)
{
Users_Versions currentVersion = UsersManager.GetCurrentVersion(user);
if (currentVersion != null && currentVersion.USER_Active)
return user;
}
}
return null;
}
public List<Ranking> GetRanking(RankingFilter filter)
{
using (BubisDEntities db = new BubisDEntities())
{
var shipments = db.Orders_Shipments.Include("Orders").Include("ArticlesByCompanies").Where(l => l.Orders.Orders_Versions.Any(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.VERS_Active));
if (filter.ORDR_DateFrom.HasValue)
shipments = shipments.Where(l => l.Orders.ORDR_Date >= filter.ORDR_DateFrom.Value);
if (filter.ORDR_DateTo.HasValue)
shipments = shipments.Where(l => l.Orders.ORDR_Date <= filter.ORDR_DateTo.Value);
if (filter.ART_Id.HasValue)
shipments = shipments.Where(l => l.ArticlesByCompanies.ART_Id == filter.ART_Id.Value);
if (filter.COMP_Id.HasValue)
shipments = shipments.Where(l => l.ArticlesByCompanies.COMP_Id == filter.COMP_Id.Value);
if (filter.CLT_Id.HasValue)
shipments = shipments.Where(l => l.Orders.Orders_Versions.Any(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.VERS_Active && v.CLT_Id == filter.CLT_Id.Value));
var collRanking = shipments.Select(l => new Ranking()
{
ORDR_Code = l.Orders.ORDR_Code,
ORDR_Date = l.Orders.ORDR_Date,
ART_Code = l.ArticlesByCompanies.Articles.ART_Code,
ART_Name = l.ArticlesByCompanies.Articles.Articles_Versions.FirstOrDefault(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.ART_Active).ART_Name,
ORDR_IsShipped = l.Orders.Orders_Versions.Any(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.VERS_Active && v.ORDR_IsShipped.Value),
ORDR_SHIP_QuantityShipped = l.ORDR_SHIP_QuantityShipped,
ORDR_SHIP_QuantityOrdered = l.ORDR_SHIP_QuantityOrdered,
ART_UM_Desc = l.ArticlesByCompanies.Articles.Articles_Versions.FirstOrDefault(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.ART_Active).Articles_UnitMeasure.ART_UM_Desc,
ORDR_SHIP_Price = l.ORDR_SHIP_Price,
ORDR_SHIP_AMLA = l.ORDR_SHIP_AMLA,
CLT_Name = l.Orders.Orders_Versions.FirstOrDefault(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.VERS_Active).Clients.CLT_Name,
COMP_Name = l.ArticlesByCompanies.Companies.COMP_Name,
ORDR_SHIP_OPE = l.ORDR_SHIP_OPE
}
).ToList();
foreach (var l in collRanking)
l.Dollars = (l.Quantity * l.ORDR_SHIP_Price) + (l.Quantity * (l.ORDR_SHIP_OPE.HasValue ? l.ORDR_SHIP_OPE.Value : 0));
return collRanking.OrderByDescending(l => l.Dollars).ToList();
}
}
public List<SupplyerCashFlow> GetSupplyerCashFlow(SupplyerCashFlowFilter filter)
{
List<SupplyerCashFlow> collCashFlow = new List<SupplyerCashFlow>();
SupplyerCashFlow cashFlow = null;
using (BubisDEntities db = new BubisDEntities())
{
// obtengo los artículos de la base, sin agrupar por pedido
var shipments = db.Orders_Shipments.Include("Orders").Include("ArticlesByCompanies").Where(l => !l.Orders.ORDR_IsLiquidate.Value && l.Orders.Orders_Versions.Any(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.VERS_Active));
if (filter.ORDR_DateFrom.HasValue)
shipments = shipments.Where(l => l.Orders.ORDR_Date >= filter.ORDR_DateFrom.Value);
if (filter.ORDR_DateTo.HasValue)
shipments = shipments.Where(l => l.Orders.ORDR_Date <= filter.ORDR_DateTo.Value);
if (filter.COMP_Id.HasValue)
shipments = shipments.Where(l => l.ArticlesByCompanies.COMP_Id == filter.COMP_Id.Value);
if (filter.ORDR_IsShipped.HasValue)
shipments = shipments.Where(l => l.Orders.Orders_Versions.FirstOrDefault(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.VERS_Active).ORDR_IsShipped == filter.ORDR_IsShipped.Value);
if (filter.ORDR_IsPaid.HasValue && filter.ORDR_IsPaid.Value)
shipments = shipments.Where(l => l.Orders.Orders_Versions.FirstOrDefault(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.VERS_Active).ORDR_Balance == 0);
else if (filter.ORDR_IsPaid.HasValue && !filter.ORDR_IsPaid.Value)
shipments = shipments.Where(l => l.Orders.Orders_Versions.FirstOrDefault(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.VERS_Active).ORDR_Balance != 0);
// los agrupo por pedido, por cada pedido hago un SupplyerCashFlow
var orders = shipments.GroupBy(l => l.ORDR_Id);
foreach (var l in orders)
{
cashFlow = new SupplyerCashFlow()
{
CurrentVersion = l.FirstOrDefault().Orders.Orders_Versions.FirstOrDefault(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.VERS_Active),
ORDR_Code = l.FirstOrDefault().Orders.ORDR_Code,
ORDR_Date = l.FirstOrDefault().Orders.ORDR_Date,
};
// si no filtra por empresa entonces el total es el total pedido, sino calcula la parte que le toca a la empresa
if (!filter.COMP_Id.HasValue)
cashFlow.ORDR_Total = cashFlow.CurrentVersion.ORDR_Total;
else
cashFlow.ORDR_Total = l.Sum(ship => CalculateTotal(cashFlow.ORDR_IsShipped, ship.ORDR_SHIP_QuantityShipped, ship.ORDR_SHIP_QuantityOrdered, ship.ORDR_SHIP_Price, ship.ORDR_SHIP_OPE));
cashFlow.ORDR_SHIP_OPE = l.Sum(ship => CalculateOPE(cashFlow.ORDR_IsShipped, ship.ORDR_SHIP_QuantityShipped, ship.ORDR_SHIP_QuantityOrdered, ship.ORDR_SHIP_OPE));
cashFlow.ORDR_SHIP_AMLA = l.Sum(ship => CalculateAMLA(cashFlow.ORDR_IsShipped, ship.ORDR_SHIP_QuantityShipped, ship.ORDR_SHIP_QuantityOrdered, ship.ORDR_SHIP_Price, ship.ORDR_SHIP_OPE, ship.ORDR_SHIP_AMLA));
cashFlow.ORDR_SHIP_AMLA_Vendor = cashFlow.CurrentVersion.Users.Vendors.VND_AMLA * cashFlow.ORDR_SHIP_AMLA / 100;
cashFlow.ORDR_SHIP_AMLA_Net = cashFlow.ORDR_SHIP_AMLA - cashFlow.ORDR_SHIP_AMLA_Vendor;
collCashFlow.Add(cashFlow);
}
return collCashFlow.OrderBy(l => l.CLT_Name).ToList();
}
}
public void Liquidate(List<SellingCommission> collCommissions)
{
// obtengo la última cotización del dolar
decimal? lastQuotation = AccountsManager.GetLastQuotationU();
// variables para la recursividad
SellingCommission commission;
AccountsConcepts concept;
AccountsConcepts_Versions concept_version;
Accounts account;
Accounts_Versions account_version;
IEnumerable<int> collIds = collCommissions.Select(l => l.ORDR_Id);
using (BubisDEntities db = new BubisDEntities())
{
var collOrders = db.Orders.Where(l => collIds.Contains(l.ORDR_Id));
foreach (var order in collOrders)
{
commission = collCommissions.First(l => l.ORDR_Id == order.ORDR_Id);
order.ORDR_IsLiquidateForVendor = true;
order.ORDR_LiquidateForVendorDate = commission.ORDR_LiquidateDate;
order.ORDR_LiquidateForVendorDateRegistered = commission.ORDR_LiquidateDateRegistered;
concept = db.AccountsConcepts.Add(new AccountsConcepts() { VERS_MustApprove = false, Accounts_Versions = null, AccountsConcepts_Versions = null });
concept_version = db.AccountsConcepts_Versions.Add(new AccountsConcepts_Versions()
{
VERS_IsVerify = true,
VERS_IsCurrent = true,
VERS_Active = true,
ACT_TYPE_Id = "EPC",
ACT_GROUP_Id = "EPC",
ACT_CPT_Id = concept.ACT_CPT_Id,
ACT_CPT_Description = string.Format("Egresos por Comisión - {0} - {1} - {2} - {3}", commission.VND_Name, commission.COMP_Name, commission.CLT_Name, commission.ORDR_Code),
Accounts_Groups = null,
Accounts_Types = null,
AccountsConcepts = null,
Audit = null
});
account = db.Accounts.Add(new Accounts() { VERS_MustApprove = false, Accounts_Versions = null });
account_version = db.Accounts_Versions.Add(new Accounts_Versions()
{
VERS_IsVerify = true,
VERS_IsCurrent = true,
VERS_Active = true,
ACT_Id = account.ACT_Id,
ACT_CPT_Id = concept.ACT_CPT_Id,
ACT_Date = commission.ORDR_LiquidateDate.Value,
ACT_Amount = commission.VND_Total.Value,
ACT_Amount_U__ = commission.VND_Total.Value,
ACT_Quotation_U__ = lastQuotation.Value,
Accounts = null,
AccountsConcepts = null,
Audit = null
});
db.SaveChanges();
}
}
}
public void UpdateAMLA(int orderId, decimal? amla, int? porc)
{
using (BubisDEntities db = new BubisDEntities())
{
var order = db.Orders.FirstOrDefault(l => l.ORDR_Id == orderId);
order.ORDR_AMLAManualForVendor = amla;
order.ORDR_PorcManualForVendor = porc;
db.SaveChanges();
}
}
public List<SellingCommission> GetSellingCommissions(SellingCommissionFilter filter)
{
List<SellingCommission> collCosts = new List<SellingCommission>();
SellingCommission cost = null;
using (BubisDEntities db = new BubisDEntities())
{
// obtengo los artículos de la base, sin agrupar por pedido
var shipments = db.Orders_Shipments.Include("Orders").Include("ArticlesByCompanies").Where(l => l.Orders.ORDR_IsLiquidate.Value && l.Orders.ORDR_IsLiquidateForVendor.Value == filter.IsLiquidate && l.Orders.Orders_Versions.Any(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.VERS_Active && v.ORDR_IsShipped.Value && v.ORDR_Balance == 0));
if (filter.COMP_Id.HasValue)
shipments = shipments.Where(l => l.ArticlesByCompanies.COMP_Id == filter.COMP_Id.Value);
if (filter.VND_Id.HasValue)
shipments = shipments.Where(l => l.Orders.Orders_Versions.FirstOrDefault(v => v.VERS_IsVerify && v.VERS_IsCurrent && v.VERS_Active).VND_Id == filter.VND_Id.Value);
if (filter.ORDR_DateFrom.HasValue)
shipments = shipments.Where(l => l.Orders.ORDR_Date >= filter.ORDR_DateFrom.Value);
if (filter.ORDR_DateTo.HasValue)
shipments = shipments.Where(l => l.Orders.ORDR_Date <= filter.ORDR_DateTo.Value);
if (filter.IsLiquidate)
{
if (filter.ORDR_LiquidateDateRegisteredFrom.HasValue)
shipments = shipments.Where(l => l.Orders.ORDR_LiquidateForVendorDateRegistered >= filter.ORDR_LiquidateDateRegisteredFrom.Value);
if (filter.ORDR_LiquidateDateRegisteredTo.HasValue)
shipments = shipments.Where(l => l.Orders.ORDR_LiquidateForVendorDateRegistered <= filter.ORDR_LiquidateDateRegisteredTo.Value);
}
// los agrupo por pedido, por cada pedido hago un ExportCost
var orders = shipments.GroupBy(l => l.ORDR_Id);
foreach (var l in orders)
{
cost = new SellingCommission()
{
CurrentVersion = l.FirstOrDefault().Orders.Orders_Versions.FirstOrDefault(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.VERS_Active),
ORDR_Id = l.FirstOrDefault().ORDR_Id,
ORDR_Code = l.FirstOrDefault().Orders.ORDR_Code,
ORDR_Date = l.FirstOrDefault().Orders.ORDR_Date,
ORDR_LiquidateDate = l.FirstOrDefault().Orders.ORDR_LiquidateForVendorDate,
ORDR_LiquidateDateRegistered = l.FirstOrDefault().Orders.ORDR_LiquidateForVendorDateRegistered,
COMP_Name = l.FirstOrDefault().Orders.COMP_NameForGrid,
CLT_Name = l.FirstOrDefault().Orders.Orders_Versions.FirstOrDefault(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.VERS_Active).Clients.CLT_Name,
VND_Name = l.FirstOrDefault().Orders.Orders_Versions.FirstOrDefault(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.VERS_Active).LastVendor.USER_FullName,
VND_AMLA = (l.FirstOrDefault().Orders.ORDR_PorcManualForVendor.HasValue) ? l.FirstOrDefault().Orders.ORDR_PorcManualForVendor.Value : l.FirstOrDefault().Orders.Orders_Versions.FirstOrDefault(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.VERS_Active).Users.Vendors.VND_AMLA,
};
// si no filtra por empresa entonces el total es el total pedido, sino calcula la parte que le toca a la empresa
if (!filter.COMP_Id.HasValue)
cost.ORDR_Total = cost.CurrentVersion.ORDR_Total;
else
cost.ORDR_Total = l.Sum(ship => CalculateTotal(cost.ORDR_IsShipped, ship.ORDR_SHIP_QuantityShipped, ship.ORDR_SHIP_QuantityOrdered, ship.ORDR_SHIP_Price, ship.ORDR_SHIP_OPE));
cost.ORDR_SHIP_OPE = l.Sum(ship => CalculateOPE(cost.ORDR_IsShipped, ship.ORDR_SHIP_QuantityShipped, ship.ORDR_SHIP_QuantityOrdered, ship.ORDR_SHIP_OPE));
/* obtengo uno de los 3 amla posibles:
* 1. el amla modificado ahora, localmente, en comisión vendedor
* 2. sino el amla modificado previamente en gastos exportación
* 3. sino el amla original del pedido que nunca se modificó
*/
if (l.FirstOrDefault().Orders.ORDR_AMLAManualForVendor.HasValue)
cost.ORDR_SHIP_AMLA = l.FirstOrDefault().Orders.ORDR_AMLAManualForVendor;
else if (l.FirstOrDefault().Orders.ORDR_AMLAManual.HasValue)
cost.ORDR_SHIP_AMLA = l.FirstOrDefault().Orders.ORDR_AMLAManual;
else
cost.ORDR_SHIP_AMLA = l.Sum(ship => CalculateAMLA(cost.ORDR_IsShipped, ship.ORDR_SHIP_QuantityShipped, ship.ORDR_SHIP_QuantityOrdered, ship.ORDR_SHIP_Price, ship.ORDR_SHIP_OPE, ship.ORDR_SHIP_AMLA));
collCosts.Add(cost);
}
return collCosts.OrderBy(l => l.VND_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();
}
}