public List <StockItemList> List_StockItemsForCategorySelection(int?categoryid)
{
using (var context = new eToolsContext())
{
if (categoryid != null)
{
var results = from x in context.StockItems
where x.CategoryID == categoryid &&
x.Discontinued.Equals(false)
orderby x.Description
select new StockItemList
{
StockItemID = x.StockItemID,
Items = x.Description,
Price = x.SellingPrice,
QtyOnStock = x.QuantityOnHand,
Count = (from y in context.StockItems
where y.CategoryID == categoryid &&
y.Discontinued.Equals(false)
select y.StockItemID).Count()
};
return(results.ToList());
}
else
{
var results = from x in context.StockItems
where x.CategoryID.Equals(x.CategoryID) &&
x.Discontinued.Equals(false)
orderby x.Description
select new StockItemList
{
StockItemID = x.StockItemID,
Items = x.Description,
Price = x.SellingPrice,
QtyOnStock = x.QuantityOnHand,
Count = (from y in context.StockItems
where y.CategoryID == categoryid &&
y.Discontinued.Equals(false)
select y.StockItemID).Count()
};
return(results.ToList());
}
}
}
public EmployeeInfo User_GetEmployee(string username)
{
//get the employeeid off the ApplicationUser record
//the Application user record represents an instance from the SQL security table AspNetUsers
//this will retreive a single value or the default null
var employeeid = (from person in Users.ToList()
where person.UserName.Equals(username)
select person.EmployeeID).SingleOrDefault(); //single filed uses singleordefault, firstordefault is for the entire record
//was the record of a user
if (employeeid == null)
{
throw new Exception("Not a registered user member");
}
else
{
//get the employee info
EmployeeInfo employeeinfo = null;
//connect to Chinook context class, not the security class, the Chinook class for DbSet<Employee>
using (var context = new eToolsContext())
{
//lookup employee record
//The value that was retreieved during the first linq query is a system.Object
//This System.Object has to be cast into a string , if getting error message "Cannot compair system object"
//Put .ToString()
employeeinfo = (from emp in context.Employees
where emp.EmployeeID.ToString().Equals(employeeid.ToString())
select new EmployeeInfo
{
EmployeeID = emp.EmployeeID,
FirstName = emp.FirstName,
LastName = emp.LastName
}).FirstOrDefault();
if (employeeinfo == null)
{
throw new Exception("Not an employee");
}
}
return(employeeinfo);
}
}
public void DeleteShoppingCart(int employeeid)
{
using (var context = new eToolsContext())
{
//code to go here
var exists = (from x in context.ShoppingCarts
where x.EmployeeID.Equals(employeeid)
select x).FirstOrDefault();
if (exists == null)
{
throw new Exception("Shooping Cart has been removed from the files.");
}
else
{
context.ShoppingCarts.Remove(exists);
context.SaveChanges();
}
}
}//eom
public List <CurrentEquipmentSelection> currentCustomerRental(int rentalId)
{
using (var context = new eToolsContext())
{
List <CurrentEquipmentSelection> current = context.RentalDetails
.Where(x => (x.RentalID == rentalId))
.Select(x =>
new CurrentEquipmentSelection()
{
eqiupmentID = x.RentalEquipment.RentalEquipmentID,
Description = x.RentalEquipment.Description,
SerailNumber = x.RentalEquipment.SerialNumber,
Rate = x.RentalEquipment.DailyRate,
outDate = x.Rental.RentalDate
}).ToList();
return(current);
}
}
public void PurchaseOrder_Delete(int vendorid)
{
using (var context = new eToolsContext())
{
PurchaseOrder getPO = context.PurchaseOrders.Where(x => x.VendorID.Equals(vendorid) && x.OrderDate == null).FirstOrDefault();
List <PurchaseOrderDetail> getPODetails = (from x in context.PurchaseOrderDetails
where x.PurchaseOrderID.Equals(getPO.PurchaseOrderID)
select x).ToList();
if (getPODetails != null)
{
foreach (PurchaseOrderDetail item in getPODetails)
{
context.PurchaseOrderDetails.Remove(item);
}
}
context.PurchaseOrders.Remove(getPO);
context.SaveChanges();
}
}
public List <UserProfile> ListAllUsers()
{
//used on the admin page user panel to load ListView
//Users.ToList() brings Users data from security tables
// into memory for use by linq query
//EmployeeID and CustomerID were added to the Users table data
// in ApplicationUser entity
//these values will be used in using() to get names from
// the Chinook database
var rm = new ApplicationRoleManager();
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 eToolsContext())
{
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());
}
} //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 EmployeeInfo User_GetEmployee(string username)
{
//get the employeeid off the ApplicationUser record
//the Application User record represents an instance
// from the sql security table AspNetUsers
//this is retreive a single value or the default null
var employeeid = (from person in Users.ToList()
where person.UserName.Equals(username)
select person.EmployeeID).SingleOrDefault();
//was the record a user
if (employeeid == null)
{
throw new Exception("Not a registered user member");
}
else
{
//get the employee info
EmployeeInfo employeeinfo = null;
//connect to Chinook context class for DbSet<Employee>
using (var context = new eToolsContext())
{
//lookup employee record
//the value that was retreive during the first
// linq query is a System.Object
//this System.Object has be cast into a string
// thus .ToString()
employeeinfo = (from emp in context.Employees
where emp.EmployeeID.ToString().Equals(employeeid.ToString())
select new EmployeeInfo
{
EmployeeID = emp.EmployeeID,
FirstName = emp.FirstName,
LastName = emp.LastName
}).FirstOrDefault();
if (employeeinfo == null)
{
throw new Exception("Not an employee");
}
}
return(employeeinfo);
}
}
public void Add_ProductToShoppingCart(int employeeid, int stockitemid, int qty)
{
List <string> reasons = new List <string>();
using (var context = new eToolsContext())
{
ShoppingCart exists = context.ShoppingCarts
.Where(x => x.EmployeeID.Equals(employeeid)).Select(x => x).FirstOrDefault();
ShoppingCartItem newItem = null;
if (exists == null)
{
exists = new ShoppingCart();
exists.EmployeeID = employeeid;
exists.CreatedOn = DateTime.Now;
exists = context.ShoppingCarts.Add(exists);
}
else
{
newItem = exists.ShoppingCartItems.SingleOrDefault(x => x.StockItemID == stockitemid);
if (newItem != null)
{
reasons.Add("Item already exists in the cart.");
}
}
if (reasons.Count() > 0)
{
throw new BusinessRuleException("Adding item to cart", reasons);
}
else
{
newItem = new ShoppingCartItem();
newItem.StockItemID = stockitemid;
newItem.Quantity = qty;
exists.ShoppingCartItems.Add(newItem);
exists.UpdatedOn = DateTime.Now;
context.SaveChanges();
}
}
}
}//eom
public void Update_RefreshCart(int employeeid, int shoppingitemid, int quantity)
{
using (var context = new eToolsContext())
{
var exists = (from x in context.ShoppingCarts
where x.EmployeeID.Equals(employeeid)
select x).FirstOrDefault();
if (exists == null)
{
throw new Exception("shopping cart has been removed from the file");
}
else
{
exists.UpdatedOn = DateTime.Now;
var newShoppingcartitems = context.ShoppingCartItems.Find(shoppingitemid);
newShoppingcartitems.Quantity = quantity;
}
context.SaveChanges();
}
}
public OpenOrder OpenOrder_FindByID(int _purchaseOrderID)
{
using (var context = new eToolsContext())
{
var openOrder = (
from x in context.PurchaseOrders
where x.PurchaseOrderID == _purchaseOrderID
select new OpenOrder
{
POID = x.PurchaseOrderID,
PONumber = x.PurchaseOrderNumber,
PODate = x.OrderDate,
VendorName = x.Vendor.VendorName,
VendorPhone = x.Vendor.Phone
})
.FirstOrDefault();
return(openOrder);
}
}
public List <OrderInfo> ListOrderInfo(int vendorid)
{
using (var context = new eToolsContext())
{
var info = from form in context.PurchaseOrderDetails
where form.PurchaseOrder.VendorID == vendorid
where form.Quantity > 0
select new OrderInfo
{
ItemID = form.StockItem.StockItemID,
Desc = form.StockItem.Description,
QOH = form.StockItem.QuantityOnHand,
ROL = form.StockItem.ReOrderLevel,
QOO = form.StockItem.QuantityOnOrder,
Buffer = form.StockItem.QuantityOnHand - form.StockItem.ReOrderLevel,
PurchasePrice = form.PurchasePrice
};
return(info.ToList());
}
}
public List <ViewPurchaseOrderItems> GetPurchaseOrderDetails(int purchaseOrderID)
{
using (var context = new eToolsContext())
{
var result = from item in context.PurchaseOrderDetails
where item.PurchaseOrderID.Equals(purchaseOrderID)
select new ViewPurchaseOrderItems()
{
ItemID = item.StockItemID,
ItemDescription = item.StockItem.Description,
Ordered = item.Quantity,
//Outstanding = item.Quantity - item.ReceiveOrderDetails.Select(receive => receive.QuantityReceived).DefaultIfEmpty(0).Sum(),
Outstanding = item.StockItem.QuantityOnOrder,
Received = 0,
Returned = 0,
Reason = ""
};
return(result.ToList());
}
}
public List <Rental> Get_RentalsByCusomterSearch(int customerID)
{
//need to pick customer then pick rentals from that
using (var context = new eToolsContext())
{
//where rental equipment is in rentaldetails where rentalid == rentalID nested
var results = from x in context.Rentals
where x.CustomerID.Equals(customerID)
select new Rental
{
RentalID = x.RentalID,
CustomerID = x.CustomerID,
RentalDate = x.RentalDate,
EmployeeID = x.EmployeeID //add more if needed idk
};
return(results.ToList());
}
}
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 List <RentalEquipmentInfo> List_RentingEquipment(int rentalID)
{
using (var context = new eToolsContext())
{
//where rental equipment is in rentaldetails where rentalid == rentalID nested
var results = from x in context.RentalDetails
where x.RentalID.Equals(rentalID)
select new RentalEquipmentInfo
{
RentalEquipmentID = x.RentalEquipment.RentalEquipmentID,
Description = x.RentalEquipment.Description,
SerialNumber = x.RentalEquipment.SerialNumber,
ModelNumber = x.RentalEquipment.ModelNumber,
DailyRate = x.DailyRate,
Condition = x.ConditionOut
};
return(results.ToList());
}
}
public List <Customer> Customer_Get_By_PhoneNumber(string ContactPhone)
{
if (ContactPhone != null)
{
if (ContactPhone.Length == 10)
{
ContactPhone = ContactPhone.Substring(0, 3) + "."
+ ContactPhone.Substring(3, 3) + "."
+ ContactPhone.Substring(6, 4);
}
}
using (var context = new eToolsContext())
{
return(context.Customers
.Where(x => x.ContactPhone.Equals(ContactPhone))
.OrderBy(x => x.LastName)
.ToList());
}
}
public List <OpenOrder> OpenOrder_List()
{
using (var context = new eToolsContext())
{
var OpenOrders = (
from x in context.PurchaseOrders
where x.Closed == false
select new OpenOrder
{
POID = x.PurchaseOrderID,
PONumber = x.PurchaseOrderNumber,
PODate = x.OrderDate,
VendorName = x.Vendor.VendorName,
VendorPhone = x.Vendor.Phone
})
.ToList();
return(OpenOrders);
}
}
public int createAndReturnEmptyRentalID(int customerid, int employeeid, int?couponid, string creditcard)
{
using (var context = new eToolsContext())
{
//Must have credit card
if (string.IsNullOrEmpty(creditcard)) //customer can NOT PAY
{
throw new BusinessRuleException("Must supply credit!", logger);
}
else //CAN PAY
{
Rental rental = new Rental();
rental.CustomerID = customerid;
rental.EmployeeID = employeeid;
rental.CouponID = couponid;
rental.SubTotal = 0;
rental.TaxAmount = 0;
rental.RentalDate = DateTime.Today; //will be overriden when submitted
rental.PaymentType = 'M'.ToString(); //default is space****! N for Not paid
rental.CreditCard = creditcard;
//this will return the add rental object w/ an id set **I hope!!
//also it will override the current object
rental = context.Rentals.Add(rental);
if (rental == null)
{
throw new BusinessRuleException("Rental could not be created!", logger);
}
else
{
//Commit changes
context.SaveChanges();
//return rentalid
return(rental.RentalID);
}
}
}
}
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 List <PurchaseOrdersList> ListPurchaseOrder(int vendorid)
{
using (var context = new eToolsContext())
{
var result = from data in context.PurchaseOrderDetails
where data.PurchaseOrder.VendorID == vendorid
where data.StockItem.StockItemID == data.StockItemID
where data.Quantity > 0
select new PurchaseOrdersList()
{
StockItemID = data.StockItem.StockItemID,
Description = data.StockItem.Description,
QuantityOnHand = data.StockItem.QuantityOnHand,
ReOrderLevel = data.StockItem.ReOrderLevel,
QuantityOnOrder = data.StockItem.QuantityOnOrder,
PurchaseOrderQuantity = data.Quantity,
PPrice = data.StockItem.PurchasePrice
};
return(result.ToList());
}
}
public List <CurrentActiveOrderList> list_PlacedCurerentActiveOrder(int vendorid)
{
using (var context = new eToolsContext())
{
var theorder = (from x in context.PurchaseOrderDetails
where x.PurchaseOrder.VendorID == vendorid &&
x.PurchaseOrder.OrderDate == DateTime.Today
orderby x.StockItem.Description
select new CurrentActiveOrderList
{
StockItemID = x.StockItem.StockItemID,
Description = x.StockItem.Description,
QuantityOnHand = x.StockItem.QuantityOnHand,
QuantityOnOrder = x.StockItem.QuantityOnOrder,
ReOrderLevel = x.StockItem.ReOrderLevel,
QuantityToOrder = x.Quantity,
Price = Math.Round(x.PurchasePrice, 2)
});
return(theorder.ToList());
}
}
public List <OpenPurchaseOrderDetails> List_OpenPurchaseOrderDetails(int PurchaseOrderID)
{
using (var context = new eToolsContext())
{
List <OpenPurchaseOrderDetails> openPODetails = (from x in context.PurchaseOrderDetails
where x.PurchaseOrder.PurchaseOrderID == PurchaseOrderID
select new OpenPurchaseOrderDetails
{
PurchaseOrderDetailID = x.PurchaseOrderDetailID,
VendorStockNumber = context.StockItems.Where(s => s.StockItemID == x.StockItemID).FirstOrDefault().VendorStockNumber,
StockItemID = x.StockItemID,
StockItemDescription = x.StockItem.Description,
QuantityOnOrder = x.Quantity,
QuantityOutstanding = x.Quantity - ((x.ReceiveOrderDetails.Sum(q => q.QuantityReceived)) == null ? 0 : x.ReceiveOrderDetails.Sum(q => q.QuantityReceived)),
ReceivedQuantity = 0,
ReturnedQuantity = 0,
ReturnReason = ""
}).ToList();
return(openPODetails);
}
}
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();
}
}
protected void PurchaseOrderSelectionList_ItemCommand(object sender, ListViewCommandEventArgs e)
{
ReceiveButton.Visible = true;
ForceCloseButton.Visible = true;
ReasonLabel.Visible = true;
ReasonTextBox.Visible = true;
int pOrderID = int.Parse(e.CommandArgument.ToString());
PurchaseOrderIDLabel.Text = pOrderID.ToString();
UnorderedPurchaseItemCartListView.DataBind();
using (var context = new eToolsContext())
{
PurchaseOrder pOrder = context.PurchaseOrders.Find(pOrderID);
PurchaseOrderNumberLabel.Text = "Purchase Order #: " + pOrder.PurchaseOrderNumber.ToString();
DateLabel.Text = "Date: " + pOrder.OrderDate == null ? "" : pOrder.OrderDate.Value.ToShortDateString();
VendorLabel.Text = pOrder.Vendor.VendorName;
VendorPhoneLabel.Text = pOrder.Vendor.Phone;
}
}
public List <UserProfile> ListAllUserInfo()
{
var rm = new ApplicationRoleManager();
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,
RequestedPassord = person.PasswordHash,
RoleMemberships = person.Roles.Select(r => rm.FindById(r.RoleId).Name)
};
using (var context = new eToolsContext())
{
Employee tempEmployee;
foreach (var person in tempresults)
{
// (!)
// Note: Passwords are encrypted, so a hard coded value is the only way to display the actual password on the About.aspx page
person.RequestedPassord = STR_DEFAULT_PASSWORD;
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 Cancel_Rental(int rentalID)
{
using (var context = new eToolsContext())
{
//remove the details then the rental
var exists = (from x in context.Rentals
where x.RentalID.Equals(rentalID)
select x).FirstOrDefault();
if (exists != null)
{
var detailExists = (from x in context.RentalDetails
where x.RentalID.Equals(rentalID)
select x).FirstOrDefault();
if (detailExists != null)
{
//delete all details
RentalDetail detail = null;
List <int> detailsIDs = (from x in context.RentalDetails
where x.RentalID.Equals(rentalID)
select x.RentalDetailID).ToList();
foreach (var detailstoDelet in detailsIDs)
{
detail = exists.RentalDetails.
Where(x => x.RentalID == rentalID).
FirstOrDefault();
if (detail != null)
{
exists.RentalDetails.Remove(detail);
}
}
}
var rentaltoremove = (from x in context.Employees
where x.Rentals.Equals(rentalID)
select x).FirstOrDefault();
rentaltoremove.Rentals.Remove(exists);
}
context.SaveChanges();
}
}
}// part-three, Insert button.
#endregion
#region Create RceiveOrderDetail
public void Create_ReceiveOrderDetail(int receorderid, List <RecevingOrderList> quanreceList)
{
using (var context = new eToolsContext())
{
foreach (var item in quanreceList)
{
ReceiveOrderDetail newReceiveOrderDetail = new ReceiveOrderDetail();
newReceiveOrderDetail.ReceiveOrderID = receorderid;
newReceiveOrderDetail.PurchaseOrderDetailID = item.PurchasePrderDetail;
newReceiveOrderDetail.QuantityReceived = item.Receiving.GetValueOrDefault();
if (item.Outstanding > 0 && item.Receiving != 0)
{
context.ReceiveOrderDetails.Add(newReceiveOrderDetail);
}
}
context.SaveChanges();
}
}// part-three, Insert button.
public List <PurchaseOrderList> List_PurchaseOrderForReceiveOrdersSelection()
{
using (var context = new eToolsContext())
{
var results = from x in context.PurchaseOrders
orderby x.OrderDate
where x.Closed.Equals(false)
select new PurchaseOrderList
{
PON = x.PurchaseOrderNumber,
OrderDate = x.OrderDate,
VendorName = x.Vendor.VendorName,
Phone = x.Vendor.Phone,
PurchaseOrderID = x.PurchaseOrderID,
VendorID = x.VendorID,
EmployeeID = x.EmployeeID
};
return(results.ToList());
}
}// part-one, select outstanding orders.
