public ActionResult GetProductos(int provID)
{
List<Product> ListaProductos = new List<Product>();
List<int> ListaID = new List<int>();
AdventureWorks2014Entities contexto = new AdventureWorks2014Entities();
var LHeader = (from OrderHeader in contexto.SalesOrderHeader
where OrderHeader.SalesPersonID == provID
join OrderDetail in contexto.SalesOrderDetail on OrderHeader.SalesOrderID equals OrderDetail.SalesOrderID into lista
from listaproductos in lista
select listaproductos.ProductID).Distinct();
ListaID = LHeader.ToList();
foreach (int id in ListaID)
{
var producto = (from product in contexto.Product
where product.ProductID == id
select product).First();
ListaProductos.Add(producto);
}
contexto.Dispose();
ViewBag.ID = provID;
return PartialView("_Producto", ListaProductos);
}
private List<VentasDetallesModel> GetProducto(int id)
{
List<VentasDetallesModel> productos = new List<VentasDetallesModel>();
using (AdventureWorks2014Entities contexto = new AdventureWorks2014Entities())
{
var filtro = from ventas in contexto.SalesOrderHeader
join detalles in contexto.SalesOrderDetail on ventas.SalesOrderID equals detalles.SalesOrderID
join producto in contexto.Product on detalles.ProductID equals producto.ProductID
where ventas.SalesOrderID == id
select new { id=producto.ProductID , nombre=producto.Name , unidades=detalles.OrderQty , PrecioUnidad=detalles.UnitPrice , PrecioTotal=detalles.LineTotal };
foreach (var item in filtro)
{
VentasDetallesModel pro = new VentasDetallesModel();
pro.ProductID = item.id;
pro.Name = item.nombre;
pro.OrderQty = item.unidades;
pro.UnitPrice = item.PrecioUnidad;
pro.LineTotal = item.PrecioTotal;
productos.Add(pro);
}
}
return productos;
}
private Person GetCliente(int id)
{
Person cliente = null;
using (AdventureWorks2014Entities contexto = new AdventureWorks2014Entities())
{
var filtro = from ventas in contexto.SalesOrderHeader
join consumidor in contexto.Customer on ventas.CustomerID equals consumidor.CustomerID
join persona in contexto.Person on consumidor.PersonID equals persona.BusinessEntityID
where ventas.SalesOrderID == id
select persona;
cliente = filtro.FirstOrDefault();
}
return cliente;
}
public ActionResult ModificarProducto(int productID)
{
Product producto;
AdventureWorks2014Entities contexto = new AdventureWorks2014Entities();
var pro = (from productos in contexto.Product
where productos.ProductID == productID
select productos).First();
producto = (Product)pro;
contexto.Dispose();
return View("ModificarProducto", producto);
}
////////////////////////////////////
// CONSULTAS DE ENTITY FRAMEWORK //
//////////////////////////////////
private List<Department> GetListaDepartamentos()
{
List<Department> listaDepartamentos = null;
using(AdventureWorks2014Entities contexto = new AdventureWorks2014Entities())
{
var lista = from departamento in contexto.Department
select departamento;
listaDepartamentos = lista.ToList();
}
return listaDepartamentos;
}
private Person GetVendedor(int id)
{
Person vendedor = null;
using (AdventureWorks2014Entities contexto = new AdventureWorks2014Entities())
{
var filtro = from ventas in contexto.SalesOrderHeader
join vendor in contexto.SalesPerson on ventas.SalesPersonID equals vendor.BusinessEntityID
join personas in contexto.Person on vendor.BusinessEntityID equals personas.BusinessEntityID
where ventas.SalesOrderID == id
select personas;
vendedor = filtro.FirstOrDefault();
}
return vendedor;
}
public ActionResult GetProveedores()
{
List<Person> ListaProveedores;
AdventureWorks2014Entities contexto = new AdventureWorks2014Entities();
var proveedores = (from prov in contexto.SalesPerson
join per in contexto.Person on prov.BusinessEntityID equals per.BusinessEntityID into lista
from ListaProv in lista
select ListaProv);
ListaProveedores = proveedores.ToList();
contexto.Dispose();
return View("Proveedor", ListaProveedores);
}
public ActionResult FiltroPedidos(string name, string lastName, DateTime inicio, DateTime final)
{
List<int> listaID = new List<int>();
using (AdventureWorks2014Entities contexto = new AdventureWorks2014Entities())
{
var id = (from vendedor in contexto.Person
where (vendedor.FirstName == name && vendedor.LastName == lastName)
select vendedor.BusinessEntityID).First();
var listaProductos = (from sales in contexto.SalesOrderHeader
where (sales.SalesPersonID == id && sales.OrderDate >= inicio && sales.OrderDate <= final)
select sales.SalesOrderID);
listaID = listaProductos.ToList();
}
return PartialView("_ListaFiltro1", listaID);
}
public List<String> GetListaJobTitle(int depId)
{
List<String> listaJobTitle = null;
using (AdventureWorks2014Entities contexto = new AdventureWorks2014Entities())
{
var lista = from e in contexto.Employee
join h in contexto.EmployeeDepartmentHistory on e.BusinessEntityID equals h.BusinessEntityID
join d in contexto.Department on h.DepartmentID equals d.DepartmentID
where d.DepartmentID == depId
select e.JobTitle;
listaJobTitle = lista.Distinct().ToList();
}
return listaJobTitle;
}
public ActionResult ModificarProveedor(int negocio)
{
Person persona;
SalesPerson salesPerson;
AdventureWorks2014Entities contexto = new AdventureWorks2014Entities();
var proveedor = (from prov in contexto.Person
where prov.BusinessEntityID == negocio
select prov).First();
persona = (Person)proveedor;
var salesP = from prov in contexto.SalesPerson
where prov.BusinessEntityID == negocio
select prov;
salesPerson = salesP.First();
contexto.Dispose();
ViewBag.salesPerson = salesPerson;
return View("ModificarProveedor", persona);
}
private List<string> GetName(int id)
{
List<string> names = new List<string>();
using (AdventureWorks2014Entities contexto = new AdventureWorks2014Entities())
{
var nombres = from p in contexto.Person
where p.BusinessEntityID == id
select p.FirstName;
names.Add(nombres.First());
var apellido = from p in contexto.Person
where p.BusinessEntityID == id
select p.LastName;
names.Add(apellido.First());
}
return names;
}
private DateTime GetFecha(int id)
{
DateTime fecha = new DateTime();
using (AdventureWorks2014Entities contexto = new AdventureWorks2014Entities())
{
var filtro = from ventas in contexto.SalesOrderHeader
where ventas.SalesOrderID == id
select ventas.OrderDate;
fecha = filtro.FirstOrDefault();
}
return fecha;
}
private List<Person> GetListaPersonas(int depId, string jobTitle)
{
List<Person> listaPersonas = null;
using (AdventureWorks2014Entities contexto = new AdventureWorks2014Entities())
{
var lista = from e in contexto.Employee
join h in contexto.EmployeeDepartmentHistory on e.BusinessEntityID equals h.BusinessEntityID
join p in contexto.Person on h.BusinessEntityID equals p.BusinessEntityID
where h.DepartmentID == depId && e.JobTitle == jobTitle
select p;
listaPersonas = lista.ToList();
}
return listaPersonas;
}
private int GetDepartamentoID(int id)
{
int departamentoId;
using (AdventureWorks2014Entities contexto = new AdventureWorks2014Entities())
{
var depID = from e in contexto.EmployeeDepartmentHistory
where e.BusinessEntityID == id
select e.DepartmentID;
departamentoId = depID.First();
}
return departamentoId;
}
private string GetEmail(int id)
{
string email;
using (AdventureWorks2014Entities contexto = new AdventureWorks2014Entities())
{
var correo=from p in contexto.EmailAddress
where p.BusinessEntityID == id
select p.EmailAddress1;
email = correo.First();
}
return email;
}
public ActionResult CrearProducto(int productID)
{
Product producto;
using (AdventureWorks2014Entities contexto = new AdventureWorks2014Entities())
{
//select MAX(ProductID) from Production.Product
var pro = (from productos in contexto.Product
select productos).Max();
producto = (Product)pro;
}
return View("NuevoProducto", producto);
}
private string GetJobTitle(int id)
{
string jobtitle;
using (AdventureWorks2014Entities contexto = new AdventureWorks2014Entities())
{
var job = from e in contexto.Employee
where e.BusinessEntityID == id
select e.JobTitle;
jobtitle = job.First();
}
return jobtitle;
}
private string GetGroupName(int id)
{
string groupname;
using (AdventureWorks2014Entities contexto = new AdventureWorks2014Entities())
{
var grupo = from d in contexto.Department
join e in contexto.EmployeeDepartmentHistory on d.DepartmentID equals e.DepartmentID
where e.BusinessEntityID == id
select d.GroupName;
groupname = grupo.First();
}
return groupname;
}
public ActionResult UpdateProducto()
{
Product producto;
int id = int.Parse(HttpContext.Request.Params["ProductID"]);
using (AdventureWorks2014Entities contexto = new AdventureWorks2014Entities())
{
var productos = from p in contexto.Product
where p.ProductID == id
select p;
producto = productos.First();
producto.Name = HttpContext.Request.Params["Name"];
producto.ProductNumber = HttpContext.Request.Params["ProductNumber"];
producto.MakeFlag = bool.Parse(HttpContext.Request.Params["MakeFlag"]);
producto.Color = HttpContext.Request.Params["Color"];
producto.SafetyStockLevel = short.Parse(HttpContext.Request.Params["SafetyStockLevel"]);
producto.Size = HttpContext.Request.Params["Size"];
producto.ProductSubcategoryID = int.Parse(HttpContext.Request.Params["ProductSubcategoryID"]);
//producto.SellStartDate = DateTime.Parse(HttpContext.Request.Params["SellStartDate"]);
// producto.SellEndDate = DateTime.Parse(HttpContext.Request.Params["SellEndDate"]);
try
{
contexto.SaveChanges();
ViewBag.Error = false;
}
catch (Exception)
{
ViewBag.Error = true;
producto = (from p in contexto.Product
where p.ProductID == id
select p).First();
}
}
return View("ModificarProducto", producto);
}
private string GetTelefono(int id)
{
string telefono;
using (AdventureWorks2014Entities contexto = new AdventureWorks2014Entities())
{
var tele=from p in contexto.PersonPhone
where p.BusinessEntityID == id
select p.PhoneNumber;
telefono = tele.First();
}
return telefono;
}
public ActionResult UpdateProveedor()
{
Person persona;
int id = int.Parse(HttpContext.Request.Params["BusinessEntityID"]);
using (AdventureWorks2014Entities contexto = new AdventureWorks2014Entities())
{
var proveedor = from prov in contexto.Person
where prov.BusinessEntityID == id
select prov;
persona = proveedor.First();
persona.PersonType = HttpContext.Request.Params["PersonType"];
persona.NameStyle = bool.Parse(HttpContext.Request.Params["NameStyle"]);
persona.Title = HttpContext.Request.Params["Title"];
persona.FirstName = HttpContext.Request.Params["FirstName"];
persona.LastName = HttpContext.Request.Params["LastName"];
persona.MiddleName = HttpContext.Request.Params["MiddleName"];
persona.Suffix = HttpContext.Request.Params["Suffix"];
persona.EmailPromotion = int.Parse(HttpContext.Request.Params["EmailPromotion"]);
persona.AdditionalContactInfo = HttpContext.Request.Params["AdditionalContactInfo"];
persona.ModifiedDate = DateTime.Now;
try
{
contexto.SaveChanges();
ViewBag.error = false;
}
catch(Exception e)
{
ViewBag.error = true;
persona = (from prov in contexto.Person
where prov.BusinessEntityID == id
select prov).First();
}
}
return View("ModificarProveedor", persona);
}
