public List <Order> RetrieveOrderByEmail(string email)
{
List <Order> orders = new List <Order>();
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_select_order_by_email");
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@OrderEmail", SqlDbType.NVarChar, 250);
cmd.Parameters["@OrderEmail"].Value = email;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var order = new Order();
order.OrderStatus = reader.GetString(0);
order.OrderFirstName = reader.GetString(1);
order.OrderLastName = reader.GetString(2);
order.OrderEmail = email;
orders.Add(order);
}
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(orders);
}
public User SelectUserByID(int id)
{
User user = new User();
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_select_employee_by_id");
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int);
cmd.Parameters["@EmployeeID"].Value = id;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
user.EmployeeID = reader.GetInt32(0);
user.FirstName = reader.GetString(1);
user.LastName = reader.GetString(2);
user.PhoneNumber = reader.GetString(3);
user.Email = reader.GetString(4);
}
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(user);
}
public int CreateNewStudentIEP(Students student, SLPUser slp)
{
int result = 0;
var conn = DBConnection.GetConnection();
string cmdText = @"sp_insert_new_iep";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@IEPDate", SqlDbType.Date);
//cmd.Parameters.Add("@StudentID", SqlDbType.Int);
//cmd.Parameters.["@StudentID"].Value = student.StudentId;
cmd.Parameters.AddWithValue("@StudentID", student.StudentId);
cmd.Parameters.AddWithValue("@SLPID", slp.SLPID);
cmd.Parameters.AddWithValue("@IEPType", student.IEPType);
//cmd.Parameters.AddWithValue("@IEPDate", student.IEPdate);
cmd.Parameters["@IEPDate"].Value = student.IEPdate;
cmd.Parameters.AddWithValue("@IEPLeaderFirstName", student.IEPLeaderFirstName);
cmd.Parameters.AddWithValue("@IEPLeaderLastName", student.IEPLeaderLastName);
cmd.Parameters.AddWithValue("@IEPNotes", student.IEPNotes);
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(result);
}
public int updateEmployee(User oldUser, User newUser)
{
int rows = 0;
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_update_employee", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmployeeID", oldUser.EmployeeID);
cmd.Parameters.AddWithValue("@NewFirstName", newUser.FirstName);
cmd.Parameters.AddWithValue("@NewLastName", newUser.LastName);
cmd.Parameters.AddWithValue("@NewPhoneNumber", newUser.PhoneNumber);
cmd.Parameters.AddWithValue("@NewEmail", newUser.Email);
cmd.Parameters.AddWithValue("@OldFirstName", oldUser.FirstName);
cmd.Parameters.AddWithValue("@OldLastName", oldUser.LastName);
cmd.Parameters.AddWithValue("@OldPhoneNumber", oldUser.PhoneNumber);
cmd.Parameters.AddWithValue("@OldEmail", oldUser.Email);
try
{
conn.Open();
rows = cmd.ExecuteNonQuery();
if (rows == 0)
{
throw new ApplicationException("Record not found");
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(rows);
}
public int UpdatePasswordHashTeacher(string email, string oldPassword, string newPassword)
{
int result = 0;
// get a connect
var conn = DBConnection.GetConnection();
//command text
string cmdText = "sp_update_passwordhash_teacher";
// command
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 250);
cmd.Parameters.Add("@NewPasswordHash", SqlDbType.NVarChar, 100);
cmd.Parameters.Add("@OldPasswordHash", SqlDbType.NVarChar, 100);
cmd.Parameters["@Email"].Value = email;
cmd.Parameters["@NewPasswordHash"].Value = newPassword;
cmd.Parameters["@OldPasswordhash"].Value = oldPassword;
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(result);
}
public List <TeacherUser> RetrieveTeachers()
{
List <TeacherUser> teachers = new List <TeacherUser>();
var conn = DBConnection.GetConnection();
string cmdText = @"sp_retrieve_teacher_info";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
TeacherUser teacher = new TeacherUser();
teacher.TeacherID = reader.GetString(0);
teacher.FirstName = reader.GetString(1);
teacher.LastName = reader.GetString(2);
teacher.Email = reader.GetString(3);
teachers.Add(teacher);
}
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(teachers);
}
public List <SLPUser> RetrieveSLPs()
{
List <SLPUser> slps = new List <SLPUser>();
var conn = DBConnection.GetConnection();
string cmdText = @"sp_retrieve_slp_info";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
SLPUser slp = new SLPUser();
slp.SLPID = reader.GetString(0);
slp.FirstName = reader.GetString(1);
slp.LastName = reader.GetString(2);
slp.Email = reader.GetString(3);
slps.Add(slp);
}
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(slps);
}
public List <string> SelectRolesByEmployeeID(int employeeID)
{
List <string> roles = new List <string>();
// connection
var conn = DBConnection.GetConnection();
// command objects
var cmd = new SqlCommand("sp_select_roles_by_userid");
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
// parameters
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int);
cmd.Parameters["@EmployeeID"].Value = employeeID;
try
{
// open connection
conn.Open();
// execute the first command
var reader = cmd.ExecuteReader();
while (reader.Read())
{
string role = reader.GetString(0);
roles.Add(role);
}
}
catch (Exception ex)
{
throw ex;
}
return(roles);
}
public int insertOrder(Order order, User user)
{
int OrderID = 0;
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_insert_order", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int);
cmd.Parameters.Add("@OrderFirstName", SqlDbType.NVarChar);
cmd.Parameters.Add("@OrderLastName", SqlDbType.NVarChar);
cmd.Parameters.Add("@OrderEmail", SqlDbType.NVarChar);
int employeeID = user.EmployeeID;
cmd.Parameters["@EmployeeID"].Value = employeeID;
cmd.Parameters["@OrderFirstName"].Value = order.OrderFirstName;
cmd.Parameters["@OrderLastName"].Value = order.OrderLastName;
cmd.Parameters["@OrderEmail"].Value = order.OrderEmail;
try
{
conn.Open();
order.OrderID = Convert.ToInt32(cmd.ExecuteScalar());
OrderID = order.OrderID;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(OrderID);
}
public int CreateBaseStandardItem()
{
int standardItemID = 0;
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_insert_standarditem", conn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
standardItemID = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(standardItemID);
}
public bool InsertAddOn(int orderID, int standardItemID, int ingredientID)
{
bool result = false;
// connection
var conn = DBConnection.GetConnection();
// command
var cmd = new SqlCommand("sp_insert_addon");
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
// parameters
cmd.Parameters.Add("@OrderID", SqlDbType.Int);
cmd.Parameters.Add("@StandardItemID", SqlDbType.Int);
cmd.Parameters.Add("@IngredientID", SqlDbType.Int);
// values
cmd.Parameters["@OrderID"].Value = orderID;
cmd.Parameters["@StandardItemID"].Value = standardItemID;
cmd.Parameters["@IngredientID"].Value = ingredientID;
// execute
try
{
conn.Open();
int rowsAffected = cmd.ExecuteNonQuery();
result = (rowsAffected == 1);
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public bool UpdatePasswordHash(int userID, string oldPassHash, string newPassHash)
{
bool result = false;
// connection
var conn = DBConnection.GetConnection();
// command
var cmd = new SqlCommand("sp_update_password");
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
// parameters
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int);
cmd.Parameters.Add("@OldPasswordHash", SqlDbType.NVarChar, 100);
cmd.Parameters.Add("@NewPasswordHash", SqlDbType.NVarChar, 100);
// values
cmd.Parameters["@EmployeeID"].Value = userID;
cmd.Parameters["@OldPasswordHash"].Value = oldPassHash;
cmd.Parameters["@NewPasswordHash"].Value = newPassHash;
// execute
try
{
conn.Open();
int rowsAffected = cmd.ExecuteNonQuery();
result = (rowsAffected == 1);
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public User SelectUserByEmail(string email)
{
User user = null;
// connection?
var conn = DBConnection.GetConnection();
// commands?
var cmd1 = new SqlCommand("sp_select_user_by_email", conn);
var cmd2 = new SqlCommand("sp_select_roles_by_userid", conn);
// command type?
cmd1.CommandType = CommandType.StoredProcedure;
cmd2.CommandType = CommandType.StoredProcedure;
// parameters?
cmd1.Parameters.Add("@Email", SqlDbType.NVarChar, 250);
cmd2.Parameters.Add("@EmployeeID", SqlDbType.Int);
// values (need to wait for cmd2's parameter)
cmd1.Parameters["@Email"].Value = email;
try
{
// open the connection
conn.Open();
var reader1 = cmd1.ExecuteReader();
user = new User();
user.Email = email;
if (reader1.Read())
{
user.EmployeeID = reader1.GetInt32(0);
user.FirstName = reader1.GetString(1);
user.LastName = reader1.GetString(2);
user.PhoneNumber = reader1.GetString(3);
}
else
{
throw new ApplicationException("User not found.");
}
reader1.Close();
// now cmd2 needs a parameter value
cmd2.Parameters["@EmployeeID"].Value = user.EmployeeID;
var reader2 = cmd2.ExecuteReader();
List <string> roles = new List <string>();
while (reader2.Read())
{
roles.Add(reader2.GetString(0));
}
reader2.Close();
user.Roles = roles;
}
catch (Exception up)
{
throw up;
}
finally
{
conn.Close();
}
return(user);
}
public Order RetrieveOrderById(int id)
{
var order = new Order();
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_get_all_active_orders");
//TODO change this
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.Add("@OrderID", SqlDbType.Int);
//cmd.Parameters["@OrderID"].Value = id;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
int tempStandardItem = 0;
string tempAddOn = null;
List <AddOn> addOns = new List <AddOn>();
List <StandardItem> standardItemList = new List <StandardItem>();
var addOn = new AddOn();
var standardItem = new StandardItem();
if (reader.HasRows)
{
while (reader.Read())
{
order.OrderID = reader.GetInt32(0);
if (reader.GetInt32(1) != tempStandardItem)//Means that it hit a new standard item
{
standardItem.StandardItemID = reader.GetInt32(1);
tempStandardItem = reader.GetInt32(1);
}
else// Means that it is on the same standard item and the add on must be added
{
AddOn newAddOn = new AddOn();
newAddOn.Name = reader.GetString(2);
addOns.Add(newAddOn);
standardItemList.Add(standardItem);
}
}
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(order);
}