private void ProceedProductMonthCharts(XlWorksheet productSheet, SalesReportProduct itemProduct)
{
double chartTop = productSheet.Rows[15].Top;
double chartWidth = productSheet.Columns[14].Left;
double chartHeight = productSheet.Rows[30].Top - productSheet.Rows[15].Top;
XlChartObject chartMonths = productSheet.ChartObjects.Add(1, chartTop, chartWidth, chartHeight);
chartMonths.Chart.SetSourceData(productSheet.Range("$A9:$M12"));
chartTop = productSheet.Rows[31].Top;
chartWidth = productSheet.Columns[14].Left;
chartHeight = productSheet.Rows[40].Top - productSheet.Rows[33].Top;
XlChartObject chartCountMonths = productSheet.ChartObjects.Add(1, chartTop, chartWidth, chartHeight);
chartCountMonths.Chart.ChartType = XlChartType.xlLine;
chartCountMonths.Chart.SetSourceData(productSheet.Range("$A13:$M13"));
double chartLeft = productSheet.Columns[15].Left;
chartTop = productSheet.Rows[15].Top;
chartWidth = productSheet.Columns[19].Left - productSheet.Columns[15].Left;
chartHeight = productSheet.Rows[30].Top - productSheet.Rows[15].Top;
XlChartObject chartCountYears = productSheet.ChartObjects.Add(chartLeft, chartTop, chartWidth, chartHeight);
chartCountYears.Chart.ChartType = XlChartType.xlCylinderColClustered;
chartCountYears.Chart.SetSourceData(productSheet.Range("$O9:$P12"));
}
private void ProceedProductMonthInfo(Excel.Worksheet productSheet, SalesReportProduct itemProduct)
{
int rowIndex = 9;
int iMonthCellIndex = 1;
// we use the native invoker to set the style as string
Excel.Range range = productSheet.get_Range("$A9:$M13");
LateBindingApi.Core.Invoker.PropertySet(range, "Style", "MonthInfos");
productSheet.Cells[rowIndex + 1, iMonthCellIndex].Value = "ManufacturerPriceSummary";
productSheet.Cells[rowIndex + 2, iMonthCellIndex].Value = "SalesPricesSummary";
productSheet.Cells[rowIndex + 3, iMonthCellIndex].Value = "TotalRevenue";
productSheet.Cells[rowIndex + 4, iMonthCellIndex].Value = "CountOfSales";
iMonthCellIndex = 2;;
foreach (SalesReportReportEntity itemMonth in itemProduct.PrevMonths)
{
productSheet.Cells[rowIndex, iMonthCellIndex].Value = GetMonthName(iMonthCellIndex - 2);
productSheet.Cells[rowIndex + 1, iMonthCellIndex].Value = itemMonth.ManufactorPriceSummary;
productSheet.Cells[rowIndex + 2, iMonthCellIndex].Value = itemMonth.SalesPricesSummary;
productSheet.Cells[rowIndex + 3, iMonthCellIndex].Value = itemMonth.OutcomeSummary;
productSheet.Cells[rowIndex + 4, iMonthCellIndex].Value = itemMonth.CountOfSales;
iMonthCellIndex++;
}
string cellAdress1 = Helper.ToCellAdress(itemProduct.PrevMonths.Count + 2, 10);
string cellAdress2 = Helper.ToCellAdress(itemProduct.PrevMonths.Count + 2, 12);
productSheet.get_Range("$B10:$" + cellAdress1).Interior.Color = Helper.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.get_Range("$B12:$" + cellAdress2).Interior.Color = Helper.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.Cells[rowIndex, iMonthCellIndex].Value = GetMonthName(_monthToReport - 1);
productSheet.Cells[rowIndex + 1, iMonthCellIndex].Value = itemProduct.Month.ManufactorPriceSummary;
productSheet.Cells[rowIndex + 2, iMonthCellIndex].Value = itemProduct.Month.SalesPricesSummary;
productSheet.Cells[rowIndex + 3, iMonthCellIndex].Value = itemProduct.Month.OutcomeSummary;
productSheet.Cells[rowIndex + 4, iMonthCellIndex].Value = itemProduct.Month.CountOfSales;
for (int i = itemProduct.PrevMonths.Count + 2; i <= 12; i++)
{
iMonthCellIndex++;
productSheet.Cells[rowIndex, iMonthCellIndex].Value = GetMonthName(i - 1);
}
productSheet.get_Range("$B9:$M9").NumberFormat = "";
productSheet.get_Range("$B9:$M9").Font.Bold = true;
productSheet.get_Range("$B13:$M13").NumberFormat = "";
productSheet.get_Range("$B13:$M13").HorizontalAlignment = XlHAlign.xlHAlignCenter;
if (itemProduct.PrevMonths.Count < 11)
{
string topLeftMergeCellAdress = "$" + Helper.ToCellAdress(itemProduct.PrevMonths.Count + 3, 10);
productSheet.get_Range(topLeftMergeCellAdress + ":$M13").MergeCells = true;
}
}
private void ProceedProductMonthInfo(XlWorksheet productSheet, SalesReportProduct itemProduct)
{
int rowIndex = 9;
int iMonthCellIndex = 1;
productSheet.Range("$A9:$M13").StyleAsString = "MonthInfos";
productSheet.Cells(rowIndex + 1, iMonthCellIndex).Value = "ManufacturerPriceSummary";
productSheet.Cells(rowIndex + 2, iMonthCellIndex).Value = "SalesPricesSummary";
productSheet.Cells(rowIndex + 3, iMonthCellIndex).Value = "TotalRevenue";
productSheet.Cells(rowIndex + 4, iMonthCellIndex).Value = "CountOfSales";
iMonthCellIndex = 2;;
foreach (SalesReportReportEntity itemMonth in itemProduct.PrevMonths)
{
productSheet.Cells(rowIndex, iMonthCellIndex).Value = GetMonthName(iMonthCellIndex - 2);
productSheet.Cells(rowIndex + 1, iMonthCellIndex).Value = itemMonth.ManufactorPriceSummary;
productSheet.Cells(rowIndex + 2, iMonthCellIndex).Value = itemMonth.SalesPricesSummary;
productSheet.Cells(rowIndex + 3, iMonthCellIndex).Value = itemMonth.OutcomeSummary;
productSheet.Cells(rowIndex + 4, iMonthCellIndex).Value = itemMonth.CountOfSales;
iMonthCellIndex++;
}
string cellAdress1 = XlConverter.ToCellAdress(itemProduct.PrevMonths.Count + 2, 10);
string cellAdress2 = XlConverter.ToCellAdress(itemProduct.PrevMonths.Count + 2, 12);
productSheet.Range("$B10:$" + cellAdress1).Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.Range("$B12:$" + cellAdress2).Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.Cells(rowIndex, iMonthCellIndex).Value = GetMonthName(_monthToReport - 1);
productSheet.Cells(rowIndex + 1, iMonthCellIndex).Value = itemProduct.Month.ManufactorPriceSummary;
productSheet.Cells(rowIndex + 2, iMonthCellIndex).Value = itemProduct.Month.SalesPricesSummary;
productSheet.Cells(rowIndex + 3, iMonthCellIndex).Value = itemProduct.Month.OutcomeSummary;
productSheet.Cells(rowIndex + 4, iMonthCellIndex).Value = itemProduct.Month.CountOfSales;
for (int i = itemProduct.PrevMonths.Count + 2; i <= 12; i++)
{
iMonthCellIndex++;
productSheet.Cells(rowIndex, iMonthCellIndex).Value = GetMonthName(i - 1);
}
productSheet.Range("$B9:$M9").NumberFormat = "";
productSheet.Range("$B9:$M9").Font.Bold = true;
productSheet.Range("$B13:$M13").NumberFormat = "";
productSheet.Range("$B13:$M13").HorizontalAlignment = XlHAlign.xlHAlignCenter;
if (itemProduct.PrevMonths.Count < 11)
{
string topLeftMergeCellAdress = "$" + XlConverter.ToCellAdress(itemProduct.PrevMonths.Count + 3, 10);
productSheet.Range(topLeftMergeCellAdress + ":$M13").MergeCells = true;
}
}
private void ProceedProductWorksheetHeader(XlWorksheet productSheet, SalesReportProduct itemProduct)
{
int rowIndex = 1;
int columnIndex = 1;
productSheet.PageSetup.LeftHeader = "&D created";
productSheet.PageSetup.CenterHeader = "Vintage Digital Inc.";
productSheet.PageSetup.RightHeader = string.Format("Monthly Sales Report {1:00}/{0}", _yearToReport, _monthToReport);
productSheet.Cells(rowIndex, columnIndex).Value = itemProduct.ProductName;
productSheet.Cells(rowIndex, columnIndex).Font.Bold = true;
productSheet.Cells(rowIndex, columnIndex).Font.Underline = true;
productSheet.Cells(rowIndex, columnIndex).Font.Size = 14;
productSheet.Cells(rowIndex, columnIndex).Font.Name = "Verdana";
}
private void ProceedProductRankingInfo(XlWorksheet productSheet, SalesReportProduct itemProduct)
{
int rowIndex = 3;
int columnIndex = 4;
productSheet.Range("$D3:$F6").StyleAsString = "StorageAndRanking";
productSheet.Cells(rowIndex, columnIndex).Value = "Count Ranking";
productSheet.Cells(rowIndex, columnIndex).Font.Bold = true;
productSheet.Cells(rowIndex + 1, columnIndex).Value = "Month";
productSheet.Cells(rowIndex + 2, columnIndex).Value = "Year";
productSheet.Cells(rowIndex + 3, columnIndex).Value = "Total";
productSheet.Cells(rowIndex + 1, columnIndex + 2).Value = itemProduct.SalesRankMonth;
productSheet.Cells(rowIndex + 2, columnIndex + 2).Value = itemProduct.SalesRankYear;
productSheet.Cells(rowIndex + 3, columnIndex + 2).Value = itemProduct.SalesRankTotal;
productSheet.Range("$D4:$E6").Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlLineStyleNone;
}
private void ProceedProductWorksheet(XlWorksheet productSheet, SalesReportProduct itemProduct)
{
string sheetName = string.Format("{0}-{1}", itemProduct.ProductName, itemProduct.ProductId.ToString());
productSheet.Name = sheetName;
// its not a random chain, write data first and create charts second
ProceedProductStorageInfo(productSheet, itemProduct);
ProceedProductMonthInfo(productSheet, itemProduct);
ProceedProductYearTotalInfo(productSheet, itemProduct);
ProceedProductMonthCharts(productSheet, itemProduct);
ProceedProductPrintSettings(productSheet);
productSheet.Columns.AutoFit(); // proceed AutoFit before header & ranking
ProceedProductWorksheetHeader(productSheet, itemProduct);
ProceedProductRankingInfo(productSheet, itemProduct);
productSheet.Columns[14].ColumnWidth = 2.14;
productSheet.Columns[17].ColumnWidth = 5.14;
}
private void ProceedProductYearTotalInfo(Excel.Worksheet productSheet, SalesReportProduct itemProduct)
{
int ColumnIndex = 15;
int RowIndex = 9;
LateBindingApi.Core.Invoker.PropertySet(productSheet.get_Range("$O9:$R13"), "Style", "YearTotalInfos");
productSheet.Cells[RowIndex, ColumnIndex].Value = "Year " + _yearToReport.ToString();
productSheet.Cells[RowIndex + 1, ColumnIndex].Value = itemProduct.Year.ManufactorPriceSummary;
productSheet.Cells[RowIndex + 2, ColumnIndex].Value = itemProduct.Year.SalesPricesSummary;
productSheet.Cells[RowIndex + 3, ColumnIndex].Value = itemProduct.Year.OutcomeSummary;
productSheet.Cells[RowIndex + 4, ColumnIndex].Value = itemProduct.Year.CountOfSales;
productSheet.Cells[RowIndex, ColumnIndex + 1].Value = "Year " + (_yearToReport - 1).ToString();
productSheet.Cells[RowIndex + 1, ColumnIndex + 1].Value = itemProduct.PrevYear.ManufactorPriceSummary;
productSheet.Cells[RowIndex + 2, ColumnIndex + 1].Value = itemProduct.PrevYear.SalesPricesSummary;
productSheet.Cells[RowIndex + 3, ColumnIndex + 1].Value = itemProduct.PrevYear.OutcomeSummary;
productSheet.Cells[RowIndex + 4, ColumnIndex + 1].Value = itemProduct.PrevYear.CountOfSales;
productSheet.get_Range("$O10:$P10").Interior.Color = Helper.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.get_Range("$O12:$P12").Interior.Color = Helper.ToDouble(System.Drawing.Color.Gainsboro);
ColumnIndex = 18;
RowIndex = 9;
productSheet.Cells[RowIndex, ColumnIndex].Value = "Total";
productSheet.Cells[RowIndex + 1, ColumnIndex].Value = itemProduct.Total.ManufactorPriceSummary;
productSheet.Cells[RowIndex + 2, ColumnIndex].Value = itemProduct.Total.SalesPricesSummary;
productSheet.Cells[RowIndex + 3, ColumnIndex].Value = itemProduct.Total.OutcomeSummary;
productSheet.Cells[RowIndex + 4, ColumnIndex].Value = itemProduct.Total.CountOfSales;
productSheet.get_Range("$R10").Interior.Color = Helper.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.get_Range("$R12").Interior.Color = Helper.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.get_Range("$O9:$R9").NumberFormat = "";
productSheet.get_Range("$O9:$R9").Font.Bold = true;
productSheet.get_Range("$O13:$R13").NumberFormat = "";
productSheet.get_Range("$O13:$R13").HorizontalAlignment = XlHAlign.xlHAlignCenter;
productSheet.get_Range("$Q9:$Q13").MergeCells = true;
}
private void ProceedProductStorageInfo(XlWorksheet productSheet, SalesReportProduct itemProduct)
{
int rowIndex = 3;
int columnIndex = 1;
productSheet.Range("$A3:$B6").StyleAsString = "StorageAndRanking";
productSheet.Cells(rowIndex, columnIndex).Value = "Storage Info";
productSheet.Cells(rowIndex, columnIndex).Font.Bold = true;
productSheet.Cells(rowIndex + 1, columnIndex).Value = "Storage Count";
productSheet.Cells(rowIndex + 2, columnIndex).Value = "Sales in Progress";
productSheet.Cells(rowIndex + 3, columnIndex).Value = "Recalc Storage Count ";
productSheet.Cells(rowIndex + 1, columnIndex + 1).Value = itemProduct.StorageCount;
productSheet.Cells(rowIndex + 2, columnIndex + 1).Value = itemProduct.OpenOrders.CountOfSales;
productSheet.Cells(rowIndex + 3, columnIndex + 1).Value = itemProduct.StorageCount - itemProduct.OpenOrders.CountOfSales;
SetProductStorageCountColor(itemProduct.StorageCount, productSheet.Cells(rowIndex + 1, columnIndex + 1));
SetProductStorageCountColor(itemProduct.StorageCount - itemProduct.OpenOrders.CountOfSales, productSheet.Cells(rowIndex + 3, columnIndex + 1));
}
// Get data and calculate the report
public void Proceed()
{
// perfom report for any product
foreach (DataRow row in _dataProducts.Tables[0].Rows)
{
SalesReportProduct newProduct = new SalesReportProduct(this);
int ProductId = Convert.ToInt32(row["iProductId"]);
string ProductName = Convert.ToString(row["iName"]);
int StorageCount = Convert.ToInt32(row["iStorageCount"]);
newProduct.ProductId = ProductId;
newProduct.ProductName = ProductName;
newProduct.StorageCount = StorageCount;
GetSalesAmounts(ProductId, ref newProduct._Year, ref newProduct._PrevYear, ref newProduct._Total, ref newProduct._Month, ref newProduct._PrevMonths);
_listProducts.Add(newProduct);
GetOrders(ProductId, ref newProduct._OpenOrders);
}
SortMode = ProductSortMode.ByCountOfSalesMonth;
_listProducts.Sort();
for (int i = 0; i < _listProducts.Count; i++)
{
_listProducts[i].SalesRankMonth = _listProducts.Count - i;
}
SortMode = ProductSortMode.ByCountOfSalesYear;
_listProducts.Sort();
for (int i = 0; i < _listProducts.Count; i++)
{
_listProducts[i].SalesRankYear = _listProducts.Count - i;
}
SortMode = ProductSortMode.ByCountOfSalesTotal;
_listProducts.Sort();
for (int i = 0; i < _listProducts.Count; i++)
{
_listProducts[i].SalesRankTotal = _listProducts.Count - i;
}
SortMode = ProductSortMode.ByMaximumOutComeMonth;
_listProducts.Sort();
}
private void ProceedProductRankingInfo(Excel.Worksheet productSheet, SalesReportProduct itemProduct)
{
int rowIndex = 3;
int columnIndex = 4;
// we use the native invoker to set the style as string
Excel.Range range = productSheet.get_Range("$D3:$F6");
LateBindingApi.Core.Invoker.PropertySet(range, "Style", "StorageAndRanking");
productSheet.Cells[rowIndex, columnIndex].Value = "Count Ranking";
productSheet.Cells[rowIndex, columnIndex].Font.Bold = true;
productSheet.Cells[rowIndex + 1, columnIndex].Value = "Month";
productSheet.Cells[rowIndex + 2, columnIndex].Value = "Year";
productSheet.Cells[rowIndex + 3, columnIndex].Value = "Total";
productSheet.Cells[rowIndex + 1, columnIndex + 2].Value = itemProduct.SalesRankMonth;
productSheet.Cells[rowIndex + 2, columnIndex + 2].Value = itemProduct.SalesRankYear;
productSheet.Cells[rowIndex + 3, columnIndex + 2].Value = itemProduct.SalesRankTotal;
productSheet.get_Range("$D4:$E6").Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlLineStyleNone;
}
private void ProceedProductStorageInfo(Excel.Worksheet productSheet, SalesReportProduct itemProduct)
{
int rowIndex = 3;
int columnIndex = 1;
// we use the native invoker to set the style as string
Excel.Range range = productSheet.get_Range("$A3:$B6");
LateBindingApi.Core.Invoker.PropertySet(range, "Style", "StorageAndRanking");
productSheet.Cells[rowIndex, columnIndex].Value = "Storage Info";
productSheet.Cells[rowIndex, columnIndex].Font.Bold = true;
productSheet.Cells[rowIndex + 1, columnIndex].Value = "Storage Count";
productSheet.Cells[rowIndex + 2, columnIndex].Value = "Sales in Progress";
productSheet.Cells[rowIndex + 3, columnIndex].Value = "Recalc Storage Count ";
productSheet.Cells[rowIndex + 1, columnIndex + 1].Value = itemProduct.StorageCount;
productSheet.Cells[rowIndex + 2, columnIndex + 1].Value = itemProduct.OpenOrders.CountOfSales;
productSheet.Cells[rowIndex + 3, columnIndex + 1].Value = itemProduct.StorageCount - itemProduct.OpenOrders.CountOfSales;
SetProductStorageCountColor(itemProduct.StorageCount, productSheet.Cells[rowIndex + 1, columnIndex + 1]);
SetProductStorageCountColor(itemProduct.StorageCount - itemProduct.OpenOrders.CountOfSales, productSheet.Cells[rowIndex + 3, columnIndex + 1]);
}