//add all the product corresponding to the customer aircraft loaded int he invoice and load the invoiceid, customer id and aircraft id from that same invoice private void Sub_Create_Sub_Load(object sender, EventArgs e) { MySqlConnection connection = new MySqlConnection(connec); connection.Open(); string query_1 = @"Select * FROM sales_orders AS s1 JOIN invoices as i1 ON i1.Invoice_ID = s1.invoices_Invoice_ID JOIN subscriptions AS s2 ON s2.Subscription_ID = s1.Subscription_ID JOIN products as p1 ON s1.products_Product_ID = p1.Product_ID WHERE invoice_completed_1y_2n = 1 AND invoices_Invoice_ID = " + invid + ""; MySqlCommand comm = connection.CreateCommand(); comm.CommandText = query_1; DataTable table = new DataTable(); MySqlDataAdapter adapt = new MySqlDataAdapter(query_1, connection); adapt.Fill(table); while (row < table.Rows.Count) { SubscriptionsDA sub = new SubscriptionsDA(); txtInvID.Text = table.Rows[row]["invoices_Invoice_ID"].ToString(); custIdTxt.Text = table.Rows[row]["Customer_ID"].ToString(); aircraft_lbl.Text = table.Rows[row]["Aircraft_ID"].ToString(); string query_2 = @"Select * FROM products as p1 WHERE Aircraft_ID = " + table.Rows[row]["Aircraft_ID"] + ""; MySqlCommand comm2 = connection.CreateCommand(); comm2.CommandText = query_2; MySqlDataAdapter adapt2 = new MySqlDataAdapter(query_2, connection); adapt2.Fill(table2); while (row < table2.Rows.Count) { ProductsDA pro = new ProductsDA(); pro.Product_id = Convert.ToInt32(table2.Rows[row]["Product_ID"]); pro.Product_name = table2.Rows[row]["Product_Name"].ToString(); pro.Product_type = table2.Rows[row]["Product_Type"].ToString(); product_list.Items.Add(pro.Show_product()); row += 1; } row += 1; } row = 0; connection.Close(); }
private void addSales_Click(object sender, EventArgs e) { if ( radBCredit.Checked == false && radCreditCard.Checked == false && radWireTrans.Checked == false ) { MessageBox.Show("please select a payment type"); } else { //insert int he databse the filled fields SubscriptionsDA sub = new SubscriptionsDA(); sub.Start_date = dateTimePicker1.Value.ToString("yyyy-MM-dd"); sub.End_date = dateTimePicker2.Value.ToString("yyyy-MM-dd"); sub.Customer_id = Convert.ToInt32(custIdTxt.Text); if (radBCredit.Checked) { sub.Payment_method = "Bombardier Credit"; } else if (radCreditCard.Checked) { sub.Payment_method = "Credit card"; } else if (radWireTrans.Checked) { sub.Payment_method = "Wire Transfer"; } sub.Active = 1; MySqlConnection connection3 = new MySqlConnection(connec); connection3.Open(); string query3 = "INSERT INTO btm495.subscriptions(Start_Date, End_Date, Payment_Method, Customer_ID, Active) VALUES( @Start_Date, @End_Date, @Payment_Method , @Customer_id , @Active)"; MySqlCommand comme = connection3.CreateCommand(); comme.CommandText = query3; comme.Parameters.AddWithValue("@Customer_id", sub.Customer_id); comme.Parameters.AddWithValue("@Start_Date", sub.Start_date); comme.Parameters.AddWithValue("@End_Date", sub.End_date); comme.Parameters.AddWithValue("@Payment_Method", sub.Payment_method); comme.Parameters.AddWithValue("@Active", sub.Active); comme.ExecuteNonQuery(); connection3.Close(); connection3.Open(); string query10 = " SELECT max(Subscription_ID) AS submax FROM subscriptions"; MySqlCommand comme4 = connection3.CreateCommand(); comme4.CommandText = query3; MySqlDataAdapter adapt2 = new MySqlDataAdapter(query10, connection3); adapt2.Fill(table5); while (row < table5.Rows.Count) { varSubscription_id = Convert.ToInt32(table5.Rows[row]["submax"]); row += 1; } MessageBox.Show("created" + varSubscription_id.ToString()); row = 0; connection3.Close(); selectedItems = new ListBox.SelectedObjectCollection(product_list); selectedItems = product_list.SelectedItems; int selectedIndex = product_list.SelectedIndex; if (selectedIndex != -1) { for (int i = selectedItems.Count - 1; i >= 0; i--) { prodIDii = selectedItems[i].ToString(); } prodIDiii = prodIDii.Split(','); prodID = Convert.ToInt32(prodIDiii[0]); } connection3.Open(); Sales_OrdersDA sales = new Sales_OrdersDA(); sales.Subscription_id = varSubscription_id; sales.Order_date = DateTime.Now.ToString(); sales.Product_id = prodID; sales.Invoice_id = Convert.ToInt32(txtInvID.Text); string query4 = @"INSERT INTO btm495.sales_orders(Order_Date, Subscription_ID, products_Product_ID, invoices_Invoice_ID) VALUES( @Order_Date, (SELECT Subscription_ID FROM btm495.subscriptions WHERE (Subscription_ID = " + sales.Subscription_id + "))," + "(SELECT Product_ID FROM btm495.products WHERE (Product_ID = " + sales.Product_id + ")), " + "(SELECT Invoice_ID FROM btm495.invoices WHERE (Invoice_ID = " + sales.Invoice_id + ")) )"; MySqlCommand comme2 = connection3.CreateCommand(); comme2.CommandText = query4; comme2.Parameters.AddWithValue("@Order_Date", sales.Order_date); comme2.ExecuteNonQuery(); connection3.Close(); // display confimation - //MessageBox.Show("subscription number " + sub.Subscription_id + " succesfully added ! " + Environment.NewLine + " If you wish to continue your purchase order please select a new product and date, the rest will be taken care of."); } }