private void CheckExisting()
{
try
{
string existName;
//checks to see if patient already exists in database
objDBConnect.OpenConnection();
objDBConnect.sqlCmd = new SqlCommand("SELECT COUNT(*) FROM Register WHERE R_UNAME LIKE @R_UNAME;", objDBConnect.sqlConn);
//query
objDBConnect.sqlCmd.Parameters.AddWithValue("@R_UNAME", userName);
//parameter
existName = objDBConnect.sqlCmd.ExecuteScalar().ToString();
//assigning query to variable
if (int.Parse(existName) > 0)
{
found = true;
//in database
}
else
{
found = false;
//not in database
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Error Cannot Check If User Exists In Database: " + ex.Message); // Shows an error message
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
T_ID = int.Parse(Interaction.InputBox("Please enter Timesheet ID: ", "Timesheet ID", "Default Text"));
try
{
objDBConnect.OpenConnection();
string sql = "DELETE FROM Timesheet WHERE (T_ID ='" + T_ID + "');";
objDBConnect.sqlCmd = new SqlCommand();
objDBConnect.sqlCmd.CommandText = sql;
objDBConnect.sqlCmd.Connection = objDBConnect.sqlConn;
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
MessageBox.Show("SUCCESS");
objDBConnect.sqlDR.Close();
objDBConnect.sqlConn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Error Cannot Delete Records: " + ex.Message);
}
}
private void btnSubmit_Click(object sender, EventArgs e)
{
Check check = new Check();
bool exit = false;
getValues();
exit = check.CheckEmpty(T_ID, "Trip ID", exit);
exit = check.CheckEmpty(V_RN, "Vehicle Reg Number", exit);
if (exit == false)
{
if (!T_ID.Equals(""))
{
try
{
objDBConnect.OpenConnection();
objDBConnect.sqlCmd = new SqlCommand("INSERT INTO TripUsage VALUES(@Trip_ID, @Vehicle_RegNumber,@Location_ID, @Trip_DateFrom, @Trip_DateTo,NULL,NULL,NULL,'NO')", objDBConnect.sqlConn);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Trip_ID", T_ID);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_RegNumber", V_RN);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Location_ID", L_ID);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Trip_DateFrom", SqlDbType.Date).Value = dtpDateFrom.Value.Date;
objDBConnect.sqlCmd.Parameters.AddWithValue("@Trip_DateTo", SqlDbType.Date).Value = dtpDateTo.Value.Date;
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
MessageBox.Show("Succesfully inserted");
CloseConnections();
}
catch (SqlException ex)
{
MessageBox.Show("ERROR!!!" + ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Error Cannot Submit Details: " + ex.Message);
}
}
else
{
MessageBox.Show("Please enter a Trip ID");
}
}
}
private void btnSubmit_Click(object sender, EventArgs e)
{
bool exit = false;
Check check = new Check();
username = txtUsername.Text;
oldPass = txtOldPass.Text;
newPass1 = txtNewPass.Text;
newPass2 = txtNewPass.Text;
exit = check.CheckEmpty(username, "Username", exit);
exit = check.CheckEmpty(oldPass, "Old Password", exit);
exit = check.CheckEmpty(newPass1, "New Password", exit);
exit = check.CheckEmpty(newPass2, "Confirm New Password", exit);
if (exit == false)
{
if (newPass1 == newPass2)
{
DBConnect objDBConnect = new DBConnect();
objDBConnect.OpenConnection();
objDBConnect.sqlCmd = new SqlCommand("UPDATE Register SET R_PWORD = @R_PWORD WHERE R_UNAME = @R_UNAME", objDBConnect.sqlConn);
objDBConnect.sqlCmd.Parameters.AddWithValue("@R_PWORD", newPass1);
objDBConnect.sqlCmd.Parameters.AddWithValue("@R_UNAME", username);
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
objDBConnect.sqlDR.Close();
MessageBox.Show("Successfully Updated");
}
else
{
MessageBox.Show("The password fields do not match");
}
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
try
{
DBConnect objDBConnect = new DBConnect();
objDBConnect.OpenConnection();
objDBConnect.sqlCmd = new SqlCommand("UPDATE Customer SET Cust_Name= @Cust_Name, Cust_Surname=@Cust_Surname, Cust_Type=@Cust_Type, Cust_ContactNo=@Cust_ContactNo,Cust_Email=@Cust_Email, Cust_PayDue=@Cust_PayDue, Cust_PayMade=@Cust_PayMade WHERE Cust_ID = @Cust_ID", objDBConnect.sqlConn);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_ID", C_ID);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_Name", C_NAME);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_Surname", C_SNAME);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_Type", C_TYPE);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_ContactNo", C_CONTACT);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_Email", C_EMAIL);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_PayDue", C_DUE);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_PayMade", C_MADE);
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
MessageBox.Show("SUCCESSFULLY UPDATED");
objDBConnect.sqlDR.Close();
objDBConnect.sqlConn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Error Cannot Update Customer Details: " + ex.Message);
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
getValues();
try
{
DBConnect objDBConnect = new DBConnect();
objDBConnect.OpenConnection();
string sql = "DELETE FROM Vehicle WHERE (Vehicle_RegNumber ='" + V_RN + "');";
objDBConnect.sqlCmd = new SqlCommand();
objDBConnect.sqlCmd.CommandText = sql;
objDBConnect.sqlCmd.Connection = objDBConnect.sqlConn;
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
MessageBox.Show("SUCCESSFUL");
objDBConnect.sqlDR.Close();
objDBConnect.sqlConn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Error! You Cannot Delete Vehicle Details: " + ex.Message);
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
getValues();
try
{
DBConnect objDBConnect = new DBConnect();
objDBConnect.OpenConnection();
string sql = "DELETE FROM Employee WHERE (Emp_ID ='" + E_ID + "');";
objDBConnect.sqlCmd = new SqlCommand();
objDBConnect.sqlCmd.CommandText = sql;
objDBConnect.sqlCmd.Connection = objDBConnect.sqlConn;
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
MessageBox.Show("SUCCESS");
objDBConnect.sqlDR.Close();
objDBConnect.sqlConn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Error Cannot Delete Employee Details: " + ex.Message); // Shows an error message
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
L_ID = Interaction.InputBox("Please enter Location ID: ", "Location ID", "Default Text");
try
{
DBConnect objDBConnect = new DBConnect();
objDBConnect.OpenConnection();
string sql = "DELETE FROM Location WHERE (Location_ID ='" + L_ID + "');";
objDBConnect.sqlCmd = new SqlCommand();
objDBConnect.sqlCmd.CommandText = sql;
objDBConnect.sqlCmd.Connection = objDBConnect.sqlConn;
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
MessageBox.Show("SUCCESS");
objDBConnect.sqlDR.Close();
objDBConnect.sqlConn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Error Cannot Delete Location Details: " + ex.Message);
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
getValues();
try
{
DBConnect objDBConnect = new DBConnect();
objDBConnect.OpenConnection();
objDBConnect.sqlCmd = new SqlCommand("UPDATE Vehicle SET Vehicle_Type=@Vehicle_Type, Vehicle_Make=@Vehicle_Make, Vehicle_Model=@Vehicle_Model, Vehicle_Year=@Vehicle_Year,Vehicle_TotalMileage= @Vehicle_TotalMileage WHERE Vehicle_RegNumber=@Vehicle_RegNumber ", objDBConnect.sqlConn);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_RegNumber", V_RN);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_Type", V_TYPE);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_Make", V_MAKE);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_Model", V_MODEL);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_Year", V_YEAR);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_TotalMileage", V_MILEAGE);
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
MessageBox.Show("SUCCESSFULLY UPDATED");
objDBConnect.sqlDR.Close();
objDBConnect.sqlConn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Error! You Cannot Update Vehicle Details: " + ex.Message);
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
try
{
DBConnect objDBConnect = new DBConnect();
objDBConnect.OpenConnection();
string sql = "DELETE FROM Customer WHERE (Cust_ID ='" + C_ID + "');";
objDBConnect.sqlCmd = new SqlCommand();
objDBConnect.sqlCmd.CommandText = sql;
objDBConnect.sqlCmd.Connection = objDBConnect.sqlConn;
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
MessageBox.Show("SUCCESS");
objDBConnect.sqlDR.Close();
objDBConnect.sqlConn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Error Cannot Delete Customer Details: " + ex.Message);
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
getValues();
try
{
DBConnect objDBConnect = new DBConnect();
objDBConnect.OpenConnection();
string sql = "DELETE FROM tblService WHERE (Service_ID ='" + S_ID + "');";
objDBConnect.sqlCmd = new SqlCommand();
objDBConnect.sqlCmd.CommandText = sql;
objDBConnect.sqlCmd.Connection = objDBConnect.sqlConn;
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
MessageBox.Show("SUCCESS");
objDBConnect.sqlDR.Close();
objDBConnect.sqlConn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Error Cannot Delete Record From Service Table in Database: " + ex.Message);
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
getValues();
try
{
DBConnect objDBConnect = new DBConnect();
objDBConnect.OpenConnection();
objDBConnect.sqlCmd = new SqlCommand("UPDATE tblService SET Vehicle_RegNumber=@Vehicle_RegNumber,Emp_ID= @Emp_ID,Service_Date= @Service_Date,Service_Time = @Service_Time,Service_Description = @Service_Description WHERE Service_ID = @Service_ID", objDBConnect.sqlConn);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Service_ID", S_ID);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_RegNumber", V_RN);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_ID", E_ID);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Service_Date", SqlDbType.Date).Value = dtpAppointmentDate.Value.Date;
objDBConnect.sqlCmd.Parameters.AddWithValue("@Service_Time", S_TIME);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Service_Description", S_DES);
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
MessageBox.Show("SUCCESSFULLY UPDATED");
objDBConnect.sqlDR.Close();
objDBConnect.sqlConn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Error Cannot Update Details in Service Table: " + ex.Message);
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
try
{
T_ID = int.Parse(Interaction.InputBox("Please enter Timesheet ID: ", "Timesheet ID", "Default Text"));
T_HOURS = double.Parse(Interaction.InputBox("Please enter Number of hours worked: " + T_ID, "Hours Worked", "Default Text"));
}
catch (FormatException f)
{
MessageBox.Show("Invalid Format. Please only enter digits: " + f.Message);
}
try
{
DBConnect objDBConnect = new DBConnect();
objDBConnect.OpenConnection();
objDBConnect.sqlCmd = new SqlCommand("UPDATE Timesheet SET T_HOURS = @T_HOURS WHERE T_ID = @T_ID", objDBConnect.sqlConn);
objDBConnect.sqlCmd.Parameters.AddWithValue("@T_ID", T_ID);
objDBConnect.sqlCmd.Parameters.AddWithValue("@T_HOURS", T_HOURS);
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
MessageBox.Show("SUCCESSFULLY INSERTED");
objDBConnect.sqlDR.Close();
objDBConnect.sqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error Cannot Submit Vehicle Details: " + ex.Message);
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
getValues();
try
{
DBConnect objDBConnect = new DBConnect();
objDBConnect.OpenConnection();
objDBConnect.sqlCmd = new SqlCommand("UPDATE Employee SET Emp_Name=@Emp_Name,Emp_Surname= @Emp_Surname,Emp_Address = @Emp_Address,Emp_Position= @Emp_Position,Emp_ContactNo= @Emp_ContactNo, Emp_Email=@Emp_Email, Emp_MonthlySalary=@Emp_MonthlySalary WHERE Emp_ID = @Emp_ID", objDBConnect.sqlConn);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_ID", E_ID);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_Name", E_NAME);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_Surname", E_SNAME);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_Address", E_ADDRESS);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_Position", E_POS);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_ContactNo", E_CONTACT);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_Email", E_EMAIL);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_MonthlySalary", E_SALARY);
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
MessageBox.Show("SUCCESSFULLY UPDATED");
objDBConnect.sqlDR.Close();
objDBConnect.sqlConn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Error Cannot Update Employee Details: " + ex.Message); // Shows an error message
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
getValues();
Check check = new Check();
bool exit = false;
exit = check.CheckEmpty(L_ID, "Location ID", exit);
exit = check.CheckEmpty(L_MANAGER, "Manager In Charge", exit);
exit = check.CheckEmpty(L_CITY, "City", exit);
exit = check.CheckEmpty(L_NAME, "Location", exit);
exit = check.CheckEmpty(L_PROVINCE, "Province", exit);
exit = check.CheckForNumbers(L_MANAGER, "Manager In Charge");
if (exit == false)
{
try
{
bool executeSQL = check.CheckDB("Location", "Location_ID", L_ID);
if (executeSQL == false)
{
DBConnect objDBConnect = new DBConnect();
objDBConnect.OpenConnection();
objDBConnect.sqlCmd = new SqlCommand("INSERT INTO LOCATION VALUES (@Location_ID, @Location_Name, @Location_City, @Location_NumVehicles, @Location_NumEmployees, @Location_Manager)", objDBConnect.sqlConn);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Location_ID", L_ID);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Location_Name", L_NAME);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Location_City", L_CITY);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Location_NumVehicles", L_VEHICLES);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Location_NumEmployees", L_EMPLOYEES);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Location_Manager", L_MANAGER);
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
MessageBox.Show("SUCCESSFULLY INSERTED");
objDBConnect.sqlDR.Close();
objDBConnect.sqlConn.Close();
}
else
{
MessageBox.Show("That Location ID already exists in the database");
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Error Cannot Submit Location Details: " + ex.Message);
}
}
}
public void populateE_ID()
{
try
{
string query = "SELECT Emp_ID from Employee;";
objDBConnect.OpenConnection();
SqlDataAdapter da = new SqlDataAdapter(query, objDBConnect.sqlConn);
DataSet ds = new DataSet();
da.Fill(ds, "Employee");
cboE_ID.ValueMember = "Emp_ID";
cboE_ID.DisplayMember = "Emp_ID";
cboE_ID.DataSource = ds.Tables["Employee"];
objDBConnect.sqlConn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message); // Shows an error message
}
}
private void frmCompletedTripDetails_Load(object sender, EventArgs e)
{
try
{
string query = "SELECT Trip_ID from TripUsage WHERE Trip_Completed like 'NO';";
objDBConnect.OpenConnection();
SqlDataAdapter da = new SqlDataAdapter(query, objDBConnect.sqlConn);
DataSet ds = new DataSet();
da.Fill(ds, "TripUsage");
cboT_ID.ValueMember = "Trip_ID";
cboT_ID.DisplayMember = "Trip_ID";
cboT_ID.DataSource = ds.Tables["TripUsage"];
objDBConnect.sqlConn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message); // Shows an error message
}
}
private void btnSubmit_Click(object sender, EventArgs e)
{
getValues();
Check check = new Check();
bool exit = false;
exit = check.CheckEmpty(V_MILEAGE, "Vehicle Milage", exit);
exit = check.CheckEmpty(V_MAKE, "Vehicle Make", exit);
exit = check.CheckEmpty(V_MODEL, "Vehicle Model", exit);
exit = check.CheckEmpty(V_RN, "Registration Number", exit);
exit = check.CheckEmpty(V_TYPE, "Type of Vehicle", exit);
exit = check.CheckForLetters(V_MILEAGE, "Vehicle Milage");
if (exit == false)
{
try
{
DBConnect objDBConnect = new DBConnect();
objDBConnect.OpenConnection();
objDBConnect.sqlCmd = new SqlCommand("INSERT INTO Vehicle VALUES (@Vehicle_RegNumber, @Vehicle_Type, @Vehicle_Make, @Vehicle_Model, @Vehicle_Year, @Vehicle_TotalMileage)", objDBConnect.sqlConn);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_RegNumber", V_RN);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_Type", V_TYPE);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_Make", V_MAKE);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_Model", V_MODEL);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_Year", V_YEAR);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_TotalMileage", V_MILEAGE);
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
MessageBox.Show("Successfully Inserted");
objDBConnect.sqlDR.Close();
objDBConnect.sqlConn.Close();
}
catch (SqlException ex)
{
MessageBox.Show("Error here" + ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Error! You Cannot Submit Vehicle Details: " + ex.Message);
}
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
getValues();
try
{
Check check = new Check();
bool executeSQL = check.CheckDB("tblService", "Service_ID", S_ID);
if (executeSQL == false)
{
DBConnect objDBConnect = new DBConnect();
objDBConnect.OpenConnection();
objDBConnect.sqlCmd = new SqlCommand("INSERT INTO tblService VALUES (@Service_ID, @Vehicle_RegNumber, @Emp_ID, @Service_Date, @Service_Time,@Service_Description)", objDBConnect.sqlConn);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Service_ID", S_ID);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_RegNumber", V_RN);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_ID", E_ID);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Service_Date", SqlDbType.Date).Value = dtpAppointmentDate.Value.Date;
objDBConnect.sqlCmd.Parameters.AddWithValue("@Service_Time", S_TIME);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Service_Description", S_DES);
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
MessageBox.Show("Succesfully inserted");
objDBConnect.sqlDR.Close();
objDBConnect.sqlConn.Close();
}
else
{
MessageBox.Show("That Service ID already exists in the database");
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Error Cannot Add Record to Service Table in Database: " + ex.Message);
}
}
public bool CheckDB(string tableName, string fieldName, string field)
{
bool checkID;
string exist_id;
DBConnect objDBConnect = new DBConnect();
objDBConnect.OpenConnection();
objDBConnect.sqlCmd = new SqlCommand("SELECT COUNT(*) FROM " + tableName + " WHERE " + fieldName + " LIKE @" + fieldName + ";", objDBConnect.sqlConn);
objDBConnect.sqlCmd.Parameters.AddWithValue("@" + fieldName, field);
exist_id = objDBConnect.sqlCmd.ExecuteScalar().ToString();
if (int.Parse(exist_id) > 0)
{
checkID = true;
}
else
{
checkID = false;
}
objDBConnect.sqlConn.Close();
return(checkID);
}
private void btnAdd_Click(object sender, EventArgs e)
{
T_ID = int.Parse(txtT_ID.Text);
E_ID = cboE_ID.SelectedValue.ToString();
T_HOURS = double.Parse(updHoursWorked.Text);
if (!T_ID.Equals(""))
{
try
{
DBConnect objDBConnect = new DBConnect();
objDBConnect.OpenConnection();
objDBConnect.sqlCmd = new SqlCommand("INSERT INTO Timesheet VALUES (@T_ID, @Emp_ID, @T_HOURS,GETDATE())", objDBConnect.sqlConn);
objDBConnect.sqlCmd.Parameters.AddWithValue("@T_ID", T_ID);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_ID", E_ID);
objDBConnect.sqlCmd.Parameters.AddWithValue("@T_HOURS", T_HOURS);
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
MessageBox.Show("SUCCESSFULLY INSERTED");
objDBConnect.sqlDR.Close();
objDBConnect.sqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error Cannot Submit Vehicle Details: " + ex.Message);
}
}
else
{
MessageBox.Show("Please enter a Timesheet ID");
}
}
private void frmSearch_Load(object sender, EventArgs e)
{
//cboE_ID = E_ID;
try
{
string query = "SELECT Emp_ID from Employee;";
objDBConnect.OpenConnection();
SqlDataAdapter da = new SqlDataAdapter(query, objDBConnect.sqlConn);
DataSet ds = new DataSet();
da.Fill(ds, "Employee");
cboE_ID.ValueMember = "Emp_ID";
cboE_ID.DisplayMember = "Emp_ID";
cboE_ID.DataSource = ds.Tables["Employee"];
objDBConnect.sqlConn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message); // Shows an error message
}
try
{
string query = "SELECT Cust_ID from Customer;";
objDBConnect.OpenConnection();
SqlDataAdapter da = new SqlDataAdapter(query, objDBConnect.sqlConn);
DataSet ds = new DataSet();
da.Fill(ds, "Customer");
cboC_ID.ValueMember = "Cust_ID";
cboC_ID.DisplayMember = "Cust_ID";
cboC_ID.DataSource = ds.Tables["Customer"];
objDBConnect.sqlConn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message); // Shows an error message
}
try
{
string query = "SELECT Location_ID from Location;";
objDBConnect.OpenConnection();
SqlDataAdapter da = new SqlDataAdapter(query, objDBConnect.sqlConn);
DataSet ds = new DataSet();
da.Fill(ds, "Location");
cboL_ID.ValueMember = "Location_ID";
cboL_ID.DisplayMember = "Location_ID";
cboL_ID.DataSource = ds.Tables["Location"];
objDBConnect.sqlConn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message); // Shows an error message
}
try
{
string query = "SELECT Trip_ID from TripUsage;";
objDBConnect.OpenConnection();
SqlDataAdapter da = new SqlDataAdapter(query, objDBConnect.sqlConn);
DataSet ds = new DataSet();
da.Fill(ds, "TripUsage");
cboT_ID.ValueMember = "Trip_ID";
cboT_ID.DisplayMember = "Trip_ID";
cboT_ID.DataSource = ds.Tables["TripUsage"];
objDBConnect.sqlConn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message); // Shows an error message
}
try
{
string query = "SELECT Vehicle_RegNumber from Vehicle;";
objDBConnect.OpenConnection();
SqlDataAdapter da = new SqlDataAdapter(query, objDBConnect.sqlConn);
DataSet ds = new DataSet();
da.Fill(ds, "Vehicle");
cboV_RN.ValueMember = "Vehicle_RegNumber";
cboV_RN.DisplayMember = "Vehicle_RegNumber";
cboV_RN.DataSource = ds.Tables["Vehicle"];
objDBConnect.sqlConn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message); // Shows an error message
}
try
{
string query = "SELECT Service_ID from tblService;";
objDBConnect.OpenConnection();
SqlDataAdapter da = new SqlDataAdapter(query, objDBConnect.sqlConn);
DataSet ds = new DataSet();
da.Fill(ds, "tblService");
cboS_ID.ValueMember = "Service_ID";
cboS_ID.DisplayMember = "Service_ID";
cboS_ID.DataSource = ds.Tables["tblService"];
objDBConnect.sqlConn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message); // Shows an error message
}
}
private void btnPassReset_Click(object sender, EventArgs e)
{
//Code adapted from: http://www.codeproject.com/Tips/520998/Send-Email-from-Yahoo-Gmail-Hotmail-Csharp
Check check = new Check();
GeneratePassword newPass = new GeneratePassword();
bool exit = false;
string tempPass = newPass.GeneratePass(10);
userName = txtUser.Text;
exit = check.CheckEmpty(userName, "Username", exit);
if (exit == false)
{
try
{
DBConnect objDBConnect = new DBConnect();
objDBConnect.OpenConnection();
objDBConnect.sqlCmd = new SqlCommand("UPDATE Register SET R_PWORD = @R_PWORD WHERE R_UNAME = @R_UNAME", objDBConnect.sqlConn);
objDBConnect.sqlCmd.Parameters.AddWithValue("@R_PWORD", tempPass);
objDBConnect.sqlCmd.Parameters.AddWithValue("@R_UNAME", userName);
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
objDBConnect.sqlDR.Close();
MessageBox.Show("Successfully updated password");
objDBConnect.sqlCmd = new SqlCommand("SELECT R_EMAIL FROM Register WHERE R_UNAME = @R_UNAME", objDBConnect.sqlConn);
objDBConnect.sqlCmd.Parameters.AddWithValue("@R_UNAME", userName);
string email = ((string)objDBConnect.sqlCmd.ExecuteScalar());
objDBConnect.sqlConn.Close();
string smtpAddress = "smtp.gmail.com";
int portNumber = 587;
bool enableSSL = true;
string emailFrom = "*****@*****.**";
string password = "******";
string emailTo = email;
string subject = "Password Reset";
string body = "You have requested a password reset, your new temporary password/Security Code is: " + tempPass + ". Please change your password as soon as possible.";
using (MailMessage mail = new MailMessage())
{
mail.From = new MailAddress(emailFrom);
mail.To.Add(emailTo);
mail.Subject = subject;
mail.Body = body;
mail.IsBodyHtml = false;
using (SmtpClient smtp = new SmtpClient(smtpAddress, portNumber))
{
smtp.Credentials = new NetworkCredential(emailFrom, password);
smtp.EnableSsl = enableSSL;
smtp.Send(mail);
MessageBox.Show("An email has been sent. If you do not receive the email within 5 minutes check that the username you entered is correct");
}
}
this.Hide();
frmPasswordReset passReset = new frmPasswordReset();
passReset.Show();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (SmtpFailedRecipientException ex)
{
MessageBox.Show(ex.Message);
}
catch (SmtpException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
try// allows user to choose which fields they want to update.
{
L_ID = Interaction.InputBox("Please enter Location ID: ", "Location ID", "");
update = int.Parse(Interaction.InputBox("Please enter the number associated with field you want to update:\n 1.Number of Employees\n2. Number of Vehicles\n3. Location Manager ", "Updates", "Default Text"));
while (update != 1 && update != 2 && update != 3)
{
update = int.Parse(Interaction.InputBox("Please enter correct number associated with field you want to update:\n 1.Number of Employees\n2. Number of Vehicles\n3. Location Manager ", "Updates", "Default Text"));
}
if (update == 1)
{
L_EMPLOYEES = int.Parse(Interaction.InputBox("Please enter Number of Employees: ", "Number of Employees", ""));
sql = "Location_NumEmployees= " + L_EMPLOYEES;
}
else
{
if (update == 2)
{
L_VEHICLES = int.Parse(Interaction.InputBox("Please enter Number of Vehicles: ", "Number of vehicles", ""));
sql = "Location_NumVehicles = " + L_VEHICLES;
}
else
{
if (update == 3)
{
L_MANAGER = Interaction.InputBox("Please enter Location Manager: ", "Location Manager", "");
sql = "Location_Manager = " + L_MANAGER;
}
else
{
MessageBox.Show("Please enter corresponding numbers only");
}
}
}
try
{
DBConnect objDBConnect = new DBConnect();
objDBConnect.OpenConnection();
objDBConnect.sqlCmd = new SqlCommand("UPDATE LOCATION SET " + sql + " WHERE Location_ID = @Location_ID", objDBConnect.sqlConn);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Location_ID", L_ID);
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
MessageBox.Show("SUCCESSFULLY UPDATED");
objDBConnect.sqlDR.Close();
objDBConnect.sqlConn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Error Cannot Update Location Details: " + ex.Message);
}
}
catch (Exception)
{
MessageBox.Show("Invalid option: Cannot Update.");
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
C_ID = txtID.Text;
C_EMAIL = txtEmail.Text;
try
{
var addr = new System.Net.Mail.MailAddress(C_EMAIL);// validates email address
}
catch (Exception ex)
{
MessageBox.Show("Error Cannot Validate Email Address: " + ex.Message);
}
Check check = new Check();
bool exit = false;
try
{
C_TYPE = this.cboCustomer.GetItemText(this.cboCustomer.SelectedItem);
//C_TYPE = cboCustomer.SelectedValue.ToString();
}
catch (Exception ex)
{
MessageBox.Show("Cobobox error: " + ex.Message);
}
C_CONTACT = txtContact.Text;
C_DUE = txtPaymentDue.Text;
C_MADE = txtPaymentMade.Text;
C_NAME = txtName.Text;
C_SNAME = txtSurname.Text;
exit = check.CheckEmpty(C_TYPE, "Customer Type", exit);
exit = check.CheckEmpty(C_CONTACT, "Customer Contact Number", exit);
exit = check.CheckEmpty(C_DUE, "Payment Due", exit);
exit = check.CheckEmpty(C_MADE, "Payment Made", exit);
exit = check.CheckEmpty(C_NAME, "Customer Name", exit);
exit = check.CheckEmpty(C_SNAME, "Customer Surname", exit);
exit = check.CheckForNumbers(C_NAME, "Customer Name");
exit = check.CheckForNumbers(C_SNAME, "Customer Surname");
exit = check.CheckForLetters(C_CONTACT, "Customer Contact Number");
exit = check.CheckForLetters(C_DUE, "Payment Due");
exit = check.CheckForLetters(C_MADE, "Payment Made");
if (exit == false)
{
try
{
bool executeSQL = check.CheckDB("Customer", "Cust_ID", C_ID);
if (executeSQL == false)
{
DBConnect objDBConnect = new DBConnect();
objDBConnect.OpenConnection();
objDBConnect.sqlCmd = new SqlCommand("INSERT INTO Customer VALUES (@Cust_ID, @Cust_Name, @Cust_Surname, @Cust_Type, @Cust_ContactNo,@Cust_Email, @Cust_PayDue, @Cust_PayMade)", objDBConnect.sqlConn);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_ID", C_ID);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_Name", C_NAME);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_Surname", C_SNAME);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_Type", C_TYPE);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_ContactNo", C_CONTACT);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_Email", C_EMAIL);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_PayDue", C_DUE);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_PayMade", C_MADE);
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
MessageBox.Show("Successfully Inserted");
objDBConnect.sqlDR.Close();
objDBConnect.sqlConn.Close();
}
else
{
MessageBox.Show("That Customer ID already exists in the database");
}
}
catch (SqlException ex)
{
MessageBox.Show("Error cannot add customer details " + ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Error Cannot Add Customer Details: " + ex.Message + ex.Data + ex.StackTrace);
}
}
else
{
MessageBox.Show("One or more fields are empty");
}
}
private void btnSubmit_Click(object sender, EventArgs e)
{
Check check = new Check();
bool exit = false;
getValues();
try
{
var addr = new System.Net.Mail.MailAddress(E_EMAIL);// Validates email address
accepted = true;
}
catch (Exception ex)
{
MessageBox.Show("Error Invalid email address: " + ex.Message); // Shows an error message
}
E_SALARY = txtSalary.Text;
Employee objEmp = new Employee(E_ID, E_NAME, E_SNAME, E_POS, E_CONTACT, E_EMAIL, E_SALARY);
exit = check.CheckEmpty(E_NAME, "Employee Name", exit);
exit = check.CheckEmpty(E_ID, "Employee ID", exit);
exit = check.CheckEmpty(E_SNAME, "Employee Surname", exit);
exit = check.CheckEmpty(E_POS, "Employee Position", exit);
exit = check.CheckEmpty(E_CONTACT, "Employee Contact", exit);
exit = check.CheckEmpty(E_EMAIL, "Employee Email", exit);
exit = check.CheckForNumbers(E_NAME, "Employee Name");
exit = check.CheckForNumbers(E_SNAME, "Employee Surname");
exit = check.CheckForLetters(E_CONTACT, "Employee Contact Number");
exit = check.CheckForLetters(E_SALARY, "Employee Salary");
if (accepted == true)
{
if (success == true)
{
try
{
bool executeSQL = check.CheckDB("Employee", "Emp_ID", E_ID);
if (executeSQL == false)
{
DBConnect objDBConnect = new DBConnect();
objDBConnect.OpenConnection();
objDBConnect.sqlCmd = new SqlCommand("INSERT INTO Employee VALUES (@Emp_ID, @Emp_Name, @Emp_Surname, @Emp_Position,@Emp_Address, @Emp_ContactNo, @Emp_Email, @Emp_MonthlySalary)", objDBConnect.sqlConn);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_ID", E_ID);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_Name", E_NAME);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_Surname", E_SNAME);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_Position", E_POS);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_Address", E_ADDRESS);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_ContactNo", E_CONTACT);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_Email", E_EMAIL);
objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_MonthlySalary", E_SALARY);
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
string message = objEmp.SuccessMessage();
MessageBox.Show(message);
objDBConnect.sqlDR.Close();
objDBConnect.sqlConn.Close();
}
else
{
MessageBox.Show("That Employee ID already exists in the database");
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Error Cannot Add Employee Details: " + ex.Message);
}
}
}
}
private void frmReports_Load(object sender, EventArgs e)
{
this.WindowState = FormWindowState.Maximized;
try // Resizes Columns in the Datagridviews
{
dgvDetailedService.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dgvCTDaily.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dgvCTMonthly.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dgvCTWeekly.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dgvPTDaily.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dgvPTWeekly.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dgvSADaily.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; // Resizes the Column
dgvSAWeekly.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dgvServiceReq.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dgvTSDaily.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dgvTSMonthly.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dgvTSWeekly.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dgvVehicleStatus.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dgvVSDaily.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dgvVSMonthly.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dgvVSWeekly.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dgvVSYearly.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
}
catch (Exception ex)
{
MessageBox.Show("Problem with Resizing Data Grid Views: " + ex); // Shows Error Message if there is a Problem
}
db.OpenConnection();
try // Adds Data to the Data Grid Views
{
string selectVehicleStatus = "SELECT * FROM Vehicle ORDER BY Vehicle_Year"; // Sql For Data Grid View
db.sqlDA = new SqlDataAdapter(selectVehicleStatus, db.sqlConn);
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(db.sqlDA);
DataSet ds = new DataSet();
db.sqlDA.Fill(ds); // Fills the DataSet
dgvVehicleStatus.ReadOnly = true;
dgvVehicleStatus.DataSource = ds.Tables[0];
string selectServiceAppointDaily = "Select * FROM tblService WHERE Month(Service_Date) = MONTH(GETDATE()) AND YEAR(Service_Date) = YEAR(GETDATE()) AND DAY(Service_Date) = DAY(GETDATE())";
db.sqlDA = new SqlDataAdapter(selectServiceAppointDaily, db.sqlConn);
SqlCommandBuilder commandBuilder2 = new SqlCommandBuilder(db.sqlDA);
DataSet ds2 = new DataSet();
db.sqlDA.Fill(ds2);
dgvSADaily.ReadOnly = true;
dgvSADaily.DataSource = ds2.Tables[0];
string selectServiceAppointWeekly = "SELECT * FROM tblService WHERE Service_Date BETWEEN GETDATE()-1 and GETDATE()+5 ORDER BY Service_Date";
db.sqlDA = new SqlDataAdapter(selectServiceAppointWeekly, db.sqlConn);
SqlCommandBuilder commandBuilder11 = new SqlCommandBuilder(db.sqlDA);
DataSet ds11 = new DataSet();
db.sqlDA.Fill(ds11);
dgvSAWeekly.ReadOnly = true;
dgvSAWeekly.DataSource = ds11.Tables[0];
string selectServiceReq = "SELECT Vehicle_RegNumber, Service_Description, Service_Date, Service_Time FROM tblService WHERE Service_Date >= GETDATE() ORDER BY Service_Date";
db.sqlDA = new SqlDataAdapter(selectServiceReq, db.sqlConn);
SqlCommandBuilder commandBuilder3 = new SqlCommandBuilder(db.sqlDA);
DataSet ds3 = new DataSet();
db.sqlDA.Fill(ds3);
dgvServiceReq.ReadOnly = true;
dgvServiceReq.DataSource = ds3.Tables[0];
string selectVehicleServicesDaily = "SELECT * FROM tblService WHERE Month(service_date) = MONTH(GETDATE()) AND YEAR(service_date) = YEAR(GETDATE()) AND DAY(service_date) = DAY(GETDATE())"; // Sql For Data Grid View
db.sqlDA = new SqlDataAdapter(selectVehicleServicesDaily, db.sqlConn);
SqlCommandBuilder commandBuilder4 = new SqlCommandBuilder(db.sqlDA);
DataSet ds4 = new DataSet();
db.sqlDA.Fill(ds4); // Fills the DataSet
dgvVSDaily.ReadOnly = true;
dgvVSDaily.DataSource = ds4.Tables[0];
string selectVehicleServicesWeekly = "SELECT * FROM tblService WHERE service_date BETWEEN getdate()-5 and getdate()";
db.sqlDA = new SqlDataAdapter(selectVehicleServicesWeekly, db.sqlConn);
SqlCommandBuilder commandBuilder12 = new SqlCommandBuilder(db.sqlDA);
DataSet ds12 = new DataSet();
db.sqlDA.Fill(ds12);
dgvVSWeekly.ReadOnly = true;
dgvVSWeekly.DataSource = ds12.Tables[0];
string selectVehicleServicesMonthly = "SELECT * FROM tblService WHERE Service_Date < getdate() and Month(Service_Date) = Month(GETDATE())";
db.sqlDA = new SqlDataAdapter(selectVehicleServicesMonthly, db.sqlConn);
SqlCommandBuilder commandBuilder13 = new SqlCommandBuilder(db.sqlDA);
DataSet ds13 = new DataSet();
db.sqlDA.Fill(ds13);
dgvVSMonthly.ReadOnly = true;
dgvVSMonthly.DataSource = ds13.Tables[0];
string selectVehicleServicesYearly = "SELECT * FROM tblService WHERE Service_Date < getdate() AND Year(Service_Date) = YEAR(GETDATE())";
db.sqlDA = new SqlDataAdapter(selectVehicleServicesYearly, db.sqlConn);
SqlCommandBuilder commandBuilder14 = new SqlCommandBuilder(db.sqlDA);
DataSet ds14 = new DataSet();
db.sqlDA.Fill(ds14);
dgvVSYearly.ReadOnly = true;
dgvVSYearly.DataSource = ds14.Tables[0];
string selectDetailService = "SELECT * FROM tblService WHERE Service_Date < GETDATE() ORDER BY Service_Date"; // Sql For Data Grid View
db.sqlDA = new SqlDataAdapter(selectDetailService, db.sqlConn);
SqlCommandBuilder commandBuilder5 = new SqlCommandBuilder(db.sqlDA);
DataSet ds5 = new DataSet();
db.sqlDA.Fill(ds5); // Fills the DataSet
dgvDetailedService.ReadOnly = true;
dgvDetailedService.DataSource = ds5.Tables[0];
string selectPlannedTripDaily = "Select * FROM TripUsage WHERE Month(Trip_DateFrom) = MONTH(GETDATE()) AND YEAR(Trip_DateFrom) = YEAR(GETDATE()) AND DAY(Trip_DateFrom) = DAY(GETDATE()) AND Trip_Completed LIKE 'NO'";
db.sqlDA = new SqlDataAdapter(selectPlannedTripDaily, db.sqlConn);
SqlCommandBuilder commandBuilder6 = new SqlCommandBuilder(db.sqlDA);
DataSet ds6 = new DataSet();
db.sqlDA.Fill(ds6);
dgvPTDaily.ReadOnly = true;
dgvPTDaily.DataSource = ds6.Tables[0];
string selectPlannedTripWeekly = "SELECT * FROM TripUsage WHERE Trip_DateFrom BETWEEN GETDATE() and GETDATE()+5 AND Trip_Completed LIKE 'NO'";
db.sqlDA = new SqlDataAdapter(selectPlannedTripWeekly, db.sqlConn);
SqlCommandBuilder commandBuilder15 = new SqlCommandBuilder(db.sqlDA);
DataSet ds15 = new DataSet();
db.sqlDA.Fill(ds15);
dgvPTWeekly.ReadOnly = true;
dgvPTWeekly.DataSource = ds15.Tables[0];
string selectCompletedTripDaily = "Select * FROM TripUsage WHERE Month(Trip_DateFrom) = MONTH(GETDATE()) AND YEAR(Trip_DateFrom) = YEAR(GETDATE()) AND DAY(Trip_DateFrom) = DAY(GETDATE()) AND Trip_Completed LIKE 'YES'";
db.sqlDA = new SqlDataAdapter(selectCompletedTripDaily, db.sqlConn);
SqlCommandBuilder commandBuilder7 = new SqlCommandBuilder(db.sqlDA);
DataSet ds7 = new DataSet();
db.sqlDA.Fill(ds7);
dgvCTDaily.ReadOnly = true;
dgvCTDaily.DataSource = ds7.Tables[0];
string selectCompletedTripWeekly = "SELECT * FROM TripUsage WHERE Trip_DateFrom BETWEEN GETDATE() and GETDATE()+5 AND Trip_Completed LIKE 'YES' ORDER BY Trip_DateFrom";
db.sqlDA = new SqlDataAdapter(selectCompletedTripWeekly, db.sqlConn);
SqlCommandBuilder commandBuilder16 = new SqlCommandBuilder(db.sqlDA);
DataSet ds16 = new DataSet();
db.sqlDA.Fill(ds16);
dgvCTWeekly.ReadOnly = true;
dgvCTWeekly.DataSource = ds16.Tables[0];
string selectCompletedTripMonthly = "SELECT * FROM TripUsage WHERE MONTH(Trip_DateFrom) = MONTH(GETDATE()) AND Trip_Completed LIKE 'YES'";
db.sqlDA = new SqlDataAdapter(selectCompletedTripMonthly, db.sqlConn);
SqlCommandBuilder commandBuilder17 = new SqlCommandBuilder(db.sqlDA);
DataSet ds17 = new DataSet();
db.sqlDA.Fill(ds17);
dgvCTMonthly.ReadOnly = true;
dgvCTMonthly.DataSource = ds17.Tables[0];
string selectTimeSheetWeek = "SELECT e.Emp_Name, e.Emp_Surname, e.Emp_ID, s.[Total Hours Weekly] FROM Employee e JOIN (SELECT Emp_ID, SUM(T_HOURS) AS [Total Hours Weekly] FROM Timesheet s WHERE T_DATE BETWEEN GETDATE() and GETDATE()+5 GROUP BY Emp_ID) as s ON e.Emp_ID = s.Emp_ID"; // Sql for Data Grid View
db.sqlDA = new SqlDataAdapter(selectTimeSheetWeek, db.sqlConn);
SqlCommandBuilder commandBuilder8 = new SqlCommandBuilder(db.sqlDA);
DataSet ds8 = new DataSet();
db.sqlDA.Fill(ds8); // Fills the DataSet
dgvTSWeekly.ReadOnly = true;
dgvTSWeekly.DataSource = ds8.Tables[0];
string selectTimeSheetMonth = "SELECT e.Emp_Name, e.Emp_Surname, e.Emp_ID, s.[Total Hours Monthly] FROM Employee e JOIN (SELECT Emp_ID, SUM(T_HOURS) AS [Total Hours Monthly] FROM Timesheet s WHERE Month(T_DATE) = Month(GETDATE())GROUP BY Emp_ID) as s ON e.Emp_ID = s.Emp_ID";
db.sqlDA = new SqlDataAdapter(selectTimeSheetMonth, db.sqlConn);
SqlCommandBuilder commandBuilder9 = new SqlCommandBuilder(db.sqlDA);
DataSet ds9 = new DataSet();
db.sqlDA.Fill(ds9);
dgvTSMonthly.ReadOnly = true;
dgvTSMonthly.DataSource = ds9.Tables[0];
string selectTimeSheetDaily = "SELECT e.Emp_Name, e.Emp_Surname, e.Emp_ID, t.T_ID as 'Timesheet ID', SUM(t.T_HOURS) AS 'Total Hours Worked-Daily' FROM Employee e inner join Timesheet t on e.Emp_ID = t.Emp_ID WHERE Day(T_DATE) = day(GETDATE())and Month(T_DATE) = Month(GETDATE()) and year(T_DATE) = year(GETDATE()) GROUP BY e.Emp_Name, e.Emp_Surname, e.Emp_ID,t.T_ID,t.T_HOURS;";
db.sqlDA = new SqlDataAdapter(selectTimeSheetDaily, db.sqlConn);
SqlCommandBuilder commandBuilder10 = new SqlCommandBuilder(db.sqlDA);
DataSet ds10 = new DataSet();
db.sqlDA.Fill(ds10);
dgvTSDaily.ReadOnly = true;
dgvTSDaily.DataSource = ds10.Tables[0];
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Error Cannot Load Sql Commands to The Data Grid Views: " + ex.Message); // Shows an error message
}
}
private void btnRegister_Click(object sender, EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;//this is used to show the user that a process is occuring
R_DOB = dtpDateOfBirth.Value.ToShortDateString();
R_NAME = txtName.Text;
R_SNAME = txtSurname.Text;
R_UNAME = txtUserName.Text;
R_PWORD = txtPass.Text;
R_EMAIL = txtEmail.Text;
R_CURRDATE = CurrDate(R_CURRDATE);
R_EMPPOS = cboEmpPosition.SelectedItem.ToString();
bool email = false;
if (!R_NAME.Equals(""))
{
if (!R_SNAME.Equals(""))
{
if (!(cboEmpPosition.SelectedIndex < 0))
{
if (!R_EMAIL.Equals(""))
{
try
{
var addr = new System.Net.Mail.MailAddress(R_EMAIL);// validates email address
email = true;
}
catch
{
}
if (email == true)
{
if (!R_UNAME.Equals(""))
{
if (!R_PWORD.Equals(""))
{
try
{
Check check = new Check();
bool executeSQL = check.CheckDB("Register", "R_UNAME", R_UNAME);
if (executeSQL == false)
{
DBConnect objDBConnect = new DBConnect();
objDBConnect.OpenConnection();
objDBConnect.sqlCmd = new SqlCommand("INSERT INTO Register VALUES (@R_UNAME,@R_DOB, @R_NAME, @R_SNAME,@R_EMPPOS,@R_EMAIL, @R_PWORD)", objDBConnect.sqlConn);
objDBConnect.sqlCmd.Parameters.AddWithValue("@R_UNAME", R_UNAME);
objDBConnect.sqlCmd.Parameters.AddWithValue("@R_DOB", SqlDbType.Date).Value = dtpDateOfBirth.Value.Date;
objDBConnect.sqlCmd.Parameters.AddWithValue("@R_NAME", R_NAME);
objDBConnect.sqlCmd.Parameters.AddWithValue("@R_SNAME", R_SNAME);
objDBConnect.sqlCmd.Parameters.AddWithValue("@R_EMPPOS", R_EMPPOS);
objDBConnect.sqlCmd.Parameters.AddWithValue("@R_EMAIL", R_EMAIL);
objDBConnect.sqlCmd.Parameters.AddWithValue("@R_PWORD", R_PWORD);
objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader();
objDBConnect.sqlDR.Close();
objDBConnect.sqlConn.Close();
string s = R_DOB + "," + R_NAME + "," + R_SNAME + "," + R_EMPPOS + "," + R_UNAME + "," + R_PWORD;
MessageBox.Show("You have been successfully registered. Please be patient while an email is sent to you.");
try
{
SmtpClient client = new SmtpClient("smtp.gmail.com");
client.Port = 587;
client.EnableSsl = true;
client.Timeout = 100000;
client.DeliveryMethod = SmtpDeliveryMethod.Network;
client.UseDefaultCredentials = false;
client.Credentials = new NetworkCredential(
R_MEMAIL, R_EPWORD);//logs into your email account
//gets values from the textboxes
MailMessage msg = new MailMessage();
msg.To.Add(txtEmail.Text);
msg.From = new MailAddress(R_MEMAIL);//checks that email address exists
msg.Subject = "Successful Registration - Fleet Tracking Application";
msg.Body = "Hello " + R_NAME.ToUpper() + " " + R_SNAME.ToUpper() + "\n\nThis is confirmation indicating that you have successfully registered to use the Fleet Tracking Application. \n\nDate: " + R_CURRDATE + "\nUser Name: " + R_UNAME + "\n(Use this to log into the application, along with your password)\n\nKind Regards,\nFleet Tracking Team\n(0312521212)";
client.Send(msg);
MessageBox.Show("Registration Successful\nConfirmation Email Sent to: " + R_MEMAIL);
Cursor.Current = Cursors.Default;// when processing is done default curser will appear
}
catch (Exception ex)
{
MessageBox.Show("Email Confirmation Not Sent:\n" + ex.Message);
}
}
else
{
MessageBox.Show("That username already exists");
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Error Cannot Be Registered: " + ex.Message);
}
}
else
{
MessageBox.Show("Enter A Password");
}
}
else
{
MessageBox.Show("Please Enter a Username");
}
}
else
{
MessageBox.Show("Invalid Email Address: " + R_EMAIL);
}
}
else
{
MessageBox.Show("Please Enter Your Email Address");
}
}
else
{
MessageBox.Show("Please Select The Employees Position");
}
}
else
{
MessageBox.Show("Please Enter Your Surname");
}
}
else
{
MessageBox.Show("Please Enter Your Name");
}
}