protected void Page_Load(object sender, EventArgs e)
{
Query q = new Query();
q.Columns = new ColumnSet(SpottedException.Columns.Message, SpottedException.Columns.MasterPath, SpottedException.Columns.PagePath);
q.ExtraSelectElements.Add("Count", "count(distinct case when dsiguid is null then newid() else dsiguid end)");
q.GroupBy = new GroupBy(new GroupBy(SpottedException.Columns.Message), new GroupBy(SpottedException.Columns.MasterPath), new GroupBy(SpottedException.Columns.PagePath));
Q notInQ = new Q(true);
foreach (string s in new string[] { "Bobs.BobNotFound", "Bobs.DSIUserFriendlyException", "MalformedUrlException" })
{
notInQ = new And(notInQ, new Q(SpottedException.Columns.ExceptionType, QueryOperator.NotEqualTo, s));
}
q.QueryCondition = new And(new Q(SpottedException.Columns.ExceptionDateTime, QueryOperator.GreaterThanOrEqualTo, DateTime.Now.AddDays(-1)),
notInQ);
q.OrderBy = new OrderBy("Count DESC");
q.TopRecords = 50;
GridView.DataSource = new Bobs.SpottedExceptionSet(q);
GridView.DataBind();
}
public Event HasSingleEvent(int Year, int Month, int Day)
{
Query q = new Query();
q.NoLock=true;
q.Columns=new ColumnSet(Event.Columns.K,Event.Columns.VenueK,Event.Columns.DateTime);
Q DateTimeQ = null;
if (Year>0 && Month>0 && Day>0)
DateTimeQ = new Q(Event.Columns.DateTime,new DateTime(Year,Month,Day));
else if (Year>0 && Month>0)
DateTimeQ = new And(
new Q(Event.Columns.DateTime,QueryOperator.GreaterThanOrEqualTo,new DateTime(Year,Month,1)),
new Q(Event.Columns.DateTime,QueryOperator.LessThan,new DateTime(Year,Month,1).AddMonths(1)));
else if (Year>0)
DateTimeQ = new And(
new Q(Event.Columns.DateTime,QueryOperator.GreaterThanOrEqualTo,new DateTime(Year,1,1)),
new Q(Event.Columns.DateTime,QueryOperator.LessThan,new DateTime(Year,1,1).AddYears(1)));
q.TopRecords=2;
q.TableElement=Event.BrandJoin;
q.QueryCondition=new And(new Q(Brand.Columns.K,this.K),DateTimeQ);
EventSet es = new EventSet(q);
if (es.Count==1)
return es[0];
else
return null;
}
public void SetBlockedAndUpdate(bool blocked)
{
if (!Usr.Current.IsAdmin)
{
throw new Exception("You need to be an admin to block or unblock a tag");
}
Transaction t = new Transaction();
Query q = new Query(new Q(TagPhoto.Columns.TagK, this.K));
List<int> tagPhotoKs = (new TagPhotoSet(q)).ToList().ConvertAll(tagPhoto => tagPhoto.K);
List<Assign> changes = new List<Assign>()
{
new Assign(TagPhoto.Columns.Disabled, blocked)
};
Q condition = new And(
new Q(TagPhoto.Columns.TagK, this.K),
new Q(TagPhoto.Columns.Disabled, !blocked)
);
Update u = new Update(TablesEnum.TagPhoto, changes, condition);
u.Run(t);
foreach (int tagPhotoK in tagPhotoKs)
{
TagPhotoHistory historyItem = new TagPhotoHistory()
{
Action = blocked ? TagPhotoHistory.TagPhotoHistoryAction.Blocked : TagPhotoHistory.TagPhotoHistoryAction.Unblocked,
DateTime = DateTime.Now,
TagPhotoK = tagPhotoK,
UsrK = Usr.Current.K
};
historyItem.Update(t);
}
this.Blocked = blocked;
this.BlockedDateTime = DateTime.Now;
this.BlockedByUsrK = Usr.Current.K;
this.Update(t);
t.Commit();
(new Caching.CacheKeys.NamespaceCacheKey(CacheKeyPrefix.TagCloudVersion)).Invalidate();
}
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 List<PromoterAccountItem> GetPromoterAccountItemList(DateTime startDate, DateTime endDate, IBob referenceBob)
{
int startingRecord = this.RecordsPerPage * (this.pageNumber -1);
List<PromoterAccountItem> promoterAccountItems = new List<PromoterAccountItem>();
InvoiceSet invoices;
TransferSet transfers;
Query invoiceSummaryQuery = new Query();
Query transferSummaryQuery = new Query();
// Descending date order: Lastest first, oldest last
// Replacing CreatedDateTime with TaxDateTime, as per Gee's request for OASIS v1.5
// Order By CreatedDateTime instead of TaxDateTime, as per Dave's request 7/2/07
invoiceSummaryQuery.OrderBy = new OrderBy(Invoice.Columns.CreatedDateTime, OrderBy.OrderDirection.Descending);
invoiceSummaryQuery.Paging.RecordsPerPage = 0;
invoiceSummaryQuery.Paging.RequestedPage = 1;
transferSummaryQuery.OrderBy = new OrderBy("IsNull([Transfer].[DateTimeComplete], [Transfer].[DateTimeCreated]) DESC");
transferSummaryQuery.Paging.RecordsPerPage = 0;
transferSummaryQuery.Paging.RequestedPage = 1;
if (referenceBob != null)
{
if (referenceBob is Invoice)
{
Invoice invoice = (Invoice)referenceBob;
if (invoice.Type.Equals(Invoice.Types.Invoice))
{
invoiceSummaryQuery.QueryCondition = new Q(InvoiceCredit.Columns.InvoiceK, invoice.K);
invoiceSummaryQuery.TableElement = new Join(Invoice.Columns.K, InvoiceCredit.Columns.CreditInvoiceK);
}
else
{
invoiceSummaryQuery.QueryCondition = new Q(InvoiceCredit.Columns.CreditInvoiceK, invoice.K);
invoiceSummaryQuery.TableElement = new Join(Invoice.Columns.K, InvoiceCredit.Columns.InvoiceK);
}
// Replacing TaxDateTime with CreatedDateTime, as per Dave's request 7/2/07
invoiceSummaryQuery.Columns = new ColumnSet(Invoice.Columns.K, Invoice.Columns.Paid, Invoice.Columns.PromoterK, Invoice.Columns.CreatedDateTime, Invoice.Columns.DueDateTime,
Invoice.Columns.Total, Invoice.Columns.Type, InvoiceCredit.Columns.Amount);
invoiceSummaryQuery.Paging.RecordsPerPage = 0;
invoiceSummaryQuery.Paging.RequestedPage = 1;
transferSummaryQuery.QueryCondition = new And(new Q(InvoiceTransfer.Columns.InvoiceK, invoice.K),
new Q(Transfer.Columns.Amount, QueryOperator.NotEqualTo, 0),
new Or(new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success),
new And(new Q(Transfer.Columns.Type, Transfer.TransferTypes.Refund),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Pending))));
transferSummaryQuery.TableElement = new Join(Transfer.Columns.K, InvoiceTransfer.Columns.TransferK);
transferSummaryQuery.Columns = new ColumnSet(Transfer.Columns.K, Transfer.Columns.Status, Transfer.Columns.PromoterK, Transfer.Columns.DateTimeComplete, Transfer.Columns.DateTimeCreated, Transfer.Columns.Method,
Transfer.Columns.TransferRefundedK, Transfer.Columns.Amount, Transfer.Columns.Type, InvoiceTransfer.Columns.Amount);
transferSummaryQuery.Paging.RecordsPerPage = 0;
transferSummaryQuery.Paging.RequestedPage = 1;
}
else if (referenceBob is Transfer)
{
Transfer transfer = (Transfer)referenceBob;
if (transfer.Type.Equals(Transfer.TransferTypes.Payment))
{
transferSummaryQuery.QueryCondition = new Q(Transfer.Columns.TransferRefundedK, transfer.K);
}
else
{
transferSummaryQuery.QueryCondition = new Q(Transfer.Columns.K, transfer.TransferRefundedK);
}
transferSummaryQuery.Paging.RecordsPerPage = 0;
transferSummaryQuery.Paging.RequestedPage = 1;
invoiceSummaryQuery.QueryCondition = new Q(InvoiceTransfer.Columns.TransferK, transfer.K);
invoiceSummaryQuery.TableElement = new Join(Invoice.Columns.K, InvoiceTransfer.Columns.InvoiceK);
// Replacing TaxDateTime with CreatedDateTime, as per Dave's request 7/2/07
invoiceSummaryQuery.Columns = new ColumnSet(Invoice.Columns.K, Invoice.Columns.Paid, Invoice.Columns.PromoterK, Invoice.Columns.CreatedDateTime, Invoice.Columns.DueDateTime,
Invoice.Columns.Total, Invoice.Columns.Type, InvoiceTransfer.Columns.Amount);
invoiceSummaryQuery.Paging.RecordsPerPage = 0;
invoiceSummaryQuery.Paging.RequestedPage = 1;
}
}
else
{
Q InvoiceQ = new Q(Invoice.Columns.PromoterK, CurrentPromoter.K);
// Replacing TaxDateTime with CreatedDateTime, as per Dave's request 7/2/07
Q DateQ = new And(new Q(Invoice.Columns.CreatedDateTime, QueryOperator.GreaterThanOrEqualTo, startDate),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.LessThanOrEqualTo, endDate));
// Outstanding only applies to Invoices
if (this.FilterDropDownList.SelectedValue.Equals(Invoice.Statuses.Outstanding.ToString() + " Invoices"))
{
invoiceSummaryQuery.QueryCondition = new And(InvoiceQ, DateQ, new Q(Invoice.Columns.Paid, false), new Q(Invoice.Columns.Type, Invoice.Types.Invoice));//, new Q(Invoice.Columns.Type, Invoice.Types.Invoice));
transferSummaryQuery.QueryCondition = new Q(Transfer.Columns.K, -1);
}
else
{
invoiceSummaryQuery.QueryCondition = new And(InvoiceQ, DateQ);
transferSummaryQuery.QueryCondition = new And(new Q(Transfer.Columns.PromoterK, CurrentPromoter.K),
new Q(Transfer.Columns.Amount, QueryOperator.NotEqualTo, 0),
new Or(new And(new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success),
new Q(Transfer.Columns.DateTimeComplete, QueryOperator.GreaterThanOrEqualTo, startDate),
new Q(Transfer.Columns.DateTimeComplete, QueryOperator.LessThanOrEqualTo, endDate)),
new And(new Q(Transfer.Columns.Type, Transfer.TransferTypes.Refund),
new Q(Transfer.Columns.DateTimeCreated, QueryOperator.GreaterThanOrEqualTo, startDate),
new Q(Transfer.Columns.DateTimeCreated, QueryOperator.LessThanOrEqualTo, endDate),
new Or(new Q(Transfer.Columns.Status, Transfer.StatusEnum.Pending),
new Q(Transfer.Columns.Status, Transfer.StatusEnum.Success)))));
}
}
invoiceSummaryQuery.FillMaxRecords = this.pageNumber * this.RecordsPerPage + 1;
transferSummaryQuery.FillMaxRecords = this.pageNumber * this.RecordsPerPage + 1;
invoices = new InvoiceSet(invoiceSummaryQuery);
transfers = new TransferSet(transferSummaryQuery);
if (referenceBob == null)
{
if (this.GetAllOutstanding)
{
this.PaginationPanel.Visible = false;
}
else
{
this.PaginationPanel.Visible = true;
this.PrevPageLinkButton.Enabled = this.pageNumber != 1;
this.NextPageLinkButton.Enabled = invoices.Count + transfers.Count > this.pageNumber * this.RecordsPerPage;
}
}
// Merge into one Bob List
int invoiceCounter = 0;
int transferCounter = 0;
while (startingRecord > invoices.Count + transfers.Count)
{
startingRecord -= this.RecordsPerPage;
}
int endingRecord = startingRecord + this.RecordsPerPage;
while ((invoices.Count > invoiceCounter || transfers.Count > transferCounter) && invoiceCounter + transferCounter < endingRecord)
{
if (invoices.Count > invoiceCounter && transfers.Count > transferCounter)
{
// Replacing TaxDateTime with CreatedDateTime, as per Dave's request 7/2/07
if (invoices[invoiceCounter].CreatedDateTime > (transfers[transferCounter].DateTimeComplete > transfers[transferCounter].DateTimeCreated ? transfers[transferCounter].DateTimeComplete : transfers[transferCounter].DateTimeCreated))
{
if (invoiceCounter + transferCounter >= startingRecord)
{
if (referenceBob != null)
{
FixFiguresForSubItem(invoices[invoiceCounter], referenceBob);
}
promoterAccountItems.Add(new PromoterAccountItem(invoices[invoiceCounter]));
}
invoiceCounter++;
}
else
{
if (invoiceCounter + transferCounter >= startingRecord)
{
if (referenceBob != null)
{
FixFiguresForSubItem(transfers[transferCounter], referenceBob);
}
promoterAccountItems.Add(new PromoterAccountItem(transfers[transferCounter]));
}
transferCounter++;
}
}
else if (invoices.Count > invoiceCounter)
{
if (invoiceCounter + transferCounter >= startingRecord)
{
if (referenceBob != null)
{
FixFiguresForSubItem(invoices[invoiceCounter], referenceBob);
}
promoterAccountItems.Add(new PromoterAccountItem(invoices[invoiceCounter]));
}
invoiceCounter++;
}
else
{
if (invoiceCounter + transferCounter >= startingRecord)
{
if (referenceBob != null)
{
FixFiguresForSubItem(transfers[transferCounter], referenceBob);
}
promoterAccountItems.Add(new PromoterAccountItem(transfers[transferCounter]));
}
transferCounter++;
}
}
return promoterAccountItems;
}
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();
public void Cal_Load(object o, System.EventArgs e)
{
int usrK = 0;
if (Usr.Current != null)
usrK = Usr.Current.K;
TableElement te = new TableElement(TablesEnum.Comment);
te = new Join(
te,
Thread.Columns.K,
Comment.Columns.ThreadK);
te = new Join(
te,
new TableElement(TablesEnum.ThreadUsr),
QueryJoinType.Left,
new And(
new Q(Thread.Columns.K, ThreadUsr.Columns.ThreadK, true),
new Q(ThreadUsr.Columns.UsrK, usrK),
new Q(ThreadUsr.Columns.Status, QueryOperator.NotEqualTo, ThreadUsr.StatusEnum.Deleted)));
te = new Join(
te,
new TableElement(TablesEnum.GroupUsr),
QueryJoinType.Left,
new And(
new Q(Thread.Columns.GroupK, GroupUsr.Columns.GroupK, true),
new Q(GroupUsr.Columns.UsrK, usrK),
new Q(GroupUsr.Columns.Status, GroupUsr.StatusEnum.Member)));
Q q = new And(
new Q(Comment.Columns.UsrK, ThisUsr.K),
new Or(
new Q(Thread.Columns.Private, false),
new Q(ThreadUsr.Columns.UsrK, usrK)
),
new Or(
new Q(Thread.Columns.GroupPrivate, false),
new Q(GroupUsr.Columns.UsrK, usrK)
),
new Or(
new Q(Thread.Columns.PrivateGroup, false),
new Q(GroupUsr.Columns.UsrK, usrK)
)
);
Cal.MonthUrlGetter = new Controls.Cal.MonthUrlDelegate(GetMonthUrl);
Cal.DayUrlGetter = new Controls.Cal.DayUrlDelegate(GetDayUrl);
Cal.DateTimeColumn = new Column(Comment.Columns.DateTime);
Cal.TableElement = te;
Cal.QueryCondition = q;
}
public static EventSet GetEventSetFromEventBoxKey(string key)
{
EventPageStub data = EventPageDetails.GetStubFromKey(key);
Query q = new Query();
Q dateQ = new Q(true);
Q ticketsQ = new Q(true);
if (data.tabType == TabType.Tickets)
{
q.OrderBy = new OrderBy(@"
1.0 * (LOG(2) / LOG((DATEDIFF(wk, GETDATE(), [Event].[DateTime]) * 0.25) + 2)) +
1.0 * (1 - (LOG((1.0 / (([TicketHeat] / 10.0) + 1)) + 1) / LOG(2))) +
1.0 * (1 - (LOG((1.0 / (([Event].[UsrAttendCount] / 30.0) + 1)) + 1) / LOG(2))) --+
--1.0 * (1 - (LOG((1.0 / (([Event].[TotalComments] / 100.0) + 1)) + 1) / LOG(2)))
DESC, [Event].[K]", Event.Columns.DateTime, Event.Columns.UsrAttendCount, Event.Columns.TotalComments);
dateQ = Event.FutureEventsQueryCondition;
ticketsQ = new Q(Event.Columns.IsTicketsAvailable, true);
}
else if (data.tabType == TabType.Future)
{
q.OrderBy = new OrderBy(@"
1.0 * (LOG(2) / LOG((DATEDIFF(wk, GETDATE(), [Event].[DateTime]) * 0.25) + 2)) +
1.0 * (1 - (LOG((1.0 / (([Event].[UsrAttendCount] / 30.0) + 1)) + 1) / LOG(2))) --+
--1.0 * (1 - (LOG((1.0 / (([Event].[TotalComments] / 100.0) + 1)) + 1) / LOG(2)))
DESC, [Event].[K]", Event.Columns.DateTime, Event.Columns.UsrAttendCount, Event.Columns.TotalComments);
dateQ = Event.FutureEventsQueryCondition;
}
else
{
q.OrderBy = new OrderBy(@"
1.0 * (LOG(2) / LOG(0 - (DATEDIFF(wk, GETDATE(), [Event].[DateTime]) * 0.25) + 2)) +
1.0 * (1 - (LOG((1.0 / (([Event].[UsrAttendCount] / 30.0) + 1)) + 1) / LOG(2))) --+
--0.5 * (1 - (LOG((1.0 / (([Event].[TotalComments] / 100.0) + 1)) + 1) / LOG(2))) +
--0.5 * (1 - (LOG((1.0 / (([Event].[LivePhotos] / 75.0) + 1)) + 1) / LOG(2))) +
DESC, [Event].[K]", Event.Columns.DateTime, Event.Columns.UsrAttendCount, Event.Columns.TotalComments, Event.Columns.LivePhotos);
//q.OrderBy = Event.PastEventOrder;
dateQ = new And(Event.PreviousEventsQueryCondition, new Q(Event.Columns.DateTime, QueryOperator.GreaterThan, DateTime.Today.AddDays(-60)));
}
//supported object types: None, Brand, Country, Place
Q parentObjectQ;
TableElement parentObjectTableElement;
if (data.parentObjectType == Model.Entities.ObjectType.None)
{
parentObjectTableElement = new TableElement(TablesEnum.Event);
parentObjectQ = new Q(true);
}
else if (data.parentObjectType == Model.Entities.ObjectType.Country)
{
parentObjectTableElement = Event.PlaceAllJoin;
parentObjectQ = new Q(Place.Columns.CountryK, data.parentObjectK);
}
else if (data.parentObjectType == Model.Entities.ObjectType.Place)
{
parentObjectTableElement = Event.VenueAllJoin;
parentObjectQ = new Q(Venue.Columns.PlaceK, data.parentObjectK);
}
else if (data.parentObjectType == Model.Entities.ObjectType.Brand)
{
parentObjectTableElement = Event.EventBrandJoin;
parentObjectQ = new Q(EventBrand.Columns.BrandK, data.parentObjectK);
}
else
throw new Exception("Unsupported object type");
q.TableElement = parentObjectTableElement;
Q musicTypeQ = new Q(true);
if (data.musicTypeK > 1)
{
List<int> al = new List<int>();
MusicType mt = new MusicType(data.musicTypeK);
al.Add(mt.K);
if (mt.ParentK == 1)
{
foreach (MusicType mtChild in mt.Children)
{
al.Add(mtChild.K);
}
}
musicTypeQ = new InListQ(EventMusicType.Columns.MusicTypeK, al);
q.TableElement = new Join(
parentObjectTableElement,
new TableElement(TablesEnum.EventMusicType),
QueryJoinType.Left,
Event.Columns.K,
EventMusicType.Columns.EventK);
q.Distinct = true;
q.DistinctColumn = Event.Columns.K;
}
q.QueryCondition = new And(
dateQ,
parentObjectQ,
musicTypeQ,
ticketsQ);
q.CacheDuration = TimeSpan.FromDays(1);
q.Paging.RecordsPerPage = 8;
q.Paging.RequestedPageIndex = data.pageIndex;
EventSet es = new EventSet(q);
return es;
}
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
}
}
}
}
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");
}
}
}