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();
}
public Products()
{
InitializeComponent();
DbHelper.EstablishConnection();
MySqlConnection connection = new MySqlConnection(connec);
connection.Open();
string queryProducts = "SELECT * FROM btm495.Products";
MySqlCommand comm = connection.CreateCommand();
comm.CommandText = queryProducts;
DataTable table = new DataTable();
MySqlDataAdapter adapt = new MySqlDataAdapter(queryProducts, connection);
adapt.Fill(table);
while (row < table.Rows.Count)
{
ProductsDA product = new ProductsDA();
product.Product_id = Convert.ToInt32(table.Rows[row]["Product_ID"]);
product.Product_name = (string)table.Rows[row]["Product_name"];
product.Product_type = (string)table.Rows[row]["Product_type"];
comboBox1.Items.Add(product.Show_product());
row += 1;
}
row = 0;
}
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();
}
//load all the pertinent information concerning an aircraft and its products and the customer info associated
private void Modify_Sub_Load_1(object sender, EventArgs e)
{
int chosenSub;
chosenSub = Subscription.subscription;
MySqlConnection connection = new MySqlConnection(connec);
connection.Open();
string query3 = @"SELECT invoices_Invoice_ID AS order_number,
i1.invoice_Date,
Aircraft_ID,
c1.First_Name,
c1.Billing_Address_Street,
c1.Person_of_Contact,
c1.Company,
s1.Subscription_ID
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
s2.Subscription_ID = " + chosenSub;
MySqlDataAdapter adapt = new MySqlDataAdapter(query3, connection);
adapt.Fill(table);
while (row < table.Rows.Count)
{
txtCustomerName.Text = table.Rows[row]["Company"].ToString();
txtSubID.Text = table.Rows[row]["Subscription_ID"].ToString();
comboBox1.Text = table.Rows[row]["Aircraft_ID"].ToString();
row += 1;
}
row = 0;
connection.Close();
//select all the product given a certain aircraft
connection.Open();
string query4 = @"SELECT * FROM products
WHERE
Aircraft_ID = " + comboBox1.Text;
MySqlDataAdapter adapt2 = new MySqlDataAdapter(query4, connection);
adapt2.Fill(table2);
while (row < table2.Rows.Count)
{
ProductsDA pro = new ProductsDA();
pro.Product_price = Convert.ToInt32(table2.Rows[row]["Product_price"]);
pro.Product_name = (string)table2.Rows[row]["Product_name"];
pro.Product_type = (string)table2.Rows[row]["Product_type"];
pro.Product_id = Convert.ToInt32(table2.Rows[row]["Product_ID"]);
product_List.Items.Add(pro.Show_product());
row += 1;
}
row = 0;
connection.Close();
}