/// <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="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 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 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 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 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>
/// 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 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 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 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 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 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 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;
}
}