public static LPHSUser GetUser(bool fromTicket = true)
{
try
{
LPHSUser user = null;
if (fromTicket)
{
user = new LPHSUser
{
ID = Convert.ToInt32(Security.CurrentIdentity.UserData["ID"]),
EmailAddress = Security.CurrentIdentity.UserData["Email"] as string,
DisplayName = Security.CurrentIdentity.UserData["DisplayName"] as string,
Active = Convert.ToBoolean(Security.CurrentIdentity.UserData["Active"]),
SchoolID = Convert.ToInt32(Security.CurrentIdentity.UserData["SchoolID"]),
UserType = (LPHSUserTypes)Enum.Parse(typeof(LPHSUserTypes), Security.CurrentIdentity.UserData["UserType"] as string),
Name = Security.CurrentIdentity.Name,
Sid = Security.CurrentIdentity.SID,
SumTotalID = Security.CurrentIdentity.UserData["SumTotalID"] as string
};
}
else
{
user = LPHSUser.GetBySid(Security.CurrentIdentity.SID);
}
if (user.SchoolID == -1)
user.SchoolID = null;
return user;
}
catch (Exception)
{
return new LPHSUser();
}
}
public ActionResult Edit(LPHSUser user)
{
using (new LogScope("/User/Edit"))
{
try
{
var u = LPHSUser.Get(user.ID);
//var u = LPHSUser.LookupInternal(user.Name);
u.DisplayName = user.DisplayName;
u.UserType = user.UserType;
u.Active = user.Active;
u.SchoolID = user.SchoolID;
//if (u == null)
//{
// ModelState.AddModelError("Name", "Employee not found.");
// ViewBag.Schools = LPHSSchool.List();
// return View(user);
//}
//user.Name = u.Name;
//user.DisplayName = u.DisplayName;
//user.EmailAddress = u.EmailAddress;
//user.Sid = u.Sid;
//if (user.UserType == LPHSUserTypes.Admin)
// user.SchoolID = null;
LPHSUser.Update(u);
return RedirectToAction("Index", new { msg = string.Format("Updated user '{0}'.", user.DisplayName) });
}
catch (Exception ex)
{
Log.Write(ex, Log.Mask.Failure);
ViewBag.Schools = LPHSSchool.List();
ModelState.AddModelError("", "Unable to update user information.");
return View(user);
}
}
}
/// <summary>
/// Reads the user.
/// </summary>
/// <param name="reader">The reader.</param>
/// <returns></returns>
internal static LPHSUser ReadUser(OracleDataReader reader)
{
var user = new LPHSUser()
{
ID = (int)OracleDatabase.GetDecimal(reader, 0, 0),
UserType = (LPHSUserTypes)(int)OracleDatabase.GetDecimal(reader, 1, 4),
Name = OracleDatabase.GetString(reader, 2, null),
DisplayName = OracleDatabase.GetString(reader, 3, null),
EmailAddress = OracleDatabase.GetString(reader, 4, null),
Active = OracleDatabase.GetBooleanFromString(reader, 5),
LastLoggedOn = reader.IsDBNull(6) ? null : new Nullable<DateTime>(reader.GetDateTime(6)),
SchoolID = (int?)OracleDatabase.GetDecimalNullable(reader, 7, null),
Sid = (string)OracleDatabase.GetString(reader, 8, null)
};
if (reader.VisibleFieldCount > 9)
user.SumTotalUserName = reader.IsDBNull(9) ? null : reader.GetString(9);
if (reader.VisibleFieldCount > 10)
user.SumTotalPassword = reader.IsDBNull(10) ? null : reader.GetString(10);
if (user.SumTotalPassword != null && user.SumTotalPassword.Length > 10)
user.SumTotalPassword = user.SumTotalPassword;
//if (user.SumTotalPassword != null)
// user.SumTotalPassword = Security.Decrypt(user.SumTotalPassword);
if (reader.VisibleFieldCount > 11)
user.SumTotalID = reader.IsDBNull(11) ? null : reader.GetString(11);
if (!user.SchoolID.HasValue)
{ user.SchoolID = -1; } //set default school
return user;
}
public static void Update(LPHSUser user)
{
const string sql = @"UPDATE LPHSDB.LPHS_USER
SET USER_TYPE_ID = :usertype
,USER_NAME = :username
,USER_DISPLAY_NAME = :displayname
,USER_EMAIL_ADDRESS = :emailaddress
,USER_STATUS = :userstatus
,USER_LAST_LOGGED_ON = :lastloggedon
,SCHOOL_ID = :schoolid
,USER_SID = :sid
,SUMTOTAL_USER_NAME = :sumtotalname
,SUMTOTAL_USER_PWD = :sumtotalpwd
,SUMTOTAL_USER_ID = :sumtotalid
WHERE USER_ID = :userid ";
using (new LogScope("LPHSUser.Update"))
{
Database.Oracle.Execute("LPHS", (conn) =>
{
using (OracleCommand command = new OracleCommand(sql, conn))
{
try
{
command.BindByName = true;
command.Parameters.Add("userid", OracleDbType.Int32).Value = user.ID;
command.Parameters.Add("usertype", OracleDbType.Int32).Value = (int)user.UserType;
command.Parameters.Add("username", OracleDbType.Varchar2).Value = user.Name;
command.Parameters.Add("displayname", OracleDbType.Varchar2).Value = user.DisplayName;
command.Parameters.Add("emailaddress", OracleDbType.Varchar2).Value = user.EmailAddress;
command.Parameters.Add("userstatus", OracleDbType.Varchar2).Value = OracleDatabase.GetBooleanStringFromBoolean(user.Active);
command.Parameters.Add("lastloggedon", OracleDbType.Date).Value = user.LastLoggedOn;
command.Parameters.Add("schoolid", OracleDbType.Int32).Value = (user.SchoolID.HasValue && user.SchoolID > 0)
? (object)user.SchoolID.Value : DBNull.Value;
command.Parameters.Add("sid", OracleDbType.Varchar2).Value = user.Sid;
command.Parameters.Add("sumtotalname", OracleDbType.Varchar2).Value = user.SumTotalUserName;
command.Parameters.Add("sumtotalpwd", OracleDbType.Varchar2).Value = user.SumTotalPassword;
command.Parameters.Add("sumtotalid", OracleDbType.Varchar2).Value = user.SumTotalID;
foreach (OracleParameter p in command.Parameters)
if (p.Value == null)
p.Value = DBNull.Value;
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Log.Write(ex, Log.Mask.Failure);
throw;
}
}
});
}
}
public static void CreateTeacherWebAuthAccount(LPHSUser user)
{
using (new LogScope("LPHSUser.CreateTeacherWebAuthAccount"))
{
try
{
SouthernIdentity ident = null;
if (Authenticator.Login(Settings.Instance.AccountCreationCredentials.Namespace,
Settings.Instance.AccountCreationCredentials.UserName,
Settings.Instance.AccountCreationCredentials.Password,
7,
ref ident) == 0)
{
var profiles = Util.CreateUsers(ident.SecurityToken, new List<User> {
new User
{
UserId = user.Name,
Email = user.Name + "@learningpowerhs.georgiapower.com",
FirstName = user.DisplayName,
LastName = "LPHS"
} });
Profile p = profiles[0];
if (p.Status != Status.Success)
throw new Exception("Failed: " + p.Status.ToString() + ": " + p.StatusMessage);
else
{
// var msg = string.Format()
// Log.Write("UserID: "+ p.Sid + " password: "******" userid:" + p.UserId, Log.Mask.Diagnostic);
user.Sid = p.Sid;
user.Password = p.Password;
}
}
else
{
throw new Exception("Not authorized.");
}
}
catch (Exception ex)
{
Log.Write(ex, Log.Mask.Failure);
throw;
}
}
}
public static void CreateSumTotalAccount(LPHSUser user)
{
SumTotal.SumTotal.CreateSumTotalAccount(user);
}
//public static void DeleteSumTotalAccount(string id)
//{
// //user.SumTotalID
// SumTotal.SumTotal.DeleteSumTotalAccount(id);
//}
#endregion
#region Create
public static LPHSUser Create(LPHSUser user)
{
const string sql = @"INSERT INTO LPHSDB.LPHS_USER
(user_id,USER_TYPE_ID,USER_NAME,USER_DISPLAY_NAME,USER_EMAIL_ADDRESS,USER_STATUS,USER_LAST_LOGGED_ON,SCHOOL_ID,USER_SID)
VALUES (LPHSDB.SEQ_USER_ID.NEXTVAL,:usertype,:username,:displayname,:emailaddress,:status,:lastloggedon,:schoolid,:sid)
RETURNING user_id INTO :lastID";
using (new LogScope("LPHSUser.Create"))
{
Database.Oracle.Execute("LPHS", (conn) =>
{
using (OracleCommand command = new OracleCommand(sql, conn))
{
try
{
//:role, :username, :displayname, :email, :status, :schoolid, :sid
command.BindByName = true;
command.Parameters.Add("usertype", OracleDbType.Int32).Value = (int)user.UserType;
command.Parameters.Add("username", OracleDbType.Varchar2).Value = user.Name;
command.Parameters.Add("displayname", OracleDbType.Varchar2).Value = user.DisplayName;
command.Parameters.Add("emailaddress", OracleDbType.Varchar2).Value = user.EmailAddress;
command.Parameters.Add("status", OracleDbType.Varchar2).Value = OracleDatabase.GetBooleanStringFromBoolean(user.Active);
command.Parameters.Add("lastloggedon", OracleDbType.Date).Value = user.LastLoggedOn;
command.Parameters.Add("schoolid", OracleDbType.Int32).Value = user.SchoolID.HasValue ? (object)user.SchoolID.Value : DBNull.Value;
command.Parameters.Add("sid", OracleDbType.Varchar2).Value = user.Sid;
command.Parameters.Add("lastId", OracleDbType.Decimal, System.Data.ParameterDirection.Output);
foreach (OracleParameter p in command.Parameters)
if (p.Value == null)
p.Value = DBNull.Value;
command.ExecuteNonQuery();
// I really hate doing this... another way?
user.ID = Convert.ToInt32(command.Parameters["lastId"].Value.ToString());
}
catch (Exception ex)
{
Log.Write(ex, Log.Mask.Failure);
throw;
}
}
});
}
return user;
}
// public static List<LPHSUser> ListWithUserActivitiesByClass(int classId)
// {
// const string oracleQuery = @"
// select U.USER_ID, U.USER_TYPE_ID, U.USER_NAME, U.USER_DISPLAY_NAME, U.USER_EMAIL_ADDRESS,
// U.USER_STATUS, U.USER_LAST_LOGGED_ON, U.SCHOOL_ID, U.USER_SID, U.SUMTOTAL_USER_NAME, U.SUMTOTAL_USER_PWD,
// c.class_id, c.class_name, a.activity_id, a.activity_name, uar.activity_status
// from lphsdb.CLASS c
// JOIN lphsdb.CLASS_STUDENTS cs on c.class_id = cs.class_id
// join lphsdb.LPHS_USER u on cs.student_id = u.user_id
// LEFT JOIN lphsdb.USER_ACTIVITY_RESULT UAR on u.user_id = uar.user_id
// LEFT join lphsdb.activity a on uar.activity_id = a.activity_id
// LEFT join lphsdb.module m on a.module_id = m.module_id
// where c.class_id = :classId
// order by u.user_display_name, m.module_name, a.activity_Name";
// var users = new List<LPHSUser>();
// Database.Oracle.Execute("LPHS", (conn) =>
// {
// using (OracleCommand command = new OracleCommand(oracleQuery, conn))
// {
// command.BindByName = true;
// command.Parameters.Add("classId", classId);
// using (var reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
// {
// LPHSUser lphsUser = null;
// while (reader.Read())
// {
// int userId = OracleDatabase.GetInt(reader, 0, 0);
// // Get this records activity (will be added to the user)
// // ToDo: replace 'status' with 'registered' when available
// LPHSActivity activity = new LPHSActivity()
// {
// ID = OracleDatabase.GetInt(reader, 13, 0),
// Name = OracleDatabase.GetString(reader, 14, string.Empty),
// Registered = OracleDatabase.GetBooleanFromString(reader, 15)
// };
// // If first user or user ID does not = previous user id, create new user
// if (lphsUser == null || userId != lphsUser.ID)
// {
// // Add existing user to users list
// if (lphsUser != null) users.Add(lphsUser);
// // Create new user, read user, module, company info, add activity.
// lphsUser = ReadUser(reader);
// lphsUser.Class = new LPHSClass()
// {
// ID = OracleDatabase.GetInt(reader, 11, 0),
// Name = OracleDatabase.GetString(reader, 12, string.Empty)
// };
// lphsUser.Activities = new List<LPHSActivity>();
// lphsUser.Activities.Add(activity);
// }
// else // User Id has not changed, only add activity
// {
// lphsUser.Activities.Add(activity);
// }
// }
// if (lphsUser != null) { users.Add(lphsUser); }
// }
// }
// });
// return users;
// }
#region Get/Lookup
/// <summary>
/// Get a specific user with the user id.
/// </summary>
/// <param name="userId">The user id.</param>
/// <returns></returns>
public static LPHSUser Get(int userId)
{
const string sql = @"SELECT U.USER_ID, U.USER_TYPE_ID, U.USER_NAME, U.USER_DISPLAY_NAME, U.USER_EMAIL_ADDRESS,
U.USER_STATUS, U.USER_LAST_LOGGED_ON, U.SCHOOL_ID, U.USER_SID, U.SUMTOTAL_USER_NAME, U.SUMTOTAL_USER_PWD, U.SUMTOTAL_USER_ID
FROM LPHSDB.LPHS_USER U
WHERE (USER_ID = :userid)";
using (new LogScope("LPHSUser.Get"))
{
try
{
var user = new LPHSUser();
Database.Oracle.Execute("LPHS", (conn) =>
{
using (OracleCommand command = new OracleCommand(sql, conn))
{
command.BindByName = true;
command.Parameters.Add("userid", userId);
using (var reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
if (reader.HasRows)
{
while (reader.Read())
{
user = ReadUser(reader);
}
}
}
}
});
return user;
}
catch (Exception ex)
{
Log.Write(ex, Log.Mask.Failure);
return null;
}
}
}
public ActionResult EditStudent(LPHSUser student)
{
using (new LogScope("/Class/EditStudent[POST]"))
{
try
{
// Update Student, changing only the student display name
LPHSUser s = LPHSUser.Get(student.ID);
s.DisplayName = student.DisplayName;
s.Active = student.Active;
LPHSUser.Update(s);
return RedirectToAction("Index", new { msg = string.Format("Student '{0}' updated.", s.DisplayName) });
}
catch (Exception ex)
{
Log.Write(ex, Log.Mask.Failure);
ModelState.AddModelError("", "Unable to update student.");
return View(new LPHSUser());
}
}
}
public ActionResult Create(LPHSUser user)
{
using (new LogScope("/User/Create[POST]"))
{
try
{
var u = LPHSUser.LookupInternal(user.Name);
//NRJ: do not remove this important validation!!!
if (u == null)
{
ModelState.AddModelError("Name", "Employee not found.");
//ViewBag.Schools = LPHSSchool.List();
return View(user);
}
else if (LPHSUser.GetBySid(u.Sid) != null)
{
ModelState.AddModelError("Name", "There is already an account for this employee.");
return View(user);
}
user.Name = u.Name.ToUpper();
user.DisplayName = u.DisplayName;
user.EmailAddress = u.EmailAddress;
user.Sid = u.Sid;
var newuser = LPHSUser.Create(user);
newuser.SumTotalUserName = "******" + newuser.ID;
newuser.SumTotalPassword = LPHSUser.GeneratePassword();
LPHSUser.CreateSumTotalAccount(newuser);
LPHSUser.Update(newuser);
return RedirectToAction("Index", new { msg = string.Format("Added user '{0}'.", user.DisplayName) });
}
catch (Exception ex)
{
Log.Write(ex, Log.Mask.Failure);
//ViewBag.Schools = LPHSSchool.List();
ModelState.AddModelError("", "Unable to add user.");
return View(user);
}
}
}