public async Task <OperationDataResult <FileStreamModel> > GenerateSingleListResults( ItemListCasesPnRequestModel requestModel) { string excelFile = null; try { var reportDataResult = await GetTableData(requestModel); if (reportDataResult == null) { return(new OperationDataResult <FileStreamModel>(false, "ERROR")); } var itemList = await _dbContext.ItemLists.SingleOrDefaultAsync(x => x.Id == requestModel.ListId); excelFile = _excelService.CopyTemplateForNewAccount("report_template_lists"); var writeResult = _excelService.WriteTableToExcel(itemList.Name, itemList.Description, reportDataResult, requestModel, excelFile); if (!writeResult) { throw new Exception($"Error while writing excel file {excelFile}"); } var result = new FileStreamModel() { FilePath = excelFile, FileStream = new FileStream(excelFile, FileMode.Open), }; return(new OperationDataResult <FileStreamModel>(true, result)); } catch (Exception e) { if (!string.IsNullOrEmpty(excelFile) && File.Exists(excelFile)) { File.Delete(excelFile); } Trace.TraceError(e.Message); //_logger.LogError(e.Message); return(new OperationDataResult <FileStreamModel>( false, _itemsPlanningLocalizationService.GetString("ErrorWhileGeneratingReportFile"))); } }
public async Task GenerateSingleListResults(ItemListCasesPnRequestModel requestModel) { OperationDataResult <FileStreamModel> result = await _listService.GenerateSingleListResults(requestModel); const int bufferSize = 4086; byte[] buffer = new byte[bufferSize]; Response.OnStarting(async() => { try { if (!result.Success) { Response.ContentLength = result.Message.Length; Response.ContentType = "text/plain"; Response.StatusCode = 400; byte[] bytes = Encoding.UTF8.GetBytes(result.Message); await Response.Body.WriteAsync(bytes, 0, result.Message.Length); await Response.Body.FlushAsync(); } else { using (FileStream excelStream = result.Model.FileStream) { int bytesRead; Response.ContentLength = excelStream.Length; Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; while ((bytesRead = excelStream.Read(buffer, 0, buffer.Length)) > 0 && !HttpContext.RequestAborted.IsCancellationRequested) { await Response.Body.WriteAsync(buffer, 0, bytesRead); await Response.Body.FlushAsync(); } } } } finally { if (!string.IsNullOrEmpty(result?.Model?.FilePath) && System.IO.File.Exists(result.Model.FilePath)) { System.IO.File.Delete(result.Model.FilePath); } } }); // return await _listService.GenerateSingleListResults(requestModel); }
public bool WriteTableToExcel(string name, string description, ItemListPnCaseResultListModel reportModel, ItemListCasesPnRequestModel requestModel, string destFile) { var workbook = new XLWorkbook(destFile); var worksheet = workbook.Worksheets.First(); // Fill base info var nameTitle = _itemsPlanningLocalizationService.GetString("Name"); worksheet.Cell(2, 2).Value = nameTitle; worksheet.Cell(2, 3).Value = name; var descriptionTitle = _itemsPlanningLocalizationService.GetString("Description"); worksheet.Cell(3, 2).Value = descriptionTitle; worksheet.Cell(3, 3).Value = description; var periodFromTitle = _itemsPlanningLocalizationService.GetString("DateFrom"); worksheet.Cell(5, 2).Value = periodFromTitle; worksheet.Cell(5, 3).Value = requestModel.DateFrom?.ToString("MM/dd/yyyy"); var periodToTitle = _itemsPlanningLocalizationService.GetString("DateTo"); worksheet.Cell(6, 2).Value = periodToTitle; worksheet.Cell(6, 3).Value = requestModel.DateTo?.ToString("MM/dd/yyyy"); var col = 2; var row = 8; // Fill headers worksheet = SetHeaders(worksheet, row, col, reportModel); row = 9; worksheet = SetRows(worksheet, row, col, reportModel); workbook.Save(); return(true); }
public async Task <OperationDataResult <ItemsListCasePnModel> > GetSingleList(ItemListCasesPnRequestModel requestModel) { try { var newItems = (_dbContext.Items.Where(item => item.ItemListId == requestModel.ListId) .Join(_dbContext.ItemCases, item => item.Id, itemCase => itemCase.ItemId, (item, itemCase) => new { itemCase.Id, item.Name, item.Description, item.Type, item.BuildYear, item.ItemNumber, itemCase.Comment, itemCase.Location, itemCase.FieldStatus, itemCase.NumberOfImages, itemCase.WorkflowState, itemCase.CreatedAt, itemCase.MicrotingSdkCaseId, itemCase.MicrotingSdkeFormId, itemCase.Status })); newItems = newItems .Where(x => x.WorkflowState != Constants.WorkflowStates.Removed); newItems = QueryHelper.AddSortToQuery(newItems, requestModel.Sort, requestModel.IsSortDsc); var total = await newItems.Select(x => x.BuildYear).CountAsync(); newItems = newItems .Skip(requestModel.Offset) .Take(requestModel.PageSize); if (newItems.Any()) { var itemsListCasePnModel = new ItemsListCasePnModel { Items = await newItems.Select(x => new ItemsListPnItemCaseModel() { Id = x.Id, Date = x.CreatedAt, CreatedAt = x.CreatedAt, Name = x.Name, ItemNumber = x.ItemNumber, BuildYear = x.BuildYear, Description = x.Description, Type = x.Type, Comment = x.Comment, Location = x.Location, FieldStatus = x.FieldStatus, NumberOfImages = x.NumberOfImages, SdkCaseId = x.MicrotingSdkCaseId, SdkeFormId = x.MicrotingSdkeFormId, Status = x.Status }).ToListAsync(), Total = total, }; return(new OperationDataResult <ItemsListCasePnModel>( true, itemsListCasePnModel)); } else { return(new OperationDataResult <ItemsListCasePnModel>( false, "")); } } catch (Exception ex) { return(new OperationDataResult <ItemsListCasePnModel>( false, ex.Message)); } }
private async Task <ItemListPnCaseResultListModel> GetTableData(ItemListCasesPnRequestModel requestModel) { var pluginConfigurationValue = _options.Value.Token; var itemList = await _dbContext.ItemLists .SingleOrDefaultAsync(x => x.Id == requestModel.ListId); var core = await _core.GetCore(); await using var microtingDbContext = core.DbContextHelper.GetDbContext(); var locale = await _userService.GetCurrentUserLocale(); var language = microtingDbContext.Languages.Single(x => x.LanguageCode.ToLower() == locale.ToLower()); var allFields = await core.Advanced_TemplateFieldReadAll(itemList.RelatedEFormId, language); // filter fields allFields = allFields.Where(x => x.FieldType != Constants.FieldTypes.SaveButton).ToList(); var itemListPnCaseResultListModel = new ItemListPnCaseResultListModel { LabelEnabled = itemList.LabelEnabled, DescriptionEnabled = itemList.DescriptionEnabled, DeployedAtEnabled = itemList.DeployedAtEnabled, DoneAtEnabled = itemList.DoneAtEnabled, DoneByUserNameEnabled = itemList.DoneByUserNameEnabled, UploadedDataEnabled = itemList.UploadedDataEnabled, ItemNumberEnabled = itemList.ItemNumberEnabled, LocationCodeEnabled = itemList.LocationCodeEnabled, BuildYearEnabled = itemList.BuildYearEnabled, TypeEnabled = itemList.TypeEnabled, NumberOfImagesEnabled = itemList.NumberOfImagesEnabled, SdkeFormId = itemList.RelatedEFormId, FieldEnabled1 = itemList.SdkFieldEnabled1, FieldEnabled2 = itemList.SdkFieldEnabled2, FieldEnabled3 = itemList.SdkFieldEnabled3, FieldEnabled4 = itemList.SdkFieldEnabled4, FieldEnabled5 = itemList.SdkFieldEnabled5, FieldEnabled6 = itemList.SdkFieldEnabled6, FieldEnabled7 = itemList.SdkFieldEnabled7, FieldEnabled8 = itemList.SdkFieldEnabled8, FieldEnabled9 = itemList.SdkFieldEnabled9, FieldEnabled10 = itemList.SdkFieldEnabled10, FieldName1 = itemList.SdkFieldEnabled1 ? allFields.SingleOrDefault(x => x.Id == itemList.SdkFieldId1)?.Label : "", FieldName2 = itemList.SdkFieldEnabled2 ? allFields.SingleOrDefault(x => x.Id == itemList.SdkFieldId2)?.Label : "", FieldName3 = itemList.SdkFieldEnabled3 ? allFields.SingleOrDefault(x => x.Id == itemList.SdkFieldId3)?.Label : "", FieldName4 = itemList.SdkFieldEnabled4 ? allFields.SingleOrDefault(x => x.Id == itemList.SdkFieldId4)?.Label : "", FieldName5 = itemList.SdkFieldEnabled5 ? allFields.SingleOrDefault(x => x.Id == itemList.SdkFieldId5)?.Label : "", FieldName6 = itemList.SdkFieldEnabled6 ? allFields.SingleOrDefault(x => x.Id == itemList.SdkFieldId6)?.Label : "", FieldName7 = itemList.SdkFieldEnabled7 ? allFields.SingleOrDefault(x => x.Id == itemList.SdkFieldId7)?.Label : "", FieldName8 = itemList.SdkFieldEnabled8 ? allFields.SingleOrDefault(x => x.Id == itemList.SdkFieldId8)?.Label : "", FieldName9 = itemList.SdkFieldEnabled9 ? allFields.SingleOrDefault(x => x.Id == itemList.SdkFieldId9)?.Label : "", FieldName10 = itemList.SdkFieldEnabled10 ? allFields.SingleOrDefault(x => x.Id == itemList.SdkFieldId10)?.Label : "", }; var newItems = _dbContext.Items .Where(item => item.ItemListId == requestModel.ListId) .Join(_dbContext.ItemCases, item => item.Id, itemCase => itemCase.ItemId, (item, itemCase) => new { itemCase.Id, item.Name, item.Description, item.BuildYear, item.LocationCode, item.ItemNumber, item.Type, itemCase.MicrotingSdkCaseDoneAt, itemCase.MicrotingSdkCaseId, itemCase.Status, itemCase.CreatedAt, itemCase.DoneByUserName, itemCase.SdkFieldValue1, itemCase.SdkFieldValue2, itemCase.SdkFieldValue3, itemCase.SdkFieldValue4, itemCase.SdkFieldValue5, itemCase.SdkFieldValue6, itemCase.SdkFieldValue7, itemCase.SdkFieldValue8, itemCase.SdkFieldValue9, itemCase.SdkFieldValue10, itemCase.WorkflowState, itemCase.NumberOfImages }); if (requestModel.DateFrom != null) { newItems = newItems.Where(x => x.CreatedAt >= new DateTime(requestModel.DateFrom.Value.Year, requestModel.DateFrom.Value.Month, requestModel.DateFrom.Value.Day, 0, 0, 0)); } if (requestModel.DateTo != null) { newItems = newItems.Where(x => x.CreatedAt <= new DateTime(requestModel.DateTo.Value.Year, requestModel.DateTo.Value.Month, requestModel.DateTo.Value.Day, 23, 59, 59)); } // Add sort newItems = QueryHelper.AddSortToQuery(newItems, requestModel.Sort, requestModel.IsSortDsc); newItems = newItems .Where(x => x.WorkflowState != Constants.WorkflowStates.Removed); // Get total itemListPnCaseResultListModel.Total = await newItems.Select(x => x.Id).CountAsync(); // Pagination newItems = newItems .Skip(requestModel.Offset) .Take(requestModel.PageSize); // add select and get from db itemListPnCaseResultListModel.Items = newItems .Select(x => new ItemsListPnCaseResultModel { Id = x.Id, DoneAt = x.MicrotingSdkCaseDoneAt, DeployedAt = x.CreatedAt, DoneByUserName = x.DoneByUserName, Label = x.Name, Description = x.Description, ItemNumber = x.ItemNumber, LocationCode = x.LocationCode, BuildYear = x.BuildYear, Type = x.Type, NumberOfImages = x.NumberOfImages, Field1 = x.SdkFieldValue1, Field2 = x.SdkFieldValue2, Field3 = x.SdkFieldValue3, Field4 = x.SdkFieldValue4, Field5 = x.SdkFieldValue5, Field6 = x.SdkFieldValue6, Field7 = x.SdkFieldValue7, Field8 = x.SdkFieldValue8, Field9 = x.SdkFieldValue9, Field10 = x.SdkFieldValue10, SdkCaseId = x.MicrotingSdkCaseId, SdkeFormId = itemList.RelatedEFormId, Status = x.Status, Token = pluginConfigurationValue, }) .ToList(); return(itemListPnCaseResultListModel); }
public async Task <OperationDataResult <ItemListPnCaseResultListModel> > GetSingleListResults(ItemListCasesPnRequestModel requestModel) { ItemListPnCaseResultListModel itemListPnCaseResultListModel = await GetTableData(requestModel); return(new OperationDataResult <ItemListPnCaseResultListModel>(true, itemListPnCaseResultListModel)); }
public async Task <OperationDataResult <ItemListPnCaseResultListModel> > GetSingleListResults(ItemListCasesPnRequestModel requestModel) { return(await _listService.GetSingleListResults(requestModel)); }
private async Task <ItemListPnCaseResultListModel> GetTableData(ItemListCasesPnRequestModel requestModel) { PluginConfigurationValue pluginConfigurationValue = await _dbContext.PluginConfigurationValues.SingleOrDefaultAsync(x => x.Name == "ItemsPlanningBaseSettings:Token"); var itemList = await _dbContext.ItemLists.SingleOrDefaultAsync(x => x.Id == requestModel.ListId); var core = await _core.GetCore(); await using MicrotingDbContext microtingDbContext = core.DbContextHelper.GetDbContext(); var locale = await _userService.GetCurrentUserLocale(); Language language = microtingDbContext.Languages.Single(x => x.LanguageCode.ToLower() == locale.ToLower()); List <FieldDto> allFields = await _core.GetCore().Result.Advanced_TemplateFieldReadAll(itemList.RelatedEFormId, language); int i = 0; List <int> toBeRemoved = new List <int>(); foreach (FieldDto field in allFields) { if (field.FieldType == Constants.FieldTypes.SaveButton) { toBeRemoved.Add(i); } i += 1; } foreach (int i1 in toBeRemoved) { allFields.RemoveAt(i1); } ItemListPnCaseResultListModel itemListPnCaseResultListModel = new ItemListPnCaseResultListModel(); itemListPnCaseResultListModel.Total = 0; itemListPnCaseResultListModel.LabelEnabled = itemList.LabelEnabled; itemListPnCaseResultListModel.DescriptionEnabled = itemList.DescriptionEnabled; itemListPnCaseResultListModel.DeployedAtEnabled = itemList.DeployedAtEnabled; itemListPnCaseResultListModel.DoneAtEnabled = itemList.DoneAtEnabled; itemListPnCaseResultListModel.DoneByUserNameEnabled = itemList.DoneByUserNameEnabled; itemListPnCaseResultListModel.UploadedDataEnabled = itemList.UploadedDataEnabled; itemListPnCaseResultListModel.ItemNumberEnabled = itemList.ItemNumberEnabled; itemListPnCaseResultListModel.LocationCodeEnabled = itemList.LocationCodeEnabled; itemListPnCaseResultListModel.BuildYearEnabled = itemList.BuildYearEnabled; itemListPnCaseResultListModel.TypeEnabled = itemList.TypeEnabled; itemListPnCaseResultListModel.NumberOfImagesEnabled = itemList.NumberOfImagesEnabled; itemListPnCaseResultListModel.SdkeFormId = itemList.RelatedEFormId; itemListPnCaseResultListModel.FieldEnabled1 = itemList.SdkFieldEnabled1; if (itemListPnCaseResultListModel.FieldEnabled1) { itemListPnCaseResultListModel.FieldName1 = allFields.SingleOrDefault(x => x.Id == itemList.SdkFieldId1)?.Label; } itemListPnCaseResultListModel.FieldEnabled2 = itemList.SdkFieldEnabled2; if (itemListPnCaseResultListModel.FieldEnabled2) { itemListPnCaseResultListModel.FieldName2 = allFields.SingleOrDefault(x => x.Id == itemList.SdkFieldId2)?.Label; } itemListPnCaseResultListModel.FieldEnabled3 = itemList.SdkFieldEnabled3; if (itemListPnCaseResultListModel.FieldEnabled3) { itemListPnCaseResultListModel.FieldName3 = allFields.SingleOrDefault(x => x.Id == itemList.SdkFieldId3)?.Label; } itemListPnCaseResultListModel.FieldEnabled4 = itemList.SdkFieldEnabled4; if (itemListPnCaseResultListModel.FieldEnabled4) { itemListPnCaseResultListModel.FieldName4 = allFields.SingleOrDefault(x => x.Id == itemList.SdkFieldId4)?.Label; } itemListPnCaseResultListModel.FieldEnabled5 = itemList.SdkFieldEnabled5; if (itemListPnCaseResultListModel.FieldEnabled5) { itemListPnCaseResultListModel.FieldName5 = allFields.SingleOrDefault(x => x.Id == itemList.SdkFieldId5)?.Label; } itemListPnCaseResultListModel.FieldEnabled6 = itemList.SdkFieldEnabled6; if (itemListPnCaseResultListModel.FieldEnabled6) { itemListPnCaseResultListModel.FieldName6 = allFields.SingleOrDefault(x => x.Id == itemList.SdkFieldId6)?.Label; } itemListPnCaseResultListModel.FieldEnabled7 = itemList.SdkFieldEnabled7; if (itemListPnCaseResultListModel.FieldEnabled7) { itemListPnCaseResultListModel.FieldName7 = allFields.SingleOrDefault(x => x.Id == itemList.SdkFieldId7)?.Label; } itemListPnCaseResultListModel.FieldEnabled8 = itemList.SdkFieldEnabled8; if (itemListPnCaseResultListModel.FieldEnabled8) { itemListPnCaseResultListModel.FieldName8 = allFields.SingleOrDefault(x => x.Id == itemList.SdkFieldId8)?.Label; } itemListPnCaseResultListModel.FieldEnabled9 = itemList.SdkFieldEnabled9; if (itemListPnCaseResultListModel.FieldEnabled9) { itemListPnCaseResultListModel.FieldName9 = allFields.SingleOrDefault(x => x.Id == itemList.SdkFieldId9)?.Label; } itemListPnCaseResultListModel.FieldEnabled10 = itemList.SdkFieldEnabled10; if (itemListPnCaseResultListModel.FieldEnabled10) { itemListPnCaseResultListModel.FieldName10 = allFields.SingleOrDefault(x => x.Id == itemList.SdkFieldId10)?.Label; } var newItems = (_dbContext.Items.Where(item => item.ItemListId == requestModel.ListId) .Join(_dbContext.ItemCases, item => item.Id, itemCase => itemCase.ItemId, (item, itemCase) => new { itemCase.Id, item.Name, item.Description, item.BuildYear, item.LocationCode, item.ItemNumber, item.Type, itemCase.MicrotingSdkCaseDoneAt, itemCase.MicrotingSdkCaseId, itemCase.Status, itemCase.CreatedAt, itemCase.DoneByUserName, itemCase.SdkFieldValue1, itemCase.SdkFieldValue2, itemCase.SdkFieldValue3, itemCase.SdkFieldValue4, itemCase.SdkFieldValue5, itemCase.SdkFieldValue6, itemCase.SdkFieldValue7, itemCase.SdkFieldValue8, itemCase.SdkFieldValue9, itemCase.SdkFieldValue10, itemCase.WorkflowState, itemCase.NumberOfImages })); if (requestModel.DateFrom != null) { newItems = newItems.Where(x => x.CreatedAt >= new DateTime(requestModel.DateFrom.Value.Year, requestModel.DateFrom.Value.Month, requestModel.DateFrom.Value.Day, 0, 0, 0)); } if (requestModel.DateTo != null) { newItems = newItems.Where(x => x.CreatedAt <= new DateTime(requestModel.DateTo.Value.Year, requestModel.DateTo.Value.Month, requestModel.DateTo.Value.Day, 23, 59, 59)); } if (!string.IsNullOrEmpty(requestModel.Sort)) { if (requestModel.IsSortDsc) { newItems = newItems .CustomOrderByDescending(requestModel.Sort); } else { newItems = newItems .CustomOrderBy(requestModel.Sort); } } else { newItems = newItems .OrderBy(x => x.Id); } itemListPnCaseResultListModel.Total = newItems.Count(x => x.WorkflowState != Constants.WorkflowStates.Removed); newItems = newItems .Where(x => x.WorkflowState != Constants.WorkflowStates.Removed) .Skip(requestModel.Offset) .Take(requestModel.PageSize); itemListPnCaseResultListModel.Items = new List <ItemsListPnCaseResultModel>(); foreach (var item in newItems.ToList()) { Console.WriteLine($"[DBG] ItemListCaseService.GetSingleListResults: Looking at case with id {item.Id} with status {item.Status}"); try { ItemsListPnCaseResultModel newItem = new ItemsListPnCaseResultModel() { Id = item.Id, DoneAt = item.MicrotingSdkCaseDoneAt, DeployedAt = item.CreatedAt, DoneByUserName = item.DoneByUserName, Label = item.Name, Description = item.Description, ItemNumber = item.ItemNumber, LocationCode = item.LocationCode, BuildYear = item.BuildYear, Type = item.Type, NumberOfImages = item.NumberOfImages, Field1 = item.SdkFieldValue1, Field2 = item.SdkFieldValue2, Field3 = item.SdkFieldValue3, Field4 = item.SdkFieldValue4, Field5 = item.SdkFieldValue5, Field6 = item.SdkFieldValue6, Field7 = item.SdkFieldValue7, Field8 = item.SdkFieldValue8, Field9 = item.SdkFieldValue9, Field10 = item.SdkFieldValue10, SdkCaseId = item.MicrotingSdkCaseId, SdkeFormId = itemList.RelatedEFormId, Status = item.Status, Token = pluginConfigurationValue.Value }; itemListPnCaseResultListModel.Items.Add(newItem); } catch (Exception ex) { Console.WriteLine(ex.Message); } } return(itemListPnCaseResultListModel); }
public async Task <OperationDataResult <ItemsListCasePnModel> > GetSingleList([FromBody] ItemListCasesPnRequestModel requestModel) { return(await _listService.GetSingleList(requestModel)); }