static int Count7(Table<Child> ch, Parent p, int n)
{
return (_count7Expression ?? (_count7Expression = Count7Expression().Compile()))(ch, p, n);
}
static int Count6(Table<Child> c, Parent p)
{
return (_count6Expression ?? (_count6Expression = Count6Expression().Compile()))(c, p);
}
public bool RemoveTable(Table Table)
{
return this.tables.Remove(Table);
}
private static List<Table> FindTable(List<Table> lstTable, Table Table)
{
List<Table> lbt = new List<Table>();
foreach (Table bt in lstTable)
{
if (bt.Name.ToUpper().Trim() == Table.Name.ToUpper().Trim())
{
lbt.Add(bt);
}
}
return lbt;
}
public bool AddTable(string tableName)
{
tableName = tableName.Trim();
foreach (Table table in this.tables)
{
if (table.Name.ToUpper().Trim() == tableName.ToUpper().Trim())
{
return false;
}
}
Table newTable = new Table(tableName.Trim());
FillSchemaTable(newTable as BaseTable, GetSchemaTable(this.connectionString, tableName.Trim()));
this.tables.Add(newTable);
return true;
}
public void GetAllTable()
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
this.tables.Clear();
foreach (string tableName in GetDatabaseObjectList(this.connectionString, DatabaseObjectType.Table))
{
Table table = new Table(tableName.Trim());
FillSchemaTable(table as BaseTable, GetSchemaTable(this.connectionString, tableName.Trim()));
this.tables.Add(table);
}
}
public bool AddTable(Table Table)
{
foreach (Table table in this.tables)
{
if (table.Name.ToUpper().Trim() == Table.Name.ToUpper().Trim())
{
return false;
}
}
this.tables.Add(Table);
return true;
}
public async Task <Table> GetThirtyDayOutputSeries(DateTime dataTo, DateTime dataFrom, TargetViewModel[] queryToPreform, string tablename)
{
var outputSeries = new List <DataSeries <int> >();
var entityList = await _newTimeSeriesData.GetDataSeriesData(dataTo, dataFrom, tablename);
var wiserHeatResultList = new List <int> {
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
};
var auraConnectResultList = new List <int> {
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
};
int maxIndex = entityList.Count - 1;
var wiserHeatActiveConnection = (string)typeof(DeviceCounts)
.GetProperty("ThirtyDayWiserHeatActiveConnections").GetValue(entityList[maxIndex]);
var auraConnectActiveConnection = (string)typeof(DeviceCounts)
.GetProperty("ThirtyDayAuraConnectActiveConnections").GetValue(entityList[maxIndex]);
JArray wiserHeatResult = (JArray)JsonConvert.DeserializeObject(wiserHeatActiveConnection);
JArray auraConnectResult = (JArray)JsonConvert.DeserializeObject(auraConnectActiveConnection);
wiserHeatResultList = wiserHeatResult.ToObject <List <int> >();
auraConnectResultList = auraConnectResult.ToObject <List <int> >();
var dayNow = DateTime.Now;
var table = new Table
{
columns = new List <Column>
{
new Column {
text = "Day", type = "string"
},
new Column {
text = "DataSource", type = "string"
},
new Column {
text = "Number", type = "number"
},
},
rows = new List <object>
{
new List <object> {
dayNow.AddDays(-29).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[0]
}, new List <object> {
dayNow.AddDays(-29).ToString("dd/MM"), "AuraConnect", auraConnectResultList[0]
},
new List <object> {
dayNow.AddDays(-28).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[1]
}, new List <object> {
dayNow.AddDays(-28).ToString("dd/MM"), "AuraConnect", auraConnectResultList[1]
},
new List <object> {
dayNow.AddDays(-27).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[2]
}, new List <object> {
dayNow.AddDays(-27).ToString("dd/MM"), "AuraConnect", auraConnectResultList[2]
},
new List <object> {
dayNow.AddDays(-26).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[3]
}, new List <object> {
dayNow.AddDays(-26).ToString("dd/MM"), "AuraConnect", auraConnectResultList[3]
},
new List <object> {
dayNow.AddDays(-25).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[4]
}, new List <object> {
dayNow.AddDays(-25).ToString("dd/MM"), "AuraConnect", auraConnectResultList[4]
},
new List <object> {
dayNow.AddDays(-24).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[5]
}, new List <object> {
dayNow.AddDays(-24).ToString("dd/MM"), "AuraConnect", auraConnectResultList[5]
},
new List <object> {
dayNow.AddDays(-23).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[6]
}, new List <object> {
dayNow.AddDays(-23).ToString("dd/MM"), "AuraConnect", auraConnectResultList[6]
},
new List <object> {
dayNow.AddDays(-22).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[7]
}, new List <object> {
dayNow.AddDays(-22).ToString("dd/MM"), "AuraConnect", auraConnectResultList[7]
},
new List <object> {
dayNow.AddDays(-21).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[8]
}, new List <object> {
dayNow.AddDays(-21).ToString("dd/MM"), "AuraConnect", auraConnectResultList[8]
},
new List <object> {
dayNow.AddDays(-20).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[9]
}, new List <object> {
dayNow.AddDays(-20).ToString("dd/MM"), "AuraConnect", auraConnectResultList[9]
},
new List <object> {
dayNow.AddDays(-19).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[10]
}, new List <object> {
dayNow.AddDays(-19).ToString("dd/MM"), "AuraConnect", auraConnectResultList[10]
},
new List <object> {
dayNow.AddDays(-18).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[11]
}, new List <object> {
dayNow.AddDays(-18).ToString("dd/MM"), "AuraConnect", auraConnectResultList[11]
},
new List <object> {
dayNow.AddDays(-17).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[12]
}, new List <object> {
dayNow.AddDays(-17).ToString("dd/MM"), "AuraConnect", auraConnectResultList[12]
},
new List <object> {
dayNow.AddDays(-16).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[13]
}, new List <object> {
dayNow.AddDays(-16).ToString("dd/MM"), "AuraConnect", auraConnectResultList[13]
},
new List <object> {
dayNow.AddDays(-15).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[14]
}, new List <object> {
dayNow.AddDays(-15).ToString("dd/MM"), "AuraConnect", auraConnectResultList[14]
},
new List <object> {
dayNow.AddDays(-14).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[15]
}, new List <object> {
dayNow.AddDays(-14).ToString("dd/MM"), "AuraConnect", auraConnectResultList[15]
},
new List <object> {
dayNow.AddDays(-13).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[16]
}, new List <object> {
dayNow.AddDays(-13).ToString("dd/MM"), "AuraConnect", auraConnectResultList[16]
},
new List <object> {
dayNow.AddDays(-12).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[17]
}, new List <object> {
dayNow.AddDays(-12).ToString("dd/MM"), "AuraConnect", auraConnectResultList[17]
},
new List <object> {
dayNow.AddDays(-11).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[18]
}, new List <object> {
dayNow.AddDays(-11).ToString("dd/MM"), "AuraConnect", auraConnectResultList[18]
},
new List <object> {
dayNow.AddDays(-10).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[19]
}, new List <object> {
dayNow.AddDays(-10).ToString("dd/MM"), "AuraConnect", auraConnectResultList[19]
},
new List <object> {
dayNow.AddDays(-9).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[20]
}, new List <object> {
dayNow.AddDays(-9).ToString("dd/MM"), "AuraConnect", auraConnectResultList[20]
},
new List <object> {
dayNow.AddDays(-8).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[21]
}, new List <object> {
dayNow.AddDays(-8).ToString("dd/MM"), "AuraConnect", auraConnectResultList[21]
},
new List <object> {
dayNow.AddDays(-7).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[22]
}, new List <object> {
dayNow.AddDays(-7).ToString("dd/MM"), "AuraConnect", auraConnectResultList[22]
},
new List <object> {
dayNow.AddDays(-6).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[23]
}, new List <object> {
dayNow.AddDays(-6).ToString("dd/MM"), "AuraConnect", auraConnectResultList[23]
},
new List <object> {
dayNow.AddDays(-5).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[24]
}, new List <object> {
dayNow.AddDays(-5).ToString("dd/MM"), "AuraConnect", auraConnectResultList[24]
},
new List <object> {
dayNow.AddDays(-4).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[25]
}, new List <object> {
dayNow.AddDays(-4).ToString("dd/MM"), "AuraConnect", auraConnectResultList[25]
},
new List <object> {
dayNow.AddDays(-3).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[26]
}, new List <object> {
dayNow.AddDays(-3).ToString("dd/MM"), "AuraConnect", auraConnectResultList[26]
},
new List <object> {
dayNow.AddDays(-2).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[27]
}, new List <object> {
dayNow.AddDays(-2).ToString("dd/MM"), "AuraConnect", auraConnectResultList[27]
},
new List <object> {
dayNow.AddDays(-1).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[28]
}, new List <object> {
dayNow.AddDays(-1).ToString("dd/MM"), "AuraConnect", auraConnectResultList[28]
},
new List <object> {
dayNow.AddDays(0).ToString("dd/MM"), "WiserHeat", wiserHeatResultList[29]
}, new List <object> {
dayNow.AddDays(0).ToString("dd/MM"), "AuraConnect", auraConnectResultList[29]
},
}
};
return(table);
}
public async Task <Table> GetOutputBarChart(DateTime dataTo, DateTime dataFrom, TargetViewModel[] queryToPreform, string tablename)
{
var outputSeries = new List <DataSeries <int> >();
var entityList = await _newTimeSeriesData.GetDataSeriesData(dataTo, dataFrom, tablename);
var wiserHeatResultList = new List <int> {
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
};
var auraConnectResultList = new List <int> {
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
};
foreach (TargetViewModel queryName in queryToPreform)
{
foreach (DeviceCounts device in entityList)
{
var wiserHeatValue = (string)typeof(DeviceCounts).GetProperty("WiserHeatBrandCount").GetValue(device);
var auraConnectValue = (string)typeof(DeviceCounts).GetProperty("AuraConnectBrandCount").GetValue(device);
JArray wiserHeatResult = (JArray)JsonConvert.DeserializeObject(wiserHeatValue);
JArray auraConnectResult = (JArray)JsonConvert.DeserializeObject(auraConnectValue);
wiserHeatResultList = wiserHeatResult.ToObject <List <int> >();
auraConnectResultList = auraConnectResult.ToObject <List <int> >();
}
}
var table = new Table
{
columns = new List <Column>
{
new Column {
text = "Month", type = "string"
},
new Column {
text = "DataSource", type = "string"
},
new Column {
text = "Number", type = "number"
},
},
rows = new List <object>
{
new List <object> {
"Jan", "WiserHeat", wiserHeatResultList[0]
}, new List <object> {
"Jan", "AuraConnect", auraConnectResultList[0]
},
new List <object> {
"Feb", "WiserHeat", wiserHeatResultList[1]
}, new List <object> {
"Feb", "AuraConnect", auraConnectResultList[1]
},
new List <object> {
"Mar", "WiserHeat", wiserHeatResultList[2]
}, new List <object> {
"Mar", "AuraConnect", auraConnectResultList[2]
},
new List <object> {
"Apr", "WiserHeat", wiserHeatResultList[3]
}, new List <object> {
"Apr", "AuraConnect", auraConnectResultList[3]
},
new List <object> {
"May", "WiserHeat", wiserHeatResultList[4]
}, new List <object> {
"May", "AuraConnect", auraConnectResultList[4]
},
new List <object> {
"Jun", "WiserHeat", wiserHeatResultList[5]
}, new List <object> {
"Jun", "AuraConnect", auraConnectResultList[5]
},
new List <object> {
"Jul", "WiserHeat", wiserHeatResultList[6]
}, new List <object> {
"Jul", "AuraConnect", auraConnectResultList[6]
},
new List <object> {
"Aug", "WiserHeat", wiserHeatResultList[7]
}, new List <object> {
"Aug", "AuraConnect", auraConnectResultList[7]
},
new List <object> {
"Sep", "WiserHeat", wiserHeatResultList[8]
}, new List <object> {
"Sep", "AuraConnect", auraConnectResultList[8]
},
new List <object> {
"Oct", "WiserHeat", wiserHeatResultList[9]
}, new List <object> {
"Oct", "AuraConnect", auraConnectResultList[9]
},
new List <object> {
"Nov", "WiserHeat", wiserHeatResultList[10]
}, new List <object> {
"Nov", "AuraConnect", auraConnectResultList[10]
},
new List <object> {
"Dec", "WiserHeat", wiserHeatResultList[11]
}, new List <object> {
"Dec", "AuraConnect", auraConnectResultList[11]
},
}
};
return(table);
}
private Table ReadSheet(Worksheet sheet, Range SchemaRange)
{
Table table = new Table();
table.Name = ((Range)(sheet.Cells[1, 2])).Value2.ToString();
table.Description = ((Range)(sheet.Cells[2, 2])).Value2.ToString();
for (int rowIndex = sheet.UsedRange.Row + 4; rowIndex <= sheet.UsedRange.Row + sheet.UsedRange.Rows.Count - 1; rowIndex++)
{
Column column = new Column();
column.Name = GetValue(sheet, rowIndex, 1);
column.Type = GetValue(sheet, rowIndex, 2);
//column.Length = GetValue(sheet, rowIndex, 3);
//column.Decimals = GetValue(sheet, rowIndex, 4);
column.IsCanNull = GetValue(sheet, rowIndex, 3) == "Y";
column.IsPrimaryKey = GetValue(sheet, rowIndex, 4) == "Y";
column.DefaultValue = GetValue(sheet, rowIndex, 5);
column.IsIdentity = GetValue(sheet, rowIndex, 6) == "Y";
column.Description = GetValue(sheet, rowIndex, 7);
table.Columns.Add(column);
}
string errmsg = "";
textBox1.Text = JsonHelperClass.ObjectToJson<Table>(table, ref errmsg);
return table;
}
// SET FOREIGN_KEY_CHECKS=0;
/// <summary>
/// 生成CreateSQL语句
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static string GenerateCreateSql(Table table, bool dropIfExists, bool ifNotExists)
{
StringBuilder sbSql = new StringBuilder();
if (dropIfExists)
{
sbSql.AppendFormat("DROP TABLE IF EXISTS `{0}`;", table.Name);
sbSql.AppendLine();
}
sbSql.AppendFormat(ParserOptions("CREATE [TEMPORARY] TABLE [IF NOT EXISTS] `{0}` (", false, ifNotExists), table.Name);
sbSql.AppendLine();
List<string> primaryKeys = new List<string>();
List<string> columnDefs = new List<string>();
foreach (Column column in table.Columns)
{
StringBuilder sbColumnDef = new StringBuilder();
// col_name type
sbColumnDef.AppendFormat(" `{0}` {1} ", column.Name, column.Type);
// [NOT NULL | NULL]
if (column.IsCanNull)
{
sbColumnDef.Append("NULL ");
}
else
{
sbColumnDef.Append("NOT NULL ");
}
// [DEFAULT default_value]
if (column.DefaultValue != null && column.DefaultValue != "")
{
sbColumnDef.AppendFormat("DEFAULT '{0}' ", column.DefaultValue);
}
// [AUTO_INCREMENT]
if (column.IsIdentity)
{
sbColumnDef.Append("AUTO_INCREMENT ");
}
// [UNIQUE [KEY] | [PRIMARY] KEY]
// [COMMENT 'string']
if (column.Description != null && column.Description != "")
{
sbColumnDef.AppendFormat("COMMENT '{0}' ", column.Description);
}
// [reference_definition]
// 加入
columnDefs.Add(sbColumnDef.ToString().TrimEnd());
// 约束
if (column.IsPrimaryKey)
{
primaryKeys.Add("`" + column.Name + "`");
}
}
// 约束 PRIMARY KEY (`id`, `new_tablecol`),
string primaryStr = string.Join(", ", primaryKeys.ToArray());
columnDefs.Add(" PRIMARY KEY (" + primaryStr + ")");
sbSql.Append(string.Join("," + Environment.NewLine, columnDefs.ToArray()));
sbSql.AppendLine(")");
// COMMENT = 'string'
if (table.Description != null && table.Description != "")
{
sbSql.AppendFormat("COMMENT = '{0}' ", table.Description);
}
return sbSql.ToString().TrimEnd() + ";" + Environment.NewLine;
}
