public void CreateSuggestedPurchaseOrder(int vendorID, int employeeID)
{
using (var context = new eBikeContext())
{
//creating the newPurchaseOrder that will be added to PurchaseOrders
PurchaseOrder newPurchaseOrder = context.PurchaseOrders.Add(new PurchaseOrder());
//setting up information in the new purchase order
newPurchaseOrder.Closed = false;
newPurchaseOrder.EmployeeID = employeeID;
newPurchaseOrder.VendorID = vendorID;
//grabbing the inventory items for the vendor selected and selecting the parts that are good to order
var inventoryItems = from inventory in context.Parts
where inventory.VendorID == vendorID && (inventory.QuantityOnHand + inventory.QuantityOnOrder) - inventory.ReorderLevel < 0
select inventory;
foreach (var item in inventoryItems.ToList())
{
//create a new PODetail to add to the new PO
var newDetail = new PurchaseOrderDetail
{
//Take information from the inventoryItems and add it to the new PODetail
PartID = item.PartID,
Quantity = item.ReorderLevel - (item.QuantityOnHand + item.QuantityOnOrder),
PurchasePrice = item.PurchasePrice
};
//add the newDetail to the new purchase order
newPurchaseOrder.PurchaseOrderDetails.Add(newDetail);
}
//Get the subtotal and tax amount
newPurchaseOrder.SubTotal = newPurchaseOrder.PurchaseOrderDetails.Sum(x => x.PurchasePrice * x.Quantity);
newPurchaseOrder.TaxAmount = (0.05m * newPurchaseOrder.SubTotal);
//commit the transaction
context.SaveChanges();
}
}
public void UpdateCartItem(int cartItemId, int quantity)
{
using (var context = new eBikeContext())
{
var existingCartItem = context.ShoppingCartItems.Find(cartItemId);
var part = context.Parts.Find(existingCartItem.PartID);
//customers can add as many items as they want
//if(part.QuantityOnHand >= quantity)
//{
existingCartItem.Quantity = quantity;
//}
//else
//{
//throw new Exception("We don't have enough quantity on hand");
//}
var existingShoppingCart = context.ShoppingCarts.Find(existingCartItem.ShoppingCartID);
existingShoppingCart.UpdatedOn = DateTime.Today;
context.SaveChanges();
}
}
}//Update_CartItem
public void Remove_CartItem(string username, int partid)
{
using (var context = new eBikeContext())
{
int customerid = (from x in context.OnlineCustomers
where x.UserName.Equals(username)
select x.OnlineCustomerID).FirstOrDefault();
int shoppingcartid = (from x in context.ShoppingCarts
where x.OnlineCustomerID.Equals(customerid)
select x.ShoppingCartID).FirstOrDefault();
int shoppingcartitemid = (from x in context.ShoppingCartItems
where x.ShoppingCartID.Equals(shoppingcartid) && x.PartID.Equals(partid)
select x.ShoppingCartItemID).FirstOrDefault();
var existingItem = context.ShoppingCartItems.Find(shoppingcartitemid);
context.ShoppingCartItems.Remove(existingItem);
context.SaveChanges();
}
}//Remove_CartItem
public void ForceCloser_Update(PurchaseOrder closePOData, List <PurchaseOrderDetailsPOCO> closingPODetailsData)
{
using (var context = new eBikeContext())
{
var result = context.PurchaseOrders.SingleOrDefault(po => po.PurchaseOrderID == closePOData.PurchaseOrderID);
if (result != null)
{
result.Notes = closePOData.Notes;
result.Closed = closePOData.Closed;
//get data from listing for future update
foreach (PurchaseOrderDetailsPOCO item in closingPODetailsData)
{
var checkPartExists = (from p in context.Parts
where p.PartID == item.PartID
select p).SingleOrDefault();
if (checkPartExists != null)
{ //make sure that qnt on order is not negative number
if (checkPartExists.QuantityOnOrder >= item.QuantityOutstanding)
{
checkPartExists.QuantityOnHand -= item.QuantityOutstanding;
}
else
{
throw new Exception("The quantity on order is less than outstanding quantity.");
}
}
else
{
throw new Exception("Sorry there is no such part number in database.");
}
}
context.SaveChanges();
}
}
}
public void Update_PurchaseOrder(PurchaseOrder purchaseorder, List <PurchaseOrderDetail> purchaseorderdetails)
{
using (var context = new eBikeContext())
{
var activeorder = (from x in context.PurchaseOrderDetails
where x.PurchaseOrder.VendorID == purchaseorder.VendorID && x.PurchaseOrder.PurchaseOrderNumber == null && x.PurchaseOrder.OrderDate == null
select x.PurchaseOrderID).FirstOrDefault();
List <PurchaseOrderPartsPOCO> existing = (from x in context.PurchaseOrderDetails
where x.PurchaseOrderID == activeorder
select new PurchaseOrderPartsPOCO
{
PurchaseOrderDetailID = x.PurchaseOrderDetailID,
PartID = x.PartID,
Description = x.Part.Description,
QuantityOnHand = x.Part.QuantityOnHand,
QuantityOnOrder = x.Part.ReorderLevel,
ReorderLevel = x.Part.QuantityOnOrder,
Quantity = x.Quantity,
PurchasePrice = x.PurchasePrice
}).ToList();
var insertdata = purchaseorderdetails.Where(x => !existing.Any(y => y.PartID == x.PartID));
var updatedata = purchaseorderdetails.Where(x => existing.Any(y => y.PartID == x.PartID));
PurchaseOrderDetail detail = new PurchaseOrderDetail();
foreach (var insert in insertdata)
{
detail = new PurchaseOrderDetail();
detail.PartID = insert.PartID;
detail.PurchasePrice = insert.PurchasePrice;
detail.Quantity = insert.Quantity;
detail.PurchaseOrderID = activeorder;
if (detail.Quantity < 1)
{
throw new Exception("You must have at least 1 quantity for each item in the current purchase order.");
}
context.PurchaseOrderDetails.Add(detail);
}
var purchaseorderdetailpartid = (from x in purchaseorderdetails
select x.PartID).ToList();
var existingpartsid = (from x in existing
select x.PartID).ToList();
// get the different part ID's between the two lists
var partIdToRemove = existingpartsid.Except(purchaseorderdetailpartid).ToList();
// convert the partID to the purchaseorderdetailID for that row on the entity
var removedata = (from x in context.PurchaseOrderDetails
where x.PurchaseOrderID == activeorder && partIdToRemove.Contains(x.PartID)
select x.PurchaseOrderDetailID).ToList();
foreach (var remove in removedata)
{
detail = new PurchaseOrderDetail();
detail.PurchaseOrderDetailID = remove;
var existingdetailorderid = context.PurchaseOrderDetails.Find(detail.PurchaseOrderDetailID);
context.PurchaseOrderDetails.Remove(existingdetailorderid);
}
foreach (var update in updatedata)
{
int detailid = (from x in context.PurchaseOrderDetails
where x.PurchaseOrderID == activeorder && x.PartID == update.PartID
select x.PurchaseOrderDetailID).Single();
PurchaseOrderDetail updateDetails = context.PurchaseOrderDetails.Find(detailid);
updateDetails.PurchasePrice = update.PurchasePrice;
updateDetails.Quantity = update.Quantity;
if (update.Quantity < 1)
{
throw new Exception("You must have at least 1 quantity for each item in the current purchase order.");
}
var newlyadded = context.PurchaseOrderDetails.Attach(updateDetails);
var updated = context.Entry(newlyadded);
updated.State = EntityState.Modified;
}
// save the changes to database and end transaction
context.SaveChanges();
}
}
public void AddToCart(string userName, int productId, int quantity)
{
//create a new shopping cart with item
using (var context = new eBikeContext())
{
var shoppingCartId = GetShoppingCartId(userName);
var customerId = GetCustomerId(userName);
var qOH = GetQOH(productId);
//0 means this customer don't have shoppingCart,
//-1 means this userName is not a online customer
//this user is not onlineCustomer
if (customerId == 0)
{
//create a new onlineCustomer
var newOnlineCustomer = new OnlineCustomer();
newOnlineCustomer.UserName = userName;
newOnlineCustomer.CreatedOn = DateTime.Today;
context.OnlineCustomers.Add(newOnlineCustomer);
var newShoppingCart = context.ShoppingCarts.Add(new ShoppingCart());
newShoppingCart.OnlineCustomerID = GetCustomerId(userName);
newShoppingCart.CreatedOn = DateTime.Today;
newShoppingCart.UpdatedOn = DateTime.Today;
//customer can add as many items as they want
//if (qOH >= quantity)
//{
var newCartItem = new ShoppingCartItem();
newCartItem.PartID = productId;
newCartItem.Quantity = quantity;
newShoppingCart.ShoppingCartItems.Add(newCartItem);
//}
//else
//{
//throw new Exception("We don't have enough quantity on hand");
//}
}
else
{
//this online customer doesn't have shopping cart
if (shoppingCartId == 0)
{
var newShoppingCart = context.ShoppingCarts.Add(new ShoppingCart());
newShoppingCart.OnlineCustomerID = GetCustomerId(userName);
newShoppingCart.CreatedOn = DateTime.Now;
newShoppingCart.UpdatedOn = DateTime.Today;
//customer can add as many items as they want
//if (qOH >= quantity) {
var newCartItem = new ShoppingCartItem();
newCartItem.PartID = productId;
newCartItem.Quantity = quantity;
newShoppingCart.ShoppingCartItems.Add(newCartItem);
//}
//else
//{
//throw new Exception("We don't have enough quantity on hand");
//}
}
else if (shoppingCartId != 0 && shoppingCartId != -1)//this online customer have a shopping cart
{
var existingShoppingCart = context.ShoppingCarts.Find(GetShoppingCartId(userName));
existingShoppingCart.UpdatedOn = DateTime.Today;
var cartItemId = GetCartItemId(shoppingCartId, productId);
if (cartItemId == 0)//in the shopping cart, customer doesn't have this part
{
//customer can add as many items as they want
//if (qOH >= quantity)
//{
//create new cartItem
var newCartItem = new ShoppingCartItem();
newCartItem.PartID = productId;
newCartItem.Quantity = quantity;
existingShoppingCart.ShoppingCartItems.Add(newCartItem);
//}
//else
//{
//throw new Exception("We don't have enough quantity on hand");
//}
}
else//customer have this part
{
var exsitingCartItem = context.ShoppingCartItems.Find(cartItemId);
//customer can add as many items as they want
//if(qOH >= exsitingCartItem.Quantity + quantity)
//{
exsitingCartItem.Quantity = exsitingCartItem.Quantity + quantity;
//}
//else
//{
//throw new Exception("We don't have enough quantity on hand");
//}
}
}
}
context.SaveChanges();
}
}
public void PlaceOrder(string name, string paymentType, int couponId)
{
//coupon == 0 means didn't select a coupon
var shoppingCartId = GetShoppingCartId(name);
var cartItemIds = GetCartItemIdsByShoppingCartId(shoppingCartId);
using (var context = new eBikeContext())
{
var coupon = context.Coupons.Find(couponId);
var saleDetailPoco = (from cartItem in context.ShoppingCartItems
where cartItem.ShoppingCartID == shoppingCartId
select new SaleDetailPoco
{
PartID = cartItem.PartID,
Quantity = cartItem.Quantity,
SellingPrice = cartItem.Part.SellingPrice,
//ShippedDate = cartItem.Part.QuantityOnHand >= cartItem.Quantity?DateTime.Today:null,
Backordered = cartItem.Part.QuantityOnHand >= cartItem.Quantity?false:true
}).ToList();
var subTotal = (from shoppingCart in context.ShoppingCarts
where shoppingCart.ShoppingCartID == shoppingCartId
select shoppingCart.ShoppingCartItems.Sum(x => (x.Part.QuantityOnHand >= x.Quantity)?x.Quantity * x.Part.SellingPrice:0)).Single();
var newSale = context.Sales.Add(new Sale());
newSale.SaleDate = DateTime.Today;
newSale.UserName = name;
newSale.EmployeeID = 1;
newSale.SubTotal = subTotal;
newSale.TaxAmount = subTotal * (decimal)0.05;
newSale.PaymentToken = Guid.NewGuid();
if (coupon != null)
{
newSale.CouponID = coupon.CouponID;
}
newSale.PaymentType = paymentType;
foreach (var saleDetail in saleDetailPoco)
{
var newSaleDetail = new SaleDetail();
newSaleDetail.PartID = saleDetail.PartID;
newSaleDetail.Quantity = saleDetail.Quantity;
newSaleDetail.SellingPrice = saleDetail.SellingPrice;
newSaleDetail.Backordered = saleDetail.Backordered;
//if it is not out of stock, assign the day to today. Otherwise is null
if (!saleDetail.Backordered)
{
newSaleDetail.ShippedDate = DateTime.Today;
}
newSale.SaleDetails.Add(newSaleDetail);
}
//delete shoppingCart and shoppingCartItem
foreach (var cartItemId in cartItemIds)
{
var existingCartItem = context.ShoppingCartItems.Find(cartItemId);
//update part table's QuantityOnHand attrabute
var part = context.Parts.Find(existingCartItem.PartID);
if (part.QuantityOnHand >= existingCartItem.Quantity)
{
part.QuantityOnHand = part.QuantityOnHand - existingCartItem.Quantity;
}
//remove existing Cart Item record
context.ShoppingCartItems.Remove(existingCartItem);
}
//remove existing ShoppingCart
var existingShoppingCart = context.ShoppingCarts.Find(shoppingCartId);
context.ShoppingCarts.Remove(existingShoppingCart);
context.SaveChanges();
}
}
}//Check_ForBackorders
public List <Part> Place_Order(string username, int couponid, FinalTotalPOCO totals, string paymethod)
{
using (var context = new eBikeContext())
{
//Get the customers id
int customerid = (from x in context.OnlineCustomers
where x.UserName.Equals(username)
select x.OnlineCustomerID).FirstOrDefault();
if (customerid == 0)
{
throw new Exception("You do not have a shopping cart, please add items to place an order");
}
else
{
//Create and populate a new Sale
Sale newsale = new Sale();
newsale.SaleDate = DateTime.Now;
newsale.UserName = username;
newsale.EmployeeID = 301;
newsale.TaxAmount = totals.GST;
newsale.SubTotal = totals.SubTotal;
//Check if a coupon has been selected (FK constraint)
if (couponid == 0)
{
newsale.CouponID = null;
}
else
{
newsale.CouponID = couponid;
}
newsale.PaymentType = paymethod;
newsale.PaymentToken = Guid.NewGuid();
//Add the new Sale to database
newsale = context.Sales.Add(newsale);
//Get the customers shopping cart id
int shoppingcartid = (from x in context.ShoppingCarts
where x.OnlineCustomerID.Equals(customerid)
select x.ShoppingCartID).FirstOrDefault();
//Get a list of all items in the customers shopping cart
List <ShoppingCartItem> useritems = (from x in context.ShoppingCartItems
where x.ShoppingCart.ShoppingCartID.Equals(shoppingcartid)
select x).ToList();
//Create a list of parts to be filled with backordered parts
List <Part> backorderedparts = new List <Part>();
//Create a Sale Detail for each item
foreach (ShoppingCartItem item in useritems)
{
//Get the corresponding part info
Part part = (from x in context.Parts
where x.PartID.Equals(item.PartID)
select x).FirstOrDefault();
//Check the remaining quantity of the part
if (item.Quantity > part.QuantityOnHand)
{
//Create a backordered sale detail
SaleDetail boitem = new SaleDetail();
boitem.SaleID = newsale.SaleID;
boitem.PartID = part.PartID;
boitem.Quantity = item.Quantity;
boitem.SellingPrice = part.SellingPrice;
boitem.Backordered = true;
boitem.ShippedDate = null;
//Add the backordered part to return list
backorderedparts.Add(part);
//Add the backordered sale detail to database
context.SaleDetails.Add(boitem);
}
else
{
//Create a regular sale detail
SaleDetail saleitem = new SaleDetail();
saleitem.SaleID = newsale.SaleID;
saleitem.PartID = part.PartID;
saleitem.Quantity = item.Quantity;
saleitem.SellingPrice = part.SellingPrice;
saleitem.Backordered = false;
saleitem.ShippedDate = DateTime.Now;
//Update QuantityOnHand for the part
part.QuantityOnHand = part.QuantityOnHand - item.Quantity;
//Make change to QOH in the database
context.Entry(part).Property(y => y.QuantityOnHand).IsModified = true;
//Add the new sale detail
context.SaleDetails.Add(saleitem);
}
//Delete the ShoppingCartItem from users ShoppingCart
context.ShoppingCartItems.Remove(item);
}//foreach item in useritems
//Find and delete the users ShoppingCart
var existingItem = context.ShoppingCarts.Find(shoppingcartid);
context.ShoppingCarts.Remove(existingItem);
//Save changes
context.SaveChanges();
//Return any backordered parts for display to user
return(backorderedparts);
}
}
}//Place_Order
public void Add_ReceivedOrders(List <NewReceiveOrderPOCO> receiveNewOrders)
{
using (var context = new eBikeContext())
{
int id = 0; //this int will hold future PK for update;
ReceiveOrder receivedOrdersData = new ReceiveOrder();
ReceiveOrderDetail receivedOrdersDetailsData = null;
ReturnedOrderDetail returnOrdersDetailsData = null;
receivedOrdersData.PurchaseOrderID = receiveNewOrders[0].PurchaseOrderID;
receivedOrdersData.ReceiveDate = DateTime.Now;
receivedOrdersData = context.ReceiveOrders.Add(receivedOrdersData);
id = receivedOrdersData.ReceiveOrderDetails.Count() + 1;
foreach (NewReceiveOrderPOCO item in receiveNewOrders)
{
if (item.QuantityReceived != 0)
{ //Part table
if (item.QuantityReceived <= item.Outstanding)
{
receivedOrdersDetailsData = new ReceiveOrderDetail();
receivedOrdersDetailsData.PurchaseOrderDetailID = item.PurchaseOrderDetailID;
receivedOrdersDetailsData.ReceiveOrderID = id;
receivedOrdersDetailsData.QuantityReceived = item.QuantityReceived;
receivedOrdersData.ReceiveOrderDetails.Add(receivedOrdersDetailsData);
var checkPartExists = (from p in context.Parts
where p.PartID == item.PartID
select p).SingleOrDefault();
if (checkPartExists != null)
{
if (checkPartExists.QuantityOnOrder >= item.Outstanding)
{
checkPartExists.QuantityOnHand += item.QuantityReceived;
checkPartExists.QuantityOnOrder -= item.QuantityReceived;
}
else
{
throw new Exception("The quantity on order is less than outstanding quantity.");
}
}
else
{
throw new Exception("Sorry there is no such part number in database.");
}
}
else
{
throw new Exception("The received quantity can't be more than outstanding.");
}
}
//ReturnOrderDetails Table
if (!string.IsNullOrEmpty(item.QuantityReturned.ToString()) && !string.IsNullOrEmpty(item.Notes))
{
returnOrdersDetailsData = new ReturnedOrderDetail();
returnOrdersDetailsData.ReceiveOrderID = id;
returnOrdersDetailsData.PurchaseOrderDetailID = item.PurchaseOrderDetailID;
returnOrdersDetailsData.ItemDescription = item.PartDescription;
returnOrdersDetailsData.Quantity = item.QuantityReturned;
returnOrdersDetailsData.Reason = item.Notes;
returnOrdersDetailsData.VendorPartNumber = item.PartID.ToString();
receivedOrdersData.ReturnedOrderDetails.Add(returnOrdersDetailsData);
}
}
int outstanding = receiveNewOrders.Sum(item => item.Outstanding);
int received = receiveNewOrders.Sum(item => item.QuantityReceived);
if ((outstanding - received) == 0)
{
PurchaseOrder poData = context.PurchaseOrders.Find(receiveNewOrders[0].PurchaseOrderID);
if (poData != null)
{
poData.Closed = true;
}
}
context.SaveChanges();
}
}