/// <summary>
/// Requests that the list of all answers by specified attempt on organization.
/// Adds the request to a given <c>LearningStoreJob</c> for later execution.
/// </summary>
/// <param name="job">A <c>LearningStoreJob</c> to add the new query to.</param>
/// <param name="attemptId"><c>AttemptItemIdentifier</c> represents id of attempt.</param>
protected void RequestInteractionResultsByAttempt(LearningStoreJob job, AttemptItemIdentifier attemptId)
{
LearningStoreQuery query = this.LStore.CreateQuery(Schema.InteractionResultsByAttempt.ViewName);
query.AddColumn(Schema.InteractionResultsByAttempt.ActivityAttemptId);
query.AddColumn(Schema.InteractionResultsByAttempt.ActivityPackageId);
query.AddColumn(Schema.InteractionResultsByAttempt.ActivityTitle);
query.AddColumn(Schema.InteractionResultsByAttempt.InteractionId);
query.AddColumn(Schema.InteractionResultsByAttempt.CompletionStatus);
query.AddColumn(Schema.InteractionResultsByAttempt.SuccessStatus);
query.AddColumn(Schema.InteractionResultsByAttempt.LearnerResponseBool);
query.AddColumn(Schema.InteractionResultsByAttempt.LearnerResponseString);
query.AddColumn(Schema.InteractionResultsByAttempt.LearnerResponseNumeric);
query.AddColumn(Schema.InteractionResultsByAttempt.CorrectResponse);
query.AddColumn(Schema.InteractionResultsByAttempt.InteractionType);
query.AddColumn(Schema.InteractionResultsByAttempt.MinScore);
query.AddColumn(Schema.InteractionResultsByAttempt.MaxScore);
query.AddColumn(Schema.InteractionResultsByAttempt.RawScore);
query.AddColumn(Schema.InteractionResultsByAttempt.ScaledScore);
query.AddColumn(Schema.InteractionResultsByAttempt.PrimaryResourceFromManifest);
query.SetParameter(Schema.InteractionResultsByAttempt.AttemptIdParam, attemptId);
job.PerformQuery(query);
}
/// <summary>
/// Retrieves the SLK Settings XML for a given SPSite.
/// </summary>
///
/// <param name="spSiteGuid">The GUID of the SPSite to retrieve SLK Settings for.</param>
///
/// <returns>
/// A string containing SLK Settings XML, or null if <pr>spSiteGuid</pr> is not configured for
/// use with SLK.
/// </returns>
///
/// <remarks>
/// This method is static so it can used outside the context of IIS. Only SharePoint
/// administrators can perform this function.
/// </remarks>
///
/// <exception cref="SafeToDisplayException">
/// An error occurred that can be displayed to a browser user.
/// </exception>
///
public static string GetSettingsXml(Guid spSiteGuid)
{
// only SharePoint administrators can perform this action
CheckPermissions();
// set <mapping> to the mapping between <spSiteGuid> and the LearningStore connection
// information for that SPSite
SlkSPSiteMapping mapping = SlkSPSiteMapping.GetMapping(spSiteGuid);
if (mapping == null)
{
return(null);
}
// return the SLK Settings XML for this SPSite
LearningStore learningStore = new LearningStore(mapping.DatabaseConnectionString,
String.Empty, ImpersonationBehavior.UseOriginalIdentity);
using (LearningStorePrivilegedScope privilegedScope = new LearningStorePrivilegedScope())
{
LearningStoreJob job = learningStore.CreateJob();
LearningStoreQuery query = learningStore.CreateQuery(Schema.SiteSettingsItem.ItemTypeName);
query.AddColumn(Schema.SiteSettingsItem.SettingsXml);
query.AddCondition(Schema.SiteSettingsItem.SiteGuid, LearningStoreConditionOperator.Equal, spSiteGuid);
job.PerformQuery(query);
DataRowCollection dataRows = job.Execute <DataTable>().Rows;
if (dataRows.Count != 1)
{
throw new SafeToDisplayException(LoadCulture(spSiteGuid).Resources.SlkSettingsNotFound, spSiteGuid);
}
DataRow dataRow = dataRows[0];
return((string)dataRow[0]);
}
}
/// <summary>
/// Retrieves attempt identifier for specified organization id and Iudico topic id.
/// </summary>
/// <param name="orgId"><c>ActivityPackageItemIdentifier</c> value representing Organization ID.</param>
/// <param name="curriculumChapterTopicId">Integer value - IUDICO curriculum chapter topic id.</param>
/// <param name="topicType"><see cref="TopicTypeEnum"/> value.</param>
/// <returns><c>AttemptItemIdentifier</c> value representing Attempt Identifier.</returns>
protected AttemptItemIdentifier GetAttemptIdentifier(
ActivityPackageItemIdentifier orgId, int curriculumChapterTopicId, TopicTypeEnum topicType)
{
AttemptItemIdentifier result = null;
LearningStoreJob job = this.LStore.CreateJob();
LearningStoreQuery query = this.LStore.CreateQuery(Schema.MyAttemptIds.ViewName);
query.AddColumn(Schema.MyAttemptIds.AttemptId);
query.SetParameter(Schema.MyAttemptIds.CurriculumChapterTopicId, curriculumChapterTopicId);
query.SetParameter(Schema.MyAttemptIds.OrganizationId, orgId);
query.SetParameter(Schema.MyAttemptIds.TopicType, topicType);
job.PerformQuery(query);
ReadOnlyCollection <object> resultList = job.Execute();
var dataTable = (DataTable)resultList[0];
if (dataTable.Rows.Count > 0)
{
// get last result
LStoreHelper.Cast(dataTable.Rows[dataTable.Rows.Count - 1][Schema.MyAttemptIds.AttemptId], out result);
}
return(result);
}
/// <summary>
/// Performs the Query Execution.
/// </summary>
/// <param name="queryDef">QueryDefinition</param>
/// <returns>Query Resutl to Render</returns>
private List <RenderedCell[]> PerformQuery(QueryDefinition queryDef)
{
// create a job for executing the query specified by <queryDef>
LearningStoreJob job = SlkStore.LearningStore.CreateJob();
// create a query based on <queryDef>
LearningStoreQuery query = null;
int[,] columnMap; // see QueryDefinition.CreateQuery
try
{
query = CreateStandardQuery(queryDef, false, out columnMap);
}
catch (SlkSettingsException ex)
{
throw new SafeToDisplayException(ex.Message);
}
// add the query to the job
job.PerformQuery(query);
// execute the job
DataTable queryResults = job.Execute <DataTable>();
// render the query results into <renderedRows>
List <RenderedCell[]> renderedRows = new List <RenderedCell[]>(queryResults.Rows.Count);
foreach (DataRow dataRow in queryResults.Rows)
{
RenderedCell[] renderedCells = queryDef.RenderQueryRowCells(dataRow, columnMap, ResolveSPWebName, SPWeb.RegionalSettings.TimeZone);
renderedRows.Add(renderedCells);
}
return(renderedRows);
}
/// <summary>
/// Performs the Query Execution.
/// </summary>
/// <param name="queryDef">QueryDefinition</param>
/// <param name="job">LearningStoreJob instance to perform Job</param>
private void PerformQuery(QueryDefinition queryDef, LearningStoreJob job)
{
// create a query based on <queryDef>
LearningStoreQuery query;
int[,] columnMap; // see QueryDefinition.CreateQuery
query = CreateStandardQuery(queryDef, true, out columnMap);
// add the query to the job
job.PerformQuery(query);
}
/// <summary>
/// Requests that information about the current user be retrieved from the
/// LearningStore database. Adds the request to a given
/// <c>LearningStoreJob</c> for later execution.
/// </summary>
///
/// <param name="job">A <c>LearningStoreJob</c> to add the new query to.
/// </param>
///
/// <remarks>
/// After executing this method, and later calling <c>Job.Execute</c>,
/// call <c>GetCurrentUserInfoResults</c> to convert the <c>DataTable</c>
/// returned by <c>Job.Execute</c> into an <c>LStoreUserInfo</c> object.
/// </remarks>
protected void RequestCurrentUserInfo(LearningStoreJob job)
{
// look up the user in the UserItem table in the database using their
// user key, and set <userId> to the LearningStore numeric identifier
// of the user (i.e. UserItem.Id -- the "Id" column of the UserItem
// table) and <userName> to their full name (e.g. "Karen Berg"); if
// there's no UserItem for the user, add one and set <userId> to its
// ID
LearningStoreQuery query = this.LStore.CreateQuery(Schema.Me.ViewName);
query.AddColumn(Schema.Me.UserId);
query.AddColumn(Schema.Me.UserName);
job.PerformQuery(query);
}
/// <summary>
/// Requests that the list of all attempts all users have performed on all organizaions.
/// Adds the request to a given <c>LearningStoreJob</c> for later execution.
/// </summary>
/// <param name="job">A <c>LearningStoreJob</c> to add the new query to.</param>
protected void RequestAllAttempts(LearningStoreJob job)
{
LearningStoreQuery query = LStore.CreateQuery(Schema.AllAttemptsResults.ViewName);
query.AddColumn(Schema.AllAttemptsResults.AttemptId);
query.AddColumn(Schema.AllAttemptsResults.UserItemKey);
query.AddColumn(Schema.AllAttemptsResults.ThemeId);
query.AddColumn(Schema.AllAttemptsResults.CompletionStatus);
query.AddColumn(Schema.AllAttemptsResults.AttemptStatus);
query.AddColumn(Schema.AllAttemptsResults.SuccessStatus);
query.AddColumn(Schema.AllAttemptsResults.StartedTimestamp);
query.AddColumn(Schema.AllAttemptsResults.Score);
job.PerformQuery(query);
}
protected void RequestAttemptsByTopic(LearningStoreJob job, Int32 topicId)
{
LearningStoreQuery query = LStore.CreateQuery(Schema.AttemptsResultsByThemeAndUser.ViewName);
query.AddColumn(Schema.AttemptsResultsByThemeAndUser.AttemptId);
query.AddColumn(Schema.AttemptsResultsByThemeAndUser.CompletionStatus);
query.AddColumn(Schema.AttemptsResultsByThemeAndUser.AttemptStatus);
query.AddColumn(Schema.AttemptsResultsByThemeAndUser.SuccessStatus);
query.AddColumn(Schema.AttemptsResultsByThemeAndUser.StartedTimestamp);
query.AddColumn(Schema.AttemptsResultsByThemeAndUser.Score);
query.SetParameter(Schema.AttemptsResultsByThemeAndUser.ThemeIdParam, topicId);
job.PerformQuery(query);
}
protected override void OnLoad(EventArgs e)
{
// ensure the user is an administrator, then execute the remaining code within a
// LearningStorePrivilegedScope (which grants full access to database views)
if (!SPFarm.Local.CurrentUserIsAdministrator())
{
throw new UnauthorizedAccessException(
"Access is denied. Only adminstrators can access this page.");
}
using (new LearningStorePrivilegedScope())
{
// skip the code below during postback since <OriginalInstructor> will have been
// populated from view state already
if (IsPostBack)
{
return;
}
// populate the <OriginalInstructor> drop-down list with the names and SLK user
// identifiers of all users who are instructors on any assignments in the current
// SharePoint site collection
using (SPWeb spWeb = SPControl.GetContextWeb(HttpContext.Current))
{
SlkStore slkStore = SlkStore.GetStore(spWeb);
LearningStoreJob job = slkStore.LearningStore.CreateJob();
LearningStoreQuery query = slkStore.LearningStore.CreateQuery(
"AllAssignmentInstructors");
query.SetParameter("SPSiteGuid", spWeb.Site.ID);
query.AddColumn("InstructorName");
query.AddColumn("InstructorId");
query.AddSort("InstructorName", LearningStoreSortDirection.Ascending);
job.PerformQuery(query);
DataRowCollection rows = job.Execute <DataTable>().Rows;
OriginalInstructor.Items.Add(String.Empty);
foreach (DataRow row in rows)
{
ListItem listItem = new ListItem();
listItem.Text = (string)row["InstructorName"];
UserItemIdentifier originalInstructorId =
new UserItemIdentifier((LearningStoreItemIdentifier)row["InstructorId"]);
listItem.Value = originalInstructorId.GetKey().ToString(
CultureInfo.InvariantCulture);
OriginalInstructor.Items.Add(listItem);
}
}
}
}
/// <summary>
/// Deletes pacakge and related attempts from database.
/// </summary>
/// <param name="packId">Long integer value represents package identifier.</param>
protected void DeletePackage(long packId)
{
// set <packageId> to the ID of this package
PackageItemIdentifier packageId = new PackageItemIdentifier(packId);
// before we delete the package, we need to delete all attempts on the package --
// the following query looks for those attempts
LearningStoreJob job = LStore.CreateJob();
LearningStoreQuery query = LStore.CreateQuery(
Schema.MyAttempts.ViewName);
query.AddCondition(Schema.MyAttempts.PackageId,
LearningStoreConditionOperator.Equal, packageId);
query.AddCondition(Schema.MyAttempts.AttemptId,
LearningStoreConditionOperator.NotEqual, null);
query.AddColumn(Schema.MyAttempts.AttemptId);
query.AddSort(Schema.MyAttempts.AttemptId,
LearningStoreSortDirection.Ascending);
job.PerformQuery(query);
DataTable dataTable = job.Execute <DataTable>();
AttemptItemIdentifier previousAttemptId = null;
// loop once for each attempt on this package
foreach (DataRow dataRow in dataTable.Rows)
{
// set <attemptId> to the ID of this attempt
AttemptItemIdentifier attemptId;
LStoreHelper.CastNonNull(dataRow["AttemptId"], out attemptId);
// if <attemptId> is a duplicate attempt ID, skip it; note that the query
// results are sorted by attempt ID (see above)
if ((previousAttemptId != null) &&
(previousAttemptId.GetKey() == attemptId.GetKey()))
{
continue;
}
// delete this attempt
StoredLearningSession.DeleteAttempt(LStore, attemptId);
// continue to the next attempt
previousAttemptId = attemptId;
}
// delete the package
PStore.DeletePackage(packageId);
}
/// <summary>
/// Requests that the list of training for the current user be retrieved from the LearningStore
/// database. Adds the request to a given <c>LearningStoreJob</c> for later execution.
/// </summary>
/// <param name="job">A <c>LearningStoreJob</c> to add the new query to.</param>
/// <param name="packageId">To request information related to a single pass the
/// <c>PackageItemIdentifier</c> of the package as this parameter. Otherwise, leave this
/// parameter <c>null</c>.</param>
protected void RequestMyTraining(LearningStoreJob job,
PackageItemIdentifier packageId)
{
LearningStoreQuery query = LStore.CreateQuery(Schema.MyAttempts.ViewName);
query.AddColumn(Schema.MyAttempts.PackageId);
query.AddColumn(Schema.MyAttempts.OrganizationId);
query.AddColumn(Schema.MyAttempts.AttemptId);
query.AddColumn(Schema.MyAttempts.AttemptStatus);
query.AddColumn(Schema.MyAttempts.TotalPoints);
if (packageId != null)
{
query.AddCondition(Schema.MyAttempts.PackageId,
LearningStoreConditionOperator.Equal, packageId);
}
job.PerformQuery(query);
}
/// <summary>
/// Affects <paramref name="job"/> object by adding query for all columns from <see cref="AllAttemptsColumns"/>
/// and adding conditions specified in <paramref name="conditions"/>
/// </summary>
/// <param name="job"><see cref="LearningStoreJob"/> object to add query to.</param>
/// <param name="conditions"><see cref="List{T}"/> of <see cref="QueryCondition"/> objects to add conditions to query.</param>
protected void RequestAttemptResults(LearningStoreJob job, List <QueryCondition> conditions)
{
LearningStoreQuery query = this.LStore.CreateQuery(Schema.AllAttemptsResults.ViewName);
// Select all columns except those mentioned in conditions.
var queryColumns = AllAttemptsColumns.Except(conditions.Select(cond => cond.ColumnName)).ToList();
// add columns to query
foreach (var queryColumn in queryColumns)
{
query.AddColumn(queryColumn);
}
// add conditions to query
foreach (var condition in conditions)
{
query.AddCondition(condition.ColumnName, condition.ConditionOperator, condition.Value);
}
job.PerformQuery(query);
}
private static SlkSettings LoadSettings(SlkSPSiteMapping mapping, SPSite site, XmlSchema xmlSchema)
{
// create a LearningStore. Read is in privileged scope so irrelevant what key is.
// Cannot use current user as may be be called in a page PreInit event when it's not necessarily valid
string learningStoreKey = "SHAREPOINT\\System";
LearningStore learningStore = new LearningStore(mapping.DatabaseConnectionString, learningStoreKey, ImpersonationBehavior.UseOriginalIdentity);
// read the SLK Settings file from the database into <settings>
SlkSettings settings;
using (LearningStorePrivilegedScope privilegedScope = new LearningStorePrivilegedScope())
{
LearningStoreJob job = learningStore.CreateJob();
LearningStoreQuery query = learningStore.CreateQuery(Schema.SiteSettingsItem.ItemTypeName);
query.AddColumn(Schema.SiteSettingsItem.SettingsXml);
query.AddColumn(Schema.SiteSettingsItem.SettingsXmlLastModified);
query.AddCondition(Schema.SiteSettingsItem.SiteGuid, LearningStoreConditionOperator.Equal, mapping.SPSiteGuid);
job.PerformQuery(query);
DataRowCollection dataRows = job.Execute <DataTable>().Rows;
if (dataRows.Count != 1)
{
throw new SafeToDisplayException(SlkCulture.GetResources().SlkSettingsNotFound, site.Url);
}
DataRow dataRow = dataRows[0];
string settingsXml = (string)dataRow[0];
DateTime settingsXmlLastModified = ((DateTime)dataRow[1]);
using (StringReader stringReader = new StringReader(settingsXml))
{
XmlReaderSettings xmlSettings = new XmlReaderSettings();
xmlSettings.Schemas.Add(xmlSchema);
xmlSettings.ValidationType = ValidationType.Schema;
using (XmlReader xmlReader = XmlReader.Create(stringReader, xmlSettings))
{
settings = new SlkSettings(xmlReader, settingsXmlLastModified);
}
}
}
return(settings);
}
/// <summary>
/// Retrieves package id by specified IUDICO course.
/// </summary>
/// <param name="course">Course object represents iudico course entity.</param>
/// <returns>PackageItemIdentifier value representing corresponding MLC Package ID.</returns>
protected PackageItemIdentifier GetPackageIdentifier(int courseId)
{
PackageItemIdentifier result = null;
LearningStoreJob job = LStore.CreateJob();
LearningStoreQuery query = LStore.CreateQuery(Schema.PackageIdByCourse.ViewName);
query.AddColumn(Schema.PackageIdByCourse.PackageId);
query.SetParameter(Schema.PackageIdByCourse.IudicoCourseRef, courseId);
job.PerformQuery(query);
ReadOnlyCollection <object> resultList = job.Execute();
DataTable dataTable = (DataTable)resultList[0];
if (dataTable.Rows.Count > 0)
{
LStoreHelper.Cast(dataTable.Rows[0][Schema.PackageIdByCourse.PackageId], out result);
}
return(result);
}
/// <summary>
/// Retrieves Organization Id by specified package oidentifier.
/// </summary>
/// <param name="packageId"><c>PackageItemIdentifier</c> value representing package id, organization is being searched by.</param>
/// <returns><c>ActivityPackageItemIdentifier</c> value, which represents organization identifier of specified package.</returns>
protected ActivityPackageItemIdentifier GetOrganizationIdentifier(PackageItemIdentifier packageId)
{
ActivityPackageItemIdentifier result = null;
LearningStoreJob job = LStore.CreateJob();
LearningStoreQuery query = LStore.CreateQuery(Schema.RootActivityByPackage.ViewName);
query.AddColumn(Schema.RootActivityByPackage.RootActivity);
query.SetParameter(Schema.RootActivityByPackage.PackageId, packageId);
job.PerformQuery(query);
var resultList = job.Execute();
DataTable dataTable = (DataTable)resultList[0];
if (dataTable.Rows.Count > 0)
{
LStoreHelper.Cast(dataTable.Rows[0][Schema.RootActivityByPackage.RootActivity], out result);
}
return(result);
}
/// <summary>
/// Requests that the list of training for the current user be retrieved from the LearningStore
/// database. Adds the request to a given <c>LearningStoreJob</c> for later execution.
/// </summary>
///
/// <param name="job">A <c>LearningStoreJob</c> to add the new query to.</param>
///
/// <param name="packageId">To request information related to a single pass the
/// <c>PackageItemIdentifier</c> of the package as this parameter. Otherwise, leave this
/// parameter <c>null</c>.</param>
///
/// <remarks>
/// After executing this method, and later calling <c>Job.Execute</c>, call
/// <c>GetMyTrainingResultsToHtml</c> to convert the <c>DataTable</c> returned by
/// <c>Job.Execute</c> into HTML.
/// </remarks>
///
protected void RequestMyTraining(LearningStoreJob job,
PackageItemIdentifier packageId)
{
LearningStoreQuery query = LStore.CreateQuery(Schema.MyAttemptsAndPackages.ViewName);
query.AddColumn(Schema.MyAttemptsAndPackages.PackageId);
query.AddColumn(Schema.MyAttemptsAndPackages.PackageFileName);
query.AddColumn(Schema.MyAttemptsAndPackages.OrganizationId);
query.AddColumn(Schema.MyAttemptsAndPackages.OrganizationTitle);
query.AddColumn(Schema.MyAttemptsAndPackages.AttemptId);
query.AddColumn(Schema.MyAttemptsAndPackages.UploadDateTime);
query.AddColumn(Schema.MyAttemptsAndPackages.AttemptStatus);
query.AddColumn(Schema.MyAttemptsAndPackages.TotalPoints);
query.AddSort(Schema.MyAttemptsAndPackages.UploadDateTime,
LearningStoreSortDirection.Ascending);
query.AddSort(Schema.MyAttemptsAndPackages.OrganizationId,
LearningStoreSortDirection.Ascending);
if (packageId != null)
{
query.AddCondition(Schema.MyAttemptsAndPackages.PackageId,
LearningStoreConditionOperator.Equal, packageId);
}
job.PerformQuery(query);
}
/// <summary>
/// Retrieves attempt identifier for specified organization id and Iudico topic id.
/// </summary>
/// <param name="orgId"><c>ActivityPackageItemIdentifier</c> value representing Organization ID.</param>
/// <param name="topicId">Integer value - IUDICO topic id.</param>
/// <returns><c>AttemptItemIdentifier</c> value representing Attempt Identifier.</returns>
protected AttemptItemIdentifier GetAttemptIdentifier(ActivityPackageItemIdentifier orgId, int topicId)
{
AttemptItemIdentifier result = null;
LearningStoreJob job = LStore.CreateJob();
LearningStoreQuery query = LStore.CreateQuery(Schema.MyAttempts.ViewName);
query.AddColumn(Schema.MyAttempts.AttemptId);
query.AddCondition(Schema.MyAttempts.OrganizationId, LearningStoreConditionOperator.Equal, orgId);
query.AddCondition(Schema.MyAttempts.ThemeId, LearningStoreConditionOperator.Equal, topicId);
job.PerformQuery(query);
ReadOnlyCollection <object> resultList = job.Execute();
DataTable dataTable = (DataTable)resultList[0];
if (dataTable.Rows.Count > 0)
{
// get last result
LStoreHelper.Cast(dataTable.Rows[dataTable.Rows.Count - 1][Schema.MyAttempts.AttemptId], out result);
}
return(result);
}
/// <summary>
/// Requests that the list of all attempts all users have performed on all organizaions.
/// Adds the request to a given <c>LearningStoreJob</c> for later execution.
/// </summary>
/// <param name="job">A <c>LearningStoreJob</c> to add the new query to.</param>
protected void RequestAllAttempts(LearningStoreJob job)
{
LearningStoreQuery query = LStore.CreateQuery(Schema.AllAttemptsResults.ViewName);
query.AddColumn(Schema.AllAttemptsResults.AttemptId);
query.AddColumn(Schema.AllAttemptsResults.UserItemKey);
query.AddColumn(Schema.AllAttemptsResults.ThemeId);
query.AddColumn(Schema.AllAttemptsResults.CompletionStatus);
query.AddColumn(Schema.AllAttemptsResults.AttemptStatus);
query.AddColumn(Schema.AllAttemptsResults.SuccessStatus);
query.AddColumn(Schema.AllAttemptsResults.StartedTimestamp);
query.AddColumn(Schema.AllAttemptsResults.Score);
job.PerformQuery(query);
}
protected void RequestAttemptsByTopic(LearningStoreJob job, Int32 topicId)
{
LearningStoreQuery query = LStore.CreateQuery(Schema.AttemptsResultsByThemeAndUser.ViewName);
query.AddColumn(Schema.AttemptsResultsByThemeAndUser.AttemptId);
query.AddColumn(Schema.AttemptsResultsByThemeAndUser.CompletionStatus);
query.AddColumn(Schema.AttemptsResultsByThemeAndUser.AttemptStatus);
query.AddColumn(Schema.AttemptsResultsByThemeAndUser.SuccessStatus);
query.AddColumn(Schema.AttemptsResultsByThemeAndUser.StartedTimestamp);
query.AddColumn(Schema.AttemptsResultsByThemeAndUser.Score);
query.SetParameter(Schema.AttemptsResultsByThemeAndUser.ThemeIdParam, topicId);
job.PerformQuery(query);
}
/// <summary>
/// Requests that the list of all answers by specified attempt on organization.
/// Adds the request to a given <c>LearningStoreJob</c> for later execution.
/// </summary>
/// <param name="job">A <c>LearningStoreJob</c> to add the new query to.</param>
/// <param name="attemptId"><c>AttemptItemIdentifier</c> represents id of attempt.</param>
protected void RequestInteractionResultsByAttempt(LearningStoreJob job, AttemptItemIdentifier attemptId)
{
LearningStoreQuery query = LStore.CreateQuery(Schema.InteractionResultsByAttempt.ViewName);
query.AddColumn(Schema.InteractionResultsByAttempt.ActivityAttemptId);
query.AddColumn(Schema.InteractionResultsByAttempt.ActivityPackageId);
query.AddColumn(Schema.InteractionResultsByAttempt.ActivityTitle);
query.AddColumn(Schema.InteractionResultsByAttempt.InteractionId);
query.AddColumn(Schema.InteractionResultsByAttempt.CompletionStatus);
query.AddColumn(Schema.InteractionResultsByAttempt.SuccessStatus);
query.AddColumn(Schema.InteractionResultsByAttempt.LearnerResponseBool);
query.AddColumn(Schema.InteractionResultsByAttempt.LearnerResponseString);
query.AddColumn(Schema.InteractionResultsByAttempt.LearnerResponseNumeric);
query.AddColumn(Schema.InteractionResultsByAttempt.CorrectResponse);
query.AddColumn(Schema.InteractionResultsByAttempt.InteractionType);
query.AddColumn(Schema.InteractionResultsByAttempt.ScaledScore);
query.SetParameter(Schema.InteractionResultsByAttempt.AttemptIdParam, attemptId);
job.PerformQuery(query);
}
/// <summary>
/// Creates a new <Typ>ExecuteNavigator</Typ> object in memory and its representation in the database.
/// </summary>
/// <param name="store">A <Typ>LearningStore</Typ> object that references the database to use.</param>
/// <param name="rootActivityId">The database identifier for the root activity (i.e. organization) of the activity tree to attempt.</param>
/// <param name="learnerId">The database identifier for the learner information.</param>
/// <param name="learnerAssignmentId">The database identifier for the learner assignment information. Only used in SLK.</param>
/// <param name="loggingFlags">Flags specifying which actions to log.</param>
/// <returns>A new <Typ>ExecuteNavigator</Typ> object.</returns>
/// <exception cref="LearningStoreItemNotFoundException">Thrown if the learner ID or root activity ID is invalid, or if the package information cannot be found.</exception>
static public ExecuteNavigator CreateExecuteNavigator(LearningStore store, long rootActivityId, long learnerId, long learnerAssignmentId, LoggingOptions loggingFlags)
{
ExecuteNavigator eNav = new ExecuteNavigator();
LearningStoreJob job = store.CreateJob();
// first add security
Dictionary <string, object> securityParameters = new Dictionary <string, object>();
securityParameters[Schema.CreateAttemptRight.RootActivityId] =
new LearningStoreItemIdentifier(Schema.ActivityPackageItem.ItemTypeName, rootActivityId);
securityParameters[Schema.CreateAttemptRight.LearnerId] =
new LearningStoreItemIdentifier(Schema.UserItem.ItemTypeName, learnerId);
job.DemandRight(Schema.CreateAttemptRight.RightName, securityParameters);
job.DisableFollowingSecurityChecks();
// first query for all information about the learner
LearningStoreQuery query = store.CreateQuery(Schema.UserItem.ItemTypeName);
query.AddColumn(Schema.UserItem.Id);
query.AddColumn(Schema.UserItem.Name);
query.AddColumn(Schema.UserItem.Language);
query.AddColumn(Schema.UserItem.AudioCaptioning);
query.AddColumn(Schema.UserItem.AudioLevel);
query.AddColumn(Schema.UserItem.DeliverySpeed);
query.AddCondition(Schema.UserItem.Id, LearningStoreConditionOperator.Equal,
new LearningStoreItemIdentifier(Schema.UserItem.ItemTypeName, learnerId));
job.PerformQuery(query);
// then query the package information
query = store.CreateQuery(Schema.SeqNavActivityPackageView.ViewName);
LearningStoreItemIdentifier rootId = new LearningStoreItemIdentifier(Schema.ActivityPackageItem.ItemTypeName, rootActivityId);
query.AddColumn(Schema.SeqNavActivityPackageView.PackageId);
query.AddColumn(Schema.SeqNavActivityPackageView.PackageFormat);
query.AddColumn(Schema.SeqNavActivityPackageView.PackagePath);
query.AddCondition(Schema.SeqNavActivityPackageView.Id, LearningStoreConditionOperator.Equal, rootId);
job.PerformQuery(query);
// then query for the activity tree
query = store.CreateQuery(Schema.SeqNavActivityTreeView.ViewName);
query.AddColumn(Schema.SeqNavActivityTreeView.DataModelCache);
query.AddColumn(Schema.SeqNavActivityTreeView.ParentActivityId);
query.AddColumn(Schema.SeqNavActivityTreeView.Id);
query.AddColumn(Schema.SeqNavActivityTreeView.ObjectivesGlobalToSystem);
query.AddCondition(Schema.SeqNavActivityTreeView.RootActivityId, LearningStoreConditionOperator.Equal, new LearningStoreItemIdentifier(Schema.ActivityPackageItem.ItemTypeName, rootActivityId));
job.PerformQuery(query);
DataTable d; // used to store results of query
ReadOnlyCollection <object> ids;
// for this transaction we need to read from the activitypackageitem table and write new records to
// the activityattemptitem and attemptitem tables
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.RepeatableRead;
using (LearningStoreTransactionScope scope = new LearningStoreTransactionScope(options))
{
// execute the query
ReadOnlyCollection <object> c = job.Execute();
Utilities.Assert(c.Count == 3);
if (((DataTable)c[0]).Rows.Count < 1)
{
throw new LearningStoreItemNotFoundException(Resources.InvalidLearnerId);
}
if (((DataTable)c[1]).Rows.Count < 1)
{
throw new LearningStoreItemNotFoundException(Resources.InvalidRootActivityId);
}
if (((DataTable)c[2]).Rows.Count < 1)
{
throw new LearningStoreItemNotFoundException(Resources.InvalidRootActivityId);
}
d = (DataTable)c[0]; // save learner information
string learnerName = (string)d.Rows[0][Schema.UserItem.Name];
AudioCaptioning learnerCaption = (AudioCaptioning)d.Rows[0][Schema.UserItem.AudioCaptioning];
float learnerAudioLevel = (float)d.Rows[0][Schema.UserItem.AudioLevel];
float learnerDeliverySpeed = (float)d.Rows[0][Schema.UserItem.DeliverySpeed];
string learnerLanguage = (string)d.Rows[0][Schema.UserItem.Language];
d = (DataTable)c[1]; // save package information
// we need to create the activity tree within the transaction because it may affect the data written
// by means of the selection and randomization processes.
eNav.m_packageFormat = (PackageFormat)d.Rows[0][Schema.SeqNavActivityPackageView.PackageFormat];
eNav.m_packageId = ((LearningStoreItemIdentifier)d.Rows[0][Schema.SeqNavActivityPackageView.PackageId]).GetKey();
eNav.m_packageLocation = (string)d.Rows[0][Schema.SeqNavActivityPackageView.PackagePath];
eNav.m_store = store;
eNav.m_learnerId = learnerId;
eNav.m_loggingFlags = loggingFlags;
// we must set this here so that the Activity constructor doesn't try and load missing info
eNav.m_attemptItemInformationIsValid = true;
d = (DataTable)c[2]; // save data to create activity tree later, when we are done with sql processing
eNav.AddActivities(d, rootActivityId, learnerName, learnerLanguage, learnerCaption, learnerAudioLevel, learnerDeliverySpeed);
/*
* // create activity objects and store them in a big dictionary
* foreach(DataRow row in d.Rows)
* {
* Activity act = new Activity(eNav, ((LearningStoreItemIdentifier)row[Schema.SeqNavActivityTreeView.Id]).GetKey(),
* ConvertLearningStoreXmlToXPathNavigator(row[Schema.SeqNavActivityTreeView.DataModelCache] as LearningStoreXml, true),
* null, null, null, eNav.WrapAttachment, eNav.WrapAttachmentGuid, -1,
* (bool)row[Schema.SeqNavActivityTreeView.ObjectivesGlobalToSystem], eNav.m_learnerId.ToString(System.Globalization.CultureInfo.InvariantCulture),
* learnerName, learnerLanguage, learnerCaption, learnerAudioLevel, learnerDeliverySpeed);
* eNav.m_activities[act.ActivityId] = act;
* if(act.ActivityId == rootActivityId)
* {
* eNav.RootActivity = act;
* if(!(row[Schema.SeqNavActivityTreeView.ParentActivityId] is DBNull))
* {
* throw new LearningStoreItemNotFoundException(Resources.InvalidRootActivityId);
* }
* }
* }
*
* // now that we have all the activities in a big dictionary, find all the parents to build the tree
* // in theory this could be done in the first loop if I sort the query by parentid, but that's making a lot
* // of assumptions about the structure of the database that I don't think are safe to make
* foreach(DataRow row in d.Rows)
* {
* LearningStoreItemIdentifier parentId = row[Schema.SeqNavActivityTreeView.ParentActivityId] as LearningStoreItemIdentifier;
* if (parentId != null)
* {
* long id = ((LearningStoreItemIdentifier)row[Schema.SeqNavActivityTreeView.Id]).GetKey();
* eNav.m_activities[id].Parent = eNav.m_activities[parentId.GetKey()];
* eNav.m_activities[parentId.GetKey()].AddChild(eNav.m_activities[id]);
* }
* }
*
* // make sure children of each parent are in the right order, and set Previous and Next pointer correctly.
* eNav.SortActivityTree();
*
* // step through the activity tree searching for selection and randomization instructions
* // this must be done before the ActivityAttemptItems are saved because they may reorder
* // and potentially even remove children.
* Random rand = new Random();
* foreach(Activity a in eNav.Traverse)
* {
* if(!a.IsLeaf)
* {
* // if there's a valid selection instruction, remove excess
* // child activities randomly
* if(a.Sequencing.SelectionTiming == RandomizationTiming.Once &&
* a.Sequencing.RandomizationSelectCount > 0 &&
* a.Sequencing.RandomizationSelectCount < a.Children.Count)
* {
* int selCount = a.Sequencing.RandomizationSelectCount;
* for(int i = 0 ; i < a.Children.Count ; ++i)
* {
* if(rand.Next(a.Children.Count) < selCount)
* {
* --selCount;
* }
* else
* {
* a.RemoveChild(i);
* }
* }
* a.SortChildren();
* }
*
* // if there's a valid randomization instruction, randomize order of the children
* // of this activity
* if((a.Sequencing.RandomizationTiming == RandomizationTiming.Once ||
* a.Sequencing.RandomizationTiming == RandomizationTiming.OnEachNewAttempt) &&
* a.Sequencing.ReorderChildren)
* {
* List<Activity> randlist = new List<Activity>();
* while(a.Children.Count > 0)
* {
* int i = rand.Next(a.Children.Count);
* randlist.Add((Activity)a.Children[i]);
* a.RemoveChild(i);
* }
* for(int i = 0 ; i < randlist.Count ; ++i)
* {
* randlist[i].RandomPlacement = i;
* a.AddChild(randlist[i]);
* }
* a.SortChildren();
* }
* }
* }
* */
// create the attemptitem
// fill in fields with no defaults
job = store.CreateJob();
job.DisableFollowingSecurityChecks();
Dictionary <string, object> attempt = new Dictionary <string, object>();
attempt[Schema.AttemptItem.RootActivityId] = new LearningStoreItemIdentifier(Schema.ActivityPackageItem.ItemTypeName, rootActivityId);
attempt[Schema.AttemptItem.LearnerId] = new LearningStoreItemIdentifier(Schema.UserItem.ItemTypeName, learnerId);
if (learnerAssignmentId != 0)
{
attempt[Schema.AttemptItem.LearnerAssignmentId] = new LearningStoreItemIdentifier("LearnerAssignmentItem", learnerAssignmentId);
}
attempt[Schema.AttemptItem.PackageId] = new LearningStoreItemIdentifier(Schema.PackageItem.ItemTypeName, eNav.m_packageId);
eNav.m_attemptStartTime = DateTime.UtcNow;
attempt[Schema.AttemptItem.StartedTimestamp] = eNav.m_attemptStartTime;
attempt[Schema.AttemptItem.LogDetailSequencing] = ((loggingFlags & LoggingOptions.LogDetailSequencing) == LoggingOptions.LogDetailSequencing);
attempt[Schema.AttemptItem.LogFinalSequencing] = ((loggingFlags & LoggingOptions.LogFinalSequencing) == LoggingOptions.LogFinalSequencing);
attempt[Schema.AttemptItem.LogRollup] = ((loggingFlags & LoggingOptions.LogRollup) == LoggingOptions.LogRollup);
LearningStoreItemIdentifier attemptId = job.AddItem(Schema.AttemptItem.ItemTypeName, attempt, true);
// create activityattemptitems for each activity in the tree
foreach (Activity a in eNav.Traverse)
{
Dictionary <string, object> activity = new Dictionary <string, object>();
// fill in everything that has no default
activity[Schema.ActivityAttemptItem.AttemptId] = attemptId;
activity[Schema.ActivityAttemptItem.ActivityPackageId] = new LearningStoreItemIdentifier(Schema.ActivityPackageItem.ItemTypeName, a.ActivityId);
if (a.RandomPlacement >= 0)
{
activity[Schema.ActivityAttemptItem.RandomPlacement] = a.RandomPlacement;
// sibling activities are ordered by RandomPlacement first,
// then by OriginalPlacment; RandomPlacement is -1 for all siblings if not randomization occurs
}
job.AddItem(Schema.ActivityAttemptItem.ItemTypeName, activity, true);
}
ids = job.Execute();
scope.Complete();
}
// fill in some vital data for the navigator
eNav.m_attemptId = ((LearningStoreItemIdentifier)ids[0]).GetKey();
int index = 1;
foreach (Activity a in eNav.Traverse)
{
a.InternalActivityId = ((LearningStoreItemIdentifier)ids[index++]).GetKey();
}
return(eNav);
}
protected void BtnOk_Click(object sender, EventArgs e)
{
// the user clicked the OK button...
// ensure the user is an administrator, then execute the remaining code within a
// LearningStorePrivilegedScope (which grants full access to database views)
if (!SPFarm.Local.CurrentUserIsAdministrator())
{
throw new UnauthorizedAccessException(
"Access is denied. Only adminstrators can access this page.");
}
using (new LearningStorePrivilegedScope())
{
// if the user didn't select an "original instructor", do nothing
if (OriginalInstructor.SelectedValue.Length == 0)
{
return; // real code would display an error message here
}
// if the user didn't enter any "new instructors", do nothing
if (NewInstructors.Accounts.Count == 0)
{
return; // the <NewInstructors> control already displays a validation error
}
// set <originalInstructorId> to the SLK identifier of the selected "original
// instructor"
UserItemIdentifier originalInstructorId = new UserItemIdentifier(
long.Parse(OriginalInstructor.SelectedValue, CultureInfo.InvariantCulture));
// execute the following code within the context of the current SharePoint Web site;
// in fact, the operations below are actually done across the entire site *collection*,
// but SlkStore.GetStore needs to be passed a Web site, so we use the current site
using (SPWeb spWeb = SPControl.GetContextWeb(HttpContext.Current))
{
// set <assignmentIds> to a list containing the IDs of the assignments for which
// <originalInstructorId> is an instructor
List <AssignmentItemIdentifier> assignmentIds = new List <AssignmentItemIdentifier>();
SlkStore slkStore = SlkStore.GetStore(spWeb);
LearningStoreJob job = slkStore.LearningStore.CreateJob();
LearningStoreQuery query = slkStore.LearningStore.CreateQuery("AllAssignmentIds");
query.AddCondition("SPSiteGuid", LearningStoreConditionOperator.Equal,
spWeb.Site.ID);
query.AddCondition("InstructorId", LearningStoreConditionOperator.Equal,
originalInstructorId);
query.AddColumn("AssignmentId");
job.PerformQuery(query);
DataRowCollection rows = job.Execute <DataTable>().Rows;
OriginalInstructor.Items.Add(String.Empty);
foreach (DataRow row in rows)
{
assignmentIds.Add(new AssignmentItemIdentifier(
(LearningStoreItemIdentifier)row["AssignmentId"]));
}
// set <newInstructorIds> to a list of SLK numeric user IDs corresponding to the
// users in the <NewInstructors> control
List <UserItemIdentifier> newInstructorIds = new List <UserItemIdentifier>();
foreach (string loginName in NewInstructors.Accounts)
{
// set <spUser> to the SharePoint SPUser corresponding to <loginName> (which
// was retrieved from the <NewInstructors> control>; quit with an error
// message if that user isn't in "All People" for this site collection
SPUser spUser;
try
{
spUser = spWeb.AllUsers[loginName];
}
catch (SPException)
{
NewInstructors.ErrorMessage = "User isn't in \"All People\": " +
loginName;
return;
}
// set <userKey> to the SLK "user key", which is a string used to identify a
// user in the SLK database; SLK uses a user's security identifier (SID) if
// they have one, or their login name if they don't (e.g. in the case of forms
// authentication)
string userKey = String.IsNullOrEmpty(spUser.Sid)
? spUser.LoginName : spUser.Sid;
// set <userId> to the SLK UserItemIdentifier of the user <spUser> by
// searching the SLK UserItem table; if the user isn't found in UserItem,
// add them
UserItemIdentifier userId;
job = slkStore.LearningStore.CreateJob();
query = slkStore.LearningStore.CreateQuery("UserItem");
query.AddCondition("Key", LearningStoreConditionOperator.Equal, userKey);
query.AddColumn("Id");
job.PerformQuery(query);
rows = job.Execute <DataTable>().Rows;
if (rows.Count != 0)
{
// found user in the SLK UserItem table
userId = new UserItemIdentifier(
(LearningStoreItemIdentifier)rows[0]["Id"]);
}
else
{
// user not found in SLK UserItem table -- add them; we use
// LearningStoreJob.AddOrUpdateItem rather than LearningStoreJob.AddItem
// to account for the rare case where the user may be added simultaneously
// by another process
job = slkStore.LearningStore.CreateJob();
Dictionary <string, object> findProperties = new Dictionary <string, object>();
findProperties["Key"] = userKey;
Dictionary <string, object> setProperties = new Dictionary <string, object>();
setProperties["Name"] = spUser.Name;
job.AddOrUpdateItem("UserItem", findProperties, setProperties, null, true);
userId = new UserItemIdentifier(
job.Execute <LearningStoreItemIdentifier>());
}
// update <newInstructorIds>
newInstructorIds.Add(userId);
}
// add each user in <newInstructorIds> as an instructor to each assignment in
// <assignmentIds>; set <updatedAssignmentCount> to the number of assignments that
// were updated (note that we don't update assignments for which the new
// instructors are already instructors)
Dictionary <UserItemIdentifier, bool> oldInstructors =
new Dictionary <UserItemIdentifier, bool>();
int updatedAssignmentCount = 0;
foreach (AssignmentItemIdentifier assignmentId in assignmentIds)
{
AssignmentProperties assignmentProperties =
slkStore.GetAssignmentProperties(assignmentId, SlkRole.Instructor);
oldInstructors.Clear();
foreach (SlkUser slkUser in assignmentProperties.Instructors)
{
oldInstructors[slkUser.UserId] = true;
}
int oldInstructorCount = oldInstructors.Count;
foreach (UserItemIdentifier userId in newInstructorIds)
{
if (!oldInstructors.ContainsKey(userId))
{
assignmentProperties.Instructors.Add(new SlkUser(userId));
}
}
if (assignmentProperties.Instructors.Count != oldInstructorCount)
{
slkStore.SetAssignmentProperties(assignmentId, assignmentProperties);
updatedAssignmentCount++;
}
}
// provide user feedback
SuccessPanel.Visible = true;
SuccessLabel.Text =
String.Format("Found {0} assignment(s); updated {1} assignment(s).",
assignmentIds.Count, updatedAssignmentCount);
OriginalInstructorSection.Visible = false;
NewInstructorsSection.Visible = false;
ButtonSection.Visible = false;
}
}
}
static void UpdateSlkSettings(string connectionString, Guid spSiteGuid, string settingsFileContents, string defaultSettingsFileContents)
{
// make sure we can access LearningStore; while we're at it, find out if there's a row
// corresponding to this SPSite in the SiteSettingsItem table
LearningStore learningStore = new LearningStore(connectionString, "", true);
LearningStoreJob job = learningStore.CreateJob();
LearningStoreQuery query = learningStore.CreateQuery(Schema.SiteSettingsItem.ItemTypeName);
query.AddColumn(Schema.SiteSettingsItem.SettingsXml);
query.AddCondition(Schema.SiteSettingsItem.SiteGuid, LearningStoreConditionOperator.Equal, spSiteGuid);
job.PerformQuery(query);
DataRowCollection results = job.Execute <DataTable>().Rows;
if (results.Count == 0)
{
// this SPSite isn't listed in the SiteSettingsItem table, so we need to add a row
if (settingsFileContents == null)
{
settingsFileContents = defaultSettingsFileContents;
}
}
else
{
object currentSettingsFileContents = results[0][Schema.SiteSettingsItem.SettingsXml];
if ((currentSettingsFileContents == null) ||
(currentSettingsFileContents is DBNull) ||
(((string)currentSettingsFileContents).Length == 0))
{
// the SLK Settings for this SPSite are missing, so we need to add them
if (settingsFileContents == null)
{
settingsFileContents = defaultSettingsFileContents;
}
}
}
// upload the SLK Settings file if needed
if (settingsFileContents != null)
{
// load "SlkSettings.xsd" from a resource into <xmlSchema>
XmlSchema xmlSchema;
using (StringReader schemaStringReader = new StringReader(SlkCulture.GetDefaultResources().SlkSettingsSchema))
{
xmlSchema = XmlSchema.Read(schemaStringReader,
delegate(object sender2, ValidationEventArgs e2)
{
// ignore warnings (already displayed when SLK Settings file was uploaded)
});
}
// validate <settingsFileContents>
using (StringReader stringReader = new StringReader(settingsFileContents))
{
XmlReaderSettings xmlSettings = new XmlReaderSettings();
xmlSettings.Schemas.Add(xmlSchema);
xmlSettings.ValidationType = ValidationType.Schema;
using (XmlReader xmlReader = XmlReader.Create(stringReader, xmlSettings))
{
try
{
SlkSettings settings = new SlkSettings(xmlReader, DateTime.MinValue);
}
catch (SlkSettingsException ex)
{
throw new SafeToDisplayException(LoadCulture(spSiteGuid).Resources.SlkSettingsFileError, ex.Message);
}
}
}
// store <settingsFileContents> in the database
job = learningStore.CreateJob();
Dictionary <string, object> uniqueProperties = new Dictionary <string, object>();
uniqueProperties.Add(Schema.SiteSettingsItem.SiteGuid, spSiteGuid);
Dictionary <string, object> updateProperties = new Dictionary <string, object>();
updateProperties.Add(Schema.SiteSettingsItem.SettingsXml, settingsFileContents);
updateProperties.Add(Schema.SiteSettingsItem.SettingsXmlLastModified,
DateTime.Now.ToUniversalTime());
job.AddOrUpdateItem(Schema.SiteSettingsItem.ItemTypeName, uniqueProperties,
updateProperties);
job.Execute();
}
}
/// <summary>
/// Requests that information about the current user be retrieved from the
/// LearningStore database. Adds the request to a given
/// <c>LearningStoreJob</c> for later execution.
/// </summary>
///
/// <param name="job">A <c>LearningStoreJob</c> to add the new query to.
/// </param>
///
/// <remarks>
/// After executing this method, and later calling <c>Job.Execute</c>,
/// call <c>GetCurrentUserInfoResults</c> to convert the <c>DataTable</c>
/// returned by <c>Job.Execute</c> into an <c>LStoreUserInfo</c> object.
/// </remarks>
///
private void RequestCurrentUserInfo(LearningStoreJob job)
{
// look up the user in the UserItem table in the database using their
// user key, and set <userId> to the LearningStore numeric identifier
// of the user (i.e. UserItem.Id -- the "Id" column of the UserItem
// table) and <userName> to their full name (e.g. "Karen Berg"); if
// there's no UserItem for the user, add one and set <userId> to its
// ID
LearningStoreQuery query = LStore.CreateQuery(
Schema.Me.ViewName);
query.AddColumn(Schema.Me.UserId);
query.AddColumn(Schema.Me.UserName);
job.PerformQuery(query);
}
protected void Page_Load(object sender, EventArgs e)
{
// set <attemptId> to the ID of this attempt
AttemptItemIdentifier attemptId = new AttemptItemIdentifier(
Convert.ToInt64(Request.QueryString["AttemptId"], CultureInfo.InvariantCulture));
// set <previousEntryId> to the ID of the sequencing log entry that we most recently
// displayed; newer entries than that will be highlighted
long previousEntryId;
string value = Request.QueryString["PreviousEntryId"];
if (value == null)
{
previousEntryId = long.MaxValue;
}
else
{
previousEntryId = Convert.ToInt64(value, CultureInfo.InvariantCulture);
}
try
{
// create a job to execute two queries
LearningStoreJob job = LStore.CreateJob();
// first query: get the package name and organization title of this attempt
LearningStoreQuery query = LStore.CreateQuery(Schema.MyAttemptsAndPackages.ViewName);
query.AddColumn(Schema.MyAttemptsAndPackages.PackageFileName);
query.AddColumn(Schema.MyAttemptsAndPackages.OrganizationTitle);
query.AddCondition(Schema.MyAttemptsAndPackages.AttemptId,
LearningStoreConditionOperator.Equal, attemptId);
job.PerformQuery(query);
// second query: get the contents of the sequencing log for this attempt
query = LStore.CreateQuery(Schema.SequencingLog.ViewName);
query.AddColumn(Schema.SequencingLog.Id);
query.AddColumn(Schema.SequencingLog.Timestamp);
query.AddColumn(Schema.SequencingLog.EventType);
query.AddColumn(Schema.SequencingLog.NavigationCommand);
query.AddColumn(Schema.SequencingLog.Message);
query.SetParameter(Schema.SequencingLog.AttemptId, attemptId);
query.AddSort(Schema.SequencingLog.Id,
LearningStoreSortDirection.Descending);
job.PerformQuery(query);
// execute the job
ReadOnlyCollection <object> results = job.Execute();
DataTable attemptInfoDataTable = (DataTable)results[0];
DataTable sequencingLogDataTable = (DataTable)results[1];
// extract information from the first query into local variables
DataRow attemptInfo = attemptInfoDataTable.Rows[0];
string packageFileName;
LStoreHelper.CastNonNull(attemptInfo[Schema.MyAttemptsAndPackages.PackageFileName],
out packageFileName);
string organizationTitle;
LStoreHelper.CastNonNull(attemptInfo[Schema.MyAttemptsAndPackages.OrganizationTitle],
out organizationTitle);
// set <trainingName> to the name to use for this attempt
string trainingName;
if (organizationTitle.Length == 0)
{
trainingName = packageFileName;
}
else
{
trainingName = String.Format("{0} - {1}", packageFileName, organizationTitle);
}
// copy <trainingName> to the UI
TrainingName.Text = Server.HtmlEncode(trainingName);
// set <maxLogEntryId> to the highest-numbered log entry ID -- which is the first one,
// since they're sorted by descending entry ID
SequencingLogEntryItemIdentifier maxLogEntryId;
if (sequencingLogDataTable.Rows.Count > 0)
{
DataRow dataRow = sequencingLogDataTable.Rows[0];
LStoreHelper.CastNonNull(dataRow[Schema.SequencingLog.Id],
out maxLogEntryId);
}
else
{
maxLogEntryId = new SequencingLogEntryItemIdentifier(-1);
}
// loop once for each item in the sequencing log
foreach (DataRow dataRow in sequencingLogDataTable.Rows)
{
// extract information from <dataRow> into local variables
SequencingLogEntryItemIdentifier logEntryId;
LStoreHelper.CastNonNull(dataRow[Schema.SequencingLog.Id],
out logEntryId);
DateTime?time;
LStoreHelper.Cast(dataRow[Schema.SequencingLog.Timestamp],
out time);
SequencingEventType?eventType;
LStoreHelper.Cast(dataRow[Schema.SequencingLog.EventType],
out eventType);
NavigationCommand?navigationCommand;
LStoreHelper.Cast(dataRow[Schema.SequencingLog.NavigationCommand],
out navigationCommand);
string message;
LStoreHelper.CastNonNull(dataRow[Schema.SequencingLog.Message],
out message);
// begin the HTML table row
TableRow htmlRow = new TableRow();
// highlight this row if it's new since the last refresh
if (logEntryId.GetKey() > previousEntryId)
{
htmlRow.CssClass = "Highlight";
}
// add the "ID" HTML cell
TableCell htmlCell = new TableCell();
htmlCell.CssClass = "Id_";
htmlCell.Wrap = false;
htmlCell.Text = NoBr(Server.HtmlEncode(logEntryId.GetKey().ToString()));
htmlRow.Cells.Add(htmlCell);
// add the "Time" HTML cell
htmlCell = new TableCell();
htmlCell.CssClass = "Time_";
htmlCell.Wrap = false;
htmlCell.Text = NoBr(Server.HtmlEncode(String.Format("{0:d} {0:t}", time)));
htmlRow.Cells.Add(htmlCell);
// add the "EventType" HTML cell
htmlCell = new TableCell();
htmlCell.CssClass = "EventType_";
htmlCell.Wrap = false;
htmlCell.Text = NoBr(Server.HtmlEncode(eventType.ToString()));
htmlRow.Cells.Add(htmlCell);
// add the "NavigationCommand" HTML cell
htmlCell = new TableCell();
htmlCell.CssClass = "NavigationCommand_";
htmlCell.Wrap = false;
htmlCell.Text = NoBr(Server.HtmlEncode(navigationCommand.ToString()));
htmlRow.Cells.Add(htmlCell);
// add the "Message" HTML cell
htmlCell = new TableCell();
htmlCell.CssClass = "Message_";
htmlCell.Wrap = false;
htmlCell.Text = Server.HtmlEncode(message);
htmlRow.Cells.Add(htmlCell);
// end the table HTML row; add it to the HTML table
LogGrid.Rows.Add(htmlRow);
}
// update <RefreshLink>
RefreshLink.NavigateUrl = String.Format("?AttemptId={0}&PreviousEntryId={1}",
attemptId.GetKey(), maxLogEntryId.GetKey());
}
catch (Exception ex)
{
// an unexpected error occurred -- display a generic message that doesn't include the
// exception message (since that message may include sensitive information), and write
// the exception message to the event log
LogTitle.Visible = false;
LogContents.Visible = false;
ErrorMessage.Visible = true;
ErrorMessage.Controls.Add(new System.Web.UI.LiteralControl(
Server.HtmlEncode("A serious error occurred. Please contact your system administrator. More information has been written to the server event log.")));
LogEvent(System.Diagnostics.EventLogEntryType.Error,
"An exception occurred while accessing the sequencing log for attempt #{0}:\n\n{1}\n\n",
attemptId.GetKey(), ex.ToString());
}
}
protected void DeletePackagesButton_Click(object sender, EventArgs e)
{
// the user clicked "Upload"...
// hide the confirmation panel
ConfirmMessage.Visible = false;
// the PackagesToDelete hidden form element contains a comma-delimited list of IDs of
// packages to delete (copied from <dialogArguments> on the client) -- attempt to delete
// those packages, and set <deleted> to the IDs of packages successfully deleted
List <string> deleted = new List <string>();
try
{
// loop once for each package to delete
foreach (string id in PackagesToDelete.Value.Split(','))
{
// set <packageId> to the ID of this package
PackageItemIdentifier packageId = new PackageItemIdentifier(
Convert.ToInt64(id, CultureInfo.InvariantCulture));
// before we delete the package, we need to delete all attempts on the package --
// the following query looks for those attempts
LearningStoreJob job = LStore.CreateJob();
LearningStoreQuery query = LStore.CreateQuery(
Schema.MyAttemptsAndPackages.ViewName);
query.AddCondition(Schema.MyAttemptsAndPackages.PackageId,
LearningStoreConditionOperator.Equal, packageId);
query.AddCondition(Schema.MyAttemptsAndPackages.AttemptId,
LearningStoreConditionOperator.NotEqual, null);
query.AddColumn(Schema.MyAttemptsAndPackages.AttemptId);
query.AddSort(Schema.MyAttemptsAndPackages.AttemptId,
LearningStoreSortDirection.Ascending);
job.PerformQuery(query);
DataTable dataTable = job.Execute <DataTable>();
AttemptItemIdentifier previousAttemptId = null;
// loop once for each attempt on this package
foreach (DataRow dataRow in dataTable.Rows)
{
// set <attemptId> to the ID of this attempt
AttemptItemIdentifier attemptId;
LStoreHelper.CastNonNull(dataRow["AttemptId"], out attemptId);
// if <attemptId> is a duplicate attempt ID, skip it; note that the query
// results are sorted by attempt ID (see above)
if ((previousAttemptId != null) &&
(previousAttemptId.GetKey() == attemptId.GetKey()))
{
continue;
}
// delete this attempt
StoredLearningSession.DeleteAttempt(LStore, attemptId);
// continue to the next attempt
previousAttemptId = attemptId;
}
// delete the package
PStore.DeletePackage(packageId);
// add the package ID to the list of deleted packages
deleted.Add(id);
}
// the operation was successful, and there are no messages to
// display to the user, so close the dialog
CloseDialogScript.Visible = true;
}
catch (Exception ex)
{
// an unexpected error occurred -- display a generic message that
// doesn't include the exception message (since that message may
// include sensitive information), and write the exception message
// to the event log
ErrorIntro.Visible = true;
ErrorMessage.Visible = true;
ErrorMessage.Controls.Add(new System.Web.UI.LiteralControl(
Server.HtmlEncode("A serious error occurred. Please contact your system administrator. More information has been written to the server event log.")));
LogEvent(System.Diagnostics.EventLogEntryType.Error,
"An exception occurred while deleting a package:\n\n{0}\n\n", ex.ToString());
}
// update the buttons
DeletePackagesButton.Visible = false;
CloseButton.Text = "OK";
// set the hidden form element PackagesSuccessfullyDeleted to a
// comma-separated list of IDs of packages that were successfully
// deleted, and enable the client-side script that communicates this
// information to the parent page
PackagesSuccessfullyDeleted.Value = String.Join(",", deleted.ToArray());
UpdateParentPageScript.Visible = true;
}
/// <summary>
/// Affects <paramref name="job"/> object by adding query for all columns from <see cref="AllAttemptsColumns"/>
/// and adding conditions specified in <paramref name="conditions"/>
/// </summary>
/// <param name="job"><see cref="LearningStoreJob"/> object to add query to.</param>
/// <param name="conditions"><see cref="List{T}"/> of <see cref="QueryCondition"/> objects to add conditions to query.</param>
protected void RequestAttemptResults(LearningStoreJob job, List<QueryCondition> conditions)
{
LearningStoreQuery query = this.LStore.CreateQuery(Schema.AllAttemptsResults.ViewName);
// Select all columns except those mentioned in conditions.
var queryColumns = AllAttemptsColumns.Except(conditions.Select(cond => cond.ColumnName)).ToList();
// add columns to query
foreach (var queryColumn in queryColumns)
{
query.AddColumn(queryColumn);
}
// add conditions to query
foreach (var condition in conditions)
{
query.AddCondition(condition.ColumnName, condition.ConditionOperator, condition.Value);
}
job.PerformQuery(query);
}