public bool Export(OXODoc documentToExport, string comment, string PACN)
{
var retVal = false;
XLWorkbook workbook = ClosedXmlExcelGenerator.GenerateExcelOXO(documentToExport.ProgrammeId, documentToExport.Id, CDSID, true);
OXOProgrammeFile file = new OXOProgrammeFile();
file.ProgrammeId = documentToExport.ProgrammeId;
file.FileCategory = ProgrammFileCategory.Publish.ToString();
file.FileComment = comment;
file.PACN = PACN;
file.FileName = String.Format("{0} {1} {2} {3} v{4}.{5} {6}.xlsx",
documentToExport.VehicleName,
documentToExport.VehicleAKA,
documentToExport.ModelYear,
documentToExport.Gateway,
documentToExport.VersionMajor,
documentToExport.VersionMinor,
documentToExport.Status);
file.FileType = "application/vnd.open";
file.FileExt = "xlsx";
file.Gateway = documentToExport.Gateway;
MemoryStream m = new MemoryStream();
workbook.SaveAs(m);
file.FileContent = m.ToArray();
file.FileSize = file.FileContent.Length;
_programmeFileDataStore.OXOProgrammeFileSave(file);
retVal = true;
return retVal;
}
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 void DocGetConfiguration(OXODoc currentDoc)
{
Programme retVal = new Programme();
using (IDbConnection conn = DbHelper.GetDBConnection())
{
try
{
var para = new DynamicParameters();
para.Add("@p_doc_id", currentDoc.Id, dbType: DbType.Int32);
using (var multi = conn.QueryMultiple("dbo.OXO_Doc_GetConfiguration", para, commandType: CommandType.StoredProcedure))
{
currentDoc.AllBodies = multi.Read <ModelBody>().ToList();
currentDoc.AllEngines = multi.Read <ModelEngine>().ToList();
currentDoc.AllTransmissions = multi.Read <ModelTransmission>().ToList();
currentDoc.AllTrims = multi.Read <ModelTrim>().ToList();
}
}
catch (Exception ex)
{
Log.Error(ex);
throw;
}
}
}
public bool Export(OXODoc documentToExport, string comment, string PACN)
{
var retVal = false;
XLWorkbook workbook = ClosedXmlExcelGenerator.GenerateExcelOXO(documentToExport.ProgrammeId, documentToExport.Id, CDSID, true);
OXOProgrammeFile file = new OXOProgrammeFile();
file.ProgrammeId = documentToExport.ProgrammeId;
file.FileCategory = ProgrammFileCategory.Publish.ToString();
file.FileComment = comment;
file.PACN = PACN;
file.FileName = String.Format("{0} {1} {2} {3} v{4}.{5} {6}.xlsx",
documentToExport.VehicleName,
documentToExport.VehicleAKA,
documentToExport.ModelYear,
documentToExport.Gateway,
documentToExport.VersionMajor,
documentToExport.VersionMinor,
documentToExport.Status);
file.FileType = "application/vnd.open";
file.FileExt = "xlsx";
file.Gateway = documentToExport.Gateway;
MemoryStream m = new MemoryStream();
workbook.SaveAs(m);
file.FileContent = m.ToArray();
file.FileSize = file.FileContent.Length;
_programmeFileDataStore.OXOProgrammeFileSave(file);
retVal = true;
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);
}
private static Vehicle GetVehicle(IDataContext context, Vehicle forVehicle, OXODoc forDocument)
{
return((Vehicle)context.Vehicle.GetVehicle(new VehicleFilter()
{
ProgrammeId = forVehicle.ProgrammeId,
Gateway = forVehicle.Gateway,
OxoDocId = forDocument.Id,
}));
}
private static OXODoc GetOxoDocument(IDataContext context, OXODoc forOxoDocument)
{
OXODoc oxoDocument = new EmptyOxoDocument();
var cacheKey = string.Format("OxoDocument_{0}", forOxoDocument.Id);
var cachedLookup = HttpContext.Current.Cache.Get(cacheKey);
if (cachedLookup != null)
{
oxoDocument = (OXODoc)cachedLookup;
}
else
{
oxoDocument = context.Volume.GetOxoDocument(new VolumeFilter()
{
OxoDocId = forOxoDocument.Id, ProgrammeId = forOxoDocument.ProgrammeId
});
HttpContext.Current.Cache.Add(cacheKey, oxoDocument, null, DateTime.Now.AddMinutes(60), Cache.NoSlidingExpiration, CacheItemPriority.Default, null);
}
return(oxoDocument);
}
public OXODoc OXODocGet(int id, int progid)
{
OXODoc retVal = null;
using (IDbConnection conn = DbHelper.GetDBConnection())
{
try
{
var para = new DynamicParameters();
para.Add("@p_Id", id, dbType: DbType.Int32);
para.Add("@p_Programme_Id", progid, dbType: DbType.Int32);
retVal = conn.Query <OXODoc>("dbo.OXO_OXODoc_Get", para, commandType: CommandType.StoredProcedure).FirstOrDefault();
}
catch (Exception ex)
{
Log.Error(ex);
throw;
}
}
return(retVal);
}
private static async Task <OXODoc> GetOxoDocument(IDataContext context, OXODoc forOxoDocument)
{
OXODoc oxoDocument;
var cacheKey = string.Format("OxoDocument_{0}", forOxoDocument.Id);
var cachedLookup = GetCache(cacheKey);
if (cachedLookup != null)
{
oxoDocument = (OXODoc)cachedLookup;
}
else
{
oxoDocument = await context.TakeRate.GetUnderlyingOxoDocument(new TakeRateFilter()
{
DocumentId = forOxoDocument.Id, ProgrammeId = forOxoDocument.ProgrammeId
});
AddCache(cacheKey, oxoDocument);
}
return(oxoDocument);
}
public bool OXODocSave(OXODoc obj)
{
bool retVal = true;
string procName = (obj.IsNew ? "dbo.OXO_OXODoc_New" : "dbo.OXO_OXODoc_Edit");
using (IDbConnection conn = DbHelper.GetDBConnection())
{
try
{
obj.Save(this.CurrentCDSID);
var para = new DynamicParameters();
para.Add("@p_Programme_Id", obj.ProgrammeId, dbType: DbType.Int32);
para.Add("@p_Gateway", obj.Gateway, dbType: DbType.String, size: 50);
para.Add("@p_Version_Id", obj.VersionId, dbType: DbType.Decimal);
para.Add("@p_Status", obj.Status, dbType: DbType.String, size: 50);
if (obj.IsNew)
{
para.Add("@p_Created_By", obj.CreatedBy, dbType: DbType.String, size: 8);
para.Add("@p_Created_On", obj.CreatedOn, dbType: DbType.DateTime);
}
para.Add("@p_Updated_By", obj.UpdatedBy, dbType: DbType.String, size: 8);
para.Add("@p_Last_Updated", obj.LastUpdated, dbType: DbType.DateTime);
para.Add("@p_Id", obj.Id, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);
conn.Execute(procName, para, commandType: CommandType.StoredProcedure);
if (obj.Id == 0)
{
obj.Id = para.Get <int>("@p_Id");
}
}
catch (Exception ex)
{
Log.Error(ex);
throw;
}
}
return(retVal);
}
private static Market GetMarket(IDataContext context, Market forMarket, OXODoc forDocument)
{
Market market = null;
var cacheKey = string.Format("Market_{0}", forMarket.Id);
var cachedLookup = HttpContext.Current.Cache.Get(cacheKey);
if (cachedLookup != null)
{
market = (Market)cachedLookup;
}
else
{
market = context.Market.GetMarket(new VolumeFilter()
{
MarketId = forMarket.Id, OxoDocId = forDocument.Id, ProgrammeId = forDocument.ProgrammeId
});
if (!(market is EmptyMarket) && market.Id != 0)
{
HttpContext.Current.Cache.Add(cacheKey, market, null, DateTime.Now.AddMinutes(60), Cache.NoSlidingExpiration, CacheItemPriority.Default, null);
}
}
return(market);
}
public bool OXODocBulkInsertUpdate(int docId, string reminder, int progId, OXODataItem[] data, string section)
{
bool isNew = (docId == 0 ? true : false);
bool retVal = true;
String guid = System.Guid.NewGuid().ToString();
//First Step - create a empty OXO doc if isNew
if (isNew)
{
OXODoc newDoc = new OXODoc();
newDoc.Active = true;
newDoc.ProgrammeId = progId;
newDoc.Save(this.CurrentCDSID);
retVal = this.OXODocSave(newDoc);
docId = newDoc.Id;
}
if (retVal)
{
//first bulk insert all data - no need for transaction here;
DataTable tempTable = GetOXOTempDataTable();
foreach (OXODataItem item in data)
{
int?marketid;
if (item.MarketId == 0)
{
marketid = null;
}
else
{
marketid = item.MarketId;
}
int?marketgroupid;
if (item.MarketGroupId == 0)
{
marketgroupid = null;
}
else
{
marketgroupid = item.MarketGroupId;
}
int?featureid;
if (item.FeatureId == 0)
{
featureid = null;
}
else
{
featureid = item.FeatureId;
}
int?packid;
if (item.PackId == 0)
{
packid = null;
}
else
{
packid = item.PackId;
}
tempTable.Rows.Add(new Object[] { 0, guid, item.Section, item.ModelId, marketid, marketgroupid, featureid, packid, docId, item.Code, reminder });
}
using (IDbConnection conn = DbHelper.GetDBConnection())
{
using (SqlBulkCopy bulk = new SqlBulkCopy((SqlConnection)conn))
{
conn.Open();
bulk.DestinationTableName = "dbo.OXO_Temp_Working_Data";
bulk.WriteToServer(tempTable);
bulk.Close();
conn.Close();
}
}
// continute with update
string procName = "dbo.OXO_OXODoc_Bulk_Update";
using (IDbConnection conn = DbHelper.GetDBConnection())
{
try
{
var para = new DynamicParameters();
para.Add("@p_GUID", guid, dbType: DbType.String, size: 500);
para.Add("@p_Doc_Id", docId, dbType: DbType.Int32);
para.Add("@p_Section", section, dbType: DbType.String, size: 50);
para.Add("@p_Updated_By", CurrentCDSID, dbType: DbType.String, size: 8);
conn.Execute(procName, para, commandType: CommandType.StoredProcedure, commandTimeout: 0);
}
catch (Exception ex)
{
Log.Error(ex);
throw;
}
}
}
return(retVal);
}
private bool IsDocumentForVehicle(OXODoc documentToCheck, IVehicle vehicle)
{
return((!vehicle.ProgrammeId.HasValue || documentToCheck.ProgrammeId == vehicle.ProgrammeId.Value) &&
(string.IsNullOrEmpty(vehicle.Gateway) || documentToCheck.Gateway == vehicle.Gateway));
}
public void DocGetConfiguration(OXODoc currentDoc)
{
Programme retVal = new Programme();
using (IDbConnection conn = DbHelper.GetDBConnection())
{
try
{
var para = new DynamicParameters();
para.Add("@p_doc_id", currentDoc.Id, dbType: DbType.Int32);
using (var multi = conn.QueryMultiple("dbo.OXO_Doc_GetConfiguration", para, commandType: CommandType.StoredProcedure))
{
currentDoc.AllBodies = multi.Read<ModelBody>().ToList();
currentDoc.AllEngines = multi.Read<ModelEngine>().ToList();
currentDoc.AllTransmissions = multi.Read<ModelTransmission>().ToList();
currentDoc.AllTrims = multi.Read<ModelTrim>().ToList();
}
}
catch (Exception ex)
{
Log.Error(ex);
throw;
}
}
}
private static async Task<IVehicle> GetVehicle(IDataContext context, Vehicle forVehicle, OXODoc forDocument)
{
IVehicle vehicle = new EmptyVehicle();
var cacheKey = string.Format("Vehicle_{0}_{1}_{2}", forVehicle.ProgrammeId, forVehicle.Gateway,
forDocument.Id);
//var cachedLookup = GetCache(cacheKey);
//if (cachedLookup != null)
//{
// vehicle = (Vehicle) cachedLookup;
//}
//else
//{
// Do not deep get all vehicle details such as markets, derivatives, etc, as these are populated elsewhere
vehicle = await context.Vehicle.GetVehicle(new VehicleFilter()
{
ProgrammeId = forVehicle.ProgrammeId, Gateway = forVehicle.Gateway, DocumentId = forDocument.Id, Deep = false
});
if (!(vehicle is EmptyVehicle))
AddCache(cacheKey, vehicle);
//}
return vehicle;
}
private static async Task<OXODoc> GetOxoDocument(IDataContext context, OXODoc forOxoDocument)
{
OXODoc oxoDocument;
var cacheKey = string.Format("OxoDocument_{0}", forOxoDocument.Id);
var cachedLookup = GetCache(cacheKey);
if (cachedLookup != null)
{
oxoDocument = (OXODoc) cachedLookup;
}
else
{
oxoDocument = await context.TakeRate.GetUnderlyingOxoDocument(new TakeRateFilter() {DocumentId = forOxoDocument.Id, ProgrammeId = forOxoDocument.ProgrammeId});
AddCache(cacheKey, oxoDocument);
}
return oxoDocument;
}
public void GetConfiguration(OXODoc doc)
{
var ds = new OXODocDataStore("system");
ds.DocGetConfiguration(doc);
}
public static string GetDisplayString(this OXODoc document)
{
return(string.Format("{0} {1} {2}", document.Gateway, document.VersionLabel, document.Status));
}
private static async Task <IVehicle> GetVehicle(IDataContext context, Vehicle forVehicle, OXODoc forDocument)
{
IVehicle vehicle = new EmptyVehicle();
var cacheKey = string.Format("Vehicle_{0}_{1}_{2}", forVehicle.ProgrammeId, forVehicle.Gateway,
forDocument.Id);
//var cachedLookup = GetCache(cacheKey);
//if (cachedLookup != null)
//{
// vehicle = (Vehicle) cachedLookup;
//}
//else
//{
// Do not deep get all vehicle details such as markets, derivatives, etc, as these are populated elsewhere
vehicle = await context.Vehicle.GetVehicle(new VehicleFilter()
{
ProgrammeId = forVehicle.ProgrammeId, Gateway = forVehicle.Gateway, DocumentId = forDocument.Id, Deep = false
});
if (!(vehicle is EmptyVehicle))
{
AddCache(cacheKey, vehicle);
}
//}
return(vehicle);
}
public static XLWorkbook GenerateExcelCoverSheet(XLWorkbook workbook, int progid, int docid, string cdsid, OXODoc OXODoc, bool popDoc)
{
Stopwatch stopWatch = new Stopwatch();
try
{
stopWatch.Reset();
stopWatch.Start();
IXLWorksheet worksheet = workbook.Worksheets.Add("Cover Sheet");
workbook.Worksheet("Cover Sheet").Cell("K1").Value = "Creating Cover Sheet: " + DateTime.Now;
worksheet.Protect("Password123")
.SetFormatColumns()
.SetFormatRows();
worksheet.Style
.Fill.SetBackgroundColor(XLColor.White)
.Font.SetFontSize(11)
.Font.SetFontName("Arial");
worksheet.TabColor = XLColor.Yellow;
string blank = (popDoc == false) ? " (BLANK)" : "";
worksheet.Cell("A1").Value = "STANDARD SPECIFICATION FORMAT";
worksheet.Cell("B1").Value = OXODoc.VehicleAKA.ToUpper() + " OXO CHART" + blank;
worksheet.Cell("B1").Style.Fill.SetBackgroundColor(XLColor.Yellow);
worksheet.Cell("A3").Value = "MODEL YEAR";
worksheet.Cell("B3").Value = OXODoc.ModelYear;
worksheet.Cell("B3").Style.Fill.SetBackgroundColor(XLColor.Yellow);
worksheet.Cell("A4").Value = "VERSION";
worksheet.Cell("B4").Value = (OXODoc.VehicleName + " (" + OXODoc.VehicleAKA + ") " + OXODoc.Gateway + " V" + OXODoc.VersionId + " " + OXODoc.Status).ToUpper();
worksheet.Cell("B4").Style.Fill.SetBackgroundColor(XLColor.Yellow);
worksheet.Cell("A5").Value = "AUTHOR";
worksheet.Cell("B5").Value = cdsid.ToUpper();
worksheet.Cell("B5").Style.Fill.SetBackgroundColor(XLColor.Yellow);
worksheet.Cell("A6").Value = "DATE ISSUED";
worksheet.Cell("B6").Value = DateTime.Now;
worksheet.Cell("B6").Style.Fill.SetBackgroundColor(XLColor.Yellow);
worksheet.Cell("B6").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
worksheet.Range("A1:B6").Style.Font.SetBold(true);
worksheet.Cell("A9").Value = "OXO KEY";
worksheet.Cell("A10").Value = "S";
worksheet.Cell("B10").Value = "Standard - the feature is standard to a particular derivative";
worksheet.Cell("A11").Value = "O";
worksheet.Cell("B11").Value = "Optional - the feature is optional to a particular derivative and must correspond to a priced feature";
worksheet.Cell("A12").Value = "NA";
worksheet.Cell("B12").Value = "Not Available";
worksheet.Cell("A13").Value = "P";
worksheet.Cell("B13").Value = "Pack - the feature is only available as part of a pack";
worksheet.Cell("A14").Value = "(O)";
worksheet.Cell("B14").Value = "Linked Option - the option has a relationship with another option that means they must be ordered together";
worksheet.Range("A10:A15").Style.Alignment.SetIndent(5);
worksheet.Cell("A17").Value = "The worksheets included in this document are as follows";
worksheet.Cell("A19").Value = "Change Log";
worksheet.Cell("A19").Hyperlink = new XLHyperlink("'Change Log'!A1");
worksheet.Cell("B19").Value = "Highlights changes which have occurred since the last OxO version was issued";
worksheet.Cell("A20").Value = "Model Market Matrix";
worksheet.Cell("A20").Hyperlink = new XLHyperlink("'Model Market Matrix'!A1");
worksheet.Cell("B20").Value = "Provides summary information on specification groups & derivative line up for each market";
worksheet.Cell("A21").Value = "Reasons for Rules";
worksheet.Cell("A21").Hyperlink = new XLHyperlink("'Reasons for Rules'!A1");
worksheet.Cell("B21").Value = "Provides further information explaining rules within the main OxO";
worksheet.Cell("A22").Value = "Global Standard Features";
worksheet.Cell("A22").Hyperlink = new XLHyperlink("'Global Standard Features'!A1");
worksheet.Cell("B22").Value = "Indicates features that are standard on all derivatives for all markets";
worksheet.Cell("A23").Value = "World At A Glance";
worksheet.Cell("A23").Hyperlink = new XLHyperlink("'World At A Glance'!A1");
worksheet.Cell("B23").Value = "Details the full specifications for the global and regional generic specifications";
worksheet.Cell("A25").Value = "Regions vs Global Generic";
worksheet.Cell("B25").Value = "Details the specification variance at regional level versus the global generic specification";
worksheet.Range("A9:A25").Style.Font.SetBold(true);
worksheet.Range("A19:A25").Style.Alignment.SetIndent(3);
worksheet.Columns("A").Width = 48;
worksheet.Columns("B").Width = 40;
stopWatch.Stop();
var executionTime = stopWatch.ElapsedMilliseconds;
}
catch (Exception ex)
{
Log.Error(ex);
throw;
}
return workbook;
}
private static MarketGroup GetMarketGroup(IDataContext context, MarketGroup forMarketGroup, OXODoc forDocument)
{
MarketGroup marketGroup = null;
var cacheKey = string.Format("MarketGroup_{0}", forMarketGroup.Id);
var cachedLookup = HttpContext.Current.Cache.Get(cacheKey);
if (cachedLookup != null)
{
marketGroup = (MarketGroup)cachedLookup;
}
else
{
marketGroup = context.Market.GetMarketGroup(new VolumeFilter() { MarketGroupId = forMarketGroup.Id, OxoDocId = forDocument.Id, ProgrammeId = forDocument.ProgrammeId });
if (!(marketGroup is EmptyMarketGroup) && marketGroup.Id != 0)
HttpContext.Current.Cache.Add(cacheKey, marketGroup, null, DateTime.Now.AddMinutes(60), Cache.NoSlidingExpiration, CacheItemPriority.Default, null);
}
return marketGroup;
}
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;
}
private static OXODoc GetOxoDocument(IDataContext context, OXODoc forOxoDocument)
{
OXODoc oxoDocument = new EmptyOxoDocument();
var cacheKey = string.Format("OxoDocument_{0}", forOxoDocument.Id);
var cachedLookup = HttpContext.Current.Cache.Get(cacheKey);
if (cachedLookup != null)
{
oxoDocument = (OXODoc)cachedLookup;
}
else
{
oxoDocument = context.Volume.GetOxoDocument(new VolumeFilter() { OxoDocId = forOxoDocument.Id, ProgrammeId = forOxoDocument.ProgrammeId });
HttpContext.Current.Cache.Add(cacheKey, oxoDocument, null, DateTime.Now.AddMinutes(60), Cache.NoSlidingExpiration, CacheItemPriority.Default, null);
}
return oxoDocument;
}
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 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 static XLWorkbook GenerateExcelRFR(XLWorkbook workbook, int progid, string cdsid, OXODoc OXODoc)
{
Stopwatch stopWatch = new Stopwatch();
try
{
stopWatch.Reset();
stopWatch.Start();
IXLWorksheet worksheet = workbook.Worksheets.Add("Reasons for Rules");
worksheet.Protect("Password123")
.SetFormatColumns()
.SetFormatRows();
worksheet.Style
.Fill.SetBackgroundColor(XLColor.White)
.Font.SetFontSize(11)
.Font.SetFontName("Arial");
worksheet.TabColor = XLColor.Green;
// data
OXORuleDataStore rds = new OXORuleDataStore(cdsid);
var ruleReasons = rds.OXORuleGetMany(progid);
// page title
worksheet.Cell("A1").Value = "REASONS FOR RULES";
worksheet.Cell("A1").Hyperlink = new XLHyperlink("'Cover Sheet'!A21");
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("A6").Value = "Rule ID";
worksheet.Column("A").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
worksheet.Cell("B6").Value = "Rule Ref";
worksheet.Cell("C6").Value = "Rule";
worksheet.Column("C").Style.Alignment.SetWrapText();
worksheet.Cell("D6").Value = "Reason";
worksheet.Column("D").Style.Alignment.SetWrapText();
worksheet.Cell("E6").Value = "Contact";
worksheet.Range("A6:E6")
.Style.Font.SetBold(true)
.Font.SetFontColor(XLColor.White)
.Fill.SetBackgroundColor(XLColor.Black)
.Alignment.Vertical = XLAlignmentVerticalValues.Center;
// output data
int row = 7;
foreach (var rule in ruleReasons)
{
worksheet.Cell(row, 1).Value = "'" + rule.Id.ToString("000000");
worksheet.Cell(row, 2).Value = rule.RuleCategory;
worksheet.Cell(row, 3).Value = rule.RuleResponse;
worksheet.Cell(row, 4).Value = rule.RuleReason;
worksheet.Cell(row, 5).Value = rule.Owner;
row = row + 1;
}
// adjust column widths
worksheet.Columns().AdjustToContents();
worksheet.Column(1).Width = 12;
worksheet.Column(3).Width = 100;
worksheet.Column(4).Width = 100;
// set border styles
worksheet.Range(7, 1, worksheet.LastRowUsed().RowNumber(), 5).Style
.Border.SetTopBorder(XLBorderStyleValues.Thin)
.Border.SetRightBorder(XLBorderStyleValues.Thin)
.Border.SetOutsideBorder(XLBorderStyleValues.Thin);
// split the screen
worksheet.SheetView.Freeze(7, 0);
stopWatch.Stop();
var executionTime = stopWatch.ElapsedMilliseconds;
}
catch (Exception ex)
{
Log.Error(ex);
throw;
}
return workbook;
}
public static XLWorkbook GenerateExcelChangeLog(XLWorkbook workbook, int docid, string cdsid, OXODoc OXODoc)
{
Stopwatch stopWatch = new Stopwatch();
try
{
stopWatch.Reset();
stopWatch.Start();
IXLWorksheet worksheet = workbook.Worksheets.Add("Change Log");
worksheet.Protect("Password123")
.SetFormatColumns()
.SetFormatRows();
worksheet.Style
.Fill.SetBackgroundColor(XLColor.White)
.Font.SetFontSize(11)
.Font.SetFontName("Arial");
worksheet.TabColor = XLColor.Orange;
// data
ChangeDiaryDataStore cds = new ChangeDiaryDataStore(cdsid);
var changeDiary = cds.ChangeDiaryGetMany(docid);
// page title
worksheet.Cell("A1").Value = "CHANGE LOG";
worksheet.Cell("A1").Hyperlink = new XLHyperlink("'Cover Sheet'!A19");
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);
// heading row
worksheet.Cell("A6").Value = "Date of OXO Change";
worksheet.Column("A").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
worksheet.Cell("B6").Value = "Market";
worksheet.Cell("C6").Value = "Engine & Trim Level";
worksheet.Cell("D6").Value = "Feature & Detail of Change (Since Previous OXO Version)";
worksheet.Cell("E6").Value = "Current" + Environment.NewLine + "Fitment";
worksheet.Column("E").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell("F6").Value = "Proposed" + Environment.NewLine + "Fitment";
worksheet.Column("F").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell("G6").Value = "Pricing Status";
worksheet.Cell("H6").Value = "PACN / PDL";
worksheet.Column("H").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell("I6").Value = "FCIM E-Tracker";
worksheet.Column("I").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell("J6").Value = "Reason for Change";
worksheet.Cell("K6").Value = "Order Call";
worksheet.Column("K").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell("L6").Value = "Build Effectivity Date";
worksheet.Range("A6:L6")
.Style.Font.SetBold(true)
.Font.SetFontColor(XLColor.White)
.Fill.SetBackgroundColor(XLColor.Black)
.Alignment.Vertical = XLAlignmentVerticalValues.Center;
// output data
int row = 7;
string header = "";
foreach (var item in changeDiary)
{
// row grouping
if (header != item.VersionInfo)
{
worksheet.Cell("A" + row).SetValue(item.VersionInfo)
.Style.Font.SetFontColor(XLColor.White)
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left)
.Font.SetBold(true)
.Font.SetUnderline(XLFontUnderlineValues.Single);
worksheet.Range(row, 1, row, 12).Style.Fill.SetBackgroundColor(XLColor.Black);
row = row + 1;
}
header = item.VersionInfo;
worksheet.Cell(row, 1).Value = item.EntryDate;
worksheet.Cell(row, 2).Value = item.Markets;
worksheet.Cell(row, 3).Value = item.Models;
worksheet.Cell(row, 4).Value = item.Features;
worksheet.Cell(row, 5).Value = item.CurrentFitment;
worksheet.Cell(row, 6).Value = item.ProposedFitment;
worksheet.Cell(row, 7).Value = item.PricingStatus;
worksheet.Cell(row, 8).Value = item.PACN;
worksheet.Cell(row, 9).Value = item.ETracker;
worksheet.Cell(row, 10).Value = item.Comment;
worksheet.Cell(row, 11).Value = item.OrderCall;
worksheet.Cell(row, 12).Value = item.BuildEffectiveDate;
row = row + 1;
}
// adjust column widths
worksheet.Columns().AdjustToContents();
worksheet.Column(1).Width = 27;
worksheet.Column(2).Width = 50;
worksheet.Column(2).Style.Alignment.SetWrapText();
worksheet.Column(3).Width = 26;
worksheet.Column(4).Width = 100;
worksheet.Column(4).Style.Alignment.SetWrapText();
worksheet.Column(5).Width = 14;
worksheet.Column(6).Width = 14;
worksheet.Column(7).Width = 20;
worksheet.Column(8).Width = 17;
worksheet.Column(9).Width = 22;
worksheet.Column(10).Width = 50;
worksheet.Column(10).Style.Alignment.SetWrapText();
worksheet.Column(11).Width = 14;
worksheet.Column(12).Width = 28;
// set border styles
worksheet.Range(7, 1, worksheet.LastRowUsed().RowNumber(), 12).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 FdpOxoDoc()
{
Header = new EmptyTakeRateSummary();
Document = new EmptyOxoDocument();
}
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;
}
private static Vehicle GetVehicle(IDataContext context, Vehicle forVehicle, OXODoc forDocument)
{
return (Vehicle)context.Vehicle.GetVehicle(new VehicleFilter()
{
ProgrammeId = forVehicle.ProgrammeId,
Gateway = forVehicle.Gateway,
OxoDocId = forDocument.Id,
});
}
public void GetConfiguration(OXODoc doc)
{
var ds = new OXODocDataStore("system");
ds.DocGetConfiguration(doc);
}
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 bool OXODocBulkInsertUpdate(int docId, string reminder, int progId, OXODataItem[] data, string section)
{
bool isNew = (docId == 0 ? true : false);
bool retVal = true;
String guid = System.Guid.NewGuid().ToString();
//First Step - create a empty OXO doc if isNew
if (isNew)
{
OXODoc newDoc = new OXODoc();
newDoc.Active = true;
newDoc.ProgrammeId = progId;
newDoc.Save(this.CurrentCDSID);
retVal = this.OXODocSave(newDoc);
docId = newDoc.Id;
}
if (retVal)
{
//first bulk insert all data - no need for transaction here;
DataTable tempTable = GetOXOTempDataTable();
foreach (OXODataItem item in data)
{
int? marketid;
if (item.MarketId == 0)
marketid = null;
else
marketid = item.MarketId;
int? marketgroupid;
if (item.MarketGroupId == 0)
marketgroupid = null;
else
marketgroupid = item.MarketGroupId;
int? featureid;
if (item.FeatureId == 0)
featureid = null;
else
featureid = item.FeatureId;
int? packid;
if (item.PackId == 0)
packid = null;
else
packid = item.PackId;
tempTable.Rows.Add(new Object[] { 0, guid, item.Section, item.ModelId, marketid, marketgroupid, featureid, packid, docId, item.Code, reminder });
}
using (IDbConnection conn = DbHelper.GetDBConnection())
{
using (SqlBulkCopy bulk = new SqlBulkCopy((SqlConnection)conn))
{
conn.Open();
bulk.DestinationTableName = "dbo.OXO_Temp_Working_Data";
bulk.WriteToServer(tempTable);
bulk.Close();
conn.Close();
}
}
// continute with update
string procName = "dbo.OXO_OXODoc_Bulk_Update";
using (IDbConnection conn = DbHelper.GetDBConnection())
{
try
{
var para = new DynamicParameters();
para.Add("@p_GUID", guid, dbType: DbType.String, size:500);
para.Add("@p_Doc_Id", docId, dbType: DbType.Int32);
para.Add("@p_Section", section, dbType: DbType.String, size: 50);
para.Add("@p_Updated_By", CurrentCDSID, dbType: DbType.String, size: 8);
conn.Execute(procName, para, commandType: CommandType.StoredProcedure, commandTimeout: 0);
}
catch (Exception ex)
{
Log.Error(ex);
throw;
}
}
}
return retVal;
}
private bool IsDocumentForVehicle(OXODoc documentToCheck, IVehicle vehicle)
{
return (!vehicle.ProgrammeId.HasValue || documentToCheck.ProgrammeId == vehicle.ProgrammeId.Value) &&
(string.IsNullOrEmpty(vehicle.Gateway) || documentToCheck.Gateway == vehicle.Gateway);
}
public bool OXODocSave(OXODoc obj)
{
bool retVal = true;
string procName = (obj.IsNew ? "dbo.OXO_OXODoc_New" : "dbo.OXO_OXODoc_Edit");
using (IDbConnection conn = DbHelper.GetDBConnection())
{
try
{
obj.Save(this.CurrentCDSID);
var para = new DynamicParameters();
para.Add("@p_Programme_Id", obj.ProgrammeId, dbType: DbType.Int32);
para.Add("@p_Gateway", obj.Gateway, dbType: DbType.String, size: 50);
para.Add("@p_Version_Id", obj.VersionId, dbType: DbType.Decimal);
para.Add("@p_Status", obj.Status, dbType: DbType.String, size: 50);
if (obj.IsNew)
{
para.Add("@p_Created_By", obj.CreatedBy, dbType: DbType.String, size: 8);
para.Add("@p_Created_On", obj.CreatedOn, dbType: DbType.DateTime);
}
para.Add("@p_Updated_By", obj.UpdatedBy, dbType: DbType.String, size: 8);
para.Add("@p_Last_Updated", obj.LastUpdated, dbType: DbType.DateTime);
para.Add("@p_Id", obj.Id, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);
conn.Execute(procName, para, commandType: CommandType.StoredProcedure);
if (obj.Id == 0)
{
obj.Id = para.Get<int>("@p_Id");
}
}
catch (Exception ex)
{
Log.Error(ex);
throw;
}
}
return retVal;
}
