/// <summary>
/// Új yacht beírása az adatbázisba
/// </summary>
/// <param name="yacht"></param>
/// <returns></returns>
public bool MysqlAddYacht(Yacht yacht)
{
try
{
string query = "INSERT INTO enYacht(yacht_id, name, producer, ower, seats, width, lenght, dive, speed) VALUES (?yacht_id, ?name, ?producer, ?ower, ?seats, ?width, ?lenght, ?dive, ?speed);";
Globals.connect.Open();
using (MySqlCommand cmd = new MySqlCommand(query, Globals.connect))
{
cmd.Parameters.Add("?yacht_id", MySqlDbType.Int16).Value = MysqlGeneral.MysqlNextId("enYacht", "yacht_id");
cmd.Parameters.Add("?name", MySqlDbType.VarChar).Value = yacht.nev;
cmd.Parameters.Add("?producer", MySqlDbType.VarChar).Value = yacht.gyarto;
cmd.Parameters.Add("?ower", MySqlDbType.Int16).Value = yacht.member_id;
cmd.Parameters.Add("?seats", MySqlDbType.Int16).Value = yacht.ferohely;
cmd.Parameters.Add("?width", MySqlDbType.Float).Value = yacht.szeles;
cmd.Parameters.Add("?lenght", MySqlDbType.Float).Value = yacht.hossz;
cmd.Parameters.Add("?dive", MySqlDbType.Float).Value = yacht.merules;
cmd.Parameters.Add("?speed", MySqlDbType.Int16).Value = yacht.sebesseg;
cmd.ExecuteNonQuery();
}
}
catch (MySqlException ex)
{
MessageBox.Show("Error in adding mysql row. Error: " + ex.Message);
return(false);
}
finally
{
Globals.connect.Close();
}
return(true);
}
/// <summary>
/// Kikeresi az adatbázisból egy yachtnak a képét
/// </summary>
/// <param name="id">yacht id</param>
/// <returns>yacht kép</returns>
public BitmapImage MysqlYachtImage(int id)
{
BitmapImage image = null;
try
{
string query = "SELECT Image FROM enYacht WHERE yacht_id = ?yacht_id;";
Globals.connect.Open();
using (MySqlCommand cmd = new MySqlCommand(query, Globals.connect))
{
cmd.Parameters.Add("?yacht_id", MySqlDbType.Int16).Value = id;
try
{ image = MysqlGeneral.ByteToImage((byte[])cmd.ExecuteScalar()); }
catch (Exception)
{ image = null; }
}
}
catch (MySqlException ex)
{
MessageBox.Show("Error in adding mysql row. Error: " + ex.Message);
}
finally
{
Globals.connect.Close();
}
return(image);
}
/// <summary>
/// Új szállitóeszköz beírása az adatbázisba
/// </summary>
/// <param name="device"></param>
/// <returns></returns>
public int MysqlAddDevice(Device device)
{
int id = 0;
try
{
string query = "INSERT INTO enDevice(device_id, type, ower, max_lenght, max_weight) VALUES (?device_id, ?type, ?ower, ?max_lenght, ?max_weight);";
Globals.connect.Open();
using (MySqlCommand cmd = new MySqlCommand(query, Globals.connect))
{
id = MysqlGeneral.MysqlNextId("enDevice", "device_id");
cmd.Parameters.Add("?device_id", MySqlDbType.Int16).Value = id;
cmd.Parameters.Add("?type", MySqlDbType.VarChar).Value = device.tipus;
cmd.Parameters.Add("?ower", MySqlDbType.Int16).Value = device.member_id;
cmd.Parameters.Add("?max_lenght", MySqlDbType.VarChar).Value = device.max_hossz;
cmd.Parameters.Add("?max_weight", MySqlDbType.Int16).Value = device.max_suly;
cmd.ExecuteNonQuery();
}
}
catch (MySqlException)
{
return(0);
}
finally
{
Globals.connect.Close();
}
return(id);
}
/// <summary>
/// Új Felhasználói login beírása a Login táblába
/// </summary>
/// <param name="regisztration">Az új felhasználó</param>
public void MysqlRegisztracioLogin(Felhasznalo regisztration)
{
try
{
string query = "INSERT INTO enLogin(login_id, login_name, password, email, admin) VALUES (?login_id, ?login_name,?password,?email, ?admin);";
Globals.connect.Open();
int id = MysqlGeneral.MysqlNextId("enLogin", "login_id");
using (MySqlCommand cmd = new MySqlCommand(query, Globals.connect))
{
cmd.Parameters.Add("?login_id", MySqlDbType.Int16).Value = id;
cmd.Parameters.Add("?login_name", MySqlDbType.VarChar).Value = regisztration.login.felhasznalonev;
cmd.Parameters.Add("?password", MySqlDbType.VarChar).Value = Globals.Encryption(regisztration.login.jelszo);
cmd.Parameters.Add("?email", MySqlDbType.VarChar).Value = regisztration.login.email;
cmd.Parameters.Add("?admin", MySqlDbType.Bit).Value = regisztration.login.admin ? 1 : 0;
cmd.ExecuteNonQuery();
}
MysqlRegisztracionUser(regisztration, id);
}
catch (MySqlException ex)
{
MessageBox.Show("Error in adding mysql row. Error: " + ex.Message);
}
finally
{
Globals.connect.Close();
}
}
/// <summary>
/// Bejelentkezés és az felhasználó adatainak a beolvasása
/// </summary>
/// <param name="login_name">Login név</param>
/// <param name="password">Jelszó</param>
/// <returns></returns>
public Felhasznalo MysqlFelhasznalo(string login_name, string password)
{
Felhasznalo user = new Felhasznalo();
user.login = Mysql_Login(login_name, password);
try
{
if (user.login != null)
{
string query = "SELECT * FROM enYacht_Club_Tag INNER JOIN enzipcode USING(zip_code) WHERE member_id = ?member_id;";
using (MySqlCommand cmd = new MySqlCommand(query, Globals.connect))
{
cmd.Parameters.Add("?member_id", MySqlDbType.Int32).Value = user.login.id;
MySqlDataReader read = cmd.ExecuteReader();
while (read.Read())
{
user.member_id = (int)read["member_id"];
user.nickname = read["nickname"].ToString();
user.veztek_nev = read["last_name"].ToString();
user.kereszt_nev = read["first_name"].ToString();
user.orszag = read["country"].ToString();
user.lakcim = read["address"].ToString();
user.iranyitoszm = (int)read["zip_code"];
user.varos = read["city"].ToString();
user.szuletesdt = DateTime.Parse(read["birthday"].ToString());
try
{ user.kep = MysqlGeneral.ByteToImage((byte[])read["image"]); }
catch (Exception)
{ user.kep = null; }
}
}
}
else
{
return(null);
}
}
catch (MySqlException ex)
{
MessageBox.Show("Error in adding mysql row. Error: " + ex.Message);
}
finally
{
Globals.connect.Close();
}
return(user);
}
public void MysqlNewMessage(Message uzenet)
{
try
{
string query = "INSERT INTO enMessage(message_id, price, sender, addressee, yacht_id, device_id, start_date, end_date, from_port, to_port) VALUES (?message_id, ?price, ?sender, ?addressee, ?yacht_id, ?device_id, ?start_date, ?end_date, ?from_port, ?to_port);";
Globals.connect.Open();
int id = MysqlGeneral.MysqlNextId("enMessage", "message_id");
using (MySqlCommand cmd = new MySqlCommand(query, Globals.connect))
{
cmd.Parameters.Add("?message_id", MySqlDbType.Int16).Value = id;
cmd.Parameters.Add("?price", MySqlDbType.Int16).Value = uzenet.price;
cmd.Parameters.Add("?sender", MySqlDbType.Int16).Value = Globals.User.member_id;
cmd.Parameters.Add("?addressee", MySqlDbType.Int16).Value = uzenet.cimzett_id;
if (uzenet.yacht_id != -1)
{
cmd.Parameters.Add("?yacht_id", MySqlDbType.Int16).Value = uzenet.yacht_id;
cmd.Parameters.Add("?device_id", MySqlDbType.Int16).Value = DBNull.Value;
}
else if (uzenet.device_id != -1)
{
cmd.Parameters.Add("?yacht_id", MySqlDbType.Int16).Value = DBNull.Value;
cmd.Parameters.Add("?device_id", MySqlDbType.Int16).Value = uzenet.device_id;
}
if (uzenet.honnan_id != -1 && uzenet.hova_id != -1)
{
cmd.Parameters.Add("?from_port", MySqlDbType.Int16).Value = uzenet.honnan_id;
cmd.Parameters.Add("?to_port", MySqlDbType.Int16).Value = uzenet.hova_id;
}
else
{
cmd.Parameters.Add("?from_port", MySqlDbType.Int16).Value = DBNull.Value;
cmd.Parameters.Add("?to_port", MySqlDbType.Int16).Value = DBNull.Value;
}
cmd.Parameters.Add("?start_date", MySqlDbType.DateTime).Value = uzenet.kezdete;
cmd.Parameters.Add("?end_date", MySqlDbType.DateTime).Value = uzenet.vege;
cmd.ExecuteNonQuery();
}
}
catch (MySqlException ex)
{
MessageBox.Show("Error in adding mysql row. Error: " + ex.Message);
}
finally
{
Globals.connect.Close();
}
}
/// <summary>
/// Yacht adatok frissitése az adatbázisba
/// </summary>
/// <param name="UpdateYacht"></param>
public void MysqlUpdateYacht(Yacht UpdateYacht)
{
try
{
string query = "UPDATE enYacht SET name = ?name, producer = ?producer, image = ?image, seats = ?seats, hire = ?hire, busy = ?busy, daly_price = ?daly_price, width = ?width, lenght = ?lenght, dive = ?dive, speed = ?speed, port_id = ?port_id WHERE yacht_id = ?yacht_id;";
Globals.connect.Open();
using (MySqlCommand cmd = new MySqlCommand(query, Globals.connect))
{
cmd.Parameters.Add("?yacht_id", MySqlDbType.Int16).Value = UpdateYacht.id;
cmd.Parameters.Add("?name", MySqlDbType.VarChar).Value = UpdateYacht.nev;
cmd.Parameters.Add("?producer", MySqlDbType.VarChar).Value = UpdateYacht.gyarto;
if (UpdateYacht.kep != null)
{
cmd.Parameters.Add("?image", MySqlDbType.LongBlob).Value = MysqlGeneral.ImageToByte(UpdateYacht.kep);
}
else
{
cmd.Parameters.Add("?image", MySqlDbType.LongBlob).Value = DBNull.Value;
}
cmd.Parameters.Add("?seats", MySqlDbType.Int16).Value = UpdateYacht.ferohely;
cmd.Parameters.Add("?hire", MySqlDbType.Bit).Value = UpdateYacht.berelheto;
cmd.Parameters.Add("?busy", MySqlDbType.Bit).Value = UpdateYacht.blfoglalt;
cmd.Parameters.Add("?daly_price", MySqlDbType.Int16).Value = UpdateYacht.napi_ar;
cmd.Parameters.Add("?width", MySqlDbType.Float).Value = UpdateYacht.szeles;
cmd.Parameters.Add("?lenght", MySqlDbType.Float).Value = UpdateYacht.hossz;
cmd.Parameters.Add("?dive", MySqlDbType.Float).Value = UpdateYacht.merules;
cmd.Parameters.Add("?speed", MySqlDbType.Int16).Value = UpdateYacht.sebesseg;
cmd.Parameters.Add("?port_id", MySqlDbType.Int16).Value = UpdateYacht.kikoto_id;
cmd.ExecuteNonQuery();
}
}
catch (MySqlException e)
{
Globals.log = "Sikertelen módosítás!";
MessageBox.Show(e.ToString(), e.ToString(), MessageBoxButton.OK);
}
finally
{
Globals.connect.Close();
}
}
/// <summary>
/// Felhasználói rekord frissitése
/// </summary>
/// <param name="UpdateUser">A frissteni kivánt felhasználó</param>
public void MysqlUpdateUser(Felhasznalo UpdateUser)
{
try
{
string query = "UPDATE enYacht_Club_Tag SET nickname = ?nickname, last_name = ?last_name, first_name = ?first_name, birthday = ?birthday, zip_code = ?zip_code, address = ?address, country = ?country, image = ?image WHERE member_id = ?member_id;";
Globals.connect.Open();
using (MySqlCommand cmd = new MySqlCommand(query, Globals.connect))
{
cmd.Parameters.Add("?nickname", MySqlDbType.VarChar).Value = UpdateUser.nickname;
cmd.Parameters.Add("?last_name", MySqlDbType.VarChar).Value = UpdateUser.kereszt_nev;
cmd.Parameters.Add("?first_name", MySqlDbType.VarChar).Value = UpdateUser.veztek_nev;
cmd.Parameters.Add("?birthday", MySqlDbType.DateTime).Value = UpdateUser.szuletesdt;
cmd.Parameters.Add("?zip_code", MySqlDbType.Int16).Value = UpdateUser.iranyitoszm;
cmd.Parameters.Add("?address", MySqlDbType.VarChar).Value = UpdateUser.lakcim;
cmd.Parameters.Add("?country", MySqlDbType.VarChar).Value = UpdateUser.orszag;
if (MysqlGeneral.ImageToByte(UpdateUser.kep) != null)
{
cmd.Parameters.Add("?image", MySqlDbType.LongBlob).Value = MysqlGeneral.ImageToByte(UpdateUser.kep);
}
else
{
cmd.Parameters.Add("?image", MySqlDbType.LongBlob).Value = DBNull.Value;
}
cmd.Parameters.Add("?member_id", MySqlDbType.Int16).Value = UpdateUser.member_id;
cmd.ExecuteNonQuery();
}
}
catch (Exception)
{
Globals.log = "Sikertelen módosítás!";
}
finally
{
Globals.connect.Close();
}
}
/// <summary>
/// Új felhasználó beszurása a YachClubTag táblába
/// </summary>
/// <param name="regisztration">A felhasználó adatai</param>
/// <param name="id">A hozzá tartozó login id</param>
private void MysqlRegisztracionUser(Felhasznalo regisztration, int id)
{
try
{
string query = "INSERT INTO enYacht_Club_Tag(member_id, login_id, nickname, last_name, first_name, birthday, zip_code, address, country) VALUES (?member_id, ?login_id, ?nickname, ?last_name, ?first_name, ?birthday, ?zip_code, ?address, ?country);";
using (MySqlCommand cmd = new MySqlCommand(query, Globals.connect))
{
cmd.Parameters.Add("?member_id", MySqlDbType.Int16).Value = MysqlGeneral.MysqlNextId("enYacht_Club_Tag", "member_id");
cmd.Parameters.Add("?login_id", MySqlDbType.Int16).Value = id;
cmd.Parameters.Add("?nickname", MySqlDbType.VarChar).Value = regisztration.nickname;
cmd.Parameters.Add("?last_name", MySqlDbType.VarChar).Value = regisztration.kereszt_nev;
cmd.Parameters.Add("?first_name", MySqlDbType.VarChar).Value = regisztration.veztek_nev;
cmd.Parameters.Add("?birthday", MySqlDbType.DateTime).Value = regisztration.szuletesdt;
cmd.Parameters.Add("?zip_code", MySqlDbType.Int16).Value = regisztration.iranyitoszm;
cmd.Parameters.Add("?address", MySqlDbType.VarChar).Value = regisztration.lakcim;
cmd.Parameters.Add("?country", MySqlDbType.VarChar).Value = regisztration.orszag;
cmd.ExecuteNonQuery();
}
}
catch (MySqlException ex)
{
MessageBox.Show("Error in adding mysql row. Error: " + ex.Message);
}
}