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");
}
}
}