C# Bobs.Query类代码示例

		void BindNewPromoters()
		{
			if (true)
			{
				Query q = new Query();
				q.QueryCondition = new And(
					(ContainerPage.Url[0].Equals("all") ? new Q(Promoter.Columns.ClientSector, Promoter.ClientSectorEnum.Promoter) : new Q(true)),
					Promoter.EnabledQ,
					new Q(Promoter.Columns.SalesStatus, Promoter.SalesStatusEnum.New),
					SalesUsrQ,
					new Q(Promoter.Columns.SalesCallCount, 0),
					new Or(
						new Q(Promoter.Columns.SalesHold, false),
						new Q(Promoter.Columns.SalesHold, QueryOperator.IsNull, null)
					)
				);

				q.OrderBy = new OrderBy(Promoter.Columns.DateTimeSignUp, OrderBy.OrderDirection.Ascending);

				PromoterSet ps = new PromoterSet(q);

				NewPromoterDataGrid.AllowPaging = (ps.Count > NewPromoterDataGrid.PageSize);
				NewPromoterDataGrid.DataSource = ps;
				NewPromoterDataGrid.DataBind();

			}
		}

		void BindCallBacks()
		{
			if (true)
			{
				Query q = new Query();
				q.QueryCondition = new And(
					(ContainerPage.Url[0].Equals("all") ? new Q(Promoter.Columns.ClientSector, Promoter.ClientSectorEnum.Promoter) : new Q(true)),
					Promoter.EnabledQ,
					new Q(Promoter.Columns.SalesStatus, Promoter.SalesStatusEnum.New),
					SalesUsrQ,
					new Or(
						new Q(Promoter.Columns.SalesHold, false),
						new Q(Promoter.Columns.SalesHold, QueryOperator.IsNull, null)
					),
					new Q(Promoter.Columns.SalesNextCall, QueryOperator.LessThan, DateTime.Today.AddDays(1)),
					new Q(Promoter.Columns.SalesCallCount, QueryOperator.NotEqualTo, 0)
				);

				q.OrderBy = Promoter.NewIdleOrder;

				PromoterSet ps = new PromoterSet(q);

				CallBacksDataGrid.AllowPaging = (ps.Count > CallBacksDataGrid.PageSize);
				CallBacksDataGrid.DataSource = ps;
				CallBacksDataGrid.DataBind();
			}
		}

		protected void BindOnlineBox()
		{
			if (Prefs.Current["ShowOnline"].Exists && Prefs.Current["ShowOnline"] == 1)
			{
				//Show box
				OnlineHiddenPanel.Visible = false;
				OnlinePanel.Visible = true;
			}
			else
			{
				//show link
				Query q = new Query();
				q.CacheDuration = TimeSpan.FromMinutes(10);
				q.QueryCondition = Usr.LoggedInChatQ;
				q.ReturnCountOnly = true;
				UsrSet us = new UsrSet(q);
				if (us.Count <= 1)
				{
					this.Visible = false;
				}
				else
				{
					Bobs.Global.SetMaxUsers(us.Count);
					OnlineHiddenLabel.Text = us.Count.ToString() + " user" + (us.Count == 1 ? "" : "s") + " online";
					OnlineHiddenPanel.Visible = true;
					OnlinePanel.Visible = false;
				}
			}
			

		}

		void ArticlesDataGrid_Bind()
		{
			Query q = new Query();
			if (Usr.Current.IsAdmin)
				q.QueryCondition = new Or(new Q(Article.Columns.OwnerUsrK, Usr.Current.K), new Q(Article.Columns.Status, Article.StatusEnum.Edit), new Q(Article.Columns.Status, Article.StatusEnum.Enabled));
			else if (Usr.Current.IsSuper)
				q.QueryCondition = new Or(new Q(Article.Columns.OwnerUsrK, Usr.Current.K), new Q(Article.Columns.Status, Article.StatusEnum.Edit));
			else
				q.QueryCondition = new Q(Article.Columns.OwnerUsrK, Usr.Current.K);

			q.OrderBy = new OrderBy(Article.Columns.AddedDateTime, OrderBy.OrderDirection.Descending);

			ArticleSet ars = new ArticleSet(q);

			if (ars.Count == 0)
			{
				NoArticlesDataGridPanel.Visible = true;
				ArticlesDataGridPanel.Visible = false;

			}
			else
			{
				ArticlesDataGrid.Columns[3].Visible = Usr.Current.IsSuper;
				NoArticlesDataGridPanel.Visible = false;
				ArticlesDataGridPanel.Visible = true;
				ArticlesDataGrid.AllowPaging = (ars.Count > ArticlesDataGrid.PageSize);
				ArticlesDataGrid.DataSource = ars;
				ArticlesDataGrid.DataBind();
			}
		}

        private void GetResults()
        {
            Query searchCardTransferQuery = new Query();

            List<Q> QueryConditionList = new List<Q>();

            searchCardTransferQuery.OrderBy = new OrderBy("dateadd(day, 0, datediff(day, 0, [Transfer].[DateTimeComplete]))");
            searchCardTransferQuery.GroupBy = new GroupBy("dateadd(day, 0, datediff(day, 0, [Transfer].[DateTimeComplete]))");
            searchCardTransferQuery.ExtraSelectElements.Add("SumAmount", "SUM([Transfer].[Amount])");
            searchCardTransferQuery.ExtraSelectElements.Add("CountTransfers", "COUNT([Transfer].[K])");
            searchCardTransferQuery.ExtraSelectElements.Add("Date", "dateadd(day, 0, datediff(day, 0, [Transfer].[DateTimeComplete]))");
            searchCardTransferQuery.Columns = new ColumnSet();

            QueryConditionList.Add(new Q(Bobs.Transfer.Columns.Method, Bobs.Transfer.Methods.Card));
            QueryConditionList.Add(new Q(Bobs.Transfer.Columns.Status, Bobs.Transfer.StatusEnum.Success));

            if (this.FromDateCal.Date != DateTime.MinValue)
                QueryConditionList.Add(new Q(Bobs.Transfer.Columns.DateTimeComplete, QueryOperator.GreaterThanOrEqualTo, this.FromDateCal.Date));
            if (this.ToDateCal.Date != DateTime.MinValue)
                QueryConditionList.Add(new Q(Bobs.Transfer.Columns.DateTimeComplete, QueryOperator.LessThan, this.ToDateCal.Date.AddDays(1)));

            searchCardTransferQuery.QueryCondition = new And(QueryConditionList.ToArray());

            TransferSet searchCardTransfers = new TransferSet(searchCardTransferQuery);

            this.CardnetAccountGridView.DataSource = searchCardTransfers;
            this.CardnetAccountGridView.DataBind();
            
            SumAmounts(searchCardTransfers);
        }

		public void SetState()
		{
			Query q = new Query();
			q.OrderBy = MusicType.OrderBy;
			MusicTypeSet mts = new MusicTypeSet(q);
			foreach (MusicType mt in mts)
			{
					CheckBox cb = (CheckBox)Cambro.Web.Helpers.SearchControl(Tree, "ItemCb" + mt.K);
					cb.Checked = false;
				if (mt.Children.Count > 0)
				{
					HtmlGenericControl children = (HtmlGenericControl)Cambro.Web.Helpers.SearchControl(Tree, "Children" + mt.K);
					children.Style["display"] = null;
					Label lab = (Label)Cambro.Web.Helpers.SearchControl(Tree, "LabCb" + mt.K);
					lab.Style["display"] = "none";
				}
			}
			if (initialMusicTypes != null)
			{
				foreach (MusicType mt in initialMusicTypes)
				{
					CheckBox cb = (CheckBox)Cambro.Web.Helpers.SearchControl(Tree, "ItemCb" + mt.K);
					cb.Checked = true;
					if (mt.Children.Count > 0)
					{
						HtmlGenericControl children = (HtmlGenericControl)Cambro.Web.Helpers.SearchControl(Tree, "Children" + mt.K);
						children.Style["display"] = "none";
						Label lab = (Label)Cambro.Web.Helpers.SearchControl(Tree, "LabCb" + mt.K);
						lab.Style["display"] = null;
					}
				}
			}
		}

		private void Page_Load(object sender, System.EventArgs e)
		{
			// Put user code to initialize the page here
			Query q = new Query();
			q.QueryCondition = Usr.LoggedInQ;
			q.NoLock = true;
			q.ReturnCountOnly = true;
			UsrSet us = new UsrSet(q);
			UsersOnline5MinLabel.Text = (us.Count == 1 ? "is " : "are ") + us.Count.ToString("#,##0") + " user" + (us.Count == 1 ? "" : "s");

			Query q30min = new Query();
			q30min.QueryCondition = Usr.LoggedIn30MinQ;
			q30min.NoLock = true;
			q30min.ReturnCountOnly = true;
			UsrSet us30min = new UsrSet(q30min);
			UsersOnline30MinLabel.Text = us30min.Count.ToString("#,##0");

			Bobs.Global gMaxUsers = new Bobs.Global(Bobs.Global.Records.MaxUsers5Min);
			MaxUsersOnline5MinLabel.Text = gMaxUsers.ValueInt.ToString("#,##0");
			MaxUsersOnline5MinDateLabel.Text = Cambro.Misc.Utility.FriendlyDate(gMaxUsers.ValueDateTime, true, false);
			Bobs.Global gMaxUsers30Min = new Bobs.Global(Bobs.Global.Records.MaxUsers30Min);
			MaxUsersOnline30MinLabel.Text = gMaxUsers30Min.ValueInt.ToString("#,##0");

			Page.DataBind();
		}

		public void DaveIgnore(object o, System.EventArgs e)
		{
			Usr Dave = new Usr(4);
			Cambro.Web.Helpers.WriteAlertHeader();

			Cambro.Web.Helpers.WriteAlert("Selecting...", 1);
			Query q = new Query();
			if (Vars.DevEnv)
				q.TopRecords = 50;
			PromoterSet bs = new PromoterSet(q);
			for (int count = 0; count < bs.Count; count++)
			{
				Promoter p = bs[count];

				// Do work here!
				try
				{
					Thread t = new Thread(p.QuestionsThreadK);

					ThreadUsr tu = t.GetThreadUsr(Dave);
					tu.ChangeStatus(ThreadUsr.StatusEnum.Ignore, DateTime.Now, true, true);
					if (count % 100 == 0)
						Cambro.Web.Helpers.WriteAlert("Done " + count + "/" + bs.Count + " - " + p.UrlName, 2);
				}
				catch
				{
					Cambro.Web.Helpers.WriteAlert("Done " + count + "/" + bs.Count + " - EXCEPTION! " + p.UrlName, 3);
				}

				bs.Kill(count);

			}
			Cambro.Web.Helpers.WriteAlert("Done!", 4);
			Cambro.Web.Helpers.WriteAlertFooter("/admin/promoterpm");
		}

		private void PanelInvite_Load(object sender, System.EventArgs e)
		{
			if (Mode.Equals(Modes.None))
			{
				ContainerPage.SetPageTitle(String.Format("Invite {0} to a group...", ThisUsr.NickName));
				InviteUsrAnchor.HRef = ThisUsr.Url();
				InviteUsrAnchor.InnerText = ThisUsr.NickName;
				ThisUsr.MakeRollover(InviteUsrAnchor);
				Header.InnerText = String.Format("Invite {0} to a group...", ThisUsr.NickName);

				if (!Page.IsPostBack)
				{
					Query q = new Query();
					q.Columns = new ColumnSet(Group.Columns.Name, Group.Columns.K);
					q.QueryCondition = Usr.Current.GroupMemberQ;
					q.TableElement = Group.UsrMemberJoin;
					q.OrderBy = new OrderBy(Group.Columns.Name);
					GroupSet gs = new GroupSet(q);
					if (gs.Count == 0)
					{
						ChangePanel(PanelNoGroups);
					}
					else
					{
						GroupDropDown.DataSource = gs;
						GroupDropDown.DataTextField = "Name";
						GroupDropDown.DataValueField = "K";
						GroupDropDown.DataBind();
					}
				}
			}
		}

		void BindExpired()
		{
			List<Q> queryConditionList = new List<Q>();
			queryConditionList.Add(Promoter.EnabledQ);
			queryConditionList.Add(new Or(new Q(Promoter.Columns.SalesHold, false),
										  new Q(Promoter.Columns.SalesHold, QueryOperator.IsNull, null)));
			queryConditionList.Add(new Q(Promoter.Columns.SalesStatusExpires, QueryOperator.LessThan, DateTime.Now));
			queryConditionList.Add(new Or(new Q(Promoter.Columns.SalesNextCall, QueryOperator.LessThan, DateTime.Now), 
										  new Q(Promoter.Columns.SalesNextCall, QueryOperator.IsNull, null)));
			queryConditionList.Add(new Q(Promoter.Columns.SalesUsrK, Usr.Current.K));
			
			if(this.SectorFilterDropDownList.SelectedValue != "")
				queryConditionList.Add(new Q(Promoter.Columns.ClientSector, Convert.ToInt32(SectorFilterDropDownList.SelectedValue)));
			if(this.SalesEstimateFilterDropDownList.SelectedValue != "")
				queryConditionList.Add(new Q(Promoter.Columns.SalesEstimate, Convert.ToInt32(SalesEstimateFilterDropDownList.SelectedValue)));

			Query q = new Query(new And(queryConditionList.ToArray()));

			q.OrderBy = new OrderBy(
				new OrderBy(Promoter.Columns.SalesNextCall, OrderBy.OrderDirection.Descending),
				new OrderBy(Promoter.Columns.SalesEstimate, OrderBy.OrderDirection.Descending),
				new OrderBy(Promoter.Columns.DateTimeSignUp, OrderBy.OrderDirection.Descending));
			PromoterSet ps = new PromoterSet(q);

			ExpiredDataGrid.AllowPaging = (ps.Count > ExpiredDataGrid.PageSize);
			ExpiredDataGrid.DataSource = ps;
			ExpiredDataGrid.DataBind();
		}

		private void LoadCurrentEventsWithTickets()
		{
			Query currentEventsWithTicketsQuery = new Query(new And(new Q(Bobs.TicketRun.Columns.PromoterK, CurrentPromoter.K),
																   new Q(Bobs.TicketRun.Columns.EndDateTime, QueryOperator.GreaterThanOrEqualTo, DateTime.Today.AddDays(-5)),
																   new Q(Bobs.TicketRun.Columns.SoldTickets, QueryOperator.GreaterThan, 0)));
			currentEventsWithTicketsQuery.TableElement = Bobs.TicketRun.EventJoin;
			currentEventsWithTicketsQuery.GroupBy = new GroupBy(new GroupBy(Event.Columns.K), new GroupBy(Event.Columns.DateTime), new GroupBy(Event.Columns.Name));
			currentEventsWithTicketsQuery.OrderBy = new OrderBy(Event.Columns.DateTime);
//			currentEventsWithTicketsQuery.ExtraSelectElements.Add("TicketsSold", "SUM([TicketRun].[SoldTickets])");

			currentEventsWithTicketsQuery.Columns = new ColumnSet(Event.Columns.K, Event.Columns.Name);
			EventSet currentEventsWithTickets = new EventSet(currentEventsWithTicketsQuery);

			this.NoTicketsP.Visible = false;
			if (currentEventsWithTickets.Count == 0)
			{
				this.NoTicketsP.Visible = true;
				this.HasTicketsP.Visible = false;
			}
			else if (currentEventsWithTickets.Count == 1)
			{
				// redirect to doorlist popup page
				Response.Redirect(currentEventsWithTickets[0].DoorlistUrl);
			}
			else
			{
				this.EventDropDownList.Items.Clear();
				foreach (Event ticketEvent in currentEventsWithTickets)
				{
					this.EventDropDownList.Items.Add(new ListItem(ticketEvent.Name, ticketEvent.K.ToString()));
				}
			}
		}

		public Dictionary<DateTime, int> GetCountOfLogItemForDates(Log.Items logItemType, List<DateTime> dates)
		{
			return Caching.Instances.Main.GetWithLocalCaching(
				String.Format("GetCountOfLogItemForDates(logItemType={0}, dates={1})", logItemType, string.Join(",", dates.ConvertAll(d => d.ToShortDateString()).ToArray())),
				() =>
				{
					var query = new Query();
					query.Columns = new ColumnSet(Log.Columns.Count, Log.Columns.Date);
					query.QueryCondition = new And(
												new Q(Log.Columns.Item, logItemType),
												   new Or(
													   dates.ConvertAll(date => new Q(Log.Columns.Date, date.Date)).ToArray()
												   )
											   );
					var ls = new LogSet(query);
					var results = new Dictionary<DateTime, int>();
					foreach (Log l in ls)
					{
						results.Add(l.Date, l.Count);
					}
					foreach (DateTime d in dates)
					{
						if (!results.ContainsKey(d.Date))
						{
							results.Add(d.Date, 0);
						}
					}
					return results;
				},
				5.Minutes(),
				1.Days()
			);
		}

		public void TopPlacesPanel_Load(object o, System.EventArgs e)
		{

			Query q = new Query();
			q.QueryCondition = new And(new Q(Place.Columns.CountryK, CurrentCountry.K), new Q(Place.Columns.Enabled, true));
			q.OrderBy = new OrderBy(new OrderBy(Place.Columns.TotalEvents, OrderBy.OrderDirection.Descending), new OrderBy(Place.Columns.Population, OrderBy.OrderDirection.Descending));
			q.TopRecords = 20;
			PlaceSet ps = new PlaceSet(q);
			if (ps.Count == 0)
			{
				TopPlacesPanel.Visible = false;
			}
			else
			{
				TopPlacesDataList.ItemTemplate = this.LoadTemplate("/Templates/Places/CountryTopPlacesList.ascx");
				TopPlacesDataList.DataSource = ps;
				TopPlacesDataList.DataBind();
			}
			if (ps.Count == 20)
			{
				TopPlacesAnchor.HRef = CurrentCountry.UrlApp("places");
				TopPlacesAnchor.InnerText = TopPlacesAnchor.InnerText.Replace("???", CurrentCountry.FriendlyName);
			}
			else
				TopPlacesAnchorPanel.Visible = false;
		}

		protected void Page_Load(object sender, EventArgs e)
		{
			if (!Page.IsPostBack)
			{
				CountryDrop.Items.Clear();
				CountryDrop.Items.Add(new ListItem("select here...", "0"));
				Query qTop = new Query();
				qTop.Columns = new ColumnSet(Country.Columns.FriendlyName, Country.Columns.K);
				qTop.OrderBy = new OrderBy(Country.Columns.TotalEvents, OrderBy.OrderDirection.Descending);
				qTop.QueryCondition = new Q(Country.Columns.Enabled, true);
				qTop.TopRecords = 10;
				CountrySet csTop = new CountrySet(qTop);
				CountryDrop.Items.Add(new ListItem("", "0"));
				CountryDrop.Items.Add(new ListItem("- POPULAR COUNTRIES -", "0"));
				foreach (Country c in csTop)
					CountryDrop.Items.Add(new ListItem(Cambro.Misc.Utility.Snip(c.FriendlyName, 25), c.K.ToString()));

				Query qAll = new Query();
				qAll.Columns = new ColumnSet(Country.Columns.FriendlyName, Country.Columns.K);
				qAll.OrderBy = new OrderBy(Country.Columns.FriendlyName);
				qAll.QueryCondition = new Q(Country.Columns.Enabled, true);
				CountrySet csAll = new CountrySet(qAll);
				CountryDrop.Items.Add(new ListItem("", "0"));
				CountryDrop.Items.Add(new ListItem("--- ALL COUNTRIES ---", "0"));
				foreach (Country c in csAll)
					CountryDrop.Items.Add(new ListItem(Cambro.Misc.Utility.Snip(c.FriendlyName, 25), c.K.ToString()));
			}
		}

		public void Template(object o, System.EventArgs e)
		{
			Cambro.Web.Helpers.WriteAlertHeader();

			Cambro.Web.Helpers.WriteAlert("Selecting xxx...", 1);
			Query q = new Query();
			//q.QueryCondition=???
			CommentSet bs = new CommentSet(q);
			for (int count = 0; count < bs.Count; count++)
			{
				Comment c = bs[count];

				try
				{
					// Do work here!
					c.Update();

					if (count % 10 == 0)
						Cambro.Web.Helpers.WriteAlert("Done " + count + "/" + bs.Count, 2);

				}
				catch(Exception ex)
				{
					Cambro.Web.Helpers.WriteAlert("Exception " + count + "/" + bs.Count + " - " + ex.ToString(), 3);
				}

				bs.Kill(count);

			}
			Cambro.Web.Helpers.WriteAlert("Done!", 3);
			Cambro.Web.Helpers.WriteAlertFooter();
		}