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(); } }