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