public List <JobListPoco> JobList()
{
using (var context = new eBikeContext())
{
var results = from x in context.Jobs
where string.IsNullOrEmpty(x.JobDateOut.ToString())
select new JobListPoco
{
JobID = x.JobID,
JobDateIn = x.JobDateIn,
JobDateStarted = x.JobDateStarted,
JobDateDone = x.JobDateDone,
Name = x.Customer.LastName + ", " + x.Customer.FirstName,
ContactPhone = x.Customer.ContactPhone
};
return(results.ToList());
}
}//eom
public List <ServiceDetailPoco> ListServiceDetails(int jobId)
{
using (var context = new eBikeContext())
{
var results = from data in context.ServiceDetails
where data.JobID == jobId
select new ServiceDetailPoco
{
ServiceDetailID = data.ServiceDetailID,
Description = data.Description,
JobHours = data.JobHours,
CouponID = data.Coupon.CouponID,
CouponIDValue = data.Coupon.CouponIDValue,
Comments = data.Comments,
Status = data.Status
};
return(results.ToList());
}
}
public string Get_EmployeeFullName(string username)
{
using (var context = new eBikeContext())
{
var findemployeeid = (from x in Users.ToList()
where x.UserName.Equals(username)
select x.EmployeeID).First();
int employeeid = int.Parse(findemployeeid.ToString());
var findemployeename = (from x in (context.Employees).ToList()
where x.EmployeeID.Equals(employeeid)
select x.FirstName + " " + x.LastName).First();
string employeename = findemployeename.ToString();
return(employeename);
}
}
public int Get_EmployeeId(string username)
{
using (var context = new eBikeContext())
{
var findemployeeid = (from x in Users.ToList()
where x.UserName.Equals(username)
select x.EmployeeID).First();
//// This is because in the eBikes database, Webmaster has no employeeID by default. Setting it as employee = 1 as a default.
if (findemployeeid == null)
{
findemployeeid = 1;
}
////
return(findemployeeid.Value);
}
}
}//Remove_CartItem
public List <Part> Check_ForBackorders(string username)
{
using (var context = new eBikeContext())
{
List <Part> backordered = new List <Part>();
//Get the customers id
int customerid = (from x in context.OnlineCustomers
where x.UserName.Equals(username)
select x.OnlineCustomerID).FirstOrDefault();
if (customerid != 0)
{
//Get the customers shopping cart id
int shoppingcartid = (from x in context.ShoppingCarts
where x.OnlineCustomerID.Equals(customerid)
select x.ShoppingCartID).FirstOrDefault();
if (shoppingcartid != 0)
{
//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();
if (useritems.Count > 0)
{
foreach (ShoppingCartItem item in useritems)
{
Part part = (from x in context.Parts
where x.PartID.Equals(item.PartID)
select x).FirstOrDefault();
if (item.Quantity > part.QuantityOnHand)
{
backordered.Add(part);
}
}
}
}
}
return(backordered);
}
}//Check_ForBackorders
public List <VendorStockItems> GetVendorStockItemsNotOnOrder(int vendorID, PurchaseOrderInfo data)
{
using (var context = new eBikeContext())
{
var results = from inventory in context.Parts.ToList()
where inventory.VendorID == vendorID &&
!data.PurchaseOrderDetails.Any(x => x.ID == inventory.PartID)
select new VendorStockItems
{
ID = inventory.PartID,
Description = inventory.Description,
QOH = inventory.QuantityOnHand,
QOO = inventory.QuantityOnOrder,
Buffer = (inventory.ReorderLevel - (inventory.QuantityOnHand + inventory.QuantityOnOrder)),
Price = inventory.PurchasePrice
};
return(results.ToList());
}
}
public List <PartInfo> ListPartsByCategory(int categoryId, string name)
{
var shoppingCartId = GetShoppingCartId(name);
using (var context = new eBikeContext())
{
if (categoryId < 0)
{
var result = from part in context.Parts
where part.Discontinued == false
orderby part.Description
select new PartInfo
{
PartID = part.PartID,
Description = part.Description,
SellingPrice = part.SellingPrice,
QuantityOnHand = part.QuantityOnHand,
QuantityInCart = (from cartItem in part.ShoppingCartItems
where cartItem.ShoppingCartID == shoppingCartId
select cartItem.Quantity).FirstOrDefault()
};
return(result.ToList());
}
else
{
var result = from part in context.Parts
where part.Discontinued == false && part.CategoryID == categoryId
orderby part.Description
select new PartInfo
{
PartID = part.PartID,
Description = part.Description,
SellingPrice = part.SellingPrice,
QuantityOnHand = part.QuantityOnHand,
QuantityInCart = (from cartItem in part.ShoppingCartItems
where cartItem.ShoppingCartID == shoppingCartId
select cartItem.Quantity).FirstOrDefault()
};
return(result.ToList());
}
}
}
public List <CartInfo> GetCartInfo(string userName)
{
var shoppingCartId = GetShoppingCartId(userName);
using (var context = new eBikeContext())
{
var result = from shoppingCartItem in context.ShoppingCartItems
where shoppingCartItem.ShoppingCartID == shoppingCartId
select new CartInfo
{
CartItemId = shoppingCartItem.ShoppingCartItemID,
UpdatedOn = shoppingCartItem.ShoppingCart.UpdatedOn,
Description = shoppingCartItem.Part.Description,
SellingPrice = shoppingCartItem.Part.SellingPrice,
Quantity = shoppingCartItem.Quantity,
QOH = shoppingCartItem.Part.QuantityOnHand
};
return(result.ToList());
}
}
public int GetCartItemId(int shoppingCartId, int productId)
{
using (var context = new eBikeContext())
{
var resultAmount = (from shoppingCarItem in context.ShoppingCartItems
where shoppingCarItem.PartID == productId && shoppingCarItem.ShoppingCartID == shoppingCartId
select shoppingCarItem.ShoppingCartItemID).Count();
if (resultAmount == 0)
{
return(0);
}
else
{
var result = from shoppingCarItem in context.ShoppingCartItems
where shoppingCarItem.PartID == productId && shoppingCarItem.ShoppingCartID == shoppingCartId
select shoppingCarItem.ShoppingCartItemID;
return(result.Single());
}
}
}
// this is to create the suggested order in the database
public void NewSuggestedOrder(PurchaseOrder purchaseorder, List <PurchaseOrderDetail> purchaseorderdetails, int employeeId)
{
using (var context = new eBikeContext()) // start transaction
{
purchaseorder.EmployeeID = employeeId;
// add the created suggested purchase order to the database
purchaseorder = context.PurchaseOrders.Add(purchaseorder); //staging
// add the created suggested purchase order details to the database
foreach (PurchaseOrderDetail detail in purchaseorderdetails)
{
detail.PurchaseOrderID = purchaseorder.PurchaseOrderID;
context.PurchaseOrderDetails.Add(detail);
}
// save the changes to database and end transaction
context.SaveChanges();
}
}
public VendorInformation Vendor_Get(int vendorID)
{
if (vendorID == 0)
{
throw new Exception("No vendor selected.");
}
using (var context = new eBikeContext())
{
var result = from vendor in context.Vendors
where vendor.VendorID.Equals(vendorID)
select new VendorInformation
{
VendorName = vendor.VendorName,
City = vendor.City,
Phone = vendor.Phone
};
return(result.Single());
}
}
public int GetCustomerId(string userName)
{
using (var context = new eBikeContext())
{
var onlineCustomerIdAmount = (from onlineCustomer in context.OnlineCustomers
where onlineCustomer.UserName == userName
select onlineCustomer.OnlineCustomerID).Count();
//don't have online customer
if (onlineCustomerIdAmount == 0)
{
return(0);
}
else
{
var onlineCustomerIdResult = from onlineCustomer in context.OnlineCustomers
where onlineCustomer.UserName == userName
select onlineCustomer.OnlineCustomerID;
return(onlineCustomerIdResult.Single());
}
}
}
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 UpdateTotalsSuggestedOrder(PurchaseOrder purchaseorder, PurchaseOrder purchaseordertotals)
{
using (var context = new eBikeContext()) // start transaction
{
var activeorder = (from x in context.PurchaseOrders
where x.VendorID == purchaseorder.VendorID && x.PurchaseOrderNumber == null && x.OrderDate == null
select x.PurchaseOrderID).FirstOrDefault();
var updateSubtotal = context.PurchaseOrders.Find(activeorder);
updateSubtotal.SubTotal = purchaseordertotals.SubTotal;
context.Entry(updateSubtotal).Property(y => y.SubTotal).IsModified = true;
var updateTaxAmount = context.PurchaseOrders.Find(activeorder);
updateTaxAmount.TaxAmount = purchaseordertotals.TaxAmount;
context.Entry(updateTaxAmount).Property(y => y.TaxAmount).IsModified = true;
// save the changes to database and end transaction
context.SaveChanges();
}
}
public List <PurchaseOrderDetails> GetCurrentActivePurchaseOrderDetails(int vendorID)
{
using (var context = new eBikeContext())
{
var results = from purchaseOrderDetails in context.PurchaseOrderDetails
where purchaseOrderDetails.PurchaseOrder.VendorID.Equals(vendorID) &&
purchaseOrderDetails.PurchaseOrder.PurchaseOrderNumber == null &&
purchaseOrderDetails.PurchaseOrder.OrderDate == null
select new PurchaseOrderDetails
{
PurchaseOrderID = purchaseOrderDetails.PurchaseOrderID,
ID = purchaseOrderDetails.PartID,
Description = purchaseOrderDetails.Part.Description,
QOH = purchaseOrderDetails.Part.QuantityOnHand,
ROL = purchaseOrderDetails.Part.ReorderLevel,
QOO = purchaseOrderDetails.Part.QuantityOnOrder,
Quantity = purchaseOrderDetails.Quantity,
PurchasePrice = purchaseOrderDetails.PurchasePrice
};
return(results.ToList());
}
}
public VendorPurchaseOrderDetailsDTO GetPODetails(int poID)
{
using (var context = new eBikeContext())
{
VendorPurchaseOrderDetailsDTO vendorPODetails = new VendorPurchaseOrderDetailsDTO();
List <PurchaseOrderDetailsPOCO> poDetails = new List <PurchaseOrderDetailsPOCO>();
var poResults = from pod in context.PurchaseOrderDetails
orderby pod.PartID ascending
where pod.PurchaseOrderID.Equals(poID)
select new PurchaseOrderDetailsPOCO
{
PurchaseOrderID = pod.PurchaseOrderID,
PurchaseOrderDetailID = pod.PurchaseOrderDetailID,
PartID = pod.PartID,
Description = pod.Part.Description,
QuantityOnOrder = pod.Quantity,
QuantityOutstanding = pod.ReceiveOrderDetails.Select(rod => rod.QuantityReceived).Any() ? pod.Quantity - pod.ReceiveOrderDetails.Sum(rod => rod.QuantityReceived) : pod.Quantity,
};
poDetails = poResults.ToList();
var vendorResults = (from po in context.PurchaseOrders
where po.PurchaseOrderID.Equals(poID)
select new
{
poNum = po.PurchaseOrderNumber,
Phone = po.Vendor.Phone,
Name = po.Vendor.VendorName
}).FirstOrDefault(); //return first record from return result(using it when have only one item to return)
vendorPODetails.PurchaseOrderNumber = vendorResults.poNum;
vendorPODetails.VendorPhone = vendorResults.Phone;
vendorPODetails.VendorName = vendorResults.Name;
vendorPODetails.PODetails = poDetails;
return(vendorPODetails);
}
}
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();
}
}
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 int GetShoppingCartId(string userName)
{
using (var context = new eBikeContext())
{
var onlineCustomerIdAmount = (from onlineCustomer in context.OnlineCustomers
where onlineCustomer.UserName == userName
select onlineCustomer.OnlineCustomerID).Count();
//don't have online customer
if (onlineCustomerIdAmount == 0)
{
return(-1);
}
else
{
var onlineCustomerIdResult = from onlineCustomer in context.OnlineCustomers
where onlineCustomer.UserName == userName
select onlineCustomer.OnlineCustomerID;
int onlineCustomerId = onlineCustomerIdResult.Single();
var shoppingCartIdAmount = (from shoppingCart in context.ShoppingCarts
where shoppingCart.OnlineCustomerID == onlineCustomerId
select shoppingCart.ShoppingCartID).Count();
if (shoppingCartIdAmount == 0)
{
return(0);
}
else
{
var shoppingCartIdResult = from shoppingCart in context.ShoppingCarts
where shoppingCart.OnlineCustomerID == onlineCustomerId
select shoppingCart.ShoppingCartID;
return(shoppingCartIdResult.Single());
}
}
}
}
}//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 List <UserProfile> ListUsers()
{
using (var context = new eBikeContext())
{
var employees = from emp in context.Employees
select new UserProfile
{
Id = emp.EmployeeID.ToString(),
Name = emp.FirstName,
OtherName = emp.LastName,
UserType = UserType.Employee
};
var customers = from cust in context.Customers
select new UserProfile
{
Id = cust.CustomerID.ToString(),
Name = cust.FirstName,
OtherName = cust.LastName,
UserType = UserType.Customer
};
var result = employees.Union(customers);
return(result.ToList());
}
}
public List <UserProfile> ListAllUsers()
{
var rm = new RoleManager();
List <UserProfile> results = new List <UserProfile>();
var tempresults = from person in Users.ToList()
select new UserProfile
{
UserId = person.Id,
UserName = person.UserName,
Email = person.Email,
EmailConfirmation = person.EmailConfirmed,
EmployeeId = person.EmployeeID,
CustomerId = person.CustomerID,
RoleMemberships = person.Roles.Select(r => rm.FindById(r.RoleId).Name)
};
//get any user first and last names
using (var context = new eBikeContext())
{
Employee tempEmployee;
foreach (var person in tempresults)
{
if (person.EmployeeId.HasValue)
{
tempEmployee = context.Employees.Find(person.EmployeeId);
if (tempEmployee != null)
{
person.FirstName = tempEmployee.FirstName;
person.LastName = tempEmployee.LastName;
}
}
results.Add(person);
}
}
return(results.ToList());
}
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 FinalTotalPOCO ShoppingCart_FinalTotals(string username, int couponid)
{
using (var context = new eBikeContext())
{
var totals = new FinalTotalPOCO();
var customer = (from x in context.OnlineCustomers
where x.UserName.Equals(username)
select x).FirstOrDefault();
if (customer == null)
{
totals.SubTotal = 0;
totals.Discount = 0;
totals.GST = 0;
totals.Total = 0;
}
else
{
int customerid = customer.OnlineCustomerID;
double coupondiscount = (from x in context.Coupons
where x.CouponID == couponid
select x.CouponDiscount).FirstOrDefault();
var shoppingcart = (from x in context.ShoppingCarts
where x.OnlineCustomerID.Equals(customerid)
select x).FirstOrDefault();
if (shoppingcart == null)
{
totals.SubTotal = 0;
totals.Discount = 0;
totals.GST = 0;
totals.Total = 0;
}
else
{
var items = (from x in context.ShoppingCartItems
where x.ShoppingCartID == shoppingcart.ShoppingCartID
select x).FirstOrDefault();
if (items == null)
{
totals.SubTotal = 0;
totals.Discount = 0;
totals.GST = 0;
totals.Total = 0;
}
else
{
var sum = (from x in context.ShoppingCartItems
where x.ShoppingCartID == shoppingcart.ShoppingCartID
select(x.Quantity * x.Part.SellingPrice)).Sum();
totals.SubTotal = sum;
totals.Discount = Decimal.Multiply(sum, decimal.Parse((coupondiscount / 100).ToString()));
totals.GST = Decimal.Multiply((sum - totals.Discount), decimal.Parse("0.05"));
totals.Total = totals.SubTotal - totals.Discount + totals.GST;
}
}
}
return(totals);
}
}//ShoppingCart_FinalTotals
}//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 AddEmployees()
{
using (var context = new eBikeContext())
{
//get all current employees
//linq query will not execute as yet
//return datatype will be IQueryable<EmployeeListPOCO>
var currentEmployees = from x in context.Employees
select new EmployeeListPOCO
{
EmployeeID = x.EmployeeID,
FirstName = x.FirstName,
LastName = x.LastName
};
//get all employees who have an user account
//Users needs to be in memory therfore use .ToList()
//POCO EmployeeID is an int
//the Users Employee id is an int?
//since we will only be retrieving
// Users that are employees (ID is not null)
// we need to convert the nullable int into
// a require int
//the results of this query will be in memory
var UserEmployees = from x in Users.ToList()
where x.EmployeeID.HasValue
select new RegisteredEmployeePOCO
{
UserName = x.UserName,
EmployeeID = int.Parse(x.EmployeeID.ToString())
};
//loop to see if auto generation of new employee
//Users record is needed
//the foreach cause the delayed execution of the
//linq above
foreach (var employee in currentEmployees)
{
//does the employee NOT have a logon (no User record)
if (!UserEmployees.Any(us => us.EmployeeID == employee.EmployeeID))
{
//create a suggested employee UserName
//firstname initial + LastName: dwelch
var newUserName = employee.FirstName.Substring(0, 1) + employee.LastName;
//create a new User ApplicationUser instance
var userAccount = new ApplicationUser()
{
UserName = newUserName,
Email = string.Format(STR_EMAIL_FORMAT, newUserName),
EmailConfirmed = true
};
userAccount.EmployeeID = employee.EmployeeID;
//create the Users record
IdentityResult result = this.Create(userAccount, STR_DEFAULT_PASSWORD);
//result hold the return value of the creation attempt
//if true, account was created,
//if false, an account already exists with that username
if (!result.Succeeded)
{
//name already in use
//get a UserName that is not in use
newUserName = VerifyNewUserName(newUserName);
userAccount.UserName = newUserName;
this.Create(userAccount, STR_DEFAULT_PASSWORD);
}
//create the staff role in UserRoles
this.AddToRole(userAccount.Id, SecurityRoles.Staff);
}
}
}
}
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();
}
}
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();
}
}
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();
}
}
