rendered paste body//Databases III Project
//by Xarhs & Vagelis
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.OleDb;
using MySql.Data.MySqlClient;
namespace Cars
{
public partial class Form1 : Form
{
public string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NEWCarRentals.mdb";
string MyConString = "SERVER=localhost;" +
"DATABASE=CarRental;" +
"UID=root;" +
"PASSWORD=;";
public ArrayList AllCars;
public ArrayList SelectedUsers;
public CarCharacteristics tmp;
public DeletedUsers du;
public int Available = 0;
public int ClientsNum = 0;
public enum RentedClicked
{
_Clicked,
_NotClicked,
}
public Form1()
{
InitializeComponent();
RentedButtonState = RentedClicked._NotClicked;
}
RentedClicked RentedButtonState = new RentedClicked();
private void OnFormLoad(object sender, EventArgs e)
{
LoadDatabase();
}
private void LoadDatabase()
{
string strSelect = @"SELECT Brands.Brand, Models.Model, Models.Category, Models.Availability,
Models.Rented
FROM Brands
INNER JOIN Models
ON Brands.BrandID = Models.BrandID";
try
{
DataSet myDataSet = new DataSet();
MySqlConnection mySqlConn = new MySqlConnection(MyConString);
MySqlDataAdapter mySqlAdapter = new MySqlDataAdapter(strSelect, mySqlConn);
mySqlAdapter.Fill(myDataSet, "Brands");
AllCars = new ArrayList();
DataRowCollection dra = myDataSet.Tables["Brands"].Rows;
foreach (DataRow dr in dra)
{
tmp = new CarCharacteristics();
tmp.Brand = dr[0].ToString();
tmp.Model = dr[1].ToString();
tmp.Category = dr[2].ToString();
tmp.Availability = System.Convert.ToInt32(dr[3]);
tmp.Rented = System.Convert.ToInt32(dr[4]);
AllCars.Add(tmp);
}
for (int i = 0; i < AllCars.Count; i++)
{
tmp = (CarCharacteristics)AllCars[i];
if(this.BrandName.Items.Contains(tmp.Brand) == false)
this.BrandName.Items.Add(tmp.Brand);
}
BrandName.SelectedIndex = -1;
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message.ToString());
}
}
private void BrandName_SelectedIndexChanged(object sender, EventArgs e)
{
ModelsName.Items.Clear();
ModelsName.Sorted = true;
CategoryRent.Items.Clear();
foreach (CarCharacteristics a in AllCars)
{
if (BrandName.SelectedItem.ToString() == a.Brand)
{
ModelsName.Items.Add(a.Model);
}
}
ModelsName.SelectedIndex = 0;
}
private void RentButton_Click(object sender, EventArgs e)
{
if (BrandName.SelectedIndex == -1)
BrandName.Focus();
else if (ModelsName.SelectedIndex == -1)
ModelsName.Focus();
else if (CategoryRent.SelectedIndex == -1)
CategoryRent.Focus();
else if (NameTextBox.Text.Length == 0)
NameTextBox.Focus();
else if (LastnameTextBox.Text.Length == 0)
LastnameTextBox.Focus();
else if(PhoneTextBox.Text.Length == 0)
PhoneTextBox.Focus();
else
{
foreach (CarCharacteristics a in AllCars)
{
if (BrandName.SelectedItem.ToString() == a.Brand &&
ModelsName.SelectedItem.ToString() == a.Model &&
CategoryRent.SelectedItem.ToString() == a.Category)
{
if (a.Rented < a.Availability)
{
a.Name[a.count] = NameTextBox.Text;
a.Lastname[a.count] = LastnameTextBox.Text;
a.phone[a.count] = PhoneTextBox.Text;
a.count++;
a.Added = true;
a.Rented++;
Available = a.Availability - a.Rented;
AvailableTextBox.Text = Available.ToString();
RentedButtonState = RentedClicked._Clicked;
ClientsNum++;
ClientNumLabel.Text = ClientsNum.ToString() + " Clients";
}
else
MessageBox.Show("Not available");
}
}
}
}
private void UpdateButton_Click(object sender, EventArgs e)
{
if (RentedButtonState == RentedClicked._Clicked)
{
UpdateDatabase();
}
}
private void UpdateDatabase()
{
try
{
MySqlConnection mySqlConn = new MySqlConnection(MyConString);
MySqlCommand UPDATECommand = new MySqlCommand();
MySqlCommand INSERTCommand = new MySqlCommand();
UPDATECommand.Connection = mySqlConn;
INSERTCommand.Connection = mySqlConn;
mySqlConn.Open();
foreach (CarCharacteristics a in AllCars)
{
for (int i = 0; i < a.count; i++)
{
if (a.Added && a.Name[i] != null)
{
INSERTCommand.CommandText = @"INSERT INTO Customers
( Name, Lastname, Phone, ModelID )
SELECT '" + a.Name[i] + "', '" + a.Lastname[i] + "', '" + a.phone[i] + "',"
+ " ModelID FROM Models WHERE Model ='" + a.Model + "'";
INSERTCommand.ExecuteNonQuery();
UPDATECommand.CommandText = @"UPDATE Models
SET Models.Rented=" + a.Rented +
" WHERE Models.BrandID=(SELECT BrandID From Brands WHERE Brand='" + a.Brand + "')"
+ " AND Models.Model='" + a.Model + "'"
+ " AND Category='" + a.Category + "'";
UPDATECommand.ExecuteNonQuery();
}
}
}
mySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message.ToString());
}
System.Array.Clear(tmp.Name, 0, tmp.Name.Length);
System.Array.Clear(tmp.Lastname, 0, tmp.Lastname.Length);
System.Array.Clear(tmp.phone, 0, tmp.phone.Length);
}
private void AddButton_Click(object sender, EventArgs e)
{
bool Cancel = false;
bool Show = false;
bool found = false;
DialogResult result;
if (BrandTextBox.Text.Length == 0)
BrandTextBox.Focus();
else if (ModelTextBox.Text.Length == 0)
ModelTextBox.Focus();
else if (CategoryAdd.SelectedIndex == -1)
CategoryAdd.Focus();
else if (CountTextBox.Text.Length == 0)
CountTextBox.Focus();
else
{
try
{
foreach (CarCharacteristics a in AllCars)
{
if (a.Brand == BrandTextBox.Text || a.Model == ModelTextBox.Text)
{
found = true;
if (a.Brand == BrandTextBox.Text && a.Model == ModelTextBox.Text)
{
Show = true;
Cancel = true;
break;
}
}
}
if (Show && found)
{
MessageBox.Show(BrandTextBox.Text + " " + ModelTextBox.Text + " already exist");
}
else if (!Show && found)
{
//Show = false;
result = MessageBox.Show("Brand or Model already exist, Are you sure?", "Confirm", MessageBoxButtons.YesNo);
if (result == DialogResult.No)
Cancel = true;
}
if (found && !Cancel)
{
string InsertModelCommand = @"INSERT INTO Models
( Model, Category, Availability, Rented, BrandID)
SELECT '" + ModelTextBox.Text + "','" + CategoryAdd.SelectedItem.ToString() + "'," + Convert.ToInt32(CountTextBox.Text) + ",0,"
+ "BrandID FROM Brands WHERE Brand='" + BrandTextBox.Text + "'";
InsertModel(InsertModelCommand);
}
if(!found && !Cancel)
{
string InsertBrand = @"INSERT INTO Brands (Brand) VALUES
('" + BrandTextBox.Text + "');";
string InsertModel = @"INSERT INTO Models
( Model, Category, Availability, Rented, BrandID )
SELECT '" + ModelTextBox.Text + "','" + CategoryAdd.SelectedItem.ToString() + " '," + Convert.ToInt32(CountTextBox.Text) + ",0,"
+ "BrandID From Brands WHERE Brand='" + BrandTextBox.Text + "'";
InsertBrandAndModel(InsertBrand, InsertModel);
}
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message.ToString());
}
}
}
private void InsertBrandAndModel(string strBrand,string strModel)
{
try
{
MySqlConnection myConn = new MySqlConnection(MyConString);
MySqlCommand InsertBrandCommand = new MySqlCommand();
MySqlCommand InsertModelCommand = new MySqlCommand();
InsertBrandCommand.Connection = myConn;
InsertModelCommand.Connection = myConn;
InsertBrandCommand.CommandText = strBrand;
InsertModelCommand.CommandText = strModel;
myConn.Open();
InsertBrandCommand.ExecuteNonQuery();
InsertModelCommand.ExecuteNonQuery();
myConn.Close();
}
catch (Exception)
{
}
}
private void InsertModel(string strCommand)
{
try
{
MySqlConnection mySqlConn = new MySqlConnection(MyConString);
MySqlCommand command = new MySqlCommand();
command.Connection = mySqlConn;
command.CommandText = strCommand;
mySqlConn.Open();
command.ExecuteNonQuery();
mySqlConn.Close();
}
catch (Exception)
{
}
}
private void RefreshButton_Click(object sender, EventArgs e)
{
BrandName.Items.Clear();
BrandName.Text = "";
ModelsName.Items.Clear();
ModelsName.Text = "";
CategoryRent.Items.Clear();
CategoryRent.Text = "";
AvailableTextBox.Clear();
NameTextBox.Clear();
LastnameTextBox.Clear();
PhoneTextBox.Clear();
LoadDatabase();
}
private void ModelsName_SelectedIndexChanged(object sender, EventArgs e)
{
CategoryRent.Sorted = true;
CategoryRent.Items.Clear();
foreach (CarCharacteristics a in AllCars)
{
if (BrandName.SelectedItem.ToString() == a.Brand &&
ModelsName.SelectedItem.ToString() == a.Model)
{
CategoryRent.Items.Add(a.Category);
CategoryRent.SelectedItem=a.Category;
}
}
}
private void CategoryRent_SelectedIndexChanged(object sender, EventArgs e)
{
foreach (CarCharacteristics a in AllCars)
{
if (BrandName.SelectedItem.ToString() == a.Brand &&
ModelsName.SelectedItem.ToString() == a.Model &&
CategoryRent.SelectedItem.ToString() == a.Category)
{
Available = a.Availability - a.Rented;
AvailableTextBox.Text = Available.ToString();
}
}
}
private void SearchButton_Click(object sender, EventArgs e)
{
string strSelect = "";
if (SearchNameTextBox.Text.Length == 0)
{
strSelect = @"SELECT Customers.Lastname, Customers.Name, Customers.Phone,
Brands.Brand, Models.Model, Models.Category
FROM Brands,Models,Customers
WHERE Brands.BrandID = Models.BrandID
AND Models.ModelID = Customers.ModelID
AND Customers.Lastname = '" + SearchLastnameTextBox.Text + "'";
Search(strSelect);
}
else if (SearchLastnameTextBox.Text.Length == 0)
{
strSelect = @"SELECT Customers.Lastname, Customers.Name, Customers.Phone,
Brands.Brand, Models.Model, Models.Category
FROM Brands,Models,Customers
WHERE Brands.BrandID = Models.BrandID
AND Models.ModelID = Customers.ModelID
AND Customers.Name = '" + SearchNameTextBox.Text + "'";
Search(strSelect);
}
else
{
strSelect = @"SELECT Customers.Lastname, Customers.Name, Customers.Phone,
Brands.Brand, Models.Model, Models.Category
FROM Brands,Models,Customers
WHERE Brands.BrandID = Models.BrandID
AND Models.ModelID = Customers.ModelID
AND Customers.Name = '" + SearchNameTextBox.Text + "'"
+ "AND Customers.Lastname = '" + SearchLastnameTextBox.Text + "'";
Search(strSelect);
}
}
private void Search(string strSelect)
{
try
{
DataSet myDataSet = new DataSet();
MySqlConnection mySqlConn = null;
mySqlConn = new MySqlConnection(MyConString);
MySqlDataAdapter mySqlAdapter = new MySqlDataAdapter(strSelect, mySqlConn);
mySqlAdapter.Fill(myDataSet, "Brands");
SearchDataGrid.DataSource = myDataSet;
SearchDataGrid.DataMember = "Brands";
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message.ToString());
}
}
private void tabPage1_Enter(object sender, EventArgs e)
{
SelectedUsers = new ArrayList();
}
private void SearchDataGrid_CellClick(object sender, DataGridViewCellEventArgs e)
{
du = new DeletedUsers();
du.Lastname = SearchDataGrid.Rows[e.RowIndex].Cells[0].FormattedValue.ToString();
du.Name = SearchDataGrid.Rows[e.RowIndex].Cells[1].FormattedValue.ToString();
SelectedUsers.Add(du);
}
private void SearchDataGrid_CellLeave(object sender, DataGridViewCellEventArgs e)
{
foreach (DeletedUsers str in SelectedUsers)
{
if (str.Lastname == SearchDataGrid.Rows[e.RowIndex].Cells[0].FormattedValue.ToString() ||
str.Name == SearchDataGrid.Rows[e.RowIndex].Cells[1].FormattedValue.ToString())
{
SelectedUsers.Remove(str.Lastname);
SelectedUsers.Remove(str.Name);
}
}
}
private void DeleteButton_Click(object sender, EventArgs e)
{
if (SelectedUsers.Count == 0)
MessageBox.Show("Nothing to delete!");
else
{
foreach (DeletedUsers str in SelectedUsers)
{
MessageBox.Show("Name: " + str.Name + Environment.NewLine + "Lastname: " + str.Lastname + Environment.NewLine
+ "DELETED!");
}
}
}
void Delete()
{
try
{
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message.ToString());
}
}
}
}