/// <summary>
/// Retrieves a path to an timeline, exhibit or content item.
/// </summary>
public string GetContentPath(Guid collectionId, Guid?contentId, string title)
{
string contentPath = string.Empty;
ContentItemRaw contentItem = Database.SqlQuery <ContentItemRaw>("SELECT * FROM ContentItems WHERE Collection_Id = {0} AND (Id = {1} OR Title = {2})", collectionId, contentId, title).FirstOrDefault();
if (contentItem != null)
{
contentPath = "/" + contentItem.Id;
contentId = contentItem.Exhibit_ID;
}
ExhibitRaw exhibit = Database.SqlQuery <ExhibitRaw>("SELECT * FROM Exhibits WHERE Collection_Id = {0} AND (Id = {1} OR Title = {2})", collectionId, contentId, title).FirstOrDefault();
if (exhibit != null)
{
contentPath = "/e" + exhibit.Id + contentPath;
contentId = exhibit.Timeline_ID;
}
TimelineRaw timeline = Database.SqlQuery <TimelineRaw>("SELECT * FROM Timelines WHERE Collection_Id = {0} AND (Id = {1} OR Title = {2})", collectionId, contentId, title).FirstOrDefault();
while (timeline != null)
{
contentPath = "/t" + timeline.Id + contentPath;
timeline = Database.SqlQuery <TimelineRaw>("SELECT * FROM Timelines WHERE Id = {0}", timeline.Timeline_ID).FirstOrDefault();
}
return(contentPath.ToString());
}
// Returns list of ids of child content items of exhibit with given id.
private List <Guid> GetChildContentItemsIds(Guid id)
{
var contentItems = new List <Guid>();
// Find child content items
string contentItemsQuery = string.Format(
CultureInfo.InvariantCulture,
"SELECT * FROM ContentItems WHERE Exhibit_Id IN ('{0}')",
string.Join("', '", id));
IEnumerable <ContentItemRaw> contentItemsRaw = new ContentItemRaw[0];
try
{
retryPolicy.ExecuteAction(
() =>
{
contentItemsRaw = Database.SqlQuery <ContentItemRaw>(contentItemsQuery);
});
}
catch (Exception e)
{
throw e;
}
foreach (ContentItemRaw contentItemRaw in contentItemsRaw)
{
contentItems.Add(contentItemRaw.Id);
}
return(contentItems);
}
// Returns list of ids of child content items of exhibit with given id.
private List<Guid> GetChildContentItemsIds(Guid id)
{
var contentItems = new List<Guid>();
// Find child content items
string contentItemsQuery = string.Format(
CultureInfo.InvariantCulture,
"SELECT * FROM ContentItems WHERE Exhibit_Id IN ('{0}')",
string.Join("', '", id));
IEnumerable<ContentItemRaw> contentItemsRaw = new ContentItemRaw[0];
try
{
retryPolicy.ExecuteAction(
() =>
{
contentItemsRaw = Database.SqlQuery<ContentItemRaw>(contentItemsQuery);
});
}
catch (Exception e)
{
throw e;
}
foreach (ContentItemRaw contentItemRaw in contentItemsRaw)
contentItems.Add(contentItemRaw.Id);
return contentItems;
}
private void FillTimelineRelations(Dictionary<Guid, Timeline> timelinesMap, int maxElements)
{
if (!timelinesMap.Keys.Any())
return;
// Populate Exhibits
string exhibitsQuery = string.Format(
CultureInfo.InvariantCulture,
"SELECT TOP({0}) *, Year as [Time] FROM Exhibits WHERE Timeline_Id IN ('{1}')",
maxElements,
string.Join("', '", timelinesMap.Keys.ToArray()));
IEnumerable<ExhibitRaw> exhibitsRaw = new ExhibitRaw[0];
try
{
retryPolicy.ExecuteAction(
() =>
{
exhibitsRaw = Database.SqlQuery<ExhibitRaw>(exhibitsQuery);
});
}
catch (Exception e)
{
throw e;
}
Dictionary<Guid, Exhibit> exhibits = new Dictionary<Guid, Exhibit>();
foreach (ExhibitRaw exhibitRaw in exhibitsRaw)
{
if (exhibitRaw.ContentItems == null)
exhibitRaw.ContentItems = new Collection<ContentItem>();
if (timelinesMap.Keys.Contains(exhibitRaw.Timeline_ID))
{
timelinesMap[exhibitRaw.Timeline_ID].Exhibits.Add(exhibitRaw);
exhibits[exhibitRaw.Id] = exhibitRaw;
}
}
if (exhibits.Keys.Any())
{
// Populate Content Items
string contentItemsQuery = string.Format(
CultureInfo.InvariantCulture,
@"
SELECT *
FROM ContentItems
WHERE Exhibit_Id IN ('{0}')
ORDER BY [Order] ASC
",
string.Join("', '", exhibits.Keys.ToArray()));
IEnumerable<ContentItemRaw> contentItemsRaw = new ContentItemRaw[0];
try
{
retryPolicy.ExecuteAction(
() =>
{
contentItemsRaw = Database.SqlQuery<ContentItemRaw>(contentItemsQuery);
});
}
catch (Exception e)
{
throw e;
}
foreach (ContentItemRaw contentItemRaw in contentItemsRaw)
{
if (exhibits.Keys.Contains(contentItemRaw.Exhibit_ID))
{
exhibits[contentItemRaw.Exhibit_ID].ContentItems.Add(contentItemRaw);
}
}
}
}
/// <summary>
/// Retrieves a path to an timeline, exhibit or content item.
/// </summary>
public string GetContentPath(Guid collectionId, Guid?contentId, string title)
{
string contentPath = string.Empty;
ContentItemRaw contentItem = null;
try
{
retryPolicy.ExecuteAction(
() =>
{
contentItem = Database.SqlQuery <ContentItemRaw>("SELECT * FROM ContentItems WHERE Collection_Id = {0} AND (Id = {1} OR Title = {2})", collectionId, contentId, title).FirstOrDefault();
});
}
catch (Exception e)
{
throw e;
}
if (contentItem != null)
{
contentPath = "/" + contentItem.Id;
contentId = contentItem.Exhibit_ID;
}
ExhibitRaw exhibit = null;
try
{
retryPolicy.ExecuteAction(
() =>
{
exhibit = Database.SqlQuery <ExhibitRaw>("SELECT * FROM Exhibits WHERE Collection_Id = {0} AND (Id = {1} OR Title = {2})", collectionId, contentId, title).FirstOrDefault();
});
}
catch (Exception e)
{
throw e;
}
if (exhibit != null)
{
contentPath = "/e" + exhibit.Id + contentPath;
contentId = exhibit.Timeline_ID;
}
TimelineRaw timeline = null;
try
{
retryPolicy.ExecuteAction(
() =>
{
timeline = Database.SqlQuery <TimelineRaw>("SELECT * FROM Timelines WHERE Collection_Id = {0} AND (Id = {1} OR Title = {2})", collectionId, contentId, title).FirstOrDefault();
});
}
catch (Exception e)
{
throw e;
}
while (timeline != null)
{
contentPath = "/t" + timeline.Id + contentPath;
try
{
retryPolicy.ExecuteAction(
() =>
{
timeline = Database.SqlQuery <TimelineRaw>("SELECT * FROM Timelines WHERE Id = {0}", timeline.Timeline_ID).FirstOrDefault();
});
}
catch (Exception e)
{
throw e;
}
}
return(contentPath.ToString(CultureInfo.InvariantCulture));
}
private void FillTimelineRelations(Dictionary <Guid, Timeline> timelinesMap, int maxElements)
{
if (!timelinesMap.Keys.Any())
{
return;
}
// Populate Exhibits
string exhibitsQuery = string.Format(
CultureInfo.InvariantCulture,
"SELECT TOP({0}) *, Year as [Time] FROM Exhibits WHERE Timeline_Id IN ('{1}')",
maxElements,
string.Join("', '", timelinesMap.Keys.ToArray()));
IEnumerable <ExhibitRaw> exhibitsRaw = new ExhibitRaw[0];
try
{
retryPolicy.ExecuteAction(
() =>
{
exhibitsRaw = Database.SqlQuery <ExhibitRaw>(exhibitsQuery);
});
}
catch (Exception e)
{
throw e;
}
Dictionary <Guid, Exhibit> exhibits = new Dictionary <Guid, Exhibit>();
foreach (ExhibitRaw exhibitRaw in exhibitsRaw)
{
if (exhibitRaw.ContentItems == null)
{
exhibitRaw.ContentItems = new Collection <ContentItem>();
}
if (timelinesMap.Keys.Contains(exhibitRaw.Timeline_ID))
{
timelinesMap[exhibitRaw.Timeline_ID].Exhibits.Add(exhibitRaw);
exhibits[exhibitRaw.Id] = exhibitRaw;
}
}
if (exhibits.Keys.Any())
{
// Populate Content Items
string contentItemsQuery = string.Format(
CultureInfo.InvariantCulture,
@"
SELECT *
FROM ContentItems
WHERE Exhibit_Id IN ('{0}')
ORDER BY [Order] ASC
",
string.Join("', '", exhibits.Keys.ToArray()));
IEnumerable <ContentItemRaw> contentItemsRaw = new ContentItemRaw[0];
try
{
retryPolicy.ExecuteAction(
() =>
{
contentItemsRaw = Database.SqlQuery <ContentItemRaw>(contentItemsQuery);
});
}
catch (Exception e)
{
throw e;
}
foreach (ContentItemRaw contentItemRaw in contentItemsRaw)
{
if (exhibits.Keys.Contains(contentItemRaw.Exhibit_ID))
{
exhibits[contentItemRaw.Exhibit_ID].ContentItems.Add(contentItemRaw);
}
}
}
}
