//Получение контроллеров из ActiveDirectory
private static List<UserBase> GetControllers()
{
var db = new DbEngine();
var roles = db.LoadRoles();
var list = new List<UserBase>();
var domain = new PrincipalContext(ContextType.Domain);
var group = GroupPrincipal.FindByIdentity(domain, IdentityType.Name, roles.First(x=>x.Role == Role.Controller).Name);
if (group != null)
{
var members = group.GetMembers(true);
foreach (var principal in members)
{
var userPrincipal = UserPrincipal.FindByIdentity(domain, principal.Name);
if (userPrincipal != null)
{
var email = userPrincipal.EmailAddress;
var name = userPrincipal.DisplayName;
var sid = userPrincipal.Sid.Value;
var shortName = GetShortName(name);
var user = new UserBase()
{
Id = sid,
Name = name,
ShortName = shortName,
Email = email
};
list.Add(user);
}
}
}
return list;
}
public static bool IsUserAccess(UserBase user)
{
var result = user.Roles.Contains(Role.Enter);
return result;
}
public static bool IsTenderStatus(UserBase user)
{
var result = user.Roles.Contains(Role.TenderStatus);
return result;
}
public static bool IsProductManager(UserBase user)
{
var result = user.Roles.Contains(Role.ProductManager);
return result;
}
public static bool IsOperator(UserBase user)
{
var result = user.Roles.Contains(Role.Operator);
return result;
}
public static bool IsController(UserBase user)
{
var result = user.Roles.Contains(Role.Controller);
return result;
}
//public static IEnumerable<KeyValuePair<string, string>> GetOperators()
//{
// return GetUserSelectionList(AdGroup.SpeCalcOperator);
// //using (WindowsImpersonationContextFacade impersonationContext
// // = new WindowsImpersonationContextFacade(
// // nc))
// //{
// // var list = new List<Operator>();
// // var domain = new PrincipalContext(ContextType.Domain);
// // var group = GroupPrincipal.FindByIdentity(domain, IdentityType.Name,
// // _roles.First(x => x.Role == Role.Operator).Name);
// // if (group != null)
// // {
// // var members = group.GetMembers(true);
// // foreach (var principal in members)
// // {
// // var userPrincipal = UserPrincipal.FindByIdentity(domain, principal.Name);
// // if (userPrincipal != null)
// // {
// // var email = userPrincipal.EmailAddress;
// // var name = userPrincipal.DisplayName;
// // var sid = userPrincipal.Sid.Value;
// // var shortName = GetShortName(name);
// // var departament = GetProperty(userPrincipal, "department");
// // var manager = GetProperty(userPrincipal, "manager");
// // var managerShortName = string.Empty;
// // if (!string.IsNullOrEmpty(manager))
// // {
// // var managerUser = UserPrincipal.FindByIdentity(domain, manager);
// // if (managerUser != null)
// // {
// // manager = managerUser.DisplayName;
// // managerShortName = GetShortName(manager);
// // }
// // }
// // var user = new Operator()
// // {
// // Id = sid,
// // Name = name,
// // ShortName = shortName,
// // Email = email,
// // SubDivision = departament,
// // Chief = manager,
// // ChiefShortName = managerShortName,
// // Roles = new List<Role>() { Role.Manager }
// // };
// // list.Add(user);
// // }
// // }
// // }
// // list = list.OrderBy(m => m.ShortName).ToList();
// // return list;
// //}
//}
//public static List<ControllerUser> GetControllerUsers()
//{
// return new List<ControllerUser>()
// {
// new ControllerUser() { Id = "bngbtjradbdfgbffg", Name = "Тихонов Андрей", Roles = new List<Role>() { Role.Enter, Role.Controller}},
// new ControllerUser() { Id = "uyjtjuktsdfvwvfv", Name = "Аршавин Денис", Roles = new List<Role>() { Role.Enter, Role.Controller, Role.TenderStatus}},
// };
//}
//public static List<TenderStatusUser> GetTenderStatusUsers()
//{
// return new List<TenderStatusUser>()
// {
// new TenderStatusUser() { Id = "rtyutyujyujyuj", Name = "C. Ronaldo", Roles = new List<Role>() { Role.Enter, Role.TenderStatus, Role.ProductManager}},
// new TenderStatusUser() { Id = "iumsdfvsdfsdr", Name = "L. Modrich", Roles = new List<Role>() { Role.Enter, Role.TenderStatus}},
// };
//}
//public static UserBase GetUserByName(string name)
//{
// UserBase user = null;
// var managers = GetManagers();
// user = managers.FirstOrDefault(x => x.Name == name);
// if (user == null)
// {
// var products = GetProductManagers();
// user = products.FirstOrDefault(x => x.Name == name);
// }
// if (user == null)
// {
// var operators = GetOperators();
// user = operators.FirstOrDefault(x => x.Name == name);
// }
// if (user == null)
// {
// var controllerUsers = GetControllerUsers();
// user = controllerUsers.FirstOrDefault(x => x.Name == name);
// }
// if (user == null)
// {
// var tenderStatusUsers = GetTenderStatusUsers();
// user = tenderStatusUsers.FirstOrDefault(x => x.Name == name);
// }
// return user;
//}
//получение юзера по id(sid)
public static UserBase GetUserById(string id)
{
//UserBase user = null;
//var managers = GetManagers();
//user = managers.FirstOrDefault(x => x.Id == id);
//if (user == null)
//{
// var products = GetProductManagers();
// user = products.FirstOrDefault(x => x.Id == id);
//}
//if (user == null)
//{
// var operators = GetOperators();
// user = operators.FirstOrDefault(x => x.Id == id);
//}
//if (user == null)
//{
// var controllerUsers = GetControllerUsers();
// user = controllerUsers.FirstOrDefault(x => x.Id == id);
//}
//if (user == null)
//{
// var tenderStatusUsers = GetTenderStatusUsers();
// user = tenderStatusUsers.FirstOrDefault(x => x.Id == id);
//}
//return user;
using (WindowsImpersonationContextFacade impersonationContext
= new WindowsImpersonationContextFacade(
nc))
{
UserBase user = null;
var domain = new PrincipalContext(ContextType.Domain);
var userPrincipal = UserPrincipal.FindByIdentity(domain, IdentityType.Sid, id);
if (userPrincipal != null)
{
var email = userPrincipal.EmailAddress;
var name = userPrincipal.DisplayName;
var sid = userPrincipal.Sid.Value;
var shortName = GetShortName(name);
var manager = GetProperty(userPrincipal, "manager");
user = new UserBase()
{
Id = sid,
Name = name,
ShortName = shortName,
Email = email,
ManagerName = manager,
Roles = new List<Role>() { Role.Enter }
};
}
return user;
}
}
//получение юзера из идентичности потока
public static UserBase GetUser(IIdentity identity)
{
using (WindowsImpersonationContextFacade impersonationContext
= new WindowsImpersonationContextFacade(
nc))
{
//UserBase user = null;
//var userName = identity.Name;
//user = GetUserByName(userName);
//return user;
UserBase user = null;
var wi = (WindowsIdentity) identity;
if (wi.User != null)
{
user = new UserBase();
var domain = new PrincipalContext(ContextType.Domain);
var id = wi.User.Value;
//id = "S-1-5-21-1970802976-3466419101-4042325969-1750";
user.Id = id;
var login = wi.Name.Remove(0, wi.Name.IndexOf("\\", StringComparison.CurrentCulture) + 1);
//login = "******";
var userPrincipal = UserPrincipal.FindByIdentity(domain, login);
if (userPrincipal != null)
{
var mail = userPrincipal.EmailAddress;
var name = userPrincipal.DisplayName;
user.Email = mail;
user.Name = name;
user.ShortName = GetShortName(user.Name);
user.Roles = new List<Role>();
var wp = new WindowsPrincipal(wi);
//user.Roles.Add(Role.Operator);
//user.Roles.Add(Role.Enter);
foreach (var role in _roles)
{
var grpSid = new SecurityIdentifier(role.Sid);
if (wp.IsInRole(grpSid))
{
user.Roles.Add(role.Role);
}
}
}
}
return user;
}
}
//Excel
//получение excel файла, содержащем только расчет по заявке
public ActionResult GetSpecificationFileOnlyCalculation(int claimId, int cv)
{
XLWorkbook excBook = null;
var ms = new MemoryStream();
var error = false;
var message = string.Empty;
try
{
//получение позиций по заявке и расчетов к ним
var db = new DbEngine();
var positions = db.LoadSpecificationPositionsForTenderClaim(claimId, cv);
var facts = db.LoadProtectFacts();
if (positions.Any())
{
var calculations = db.LoadCalculateSpecificationPositionsForTenderClaim(claimId, cv);
if (calculations != null && calculations.Any())
{
foreach (var position in positions)
{
if (position.State == 1) continue;
position.Calculations =
calculations.Where(x => x.IdSpecificationPosition == position.Id).ToList();
}
}
var filePath = Path.Combine(Server.MapPath("~"), "App_Data", "Specification_fin.xlsx");
using (var fs = System.IO.File.OpenRead(filePath))
{
var buffer = new byte[fs.Length];
fs.Read(buffer, 0, buffer.Count());
ms.Write(buffer, 0, buffer.Count());
ms.Seek(0, SeekOrigin.Begin);
}
//создание файла excel с инфой по расчетам
excBook = new XLWorkbook(ms);
var workSheet = excBook.Worksheet("WorkSheet");
workSheet.Name = "лот";
var claim = db.LoadTenderClaimById(claimId);
//>>>>>>>>Шапка - Заполнение инфы о заявке<<<<<<
//Менеджер из Москвы
bool managerIsMoscou = false;
string managerSid = GetUser().Id;
var dtUserIsMoscou = Db.CheckManagerIsMoscou(managerSid);
if (dtUserIsMoscou.Rows.Count > 0)
{
managerIsMoscou = dtUserIsMoscou.Rows[0]["result"].ToString().Equals("1");
}
// />Менеджер из Москвы
var dealTypes = db.LoadDealTypes();
var deliveryTimes = db.LoadDeliveryTimes();
UserBase manager;
try
{
manager = UserHelper.GetUserById(claim.Manager.Id);
}
catch (Exception ex)
{
manager = new UserBase();
}
var dt = Db.GetExchangeRatesOnDate(DateTime.Now);
double? usdRate = null;
double? eurRate = null;
if (dt.Rows.Count >= 3)
{
usdRate = Convert.ToDouble(dt.Rows[1]["price"].ToString()) * 1.03;
eurRate = Convert.ToDouble(dt.Rows[2]["price"].ToString()) * 1.03;
}
int rowHead = 1;
var usdCell = workSheet.Cell(rowHead, 4);
usdCell.Value =
usdRate.HasValue
? usdRate.Value.ToString("N2")
: string.Empty;
workSheet.Cell(rowHead, 4).DataType = XLCellValues.Number;
var eurCell = workSheet.Cell(++rowHead, 4);
eurCell.Value = eurRate.HasValue
? eurRate.Value.ToString("N2")
: string.Empty;
workSheet.Cell(rowHead, 4).DataType = XLCellValues.Number;
var eurRicohCell = workSheet.Cell(++rowHead, 4);
var profitCell = workSheet.Cell(++rowHead, 4);//Рентабельность
workSheet.Cell(++rowHead, 4).Value = dealTypes.First(x => x.Id == claim.DealType).Value;
workSheet.Cell(++rowHead, 4).Value = manager != null ? manager.ShortName : string.Empty;
workSheet.Cell(++rowHead, 4).Value = claim.Customer;
//срок готовности цен от снабжения???
rowHead++;
workSheet.Cell(++rowHead, 4).Value = claim.Sum;//Максимальная цена контракта???
workSheet.Cell(++rowHead, 4).Value = claim.DeliveryDateString;
workSheet.Cell(rowHead, 4).DataType = XLCellValues.DateTime;
workSheet.Cell(++rowHead, 4).Value = claim.DeliveryPlace;
workSheet.Cell(++rowHead, 4).Value = claim.KPDeadlineString;
workSheet.Cell(rowHead, 4).DataType = XLCellValues.DateTime;
workSheet.Cell(++rowHead, 4).Value = claim.AuctionDateString;
workSheet.Cell(rowHead, 4).DataType = XLCellValues.DateTime;
workSheet.Cell(++rowHead, 4).Value = claim.Comment;
//workSheet.Cell(3, 4).Value = claim.TenderNumber;
//workSheet.Cell(4, 4).Value = claim.TenderStartString;
//workSheet.Cell(4, 4).DataType = XLCellValues.DateTime;
//workSheet.Cell(5, 4).Value = claim.ClaimDeadlineString;
//workSheet.Cell(5, 4).DataType = XLCellValues.DateTime;
//workSheet.Cell(8, 4).Value = claim.CustomerInn;
//workSheet.Cell(9, 4).Value = !claim.Sum.Equals(0) ? claim.Sum.ToString("N2") : string.Empty;
//workSheet.Cell(11, 4).Value = claim.TenderUrl;
//workSheet.Cell(13, 4).Value = claim.Manager.SubDivision;
//workSheet.Cell(14, 4).DataType = XLCellValues.DateTime;
//заголовок для расчетов
//workSheet.Cell(18, 1).Value = "№ пп";
//workSheet.Cell(18, 2).Value = "Каталожный номер*";
//workSheet.Cell(18, 3).Value = "Наименование*";
//workSheet.Cell(18, 4).Value = "Замена";
//workSheet.Cell(18, 5).Value = "Цена за ед.";
//workSheet.Cell(18, 6).Value = "Сумма вход";
//workSheet.Cell(18, 7).Value = "Валюта";
////workSheet.Cell(13, 7).Value = "Цена за ед. руб";
////workSheet.Cell(13, 8).Value = "Сумма вход руб*";
//workSheet.Cell(18, 8).Value = "Поставщик";
//workSheet.Cell(18, 9).Value = "Факт получ.защиты*";
//workSheet.Cell(18, 10).Value = "Условия защиты";
//workSheet.Cell(18, 11).Value = "Цена с ТЗР";
//workSheet.Cell(18, 12).Value = "Сумма с ТЗР";
//workSheet.Cell(18, 13).Value = "Цена с НДС";
//workSheet.Cell(18, 14).Value = "Сумма с НДС";
//workSheet.Cell(18, 15).Value = "Комментарий";
//var calcHeaderRange = workSheet.Range(workSheet.Cell(18, 1), workSheet.Cell(18, 15));
//calcHeaderRange.Style.Font.SetBold(true);
//calcHeaderRange.Style.Fill.BackgroundColor = XLColor.FromArgb(0, 204, 255, 209);
//calcHeaderRange.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
//calcHeaderRange.Style.Border.SetBottomBorder(XLBorderStyleValues.Thin);
//calcHeaderRange.Style.Border.SetBottomBorderColor(XLColor.Gray);
//calcHeaderRange.Style.Border.SetTopBorder(XLBorderStyleValues.Thin);
//calcHeaderRange.Style.Border.SetTopBorderColor(XLColor.Gray);
//calcHeaderRange.Style.Border.SetRightBorder(XLBorderStyleValues.Thin);
//calcHeaderRange.Style.Border.SetRightBorderColor(XLColor.Gray);
//calcHeaderRange.Style.Border.SetLeftBorder(XLBorderStyleValues.Thin);
//calcHeaderRange.Style.Border.SetLeftBorderColor(XLColor.Gray);
//var currencies = db.LoadCurrencies();
//<<<<<<<Номер строки - начало вывода инфы>>>>>>
var row = rowHead+2;
int firstRow = row;
var rowNumber = 1;
//строки расчета
foreach (var position in positions)
{
position.Name = position.Name.Replace("\n", "").Replace("\r", "");
if (position.Calculations != null && position.Calculations.Any())
{
foreach (var calculation in position.Calculations)
{
calculation.Name = calculation.Name.Replace("\n", "").Replace("\r", "");
int col = 0;
workSheet.Cell(row, ++col).Value = rowNumber;
workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
workSheet.Cell(row, ++col).Value = position.CatalogNumber;
//? position.CatalogNumber
//: calculation.CatalogNumber;
double hPosCatNum = GetCellHeight(workSheet.Cell(row, col).Value.ToString().Length, 30);
workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
workSheet.Cell(row, ++col).Value = position.Name;
double hPosName = GetCellHeight(workSheet.Cell(row, col).Value.ToString().Length, 40);
//String.IsNullOrEmpty(calculation.Name)? position.Name: calculation.Name;
workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
workSheet.Cell(row, ++col).Value = calculation.CatalogNumber;
double hCalcCatNum = GetCellHeight(workSheet.Cell(row, col).Value.ToString().Length, 30);
workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
workSheet.Cell(row, ++col).Value = calculation.Name + (!String.IsNullOrEmpty(calculation.Name) ? "\r\n" : "")+ (!String.IsNullOrEmpty(calculation.Replace) ? "Замена:\r\n" + calculation.Replace : String.Empty);
double hCalcName = GetCellHeight(workSheet.Cell(row, col).Value.ToString().Length, 40);
workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
//workSheet.Cell(row, ++col).Value = calculation.Replace;
//workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
workSheet.Cell(row, ++col).Value = GetUnitStr(position.Unit);
workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
var countCell = workSheet.Cell(row, ++col);
countCell.Value = position.Value;
countCell.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
if (position.ProductManager != null)
{
var prodManager = UserHelper.GetUserById(position.ProductManager.Id);
workSheet.Cell(row, ++col).Value = prodManager == null
? String.Empty
: prodManager.ShortName;
}
double hProdManager = GetCellHeight(workSheet.Cell(row, col).Value.ToString().Length, 16);
workSheet.Cell(row, ++col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
if (calculation.DeliveryTime != null)
{
var delivTime = deliveryTimes.First(x => x.Id == calculation.DeliveryTime.Id);
workSheet.Cell(row, col).Value = delivTime == null ? String.Empty : delivTime.Value;
}
double hCalcDeliv = GetCellHeight(workSheet.Cell(row, col).Value.ToString().Length, 16);
workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
string partNum4Online = String.IsNullOrEmpty(position.CatalogNumber)? calculation.CatalogNumber: position.CatalogNumber;
string onlinePrice = String.Empty;
try
{onlinePrice = CatalogProduct.PriceRequest(partNum4Online);}
catch {
}
workSheet.Cell(row, ++col).Value = onlinePrice;//Цена B2B
workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
var priceUsdCell = workSheet.Cell(row, ++col);
priceUsdCell.Value = calculation.PriceUsd;
priceUsdCell.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
priceUsdCell.Style.NumberFormat.Format = "$ #,##0.00";
var priceEurCell = workSheet.Cell(row, ++col);
priceEurCell.Value = calculation.PriceEur;
priceEurCell.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
priceEurCell.Style.NumberFormat.Format = "€ #,##0.00";
var priceEurRicohCell = workSheet.Cell(row, ++col);
priceEurRicohCell.Value = calculation.PriceEurRicoh;
priceEurRicohCell.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
priceEurRicohCell.Style.NumberFormat.Format = "€ #,##0.00";
var priceRublCell = workSheet.Cell(row, ++col);
priceRublCell.Value = calculation.PriceRubl;
priceRublCell.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
//Вход за ед
if (String.IsNullOrEmpty(priceRublCell.Value.ToString().Trim()))
{
if (!String.IsNullOrEmpty(priceEurRicohCell.Value.ToString().Trim()))
{
priceRublCell.FormulaR1C1 = String.Format("{0}*{1}", eurRicohCell.Address.ToStringFixed(), priceEurRicohCell.Address);
}
if (!String.IsNullOrEmpty(priceEurCell.Value.ToString().Trim()))
{
priceRublCell.FormulaR1C1 = String.Format("{0}*{1}", eurCell.Address.ToStringFixed(), priceEurCell.Address);
}
if (!String.IsNullOrEmpty(priceUsdCell.Value.ToString().Trim()))
{
priceRublCell.FormulaR1C1 = String.Format("{0}*{1}", usdCell.Address.ToStringFixed(), priceUsdCell.Address);
}
}
// />Вход за ед
priceRublCell.Style.NumberFormat.Format = "₽ #,##0.00";
//Сумма
var priceSumCell = workSheet.Cell(row, ++col);
priceSumCell.Style.NumberFormat.Format = "₽ #,##0.00";
priceSumCell.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
priceSumCell.FormulaR1C1 = String.Format("{0}*{1}", countCell.Address,
priceRublCell.Address);
// />Сумма
//Цена с ТЗР
var priceTzrCell = workSheet.Cell(row, ++col);
string formulaPriceTzrCell = String.Format("(({0}*1.02)*1.02)", priceRublCell.Address);
if (managerIsMoscou)
{
formulaPriceTzrCell = String.Format("({0}*1.02)", priceRublCell.Address);
}
priceTzrCell.FormulaR1C1 = formulaPriceTzrCell;
priceTzrCell.Style.NumberFormat.Format = "₽ #,##0.00";
priceTzrCell.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
// /> Цена с ТЗР
//Сумма с ТЗР
var sumTzrCell = workSheet.Cell(row, ++col);
string formulaSumTzrCell = String.Format("{0}*{1}", countCell.Address,priceTzrCell.Address);
sumTzrCell.FormulaR1C1 = formulaSumTzrCell;
sumTzrCell.Style.NumberFormat.Format = "₽ #,##0.00";
sumTzrCell.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
// /> Сумма с ТЗР
//Цена С НДС
var priceNdsCell = workSheet.Cell(row, ++col);
string formulaPriceNdsCell = String.Format("{0}*(1+({1}/100))", priceTzrCell.Address, profitCell.Address);
priceNdsCell.FormulaR1C1 = formulaPriceNdsCell;
priceNdsCell.Style.NumberFormat.Format = "₽ #,##0.00";
priceNdsCell.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
// /> Цена с НДС
//Сумма с НДС
var sumNdsCell = workSheet.Cell(row, ++col);
string formulaSumNdsCell = String.Format("{0}*{1}", countCell.Address, priceNdsCell.Address);
sumNdsCell.FormulaR1C1 = formulaSumNdsCell;
sumNdsCell.Style.NumberFormat.Format = "₽ #,##0.00";
sumNdsCell.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
// /> Сумма с НДС
//workSheet.Cell(row, 5).Value = !calculation.PriceCurrency.Equals(0)
// ? calculation.PriceCurrency.ToString("N2")
// : string.Empty;
//workSheet.Cell(row, 6).Value = !calculation.SumCurrency.Equals(0)
// ? calculation.SumCurrency.ToString("N2")
// : string.Empty;
//workSheet.Cell(row, 7).Value = "";//currencies.First(x => x.Id == calculation.Currency).Value;
//workSheet.Cell(row, 7).Value = !calculation.PriceRub.Equals(0)
// ? calculation.PriceRub.ToString("N2")
// : string.Empty;
//workSheet.Cell(row, 8).Value = !calculation.SumRub.Equals(0)
// ? calculation.SumRub.ToString("N2")
// : string.Empty;
workSheet.Cell(row, ++col).Value = calculation.Provider;
workSheet.Cell(row, col)
.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
//workSheet.Cell(row, 9).Value = calculation.ProtectFact != null ?
// facts.First(x => x.Id == calculation.ProtectFact.Id).Value : String.Empty;
//workSheet.Cell(row, 10).Value = calculation.ProtectCondition;
workSheet.Cell(row, ++col).Value = calculation.Comment;
workSheet.Cell(row, col).Style.Font.SetFontColor(XLColor.Red);
double hComent = GetCellHeight(workSheet.Cell(row, col).Value.ToString().Length, 40);
double[] arr = { hPosCatNum, hPosName, hCalcName, hCalcDeliv, hProdManager, hComent, hCalcCatNum };
workSheet.Row(row).Height = arr.Max();
row++;
rowNumber++;
}
}
else
{
int col = 0;
workSheet.Cell(row, ++col).Value = rowNumber;
workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
workSheet.Cell(row, ++col).Value = position.CatalogNumber;
workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
workSheet.Cell(row, ++col).SetValue(position.Name);
workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
workSheet.Row(row).Height = GetCellHeight(position.CatalogNumber.Length, 10);
workSheet.Row(row).Height = GetCellHeight(position.Name.Length, 40);
++col;
++col;
//workSheet.Cell(row, ++col).Value = String.Empty;
workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
workSheet.Cell(row, ++col).Value = GetUnitStr(position.Unit);
workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
workSheet.Cell(row, ++col).Value = position.Value;
workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
if (position.ProductManager != null)
{
var prodManager = UserHelper.GetUserById(position.ProductManager.Id);
workSheet.Cell(row, ++col).Value = prodManager == null
? String.Empty
: prodManager.ShortName;
}
workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
workSheet.Cell(row, ++col).Value = String.Empty;
workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
++col;//Цена B2B
workSheet.Cell(row, ++col).Value = String.Empty;
workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
workSheet.Cell(row, col).Style.NumberFormat.Format = "$ #,###";
workSheet.Cell(row, ++col).Value = String.Empty;
workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
workSheet.Cell(row, col).Style.NumberFormat.Format = "€ #,###";
workSheet.Cell(row, ++col).Value = String.Empty;
workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
workSheet.Cell(row, col).Style.NumberFormat.Format = "€ #,###";
workSheet.Cell(row, ++col).Value = String.Empty;
workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
workSheet.Cell(row, col).Style.NumberFormat.Format = "₽ #,###";
col = col + 6;
workSheet.Cell(row, col).Value = String.Empty;
workSheet.Cell(row, col).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
row++;
rowNumber++;
}
}
var range = workSheet.Range(workSheet.Cell(firstRow, 1), workSheet.Cell(row - 1, 21));
range.Style.Border.SetBottomBorder(XLBorderStyleValues.Thin);
range.Style.Border.SetBottomBorderColor(XLColor.Gray);
range.Style.Border.SetTopBorder(XLBorderStyleValues.Thin);
range.Style.Border.SetTopBorderColor(XLColor.Gray);
range.Style.Border.SetRightBorder(XLBorderStyleValues.Thin);
range.Style.Border.SetRightBorderColor(XLColor.Gray);
range.Style.Border.SetLeftBorder(XLBorderStyleValues.Thin);
range.Style.Border.SetLeftBorderColor(XLColor.Gray);
excBook.SaveAs(ms);
excBook.Dispose();
ms.Seek(0, SeekOrigin.Begin);
}
else
{
error = true;
message = "Нет позиций для расчета";
}
}
catch (Exception ex)
{
error = true;
message = "Ошибка сервера " + ex.Message;
}
finally
{
if (excBook != null)
{
excBook.Dispose();
}
}
if (!error)
{
return new FileStreamResult(ms, "application/vnd.ms-excel")
{
FileDownloadName = "Calculation_" + claimId + ".xlsx"
};
}
else
{
ViewBag.Message = message;
return View();
}
}
//создание уникального имени снабженца, для excel файла загрузки позиций
private string GetUniqueDisplayName(UserBase user)
{
var result = new StringBuilder();
var name = user.Name;
var nameArr = name.Split(new[] { " " }, StringSplitOptions.RemoveEmptyEntries);
if (nameArr.Count() > 2)
{
result.Append(nameArr[0]);
result.Append(" ");
result.Append(nameArr[1].Substring(0, 1));
result.Append(".");
result.Append(nameArr[2].Substring(0, 1));
result.Append(".");
result.Append(" /");
result.Append(user.Email);
}
else
{
result.Append(name);
result.Append("/");
result.Append(user.Email);
}
return result.ToString();
}
public bool DeleteTenderClaim(int id, UserBase user)
{
var result = false;
using (var conn = new SqlConnection(_connectionString))
{
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "DeleteTenderClaims";
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@deletedUser", user.Id);
cmd.Parameters.AddWithValue("@date", DateTime.Now);
conn.Open();
result = cmd.ExecuteNonQuery() > 0;
}
return result;
}
//Получени инфы о юзере из ActiveDirectory
private static UserBase GetUserById(string id)
{
UserBase user = null;
var domain = new PrincipalContext(ContextType.Domain);
var userPrincipal = UserPrincipal.FindByIdentity(domain, IdentityType.Sid, id);
if (userPrincipal != null)
{
var email = userPrincipal.EmailAddress;
var name = userPrincipal.DisplayName;
var sid = userPrincipal.Sid.Value;
var shortName = GetShortName(name);
user = new UserBase()
{
Id = sid,
Name = name,
ShortName = shortName,
Email = email,
Roles = new List<Role>() { Role.Enter }
};
}
return user;
}
public UserBase GetUserBase(List<Role> roles)
{
var user = new UserBase()
{
Id = this?.AdSid,
Name = this?.FullName,
ShortName = this?.DisplayName,
Email = this?.Email,
Roles = roles
};
return user;
}
