public bool addUser(String employeeName, String userName, String password, String designation)
{
DatabaseOperations operations = new DatabaseOperations();
String addUserQuery = "INSERT INTO UserDetails (UserName, Password) VALUES ('" + userName + "','" + password + "')";
int lastEmployeeID;
if (operations.executeSelectQuery("SELECT TOP 1 EmployeeID FROM EmployeeDetails ORDER BY EmployeeID DESC").Rows.Count > 0)
{
lastEmployeeID = int.Parse(operations.executeSelectQuery("SELECT TOP 1 EmployeeID FROM EmployeeDetails ORDER BY EmployeeID DESC").Rows[0][0].ToString());
}
else
{
lastEmployeeID = 101;
}
int designationID = int.Parse(operations.executeSelectQuery("SELECT DesignationID FROM Designation WHERE DesignationName='" + designation.ToLower() + "'").Rows[0][0].ToString());
String addEmployeeQuery = "INSERT INTO EmployeeDetails VALUES(" + (lastEmployeeID + 1) + "," + designationID + ",'" + employeeName + "','" + userName + "')";
try
{
if (operations.executeInsUpdDelQuery(addUserQuery) == 1 && operations.executeInsUpdDelQuery(addEmployeeQuery) == 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
throw ex;
}
}
public bool addNewOrder(Order newOrder)
{
DatabaseOperations operations = new DatabaseOperations();
try
{
int paperTypeID = int.Parse(operations.executeSelectQuery("SELECT PaperTypeID FROM PaperType WHERE PaperTypeName='" + newOrder.PaperType + "'").Rows[0][0].ToString());
int statusTypeID = int.Parse(operations.executeSelectQuery("SELECT OrderStatusID FROM OrderStatus WHERE OrderStatusName='" + newOrder.OrderStatus + "'").Rows[0][0].ToString());
DataTable customerDetails = operations.executeSelectQuery("SELECT * FROM CustomerDetails WHERE CustomerName='" + newOrder.Customer.Name + "' AND CustomerContactNumber=" + newOrder.Customer.ContactNumber);
if (customerDetails.Rows.Count == 0)
{
DataTable allCustomerDetails = operations.executeSelectQuery("SELECT * FROM CustomerDetails");
if (allCustomerDetails.Rows.Count == 0)
{
operations.executeInsUpdDelQuery("INSERT INTO CustomerDetails (CustomerID, CustomerName, CustomerAddress, CustomerContactNumber) VALUES (" + 1 + ",'" + newOrder.Customer.Name + "','" + newOrder.Customer.Address + "," + newOrder.Customer.ContactNumber + ")");
newOrder.Customer.ID = 1;
}
else
{
int lastCustomerID = (int)allCustomerDetails.Rows[allCustomerDetails.Rows.Count - 1][0];
operations.executeInsUpdDelQuery("INSERT INTO CustomerDetails (CustomerID, CustomerName, CustomerAddress, CustomerContactNumber) VALUES (" + (lastCustomerID + 1) + ",'" + newOrder.Customer.Name + "','" + newOrder.Customer.Address + "'," + newOrder.Customer.ContactNumber + ")");
newOrder.Customer.ID = lastCustomerID + 1;
}
int i = operations.executeInsUpdDelQuery("INSERT INTO OrderDetails (OrderID, CustomerID, DesignID, PaperTypeID, OrderStatusID, Quantity, Size, DeliveryDate, PerProductCost, AdvancePayment, OrderDelivered, Color) VALUES (" + newOrder.OrderID + "," + newOrder.Customer.ID + ",'" + newOrder.DesignID + "'," + paperTypeID + "," + statusTypeID + "," + newOrder.Quantity + ",'" + newOrder.Size + "','" + newOrder.DeliveryDate + "'," + newOrder.UnitPrice + "," + newOrder.AdvancePayment + ",0,'" + newOrder.Color + "')");
int j = operations.executeUpdImageQuery("OrderDetails", "FinalDesignToBePrinted", newOrder.FinalizedDesign, "OrderID", newOrder.OrderID.ToString());
if (i == 1 && j == 1)
{
return(true);
}
else
{
return(false);
}
}
else
{
newOrder.Customer.ID = int.Parse(customerDetails.Rows[0][0].ToString());
int i = operations.executeInsUpdDelQuery("INSERT INTO OrderDetails (OrderID, CustomerID, DesignID, PaperTypeID, OrderStatusID, Quantity, Size, DeliveryDate, PerProductCost, AdvancePayment, OrderDelivered, Color) VALUES (" + newOrder.OrderID + "," + newOrder.Customer.ID + ",'" + newOrder.DesignID + "'," + paperTypeID + "," + statusTypeID + "," + newOrder.Quantity + ",'" + newOrder.Size + "','" + newOrder.DeliveryDate + "'," + newOrder.UnitPrice + "," + newOrder.AdvancePayment + ",0,'" + newOrder.Color + "')");
int j = operations.executeUpdImageQuery("OrderDetails", "FinalDesignToBePrinted", newOrder.FinalizedDesign, "OrderID", newOrder.OrderID.ToString());
if (i == 1 && j == 1)
{
return(true);
}
else
{
return(false);
}
}
}
catch (Exception ex)
{
return(false);
}
}
public bool isOrderDelivered(int orderID)
{
DatabaseOperations operations = new DatabaseOperations();
int del = int.Parse(operations.executeSelectQuery("SELECT OrderStatusID FROM OrderStatus WHERE OrderStatusName='Delivered'").Rows[0][0].ToString());
if (int.Parse(operations.executeSelectQuery("SELECT OrderStatusID FROM OrderDetails WHERE OrderID=" + orderID).Rows[0][0].ToString()) == del)
{
return(true);
}
else
{
return(false);
}
}
public DataTable getAllProducts(int categoryID)
{
DatabaseOperations operations = new DatabaseOperations();
DataTable products = operations.executeSelectQuery("SELECT * FROM Product WHERE Product.CategoryID=" + categoryID);
return(products);
}
public DataSet getAllOrders()
{
DatabaseOperations operations = new DatabaseOperations();
DataTable orderDetails = operations.executeSelectQuery("SELECT OrderDetails.CustomerID,OrderDetails.OrderID,OrderDetails.DeliveryDate,OrderDetails.Quantity,OrderStatus.OrderStatusName,CustomerDetails.CustomerName FROM OrderStatus,OrderDetails,CustomerDetails WHERE OrderStatus.OrderStatusID=OrderDetails.OrderStatusID and CustomerDetails.CustomerID=OrderDetails.CustomerID").Copy();
orderDetails.TableName = "order";
DataTable status = operations.executeSelectQuery("Select OrderStatusName from OrderStatus").Copy();
status.TableName = "status";
DataSet ds = new DataSet();
ds.Tables.Add(orderDetails);
ds.Tables.Add(status);
//CustomerDetails.CustomerName FROM OrderDetails,CustomerDetails where CustomerDetails.CustomerID=OrderDetails.CustomerID'");
return(ds);
}
public DataTable getAllCategories()
{
DatabaseOperations operations = new DatabaseOperations();
DataTable categories = operations.executeSelectQuery("SELECT * FROM Category");
return(categories);
}
public DataTable remainingPayments(int orderID)
{
DatabaseOperations operations = new DatabaseOperations();
return(operations.executeSelectQuery("Select OrderDetails.OrderID, CustomerDetails.CustomerName, CustomerDetails.CustomerContactNumber, OrderDetails.DeliveryDate, OrderDetails.AdvancePayment, ((OrderDetails.Quantity * OrderDetails.PerProductCost)- OrderDetails.AdvancePayment) AS RemainingPayment, (OrderDetails.Quantity*OrderDetails.PerProductCost) AS TotalCost, OrderStatus.OrderStatusName FROM OrderDetails, CustomerDetails, OrderStatus WHERE OrderDetails.CustomerID=CustomerDetails.CustomerID AND OrderStatus.OrderStatusID=OrderDetails.OrderStatusID AND OrderDetails.OrderID=" + orderID));
}
public bool editDesign(Design d)
{
DatabaseOperations operations = new DatabaseOperations();
int paperTypeID = int.Parse(operations.executeSelectQuery("SELECT PaperTypeID FROM PaperType WHERE PaperType.PaperTypeName='" + d.DesignPaperType + "'").Rows[0][0].ToString());
return(operations.executeInsUpdDelQuery("UPDATE Design SET DesignName='" + d.DesignName + "', Size='" + d.DesignSize + "', PaperTypeID=" + paperTypeID + " WHERE DesignID='" + d.DesignID + "'") == 1);
}
public DataTable getAllOrders()
{
DatabaseOperations operations = new DatabaseOperations();
DataTable orderDetails = operations.executeSelectQuery("SELECT OrderDetails.OrderID, CustomerDetails.CustomerName, CustomerDetails.CustomerContactNumber, OrderDetails.DeliveryDate,OrderDetails.Quantity, Category.CategoryName, OrderStatus.OrderStatusName FROM OrderStatus,CustomerDetails,OrderDetails, Category, Product, Design WHERE OrderStatus.OrderStatusID=OrderDetails.OrderStatusID and OrderDetails.CustomerID=CustomerDetails.CustomerID AND OrderStatus.OrderStatusName!='Delivered' AND Category.CategoryID = Product.CategoryID AND Product.ProductID = Design.ProductID AND Design.DesignID = OrderDetails.DesignID");
return(orderDetails);
}
public bool changePassword(String userName, String oldPassword, string newPassword)
{
try
{
DatabaseOperations operations = new DatabaseOperations();
DataTable users = new DataTable();
users = operations.executeSelectQuery("Select * from UserDetails where UserName='******' AND Password='******'");
if (users.Rows.Count == 1)
{
if (operations.executeInsUpdDelQuery("UPDATE UserDetails SET Password='******' WHERE UserName='******'") == 1)
{
return(true);
}
else
{
return(false);
}
}
else
{
return(false);
}
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable getMonthlyReport(string productName, int month, int year)
{
DatabaseOperations operations = new DatabaseOperations();
int productCode = int.Parse(operations.executeSelectQuery("SELECT ProductID FROM Product WHERE ProductName='" + productName + "'").Rows[0][0].ToString());
DataTable dt = null;
return(dt);
}
public LoginStatus isValidUser(String username, String password)
{
Employee employee = null;
DatabaseOperations operations = new DatabaseOperations();
DataTable users = new DataTable();
users = operations.executeSelectQuery("Select * from UserDetails where UserName='******' AND Password='******'");
if (users.Rows.Count == 1)
{
DataTable userType = new DataTable();
userType = operations.executeSelectQuery("Select DesignationName FROM Designation where DesignationID =(Select DesignationID from EmployeeDetails where UserName='******')");
if (userType.Rows[0][0].ToString().Equals("Admin"))
{
employee = new Admin();
}
else if (userType.Rows[0][0].ToString().Equals("Receptionist"))
{
employee = new Receptionist();
}
else if (userType.Rows[0][0].ToString().Equals("Designer"))
{
employee = new Designer();
}
else if (userType.Rows[0][0].ToString().Equals("Manufacturing Worker"))
{
employee = new ManufacturingWorker();
}
employee.EmployeeName = username;
if (username.Equals(password))
{
return(new LoginStatus(true, true, employee));
}
else
{
return(new LoginStatus(false, true, employee));
}
}
else
{
return(new LoginStatus(false, false, employee));
}
}
public DataTable searchOrder(String customerName)
{
DatabaseOperations operations = new DatabaseOperations();
DataTable customerDetails = operations.executeSelectQuery("SELECT CustomerID FROM CustomerDetails WHERE CustomerName=" + customerName);
if (customerDetails.Rows.Count > 0)
{
DataTable orderDetails = operations.executeSelectQuery("SELECT * FROM OrderDetails WHERE CustomerID=" + customerDetails.Rows[0][0]);
if (orderDetails.Rows.Count > 0)
{
return(orderDetails);
}
else
{
return(null);
}
}
return(null);
}
public DataTable searchOrder(String customerName)
{
DatabaseOperations operations = new DatabaseOperations();
DataTable customerDetails = operations.executeSelectQuery("SELECT CustomerID FROM CustomerDetails WHERE CustomerName LIKE '" + customerName + "%'");
if (customerDetails.Rows.Count > 0)
{
DataTable orderDetails = operations.executeSelectQuery("SELECT OrderDetails.OrderID, CustomerDetails.CustomerName, CustomerDetails.CustomerContactNumber, OrderDetails.DeliveryDate, OrderDetails.Quantity, Category.CategoryName, OrderStatus.OrderStatusName FROM OrderStatus,CustomerDetails,OrderDetails, Category, Product, Design WHERE OrderStatus.OrderStatusID=OrderDetails.OrderStatusID and OrderDetails.CustomerID=CustomerDetails.CustomerID AND Category.CategoryID = Product.CategoryID AND Product.ProductID = Design.ProductID AND Design.DesignID = OrderDetails.DesignID AND CustomerDetails.CustomerName LIKE '" + customerName + "%'");
if (orderDetails.Rows.Count > 0)
{
return(orderDetails);
}
else
{
return(null);
}
}
return(null);
}
public List <Design> getDesignByName(string designName)
{
DatabaseOperations operations = new DatabaseOperations();
DataTable designs = operations.executeSelectQuery("SELECT * FROM Design WHERE Design.DesignName LIKE '" + designName + "%'");
List <Design> designList = new List <Design>();
foreach (DataRow dr in designs.Rows)
{
Design d = new Design();
d.DesignID = dr[0].ToString();
d.DesignName = dr[1].ToString();
d.DesignFile = (byte[])dr[2];
string paperTypeName = operations.executeSelectQuery("SELECT PaperTypeName FROM PaperType WHERE PaperType.PaperTypeID=" + dr[5].ToString()).Rows[0][0].ToString();
d.DesignPaperType = paperTypeName;
d.DesignSize = dr[6].ToString();
designList.Add(d);
}
return(designList);
}
public List <Design> getAllDesigns(string productName)
{
DatabaseOperations operations = new DatabaseOperations();
int productID = int.Parse(operations.executeSelectQuery("SELECT ProductID FROM Product WHERE Product.ProductName='" + productName + "'").Rows[0][0].ToString());
DataTable designs = operations.executeSelectQuery("SELECT * FROM Design WHERE Design.ProductID=" + productID);
List <Design> designList = new List <Design>();
foreach (DataRow dr in designs.Rows)
{
Design d = new Design();
d.DesignID = dr[0].ToString();
d.DesignName = dr[1].ToString();
d.DesignFile = (byte[])dr[2];
string paperTypeName = operations.executeSelectQuery("SELECT PaperTypeName FROM PaperType WHERE PaperType.PaperTypeID=" + dr[5].ToString()).Rows[0][0].ToString();
d.DesignPaperType = paperTypeName;
d.DesignSize = dr[6].ToString();
designList.Add(d);
}
return(designList);
}
public bool addProduct(string categoryName, string productName)
{
DatabaseOperations operations = new DatabaseOperations();
int categoryID = int.Parse(operations.executeSelectQuery("SELECT CategoryID FROM Category WHERE CategoryName='" + categoryName + "'").Rows[0][0].ToString());
DataTable productIDs = operations.executeSelectQuery("SELECT ProductID FROM Product");
int lastProductID = 0;
if (productIDs.Rows.Count > 0)
{
lastProductID = int.Parse(productIDs.Rows[productIDs.Rows.Count - 1][0].ToString());
}
if (operations.executeInsUpdDelQuery("INSERT INTO Product VALUES(" + (lastProductID + 1) + "," + categoryID + ",'" + productName + "')") == 1)
{
return(true);
}
else
{
return(false);
}
}
public bool editOrder(Order order)
{
DatabaseOperations operations = new DatabaseOperations();
int paperTypeID = int.Parse(operations.executeSelectQuery("SELECT PaperTypeID FROM PaperType WHERE PaperTypeName='" + order.PaperType + "'").Rows[0][0].ToString());
int statusTypeID = int.Parse(operations.executeSelectQuery("SELECT OrderStatusID FROM OrderStatus WHERE OrderStatusName='" + order.OrderStatus + "'").Rows[0][0].ToString());
int i = operations.executeInsUpdDelQuery("UPDATE OrderDetails SET DesignID='" + order.DesignID + "', PaperTypeID=" + paperTypeID + ", Size='" + order.Size + "', OrderStatusID=" + statusTypeID + ", Quantity=" + order.Quantity + ", DeliveryDate='" + order.DeliveryDate.ToString() + "', PerProductCost=" + order.UnitPrice + ", AdvancePayment=" + order.AdvancePayment + " WHERE OrderID=" + order.OrderID);
int j = 1;
if (order.FinalizedDesign != "Click Here")
{
j = operations.executeUpdImageQuery("OrderDetails", "FinalDesignToBePrinted", order.FinalizedDesign, "OrderID", order.OrderID.ToString());
}
if (i == 1 && j == 1)
{
return(true);
}
else
{
return(false);
}
}
public Order getOrderByID(int orderID)
{
DatabaseOperations operations = new DatabaseOperations();
DataTable orderDetails = operations.executeSelectQuery("SELECT * FROM OrderDetails WHERE OrderID=" + orderID);
Order order = new Order();
order.OrderID = int.Parse(orderDetails.Rows[0][0].ToString());
order.DesignID = orderDetails.Rows[0][2].ToString();
order.Quantity = int.Parse(orderDetails.Rows[0][3].ToString());
order.Size = orderDetails.Rows[0][6].ToString();
order.DesignFile = (byte[])orderDetails.Rows[0][7];
order.DeliveryDate = ((DateTime)orderDetails.Rows[0][8]);
order.UnitPrice = int.Parse(orderDetails.Rows[0][9].ToString());
order.AdvancePayment = int.Parse(orderDetails.Rows[0][10].ToString());
DataTable customerDetails = operations.executeSelectQuery("SELECT * FROM CustomerDetails WHERE CustomerID=" + orderDetails.Rows[0][1].ToString());
order.Customer = new Customer(int.Parse(customerDetails.Rows[0][0].ToString()), customerDetails.Rows[0][1].ToString(), customerDetails.Rows[0][2].ToString(), long.Parse(customerDetails.Rows[0][3].ToString()));
order.DesigName = operations.executeSelectQuery("Select DesignName from Design WHERE DesignID='" + order.DesignID + "'").Rows[0][0].ToString();
order.PaperType = operations.executeSelectQuery("Select PaperTypeName from PaperType WHERE PaperTypeID=" + orderDetails.Rows[0][4].ToString()).Rows[0][0].ToString();
order.OrderStatus = operations.executeSelectQuery("Select OrderStatusName from OrderStatus WHERE OrderStatusID=" + orderDetails.Rows[0][4].ToString()).Rows[0][0].ToString();
return(order);
}
public bool orderExists(int orderID)
{
DatabaseOperations operations = new DatabaseOperations();
if (operations.executeSelectQuery("SELECT * FROM OrderDetails WHERE OrderID=" + orderID).Rows.Count == 1)
{
return(true);
}
else
{
return(false);
}
}
public DataTable searchOrder(int orderID)
{
DatabaseOperations operations = new DatabaseOperations();
DataTable orderDetails = operations.executeSelectQuery("SELECT * FROM OrderDetails WHERE OrderID=" + orderID);
if (orderDetails.Rows.Count > 0)
{
return(orderDetails);
}
else
{
return(null);
}
}
public DataTable searchOrder(DateTime deliveryDate)
{
DatabaseOperations operations = new DatabaseOperations();
DataTable orderDetails = operations.executeSelectQuery("SELECT * FROM OrderDetails WHERE DeliverDate='" + deliveryDate + "'");
if (orderDetails.Rows.Count > 0)
{
return(orderDetails);
}
else
{
return(null);
}
}
public bool saveDesign(Design d)
{
try
{
DatabaseOperations operations = new DatabaseOperations();
int paperTypeID = int.Parse(operations.executeSelectQuery("SELECT PaperTypeID FROM PaperType WHERE PaperType.PaperTypeName='" + d.DesignPaperType + "'").Rows[0][0].ToString());
int productID = int.Parse(operations.executeSelectQuery("SELECT ProductID FROM Product WHERE Product.ProductName='" + d.ProductName + "'").Rows[0][0].ToString());
int i = operations.executeInsUpdDelQuery("INSERT INTO Design (DesignID, DesignName, ProductID, PaperTypeID, Size) VALUES('" + d.DesignID + "','" + d.DesignName + "'," + productID + "," + paperTypeID + ",'" + d.DesignSize + "')");
int j = operations.executeUpdImageQuery("Design", "DesignFile", d.DesignFilePath, "DesignID", d.DesignID);
if (i == 1 && j == 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
return(false);
}
}
public bool addCategory(string categoryName)
{
DatabaseOperations operations = new DatabaseOperations();
DataTable categoryIDs = operations.executeSelectQuery("SELECT CategoryID FROM Category");
int lastcategoryID = 0;
if (categoryIDs.Rows.Count > 0)
{
lastcategoryID = int.Parse(categoryIDs.Rows[categoryIDs.Rows.Count - 1][0].ToString());
}
if (operations.executeInsUpdDelQuery("INSERT INTO Category VALUES(" + (lastcategoryID + 1) + ",'" + categoryName + "')") == 1)
{
return(true);
}
else
{
return(false);
}
}
public bool setOrderStatus(int orderID, string status)
{
DatabaseOperations operations = new DatabaseOperations();
try
{
int statusID = int.Parse(operations.executeSelectQuery("SELECT OrderStatusID FROM OrderStatus WHERE OrderStatusName='" + status + "'").Rows[0][0].ToString());
if (operations.executeInsUpdDelQuery("UPDATE OrderDetails SET OrderStatusID=" + statusID + " WHERE OrderID=" + orderID) == 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
return(false);
}
}