/// <summary>
/// Construct a new <see cref="Table" />.
/// </summary>
/// <param name="tableName">
/// The name of the <see cref="Table" />.
/// </param>
/// <param name="headerAlignment">
/// The method to use to align the text in the table's header.
/// Defaults to <see cref="StringFormatting.LeftJustified" />.
/// </param>
public Table(string tableName=null, Alignment headerAlignment=null)
{
Title = tableName ?? string.Empty;
HeaderAlignment = headerAlignment ?? StringFormatting.LeftJustified;
Columns = new ColumnCollection();
Rows = new RowCollection(this);
}
private static void AddColumnsToCheckList(CheckedListBox checkList, RowCollection rowCollection)
{
foreach (var columnHeader in rowCollection.GetColumnHeaders())
{
checkList.Items.Add(columnHeader);
}
}
public void ReadXml(XmlReader reader)
{
var xml = new XmlHelper(reader);
PersonalContacts = xml.deserializeRowSet<Contact>("contactList");
CorporationContacts = xml.deserializeRowSet<Contact>("corporateContactList");
AllianceContacts = xml.deserializeRowSet<Contact>("allianceContactList");
}
internal GridPanel(C1FlexGrid grid, CellType cellType, int rowHeight, int colWidth)
{
Grid = grid;
CellType = cellType;
_cells = new CellRangeDictionary();
ViewRange = CellRange.Empty;
Rows = new RowCollection(this, rowHeight);
Columns = new ColumnCollection(this, colWidth);
HorizontalAlignment = HorizontalAlignment.Left;
VerticalAlignment = VerticalAlignment.Top;
}
public Table(string sheetName, int columns, int rows)
{
ArgumentUtility.EnsureNotNull(sheetName, "sheetName");
ArgumentUtility.EnsureCondition(columns, c => c > 0, "columns");
ArgumentUtility.EnsureCondition(rows, r => r > 0, "rows");
Rows = new RowCollection(columns);
for (int i = 0; i < rows; i++)
{
Rows.AddAndInitNewRow();
}
SheetName = sheetName;
}
public void Then_returns_expected_Headers()
{
var rowCollection = new RowCollection(new List<Row>
{
new Row
{
new Column("Test", "Test"),
new Column("Foo", "Bar"),
new Column("Abc", "Def")
}
});
var expectedHeaders = new List<string> { "Test", "Foo", "Abc" };
rowCollection.GetColumnHeaders().ShouldAllBeEquivalentTo(expectedHeaders);
}
/// <summary>
/// Edits the value of the specified object using the specified
/// service provider and context
/// </summary>
/// <param name="context">An ITypeDescriptorContext that can be
/// used to gain additional context information</param>
/// <param name="isp">A service provider object through which
/// editing services can be obtained</param>
/// <param name="value">The object to edit the value of</param>
/// <returns>The new value of the object. If the value of the
/// object has not changed, this should return the same object
/// it was passed</returns>
public override object EditValue(ITypeDescriptorContext context, IServiceProvider isp, object value)
{
this.rows = (RowCollection) value;
object returnObject = base.EditValue(context, isp, value);
TableModel model = (TableModel) context.Instance;
// make sure the TableModel's Table redraws any additions/deletions
if (model.Table != null)
{
model.Table.PerformLayout();
model.Table.Refresh();
}
return returnObject;
}
public SelectColumnsForm(ComparerArguments comparerArguments)
{
InitializeComponent();
using (var sourceReader = FileReaderFactory.CreateFromFileName(new FileInfoWrapper(new FileInfo(comparerArguments.SourceFilePath))))
{
_sourceRowCollection = sourceReader.Read();
}
using (var targetReader = FileReaderFactory.CreateFromFileName(new FileInfoWrapper(new FileInfo(comparerArguments.TargetFilePath))))
{
_targetRowCollection = targetReader.Read();
}
AddColumnsToCheckList(SourceColumnsCheckList, _sourceRowCollection);
AddColumnsToCheckList(TargetColumnsCheckList, _targetRowCollection);
}
public Table()
{
//ExpandedColumnCount = 1;
//ExpandedRowCount = 1;
FullColumns = 1;
FullRows = 1;
DefaultColumnWidth = 54.0;
DefaultRowHeight = 13.5;
Rows = new RowCollection();
Columns = new ColumnCollection();
}
public void Given_source_and_target_collection_has_one_name_that_the_second_name_contains_Then_both_are_added_to_equal_rows()
{
var compareColumn = new List<string> {"Name"};
var rows = new List<Row>
{
new Row
{
new Column("Name", "Foo Bar")
},
new Row
{
new Column("Name", "Foo Barr")
}
};
var sourceCollection = new RowCollection(rows) {ColumnHeadersToCompare = compareColumn};
var targetCollection = new RowCollection(rows) {ColumnHeadersToCompare = compareColumn};
var comparisonResult = new RowCollectionComparer(sourceCollection, targetCollection).GetCollectionComparisonResult();
comparisonResult.EqualRows.ShouldAllBeEquivalentTo(rows);
}
internal Worksheet(Workbook wb, BoundSheetRecord sheet, SortedList<long, Biff> records)
: base(wb)
{
_name = sheet.Name;
int idx = records.IndexOfKey((long)sheet.BofPos);
_hyperlinks = new HyperLinkCollection(wb);
for (int i = idx + 1; i < records.Count; ++i)
{
Biff biff = records.Values[i];
if (biff is HyperLinkRecord)
_hyperlinks.Add((HyperLinkRecord)biff);
else if (biff is EofRecord)
break;
}
BofRecord bof = (BofRecord)records.Values[idx++];
Biff seeker = records.Values[idx++];
while (!(seeker is IndexRecord))
seeker = records.Values[idx++];
IndexRecord index = (IndexRecord)seeker;
_rows = new RowCollection(wb);
foreach (uint indexPos in index.Rows)
{
long dbCellPos = indexPos;
int dbCellIdx = records.IndexOfKey(dbCellPos);
DbCellRecord dbCell = (DbCellRecord)records[dbCellPos];
if (dbCell.RowOffset > 0)
{
long rowPos = dbCellPos - dbCell.RowOffset;
int recIndex = records.IndexOfKey(rowPos);
Debug.Assert(recIndex != -1);
Biff record = records.Values[recIndex++];
while (record is RowRecord)
{
RowRecord row = (RowRecord)record;
Row currentRow = new Row(Workbook, row);
_rows.Add(row.RowNumber, currentRow);
record = records.Values[recIndex++];
}
while (recIndex <= dbCellIdx)
{
if (!(record is CellRecord))
{
record = records.Values[recIndex++];
continue;
}
CellRecord thecell = (CellRecord)record;
Row currentRow = _rows[thecell.Row];
if (thecell is SingleColCellRecord)
{
SingleColCellRecord cell = (SingleColCellRecord)thecell;
object val = cell.Value;
Cell newCell = new Cell(Workbook, val);
if (cell is RowColXfCellRecord)
{
RowColXfCellRecord xfCell = (RowColXfCellRecord)cell;
Style style = Workbook.Styles[xfCell.Xf];
Debug.Assert(style != null);
newCell.Style = style;
}
currentRow.Cells.Add((byte)cell.Col, newCell);
}
else
{
MultipleColCellRecord cells = (MultipleColCellRecord)thecell;
for (ushort i = cells.FirstCol; i <= cells.LastCol; ++i)
{
object val = cells.GetValue(i);
if (val != null)
{
Cell newCell = null;
if (val is RkRec)
{
RkRec rk = (RkRec)val;
newCell = new Cell(Workbook, rk.Value);
Style style = Workbook.Styles[rk.Xf];
Debug.Assert(style != null);
newCell.Style = style;
}
else
newCell = new Cell(Workbook, val);
currentRow.Cells.Add((byte)i, newCell);
}
}
}
record = records.Values[recIndex++];
}
}
}
}
public GridCell() : base(UITableViewCellStyle.Default, ListView.CellId.ToString())
{
Columns = new ColumnCollection();
Rows = new RowCollection();
}
private static RowCollection CloneRows(GridPanel panel)
{
RowCollection rows = new RowCollection(panel, (int) panel.Rows.DefaultSize);
using (rows.DeferNotifications())
{
foreach (Row row in panel.Rows)
{
rows.Add(row);
row.List = panel.Rows;
}
}
rows.MinSize = panel.Rows.MinSize;
return rows;
}
public Iterator(RowCollection collection)
{
this.collection = collection;
version = collection.version;
index = -1;
}
private void Initialize(IAttributeSet attrs = null)
{
MaxHeight = double.PositiveInfinity;
Columns = new ColumnCollection();
Rows = new RowCollection();
ChildViewAdded -= GridBase_ChildViewAdded;
ChildViewAdded += GridBase_ChildViewAdded;
ChildViewRemoved -= GridBase_ChildViewRemoved;
ChildViewRemoved += GridBase_ChildViewRemoved;
if (attrs == null)
{
return;
}
var rowCountString = attrs.GetAttributeValue(ElementExtensions.XmlNamespace, "rows");
var rows = rowCountString?.Split(',');
if (rows?.Length == 1 && int.TryParse(rows[0], out int rowCount))
{
this.SetRows(rowCount);
}
else if (rows?.Length > 0)
{
foreach (var row in rows)
{
if (row.Contains('*'))
{
double.TryParse(row.Replace("*", string.Empty), out double height);
Rows.Add(new Row(height == 0 ? 1 : height, LayoutUnitType.Star));
}
else if (row.ToUpper().StartsWith("A"))
{
Rows.Add(Row.AutoSized);
}
else
{
var height = double.Parse(row);
Rows.Add(new Row(height, LayoutUnitType.Absolute));
}
}
}
var columns = attrs.GetAttributeValue(ElementExtensions.XmlNamespace, "columns")?.Split(',');
if (columns?.Length == 1 && int.TryParse(columns[0], out int columnCount))
{
this.SetColumns(columnCount);
}
else if (columns?.Length > 0)
{
foreach (var column in columns)
{
if (column.Contains('*'))
{
double.TryParse(column.Replace("*", string.Empty), out double width);
Columns.Add(new Column(width == 0 ? 1 : width, LayoutUnitType.Star));
}
else if (column.ToUpper().StartsWith("A"))
{
Columns.Add(Column.AutoSized);
}
else
{
var width = double.Parse(column);
Columns.Add(new Column(width, LayoutUnitType.Absolute));
}
}
}
string padding = attrs.GetAttributeValue(ElementExtensions.XmlNamespace, "padding");
if (!string.IsNullOrEmpty(padding))
{
var padValues = padding.Split(',').Select(p => p.TryParseDouble()).ToList();
switch (padValues.Count)
{
case 1:
Padding = new Thickness(padValues[0]);
break;
case 2:
Padding = new Thickness(padValues[0], padValues[1]);
break;
case 4:
Padding = new Thickness(padValues[0], padValues[1], padValues[2], padValues[3]);
break;
default:
throw new FormatException("Invalid padding format: " + padding);
}
}
var left = ElementExtensions.ParseThickness(attrs, "paddingLeft", (int)Padding.Left);
var top = ElementExtensions.ParseThickness(attrs, "paddingTop", (int)Padding.Top);
var right = ElementExtensions.ParseThickness(attrs, "paddingRight", (int)Padding.Right);
var bottom = ElementExtensions.ParseThickness(attrs, "paddingBottom", (int)Padding.Bottom);
Padding = new Thickness(left, top, right, bottom);
}
public void Dispose()
{
RowCollection.RemoveRow(this);
}
public virtual void VisitRowCollection(RowCollection coll)
{
}
/// <summary>
/// Initializes a new instance of the RowCollectionEditor class
/// using the specified collection type
/// </summary>
/// <param name="type">The type of the collection for this editor to edit</param>
public RowCollectionEditor(Type type) : base(type)
{
this.rows = null;
}
public void DumpToExcel([NotNull] string dstPath, XlsResultOutputMode mode)
{
RowCollection rc = new RowCollection("GeneratedLoadProfiles", "GeneratedLoadProfiles");
if (mode == XlsResultOutputMode.ByTrafoStationTree)
{
var trafostationen = Houses.SelectMany(x => x.HausAnschlussList).Select(y => y.Trafokreis).Distinct().ToList();
foreach (var trafostation in trafostationen)
{
rc.Add(RowBuilder.Start("Trafostation", trafostation));
foreach (var houseRo in Houses)
{
if (houseRo.HausAnschlussList.Any(x => x.Trafokreis == trafostation))
{
rc.Add(houseRo.ToRowBuilder());
foreach (HausAnschlussRo hausAnschlussRo in houseRo.HausAnschlussList)
{
if (hausAnschlussRo.Trafokreis == trafostation)
{
rc.Add(hausAnschlussRo.ToRowBuilder(houseRo, mode));
foreach (HouseComponentRo component in hausAnschlussRo.HouseComponents)
{
rc.Add(component.ToRowBuilder(houseRo, hausAnschlussRo, mode));
}
}
}
}
}
}
}
else if (mode == XlsResultOutputMode.ByTrafoStationHausanschlussTree)
{
var trafostationen = Houses.SelectMany(x => x.HausAnschlussList).Select(y => y.Trafokreis).Distinct().ToList();
var haros = Houses.SelectMany(x => x.HausAnschlussList).Distinct().ToList();
haros.Sort((x, y) => String.Compare(x.ObjektID, y.ObjektID, StringComparison.Ordinal));
foreach (var trafostation in trafostationen)
{
rc.Add(RowBuilder.Start("Trafostation", trafostation));
var filteredHaros = haros.Where(x => x.Trafokreis == trafostation);
foreach (var anschlussRo in filteredHaros)
{
rc.Add(anschlussRo.ToRowBuilder(null, XlsResultOutputMode.ByTrafoStationHausanschlussTree));
var housesForAnschluss = Houses.Where(x => x.HausAnschlussList.Contains(anschlussRo)).ToList();
foreach (var houseRo in housesForAnschluss)
{
rc.Add(houseRo.ToRowBuilder());
foreach (HouseComponentRo component in anschlussRo.HouseComponents)
{
rc.Add(component.ToRowBuilder(houseRo, anschlussRo, mode));
}
}
}
}
}
else
{
foreach (HouseRo house in Houses)
{
if (mode == XlsResultOutputMode.Tree)
{
rc.Add(house.ToRowBuilder());
}
foreach (var anschlussRo in house.HausAnschlussList)
{
if (mode == XlsResultOutputMode.Tree)
{
rc.Add(anschlussRo.ToRowBuilder(house, mode));
}
foreach (var component in anschlussRo.HouseComponents)
{
rc.Add(component.ToRowBuilder(house, anschlussRo, mode));
}
}
}
}
XlsxDumper.WriteToXlsx(dstPath, rc);
}
private static void PrintStats(string path, string htmloutfile)
{
using (var thread = Load(path))
using (var writer = new CoExHtmlWriter()
{
Title = "Das Längste"
})
{
CoEx.WriteTitleLarge($"Statistics for {thread.StartpageUrl}");
CoEx.WriteLine();
CoEx.WriteLine(new string[] {
"If you don't want to appear in this statistic, ",
"just add the keyword 'donottrack' to your signature."
});
CoEx.WriteLine();
//--> Filter functions
bool filteroptoutpost(ThreadPost post)
{
return((post?.User?.SignatureHtml?.RemoveHtml().Contains("donottrack", StringComparison.InvariantCultureIgnoreCase) ?? false) == false);
}
bool filteroptoutuser(ForumUser user)
{
return((user?.SignatureHtml?.RemoveHtml().Contains("donottrack", StringComparison.InvariantCultureIgnoreCase) ?? false) == false);
}
//--> Default filters
var cleanposts = thread.Posts.Where(v => filteroptoutpost(v));
var cleanusers = thread.Users.Users
.Where(v => filteroptoutuser(v))
.Intersect(cleanposts.Select(u => u.User).Distinct());
//--> Current status
var curstats = new string[][]
{
new string[] { "JSON file:", Path.GetFileName(path) },
new string[] { "JSON file size:", (new FileInfo(path)).Length.HumanBytes() },
new string[] { "Crawl date:", thread.CrawlTimestamp.ToString("yyyy-MM-dd HH:mm:ss") },
new string[] { "Post count:", cleanposts.Count().ToString("#,###") },
new string[] { "Users involved:", cleanusers.Count().ToString("#,###") },
new string[] { "Banned users:", cleanusers.Where(u => u.IsBanned).Count().ToString("#,###") },
new string[] { "Opt-Out users:", thread.Users.Users.Where(v => filteroptoutuser(v) == false).Count().ToString("#,##0") },
new string[] { "Total likes:", cleanposts.Sum(v => v.LikeCount).ToString("#,##0") },
new string[] { "Total dislikes:", cleanposts.Sum(v => v.DislikeCount).ToString("#,##0") },
new string[] { "Posts with likes:", cleanposts.Where(v => v.LikeCount > 0).Count().ToString("#,##0") },
new string[] { "Posts with dislikes:", cleanposts.Where(v => v.DislikeCount > 0).Count().ToString("#,##0") },
new string[] { "First post:", cleanposts.Min(v => v.Date).ToString("yyyy-MM-dd HH:mm:ss") },
new string[] { "Last post:", cleanposts.Max(v => v.Date).ToString("yyyy-MM-dd HH:mm:ss") },
new string[] { "Thread age:", (cleanposts.Max(v => v.Date) - cleanposts.Min(v => v.Date)).PrettyPrint() },
new string[] { "First user:"******"{v.Key.Username} ({v.Count()})").First() },
new string[] { "Newest user:"******"{v.Key.Username} ({v.Count()})").First() }
};
CoEx.WriteTitle("Current status");
CoEx.WriteTable(RowCollection.Create(curstats));
CoEx.WriteLine();
//--> User statistics
var userstats = cleanposts
.Where(v => v.User != null)
.GroupBy(v => v.User)
.OrderByDescending(v => v.Count())
.Take(30)
.Select(v => new string[]
{
v.Key.Username,
v.Key.PostCount.ToString("#,###"),
v.Count().ToString("#,###"),
(100.0 * v.Count() / v.Key.PostCount).ToString("0.0") + "%",
(100.0 * v.Count() / cleanposts.Count()).ToString("0.0") + "%",
v.Min(j => j.Date).ToString("yyyy-MM-dd HH:mm"),
v.Max(j => j.Date).ToString("yyyy-MM-dd HH:mm"),
v.Key.MemberSince.ToString("yyyy-MM-dd")
})
.Prepend(new string[]
{
"Username",
"All Posts",
"Thread Posts",
"User %",
"Thread %",
"First Post",
"Last Post",
"Member since"
});
var userrows = RowCollection.Create(userstats.ToArray());
userrows.Settings.Border.Enabled = true;
userrows.Settings.Align = (conf, colidx, s) =>
{
switch (colidx)
{
case 1:
case 2:
case 3:
case 4:
return(RowCollectionSettings.ALIGN.RIGHT);
default:
return(RowCollectionSettings.ALIGN.LEFT);
}
};
CoEx.WriteTitle("Statistics by user");
CoEx.WriteTable(userrows);
CoEx.WriteLine();
//--> User like statistics
var userlikes = cleanposts
.Where(v => v.User != null)
.GroupBy(v => v.User)
.Where(v => v.Sum(j => j.LikeCount) > 0)
.OrderByDescending(v => v.Sum(j => j.LikeCount))
.Take(10)
.Select(v => new string[]
{
v.Key.Username,
v.Sum(j => j.LikeCount).ToString("#,##0"),
v.Where(j => j.LikeCount > 0).Min(j => j.Date).ToString("yyyy-MM-dd HH:mm"),
v.Where(j => j.LikeCount > 0).Max(j => j.Date).ToString("yyyy-MM-dd HH:mm")
})
.Prepend(new string[]
{
"Username",
"Likes",
"First like received",
"Last like received"
});
var userlikerows = RowCollection.Create(userlikes.ToArray());
userlikerows.Settings.Border.Enabled = true;
userlikerows.Settings.Align = (conf, colidx, s) =>
{
switch (colidx)
{
case 1:
return(RowCollectionSettings.ALIGN.RIGHT);
default:
return(RowCollectionSettings.ALIGN.LEFT);
}
};
CoEx.WriteTitle("Statistics by likes");
CoEx.WriteTable(userlikerows);
CoEx.WriteLine();
//--> User title statistics
var titlestats = cleanposts
.Where(v => v.User != null)
.GroupBy(v => v.User.Title)
.OrderByDescending(v => v.Count())
.Select(v => new string[]
{
v.Key,
v.Count().ToString("#,###"),
v.Min(j => j.Date).ToString("yyyy-MM-dd HH:mm"),
v.Max(j => j.Date).ToString("yyyy-MM-dd HH:mm")
})
.Prepend(new string[]
{
"Title",
"Posts",
"First Post",
"Last Post"
});
var titlerows = RowCollection.Create(titlestats.ToArray());
titlerows.Settings.Border.Enabled = true;
titlerows.Settings.Align = (conf, colidx, s) =>
{
switch (colidx)
{
case 1:
return(RowCollectionSettings.ALIGN.RIGHT);
default:
return(RowCollectionSettings.ALIGN.LEFT);
}
};
CoEx.WriteTitle("Statistics by user title");
CoEx.WriteTable(titlerows);
CoEx.WriteLine();
//--> Post likes
var likeposts = cleanposts
.Where(v => v.User != null)
.OrderByDescending(v => v.LikeCount)
.Take(10)
.Select(v => new string[]
{
v.Date.ToString("yyyy-MM-dd"),
v.LikeCount.ToString("#,###"),
v.User.Username,
v.Url
})
.Prepend(new string[]
{
"Date",
"Likes",
"Author",
"Post Url"
});
var likepostrows = RowCollection.Create(likeposts.ToArray());
likepostrows.Settings.Border.Enabled = true;
likepostrows.Settings.Align = (conf, colidx, s) =>
{
switch (colidx)
{
case 1:
return(RowCollectionSettings.ALIGN.RIGHT);
default:
return(RowCollectionSettings.ALIGN.LEFT);
}
};
CoEx.WriteTitle("Most liked posts");
CoEx.WriteTable(likepostrows);
CoEx.WriteLine();
//--> Year statistics
var yearmaxlength = cleanposts
.GroupBy(v => v.Date.Year)
.Select(v => v.Count().ToString().Length)
.Max();
var yearstats = cleanposts
.GroupBy(v => v.Date.Year)
.Select(v => new string[]
{
v.Key.ToString(),
v.Min(j => j.Date).ToString("MM-dd HH:mm"),
v.Max(j => j.Date).ToString("MM-dd HH:mm"),
v.GroupBy(j => j.User).Count().ToString("#,###"),
v.Count().ToString("#,###"),
string.Join(", ", v.GroupBy(j => j.User).OrderByDescending(j => j.Count()).Take(3).Select(j => $"{j.Key?.Username??"Guest"} ({j.Count()})")),
v.Max(j => j.MessageHtml.RemoveHtml().Length).ToString("#,###")
})
.Prepend(new string[]
{
"Year",
"First Post",
"Last Post",
"Active",
"Posts",
"Top Users",
"Largest Msg"
});
var yeargraphstats = cleanposts
.GroupBy(v => v.Date.Year)
.Select(v => new dynamic[]
{
v.Key.ToString(),
v.Count(),
})
.OrderByDescending(v => (string)v[0])
.Take((int)((CoEx.ForcedBufferWidth.Value - yearmaxlength - 4) / 2))
.OrderBy(v => (string)v[0])
.ToDictionary(key => (string)key[0], value => (double)value[1]);
var yearrows = RowCollection.Create(yearstats.ToArray());
yearrows.Settings.Border.Enabled = true;
yearrows.Settings.Align = (conf, colidx, s) =>
{
switch (colidx)
{
case 3:
case 4:
case 6:
return(RowCollectionSettings.ALIGN.RIGHT);
default:
return(RowCollectionSettings.ALIGN.LEFT);
}
};
CoEx.WriteTitle("Statistics by year");
var graph = new SimpleGraph()
{
Height = 15
};
graph.Draw(yeargraphstats);
CoEx.WriteLine();
CoEx.WriteTable(yearrows);
CoEx.WriteLine();
//--> Month statistics
var monthstats = cleanposts
.GroupBy(v => v.Date.GetMonth())
.Select(v => new string[]
{
v.Key.ToString("yyyy-MM"),
v.Min(j => j.Date).ToString("dd HH:mm"),
v.Max(j => j.Date).ToString("dd HH:mm"),
v.GroupBy(j => j.User).Count().ToString("#,###"),
v.Count().ToString("#,###"),
string.Join(", ", v.GroupBy(j => j.User).OrderByDescending(j => j.Count()).Take(3).Select(j => $"{j.Key?.Username??"Guest"} ({j.Count()})")),
v.Max(j => j.MessageHtml.RemoveHtml().Length).ToString("#,###")
})
.Prepend(new string[]
{
"Month",
"First Post",
"Last Post",
"Active",
"Posts",
"Top Users",
"Largest Msg"
});
var monthmaxlength = cleanposts
.GroupBy(v => v.Date.GetMonth())
.Select(v => v.Count().ToString().Length)
.Max();
var monthgraphstats = cleanposts
.GroupBy(v => v.Date.GetMonth())
.Select(v => new dynamic[]
{
v.Key.ToString("yyMM"),
v.Count(),
})
.OrderByDescending(v => (string)v[0])
.Take((int)((CoEx.ForcedBufferWidth.Value - monthmaxlength - 4) / 2))
.OrderBy(v => (string)v[0])
.ToDictionary(key => (string)key[0], value => (double)value[1]);
var monthrows = RowCollection.Create(monthstats.ToArray());
monthrows.Settings.Border.Enabled = true;
monthrows.Settings.Align = (conf, colidx, s) =>
{
switch (colidx)
{
case 3:
case 4:
case 6:
return(RowCollectionSettings.ALIGN.RIGHT);
default:
return(RowCollectionSettings.ALIGN.LEFT);
}
};
CoEx.WriteTitle("Statistics by month");
graph.Draw(monthgraphstats);
CoEx.WriteLine();
CoEx.WriteTable(monthrows);
CoEx.WriteLine();
writer.SaveAs(htmloutfile);
}
}
public static DataTable ConvertExcelToDataTable(string filepath
, DataView dvCol
, DocumentFormat docType
, int StartRow
, int EndRow
, string StartColumn
, string KeyHID
, string KeyWord)
{
var startRow = StartRow;
Workbook workBook = new Workbook();
workBook.LoadDocument(filepath);
Worksheet worksheet = workBook.Worksheets[0];
Range rangeAll = worksheet.GetDataRange();
string lastHeader = worksheet.Columns[rangeAll.ColumnCount - 1].Heading;
int columnIndex = worksheet.Columns[StartColumn].Index + 1;
int tempColumn = 0;
Range range = null;
if (EndRow > 0)
{
range = worksheet.Range.Parse(string.Format("{0}{1}:{3}{2}", StartColumn, startRow, startRow, lastHeader));
}
else
{
range = worksheet.Range.Parse(string.Format("{0}{1}:{2}{1}", StartColumn, startRow, lastHeader));
}
DataTable dataTable = new DataTable();
if (dvCol != null && dvCol.Count > 0)
{
dvCol.Sort = "No asc";
foreach (DataRowView dr in dvCol)
{
dataTable.Columns.Add(dr["ColumnName"].ToString());
}
}
// dataTable.Columns.Add("DID");
foreach (DataColumn dc in dataTable.Columns)
{
dc.DataType = typeof(String);
}
RowCollection rowAll = worksheet.Rows;
int colNumber = range.CurrentRegion.ColumnCount - (columnIndex - 1);
DataRow newRow = null;
switch (KeyWord.ToLower())
{
case "outgoing":
#region outgoing case
dataTable = ConvertInOut(dataTable, dvCol, worksheet, startRow, "{2:I");
#endregion
break;
case "incoming":
dataTable = ConvertInOut(dataTable, dvCol, worksheet, startRow, "{2:O");
break;
default:
#region normal case
for (int i = startRow; i <= rangeAll.RowCount - 1; i++)
{
newRow = dataTable.NewRow();
tempColumn = columnIndex;
// Row row= rowAll[i];
foreach (DataRowView dr in dvCol)
{
newRow[dr["ColumnName"].ToString()] = worksheet.Cells[i, Utility.ConvertToInt(dr["ColumnNo"].ToString())].DisplayText;
tempColumn++;
}
dataTable.Rows.Add(newRow);
}
#endregion
break;
}
worksheet = null;
workBook.Dispose();
return(dataTable);
}
private string _ExportToExcel(ConfigurationKpiAchievementsViewModel viewModel)
{
string dateFormat = "dd-mmm-yy";
string workSheetName = new StringBuilder(viewModel.PeriodeType).ToString();
switch (viewModel.PeriodeType)
{
case "Yearly":
dateFormat = "yyyy";
break;
case "Monthly":
dateFormat = "mmm-yy";
workSheetName = string.Format("{0}_{1}", workSheetName, viewModel.Year);
break;
default:
dateFormat = "dd-mmm-yy";
workSheetName = string.Format("{0}_{1}-{2}", workSheetName, viewModel.Year, viewModel.Month.ToString().PadLeft(2, '0'));
break;
}
string fileName = new StringBuilder(workSheetName).Append(".xls").ToString();
var path = System.Web.HttpContext.Current.Request.MapPath(TemplateDirectory + "/KpiAchievement/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string resultFilePath = System.Web.HttpContext.Current.Request.MapPath(string.Format("{0}/KpiAchievement/{1}", TemplateDirectory, fileName));
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Name = workSheetName;
workbook.Worksheets.ActiveWorksheet = worksheet;
RowCollection rows = workbook.Worksheets[0].Rows;
ColumnCollection columns = workbook.Worksheets[0].Columns;
Row HeaderRow = rows[0];
HeaderRow.FillColor = Color.DarkGray;
HeaderRow.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
HeaderRow.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
Column KpiIdColumn = columns[0];
Column KpiNameColumn = columns[1];
KpiIdColumn.Visible = false;
HeaderRow.Worksheet.Cells[HeaderRow.Index, KpiIdColumn.Index].Value = "KPI ID";
HeaderRow.Worksheet.Cells[HeaderRow.Index, KpiNameColumn.Index].Value = "KPI Name";
int i = 1; //i for row
foreach (var kpi in viewModel.Kpis)
{
worksheet.Cells[i, KpiIdColumn.Index].Value = kpi.Id;
worksheet.Cells[i, KpiNameColumn.Index].Value = string.Format("{0} ({1})", kpi.Name, kpi.Measurement);
int j = 2; // for column
foreach (var achievement in kpi.KpiAchievements)
{
worksheet.Cells[HeaderRow.Index, j].Value = achievement.Periode;
worksheet.Cells[HeaderRow.Index, j].NumberFormat = dateFormat;
worksheet.Cells[HeaderRow.Index, j].AutoFitColumns();
worksheet.Cells[i, j].Value = achievement.Value;
worksheet.Cells[i, j].NumberFormat = "#,0.#0";
worksheet.Columns[j].AutoFitColumns();
j++;
}
Column TotalValueColumn = worksheet.Columns[j];
if (i == HeaderRow.Index + 1)
{
worksheet.Cells[HeaderRow.Index, TotalValueColumn.Index].Value = "Average";
worksheet.Cells[HeaderRow.Index, TotalValueColumn.Index + 1].Value = "SUM";
Range r1 = worksheet.Range.FromLTRB(KpiNameColumn.Index + 1, i, j - 1, i);
worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r1.GetReferenceA1());
worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r1.GetReferenceA1());
}
else
{
// add formula
Range r2 = worksheet.Range.FromLTRB(KpiNameColumn.Index + 1, i, j - 1, i);
worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r2.GetReferenceA1());
worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r2.GetReferenceA1());
}
i++;
}
KpiNameColumn.AutoFitColumns();
worksheet.FreezePanes(HeaderRow.Index, KpiNameColumn.Index);
using (FileStream stream = new FileStream(resultFilePath, FileMode.Create, FileAccess.ReadWrite))
{
workbook.SaveDocument(stream, DevExpress.Spreadsheet.DocumentFormat.Xlsx);
stream.Close();
}
//workbook.SaveDocument(resultFilePath, DocumentFormat.OpenXml);
//todo create file from viewModel
return(string.Format("{0}KpiAchievement/{1}", TemplateDirectory, fileName));
}
public void ReadXml(XmlReader reader)
{
var xml = new XmlHelper(reader);
CharacterId = xml.getLong("characterID");
Name = xml.getString("name");
DateOfBirthAsString = xml.getString("DoB");
Race = xml.getString("race");
Bloodline = xml.getString("bloodLine");
Ancestry = xml.getString("ancestry");
Gender = xml.getString("gender");
CorporationName = xml.getString("corporationName");
CorporationId = xml.getLong("corporationID");
AllianceName = xml.getString("allianceName");
AllianceId = xml.getLong("allianceID");
CloneName = xml.getString("cloneName");
CloneSkillPoints = xml.getInt("cloneSkillPoints");
Balance = xml.getDecimal("balance");
AttributeEnhancers = xml.deserialize<Implants>("attributeEnhancers");
Skills = xml.deserializeRowSet<Skill>("skills");
Certificates = xml.deserializeRowSet<Certificate>("certificates");
CorporationRoles = xml.deserializeRowSet<Role>("corporationRoles");
CorporationRolesAtHq = xml.deserializeRowSet<Role>("corporationRolesAtHQ");
CorporationRolesAtBase = xml.deserializeRowSet<Role>("corporationRolesAtBase");
CorporationRolesAtOther = xml.deserializeRowSet<Role>("corporationRolesAtOther");
CorporationTitles = xml.deserializeRowSet<Title>("corporationTitles");
}
protected override void RunActualProcess([NotNull] ScenarioSliceParameters parameters)
{
Services.SqlConnection.RecreateTable <HausAnschlussExportEntry>(Stage.ProfileExport, parameters);
MyDb dbProfileExport = PrepareProfileExport(parameters);
//prepare profile export
//load previous data
var dbProfileGeneration = LoadPreviousData(parameters,
out var houses, out var households,
out var businesses);
//trafokreise
var trafokreise = houses.SelectMany(x => x.Hausanschluss).Select(x => x.Trafokreis).Distinct().ToList();
//int trafokreiscount = 1;
dbProfileGeneration.Database.BeginTransaction();
RowCollection rc = new RowCollection();
foreach (var trafokreis in trafokreise)
{
//prepare
if (string.IsNullOrWhiteSpace(trafokreis))
{
continue;
}
// if (trafokreiscount > 3) {
// continue;
//}
// trafokreiscount++;
//merge
var houseExportEntries = MergeAllEntriesForTrafokreis(houses, dbProfileGeneration, trafokreis,
households, businesses);
//validate
CheckHouseExportEntryIfAllHouseholdsAreCovered(households, houseExportEntries);
//save
var saLoad = Prosumer.GetSaveableEntry(dbProfileExport, TableType.HouseLoad);
var saGeneration = Prosumer.GetSaveableEntry(dbProfileExport, TableType.HouseGeneration);
foreach (var houseExportEntry in houseExportEntries)
{
var rb = RowBuilder.Start("Trafokreis", trafokreis);
//load
var ps = new Prosumer(houseExportEntry.HouseGuid,
houseExportEntry.HouseName, ProsumerType.HouseLoad,
houseExportEntry.HouseGuid, houseExportEntry.Isn, houseExportEntry.Trafokreis,
houseExportEntry.GetSumProfile(HausAnschlussExportEntry.TypeOfSum.Load), houseExportEntry.HausanschlussGuid, houseExportEntry.HausAnschlussKeyString);
double profileEnergy = ps.Profile?.EnergySum() ?? throw new Exception("Invalid profile");
rb.Add("Housename", houseExportEntry.HouseName)
.Add("Load in Profile", profileEnergy).Add("Total Energy Load Planned", houseExportEntry.TotalEnergyLoad);
if (Math.Abs(profileEnergy - houseExportEntry.TotalEnergyLoad) > 1)
{
throw new Exception("Wrong energy");
}
saLoad.AddRow(ps);
//generation
if (houseExportEntry.TotalEnergyGeneration > 1)
{
var psGen = new Prosumer(houseExportEntry.HouseGuid, houseExportEntry.HouseName, ProsumerType.HouseLoad, houseExportEntry.HouseGuid, houseExportEntry.Isn,
houseExportEntry.Trafokreis, houseExportEntry.GetSumProfile(HausAnschlussExportEntry.TypeOfSum.Generation), houseExportEntry.HausanschlussGuid,
houseExportEntry.HausAnschlussKeyString);
double profileEnergyGen = psGen.Profile?.EnergySum() ?? throw new Exception("Invalid profile");
if (Math.Abs(profileEnergyGen - houseExportEntry.TotalEnergyGeneration) > 1)
{
throw new Exception("Wrong energy");
}
rb.Add("Generation Profile Energy", profileEnergyGen)
.Add("Total Energy Generation Planned", houseExportEntry.TotalEnergyGeneration);
saGeneration.AddRow(psGen);
}
dbProfileExport.Database.Save(houseExportEntry);
rc.Add(rb);
}
saLoad.SaveDictionaryToDatabase();
if (saGeneration.RowEntries.Count > 0)
{
saGeneration.SaveDictionaryToDatabase();
}
}
var fnLoad = MakeAndRegisterFullFilename("MergedStuff.xlsx", Name, "", parameters);
XlsxDumper.WriteToXlsx(rc, fnLoad, "ProsumersMerged");
}
/// <summary>
/// Sheet 의 내용을 읽어 처리힌다.
/// </summary>
/// <param name="sheet"></param>
/// <param name="itemIndex"></param>
private void GetRows(Worksheet sheet, int itemIndex)
{
string line_tp = "";
RowCollection Rows = sheet.Rows;
int LastIndex = Rows.LastUsedIndex;
for (int i = 0; i <= LastIndex; i++)
{
string str = sheet.Columns[1][i].Value.ToString();
//header에 Hscale 이 있으면 그 다움줄은 Header 정보이다.
if (sheet.Columns[1][i].Value.ToString() == "Hscale")
{
line_tp = "header";
continue;
}
//누가거리가 있으면 그 다움줄은 관로정보이다.
else if (sheet.Columns[1][i].Value.ToString() == "누가거리")
{
line_tp = "line";
if (itemIndex == 0)
{
///컬럼명 index를 지정한다.
SetLineMap(Rows[i]);
}
continue;
}
else if (sheet.Columns[1][i].Value.ToString() == "측점")
{
line_tp = "지장물";
if (지장물_map.Count <= 0)
{
SetXMap(Rows[i]);
}
continue;
}
else if (sheet.Columns[1][i].Value.ToString() == "포장측점")
{
line_tp = "포장측점";
if (구간_map.Count <= 0)
{
Set구간Map(Rows[i]);
}
continue;
}
/// 데이터를 읽어 DATA TABLE에 추가한다.
if (line_tp == "header")
{
}
else if (line_tp == "line")
{
AddLineData(Rows[i]);
}
else if (line_tp == "지장물")
{
AddXData(Rows[i]);
}
else if (line_tp == "포장측점")
{
AddGData(Rows[i]);
}
}
///sheet 내용을 읽어서 maxer Data Table을 구성한다.
foreach (DataRow item in m_dt_line_tmp.Rows)
{
///맨홀이고 맨홀이름이 있을때만 동작한다.
if (item["manhole_tp"].ToString() == "P" &&
item["맨홀"].ToString().Trim() != ""
)
{
DataRow dr = m_dt_maxer_tmp.NewRow();
if (item["맨홀"].ToString().Trim() == "")
{
continue;
}
dr["관로"] = item["맨홀"].ToString();
dr["관로_key"] = item["key"].ToString();
try
{
m_dt_maxer_tmp.Rows.Add(dr);
}
catch (ConstraintException e)
{
continue;
}
}
}
foreach (DataRow maxer_item in m_dt_maxer_tmp.Rows)
{
foreach (DataRow line_item in m_dt_line_tmp.Rows)
{
if (Convert.ToUInt32(maxer_item["관로_key"].ToString()) < Convert.ToUInt32(line_item["key"].ToString()) &&
line_item["manhole_tp"].ToString() == "P" &&
maxer_item["관로"].ToString() != line_item["맨홀"].ToString() &&
line_item["맨홀"].ToString().Trim() != ""
)
{
maxer_item["다음관로"] = line_item["맨홀"].ToString();
//maxer_item["연장"] = GetExtention(m_dt_line_tmp, ref maxer_item);
DataRow tmpRow = GetExtention(m_dt_line_tmp, maxer_item);
maxer_item["연장"] = tmpRow["연장"];
maxer_item["관경"] = tmpRow["관경"];
maxer_item["상류지반고"] = tmpRow["상류지반고"];
maxer_item["하류지반고"] = tmpRow["하류지반고"];
maxer_item["상류관저고"] = tmpRow["상류관저고"];
maxer_item["하류관저고"] = tmpRow["하류관저고"];
maxer_item["시작거리"] = tmpRow["시작거리"];
maxer_item["끝거리"] = tmpRow["끝거리"];
maxer_item["포장"] = tmpRow["포장"];
break;
}
}
}
///m_dt_maxer_tmp에 관로는 있지만 다음관로가 없다면
///마지막으로 인지하고 삭제한다.
///
if (m_dt_maxer_tmp.Rows[m_dt_maxer_tmp.Rows.Count - 1]["다음관로"].ToString().Trim() == "")
{
m_dt_maxer_tmp.Rows[m_dt_maxer_tmp.Rows.Count - 1].Delete();
}
///지장물 정보를 막서 데이터에 추가한다.
///
foreach (DataRow maxer_item in m_dt_maxer_tmp.Rows)
{
DataRow tmpRow = Get지장물Data(maxer_item);
maxer_item["지장물"] = tmpRow["지장물"];
}
///구간 정보를 막서 데이터에 추가한다.
///
foreach (DataRow maxer_item in m_dt_maxer_tmp.Rows)
{
DataRow tmpRow = Get구간Data(maxer_item);
maxer_item["포장"] = tmpRow["포장"];
}
///m_dt_maxer에 추가
///
foreach (DataRow item in m_dt_maxer_tmp.Rows)
{
DataRow dr = m_dt_maxer.NewRow();
dr.ItemArray = item.ItemArray;
m_dt_maxer.Rows.Add(dr);
}
}
private void ChangeHeatingSystemsForOneType([NotNull] ScenarioSliceParameters slice,
[NotNull][ItemNotNull] List <HeatingSystemEntry> allPotentialSystemsToChange,
HeatingSystemType heatingSystemType,
double sumToSwitch,
[NotNull] RowCollection rc,
[NotNull] Dictionary <string, House> housesByGuid)
{
if (Math.Abs(sumToSwitch) < 0.1)
{
return;
}
var rb = RowBuilder.Start("Type to Change", heatingSystemType.ToString());
rc.Add(rb);
var matchingPotentialSystemsToChange =
allPotentialSystemsToChange.Where(x => x.SynthesizedHeatingSystemType == heatingSystemType).ToList();
rb.Add("Planned Sum", sumToSwitch);
rb.Add("Energy Demand before across all systems", matchingPotentialSystemsToChange.Select(x => x.OriginalHeatDemand2017).Sum());
WeightedRandomAllocator <HeatingSystemEntry> wra = new WeightedRandomAllocator <HeatingSystemEntry>(Services.Rnd, MyLogger);
var pickedHeatingSystems = wra.PickObjectUntilLimit(matchingPotentialSystemsToChange,
WeighingFunctionForSwitchingToHeatpump,
x => x.OriginalHeatDemand2017,
sumToSwitch,
false);
double changedEnergy = 0;
foreach (var pickedHeatingSystem in pickedHeatingSystems)
{
pickedHeatingSystem.Age = 0;
pickedHeatingSystem.SynthesizedHeatingSystemType = HeatingSystemType.Heatpump;
pickedHeatingSystem.ProvideProfile = true;
changedEnergy += pickedHeatingSystem.OriginalHeatDemand2017;
House house = housesByGuid[pickedHeatingSystem.HouseGuid];
var rb1 = RowBuilder.Start("House", house.ComplexName);
rb1.Add("Changed Energy", pickedHeatingSystem.EffectiveEnergyDemand);
rb1.Add("Heating System", heatingSystemType);
rc.Add(rb1);
}
rb.Add("Changed Sum", changedEnergy);
Info("Changed " + pickedHeatingSystems.Count + " from " + heatingSystemType + " to heatpump for a total of " + changedEnergy / 1_000_000 +
" gwh");
double overSubscribed = sumToSwitch - changedEnergy;
rb.Add("Oversubscribed", overSubscribed);
if (slice.DstYear != 2050)
{
if (overSubscribed > 0)
{
throw new FlaException("Problem: tried to allocate " + sumToSwitch / Constants.GWhFactor +
"gwh to heat pumps, but could only switch " + changedEnergy / Constants.GWhFactor + " gwh in the year " +
slice.DstYear + " and scenario " + slice.DstScenario + " from type " + heatingSystemType);
}
//im letzten jahr ist oversubscribe ok.
//overSubscribed.Should().BeLessOrEqualTo(0);
}
var leftoveroldSystems = matchingPotentialSystemsToChange.Where(x => x.SynthesizedHeatingSystemType == heatingSystemType).ToList();
rb.Add("Energy Demand after across all systems", leftoveroldSystems.Select(x => x.EffectiveEnergyDemand).Sum());
}
protected override void RunActualProcess([NotNull] ScenarioSliceParameters slice)
{
var dbSrcHouses = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Houses, slice.PreviousSliceNotNull);
var dbDstHouses = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Houses, slice);
dbDstHouses.RecreateTable <PvSystemEntry>();
dbDstHouses.RecreateTable <PVPotential>();
var srcHouses = dbSrcHouses.Fetch <House>();
var srcPVPotentials = dbSrcHouses.Fetch <PVPotential>();
var srcPvSystemEntries = dbSrcHouses.Fetch <PvSystemEntry>();
var hausanschlusses = dbDstHouses.Fetch <Hausanschluss>();
dbDstHouses.BeginTransaction();
foreach (var potential in srcPVPotentials)
{
potential.ID = 0;
dbDstHouses.Save(potential);
}
//copy pv systems from previous slice
HashSet <string> houseGuidsForSystemsWithPV = new HashSet <string>();
Dictionary <string, double> pvPotentialByHouseGuid = new Dictionary <string, double>();
foreach (var pvpot in srcPVPotentials)
{
if (!houseGuidsForSystemsWithPV.Contains(pvpot.HouseGuid))
{
houseGuidsForSystemsWithPV.Add(pvpot.HouseGuid);
pvPotentialByHouseGuid.Add(pvpot.HouseGuid, 0);
}
pvPotentialByHouseGuid[pvpot.HouseGuid] += pvpot.SonnendachStromErtrag;
}
var potentialhousesForPvSystems = srcHouses.Where(x => houseGuidsForSystemsWithPV.Contains(x.Guid)).ToList();
foreach (var entry in srcPvSystemEntries)
{
var toremove = potentialhousesForPvSystems.FirstOrDefault(x => x.Guid == entry.HouseGuid);
if (toremove != null)
{
potentialhousesForPvSystems.Remove(toremove);
}
if (entry.PVAreas.Count == 0)
{
throw new FlaException("No PV System areas defined.");
}
entry.ID = 0;
dbDstHouses.Save(entry);
}
var pvToInstallInkWh = slice.PvPowerToInstallInGwh * 1_000_000;
bool continueAllocation = true;
int pvSystemCount = 0;
while (pvToInstallInkWh > 0 && continueAllocation)
{
//make ranges
var rangeEntries = SetRanges(potentialhousesForPvSystems, pvPotentialByHouseGuid);
if (rangeEntries.Count == 0)
{
continueAllocation = false;
continue;
}
//randomly pick
var max = rangeEntries.Max(x => x.EndRange);
var pick = Services.Rnd.NextDouble() * max;
var rangeEntry = rangeEntries.Single(x => pick >= x.StartRange && pick <= x.EndRange);
//remove house
potentialhousesForPvSystems.Remove(rangeEntry.House);
//save pvsystementry
var pvPotenial = pvPotentialByHouseGuid[rangeEntry.House.Guid];
pvSystemCount++;
string erzeugerid = "PV-" + slice.DstYear + "-" + pvSystemCount;
var hausanschlsus = rangeEntry.House.GetHausanschlussByIsn(new List <int>(), erzeugerid, hausanschlusses, MyLogger) ??
throw new FlaException("no hausanschluss");
if (hausanschlsus.ObjectID.ToLower().Contains("leuchte"))
{
throw new FlaException("pv an leuchte in " + slice.DstYear + " " + hausanschlsus.ObjectID);
}
var pvSystemEntry = new PvSystemEntry(rangeEntry.House.Guid,
Guid.NewGuid().ToString(),
hausanschlsus.Guid,
rangeEntry.House.ComplexName,
erzeugerid,
slice.DstYear)
{
EffectiveEnergyDemand = pvPotenial
};
var areas = srcPVPotentials.Where(x => x.HouseGuid == rangeEntry.House.Guid).ToList();
foreach (var area in areas)
{
pvSystemEntry.PVAreas.Add(new PVSystemArea(area.Ausrichtung, area.Neigung, area.SonnendachStromErtrag));
}
if (pvSystemEntry.PVAreas.Count == 0)
{
throw new FlaException("No PV System areas defined.");
}
pvToInstallInkWh -= pvSystemEntry.EffectiveEnergyDemand;
pvSystemEntry.BuildYear = slice.DstYear;
dbDstHouses.Save(pvSystemEntry);
//deduct from pvtoinstall
}
dbDstHouses.CompleteTransaction();
var newPVs = dbDstHouses.FetchAsRepo <PvSystemEntry>();
RowCollection rc = new RowCollection("pv", "pv");
foreach (var pv in newPVs)
{
foreach (var area in pv.PVAreas)
{
RowBuilder rb = RowBuilder.Start("HA", pv.HausAnschlussGuid);
rc.Add(rb);
rb.Add("Azimut", area.Azimut);
rb.Add("Tilt", area.Tilt);
rb.Add("Energy", area.Energy);
}
}
var fn = MakeAndRegisterFullFilename("PVExport.xlsx", slice);
XlsxDumper.WriteToXlsx(fn, rc);
}
public virtual void TerminateRowCollection(RowCollection coll)
{
}
protected override void RunActualProcess([NotNull] ScenarioSliceParameters slice)
{
var dbSrcHouses = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Houses, slice.PreviousSliceNotNull);
var dbDstHouses = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Houses, slice);
dbDstHouses.RecreateTable <HeatingSystemEntry>();
var srcHeatingSystems = dbSrcHouses.Fetch <HeatingSystemEntry>();
var srcHouses = dbSrcHouses.Fetch <House>();
var dstHouses = dbDstHouses.FetchAsRepo <House>();
if (srcHeatingSystems.Count == 0)
{
throw new FlaException("No heating systems were found");
}
double totalHeatingDemand = srcHeatingSystems.Sum(x => x.HeatDemand);
double totalEbf = srcHouses.Sum(x => x.EnergieBezugsFläche);
double averageEnergyIntensityForNewAppartments = totalHeatingDemand / totalEbf * 0.5;
//add heating demand from new appartments
foreach (var hse in srcHeatingSystems)
{
var house = dstHouses.GetByGuid(hse.HouseGuid);
foreach (var expansion in house.Appartments)
{
var hseExpansion = hse.HeatDemands.FirstOrDefault(x => x.HouseExpansionGuid == expansion.Guid);
if (hseExpansion == null)
{
AppartmentHeatingDemand hsex = new AppartmentHeatingDemand(expansion.Guid,
expansion.EnergieBezugsFläche,
averageEnergyIntensityForNewAppartments * expansion.EnergieBezugsFläche,
slice.DstYear);
hse.HeatDemands.Add(hsex);
}
}
}
var dstHousesByGuid = dstHouses.ToDictionary(x => x.Guid, x => x);
DumpHeatingSystemsToXlsx(slice, srcHeatingSystems, dstHousesByGuid);
//building renovations
WeightedRandomAllocator <HeatingSystemEntry> wra = new WeightedRandomAllocator <HeatingSystemEntry>(Services.Rnd, MyLogger);
var potentialRenovationHeatingSystems = srcHeatingSystems
.Where(x => x.Age > 10 && x.HeatDemand > 0 && Math.Abs(x.HeatDemand - x.OriginalHeatDemand2017) < 0.1).ToList();
if (potentialRenovationHeatingSystems.Count == 0)
{
throw new FlaException("Not a single heating system found");
}
double averageRenovationFactor = slice.AverageHouseRenovationFactor;
if (Math.Abs(averageRenovationFactor) < 0.000001)
{
throw new FlaException("Renovation factor was 0 for scenario " + slice);
}
int numberOfBuildingRenovations = (int)(slice.RenovationRatePercentage * srcHouses.Count);
Info("renovating houses, target number " + numberOfBuildingRenovations + " GWh, renovation factor " + averageRenovationFactor);
bool failOnOversubscribe = slice.DstYear != 2050;
var systemsToRenovate = wra.PickNumberOfObjects(potentialRenovationHeatingSystems,
WeighingFunctionForRenovation,
numberOfBuildingRenovations,
failOnOversubscribe);
Info("Renovating " + systemsToRenovate.Count + " houses with a total heat demand of " + systemsToRenovate.Sum(x => x.HeatDemand));
foreach (var entry in systemsToRenovate)
{
entry.RenovateAllHeatDemand(averageRenovationFactor);
}
//change heating systems
var changeableHeatingSystems = srcHeatingSystems.ToList();
int elapsedTime = slice.DstYear - slice.PreviousSliceNotNull.DstYear;
foreach (var heatingSystemEntry in srcHeatingSystems)
{
heatingSystemEntry.Age += elapsedTime;
}
int yearsToAge = slice.DstYear - slice.PreviousSliceNotNull.DstYear;
RowCollection rc = new RowCollection("Changes", "Changes");
double totalOilHeatDemand2017 = changeableHeatingSystems.Where(x => x.HeatingSystemType2017 == HeatingSystemType.Öl)
.Sum(x => x.OriginalHeatDemand2017);
double oilenergyAmountToChange = totalOilHeatDemand2017 * slice.Energy2017PercentageFromOilToHeatpump;
ChangeHeatingSystemsForOneType(slice, changeableHeatingSystems, HeatingSystemType.Öl, oilenergyAmountToChange, rc, dstHousesByGuid);
double totalGasHeatDemand2017 = changeableHeatingSystems.Where(x => x.HeatingSystemType2017 == HeatingSystemType.Gas)
.Sum(x => x.OriginalHeatDemand2017);
double gasEnergyAmountToChange = totalGasHeatDemand2017 * slice.Energy2017PercentageFromGasToHeatpump;
ChangeHeatingSystemsForOneType(slice, changeableHeatingSystems, HeatingSystemType.Gas, gasEnergyAmountToChange, rc, dstHousesByGuid);
double totalOtherHeatDemand = changeableHeatingSystems.Where(x => x.HeatingSystemType2017 == HeatingSystemType.Other)
.Sum(x => x.OriginalHeatDemand2017);
double otherDemandToChange = slice.Energy2017PercentageFromOtherToHeatpump * totalOtherHeatDemand;
ChangeHeatingSystemsForOneType(slice, changeableHeatingSystems, HeatingSystemType.Other, otherDemandToChange, rc, dstHousesByGuid);
var fn = MakeAndRegisterFullFilename("HeatingChangeLog.xlsx", slice);
if (rc.Rows.Count > 0)
{
XlsxDumper.WriteToXlsx(fn, rc);
}
dbDstHouses.BeginTransaction();
foreach (HeatingSystemEntry heatingSystemEntry in srcHeatingSystems)
{
heatingSystemEntry.Age += yearsToAge;
heatingSystemEntry.ID = 0;
dbDstHouses.Save(heatingSystemEntry);
}
dbDstHouses.CompleteTransaction();
var srcHouseGuids = srcHouses.Select(x => x.Guid).ToHashSet();
var dstHouseGuids = dstHouses.Select(x => x.Guid).ToHashSet();
foreach (var heatingSystem in srcHeatingSystems)
{
srcHouseGuids.Should().Contain(heatingSystem.HouseGuid);
dstHouseGuids.Should().Contain(heatingSystem.HouseGuid);
}
}
public GridControl()
{
Columns = new ColumnCollection();
Rows = new RowCollection();
TabStop = false;
}
public FileResult DownloadTemplate(DownloadTemplateViewModel vModel)
{
ConfigType config = string.IsNullOrEmpty(vModel.ConfigType) ? ConfigType.KpiTarget
: (ConfigType)Enum.Parse(typeof(ConfigType), vModel.ConfigType);
#region Get Data
PeriodeType pType = string.IsNullOrEmpty(vModel.PeriodeType) ? PeriodeType.Yearly
: (PeriodeType)Enum.Parse(typeof(PeriodeType), vModel.PeriodeType);
var viewModel = new ConfigurationViewModel();
switch (config)
{
case ConfigType.KpiTarget:
{
var request = new GetKpiTargetsConfigurationRequest()
{
PeriodeType = vModel.PeriodeType,
Year = vModel.Year,
Month = vModel.Month,
RoleGroupId = vModel.RoleGroupId
};
var target = _kpiTargetService.GetKpiTargetsConfiguration(request);
viewModel = target.MapTo <ConfigurationViewModel>();
break;
}
case ConfigType.KpiAchievement:
{
var request = new GetKpiAchievementsConfigurationRequest()
{
PeriodeType = vModel.PeriodeType,
Year = vModel.Year,
Month = vModel.Month,
RoleGroupId = vModel.RoleGroupId
};
var achievement = _kpiAchievementService.GetKpiAchievementsConfiguration(request);
viewModel = achievement.MapTo <ConfigurationViewModel>();
break;
}
case ConfigType.OperationData:
{
var request = vModel.MapTo <GetOperationDataConfigurationRequest>();
request.PeriodeType = pType;
request.IsPartial = false;
var operationData = _operationDataService.GetOperationDataConfiguration(request);
viewModel = operationData.MapTo <ConfigurationViewModel>();
//return new FileContentResult(null, "application/octet-stream") { FileDownloadName = "as" };
break;
}
default:
break;
}
#endregion
/*
* Find and Create Directory
*/
var resultPath = Server.MapPath(string.Format("{0}{1}/", TemplateDirectory, vModel.ConfigType));
if (!Directory.Exists(resultPath))
{
Directory.CreateDirectory(resultPath);
}
#region parsing data to excel
string dateFormat = string.Empty;
string workSheetName = new StringBuilder(vModel.PeriodeType).ToString();
switch (vModel.PeriodeType)
{
case "Yearly":
dateFormat = "yyyy";
break;
case "Monthly":
dateFormat = "mmm-yy";
workSheetName = string.Format("{0}_{1}", workSheetName, vModel.Year);
break;
default:
dateFormat = "dd-mmm-yy";
workSheetName = string.Format("{0}_{1}-{2}", workSheetName, vModel.Year, vModel.Month.ToString().PadLeft(2, '0'));
break;
}
string fileName = string.Format(@"{0}_{1}_{2}.xlsx", vModel.ConfigType, vModel.PeriodeType, DateTime.Now.ToString("yyyymmddMMss"));
IWorkbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Name = workSheetName;
workbook.Worksheets.ActiveWorksheet = worksheet;
RowCollection rows = workbook.Worksheets[0].Rows;
ColumnCollection columns = workbook.Worksheets[0].Columns;
Row headerRow = rows[0];
headerRow.FillColor = Color.DarkGray;
headerRow.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
headerRow.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
Column kpiIdColumn = columns[0];
Column kpiNameColumn = columns[1];
kpiIdColumn.Visible = false;
headerRow.Worksheet.Cells[headerRow.Index, kpiIdColumn.Index].Value = "KPI ID";
headerRow.Worksheet.Cells[headerRow.Index, kpiNameColumn.Index].Value = "KPI Name";
int i = 1; //i for row
#region inserting from models
foreach (var kpi in viewModel.Kpis)
{
worksheet.Cells[i, kpiIdColumn.Index].Value = kpi.Id;
worksheet.Cells[i, kpiNameColumn.Index].Value = string.Format("{0} ({1})", kpi.Name, kpi.Measurement);
int j = 2; // for column
var items = new List <ConfigurationViewModel.Item>();
switch (vModel.ConfigType)
{
case "KpiTarget":
{
foreach (var target in kpi.KpiTargets)
{
var item = new ConfigurationViewModel.Item
{
Id = target.Id,
KpiId = kpi.Id,
Periode = target.Periode,
Remark = target.Remark,
Value = target.Value.HasValue ? target.Value.ToString() : string.Empty,
PeriodeType = pType
};
items.Add(item);
}
break;
}
case "KpiAchievement":
{
foreach (var achievement in kpi.KpiAchievements)
{
var item = new ConfigurationViewModel.Item()
{
Id = achievement.Id,
KpiId = kpi.Id,
Periode = achievement.Periode,
Remark = achievement.Remark,
Value = achievement.Value.HasValue ? achievement.Value.ToString() : string.Empty,
PeriodeType = pType
};
items.Add(item);
}
break;
}
case "OperationData":
{
//items = kpi.OperationData.MapTo<ConfigurationViewModel.Item>();
foreach (var operationData in kpi.OperationData)
{
var item = new ConfigurationViewModel.Item()
{
Id = operationData.Id,
KpiId = kpi.Id,
Periode = operationData.Periode,
Remark = operationData.Remark,
Value = operationData.Value.HasValue ? operationData.Value.ToString() : string.Empty,
PeriodeType = pType
};
items.Add(item);
}
break;
}
}
foreach (var item in items)
{
worksheet.Cells[headerRow.Index, j].Value = item.Periode;
worksheet.Cells[headerRow.Index, j].NumberFormat = dateFormat;
worksheet.Cells[headerRow.Index, j].AutoFitColumns();
worksheet.Cells[i, j].Value = item.RealValue;
worksheet.Cells[i, j].NumberFormat = "#,0.#0";
worksheet.Columns[j].AutoFitColumns();
j++;
}
Column totalValueColumn = worksheet.Columns[j];
if (i == headerRow.Index + 1)
{
worksheet.Cells[headerRow.Index, totalValueColumn.Index].Value = "Average";
worksheet.Cells[headerRow.Index, totalValueColumn.Index + 1].Value = "SUM";
Range r1 = worksheet.Range.FromLTRB(kpiNameColumn.Index + 1, i, j - 1, i);
worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r1.GetReferenceA1());
worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r1.GetReferenceA1());
}
else
{
// add formula
Range r2 = worksheet.Range.FromLTRB(kpiNameColumn.Index + 1, i, j - 1, i);
worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r2.GetReferenceA1());
worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r2.GetReferenceA1());
}
i++;
}
#endregion
kpiNameColumn.AutoFitColumns();
worksheet.FreezePanes(headerRow.Index, kpiNameColumn.Index);
string resultFilePath = string.Format("{0},{1}", resultPath, fileName);
//System.Web.HttpContext.Current.Request.MapPath(resultPath + fileName);
//System.Web.HttpContext.Current.Response.Clear();
//System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//System.Web.HttpContext.Current.Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}", fileName));
using (FileStream stream = new FileStream(resultFilePath, FileMode.Create, FileAccess.ReadWrite))
{
workbook.SaveDocument(stream, DevExpress.Spreadsheet.DocumentFormat.Xlsx);
stream.Close();
}
//System.Web.HttpContext.Current.Response.End();
//workbook.SaveDocument(resultFilePath, DocumentFormat.OpenXml);
//workbook.Dispose();
#endregion
string namafile = Path.GetFileName(resultFilePath);
byte[] fileBytes = System.IO.File.ReadAllBytes(resultFilePath);
var response = new FileContentResult(fileBytes, "application/octet-stream")
{
FileDownloadName = fileName
};
return(response);
}
/// <summary>
/// Initializes a new instance of the RowCollectionEditor class
/// using the specified collection type
/// </summary>
/// <param name="type">The type of the collection for this editor to edit</param>
public RowCollectionEditor(Type type) : base(type)
{
this.rows = null;
}
public CSVAdapter()
{
_Rows = new RowCollection();
}
///<inheritdoc/>
protected override void OnInitialized()
{
RowCollection.AddRow(this);
}
protected override void RunActualProcess()
{
var dbHouses = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Houses, Constants.PresentSlice);
var dbComplex = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Complexes, Constants.PresentSlice);
dbHouses.RecreateTable <HouseHeating>();
var buildingcomplexes = dbComplex.Fetch <BuildingComplex>();
var dbRaw = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice);
var energieBedarfsDatenBern = dbRaw.Fetch <EnergiebedarfsdatenBern>();
var houses = dbHouses.Fetch <House>();
var potentialHeatingSystems = dbHouses.Fetch <PotentialHeatingSystemEntry>();
RowCollection rc = new RowCollection("Heating", "Heizung");
dbHouses.BeginTransaction();
foreach (var h in houses)
{
RowBuilder rb = RowBuilder.Start("House", h.ComplexName);
rc.Add(rb);
var c = buildingcomplexes.First(x => x.ComplexID == h.ComplexID);
var hs = potentialHeatingSystems.Where(x => x.HouseGuid == h.Guid).ToList();
var houseHeating = new HouseHeating {
LocalnetFernwärmeEnergyUse = hs.Sum(x => x.YearlyFernwärmeDemand),
LocalnetGasEnergyUse = hs.Sum(x => x.YearlyGasDemand),
HouseGuid = h.Guid,
LocalnetHeatingSystemEntryCount = hs.Count
};
rb.Add("Fernwärme", houseHeating.LocalnetFernwärmeEnergyUse).Add("LocalnetEntries", hs.Count);
rb.Add("Standort", string.Join(",", hs.Select(x => x.Standort).Distinct()));
rb.Add("Gas", houseHeating.LocalnetGasEnergyUse);
rb.Add("EBF", h.EnergieBezugsFläche);
rb.Add("Abrechnung", JsonConvert.SerializeObject(hs, Formatting.Indented));
//collect ebbe daten
foreach (var eGid in c.EGids)
{
var ebdbs = energieBedarfsDatenBern.Where(x => x.egid == eGid).ToList();
if (ebdbs.Count > 1)
{
throw new Exception("too many ebdb");
}
if (ebdbs.Count == 1)
{
var eb = ebdbs[0];
houseHeating.KantonHeatingMethods.Add(GetHeatingMethodString((int)eb.upd_genhz));
houseHeating.KantonTotalEnergyDemand += eb.calc_ehzww;
houseHeating.KantonHeizungEnergyDemand += eb.calc_ehz;
houseHeating.KantonWarmwasserEnergyDemand += eb.calc_eww;
houseHeating.KantonDhwMethods.Add(GetHeatingMethodString(eb.upd_genww));
}
}
houseHeating.LocalnetCombinedEnergyDemand = houseHeating.LocalnetFernwärmeEnergyUse + houseHeating.LocalnetGasEnergyUse;
houseHeating.LocalnetHeatingEnergyDensity = houseHeating.LocalnetAdjustedHeatingDemand / h.EnergieBezugsFläche;
if (houseHeating.LocalnetHeatingEnergyDensity > 500)
{
houseHeating.LocalnetHeatingEnergyDensity = 250;
houseHeating.EnergyDensityIndustrialApplication = houseHeating.LocalnetHeatingEnergyDensity - 250;
}
houseHeating.KantonHeatingEnergyDensity = houseHeating.KantonTotalEnergyDemand / h.EnergieBezugsFläche;
if (Math.Abs(houseHeating.LocalnetHeatingEnergyDensity) > 0.001)
{
houseHeating.HeatingEnergyDifference = houseHeating.LocalnetHeatingEnergyDensity - houseHeating.KantonHeatingEnergyDensity;
if (double.IsNaN(houseHeating.HeatingEnergyDifference) || double.IsInfinity(houseHeating.HeatingEnergyDifference))
{
houseHeating.HeatingEnergyDifference = 0;
}
}
if (houseHeating.LocalnetHeatingEnergyDensity > 0)
{
houseHeating.MergedHeatingEnergyDensity = houseHeating.LocalnetHeatingEnergyDensity;
}
else
{
houseHeating.MergedHeatingEnergyDensity = houseHeating.KantonHeatingEnergyDensity;
}
if (houseHeating.MergedHeatingEnergyDensity < 0)
{
throw new Exception("Negative heating intensity");
}
houseHeating.MergedHeatingDemand = 0;
if (houseHeating.LocalnetCombinedEnergyDemand > 1)
{
houseHeating.MergedHeatingDemand = houseHeating.LocalnetCombinedEnergyDemand;
}
else
{
houseHeating.MergedHeatingDemand = houseHeating.KantonTotalEnergyDemand;
}
dbHouses.Save(h);
dbHouses.Save(houseHeating);
}
dbHouses.CompleteTransaction();
var fn = MakeAndRegisterFullFilename("heatingCalcsDump.xlsx", Constants.PresentSlice);
XlsxDumper.WriteToXlsx(fn, rc);
}
protected override void BeginProcessing()
{
base.BeginProcessing();
if (parFile.StartsWith(@".\"))
{
parFile = System.IO.Path.Combine(this.CurrentProviderLocation("FileSystem").ProviderPath, parFile.Substring(2));
}
parFile = System.IO.Path.GetFullPath(parFile);
if (!System.IO.File.Exists(parFile))
{
ThrowTerminatingError(new ErrorRecord(new FileNotFoundException(string.Format("The source file does not exist", parFile)), "200", ErrorCategory.OpenError, parFile));
}
FileInfo fileInfo = new System.IO.FileInfo(parFile);
ExcelPackage excelPackage = new ExcelPackage(fileInfo);
ExcelWorkbook excelWorkbook = excelPackage.Workbook;
Workbook workbook = new Workbook();
workbook.Filepath = parFile;
workbook.FileIsReadOnly = fileInfo.IsReadOnly;
workbook.FileLastModified = fileInfo.LastWriteTime;
workbook.FileCreated = fileInfo.CreationTime;
workbook.Author = excelWorkbook.Properties.Author;
workbook.Title = excelWorkbook.Properties.Title;
workbook.Comments = excelWorkbook.Properties.Comments;
workbook.Keywords = excelWorkbook.Properties.Keywords;
List <Worksheet> worksheets = new List <Worksheet>();
foreach (ExcelWorksheet excelWorksheet in excelWorkbook.Worksheets)
{
DynamicTypeManager dynType = new DynamicTypeManager("Sorlov.PowerShell.MicrosoftOffice", "ExcelWorkbook#" + parFile.GetHashCode().ToString());
Worksheet worksheet = new Worksheet();
worksheet.Name = excelWorksheet.Name;
int colCount = 1;
while (true)
{
if (excelWorksheet.Cell(1, colCount).Value == null)
{
break;
}
if (excelWorksheet.Cell(1, colCount).Value.Trim() == string.Empty)
{
break;
}
dynType.CreateProperty(excelWorksheet.Cell(1, colCount).Value.Trim(), typeof(string));
colCount++;
}
List <object> rowList = new List <object>();
int rowCount = 2;
while (true)
{
bool foundSomething = false;
object newRow = dynType.Create();
for (int i = 1; i < colCount; i++)
{
string cellValue = excelWorksheet.Cell(rowCount, i).Value;
if (cellValue != null)
{
if (cellValue.Trim() != string.Empty)
{
DynamicTypeManager.SetProperty(newRow, excelWorksheet.Cell(1, i).Value.Trim(), cellValue);
foundSomething = true;
}
}
}
if (foundSomething == false)
{
break;
}
rowList.Add(newRow);
rowCount++;
}
RowCollection rowCollection = new RowCollection(rowList);
worksheet.Rows = rowCollection;
worksheets.Add(worksheet);
}
workbook.Worksheets = new WorksheetCollection(worksheets);
WriteObject(workbook);
excelPackage.Dispose();
}
internal CsvDocument()
: base(CsvNodeType.Document)
{
names = new NameCollection(this);
rows = new RowCollection(this);
}
public Sheet(string name)
: this()
{
this.Rows = new RowCollection(this);
this.Name = name;
}
private ActionResult ConvertToExcelFile(OperationDataParamConfigurationViewModel viewModel,
OperationDataConfigurationViewModel data)
{
var resultPath = Server.MapPath(string.Format("{0}{1}/", TemplateDirectory, ConfigType.OperationData));
if (!Directory.Exists(resultPath))
{
Directory.CreateDirectory(resultPath);
}
string workSheetName = new StringBuilder(viewModel.PeriodeType).ToString();
string dateFormat = string.Empty;
switch (viewModel.PeriodeType)
{
case "Yearly":
dateFormat = "yyyy";
break;
case "Monthly":
dateFormat = "mmm-yy";
workSheetName = string.Format("{0}_{1}", workSheetName, viewModel.Year);
break;
default:
dateFormat = "dd-mmm-yy";
workSheetName = string.Format("{0}_{1}-{2}", workSheetName, viewModel.Year,
viewModel.Month.ToString().PadLeft(2, '0'));
break;
}
string fileName = string.Format(@"{0}.xlsx", DateTime.Now.ToString("yyyymmddMMss"));
/*string fileName = new StringBuilder(guid).Append(".xlsx").ToString();*/
IWorkbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Name = workSheetName;
workbook.Worksheets.ActiveWorksheet = worksheet;
RowCollection rows = workbook.Worksheets[0].Rows;
ColumnCollection columns = workbook.Worksheets[0].Columns;
Row headerRow = rows[0];
headerRow.FillColor = Color.DarkGray;
headerRow.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
headerRow.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
Column kpiIdColumn = columns[0];
Column kpiNameColumn = columns[1];
kpiIdColumn.Visible = false;
headerRow.Worksheet.Cells[headerRow.Index, kpiIdColumn.Index].Value = "KPI ID";
headerRow.Worksheet.Cells[headerRow.Index, kpiNameColumn.Index].Value = "KPI Name";
int i = 1;
foreach (var kpi in data.Kpis)
{
int j = 2;
worksheet.Cells[i, kpiIdColumn.Index].Value = kpi.Id;
worksheet.Cells[i, kpiNameColumn.Index].Value = string.Format(@"{0} ({1})", kpi.Name, kpi.MeasurementName);
foreach (var operationData in kpi.OperationData.OrderBy(x => x.Periode))
{
worksheet.Cells[headerRow.Index, j].Value = operationData.Periode;
worksheet.Cells[headerRow.Index, j].NumberFormat = dateFormat;
worksheet.Cells[headerRow.Index, j].AutoFitColumns();
worksheet.Cells[i, j].Value = operationData.Value;
worksheet.Cells[i, j].NumberFormat = "#,0.#0";
worksheet.Columns[j].AutoFitColumns();
j++;
}
Column totalValueColumn = worksheet.Columns[j];
if (i == headerRow.Index + 1)
{
worksheet.Cells[headerRow.Index, totalValueColumn.Index].Value = "Average";
worksheet.Cells[headerRow.Index, totalValueColumn.Index + 1].Value = "SUM";
Range r1 = worksheet.Range.FromLTRB(kpiNameColumn.Index + 1, i, j - 1, i);
worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r1.GetReferenceA1());
worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r1.GetReferenceA1());
}
else
{
// add formula
Range r2 = worksheet.Range.FromLTRB(kpiNameColumn.Index + 1, i, j - 1, i);
worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r2.GetReferenceA1());
worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r2.GetReferenceA1());
}
i++;
}
kpiNameColumn.AutoFitColumns();
worksheet.FreezePanes(headerRow.Index, kpiNameColumn.Index);
string resultFilePath = string.Format("{0},{1}", resultPath, fileName);
using (FileStream stream = new FileStream(resultFilePath, FileMode.Create, FileAccess.ReadWrite))
{
workbook.SaveDocument(stream, DevExpress.Spreadsheet.DocumentFormat.Xlsx);
stream.Close();
}
string namafile = Path.GetFileName(resultFilePath);
byte[] fileBytes = System.IO.File.ReadAllBytes(resultFilePath);
var response = new FileContentResult(fileBytes, "application/octet-stream")
{
FileDownloadName = fileName
};
return(response);
}
public void ReadXml(XmlReader reader)
{
var xml = new XmlHelper(reader);
Agents = xml.deserializeRowSet<StandingEntry>("agents");
Corporations = xml.deserializeRowSet<StandingEntry>("NPCCorporations");
Factions = xml.deserializeRowSet<StandingEntry>("factions");
}
public void ReadXml(XmlReader reader)
{
var xml = new XmlHelper(reader);
KillsYesterday = xml.deserializeRowSet<CorporationEntry>("KillsYesterday");
KillsLastWeek = xml.deserializeRowSet<CorporationEntry>("KillsLastWeek");
KillsTotal = xml.deserializeRowSet<CorporationEntry>("KillsTotal");
VictoryPointsYesterday = xml.deserializeRowSet<CorporationEntry>("VictoryPointsYesterday");
VictoryPointsLastWeek = xml.deserializeRowSet<CorporationEntry>("VictoryPointsLastWeek");
VictoryPointsTotal = xml.deserializeRowSet<CorporationEntry>("VictoryPointsTotal");
}
public void Given_empty_collection_Then_returns_empty_Headers_list()
{
var rowCollection = new RowCollection(new List<Row>());
rowCollection.GetColumnHeaders().Should().BeEmpty();
}
private void OpenTable(string server, string database, string table)
{
string cnString = SqlTextReader.GetConnectionString(server, database);
// get the types
SqlCommand cmd = null;
SqlDataReader sqlReader = null;
try
{
cn = new SqlConnection(cnString);
if (cn.State == System.Data.ConnectionState.Closed)
{
cn.Open();
}
cmd = new SqlCommand("SELECT TOP 1 * FROM [" + table + "]", cn);
//sqlReader = cmd.ExecuteReader();
sqlReader = cmd.ExecuteReader(CommandBehavior.CloseConnection |
CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow |
CommandBehavior.SchemaOnly);
int c = sqlReader.FieldCount;
fieldTypes = new Type[c];
typeNames = new string[c];
fieldNames = new string[c];
for (int i = 0; i < c; i++)
{
fieldTypes[i] = sqlReader.GetFieldType(i);
typeNames[i] = sqlReader.GetDataTypeName(i);
fieldNames[i] = sqlReader.GetName(i);
}
}
finally
{
if (cmd != null)
{
try
{
cmd.Cancel();
}
catch
{
}
}
if (sqlReader != null)
{
try
{
sqlReader.Close();
sqlReader.Dispose();
}
catch
{
}
}
if (cn != null)
{
try
{
cn.Close();
}
catch
{
}
}
if (cmd != null)
{
try
{
cmd.Dispose();
}
catch
{
}
}
}
rows = new RowCollection(batchSize, fieldNames, fieldTypes, typeNames);
// open a bulk copy
try
{
cn = new SqlConnection(cnString);
if (cn.State == System.Data.ConnectionState.Closed)
{
cn.Open();
}
sqlBulkCopy = new SqlBulkCopy(cn);
sqlBulkCopy.BatchSize = batchSize;
sqlBulkCopy.DestinationTableName = table;
sqlBulkCopy.BulkCopyTimeout = Timeout;
//sqlBulkCopy.ColumnMappings
}
catch
{
try
{
cn.Close();
}
catch
{
}
throw;
}
}
public virtual void TerminateRowCollection(RowCollection coll)
{
}
internal void redimTable(int cols, int rows, bool copy,
int cpCol, int cpRow, bool cpInserted,
int colsAffected, int rowsAffected, bool undo)
{
resetCoveredCells();
hasHeaderRows = false;
if (undo) flowChart.UndoManager.onRedimTable(this);
// remove arrows attached to deleted rows
if (!cpInserted && (rowsAffected != 0) && (rowsList != null))
{
ArrayList arrowsToRemove = new ArrayList();
for (int i = cpRow; i < cpRow + rowsAffected && i < this.rowsCount; ++i)
{
Row r = (Row)this.rowsList[i];
for (int k = 0; k < r.IncomingArrows.Count; ++k)
arrowsToRemove.Add(r.IncomingArrows[k]);
for (int k = 0; k < r.OutgoingArrows.Count; ++k)
arrowsToRemove.Add(r.OutgoingArrows[k]);
}
for (int j = 0; j < arrowsToRemove.Count; ++j)
flowChart.DeleteObject((ChartObject)arrowsToRemove[j]);
arrowsToRemove.Clear();
}
if (undo) flowChart.UndoManager.onRedimArrowsDeleted(this);
CellCollection oldData = cells;
RowCollection oldRowsDsc = rowsList;
ColumnCollection oldColsDsc = colsList;
int oldCols = this.columnsCount;
int oldRows = this.rowsCount;
bool copyC, copyR;
copyC = copyR = copy;
// resize the table
this.columnsCount = cols;
this.rowsCount = rows;
// resize the columns array
colsList = new ColumnCollection();
if (this.columnsCount > 0)
{
for (int c = 0; c < this.columnsCount; ++c)
{
Column col = new Column(this);
col.width = columnWidth;
colsList.Add(col);
}
}
else
{
copyC = false;
}
// resize the rows array
rowsList = new RowCollection();
if (this.rowsCount > 0)
{
for (int rr = 0; rr < this.rowsCount; ++rr)
{
Row row = new Row(this);
row.Height = rowHeight;
rowsList.Add(row);
}
}
else
{
copyR = false;
}
// resize the cells array
cells = new CellCollection();
if (this.columnsCount > 0 && this.rowsCount > 0)
{
for (int i = 0; i < this.columnsCount * this.rowsCount; ++i)
cells.Add(new Cell(this));
}
else
{
copy = false;
}
// copy data from the old cells array to the new one
if (copy && (cells != null) && (oldData != null))
{
for (int rr = 0; rr < this.rowsCount; ++rr)
{
for (int c = 0; c < this.columnsCount; ++c)
{
int cr = rr;
int cc = c;
bool cp = true;
if (cpInserted)
{
if (c >= cpCol && c < cpCol + colsAffected) cp = false;
if (rr >= cpRow && rr < cpRow + rowsAffected) cp = false;
if (c >= cpCol + colsAffected) cc -= colsAffected;
if (rr >= cpRow + rowsAffected) cr -= rowsAffected;
}
else
{
if (c >= cpCol) cc += colsAffected;
if (rr >= cpRow) cr += rowsAffected;
}
if (cp)
{
cells[rr * this.columnsCount + c] = oldData[cr * oldCols + cc];
oldData[cr * oldCols + cc] = null;
}
else
cells[rr * this.columnsCount + c] = new Cell(this);
}
}
}
if (oldData != null)
{
for (int oc = 0; oc < oldData.Count; ++oc)
{
if (oldData[oc] != null)
oldData[oc].freeResources();
}
}
// copy data from the old rows array to the new one
if (copyR && (rowsList != null) && (oldRowsDsc != null))
{
for (int rr = 0; rr < this.rowsCount; ++rr)
{
int cr = rr;
bool cp = true;
if (cpInserted)
{
if (rr >= cpRow && rr < cpRow + rowsAffected) cp = false;
if (rr >= cpRow + rowsAffected) cr -= rowsAffected;
}
else
{
if (rr >= cpRow) cr += rowsAffected;
}
if (cp)
rowsList[rr] = oldRowsDsc[cr];
}
}
// copy data from the old columns array to the new one
if (copyC && (colsList != null) && (oldColsDsc != null))
{
for (int c = 0; c < this.columnsCount; ++c)
{
int cc = c;
bool cp = true;
if (cpInserted)
{
if (c >= cpCol && c < cpCol + colsAffected) cp = false;
if (c >= cpCol + colsAffected) cc -= colsAffected;
}
else
{
if (c >= cpCol) cc += colsAffected;
}
if (cp)
colsList[c] = oldColsDsc[cc];
}
}
if (rowsAffected > 0)
{
updateLinksIndices();
updateArrowsPos(cpRow);
}
if (copy && rowsAffected != 0)
checkForHeaderRows();
if (undo) flowChart.UndoManager.onCompleteRedim();
}
protected override void RunActualProcess()
{
var dbHouses = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Houses, Constants.PresentSlice);
dbHouses.RecreateTable <DHWHeaterEntry>();
var houses = dbHouses.Fetch <House>();
var households = dbHouses.Fetch <Household>();
var houseHeatings = dbHouses.Fetch <HouseHeating>();
var hausanschlusses = dbHouses.Fetch <Hausanschluss>();
if (houseHeatings.All(x => x.KantonDhwMethods.Count == 0))
{
throw new Exception("not a single dhw heating method was set");
}
if (houseHeatings.All(x => x.KantonHeatingMethods.Count == 0))
{
throw new Exception("not a single space heating method was set");
}
RowCollection rc = new RowCollection("Sheet1", "Sheet1");
dbHouses.BeginTransaction();
foreach (var house in houses)
{
var householdInHouse = households.Where(x => x.HouseGuid == house.Guid).ToList();
var occupantsInHouse = householdInHouse.SelectMany(x => x.Occupants).ToList();
var dhwHeaterEntry = new DHWHeaterEntry(house.Guid, Guid.NewGuid().ToString(), "DHW@" + house.ComplexName);
var houseHeating = houseHeatings.Single(x => x.HouseGuid == house.Guid);
var heatingMethod = HeatingSystemType.Unbekannt;
if (houseHeating.KantonDhwMethods.Count > 0)
{
heatingMethod = houseHeating.GetDominantDhwHeatingMethod();
}
var peopleInHouse = occupantsInHouse.Count;
RowBuilder rb = RowBuilder.Start("House", house.ComplexName);
rc.Add(rb);
rb.Add("Households", households.Count);
rb.Add("Persons", occupantsInHouse.Count);
rb.Add("Ebbe DHW Estimate", houseHeating.KantonWarmwasserEnergyDemand);
switch (heatingMethod)
{
case HeatingSystemType.Electricity:
dhwHeaterEntry.DhwHeatingSystemType = DhwHeatingSystem.Electricity;
// electricity at night
break;
case HeatingSystemType.SolarThermal:
dhwHeaterEntry.DhwHeatingSystemType = DhwHeatingSystem.Electricity;
break;
case HeatingSystemType.Gas:
dhwHeaterEntry.DhwHeatingSystemType = DhwHeatingSystem.Gasheating;
break;
case HeatingSystemType.Heatpump:
dhwHeaterEntry.DhwHeatingSystemType = DhwHeatingSystem.Electricity;
break;
case HeatingSystemType.Fernwärme:
dhwHeaterEntry.DhwHeatingSystemType = DhwHeatingSystem.DistrictHeating;
break;
case HeatingSystemType.Other:
dhwHeaterEntry.DhwHeatingSystemType = DhwHeatingSystem.Electricity;
break;
case HeatingSystemType.None:
dhwHeaterEntry.DhwHeatingSystemType = DhwHeatingSystem.None;
break;
case HeatingSystemType.Öl:
dhwHeaterEntry.DhwHeatingSystemType = DhwHeatingSystem.OilHeating;
break;
case HeatingSystemType.Holz:
dhwHeaterEntry.DhwHeatingSystemType = DhwHeatingSystem.Electricity;
break;
case HeatingSystemType.Unbekannt:
dhwHeaterEntry.DhwHeatingSystemType = DhwHeatingSystem.Electricity;
break;
case HeatingSystemType.GasheatingLocalnet:
dhwHeaterEntry.DhwHeatingSystemType = DhwHeatingSystem.Gasheating;
break;
case HeatingSystemType.FernwärmeLocalnet:
throw new Exception("Unknown heating method: " + heatingMethod);
case HeatingSystemType.FeuerungsstättenOil:
throw new Exception("Unknown heating method: " + heatingMethod);
case HeatingSystemType.FeuerungsstättenGas:
throw new Exception("Unknown heating method: " + heatingMethod);
case HeatingSystemType.Kohle:
throw new Exception("Unknown heating method: " + heatingMethod);
default: throw new Exception("Unknown heating method: " + heatingMethod);
}
rb.Add("Heating Method", dhwHeaterEntry.DhwHeatingSystemType);
double totalEnergy = 0;
double dhwEnergy = 0;
Hausanschluss hausanschluss;
string hausanschlussGuid = null;
string standort = null;
foreach (var hh in householdInHouse)
{
double personEnergy = hh.EffectiveEnergyDemand / hh.Occupants.Count;
if (personEnergy > 1800)
{
dhwHeaterEntry.DhwHeatingSystemType = DhwHeatingSystem.Electricity;
}
}
foreach (var hh in householdInHouse)
{
double hhdhwEnergy = 0;
double personEnergy = hh.EffectiveEnergyDemand / hh.Occupants.Count;
if (dhwHeaterEntry.DhwHeatingSystemType == DhwHeatingSystem.Electricity)
{
hhdhwEnergy = CalculateDHWEnergy(personEnergy) * hh.Occupants.Count;
}
var rbhh = RowBuilder.Start("household", hh.Name);
rbhh.Add("Persons", hh.Occupants.Count);
rbhh.Add("Energy Per Person in Household [kWh]", hhdhwEnergy);
rbhh.Add("Household DHW heating method", dhwHeaterEntry.DhwHeatingSystemType);
rbhh.Add("Household Energy", hh.EffectiveEnergyDemand);
rc.Add(rbhh);
if (Math.Abs(hh.EffectiveEnergyDemand - hh.LocalnetLowVoltageYearlyTotalElectricityUse) > 0.1)
{
if (Math.Abs(hh.EffectiveEnergyDemand - (hh.LocalnetLowVoltageYearlyTotalElectricityUse + dhwEnergy)) > 0.1)
{
throw new FlaException("Energy use does not fit");
}
}
hh.SetEnergyReduction("DHW", hhdhwEnergy);
if (hh.EffectiveEnergyDemand < 0)
{
throw new FlaException("Effective Energy demand was null");
}
totalEnergy += hh.EffectiveEnergyDemand;
hausanschlussGuid = hh.HausAnschlussGuid;
standort = hh.Standort;
dbHouses.Save(hh);
dhwEnergy += hhdhwEnergy;
}
if (hausanschlussGuid != null)
{
hausanschluss = hausanschlusses.Single(x => x.Guid == hausanschlussGuid);
}
else
{
hausanschluss = house.GetHausanschlussByIsn(new List <int>(), null, hausanschlusses, MyLogger) ??
throw new FlaException("no hausanschluss");
}
dhwHeaterEntry.Standort = standort;
dhwHeaterEntry.HausAnschlussGuid = hausanschluss.Guid;
dhwHeaterEntry.EffectiveEnergyDemand = dhwEnergy;
if (totalEnergy < 0)
{
throw new FlaException("Negative total energy");
}
rb.Add("Total Energy Originally [kWh]", totalEnergy);
rb.Add("Total Energy DHW [kWh]", dhwEnergy);
rb.Add("DHW Power [kWh]", dhwEnergy / 365 / 2);
rb.Add("Total Energy After Dhw [kWh]", totalEnergy - dhwEnergy);
rb.Add("Energy Per Person [kWh]", totalEnergy / peopleInHouse);
dbHouses.Save(dhwHeaterEntry);
}
dbHouses.CompleteTransaction();
var hhdump = MakeAndRegisterFullFilename("Households.xlsx", Constants.PresentSlice);
XlsxDumper.WriteToXlsx(hhdump, rc);
}
internal void restoreStructure(TableStructure str)
{
resetCoveredCells();
cells = str.cells.Clone();
colsList = str.cols.Clone();
rowsList = str.rows.Clone();
rowsCount = str.rowsCount;
columnsCount = str.columnsCount;
hasHeaderRows = str.hasHeaderRows;
layoutText();
layoutCellText();
}
public void ReadXml(XmlReader reader)
{
var xml = new XmlHelper(reader);
CallGroups = xml.deserializeRowSet<CallGroup>("callGroups");
Calls = xml.deserializeRowSet<Call>("calls");
}
public Row(IEnumerable <string> path, IEnumerable <Row> subRows)
{
Path = new ReadOnlyCollection <string>(new List <string>(path));
SubRows = new RowCollection(new List <Row>(subRows));
}
protected override void MakeVisualization([NotNull] ScenarioSliceParameters slice, bool isPresent)
{
var dbHouse = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Houses, slice);
List <House> houses1 = dbHouse.Fetch <House>();
var heatingsystems1 = dbHouse.Fetch <HeatingSystemEntry>();
Dictionary <string, HeatingSystemEntry> heatingsystemsByGuid = new Dictionary <string, HeatingSystemEntry>();
foreach (var hse in heatingsystems1)
{
heatingsystemsByGuid.Add(hse.HouseGuid, hse);
}
MakeHeatingSystemAnalysis();
EnergyIntensityHistogram();
MakeHeatingTypeIntensityMap();
MakeHeatingTypeMap();
MakeHeatingSystemSankey();
HeatingSystemCountHistogram();
MakeHeatingSystemMapError();
MakeHeatingSystemMap();
MakeFernwärmeTypeMap();
if (isPresent)
{
PresentOnlyVisualisations(heatingsystemsByGuid, slice, houses1, dbHouse);
}
void MakeHeatingSystemAnalysis()
{
var filename = MakeAndRegisterFullFilename("analysis.csv", slice);
var sw = new StreamWriter(filename);
sw.WriteLine("Original Heating system - Anzahl");
foreach (HeatingSystemType hst in Enum.GetValues(typeof(HeatingSystemType)))
{
var fs1 = heatingsystems1.Where(x => x.OriginalHeatingSystemType == hst).ToList();
sw.WriteLine(hst + ";" + fs1.Count);
}
sw.WriteLine("");
sw.WriteLine("");
sw.WriteLine("Original Heating system - Summe");
foreach (HeatingSystemType hst in Enum.GetValues(typeof(HeatingSystemType)))
{
var fs1 = heatingsystems1.Where(x => x.OriginalHeatingSystemType == hst).ToList();
sw.WriteLine(hst + ";" + fs1.Sum(x => x.EffectiveEnergyDemand));
}
sw.WriteLine("");
sw.WriteLine("");
sw.WriteLine("Target Anzahl");
foreach (HeatingSystemType hst in Enum.GetValues(typeof(HeatingSystemType)))
{
var fs1 = heatingsystems1.Where(x => x.SynthesizedHeatingSystemType == hst).ToList();
sw.WriteLine(hst + ";" + fs1.Count);
}
sw.WriteLine("");
sw.WriteLine("");
sw.WriteLine("Target Summe");
foreach (HeatingSystemType hst in Enum.GetValues(typeof(HeatingSystemType)))
{
var fs1 = heatingsystems1.Where(x => x.SynthesizedHeatingSystemType == hst).ToList();
sw.WriteLine(hst + ";" + fs1.Sum(x => x.EffectiveEnergyDemand));
}
sw.Close();
}
void EnergyIntensityHistogram()
{
var filename = MakeAndRegisterFullFilename("EnergyIntensityHistogram.png", slice);
var ages = heatingsystems1.Select(x => x.CalculatedAverageHeatingEnergyDemandDensity).Where(y => y > 0).ToList();
var barSeries = new List <BarSeriesEntry>();
var h = new Histogram(ages, 100);
barSeries.Add(BarSeriesEntry.MakeBarSeriesEntry(h, out var colnames, "F0"));
Services.PlotMaker.MakeBarChart(filename, "EnergyIntensityHistogram", barSeries, colnames);
var xlsfilename = MakeAndRegisterFullFilename("EnergyIntensity.xlsx", slice);
var heatingSystems = heatingsystems1.Where(y => y.CalculatedAverageHeatingEnergyDemandDensityWithNulls != null).ToList();
var rbDict = new Dictionary <string, RowBuilder>();
foreach (var hs in heatingSystems)
{
int bucket = (int)(Math.Floor(hs.CalculatedAverageHeatingEnergyDemandDensityWithNulls / 50 ?? 0) * 50);
string key = bucket.ToString();
string et = hs.EnergyType.ToString();
RowBuilder rb;
if (rbDict.ContainsKey(key))
{
rb = rbDict[key];
}
else
{
rb = RowBuilder.Start("Energieträger", key);
rbDict.Add(key, rb);
}
rb.AddToPossiblyExisting(et, 1);
}
RowCollection rc = new RowCollection("energy", "energy");
foreach (var builder in rbDict)
{
rc.Add(builder.Value);
}
RowCollection rc2 = new RowCollection("raw", "raw");
foreach (var heatingSystemEntry in heatingsystems1)
{
RowBuilder rb = RowBuilder.Start("Träger", heatingSystemEntry.EnergyType)
.Add("Effektiv", heatingSystemEntry.EffectiveEnergyDemand)
.Add("EBF", heatingSystemEntry.Ebf);
rc2.Add(rb);
}
XlsxDumper.WriteToXlsx(xlsfilename, rc, rc2);
}
void MakeHeatingSystemSankey()
{
var ssa1 = new SingleSankeyArrow("HouseHeatingSystems", 1500, MyStage, SequenceNumber, Name, slice, Services);
ssa1.AddEntry(new SankeyEntry("Houses", houses1.Count, 5000, Orientation.Straight));
var ssa2 = new SingleSankeyArrow("EnergyBySystems", 1500, MyStage, SequenceNumber, Name, slice, Services);
ssa2.AddEntry(new SankeyEntry("Houses", heatingsystems1.Sum(x => x.EffectiveEnergyDemand) / 1000000, 5000, Orientation.Straight));
var counts = new Dictionary <HeatingSystemType, int>();
var energy = new Dictionary <HeatingSystemType, double>();
foreach (var entry in heatingsystems1)
{
if (!counts.ContainsKey(entry.SynthesizedHeatingSystemType))
{
counts.Add(entry.SynthesizedHeatingSystemType, 0);
energy.Add(entry.SynthesizedHeatingSystemType, 0);
}
counts[entry.SynthesizedHeatingSystemType]++;
energy[entry.SynthesizedHeatingSystemType] += entry.EffectiveEnergyDemand;
}
var i = 1;
foreach (var pair in counts)
{
ssa1.AddEntry(new SankeyEntry(pair.Key.ToString(), pair.Value * -1, 2000 * i, Orientation.Up));
i++;
}
i = 1;
foreach (var pair in energy)
{
ssa2.AddEntry(new SankeyEntry(pair.Key.ToString(), pair.Value * -1 / 1000000, 2000 * i, Orientation.Up));
i++;
}
Services.PlotMaker.MakeSankeyChart(ssa1);
Services.PlotMaker.MakeSankeyChart(ssa2);
}
void HeatingSystemCountHistogram()
{
var counts = new Dictionary <HeatingSystemType, int>();
foreach (var entry in heatingsystems1)
{
if (!counts.ContainsKey(entry.SynthesizedHeatingSystemType))
{
counts.Add(entry.SynthesizedHeatingSystemType, 0);
}
counts[entry.SynthesizedHeatingSystemType]++;
}
var filename = MakeAndRegisterFullFilename("HeatingSystemHistogram.png", slice);
var names = new List <string>();
var barSeries = new List <BarSeriesEntry>();
var column = 0;
foreach (var pair in counts)
{
names.Add(pair.Value.ToString());
var count = pair.Value;
barSeries.Add(BarSeriesEntry.MakeBarSeriesEntry(pair.Key.ToString(), count, column));
column++;
}
Services.PlotMaker.MakeBarChart(filename, "HeatingSystemHistogram", barSeries, names);
}
void MakeHeatingSystemMapError()
{
RGB GetColor(House h)
{
var hse = heatingsystemsByGuid[h.Guid];
if (hse.OriginalHeatingSystemType == HeatingSystemType.Fernwärme)
{
return(Constants.Red);
}
if (hse.OriginalHeatingSystemType == HeatingSystemType.Gas)
{
return(Constants.Orange);
}
if (hse.OriginalHeatingSystemType == HeatingSystemType.FeuerungsstättenGas)
{
return(Constants.Orange);
}
return(Constants.Black);
}
var mapPoints = houses1.Select(x => x.GetMapPoint(GetColor)).ToList();
var filename = MakeAndRegisterFullFilename("KantonHeatingSystemErrors.svg", slice);
var legendEntries = new List <MapLegendEntry> {
new MapLegendEntry("Kanton Fernwärme", Constants.Red),
new MapLegendEntry("Kanton Gas", Constants.Orange)
};
Services.PlotMaker.MakeMapDrawer(filename, Name, mapPoints, legendEntries);
}
void MakeHeatingSystemMap()
{
var rgbs = new Dictionary <HeatingSystemType, RGB>();
var hs = heatingsystems1.Select(x => x.SynthesizedHeatingSystemType).Distinct().ToList();
var idx = 0;
foreach (var type in hs)
{
rgbs.Add(type, ColorGenerator.GetRGB(idx++));
}
RGB GetColor(House h)
{
var hse = heatingsystemsByGuid[h.Guid];
return(rgbs[hse.SynthesizedHeatingSystemType]);
}
var mapPoints = houses1.Select(x => x.GetMapPoint(GetColor)).ToList();
var filename = MakeAndRegisterFullFilename("HeatingSystemMap.svg", slice);
var legendEntries = new List <MapLegendEntry>();
foreach (var pair in rgbs)
{
legendEntries.Add(new MapLegendEntry(pair.Key.ToString(), pair.Value));
}
Services.PlotMaker.MakeMapDrawer(filename, Name, mapPoints, legendEntries);
}
void MakeHeatingTypeMap()
{
var maxEnergy = heatingsystems1.Max(x => x.EffectiveEnergyDemand);
var colorsByHeatingSystem = new Dictionary <HeatingSystemType, RGB> {
{
HeatingSystemType.Gas, Constants.Orange
}, {
HeatingSystemType.Öl, Constants.Black
}, {
HeatingSystemType.Electricity, Constants.Green
}, {
HeatingSystemType.Heatpump, Constants.Green
}, {
HeatingSystemType.Fernwärme, Constants.Blue
}, {
HeatingSystemType.Other, Constants.Türkis
}, {
HeatingSystemType.None, Constants.Yellow
}
};
RGBWithSize GetColorWithSize(House h)
{
var s = heatingsystemsByGuid[h.Guid];
var energy = Math.Log(s.EffectiveEnergyDemand / maxEnergy * 100) * 50;
if (energy < 10)
{
energy = 10;
}
if (!colorsByHeatingSystem.ContainsKey(s.SynthesizedHeatingSystemType))
{
throw new Exception("undefined color for " + s.SynthesizedHeatingSystemType);
}
var rgb = colorsByHeatingSystem[s.SynthesizedHeatingSystemType];
return(new RGBWithSize(rgb.R, rgb.G, rgb.B, (int)energy));
}
RGB GetColor(House h)
{
var s = heatingsystemsByGuid[h.Guid];
if (!colorsByHeatingSystem.ContainsKey(s.SynthesizedHeatingSystemType))
{
throw new Exception("undefined color for " + s.SynthesizedHeatingSystemType);
}
var rgb = colorsByHeatingSystem[s.SynthesizedHeatingSystemType];
return(new RGB(rgb.R, rgb.G, rgb.B));
}
var mapPoints = houses1.Select(x => x.GetMapPointWithSize(GetColorWithSize)).ToList();
var filename = MakeAndRegisterFullFilename("MapHeatingTypeAndSystemPerHousehold.svg", slice);
var legendEntries = new List <MapLegendEntry>();
foreach (var pair in colorsByHeatingSystem)
{
legendEntries.Add(new MapLegendEntry(pair.Key.ToString(), pair.Value));
}
Services.PlotMaker.MakeMapDrawer(filename, Name, mapPoints, legendEntries);
var filenameOsm = MakeAndRegisterFullFilename("MapHeatingTypeAndSystemPerHouseholdOsm.png", slice);
legendEntries.Add(new MapLegendEntry("Nicht Stadtgebiet", Constants.Red));
var mceh = houses1.Select(x => x.GetMapColorForHouse(GetColor)).ToList();
Services.PlotMaker.MakeOsmMap("HeatingTypeMap", filenameOsm, mceh, new List <WgsPoint>(), legendEntries, new List <LineEntry>());
}
void MakeFernwärmeTypeMap()
{
RGBWithLabel GetColor(House h)
{
var s = heatingsystemsByGuid[h.Guid];
if (s.SynthesizedHeatingSystemType == HeatingSystemType.Fernwärme)
{
return(new RGBWithLabel(Constants.Green, ""));
}
return(new RGBWithLabel(Constants.Blue, "")); //h.ComplexName
}
var legendEntries = new List <MapLegendEntry>();
var filenameOsm = MakeAndRegisterFullFilename("FernwärmeOSM.png", slice);
legendEntries.Add(new MapLegendEntry("Nicht Stadtgebiet", Constants.Red));
legendEntries.Add(new MapLegendEntry("Nicht Fernwärme", Constants.Blue));
legendEntries.Add(new MapLegendEntry("Fernwärme", Constants.Green));
var mceh = houses1.Select(x => x.GetMapColorForHouse(GetColor)).ToList();
Services.PlotMaker.MakeOsmMap("HeatingTypeMap", filenameOsm, mceh, new List <WgsPoint>(), legendEntries, new List <LineEntry>());
}
void MakeHeatingTypeIntensityMap()
{
var colorsByHeatingSystem = new Dictionary <HeatingSystemType, RGB> {
{
HeatingSystemType.Gas, Constants.Orange
}, {
HeatingSystemType.Öl, Constants.Black
}, {
HeatingSystemType.Electricity, Constants.Green
}, {
HeatingSystemType.Heatpump, Constants.Green
}, {
HeatingSystemType.Fernwärme, Constants.Blue
}, {
HeatingSystemType.Other, Constants.Türkis
}, {
HeatingSystemType.None, Constants.Türkis
}
};
RGBWithSize GetColor(House h)
{
var s = heatingsystemsByGuid[h.Guid];
var energy = s.CalculatedAverageHeatingEnergyDemandDensity / 10;
if (energy < 10)
{
energy = 10;
}
if (!colorsByHeatingSystem.ContainsKey(s.SynthesizedHeatingSystemType))
{
throw new Exception("undefined color for " + s.SynthesizedHeatingSystemType);
}
var rgb = colorsByHeatingSystem[s.SynthesizedHeatingSystemType];
return(new RGBWithSize(rgb.R, rgb.G, rgb.B, (int)energy));
}
var mapPoints = houses1.Select(x => x.GetMapPointWithSize(GetColor)).ToList();
var filename = MakeAndRegisterFullFilename("MapHeatingTypeAndIntensityPerHouse.svg", slice);
var legendEntries = new List <MapLegendEntry>();
foreach (var pair in colorsByHeatingSystem)
{
legendEntries.Add(new MapLegendEntry(pair.Key.ToString(), pair.Value));
}
Services.PlotMaker.MakeMapDrawer(filename, Name, mapPoints, legendEntries);
}
}
public RowCollectionFilterItem()
{
InitializeComponent();
this.rowCollection = null;
}
public virtual void VisitRowCollection(RowCollection coll)
{
}
protected override void RunChartMaking()
{
var slice = Constants.PresentSlice;
//var dbHouse = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Houses, Constants.PresentSlice);
var ap = new AnalysisRepository(Services.RunningConfig);
var dbHouse = ap.GetSlice(Constants.PresentSlice);
var houses = dbHouse.Fetch <House>();
var houseTypeEntries = dbHouse.Fetch <HouseTypeEntry>();
MakeHouseTypeEntries();
MakeHouseTypeMap();
MakeEnergyUseXls();
void MakeEnergyUseXls()
{
var households = dbHouse.Fetch <Household>();
var businesses = dbHouse.Fetch <BusinessEntry>();
var infra = dbHouse.Fetch <BuildingInfrastructure>();
var light = dbHouse.Fetch <StreetLightingEntry>();
var dhw = dbHouse.Fetch <DHWHeaterEntry>();
var rc = new RowCollection("energy", "energy");
RowBuilder rb = RowBuilder.Start("Haushalte", households.Sum(x => x.EffectiveEnergyDemand) / Constants.GWhFactor);
var bigcustomers = businesses.Where(x => x.EffectiveEnergyDemand > 20000);
var small = businesses.Where(x => x.EffectiveEnergyDemand <= 20000);
rb.Add("Geschäftskunden > 20 MWh", bigcustomers.Sum(x => x.EffectiveEnergyDemand) / Constants.GWhFactor);
rb.Add("Geschäftskunden < 20 MWh", small.Sum(x => x.EffectiveEnergyDemand) / Constants.GWhFactor);
rb.Add("Gebäudeinfrastruktur", infra.Sum(x => x.EffectiveEnergyDemand) / Constants.GWhFactor);
rb.Add("Strassenbeleuchtung", light.Sum(x => x.YearlyElectricityUse) / Constants.GWhFactor);
rb.Add("Elektroboiler", dhw.Sum(x => x.EffectiveEnergyDemand) / Constants.GWhFactor);
rc.Add(rb);
var fn = MakeAndRegisterFullFilename("EnergyTreeMap.xlsx", Constants.PresentSlice);
XlsxDumper.WriteToXlsx(fn, rc);
SaveToPublicationDirectory(fn, Constants.PresentSlice, "4.2");
}
void MakeHouseTypeEntries()
{
var ssa = new SingleSankeyArrow("HouseTypeEntries", 1500, MyStage,
SequenceNumber, Name, slice, Services);
ssa.AddEntry(new SankeyEntry("Houses", houses.Count, 5000, Orientation.Straight));
var countsPerType = new Dictionary <HouseType, int>();
foreach (var entry in houseTypeEntries)
{
if (!countsPerType.ContainsKey(entry.HouseType))
{
countsPerType.Add(entry.HouseType, 0);
}
countsPerType[entry.HouseType]++;
}
foreach (var pair in countsPerType)
{
ssa.AddEntry(new SankeyEntry(pair.Key.ToString(), pair.Value * -1, 2000, Orientation.Up));
}
Services.PlotMaker.MakeSankeyChart(ssa);
}
void MakeHouseTypeMap()
{
var rgbs = new Dictionary <HouseType, RGB>();
var hs = houseTypeEntries.Select(x => x.HouseType).Distinct().ToList();
var idx = 0;
foreach (var type in hs)
{
var rgb = ColorGenerator.GetRGB(idx++);
if (rgb.R == 255 && rgb.B == 255 && rgb.G == 255)
{
rgb = new RGB(200, 200, 200);
}
if (rgb.R == 255 && rgb.B == 0 && rgb.G == 0)
{
rgb = Constants.Orange;
}
rgbs.Add(type, rgb);
}
RGB GetColor(House h)
{
var hse = houseTypeEntries.Single(x => x.HouseGuid == h.Guid);
return(rgbs[hse.HouseType]);
}
var mapPoints = houses.Select(x => x.GetMapColorForHouse(GetColor)).ToList();
var filename = MakeAndRegisterFullFilename("HouseTypeMap.png", slice);
var legendEntries = new List <MapLegendEntry>();
foreach (var pair in rgbs)
{
legendEntries.Add(new MapLegendEntry(pair.Key.ToString(), pair.Value));
}
Services.PlotMaker.MakeOsmMap(Name, filename, mapPoints, new List <WgsPoint>(), legendEntries, new List <LineEntry>());
}
}
public void ReadXml(XmlReader reader)
{
var xml = new XmlHelper(reader);
CharacterId = xml.getLongAttribute("characterID");
CharacterName = xml.getStringAttribute("name");
Roles = xml.deserializeRowSet<Role>("roles");
GrantableRoles = xml.deserializeRowSet<Role>("grantableRoles");
RolesAtHq = xml.deserializeRowSet<Role>("rolesAtHQ");
GrantableRolesAtHq = xml.deserializeRowSet<Role>("grantableRolesAtHQ");
RolesAtBase = xml.deserializeRowSet<Role>("rolesAtBase");
GrantableRolesAtBase = xml.deserializeRowSet<Role>("grantableRolesAtBase");
RolesAtOther = xml.deserializeRowSet<Role>("rolesAtOther");
GrantableRolesAtOther = xml.deserializeRowSet<Role>("grantableRolesAtOther");
Titles = xml.deserializeRowSet<Title>("titles");
}
/// <summary>
/// Initializes a new instance of the <see cref="Table">Table</see> class.
/// </summary>
/// <param name="mygrid">The Grid that is the parent object of the table.</param>
/// <param name="gotcache">if set to <c>true</c> [m_ gotcache].</param>
public Table(Grid mygrid, bool gotcache)
{
m_Grid = mygrid;
GotCache = gotcache;
m_Rows = new RowCollection(this);
m_Columns = new ColumnCollection(this);
}
public override void setReference(int refId, IPersists obj)
{
base.setReference(refId, obj);
switch (refId)
{
case 1:
cells = (CellCollection)obj;
break;
case 2:
rowsList = (RowCollection)obj;
break;
case 3:
colsList = (ColumnCollection)obj;
break;
case 4:
brush.Release();
brush = (Brush)obj;
brush.AddRef();
break;
case 5:
incomingArrows = (ArrowCollection)obj;
break;
case 6:
outgoingArrows = (ArrowCollection)obj;
break;
case 7:
if (captionBackBrush != null)
captionBackBrush.Release();
captionBackBrush = (Brush)obj;
if (captionBackBrush != null)
captionBackBrush.AddRef();
break;
}
}
/// <summary>
/// Initializes a new instance of the <see cref="Table">Table</see> class.
/// </summary>
internal Table(Grid mygrid)
{
m_Grid = mygrid;
//DBInterface = new Database.SqlConnection();
m_Rows = new RowCollection(this);
m_Columns = new ColumnCollection(this);
}
public RowCollection Clone()
{
RowCollection rc = new RowCollection();
foreach (Row r in this)
rc.Add(r.Clone());
return rc;
}
protected override void RunActualProcess()
{
var dbHouses = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Houses, Constants.PresentSlice);
var dbHousesPersistence =
Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Houses, Constants.PresentSlice, DatabaseCode.Persistence);
dbHousesPersistence.CreateTableIfNotExists <PersistentHouseholdResidents>();
var dbRaw = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice);
var households = dbHouses.Fetch <Household>();
var jahrgänge = dbRaw.Fetch <Jahrgang>();
if (households.Count == 0)
{
throw new Exception("no households founds");
}
if (households.Count != A05_HouseholdMaker.HouseholdAccordingToStadtverwaltung)
{
throw new FlaException("Missing households!?");
}
foreach (var household in households)
{
household.Occupants.Clear();
}
var persistentResidents = dbHousesPersistence.Fetch <PersistentHouseholdResidents>();
int householdCountBefore = households.Count;
dbHouses.BeginTransaction();
Info("found " + persistentResidents.Count + " persistent residents");
foreach (var persistentHouseholdResidentse in persistentResidents)
{
var household = households.FirstOrDefault(x => x.HouseholdKey == persistentHouseholdResidentse.HouseholdKey);
if (household == null)
{
Info("Invalid persistence for " + persistentHouseholdResidentse.HouseholdKey);
dbHousesPersistence.Delete(persistentHouseholdResidentse);
continue;
}
foreach (var persistentOccupant in persistentHouseholdResidentse.Occupants)
{
var occupant = new Occupant(household.Guid,
Guid.NewGuid().ToString(),
persistentOccupant.Age,
persistentOccupant.Gender,
household.HouseGuid,
household.HouseholdKey);
household.Occupants.Add(occupant);
var jahrgang = 2018 - persistentOccupant.Age;
var einträge = jahrgänge.Single(x => x.Jahr == jahrgang);
einträge.Count--;
if (einträge.Count < 0)
{
throw new FlaException("Negative population");
}
}
dbHouses.Save(household);
households.Remove(household);
}
Info("Covered " + (householdCountBefore - households.Count) + " households from persistence, households left: " + households.Count);
var hmfc = new HouseMemberFuzzyCalc(Services.MyLogger, MyStage);
var potentialPersons = new List <PotentialPerson>();
foreach (var jahrgang in jahrgänge)
{
var age = 2018 - jahrgang.Jahr;
var g = Gender.Male;
for (var i = 0; i < jahrgang.Count; i++)
{
var pp = new PotentialPerson(g, age);
potentialPersons.Add(pp);
g = g == Gender.Male ? Gender.Female : Gender.Male;
}
}
var r = new Random(1);
foreach (var household in households)
{
household.HeuristicFamiliySize = hmfc.GetPeopleCountForEnergy(household.EffectiveEnergyDemand);
household.Occupants.Clear();
}
//put at least one person into every household
foreach (var household in households)
{
var eligiablePersons = potentialPersons.Where(x => x.Age >= 18).ToList();
var occ = MakeOccupant(eligiablePersons, r, potentialPersons, household);
household.Occupants.Add(occ);
dbHouses.Save(occ);
}
//put a second person into the households that might have a second one
foreach (var household in households)
{
if (household.HeuristicFamiliySize < 2)
{
continue;
}
var g = household.Occupants[0].Gender;
var otherGender = g == Gender.Male ? Gender.Female : Gender.Male;
var eligiablePersons = potentialPersons.Where(x => x.Age >= 18 && x.Gender == otherGender).ToList();
if (eligiablePersons.Count == 0)
{
eligiablePersons = potentialPersons;
}
var occ2 = MakeOccupant(eligiablePersons, r, potentialPersons, household);
household.Occupants.Add(occ2);
}
var count = 0;
while (potentialPersons.Count > 0)
{
count++;
if (count > 100000)
{
throw new Exception("Couldnt allocate everything after " + count + " iterations," + potentialPersons.Count + " left.");
}
var allocatedCount = 0;
foreach (var household in households)
{
if (household.Occupants.Count >= household.HeuristicFamiliySize)
{
continue;
}
if (potentialPersons.Count == 0)
{
break;
}
var eligiablePersonsKids = potentialPersons.Where(x => x.Age < 18).ToList();
if (eligiablePersonsKids.Count == 0)
{
eligiablePersonsKids = potentialPersons;
}
var occ3 = MakeOccupant(eligiablePersonsKids, r, potentialPersons, household);
household.Occupants.Add(occ3);
allocatedCount++;
}
if (allocatedCount == 0 && potentialPersons.Count > 0)
{
var hhs = households.Where(x => x.HeuristicFamiliySize > 2).ToList();
if (hhs.Count == 0)
{
hhs = households;
}
var idx = Services.Rnd.Next(hhs.Count);
hhs[idx].HeuristicFamiliySize++;
}
}
List <PersistentHouseholdResidents> newPersistentResidents = new List <PersistentHouseholdResidents>();
int peopleCount = 0;
foreach (var hh in households)
{
dbHouses.Save(hh);
PersistentHouseholdResidents phhr = new PersistentHouseholdResidents(hh.HouseholdKey);
foreach (var occupant in hh.Occupants)
{
phhr.Occupants.Add(new PersistentOccupant(occupant.Age, occupant.Gender));
peopleCount++;
}
newPersistentResidents.Add(phhr);
}
dbHouses.CompleteTransaction();
dbHousesPersistence.BeginTransaction();
foreach (var phhr in newPersistentResidents)
{
dbHousesPersistence.Save(phhr);
}
Info("Saved " + newPersistentResidents.Count + " persistence records with a total of " + peopleCount + " people");
dbHousesPersistence.CompleteTransaction();
var allhouseholds = dbHouses.FetchAsRepo <Household>();
var rc = new RowCollection("occupants", "occupants");
foreach (var hh in allhouseholds)
{
foreach (var occupant in hh.Occupants)
{
var rb = RowBuilder.Start("age", occupant.Age).Add("Gender", occupant.Gender);
rc.Add(rb);
}
}
var fn = MakeAndRegisterFullFilename("OccupantList.xlsx", Constants.PresentSlice);
XlsxDumper.WriteToXlsx(fn, rc);
}
