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