public async Task <OperationDataResult <ItemsPlanningBaseSettings> > GetSettings()
{
try
{
var option = _options.Value;
if (option.Token == "...")
{
const string chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
Random random = new Random();
string result = new string(chars.Select(c => chars[random.Next(chars.Length)]).Take(32).ToArray());
await _options.UpdateDb(settings => { settings.Token = result; }, _dbContext, UserId);
}
if (option.SdkConnectionString == "...")
{
string connectionString = _dbContext.Database.GetDbConnection().ConnectionString;
string dbNameSection = Regex.Match(connectionString, @"(Database=(...)_eform-angular-\w*-plugin;)").Groups[0].Value;
string dbPrefix = Regex.Match(connectionString, @"Database=(\d*)_").Groups[1].Value;
string sdk = $"Database={dbPrefix}_SDK;";
connectionString = connectionString.Replace(dbNameSection, sdk);
await _options.UpdateDb(settings => { settings.SdkConnectionString = connectionString; }, _dbContext, UserId);
}
return(new OperationDataResult <ItemsPlanningBaseSettings>(true, option));
}
catch (Exception e)
{
Trace.TraceError(e.Message);
_logger.LogError(e.Message);
return(new OperationDataResult <ItemsPlanningBaseSettings>(false,
_itemsPlanningLocalizationService.GetString("ErrorWhileObtainingItemsPlanningSettings")));
}
}
public async Task <OperationDataResult <List <CommonDictionaryModel> > > GetItemsPlanningTags()
{
try
{
var itemsPlanningTags = await _dbContext.PlanningTags
.Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
.AsNoTracking()
.Select(x => new CommonDictionaryModel
{
Id = x.Id,
Name = x.Name
}).OrderBy(x => x.Name).ToListAsync();
return(new OperationDataResult <List <CommonDictionaryModel> >(
true,
itemsPlanningTags));
}
catch (Exception e)
{
Console.WriteLine(e);
_logger.LogError(e.Message);
return(new OperationDataResult <List <CommonDictionaryModel> >(
false,
_itemsPlanningLocalizationService.GetString("ErrorWhileObtainingItemsPlanningTags")));
}
}
#pragma warning disable CS1998
public async Task <OperationDataResult <ItemsPlanningBaseSettings> > GetSettings()
#pragma warning restore CS1998
{
try
{
var option = _options.Value;
return(new OperationDataResult <ItemsPlanningBaseSettings>(true, option));
}
catch (Exception e)
{
Trace.TraceError(e.Message);
_logger.LogError(e.Message);
return(new OperationDataResult <ItemsPlanningBaseSettings>(false,
_itemsPlanningLocalizationService.GetString("ErrorWhileObtainingItemsPlanningSettings")));
}
}
public async Task <OperationDataResult <ReportModel> > GenerateReport(GenerateReportModel model)
{
try
{
var core = await _coreHelper.GetCore();
await using var dbContext = core.DbContextHelper.GetDbContext();
var itemList = await _dbContext.ItemLists.FirstAsync(x => x.Id == model.ItemList);
var item = await _dbContext.Items.FirstAsync(x => x.Id == model.Item);
var locale = await _userService.GetCurrentUserLocale();
Language language = dbContext.Languages.Single(x => x.LanguageCode.ToLower() == locale.ToLower());
var template = await core.ReadeForm(itemList.RelatedEFormId, language);
var casesQuery = _dbContext.ItemCases.Where(x => x.ItemId == item.Id);
if (model.DateFrom != null)
{
casesQuery = casesQuery.Where(x =>
x.CreatedAt >= new DateTime(model.DateFrom.Value.Year, model.DateFrom.Value.Month, model.DateFrom.Value.Day, 0, 0, 0));
}
if (model.DateTo != null)
{
casesQuery = casesQuery.Where(x =>
x.CreatedAt <= new DateTime(model.DateTo.Value.Year, model.DateTo.Value.Month, model.DateTo.Value.Day, 23, 59, 59));
}
var itemCases = await casesQuery.ToListAsync();
var reportModel = await GetReportData(model, item, itemCases, template);
return(new OperationDataResult <ReportModel>(true, reportModel));
}
catch (Exception e)
{
Trace.TraceError(e.Message);
_logger.LogError(e.Message);
return(new OperationDataResult <ReportModel>(false,
_itemsPlanningLocalizationService.GetString("ErrorWhileGeneratingReport")));
}
}
public async Task <OperationDataResult <UploadedDatasModel> > Index(int itemCaseId)
{
try
{
UploadedDatasModel uploadedDatasModel = new UploadedDatasModel();
IQueryable <UploadedData> uploadedDataQuery = _dbContext.UploadedDatas.AsQueryable();
uploadedDataQuery
= uploadedDataQuery
.Where(x => x.WorkflowState != Constants.WorkflowStates.Removed && x.ItemCaseId == itemCaseId);
List <UploadedDataModel> uploadedDatas = await uploadedDataQuery.Select(x => new UploadedDataModel()
{
Id = x.Id,
Checksum = x.Checksum,
CurrentFile = x.CurrentFile,
Extension = x.Extension,
FileLocation = x.FileLocation,
FileName = x.FileName,
ItemCaseId = x.ItemCaseId,
UploaderType = x.UploaderType
}).ToListAsync();
uploadedDatasModel.Total =
_dbContext.UploadedDatas.Count(x => x.WorkflowState != Constants.WorkflowStates.Removed);
uploadedDatasModel.UploadedDatas = uploadedDatas;
return(new OperationDataResult <UploadedDatasModel>(true, uploadedDatasModel));
}
catch (Exception e)
{
Trace.TraceError(e.Message);
_core.LogException(e.Message);
return(new OperationDataResult <UploadedDatasModel>(false,
_itemsPlanningLocalizationService.GetString("ErrorObtainingUploadedDatas")));
}
}
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")));
}
}
#pragma warning disable CS1998
public async Task <OperationDataResult <Stream> > GenerateExcelDashboard(List <ReportEformModel> reportModel)
#pragma warning restore CS1998
{
try
{
Directory.CreateDirectory(Path.Combine(Path.GetTempPath(), "results"));
var timeStamp = $"{DateTime.UtcNow:yyyyMMdd}_{DateTime.UtcNow:hhmmss}";
var resultDocument = Path.Combine(Path.GetTempPath(), "results",
$"{timeStamp}_.xlsx");
IXLWorkbook wb = new XLWorkbook();
foreach (var eformModel in reportModel)
{
if (eformModel.FromDate != null)
{
var x = 0;
var y = 0;
var sheetName = eformModel.TemplateName;
sheetName = sheetName
.Replace(":", "")
.Replace("\\", "")
.Replace("/", "")
.Replace("?", "")
.Replace("*", "")
.Replace("[", "")
.Replace("]", "");
if (sheetName.Length > 30)
{
sheetName = sheetName.Substring(0, 30);
}
var worksheet = wb.Worksheets.Add(sheetName);
if (eformModel.Items.Any())
{
worksheet.Cell(x + 1, y + 1).Value = _localizationService.GetString("Id");
worksheet.Cell(x + 1, y + 1).Style.Font.Bold = true;
y++;
worksheet.Cell(x + 1, y + 1).Value = _localizationService.GetString("CreatedAt");
worksheet.Cell(x + 1, y + 1).Style.Font.Bold = true;
y++;
worksheet.Cell(x + 1, y + 1).Value = _localizationService.GetString("DoneBy");
worksheet.Cell(x + 1, y + 1).Style.Font.Bold = true;
y++;
worksheet.Cell(x + 1, y + 1).Value = _localizationService.GetString("ItemName");
worksheet.Cell(x + 1, y + 1).Style.Font.Bold = true;
foreach (var itemHeader in eformModel.ItemHeaders)
{
y++;
worksheet.Cell(x + 1, y + 1).Value = itemHeader.Value;
worksheet.Cell(x + 1, y + 1).Style.Font.Bold = true;
}
}
x = 1;
foreach (var dataModel in eformModel.Items)
{
y = 0;
worksheet.Cell(x + 1, y + 1).Value = dataModel.MicrotingSdkCaseId;
y++;
worksheet.Cell(x + 1, y + 1).Value = $"{dataModel.MicrotingSdkCaseDoneAt:dd.MM.yyyy HH:mm:ss}";
y++;
worksheet.Cell(x + 1, y + 1).Value = dataModel.DoneBy;
y++;
worksheet.Cell(x + 1, y + 1).Value = dataModel.ItemName;
y++;
foreach (var dataModelCaseField in dataModel.CaseFields)
{
if (dataModelCaseField == "checked")
{
worksheet.Cell(x + 1, y + 1).Value = 1;
}
else
{
worksheet.Cell(x + 1, y + 1).Value = dataModelCaseField == "unchecked" ? "0" : dataModelCaseField == "checked" ? "1" : dataModelCaseField;
}
y++;
}
x++;
}
}
}
wb.SaveAs(resultDocument);
Stream result = File.Open(resultDocument, FileMode.Open);
return(new OperationDataResult <Stream>(true, result));
}
catch (Exception e)
{
Trace.TraceError(e.Message);
_logger.LogError(e.Message);
return(new OperationDataResult <Stream>(
false,
_localizationService.GetString("ErrorWhileCreatingWordFile")));
}
}
public bool WriteRecordsExportModelsToExcelFile(ReportModel reportModel, GenerateReportModel generateReportModel, 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 = reportModel.Name;
var descriptionTitle = _itemsPlanningLocalizationService.GetString("Description");
worksheet.Cell(3, 2).Value = descriptionTitle;
worksheet.Cell(3, 3).Value = reportModel.Description;
var periodFromTitle = _itemsPlanningLocalizationService.GetString("DateFrom");
worksheet.Cell(5, 2).Value = periodFromTitle;
worksheet.Cell(5, 3).Value = reportModel.DateFrom?.ToString("MM/dd/yyyy HH:mm");
var periodToTitle = _itemsPlanningLocalizationService.GetString("DateTo");
worksheet.Cell(6, 2).Value = periodToTitle;
worksheet.Cell(6, 3).Value = reportModel.DateTo?.ToString("MM/dd/yyyy HH:mm");
var col = 4;
var row = 8;
// Fill dates headers
var idName = _itemsPlanningLocalizationService.GetString("Id");
worksheet.Cell(8, 3).Value = idName;
foreach (var id in reportModel.Ids)
{
worksheet.Cell(row, col).Value = id;
worksheet.Cell(row, col).Style.Font.Bold = true;
worksheet.Cell(row, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
// TODO fix worksheet.Cell(row, col).AutoFitColumns();
col++;
}
row += 1;
col = 4;
var deployedAt = _itemsPlanningLocalizationService.GetString("Deployed at");
worksheet.Cell(9, 3).Value = deployedAt;
foreach (var date in reportModel.Dates)
{
worksheet.Cell(row, col).Value = date?.ToString("MM/dd/yyyy");
worksheet.Cell(row, col).Style.Font.Bold = true;
worksheet.Cell(row, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
// TODO fix worksheet.Cell(row, col).AutoFitColumns();
col++;
}
row += 1;
col = 4;
var doneAt = _itemsPlanningLocalizationService.GetString("Date of doing");
worksheet.Cell(10, 3).Value = doneAt;
foreach (var date in reportModel.DatesDoneAt)
{
worksheet.Cell(row, col).Value = date?.ToString("MM/dd/yyyy");
worksheet.Cell(row, col).Style.Font.Bold = true;
worksheet.Cell(row, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
// TODO fix worksheet.Cell(row, col).AutoFitColumns();
col++;
}
row += 1;
col = 4;
var doneBy = _itemsPlanningLocalizationService.GetString("Done by");
worksheet.Cell(11, 3).Value = doneBy;
foreach (var name in reportModel.DoneBy)
{
worksheet.Cell(row, col).Value = name;
worksheet.Cell(row, col).Style.Font.Bold = true;
worksheet.Cell(row, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
// TODO fix worksheet.Cell(row, col).AutoFitColumns();
col++;
}
col = 4;
worksheet.Range(row, 2, row, col - 1).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
row++;
// Fill form fields and options with data
foreach (var field in reportModel.FormFields)
{
worksheet.Cell(row, 2).Value = field.Label;
//worksheet.Cells(row, 2, row + field.Options.Count - 1, 2);
var fRow = row;
foreach (var option in field.Options)
{
col = 4;
foreach (var value in option.Values)
{
if (value?.GetType() == typeof(decimal))
{
worksheet.Cell(row, col).Style.NumberFormat.Format = "0.00";
}
worksheet.Cell(row, col).Value = value;
worksheet.Cell(row, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
col++;
}
worksheet.Cell(row, 3).Value = option.Label;
worksheet.Cell(row, 3).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
row++;
}
worksheet.Range(fRow, 2, row - 1, col - 1).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
}
workbook.Save();
return(true);
}
public async Task <OperationDataResult <Stream> > GenerateWordDashboard(List <ReportEformModel> reportModel)
{
try
{
// get core
var core = await _coreHelper.GetCore();
var headerImageName = _dbContext.PluginConfigurationValues.Single(x => x.Name == "ItemsPlanningBaseSettings:ReportImageName").Value;
_s3Enabled = core.GetSdkSetting(Settings.s3Enabled).Result.ToLower() == "true";
_swiftEnabled = core.GetSdkSetting(Settings.swiftEnabled).Result.ToLower() == "true";
// Read html and template
var resourceString = "ItemsPlanning.Pn.Resources.Templates.WordExport.page.html";
var assembly = Assembly.GetExecutingAssembly();
var resourceStream = assembly.GetManifestResourceStream(resourceString);
string html;
using (var reader = new StreamReader(resourceStream ?? throw new InvalidOperationException($"{nameof(resourceStream)} is null")))
{
html = await reader.ReadToEndAsync();
}
resourceString = "ItemsPlanning.Pn.Resources.Templates.WordExport.file.docx";
var docxFileResourceStream = assembly.GetManifestResourceStream(resourceString);
if (docxFileResourceStream == null)
{
throw new InvalidOperationException($"{nameof(docxFileResourceStream)} is null");
}
var docxFileStream = new MemoryStream();
await docxFileResourceStream.CopyToAsync(docxFileStream);
string basePicturePath = await core.GetSdkSetting(Settings.fileLocationPicture);
var word = new WordProcessor(docxFileStream);
var itemsHtml = new StringBuilder();;
var header = _dbContext.PluginConfigurationValues.Single(x => x.Name == "ItemsPlanningBaseSettings:ReportHeaderName").Value;
var subHeader = _dbContext.PluginConfigurationValues.Single(x => x.Name == "ItemsPlanningBaseSettings:ReportSubHeaderName").Value;
itemsHtml.Append("<body>");
itemsHtml.Append(@"<p style='display:flex;align-content:center;justify-content:center;flex-wrap:wrap;'>");
for (var i = 0; i < 8; i++)
{
itemsHtml.Append(@"<p style='font-size:24px;text-align:center;color:#fff;'>Enter</p>");
}
itemsHtml.Append($@"<p style='font-size:24px;text-align:center;'>{header}</p>");
itemsHtml.Append($@"<p style='font-size:20px;text-align:center;'>{subHeader}</p>");
itemsHtml.Append($@"<p style='font-size:15px;text-align:center;'>{_localizationService.GetString("ReportPeriod")}: {reportModel.First().FromDate} - {reportModel.First().ToDate}</p>");
// if (!string.IsNullOrEmpty(headerImageName) && headerImageName != "../../../assets/images/logo.png")
// {
// itemsHtml = await InsertImage(headerImageName, itemsHtml, 150, 150, core, basePicturePath);
// }
itemsHtml.Append(@"</p>");
// moving the cursor to the end of the page
for (var i = 0; i < 5; i++)
{
itemsHtml.Append(@"<p style='font-size:24px;text-align:center;color:#fff;'>Enter</p>");
}
// add tag names in end document
foreach (var nameTage in reportModel.Last().NameTagsInEndPage)
{
itemsHtml.Append($@"<p style='font-size:24px;text-align:center;'>{nameTage}</p>");
}
itemsHtml.Append(@"<div style='page-break-before:always;'>");
for (var i = 0; i < reportModel.Count; i++)
{
var reportEformModel = reportModel[i];
if (reportEformModel.TextHeaders != null)
{
if (!string.IsNullOrEmpty(reportEformModel.TextHeaders.Header1))
{
itemsHtml.Append($@"<h1>{Regex.Replace(reportEformModel.TextHeaders.Header1, @"\. ", ".")}</h1>");
// We do this, even thought some would look at it and find it looking stupid. But if we don't do it,
// Word WILL mess up the header titles, because it thinks it needs to fix the number order.
}
if (!string.IsNullOrEmpty(reportEformModel.TextHeaders.Header2))
{
itemsHtml.Append($@"<h2>{reportEformModel.TextHeaders.Header2}</h2>");
}
if (!string.IsNullOrEmpty(reportEformModel.TextHeaders.Header3))
{
itemsHtml.Append($@"<h3>{reportEformModel.TextHeaders.Header3}</h3>");
}
if (!string.IsNullOrEmpty(reportEformModel.TextHeaders.Header4))
{
itemsHtml.Append($@"<h4>{reportEformModel.TextHeaders.Header4}</h4>");
}
if (!string.IsNullOrEmpty(reportEformModel.TextHeaders.Header5))
{
itemsHtml.Append($@"<h5>{reportEformModel.TextHeaders.Header5}</h5>");
}
}
foreach (var description in reportEformModel.DescriptionBlocks)
{
itemsHtml.Append($@"<p style='font-size: 7pt;'>{description}</p>");
}
// if (!string.IsNullOrEmpty(reportEformModel.TableName))
// {
// itemsHtml.Append($@"<p style='padding-bottom: 0;'>{_localizationService.GetString("Table")}: {reportEformModel.TableName}</p>");
// }
if (reportEformModel.Items.Any())
{
itemsHtml.Append(@"<table width=""100%"" border=""1"">"); // TODO change font-size 7
// Table header
itemsHtml.Append(@"<tr style='background-color:#f5f5f5;font-weight:bold;font-size: 7pt;'>");
itemsHtml.Append($@"<td>{_localizationService.GetString("Id")}</td>");
itemsHtml.Append($@"<td>{_localizationService.GetString("CreatedAt")}</td>");
itemsHtml.Append($@"<td>{_localizationService.GetString("DoneBy")}</td>");
itemsHtml.Append($@"<td>{_localizationService.GetString("ItemName")}</td>");
foreach (var itemHeader in reportEformModel.ItemHeaders)
{
itemsHtml.Append($@"<td>{itemHeader.Value}</td>");
}
// itemsHtml += $@"<td>{_localizationService.GetString("Pictures")}</td>";
// itemsHtml += $@"<td>{_localizationService.GetString("Posts")}</td>";
itemsHtml.Append(@"</tr>");
foreach (var dataModel in reportEformModel.Items)
{
itemsHtml.Append(@"<tr style='font-size: 7pt;'>");
itemsHtml.Append($@"<td>{dataModel.MicrotingSdkCaseId}</td>");
itemsHtml.Append($@"<td>{dataModel.MicrotingSdkCaseDoneAt:dd.MM.yyyy}</td>");
itemsHtml.Append($@"<td>{dataModel.DoneBy}</td>");
itemsHtml.Append($@"<td>{dataModel.ItemName}</td>");
foreach (var dataModelCaseField in dataModel.CaseFields)
{
if (dataModelCaseField == "checked")
{
itemsHtml.Append($@"<td>✔</td>");
}
else
{
if (dataModelCaseField == "unchecked")
{
itemsHtml.Append($@"<td></td>");
}
else
{
itemsHtml.Append($@"<td>{dataModelCaseField}</td>");
}
}
}
// itemsHtml += $@"<td>{dataModel.ImagesCount}</td>";
// itemsHtml += $@"<td>{dataModel.PostsCount}</td>";
itemsHtml.Append(@"</tr>");
}
itemsHtml.Append(@"</table>");
}
itemsHtml.Append(@"<br/>");
// if (!string.IsNullOrEmpty(reportEformModel.TemplateName))
// {
// itemsHtml.Append($@"{reportEformModel.TemplateName}");
// }
foreach (var imagesName in reportEformModel.ImageNames)
{
itemsHtml.Append($@"<p style='font-size: 7pt; page-break-before:always'>{_localizationService.GetString("Id")}: {imagesName.Key[1]}</p>"); // TODO change to ID: {id}; imagesName.Key[1]
itemsHtml = await InsertImage(imagesName.Value[0], itemsHtml, 700, 650, core, basePicturePath);
if (!string.IsNullOrEmpty(imagesName.Value[1]))
{
itemsHtml.Append($@"<p style='font-size: 7pt;'>{_localizationService.GetString("Position")}:<a href=""{imagesName.Value[1]}"">{imagesName.Value[1]}</a></p>"); // TODO change to Position : URL
}
}
// itemsHtml += $@"<h2><b>{reportEformModel.Name} {_localizationService.GetString("posts")}</b></h2>";
// itemsHtml += @"<table width=""100%"" border=""1"">";
//
// // Table header
// itemsHtml += @"<tr style=""background-color:#f5f5f5;font-weight:bold"">";
// // itemsHtml += $@"<td>{_localizationService.GetString("Id")}</td>";
// itemsHtml += $@"<td>{_localizationService.GetString("CaseId")}</td>";
// itemsHtml += $@"<td>{_localizationService.GetString("PostDate")}</td>";
// itemsHtml += $@"<td>{_localizationService.GetString("SentTo")}</td>";
// itemsHtml += $@"<td>{_localizationService.GetString("Comment")}</td>";
// itemsHtml += @"</tr>";
//
// foreach (var dataModel in reportEformModel.Posts)
// {
// itemsHtml += @"<tr>";
// // itemsHtml += $@"<td>{dataModel.PostId}</td>";
// itemsHtml += $@"<td>{dataModel.CaseId}</td>";
// itemsHtml += $@"<td>{dataModel.PostDate:dd.MM.yyyy HH:mm:ss}</td>";
// itemsHtml += $@"<td>{dataModel.SentTo.Join()} {dataModel.SentToTags.Join()}</td>";
// itemsHtml += $@"<td>{dataModel.Comment}</td>";
// itemsHtml += @"</tr>";
// }
// itemsHtml += @"</table>";
}
itemsHtml.Append(@"</div>");
itemsHtml.Append("</body>");
html = html.Replace("{%ItemList%}", itemsHtml.ToString());
word.AddHtml(html);
word.Dispose();
docxFileStream.Position = 0;
return(new OperationDataResult <Stream>(true, docxFileStream));
}
catch (Exception e)
{
Trace.TraceError(e.Message);
_logger.LogError(e.Message);
return(new OperationDataResult <Stream>(
false,
_localizationService.GetString("ErrorWhileCreatingWordFile")));
}
}
public async Task <OperationDataResult <ItemsListsModel> > Index(ItemsListRequestModel pnRequestModel)
{
try
{
ItemsListsModel listsModel = new ItemsListsModel();
IQueryable <ItemList> itemListsQuery = _dbContext.ItemLists.AsQueryable();
if (!string.IsNullOrEmpty(pnRequestModel.Sort))
{
if (pnRequestModel.IsSortDsc)
{
itemListsQuery = itemListsQuery
.CustomOrderByDescending(pnRequestModel.Sort);
}
else
{
itemListsQuery = itemListsQuery
.CustomOrderBy(pnRequestModel.Sort);
}
}
else
{
itemListsQuery = _dbContext.ItemLists
.OrderBy(x => x.Id);
}
if (!string.IsNullOrEmpty(pnRequestModel.NameFilter))
{
itemListsQuery = itemListsQuery.Where(x => x.Name.Contains(pnRequestModel.NameFilter));
}
itemListsQuery
= itemListsQuery
.Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
.Skip(pnRequestModel.Offset)
.Take(pnRequestModel.PageSize);
List <ItemsListPnModel> lists = await itemListsQuery.Select(x => new ItemsListPnModel()
{
Id = x.Id,
Name = x.Name,
Description = x.Description,
RepeatEvery = x.RepeatEvery,
RepeatType = x.RepeatType,
RepeatUntil = x.RepeatUntil,
DayOfWeek = x.DayOfWeek,
DayOfMonth = x.DayOfMonth,
RelatedEFormId = x.RelatedEFormId,
RelatedEFormName = x.RelatedEFormName,
}).ToListAsync();
listsModel.Total = await _dbContext.ItemLists.CountAsync(x =>
x.WorkflowState != Constants.WorkflowStates.Removed);
listsModel.Lists = lists;
return(new OperationDataResult <ItemsListsModel>(true, listsModel));
}
catch (Exception e)
{
Trace.TraceError(e.Message);
return(new OperationDataResult <ItemsListsModel>(false,
_itemsPlanningLocalizationService.GetString("ErrorObtainingLists")));
}
}
public async Task <OperationDataResult <PairingsModel> > GetAllPairings(PairingRequestModel pairingRequestModel)
{
try
{
var sdkCore = await _coreService.GetCore();
await using var sdkDbContext = sdkCore.DbContextHelper.GetDbContext();
var sitesQuery = sdkDbContext.Sites
.Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
.AsNoTracking();
if (pairingRequestModel.SiteIds.Any())
{
sitesQuery = sitesQuery.Where(x => pairingRequestModel.SiteIds.Contains(x.Id));
}
var deviceUsers = await sitesQuery.Select(x => new CommonDictionaryModel
{
Id = x.Id,
Name = x.Name,
}).ToListAsync();
var pairingQuery = _dbContext.Plannings
.Where(x => x.SdkFolderId != null)
.Where(x => x.SdkFolderId != 0)
.Where(x => x.IsLocked == false)
.Where(x => x.IsEditable == true)
.Where(x => x.IsHidden == false)
.AsQueryable();
if (pairingRequestModel.TagIds.Any())
{
foreach (var tagId in pairingRequestModel.TagIds)
{
pairingQuery = pairingQuery.Where(x => x.PlanningsTags.Any(y =>
y.PlanningTagId == tagId && y.WorkflowState != Constants.WorkflowStates.Removed));
}
}
var language = await _userService.GetCurrentUserLanguage();
var pairing = await pairingQuery
.Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
.Select(x => new PairingModel
{
PlanningId = x.Id,
PlanningName = x.NameTranslations
.Where(y => y.WorkflowState != Constants.WorkflowStates.Removed)
.Where(y => y.Language.Id == language.Id)
.Select(y => y.Name)
.FirstOrDefault(),
PairingValues = x.PlanningSites
.Where(y => y.WorkflowState != Constants.WorkflowStates.Removed)
.Select(y => new PairingValueModel
{
DeviceUserId = y.SiteId,
Paired = true
}).ToList(),
}).ToListAsync();
// Add users who is not paired
foreach (var pairingModel in pairing)
{
// get status last executed case for planning
foreach (var pairingModelPairingValue in pairingModel.PairingValues)
{
var planningCaseSite = await _dbContext.PlanningCaseSites
.CustomOrderByDescending("UpdatedAt")
.Where(z => z.PlanningId == pairingModel.PlanningId)
.Where(z => z.WorkflowState != Constants.WorkflowStates.Removed)
.Where(z => z.MicrotingSdkSiteId == pairingModelPairingValue.DeviceUserId)
.FirstOrDefaultAsync();
//if (pairingModelPairingValue.DeviceUserId == planningCaseSite.MicrotingSdkSiteId &&
// pairingModelPairingValue.Paired)
//{
if (planningCaseSite != null)
{
pairingModelPairingValue.LatestCaseStatus = planningCaseSite.Status;
pairingModelPairingValue.PlanningCaseSiteId = planningCaseSite.Id;
}
//}
}
// Add users
foreach (var deviceUser in deviceUsers)
{
if (deviceUser.Id != null && pairingModel.PairingValues.All(x => x.DeviceUserId != deviceUser.Id))
{
var pairingValue = new PairingValueModel
{
DeviceUserId = (int)deviceUser.Id,
Paired = false,
};
pairingModel.PairingValues.Add(pairingValue);
}
}
pairingModel.PairingValues = pairingModel.PairingValues
.OrderBy(x => x.DeviceUserId)
.ToList();
}
// Build result
var result = new PairingsModel()
{
DeviceUsers = deviceUsers.Select(x => x.Name).ToList(),
Pairings = pairing,
};
return(new OperationDataResult <PairingsModel>(
true,
result));
}
catch (Exception e)
{
Trace.TraceError(e.Message);
return(new OperationDataResult <PairingsModel>(false,
_itemsPlanningLocalizationService.GetString("ErrorWhileObtainingPlanningPairings")));
}
}
public async Task <OperationResult> Update(ReplyRequest model)
{
var checkListValueList = new List <string>();
var fieldValueList = new List <string>();
var core = await _coreHelper.GetCore();
var language = await _userService.GetCurrentUserLanguage();
var currentUser = await _userService.GetCurrentUserAsync();
try
{
model.ElementList.ForEach(element =>
{
checkListValueList.AddRange(CaseUpdateHelper.GetCheckList(element));
fieldValueList.AddRange(CaseUpdateHelper.GetFieldList(element));
});
}
catch (Exception ex)
{
Log.LogException(ex.Message);
Log.LogException(ex.StackTrace);
return(new OperationResult(false, $"{_localizationService.GetString("CaseCouldNotBeUpdated")} Exception: {ex.Message}"));
}
try
{
await core.CaseUpdate(model.Id, fieldValueList, checkListValueList);
await core.CaseUpdateFieldValues(model.Id, language);
var sdkDbContext = core.DbContextHelper.GetDbContext();
var foundCase = await sdkDbContext.Cases
.Where(x => x.Id == model.Id &&
x.WorkflowState != Constants.WorkflowStates.Removed)
.FirstOrDefaultAsync();
if (foundCase != null)
{
if (foundCase.DoneAt != null)
{
var newDoneAt = new DateTime(model.DoneAt.Year, model.DoneAt.Month, model.DoneAt.Day, foundCase.DoneAt.Value.Hour, foundCase.DoneAt.Value.Minute, foundCase.DoneAt.Value.Second);
foundCase.DoneAtUserModifiable = newDoneAt;
}
// foundCase.SiteId = sdkDbContext.Sites
// .Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
// .Single(x => x.Name == $"{currentUser.FirstName} {currentUser.LastName}").Id;
foundCase.Status = 100;
await foundCase.Update(sdkDbContext);
var planningCase = await _dbContext.PlanningCases.SingleAsync(x => x.MicrotingSdkCaseId == model.Id);
var planningCaseSite = await _dbContext.PlanningCaseSites.SingleOrDefaultAsync(x => x.MicrotingSdkCaseId == model.Id && x.PlanningCaseId == planningCase.Id);
if (planningCaseSite == null)
{
planningCaseSite = new PlanningCaseSite()
{
MicrotingSdkCaseId = model.Id,
PlanningCaseId = planningCase.Id,
MicrotingSdkeFormId = planningCase.MicrotingSdkeFormId,
PlanningId = planningCase.PlanningId,
Status = 100,
MicrotingSdkSiteId = (int)foundCase.SiteId
};
await planningCaseSite.Create(_dbContext);
}
planningCaseSite.MicrotingSdkCaseDoneAt = foundCase.DoneAtUserModifiable;
planningCaseSite = await SetFieldValue(planningCaseSite, foundCase.Id, language);
await planningCaseSite.Update(_dbContext);
planningCase.MicrotingSdkCaseDoneAt = foundCase.DoneAtUserModifiable;
planningCase = await SetFieldValue(planningCase, foundCase.Id, language);
await planningCase.Update(_dbContext);
}
else
{
return(new OperationResult(false, _localizationService.GetString("CaseNotFound")));
}
return(new OperationResult(true, _localizationService.GetString("CaseHasBeenUpdated")));
}
catch (Exception ex)
{
Log.LogException(ex.Message);
Log.LogException(ex.StackTrace);
return(new OperationResult(false, _localizationService.GetString("CaseCouldNotBeUpdated") + $" Exception: {ex.Message}"));
}
}
public async Task <OperationDataResult <ExcelParseResult> > ImportPlannings(Stream excelStream)
{
try
{
var result = new ExcelParseResult();
var core = await _coreService.GetCore();
await using var microtingDbContext = core.DbContextHelper.GetDbContext();
var timeZone = await _userService.GetCurrentUserTimeZoneInfo();
await using var dbContext = core.DbContextHelper.GetDbContext();
var theLanguage = await dbContext.Languages
.SingleAsync(x => x.LanguageCode == "da");
var templatesDto = await core.TemplateItemReadAll(
false,
"",
"",
false,
"",
new List <int>(),
timeZone, theLanguage);
// Parse excel file
var fileResult = _planningExcelService.ParsePlanningImportFile(excelStream);
// Get planning names list
var planningNames = await _dbContext.PlanningNameTranslation
.AsNoTracking()
.Where(x => x.WorkflowState != Constants.WorkflowStates.Removed &&
x.LanguageId == theLanguage.Id)
.Select(x => x.Name)
.ToListAsync();
// Validation
var excelErrors = new List <ExcelParseErrorModel>();
List <string> languageCodes = new List <string>()
{
"da",
"en-US",
"de-DE"
};
foreach (var excelModel in fileResult)
{
if (string.IsNullOrEmpty(excelModel.EFormName))
{
var error = new ExcelParseErrorModel
{
Col = PlanningImportExcelConsts.EformNameCol,
Row = excelModel.ExcelRow,
Message = _itemsPlanningLocalizationService.GetString(
"EformNameIsEmpty")
};
excelErrors.Add(error);
}
if (string.IsNullOrEmpty(excelModel.PlanningName))
{
var error = new ExcelParseErrorModel
{
Col = PlanningImportExcelConsts.PlanningItemNameCol,
Row = excelModel.ExcelRow,
Message = _itemsPlanningLocalizationService.GetString(
"ItemNameIsEmpty")
};
excelErrors.Add(error);
}
if (!excelModel.Folders.Any())
{
var error = new ExcelParseErrorModel
{
Row = excelModel.ExcelRow,
Message = _itemsPlanningLocalizationService.GetString(
"FolderNotFound")
};
excelErrors.Add(error);
}
//if (!string.IsNullOrEmpty(planningName))
//{
// var error = new ExcelParseErrorModel
// {
// Col = PlanningImportExcelConsts.PlanningItemNameCol,
// Row = excelModel.ExcelRow,
// Message = _itemsPlanningLocalizationService.GetString(
// "PlanningWithNameAlreadyExists",
// excelModel.PlanningName)
// };
// excelErrors.Add(error);
//}
var templateByName = templatesDto
.FirstOrDefault(x =>
x.Label ==
excelModel.EFormName);
if (templateByName == null)
{
var error = new ExcelParseErrorModel
{
Col = PlanningImportExcelConsts.EformNameCol,
Row = excelModel.ExcelRow,
Message = _itemsPlanningLocalizationService.GetString(
"EformNotFound")
};
excelErrors.Add(error);
}
else
{
excelModel.EFormId = templateByName.Id;
}
}
result.Errors = excelErrors;
if (excelErrors.Any())
{
return(new OperationDataResult <ExcelParseResult>(
true,
result));
}
// Process plannings
//using (var transaction = await _dbContext.Database.BeginTransactionAsync())
//{
try
{
// Process planning tags
var tags = await _dbContext.PlanningTags
//.AsNoTracking()
.Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
.Select(x => new
{
x.Id,
Name = x.Name,
}).ToListAsync();
// Trim tag names
foreach (var excelModel in fileResult)
{
for (var y = 0; y < excelModel.Tags.Count; y++)
{
excelModel.Tags[y] = excelModel.Tags[y].Trim();
}
}
var fileTags = fileResult.SelectMany(x => x.Tags)
.GroupBy(x => x)
.Select(x => x.Key)
.ToList();
foreach (var fileTag in fileTags)
{
var planningTagExist = tags.FirstOrDefault(x =>
x.Name ==
fileTag);
if (planningTagExist == null)
{
var planningTag = new PlanningTag
{
Name = fileTag,
CreatedByUserId = _userService.UserId,
UpdatedByUserId = _userService.UserId,
};
await planningTag.Create(_dbContext);
}
}
tags = await _dbContext.PlanningTags
.AsNoTracking()
.Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
.Select(x => new
{
x.Id,
Name = x.Name,
}).ToListAsync();
// Folders
var danishLanguage = await dbContext.Languages.SingleOrDefaultAsync(x => x.LanguageCode == "da");
var folders = await dbContext.Folders
.Join(dbContext.FolderTranslations,
folder => folder.Id,
translation => translation.FolderId,
(folder, translation) => new
{
folderWorkflowState = folder.WorkflowState,
translationWorkflowState = translation.WorkflowState,
folder.Id,
translation.Name,
translation.Description,
folder.ParentId,
translation.LanguageId
})
.AsNoTracking()
.Where(x => x.LanguageId == danishLanguage.Id)
.Where(x => x.folderWorkflowState != Constants.WorkflowStates.Removed)
.Select(x => new PlanningImportFolderModel
{
Id = x.Id,
Label = x.Name,
Description = x.Description,
ParentId = x.ParentId,
}).ToListAsync();
// Process folders
foreach (var excelModel in fileResult)
{
for (var i = 0; i < excelModel.Folders.Count; i++)
{
var level = i + 1;
var folderModel = excelModel.Folders[i];
if (level == 1)
{
var mainFolder = folders.FirstOrDefault(x =>
x.Label ==
folderModel.Label.Split("|")[0] &&
x.ParentId == null);
if (mainFolder == null)
{
List <KeyValuePair <string, string> > names = new List <KeyValuePair <string, string> >();
List <KeyValuePair <string, string> > descriptions = new List <KeyValuePair <string, string> >();
var sourceNames = folderModel.Label.Split("|");
var sourceDescriptions = folderModel.Description.Split("|");
names.Add(new KeyValuePair <string, string>("da", sourceNames[0]));
descriptions.Add(new KeyValuePair <string, string>("da", sourceDescriptions[0]));
if (sourceNames.Length > 1)
{
names.Add(new KeyValuePair <string, string>("en-US", sourceNames[1]));
}
else
{
names.Add(new KeyValuePair <string, string>("en-US", ""));
}
if (sourceNames.Length > 2)
{
names.Add(new KeyValuePair <string, string>("de-DE", sourceNames[2]));
}
else
{
names.Add(new KeyValuePair <string, string>("de-DE", ""));
}
if (sourceDescriptions.Length > 1)
{
descriptions.Add(new KeyValuePair <string, string>("en-US", sourceDescriptions[1]));
}
else
{
descriptions.Add(new KeyValuePair <string, string>("en-US", ""));
}
if (sourceNames.Length > 2)
{
descriptions.Add(new KeyValuePair <string, string>("de-DE", sourceDescriptions[2]));
}
else
{
descriptions.Add(new KeyValuePair <string, string>("de-DE", ""));
}
folderModel.Id = await core.FolderCreate(
names,
descriptions,
null);
}
else
{
folderModel.Id = mainFolder.Id;
}
folderModel.Description = folderModel.Description.Split("|")[0];
folderModel.Label = folderModel.Label.Split("|")[0];
folders.Add(folderModel);
}
if (level > 1)
{
var parentId = excelModel.Folders[i - 1].Id;
var sdkFolder = folders.FirstOrDefault(x =>
x.Label ==
folderModel.Label.Split("|")[0] &&
x.ParentId == parentId);
if (sdkFolder == null)
{
List <KeyValuePair <string, string> > names = new List <KeyValuePair <string, string> >();
List <KeyValuePair <string, string> > descriptions = new List <KeyValuePair <string, string> >();
var sourceNames = folderModel.Label.Split("|");
var sourceDescriptions = folderModel.Description.Split("|");
names.Add(new KeyValuePair <string, string>("da", sourceNames[0]));
descriptions.Add(new KeyValuePair <string, string>("da", sourceDescriptions[0]));
if (sourceNames.Length > 1)
{
names.Add(new KeyValuePair <string, string>("en-US", sourceNames[1]));
}
else
{
names.Add(new KeyValuePair <string, string>("en-US", ""));
}
if (sourceNames.Length > 2)
{
names.Add(new KeyValuePair <string, string>("de-DE", sourceNames[2]));
}
else
{
names.Add(new KeyValuePair <string, string>("de-DE", ""));
}
if (sourceDescriptions.Length > 1)
{
descriptions.Add(new KeyValuePair <string, string>("en-US", sourceDescriptions[1]));
}
else
{
descriptions.Add(new KeyValuePair <string, string>("en-US", ""));
}
if (sourceNames.Length > 2)
{
descriptions.Add(new KeyValuePair <string, string>("de-DE", sourceDescriptions[2]));
}
else
{
descriptions.Add(new KeyValuePair <string, string>("de-DE", ""));
}
folderModel.Id = await core.FolderCreate(
names,
descriptions,
parentId);
}
else
{
folderModel.Id = sdkFolder.Id;
}
folderModel.ParentId = parentId;
folderModel.Description = folderModel.Description.Split("|")[0];
folderModel.Label = folderModel.Label.Split("|")[0];
folders.Add(folderModel);
}
}
}
// Process plannings
foreach (var excelModel in fileResult)
{
var tagIds = new List <int>();
if (excelModel.Tags.Any())
{
foreach (var tagName in excelModel.Tags)
{
var planningTagExist = tags.FirstOrDefault(x => x.Name == tagName);
if (planningTagExist != null)
{
tagIds.Add(planningTagExist.Id);
}
}
}
var planningNameFromExcelModel = excelModel.PlanningName.Split("|").First();
var sdkFolder = excelModel.Folders.Last();
// Find planning name
var planningName = _dbContext.PlanningNameTranslation
.Join(_dbContext.Plannings,
planningNameTranslation => planningNameTranslation.PlanningId,
plannings => plannings.Id,
(planningNameTranslation, planning) => new
{
planning.SdkFolderId,
planningNameTranslation.Name,
planningNameTranslation.PlanningId,
planning.WorkflowState
}).FirstOrDefault(x =>
x.Name == planningNameFromExcelModel &&
x.SdkFolderId == sdkFolder.Id &&
x.WorkflowState != Constants.WorkflowStates.Removed);
if (planningName != null)
{
var planningFromDb = await _dbContext.Plannings
.Where(x => x.Id == planningName.PlanningId)
.FirstAsync();
var planningTranslations = _dbContext.PlanningNameTranslation
.Where(x => x.PlanningId == planningFromDb.Id)
.ToList();
// create or update name tranlations planning
var i = 1;
foreach (var translationText in excelModel.PlanningName.Split("|"))
{
var language = await dbContext.Languages.SingleAsync(x => x.Id == i);
var planningNameTranslation =
planningTranslations
.Where(x => x.PlanningId == planningFromDb.Id)
.FirstOrDefault(x => x.LanguageId == i);
if (planningNameTranslation == null)
{
planningNameTranslation = new PlanningNameTranslation
{
Name = translationText,
LanguageId = language.Id,
PlanningId = planningFromDb.Id,
CreatedByUserId = _userService.UserId,
UpdatedByUserId = _userService.UserId,
};
await planningNameTranslation.Create(_dbContext);
}
else
{
planningNameTranslation.UpdatedByUserId = _userService.UserId;
planningNameTranslation.WorkflowState = Constants.WorkflowStates.Created;
await planningNameTranslation.Update(_dbContext);
}
i++;
}
if (excelModel.DayOfMonth != null && planningFromDb.DayOfMonth != excelModel.DayOfMonth)
{
planningFromDb.DayOfMonth = excelModel.DayOfMonth;
}
if (excelModel.DayOfWeek != null && planningFromDb.DayOfWeek != excelModel.DayOfWeek)
{
planningFromDb.DayOfWeek = excelModel.DayOfWeek;
}
if (excelModel.RepeatEvery != null && planningFromDb.RepeatEvery != excelModel.RepeatEvery)
{
planningFromDb.RepeatEvery = (int)excelModel.RepeatEvery;
}
if (excelModel.RepeatType != null && planningFromDb.RepeatType != excelModel.RepeatType)
{
planningFromDb.RepeatType = (RepeatType)excelModel.RepeatType;
}
if (excelModel.RepeatUntil != null && planningFromDb.RepeatUntil != excelModel.RepeatUntil)
{
planningFromDb.RepeatUntil = excelModel.RepeatUntil;
}
planningFromDb.UpdatedByUserId = _userService.UserId;
planningFromDb.StartDate = DateTime.Now;
planningFromDb.WorkflowState = Constants.WorkflowStates.Created;
var tagsIdForAddToPlanningTags = planningFromDb.PlanningsTags
.Where(x => tagIds.Any(y => x.PlanningTagId != y)).ToList();
var tagsIdForRemoveInPlanningTags = tagIds
.Where(x => planningFromDb.PlanningsTags.Any(y => x != y.PlanningTagId)).ToList();
if (tagsIdForAddToPlanningTags.Any())
{
foreach (var tagsIdForAddToPlanningTag in tagsIdForAddToPlanningTags)
{
planningFromDb.PlanningsTags.Add(
new PlanningsTags
{
CreatedByUserId = _userService.UserId,
UpdatedByUserId = _userService.UserId,
PlanningTagId = tagsIdForAddToPlanningTag.PlanningTagId
});
}
}
if (tagsIdForRemoveInPlanningTags.Any())
{
foreach (var tagsIdForAddToPlanningTag in tagsIdForAddToPlanningTags)
{
await planningFromDb.PlanningsTags
.Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
.FirstOrDefault(x => x.PlanningTagId == tagsIdForAddToPlanningTag.PlanningTagId)
.Delete(_dbContext);
}
}
await planningFromDb.Update(_dbContext);
}
else
{
sdkFolder = excelModel.Folders.Last();
var newPlanning = new Planning
{
CreatedByUserId = _userService.UserId,
RepeatUntil = excelModel.RepeatUntil,
DayOfWeek = excelModel.DayOfWeek,
DayOfMonth = excelModel.DayOfMonth,
Enabled = true,
RelatedEFormId = (int)excelModel.EFormId,
RelatedEFormName = excelModel.EFormName,
PlanningsTags = new List <PlanningsTags>(),
SdkFolderName = sdkFolder?.Label,
SdkFolderId = sdkFolder?.Id,
StartDate = DateTime.UtcNow,
RepeatEvery = excelModel.RepeatEvery ?? 1,
RepeatType = excelModel.RepeatType ?? RepeatType.Month,
IsLocked = false,
IsEditable = true,
IsHidden = false
};
foreach (var tagId in tagIds)
{
newPlanning.PlanningsTags.Add(
new PlanningsTags
{
CreatedAt = DateTime.UtcNow,
CreatedByUserId = _userService.UserId,
UpdatedAt = DateTime.UtcNow,
UpdatedByUserId = _userService.UserId,
Version = 1,
PlanningTagId = tagId
});
}
await newPlanning.Create(_dbContext);
var i = 1;
foreach (var translationText in excelModel.PlanningName.Split("|"))
{
var language = await dbContext.Languages.SingleAsync(x => x.Id == i);
var planningNameTranslation = new PlanningNameTranslation()
{
Name = translationText,
LanguageId = language.Id,
PlanningId = newPlanning.Id
};
await planningNameTranslation.Create(_dbContext);
i += 1;
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
result.Message = _itemsPlanningLocalizationService.GetString("ImportCompletedSuccessfully");
return(new OperationDataResult <ExcelParseResult>(
true,
result));
}
catch (Exception e)
{
Trace.TraceError(e.Message);
_logger.LogError(e, e.Message);
return(new OperationDataResult <ExcelParseResult>(false,
_itemsPlanningLocalizationService.GetString("ErrorWhileImportingExcelFile")));
}
}
public async Task <OperationDataResult <List <ReportEformModel> > > GenerateReport(GenerateReportModel model, bool isDocx)
{
try
{
var timeZoneInfo = await _userService.GetCurrentUserTimeZoneInfo();
var core = await _coreHelper.GetCore();
await using var sdkDbContext = core.DbContextHelper.GetDbContext();
//var casesQuery = microtingDbContext.cases
// .Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
// .Include(x => x.Site)
// .AsQueryable();
var fromDate = new DateTime(model.DateFrom.Value.Year, model.DateFrom.Value.Month,
model.DateFrom.Value.Day, 0, 0, 0);
var toDate = new DateTime(model.DateTo.Value.Year, model.DateTo.Value.Month,
model.DateTo.Value.Day, 23, 59, 59);
var planningCasesQuery = _dbContext.PlanningCases
.Include(x => x.Planning)
.ThenInclude(x => x.PlanningsTags)
.Where(x => x.Status == 100)
.Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
.AsNoTracking()
.AsQueryable();
if (model.DateFrom != null)
{
planningCasesQuery = planningCasesQuery.Where(x =>
x.MicrotingSdkCaseDoneAt >= fromDate);
}
if (model.DateTo != null)
{
planningCasesQuery = planningCasesQuery.Where(x =>
x.MicrotingSdkCaseDoneAt <= toDate);
}
if (model.TagIds.Count > 0)
{
foreach (var tagId in model.TagIds)
{
planningCasesQuery = planningCasesQuery.Where(x =>
x.Planning.PlanningsTags.Any(y => y.PlanningTagId == tagId && y.WorkflowState != Constants.WorkflowStates.Removed))
.AsNoTracking();
}
}
var groupedCaseCheckListIds = planningCasesQuery.GroupBy(x => x.MicrotingSdkeFormId)
.Select(x => x.Key)
.ToList();
List <CheckList> checkLists = new List <CheckList>();
if (groupedCaseCheckListIds.Count > 0)
{
checkLists = await sdkDbContext.CheckLists
.FromSqlRaw("SELECT * FROM CheckLists WHERE" +
$" Id IN ({string.Join(",", groupedCaseCheckListIds)})" +
" ORDER BY ReportH1, ReportH2, ReportH3, ReportH4").AsNoTracking().ToListAsync();
}
var itemCases = await planningCasesQuery
.OrderBy(x => x.Planning.RelatedEFormName)
.ToListAsync();
var groupedCases = itemCases
.GroupBy(x => x.MicrotingSdkeFormId)
.Select(x => new
{
templateId = x.Key,
cases = x.ToList(),
})
.ToList();
var result = new List <ReportEformModel>();
// Exclude field types: None, Picture, Audio, Movie, Signature, Show PDF, FieldGroup, SaveButton
var excludedFieldTypes = new List <string>()
{
Constants.FieldTypes.None,
Constants.FieldTypes.Picture,
Constants.FieldTypes.Audio,
Constants.FieldTypes.Movie,
Constants.FieldTypes.Signature,
Constants.FieldTypes.ShowPdf,
Constants.FieldTypes.FieldGroup,
Constants.FieldTypes.SaveButton
};
var localeString = await _userService.GetCurrentUserLocale();
var language = sdkDbContext.Languages.Single(x => x.LanguageCode == localeString);
//foreach (var groupedCase in groupedCases)
foreach (var checkList in checkLists)
{
bool hasChildCheckLists = sdkDbContext.CheckLists.Any(x => x.ParentId == checkList.Id);
var checkListTranslation = sdkDbContext.CheckListTranslations
.Where(x => x.CheckListId == checkList.Id)
.First(x => x.LanguageId == language.Id).Text;
//var template = await sdkDbContext.CheckLists.SingleAsync(x => x.Id == groupedCase.templateId);
var groupedCase = groupedCases.SingleOrDefault(x => x.templateId == checkList.Id);
if (groupedCase != null)
{
var reportModel = new ReportEformModel
{
TemplateName = checkListTranslation,
FromDate = $"{fromDate:yyyy-MM-dd}",
ToDate = $"{toDate:yyyy-MM-dd}",
TextHeaders = new ReportEformTextHeaderModel(),
TableName = checkListTranslation,
};
// first pass
if (result.Count <= 0)
{
if (checkList.ReportH1 != null)
{
reportModel.TextHeaders.Header1 = checkList.ReportH1;
}
else
{
reportModel.TextHeaders.Header1 = checkListTranslation;
// reportModel.TableName = null;
// reportModel.TemplateName = null;
}
reportModel.TextHeaders.Header2 = checkList.ReportH2;
reportModel.TextHeaders.Header3 = checkList.ReportH3;
reportModel.TextHeaders.Header4 = checkList.ReportH4;
reportModel.TextHeaders.Header5 = checkList.ReportH5;
}
else // other pass
{
var header1 = result.LastOrDefault(x => x.TextHeaders.Header1 != null)?.TextHeaders.Header1;
var header2 = result.LastOrDefault(x => x.TextHeaders.Header2 != null)?.TextHeaders.Header2;
var header3 = result.LastOrDefault(x => x.TextHeaders.Header3 != null)?.TextHeaders.Header3;
var header4 = result.LastOrDefault(x => x.TextHeaders.Header4 != null)?.TextHeaders.Header4;
var header5 = result.LastOrDefault(x => x.TextHeaders.Header5 != null)?.TextHeaders.Header5;
// if not find or finded and templateHeader not equal
if (header1 == null || checkList.ReportH1 != header1)
{
reportModel.TextHeaders.Header1 = checkList.ReportH1 ?? "";
}
if (header2 == null || checkList.ReportH2 != header2)
{
reportModel.TextHeaders.Header2 = checkList.ReportH2 ?? "";
}
if (header3 == null || checkList.ReportH3 != header3)
{
reportModel.TextHeaders.Header3 = checkList.ReportH3 ?? "";
}
if (header4 == null || checkList.ReportH4 != header4)
{
reportModel.TextHeaders.Header4 = checkList.ReportH4 ?? "";
}
if (header5 == null || checkList.ReportH5 != header5)
{
reportModel.TextHeaders.Header5 = checkList.ReportH5 ?? "";
}
}
var fields = await core.Advanced_TemplateFieldReadAll(
checkList.Id, language);
foreach (var fieldDto in fields)
{
if (fieldDto.FieldType == Constants.FieldTypes.None)
{
var fieldTranslation =
await sdkDbContext.FieldTranslations.FirstAsync(x =>
x.FieldId == fieldDto.Id && x.LanguageId == language.Id);
reportModel.DescriptionBlocks.Add(fieldTranslation.Description);
}
if (!excludedFieldTypes.Contains(fieldDto.FieldType))
{
var fieldTranslation =
await sdkDbContext.FieldTranslations.FirstAsync(x =>
x.FieldId == fieldDto.Id && x.LanguageId == language.Id);
string text = fieldTranslation.Text;
if (hasChildCheckLists)
{
var clTranslation =
await sdkDbContext.CheckListTranslations.FirstOrDefaultAsync(x =>
x.CheckListId == fieldDto.CheckListId && x.LanguageId == language.Id);
if (checkListTranslation != clTranslation.Text)
{
text = $"{clTranslation.Text} - {text}";
}
}
var kvp = new KeyValuePair <int, string>(fieldDto.Id, text);
reportModel.ItemHeaders.Add(kvp);
}
}
// images
var templateCaseIds = groupedCase.cases.Select(x => (int?)x.MicrotingSdkCaseId).ToArray();
var imagesForEform = await sdkDbContext.FieldValues
.Include(x => x.UploadedData)
.Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
.Where(x => x.UploadedData.WorkflowState != Constants.WorkflowStates.Removed)
.Where(x => x.Field.FieldTypeId == 5)
.Where(x => templateCaseIds.Contains(x.CaseId))
.Where(x => x.UploadedDataId != null)
.OrderBy(x => x.CaseId)
.ToListAsync();
foreach (var imageField in imagesForEform)
{
var planningCase = groupedCase.cases.Single(x => x.MicrotingSdkCaseId == imageField.CaseId && x.PlanningId != 0);
var planningNameTranslation =
await _dbContext.PlanningNameTranslation.SingleOrDefaultAsync(x =>
x.PlanningId == planningCase.PlanningId && x.LanguageId == language.Id);
if (planningNameTranslation != null)
{
var label = $"{imageField.CaseId}; {planningNameTranslation.Name}";
var geoTag = "";
if (!string.IsNullOrEmpty(imageField.Latitude))
{
geoTag =
$"https://www.google.com/maps/place/{imageField.Latitude},{imageField.Longitude}";
}
var keyList = new List <string> {
imageField.CaseId.ToString(), label
};
var list = new List <string>();
if (!string.IsNullOrEmpty(imageField.UploadedData.FileName))
{
list.Add(isDocx
? $"{imageField.UploadedData.Id}_700_{imageField.UploadedData.Checksum}{imageField.UploadedData.Extension}"
: imageField.UploadedData.FileName);
list.Add(geoTag);
reportModel.ImageNames.Add(
new KeyValuePair <List <string>, List <string> >(keyList, list));
}
}
}
// posts
var casePostRequest = new CasePostsRequestCommonModel
{
Offset = 0,
PageSize = int.MaxValue,
TemplateId = checkList.Id,
};
var casePostListResult = await _casePostBaseService.GetCommonPosts(casePostRequest);
if (!casePostListResult.Success)
{
return(new OperationDataResult <List <ReportEformModel> >(
false,
casePostListResult.Message));
}
foreach (var casePostCommonModel in casePostListResult.Model.Entities)
{
reportModel.Posts.Add(new ReportEformPostModel
{
CaseId = casePostCommonModel.CaseId,
PostId = casePostCommonModel.PostId,
Comment = casePostCommonModel.Text,
SentTo = casePostCommonModel.ToRecipients,
SentToTags = casePostCommonModel.ToRecipientsTags,
PostDate = casePostCommonModel.PostDate
});
}
// add cases
foreach (var planningCase in groupedCase.cases.OrderBy(x => x.MicrotingSdkCaseDoneAt).ToList())
{
var planningNameTranslation =
await _dbContext.PlanningNameTranslation.SingleOrDefaultAsync(x =>
x.PlanningId == planningCase.PlanningId && x.LanguageId == language.Id);
if (planningNameTranslation != null)
{
var item = new ReportEformItemModel
{
Id = planningCase.Id,
MicrotingSdkCaseId = planningCase.MicrotingSdkCaseId,
MicrotingSdkCaseDoneAt = TimeZoneInfo.ConvertTimeFromUtc((DateTime)planningCase.MicrotingSdkCaseDoneAt, timeZoneInfo),
eFormId = planningCase.MicrotingSdkeFormId,
DoneBy = planningCase.DoneByUserName,
ItemName = planningNameTranslation.Name,
ItemDescription = planningCase.Planning.Description,
};
var caseFields = await core.Advanced_FieldValueReadList(
new List <int>()
{
planningCase.MicrotingSdkCaseId
}, language);
foreach (var caseField in reportModel.ItemHeaders.Select(itemHeader => caseFields
.FirstOrDefault(x => x.FieldId == itemHeader.Key)).Where(caseField => caseField != null))
{
switch (caseField.FieldType)
{
case Constants.FieldTypes.MultiSelect:
item.CaseFields.Add(caseField.ValueReadable.Replace("|", "<br>"));
break;
case Constants.FieldTypes.EntitySearch or
Constants.FieldTypes.EntitySelect or
Constants.FieldTypes.SingleSelect:
item.CaseFields.Add(caseField.ValueReadable);
break;
default:
item.CaseFields.Add(caseField.Value);
break;
}
}
item.ImagesCount = await sdkDbContext.FieldValues
.Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
.Where(x => x.Field.FieldTypeId == 5)
.Where(x => x.CaseId == planningCase.MicrotingSdkCaseId)
.Where(x => x.UploadedDataId != null)
.Select(x => x.Id)
.CountAsync();
item.PostsCount = casePostListResult.Model.Entities
.Where(x => x.CaseId == planningCase.MicrotingSdkCaseId)
.Select(x => x.PostId)
.Count();
reportModel.Items.Add(item);
}
}
result.Add(reportModel);
}
}
var reportEformModel = new ReportEformModel();
reportEformModel.NameTagsInEndPage.AddRange(_dbContext.PlanningTags
.Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
.Where(x => model.TagIds.Any(y => y == x.Id))
.Select(x => x.Name));
result.Add(reportEformModel);
if (result.Any())
{
return(new OperationDataResult <List <ReportEformModel> >(true, result));
}
return(new OperationDataResult <List <ReportEformModel> >(false, _itemsPlanningLocalizationService.GetString("NoDataInSelectedPeriod")));
}
catch (Exception e)
{
Trace.TraceError(e.Message);
_logger.LogError(e.Message);
return(new OperationDataResult <List <ReportEformModel> >(false,
_itemsPlanningLocalizationService.GetString("ErrorWhileGeneratingReport")));
}
}
public async Task <OperationDataResult <Paged <PlanningPnModel> > > Index(PlanningsRequestModel pnRequestModel)
{
try
{
var sdkCore =
await _coreService.GetCore();
await using var sdkDbContext = sdkCore.DbContextHelper.GetDbContext();
var planningsQuery = _dbContext.Plannings
.Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
.AsQueryable();
if (!string.IsNullOrEmpty(pnRequestModel.NameFilter))
{
planningsQuery = planningsQuery.Where(x =>
x.NameTranslations.Any(y => y.Name.Contains(pnRequestModel.NameFilter)));
}
if (!string.IsNullOrEmpty(pnRequestModel.DescriptionFilter))
{
planningsQuery = planningsQuery.Where(x =>
x.Description.Contains(pnRequestModel.DescriptionFilter));
}
var excludeSort = new List <string> {
"TranslatedName"
};
// sort
planningsQuery = QueryHelper.AddSortToQuery(planningsQuery, pnRequestModel.Sort,
pnRequestModel.IsSortDsc, excludeSort);
// ReSharper disable once ForeachCanBeConvertedToQueryUsingAnotherGetEnumerator
foreach (var tagId in pnRequestModel.TagIds)
{
planningsQuery = planningsQuery.Where(x => x.PlanningsTags.Any(y =>
y.PlanningTagId == tagId && y.WorkflowState != Constants.WorkflowStates.Removed));
}
// ReSharper disable once ForeachCanBeConvertedToQueryUsingAnotherGetEnumerator
foreach (var deviceUserId in pnRequestModel.DeviceUserIds)
{
planningsQuery = planningsQuery.Where(x => x.PlanningSites
.Where(y => y.WorkflowState != Constants.WorkflowStates.Removed)
.Select(y => y.SiteId)
.Contains(deviceUserId));
}
// calculate total before pagination
var total = await planningsQuery.Select(x => x.Id).CountAsync();
// add select
var localeString = await _userService.GetCurrentUserLocale();
if (string.IsNullOrEmpty(localeString))
{
return(new OperationDataResult <Paged <PlanningPnModel> >(false,
_itemsPlanningLocalizationService.GetString("LocaleDoesNotExist")));
}
var language = sdkDbContext.Languages.Single(x => x.LanguageCode == localeString);
var languageIemPlanning = _dbContext.Languages.Single(x => x.Id == language.Id);
var planningQueryWithSelect = AddSelectToPlanningQuery(planningsQuery, languageIemPlanning);
if (pnRequestModel.Sort == "TranslatedName")
{
planningQueryWithSelect = pnRequestModel.IsSortDsc
? planningQueryWithSelect.OrderByDescending(x => x.TranslatedName)
: planningQueryWithSelect.OrderBy(x => x.TranslatedName);
}
planningQueryWithSelect
= planningQueryWithSelect
.Skip(pnRequestModel.Offset)
.Take(pnRequestModel.PageSize);
var checkListIds = await planningsQuery.Select(x => x.RelatedEFormId).ToListAsync();
var checkListWorkflowState = sdkDbContext.CheckLists.Where(x => checkListIds.Contains(x.Id))
.Select(checkList => new KeyValuePair <int, string>(checkList.Id, checkList.WorkflowState))
.ToList();
// add select and take objects from db
var sql = planningQueryWithSelect.ToQueryString();
List <PlanningPnModel> plannings = await planningQueryWithSelect.ToListAsync();
// get site names
var assignedSitesFromPlanning = plannings.SelectMany(y => y.AssignedSites).ToList();
var sites = await sdkDbContext.Sites
.AsNoTracking()
.Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
.Where(x => assignedSitesFromPlanning.Select(y => y.SiteId).Contains(x.Id))
.Select(x => new CommonDictionaryModel
{
Id = x.Id,
Name = x.Name,
}).ToListAsync();
foreach (var planning in plannings)
{
foreach (var assignedSite in assignedSitesFromPlanning)
{
foreach (var site in sites.Where(site => site.Id == assignedSite.SiteId))
{
assignedSite.Name = site.Name;
}
}
var(_, value) = checkListWorkflowState.SingleOrDefault(x => x.Key == planning.BoundEform.RelatedEFormId);
planning.BoundEform.IsEformRemoved = value == Constants.WorkflowStates.Removed;
// This is done to update existing Plannings to using EFormSdkFolderId instead of EFormSdkFolderName
if ((planning.Folder.EFormSdkFolderId == 0 || planning.Folder.EFormSdkFolderId == null) && planning.Folder.EFormSdkFolderName != null)
{
var locateFolder = await sdkDbContext.Folders
.Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
.Where(x => x.Name == planning.Folder.EFormSdkFolderName)
.FirstOrDefaultAsync();
if (locateFolder != null)
{
var thePlanning = await _dbContext.Plannings.SingleAsync(x => x.Id == planning.Id);
thePlanning.SdkFolderId = locateFolder.Id;
await thePlanning.Update(_dbContext);
planning.Folder.EFormSdkFolderId = locateFolder.Id;
}
}
var folder = await sdkDbContext.Folders
.Include(x => x.Parent)
.Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
.Where(y => y.Id == planning.Folder.EFormSdkFolderId)
.Select(x => new
{
x.Name,
x.Parent,
x.Id,
})
.FirstOrDefaultAsync();
if (folder != null)
{
planning.Folder.EFormSdkFolderId = folder.Id;
if (folder.Parent != null)
{
planning.Folder.EFormSdkParentFolderName = folder.Parent.Name;
}
else
{
planning.Folder.EFormSdkFolderName = null;
}
}
}
var planningsModel = new Paged <PlanningPnModel>
{
Total = total,
Entities = plannings
};
return(new OperationDataResult <Paged <PlanningPnModel> >(true, planningsModel));
}
catch (Exception e)
{
Trace.TraceError(e.Message);
return(new OperationDataResult <Paged <PlanningPnModel> >(false,
_itemsPlanningLocalizationService.GetString("ErrorObtainingLists")));
}
}