public static LPHSSession GetSessionBySchoolID(int? schoolIDNullable)
        {
           LPHSSession sessList = new LPHSSession();
            string sqlQuery = @"SELECT S.SESSION_ID,SESSION_NAME,SESSION_START_DATE,SESSION_END_DATE,SESSION_CREATED_BY_SID 
                    FROM LPHSDB.SESSION_TBL S, LPHSDB.SCHOOL_SESSION SS 
                    WHERE S.SESSION_ID = SS.SCHOOL_SESSION AND SS.SCHOOL_ID = :schoolID";

            using (new LogScope("LPHSSession.GetSessionBySchoolID"))
            {
                try
                {
                    Database.Oracle.Execute("LPHS", (conn) =>
                    {
                        using (OracleCommand cmd = new OracleCommand(sqlQuery, conn))
                        {
                            int schoolID = schoolIDNullable.HasValue ? schoolIDNullable.Value : -1;
                            cmd.Parameters.Add("schoolID", (int)schoolID);
                            OracleDataReader reader = cmd.ExecuteReader();
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    sessList = LPHSSession.ReadSession(reader);
                                }
                            }

                        }
                        conn.Close();
                        
                    });
                }
                catch (Exception ex)
                {
                    Log.Write(ex, Log.Mask.Failure);
                    throw;
                }
            }
            return sessList;
        }
        public static void Update(LPHSSession session)
        {
            using (new LogScope("LPHSSession.Update"))
            {
                try
                {
                    Database.Oracle.Execute("LPHS", (conn) =>
                    {
                        
                        using (OracleCommand command = new OracleCommand("UPDATE LPHSDB.SESSION_TBL SET SESSION_NAME = :Name, SESSION_START_DATE = :StartDate, SESSION_END_DATE = :EndDate WHERE SESSION_ID = :SessionID", conn))
                        {
                            command.BindByName = true;

                            command.Parameters.Add("Name", OracleDbType.Varchar2).Value = session.Name;
                            command.Parameters.Add("StartDate", OracleDbType.Date).Value = session.StartDate;
                            command.Parameters.Add("EndDate", OracleDbType.Date).Value = session.EndDate;
                            command.Parameters.Add("SessionID", OracleDbType.Int64).Value = session.ID;
                            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;
                }
            }
        }
        private static LPHSSession ReadSession(OracleDataReader reader)
        {
            LPHSSession session = new LPHSSession();
            session.ID = (int)reader.GetDecimal(0);
            session.Name = OracleDatabase.GetString(reader, 1, string.Empty);
            session.StartDate = OracleDatabase.GetDateTime(reader, 2, DateTime.Now);
            session.EndDate = OracleDatabase.GetDateTime(reader, 3, DateTime.Now);
            session.UserSid = OracleDatabase.GetString(reader, 4, string.Empty);

            return session;
        }
        public static LPHSSession Create(LPHSSession session)
        {
            int sessionCreatedID = -1;
            using (new LogScope("LPHSSession.Create"))
            {
                try
                {
                    Database.Oracle.Execute("LPHS", (conn) =>
                    {
                        using (OracleCommand command = new OracleCommand(@"
                           
                        INSERT INTO LPHSDB.SESSION_TBL 
                        (SESSION_ID, SESSION_NAME, SESSION_START_DATE, SESSION_END_DATE, SESSION_CREATED_BY_SID) VALUES (LPHSDB.SEQ_SESSION_ID.NEXTVAL, :Name, 
                        :StartDate, :EndDate, :UserSid) RETURNING session_id INTO :lastID"
                        , conn))
                        {
                            command.BindByName = true;
                            command.Parameters.Add("Name", OracleDbType.Varchar2).Value = session.Name;
                            command.Parameters.Add("StartDate", OracleDbType.Date).Value = session.StartDate;
                            command.Parameters.Add("EndDate", OracleDbType.Date).Value = session.EndDate;
                            command.Parameters.Add("UserSid", OracleDbType.Varchar2).Value = session.UserSid;

                            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();
                            session.ID = Convert.ToInt32(command.Parameters["lastID"].Value.ToString());
                        }
                    });
                   
                }
                catch (Exception ex)
                {
                    if(session.ID >0)
                        LPHSSession.Delete(sessionCreatedID);
                    
                    Log.Write(ex, Log.Mask.Failure);
                    throw;
                }
                //create session activity. 
                if (session.ID > 0)
                {
                    //ensure we always have a session value. 
                    session.SessionCopied = session.SessionCopied.HasValue ? session.SessionCopied.Value : 1;
                    try
                    {
                        Database.Oracle.Execute("LPHS", (conn) =>
                        {
                            using (OracleCommand command = new OracleCommand(@"INSERT INTO LPHSDB.ACTIVITY_SESSION (
                                                                        ACTIVITY_ID, SESSION_ID, SUMTOTAL_ACTIVITY_ID, SUMTOTAL_URL) 
                                                                        select distinct activity_id, " + session.ID.ToString() + @", SUMTOTAL_ACTIVITY_ID, SUMTOTAL_URL
                                                                        from lphsdb.activity_session
                                                                        where session_id = :sessionIDCopy"
                                , conn))
                            {
                                command.Parameters.Add("sessionIDCopy", OracleDbType.Decimal).Value = session.SessionCopied.Value;
                                //command.Parameters.Add("sessionID", OracleDbType.Decimal).Value = session.ID;
                                command.ExecuteNonQuery();
                            }
                        });
                    }

                    catch (Exception ex)
                    {
                      //  LPHSSession.Delete(session.ID);
                        Log.Write(ex, Log.Mask.Failure);
                        throw;
                    }
                }
                return session;
            }
        }