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 button2_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(id_lbl.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 , (SELECT Customer_ID FROM btm495.customers WHERE (Customer_ID = " + sub.Customer_id + ")) , @Active )";
MySqlCommand comme = connection3.CreateCommand();
comme.CommandText = query3;
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();
// select the last created subscription
MySqlConnection connection4 = new MySqlConnection(connec);
connection4.Open();
string query4 = "SELECT max(Subscription_ID) AS sub FROM btm495.subscriptions as s1 ";
MySqlDataAdapter adapt = new MySqlDataAdapter(query4, connection4);
adapt.Fill(table3);
while (row < table3.Rows.Count)
{
varSubscription_id = Convert.ToInt32(table3.Rows[row]["sub"]);
row += 1;
}
row = 0;
connection4.Close();
//select the last created invoice
MySqlConnection connection5 = new MySqlConnection(connec);
connection5.Open();
string query6 = "SELECT max(Invoice_ID) AS inv FROM btm495.invoices as i1 ";
MySqlDataAdapter adapt2 = new MySqlDataAdapter(query6, connection5);
adapt2.Fill(table4);
while (row < table4.Rows.Count)
{
varorderinv = Convert.ToInt32(table4.Rows[row]["inv"]);
row += 1;
}
row = 0;
connection5.Close();
//add to the sales order table
//select the right product
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]);
}
Sales_OrdersDA sales = new Sales_OrdersDA();
sales.Subscription_id = varSubscription_id;
sales.Order_date = DateTime.Now.ToString();
sales.Product_id = prodID;
sales.Invoice_id = varorderinv;
MySqlConnection connection6 = new MySqlConnection(connec);
connection6.Open();
string query5 = "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 = connection6.CreateCommand();
comme2.CommandText = query5;
comme2.Parameters.AddWithValue("@Order_Date", sales.Order_date);
comme2.ExecuteNonQuery();
// - fill the subscription ID label
txtSubID.Text = sales.Subscription_id.ToString();
// display confimation -
MessageBox.Show("subscription number " + sales.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.");
connection6.Close();
}
}