private void PrepareOrder()
{
//array with controls to check
Control[] c = new Control[] { BZ_straat, BZ_HuisNr, BZ_Postcode, BZ_plaats, BZ_land, BZ_ontvanger, BZ_Datum };
//Checkstate and proceed
if (Check._Ctrl(c, Methode.Color) && palletsTemp.Count != 0)
{
Bezorging b = new Bezorging(BZ_straat.Text, Convert.string_int(BZ_HuisNr.Text), BZ_Postcode.Text, BZ_plaats.Text, BZ_land.Text, BZ_ontvanger.Text, BZ_Datum.Text);
CreateOrder(palletsTemp, b);
}
//if not all fields are filled in
else if (!Check._Ctrl(c, Methode.Color))
{
MessageBox.Show("Not all Bezorging fields are filled in!");
}
//if pallet_list is empty
else if (palletsTemp.Count == 0)
{
MessageBox.Show("There are no pallets please add some!");
}
//if both above failed
else
{
MessageBox.Show("Something is wrong! [Both wrong?]");
}
}
private void CreateOrder(List <Pallets> p, Bezorging b)
{
#region Variable
int Order_ID = 0;
#endregion
#region Order to Database
using (MySqlConnection connection = new MySqlConnection(Db.ConString))
{
using (MySqlCommand command = new MySqlCommand())
{
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = "" +
"INSERT INTO `orders`(`order_ID`, `klant_ID`, `rit_ID`, `postcode`, `straatnaam`, `huis_nr`, `plaats`, `land`, `ontvanger`, `datum`) " +
"VALUES (@order_ID,@klant_ID,@rit_ID,@postcode,@straatnaam,@huis_nr,@plaats,@land,@ontvanger,@datum)";
//Set parameters
command.Parameters.AddWithValue("@order_ID", "");
command.Parameters.AddWithValue("@klant_ID", KlantID);
command.Parameters.AddWithValue("@rit_ID", "");
command.Parameters.AddWithValue("@postcode", b.Postcode);
command.Parameters.AddWithValue("@straatnaam", b.Straat);
command.Parameters.AddWithValue("@huis_nr", b.HuisNr);
command.Parameters.AddWithValue("@plaats", b.Plaats);
command.Parameters.AddWithValue("@land", b.Land);
command.Parameters.AddWithValue("@ontvanger", b.Ontvanger);
command.Parameters.AddWithValue("@datum", b.Datum);
try
{
connection.Open();
int recordsAffected = command.ExecuteNonQuery();
MessageBox.Show("Order has added");
}
catch (SqlException)
{
throw;
}
finally
{
connection.Close();
}
}
}
#endregion
#region Search Created Order
//create connection and open it
MySqlConnection SelectCommand = new MySqlConnection(Db.ConString);
SelectCommand.Open();
//try to connect to database
try
{
//Get Order_id and pass it
MySqlCommand id_cmd = SelectCommand.CreateCommand();
id_cmd.CommandText = "SELECT `order_ID` FROM `orders` " +
"WHERE `klant_ID`= @klant_ID " +
"AND `postcode`= @postcode " +
"AND `straatnaam`= @straatnaam " +
"AND `huis_nr`= @huis_nr " +
"AND `plaats`= @plaats " +
"AND `land`= @land " +
"AND `ontvanger`= @ontvanger " +
"AND `datum`= @datum";
//Set params
id_cmd.Parameters.AddWithValue("@klant_ID", KlantID);
id_cmd.Parameters.AddWithValue("@postcode", b.Postcode);
id_cmd.Parameters.AddWithValue("@straatnaam", b.Straat);
id_cmd.Parameters.AddWithValue("@huis_nr", b.HuisNr);
id_cmd.Parameters.AddWithValue("@plaats", b.Plaats);
id_cmd.Parameters.AddWithValue("@land", b.Land);
id_cmd.Parameters.AddWithValue("@ontvanger", b.Ontvanger);
id_cmd.Parameters.AddWithValue("@datum", b.Datum);
MySqlDataReader reader = id_cmd.ExecuteReader();
//Read results and set Order ID
if (reader.Read())
{
Order_ID = int.Parse(reader["order_ID"].ToString());
}
else
{
MessageBox.Show("no match id found!");
}
}
//finally
finally
{
//check state and clone
if (SelectCommand.State == ConnectionState.Open)
{
SelectCommand.Clone();
}
}
#endregion
#region Create pallets
for (int i = 0; i < p.Count; i++)
{
using (MySqlConnection connection = new MySqlConnection(Db.ConString))
{
using (MySqlCommand command = new MySqlCommand())
{
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = "" +
"INSERT INTO `pallets`(`pallet_ID`, `order_ID`, `gewicht`, `inhoud`, `notitie`) " +
"VALUES (@pallet_ID,@order_ID,@gewicht,@inhoud,@notitie)";
//Set parameters
command.Parameters.AddWithValue("@pallet_ID", "");
command.Parameters.AddWithValue("@order_ID", Order_ID);
command.Parameters.AddWithValue("@gewicht", p[i].Gewicht);
command.Parameters.AddWithValue("@inhoud", p[i].Inhoud);
command.Parameters.AddWithValue("@notitie", p[i].Notitie);
try
{
connection.Open();
int recordsAffected = command.ExecuteNonQuery();
MessageBox.Show("Pallet has been added");
}
catch (SqlException)
{
throw;
}
finally
{
connection.Close();
}
}
}
}
#endregion
}
