private void ExportSalesTeamMrReportToExcelRow(ExcelWorksheet sheet, int row, Color firstColumnBackground, CommonTargetActualSales line, int rowLevel = 0) { int indentEachLevel = 5; int col = 1; WriteCell(sheet, row, col, line.Name, ValueFormat.Text, firstColumnBackground, 20, rowLevel * indentEachLevel); col++; WriteCell(sheet, row, col, line.LastYearActualAmt, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.TargetAmt, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualAmt, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.Achivement, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.Growth, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualQty, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.LastYearActualAmt01, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.TargetAmt01, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualAmt01, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.Achivement01, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.Growth01, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualQty01, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.LastYearActualAmt02, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.TargetAmt02, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualAmt02, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.Achivement02, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.Growth02, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualQty02, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.LastYearActualAmt03, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.TargetAmt03, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualAmt03, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.Achivement03, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.Growth03, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualQty03, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.LastYearActualAmt04, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.TargetAmt04, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualAmt04, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.Achivement04, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.Growth04, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualQty04, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.LastYearActualAmt05, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.TargetAmt05, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualAmt05, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.Achivement05, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.Growth05, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualQty05, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.LastYearActualAmt06, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.TargetAmt06, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualAmt06, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.Achivement06, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.Growth06, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualQty06, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.LastYearActualAmt07, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.TargetAmt07, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualAmt07, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.Achivement07, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.Growth07, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualQty07, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.LastYearActualAmt08, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.TargetAmt08, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualAmt08, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.Achivement08, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.Growth08, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualQty08, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.LastYearActualAmt09, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.TargetAmt09, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualAmt09, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.Achivement09, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.Growth09, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualQty09, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.LastYearActualAmt10, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.TargetAmt10, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualAmt10, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.Achivement10, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.Growth10, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualQty10, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.LastYearActualAmt11, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.TargetAmt11, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualAmt11, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.Achivement11, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.Growth11, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualQty11, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.LastYearActualAmt12, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.TargetAmt12, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualAmt12, ValueFormat.Number, Color.Transparent); col++; WriteCell(sheet, row, col, line.Achivement12, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.Growth12, ValueFormat.Percent, Color.Transparent); col++; WriteCell(sheet, row, col, line.ActualQty12, ValueFormat.Number, Color.Transparent); col++; }
public void ExportRegionReportToExcel(Stream stream, int year) { if (stream == null) throw new ArgumentNullException("stream"); using (var xlPackage = new ExcelPackage(stream)) { // get handle to the existing worksheet var worksheet = xlPackage.Workbook.Worksheets.Add("Sheet1"); //Format Header 1 int header1 = 2; worksheet.Cells[1, header1].Value = "Total"; header1 = header1 + 4; worksheet.Cells[1, header1].Value = "January"; header1 = header1 + 4; worksheet.Cells[1, header1].Value = "February"; header1 = header1 + 4; worksheet.Cells[1, header1].Value = "March"; header1 = header1 + 4; worksheet.Cells[1, header1].Value = "April"; header1 = header1 + 4; worksheet.Cells[1, header1].Value = "May"; header1 = header1 + 4; worksheet.Cells[1, header1].Value = "June"; header1 = header1 + 4; worksheet.Cells[1, header1].Value = "July"; header1 = header1 + 4; worksheet.Cells[1, header1].Value = "August"; header1 = header1 + 4; worksheet.Cells[1, header1].Value = "September"; header1 = header1 + 4; worksheet.Cells[1, header1].Value = "October"; header1 = header1 + 4; worksheet.Cells[1, header1].Value = "November"; header1 = header1 + 4; worksheet.Cells[1, header1].Value = "December"; header1 = header1 + 4; //Format Header 2 for (int month = 0; month <= 12; month++) { int i = month * 4; worksheet.Cells[2, i + 2].Value = "Last actual amt"; worksheet.Cells[2, i + 3].Value = "Actual amt"; worksheet.Cells[2, i + 4].Value = "Growth"; worksheet.Cells[2, i + 5].Value = "Quantity"; } //Data int row = 3; var thisYearActualSalesQuery = from item in _dataContext.Transactions where item.FigureTypeId == (int)FigureTypeEnum.Sales && item.ScenarioId == (int)ScenarioEnum.Actual && item.Date.Year == year //&& (item.Client.ChannelId == (int)ChannelEnum.Direct || item.Client.ChannelId == (int)ChannelEnum.Indirect) && item.Item.Sku.ToLower() != "na" select item; var lastYearActualSalesQuery = from item in _dataContext.Transactions where item.FigureTypeId == (int)FigureTypeEnum.Sales && item.ScenarioId == (int)ScenarioEnum.Actual && item.Date.Year == year - 1 //&& (item.Client.ChannelId == (int)ChannelEnum.Direct || item.Client.ChannelId == (int)ChannelEnum.Indirect) && item.Item.Sku.ToLower() != "na" select item; var thisYearActualSales = thisYearActualSalesQuery; var lastYearActualSales = lastYearActualSalesQuery; #region authorization if (_workContext.CurrentCustomer.IsInCustomerRole("Administrators") || _workContext.CurrentCustomer.IsInCustomerRole("Management")) { //sales = query; } else { if (_workContext.CurrentCustomer.IsInCustomerRole("MrManagers")) { thisYearActualSales = from item in thisYearActualSalesQuery where item.Mr.SalesTeam.SalesTeamCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower() select item; lastYearActualSales = from item in lastYearActualSales where item.Mr.SalesTeam.SalesTeamCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower() select item; } else { thisYearActualSales = from item in thisYearActualSalesQuery where item.Mr.MrCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower() select item; lastYearActualSales = from item in thisYearActualSalesQuery where item.Mr.MrCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower() select item; } } #endregion IList<CommonTargetActualSales> reportRows = new List<CommonTargetActualSales>(); foreach (var channel in _dataContext.Channels) { #region Channel line CommonTargetActualSales channelLine = new CommonTargetActualSales(); channelLine.Code = channel.ChannelId.ToString(); channelLine.Name = channel.ChannelName; var channelQuery = thisYearActualSales.Where(a => a.Client.ChannelId == channel.ChannelId); var lastYearChannelQuery = lastYearActualSales.Where(a => a.Client.ChannelId == channel.ChannelId); channelLine.ActualQty01 = GetMonthQuantity(channelQuery, year, 1); channelLine.ActualQty02 = GetMonthQuantity(channelQuery, year, 2); channelLine.ActualQty03 = GetMonthQuantity(channelQuery, year, 3); channelLine.ActualQty04 = GetMonthQuantity(channelQuery, year, 4); channelLine.ActualQty05 = GetMonthQuantity(channelQuery, year, 5); channelLine.ActualQty06 = GetMonthQuantity(channelQuery, year, 6); channelLine.ActualQty07 = GetMonthQuantity(channelQuery, year, 7); channelLine.ActualQty08 = GetMonthQuantity(channelQuery, year, 8); channelLine.ActualQty09 = GetMonthQuantity(channelQuery, year, 9); channelLine.ActualQty10 = GetMonthQuantity(channelQuery, year, 10); channelLine.ActualQty11 = GetMonthQuantity(channelQuery, year, 11); channelLine.ActualQty12 = GetMonthQuantity(channelQuery, year, 12); channelLine.ActualAmt01 = GetMonthTotalAmount(channelQuery, year, 1); channelLine.ActualAmt02 = GetMonthTotalAmount(channelQuery, year, 2); channelLine.ActualAmt03 = GetMonthTotalAmount(channelQuery, year, 3); channelLine.ActualAmt04 = GetMonthTotalAmount(channelQuery, year, 4); channelLine.ActualAmt05 = GetMonthTotalAmount(channelQuery, year, 5); channelLine.ActualAmt06 = GetMonthTotalAmount(channelQuery, year, 6); channelLine.ActualAmt07 = GetMonthTotalAmount(channelQuery, year, 7); channelLine.ActualAmt08 = GetMonthTotalAmount(channelQuery, year, 8); channelLine.ActualAmt09 = GetMonthTotalAmount(channelQuery, year, 9); channelLine.ActualAmt10 = GetMonthTotalAmount(channelQuery, year, 10); channelLine.ActualAmt11 = GetMonthTotalAmount(channelQuery, year, 11); channelLine.ActualAmt12 = GetMonthTotalAmount(channelQuery, year, 12); channelLine.LastYearActualAmt01 = GetMonthTotalAmount(lastYearChannelQuery, year - 1, 1); channelLine.LastYearActualAmt02 = GetMonthTotalAmount(lastYearChannelQuery, year - 1, 2); channelLine.LastYearActualAmt03 = GetMonthTotalAmount(lastYearChannelQuery, year - 1, 3); channelLine.LastYearActualAmt04 = GetMonthTotalAmount(lastYearChannelQuery, year - 1, 4); channelLine.LastYearActualAmt05 = GetMonthTotalAmount(lastYearChannelQuery, year - 1, 5); channelLine.LastYearActualAmt06 = GetMonthTotalAmount(lastYearChannelQuery, year - 1, 6); channelLine.LastYearActualAmt07 = GetMonthTotalAmount(lastYearChannelQuery, year - 1, 7); channelLine.LastYearActualAmt08 = GetMonthTotalAmount(lastYearChannelQuery, year - 1, 8); channelLine.LastYearActualAmt09 = GetMonthTotalAmount(lastYearChannelQuery, year - 1, 9); channelLine.LastYearActualAmt10 = GetMonthTotalAmount(lastYearChannelQuery, year - 1, 10); channelLine.LastYearActualAmt11 = GetMonthTotalAmount(lastYearChannelQuery, year - 1, 11); channelLine.LastYearActualAmt12 = GetMonthTotalAmount(lastYearChannelQuery, year - 1, 12); if (channelLine.TargetAmt == 0 && channelLine.LastYearActualAmt == 0 && channelLine.ActualAmt == 0) { //do nothing } else { ExportRegionReportToExcelRow(worksheet, row, Color.LightBlue, channelLine); reportRows.Add(channelLine); row++; } #endregion foreach (var region in _dataContext.Regions) { #region region line CommonTargetActualSales regionLine = new CommonTargetActualSales(); regionLine.Code = region.RegionId.ToString(); regionLine.Name = region.RegionName; var regionQuery = thisYearActualSales.Where(a => a.Client.Province.RegionId == region.RegionId && a.Client.ChannelId == channel.ChannelId); var lastYearRegionQuery = lastYearActualSales.Where(a => a.Client.Province.RegionId == region.RegionId && a.Client.ChannelId == channel.ChannelId); regionLine.ActualQty01 = GetMonthQuantity(regionQuery, year, 1); regionLine.ActualQty02 = GetMonthQuantity(regionQuery, year, 2); regionLine.ActualQty03 = GetMonthQuantity(regionQuery, year, 3); regionLine.ActualQty04 = GetMonthQuantity(regionQuery, year, 4); regionLine.ActualQty05 = GetMonthQuantity(regionQuery, year, 5); regionLine.ActualQty06 = GetMonthQuantity(regionQuery, year, 6); regionLine.ActualQty07 = GetMonthQuantity(regionQuery, year, 7); regionLine.ActualQty08 = GetMonthQuantity(regionQuery, year, 8); regionLine.ActualQty09 = GetMonthQuantity(regionQuery, year, 9); regionLine.ActualQty10 = GetMonthQuantity(regionQuery, year, 10); regionLine.ActualQty11 = GetMonthQuantity(regionQuery, year, 11); regionLine.ActualQty12 = GetMonthQuantity(regionQuery, year, 12); regionLine.ActualAmt01 = GetMonthTotalAmount(regionQuery, year, 1); regionLine.ActualAmt02 = GetMonthTotalAmount(regionQuery, year, 2); regionLine.ActualAmt03 = GetMonthTotalAmount(regionQuery, year, 3); regionLine.ActualAmt04 = GetMonthTotalAmount(regionQuery, year, 4); regionLine.ActualAmt05 = GetMonthTotalAmount(regionQuery, year, 5); regionLine.ActualAmt06 = GetMonthTotalAmount(regionQuery, year, 6); regionLine.ActualAmt07 = GetMonthTotalAmount(regionQuery, year, 7); regionLine.ActualAmt08 = GetMonthTotalAmount(regionQuery, year, 8); regionLine.ActualAmt09 = GetMonthTotalAmount(regionQuery, year, 9); regionLine.ActualAmt10 = GetMonthTotalAmount(regionQuery, year, 10); regionLine.ActualAmt11 = GetMonthTotalAmount(regionQuery, year, 11); regionLine.ActualAmt12 = GetMonthTotalAmount(regionQuery, year, 12); regionLine.LastYearActualAmt01 = GetMonthTotalAmount(lastYearRegionQuery, year - 1, 1); regionLine.LastYearActualAmt02 = GetMonthTotalAmount(lastYearRegionQuery, year - 1, 2); regionLine.LastYearActualAmt03 = GetMonthTotalAmount(lastYearRegionQuery, year - 1, 3); regionLine.LastYearActualAmt04 = GetMonthTotalAmount(lastYearRegionQuery, year - 1, 4); regionLine.LastYearActualAmt05 = GetMonthTotalAmount(lastYearRegionQuery, year - 1, 5); regionLine.LastYearActualAmt06 = GetMonthTotalAmount(lastYearRegionQuery, year - 1, 6); regionLine.LastYearActualAmt07 = GetMonthTotalAmount(lastYearRegionQuery, year - 1, 7); regionLine.LastYearActualAmt08 = GetMonthTotalAmount(lastYearRegionQuery, year - 1, 8); regionLine.LastYearActualAmt09 = GetMonthTotalAmount(lastYearRegionQuery, year - 1, 9); regionLine.LastYearActualAmt10 = GetMonthTotalAmount(lastYearRegionQuery, year - 1, 10); regionLine.LastYearActualAmt11 = GetMonthTotalAmount(lastYearRegionQuery, year - 1, 11); regionLine.LastYearActualAmt12 = GetMonthTotalAmount(lastYearRegionQuery, year - 1, 12); if (regionLine.TargetAmt == 0 && regionLine.LastYearActualAmt == 0 && regionLine.ActualAmt == 0) { //do nothing } else { ExportRegionReportToExcelRow(worksheet, row, Color.LightBlue, regionLine, 1); row++; } #endregion } } #region total line CommonTargetActualSales totalLine = new CommonTargetActualSales(); totalLine.Code = "Total"; totalLine.Name = "Total"; totalLine.ActualQty01 = GetMonthQuantity(thisYearActualSales, year, 1); totalLine.ActualQty02 = GetMonthQuantity(thisYearActualSales, year, 2); totalLine.ActualQty03 = GetMonthQuantity(thisYearActualSales, year, 3); totalLine.ActualQty04 = GetMonthQuantity(thisYearActualSales, year, 4); totalLine.ActualQty05 = GetMonthQuantity(thisYearActualSales, year, 5); totalLine.ActualQty06 = GetMonthQuantity(thisYearActualSales, year, 6); totalLine.ActualQty07 = GetMonthQuantity(thisYearActualSales, year, 7); totalLine.ActualQty08 = GetMonthQuantity(thisYearActualSales, year, 8); totalLine.ActualQty09 = GetMonthQuantity(thisYearActualSales, year, 9); totalLine.ActualQty10 = GetMonthQuantity(thisYearActualSales, year, 10); totalLine.ActualQty11 = GetMonthQuantity(thisYearActualSales, year, 11); totalLine.ActualQty12 = GetMonthQuantity(thisYearActualSales, year, 12); totalLine.ActualAmt01 = GetMonthTotalAmount(thisYearActualSales, year, 1); totalLine.ActualAmt02 = GetMonthTotalAmount(thisYearActualSales, year, 2); totalLine.ActualAmt03 = GetMonthTotalAmount(thisYearActualSales, year, 3); totalLine.ActualAmt04 = GetMonthTotalAmount(thisYearActualSales, year, 4); totalLine.ActualAmt05 = GetMonthTotalAmount(thisYearActualSales, year, 5); totalLine.ActualAmt06 = GetMonthTotalAmount(thisYearActualSales, year, 6); totalLine.ActualAmt07 = GetMonthTotalAmount(thisYearActualSales, year, 7); totalLine.ActualAmt08 = GetMonthTotalAmount(thisYearActualSales, year, 8); totalLine.ActualAmt09 = GetMonthTotalAmount(thisYearActualSales, year, 9); totalLine.ActualAmt10 = GetMonthTotalAmount(thisYearActualSales, year, 10); totalLine.ActualAmt11 = GetMonthTotalAmount(thisYearActualSales, year, 11); totalLine.ActualAmt12 = GetMonthTotalAmount(thisYearActualSales, year, 12); totalLine.LastYearActualAmt01 = GetMonthTotalAmount(lastYearActualSales, year - 1, 1); totalLine.LastYearActualAmt02 = GetMonthTotalAmount(lastYearActualSales, year - 1, 2); totalLine.LastYearActualAmt03 = GetMonthTotalAmount(lastYearActualSales, year - 1, 3); totalLine.LastYearActualAmt04 = GetMonthTotalAmount(lastYearActualSales, year - 1, 4); totalLine.LastYearActualAmt05 = GetMonthTotalAmount(lastYearActualSales, year - 1, 5); totalLine.LastYearActualAmt06 = GetMonthTotalAmount(lastYearActualSales, year - 1, 6); totalLine.LastYearActualAmt07 = GetMonthTotalAmount(lastYearActualSales, year - 1, 7); totalLine.LastYearActualAmt08 = GetMonthTotalAmount(lastYearActualSales, year - 1, 8); totalLine.LastYearActualAmt09 = GetMonthTotalAmount(lastYearActualSales, year - 1, 9); totalLine.LastYearActualAmt10 = GetMonthTotalAmount(lastYearActualSales, year - 1, 10); totalLine.LastYearActualAmt11 = GetMonthTotalAmount(lastYearActualSales, year - 1, 11); totalLine.LastYearActualAmt12 = GetMonthTotalAmount(lastYearActualSales, year - 1, 12); ExportRegionReportToExcelRow(worksheet, row, Color.LightBlue, totalLine); #endregion //Report name worksheet.Cells[2, 1].Value = "Report by Channel - region in " + year.ToString(); worksheet.Cells[2, 1].AutoFitColumns(); //Rows & columns count int cols = 12 * 4 + 4 + 1; int rows = row; //Format header var header = worksheet.Cells[1, 1, 2, cols]; header.Style.Font.Bold = true; header.Style.Fill.PatternType = ExcelFillStyle.Solid; header.Style.Fill.BackgroundColor.SetColor(Color.LightBlue); //Format total var total = worksheet.Cells[rows, 1, row, cols]; total.Style.Font.Bold = true; total.Style.Fill.PatternType = ExcelFillStyle.Solid; total.Style.Fill.BackgroundColor.SetColor(Color.LightBlue); //Format row var text = worksheet.Cells[1, 1, row, 1]; text.Style.Font.Bold = true; text.Style.Fill.PatternType = ExcelFillStyle.Solid; text.Style.Fill.BackgroundColor.SetColor(Color.LightBlue); text.AutoFitColumns(); // save the new spreadsheet xlPackage.Save(); } }
public void ExportSalesTeamMrReportToExcel(Stream stream, int year) { if (stream == null) throw new ArgumentNullException("stream"); using (var xlPackage = new ExcelPackage(stream)) { // get handle to the existing worksheet var worksheet = xlPackage.Workbook.Worksheets.Add("Sheet1"); //Format Header 1 int header1 = 2; worksheet.Cells[1, header1].Value = "Total"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "January"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "February"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "March"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "April"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "May"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "June"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "July"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "August"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "September"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "October"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "November"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "December"; header1 = header1 + 6; //Format Header 2 for (int month = 0; month <= 12; month++) { int i = month * 6; worksheet.Cells[2, i + 2].Value = "Last actual amt"; worksheet.Cells[2, i + 3].Value = "Target amt"; worksheet.Cells[2, i + 4].Value = "Actual amt"; worksheet.Cells[2, i + 5].Value = "Achievement"; worksheet.Cells[2, i + 6].Value = "Growth"; worksheet.Cells[2, i + 7].Value = "Quantity"; } //Data int row = 3; var thisYearTargetSales = from item in _dataContext.TargetSetups where item.Year == year && item.IsTeamTarget == false && item.Item.Sku.ToLower() != "na" select item; var actualSalesQuery = from item in _dataContext.ReportDatas where (item.Year == year || item.Year == year - 1) select item; if (_workContext.CurrentCustomer.IsInCustomerRole("Administrators") || _workContext.CurrentCustomer.IsInCustomerRole("Management")) { //sales = query; } else { if (_workContext.CurrentCustomer.IsInCustomerRole("MrManagers")) { actualSalesQuery = from item in actualSalesQuery where item.SalesTeamCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower() select item; } else { actualSalesQuery = from item in actualSalesQuery where item.MrCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower() select item; thisYearTargetSales = from item in _dataContext.TargetSetups where item.Year == year && item.IsTeamTarget == true && item.Mr.MrCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower() select item; } } DataTable table = LinqExtension.LinqResultToDataTable(actualSalesQuery); var quantityPivotData = new PivotData(new[] { "ChannelName", "SalesTeamCode", "MrCode", "Year", "Month" }, new SumAggregatorFactory("Quantity") ); quantityPivotData.ProcessData(new DataTableReader(table)); var amountPivotData = new PivotData(new[] { "ChannelName", "SalesTeamCode", "MrCode", "Year", "Month" }, new SumAggregatorFactory("TotalAmount") ); amountPivotData.ProcessData(new DataTableReader(table)); foreach (var channel in _dataContext.Channels) { #region Channel line CommonTargetActualSales channelLine = new CommonTargetActualSales(); channelLine.Code = channel.ChannelId.ToString(); channelLine.Name = channel.ChannelName; var thisYearTargetSalesChannel = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId); channelLine.ActualQty01 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 1].Value); channelLine.ActualQty02 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 2].Value); channelLine.ActualQty03 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 3].Value); channelLine.ActualQty04 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 4].Value); channelLine.ActualQty05 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 5].Value); channelLine.ActualQty06 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 6].Value); channelLine.ActualQty07 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 7].Value); channelLine.ActualQty08 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 8].Value); channelLine.ActualQty09 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 9].Value); channelLine.ActualQty10 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 10].Value); channelLine.ActualQty11 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 11].Value); channelLine.ActualQty12 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 12].Value); channelLine.ActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 1].Value); channelLine.ActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 2].Value); channelLine.ActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 3].Value); channelLine.ActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 4].Value); channelLine.ActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 5].Value); channelLine.ActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 6].Value); channelLine.ActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 7].Value); channelLine.ActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 8].Value); channelLine.ActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 9].Value); channelLine.ActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 10].Value); channelLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 11].Value); channelLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 12].Value); channelLine.LastYearActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 1].Value); channelLine.LastYearActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 2].Value); channelLine.LastYearActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 3].Value); channelLine.LastYearActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 4].Value); channelLine.LastYearActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 5].Value); channelLine.LastYearActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 6].Value); channelLine.LastYearActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 7].Value); channelLine.LastYearActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 8].Value); channelLine.LastYearActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 9].Value); channelLine.LastYearActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 10].Value); channelLine.LastYearActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 11].Value); channelLine.LastYearActualAmt12 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 12].Value); channelLine.TargetAmt01 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 1); channelLine.TargetAmt02 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 2); channelLine.TargetAmt03 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 3); channelLine.TargetAmt04 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 4); channelLine.TargetAmt05 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 5); channelLine.TargetAmt06 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 6); channelLine.TargetAmt07 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 7); channelLine.TargetAmt08 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 8); channelLine.TargetAmt09 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 9); channelLine.TargetAmt10 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 10); channelLine.TargetAmt11 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 11); channelLine.TargetAmt12 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 12); if (channelLine.TargetAmt == 0 && channelLine.LastYearActualAmt == 0 && channelLine.ActualAmt == 0) { //do nothing } else { ExportSalesTeamMrReportToExcelRow(worksheet, row, Color.LightBlue, channelLine); row++; } #endregion foreach (var team in channel.SalesTeams) { #region team line CommonTargetActualSales teamLine = new CommonTargetActualSales(); teamLine.Code = team.SalesTeamCode; teamLine.Name = team.SalesTeamName; var thisYearTargetItemGroup = thisYearTargetSales.Where(a => a.Mr.SalesTeam.SalesTeamCode.ToLower() == team.SalesTeamCode.ToLower()); teamLine.ActualQty01 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 1].Value); teamLine.ActualQty02 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 2].Value); teamLine.ActualQty03 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 3].Value); teamLine.ActualQty04 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 4].Value); teamLine.ActualQty05 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 5].Value); teamLine.ActualQty06 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 6].Value); teamLine.ActualQty07 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 7].Value); teamLine.ActualQty08 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 8].Value); teamLine.ActualQty09 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 9].Value); teamLine.ActualQty10 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 10].Value); teamLine.ActualQty11 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 11].Value); teamLine.ActualQty12 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 12].Value); teamLine.ActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 1].Value); teamLine.ActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 2].Value); teamLine.ActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 3].Value); teamLine.ActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 4].Value); teamLine.ActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 5].Value); teamLine.ActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 6].Value); teamLine.ActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 7].Value); teamLine.ActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 8].Value); teamLine.ActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 9].Value); teamLine.ActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 10].Value); teamLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 11].Value); teamLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 12].Value); teamLine.LastYearActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 1].Value); teamLine.LastYearActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 2].Value); teamLine.LastYearActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 3].Value); teamLine.LastYearActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 4].Value); teamLine.LastYearActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 5].Value); teamLine.LastYearActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 6].Value); teamLine.LastYearActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 7].Value); teamLine.LastYearActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 8].Value); teamLine.LastYearActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 9].Value); teamLine.LastYearActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 10].Value); teamLine.LastYearActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 11].Value); teamLine.LastYearActualAmt12 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 12].Value); teamLine.TargetAmt01 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 1); teamLine.TargetAmt02 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 2); teamLine.TargetAmt03 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 3); teamLine.TargetAmt04 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 4); teamLine.TargetAmt05 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 5); teamLine.TargetAmt06 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 6); teamLine.TargetAmt07 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 7); teamLine.TargetAmt08 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 8); teamLine.TargetAmt09 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 9); teamLine.TargetAmt10 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 10); teamLine.TargetAmt11 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 11); teamLine.TargetAmt12 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 12); if (teamLine.TargetAmt == 0 && teamLine.LastYearActualAmt == 0 && teamLine.ActualAmt == 0) { //do nothing } else { ExportSalesTeamMrReportToExcelRow(worksheet, row, Color.LightBlue, teamLine, 1); row++; } #endregion foreach (var item in team.Mrs.Where(a=>a.MrCode.ToLower()!=team.SalesTeamCode.ToLower())) { #region item line CommonTargetActualSales mrLine = new CommonTargetActualSales(); mrLine.Code = item.MrCode; mrLine.Name = item.MrName; var thisYearTargetItem = thisYearTargetSales.Where(a => a.Mr.MrCode.ToLower() == item.MrCode.ToLower()); mrLine.ActualQty01 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 1].Value); mrLine.ActualQty02 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 2].Value); mrLine.ActualQty03 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 3].Value); mrLine.ActualQty04 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 4].Value); mrLine.ActualQty05 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 5].Value); mrLine.ActualQty06 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 6].Value); mrLine.ActualQty07 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 7].Value); mrLine.ActualQty08 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 8].Value); mrLine.ActualQty09 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 9].Value); mrLine.ActualQty10 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 10].Value); mrLine.ActualQty11 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 11].Value); mrLine.ActualQty12 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 12].Value); mrLine.ActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 1].Value); mrLine.ActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 2].Value); mrLine.ActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 3].Value); mrLine.ActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 4].Value); mrLine.ActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 5].Value); mrLine.ActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 6].Value); mrLine.ActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 7].Value); mrLine.ActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 8].Value); mrLine.ActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 9].Value); mrLine.ActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 10].Value); mrLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 11].Value); mrLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 12].Value); mrLine.LastYearActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 1].Value); mrLine.LastYearActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 2].Value); mrLine.LastYearActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 3].Value); mrLine.LastYearActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 4].Value); mrLine.LastYearActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 5].Value); mrLine.LastYearActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 6].Value); mrLine.LastYearActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 7].Value); mrLine.LastYearActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 8].Value); mrLine.LastYearActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 9].Value); mrLine.LastYearActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 10].Value); mrLine.LastYearActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 11].Value); mrLine.LastYearActualAmt12 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 12].Value); mrLine.TargetAmt01 = GetMonthTargetAmount(thisYearTargetItem, year, 1); mrLine.TargetAmt02 = GetMonthTargetAmount(thisYearTargetItem, year, 2); mrLine.TargetAmt03 = GetMonthTargetAmount(thisYearTargetItem, year, 3); mrLine.TargetAmt04 = GetMonthTargetAmount(thisYearTargetItem, year, 4); mrLine.TargetAmt05 = GetMonthTargetAmount(thisYearTargetItem, year, 5); mrLine.TargetAmt06 = GetMonthTargetAmount(thisYearTargetItem, year, 6); mrLine.TargetAmt07 = GetMonthTargetAmount(thisYearTargetItem, year, 7); mrLine.TargetAmt08 = GetMonthTargetAmount(thisYearTargetItem, year, 8); mrLine.TargetAmt09 = GetMonthTargetAmount(thisYearTargetItem, year, 9); mrLine.TargetAmt10 = GetMonthTargetAmount(thisYearTargetItem, year, 10); mrLine.TargetAmt11 = GetMonthTargetAmount(thisYearTargetItem, year, 11); mrLine.TargetAmt12 = GetMonthTargetAmount(thisYearTargetItem, year, 12); if (mrLine.TargetAmt == 0 && mrLine.LastYearActualAmt == 0 && mrLine.ActualAmt == 0) { //do nothing } else { ExportSalesTeamMrReportToExcelRow(worksheet, row, Color.LightBlue, mrLine, 2); row++; } #endregion } } } #region Total line CommonTargetActualSales totalLine = new CommonTargetActualSales(); totalLine.Code = "Total"; totalLine.Name = "Total"; totalLine.ActualQty01 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 1].Value); totalLine.ActualQty02 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 2].Value); totalLine.ActualQty03 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 3].Value); totalLine.ActualQty04 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 4].Value); totalLine.ActualQty05 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 5].Value); totalLine.ActualQty06 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 6].Value); totalLine.ActualQty07 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 7].Value); totalLine.ActualQty08 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 8].Value); totalLine.ActualQty09 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 9].Value); totalLine.ActualQty10 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 10].Value); totalLine.ActualQty11 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 11].Value); totalLine.ActualQty12 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 12].Value); totalLine.ActualAmt01 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 1].Value); totalLine.ActualAmt02 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 2].Value); totalLine.ActualAmt03 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 3].Value); totalLine.ActualAmt04 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 4].Value); totalLine.ActualAmt05 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 5].Value); totalLine.ActualAmt06 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 6].Value); totalLine.ActualAmt07 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 7].Value); totalLine.ActualAmt08 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 8].Value); totalLine.ActualAmt09 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 9].Value); totalLine.ActualAmt10 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 10].Value); totalLine.ActualAmt11 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 11].Value); totalLine.ActualAmt11 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 12].Value); totalLine.LastYearActualAmt01 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 1].Value); totalLine.LastYearActualAmt02 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 2].Value); totalLine.LastYearActualAmt03 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 3].Value); totalLine.LastYearActualAmt04 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 4].Value); totalLine.LastYearActualAmt05 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 5].Value); totalLine.LastYearActualAmt06 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 6].Value); totalLine.LastYearActualAmt07 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 7].Value); totalLine.LastYearActualAmt08 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 8].Value); totalLine.LastYearActualAmt09 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 9].Value); totalLine.LastYearActualAmt10 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 10].Value); totalLine.LastYearActualAmt11 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 11].Value); totalLine.LastYearActualAmt12 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 12].Value); totalLine.TargetAmt01 = GetMonthTargetAmount(thisYearTargetSales, year, 1); totalLine.TargetAmt02 = GetMonthTargetAmount(thisYearTargetSales, year, 2); totalLine.TargetAmt03 = GetMonthTargetAmount(thisYearTargetSales, year, 3); totalLine.TargetAmt04 = GetMonthTargetAmount(thisYearTargetSales, year, 4); totalLine.TargetAmt05 = GetMonthTargetAmount(thisYearTargetSales, year, 5); totalLine.TargetAmt06 = GetMonthTargetAmount(thisYearTargetSales, year, 6); totalLine.TargetAmt07 = GetMonthTargetAmount(thisYearTargetSales, year, 7); totalLine.TargetAmt08 = GetMonthTargetAmount(thisYearTargetSales, year, 8); totalLine.TargetAmt09 = GetMonthTargetAmount(thisYearTargetSales, year, 9); totalLine.TargetAmt10 = GetMonthTargetAmount(thisYearTargetSales, year, 10); totalLine.TargetAmt11 = GetMonthTargetAmount(thisYearTargetSales, year, 11); totalLine.TargetAmt12 = GetMonthTargetAmount(thisYearTargetSales, year, 12); ExportItemGroupItemReportToExcelRow(worksheet, row, Color.LightBlue, totalLine); #endregion //Report name worksheet.Cells[2, 1].Value = "Report by Channel - Sales team - Mr in " + year.ToString(); worksheet.Cells[2, 1].AutoFitColumns(); //Rows & columns count int cols = 12 * 6 + 6 + 1; int rows = row; //Format header var header = worksheet.Cells[1, 1, 2, cols]; header.Style.Font.Bold = true; header.Style.Fill.PatternType = ExcelFillStyle.Solid; header.Style.Fill.BackgroundColor.SetColor(Color.LightBlue); //Format total var total = worksheet.Cells[rows, 1, row, cols]; total.Style.Font.Bold = true; total.Style.Fill.PatternType = ExcelFillStyle.Solid; total.Style.Fill.BackgroundColor.SetColor(Color.LightBlue); //Format row var text = worksheet.Cells[1, 1, row, 1]; text.Style.Font.Bold = true; text.Style.Fill.PatternType = ExcelFillStyle.Solid; text.Style.Fill.BackgroundColor.SetColor(Color.LightBlue); text.AutoFitColumns(); // save the new spreadsheet xlPackage.Save(); } }
public void ExportChannelReportToExcelUSD(Stream stream, int year) { if (stream == null) throw new ArgumentNullException("stream"); using (var xlPackage = new ExcelPackage(stream)) { // get handle to the existing worksheet var worksheet = xlPackage.Workbook.Worksheets.Add("Sheet1"); //Format Header 1 int header1 = 2; worksheet.Cells[1, header1].Value = "Total"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "January"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "February"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "March"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "April"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "May"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "June"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "July"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "August"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "September"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "October"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "November"; header1 = header1 + 6; worksheet.Cells[1, header1].Value = "December"; header1 = header1 + 6; //Format Header 2 for (int month = 0; month <= 12; month++) { int i = month * 6; worksheet.Cells[2, i + 2].Value = "Last actual amt"; worksheet.Cells[2, i + 3].Value = "Target amt"; worksheet.Cells[2, i + 4].Value = "Actual amt"; worksheet.Cells[2, i + 5].Value = "Achievement"; worksheet.Cells[2, i + 6].Value = "Growth"; worksheet.Cells[2, i + 7].Value = "Quantity"; } //Exc double exchangeRate = 22500; //Data int row = 3; var thisYearTargetSales = from item in _dataContext.TargetSetups where item.Year == year && item.IsTeamTarget == true //&& (item.Mr.SalesTeam.ChannelId == (int)ChannelEnum.Direct || item.Mr.SalesTeam.ChannelId == (int)ChannelEnum.Indirect) select item; var actualSalesQuery = from item in _dataContext.ReportDatas where (item.Year == year || item.Year == year - 1) select item; if (_workContext.CurrentCustomer.IsInCustomerRole("Administrators") || _workContext.CurrentCustomer.IsInCustomerRole("Management")) { //sales = query; } else { if (_workContext.CurrentCustomer.IsInCustomerRole("MrManagers")) { actualSalesQuery = from item in actualSalesQuery where item.SalesTeamCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower() select item; } else { actualSalesQuery = from item in actualSalesQuery where item.MrCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower() select item; thisYearTargetSales = from item in _dataContext.TargetSetups where item.Year == year && item.IsTeamTarget == false && item.Mr.MrCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower() select item; } } DataTable table = LinqExtension.LinqResultToDataTable(actualSalesQuery); var quantityPivotData = new PivotData(new[] { "ChannelName", "Sku", "Year", "Month" }, new SumAggregatorFactory("Quantity") ); quantityPivotData.ProcessData(new DataTableReader(table)); var amountPivotData = new PivotData(new[] { "ChannelName", "Sku", "Year", "Month" }, new SumAggregatorFactory("CifAmount") ); amountPivotData.ProcessData(new DataTableReader(table)); foreach (var channel in _dataContext.Channels.Where(a => a.ChannelName.ToLower() != "na")) { CommonTargetActualSales channelLine = new CommonTargetActualSales(); channelLine.Code = channel.ChannelName; channelLine.Name = channel.ChannelName; channelLine.ActualQty01 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 1].Value); channelLine.ActualQty02 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 2].Value); channelLine.ActualQty03 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 3].Value); channelLine.ActualQty04 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 4].Value); channelLine.ActualQty05 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 5].Value); channelLine.ActualQty06 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 6].Value); channelLine.ActualQty07 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 7].Value); channelLine.ActualQty08 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 8].Value); channelLine.ActualQty09 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 9].Value); channelLine.ActualQty10 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 10].Value); channelLine.ActualQty11 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 11].Value); channelLine.ActualQty12 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 12].Value); channelLine.ActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 1].Value) ; channelLine.ActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 2].Value) ; channelLine.ActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 3].Value) ; channelLine.ActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 4].Value) ; channelLine.ActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 5].Value) ; channelLine.ActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 6].Value) ; channelLine.ActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 7].Value) ; channelLine.ActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 8].Value) ; channelLine.ActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 9].Value) ; channelLine.ActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 10].Value) ; channelLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 11].Value) ; channelLine.ActualAmt12 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 12].Value) ; channelLine.LastYearActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 1].Value) ; channelLine.LastYearActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 2].Value) ; channelLine.LastYearActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 3].Value) ; channelLine.LastYearActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 4].Value) ; channelLine.LastYearActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 5].Value) ; channelLine.LastYearActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 6].Value) ; channelLine.LastYearActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 7].Value) ; channelLine.LastYearActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 8].Value) ; channelLine.LastYearActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 9].Value) ; channelLine.LastYearActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 10].Value) ; channelLine.LastYearActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 11].Value) ; channelLine.LastYearActualAmt12 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 12].Value) ; if (thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Count() > 0) { channelLine.TargetAmt01 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount01) / exchangeRate; channelLine.TargetAmt02 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount02) / exchangeRate; channelLine.TargetAmt03 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount03) / exchangeRate; channelLine.TargetAmt04 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount04) / exchangeRate; channelLine.TargetAmt05 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount05) / exchangeRate; channelLine.TargetAmt06 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount06) / exchangeRate; channelLine.TargetAmt07 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount07) / exchangeRate; channelLine.TargetAmt08 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount08) / exchangeRate; channelLine.TargetAmt09 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount09) / exchangeRate; channelLine.TargetAmt10 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount10) / exchangeRate; channelLine.TargetAmt11 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount11) / exchangeRate; channelLine.TargetAmt12 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount12) / exchangeRate; } if (channelLine.TargetAmt == 0 && channelLine.LastYearActualAmt == 0 && channelLine.ActualAmt == 0) { //do nothing } else { ExportChannelReportToExcelRow(worksheet, row, Color.LightBlue, channelLine); row++; } foreach (var item in _dataContext.Items.OrderBy(a=>a.ItemName)) { CommonTargetActualSales itemLine = new CommonTargetActualSales(); itemLine.Code = item.Sku; itemLine.Name = item.ItemName; itemLine.ActualQty01 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 1].Value); itemLine.ActualQty02 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 2].Value); itemLine.ActualQty03 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 3].Value); itemLine.ActualQty04 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 4].Value); itemLine.ActualQty05 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 5].Value); itemLine.ActualQty06 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 6].Value); itemLine.ActualQty07 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 7].Value); itemLine.ActualQty08 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 8].Value); itemLine.ActualQty09 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 9].Value); itemLine.ActualQty10 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 10].Value); itemLine.ActualQty11 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 11].Value); itemLine.ActualQty12 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 12].Value); itemLine.ActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 1].Value) ; itemLine.ActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 2].Value) ; itemLine.ActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 3].Value) ; itemLine.ActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 4].Value) ; itemLine.ActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 5].Value) ; itemLine.ActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 6].Value) ; itemLine.ActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 7].Value) ; itemLine.ActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 8].Value) ; itemLine.ActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 9].Value) ; itemLine.ActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 10].Value) ; itemLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 11].Value) ; itemLine.ActualAmt12 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 12].Value) ; itemLine.LastYearActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 1].Value) ; itemLine.LastYearActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 2].Value) ; itemLine.LastYearActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 3].Value) ; itemLine.LastYearActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 4].Value) ; itemLine.LastYearActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 5].Value) ; itemLine.LastYearActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 6].Value) ; itemLine.LastYearActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 7].Value) ; itemLine.LastYearActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 8].Value) ; itemLine.LastYearActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 9].Value) ; itemLine.LastYearActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 10].Value) ; itemLine.LastYearActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 11].Value) ; itemLine.LastYearActualAmt12 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 12].Value) ; if (thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Count() > 0) { itemLine.TargetAmt01 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount01) / exchangeRate; itemLine.TargetAmt02 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount02) / exchangeRate; itemLine.TargetAmt03 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount03) / exchangeRate; itemLine.TargetAmt04 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount04) / exchangeRate; itemLine.TargetAmt05 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount05) / exchangeRate; itemLine.TargetAmt06 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount06) / exchangeRate; itemLine.TargetAmt07 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount07) / exchangeRate; itemLine.TargetAmt08 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount08) / exchangeRate; itemLine.TargetAmt09 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount09) / exchangeRate; itemLine.TargetAmt10 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount10) / exchangeRate; itemLine.TargetAmt11 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount11) / exchangeRate; itemLine.TargetAmt12 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount12) / exchangeRate; } if (itemLine.TargetAmt == 0 && itemLine.LastYearActualAmt == 0 && itemLine.ActualAmt == 0) { //do nothing } else { ExportChannelReportToExcelRow(worksheet, row, Color.LightBlue, itemLine, 1); row++; } } } //Report name worksheet.Cells[2, 1].Value = "Report by Channel - product in " + year.ToString(); worksheet.Cells[2, 1].AutoFitColumns(); //Rows & columns count int cols = 12 * 6 + 6 + 1; int rows = row; //Format header var header = worksheet.Cells[1, 1, 2, cols]; header.Style.Font.Bold = true; header.Style.Fill.PatternType = ExcelFillStyle.Solid; header.Style.Fill.BackgroundColor.SetColor(Color.LightBlue); //Format total var total = worksheet.Cells[rows, 1, row, cols]; total.Style.Font.Bold = true; total.Style.Fill.PatternType = ExcelFillStyle.Solid; total.Style.Fill.BackgroundColor.SetColor(Color.LightBlue); //Format row var text = worksheet.Cells[1, 1, row, 1]; text.Style.Font.Bold = true; text.Style.Fill.PatternType = ExcelFillStyle.Solid; text.Style.Fill.BackgroundColor.SetColor(Color.LightBlue); text.AutoFitColumns(); // save the new spreadsheet xlPackage.Save(); } }