void LoadAnalysisData() {
spreadsheetControl.Document.BeginUpdate();
var salesReportWorksheet = spreadsheetControl.Document.Worksheets["Sales Report"];
var salesReportItems = ViewModel.GetSalesReport().ToList(); // materialize
var frCustomers = salesReportItems
.Select(i => i.CustomerName)
.Distinct()
.OrderBy(i => i).ToList();
salesReportWorksheet.Import(frCustomers, 14, 1, true);
foreach(var reportItem in salesReportItems) {
int rowOffset = frCustomers.IndexOf(reportItem.CustomerName);
int columnOffset = AnalysisPeriod.MonthOffsetFromStart(reportItem.Date) / 12;
if(rowOffset < 0 || columnOffset < 0) continue;
salesReportWorksheet.Cells[14 + rowOffset, 3 + columnOffset * 2].SetValue(reportItem.Total);
}
var salesDataWorksheet = spreadsheetControl.Document.Worksheets["Sales Data"];
var salesDataItems = ViewModel.GetSalesData().ToList(); // materialize
var states = salesDataItems.Select(i => i.State).Distinct().OrderBy(i => i).ToList();
salesDataWorksheet.Import(ViewModel.GetStates(states), 5, 3, false);
foreach(var dataItem in salesDataItems) {
int rowOffset = AnalysisPeriod.MonthOffsetFromStart(dataItem.Date);
int columnOffset = states.IndexOf(dataItem.State);
if(rowOffset < 0 || columnOffset < 0) continue;
salesDataWorksheet.Cells[6 + rowOffset, 3 + columnOffset].SetValue(dataItem.Total);
}
spreadsheetControl.Document.Worksheets.ActiveWorksheet = salesReportWorksheet;
spreadsheetControl.Document.EndUpdate();
}
void LoadData()
{
CustomerAnalysisViewModel ViewModel = (CustomerAnalysisViewModel)this.DataContext;
spreadsheetControl.Document.BeginUpdate();
var salesReportWorksheet = spreadsheetControl.Document.Worksheets["Sales Report"];
var salesReportItems = ViewModel.GetSalesReport().ToList(); // materialize
var frCustomers = salesReportItems
.Select(i => i.CustomerName)
.Distinct()
.OrderBy(i => i).ToList();
salesReportWorksheet.Import(frCustomers, 14, 1, true);
var startReportsDate = salesReportItems.Min(x => x.Date);
foreach (CustomersAnalysis.Item item in salesReportItems)
{
int rowOffset = frCustomers.IndexOf(item.CustomerName);
int columnOffset = (int)(AnalysisPeriod.GetMonthOffsetFromStart(item.Date, startReportsDate) / 12);
if (rowOffset < 0 || columnOffset < 0)
{
continue;
}
salesReportWorksheet.Cells[14 + rowOffset, 3 + columnOffset * 2].SetValue(item.Total);
}
var salesDataWorksheet = spreadsheetControl.Document.Worksheets["Sales Data"];
var salesDataItems = ViewModel.GetSalesData().ToList(); // materialize
var states = salesDataItems.Select(i => i.State).Distinct().OrderBy(i => i).ToList();
salesDataWorksheet.Import(ViewModel.GetStates(states), 5, 3, false);
foreach (CustomersAnalysis.Item item in salesDataItems)
{
int rowOffset = AnalysisPeriod.GetMonthOffsetFromStart(item.Date, startReportsDate) - 1;
int columnOffset = states.IndexOf(item.State);
if (rowOffset < 0 || columnOffset < 0)
{
continue;
}
salesDataWorksheet.Cells[6 + rowOffset, 3 + columnOffset].SetValue(item.Total);
}
spreadsheetControl.Document.Worksheets.ActiveWorksheet = salesReportWorksheet;
spreadsheetControl.Document.EndUpdate();
}
void LoadData()
{
ProductAnalysisViewModel ViewModel = (ProductAnalysisViewModel)this.DataContext;
spreadsheetControl.Document.BeginUpdate();
var financialReportWorksheet = spreadsheetControl.Document.Worksheets["Financial Report"];
var financialReportItems = ViewModel.GetFinancialReport().ToList(); // materialize
var frProducts = financialReportItems
.Select(i => i.ProductName)
.Distinct()
.OrderBy(i => i).ToList();
financialReportWorksheet.Import(frProducts, 17, 1, true);
var startReportsDate = financialReportItems.Min(x => x.Date);
foreach (ProductsAnalysis.Item item in financialReportItems)
{
int rowOffset = frProducts.IndexOf(item.ProductName);
int columnOffset = (int)(AnalysisPeriod.GetMonthOffsetFromStart(item.Date, startReportsDate) / 12);
if (rowOffset < 0 || columnOffset < 0)
{
continue;
}
financialReportWorksheet.Cells[17 + rowOffset, 3 + columnOffset * 2].SetValue(item.Total);
}
var financialDataWorksheet = spreadsheetControl.Document.Worksheets["Financial Data"];
var financialDataItems = ViewModel.GetFinancialData().ToList(); // materialize
foreach (ProductsAnalysis.Item item in financialDataItems)
{
int rowOffset = AnalysisPeriod.GetMonthOffsetFromStart(item.Date, startReportsDate) - 1;
int columnOffset = GetColumnIndex(item.ProductCategory);
if (rowOffset < 0 || columnOffset < 0)
{
continue;
}
financialDataWorksheet.Cells[6 + rowOffset, 3 + columnOffset].SetValue(item.Total);
}
spreadsheetControl.Document.Worksheets.ActiveWorksheet = financialReportWorksheet;
spreadsheetControl.Document.EndUpdate();
}
/***************************************************/
/**** Public Methods ****/
/***************************************************/
public static AnalysisPeriod AnalysisPeriod(SpaceTime spaceTimeStart, SpaceTime spaceTimeEnd)
{
//DateTime startDateTime = Convert.ToDateTime(spaceTimeStart);
//DateTime endDateTime = Convert.ToDateTime(spaceTimeEnd);
DateTime startDateTime = new DateTime(spaceTimeStart.Year, spaceTimeStart.Month, spaceTimeStart.Day, spaceTimeStart.Hour, spaceTimeStart.Minute, spaceTimeStart.Second, spaceTimeStart.Millisecond);
DateTime endDateTime = new DateTime(spaceTimeEnd.Year, spaceTimeEnd.Month, spaceTimeEnd.Day, spaceTimeEnd.Hour, spaceTimeEnd.Minute, spaceTimeEnd.Second, spaceTimeEnd.Millisecond);
PeriodType periodType;
TimeSpan period = endDateTime - startDateTime;
if (period.TotalHours < 0)
{
spaceTimeEnd = spaceTimeStart;
}
if (period.TotalHours >= 1)
{
if (period.TotalHours > 24)
{
periodType = PeriodType.Period;
}
else
{
periodType = PeriodType.Day;
}
}
else
{
periodType = PeriodType.Point;
}
AnalysisPeriod analysisPeriod = new AnalysisPeriod
{
StartTime = spaceTimeStart,
EndTime = spaceTimeEnd,
Type = periodType
};
return(analysisPeriod);
}
void LoadAnalysisData()
{
spreadsheetControl.Document.BeginUpdate();
var financialReportWorksheet = spreadsheetControl.Document.Worksheets["Financial Report"];
var financialReportItems = ViewModel.GetFinancialReport().ToList(); // materialize
var frProducts = financialReportItems
.Select(i => i.ProductName)
.Distinct()
.OrderBy(i => i).ToList();
financialReportWorksheet.Import(frProducts, 17, 1, true);
foreach (var reportItem in financialReportItems)
{
int rowOffset = frProducts.IndexOf(reportItem.ProductName);
int columnOffset = AnalysisPeriod.MonthOffsetFromStart(reportItem.Date) / 12;
if (rowOffset < 0 || columnOffset < 0)
{
continue;
}
financialReportWorksheet.Cells[17 + rowOffset, 3 + columnOffset * 2].SetValue(reportItem.Total);
}
var financialDataWorksheet = spreadsheetControl.Document.Worksheets["Financial Data"];
var financialDataItems = ViewModel.GetFinancialData().ToList(); // materialize
foreach (var dataItem in financialDataItems)
{
int rowOffset = AnalysisPeriod.MonthOffsetFromStart(dataItem.Date);
int columnOffset = GetColumnIndex(dataItem.ProductCategory);
if (rowOffset < 0 || columnOffset < 0)
{
continue;
}
financialDataWorksheet.Cells[6 + rowOffset, 3 + columnOffset].SetValue(dataItem.Total);
}
spreadsheetControl.Document.Worksheets.ActiveWorksheet = financialReportWorksheet;
spreadsheetControl.Document.EndUpdate();
}
