//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(); } }
//форма заявки, если передан параметр 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(); }
//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(); } }
//Страница расчета позиций по заявке 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(); }
public ActionResult UploadFileForm(HttpPostedFileBase file, int claimId, int cv) { var error = false; var message = string.Empty; XLWorkbook excBook = null; Stream inputStream = null; var positions = new List<SpecificationPosition>(); try { if (file == null || !file.FileName.EndsWith(".xlsx")) { error = true; message = "Файл не предоставлен или имеет неверный формат"; } else { inputStream = file.InputStream; inputStream.Seek(0, SeekOrigin.Begin); excBook = new XLWorkbook(inputStream); var workSheet = excBook.Worksheet("Расчет"); //разбор полученного файла if (workSheet != null) { var user = GetUser(); //<<<<<<<Номер строки - начало разбора инфы>>>>>> var row = 5; var errorStringBuilder = new StringBuilder(); var db = new DbEngine(); var emptyRowCount = 0; SpecificationPosition model = null; CalculateSpecificationPosition calculate = null; var protectFacts = db.LoadProtectFacts(); var deliveryTimes = db.LoadDeliveryTimes(); var currencies = db.LoadCurrencies(); var adProductManagers = UserHelper.GetProductManagers(); int? idPos = null; //проход по всем строкам while (true) { var rowValid = true; var controlCell = workSheet.Cell(row, 1); //определение типа строки var controlValue = controlCell.Value; bool isCalcRow = false; if (controlValue != null && String.IsNullOrEmpty(controlValue.ToString()) && controlCell.IsMerged() && idPos.HasValue) { controlValue = idPos.Value; isCalcRow = true; } if (controlValue != null || isCalcRow) { if (!isCalcRow) { var controlValueString = controlValue.ToString(); if (string.IsNullOrEmpty(controlValueString)) { //Если строка запроса пустая то Конец if (!workSheet.Cell(row, 3).IsMerged() && String.IsNullOrEmpty(workSheet.Cell(row, 3).Value.ToString().Trim())) { break; } //строка расчета errorStringBuilder.Append("Не найден идентификатор позиции в строке: " + row + "<br/>"); break; } else { int id; var converting = int.TryParse(controlValueString, out id); if (converting) { model = new SpecificationPosition() { Calculations = new List<CalculateSpecificationPosition>(), Author = user.Id }; model.Id = id; idPos = id; positions.Add(model); } else { errorStringBuilder.Append("Ошибка разбора Id позиции в строке: " + row + "<br/>"); break; } } } } //разбор инфы по расчету к позиции //Если строка расчета не пустая, то парсим ее bool flag4Parse = false; for (int i = 4; i <= 15; i++) { if (!String.IsNullOrEmpty(workSheet.Cell(row, i).Value.ToString().Trim())) { flag4Parse = true; break; } } if (flag4Parse) { calculate = new CalculateSpecificationPosition() { IdSpecificationPosition = model.Id, IdTenderClaim = claimId, Author = user.Id }; //получение значений расчета из ячеек var catalogValue = workSheet.Cell(row, 6).Value; var nameValue = workSheet.Cell(row, 7).Value; var replaceValue = workSheet.Cell(row, 8).Value; var priceUsd = workSheet.Cell(row, 9).Value; var priceEur = workSheet.Cell(row, 10).Value; var priceEurRicoh = workSheet.Cell(row, 11).Value; var priceRubl = workSheet.Cell(row, 12).Value; var providerValue = workSheet.Cell(row, 13).Value; var deliveryTimeValue = workSheet.Cell(row, 14).Value; var protectFactValue = workSheet.Cell(row, 15).Value; var protectConditionValue = workSheet.Cell(row, 16).Value; var commentValue = workSheet.Cell(row, 17).Value; //Проверка if (deliveryTimeValue != null && string.IsNullOrEmpty(deliveryTimeValue.ToString().Trim())) { rowValid = false; errorStringBuilder.Append("Строка: " + row + ", не задано обязательное значение Срок поставки<br/>"); } if ((priceUsd != null && string.IsNullOrEmpty(priceUsd.ToString().Trim())) && (priceEur != null && string.IsNullOrEmpty(priceEur.ToString().Trim())) && (priceEurRicoh != null && string.IsNullOrEmpty(priceEurRicoh.ToString().Trim())) && (priceRubl != null && string.IsNullOrEmpty(priceRubl.ToString().Trim()))) { rowValid = false; errorStringBuilder.Append("Строка: " + row + ", не указано ни одной цены<br/>"); } //Заполняем calculate.CatalogNumber = catalogValue.ToString(); calculate.Name = nameValue.ToString(); calculate.Replace = replaceValue.ToString(); double prUsd; if (!String.IsNullOrEmpty(priceUsd.ToString().Trim()) && double.TryParse(priceUsd.ToString().Trim(), out prUsd)) { calculate.PriceUsd = prUsd; } double prEur; if (!String.IsNullOrEmpty(priceEur.ToString().Trim()) && double.TryParse(priceEur.ToString().Trim(), out prEur)) { calculate.PriceEur = prEur; } double prEurRicoh; if (!String.IsNullOrEmpty(priceEurRicoh.ToString().Trim()) && double.TryParse(priceEurRicoh.ToString().Trim(), out prEurRicoh)) { calculate.PriceEurRicoh = prEurRicoh; } double prRubl; if (!String.IsNullOrEmpty(priceRubl.ToString().Trim()) && double.TryParse(priceRubl.ToString().Trim(), out prRubl)) { calculate.PriceRubl = prRubl; } calculate.Provider = providerValue.ToString(); var delivertTimeValueString = deliveryTimeValue.ToString().Trim(); var possibleDelTimValues = deliveryTimes.Select(x => x.Value); if (!possibleDelTimValues.Contains(delivertTimeValueString)) { rowValid = false; errorStringBuilder.Append("Строка: " + row + ", Значение '" + delivertTimeValueString + "' не является допустимым для Срок поставки<br/>"); } else { var delTime = deliveryTimes.First(x => x.Value == delivertTimeValueString); calculate.DeliveryTime = delTime; } var protectFactValueString = protectFactValue.ToString().Trim(); var possibleValues = protectFacts.Select(x => x.Value); if (!possibleValues.Contains(protectFactValueString)) { //rowValid = false; //errorStringBuilder.Append("Строка: " + row + // ", Значение '" + protectFactValueString + "' не является допустимым для Факт получ.защиты<br/>"); calculate.ProtectFact = null; } else { var fact = protectFacts.First(x => x.Value == protectFactValueString); calculate.ProtectFact = fact; } calculate.ProtectCondition = protectConditionValue.ToString(); calculate.Comment = commentValue.ToString(); //Если есть ошибки то не добавляем if (rowValid)model.Calculations.Add(calculate); } row++; } //получение позиций для текущего юзера var userPositions = new List<SpecificationPosition>(); if (UserHelper.IsController(user)) { userPositions = db.LoadSpecificationPositionsForTenderClaim(claimId, cv); } else if (UserHelper.IsProductManager(user)) { userPositions = db.LoadSpecificationPositionsForTenderClaimForProduct(claimId, user.Id, cv); } //позиции доступные для изменения var possibleEditPosition = userPositions.Where(x => x.State == 1 || x.State == 3).ToList(); if (possibleEditPosition.Any()) { //сохранение позиций и расчета к ним в БД db.DeleteCalculateForPositions(claimId, possibleEditPosition); var userPositionsId = possibleEditPosition.Select(x => x.Id).ToList(); var positionCalculate = 0; var calculateCount = 0; if (positions != null && positions.Any()) { foreach (var position in positions) { if (!userPositionsId.Contains(position.Id)) continue; if (position.Calculations.Any()) positionCalculate++; foreach (var calculatePosition in position.Calculations) { calculateCount++; calculatePosition.IdSpecificationPosition = position.Id; calculatePosition.IdTenderClaim = claimId; db.SaveCalculateSpecificationPosition(calculatePosition); } } } var errorPart = errorStringBuilder.ToString().Trim(); if (string.IsNullOrEmpty(errorPart)) errorPart = "нет"; else errorPart = "<br/>" + errorPart; message = "Позиций расчитано: " + positionCalculate + "<br/>Строк расчета: " + calculateCount + "<br/>Ошибки: " + errorPart; } else { var errorPart = errorStringBuilder.ToString().Trim(); if (string.IsNullOrEmpty(errorPart)) errorPart = "нет"; else errorPart = "<br/>" + errorPart; message = "нет позиций для расчета<br/>Ошибки: " + errorPart; } //получение позиций и расчетов к ним для текущего юзера для передачи в ответ var isController = UserHelper.IsController(user); if (!isController) { positions = db.LoadSpecificationPositionsForTenderClaimForProduct(claimId, user.Id, cv); } else { positions = db.LoadSpecificationPositionsForTenderClaim(claimId, cv); } var productManagers = 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, cv); if (calculations != null && calculations.Any()) { foreach (var position in positions) { position.Calculations = calculations.Where(x => x.IdSpecificationPosition == position.Id).ToList(); } } } else { error = true; message = "Не найден рабочий лист с расчетом спецификаций"; } excBook.Dispose(); excBook = null; } } catch (Exception) { error = true; message = "Ошибка сервера"; } finally { if (inputStream != null) { inputStream.Dispose(); } if (excBook != null) { excBook.Dispose(); } } ViewBag.FirstLoad = false; ViewBag.Error = error.ToString().ToLowerInvariant(); ViewBag.Message = message; ViewBag.Positions = positions; ViewBag.ClaimId = claimId; return View(); }