public void updateRental(int rentalID, string creditcard, int couponID)
{
using (var context = new eToolsContext())
{
Rental rental = context.Rentals.Where(x => x.RentalID.Equals(rentalID)).Select(x => x).FirstOrDefault();
//RentalDetail exists = context.RentalDetails.Where(x => x.RentalEquipmentID.Equals(equipmentID) && x.RentalID.Equals(rentalID)).Select(x => x).FirstOrDefault();
if (rental != null)//equipmnet already in there
{
throw new BusinessRuleException("Rental Does not exist", "Rental does not exist, please create a rental before submiting it");
}
else
{
Rental update = new Rental();
update.RentalID = rentalID;
update.CreditCard = creditcard;
//update.CustomerID = customerID;
//update.EmployeeID = employeeID;
update.CouponID = couponID;
context.Entry(update).Property(y => y.CreditCard).IsModified = true;
context.Entry(update).Property(y => y.CouponID).IsModified = true;
context.SaveChanges();
}
}
}
public void Update_ShoppingCartFromDisplay(List <CartSelection> cart)
{
using (var context = new eToolsContext())
{
bool cartUpdated = false;
int shoppingcartID = 0;
foreach (CartSelection cartitem in cart)
{
ShoppingCartItem item = context.ShoppingCartItems.Find(cartitem.ShoppingCartitemID);
//check to see if cart has actually been updated, if so, update ShoppingCart UpdatedOn
if (item.Quantity != cartitem.QuantitySelected)
{
item.Quantity = cartitem.QuantitySelected;
cartUpdated = true;
shoppingcartID = item.ShoppingCartID;
}
context.Entry(item).State = System.Data.Entity.EntityState.Modified;
}
if (cartUpdated)
{
//set updated property in associated shopping cart
ShoppingCart shoppingCart = context.ShoppingCarts.Find(shoppingcartID);
shoppingCart.UpdatedOn = DateTime.Now;
context.Entry(shoppingCart).State = System.Data.Entity.EntityState.Modified;
}
context.SaveChanges();
}
}
public void removeRentalEquipment(int rentalId, int equitmentid)
{
using (var context = new eToolsContext())
{
//Return equipment
RentalEquipment checkoutEquipment = context.RentalEquipments
.Where(x => (x.RentalEquipmentID == equitmentid)).FirstOrDefault();
//Update availability
checkoutEquipment.Available = true;
//COMMIT UPDATE***
context.Entry(checkoutEquipment).State = EntityState.Modified;
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//retrive rentaldetails table that will destoryed
RentalDetail existingRentalEquipment = context.RentalDetails
.Where(x => ((x.RentalID == rentalId) &&
(x.RentalEquipmentID == equitmentid))).FirstOrDefault();
//COMMIT DELETE***
context.Entry(existingRentalEquipment).State = EntityState.Deleted;
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//Finalize Transaction!
context.SaveChanges();
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////
}
}
public void deleteRental(int rentalid)
{
using (var context = new eToolsContext())
{
//.FirstOrDefault() is added in Visual Studio not in LINQ, as it does NOT AUTO. CONVERT!!
Rental rental = context.Rentals.Where(x => (x.RentalID == rentalid)).FirstOrDefault();
//A return of 1 means an EXACT MATCH
//either there is a value or not; 1 or 0
if (rental == null)
{
throw new BusinessRuleException("No such rental exists!", logger);
}
else
{
//Heavy Query!
DELETE_RentalDetailsTable_UPDATE_RentalEquipmentTable removeList = context.Rentals.Where(x => (x.RentalID == rentalid))
.Select(x =>
new DELETE_RentalDetailsTable_UPDATE_RentalEquipmentTable()
{
deleteSet = x.RentalDetails.Select(y =>
new SingleEquipmentPairWithRentalDetailDelete()
{
_RentalDetailTable = y,
_RentalEquipmentTable = y.RentalEquipment
})
}).FirstOrDefault();
//The.find(...) returns an entity based on...ID
//DTO
foreach (var remove in removeList.deleteSet)
{
//I not sure if this returns or mods anything
//ii._RentalEquipmentTable.Available = true;
//better way... not this, but use above query
// ...== ii._RentalEquipmentTable.RentalEquipmentID)... id the equipment
//RentalEquipment updatedEquipment = context.RentalEquipments
// .Where(x => (x.RentalEquipmentID == ii._RentalEquipmentTable.RentalEquipmentID)).FirstOrDefault();
RentalEquipment updatedEquipment = remove._RentalEquipmentTable;
updatedEquipment.Available = true;
context.Entry(updatedEquipment).State = EntityState.Modified; //Update Equipment ....Available = true; aka returned
RentalDetail deletedRentalDetail = remove._RentalDetailTable;
context.Entry(deletedRentalDetail).State = EntityState.Deleted; //Delete RentalDetails
}
//Delete Rental
context.Entry(rental).State = EntityState.Deleted;
}
//Commit Transaction
context.SaveChanges();
}
}
public void ReceivePurchaseOrder(int orderNumber, List <ViewPurchaseOrderItems> orderDetails, bool orderCompleted)
{
using (var context = new eToolsContext())
{
PurchaseOrder purchaseOrder = context.PurchaseOrders.Find(orderNumber);
purchaseOrder.Closed = orderCompleted;
context.Entry(purchaseOrder).Property("Closed").IsModified = true;
ReceiveOrder receiveOrder = new ReceiveOrder();
receiveOrder.PurchaseOrderID = orderNumber;
receiveOrder.ReceiveDate = DateTime.Now;
context.ReceiveOrders.Add(receiveOrder);
StockItem stockItem = null;
PurchaseOrderDetail purchaseOrderDetail = null;
ReceiveOrderDetail receiveOrderDetail = null;
ReturnedOrderDetail returnedOrderDetail = null;
foreach (ViewPurchaseOrderItems item in orderDetails)
{
purchaseOrderDetail = purchaseOrder.PurchaseOrderDetails.Where(order => order.StockItemID == item.ItemID).SingleOrDefault();
if (item.Received > 0)
{
receiveOrderDetail = new ReceiveOrderDetail();
receiveOrderDetail.ReceiveOrderID = receiveOrder.ReceiveOrderID;
receiveOrderDetail.PurchaseOrderDetailID = purchaseOrderDetail.PurchaseOrderDetailID;
receiveOrderDetail.QuantityReceived = item.Received;
context.ReceiveOrderDetails.Add(receiveOrderDetail);
stockItem = context.StockItems.Find(item.ItemID);
stockItem.QuantityOnHand += item.Received;
context.Entry(stockItem).Property("QuantityOnHand").IsModified = true;
if (item.Received > stockItem.QuantityOnOrder)
{
stockItem.QuantityOnOrder = 0;
}
else
{
stockItem.QuantityOnOrder -= item.Received;
}
context.Entry(stockItem).Property("QuantityOnOrder").IsModified = true;
}
if (item.Returned > 0)
{
returnedOrderDetail = new ReturnedOrderDetail();
returnedOrderDetail.ReceiveOrderID = receiveOrder.ReceiveOrderID;
returnedOrderDetail.PurchaseOrderDetailID = purchaseOrderDetail.PurchaseOrderDetailID;
returnedOrderDetail.ItemDescription = stockItem.Description;
returnedOrderDetail.Quantity = item.Returned;
returnedOrderDetail.Reason = item.Reason;
context.ReturnedOrderDetails.Add(returnedOrderDetail);
}
}
context.SaveChanges();
}
}
/// <summary>
/// Transactional force close of an outstanding order
/// </summary>
/// <param name="_poID"></param>
/// <param name="_forceCloseReason"></param>
/// <param name="_OODs"></param>
public void OpenOrder_ForceClose(int _poID, string _forceCloseReason, List <OpenOrderDetail> _OODs)
{
using (var context = new eToolsContext())
{
// Transaction process:
// A) Update the purchase order as closed
// B) Update purchase order notes field with force close reason
// C) Update QuantityOnOrder for each stock item by subtracting outstanding quantity
// Ensure a reason was entered by the user
if (string.IsNullOrEmpty(_forceCloseReason))
{
throw new Exception("A reason is required for a force close of an order");
}
else
{
// Pull purchase order from database
var purchaseOrder = (
from x in context.PurchaseOrders
where x.PurchaseOrderID == _poID
select x)
.FirstOrDefault();
// A >>
purchaseOrder.Closed = true;
context.Entry(purchaseOrder).Property(y => y.Closed).IsModified = true;
// B >>
purchaseOrder.Notes = _forceCloseReason;
context.Entry(purchaseOrder).Property(y => y.Notes).IsModified = true;
// C >>
List <StockItem> StockItems = new List <StockItem>();
foreach (OpenOrderDetail ood in _OODs)
{
int stockItemID = ood.StockItemID;
var stockItem = (
from x in context.StockItems
where x.StockItemID == stockItemID
select x)
.FirstOrDefault();
stockItem.QuantityOnOrder -= ood.QtyOutstanding;
// Update qty on order
context.Entry(stockItem).Property(y => y.QuantityOnOrder).IsModified = true;
}
// Commit changes
context.SaveChanges();
}
}
}
public void addRentalEquipment(int rentalId, int equitmentid)
{
using (var context = new eToolsContext())
{
//Checkout equipment
RentalEquipment checkoutEquipment = context.RentalEquipments
.Where(x => (x.RentalEquipmentID == equitmentid)).FirstOrDefault();
//Update
checkoutEquipment.Available = false;
//COMMIT UPDATE***
context.Entry(checkoutEquipment).State = EntityState.Modified;
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//create Rental Details
RentalDetail newRentalEquipment = new RentalDetail();
//Recived values
newRentalEquipment.RentalID = rentalId;
newRentalEquipment.RentalEquipmentID = equitmentid;
//unmutatble Values
newRentalEquipment.DailyRate = context.RentalEquipments
.Where(x => (x.RentalEquipmentID == equitmentid))
.Select(x => x.DailyRate).FirstOrDefault();
newRentalEquipment.ConditionOut = context.RentalEquipments
.Where(x => (x.RentalEquipmentID == equitmentid))
.Select(x => x.Condition).FirstOrDefault();
//Pending return
//Set to default values
newRentalEquipment.Days = 1; //bussiness rule!
newRentalEquipment.ConditionIn = "Has not been returned";
newRentalEquipment.DamageRepairCost = 0.0m;
newRentalEquipment.Comments = "";
//COMMIT ADD***
context.Entry(newRentalEquipment).State = EntityState.Added;
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//Finalize Transaction!
context.SaveChanges();
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////
}
}
public void PlaceOrder(Sale sale, ShoppingCart cart)
{
using (var context = new eToolsContext())
{
context.Sales.Add(sale);
List <ShoppingCartItem> cartItems = context.ShoppingCartItems.Where(x => x.ShoppingCartID.Equals(cart.ShoppingCartID)).Select(x => x).ToList();
foreach (ShoppingCartItem cartItem in cartItems)
{
StockItem stockItem = context.StockItems.Where(x => x.StockItemID.Equals(cartItem.StockItemID)).FirstOrDefault();
SaleDetail saleDetail = new SaleDetail();
saleDetail.StockItemID = cartItem.StockItemID;
saleDetail.SellingPrice = stockItem.SellingPrice;
saleDetail.Quantity = cartItem.Quantity;
if (saleDetail.Quantity > (stockItem.QuantityOnHand + stockItem.QuantityOnOrder))
{
throw new Exception("Sale cannot proceed. " + stockItem.Description + " is out of stock.");
}
saleDetail.Backordered = saleDetail.Quantity > stockItem.QuantityOnHand ? true : false;
sale.SaleDetails.Add(saleDetail);
stockItem.QuantityOnHand = saleDetail.Quantity > stockItem.QuantityOnHand ? 0 : stockItem.QuantityOnHand - saleDetail.Quantity;
context.Entry(stockItem).State = System.Data.Entity.EntityState.Modified;
context.ShoppingCartItems.Remove(cartItem);
}
ShoppingCart theCart = context.ShoppingCarts.Where(x => x.ShoppingCartID.Equals(cart.ShoppingCartID)).FirstOrDefault();
context.ShoppingCarts.Remove(theCart);
context.SaveChanges();
}
}
public int Return_Update(ReturnForm rtn)
{
using (var context = new eToolsContext())
{
//RentalDetail item = context.RentalDetails.Where(x => (x.RentalDetailID == rtn.ID)).FirstOrDefault();
//RentalDetail item2 = new RentalDetail();
//item2 = item;
//item2.Comments = rtn.CustomerCommets == null ? "" : rtn.CustomerCommets; //can be null
//item2.ConditionIn = rtn.CoditionCommets == null ? "" : rtn.CoditionCommets;
RentalDetail rdptr = context.RentalDetails.Where(x => (x.RentalDetailID == rtn.ID)).FirstOrDefault();
RentalDetail item = new RentalDetail();
if (rdptr == null)
{
throw new BusinessRuleException("No such rental exists!", logger);
}
else
{
item.RentalDetailID = rtn.ID;
item.RentalID = rdptr.RentalID;
item.RentalEquipmentID = rdptr.RentalEquipmentID;
item.Days = rdptr.Days;
item.DailyRate = rdptr.DailyRate;
item.ConditionOut = rdptr.ConditionOut;
item.ConditionIn = string.IsNullOrEmpty(rtn.CoditionCommets) ? "" : rtn.CoditionCommets;
item.DamageRepairCost = rdptr.DamageRepairCost;
item.Comments = string.IsNullOrEmpty(rtn.CustomerCommets) ? "" : rtn.CustomerCommets;
}
context.Entry(item).State = System.Data.Entity.EntityState.Modified;
return(context.SaveChanges());
}
}
public string UpdateDBPOPODetails(int vendorID, List <CurrentActiveOrderView> currentOrderListPOCOs)
{
using (var context = new eToolsContext())
{
decimal subtotal = 0;
//decimal subtotal = 0;
decimal tax = 0;
//1. check existing order
var existOrder = (from x in context.PurchaseOrders
where x.VendorID == vendorID
where x.PurchaseOrderNumber == null && x.OrderDate == null
select x).FirstOrDefault();
if (existOrder == null)
{
throw new Exception("error, order does not exist");
}
else
{
//change list: poco.sid == detail.sid
List <PurchaseOrderDetail> changeList = existOrder.PurchaseOrderDetails.ToList();
foreach (var item in changeList)
{
var pocoItem = currentOrderListPOCOs.Where(poco => poco.SID == item.StockItemID).FirstOrDefault();
if (pocoItem != null)
{
item.PurchasePrice = pocoItem.Price;
item.Quantity = pocoItem.QTO;
subtotal += item.PurchasePrice * item.Quantity;
tax += (item.PurchasePrice * item.Quantity) * (decimal)0.05;
}
context.Entry(item).State = System.Data.Entity.EntityState.Modified;
}
existOrder.SubTotal = subtotal;
existOrder.TaxAmount = tax;
context.Entry(existOrder).State = System.Data.Entity.EntityState.Modified;
}//endofelse
string msg = "Order " + existOrder.PurchaseOrderID.ToString() + "with " + existOrder.PurchaseOrderDetails.Count + " items was updated at " + DateTime.Now.ToString();
context.SaveChanges();
return(msg);
} //eou
} //eom
public void Update_ShoppingCartItem(ShoppingCartItem item)
{
using (var context = new eToolsContext())
{
context.Entry(item).State = System.Data.Entity.EntityState.Modified;
context.SaveChanges();
}
}
}// Create ReturnedOrderDetails.
#endregion
#region Update PurchaseOrder
public int Update_PurchaseOrder(PurchaseOrder item)
{
using (var context = new eToolsContext())
{
context.Entry(item).State = System.Data.Entity.EntityState.Modified;
return(context.SaveChanges());
}
}// Create ReturnedOrderDetails.
public int newReturn_Update(RentalEquipment item)
{
using (var context = new eToolsContext())
{
context.Entry(item).State = System.Data.Entity.EntityState.Modified;
return(context.SaveChanges());
}
}
public void ForceClosePurchaseOrder(int purchaseOrderID, string reasonClosed)
{
using (var context = new eToolsContext())
{
PurchaseOrder pOrder = context.PurchaseOrders.Find(purchaseOrderID);
pOrder.Closed = true;
pOrder.Notes = reasonClosed;
context.Entry(pOrder).State = System.Data.Entity.EntityState.Modified;
context.SaveChanges();
}
}
public void deleteRental(int rentalid)
{
using (var context = new eToolsContext())
{
//.FirstOrDefault() is added in Visual Studio not in LINQ, as it does NOT AUTO. CONVERT!!
Rental rental = context.Rentals.Where(x => (x.RentalID == rentalid)).FirstOrDefault();
//A return of 1 means an EXACT MATCH
//either there is a value or not; 1 or 0
if (rental == null)
{
throw new BusinessRuleException("No such rental exists!", logger);
}
else
{
var removeList = context.RentalDetails
.Where(x => (x.RentalID == rentalid))
.Select(x =>
new
{
RentalDetailTable = x,
RentalEquipmentTable = x.RentalEquipment
}
);
foreach (var remove in removeList)
{
//Free equipmwnt
remove.RentalEquipmentTable.Available = true;
context.Entry(remove.RentalEquipmentTable).State = EntityState.Modified;
//Delete rental details/ equipment's parent
context.Entry(remove.RentalDetailTable).State = EntityState.Deleted;
}
//Delete Rental/ details parent
context.Entry(rental).State = EntityState.Deleted;
}
//Commit Transaction
context.SaveChanges();
}
}
public void ForceClosePurchaseOrder(int orderNumber, string reason)
{
using (var context = new eToolsContext())
{
PurchaseOrder purchaseOrder = context.PurchaseOrders.Find(orderNumber);
purchaseOrder.Notes = reason;
context.Entry(purchaseOrder).Property("Notes").IsModified = true;
purchaseOrder.Closed = true;
context.Entry(purchaseOrder).Property("Closed").IsModified = true;
StockItem stockItem = null;
foreach (PurchaseOrderDetail item in purchaseOrder.PurchaseOrderDetails)
{
stockItem = context.StockItems.Find(item.StockItemID);
//stockItem.QuantityOnOrder -= item.Quantity - item.ReceiveOrderDetails.Select(receive => receive.QuantityReceived).DefaultIfEmpty(0).Sum();
stockItem.QuantityOnOrder = 0;
context.Entry(stockItem).Property("QuantityOnOrder").IsModified = true;
}
context.SaveChanges();
}
}
public void UpdateOrder(PurchaseOrder item)
{
using (eToolsContext context = new eToolsContext())
{
var attached = context.PurchaseOrders.Attach(item);
var existing = context.Entry(attached);
existing.State = System.Data.Entity.EntityState.Modified;
context.SaveChanges();
}
}
public void payRental(int rentalid)
{
using (var context = new eToolsContext())
{
List <RentalDetail> pay = context.RentalDetails.Where(x => (x.RentalID == rentalid)).ToList();
foreach (var rd in pay)
{
rd.Paid = true;
context.Entry(rd).State = EntityState.Modified;
}
//Commit Transaction
context.SaveChanges();
}
}
//public List<PODetail> ItemsInStockSuggested(int vendorid)
//{
// List<PODetail> itemToOrder = SuggestedOrderDetails(vendorid);
// using (var context = new eToolsContext())
// {
// //var allItems = context.StockItems.ToList();
// List<int> poDetailItems = (from x in itemToOrder
// select x.SID).ToList();
// List<StockItem> remainStockItems = new List<StockItem>();
// foreach (var item in context.StockItems.Where(x => x.VendorID == vendorid))
// {
// if (!poDetailItems.Contains(item.StockItemID))
// {
// remainStockItems.Add(item);
// }
// }
// var resault = from x in remainStockItems
// select new PODetail
// {
// SID = x.StockItemID,
// description = x.Description,
// qoh = x.QuantityOnHand,
// qoo = x.QuantityOnOrder,
// rol = x.ReOrderLevel,
// price = x.PurchasePrice,
// qto = ((x.QuantityOnHand + x.QuantityOnOrder) - x.ReOrderLevel) >= 0 ? ((x.QuantityOnHand + x.QuantityOnOrder) - x.ReOrderLevel) : 0
// };
// return resault.ToList();
// }
//}
public void Create_PurchaseOrder(int vendorid, int employeeid)
{
PurchaseOrder newPO = new PurchaseOrder();
newPO.VendorID = vendorid;
newPO.EmployeeID = employeeid;
newPO.Closed = false;
newPO.TaxAmount = 0;
newPO.SubTotal = 0;
decimal subTotal = 0;
//List<PurchaseOrderDetail> showPODetails = new List<PurchaseOrderDetail>();
using (var context = new eToolsContext())
{
context.PurchaseOrders.Add(newPO);
context.SaveChanges();
List <StockItem> vendorStockItems = (from x in context.StockItems
where x.VendorID.Equals(vendorid) && (x.ReOrderLevel - (x.QuantityOnHand + x.QuantityOnOrder)) > 0
select x).ToList();
// generate PurchaseOrderDetail for the new PurchaseOrder
foreach (var item in vendorStockItems)
{
PurchaseOrderDetail newItem = new PurchaseOrderDetail();
newItem.PurchaseOrderID = newPO.PurchaseOrderID;
newItem.StockItemID = item.StockItemID;
newItem.PurchasePrice = item.PurchasePrice;
newItem.Quantity = item.ReOrderLevel - (item.QuantityOnHand + item.QuantityOnOrder);
subTotal += item.PurchasePrice;
//PODetail showPODetail = new PODetail();
//showPODetail.SID = item.StockItemID;
//showPODetail.description = item.Description;
//showPODetail.qoh = item.QuantityOnHand;
//showPODetail.qoo = item.QuantityOnOrder;
//showPODetail.rol = item.ReOrderLevel;
//showPODetail.QtyToOrder = newItem.Quantity;
//showPODetails.Add(newItem);
context.PurchaseOrderDetails.Add(newItem);
}
newPO.SubTotal = decimal.Round(subTotal, 2);
newPO.TaxAmount = decimal.Round(subTotal * ((decimal)0.05), 2);
context.Entry(newPO).State = System.Data.Entity.EntityState.Modified;
context.SaveChanges();
}
}
public int UpdateShoppingCartItem(int ShoppingCartItemID, int quantity)
{
using (var context = new eToolsContext())
{
ShoppingCartItem cartItem = context.ShoppingCartItems.Find(ShoppingCartItemID);
cartItem.Quantity = quantity;
var attached = context.ShoppingCartItems.Attach(cartItem);
context.Entry(attached).State = System.Data.Entity.EntityState.Modified;
return(context.SaveChanges());
}
}
} //eom
public int Place_Order(int venderID, List <CurrentActiveOrderView> currentOrderListPOCOs)
{
using (var context = new eToolsContext())
{
PurchaseOrder placeOrder = context.PurchaseOrders.Where(x => x.VendorID.Equals(venderID) && (x.PurchaseOrderNumber == null && x.OrderDate == null)).FirstOrDefault();
string updateMsg = UpdateDBPOPODetails(venderID, currentOrderListPOCOs);
placeOrder.OrderDate = DateTime.Now;
placeOrder.PurchaseOrderNumber = context.PurchaseOrders.Max(x => x.PurchaseOrderNumber) == null? 1 : context.PurchaseOrders.Max(x => x.PurchaseOrderNumber) + 1;
foreach (var item in placeOrder.PurchaseOrderDetails)
{
item.StockItem.QuantityOnOrder += item.Quantity;
context.Entry(item).State = System.Data.Entity.EntityState.Modified;
}
context.SaveChanges();
return(placeOrder.PurchaseOrderID);
}
}//eom
public void PayNow(int rentalID, string paymentType)
{
using (var context = new eToolsContext())
{
Rental rental = context.Rentals.Where(x => x.RentalID.Equals(rentalID)).Select(x => x).FirstOrDefault();
if (rental != null)//equipmnet already in there
{
throw new BusinessRuleException("Rental Does not exist", "Rental does not exist, please create a rental before submiting it");
}
else
{
Rental update = new Rental();
update.RentalID = rentalID;
update.PaymentType = paymentType;
context.Entry(update).Property(y => y.PaymentType).IsModified = true;
context.SaveChanges();
}
}
}
public void UpdateToShoppingCart(int stockitemid, int shoppingcartitemid, string username, int quantity)
{
using (var context = new eToolsContext())
{
int result = (from cart in context.ShoppingCartItems
where cart.ShoppingCart.OnlineCustomer.UserName == username
select cart.ShoppingCartID).First();
int result2 = (from cart in context.ShoppingCartItems
where cart.ShoppingCart.OnlineCustomer.UserName == username && cart.StockItemID == stockitemid
select cart.Quantity).First();
ShoppingCartItem item = new ShoppingCartItem();
item.ShoppingCartItemID = shoppingcartitemid;
item.StockItemID = stockitemid;
item.Quantity = quantity + result2;
item.ShoppingCartID = result;
context.Entry <ShoppingCartItem>(context.ShoppingCartItems.Attach(item)).State =
System.Data.Entity.EntityState.Modified;
context.SaveChanges();
}
}
/// <summary>
/// Transactional process of an outstanding order:
/// <para>>> Updates the order details</para>
/// <para>>> Updates stock item quantity fields for the receive order detail rows</para>
/// <para>>> Creates a receive order record</para>
/// <para>>> Creates receive order details and/or returned order details record(s)</para>
/// <para>>> Updates the outstanding order as closed if fully received</para>
/// </summary>
/// <param name="_poID"></param>
/// <param name="_OODs"></param>
/// <param name="_UPICs"></param>
public bool OpenOrder_Receive(int _poID, List <OpenOrderDetail> _OODs, List <int> _UPICs)
{
using (var context = new eToolsContext())
{
// Transaction process:
// A) Create a ReceiveOrder record when at least one item is received, returned, or unordered
// B) Create a new ReceiveOrderDetail record for each item that is received
// C) Increase stock item QuantityOnHand by the received quantity
// D) Decrease stock item QuantityOnOrder by the received quantity
// E) Create a new ReturnedOrderDetail record for each item that is returned
// F) Check if the PurchaseOrder can be closed
// G) Create a new ReturnedOrderDetail record for each item that is unordered
// H) Save all changes
// All the records that can be created or updated in the transaction processs
List <ReceiveOrderDetail> ReceiveOrderDetails = new List <ReceiveOrderDetail>();
List <ReturnedOrderDetail> ReturnedOrderDetails = new List <ReturnedOrderDetail>();
List <StockItem> StockItems = new List <StockItem>();
PurchaseOrder purchaseOrder = null;
// A >>
ReceiveOrder receiveOrder = new ReceiveOrder(); // Note: ReceiveOrderID is an identity created when ReceiveOrder inserted
receiveOrder.PurchaseOrderID = _poID;
receiveOrder.ReceiveDate = DateTime.Now; // Nullable
// Continue if order details exist
if (_OODs.Count > 0)
{
bool isOpen = false; // Reverse logic check where order has to be proven to be open (upon iteration through each order detail row)
// Process each open order detail
foreach (OpenOrderDetail ood in _OODs)
{
StockItem stockItem = null;
int qtyOutstanding = ood.QtyOutstanding;
int qtyReceived = (int)ood.QtyReceived;
// Check if item was received
if (ood.QtyReceived != null && ood.QtyReceived > 0)
{
// Ensure received qty is not greather than the outstanding qty
if (qtyReceived > qtyOutstanding)
{
throw new Exception("Each received qty must be less than the outstanding qty");
}
else
{
// Monitor order state
if (qtyReceived < qtyOutstanding)
{
isOpen = true;
}
// B >>
ReceiveOrderDetail receiveOrderDetail = new ReceiveOrderDetail(); // Note: ReceiveOrderID is an identity created when ReceiveOrder inserted
receiveOrderDetail.PurchaseOrderDetailID = ood.PODetailID;
receiveOrderDetail.QuantityReceived = qtyReceived;
// Pull stock item from database
int stockItemID = ood.StockItemID;
stockItem = (
from x in context.StockItems
where x.StockItemID == stockItemID
select x)
.FirstOrDefault();
// C >>
stockItem.QuantityOnHand += qtyReceived;
// D >>
stockItem.QuantityOnOrder -= qtyReceived;
// Hold created and updated records
ReceiveOrderDetails.Add(receiveOrderDetail);
StockItems.Add(stockItem);
}
}
else // Item not received
{
// Monitor order state
if (qtyOutstanding > 0)
{
isOpen = true;
}
}
// Check if item was returned
if (ood.QtyReturned != null && ood.QtyReturned > 0)
{
// Ensure a reason was entered by the user
if (string.IsNullOrEmpty(ood.ReturnReason))
{
throw new Exception("Returned quantities require a reason entry");
}
else
{
// Pull stock item from database (if it has not yet been retrieved)
if (stockItem == null)
{
int stockItemID = ood.StockItemID;
stockItem = (
from x in context.StockItems
where x.StockItemID == stockItemID
select x)
.FirstOrDefault();
}
// E >>
ReturnedOrderDetail returnedOrderDetail = new ReturnedOrderDetail(); // Note: ReceiveOrderID is an identity created when ReceiveOrder inserted
returnedOrderDetail.PurchaseOrderDetailID = ood.PODetailID; // Nullable
returnedOrderDetail.ItemDescription = stockItem.Description; // Nullable
returnedOrderDetail.Quantity = (int)ood.QtyReturned;
returnedOrderDetail.Reason =
string.IsNullOrEmpty(ood.ReturnReason)
? null
: ood.ReturnReason;
returnedOrderDetail.VendorStockNumber = null; // Nullable
// Hold created record
ReturnedOrderDetails.Add(returnedOrderDetail);
}
}
}
// F >>
if (isOpen == false)
{
// Pull purchase order from database
purchaseOrder = (
from x in context.PurchaseOrders
where x.PurchaseOrderID == _poID
select x)
.FirstOrDefault();
// Close order
purchaseOrder.Closed = true;
}
}
// Process each unordered purchase item cart (Note: This can happen even if there are no order details for the order)
if (_UPICs.Count > 0)
{
foreach (int cart in _UPICs)
{
// Pull upic from database
int cartID = cart;
var upic = (
from x in context.UnorderedPurchaseItemCart
where x.CartID == cartID
select x)
.FirstOrDefault();
// G >>
ReturnedOrderDetail returnedOrderDetail = new ReturnedOrderDetail(); // Note: ReceiveOrderID is an identity created when ReceiveOrder inserted
returnedOrderDetail.PurchaseOrderDetailID = null; // Nullable
returnedOrderDetail.ItemDescription = upic.Description; // Nullable
returnedOrderDetail.Quantity = upic.Quantity;
returnedOrderDetail.Reason = null; // Nullable
returnedOrderDetail.VendorStockNumber =
string.IsNullOrEmpty(upic.VendorStockNumber)
? null
: upic.VendorStockNumber;
// Hold created record
ReturnedOrderDetails.Add(returnedOrderDetail);
}
}
// H >>
context.ReceiveOrders.Add(receiveOrder); // Creates identity ID used by detail records
foreach (ReceiveOrderDetail rod in ReceiveOrderDetails)
{
// Set detail records receive order ID
rod.ReceiveOrderID = receiveOrder.ReceiveOrderID;
// Stage detail record
context.ReceiveOrderDetails.Add(rod);
}
foreach (ReturnedOrderDetail rod in ReturnedOrderDetails)
{
// Set detail record receive order ID
rod.ReceiveOrderID = receiveOrder.ReceiveOrderID;
// Stage detail record
context.ReturnedOrderDetails.Add(rod);
}
foreach (StockItem si in StockItems)
{
context.Entry(si).Property(y => y.QuantityOnHand).IsModified = true;
context.Entry(si).Property(y => y.QuantityOnOrder).IsModified = true;
}
bool closed = false;
if (purchaseOrder != null) // Purchase order can be closed
{
context.Entry(purchaseOrder).Property(y => y.Closed).IsModified = true;
closed = true;
}
// Commit transaction
context.SaveChanges();
// Return order state
return(closed);
}
}
public SalesReceipt PlaceOrder(int employeeId, string couponCode, string paymentMethod)
{
using (var context = new eToolsContext())
{
Employee employee = context.Employees.Find(employeeId);
CartSummaryInfo cartSummary = ShoppingCart_GetSummary(employeeId, couponCode);
List <CartItemInfo> cartItems = ShoppingCart_GetItems(employeeId);
Dictionary <string, string> validPaymentMethods = new Dictionary <string, string>
{
{ "M", "Money" },
{ "C", "Credit" },
{ "D", "Debit" }
};
List <string> errors = new List <string>();
if (employee == null)
{
errors.Add("Employee not found.");
}
else if (cartSummary == null)
{
errors.Add("Employee has no shopping cart.");
}
else if (cartItems == null || cartItems.Count == 0)
{
errors.Add("Shopping cart is empty.");
}
else if (!string.IsNullOrWhiteSpace(couponCode) && cartSummary.DiscountCoupon == null)
{
errors.Add("Coupon code invalid.");
}
else if (cartSummary.DiscountCoupon != null && !cartSummary.DiscountCoupon.IsValid)
{
errors.Add("Coupon expired.");
}
if (string.IsNullOrWhiteSpace(paymentMethod))
{
errors.Add("Payment method must not be empty.");
}
else if (!validPaymentMethods.ContainsKey(paymentMethod))
{
errors.Add("Payment method invalid.");
}
// validate each item in the cart
foreach (var item in cartItems)
{
StockItem product = context.StockItems.Find(item.ProductID);
List <string> itemErrors = ValidateCartItem(employee, product, item.QtyInCart);
errors.AddRange(itemErrors);
}
if (errors.Count > 0)
{
throw new BusinessRuleException("Order cannot be placed.", errors);
}
else
{
// all validation passed, build sales receipt
var receipt = new SalesReceipt()
{
SaleDate = DateTime.Now,
PaymentType = paymentMethod,
PaymentTypeDesc = validPaymentMethods[paymentMethod],
EmployeeId = employeeId,
EmployeeName = employee.LastName + ", " + employee.FirstName,
SubTotal = cartSummary.Subtotal,
CouponId = cartSummary.DiscountCoupon?.CouponID,
CouponCode = couponCode,
DiscountPercent = (cartSummary.DiscountCoupon == null) ? 0 : cartSummary.DiscountCoupon.DiscountPercent,
DiscountAmount = cartSummary.Discount,
TaxAmount = cartSummary.GST,
TotalAmount = cartSummary.Total,
Items = cartItems.Select(item => new SalesReceiptItem
{
ProductId = item.ProductID,
ProductName = item.ProductDescription,
SellingPrice = item.SellingPrice,
Qty = item.QtyInCart,
SubTotal = item.TotalPrice
}).ToList()
};
// store new sale
Sale newSale = context.Sales.Add(new Sale()
{
SaleDate = receipt.SaleDate,
PaymentType = receipt.PaymentType,
EmployeeID = receipt.EmployeeId,
TaxAmount = receipt.TaxAmount,
SubTotal = receipt.SubTotal,
CouponID = receipt.CouponId
});
if (newSale == null)
{
throw new Exception("Sale cannot be added to the database.");
}
// process each item: insert into the database and decrease qty on hand
foreach (var item in receipt.Items)
{
SaleDetail newSaleDetail = context.SaleDetails.Add(new SaleDetail()
{
SaleID = newSale.SaleID,
StockItemID = item.ProductId,
SellingPrice = item.SellingPrice,
Quantity = item.Qty,
Backordered = false,
ShippedDate = null
});
if (newSaleDetail == null)
{
throw new Exception("Sale detail cannot be added to the database.");
}
// decrease stock
StockItem currentProduct = context.StockItems.Find(item.ProductId);
currentProduct.QuantityOnHand -= item.Qty;
context.Entry(currentProduct).Property(x => x.QuantityOnHand).IsModified = true;
if (currentProduct.QuantityOnHand < 0)
{
throw new Exception("Product out of stock.");
}
}
// all good, delete cart
context.ShoppingCartItems.RemoveRange(
context.ShoppingCartItems.Where(x => x.ShoppingCart.EmployeeID == employeeId));
// delete shopping cart (I used RemoveRange just to make it simpler)
context.ShoppingCarts.RemoveRange(
context.ShoppingCarts.Where(x => x.EmployeeID == employeeId));
// commit all changes
context.SaveChanges();
// retrieve receipt ID
receipt.SaleID = newSale.SaleID;
return(receipt);
}
}
}
// Update cart and cartitems. Must be called with validated parameters.
private void CreateOrUpdateCartValidated(Employee employee, StockItem product, int quantity, CartUpdateMode mode)
{
// handle cart update in one transaction
using (var context = new eToolsContext())
{
// find shopping cart
ShoppingCart cart = context.ShoppingCarts.Where(x => x.EmployeeID == employee.EmployeeID).FirstOrDefault();
// create new cart if not found or update existing cart
if (cart == null)
{
// create new cart
cart = new ShoppingCart();
cart.EmployeeID = employee.EmployeeID;
cart.CreatedOn = DateTime.Now;
context.ShoppingCarts.Add(cart);
}
else
{
// update existing cart
cart.UpdatedOn = DateTime.Now;
context.Entry(cart).Property(x => x.UpdatedOn).IsModified = true;
// context.Entry(cart).State = System.Data.Entity.EntityState.Modified;
}
// find cart item in cart
ShoppingCartItem item = cart.ShoppingCartItems.Where(x => x.StockItemID == product.StockItemID).FirstOrDefault();
// add item to cart if not found or update qty if found
if (item == null)
{
// add item to cart
item = new ShoppingCartItem();
item.ShoppingCartID = cart.ShoppingCartID;
item.StockItemID = product.StockItemID;
item.Quantity = quantity;
context.ShoppingCartItems.Add(item);
}
else
{
// update existing item in cart
if (mode == CartUpdateMode.INCREMENTAL)
{
item.Quantity += quantity;
if (item.Quantity > product.QuantityOnHand)
{
throw new BusinessRuleException(
"Product not added to cart.",
string.Format("Not enough stock: requested {0} available {1}.",
item.Quantity,
product.QuantityOnHand));
}
}
else
{
item.Quantity = quantity;
if (item.Quantity > product.QuantityOnHand)
{
throw new BusinessRuleException(
"Product not added to cart.",
string.Format("Not enough stock: requested {0} available {1}.",
item.Quantity,
product.QuantityOnHand));
}
}
context.Entry(item).Property(x => x.Quantity).IsModified = true;
}
// commit transaction
context.SaveChanges();
}
}
public void ReceiveOrder(int purchaseOrderID, List <OpenPurchaseOrderDetails> openPODetails)
{
//openpodetails and unordereditems collected in code-behind from controls on page
using (var context = new eToolsContext())
{
UnorderedPurchaseItemCartController unorderedItemsController = new UnorderedPurchaseItemCartController();
List <UnorderedPurchaseItemCart> unorderedItems = unorderedItemsController.Get_ListUnorderedPurchaseItemCart();
//transaction
//create a receiveorder for the purchaseorderid
ReceiveOrder receiveOrder = new ReceiveOrder();
receiveOrder.PurchaseOrderID = purchaseOrderID;
receiveOrder.ReceiveDate = DateTime.Now;
context.ReceiveOrders.Add(receiveOrder);
//create receiveorderdetails for each openPODetails if there is anything entered in receivedquantity
foreach (OpenPurchaseOrderDetails openPO in openPODetails)
{
//make a receiveorderdetail if quantityreceived > 0 and update stockitems, make a returnorderdetail if quantityreturned > 0
if (openPO.ReceivedQuantity > 0)
{
ReceiveOrderDetail rOD = new ReceiveOrderDetail();
rOD.ReceiveOrderID = receiveOrder.ReceiveOrderID;
rOD.PurchaseOrderDetailID = openPO.PurchaseOrderDetailID;
rOD.QuantityReceived = openPO.ReceivedQuantity;
openPO.QuantityOutstanding -= openPO.ReceivedQuantity;
context.ReceiveOrderDetails.Add(rOD);
//adjust stockitem QoH (+ReceivedQuantity) and QoO (-ReceivedQuantity) with same stockitemid
StockItem stockItem = context.StockItems.Find(openPO.StockItemID);
stockItem.QuantityOnHand += openPO.ReceivedQuantity;
stockItem.QuantityOnOrder -= openPO.ReceivedQuantity;
context.Entry(stockItem).State = System.Data.Entity.EntityState.Modified;
}
if (openPO.ReturnedQuantity > 0)
{
ReturnedOrderDetail reOD = new ReturnedOrderDetail();
reOD.ReceiveOrderID = receiveOrder.ReceiveOrderID;
reOD.PurchaseOrderDetailID = openPO.PurchaseOrderDetailID;
reOD.ItemDescription = openPO.StockItemDescription;
reOD.Quantity = openPO.ReturnedQuantity;
reOD.Reason = openPO.ReturnReason;
reOD.VendorStockNumber = openPO.VendorStockNumber;
context.ReturnedOrderDetails.Add(reOD);
}
}
//create returnorderdetail for each unorderedItems and delete them from database
UnorderedPurchaseItemCartController unOsysmgr = new UnorderedPurchaseItemCartController();
foreach (UnorderedPurchaseItemCart item in unorderedItems)
{
ReturnedOrderDetail reOD = new ReturnedOrderDetail();
reOD.ReceiveOrderID = receiveOrder.ReceiveOrderID;
reOD.ItemDescription = item.Description;
reOD.Quantity = item.Quantity;
reOD.VendorStockNumber = item.VendorStockNumber;
context.ReturnedOrderDetails.Add(reOD);
unOsysmgr.Delete_UnorderedPurchaseItemCart(item.CartID);
}
//if no purchaseorderdetails have > 0 QOS, close the order
if (!(openPODetails.Any(x => x.QuantityOutstanding > 0)))
{
PurchaseOrder pOrder = context.PurchaseOrders.Find(purchaseOrderID);
pOrder.Closed = true;
context.Entry(pOrder).State = System.Data.Entity.EntityState.Modified;
}
context.SaveChanges();
}
}
