public ActionResult _ChartTitle(DateGrouping grouping, string itemId, bool accumulative)
{
var model = new ChartTitleViewModel();
model.Grouping = grouping;
model.Item = ItemViewModelLight.Create(ItemsXml, itemId);
model.Accumulative = accumulative;
return(PartialView("_ChartTitle", model));
}
public ActionResult DateCapsule(DateGrouping grouping, string capsuleCode)
{
DateTime inicio = DateTime.Now;
var reproduccionesDB = Database.SpawnsCapsulesItems.Where(p => p.SpawnsCapsules.Spawns.UserId == Username && (string.IsNullOrEmpty(capsuleCode) || p.SpawnsCapsules.CapsuleCode == capsuleCode))
.GroupBy(r => new { Date = DbFunctions.TruncateTime(r.SpawnsCapsules.Spawns.Date).Value, Code = r.SpawnsCapsules.CapsuleCode, ItemId = r.ItemId })
.Select(s => new {
Code = s.Key.Code,
Date = s.Key.Date,
ItemId = s.Key.ItemId,
Quantity = s.Sum(m => m.Quantity),
}).ToList();
var filas = reproduccionesDB
.GroupBy(r => new { Code = r.Code, Date = r.Date.GetResolvedDate(grouping) })
.Select(s => new FechaTotalViewModel {
Code = s.Key.Code,
Date = s.Key.Date,
TotalQuantity = s.Sum(p => p.Quantity),
Items = s.GroupBy(u => u.ItemId).Select(v => new ItemCapsuleViewModel {
Quantity = v.Sum(w => w.Quantity), CurrentItem = ItemBase.Create(ItemsXml, v.First().ItemId)
})
}).ToList();
var totales = reproduccionesDB
.GroupBy(r => r.Code)
.Select(s => new FechaTotalViewModel {
Code = s.Key,
Date = DateTime.MinValue,
TotalQuantity = s.Sum(p => p.Quantity),
Items = s.GroupBy(u => u.ItemId).Select(v => new ItemCapsuleViewModel {
Quantity = v.Sum(w => w.Quantity), CurrentItem = ItemBase.Create(ItemsXml, v.First().ItemId)
})
}).ToList();
var model = new ByDateViewModel();
model.Grouping = grouping;
model.Capsules = reproduccionesDB.Select(p => p.Code).Distinct().ToList();
model.DateInfo = reproduccionesDB.GroupBy(p => p.Date.GetResolvedDate(grouping)).Select(q => new DateInfoModel {
Date = q.Key, TotalCapsules = q.Select(z => z.Code).Distinct().Count(), TotalItems = q.Sum(r => r.Quantity), RealDays = (int)(q.Max(h => h.Date) - q.Min(i => i.Date)).TotalDays + 1
}).OrderByDescending(b => b.Date);
model.Rows = filas;
model.Totals = totales;
TimeSpan tiempo = DateTime.Now.Subtract(inicio);
return(View(model));
}
public static DateTime GetResolvedDate(this DateTime date, DateGrouping grouping)
{
switch (grouping)
{
case DateGrouping.Week:
return(date.AddDays(-(int)date.DayOfWeek).Date);
case DateGrouping.Month:
return(new DateTime(date.Year, date.Month, 1));
case DateGrouping.Year:
return(new DateTime(date.Year, 1, 1));
case DateGrouping.Day:
default:
return(new DateTime(date.Year, date.Month, date.Day));
}
}
static void FilterMixedDataTypesByValues(Workbook workbook)
{
#region #FilterMixedDataByValues
Worksheet worksheet = workbook.Worksheets["Regional sales"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Enable filtering for the "B2:E23" cell range.
CellRange range = worksheet["B2:E23"];
worksheet.AutoFilter.Apply(range);
// Create date grouping item to filter January 2015 dates.
IList <DateGrouping> groupings = new List <DateGrouping>();
DateGrouping dateGroupingJan2015 = new DateGrouping(new DateTime(2015, 1, 1), DateTimeGroupingType.Month);
groupings.Add(dateGroupingJan2015);
// Filter data in the "Reported Date" column
// to display values reported in January 2015.
worksheet.AutoFilter.Columns[3].ApplyFilterCriteria("gennaio 2015", groupings);
#endregion #FilterMixedDataByValues
}
static void FilterMixedDataTypesByValues(IWorkbook workbook)
{
workbook.LoadDocument("Documents\\SalesReport.xlsx");
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["Regional sales"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #FilterMixedDataTypesByValues
// Enable filtering for the specified cell range.
CellRange range = worksheet["B2:E23"];
worksheet.AutoFilter.Apply(range);
// Create date grouping item to filter January 2015 dates.
IList <DateGrouping> groupings = new List <DateGrouping>();
DateGrouping dateGroupingJan2015 = new DateGrouping(new DateTime(2015, 1, 1), DateTimeGroupingType.Month);
groupings.Add(dateGroupingJan2015);
// Filter the data in the "Reported Date" column to display values reported in January 2015.
worksheet.AutoFilter.Columns[3].ApplyFilterCriteria("gennaio 2015", groupings);
#endregion #FilterMixedDataTypesByValues
}
finally { workbook.EndUpdate(); }
}
public ActionResult InterestsChart(DateGrouping grouping, string itemId, bool accumulative)
{
DateTime inicio = DateTime.Now;
var reproduccionesDB = Database.SpawnsCapsules.Where(p => p.Spawns.UserId == Username)
.Select(s => new {
Code = s.CapsuleCode,
Date = s.Spawns.Date,
Items = s.SpawnsCapsulesItems.Select(y => new { ItemId = y.ItemId, Quantity = y.Quantity }),
}).ToList();
var model = new ByDateViewModel();
model.Grouping = grouping;
model.DateInfo = reproduccionesDB
.GroupBy(r => r.Date.GetResolvedDate(grouping))
.Select(s => new DateInfoModel {
Date = s.Key,
TotalCapsules = s.Where(n => n.Items.Any(q => string.IsNullOrEmpty(itemId) || q.ItemId == itemId)).Select(z => z.Code).Distinct().Count(),
TotalItems = s.SelectMany(n => n.Items).Where(b => string.IsNullOrEmpty(itemId) || b.ItemId == itemId).DefaultIfEmpty().Sum(p => p == null ? 0 : p.Quantity)
}).OrderBy(i => i.Date).ToList();
if (accumulative)
{
int actualItemQty = 0;
int actualCapsQty = 0;
foreach (DateInfoModel info in model.DateInfo)
{
info.TotalItems += actualItemQty;
actualItemQty = info.TotalItems;
info.TotalCapsules += actualCapsQty;
actualCapsQty = info.TotalCapsules;
}
}
TimeSpan tiempo = DateTime.Now.Subtract(inicio);
string dateFormat = Resources.General.ResourceManager.GetString(string.Format("InterestsChart_DateFormat_{0}", model.Grouping.ToString()));
PrivateFontCollection pfc = new PrivateFontCollection();
pfc.AddFontFile(Server.MapPath("~/Content/Coda-Regular.ttf"));
using (Chart chart = new Chart()) {
chart.Font.Name = pfc.Families[0].Name;
chart.Font.Size = FontUnit.Point(8);
chart.Width = 1000;
chart.Height = 400;
chart.BackColor = Color.FromArgb(255, 0x27, 0x2B, 0x30);
chart.BorderlineDashStyle = ChartDashStyle.Solid;
chart.BorderlineColor = Color.Gray;
chart.BorderlineWidth = 1;
chart.Palette = ChartColorPalette.None;
chart.PaletteCustomColors = new[] { Color.Orange, Color.LightGray };
using (Font fuente = new Font(pfc.Families[0], 8, GraphicsUnit.Point)) {
ChartArea area = new ChartArea();
area.BackColor = Color.Transparent;
area.ShadowColor = Color.Transparent;
area.BorderColor = Color.FromArgb(255, 0x88, 0x88, 0x88);
area.BorderDashStyle = ChartDashStyle.Solid;
var ejeX = area.AxisX;
ejeX.LabelStyle.Font = fuente;
ejeX.LabelStyle.ForeColor = Color.White;
ejeX.LineColor = Color.FromArgb(255, 0x99, 0x99, 0x99);
ejeX.IsLabelAutoFit = false;
ejeX.IsMarginVisible = true;
ejeX.MajorGrid.LineColor = Color.FromArgb(255, 0x99, 0x99, 0x99);
ejeX.MajorTickMark.LineColor = Color.FromArgb(255, 0xAA, 0xAA, 0xAA);
var interval = Math.Max(model.DateInfo.Count() / 25, 1);
ejeX.Interval = interval;
ejeX.LabelStyle.Angle = -90;
var ejeY = area.AxisY;
ejeY.LabelStyle.Font = fuente;
ejeY.LabelStyle.ForeColor = Color.White;
ejeY.LineColor = Color.FromArgb(255, 0x99, 0x99, 0x99);
ejeY.IsLabelAutoFit = false;
ejeY.IsMarginVisible = true;
ejeY.MajorGrid.LineColor = Color.FromArgb(255, 0x99, 0x99, 0x99);
ejeY.MajorTickMark.LineColor = Color.FromArgb(255, 0xAA, 0xAA, 0xAA);
chart.ChartAreas.Add(area);
Series itemsSerie = new Series("Items");
itemsSerie.Font = fuente;
itemsSerie.ChartType = SeriesChartType.Spline;
itemsSerie.Points.DataBindXY(model.DateInfo.Select(p => p.Date.ToString(dateFormat)).ToArray(), model.DateInfo.Select(p => p.TotalItems).ToArray());
itemsSerie.IsValueShownAsLabel = grouping != DateGrouping.Day;
itemsSerie.LabelForeColor = Color.Orange;
chart.Series.Add(itemsSerie);
Series capsulesSerie = new Series("Capsules");
capsulesSerie.Font = fuente;
capsulesSerie.ChartType = SeriesChartType.Column;
capsulesSerie.Points.DataBindXY(model.DateInfo.Select(p => p.Date.ToString(dateFormat)).ToArray(), model.DateInfo.Select(p => p.TotalCapsules).ToArray());
capsulesSerie.IsValueShownAsLabel = grouping != DateGrouping.Day;
capsulesSerie.LabelForeColor = Color.LightGray;
chart.Series.Add(capsulesSerie);
MemoryStream ms = new MemoryStream();
chart.SaveImage(ms, ChartImageFormat.Png);
return(File(ms.ToArray(), "image/png"));
}
}
}
public FileResult PercentageRarityChart(DateGrouping grouping, bool percentage)
{
DateTime inicio = DateTime.Now;
var type = SeriesChartType.StackedColumn;
if (percentage)
{
type = SeriesChartType.StackedColumn100;
}
string dateFormat = Resources.General.ResourceManager.GetString(string.Format("InterestsChart_DateFormat_{0}", grouping.ToString()));
var rarityDef = new[] {
new { Rarity = "R1", Name = "Very Common", Color = "#b5b2b5" },
new { Rarity = "R2", Name = "Common", Color = "#84f7b5" },
new { Rarity = "R3", Name = "Rare", Color = "#ad8eff" },
new { Rarity = "R4", Name = "Very Rare", Color = "#ff8ef7" }
};
var dbData = Database.SpawnsCapsulesItems.Where(p => p.SpawnsCapsules.Spawns.UserId == Username)
.GroupBy(r => new { Date = DbFunctions.TruncateTime(r.SpawnsCapsules.Spawns.Date).Value, ItemId = r.ItemId })
.Select(s => new {
Date = s.Key.Date,
ItemId = s.Key.ItemId,
Quantity = s.Sum(m => m.Quantity),
}).ToList()
.GroupBy(r => new { Rarity = ItemsXml.Single(j => j.ItemId == r.ItemId).Rarity, Date = r.Date.GetResolvedDate(grouping) })
.Select(s => new {
Rarity = s.Key.Rarity,
Date = s.Key.Date,
Quantity = s.Sum(w => w.Quantity)
});
var dates = dbData.Select(p => p.Date.GetResolvedDate(grouping)).Distinct().ToArray();
var rarityies = dbData.Select(p => p.Rarity).Distinct().ToArray();
var points = from date in dates
from rarity in rarityies
select new {
Rarity = rarity,
Date = date
};
var matrix = from point in points
join data in dbData on new { point.Rarity, point.Date } equals new { data.Rarity, data.Date } into joineddbData
from subData in joineddbData.DefaultIfEmpty()
select new {
Rarity = point.Rarity,
Date = point.Date,
Qty = subData == null ? 0 : subData.Quantity
};
var itemsQtyDate = matrix
.OrderByDescending(a => a.Rarity)
.ThenBy(d => d.Date)
.GroupBy(b => b.Rarity)
.Select(c => new {
Rarity = c.Key,
Name = rarityDef.Single(p => p.Rarity == c.Key).Name,
Color = System.Drawing.ColorTranslator.FromHtml(rarityDef.Single(p => p.Rarity == c.Key).Color),
Dates = c.Select(o => o.Date.ToString(dateFormat)).ToArray(),
Values = c.Select(o => o.Qty).ToArray()
}
);
PrivateFontCollection pfc = new PrivateFontCollection();
pfc.AddFontFile(Server.MapPath("~/Content/Coda-Regular.ttf"));
using (Chart chart = new Chart()) {
chart.Font.Name = pfc.Families[0].Name;
chart.Font.Size = FontUnit.Point(8);
chart.Width = 1000;
chart.Height = 400;
chart.BackColor = Color.FromArgb(255, 0x27, 0x2B, 0x30);
chart.BorderlineDashStyle = ChartDashStyle.Solid;
chart.BorderlineColor = Color.Gray;
chart.BorderlineWidth = 1;
Legend legend = new Legend();
legend.BackColor = Color.Transparent;
legend.ForeColor = Color.White;
chart.Legends.Add(legend);
chart.Palette = ChartColorPalette.None;
chart.PaletteCustomColors = itemsQtyDate.Select(p => p.Color).ToArray();
using (Font fuente = new Font(pfc.Families[0], 8, GraphicsUnit.Point)) {
ChartArea area = new ChartArea();
area.BackColor = Color.Transparent;
area.ShadowColor = Color.Transparent;
area.BorderColor = Color.FromArgb(255, 0x88, 0x88, 0x88);
area.BorderDashStyle = ChartDashStyle.Solid;
var ejeX = area.AxisX;
ejeX.LabelStyle.Font = fuente;
ejeX.LabelStyle.ForeColor = Color.White;
ejeX.LineColor = Color.FromArgb(255, 0x99, 0x99, 0x99);
ejeX.IsLabelAutoFit = false;
ejeX.IsMarginVisible = true;
ejeX.MajorGrid.LineColor = Color.FromArgb(255, 0x99, 0x99, 0x99);
ejeX.MajorTickMark.LineColor = Color.FromArgb(255, 0xAA, 0xAA, 0xAA);
var interval = Math.Max(dates.Count() / 25, 1);
ejeX.Interval = interval;
ejeX.LabelStyle.Angle = -90;
var ejeY = area.AxisY;
ejeY.LabelStyle.Font = fuente;
ejeY.LabelStyle.ForeColor = Color.White;
ejeY.LineColor = Color.FromArgb(255, 0x99, 0x99, 0x99);
ejeY.IsLabelAutoFit = false;
ejeY.IsMarginVisible = true;
ejeY.MajorGrid.LineColor = Color.FromArgb(255, 0x99, 0x99, 0x99);
ejeY.MajorTickMark.LineColor = Color.FromArgb(255, 0xAA, 0xAA, 0xAA);
chart.ChartAreas.Add(area);
foreach (var item in itemsQtyDate)
{
Series itemsSerie = new Series(item.Name);
itemsSerie.Font = fuente;
itemsSerie.ChartType = type;
itemsSerie.Points.DataBindXY(item.Dates, item.Values);
itemsSerie.IsValueShownAsLabel = grouping != DateGrouping.Day;
chart.Series.Add(itemsSerie);
}
MemoryStream ms = new MemoryStream();
chart.SaveImage(ms, ChartImageFormat.Png);
return(File(ms.ToArray(), "image/png"));
}
}
}
public JsonResult ByItemDate(DateGrouping grouping, int periods)
{
DateTime startDate = new DateTime(2000, 1, 1); // all data
if (periods < 0)
{
periods = 4;
}
if (periods > 0)
{
switch (grouping)
{
case DateGrouping.Year:
startDate = DateTime.Now.Date.GetResolvedDate(DateGrouping.Year).AddYears(1 - periods);
break;
case DateGrouping.Month:
startDate = DateTime.Now.Date.GetResolvedDate(DateGrouping.Month).AddMonths(1 - periods);
break;
case DateGrouping.Week:
startDate = DateTime.Now.Date.GetResolvedDate(DateGrouping.Week).AddDays((1 - periods) * 7);
break;
case DateGrouping.Day:
default:
startDate = DateTime.Now.Date.AddDays(1 - periods);
break;
}
}
string dateFormat = Resources.General.ResourceManager.GetString(string.Format("InterestsByDate_DateFormat_{0}", grouping.ToString()));
var reproduccionesDB = Database.SpawnsCapsulesItems.Where(p => p.SpawnsCapsules.Spawns.UserId == Username && DbFunctions.TruncateTime(p.SpawnsCapsules.Spawns.Date).Value >= startDate)
.GroupBy(r => new { Date = DbFunctions.TruncateTime(r.SpawnsCapsules.Spawns.Date).Value, Code = r.SpawnsCapsules.CapsuleCode, ItemId = r.ItemId })
.Select(s => new {
Code = s.Key.Code,
Date = s.Key.Date,
ItemId = s.Key.ItemId,
Quantity = s.Sum(m => m.Quantity),
}).ToList();
var items = reproduccionesDB
.GroupBy(r => new { Date = r.Date.GetResolvedDate(grouping) })
.Select(s => new DateInfoTotalModel {
Date = s.Key.Date,
FormattedDate = s.Key.Date.ToString(dateFormat),
TotalCapsules = s.Select(h => h.Code).Distinct().Count(),
TotalItems = s.Sum(p => p.Quantity),
RealDays = (int)(s.Max(h => h.Date) - s.Min(i => i.Date)).TotalDays + 1,
Items = s.GroupBy(u => u.ItemId).Select(v => new { ItemId = v.Key, Cantidad = v.Sum(w => w.Quantity) }).ToDictionary(m => m.ItemId, n => n.Cantidad)
}).ToList();
var totals = reproduccionesDB
.GroupBy(q => q.ItemId)
.Select(i => new ItemCapsuleViewModel {
CurrentItem = ItemBase.Create(ItemsXml, i.Key), Quantity = i.Sum(j => j.Quantity)
}).OrderBy(k => k.CurrentItem.Order);
var highests = reproduccionesDB
.GroupBy(r => new { Date = r.Date.GetResolvedDate(grouping), ItemId = r.ItemId })
.Select(s => new { ItemId = s.Key.ItemId, Cantidad = s.Sum(p => p.Quantity) })
.GroupBy(h => h.ItemId)
.Select(i => new { ItemId = i.Key, Maximo = i.Max(j => j.Cantidad) })
.ToDictionary(h => h.ItemId, i => i.Maximo);
var model = new ByDateTotalViewModel();
model.Grouping = grouping;
model.DateInfo = items.OrderByDescending(b => b.Date);
model.Totals = totals;
model.Highests = highests;
model.TotalItems = totals.Sum(p => p.Quantity);
model.Items = model.Totals.Select(p => p.CurrentItem.ItemId).ToArray();
return(Json(model, JsonRequestBehavior.AllowGet));
}
private void GenerateSalesCallsReport(DateTime fromDate, DateTime toDate, DateGrouping dateGrouping, string salesUsrK)
{
Page.Validate("");
if (Page.IsValid)
{
this.DateRangeValueLabel.Text = fromDate.ToString("dd/MM/yy") + " to " + toDate.ToString("dd/MM/yy");
this.DateRangeLabel.Visible = true;
this.DateRangeValueLabel.Visible = true;
int startOfWeek = 1; // Sunday = 0, Monday = 1
fromDate = new DateTime(fromDate.Year, fromDate.Month, fromDate.Day);
toDate = new DateTime(toDate.Year, toDate.Month, toDate.Day);
if (toDate >= fromDate)
{
int numberOfDateGroupings = 0;
if (dateGrouping.Equals(DateGrouping.Daily))
{
toDate = new DateTime(toDate.Year, toDate.Month, toDate.Day).AddDays(1);
numberOfDateGroupings = ((TimeSpan)(toDate - fromDate)).Days;
}
else if (dateGrouping.Equals(DateGrouping.Weekly))
{
fromDate = Utilities.GetStartOfWeek(fromDate);
toDate = Utilities.GetEndOfWeek(toDate);
toDate = new DateTime(toDate.Year, toDate.Month, toDate.Day).AddDays(1);
numberOfDateGroupings = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(((TimeSpan)(toDate - fromDate)).Days) / 7d));
}
else if (dateGrouping.Equals(DateGrouping.Monthly))
{
fromDate = Utilities.GetStartOfMonth(fromDate);
toDate = Utilities.GetEndOfMonth(toDate);
toDate = new DateTime(toDate.Year, toDate.Month, toDate.Day).AddDays(1);
numberOfDateGroupings = (toDate.AddDays(-1).Year - fromDate.Year) * 12 + (toDate.AddDays(-1).Month - fromDate.Month) + 1;
}
List<UsrDataHolder> selectedSalesUsrs = SalesUsrs;
if (salesUsrK != "0")
{
selectedSalesUsrs = new List<UsrDataHolder>();
if (salesUsrK.IndexOf("team") == 0)
{
UsrSet salesTeam = Usr.GetCurrentSalesUsrsNameAndK(Convert.ToInt32(salesUsrK.Replace("team", "")));
foreach (Usr salesUsr in salesTeam)
{
selectedSalesUsrs.Add(new UsrDataHolder(salesUsr));
}
}
else
{
selectedSalesUsrs.Add(SalesUsrs[GetSalesPersonIndexNumber(SalesUsrs, Convert.ToInt32(salesUsrK))]);
}
}
if (selectedSalesUsrs.Count > 0)
{
Q salesUsrsQueryCondition = new Q(SalesCall.Columns.UsrK, selectedSalesUsrs[0].K);
Q invoiceSalesUsrsQueryCondition = new Q(Invoice.Columns.SalesUsrK, selectedSalesUsrs[0].K);
Q promoterAddedByQueryCondition = new Q(Promoter.Columns.AddedByUsrK, selectedSalesUsrs[0].K);
for (int i = 1; i < selectedSalesUsrs.Count; i++)
{
promoterAddedByQueryCondition = new Or(promoterAddedByQueryCondition,
new Q(Promoter.Columns.AddedByUsrK, selectedSalesUsrs[i].K));
salesUsrsQueryCondition = new Or(salesUsrsQueryCondition,
new Q(SalesCall.Columns.UsrK, selectedSalesUsrs[i].K));
invoiceSalesUsrsQueryCondition = new Or(invoiceSalesUsrsQueryCondition,
new Q(Invoice.Columns.SalesUsrK, selectedSalesUsrs[i].K));
}
Q salesCallDateRangeQueryCondition = new And(new Q(SalesCall.Columns.IsCall, true),
new Q(SalesCall.Columns.DateTimeStart, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(SalesCall.Columns.DateTimeStart, QueryOperator.LessThan, toDate));
Q promoterDateRangeQueryCondition = new And(
new Q(Promoter.Columns.AddedMethod, Promoter.AddedMedhods.SalesUser),
new Q(Promoter.Columns.DateTimeSignUp, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(Promoter.Columns.DateTimeSignUp, QueryOperator.LessThan, toDate));
#region Sales Calls: Total Money
Query totalSalesAmountQuery = new Query(new And(invoiceSalesUsrsQueryCondition,
new Q(Invoice.Columns.SalesUsrAmount, QueryOperator.NotEqualTo, 0),
new Or(new And(new Q(Invoice.Columns.Type, Invoice.Types.Invoice),
new Q(Invoice.Columns.Paid, true),
new Q(Invoice.Columns.PaidDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(Invoice.Columns.PaidDateTime, QueryOperator.LessThan, toDate),
new Q(Usr.Columns.SalesTeam, QueryOperator.NotEqualTo, Usr.SalesTeams.CorporateSalesTeam)),
new And(new Q(Invoice.Columns.Type, Invoice.Types.Invoice),
new Q(Invoice.Columns.TaxDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(Invoice.Columns.TaxDateTime, QueryOperator.LessThan, toDate),
new Q(Usr.Columns.SalesTeam, Usr.SalesTeams.CorporateSalesTeam)),
new And(new Q(Invoice.Columns.Type, Invoice.Types.Credit),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.GreaterThanOrEqualTo, fromDate),
new Q(Invoice.Columns.CreatedDateTime, QueryOperator.LessThan, toDate)))));
totalSalesAmountQuery.TableElement = new Join(Invoice.Columns.SalesUsrK, Usr.Columns.K);
totalSalesAmountQuery.ExtraSelectElements.Add("TotalSales", "SUM(SalesUsrAmount)");
totalSalesAmountQuery.Columns = new ColumnSet(Invoice.Columns.SalesUsrK);
if (dateGrouping.Equals(DateGrouping.Daily))
{
totalSalesAmountQuery.ExtraSelectElements.Add("Date", "CONVERT(datetime,CONVERT(varchar(2),DAY(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))) + '/' + CONVERT(varchar(2),MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))) + '/' + CONVERT(varchar(4),Year(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))))");
totalSalesAmountQuery.OrderBy = new OrderBy("[Usr].[FirstName], YEAR(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) desc, MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) desc, DAY(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) desc");
totalSalesAmountQuery.GroupBy = new GroupBy("[Usr].[FirstName], DAY(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)), MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)), YEAR(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)), [Invoice].[SalesUsrK]");
}
else if (dateGrouping.Equals(DateGrouping.Weekly))
{
totalSalesAmountQuery.ExtraSelectElements.Add("Date", "CONVERT(dateTime,(CONVERT(varchar(2),DAY(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(2),MONTH(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(4),YEAR(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))))))");
totalSalesAmountQuery.OrderBy = new OrderBy("[Usr].[FirstName], CONVERT(dateTime,(CONVERT(varchar(2),DAY(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(2),MONTH(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(4),YEAR(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))))) desc");
totalSalesAmountQuery.GroupBy = new GroupBy("[Usr].[FirstName], CONVERT(dateTime,(CONVERT(varchar(2),DAY(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(2),MONTH(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))) + '/' + CONVERT(varchar(4),YEAR(DateAdd(day, -1 * datepart(dw, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) + 1, IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)))))), [Invoice].[SalesUsrK]");
}
else if (dateGrouping.Equals(DateGrouping.Monthly))
{
totalSalesAmountQuery.ExtraSelectElements.Add("Date", "CONVERT(datetime,'1/' + CONVERT(varchar(2),MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))) + '/' + CONVERT(varchar(4),Year(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime))))");
totalSalesAmountQuery.OrderBy = new OrderBy("[Usr].[FirstName], YEAR(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) desc, MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)) desc");
totalSalesAmountQuery.GroupBy = new GroupBy("[Usr].[FirstName], MONTH(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)), YEAR(IsNULL(CASE WHEN Usr.SalesTeam = " + Convert.ToInt32(Usr.SalesTeams.CorporateSalesTeam).ToString() + " THEN TaxDateTime ELSE PaidDateTime END, CreatedDateTime)), [Invoice].[SalesUsrK]");
}
InvoiceSet salesInvoices = new InvoiceSet(totalSalesAmountQuery);
#endregion
#region Sales Calls: SalesUsrK, Minutes, and Total calls
Query salesCallMinutesQuery = new Query(new And(salesCallDateRangeQueryCondition,
salesUsrsQueryCondition));
salesCallMinutesQuery.ExtraSelectElements.Add("Minutes", "SUM([SalesCall].[Duration])");
salesCallMinutesQuery = PopulateSalesCallQuery(salesCallMinutesQuery, dateGrouping, startOfWeek);
SalesCallSet salesCallsMinutes = new SalesCallSet(salesCallMinutesQuery);
#endregion
#region Promoters: New leads
Query promotersNewLeadsQuery = new Query(new And(promoterDateRangeQueryCondition,
promoterAddedByQueryCondition));
promotersNewLeadsQuery = PopulatePromotersQuery(promotersNewLeadsQuery, dateGrouping, startOfWeek);
PromoterSet promotersNewLeads = new PromoterSet(promotersNewLeadsQuery);
#endregion
#region Sales Calls: New leads
Query salesCallNewLeadsQuery = new Query(new And(salesCallDateRangeQueryCondition,
salesUsrsQueryCondition,
new Q(SalesCall.Columns.IsCallToNewLead, true)));
salesCallNewLeadsQuery = PopulateSalesCallQuery(salesCallNewLeadsQuery, dateGrouping, startOfWeek);
SalesCallSet salesCallsNewLeads = new SalesCallSet(salesCallNewLeadsQuery);
#endregion
#region Sales Calls: Effective
Query salesCallEffectiveQuery = new Query(new And(salesCallDateRangeQueryCondition,
salesUsrsQueryCondition,
new Q(SalesCall.Columns.Effective, true)));
salesCallEffectiveQuery = PopulateSalesCallQuery(salesCallEffectiveQuery, dateGrouping, startOfWeek);
SalesCallSet salesCallsEffective = new SalesCallSet(salesCallEffectiveQuery);
#endregion
#region Sales Calls: Cold
Query salesCallColdQuery = new Query(new And(salesCallDateRangeQueryCondition,
salesUsrsQueryCondition,
new Q(SalesCall.Columns.Type, SalesCall.Types.Cold)));
salesCallColdQuery = PopulateSalesCallQuery(salesCallColdQuery, dateGrouping, startOfWeek);
SalesCallSet salesCallsCold = new SalesCallSet(salesCallColdQuery);
#endregion
#region Sales Calls: Followup
Query salesCallFollowupQuery = new Query(new And(salesCallDateRangeQueryCondition,
salesUsrsQueryCondition,
new Q(SalesCall.Columns.Type, SalesCall.Types.ProactiveFollowUp)));
salesCallFollowupQuery = PopulateSalesCallQuery(salesCallFollowupQuery, dateGrouping, startOfWeek);
SalesCallSet salesCallsFollowup = new SalesCallSet(salesCallFollowupQuery);
#endregion
#region Sales Calls: Active
Query salesCallActiveQuery = new Query(new And(salesCallDateRangeQueryCondition,
salesUsrsQueryCondition,
new Q(SalesCall.Columns.Type, SalesCall.Types.Active)));
salesCallActiveQuery = PopulateSalesCallQuery(salesCallActiveQuery, dateGrouping, startOfWeek);
SalesCallSet salesCallsActive = new SalesCallSet(salesCallActiveQuery);
#endregion
#region Table
this.SalesStatsResultsTable.Visible = false;
this.SalesCallsResultsTable.Visible = true;
HtmlTable salesCallsTable = this.SalesCallsResultsTable;
salesCallsTable.Rows.Clear();
#endregion
#region Header Rows
HtmlTableRow headerRow1 = new HtmlTableRow();
headerRow1.Attributes.Add("class", "dataGrid1stHeader");
salesCallsTable.Rows.Add(headerRow1);
HtmlTableCell[] header1TableCells = new HtmlTableCell[6];
header1TableCells[0] = new HtmlTableCell("td");
header1TableCells[0].RowSpan = 2;
header1TableCells[0].InnerHtml = "Date";
for (int i = 1; i < header1TableCells.Length; i++)
{
header1TableCells[i] = new HtmlTableCell("th");
header1TableCells[i].ColSpan = selectedSalesUsrs.Count;
header1TableCells[i].Align = "center";
header1TableCells[i].Attributes.Add("class", "dataGridColumnDivider");
}
header1TableCells[1].InnerHtml = "Total sales";
header1TableCells[2].InnerHtml = "<b>Total calls</b>";
header1TableCells[3].InnerHtml = "Minutes per call";
header1TableCells[4].InnerHtml = "<b>New leads</b>";
header1TableCells[5].InnerHtml = "Calls to new leads";
//header1TableCells[4].InnerHtml = "<nobr>Effective calls</nobr>";
//header1TableCells[7].InnerHtml = "<nobr>Cold (%)</nobr>";
//header1TableCells[8].InnerHtml = "<nobr>Followup (%)</nobr>";
//header1TableCells[9].InnerHtml = "<nobr>Active (%)</nobr>";
foreach (HtmlTableCell tc in header1TableCells)
headerRow1.Cells.Add(tc);
HtmlTableRow headerRow2 = new HtmlTableRow();
headerRow2.Attributes.Add("class", "dataGrid2ndHeader");
salesCallsTable.Rows.Add(headerRow2);
HtmlTableCell[] header2TableCells = new HtmlTableCell[selectedSalesUsrs.Count * (header1TableCells.Length - 1)];
for (int i = 0; i < header2TableCells.Length; i++)
{
header2TableCells[i] = new HtmlTableCell("th");
header2TableCells[i].InnerHtml = "<b>" + selectedSalesUsrs[i % selectedSalesUsrs.Count].FirstName + "</b>";
header2TableCells[i].Align = "right";
header2TableCells[i].Width = "10";
if (i % selectedSalesUsrs.Count == 0)
{
header2TableCells[i].Attributes.Add("class", "dataGridColumnDivider");
}
headerRow2.Cells.Add(header2TableCells[i]);
}
#endregion
#region Data Rows
HtmlTableCell[,] dataTableCells = new HtmlTableCell[numberOfDateGroupings, selectedSalesUsrs.Count * (header1TableCells.Length - 1) + 1];
for (int i = 0; i < numberOfDateGroupings; i++)
{
dataTableCells[i, 0] = new HtmlTableCell();
dataTableCells[i, 0].InnerHtml = "<nobr>";
if (dateGrouping.Equals(DateGrouping.Daily))
dataTableCells[i, 0].InnerHtml += String.Format("{0:ddd' 'd' 'MMM}", toDate.AddDays(-1 * (i + 1)));
else if (dateGrouping.Equals(DateGrouping.Weekly))
{
dataTableCells[i, 0].InnerHtml += toDate.AddDays(-7 * (i + 1)).ToString("dd/MM/yy") + " - " + toDate.AddDays(-7 * (i + 1) + 6).ToString("dd/MM/yy");
}
else if (dateGrouping.Equals(DateGrouping.Monthly))
{
dataTableCells[i, 0].InnerHtml += String.Format("{0:MMM' 'yy}", toDate.AddMonths(-1 * (i + 1)));
}
dataTableCells[i, 0].InnerHtml += "</nobr>";
}
foreach (Invoice salesInvoice in salesInvoices)
{
try
{
int i = GetSalesPersonIndexNumber(selectedSalesUsrs, salesInvoice.SalesUsrK);
int dateGroupingsFromTop = 0;
if (dateGrouping.Equals(DateGrouping.Daily))
dateGroupingsFromTop = ((TimeSpan)(toDate - ((DateTime)salesInvoice.ExtraSelectElements["Date"]))).Days;
else if (dateGrouping.Equals(DateGrouping.Weekly))
dateGroupingsFromTop = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(((TimeSpan)(toDate - ((DateTime)salesInvoice.ExtraSelectElements["Date"]))).Days) / 7d));
else if (dateGrouping.Equals(DateGrouping.Monthly))
{
dateGroupingsFromTop = (toDate.AddDays(-1).Year - ((DateTime)salesInvoice.ExtraSelectElements["Date"]).Year) * 12 + (toDate.AddDays(-1).Month - ((DateTime)salesInvoice.ExtraSelectElements["Date"]).Month) + 1;
}
dataTableCells[dateGroupingsFromTop - 1, i + 1] = new HtmlTableCell();
private void GenerateSalesCallsReport(DateTime fromDate, DateTime toDate, DateGrouping dateGrouping)
{
// salesUsrK of 0 means all sales usrs
GenerateSalesCallsReport(fromDate, toDate, dateGrouping, "0");
}
private void GetDatesAndRunSalesCallsReport(DateGrouping dateGrouping)
{
DateTime fromDate = FromDateCal.Date;
DateTime toDate = ToDateCal.Date;
// If dates arent overridden, use defaults
if (!this.OverrideDateCheckBox.Checked)
{
if (dateGrouping.Equals(DateGrouping.Daily))
{
// Default is today + 6 days previous = 7 days
fromDate = DateTime.Now.AddDays(-6);
toDate = DateTime.Now;
}
else if (dateGrouping.Equals(DateGrouping.Weekly))
{
// Default is this week + 5 weeks previous = 6 weeks
fromDate = Utilities.GetStartOfWeek(DateTime.Now).AddDays(-35);
toDate = Utilities.GetEndOfWeek(DateTime.Now);
}
else if (dateGrouping.Equals(DateGrouping.Monthly))
{
// Default is this month + 5 months previous = 6 months
fromDate = Utilities.GetStartOfMonth(DateTime.Now).AddMonths(-5);
toDate = Utilities.GetEndOfMonth(DateTime.Now);
}
FromDateCal.Date = fromDate;
ToDateCal.Date = toDate;
}
if (fromDate > DateTime.MinValue && toDate > DateTime.MinValue)
{
GenerateSalesCallsReport(fromDate, toDate, dateGrouping, this.SalesPersonsDropDownList.SelectedValue);
}
}
public async Task<IEnumerable<AggregatedData>> GetAggregatedData(string table, int deviceId, DateTime now, DateGrouping groupType = DateGrouping.Hourly)
{
var result = new List<AggregatedData>();
string query, minDate, maxDate;
var aggregator = table == "EnergyUsages" ? "SUM" : "AVG";
switch(groupType)
{
case DateGrouping.Monthly:
query = string.Format(_monthlyQuery, aggregator, table);
minDate = (new DateTime(now.Year, now.Month, 1)).AddYears(-1).ToString("yyyy-MM-dd");
maxDate = (new DateTime(now.Year, now.Month, 1)).ToString("yyyy-MM-dd");
break;
case DateGrouping.Daily:
query = string.Format(_dailyQuery, aggregator, table);
minDate = (new DateTime(now.Year, now.Month, now.Day)).AddMonths(-1).ToString("yyyy-MM-dd");
maxDate = (new DateTime(now.Year, now.Month, now.Day)).ToString("yyyy-MM-dd");
break;
default:
query = string.Format(_hourlyQuery, aggregator, table);
minDate = (new DateTime(now.Year, now.Month, now.Day, now.Hour, 0, 0)).AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss");
maxDate = (new DateTime(now.Year, now.Month, now.Day, now.Hour, 0, 0)).ToString("yyyy-MM-dd HH:mm:ss");
break;
}
await _connection.OpenAsync();
using (var cmd = _connection.CreateCommand())
{
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@devId", deviceId);
cmd.Parameters.AddWithValue("@mindate", minDate);
cmd.Parameters.AddWithValue("@maxdate", maxDate);
using (var reader = await cmd.ExecuteReaderAsync())
{
if (reader != null)
{
while (await reader.ReadAsync())
{
var datapoint = new AggregatedData();
datapoint.NumSamples = (long)reader["numSamples"];
datapoint.AvgValue = (double)reader["average"];
datapoint.DeviceId = deviceId;
datapoint.GroupedType = groupType;
datapoint.Type = table;
switch (groupType)
{
case DateGrouping.Monthly:
datapoint.GroupedDate = new DateTime((int)reader["year"], (int)reader["month"], 1);
break;
case DateGrouping.Daily:
datapoint.GroupedDate = (DateTime)reader["date"];
break;
default:
datapoint.GroupedDate = ((DateTime)reader["date"]).AddHours((int)reader["hour"]);
break;
}
result.Add(datapoint);
}
reader.Close();
}
}
}
await _connection.CloseAsync();
return result;
}
private Query PopulateSalesCallQuery(Query query, DateGrouping dateGrouping, int startOfWeek)
{
// startOfWeek: Sunday = 0, Monday = 1
query.TableElement = new Join(SalesCall.Columns.UsrK, Usr.Columns.K);
query.Columns = new ColumnSet(SalesCall.Columns.UsrK);
//salesCallActiveQuery.OrderBy = new OrderBy("[Usr].[FirstName], YEAR([SalesCall].[DateTimeStart]) desc, MONTH([SalesCall].[DateTimeStart]) desc, DAY([SalesCall].[DateTimeStart]) desc");
//salesCallActiveQuery.GroupBy = new GroupBy("[Usr].[FirstName], DAY([SalesCall].[DateTimeStart]), MONTH([SalesCall].[DateTimeStart]), YEAR([SalesCall].[DateTimeStart]), [SalesCall].[UsrK], DATENAME(dw, [SalesCall].[DateTimeStart])");
query.ExtraSelectElements.Add("TotalCalls", "COUNT(*)");
if (dateGrouping.Equals(DateGrouping.Daily))
{
query.ExtraSelectElements.Add("Date", "CONVERT(datetime,CONVERT(varchar(2),DAY([SalesCall].[DateTimeStart])) + '/' + CONVERT(varchar(2),MONTH([SalesCall].[DateTimeStart])) + '/' + CONVERT(varchar(4),Year([SalesCall].[DateTimeStart])))");
query.OrderBy = new OrderBy("[Usr].[FirstName], YEAR([SalesCall].[DateTimeStart]) desc, MONTH([SalesCall].[DateTimeStart]) desc, DAY([SalesCall].[DateTimeStart]) desc");
query.GroupBy = new GroupBy("[Usr].[FirstName], DAY([SalesCall].[DateTimeStart]), MONTH([SalesCall].[DateTimeStart]), YEAR([SalesCall].[DateTimeStart]), [SalesCall].[UsrK]");
}
else if (dateGrouping.Equals(DateGrouping.Weekly))
{
query.ExtraSelectElements.Add("Date", "CONVERT(dateTime,(CONVERT(varchar(2),DAY(DateAdd(day, -1 * datepart(dw, [SalesCall].[DateTimeStart]) + " + startOfWeek.ToString() + ", [SalesCall].[DateTimeStart]))) + '/' + CONVERT(varchar(2),MONTH(DateAdd(day, -1 * datepart(dw, [SalesCall].[DateTimeStart]) + " + startOfWeek.ToString() + ", [SalesCall].[DateTimeStart]))) + '/' + CONVERT(varchar(4),YEAR(DateAdd(day, -1 * datepart(dw, [SalesCall].[DateTimeStart]) + " + startOfWeek.ToString() + ", [SalesCall].[DateTimeStart])))))");
query.OrderBy = new OrderBy("[Usr].[FirstName], CONVERT(dateTime,(CONVERT(varchar(2),DAY(DateAdd(day, -1 * datepart(dw, [SalesCall].[DateTimeStart]) + " + startOfWeek.ToString() + ", [SalesCall].[DateTimeStart]))) + '/' + CONVERT(varchar(2),MONTH(DateAdd(day, -1 * datepart(dw, [SalesCall].[DateTimeStart]) + " + startOfWeek.ToString() + ", [SalesCall].[DateTimeStart]))) + '/' + CONVERT(varchar(4),YEAR(DateAdd(day, -1 * datepart(dw, [SalesCall].[DateTimeStart]) + " + startOfWeek.ToString() + ", [SalesCall].[DateTimeStart]))))) desc");
query.GroupBy = new GroupBy("[Usr].[FirstName], CONVERT(dateTime,(CONVERT(varchar(2),DAY(DateAdd(day, -1 * datepart(dw, [SalesCall].[DateTimeStart]) + " + startOfWeek.ToString() + ", [SalesCall].[DateTimeStart]))) + '/' + CONVERT(varchar(2),MONTH(DateAdd(day, -1 * datepart(dw, [SalesCall].[DateTimeStart]) + " + startOfWeek.ToString() + ", [SalesCall].[DateTimeStart]))) + '/' + CONVERT(varchar(4),YEAR(DateAdd(day, -1 * datepart(dw, [SalesCall].[DateTimeStart]) + " + startOfWeek.ToString() + ", [SalesCall].[DateTimeStart]))))), [SalesCall].[UsrK]");
}
else if (dateGrouping.Equals(DateGrouping.Monthly))
{
query.ExtraSelectElements.Add("Date", "CONVERT(datetime,'1/' + CONVERT(varchar(2),MONTH([SalesCall].[DateTimeStart])) + '/' + CONVERT(varchar(4),Year([SalesCall].[DateTimeStart])))");
query.OrderBy = new OrderBy("[Usr].[FirstName], YEAR([SalesCall].[DateTimeStart]) desc, MONTH([SalesCall].[DateTimeStart]) desc");
query.GroupBy = new GroupBy("[Usr].[FirstName], MONTH([SalesCall].[DateTimeStart]), YEAR([SalesCall].[DateTimeStart]), [SalesCall].[UsrK]");
}
return query;
}
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)
{ }
}
}
