/// <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); } }