public Citizens(int i)
{
InitializeComponent();
App.LastSelect = "SELECT COUNT(irs_number) AS 'Population', city as 'City' FROM employee GROUP BY city HAVING (COUNT(IRS_NUMBER)>=" + i + ") ORDER BY (COUNT(IRS_NUMBER)) DESC;";
App.DataGrid = DataGrid;
App.RefreshDataGrid();
}
public Works(DataRowView drv)
{
InitializeComponent();
_drv = drv;
App.LastSelect = "SELECT works.*, employee.lastname as 'Surname' FROM Works INNER join employee on employee.irs_number = works.irs_number where storeid=" + _drv["storeid"].ToString() + ";";
App.DataGrid = DataGrid;
App.RefreshDataGrid();
}
public Kilometers(int km)
{
InitializeComponent();
App.LastSelect = "SELECT * from vehicle group by Licenseplate having (kilometers<=" + km + ") ORDER BY kilometers ASC;";
App.DataGrid = DataGrid;
App.RefreshDataGrid();
}
public HorsePower()
{
InitializeComponent();
App.LastSelect = "SELECT * from horsepower;";
App.DataGrid = DataGrid;
App.RefreshDataGrid();
}
public NumberOfCars()
{
InitializeComponent();
App.LastSelect = "SELECT * from numberofcars;";
App.DataGrid = DataGrid;
App.RefreshDataGrid();
}
public MainWindow()
{
InitializeComponent();
InitializeTablesComboBox();
App.DataGrid = DataGrid;
App.RefreshDataGrid();
}
public PhonesWindow(DataRowView drv)
{
InitializeComponent();
_drv = drv;
App.LastSelect = "SELECT num as 'Numbers:' FROM phonenumber where storeid=" + _drv["storeid"].ToString() + ";";
App.DataGrid = DataGrid;
App.RefreshDataGrid();
}
private void Add_OnClick(object sender, RoutedEventArgs e)
{
if (Check())
{
return;
}
App.RunCommand("select * from employee where irs_number=" + int.Parse(IRS_numberTextBox.Text) + ";");
if (App.DataTable.Rows.Count == 0)
{
MessageBox.Show("This employee does not exist!", "Error");
IRS_numberTextBox.Text = "";
return;
}
string Startdate, Finisdate;
Startdate = "'" + StartDateTextBox.Text + "'";
if (string.IsNullOrWhiteSpace(FinishDateTextBox.Text))
{
Finisdate = "null";
}
else
{
Finisdate = "'" + FinishDateTextBox.Text + "'";
}
App.RunCommand("select * from works where Irs_number='" + int.Parse(IRS_numberTextBox.Text) + "' && startdate=" + Startdate + " && storeid='" + _ID + "';");
if (App.DataTable.Rows.Count != 0)
{
MessageBox.Show("This employee alread worked at that store at that specific date!", "Error");
IRS_numberTextBox.Text = "";
return;
}
try
{
App.RunCommand("Insert into works(storeID,startdate, finishdate, position, irs_number)" +
"values" +
"(" + _ID + "," +
"" + Startdate + "," +
"" + Finisdate + "," +
"'" + PositionTextBox.Text + "'," +
"" + int.Parse(IRS_numberTextBox.Text) + ");");
App.RefreshDataGrid();
MessageBox.Show("Employer added succesfully!", "Success");
Close();
}
catch
{
MessageBox.Show("Something went wrong!", "Error");
}
}
private void Update_OnClick(object sender, RoutedEventArgs e)
{
if (_drv.DataView.Table.ToString() == "employee")
{
App.RunCommand("select * from employee where irs_number=" + int.Parse(EmployeeIRSNumberTextBox.Text));
if (App.DataTable.Rows.Count != 0 && int.Parse(EmployeeIRSNumberTextBox.Text) != int.Parse(_drv["irs_number"].ToString()))
{
MessageBox.Show("Employee with IRS Number " + EmployeeIRSNumberTextBox.Text + " already exists!", "Error");
EmployeeIRSNumberTextBox.Text = "";
return;
}
App.RunCommand("update employee set firstname='" + EmployeeFirstNameTextBox.Text + "' where employee.irs_number=" + int.Parse(_drv["irs_number"].ToString()) + ";");
App.RunCommand("update employee set lastname='" + EmployeeLastNameTextBox.Text + "' where employee.irs_number=" + int.Parse(_drv["irs_number"].ToString()) + ";");
App.RunCommand("update employee set street='" + EmployeeStreetTextBox.Text + "' where employee.irs_number=" + int.Parse(_drv["irs_number"].ToString()) + ";");
App.RunCommand("update employee set streetnumber='" + EmployeeNumberTextBox.Text + "' where employee.irs_number=" + int.Parse(_drv["irs_number"].ToString()) + ";");
App.RunCommand("update employee set postalcode='" + EmployeePostalCodeTextBox.Text + "' where employee.irs_number=" + int.Parse(_drv["irs_number"].ToString()) + ";");
App.RunCommand("update employee set city='" + EmployeeCityTextBox.Text + "' where employee.irs_number=" + int.Parse(_drv["irs_number"].ToString()) + ";");
App.RunCommand("update employee set socialSecurityNumber='" + EmployeeSocialSecurityNumberTextBox.Text + "' where employee.irs_number=" + int.Parse(_drv["irs_number"].ToString()) + ";");
App.RunCommand("update employee set driverlicense='" + EmployeeDriverLicenceTextBox.Text + "' where employee.irs_number=" + int.Parse(_drv["irs_number"].ToString()) + ";");
App.RunCommand("update employee set irs_number='" + int.Parse(EmployeeIRSNumberTextBox.Text) + "' where employee.irs_number=" + int.Parse(_drv["irs_number"].ToString()) + ";");
App.RefreshDataGrid();
MessageBox.Show("Employee updated succesfully!", "Success");
Close();
}
else if (_drv.DataView.Table.ToString() == "store")
{
App.RunCommand("select * from store where storeid=" + int.Parse(StoreIDTextBox.Text));
if (App.DataTable.Rows.Count != 0 && int.Parse(StoreIDTextBox.Text) != int.Parse(_drv["Storeid"].ToString()))
{
MessageBox.Show("Store with ID Number " + StoreIDTextBox.Text + " already exists!", "Error");
StoreIDTextBox.Text = "";
return;
}
App.RunCommand("update store set street='" + StoreStreetTextBox.Text + "' where store.storeid=" + int.Parse(_drv["storeid"].ToString()) + ";");
App.RunCommand("update store set streetnumber='" + StoreStreetNumberTextBox.Text + "' where store.storeid=" + int.Parse(_drv["storeid"].ToString()) + ";");
App.RunCommand("update store set postalcode='" + StorePostalCodeTextBox.Text + "' where store.storeid=" + int.Parse(_drv["storeid"].ToString()) + ";");
App.RunCommand("update store set city='" + StoreCityTextBox.Text + "' where store.storeid=" + int.Parse(_drv["storeid"].ToString()) + ";");
App.RunCommand("update store set storeid='" + int.Parse(StoreIDTextBox.Text) + "' where store.storeid=" + int.Parse(_drv["storeid"].ToString()) + ";");
App.RefreshDataGrid();
MessageBox.Show("Store updated succesfully!", "Success");
Close();
}
}
private void Delete_OnClick(object sender, RoutedEventArgs e)
{
if (DataGrid.SelectedItem != null)
{
var test = (DataRowView)DataGrid.SelectedItem;
var record = int.Parse(test["Numbers:"].ToString());
if (MessageBox.Show("Delete number " + record + "?", "Warning", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.Yes)
{
try
{
App.RunCommand("DELETE FROM phonenumber WHERE num LIKE '" + record + "';");
MessageBox.Show("Number deleted succesfully!", "Success");
App.LastSelect = "SELECT num as 'Numbers:' FROM phonenumber where storeid=" + _drv["storeid"].ToString() + ";";
App.RefreshDataGrid();
}
catch
{
MessageBox.Show("Error deleting record", "Error");
}
}
}
}
private void TablesComboBox_OnSelectionChanged(object sender, SelectionChangedEventArgs e)
{
if (TablesComboBox.SelectedItem.ToString() == "Employee")
{
City.Visibility = Visibility.Visible;
}
else
{
City.Visibility = Visibility.Collapsed;
}
if (TablesComboBox.SelectedItem.ToString() == "Store")
{
ViewPhones.Visibility = Visibility.Visible;
ViewEmails.Visibility = Visibility.Visible;
ViewEmployees.Visibility = Visibility.Visible;
Numberofcars.Visibility = Visibility.Visible;
}
else
{
ViewPhones.Visibility = Visibility.Collapsed;
ViewEmails.Visibility = Visibility.Collapsed;
ViewEmployees.Visibility = Visibility.Collapsed;
Numberofcars.Visibility = Visibility.Collapsed;
}
if (TablesComboBox.SelectedItem.ToString() == "Vehicle")
{
Horsepower.Visibility = Visibility.Visible;
FindVehicle.Visibility = Visibility.Visible;
}
else
{
Horsepower.Visibility = Visibility.Collapsed;
FindVehicle.Visibility = Visibility.Collapsed;
}
App.LastSelect = "SELECT * FROM " + TablesComboBox.SelectedItem.ToString() + ";";
App.DataGrid = DataGrid;
App.RefreshDataGrid();
}
private void Delete_OnClick(object sender, RoutedEventArgs e)
{
if (DataGrid.SelectedItem != null)
{
var test = (DataRowView)DataGrid.SelectedItem;
var record = int.Parse(test["IRS_Number"].ToString());
var date = DateTime.Parse(test["startDate"].ToString());
if (MessageBox.Show("Delete Employee with IRS number: " + record + "?", "Warning", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.Yes)
{
try
{
App.RunCommand("DELETE FROM Works WHERE irs_number LIKE '" + record + "' && startdate LIKE '" + date.ToString("yyyy-MM-dd") + "';");
MessageBox.Show("Employee deleted succesfully!", "Success");
App.LastSelect = "SELECT * FROM works where storeid=" + _drv["storeid"].ToString() + ";";
App.RefreshDataGrid();
}
catch
{
MessageBox.Show("Error deleting record", "Error");
}
}
}
}
private void Add_OnClick(object sender, RoutedEventArgs e)
{
if (string.IsNullOrWhiteSpace(PhoneNumberTextBox.Text))
{
MessageBox.Show("Number is empty!");
return;
}
App.RunCommand("select * from phonenumber where num=" + int.Parse(PhoneNumberTextBox.Text));
if (App.DataTable.Rows.Count != 0)
{
MessageBox.Show("This number already exists!", "Error");
PhoneNumberTextBox.Text = "";
return;
}
App.RunCommand("Insert into phonenumber(storeid,num)" +
"values" +
"(" + _ID + "," +
"" + int.Parse(PhoneNumberTextBox.Text) + ");");
App.RefreshDataGrid();
MessageBox.Show("Phone number added succesfully!", "Success");
Close();
}
private void Add_OnClick(object sender, RoutedEventArgs e)
{
if (Check())
{
return;
}
if (RecordComboBox.SelectedItem.ToString() == "Employee")
{
App.RunCommand("select * from employee where irs_number=" + int.Parse(EmployeeIRSNumberTextBox.Text));
if (App.DataTable.Rows.Count != 0)
{
MessageBox.Show("Employee with IRS Number " + EmployeeIRSNumberTextBox.Text + " already exists!", "Error");
EmployeeIRSNumberTextBox.Text = "";
return;
}
try
{
App.RunCommand("insert into employee (irs_number, lastname, firstname, driverlicense, socialsecuritynumber, street, streetnumber, postalcode, city)" +
"values" +
"(" + int.Parse(EmployeeIRSNumberTextBox.Text) + "," +
"'" + EmployeeLastNameTextBox.Text + "'," +
"'" + EmployeeFirstNameTextBox.Text + "'," +
"'" + EmployeeDriverLicenceTextBox.Text + "'," +
"'" + EmployeeSocialSecurityNumberTextBox.Text + "'," +
"'" + EmployeeStreetTextBox.Text + "'," +
"'" + EmployeeStreetNumberTextBox.Text + "'," +
"'" + EmployeePostalCodeTextBox.Text + "'," +
"'" + EmployeeCityTextBox.Text + "');");
App.RefreshDataGrid();
MessageBox.Show("Employee added succesfully!", "Success");
Close();
}
catch
{
MessageBox.Show("Something went wrong!", "Error");
}
}
else if (RecordComboBox.SelectedItem.ToString() == "Store")
{
App.RunCommand("select * from store where storeid=" + int.Parse(StoreIDTextBox.Text));
if (App.DataTable.Rows.Count != 0)
{
MessageBox.Show("Store with Store ID " + StoreIDTextBox.Text + " already exists!", "Error");
StoreIDTextBox.Text = "";
return;
}
try
{
App.RunCommand("insert into store (storeid, street, streetnumber, postalcode, city)" +
"values" +
"(" + int.Parse(StoreIDTextBox.Text) + "," +
"'" + StoreStreetTextBox.Text + "'," +
"'" + StoreNumberTextBox.Text + "'," +
"'" + StorePostalCodeTextBox.Text + "'," +
"'" + StoreCityTextBox.Text + "');");
App.RefreshDataGrid();
MessageBox.Show("Store added succesfully!", "Success");
Close();
}
catch
{
MessageBox.Show("Something went wrong!", "Error");
}
}
else if (RecordComboBox.SelectedItem.ToString() == "Vehicle")
{
App.RunCommand("select * from vehicle where licenseplate ='" + (LicensePlateTextBox.Text) + "';");
if (App.DataTable.Rows.Count != 0)
{
MessageBox.Show("Vehicle with Licence Plate " + LicensePlateTextBox.Text + " already exists!", "Error");
LicensePlateTextBox.Text = "";
return;
}
try
{
string yearmade, Nservice, Lservice, Insurance;
if (YearMadeComboBox.SelectedIndex != -1)
{
yearmade = "'" + YearMadeComboBox.SelectedItem.ToString() + "'";
}
else
{
yearmade = "null";
}
if (string.IsNullOrWhiteSpace(NextServiceTextBox.Text))
{
Nservice = "null";
}
else
{
Nservice = "'" + NextServiceTextBox.Text + "'";
}
if (string.IsNullOrWhiteSpace(LastServiceTextBox.Text))
{
Lservice = "null";
}
else
{
Lservice = "'" + LastServiceTextBox.Text + "'";
}
if (string.IsNullOrWhiteSpace(InsuranceExpirationDateTextBox.Text))
{
Insurance = "null";
}
else
{
Insurance = "'" + InsuranceExpirationDateTextBox.Text + "'";
}
App.RunCommand("select * from store where storeid=" + int.Parse(StoreIDVehicleTextBox.Text) + ";");
if (App.DataTable.Rows.Count == 0)
{
MessageBox.Show("Store with Store ID " + StoreIDVehicleTextBox.Text + " does not exist!", "Error");
CustomerIDTextBox.Text = "";
return;
}
int KM, CC, HP;
if (!int.TryParse(KilometersTextBox.Text, out KM))
{
KM = 0;
}
if (!int.TryParse(CylinderCapacityTextBox.Text, out CC))
{
CC = 0;
}
if (!int.TryParse(HorsePowerTextBox.Text, out HP))
{
HP = 0;
}
App.RunCommand("insert into vehicle(licenseplate, model, cartype, make, yearmade, kilometers, cylindercapacity,horsepower, damages, malfunctions, nextservice,lastservice, InsuranceExpirationDate ,storeid)" +
"values" +
"('" + LicensePlateTextBox.Text + "'," +
"'" + ModelTextBox.Text + "'," +
"'" + CarTypeTextBox.Text + "'," +
"'" + MakeTextBox.Text + "'," +
"" + yearmade + "," +
"" + KM + "," +
"" + CC + "," +
"" + HP + "," +
"'" + DamagesTextBox.Text + "'," +
"'" + MalfunctionsTextBox.Text + "'," +
"" + Nservice + "," +
"" + Lservice + "," +
"" + Insurance + "," +
"" + int.Parse(StoreIDVehicleTextBox.Text) + ");");
App.RefreshDataGrid();
MessageBox.Show("Vehicle added succesfully!", "Success");
Close();
}
catch (Exception ex)
{
MessageBox.Show("Something went wrong!", "Error");
}
}
else if (RecordComboBox.SelectedItem.ToString() == "Customer")
{
App.RunCommand("select * from customer where customerid=" + int.Parse(CustomerIDTextBox.Text));
if (App.DataTable.Rows.Count != 0)
{
MessageBox.Show("Customer with Customer ID " + CustomerIDTextBox.Text + " already exists!", "Error");
CustomerIDTextBox.Text = "";
return;
}
try
{
string registration;
if (string.IsNullOrWhiteSpace(FirstRegistrationTextBox.Text))
{
registration = "null";
}
else
{
registration = "'" + FirstRegistrationTextBox.Text + "'";
}
App.RunCommand("insert into customer(Customerid, FirstRegistration, SocialSecutiryNumber, DriverLicense, Irs_number, firstname, lastname, street, streetnumber, postalcode, city)" +
"values" +
"(" + int.Parse(CustomerIDTextBox.Text) + "," +
"" + registration + "," +
"'" + CustomerSocialSecurityNumberTextBox.Text + "'," +
"'" + CustomerDriverLicenceTextBox.Text + "'," +
"'" + CustomerIRSNumberTextBox.Text + "'," +
"'" + CustomerFirstNameTextBox.Text + "'," +
"'" + CustomerLastNameTextBox.Text + "'," +
"'" + CustomerStreetTextBox.Text + "'," +
"'" + CustomerNumberTextBox.Text + "'," +
"'" + CustomerPostalCodeTextBox.Text + "'," +
"'" + CustomerCityTextBox.Text + "');");
App.RefreshDataGrid();
MessageBox.Show("Customer added succesfully!", "Success");
Close();
}
catch
{
MessageBox.Show("Something went wrong!", "Error");
}
}
}
private void Delete_OnClick(object sender, RoutedEventArgs e)
{
if (DataGrid.SelectedItem != null)
{
var drv = (DataRowView)DataGrid.SelectedItem;
var table = drv.DataView.Table.ToString();
if (table == "employee")
{
var record = int.Parse(drv["IRS_NUMBER"].ToString());
if (MessageBox.Show("Delete " + table + " with IRS Number " + record + "?", "Warning", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.Yes)
{
try
{
App.RunCommand("DELETE FROM " + table + " WHERE IRS_NUMBER LIKE '" + record + "';");
MessageBox.Show("Employee deleted succesfully!", "Success");
App.LastSelect = "SELECT * FROM EMPLOYEE";
App.RefreshDataGrid();
}
catch
{
MessageBox.Show("Error deleting record", "Error");
}
}
}
else if (table == "store")
{
var record = int.Parse(drv["storeid"].ToString());
if (MessageBox.Show("Delete " + table + " with Store ID " + record + "?", "Warning", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.Yes)
{
try
{
App.RunCommand("DELETE FROM " + table + " WHERE storeid LIKE '" + record + "';");
MessageBox.Show("Store deleted succesfully!", "Success");
App.LastSelect = "SELECT * FROM store";
App.RefreshDataGrid();
}
catch
{
MessageBox.Show("Error deleting record", "Error");
}
}
}
else if (table == "vehicle")
{
var record = drv["licenseplate"].ToString();
if (MessageBox.Show("Delete " + table + " with License Plate " + record + "?", "Warning", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.Yes)
{
try
{
App.RunCommand("DELETE FROM " + table + " WHERE licenseplate LIKE '" + record + "';");
MessageBox.Show("Vehicle deleted succesfully!", "Success");
App.LastSelect = "SELECT * FROM vehicle";
App.RefreshDataGrid();
}
catch
{
MessageBox.Show("Error deleting record", "Error");
}
}
}
else
{
var record = int.Parse(drv["customerid"].ToString());
if (MessageBox.Show("Delete " + table + " with ID " + record + "?", "Warning", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.Yes)
{
try
{
App.RunCommand("DELETE FROM " + table + " WHERE customerid LIKE '" + record + "';");
MessageBox.Show("Customer deleted succesfully!", "Success");
App.LastSelect = "SELECT * FROM customer";
App.RefreshDataGrid();
}
catch
{
MessageBox.Show("Error deleting record", "Error");
}
}
}
}
}
