private void btnLogin_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(tbLogin.Text) ||
String.IsNullOrWhiteSpace(tbPassword.Text))
{
DialogHelper.Error("Podaj dane do logowania");
return;
}
Cursor.Current = Cursors.WaitCursor;
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.Error(exc.Message);
} finally
{
Cursor.Current = Cursors.Default;
}
}
private void SaveData()
{
try
{
String sql = "";
if (RowId > 0)
{
// update SQL
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,
image, fuel, avail )
VALUES
(@model_id, @type_id, @reg_plate, @engine, @year, @image, @fuel, 1)
";
}
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 = cbModels.SelectedValue;
cmd.Parameters["@type_id"].Value = cbTypes.SelectedValue;
cmd.Parameters["@reg_plate"].Value = tbRegPlate.Text.Trim();
cmd.Parameters["@year"].Value = numYear.Value;
cmd.Parameters["@engine"].Value = numEngine.Value;
cmd.Parameters["@fuel"].Value = cbFuel.SelectedItem;
cmd.Parameters["@row_id"].Value = RowId;
if (pictureFileName != null)
{
cmd.Parameters["@image"].Value = File.ReadAllBytes(pictureFileName);
}
else
{
cmd.Parameters["@image"].Value = null;
}
cmd.ExecuteNonQuery();
DialogResult = DialogResult.OK;
Close();
} catch (Exception exc)
{
DialogHelper.Error(exc.Message);
}
}
private void FormOperation_Load(object sender, EventArgs e)
{
if (OperBack)
{
this.Text = "Zwrot pojazdu" + this.RegPlate;
try
{
String sql = "SELECT id, description FROM operations where car_id=" + CarId + " ORDER BY id DESC LIMIT 0,1 ";
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = new MySqlCommand(sql, GlobalData.connection);
DataTable dt = new DataTable();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
lastRecordId = Convert.ToInt32(dt.Rows[0]["id"]);
tbDescr.Text = dt.Rows[0]["description"].ToString();
}
} catch (Exception exc)
{
DialogHelper.Error(exc.Message);
}
}
else
{
this.Text = "Wydanie pojazdu " + this.RegPlate;
}
}
private void btnlogin_Click(object sender, EventArgs e)
{
//aby sie podpiąc do bazy wpierw nalezy dodać referencje mysql
// String cs = "Server=127.0.0.1;Port=3306;Database=rent_a_car;Uid={0};Pwd={1}";
//to powyżej to hardskodowane połączenie do bazy - tak sie nie robi! bo np
//przeniesiono baze na inny serwer
//trzeba użyć plik konfiguracyjny (app.config)
//do refeerencji musimy dodać system.configuration i robimy tak:
String cs = ConfigurationManager.AppSettings["cs"];
try
{
if (String.IsNullOrWhiteSpace(tbLogin.Text) ||
String.IsNullOrWhiteSpace(tbPassword.Text))
{
DialogHelper.Error("Podaj dane logowania");
return;
}
//zmiana kursora na czas podłączania do bazy
Cursor.Current = Cursors.WaitCursor;
//dotyczy bieżacego okna, więc kursor wróci do bazowej wersji przy zamkniecuy okienka
cs = String.Format(cs, tbLogin.Text.Trim(), tbPassword.Text.Trim());
//MySqlConnection connection = new MySqlConnection(cs);
//connection.Open();
//po utworzeniu globaldata, możemy wywalić powyższe linijki i
//dodajemy dwie poniższe linijki
GlobalData.connection = new MySqlConnection(cs);
GlobalData.connection.Open();
//przed zamknięciem okna zwrócmy kod wyjścia i przekazać go do formy głównej w pasku na dole formy
this.DialogResult = DialogResult.OK;
Close();
//połączenie mamy tylko z formy login, więc jej zamknięcie
//spowoduje rozłączenie z bazą. trzeba zrobić "kontener" na
//połączenie globalne (klasę statyczną) - folder utils a tam
//klase pomocniczą - GlobalData
}
catch (Exception exc)
{
//MessageBox.Show(exc.Message);
//Po zrobieniu klasy pomocniczej dialog helper możemy wywalic powyższe a dodać poniższy kod
DialogHelper.Error(exc.Message);
}
finally
{
//przywrócenie domyslnego kursora
Cursor.Current = Cursors.Default;
}
}
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, image, fuel, avail)
values
(@model_id, @type_id, @reg_plate, @engine, @year, @image, @fuel, 1)";
}
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("@image", MySqlDbType.MediumBlob);
cmd.Parameters.Add("@fuel", MySqlDbType.VarChar, 50);
cmd.Parameters.Add("@row_id", MySqlDbType.Int32);
cmd.Parameters["@model_id"].Value = cbModels.SelectedValue;
cmd.Parameters["@type_id"].Value = cbTypes.SelectedValue;
cmd.Parameters["@reg_plate"].Value = mtbRegistration.Text;
cmd.Parameters["@engine"].Value = nudEngine.Value;
cmd.Parameters["@year"].Value = nudYear.Value;
cmd.Parameters["@fuel"].Value = cbFuel.SelectedItem;
cmd.Parameters["@row_id"].Value = rowId;
if (pictureFileName != null)
{
cmd.Parameters["@image"].Value = File.ReadAllBytes(pictureFileName);
}
else
{
cmd.Parameters["@image"].Value = null;
}
cmd.ExecuteNonQuery();
DialogResult = DialogResult.OK;
Close();
}
catch (Exception exc)
{
DialogHelper.Error(exc.Message);
}
}
private void LoadDictionaryData()
{
try
{
//ładownanie słownika marek
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;
cbBrands.DataSource = bsBrands;
cbBrands.DisplayMember = "name"; //to wyswietlamy uzytkownikowi
cbBrands.ValueMember = "id"; //identyfikator numeryczny
cbBrands.SelectedIndex = -1; //komórka wyboru marki bedzie pusta
cbBrands.SelectedIndexChanged += CbBrands_SelectedIndexChanged;
//ładowanie slownika modeli mamy adapter,
//wiec nie musimy go tworzyć na nowo
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;
cbModels.DataSource = bsModels;
cbModels.DisplayMember = "name";
cbModels.ValueMember = "id";
cbModels.SelectedIndex = -1;
cbModels.Enabled = false;
//ładowanie słownika własności
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;
cbTypes.DataSource = bsTypes;
cbTypes.DisplayMember = "name";
cbTypes.ValueMember = "id";
cbTypes.SelectedIndex = -1;
}
catch (Exception exc)
{
DialogHelper.Error(exc.Message);
}
}
private void LoadDictionaryData()
{
try
{
// ładowanie słownika producentó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;
cbBrands.DataSource = bsBrands;
cbBrands.DisplayMember = "name";
cbBrands.ValueMember = "id";
cbBrands.SelectedIndex = -1;
// podpinanie oblsugi zdarzenia
cbBrands.SelectedIndexChanged += CbBrands_SelectedIndexChanged;
// ładowanie słownika modeli
adapter = new MySqlDataAdapter();
sql = "select id, brand_id, name from car_models order BY brand_id asc, NAME asc";
adapter.SelectCommand = new MySqlCommand(sql, GlobalData.connection);
dt = new DataTable();
adapter.Fill(dt);
bsModels.DataSource = dt;
cbModels.DataSource = bsModels;
cbModels.DisplayMember = "name";
cbModels.ValueMember = "id";
cbModels.SelectedIndex = -1;
cbModels.Enabled = false;
// ładowanie słownika typów własnosci
adapter = new MySqlDataAdapter();
sql = "select id, name from car_types order BY NAME asc";
adapter.SelectCommand = new MySqlCommand(sql, GlobalData.connection);
dt = new DataTable();
adapter.Fill(dt);
bsTypes.DataSource = dt;
cbTypes.DataSource = bsTypes;
cbTypes.DisplayMember = "name";
cbTypes.ValueMember = "id";
cbTypes.SelectedIndex = -1;
} catch (Exception exc)
{
DialogHelper.Error(exc.Message);
}
}
private bool ValidateDate()
{//sprawdzamy czy wszystkie dane wypełniono i czy można zrobić save do bazy
if (cbModels.SelectedIndex > -1 &&
cbTypes.SelectedIndex > -1 &&
cbFuel.SelectedIndex > -1 &&
tbRegPlate.Text.Replace(" ", "").Length >= 3)
{
return(true);
}
else
{
DialogHelper.Error("Sprawdź pola formularza!");
return(false);
}
}
private bool ValidateData()
{
if (cbBrands.SelectedIndex > -1 &&
cbModels.SelectedIndex > -1 &&
cbTypes.SelectedIndex > -1 &&
cbFuel.SelectedIndex > -1 &&
tbRegPlate.Text.Replace(" ", "").Length > 0)
{
return(true);
}
else
{
DialogHelper.Error("Sprawdź formularz");
return(false);
}
}
private void FormAddCar_Load(object sender, EventArgs e)
{
LoadDictionaryData();
//jesli uzupełnimy configapp, to możemy pobrac dane z konfiguracji
//uwaga - pobrane dane to string, więc trzeba to konwertować
//uwaga2 - trzeba się zabezpieczyc, przed wpisaniem bzdur w configu
//numYear.Minimum = Convert.ToInt32(ConfigurationManager.AppSettings["minyear"]);
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);
//zapytanie zwraca jeden wiersz wieć uzyjemy datareader
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows) //sprawdzamy czy zapytanie zwróciło cokolwiek
{
reader.Read();
//odczyt danych z readera
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"]);
//selectedvalue, bo robilismy databinding
cbBrands.SelectedValue = reader["brand_id"];
cbModels.SelectedValue = reader["model_id"];
cbTypes.SelectedValue = reader["type_id"];
cbModels.Enabled = true;
//czy wartość reader od image nie jest dbnull - czyli czy odczytywana
//z bazy danych wartość nie jest nullowa
if (!(reader["image"] is DBNull))
{
//image - sórowka - zawartośc image
byte[] b = (byte[])reader["image"];
//wszystkie dane mamy wymagane, ale obrazek jest w bazie opcjonalny
//więc trzeba zrobić if, aby apka nie wywaliła się
if (b != null && b.Length > 0)
{
//tworzenie strumienia
using (MemoryStream ms = new MemoryStream(b))
{ //tworzenie obiektu typu image przypisaywany do naszego obrazka w picture boxie
picCar.Image = Image.FromStream(ms);
}
}
}
//readera trzeba zamknąć
reader.Close();
}
}
} catch (Exception exc)
{
DialogHelper.Error(exc.Message);
}
//zmiana napisów na przyciskach i oknie
if (RowId > 0)
{
btnOk.Text = "Zapisz zmiany";
Text = "Edycja pojazdu";
}
else
{
btnOk.Text = "Dodaj";
Text = "Nowy pojazd";
}
}
private void SaveDate()
{
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,
image = @image,
fuel = @fuel
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 = cbModels.SelectedValue;
cmd.Parameters["@type_id"].Value = cbTypes.SelectedValue;
cmd.Parameters["@reg_plate"].Value = tbRegPlate.Text.Trim();
cmd.Parameters["@year"].Value = numYear.Value;
cmd.Parameters["@engine"].Value = numEngine.Value;
cmd.Parameters["@fuel"].Value = cbFuel.SelectedItem; //bo paliwo mamy zdefiniowane jako kolekcje (wpisywaliśmy z lapy listę on, lpg, pb)
cmd.Parameters["@row_id"].Value = RowId;
//jesli plik jest
if (pictureFileName != null && File.Exists(pictureFileName))
{
cmd.Parameters["@image"].Value = File.ReadAllBytes(pictureFileName);
}
else //jesli nie ma
{
cmd.Parameters["@image"].Value = null;
}
//wywołanie zapytania
cmd.ExecuteNonQuery();
DialogResult = DialogResult.OK;
Close();
} catch (Exception exc)
{
DialogHelper.Error(exc.Message);
}
}
private void btnOK_Click(object sender, EventArgs e)
{
MySqlTransaction tr = null;
try
{
string sql = "";
if (!OperBack)
{
// zakladamy nowy rekord
sql = @"
insert into operations
(car_id, ts_out, mileage_out, description)
values
(@car_id, @ts, @mileage, @descr)
";
}
else
{
// aktualizojemy ostatni rekord operacji
sql = @"
update operations SET
ts_in=@ts, mileage_in=@mileage, description=@descr
where
id=@id;
";
}
tr = GlobalData.connection.BeginTransaction();
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 = nudMilage.Value;
cmd.Parameters.Add("@descr", MySqlDbType.Text).Value = tbDescr.Text;
cmd.ExecuteNonQuery();
// update kolumny avail w tablicy Cars
sql = @"UPDATE cars SET avail=@avail WHERE id=@id";
cmd = new MySqlCommand(sql, GlobalData.connection);
cmd.Transaction = tr;
cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = CarId;
cmd.Parameters.Add("@avail", MySqlDbType.Int32).Value = (OperBack) ? 1 : 0;
cmd.ExecuteNonQuery();
tr.Commit();
DialogResult = DialogResult.OK;
Close();
} catch (Exception exc)
{
if (tr != null)
{
tr.Rollback();
}
DialogHelper.Error(exc.Message);
}
}
private void btnOk_Click(object sender, EventArgs e)
{
// document
String filename = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "rent-a-car.docx");
DocX document = DocX.Load(filename);
document.ReplaceText("{REG_PLATE}", RegPlate);
document.ReplaceText("{MILAGE}", 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", RegPlate + " -" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".docx");
document.SaveAs(filename);
MySqlTransaction tr = null;
try
{ //transakcja
tr = GlobalData.connection.BeginTransaction();
String sql;
int avail;
if (OperBack == false)
{
//wydaj auto i 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; //.value pozwala przypisać wartość odrazu przy tworzeniu parametru
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; //podpinamy transakcje pod komende
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.Error(exc.Message);
}
}