/// <summary>
/// 将数据写入数据库
/// </summary>
/// <param name="dataString"></param>
private static void WriteIntoDatabase(byte[] dataByte, DateTime dateTime, OleDbConnection oleDbCon)
{
DatabaseCore dataCore = new DatabaseCore(oleDbCon);
//数据库列名
string[] colName = { "ID", "Year", "Month", "Day", "Hour", "Minute", "Second",
"WindSpeed(m/s)", "AirTemperayure", "Rasiation(W/m2)",
"WindDirection", "Humidity(%RH)",
"Component2Temperature", "Component3Temperature",
"Component4Temperature", "Component5Temperature","Component6Temperature",
};
//string colString = "Year, Month, Day, Hour, Minute, Second, WindSpeed(m/s), AirTemperayure, Rasiation(W/m2), WindDirection, Humidity(%RH), Component1Temperature, Component2Temperature, Component3Temperature,Component4Temperature, Component5Temperature, Component6Temperature";
//string valueString = "";//要插入的语句
Dictionary<string, string> QXdataDic = new Dictionary<string, string>();
//添加日期数据
int year = dateTime.Year;
int month = dateTime.Month;
int day = dateTime.Day;
int hour = dateTime.Hour;
int minute = dateTime.Minute;
int second = dateTime.Second;
int[] time = { year, month, day, hour, minute, second };
for (int i = 0; i < time.Length; i++)
{
QXdataDic.Add(colName[i+1], time[i].ToString()); //第一列不添加
}
//添加气象仪数据
int[] index = { 1, 3, 6, 7, 9, 11, 12, 13, 14, 15 }; //有效通道
double[] precision = { 0.1, 0.1, 1.0, 1.0, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1, }; //每个有效通道数值的精度
for (int i = 0; i < index.Length; i++)
{
int k = (index[i] + 1) * 2; //前面两个字内容无效, 所以+2 , 但由于dataByte的下标从零开始, 所以要-1 即: (index[i]-1+2), 每个数据占两个字节, 所以*2
int value = (dataByte[k] << 8) + dataByte[k + 1]; //高位左移8位地位 = 实际值
if (value >> 15 == 1) //如果最高位为1, 则取补, 否则不改变
{
value = -(0x10000 - value);
}
double dvalue = value * precision[i]; //取精度
QXdataDic.Add(colName[i + 7], dvalue.ToString());
}
try
{
dataCore.InsertData("MeteorologicalData", QXdataDic); //插入数据
}
catch (Exception ex)
{
throw ex;
}
}
public bool Export()
{
OleDbConnection dbCon = DatabaseConnection.GetConnection();
dbCon.Open();
DatabaseCore dc = new DatabaseCore(dbCon);
Dictionary<string, string> query = new Dictionary<string,string>();
query.Add("Year",_dt.Year.ToString());
query.Add("Month",_dt.Month.ToString());
query.Add("Day",_dt.Day.ToString());
System.Data.DataTable ivDt = dc.SelectData("dbo_IVTable", query);
System.Data.DataTable mDt = dc.SelectData("MeteorologicalData", query);
dbCon.Close();
if (ivDt.Rows.Count == 0 && mDt.Rows.Count == 0)
return false;
DataView dv = ivDt.DefaultView;
dv.Sort = "[Hour] ASC, [Minute] ASC, [Second] ASC";
ivDt = dv.ToTable();
dv = mDt.DefaultView;
mDt = dv.ToTable();
string path = System.Environment.CurrentDirectory + "\\Excel\\";
Application excel = new Application();
Workbooks wbks = excel.Workbooks;
Workbook wb = wbks.Add(path + "mb.xlsx");
int row = 2;
foreach(DataRow dr in mDt.Rows)
{
Worksheet wsh = wb.Sheets[3];
int col = 1;
wsh.Cells[row, col++] = dr["Hour"] + ":" + dr["Minute"] + ":" + dr["Second"];
wsh.Cells[row, col++] = dr["WindSpeed(m/s)"];
wsh.Cells[row, col++] = dr["AirTemperayure"];
wsh.Cells[row, col++] = dr["Rasiation(W/m2)"];
wsh.Cells[row, col++] = dr["WindDirection"];
wsh.Cells[row, col++] = dr["Humidity(%RH)"];
string str = "[Hour] = " + dr["Hour"] + " and " + "[Minute] = " + dr["Minute"];
DataRow[] result = ivDt.Select(str);
if (result.Length != 0)
wsh.Cells[row, col++] = result[0]["Component1Temperature"];
else
col++;
wsh.Cells[row, col++] = dr["Component2Temperature"];
wsh.Cells[row, col++] = dr["Component3Temperature"];
wsh.Cells[row, col++] = dr["Component4Temperature"];
wsh.Cells[row, col++] = dr["Component5Temperature"];
wsh.Cells[row, col++] = dr["Component6Temperature"];
row++;
}
int sheet1Row = 4;
int sheet2Row = 3;
foreach (DataRow dr in ivDt.Rows)
{
int sheet;
int col;
GetLocation((int)dr["ComponentId"], (int)dr["Azimuth"], (int)dr["Obliquity"], out sheet, out col);
Worksheet wsh = wb.Sheets[sheet];
if(sheet == 1)
{
wsh.Cells[sheet1Row, 1] = dr["Hour"] + ":" + dr["Minute"] + ":" + dr["Second"];
wsh.Cells[sheet1Row, col++] = dr["OpenCircuitVoltage"];
wsh.Cells[sheet1Row, col++] = dr["ShortCircuitCurrent"];
wsh.Cells[sheet1Row, col++] = dr["MaxPowerVoltage"];
wsh.Cells[sheet1Row, col++] = dr["MaxPowerCurrent"];
wsh.Cells[sheet1Row, col++] = dr["MaxPower"];
++sheet1Row;
}
if (sheet == 2)
{
wsh.Cells[sheet2Row, 1] = dr["Hour"] + ":" + dr["Minute"] + ":" + dr["Second"];
wsh.Cells[sheet2Row, col++] = dr["OpenCircuitVoltage"];
wsh.Cells[sheet2Row, col++] = dr["ShortCircuitCurrent"];
wsh.Cells[sheet2Row, col++] = dr["MaxPowerVoltage"];
wsh.Cells[sheet2Row, col++] = dr["MaxPowerCurrent"];
wsh.Cells[sheet2Row, col++] = dr["MaxPower"];
++sheet2Row;
}
}
wb.SaveAs(path + _dt.Year + "年" + _dt.Month + "月" + _dt.Day + "日.xlsx");
wb.Close();
wbks.Close();
return true;
}
public static bool Analy(DateTime time, List<KeyValuePair<byte, bool>> messageQueue, OleDbConnection oleDbCon, int componentId, int azimuth, int obliquity)
{
DatabaseCore dataCore = new DatabaseCore(oleDbCon);
Dictionary<string, string> IVdataDic = new Dictionary<string, string>();
byte[] byteArray = messageQueue.Select(b => b.Key).ToArray();
string byteStr = Transfer.BaToS(byteArray);
string Regex = @"AA0012019001([A-Za-z0-9_]{800})CC33C33CAA0012029090([A-Za-z0-9_]{800})CC33C33CAA0012091100([A-Za-z0-9_]{34})CC33C33C";
Regex Re = new Regex(Regex);
if (Re.IsMatch(byteStr))
{
Match byteMatch = Re.Match(byteStr);
string reasultStr = byteMatch.Groups[3].Value;
int Tep = Convert.ToInt32(Inverse(reasultStr.Substring(2, 4)), 16);
double Vo = Convert.ToInt32(Inverse(reasultStr.Substring(10, 4)), 16) / 10.0;
double Is = Convert.ToInt32(Inverse(reasultStr.Substring(14, 4)), 16) / 100.0;
double Vm = Convert.ToInt32(Inverse(reasultStr.Substring(18, 4)), 16) / 10.0;
double Im = Convert.ToInt32(Inverse(reasultStr.Substring(22, 4)), 16) / 100.0;
double Pm = Convert.ToInt64(Inverse(reasultStr.Substring(26, 8)), 16) / 10.0;
int index = -1;
//不用while，方朝增本来也这么做，也死循环；每次解析一条就好了
if ((index = byteStr.IndexOf(byteMatch.Value, index + 1)) != -1)
{
for (int i = index / 2; i < (index + byteMatch.Length) / 2; i++)
messageQueue[i] = new KeyValuePair<byte, bool>(messageQueue[i].Key, false);
index = -1;
}
int year = time.Year;
int month = time.Month;
int day = time.Day;
int hour = time.Hour;
int minute = time.Minute;
int second = time.Second;
IVdataDic.Add("Year", year.ToString());
IVdataDic.Add("Month", month.ToString());
IVdataDic.Add("Day", day.ToString());
IVdataDic.Add("Hour", hour.ToString());
IVdataDic.Add("Minute", minute.ToString());
IVdataDic.Add("Second", second.ToString());
IVdataDic.Add("ComponentId", componentId.ToString());
IVdataDic.Add("Component1Temperature", Tep.ToString());
IVdataDic.Add("OpenCircuitVoltage", Vo.ToString());
IVdataDic.Add("ShortCircuitCurrent", Is.ToString());
IVdataDic.Add("MaxPowerVoltage", Vm.ToString());
IVdataDic.Add("MaxPowerCurrent", Im.ToString());
IVdataDic.Add("MaxPower", Pm.ToString());
IVdataDic.Add("Azimuth", azimuth.ToString());
IVdataDic.Add("Obliquity", obliquity.ToString());
IVdataDic.Add("CurrentSeq", "'" + byteMatch.Groups[1].Value + "'");
IVdataDic.Add("VoltageSeq", "'" + byteMatch.Groups[2].Value + "'");
try
{
dataCore.InsertData("dbo_IVTable", IVdataDic);
}
catch (Exception ex)
{
throw ex;
}
}
return true;
}
/// <summary>
/// 获得属于类别partName的所有数据表名
/// </summary>
/// <param name="partName">类别名</param>
/// <returns>属于类别partName的所有数据表名</returns>
public List<string> GetTableNames(string partName)
{
DatabaseCore dc = new DatabaseCore(_sqlCon);
Dictionary<string,string> dict = new Dictionary<string,string>();
dict.Add("PartName",partName);
DataTable dt = dc.SelectData("dbo.Parts",dict);
string partID = dt.Rows[0]["PartID"].ToString();
dict.Clear();
dict.Add("PartID", partID);
dt = dc.SelectData("dbo.Tables", dict);
List<string> result = new List<string>();
foreach (DataRow dr in dt.Rows)
{
result.Add(dr["TableName"].ToString());
}
return result;
}
/// <summary>
/// 获得指定的气象数据表数据
/// </summary>
/// <param name="latitude">纬度</param>
/// <param name="longitude">经度</param>
/// <param name="partName">类别名</param>
/// <param name="tableName">表名</param>
/// <returns>指定的气象数据表数据</returns>
public DataTable GetTableData(double latitude, double longitude, string partName, string tableName)
{
double nearestLatitude;
double nearestLongitude;
GetNearestCoordinate(latitude, longitude, out nearestLatitude, out nearestLongitude);
DatabaseCore dc = new DatabaseCore(_sqlCon);
string partID;
string tableID;
DataTable dataTable;
Dictionary<string, string> queryTerms = new Dictionary<string, string>();
queryTerms.Add("PartName", partName);
DataTable dt = dc.SelectData("dbo.Parts", queryTerms);
if (dt.Rows.Count != 1)
throw new Exception("the result of select is abnormal");
partID = dt.Rows[0]["PartID"].ToString();
queryTerms.Clear();
queryTerms.Add("PartID", partID);
queryTerms.Add("TableName", tableName);
dt = dc.SelectData("dbo.Tables", queryTerms);
if (dt.Rows.Count != 1)
throw new Exception("the result of select is abnormal");
tableID = dt.Rows[0]["TableID"].ToString();
queryTerms.Clear();
queryTerms.Add("TableID", tableID);
queryTerms.Add("Lat", nearestLatitude.ToString());
queryTerms.Add("Lon", nearestLongitude.ToString());
dt = dc.SelectData("dbo.Lines", queryTerms);
if (dt.Rows.Count <= 0)
throw new Exception("the result of select is abnormal");
dataTable = dt;
return dataTable;
}
/// <summary>
/// 获得所有类别名
/// </summary>
/// <returns>所有类别名</returns>
public List<string> GetPartNames()
{
DatabaseCore dc = new DatabaseCore(_sqlCon);
DataTable dt = dc.SelectData("dbo.Parts", null);
List<string> result = new List<string>();
foreach (DataRow dr in dt.Rows)
{
result.Add(dr["PartName"].ToString());
}
return result;
}