/// <summary>
/// 获取要更新的数据
/// </summary>
/// <param name="mapping">数据映射</param>
/// <param name="initialValue">初值</param>
/// <param name="timeSpan">时间间隔</param>
/// <returns></returns>
private DataTable GetData2Update(List <SourceMapping> mapping, Dictionary <string, InitialValue> initialValue, Dictionary <string, TimeSpan> timeSpan)
{
DataTable rslt = new DataTable();
foreach (var current in mapping)
{
string key = current.SensorId;
InitialValue initialValue2 = initialValue[key];
double? x = initialValue2.X;
string num = x.HasValue ? "-" + x.GetValueOrDefault() : string.Empty;
double? y = initialValue2.Y;
string num2 = y.HasValue ? "-" + y.GetValueOrDefault() : string.Empty;
double? z = initialValue2.Z;
string num3 = z.HasValue ? "-" + z.GetValueOrDefault() : string.Empty;
string text = string.Empty;
if (timeSpan.ContainsKey(key) && timeSpan[key].DestinationUpdateTime.HasValue && timeSpan[key].SourceUpdateTime.HasValue)
{
text = string.Format(" where aDatetime > '{0}' ", timeSpan[key].DestinationUpdateTime);
}
string sql = string.Format(
"select '{0}','9',X{1},Y{2},Height{3},aDatetime from {4} {5}",
new object[] { key, num, num2, num3, current.SourceTable, text });
var table = SqlHelper.ExecuteDataSet(current.SourceSqlConn, CommandType.Text, sql, null).Tables[0];
rslt.Merge(table);
}
return(rslt);
}
/// <summary>
/// 获取源数据库初值
/// </summary>
/// <param name="sensors">传感器列表</param>
/// <param name="mapping">传感器id-对应表</param>
private Dictionary <string, InitialValue> GetSourceInitialValue(IList <string> sensors, List <SourceMapping> mapping)
{
Dictionary <string, InitialValue> dictionary = new Dictionary <string, InitialValue>();
foreach (var sensor in sensors)
{
var config = mapping.FirstOrDefault(m => m.SensorId == sensor);
if (config == null)
{
Console.WriteLine("警告:传感器{0}无数据源配置");
continue;
}
string sql =
string.Format(
"select '{0}',X,Y,Height from {1} where GPSIndex = (SELECT MIN(GPSIndex) FROM {1})",
sensor,
config.SourceTable);
try
{
var dt = SqlHelper.ExecuteDataSet(config.SourceSqlConn, CommandType.Text, sql, null).Tables[0];
foreach (DataRow row in dt.Rows)
{
InitialValue value = new InitialValue
{
X =
(row[1] == DBNull.Value)
? null
: (double?)Convert.ToDouble(row[1]),
Y =
(row[2] == DBNull.Value)
? null
: (double?)Convert.ToDouble(row[2]),
Z =
(row[3] == DBNull.Value)
? null
: (double?)Convert.ToDouble(row[3])
};
dictionary.Add(row[0].ToString(), value);
}
}
catch (Exception e)
{
Console.WriteLine("警告:\t传感器:{2}配置,数据源:{0},数据表:{1}无效", config.SourceSqlConn, config.SourceTable, config.SensorId);
}
}
return(dictionary);
}
/// <summary>
/// 获取数据库配置的初值
/// </summary>
/// <param name="sensors">传感器列表</param>
private Dictionary <string, InitialValue> GetInitialValueConfig(IList <string> sensors)
{
Dictionary <string, InitialValue> dictionary = new Dictionary <string, InitialValue>();
string arg = string.Join(",", sensors);
string cmdText = string.Format(@"select SENSOR_ID,INITIAL_X,INITIAL_Y,INITIAL_Z
from dbo.T_DIM_SENSOR_GPS
where SENSOR_ID in ({0})", arg);
var dt = SqlHelper.ExecuteDataSet(this.destinationDBConnStr, CommandType.Text, cmdText, null).Tables[0];
foreach (DataRow row in dt.Rows)
{
InitialValue value = new InitialValue
{
X = (row[1] == DBNull.Value) ? null : new double?(Convert.ToDouble(row[1])),
Y = (row[2] == DBNull.Value) ? null : new double?(Convert.ToDouble(row[2])),
Z = (row[3] == DBNull.Value) ? null : new double?(Convert.ToDouble(row[3]))
};
dictionary.Add(row[0].ToString(), value);
}
return(dictionary);
}
/// <summary>
/// 更新初值配置
/// </summary>
/// <param name="sensors">传感器列表</param>
/// <param name="initialConfig">配置的初值</param>
/// <param name="sourceInitialValue">源数据初值</param>
private Dictionary <string, InitialValue> UpdateInitialValueConfig(Dictionary <string, InitialValue> initialConfig, Dictionary <string, InitialValue> sourceInitialValue)
{
Dictionary <string, InitialValue> dictionary = new Dictionary <string, InitialValue>();
IList <string> sqlX = new List <string>();
IList <string> sqlY = new List <string>();
IList <string> sqlZ = new List <string>();
// 遍历配置的初值
foreach (var current in initialConfig)
{
// 是否配置了x方向初值
double?x = null;
if (!initialConfig[current.Key].X.HasValue)
{
// 源数据表是否有初值
if (sourceInitialValue.Keys.Contains(current.Key) && sourceInitialValue[current.Key].X.HasValue)
{
x = sourceInitialValue[current.Key].X;
sqlX.Add(string.Format("when {0} then {1}", current.Key, x));
}
}
else
{
x = initialConfig[current.Key].X;
}
double?y = null;
if (!initialConfig[current.Key].Y.HasValue)
{
if (sourceInitialValue.Keys.Contains(current.Key) && sourceInitialValue[current.Key].Y.HasValue)
{
y = sourceInitialValue[current.Key].Y;
sqlY.Add(string.Format("when {0} then {1}", current.Key, y));
}
}
else
{
y = initialConfig[current.Key].Y;
}
double?z = null;
if (!initialConfig[current.Key].Z.HasValue)
{
if (sourceInitialValue.Keys.Contains(current.Key) && sourceInitialValue[current.Key].Z.HasValue)
{
z = sourceInitialValue[current.Key].Z;
sqlZ.Add(string.Format("when {0} then {1}", current.Key, z));
}
}
else
{
z = initialConfig[current.Key].Z;
}
InitialValue value = new InitialValue
{
X = x,
Y = y,
Z = z
};
dictionary.Add(current.Key, value);
}
if (sqlX.Count == 0 && sqlY.Count == 0 && sqlZ.Count == 0)
{
return(dictionary);
}
string arg = (sqlX.Count == 0) ? string.Empty : string.Format("INITIAL_X = case SENSOR_ID {0} else INITIAL_X end,", string.Join(" ", sqlX));
string arg2 = (sqlY.Count == 0) ? string.Empty : string.Format("INITIAL_Y = case SENSOR_ID {0} else INITIAL_Y end,", string.Join(" ", sqlY));
string arg3 = (sqlZ.Count == 0) ? string.Empty : string.Format("INITIAL_Z = case SENSOR_ID {0} else INITIAL_Z end,", string.Join(" ", sqlZ));
string text = string.Format(@"update T_DIM_SENSOR_GPS
set {0} {1} {2}", arg, arg2, arg3);
text = text.Remove(text.LastIndexOf(','), 1);
SqlHelper.ExecteNonQuery(this.destinationDBConnStr, CommandType.Text, text, null);
return(dictionary);
}