public ListViewModels(FilterTenderClaim filter, List<KeyValuePair<string, string>> subordinates, Role mainRole)
{
var db = new DbEngine();
var authorsSidNamePairs = Employee.GetUserListByAdGroup(AdGroup.SpeCalcManager);
var managersSidNamePairs = mainRole == Role.Manager
? subordinates
: authorsSidNamePairs;
authorsSidNamePairs.AddRange(Employee.GetUserListByAdGroup(AdGroup.SpeCalcOperator));
var productsSidNamePairs = mainRole == Role.ProductManager
? subordinates
: Employee.GetUserListByAdGroup(AdGroup.SpeCalcProduct);
Managers = new List<Manager>();
foreach (var pair in managersSidNamePairs)
{
Managers.Add(new Manager() { Id = pair.Key, ShortName = pair.Value });
}
ProductManagers = new List<ProductManager>();
foreach (var pair in productsSidNamePairs)
{
ProductManagers.Add(new ProductManager() { Id = pair.Key, ShortName = pair.Value });
}
Claims = db.FilterTenderClaims(filter);
db.SetProductManagersForClaims(Claims);
db.SetStatisticsForClaims(Claims);
foreach (var claim in Claims)
{
claim.Manager.ShortName = authorsSidNamePairs.Find(m => m.Key == claim.Manager.Id).Value;
claim.Author.ShortName = authorsSidNamePairs.Find(m => m.Key == claim.Author.Id).Value;
}
foreach (var claim in Claims)
{
foreach (var product in claim.ProductManagers)
{
product.ShortName = ProductManagers.Find(p => p.Id == product.Id)?.ShortName;
}
}
TotalClaimsCount = db.GetCountFilteredTenderClaims(filter);
Filter = filter;
}
//загрузка списка заявок в excel файл, с учетом фильтра - фильтр передается
//в параметре modelJson, сериализованный в формат JSON
public ActionResult GetListExcelFile(string modelJson)
{
XLWorkbook excBook = null;
var ms = new MemoryStream();
var error = false;
var message = string.Empty;
try
{
//получение объекта фильтра
var model = new FilterTenderClaim();
if (!string.IsNullOrEmpty(modelJson))
{
model = JsonConvert.DeserializeObject<FilterTenderClaim>(modelJson);
}
if (model.RowCount == 0) model.RowCount = 10;
//получение отфильтрованной инфы по заявкам из БД
var db = new DbEngine();
var list = db.FilterTenderClaims(model);
var tenderStatus = db.LoadTenderStatus();
//снабженцв и менеджеры из ActiveDirectory
var adProductManagers = UserHelper.GetProductManagers();
var managers = UserHelper.GetManagers();
if (list.Any())
{
db.SetProductManagersForClaims(list);
var claimProductManagers = list.SelectMany(x => x.ProductManagers).ToList();
foreach (var claimProductManager in claimProductManagers)
{
var managerFromAD = adProductManagers.FirstOrDefault(x => x.Id == claimProductManager.Id);
if (managerFromAD != null)
{
claimProductManager.Name = managerFromAD.Name;
claimProductManager.ShortName = managerFromAD.ShortName;
}
}
foreach (var claim in list)
{
var manager = managers.FirstOrDefault(x => x.Id == claim.Manager.Id);
if (manager != null)
{
claim.Manager = manager;
}
}
db.SetStatisticsForClaims(list);
var dealTypes = db.LoadDealTypes();
var status = db.LoadClaimStatus();
//Создание excel файла с инфой о заявках
excBook = new XLWorkbook();
var workSheet = excBook.AddWorksheet("Заявки");
//заголовок
workSheet.Cell(1, 1).Value = "ID";
workSheet.Cell(1, 2).Value = "№ Конкурса";
workSheet.Cell(1, 3).Value = "Контрагент";
workSheet.Cell(1, 4).Value = "Сумма";
workSheet.Cell(1, 5).Value = "Менеджер";
workSheet.Cell(1, 6).Value = "Позиции";
workSheet.Cell(1, 7).Value = "Снабженцы";
workSheet.Cell(1, 8).Value = "Тип сделки";
workSheet.Cell(1, 9).Value = "Статус";
workSheet.Cell(1, 10).Value = "Создано";
workSheet.Cell(1, 11).Value = "Срок сдачи";
workSheet.Cell(1, 12).Value = "Статус конкурса";
workSheet.Cell(1, 13).Value = "Автор";
workSheet.Cell(1, 14).Value = "Просроченна";
var headRange = workSheet.Range(workSheet.Cell(1, 1), workSheet.Cell(1, 14));
headRange.Style.Font.SetBold(true);
headRange.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
headRange.Style.Border.SetBottomBorder(XLBorderStyleValues.Thin);
headRange.Style.Border.SetBottomBorderColor(XLColor.Gray);
headRange.Style.Border.SetTopBorder(XLBorderStyleValues.Thin);
headRange.Style.Border.SetTopBorderColor(XLColor.Gray);
headRange.Style.Border.SetRightBorder(XLBorderStyleValues.Thin);
headRange.Style.Border.SetRightBorderColor(XLColor.Gray);
headRange.Style.Border.SetLeftBorder(XLBorderStyleValues.Thin);
headRange.Style.Border.SetLeftBorderColor(XLColor.Gray);
headRange.Style.Fill.BackgroundColor = XLColor.FromArgb(0, 204, 233, 255);
var row = 2;
//строки с инфой по заявкам
foreach (var claim in list)
{
workSheet.Cell(row, 1).Value = claim.Id.ToString("G");
workSheet.Cell(row, 2).Value = claim.TenderNumber;
workSheet.Cell(row, 3).Value = claim.Customer;
workSheet.Cell(row, 4).Value = claim.Sum.ToString("N2");
workSheet.Cell(row, 5).Value = claim.Manager.ShortName;
workSheet.Cell(row, 6).Value = "Всего: " + claim.PositionsCount + "\rРасчетов: " +
claim.CalculatesCount;
workSheet.Cell(row, 7).Value = claim.ProductManagers != null
? string.Join("\r", claim.ProductManagers.Select(x => x.ShortName + " " + x.PositionsCount + "/" + x.CalculatesCount))
: string.Empty;
workSheet.Cell(row, 8).Value = dealTypes.First(x => x.Id == claim.DealType).Value;
workSheet.Cell(row, 9).Value = status.First(x => x.Id == claim.ClaimStatus).Value;
workSheet.Cell(row, 10).Value = claim.RecordDate.ToString("dd.MM.yyyy");
workSheet.Cell(row, 10).DataType = XLCellValues.DateTime;
workSheet.Cell(row, 11).Value = claim.ClaimDeadline.ToString("dd.MM.yyyy");
workSheet.Cell(row, 11).DataType = XLCellValues.DateTime;
workSheet.Cell(row, 12).Value = tenderStatus.First(x => x.Id == claim.TenderStatus).Value;
workSheet.Cell(row, 13).Value = UserHelper.GetUserById(claim.Author.Id).ShortName;
var overDie = "Нет";
if (claim.ClaimDeadline > DateTime.Now)
{
if (claim.ClaimStatus != 1 || claim.ClaimStatus != 8)
{
overDie = "Да";
}
}
workSheet.Cell(row, 14).Value = overDie;
row++;
}
workSheet.Columns(6, 7).Style.Alignment.WrapText = true;
workSheet.Columns(1, 14).AdjustToContents();
excBook.SaveAs(ms);
excBook.Dispose();
ms.Seek(0, SeekOrigin.Begin);
}
else
{
error = true;
message = "Пустой набор";
}
}
catch (Exception)
{
error = true;
message = "Ошибка сервера";
}
finally
{
if (excBook != null)
{
excBook.Dispose();
}
}
if (!error)
{
var date = DateTime.Now.ToString("yyyyMMdd_HHmm");
return new FileStreamResult(ms, "application/vnd.ms-excel")
{
FileDownloadName = "Report-" + date + ".xlsx"
};
}
else
{
ViewBag.Message = message;
return View();
}
}
public JsonResult FilterClaim(FilterTenderClaim model)
{
var isComplete = false;
var list = new List<TenderClaim>();
var count = -1;
try
{
var user = GetUser();
var isController = UserHelper.IsController(user);
var isProduct = UserHelper.IsProductManager(user);
var isManager = UserHelper.IsManager(user);
var db = new DbEngine();
if (model.RowCount == 0) model.RowCount = 10;
if (string.IsNullOrEmpty(model.IdManager) && isManager && !isController)
{
var subMans = Employee.GetSubordinates(GetUser().Id);
model.IdManager = user.Id + "," + string.Join(",", subMans);
}
if (string.IsNullOrEmpty(model.IdProductManager) && isProduct && !isController)
{
var subProds = string.Join(",", Employee.GetSubordinates(GetUser().Id));
model.IdProductManager = user.Id + "," + subProds;
}
list = db.FilterTenderClaims(model);
var adProductManagers = UserHelper.GetProductManagers();
var managers = UserHelper.GetManagers();
if (list.Any())
{
db.SetProductManagersForClaims(list);
var claimProductManagers = list.SelectMany(x => x.ProductManagers).ToList();
foreach (var claimProductManager in claimProductManagers)
{
var managerFromAD = adProductManagers.FirstOrDefault(x => x.Id == claimProductManager.Id);
if (managerFromAD != null)
{
claimProductManager.Name = managerFromAD.Name;
claimProductManager.ShortName = managerFromAD.ShortName;
}
}
foreach (var claim in list)
{
var manager = managers.FirstOrDefault(x => x.Id == claim.Manager.Id);
if (manager != null)
{
claim.Manager.ShortName = manager.ShortName;
}
claim.Author = UserHelper.GetUserById(claim.Author.Id);
}
db.SetStatisticsForClaims(list);
}
count = db.GetCountFilteredTenderClaims(model);
isComplete = true;
}
catch (Exception)
{
isComplete = false;
}
return Json(new { IsComplete = isComplete, Claims = list, Count = count });
}
public ActionResult ListOld()
{
//получение пользователя и через наличие у него определенных ролей, определяются настройки по
//функциональности на странице
var user = GetUser();
if (user == null || !UserHelper.IsUserAccess(user))
{
var dict = new RouteValueDictionary();
dict.Add("message", "У Вас нет доступа к приложению");
return RedirectToAction("ErrorPage", "Auth", dict);
}
ViewBag.UserName = user.Name;
var showCalculate = false;
var showEdit = false;
var changeTenderStatus = false;
var filterProduct = string.Empty;
var filterManager = string.Empty;
var clickAction = string.Empty;
var posibleAction = string.Empty;
var userId = string.Empty;
var author = string.Empty;
var reportExcel = false;
var deleteClaim = "none";
var newClaim = "true";
var filterClaimStatus = new List<int>();
var isController = UserHelper.IsController(user);
var isTenderStatus = UserHelper.IsTenderStatus(user);
var isManager = UserHelper.IsManager(user);
var isProduct = UserHelper.IsProductManager(user);
var isOperator = UserHelper.IsOperator(user);
if (isController)
{
showCalculate = true;
showEdit = true;
changeTenderStatus = true;
clickAction = "editClaim";
posibleAction = "all";
reportExcel = true;
deleteClaim = "true";
}
else
{
if (isTenderStatus)
{
changeTenderStatus = true;
clickAction = "null";
posibleAction = "null";
newClaim = "false";
if (isOperator || isManager) newClaim = "true";
}
if (isManager)
{
showEdit = true;
//filterManager = user.Id;
clickAction = "editClaim";
posibleAction = "editClaim";
userId = user.Id;
filterClaimStatus.AddRange(new[] { 1, 2, 3, 6, 7 });
deleteClaim = "self&manager";
}
if (isProduct)
{
showCalculate = true;
// filterProduct = user.Id;
clickAction = "calculateClaim";
posibleAction = (isManager ? "all" : "calculateClaim");
userId = user.Id;
newClaim = "false";
if (isOperator || isManager) newClaim = "true";
if (!isManager) filterClaimStatus.AddRange(new[] { 2, 3, 6, 7 });
}
if (isOperator)
{
showEdit = true;
clickAction = "editClaim";
posibleAction = (isProduct ? "all" : "editClaim");
author = user.Id;
deleteClaim = "self";
}
}
ViewBag.Settings = new
{
showCalculate,
showEdit,
changeTenderStatus,
filterProduct,
filterManager,
clickAction,
posibleAction,
userId,
filterClaimStatus,
author,
reportExcel,
deleteClaim,
newClaim
};
ViewBag.Error = false.ToString().ToLower();
ViewBag.ClaimCount = 0;
try
{
//получение инфы по заявкам из БД
var db = new DbEngine();
var filter = new FilterTenderClaim()
{
RowCount = 30,
};
var subsProduct = new List<KeyValuePair<string, string>>();
var subsManagers = new List<KeyValuePair<string, string>>();
if (!string.IsNullOrEmpty(filterManager)) filter.IdManager = filterManager;
else
{
if (isManager && !isController)
{
filter.IdManager = user.Id;
subsManagers = Employee.GetSubordinates(user.Id).ToList();
if (subsManagers.Any())
{
filter.IdManager = user.Id;// + ","+ String.Join(",", subsManagers);
foreach (var sub in subsManagers)
{
if (sub.Key != null)
{
filter.IdManager += "," + sub.Key;
}
}
}
}
}
if (!string.IsNullOrEmpty(filterProduct)) filter.IdProductManager = filterProduct;
else
{
if (isProduct && !isController)
{
filter.IdProductManager = user.Id;
subsProduct = Employee.GetSubordinates(user.Id).ToList();
if (subsProduct.Any())
{
filter.IdProductManager = user.Id;// + "," + String.Join(",", subsProduct);
foreach (var sub in subsProduct)
{
if (sub.Key != null)
{
filter.IdProductManager += "," + sub.Key;
}
}
}
}
//filter.IdProductManager = isProduct && !isController
// ? String.Join(",", Employee.GetSubordinates(user.Id))
// : String.Empty;
}
if (!string.IsNullOrEmpty(author)) filter.Author = author;
if (filterClaimStatus.Any()) filter.ClaimStatus = filterClaimStatus;
var claims = db.FilterTenderClaims(filter);
//снабженцы и менеджеры из ActiveDirectory
var prodManSelList = UserHelper.GetProductManagersSelectionList();
var adProductManagers = new List<ProductManager>();
adProductManagers = prodManSelList;
if (!isController && isProduct)
{
var subProds = Employee.GetSubordinateProductManagers(user.Id, subsProduct);
if (subProds.Any())
{
adProductManagers = subProds;
}
else
{
var curProd = new ProductManager() {Id = user.Id, ShortName = user.ShortName};
adProductManagers = new List<ProductManager>();
adProductManagers.Add(curProd);
}
}
var manSelList = UserHelper.GetManagersSelectionList();
var managers = new List<Manager>();
managers = manSelList;
if (!isController && isManager)
{
var subMans = Employee.GetSubordinateManagers(user.Id, subsManagers);
if (subMans.Any())
{
managers = subMans;
}
else
{
var curMan = new Manager() { Id = user.Id, ShortName = user.ShortName };
managers = new List<Manager>();
managers.Add(curMan);
}
}
//var prodManSelList = UserHelper.Get();
if (claims != null && claims.Any())
{
db.SetProductManagersForClaims(claims);
var claimProductManagers = claims.SelectMany(x => x.ProductManagers).ToList();
foreach (var claimProductManager in claimProductManagers)
{
claimProductManager.ShortName = prodManSelList.FirstOrDefault(x=>x.Id== claimProductManager.Id)?.ShortName;
//var productUser = UserHelper.GetUserById(claimProductManager.Id);
//if (productUser != null)
//{
// claimProductManager.Name = productUser.Name;
// claimProductManager.Name = productUser.ShortName;
//}
//var managerFromAD = adProductManagers.FirstOrDefault(x => x.Id == claimProductManager.Id);
//if (managerFromAD != null)
//{
// claimProductManager.Name = managerFromAD.Name;
// claimProductManager.ShortName = managerFromAD.ShortName;
//}
}
//var authorsList = UserHelper.GetAuthorsSelectionList();
foreach (var claim in claims)
{
claim.Manager.ShortName = manSelList.FirstOrDefault(x => x.Id == claim.Manager.Id)?.ShortName;
//if (manager != null)
//{
// claim.Manager.ShortName = manager.ShortName;
//}
//var auth = authorsList.SingleOrDefault(x => x.Key == claim.Author.Id);
claim.Author = UserHelper.GetUserById(claim.Author.Id);//new UserBase() { Id= auth.Key, ShortName = auth.Value};// UserHelper.GetUserById(claim.Author.Id);
}
db.SetStatisticsForClaims(claims);
}
ViewBag.Claims = claims;
ViewBag.DealTypes = db.LoadDealTypes();
ViewBag.ClaimStatus = db.LoadClaimStatus();
ViewBag.ProductManagers = adProductManagers;
ViewBag.Managers = managers;
ViewBag.ClaimCount = db.GetCountFilteredTenderClaims(filter);
ViewBag.TenderStatus = db.LoadTenderStatus();
}
catch (Exception ex)
{
ViewBag.Error = true.ToString().ToLower();
}
return View();
}
public int GetCountFilteredTenderClaims(FilterTenderClaim filter)
{
var count = -1;
using (var conn = new SqlConnection(_connectionString))
{
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "FilterTenderClaimsCount";
if (filter.IdClaim != 0) cmd.Parameters.AddWithValue("@idClaim", filter.IdClaim);
if (!string.IsNullOrEmpty(filter.TenderNumber)) cmd.Parameters.AddWithValue("@tenderNumber", filter.TenderNumber);
if (!string.IsNullOrEmpty(filter.Customer)) cmd.Parameters.AddWithValue("@customer", filter.Customer);
if (filter.ClaimStatus != null && filter.ClaimStatus.Any()) cmd.Parameters.AddWithValue("@claimStatusIds", string.Join(",", filter.ClaimStatus));
if (filter.DealTypeId != 0) cmd.Parameters.AddWithValue("@dealTypeId", filter.DealTypeId);
if (!string.IsNullOrEmpty(filter.IdManager)) cmd.Parameters.AddWithValue("@manager", filter.IdManager);
if (!string.IsNullOrEmpty(filter.ManagerSubDivision)) cmd.Parameters.AddWithValue("@managerSubDivision", filter.ManagerSubDivision);
if (filter.Overdie.HasValue) cmd.Parameters.AddWithValue("@overdie", filter.Overdie);
if (!string.IsNullOrEmpty(filter.IdProductManager)) cmd.Parameters.AddWithValue("@idProductManager", filter.IdProductManager);
if (!string.IsNullOrEmpty(filter.Author)) cmd.Parameters.AddWithValue("@author", filter.Author);
if (!string.IsNullOrEmpty(filter.TenderStartFrom)) cmd.Parameters.AddWithValue("@tenderStartFrom", DateTime.ParseExact(filter.TenderStartFrom, "dd.MM.yyyy", CultureInfo.CurrentCulture));
if (!string.IsNullOrEmpty(filter.TenderStartTo)) cmd.Parameters.AddWithValue("@tenderStartTo", DateTime.ParseExact(filter.TenderStartTo, "dd.MM.yyyy", CultureInfo.CurrentCulture));
conn.Open();
count = (int)cmd.ExecuteScalar();
}
return count;
}
public List<TenderClaim> FilterTenderClaims(FilterTenderClaim filter)
{
var list = new List<TenderClaim>();
using (var conn = new SqlConnection(_connectionString))
{
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "FilterTenderClaims";
cmd.Parameters.AddWithValue("@rowCount", filter.RowCount);
if (filter.IdClaim != 0) cmd.Parameters.AddWithValue("@idClaim", filter.IdClaim);
if (!string.IsNullOrEmpty(filter.TenderNumber)) cmd.Parameters.AddWithValue("@tenderNumber", filter.TenderNumber);
if (!string.IsNullOrEmpty(filter.Customer)) cmd.Parameters.AddWithValue("@customer", filter.Customer);
if (filter.ClaimStatus != null && filter.ClaimStatus.Any()) cmd.Parameters.AddWithValue("@claimStatusIds", string.Join(",", filter.ClaimStatus));
if (filter.DealTypeId != 0) cmd.Parameters.AddWithValue("@dealTypeId", filter.DealTypeId);
if (!string.IsNullOrEmpty(filter.IdManager)) cmd.Parameters.AddWithValue("@manager", filter.IdManager);
if (!string.IsNullOrEmpty(filter.ManagerSubDivision)) cmd.Parameters.AddWithValue("@managerSubDivision", filter.ManagerSubDivision);
if (filter.Overdie.HasValue) cmd.Parameters.AddWithValue("@overdie", filter.Overdie);
if (!string.IsNullOrEmpty(filter.IdProductManager)) cmd.Parameters.AddWithValue("@idProductManager", filter.IdProductManager);
if (!string.IsNullOrEmpty(filter.Author)) cmd.Parameters.AddWithValue("@author", filter.Author);
if (!string.IsNullOrEmpty(filter.TenderStartFrom)) cmd.Parameters.AddWithValue("@tenderStartFrom", DateTime.ParseExact(filter.TenderStartFrom, "dd.MM.yyyy", CultureInfo.CurrentCulture));
if (!string.IsNullOrEmpty(filter.TenderStartTo)) cmd.Parameters.AddWithValue("@tenderStartTo", DateTime.ParseExact(filter.TenderStartTo, "dd.MM.yyyy", CultureInfo.CurrentCulture));
conn.Open();
var rd = cmd.ExecuteReader();
if (rd.HasRows)
{
while (rd.Read())
{
var model = new TenderClaim()
{
Id = rd.GetInt32(0),
TenderNumber = rd.GetString(1),
TenderStart = rd.GetDateTime(2),
ClaimDeadline = rd.GetDateTime(3),
KPDeadline = rd.GetDateTime(4),
Comment = rd.GetString(5),
Customer = rd.GetString(6),
Sum = (double) rd.GetDecimal(8),
DealType = rd.GetInt32(9),
TenderUrl = rd.GetString(10),
TenderStatus = rd.GetInt32(11),
Manager = new Manager()
{
Id = rd.GetString(12),
SubDivision = rd.GetString(13)
},
ClaimStatus = rd.GetInt32(14),
RecordDate = rd.GetDateTime(15),
Author = new UserBase() {Id = rd.GetString(17)},
CurrencyUsd = (double)rd.GetDecimal(20),
CurrencyEur = (double)rd.GetDecimal(21),
DeliveryDate = rd[22] == DBNull.Value ? null : (DateTime?)rd.GetDateTime(22),
DeliveryPlace = rd.GetString(23),
AuctionDate = rd[24] == DBNull.Value ? null : (DateTime?)rd.GetDateTime(24),
ProductManagers = new List<ProductManager>(),
StrSum = String.Format("{0:### ### ### ### ###.##}", (double)rd.GetDecimal(8))
};
if (!rd.IsDBNull(7))
{
model.CustomerInn = rd.GetString(7);
}
else
{
model.CustomerInn = String.Empty;}
if (model.Sum.Equals(-1)) model.Sum = 0;
if (model.CurrencyUsd.Equals(-1)) model.CurrencyUsd = 0;
if (model.CurrencyEur.Equals(-1)) model.CurrencyEur = 0;
model.KPDeadlineString = model.KPDeadline.ToString("dd.MM.yyyy");
model.TenderStartString = model.TenderStart.ToString("dd.MM.yyyy");
model.ClaimDeadlineString = model.ClaimDeadline.ToString("dd.MM.yyyy");
model.RecordDateString = model.RecordDate.ToString("dd.MM.yyyy HH:mm");
model.DeliveryDateString = model.DeliveryDate.HasValue
? model.DeliveryDate.Value.ToString("dd.MM.yyyy")
: string.Empty;
model.AuctionDateString = model.AuctionDate.HasValue
? model.AuctionDate.Value.ToString("dd.MM.yyyy")
: string.Empty;
list.Add(model);
}
}
rd.Dispose();
}
return list;
}