public IHttpActionResult Filter(string productName = null, string categoryName = null, decimal?byPrice = null)
{
var DB = new DB_Context();
try
{
Dictionary <string, object> result = new Dictionary <string, object>();
var temp = DB.Products.AsQueryable();
if (productName != null)
{
temp = temp.Where(data => data.ProductName.ToLower().Contains(productName.ToLower()));
}
if (categoryName != null)
{
temp = temp.Where(data => data.Category.CategoryName.ToLower().Contains(categoryName.ToLower()));
}
if (byPrice != null)
{
temp = temp.Where(data => data.UnitPrice <= byPrice);
}
var listProduct = temp.ToList().Select(item => new ProductsViewModel(item));
result.Add("data", listProduct);
DB.Dispose();
return(Ok(result));
}
catch (Exception)
{
throw;
}
}
public IHttpActionResult Update([FromBody] ProductsViewModel dataBody)
{
var DB = new DB_Context();
try
{
Dictionary <string, object> result = new Dictionary <string, object>();
Product product = DB.Products.Find(dataBody.ProductID);
product.ProductID = dataBody.ProductID;
product.ProductName = dataBody.ProductName;
product.CategoryID = dataBody.CategoryID;
product.QuantityPerUnit = dataBody.QuantityPerUnit;
product.UnitPrice = dataBody.UnitPrice;
product.UnitsInStock = dataBody.UnitsInStock;
product.ReorderLevel = dataBody.ReorderLevel;
product.Discontinued = dataBody.Discontinued;
DB.SaveChanges();
DB.Dispose();
result.Add("Message", "Update Data Succses");
return(Ok(result));
}
catch (Exception)
{
throw;
}
}
public IHttpActionResult OrderPerSupplier(DateTime?orderDate = null)
{
Dictionary <string, object> result = new Dictionary <string, object>();
List <OrderSupViewModel2> listOrdDate = new List <OrderSupViewModel2>();
using (var db = new DB_Context())
{
var dataOrders = db.Orders.AsQueryable();
if (orderDate != null)
{
dataOrders = dataOrders.Where(item => item.OrderDate == orderDate);
}
var listOrderDate = dataOrders.ToList()
.Select(dt => new OrderViewModel(dt)).AsQueryable();
//foreach (var item in listOrderDate)
//{
// var listSupplierDetail = item.Supplierlist.ToList();
//}
result.Add("data", listOrderDate);
}
return(Ok(result));
}
public IHttpActionResult ReadBy(int?orderId = null)
{
try
{
using (var db = new DB_Context())
{
List <OrderCustome1ViewModel> listAllData = new List <OrderCustome1ViewModel>();
var temp = db.Orders.AsQueryable();
if (orderId != null)
{
return(Ok(readAja(orderId)));
}
else
{
foreach (var data1 in temp)
{
orderId = data1.OrderID;
listAllData.Add(readAja(orderId));
}
return(Ok(listAllData));
}
}
}
catch (Exception)
{
throw;
}
}
public override void vincular(DB_Context contexto, Organizacion organizacion)
{
var listaIngresos = contexto.operacionDeIngreso
.SqlQuery("Select * from operaciondeingreso where ID_Organizacion = {0} order by fecha", organizacion.ID_Organizacion)
.ToList();
var listaEgresos = contexto.operacionDeEgreso
.SqlQuery("Select * from operaciondeegreso where ID_Organizacion = {0} order by fecha", organizacion.ID_Organizacion)
.ToList();
foreach (OperacionDeIngreso opingreso in listaIngresos)
{
foreach (OperacionDeEgreso opegreso in listaEgresos)
{
if (opingreso.Monto >= opegreso.ValorTotal &&
opegreso.IngresoAsociado is null &&
cumpleCondicionesVinculador(opegreso, opingreso))
{
// Guardo lo que me falta para llenar el ingreso
opingreso.Monto -= opegreso.ValorTotal;
// Si se puede, lo asocio
asociarEgresoIngreso(contexto, opegreso, opingreso);
}
}
}
}
public IHttpActionResult Delete(int categoryId)
{
// membuat koneksi DB
var db = new DB_Context();
try
{
Dictionary <string, object> result = new Dictionary <string, object>();
// Buat object dan menginisialisasi dengan data dari database menggunakan method Find (Primary Key)
Category category = db.Categories.Where(data => data.CategoryID == categoryId).FirstOrDefault();
// remove data yang diinginkan
db.Categories.Remove(category);
//simpan perubahan
result.Add("Message", "Delete data success");
return(Ok(result));
}
catch (Exception)
{
throw;
}
}
public Dictionary <DateTime, float> TemperatureInCelsiusForJFK()
{
var context = new DB_Context();
return(context.Weather.Where(w => !string.IsNullOrEmpty(w.origin) && w.origin.Equals("JFK"))
.Select(w => new { w.time_hour, w.temp }).ToDictionary(g => g.time_hour, g => (g.temp - 32) * 5 / 9));
}
public IHttpActionResult Create([FormBody] ProductViewModel databody)
{
using (var db = new DB_Context())
{
try
{
Dictionary <string, object> result = new Dictionary <string, object>();
Product newProduct = new Product()
{
ProductID = databody.ProductID,
ProductName = databody.ProductName,
SupplierID = databody.SupplierID,
CategoryID = databody.CategoryID,
QuantityPerUnit = databody.QuantityPerUnit,
UnitPrice = databody.UnitPrice,
UnitsInStock = databody.UnitsInStock,
UnitsOnOrder = databody.UnitsOnOrder,
ReorderLevel = databody.ReorderLevel,
Discontinued = databody.Discontinued
};
db.Products.Add(newProduct);
db.SaveChanges();
result.Add("Message", "Insert Data Success");
return(Ok(result));
}
catch (Exception)
{
throw;
}
}
}
public IHttpActionResult Create([FromBody] ProductViewModel dataBody)
{
using (var db = new DB_Context())
{
try
{
Dictionary <string, object> result = new Dictionary <string, object>();
Product newProduct = new Product()
{
ProductID = dataBody.ProductID,
ProductName = dataBody.ProductName,
SupplierID = dataBody.SupplierID,
CategoryID = dataBody.CategoryID,
QuantityPerUnit = dataBody.QuantityPerUnit,
UnitPrice = dataBody.UnitPrice,
UnitsInStock = dataBody.UnitsInStock,
UnitsOnOrder = dataBody.UnitsOnOrder,
ReorderLevel = dataBody.ReorderLevel,
Discontinued = dataBody.Discontinued
};
// menambahkan kategori baru ke Category Entity Database
db.Products.Add(newProduct);
// method yang digunakan untuk menyimpan perubahan baru di database
db.SaveChanges();
result.Add("Message", "Insert data success");
return(Ok(result));
}
catch (Exception)
{
throw;
}
}
}
public Dictionary <string, string> MeanAirtimeForOrigins()
{
var context = new DB_Context();
double?averageAirtimeJFK = context.Flights.Where(f => !string.IsNullOrEmpty(f.origin) && f.origin.Equals("JFK")).Select(f => f.air_time).ToList().Average();
double?averageAirtimeEWR = context.Flights.Where(f => !string.IsNullOrEmpty(f.origin) && f.origin.Equals("EWR")).Select(f => f.air_time).ToList().Average();
double?averageAirtimeLGA = context.Flights.Where(f => !string.IsNullOrEmpty(f.origin) && f.origin.Equals("LGA")).Select(f => f.air_time).ToList().Average();
TimeSpan tavgAirtimeJFK = new TimeSpan(), tavgAirtimeEWR = new TimeSpan(), tavgAirtimeLGA = new TimeSpan();
if (averageAirtimeJFK != null)
{
tavgAirtimeJFK = TimeSpan.FromMinutes((double)averageAirtimeJFK);
}
if (averageAirtimeEWR != null)
{
tavgAirtimeEWR = TimeSpan.FromMinutes((double)averageAirtimeEWR);
}
if (averageAirtimeLGA != null)
{
tavgAirtimeLGA = TimeSpan.FromMinutes((double)averageAirtimeLGA);
}
return(new Dictionary <string, string>()
{
{ "JFK", tavgAirtimeJFK.ToString("hh\\:mm\\:ss") },
{ "EWR", tavgAirtimeEWR.ToString("hh\\:mm\\:ss") },
{ "LGA", tavgAirtimeLGA.ToString("hh\\:mm\\:ss") }
});
}
public IHttpActionResult Filter(string namaProduct = null, string namaCategory = null, decimal?price = null)
{
using (var db = new DB_Context())
{
Dictionary <string, object> result = new Dictionary <string, object>();
var temp = db.Products.AsQueryable();
if (namaProduct != null)
{
temp = temp.Where(data => data.ProductName.ToLower().Contains(namaProduct.ToLower()));
}
if (namaCategory != null)
{
temp = temp.Where(data => data.Category.CategoryName.ToLower().Contains(namaCategory.ToLower()));
}
if (price != null)
{
temp = temp.Where(data => data.UnitPrice < price);
}
var listProduct = temp.ToList().Select(data => new ProductViewModel(data));
result.Add("mesage", "Read data success");
result.Add("data", listProduct);
return(Ok(result));
}
}
public IHttpActionResult Update([FromBody] ProductViewModel dataBody)
{
// membuat koneksi DB
var db = new DB_Context();
try
{
Dictionary <string, object> result = new Dictionary <string, object>();
// Buat Object dan menginisialisasi dengan data dari database menggunakan method Find(Primary Key)
Product product = db.Products.Find(dataBody.ProductID);
// inisialisasi data yang akan dirubah
product.ProductID = dataBody.ProductID;
product.ProductName = dataBody.ProductName;
product.SupplierID = dataBody.SupplierID;
product.CategoryID = dataBody.CategoryID;
product.QuantityPerUnit = dataBody.QuantityPerUnit;
product.UnitPrice = dataBody.UnitPrice;
product.UnitsInStock = dataBody.UnitsInStock;
product.UnitsOnOrder = dataBody.UnitsOnOrder;
product.ReorderLevel = dataBody.ReorderLevel;
product.Discontinued = dataBody.Discontinued;
db.SaveChanges();
db.Dispose();
result.Add("Message", "Update data success");
return(Ok(result));
}
catch (Exception)
{
throw;
}
}
public IHttpActionResult GetCostCalculation(string condition, int userDemand)
{
using (var db = new DB_Context())
{
try
{
var temp = db.Products.AsQueryable();
List <CostCalculationViewModel> listProduct = new List <CostCalculationViewModel>();
Dictionary <string, object> result = new Dictionary <string, object>();
temp = temp.Where(data => data.ProductType.Contains("TransportationServices"));
var listCostEntity = temp.AsEnumerable().ToList();
foreach (var item in listCostEntity)
{
CostCalculationViewModel productDet = new CostCalculationViewModel(item, condition, userDemand);
listProduct.Add(productDet);
}
;
result.Add("Data", listProduct);
return(Ok(result));
}
catch (Exception)
{
throw;
}
}
}
public IHttpActionResult Update([FromBody] RegionViewModel dataBody)
{
using (var db = new DB_Context())
{
try
{
Dictionary <string, object> result = new Dictionary <string, object>();
List <RegionViewModel> listRegion = new List <RegionViewModel>();
Region regionTemp = db.Regions.Find(dataBody.RegionID);
regionTemp.RegionID = dataBody.RegionID;
regionTemp.RegionDescription =
dataBody.RegionName + "|" +
dataBody.RegionLangitude + "|" +
dataBody.RegionLatitude + "|" +
dataBody.Country;
RegionViewModel region = new RegionViewModel(regionTemp);
listRegion.Add(region);
db.SaveChanges();
result.Add("Message", "Update data success");
result.Add("data", listRegion);
return(Ok(result));
}
catch (Exception)
{
throw;
}
}
}
public Dictionary <string, float> DailyMeanTempInCelsiusForLGA()
{
var context = new DB_Context();
return(context.Weather.Where(w => !string.IsNullOrEmpty(w.origin) && w.origin.Equals("LGA"))
.GroupBy(g => g.time_hour.Date.ToShortDateString()).ToDictionary(p => p.Key, p => (p.Average(g => g.temp) - 32) * 5 / 9));
}
public IHttpActionResult FilterBy(string ProductName = null, string CategoryName = null, decimal?UnitPrice = null)
{
using (var db = new DB_Context())
{
try
{
Dictionary <string, object> result = new Dictionary <string, object>();
var temp = db.Products.AsQueryable();
if (ProductName != null)
{
temp = temp.Where(data => data.ProductName.Contains(ProductName.ToLower()));
}
if (CategoryName != null)
{
temp = temp.Where(data => data.Category.CategoryName.ToLower().Contains(CategoryName.ToLower()));
}
if (UnitPrice != null)
{
temp = temp.Where(data => data.UnitPrice < UnitPrice);
}
var listProduct = temp.ToList().Select(data => new ProductViewModel(data));
result.Add("Message", "Read Data Success");
result.Add("Data", listProduct);
return(Ok(result));
}
catch (Exception)
{
throw;
}
}
}
public Dictionary <string, string> MeanDepartureAndArrivalDelayForLGA()
{
var context = new DB_Context();
double?averageDepDelayLGA = context.Flights.Where(f => !string.IsNullOrEmpty(f.origin) && f.origin.Equals("LGA")).Select(f => f.dep_delay).ToList().Average();
double?averageArrDelayLGA = context.Flights.Where(f => !string.IsNullOrEmpty(f.origin) && f.origin.Equals("LGA")).Select(f => f.arr_delay).ToList().Average();
TimeSpan tavgDepDelayLGA = new TimeSpan();
TimeSpan tavgArrDelayLGA = new TimeSpan();
if (averageDepDelayLGA != null)
{
tavgDepDelayLGA = TimeSpan.FromMinutes((double)averageDepDelayLGA);
}
if (averageArrDelayLGA != null)
{
tavgArrDelayLGA = TimeSpan.FromMinutes((double)averageArrDelayLGA);
}
return(new Dictionary <string, string>()
{
{ tavgDepDelayLGA.TotalSeconds >= 0 ? tavgDepDelayLGA.ToString("hh\\:mm\\:ss") : "-" + tavgDepDelayLGA.ToString("hh\\:mm\\:ss"),
tavgArrDelayLGA.TotalSeconds >= 0 ? tavgArrDelayLGA.ToString("hh\\:mm\\:ss") : "-" + tavgArrDelayLGA.ToString("hh\\:mm\\:ss") }
});
}
private void btn_GrabarTipo_Click(object sender, EventArgs e)
{
if (_tipoArticulosNueno)
{
_contexto.Dispose();
_contexto = new DB_Context();
var NuevoTipoArticulo = new TipoArticulo
{
Nombre = txt_Nombre.Text,
Descripcion = rtb_Descripcion.Text,
EstasActivo = ckb_EstaAciva.Checked
};
_contexto.TipoArticulos.Add(NuevoTipoArticulo);
_contexto.SaveChanges();
MessageBox.Show(" Tipo de articulo ha sido creado exitosamente");
this.ClearAll("all");
this.LoadListaArticulos();
}
else
{
_contexto.Dispose();
_contexto = new DB_Context();
int IdTipoArticulo = int.Parse(txt_Id.Text);
var ModificarTipoArticulo = _contexto.TipoArticulos.FirstOrDefault(t => t.Id_TipoArticulo == IdTipoArticulo);
ModificarTipoArticulo.Nombre = txt_Nombre.Text;
ModificarTipoArticulo.Descripcion = rtb_Descripcion.Text;
ModificarTipoArticulo.EstasActivo = ckb_EstaAciva.Checked;
_contexto.SaveChanges();
MessageBox.Show(" Tipo de articulo ha sido Modificado exitosamente");
this.ClearAll("all");
this.LoadListaArticulos();
}
}
public Dictionary <string, int> ManufacturersMoreThanTwoHundredPlanes()
{
var context = new DB_Context();
return(context.Planes.Select(p => p.manufacturer).ToList()
.GroupBy(m => m).Where(m => m.Count() >= 200).ToDictionary(g => g.Key, g => g.Count()));
}
public IHttpActionResult Update([FromBody] CategoryViewModel dataBody)
{
// membuat koneksi DB
var db = new DB_Context();
try
{
Dictionary <string, object> result = new Dictionary <string, object>();
// Buat Object dan menginisialisasi dengan data dari database menggunakan method Find(Primary Key)
Category category = db.Categories.Find(dataBody.CategoryID);
// inisialisasi data yang akan dirubah
category.CategoryID = dataBody.CategoryID;
category.CategoryName = dataBody.CategoryName;
category.Description = dataBody.Description;
category.Picture = dataBody.picture;
db.SaveChanges();
db.Dispose();
result.Add("Message", "Update data success");
return(Ok(result));
}
catch (Exception)
{
throw;
}
}
public Dictionary <string, int> FlightsManufacturersMoreThanTwoHundredPlanes()
{
var context = new DB_Context();
Dictionary <string, int> flightsManufacturersMoreThanTwoHundredPlanes = new Dictionary <string, int>();
Dictionary <string, int> manufacturersMoreThanTwoHundredPlanes = ManufacturersMoreThanTwoHundredPlanes();
Dictionary <string, int> countByTailNum = context.Flights.Select(f => f.tailnum).ToList().GroupBy(m => m).ToDictionary(g => g.Key, g => g.Count());
foreach (KeyValuePair <string, int> pair in manufacturersMoreThanTwoHundredPlanes)
{
List <string> tailNums = context.Planes.Where(p => p.manufacturer.Equals(pair.Key)).Select(p => p.tailnum).ToList();
int flights = 0;
int flightsForTailNum = 0;
foreach (string tailNum in tailNums)
{
countByTailNum.TryGetValue(tailNum, out flightsForTailNum);
flights += flightsForTailNum;
}
flightsManufacturersMoreThanTwoHundredPlanes.Add(pair.Key, flights);
}
return(flightsManufacturersMoreThanTwoHundredPlanes);
}
public IHttpActionResult Create([FromBody] CategoryViewModel dataBody)
{
var db = new DB_Context();
try
{
Dictionary <string, object> result = new Dictionary <string, object>();
// Buat Object dan menginisialisasi dengan data dari body
Category newCategory = new Category()
{
CategoryID = dataBody.CategoryID,
CategoryName = dataBody.CategoryName,
Description = dataBody.Description,
Picture = dataBody.picture
};
// menambahkan kategori baru ke Category Entity Database
db.Categories.Add(newCategory);
// method yang digunakan untuk menyimpan perubahan baru di database
db.SaveChanges();
db.Dispose();
result.Add("Message", "Insert data success");
return(Ok(result));
}
catch (Exception)
{
throw;
}
}
public Dictionary <string, int> FlightsPerMonthForLGA()
{
var context = new DB_Context();
return(context.Flights.Where(f => !string.IsNullOrEmpty(f.origin) && f.origin.Equals("LGA")).Select(f => f.month).ToList()
.GroupBy(m => m).OrderBy(g => g.Key).ToDictionary(g => monthsByNumber[g.Key], g => g.Count()));
}
public IHttpActionResult ReadBy2(int?orderId = null)
{
Dictionary <string, object> result = new Dictionary <string, object>();
List <OrderCusViewModel> listDetailOrder = new List <OrderCusViewModel>();
using (var db = new DB_Context())
{
var dataOrders = db.Orders.AsQueryable();
if (orderId != null)
{
dataOrders = dataOrders.Where(item => item.OrderID == orderId);
}
var listOrder = dataOrders.AsEnumerable().ToList();
foreach (var item in listOrder)
{
var listProductDetail = item.Order_Details.ToList()
.Select(dt => new OrderCusViewModel1(dt)).ToList();
OrderCusViewModel orderDetail = new OrderCusViewModel(item.OrderID, item.Customer.ContactName, listProductDetail);
listDetailOrder.Add(orderDetail);
}
result.Add("Message", "Read Data Success");
result.Add("Data", listDetailOrder);
}
return(Ok(result));
}
public Dictionary <string, int> FlightsToTopTenDestinations()
{
var context = new DB_Context();
return(context.Flights.Select(f => f.dest).ToList().GroupBy(d => d).ToDictionary(g => g.Key, g => g.Count())
.OrderByDescending(val => val.Value).Take(10).ToDictionary(g => g.Key, g => g.Value));
}
public IHttpActionResult Soal()
{
var DB = new DB_Context();
try
{
Dictionary <string, object> result = new Dictionary <string, object>();
IEnumerable <ProductsViewModel> expensiveValue = DB.Products.Where(data => data.UnitPrice == DB.Products.Max(max => max.UnitPrice))
.ToList().Select(item => new ProductsViewModel(item));
IEnumerable <ProductsViewModel> cheapestValue = DB.Products.Where(data => data.UnitPrice == DB.Products.Min(min => min.UnitPrice))
.ToList().Select(item => new ProductsViewModel(item));
IEnumerable <ProductsViewModel> avgValue = DB.Products.Where(data => data.UnitPrice < DB.Products.Average(avg => avg.UnitPrice))
.ToList().Select(item => new ProductsViewModel(item));
DB.Dispose();
result.Add("expensiveValue", expensiveValue);
result.Add("cheapestValue", cheapestValue);
result.Add("minAverageValue", avgValue);
return(Ok(result));
}
catch (Exception)
{
throw;
}
}
public IHttpActionResult ReadAll(int?regionID = null)
{
using (var DB = new DB_Context())
{
try
{
var tem = DB.Regions.AsQueryable();
RegionDetailViewModel regionObj = new RegionDetailViewModel();
List <RegionDetailViewModel> listRegion = new List <RegionDetailViewModel>();
if (regionID != null)
{
tem = tem.Where(data => data.RegionID == regionID);
}
var listRegionEntity = tem.AsEnumerable().ToList();
foreach (var item in listRegionEntity)
{
RegionDetailViewModel region = new RegionDetailViewModel(item);
listRegion.Add(region);
}
return(Ok(regionObj.finalResult(listRegion, "Read Data Success")));
}
catch (Exception)
{
throw;
}
}
}
public IHttpActionResult Create([FromBody] ProductsViewModel dataBody)
{
var DB = new DB_Context();
try
{
Dictionary <string, object> result = new Dictionary <string, object>();
Product newProduct = new Product()
{
ProductID = dataBody.ProductID,
ProductName = dataBody.ProductName,
SupplierID = dataBody.SupplierID,
CategoryID = dataBody.CategoryID,
QuantityPerUnit = dataBody.QuantityPerUnit,
UnitPrice = dataBody.UnitPrice,
UnitsInStock = dataBody.UnitsInStock,
ReorderLevel = dataBody.ReorderLevel,
Discontinued = dataBody.Discontinued
};
DB.Products.Add(newProduct);
DB.SaveChanges();
DB.Dispose();
result.Add("Message", "Read Data Succses");
return(Ok(result));
}
catch (Exception)
{
throw;
}
}
public IHttpActionResult Update([FormBody] ProductViewModel databody)
{
using (var db = new DB_Context())
{
try
{
Dictionary <string, object> result = new Dictionary <string, object>();
Product product = db.Products.Find(databody.ProductID);
product.ProductID = databody.ProductID;
product.ProductName = databody.ProductName;
product.SupplierID = databody.SupplierID;
product.CategoryID = databody.CategoryID;
product.QuantityPerUnit = databody.QuantityPerUnit;
product.UnitPrice = databody.UnitPrice;
product.UnitsInStock = databody.UnitsInStock;
product.UnitsOnOrder = databody.UnitsOnOrder;
product.ReorderLevel = databody.ReorderLevel;
product.Discontinued = databody.Discontinued;
db.SaveChanges();
result.Add("Message", "Update Data Success");
return(Ok(result));
}
catch (Exception)
{
throw;
}
}
}
public static Usuario registrarse(string _usuario, string _contraseña)
{
if (!ValidadorDeContraseña.getInstanceValidadorContra().validarContraseña(_contraseña))
{
return(null);
}
else
{
var contexto = new DB_Context();
var usuarioExistente = contexto.usuario
.Where(u => u.NombreUsuario == _usuario)
.FirstOrDefault();
if (usuarioExistente == null)
{
Usuario usuarioNuevo = new Usuario(_contraseña, _usuario);
contexto.usuario.Add(usuarioNuevo);
contexto.SaveChanges();
Logger.getInstance.update("Se agrega un nuevo usuario." + " ID Usuario:" + usuarioNuevo.ID_Usuario.ToString());
return(usuarioNuevo);
}
else
{
return(null);
}
}
}
private DatabaseContextHelper()
{
dbContext = new DB_Context();
}
