public List<CalculateSpecificationPosition> LoadCalculateSpecificationPositionsForTenderClaim(int claimId, int version) { var list = new List<CalculateSpecificationPosition>(); using (var conn = new SqlConnection(_connectionString)) { var cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "LoadCalculateClaimPositionForClaim"; cmd.Parameters.AddWithValue("@id", claimId); cmd.Parameters.AddWithValue("@version", version); conn.Open(); var rd = cmd.ExecuteReader(); if (rd.HasRows) { while (rd.Read()) { var model = new CalculateSpecificationPosition() { Id = rd.GetInt32(0), IdSpecificationPosition = rd.GetInt32(1), IdTenderClaim = rd.GetInt32(2), CatalogNumber = rd.GetString(3), Name = rd.GetString(4), Replace = rd.GetString(5), PriceCurrency = (double)rd.GetDecimal(6), SumCurrency = (double)rd.GetDecimal(7), PriceRub = (double)rd.GetDecimal(8), SumRub = (double)rd.GetDecimal(9), Provider = rd.GetString(10), ProtectCondition = rd.GetString(12), Comment = rd.GetString(13), Author = rd.GetString(14) }; if (!rd.IsDBNull(11)) { model.ProtectFact = new ProtectFact() {Id = rd.GetInt32(11)}; } if (!rd.IsDBNull(18)) { model.Currency = rd.GetInt32(18); } if (!rd.IsDBNull(19)) { model.PriceUsd = (double)rd.GetDecimal(19); } if (!rd.IsDBNull(20)) { model.PriceEur = (double)rd.GetDecimal(20); } if (!rd.IsDBNull(21)) { model.PriceEurRicoh = (double)rd.GetDecimal(21); } if (!rd.IsDBNull(22)) { model.PriceRubl = (double)rd.GetDecimal(22); } if (!rd.IsDBNull(23)) { model.DeliveryTime = new DeliveryTime() { Id = rd.GetInt32(23) }; } if (model.PriceCurrency.Equals(-1)) model.PriceCurrency = 0; if (model.SumCurrency.Equals(-1)) model.SumCurrency = 0; if (model.PriceRub.Equals(-1)) model.PriceRub = 0; list.Add(model); } } rd.Dispose(); } return list; }
public bool UpdateCalculateSpecificationPosition(CalculateSpecificationPosition model) { var result = false; using (var conn = new SqlConnection(_connectionString)) { var cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "UpdateCalculateClaimPosition"; if (!string.IsNullOrEmpty(model.Replace)) cmd.Parameters.AddWithValue("@replaceValue", model.Replace); if (!string.IsNullOrEmpty(model.Comment)) cmd.Parameters.AddWithValue("@comment", model.Comment); if (!string.IsNullOrEmpty(model.ProtectCondition)) cmd.Parameters.AddWithValue("@protectCondition", model.ProtectCondition); if (!string.IsNullOrEmpty(model.Provider)) cmd.Parameters.AddWithValue("@provider", model.Provider); if (!model.PriceCurrency.Equals(0)) cmd.Parameters.AddWithValue("@priceCurrency", model.PriceCurrency); if (!model.PriceRub.Equals(0)) cmd.Parameters.AddWithValue("@priceRub", model.PriceRub); if (!model.SumCurrency.Equals(0)) cmd.Parameters.AddWithValue("@sumCurrency", model.SumCurrency); cmd.Parameters.AddWithValue("@id", model.Id); cmd.Parameters.AddWithValue("@name", model.Name); cmd.Parameters.AddWithValue("@catalogNumber", model.CatalogNumber); if (model.ProtectFact != null) cmd.Parameters.AddWithValue("@protectFact", model.ProtectFact.Id); cmd.Parameters.AddWithValue("@sumRub", model.SumRub); cmd.Parameters.AddWithValue("@author", model.Author); //cmd.Parameters.AddWithValue("@currency", model.Currency); cmd.Parameters.AddWithValue("@priceUsd", model.PriceUsd); cmd.Parameters.AddWithValue("@priceEur", model.PriceEur); cmd.Parameters.AddWithValue("@priceEurRicoh", model.PriceEurRicoh); cmd.Parameters.AddWithValue("@priceRubl", model.PriceRubl); cmd.Parameters.AddWithValue("@deliveryTime", model.DeliveryTime.Id); conn.Open(); result = cmd.ExecuteNonQuery() > 0; } return result; }
public bool SaveCalculateSpecificationPosition(CalculateSpecificationPosition model) { var result = false; using (var conn = new SqlConnection(_connectionString)) { var cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "SaveCalculateClaimPosition"; if (!string.IsNullOrEmpty(model.Replace)) cmd.Parameters.AddWithValue("@replaceValue", model.Replace); if (!string.IsNullOrEmpty(model.Comment)) cmd.Parameters.AddWithValue("@comment", model.Comment); if (!string.IsNullOrEmpty(model.ProtectCondition)) cmd.Parameters.AddWithValue("@protectCondition", model.ProtectCondition); if (!string.IsNullOrEmpty(model.Provider)) cmd.Parameters.AddWithValue("@provider", model.Provider); if (!model.PriceCurrency.Equals(0)) cmd.Parameters.AddWithValue("@priceCurrency", model.PriceCurrency); if (!model.PriceRub.Equals(0)) cmd.Parameters.AddWithValue("@priceRub", model.PriceRub); if (!model.SumCurrency.Equals(0)) cmd.Parameters.AddWithValue("@sumCurrency", model.SumCurrency); cmd.Parameters.AddWithValue("@idClaim", model.IdTenderClaim); cmd.Parameters.AddWithValue("@idPosition", model.IdSpecificationPosition); cmd.Parameters.AddWithValue("@name", model.Name); cmd.Parameters.AddWithValue("@catalogNumber", model.CatalogNumber); if (model.ProtectFact != null)cmd.Parameters.AddWithValue("@protectFact", model.ProtectFact.Id); cmd.Parameters.AddWithValue("@sumRub", model.SumRub); cmd.Parameters.AddWithValue("@author", model.Author); //cmd.Parameters.AddWithValue("@currency", model.Currency); cmd.Parameters.AddWithValue("@priceUsd", model.PriceUsd); cmd.Parameters.AddWithValue("@priceEur", model.PriceEur); cmd.Parameters.AddWithValue("@priceEurRicoh", model.PriceEurRicoh); cmd.Parameters.AddWithValue("@priceRubl", model.PriceRubl); cmd.Parameters.AddWithValue("@deliveryTime", model.DeliveryTime.Id); conn.Open(); var rd = cmd.ExecuteReader(); if (rd.HasRows) { rd.Read(); var id = rd.GetInt32(0); if (id > 0) { result = true; model.Id = id; } } rd.Dispose(); } return result; }
public JsonResult Save(CalculateSpecificationPosition model) { var isComplete = false; var id = -1; try { model.Author = GetUser().Id; var db = new DbEngine(); isComplete = db.SaveCalculateSpecificationPosition(model); id = model.Id; } catch (Exception ex) { isComplete = false; } return Json(new { IsComplete = isComplete, Id = id }); }
public JsonResult Edit(CalculateSpecificationPosition model) { var isComplete = false; try { model.Author = GetUser().Id; var db = new DbEngine(); isComplete = db.UpdateCalculateSpecificationPosition(model); } catch (Exception) { isComplete = false; } return Json(new { IsComplete = isComplete }); }
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(); }