private void button1_Click(object sender, EventArgs e)
{
if (txtNames.Text == "" || txtCell.Text == "" || txtEmail.Text == "" || txtLicenseNo.Text == "" || txtNationalID.Text == "" || txtxAddress.Text == "")
{
MessageBox.Show("Please insert all the details");
}
else
{
Dbconnect d = new Dbconnect();
var c = d.getConnection();
//Security secure = new Security();
string pass = "******"; // secure.HashPass("password");
string sql = "INSERT INTO drivers(drivernames,nationalID,licenseNo,contactCell,email,physicalAddress,password,status,regDate,aLevel) VALUES('" + txtNames.Text + "','" + txtNationalID.Text + "', '" + txtLicenseNo.Text + "','" + txtCell.Text + "','" + txtEmail.Text + "','" + txtxAddress.Text + "','" + pass + "','Active','" + Convert.ToDateTime(DateTime.Today).ToString("yyyy-MM-dd") + "','" + cmbLevel.Text + "')";
try
{
MySqlCommand cmdSave = new MySqlCommand(sql, c);
int i = cmdSave.ExecuteNonQuery();
if (i >= 1)
{
MessageBox.Show("Driver details has been saved");
Clear();
loadGrid();
}
else
{
MessageBox.Show("An error occured");
}
}
catch (Exception execption)
{
MessageBox.Show(execption.Message);
}
}
}
private void button1_Click(object sender, EventArgs e)
{
if (txtRegno.Text == "" || txtWeight.Text == "" || txtDescription.Text == "")
{
MessageBox.Show("Please insert all the details", "Error");
}
else
{
Dbconnect d = new Dbconnect();
var c = d.getConnection();
string sql = "INSERT INTO trucks(regno,truckdescription,weight,truckRegDate,truckStatus) VALUES('" + txtRegno.Text + "','" + txtDescription.Text + "','" + txtWeight.Text + "','" + DateTime.Now + "','Available')";
try
{
MySqlCommand cmdSave = new MySqlCommand(sql, c);
int i = cmdSave.ExecuteNonQuery();
if (i >= 1)
{
MessageBox.Show("Truck details has been saved");
Clear();
loadGrid();
}
else
{
MessageBox.Show("An error occured");
}
}
catch (Exception execption)
{
MessageBox.Show(execption.Message);
}
}
}
private void loadGrid()
{
Dbconnect d = new Dbconnect();
var c = d.getConnection();
var select = "SELECT regno AS RegNo,weight AS Weight,truckdescription AS Description,truckstatus AS Status FROM trucks";
var dataAdapter = new MySqlDataAdapter(select, c);
var commandBuilder = new MySqlCommandBuilder(dataAdapter);
var ds = new DataSet();
dataAdapter.Fill(ds);
dgvTrucks.ReadOnly = true;
dgvTrucks.DataSource = ds.Tables[0];
}
private void loadGrid()
{
Dbconnect d = new Dbconnect();
var c = d.getConnection();
var select = "SELECT drivernames AS Driver,CONCAT(regno,' ',truckDescription) AS Vehicle, assignmentDate AS Date, assignmentStatus AS Status FROM assignments INNER JOIN drivers ON drivernum = driverID INNER JOIN trucks ON truckNum = truckID";
var dataAdapter = new MySqlDataAdapter(select, c);
var commandBuilder = new MySqlCommandBuilder(dataAdapter);
var ds = new DataSet();
dataAdapter.Fill(ds);
dgvAssignments.ReadOnly = true;
dgvAssignments.DataSource = ds.Tables[0];
}
private void loadGrid()
{
Dbconnect d = new Dbconnect();
var c = d.getConnection();
var select = "SELECT drivernames AS DriverNames,nationalID AS NationalID,licenseNo AS LicenseNo,email AS Email,Status AS Status FROM drivers";
var dataAdapter = new MySqlDataAdapter(select, c);
var commandBuilder = new MySqlCommandBuilder(dataAdapter);
var ds = new DataSet();
dataAdapter.Fill(ds);
dgvDrivers.ReadOnly = true;
dgvDrivers.DataSource = ds.Tables[0];
}
private void loadgrid()
{
Dbconnect d = new Dbconnect();
using (var c = d.getConnection())
{
var select = "SELECT fault FROM faultscategories";
var dataAdapter = new MySqlDataAdapter(select, c);
var commandBuilder = new MySqlCommandBuilder(dataAdapter);
var ds = new DataSet();
dataAdapter.Fill(ds);
dgvCategory.ReadOnly = true;
dgvCategory.DataSource = ds.Tables[0];
}
}
private void loadRoutes()
{
Dbconnect d = new Dbconnect();
var c = d.getConnection();
var select = "SELECT routeID, routeDescription FROM routes";
MySqlCommand cmd = new MySqlCommand(select, c);
MySqlDataAdapter da = new MySqlDataAdapter(select, c);
DataSet ds = new DataSet();
cmbRoute.DisplayMember = "routeDescription";
cmbRoute.ValueMember = "routeID";
da.Fill(ds);
cmbRoute.DataSource = ds.Tables[0];
c.Close();
}
private void loadTrucks()
{
Dbconnect d = new Dbconnect();
var c = d.getConnection();
var select = "SELECT truckID, regno FROM trucks WHERE truckStatus = 'Available'";
MySqlCommand cmd = new MySqlCommand(select, c);
MySqlDataAdapter da = new MySqlDataAdapter(select, c);
DataSet ds = new DataSet();
cmbTruck.DisplayMember = "regno";
cmbTruck.ValueMember = "truckID";
da.Fill(ds);
cmbTruck.DataSource = ds.Tables[0];
c.Close();
}
private void loadDrivers()
{
Dbconnect d = new Dbconnect();
var c = d.getConnection();
var select = "SELECT driverID, drivernames FROM drivers WHERE status = 'Active'";
MySqlCommand cmd = new MySqlCommand(select, c);
MySqlDataAdapter da = new MySqlDataAdapter(select, c);
DataSet ds = new DataSet();
cmbDriver.DisplayMember = "drivernames";
cmbDriver.ValueMember = "driverID";
da.Fill(ds);
cmbDriver.DataSource = ds.Tables[0];
c.Close();
}
private void btnSave_Click(object sender, EventArgs e)
{
if (cmbDriver.Text == string.Empty || cmbRoute.Text == string.Empty || cmbTruck.Text == string.Empty)
{
MessageBox.Show("Insert all the details", "Error");
}
else
{
Dbconnect d = new Dbconnect();
var c = d.getConnection();
string sql = "INSERT INTO assignments(driverNum,truckNum,route,assignmentDate,assignmentStatus) VALUES('" + (cmbDriver.SelectedValue) + "','" + (cmbTruck.SelectedValue) + "','" + (cmbRoute.SelectedValue) + "','" + Convert.ToDateTime(txtDate.Text).ToString("yyyy-MM-dd") + "','Active')";
string sqlUser = "******" + cmbDriver.SelectedValue + "'";
string sqlTruck = "UPDATE trucks SET truckStatus = 'Out' WHERE truckID = '" + cmbTruck.SelectedValue + "'";
string msg = "You have been assigned " + cmbTruck.Text + " to route " + cmbRoute.Text + " on " + Convert.ToDateTime(txtDate.Text).ToString("yyyy-MM-dd") + " have a safe journey.";
string sqlMesg = "INSERT INTO messages(receiver,messageBody,sender,isRead,mDate) VALUES('" + cmbDriver.SelectedValue + "','" + msg + "','admin',0,'" + Convert.ToDateTime(txtDate.Text).ToString("yyyy-MM-dd") + "')";
try
{
MySqlCommand cmdSave = new MySqlCommand(sql, c);
MySqlCommand cmdUpdateD = new MySqlCommand(sqlUser, c);
MySqlCommand cmdSavemessage = new MySqlCommand(sqlMesg, c);
int j = cmdUpdateD.ExecuteNonQuery();
MySqlCommand cmdTruck = new MySqlCommand(sqlTruck, c);
int k = cmdTruck.ExecuteNonQuery();
int i = cmdSave.ExecuteNonQuery();
int z = cmdSavemessage.ExecuteNonQuery();
if (i >= 1 && j >= 1 && k >= 1)
{
MessageBox.Show("Assignment details has been saved");
Clear();
loadGrid();
loadDrivers();
loadTrucks();
}
else
{
MessageBox.Show("An error occured");
}
}
catch (Exception execption)
{
MessageBox.Show(execption.Message);
}
}
}
private int saveData(string name)
{
int i = 0;
Dbconnect cn = new Dbconnect();
try
{
using (var c = cn.getConnection())
{
string save = "INSERT INTO faultscategories VALUES('" + name + "')";
MySqlCommand cmdSave = new MySqlCommand(save, c);
i = cmdSave.ExecuteNonQuery();
}
} catch (Exception message)
{
throw message;
}
return(i);
}
private int SaveDetails()
{
int i = 0;
Dbconnect cn = new Dbconnect();
try
{
using (var c = cn.getConnection())
{
string save = "INSERT INTO faults(faultcategory,faultDescription,truck,driver,assesedDate,faultStatus) VALUES('" + cmbCategory.Text + "','" + txtDescription.Text + "',1,1,'" + Convert.ToDateTime(txtxDate.Text).ToString("yyyy-MM-dd") + "','Pending')";
MySqlCommand cmdSave = new MySqlCommand(save, c);
i = cmdSave.ExecuteNonQuery();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(i);
}
private void loadGrid()
{
Dbconnect d = new Dbconnect();
try
{
using (var c = d.getConnection())
{
var select = "SELECT drivernames AS Driver,CONCAT(regno,' ',truckDescription) AS Vehicle, assesedDate AS Date, faultStatus AS Status FROM faults INNER JOIN drivers ON driver = driverID INNER JOIN trucks ON truck = truckID";
var dataAdapter = new MySqlDataAdapter(select, c);
var commandBuilder = new MySqlCommandBuilder(dataAdapter);
var ds = new DataSet();
dataAdapter.Fill(ds);
dgvFaults.ReadOnly = true;
dgvFaults.DataSource = ds.Tables[0];
}
}catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void loadCategories()
{
Dbconnect d = new Dbconnect();
using (var c = d.getConnection())
{
try
{
var select = "SELECT fault FROM faultscategories";
MySqlCommand cmd = new MySqlCommand(select, c);
MySqlDataAdapter da = new MySqlDataAdapter(select, c);
DataSet ds = new DataSet();
cmbCategory.DisplayMember = "fault";
da.Fill(ds);
cmbCategory.DataSource = ds.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private void loadTrucks()
{
Dbconnect d = new Dbconnect();
using (var c = d.getConnection())
{
try
{
var select = "SELECT truckID, regno FROM trucks";
MySqlCommand cmd = new MySqlCommand(select, c);
MySqlDataAdapter da = new MySqlDataAdapter(select, c);
DataSet ds = new DataSet();
cmbTruck.DisplayMember = "regno";
cmbTruck.ValueMember = "truckID";
da.Fill(ds);
cmbTruck.DataSource = ds.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}