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