public JsonResult SetPositonRejected(List<int> positionsId, string comment, int idClaim, int cv)
{
var isComplete = false;
ClaimStatusHistory model = null;
try
{
var user = GetUser();
var db = new DbEngine();
isComplete = db.ChangePositionsState(positionsId, 3);
var allPositions = db.LoadSpecificationPositionsForTenderClaim(idClaim, cv);
int claimStatus = 3;
bool isSameCalculate = allPositions.Any(x => x.State == 2 || x.State == 4);
if (isSameCalculate) claimStatus = 6;
var status = db.LoadLastStatusHistoryForClaim(idClaim).Status.Id;
//изменение статуса заявки и истроиии изменения статусов
if (status != claimStatus)
{
var changeStatusComplete = DbEngine.ChangeTenderClaimClaimStatus(new TenderClaim() { Id = idClaim, ClaimStatus = claimStatus });
if (changeStatusComplete)
{
model = new ClaimStatusHistory()
{
Date = DateTime.Now,
IdUser = user.Id,
IdClaim = idClaim,
Comment = comment,
Status = new ClaimStatus() { Id = claimStatus }
};
if (string.IsNullOrEmpty(model.Comment)) model.Comment = string.Empty;
db.SaveClaimStatusHistory(model);
model.DateString = model.Date.ToString("dd.MM.yyyy HH:mm");
}
}
if (isComplete)
{
//>>>>Уведомления
var claim = db.LoadTenderClaimById(idClaim);
var productManagers =
allPositions.Where(x => positionsId.Contains(x.Id)).Select(x => x.ProductManager).ToList();
if (productManagers.Any())
{
var productManagersFromAd = UserHelper.GetProductManagers();
var productInClaim =
productManagersFromAd.Where(x => productManagers.Select(y => y.Id).Contains(x.Id)).ToList();
var host = ConfigurationManager.AppSettings["AppHost"];
var messageMail = new StringBuilder();
messageMail.Append("Добрый день!");
messageMail.Append("<br/>");
messageMail.Append("Пользователь ");
messageMail.Append(user.ShortName);
messageMail.Append(" отклонил Ваш расчет позиции по заявке № " + claim.Id + "<br/>");
if (!string.IsNullOrEmpty(comment)) messageMail.Append("Комментарий: " + comment);
messageMail.Append("<br/><br/>");
//messageMail.Append(GetClaimInfo(claim));
//messageMail.Append("<br/>");
messageMail.Append("Ссылка на заявку: ");
messageMail.Append("<a href='" + host + "/Calc/Index?claimId=" + claim.Id + "'>" + host +
"/Calc/Index?claimId=" + claim.Id + "</a>");
//messageMail.Append("<br/>Сообщение от системы Спец расчет");
Notification.SendNotification(productInClaim, messageMail.ToString(),
String.Format("{0} - {1} - Отклонение расчета позиций СпецРасчет", claim.TenderNumber, claim.Customer));
}
}
}
catch (Exception)
{
isComplete = false;
}
return Json(new { IsComplete = isComplete, Model = model });
}
//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();
}
}
//>>>>Уведомления
//подтверждение позиций по заявке
public JsonResult SetClaimAllPositonConfirmed(int idClaim, int cv)
{
var isComplete = false;
ClaimStatusHistory model = null;
var message = string.Empty;
try
{
var user = GetUser();
var db = new DbEngine();
var positions = db.LoadSpecificationPositionsForTenderClaim(idClaim, cv);
if (positions.Any())
{
//все ли позиции имеют расчет
var isReady = db.IsPositionsReadyToConfirm(positions);
if (isReady)
{
//изменение статуса позиций, заявки и истории изменения статусов
isComplete = db.ChangePositionsState(positions.Select(x => x.Id).ToList(), 4);
if (isComplete)
{
DbEngine.ChangeTenderClaimClaimStatus(new TenderClaim() { Id = idClaim, ClaimStatus = 8 });
model = new ClaimStatusHistory()
{
Date = DateTime.Now,
IdUser = user.Id,
IdClaim = idClaim,
Comment = string.Empty,
Status = new ClaimStatus() { Id = 8 }
};
db.SaveClaimStatusHistory(model);
model.DateString = model.Date.ToString("dd.MM.yyyy HH:mm");
//>>>>Уведомления
var claim = db.LoadTenderClaimById(idClaim);
var productManagers = positions.Select(x => x.ProductManager).ToList();
if (productManagers.Any())
{
var productManagersFromAd = UserHelper.GetProductManagers();
var productInClaim =
productManagersFromAd.Where(x => productManagers.Select(y => y.Id).Contains(x.Id)).ToList();
var host = ConfigurationManager.AppSettings["AppHost"];
var messageMail = new StringBuilder();
messageMail.Append("Добрый день!");
messageMail.Append("<br/>");
messageMail.Append("Пользователь ");
messageMail.Append(user.ShortName);
messageMail.Append(" подтвердил Ваш расчет позиции по заявке № " + claim.Id + " - версия " + cv);
messageMail.Append("<br/><br/>");
//messageMail.Append(GetClaimInfo(claim));
//messageMail.Append("<br/>");
//messageMail.Append("Ссылка на заявку: ");
//messageMail.Append("<a href='" + host + "/Calc/Index?claimId=" + claim.Id + "'>" + host +
// "/Calc/Index?claimId=" + claim.Id + "</a>");
//messageMail.Append("<br/>Сообщение от системы Спец расчет");
Notification.SendNotification(productInClaim, messageMail.ToString(),
String.Format("{0} - версия {2} - {1} - Подтверждение расчета позиций заявки СпецРасчет", claim.TenderNumber, claim.Customer, cv));
}
}
}
else
{
message = "Невозможно отправить позиции на подтверждение\rНе все позиции имеют расчет";
}
}
else
{
message = "Невозможно отправить позиции на подтверждение\rВ заявке нет позиций";
}
}
catch (Exception)
{
isComplete = false;
}
return Json(new { IsComplete = isComplete, Model = model, Message = message }, JsonRequestBehavior.AllowGet);
}
public JsonResult SetClaimOnWork(int id, int? cv)
{
cv = cv ?? 0;
var isComplete = false;
var message = string.Empty;
ClaimStatusHistory model = null;
try
{
var db = new DbEngine();
var hasPosition = db.HasClaimPosition(id);
if (hasPosition)
{
isComplete = DbEngine.ChangeTenderClaimClaimStatus(new TenderClaim() { Id = id, ClaimStatus = 2 });
var productManagers = db.LoadProductManagersForClaim(id, cv.Value);
if (productManagers != null && productManagers.Any())
{
var productManagersFromAd = UserHelper.GetProductManagers();
foreach (var productManager in productManagers)
{
var productManagerFromAd =
productManagersFromAd.FirstOrDefault(x => x.Id == productManager.Id);
if (productManagerFromAd != null)
{
productManager.ShortName = productManagerFromAd.ShortName;
}
}
//истроия изменения статуса заявки
var user = GetUser();
var comment = "Продакты/снабженцы:<br />";
comment += string.Join("<br />", productManagers.Select(x => x.ShortName));
comment += "<br />Автор: " + user.ShortName;
model = new ClaimStatusHistory()
{
Date = DateTime.Now,
IdClaim = id,
IdUser = user.Id,
Status = new ClaimStatus() { Id = 2 },
Comment = comment
};
db.SaveClaimStatusHistory(model);
model.DateString = model.Date.ToString("dd.MM.yyyy HH:mm");
//>>>>Уведомления
var claimPositions = db.LoadSpecificationPositionsForTenderClaim(id, cv.Value);
var productInClaim =
productManagersFromAd.Where(x => productManagers.Select(y => y.Id).Contains(x.Id)).ToList();
var claim = db.LoadTenderClaimById(id);
var host = ConfigurationManager.AppSettings["AppHost"];
foreach (var productManager in productInClaim)
{
var positionCount = claimPositions.Count(x => x.ProductManager.Id == productManager.Id);
var messageMail = new StringBuilder();
messageMail.Append("Добрый день!");
messageMail.Append(String.Format("<br/>На имя {0} назначена заявка в системе СпецРасчет.", productManager.ShortName));
//messageMail.Append("<br/>Пользователь ");
//messageMail.Append(user.Name);
//messageMail.Append(
// " создал заявку где Вам назначены позиции для расчета. Количество назначенных позиций: " +
// positionCount + "<br/>");
messageMail.Append("<br/><br />");
messageMail.Append(GetClaimInfo(claim));
messageMail.Append("<br />Ссылка на заявку: ");
messageMail.Append("<a href='" + host + "/Calc/Index?claimId=" + claim.Id + "'>" + host +
"/Calc/Index?claimId=" + claim.Id + "</a>");
//messageMail.Append("<br/>Сообщение от системы Спец расчет");
Notification.SendNotification(new[] { productManager }, messageMail.ToString(),
String.Format("{0} - {1} - Новая заявка СпецРасчет", claim.TenderNumber, claim.Customer));
}
}
}
else
{
message = "Невозможно передать заявку в работу без позиций спецификаций";
}
}
catch (Exception)
{
isComplete = false;
}
return Json(new { IsComplete = isComplete, Message = message, Model = model }, JsonRequestBehavior.AllowGet);
}
public JsonResult AskPositionReject(List<int> posIds, int idClaim, int cv)
{
var isComplete = false;
var message = string.Empty;
ClaimStatusHistory model = null;
var user = GetUser();
var db = new DbEngine();
var positions = new List<SpecificationPosition>();
var positionIds = new List<int>();
if (posIds.Any())
{
positionIds = posIds;
}
isComplete = db.ChangePositionsState(positionIds, 7);
if (!isComplete) message = "Запрос не отправлен";
else
{
var allPositions = db.LoadSpecificationPositionsForTenderClaim(idClaim, cv);
var isAllRejected = allPositions.Count() ==
allPositions.Count(x => x.State == 7);
var lastClaimStatus = db.LoadLastStatusHistoryForClaim(idClaim).Status.Id;
var claimStatus = lastClaimStatus;
//Изменение статуса заявки и истроии изменения статусов
if (lastClaimStatus != claimStatus)
{
DbEngine.ChangeTenderClaimClaimStatus(new TenderClaim()
{
Id = idClaim,
ClaimStatus = claimStatus
});
var statusHistory = new ClaimStatusHistory()
{
Date = DateTime.Now,
Comment = String.Format("Пользователь {0} запросил отклонение {1} из {2} позиций.<br/>", user.ShortName, positionIds.Count, allPositions.Count),
IdClaim = idClaim,
IdUser = user.Id,
Status = new ClaimStatus() { Id = claimStatus }
};
db.SaveClaimStatusHistory(statusHistory);
statusHistory.DateString = statusHistory.Date.ToString("dd.MM.yyyy HH:mm");
model = statusHistory;
}
else
{
var statusHistory = new ClaimStatusHistory()
{
Date = DateTime.Now,
Comment = String.Format("Пользователь {0} запросил отклонение {1} из {2} позиций.<br/>", user.ShortName, positionIds.Count, allPositions.Count),
IdClaim = idClaim,
IdUser = user.Id,
Status = new ClaimStatus() { Id = lastClaimStatus }
};
db.SaveClaimStatusHistory(statusHistory);
statusHistory.DateString = statusHistory.Date.ToString("dd.MM.yyyy HH:mm");
model = statusHistory;
}
//инфа для уведомления
var claim = db.LoadTenderClaimById(idClaim);
var host = ConfigurationManager.AppSettings["AppHost"];
var productManagersFromAd = UserHelper.GetProductManagers();
var productManagers = db.LoadProductManagersForClaim(claim.Id, cv);
var productInClaim =
productManagersFromAd.Where(x => productManagers.Select(y => y.Id).Contains(x.Id))
.ToList();
var manager = UserHelper.GetUserById(claim.Manager.Id);
var author = UserHelper.GetUserById(claim.Author.Id);
var to = new List<UserBase>();
to.Add(manager);
if (author.Id != manager.Id)
{
to.Add(author);
}
//>>>>Уведомления
if (isAllRejected)
{
var messageMail = new StringBuilder();
messageMail.Append("Добрый день!<br/>");
messageMail.Append("Запрос на отклонение позиций в заявке №" + claim.Id + " пльзователем " + user.Name + ".<br/>");
//messageMail.Append("Комментарий:<br/>");
//messageMail.Append(comment + "<br/>");
//messageMail.Append("Продакты/Снабженцы: <br/>");
//foreach (var productManager in productInClaim)
//{
// messageMail.Append(productManager.Name + "<br/>");
//}
messageMail.Append("Ссылка на заявку: ");
messageMail.Append("<a href='" + host + "/Claim/Index?claimId=" + claim.Id + "'>" + host +
"/Claim/Index?claimId=" + claim.Id + "</a>");
//messageMail.Append("<br/>Сообщение от системы Спец расчет");
Notification.SendNotification(to, messageMail.ToString(),
String.Format("{0} - {1} - Запрос на отклонение позиций заявки СпецРасчет", claim.TenderNumber,
claim.Customer));
}
//>>>>Уведомления
if (!isAllRejected)
{
var noneRejectedPositionManagers =
allPositions.Where(x => x.State == 1 || x.State == 3)
.Select(x => x.ProductManager)
.ToList();
if (noneRejectedPositionManagers.Any())
{
var products =
productManagersFromAd.Where(
x => noneRejectedPositionManagers.Select(y => y.Id).Contains(x.Id))
.ToList();
var messageMail = new StringBuilder();
messageMail.Append("Добрый день!<br/>");
messageMail.Append("Запрос на отклонение позиций в заявке №" + claim.Id + " пльзователем " + user.Name + ".<br/>");
messageMail.Append("Отклонено позиций " + allPositions.Count(x => x.State == 5) + " из " + allPositions.Count + ".<br/>");
//messageMail.Append("<br/>");
//messageMail.Append(GetClaimInfo(claim));
//messageMail.Append("<br/>");
messageMail.Append("Ссылка на заявку: ");
messageMail.Append("<a href='" + host + "/Claim/Index?claimId=" + claim.Id + "'>" +
host +
"/Claim/Index?claimId=" + claim.Id + "</a>");
//messageMail.Append("<br/>Сообщение от системы Спец расчет");
Notification.SendNotification(to, messageMail.ToString(),
String.Format("{0} - {1} - Запрос на отклонение позиций заявки СпецРасчет",
claim.TenderNumber, claim.Customer));
}
}
}
return Json(new { IsComplete = isComplete, Message = message, Model = model }, JsonRequestBehavior.AllowGet);
}
public JsonResult SendPositonOnWork(List<int> positionsId, string comment, int idClaim, int cv)
{
var isComplete = false;
ClaimStatusHistory model = null;
try
{
var user = GetUser();
var db = new DbEngine();
isComplete = db.ChangePositionsState(positionsId, 1);
var lastStatus = db.LoadLastStatusHistoryForClaim(idClaim);
int claimStatus; var allPositions = db.LoadSpecificationPositionsForTenderClaim(idClaim, cv);
if (lastStatus.Status.Id == 9) claimStatus = 2;
else claimStatus = lastStatus.Status.Id;
var productManagers =
allPositions.Where(x => positionsId.Contains(x.Id)).Select(x => x.ProductManager).ToList();
if (productManagers != null && productManagers.Any())
{
var productManagersFromAd = UserHelper.GetProductManagers();
foreach (var productManager in productManagers)
{
var productManagerFromAd =
productManagersFromAd.FirstOrDefault(x => x.Id == productManager.Id);
if (productManagerFromAd != null)
{
productManager.ShortName = productManagerFromAd.ShortName;
}
}
}
var status = db.LoadLastStatusHistoryForClaim(idClaim).Status.Id;
//изменение статуса заявки и истроиии изменения статусов
var changeStatusComplete = true;
if (lastStatus.Status.Id == 9) changeStatusComplete = DbEngine.ChangeTenderClaimClaimStatus(new TenderClaim() { Id = idClaim, ClaimStatus = 2 });
if (changeStatusComplete)
{
model = new ClaimStatusHistory()
{
Date = DateTime.Now,
IdUser = user.Id,
IdClaim = idClaim,
Comment = "Переданы позиции для повторного расчета для:<br />"
+string.Join("<br />", productManagers.Select(x => x.ShortName))+"<br /><br />",
Status = new ClaimStatus() { Id = claimStatus}
};
if (!string.IsNullOrEmpty(comment)) model.Comment += "Комментарий: " + comment + "<br />";
model.Comment += "Автор: " + user.ShortName;
db.SaveClaimStatusHistory(model);
model.DateString = model.Date.ToString("dd.MM.yyyy HH:mm");
}
if (isComplete)
{
//>>>>Уведомления
var claim = db.LoadTenderClaimById(idClaim);
productManagers =
allPositions.Where(x => positionsId.Contains(x.Id)).Select(x => x.ProductManager).ToList();
if (productManagers.Any())
{
var productManagersFromAd = UserHelper.GetProductManagers();
var productInClaim =
productManagersFromAd.Where(x => productManagers.Select(y => y.Id).Contains(x.Id)).ToList();
var host = ConfigurationManager.AppSettings["AppHost"];
var messageMail = new StringBuilder();
messageMail.Append("Добрый день!<br/>");
messageMail.Append("В заявке № " + claim.Id + " вам вновь переданы позиции для расчета пользователем " + user.Name +"<br/>");
if (!string.IsNullOrEmpty(comment)) messageMail.Append("Комментарий: " + comment+"<br/>");
messageMail.Append("<br/>");
messageMail.Append("Ссылка на заявку: ");
messageMail.Append("<a href='" + host + "/Calc/Index?claimId=" + claim.Id + "'>" + host +
"/Calc/Index?claimId=" + claim.Id + "</a>");
//messageMail.Append("<br/>Сообщение от системы Спец расчет");
Notification.SendNotification(productInClaim, messageMail.ToString(),
String.Format("{0} - {1} - Повторная передача позиций СпецРасчет для расчета", claim.TenderNumber, claim.Customer));
}
}
}
catch (Exception)
{
isComplete = false;
}
return Json(new { IsComplete = isComplete, Model = model });
}
public ActionResult GoActual(int claimId, int cv, int[] selIds)
{
if (claimId <= 0)throw new ArgumentException("Не указана заявка");
if (cv<= 0) throw new ArgumentException("Не указана верия для актулизации");
int newClaimState = 10;
bool isComplete = DbEngine.ChangeTenderClaimClaimStatus(new TenderClaim() { Id = claimId, ClaimStatus = newClaimState });
int newVersion = DbEngine.CopyPositionsForNewVersion(claimId, cv, GetUser().Id, selIds);
var db = new DbEngine();
var productManagers = db.LoadProductManagersForClaim(claimId, newVersion, getActualize:true);
if (productManagers != null && productManagers.Any())
{
var productManagersFromAd = UserHelper.GetProductManagers();
foreach (var productManager in productManagers)
{
var productManagerFromAd =
productManagersFromAd.FirstOrDefault(x => x.Id == productManager.Id);
if (productManagerFromAd != null)
{
productManager.ShortName = productManagerFromAd.ShortName;
}
}
//истроия изменения статуса заявки
var user = GetUser();
var comment = "Продакты/снабженцы:<br />";
comment += string.Join("<br />", productManagers.Select(x => x.ShortName));
comment += "<br />Автор: " + user.ShortName;
ClaimStatusHistory model = new ClaimStatusHistory()
{
Date = DateTime.Now,
IdClaim = claimId,
IdUser = user.Id,
Status = new ClaimStatus() {Id = newClaimState },
Comment = comment
};
db.SaveClaimStatusHistory(model);
model.DateString = model.Date.ToString("dd.MM.yyyy HH:mm");
//>>>>Уведомления
var claimPositions = db.LoadSpecificationPositionsForTenderClaim(claimId, newVersion);
var productInClaim =
productManagersFromAd.Where(x => productManagers.Select(y => y.Id).Contains(x.Id)).ToList();
var claim = db.LoadTenderClaimById(claimId);
var host = ConfigurationManager.AppSettings["AppHost"];
foreach (var productManager in productInClaim)
{
var positionCount = claimPositions.Count(x => x.ProductManager.Id == productManager.Id);
var messageMail = new StringBuilder();
messageMail.Append("Добрый день!");
messageMail.Append(String.Format("<br/>На имя {0} назначена Актуализация расчета по заявке в системе СпецРасчет.",
productManager.ShortName));
//messageMail.Append("<br/>Пользователь ");
//messageMail.Append(user.Name);
//messageMail.Append(
// " создал заявку где Вам назначены позиции для расчета. Количество назначенных позиций: " +
// positionCount + "<br/>");
messageMail.Append("<br/><br />");
messageMail.Append(GetClaimInfo(claim));
messageMail.Append("<br />Ссылка на заявку: ");
messageMail.Append("<a href='" + host + "/Calc/Index?claimId=" + claim.Id + "'>" + host +
"/Calc/Index?claimId=" + claim.Id + "&cv=" + newVersion + "</a>");
//messageMail.Append("<br/>Сообщение от системы Спец расчет");
Notification.SendNotification(new[] {productManager}, messageMail.ToString(),
String.Format("{0} - {1} - Актуализация расчета заявки СпецРасчет", claim.TenderNumber, claim.Customer));
}
}
return Json(new { claimId = claimId, newVersion = newVersion });
//return RedirectToAction("Index", new {claimId = claimId, cv= newVersion });
}
//форма заявки, если передан параметр 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 JsonResult SetPositionRejected(List<int> posIds, int idClaim, string comment, int cv)
{
var isComplete = false;
var message = string.Empty;
ClaimStatusHistory model = null;
/* try
{*/
var user = GetUser();
var db = new DbEngine();
//получение позиций для текущего юзера
var positions = new List<SpecificationPosition>();
if (UserHelper.IsController(user))
{
positions = db.LoadSpecificationPositionsForTenderClaim(idClaim, cv);
}
else
{
if (UserHelper.IsProductManager(user))
{
positions = db.LoadSpecificationPositionsForTenderClaimForProduct(idClaim, user.Id, cv);
}
}
var positionIds = new List<int>();
//if (positions.Any())
if (posIds.Any())
{
//Переделано для частичной передачи расчета
positionIds = posIds;
}
//изменения статуса позиций на - отправлено
else
{
foreach (var position in positions)
{
positionIds.Add(position.Id);
}
}
isComplete = db.ChangePositionsState(positionIds,5);
if (!isComplete) message = "Позиции не отклонены";
else
{
var allPositions = db.LoadSpecificationPositionsForTenderClaim(idClaim, cv);
var isAllRejected = allPositions.Count() ==
allPositions.Count(x => x.State == 5);
var lastClaimStatus = db.LoadLastStatusHistoryForClaim(idClaim).Status.Id;
var claimStatus = isAllRejected ? 9 : lastClaimStatus;
//Изменение статуса заявки и истроии изменения статусов
if (lastClaimStatus != claimStatus)
{
DbEngine.ChangeTenderClaimClaimStatus(new TenderClaim()
{
Id = idClaim,
ClaimStatus = claimStatus
});
var statusHistory = new ClaimStatusHistory()
{
Date = DateTime.Now,
Comment = String.Format("Пользователь {0} отклонил {2} из {3} позиций.<br/>Комментарий: {1} ",user.ShortName,comment,positionIds.Count,allPositions.Count),
IdClaim = idClaim,
IdUser = user.Id,
Status = new ClaimStatus() { Id = claimStatus }
};
db.SaveClaimStatusHistory(statusHistory);
statusHistory.DateString = statusHistory.Date.ToString("dd.MM.yyyy HH:mm");
model = statusHistory;
}
else
{
var statusHistory = new ClaimStatusHistory()
{
Date = DateTime.Now,
Comment = String.Format("Пользователь {0} отклонил {2} из {3} позиций.<br/>Комментарий: {1} ", user.ShortName, comment, positionIds.Count, allPositions.Count),
IdClaim = idClaim,
IdUser = user.Id,
Status = new ClaimStatus() { Id = lastClaimStatus }
};
db.SaveClaimStatusHistory(statusHistory);
statusHistory.DateString = statusHistory.Date.ToString("dd.MM.yyyy HH:mm");
model = statusHistory;
}
//инфа для уведомления
var claim = db.LoadTenderClaimById(idClaim);
var host = ConfigurationManager.AppSettings["AppHost"];
var productManagersFromAd = UserHelper.GetProductManagers();
var productManagers = db.LoadProductManagersForClaim(claim.Id, cv);
var productInClaim =
productManagersFromAd.Where(x => productManagers.Select(y => y.Id).Contains(x.Id))
.ToList();
var manager = UserHelper.GetUserById(claim.Manager.Id);
var author = UserHelper.GetUserById(claim.Author.Id);
var to = new List<UserBase>();
to.Add(manager);
if (author.Id != manager.Id)
{
to.Add(author);
}
//>>>>Уведомления
if (claimStatus == 9)
{
var messageMail = new StringBuilder();
messageMail.Append("Добрый день!<br/>");
messageMail.Append("Позиции в заявке № " + claim.Id + " отклонены пользователем " + user.Name + ".<br/>");
messageMail.Append("Отклонены все позиции.<br/>");
messageMail.Append("Комментарий:<br/>");
messageMail.Append(comment+"<br/>");
//messageMail.Append("Продакты/Снабженцы: <br/>");
//foreach (var productManager in productInClaim)
//{
// messageMail.Append(productManager.Name + "<br/>");
//}
messageMail.Append("Ссылка на заявку: ");
messageMail.Append("<a href='" + host + "/Claim/Index?claimId=" + claim.Id + "'>" + host +
"/Claim/Index?claimId=" + claim.Id + "</a>");
//messageMail.Append("<br/>Сообщение от системы Спец расчет");
Notification.SendNotification(to, messageMail.ToString(),
String.Format("{0} - {1} - Полное отклонение заявки СпецРасчет", claim.TenderNumber,
claim.Customer));
}
//>>>>Уведомления
if (claimStatus == lastClaimStatus)
{
var noneRejectedPositionManagers =
allPositions.Where(x => x.State == 1 || x.State == 3)
.Select(x => x.ProductManager)
.ToList();
if (noneRejectedPositionManagers.Any())
{
var products =
productManagersFromAd.Where(
x => noneRejectedPositionManagers.Select(y => y.Id).Contains(x.Id))
.ToList();
var messageMail = new StringBuilder();
messageMail.Append("Добрый день!<br/>");
messageMail.Append("Позиции в заявке №" + claim.Id + " отклонены пльзователем "+ user.Name+".<br/>");
messageMail.Append("Отклонено позиций "+allPositions.Count(x => x.State==5)+" из "+allPositions.Count+".<br/>");
//messageMail.Append("<br/>");
//messageMail.Append(GetClaimInfo(claim));
//messageMail.Append("<br/>");
messageMail.Append("Ссылка на заявку: ");
messageMail.Append("<a href='" + host + "/Claim/Index?claimId=" + claim.Id + "'>" +
host +
"/Claim/Index?claimId=" + claim.Id + "</a>");
//messageMail.Append("<br/>Сообщение от системы Спец расчет");
Notification.SendNotification(to, messageMail.ToString(),
String.Format("{0} - {1} - Частичное отклонение заявки СпецРасчет",
claim.TenderNumber, claim.Customer));
}
}
}
/* }
else
{
message = "Невозможно отправить позиции на подтверждение\rНе все позиции имеют расчет";
}
}
else
{
message = "Выберите хотябы одну позицию!";
}*/
/* }
catch (Exception)
{
isComplete = false;
message = "Ошибка сервера";
}*/
return Json(new { IsComplete = isComplete, Message = message, Model = model }, JsonRequestBehavior.AllowGet);
}
//Страница расчета позиций по заявке
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();
}
}
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();
}
public JsonResult SetPositionToConfirm(List<int> posIds, int idClaim, string comment, int cv)
{
var isComplete = false;
var message = string.Empty;
ClaimStatusHistory model = null;
try
{
var user = GetUser();
var db = new DbEngine();
//получение позиций для текущего юзера
var positions = new List<SpecificationPosition>();
if (UserHelper.IsController(user))
{
positions = db.LoadSpecificationPositionsForTenderClaim(idClaim, cv);
}
else
{
if (UserHelper.IsProductManager(user))
{
positions = db.LoadSpecificationPositionsForTenderClaimForProduct(idClaim, user.Id, cv);
}
}
//if (positions.Any())
if (posIds.Any())
{
//Переделано для частичной передачи расчета
positions = new List<SpecificationPosition>();
foreach (int p in posIds)
{
positions.Add(new SpecificationPosition(){Id=p});
}
// /> частичная передача
//проверка наличия у позиций строк расчета
var isReady = db.IsPositionsReadyToConfirm(positions);
if (isReady)
{
//изменения статуса позиций на - отправлено
isComplete = db.SetPositionsToConfirm(positions);
if (!isComplete) message = "Позиции не отправлены";
else
{
var allPositions = db.LoadSpecificationPositionsForTenderClaim(idClaim, cv);
var isAllCalculate = allPositions.Count() ==
allPositions.Count(x => x.State == 2 || x.State == 4);
var claimStatus = isAllCalculate ? 7 : 6;
//Изменение статуса заявки и истроии изменения статусов
var status = db.LoadLastStatusHistoryForClaim(idClaim).Status.Id;
if (status != claimStatus)
{
DbEngine.ChangeTenderClaimClaimStatus(new TenderClaim()
{
Id = idClaim,
ClaimStatus = claimStatus
});
var statusHistory = new ClaimStatusHistory()
{
Date = DateTime.Now,
Comment = comment,
IdClaim = idClaim,
IdUser = user.Id,
Status = new ClaimStatus() {Id = claimStatus}
};
db.SaveClaimStatusHistory(statusHistory);
statusHistory.DateString = statusHistory.Date.ToString("dd.MM.yyyy HH:mm");
model = statusHistory;
}
else
{
var statusHistory = new ClaimStatusHistory()
{
Date = DateTime.Now,
Comment = comment,
IdClaim = idClaim,
IdUser = user.Id,
Status = new ClaimStatus() {Id = status}
};
db.SaveClaimStatusHistory(statusHistory);
statusHistory.DateString = statusHistory.Date.ToString("dd.MM.yyyy HH:mm");
model = statusHistory;
}
//инфа для уведомления
var claim = db.LoadTenderClaimById(idClaim);
var host = ConfigurationManager.AppSettings["AppHost"];
var productManagersFromAd = UserHelper.GetProductManagers();
var productManagers = db.LoadProductManagersForClaim(claim.Id, cv);
var productInClaim =
productManagersFromAd.Where(x => productManagers.Select(y => y.Id).Contains(x.Id))
.ToList();
var manager = UserHelper.GetUserById(claim.Manager.Id);
var author = UserHelper.GetUserById(claim.Author.Id);
var to = new List<UserBase>();
to.Add(manager);
if (author.Id != manager.Id)
{
to.Add(author);
}
//>>>>Уведомления
if (claimStatus == 7)
{
var messageMail = new StringBuilder();
messageMail.Append("Добрый день!");
messageMail.Append("<br/>");
messageMail.Append("Заявка №" + claim.Id + " - версия " + cv + " - полностью расчитана.");
//messageMail.Append("<br/><br />");
//messageMail.Append(GetClaimInfo(claim));
messageMail.Append("<br/>");
//messageMail.Append("Продакты/Снабженцы: <br/>");
//foreach (var productManager in productInClaim)
//{
// messageMail.Append(productManager.Name + "<br/>");
//}
messageMail.Append("Ссылка на заявку: ");
messageMail.Append("<a href='" + host + "/Claim/Index?claimId=" + claim.Id + "'>" + host +
"/Claim/Index?claimId=" + claim.Id + "</a>");
//messageMail.Append("<br/>Сообщение от системы Спец расчет");
Notification.SendNotification(to, messageMail.ToString(),
String.Format("{0} - версия {2} - {1} - Полный расчет заявки СпецРасчет", claim.TenderNumber,
claim.Customer, cv));
}
//>>>>Уведомления
if (claimStatus == 6)
{
var noneCalculatePositionManagers =
allPositions.Where(x => x.State == 1 || x.State == 3)
.Select(x => x.ProductManager)
.ToList();
if (noneCalculatePositionManagers.Any())
{
var products =
productManagersFromAd.Where(
x => noneCalculatePositionManagers.Select(y => y.Id).Contains(x.Id))
.ToList();
var messageMail = new StringBuilder();
messageMail.Append("Добрый день!");
messageMail.Append("<br/>");
messageMail.Append("Заявка №" + claim.Id + " частично расчитана.");
messageMail.Append("Продакты/Снабженцы, у которых расчет еще в работе: <br/>");
foreach (var productManager in products)
{
messageMail.Append(productManager.Name + "<br/>");
}
//messageMail.Append("<br/>");
//messageMail.Append(GetClaimInfo(claim));
//messageMail.Append("<br/>");
messageMail.Append("Ссылка на заявку: ");
messageMail.Append("<a href='" + host + "/Claim/Index?claimId=" + claim.Id + "'>" +
host +
"/Claim/Index?claimId=" + claim.Id + "</a>");
//messageMail.Append("<br/>Сообщение от системы Спец расчет");
Notification.SendNotification(to, messageMail.ToString(),
String.Format("{0} - {1} - Частичный расчет заявки СпецРасчет",
claim.TenderNumber, claim.Customer));
}
}
}
}
else
{
message = "Невозможно отправить позиции на подтверждение\rНе все позиции имеют расчет";
}
}
else
{
message = "Выберите хотябы одну позицию!";
}
}
catch (Exception)
{
isComplete = false;
message = "Ошибка сервера";
}
return Json(new { IsComplete = isComplete, Message = message, Model = model }, JsonRequestBehavior.AllowGet);
}