public static UsrSet GetCurrentSalesUsrsNameAndK(int[] salesTeams) { Q salesUsrsQueryCondition = new Q(true); if (salesTeams.Length > 0) { salesUsrsQueryCondition = new Q(Usr.Columns.SalesTeam, -1); foreach (int salesTeam in salesTeams) salesUsrsQueryCondition = new Or(salesUsrsQueryCondition, new Q(Usr.Columns.SalesTeam, salesTeam)); } Query salesPersonsQuery = new Query(new And(salesUsrsQueryCondition, new Q(Usr.Columns.SalesTeam, QueryOperator.GreaterThan, 0))); salesPersonsQuery.Columns = new ColumnSet(Usr.Columns.K, Usr.Columns.NickName, Usr.Columns.FirstName, Usr.Columns.LastName, Usr.Columns.SalesTeam); salesPersonsQuery.OrderBy = new OrderBy(Usr.Columns.FirstName, OrderBy.OrderDirection.Ascending); return new UsrSet(salesPersonsQuery); }
public void Init(Usr u) { AllMusicTypes = null; SelectedMusicTypes = null; GenericMusicTypes = null; SelectedPlaces = null; MusicQ = null; PlaceQ = null; #region Populate MusicQ and MusicTypes if (u.FavouriteMusicTypeK == 0 && u.MusicTypesFavouriteCount == 0) { MusicQ = new Q(true); } else { List<int> musicTypesK = new List<int>(); #region Add MusicTypes if (u.MusicTypesFavouriteCount > 0) { foreach (MusicType mt in u.MusicTypesFavourite) { if (!musicTypesK.Contains(mt.K)) { musicTypesK.Add(mt.K); AllMusicTypes.Add(mt.Order, mt); SelectedMusicTypes.Add(mt.Order, mt); AddMusicTypeChildren(mt, ref musicTypesK); #region Add the parent if (u.UpdateSendGenericMusic && mt.ParentK > 1 && !musicTypesK.Contains(mt.ParentK)) { musicTypesK.Add(mt.ParentK); AllMusicTypes.Add(mt.Parent.Order, mt.Parent); GenericMusicTypes.Add(mt.Parent.Order, mt.Parent); } #endregion } } } else { if (u.FavouriteMusicTypeK != 0) { musicTypesK.Add(u.FavouriteMusicTypeK); AllMusicTypes.Add(u.FavouriteMusicType.Order, u.FavouriteMusicType); SelectedMusicTypes.Add(u.FavouriteMusicType.Order, u.FavouriteMusicType); AddMusicTypeChildren(u.FavouriteMusicType, ref musicTypesK); } } if (u.UpdateSendGenericMusic && !musicTypesK.Contains(1)) { musicTypesK.Add(1); MusicType mtAllMusic = new MusicType(1); AllMusicTypes.Add(mtAllMusic.Order, mtAllMusic); GenericMusicTypes.Add(mtAllMusic.Order, mtAllMusic); } #endregion musicTypesK.Sort(); MusicQ = new Or(musicTypesK.ConvertAll<Q>(mtk => new Q(EventMusicType.Columns.MusicTypeK, mtk)).ToArray()); } #endregion #region Populate PlaceQ and SelectedPlaces List<int> placesK = new List<int>(); if (u.HomePlaceK > 0) { placesK.Add(u.HomePlaceK); SelectedPlaces.Add(u.Home.Name, u.Home); } foreach (Place p in u.PlacesVisit(null, 0)) { if (!placesK.Contains(p.K)) { placesK.Add(p.K); SelectedPlaces.Add(p.Name, p); } } if (placesK.Count > 0) { placesK.Sort(); PlaceQ = new Or(placesK.ConvertAll<Q>(pk => new Q(Venue.Columns.PlaceK, pk)).ToArray()); } else { PlaceQ = new Q(false); } #endregion }
private void DisplayDoorList() { if (!EndAllTicketRunsPanel.Visible) { List<int> ticketRunKs = new List<int>(); foreach (RepeaterItem ri in TicketRunSelectRepeater.Items) { if (((CheckBox)ri.FindControl("TicketRunCheckBox")).Checked) { ticketRunKs.Add(Convert.ToInt32(((TextBox)ri.FindControl("TicketRunKTextBox")).Text)); ((HtmlTableRow)ri.FindControl("TicketRunRow")).Attributes.Remove("class"); } else ((HtmlTableRow)ri.FindControl("TicketRunRow")).Attributes.Add("class", "HideOnPrint"); } if (ticketRunKs.Count == 0) { DoorlistGridView.Visible = false; HideOnPrintP.Visible = false; NoTicketsToDisplay = true; return; } //TemplateField firstNameColumn = (TemplateField)DoorlistGridView.Columns[0]; //TemplateField lastNameColumn = (TemplateField)DoorlistGridView.Columns[1]; //TemplateField codeColumn = (TemplateField)DoorlistGridView.Columns[2]; //TemplateField cardNumberEndColumn = (TemplateField)DoorlistGridView.Columns[3]; //TemplateField ticketRunNameColumn = (TemplateField)DoorlistGridView.Columns[4]; //TemplateField ticketsColumn = (TemplateField)DoorlistGridView.Columns[5]; //TemplateField cv2Column = (TemplateField)DoorlistGridView.Columns[6]; //DoorlistGridView.Columns.Clear(); Q selectTicketRunQ = new Q(); selectTicketRunQ = new Q(Ticket.Columns.TicketRunK, ticketRunKs[0]); for (int i = 1; i < ticketRunKs.Count; i++) { selectTicketRunQ = new Or(selectTicketRunQ, new Q(Ticket.Columns.TicketRunK, ticketRunKs[i])); } Query ticketQuery = new Query(new And(selectTicketRunQ, Ticket.ValidTicketsQ, new Or(new Q(Ticket.Columns.IsFraud, QueryOperator.IsNull, null), new Q(Ticket.Columns.IsFraud, false)) )); ticketQuery.Columns = new ColumnSet(Ticket.Columns.K, Ticket.Columns.FirstName, Ticket.Columns.LastName, Ticket.Columns.Code, Ticket.Columns.CardNumberEnd, Ticket.Columns.Quantity, Ticket.Columns.TicketRunK, Ticket.Columns.CardCheckedByPromoter, Ticket.Columns.CardCheckAttempts, Promoter.Columns.WillCheckCardsForPurchasedTickets); ticketQuery.TableElement = new Join( new Join(Ticket.Columns.TicketRunK, TicketRun.Columns.K), Promoter.Columns.K, TicketRun.Columns.PromoterK); ticketQuery.ExtraSelectElements.Add("TicketRunPriceName", "CASE WHEN LEN([TicketRun].[Name]) > 0 THEN '£' + CONVERT(varchar(9), CONVERT(money,[TicketRun].[Price])) + ' : ' + [TicketRun].[Name] ELSE '£' + CONVERT(varchar(9), CONVERT(money,[TicketRun].[Price])) END"); for (int i = 0; i < 8; i++) { DoorlistGridView.Columns[i].Visible = false; } TemplateField codeColumn = null; if (ListOrderBy.Equals(DoorlistOrderBy.FirstName)) { ticketQuery.OrderBy = new OrderBy(new OrderBy(Ticket.Columns.FirstName), new OrderBy(Ticket.Columns.LastName)); //DoorlistGridView.Columns.Add(firstNameColumn); //DoorlistGridView.Columns.Add(lastNameColumn); //DoorlistGridView.Columns.Add(codeColumn); //DoorlistGridView.Columns.Add(cardNumberEndColumn); DoorlistGridView.Columns[0].Visible = true; DoorlistGridView.Columns[1].Visible = true; codeColumn = (TemplateField)DoorlistGridView.Columns[2]; DoorlistGridView.Columns[2].Visible = true; DoorlistGridView.Columns[3].Visible = true; } else if (ListOrderBy.Equals(DoorlistOrderBy.LastName)) { ticketQuery.OrderBy = new OrderBy(new OrderBy(Ticket.Columns.LastName), new OrderBy(Ticket.Columns.FirstName)); //DoorlistGridView.Columns.Add(lastNameColumn); //DoorlistGridView.Columns.Add(firstNameColumn); //DoorlistGridView.Columns.Add(codeColumn); //DoorlistGridView.Columns.Add(cardNumberEndColumn); DoorlistGridView.Columns[1].Visible = true; DoorlistGridView.Columns[4].Visible = true; codeColumn = (TemplateField)DoorlistGridView.Columns[6]; DoorlistGridView.Columns[6].Visible = true; DoorlistGridView.Columns[7].Visible = true; } else if (ListOrderBy.Equals(DoorlistOrderBy.CardNumberEnd)) { ticketQuery.OrderBy = new OrderBy(new OrderBy(Ticket.Columns.CardNumberEnd), new OrderBy(Ticket.Columns.FirstName), new OrderBy(Ticket.Columns.LastName)); //DoorlistGridView.Columns.Add(cardNumberEndColumn); //DoorlistGridView.Columns.Add(firstNameColumn); //DoorlistGridView.Columns.Add(lastNameColumn); //DoorlistGridView.Columns.Add(codeColumn); DoorlistGridView.Columns[3].Visible = true; DoorlistGridView.Columns[4].Visible = true; DoorlistGridView.Columns[5].Visible = true; codeColumn = (TemplateField)DoorlistGridView.Columns[6]; DoorlistGridView.Columns[6].Visible = true; } else if (ListOrderBy.Equals(DoorlistOrderBy.Code)) { ticketQuery.OrderBy = new OrderBy(new OrderBy(Ticket.Columns.Code), new OrderBy(Ticket.Columns.FirstName), new OrderBy(Ticket.Columns.LastName)); //DoorlistGridView.Columns.Add(codeColumn); //DoorlistGridView.Columns.Add(firstNameColumn); //DoorlistGridView.Columns.Add(lastNameColumn); //DoorlistGridView.Columns.Add(cardNumberEndColumn); codeColumn = (TemplateField)DoorlistGridView.Columns[2]; DoorlistGridView.Columns[2].Visible = true; DoorlistGridView.Columns[4].Visible = true; DoorlistGridView.Columns[5].Visible = true; DoorlistGridView.Columns[7].Visible = true; } //DoorlistGridView.Columns.Add(ticketRunNameColumn); //DoorlistGridView.Columns.Add(ticketsColumn); //DoorlistGridView.Columns.Add(cv2Column); TicketSet doorlistTickets = new TicketSet(ticketQuery); if (doorlistTickets.Count == 0) { DoorlistGridView.Visible = false; NoTicketsToDisplay = true; uiNoTickets.Visible = true; HideOnPrintP.Visible = false; return; } HideOnPrintP.Visible = true; uiNoTickets.Visible = false; DoorlistGridView.Visible = true; codeColumn.Visible = false; TemplateField cv2Column = (TemplateField)DoorlistGridView.Columns[10]; cv2Column.Visible = false; foreach (Ticket ticket in doorlistTickets) { if (codeColumn.Visible == false && ticket.Code.Length > 0) { codeColumn.Visible = true; } if (cv2Column.Visible == false && ticket.ExtraSelectElements["Promoter_WillCheckCardsForPurchasedTickets"].ToString() == bool.TrueString) { cv2Column.Visible = true; } if (codeColumn.Visible && cv2Column.Visible) break; } doorlistTickets.Reset(); if (codeColumn.Visible) AddCodeListItemToOrderByDropDownList(); DoorlistGridView.DataSource = doorlistTickets; DoorlistGridView.DataBind(); } }
public Event WhereAmI(DateTime dateTime) { //First try to match exactly Q eventStartTime = null; if (dateTime.Hour < 10) { //before 9:00am - morning //all day event today OR evening event yesterday OR morning event today eventStartTime = new Or( new And( new Q(Event.Columns.DateTime, dateTime.Date.AddDays(-1)), new Q(Event.Columns.StartTime, Event.StartTimes.Evening) ), new And( new Q(Event.Columns.DateTime, dateTime.Date), new Q(Event.Columns.StartTime, Event.StartTimes.Morning) ) ); } else if (dateTime.Hour < 18) { //before 6:00pm but after 10:00am - late morning / early afternoon //all day event today OR morning event today OR daytime event today eventStartTime = new Or( new And( new Q(Event.Columns.DateTime, dateTime.Date), new Q(Event.Columns.StartTime, Event.StartTimes.Morning) ), new And( new Q(Event.Columns.DateTime, dateTime.Date), new Q(Event.Columns.StartTime, Event.StartTimes.Daytime) ) ); } else if (dateTime.Hour < 20) { //before 8:00pm but after 6:00pm - afternoon / evening //all day event today OR daytime event today OR evening event today eventStartTime = new Or( new And( new Q(Event.Columns.DateTime, dateTime.Date), new Q(Event.Columns.StartTime, Event.StartTimes.Daytime) ), new And( new Q(Event.Columns.DateTime, dateTime.Date), new Q(Event.Columns.StartTime, Event.StartTimes.Evening) ) ); } else { //after 8pm - evening - all day event today OR evening event today eventStartTime = new And( new Q(Event.Columns.DateTime, dateTime.Date), new Q(Event.Columns.StartTime, Event.StartTimes.Evening) ); } Query qExact = new Query(); qExact.TableElement = Event.UsrAttendedJoin; qExact.QueryCondition = new And( new Q(Usr.Columns.K, this.K), eventStartTime ); EventSet esExact = new EventSet(qExact); if (esExact.Count > 1) return null; else if (esExact.Count == 1) return esExact[0]; else { //loose matching //Any event today or yesterday eventStartTime = new Or( new Q(Event.Columns.DateTime, dateTime.Date), new Q(Event.Columns.DateTime, dateTime.Date.AddDays(-1)) ); Query qLoose = new Query(); qLoose.TableElement = Event.UsrAttendedJoin; qLoose.QueryCondition = new And( new Q(Usr.Columns.K, this.K), eventStartTime ); EventSet esLoose = new EventSet(qLoose); if (esLoose.Count > 1) return null; else if (esLoose.Count == 1) return esLoose[0]; else { //very loose matching //any event tomorrow, today, yesterday, or the day before eventStartTime = new Or( new Q(Event.Columns.DateTime, dateTime.Date.AddDays(1)), new Q(Event.Columns.DateTime, dateTime.Date), new Q(Event.Columns.DateTime, dateTime.Date.AddDays(-1)), new Q(Event.Columns.DateTime, dateTime.Date.AddDays(-2)) ); Query qVeryLoose = new Query(); qVeryLoose.TableElement = Event.UsrAttendedJoin; qVeryLoose.QueryCondition = new And( new Q(Usr.Columns.K, this.K), eventStartTime ); EventSet esVeryLoose = new EventSet(qVeryLoose); if (esVeryLoose.Count > 1) return null; else if (esVeryLoose.Count == 1) return esVeryLoose[0]; else return null; } } }
private double TotalSalesAmountPaid(DateTime fromDate, DateTime toDate, UsrSet salesUsrs) { fromDate = new DateTime(fromDate.Year, fromDate.Month, fromDate.Day); toDate = new DateTime(toDate.Year, toDate.Month, toDate.Day).AddDays(1); Q salesUsrsQueryCondition = new Q(Invoice.Columns.SalesUsrK, salesUsrs[0].K); for (int i = 1; i < salesUsrs.Count; i++) { salesUsrsQueryCondition = new Or(salesUsrsQueryCondition, new Q(Invoice.Columns.SalesUsrK, salesUsrs[i].K)); } Query totalSalesAmountQuery = new Query(new And(salesUsrsQueryCondition, new Q(Invoice.Columns.Paid, true), new Q(Invoice.Columns.SalesUsrAmount, QueryOperator.GreaterThan, 0), new Q(Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate), new Q(Invoice.Columns.PaidDateTime, QueryOperator.LessThan, toDate))); totalSalesAmountQuery.ExtraSelectElements.Add("TotalPrice", "SUM(Price)"); totalSalesAmountQuery.Columns = new ColumnSet(); InvoiceSet invoices = new InvoiceSet(totalSalesAmountQuery); double result = 0; try { if (invoices.Count > 0) result = Convert.ToDouble(invoices[0].ExtraSelectElements["TotalPrice"]); } catch (Exception) { } return result; }
public static VenueSet SimilarVenuesStatic(string name, Place place, int excludeVenueK, string postCode) { //split name into words and get all > 3 chars... ArrayList al = new ArrayList(); ArrayList commonWords = new ArrayList(Q.CommonWords); commonWords.Add("bar"); commonWords.Add("club"); string[] words = name.Split(' '); int wordsCount = 0; foreach (string word in words) { if (!commonWords.Contains(word.ToLower()) && word.Length>1) { al.Add(new Q(Columns.Name,QueryOperator.TextContains,word)); wordsCount++; } } Q wordsOr = new Q(true); if (wordsCount>0) wordsOr = new Or((Q[])al.ToArray(typeof(Q))); else wordsOr = new Q(false); //10 nearest places also Query qPlaces = new Query(); qPlaces.TopRecords=10; qPlaces.OrderBy=place.NearestPlacesOrderBy; PlaceSet ps = new PlaceSet(qPlaces); ArrayList al1 = new ArrayList(); foreach (Place p in ps) { al1.Add(new Q(Columns.PlaceK,p.K)); } Or placesOr = new Or((Q[])al1.ToArray(typeof(Q))); Q thisVenue = new Q(true); if (excludeVenueK>0) thisVenue = new Q(Columns.K,QueryOperator.NotEqualTo,excludeVenueK); Q postCodeQ = null; if (postCode.Length>0) postCodeQ = new Q(Columns.Postcode,postCode); else postCodeQ = new Q(false); Query qSimilar = new Query(); qSimilar.QueryCondition = new And(new Or(new And(wordsOr, placesOr), postCodeQ), thisVenue); VenueSet vsSimilar = new VenueSet(qSimilar); return vsSimilar; }
public Dictionary<string, int> ResolveUsrsFromMultiBuddyChooserValues(IEnumerable<string> values, int restrictionGroupK, int threadK, Q restrictionGroupQ) { Dictionary<string, int> hash = new Dictionary<string, int>(); foreach (var item in values) { if (item.StartsWith("{'email'")) { var esv = javaScriptSerializer.Deserialize<EmailSuggestionValue>(item); hash[esv.email] = CreateUsrFromEmail(esv.email, true); }else if (item.StartsWith("{'emails'")) { var esv = javaScriptSerializer.Deserialize<EmailsSuggestionValue>(item); string[] emails = GlobalObject.unescape(esv.emails).Split(' '); foreach (var email in emails) { hash[email] = CreateUsrFromEmail(email, esv.buddies); } } else if (item.StartsWith("{'MusicTypeK'")) { var musicTypeAndPlaceK = javaScriptSerializer.Deserialize<MusicTypeKAndPlaceK>(item); Query q = new Query(); Join j = new Join( Usr.BuddyUsrJoin, new TableElement(TablesEnum.UsrMusicTypeFavourite), QueryJoinType.Left, Usr.Columns.K, UsrMusicTypeFavourite.Columns.UsrK); q.TableElement = new Join( j, new TableElement(TablesEnum.UsrPlaceVisit), QueryJoinType.Left, Usr.Columns.K, UsrPlaceVisit.Columns.UsrK); if (threadK > 0) { q.TableElement = new Join( q.TableElement, new TableElement(TablesEnum.ThreadUsr), QueryJoinType.Left, new And( new Q(Usr.Columns.K, ThreadUsr.Columns.UsrK, true), new Q(ThreadUsr.Columns.ThreadK, threadK) ) ); } else if (restrictionGroupK > 0) { q.TableElement = new Join( q.TableElement, new TableElement(TablesEnum.GroupUsr), QueryJoinType.Left, new And( new Q(Usr.Columns.K, GroupUsr.Columns.UsrK, true), new Q(GroupUsr.Columns.GroupK, restrictionGroupK) ) ); } Q placeQ = new Q(true); if (musicTypeAndPlaceK.PlaceK > 0) { placeQ = new Or( new Q(Usr.Columns.HomePlaceK, musicTypeAndPlaceK.PlaceK), new Q(UsrPlaceVisit.Columns.PlaceK, musicTypeAndPlaceK.PlaceK)); } Q musicQ = new Q(true); if (musicTypeAndPlaceK.MusicTypeK > 1) { ArrayList musicQs = new ArrayList(); MusicType mt = new MusicType(musicTypeAndPlaceK.MusicTypeK); musicQs.Add(new Q(Usr.Columns.FavouriteMusicTypeK, 1)); musicQs.Add(new Q(UsrMusicTypeFavourite.Columns.MusicTypeK, 1)); musicQs.Add(new Q(Usr.Columns.FavouriteMusicTypeK, musicTypeAndPlaceK.MusicTypeK)); musicQs.Add(new Q(UsrMusicTypeFavourite.Columns.MusicTypeK, musicTypeAndPlaceK.MusicTypeK)); foreach (MusicType mtChild in mt.Children) { musicQs.Add(new Q(Usr.Columns.FavouriteMusicTypeK, mtChild.K)); musicQs.Add(new Q(UsrMusicTypeFavourite.Columns.MusicTypeK, mtChild.K)); } musicQ = new Or((Q[])musicQs.ToArray(typeof(Q))); } Q restrictionQ = new Q(true); if (threadK > 0) { restrictionQ = new Q(ThreadUsr.Columns.UsrK, QueryOperator.IsNull, null); } else if (restrictionGroupK > 0) { restrictionQ = restrictionGroupQ; } q.QueryCondition = new And( new Q(Buddy.Columns.BuddyUsrK, Usr.Current.K), new Q(Buddy.Columns.FullBuddy, true), new Q(Buddy.Columns.CanBuddyInvite, true), musicQ, placeQ); if (restrictionGroupQ != null) q.QueryCondition = new And(q.QueryCondition, restrictionGroupQ); q.Columns = new ColumnSet(Usr.Columns.NickName, Usr.Columns.K, Usr.Columns.Pic, Usr.Columns.FacebookUID, Usr.Columns.Email); q.Distinct = true; q.OrderBy = new OrderBy(Usr.Columns.NickName); q.DistinctColumn = Usr.Columns.K; UsrSet us = new UsrSet(q); foreach (Usr u in us) { hash[u.NickName] = u.K; } } else if (item.IsNumeric()) { int usrK = int.Parse(item); Usr usr = new Usr(usrK); if (usr.NickName == "") { hash["User" + usr.K] = usrK; } else { hash[usr.NickName] = usrK; } } else { throw new NotImplementedException(); } } return hash; }
void BindMemberAdminOptionsDataGrid() { if (HasMemberAdmin) { #region Status level Q ArrayList alStatusLevels = new ArrayList(); if (MemberAdminOptionsMembersCheckBox.Checked) { alStatusLevels.Add(new Q(GroupUsr.Columns.Status, GroupUsr.StatusEnum.Member)); } if (MemberAdminOptionsRequestCheckBox.Checked) { alStatusLevels.Add(new Q(GroupUsr.Columns.Status, GroupUsr.StatusEnum.Request)); alStatusLevels.Add(new Q(GroupUsr.Columns.Status, GroupUsr.StatusEnum.Recommend)); } if (MemberAdminOptionsInvitedCheckBox.Checked) { alStatusLevels.Add(new Q(GroupUsr.Columns.Status, GroupUsr.StatusEnum.Invite)); alStatusLevels.Add(new Q(GroupUsr.Columns.Status, GroupUsr.StatusEnum.InviteRejected)); } if (MemberAdminOptionsRejectedCheckBox.Checked) { alStatusLevels.Add(new Q(GroupUsr.Columns.Status, GroupUsr.StatusEnum.RecommendRejected)); alStatusLevels.Add(new Q(GroupUsr.Columns.Status, GroupUsr.StatusEnum.RequestRejected)); } if (MemberAdminOptionsExitedCheckBox.Checked) { alStatusLevels.Add(new Q(GroupUsr.Columns.Status, GroupUsr.StatusEnum.Exited)); } if (MemberAdminOptionsBarredCheckBox.Checked) { alStatusLevels.Add(new Q(GroupUsr.Columns.Status, GroupUsr.StatusEnum.Barred)); } Q qStatusLevels = new Q(false); MemberAdminOptionsSearchNoneSelected.Visible = alStatusLevels.Count == 0 && MemberAdminOptionsSearchClicked; if (alStatusLevels.Count > 0) { qStatusLevels = new Or((Q[])alStatusLevels.ToArray(typeof(Q))); } #endregion #region Nickname Q Q qNickName = new Q(true); if (MemberAdminOptionsSearchTextBox.Text.Trim().Length > 0) { qNickName = new Q(new Column(GroupUsr.Columns.UsrK, Usr.Columns.NickName), QueryOperator.TextContains, MemberAdminOptionsSearchTextBox.Text.Trim()); } #endregion Query q = new Query(); q.QueryCondition = new And( new Q(GroupUsr.Columns.GroupK, CurrentGroup.K), qStatusLevels, qNickName ); q.Columns = new ColumnSet( GroupUsr.Columns.UsrK, GroupUsr.Columns.Status, GroupUsr.Columns.Moderator, GroupUsr.Columns.NewsAdmin, GroupUsr.Columns.MemberAdmin, GroupUsr.Columns.Owner, GroupUsr.Columns.StatusChangeDateTime, GroupUsr.Columns.StatusChangeUsrK, GroupUsr.Columns.InviteUsrK, new JoinedColumnSet( GroupUsr.Columns.UsrK, Usr.LinkColumns ), new JoinedColumnSet( GroupUsr.Columns.StatusChangeUsrK, Usr.LinkColumns ), new JoinedColumnSet( GroupUsr.Columns.InviteUsrK, Usr.LinkColumns ) ); q.TableElement = GroupUsr.UsrAndStatusChangeUsrJoin; q.OrderBy = new OrderBy(new OrderBy(GroupUsr.Columns.StatusChangeDateTime, OrderBy.OrderDirection.Descending), new OrderBy(new Column(GroupUsr.Columns.UsrK, Usr.Columns.NickName))); GroupUsrSet gus = new GroupUsrSet(q); MemberAdminOptionsSearchNoResults.Visible = gus.Count == 0 && alStatusLevels.Count > 0; MemberAdminOptionsDataGridP.Visible = gus.Count > 0; if (gus.Count > 0) { MemberAdminOptionsDataGrid.AllowPaging = (gus.Count > MemberAdminOptionsDataGrid.PageSize); MemberAdminOptionsDataGrid.DataSource = gus; MemberAdminOptionsDataGrid.DataBind(); } } }
private void GenerateSalesCallsReport(DateTime fromDate, DateTime toDate, DateGrouping dateGrouping, string salesUsrK) { Page.Validate(""); if (Page.IsValid) { this.DateRangeValueLabel.Text = fromDate.ToString("dd/MM/yy") + " to " + toDate.ToString("dd/MM/yy"); this.DateRangeLabel.Visible = true; this.DateRangeValueLabel.Visible = true; int startOfWeek = 1; // Sunday = 0, Monday = 1 fromDate = new DateTime(fromDate.Year, fromDate.Month, fromDate.Day); toDate = new DateTime(toDate.Year, toDate.Month, toDate.Day); if (toDate >= fromDate) { int numberOfDateGroupings = 0; if (dateGrouping.Equals(DateGrouping.Daily)) { toDate = new DateTime(toDate.Year, toDate.Month, toDate.Day).AddDays(1); numberOfDateGroupings = ((TimeSpan)(toDate - fromDate)).Days; } else if (dateGrouping.Equals(DateGrouping.Weekly)) { fromDate = Utilities.GetStartOfWeek(fromDate); toDate = Utilities.GetEndOfWeek(toDate); toDate = new DateTime(toDate.Year, toDate.Month, toDate.Day).AddDays(1); numberOfDateGroupings = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(((TimeSpan)(toDate - fromDate)).Days) / 7d)); } else if (dateGrouping.Equals(DateGrouping.Monthly)) { fromDate = Utilities.GetStartOfMonth(fromDate); toDate = Utilities.GetEndOfMonth(toDate); toDate = new DateTime(toDate.Year, toDate.Month, toDate.Day).AddDays(1); numberOfDateGroupings = (toDate.AddDays(-1).Year - fromDate.Year) * 12 + (toDate.AddDays(-1).Month - fromDate.Month) + 1; } List<UsrDataHolder> selectedSalesUsrs = SalesUsrs; if (salesUsrK != "0") { selectedSalesUsrs = new List<UsrDataHolder>(); if (salesUsrK.IndexOf("team") == 0) { UsrSet salesTeam = Usr.GetCurrentSalesUsrsNameAndK(Convert.ToInt32(salesUsrK.Replace("team", ""))); foreach (Usr salesUsr in salesTeam) { selectedSalesUsrs.Add(new UsrDataHolder(salesUsr)); } } else { selectedSalesUsrs.Add(SalesUsrs[GetSalesPersonIndexNumber(SalesUsrs, Convert.ToInt32(salesUsrK))]); } } if (selectedSalesUsrs.Count > 0) { Q salesUsrsQueryCondition = new Q(SalesCall.Columns.UsrK, selectedSalesUsrs[0].K); Q invoiceSalesUsrsQueryCondition = new Q(Invoice.Columns.SalesUsrK, selectedSalesUsrs[0].K); Q promoterAddedByQueryCondition = new Q(Promoter.Columns.AddedByUsrK, selectedSalesUsrs[0].K); for (int i = 1; i < selectedSalesUsrs.Count; i++) { promoterAddedByQueryCondition = new Or(promoterAddedByQueryCondition, new Q(Promoter.Columns.AddedByUsrK, selectedSalesUsrs[i].K)); salesUsrsQueryCondition = new Or(salesUsrsQueryCondition, new Q(SalesCall.Columns.UsrK, selectedSalesUsrs[i].K)); invoiceSalesUsrsQueryCondition = new Or(invoiceSalesUsrsQueryCondition, new Q(Invoice.Columns.SalesUsrK, selectedSalesUsrs[i].K)); } Q salesCallDateRangeQueryCondition = new And(new Q(SalesCall.Columns.IsCall, true), new Q(SalesCall.Columns.DateTimeStart, QueryOperator.GreaterThanOrEqualTo, fromDate), new Q(SalesCall.Columns.DateTimeStart, QueryOperator.LessThan, toDate)); Q promoterDateRangeQueryCondition = new And( new Q(Promoter.Columns.AddedMethod, Promoter.AddedMedhods.SalesUser), new Q(Promoter.Columns.DateTimeSignUp, QueryOperator.GreaterThanOrEqualTo, fromDate), new Q(Promoter.Columns.DateTimeSignUp, QueryOperator.LessThan, toDate)); #region Sales Calls: Total Money Query totalSalesAmountQuery = new Query(new And(invoiceSalesUsrsQueryCondition, new Q(Invoice.Columns.SalesUsrAmount, QueryOperator.NotEqualTo, 0), new Or(new And(new Q(Invoice.Columns.Type, Invoice.Types.Invoice), new Q(Invoice.Columns.Paid, true), new Q(Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate), new Q(Invoice.Columns.PaidDateTime, QueryOperator.LessThan, toDate), new Q(Usr.Columns.SalesTeam, QueryOperator.NotEqualTo, Usr.SalesTeams.CorporateSalesTeam)), new And(new Q(Invoice.Columns.Type, Invoice.Types.Invoice), new Q(Invoice.Columns.TaxDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate), new Q(Invoice.Columns.TaxDateTime, QueryOperator.LessThan, toDate), new Q(Usr.Columns.SalesTeam, Usr.SalesTeams.CorporateSalesTeam)), new And(new Q(Invoice.Columns.Type, Invoice.Types.Credit), new Q(Invoice.Columns.CreatedDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate), new Q(Invoice.Columns.CreatedDateTime, QueryOperator.LessThan, toDate))))); totalSalesAmountQuery.TableElement = new Join(Invoice.Columns.SalesUsrK, Usr.Columns.K); totalSalesAmountQuery.ExtraSelectElements.Add("TotalSales", "SUM(SalesUsrAmount)"); totalSalesAmountQuery.Columns = new ColumnSet(Invoice.Columns.SalesUsrK); if (dateGrouping.Equals(DateGrouping.Daily)) { totalSalesAmountQuery.ExtraSelectElements.Add("Date", "CONVERT(datetime,CONVERT(varchar(2),DAY(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))) + '/' + CONVERT(varchar(2),MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))) + '/' + CONVERT(varchar(4),Year(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))))"); totalSalesAmountQuery.OrderBy = new OrderBy("[Usr].[FirstName], YEAR(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) desc, MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) desc, DAY(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) desc"); totalSalesAmountQuery.GroupBy = new GroupBy("[Usr].[FirstName], DAY(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)), MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)), YEAR(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)), [Invoice].[SalesUsrK]"); } else if (dateGrouping.Equals(DateGrouping.Weekly)) { totalSalesAmountQuery.ExtraSelectElements.Add("Date", "CONVERT(dateTime,(CONVERT(varchar(2),DAY(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(2),MONTH(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(4),YEAR(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))))))"); totalSalesAmountQuery.OrderBy = new OrderBy("[Usr].[FirstName], CONVERT(dateTime,(CONVERT(varchar(2),DAY(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(2),MONTH(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(4),YEAR(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))))) desc"); totalSalesAmountQuery.GroupBy = new GroupBy("[Usr].[FirstName], CONVERT(dateTime,(CONVERT(varchar(2),DAY(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(2),MONTH(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(4),YEAR(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))))), [Invoice].[SalesUsrK]"); } else if (dateGrouping.Equals(DateGrouping.Monthly)) { totalSalesAmountQuery.ExtraSelectElements.Add("Date", "CONVERT(datetime,'1/' + CONVERT(varchar(2),MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))) + '/' + CONVERT(varchar(4),Year(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))))"); totalSalesAmountQuery.OrderBy = new OrderBy("[Usr].[FirstName], YEAR(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) desc, MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) desc"); totalSalesAmountQuery.GroupBy = new GroupBy("[Usr].[FirstName], MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)), YEAR(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)), [Invoice].[SalesUsrK]"); } InvoiceSet salesInvoices = new InvoiceSet(totalSalesAmountQuery); #endregion #region Sales Calls: SalesUsrK, Minutes, and Total calls Query salesCallMinutesQuery = new Query(new And(salesCallDateRangeQueryCondition, salesUsrsQueryCondition)); salesCallMinutesQuery.ExtraSelectElements.Add("Minutes", "SUM([SalesCall].[Duration])"); salesCallMinutesQuery = PopulateSalesCallQuery(salesCallMinutesQuery, dateGrouping, startOfWeek); SalesCallSet salesCallsMinutes = new SalesCallSet(salesCallMinutesQuery); #endregion #region Promoters: New leads Query promotersNewLeadsQuery = new Query(new And(promoterDateRangeQueryCondition, promoterAddedByQueryCondition)); promotersNewLeadsQuery = PopulatePromotersQuery(promotersNewLeadsQuery, dateGrouping, startOfWeek); PromoterSet promotersNewLeads = new PromoterSet(promotersNewLeadsQuery); #endregion #region Sales Calls: New leads Query salesCallNewLeadsQuery = new Query(new And(salesCallDateRangeQueryCondition, salesUsrsQueryCondition, new Q(SalesCall.Columns.IsCallToNewLead, true))); salesCallNewLeadsQuery = PopulateSalesCallQuery(salesCallNewLeadsQuery, dateGrouping, startOfWeek); SalesCallSet salesCallsNewLeads = new SalesCallSet(salesCallNewLeadsQuery); #endregion #region Sales Calls: Effective Query salesCallEffectiveQuery = new Query(new And(salesCallDateRangeQueryCondition, salesUsrsQueryCondition, new Q(SalesCall.Columns.Effective, true))); salesCallEffectiveQuery = PopulateSalesCallQuery(salesCallEffectiveQuery, dateGrouping, startOfWeek); SalesCallSet salesCallsEffective = new SalesCallSet(salesCallEffectiveQuery); #endregion #region Sales Calls: Cold Query salesCallColdQuery = new Query(new And(salesCallDateRangeQueryCondition, salesUsrsQueryCondition, new Q(SalesCall.Columns.Type, SalesCall.Types.Cold))); salesCallColdQuery = PopulateSalesCallQuery(salesCallColdQuery, dateGrouping, startOfWeek); SalesCallSet salesCallsCold = new SalesCallSet(salesCallColdQuery); #endregion #region Sales Calls: Followup Query salesCallFollowupQuery = new Query(new And(salesCallDateRangeQueryCondition, salesUsrsQueryCondition, new Q(SalesCall.Columns.Type, SalesCall.Types.ProactiveFollowUp))); salesCallFollowupQuery = PopulateSalesCallQuery(salesCallFollowupQuery, dateGrouping, startOfWeek); SalesCallSet salesCallsFollowup = new SalesCallSet(salesCallFollowupQuery); #endregion #region Sales Calls: Active Query salesCallActiveQuery = new Query(new And(salesCallDateRangeQueryCondition, salesUsrsQueryCondition, new Q(SalesCall.Columns.Type, SalesCall.Types.Active))); salesCallActiveQuery = PopulateSalesCallQuery(salesCallActiveQuery, dateGrouping, startOfWeek); SalesCallSet salesCallsActive = new SalesCallSet(salesCallActiveQuery); #endregion #region Table this.SalesStatsResultsTable.Visible = false; this.SalesCallsResultsTable.Visible = true; HtmlTable salesCallsTable = this.SalesCallsResultsTable; salesCallsTable.Rows.Clear(); #endregion #region Header Rows HtmlTableRow headerRow1 = new HtmlTableRow(); headerRow1.Attributes.Add("class", "dataGrid1stHeader"); salesCallsTable.Rows.Add(headerRow1); HtmlTableCell[] header1TableCells = new HtmlTableCell[6]; header1TableCells[0] = new HtmlTableCell("td"); header1TableCells[0].RowSpan = 2; header1TableCells[0].InnerHtml = "Date"; for (int i = 1; i < header1TableCells.Length; i++) { header1TableCells[i] = new HtmlTableCell("th"); header1TableCells[i].ColSpan = selectedSalesUsrs.Count; header1TableCells[i].Align = "center"; header1TableCells[i].Attributes.Add("class", "dataGridColumnDivider"); } header1TableCells[1].InnerHtml = "Total sales"; header1TableCells[2].InnerHtml = "<b>Total calls</b>"; header1TableCells[3].InnerHtml = "Minutes per call"; header1TableCells[4].InnerHtml = "<b>New leads</b>"; header1TableCells[5].InnerHtml = "Calls to new leads"; //header1TableCells[4].InnerHtml = "<nobr>Effective calls</nobr>"; //header1TableCells[7].InnerHtml = "<nobr>Cold (%)</nobr>"; //header1TableCells[8].InnerHtml = "<nobr>Followup (%)</nobr>"; //header1TableCells[9].InnerHtml = "<nobr>Active (%)</nobr>"; foreach (HtmlTableCell tc in header1TableCells) headerRow1.Cells.Add(tc); HtmlTableRow headerRow2 = new HtmlTableRow(); headerRow2.Attributes.Add("class", "dataGrid2ndHeader"); salesCallsTable.Rows.Add(headerRow2); HtmlTableCell[] header2TableCells = new HtmlTableCell[selectedSalesUsrs.Count * (header1TableCells.Length - 1)]; for (int i = 0; i < header2TableCells.Length; i++) { header2TableCells[i] = new HtmlTableCell("th"); header2TableCells[i].InnerHtml = "<b>" + selectedSalesUsrs[i % selectedSalesUsrs.Count].FirstName + "</b>"; header2TableCells[i].Align = "right"; header2TableCells[i].Width = "10"; if (i % selectedSalesUsrs.Count == 0) { header2TableCells[i].Attributes.Add("class", "dataGridColumnDivider"); } headerRow2.Cells.Add(header2TableCells[i]); } #endregion #region Data Rows HtmlTableCell[,] dataTableCells = new HtmlTableCell[numberOfDateGroupings, selectedSalesUsrs.Count * (header1TableCells.Length - 1) + 1]; for (int i = 0; i < numberOfDateGroupings; i++) { dataTableCells[i, 0] = new HtmlTableCell(); dataTableCells[i, 0].InnerHtml = "<nobr>"; if (dateGrouping.Equals(DateGrouping.Daily)) dataTableCells[i, 0].InnerHtml += String.Format("{0:ddd' 'd' 'MMM}", toDate.AddDays(-1 * (i + 1))); else if (dateGrouping.Equals(DateGrouping.Weekly)) { dataTableCells[i, 0].InnerHtml += toDate.AddDays(-7 * (i + 1)).ToString("dd/MM/yy") + " - " + toDate.AddDays(-7 * (i + 1) + 6).ToString("dd/MM/yy"); } else if (dateGrouping.Equals(DateGrouping.Monthly)) { dataTableCells[i, 0].InnerHtml += String.Format("{0:MMM' 'yy}", toDate.AddMonths(-1 * (i + 1))); } dataTableCells[i, 0].InnerHtml += "</nobr>"; } foreach (Invoice salesInvoice in salesInvoices) { try { int i = GetSalesPersonIndexNumber(selectedSalesUsrs, salesInvoice.SalesUsrK); int dateGroupingsFromTop = 0; if (dateGrouping.Equals(DateGrouping.Daily)) dateGroupingsFromTop = ((TimeSpan)(toDate - ((DateTime)salesInvoice.ExtraSelectElements["Date"]))).Days; else if (dateGrouping.Equals(DateGrouping.Weekly)) dateGroupingsFromTop = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(((TimeSpan)(toDate - ((DateTime)salesInvoice.ExtraSelectElements["Date"]))).Days) / 7d)); else if (dateGrouping.Equals(DateGrouping.Monthly)) { dateGroupingsFromTop = (toDate.AddDays(-1).Year - ((DateTime)salesInvoice.ExtraSelectElements["Date"]).Year) * 12 + (toDate.AddDays(-1).Month - ((DateTime)salesInvoice.ExtraSelectElements["Date"]).Month) + 1; } dataTableCells[dateGroupingsFromTop - 1, i + 1] = new HtmlTableCell();
private void Page_Load(object sender, System.EventArgs e) { PlaceName.Text = CurrentPlace.Name; Query q = new Query(); q.NoLock = true; q.QueryCondition = new Q(MusicType.Columns.ParentK, QueryOperator.LessThanOrEqualTo, 1); q.OrderBy = new OrderBy(MusicType.Columns.Order); MusicTypeSet mts = new MusicTypeSet(q); int i = 0; foreach (MusicType mt in mts) { HtmlTableRow row = new HtmlTableRow(); HtmlTableCell cellName = new HtmlTableCell(); cellName.InnerText = mt.Name; row.Cells.Add(cellName); Q MusicQ = null; TableElement MyJoin = null; Q ActivityQ = new Q(true); if (mt.K == 1) { MusicQ = new Q(true); MyJoin = new JoinLeft(Usr.Columns.K, UsrPlaceVisit.Columns.UsrK); } else { ArrayList musicTypesQ = new ArrayList(); ArrayList musicTypesK = new ArrayList(); musicTypesQ.Add(new Q(Usr.Columns.FavouriteMusicTypeK, mt.K)); musicTypesQ.Add(new Q(UsrMusicTypeFavourite.Columns.MusicTypeK, mt.K)); musicTypesK.Add(mt.K); AddMusicTypeQChildren(musicTypesQ, mt, ref musicTypesK); MusicQ = new Or((Q[])musicTypesQ.ToArray(typeof(Q))); MyJoin = new Join( new JoinLeft(Usr.Columns.K, UsrPlaceVisit.Columns.UsrK), new TableElement(TablesEnum.UsrMusicTypeFavourite), QueryJoinType.Left, Usr.Columns.K, UsrMusicTypeFavourite.Columns.UsrK); } Q PlaceQ = new Or( new Q(Usr.Columns.HomePlaceK, CurrentPlace.K), new Q(UsrPlaceVisit.Columns.PlaceK, CurrentPlace.K)); #region All users if (true) { Query q1 = new Query(); q1.Columns = new ColumnSet(Usr.Columns.K); q1.DistinctColumn = Usr.Columns.K; q1.Distinct = true; q1.ReturnCountOnly = true; q1.QueryCondition = new And( Usr.IsNotSkeletonQ, Usr.IsEmailVerifiedQ, ActivityQ, MusicQ, PlaceQ ); q1.TableElement = MyJoin; UsrSet us1 = new UsrSet(q1); HtmlTableCell cell1 = new HtmlTableCell(); cell1.InnerText = us1.Count.ToString(); row.Cells.Add(cell1); } #endregion #region Emails if (true) { Query q1 = new Query(); q1.Columns = new ColumnSet(Usr.Columns.K); q1.DistinctColumn = Usr.Columns.K; q1.Distinct = true; q1.ReturnCountOnly = true; q1.QueryCondition = new And( Usr.IsNotSkeletonQ, Usr.IsEmailVerifiedQ, ActivityQ, MusicQ, PlaceQ, new Q(Usr.Columns.SendSpottedEmails, true) ); q1.TableElement = MyJoin; UsrSet us1 = new UsrSet(q1); HtmlTableCell cell1 = new HtmlTableCell(); cell1.InnerText = us1.Count.ToString(); row.Cells.Add(cell1); } #endregion #region Flyers if (true) { Query q1 = new Query(); q1.Columns = new ColumnSet(Usr.Columns.K); q1.DistinctColumn = Usr.Columns.K; q1.Distinct = true; q1.ReturnCountOnly = true; q1.QueryCondition = new And( Usr.IsNotSkeletonQ, Usr.IsEmailVerifiedQ, ActivityQ, MusicQ, PlaceQ, new Q(Usr.Columns.SendFlyers, true) ); q1.TableElement = MyJoin; UsrSet us1 = new UsrSet(q1); HtmlTableCell cell1 = new HtmlTableCell(); cell1.InnerText = us1.Count.ToString(); row.Cells.Add(cell1); } #endregion #region Invites if (true) { Query q1 = new Query(); q1.Columns = new ColumnSet(Usr.Columns.K); q1.DistinctColumn = Usr.Columns.K; q1.Distinct = true; q1.ReturnCountOnly = true; q1.QueryCondition = new And( Usr.IsNotSkeletonQ, Usr.IsEmailVerifiedQ, ActivityQ, MusicQ, PlaceQ, new Q(Usr.Columns.SendInvites, true) ); q1.TableElement = MyJoin; UsrSet us1 = new UsrSet(q1); HtmlTableCell cell1 = new HtmlTableCell(); cell1.InnerText = us1.Count.ToString(); row.Cells.Add(cell1); } #endregion #region Texts if (true) { Query q1 = new Query(); q1.Columns = new ColumnSet(Usr.Columns.K); q1.DistinctColumn = Usr.Columns.K; q1.Distinct = true; q1.ReturnCountOnly = true; q1.QueryCondition = new And( new Q[]{ Usr.IsNotSkeletonQ, Usr.IsEmailVerifiedQ, ActivityQ, MusicQ, PlaceQ, new Q(Usr.Columns.SendSpottedTexts,true), new Q(Usr.Columns.Mobile,QueryOperator.NotEqualTo,"") } ); q1.TableElement = MyJoin; UsrSet us1 = new UsrSet(q1); HtmlTableCell cell1 = new HtmlTableCell(); cell1.InnerText = us1.Count.ToString(); row.Cells.Add(cell1); } #endregion HtmlTableCell cellMid = new HtmlTableCell(); cellMid.Align = "center"; cellMid.InnerHtml = " "; row.Cells.Add(cellMid); ActivityQ = new Q(Usr.Columns.DateTimeLastPageRequest, QueryOperator.GreaterThan, DateTime.Now.AddMonths(-1)); #region All users if (true) { Query q1 = new Query(); q1.Columns = new ColumnSet(Usr.Columns.K); q1.DistinctColumn = Usr.Columns.K; q1.Distinct = true; q1.ReturnCountOnly = true; q1.QueryCondition = new And( Usr.IsNotSkeletonQ, Usr.IsEmailVerifiedQ, ActivityQ, MusicQ, PlaceQ ); q1.TableElement = MyJoin; UsrSet us1 = new UsrSet(q1); HtmlTableCell cell1 = new HtmlTableCell(); cell1.InnerText = us1.Count.ToString(); row.Cells.Add(cell1); } #endregion #region Emails if (true) { Query q1 = new Query(); q1.Columns = new ColumnSet(Usr.Columns.K); q1.DistinctColumn = Usr.Columns.K; q1.Distinct = true; q1.ReturnCountOnly = true; q1.QueryCondition = new And( Usr.IsNotSkeletonQ, Usr.IsEmailVerifiedQ, ActivityQ, MusicQ, PlaceQ, new Q(Usr.Columns.SendSpottedEmails, true) ); q1.TableElement = MyJoin; UsrSet us1 = new UsrSet(q1); HtmlTableCell cell1 = new HtmlTableCell(); cell1.InnerText = us1.Count.ToString(); row.Cells.Add(cell1); } #endregion #region Flyers if (true) { Query q1 = new Query(); q1.Columns = new ColumnSet(Usr.Columns.K); q1.DistinctColumn = Usr.Columns.K; q1.Distinct = true; q1.ReturnCountOnly = true; q1.QueryCondition = new And( Usr.IsNotSkeletonQ, Usr.IsEmailVerifiedQ, ActivityQ, MusicQ, PlaceQ, new Q(Usr.Columns.SendFlyers, true) ); q1.TableElement = MyJoin; UsrSet us1 = new UsrSet(q1); HtmlTableCell cell1 = new HtmlTableCell(); cell1.InnerText = us1.Count.ToString(); row.Cells.Add(cell1); } #endregion #region Invites if (true) { Query q1 = new Query(); q1.Columns = new ColumnSet(Usr.Columns.K); q1.DistinctColumn = Usr.Columns.K; q1.Distinct = true; q1.ReturnCountOnly = true; q1.QueryCondition = new And( Usr.IsNotSkeletonQ, Usr.IsEmailVerifiedQ, ActivityQ, MusicQ, PlaceQ, new Q(Usr.Columns.SendInvites, true) ); q1.TableElement = MyJoin; UsrSet us1 = new UsrSet(q1); HtmlTableCell cell1 = new HtmlTableCell(); cell1.InnerText = us1.Count.ToString(); row.Cells.Add(cell1); } #endregion #region Texts if (true) { Query q1 = new Query(); q1.Columns = new ColumnSet(Usr.Columns.K); q1.DistinctColumn = Usr.Columns.K; q1.Distinct = true; q1.ReturnCountOnly = true; q1.QueryCondition = new And( new Q[]{ Usr.IsNotSkeletonQ, Usr.IsEmailVerifiedQ, ActivityQ, MusicQ, PlaceQ, new Q(Usr.Columns.SendSpottedTexts,true), new Q(Usr.Columns.Mobile,QueryOperator.NotEqualTo,"") } ); q1.TableElement = MyJoin; UsrSet us1 = new UsrSet(q1); HtmlTableCell cell1 = new HtmlTableCell(); cell1.InnerText = us1.Count.ToString(); row.Cells.Add(cell1); } #endregion if (i % 2 == 0) row.Attributes["class"] = "dataGridAltItem"; i++; Tab.Rows.Add(row); } }
public void PromoterOutstandingAccountPanel() { if (Usr.Current != null && !Usr.Current.IsAdmin && !Usr.Current.IsSuperAdmin && Usr.Current.IsEnabledPromoter()) { PromoterSet promoters = Usr.Current.Promoters(new ColumnSet(Promoter.Columns.UrlName, Promoter.Columns.Name, Promoter.Columns.K, Promoter.Columns.OverrideApplyTicketFundsToInvoices)); Query outstandingInvoiceQuery = new Query(); Q promoterOr = new Q(Invoice.Columns.PromoterK, -1); foreach (Promoter promoter in promoters) { promoterOr = new Or(promoterOr, new Q(Invoice.Columns.PromoterK, promoter.K)); } outstandingInvoiceQuery.QueryCondition = new And(promoterOr, new Q(Invoice.Columns.Type, Invoice.Types.Invoice), new Q(Invoice.Columns.Paid, false), new Q(Invoice.Columns.DueDateTime, QueryOperator.LessThanOrEqualTo, DateTime.Today.AddDays(8).AddMilliseconds(-1))); outstandingInvoiceQuery.Columns = new ColumnSet(Invoice.Columns.PromoterK); outstandingInvoiceQuery.ExtraSelectElements.Add("MinDueDate", "MIN(DueDateTime)"); outstandingInvoiceQuery.OrderBy = new OrderBy("MIN([Invoice].[DueDateTime])"); outstandingInvoiceQuery.GroupBy = new GroupBy("[Invoice].[PromoterK]"); InvoiceSet invoices = new InvoiceSet(outstandingInvoiceQuery); bool isRedirectPage = false; PromoterAccountsOutstandingPanel.Visible = invoices.Count > 0; this.Visible = invoices.Count > 0; if (invoices.Count > 0) { string tableClass = "dataGrid"; string headerText = ""; string messageText = ""; // Set urgency DateTime minDueDate = Utilities.GetStartOfDay((DateTime)invoices[0].ExtraSelectElements["MinDueDate"]); DateTime startOfToday = DateTime.Today; this.PromoterAccountsOutstandingMessage.Visible = true; this.AccountLockoutHelp.Visible = true; // If SuperAdmin has set "DisableOverdueRedirect" then it prevents the suspension of the promoter's access to the site, but will still show wanring message with all outstanding / overdue accounts. if (minDueDate < startOfToday.AddDays(-7) && !invoices[0].Promoter.DisableOverdueRedirect) { // Far overdue - redirect headerText = "WARNING! Your promoter account invoices are overdue. You must pay now!"; messageText = "Your access to DontStayIn has been suspended due to invoices that have been overdue for an extended period.<br>You must pay the account balance immediately."; if (!this.IsPostBack && !Usr.Current.IsAdmin && !Usr.Current.IsSuperAdmin && !this.IsAllowedPage(invoices)) { HttpContext.Current.Response.Redirect(REDIRECT_PAGE); } else { // Hide panel if they are redirected to pay immediately if (IsOnPromoterPayPage(invoices)) { PromoterAccountsOutstandingPanel.Visible = false; this.Visible = false; return; } isRedirectPage = true; } } else if (minDueDate < startOfToday) { // Overdue headerText = "WARNING! Your promoter account invoices are overdue. You must pay now!"; messageText = "Your promoter account invoices are now overdue.<br>You must pay the account balance immediately to prevent a suspension of your DontStayIn account."; } else if (minDueDate < startOfToday.AddDays(3)) { // Almost overdue headerText = "URGENT! Your promoter account invoices are almost due. Please pay now."; messageText = "Your promoter account invoices are almost overdue."; } else { // Outstanding headerText = "Your promoter account invoices are nearly due. Please pay now."; //messageText = "Your promoter account invoices are nearing the due dates. Please pay the account balance."; this.PromoterAccountsOutstandingMessage.Visible = false; this.AccountLockoutHelp.Visible = false; } this.PromoterAccountsOutstandingHeader.InnerHtml = headerText; this.PromoterAccountsOutstandingMessage.InnerHtml = messageText; //this.AccountLockoutHelp.Visible = isRedirectPage; this.PromoterAccountsOutstandingTable.Rows.Clear(); this.PromoterAccountsOutstandingTable.Attributes.Add("class", tableClass); #region Header Row HtmlTableRow headerRow = new HtmlTableRow(); headerRow.Attributes.Add("class", "dataGridHeader"); this.PromoterAccountsOutstandingTable.Rows.Add(headerRow); HtmlTableCell[] headerTableCells = new HtmlTableCell[4]; for (int i = 0; i < headerTableCells.Length; i++) { headerTableCells[i] = new HtmlTableCell("th"); headerTableCells[i].Align = "left"; headerRow.Cells.Add(headerTableCells[i]); } headerTableCells[0].InnerHtml = "<nobr>Promoter account</nobr>"; headerTableCells[1].InnerHtml = "Balance"; headerTableCells[2].InnerHtml = "<nobr>Payment due</nobr>"; headerTableCells[3].InnerHtml = "<nobr>Pay now</nobr>"; #endregion #region Data Rows HtmlTableRow[] dataRows = new HtmlTableRow[invoices.Count]; for (int i = 0; i < invoices.Count; i++) { dataRows[i] = new HtmlTableRow(); if (i % 2 == 0) dataRows[i].Attributes.Add("class", "dataGridItem"); else dataRows[i].Attributes.Add("class", "dataGridAltItem"); this.PromoterAccountsOutstandingTable.Rows.Add(dataRows[i]); HtmlTableCell[] dataTableCells = new HtmlTableCell[4]; for (int j = 0; j < dataTableCells.Length; j++) { dataTableCells[j] = new HtmlTableCell(); dataRows[i].Cells.Add(dataTableCells[j]); } promoters.Reset(); string[] urlAppParams = new string[]{ "PayOutstanding", "true" }; foreach (Promoter promoter in promoters) { if (invoices[i].PromoterK == promoter.K) { // if isRedirect remove link, cause they are only allowed to go to the payment page if(isRedirectPage) dataTableCells[0].InnerHtml = "<nobr>" + promoter.Name + "</nobr>"; else dataTableCells[0].InnerHtml = "<nobr><a href=\"" + promoter.Url() + "\">" + promoter.Name + "</a></nobr>"; dataTableCells[1].InnerHtml = "<nobr><font color=\"#ff0000;\"><b>" + Math.Abs(promoter.GetBalance()).ToString("c") + "</b></font></nobr>"; if (Utilities.GetStartOfDay((DateTime)invoices[i].ExtraSelectElements["MinDueDate"]) >= DateTime.Today) urlAppParams = new string[] { "PayOutstanding", "true"}; else urlAppParams = new string[] { "PayOutstanding", "true", "Overdue", "true" }; dataTableCells[3].InnerHtml = "<button onclick=\"window.location.href='" + promoter.UrlApp("invoices", urlAppParams) + "';return false;\">Pay now</button>"; break; } } dataTableCells[1].Align = "right"; dataTableCells[2].InnerHtml = Utilities.DateToString((DateTime)invoices[i].ExtraSelectElements["MinDueDate"]); } #endregion } } else { PromoterAccountsOutstandingPanel.Visible = false; this.Visible = false; } }
private void GenerateSalesStatsReport(DateTime fromDate, DateTime toDate, string salesUsrK) { Page.Validate(""); if (Page.IsValid) { this.DateRangeValueLabel.Text = fromDate.ToString("dd/MM/yy") + " to " + toDate.ToString("dd/MM/yy"); this.DateRangeLabel.Visible = true; this.DateRangeValueLabel.Visible = true; fromDate = new DateTime(fromDate.Year, fromDate.Month, fromDate.Day); toDate = new DateTime(toDate.Year, toDate.Month, toDate.Day).AddDays(1); if (toDate > fromDate) { int numberOfDays = ((TimeSpan)(toDate - fromDate)).Days; //UsrSet salesUsrs = Usr.GetSalesUsrsNameAndK(salesUsrK); List<UsrDataHolder> selectedSalesUsrs = SalesUsrs; if (salesUsrK != "0") { selectedSalesUsrs = new List<UsrDataHolder>(); if (salesUsrK.IndexOf("team") == 0) { UsrSet salesTeam = Usr.GetCurrentSalesUsrsNameAndK(Convert.ToInt32(salesUsrK.Replace("team", ""))); foreach (Usr salesUsr in salesTeam) { selectedSalesUsrs.Add(new UsrDataHolder(salesUsr)); } } else { selectedSalesUsrs.Add(SalesUsrs[GetSalesPersonIndexNumber(SalesUsrs, Convert.ToInt32(salesUsrK))]); } } if (selectedSalesUsrs.Count > 0) { Q salesUsrsQueryCondition = new Q(Invoice.Columns.SalesUsrK, selectedSalesUsrs[0].K); for (int i = 1; i < selectedSalesUsrs.Count; i++) { salesUsrsQueryCondition = new Or(salesUsrsQueryCondition, new Q(Invoice.Columns.SalesUsrK, selectedSalesUsrs[i].K)); } Q salesStatsDateRangeQueryCondition = new And(new Q(Invoice.Columns.SalesUsrAmount, QueryOperator.NotEqualTo, 0), new Or(new And(new Q(Invoice.Columns.Type, Invoice.Types.Invoice), new Q(Invoice.Columns.Paid, true), new Q(Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate), new Q(Invoice.Columns.PaidDateTime, QueryOperator.LessThan, toDate), new Q(Usr.Columns.SalesTeam, QueryOperator.NotEqualTo, Usr.SalesTeams.CorporateSalesTeam)), new And(new Q(Invoice.Columns.Type, Invoice.Types.Invoice), new Q(Invoice.Columns.TaxDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate), new Q(Invoice.Columns.TaxDateTime, QueryOperator.LessThan, toDate), new Q(Usr.Columns.SalesTeam, Usr.SalesTeams.CorporateSalesTeam)), new And(new Q(Invoice.Columns.Type, Invoice.Types.Credit), new Q(Invoice.Columns.CreatedDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate), new Q(Invoice.Columns.CreatedDateTime, QueryOperator.LessThan, toDate)))); Query salesStatsMinutesQuery = new Query(new And(salesStatsDateRangeQueryCondition, salesUsrsQueryCondition)); salesStatsMinutesQuery.TableElement = new Join(Invoice.Columns.SalesUsrK, Usr.Columns.K); salesStatsMinutesQuery.OrderBy = new OrderBy("CASE WHEN [Usr].[SalesTeam] = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN [Invoice].[TaxDateTime] ELSE CASE WHEN [Invoice].[PaidDateTime] > [Invoice].[CreatedDateTime] THEN [Invoice].[PaidDateTime] ELSE [Invoice].[CreatedDateTime] END END DESC, [Invoice].[K]"); //new OrderBy(Invoice.Columns.PaidDateTime, OrderBy.OrderDirection.Descending), new OrderBy(Invoice.Columns.K, OrderBy.OrderDirection.Ascending)); InvoiceSet salesInvoices = new InvoiceSet(salesStatsMinutesQuery); #region Table this.SalesCallsResultsTable.Visible = false; this.SalesStatsResultsTable.Visible = true; HtmlTable salesStatsTable = this.SalesStatsResultsTable; salesStatsTable.Rows.Clear(); #endregion #region Header Rows HtmlTableRow headerRow = new HtmlTableRow(); headerRow.Attributes.Add("class", "dataGridHeader"); salesStatsTable.Rows.Add(headerRow); HtmlTableCell[] headerTableCells = new HtmlTableCell[6]; for (int i = 0; i < headerTableCells.Length; i++) { headerTableCells[i] = new HtmlTableCell("th"); headerTableCells[i].Align = "left"; } headerTableCells[0].InnerHtml = "Time"; headerTableCells[1].InnerHtml = "Date"; headerTableCells[2].InnerHtml = "<nobr>Link to invoice and items</nobr>"; headerTableCells[2].Width = "250"; headerTableCells[3].InnerHtml = "Promoter"; headerTableCells[4].InnerHtml = "<nobr>Sales amount</nobr>"; headerTableCells[5].InnerHtml = "<nobr>Sales person</nobr>"; foreach (HtmlTableCell tc in headerTableCells) headerRow.Cells.Add(tc); #endregion #region Data Rows decimal totalSalesAmount = 0; for (int i = 0; i < salesInvoices.Count; i++) { HtmlTableRow tr = new HtmlTableRow(); salesStatsTable.Rows.Add(tr); if (i % 2 == 0) tr.Attributes.Add("class", "dataGridItem"); else tr.Attributes.Add("class", "dataGridAltItem"); HtmlTableCell[] dataTableCells = new HtmlTableCell[6]; for (int j = 0; j < dataTableCells.Length; j++) { dataTableCells[j] = new HtmlTableCell(); tr.Cells.Add(dataTableCells[j]); } DateTime dt = salesInvoices[i].Type == Invoice.Types.Invoice && salesInvoices[i].SalesUsr.SalesTeam != Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam) ? salesInvoices[i].PaidDateTime : salesInvoices[i].CreatedDateTime; if (dt > DateTime.MinValue) { dataTableCells[0].InnerHtml = dt.ToString("HH:mm"); dataTableCells[1].InnerHtml = Utilities.DateToString(dt); } else { dataTableCells[0].InnerHtml = " "; dataTableCells[1].InnerHtml = " "; } dataTableCells[2].InnerHtml = "<a href=\"" + salesInvoices[i].UrlAdmin() + "\" target=\"_blank\">Invoice #" + salesInvoices[i].K.ToString() + "</a>"; HtmlTable itemsTable = new HtmlTable(); itemsTable.ID = "InvoiceItems" + salesInvoices[i].K.ToString(); itemsTable.Style["display"] = "none"; itemsTable.CellPadding = 3; itemsTable.CellSpacing = 0; itemsTable.Width = "250"; itemsTable.Border = 0; for (int k = 0; k < salesInvoices[i].Items.Count; k++) { HtmlTableRow itemTr = new HtmlTableRow(); itemsTable.Rows.Add(itemTr); HtmlTableCell itemDescriptionTd = new HtmlTableCell(); HtmlTableCell itemPriceTd = new HtmlTableCell(); itemTr.Cells.Add(itemDescriptionTd); itemTr.Cells.Add(itemPriceTd); itemDescriptionTd.Attributes.Add("style", "background-color:transparent;padding-top:1px;padding-bottom:1px;border-left:0px;"); itemPriceTd.Attributes.Add("style", "background-color:transparent;padding-top:1px;padding-bottom:1px;border-left:0px;"); try { itemDescriptionTd.InnerHtml = "<small><a href=\"" + salesInvoices[i].Items[k].AdminUrl + "\" target=\"_blank\">" + salesInvoices[i].Items[k].Description + "</a></small>"; } catch { itemDescriptionTd.InnerHtml = "<small>" + salesInvoices[i].Items[k].Description + "</small>"; } itemPriceTd.InnerHtml = "<nobr><small>" + salesInvoices[i].Items[k].Price.ToString("c") + "</small></nobr>"; itemPriceTd.Align = "right"; } string invoiceHtml = "<a href=\"" + salesInvoices[i].UrlAdmin() + "\" target=\"_blank\">" + salesInvoices[i].Type.ToString() + " #" + salesInvoices[i].K.ToString() + "</a>"; dataTableCells[2].InnerHtml = "<a href=\"#\" onclick=\"var elem = document.getElementById('Content_" + itemsTable.ClientID + "'); var img = document.getElementById('" + this.ClientID + "_SalesStatsPlusMinus" + salesInvoices[i].K + "'); img.src = elem.style.display == 'none' ? '/gfx/minus.gif' : '/gfx/plus.gif'; elem.style.display = elem.style.display == 'none' ? '' : 'none'; return false;\"><img id=\"" + this.ClientID + "_SalesStatsPlusMinus" + salesInvoices[i].K + "\" src=\"/gfx/plus.gif\" alt=\"Show items\" border=\"0\" align=\"absmiddle\" style=\"margin-right:4px;\" /></a>" + invoiceHtml; dataTableCells[2].Controls.Add(itemsTable); // Make Promoter Link dataTableCells[3].InnerHtml = "<nobr>" + salesInvoices[i].Promoter.LinkNewWindow() + "</nobr>"; dataTableCells[3].Width = "250"; dataTableCells[4].InnerHtml = salesInvoices[i].SalesUsrAmount.ToString("c") + " "; dataTableCells[4].Align = "right"; dataTableCells[5].InnerHtml = "<nobr>" + salesInvoices[i].SalesUsr.LinkNewWindow() + "</nobr>"; // Fixes issue with random assigning of ColSpan = 5. Because footer has colspan=5 it "remembers" that on the same row #, but this fix has resolved it. for (int j = 0; j < dataTableCells.Length; j++) dataTableCells[j].ColSpan = 1; totalSalesAmount += salesInvoices[i].SalesUsrAmount; } #endregion #region Footer Row HtmlTableRow footerRow = new HtmlTableRow(); salesStatsTable.Rows.Add(footerRow); footerRow.Attributes.Add("class", "dataGridFooter"); HtmlTableCell footerTableCell = new HtmlTableCell(); footerRow.Cells.Add(footerTableCell); footerTableCell.ColSpan = 5; footerTableCell.Align = "right"; footerTableCell.InnerHtml = "Total for period: <b>" + totalSalesAmount.ToString("c") + "</b> "; #endregion } } } }
private void GenerateSalesCallsReport(DateTime fromDate, DateTime toDate, DateGrouping dateGrouping, string salesUsrK) { Page.Validate(""); if (Page.IsValid) { this.DateRangeValueLabel.Text = fromDate.ToString("dd/MM/yy") + " to " + toDate.ToString("dd/MM/yy"); this.DateRangeLabel.Visible = true; this.DateRangeValueLabel.Visible = true; int startOfWeek = 1; // Sunday = 0, Monday = 1 fromDate = new DateTime(fromDate.Year, fromDate.Month, fromDate.Day); toDate = new DateTime(toDate.Year, toDate.Month, toDate.Day); if (toDate >= fromDate) { int numberOfDateGroupings = 0; if (dateGrouping.Equals(DateGrouping.Daily)) { toDate = new DateTime(toDate.Year, toDate.Month, toDate.Day).AddDays(1); numberOfDateGroupings = ((TimeSpan)(toDate - fromDate)).Days; } else if (dateGrouping.Equals(DateGrouping.Weekly)) { fromDate = Utilities.GetStartOfWeek(fromDate); toDate = Utilities.GetEndOfWeek(toDate); toDate = new DateTime(toDate.Year, toDate.Month, toDate.Day).AddDays(1); numberOfDateGroupings = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(((TimeSpan)(toDate - fromDate)).Days) / 7d)); } else if (dateGrouping.Equals(DateGrouping.Monthly)) { fromDate = Utilities.GetStartOfMonth(fromDate); toDate = Utilities.GetEndOfMonth(toDate); toDate = new DateTime(toDate.Year, toDate.Month, toDate.Day).AddDays(1); numberOfDateGroupings = (toDate.AddDays(-1).Year - fromDate.Year) * 12 + (toDate.AddDays(-1).Month - fromDate.Month) + 1; } List<UsrDataHolder> selectedSalesUsrs = SalesUsrs; if (salesUsrK != "0") { selectedSalesUsrs = new List<UsrDataHolder>(); if (salesUsrK.IndexOf("team") == 0) { UsrSet salesTeam = Usr.GetCurrentSalesUsrsNameAndK(Convert.ToInt32(salesUsrK.Replace("team", ""))); foreach (Usr salesUsr in salesTeam) { selectedSalesUsrs.Add(new UsrDataHolder(salesUsr)); } } else { selectedSalesUsrs.Add(SalesUsrs[GetSalesPersonIndexNumber(SalesUsrs, Convert.ToInt32(salesUsrK))]); } } if (selectedSalesUsrs.Count > 0) { Q salesUsrsQueryCondition = new Q(SalesCall.Columns.UsrK, selectedSalesUsrs[0].K); Q invoiceSalesUsrsQueryCondition = new Q(Invoice.Columns.SalesUsrK, selectedSalesUsrs[0].K); Q promoterAddedByQueryCondition = new Q(Promoter.Columns.AddedByUsrK, selectedSalesUsrs[0].K); for (int i = 1; i < selectedSalesUsrs.Count; i++) { promoterAddedByQueryCondition = new Or(promoterAddedByQueryCondition, new Q(Promoter.Columns.AddedByUsrK, selectedSalesUsrs[i].K)); salesUsrsQueryCondition = new Or(salesUsrsQueryCondition, new Q(SalesCall.Columns.UsrK, selectedSalesUsrs[i].K)); invoiceSalesUsrsQueryCondition = new Or(invoiceSalesUsrsQueryCondition, new Q(Invoice.Columns.SalesUsrK, selectedSalesUsrs[i].K)); } Q salesCallDateRangeQueryCondition = new And(new Q(SalesCall.Columns.IsCall, true), new Q(SalesCall.Columns.DateTimeStart, QueryOperator.GreaterThanOrEqualTo, fromDate), new Q(SalesCall.Columns.DateTimeStart, QueryOperator.LessThan, toDate)); Q promoterDateRangeQueryCondition = new And( new Q(Promoter.Columns.AddedMethod, Promoter.AddedMedhods.SalesUser), new Q(Promoter.Columns.DateTimeSignUp, QueryOperator.GreaterThanOrEqualTo, fromDate), new Q(Promoter.Columns.DateTimeSignUp, QueryOperator.LessThan, toDate)); #region Sales Calls: Total Money Query totalSalesAmountQuery = new Query(new And(invoiceSalesUsrsQueryCondition, new Q(Invoice.Columns.SalesUsrAmount, QueryOperator.NotEqualTo, 0), new Or(new And(new Q(Invoice.Columns.Type, Invoice.Types.Invoice), new Q(Invoice.Columns.Paid, true), new Q(Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate), new Q(Invoice.Columns.PaidDateTime, QueryOperator.LessThan, toDate), new Q(Usr.Columns.SalesTeam, QueryOperator.NotEqualTo, Usr.SalesTeams.CorporateSalesTeam)), new And(new Q(Invoice.Columns.Type, Invoice.Types.Invoice), new Q(Invoice.Columns.TaxDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate), new Q(Invoice.Columns.TaxDateTime, QueryOperator.LessThan, toDate), new Q(Usr.Columns.SalesTeam, Usr.SalesTeams.CorporateSalesTeam)), new And(new Q(Invoice.Columns.Type, Invoice.Types.Credit), new Q(Invoice.Columns.CreatedDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate), new Q(Invoice.Columns.CreatedDateTime, QueryOperator.LessThan, toDate))))); totalSalesAmountQuery.TableElement = new Join(Invoice.Columns.SalesUsrK, Usr.Columns.K); totalSalesAmountQuery.ExtraSelectElements.Add("TotalSales", "SUM(SalesUsrAmount)"); totalSalesAmountQuery.Columns = new ColumnSet(Invoice.Columns.SalesUsrK); if (dateGrouping.Equals(DateGrouping.Daily)) { totalSalesAmountQuery.ExtraSelectElements.Add("Date", "CONVERT(datetime,CONVERT(varchar(2),DAY(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))) + '/' + CONVERT(varchar(2),MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))) + '/' + CONVERT(varchar(4),Year(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))))"); totalSalesAmountQuery.OrderBy = new OrderBy("[Usr].[FirstName], YEAR(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) desc, MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) desc, DAY(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) desc"); totalSalesAmountQuery.GroupBy = new GroupBy("[Usr].[FirstName], DAY(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)), MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)), YEAR(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)), [Invoice].[SalesUsrK]"); } else if (dateGrouping.Equals(DateGrouping.Weekly)) { totalSalesAmountQuery.ExtraSelectElements.Add("Date", "CONVERT(dateTime,(CONVERT(varchar(2),DAY(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(2),MONTH(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(4),YEAR(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))))))"); totalSalesAmountQuery.OrderBy = new OrderBy("[Usr].[FirstName], CONVERT(dateTime,(CONVERT(varchar(2),DAY(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(2),MONTH(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(4),YEAR(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))))) desc"); totalSalesAmountQuery.GroupBy = new GroupBy("[Usr].[FirstName], CONVERT(dateTime,(CONVERT(varchar(2),DAY(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(2),MONTH(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(4),YEAR(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))))), [Invoice].[SalesUsrK]"); } else if (dateGrouping.Equals(DateGrouping.Monthly)) { totalSalesAmountQuery.ExtraSelectElements.Add("Date", "CONVERT(datetime,'1/' + CONVERT(varchar(2),MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))) + '/' + CONVERT(varchar(4),Year(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))))"); totalSalesAmountQuery.OrderBy = new OrderBy("[Usr].[FirstName], YEAR(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) desc, MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) desc"); totalSalesAmountQuery.GroupBy = new GroupBy("[Usr].[FirstName], MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)), YEAR(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)), [Invoice].[SalesUsrK]"); } InvoiceSet salesInvoices = new InvoiceSet(totalSalesAmountQuery); #endregion #region Sales Calls: SalesUsrK, Minutes, and Total calls Query salesCallMinutesQuery = new Query(new And(salesCallDateRangeQueryCondition, salesUsrsQueryCondition)); salesCallMinutesQuery.ExtraSelectElements.Add("Minutes", "SUM([SalesCall].[Duration])"); salesCallMinutesQuery = PopulateSalesCallQuery(salesCallMinutesQuery, dateGrouping, startOfWeek); SalesCallSet salesCallsMinutes = new SalesCallSet(salesCallMinutesQuery); #endregion #region Promoters: New leads Query promotersNewLeadsQuery = new Query(new And(promoterDateRangeQueryCondition, promoterAddedByQueryCondition)); promotersNewLeadsQuery = PopulatePromotersQuery(promotersNewLeadsQuery, dateGrouping, startOfWeek); PromoterSet promotersNewLeads = new PromoterSet(promotersNewLeadsQuery); #endregion #region Sales Calls: New leads Query salesCallNewLeadsQuery = new Query(new And(salesCallDateRangeQueryCondition, salesUsrsQueryCondition, new Q(SalesCall.Columns.IsCallToNewLead, true))); salesCallNewLeadsQuery = PopulateSalesCallQuery(salesCallNewLeadsQuery, dateGrouping, startOfWeek); SalesCallSet salesCallsNewLeads = new SalesCallSet(salesCallNewLeadsQuery); #endregion #region Sales Calls: Effective Query salesCallEffectiveQuery = new Query(new And(salesCallDateRangeQueryCondition, salesUsrsQueryCondition, new Q(SalesCall.Columns.Effective, true))); salesCallEffectiveQuery = PopulateSalesCallQuery(salesCallEffectiveQuery, dateGrouping, startOfWeek); SalesCallSet salesCallsEffective = new SalesCallSet(salesCallEffectiveQuery); #endregion #region Sales Calls: Cold Query salesCallColdQuery = new Query(new And(salesCallDateRangeQueryCondition, salesUsrsQueryCondition, new Q(SalesCall.Columns.Type, SalesCall.Types.Cold))); salesCallColdQuery = PopulateSalesCallQuery(salesCallColdQuery, dateGrouping, startOfWeek); SalesCallSet salesCallsCold = new SalesCallSet(salesCallColdQuery); #endregion #region Sales Calls: Followup Query salesCallFollowupQuery = new Query(new And(salesCallDateRangeQueryCondition, salesUsrsQueryCondition, new Q(SalesCall.Columns.Type, SalesCall.Types.ProactiveFollowUp))); salesCallFollowupQuery = PopulateSalesCallQuery(salesCallFollowupQuery, dateGrouping, startOfWeek); SalesCallSet salesCallsFollowup = new SalesCallSet(salesCallFollowupQuery); #endregion #region Sales Calls: Active Query salesCallActiveQuery = new Query(new And(salesCallDateRangeQueryCondition, salesUsrsQueryCondition, new Q(SalesCall.Columns.Type, SalesCall.Types.Active))); salesCallActiveQuery = PopulateSalesCallQuery(salesCallActiveQuery, dateGrouping, startOfWeek); SalesCallSet salesCallsActive = new SalesCallSet(salesCallActiveQuery); #endregion #region Table this.SalesStatsResultsTable.Visible = false; this.SalesCallsResultsTable.Visible = true; HtmlTable salesCallsTable = this.SalesCallsResultsTable; salesCallsTable.Rows.Clear(); #endregion #region Header Rows HtmlTableRow headerRow1 = new HtmlTableRow(); headerRow1.Attributes.Add("class", "dataGrid1stHeader"); salesCallsTable.Rows.Add(headerRow1); HtmlTableCell[] header1TableCells = new HtmlTableCell[6]; header1TableCells[0] = new HtmlTableCell("td"); header1TableCells[0].RowSpan = 2; header1TableCells[0].InnerHtml = "Date"; for (int i = 1; i < header1TableCells.Length; i++) { header1TableCells[i] = new HtmlTableCell("th"); header1TableCells[i].ColSpan = selectedSalesUsrs.Count; header1TableCells[i].Align = "center"; header1TableCells[i].Attributes.Add("class", "dataGridColumnDivider"); } header1TableCells[1].InnerHtml = "Total sales"; header1TableCells[2].InnerHtml = "<b>Total calls</b>"; header1TableCells[3].InnerHtml = "Minutes per call"; header1TableCells[4].InnerHtml = "<b>New leads</b>"; header1TableCells[5].InnerHtml = "Calls to new leads"; //header1TableCells[4].InnerHtml = "<nobr>Effective calls</nobr>"; //header1TableCells[7].InnerHtml = "<nobr>Cold (%)</nobr>"; //header1TableCells[8].InnerHtml = "<nobr>Followup (%)</nobr>"; //header1TableCells[9].InnerHtml = "<nobr>Active (%)</nobr>"; foreach (HtmlTableCell tc in header1TableCells) headerRow1.Cells.Add(tc); HtmlTableRow headerRow2 = new HtmlTableRow(); headerRow2.Attributes.Add("class", "dataGrid2ndHeader"); salesCallsTable.Rows.Add(headerRow2); HtmlTableCell[] header2TableCells = new HtmlTableCell[selectedSalesUsrs.Count * (header1TableCells.Length - 1)]; for (int i = 0; i < header2TableCells.Length; i++) { header2TableCells[i] = new HtmlTableCell("th"); header2TableCells[i].InnerHtml = "<b>" + selectedSalesUsrs[i % selectedSalesUsrs.Count].FirstName + "</b>"; header2TableCells[i].Align = "right"; header2TableCells[i].Width = "10"; if (i % selectedSalesUsrs.Count == 0) { header2TableCells[i].Attributes.Add("class", "dataGridColumnDivider"); } headerRow2.Cells.Add(header2TableCells[i]); } #endregion #region Data Rows HtmlTableCell[,] dataTableCells = new HtmlTableCell[numberOfDateGroupings, selectedSalesUsrs.Count * (header1TableCells.Length - 1) + 1]; for (int i = 0; i < numberOfDateGroupings; i++) { dataTableCells[i, 0] = new HtmlTableCell(); dataTableCells[i, 0].InnerHtml = "<nobr>"; if (dateGrouping.Equals(DateGrouping.Daily)) dataTableCells[i, 0].InnerHtml += String.Format("{0:ddd' 'd' 'MMM}", toDate.AddDays(-1 * (i + 1))); else if (dateGrouping.Equals(DateGrouping.Weekly)) { dataTableCells[i, 0].InnerHtml += toDate.AddDays(-7 * (i + 1)).ToString("dd/MM/yy") + " - " + toDate.AddDays(-7 * (i + 1) + 6).ToString("dd/MM/yy"); } else if (dateGrouping.Equals(DateGrouping.Monthly)) { dataTableCells[i, 0].InnerHtml += String.Format("{0:MMM' 'yy}", toDate.AddMonths(-1 * (i + 1))); } dataTableCells[i, 0].InnerHtml += "</nobr>"; } foreach (Invoice salesInvoice in salesInvoices) { try { int i = GetSalesPersonIndexNumber(selectedSalesUsrs, salesInvoice.SalesUsrK); int dateGroupingsFromTop = 0; if (dateGrouping.Equals(DateGrouping.Daily)) dateGroupingsFromTop = ((TimeSpan)(toDate - ((DateTime)salesInvoice.ExtraSelectElements["Date"]))).Days; else if (dateGrouping.Equals(DateGrouping.Weekly)) dateGroupingsFromTop = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(((TimeSpan)(toDate - ((DateTime)salesInvoice.ExtraSelectElements["Date"]))).Days) / 7d)); else if (dateGrouping.Equals(DateGrouping.Monthly)) { dateGroupingsFromTop = (toDate.AddDays(-1).Year - ((DateTime)salesInvoice.ExtraSelectElements["Date"]).Year) * 12 + (toDate.AddDays(-1).Month - ((DateTime)salesInvoice.ExtraSelectElements["Date"]).Month) + 1; } dataTableCells[dateGroupingsFromTop - 1, i + 1] = new HtmlTableCell(); dataTableCells[dateGroupingsFromTop - 1, i + 1].InnerHtml = "<nobr>" + Convert.ToDouble(salesInvoice.ExtraSelectElements["TotalSales"]).ToString("£#,##0") + "</nobr>"; } catch (Exception ex) { string x = ex.Message; } } foreach (SalesCall salesCall in salesCallsMinutes) { try { int i = GetSalesPersonIndexNumber(selectedSalesUsrs, salesCall.UsrK); int dateGroupingsFromTop = 0; if (dateGrouping.Equals(DateGrouping.Daily)) dateGroupingsFromTop = ((TimeSpan)(toDate - ((DateTime)salesCall.ExtraSelectElements["Date"]))).Days; else if (dateGrouping.Equals(DateGrouping.Weekly)) dateGroupingsFromTop = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(((TimeSpan)(toDate - ((DateTime)salesCall.ExtraSelectElements["Date"]))).Days) / 7d)); else if (dateGrouping.Equals(DateGrouping.Monthly)) { dateGroupingsFromTop = (toDate.AddDays(-1).Year - ((DateTime)salesCall.ExtraSelectElements["Date"]).Year) * 12 + (toDate.AddDays(-1).Month - ((DateTime)salesCall.ExtraSelectElements["Date"]).Month) + 1; } dataTableCells[dateGroupingsFromTop - 1, selectedSalesUsrs.Count + i + 1] = new HtmlTableCell(); dataTableCells[dateGroupingsFromTop - 1, selectedSalesUsrs.Count + i + 1].Style["font-weight"] = "bold"; dataTableCells[dateGroupingsFromTop - 1, selectedSalesUsrs.Count + i + 1].InnerHtml = Convert.ToInt32(salesCall.ExtraSelectElements["TotalCalls"]).ToString("#,##0"); try { double minutesPerCall = Convert.ToDouble(salesCall.ExtraSelectElements["Minutes"]) / Convert.ToDouble(salesCall.ExtraSelectElements["TotalCalls"]); int minutes = (int)Math.Floor(minutesPerCall); double minuteFraction = minutesPerCall - minutes; double seconds = minuteFraction * 60; dataTableCells[dateGroupingsFromTop - 1, selectedSalesUsrs.Count * 2 + i + 1] = new HtmlTableCell(); dataTableCells[dateGroupingsFromTop - 1, selectedSalesUsrs.Count * 2 + i + 1].InnerHtml = minutes.ToString("0") + ":" + seconds.ToString("00"); } catch (Exception ex) { string x = ex.Message; } } catch (Exception ex) { string x = ex.Message; } } foreach (Promoter promoter in promotersNewLeads) { try { int i = GetSalesPersonIndexNumber(selectedSalesUsrs, promoter.AddedByUsrK); int dateGroupingsFromTop = 0; if (dateGrouping.Equals(DateGrouping.Daily)) dateGroupingsFromTop = ((TimeSpan)(toDate - ((DateTime)promoter.ExtraSelectElements["Date"]))).Days; else if (dateGrouping.Equals(DateGrouping.Weekly)) dateGroupingsFromTop = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(((TimeSpan)(toDate - ((DateTime)promoter.ExtraSelectElements["Date"]))).Days) / 7d)); else if (dateGrouping.Equals(DateGrouping.Monthly)) { dateGroupingsFromTop = (toDate.AddDays(-1).Year - ((DateTime)promoter.ExtraSelectElements["Date"]).Year) * 12 + (toDate.AddDays(-1).Month - ((DateTime)promoter.ExtraSelectElements["Date"]).Month) + 1; } dataTableCells[dateGroupingsFromTop - 1, selectedSalesUsrs.Count * 3 + i + 1] = new HtmlTableCell(); dataTableCells[dateGroupingsFromTop - 1, selectedSalesUsrs.Count * 3 + i + 1].Style["font-weight"] = "bold"; dataTableCells[dateGroupingsFromTop - 1, selectedSalesUsrs.Count * 3 + i + 1].InnerHtml = Convert.ToInt32(promoter.ExtraSelectElements["TotalPromoters"]).ToString("#,##0"); } catch (Exception ex) { string x = ex.Message; } } foreach (SalesCall salesCall in salesCallsNewLeads) { try { int i = GetSalesPersonIndexNumber(selectedSalesUsrs, salesCall.UsrK); int dateGroupingsFromTop = 0; if (dateGrouping.Equals(DateGrouping.Daily)) dateGroupingsFromTop = ((TimeSpan)(toDate - ((DateTime)salesCall.ExtraSelectElements["Date"]))).Days; else if (dateGrouping.Equals(DateGrouping.Weekly)) dateGroupingsFromTop = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(((TimeSpan)(toDate - ((DateTime)salesCall.ExtraSelectElements["Date"]))).Days) / 7d)); else if (dateGrouping.Equals(DateGrouping.Monthly)) { dateGroupingsFromTop = (toDate.AddDays(-1).Year - ((DateTime)salesCall.ExtraSelectElements["Date"]).Year) * 12 + (toDate.AddDays(-1).Month - ((DateTime)salesCall.ExtraSelectElements["Date"]).Month) + 1; } dataTableCells[dateGroupingsFromTop - 1, selectedSalesUsrs.Count * 4 + i + 1] = new HtmlTableCell(); dataTableCells[dateGroupingsFromTop - 1, selectedSalesUsrs.Count * 4 + i + 1].InnerHtml = Convert.ToInt32(salesCall.ExtraSelectElements["TotalCalls"]).ToString("#,##0"); } catch (Exception ex) { string x = ex.Message; } } //foreach (SalesCall salesCall in salesCallsEffective) //{ // try // { // int i = GetSalesPersonIndexNumber(selectedSalesUsrs, salesCall.UsrK); // int dateGroupingsFromTop = 0; // if (dateGrouping.Equals(DateGrouping.Daily)) // dateGroupingsFromTop = ((TimeSpan)(toDate - ((DateTime)salesCall.ExtraSelectElements["Date"]))).Days; // else if (dateGrouping.Equals(DateGrouping.Weekly)) // dateGroupingsFromTop = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(((TimeSpan)(toDate - ((DateTime)salesCall.ExtraSelectElements["Date"]))).Days) / 7d)); // else if (dateGrouping.Equals(DateGrouping.Monthly)) // { // dateGroupingsFromTop = (toDate.AddDays(-1).Year - ((DateTime)salesCall.ExtraSelectElements["Date"]).Year) * 12 + (toDate.AddDays(-1).Month - ((DateTime)salesCall.ExtraSelectElements["Date"]).Month) + 1; // } // dataTableCells[dateGroupingsFromTop - 1, selectedSalesUsrs.Count * 5 + i + 1] = new HtmlTableCell(); // dataTableCells[dateGroupingsFromTop - 1, selectedSalesUsrs.Count * 5 + i + 1].InnerHtml = Convert.ToInt32(salesCall.ExtraSelectElements["TotalCalls"]).ToString("#,##0"); // } // catch (Exception ex) // { // string x = ex.Message; // } //} //LoadSalesCallPercentDataToTable(dataTableCells, selectedSalesUsrs, salesCallsCold, toDate, 6, dateGrouping); //LoadSalesCallPercentDataToTable(dataTableCells, selectedSalesUsrs, salesCallsFollowup, toDate, 7, dateGrouping); //LoadSalesCallPercentDataToTable(dataTableCells, selectedSalesUsrs, salesCallsActive, toDate, 8, dateGrouping); // Fill all empty table cells with zeros HtmlTableRow tr; for (int i = 0; i < numberOfDateGroupings; i++) { tr = new HtmlTableRow(); tr.Attributes.Clear(); for (int j = 0; j < selectedSalesUsrs.Count * (header1TableCells.Length - 1) + 1; j++) { if (dataTableCells[i, j] == null) { dataTableCells[i, j] = new HtmlTableCell(); if (j >= 1 && j <= selectedSalesUsrs.Count) dataTableCells[i, j].InnerHtml = "£0"; else if ((j > selectedSalesUsrs.Count && j <= selectedSalesUsrs.Count * 2) || (j > selectedSalesUsrs.Count * 3 && j <= selectedSalesUsrs.Count * 4)) dataTableCells[i, j].InnerHtml = "<b>0</b>"; else dataTableCells[i, j].InnerHtml = "0"; } if ((selectedSalesUsrs.Count == 1 && j > 0) || j % selectedSalesUsrs.Count - 1 == 0) { dataTableCells[i, j].Attributes.Add("class", "dataGridColumnDivider"); } dataTableCells[i, j].Align = "right"; tr.Cells.Add(dataTableCells[i, j]); } if (i % 2 == 0) tr.Attributes.Add("class", "dataGridItem"); else tr.Attributes.Add("class", "dataGridAltItem"); salesCallsTable.Rows.Add(tr); } #endregion #region Footer Row HtmlTableRow footerRow = new HtmlTableRow(); salesCallsTable.Rows.Add(footerRow); //footerRow.Attributes.Add("class", "dataGridFooter"); HtmlTableCell footerTotalLabelTableCell = new HtmlTableCell(); footerTotalLabelTableCell.Style.Add("border-top", "solid 1px #000000;"); footerTotalLabelTableCell.InnerHtml = "<b>Total:</b>"; footerTotalLabelTableCell.Align = "right"; footerRow.Cells.Add(footerTotalLabelTableCell); HtmlTableCell[,] footerTotalValueTableCells = new HtmlTableCell[selectedSalesUsrs.Count, 5]; for (int i = 0; i < 5; i++) { for (int j = 0; j < selectedSalesUsrs.Count; j++) { footerTotalValueTableCells[j, i] = new HtmlTableCell(); footerTotalValueTableCells[j, i].Align = "right"; if (i == 0) { footerTotalValueTableCells[j, i].Style.Add("border-top", "solid 1px #000000;"); decimal total = 0; for (int k = 0; k < numberOfDateGroupings; k++) { total += Utilities.ConvertMoneyStringToDecimal(Cambro.Web.Helpers.StripHtml(dataTableCells[k, selectedSalesUsrs.Count * i + j + 1].InnerHtml)); } if (i == 0) footerTotalValueTableCells[j, i].InnerHtml = "<b>" + total.ToString("£#,##0") + "</b>"; else footerTotalValueTableCells[j, i].InnerHtml = "<b>" + total.ToString() + "</b>"; } else footerTotalValueTableCells[j, i].InnerHtml = " "; if (j % selectedSalesUsrs.Count == 0) { footerTotalValueTableCells[j, i].Attributes.Add("class", "dataGridColumnDivider"); } footerRow.Cells.Add(footerTotalValueTableCells[j, i]); } } #endregion } } } }
protected void AddAllNow_Click(object sender, System.EventArgs e) { Query q = new Query(); Join j = new Join( Usr.BuddyUsrJoin, new TableElement(TablesEnum.UsrMusicTypeFavourite), QueryJoinType.Left, Usr.Columns.K, UsrMusicTypeFavourite.Columns.UsrK); q.TableElement = new Join( j, new TableElement(TablesEnum.UsrPlaceVisit), QueryJoinType.Left, Usr.Columns.K, UsrPlaceVisit.Columns.UsrK); if (ThreadK > 0) { q.TableElement = new Join( q.TableElement, new TableElement(TablesEnum.ThreadUsr), QueryJoinType.Left, new And( new Q(Usr.Columns.K, ThreadUsr.Columns.UsrK, true), new Q(ThreadUsr.Columns.ThreadK, ThreadK) ) ); } else if (RestrictionGroupK > 0) { q.TableElement = new Join( q.TableElement, new TableElement(TablesEnum.GroupUsr), QueryJoinType.Left, new And( new Q(Usr.Columns.K, GroupUsr.Columns.UsrK, true), new Q(GroupUsr.Columns.GroupK, RestrictionGroupK) ) ); } Q placeQ = new Q(true); if (!AddAllPlaceDrop.SelectedValue.Equals("0")) { placeQ = new Or( new Q(Usr.Columns.HomePlaceK, int.Parse(AddAllPlaceDrop.SelectedValue)), new Q(UsrPlaceVisit.Columns.PlaceK, int.Parse(AddAllPlaceDrop.SelectedValue))); } int musicTypeK = int.Parse(AddAllMusicDrop.SelectedValue); Q musicQ = new Q(true); if (musicTypeK > 1) { ArrayList musicQs = new ArrayList(); MusicType mt = new MusicType(musicTypeK); musicQs.Add(new Q(Usr.Columns.FavouriteMusicTypeK, 1)); musicQs.Add(new Q(UsrMusicTypeFavourite.Columns.MusicTypeK, 1)); musicQs.Add(new Q(Usr.Columns.FavouriteMusicTypeK, musicTypeK)); musicQs.Add(new Q(UsrMusicTypeFavourite.Columns.MusicTypeK, musicTypeK)); foreach (MusicType mtChild in mt.Children) { musicQs.Add(new Q(Usr.Columns.FavouriteMusicTypeK, mtChild.K)); musicQs.Add(new Q(UsrMusicTypeFavourite.Columns.MusicTypeK, mtChild.K)); } musicQ = new Or((Q[])musicQs.ToArray(typeof(Q))); } Q restrictionQ = new Q(true); if (ThreadK > 0) { restrictionQ = new Q(ThreadUsr.Columns.UsrK, QueryOperator.IsNull, null); } else if (RestrictionGroupK > 0) { restrictionQ = RestrictionGroupQ; } q.QueryCondition = new And( new Q(Buddy.Columns.BuddyUsrK, Usr.Current.K), new Q(Buddy.Columns.FullBuddy, true), new Q(Buddy.Columns.CanBuddyInvite, true), restrictionQ, musicQ, placeQ); q.Columns = new ColumnSet(Usr.Columns.NickName, Usr.Columns.K, Usr.Columns.Pic, Usr.Columns.FacebookUID); q.Distinct = true; q.OrderBy = new OrderBy(Usr.Columns.NickName); q.DistinctColumn = Usr.Columns.K; UsrSet us = new UsrSet(q); int duplicate = 0; int done = 0; foreach (Usr u in us) { bool yes = this.AddUsr(u); if (yes) done++; else duplicate++; } string selectedDuplicate = ""; if (duplicate > 0) selectedDuplicate = us.Count.ToString("#,##0") + " " + (us.Count == 1 ? "buddy" : "buddies") + " selected\n" + duplicate.ToString("#,##0") + " of them " + (duplicate == 1 ? "was" : "were") + " already in the list\n"; AlertMessageAll(selectedDuplicate + done.ToString("#,##0") + " " + (done == 1 ? "buddy" : "buddies") + " added"); if (done > 0) { MoreButton.InnerText = "More..."; ShowMore = false; SetVisibility(); } if (AnchorSkip.Length > 0) ((Spotted.Master.DsiPage)Page).AnchorSkip(AnchorSkip); }
protected override void Execute() { if (!IsAlreadyRunning.Value) { Caching.Instances.Main.Store(MemcachedKey.Value, Job.JobStatus.Running, new TimeSpan(0, 15, 0)); Update uThreadUsr = new Update(); try { if (StatusChangeObjectType.Value == null && StatusChangeObjectK.Value != null) throw new Exception("Usr.UpdateThreadUsrJob(): Invalid StatusChangeObject."); uThreadUsr.Table = TablesEnum.ThreadUsr; uThreadUsr.Changes.Add(new Assign(ThreadUsr.Columns.Status, ChangeStatus.Value)); uThreadUsr.Changes.Add(new Assign(ThreadUsr.Columns.StatusChangeDateTime, Common.Time.Now)); uThreadUsr.Where = new Q(ThreadUsr.Columns.UsrK, UsrK.Value); if (ThreadStatusesToChange.Value != null && ThreadStatusesToChange.Value.Count > 0) { Or statusOr = new Or(); foreach (ThreadUsr.StatusEnum statusEnum in ThreadStatusesToChange.Value) { statusOr = new Or(statusOr, new Q(ThreadUsr.Columns.Status, statusEnum)); } uThreadUsr.Where = new And(uThreadUsr.Where, statusOr); } else throw new Exception("Usr.UpdateThreadUsrs(): Invalid list of ThreadUsr.StatusEnum to change."); if (StatusChangeObjectType.Value != null) { if (StatusChangeObjectType.Value == Model.Entities.ObjectType.Usr) { // do nothing here } else { uThreadUsr.Where = new And(uThreadUsr.Where, new Q(ThreadUsr.Columns.StatusChangeObjectType, StatusChangeObjectType.Value)); } if (StatusChangeObjectK.Value != null) { if (StatusChangeObjectType.Value == Model.Entities.ObjectType.Usr) { uThreadUsr.Where = new And(uThreadUsr.Where, new Q(ThreadUsr.Columns.InvitingUsrK, StatusChangeObjectK.Value)); } else { uThreadUsr.Where = new And(uThreadUsr.Where, new Q(ThreadUsr.Columns.StatusChangeObjectK, StatusChangeObjectK.Value)); } } } uThreadUsr.CommandTimeout = 300; uThreadUsr.Run(); // Update memcached Caching.Instances.Main.Store(MemcachedKey.Value, Job.JobStatus.Completed, new TimeSpan(0, 15, 0)); } catch (Exception ex) { Caching.Instances.Main.Store(MemcachedKey.Value, Job.JobStatus.Failed, new TimeSpan(0, 15, 0)); throw ex; } } }
public void CompPanel_Load(object o, System.EventArgs e) { if (OnlyShowThreads) { CompPanel.Visible = false; return; } Q RelevanceQ = null; if (Discussable == null) RelevanceQ = new Q(true); else if (Discussable.UsedDiscussable is Event) RelevanceQ = new Q(Comp.Columns.EventK, Discussable.UsedDiscussable.K); else if (Discussable.UsedDiscussable is Venue) RelevanceQ = new Q(Event.Columns.VenueK, Discussable.UsedDiscussable.K); else if (Discussable.UsedDiscussable is Place) RelevanceQ = new Q(Venue.Columns.PlaceK, Discussable.UsedDiscussable.K); else if (Discussable.UsedDiscussable is Country) RelevanceQ = new Q(Place.Columns.CountryK, Discussable.UsedDiscussable.K); else if (Discussable.UsedDiscussable is Brand) RelevanceQ = new Or( new Q(Brand.Columns.K, Discussable.UsedDiscussable.K), new Q(Comp.Columns.BrandK, Discussable.UsedDiscussable.K)); else if (Discussable.UsedDiscussable is Group) RelevanceQ = new Q(Group.Columns.K, Discussable.UsedDiscussable.K); TableElement t = new TableElement(TablesEnum.Comp); if (Discussable != null && Discussable.UsedDiscussable is Venue) t = new Join(Comp.Columns.EventK, Event.Columns.K); else if (Discussable != null && Discussable != null && Discussable.UsedDiscussable is Place) t = new Join(new Join(Comp.Columns.EventK, Event.Columns.K), new TableElement(TablesEnum.Venue), QueryJoinType.Inner, Event.Columns.VenueK, Venue.Columns.K); else if (Discussable != null && Discussable.UsedDiscussable is Country) t = new Join( new Join( new JoinLeft(Comp.Columns.EventK, Event.Columns.K), new TableElement(TablesEnum.Venue), QueryJoinType.Left, Event.Columns.VenueK, Venue.Columns.K), new TableElement(TablesEnum.Place), QueryJoinType.Left, Venue.Columns.PlaceK, Place.Columns.K); else if (Discussable != null && Discussable.UsedDiscussable is Brand) t = new Join(new TableElement(TablesEnum.Comp), Event.BrandJoin, QueryJoinType.Left, Comp.Columns.EventK, Event.Columns.K); else if (Discussable != null && Discussable.UsedDiscussable is Group) t = new Join(new TableElement(TablesEnum.Comp), Event.GroupJoin, QueryJoinType.Inner, Comp.Columns.EventK, Event.Columns.K); t = Templates.Comps.Latest.PerformJoins(t); Query q = new Query(); q.Columns = Templates.Comps.Latest.Columns; q.QueryCondition = new And( new Q(Comp.Columns.Status, Comp.StatusEnum.Enabled), new Q(Comp.Columns.DateTimeClose, QueryOperator.GreaterThan, DateTime.Now), new Q(Comp.Columns.DateTimeStart, QueryOperator.LessThan, DateTime.Now), RelevanceQ ); q.OrderBy = new OrderBy(new OrderBy(Comp.Columns.PrizeValueRange, OrderBy.OrderDirection.Descending), new OrderBy(OrderBy.OrderDirection.Random)); q.TopRecords = Items; q.TableElement = t; CompSet cs = new CompSet(q); if (cs.Count == 0) CompPanel.Visible = false; else { CompPanel.Visible = true; CompDataList.DataSource = cs; CompDataList.ItemTemplate = this.LoadTemplate("/Templates/Comps/Latest.ascx"); CompDataList.DataBind(); if (Discussable == null) CompArchiveAnchor.HRef = Archive.GetUrl(DateTime.Now.Year, DateTime.Now.Month, 0, ArchiveObjectType.Comp, new string[] { }, ""); else if (Discussable.UsedDiscussable is IHasArchive && cs.Count == Items) CompArchiveAnchor.HRef = ((IHasArchive)Discussable.UsedDiscussable).UrlArchiveDate(DateTime.Now.Year, DateTime.Now.Month, 0, ArchiveObjectType.Comp); else { CompArchiveDiv.Visible = false; CompPanelInner.Attributes["class"] = "LatestPanel Big CleanLinks"; } } }
private void RunUpdate(bool isArchive) { if (StatusChangeObjectType.Value == null && StatusChangeObjectK.Value != null) throw new Exception("Usr.SmartDeleteThreadUsrJob(): Invalid StatusChangeObject."); if(ThreadStatusesToChange.Value.Count == 0) throw new Exception("Usr.SmartDeleteThreadUsrJob(): Invalid ThreadStatusesToChange."); Update uThreadUsr = new Update(); uThreadUsr.Table = TablesEnum.ThreadUsr; uThreadUsr.From = new Join(ThreadUsr.Columns.ThreadK, Thread.Columns.K); uThreadUsr.Changes.Add(new Assign(ThreadUsr.Columns.StatusChangeDateTime, Common.Time.Now)); uThreadUsr.Where = new Q(ThreadUsr.Columns.UsrK, UsrK.Value); if (isArchive) { uThreadUsr.Changes.Add(new Assign(ThreadUsr.Columns.Status, ThreadUsr.StatusEnum.Archived)); uThreadUsr.Where = new And(uThreadUsr.Where, new Q(Thread.Columns.TotalWatching, QueryOperator.LessThanOrEqualTo, SMART_DELETE_CUT_OFF_NUMBER_OF_PEOPLE_WATCHING)); } else { uThreadUsr.Changes.Add(new Assign(ThreadUsr.Columns.Status, ThreadUsr.StatusEnum.Ignore)); uThreadUsr.Where = new And(uThreadUsr.Where, new Q(Thread.Columns.TotalWatching, QueryOperator.GreaterThan, SMART_DELETE_CUT_OFF_NUMBER_OF_PEOPLE_WATCHING)); } if (ThreadStatusesToChange.Value != null && ThreadStatusesToChange.Value.Count > 0) { Or statusOr = new Or(); foreach (ThreadUsr.StatusEnum statusEnum in ThreadStatusesToChange.Value) { statusOr = new Or(statusOr, new Q(ThreadUsr.Columns.Status, statusEnum)); } uThreadUsr.Where = new And(uThreadUsr.Where, statusOr); } else throw new Exception("Usr.SmartDeleteThreadUsrJob(): Invalid list of ThreadUsr.StatusEnum to change."); if (StatusChangeObjectType.Value != null) { if (StatusChangeObjectType.Value == Model.Entities.ObjectType.Usr) { // do nothing here } else { uThreadUsr.Where = new And(uThreadUsr.Where, new Q(ThreadUsr.Columns.StatusChangeObjectType, StatusChangeObjectType.Value)); } if (StatusChangeObjectK.Value != null) { if (StatusChangeObjectType.Value == Model.Entities.ObjectType.Usr) { uThreadUsr.Where = new And(uThreadUsr.Where, new Q(ThreadUsr.Columns.InvitingUsrK, StatusChangeObjectK.Value)); } else { uThreadUsr.Where = new And(uThreadUsr.Where, new Q(ThreadUsr.Columns.StatusChangeObjectK, StatusChangeObjectK.Value)); } } } uThreadUsr.CommandTimeout = 300; uThreadUsr.Run(); }
protected void Page_Load(object sender, EventArgs e) { if (!hasParent || (CurrentCountry != null && CurrentCountry.K == 224) || (CurrentGroup != null && !CurrentGroup.HasEvents)) { this.Visible = false; return; } NextEventList.Future = true; PastEventList.Future = false; if (CurrentCountry != null) { NextEventList.ParentObjectType = Model.Entities.ObjectType.Country; NextEventList.Filter = new Q(Place.Columns.CountryK, CurrentCountry.K); NextEventList.Join = Event.PlaceAllJoin; NextEventList.Calendar = CurrentCountry; NextEventList.Size = 10; Q photosQ = new Q(true); PastEventList.OnlyPhotos = false; PastEventList.ParentObjectType = Model.Entities.ObjectType.Country; if (Country.FilterK == 0 || Country.Current.Mature) { photosQ = new Or( new Q(Event.Columns.HasSpotter, true), new Q(Event.Columns.LivePhotos, QueryOperator.GreaterThan, 0)); PastEventList.OnlyPhotos = true; } PastEventList.Filter = new And( new Q(Place.Columns.CountryK, CurrentCountry.K), photosQ ); PastEventList.Join = Event.PlaceAllJoin; PastEventList.Calendar = CurrentCountry; PastEventList.Size = 10; } else if (CurrentPlace != null) { NextEventList.ParentObjectType = Model.Entities.ObjectType.Place; NextEventList.Filter = new Q(Place.Columns.K, CurrentPlace.K); NextEventList.Join = Event.PlaceAllJoin; NextEventList.Size = 7; NextEventList.Calendar = CurrentPlace; PastEventList.ParentObjectType = Model.Entities.ObjectType.Place; PastEventList.Filter = new Q(Place.Columns.K, CurrentPlace.K); PastEventList.Join = Event.PlaceAllJoin; PastEventList.Size = 7; PastEventList.Calendar = CurrentPlace; } else if (CurrentVenue != null) { NextEventList.ParentObjectType = Model.Entities.ObjectType.Venue; NextEventList.Filter = new Q(Event.Columns.VenueK, CurrentVenue.K); NextEventList.Size = 7; NextEventList.Calendar = CurrentVenue; NextEventList.AddEventUrl = CurrentVenue.AddEventLink; PastEventList.ParentObjectType = Model.Entities.ObjectType.Venue; PastEventList.Filter = new Q(Event.Columns.VenueK, CurrentVenue.K); PastEventList.Size = 7; PastEventList.Calendar = CurrentVenue; PastEventList.AddEventUrl = CurrentVenue.AddEventLink; } else if (CurrentBrand != null) { NextEventList.ParentObjectType = Model.Entities.ObjectType.Brand; NextEventList.Filter = new Q(EventBrand.Columns.BrandK, CurrentBrand.K); NextEventList.Size = 7; NextEventList.Join = Event.LeftBrandJoin; NextEventList.Calendar = CurrentBrand; PastEventList.ParentObjectType = Model.Entities.ObjectType.Brand; PastEventList.Filter = new Q(EventBrand.Columns.BrandK, CurrentBrand.K); PastEventList.Size = 7; PastEventList.Join = Event.LeftBrandJoin; PastEventList.Calendar = CurrentBrand; } else if (CurrentGroup != null) { NextEventList.ParentObjectType = Model.Entities.ObjectType.Group; NextEventList.Filter = new Q(GroupEvent.Columns.GroupK, CurrentGroup.K); NextEventList.Size = 7; NextEventList.Join = Event.LeftGroupJoin; NextEventList.Calendar = CurrentGroup; PastEventList.ParentObjectType = Model.Entities.ObjectType.Group; PastEventList.Filter = new Q(GroupEvent.Columns.GroupK, CurrentGroup.K); PastEventList.Size = 7; PastEventList.Join = Event.LeftGroupJoin; PastEventList.Calendar = CurrentGroup; } NextEventList.Bind(); PastEventList.Bind(); }
void FileAssignDropDownBuild() { Query q = new Query(); Q ExtQ = null; #region Leaderboard Q LeaderboardFlash = new And( new Q(Bobs.Misc.Columns.Extention, "swf"), new Q(Bobs.Misc.Columns.Size, QueryOperator.LessThanOrEqualTo, 150 * 1024), new Or( new Q(Bobs.Misc.Columns.NeedsAuth, true), new And( new Q(Bobs.Misc.Columns.NeedsAuth, false), new Q(Bobs.Misc.Columns.BannerBroken, false) ) ) ); Q LeaderboardGif = new And( new Q(Bobs.Misc.Columns.Extention, "gif"), new Q(Bobs.Misc.Columns.Size, QueryOperator.LessThanOrEqualTo, 150 * 1024), new Q(Bobs.Misc.Columns.Width, 728), new Q(Bobs.Misc.Columns.Height, 90) ); Q LeaderboardJpg = new And( new Q(Bobs.Misc.Columns.Extention, "jpg"), new Q(Bobs.Misc.Columns.Size, QueryOperator.LessThanOrEqualTo, 150 * 1024), new Q(Bobs.Misc.Columns.Width, 728), new Q(Bobs.Misc.Columns.Height, 90) ); #endregion #region Skyscraper Q SkyscraperFlash = new And( new Q(Bobs.Misc.Columns.Extention, "swf"), new Q(Bobs.Misc.Columns.Size, QueryOperator.LessThanOrEqualTo, 150 * 1024), new Or( new Q(Bobs.Misc.Columns.NeedsAuth, true), new And( new Q(Bobs.Misc.Columns.NeedsAuth, false), new Q(Bobs.Misc.Columns.BannerBroken, false) ) ) ); Q SkyscraperGif = new And( new Q(Bobs.Misc.Columns.Extention, "gif"), new Q(Bobs.Misc.Columns.Size, QueryOperator.LessThanOrEqualTo, 150 * 1024), //XMAS new Q(Bobs.Misc.Columns.Width, 300), new Q(Bobs.Misc.Columns.Height, 250) //600 ); Q SkyscraperJpg = new And( new Q(Bobs.Misc.Columns.Extention, "jpg"), new Q(Bobs.Misc.Columns.Size, QueryOperator.LessThanOrEqualTo, 150 * 1024), //XMAS new Q(Bobs.Misc.Columns.Width, 300), new Q(Bobs.Misc.Columns.Height, 250) //600 ); #endregion #region Hotbox Q HotboxFlash = new And( new Q(Bobs.Misc.Columns.Extention, "swf"), new Q(Bobs.Misc.Columns.Size, QueryOperator.LessThanOrEqualTo, 150 * 1024), new Or( new Q(Bobs.Misc.Columns.NeedsAuth, true), new And( new Q(Bobs.Misc.Columns.NeedsAuth, false), new Q(Bobs.Misc.Columns.BannerBroken, false) ) ) ); Q HotboxGif = new And( new Q(Bobs.Misc.Columns.Extention, "gif"), new Q(Bobs.Misc.Columns.Size, QueryOperator.LessThanOrEqualTo, 150 * 1024), new Or( new And(new Q(Bobs.Misc.Columns.Width, 191),new Q(Bobs.Misc.Columns.Height, 191)), new And(new Q(Bobs.Misc.Columns.Width, 300),new Q(Bobs.Misc.Columns.Height, 250)) ) ); Q HotboxJpg = new And( new Q(Bobs.Misc.Columns.Extention, "jpg"), new Q(Bobs.Misc.Columns.Size, QueryOperator.LessThanOrEqualTo, 150 * 1024), new Or( new And(new Q(Bobs.Misc.Columns.Width, 191), new Q(Bobs.Misc.Columns.Height, 191)), new And(new Q(Bobs.Misc.Columns.Width, 300), new Q(Bobs.Misc.Columns.Height, 250)) ) ); #endregion #region PhotoBanner Q PhotoBannerFlash = new And( new Q(Bobs.Misc.Columns.Extention, "swf"), new Q(Bobs.Misc.Columns.Size, QueryOperator.LessThanOrEqualTo, 30 * 1024), new Or( new Q(Bobs.Misc.Columns.NeedsAuth, true), new And( new Q(Bobs.Misc.Columns.NeedsAuth, false), new Q(Bobs.Misc.Columns.BannerBroken, false) ) ) ); Q PhotoBannerGif = new And( new Q(Bobs.Misc.Columns.Extention, "gif"), new Q(Bobs.Misc.Columns.Size, QueryOperator.LessThanOrEqualTo, 30 * 1024), new Q(Bobs.Misc.Columns.Width, 450), new Q(Bobs.Misc.Columns.Height, 50) ); Q PhotoBannerJpg = new And( new Q(Bobs.Misc.Columns.Extention, "jpg"), new Q(Bobs.Misc.Columns.Size, QueryOperator.LessThanOrEqualTo, 30 * 1024), new Q(Bobs.Misc.Columns.Width, 450), new Q(Bobs.Misc.Columns.Height, 50) ); #endregion #region EmailBanner //Q EmailBannerFlash = new Q(false); Q EmailBannerGif = new And( new Q(Bobs.Misc.Columns.Extention, "gif"), new Q(Bobs.Misc.Columns.Size, QueryOperator.LessThanOrEqualTo, 40 * 1024), new Q(Bobs.Misc.Columns.Width, 331), new Q(Bobs.Misc.Columns.Height, 51) ); Q EmailBannerJpg = new And( new Q(Bobs.Misc.Columns.Extention, "jpg"), new Q(Bobs.Misc.Columns.Size, QueryOperator.LessThanOrEqualTo, 40 * 1024), new Q(Bobs.Misc.Columns.Width, 331), new Q(Bobs.Misc.Columns.Height, 51) ); #endregion if (CurrentBanner.Position.Equals(Banner.Positions.Leaderboard)) ExtQ = new Or(LeaderboardFlash, LeaderboardGif, LeaderboardJpg); else if (CurrentBanner.Position.Equals(Banner.Positions.Skyscraper)) ExtQ = new Or(SkyscraperFlash, SkyscraperGif, SkyscraperJpg); else if (CurrentBanner.Position.Equals(Banner.Positions.Hotbox)) ExtQ = new Or(HotboxFlash, HotboxGif, HotboxJpg); else if (CurrentBanner.Position.Equals(Banner.Positions.PhotoBanner)) ExtQ = new Or(PhotoBannerFlash, PhotoBannerGif, PhotoBannerJpg); else if (CurrentBanner.Position.Equals(Banner.Positions.EmailBanner)) ExtQ = new Or(EmailBannerGif, EmailBannerJpg); if (ExtQ != null) { q.QueryCondition = new And(new Q(Bobs.Misc.Columns.PromoterK, CurrentPromoter.K), ExtQ); q.OrderBy = Bobs.Misc.OrderBy; MiscSet ms = new MiscSet(q); if (ms.Count > 0) { FileAssignDropDown.DataSource = ms; FileAssignDropDown.DataTextField = "FullNameWithK"; FileAssignDropDown.DataValueField = "K"; FileAssignDropDown.DataBind(); FileAssignNoFiles.Visible = false; FileAssignDropDownP.Visible = true; } else { FileAssignNoFiles.Visible = true; FileAssignDropDownP.Visible = false; FileAssignNoFilesUploadLink.HRef = CurrentPromoter.UrlApp("files", "mode", "upload"); } } }