/// <summary>
/// Gets the an LPHS class by class id.
/// </summary>
/// <param name="classId">The class id.</param>
/// <returns></returns>
public static LPHSClass Get(int classId)
{
using (new LogScope("LPHSClass.Get"))
{
try
{
const string sql = @"SELECT CLASS_ID, CLASS_NAME, CLASS_DESCRIPTION, SCHOOL_ID, TEACHER_USER_ID, ACTIVE
FROM LPHSDB.CLASS
WHERE CLASS_ID = :classId";
var lphsClass = new LPHSClass();
Database.Oracle.Execute("LPHS", (conn) =>
{
using (OracleCommand command = new OracleCommand(sql, conn))
{
command.Parameters.Add("classId", classId);
using (var reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
if (reader.HasRows)
{
while (reader.Read())
{
lphsClass = ReadClass(reader);
}
}
}
}
});
return lphsClass;
}
catch (Exception ex)
{
Log.Write(ex, Log.Mask.Failure);
throw;
}
}
}
/// <summary>
/// Fills and returns an LPHS class from an Oracle datareader.
/// </summary>
/// <param name="reader">The reader.</param>
/// <returns></returns>
private static LPHSClass ReadClass(OracleDataReader reader)
{
LPHSClass lphsClass = new LPHSClass();
lphsClass.ID = (int)OracleDatabase.GetDecimal(reader, 0, 0);
lphsClass.Name = OracleDatabase.GetString(reader, 1, string.Empty);
lphsClass.Description = OracleDatabase.GetString(reader, 2, string.Empty);
lphsClass.SchoolID = (int?)OracleDatabase.GetDecimalNullable(reader, 3, null);
lphsClass.TeacherID = (int?)OracleDatabase.GetDecimalNullable(reader, 4, null);
//lphsClass.Active = OracleDatabase.GetBoolean(reader, 5, false);
lphsClass.Active = OracleDatabase.GetBooleanFromString(reader, 5);
return lphsClass;
}
public static void Update(LPHSClass lphsClass)
{
using (new LogScope("LPHSClass.Update"))
{
try
{
Database.Oracle.Execute("LPHS", (conn) =>
{
StringBuilder oracleQuery = new StringBuilder();
oracleQuery.Append(" UPDATE LPHSDB.CLASS");
oracleQuery.Append(" SET CLASS_NAME = :className");
oracleQuery.Append(" ,CLASS_DESCRIPTION = :classDescription");
oracleQuery.Append(" ,SCHOOL_ID = :schoolId");
oracleQuery.Append(" ,TEACHER_USER_ID = :teacherUserId");
oracleQuery.Append(" ,ACTIVE = :active");
oracleQuery.Append(" WHERE (CLASS_ID = :classId)");
using (OracleCommand command = new OracleCommand(oracleQuery.ToString(), conn))
{
command.BindByName = true;
command.Parameters.Add("className", OracleDbType.Varchar2).Value = lphsClass.Name;
command.Parameters.Add("classDescription", OracleDbType.Varchar2).Value = lphsClass.Description;
command.Parameters.Add("schoolId", OracleDbType.Int32).Value = lphsClass.SchoolID;
command.Parameters.Add("teacherUserId", OracleDbType.Int32).Value = lphsClass.TeacherID;
command.Parameters.Add("active", OracleDbType.Varchar2).Value = OracleDatabase.GetBooleanStringFromBoolean(lphsClass.Active);
command.Parameters.Add("classId", OracleDbType.Int32).Value = lphsClass.ID;
command.ExecuteNonQuery();
}
});
}
catch (Exception ex)
{
Log.Write(ex, Log.Mask.Failure);
throw;
}
}
}
/// <summary>
/// Creates a new school
/// </summary>
/// <param name="school">LPHS School to save</param>
/// <returns></returns>
/// <author>David Williams</author>
/// <date>11/15/2012</date>
public static int Create(LPHSClass lphsClass)
{
int classId = 0;
Database.Oracle.Execute("LPHS", (conn) =>
{
StringBuilder oracleCommand = new StringBuilder();
oracleCommand.Append(" INSERT INTO LPHSDB.CLASS (CLASS_ID, CLASS_NAME, CLASS_DESCRIPTION, SCHOOL_ID, TEACHER_USER_ID, ACTIVE) ");
oracleCommand.Append(" VALUES (LPHSDB.SEQ_CLASS_ID.NEXTVAL, :Name, :Description, :SchoolId, :TeacherUserId, :Active)");
oracleCommand.Append(" RETURNING CLASS_ID INTO :classkey");
using (OracleCommand command = new OracleCommand(oracleCommand.ToString(), conn))
{
try
{
OracleParameter outParam = new OracleParameter("classkey", OracleDbType.Decimal, System.Data.ParameterDirection.Output);
command.BindByName = true;
command.Parameters.Add("Name", OracleDbType.Varchar2).Value = lphsClass.Name;
command.Parameters.Add("Description", OracleDbType.Varchar2).Value = lphsClass.Description;
command.Parameters.Add("SchoolId", OracleDbType.Int32).Value = lphsClass.SchoolID;
command.Parameters.Add("TeacherUserId", OracleDbType.Int32).Value = lphsClass.TeacherID;
command.Parameters.Add("Active", OracleDbType.Varchar2).Value = OracleDatabase.GetBooleanStringFromBoolean(lphsClass.Active);
command.Parameters.Add(outParam);
foreach (OracleParameter p in command.Parameters)
if (p.Value == null)
p.Value = DBNull.Value;
command.ExecuteNonQuery();
classId = (int)(Oracle.DataAccess.Types.OracleDecimal)outParam.Value;
}
catch (Exception ex)
{
Log.Write(ex, Log.Mask.Failure);
throw;
}
}
});
return classId;
}
public ActionResult CreateClass(LPHSClass lphsClass, string source)
{
try
{
// ToDo: Save new class to DB
int classId = LPHSClass.Create(lphsClass);
if (!string.IsNullOrWhiteSpace(source) && source == "s")
{
return RedirectToAction("Index", "School", new { id = lphsClass.SchoolID });
}
return RedirectToAction("Index", new { classId = classId });
}
catch (Exception ex)
{
Log.Write("ClassController", ex, Log.Mask.Failure);
ViewBag.Source = source;
var user = SessionHelper.GetUser();
var schools = new List<LPHSSchool>();
if (user.UserType == LPHSUserTypes.Teacher)
schools.Add(LPHSSchool.Get(user.SchoolID.Value));
else
schools.AddRange(LPHSSchool.ListByCoordinator(user.ID, user.UserType));
ViewBag.Schools = schools;
var dict = new Dictionary<string, List<LPHSUser>>();
foreach (var t in LPHSUser.ListTeachersBySchool(0))
{
if (!dict.ContainsKey(t.SchoolID.Value.ToString()))
dict[t.SchoolID.Value.ToString()] = new List<LPHSUser>();
dict[t.SchoolID.Value.ToString()].Add(t);
}
ViewBag.Teachers = dict;
ViewBag.SelectedSchoolId = lphsClass.SchoolID;
return View(lphsClass);
}
}
/// <summary>
/// Returns view to edit new class
/// </summary>
/// <param name="id">School id</param>
/// <author>David Williams</author>
/// <date>11/26/2012</date>
public ActionResult CreateClass(int id, string source)
{
ViewBag.Source = source;
var user = SessionHelper.GetUser();
var schools = new List<LPHSSchool>();
if (user.UserType == LPHSUserTypes.Teacher)
schools.Add(LPHSSchool.Get(user.SchoolID.Value));
else
schools.AddRange(LPHSSchool.ListByCoordinator(user.ID, user.UserType));
ViewBag.Schools = schools;
var dict = new Dictionary<string, List<LPHSUser>>();
foreach (var t in LPHSUser.ListTeachersBySchool(0))
{
if (!dict.ContainsKey(t.SchoolID.Value.ToString()))
dict[t.SchoolID.Value.ToString()] = new List<LPHSUser>();
dict[t.SchoolID.Value.ToString()].Add(t);
}
ViewBag.Teachers = dict;
ViewBag.SelectedSchoolId = id;
LPHSClass lphsClass = new LPHSClass() { Active = true, SchoolID = id };
return View(lphsClass);
}
public ActionResult EditClass(LPHSClass lphsClass, string source)
{
try
{
bool inactivate = false;
LPHSClass updateClass = LPHSClass.Get(lphsClass.ID);
updateClass.Name = lphsClass.Name;
updateClass.Description = lphsClass.Description;
updateClass.SchoolID = lphsClass.SchoolID;
updateClass.TeacherID = lphsClass.TeacherID;
if (updateClass.Active && !lphsClass.Active)
inactivate = true;
updateClass.Active = lphsClass.Active;
LPHSClass.Update(updateClass);
if (inactivate)
LPHSClass.Inactivate(lphsClass.ID);
if (!string.IsNullOrWhiteSpace(source) && source == "s")
{
RedirectToAction("Index", "School", new { id = lphsClass.SchoolID });
}
return RedirectToAction("Index", new { id = lphsClass.ID });
}
catch (Exception ex)
{
Log.Write("ClassController", ex, Log.Mask.Failure);
ViewBag.Source = source;
var user = SessionHelper.GetUser();
var schools = new List<LPHSSchool>();
if (user.UserType == LPHSUserTypes.Teacher)
schools.Add(LPHSSchool.Get(user.SchoolID.Value));
else
schools.AddRange(LPHSSchool.ListByCoordinator(user.ID, user.UserType));
ViewBag.Schools = schools;
var dict = new Dictionary<string, List<LPHSUser>>();
foreach (var t in LPHSUser.ListTeachersBySchool(0))
{
if (!dict.ContainsKey(t.SchoolID.Value.ToString()))
dict[t.SchoolID.Value.ToString()] = new List<LPHSUser>();
dict[t.SchoolID.Value.ToString()].Add(t);
}
ViewBag.Teachers = dict;
ViewBag.SelectedSchoolId = lphsClass.SchoolID;
return View(lphsClass);
}
}
