public void WriteOutNumberOfEntriesPerOrtsTeil(ZustaendigAgOrtsTeilOrt ZustaendigAgOrtsTeilOrtInstance,
string FileName)
{
XLWorkbook workBook = new XLWorkbook();
foreach (
StatistikRootClass.DistributionCountTyp distTyp in Enum.GetValues(typeof (StatistikRootClass.DistributionCountTyp)))
{
IXLWorksheet workSheet = workBook.Worksheets.Add(distTyp.ToString());
int StartForContentLine = WriteOutNumberOfEntriesPerOrtsTeil(workSheet, distTyp);
WriteOutNumberOfEntriesPerOrtsTeil(workSheet, ZustaendigAgOrtsTeilOrtInstance, distTyp,
StartForContentLine);
}
workBook.SaveAs(new FileStream(FileName, FileMode.Create, FileAccess.Write, FileShare.ReadWrite));
workBook.Dispose();
}
public void WriteOutNumberOfEntriesPerOrtsTeil(IXLWorksheet workSheet,
ZustaendigAgOrtsTeilOrt ZustaendigAgOrtsTeilOrtInstance,
StatistikRootClass.DistributionCountTyp DistTyp, int StartLine)
{
switch (DistTyp)
{
case StatistikRootClass.DistributionCountTyp.AllTogether:
{
foreach (AGCounter agCounter in ZustaendigAgOrtsTeilOrtInstance.Children)
{
workSheet.Cell($"B{StartLine}").Value = agCounter.ArbeitsGruppeDaten.NameId;
IXLRange AGNameRange = workSheet.Range($"C{StartLine}:D{StartLine}").Merge();
AGNameRange.Style.Alignment.WrapText = true;
AGNameRange.Style.Alignment.SetVertical(XLAlignmentVerticalValues.Justify);
AGNameRange.Value = agCounter.ArbeitsGruppeDaten.Beschreibung;
StartLine++;
StartLine++;
foreach (OrtsTeilCounter ortsTeilCounter in agCounter.Children)
{
workSheet.Cell($"C{StartLine}").Value = ortsTeilCounter.OrtsTeilDaten.NameId;
StartLine++;
foreach (OrtsCounter ortsCounter in ortsTeilCounter.Children)
{
workSheet.Cell($"D{StartLine}").Value = ortsCounter.OrtDaten.Bezeichnung;
workSheet.Cell($"E{StartLine}").SetDataType(XLCellValues.Number);
workSheet.Cell($"F{StartLine}").SetDataType(XLCellValues.Number);
workSheet.Cell($"E{StartLine}").Value = ortsCounter.Counter.NumberOfVorschlaege;
workSheet.Cell($"F{StartLine}").Value = ortsCounter.Counter.NumberOfIdeen;
StartLine++;
}
workSheet.Cell($"D{StartLine}").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
workSheet.Cell($"D{StartLine}").Value = ortsTeilCounter.OrtsTeilDaten.NameId + " Summen";
workSheet.Cell($"E{StartLine}").SetDataType(XLCellValues.Number);
workSheet.Cell($"F{StartLine}").SetDataType(XLCellValues.Number);
workSheet.Cell($"E{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray;
workSheet.Cell($"F{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray;
workSheet.Cell($"E{StartLine}").Value = ortsTeilCounter.Counter.NumberOfVorschlaege;
workSheet.Cell($"F{StartLine}").Value = ortsTeilCounter.Counter.NumberOfIdeen;
StartLine++;
StartLine++;
}
StartLine++;
workSheet.Cell($"D{StartLine}").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
workSheet.Cell($"D{StartLine}").Value = agCounter.ArbeitsGruppeDaten.NameId + " Summen";
workSheet.Cell($"E{StartLine}").SetDataType(XLCellValues.Number);
workSheet.Cell($"F{StartLine}").SetDataType(XLCellValues.Number);
workSheet.Cell($"E{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray;
workSheet.Cell($"F{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray;
workSheet.Cell($"E{StartLine}").Value = agCounter.Counter.NumberOfVorschlaege;
workSheet.Cell($"F{StartLine}").Value = agCounter.Counter.NumberOfIdeen;
StartLine++;
StartLine++;
}
StartLine++;
}
break;
case StatistikRootClass.DistributionCountTyp.PerAG:
{
StartLine++;
foreach (AGCounter agCounter in ZustaendigAgOrtsTeilOrtInstance.Children)
{
workSheet.Cell($"B{StartLine}").Value = agCounter.ArbeitsGruppeDaten.NameId;
IXLRange AGNameRange = workSheet.Range($"C{StartLine}:D{StartLine}").Merge();
AGNameRange.Style.Alignment.WrapText = true;
AGNameRange.Style.Alignment.SetVertical(XLAlignmentVerticalValues.Justify);
AGNameRange.Value = agCounter.ArbeitsGruppeDaten.Beschreibung;
workSheet.Cell($"E{StartLine}").SetDataType(XLCellValues.Number);
workSheet.Cell($"F{StartLine}").SetDataType(XLCellValues.Number);
workSheet.Cell($"E{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray;
workSheet.Cell($"F{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray;
workSheet.Cell($"E{StartLine}").Value = agCounter.Counter.NumberOfProjekte;
workSheet.Cell($"F{StartLine}").Value = agCounter.Counter.NumberOfIdeen;
StartLine++;
StartLine++;
}
StartLine++;
}
break;
case StatistikRootClass.DistributionCountTyp.PerOrtsTeil:
{
foreach (AGCounter agCounter in ZustaendigAgOrtsTeilOrtInstance.Children)
{
StartLine++;
foreach (OrtsTeilCounter ortsTeilCounter in agCounter.Children)
{
workSheet.Cell($"C{StartLine}").Value = ortsTeilCounter.OrtsTeilDaten.NameId;
workSheet.Cell($"E{StartLine}").SetDataType(XLCellValues.Number);
workSheet.Cell($"F{StartLine}").SetDataType(XLCellValues.Number);
workSheet.Cell($"E{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray;
workSheet.Cell($"F{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray;
workSheet.Cell($"E{StartLine}").Value = ortsTeilCounter.Counter.NumberOfProjekte;
workSheet.Cell($"F{StartLine}").Value = ortsTeilCounter.Counter.NumberOfIdeen;
StartLine++;
StartLine++;
}
}
StartLine++;
}
break;
}
}
private IXLCell CreateXLSXContentLines(ZustaendigAgOrtsTeilOrt ZustaendigAgOrtsTeilOrtInstance,
IXLWorksheet workSheet, IXLCell StartCell,
StatistikRootClass.StatistikDataSelectionTypes writeOutType,
bool StandardContent = true, bool ProcessOrte = true)
{
int ActuallRow = StartCell.WorksheetRow().RowNumber() + 1;
int FirstCellNumber = StartCell.WorksheetColumn().ColumnNumber();
IXLCell LastCell = null;
foreach (AGCounter agCounter in ZustaendigAgOrtsTeilOrtInstance.Children)
{
if (StandardContent)
{
workSheet.Cell(ActuallRow, FirstCellNumber).Value = agCounter.ArbeitsGruppeDaten.NameId;
workSheet.Cell(ActuallRow, FirstCellNumber + 1).Value
= agCounter.ArbeitsGruppeDaten.Beschreibung;
}
ActuallRow++;
foreach (OrtsTeilCounter ortsTeilCounter in agCounter.Children)
{
if (StandardContent)
{
workSheet.Cell(ActuallRow, FirstCellNumber + 1).Value
= ortsTeilCounter.OrtsTeilDaten.NameId;
}
if (ProcessOrte)
{
ActuallRow++;
if (StandardContent)
{
foreach (OrtsCounter ortsCounter in ortsTeilCounter.Children)
{
workSheet.Cell(ActuallRow, FirstCellNumber + 2).Value
= ortsCounter.OrtDaten.Bezeichnung;
LastCell = ShowStandardDataPerEntry(workSheet, ortsCounter.Counter,
ActuallRow, 6);
ActuallRow = LastCell.Address.RowNumber + 1;
}
if (ProcessOrte)
{
IXLRange OrtsTeilLine = workSheet.Range(ActuallRow, FirstCellNumber + 2, ActuallRow, FirstCellNumber + 3).Merge();
OrtsTeilLine.Style.Font.SetBold();
OrtsTeilLine.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
OrtsTeilLine.Value = ortsTeilCounter.OrtsTeilDaten.NameId + " - Summe";
LastCell = ShowStandardDataPerEntry(workSheet, ortsTeilCounter.Counter,
ActuallRow, 6);
ActuallRow = LastCell.Address.RowNumber;
}
}
else
{
foreach (OrtsCounter ortsCounter in ortsTeilCounter.Children)
{
LastCell = ShowDataPerEntry(workSheet, writeOutType, ortsCounter.Counter,
ActuallRow, StartCell.Address.ColumnNumber);
ActuallRow = LastCell.Address.RowNumber + 1;
}
if (ProcessOrte)
{
LastCell = ShowDataPerEntry(workSheet, writeOutType, ortsTeilCounter.Counter,
ActuallRow, StartCell.Address.ColumnNumber);
ActuallRow = LastCell.Address.RowNumber;
}
}
ActuallRow++;
}
else
{
if (StandardContent)
{
LastCell = ShowStandardDataPerEntry(workSheet, ortsTeilCounter.Counter,
ActuallRow, 6);
}
else
{
LastCell = ShowDataPerEntry(workSheet, writeOutType, ortsTeilCounter.Counter,
ActuallRow, StartCell.Address.ColumnNumber);
}
ActuallRow = LastCell.Address.RowNumber + 1;
}
}
}
return LastCell;
}
private void WriteOutZustaendigAGOrtsTeilOrt(ZustaendigAgOrtsTeilOrt ZustaendigAgOrtsTeilOrtInstance,
StatistikRootClass.StatistikDataSelectionTypes writeOutType, IXLWorksheet workSheet, bool ProcessOrte)
{
IXLCell StartCell = workSheet.Cell(5, 2);
IXLCell StartTableCell = workSheet.Cell(4, 2);
IXLCell EndCell = CreateXLSXContentLines(ZustaendigAgOrtsTeilOrtInstance,
workSheet, StartCell, writeOutType, true, ProcessOrte);
CreateXLSXHeadLines(workSheet, StartTableCell, writeOutType);
IXLCell StartXLSXContent = workSheet.Cell(4, EndCell.Address.ColumnNumber);
StartCell = workSheet.Cell(5, EndCell.Address.ColumnNumber);
EndCell = CreateXLSXContentLines(ZustaendigAgOrtsTeilOrtInstance,
workSheet, StartCell, writeOutType, false, ProcessOrte);
}
private void WriteOutZustaendigAGOrtsTeilOrt(ZustaendigAgOrtsTeilOrt ZustaendigAgOrtsTeilOrtInstance,
StatistikRootClass.StatistikDataSelectionTypes writeOutType, string OutputFileName)
{
XLWorkbook workBook = new XLWorkbook();
IXLWorksheet workSheetOhneOrte = workBook.Worksheets.Add(writeOutType.ToString());
WriteOutZustaendigAGOrtsTeilOrt(ZustaendigAgOrtsTeilOrtInstance,
writeOutType, workSheetOhneOrte, false);
IXLWorksheet workSheetMitOrte = workBook.Worksheets.Add(writeOutType + "_inclOrte");
WriteOutZustaendigAGOrtsTeilOrt(ZustaendigAgOrtsTeilOrtInstance,
writeOutType, workSheetMitOrte, true);
workBook.SaveAs(new FileStream(OutputFileName, FileMode.Create,
FileAccess.Write, FileShare.ReadWrite));
workBook.Dispose();
}
public List<object> CreateData(DataWrapper.Statistics RequestedStatistic,
DataWrapper.ReportType StatisticSubType,
string OutputDirectory)
{
List<object> Result = null;
switch (RequestedStatistic)
{
case DataWrapper.Statistics.ZustaendigAgOrtsTeilOrt:
{
ZustaendigAgOrtsTeilOrt ZustaendigAgOrtsTeilOrtInstance = new ZustaendigAgOrtsTeilOrt();
ZustaendigAgOrtsTeilOrtInstance.FillMe();
ZustaendigAgOrtsTeilOrtInstance.AddMyChildsToMe();
switch (StatisticSubType)
{
case DataWrapper.ReportType.StatistikenBaseData:
{
foreach (StatistikRootClass.StatistikDataSelectionTypes writeOutType in
Enum.GetValues(typeof (StatistikRootClass.StatistikDataSelectionTypes)))
{
string OutputFileName = Path.Combine(OutputDirectory,
RequestedStatistic + "_" + writeOutType + ".csv");
if (File.Exists(OutputFileName))
Basics.SecureFileDelete(OutputFileName);
if (File.Exists(OutputFileName.Replace(".csv", ".xlsx")))
Basics.SecureFileDelete(OutputFileName.Replace(".csv", ".xlsx"));
WriteOutZustaendigAGOrtsTeilOrt(ZustaendigAgOrtsTeilOrtInstance,
writeOutType, OutputFileName.Replace(".csv", ".xlsx"));
ZustaendigAgOrtsTeilOrtInstance.WriteCSVData(writeOutType, OutputFileName);
}
string HaeufigkeitenFileName = Path.Combine(OutputDirectory, "Ideen_Projekt_Häufigkeiten.xlsx");
if (File.Exists(HaeufigkeitenFileName))
Basics.SecureFileDelete(HaeufigkeitenFileName);
WriteOutNumberOfEntriesPerOrtsTeil
(ZustaendigAgOrtsTeilOrtInstance, HaeufigkeitenFileName);
}
break;
case DataWrapper.ReportType.StatistikenLayouted:
break;
}
}
break;
case DataWrapper.Statistics.SchulenZuOrtsTeilen:
{
}
break;
case DataWrapper.Statistics.TypenVerteilung:
{
}
break;
case DataWrapper.Statistics.GenderedIdeen:
{
Data.DbServer3.WordUp.WSPlakate.Clear();
Data.DbServer3.WordUp.WSPlakate.Load_ActualPlakate();
bool IsExcelOrPdf = true;
if (OutputDirectory.Contains("PDF"))
IsExcelOrPdf = false;
string FileNamePart = RequestedStatistic + ".xlsx";
string OutputFileName = Path.Combine(OutputDirectory,
FileNamePart);
if (File.Exists(OutputFileName))
Basics.SecureFileDelete(OutputFileName);
if (IsExcelOrPdf)
WriteOutStatusTaggedGenderExcel(WordUp.DataSelection.Ideen, OutputFileName);
else
{
Result = CreateOutStatusTaggedGenderPDF
(WordUp.DataSelection.Ideen, RequestedStatistic).ToList<Object>();
}
}
break;
case DataWrapper.Statistics.GenderedWuensche:
{
Data.DbServer3.WordUp.WSPlakate.Load_ActualPlakate();
Data.DbServer3.WordUp.AktivitaetsTypen.Load_All();
Data.DbServer3.WordUp.Aktivitaeten.Load_ActualAktivitaeten();
Data.DbServer3.WordUp.Projekte.Load_ActualProjekte();
Data.DbServer3.WordUp.AcceptChanges();
bool IsExcelOrPdf = true;
if (OutputDirectory.Contains("PDF"))
IsExcelOrPdf = false;
string FileNamePart = RequestedStatistic + ".xlsx";
string OutputFileName = Path.Combine(OutputDirectory,
FileNamePart);
if (File.Exists(OutputFileName))
Basics.SecureFileDelete(OutputFileName);
if (IsExcelOrPdf)
WriteOutStatusTaggedGenderExcel(WordUp.DataSelection.Wuensche, OutputFileName);
else
{
Result = CreateOutStatusTaggedGenderPDF
(WordUp.DataSelection.Wuensche, RequestedStatistic).ToList<Object>();
}
}
break;
case DataWrapper.Statistics.GenderedPlanungen:
{
Data.DbServer3.WordUp.WSPlakate.Load_ActualPlakate();
Data.DbServer3.WordUp.AktivitaetsTypen.Load_All();
Data.DbServer3.WordUp.Aktivitaeten.Load_ActualAktivitaeten();
Data.DbServer3.WordUp.Projekte.Load_ActualProjekte();
Data.DbServer3.WordUp.AcceptChanges();
bool IsExcelOrPdf = true;
if (OutputDirectory.Contains("PDF"))
IsExcelOrPdf = false;
string FileNamePart = RequestedStatistic + ".xlsx";
string OutputFileName = Path.Combine(OutputDirectory,
FileNamePart);
if (File.Exists(OutputFileName))
Basics.SecureFileDelete(OutputFileName);
if (IsExcelOrPdf)
WriteOutStatusTaggedGenderExcel(WordUp.DataSelection.Planungen, OutputFileName);
else
{
Result = CreateOutStatusTaggedGenderPDF
(WordUp.DataSelection.Planungen, RequestedStatistic).ToList<Object>();
}
}
break;
case DataWrapper.Statistics.GenderedProjekte:
{
Data.DbServer3.WordUp.WSPlakate.Load_ActualPlakate();
Data.DbServer3.WordUp.AktivitaetsTypen.Load_All();
Data.DbServer3.WordUp.Aktivitaeten.Load_ActualAktivitaeten();
Data.DbServer3.WordUp.Projekte.Load_ActualProjekte();
Data.DbServer3.WordUp.AcceptChanges();
bool IsExcelOrPdf = true;
if (OutputDirectory.Contains("PDF"))
IsExcelOrPdf = false;
string FileNamePart = RequestedStatistic + ".xlsx";
string OutputFileName = Path.Combine(OutputDirectory,
FileNamePart);
if (File.Exists(OutputFileName))
Basics.SecureFileDelete(OutputFileName);
if (IsExcelOrPdf)
WriteOutStatusTaggedGenderExcel(WordUp.DataSelection.Projekte, OutputFileName);
else
{
Result = CreateOutStatusTaggedGenderPDF
(WordUp.DataSelection.Projekte, RequestedStatistic).ToList<Object>();
}
}
break;
case DataWrapper.Statistics.GenderedRealisierungen:
{
Data.DbServer3.WordUp.WSPlakate.Load_ActualPlakate();
Data.DbServer3.WordUp.AktivitaetsTypen.Load_All();
Data.DbServer3.WordUp.Aktivitaeten.Load_ActualAktivitaeten();
Data.DbServer3.WordUp.Projekte.Load_ActualProjekte();
Data.DbServer3.WordUp.AcceptChanges();
bool IsExcelOrPdf = true;
if (OutputDirectory.Contains("PDF"))
IsExcelOrPdf = false;
string FileNamePart = RequestedStatistic + ".xlsx";
string OutputFileName = Path.Combine(OutputDirectory,
FileNamePart);
if (File.Exists(OutputFileName))
Basics.SecureFileDelete(OutputFileName);
if (IsExcelOrPdf)
WriteOutStatusTaggedGenderExcel(WordUp.DataSelection.Realisierungen, OutputFileName);
else
{
Result = CreateOutStatusTaggedGenderPDF
(WordUp.DataSelection.Realisierungen, RequestedStatistic).ToList<Object>();
}
}
break;
case DataWrapper.Statistics.GenderedVergleich:
{
Data.DbServer3.WordUp.WSPlakate.Load_ActualPlakate();
Data.DbServer3.WordUp.AktivitaetsTypen.Load_All();
Data.DbServer3.WordUp.Aktivitaeten.Load_ActualAktivitaeten();
Data.DbServer3.WordUp.Projekte.Load_ActualProjekte();
Data.DbServer3.WordUp.AcceptChanges();
bool IsExcelOrPdf = true;
if (OutputDirectory.Contains("PDF"))
IsExcelOrPdf = false;
string FileNamePart = RequestedStatistic + ".xlsx";
string OutputFileName = Path.Combine(OutputDirectory,
FileNamePart);
if (File.Exists(OutputFileName))
Basics.SecureFileDelete(OutputFileName);
if (IsExcelOrPdf)
WriteOutStatusTaggedGenderExcel(WordUp.DataSelection.Planungen, OutputFileName);
else
{
List<Object> ResultIdeen = CreateOutStatusTaggedGenderPDF
(WordUp.DataSelection.Ideen, RequestedStatistic);
List<Object> ResultWuensche = CreateOutStatusTaggedGenderPDF
(WordUp.DataSelection.Wuensche, RequestedStatistic);
List<Object> ResultPlanung = CreateOutStatusTaggedGenderPDF
(WordUp.DataSelection.Planungen, RequestedStatistic);
List<Object> ResultProjekt = CreateOutStatusTaggedGenderPDF
(WordUp.DataSelection.Projekte, RequestedStatistic);
List<Object> ResultRealisierung = CreateOutStatusTaggedGenderPDF
(WordUp.DataSelection.Realisierungen, RequestedStatistic);
return CombineStatisticResults(ResultIdeen,
ResultWuensche, ResultPlanung, ResultProjekt, ResultRealisierung);
}
}
break;
case DataWrapper.Statistics.GenderedWertigkeiten:
{
}
break;
case DataWrapper.Statistics.GenderedAusschliessungen:
{
}
break;
}
return Result;
}
public AGCounter(ArbeitsGruppe arbeitsGruppe, ZustaendigAgOrtsTeilOrt zustaendigAgOrtsTeilOrtParent)
: base (arbeitsGruppe)
{
Parent = zustaendigAgOrtsTeilOrtParent;
}
