public static void submitRequest(RadioButton rbRepairType1, RadioButton rbRepairType2, DropDownList carMakeDropdown)
{
string repairType = "";
if (rbRepairType1.Checked)
{
repairType = "Basic Repair";
}
else if (rbRepairType2.Checked)
{
repairType = "Complete Repair";
}
string carMake = carMakeDropdown.SelectedValue;
string incidentDescription = carMake + " " + repairType;
string chooseKit = incidentDescription + " Kit";
//default supervisor id for all requests will be 7 for assistant location manager to reassign to a technician supervisor
string connection = SQLFunction.sqlConnection();
MySqlConnection conn = new MySqlConnection(connection);
conn.Open();
//-------------------------------------------------------------------------------------------------
//Insert new Incident Request
string sql = "INSERT INTO incident_requests (Customer_ID, Date_Completed, Date_Requested, Date_Started, Incident_Description, Package_ID, Supervisor_ID) VALUES ((SELECT c.Customer_ID FROM customers c JOIN accounts a ON c.Customer_ID = a.Customer_ID WHERE Acc_Username = @username), NULL, CURDATE(), NULL, @incidentDescription, (SELECT Package_ID FROM repair_packages WHERE Package_Name = @packageName), 7)";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@username", HttpContext.Current.Session["username"]));
cmd.Parameters.Add(new MySqlParameter("@incidentDescription", @incidentDescription));
cmd.Parameters.Add(new MySqlParameter("@packageName", @chooseKit));
MySqlDataReader reader = cmd.ExecuteReader();
reader.Close();
conn.Close();
HttpContext.Current.Response.Write("<script>alert('Thank you for submitting your request.');window.location='dashboard.aspx';</script>");
}
public static void loadPayment_Data(DropDownList invoiceDropDown)
{
string connection = SQLFunction.sqlConnection();
MySqlConnection conn = new MySqlConnection(connection);
conn.Open();
//-------------------------------------------------------------------------------------------------
//Get user's completed invoice ids from database
string sqlCount = "SELECT COUNT(Invoice_ID) AS ArraySize FROM invoices i JOIN accounts a ON i.Account_ID = a.Account_ID WHERE Acc_Username = @username";
MySqlCommand cmdCount = new MySqlCommand(sqlCount, conn);
cmdCount.Parameters.Add(new MySqlParameter("@username", HttpContext.Current.Session["username"]));
MySqlDataReader readerCount = cmdCount.ExecuteReader();
while (readerCount.Read())
{
setArray(Convert.ToInt32(readerCount.GetString("ArraySize")));
}
readerCount.Close();
string sql = "SELECT DATE_FORMAT(Invoice_Date, '%m/%d/%Y') AS Invoice_Date, Invoice_ID FROM invoices i JOIN accounts a ON i.Account_ID = a.Account_ID WHERE Acc_Username = @username";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@username", HttpContext.Current.Session["username"]));
MySqlDataReader reader = cmd.ExecuteReader();
int index = 0;
if (invoiceDropDown.Items.Count <= 0)
{
while (reader.Read())
{
invoiceDropDown.Items.Insert(index, reader.GetString("Invoice_Date"));
getArray().SetValue(reader.GetString("Invoice_ID"), index);
index++;
}
}
reader.Close();
}
public static bool checkIncidents()
{
bool valid = true;
string connection = SQLFunction.sqlConnection();
MySqlConnection conn = new MySqlConnection(connection);
conn.Open();
//-------------------------------------------------------------------------------------------------
//Get user's active incident requests from database
string sql = "SELECT COUNT(Incident_ID) AS ActiveIncidents FROM incident_requests ir JOIN accounts a ON ir.Customer_ID = a.Customer_ID WHERE Acc_Username = @username AND Date_Started IS NOT NULL AND Date_Completed IS NULL";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@username", HttpContext.Current.Session["username"]));
MySqlDataReader reader = cmd.ExecuteReader();
int activeIncidents = 0;
while (reader.Read())
{
activeIncidents = Convert.ToInt32(reader.GetString("ActiveIncidents"));
}
reader.Close();
conn.Close();
if (activeIncidents > 0)
{
//do nothing and continue
valid = true;
}
else
{
valid = false;
HttpContext.Current.Response.Write("<script>alert('You do not currently have any active incident requests.');window.location='dashboard.aspx';</script>");
}
return(valid);
}
public static bool checkInvoices()
{
bool valid = true;
string connection = SQLFunction.sqlConnection();
MySqlConnection conn = new MySqlConnection(connection);
conn.Open();
//-------------------------------------------------------------------------------------------------
//Get user's active invoices from database
string sql = "SELECT COUNT(Invoice_ID) AS InvoiceCount FROM invoices i JOIN accounts a ON i.Account_ID = a.Account_ID WHERE Acc_Username = @username AND (Invoice_Total - Payment_Total - Credit_Total) != 0";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@username", HttpContext.Current.Session["username"]));
MySqlDataReader reader = cmd.ExecuteReader();
int invoiceCount = 0;
while (reader.Read())
{
invoiceCount = Convert.ToInt32(reader.GetString("InvoiceCount"));
}
reader.Close();
conn.Close();
if (invoiceCount > 0)
{
//do nothing and continue
valid = true;
}
else
{
valid = false;
HttpContext.Current.Response.Write("<script>alert('You do not currently have any active invoices.');window.location='dashboard.aspx';</script>");
}
return(valid);
}
//=====================change to supervisor only
public static void loadContactButtonInfo(LinkButton btnSupervisor, Label supervisorNameLabel)
{
string supervisorName = supervisorNameLabel.Text;
btnSupervisor.Text = supervisorName;
string connection = SQLFunction.sqlConnection();
MySqlConnection conn = new MySqlConnection(connection);
conn.Open();
string sql = "SELECT S_Email FROM supervisors WHERE CONCAT(S_First_Name, ' ', S_Last_Name) = @supervisor";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@supervisor", supervisorName));
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
btnSupervisor.OnClientClick = "window.open('mailto:" + reader.GetString("S_Email") + "', 'email')";
}
reader.Close();
}
public static int getRequestsSubmittedToday()
{
int submitted = 0;
string connection = SQLFunction.sqlConnection();
MySqlConnection conn = new MySqlConnection(connection);
conn.Open();
//-------------------------------------------------------------------------------------------------
//Get number of requests user has submitted on the current date
string sql = "SELECT COUNT(Date_Requested) AS RequestsToday FROM incident_requests ir JOIN accounts a ON ir.Customer_ID = a.Customer_ID WHERE Acc_Username = @username AND Date_Requested = CURDATE()";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@username", HttpContext.Current.Session["username"]));
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
submitted = (Convert.ToInt32(reader.GetString("RequestsToday")));
}
return(submitted);
}
public static void checkUserInput_Account(TextBox txtEmail)
{
if (txtEmail.Text == "")
{
HttpContext.Current.Response.Write("<script>alert('All fields are required');</script>");
}
else
{
string input = @txtEmail.Text;
if (!Regex.IsMatch(input, "^[A-Za-z0-9]+(-|.)?[A-Za-z0-9]+@[A-Za-z]+.[A-Za-z]+$"))
{
HttpContext.Current.Response.Write("<script>alert('Please enter a valid email address.');</script>");
}
else
{
txtEmail.Text = input;
string connection = SQLFunction.sqlConnection();
MySqlConnection conn = new MySqlConnection(connection);
conn.Open();
string sql = "UPDATE accounts SET Acc_Email = @email WHERE Acc_Username = @username";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@username", HttpContext.Current.Session["username"]));
cmd.Parameters.Add(new MySqlParameter("@email", txtEmail.Text));
MySqlDataReader reader = cmd.ExecuteReader();
reader.Close();
conn.Close();
HttpContext.Current.Session.Clear();
HttpContext.Current.Response.Write("<script>alert('Your information has been updated. Please login again.');window.location='login.aspx';</script>");
}
}
}
public static void check_login(TextBox userText, TextBox passText)
{
HttpContext.Current.Session["username"] = userText.Text;
HttpContext.Current.Session["password"] = passText.Text;
string connection = SQLFunction.sqlConnection();
MySqlConnection conn = new MySqlConnection(connection);
conn.Open();
//-------------------------------------------------------------------------------------------------
//Get user's number of incident requests from database
string sql = "SELECT COUNT(Account_ID) AS AccValid FROM accounts WHERE Acc_Username = @username AND Acc_Password = SHA1(@password)";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@username", HttpContext.Current.Session["username"]));
cmd.Parameters.Add(new MySqlParameter("@password", HttpContext.Current.Session["password"]));
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int validate = Convert.ToInt32(reader.GetString("AccValid"));
if (validate == 1)
{
HttpContext.Current.Response.Redirect("dashboard.aspx");
}
else
{
HttpContext.Current.Response.Write("<script>alert('Incorrect username or password.');</script>");
HttpContext.Current.Session["username"] = null;
HttpContext.Current.Session["password"] = null;
}
}
reader.Close();
conn.Close();
}
public static void loadIncidentInfo(DropDownList incidentProgressDropdown, Label technicianListLabel, Label dateStartedLabel, Label incidentDescriptionLabel, Label supervisorNameLabel, Label packageNameLabel, Label packageCostLabel)
{
string incidentID = getArray()[incidentProgressDropdown.SelectedIndex];
//clear list labels
technicianListLabel.Text = "";
string connection = SQLFunction.sqlConnection();
MySqlConnection conn = new MySqlConnection(connection);
conn.Open();
//-------------------------------------------------------------------------------------------------
//Get incident details based on selected incident
string sql = "SELECT DATE_FORMAT(Date_Started, '%m/%d/%Y') AS Date_Started, Incident_Description FROM incident_requests WHERE Incident_ID = @incidentID";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@incidentID", incidentID));
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
dateStartedLabel.Text = reader.GetString("Date_Started");
incidentDescriptionLabel.Text = reader.GetString("Incident_Description");
}
reader.Close();
//---------------------------------------
sql = "SELECT S_First_Name, S_Last_Name FROM supervisors s JOIN incident_requests ir ON s.Supervisor_ID = ir.Supervisor_ID WHERE Incident_ID = @incidentID";
cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@incidentID", incidentID));
reader = cmd.ExecuteReader();
while (reader.Read())
{
supervisorNameLabel.Text = (reader.GetString("S_First_Name") + " " + reader.GetString("S_Last_Name"));
}
reader.Close();
//---------------------------------------
sql = "SELECT E_First_Name, E_Last_Name FROM employees e JOIN incident_requests ir ON e.Supervisor_ID = ir.Supervisor_ID WHERE Incident_ID = @incidentID";
cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@incidentID", incidentID));
reader = cmd.ExecuteReader();
while (reader.Read())
{
technicianListLabel.Text += (reader.GetString("E_First_Name") + " " + reader.GetString("E_Last_Name") + "<br />");
}
reader.Close();
//---------------------------------------
sql = "SELECT Package_Name FROM repair_packages rp JOIN incident_requests ir ON rp.Package_ID = ir.Package_ID WHERE Incident_ID = @incidentID";
cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@incidentID", incidentID));
reader = cmd.ExecuteReader();
while (reader.Read())
{
packageNameLabel.Text = reader.GetString("Package_Name");
}
reader.Close();
//---------------------------------------
sql = "SELECT ROUND(SUM(Unit_Price * Amount_Needed), 2) AS PackageCost FROM inventory i JOIN package_items pi ON i.Part_ID = pi.Part_ID JOIN incident_requests ir ON pi.Package_ID = ir.Package_ID WHERE Incident_ID = @incidentID LIMIT 1";
cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@incidentID", incidentID));
reader = cmd.ExecuteReader();
while (reader.Read())
{
packageCostLabel.Text = reader.GetString("PackageCost");
}
reader.Close();
}
public static int loadProgressStats(DropDownList incidentProgressDropdown, Label progress1Label, Label progress2Label, Label progress3Label)
{
string incidentID = getArray()[incidentProgressDropdown.SelectedIndex];
string connection = SQLFunction.sqlConnection();
MySqlConnection conn = new MySqlConnection(connection);
conn.Open();
string sql = "SELECT DATEDIFF(CURRENT_DATE(), Date_Requested) AS daysSince FROM incident_requests WHERE Incident_ID = @incidentID";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@incidentID", incidentID));
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
progress1Label.Text = reader.GetString("daysSince");
}
reader.Close();
//---------------------------------------
sql = "SELECT DATEDIFF(Date_Started, Date_Requested) AS daysBetween FROM incident_requests WHERE Incident_ID = @incidentID";
cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@incidentID", incidentID));
reader = cmd.ExecuteReader();
while (reader.Read())
{
progress2Label.Text = reader.GetString("daysBetween");
}
reader.Close();
//---------------------------------------
sql = "SELECT Supervisor_ID FROM incident_requests WHERE Incident_ID = @incidentID";
cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@incidentID", incidentID));
reader = cmd.ExecuteReader();
string supervisorID = "";
while (reader.Read())
{
supervisorID = reader.GetString("Supervisor_ID");
}
reader.Close();
sql = "SELECT (COUNT(Supervisor_ID) - 1) AS Projects FROM incident_requests WHERE Supervisor_ID = @supervisorID";
cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@supervisorID", supervisorID));
reader = cmd.ExecuteReader();
while (reader.Read())
{
progress3Label.Text = reader.GetString("Projects");
}
reader.Close();
conn.Close();
//-----------------------------------------------------------
//Estimate progress on a scale from 0 to 10
int daysBetween = Convert.ToInt32(progress2Label.Text);
int otherProjects = Convert.ToInt32(progress3Label.Text);
int progress1, progress2, calculation;
switch (daysBetween)
{
case 0:
case 1:
case 2:
progress1 = 5;
break;
case 3:
case 4:
progress1 = 4;
break;
case 5:
case 6:
progress1 = 3;
break;
case 7:
case 8:
progress1 = 2;
break;
case 9:
case 10:
progress1 = 1;
break;
case 11:
case 12:
case 13:
case 14:
progress1 = 0;
break;
default:
progress1 = 0;
break;
}
switch (otherProjects)
{
case 0:
progress2 = 4;
break;
case 1:
progress2 = 3;
break;
case 2:
progress2 = 2;
break;
case 3:
case 4:
progress2 = 1;
break;
case 5:
progress2 = 0;
break;
default:
progress2 = 0;
break;
}
calculation = (progress1 + progress2);
return(calculation);
}
public static void checkUserInput_Password(TextBox txtOldPass, TextBox txtNewPass, TextBox txtRetype)
{
bool checkPass = false;
if (txtOldPass.Text == "" || txtNewPass.Text == "" || txtRetype.Text == "")
{
HttpContext.Current.Response.Write("<script>alert('All fields are required');</script>");
}
else
{
string input1 = @txtOldPass.Text,
input2 = @txtNewPass.Text,
input3 = @txtRetype.Text;
string connection = SQLFunction.sqlConnection();
MySqlConnection conn = new MySqlConnection(connection);
conn.Open();
string sql = "SELECT COUNT(Acc_Password) AS Pass FROM accounts WHERE Acc_Username = @username AND Acc_Password = SHA1(@password)";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@username", HttpContext.Current.Session["username"]));
cmd.Parameters.Add(new MySqlParameter("@password", input1));
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (Convert.ToInt32(reader.GetString("Pass")) <= 0)
{
checkPass = false;
}
else
{
checkPass = true;
}
}
reader.Close();
conn.Close();
if (checkPass == true)
{
if (input1 == input2)
{
HttpContext.Current.Response.Write("<script>alert('New password cannot match old password!');</script>");
}
else if (input2 != input3)
{
HttpContext.Current.Response.Write("<script>alert('New passwords must match!');</script>");
}
else
{
connection = SQLFunction.sqlConnection();
conn = new MySqlConnection(connection);
conn.Open();
sql = "UPDATE accounts SET Acc_Password = SHA1(@newpass) WHERE Acc_Username = @username";
cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@username", HttpContext.Current.Session["username"]));
cmd.Parameters.Add(new MySqlParameter("@newpass", txtNewPass.Text));
reader = cmd.ExecuteReader();
reader.Close();
conn.Close();
HttpContext.Current.Session.Clear();
HttpContext.Current.Response.Write("<script>alert('Your information has been updated. Please login again.');window.location='login.aspx';</script>");
}
}
else
{
HttpContext.Current.Response.Write("<script>alert('Your old password was entered incorrectly, please try again.');</script>");
}
}
}