public ModbusDataWriter(ModbusInitDataArray arr, RWModeEnum mode = RWModeEnum.hh)
{
InitArray = arr;
Headers = new List<string>();
RWMode = mode;
FirstRun = true;
}
public static String GetFileName(String path, ModbusInitDataArray InitArray, RWModeEnum RWMode, DateTime date, bool createDir)
{
string dirName=GetDir(path, InitArray, RWMode, date);
if (createDir) {
Directory.CreateDirectory(dirName);
}
string fileName=String.Format("{0}\\data_{1}.csv",dirName,date.ToString("HH_mm"));
return fileName;
}
public static DateTime GetFileDate(DateTime date, RWModeEnum RWMode, bool correctTime=true)
{
int min=date.Minute;
if (RWMode == RWModeEnum.hh) {
min = min < 30 ? 0 : 30;
}
DateTime dt=new DateTime(date.Year, date.Month, date.Day, date.Hour, min, 0);
dt = dt.AddHours(correctTime ? -Settings.single.HoursDiff : 0);
return dt;
}
public void Process(DateTime DateStart, DateTime DateEnd, RWModeEnum mode)
{
DateTime de = ModbusDataWriter.GetFileDate(DateEnd, mode, false);
DateTime now = ModbusDataWriter.GetFileDate(DateTime.Now, mode, true);
DateEnd = de > now ? now : de;
foreach (string id in InitArrays.Keys)
{
processDate(id, DateStart, DateEnd, mode);
}
}
public static DateTime GetFileDate(DateTime date, RWModeEnum RWMode, bool correctTime = true)
{
int min = date.Minute;
if (RWMode == RWModeEnum.hh)
{
min = min < 30 ? 0 : 30;
}
DateTime dt = new DateTime(date.Year, date.Month, date.Day, date.Hour - (correctTime?Settings.single.HoursDiff:0), min, 0);
return(dt);
}
public static String GetFileName(ModbusInitDataArray InitArray, RWModeEnum RWMode, DateTime date, bool createDir)
{
string dirName = GetDir(InitArray, RWMode, date);
if (createDir)
{
Directory.CreateDirectory(dirName);
}
string fileName = String.Format("{0}\\data_{1}.csv", dirName, date.ToString("HH_mm"));
return(fileName);
}
public ModbusDataWriter(ModbusInitDataArray arr, RWModeEnum mode = RWModeEnum.hh)
{
InitArray = arr;
Headers = new List <int>();
foreach (ModbusInitData data in arr.Data)
{
if (!data.Name.Contains("_FLAG") && !String.IsNullOrEmpty(data.Name))
{
Headers.Add(data.Addr);
}
}
rwMode = mode;
}
public void Process(DateTime needDate, RWModeEnum mode, int depth)
{
DateTime DateEnd = needDate.AddMinutes(0);
DateTime DateStart = needDate.AddMinutes(0);
if (mode == RWModeEnum.hh)
{
DateEnd = ModbusDataWriter.GetFileDate(DateEnd, RWModeEnum.hh).AddMinutes(-30);
DateStart = DateEnd.AddMinutes(-depth * 30);
}
else
{
DateEnd = ModbusDataWriter.GetFileDate(DateEnd, RWModeEnum.min).AddMinutes(-1);
DateStart = DateEnd.AddMinutes(-depth * 1);
}
foreach (string id in InitArrays.Keys)
{
processDate(id, DateStart, DateEnd, mode);
}
}
public static string GetDir(String path,ModbusInitDataArray InitArray, RWModeEnum RWMode, DateTime date)
{
string dirName=String.Format("{0}\\{1}\\{2}\\{3}",path,InitArray.ID,RWMode.ToString(),date.ToString("yyyy_MM_dd"));
return dirName;
}
protected DateTime processDate(DateTime DateStart, DateTime DateEnd,RWModeEnum mode)
{
Logger.Info(String.Format("{0}: {1} {2} -- {3}",DateTime.Now,mode,DateStart,DateEnd));
DateTime date=DateStart.AddHours(0);
SortedList<string,DateTime> dtList=new SortedList<string, DateTime>();
while (date <= DateEnd) {
foreach (string idInitArray in InitArrays.Keys) {
if (!dtList.ContainsKey(idInitArray)) {
dtList.Add(idInitArray, DateTime.MaxValue);
}
Logger.Info(String.Format("=={0} {1}", date, idInitArray));
try {
DataDBWriter writer=Writers[idInitArray];
List<String> fileNames=new List<string>();
fileNames.Add(ModbusDataWriter.GetFileName(Settings.single.DataPath, InitArrays[idInitArray], mode, date, false));
foreach (string path in Settings.single.AddDataPath) {
try {
fileNames.Add(ModbusDataWriter.GetFileName(path, InitArrays[idInitArray], mode, date, false));
} catch { }
}
bool ready=writer.init(fileNames);
if (ready) {
writer.ReadAll();
writer.writeData(mode);
dtList[idInitArray] = date;
Logger.Info("====ok");
}
} catch (Exception e) {
Logger.Error("Ошибка при записи в базу");
Logger.Info(e.ToString());
}
}
date = mode == RWModeEnum.hh ? date.AddMinutes(30) : date.AddMinutes(1);
}
return dtList.Values.Min();
}
public DateTime Process(DateTime DateStart, DateTime DateEnd, RWModeEnum mode)
{
DateTime de=ModbusDataWriter.GetFileDate(DateEnd, mode, false);
DateTime now=ModbusDataWriter.GetFileDate(DateTime.Now, mode, true);
DateEnd = de > now ? now : de;
return processDate( DateStart, DateEnd, mode);
}
public DateTime Process(DateTime needDate, RWModeEnum mode, int depth,DateTime lastProcessed)
{
DateTime DateEnd=needDate.AddMinutes(0);
DateTime DateStart=needDate.AddMinutes(0);
if (mode == RWModeEnum.hh) {
DateEnd = ModbusDataWriter.GetFileDate(DateEnd, RWModeEnum.hh).AddMinutes(-30);
DateStart = DateEnd.AddMinutes(-depth * 30);
} else {
DateEnd = ModbusDataWriter.GetFileDate(DateEnd, RWModeEnum.min).AddMinutes(-1);
DateStart = DateEnd.AddMinutes(-depth * 1);
}
if (lastProcessed < DateStart) {
DateStart = lastProcessed;
}
return processDate( DateStart, DateEnd, mode);
}
public void Process(DateTime needDate, RWModeEnum mode, int depth)
{
DateTime DateEnd=needDate.AddMinutes(0);
DateTime DateStart=needDate.AddMinutes(0);
if (mode == RWModeEnum.hh) {
DateEnd = ModbusDataWriter.GetFileDate(DateEnd, RWModeEnum.hh).AddMinutes(-30);
DateStart = DateEnd.AddMinutes(-depth * 30);
} else {
DateEnd = ModbusDataWriter.GetFileDate(DateEnd, RWModeEnum.min).AddMinutes(-1);
DateStart = DateEnd.AddMinutes(-depth * 1);
}
foreach (string id in InitArrays.Keys) {
processDate(id, DateStart, DateEnd, mode);
}
}
protected void processDate(string idInitArray, DateTime DateStart, DateTime DateEnd,RWModeEnum mode)
{
DateTime date=DateStart.AddHours(0);
while (date <= DateEnd) {
try {
DataDBWriter writer=Writers[idInitArray];
bool ready=writer.init(ModbusDataWriter.GetFileName(InitArrays[idInitArray], mode, date, false));
if (ready) {
writer.ReadAll();
writer.writeData(mode);
}
} catch (Exception e) {
Logger.Error("Ошибка при записи в базу");
Logger.Info(e.ToString());
} finally {
date = date.AddMinutes(30);
}
}
}
public void Process(DateTime DateStart, DateTime DateEnd, RWModeEnum mode)
{
DateTime de=ModbusDataWriter.GetFileDate(DateEnd, mode, false);
DateTime now=ModbusDataWriter.GetFileDate(DateTime.Now, mode, true);
DateEnd = de > now ? now : de;
foreach (string id in InitArrays.Keys) {
processDate(id, DateStart, DateEnd, mode);
}
}
public static string GetDir(ModbusInitDataArray InitArray, RWModeEnum RWMode, DateTime date)
{
string dirName = String.Format("{0}\\{1}\\{2}\\{3}", Settings.single.DataPath, InitArray.ID, RWMode.ToString(), date.ToString("yyyy_MM_dd"));
return(dirName);
}
public void writeData(RWModeEnum mode)
{
SqlConnection con=null;
SqlDataReader reader=null;
SortedList<string,List<string>> inserts=new SortedList<string, List<string>>();
SortedList<string,List<string>> deletes=new SortedList<string, List<string>>();
string insertIntoHeader="INSERT INTO Data (parnumber,object,item,value0,value1,valueMin,valueMax,valueEq,objtype,data_date,rcvstamp,season)";
string frmt="SELECT {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, '{9}', '{10}', {11}";
string frmDel="(parnumber={0} and object={1} and objType={2} and item={3} and data_date='{4}')";
string frmDelAll="(parnumber={0} and object={1} and objType={2} and item={3} and data_date>='{4}' and data_date<='{5}')";
string df=Settings.single.DBDateFormat;
foreach (DataDBRecord rec in Data.Values) {
ModbusInitData init=InitArray.FullData[rec.Header];
if (init.WriteToDBHH || init.WriteToDBMin || init.WriteToDBDiff) {
if (init.WriteToDBMin && mode == RWModeEnum.min) {
string insert=String.Format(frmt, init.ParNumberMin, init.Obj, init.Item, rec.AvgMin,0, rec.Min, rec.Max, rec.Eq, init.ObjType,
Date.AddMinutes(1).ToString(df), DateTime.Now.ToString(df), DBSettings.getSeason(Date.AddMinutes(1)));
string delete=String.Format(frmDel, init.ParNumberMin, init.Obj, init.ObjType, init.Item, Date.AddMinutes(1).ToString(df));
//string delete=String.Format(frmDelAll, init.ParNumberMin, init.Obj, init.ObjType, init.Item, Date.ToString(df), Date.AddMinutes(1).ToString(df));
if (!inserts.ContainsKey(init.DBNameMin)) {
inserts.Add(init.DBNameMin, new List<string>());
}
if (!deletes.ContainsKey(init.DBNameMin)) {
deletes.Add(init.DBNameMin, new List<string>());
}
inserts[init.DBNameMin].Add(insert);
deletes[init.DBNameMin].Add(delete);
}
if (init.WriteToDBHH && mode == RWModeEnum.hh) {
string insert=String.Format(frmt, init.ParNumberHH, init.Obj, init.Item, rec.Avg,0, rec.Min, rec.Max, rec.Eq, init.ObjType,
Date.AddMinutes(30).ToString(df), DateTime.Now.ToString(df), DBSettings.getSeason(Date.AddMinutes(30)));
string delete=String.Format(frmDel, init.ParNumberHH, init.Obj, init.ObjType, init.Item, Date.AddMinutes(30).ToString(df));
//string delete=String.Format(frmDelAll, init.ParNumberHH, init.Obj, init.ObjType, init.Item, Date.ToString(df), Date.AddMinutes(30).ToString(df));
if (!inserts.ContainsKey(init.DBNameHH)) {
inserts.Add(init.DBNameHH, new List<string>());
}
if (!deletes.ContainsKey(init.DBNameHH)) {
deletes.Add(init.DBNameHH, new List<string>());
}
inserts[init.DBNameHH].Add(insert);
deletes[init.DBNameHH].Add(delete);
}
if (init.WriteToDBDiff && mode == RWModeEnum.hh) {
double lastVal=Double.NaN;
DateTime lastDate=DateTime.Now;
try {
string select=String.Format(
"SELECT TOP 1 data_date,VALUE0 FROM DATA WHERE ParNumber={0} and Object={1} and ObjType={2} and Item={3} and Data_date<'{4}' order by DATA_DATE desc",
init.ParNumberDiff, init.Obj, init.ObjType, init.Item, rec.DiffVals.First().Key.ToString(df));
con = PiramidaAccess.getConnection(init.DBNameDiff);
con.Open();
SqlCommand command=null;
command = con.CreateCommand();
command.CommandText = select;
reader=command.ExecuteReader();
if (reader.Read()) {
lastDate = Convert.ToDateTime(reader[0]);
lastVal = Convert.ToInt32(reader[1]);
}
//lastVal = (double)command.ExecuteScalar();
} catch {
} finally {
try { reader.Close(); } catch { }
try { con.Close(); } catch { }
}
if (!Double.IsNaN(lastVal)) {
if (Math.Abs(lastVal - rec.DiffVals.First().Value) < init.Diff) {
rec.DiffVals.RemoveAt(0);
}
}
double timeChange;
DateTime prevDate=Double.IsNaN(lastVal) ? (rec.DiffVals.Count > 0 ? rec.DiffVals.First().Key : DateTime.Now) : lastDate;
string delete=String.Format(frmDelAll, init.ParNumberDiff, init.Obj, init.ObjType, init.Item,
Date.ToString(df), Date.AddMinutes(30).ToString(df));
if (!deletes.ContainsKey(init.DBNameDiff)) {
deletes.Add(init.DBNameDiff, new List<string>());
}
deletes[init.DBNameDiff].Add(delete);
foreach (KeyValuePair<DateTime,double>diff in rec.DiffVals) {
timeChange = (diff.Key.Ticks - prevDate.Ticks) / (10000000.0*60.0);
string insert=String.Format(frmt, init.ParNumberDiff, init.Obj, init.Item, diff.Value, timeChange, diff.Value, diff.Value, diff.Value, init.ObjType,
diff.Key.ToString(df), DateTime.Now.ToString(df), DBSettings.getSeason(diff.Key));
if (!inserts.ContainsKey(init.DBNameDiff)) {
inserts.Add(init.DBNameDiff, new List<string>());
}
inserts[init.DBNameDiff].Add(insert);
prevDate = diff.Key;
}
}
}
}
SortedList<string,SqlConnection> connections=new SortedList<string, SqlConnection>();
SortedList<string,SqlTransaction> transactions=new SortedList<string, SqlTransaction>();
foreach (KeyValuePair<string,List<string>> de in deletes) {
SqlConnection conect = PiramidaAccess.getConnection(de.Key);
conect.Open();
SqlTransaction transact=conect.BeginTransaction("Start_" + de.Key);
connections.Add(de.Key, conect);
transactions.Add(de.Key, transact);
}
foreach (KeyValuePair<string,List<string>> de in deletes) {
con = connections[de.Key];
List<string> qDels=new List<string>();
for (int i=0; i < de.Value.Count; i++) {
qDels.Add(de.Value[i]);
if ((i + 1) % 20 == 0 || i == de.Value.Count - 1) {
string deletesSQL=String.Join(" OR ", qDels);
string deleteSQL=String.Format("{0}\n{1}", "DELETE from DATA where", deletesSQL);
try {
SqlCommand command=null;
command = con.CreateCommand();
command.CommandText = deleteSQL;
command.Transaction = transactions[de.Key];
command.ExecuteNonQuery();
} catch (Exception e) {
Logger.Error("Ошибка в запросе " + e);
Logger.Info(deleteSQL);
} finally {
//try { con.Close(); } catch { }
}
qDels = new List<string>();
}
}
}
foreach (KeyValuePair<string,List<string>> de in inserts) {
con = connections[de.Key];
List<string> qInserts=new List<string>();
for (int i=0; i < de.Value.Count; i++) {
qInserts.Add(de.Value[i]);
if ((i + 1) % 20 == 0 || i == de.Value.Count - 1) {
string insertsSQL=String.Join("\nUNION ALL\n", qInserts);
string insertSQL=String.Format("{0}\n{1}", insertIntoHeader, insertsSQL);
try {
//con.Open();
SqlCommand command=null;
command = con.CreateCommand();
command.CommandText = insertSQL;
command.Transaction = transactions[de.Key];
command.ExecuteNonQuery();
} catch (Exception e) {
Logger.Error("Ошибка в запросе " + e);
Logger.Info(insertSQL);
} finally {
//try { con.Close(); } catch { }
}
qInserts = new List<string>();
}
}
}
foreach (KeyValuePair<string,SqlConnection> de in connections) {
try {
transactions[de.Key].Commit();
} catch { } finally {
try { de.Value.Close(); } catch { }
}
}
}
public void writeData(RWModeEnum mode)
{
SortedList<string,List<string>> inserts=new SortedList<string, List<string>>();
SortedList<string,List<string>> deletes=new SortedList<string, List<string>>();
string insertIntoHeader="INSERT INTO Data (parnumber,object,item,value0,objtype,data_date,rcvstamp,season)";
string frmt="SELECT {0}, {1}, {2}, {3}, {4}, '{5}', '{6}', {7}";
string frmDel="(parnumber={0} and object={1} and objType={2} and item={3} and data_date='{4}')";
foreach (DataDBRecord rec in Data.Values) {
ModbusInitData init=InitArray.FullData[rec.Header];
if (init.WriteToDBHH || init.WriteToDBMin) {
if (init.WriteToDBMin && mode==RWModeEnum.min) {
string insert=String.Format(frmt, init.ParNumberMin, init.Obj, init.Item, rec.Avg, init.ObjType, Date.AddMinutes(1), DateTime.Now, 0);
string delete=String.Format(frmDel, init.ParNumberMin, init.Obj, init.ObjType, init.Item, Date.AddMinutes(1));
if (!inserts.ContainsKey(init.DBNameMin)) {
inserts.Add(init.DBNameMin, new List<string>());
deletes.Add(init.DBNameMin, new List<string>());
}
inserts[init.DBNameMin].Add(insert);
deletes[init.DBNameMin].Add(delete);
}
if (init.WriteToDBHH && mode==RWModeEnum.hh) {
string insert=String.Format(frmt, init.ParNumberHH, init.Obj, init.Item, rec.Avg, init.ObjType, Date.AddMinutes(30), DateTime.Now, 0);
string delete=String.Format(frmDel, init.ParNumberHH, init.Obj, init.ObjType, init.Item, Date.AddMinutes(30));
if (!inserts.ContainsKey(init.DBNameHH)) {
inserts.Add(init.DBNameHH, new List<string>());
deletes.Add(init.DBNameHH, new List<string>());
}
inserts[init.DBNameHH].Add(insert);
deletes[init.DBNameHH].Add(delete);
}
}
}
foreach (KeyValuePair<string,List<string>> de in deletes) {
List<string> qDels=new List<string>();
for (int i=0; i < de.Value.Count; i++) {
qDels.Add(de.Value[i]);
if ((i + 1) % 20 == 0 || i == de.Value.Count - 1) {
string deletesSQL=String.Join(" OR ", qDels);
string deleteSQL=String.Format("{0}\n{1}", "DELETE from DATA where", deletesSQL);
SqlCommand command=null;
command = PiramidaAccess.getConnection(de.Key).CreateCommand();
command.CommandText = deleteSQL;
//Logger.Info(deleteSQL);
try {
command.ExecuteNonQuery();
} catch (Exception e) {
Logger.Error("Ошибка в запросе "+e);
Logger.Info(deleteSQL);
}
qDels = new List<string>();
}
}
}
foreach (KeyValuePair<string,List<string>> de in inserts) {
List<string> qInserts=new List<string>();
for (int i=0; i < de.Value.Count; i++) {
qInserts.Add(de.Value[i]);
if ((i+1)%20==0 ||i==de.Value.Count-1){
string insertsSQL=String.Join("\nUNION ALL\n", qInserts);
string insertSQL=String.Format("{0}\n{1}", insertIntoHeader, insertsSQL);
SqlCommand command=null;
command = PiramidaAccess.getConnection(de.Key).CreateCommand();
command.CommandText = insertSQL;
//Logger.Info(insertSQL);
try{
command.ExecuteNonQuery();
} catch (Exception e) {
Logger.Error("Ошибка в запросе " + e);
Logger.Info(insertSQL);
}
qInserts=new List<string>();
}
}
}
}
public void writeData(RWModeEnum mode)
{
SortedList <string, List <string> > inserts = new SortedList <string, List <string> >();
SortedList <string, List <string> > deletes = new SortedList <string, List <string> >();
string insertIntoHeader = "INSERT INTO Data (parnumber,object,item,value0,objtype,data_date,rcvstamp,season)";
string frmt = "SELECT {0}, {1}, {2}, {3}, {4}, '{5}', '{6}', {7}";
string frmDel = "(parnumber={0} and object={1} and objType={2} and item={3} and data_date='{4}')";
foreach (DataDBRecord rec in Data.Values)
{
ModbusInitData init = InitArray.FullData[rec.Header];
if (init.WriteToDBHH || init.WriteToDBMin)
{
if (init.WriteToDBMin && mode == RWModeEnum.min)
{
string insert = String.Format(frmt, init.ParNumberMin, init.Obj, init.Item, rec.Avg, init.ObjType, Date.AddMinutes(1), DateTime.Now, 0);
string delete = String.Format(frmDel, init.ParNumberMin, init.Obj, init.ObjType, init.Item, Date.AddMinutes(1));
if (!inserts.ContainsKey(init.DBNameMin))
{
inserts.Add(init.DBNameMin, new List <string>());
deletes.Add(init.DBNameMin, new List <string>());
}
inserts[init.DBNameMin].Add(insert);
deletes[init.DBNameMin].Add(delete);
}
if (init.WriteToDBHH && mode == RWModeEnum.hh)
{
string insert = String.Format(frmt, init.ParNumberHH, init.Obj, init.Item, rec.Avg, init.ObjType, Date.AddMinutes(30), DateTime.Now, 0);
string delete = String.Format(frmDel, init.ParNumberHH, init.Obj, init.ObjType, init.Item, Date.AddMinutes(30));
if (!inserts.ContainsKey(init.DBNameHH))
{
inserts.Add(init.DBNameHH, new List <string>());
deletes.Add(init.DBNameHH, new List <string>());
}
inserts[init.DBNameHH].Add(insert);
deletes[init.DBNameHH].Add(delete);
}
}
}
foreach (KeyValuePair <string, List <string> > de in deletes)
{
List <string> qDels = new List <string>();
for (int i = 0; i < de.Value.Count; i++)
{
qDels.Add(de.Value[i]);
if ((i + 1) % 20 == 0 || i == de.Value.Count - 1)
{
string deletesSQL = String.Join(" OR ", qDels);
string deleteSQL = String.Format("{0}\n{1}", "DELETE from DATA where", deletesSQL);
SqlCommand command = null;
command = PiramidaAccess.getConnection(de.Key).CreateCommand();
command.CommandText = deleteSQL;
//Logger.Info(deleteSQL);
try {
command.ExecuteNonQuery();
} catch (Exception e) {
Logger.Error("Ошибка в запросе " + e);
Logger.Info(deleteSQL);
}
qDels = new List <string>();
}
}
}
foreach (KeyValuePair <string, List <string> > de in inserts)
{
List <string> qInserts = new List <string>();
for (int i = 0; i < de.Value.Count; i++)
{
qInserts.Add(de.Value[i]);
if ((i + 1) % 20 == 0 || i == de.Value.Count - 1)
{
string insertsSQL = String.Join("\nUNION ALL\n", qInserts);
string insertSQL = String.Format("{0}\n{1}", insertIntoHeader, insertsSQL);
SqlCommand command = null;
command = PiramidaAccess.getConnection(de.Key).CreateCommand();
command.CommandText = insertSQL;
//Logger.Info(insertSQL);
try{
command.ExecuteNonQuery();
} catch (Exception e) {
Logger.Error("Ошибка в запросе " + e);
Logger.Info(insertSQL);
}
qInserts = new List <string>();
}
}
}
}
protected void processDate(string idInitArray, DateTime DateStart, DateTime DateEnd, RWModeEnum mode)
{
DateTime date = DateStart.AddHours(0);
while (date <= DateEnd)
{
try {
DataDBWriter writer = Writers[idInitArray];
bool ready = writer.init(ModbusDataWriter.GetFileName(InitArrays[idInitArray], mode, date, false));
if (ready)
{
writer.ReadAll();
writer.writeData(mode);
}
} catch (Exception e) {
Logger.Error("Ошибка при записи в базу");
Logger.Info(e.ToString());
} finally {
date = date.AddMinutes(30);
}
}
}
