Пример #1
0
 private static string GetClaimInfo(TenderClaim claim)
 {
     var db = new DbEngine();
     var dealTypes = db.LoadDealTypes();
     return "Заявка № " + claim.Id + ", Автор: " + GetUserById(claim.Author.Id).ShortName +
            ", Номер конкурса: " + claim.TenderNumber + ", Дата начала" +
            claim.TenderStart.ToString("dd.MM.yyyy") + ", Срок сдачи: "
            + claim.ClaimDeadline.ToString("dd.MM.yyyy") + ", Менеджер: " +
            GetUserById(claim.Manager.Id).ShortName + ", Подразделение менеджера: " +
            claim.Manager.SubDivision +
            ", Заказчик: " + claim.Customer + " ИНН заказчика: " + claim.CustomerInn +
            ", Тип конкурса: " + dealTypes.First(x => x.Id == claim.DealType).Value + (claim.Sum > 0
                ? ", Сумма: " + claim.Sum.ToString("N2")
                : string.Empty) + (!string.IsNullOrEmpty(claim.TenderUrl)
                    ? ", Сcылка на конкурс: <a href='" + claim.TenderUrl + "'>[Ссылка]</a>]"
                    : string.Empty) + ".<br/>";
 }
        //Excel
        //получение excel файла, содержащем только расчет по заявке
        public ActionResult GetSpecificationFileOnlyCalculation(int claimId, int cv)
        {
            XLWorkbook excBook = null;
            var ms = new MemoryStream();
            var error = false;
            var message = string.Empty;
            try
            {

                //получение позиций по заявке и расчетов к ним
                var db = new DbEngine();
                var positions = db.LoadSpecificationPositionsForTenderClaim(claimId, cv);
                var facts = db.LoadProtectFacts();
                if (positions.Any())
                {
                    var calculations = db.LoadCalculateSpecificationPositionsForTenderClaim(claimId, cv);
                    if (calculations != null && calculations.Any())
                    {
                        foreach (var position in positions)
                        {
                            if (position.State == 1) continue;
                            position.Calculations =
                                calculations.Where(x => x.IdSpecificationPosition == position.Id).ToList();
                        }
                    }
                    var filePath = Path.Combine(Server.MapPath("~"), "App_Data", "Specification_fin.xlsx");
                    using (var fs = System.IO.File.OpenRead(filePath))
                    {
                        var buffer = new byte[fs.Length];
                        fs.Read(buffer, 0, buffer.Count());
                        ms.Write(buffer, 0, buffer.Count());
                        ms.Seek(0, SeekOrigin.Begin);
                    }
                    //создание файла excel с инфой по расчетам
                    excBook = new XLWorkbook(ms);
                    var workSheet = excBook.Worksheet("WorkSheet");
                    workSheet.Name = "лот";
                    var claim = db.LoadTenderClaimById(claimId);
                    //>>>>>>>>Шапка - Заполнение инфы о заявке<<<<<<

                    //Менеджер из Москвы
                    bool managerIsMoscou = false;
                    string managerSid = GetUser().Id;
                    var dtUserIsMoscou = Db.CheckManagerIsMoscou(managerSid);
                    if (dtUserIsMoscou.Rows.Count > 0)
                    {
                        managerIsMoscou = dtUserIsMoscou.Rows[0]["result"].ToString().Equals("1");
                    }
                    // />Менеджер из Москвы

                    var dealTypes = db.LoadDealTypes();
                    var deliveryTimes = db.LoadDeliveryTimes();
                    UserBase manager;
                    try
                    {
                        manager = UserHelper.GetUserById(claim.Manager.Id);
                    }
                    catch (Exception ex)
                    {
                        manager = new UserBase();
                    }
                    var dt = Db.GetExchangeRatesOnDate(DateTime.Now);

                    double? usdRate = null;
                    double? eurRate = null;

                    if (dt.Rows.Count >= 3)
                    {
                        usdRate = Convert.ToDouble(dt.Rows[1]["price"].ToString()) * 1.03;
                        eurRate = Convert.ToDouble(dt.Rows[2]["price"].ToString()) * 1.03;
                    }

                    int rowHead = 1;

                    var usdCell = workSheet.Cell(rowHead, 4);
                    usdCell.Value =
                        usdRate.HasValue
                            ? usdRate.Value.ToString("N2")
                            : string.Empty;
                    workSheet.Cell(rowHead, 4).DataType = XLCellValues.Number;

                    var eurCell = workSheet.Cell(++rowHead, 4);
                    eurCell.Value = eurRate.HasValue
                            ? eurRate.Value.ToString("N2")
                            : string.Empty;
                    workSheet.Cell(rowHead, 4).DataType = XLCellValues.Number;

                    var eurRicohCell = workSheet.Cell(++rowHead, 4);
                    var profitCell = workSheet.Cell(++rowHead, 4);//Рентабельность
                    workSheet.Cell(++rowHead, 4).Value = dealTypes.First(x => x.Id == claim.DealType).Value;
                    workSheet.Cell(++rowHead, 4).Value = manager != null ? manager.ShortName : string.Empty;
                    workSheet.Cell(++rowHead, 4).Value = claim.Customer;
                    //срок готовности цен от снабжения???
                    rowHead++;
                    workSheet.Cell(++rowHead, 4).Value = claim.Sum;//Максимальная цена контракта???
                    workSheet.Cell(++rowHead, 4).Value = claim.DeliveryDateString;
                    workSheet.Cell(rowHead, 4).DataType = XLCellValues.DateTime;
                    workSheet.Cell(++rowHead, 4).Value = claim.DeliveryPlace;
                    workSheet.Cell(++rowHead, 4).Value = claim.KPDeadlineString;
                    workSheet.Cell(rowHead, 4).DataType = XLCellValues.DateTime;
                    workSheet.Cell(++rowHead, 4).Value = claim.AuctionDateString;
                    workSheet.Cell(rowHead, 4).DataType = XLCellValues.DateTime;
                    workSheet.Cell(++rowHead, 4).Value = claim.Comment;

                    //workSheet.Cell(3, 4).Value = claim.TenderNumber;
                    //workSheet.Cell(4, 4).Value = claim.TenderStartString;
                    //workSheet.Cell(4, 4).DataType = XLCellValues.DateTime;
                    //workSheet.Cell(5, 4).Value = claim.ClaimDeadlineString;
                    //workSheet.Cell(5, 4).DataType = XLCellValues.DateTime;
                    //workSheet.Cell(8, 4).Value = claim.CustomerInn;
                    //workSheet.Cell(9, 4).Value = !claim.Sum.Equals(0) ? claim.Sum.ToString("N2") : string.Empty;
                    //workSheet.Cell(11, 4).Value = claim.TenderUrl;
                    //workSheet.Cell(13, 4).Value = claim.Manager.SubDivision;
                    //workSheet.Cell(14, 4).DataType = XLCellValues.DateTime;

                    //заголовок для расчетов
                    //workSheet.Cell(18, 1).Value = "№ пп";
                    //workSheet.Cell(18, 2).Value = "Каталожный номер*";
                    //workSheet.Cell(18, 3).Value = "Наименование*";
                    //workSheet.Cell(18, 4).Value = "Замена";
                    //workSheet.Cell(18, 5).Value = "Цена за ед.";
                    //workSheet.Cell(18, 6).Value = "Сумма вход";
                    //workSheet.Cell(18, 7).Value = "Валюта";
                    ////workSheet.Cell(13, 7).Value = "Цена за ед. руб";
                    ////workSheet.Cell(13, 8).Value = "Сумма вход руб*";
                    //workSheet.Cell(18, 8).Value = "Поставщик";
                    //workSheet.Cell(18, 9).Value = "Факт получ.защиты*";
                    //workSheet.Cell(18, 10).Value = "Условия защиты";
                    //workSheet.Cell(18, 11).Value = "Цена с ТЗР";
                    //workSheet.Cell(18, 12).Value = "Сумма с ТЗР";
                    //workSheet.Cell(18, 13).Value = "Цена с НДС";
                    //workSheet.Cell(18, 14).Value = "Сумма с НДС";
                    //workSheet.Cell(18, 15).Value = "Комментарий";
                    //var calcHeaderRange = workSheet.Range(workSheet.Cell(18, 1), workSheet.Cell(18, 15));
                    //calcHeaderRange.Style.Font.SetBold(true);
                    //calcHeaderRange.Style.Fill.BackgroundColor = XLColor.FromArgb(0, 204, 255, 209);
                    //calcHeaderRange.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                    //calcHeaderRange.Style.Border.SetBottomBorder(XLBorderStyleValues.Thin);
                    //calcHeaderRange.Style.Border.SetBottomBorderColor(XLColor.Gray);
                    //calcHeaderRange.Style.Border.SetTopBorder(XLBorderStyleValues.Thin);
                    //calcHeaderRange.Style.Border.SetTopBorderColor(XLColor.Gray);
                    //calcHeaderRange.Style.Border.SetRightBorder(XLBorderStyleValues.Thin);
                    //calcHeaderRange.Style.Border.SetRightBorderColor(XLColor.Gray);
                    //calcHeaderRange.Style.Border.SetLeftBorder(XLBorderStyleValues.Thin);
                    //calcHeaderRange.Style.Border.SetLeftBorderColor(XLColor.Gray);
                    //var currencies = db.LoadCurrencies();
                    //<<<<<<<Номер строки - начало вывода инфы>>>>>>
                    var row = rowHead+2;
                    int firstRow = row;
                    var rowNumber = 1;
                    //строки расчета
                    foreach (var position in positions)
                    {
                        position.Name = position.Name.Replace("\n", "").Replace("\r", "");

                        if (position.Calculations != null && position.Calculations.Any())
                        {
                            foreach (var calculation in position.Calculations)
                            {
                                calculation.Name = calculation.Name.Replace("\n", "").Replace("\r", "");

                                int col = 0;
                                workSheet.Cell(row, ++col).Value = rowNumber;
                                workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);

                                workSheet.Cell(row, ++col).Value = position.CatalogNumber;
                                    //? position.CatalogNumber
                                    //: calculation.CatalogNumber;
                                double hPosCatNum = GetCellHeight(workSheet.Cell(row, col).Value.ToString().Length, 30);
                                workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);

                                workSheet.Cell(row, ++col).Value = position.Name;
                                double hPosName = GetCellHeight(workSheet.Cell(row, col).Value.ToString().Length, 40);
                                    //String.IsNullOrEmpty(calculation.Name)? position.Name: calculation.Name;
                                workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);

                                workSheet.Cell(row, ++col).Value = calculation.CatalogNumber;
                                double hCalcCatNum = GetCellHeight(workSheet.Cell(row, col).Value.ToString().Length, 30);
                                workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);

                                workSheet.Cell(row, ++col).Value = calculation.Name + (!String.IsNullOrEmpty(calculation.Name) ? "\r\n" : "")+  (!String.IsNullOrEmpty(calculation.Replace) ? "Замена:\r\n" + calculation.Replace : String.Empty);
                                double hCalcName = GetCellHeight(workSheet.Cell(row, col).Value.ToString().Length, 40);
                                workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
                                //workSheet.Cell(row, ++col).Value = calculation.Replace;
                                //workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);

                                workSheet.Cell(row, ++col).Value = GetUnitStr(position.Unit);
                                workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                                var countCell = workSheet.Cell(row, ++col);
                                countCell.Value = position.Value;

                                countCell.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                                if (position.ProductManager != null)
                                {
                                    var prodManager = UserHelper.GetUserById(position.ProductManager.Id);
                                    workSheet.Cell(row, ++col).Value = prodManager == null
                                        ? String.Empty
                                        : prodManager.ShortName;
                                }
                                double hProdManager = GetCellHeight(workSheet.Cell(row, col).Value.ToString().Length, 16);
                                workSheet.Cell(row, ++col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                                if (calculation.DeliveryTime != null)
                                {
                                    var delivTime = deliveryTimes.First(x => x.Id == calculation.DeliveryTime.Id);
                                    workSheet.Cell(row, col).Value = delivTime == null ? String.Empty : delivTime.Value;
                                }
                                double hCalcDeliv = GetCellHeight(workSheet.Cell(row, col).Value.ToString().Length, 16);
                                workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                                string partNum4Online = String.IsNullOrEmpty(position.CatalogNumber)? calculation.CatalogNumber: position.CatalogNumber;
                                    string onlinePrice = String.Empty;
                                try
                                {onlinePrice = CatalogProduct.PriceRequest(partNum4Online);}
                                catch {

                                }
                                workSheet.Cell(row, ++col).Value = onlinePrice;//Цена B2B
                                workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);

                                var priceUsdCell = workSheet.Cell(row, ++col);
                                priceUsdCell.Value = calculation.PriceUsd;
                                priceUsdCell.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
                                priceUsdCell.Style.NumberFormat.Format = "$ #,##0.00";
                                var priceEurCell = workSheet.Cell(row, ++col);
                                priceEurCell.Value = calculation.PriceEur;
                                priceEurCell.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
                                priceEurCell.Style.NumberFormat.Format = "€ #,##0.00";
                                var priceEurRicohCell = workSheet.Cell(row, ++col);
                                priceEurRicohCell.Value = calculation.PriceEurRicoh;
                                priceEurRicohCell.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
                                priceEurRicohCell.Style.NumberFormat.Format = "€ #,##0.00";
                                var priceRublCell = workSheet.Cell(row, ++col);
                                priceRublCell.Value = calculation.PriceRubl;
                                priceRublCell.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);

                                //Вход за ед
                                if (String.IsNullOrEmpty(priceRublCell.Value.ToString().Trim()))
                                {
                                    if (!String.IsNullOrEmpty(priceEurRicohCell.Value.ToString().Trim()))
                                    {
                                        priceRublCell.FormulaR1C1 = String.Format("{0}*{1}", eurRicohCell.Address.ToStringFixed(), priceEurRicohCell.Address);
                                    }
                                    if (!String.IsNullOrEmpty(priceEurCell.Value.ToString().Trim()))
                                    {
                                        priceRublCell.FormulaR1C1 = String.Format("{0}*{1}", eurCell.Address.ToStringFixed(), priceEurCell.Address);
                                    }
                                    if (!String.IsNullOrEmpty(priceUsdCell.Value.ToString().Trim()))
                                    {
                                        priceRublCell.FormulaR1C1 = String.Format("{0}*{1}", usdCell.Address.ToStringFixed(), priceUsdCell.Address);
                                    }

                                }
                                // />Вход за ед
                                priceRublCell.Style.NumberFormat.Format = "₽ #,##0.00";
                                //Сумма
                                var priceSumCell = workSheet.Cell(row, ++col);
                                priceSumCell.Style.NumberFormat.Format = "₽ #,##0.00";
                                priceSumCell.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
                                priceSumCell.FormulaR1C1 = String.Format("{0}*{1}", countCell.Address,
                                    priceRublCell.Address);
                                // />Сумма

                                //Цена с ТЗР
                                var priceTzrCell = workSheet.Cell(row, ++col);
                                string formulaPriceTzrCell = String.Format("(({0}*1.02)*1.02)", priceRublCell.Address);
                                if (managerIsMoscou)
                                {
                                    formulaPriceTzrCell = String.Format("({0}*1.02)", priceRublCell.Address);
                                }
                                priceTzrCell.FormulaR1C1 = formulaPriceTzrCell;
                                priceTzrCell.Style.NumberFormat.Format = "₽ #,##0.00";
                                priceTzrCell.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
                                // /> Цена с ТЗР

                                //Сумма с ТЗР
                                var sumTzrCell = workSheet.Cell(row, ++col);
                                string formulaSumTzrCell = String.Format("{0}*{1}", countCell.Address,priceTzrCell.Address);
                                sumTzrCell.FormulaR1C1 = formulaSumTzrCell;
                                sumTzrCell.Style.NumberFormat.Format = "₽ #,##0.00";
                                sumTzrCell.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
                                // /> Сумма с ТЗР

                                //Цена С НДС
                                var priceNdsCell = workSheet.Cell(row, ++col);
                                string formulaPriceNdsCell = String.Format("{0}*(1+({1}/100))", priceTzrCell.Address, profitCell.Address);
                                priceNdsCell.FormulaR1C1 = formulaPriceNdsCell;
                                priceNdsCell.Style.NumberFormat.Format = "₽ #,##0.00";
                                priceNdsCell.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
                                // /> Цена с НДС

                                //Сумма с НДС
                                var sumNdsCell = workSheet.Cell(row, ++col);
                                string formulaSumNdsCell = String.Format("{0}*{1}", countCell.Address, priceNdsCell.Address);
                                sumNdsCell.FormulaR1C1 = formulaSumNdsCell;
                                sumNdsCell.Style.NumberFormat.Format = "₽ #,##0.00";
                                sumNdsCell.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
                                // /> Сумма с НДС

                                //workSheet.Cell(row, 5).Value = !calculation.PriceCurrency.Equals(0)
                                //    ? calculation.PriceCurrency.ToString("N2")
                                //    : string.Empty;
                                //workSheet.Cell(row, 6).Value = !calculation.SumCurrency.Equals(0)
                                //    ? calculation.SumCurrency.ToString("N2")
                                //    : string.Empty;
                                //workSheet.Cell(row, 7).Value = "";//currencies.First(x => x.Id == calculation.Currency).Value;
                                //workSheet.Cell(row, 7).Value = !calculation.PriceRub.Equals(0)
                                //    ? calculation.PriceRub.ToString("N2")
                                //    : string.Empty;
                                //workSheet.Cell(row, 8).Value = !calculation.SumRub.Equals(0)
                                //    ? calculation.SumRub.ToString("N2")
                                //    : string.Empty;

                                workSheet.Cell(row, ++col).Value = calculation.Provider;
                                workSheet.Cell(row, col)
                                    .Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                                //workSheet.Cell(row, 9).Value = calculation.ProtectFact != null ?
                                //    facts.First(x => x.Id == calculation.ProtectFact.Id).Value : String.Empty;
                                //workSheet.Cell(row, 10).Value = calculation.ProtectCondition;
                                workSheet.Cell(row, ++col).Value = calculation.Comment;
                                workSheet.Cell(row, col).Style.Font.SetFontColor(XLColor.Red);
                                double hComent = GetCellHeight(workSheet.Cell(row, col).Value.ToString().Length, 40);

                                double[] arr = { hPosCatNum, hPosName, hCalcName, hCalcDeliv, hProdManager, hComent, hCalcCatNum };
                                workSheet.Row(row).Height = arr.Max();
                                row++;
                                rowNumber++;
                            }
                        }

                        else
                        {
                            int col = 0;
                            workSheet.Cell(row, ++col).Value = rowNumber;
                            workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                            workSheet.Cell(row, ++col).Value = position.CatalogNumber;
                            workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);

                            workSheet.Cell(row, ++col).SetValue(position.Name);
                            workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);

                            workSheet.Row(row).Height = GetCellHeight(position.CatalogNumber.Length, 10);
                            workSheet.Row(row).Height = GetCellHeight(position.Name.Length, 40);
                            ++col;
                            ++col;
                            //workSheet.Cell(row, ++col).Value = String.Empty;
                            workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
                            workSheet.Cell(row, ++col).Value = GetUnitStr(position.Unit);
                            workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                            workSheet.Cell(row, ++col).Value = position.Value;
                            workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                            if (position.ProductManager != null)
                            {
                                var prodManager = UserHelper.GetUserById(position.ProductManager.Id);
                                workSheet.Cell(row, ++col).Value = prodManager == null
                                    ? String.Empty
                                    : prodManager.ShortName;
                            }
                            workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                            workSheet.Cell(row, ++col).Value = String.Empty;
                            workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);

                            ++col;//Цена B2B

                            workSheet.Cell(row, ++col).Value = String.Empty;
                            workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                            workSheet.Cell(row, col).Style.NumberFormat.Format = "$ #,###";
                            workSheet.Cell(row, ++col).Value = String.Empty;
                            workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                            workSheet.Cell(row, col).Style.NumberFormat.Format = "€ #,###";
                            workSheet.Cell(row, ++col).Value = String.Empty;
                            workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                            workSheet.Cell(row, col).Style.NumberFormat.Format = "€ #,###";
                            workSheet.Cell(row, ++col).Value = String.Empty;
                            workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                            workSheet.Cell(row, col).Style.NumberFormat.Format = "₽ #,###";
                            col = col + 6;
                            workSheet.Cell(row, col).Value = String.Empty;
                            workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                            row++;
                            rowNumber++;
                        }
                    }

                    var range = workSheet.Range(workSheet.Cell(firstRow, 1), workSheet.Cell(row - 1, 21));
                    range.Style.Border.SetBottomBorder(XLBorderStyleValues.Thin);
                    range.Style.Border.SetBottomBorderColor(XLColor.Gray);
                    range.Style.Border.SetTopBorder(XLBorderStyleValues.Thin);
                    range.Style.Border.SetTopBorderColor(XLColor.Gray);
                    range.Style.Border.SetRightBorder(XLBorderStyleValues.Thin);
                    range.Style.Border.SetRightBorderColor(XLColor.Gray);
                    range.Style.Border.SetLeftBorder(XLBorderStyleValues.Thin);
                    range.Style.Border.SetLeftBorderColor(XLColor.Gray);

                    excBook.SaveAs(ms);
                    excBook.Dispose();
                    ms.Seek(0, SeekOrigin.Begin);
                }
                else
                {
                    error = true;
                    message = "Нет позиций для расчета";
                }
            }
            catch (Exception ex)
            {
                error = true;
                message = "Ошибка сервера " + ex.Message;
            }
            finally
            {
                if (excBook != null)
                {
                    excBook.Dispose();
                }
            }
            if (!error)
            {
                return new FileStreamResult(ms, "application/vnd.ms-excel")
                {
                    FileDownloadName = "Calculation_" + claimId + ".xlsx"
                };
            }
            else
            {
                ViewBag.Message = message;
                return View();
            }
        }
        //Excel
        //получение excel файла, для определения позиций по заявке
        public ActionResult GetSpecificationFile(int claimId)
        {
            XLWorkbook excBook = null;
            var ms = new MemoryStream();
            var error = false;
            try
            {
                var db = new DbEngine();
                var claim = db.LoadTenderClaimById(claimId);
                //получение файла-шаблона
                var filePath = Path.Combine(Server.MapPath("~"), "App_Data", "Specification.xlsx");
                using (var fs = System.IO.File.OpenRead(filePath))
                {
                    var buffer = new byte[fs.Length];
                    fs.Read(buffer, 0, buffer.Count());
                    ms.Write(buffer, 0, buffer.Count());
                    ms.Seek(0, SeekOrigin.Begin);
                }
                //создание диапазона выбора снабженцев
                var productManagers = UserHelper.GetProductManagers();
                excBook = new XLWorkbook(ms);
                var workSheet = excBook.Worksheet("Лот");
                var userRangeSheet = excBook.Worksheet(2);

                if (workSheet != null && userRangeSheet != null)
                {
                    userRangeSheet.Visibility = XLWorksheetVisibility.Hidden;
                    //>>>>>>>>Шапка - Заполнение инфы о заявке<<<<<<
                    var dealTypes = db.LoadDealTypes();

                    var manager = UserHelper.GetUserById(claim.Manager.Id);
                    //workSheet.Cell(1, 3).Value = !claim.CurrencyUsd.Equals(0)
                    //    ? claim.CurrencyUsd.ToString("N2")
                    //    : string.Empty;
                    //workSheet.Cell(2, 3).Value = !claim.CurrencyEur.Equals(0)
                    //    ? claim.CurrencyEur.ToString("N2")
                    //    : string.Empty;
                    //workSheet.Cell(1, 3).DataType = XLCellValues.Number;
                    //workSheet.Cell(2, 3).DataType = XLCellValues.Number;
                    workSheet.Cell(1, 3).Value = claim.TenderNumber;
                    //workSheet.Cell(4, 3).Value = claim.TenderStartString;
                    //workSheet.Cell(4, 3).DataType = XLCellValues.DateTime;
                    //workSheet.Cell(5, 3).Value = claim.ClaimDeadlineString;
                    //workSheet.Cell(5, 3).DataType = XLCellValues.DateTime;
                    //workSheet.Cell(6, 3).Value = claim.KPDeadlineString;
                    //workSheet.Cell(6, 3).DataType = XLCellValues.DateTime;
                    workSheet.Cell(2, 3).Value = claim.Customer;
                    //workSheet.Cell(8, 3).Value = claim.CustomerInn;
                    //workSheet.Cell(9, 3).Value = !claim.Sum.Equals(0) ? claim.Sum.ToString("N2") : string.Empty;
                    //workSheet.Cell(10, 3).Value = dealTypes.First(x => x.Id == claim.DealType).Value;
                    //workSheet.Cell(11, 3).Value = claim.TenderUrl;
                    workSheet.Cell(3, 3).Value = manager != null ? manager.ShortName : string.Empty;
                    //workSheet.Cell(13, 3).Value = claim.Manager.SubDivision;
                    //workSheet.Cell(14, 3).Value = claim.DeliveryDateString;
                    //workSheet.Cell(14, 3).DataType = XLCellValues.DateTime;
                    //workSheet.Cell(15, 3).Value = claim.DeliveryPlace;
                    //workSheet.Cell(16, 3).Value = claim.AuctionDateString;
                    //workSheet.Cell(16, 3).DataType = XLCellValues.DateTime;
                    //workSheet.Cell(17, 3).Value = claim.Comment;
                    for (var i = 0; i < productManagers.Count(); i++)
                    {
                        var product = productManagers[i];
                        var cell = userRangeSheet.Cell(i + 1, 2);
                        if (cell != null)
                        {
                            cell.Value = GetUniqueDisplayName(product);
                        }
                    }
                    var namedRange = userRangeSheet.Range(userRangeSheet.Cell(1, 2), userRangeSheet.Cell(productManagers.Count(), 2));
                    //var currencies = db.LoadCurrencies();
                    //for (var i = 0; i < currencies.Count(); i++)
                    //{
                    //    var currency = currencies[i];
                    //    var cell = userRangeSheet.Cell(i + 1, 3);
                    //    if (cell != null)
                    //    {
                    //        cell.Value = currency.Value;
                    //    }
                    //}
                    //var currenciesRange = userRangeSheet.Range(userRangeSheet.Cell(1, 3), userRangeSheet.Cell(currencies.Count(), 3));
                    var workRange = workSheet.Cell(5, 6);
                    if (workRange != null)
                    {
                        var validation = workRange.SetDataValidation();
                        validation.AllowedValues = XLAllowedValues.List;
                        validation.InCellDropdown = true;
                        validation.Operator = XLOperator.Between;
                        validation.List(namedRange);
                    }
                    //workRange = workSheet.Cell(14, 8);
                    //if (workRange != null)
                    //{
                    //    var validation = workRange.SetDataValidation();
                    //    validation.AllowedValues = XLAllowedValues.List;
                    //    validation.InCellDropdown = true;
                    //    validation.Operator = XLOperator.Between;
                    //    validation.List(currenciesRange);
                    //}
                    userRangeSheet.Visibility = XLWorksheetVisibility.Hidden;
                    workSheet.Select();
                    //workSheet.Column(3).AdjustToContents();
                    //workSheet.Column(6).Style.Alignment.WrapText = true;
                    excBook.SaveAs(ms);
                }
                excBook.Dispose();
                excBook = null;
                ms.Seek(0, SeekOrigin.Begin);

            }
            catch (Exception)
            {
                error = true;
            }
            finally
            {
                if (excBook != null)
                {
                    excBook.Dispose();
                }
            }
            if (!error)
            {
                return new FileStreamResult(ms, "application/vnd.ms-excel")
                {
                    FileDownloadName = "Specification.xlsx"
                };
            }
            else
            {
                return View();
            }
        }
 //загрузка списка заявок в excel файл, с учетом фильтра - фильтр передается
 //в параметре modelJson, сериализованный в формат JSON
 public ActionResult GetListExcelFile(string modelJson)
 {
     XLWorkbook excBook = null;
     var ms = new MemoryStream();
     var error = false;
     var message = string.Empty;
     try
     {
         //получение объекта фильтра
         var model = new FilterTenderClaim();
         if (!string.IsNullOrEmpty(modelJson))
         {
             model = JsonConvert.DeserializeObject<FilterTenderClaim>(modelJson);
         }
         if (model.RowCount == 0) model.RowCount = 10;
         //получение отфильтрованной инфы по заявкам из БД
         var db = new DbEngine();
         var list = db.FilterTenderClaims(model);
         var tenderStatus = db.LoadTenderStatus();
         //снабженцв и менеджеры из ActiveDirectory
         var adProductManagers = UserHelper.GetProductManagers();
         var managers = UserHelper.GetManagers();
         if (list.Any())
         {
             db.SetProductManagersForClaims(list);
             var claimProductManagers = list.SelectMany(x => x.ProductManagers).ToList();
             foreach (var claimProductManager in claimProductManagers)
             {
                 var managerFromAD = adProductManagers.FirstOrDefault(x => x.Id == claimProductManager.Id);
                 if (managerFromAD != null)
                 {
                     claimProductManager.Name = managerFromAD.Name;
                     claimProductManager.ShortName = managerFromAD.ShortName;
                 }
             }
             foreach (var claim in list)
             {
                 var manager = managers.FirstOrDefault(x => x.Id == claim.Manager.Id);
                 if (manager != null)
                 {
                     claim.Manager = manager;
                 }
             }
             db.SetStatisticsForClaims(list);
             var dealTypes = db.LoadDealTypes();
             var status = db.LoadClaimStatus();
             //Создание excel файла с инфой о заявках
             excBook = new XLWorkbook();
             var workSheet = excBook.AddWorksheet("Заявки");
             //заголовок
             workSheet.Cell(1, 1).Value = "ID";
             workSheet.Cell(1, 2).Value = "№ Конкурса";
             workSheet.Cell(1, 3).Value = "Контрагент";
             workSheet.Cell(1, 4).Value = "Сумма";
             workSheet.Cell(1, 5).Value = "Менеджер";
             workSheet.Cell(1, 6).Value = "Позиции";
             workSheet.Cell(1, 7).Value = "Снабженцы";
             workSheet.Cell(1, 8).Value = "Тип сделки";
             workSheet.Cell(1, 9).Value = "Статус";
             workSheet.Cell(1, 10).Value = "Создано";
             workSheet.Cell(1, 11).Value = "Срок сдачи";
             workSheet.Cell(1, 12).Value = "Статус конкурса";
             workSheet.Cell(1, 13).Value = "Автор";
             workSheet.Cell(1, 14).Value = "Просроченна";
             var headRange = workSheet.Range(workSheet.Cell(1, 1), workSheet.Cell(1, 14));
             headRange.Style.Font.SetBold(true);
             headRange.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
             headRange.Style.Border.SetBottomBorder(XLBorderStyleValues.Thin);
             headRange.Style.Border.SetBottomBorderColor(XLColor.Gray);
             headRange.Style.Border.SetTopBorder(XLBorderStyleValues.Thin);
             headRange.Style.Border.SetTopBorderColor(XLColor.Gray);
             headRange.Style.Border.SetRightBorder(XLBorderStyleValues.Thin);
             headRange.Style.Border.SetRightBorderColor(XLColor.Gray);
             headRange.Style.Border.SetLeftBorder(XLBorderStyleValues.Thin);
             headRange.Style.Border.SetLeftBorderColor(XLColor.Gray);
             headRange.Style.Fill.BackgroundColor = XLColor.FromArgb(0, 204, 233, 255);
             var row = 2;
             //строки с инфой по заявкам
             foreach (var claim in list)
             {
                 workSheet.Cell(row, 1).Value = claim.Id.ToString("G");
                 workSheet.Cell(row, 2).Value = claim.TenderNumber;
                 workSheet.Cell(row, 3).Value = claim.Customer;
                 workSheet.Cell(row, 4).Value = claim.Sum.ToString("N2");
                 workSheet.Cell(row, 5).Value = claim.Manager.ShortName;
                 workSheet.Cell(row, 6).Value = "Всего: " + claim.PositionsCount + "\rРасчетов: " +
                                                claim.CalculatesCount;
                 workSheet.Cell(row, 7).Value = claim.ProductManagers != null
                     ? string.Join("\r", claim.ProductManagers.Select(x => x.ShortName + " " + x.PositionsCount + "/" + x.CalculatesCount))
                     : string.Empty;
                 workSheet.Cell(row, 8).Value = dealTypes.First(x => x.Id == claim.DealType).Value;
                 workSheet.Cell(row, 9).Value = status.First(x => x.Id == claim.ClaimStatus).Value;
                 workSheet.Cell(row, 10).Value = claim.RecordDate.ToString("dd.MM.yyyy");
                 workSheet.Cell(row, 10).DataType = XLCellValues.DateTime;
                 workSheet.Cell(row, 11).Value = claim.ClaimDeadline.ToString("dd.MM.yyyy");
                 workSheet.Cell(row, 11).DataType = XLCellValues.DateTime;
                 workSheet.Cell(row, 12).Value = tenderStatus.First(x => x.Id == claim.TenderStatus).Value;
                 workSheet.Cell(row, 13).Value = UserHelper.GetUserById(claim.Author.Id).ShortName;
                 var overDie = "Нет";
                 if (claim.ClaimDeadline > DateTime.Now)
                 {
                     if (claim.ClaimStatus != 1 || claim.ClaimStatus != 8)
                     {
                         overDie = "Да";
                     }
                 }
                 workSheet.Cell(row, 14).Value = overDie;
                 row++;
             }
             workSheet.Columns(6, 7).Style.Alignment.WrapText = true;
             workSheet.Columns(1, 14).AdjustToContents();
             excBook.SaveAs(ms);
             excBook.Dispose();
             ms.Seek(0, SeekOrigin.Begin);
         }
         else
         {
             error = true;
             message = "Пустой набор";
         }
     }
     catch (Exception)
     {
         error = true;
         message = "Ошибка сервера";
     }
     finally
     {
         if (excBook != null)
         {
             excBook.Dispose();
         }
     }
     if (!error)
     {
         var date = DateTime.Now.ToString("yyyyMMdd_HHmm");
         return new FileStreamResult(ms, "application/vnd.ms-excel")
         {
             FileDownloadName = "Report-" + date + ".xlsx"
         };
     }
     else
     {
         ViewBag.Message = message;
         return View();
     }
 }
        public ActionResult ListOld()
        {
            //получение пользователя и через наличие у него определенных ролей, определяются настройки по
            //функциональности на странице
            var user = GetUser();
            if (user == null || !UserHelper.IsUserAccess(user))
            {
                var dict = new RouteValueDictionary();
                dict.Add("message", "У Вас нет доступа к приложению");
                return RedirectToAction("ErrorPage", "Auth", dict);
            }
            ViewBag.UserName = user.Name;
            var showCalculate = false;
            var showEdit = false;
            var changeTenderStatus = false;
            var filterProduct = string.Empty;
            var filterManager = string.Empty;
            var clickAction = string.Empty;
            var posibleAction = string.Empty;
            var userId = string.Empty;
            var author = string.Empty;
            var reportExcel = false;
            var deleteClaim = "none";
            var newClaim = "true";
            var filterClaimStatus = new List<int>();
            var isController = UserHelper.IsController(user);
            var isTenderStatus = UserHelper.IsTenderStatus(user);
            var isManager = UserHelper.IsManager(user);
            var isProduct = UserHelper.IsProductManager(user);
            var isOperator = UserHelper.IsOperator(user);
            if (isController)
            {
                showCalculate = true;
                showEdit = true;
                changeTenderStatus = true;
                clickAction = "editClaim";
                posibleAction = "all";
                reportExcel = true;
                deleteClaim = "true";
            }
            else
            {
                if (isTenderStatus)
                {
                    changeTenderStatus = true;
                    clickAction = "null";
                    posibleAction = "null";
                    newClaim = "false";
                    if (isOperator || isManager) newClaim = "true";
                }
                if (isManager)
                {
                    showEdit = true;
                    //filterManager = user.Id;
                    clickAction = "editClaim";
                    posibleAction = "editClaim";
                    userId = user.Id;
                    filterClaimStatus.AddRange(new[] { 1, 2, 3, 6, 7 });
                    deleteClaim = "self&manager";
                }
                if (isProduct)
                {
                    showCalculate = true;
                   // filterProduct = user.Id;
                    clickAction = "calculateClaim";
                    posibleAction = (isManager ? "all" : "calculateClaim");
                    userId = user.Id;
                    newClaim = "false";
                    if (isOperator || isManager) newClaim = "true";
                    if (!isManager) filterClaimStatus.AddRange(new[] { 2, 3, 6, 7 });
                }
                if (isOperator)
                {
                    showEdit = true;
                    clickAction = "editClaim";
                    posibleAction = (isProduct ? "all" : "editClaim");
                    author = user.Id;
                    deleteClaim = "self";
                }
            }
            ViewBag.Settings = new
            {
                showCalculate,
                showEdit,
                changeTenderStatus,
                filterProduct,
                filterManager,
                clickAction,
                posibleAction,
                userId,
                filterClaimStatus,
                author,
                reportExcel,
                deleteClaim,
                newClaim
            };
            ViewBag.Error = false.ToString().ToLower();
            ViewBag.ClaimCount = 0;
            try
            {
                //получение инфы по заявкам из БД
                var db = new DbEngine();
                var filter = new FilterTenderClaim()
                {
                    RowCount = 30,
                };
                var subsProduct = new List<KeyValuePair<string, string>>();
                var subsManagers = new List<KeyValuePair<string, string>>();
                if (!string.IsNullOrEmpty(filterManager)) filter.IdManager = filterManager;
                else
                {
                    if (isManager && !isController)
                    {
                        filter.IdManager = user.Id;
                        subsManagers = Employee.GetSubordinates(user.Id).ToList();
                        if (subsManagers.Any())
                        {
                            filter.IdManager = user.Id;// + ","+ String.Join(",", subsManagers);
                            foreach (var sub in subsManagers)
                            {
                                if (sub.Key != null)
                                {
                                    filter.IdManager += "," + sub.Key;
                                }
                            }
                        }
                    }
                }

                if (!string.IsNullOrEmpty(filterProduct)) filter.IdProductManager = filterProduct;
                else
                {

                    if (isProduct && !isController)
                    {
                        filter.IdProductManager = user.Id;
                        subsProduct = Employee.GetSubordinates(user.Id).ToList();
                        if (subsProduct.Any())
                        {
                            filter.IdProductManager = user.Id;// + "," + String.Join(",", subsProduct);
                            foreach (var sub in subsProduct)
                            {
                                if (sub.Key != null)
                                {
                                    filter.IdProductManager += "," + sub.Key;
                                }
                            }
                        }
                    }
                    //filter.IdProductManager = isProduct && !isController
                    //    ? String.Join(",", Employee.GetSubordinates(user.Id))
                    //    : String.Empty;
                }
                if (!string.IsNullOrEmpty(author)) filter.Author = author;
                if (filterClaimStatus.Any()) filter.ClaimStatus = filterClaimStatus;
                var claims = db.FilterTenderClaims(filter);
                //снабженцы и менеджеры из ActiveDirectory
                var prodManSelList = UserHelper.GetProductManagersSelectionList();

                var adProductManagers = new List<ProductManager>();

                adProductManagers = prodManSelList;

                if (!isController && isProduct)
                {
                    var subProds = Employee.GetSubordinateProductManagers(user.Id, subsProduct);
                    if (subProds.Any())
                    {
                        adProductManagers = subProds;
                    }
                    else
                    {
                        var curProd = new ProductManager() {Id = user.Id, ShortName = user.ShortName};
                        adProductManagers = new List<ProductManager>();
                        adProductManagers.Add(curProd);
                    }
                }
                var manSelList = UserHelper.GetManagersSelectionList();

                var managers = new List<Manager>();

                    managers = manSelList;

                if (!isController && isManager)
                {
                    var subMans = Employee.GetSubordinateManagers(user.Id, subsManagers);
                    if (subMans.Any())
                    {
                        managers = subMans;
                    }
                    else
                    {
                        var curMan = new Manager() { Id = user.Id, ShortName = user.ShortName };
                        managers = new List<Manager>();
                        managers.Add(curMan);
                    }
                }

                //var prodManSelList = UserHelper.Get();

                if (claims != null && claims.Any())
                {
                    db.SetProductManagersForClaims(claims);
                    var claimProductManagers = claims.SelectMany(x => x.ProductManagers).ToList();
                    foreach (var claimProductManager in claimProductManagers)
                    {
                        claimProductManager.ShortName = prodManSelList.FirstOrDefault(x=>x.Id== claimProductManager.Id)?.ShortName;
                        //var productUser = UserHelper.GetUserById(claimProductManager.Id);
                        //if (productUser != null)
                        //{
                        //    claimProductManager.Name = productUser.Name;
                        //    claimProductManager.Name = productUser.ShortName;
                        //}
                        //var managerFromAD = adProductManagers.FirstOrDefault(x => x.Id == claimProductManager.Id);
                        //if (managerFromAD != null)
                        //{
                        //    claimProductManager.Name = managerFromAD.Name;
                        //    claimProductManager.ShortName = managerFromAD.ShortName;
                        //}
                    }
                    //var authorsList = UserHelper.GetAuthorsSelectionList();
                    foreach (var claim in claims)
                    {
                        claim.Manager.ShortName = manSelList.FirstOrDefault(x => x.Id == claim.Manager.Id)?.ShortName;
                        //if (manager != null)
                        //{
                        //    claim.Manager.ShortName = manager.ShortName;
                        //}
                        //var auth = authorsList.SingleOrDefault(x => x.Key == claim.Author.Id);
                        claim.Author = UserHelper.GetUserById(claim.Author.Id);//new UserBase() { Id= auth.Key, ShortName = auth.Value};// UserHelper.GetUserById(claim.Author.Id);

                    }
                    db.SetStatisticsForClaims(claims);
                }
                ViewBag.Claims = claims;
                ViewBag.DealTypes = db.LoadDealTypes();
                ViewBag.ClaimStatus = db.LoadClaimStatus();
                ViewBag.ProductManagers = adProductManagers;
                ViewBag.Managers = managers;
                ViewBag.ClaimCount = db.GetCountFilteredTenderClaims(filter);
                ViewBag.TenderStatus = db.LoadTenderStatus();
            }
            catch (Exception ex)
            {
                ViewBag.Error = true.ToString().ToLower();
            }
            return View();
        }
        //форма заявки, если передан параметр idClaim, то загружается инфа по заявки с этим id
        public ActionResult Index(int? claimId, int? cv)
        {
            var user = GetUser();
            if (!UserHelper.IsController(user) && UserHelper.IsProductManager(user))
                return RedirectToAction("Index", "Calc", new {claimId = claimId, cv = cv});
            if (user == null || !UserHelper.IsUserAccess(user))
            {
                var dict = new RouteValueDictionary();
                dict.Add("message", "У Вас нет доступа к приложению");
                return RedirectToAction("ErrorPage", "Auth", dict);
            }

            if (claimId.HasValue && !cv.HasValue)
            {
                var verList = DbEngine.GetCalcVersionList(claimId.Value);
                if (verList.Any())
                {
                    int lastVersion = verList.Last();
                    return RedirectToAction("Index", new {claimId = claimId, cv = lastVersion});
                }
                else
                {
                    cv = 0;
                }
            }

            //получения текущего юзера и проверка наличия у него доступа к странице
            ViewBag.Error = false.ToString().ToLower();
            TempData["tenderClaimFileFormats"] = WebConfigurationManager.AppSettings["FileFormat4TenderClaimFile"];

            ViewBag.UserName = user.Name;
            var isController = UserHelper.IsController(user);
            var isManager = UserHelper.IsManager(user);
            var isOperator = UserHelper.IsOperator(user);
            if (!isController && !isManager && !isOperator)
            {
                var dict = new RouteValueDictionary();
                dict.Add("message", "У Вас нет доступа к этой странице");
                return RedirectToAction("ErrorPage", "Auth", dict);
            }
            try
            {
                //получение необходимой инфы из БД и ActiveDirectory
                var managers = UserHelper.GetManagers();
                ViewBag.Managers = managers;
                ViewBag.DateStart = DateTime.Now.ToString("dd.MM.yyyy");
                var db = new DbEngine();
                ViewBag.NextDateMin = DateTime.Now.DayOfWeek == DayOfWeek.Friday
                    ? DateTime.Now.AddDays(4).ToShortDateString()
                    : DateTime.Now.AddDays(2).ToShortDateString();
                ViewBag.DealTypes = db.LoadDealTypes();
                ViewBag.ClaimStatus = db.LoadClaimStatus();
                var adProductManagers = UserHelper.GetProductManagers();
                ViewBag.ProductManagers = adProductManagers;
                ViewBag.StatusHistory = new List<ClaimStatusHistory>();
                ViewBag.Facts = db.LoadProtectFacts();
                ViewBag.DeliveryTimes = db.LoadDeliveryTimes();
                ViewBag.HasTransmissedPosition = false.ToString().ToLower();
                ViewBag.Currencies = db.LoadCurrencies();
                TenderClaim claim = null;
                var dealTypeString = String.Empty;
                var tenderStatus = String.Empty;
                if (claimId.HasValue && cv.HasValue)
                {
                    claim = db.LoadTenderClaimById(claimId.Value);
                    if (claim != null)
                    {
                        var allPositions = db.LoadSpecificationPositionsForTenderClaim(claimId.Value, cv.Value);
                        var editablePosIds = new List<int>();
                        foreach (var position in allPositions)
                        {
                           if (position.State == 5) editablePosIds.Add(position.Id);
                        }
                        ViewBag.EditablePositions = editablePosIds;
                        ViewBag.HasTransmissedPosition = db.HasTenderClaimTransmissedPosition(claimId.Value, cv.Value).ToString().ToLower();
                        //проверка наличия доступа к данной заявке
                        if (!isController)
                        {
                            if (claim.Manager.Id == user.Id || claim.Author.Id == user.Id)
                            {

                            }
                            else if (isManager)
                            {
                                var subs = Employee.GetSubordinates(user.Id).ToList();
                                if (!Employee.UserIsSubordinate(subs, claim.Manager.Id) && !Employee.UserIsSubordinate(subs, claim.Author.Id))
                                {
                                    var dict = new RouteValueDictionary();
                                    dict.Add("message", "У Вас нет доступа к этой странице");
                                    return RedirectToAction("ErrorPage", "Auth", dict);
                                }
                            }
                        }

                        var managerFromAd = managers.FirstOrDefault(x => x.Id == claim.Manager.Id);
                        if (managerFromAd != null)
                        {
                            claim.Manager.Name = managerFromAd.Name;
                            claim.Manager.ShortName = managerFromAd.ShortName;
                            claim.Manager.ChiefShortName = managerFromAd.ChiefShortName;
                        }

                        var dealTypes = db.LoadDealTypes();
                        var dealType = dealTypes.FirstOrDefault(x => x.Id == claim.DealType);
                        if (dealType != null)
                        {
                            dealTypeString = dealType.Value;
                        }
                        var tenderStatusList = db.LoadTenderStatus();
                        var status = tenderStatusList.FirstOrDefault(x => x.Id == claim.TenderStatus);
                        if (status != null)
                        {
                            tenderStatus = status.Value;
                        }
                        //получение позиций по заявке и расчета к ним
                        claim.Certs = db.LoadClaimCerts(claimId.Value);
                        claim.Files = db.LoadTenderClaimFiles(claimId.Value);
                        claim.Positions = db.LoadSpecificationPositionsForTenderClaim(claimId.Value, cv.Value);
                        if (claim.Positions != null && claim.Positions.Any())
                        {
                            var productManagers = claim.Positions.Select(x => x.ProductManager).ToList();
                            foreach (var productManager in productManagers)
                            {
                                var productManagerFromAd =
                                    adProductManagers.FirstOrDefault(x => x.Id == productManager.Id);
                                if (productManagerFromAd != null)
                                {
                                    productManager.Name = productManagerFromAd.Name;
                                }
                            }
                            var calculations = db.LoadCalculateSpecificationPositionsForTenderClaim(claimId.Value, cv.Value);
                            if (calculations != null && calculations.Any())
                            {
                                foreach (var position in claim.Positions)
                                {
                                    if (position.State == 1) continue;
                                    position.Calculations =
                                        calculations.Where(x => x.IdSpecificationPosition == position.Id).ToList();
                                    position.Calculations.Reverse();
                                }
                            }
                        }
                        ViewBag.StatusHistory = db.LoadStatusHistoryForClaim(claimId.Value);
                    }
                }
                ViewBag.Claim = claim;
                ViewBag.DealType = dealTypeString;
                ViewBag.status = tenderStatus;
            }
            catch (Exception)
            {
                ViewBag.Error = true.ToString().ToLower();
            }
            return View();
        }
        //Страница расчета позиций по заявке
        public ActionResult Index(int? claimId, int? cv)
        {
            var user = GetUser();
            if (!UserHelper.IsController(user) && (UserHelper.IsManager(user) || UserHelper.IsOperator(user)))
                return RedirectToAction("Index", "Claim", new { claimId = claimId, cv = cv });

            //проверка наличия доступа к странице
            if (user == null || !UserHelper.IsUserAccess(user))
            {
                var dict = new RouteValueDictionary();
                dict.Add("message", "У Вас нет доступа к приложению");
                return RedirectToAction("ErrorPage", "Auth", dict);
            }

            if (claimId.HasValue && !cv.HasValue)
            {
                int lastVersion = DbEngine.GetCalcVersionList(claimId.Value).Last();
                return RedirectToAction("Index", new { claimId = claimId, cv = lastVersion });
            }

            ViewBag.UserName = user.Name;
            var isController = UserHelper.IsController(user);
            var isProduct = UserHelper.IsProductManager(user);
            if (!isController && !isProduct)
            {
                var dict = new RouteValueDictionary();
                dict.Add("message", "У Вас нет доступа к этой странице");
                return RedirectToAction("ErrorPage", "Auth", dict);
            }
            ViewBag.Error = false.ToString().ToLower();
            ViewBag.DealType = string.Empty;
            ViewBag.Status = string.Empty;
            ViewBag.StatusHistory = new List<ClaimStatusHistory>();
            var newClaim = true;
            if (!isController) newClaim = false;
            ViewBag.NewClaim = newClaim.ToString().ToLower();
            try
            {
                //получение инфы по заявке и сопутствующих справочников
                var db = new DbEngine();
                TenderClaim claim = null;
                var dealTypeString = string.Empty;
                var tenderStatus = string.Empty;
                ViewBag.ClaimStatus = db.LoadClaimStatus();
                ViewBag.Currencies = db.LoadCurrencies();
                ViewBag.DeliveryTimes = db.LoadDeliveryTimes();
                if (claimId.HasValue)
                {
                    claim = db.LoadTenderClaimById(claimId.Value);
                    claim.Certs = db.LoadClaimCerts(claimId.Value);
                    claim.Files = db.LoadTenderClaimFiles(claimId.Value);
                    var adProductsManager = UserHelper.GetProductManagers();
                    if (claim != null)
                    {
                        //if (claim.ClaimStatus == 1)
                        //{
                        //    var dict = new RouteValueDictionary();
                        //    dict.Add("message", "Статус заявки не позволяет производить расчет позиций");
                        //    return RedirectToAction("ErrorPage", "Auth", dict);
                        //}
                        //позиции заявки, в зависимости от роли юзера
                        if (!isController)
                        {
                            claim.Positions = db.LoadSpecificationPositionsForTenderClaimForProduct(claimId.Value,
                                user.Id, cv.Value);
                        }
                        else
                        {
                            claim.Positions = db.LoadSpecificationPositionsForTenderClaim(claimId.Value, cv.Value);
                        }
                        if (claim.Positions != null && claim.Positions.Any())
                        {
                            //изменение статуса  в Работе если это первая загрузка для расчета по данной заявке
                            if (claim.ClaimStatus == 2)
                            {
                                claim.ClaimStatus = 3;
                                DbEngine.ChangeTenderClaimClaimStatus(claim);
                                var statusHistory = new ClaimStatusHistory()
                                {
                                    IdClaim = claim.Id,
                                    Date = DateTime.Now,
                                    Comment = string.Empty,
                                    Status = new ClaimStatus() { Id = claim.ClaimStatus },
                                    IdUser = user.Id
                                };
                                db.SaveClaimStatusHistory(statusHistory);
                            }
                            //менеджеры и снабженцы из ActiveDirectory
                            var managerFromAd = UserHelper.GetUserById(claim.Manager.Id);
                            claim.Manager.Name = managerFromAd.Name;
                            claim.Manager.ShortName = managerFromAd.ShortName;
                            claim.Manager.ChiefShortName = managerFromAd.ManagerName;
                            //var managers = UserHelper.GetManagers();
                            //var managerFromAd = managers.FirstOrDefault(x => x.Id == claim.Manager.Id);
                            //if (managerFromAd != null)
                            //{
                            //    claim.Manager.Name = managerFromAd.Name;
                            //    claim.Manager.ShortName = managerFromAd.ShortName;
                            //    claim.Manager.ChiefShortName = managerFromAd.ChiefShortName;
                            //}

                            var subordinateList = Employee.GetSubordinates(user.Id);

                            var productManagers = claim.Positions.Select(x => x.ProductManager).ToList();
                            var prodManSelList = UserHelper.GetProductManagersSelectionList();
                            bool hasAccess = isController || claim.Positions.Any(x => x.ProductManager.Id == user.Id);

                            foreach (var productManager in productManagers)
                            {
                                hasAccess = hasAccess || (subordinateList.Any() && Employee.UserIsSubordinate(subordinateList, productManager.Id));// subordinateList.ToList().Contains(productManager.Id);
                                productManager.ShortName = prodManSelList.FirstOrDefault(x => x.Id == productManager.Id)?.ShortName;
                                //var productUser = UserHelper.GetUserById(productManager.Id);
                                //if (productUser != null)
                                //{
                                //    productManager.Name = productUser.Name;
                                //    productManager.ShortName = productUser.ShortName;
                                //}
                                //var productManagerFromAd = adProductsManager.First(x => x.Id == productManager.Id);
                                //if (productManagerFromAd != null)
                                //{
                                //    productManager.Name = productManagerFromAd.Name;
                                //    productManager.ShortName = productManagerFromAd.ShortName;
                                //}
                            }
                            if (!hasAccess)
                            {
                                var dict = new RouteValueDictionary();
                                dict.Add("message", "У Вас нет доступа к этой заявке, Вам не назначены позиции для расчета");
                                return RedirectToAction("ErrorPage", "Auth", dict);
                            }
                            //Расчет по позициям
                            var calculations = db.LoadCalculateSpecificationPositionsForTenderClaim(claimId.Value, cv.Value);
                            if (calculations != null && calculations.Any())
                            {
                                foreach (var position in claim.Positions)
                                {
                                    position.Calculations =
                                        calculations.Where(x => x.IdSpecificationPosition == position.Id).ToList();
                                    position.Calculations.Reverse();
                                }
                            }
                        }
                        else
                        {
                            if (isController)
                            {
                                var dict = new RouteValueDictionary();
                                dict.Add("message", "У заявки нет позиций");
                                return RedirectToAction("ErrorPage", "Auth", dict);
                            }
                            else
                            {
                                var dict = new RouteValueDictionary();
                                dict.Add("message", "У Вас нет доступа к этой заявке, Вам не назначены позиции для расчета");
                                return RedirectToAction("ErrorPage", "Auth", dict);
                            }
                        }
                        var dealTypes = db.LoadDealTypes();
                        var dealType = dealTypes.FirstOrDefault(x => x.Id == claim.DealType);
                        if (dealType != null)
                        {
                            dealTypeString = dealType.Value;
                        }
                        var tenderStatusList = db.LoadTenderStatus();
                        var status = tenderStatusList.FirstOrDefault(x => x.Id == claim.TenderStatus);
                        if (status != null)
                        {
                            tenderStatus = status.Value;
                        }
                        ViewBag.StatusHistory = db.LoadStatusHistoryForClaim(claimId.Value);
                        ViewBag.ProductManagers = adProductsManager;
                    }
                }
                ViewBag.Claim = claim;
                ViewBag.DealType = dealTypeString;
                ViewBag.Status = tenderStatus;
                ViewBag.ProtectFacts = db.LoadProtectFacts();
            }
            catch (Exception ex)
            {
                ViewBag.Error = true.ToString().ToLower();
            }
            return View();
        }
        //Excel
        //получение excel файла с инфой по позициям и расчетам к ним
        public ActionResult GetSpecificationFile(int claimId, bool forManager, int cv)
        {
            XLWorkbook excBook = null;
            var ms = new MemoryStream();
            var error = false;
            var message = string.Empty;
            try
            {
                var user = GetUser();
                var db = new DbEngine();
                var positions = new List<SpecificationPosition>();
                //получение позиций исходя из роли юзера
                if (UserHelper.IsController(user) || UserHelper.IsManager(user))
                {
                    positions = db.LoadSpecificationPositionsForTenderClaim(claimId, cv);
                }
                else
                {
                    if (UserHelper.IsProductManager(user))
                    {
                        positions = db.LoadSpecificationPositionsForTenderClaimForProduct(claimId, user.Id, cv);
                    }
                }
                if (positions.Any())
                {
                    //if (forManager) positions = positions.Where(x => x.State == 2 || x.State == 4).ToList();
                    //else positions = positions.Where(x => x.State == 1 || x.State == 3).ToList();
                    if (positions.Any())
                    {
                        //расчет к позициям
                        var calculations = db.LoadCalculateSpecificationPositionsForTenderClaim(claimId, cv);
                        if (calculations != null && calculations.Any())
                        {
                            foreach (var position in positions)
                            {
                                if (UserHelper.IsManager(user) && position.State == 1 && !UserHelper.IsController(user) && !UserHelper.IsProductManager(user)) continue;
                                position.Calculations =
                                    calculations.Where(x => x.IdSpecificationPosition == position.Id).ToList();
                            }
                        }
                        var filePath = Path.Combine(Server.MapPath("~"), "App_Data", "Template.xlsx");
                        using (var fs = System.IO.File.OpenRead(filePath))
                        {
                            var buffer = new byte[fs.Length];
                            fs.Read(buffer, 0, buffer.Count());
                            ms.Write(buffer, 0, buffer.Count());
                            ms.Seek(0, SeekOrigin.Begin);
                        }
                        //создание excel файла
                        excBook = new XLWorkbook(ms);
                        var workSheet = excBook.Worksheet("WorkSheet");
                        workSheet.Name = "Расчет";
                        var claim = db.LoadTenderClaimById(claimId);
                        //>>>>>>>>Шапка - Заполнение инфы о заявке<<<<<<
                        var dealTypes = db.LoadDealTypes();
                        var manager = UserHelper.GetUserById(claim.Manager.Id);
                        //workSheet.Cell(1, 3).Value = !claim.CurrencyUsd.Equals(0)
                        //    ? claim.CurrencyUsd.ToString("N2")
                        //    : string.Empty;
                        //workSheet.Cell(2, 3).Value = !claim.CurrencyEur.Equals(0)
                        //    ? claim.CurrencyEur.ToString("N2")
                        //    : string.Empty;
                        //workSheet.Cell(1, 3).DataType = XLCellValues.Number;
                        //workSheet.Cell(2, 3).DataType = XLCellValues.Number;
                        workSheet.Cell(1, 4).Value = claim.TenderNumber;
                        //workSheet.Cell(4, 3).Value = claim.TenderStartString;
                        //workSheet.Cell(4, 3).DataType = XLCellValues.DateTime;
                        //workSheet.Cell(5, 3).Value = claim.ClaimDeadlineString;
                        //workSheet.Cell(5, 3).DataType = XLCellValues.DateTime;
                        //workSheet.Cell(6, 3).Value = claim.KPDeadlineString;
                        //workSheet.Cell(6, 3).DataType = XLCellValues.DateTime;
                        workSheet.Cell(2, 4).Value = claim.Customer;
                        //workSheet.Cell(8, 3).Value = claim.CustomerInn;
                        //workSheet.Cell(9, 3).Value = !claim.Sum.Equals(0) ? claim.Sum.ToString("N2") : string.Empty;
                        //workSheet.Cell(10, 3).Value = dealTypes.First(x => x.Id == claim.DealType).Value;
                        //workSheet.Cell(11, 3).Value = claim.TenderUrl;
                        workSheet.Cell(3, 4).Value = manager != null ? manager.ShortName : string.Empty;
                        //workSheet.Cell(13, 3).Value = claim.Manager.SubDivision;
                        //workSheet.Cell(14, 3).Value = claim.DeliveryDateString;
                        //workSheet.Cell(14, 3).DataType = XLCellValues.DateTime;
                        //workSheet.Cell(15, 3).Value = claim.DeliveryPlace;
                        //workSheet.Cell(16, 3).Value = claim.AuctionDateString;
                        //workSheet.Cell(16, 3).DataType = XLCellValues.DateTime;
                        //workSheet.Cell(17, 3).Value = claim.Comment;
                        var directRangeSheet = excBook.AddWorksheet("Справочники");
                        //создание дипазона выбора значений Факт получения защиты
                        var facts = db.LoadProtectFacts();
                        //var currencies = db.LoadCurrencies();
                        var deliveryTimes = db.LoadDeliveryTimes();

                        var deliveryTimesList = deliveryTimes.Select(x => x.Value).ToList();
                        for (var i = 0; i < deliveryTimesList.Count(); i++)
                        {
                            var time = deliveryTimesList[i];
                            var cell = directRangeSheet.Cell(i + 1, 3);
                            if (cell != null)
                            {
                                cell.Value = time;
                            }
                        }

                        var protectFactList = facts.Select(x => x.Value).ToList();
                        for (var i = 0; i < protectFactList.Count(); i++)
                        {
                            var protectFact = protectFactList[i];
                            var cell = directRangeSheet.Cell(i + 1, 1);
                            if (cell != null)
                            {
                                cell.Value = protectFact;
                            }
                        }

                        //var availableCurrencies = currencies.Where(x => x.Value.ToLowerInvariant() != "руб").ToList();
                        //for (var i = 0; i < currencies.Count(); i++)
                        //{
                        //    var currency = currencies[i];
                        //    var cell = directRangeSheet.Cell(i + 1, 2);
                        //    if (cell != null)
                        //    {
                        //        cell.Value = currency.Value;
                        //    }
                        //}

                        var protectFactRange = directRangeSheet.Range(directRangeSheet.Cell(1, 1),
                            directRangeSheet.Cell(protectFactList.Count(), 1));
                        //var currenciesRange = directRangeSheet.Range(directRangeSheet.Cell(1, 2),
                        //    directRangeSheet.Cell(currencies.Count(), 2));
                        var deliveryTimeRange = directRangeSheet.Range(directRangeSheet.Cell(1, 3),
                            directRangeSheet.Cell(deliveryTimes.Count(), 3));

                        directRangeSheet.Visibility = XLWorksheetVisibility.Hidden;
                        //>>>>>>>номер строки начало вывода инфы<<<<<<
                        var row = 4;

                        //В первой колонке храним id шники
                        workSheet.Column(1).Hide();

                        //вывод инфы по позициям
                        //workSheet.Cell(row, 2).Value = "Запрос";

                        //заголовок для строк расчета
                        //workSheet.Cell(row, 3).Value = "Каталожный номер*";
                        //workSheet.Cell(row, 4).Value = "Наименование*";
                        //workSheet.Cell(row, 5).Value = "Замена";
                        //workSheet.Cell(row, 6).Value = "Цена USD";
                        //workSheet.Cell(row, 7).Value = "Цена EUR";
                        //workSheet.Cell(row, 8).Value = "Цена EUR Ricoh";
                        //workSheet.Cell(row, 9).Value = "Цена руб";
                        //workSheet.Cell(row, 10).Value = "Поставщик";
                        //workSheet.Cell(row, 11).Value = "Срок поставки";
                        //workSheet.Cell(row, 12).Value = "Факт защиты*";
                        //workSheet.Cell(row, 13).Value = "Условия защиты";
                        //workSheet.Cell(row, 14).Value = "Комментарий";
                        //workSheet.Range(workSheet.Cell(row, 2), workSheet.Cell(row, 14)).Style.Font.SetBold(true);
                        var posCounter = 0;

                        foreach (var position in positions)
                        {
                            //заголовок и данные по позиции

                            //workSheet.Cell(row, 1).Value = "Каталожный номер";
                            //workSheet.Cell(row, 2).Value = "Наименование";
                            //workSheet.Cell(row, 3).Value = "Замена";
                            //workSheet.Cell(row, 3).Value = "Единица";
                            //workSheet.Cell(row, 4).Value = "Количество";
                            //workSheet.Cell(row, 5).Value = "Комментарий";
                            //workSheet.Cell(row, 7).Value = "Сумма, максимум";
                            //workSheet.Cell(row, 8).Value = "Id";
                            //workSheet.Cell(row, 9).Value = "Сумма с ТЗР";
                            //workSheet.Cell(row, 10).Value = "Сумма с НДС";

                            row++;

                            var idCell = workSheet.Cell(row, 1);
                            idCell.Value = position.Id;
                            workSheet.Cell(row, 2).Value = ++posCounter;
                            workSheet.Cell(row, 2).Style.Font.SetBold();
                            workSheet.Cell(row, 2).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                            workSheet.Cell(row, 2).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);

                            workSheet.Cell(row, 3).Value = position.CatalogNumber;
                            var posCell = workSheet.Cell(row, 4);
                            posCell.Value = String.Format("{2}\r\n{5}", position.Id, position.CatalogNumber, position.Name, GetUnitString(position.Unit), position.Value, position.Comment);
                            posCell.Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
                            posCell.Style.Alignment.SetWrapText();
                            workSheet.Row(row).AdjustToContents();

                            workSheet.Cell(row, 5).Value = String.Format("{1} {0}", GetUnitString(position.Unit),
                                position.Value);

                            //Объединяем две ячейки чтобы удобнее было добавлять строки пользователям руками
                            workSheet.Range(workSheet.Cell(row, 1), workSheet.Cell(row + 1, 1)).Merge();
                            workSheet.Range(workSheet.Cell(row, 2), workSheet.Cell(row + 1, 2)).Merge();
                            workSheet.Range(workSheet.Cell(row, 3), workSheet.Cell(row + 1, 3)).Merge();
                            workSheet.Range(workSheet.Cell(row, 4), workSheet.Cell(row + 1, 4)).Merge();
                            workSheet.Range(workSheet.Cell(row, 5), workSheet.Cell(row + 1, 5)).Merge();
                            //workSheet.Rows(row, row+1).AdjustToContents();

                            //workSheet.Cell(row, 1).Value = position.CatalogNumber;
                            //workSheet.Cell(row, 2).Value = position.Name;
                            ////workSheet.Cell(row, 3).Value = position.Replace;
                            //workSheet.Cell(row, 3).Value = GetUnitString(position.Unit);
                            //workSheet.Cell(row, 4).Value = position.Value;
                            //workSheet.Cell(row, 5).Value = position.Comment;
                            //var currency = currencies.First(x => x.Id == position.Currency);
                            //workSheet.Cell(row, 7).Value = !position.Sum.Equals(0)
                            //    ? position.Sum.ToString("N2") + " " + currency.Value
                            //    : string.Empty;
                            //workSheet.Cell(row, 9).Value = !position.Sum.Equals(0)
                            //    ? position.SumTzr.ToString("N2") + " " + currency.Value
                            //    : string.Empty;
                            //workSheet.Cell(row, 10).Value = !position.Sum.Equals(0)
                            //    ? position.SumNds.ToString("N2") + " " + currency.Value
                            //    : string.Empty;
                            //workSheet.Cell(row, 8).Value = position.Id;
                            //var positionRange = workSheet.Range(workSheet.Cell(row - 1, 1), workSheet.Cell(row, 5));
                            //positionRange.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                            //positionRange.Style.Border.SetBottomBorder(XLBorderStyleValues.Thin);
                            //positionRange.Style.Border.SetBottomBorderColor(XLColor.Gray);
                            //positionRange.Style.Border.SetTopBorder(XLBorderStyleValues.Thin);
                            //positionRange.Style.Border.SetTopBorderColor(XLColor.Gray);
                            //positionRange.Style.Border.SetRightBorder(XLBorderStyleValues.Thin);
                            //positionRange.Style.Border.SetRightBorderColor(XLColor.Gray);
                            //positionRange.Style.Border.SetLeftBorder(XLBorderStyleValues.Thin);
                            //positionRange.Style.Border.SetLeftBorderColor(XLColor.Gray);
                            //positionRange.Style.Fill.BackgroundColor = XLColor.FromArgb(0, 204, 233, 255);
                            //row++;
                            ////заголовок для строк расчета
                            //workSheet.Cell(row, 1).Value = "Каталожный номер*";
                            //workSheet.Cell(row, 2).Value = "Наименование*";
                            //workSheet.Cell(row, 3).Value = "Замена";
                            //workSheet.Cell(row, 4).Value = "Цена за ед.";
                            //workSheet.Cell(row, 5).Value = "Сумма вход";
                            //workSheet.Cell(row, 6).Value = "Валюта";
                            ////workSheet.Cell(row, 7).Value = "Цена за ед. руб";
                            ////workSheet.Cell(row, 9).Value = "Сумма вход руб*";
                            //workSheet.Cell(row, 7).Value = "Поставщик";
                            //workSheet.Cell(row, 8).Value = "callHd";
                            //workSheet.Cell(row, 9).Value = "Факт получ.защиты*";
                            //workSheet.Cell(row, 10).Value = "Условия защиты";
                            //workSheet.Cell(row, 11).Value = "Комментарий";
                            //var calcHeaderRange = workSheet.Range(workSheet.Cell(row, 1), workSheet.Cell(row, 11));
                            //calcHeaderRange.Style.Font.SetBold(true);
                            //calcHeaderRange.Style.Fill.BackgroundColor = XLColor.FromArgb(0, 204, 255, 209);
                            //calcHeaderRange.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                            //calcHeaderRange.Style.Border.SetBottomBorder(XLBorderStyleValues.Thin);
                            //calcHeaderRange.Style.Border.SetBottomBorderColor(XLColor.Gray);
                            //calcHeaderRange.Style.Border.SetTopBorder(XLBorderStyleValues.Thin);
                            //calcHeaderRange.Style.Border.SetTopBorderColor(XLColor.Gray);
                            //calcHeaderRange.Style.Border.SetRightBorder(XLBorderStyleValues.Thin);
                            //calcHeaderRange.Style.Border.SetRightBorderColor(XLColor.Gray);
                            //calcHeaderRange.Style.Border.SetLeftBorder(XLBorderStyleValues.Thin);
                            //calcHeaderRange.Style.Border.SetLeftBorderColor(XLColor.Gray);

                            var firstPosRow = row;
                            //вывод инфы по расчету к позиции
                            if (position.Calculations != null && position.Calculations.Any())
                            {
                                var calcCount = 1;

                                foreach (var calculation in position.Calculations)
                                {
                                    ExcelDataFormatCalcRow(ref workSheet, row, deliveryTimeRange, protectFactRange);

                                    if (calcCount > 1)
                                    {
                                        row++;

                                        ExcelDataFormatCalcRow(ref workSheet, row, deliveryTimeRange, protectFactRange);

                                        workSheet.Range(workSheet.Cell(firstPosRow, 1), workSheet.Cell(row, 1)).Merge();
                                        workSheet.Range(workSheet.Cell(firstPosRow, 2), workSheet.Cell(row, 2)).Merge();
                                        workSheet.Range(workSheet.Cell(firstPosRow, 3), workSheet.Cell(row, 3)).Merge();
                                        workSheet.Range(workSheet.Cell(firstPosRow, 4), workSheet.Cell(row , 4)).Merge();
                                        workSheet.Range(workSheet.Cell(firstPosRow, 5), workSheet.Cell(row, 5)).Merge();
                                    }

                                    workSheet.Cell(row, 6).Value = calculation.CatalogNumber;
                                    workSheet.Cell(row, 7).Value = calculation.Name;
                                    workSheet.Cell(row, 7).Style.Alignment.SetWrapText();
                                    workSheet.Cell(row, 8).Value = calculation.Replace;
                                    workSheet.Cell(row, 8).Style.Alignment.SetWrapText();

                                    workSheet.Cell(row, 9).Value = calculation.PriceUsd;
                                    workSheet.Cell(row, 10).Value = calculation.PriceEur;
                                    workSheet.Cell(row, 11).Value = calculation.PriceEurRicoh;
                                    workSheet.Cell(row, 12).Value = calculation.PriceRubl;
                                    workSheet.Cell(row, 13).Value = calculation.Provider;
                                    workSheet.Cell(row, 13).Style.Alignment.SetWrapText();

                                    if (calculation.DeliveryTime != null) workSheet.Cell(row, 14).Value = deliveryTimes.First(x => x.Id == calculation.DeliveryTime.Id).Value;
                                    workSheet.Cell(row, 14).Style.Alignment.SetWrapText();

                                    if (calculation.ProtectFact != null) workSheet.Cell(row, 15).Value = facts.First(x => x.Id == calculation.ProtectFact.Id).Value;

                                    workSheet.Cell(row, 16).Value = calculation.ProtectCondition;
                                    workSheet.Cell(row, 16).Style.Alignment.SetWrapText();
                                    workSheet.Cell(row, 17).Value = calculation.Comment;
                                    workSheet.Cell(row, 17).Style.Alignment.SetWrapText();

                                    calcCount++;
                                }

                                //foreach (var calculation in position.Calculations)
                                //{
                                //    row++;
                                //    workSheet.Cell(row, 1).Value = calculation.CatalogNumber;
                                //    workSheet.Cell(row, 2).Value = calculation.Name;
                                //    workSheet.Cell(row, 3).Value = calculation.Replace;
                                //    workSheet.Cell(row, 4).Value = !calculation.PriceCurrency.Equals(0)
                                //        ? calculation.PriceCurrency.ToString("N2")
                                //        : string.Empty;
                                //    workSheet.Cell(row, 5).Value = !calculation.SumCurrency.Equals(0)
                                //        ? calculation.SumCurrency.ToString("N2")
                                //        : string.Empty;
                                //    var validation = workSheet.Cell(row, 6).SetDataValidation();
                                //    validation.AllowedValues = XLAllowedValues.List;
                                //    validation.InCellDropdown = true;
                                //    validation.Operator = XLOperator.Between;
                                //    validation.List(currenciesRange);
                                //    workSheet.Cell(row, 6).Value =
                                //        currencies.First(x => x.Id == calculation.Currency).Value;
                                //    //workSheet.Cell(row, 7).Value = !calculation.PriceRub.Equals(0)
                                //    //    ? calculation.PriceRub.ToString("N2")
                                //    //    : string.Empty;
                                //    //workSheet.Cell(row, 9).Value = !calculation.SumRub.Equals(0)
                                //    //    ? calculation.SumRub.ToString("N2")
                                //    //    : string.Empty;
                                //    workSheet.Cell(row, 7).Value = calculation.Provider;
                                //    validation = workSheet.Cell(row, 9).SetDataValidation();
                                //    validation.AllowedValues = XLAllowedValues.List;
                                //    validation.InCellDropdown = true;
                                //    validation.Operator = XLOperator.Between;
                                //    validation.List(protectFactRange);
                                //    workSheet.Cell(row, 9).Value =
                                //        facts.First(x => x.Id == calculation.ProtectFact.Id).Value;
                                //    workSheet.Cell(row, 10).Value = calculation.ProtectCondition;
                                //    workSheet.Cell(row, 11).Value = calculation.Comment;
                                //}
                            }
                            else
                            {

                                //var validation = workSheet.Cell(row, 12).SetDataValidation();
                                //validation.AllowedValues = XLAllowedValues.List;
                                //validation.InCellDropdown = true;
                                //validation.Operator = XLOperator.Between;
                                //validation.List(deliveryTimeRange);

                                //validation = workSheet.Cell(row, 13).SetDataValidation();
                                //validation.AllowedValues = XLAllowedValues.List;
                                //validation.InCellDropdown = true;
                                //validation.Operator = XLOperator.Between;
                                //validation.List(protectFactRange);

                                ExcelDataFormatCalcRow(ref workSheet, row, deliveryTimeRange, protectFactRange);
                                //Специально добавляем строчу так как мы делаем специально две на позицию чтобы ыбло удобнее добавлять руками в Экселе
                                row++;
                                ExcelDataFormatCalcRow(ref workSheet, row, deliveryTimeRange, protectFactRange);

                                //row++;
                                //var validation = workSheet.Cell(row, 6).SetDataValidation();
                                //validation.AllowedValues = XLAllowedValues.List;
                                //validation.InCellDropdown = true;
                                //validation.Operator = XLOperator.Between;
                                //validation.List(currenciesRange);
                                //validation = workSheet.Cell(row, 9).SetDataValidation();
                                //validation.AllowedValues = XLAllowedValues.List;
                                //validation.InCellDropdown = true;
                                //validation.Operator = XLOperator.Between;
                                //validation.List(protectFactRange);
                            }
                            //row++;
                        }

                        var list = workSheet.Range(workSheet.Cell(4, 1), workSheet.Cell(row, 17));

                        list.Style.Border.SetBottomBorder(XLBorderStyleValues.Thin);
                        list.Style.Border.SetBottomBorderColor(XLColor.Gray);
                        list.Style.Border.SetTopBorder(XLBorderStyleValues.Thin);
                        list.Style.Border.SetTopBorderColor(XLColor.Gray);
                        list.Style.Border.SetRightBorder(XLBorderStyleValues.Thin);
                        list.Style.Border.SetRightBorderColor(XLColor.Gray);
                        list.Style.Border.SetLeftBorder(XLBorderStyleValues.Thin);
                        list.Style.Border.SetLeftBorderColor(XLColor.Gray);

                        //workSheet.Columns(1, 11).AdjustToContents();
                        //workSheet.Column(8).Hide();
                        excBook.SaveAs(ms);
                        excBook.Dispose();
                        ms.Seek(0, SeekOrigin.Begin);
                    }
                    else
                    {
                        error = true;
                        message = "Нет позиций для расчета";
                    }
                }
                else
                {
                    error = true;
                    message = "Нет позиций для расчета";
                }
            }
            catch (Exception)
            {
                error = true;
                message = "Ошибка сервера";
            }
            finally
            {
                if (excBook != null)
                {
                    excBook.Dispose();
                }
            }
            if (!error)
            {
                return new FileStreamResult(ms, "application/vnd.ms-excel")
                {
                    FileDownloadName = "Specification_" + claimId + ".xlsx"
                };
            }
            else
            {
                ViewBag.Message = message;
                return View();
            }
        }