public DataSet Select(DataBaseName database)
{
string connstr = Connectionstring.GetConnectionString(database);
var sqlstr = new StringBuilder();
sqlstr.Append("SELECT @ProjectCode as ProjectCode, @databaseId as DataBaseNameID, SENSOR_SET_ID, ModuleNo, ChannelID, ACQUISITION_DATETIME, 14 as SAFETY_FACTOR_TYPE_ID, AngleOriginalX, AngleOriginalY, AngleOffsetX, AngleOffsetY FROM D_OriginalInclinationData where ACQUISITION_DATETIME >@ACQUISITION_DATETIME1 order by ID ASC")
.Append(";")
.Append("SELECT @ProjectCode as ProjectCode, @databaseId as DataBaseNameID, SENSOR_SET_ID, ModuleNo, ChannelID, ACQUISITION_DATETIME, 12 as SAFETY_FACTOR_TYPE_ID, OriginalDisplayment, OffsetDisplayment FROM D_OriginalLVDTData where ACQUISITION_DATETIME >@ACQUISITION_DATETIME2 order by ID ASC")
.Append(";")
.Append("SELECT @ProjectCode as ProjectCode, @databaseId as DataBaseNameID, SENSOR_Set_ID, ModuleNo, ChannelID, ACQUISITION_DATETIME, 6 as SAFETY_FACTOR_TYPE_ID, OrgVoltage, HUMILITY_VALUE, Mechan_Value FROM D_OriginalMagneticFluxData where ACQUISITION_DATETIME >@ACQUISITION_DATETIME3 order by ID ASC")
.Append(";")
.Append("SELECT @ProjectCode as ProjectCode, @databaseId as DataBaseNameID, SENSOR_Set_ID, ModuleNo, ChannelID, ACQUISITION_DATETIME, 11 as SAFETY_FACTOR_TYPE_ID, ColPressureValue, CulcPressureValue FROM D_OriginalPressureData where ACQUISITION_DATETIME >@ACQUISITION_DATETIME4 order by ID ASC")
.Append(";")
.Append("SELECT @ProjectCode as ProjectCode, @databaseId as DataBaseNameID, SENSOR_Set_ID, ModuleNo, ChannelID, ACQUISITION_DATETIME, 3 as SAFETY_FACTOR_TYPE_ID, RainFall FROM D_OriginalRainFallData where ACQUISITION_DATETIME >@ACQUISITION_DATETIME5 order by ID ASC")
.Append(";")
.Append("SELECT @ProjectCode as ProjectCode, @databaseId as DataBaseNameID, SENSOR_Set_ID, ModuleNo, ChannelID, ACQUISITION_DATETIME, 9 as SAFETY_FACTOR_TYPE_ID, TEMPERATURE_VALUE, HUMILITY_VALUE FROM D_OriginalTempHumiData where ACQUISITION_DATETIME >@ACQUISITION_DATETIME6 order by ID ASC")
.Append(";")
.Append("SELECT @ProjectCode as ProjectCode, @databaseId as DataBaseNameID, SENSOR_SET_ID, ModuleNo, ChannelID, ACQUISITION_DATETIME, 2 as SAFETY_FACTOR_TYPE_ID, Frequency_VALUE, TEMPERATURE_VALUE, PhysicalValue FROM D_OriginalVibratingWireData where ACQUISITION_DATETIME >@ACQUISITION_DATETIME7 order by ID ASC")
.Append(";")
.Append("SELECT @ProjectCode as ProjectCode, @databaseId as DataBaseNameID, SENSOR_Set_ID, ModuleNo, ChannelID, ACQUISITION_DATETIME, 1 as SAFETY_FACTOR_TYPE_ID, OrgVoltage, displayment FROM D_OriginalVoltageData where ACQUISITION_DATETIME >@ACQUISITION_DATETIME8 order by ID ASC")
.Append(";")
.Append("SELECT @ProjectCode as ProjectCode, @databaseId as DataBaseNameID, SENSOR_Set_ID, ModuleNo, ChannelID, ACQUISITION_DATETIME, 5 as SAFETY_FACTOR_TYPE_ID, WIND_SPEED_VALUE, WIND_DIRECTION_VALUE, WIND_ELEVATION_VALUE, TEMPERATURE_VALUE FROM D_OriginalWindData where ACQUISITION_DATETIME >@ACQUISITION_DATETIME9 order by ID ASC");
var para = new[]
{
new SQLiteParameter("@ProjectCode", ProjectInfoDic.GetInstance().GetProjectInfo().ProjectCode),
new SQLiteParameter("@databaseId", database.ID),
new SQLiteParameter("@ACQUISITION_DATETIME1", ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID, "D_OriginalInclinationData").Acqtime),
new SQLiteParameter("@ACQUISITION_DATETIME2", ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID, "D_OriginalLVDTData").Acqtime),
new SQLiteParameter("@ACQUISITION_DATETIME3", ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID, "D_OriginalMagneticFluxData").Acqtime),
new SQLiteParameter("@ACQUISITION_DATETIME4", ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID, "D_OriginalPressureData").Acqtime),
new SQLiteParameter("@ACQUISITION_DATETIME5", ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID, "D_OriginalRainFallData").Acqtime),
new SQLiteParameter("@ACQUISITION_DATETIME6", ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID, "D_OriginalTempHumiData").Acqtime),
new SQLiteParameter("@ACQUISITION_DATETIME7", ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID, "D_OriginalVibratingWireData").Acqtime),
new SQLiteParameter("@ACQUISITION_DATETIME8", ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID, "D_OriginalVoltageData").Acqtime),
new SQLiteParameter("@ACQUISITION_DATETIME9", ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID, "D_OriginalWindData").Acqtime)
};
var dbhelper = new DbHelperSqLiteP(connstr);
return(dbhelper.Query(sqlstr.ToString(), para));
}
public IList <Data> TextOrBinarySelect(DataBaseName path)
{
IList <Data> list = new List <Data>();
if (path.DataBaseType == (int)DataBaseType.Fiber)
{
DateTime readtime = new DateTime();
string[] timeformats =
{
"yyyy/MM/dd HH:mm:ss", "yyyy/MM/dd HH:mm:ss.fff", "yyyy-MM-dd HH:mm:ss",
"yyyy-MM-dd HH:mm:ss.fff", "yyyyMMddHHmmss", "yyyyMMddHHmmss.fff",
"yyyy-MM-dd h:mm:ss"
};
var dir = new DirectoryInfo(path.Location);
DirectoryInfo[] childDirectories;
try
{
childDirectories = dir.GetDirectories("FBG_*");
}
catch (Exception ex)
{
return(list);
}
var fbgList = new List <string>();
if (childDirectories != null && childDirectories.Length > 0)
{
foreach (DirectoryInfo childDirectory in childDirectories)
{
fbgList.Add(childDirectory.FullName);
}
}
if (fbgList != null && fbgList.Count > 0)
{
foreach (string fbg in fbgList) //遍历所有光栅光纤文件夹
{
bool isSuccess = false;
try
{
if (ExtractionConfigDic.GetExtractionConfigDic()
.GetExtractionConfig((int)path.ID).Count > 0)
{
isSuccess =
DateTime.TryParseExact(
ExtractionConfigDic.GetExtractionConfigDic()
.GetExtractionConfig((int)path.ID)[0]
.Acqtime,
timeformats,
CultureInfo.CurrentCulture,
DateTimeStyles.None,
out readtime);
}
}
catch (Exception e)
{
throw e;
}
if (!isSuccess)
{
readtime = new DateTime(2013, 1, 1);
}
TimeSpan ts = DateTime.Now.Date.Subtract(readtime.Date);
var strdays = new string[ts.Days + 1];
for (int i = 0; i < strdays.Length; i++)
{
strdays[i] = readtime.AddDays(i).ToString("yyyyMMdd");
}
string moudleId = Path.GetFileNameWithoutExtension(fbg).Remove(0, 4);
foreach (string strday in strdays) // 遍历一个模块下某一天的所有通道的数据
{
string filter = "CH_*_" + strday + "_*.txt";
string[] files = Directory.GetFiles(fbg, filter, SearchOption.TopDirectoryOnly);
foreach (string file in files)// 遍历某通道的所有数据
{
string fileNameWithoutExtension = Path.GetFileNameWithoutExtension(file);
int channel =
int.Parse(
fileNameWithoutExtension.Split(
new[] { '_' },
StringSplitOptions.RemoveEmptyEntries)[1].Trim());
DateTime lasTime;
isSuccess =
DateTime.TryParseExact(
ExtractionConfigDic.GetExtractionConfigDic()
.GetExtractionConfig((int)path.ID, channel.ToString())
.Acqtime,
timeformats,
CultureInfo.CurrentCulture,
DateTimeStyles.None,
out lasTime);
try
{
using (var tfc = new ZetaTemporaryFileCloner(file))
{
using (var sr = new StreamReader(tfc.FilePath))
{
string linestr = string.Empty;
DateTime time = DateTime.Now;
while ((linestr = sr.ReadLine()) != null)
{
string[] pars = linestr.Split(
new[] { '\t' },
StringSplitOptions.RemoveEmptyEntries);
string[] tempTime1 = pars[0].Remove(0, 1).Split(new char[] { '-', ' ', '.', ':' });
string tempTime = null;
for (int i = 0; i < tempTime1.Count() - 1; i++)
{
tempTime += tempTime1[i];
}
if (!DateTime.TryParseExact(tempTime, "yyyyMMddHHmmss",
CultureInfo.CurrentCulture, DateTimeStyles.None, out time))
{
//MessageBox.Show("时间转换失败");
}
if (time > lasTime)
{
Data data = new Data();
data.MoudleNo = moudleId;
data.ChannelId = channel;
data.CollectTime = time;
data.DataBaseId = (int)path.ID;
data.SafeTypeId = 11;
data.DataSet = new List <double>();
try
{
for (int i = 1; i < pars.Length; i++)
{
data.DataSet.Add(double.Parse(pars[i].Trim()));
}
list.Add(data);
}
catch
{
continue;
}
}
ExtractionConfigDic.GetExtractionConfigDic()
.UpdateExtractionConfig(
new ExtractionConfig
{
DataBaseId = (int)path.ID,
TableName = channel.ToString(),
Acqtime =
time.ToString("yyyyMMddHHmmss.fff")
});
}
}
}
}
catch (Exception e)
{
throw e;
}
}
}
Console.WriteLine("AAA");
}
ExtractionConfigDic.GetExtractionConfigDic()
.UpdateExtractionConfig(
new ExtractionConfig
{
DataBaseId = (int)path.ID,
TableName = string.Empty,
Acqtime =
DateTime.Now.ToString("yyyyMMddHHmmss.fff")
});
}
}
return(list);
}
/// <summary>
/// 提取其他数据库
/// </summary>
/// <param name="dbName"></param>
private void ExtractOtherOneDb(DataBaseName dbName)
{
var bll = new SelectOtherTablesBll();
DataSet ds = null;
log.Debug("提取其他数据库");
try
{
ds = bll.Select(dbName);
}
catch (Exception ex)
{
log.Error(ex.Message);
}
if (ds != null)
{
log.Debug("提取成功");
foreach (DataTable dt in ds.Tables)
{
try
{
List <Data> list = new List <Data>();
if (this.messagesShowEventHandler != null)
{
var msg = new StringBuilder();
msg.Append("提取").Append(dt.TableName).Append(dt.Rows.Count).Append("条数据");
log.Info(msg.ToString());
this.messagesShowEventHandler(
this,
new MessagesShowEventArgs {
MessageType = MsgType.Info, MessagesShow = msg.ToString()
});
}
if (dt != null && dt.Rows.Count > 0)
{
int index = 0;
bool flag = false;
bool ultraSetFlag = false;//@Modify20150107by yww
int flagCount = 0;
ConcurrentDictionary <int, ConfigTableInfo> dic = new ConcurrentDictionary <int, ConfigTableInfo>();
ConfigTableInfo config = new ConfigTableInfo();
if (
!string.IsNullOrEmpty(
TableFieldInfoDic.GetTableFieldInfoDic()
.GeTableFieldInfo((int)dbName.ID, dt.TableName)
.OtherFlag))
{
index = 1;
}
if (ConfigTableDic.GetConfigTableDic().SelectList().Count > 0)
{
ConfigTable configtable = ConfigTableDic.GetConfigTableDic().SelecConfigTable((int)dbName.ID);
//ConfigTable configtable = ConfigTableDic.GetConfigTableDic().SelecConfigTable((int)dbName.ID, dt.TableName);
if (configtable != null)
{
flag = true;
flagCount = 1;
}
// 特殊处理(在有Sensor配置的时候直接拿SensorID做为模块号)
if (dt.TableName == "MainStreeData")
{
ultraSetFlag = true;
flag = false;
flagCount = 1;
}
}
else
{
flag = false;
flagCount = 2;
}
foreach (DataRow row in dt.Rows)
{
try
{
var data = new Data
{
ProjectCode = Convert.ToInt16(row[0]),
DataBaseId = Convert.ToInt32(row[1]),
SafeTypeId = Convert.ToInt32(row[2]),
ChannelId = 1
};
data.DataSet = new List <double>();
if (flag)
{
int sensorid = 0;
bool identifyBySerialNo = false;
string serialNo = "";
if (row[4] != DBNull.Value)
{
if (int.TryParse(row[4].ToString(), out sensorid))
{
data.SensorId = sensorid;
identifyBySerialNo = false;
}
else
{
serialNo = row[4].ToString();
identifyBySerialNo = true;
}
//data.SensorId = Convert.ToInt32(row[4]);
if (ConfigInfoTable.ConfigtableInfoDictionary.ContainsKey(data.DataBaseId))
{
dic = ConfigInfoTable.ConfigtableInfoDictionary[data.DataBaseId];
}
else
{
log.Error("字典中数据库ID关键字不存在");
continue;
}
if (!identifyBySerialNo)
{
if (dic.ContainsKey(data.SensorId))
{
config = dic[data.SensorId];
}
else
{
log.Error("字典中传感器ID关键字不存在");
continue;
}
}
else
{
config = (from s in dic.Values
where StringComparer.OrdinalIgnoreCase.Compare(s.Otherflag, serialNo) == 0
select s).FirstOrDefault();
if (config == null)
{
log.Error("字典中传感器唯一标示符不存在");
continue;
}
}
data.ChannelId = config.ChannelId;
data.MoudleNo = config.MoudleNo;
}
else // 没有传感器ID
{
data.MoudleNo = "1";
}
}
else
{
data.ChannelId = 1;
if (!ultraSetFlag)
{
if (row[5] != DBNull.Value)
{
data.ChannelId = Convert.ToInt32(row[5]);
}
}
if (row[4] != DBNull.Value)
{
data.MoudleNo = row[4].ToString();
}
}
if (index == 1)
{
data.OFlag = Convert.ToInt32(row[6]);
}
// 采集时间转换问题，提供几种常见格式的时间转换
DateTime acqtime = Convert.ToDateTime(row[3].ToString().Trim());
//string timestr = ;
//string[] timeformats =
// {
// "yyyy/MM/dd HH:mm:ss", "yyyy/MM/dd HH:mm:ss.fff",
// "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm:ss.fff", "yyyyMMddHHmmss",
// "yyyyMMddHHmmss.fff","yyyy-MM-dd h:mm:ss","yyyy-M-d h:mm:ss"
// };
//timestr
//bool isSuccess = DateTime.TryParseExact(
// timestr,
// timeformats,
// CultureInfo.CurrentCulture,
// DateTimeStyles.None,
// out acqtime); //AssumeLocal
//if (!isSuccess)
//{
// try
// {
// acqtime = Convert.ToDateTime(timestr);
// }
// catch
// {
// log.Error("时间格式转换失败；" + timestr);
// }
//}
data.CollectTime = acqtime;
for (int i = 4 + flagCount + index; i < row.ItemArray.Length; i++)
{
double value;
double.TryParse(row[i].ToString().Trim(), out value);
data.DataSet.Add(value);
}
list.Add(data);
}
catch (Exception ex)
{
log.Error(ex.Message);
}
}
string strtime = dt.Compute("Max(ACQUISITION_DATETIME)", Boolean.TrueString).ToString();
ExtractionConfigDic.GetExtractionConfigDic()
.UpdateExtractionConfig(
new ExtractionConfig
{
DataBaseId = (int)dbName.ID,
TableName = dt.TableName,
Acqtime = strtime
});
}
//添加到队列
foreach (Data data in list)
{
datasTobesent.Enqueue(data);
}
}
catch (Exception ex)
{
log.Error(ex.Message);
}
}
}
else
{
log.Debug("DataSet==null");
}
}
/// <summary>
/// 提取统一采集软件数据
/// </summary>
private void ExtractFSDB()
{
try
{
if (!string.IsNullOrEmpty(DataBaseNameDic.GetDataBaseNameDic().GetFSUSBaseName().DataBaseCode))
{
var bll = new SelectFSUSDBTablesBll();
DataBaseName dataBase = DataBaseNameDic.GetDataBaseNameDic().GetFSUSBaseName();
DataSet ds = bll.Select(dataBase);
foreach (DataTable dt in ds.Tables)
{
try
{
if (this.messagesShowEventHandler != null)
{
var msg = new StringBuilder();
msg.Append("提取").Append(dt.TableName).Append(dt.Rows.Count).Append("条数据");
log.Info(msg.ToString());
this.messagesShowEventHandler(
this,
new MessagesShowEventArgs
{
MessageType = MsgType.Info,
MessagesShow = msg.ToString()
});
}
List <Data> list = new List <Data>();
if (dt != null && dt.Rows.Count > 0)
{
log.Info("进入提取数据表");
foreach (DataRow row in dt.Rows)
{
DateTime collectTime;
ValueHelper.String2Time(row[5].ToString().Trim(), out collectTime);
Data data = new Data
{
ProjectCode = Convert.ToInt16(row[0].ToString().Trim()),
DataBaseId = Convert.ToInt32(row[1].ToString().Trim()),
SensorId = Convert.ToInt32(row[2].ToString().Trim()),
MoudleNo = row[3].ToString().Trim(),
ChannelId = Convert.ToInt32(row[4].ToString().Trim()),
CollectTime = collectTime,
SafeTypeId = Convert.ToInt32(row[6].ToString().Trim())
};
data.DataSet = new List <double>();
for (int i = 7; i < row.ItemArray.Length; i++)
{
if (row[i] != DBNull.Value && !string.IsNullOrEmpty(row[i].ToString().Trim()))
{
double value;
double.TryParse(row[i].ToString().Trim(), out value);
data.DataSet.Add(value);
}
else
{
data.DataSet.Add(0);
}
}
list.Add(data);
}
string timestr = dt.Compute("Max(ACQUISITION_DATETIME)", Boolean.TrueString).ToString();
ExtractionConfigDic.GetExtractionConfigDic()
.UpdateExtractionConfig(
new ExtractionConfig
{
DataBaseId = (int)dataBase.ID,
TableName = dt.TableName,
Acqtime = timestr
});
log.Info("提取数据表结束");
}
//添加到队列
foreach (Data data in list)
{
datasTobesent.Enqueue(data);
}
}
catch (Exception ex)
{
log.Error(ex.Message);
}
}
}
}
catch (Exception ex)
{
log.Error(ex.Message);
}
}
private DataSet SelectAll(DataBaseName database)
{
string connstr = Connectionstring.GetConnectionString(database);
var sqlstr = new StringBuilder();
List <TableFieldInfo> tables =
TableFieldInfoDic.GetTableFieldInfoDic().GetSameDataBaseTableFieldInfos((int)database.ID);
for (int i = 0; i < tables.Count; i++)
{
// Select CONVERT(varchar(100), GETDATE(), 25)
string str = string.Format(
"select {0} as ProjectCode,{1} as DataBaseNameID,{2} as sensorType,{3} as ACQUISITION_DATETIME,",
ProjectInfoDic.GetInstance().GetProjectInfo().ProjectCode,
database.ID,
tables[i].SensorType,
tables[i].AcqTime);
sqlstr.Append(str);
if (ConfigInfoTable.ConfigtableInfoDictionary.ContainsKey((int)database.ID))
{
sqlstr.Append(tables[i].SensorID).Append(",");
}
else
{
if (!string.IsNullOrEmpty(tables[i].ModuleNo))
{
sqlstr.Append(tables[i].ModuleNo).Append(",");
}
if (!string.IsNullOrEmpty(tables[i].ChannelId))
{
sqlstr.Append(tables[i].ChannelId).Append(",");
}
else
{
sqlstr.Append(string.Format("{0} as channelId", 1)).Append(",");
}
if (!string.IsNullOrEmpty(tables[i].OtherFlag))
{
sqlstr.Append(tables[i].OtherFlag).Append(",");
}
}
Type t = tables[i].GetType();
for (int j = 1; j <= tables[i].ValueNameCount; j++)
{
System.Reflection.PropertyInfo propertyInfo = t.GetProperty(FieldNamestr + j);
if (!string.IsNullOrEmpty(propertyInfo.GetValue(tables[i], null).ToString()))
{
sqlstr.Append(propertyInfo.GetValue(tables[i], null)).Append(",");
}
}
sqlstr.Replace(',', ' ', sqlstr.Length - 1, 1);
sqlstr.Append("from ")
.Append(tables[i].TableName)
.Append(" where ")
.Append(tables[i].AcqTime)
.Append(" >@")
.Append(acqtime)
.Append(i);
if (i != tables.Count - 1)
{
sqlstr.Append(";");
}
}
List <ExtractionConfig> list = ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID);
switch (database.DataBaseType)
{
case (int)DataBaseType.SQLite:
var sqlitepara =
new SQLiteParameter[tables.Count];
if (sqlitepara.Length > 0)
{
for (int i = 0; i < sqlitepara.Length; i++)
{
var str = new StringBuilder();
str.Append("@").Append(acqtime).Append(i);
sqlitepara[i] = new SQLiteParameter(str.ToString(),
this.GetLastTime(list, tables[i].TableName));
}
var sqlitehelper = new DbHelperSqLiteP(connstr);
try
{
return(sqlitehelper.Query(sqlstr.ToString(), sqlitepara));
}
catch (Exception ex)
{
throw ex;
}
}
break;
case (int)DataBaseType.SQLServer:
var sqlpara =
new SqlParameter[tables.Count];
if (sqlpara.Length > 0)
{
for (int i = 0; i < sqlpara.Length; i++)
{
var str = new StringBuilder();
str.Append("@").Append(acqtime).Append(i);
string time = this.GetLastTime(list, tables[i].TableName);
//string timestr = null;
//string[] sliptime = time.Split(new char[]{'/',' ',':'});
//if (sliptime.Count() > 1)
//{
// for (int n = 0; n < sliptime.Count(); n++)
// {
// if (sliptime[n].Length < 8 && sliptime[n]!="")
// {
// if (Convert.ToInt32(sliptime[n]) < 10 && sliptime[n].Length == 1)
// {
// sliptime[n] = "0" + sliptime[n];
// }
// }
// }
//}
//for (int m = 0; m < sliptime.Count(); m++)
//{
// timestr += sliptime[m].Trim();
//}
//string[] timeformats =
// {
// "yyyy/MM/dd HH:mm:ss", "yyyy/MM/dd HH:mm:ss.fff",
// "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm:ss.fff", "yyyyMMddHHmmss",
// "yyyyMMddHHmmss.fff", "yyyy-MM-dd h:mm:ss"
// };
DateTime timeTemp = Convert.ToDateTime(time).AddMilliseconds(999);
//bool isSuccess = DateTime.TryParseExact(
//timestr,
//timeformats,
//CultureInfo.CurrentCulture,
//DateTimeStyles.None,
//out timeTemp); //AssumeLocal
//if (!isSuccess)
//{
// timeTemp = Convert.ToDateTime(timestr);
//}
sqlpara[i] = new SqlParameter(str.ToString(), timeTemp.ToString("yyyy-MM-dd HH:mm:ss.fff"));
}
var sqlhelper = new DbHelperSQLP(connstr);
try
{
return(sqlhelper.Query(sqlstr.ToString(), 300, sqlpara));
}
catch (Exception ex)
{
throw ex;
}
}
break;
case (int)DataBaseType.ACCESSOld:
case (int)DataBaseType.ACCESSNew:
var olepara =
new OleDbParameter[tables.Count];
if (olepara.Length > 0)
{
for (int i = 0; i < olepara.Length; i++)
{
var str = new StringBuilder();
str.Append("@").Append(acqtime).Append(i);
try
{
string timestr = this.GetLastTime(list, tables[i].TableName);
DateTime timeTemp = Convert.ToDateTime(timestr);
// string[] timeformats =
// {
// "yyyy/MM/dd HH:mm:ss", "yyyy/MM/dd HH:mm:ss.fff",
// "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm:ss.fff",
// "yyyyMMddHHmmss", "yyyyMMddHHmmss.fff", "yyyy-MM-dd h:mm:ss"
// };
//bool isSuccess = DateTime.TryParseExact(
//timestr,
//timeformats,
//CultureInfo.CurrentCulture,
//DateTimeStyles.None,
//out timeTemp); //AssumeLocal
// if (!isSuccess)
// {
// timeTemp = Convert.ToDateTime(timestr);
// }
olepara[i] = new OleDbParameter(str.ToString(),
timeTemp);
}
catch (Exception ex)
{
throw ex;
}
}
var oledbhelper = new DbHelperOleDbP(connstr);
string[] sqlstrings = sqlstr.ToString().Split(';');
DataSet[] ds = new DataSet[olepara.Length];
for (int i = 0; i < olepara.Length; i++)
{
ds[i] = new DataSet();
}
for (int i = 0; i < olepara.Length; i++)
{
ds[i] = oledbhelper.Query(sqlstrings[i], olepara[i]);
}
DataSet retSet = new DataSet();
for (int i = 0; i < olepara.Length; i++)
{
retSet.Merge(ds[i]);
}
return(retSet);
//return oledbhelper.Query(sqlstr.ToString(), olepara);
}
break;
default:
return(new DataSet());
}
return(new DataSet());
}
/// <summary>
/// 当待提取数据超过1天(提取历史数据)时 每次提取一天的数据
/// </summary>
private DataSet SelectByDay(DataBaseName database)
{
var connstr = Connectionstring.GetConnectionString(database);
var sqlstr = new StringBuilder();
var tables = TableFieldInfoDic.GetTableFieldInfoDic().GetSameDataBaseTableFieldInfos((int)database.ID);
for (var i = 0; i < tables.Count; i++)
{
string str = string.Format(
"select {0} as ProjectCode,{1} as DataBaseNameID,{2} as sensorType,{3} as ACQUISITION_DATETIME,",
ProjectInfoDic.GetInstance().GetProjectInfo().ProjectCode,
database.ID,
tables[i].SensorType,
tables[i].AcqTime);
sqlstr.Append(str);
if (ConfigInfoTable.ConfigtableInfoDictionary.ContainsKey((int)database.ID))
{
sqlstr.Append(tables[i].SensorID).Append(",");
}
else
{
if (!string.IsNullOrEmpty(tables[i].ModuleNo))
{
sqlstr.Append(tables[i].ModuleNo).Append(",");
}
if (!string.IsNullOrEmpty(tables[i].ChannelId))
{
sqlstr.Append(tables[i].ChannelId).Append(",");
}
else
{
sqlstr.Append(string.Format("{0} as channelId", 1)).Append(",");
}
if (!string.IsNullOrEmpty(tables[i].OtherFlag))
{
sqlstr.Append(tables[i].OtherFlag).Append(",");
}
}
Type t = tables[i].GetType();
for (int j = 1; j <= tables[i].ValueNameCount; j++)
{
System.Reflection.PropertyInfo propertyInfo = t.GetProperty(FieldNamestr + j);
if (!string.IsNullOrEmpty(propertyInfo.GetValue(tables[i], null).ToString()))
{
sqlstr.Append(propertyInfo.GetValue(tables[i], null)).Append(",");
}
}
sqlstr.Replace(',', ' ', sqlstr.Length - 1, 1);
sqlstr.Append("from ")
.Append(tables[i].TableName)
.Append(" where ")
.Append(tables[i].AcqTime)
.Append(" >@")
.Append(acqtime)
.Append(i)
.Append(" and ")
.Append(tables[i].AcqTime)
.Append(" <=@")
.Append(acqendtime)
.Append(i);
if (i != tables.Count - 1)
{
sqlstr.Append(";");
}
}
List <ExtractionConfig> list = ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)database.ID);
switch (database.DataBaseType)
{
case (int)DataBaseType.SQLite:
var sqlitepara =
new SQLiteParameter[tables.Count * 2];
if (sqlitepara.Length > 0)
{
var endtime1 = new DateTime[tables.Count];
for (int i = 0; i < tables.Count; i++)
{
string time = this.GetLastTimeOrDefaultMin(list, tables[i], database);
DateTime timeTemp = Convert.ToDateTime(time).AddSeconds(1);
endtime1[i] = GetDayStepEndTime(timeTemp);
sqlitepara[i * 2] = new SQLiteParameter(new StringBuilder().Append("@").Append(acqtime).Append(i).ToString(),
timeTemp.ToString("yyyy-MM-dd HH:mm:ss"));
sqlitepara[i * 2 + 1] = new SQLiteParameter(new StringBuilder().Append("@").Append(acqendtime).Append(i).ToString(),
endtime1[i].ToString("yyyy-MM-dd HH:mm:ss"));
}
var sqlitehelper = new DbHelperSqLiteP(connstr);
try
{
var s = sqlitehelper.Query(sqlstr.ToString(), sqlitepara);
for (var i = 0; i < tables.Count; i++)
{
ExtractionConfigDic.GetExtractionConfigDic()
.UpdateExtractionConfig(
new ExtractionConfig
{
DataBaseId = (int)database.ID,
TableName = tables[i].TableName,
Acqtime = endtime1[i].ToString("yyyy-MM-dd HH:mm:ss")
});
}
return(s);
}
catch (Exception ex)
{
throw ex;
}
}
break;
case (int)DataBaseType.SQLServer:
var sqlpara =
new SqlParameter[tables.Count * 2];
if (sqlpara.Length > 0)
{
var endtime2 = new DateTime[tables.Count];
for (var i = 0; i < tables.Count; i++)
{
string time = this.GetLastTimeOrDefaultMin(list, tables[i], database);
DateTime timeTemp = Convert.ToDateTime(time).AddSeconds(1); //这里加1秒是防止重复提取(因为时间记录中不包含毫秒数)
endtime2[i] = GetDayStepEndTime(timeTemp);
sqlpara[i * 2] = new SqlParameter(new StringBuilder().Append("@").Append(acqtime).Append(i).ToString(),
timeTemp.ToString("yyyy-MM-dd HH:mm:ss"));
sqlpara[i * 2 + 1] = new SqlParameter(new StringBuilder().Append("@").Append(acqendtime).Append(i).ToString(),
endtime2[i].ToString("yyyy-MM-dd HH:mm:ss"));
}
var sqlhelper = new DbHelperSQLP(connstr);
try
{
var s1 = sqlhelper.Query(sqlstr.ToString(), 300, sqlpara);
for (var i = 0; i < tables.Count; i++)
{
ExtractionConfigDic.GetExtractionConfigDic()
.UpdateExtractionConfig(
new ExtractionConfig
{
DataBaseId = (int)database.ID,
TableName = tables[i].TableName,
Acqtime = endtime2[i].ToString("yyyy-MM-dd HH:mm:ss")
});
}
return(s1);
}
catch (Exception ex)
{
throw ex;
}
}
break;
case (int)DataBaseType.ACCESSOld:
case (int)DataBaseType.ACCESSNew:
var olepara =
new OleDbParameter[tables.Count * 2];
if (olepara.Length > 0)
{
var endtime3 = new DateTime[tables.Count];
for (var i = 0; i < tables.Count; i++)
{
try
{
var timestr = this.GetLastTimeOrDefaultMin(list, tables[i], database);
var timeTemp = Convert.ToDateTime(timestr).AddSeconds(1);
endtime3[i] = GetDayStepEndTime(timeTemp);
olepara[i * 2] = new OleDbParameter(new StringBuilder().Append("@").Append(acqtime).Append(i).ToString(),
timeTemp);
olepara[i * 2 + 1] = new OleDbParameter(new StringBuilder().Append("@").Append(acqendtime).Append(i).ToString(),
endtime3[i]);
}
catch (Exception ex)
{
throw ex;
}
}
var oledbhelper = new DbHelperOleDbP(connstr);
string[] sqlstrings = sqlstr.ToString().Split(';');
DataSet[] ds = new DataSet[tables.Count];
for (int i = 0; i < tables.Count; i++)
{
ds[i] = new DataSet();
}
for (int i = 0; i < tables.Count; i++)
{
//@MODIFY 2015-01-12 参数化SQL存在错误？
var sql = sqlstrings[i].Replace("@" + acqtime + i.ToString(), "#" + Convert.ToDateTime(olepara[i * 2].Value).ToString("yyyy-MM-dd HH:mm:ss") + "#");
sql = sql.Replace("@" + acqendtime + i.ToString(), "#" + Convert.ToDateTime(olepara[i * 2 + 1].Value).ToString("yyyy-MM-dd HH:mm:ss") + "#");
ds[i] = oledbhelper.Query(sql);
//ds[i] = oledbhelper.Query(sqlstrings[i], olepara[i * 2], olepara[i * 2 + 1]);
ExtractionConfigDic.GetExtractionConfigDic()
.UpdateExtractionConfig(
new ExtractionConfig
{
DataBaseId = (int)database.ID,
TableName = tables[i].TableName,
Acqtime = endtime3[i].ToString("yyyy-MM-dd HH:mm:ss")
});
}
DataSet retSet = new DataSet();
for (int i = 0; i < tables.Count; i++)
{
retSet.Merge(ds[i]);
}
return(retSet);
//return oledbhelper.Query(sqlstr.ToString(), olepara);
}
break;
default:
return(new DataSet());
}//switch
return(new DataSet());
}
public IList <Data> SelectByTime(DataBaseName path)
{
if (path.DataBaseType == (int)DataBaseType.Shake)
{
ArtAccDatum artdata = null;
string errorstr = string.Empty;
var list = new List <Data>();
DateTime readtime;
DateTime extratime = new DateTime(2013, 1, 1);
string[] timeformats =
{
"yyyy/MM/dd HH:mm:ss", "yyyy/MM/dd HH:mm:ss.fff", "yyyy-MM-dd HH:mm:ss",
"yyyy-MM-dd HH:mm:ss.fff", "yyyyMMddHHmmss", "yyyyMMddHHmmss.fff",
"yyyy-MM-dd h:mm:ss"
}; //TIM_ 2014 05 03 16 17 07
bool isSuccess = DateTime.TryParseExact(
ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)path.ID, 0.ToString(CultureInfo.InvariantCulture)).Acqtime,
timeformats,
CultureInfo.CurrentCulture,
DateTimeStyles.None,
out readtime);
if (!isSuccess)
{
readtime = new DateTime(2013, 1, 1);
}
string[] files = BinaryFileHelper.GetFileNames(path.Location, "*.usb", readtime, TxtDateType.Vibration);
foreach (string file in files)
{
if (BinaryFileHelper.Read(file, out artdata, out errorstr))
{
var acqtime = BinaryFileHelper.GetFileRecordTime(file, TxtDateType.Vibration);
if (acqtime > extratime)
{
extratime = acqtime;
}
var directoryName = Path.GetDirectoryName(file);
if (directoryName != null)
{
var pts = directoryName.Split(Path.DirectorySeparatorChar);
int mod = -1;
int.TryParse(pts.Last(), out mod);
for (var i = 0; i < artdata.MilliVolt.Length; i++)
{
var data = new Data
{
ChannelId = i + 1,
MoudleNo = mod.ToString(CultureInfo.InvariantCulture),
OFlag = artdata.FileHeader.ADPara.Frequency,
DataBaseId = (int)path.ID,
SafeTypeId = (int)SensorCategory.Vibration,
ProjectCode = (short)ProjectInfoDic.GetInstance().GetProjectInfo().ProjectCode,
DataSet = new List <double>()
};
foreach (var value in artdata.MilliVolt[i])
{
data.DataSet.Add(value);
}
data.CollectTime = acqtime;
list.Add(data);
}
}
}
}// foreach
if (extratime > new DateTime(2013, 1, 1))
{
ExtractionConfigDic.GetExtractionConfigDic()
.UpdateExtractionConfig(
new ExtractionConfig
{
DataBaseId = (int)path.ID,
TableName = 0.ToString(CultureInfo.InvariantCulture),
Acqtime = extratime.ToString("yyyyMMddHHmmss.fff")
});
}
return(list);
}
return(new List <Data>());
}
