public int SaveInstantTask(DACTask task)
{
Log.DebugFormat("Saving task result for {0}", task.DtuID);
string sql;
if (task.Saved)
{
sql = string.Format("update T_TASK_INSTANT set status={0}, finished='{1}' where ID={2}", (int)task.Status, String.Format("{0:yyyy-M-d HH:mm:ss}", task.Finished), task.ID);
}
else
{
sql = string.Format(@"insert into T_TASK_INSTANT
(DTU_ID,SENSORS,TASK_NAME,REQUESTER,REQUESTED,STATUS,MSG_ID) values
({0},'{1}','{2}','{3}','{4}',{5},'{6}')",
task.DtuID,
ValueHelper.ToStr(task.Sensors),
"Instant",
"admin",
String.Format("{0:yyyy-M-d HH:mm:ss}", task.Requested),
(int)task.Status,
task.TID
);
}
Console.WriteLine(sql);
int effectedRowsOrId = _helper.ExecuteSql(sql);
if (!task.Saved)
{
task.ID = effectedRowsOrId;
}
task.Saved = true;
return(effectedRowsOrId);
}
private void DoUpdateAsNecessary(IEnumerable <DataSourseTableInfo> tableInfos)
{
int updatedCnt = 0;
IList <TableDataProvider> pvs = new List <TableDataProvider>();
foreach (DataSourseTableInfo tableInfo in tableInfos)
{
sqlHelper = SqlHelperFactory.Create(tableInfo.DbType, tableInfo.ConnectionString);
if (ShouldUpdate(tableInfo.TableName))
{
string datatype = "nchar(20)";
string columnstr = " column";
if (tableInfo.DbType == FS.DbHelper.DbType.Access)
{
datatype = "text(20)";
}
if (tableInfo.DbType == FS.DbHelper.DbType.MSSQL)
{
columnstr = string.Empty;
}
Log.DebugFormat("Upgrading table {0}...", tableInfo.TableName);
sqlHelper.ExecuteSql(string.Format("alter table {0} add{1} lastSyncTime {2} null", tableInfo.TableName, columnstr, datatype));
}
pvs.Add(new TableDataProvider(tableInfo, sqlHelper));
}
this.DataProviders = pvs.ToArray();
Log.InfoFormat("Upgraded {0} tables...", updatedCnt);
}
/// <summary>
/// 结构物分数入库
/// </summary>
/// <param name="orgStcId"></param>
/// <param name="stcScore"></param>
/// <param name="time"></param>
public static void SaveStructureScore(int orgStcId, int stcScore, DateTime time)
{
string sql = string.Format(@"
INSERT INTO[T_FACT_STRUCTURE_SCORE](ORG_STRUC_ID,STRUCTURE_SCORE,EVALUATION_DATETIME)
VALUES({0},{1},'{2}')
", orgStcId, stcScore, time);
try
{
sqlHelper.ExecuteSql(sql);
}
catch (Exception e)
{
Log.Error("save stc score error", e);
}
}
public void TestThemeValueFormDb11()
{
var config = new ConfigInfo()
{
WindowSize = 40,
KThreshold = 0.2m,
DiscreteThreshold = 10,
ReCalcRValueThreshold = 30,
IsOpenWindow = true
};
var window = new ValidateWindow(config);
//查数据 ，循环处理
string cs = "server=192.168.1.250;database=DW_iSecureCloud_Empty;uid=sa;pwd=Fas123_;pooling=false";
ISqlHelper sqlHelper = SqlHelperFactory.Create(DbType.MSSQL, cs);
string sql = string.Format(@"
select top 2000 [DEEP_DISPLACEMENT_X_VALUE] as orginDatas
from [T_THEMES_DEFORMATION_DEEP_DISPLACEMENT] where SENSOR_ID =34 ");
sqlHelper.ExecuteSql(sql);
DataTable dt = sqlHelper.Query(sql).Tables[0];
foreach (DataRow item in dt.Rows)
{
var value = new AnalysisValue(Convert.ToDecimal(item[0]));
window.ProcessValue(value);
// Console.Write(value.ValidValue + "\n");
}
}
public void TestThemeValueFormDb2()
{
var config = new ConfigInfo()
{
WindowSize = 30,
KThreshold = 0.5m,
DiscreteThreshold = 15,
ReCalcRValueThreshold = 25,
IsOpenWindow = true
};
var window = new ValidateWindow(config);
//查数据 ，循环处理
string cs = "server=192.168.1.250;database=DW_iSecureCloud_Empty;uid=sa;pwd=Fas123_;pooling=false";
ISqlHelper sqlHelper = SqlHelperFactory.Create(DbType.MSSQL, cs);
string sql = string.Format(@"
select top 2000 * from (
select (case when [DEFLECTION_VALUE] > 20 or [DEFLECTION_VALUE] < 0 then NULL else [DEFLECTION_VALUE] end) as orginDatas
from [T_THEMES_DEFORMATION_BRIDGE_DEFLECTION] where SENSOR_ID =100) t
where t.orginDatas is not null");
sqlHelper.ExecuteSql(sql);
DataTable dt = sqlHelper.Query(sql).Tables[0];
//print value
foreach (DataRow item in dt.Rows)
{
var value = new AnalysisValue(Convert.ToDecimal(item[0]));
window.ProcessValue(value);
//Console.Write(value.ValidValue + "\n");
}
}
public void TestSettlement1568()
{
var config = new ConfigInfo()
{
WindowSize = 50,
KThreshold = 0.4m,
DiscreteThreshold = 10,
ReCalcRValueThreshold = 30,
IsOpenWindow = true
};
var window = new ValidateWindow(config);
string cs = "server=192.168.1.30;database=DW_iSecureCloud_Empty21;uid=sa;pwd=Windows2008;pooling=false";
ISqlHelper sqlHelper = SqlHelperFactory.Create(DbType.MSSQL, cs);
string sql =
string.Format(
@"Select top 1000 [SETTLEMENT_VALUE] as orginDatas from [T_THEMES_DEFORMATION_SETTLEMENT] where SENSOR_ID =1568 order by [ACQUISITION_DATETIME] ");
sqlHelper.ExecuteSql(sql);
DataTable dt = sqlHelper.Query(sql).Tables[0];
foreach (DataRow item in dt.Rows)
{
var value = new AnalysisValue(Convert.ToDecimal(item[0]));
window.ProcessValue(value);
//Console.Write(value.ValidValue + "\n");
}
}
private void DeleteData()
{
string sqlString = "server=192.168.1.128;database=DW_iSecureCloud_Empty2.2;uid=sa;pwd=861004;pooling=false";
ISqlHelper sqlHelper = SqlHelperFactory.Create(DbType.MSSQL, sqlString);
string sql = "delete from T_DIM_ABNORMALSENSOR_CONFIG where SensorId in (17,18,19)";
sqlHelper.ExecuteSql(sql);
}
private void DeleteData()
{
string cs = "server=192.168.1.250;database=DW_iSecureCloud_Empty;uid=sa;pwd=Fas123_;pooling=false";
ISqlHelper sqlHelper = SqlHelperFactory.Create(DbType.MSSQL, cs);
string sql = "delete from T_DATA_STABLE_FILTER_CONFIG where SensorId =990";
sqlHelper.ExecuteSql(sql);
}
public FsMessage GetDtuStatusMsg(DTUConnectionStatusChangedMsg msg)
{
try
{
string cs = ConfigurationManager.AppSettings["SecureCloud"];
ISqlHelper sqlHelper = SqlHelperFactory.Create(DbType.MSSQL, cs);
DataSet ds =
sqlHelper.Query(string.Format("(select ID from T_DIM_REMOTE_DTU where REMOTE_DTU_NUMBER ='{0}')",
msg.DTUID));
if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
this._log.InfoFormat("DTU {0} 不是一个有效的DTU", msg.DTUID);
}
else
{
string sqlstr =
string.Format(
"insert into T_DIM_DTU_STATUS (DtuId,Status,[Time]) values ({0},{1},'{2:yyyy-MM-dd HH:mm:ss.fff}')",
(string.Format("(select ID from T_DIM_REMOTE_DTU where REMOTE_DTU_NUMBER ='{0}')", msg.DTUID)),
msg.IsOnline ? 1 : 0, DateTime.Now);
sqlHelper.ExecuteSql(sqlstr);
}
}
catch (Exception ex)
{
this._log.ErrorFormat("Up dtu status error : {0}", ex.Message);
}
if (msg.IsOnline)
{
return(null);
}
var dtustateChangedMsg = new DTUStatusChangedMsg
{
Id = Guid.NewGuid(),
WarningTypeId = ((int)Errors.ERR_NOT_CONNECTED).ToString(),
DeviceTypeId = DTUDEVICETYPEID,
DTUID = msg.DTUID,
IsOnline = msg.IsOnline,
TimeStatusChanged = msg.TimeStatusChanged,
WarningContent = msg.IsOnline?"DTU上线":"DTU下线",
DateTime = DateTime.Now
};
var warningmsg = new FsMessage
{
Header = new FsMessageHeader
{
A = "PUT",
R = "/warning/dtu",
U = Guid.NewGuid(),
T = Guid.NewGuid(),
D = _warningAppName,
M = "Warning"
},
Body = JsonConvert.SerializeObject(dtustateChangedMsg)
};
return(warningmsg);
}
private void ChangeData()
{
string cs = "server=192.168.1.250;database=DW_iSecureCloud_Empty;uid=sa;pwd=Fas123_;pooling=false";
ISqlHelper sqlHelper = SqlHelperFactory.Create(DbType.MSSQL, cs);
string sql =
"update T_DATA_STABLE_FILTER_CONFIG set WindowSize='25', KT='0.05', DT='8', RT='20' where SensorId =990 and ItemId=1";
sqlHelper.ExecuteSql(sql);
}
private void InsertData(int sensorId, decimal?value1, decimal?value2, decimal?value3, decimal?value4)
{
string sqlString = "server=192.168.1.128;database=DW_iSecureCloud_Empty2.2;uid=sa;pwd=861004;pooling=false";
ISqlHelper sqlHelper = SqlHelperFactory.Create(DbType.MSSQL, sqlString);
//用代码向数据库插数据
var sql = string.Format(@"
insert into T_DIM_ABNORMALSENSOR_CONFIG
values({0},{1},{2},{3},{4},1,{5},{6})
", sensorId, value1 == null ? "null" : Convert.ToString(value1), value2 == null ? "null" : Convert.ToString(value2), value3 == null ? "null" : Convert.ToString(value3), value4 == null ? "null" : Convert.ToString(value4), "null", "null");
sqlHelper.ExecuteSql(sql);
}
public void TestSetUp()
{
this.DeleteData();
string cs = "server=192.168.1.250;database=DW_iSecureCloud_Empty;uid=sa;pwd=Fas123_;pooling=false";
ISqlHelper sqlHelper = SqlHelperFactory.Create(DbType.MSSQL, cs);
//用代码向数据库插数据
string sql = @"insert into T_DATA_STABLE_FILTER_CONFIG
Values
('990','1','true','50','0.1','10','25','true'),
('990','2','true','50','0.2','20','25','false'),
('990','3','false','25','0.3','5','25','false')";
sqlHelper.ExecuteSql(sql);
}
public int OnDataSynchronized(int[] ids)
{
int updated = 0;
if (ids.Length >= 0)
{
StringBuilder sb = new StringBuilder();
foreach (int i in ids)
{
sb.Append(",").Append(i);
}
string sql = string.Format("update {0} set lastSyncTime='{2}' where id in ('' {1})", Meta.TableName, sb.ToString(), System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
updated = _sqlHelper.ExecuteSql(sql);
this.Remainder -= updated;
}
return(updated);
}
public static void GetGuangfuTest(Item item)
{
ISqlHelper sqlHelper = SqlHelperFactory.Create(DbType.MSSQL, item.ConnectionString);
string sql =
string.Format(
@"Select [{0}] as orginDatas from [{1}] where SensorId ={2} and [CollectTime]>'2015-01-13 00:13:08.000' order by [CollectTime] ",
item.FiledName, item.TableName, item.SensorId);
sqlHelper.ExecuteSql(sql);
var dt = sqlHelper.Query(sql).Tables[0];
var workbook = new Workbook();
var worksheet = workbook.Worksheets[0];
worksheet.Name = item.ToString();
var cells = worksheet.Cells;
var j = 1; //column
var z = 0;
cells[0, 0].PutValue("基点2");
const decimal firstValue = 5.4055m;
foreach (DataRow row in dt.Rows)
{
z++;
var value = new AnalysisValue((Convert.ToDecimal(row[0]) - firstValue) * 100);
cells[z, 0].PutValue(value.RawValue);
}
foreach (var config in item.Configs)
{
var k = 1; //row
var window = new ValidateWindow(config);
foreach (DataRow row in dt.Rows)
{
var value = new AnalysisValue((Convert.ToDecimal(row[0]) - firstValue) * 100);
window.ProcessValue(value);
cells[k, j].PutValue(value.ValidValue);
k++;
}
cells[0, j].PutValue(config.ToString());
j++;
}
workbook.Save(item.ToString() + ".xls");
}
public static void ProcessItem(Item item)
{
ISqlHelper sqlHelper = SqlHelperFactory.Create(DbType.MSSQL, item.ConnectionString);
string sql =
string.Format(
@"Select [{0}] as orginDatas from [{1}] where SENSOR_ID ={2} and [ACQUISITION_DATETIME]>'2014-12-24 22:36:00.000' order by [ACQUISITION_DATETIME] ",
item.FiledName, item.TableName, item.SensorId);
sqlHelper.ExecuteSql(sql);
var dt = sqlHelper.Query(sql).Tables[0];
var workbook = new Workbook();
var worksheet = workbook.Worksheets[0];
worksheet.Name = item.ToString();
var cells = worksheet.Cells;
var j = 1; //column
var z = 0;
cells[0, 0].PutValue("orgin");
foreach (DataRow row in dt.Rows)
{
z++;
var value = new AnalysisValue(Convert.ToDecimal(row[0]));
cells[z, 0].PutValue(value.RawValue);
}
foreach (var config in item.Configs)
{
var k = 1; //row
var window = new ValidateWindow(config);
foreach (DataRow row in dt.Rows)
{
var value = new AnalysisValue(Convert.ToDecimal(row[0]));
window.ProcessValue(value);
cells[k, j].PutValue(value.ValidValue);
k++;
}
cells[0, j].PutValue(config.ToString());
j++;
}
workbook.Save(item.ToString() + ".xls");
}
private void DoUpdateAsNecessary(IEnumerable <DataSourseTableInfo> tableInfos)
{
//DataMetaInfo[] dms = DbAccessorHelper.DbAccessor.GetDataMetas();
//Log.DebugFormat("{0} tables found.", dms.Length);
//if (dms.Length <= 0)
// return;
int updatedCnt = 0;
IList <TableDataProvider> pvs = new List <TableDataProvider>();
foreach (DataSourseTableInfo dmi in tableInfos)
{
if (ShouldUpdate(dmi.TableName))
{
updatedCnt++;
Log.DebugFormat("Upgrading table {0}...", dmi.TableName);
sqlHelper.ExecuteSql( // 2014-08-08 08:08:08, 19字符长度.
string.Format("alter table {0} add column lastSyncTime nchar(20) null", dmi.TableName));
}
pvs.Add(new TableDataProvider(dmi, sqlHelper));
}
this._dataProviders = pvs.ToArray();
Log.InfoFormat("Upgraded {0} tables...", updatedCnt);
}
public static int ExecuteSql(string strSql)
{
return(IHelper.ExecuteSql(strSql));
}
public void ClearAllRationalRange()
{
string sql = "truncate table T_DATA_RATIONAL_FILTER_CONFIG";
sqlHelper.ExecuteSql(sql);
}