private Dictionary <TalentExcelImport, ITalent> ExcelImportAddDeductions(Dictionary <TalentExcelImport, ITalent> talentDic)
{
foreach (var talent in talentDic)
{
if (IsValidDeduction(talent.Key.VerwandteFertigkeiten, talent.Key.Ableiten))
{
var deductionTalentStrings = GetSplitDeduction(talent.Key.VerwandteFertigkeiten, talent.Key.Ableiten);
foreach (var deductionString in deductionTalentStrings)
{
var value = deductionString;
var valueint = -1;
var mainReqg = new Regex("[(][+][0-9]?[0-9][)]");
var innerReqg = new Regex("[0-9]?[0-9]");
var stringTalentReqg = new Regex("[(][A-Za-zäüß]{1,}[ ]?[A-Za-zäüß]{1,}[)]");
if (mainReqg.IsMatch(deductionString))
{
value = mainReqg.Split(deductionString)[0].Trim();
if (!int.TryParse(innerReqg.Match(deductionString).ToString(), out valueint))
{
valueint = -1;
}
}
if (valueint == -1)
{
valueint = talent.Value.BaseDeduction;
}
ITalentDeduction deduction = null;
var deductionTalent = talentDic.Where(x => x.Value.Name.StartsWith(value, StringComparison.CurrentCulture));
if (deductionTalent.Any())
{
deduction = new TalentDeductionTalent(deductionTalent.First().Value, valueint, talent.Value.BaseDeduction);
}
else
{
var stringTalent = stringTalentReqg.Split(deductionString)[0].Trim();
var innerTalent = talentDic.Where(x => x.Value.Name.StartsWith(value, StringComparison.CurrentCulture));
if (innerTalent.Any())
{
value = mainReqg.Split(deductionString)[0].Trim();
if (stringTalentReqg.IsMatch(value))
{
var description = stringTalentReqg.Match(value).Value;
description = description.Replace("(", "").Replace(")", "");
deduction = new TalentDeductionTalent(innerTalent.First().Value, valueint, talent.Value.BaseDeduction, description);
}
else
{
deduction = new TalentDeductionFreeText(deductionString);
}
}
else
{
deduction = new TalentDeductionFreeText(deductionString);
}
}
talent.Value.Deductions.Add(deduction);
}
}
}
return(talentDic);
}
public static List <ITalent> ExcelImport(string importFile, out List <LanguageFamily> familieList)
{
familieList = new List <LanguageFamily>();
var ret = new List <ITalent>();
var excelTalentDic = new Dictionary <string, List <ExcelTalent> >();
var talentsWithDeduction = new Dictionary <ITalent, ExcelTalent>();
var talentWithRequirements = new Dictionary <ITalent, ExcelTalent>();
#region Import der Exel Datei
SpreadsheetDocument document = SpreadsheetDocument.Open(importFile, false);
WorkbookPart wbPart = document.WorkbookPart;
List <Sheet> sheets = wbPart.Workbook.Descendants <Sheet>().ToList();
foreach (var sheet in sheets)
{
var orginalPosition = 0;
var contentType = sheet.Name;
var currentTitle = string.Empty;
var excelTalents = new List <ExcelTalent>();
WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(sheet.Id));
var rowList = wsPart.Worksheet.GetFirstChild <SheetData>().Elements <Row>().ToList();
var titleRowHeaders = new List <string>();
var titleRow = rowList[0];
rowList.RemoveAt(0); //Titel Leiste Entfernen
foreach (var cell in titleRow.Descendants <Cell>().ToList())
{
var cellValue = ExcelImportGetCellValue(wbPart, cell);
titleRowHeaders.Add(cellValue);
}
foreach (var row in rowList)
{
var excelTalent = new ExcelTalent();
var celllist = row.Descendants <Cell>().ToList();
var counter = 0;
excelTalent.OrginalPosition = orginalPosition++;
foreach (var cell in celllist)
{
var cellValue = ExcelImportGetCellValue(wbPart, cell);
excelTalent.AddValue(titleRowHeaders[counter], cellValue);
counter++;
}
var excelRowType = excelTalent.ExcelRowType();
if (excelRowType == ExcleRowType.Title)
{
var title = excelTalent.Talent.Replace(EXCELTITLE, "");
currentTitle = title;
}
else if (excelRowType == ExcleRowType.ValidTalent)
{
excelTalent.Title = currentTitle;
excelTalents.Add(excelTalent);
}
}
excelTalentDic.Add(contentType, excelTalents);
}
#endregion
#region Talente Erstellen
foreach (var talentGroup in excelTalentDic)
{
LanguageFamily currentLanguageFamily = null;
var talentList = talentGroup.Value;
var pos = 0;
foreach (var excelTalent in talentList)
{
var name = string.Empty;
var nameExtension = string.Empty;
if (excelTalent.Talent.Contains("("))
{
var items = excelTalent.Talent.Split('(');
name = items[0];
nameExtension = items[1].Split(')').First();
}
else
{
name = excelTalent.Talent;
}
ITalent newTalent = null;
if (!string.IsNullOrEmpty(name))
{
newTalent = SearchTalent(name, ret, GetTypeFromString(talentGroup.Key));
if (newTalent == null)
{
newTalent = CreateTalent(
contentType: talentGroup.Key,
probe: excelTalent.GetConvertAttribute(),
be: excelTalent.BE,
name: name,
nameExtension: nameExtension,
orginalPos: excelTalent.OrginalPosition);
}
}
#region Sprache
if (talentGroup.Key == nameof(TalentSpeaking))
{
TalentSpeaking talentLanguage = null;
TalentWriting talentWriting = null;
if (newTalent != null)
{
talentLanguage = (TalentSpeaking)newTalent;
}
if (!string.IsNullOrEmpty(excelTalent.Schrift))
{
talentWriting = (TalentWriting)SearchTalent(excelTalent.Schrift, ret, typeof(TalentWriting));
if (talentWriting == null)
{
talentWriting = (TalentWriting)CreateTalent(
contentType: nameof(TalentWriting),
probe: excelTalent.GetConvertAttribute(),
be: excelTalent.Komplex2,
name: excelTalent.Schrift,
nameExtension: nameExtension);
}
}
if (currentLanguageFamily == null || currentLanguageFamily.Name != excelTalent.Title)
{
currentLanguageFamily = new LanguageFamily(excelTalent.Title);
familieList.Add(currentLanguageFamily);
pos = 0;
}
if (talentLanguage != null)
{
currentLanguageFamily.Languages.Add(pos, talentLanguage);
}
if (talentWriting != null)
{
currentLanguageFamily.Writings.Add(pos, talentWriting);
}
if (talentWriting != null && !ret.Contains(talentWriting))
{
ret.Add(talentWriting);
}
}
#endregion
if (excelTalent.IsValidVerwanteFertigkeit())
{
talentsWithDeduction.Add(newTalent, excelTalent);
}
if (excelTalent.IsValidAnforderung())
{
talentWithRequirements.Add(newTalent, excelTalent);
}
if (newTalent != null && !ret.Contains(newTalent))
{
ret.Add(newTalent);
}
pos++;
}
}
ret = new List <ITalent>(ret.OrderBy(x => x.Name));
foreach (var talentwithDeduction in talentsWithDeduction)
{
var deductionTalentStrings = talentwithDeduction.Value.GetSplitDeduction();
foreach (var deductionString in deductionTalentStrings)
{
var value = deductionString;
var valueint = -1;
var mainReqg = new Regex("[(][+][0-9]?[0-9][)]");
var innerReqg = new Regex("[0-9]?[0-9]");
var stringTalentReqg = new Regex("[(][A-Za-zäüß]{1,}[ ]?[A-Za-zäüß]{1,}[)]");
if (mainReqg.IsMatch(deductionString))
{
value = mainReqg.Split(deductionString)[0].Trim();
if (!int.TryParse(innerReqg.Match(deductionString).ToString(), out valueint))
{
valueint = -1;
}
}
if (valueint == -1)
{
valueint = talentwithDeduction.Key.BaseDeduction;
}
ITalentDeduction deduction = null;
var deductionTalent = ret.Where(x => x.Name.StartsWith(value, StringComparison.CurrentCulture));
if (deductionTalent.Any())
{
deduction = new TalentDeductionTalent(deductionTalent.First(), valueint, talentwithDeduction.Key.BaseDeduction);
}
else
{
var stringTalent = stringTalentReqg.Split(deductionString)[0].Trim();
var innerTalent = ret.Where(x => x.Name == stringTalent).FirstOrDefault();
if (innerTalent != null)
{
value = mainReqg.Split(deductionString)[0].Trim();
if (stringTalentReqg.IsMatch(value))
{
var description = stringTalentReqg.Match(value).Value;
description = description.Replace("(", "").Replace(")", "");
deduction = new TalentDeductionTalent(innerTalent, valueint, talentwithDeduction.Key.BaseDeduction, description);
}
else
{
deduction = new TalentDeductionFreeText(deductionString);
}
}
else
{
deduction = new TalentDeductionFreeText(deductionString);
}
}
talentwithDeduction.Key.Deductions.Add(deduction);
}
}
foreach (var talentWithRequirement in talentWithRequirements)
{
var splitRequirement = talentWithRequirement.Value.GetSplitRequirement();
foreach (var requirementString in splitRequirement)
{
ITalentRequirement requirement;
var talent = (AbstractTalentGeneral)talentWithRequirement.Key;
var value = requirementString;
var valueStart = -1;
var valueEnd = -1;
var reqTalent = ret.Where(x => x.Name.StartsWith(value, StringComparison.CurrentCulture));
var startReqg = new Regex("[0-9]?[0-9][+][:]");
var endReqg = new Regex("[ ][0-9]?[0-9]");
if (endReqg.IsMatch(value))
{
if (startReqg.IsMatch(value))
{
var innerStartReq = new Regex("[0-9]?[0-9]");
var startvalue = startReqg.Match(value).ToString();
var truestartValue = innerStartReq.Match(startvalue).ToString();
valueStart = Int32.Parse(truestartValue, Helper.CultureInfo);
value = startReqg.Split(value)[1];
}
var startSplit = startReqg.Split(value);
valueEnd = Int32.Parse(endReqg.Match(value).ToString(), Helper.CultureInfo);
value = endReqg.Split(value)[0].Trim();
}
requirement = new TalentRequirementFreeText(requirementString);
if (reqTalent.Any() && valueEnd != -1 && valueStart != -1)
{
requirement = new TalentRequirementTalent(reqTalent.First(), valueEnd, valueStart);
}
else if (reqTalent.Any() && valueEnd != -1)
{
var trueReqTalent = reqTalent.First();
requirement = new TalentRequirementTalent(reqTalent.First(), valueEnd);
}
else
{
requirement = new TalentRequirementFreeText(requirementString);
}
talent.Requirements.Add(requirement);
}
}
#endregion
return(ret);
}
