public async Task <OperationDataResult <ExcelParseResult> > Import(Stream excelStream)
{
try
{
var result = new ExcelParseResult();
var core = await _coreHelper.GetCore();
await using var dbContext = core.DbContextHelper.GetDbContext();
var language = await _userService.GetCurrentUserLanguage();
var timeZone = await _userService.GetCurrentUserTimeZoneInfo();
var templatesDto = await core.TemplateItemReadAll(
false,
"",
"",
false,
"",
new List <int>(),
timeZone, language);
// Read file
var fileResult = _eformExcelImportService.EformImport(excelStream);
// Validation
var excelErrors = new List <ExcelParseErrorModel>();
foreach (var excelModel in fileResult)
{
var templateByName = templatesDto
.FirstOrDefault(x => string.Equals(
x.Label,
excelModel.Name,
StringComparison.CurrentCultureIgnoreCase));
if (templateByName != null)
{
var error = new ExcelParseErrorModel
{
Row = excelModel.ExcelRow,
Message = _localizationService.GetStringWithFormat(
"EFormWithNameAlreadyExists",
excelModel.Name)
};
excelErrors.Add(error);
}
}
var duplicates = fileResult
.GroupBy(x => x.Name.ToLower())
.Select(x => new
{
x.Key,
Count = x.Count(),
})
.Where(x => x.Count > 1)
.ToList();
foreach (var duplicateObject in duplicates)
{
var error = new ExcelParseErrorModel
{
Row = 0,
Message = _localizationService.GetStringWithFormat(
"EFormWithNameAlreadyExistsInTheImportedDocument",
duplicateObject.Key)
};
excelErrors.Add(error);
}
result.Errors = excelErrors;
if (excelErrors.Any())
{
return(new OperationDataResult <ExcelParseResult>(
true,
result));
}
// Process file result
foreach (var importExcelModel in fileResult)
{
if (!string.IsNullOrEmpty(importExcelModel.Name) ||
!string.IsNullOrEmpty(importExcelModel.EformXML))
{
var tags = await core.GetAllTags(false);
var tagIds = new List <int>();
// Process tags
foreach (var tag in importExcelModel.Tags)
{
var tagId = tags
.Where(x => string.Equals(x.Name, tag, StringComparison.CurrentCultureIgnoreCase))
.Select(x => x.Id)
.FirstOrDefault();
if (tagId < 1)
{
tagId = await core.TagCreate(tag);
}
tagIds.Add(tagId);
}
// Create eform
var newTemplate = await core.TemplateFromXml(importExcelModel.EformXML);
newTemplate = await core.TemplateUploadData(newTemplate);
if (newTemplate == null)
{
throw new Exception(_localizationService.GetString("eFormCouldNotBeCreated"));
}
// Set tags to eform
var eFormId = await core.TemplateCreate(newTemplate);
var eForm = await dbContext.CheckLists.SingleAsync(x => x.Id == eFormId);
eForm.ReportH1 = importExcelModel.ReportH1;
eForm.ReportH2 = importExcelModel.ReportH2;
eForm.ReportH3 = importExcelModel.ReportH3;
eForm.ReportH4 = importExcelModel.ReportH4;
await eForm.Update(dbContext);
if (tagIds.Any())
{
await core.TemplateSetTags(newTemplate.Id, tagIds);
}
}
}
result.Message = _localizationService.GetString("ImportFinishedSuccessfully");
return(new OperationDataResult <ExcelParseResult>(true, result));
}
catch (Exception e)
{
_logger.LogError(e, e.Message);
return(new OperationDataResult <ExcelParseResult>(false, e.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")));
}
}