/// <summary>
///
/// </summary>
/// <param name="i"></param>
/// <returns></returns>
public byte[] CreateSatateBytes(byte i)
{
log.Info("进入函数CreateSatateBytes");
byte[] packBytes = new byte[7];
packBytes[0] = 0xfe;
packBytes[1] = 0xef;
Array.Copy(
BitConverter.GetBytes(Convert.ToInt16(ProjectInfoDic.GetInstance().GetProjectInfo().ProjectCode)),
0,
packBytes,
2,
2);
packBytes[4] = i;
byte[] crc16 = CheckModeResult.GetCheckResult(packBytes, 0, 3, CheckType.CRC16HighByteFirst);
packBytes[5] = crc16[0];
packBytes[6] = crc16[1];
StringBuilder temp = new StringBuilder();
for (int j = 0; j < 7; j++)
{
temp.Append(packBytes[j]);
}
log.Info("离开函数CreateSatateBytes：" + temp.ToString());
return(packBytes);
}
private void startService()
{
if (this.messagesShowEventHandler != null)
{
this.messagesShowEventHandler(this, new MessagesShowEventArgs {
MessageType = MsgType.Info, MessagesShow = "开始提取"
});
}
portManager.StartService();
var file = new ExeConfigurationFileMap {
ExeConfigFilename = "config/Params.config"
};
Configuration config = ConfigurationManager.OpenMappedExeConfiguration(file, ConfigurationUserLevel.None);
string strInterval = config.AppSettings.Settings["heartbeatInterval"].Value;
if (!int.TryParse(strInterval, out heartbeatInterval))
{
heartbeatInterval = 3;
}
heartbeatInterval = heartbeatInterval * 60000;
if (extractionInterval == null)
{
this.extractionInterval = new Timer(new TimerCallback(ExtractDb), null, Timeout.Infinite, Timeout.Infinite);
}
this.extractionInterval.Change(100, ProjectInfoDic.GetInstance().GetProjectInfo().IntervalTime *60000);
//创建发送数据线程
isready = true;
task = new Task(() => this.Send());
task.Start();
}
private void BtnDelete_Click(object sender, EventArgs e)
{
if (proJectNameTextBox.Text != "" &&
projectNumTextBox.Text != ""
//&& commPortComBox.Text != ""
&& IntervalTimeComboBox.Text != "")
{
var projectSet = new ProjectInfo
{
ProjectName = proJectNameTextBox.Text,
ProjectCode = Convert.ToInt32(projectNumTextBox.Text),
TargetName = commPortComBox.Text,
IntervalTime = Convert.ToInt32(IntervalTimeComboBox.Text),
};
List <ProjectInfo> list = ProjectInfoDic.GetInstance().GetProjectInfos();
int id = 0;
for (int i = 0; i < list.Count; i++)
{
if (list[i].IntervalTime == projectSet.IntervalTime &&
list[i].ProjectCode == projectSet.ProjectCode &&
list[i].ProjectName == projectSet.ProjectName
//&& list[i].TargetName == projectSet.TargetName
)
{
id = Convert.ToInt32(list[i].Id);
break;
}
}
if (id > 0)
{
DialogResult result = MessageBox.Show(@"是否删除？", @"提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
ProjectInfoDic.GetInstance().Delete(id);
BingDataSource();
MessageBox.Show(@"删除成功!");
if (proSetDataGridView.Rows.Count > 0)
{
proSetDataGridView.Rows[0].Selected = false;
}
}
else
{
return;
}
}
else
{
MessageBox.Show(@"删除项不存在!");
}
}
else
{
MessageBox.Show(@"信息不全，无法删除!");
}
}
private void BingDataSource()
{
proSetDataGridView.Columns.Clear();
proSetDataGridView.DataSource = null;
AddColumns();
List <ProjectInfo> list = ProjectInfoDic.GetInstance().GetProjectInfos();
proSetDataGridView.DataSource = list;
}
private IList <Data> SelectAndMove(DataBaseName path)
{
if (path.DataBaseType == (int)DataBaseType.Shake)
{
ArtAccDatum artdata = null;
string errorstr = string.Empty;
var list = new List <Data>();
string[] files = BinaryFileHelper.GetFileNames(path.Location, "*.usb", TxtDateType.Vibration);
//string[] files = Directory.GetFiles(path.Location,"*.usb");
foreach (string file in files)
{
DateTime lasTime;
DateTime acqtime = BinaryFileHelper.GetFileRecordTime(file, TxtDateType.Vibration);
//if (acqtime > lasTime)
{
if (BinaryFileHelper.Read(file, out artdata, out errorstr))
{
var pts = Path.GetDirectoryName(file).Split(Path.DirectorySeparatorChar);
int mod = -1;
int.TryParse(pts.Last(), out mod);
//DateTime acqtime = BinaryFileHelper.GetFileRecordTime(file, TxtDateType.Vibration);
for (int i = 0; i < artdata.MilliVolt.Length; i++)
{
Data data = new Data();
data.ChannelId = i + 1;
//data.MoudleNo = artdata.FileHeader.DeviceNum.ToString().Trim();
data.MoudleNo = mod.ToString();
data.OFlag = artdata.FileHeader.ADPara.Frequency;
data.DataBaseId = (int)path.ID;
data.SafeTypeId = (int)SensorCategory.Vibration;
data.ProjectCode = (short)ProjectInfoDic.GetInstance().GetProjectInfo().ProjectCode;
data.DataSet = new List <double>();
foreach (float value in artdata.MilliVolt[i])
{
data.DataSet.Add(value);
}
data.CollectTime = acqtime;
list.Add(data);
}
MoveFile(file);
}
}
}
return(list);
}
return(new List <Data>());
}
private void dataBsaeToolStripButton_Click(object sender, EventArgs e)
{
Common.Initialise();
List <ProjectInfo> list = ProjectInfoDic.GetInstance().GetProjectInfos();
if (list.Count == 0)
{
MessageBox.Show(@"请先配置项目信息");
return;
}
else
{
var frmConfig = new FrmConfig();
frmConfig.ShowDialog();
}
}
private void button1_Click(object sender, EventArgs e)
{
if (this.CheckInfo())
{
List <ProjectInfo> list = ProjectInfoDic.GetInstance().GetProjectInfos();
if (list.Count > 0)
{
MessageBox.Show(@"项目只能添加一个!请删除后再添加", @"ERROR");
}
else
{
var projectSet = new ProjectInfo
{
ProjectName = proJectNameTextBox.Text,
ProjectCode = Convert.ToInt32(projectNumTextBox.Text),
TargetName = commPortComBox.Text,
IntervalTime = Convert.ToInt32(IntervalTimeComboBox.Text),
Id = 1,
};
for (int i = 0; i < list.Count; i++)
{
if (list[i].IntervalTime == projectSet.IntervalTime &&
list[i].ProjectCode == projectSet.ProjectCode &&
list[i].ProjectName == projectSet.ProjectName &&
list[i].TargetName == projectSet.TargetName)
{
MessageBox.Show(@"项目已存在", @"WARING");
return;
}
}
int issucceed = ProjectInfoDic.GetInstance().AddProjectSetInfo(projectSet);
if (issucceed > 0)
{
MessageBox.Show(@"添加成功", @"INFO");
BingDataSource();
proSetDataGridView.Rows[0].Selected = false;
this.Close();
}
else
{
MessageBox.Show(@"添加失败", @"INFO");
}
}
}
}
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));
}
private void startButton_Click(object sender, EventArgs e)
{
lock (sender)
{
if (start)
{
if (ProjectInfoDic.GetInstance().GetProjectInfos().Count > 0)
{
stop = true;
start = false;
ExtractionManager.GetExtractionManager().StartExtractDb();
ExtractionManager.GetExtractionManager().MessagesShowEventHandler += ExInfo;
tsstate.Text = @"状态:正在提取数据库";
}
else
{
MessageBox.Show(@"请配置项目信息");
}
}
}
}
// =Boolean.Parse()
public IList <Data> TextOrBinarySelect(DataBaseName path)
{
var list = new List <Data>();
if (path.DataBaseType == (int)DataBaseType.Vibration)
{
//DateTime readtime;
//bool isSuccess = ValueHelper.String2Time(
// ExtractionConfigDic.GetExtractionConfigDic().GetExtractionConfig((int)path.ID, string.Empty).Acqtime,out readtime);
//if (!isSuccess)
//{
// readtime = new DateTime(2013, 1, 1);
//}
string[][] files = GetFileNames(path.Location, "*.*", TxtDateType.Vibration);
if (files.Length > 0)
{
foreach (string file in files[0])
{
if (file.EndsWith("odb"))
{
string parastr = file.Remove(file.Length - 3, 3);
parastr += "sdb";
if (files[1].Contains(parastr))
{
var fi = new FileInfo(file);
FileParamStruct para = ReadParamS(parastr);
double[] datas = ReadDatumS(file);
Data data = new Data();
string[] strs = fi.Name.Split(new[] { '_' }, StringSplitOptions.RemoveEmptyEntries);
string strch = strs[1];
data.MoudleNo = strs[0];
data.ChannelId = int.Parse(strch);
data.OFlag = para.diSampleFreq;
data.DataBaseId = (int)path.ID;
data.SafeTypeId = (int)SensorCategory.Vibration;
data.ProjectCode = (short)ProjectInfoDic.GetInstance().GetProjectInfo().ProjectCode;
data.DataSet = datas.ToList();
DateTime acqtime;
string str = strs[2].Trim();
if (strs[2].Contains(".sdb") || strs[2].Contains(".odb"))
{
str = strs[2].Substring(0, strs[2].Length - 4);
}
ValueHelper.String2Time(str, out acqtime);
data.CollectTime = acqtime;
data.Reserve = 0;
if (strs.Length == 4)
{
// 触发采样
data.Reserve = 1;
}
list.Add(data);
//ExtractionConfigDic.GetExtractionConfigDic()
// .UpdateExtractionConfig(
// new ExtractionConfig
// {
// DataBaseId = (int)path.ID,
// TableName = string.Empty,
// Acqtime =
// fi.LastWriteTime.ToString("yyyyMMddHHmmss.fff")
// });
MoveFile(file);
MoveFile(parastr);
}
}
}
}
}
return(list);
}
private PortManager()
{
SerialPortCreator creator = new SerialPortCreator();
comport = creator.CreateCommunicationFactory(ProjectInfoDic.GetInstance().GetProjectInfo().TargetName, new int[] { 9600, 0, 8, 1, 100 });
}
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>());
}