/// <summary>
/// Handles the Load event of the Page control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs" /> instance containing the event data.</param>
protected void Page_Load(object sender, EventArgs e)
{
try
{
if (!IsPostBack)
{
// Populate the carts drop down
var context = new SoaDataContext();
var labels = context.Carts.Where(c => c.deleted == (byte)0)
.Select(c => String.Format("{0},{1}", c.orderID, c.prodID)).ToList();
CartDropDown.Items.Clear();
foreach (var label in labels)
{
CartDropDown.Items.Add(label);
}
SetupDeleteButtons();
if (Request.QueryString["response"] != null)
{
ServerResponseText.Text = Request.QueryString["response"].ToString();
}
}
}
catch (Exception ex)
{
Logger.GetInstance().Write(ex);
throw;
}
}
/// <summary>
/// Performs a purchase order search
/// </summary>
/// <returns>The search results.</returns>
private SearchResult PurchaseOrderSearch()
{
var context = new SoaDataContext();
var query = (from customer in context.Customers
where customer.deleted == (byte)0
from order in context.Orders
where customer.custID == order.custID && order.deleted == (byte)0
from cart in context.Carts
where cart.orderID == order.orderID && cart.deleted == (byte)0
from product in context.Products
where product.prodID == cart.prodID && product.deleted == (byte)0
select new { Customer = customer, Cart = cart, Order = order, Product = product }).Distinct();
// Customer fields
if (Data.Customer_CustID != null)
{
query = query.Where(q => q.Customer.custID == Data.Customer_CustID);
}
if (Data.Customer_FirstName != null)
{
query = query.Where(q => q.Customer.firstName == Data.Customer_FirstName);
}
if (Data.Customer_LastName != null)
{
query = query.Where(q => q.Customer.lastName == Data.Customer_LastName);
}
// Order fields
if (Data.Order_OrderID != null)
{
query = query.Where(q => q.Order.orderID == Data.Order_OrderID);
}
if (Data.Order_OrderDate != null)
{
query = query.Where(q => q.Order.orderDate == Data.Order_OrderDate);
}
if (Data.Order_PoNumber != null)
{
query = query.Where(q => q.Order.poNumber == Data.Order_PoNumber);
}
var searchResult = new SearchResult();
var results = query.Distinct().Select(q => CreateServerServiceRequest(q.Customer, q.Order, q.Product, q.Cart)).ToList();
searchResult.Columns.AddRange(new string[]
{
"Product_ProdID",
"Product_ProdName",
"Cart_Quantity",
"Product_Price",
"Product_ProdWeight"
});
searchResult.Rows = results;
try
{
var cust = query.Select(c => c.Customer).FirstOrDefault();
searchResult.CustID = cust.custID;
searchResult.FirstName = cust.firstName;
searchResult.LastName = cust.lastName;
searchResult.PhoneNumber = cust.phoneNumber;
var order = query.Select(c => c.Order).FirstOrDefault();
searchResult.PoNumber = order.poNumber;
searchResult.PurchaseDate = order.orderDate;
var subTotal = query.Where(q => q.Product.inStock == (byte)1).Select(q => q.Product.price * q.Cart.quantity).Sum();
var tax = subTotal * 0.13;
var total = subTotal + tax;
searchResult.SubTotal = subTotal;
searchResult.Tax = tax;
searchResult.Total = total;
var totalPieces = query.Where(q => q.Product.inStock == (byte)1).Select(q => q.Cart.quantity).Sum();
var totalWeight = query.Where(q => q.Product.inStock == (byte)1).Select(q => q.Product.prodWeight * q.Cart.quantity).Sum();
searchResult.TotalNumberOfPieces = totalPieces;
searchResult.TotalWeight = totalWeight;
}
catch (Exception)
{
throw new Exception("No data found for purchase order");
}
return(searchResult);
}
/// <summary>
/// Performs the normal search.
/// </summary>
/// <returns></returns>
private SearchResult PerformNormalSearch()
{
var context = new SoaDataContext();
var query = (from customer in context.Customers
where customer.deleted == (byte)0
from order in context.Orders
where customer.custID == order.custID && order.deleted == (byte)0
from cart in context.Carts
where cart.orderID == order.orderID && cart.deleted == (byte)0
from product in context.Products
where product.prodID == cart.prodID && product.deleted == (byte)0
select new { Customer = customer, Cart = cart, Order = order, Product = product }).Distinct();
// TODO: Replace with reflection
// Customer fields
if (Data.Customer_CustID != null)
{
query = query.Where(q => q.Customer.custID == Data.Customer_CustID);
}
if (Data.Customer_FirstName != null)
{
query = query.Where(q => q.Customer.firstName == Data.Customer_FirstName);
}
if (Data.Customer_LastName != null)
{
query = query.Where(q => q.Customer.lastName == Data.Customer_LastName);
}
if (Data.Customer_PhoneNumber != null)
{
query = query.Where(q => q.Customer.phoneNumber == Data.Customer_PhoneNumber);
}
// Order fields
if (Data.Order_CustID != null)
{
query = query.Where(q => q.Order.custID == Data.Order_CustID);
}
if (Data.Order_OrderID != null)
{
query = query.Where(q => q.Order.orderID == Data.Order_OrderID);
}
if (Data.Order_OrderDate != null)
{
query = query.Where(q => q.Order.orderDate == Data.Order_OrderDate);
}
if (Data.Order_PoNumber != null)
{
query = query.Where(q => q.Order.poNumber == Data.Order_PoNumber);
}
// Product fields
if (Data.Product_ProdID != null)
{
query = query.Where(q => q.Product.prodID == Data.Product_ProdID);
}
if (Data.Product_ProdName != null)
{
query = query.Where(q => q.Product.prodName == Data.Product_ProdName);
}
if (Data.Product_ProdWeight != null)
{
query = query.Where(q => q.Product.prodWeight == Data.Product_ProdWeight);
}
if (Data.Product_Price != null)
{
query = query.Where(q => q.Product.price == Data.Product_Price);
}
if (Data.Product_InStock != null)
{
query = query.Where(q => q.Product.inStock == Data.Product_InStock);
}
// Cart fields
if (Data.Cart_OrderID != null)
{
query = query.Where(q => q.Cart.orderID == Data.Cart_OrderID);
}
if (Data.Cart_ProdID != null)
{
query = query.Where(q => q.Cart.prodID == Data.Cart_ProdID);
}
if (Data.Cart_Quantity != null)
{
query = query.Where(q => q.Cart.quantity == Data.Cart_Quantity);
}
// Get the results
var hasCustomerField = (Data.Customer_CustID != null ||
Data.Customer_FirstName != null ||
Data.Customer_LastName != null ||
Data.Customer_PhoneNumber != null);
var hasProductField = (Data.Product_ProdID != null ||
Data.Product_ProdName != null ||
Data.Product_ProdWeight != null ||
Data.Product_Price != null ||
Data.Product_InStock != null);
var hasOrderField = (Data.Order_CustID != null ||
Data.Order_OrderDate != null ||
Data.Order_OrderID != null ||
Data.Order_PoNumber != null);
var hasCartField = (Data.Cart_OrderID != null ||
Data.Cart_ProdID != null ||
Data.Cart_Quantity != null);
var showOnlyCustomer = hasCustomerField && !hasProductField && !hasOrderField && !hasCartField;
var showOnlyProduct = !hasCustomerField && hasProductField && !hasOrderField && !hasCartField;
var showOrderAndCustomer = !hasProductField && hasOrderField && !hasCartField;
var showProductAndOrderAndCart = (hasProductField && hasOrderField) || hasCartField;
var searchResult = new SearchResult();
var customerFields = new string[]
{
"Customer_CustID",
"Customer_FirstName",
"Customer_LastName",
"Customer_PhoneNumber"
};
var productFields = new string[]
{
"Product_ProdID",
"Product_ProdName",
"Product_ProdWeight",
"Product_Price",
"Product_InStock"
};
var orderFields = new string[]
{
"Order_OrderID",
"Order_CustID",
"Order_OrderDate",
"Order_PoNumber"
};
var cartFields = new string[]
{
"Cart_ProdID",
"Cart_OrderID",
"Cart_Quantity"
};
if (showOnlyCustomer)
{
searchResult.Columns.AddRange(customerFields);
searchResult.Columns.AddRange(cartFields);
}
else if (showOnlyProduct)
{
searchResult.Columns.AddRange(productFields);
searchResult.Columns.AddRange(cartFields);
}
else if (showOrderAndCustomer)
{
searchResult.Columns.AddRange(customerFields);
searchResult.Columns.AddRange(orderFields);
searchResult.Columns.AddRange(cartFields);
}
else if (showProductAndOrderAndCart)
{
searchResult.Columns.AddRange(customerFields);
searchResult.Columns.AddRange(productFields);
searchResult.Columns.AddRange(orderFields);
searchResult.Columns.AddRange(cartFields);
}
else
{
searchResult.Columns.AddRange(customerFields);
searchResult.Columns.AddRange(orderFields);
searchResult.Columns.AddRange(productFields);
searchResult.Columns.AddRange(cartFields);
}
var results = query.Select(q => CreateServerServiceRequest(q.Customer, q.Order, q.Product, q.Cart)).ToList();
searchResult.Rows = results;
return(searchResult);
}
/// <summary>
/// Inserts data into the database
/// </summary>
public void Insert()
{
var context = new SoaDataContext();
Customer customer = null;
if (Data.Customer_LastName != null)
{
customer = new Customer();
customer.firstName = Data.Customer_FirstName;
customer.lastName = Data.Customer_LastName;
customer.phoneNumber = Data.Customer_PhoneNumber;
context.Customers.InsertOnSubmit(customer);
}
context.SubmitChanges();
Order order = null;
if (Data.Order_OrderDate != null)
{
order = new Order();
if (Data.Order_CustID == null)
{
throw new Exception("Please enter a CustID into the order table");
}
order.custID = (int)Data.Order_CustID;
order.orderDate = Data.Order_OrderDate;
order.poNumber = Data.Order_PoNumber;
context.Orders.InsertOnSubmit(order);
}
context.SubmitChanges();
Product product = null;
if (Data.Product_Price != null)
{
product = new Product();
product.prodName = Data.Product_ProdName;
product.price = (Data.Product_Price != null) ? (double)Data.Product_Price : 0;
product.prodWeight = (Data.Product_ProdWeight != null) ? (double)Data.Product_ProdWeight : 0;
product.inStock = (Data.Product_InStock != null) ? (byte)Data.Product_InStock : (byte)0;
context.Products.InsertOnSubmit(product);
}
context.SubmitChanges();
if (Data.Cart_Quantity != null)
{
var cart = new Cart();
if (order != null)
{
cart.orderID = order.orderID;
}
if (product != null)
{
cart.prodID = product.prodID;
}
cart.quantity = (Data.Cart_Quantity != null) ? (int)Data.Cart_Quantity : 0;
}
context.SubmitChanges();
}
/// <summary>
/// Deletes from the database
/// </summary>
public void Delete()
{
var context = new SoaDataContext();
if (Data.Customer_CustID != null)
{
var customer = (from c in context.Customers
where c.custID == Data.Customer_CustID && c.deleted == (byte)0
select c).FirstOrDefault();
if (customer == null)
{
throw new Exception(String.Format("Customer with ID {0} does not exist", Data.Customer_CustID));
}
else
{
// Soft delete
customer.deleted = (byte)1;
}
// Delete all the data for the customer
var orders = (from o in context.Orders
where o.custID == customer.custID && o.deleted == (byte)0
select o).ToArray();
foreach (var order in orders)
{
// Delete all the carts for the orders
foreach (var cart in order.Carts)
{
cart.deleted = (byte)1;
}
order.deleted = (byte)1;
}
}
if (Data.Order_OrderID != null)
{
var order = (from c in context.Orders
where c.orderID == Data.Order_OrderID && c.deleted == (byte)0
select c).FirstOrDefault();
if (order == null)
{
throw new Exception(String.Format("Order with ID {0} does not exist", Data.Order_OrderID));
}
else
{
// Soft delete
order.deleted = (byte)1;
}
// Delete all the carts for the orders
foreach (var cart in order.Carts)
{
cart.deleted = (byte)1;
}
}
if (Data.Product_ProdID != null)
{
var product = (from c in context.Products
where c.prodID == Data.Product_ProdID && c.deleted == (byte)0
select c).FirstOrDefault();
if (product == null)
{
throw new Exception(String.Format("Product with ID {0} does not exist", Data.Product_ProdID));
}
else
{
// Soft delete
product.deleted = (byte)1;
}
// Delete from carts from product
foreach (var cart in product.Carts)
{
cart.deleted = (byte)1;
}
}
if (Data.Cart_OrderID != null && Data.Cart_ProdID != null)
{
var cart = (from c in context.Carts
where c.orderID == Data.Cart_OrderID && c.prodID == Data.Cart_ProdID && c.deleted == (byte)0
select c).FirstOrDefault();
if (cart == null)
{
throw new Exception(String.Format("Cart with ProdID {0} and OrderID {1} does not exist",
Data.Cart_ProdID, Data.Cart_OrderID));
}
else
{
// Soft delete
cart.deleted = (byte)1;
}
}
context.SubmitChanges();
}
/// <summary>
/// Updates the database
/// </summary>
public void Update()
{
var context = new SoaDataContext();
if (Data.Customer_CustID != null)
{
var customer = (from c in context.Customers
where c.custID == Data.Customer_CustID && c.deleted == (byte)0
select c).FirstOrDefault();
if (customer == null)
{
throw new Exception(String.Format("Customer with ID {0} does not exist", Data.Customer_CustID));
}
else
{
// Update with new fields
customer.firstName = Data.Customer_FirstName;
customer.lastName = Data.Customer_LastName;
customer.firstName = Data.Customer_FirstName;
customer.phoneNumber = Data.Customer_PhoneNumber;
}
}
if (Data.Order_OrderID != null)
{
var order = (from c in context.Orders
where c.orderID == Data.Order_OrderID && c.deleted == (byte)0
select c).FirstOrDefault();
if (order == null)
{
throw new Exception(String.Format("Order with ID {0} does not exist", Data.Order_OrderID));
}
else
{
// Update with new fields
order.poNumber = Data.Order_PoNumber;
order.orderDate = Data.Order_OrderDate;
order.custID = (int)Data.Order_CustID;
}
}
if (Data.Product_ProdID != null)
{
var product = (from c in context.Products
where c.prodID == Data.Product_ProdID && c.deleted == (byte)0
select c).FirstOrDefault();
if (product == null)
{
throw new Exception(String.Format("Product with ID {0} does not exist", Data.Product_ProdID));
}
else
{
// Update with new fields
product.prodName = Data.Product_ProdName;
product.prodWeight = (Data.Product_ProdWeight != null) ? (double)Data.Product_ProdWeight : 0;
product.price = (Data.Product_Price != null) ? (double)Data.Product_Price : 0;
product.inStock = (Data.Product_InStock != null) ? (byte)Data.Product_InStock : (byte)0;
}
}
if (Data.Cart_OrderID != null && Data.Cart_ProdID != null)
{
var cart = (from c in context.Carts
where c.orderID == Data.Cart_OrderID && c.prodID == Data.Cart_ProdID && c.deleted == (byte)0
select c).FirstOrDefault();
if (cart == null)
{
throw new Exception(String.Format("Cart with ProdID {0} and OrderID {1} does not exist",
Data.Cart_ProdID, Data.Cart_OrderID));
}
else
{
// Update with new fields
cart.quantity = (Data.Cart_Quantity != null) ? (int)Data.Cart_Quantity : 0;
}
}
context.SubmitChanges();
}