public void Report(RentedCar rc, double mileage)
{
String firstQuery = String.Format("INSERT INTO {0}.archive (Car_ID, SName, FName, TName, FDay, LDay, TotalHours, Price) VALUES ({1}, '{2}', '{3}', '{4}', '{5}', '{6}', {7}, {8}) ", DataBase, rc.Car_ID, rc.Sname, rc.Fname, rc.Tname, rc.FDate.ToString("yyyy-MM-dd H:mm:ss"), rc.LDate.ToString("yyyy-MM-dd H:mm:ss"), rc.Hours, rc.Price);
MySqlCommand firstCmd = new MySqlCommand(firstQuery, serverconn);
String secondQuery = String.Format("INSERT INTO {0}.report (Car_ID, Mileage, TotalHours, Income) VALUES ({1}, {2}, {3}, {4}) ON DUPLICATE KEY UPDATE Mileage = Mileage + {2}, TotalHours = TotalHours + {3}, Income = Income + {4} ", DataBase, rc.Car_ID, mileage, rc.Hours, rc.Price);
MySqlCommand secondCmd = new MySqlCommand(secondQuery, serverconn);
try
{
serverconn.Open();
firstCmd.ExecuteNonQuery();
secondCmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
switch (ex.Number)
{
case 1062:
MessageBox.Show("Елемент з таким ім'ям вже існує в базі");
break;
}
}
finally
{
serverconn.Close();
}
}
public void RentCar(RentedCar rc)
{
String firstQuery = String.Format("INSERT INTO {0}.rented_car (Car_ID, SName, FName, TName, Passport, ID_Number, License, FDay, LDay, TotalHours, Price) VALUES ({1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',{10}, {11}) ", DataBase, rc.Car_ID, rc.Sname, rc.Fname, rc.Tname, rc.Passport, rc.Id_number, rc.License, rc.FDate.ToString("yyyy-MM-dd H:mm:ss"), rc.LDate.ToString("yyyy-MM-dd H:mm:ss"), rc.Hours, rc.Price);
MySqlCommand firstCmd = new MySqlCommand(firstQuery, serverconn);
try
{
serverconn.Open();
firstCmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
switch (ex.Number)
{
case 1042:
MessageBox.Show("Немає доступу до серверу");
break;
case 1062:
MessageBox.Show("Елемент з таким ім'ям вже існує в базі");
break;
}
}
finally
{
serverconn.Close();
}
}
public void CheckStatus(DateTime dt)
{
string title = "Статус";
string text = "";
List <RentedCar> activeList = new List <RentedCar>();
List <RentedCar> finishedList = new List <RentedCar>();
RentedCar rc = new RentedCar();
int activeCount = 0;
int finishedCount = 0;
String query = String.Format("SELECT * FROM {0}.rented_car", DataBase);
MySqlCommand cmd = new MySqlCommand(query, serverconn);
try
{
serverconn.Open();
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
rc.ID = int.Parse(reader["ID"].ToString());
rc.FDate = Convert.ToDateTime(reader["FDay"].ToString());
rc.LDate = Convert.ToDateTime(reader["Lday"].ToString());
if (rc.LDate <= dt)
{
finishedCount++;
finishedList.Add(rc);
}
else if (rc.FDate <= dt)
{
activeCount++;
activeList.Add(rc);
}
}
if (activeCount > 0)
{
text = "К-ть активних оренд : " + activeCount.ToString();
nicon.ShowBalloonTip(5000, title, text, System.Windows.Forms.ToolTipIcon.Info);
}
if (finishedCount > 0)
{
text = "К-ть завершених оренд : " + finishedCount.ToString();
nicon.ShowBalloonTip(5000, title, text, System.Windows.Forms.ToolTipIcon.Info);
}
}
catch (MySqlException ex)
{
switch (ex.Number)
{
case 1042:
MessageBox.Show("Немає доступу до серверу");
break;
}
}
finally
{
serverconn.Close();
}
ChangeStatus(activeList, 1);
ChangeStatus(finishedList, 2);
}
public void CarRented(DateTime dt)
{
string title = "Прокат";
string text = "";
List <RentedCar> activeList = new List <RentedCar>();
List <RentedCar> finishedList = new List <RentedCar>();
RentedCar rc = new RentedCar();
String query = String.Format("SELECT * FROM {0}.rented_car", DataBase);
MySqlCommand cmd = new MySqlCommand(query, serverconn);
try
{
serverconn.Open();
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
rc.ID = int.Parse(reader["ID"].ToString());
rc.Fname = reader["FName"].ToString();
rc.Sname = reader["SName"].ToString();
rc.Tname = reader["TName"].ToString();
rc.FDate = Convert.ToDateTime(reader["FDay"].ToString());
rc.LDate = Convert.ToDateTime(reader["Lday"].ToString());
if (rc.LDate <= dt)
{
text = String.Format("Прокат закінчився ({0} {1} {2})", rc.Sname, rc.Fname, rc.Tname);
finishedList.Add(rc);
}
else if (rc.FDate <= dt)
{
text = String.Format("Прокат почався ({0} {1} {2})", rc.Sname, rc.Fname, rc.Tname);
activeList.Add(rc);
}
nicon.ShowBalloonTip(5000, title, text, System.Windows.Forms.ToolTipIcon.Info);
}
}
catch (MySqlException ex)
{
switch (ex.Number)
{
case 1042:
MessageBox.Show("Немає доступу до серверу");
break;
}
}
finally
{
serverconn.Close();
}
ChangeStatus(activeList, 1);
ChangeStatus(finishedList, 2);
}
private void Report_click(object sender, RoutedEventArgs e)
{
DataRowView row = dataGrid.SelectedItem as DataRowView;
if (row != null)
{
int value = int.Parse(row.Row.ItemArray[0].ToString());
RentedCar rc = s.GetRentedCarInfo(value);
if (rc != null)
{
rw = new ReportWindow(s, rc);
if (rw.ShowDialog() == true)
{
UpdateTable();
}
}
}
}
private void RentButton_Click(object sender, RoutedEventArgs e)
{
if (SNameBox.Text != "" && FNameBox.Text != "" && TNameBox.Text != "" && PassportBox.Text != "" && IDBox.Text != "" &&
LicenseBox.Text != "" && PriceBox.Text != "" && TimeFBox.SelectedIndex != -1 && TimeLBox.SelectedIndex != -1 &&
TimeF.SelectedDate != null && TimeL.SelectedDate != null)
{
TimeSpan span = TimeSpan.Zero;
DateTime dt = new DateTime(TimeF.SelectedDate.Value.Year, TimeF.SelectedDate.Value.Month, TimeF.SelectedDate.Value.Day, (TimeFBox.SelectedIndex + 8), 0, 0);
DateTime dt1 = new DateTime(TimeL.SelectedDate.Value.Year, TimeL.SelectedDate.Value.Month, TimeL.SelectedDate.Value.Day, (TimeLBox.SelectedIndex + 8), 0, 0);
if (dt >= dt1)
{
MessageBox.Show("Неправильно введений час");
}
else
{
span = dt1.Subtract(dt);
}
RentedCar rc = new RentedCar();
rc.Car_ID = c.Id;
rc.Sname = SNameBox.Text;
rc.Fname = FNameBox.Text;
rc.Tname = TNameBox.Text;
rc.Passport = PassportBox.Text;
rc.Id_number = IDBox.Text;
rc.License = LicenseBox.Text;
rc.FDate = dt;
rc.LDate = dt1;
rc.Hours = int.Parse(span.TotalHours.ToString());
rc.Price = int.Parse(PriceBox.Text);
if (!s.CheckTime(rc))
{
s.RentCar(rc);
DialogResult = true;
}
}
else
{
MessageBox.Show("Одне або декілька полів вводу пусті");
}
}
public RentedCar GetRentedCarInfo(int ID)
{
RentedCar rc = new RentedCar();
String query = String.Format("SELECT Car_ID, SName, FName, TName, FDay, LDay, TotalHours, Price FROM {0}.rented_car WHERE ID = {1}", DataBase, ID);
MySqlCommand cmd = new MySqlCommand(query, serverconn);
try
{
serverconn.Open();
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
rc.ID = ID;
rc.Car_ID = int.Parse(reader["Car_ID"].ToString());
rc.Sname = reader["SName"].ToString();
rc.Fname = reader["FName"].ToString();
rc.Tname = reader["TName"].ToString();
rc.FDate = Convert.ToDateTime(reader["FDay"].ToString());
rc.LDate = Convert.ToDateTime(reader["Lday"].ToString());
rc.Hours = int.Parse(reader["TotalHours"].ToString());
rc.Price = int.Parse(reader["Price"].ToString());
}
catch (MySqlException ex)
{
switch (ex.Number)
{
case 1042:
{
MessageBox.Show("Немає доступу до бази даних");
rc = null;
}
break;
}
}
finally
{
serverconn.Close();
}
return(rc);
}
public bool CheckTime(RentedCar newCar)
{
RentedCar rc = new RentedCar();
String query = String.Format("SELECT * FROM {0}.rented_car", DataBase);
MySqlCommand cmd = new MySqlCommand(query, serverconn);
try
{
serverconn.Open();
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
rc.FDate = Convert.ToDateTime(reader["FDay"].ToString());
rc.LDate = Convert.ToDateTime(reader["Lday"].ToString());
if (!((newCar.FDate >= rc.LDate) || (newCar.LDate <= rc.FDate)))
{
MessageBox.Show("Час зайнятий");
return(true);
}
}
return(false);
}
catch (MySqlException ex)
{
switch (ex.Number)
{
case 1042: MessageBox.Show("Немає доступу до бази даних");
break;
}
return(false);
}
finally
{
serverconn.Close();
}
}
public ReportWindow(Session s, RentedCar rc)
{
InitializeComponent();
this.s = s;
this.rc = rc;
}