protected void LoginControl_Authenticate(object sender, AuthenticateEventArgs e)
{
string email = Login1.UserName;
string password = Login1.Password;
string checker = "SELECT * FROM Customers WHERE Email = " + "'" + email + "' AND Password = "******"'" + password + "'";
SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString());
SqlCommand cmd = new SqlCommand(checker, con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (!rdr.HasRows)
{
Login1.FailureText = "Login Failed. Try Again.";
return;
}
else
{
if (rdr.Read())
{
string role = rdr["Role"].ToString();
string name = rdr["Name"].ToString();
Session["Email"] = email;
Session["Name"] = name;
Session["Role"] = role;
con.Close();
Response.Redirect("ContactUs.aspx");
}
}
}
public static IEnumerable GetAllTechnicians()
{
SqlConnection connection = new SqlConnection(TechSupportDB.GetConnectionString());
string select = "SELECT TechID, Name FROM Technicians ORDER BY Name";
SqlCommand cmd = new SqlCommand(select, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
return(reader);
}
public static IEnumerable GetAllTechinicians()
{
SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString());
string sel =
"SELECT TechID,Name , Email, Phone " + "FROM Technicians ORDER BY Name";
SqlCommand cmd = new SqlCommand(sel, con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return(rdr);
}
public static IEnumerable GetCustomerIncidents(int CustomerID)
{
SqlConnection connection = new SqlConnection(TechSupportDB.GetConnectionString());
string select = "SELECT IncidentID, ProductCode, DateOpened, DateClosed, Title, Description FROM Incidents WHERE TechID IS NOT NULL AND CustomerID=@CustomerID";
SqlCommand cmd = new SqlCommand(select, connection);
cmd.Parameters.AddWithValue("CustomerID", CustomerID);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
return(reader);
}
public static IEnumerable GetOpenIncidents(int TechID)
{
SqlConnection connection = new SqlConnection(TechSupportDB.GetConnectionString());
string select = "SELECT DateOpened, ProductCode, Name FROM Incidents JOIN Customers ON Incidents.CustomerID = Customers.CustomerID WHERE DateClosed IS NULL AND TechID=@TechID ORDER BY DateOpened";
SqlCommand cmd = new SqlCommand(select, connection);
cmd.Parameters.AddWithValue("TechID", TechID);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
return(reader);
}
public static IEnumerable GetCustomerList()
{
SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString());
string sel = "SELECT CustomerID, Name "
+ "FROM Customers "
+ "ORDER BY Name";
SqlCommand cmd = new SqlCommand(sel, con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return(dr);
}
protected void Button1_Click(object sender, EventArgs e)
{
Random rnd = new Random();
int id = rnd.Next(1, 10000);
string incident2 = Session["Chosen"].ToString();
string comment = TextBox1.Text.ToString() + " - " + Session["name"].ToString();
string statement = "INSERT INTO Forum (ForumID, comment, IncidentID) VALUES (" + "'" + id + "'" + ", " + "'" + comment + "'" + ", " + "'" + incident2 + "'" + ")";
SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString());
SqlCommand cmd = new SqlCommand(statement, con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
Response.Redirect("IncidentForum.aspx");
}
public static IEnumerable GetCustomerIncidents(int CustomerID)
{
SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString());
string sel = "SELECT IncidentID, ProductCode, "
+ "DateOpened, DateClosed, Title, Description "
+ "FROM Incidents "
+ "WHERE CustomerID = @CustomerID "
+ "AND DateClosed IS NULL";
SqlCommand cmd = new SqlCommand(sel, con);
cmd.Parameters.AddWithValue("CustomerID", CustomerID);
con.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return(dr);
}
public static IEnumerable GetCustomersWithIncidents()
{
SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString());
string sel = "SELECT CustomerID, Name "
+ "FROM Customers "
+ "WHERE CustomerID IN "
+ "(SELECT DISTINCT CustomerID "
+ "FROM Incidents "
+ "WHERE DateClosed IS NULL) "
+ "ORDER BY Name";
SqlCommand cmd = new SqlCommand(sel, con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return(dr);
}
protected void Button1_Click(object sender, EventArgs e)
{
Boolean taken = true;
string address = TextBox1.Text.ToString();
string email = TextBox2.Text.ToString();
string password = TextBox3.Text.ToString();
string name = TextBox9.Text.ToString() + " " + TextBox10.Text.ToString();
string state = TextBox5.Text.ToString();
string city = TextBox6.Text.ToString();
string zipcode = TextBox7.Text.ToString();
string phonenum = TextBox8.Text.ToString();
string role = "customer";
Random rnd = new Random();
int customerID = rnd.Next(1000, 10000);
SqlConnection con1 = new SqlConnection(TechSupportDB.GetConnectionString());
con1.Open();
string checker = "SELECT * FROM Customers WHERE Email = " + "'" + email + "'";
SqlCommand comand1 = new SqlCommand(checker, con1);
SqlDataReader reader1 = comand1.ExecuteReader(CommandBehavior.CloseConnection);
if (reader1.HasRows)
{
con1.Close();
errLabel.Text = "Email Already in Use";
}
else
{
string statement = "INSERT INTO Customers (Name, Address, City, State, ZipCode, Phone, Email, Password, Role) VALUES (" + "'" + name + "'" + ", " + "'" + address + "'" + ", " + "'" + city + "'" + ", " + "'" + state + "'" + ", " + "'" + zipcode + "'" + ", " + "'" + phonenum + "'" + ", " + "'" + email + "'" + ", " + "'" + password + "'" + ", " + "'" + role + "'" + ")";
SqlConnection con2 = new SqlConnection(TechSupportDB.GetConnectionString());
SqlCommand comand2 = new SqlCommand(statement, con2);
con2.Open();
SqlDataReader reader2 = comand2.ExecuteReader(CommandBehavior.CloseConnection);
con2.Close();
Session["Role"] = "customer";
Session["Name"] = name;
Session["Eamil"] = email;
string AccountSid = "ACb5e12563bfb0848030ce8b4218a4f468";
string AuthToken = "09396ddfe43280a9c661820d7abdfac8";
TwilioRestClient twilio = new TwilioRestClient(AccountSid, AuthToken);
twilio.SendMessage("17085057088", phonenum, "Welcome to SportPro " + name + " Your Email is: " + email + " and Password is: " + password + " You will need these for future Logins!", "");
Response.Redirect("ContactUs.aspx");
}
return;
}
public static IEnumerable GetOpenTechIncidents(int techID)
{
SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString());
string sel = "SELECT DateOpened, ProductCode, Name " +
"FROM Incidents JOIN Customers " +
"ON Incidents.CustomerID = Customers.CustomerID " +
"WHERE DateClosed IS NULL " +
"AND TechID = @TechID " +
"ORDER BY DateOpened";
SqlCommand cmd = new SqlCommand(sel, con);
cmd.Parameters.AddWithValue("TechID", techID);
con.Open();
SqlDataReader rdr =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
return(rdr);
}
public static IEnumerable GetOpenTechIncidents(int techID)
{
SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString());
string sel = "SELECT Customers.Name,Incidents.ProductCode,Incidents.DateOpened,Incidents.TechID " +
" FROM Customers INNER JOIN " +
" Incidents ON Customers.CustomerID = Incidents.CustomerID " +
" WHERE(Incidents.TechID = @TechID)" +
" ORDER BY Incidents.DateOpened DESC";
SqlCommand cmd = new SqlCommand(sel, con);
cmd.Parameters.AddWithValue("TechID", techID);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return(rdr);
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString());
string name = DropDownList1.Text.ToString();
string sel = "UPDATE customers " +
"SET Role = 'admin' " +
"WHERE name = " + "'" + name + "'";
SqlCommand cmd = new SqlCommand(sel, con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dr.Read();
DropDownList1.DataBind();
Label1.Text = "Success!";
return;
}
protected void btnGetIncidents_Click(object sender, EventArgs e)
{
int customerID = Convert.ToInt32(txtCustomerID.Text.ToString());
SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString());
string selectStatement = "SELECT * "
+ "FROM Incidents "
+ "WHERE DateClosed IS NOT NULL AND CustomerID = @CustomerID";
SqlCommand command = new SqlCommand(selectStatement, con);
command.Parameters.AddWithValue("CustomerID", customerID);
con.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
if (reader.HasRows)
{
lstIncidents.Items.Add(new ListItem("--Select an incident--", "None"));
while (reader.Read())
{
Partial_Incident incident = new Partial_Incident();
incident.IncidentID = Convert.ToInt32(reader["IncidentID"]);
incident.ProductCode = reader["ProductCode"].ToString();
incident.DateClosed = Convert.ToDateTime(reader["DateClosed"]);
incident.Title = reader["Title"].ToString();
lstIncidents.Items.Add(new ListItem(
incident.CustomerIncidentDisplay(), incident.IncidentID.ToString()));
}
lstIncidents.SelectedIndex = 0;
lblNoIncidents.Text = "";
this.EnableControls(true);
lstIncidents.Focus();
}
else
{
lblNoIncidents.Text = "There are no incidents for that customer.";
this.EnableControls(false);
}
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString());
string name = DropDownList1.Text.ToString();
string sel = "SELECT Phone " +
"FROM Customers " +
"WHERE Name = " + "'" + name + "'";
SqlCommand cmd = new SqlCommand(sel, con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dr.Read();
string number = dr["Phone"].ToString();
string AccountSid = "ACb5e12563bfb0848030ce8b4218a4f468";
string AuthToken = "09396ddfe43280a9c661820d7abdfac8";
string message = TextBox1.Text.ToString();
TwilioRestClient twilio = new TwilioRestClient(AccountSid, AuthToken);
twilio.SendMessage("17085057088", number, message, "");
TextBox1.Text = String.Empty;
return;
}
protected void dtlCustomers_Deleted(object sender, DetailsViewDeletedEventArgs e)
{
string ID = (dtlCustomers.Rows[0].Cells[1].Text.ToString());
int ID1 = Int32.Parse(ID);
SqlConnection con = new SqlConnection(TechSupportDB.GetConnectionString());
string sel = "DELETE "
+ "FROM Customers "
+ "WHERE CustomerID = '" + ID1 + "'";
SqlCommand cmd = new SqlCommand(sel, con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
con.Close();
if (e.Exception != null)
{
lblErrorMessage.Text = "A database error has occurred. ";
e.ExceptionHandled = true;
}
else
{
maintainCustomers.DataBind();
}
}
