public static void Update(LPHSUserActivityResult userActivityResult)
{
Database.Oracle.Execute("LPHS", (conn) =>
{
String oracleQuery = @"
UPDATE LPHSDB.USER_ACTIVITY_RESULT
SET ACTIVITY_STATUS = :status, ACTIVITY_DATE_COMPLETED = :dateCompleted, ACTIVITY_SCORE = :score
WHERE (ACTIVITY_ID = :sumTotalActivityId) AND (USER_ID = :userId) ";
using (OracleCommand command = new OracleCommand(oracleQuery.ToString(), conn))
{
try
{
command.BindByName = true;
command.Parameters.Add("userId", OracleDbType.Int32).Value = userActivityResult.UserId;
command.Parameters.Add("sumTotalActivityId", OracleDbType.Int32).Value = userActivityResult.ActivityId;
command.Parameters.Add("status", OracleDbType.Varchar2).Value = (int)userActivityResult.Status;// OracleDatabase.GetBooleanStringFromBooleanNullable(userActivityResult.Registered);
command.Parameters.Add("dateCompleted", OracleDbType.Date).Value = userActivityResult.DateCompleted;
command.Parameters.Add("score", OracleDbType.Double).Value = userActivityResult.Score;
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Log.Write(ex, Log.Mask.Failure);
return;
}
}
});
}
//// Flattend view For RDLC grade report
//public string ActivityName { get; set; }
//public string UserName { get; set; }
//public bool Completed { get { return (Status == LPHSUserActivityResultStatus.Completed); } }
//public int ModuleId { get; set; }
//public string ModuleName { get; set; }
//public string UserDisplayName { get; set; }
public static LPHSUserActivityResult Get(int userId, int activityId)
{
String oracleQuery = @"
SELECT ACTIVITY_ID, USER_ID, ACTIVITY_STATUS, ACTIVITY_DATE_COMPLETED, ACTIVITY_SCORE, REGISTERED
FROM LPHSDB.USER_ACTIVITY_RESULT
WHERE USER_ID = :userId AND ACTIVITY_ID = :sumTotalActivityId ";
LPHSUserActivityResult userActResult = new LPHSUserActivityResult();
Database.Oracle.Execute("LPHS", (conn) =>
{
using (OracleCommand command = new OracleCommand(oracleQuery.ToString(), conn))
{
command.BindByName = true;
command.Parameters.Add("userId", userId);
command.Parameters.Add("sumTotalActivityId", activityId);
using (var reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
if (reader.HasRows)
{
while (reader.Read())
{
userActResult.ActivityId = OracleDatabase.GetInt(reader, 0, 0);
userActResult.UserId = OracleDatabase.GetInt(reader, 1, 0);
userActResult.Status = (LPHSUserActivityResultStatus)Convert.ToInt32((reader.IsDBNull(2) ? "0" : reader.GetString(2)));
userActResult.DateCompleted = OracleDatabase.GetDateTimeNullable(reader, 3);
userActResult.Score = OracleDatabase.GetIntNullable(reader, 4, null);
userActResult.Registered = OracleDatabase.GetBooleanFromStringNullable(reader, 4, null);
}
}
}
}
});
return userActResult;
}
