/// <summary>
/// Get an LPHS Activity by id
/// </summary>
/// <param name="sumTotalActivityId">The activity id.</param>
/// <returns></returns>
/// <author>David Williams</author>
/// <date>12/6/2012</date>
public static LPHSActivity Get(int activityId, int? schoolIDNullable)
{
int schoolID = schoolIDNullable.HasValue ? schoolIDNullable.Value : -1;
using (new LogScope("LPHSModule.Get"))
{
try
{
String oracleQuery = @"SELECT A.ACTIVITY_ID,
A.ACTIVITY_NAME,
A.ACTIVITY_DESCRIPTION,
A.MODULE_ID,
s.SUMTOTAL_ACTIVITY_ID,
s.SUMTOTAL_URL,
A.REQUIRED
FROM LPHSDB.ACTIVITY A
INNER JOIN LPHSDB.ACTIVITY_SESSION s
ON S.ACTIVITY_ID = A.ACTIVITY_ID
INNER JOIN LPHSDB.SCHOOL_SESSION SS
ON SS.SCHOOL_ID = :schoolID
AND SS.SCHOOL_SESSION = s.SESSION_ID
WHERE A.ACTIVITY_ID = :activityID ";
LPHSActivity activity = new LPHSActivity() { ID = activityId };
Database.Oracle.Execute("LPHS", (conn) =>
{
using (OracleCommand command = new OracleCommand(oracleQuery.ToString(), conn))
{
command.BindByName = true;
command.Parameters.Add("activityID", activityId);
command.Parameters.Add("schoolID", schoolID);
//command.Parameters.Add("sessionID", sessionID);
using (var reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
while (reader.Read())
{
activity.Name = OracleDatabase.GetString(reader, 1, string.Empty);
activity.Description = OracleDatabase.GetString(reader, 2, string.Empty);
activity.ModuleId = OracleDatabase.GetInt(reader, 3, 0);
activity.SumTotalActivityID = reader.IsDBNull(4) ? null : reader.GetDecimal(4).ToString();
activity.SumTotalURL = reader.IsDBNull(5) ? null : reader.GetString(5);
activity.Required = OracleDatabase.GetBooleanFromString(reader, 6);
}
}
}
});
return activity;
}
catch (Exception ex)
{
Log.Write(ex, Log.Mask.Failure);
throw;
}
}
}
public static LPHSActivity Create(LPHSActivity activity)
{
using (new LogScope("LPHSModule.Create"))
{
try
{
return activity;
}
catch (Exception ex)
{
Log.Write(ex, Log.Mask.Failure);
throw;
}
}
}
public static void Update(LPHSActivity activity)
{
using (new LogScope("LPHSActivity.Update"))
{
try
{
String sql = @"UPDATE LPHSDB.ACTIVITY A
SET A.ACTIVITY_NAME = :name,
A.ACTIVITY_DESCRIPTION = :description,
A.MODULE_ID = :moduleID,
A.SUMTOTAL_ACTIVITY_ID = :sumtotalActivityID,
A.SUMTOTAL_URL = :sumtotalURL,
A.REQUIRED = :required
WHERE A.ACTIVITY_ID = :activityID";
Database.Oracle.Execute("LPHS", (conn) =>
{
using (OracleCommand command = new OracleCommand(sql, conn))
{
command.BindByName = true;
command.Parameters.Add("activityID", OracleDbType.Int64).Value = activity.ID;
command.Parameters.Add("name", OracleDbType.Varchar2).Value = activity.Name;
command.Parameters.Add("description", OracleDbType.Varchar2).Value = activity.Description;
command.Parameters.Add("moduleID", OracleDbType.Int64).Value = activity.ModuleId;
command.Parameters.Add("sumtotalActivityID", OracleDbType.Varchar2).Value = activity.SumTotalActivityID;
command.Parameters.Add("sumtotalURL", OracleDbType.Varchar2).Value = activity.SumTotalURL;
command.Parameters.Add("required", OracleDbType.Varchar2).Value = activity.Required ? Constants.TRUE : Constants.FALSE;
foreach (OracleParameter parm in command.Parameters)
if (parm.Value == null)
parm.Value = DBNull.Value;
command.ExecuteNonQuery();
}
});
}
catch (Exception ex)
{
Log.Write(ex, Log.Mask.Failure);
throw;
}
}
}
