public ImportDataContext(string cdsId) : base(cdsId)
 {
     _importDataStore = new ImportQueueDataStore(cdsId);
     _marketDataStore = new MarketDataStore(cdsId);
     _derivativeDataStore = new DerivativeDataStore(cdsId);
     _featureDataStore = new FeatureDataStore(cdsId);
     _trimDataStore = new ModelTrimDataStore(cdsId);
 }
 public ImportDataContext(string cdsId) : base(cdsId)
 {
     _importDataStore     = new ImportQueueDataStore(cdsId);
     _marketDataStore     = new MarketDataStore(cdsId);
     _derivativeDataStore = new DerivativeDataStore(cdsId);
     _featureDataStore    = new FeatureDataStore(cdsId);
     _trimDataStore       = new ModelTrimDataStore(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 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;
        }