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();
}
}