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