Example #1
0
        /// <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;
            }
        }
Example #2
0
        /// <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");
		}
Example #3
0
        /// <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");
		}
Example #4
0
        /// <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;
			}
		}
Example #5
0
        /// <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");
		}
Example #6
0
        /// <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");
        }
Example #7
0
		/// <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);
		}
Example #8
0
        /// <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();
		}
Example #9
0
        /// <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");
		}
Example #10
0
        /// <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");
		}
Example #11
0
        /// <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");
		}
Example #12
0
        /// <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");

        }
Example #13
0
        /// <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");

        }
Example #14
0
        /// <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");
        }
Example #15
0
        /// <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;
			}
		}