using SQLite;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace FLY.HeatingHelper
{
class ThickHeatData4SQLite_V2 : ThickHeatData
{
#region constructor
public ThickHeatData4SQLite_V2() : base() { }
public ThickHeatData4SQLite_V2(string dbname)
:base(dbname)
{
}
#endregion
#region 数据库接口
class Temp
{
public string A { get; set; }
public string B { get; set; }
public string C { get; set; }
}
private SQLiteHelper mSQLiteHelper = new SQLiteHelper();
private string ConnectionString
{
get
{
return string.Format("Data Source={0};Version=3;", DBName);
}
}
/// <summary>
/// 从数据库中获取数据，清除以前数据
/// </summary>
public override void GetDataFromDB(bool reload = false)
{
//Model.Model_HeatData db;
//string connectionString = ConfigurationManager.ConnectionStrings[DBName].ConnectionString.ToString();
//using (db = new Model.Model_HeatData(DBName))
//{
// var result = db.Database.SqlQuery<Temp>("SELECT min(\"Time\")as A,MAX(\"Time\") as B from ThickHeat").First();
// TotalDataFrom = DateTime.Parse(result.A);
// TotalDataTo = DateTime.Parse(result.B);
// DataTo = new DateTime(TotalDataTo.Ticks);
// DataSpan = new TimeSpan(2, 0, 0, 0);
// //DataFrom = DBTo - new TimeSpan(2, 0, 0, 0);
//}
mSQLiteHelper.ConnectionString = ConnectionString;
var dtb = mSQLiteHelper.ExecuteReader("SELECT min(\"Time\")as A,MAX(\"Time\") as B from ThickHeat");
if (dtb.Rows[0].IsNull("A")) return;
TotalDataFrom = DateTime.Parse(dtb.Rows[0].Field<string>("A"));
TotalDataTo = DateTime.Parse(dtb.Rows[0].Field<string>("B"));
LoadDataTo = new DateTime((TotalDataTo+new TimeSpan(0,0,1)).Ticks);
LoadDataSpan = new TimeSpan(2, 0, 0, 0);
string cmdstr;
if (reload || _ids.Count() == 0)
{
if(IsFilterUnstable)
cmdstr = string.Format("select * from [ThickHeat] where IsStable=1 and Time>=\"{0}\" and Time<=\"{1}\"",
(LoadDataTo - LoadDataSpan).ToString("yyyy-MM-dd HH:mm:ss"),
LoadDataTo.ToString("yyyy-MM-dd HH:mm:ss"));
else
cmdstr = string.Format("select * from [ThickHeat] where Time>=\"{0}\" and Time<=\"{1}\"",
(LoadDataTo - LoadDataSpan).ToString("yyyy-MM-dd HH:mm:ss"),
LoadDataTo.ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
if(IsFilterUnstable)
cmdstr = string.Format("select * from [ThickHeat] where ID>\"{0}\" and IsStable=1", _ids.Last());
else
cmdstr = string.Format("select * from [ThickHeat] where ID>\"{0}\"", _ids.Last());
}
dataTable = mSQLiteHelper.ExecuteReader(cmdstr);
//SQLiteDataAdapter dbDataAdapter = new SQLiteDataAdapter(cmdstr, connectionString);
//SQLiteCommandBuilder cb = new SQLiteCommandBuilder(dbDataAdapter);
//dataTable.Clear();
//dbDataAdapter.Fill(dataTable);
//FetchData();
//ResetCluster();
}
class Boltmap
{
public int OldNo;
public int NewNo;
}
/// <summary>
/// 从dataTable中提取数据到内部缓冲区
/// </summary>
protected override int FetchData()
{
_dat_times.Clear();
_thicks.Clear();
_thick_means.Clear();
_thick_2sigmas.Clear();
_heats.Clear();
_ids.Clear();
_resetBolts.Clear();
_rotAngles.Clear();
_mapbacks.Clear();
_mapback_thicks.Clear();
int cnt = 0;
foreach (DataRow dr in dataTable.Rows)
{
AddOneRecord(dr);
cnt++;
}
return cnt;
}
/// <summary>
/// 从dataTable中提取新数据到内部缓冲区
/// </summary>
protected override int FetchNewData()
{
int lastID = _ids.Last();
int cnt = 0;
foreach (DataRow dr in dataTable.Rows)
{
var id = (int)dr.Field<long>("ID");
if (lastID >= id) continue;
AddOneRecord(dr);
cnt++;
}
return cnt;
}
private void AddOneRecord(DataRow dr)
{
string th_dat = dr.Field<string>("Thicks");
string heat_dat = dr.Field<string>("Heats");
var map_dat = dr.Field<string>("Boltmap");
var t1 = Newtonsoft.Json.JsonConvert.DeserializeObject<double[]>(th_dat);
var h1 = Newtonsoft.Json.JsonConvert.DeserializeObject<double[]>(heat_dat);
var map = Newtonsoft.Json.JsonConvert.DeserializeObject<Boltmap[]>(map_dat);
if ((_heats.Count() > 0) && IsDataFilter)
{
if (IsHeatSame(h1, _heats.Last())) DeleteDataLast();
}
_ids.Add((int)dr.Field<long>("ID"));
_dat_times.Add(dr.Field<DateTime>("Time"));
_thicks.Add(t1);
_heats.Add(h1);
var t2 = RemoveNaN(t1);
var r = MathNet.Numerics.Statistics.Statistics.MeanStandardDeviation(t2);
_thick_means.Add(r.Item1);
_thick_2sigmas.Add(r.Item2 * 2);
_resetBolts.Add((int)dr.Field<long>("OrgBoltNo"));
_rotAngles.Add(dr.Field<double>("RAngle"));
if(map != null)
{
Dictionary<int, int> m = new Dictionary<int, int>();
foreach (Boltmap bm in map)
{
m.Add(bm.NewNo - 1, bm.OldNo - 1);
}
_mapbacks.Add(m);
_mapback_thicks.Add(Misc.MyMath.Map(t1, m));
}
else
{
_mapbacks.Add(null);
_mapback_thicks.Add(t1);
}
}
/// <summary>
/// 判断两个加热是否相同
/// </summary>
/// <param name="h1"></param>
/// <param name="h2"></param>
/// <returns></returns>
private bool IsHeatSame(double[] h1, double[] h2)
{
int cnt = h1.Count();
if (h2.Count() != cnt) return false;
for (int i = 0; i < cnt; i++)
{
if (Math.Abs(h1[i] - h2[i]) > 0.1) return false;
}
return true;
}
private void DeleteDataLast()
{
int idx = _ids.Count() - 1;
_ids.RemoveAt(idx);
_dat_times.RemoveAt(idx);
_thicks.RemoveAt(idx);
_thick_2sigmas.RemoveAt(idx);
_thick_means.RemoveAt(idx);
_heats.RemoveAt(idx);
_resetBolts.RemoveAt(idx);
_rotAngles.RemoveAt(idx);
_mapbacks.RemoveAt(idx);
_mapback_thicks.RemoveAt(idx);
}
public override int LoadMetaDataFromDB()
{
mSQLiteHelper.ConnectionString = ConnectionString;
var dtb = mSQLiteHelper.ExecuteReader("SELECT min(\"Time\")as A,MAX(\"Time\") as B from ThickHeat");
if (dtb.Rows[0].IsNull("A")) return -1;
TotalDataFrom = DateTime.Parse(dtb.Rows[0].Field<string>("A"));
TotalDataTo = DateTime.Parse(dtb.Rows[0].Field<string>("B"));
return 0;
}
public override int LoadDataFromDB(int reload)
{
mSQLiteHelper.ConnectionString = ConnectionString;
string cmdstr;
bool clear = false;
if ((reload == 0 && _ids.Count() == 0) || (reload == 1))
{
clear = true;
if(IsFilterUnstable)
cmdstr = string.Format("select * from [ThickHeat] where IsStable=1 and Time>=\"{0}\" and Time<=\"{1}\"",
LoadDataFrom.ToString("yyyy-MM-dd HH:mm:ss"),
LoadDataTo.ToString("yyyy-MM-dd HH:mm:ss"));
else
cmdstr = string.Format("select * from [ThickHeat] where Time>=\"{0}\" and Time<=\"{1}\"",
LoadDataFrom.ToString("yyyy-MM-dd HH:mm:ss"),
LoadDataTo.ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
if(IsFilterUnstable)
cmdstr = string.Format("select * from [ThickHeat] where ID>\"{0}\" and IsStable=1", _ids.Last());
else
cmdstr = string.Format("select * from [ThickHeat] where ID>\"{0}\"", _ids.Last());
}
try
{
dataTable = mSQLiteHelper.ExecuteReader(cmdstr);
if (clear)
{
return FetchData();
}
else
{
return FetchNewData();
}
}
catch
{
return -99;
}
}
private DataTable dataTable = new DataTable();
#endregion
}
}