private void SaveData()
{
try
{
string sql = "";
if (RowId > 0)
{
sql = @"UPDATE cars SET model_id=@model_id, type_id=@type_id,registration_plate=@reg_plate,
engine=@engine, manufacturer_year=@year, fuel= @fuel, image= @image
WHERE id=@row_id";
}
else
{
sql = @"INSERT INTO cars (model_id, type_id,registration_plate, engine, manufacturer_year,
avail, image, fuel) VALUES(@model_id, @type_id, @reg_plate, @engine, @year,
1, @image, @fuel)";
}
MySqlCommand cmd = new MySqlCommand(sql, GlobalData.connection);
cmd.Parameters.Add("@model_id", MySqlDbType.Int32);
cmd.Parameters.Add("@type_id", MySqlDbType.Int32);
cmd.Parameters.Add("@reg_plate", MySqlDbType.VarChar, 50);
cmd.Parameters.Add("@engine", MySqlDbType.Int32);
cmd.Parameters.Add("@year", MySqlDbType.Int32);
cmd.Parameters.Add("@fuel", MySqlDbType.VarChar, 10);
cmd.Parameters.Add("@image", MySqlDbType.MediumBlob);
cmd.Parameters.Add("@row_id", MySqlDbType.Int32);
cmd.Parameters["@model_id"].Value = cb_Models.SelectedValue;
cmd.Parameters["@type_id"].Value = cb_Types.SelectedValue;
cmd.Parameters["@reg_plate"].Value = tb_RegPlate.Text.Trim();
cmd.Parameters["@engine"].Value = num_Engine.Value;
cmd.Parameters["@year"].Value = num_Year.Value;
cmd.Parameters["@fuel"].Value = cb_Fuel.SelectedItem;
cmd.Parameters["@row_id"].Value = RowId;
if (pictureFileName != null && File.Exists(pictureFileName))
{
cmd.Parameters["@image"].Value = File.ReadAllBytes(pictureFileName);
}
else
{
cmd.Parameters["@image"].Value = null;
}
cmd.ExecuteNonQuery();
DialogResult = DialogResult.OK;
Close();
}
catch (Exception exc)
{
DialogHelper.E(exc.Message);
}
}
private void btnLogin_Click(object sender, EventArgs e)
{
// połączenie z bazą
String cs = ConfigurationManager.AppSettings["cs"];
try
{
if (String.IsNullOrWhiteSpace(tbLogin.Text) ||
String.IsNullOrWhiteSpace(tbPassword.Text))
{
DialogHelper.E("Podaj dane do logowania");
return;
}
cs = String.Format(cs, tbLogin.Text.Trim(), tbPassword.Text.Trim());
GlobalData.connection = new MySqlConnection(cs);
GlobalData.connection.Open();
DialogResult = DialogResult.OK;
Close();
} catch (Exception exc)
{
DialogHelper.E(exc.Message);
}
}
private void btn_Login_Click(object sender, EventArgs e)
{
//string cs = "Server=127.0.0.1;Port = 3306; Database=rent_a_car; Uid = {0}; Pwd = {1}";
string cs = ConfigurationManager.AppSettings["cs"];
try
{
if (String.IsNullOrWhiteSpace(tb_Login.Text) || String.IsNullOrWhiteSpace(tb_Password.Text))
{
DialogHelper.E("Podaj dane do logowania");
return;
}
// zmiana kursora na kręciołkę
Cursor.Current = Cursors.WaitCursor;
cs = string.Format(cs, tb_Login.Text.Trim(), tb_Password.Text.Trim());
GlobalData.connection = new MySqlConnection(cs);
GlobalData.connection.Open();
this.DialogResult = DialogResult.OK;
Close();
}
catch (Exception exc)
{
DialogHelper.E(exc.Message);
}
finally
{
Cursor.Current = Cursors.Default;
}
}
private void LoadDictionaryData()
{
try
{
//ładowanie słownika marek
MySqlDataAdapter adapter = new MySqlDataAdapter();
String sql = "SELECT id, name FROM car_brands ORDER BY name ASC";
adapter.SelectCommand = new MySqlCommand(sql, GlobalData.connection);
DataTable dt = new DataTable();
adapter.Fill(dt);
bsBrands.DataSource = dt;
cbAddModel.DataSource = bsBrands;
cbAddModel.DisplayMember = "name";
cbAddModel.ValueMember = "id";
cbAddModel.SelectedIndex = -1;
cbAddModel.SelectedIndexChanged += CbAddModel_SelectedIndexChanged;
tbAddModel.Enabled = false;
}
catch (Exception exc)
{
DialogHelper.E(exc.Message);
}
}
private void btnOK_Click(object sender, EventArgs e)
{
if (!ValidateData())
{
DialogHelper.E("Sprawdź pola formularza");
return;
}
// zapis do bazy
SaveData();
}
private bool ValidateData()
{
if (tbAddModel.Text != "")
{
return(true);
}
else
{
DialogHelper.E("Wypełnij pole tekstowe");
return(false);
}
}
private bool ValidateData()
{
if (tbBrand.Text != null)
{
return(true);
}
else
{
DialogHelper.E("Wypełnij pole tekstowe");
return(false);
}
}
private void LoadDictionaryData()
{
try
{
// ładowanie słownika marek (brendów)
MySqlDataAdapter adapter = new MySqlDataAdapter();
String sql = " select id, name FROM car_brands ORDER BY name ";
adapter.SelectCommand = new MySqlCommand(sql, GlobalData.connection);
DataTable dt = new DataTable();
adapter.Fill(dt);
bsBrands.DataSource = dt;
cb_Brands.DataSource = bsBrands;
cb_Brands.DisplayMember = "name";
cb_Brands.ValueMember = "id";
cb_Brands.SelectedIndex = -1;
cb_Brands.SelectedIndexChanged += Cb_Brands_SelectedIndexChanged;
//ładowanie słownika modeli
sql = "SELECT * FROM car_models ORDER BY NAME ";
adapter = new MySqlDataAdapter();
adapter.SelectCommand = new MySqlCommand(sql, GlobalData.connection);
dt = new DataTable();
adapter.Fill(dt);
bsModels.DataSource = dt;
cb_Models.DataSource = bsModels;
cb_Models.DisplayMember = "name";
cb_Models.ValueMember = "id";
cb_Models.SelectedIndex = -1;
cb_Models.Enabled = false;
//ładowanie słownika własnosci
sql = "SELECT * FROM car_types ORDER BY NAME ";
adapter = new MySqlDataAdapter();
adapter.SelectCommand = new MySqlCommand(sql, GlobalData.connection);
dt = new DataTable();
adapter.Fill(dt);
bsTypes.DataSource = dt;
cb_Types.DataSource = bsTypes;
cb_Types.DisplayMember = "name";
cb_Types.ValueMember = "id";
cb_Types.SelectedIndex = -1;
}
catch (Exception exc)
{
DialogHelper.E(exc.Message);
}
}
private bool ValidateData()
{
if (cbModels.SelectedIndex > -1 &&
cbTypes.SelectedIndex > -1 &&
cbFuel.SelectedIndex > -1 &&
tbRegPlate.Text.Replace(" ", "").Length >= 3)
{
return(true);
}
else
{
DialogHelper.E("Sprawdż pola formularza");
return(false);
}
}
private void btnInsertPic_Click(object sender, EventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();
dialog.Filter = "Pliki graficzne|*.png;*.jpg;*.jpeg;*.bmp|Pliki GIF|*.gif";
dialog.FileName = "";
if (dialog.ShowDialog() == DialogResult.OK)
{
try
{
picCar.Image = new Bitmap(dialog.FileName);
pictureFileName = dialog.FileName;
} catch (Exception exc)
{
DialogHelper.E(exc.Message);
}
}
}
private void SaveData()
{
String sql = "";
try
{
sql = @"INSERT INTO car_brands VALUES (NULL, @name)";
MySqlCommand cmd = new MySqlCommand(sql, GlobalData.connection);
cmd.Parameters.Add("@name", MySqlDbType.VarChar, 50);
cmd.Parameters["@name"].Value = tbBrand.Text;
cmd.ExecuteNonQuery();
}
catch (Exception exc)
{
DialogHelper.E(exc.Message);
}
}
private void btnOK_Click(object sender, EventArgs e)
{
String filename =
Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "rent-a-car.docx");
DocX document = DocX.Load(filename);
document.ReplaceText("{REG_PLATE}", RegPlate);
document.ReplaceText("{MILEAGE}", numMileage.Value.ToString());
document.ReplaceText("{TS}", dtDate.Value.ToString("yyyy-MM-dd HH:mm"));
if (OperBack)
{
document.ReplaceText("{DOC_TYPE}", "ZWROT POJAZDU");
}
else
{
document.ReplaceText("{DOC_TYPE}", "WYDANIE POJAZDU");
}
filename = Path.Combine(@"c:\tmp", DateTime.Now.ToString("yyyyMMddHHmm") + ".docx");
document.SaveAs(filename);
MySqlTransaction tr = null;
try
{
tr = GlobalData.connection.BeginTransaction();
String sql;
int avail;
if (OperBack == false)
{
// wydaj auto - dodaj rekord
sql = @"INSERT INTO operations
(car_id, ts_out, mileage_out, description)
VALUES
(@car_id, @ts, @mileage, @descr)";
avail = 0;
}
else
{
// przyjmij auto - zaktualizuj ostatni rekord operacji dla auta
sql = @"UPDATE operations SET
ts_in=@ts, mileage_in=@mileage, description=@descr
WHERE id=@id";
avail = 1;
}
MySqlCommand cmd = new MySqlCommand(sql, GlobalData.connection);
cmd.Transaction = tr;
cmd.Parameters.Add("@car_id", MySqlDbType.Int32).Value = CarId;
cmd.Parameters.Add("@ts", MySqlDbType.DateTime).Value = dtDate.Value;
cmd.Parameters.Add("@mileage", MySqlDbType.Int32).Value = numMileage.Value;
cmd.Parameters.Add("@descr", MySqlDbType.Text).Value = tbDescr.Text;
cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = lastRecordId;
cmd.ExecuteNonQuery();
sql = "UPDATE cars SET avail=@avail WHERE id=@id";
cmd = new MySqlCommand(sql, GlobalData.connection);
cmd.Transaction = tr;
cmd.Parameters.Add("@avail", MySqlDbType.Int32).Value = avail;
cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = CarId;
cmd.ExecuteNonQuery();
tr.Commit();
DialogResult = DialogResult.OK;
Close();
} catch (Exception exc)
{
if (tr != null)
{
tr.Rollback();
}
DialogHelper.E(exc.Message);
}
}
private void FormAddCar_Load(object sender, EventArgs e)
{
LoadDictionaryData();
numYear.Minimum = Convert.ToInt32(ConfigurationManager.AppSettings["minYear"]);
numYear.Maximum = Convert.ToInt32(ConfigurationManager.AppSettings["maxYear"]);
try
{
if (RowId > 0)
{
//wczytaj dane edytowanego rekordu i pokaż w kontrolkach
String sql = @"SELECT c.*, m.brand_id FROM cars c, car_models m
WHERE c.id = {0} AND c.model_id = m.id";
sql = String.Format(sql, RowId);
MySqlCommand cmd = new MySqlCommand(sql, GlobalData.connection);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
//odczytywanie danych
numEngine.Value = Convert.ToInt32(reader["engine"]);
numYear.Value = Convert.ToInt32(reader["manufacturer_year"]);
tbRegPlate.Text = reader["registration_plate"].ToString();
cbFuel.SelectedIndex = cbFuel.Items.IndexOf(reader["fuel"]);
cbBrands.SelectedValue = reader["brand_id"];
cbModels.SelectedValue = reader["model_id"];
cbTypes.SelectedValue = reader["type_id"];
cbModels.Enabled = true;
if (!(reader["image"] is DBNull))
{
byte[] b = (byte[])reader["image"];
if (b != null && b.Length > 0)
{
using (MemoryStream ms = new MemoryStream(b))
{
picCar.Image = Image.FromStream(ms);
}
}
}
//zamknąć reader
reader.Close();
}
}
}catch (Exception exc)
{
DialogHelper.E(exc.Message);
}
//Zmiana tekstów przy edycji/ dodawaniu pojazdu
if (RowId > 0)
{
btnOK.Text = "Zapisz zmiany";
Text = "Edycja pojazdu";
}
else
{
btnOK.Text = "Dodaj";
Text = "Nowy pojazd";
}
}