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();
}