public TakeRateDataContext(string cdsId) : base(cdsId)
{
_documentDataStore = new OXODocDataStore(cdsId);
_takeRateDataStore = new TakeRateDataStore(cdsId);
_marketGroupDataStore = new MarketGroupDataStore(cdsId);
_programmeDataStore = new ProgrammeDataStore(cdsId);
}
public TakeRateDataContext(string cdsId) : base(cdsId)
{
_documentDataStore = new OXODocDataStore(cdsId);
_takeRateDataStore = new TakeRateDataStore(cdsId);
_marketGroupDataStore = new MarketGroupDataStore(cdsId);
_programmeDataStore = new ProgrammeDataStore(cdsId);
}
public DataRowCollection ItemDataCol(OXODoc doc, OXOSection section, string mode, int objectId, string modelIds)
{
OXODocDataStore ds = new OXODocDataStore("system");
var retVal = ds.OXODocGetItemDataCol(doc.VehicleMake, doc.Id, doc.ProgrammeId, section.ToString(), mode, objectId, modelIds);
return(retVal);
}
public IEnumerable <OXODataItemHistory> ItemDataHistory(OXODoc doc, OXOSection section, int modelId, int marketgroupId, int marketId, int featureId)
{
OXODocDataStore ds = new OXODocDataStore("system");
var retVal = ds.OXODocGetItemDataHistory(doc.Id, section.ToString(), modelId, marketgroupId, marketId, featureId);
return(retVal);
}
public MarketDataContext(string cdsId)
: base(cdsId)
{
_marketDataStore = new MarketDataStore(cdsId);
_modelDataStore = new ModelDataStore(cdsId);
_marketGroupDataStore = new MarketGroupDataStore(cdsId);
_documentDataStore = new OXODocDataStore(cdsId);
}
public MarketDataContext(string cdsId)
: base(cdsId)
{
_marketDataStore = new MarketDataStore(cdsId);
_modelDataStore = new ModelDataStore(cdsId);
_marketGroupDataStore = new MarketGroupDataStore(cdsId);
_documentDataStore = new OXODocDataStore(cdsId);
}
public VehicleDataContext(string cdsId) : base(cdsId)
{
_vehicleDataStore = new VehicleDataStore(cdsId);
_documentDataStore = new OXODocDataStore(cdsId);
_programmeDataStore = new ProgrammeDataStore(cdsId);
_volumeDataStore = new TakeRateDataStore(cdsId);
_modelDataStore = new ModelDataStore(cdsId);
_marketDataStore = new MarketDataStore(cdsId);
_marketGroupDataStore = new MarketGroupDataStore(cdsId);
_bodyDataStore = new ModelBodyDataStore(cdsId);
_trimDataStore = new ModelTrimDataStore(cdsId);
_engineDataStore = new ModelEngineDataStore(cdsId);
_transmissionDataStore = new ModelTransmissionDataStore(cdsId);
_vehicleDataStore = new VehicleDataStore(cdsId);
_featureDataStore = new FeatureDataStore(cdsId);
_derivativeDataStore = new DerivativeDataStore(cdsId);
}
public VehicleDataContext(string cdsId) : base(cdsId)
{
_vehicleDataStore = new VehicleDataStore(cdsId);
_documentDataStore = new OXODocDataStore(cdsId);
_programmeDataStore = new ProgrammeDataStore(cdsId);
_volumeDataStore = new TakeRateDataStore(cdsId);
_modelDataStore = new ModelDataStore(cdsId);
_marketDataStore = new MarketDataStore(cdsId);
_marketGroupDataStore = new MarketGroupDataStore(cdsId);
_bodyDataStore = new ModelBodyDataStore(cdsId);
_trimDataStore = new ModelTrimDataStore(cdsId);
_engineDataStore = new ModelEngineDataStore(cdsId);
_transmissionDataStore = new ModelTransmissionDataStore(cdsId);
_vehicleDataStore = new VehicleDataStore(cdsId);
_featureDataStore = new FeatureDataStore(cdsId);
_derivativeDataStore = new DerivativeDataStore(cdsId);
}
public static XLWorkbook GenerateExcelMBM(XLWorkbook workbook, int progid, int docid, string cdsid, OXODoc OXODoc, OXODocDataStore ods, IEnumerable<Model> carModels, bool popDoc)
{
Stopwatch stopWatch = new Stopwatch();
try
{
stopWatch.Reset();
stopWatch.Start();
IXLWorksheet worksheet = workbook.Worksheets.Add("Model Market Matrix");
worksheet.Protect("Password123")
.SetFormatColumns()
.SetFormatRows();
worksheet.Style
.Fill.SetBackgroundColor(XLColor.White)
.Font.SetFontSize(11)
.Font.SetFontName("Arial");
worksheet.TabColor = XLColor.Orange;
// car models
string modelIds = string.Join(",", carModels.Select(p => string.Format("[{0}]", p.Id.ToString())));
int modelCount = carModels.Count();
// data
string make = OXODoc.VehicleMake;
var OXOData = ods.OXODocGetItemData(make, docid, progid, "MBM", "mbm", -1, modelIds);
int rowCount = OXOData.Count();
// white page title
worksheet.Cell("A1").Value = "DERIVATIVE AVAILABILITY";
worksheet.Cell("A1").Hyperlink = new XLHyperlink("'Cover Sheet'!A20");
worksheet.Cell("A1").Style.Font.SetUnderline(XLFontUnderlineValues.Single);
worksheet.Cell("A2").Value = (OXODoc.VehicleName + " (" + OXODoc.VehicleAKA + ") " + OXODoc.ModelYear + " " + OXODoc.Gateway + " V" + OXODoc.VersionId + " " + OXODoc.Status).ToUpper();
worksheet.Cell("A3").Value = DateTime.Now;
worksheet.Cell("A4").Value = cdsid.ToUpper();
worksheet.Range("A1:A4").Style
.Font.SetBold()
.Font.SetFontSize(14)
.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
// grey derivative title
worksheet.Cell("B1").Value = "BODY STYLE";
worksheet.Cell("B2").Value = "ENGINE";
worksheet.Cell("B3").Value = "DERIVATIVE";
worksheet.Cell("B4").Value = "MODEL CODE";
worksheet.Cell("B5").Value = "TRIM LEVEL";
worksheet.Cell("B6").Value = "DERIVATIVE PACK CODE";
worksheet.Range("B1:B6").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right)
.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
worksheet.Range(1, 2, 6, modelCount + 3).Style.Font.SetBold(true)
.Fill.SetBackgroundColor(XLColor.LightGray);
// heading row - 7
worksheet.Cell("A7").Value = "MARKET";
worksheet.Cell("B7").Value = "PAR CODE";
worksheet.Cell("B7").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
worksheet.Range(7, 1, 7, modelCount + 3)
.Style.Font.SetBold(true)
.Font.SetFontColor(XLColor.White)
.Fill.SetBackgroundColor(XLColor.Black)
.Alignment.Vertical = XLAlignmentVerticalValues.Center;
// car models heading
int col = 4; // column D
worksheet.Range(1, col, 7, col + modelCount - 1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
foreach (var carModel in carModels)
{
string KD = carModel.KD ? "KD" : "";
worksheet.Cell(1, col).Value = carModel.Doors + " " + carModel.Wheelbase + " " + carModel.BodyShape;
worksheet.Cell(2, col).Value = carModel.EngineSize + " " + carModel.Cylinder + " " + carModel.Turbo + " " + carModel.Power;
worksheet.Cell(3, col).Value = carModel.TrimName;
worksheet.Cell(4, col).Value = carModel.BMC;
worksheet.Cell(5, col).Value = carModel.TrimLevel + KD;
worksheet.Cell(6, col).Value = carModel.DPCK;
col = col + 1;
}
// output data
int row = 8;
string groupName = "";
string subGroupName = "";
foreach (var item in OXOData)
{
// market grouping
if (groupName != item[1].ToString())
{
worksheet.Cell("A" + row).SetValue(item[1].ToString().ToUpper())
.Style.Font.SetFontColor(XLColor.White)
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left)
.Alignment.SetIndent(2)
.Font.SetBold(true);
worksheet.Range(row, 1, row, modelCount + 3).Style.Fill.SetBackgroundColor(XLColor.Black);
row = row + 1;
subGroupName = "";
}
groupName = item[1].ToString();
// market sub-grouping
if (subGroupName != item[8].ToString())
{
worksheet.Cell("A" + row).SetValue(item[8].ToString().ToUpper())
.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left)
.Alignment.SetIndent(4)
.Font.SetBold(true);
worksheet.Range(row, 1, row, modelCount + 3).Style.Fill.SetBackgroundColor(XLColor.LightGray);
row = row + 1;
}
subGroupName = item[8].ToString();
worksheet.Cell("A" + row).Value = item[3];
worksheet.Cell("A" + row).Style.Alignment.SetIndent(6);
worksheet.Cell("B" + row).Value = item[2];
if (popDoc == true)
{
for (var n = 0; n < modelCount; n++)
{
int j = n + 10;
if (String.IsNullOrEmpty("" + item[j]))
{
worksheet.Cell(row, j - 6).Value = "NO";
worksheet.Cell(row, j - 6).Style.Font.SetFontColor(XLColor.Red);
}
else
{
worksheet.Cell(row, j - 6).Value = "YES";
}
}
}
row = row + 1;
}
// center align data columns
worksheet.Range(10, 2, row, modelCount + 3).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
// allow car model headings to wrap
worksheet.Range(1, 4, 6, modelCount + 3).Style.Alignment.SetWrapText();
// black vertical band
worksheet.Range(1, 3, worksheet.LastRowUsed().RowNumber(), 3).Style
.Font.SetFontColor(XLColor.White)
.Fill.SetBackgroundColor(XLColor.Black);
// set border styles
worksheet.RangeUsed().Style
.Border.SetTopBorder(XLBorderStyleValues.Thin)
.Border.SetRightBorder(XLBorderStyleValues.Thin)
.Border.SetOutsideBorder(XLBorderStyleValues.Thin);
worksheet.Range("A1:A6").Style
.Border.SetTopBorder(XLBorderStyleValues.None)
.Border.SetRightBorder(XLBorderStyleValues.None)
.Border.SetOutsideBorder(XLBorderStyleValues.Thin);
// adjust column widths
worksheet.Columns().AdjustToContents();
worksheet.Column(3).Width = 8;
worksheet.Columns(4, modelCount + 7).Width = 14;
// split the screen
worksheet.SheetView.Freeze(7, 2);
stopWatch.Stop();
var executionTime = stopWatch.ElapsedMilliseconds;
}
catch (Exception ex)
{
Log.Error(ex);
throw;
}
return workbook;
}
public void GetConfiguration(OXODoc doc)
{
var ds = new OXODocDataStore("system");
ds.DocGetConfiguration(doc);
}
public DocumentDataContext(string cdsId) : base(cdsId)
{
_documentDataStore = new OXODocDataStore(cdsId);
_programmeFileDataStore = new OXOProgrammeFileDataStore(cdsId);
}
public IEnumerable<OXODataItemHistory> ItemDataHistory(OXODoc doc, OXOSection section, int modelId, int marketgroupId, int marketId, int featureId)
{
OXODocDataStore ds = new OXODocDataStore("system");
var retVal = ds.OXODocGetItemDataHistory(doc.Id, section.ToString(), modelId, marketgroupId, marketId, featureId);
return retVal;
}
public static XLWorkbook GenerateExcelChangeSet(int progid, int docid, string cdsid)
{
Stopwatch stopWatch = new Stopwatch();
XLWorkbook workbook = new XLWorkbook();
try
{
stopWatch.Reset();
stopWatch.Start();
IXLWorksheet worksheet = workbook.Worksheets.Add("Change Set");
worksheet.Protect("Password123")
.SetFormatColumns()
.SetFormatRows();
worksheet.Style
.Fill.SetBackgroundColor(XLColor.White)
.Font.SetFontSize(11)
.Font.SetFontName("Arial");
worksheet.TabColor = XLColor.Orange;
// data
OXODocDataStore ods = new OXODocDataStore(cdsid);
var OXODoc = ods.OXODocGet(docid, progid);
ChangeSetDataStore cds = new ChangeSetDataStore(cdsid);
var changeSet = cds.ChangeSetDetailDownload(docid, progid);
// page title
worksheet.Cell("A1").Value = "CHANGE SET";
worksheet.Cell("A2").Value = (OXODoc.VehicleName + " (" + OXODoc.VehicleAKA + ") " + OXODoc.ModelYear + " " + OXODoc.Status + " " + OXODoc.Gateway + " V" + OXODoc.VersionId).ToUpper();
worksheet.Cell("A3").Value = DateTime.Now;
worksheet.Cell("A4").Value = cdsid.ToUpper();
worksheet.Range("A1:A4").Style.Font.SetBold()
.Font.SetFontSize(14)
.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
// heading row
worksheet.Cell("A6").Value = "Set Id";
worksheet.Column("A").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
worksheet.Cell("B6").Value = "Version Id";
worksheet.Column("B").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell("C6").Value = "Reminder";
worksheet.Cell("D6").Value = "Updated By";
worksheet.Column("D").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell("E6").Value = "Last Updated";
worksheet.Column("E").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
worksheet.Cell("F6").Value = "Market Name";
worksheet.Cell("G6").Value = "Model Name";
worksheet.Cell("H6").Value = "Feature Name";
worksheet.Cell("I6").Value = "Prev. Fitment";
worksheet.Column("I").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell("J6").Value = "Curr. Fitment";
worksheet.Column("J").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Range("A6:J6")
.Style.Font.SetBold(true)
.Font.SetFontColor(XLColor.White)
.Fill.SetBackgroundColor(XLColor.Black)
.Alignment.Vertical = XLAlignmentVerticalValues.Center;
// output data
int row = 7;
foreach (var item in changeSet)
{
worksheet.Cell(row, 1).Value = "'" + item.ChangeSetLabel;
worksheet.Cell(row, 2).Value = item.VersionLabel;
worksheet.Cell(row, 3).Value = item.Reminder;
worksheet.Cell(row, 4).Value = item.UpdatedBy;
worksheet.Cell(row, 5).Value = item.LastUpdatedLabel;
worksheet.Cell(row, 6).Value = item.MarketName;
worksheet.Cell(row, 7).Value = item.ModelName;
worksheet.Cell(row, 8).Value = item.FeatureName;
worksheet.Cell(row, 9).Value = item.PrevFitment;
worksheet.Cell(row, 10).Value = item.CurrFitment;
row = row + 1;
}
// adjust column widths
worksheet.Columns().AdjustToContents();
worksheet.Column(1).Width = 14;
worksheet.Column(2).Width = 12;
worksheet.Column(3).Width = 50;
worksheet.Column(3).Style.Alignment.SetWrapText();
worksheet.Column(4).Width = 13;
worksheet.Column(5).Width = 17;
worksheet.Column(6).Width = 20;
worksheet.Column(7).Width = 40;
worksheet.Column(7).Style.Alignment.SetWrapText();
worksheet.Column(8).Width = 40;
worksheet.Column(8).Style.Alignment.SetWrapText();
worksheet.Column(9).Width = 16;
worksheet.Column(10).Width = 16;
// set border styles
worksheet.Range(6, 1, worksheet.LastRowUsed().RowNumber(), 10).Style
.Border.SetTopBorder(XLBorderStyleValues.Thin)
.Border.SetRightBorder(XLBorderStyleValues.Thin)
.Border.SetOutsideBorder(XLBorderStyleValues.Thin);
// split the screen
worksheet.SheetView.Freeze(6, 0);
stopWatch.Stop();
var executionTime = stopWatch.ElapsedMilliseconds;
}
catch (Exception ex)
{
Log.Error(ex);
throw;
}
return workbook;
}
public static XLWorkbook GenerateExcelOXO(int progid, int docid, string cdsid, bool popDoc)
{
Stopwatch stopWatch = new Stopwatch();
DateTime start = DateTime.Now;
XLWorkbook workbook = new XLWorkbook();
try
{
stopWatch.Reset();
stopWatch.Start();
// data
OXODocDataStore ods = new OXODocDataStore(cdsid);
var OXODoc = ods.OXODocGet(docid, progid);
string make = OXODoc.VehicleMake;
// car models
ModelDataStore mds = new ModelDataStore(cdsid);
var carModels = mds.ModelGetMany(new ProgrammeFilter() { ProgrammeId = progid, DocumentId = docid });
string modelIds = string.Join(",", carModels.Select(p => string.Format("[{0}]", p.Id.ToString())));
int modelCount = carModels.Count();
// cover sheet
GenerateExcelCoverSheet(workbook, progid, docid, cdsid, OXODoc, popDoc);
workbook.Worksheet("Cover Sheet").Cell("L1").Value = "Finished Cover Sheet: " + DateTime.Now;
// change log
workbook.Worksheet("Cover Sheet").Cell("K2").Value = "Creating Change Log: " + DateTime.Now;
GenerateExcelChangeLog(workbook, docid, cdsid, OXODoc);
workbook.Worksheet("Cover Sheet").Cell("L2").Value = "Finished Change Log: " + DateTime.Now;
// model market matrix
workbook.Worksheet("Cover Sheet").Cell("K3").Value = "Creating Model Market Matrix: " + DateTime.Now;
GenerateExcelMBM(workbook, progid, docid, cdsid, OXODoc, ods, carModels, popDoc);
workbook.Worksheet("Cover Sheet").Cell("L3").Value = "Finished Model Market Matrix: " + DateTime.Now;
// reasons for rules
workbook.Worksheet("Cover Sheet").Cell("K4").Value = "Creating Reasons for Rules: " + DateTime.Now;
GenerateExcelRFR(workbook, progid, cdsid, OXODoc);
workbook.Worksheet("Reasons for Rules").Cell("A1").Hyperlink = new XLHyperlink("'Cover Sheet'!A21");
workbook.Worksheet("Cover Sheet").Cell("L4").Value = "Finished Reasons for Rules: " + DateTime.Now;
// global standard features
workbook.Worksheet("Cover Sheet").Cell("K5").Value = "Creating Global Standard Features: " + DateTime.Now;
GenerateExcelGSF(workbook, progid, docid, cdsid, OXODoc, ods, popDoc);
workbook.Worksheet("Global Standard Features").Cell("A1").Hyperlink = new XLHyperlink("'Cover Sheet'!A22");
workbook.Worksheet("Cover Sheet").Cell("L5").Value = "Finished Global Standard Features: " + DateTime.Now;
// world at a glance
workbook.Worksheet("Cover Sheet").Cell("K6").Value = "Creating World At A Glance: " + DateTime.Now;
GenerateExcelFBM(workbook, "World At A Glance", "Global Generic", progid, docid, "g", -1, cdsid, OXODoc, ods, carModels, "f", popDoc);
workbook.Worksheet("World At A Glance").Cell("A1").Hyperlink = new XLHyperlink("'Cover Sheet'!A23");
workbook.Worksheet("Cover Sheet").Cell("A23").Hyperlink = new XLHyperlink("'World At A Glance'!A1");
workbook.Worksheet("Cover Sheet").Cell("L6").Value = "Finished World At A Glance (core): " + DateTime.Now;
// regions vs global generic - region variance
workbook.Worksheet("Cover Sheet").Cell("K7").Value = "Creating Regions vs Global Generic: " + DateTime.Now;
GenerateExcelFBM(workbook, "Regions vs Global Generic", "Global Generic", progid, docid, "g", -1, cdsid, OXODoc, ods, carModels, "f", popDoc);
workbook.Worksheet("Regions vs Global Generic").Cell("A1").Hyperlink = new XLHyperlink("'Cover Sheet'!A25");
workbook.Worksheet("Cover Sheet").Cell("A25").Hyperlink = new XLHyperlink("'Regions vs Global Generic'!A1");
workbook.Worksheet("Cover Sheet").Cell("L7").Value = "Finished Regions vs Global Generic (core): " + DateTime.Now;
MarketGroupDataStore mg = new MarketGroupDataStore(cdsid);
var marketGroups = mg.MarketGroupGetMany(progid, docid, true);
int logRow = 8;
int row = 25;
int groupCol = modelCount + 8;
foreach (var marketGroup in marketGroups)
{
OXODocDataStore ds = new OXODocDataStore("system");
int availCount = ds.OXODocAvailableModelsByMarketGroup(progid, docid, marketGroup.Id).Where(p => p.Available == true).Count();
if (availCount > 0)
{
int marketCol = modelCount + 8;
string groupSheetName = marketGroup.GroupName + " Variance";
workbook.Worksheet("Cover Sheet").Cell(logRow, 11).Value = "Creating " + groupSheetName + ": " + DateTime.Now;
GenerateExcelFBM(workbook, groupSheetName, marketGroup.GroupName, progid, docid, "mg", marketGroup.Id, cdsid, OXODoc, ods, carModels, "v", popDoc);
workbook.Worksheet("Cover Sheet").Cell(logRow, 12).Value = "Finished " + groupSheetName + " : " + DateTime.Now;
workbook.Worksheet(groupSheetName).Cell("A1").Hyperlink = new XLHyperlink("'Cover Sheet'!A" + row);
// copy the market group to Regions vs Global Generic
workbook.Worksheet("Regions vs Global Generic").Cell(1, groupCol).Value = workbook.Worksheet(groupSheetName).Range(1, 7, workbook.Worksheet(groupSheetName).LastRowUsed().RowNumber(), modelCount + 7);
workbook.Worksheet("Regions vs Global Generic").Column(groupCol).Width = 8;
workbook.Worksheet("Regions vs Global Generic").Columns(groupCol + 1, groupCol + modelCount + 1).Width = 14;
// commented out as you can't un-group on a protected worksheet
//workbook.Worksheet("Regions vs Global Generic").Columns(groupCol + 1, groupCol + modelCount).Group();
// delete the group variance sheet as it's no longer required
workbook.Worksheet(groupSheetName).Delete();
// hide derivatives with no availability
HideNoDerivativeColumn(workbook.Worksheet("Regions vs Global Generic"), groupCol + 1);
groupCol = groupCol + modelCount + 1;
logRow = logRow + 1;
row = row + 1;
}
}
workbook.Worksheet("Cover Sheet").Cell(logRow, 12).Value = "Finished Regions vs Global Generic (extended): " + DateTime.Now;
// markets vs market group - variance
logRow = logRow + 1;
row = 26;
groupCol = modelCount + 8;
foreach (var marketGroup in marketGroups)
{
OXODocDataStore ds = new OXODocDataStore("system");
int availGroupCount = ds.OXODocAvailableModelsByMarketGroup(progid, docid, marketGroup.Id).Where(p => p.Available == true).Count();
if (availGroupCount > 0)
{
int marketCol = modelCount + 8;
string groupSheetName = "Markets vs " + marketGroup.GroupName;
workbook.Worksheet("Cover Sheet").Cell(logRow, 11).Value = "Creating " + groupSheetName + ": " + DateTime.Now;
GenerateExcelFBM(workbook, groupSheetName, marketGroup.GroupName, progid, docid, "mg", marketGroup.Id, cdsid, OXODoc, ods, carModels, "f", popDoc);
// hide derivatives with no availability
HideNoDerivativeColumn(workbook.Worksheet(groupSheetName), 8);
workbook.Worksheet("Cover Sheet").Cell(logRow, 12).Value = "Finished " + groupSheetName + " (core): " + DateTime.Now;
workbook.Worksheet(groupSheetName).Cell("A1").Hyperlink = new XLHyperlink("'Cover Sheet'!A" + row);
// generate hyperlinks
workbook.Worksheet("Cover Sheet").Cell("A" + row).Value = groupSheetName;
workbook.Worksheet("Cover Sheet").Cell("B" + row).Value = "Details the specification variance at market level versus the region generic specification";
workbook.Worksheet("Cover Sheet").Cell("A" + row).Hyperlink = new XLHyperlink("'" + groupSheetName + "'!A1");
groupCol = groupCol + modelCount + 1;
logRow = logRow + 1;
row = row + 1;
var markets = marketGroup.Markets;
// markets
foreach (var market in markets)
{
int availMarketCount = ds.OXODocAvailableModelsByMarket(progid, docid, market.Id).Where(p => p.Available == true).Count();
if (availMarketCount > 0)
{
string marketSheetName = market.Name + " Variance";
workbook.Worksheet("Cover Sheet").Cell(logRow, 11).Value = "Creating " + marketSheetName + ": " + DateTime.Now;
GenerateExcelFBM(workbook, marketSheetName, market.Name, progid, docid, "m", market.Id, cdsid, OXODoc, ods, carModels, "v", popDoc);
// seperate KD models
int kdCount = ds.OXODocAvailableModelsByMarket(progid, docid, market.Id).Where(p => p.Available == true && p.KD == true).Count();
int kdSplit = 0;
if (kdCount > 0)
{
SplitKD(workbook.Worksheet(marketSheetName), market.Name, 8);
kdSplit = 1;
}
// copy the market to the market group
if (kdCount > 0)
{
workbook.Worksheet(groupSheetName).Cell(1, marketCol).Value = workbook.Worksheet(marketSheetName).Range(1, 7, workbook.Worksheet(marketSheetName).LastRowUsed().RowNumber(), modelCount + 7 + kdSplit);
workbook.Worksheet(groupSheetName).Column(marketCol).Width = 8;
workbook.Worksheet(groupSheetName).Columns(marketCol + 1, marketCol + modelCount + 1).Width = 14;
workbook.Worksheet(groupSheetName).Column(marketCol + modelCount - kdCount + 1).Width = 8;
// commented out as you can't un-group on a protected worksheet
// workbook.Worksheet(groupSheetName).Columns(marketCol + 1, marketCol + modelCount - kdCount).Group();
// workbook.Worksheet(groupSheetName).Columns(marketCol + 1 + modelCount - kdCount + 1, marketCol + 1 + modelCount).Group();
}
else
{
workbook.Worksheet(groupSheetName).Cell(1, marketCol).Value = workbook.Worksheet(marketSheetName).Range(1, 7, workbook.Worksheet(marketSheetName).LastRowUsed().RowNumber(), modelCount + 7);
workbook.Worksheet(groupSheetName).Column(marketCol).Width = 8;
workbook.Worksheet(groupSheetName).Columns(marketCol + 1, marketCol + modelCount + 1).Width = 14;
// commented out as you can't un-group on a protected worksheet
// workbook.Worksheet(groupSheetName).Columns(marketCol + 1, marketCol + modelCount).Group();
}
// delete the market variance sheet as it's no longer required
workbook.Worksheet(marketSheetName).Delete();
// hide derivatives with no availability
HideNoDerivativeColumn(workbook.Worksheet(groupSheetName), marketCol + 1);
marketCol = marketCol + modelCount + kdSplit + 1;
workbook.Worksheet("Cover Sheet").Cell(logRow, 12).Value = "Finished " + marketSheetName + ": " + DateTime.Now;
logRow = logRow + 1;
}
}
workbook.Worksheet("Cover Sheet").Cell(logRow, 12).Value = "Finished " + groupSheetName + " (extended): " + DateTime.Now;
logRow = logRow + 1;
}
}
workbook.Worksheet("Cover Sheet").Range(26, 1, row, 1).Style
.Alignment.SetIndent(6)
.Font.SetBold(true);
row = row + 2;
workbook.Worksheet("Cover Sheet").Cell(row, 1).Value = "Global Generic";
workbook.Worksheet("Cover Sheet").Cell(row, 2).Value = "Details the full specifications for the global generic";
row = row + 1;
workbook.Worksheet("Cover Sheet").Cell(row, 1).Value = "Region X in Full";
workbook.Worksheet("Cover Sheet").Cell(row, 2).Value = "Details the full specifications for the generic region";
row = row + 1;
workbook.Worksheet("Cover Sheet").Cell(row, 1).Value = "Market";
workbook.Worksheet("Cover Sheet").Cell(row, 2).Value = "Details the full specifications for the individual market";
row = row + 3;
workbook.Worksheet("Cover Sheet").Cell(row, 1).Value = "Quick links to full specifications ;";
workbook.Worksheet("Cover Sheet").Range(row - 5, 1, row, 1).Style
.Alignment.SetIndent(3)
.Font.SetBold(true);
row = row + 2;
int startRow = row;
// global generic
workbook.Worksheet("Cover Sheet").Cell(logRow, 11).Value = "Creating Global Generic: " + DateTime.Now;
GenerateExcelFBM(workbook, "Global Generic", "Global Generic", progid, docid, "g", -1, cdsid, OXODoc, ods, carModels, "f", popDoc);
workbook.Worksheet("Global Generic").Cell("A1").Hyperlink = new XLHyperlink("'Cover Sheet'!A" + row);
workbook.Worksheet("Cover Sheet").Cell(logRow, 12).Value = "Finished Global Generic: " + DateTime.Now;
workbook.Worksheet("Cover Sheet").Cell(row, 1).Value = "Global Generic";
workbook.Worksheet("Cover Sheet").Cell(row, 1).Hyperlink = new XLHyperlink("'Global Generic'!A1");
logRow = logRow + 1;
row = row + 1;
// markets vs market groups - in full
groupCol = modelCount + 8;
foreach (var marketGroup in marketGroups)
{
OXODocDataStore ds = new OXODocDataStore("system");
int availCount = ds.OXODocAvailableModelsByMarketGroup(progid, docid, marketGroup.Id).Where(p => p.Available == true).Count();
if (availCount > 0)
{
int marketCol = modelCount + 8;
string groupSheetName = marketGroup.GroupName + " in Full";
workbook.Worksheet("Cover Sheet").Cell(logRow, 11).Value = "Creating " + groupSheetName + ": " + DateTime.Now;
GenerateExcelFBM(workbook, groupSheetName, marketGroup.GroupName, progid, docid, "mg", marketGroup.Id, cdsid, OXODoc, ods, carModels, "f", popDoc);
workbook.Worksheet("Cover Sheet").Cell(logRow, 12).Value = "Finished " + groupSheetName + " (core): " + DateTime.Now;
workbook.Worksheet(groupSheetName).Cell("A1").Hyperlink = new XLHyperlink("'Cover Sheet'!A" + row);
// copy the market group to world at a glance
workbook.Worksheet("World At A Glance").Cell(1, groupCol).Value = workbook.Worksheet(groupSheetName).Range(1, 7, workbook.Worksheet(groupSheetName).LastRowUsed().RowNumber(), modelCount + 7);
workbook.Worksheet("World At A Glance").Column(groupCol).Width = 8;
workbook.Worksheet("World At A Glance").Columns(groupCol + 1, groupCol + modelCount + 1).Width = 14;
// commented out as you can't un-group on a protected worksheet
// workbook.Worksheet("World At A Glance").Columns(groupCol + 1, groupCol + modelCount).Group();
// hide derivatives with no availability
HideNoDerivativeColumn(workbook.Worksheet("World At A Glance"), groupCol + 1);
// generate hyperlinks
workbook.Worksheet("Cover Sheet").Cell("A" + row).Value = groupSheetName;
workbook.Worksheet("Cover Sheet").Cell("A" + row).Hyperlink = new XLHyperlink("'" + groupSheetName + "'!A1");
groupCol = groupCol + modelCount + 1;
logRow = logRow + 1;
row = row + 1;
var markets = marketGroup.Markets;
// markets
foreach (var market in markets)
{
int availMarketCount = ds.OXODocAvailableModelsByMarket(progid, docid, market.Id).Where(p => p.Available == true).Count();
if (availMarketCount > 0)
{
string marketSheetName = market.Name;
workbook.Worksheet("Cover Sheet").Cell(logRow, 11).Value = "Creating " + marketSheetName + ": " + DateTime.Now;
workbook.Worksheet("Cover Sheet").Cell("B" + row).Value = marketSheetName;
workbook.Worksheet("Cover Sheet").Cell("B" + row).Hyperlink = new XLHyperlink("'" + marketSheetName + "'!A1");
GenerateExcelFBM(workbook, marketSheetName, market.Name, progid, docid, "m", market.Id, cdsid, OXODoc, ods, carModels, "f", popDoc);
workbook.Worksheet(marketSheetName).Cell("A1").Hyperlink = new XLHyperlink("'Cover Sheet'!B" + row);
// seperate KD models
int kdSplit = 0;
int kdCount = ds.OXODocAvailableModelsByMarket(progid, docid, market.Id).Where(p => p.Available == true && p.KD == true).Count();
if (kdCount > 0)
{
SplitKD(workbook.Worksheet(marketSheetName), market.Name, 8);
kdSplit = 1;
}
// hide derivatives with no availability
HideNoDerivativeColumn(workbook.Worksheet(marketSheetName), 8);
// copy the market to the market group
if (kdCount > 0)
{
workbook.Worksheet(groupSheetName).Cell(1, marketCol).Value = workbook.Worksheet(marketSheetName).Range(1, 7, workbook.Worksheet(marketSheetName).LastRowUsed().RowNumber(), modelCount + 7 + kdSplit);
workbook.Worksheet(groupSheetName).Column(marketCol).Width = 8;
workbook.Worksheet(groupSheetName).Columns(marketCol + 1, marketCol + modelCount + 1).Width = 14;
workbook.Worksheet(groupSheetName).Column(marketCol + modelCount - kdCount + 1).Width = 8;
// commented out as you can't un-group on a protected worksheet
// workbook.Worksheet(groupSheetName).Columns(marketCol + 1, marketCol + modelCount - kdCount).Group();
// workbook.Worksheet(groupSheetName).Columns(marketCol + 1 + modelCount - kdCount + 1, marketCol + 1 + modelCount).Group();
}
else
{
workbook.Worksheet(groupSheetName).Cell(1, marketCol).Value = workbook.Worksheet(marketSheetName).Range(1, 7, workbook.Worksheet(marketSheetName).LastRowUsed().RowNumber(), modelCount + 7);
workbook.Worksheet(groupSheetName).Column(marketCol).Width = 8;
workbook.Worksheet(groupSheetName).Columns(marketCol + 1, marketCol + modelCount + 1).Width = 14;
// commented out as you can't un-group on a protected worksheet
// workbook.Worksheet(groupSheetName).Columns(marketCol + 1, marketCol + modelCount).Group();
}
// hide derivatives with no availability
HideNoDerivativeColumn(workbook.Worksheet(groupSheetName), marketCol + 1);
marketCol = marketCol + modelCount + kdSplit + 1;
workbook.Worksheet("Cover Sheet").Cell(logRow, 12).Value = "Finished " + marketSheetName + ": " + DateTime.Now;
logRow = logRow + 1;
row = row + 1;
}
}
// hide derivatives with no availability
HideNoDerivativeColumn(workbook.Worksheet(groupSheetName), 8);
workbook.Worksheet("Cover Sheet").Cell(logRow, 12).Value = "Finished " + groupSheetName + " (extended): " + DateTime.Now;
logRow = logRow + 1;
row = row + 1;
}
}
workbook.Worksheet("Cover Sheet").Cell(logRow, 12).Value = "Finished World At A Glance (extended): " + DateTime.Now;
logRow = logRow + 1;
workbook.Worksheet("Cover Sheet").Range(startRow, 1, row, 1).Style
.Alignment.SetIndent(18)
.Font.SetBold(true);
workbook.Worksheet("Cover Sheet").Range(startRow, 2, row, 2).Style
.Alignment.SetIndent(3)
.Font.SetBold(true);
// cover sheet footer - need to do it here due to flexible number of market groups / markets
workbook.Worksheet("Cover Sheet").Cell(row + 2, 1).Value = "This file is classified as follows according to the Global Marketing GIS1 matrix";
workbook.Worksheet("Cover Sheet").Cell(row + 2, 1).Style.Font.SetBold(true);
workbook.Worksheet("Cover Sheet").Cell(row + 3, 1).Value = "Record title:";
workbook.Worksheet("Cover Sheet").Cell(row + 3, 2).Value = "Product & Marketing Brief";
workbook.Worksheet("Cover Sheet").Cell(row + 4, 1).Value = "Security Classification:";
workbook.Worksheet("Cover Sheet").Cell(row + 4, 2).Value = "CONFIDENTIAL";
workbook.Worksheet("Cover Sheet").Cell(row + 5, 1).Value = "Retention period:";
workbook.Worksheet("Cover Sheet").Cell(row + 5, 2).Value = "10 years";
workbook.Worksheet("Cover Sheet").Cell(row + 6, 1).Value = "Retention Qualifers:";
workbook.Worksheet("Cover Sheet").Cell(row + 6, 2).Value = "T";
workbook.Worksheet("Cover Sheet").Range(row + 3, 1, row + 11, 1).Style
.Alignment.SetIndent(3)
.Font.SetBold(true);
workbook.Worksheet("Cover Sheet").Columns(11, 12)
.Style.Font.SetFontSize(8)
.Font.SetFontName("Courier New");
workbook.Worksheet("Cover Sheet").Columns(11, 12).Hide();
DateTime end = DateTime.Now;
workbook.Worksheet("Cover Sheet").Cell("A1").Comment.AddText("Time to export this OxO: " + (end - start).TotalMinutes + " minutes");
stopWatch.Stop();
var executionTime = stopWatch.ElapsedMilliseconds;
}
catch (Exception ex)
{
Log.Error(ex);
throw;
}
return workbook;
}
public static XLWorkbook GenerateExcelGSF(XLWorkbook workbook, int progid, int docid, string cdsid, OXODoc OXODoc, OXODocDataStore ods, bool popDoc)
{
Stopwatch stopWatch = new Stopwatch();
try
{
stopWatch.Reset();
stopWatch.Start();
IXLWorksheet worksheet = workbook.Worksheets.Add("Global Standard Features");
worksheet.Protect("Password123")
.SetFormatColumns()
.SetFormatRows()
.SetAutoFilter();
worksheet.Style.Font.SetFontSize(11)
.Font.SetFontName("Arial");
worksheet.TabColor = XLColor.Orange;
// car models
ModelDataStore mds = new ModelDataStore(cdsid);
var carModels = mds.GSFModelGetMany(progid, docid);
string modelIds = string.Join(",", carModels.Select(p => string.Format("[{0}]", p.GSFId.ToString())));
int modelCount = carModels.Count();
// data
string make = OXODoc.VehicleMake;
var OXOData = ods.OXODocGetItemData(make, docid, progid, "GSF", "g", -1, modelIds);
int rowCount = OXOData.Count();
// page title
worksheet.Cell("A1").Value = "GLOBAL STANDARD FEATURES";
worksheet.Cell("A2").Value = (OXODoc.VehicleName + " (" + OXODoc.VehicleAKA + ") " + OXODoc.ModelYear + " " + OXODoc.Gateway + " V" + OXODoc.VersionId + " " + OXODoc.Status).ToUpper();
worksheet.Cell("A3").Value = DateTime.Now;
worksheet.Cell("A4").Value = cdsid.ToUpper();
worksheet.Range("A1:A4").Style.Font.SetBold()
.Font.SetFontSize(14)
.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
// heading row
worksheet.Cell("A5").Value = "FEATURE GROUP";
worksheet.Cell("B5").Value = "FEATURE CODE";
worksheet.Cell("C5").Value = "MARKETING FEATURE DESCRIPTION";
worksheet.Cell("D5").Value = "COMMENTS";
worksheet.Range(5, 1, 5, modelCount + 4)
.Style.Font.SetBold(true)
.Font.SetFontColor(XLColor.White)
.Fill.SetBackgroundColor(XLColor.Black)
.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Range(1, 5, 4, modelCount + 4)
.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
worksheet.Cell("D2").Value = "BODY STYLE";
worksheet.Cell("D3").Value = "ENGINE";
worksheet.Cell("D4").Value = "MODEL CODE";
worksheet.Range("D1:D4").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right)
.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
// key
worksheet.Cell("C1").Value = "Key: S = Standard Feature, NA = Not Available";
worksheet.Cell("C1").Style.Font.SetFontSize(10);
// gray top area
worksheet.Range(1, 1, 4, modelCount + 4).Style.Font.SetBold(true)
.Fill.SetBackgroundColor(XLColor.LightGray);
// car models heading
int col = 5; // column E
worksheet.Range(1, col, 4, col + modelCount - 1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Alignment.SetVertical(XLAlignmentVerticalValues.Center)
.Alignment.SetWrapText();
foreach (var carModel in carModels)
{
worksheet.Cell(2, col).Value = carModel.GSFBody;
worksheet.Cell(3, col).Value = carModel.GSFEngine;
worksheet.Cell(4, col).Value = carModel.BMC;
col = col + 1;
}
// output data
int row = 6;
string groupName = "";
foreach (var item in OXOData)
{
// feature family grouping
if (groupName != item[1].ToString())
{
worksheet.Cell("A" + row).SetValue(item[1].ToString().ToUpper())
.Style.Font.SetFontColor(XLColor.White)
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left)
.Font.SetBold(true)
.Font.SetUnderline(XLFontUnderlineValues.Single);
worksheet.Range(row, 1, row, modelCount + 4).Style.Fill.SetBackgroundColor(XLColor.Black);
row = row + 1;
}
groupName = item[1].ToString();
worksheet.Cell("A" + row).Value = item[1];
worksheet.Cell("B" + row).Value = item[3];
worksheet.Cell("C" + row).Value = item[4];
worksheet.Cell("D" + row).Value = item[6];
var columnOffSet = 9;
if (popDoc == true)
{
for (var n = 0; n < modelCount; n++)
{
var j = n + columnOffSet;
if (String.IsNullOrEmpty("" + item[j]))
{
worksheet.Cell(row, j - 4).Value = "";
}
else
{
if (item[j].ToString() == "S")
{
worksheet.Cell(row, j - 4).Value = "S";
}
else
{
worksheet.Cell(row, j - 4).Value = "NA";
worksheet.Cell(row, j - 4).Style.Fill.SetBackgroundColor(XLColor.LightGray);
}
}
}
}
row = row + 1;
}
// set border styles
worksheet.RangeUsed().Style
.Border.SetTopBorder(XLBorderStyleValues.Thin)
.Border.SetRightBorder(XLBorderStyleValues.Thin)
.Border.SetOutsideBorder(XLBorderStyleValues.Thin);
worksheet.Range("A1:D4").Style
.Border.SetTopBorder(XLBorderStyleValues.None)
.Border.SetRightBorder(XLBorderStyleValues.None)
.Border.SetOutsideBorder(XLBorderStyleValues.Thin);
// center align all of the data columns
worksheet.Range(7, 5, worksheet.LastRowUsed().RowNumber(), modelCount + 4).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
// allow car model headings to wrap
worksheet.Range(1, 5, 4, modelCount + 3).Style.Alignment.SetWrapText();
// adjust column widths
worksheet.Columns().AdjustToContents();
worksheet.Column("A").Width = 32;
worksheet.Column("B").Width = 20;
worksheet.Column("B").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
worksheet.Columns("C:D").Width = 90;
worksheet.Columns("C:D").Style.Alignment.SetWrapText();
worksheet.Columns(5, modelCount + 4).Width = 14;
// apply filter
worksheet.Range(5, 1, worksheet.LastRowUsed().RowNumber(), 1).SetAutoFilter();
// split the screen
worksheet.SheetView.Freeze(5, 2);
stopWatch.Stop();
var executionTime = stopWatch.ElapsedMilliseconds;
}
catch (Exception ex)
{
Log.Error(ex);
throw;
}
return workbook;
}
public void GetConfiguration(OXODoc doc)
{
var ds = new OXODocDataStore("system");
ds.DocGetConfiguration(doc);
}
public DocumentDataContext(string cdsId) : base(cdsId)
{
_documentDataStore = new OXODocDataStore(cdsId);
_programmeFileDataStore = new OXOProgrammeFileDataStore(cdsId);
}
public static XLWorkbook GenerateExcelFBM(XLWorkbook workbook, string sheetName, string marketName, int progid, int docid, string level, int objid, string cdsid, OXODoc OXODoc, OXODocDataStore ods, IEnumerable<Model> carModels, string option, bool popDoc)
{
Stopwatch stopWatch = new Stopwatch();
try
{
stopWatch.Reset();
stopWatch.Start();
IXLWorksheet worksheet = workbook.Worksheets.Add(sheetName);
worksheet.Protect("Password123")
.SetFormatColumns()
.SetFormatRows()
.SetAutoFilter();
worksheet.Style.Font.SetFontSize(11)
.Font.SetFontName("Arial");
var test = (level == "mg") ? worksheet.TabColor = XLColor.Blue : worksheet.TabColor = XLColor.Green;
// country code
string parCode = "-";
if (level == "m")
{
MarketDataStore mkt = new MarketDataStore(cdsid);
parCode = mkt.MarketGet(objid).PAR_X;
}
// car models
string modelIds = string.Join(",", carModels.Select(p => string.Format("[{0}]", p.Id.ToString())));
int modelCount = carModels.Count();
// data
string make = OXODoc.VehicleMake;
var OXOData = ods.OXODocGetItemData(make, docid, progid, "FBM", level, objid, modelIds, true);
int rowCount = OXOData.Count();
FeatureDataStore fds = new FeatureDataStore(cdsid);
// page title
worksheet.Cell("A1").Value = sheetName.ToUpper();
worksheet.Cell("A2").Value = (OXODoc.VehicleName + " (" + OXODoc.VehicleAKA + ") " + OXODoc.ModelYear + " " + OXODoc.Gateway + " V" + OXODoc.VersionId + " " + OXODoc.Status).ToUpper();
worksheet.Cell("A3").Value = DateTime.Now;
worksheet.Cell("A4").Value = cdsid.ToUpper();
worksheet.Range("A1:A4").Style.Font.SetBold()
.Font.SetFontSize(14)
.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
// key
worksheet.Cell("C1").Value = "Key: S = Standard Feature, O = Optional Feature, P = Feature part of Option Pack, NA = Not Available, (O) = Linked Option";
worksheet.Cell("C1").Style.Font.SetFontSize(10);
// heading row
worksheet.Cell("A10").Value = "FEATURE GROUP";
worksheet.Cell("B10").Value = "FEATURE CODE";
worksheet.Cell("C10").Value = "MARKETING FEATURE DESCRIPTION" + Environment.NewLine + "(Standard Features for all derivatives are shown in the \"Standard Fitment\" sheet)";
worksheet.Cell("D10").Value = "SYSTEM DESCRIPTION" + Environment.NewLine + "(Maximum 40 Characters)";
worksheet.Cell("E10").Value = "RULES";
worksheet.Cell("F10").Value = "COMMENTS";
worksheet.Range(10, 1, 10, modelCount + 7)
.Style.Font.SetBold(true)
.Font.SetFontColor(XLColor.White)
.Fill.SetBackgroundColor(XLColor.Black)
.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Range(1, 7, rowCount + 9, modelCount + 7)
.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
worksheet.Cell("F1").Value = "SPEC GROUP";
worksheet.Cell("F2").Value = "BODY STYLE";
worksheet.Cell("F3").Value = "ENGINE";
worksheet.Cell("F4").Value = "DERIVATIVE";
worksheet.Cell("F5").Value = "MODEL CODE";
worksheet.Cell("F6").Value = "TRIM LEVEL";
worksheet.Cell("F7").Value = "DERIVATIVE PACK CODE";
worksheet.Cell("F8").Value = "PAR CODE";
worksheet.Cell("F9").Value = "MODEL AVAILABILITY";
worksheet.Range("F1:F9").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right)
.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
// gray top area
worksheet.Range(1, 1, 9, modelCount + 7).Style.Font.SetBold(true)
.Fill.SetBackgroundColor(XLColor.LightGray);
// car models heading - column H
int col = 8;
worksheet.Cell(1, col).Value = marketName.ToString().ToUpper();
worksheet.Range(1, col, 1, col + modelCount - 1).Merge();
worksheet.Range(1, col, 1, col + modelCount - 1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
foreach (var carModel in carModels)
{
string KD = carModel.KD ? "KD" : "";
worksheet.Cell(2, col).Value = carModel.Doors + " " + carModel.Wheelbase + " " + carModel.BodyShape;
worksheet.Cell(3, col).Value = carModel.EngineSize + " " + carModel.Cylinder + " " + carModel.Turbo + " " + carModel.Power;
worksheet.Cell(4, col).Value = carModel.TrimName;
worksheet.Cell(5, col).Value = carModel.BMC;
worksheet.Cell(6, col).Value = carModel.TrimLevel + KD;
worksheet.Cell(7, col).Value = carModel.DPCK;
worksheet.Cell(8, col).Value = parCode;
worksheet.Cell(9, col).Value = "";
col = col + 1;
}
// output data
int row = 11;
string groupName = "";
foreach (var item in OXOData)
{
// feature family grouping
if (groupName != item[1].ToString())
{
worksheet.Cell("A" + row).SetValue(item[1].ToString().ToUpper())
.Style.Font.SetFontColor(XLColor.White)
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left)
.Font.SetBold(true)
.Font.SetUnderline(XLFontUnderlineValues.Single);
worksheet.Range(row, 1, row, modelCount + 7).Style.Fill.SetBackgroundColor(XLColor.Black);
row = row + 1;
}
groupName = item[1].ToString();
worksheet.Cell("A" + row).Value = item[1];
worksheet.Cell("B" + row).Value = item[3];
worksheet.Cell("C" + row).Value = item[4];
worksheet.Cell("D" + row).Value = item[7];
worksheet.Cell("F" + row).Value = item[6];
// feature rules
if (Convert.ToInt32(item[8]) > 0)
{
string ruleText = "";
Feature feature = fds.ProgrammeFeatureGet(progid, 0, Convert.ToInt32(item[5]));
if (feature != null)
{
ruleText = "" + feature.RuleText;
}
worksheet.Cell("E" + row).Value = ruleText;
// TODO : to be continued.....
// var featRules = fds.RuleToolTipGetByFeature(progid, Convert.ToInt32(item[5]));
// string newLine = Environment.NewLine;
// string output = "";
// int i = 1;
//foreach (var rule in featRules)
//{
// if (rule.RuleActive == true && rule.RuleApproved == true)
// {
// if (i < featRules.Count())
// {
// output = output + rule.RuleResponse + newLine;
// }
// else
// {
// output = output + rule.RuleResponse;
// }
// }
// i = i + 1;
//}
}
var columnOffSet = 10;
for (var n = 0; n < modelCount; n++)
{
var j = n + columnOffSet;
var k = j - 2;
if (item[j].ToString().EndsWith("***"))
{
worksheet.Cell(9, k).Value = "NO";
}
else
{
worksheet.Cell(9, k).Value = "YES";
}
if (popDoc == true)
{
switch (option) // v: variance | f: full
{
case "v":
if (String.IsNullOrEmpty("" + item[j]))
{
worksheet.Cell(row, k).Value = "";
}
else
{
if (item[j].ToString().EndsWith("*"))
{
worksheet.Cell(row, k).Value = "";
}
else
{
worksheet.Cell(row, k).Value = item[j];
}
}
break;
case "f":
if (String.IsNullOrEmpty("" + item[j]))
{
worksheet.Cell(row, k).Value = "";
}
else
{
worksheet.Cell(row, k).Value = item[j].ToString().TrimEnd('*');
}
break;
}
}
}
row = row + 1;
}
// packs headings
var packHeaderItem = ods.OXODocGetItemData(make, docid, progid, "PCK", level, objid, modelIds);
// pack features
var packDataItem = ods.OXODocGetItemData(make, docid, progid, "FPS", level, objid, modelIds);
if (packDataItem != null && packDataItem.Count() > 0)
{
// packs heading row
worksheet.Cell("A" + row).SetValue("OPTION PACKS")
.Style.Font.SetFontColor(XLColor.White)
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left)
.Font.SetBold(true)
.Font.SetUnderline(XLFontUnderlineValues.Single);
worksheet.Range(row, 1, row, modelCount + 7).Style.Fill.SetBackgroundColor(XLColor.Black);
var currentPackName = "";
var packHeader = packHeaderItem[0];
foreach (var item in packDataItem)
{
var subGroup = item["PackName"].ToString();
var columnOffSet = 7;
if (currentPackName != subGroup)
{
currentPackName = subGroup;
foreach (var pack in packHeaderItem)
{
if (pack["PackName"].ToString() == subGroup)
{
row = row + 1;
packHeader = pack;
}
}
// pack heading row
worksheet.Cell("A" + row).SetValue(item["PackName"].ToString().ToUpper());
worksheet.Cell("B" + row).SetValue(packHeader["FeatureCode"]);
worksheet.Range(row, 1, row, modelCount + 7).Style.Fill.SetBackgroundColor(XLColor.Black)
.Font.SetFontColor(XLColor.White);
if (popDoc == true)
{
switch (option) // v: variance | f: full
{
case "v":
for (var i = 0; i < modelCount; i++)
{
var j = i + columnOffSet;
var oxoCode = (packHeader[j] == null ? "" : packHeader[j].ToString());
if (oxoCode.ToString().EndsWith("*"))
{
worksheet.Cell(row, j + 1).Value = "";
}
else
{
worksheet.Cell(row, j + 1).Value = oxoCode.ToString().TrimEnd('*');
}
}
break;
case "f":
for (var i = 0; i < modelCount; i++)
{
var j = i + columnOffSet;
var oxoCode = (packHeader[j] == null ? "" : packHeader[j].ToString());
worksheet.Cell(row, j + 1).Value = oxoCode.ToString().TrimEnd('*');
}
break;
}
}
}
row = row + 1;
// pack features
worksheet.Cell("A" + row).SetValue(item["PackName"].ToString());
worksheet.Cell("B" + row).SetValue(item["FeatureCode"]);
worksheet.Cell("C" + row).SetValue(item["BrandDescription"].ToString());
worksheet.Cell("D" + row).SetValue(item["SystemDescription"].ToString());
if (popDoc == true)
{
switch (option) // v: variance | f: full
{
case "v":
for (var i = 0; i < modelCount; i++)
{
var j = i + columnOffSet;
var oxoCode = (item[j] == null ? "" : item[j].ToString());
if (oxoCode.ToString().EndsWith("*"))
{
worksheet.Cell(row, j + 1).Value = "";
}
else
{
worksheet.Cell(row, j + 1).Value = oxoCode.ToString().TrimEnd('*');
}
}
break;
case "f":
for (var i = 0; i < modelCount; i++)
{
var j = i + columnOffSet;
var oxoCode = (item[j] == null ? "" : item[j].ToString());
worksheet.Cell(row, j + 1).Value = oxoCode.ToString().TrimEnd('*');
}
break;
}
}
}
}
// market vertical black band
worksheet.Cell(1, 7).Value = marketName.ToString().ToUpper();
worksheet.Range(1, 7, 9, 7).Merge();
worksheet.Range(1, 7, worksheet.LastRowUsed().RowNumber(), 7).Style
.Font.SetBold(true)
.Font.SetFontColor(XLColor.White)
.Fill.SetBackgroundColor(XLColor.Black)
.Alignment.SetVertical(XLAlignmentVerticalValues.Bottom)
.Alignment.SetTextRotation(90);
// set border styles
worksheet.RangeUsed().Style
.Border.SetTopBorder(XLBorderStyleValues.Thin)
.Border.SetRightBorder(XLBorderStyleValues.Thin)
.Border.SetOutsideBorder(XLBorderStyleValues.Thin);
worksheet.Range("A1:F9").Style
.Border.SetTopBorder(XLBorderStyleValues.None)
.Border.SetRightBorder(XLBorderStyleValues.None)
.Border.SetOutsideBorder(XLBorderStyleValues.Thin);
// center align all of the data columns
worksheet.Range(12, 8, worksheet.LastRowUsed().RowNumber(), modelCount + 7).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
// allow car model headings to wrap
worksheet.Range(1, 8, 9, modelCount + 7).Style.Alignment.SetWrapText();
// outline the models - commented out as you can't un-group on a protected worksheet
// worksheet.Columns(8, modelCount + 7).Group();
// adjust column widths
worksheet.Column("A").Width = 36;
worksheet.Column("B").Width = 20;
worksheet.Column("B").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
worksheet.Columns("C:F").Width = 90;
worksheet.Columns("C:F").Style.Alignment.SetWrapText();
worksheet.Column("G").Width = 8;
worksheet.Columns(8, modelCount + 7).Width = 14;
// apply filter
worksheet.Range(11, 1, worksheet.LastRowUsed().RowNumber(), 1).SetAutoFilter();
// split the screen
worksheet.SheetView.Freeze(10, 3);
stopWatch.Stop();
var executionTime = stopWatch.ElapsedMilliseconds;
}
catch (Exception ex)
{
Log.Error(ex);
throw;
}
return workbook;
}
public DataRowCollection ItemDataCol(OXODoc doc, OXOSection section, string mode, int objectId, string modelIds)
{
OXODocDataStore ds = new OXODocDataStore("system");
var retVal = ds.OXODocGetItemDataCol(doc.VehicleMake, doc.Id, doc.ProgrammeId, section.ToString(), mode, objectId, modelIds);
return retVal;
}