private void Create_Sub_Load(object sender, EventArgs e)
{
//Load the information convening to the prevously filled customer information and aircraft choice
MySqlConnection connection = new MySqlConnection(connec);
connection.Open();
string query3 = @" SELECT
aircraft_license AS licenseID,
Customer_ID,
p1.Aircraft_ID,
Product_ID,
Product_name,
Product_price,
Product_type
FROM
btm495.products AS p1 JOIN
btm495.aircraft_license AS l1
ON p1.Aircraft_ID = l1.Aircraft_ID
WHERE
aircraft_license = (select max(aircraft_license) from btm495.aircraft_license)";
MySqlDataAdapter adapt = new MySqlDataAdapter(query3, connection);
adapt.Fill(table);
while (row < table.Rows.Count)
{
ProductsDA pro = new ProductsDA();
pro.Product_price = Convert.ToInt32(table.Rows[row]["Product_price"]);
pro.Product_name = (string)table.Rows[row]["Product_name"];
pro.Product_type = (string)table.Rows[row]["Product_type"];
pro.Product_id = Convert.ToInt32(table.Rows[row]["Product_ID"]);
product_list.Items.Add(pro.Show_product());
aicraft_lbl.Text = table.Rows[row]["Aircraft_ID"].ToString();
id_lbl.Text = table.Rows[row]["Customer_ID"].ToString();
row += 1;
}
row = 0;
connection.Close();
//create an invoice which will be used as a basquet of goods through the following process, date uploaded
connection.Open();
InvoicesDA invoice = new InvoicesDA();
invoice.Completed = 2;
invoice.Date = DateTime.Now.ToString();
MySqlConnection connection3 = new MySqlConnection(connec);
connection3.Open();
string query8 = "INSERT INTO btm495.invoices(invoice_completed_1y_2n, invoice_Date) VALUES( @invoice_completed_1y_2n, @invoice_Date)";
MySqlCommand comme = connection3.CreateCommand();
comme.CommandText = query8;
comme.Parameters.AddWithValue("@invoice_completed_1y_2n", invoice.Completed);
comme.Parameters.AddWithValue("@invoice_Date", invoice.Date);
comme.ExecuteNonQuery();
connection.Close();
}
private void Invoice_History_Load(object sender, EventArgs e)
{
MySqlConnection connection1 = new MySqlConnection(connec);
connection1.Open();
string query1 = "SELECT * FROM btm495.invoices WHERE invoice_completed_1y_2n = 1";
MySqlCommand comm1 = connection1.CreateCommand();
comm1.CommandText = query1;
MySqlDataAdapter adapt1 = new MySqlDataAdapter(query1, connection1);
adapt1.Fill(table1);
while (row1 < table1.Rows.Count)
{
InvoicesDA inv = new InvoicesDA();
inv.Invoice_id = Convert.ToInt32(table1.Rows[row1]["Invoice_ID"]);
inv.Sub_total = Convert.ToInt32(table1.Rows[row1]["Sub_Total"]);
inv.Date = table1.Rows[row1]["invoice_date"].ToString();
inv_listbx.Items.Add(inv.showinv2());
row1 += 1;
}
row1 = 0;
}
private void Subscription_Load(object sender, EventArgs e)
{
int row = 0;
//load the customer list method with the data from mysql
MySqlConnection connection = new MySqlConnection(connec);
connection.Open();
string query3 = "SELECT * FROM subscriptions as s1 JOIN customers as c1 ON s1.Customer_ID = c1.Customer_ID ";
MySqlCommand comm = connection.CreateCommand();
comm.CommandText = query3;
DataTable table = new DataTable();
MySqlDataAdapter adapt = new MySqlDataAdapter(query3, connection);
adapt.Fill(table);
while (row < table.Rows.Count)
{
SubscriptionsDA sub = new SubscriptionsDA();
sub.Customer_id = Convert.ToInt32(table.Rows[row]["Customer_ID"]);
sub.Subscription_id = Convert.ToInt32(table.Rows[row]["Subscription_ID"]);
sub.Customer_name = table.Rows[row]["Company"].ToString();
subLB.Items.Add(sub.show_sub2());
row += 1;
}
row = 0;
connection.Close();
//load the invoice list method with the data from mysql
connection.Open();
string query4 = "Select * FROM invoices WHERE invoice_completed_1y_2n = 1";
MySqlCommand comm2 = connection.CreateCommand();
comm2.CommandText = query3;
DataTable table2 = new DataTable();
MySqlDataAdapter adapt2 = new MySqlDataAdapter(query4, connection);
adapt2.Fill(table2);
while (row < table2.Rows.Count)
{
InvoicesDA sub = new InvoicesDA();
sub.Invoice_id = Convert.ToInt32(table2.Rows[row]["Invoice_ID"]);
sub.Sub_total = Convert.ToInt32(table2.Rows[row]["Sub_Total"]);
sub.Date = table2.Rows[row]["invoice_date"].ToString();
invoice_lstbx.Items.Add(sub.Show_inv());
row += 1;
}
row = 0;
connection.Close();
}
private void View_sales_order_Load(object sender, EventArgs e)
{
MySqlConnection connection = new MySqlConnection(connec);
connection.Open();
string query3 = @"SELECT invoices_Invoice_ID AS order_number,
i1.invoice_Date,
Aircraft_ID,
products_Product_ID,
Product_Name,
Product_Price,
Product_Type,
c1.First_Name,
c1.Billing_Address_Street,
c1.Person_of_Contact,
c1.Company
FROM
sales_orders AS s1
JOIN products AS p1
ON s1.products_Product_ID = p1.Product_ID
JOIN invoices AS i1
ON i1.Invoice_ID = s1.invoices_Invoice_ID
JOIN subscriptions AS s2
ON s1.Subscription_ID = s2.Subscription_ID
JOIN customers AS c1
ON s2.Customer_ID = c1.Customer_ID
WHERE
invoices_Invoice_ID = (select max(Invoice_ID) from invoices)";
MySqlDataAdapter adapt = new MySqlDataAdapter(query3, connection);
adapt.Fill(table);
while (row < table.Rows.Count)
{
ProductsDA pro = new ProductsDA();
pro.Product_price = Convert.ToInt32(table.Rows[row]["Product_price"]);
pro.Product_name = (string)table.Rows[row]["Product_name"];
pro.Product_type = (string)table.Rows[row]["Product_type"];
pro.Product_id = Convert.ToInt32(table.Rows[row]["products_Product_ID"]);
product_listbox.Items.Add(pro.Show_product());
order_nm.Text = table.Rows[row]["order_number"].ToString();
order_date_lbl.Text = table.Rows[row]["invoice_Date"].ToString();
nme_lbl.Text = table.Rows[row]["First_Name"].ToString();
ship_lbl.Text = table.Rows[row]["Billing_Address_Street"].ToString();
company_lbl.Text = table.Rows[row]["Company"].ToString();
contact_lbl.Text = table.Rows[row]["Person_of_Contact"].ToString();
aircraft_lbl.Text = table.Rows[row]["Aircraft_ID"].ToString();
row += 1;
}
row = 0;
connection.Close();
connection.Open();
string query4 = @"SELECT
MAX(invoices_Invoice_ID) AS order_number,
i1.invoice_Date,
COUNT(products_Product_ID) AS total_quantity,
SUM(Product_Price) AS total_cost
FROM
sales_orders AS s1
JOIN products AS p1
ON s1.products_Product_ID = p1.Product_ID
JOIN invoices AS i1
ON i1.Invoice_ID = s1.invoices_Invoice_ID
WHERE
invoices_Invoice_ID = (select max(Invoice_ID) from invoices)
GROUP BY
Invoice_ID";
MySqlDataAdapter adapt2 = new MySqlDataAdapter(query4, connection);
adapt2.Fill(table2);
while (row < table2.Rows.Count)
{
InvoicesDA inv = new InvoicesDA();
inv.Invoice_id = Convert.ToInt32(table2.Rows[row]["order_number"]);
inv.Completed = 1;
inv.Order_total = Convert.ToDecimal(table2.Rows[row]["total_quantity"]);
inv.Sub_total = Convert.ToDecimal(table2.Rows[row]["total_cost"]);
int Tax_total = Convert.ToInt32(table2.Rows[row]["total_cost"]);
inv.Tax_total = Convert.ToDecimal(Math.Round((Tax_total * 0.15), MidpointRounding.AwayFromZero));
total_lbl.Text = table2.Rows[row]["total_quantity"].ToString();
cost_lbl.Text = table2.Rows[row]["total_cost"].ToString();
tax_lbl.Text = "15%";
grand_total_lbl.Text = (inv.Tax_total + inv.Sub_total).ToString();
//update invoice table with the acording information
// connection.Open();
string query7 = "UPDATE btm495.invoices SET invoice_completed_1y_2n = @invoice_completed_1y_2n, Sub_total = @Sub_total, Order_total = @Order_total, Tax_total= @Tax_total WHERE Invoice_ID = @Invoice_ID";
MySqlCommand comm = connection.CreateCommand();
comm.CommandText = query7;
comm.Parameters.AddWithValue("@invoice_completed_1y_2n", inv.Completed);
comm.Parameters.AddWithValue("@Sub_total", inv.Sub_total);
comm.Parameters.AddWithValue("@Order_total", inv.Order_total);
comm.Parameters.AddWithValue("@Tax_total", inv.Tax_total);
comm.Parameters.AddWithValue("@Invoice_ID", inv.Invoice_id);
comm.ExecuteNonQuery();
// connection.Close();
row += 1;
}
row = 0;
connection.Close();
}