public ActionResult Index()
{
// Get the data
var helper = new SqlHelper();
var dataset = helper.GetSet(string.Format(@"
DECLARE @c_StartDate AS DATETIME = convert(DATETIME, '{0}');
DECLARE @c_EndDate AS DATETIME = convert(DATETIME, '{1}');
-- Earnings
SELECT c.CommissionRunID
, cr.CommissionRunDescription
, cr.AcceptedDate
, cr.PeriodTypeID
, pr.PaidRankID
, PaidRankDescription = r.RankDescription
, Total = sum(c.Total)
, Earnings = sum(c.Earnings)
, PaidAsRankCount = count(pr.PaidRankID)
FROM
Commissions c
INNER JOIN CommissionRuns cr
ON cr.CommissionRunID = c.CommissionRunID
INNER JOIN PeriodVolumes pr
ON pr.CustomerID = c.CustomerID
AND pr.PeriodID = cr.PeriodID
AND pr.PeriodTypeID = cr.PeriodTypeID
INNER JOIN Ranks r
ON pr.PaidRankID = r.RankID
WHERE
cr.CommissionRunStatusID IN (3)
AND cr.AcceptedDate >= @c_StartDate
AND cr.AcceptedDate < @c_EndDate
GROUP BY
c.CommissionRunID
, cr.CommissionRunDescription
, cr.AcceptedDate
, cr.PeriodTypeID
, pr.PaidRankID
, r.RankDescription
ORDER BY
c.CommissionRunID ASC
, cr.PeriodTypeID ASC
, pr.PaidRankID ASC
-- Bonuses
SELECT cb.CommissionRunID
, cr.CommissionRunDescription
, cr.AcceptedDate
, cr.PeriodTypeID
, cb.BonusID
, b.BonusDescription
, BonusAmount = sum(cb.Amount)
FROM
CommissionBonuses cb
INNER JOIN CommissionRuns cr
ON cr.CommissionRunID = cb.CommissionRunID
INNER JOIN Bonuses b
ON b.BonusID = cb.BonusID AND b.PeriodTypeID = cr.PeriodTypeID
WHERE
cr.CommissionRunStatusID IN (3)
AND cr.AcceptedDate >= @c_StartDate
AND cr.AcceptedDate < @c_EndDate
GROUP BY
cb.CommissionRunID
, cr.CommissionRunDescription
, cr.AcceptedDate
, cr.PeriodTypeID
, cb.BonusID
, b.BonusDescription
ORDER BY
cb.CommissionRunID ASC
, cr.PeriodTypeID ASC
, cb.BonusID ASC
", new DateTime(DateTime.Now.AddYears(-1).Year, DateTime.Now.AddYears(-1).Month, DateTime.Now.AddYears(-1).Day).ToShortDateString(),
new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day).ToShortDateString()));
var model = new CommissionSummaryViewModel(dataset);
if (Request.IsAjaxRequest()) return PartialView(model);
else return View(model);
}
public ActionResult Index()
{
var model = new ProjectedAutoOrdersViewModel();
var helper = new SqlHelper();
var dataset = helper.GetSet(string.Format(@"
DECLARE @pa_StartDate AS DATETIME = convert(DATETIME, '{0}');
DECLARE @pa_EndDate AS DATETIME = convert(DATETIME, '{1}');
SELECT ""Month"" = month(aos.ScheduledDate)
, ""Day"" = day(aos.ScheduledDate)
, ""Year"" = year(aos.ScheduledDate)
, Total = sum(ao.SubTotal)
FROM
AutoOrders ao
INNER JOIN AutoOrderSchedules aos
ON aos.AutoOrderID = ao.AutoOrderID
WHERE
ao.AutoOrderStatusID = 0
AND aos.ScheduledDate >= @pa_StartDate
AND aos.ScheduledDate < @pa_EndDate
GROUP BY
month(aos.ScheduledDate)
, day(aos.ScheduledDate)
, year(aos.ScheduledDate)
ORDER BY
year(aos.ScheduledDate) ASC
, month(aos.ScheduledDate) ASC
, day(aos.ScheduledDate) ASC
", new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day).ToShortDateString(),
new DateTime(DateTime.Now.AddMonths(1).Year, DateTime.Now.AddMonths(1).Month, DateTime.Now.AddMonths(1).Day).ToShortDateString()));
// Divide our dataset into separate tables for easy reading
var projectedAutoOrdersData = dataset.Tables[0];
// Populate our details collection
var details = new List<ProjectedAutoOrderDetail>();
foreach (DataRow row in projectedAutoOrdersData.Rows)
{
var detail = new ProjectedAutoOrderDetail();
detail.Month = Convert.ToInt32(row["Month"]);
detail.Day = Convert.ToInt32(row["Day"]);
detail.Year = Convert.ToInt32(row["Year"]);
detail.Total = Convert.ToDecimal(row["Total"]);
details.Add(detail);
}
model.Details = details;
// Start filling out our view model
var highesttotaldetail = model.Details.OrderByDescending(c => c.Total).FirstOrDefault();
var lowesttotaldetail = model.Details.OrderBy(c => c.Total).FirstOrDefault();
if (highesttotaldetail != null)
{
model.HighestTotal = highesttotaldetail.Total;
model.HighestTotalDate = highesttotaldetail.Date;
}
if (lowesttotaldetail != null)
{
model.LowestTotal = lowesttotaldetail.Total;
model.LowestTotalDate = lowesttotaldetail.Date;
}
if (model.Details.Count() > 0)
{
model.AverageTotal = model.Details.Average(c => c.Total);
}
var detailsByWeekDay = (from d in model.Details
group d by d.WeekDay into g
select new
{
WeekDay = g.Key,
Total = g.Sum(a => a.Total)
});
if (detailsByWeekDay.Count() > 0)
{
model.HighestTotalDayOfWeek = detailsByWeekDay.OrderByDescending(c => c.Total).First().WeekDay;
model.LowestTotalDayOfWeek = detailsByWeekDay.OrderBy(c => c.Total).First().WeekDay;
}
// Charts
var dataSource = new SingleSeriesChartXMLDataSource();
// Customize our chart
dataSource.yAxisName = "Projected Revenue";
dataSource.ShowValues = false;
// Add our sets
foreach (DataRow row in projectedAutoOrdersData.Rows)
{
var month = Convert.ToInt32(row["Month"]);
var day = Convert.ToInt32(row["Day"]);
var year = Convert.ToInt32(row["Year"]);
var value = Convert.ToDecimal(row["Total"]);
var label = string.Format("{0} {1}/{2}", new DateTime(year, month, day).ToString("dddd").Substring(0, 3), month, day);
var set = new ChartDataSetDetail();
set.Label = label;
set.Value = value;
set.ToolHelp = label;
dataSource.Sets.Add(set);
}
// Add some trend lines to the highest and lowest sets
var highestValueSet = dataSource.Sets.OrderByDescending(c => c.Value).FirstOrDefault();
if (highestValueSet != null)
{
highestValueSet.VerticalTrendLine = new VerticalTrendLine()
{
Label = "▲",
LineColor = "00CC00",
LabelVerticalAlignment = "top",
LinePosition = 0M,
LabelPosition = 0.999M,
LineThickness = 2,
ShowLabelBorder = false
};
}
var lowestValueSet = dataSource.Sets.OrderBy(c => c.Value).FirstOrDefault();
if (lowestValueSet != null)
{
lowestValueSet.VerticalTrendLine = new VerticalTrendLine()
{
Label = "▼",
LineColor = "FF0000",
LabelVerticalAlignment = "bottom",
LinePosition = 0M,
LabelPosition = 0.005M,
LineThickness = 2,
ShowLabelBorder = false
};
}
// Add our average trend line
if (dataSource.Sets.Count > 0)
{
var meanValue = dataSource.Sets.Average(c => Convert.ToDecimal(c.Value));
dataSource.HorizontalTrendLines.Add(new HorizontalTrendLine()
{
StartValue = meanValue,
DisplayValue = "Average",
ToolText = string.Format("The average daily auto-order revenue ({0:C})", meanValue),
LineColor = "006699"
});
}
// Return the formatted XML
model.ProjectedAutoOrdersChartXml = dataSource.ToString();
if (Request.IsAjaxRequest()) return PartialView(model);
else return View(model);
}