public void BindCalls()
{
Query q = new Query();
q.QueryCondition = new And(
new Q(SalesCall.Columns.UsrK, Usr.Current.K),
new Q(SalesCall.Columns.IsCall, true),
new Or(new Q(SalesCall.Columns.InProgress, true), new Q(SalesCall.Columns.Dismissed, false)));
SalesCallSet scs = new SalesCallSet(q);
if (scs.Count > 0)
{
foreach (SalesCall sc in scs)
{
Spotted.Controls.Admin.SalesCallControl c = (Spotted.Controls.Admin.SalesCallControl)this.LoadControl("~/Controls/Admin/SalesCallControl.ascx");
c.CurrentSalesCall = sc;
SalesCallPlaceHolder.Controls.Add(c);
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString["type"].Equals("hangup"))
{
Phone p = Phone.GetFromMac(Request.QueryString["mac"]);
SalesCallSet scs = new SalesCallSet(new Query(new And(new Q(SalesCall.Columns.UsrK,p.UsrK), new Q(SalesCall.Columns.InProgress, true))));
foreach (SalesCall sc in scs)
{
sc.InProgress = false;
sc.DateTimeEnd = DateTime.Now;
if (sc.DateTimeEnd < sc.DateTimeStart)
sc.DateTimeEnd = sc.DateTimeStart;
TimeSpan ts = sc.DateTimeEnd - sc.DateTimeStart;
sc.Duration = ts.TotalMinutes;
sc.Update();
}
}
else if (Request.QueryString["type"].Equals("register"))
{
try
{
Phone p = Phone.GetFromMac(Request.QueryString["mac"]);
p.IpAddress = Request.ServerVariables["REMOTE_ADDR"];
p.Update();
Response.Write("Done - " + Request.ServerVariables["REMOTE_ADDR"]);
}
catch
{
Response.Write("Can't find phone with mac: " + Request.QueryString["mac"]);
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!ContainerPage.Url.HasMonthFilter || !ContainerPage.Url["UsrK"].IsInt)
Response.Redirect("/admin/salescalls/usrk-1/year-" + DateTime.Now.Year + "/month-" + DateTime.Now.Month);
Query q1 = new Query();
q1.QueryCondition = new Q(Usr.Columns.IsSalesPerson, true);
q1.OrderBy = new OrderBy(Usr.Columns.K);
UsrSet us = new UsrSet(q1);
foreach (Usr u in us)
{
if (CurrentUsr != null && u.K == CurrentUsr.K)
UsersPh.Controls.Add(new LiteralControl("<b>" + u.NickName + "</b>"));
else
{
if (ContainerPage.Url.HasDayFilter)
{
UsersPh.Controls.Add(new LiteralControl("<a href=\"/admin/salescalls/usrk-" + u.K + "/year-" + ContainerPage.Url.DateFilter.Year + "/month-" + ContainerPage.Url.DateFilter.Month + "/day-" + ContainerPage.Url.DateFilter.Day + "\" " + u.Rollover + ">" + u.NickName + "</a>"));
}
else
{
UsersPh.Controls.Add(new LiteralControl("<a href=\"/admin/salescalls/usrk-" + u.K + "/year-" + ContainerPage.Url.DateFilter.Year + "/month-" + ContainerPage.Url.DateFilter.Month + "\" " + u.Rollover + ">" + u.NickName + "</a>"));
}
}
UsersPh.Controls.Add(new LiteralControl(" | "));
}
Cal.MonthUrlGetter = new Controls.Cal.MonthUrlDelegate(GetMonthUrl);
Cal.DayUrlGetter = new Controls.Cal.DayUrlDelegate(GetDayUrl);
Cal.DateTimeColumn = new Column(SalesCall.Columns.DateTimeStart);
Cal.TableElement = new TableElement(TablesEnum.SalesCall);
Cal.QueryCondition = new And(new Q(SalesCall.Columns.UsrK, CurrentUsr.K), new Q(SalesCall.Columns.IsCall, true));
DateTime dtStart = new DateTime(ContainerPage.Url["Year"], ContainerPage.Url["Month"], 1);
DateTime dtEnd = new DateTime(ContainerPage.Url["Year"], ContainerPage.Url["Month"], 1).AddMonths(1);
if (ContainerPage.Url["Day"].Exists)
{
dtStart = new DateTime(ContainerPage.Url["Year"], ContainerPage.Url["Month"], ContainerPage.Url["Day"]);
dtEnd = new DateTime(ContainerPage.Url["Year"], ContainerPage.Url["Month"], ContainerPage.Url["Day"]).AddDays(1);
}
Query q = new Query();
q.QueryCondition = new And(
new Q(SalesCall.Columns.UsrK, CurrentUsr.K),
new Q(SalesCall.Columns.DateTimeStart, QueryOperator.GreaterThanOrEqualTo, dtStart),
new Q(SalesCall.Columns.DateTimeStart, QueryOperator.LessThan, dtEnd)
);
q.OrderBy = new OrderBy(SalesCall.Columns.DateTimeStart);
SalesCallSet scs = new SalesCallSet(q);
StringBuilder sb = new StringBuilder();
foreach (SalesCall sc in scs)
{
SalesCallToString(sc, sb);
}
SalesCallsPh.Controls.Add(new LiteralControl(sb.ToString()));
}
public void BindNotes()
{
#region Important Notes / Calls
Query q = new Query(new And(new Q(SalesCall.Columns.PromoterK, CurrentPromoter.K),
new Q(SalesCall.Columns.IsImportant, true)));
q.OrderBy = new OrderBy(SalesCall.Columns.DateTimeStart, OrderBy.OrderDirection.Descending);
SalesCallSet importantSalesCalls = new SalesCallSet(q);
if (importantSalesCalls.Count == 0)
ImportantCallsPanel.Visible = false;
else
{
ImportantCallsPanel.Visible = true;
this.ImportantCallsGridView.DataSource = importantSalesCalls;
this.ImportantCallsGridView.DataBind();
}
#endregion
#region Notes / Calls
Query q2 = new Query(new And(new Q(SalesCall.Columns.PromoterK, CurrentPromoter.K),
new Or(new Q(SalesCall.Columns.IsImportant, 0),
new Q(SalesCall.Columns.IsImportant, QueryOperator.IsNull, null))));
q2.OrderBy = new OrderBy(SalesCall.Columns.DateTimeStart, OrderBy.OrderDirection.Descending);
q2.TopRecords = 100;
SalesCallSet scs = new SalesCallSet(q2);
if (scs.Count == 0)
CallsPanel.Visible = false;
else
{
if (scs.Count > 10)
{
CallsDiv.Style["height"] = "200px";
CallsDiv.Style["overflow"] = "auto";
}
else if (scs.Count > 5)
{
CallsDiv.Style["height"] = "100px";
CallsDiv.Style["overflow"] = "auto";
}
CallsPanel.Visible = true;
this.CallsGridView.DataSource = scs;
this.CallsGridView.DataBind();
//StringBuilder sb = new StringBuilder();
//foreach (SalesCall sc in scs)
//{
// sb.Append("<p>" + SalesCallToString(sc) + "</p>");
//}
//CallsPh.Controls.Clear();
//CallsPh.Controls.Add(new LiteralControl(sb.ToString()));
}
#endregion
}
public void MakeCall(bool sales)
{
if (Usr.Current.IsAdmin)
{
string number;
if (AdminPhoneNumbersDropDown.SelectedValue.Equals("0"))
number = CurrentPromoter.PhoneNumber;
else
{
Usr u = new Usr(int.Parse(AdminPhoneNumbersDropDown.SelectedValue));
number = u.MobileDial;
}
if (number.Length>0)
{
Query qCurrent = new Query();
qCurrent.QueryCondition = new And(
new Q(SalesCall.Columns.PromoterK, CurrentPromoter.K),
new Q(SalesCall.Columns.InProgress, true),
new Q(SalesCall.Columns.IsCall, true));
SalesCallSet scsCurrent = new SalesCallSet(qCurrent);
if (scsCurrent.Count > 0)
{
SalesCallError.Text = "<br>" + scsCurrent[0].Usr.NickName + " has a current call with this promoter. Please get them to hang up, or try again later.";
SalesCallError.Visible = true;
return;
}
if (sales)
{
Query qDup = new Query();
qDup.QueryCondition = new Q(SalesCall.Columns.DuplicateGuid, (Guid)this.ViewState["SalesCallDuplicateGuid"]);
SalesCallSet scsDup = new SalesCallSet(qDup);
if (scsDup.Count == 0)
{
//com.dontstayin.hoth.Phone Phone = new Spotted.com.dontstayin.hoth.Phone();
Phone p = Bobs.Phone.GetFromUsrK(Usr.Current.K);
p.MakeCall(number);
SalesCall sc = new SalesCall();
sc.DuplicateGuid = (Guid)this.ViewState["SalesCallDuplicateGuid"];
sc.UsrK = Usr.Current.K;
sc.PromoterK = CurrentPromoter.K;
sc.DateTimeStart = DateTime.Now;
sc.Dismissed = false;
sc.InProgress = true;
sc.IsCallToNewLead = CurrentPromoter.IsNewLead;
sc.Direction = SalesCall.Directions.Outgoing;
if (CurrentPromoter.EffectiveSalesStatus.Equals(Promoter.SalesStatusEnum.Active))
sc.Type = SalesCall.Types.Active;
else if (CurrentPromoter.EffectiveSalesStatus.Equals(Promoter.SalesStatusEnum.Proactive))
sc.Type = SalesCall.Types.ProactiveFollowUp;
else
sc.Type = SalesCall.Types.Cold;
sc.Effective = false;
sc.IsCall = true;
sc.Update();
CurrentPromoter.UpdateSalesCallCount(true);
Response.Redirect(CurrentPromoter.Url());
}
}
else
{
//com.dontstayin.hoth.Phone Phone = new Spotted.com.dontstayin.hoth.Phone();
Phone p = Bobs.Phone.GetFromUsrK(Usr.Current.K);
p.MakeCall(number);
}
}
}
}
protected void TakeIncomingCall(object sender, EventArgs eventArgs)
{
if (Usr.Current.IsAdmin)
{
Query qDup = new Query();
qDup.QueryCondition = new Q(SalesCall.Columns.DuplicateGuid, (Guid)this.ViewState["SalesCallDuplicateGuid"]);
SalesCallSet scsDup = new SalesCallSet(qDup);
if (scsDup.Count == 0)
{
SalesCall sc = new SalesCall();
sc.DuplicateGuid = (Guid)this.ViewState["SalesCallDuplicateGuid"];
sc.UsrK = Usr.Current.K;
sc.PromoterK = CurrentPromoter.K;
sc.DateTimeStart = DateTime.Now;
sc.Dismissed = false;
sc.InProgress = true;
sc.IsCallToNewLead = CurrentPromoter.IsNewLead;
sc.Direction = SalesCall.Directions.Incoming;
if (CurrentPromoter.EffectiveSalesStatus.Equals(Promoter.SalesStatusEnum.Active))
sc.Type = SalesCall.Types.Active;
else if (CurrentPromoter.EffectiveSalesStatus.Equals(Promoter.SalesStatusEnum.Proactive))
sc.Type = SalesCall.Types.ProactiveFollowUp;
else
sc.Type = SalesCall.Types.Cold;
sc.Effective = true;
sc.IsCall = true;
sc.Update();
sc.EffectiveAction();
CurrentPromoter.UpdateSalesCallCount(true);
Response.Redirect(CurrentPromoter.Url());
}
}
}
private void GenerateSalesTodayTable(List<UsrDataHolder> salesUsrs)
{
DateTime fromDate = DateTime.Today;
DateTime toDate = fromDate.AddDays(1);
if (salesUsrs.Count > 0)
{
#region Sales: Total Money
Query totalSalesAmountQuery = new Query(new And(new Q(Usr.Columns.SalesTeam, QueryOperator.GreaterThan, 0),
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(Usr.Columns.K, Invoice.Columns.SalesUsrK, QueryJoinType.Left);
totalSalesAmountQuery.ExtraSelectElements.Add("TotalSales", "SUM(ISNULL([Invoice].[SalesUsrAmount],0))");
totalSalesAmountQuery.Columns = new ColumnSet(Invoice.Columns.SalesUsrK, Usr.Columns.FirstName);
totalSalesAmountQuery.OrderBy = new OrderBy("[Usr].[FirstName], SUM(SalesUsrAmount)");
totalSalesAmountQuery.GroupBy = new GroupBy("[Usr].[FirstName], [Invoice].[SalesUsrK]");
InvoiceSet invoices = new InvoiceSet(totalSalesAmountQuery);
#endregion
#region Sales Calls: SalesUsrK, Minutes, and Total calls
Query salesCallMinutesQuery = new Query(new And(new Q(SalesCall.Columns.IsCall, true),
new Q(SalesCall.Columns.DateTimeStart, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(SalesCall.Columns.DateTimeStart, QueryOperator.LessThan, toDate),
new Q(Usr.Columns.IsSalesPerson, true)));
//salesCallMinutesQuery.ExtraSelectElements.Add("Minutes", "SUM(ISNULL([SalesCall].[Duration],0))");
salesCallMinutesQuery.ExtraSelectElements.Add("TotalCalls", "COUNT([SalesCall].[Duration])");
salesCallMinutesQuery.TableElement = new Join(Usr.Columns.K, SalesCall.Columns.UsrK, QueryJoinType.Left);
salesCallMinutesQuery.Columns = new ColumnSet(SalesCall.Columns.UsrK);
salesCallMinutesQuery.OrderBy = new OrderBy("[Usr].[FirstName]");
salesCallMinutesQuery.GroupBy = new GroupBy("[Usr].[FirstName], [SalesCall].[UsrK]");
SalesCallSet salesCallsMinutes = new SalesCallSet(salesCallMinutesQuery);
#endregion
#region Table
HtmlTable salesTodayTable = this.SalesTodayTable;
salesTodayTable.Rows.Clear();
#endregion
#region Header Row
HtmlTableRow headerRow = new HtmlTableRow();
headerRow.Attributes.Add("class", "dataGridHeader");
salesTodayTable.Rows.Add(headerRow);
HtmlTableCell[] headerTableCells = new HtmlTableCell[3];
for (int i = 0; i < headerTableCells.Length; i++)
{
headerTableCells[i] = new HtmlTableCell("th");
headerTableCells[i].Align = "right";
headerTableCells[i].InnerHtml = " ";
headerRow.Cells.Add(headerTableCells[i]);
}
headerTableCells[0].Style.Add("border-left", "0px;");
//headerTableCells[1].InnerHtml = "£";
//headerTableCells[2].InnerHtml = "Ca-<br>lls";
#endregion
#region Data Rows
HtmlTableRow[] dataRows = new HtmlTableRow[salesUsrs.Count];
for (int i = 0; i < dataRows.Length; i++)
{
dataRows[i] = new HtmlTableRow();
HtmlTableCell[] dataTableCells = new HtmlTableCell[3];
for (int k = 0; k < dataTableCells.Length; k++)
{
dataTableCells[k] = new HtmlTableCell();
dataRows[i].Cells.Add(dataTableCells[k]);
}
dataTableCells[0].InnerHtml = salesUsrs[i].FirstName;
dataTableCells[0].Style.Add("border-left", "0px;");
dataTableCells[1].Align = "right";
dataTableCells[2].Align = "right";
//dataTableCells[3].Align = "right";
dataTableCells[1].InnerHtml = "£0";
dataTableCells[2].InnerHtml = "0 calls";
//dataTableCells[3].InnerHtml = "0";
if (i == dataRows.Length - 1)
{
dataTableCells[0].Style.Add("border-bottom", "0px;");
dataTableCells[1].Style.Add("border-bottom", "0px;");
dataTableCells[2].Style.Add("border-bottom", "0px;");
//dataTableCells[3].Style.Add("border-bottom", "0px;");
}
for (int m = 0; m < invoices.Count; m++)
{
if (invoices[m].SalesUsrK == salesUsrs[i].K)
{
dataTableCells[1].InnerHtml = "£" + Convert.ToDouble(invoices[m].ExtraSelectElements["TotalSales"]).ToString("#,##0");
break;
}
}
for (int n = 0; n < salesCallsMinutes.Count; n++)
{
if (salesCallsMinutes[n].UsrK == salesUsrs[i].K)
{
//dataTableCells[2].InnerHtml = Convert.ToDouble(salesCallsMinutes[n].ExtraSelectElements["Minutes"]).ToString("#,##0");
dataTableCells[2].InnerHtml = Convert.ToDouble(salesCallsMinutes[n].ExtraSelectElements["TotalCalls"]).ToString("#,##0") + " calls";
break;
}
}
salesTodayTable.Rows.Add(dataRows[i]);
}
#endregion
}
}
public void BindSalesCalls()
{
if (Usr.Current != null && Usr.Current.IsAdmin)
{
Query q = new Query();
q.QueryCondition = new And(new Q(SalesCall.Columns.UsrK, Usr.Current.K),
new Q(SalesCall.Columns.IsCall, true),
new Or(new Q(SalesCall.Columns.InProgress, true),
new Q(SalesCall.Columns.Dismissed, false)));
SalesCallSet scs = new SalesCallSet(q);
SalesCallPanel.Controls.Clear();
SalesCallPanel.Visible = scs.Count > 0;
if (scs.Count > 0)
{
foreach (SalesCall sc in scs)
{
Spotted.Controls.Admin.SalesCallControl c = (Spotted.Controls.Admin.SalesCallControl)this.LoadControl("~/Controls/Admin/SalesCallControl.ascx");
c.CurrentSalesCall = sc;
SalesCallPanel.Controls.Add(c);
}
}
}
else
SalesCallPanel.Visible = false;
}
private void GenerateSalesTodayTable(List<UsrDataHolder> salesUsrs)
{
DateTime fromDate = DateTime.Today;
DateTime toDate = fromDate.AddDays(1);
if (salesUsrs.Count > 0)
{
#region Sales: Total Money
Query totalSalesAmountQuery = new Query(new And(new Q(Usr.Columns.SalesTeam, QueryOperator.GreaterThan, 0),
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(Usr.Columns.K, Invoice.Columns.SalesUsrK, QueryJoinType.Left);
totalSalesAmountQuery.ExtraSelectElements.Add("TotalSales", "SUM(ISNULL([Invoice].[SalesUsrAmount],0))");
totalSalesAmountQuery.Columns = new ColumnSet(Invoice.Columns.SalesUsrK, Usr.Columns.FirstName);
totalSalesAmountQuery.OrderBy = new OrderBy("[Usr].[FirstName], SUM(SalesUsrAmount)");
totalSalesAmountQuery.GroupBy = new GroupBy("[Usr].[FirstName], [Invoice].[SalesUsrK]");
InvoiceSet invoices = new InvoiceSet(totalSalesAmountQuery);
#endregion
#region Sales Calls: SalesUsrK, Minutes, and Total calls
Query salesCallMinutesQuery = new Query(new And(new Q(SalesCall.Columns.IsCall, true),
new Q(SalesCall.Columns.DateTimeStart, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(SalesCall.Columns.DateTimeStart, QueryOperator.LessThan, toDate),
new Q(Usr.Columns.IsSalesPerson, true)));
//salesCallMinutesQuery.ExtraSelectElements.Add("Minutes", "SUM(ISNULL([SalesCall].[Duration],0))");
salesCallMinutesQuery.ExtraSelectElements.Add("TotalCalls", "COUNT([SalesCall].[Duration])");
salesCallMinutesQuery.TableElement = new Join(Usr.Columns.K, SalesCall.Columns.UsrK, QueryJoinType.Left);
salesCallMinutesQuery.Columns = new ColumnSet(SalesCall.Columns.UsrK);
salesCallMinutesQuery.OrderBy = new OrderBy("[Usr].[FirstName]");
salesCallMinutesQuery.GroupBy = new GroupBy("[Usr].[FirstName], [SalesCall].[UsrK]");
SalesCallSet salesCallsMinutes = new SalesCallSet(salesCallMinutesQuery);
#endregion
#region Table
HtmlTable salesTodayTable = this.SalesTodayTable;
salesTodayTable.Rows.Clear();
#endregion
#region Header Row
HtmlTableRow headerRow = new HtmlTableRow();
headerRow.Attributes.Add("class", "dataGridHeader");
salesTodayTable.Rows.Add(headerRow);
HtmlTableCell[] headerTableCells = new HtmlTableCell[3];
for (int i = 0; i < headerTableCells.Length; i++)
{
headerTableCells[i] = new HtmlTableCell("th");
headerTableCells[i].Align = "right";
headerTableCells[i].InnerHtml = " ";
headerRow.Cells.Add(headerTableCells[i]);
}
headerTableCells[0].Style.Add("border-left", "0px;");
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 UpdateSalesCallCount(bool update)
{
Query q = new Query();
q.QueryCondition = new Q(SalesCall.Columns.PromoterK, this.K);
q.ReturnCountOnly = true;
SalesCallSet scs = new SalesCallSet(q);
if (this.SalesCallCount != scs.Count)
{
this.SalesCallCount = scs.Count;
if (update)
this.Update();
}
}
public bool AddNote(string Note, Guid DuplicateGuid, Usr CurrentUsr, bool isImportant)
{
Query qDup = new Query();
qDup.QueryCondition = new Q(SalesCall.Columns.DuplicateGuid, DuplicateGuid);
SalesCallSet scsDup = new SalesCallSet(qDup);
if (scsDup.Count == 0)
{
SalesCall scNote = new SalesCall();
scNote.DuplicateGuid = DuplicateGuid;
scNote.UsrK = CurrentUsr.K;
scNote.PromoterK = this.K;
scNote.DateTimeStart = DateTime.Now;
scNote.DateTimeEnd = scNote.DateTimeStart;
scNote.Duration = 0;
scNote.Dismissed = true;
scNote.InProgress = false;
scNote.Direction = SalesCall.Directions.Outgoing;
if (this.EffectiveSalesStatus.Equals(Promoter.SalesStatusEnum.Active))
scNote.Type = SalesCall.Types.Active;
else if (this.EffectiveSalesStatus.Equals(Promoter.SalesStatusEnum.Proactive))
scNote.Type = SalesCall.Types.ProactiveFollowUp;
else
scNote.Type = SalesCall.Types.Cold;
scNote.IsCall = false;
scNote.Note = Note;
scNote.Effective = true;
scNote.IsImportant = isImportant;
scNote.Update();
this.ManualNote = Note + " [" + CurrentUsr.NickName + " " + DateTime.Now.ToShortDateString() + "]\n" + this.ManualNote;
this.UpdateSalesCallCount(false);
this.Update();
return true;
}
return false;
}
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
}
}
}
}
private void LoadSalesCallPercentDataToTable(HtmlTableCell[,] dataTableCells, List<UsrDataHolder> salesUsrs, SalesCallSet salesCalls, DateTime toDate, int column, DateGrouping dateGrouping)
{
foreach (SalesCall salesCall in salesCalls)
{
try
{
int i = GetSalesPersonIndexNumber(salesUsrs, 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;
}
if (dataTableCells[dateGroupingsFromTop - 1, salesUsrs.Count * 2 + i + 1] != null)
{
double totalCalls = Convert.ToDouble(dataTableCells[dateGroupingsFromTop - 1, salesUsrs.Count * 2 + i + 1].InnerHtml.ToString());
dataTableCells[dateGroupingsFromTop - 1, salesUsrs.Count * column + i + 1] = new HtmlTableCell();
dataTableCells[dateGroupingsFromTop - 1, salesUsrs.Count * column + i + 1].InnerHtml = Math.Round(Convert.ToDouble(salesCall.ExtraSelectElements["TotalCalls"]) * 100 / totalCalls, 0).ToString();
}
}
catch (Exception)
{ }
}
}
