private void frmCreateIncident_Load(object sender, EventArgs e)
{
TechSupportDB.SetConnectionString(ConfigurationManager.
ConnectionStrings["SportsPro.Properties.Settings.TechSupportConnectionString"].
ConnectionString);
PopulateComboBoxes();
}
private void frmOpenIncidents_Load(object sender, EventArgs e)
{
TechSupportDB.SetConnectionString(ConfigurationManager.
ConnectionStrings["SportsPro.Properties.Settings.TechSupportConnectionString"].
ConnectionString);
//TechSupportDB.SetConnectionString("Data Source=localhost;Initial Catalog=TechSupportx;Integrated Security=True");
PopulateIncidentsList();
}
private void frmTechnicianIncidents_Load(object sender, EventArgs e)
{
TechSupportDB.SetConnectionString(ConfigurationManager.
ConnectionStrings["SportsPro.Properties.Settings.TechSupportConnectionString"].
ConnectionString);
technicianList = TechnicianDB.GetTechnicianList();
nameComboBox.DataSource = technicianList;
nameComboBox.SelectedIndex = 0;
}
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 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 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 List <Incident> GetOpenIncidents()
{
SqlCommand aCommand = new SqlCommand();
aCommand.Connection = TechSupportDB.GetConnection();
string selectStatement = "Select ProductCode, DateOpened,Customers.Name,Technicians.Name AS Technician,Title FROM Incidents left outer join Technicians on Incidents.TechID = Technicians.TechID inner JOIN Customers on Incidents.CustomerID = Customers.CustomerID WHERE DateClosed is NULL";
aCommand.CommandText = selectStatement;
aCommand.CommandType = CommandType.Text;
List <Incident> listOfIncidents = new List <Incident>();
try
{
aCommand.Connection.Open();
SqlDataReader aReader = aCommand.ExecuteReader
(CommandBehavior.CloseConnection);
while (aReader.Read())
{
Incident anIncident = new Incident();
anIncident.ProductCode = aReader["ProductCode"].ToString();
anIncident.DateOpened = (DateTime)aReader["DateOpened"];
anIncident.CustomerName = aReader["Technician"].ToString();
anIncident.TechName = aReader["Technician"].ToString();
anIncident.Title = aReader["Title"].ToString();
listOfIncidents.Add(anIncident);
}
return(listOfIncidents);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
aCommand.Connection.Close();
}
}
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;
}
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;
}
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);
}
public bool ProductRegistration(int customerID, string productCode)
{
Registration aRegistration = new Registration();
SqlCommand aCommand = new SqlCommand();
aCommand.Connection = TechSupportDB.GetConnection();
aCommand.CommandText = "Select CustomerID, ProductCode FROM Registrations WHERE CustomerID = @CustomerID AND ProductCode = @ProductCode";
aCommand.CommandType = CommandType.Text;
aCommand.Parameters.AddWithValue("@CustomerID", customerID);
aCommand.Parameters.AddWithValue("@ProductCode", productCode);
try
{
aCommand.Connection.Open();
SqlDataReader aReader = aCommand.ExecuteReader(CommandBehavior.CloseConnection);
if (aReader.Read())
{
aRegistration.CustomerID = (int)aReader["CustomerID"];
aRegistration.ProductCode = aReader["ProductCode"].ToString();
return(true);
}
else
{
aRegistration = null;
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
aCommand.Connection.Close();
}
}
public Product GetProductName(String productCode)
{
Product aProduct = new Product();
SqlCommand aCommand = new SqlCommand();
aCommand.Connection = TechSupportDB.GetConnection();
aCommand.CommandText = " select Name from Products Where ProductCode = @ProductCode ";
aCommand.CommandType = CommandType.Text;
aCommand.Parameters.AddWithValue("@ProductCode", productCode);
try
{
aCommand.Connection.Open();
SqlDataReader aReader = aCommand.ExecuteReader(CommandBehavior.CloseConnection);
if (aReader.Read())
{
aProduct.Name = aReader["Name"].ToString();
aProduct.ProductCode = aReader["ProductCode"].ToString();
}
else
{
aProduct = null;
}
aReader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
aCommand.Connection.Close();
}
return(aProduct);
}
public Customer GetCustomerName(int customerID)
{
Customer aCustomer = new Customer();
SqlCommand aCommand = new SqlCommand();
aCommand.Connection = TechSupportDB.GetConnection();
aCommand.CommandText = " select Name from Customers Where CustomerID = @CustomerID ";
aCommand.CommandType = CommandType.Text;
aCommand.Parameters.AddWithValue("@CustomerID", customerID);
try
{
aCommand.Connection.Open();
SqlDataReader aReader = aCommand.ExecuteReader(CommandBehavior.CloseConnection);
if (aReader.Read())
{
aCustomer.Name = aReader["Name"].ToString();
}
else
{
aCustomer = null;
}
aReader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
aCommand.Connection.Close();
}
return(aCustomer);
}
public List <Customer> GetCustomerList()
{
List <Customer> listOfCustomers = new List <Customer>();
SqlCommand aCommand = new SqlCommand();
aCommand.Connection = TechSupportDB.GetConnection();
aCommand.CommandText = "SELECT CustomerID, Name FROM Customers";
aCommand.CommandType = CommandType.Text;
try
{
aCommand.Connection.Open();
SqlDataReader aReader = aCommand.ExecuteReader
(CommandBehavior.CloseConnection);
while (aReader.Read())
{
Customer aCustomer = new Customer();
aCustomer.CustomerID = (int)aReader["CustomerID"];
aCustomer.Name = aReader["Name"].ToString();
listOfCustomers.Add(aCustomer);
}
aReader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
aCommand.Connection.Close();
}
return(listOfCustomers);
}
public List <Product> GetProductsList()
{
List <Product> listOfProducts = new List <Product>();
SqlCommand aCommand = new SqlCommand();
aCommand.Connection = TechSupportDB.GetConnection();
aCommand.CommandText = "SELECT Name, ProductCode FROM Products";
aCommand.CommandType = CommandType.Text;
try
{
aCommand.Connection.Open();
SqlDataReader aReader = aCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (aReader.Read())
{
Product aProduct = new Product();
aProduct.Name = aReader["Name"].ToString();
//aProduct.Name = aReader["ProductCode"].ToString();\
aProduct.ProductCode = aReader["ProductCode"].ToString();
listOfProducts.Add(aProduct);
}
aReader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
aCommand.Connection.Close();
}
return(listOfProducts);
}
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();
}
}
