public static void Update(LPHSModule module)
{
using (new LogScope("LPHSModule.Update"))
{
try
{
String sql = @"UPDATE LPHSDB.MODULE
SET MODULE_NAME = :name,
MODULE_DESCRIPTION = :description,
COURSE_ID = :courseID
WHERE MODULE_ID = :moduleID";
Database.Oracle.Execute("LPHS", (conn) =>
{
using (OracleCommand command = new OracleCommand(sql, conn))
{
command.BindByName = true;
command.Parameters.Add("moduleID", OracleDbType.Int64).Value = module.ID;
command.Parameters.Add("name", OracleDbType.Varchar2).Value = module.Name;
command.Parameters.Add("description", OracleDbType.Varchar2).Value = module.Description;
command.Parameters.Add("courseID", OracleDbType.Int64).Value = module.CourseId;
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;
}
}
}
/// <summary>
/// Return list of all modules
/// </summary>
/// <author>David Williams</author>
/// <date>12/7/2012</date>
public static List<LPHSModule> List( int? schoolIDNullable)
{
int schoolID = schoolIDNullable.HasValue ? schoolIDNullable.Value : -1;
using (new LogScope("LPHSModule.List"))
{
try
{
Dictionary<int, LPHSModule> modules = new Dictionary<int, LPHSModule>();
Database.Oracle.Execute("LPHS", (conn) =>
{
OracleCommand command = new OracleCommand(@"
SELECT m.module_id,
m.module_name,
m.module_description,
a.activity_id,
a.activity_name,
a.activity_description,
aa.sumtotal_activity_id,
a.required,
aA.sumtotal_url
FROM LPHSDB.ACTIVITY a,
LPHSDB.MODULE m,
LPHSDB.SCHOOL_SESSION ss,
LPHSDB.ACTIVITY_SESSION aa
WHERE m.module_id = a.module_id
AND ss.SCHOOL_ID = :schoolID
AND aa.SESSION_ID = ss.SCHOOL_SESSION
AND aa.ACTIVITY_ID = a.ACTIVITY_ID
ORDER BY a.activity_id, m.module_id
", conn);
command.Parameters.Add("schoolID", OracleDbType.Int64).Value = schoolID;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
int moduleid = (int)reader.GetDecimal(0);
if (!modules.ContainsKey(moduleid))
{
modules[moduleid] = new LPHSModule
{
ID = moduleid,
Name = reader.GetString(1),
Description = reader.IsDBNull(2) ? null : reader.GetString(2),
Activities = new List<LPHSActivity>()
};
}
modules[moduleid].Activities.Add(new LPHSActivity
{
ModuleId = moduleid,
ID = (int)reader.GetDecimal(3),
Name = reader.GetString(4),
Description = reader.IsDBNull(5) ? null : reader.GetString(5),
SumTotalActivityID = reader.IsDBNull(6) ? null : reader.GetDecimal(6).ToString(),
Required = OracleDatabase.GetBooleanFromString(reader, 7), // reader.IsDBNull(7) ? -1 : (int)reader.GetDecimal(6),
ModuleName = OracleDatabase.GetString(reader, 1, string.Empty),
SumTotalURL = OracleDatabase.GetString(reader, 8, null)
});
}
}
});
return modules.Values.ToList();
}
catch (Exception ex)
{
Log.Write(ex, Log.Mask.Failure);
throw;
}
}
}
/// <summary>
/// Get single module with activities for this user.
/// </summary>
/// <param name="userId">The user id.</param>
/// <author>David Williams</author>
/// <date>12/7/2012</date>
public static LPHSModule Get(int moduleId, int? schoolIDNullable)
{
int schoolID = schoolIDNullable.HasValue ? schoolIDNullable.Value : -1;
using (new LogScope("LPHSModule.Get"))
{
try
{
LPHSModule lphsModule = new LPHSModule();
Database.Oracle.Execute("LPHS", (conn) =>
{
string oracleQuery = @"
SELECT m.module_id,
m.module_name,
m.module_description,
a.activity_id,
a.activity_name,
a.activity_description,
aa.sumtotal_activity_id,
a.required,
aA.sumtotal_url
FROM LPHSDB.ACTIVITY a,
LPHSDB.MODULE m,
LPHSDB.SCHOOL_SESSION ss,
LPHSDB.ACTIVITY_SESSION aa
WHERE m.module_id = a.module_id
AND ss.SCHOOL_ID = :schoolID
AND aa.SESSION_ID = ss.SCHOOL_SESSION
AND aa.ACTIVITY_ID = a.ACTIVITY_ID
AND m.module_id = :moduleID
ORDER BY a.activity_id, m.module_id
";
OracleCommand command = new OracleCommand(oracleQuery, conn);
command.BindByName = true;
command.Parameters.Add("moduleId", (int)moduleId);
command.Parameters.Add("SchoolId", (int)schoolID);
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
// Initialize Module
lphsModule = new LPHSModule()
{
ID = OracleDatabase.GetInt(reader, 0, 0),
Name = OracleDatabase.GetString(reader, 1, string.Empty),
Description = OracleDatabase.GetString(reader, 2, string.Empty),
CourseId = OracleDatabase.GetIntNullable(reader, 3, null),
Activities = new List<LPHSActivity>()
};
// Add first activity to module
lphsModule.Activities.Add(new LPHSActivity()
{
ID = OracleDatabase.GetInt(reader, 4, 0),
Name = OracleDatabase.GetString(reader, 5, string.Empty),
Description = OracleDatabase.GetString(reader, 6, null),
SumTotalActivityID = reader.IsDBNull(7) ? null : reader.GetDecimal(7).ToString(),
SumTotalURL = OracleDatabase.GetString(reader, 8, string.Empty)
});
};
//while (reader.Read())
//{
// // Add additional activiites for this module
// lphsModule.Activities.Add(new LPHSActivity()
// {
// ID = OracleDatabase.GetInt(reader, 2, 0),
// Name = OracleDatabase.GetString(reader, 3, string.Empty),
// Description = OracleDatabase.GetString(reader, 4, null),
// SumTotalActivityID = reader.IsDBNull(5) ? null : reader.GetDecimal(5).ToString(),
// SumTotalURL = OracleDatabase.GetString(reader, 6, string.Empty)
// });
//}
}
});
return lphsModule;
}
catch (Exception ex)
{
Log.Write(ex, Log.Mask.Failure);
throw;
}
}
}
