/// <summary>
///
/// </summary>
public ListFilterKeyPhrases(ListTypeBase ListType, List<Phrase> keyphrases )
: base(ListType)
{
if (keyphrases.Count == 0)
{
//No Keyphrases - nothing to do.
return;
}
// Get select table from list type class
TableEntry SelectTable = ListType.GetSelectTable();
if (ListType.GetListTypeName().ToUpper() == "ARTICLES" )
{
//Create an Inner Join for each key phrase in the filter - the filter specifies and AND search
TableEntry ArticleKeyPhrasesTable = new TableEntry("ArticleKeyPhrases", "akp");
for ( int i = 0; i < keyphrases.Count; ++i )
{
if (i == 0)
{
//Article Key Phrases
InnerJoinEntry ije = new InnerJoinEntry(ArticleKeyPhrasesTable);
ije.Conditions.Add(new ConditionEntry(ArticleKeyPhrasesTable.MakeField("entryID"), "=", SelectTable.MakeField("entryID")));
//Additional Filter on SiteId to assist query performance
ije.Conditions.Add(new ConditionEntry(ArticleKeyPhrasesTable.MakeField("siteID"),"=", "@siteID"));
InnerJoins.Add(ije);
}
else
{
//Join on Article Key Phrases for each phrase.
TableEntry ArticleKeyPhrasesTable1 = new TableEntry("ArticleKeyPhrases", ArticleKeyPhrasesTable.Name + i);
InnerJoins.Add(new InnerJoinEntry(ArticleKeyPhrasesTable1,
new ConditionEntry(ArticleKeyPhrasesTable1.MakeField("entryID"), "=", ArticleKeyPhrasesTable.MakeField("entryID"))));
ArticleKeyPhrasesTable = ArticleKeyPhrasesTable1;
}
// Filter article including on each of the given key phrases
TableEntry phraseNameSpacesTable = new TableEntry("PhraseNameSpaces", "PhraseNameSpaces" + i);
TableEntry KeyPhrasesTable = new TableEntry("KeyPhrases", "KeyPhrases" + i);
//Inner join on phrasenamespaces.
InnerJoinEntry pn = new InnerJoinEntry(phraseNameSpacesTable);
pn.Conditions.Add(new ConditionEntry(phraseNameSpacesTable.MakeField("phrasenamespaceid"), "=", ArticleKeyPhrasesTable.MakeField("phrasenamespaceID")));
InnerJoins.Add(pn);
Phrase phraseFilter = keyphrases[i];
//Inner join on key phrases
InnerJoinEntry kp = new InnerJoinEntry(KeyPhrasesTable);
kp.Conditions.Add(new ConditionEntry(KeyPhrasesTable.MakeField("phraseID"), "=", phraseNameSpacesTable.MakeField("phraseID")));
kp.Conditions.Add(new ConditionEntry(KeyPhrasesTable.MakeField("phrase"), "=", "'" + phraseFilter.PhraseName + "'"));
InnerJoins.Add(kp);
//Inner Join on NameSpaces if a namespace has been specified.
if (phraseFilter.NameSpace != string.Empty)
{
TableEntry nameSpacesTable = new TableEntry("NameSpaces", "NameSpaces" + i);
InnerJoinEntry n = new InnerJoinEntry(nameSpacesTable);
n.Conditions.Add(new ConditionEntry(nameSpacesTable.MakeField("namespaceid"), "=", phraseNameSpacesTable.MakeField("namespaceID")));
n.Conditions.Add(new ConditionEntry(nameSpacesTable.MakeField("name"), "=", "'" + phraseFilter.NameSpace + "'"));
InnerJoins.Add(n);
}
}
}
else if (ListType.GetListTypeName().ToUpper() == "THREADS")
{
//Create an Inner Join for each key phrase in the filter - the filter specifies and AND search
TableEntry ThreadKeyPhrasesTable = new TableEntry("ThreadKeyPhrases", "tkp");
for (int i = 0; i < keyphrases.Count; i++)
{
//Do and Inner Join to get all the phrases for the given threadId
if (i == 0)
{
InnerJoinEntry ije = new InnerJoinEntry(ThreadKeyPhrasesTable);
ije.Conditions.Add( new ConditionEntry(ThreadKeyPhrasesTable.MakeField("ThreadID"), "=", SelectTable.MakeField("ThreadID")));
InnerJoins.Add(ije);
}
else
{
TableEntry ThreadKeyPhrasesTable1 = new TableEntry("ThreadKeyPhrases", ThreadKeyPhrasesTable.Name + i);
InnerJoins.Add(new InnerJoinEntry(ThreadKeyPhrasesTable1,
new ConditionEntry(ThreadKeyPhrasesTable1.MakeField("ThreadID"), "=", ThreadKeyPhrasesTable.MakeField("ThreadID"))));
ThreadKeyPhrasesTable = ThreadKeyPhrasesTable1;
}
// Filter thread including on each of the given key phrases
TableEntry KeyPhrasesTable = new TableEntry("KeyPhrases", "KeyPhrases" + i);
InnerJoinEntry ije2 = new InnerJoinEntry(KeyPhrasesTable);
ije2.Conditions.Add(new ConditionEntry(KeyPhrasesTable.MakeField("phraseID"), "=", ThreadKeyPhrasesTable.MakeField("phraseID")));
ije2.Conditions.Add(new ConditionEntry(KeyPhrasesTable.MakeField("phrase"), "=", "'" + keyphrases[i].PhraseName + "'" ));
InnerJoins.Add(ije2);
}
}
else
{
// not supported
return;
}
}
/// <summary>
///
/// </summary>
/// <param name="Table"></param>
/// <param name="FieldName"></param>
public FieldEntry(TableEntry Table, string FieldName)
{
this.Table = Table;
this.FieldName = FieldName;
op = null;
}
/// <summary>
///
/// </summary>
public ListFilterVoteCount(ListTypeBase ListType, int VoteCount) : base(ListType)
{
if(!IsSupported())
{
return;
}
TableEntry SelectTable = ListType.GetSelectTable();
// Construct inner join
// INNER JOIN PageVotes v ON v.ItemType = 1 AND v.ItemID = g.h2g2id
TableEntry PageVotesTable = new TableEntry("PageVotes", "v");
InnerJoinEntry ije = new InnerJoinEntry(PageVotesTable);
ije.Conditions.Add(new ConditionEntry(PageVotesTable.MakeField("ItemType"), "=", "1"));
ije.Conditions.Add(new ConditionEntry(PageVotesTable.MakeField("ItemID"), "=", SelectTable.MakeField("h2g2id")));
InnerJoins.Add(ije);
WhereConditions.Add(new ConditionEntry(PageVotesTable.MakeField("VoteCount"), ">", System.Convert.ToString(VoteCount)));
// Select fields
ListType.AddField(PageVotesTable, "VoteCount", "item/poll-list/poll/statistics/@votecount");
}
/// <summary>
///
/// </summary>
public ListFilterRating(ListTypeBase ListType, double AverageRating) : base(ListType)
{
if(!IsSupported()) return;
// Get select table from list type class
TableEntry SelectTable = ListType.GetSelectTable();
// Construct inner join
// INNER JOIN PageVotes v ON v.ItemType = 1 AND v.ItemID = g.h2g2id
TableEntry PageVotesTable = new TableEntry("PageVotes", "v");
InnerJoinEntry ije = new InnerJoinEntry(PageVotesTable);
ije.Conditions.Add(new ConditionEntry(PageVotesTable.MakeField("ItemType"), "=", "1"));
ije.Conditions.Add(new ConditionEntry(PageVotesTable.MakeField("ItemID"), "=", SelectTable.MakeField("h2g2id")));
InnerJoins.Add(ije);
// Construct condition WHERE Guide for ARTICLES type of list
WhereConditions.Add(new ConditionEntry(PageVotesTable.MakeField("averagerating"), ">", System.Convert.ToString(AverageRating)));
// Select fields
ListType.AddField(PageVotesTable, "averagerating", "item/poll-list/poll/statistics/@averagerating");
}
/// <summary>
///
/// </summary>
public ListTypeUsers()
{
UsersTable = new TableEntry("Users", "u");
// Add fields
AddFieldDateCreated(new FieldEntry(UsersTable, "DateJoined"));
AddFieldID(new FieldEntry(UsersTable, "UserID"));
AddFieldTitle(new FieldEntry(UsersTable, "username"));
AddField("userid", "item/user-item/@userid");
AddField("userid", "item/user-item/userid");
AddField("UserName", "item/user-item/username");
AddField("FirstNames", "item/user-item/firstnames");
AddField("LastName", "item/user-item/lastname");
AddField("area", "item/user-item/lastname");
AddField("status", "item/user-item/status");
AddField("taxonomynode", "item/user-item/taxonomynode");
AddField("journal", "item/user-item/journal");
AddField("active", "item/user-item/active");
}
/// <summary>
///
/// </summary>
public ListTypeTopicForums()
{
TopicsTable = new TableEntry("Topics", "t");
GuideEntriesTable = new TableEntry("GuideEntries", "g");
ForumsTable = new TableEntry("Forums", "f");
// Add fields
AddFieldDateUpdated(new FieldEntry(ForumsTable, "LastPosted"));
AddFieldID(new FieldEntry(TopicsTable, "TopicID"));
AddFieldTitle(new FieldEntry(GuideEntriesTable, "Subject"));
AddField("TopicID", "item/topic-item/@topicid");
AddField(ForumsTable, "ForumPostCount", "item/topic-item/@forumpostcount");
AddField(GuideEntriesTable, "forumid", "item/topic-item/@forumid");
AddField(GuideEntriesTable, "subject", "item/topic-item/subject");
AddField(ForumsTable, "LastPosted", "item/topic-item/date-lastposted");
}
/// <summary>
///
/// </summary>
public OrderByDateCreated(ListTypeBase ListType) : base(ListType)
{
if(!IsSupported())
{
return;
}
if(ListType.GetListTypeName() == "ARTICLES"
|| ListType.GetListTypeName() == "CLUBS"
|| ListType.GetListTypeName() == "THREADS"
|| ListType.GetListTypeName() == "FORUMS"
|| ListType.GetListTypeName() == "CAMPAIGNDIARYENTRIES")
{
OrderByField = ListType.GetSelectTable().MakeField("DateCreated");
}
else if(ListType.GetListTypeName() == "TOPICFORUMS")
{
OrderByField = ListType.GetSelectTable().MakeField("CreatedDate");
}
else if(ListType.GetListTypeName() == "CATEGORIES")
{
// join on guideentries
TableEntry GuideEntriesTable = new TableEntry("Guideentries", "g");
InnerJoins.Add(new InnerJoinEntry(GuideEntriesTable,
new ConditionEntry(GuideEntriesTable.MakeField("h2g2id"), "=", ListType.GetSelectTable().MakeField("h2g2id"))));
OrderByField = GuideEntriesTable.MakeField("DateCreated");
}
else if(ListType.GetListTypeName() == "USERS")
{
// datejoined
OrderByField = ListType.GetSelectTable().MakeField("DateJoined");
}
ListType.AddField(OrderByField, "item/date-created");
}
/// <summary>
///
/// </summary>
public InnerJoinEntry(TableEntry JoinTable)
{
this.JoinTable = JoinTable;
Conditions = new ArrayList();
}
/// <summary>
///
/// </summary>
public OrderByCommentForumPostCount(ListTypeBase ListType, string dateCreated )
: base(ListType)
{
if (!IsSupported())
{
return;
}
TableEntry SelectTable = ListType.GetSelectTable();
// Need to Inner Join computed BookMark Count table in order to order by it.
String subSelect = "( SELECT cf.forumid, COUNT(te.entryid) 'commentforumpostcount' FROM CommentForums cf INNER JOIN Forums f ON f.ForumId = cf.ForumId INNER JOIN Threads t ON t.forumid = f.forumid INNER JOIN ThreadEntries te ON te.threadid = t.threadid";
string whereClause = string.Empty;
if ( dateCreated != null && dateCreated != string.Empty )
{
subSelect += " WHERE te.DatePosted > DATEADD(day,-" + dateCreated + ",getdate())";
}
subSelect += " GROUP BY cf.ForumId )";
TableEntry countsTable = new TableEntry(subSelect, "cfcount");
countsTable.IsSubSelect = true;
InnerJoinEntry ije = new InnerJoinEntry(countsTable);
ije.Conditions.Add(new ConditionEntry(countsTable.MakeField("ForumId"), "=", SelectTable.MakeField("forumid")));
InnerJoins.Add(ije);
OrderByField = countsTable.MakeField("commentforumpostcount");
// Select fields
ListType.AddField(OrderByField, "item/commentforum-item/commentforumpostcount");
}
/// <summary>
///
/// </summary>
public OrderByVoteCount(ListTypeBase ListType) : base(ListType)
{
if(!IsSupported())
{
return;
}
// INNER JOIN PageVotes v ON v.ItemType = 1 AND v.ItemID = g.h2g2id
TableEntry PageVotesTable = new TableEntry("PageVotes", "v");
InnerJoinEntry ije = new InnerJoinEntry(PageVotesTable);
ije.Conditions.Add(new ConditionEntry(PageVotesTable.MakeField("ItemType"), "=", "1"));
ije.Conditions.Add(new ConditionEntry(PageVotesTable.MakeField("ItemID"), "=", ListType.GetSelectTable().MakeField("h2g2id")));
InnerJoins.Add(ije);
OrderByField = PageVotesTable.MakeField("VoteCount");
ListType.AddField(OrderByField, "item/poll-list/poll/statistics/@votecount");
}
/// <summary>
///
/// </summary>
public OrderByBookmarkCount(ListTypeBase ListType)
: base(ListType)
{
if (!IsSupported())
{
return;
}
TableEntry SelectTable = ListType.GetSelectTable();
// Need to Inner Join computed BookMark Count table in order to order by it.
String subSelect = "( Select DestinationId, Count(destinationid) 'bookmarkcount' FROM Links GROUP BY DestinationId )";
TableEntry LinksTable = new TableEntry(subSelect, "l");
LinksTable.IsSubSelect = true;
InnerJoinEntry ije = new InnerJoinEntry(LinksTable);
ije.Conditions.Add(new ConditionEntry(LinksTable.MakeField("DestinationId"), "=", SelectTable.MakeField("h2g2id")));
InnerJoins.Add(ije);
OrderByField = LinksTable.MakeField("bookmarkcount");
// Select fields
ListType.AddField(OrderByField, "item/bookmarkcount");
}
/// <summary>
///
/// </summary>
public ListFilterArticleDate(ListTypeBase ListType, DateTime startdate, DateTime enddate )
: base(ListType)
{
if (!IsSupported()) return;
// Get select table from list type class
TableEntry SelectTable = ListType.GetSelectTable();
// Only pull out articles where their date range falls within date range given.
TableEntry ArticleDateRange = new TableEntry("ArticleDateRange", "ad");
InnerJoinEntry ije = new InnerJoinEntry(ArticleDateRange);
ije.Conditions.Add(new ConditionEntry(ArticleDateRange.MakeField("entryID"), "=", SelectTable.MakeField("entryID")));
ije.Conditions.Add(new ConditionEntry(ArticleDateRange.MakeField("startdate"), ">", "'" + startdate.ToString("yyyy-MM-dd HH:mm:ss") + "'"));
ije.Conditions.Add(new ConditionEntry(ArticleDateRange.MakeField("enddate"), "<=", "'" + enddate.ToString("yyyy-MM-dd HH:mm:ss") + "'"));
InnerJoins.Add(ije);
ListType.AddField(ArticleDateRange.MakeField("startdate"), "item/date-articlestartdate");
ListType.AddField(ArticleDateRange.MakeField("enddate"),"item/date-articleenddate");
}
/// <summary>
///
/// </summary>
public FieldEntry(TableEntry TableLHS, string FieldNameLHS, string condition, TableEntry TableRHS, string FieldNameRHS)
{
this.Table = TableLHS;
this.FieldName = FieldNameLHS;
op = condition;
this.TableRHS = TableRHS;
this.FieldNameRHS = FieldNameRHS;
}
/// <summary>
///
/// </summary>
public ListTypeThreads()
{
ThreadTable = new TableEntry("Threads", "t");
ThreadEntriesTable = new TableEntry("ThreadEntries", "te");
UsersTable = new TableEntry("Users", "u");
ForumsTable = new TableEntry("Forums", "f");
// Add fields
AddFieldID(new FieldEntry(ThreadTable, "threadid"));
AddFieldTitle(new FieldEntry(ThreadTable, "FirstSubject"));
AddFieldDateCreated(new FieldEntry(ThreadTable, "DateCreated"));
AddFieldDateUpdated(new FieldEntry(ThreadTable, "LastPosted"));
AddFieldCreator(UsersTable);
AddField("ThreadID", "item/thread-item/@threadid");
AddField("ForumID", "item/thread-item/@forumid");
AddField("FirstSubject", "item/thread-item/firstsubject");
AddField("LastPosted", "item/thread-item/date-lastposted");
AddField(ThreadEntriesTable, "text", "item/thread-item/text");
AddFieldAuthor();
}
/// <summary>
///
/// </summary>
public OrderByDateUpdated(ListTypeBase ListType) : base(ListType)
{
if(!IsSupported())
{
return;
}
if(ListType.GetListTypeName() == "CLUBS")
{
TableEntry GuideEntriesTable = new TableEntry("GuideEntries", "g");
OrderByField
= new FieldEntry(ListType.GetSelectTable(), "LastUpdated", ">", GuideEntriesTable, "LastUpdated");
}
else if(ListType.GetListTypeName() == "CAMPAIGNDIARYENTRIES"
|| ListType.GetListTypeName() == "THREADS")
{
OrderByField = ListType.GetSelectTable().MakeField("LastPosted");
}
else
{
OrderByField = ListType.GetSelectTable().MakeField("LastUpdated");
}
ListType.AddField(OrderByField, "item/date-updated");
}
/// <summary>
///
/// </summary>
public ListTypeCommentForums()
{
CommentForumsTable = new TableEntry("CommentForums", "cf");
ForumsTable = new TableEntry("Forums", "f");
AddField(CommentForumsTable, "forumid", "item/commentforum-item/forumid");
AddField(CommentForumsTable, "uid", "item/commentforum-item/uid");
AddField(CommentForumsTable, "url", "item/commentforum-item/url");
AddField(ForumsTable, "title", "item/commentforum-item/title");
}
/// <summary>
///
/// </summary>
public OrderBySignificance(ListTypeBase ListType) : base(ListType)
{
TableEntry ContentSignifTable;
TableEntry JoinTable;
string IDField;
// Choose ContentSignif table and id field
if(ListType.GetListTypeName() == "ARTICLES")
{
JoinTable = ListType.GetSelectTable();
ContentSignifTable = new TableEntry("ContentSignifArticle", "csa");
IDField = "h2g2id";
}
else if (ListType.GetListTypeName() == "CLUBS")
{
JoinTable = ListType.GetSelectTable();
ContentSignifTable = new TableEntry("ContentSignifClub", "csc");
IDField = "Clubid";
}
else if (ListType.GetListTypeName() == "USERS")
{
JoinTable = ListType.GetSelectTable();
ContentSignifTable = new TableEntry("ContentSignifUser", "csu");
IDField = "Userid";
}
else if (ListType.GetListTypeName() == "THREADS")
{
JoinTable = ListType.GetSelectTable();
ContentSignifTable = new TableEntry("ContentSignifThread", "csc");
IDField = "ThreadID";
}
else if (ListType.GetListTypeName() == "CATEGORIES")
{
JoinTable = ListType.GetSelectTable();
ContentSignifTable = new TableEntry("ContentSignifNode", "csn");
IDField = "NodeID";
}
else if (ListType.GetListTypeName() == "FORUMS")
{
JoinTable = ListType.GetSelectTable();
ContentSignifTable = new TableEntry("ContentSignifForum", "csf");
IDField = "ForumID";
}
else if (ListType.GetListTypeName() == "TOPICFORUMS")
{
// Get forum
// inner join guideentries g on g.h2g2id=t.h2g2id
TableEntry GuideEntriesTable = new TableEntry("GuideEntries", "g");
InnerJoinEntry ije1 = new InnerJoinEntry(GuideEntriesTable);
ije1.Conditions.Add(new ConditionEntry(GuideEntriesTable.MakeField("h2g2id"), "=", ListType.GetSelectTable().MakeField("h2g2id")));
InnerJoins.Add(ije1);
// inner join forums f on f.forumid=g.forumid
TableEntry ForumsTable = new TableEntry("Forums", "f");
InnerJoinEntry ije2 = new InnerJoinEntry(ForumsTable);
ije2.Conditions.Add(new ConditionEntry(ForumsTable.MakeField("forumid"), "=", GuideEntriesTable.MakeField("forumid")));
InnerJoins.Add(ije2);
ContentSignifTable = new TableEntry("ContentSignifForum", "csf");
IDField = "ForumID";
JoinTable = ForumsTable;
}
else
{
// SHould not come here
return;
}
// INNER JOIN ContentSignifXXXXX csx on csx.[id] = selecttable.[id]
InnerJoinEntry ije = new InnerJoinEntry(ContentSignifTable);
ije.Conditions.Add(new ConditionEntry(ContentSignifTable.MakeField(IDField), "=", JoinTable.MakeField(IDField)));
InnerJoins.Add(ije);
OrderByField = ContentSignifTable.MakeField("Score");
ListType.AddField(OrderByField, "item/score");
}
/// <summary>
///
/// </summary>
public ListTypeCategories()
{
SelectTable = new TableEntry("Hierarchy", "ltcHierarchy");
// Add fields
AddField("NodeID", "item/@itemid");
AddField("DisplayName", "item/title");
AddField("LastUpdated", "item/date-updated");
AddField("NodeID", "item/category-item/@nodeid");
AddField("NodeMembers", "item/category-item/@nodemembercount");
AddField("DisplayName", "item/category-item/displayname");
}
/// <summary>
/// Adds creator block
/// </summary>
/// <param name="UsersTable">Table from where to pull user fields</param>
/// <returns>Field alias of IDField, or null if failed</returns>
public string AddFieldCreator(TableEntry UsersTable)
{
string IDFieldAlias;
if((IDFieldAlias = AddField(UsersTable, "UserID", "item/creator/user/userid")) == null)
{
return null;
}
// If any of these fail, function will still succeed, but userblock will
// be incomplete
AddField(UsersTable, "username", "item/creator/user/username");
AddField(UsersTable, "firstnames", "item/creator/user/firstnames");
AddField(UsersTable, "lastname", "item/creator/user/lastname");
AddField(UsersTable, "area", "item/creator/user/area");
AddField(UsersTable, "status", "item/creator/user/status");
AddField(UsersTable, "taxonomynode", "item/creator/user/taxonomynode");
AddField(UsersTable, "journal", "item/creator/user/journal");
AddField(UsersTable, "active", "item/creator/user/active");
return IDFieldAlias;
}
/// <summary>
///
/// </summary>
public InnerJoinEntry(TableEntry JoinTable, ConditionEntry Condition)
{
this.JoinTable = JoinTable;
Conditions = new ArrayList();
Conditions.Add(Condition);
}
/// <summary>
/// Adds a field to the select list and populates xpath with an automatically
/// generated field alias.
/// </summary>
/// <param name="table">Table where field comes from. If null, SelectTable is used.</param>
/// <param name="name">Field name</param>
/// <param name="xpath">XPath expression to populate with field alias</param>
/// <returns>Field alias, or null if failed to add field (bad xpath?)</returns>
public string AddField(TableEntry table, string name, string xpath)
{
// Get select table if none specified
if(table == null)
{
table = GetSelectTable();
}
return AddFieldorValue(table.MakeField(name).ToString(), xpath);
}
/// <summary>
///
/// </summary>
public ListTypeCampaignDiaryEntries()
{
ThreadTable = new TableEntry("Threads", "t");
ClubTable = new TableEntry("Clubs", "c");
ThreadEntriesTable = new TableEntry("ThreadEntries", "te");
UsersTable = new TableEntry("Users", "u");
//GuideEntriesTable = new TableEntry("GuideEntries", "g");
// Add 'Standard' fields
AddFieldCreator(UsersTable);
AddFieldID(ThreadTable.MakeField("ThreadID"));
AddFieldDateUpdated(ThreadTable.MakeField("LastPosted"));
AddFieldDateCreated(ThreadTable.MakeField("DateCreated"));
AddFieldTitle(ThreadTable.MakeField("FirstSubject"));
// Add CampaignDiaryEntries specific fields
AddField(ThreadTable, "ForumID", "item/campaign-diary-entry-item/forumid");
AddField(ThreadTable, "ThreadID", "item/campaign-diary-entry-item/threadid");
AddField(ClubTable, "ClubID", "item/campaign-diary-entry-item/clubid");
AddField(ThreadTable, "FirstSubject", "item/campaign-diary-entry-item/title");
AddField(ThreadTable, "FirstSubject", "item/campaign-diary-entry-item/subject");
AddValue("'campaign'", "item/campaign-diary-entry-item/linkitemtype");
AddField(ClubTable, "ClubID", "item/campaign-diary-entry-item/linkitemid");
AddField(ClubTable, "Name", "item/campaign-diary-entry-item/linkitemname");
AddField(ThreadTable, "LastPosted", "item/campaign-diary-entry-item/date-updated");
AddFieldAuthor();
}
/// <summary>
///
/// </summary>
public ListTypeArticles()
{
SelectTable = new TableEntry("GuideEntries", "g");
UsersTable = new TableEntry("Users", "u");
// Add default fields //
AddField("h2g2id", "item/@itemid");
AddField("h2g2id", "item/article-item/@h2g2id");
AddField("datecreated", "item/date-created");
AddField("lastupdated", "item/date-updated");
AddFieldTitle(new FieldEntry(SelectTable, "subject"));
// item/creator
AddField("editor", "item/creator/user/userid");
AddField(UsersTable, "username", "item/creator/user/username");
AddField(UsersTable, "firstnames", "item/creator/user/firstnames");
AddField(UsersTable, "lastname", "item/creator/user/lastname");
AddField(UsersTable, "area", "item/creator/user/area");
AddField(UsersTable, "status", "item/creator/user/status");
AddField(UsersTable, "taxonomynode", "item/creator/user/taxonomynode");
AddField(UsersTable, "journal", "item/creator/user/journal");
AddField(UsersTable, "active", "item/creator/user/active");
// article-item
AddField("subject", "item/article-item/subject");
AddField("extrainfo", "item/article-item/extrainfo");
// article-item/author
AddField("editor", "item/article-item/author/user/userid");
AddField(UsersTable, "username", "item/article-item/author/user/username");
AddField(UsersTable, "firstnames", "item/article-item/author/user/firstnames");
AddField(UsersTable, "lastname", "item/article-item/author/user/lastname");
AddField(UsersTable, "area", "item/article-item/author/user/area");
AddField(UsersTable, "status", "item/article-item/author/user/status");
AddField(UsersTable, "taxonomynode", "item/article-item/author/user/taxonomynode");
AddField(UsersTable, "journal", "item/article-item/author/user/journal");
AddField(UsersTable, "active", "item/article-item/author/user/active");
}
/// <summary>
///
/// </summary>
public ListFilterBookmarkCount(ListTypeBase ListType, int BookmarkCount)
: base(ListType)
{
if (!IsSupported())
{
return;
}
TableEntry SelectTable = ListType.GetSelectTable();
// Construct inner join
// This table is actually a sub select not a concrete DB table.
String subSelect = "( Select DestinationId, Count(destinationid) 'bookmarkcount' FROM Links GROUP BY DestinationId )";
TableEntry LinksTable = new TableEntry(subSelect, "l");
LinksTable.IsSubSelect = true;
InnerJoinEntry ije = new InnerJoinEntry(LinksTable);
ije.Conditions.Add(new ConditionEntry(LinksTable.MakeField("DestinationId"), "=", SelectTable.MakeField("h2g2id")));
InnerJoins.Add(ije);
WhereConditions.Add(new ConditionEntry(LinksTable.MakeField("bookmarkcount"), ">", System.Convert.ToString(BookmarkCount)));
// Select fields
ListType.AddField(LinksTable, "bookmarkcount", "item/bookmarkcount");
}
/// <summary>
///
/// </summary>
public ListTypeClubs()
{
SelectTable = new TableEntry("Clubs", "c");
GuideEntriesTable = new TableEntry("Guideentries", "g");
UsersTable = new TableEntry("Users", "u");
// Add base fields
AddFieldID(new FieldEntry(SelectTable, "clubid"));
AddFieldDateCreated(new FieldEntry(GuideEntriesTable, "DateCreated"));
AddFieldDateUpdated(new FieldEntry(SelectTable, "LastUpdated", ">", GuideEntriesTable, "LastUpdated"));
AddFieldTitle(new FieldEntry(SelectTable, "name"));
AddFieldCreator(UsersTable);
// club-item fields
AddField("clubid", "item/club-item/@clubid");
AddField("name", "item/club-item/name");
AddField(GuideEntriesTable, "extrainfo", "item/club-item/extrainfo");
AddFieldAuthor();
}
/// <summary>
///
/// </summary>
public ListFilterCategories(ListTypeBase ListType, int [] Categories) : base(ListType)
{
// Get select table from list type class
TableEntry SelectTable = ListType.GetSelectTable();
// Convert catids into comma delimeted string
string CategoriesSQL = "(";
for(int i=0;i<Categories.Length;i++)
{
CategoriesSQL += Categories[i];
if(i+1 != Categories.Length)
{
CategoriesSQL += ",";
}
}
CategoriesSQL += ")";
if(ListType.GetListTypeName().ToUpper() == "ARTICLES")
{
// INNER JOIN HierarchyXXXMembers ham on ham.h2g2id = g.h2g2id
TableEntry HierarchyMembersTable = new TableEntry("HierarchyArticleMembers", "ham");
InnerJoinEntry ije = new InnerJoinEntry(HierarchyMembersTable);
ije.Conditions.Add(new ConditionEntry(HierarchyMembersTable.MakeField("entryid"), "=", SelectTable.MakeField("entryid")));
InnerJoins.Add(ije);
// WHERE ham.nodeid in (category ids)
WhereConditions.Add(new ConditionEntry(HierarchyMembersTable.MakeField("nodeid"), "in", CategoriesSQL));
// Select Field
ListType.AddField(HierarchyMembersTable, "nodeid", "item/category/@nodeid");
}
else if(ListType.GetListTypeName().ToUpper() == "CLUBS")
{
// INNER JOIN HierarchyXXXMembers ham on ham.h2g2id = g.h2g2id
TableEntry HierarchyMembersTable = new TableEntry("HierarchyClubMembers", "hcm");
InnerJoinEntry ije = new InnerJoinEntry(HierarchyMembersTable);
ije.Conditions.Add(new ConditionEntry(HierarchyMembersTable.MakeField("clubid"), "=", SelectTable.MakeField("clubid")));
InnerJoins.Add(ije);
// WHERE ham.nodeid in (category ids)
WhereConditions.Add(new ConditionEntry(HierarchyMembersTable.MakeField("nodeid"), "in", CategoriesSQL));
// SiteID condition
//WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("siteid"), "=", "@SiteID"));
// Status condition (club.status==open)
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("status"), "=", "1"));
// Canview (club.canview=1)
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("canview"), "=", "1"));
ListType.AddField(HierarchyMembersTable, "nodeid", "item/category/@nodeid");
}
else if(ListType.GetListTypeName().ToUpper() == "TOPICFORUMS")
{
// inner join guideentries g on g.h2g2id=t.h2g2id
TableEntry GuideEntriesTable = new TableEntry("GuideEntries", "g");
InnerJoinEntry ije1 = new InnerJoinEntry(GuideEntriesTable);
ije1.Conditions.Add(new ConditionEntry(GuideEntriesTable.MakeField("h2g2id"), "=", SelectTable.MakeField("h2g2id")));
InnerJoins.Add(ije1);
// INNER JOIN HierarchyXXXMembers ham on ham.h2g2id = g.h2g2id
TableEntry HierarchyMembersTable = new TableEntry("HierarchyArticleMembers", "ham");
InnerJoinEntry ije = new InnerJoinEntry(HierarchyMembersTable);
ije.Conditions.Add(new ConditionEntry(HierarchyMembersTable.MakeField("entryid"), "=", GuideEntriesTable.MakeField("entryid")));
InnerJoins.Add(ije);
// INNER JOIN on forums to get forum.canread
TableEntry ForumsTable = new TableEntry("Forums", "f");
InnerJoins.Add(new InnerJoinEntry(ForumsTable,
new ConditionEntry(ForumsTable.MakeField("forumid"), "=", GuideEntriesTable.MakeField("forumid"))));
// forum.canread=1
WhereConditions.Add(new ConditionEntry(ForumsTable.MakeField("canread"), "=", "1"));
// topicstatus = 0 (TS_LIVE)
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("topicstatus"), "=", "0"));
// WHERE ham.nodeid in (category ids)
WhereConditions.Add(new ConditionEntry(HierarchyMembersTable.MakeField("nodeid"), "in", CategoriesSQL));
// SiteID condition
//WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("siteid"), "=", "@SiteID"));
ListType.AddField(HierarchyMembersTable, "nodeid", "item/category/@nodeid");
}
else if(ListType.GetListTypeName().ToUpper() == "USERS")
{
// INNER JOIN HierarchyXXXMembers ham on ham.h2g2id = g.h2g2id
TableEntry HierarchyMembersTable = new TableEntry("HierarchyUserMembers", "hum");
InnerJoinEntry ije = new InnerJoinEntry(HierarchyMembersTable);
ije.Conditions.Add(new ConditionEntry(HierarchyMembersTable.MakeField("userid"), "=", SelectTable.MakeField("userid")));
InnerJoins.Add(ije);
// INNER JOIN Hierarhcy h on h.nodeid = hum.nodeid
TableEntry HierarchyTable = new TableEntry("Hierarchy", "h");
InnerJoinEntry ije2 = new InnerJoinEntry(HierarchyTable);
ije2.Conditions.Add(new ConditionEntry(HierarchyTable.MakeField("nodeid"), "=", HierarchyMembersTable.MakeField("nodeid")));
InnerJoins.Add(ije2);
// WHERE ham.nodeid in (category ids)
WhereConditions.Add(new ConditionEntry(HierarchyMembersTable.MakeField("nodeid"), "in", CategoriesSQL));
// SiteID condition
//WhereConditions.Add(new ConditionEntry(HierarchyTable.MakeField("siteid"), "=", "@SiteID"));
// Active
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("active"), "=", "1"));
// Status
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("status"), "=", "1"));
ListType.AddField(HierarchyMembersTable, "nodeid", "item/category/@nodeid");
}
else if(ListType.GetListTypeName().ToUpper() == "THREADS")
{
// Join on forums
TableEntry ForumsTable = new TableEntry("Forums", "lfcForums");
InnerJoins.Add(new InnerJoinEntry(ForumsTable,
new ConditionEntry(SelectTable.MakeField("ForumID").ToString(), "=", ForumsTable.MakeField("ForumID").ToString())));
// Join on guideentries
//TableEntry GuideEntriesTable = new TableEntry("GuideEntries", "lfcGuideEntries");
//InnerJoins.Add(new InnerJoinEntry(GuideEntriesTable,
// new ConditionEntry(GuideEntriesTable.MakeField("forumid").ToString(), "=", ForumsTable.MakeField("ForumID").ToString())));
// Join on hierarchymembers
TableEntry HierarchyMembersTable = new TableEntry("HierarchyThreadMembers", "htm");
InnerJoinEntry ije = new InnerJoinEntry(HierarchyMembersTable);
ije.Conditions.Add(new ConditionEntry(HierarchyMembersTable.MakeField("threadid"), "=", SelectTable.MakeField("threadid")));
InnerJoins.Add(ije);
// WHERE ham.nodeid in (category ids)
WhereConditions.Add(new ConditionEntry(HierarchyMembersTable.MakeField("nodeid"), "in", CategoriesSQL));
// SiteID
//WhereConditions.Add(new ConditionEntry(ForumsTable.MakeField("siteid"), "=", "@SiteID"));
// threads can read
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("canread"), "=", "1"));
// forum can read
WhereConditions.Add(new ConditionEntry(ForumsTable.MakeField("canread"), "=", "1"));
ListType.AddField(HierarchyMembersTable, "nodeid", "item/category/@nodeid");
}
else if(ListType.GetListTypeName().ToUpper() == "CATEGORIES")
{
// Where parent = (n,n.n)
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("parentid"), "in", CategoriesSQL));
// SiteID
//WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("siteid"), "=", "@SiteID"));
ListType.AddField("parentid", "item/category/@nodeid");
}
else
{
// not supported
return;
}
}
/// <summary>
///
/// </summary>
public ListTypeForums()
{
ForumTable = new TableEntry("Forums", "f");
// Add fields
AddFieldID(new FieldEntry(ForumTable, "ForumID"));
AddFieldDateCreated(new FieldEntry(ForumTable, "DateCreated"));
AddFieldDateUpdated(new FieldEntry(ForumTable, "LastUpdated"));
AddFieldTitle(new FieldEntry(ForumTable, "Title"));
AddField("forumid", "item/forum-item/@forumid");
AddField("ForumPostCount", "item/forum-item/@forumpostcount");
AddField("LastPosted", "item/forum-item/date-lastposted");
AddField("Title", "item/forum-item/title");
}
/// <summary>
///
/// </summary>
/// <param name="ListType"></param>
/// <param name="Score"></param>
public ListFilterSignificance(ListTypeBase ListType, int Score) : base(ListType)
{
// Get select table from list type class
TableEntry SelectTable = ListType.GetSelectTable();
if(!IsSupported()) return;
TableEntry ContentSignifTable;
string IDField;
// Choose ContentSignif table and id field
if(this.ListType.GetListTypeName().ToUpper() == "ARTICLES")
{
ContentSignifTable = new TableEntry("ContentSignifArticle", "csa");
IDField = "h2g2id";
}
else if (this.ListType.GetListTypeName().ToUpper() == "CLUBS")
{
ContentSignifTable = new TableEntry("ContentSignifClub", "csc");
IDField = "Clubid";
// Status condition (club.status==open)
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("status"), "=", "1"));
// Canview (club.canview=1)
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("canview"), "=", "1"));
}
else if (this.ListType.GetListTypeName().ToUpper() == "USERS")
{
ContentSignifTable = new TableEntry("ContentSignifUser", "csu");
IDField = "Userid";
// Active
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("active"), "=", "1"));
// Status
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("status"), "=", "1"));
}
else if (this.ListType.GetListTypeName().ToUpper() == "THREADS")
{
ContentSignifTable = new TableEntry("ContentSignifThread", "csc");
IDField = "ThreadID";
}
else if (this.ListType.GetListTypeName().ToUpper() =="CATEGORIES")
{
ContentSignifTable = new TableEntry("ContentSignifNode", "csn");
IDField = "NodeID";
}
else if(this.ListType.GetListTypeName().ToUpper() == "CAMPAIGNDIARYENTRIES")
{
ContentSignifTable = new TableEntry("ContentSignifThread", "cst");
IDField = "ThreadID";
}
else if (this.ListType.GetListTypeName().ToUpper() =="FORUMS")
{
ContentSignifTable = new TableEntry("ContentSignifForum", "csf");
IDField = "ForumID";
// CanRead
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("canread"), "=", "1"));
}
else if (this.ListType.GetListTypeName().ToUpper() == "TOPICFORUMS")
{
// inner join guideentries g on g.h2g2id=t.h2g2id
TableEntry GuideEntriesTable = new TableEntry("GuideEntries", "g");
InnerJoinEntry ije1 = new InnerJoinEntry(GuideEntriesTable);
ije1.Conditions.Add(new ConditionEntry(GuideEntriesTable.MakeField("h2g2id"), "=", SelectTable.MakeField("h2g2id")));
InnerJoins.Add(ije1);
// inner join forums f on f.forumid=g.forumid
TableEntry ForumsTable = new TableEntry("Forums", "f");
InnerJoinEntry ije2 = new InnerJoinEntry(ForumsTable);
ije2.Conditions.Add(new ConditionEntry(ForumsTable.MakeField("forumid"), "=", GuideEntriesTable.MakeField("forumid")));
InnerJoins.Add(ije2);
// forum.canread=1
WhereConditions.Add(new ConditionEntry(ForumsTable.MakeField("canread"), "=", "1"));
// topicstatus = 0 (TS_LIVE)
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("topicstatus"), "=", "0"));
ContentSignifTable = new TableEntry("ContentSignifForum", "csf");
IDField = "ForumID";
// INNER JOIN ContentSignifXXXXX csx on csx.[id] = selecttable.[id]
InnerJoinEntry ije = new InnerJoinEntry(ContentSignifTable);
ije.Conditions.Add(new ConditionEntry(ContentSignifTable.MakeField(IDField), "=", ForumsTable.MakeField(IDField)));
InnerJoins.Add(ije);
}
else
{
// not supported
return;
}
if (this.ListType.GetListTypeName().ToUpper() != "TOPICFORUMS") // TOPICFORUMS do things differently
{
// INNER JOIN ContentSignifXXXXX csx on csx.[id] = selecttable.[id]
InnerJoinEntry ije = new InnerJoinEntry(ContentSignifTable);
ije.Conditions.Add(new ConditionEntry(ContentSignifTable.MakeField(IDField), "=", SelectTable.MakeField(IDField)));
InnerJoins.Add(ije);
}
// WHERE csx.Score = Score
WhereConditions.Add(new ConditionEntry(ContentSignifTable.MakeField("Score"), ">", System.Convert.ToString(Score)));
// @SiteID condition
//WhereConditions.Add(new ConditionEntry(ContentSignifTable.MakeField("SiteID"), "=", "@SiteID"));
// Select fields
ListType.AddField(ContentSignifTable, "Score", "item/score");
}
/// <summary>
///
/// </summary>
public ListFilterDateCreated(ListTypeBase ListType, int DaysAgo) : base(ListType)
{
// Get select table from list type class
TableEntry SelectTable = ListType.GetSelectTable();
// Convert date into SQL
string DateVal;
DateVal = "DATEADD(day, -" + System.Convert.ToString(DaysAgo) + ", getdate())";
if(this.ListType.GetListTypeName().ToUpper() == "ARTICLES")
{
// Where DateCreated > date
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("DateCreated"), ">", DateVal) );
// Add fields
ListType.AddField("DateCreated", "item/date-created");
}
else if (ListType.GetListTypeName().ToUpper() == "CLUBS")
{
// Where DateCreated > date
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("DateCreated"), ">", DateVal ) );
// SiteID
//WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("SiteID"), "=", "@SiteID" ) );
// Status condition (club.status==open)
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("status"), "=", "1"));
// Canview (club.canview=1)
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("canview"), "=", "1"));
ListType.AddField("DateCreated", "item/date-created");
}
else if (ListType.GetListTypeName().ToUpper() == "FORUMS")
{
// Where DateCreated > date
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("DateCreated"), ">", DateVal ) );
// SiteID
//WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("SiteID"), "=", "@SiteID" ) );
// CanRead
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("canread"), "=", "1" ) );
ListType.AddField("DateCreated", "item/date-created");
}
else if(ListType.GetListTypeName().ToUpper() == "THREADS")
{
// inner join forums (for site id)
TableEntry ForumsTable = new TableEntry("Forums", "f");
InnerJoins.Add(new InnerJoinEntry(ForumsTable,
new ConditionEntry(ForumsTable.MakeField("forumid"), "=", SelectTable.MakeField("forumid"))));
// Where DateCreated > date
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("DateCreated"), ">", DateVal ) );
// SiteID
//WhereConditions.Add(new ConditionEntry(ForumsTable.MakeField("SiteID"), "=", "@SiteID" ) );
ListType.AddField("DateCreated", "item/date-created");
}
else if(ListType.GetListTypeName().ToUpper() == "CATEGORIES")
{
// INNER JOIN GuideEntries g ON g.h2g2id = h.h2g2id
TableEntry GuideEntriesTable = new TableEntry("GuideEntries", "g");
InnerJoinEntry ije = new InnerJoinEntry(GuideEntriesTable,
new ConditionEntry(GuideEntriesTable.MakeField("h2g2id"), "=", SelectTable.MakeField("h2g2id")));
InnerJoins.Add(ije);
// Where g.DateCreated > date
WhereConditions.Add(new ConditionEntry(GuideEntriesTable.MakeField("DateCreated"), ">", DateVal ) );
// SiteID
//WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("SiteID"), "=", "@SiteID" ) );
ListType.AddField(GuideEntriesTable, "DateCreated", "item/date-created");
}
else if(ListType.GetListTypeName().ToUpper() == "TOPICFORUMS")
{
// inner join guideentries g on g.h2g2id=t.h2g2id
TableEntry GuideEntriesTable = new TableEntry("GuideEntries", "g");
InnerJoinEntry ije1 = new InnerJoinEntry(GuideEntriesTable);
ije1.Conditions.Add(new ConditionEntry(GuideEntriesTable.MakeField("h2g2id"), "=", SelectTable.MakeField("h2g2id")));
InnerJoins.Add(ije1);
// inner join forums f on f.forumid=g.forumid
TableEntry ForumsTable = new TableEntry("Forums", "f");
InnerJoinEntry ije2 = new InnerJoinEntry(ForumsTable);
ije2.Conditions.Add(new ConditionEntry(ForumsTable.MakeField("forumid"), "=", GuideEntriesTable.MakeField("forumid")));
InnerJoins.Add(ije2);
// forum.canread=1
WhereConditions.Add(new ConditionEntry(ForumsTable.MakeField("canread"), "=", "1"));
// topicstatus = 0 (TS_LIVE)
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("topicstatus"), "=", "0"));
// WHERE forums.datecreated > date
WhereConditions.Add(new ConditionEntry(ForumsTable.MakeField("DateCreated"), ">", DateVal ) );
// SiteID
//WhereConditions.Add(new ConditionEntry(ForumsTable.MakeField("SiteID"), "=", "@SiteID" ) );
ListType.AddField(ForumsTable, "DateCreated", "item/date-created");
}
else if(ListType.GetListTypeName().ToUpper() == "CAMPAIGNDIARYENTRIES")
{
WhereConditions.Add
(new ConditionEntry(SelectTable.MakeField("DateCreated"), ">", DateVal));
ListType.AddField(SelectTable, "DateCreated", "item/date-created");
}
else
{
return;
}
}