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