public InvoiceCreator(OrdersForm ordersForm, Customer customer, Order order, List<OrderedStock> orderedStock)
{
this.ordersForm = ordersForm;
this.customer = customer;
this.order = order;
this.orderedStock = orderedStock;
grandTotal = 0;
}
/*Precondition:
Postcondition: When user selects new order, information is updated to display the selected order*/
private void dataGridView1_SelectionChanged(object sender, EventArgs e)
{
int currRow = dataGridView1.CurrentCell.RowIndex;
if (currRow < customersOrders.Count)
{
currOrder = customersOrders[currRow];
selectedOrderUpdated();
}
}
/*Precondition:
Postcondition: Starts a search for orders depending on what search boxes have been filled in*/
private void startSearch()
{
//Clear all the text boxes out for a new search
clearForNewSearch();
if (boxOrderSearchID.Text != "")
{
int orderID = Convert.ToInt32(boxOrderSearchID.Text);
//Run search
currOrder = dbManager.searchOrders(orderID);
//Check order wasn't null
if (currOrder != null)
{
//Get the stock that was ordered for this order
currOrderedStock = dbManager.searchOrderedStock(orderID);
//Autofill into text boxes the found order
boxOrderID.Text = currOrder.orderID.ToString();
boxOrderRef.Text = currOrder.orderReference;
boxProgress.Text = currOrder.progress;
boxInvoiceDate.Text = currOrder.invoiceDate.ToString("d/MM/yyyy");
boxFreight.Text = "$" + String.Format("{0:0.00}", currOrder.freightCost);
boxComments.Text = currOrder.comments;
//Search for customer attatched to the order
currCustomer = dbManager.searchCustomers(currOrder.customerID);
//Use customers data if the customer was found
if (currCustomer != null)
{
boxCustID.Text = currCustomer.custID.ToString();
boxFirstName.Text = currCustomer.firstName;
boxLastName.Text = currCustomer.lastName;
boxInstitution.Text = currCustomer.institution;
boxPostcode.Text = currCustomer.postCode;
boxAdd1.Text = currCustomer.address1;
boxAdd2.Text = currCustomer.address2;
boxAdd3.Text = currCustomer.address3;
boxCountry.Text = currCustomer.country;
}
else
{
//Use the default data that was stored in the order
boxFirstName.Text = currOrder.firstName + " " + currOrder.lastName;
}
//Loop over and display all of the ordered stock for the order
foreach (OrderedStock o in currOrderedStock)
{
dataGridView1.Rows.Add(o.quantity, o.author, o.title, "$" + String.Format("{0:0.00}", o.price), o.bookID, "$" + String.Format("{0:0.00}", o.discount));
}
}
else
{
if (orderID == -1)
boxOrderSearchID.Text = "";
else
MessageBox.Show("Order not found");
}
}
}
/*Precondition:
Postcondition: Does setup and initializes everything needed*/
private void setup()
{
//Initialize globals
dbManager = new DatabaseManager();
allOrders = new List<Order>();
allOrderedStock = new List<OrderedStock>();
currOrderedStock = new List<OrderedStock>();
newOrderedStock = new List<OrderedStock>();
fileManager = new FileManager();
currCustomer = null;
currOrder = null;
canEdit = false;
boxOrderSearchID.Select();
//Set up column widths
DataGridViewColumn colQuantity = dataGridView1.Columns[0];
colQuantity.Width = 50;
DataGridViewColumn colAuthor = dataGridView1.Columns[1];
colAuthor.Width = 187;
DataGridViewColumn colTitle = dataGridView1.Columns[2];
colTitle.Width = 270;
DataGridViewColumn colPrice = dataGridView1.Columns[3];
colPrice.Width = 75;
DataGridViewColumn colBookID = dataGridView1.Columns[4];
colBookID.Width = 75;
DataGridViewColumn colDiscount = dataGridView1.Columns[5];
colDiscount.Width = 75;
loadNewestOrder();
//If this form was opened through customers orders then remove unneccessary controls and adjust form
if (mainMenu == null)
{
labOrderID.Visible = false;
boxOrderSearchID.Visible = false;
btnSearch.Enabled = false;
btnSearch.Visible = false;
btnNewestOrder.Enabled = false;
btnNewestOrder.Visible = false;
btnNewOrder.Enabled = false;
btnNewOrder.Visible = false;
btnPrev.Enabled = false;
btnPrev.Visible = false;
btnNext.Enabled = false;
btnNext.Visible = false;
btnMainMenu.Text = "Close";
groupBox1.Left = groupBox1.Left - 50;
btnAddBook.Left = btnAddBook.Left - 50;
btnMainMenu.Left = btnMainMenu.Left - 50;
btnCreateInvoice.Left = btnCreateInvoice.Left - 40;
btnBigMailingLabel.Left = btnBigMailingLabel.Left - 40;
btnSmallMailingLabel.Left = btnSmallMailingLabel.Left - 40;
labMailingLabels.Left = labMailingLabels.Left - 40;
Width = Width - 50;
}
}
/*Precondition:
Postcondition: Loops through list of stock passed in, and inserts them into SQLite DB*/
public void insertOrder(Order newOrder)
{
//Check to see if orders table exists
if (checkForTable("Orders"))
{
string firstName = SyntaxHelper.escapeSingleQuotes(newOrder.firstName);
string lastName = SyntaxHelper.escapeSingleQuotes(newOrder.lastName);
string institution = SyntaxHelper.escapeSingleQuotes(newOrder.institution);
string postcode = SyntaxHelper.escapeSingleQuotes(newOrder.postcode);
string orderReference = SyntaxHelper.escapeSingleQuotes(newOrder.orderReference);
string progress = SyntaxHelper.escapeSingleQuotes(newOrder.progress);
double freightCost = newOrder.freightCost;
int invoiceNo = newOrder.invoiceNo;
DateTime invoiceDate = newOrder.invoiceDate;
string comments = SyntaxHelper.escapeSingleQuotes(newOrder.comments);
int customerID = newOrder.customerID;
int orderID = newOrder.orderID;
//Open DB
dbConnection.Open();
string orderInsert = "";
//Build insert command
orderInsert = "INSERT INTO Orders VALUES(null, '" + firstName + "', '" + lastName + "', '" + institution + "', '" + postcode + "', '" +
orderReference + "', '" + progress + "', '" + freightCost + "', '" + invoiceNo + "', '" + invoiceDate.ToString("yyyy-MM-dd HH:mm:ss") + "', '" + comments + "', '" +
customerID + "')";
SQLiteCommand insertCommand = new SQLiteCommand(orderInsert, dbConnection);
insertCommand.ExecuteNonQuery();
dbConnection.Close();
}
}
/*Precondition:
Postcondition: Sets up everything that needs to be done when form is initialized*/
private void setup()
{
dbManager = new DatabaseManager();
customersOrders = new List<Order>();
labCustomerName.Text = "Orders for: " + currCustomer.firstName + " " + currCustomer.lastName;
//Set up column widths
DataGridViewColumn colOrderID = dataGridView1.Columns[0];
colOrderID.Width = 100;
DataGridViewColumn colRef = dataGridView1.Columns[1];
colRef.Width = 100;
DataGridViewColumn colInvoiceDate = dataGridView1.Columns[2];
colInvoiceDate.Width = 100;
//Set column widths of 2nd datagridview
DataGridViewColumn colQuantity = dataGridView2.Columns[0];
colQuantity.Width = 50;
DataGridViewColumn colAuthor = dataGridView2.Columns[1];
colAuthor.Width = 187;
DataGridViewColumn colTitle = dataGridView2.Columns[2];
colTitle.Width = 270;
DataGridViewColumn colPrice = dataGridView2.Columns[3];
colPrice.Width = 75;
DataGridViewColumn colBookID = dataGridView2.Columns[4];
colBookID.Width = 75;
DataGridViewColumn colDiscount = dataGridView2.Columns[5];
colDiscount.Width = 75;
//Find the customers orders
customersOrders = dbManager.searchCustomersOrders(currCustomer.custID);
//Set up first order from customer if they have any orders
if (customersOrders.Count > 0)
{
currOrder = customersOrders[0];
foreach (Order o in customersOrders)
{
dataGridView1.Rows.Add(o.orderID, o.orderReference, o.invoiceDate);
}
selectedOrderUpdated();
}
}
/*Precondition: month needs to be a number e.g march = "03", year = "2016"
Postcondition: Returns a list of Orders that were made in the passed in month and year */
public List<Order> getOrdersByMonth(string month, string year)
{
List<Order> foundOrders = new List<Order>();
//Check to make sure orders table exists
if (checkForTable("Orders"))
{
dbConnection.Open();
//Days in months needs to be incremented by 1 to get the full range from the database
int daysInMonth = DateTime.DaysInMonth(Convert.ToInt32(year), Convert.ToInt32(month));
daysInMonth += 1;
string day1 = "01";
string day2 = daysInMonth.ToString();
//Execute SQL query
string sql = "SELECT * FROM Orders WHERE invoiceDate BETWEEN '" + year + "-" + month + "-" + day1 + "' AND '" + year + "-" + month + "-" + day2 + "'";
SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
//Loop over and store results
while (reader.Read())
{
string date = reader[9].ToString();
string[] splitOnSpace = date.Split(' ');
string[] splitOnSlash = splitOnSpace[0].Split('/');
//Convert date into datetime
DateTime currDate = new DateTime(Convert.ToInt32(splitOnSlash[2]), Convert.ToInt32(splitOnSlash[1]), Convert.ToInt32(splitOnSlash[0]));
Order foundOrder = new Order(Convert.ToInt32(reader[0]), reader[1].ToString(), reader[2].ToString(), reader[3].ToString(), reader[4].ToString(), reader[5].ToString(), reader[6].ToString(),
Convert.ToDouble(reader[7]), Convert.ToInt32(reader[8]), currDate, reader[10].ToString(), Convert.ToInt32(reader[11]));
foundOrders.Add(foundOrder);
}
dbConnection.Close();
}
return foundOrders;
}
/*Precondition:
Postcondition: Returns a list of all the orders */
public List<Order> getAllOrders()
{
List<Order> foundOrders = new List<Order>();
//Check to make sure orders table exists
if (checkForTable("Orders"))
{
dbConnection.Open();
//Execute SQL query
string sql = "SELECT * FROM Orders";
SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
//Loop over and store results
while (reader.Read())
{
string date = reader[9].ToString();
string[] splitOnSpace = date.Split(' ');
string[] splitOnSlash = splitOnSpace[0].Split('/');
DateTime currDate = new DateTime(Convert.ToInt32(splitOnSlash[2]), Convert.ToInt32(splitOnSlash[1]), Convert.ToInt32(splitOnSlash[0]));
Order currOrder = new Order(Convert.ToInt32(reader[0]), reader[1].ToString(), reader[2].ToString(), reader[3].ToString(), reader[4].ToString(), reader[5].ToString(), reader[6].ToString(),
Convert.ToDouble(reader[7]), Convert.ToInt32(reader[8]), currDate, reader[10].ToString(), Convert.ToInt32(reader[11]));
foundOrders.Add(currOrder);
}
dbConnection.Close();
}
//Return results
return foundOrders;
}
/*Precondition:
Postcondition: Updates the passed in order details, new details already added onto the order, use the ID to update*/
public void updateOrder(Order order)
{
if (checkForTable("Orders"))
{
string firstName = SyntaxHelper.escapeSingleQuotes(order.firstName);
string lastName = SyntaxHelper.escapeSingleQuotes(order.lastName);
string institution = SyntaxHelper.escapeSingleQuotes(order.institution);
string postcode = SyntaxHelper.escapeSingleQuotes(order.postcode);
string orderReference = SyntaxHelper.escapeSingleQuotes(order.orderReference);
string progress = SyntaxHelper.escapeSingleQuotes(order.progress);
double freightCost = order.freightCost;
int invoiceNo = order.invoiceNo;
DateTime invoiceDate = order.invoiceDate;
string comments = SyntaxHelper.escapeSingleQuotes(order.comments);
int customerID = order.customerID;
int orderID = order.orderID;
//Apostrophies cause program to crash
string updateQuery = "UPDATE Orders SET customerFirstName = '" + firstName + "', customerLastName = '" + lastName + "', institution = '" + institution +
"', postcode = '" + postcode + "', orderReference = '" + orderReference + "', progress = '" + progress + "', freightCost = '" + freightCost +
"', invoice = '" + invoiceNo + "', invoiceDate = '" + invoiceDate.ToString("yyyy-MM-dd HH:mm:ss") + "', comments = '" + comments + "', customerID = '" + customerID + "' WHERE orderID = " + orderID;
dbConnection.Open();
SQLiteCommand updateCommand = new SQLiteCommand(updateQuery, dbConnection);
updateCommand.ExecuteNonQuery();
dbConnection.Close();
}
}
/*Precondition:
Postcondition: Returns a list of orderedStock from the orderedBooks and data in datagrid */
private List<OrderedStock> createOrderedStock(Order order)
{
List<OrderedStock> newOrderedStock = new List<OrderedStock>();
//Get invoice/orderID for current order
int nextOrderIDAndInvoiceNo = dbManager.getNextOrderID();
int currRowIndex = 0;
//Loop over all the books selected and create orderedStock from them
foreach (Stock s in orderedBooks)
{
//Get quantity, price and discount from the datagrid
int quantity = Convert.ToInt32(dataGridView1.Rows[currRowIndex].Cells[0].Value.ToString());
string author = dataGridView1.Rows[currRowIndex].Cells[1].Value.ToString();
string title = dataGridView1.Rows[currRowIndex].Cells[2].Value.ToString();
string priceString = dataGridView1.Rows[currRowIndex].Cells[3].Value.ToString();
double price = 0.00;
if (priceString != "")
{
if(priceString[0] == '$')
price = Convert.ToDouble(priceString.Substring(1));
else
price = Convert.ToDouble(priceString);
}
string bookID = dataGridView1.Rows[currRowIndex].Cells[4].Value.ToString();
string discountString = dataGridView1.Rows[currRowIndex].Cells[5].Value.ToString();
double discount = 0.00;
if (discountString != "")
{
if(discountString[0] == '$')
discount = Convert.ToDouble(discountString.Substring(1));
else
discount = Convert.ToDouble(discountString);
}
//Create the orderedStock and store
OrderedStock o = new OrderedStock(nextOrderIDAndInvoiceNo, s.stockID, quantity, author, title, price, bookID, discount);
newOrderedStock.Add(o);
currRowIndex++;
}
return newOrderedStock;
}
/*Precondition:
Postcondition: Returns an order from what information is currently entered in the form */
private Order createOrder()
{
//Get all information out of text boxes
string firstName = boxFirstName.Text;
string lastName = boxLastName.Text;
string institution = boxInstitution.Text;
string add1 = boxAddress1.Text;
string add2 = boxAddress2.Text;
string add3 = boxAddress3.Text;
string postcode = boxPostcode.Text;
string country = boxCountry.Text;
string orderRef = boxOrderRef.Text;
string progress = boxProgress.Text;
string stringDate = boxInvoiceDate.Text;
string[] splitDate = stringDate.Split('/');
DateTime invoiceDate = new DateTime(Convert.ToInt32(splitDate[2]), Convert.ToInt32(splitDate[1]), Convert.ToInt32(splitDate[0]));
string freightString = boxFreight.Text;
double freight = 0.00;
if(freightString[0] == '$')
freight = Convert.ToDouble(freightString.Substring(1));
else
freight = Convert.ToDouble(freightString);
string comments = boxComments.Text;
//Get invoice/orderID for current order
int nextOrderIDAndInvoiceNo = dbManager.getNextOrderID();
Order newOrder = new Order(firstName, lastName, institution, postcode, orderRef, progress, freight, nextOrderIDAndInvoiceNo, invoiceDate, comments, currentCustomer.custID);
return newOrder;
}
