/// <summary>
/// Creates array list of list filters based on xml input parameters
/// </summary>
/// <returns>ArrayList of ListFilterBase derived classes</returns>
private ArrayList CreateListFilterCollection(ListTypeBase ListType)
{
ArrayList ListFilterCollection = new ArrayList();
// Add common filters //
// SiteID filter
ListFilterSiteID lfsid = new ListFilterSiteID(ListType);
if(lfsid.IsSupported())
{
ListFilterCollection.Add(lfsid);
}
// Moderation filter
ListFilterModeration lfm = new ListFilterModeration(ListType);
if(lfm.IsSupported())
{
ListFilterCollection.Add(lfm);
}
// Create filter classes based on what filters we have //
if(xmlArticleType != null)
{
ListFilterArticleType lf = new ListFilterArticleType(ListType, System.Convert.ToInt32(xmlArticleType));
// Only add if supported
if(lf.IsSupported())
{
ListFilterCollection.Add(lf);
}
}
//Add a filter for article date range.
if (xmlArticleStartDate != null)
{
DateTime startDate, endDate;
if (xmlArticleStartDate != null && DateTime.TryParse(xmlArticleStartDate, out startDate)
&& xmlArticleEndDate != null && DateTime.TryParse(xmlArticleEndDate, out endDate))
{
ListFilterArticleDate lfad = new ListFilterArticleDate(ListType, startDate, endDate);
if (lfad.IsSupported())
{
ListFilterCollection.Add(lfad);
}
}
}
if(xmlStatus != null)
{
ListFilterArticleStatus lf = new ListFilterArticleStatus(ListType, System.Convert.ToInt32(xmlStatus));
// Only add if supported
if(lf.IsSupported())
{
ListFilterCollection.Add(lf);
}
}
// Averagerating filter
if(xmlRating != null)
{
// Create Rating filter
ListFilterRating lf = new ListFilterRating(ListType, System.Convert.ToDouble(xmlRating));
// Only add if supported
if(lf.IsSupported())
{
ListFilterCollection.Add(lf);
}
}
// Date Created
if(xmlDateCreated != null)
{
ListFilterDateCreated lf = new ListFilterDateCreated(ListType, System.Convert.ToInt32(xmlDateCreated));
// Only add if supported
if(lf.IsSupported())
{
ListFilterCollection.Add(lf);
}
}
if(xmlScore != null)
{
ListFilterSignificance lf = new ListFilterSignificance(ListType, System.Convert.ToInt32(xmlScore));
// Only add if supported
if(lf.IsSupported())
{
ListFilterCollection.Add(lf);
}
}
if(xmlLastUpdated != null)
{
ListFilterDateUpdated lf = new ListFilterDateUpdated(ListType, System.Convert.ToInt32(xmlLastUpdated));
// Only add if supported
if(lf.IsSupported())
{
ListFilterCollection.Add(lf);
}
}
// Filter on UID Prefix - CommentForums only.
if (xmlUIDPrefix != null)
{
ListFilterUIDPrefix lf = new ListFilterUIDPrefix(ListType, xmlUIDPrefix);
if (lf.IsSupported())
{
ListFilterCollection.Add(lf);
}
}
if(xmlCategories != null)
{
int [] CategoryIDs = new int[xmlCategories.Length];
for(int n=0;n<xmlCategories.Length;n++)
{
CategoryIDs[n] = System.Convert.ToInt32(xmlCategories[n]);
}
ListFilterCategories lf = new ListFilterCategories(ListType, CategoryIDs);
if(lf.IsSupported())
{
ListFilterCollection.Add(lf);
}
}
if ( xmlKeyPhrases != null && xmlKeyPhrases.Count > 0 )
{
ListFilterKeyPhrases kpf = new ListFilterKeyPhrases(ListType, xmlKeyPhrases);
if ( kpf.IsSupported() )
{
ListFilterCollection.Add(kpf);
}
}
if(xmlVoteCount != null)
{
ListFilterVoteCount lf = new ListFilterVoteCount(ListType, System.Convert.ToInt32(xmlVoteCount));
if(lf.IsSupported())
{
ListFilterCollection.Add(lf);
}
}
if (xmlBookmarkCount != null)
{
ListFilterBookmarkCount lf = new ListFilterBookmarkCount(ListType, System.Convert.ToInt32(xmlBookmarkCount));
if (lf.IsSupported())
{
ListFilterCollection.Add(lf);
}
}
if(xmlThreadType != null)
{
ListFilterThreadType lftt = new ListFilterThreadType(ListType, xmlThreadType);
if(lftt.IsSupported())
{
ListFilterCollection.Add(lftt);
}
}
if(xmlEventDate != null)
{
ListFilterEventDate lfed = new ListFilterEventDate(ListType, System.Convert.ToInt32(xmlEventDate));
if(lfed.IsSupported())
{
ListFilterCollection.Add(lfed);
}
}
return ListFilterCollection;
}
/// <summary>
///
/// </summary>
public ListFilterArticleType(ListTypeBase ListType, int Type) : base(ListType)
{
if(!IsSupported()) return;
// Get select table from list type class
TableEntry SelectTable = ListType.GetSelectTable();
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("Type"), "=", System.Convert.ToString(Type)));
// SiteID condition
//WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("siteid"), "=", "@SiteID"));
// Select field
ListType.AddField("type", "item/article-item/@articletype");
}
/// <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 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 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>
public ListFilterModeration(ListTypeBase ListType): base(ListType)
{
if(!IsSupported()) return;
string ListTypeName = this.ListType.GetListTypeName().ToUpper();
if(ListTypeName == "ARTICLES")
{
this.WhereConditions.Add(new ConditionEntry(ListType.GetSelectTable().MakeField("hidden"), "is", "null"));
}
else if(ListTypeName == "CAMPAIGNDIARYENTRIES"
|| ListTypeName == "THREADS")
{
this.WhereConditions.Add(new ConditionEntry(ListType.GetSelectTable().MakeField("VisibleTo"), "is", "null"));
this.WhereConditions.Add(new ConditionEntry(ListType.GetSelectTable().MakeField("CanRead"), "=", "1"));
}
}
/// <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>
/// Constructs an OrderBy filter. Only one of these should be needed.
/// </summary>
/// <param name="ListType">Instance of ListTypeBase derived class</param>
public OrderByBase(ListTypeBase ListType)
{
this.ListType = ListType;
InnerJoins = new ArrayList();
}
/// <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 ListFilterUIDPrefix(ListTypeBase ListType, string prefix)
: base(ListType)
{
if (!IsSupported()) return;
WhereConditions.Add(new ConditionEntry(ListType.GetSelectTable().MakeField("uid").ToString(), "LIKE", "'" + prefix + "%'"));
}
/// <summary>
///
/// </summary>
public ListFilterEventDate(ListTypeBase ListType, int DaysAgo) : base(ListType)
{
if(!IsSupported()) return;
string DateVal;
DateVal = "DATEADD(day, -" + System.Convert.ToString(DaysAgo) + ", getdate())";
WhereConditions.Add(new ConditionEntry(ListType.GetSelectTable().MakeField("eventdate").ToString(), ">", DateVal));
ListType.AddField(ListType.GetSelectTable().MakeField("eventdate"), "item/thread-item/eventdate");
}
/// <summary>
///
/// </summary>
public ListFilterThreadType(ListTypeBase ListType, string type) : base(ListType)
{
if(!IsSupported()) return;
// Where THREAD.TYPE = 'type'
WhereConditions.Add(new ConditionEntry(ListType.GetSelectTable().MakeField("Type").ToString(), "=", "'" + type + "'"));
ListType.AddField(ListType.GetSelectTable().MakeField("Type"), "item/thread-item/type");
}
/// <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 ListFilterArticleStatus(ListTypeBase ListType, int Status) : base(ListType)
{
if(!IsSupported()) return;
// Get select table from list type class
TableEntry SelectTable = ListType.GetSelectTable();
// Status
WhereConditions.Add(new ConditionEntry(SelectTable.MakeField("Status"), "=", System.Convert.ToString(Status)));
// Add select field
ListType.AddField("status", "item/article-item/@articlestatus");
}
/// <summary>
/// Creates order by class for type of list and sort option (xmlOrderBy)
/// </summary>
/// <param name="ListType"></param>
/// <returns>New instance of OrderBy class, or null is no such orderby exists</returns>
private OrderByBase CreateOrderByClass(ListTypeBase ListType)
{
// Create order by
if(xmlOrderBy == "RATING")
{
return new OrderByRating(ListType);
}
else if(xmlOrderBy == "DATECREATED")
{
return new OrderByDateCreated(ListType);
}
else if(xmlOrderBy == "DATEUPDATED")
{
return new OrderByDateUpdated(ListType);
}
else if(xmlOrderBy == "SIGNIFICANCE")
{
return new OrderBySignificance(ListType);
}
else if(xmlOrderBy == "VOTECOUNT")
{
return new OrderByVoteCount(ListType);
}
else if(xmlOrderBy == "EVENTDATE")
{
return new OrderByEventDate(ListType);
}
else if (xmlOrderBy == "BOOKMARKCOUNT")
{
return new OrderByBookmarkCount(ListType);
}
else if (xmlOrderBy == "COMMENTCOUNT")
{
//Very Specific Order By - Order By Comment Count since datePosted.
return new OrderByCommentForumPostCount( ListType, xmlDateCreated );
}
return null;
}
/// <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>
/// Represents a single filter for the list
/// </summary>
/// <param name="ListType">Valid list type ("ARTICLES", "FORUMS", etc...)</param>
public ListFilterBase(ListTypeBase ListType)
{
this.ListType = ListType;
InnerJoins = new ArrayList();
WhereConditions = new ArrayList();
}
/// <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 ListFilterSiteID(ListTypeBase ListType) : base(ListType)
{
string FieldName = this.ListType.GetSiteIDField();
if(FieldName == null)
{
return;
}
WhereConditions.Add(new ConditionEntry(FieldName, "=", "@SiteID"));
}
/// <summary>
///
/// </summary>
public OrderByEventDate(ListTypeBase ListType) : base(ListType)
{
if(!IsSupported())
{
return;
}
OrderByField = ListType.GetSelectTable().MakeField("EventDate");
ListType.AddField(ListType.GetSelectTable().MakeField("EventDate"), "item/thread-item/eventdate");
}
/// <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 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 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 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>
/// <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;
}
}