/// <summary> /// Функция, возвращающая количество записей о карточках /// </summary> /// <param name="searchString"></param> /// <param name="filter"></param> /// <returns></returns> public long GetCount(string searchString, CardFilterDefinition filter) { var request = _applicationContext.Cards.AsQueryable().Where(x => x.DeletedAt == null); if (!string.IsNullOrEmpty(searchString)) { request = request.Where(x => x.Id.ToString().Contains(searchString)); } #region Применение фильтров if (filter.IsByEmployee && filter.EmployeeId != null) { request = request.Where(x => x.EmployeeId == filter.EmployeeId); } if (filter.IsBySumWorkTime) { if (filter.SumWorkTimeLowBound != null) { request = request.Where(x => x.SumWorkLoadTime >= filter.SumWorkTimeLowBound); } if (filter.SumWorkTimeHighBound != null) { request = request.Where(x => x.SumWorkLoadTime <= filter.SumWorkTimeHighBound); } } if (filter.IsByDatePass) { if (filter.DatePassLowBound != null) { request = request.Where(x => x.DatePass >= filter.DatePassLowBound); } if (filter.DatePassHighBound != null) { request = request.Where(x => x.DatePass <= filter.DatePassHighBound); } } #endregion return(request.Count()); }
/// <summary> /// Функция возвращает список записей о карточках /// отработанного времени /// </summary> /// <param name="searchString">Строка поиска</param> /// <param name="sortBy">Поле, по которому сортируется спиоск</param> /// <param name="sortDirection">Направление сортировки true - возрастание, false - убывание</param> /// <param name="filter">Критерии выбора данных</param> /// <param name="limit">Ограничение величины списка</param> /// <param name="offset">Смещение относительно начала списка</param> /// <returns>Список записей работников</returns> public List <Card> Get(string searchString, string sortBy, bool sortDirection, CardFilterDefinition filter, int limit, int offset) { var request = _applicationContext.Cards.AsQueryable().Where(x => x.DeletedAt == null); if (!string.IsNullOrEmpty(searchString)) { request = request.Where(x => x.Id.ToString().Contains(searchString)); } #region Применение фильтров if (filter.IsByEmployee && filter.EmployeeId != null) { request = request.Where(x => x.EmployeeId == filter.EmployeeId); } if (filter.IsBySumWorkTime) { if (filter.SumWorkTimeLowBound != null) { request = request.Where(x => x.SumWorkLoadTime >= filter.SumWorkTimeLowBound); } if (filter.SumWorkTimeHighBound != null) { request = request.Where(x => x.SumWorkLoadTime <= filter.SumWorkTimeHighBound); } } if (filter.IsByDatePass) { if (filter.DatePassLowBound != null) { request = request.Where(x => x.DatePass >= filter.DatePassLowBound); } if (filter.DatePassHighBound != null) { request = request.Where(x => x.DatePass <= filter.DatePassHighBound); } } #endregion //Применение сортировки switch (sortBy) { case "WorkLoadTimeMonday": request = sortDirection ? request.OrderBy(x => x.WorkLoadTimeMonday) : request.OrderByDescending(x => x.WorkLoadTimeMonday); break; case "WorkLoadTimeTuesday": request = sortDirection ? request.OrderBy(x => x.WorkLoadTimeTuesday) : request.OrderByDescending(x => x.WorkLoadTimeTuesday); break; case "WorkLoadTimeWednesday": request = sortDirection ? request.OrderBy(x => x.WorkLoadTimeWednesday) : request.OrderByDescending(x => x.WorkLoadTimeWednesday); break; case "WorkLoadTimeThursday": request = sortDirection ? request.OrderBy(x => x.WorkLoadTimeThursday) : request.OrderByDescending(x => x.WorkLoadTimeThursday); break; case "WorkLoadTimeFriday": request = sortDirection ? request.OrderBy(x => x.WorkLoadTimeFriday) : request.OrderByDescending(x => x.WorkLoadTimeFriday); break; case "DatePass": request = sortDirection ? request.OrderBy(x => x.DatePass) : request.OrderByDescending(x => x.DatePass); break; case "Employee": request = sortDirection ? request.OrderBy(x => x.EmployeeId) : request.OrderByDescending(x => x.EmployeeId); break; case "SumWorkLoadTime": request = sortDirection ? request.OrderBy(x => x.SumWorkLoadTime) : request.OrderByDescending(x => x.SumWorkLoadTime); break; case "Payment": request = sortDirection ? request.OrderBy(x => x.Payment) : request.OrderByDescending(x => x.Payment); break; case "PaymentFull": request = sortDirection ? request.OrderBy(x => x.PaymentFull) : request.OrderByDescending(x => x.PaymentFull); break; default: request = sortDirection ? request.OrderBy(x => x.Id) : request.OrderByDescending(x => x.Id); break; } return(request.Skip(offset).Take(limit).ToList()); }
/// <summary> /// Функция, экспортирующая список карточек загруженности /// в файл MS Excel. /// </summary> /// <param name="searchString">Строка поиска</param> /// <param name="sortBy">Поле, по которому производится сортировка</param> /// <param name="sortDirection">Направление сортировки</param> /// <param name="filter">Критерии выбора данных</param> public void SaveExcelDocument(string searchString, string sortBy, bool sortDirection, CardFilterDefinition filter) { var cards = this.Get(searchString, sortBy, sortDirection, filter, int.MaxValue, 0); var workbook = new XSSFWorkbook(); var sheet = workbook.CreateSheet(); workbook.SetSheetName(0, "Карточки загруженности"); var header = sheet.CreateRow(0); //Создание строки заголовка var tableSheetHeader = new[] { "№", "Работник", "Дата сдачи", "Пн", "Вт", "Ср", "Чт", "Пт", "Отработно всего", "З/П в час", "З/П всего" }; for (var i = 0; i < tableSheetHeader.Length; i++) { header.CreateCell(i).SetCellValue(tableSheetHeader[i]); } //Заполнение строк таблицы for (int i = 0; i < cards.Count; i++) { var row = sheet.CreateRow(sheet.PhysicalNumberOfRows); row.CreateCell(0).SetCellValue(cards[i].Id); row.CreateCell(1).SetCellValue(cards[i].Employee.Signature); row.CreateCell(2).SetCellValue(cards[i].DatePass.ToString("dd.MM.yyyy")); row.CreateCell(3).SetCellValue(cards[i].WorkLoadTimeMonday.ToString("0:00")); row.CreateCell(4).SetCellValue(cards[i].WorkLoadTimeTuesday.ToString("0:00")); row.CreateCell(5).SetCellValue(cards[i].WorkLoadTimeWednesday.ToString("0:00")); row.CreateCell(6).SetCellValue(cards[i].WorkLoadTimeThursday.ToString("0:00")); row.CreateCell(7).SetCellValue(cards[i].WorkLoadTimeFriday.ToString("0:00")); row.CreateCell(8).SetCellValue(cards[i].SumWorkLoadTime.ToString("0:00")); row.CreateCell(9).SetCellValue(cards[i].Payment.ToString(CultureInfo.GetCultureInfo("en-GB"))); row.CreateCell(10).SetCellValue(cards[i].PaymentFull.ToString(CultureInfo.GetCultureInfo("en-GB"))); } for (var i = 0; i < tableSheetHeader.Length; i++) { sheet.AutoSizeColumn(i); } var dialog = new SaveFileDialog { InitialDirectory = @"~/Documents", Title = $"Путь к экспортируемой таблице карточек загруженности", AddExtension = true, Filter = "Файлы Excel 2007 (*.xlsx)|*.xlsx|Все остальные файлы (*.*)|*.*" }; if (dialog.ShowDialog() == DialogResult.OK) { if (!File.Exists(dialog.FileName)) { File.Delete(dialog.FileName); } //запись в файл using (var fs = new FileStream(dialog.FileName, FileMode.Create, FileAccess.Write)) { workbook.Write(fs); } } }