private void CreateReferenceSheet(IXlsxDocumentBuilder document, int id)
{
var builder = new XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder("Reference", isPreservationSheet: true, isHidden: true);
builder.ActualRow = true;
document.AppendSheet(builder);
var excelinfo = new ExcelColumnInfo(id.ToString(), null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(" ", null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo("Version", null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo("2.1", null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
}
private void FormatWorkSheet(XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder builder, ActualWorksheet item, Dictionary <string, string> custom)
{
var excelinfo = new ExcelColumnInfo(item.Data, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlanName, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.VehicleName, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ProductName, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.SubtypeName, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.UnitName, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.MarketName, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.DemoName, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
if (custom != null && custom.Count > 0)
{
foreach (var it in custom)
{
excelinfo = new ExcelColumnInfo(it.Key, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
}
}
else
{
excelinfo = new ExcelColumnInfo(item.CreativeName, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.FundingSourceName, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
}
excelinfo = new ExcelColumnInfo(item.StartDate, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.EndDate, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
//planned
excelinfo = new ExcelColumnInfo(item.PlannedGRPs, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedTRPs, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
var hidden = !(Convert.ToBoolean(item.MediaType.EnableWeeklyReach) || Convert.ToInt16(item.MediaType.ReachType) > 0);
excelinfo = new ExcelColumnInfo(item.PlannedReach, null, new ExcelColumnAttribute()
{
IsHidden = hidden
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedFrequency, null, new ExcelColumnAttribute()
{
IsHidden = hidden
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
var shownet = Convert.ToBoolean(item.MediaType.ExternalActualsNet);
excelinfo = new ExcelColumnInfo(item.PlannedGross, null, new ExcelColumnAttribute()
{
IsHidden = shownet
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedNet, null, new ExcelColumnAttribute()
{
IsHidden = !shownet
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
PopulatePlannedActualColumns(builder, item);
//actual
excelinfo = new ExcelColumnInfo(item.ActualGRPs, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualTRPs, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualReach, null, new ExcelColumnAttribute()
{
IsHidden = hidden
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualFrequency, null, new ExcelColumnAttribute()
{
IsHidden = hidden
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
//if (!shownet)
//{
// excelinfo = new ExcelColumnInfo(item.ActualGross, null, new ExcelColumnAttribute(), null);
// builder.AppendColumnHeaderRowItem(excelinfo);
//}
//else
//{
// excelinfo = new ExcelColumnInfo(item.ActualNet, null, new ExcelColumnAttribute(), null);
// builder.AppendColumnHeaderRowItem(excelinfo);
//}
excelinfo = new ExcelColumnInfo(item.ActualGross, null, new ExcelColumnAttribute()
{
IsHidden = shownet
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualNet, null, new ExcelColumnAttribute()
{
IsHidden = !shownet
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
PopulatePlannedActualColumns(builder, item, true);
excelinfo = new ExcelColumnInfo(item.DateActualized, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualizedBy, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
}
private void PopulatePlannedActualColumns(XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder builder, ActualWorksheet item, bool actual = false)
{
switch (item.MediaType.MediaClass)
{
case (int)MediaClass.Print:
case (int)MediaClass.Newspaper:
case (int)MediaClass.ReachBased:
if (!actual)
{
//visible
var excelinfo = new ExcelColumnInfo(item.PlannedProduction, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
//unvisible
excelinfo = new ExcelColumnInfo(item.PlannedImpressions, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedClicks, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedLeads, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedRichMedia, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedAdServing, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
}
else
{
var excelinfo = new ExcelColumnInfo(item.ActualProduction, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualImpressions, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualClicks, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualLeads, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualRichMedia, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualAdServing, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
}
break;
case (int)MediaClass.Interactive:
if (!actual)
{
var excelinfo = new ExcelColumnInfo(item.PlannedProduction, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedImpressions, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedClicks, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedLeads, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedRichMedia, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedAdServing, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
}
else
{
var excelinfo = new ExcelColumnInfo(item.ActualProduction, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualImpressions, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualClicks, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualLeads, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualRichMedia, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualAdServing, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
}
break;
default:
if (!actual)
{
var excelinfo = new ExcelColumnInfo(item.PlannedProduction, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedImpressions, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedClicks, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedLeads, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedRichMedia, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedAdServing, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
}
else
{
var excelinfo = new ExcelColumnInfo(item.ActualProduction, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualImpressions, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualClicks, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualLeads, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualRichMedia, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualAdServing, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
}
break;
}
}
