public void Import(ExcelPackage Package, bool ClearDatabase)
{
Clr clr = new Clr();
if (ClearDatabase) {
Cleaner cleander = new Cleaner();
cleander.Clear();
}
Dictionary<ulong, object> objects = new Dictionary<ulong, object>();
List<ImporterRelation> relations = new List<ImporterRelation>();
Db.Transact(() => {
for (int i = 2; i <= Package.Workbook.Worksheets.Count; i++) {
ExcelWorksheet sheet = Package.Workbook.Worksheets[i];
this.ExportTable(sheet, objects, relations);
}
foreach (ImporterRelation r in relations) {
this.ApplyRelation(objects, r);
}
});
}
protected void ExportTable(ExcelWorksheet Sheet, Dictionary<ulong, object> Objects, List<ImporterRelation> Relations)
{
string tableName = Sheet.Cells[1, 1].Value.ToString();
ClrClass clrTable = Db.SQL<ClrClass>("SELECT t FROM Starcounter.Metadata.ClrClass t WHERE t.FullName = ?", tableName).First;
int rowIndex = 2;
//1 - ObjectId
//2 - ObjectNo
int colIndex = 3;
Clr clr = new Clr();
List<PropertyInfo> properties = new List<PropertyInfo>();
System.Type type = clr.GetType(clrTable);
while (!string.IsNullOrEmpty(Sheet.Cells[rowIndex, colIndex].Value as string)) {
string name = Sheet.Cells[rowIndex, colIndex].Value.ToString();
PropertyInfo pi = type.GetProperty(name);
properties.Add(pi);
colIndex++;
}
rowIndex++;
colIndex = 1;
while (!string.IsNullOrEmpty(Sheet.Cells[rowIndex, colIndex].Value as string)) {
object obj = Activator.CreateInstance(type);
string objectId = Sheet.Cells[rowIndex, colIndex++].Value as string;
ulong objectNo = ulong.Parse(Sheet.Cells[rowIndex, colIndex++].Value.ToString());
Objects.Add(objectNo, obj);
for (int i = 0; i < properties.Count; i++) {
PropertyInfo pi = properties[i];
object value = Sheet.Cells[rowIndex, colIndex++].Value;
if (value == null) {
continue;
}
ImporterRelation r = clr.ParseRelation(obj, value);
if (r != null) {
r.Property = pi;
Relations.Add(r);
continue;
}
pi.SetValue(obj, Convert.ChangeType(value, pi.PropertyType));
}
colIndex = 1;
rowIndex++;
}
}
protected override void OnData()
{
base.OnData();
Clr clr = new Clr();
Importer importer = new Importer();
this.Tables.Data = clr.SelectUserTables().Where(x => x.FullName.Contains(this.Filter));
foreach (var item in this.Tables) {
item.Selected = true;
}
}
/// <summary>
/// Removes all data from tables provided
/// </summary>
public void Clear(string[] TableNames)
{
Clr clr = new Clr();
Db.Transact(() => {
foreach (string table in TableNames) {
string name = clr.EscapeTableName(table);
string sql = string.Format("DELETE FROM {0}", name);
Db.SlowSQL(sql);
}
});
}
/// <summary>
/// Removes all data from user defined tables
/// </summary>
public void Clear()
{
Clr clr = new Clr();
this.Clear(clr.SelectUserTables().Select(x => x.FullName).ToArray());
}
public ExcelPackage Export(string[] TableNames)
{
Dictionary<ulong, TableContainer> dictionary = new Dictionary<ulong, TableContainer>();
List<ulong> selectedObjects = new List<ulong>();
foreach (string name in TableNames) {
ClrClass meta = Db.SQL<ClrClass>("SELECT t FROM Starcounter.Metadata.ClrClass t WHERE t.FullName = ?", name).First;
if (meta == null) {
throw new ArgumentException(string.Format("Invalid table name: {0}!", name));
}
TableContainer table;
if (!dictionary.TryGetValue(meta.GetObjectNo(), out table)) {
table = new TableContainer();
dictionary.Add(meta.GetObjectNo(), table);
}
table.Meta = meta;
if (meta.Inherits == null) {
continue;
}
TableContainer parent;
if (!dictionary.TryGetValue(meta.Inherits.GetObjectNo(), out parent)) {
parent = new TableContainer();
dictionary.Add(meta.Inherits.GetObjectNo(), parent);
}
table.Parent = parent;
parent.Children.Add(table);
}
Clr clr = new Clr();
ExcelPackage package = new ExcelPackage();
ExcelWorksheet sheet = package.Workbook.Worksheets.Add("Legend");
int row = 1;
sheet.Cells[row, 1].Value = "Table";
sheet.Cells[row, 2].Value = "Objects";
while (dictionary.Any()) {
var leaves = dictionary.Where(x => x.Value.Children.Count == 0).ToList();
foreach (var container in leaves.ToList()) {
string tableName = clr.EscapeTableName(container.Value.Meta.FullName);
string sql = string.Format("SELECT o FROM {0} o", tableName);
QueryResultRows<object> objects = Db.SQL(sql);
foreach (object obj in objects) {
if (selectedObjects.Contains(obj.GetObjectNo())) {
continue;
}
selectedObjects.Add(obj.GetObjectNo());
container.Value.Objects.Add(obj);
}
}
foreach (var leaf in leaves) {
row++;
sheet.Cells[row, 1].Value = leaf.Value.Meta.FullName;
sheet.Cells[row, 2].Value = leaf.Value.Objects.Count;
ExportTable(package, leaf.Value);
if (leaf.Value.Parent != null) {
leaf.Value.Parent.Children.Remove(leaf.Value);
}
dictionary.Remove(leaf.Value.Meta.GetObjectNo());
}
}
sheet.Cells.AutoFitColumns();
return package;
}