//Constructor
public CustomerOrdersForm(Customer currCustomer)
{
this.StartPosition = FormStartPosition.CenterScreen;
InitializeComponent();
this.currCustomer = currCustomer;
setup();
}
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: Customer has been found or created, autofill with customer information */
public void addCustomer(Customer newCustomer)
{
currentCustomer = newCustomer;
boxFirstName.Text = newCustomer.firstName;
boxLastName.Text = newCustomer.lastName;
boxInstitution.Text = newCustomer.institution;
boxAddress1.Text = newCustomer.address1;
boxAddress2.Text = newCustomer.address2;
boxAddress3.Text = newCustomer.address3;
boxPostcode.Text = newCustomer.postCode;
boxCountry.Text = newCustomer.country;
}
/*Precondition:
Postcondition: Changes the currently set customer and updates the relevant display for customer */
public void addCustomer(Customer newCustomer)
{
currCustomer = newCustomer;
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;
currOrder.customerID = currCustomer.custID;
}
public MailingLabelCreator(OrdersForm ordersForm, Customer customer, bool bigLabel)
{
this.ordersForm = ordersForm;
this.customer = customer;
if (bigLabel)
{
customerDetailsFontSize = "48";
bookshopLabelTitleSize = "24";
bookshopLabelSubTitleSize = "18";
tableCellWidth = "6282";
}
else
{
customerDetailsFontSize = "28";
bookshopLabelTitleSize = "22";
bookshopLabelSubTitleSize = "8";
tableCellWidth = "3355";
}
}
/*Precondition:
Postcondition: New customer was entered into database, load up their details and set to currCustomer */
public void addCustomer(Customer newCustomer)
{
newCustAndHaventRefreshed = true;
currCustomer = newCustomer;
loadUpCustomer();
}
/*Precondition:
Postcondition: Starts a search for customers depending on what search boxes have been filled in*/
private void startSearch()
{
newCustAndHaventRefreshed = true;
//Reset datagrid and foundcustomers so searches don't stack
foundCustomers = new List<Customer>();
dataGridView1.Rows.Clear();
bool customersFound = false;
//Check if an ID has been entered and search on that if it has
if (boxSearchCustID.Text != "")
{
int custID = Convert.ToInt32(boxSearchCustID.Text);
//Put found customer into list
foundCustomers.Add(dbManager.searchCustomers(custID));
//Display found customer
foreach (Customer c in foundCustomers)
{
if (c != null)
{
//Display number of results to user
labResults.Text = foundCustomers.Count.ToString();
customersFound = true;
dataGridView1.Rows.Add(c.firstName, c.lastName, c.address1, c.address2, c.country, c.email);
dataGridView1.Focus();
}
else
labResults.Text = "0";
}
}
//Else if ID hasn't been entered check for other fields to search on
else if (boxSearchFirstName.Text != "" || boxSearchLastName.Text != "" || boxSearchInstiution.Text != "" || boxSearchEmail.Text != "")
{
string firstName = null;
string lastName = null;
string instutution = null;
string email = null;
//Check if the user wants the name to match exactly what they typed
bool exactName = checkExactName.Checked;
//Get fields if they have been entered
if (boxSearchFirstName.Text != "")
firstName = boxSearchFirstName.Text;
if (boxSearchLastName.Text != "")
lastName = boxSearchLastName.Text;
if (boxSearchInstiution.Text != "")
instutution = boxSearchInstiution.Text;
if (boxSearchEmail.Text != "")
email = boxSearchEmail.Text;
//Search for customers with fields entered
foundCustomers = dbManager.searchCustomers(firstName, lastName, instutution, email, exactName);
//display number of results to user
labResults.Text = foundCustomers.Count.ToString();
if (foundCustomers.Count > 0)
{
customersFound = true;
//Sort customers by first name
foundCustomers = foundCustomers.OrderBy(x => x.firstName).ToList();
//Display found customers
foreach (Customer c in foundCustomers)
{
dataGridView1.Rows.Add(c.firstName, c.lastName, c.address1, c.address2, c.country, c.email);
}
dataGridView1.Focus();
}
}
//If there wasn't a customer in the search, display it in the datagrid and clear the textboxes
if (!customersFound)
{
currCustomer = null;
dataGridView1.Rows.Add("No customer found", "", "", "", "", "");
boxCustID.Text = "";
boxFirstName.Text = "";
boxLastName.Text = "";
boxInstitution.Text = "";
boxAddress1.Text = "";
boxAddress2.Text = "";
boxAddress3.Text = "";
boxPostcode.Text = "";
boxCountry.Text = "";
boxEmail.Text = "";
boxComments.Text = "";
boxSales.Text = "";
boxPayment.Text = "";
}
}
/*Precondition:
Postcondition: Setup and initialize everything needed */
private void setup()
{
//Intialization of globals
dbManager = new DatabaseManager();
fileManager = new FileManager();
foundCustomers = new List<Customer>();
allCustomers = new List<Customer>();
currCustomer = null;
newCustAndHaventRefreshed = false;
//Set up column widths
DataGridViewColumn colFirstName = dataGridView1.Columns[0];
colFirstName.Width = 110;
DataGridViewColumn colLastName = dataGridView1.Columns[1];
colLastName.Width = 110;
DataGridViewColumn colAdd1 = dataGridView1.Columns[2];
colAdd1.Width = 120;
DataGridViewColumn colAdd2 = dataGridView1.Columns[3];
colAdd2.Width = 120;
DataGridViewColumn colCountry = dataGridView1.Columns[4];
colCountry.Width = 60;
DataGridViewColumn colEmail = dataGridView1.Columns[5];
colEmail.Width = 182;
//Set focus to first name box
boxSearchFirstName.Select();
//Setup event handlers for when enter is pressed while on textboxes
boxSearchCustID.KeyPress += TextBox_KeyPress_Enter;
boxSearchFirstName.KeyPress += TextBox_KeyPress_Enter;
boxSearchLastName.KeyPress += TextBox_KeyPress_Enter;
boxSearchInstiution.KeyPress += TextBox_KeyPress_Enter;
boxSearchEmail.KeyPress += TextBox_KeyPress_Enter;
}
/*Precondition:
Postcondition: Enables details button when entry is selected in datagrid*/
private void dataGridView1_SelectionChanged_1(object sender, EventArgs e)
{
if (foundCustomers.Count != 0)
{
int currRow = dataGridView1.CurrentCell.RowIndex;
currCustomer = foundCustomers[currRow];
if(currCustomer != null)
loadUpCustomer();
}
}
/*Precondition:
Postcondition: Returns a list of all of the customers */
public List<Customer> getAllCustomers()
{
List<Customer> foundCustomers = new List<Customer>();
//Check to make sure customer table exists
if (checkForTable("Customer"))
{
dbConnection.Open();
string searchQuery = "SELECT * FROM Customer";
//Execute query
SQLiteCommand command = new SQLiteCommand(searchQuery, dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
//Loop over and store results
while (reader.Read())
{
Customer foundCustomer = new Customer(Convert.ToInt32(reader[0]), reader[1].ToString(), reader[2].ToString(), reader[3].ToString(), reader[4].ToString(), reader[5].ToString(), reader[6].ToString(),
reader[7].ToString(), reader[8].ToString(), reader[9].ToString(), reader[10].ToString(), reader[11].ToString(), reader[12].ToString());
foundCustomers.Add(foundCustomer);
}
dbConnection.Close();
}
//Return results
return foundCustomers;
}
/*Precondition:
Postcondition: Creates a new customer and passes it to database for storage*/
private void btnSave_Click(object sender, EventArgs e)
{
string firstName = boxFirstName.Text;
string lastName = boxLastName.Text;
string institution = boxInstitution.Text;
string address1 = boxAddress1.Text;
string address2 = boxAddress2.Text;
string address3 = boxAddress3.Text;
string postcode = boxPostcode.Text;
string country = boxCountry.Text;
string email = boxEmail.Text;
string comments = boxComments.Text;
string sales = boxSales.Text;
string payment = boxPayment.Text;
//Check that the basic things haven't been left empty so a blank customer isn't saved
if (firstName != "" || lastName != "" || address1 != "" || address2 != "" || address3 != "")
{
Customer newCustomer = new Customer(firstName, lastName, institution, address1, address2, address3, country, postcode, email, comments, sales, payment);
int nextID = dbManager.getNextCustomerID();
dbManager.insertCustomer(newCustomer);
newCustomer.custID = nextID;
customerReciever.addCustomer(newCustomer);
this.Close();
}
else
{
MessageBox.Show("Not enough information entered");
}
}
/*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;
}
}
/********** Random useful code snippits *********/
//COLLATE NOCASE = search isn't case sensitive
//string sql = "SELECT * FROM Customer WHERE firstName = 'david' COLLATE NOCASE";
//string sql = "SELECT * FROM Customer WHERE customerFirstName LIKE 'da%'";
/***************** Updating ***************************/
/*Precondition:
Postcondition: Updates the passed in customers details, new details already added onto the customer, use the ID to update*/
public void updateCustomer(Customer customer)
{
if (checkForTable("Customer"))
{
string firstName = SyntaxHelper.escapeSingleQuotes(customer.firstName);
string lastName = SyntaxHelper.escapeSingleQuotes(customer.lastName);
string institution = SyntaxHelper.escapeSingleQuotes(customer.institution);
string address1 = SyntaxHelper.escapeSingleQuotes(customer.address1);
string address2 = SyntaxHelper.escapeSingleQuotes(customer.address2);
string address3 = SyntaxHelper.escapeSingleQuotes(customer.address3);
string country = SyntaxHelper.escapeSingleQuotes(customer.country);
string postcode = SyntaxHelper.escapeSingleQuotes(customer.postCode);
string email = SyntaxHelper.escapeSingleQuotes(customer.email);
string comments = SyntaxHelper.escapeSingleQuotes(customer.comments);
string sales = SyntaxHelper.escapeSingleQuotes(customer.sales);
string payment = SyntaxHelper.escapeSingleQuotes(customer.payment);
string updateQuery = "UPDATE Customer SET firstName = '" + firstName + "', lastName = '" + lastName + "', institution = '" + institution + "', address1 = '" + address1 +
"', address2 = '" + address2 + "', address3 = '" + address3 + "', country = '" + country + "'" + ", postcode = '" + postcode +
"', email = '" + email + "', comments = '" + comments + "', sales = '" + sales + "', payment = '" + payment + "' WHERE customerID = " + customer.custID;
dbConnection.Open();
SQLiteCommand updateCommand = new SQLiteCommand(updateQuery, dbConnection);
updateCommand.ExecuteNonQuery();
dbConnection.Close();
}
}
/***************** Inserting ***************************/
/*Precondition:
Postcondition: Insert new customer into the database*/
public void insertCustomer(Customer newCustomer)
{
//Check to see if customer table exists
if (checkForTable("Customer"))
{
string firstName = SyntaxHelper.escapeSingleQuotes(newCustomer.firstName);
string lastName = SyntaxHelper.escapeSingleQuotes(newCustomer.lastName);
string institution = SyntaxHelper.escapeSingleQuotes(newCustomer.institution);
string address1 = SyntaxHelper.escapeSingleQuotes(newCustomer.address1);
string address2 = SyntaxHelper.escapeSingleQuotes(newCustomer.address2);
string address3 = SyntaxHelper.escapeSingleQuotes(newCustomer.address3);
string country = SyntaxHelper.escapeSingleQuotes(newCustomer.country);
string postcode = SyntaxHelper.escapeSingleQuotes(newCustomer.postCode);
string email = SyntaxHelper.escapeSingleQuotes(newCustomer.email);
string comments = SyntaxHelper.escapeSingleQuotes(newCustomer.comments);
string sales = SyntaxHelper.escapeSingleQuotes(newCustomer.sales);
string payment = SyntaxHelper.escapeSingleQuotes(newCustomer.payment);
//Open DB
dbConnection.Open();
//Build insert command
string customerInsert = "INSERT INTO Customer VALUES(null, '" + firstName + "', '" + lastName + "', '" + institution + "', '" + address1 + "', '" + address2 + "', '" +
address3 + "', '" + country + "', '" + postcode + "', '" + email + "', '" + comments + "', '" + sales + "', '" + payment + "')";
//Insert new customer
SQLiteCommand insertCommand = new SQLiteCommand(customerInsert, dbConnection);
insertCommand.ExecuteNonQuery();
//Close DB
dbConnection.Close();
}
}
/*Precondition:
Postcondition: Returns a list of customers from the database whose names are similar to or matching the names passed in */
public List<Customer> searchCustomers(string firstName, string lastName, string institution, string email, bool exactName)
{
List<Customer> foundCustomers = new List<Customer>();
//Check to see if customer table exists
if (checkForTable("Customer"))
{
dbConnection.Open();
firstName = SyntaxHelper.escapeSingleQuotes(firstName);
lastName = SyntaxHelper.escapeSingleQuotes(lastName);
institution = SyntaxHelper.escapeSingleQuotes(institution);
email = SyntaxHelper.escapeSingleQuotes(email);
bool addAnds = false;
//Build up query string depending on what names were passed in
string searchQuery = "SELECT * FROM Customer WHERE";
//If a firstname was passed in, include it in the query
if (firstName != null)
{
if (exactName)
searchQuery += " firstName = '" + firstName + "'";
else
searchQuery += " firstName LIKE '%" + firstName + "%'";
addAnds = true;
}
if (lastName != null)
{
if (addAnds)
{
if (exactName)
searchQuery += " AND lastName = '" + lastName + "'";
else
searchQuery += " AND lastName LIKE '%" + lastName + "%'";
}
else
{
if (exactName)
searchQuery += " lastName = '" + lastName + "'";
else
searchQuery += " lastName LIKE '%" + lastName + "%'";
}
addAnds = true;
}
if (institution != null)
{
if (addAnds)
{
if (exactName)
searchQuery += " AND institution = '" + institution + "'";
else
searchQuery += " AND institution LIKE '%" + institution + "%'";
}
else
{
if (exactName)
searchQuery += " institution = '" + institution + "'";
else
searchQuery += " institution LIKE '%" + institution + "%'";
}
addAnds = true;
}
if (email != null)
{
if (addAnds)
{
if (exactName)
searchQuery += " AND email = '" + email + "'";
else
searchQuery += " AND email LIKE '%" + email + "%'";
}
else
{
if (exactName)
searchQuery += " email = '" + email + "'";
else
searchQuery += " email LIKE '%" + email + "%'";
}
}
//Make is so search isn't case sensitive
searchQuery += " COLLATE NOCASE";
//Execute query
SQLiteCommand command = new SQLiteCommand(searchQuery, dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
//Loop over and store results
while (reader.Read())
{
Customer foundCustomer = new Customer(Convert.ToInt32(reader[0]), reader[1].ToString(), reader[2].ToString(), reader[3].ToString(), reader[4].ToString(), reader[5].ToString(), reader[6].ToString(),
reader[7].ToString(), reader[8].ToString(), reader[9].ToString(), reader[10].ToString(), reader[11].ToString(), reader[12].ToString());
foundCustomers.Add(foundCustomer);
}
dbConnection.Close();
}
//Return results
return foundCustomers;
}
/***************** Searching ***************************/
/*Precondition:
Postcondition: Returns the customer of the passed in ID*/
public Customer searchCustomers(int custID)
{
Customer foundCustomer = null;
//Check to see if customer table exists
if (checkForTable("Customer"))
{
dbConnection.Open();
//SQL query and command
string sql = "SELECT * FROM Customer WHERE customerID = " + custID;
SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
//Loop over and store results
while (reader.Read())
{
foundCustomer = new Customer(Convert.ToInt32(reader[0]), reader[1].ToString(), reader[2].ToString(), reader[3].ToString(), reader[4].ToString(), reader[5].ToString(), reader[6].ToString(),
reader[7].ToString(), reader[8].ToString(), reader[9].ToString(), reader[10].ToString(), reader[11].ToString(), reader[12].ToString());
}
dbConnection.Close();
}
//Return results
return foundCustomer;
}
