/*
* This will return a list of scenarios created by the student who's ID is stored in the Singleton
*/
public static Scenarios getStudentScenariosToEdit(SqlConnection connection)
{
var scenarios = new Scenarios {
ScenarioList = new List <Scenario>()
};
try
{
var scenariosToEditCommand = new SqlCommand(null, connection)
{
CommandText = "SELECT * " +
"FROM SCENARIOS " +
"WHERE SCENARIO_ID != 0 AND SCENARIO_APPROVED = 0 AND SCENARIO_MADE_BY = @id"
};
var studentIDParam = new SqlParameter("@id", SqlDbType.NVarChar, 128)
{
Value = (string)HttpContext.Current.Session["userId"]
};
scenariosToEditCommand.Parameters.Add(studentIDParam);
connection.Close(); connection.Open();
scenariosToEditCommand.Prepare();
using (var reader = scenariosToEditCommand.ExecuteReader())
{
while (reader.Read())
{
var scenario = new Scenario();
scenario.scenarioName = $"{reader["SCENARIO_NAME"]}";
scenario.scenarioID = Convert.ToInt32($"{reader["SCENARIO_ID"]}");
scenario.description = $"{reader["SCENARIO_DESCRIPTION"]}";
scenario.isActive = Convert.ToBoolean($"{reader["SCENARIO_IS_ACTIVE"]}".ToLower());
scenario.cellFunction = $"{reader["SCENARIO_CELL_FUNCTION"]}";
scenario.cellShapeAndFeatures = $"{reader["SCENARIO_CELL_SHAPE_AND_FEATURES"]}";
scenario.cellLifespan = $"{reader["SCENARIO_CELL_LIFESPAN"]}";
scenario.cellNutrition = $"{reader["SCENARIO_CELL_NUTRITION"]}";
if ($"{reader["SCENARIO_FIRST_QUESTION"]}".Equals(""))
{
scenario.currentQuestion = 0;
}
else
{
scenario.currentQuestion = Convert.ToInt32($"{reader["SCENARIO_FIRST_QUESTION"]}");
}
scenarios.ScenarioList.Add(scenario);
}
}
}
catch (Exception ex)
{
Singleton.errorCode = "USR05";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
return(scenarios);
}
/*
* When a user registers, split the email into strings for their first name and last name. Concatenate
* these and store them in the users table as the UserName.
*/
public static void setNewUserName(string email, SqlConnection connection)
{
try
{
connection.Close(); connection.Open();
string namePortion = email.Split(new[] { '@' })[0];
string[] nameSplit = namePortion.Split(new[] { '.' });
string firstName = nameSplit[0];
string lastName = "";
if (nameSplit.Length == 2)
{
lastName = nameSplit[1];
}
string fullName = firstName + " " + lastName;
fullName = fullName.Trim();
HttpContext.Current.Session["userName"] = fullName;
//we need to ensure that we're not writing in null values to the database.
var commandAddName = new SqlCommand(null, connection)
{
CommandText = "INSERT INTO STUDENTS " +
"(STUDENT_ID, STUDENT_NAME) " +
"VALUES (@studentId, @studentName);"
};
if (String.IsNullOrEmpty((string)HttpContext.Current.Session["userId"]))
{
//This seems to be causing issues with some foreign keys in the database.
//Figure out why it's attempting to write a NULL.
var studentId = new SqlParameter("@studentId", SqlDbType.NVarChar, 128)
{
Value = " ***ERROR*** "
};
commandAddName.Parameters.Add(studentId);
}
else
{
var studentId = new SqlParameter("@studentId", SqlDbType.NVarChar, 128)
{
Value = (string)HttpContext.Current.Session["userId"]
};
commandAddName.Parameters.Add(studentId);
}
if (String.IsNullOrEmpty((string)HttpContext.Current.Session["userName"]))
{
var studentName = new SqlParameter("@studentName", SqlDbType.VarChar, 40)
{
Value = " ***ERROR*** "
};
commandAddName.Parameters.Add(studentName);
}
else
{
var studentName = new SqlParameter("@studentName", SqlDbType.VarChar, 40)
{
Value = fullName
};
commandAddName.Parameters.Add(studentName);
}
commandAddName.Prepare();
commandAddName.ExecuteNonQuery();
}
catch (Exception ex)
{
Singleton.errorCode = "USR02";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
}
/*
* This function is called when a user logs in. It sets the username and ID in the Singleton.
*/
public static void setUserNameId(string email, SqlConnection connection)
{
try
{
string userIdString;
string userNameString;
var setUserNameCommand = new SqlCommand(null, connection)
{
CommandText = "SELECT AspNetUsers.Id, STUDENTS.STUDENT_NAME " +
"FROM AspNetUsers JOIN STUDENTS ON AspNetUsers.Id = STUDENTS.STUDENT_ID " +
"WHERE Email = @email"
};
var paramEmail = new SqlParameter("@email", SqlDbType.VarChar, 40)
{
Value = email
};
setUserNameCommand.Parameters.Add(paramEmail);
connection.Close(); connection.Open();
setUserNameCommand.Prepare();
bool found = false;
using (var reader = setUserNameCommand.ExecuteReader())
{
if (reader.Read())
{
found = true;
userIdString = $"{reader["Id"]}";
userNameString = $"{reader["STUDENT_NAME"]}";
HttpContext.Current.Session["userId"] = userIdString;
HttpContext.Current.Session["userName"] = userNameString;
}
}
if (!found)
{
var command1 = new SqlCommand(null, connection)
{
CommandText = "SELECT AspNetUsers.Id " +
"FROM AspNetUsers " +
"WHERE Email = @email"
};
var param2 = new SqlParameter("@email", SqlDbType.VarChar, 40)
{
Value = email
};
command1.Parameters.Add(param2);
command1.Prepare();
using (var reader = command1.ExecuteReader())
{
if (reader.Read())
{
HttpContext.Current.Session["userId"] = $"{reader["Id"]}";
}
}
setNewUserName(email, connection);
}
}
catch (Exception ex)
{
Singleton.errorCode = "USR01";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
}