/// <summary>
/// Display datagridviewOrder
/// </summary>
public void DisplayOder()
{
//open the RosePurchaseEntities context
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
//Create a list to display order
List <OrderDisplay> orderDisplayList = new List <OrderDisplay>();
//set up each properties for each OrderlIst Object
var orderList = (from rose in context.Roses
from size in rose.RoseSizes
from order in size.Orders
select new
{
oderid = order.OrderID,
name = rose.RoseName,
bunches = order.Number_of_bunches,
}).ToList();
foreach (var order in orderList)
{
OrderDisplay orderDisplay = new OrderDisplay()
{
OderId = order.oderid,
NumberOfBunches = order.bunches,
RoseName = order.name
};
orderDisplayList.Add(orderDisplay);
}
//set up datasource for dataGridViewOrder
dataGridViewOrder.DataSource = orderDisplayList;
}
}
List <SupplierInventory> GetSupplierInventory()
{
List <SupplierInventory> supplierInventories = new List <SupplierInventory>();
List <Inventory> inventory = (List <Inventory>) Controller <RosePurchaseManagementEntities, Inventory> .GetEntitiesWithIncluded("BoxInventories", "Farm");
List <Box> box = (List <Box>) Controller <RosePurchaseManagementEntities, Box> .GetEntitiesWithIncluded("BoxInventories");
foreach (Inventory inventories in inventory)
{
foreach (BoxInventory boxes in inventories.BoxInventories)
{
SupplierInventory supplierInventory = new SupplierInventory()
{
InventoryID = inventories.InventoryID,
FarmID = inventories.FarmID,
FarmName = inventories.Farm.FarmName,
Price = inventories.Price_per_stem,
Quantity = boxes.Quantity,
};
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
var roseName = context.RoseSizes.Include("Rose").Where(x => x.RoseSizeID == inventories.RoseSizeID).Select(r => r.Rose.RoseName).FirstOrDefault();
supplierInventory.RoseName = roseName;
}
supplierInventories.Add(supplierInventory);
}
}
return(supplierInventories);
}
private void ButtonDeleteInventory_Click(object sender, EventArgs e)
{
if ((dataGridViewFlowers.SelectedRows.Count <= 0))
{
MessageBox.Show("Please select the inventory to delete");
return;
}
var selectedInventory = dataGridViewFlowers.SelectedRows
.OfType <DataGridViewRow>()
.ToList();
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
var inven = (SupplierInventory)selectedInventory.Select(x => x).FirstOrDefault().DataBoundItem;
var selectedBoxId = context.Boxes.Where(b => b.BoxID == inven.BoxID).Select(i => i.BoxID).FirstOrDefault();
Inventory inventory = context.Inventories.Where(z => z.InventoryID == inven.InventoryID).FirstOrDefault();
BoxInventory boxInventory = context.BoxInventories.Where(b => (b.BoxID == selectedBoxId) && (b.InventoryID == inven.InventoryID)).FirstOrDefault();
context.BoxInventories.Remove(boxInventory);
context.SaveChanges();
context.Inventories.Remove(inventory);
context.SaveChanges();
}
GetSupplierInventory();
}
/// <summary>
/// Deletes the selected row in Purchasing datagridView. Deleting the entity Purchase and BoxPurchase.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ButtonDelete_Click(object sender, EventArgs e)
{
if ((dataGridViewPurchase.SelectedRows.Count <= 0))
{
MessageBox.Show("Please select the purchase to delete");
return;
}
//selecting the row
var selectedPurchase = dataGridViewPurchase.SelectedRows
.OfType <DataGridViewRow>()
.ToList();
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
var pur = (PurchaseBoxQuantity)selectedPurchase.Select(x => x).FirstOrDefault().DataBoundItem;
var selectedBoxId = context.Boxes.Where(b => b.BoxName == pur.BoxName).Select(i => i.BoxID).FirstOrDefault();
Purchase purchase = context.Purchases.Where(p => p.PurchaseID == pur.PurchaseID).FirstOrDefault();
BoxPurchase boxPurchase = context.BoxPurchases.Where(b => (b.BoxID == selectedBoxId) && (b.PurchaseID == pur.PurchaseID)).FirstOrDefault();
context.BoxPurchases.Remove(boxPurchase);
context.SaveChanges();
context.Purchases.Remove(purchase);
context.SaveChanges();
}
//updates the datagridview after the deletions
UpdatePurchase();
}
private void RosePurchaseManagementMainForm_Load()
{
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
context.SeedDatabase();
}
}
public static bool IsValidPurchaseId(this Purchase purchase)
{
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
context.Database.Log = (s => Debug.Write(s));
return(context.Purchases.Any(c => c.PurchaseID == purchase.PurchaseID));
}
}
/// <summary>
/// Checks to see if the RoseSizeId is valid.
/// </summary>
public static bool IsValidRoseSizeId(this Order order, bool checkIfOrdered = false)
{
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
context.Database.Log = (s => Debug.Write(s));
return(context.Orders.Any(o => o.RoseSizeID == order.RoseSizeID));
}
}
public static bool IsValidOrderId(this Order order)
{
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
context.Database.Log = (s => Debug.Write(s));
return(context.Orders.Any(c => c.OrderID == order.OrderID));
}
}
/// <summary>
/// Update listener of Order
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ButtonUpdateOrder_Click(object sender, EventArgs e)
{
//making sure a student is selected
if (dataGridViewOrder.SelectedRows.Count < 0)
{
MessageBox.Show("Order to be updated must be selected");
return;
}
else
{
//Check whether the datafield is intialized
if (int.TryParse(textBoxNumberOfBunches.Text, out int numberOfBunches) && (listBoxRoses.SelectedItems.Count > 0))
{
//Get the row from the datagridviewOrder as Order displa
var selectedOrder = dataGridViewOrder.SelectedRows
.OfType <DataGridViewRow>()
.ToList();
var ord = (OrderDisplay)selectedOrder.Select(x => x).FirstOrDefault().DataBoundItem;
//Create a new order
Order order = new Order();
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
//Get the selected order
order = context.Orders.Where(i => i.OrderID == ord.OderId).FirstOrDefault();
//select the item from the listbox
String selectedRoses = listBoxRoses.SelectedItem.ToString();
//get the roseId for the selected roses
var roseSizeId = context.RoseSizes.Include("Rose").Where(r => r.Rose.RoseName == selectedRoses).FirstOrDefault();
//set properties ororder
order.RoseSizeID = roseSizeId.RoseSizeID;
order.Number_of_bunches = numberOfBunches;
}
// Update order to the list using controller
Controller <RosePurchaseManagementEntities, Order> .UpdateEntity(order);
//display orderDatagridView
DisplayOder();
//clear selected roses
listBoxRoses.ClearSelected();
//empty string
textBoxNumberOfBunches.Text = "";
}
else
{
//display if data is not valid
MessageBox.Show("Enter the valid data");
}
}
}
/// <summary>
/// When the SuppliersInventory datagridview changes the selection, the data is displayed into the different textboxes.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void DataGridViewSuppliersInventory_SelectionChanged(object sender, EventArgs e)
{
listBoxInvoice.DataSource = null;
Inventory inventory = (Inventory)dataGridViewSuppliersInventory.CurrentRow.DataBoundItem;
listBoxBox.ClearSelected();
listBoxWarehouse.ClearSelected();
textBoxQuantity.Text = null;
textBoxFarmName.Text = inventory.Farm.FarmName.ToString();
textBoxRoseSizeID.Text = inventory.RoseSizeID.ToString();
textBoxPricePerStem.Text = inventory.Price_per_stem.ToString();
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
var invoiceList = context.Invoices.Include("Farm").Where(x => x.FarmID == inventory.FarmID).Select(x => x.InvoiceID);
listBoxInvoice.DataSource = invoiceList.ToList();
}
}
/// <summary>
/// Display Listbox roses
/// </summary>
public void DisplayRoses()
{
//get the selected items from the listWareHouse
var selectedList = listBoxWarehouse.SelectedItems;
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
//Get each Roses in each WareHouse
//Get the selected warehouse from the Listbox
List <String> list = new List <String>();
foreach (String warehouse in selectedList)
{
var wareHouseId = context.Warehouses.Where(x => x.WarehouseName == warehouse).Select(i => i.WarehouseID).FirstOrDefault();
var item = context.Purchases.Include("RoseSize").Where(x => x.WarehouseID == wareHouseId).Select(r => r.RoseSize.Rose.RoseName).FirstOrDefault();
list.Add(item);
}
listBoxRosesIn.DataSource = list;
}
}
/// <summary>
/// DElete order listner
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ButtonDeleteOrder_Click(object sender, EventArgs e)
{
//To get the selected row from the order table
var selectedOrder = dataGridViewOrder.SelectedRows
.OfType <DataGridViewRow>()
.ToArray();
//Remove each selected by selecting
foreach (var row in selectedOrder)
{
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
var ord = (OrderDisplay)row.DataBoundItem;
Order order = context.Orders.Where(x => x.OrderID == ord.OderId).FirstOrDefault();
context.Orders.Remove(order);
context.SaveChanges();
}
}
DisplayOder();
}
/// <summary>
/// Adds a new Invoice entity to Invoice
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ButtonInvoiceAdd_Click(object sender, EventArgs e)
{
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
string selectedFarm = listBoxFarms.SelectedItem.ToString();
var farmID = context.Farms.Where(f => f.FarmName == selectedFarm).FirstOrDefault();
Invoice invoice = new Invoice()
{
InvoiceID = int.Parse(textBoxInvoiceNumber.Text),
Date = dateTimePickerInvoice.Value,
TotalAmount = float.Parse(textBoxTotalAmount.Text.Trim()),
FarmID = farmID.FarmID,
};
if (Controller <RosePurchaseManagementEntities, Invoice> .AddEntity(invoice) == null)
{
MessageBox.Show("cannot add invoice to database");
return;
}
}
UpdateInvoice();
}
/// <summary>
/// When the SelectionChanged from DataGridViewPurchase, data is displayed in different textboxes.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void DataGridViewPurchase_SelectionChanged(object sender, EventArgs e)
{
dataGridViewSuppliersInventory.ClearSelection();
var selectedPurchase = dataGridViewPurchase.SelectedRows
.OfType <DataGridViewRow>()
.ToList();
if (dataGridViewPurchase.SelectedRows.Count != 0)
{
var pur = (PurchaseBoxQuantity)selectedPurchase.Select(x => x).FirstOrDefault().DataBoundItem;
textBoxFarmName.Text = pur.FarmName;
textBoxPricePerStem.Text = pur.Price.ToString();
textBoxQuantity.Text = pur.BoxQuantity.ToString();
textBoxInvoiceID.Text = pur.InvoiceNumber.ToString();
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
var purch = context.Purchases.Where(p => p.PurchaseID == pur.PurchaseID).FirstOrDefault();
var farmId = context.Farms.Where(x => x.FarmName == pur.FarmName).Select(i => i.FarmID).FirstOrDefault();
var inventoryId = context.Inventories.Where(x => (x.FarmID == purch.FarmID) && (x.RoseSizeID == purch.RoseSizeID)).Select(x => x.InventoryID).FirstOrDefault();
var invoiceList = context.Invoices.Include("Farm").Where(x => x.FarmID == farmId).Select(x => x.InvoiceID);
listBoxInvoice.DataSource = null;
listBoxInvoice.DataSource = invoiceList.ToList();
textBoxRoseSizeID.Text = purch.RoseSizeID.ToString();
dataGridViewSuppliersInventory.SelectionChanged -= DataGridViewSuppliersInventory_SelectionChanged;
dataGridViewSuppliersInventory.Rows[inventoryId - 1].Selected = true;
dataGridViewSuppliersInventory.SelectionChanged += DataGridViewSuppliersInventory_SelectionChanged;
}
listBoxInvoice.SelectedIndex = listBoxInvoice.Items.IndexOf(pur.InvoiceNumber);
listBoxWarehouse.SelectedIndex = listBoxWarehouse.Items.IndexOf(pur.WarehouseName);
listBoxBox.SelectedIndex = listBoxBox.Items.IndexOf(pur.BoxName);
boxId = listBoxBox.SelectedIndex + 1;
}
}
/// <summary>
/// Creates a new PUrchase entity and a new BoxPurchase entity. Updates the datagridView Purchase
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ButtonPurchase_Click(object sender, EventArgs e)
{
Purchase purchase = new Purchase();
BoxPurchase boxPurchase = new BoxPurchase();
if ((listBoxWarehouse.SelectedItems.Count < 0))
{
MessageBox.Show("Warehouse to be selected");
return;
}
if ((listBoxBox.SelectedItems.Count < 0))
{
MessageBox.Show("Box to be selected");
return;
}
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
//select the item from the listbox
string selectedFarm = textBoxFarmName.Text.Trim();
int selectedInvoice = int.Parse(textBoxInvoiceID.Text);
//get the farmId for the selected farm
var farmID = context.Farms.Where(f => f.FarmName == selectedFarm).FirstOrDefault();
purchase.FarmID = farmID.FarmID;
purchase.RoseSizeID = int.Parse(textBoxRoseSizeID.Text);
purchase.Price_per_stem = float.Parse(textBoxPricePerStem.Text);
purchase.InvoiceID = int.Parse(textBoxInvoiceID.Text);
purchase.WarehouseID = listBoxWarehouse.SelectedIndex + 1;
var purch = Controller <RosePurchaseManagementEntities, Purchase> .AddEntity(purchase);
boxPurchase.PurchaseID = purch.PurchaseID;
boxPurchase.BoxID = listBoxBox.SelectedIndex + 1;
boxPurchase.Quantity = int.Parse(textBoxQuantity.Text);
Controller <RosePurchaseManagementEntities, BoxPurchase> .AddEntity(boxPurchase);
}
UpdatePurchase();
Clear();
}
/// <summary>
/// Deletes a selected Invoice when the Invoice it is not added to a purchase. Otherwise first you have to delete the purchase, the boxPurchase and then the invoice
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ButtonInvoiceDelete_Click(object sender, EventArgs e)
{
if ((dataGridViewInvoice.SelectedRows.Count <= 0))
{
MessageBox.Show("Please select the invoice to delete");
return;
}
//To get the selected row from the invoice table
var selectedInvoice = dataGridViewInvoice.SelectedRows
.OfType <DataGridViewRow>()
.ToArray();
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
var inv = (Invoice)selectedInvoice.Select(x => x).FirstOrDefault().DataBoundItem;
Invoice invoice = context.Invoices.Where(x => x.InvoiceID == inv.InvoiceID).FirstOrDefault();
context.Invoices.Remove(invoice);
context.SaveChanges();
}
UpdateInvoice();
}
/// <summary>
/// Creates a list of PurchaseBoxquantity objects and returns the list of objects
/// </summary>
/// <returns></returns>
List <PurchaseBoxQuantity> GetPurchaseBoxQuantities()
{
List <PurchaseBoxQuantity> purchaseBoxQuantities = new List <PurchaseBoxQuantity>();
List <Purchase> purchases = (List <Purchase>) Controller <RosePurchaseManagementEntities, Purchase> .GetEntitiesWithIncluded("BoxPurchases", "RoseSize", "Farm", "Invoice", "Warehouse");
List <Box> boxes = (List <Box>) Controller <RosePurchaseManagementEntities, Box> .GetEntitiesWithIncluded("BoxPurchases");
foreach (Purchase purchase in purchases)
{
foreach (BoxPurchase boxPurchase in purchase.BoxPurchases)
{
PurchaseBoxQuantity purchaseBoxQuantity = new PurchaseBoxQuantity()
{
PurchaseID = purchase.PurchaseID,
FarmName = purchase.Farm.FarmName,
Price = purchase.Price_per_stem,
InvoiceNumber = purchase.InvoiceID,
WarehouseName = purchase.Warehouse.WarehouseName,
BoxQuantity = boxPurchase.Quantity
};
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
var roseName = context.RoseSizes.Include("Rose").Where(x => x.RoseSizeID == purchase.RoseSizeID).Select(r => r.Rose.RoseName).FirstOrDefault();
purchaseBoxQuantity.RoseName = roseName;
var roseSize = context.RoseSizes.Include("Size").Where(r => r.RoseSizeID == purchase.RoseSizeID).Select(s => s.Size.SizeName).FirstOrDefault();
purchaseBoxQuantity.RoseSize = roseSize;
var boxType = context.BoxPurchases.Include("Box").Where(x => x.BoxID == boxPurchase.BoxID).Select(t => t.Box.BoxName).FirstOrDefault();
purchaseBoxQuantity.BoxName = boxType;
}
purchaseBoxQuantities.Add(purchaseBoxQuantity);
}
}
return(purchaseBoxQuantities);
}
private void ButtonUpdateInventory_Click(object sender, EventArgs e)
{
var selectedInventory = dataGridViewFlowers.SelectedRows
.OfType <DataGridViewRow>()
.ToArray();
if (dataGridViewFlowers.SelectedRows.Count != 0)
{
var inven = (SupplierInventory)selectedInventory.Select(x => x).FirstOrDefault().DataBoundItem;
Inventory invent = new Inventory();
BoxInventory boxinventory = new BoxInventory();
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
//select the item from the listbox
String selectedFarm = listBoxFarms.SelectedItem.ToString();
String selectedRose = comboBoxRoses.Text;
//get the roseId for the selected roses
var farmId = context.Inventories.Include("Farm").Where(f => f.Farm.FarmName == selectedFarm).FirstOrDefault();
var roseSizeId = context.RoseSizes.Include("Rose").Where(r => r.Rose.RoseName == selectedRose).FirstOrDefault();
invent.FarmID = farmId.FarmID;
invent.RoseSizeID = roseSizeId.RoseSizeID;
invent.Price_per_stem = float.Parse(textBoxPrice.Text);
boxinventory.Quantity = int.Parse(textBoxQuantity.Text);
context.SaveChanges();
Controller <RosePurchaseManagementEntities, Inventory> .UpdateEntity(invent);
Controller <RosePurchaseManagementEntities, BoxInventory> .UpdateEntity(boxinventory);
}
GetSupplierInventory();
}
}
/// <summary>
/// listner for oder button
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ButtonOrder_Click(object sender, EventArgs e)
{
if (int.TryParse(textBoxNumberOfBunches.Text, out int numberOfBunches) && (listBoxRoses.SelectedItems.Count > 0))
{
//create an oder
Order order = new Order();
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
//select the item from the listbox
String selectedRoses = listBoxRoses.SelectedItem.ToString();
//get the roseId for the selected roses
var roseSizeId = context.RoseSizes.Include("Rose").Where(r => r.Rose.RoseName == selectedRoses).FirstOrDefault();
order.RoseSizeID = roseSizeId.RoseSizeID;
order.Number_of_bunches = numberOfBunches;
}
// add oder to the list using controller
if (Controller <RosePurchaseManagementEntities, Order> .AddEntity(order) == null)
{
MessageBox.Show("Cannot add order to database");
return;
}
//display orderDatagridView
DisplayOder();
//clear selected roses
listBoxRoses.ClearSelected();
//empty string
textBoxNumberOfBunches.Text = "";
}
else
{
MessageBox.Show("Enter the valid data");
}
}
private void ButtonAddInventory_Click(object sender, EventArgs e)
{
Inventory invent = new Inventory();
BoxInventory boxinventory = new BoxInventory();
if (listBoxFarms.SelectedIndex < 0)
{
MessageBox.Show("A farm must be selected. ");
return;
}
if (textBoxQuantity.Text == "")
{
MessageBox.Show("Quantity must be inserted.");
return;
}
if (comboBoxRoses.Text == "")
{
MessageBox.Show("Rose name must be inserted..");
return;
}
if (textBoxPrice.Text == "")
{
MessageBox.Show("Price must be inserted.");
return;
}
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
//select the item from the listbox
String selectedFarm = listBoxFarms.SelectedItem.ToString();
String selectedRose = comboBoxRoses.Text;
//get the roseId and farmid for the selected roses and farms
var farmId = context.Inventories.Include("Farm").Where(f => f.Farm.FarmName == selectedFarm).FirstOrDefault();
var roseSizeId = context.RoseSizes.Include("Rose").Where(r => r.Rose.RoseName == selectedRose).FirstOrDefault();
invent.FarmID = farmId.FarmID;
invent.RoseSizeID = roseSizeId.RoseSizeID;
invent.Price_per_stem = float.Parse(textBoxPrice.Text);
boxinventory.Quantity = int.Parse(textBoxPrice.Text);
}
// add inventory to the list using controller
if (Controller <RosePurchaseManagementEntities, Inventory> .AddEntity(invent) == null || Controller <RosePurchaseManagementEntities, BoxInventory> .AddEntity(boxinventory) == null)
{
MessageBox.Show("Cannot add order to database");
return;
}
//display
GetSupplierInventory();
//clear selected farms
listBoxFarms.ClearSelected();
//empty string
comboBoxRoses.Text = "";
textBoxPrice.Text = "";
textBoxQuantity.Text = "";
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
// String selectedRose = comboBoxRoses.Text;
String selectedFarm = listBoxFarms.SelectedItem.ToString();
var farmID = context.Inventories.Include("Farm").Where(f => f.Farm.FarmName == selectedFarm).FirstOrDefault();
// var roseSizeId = context.RoseSizes.Include("Rose").Where(r => r.Rose.RoseName == selectedRose).FirstOrDefault();
invent.FarmID = listBoxFarms.SelectedIndex + 1;
invent.Price_per_stem = float.Parse(textBoxPrice.Text);
// invent.RoseSizeID = roseSizeId.RoseSizeID;
boxinventory.Quantity = int.Parse(textBoxQuantity.Text);
Controller <RosePurchaseManagementEntities, BoxInventory> .AddEntity(boxinventory);
}
if (Controller <RosePurchaseManagementEntities, Inventory> .AddEntity(invent) == null)
{
MessageBox.Show("Cannot add to database");
return;
}
GetSupplierInventory();
}
/// <summary>
/// zero out the db tables, then seed all tables with initial data
/// </summary>
public static void SeedDatabase(this RosePurchaseManagementEntities context)
{
// set up database log to write to output window in VS
context.Database.Log = (s => Debug.Write(s));
// reset the db
context.Database.Delete();
context.Database.Create();
context.SaveChanges();
// another way to reinitialize the database, resetting everything and zeroing out data
//Database.SetInitializer(new DropCreateDatabaseAlways<StudentRegistrationEntities>());
//context.Database.Initialize(true);
context.Boxes.Load();
context.BoxInventories.Load();
context.BoxPurchases.Load();
context.Farms.Load();
context.Groups.Load();
context.Inventories.Load();
context.Invoices.Load();
context.Orders.Load();
context.Purchases.Load();
context.Roses.Load();
context.RoseSizes.Load();
context.Sizes.Load();
context.Warehouses.Load();
//Loading Box data to the database
List <Box> boxList = new List <Box>()
{
new Box {
BoxID = 1, BoxName = "Quarter Box"
},
new Box {
BoxID = 2, BoxName = "Half Box"
},
new Box {
BoxID = 3, BoxName = "Full Box"
},
};
Dictionary <int, Box> boxes = boxList.ToDictionary(x => x.BoxID, x => x);
context.Boxes.AddRange(boxes.Values);
//Loading Group data to the database
List <Group> groupList = new List <Group> {
new Group {
GroupName = "Cream", GroupCode = "CR"
},
new Group {
GroupName = "Green", GroupCode = "GR"
},
new Group {
GroupName = "Mixture", GroupCode = "MIX"
},
new Group {
GroupName = "Orange", GroupCode = "OR"
},
new Group {
GroupName = "PINK", GroupCode = "PI"
},
new Group {
GroupName = "RED", GroupCode = "RE"
},
new Group {
GroupName = "White", GroupCode = "WH"
},
new Group {
GroupName = "Two Colors", GroupCode = "TT"
},
};
Dictionary <String, Group> group = groupList.ToDictionary(x => x.GroupCode, x => x);
context.Groups.AddRange(group.Values);
//Loading warehouse data to the database
List <Warehouse> warehouseList = new List <Warehouse> {
new Warehouse {
WarehouseID = 1, WarehouseName = "BQT"
},
new Warehouse {
WarehouseID = 2, WarehouseName = "CARL"
},
new Warehouse {
WarehouseID = 3, WarehouseName = "ECLO"
},
new Warehouse {
WarehouseID = 4, WarehouseName = "ERIC"
},
new Warehouse {
WarehouseID = 5, WarehouseName = "MATS"
},
new Warehouse {
WarehouseID = 6, WarehouseName = "ORCHI"
},
new Warehouse {
WarehouseID = 7, WarehouseName = "SHINE"
},
new Warehouse {
WarehouseID = 8, WarehouseName = "KARE9"
},
new Warehouse {
WarehouseID = 9, WarehouseName = "TWIG3"
},
new Warehouse {
WarehouseID = 10, WarehouseName = "MISCMA"
},
new Warehouse {
WarehouseID = 11, WarehouseName = "SOUT9"
},
new Warehouse {
WarehouseID = 12, WarehouseName = "ROLL"
},
new Warehouse {
WarehouseID = 13, WarehouseName = "VIDAL"
},
new Warehouse {
WarehouseID = 14, WarehouseName = "MOSA"
},
new Warehouse {
WarehouseID = 15, WarehouseName = "JEAN8"
},
new Warehouse {
WarehouseID = 16, WarehouseName = "HFM"
},
new Warehouse {
WarehouseID = 17, WarehouseName = "VEGAS"
},
};
Dictionary <int, Warehouse> warehouses = warehouseList.ToDictionary(x => x.WarehouseID, x => x);
context.Warehouses.AddRange(warehouses.Values);
//Loading Farm data to the database
List <Farm> farmList = new List <Farm>()
{
new Farm {
FarmID = 1, FarmName = "Qualisa", Phone = "593484427", Email = "*****@*****.**"
},
new Farm {
FarmID = 2, FarmName = "Ecuatorian", Phone = "593556879", Email = "*****@*****.**"
},
new Farm {
FarmID = 3, FarmName = "Welyflor", Phone = "593556543", Email = "*****@*****.**"
},
new Farm {
FarmID = 4, FarmName = "Inroses", Phone = "593566543", Email = "*****@*****.**"
},
};
Dictionary <int, Farm> farms = farmList.ToDictionary(x => x.FarmID, x => x);
context.Farms.AddRange(farms.Values);
context.SaveChanges();
//Loading Rose data to the database
List <Rose> RoseList = new List <Rose>()
{
new Rose {
RoseID = 1, RoseName = "Creme de la Creme", GroupCode = "CR"
},
new Rose {
RoseID = 2, RoseName = "Vendela", GroupCode = "CR"
},
new Rose {
RoseID = 3, RoseName = "Green Tea", GroupCode = "GR"
},
new Rose {
RoseID = 4, RoseName = "Full Mix Color", GroupCode = "MIX"
},
new Rose {
RoseID = 5, RoseName = "Cayenne", GroupCode = "OR"
},
new Rose {
RoseID = 6, RoseName = "OrangeCabaret Crush", GroupCode = "OR"
},
new Rose {
RoseID = 7, RoseName = "Carrousel", GroupCode = "PI"
},
new Rose {
RoseID = 8, RoseName = "Freedom", GroupCode = "RE"
},
new Rose {
RoseID = 9, RoseName = "Cabaret", GroupCode = "TT"
},
new Rose {
RoseID = 10, RoseName = "Tibet", GroupCode = "WH"
},
new Rose {
RoseID = 11, RoseName = "Free Spirit", GroupCode = "OR"
},
};
Dictionary <int, Rose> roses = RoseList.ToDictionary(x => x.RoseID, x => x);
context.Roses.AddRange(roses.Values);
//Loading size data to the database
List <Size> sizeList = new List <Size>()
{
new Size {
SizeID = 1, SizeName = "40", Freight = 0.16f
},
new Size {
SizeID = 2, SizeName = "50", Freight = 0.18f
},
new Size {
SizeID = 3, SizeName = "40/50", Freight = 0.17f
},
new Size {
SizeID = 4, SizeName = "60", Freight = 0.20f
},
new Size {
SizeID = 5, SizeName = "50/60", Freight = 0.19f
},
new Size {
SizeID = 6, SizeName = "70", Freight = 0.22f
},
};
Dictionary <int, Size> sizes = sizeList.ToDictionary(x => x.SizeID, x => x);
context.Sizes.AddRange(sizes.Values);
//Loading RoseSize data to the database
List <RoseSize> RoseSizeList = new List <RoseSize>()
{
new RoseSize {
RoseSizeID = 1, Rose = roses[1], Size = sizes[1]
},
new RoseSize {
RoseSizeID = 2, Rose = roses[2], Size = sizes[2]
},
new RoseSize {
RoseSizeID = 3, Rose = roses[3], Size = sizes[3]
},
new RoseSize {
RoseSizeID = 4, Rose = roses[4], Size = sizes[2]
},
new RoseSize {
RoseSizeID = 5, Rose = roses[5], Size = sizes[2]
},
new RoseSize {
RoseSizeID = 6, Rose = roses[6], Size = sizes[4]
},
new RoseSize {
RoseSizeID = 7, Rose = roses[7], Size = sizes[2]
},
new RoseSize {
RoseSizeID = 8, Rose = roses[8], Size = sizes[2]
},
new RoseSize {
RoseSizeID = 9, Rose = roses[9], Size = sizes[2]
},
new RoseSize {
RoseSizeID = 10, Rose = roses[10], Size = sizes[1]
},
};
Dictionary <int, RoseSize> roseSizes = RoseSizeList.ToDictionary(x => x.RoseSizeID, x => x);
context.RoseSizes.AddRange(roseSizes.Values);
context.SaveChanges();
//Loading size data to the database
List <Invoice> invoicesList = new List <Invoice>()
{
new Invoice {
InvoiceID = 1233, Date = DateTime.ParseExact("01-02-2021", "dd-MM-yyyy", null), TotalAmount = 70, Farm = farms[1]
},
new Invoice {
InvoiceID = 1234, Date = DateTime.ParseExact("01-02-2021", "dd-MM-yyyy", null), TotalAmount = 70, Farm = farms[1]
},
new Invoice {
InvoiceID = 2244, Date = DateTime.ParseExact("02-02-2021", "dd-MM-yyyy", null), TotalAmount = 70, Farm = farms[2]
},
new Invoice {
InvoiceID = 3124, Date = DateTime.ParseExact("02-02-2021", "dd-MM-yyyy", null), TotalAmount = 70, Farm = farms[3]
},
new Invoice {
InvoiceID = 4124, Date = DateTime.ParseExact("02-02-2021", "dd-MM-yyyy", null), TotalAmount = 70, Farm = farms[4]
},
};
Dictionary <int, Invoice> invoices = invoicesList.ToDictionary(x => x.InvoiceID, x => x);
context.Invoices.AddRange(invoices.Values);
context.SaveChanges();
//Loading order data the database
List <Order> OrderList = new List <Order>()
{
new Order {
OrderID = 1, RoseSizeID = 1, Number_of_bunches = 52
},
new Order {
OrderID = 2, RoseSizeID = 2, Number_of_bunches = 22
},
new Order {
OrderID = 3, RoseSizeID = 3, Number_of_bunches = 7
},
new Order {
OrderID = 4, RoseSizeID = 4, Number_of_bunches = 15
},
new Order {
OrderID = 5, RoseSizeID = 5, Number_of_bunches = 176
},
new Order {
OrderID = 6, RoseSizeID = 7, Number_of_bunches = 9
},
new Order {
OrderID = 7, RoseSizeID = 8, Number_of_bunches = 139
},
new Order {
OrderID = 8, RoseSizeID = 9, Number_of_bunches = 14
},
new Order {
OrderID = 9, RoseSizeID = 10, Number_of_bunches = 6
},
new Order {
OrderID = 10, RoseSizeID = 6, Number_of_bunches = 12
},
};
Dictionary <int, Order> order = OrderList.ToDictionary(x => x.OrderID, x => x);
context.Orders.AddRange(order.Values);
//Loading Inventory data to the database
List <Inventory> inventoryList = new List <Inventory>()
{
new Inventory {
InventoryID = 1, Farm = farms[1], RoseSize = roseSizes[2], Price_per_stem = 0.30f
},
new Inventory {
InventoryID = 2, Farm = farms[2], RoseSize = roseSizes[4], Price_per_stem = 0.25f
},
new Inventory {
InventoryID = 3, Farm = farms[3], RoseSize = roseSizes[3], Price_per_stem = 0.30f
},
new Inventory {
InventoryID = 4, Farm = farms[4], RoseSize = roseSizes[4], Price_per_stem = 0.30f
},
new Inventory {
InventoryID = 5, Farm = farms[1], RoseSize = roseSizes[4], Price_per_stem = 0.28f
},
new Inventory {
InventoryID = 6, Farm = farms[2], RoseSize = roseSizes[3], Price_per_stem = 0.30f
},
new Inventory {
InventoryID = 7, Farm = farms[2], RoseSize = roseSizes[6], Price_per_stem = 0.29f
},
};
Dictionary <int, Inventory> inventory = inventoryList.ToDictionary(x => x.InventoryID, x => x);
context.Inventories.AddRange(inventory.Values);
//Loading purchase data to the database
List <Purchase> purchaseList = new List <Purchase>()
{
new Purchase {
PurchaseID = 1, Farm = farms[2], RoseSize = roseSizes[4], Price_per_stem = 0.25f, InvoiceID = 2244, Warehouse = warehouses[1]
},
new Purchase {
PurchaseID = 2, Farm = farms[1], RoseSize = roseSizes[4], Price_per_stem = 0.28f, InvoiceID = 1233, Warehouse = warehouses[2]
},
new Purchase {
PurchaseID = 3, Farm = farms[3], RoseSize = roseSizes[3], Price_per_stem = 0.30f, InvoiceID = 3124, Warehouse = warehouses[3]
},
new Purchase {
PurchaseID = 4, Farm = farms[4], RoseSize = roseSizes[4], Price_per_stem = 0.30f, InvoiceID = 4124, Warehouse = warehouses[4]
},
};
Dictionary <int, Purchase> purchases = purchaseList.ToDictionary(x => x.PurchaseID, x => x);
context.Purchases.AddRange(purchases.Values);
//Loading BoxPurchase data to the database
List <BoxPurchase> boxPurchaseList = new List <BoxPurchase>()
{
new BoxPurchase {
Purchase = purchases[1], Box = boxes[3], Quantity = 2
},
new BoxPurchase {
Purchase = purchases[2], Box = boxes[2], Quantity = 10
},
new BoxPurchase {
Purchase = purchases[3], Box = boxes[1], Quantity = 4
},
new BoxPurchase {
Purchase = purchases[4], Box = boxes[3], Quantity = 5
},
};
//var boxPurchase = boxPurchaseList.ToDictionary(x => Tuple.Create(x.PurchaseID, x.BoxID), x => x);
//context.BoxPurchases.AddRange(boxPurchase.Values);
context.BoxPurchases.AddRange(boxPurchaseList);
// Loading BoxInventory data to the database
List <BoxInventory> boxInventoryList = new List <BoxInventory>()
{
new BoxInventory {
InventoryID = 1, BoxID = 2, Quantity = 2
},
new BoxInventory {
InventoryID = 2, BoxID = 3, Quantity = 10
},
new BoxInventory {
InventoryID = 3, BoxID = 1, Quantity = 4
},
new BoxInventory {
InventoryID = 4, BoxID = 3, Quantity = 5
},
new BoxInventory {
InventoryID = 5, BoxID = 3, Quantity = 9
},
new BoxInventory {
InventoryID = 6, BoxID = 1, Quantity = 3
},
new BoxInventory {
InventoryID = 7, BoxID = 2, Quantity = 2
},
};
Dictionary <int, BoxInventory> boxInventory = boxInventoryList.ToDictionary(x => x.InventoryID, x => x);
context.BoxInventories.AddRange(boxInventory.Values);
context.SaveChanges();
}
/// <summary>
/// Display PurchaseDatgridview
/// </summary>
public void DisplayPurchase()
{
//Create lsit of box Purchase Quatity
List <PurchaseBoxQuantity> purchaseBoxQuantities = new List <PurchaseBoxQuantity>();
//Get each purchases
var purchases = Controller <RosePurchaseManagementEntities, Purchase> .GetEntitiesWithIncluded("BoxPurchases", "RoseSize", "Farm", "Invoice", "Warehouse");
List <Box> boxes = (List <Box>) Controller <RosePurchaseManagementEntities, Box> .GetEntitiesWithIncluded("BoxPurchases");
//Check checkbox is checked or not
if (checkBoxSearch.Checked)
{
purchases = purchases.Where(x => x.Invoice.Date >= dateTimePickerStartDate.Value).Where(x => x.Invoice.Date <= dateTimePickerEndDate.Value);
}
//Set up properties for each PurchaseBoxQuantity object
foreach (Purchase purchase in purchases)
{
foreach (BoxPurchase boxPurchase in purchase.BoxPurchases)
{
PurchaseBoxQuantity purchaseBoxQuantity = new PurchaseBoxQuantity()
{
PurchaseID = purchase.PurchaseID,
FarmName = purchase.Farm.FarmName,
Price = purchase.Price_per_stem,
InvoiceNumber = purchase.InvoiceID,
WarehouseName = purchase.Warehouse.WarehouseName,
Total = purchase.Invoice.TotalAmount,
Date = purchase.Invoice.Date,
BoxQuantity = boxPurchase.Quantity
};
//GEt each BoxPurchase based on purchase quantity
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
var roseName = context.RoseSizes.Include("Rose").Where(x => x.RoseSizeID == purchase.RoseSizeID).Select(r => r.Rose.RoseName).FirstOrDefault();
purchaseBoxQuantity.RoseName = roseName;
var boxType = context.BoxPurchases.Include("Box").Where(x => x.BoxID == boxPurchase.BoxID).Select(t => t.Box.BoxName).FirstOrDefault();
purchaseBoxQuantity.BoxName = boxType;
}
purchaseBoxQuantities.Add(purchaseBoxQuantity);
}
}
//Getting the count
var count = purchaseBoxQuantities.Count();
labelCount.Text = count.ToString();
if (count != 0)
{
//Setting up th average
var sum = purchaseBoxQuantities.Select(x => x.Total).Average();
labelAveragePrice.Text = sum.ToString();
}
else
{
labelAveragePrice.Text = "";
}
//set up datasource of dataGridViewPurchase
dataGridViewPurchase.DataSource = purchaseBoxQuantities;
}
/// <summary>
/// Updates the purchaseBoxQuantity object, by updating the Purchase entity and the BoxPurchase entity
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ButtonUpdatePurchase_Click(object sender, EventArgs e)
{
//if no row is selected show a message
if ((listBoxWarehouse.SelectedItems.Count < 0))
{
MessageBox.Show("Warehouse to be selected");
return;
}
if ((listBoxBox.SelectedItems.Count < 0))
{
MessageBox.Show("Box to be selected");
return;
}
//select the row in datagridview
var selectedPurchase = dataGridViewPurchase.SelectedRows
.OfType <DataGridViewRow>()
.ToList();
if (dataGridViewPurchase.SelectedRows.Count != 0)
{
var pur = (PurchaseBoxQuantity)selectedPurchase.Select(x => x).FirstOrDefault().DataBoundItem;
Purchase purchase = new Purchase();
BoxPurchase boxPurchase = new BoxPurchase();
using (RosePurchaseManagementEntities context = new RosePurchaseManagementEntities())
{
purchase = context.Purchases.Where(x => x.PurchaseID == pur.PurchaseID).FirstOrDefault();
boxPurchase = context.BoxPurchases.Where(x => x.PurchaseID == pur.PurchaseID).FirstOrDefault();
//select the item from the listbox
string selectedFarm = textBoxFarmName.Text.Trim();
int selectedInvoice = int.Parse(textBoxInvoiceID.Text);
//get the farmId for the selected farm
purchase.RoseSizeID = int.Parse(textBoxRoseSizeID.Text);
purchase.Price_per_stem = float.Parse(textBoxPricePerStem.Text);
purchase.InvoiceID = int.Parse(textBoxInvoiceID.Text);
purchase.WarehouseID = listBoxWarehouse.SelectedIndex + 1;
context.SaveChanges();
if (context.BoxPurchases.Where(x => (x.PurchaseID == purchase.PurchaseID) && (x.BoxID == listBoxBox.SelectedIndex + 1)).Count() > 0)
{
boxPurchase.PurchaseID = purchase.PurchaseID;
boxPurchase.BoxID = listBoxBox.SelectedIndex + 1;
boxPurchase.Quantity = int.Parse(textBoxQuantity.Text);
context.SaveChanges();
}
else
{
var boxSel = context.BoxPurchases.Where(x => (x.PurchaseID == purchase.PurchaseID) && (x.BoxID == boxId)).FirstOrDefault();
context.BoxPurchases.Remove(boxSel);
boxPurchase = new BoxPurchase();
boxPurchase.PurchaseID = purchase.PurchaseID;
boxPurchase.BoxID = listBoxBox.SelectedIndex + 1;
boxPurchase.Quantity = int.Parse(textBoxQuantity.Text);
context.BoxPurchases.Add(boxPurchase);
context.SaveChanges();
}
}
UpdatePurchase();
Clear();
}
else
{
MessageBox.Show("Please select the purchase");
}
}
