public SortedList <DateTime, double> ReadGGRun() { SqlConnection con = ReportOutputFile.getConnection(); string query = String.Format( @"select format(datestart, 'dd.MM.yyyy') as dt, min(dateStart) as mindate, count(p_avg) as timeRun from SvodTable where dateStart>='{0}' and dateEnd<='{1}' and p_avg>10 group by format(datestart, 'dd.MM.yyyy') order by mindate", StartDate.ToString(DateFormat), EndDate.ToString(DateFormat)); SqlCommand com = con.CreateCommand(); com.CommandText = query; SortedList <DateTime, double> Data = new SortedList <DateTime, double>(); SqlDataReader reader = com.ExecuteReader(); while (reader.Read()) { DateTime dateStart = reader.GetDateTime(reader.GetOrdinal("mindate")); double run = reader.GetInt32(reader.GetOrdinal("timeRun")); Data.Add(dateStart, run); } return(Data); }
public Dictionary <DateTime, SvodDataRecord> ReadSvod(string groupName, double start, double stop, string IsUstGroup) { SqlConnection con = ReportOutputFile.getConnection(); string query = String.Format("Select * from svodTable where dateStart>='{0}' and dateEnd<='{1}' and {2}>={3} and {2}<={4} and {5}>0 order by dateStart", StartDate.ToString(DateFormat), EndDate.ToString(DateFormat), groupName, start.ToString().Replace(",", "."), stop.ToString().Replace(",", "."), IsUstGroup); SqlCommand com = con.CreateCommand(); com.CommandText = query; Dictionary <DateTime, SvodDataRecord> Data = new Dictionary <DateTime, SvodDataRecord>(); SqlDataReader reader = com.ExecuteReader(); while (reader.Read()) { SvodDataRecord rec = new SvodDataRecord(); rec.DateStart = reader.GetDateTime(reader.GetOrdinal("DateStart")); rec.DateEnd = reader.GetDateTime(reader.GetOrdinal("DateEnd")); rec.IsUst = reader.GetBoolean(reader.GetOrdinal("IsUst")); rec.IsUstGP = reader.GetBoolean(reader.GetOrdinal("IsUstGP")); rec.IsUstPP = reader.GetBoolean(reader.GetOrdinal("IsUstPP")); rec.IsUstGPOhl = reader.GetBoolean(reader.GetOrdinal("IsUstGPOhl")); rec.IsUstPPOhl = reader.GetBoolean(reader.GetOrdinal("IsUstPPOhl")); rec.PAvg = reader.GetDouble(reader.GetOrdinal("P_Avg")); rec.PMin = reader.GetDouble(reader.GetOrdinal("P_Min")); rec.PMax = reader.GetDouble(reader.GetOrdinal("P_Max")); rec.LN1Time = reader.GetDouble(reader.GetOrdinal("LN1_Time")); rec.LN2Time = reader.GetDouble(reader.GetOrdinal("LN2_Time")); rec.DN1Time = reader.GetDouble(reader.GetOrdinal("DN1_Time")); rec.DN2Time = reader.GetDouble(reader.GetOrdinal("DN2_Time")); rec.MNU1Time = reader.GetDouble(reader.GetOrdinal("MNU1_Time")); rec.MNU2Time = reader.GetDouble(reader.GetOrdinal("MNU2_Time")); rec.MNU3Time = reader.GetDouble(reader.GetOrdinal("MNU3_Time")); rec.LN1Pusk = reader.GetInt32(reader.GetOrdinal("LN1_Pusk")); rec.LN2Pusk = reader.GetInt32(reader.GetOrdinal("LN2_Pusk")); rec.DN1Pusk = reader.GetInt32(reader.GetOrdinal("DN1_Pusk")); rec.DN2Pusk = reader.GetInt32(reader.GetOrdinal("DN2_Pusk")); rec.MNU1Pusk = reader.GetInt32(reader.GetOrdinal("MNU1_Pusk")); rec.MNU2Pusk = reader.GetInt32(reader.GetOrdinal("MNU2_Pusk")); rec.MNU3Pusk = reader.GetInt32(reader.GetOrdinal("MNU3_Pusk")); rec.GPHot = reader.GetDouble(reader.GetOrdinal("GP_Hot")); rec.GPCold = reader.GetDouble(reader.GetOrdinal("GP_Cold")); rec.GPLevel = reader.GetDouble(reader.GetOrdinal("GP_Level")); rec.PPHot = reader.GetDouble(reader.GetOrdinal("PP_Hot")); rec.PPCold = reader.GetDouble(reader.GetOrdinal("PP_Cold")); rec.PPLevel = reader.GetDouble(reader.GetOrdinal("PP_Level")); rec.GPOhlRashod = reader.GetDouble(reader.GetOrdinal("GP_OhlRash")); rec.PPOhlRashod = reader.GetDouble(reader.GetOrdinal("PP_OhlRash")); Data.Add(rec.DateStart, rec); } return(Data); }
public SortedList <DateTime, PumpDataRecord> ReadDataPump(PumpTypeEnum type, int powerStart, int powerStop) { SqlConnection con = ReportOutputFile.getConnection(); string query = String.Format("Select * from pumpTable where dateStart>='{0}' and dateEnd<='{1}' and isUst=1 and pAvg>={2} and pAvg<={3} and PumpType='{4}' order by dateStart", StartDate.ToString(DateFormat), EndDate.ToString(DateFormat), powerStart, powerStop, type.ToString()); SqlCommand com = con.CreateCommand(); com.CommandText = query; SortedList <DateTime, PumpDataRecord> Data = new SortedList <DateTime, PumpDataRecord>(); SqlDataReader reader = com.ExecuteReader(); while (reader.Read()) { PumpDataRecord rec = new PumpDataRecord(); rec.DateStart = reader.GetDateTime(reader.GetOrdinal("DateStart")); rec.DateEnd = reader.GetDateTime(reader.GetOrdinal("DateEnd")); rec.IsUst = reader.GetBoolean(reader.GetOrdinal("IsUst")); rec.PumpNum = reader.GetInt32(reader.GetOrdinal("PumpNum")); rec.RunTime = reader.GetDouble(reader.GetOrdinal("RunTime")); rec.LevelStart = reader.GetDouble(reader.GetOrdinal("LevelStart")); rec.LevelStop = reader.GetDouble(reader.GetOrdinal("LevelStop")); rec.PAvg = reader.GetDouble(reader.GetOrdinal("PAvg")); rec.PMin = reader.GetDouble(reader.GetOrdinal("PMin")); rec.PMax = reader.GetDouble(reader.GetOrdinal("PMax")); string tp = reader.GetString(reader.GetOrdinal("PumpType")); rec.PumpType = (PumpTypeEnum)Enum.Parse(typeof(PumpTypeEnum), tp); Data.Add(rec.DateStart, rec); } return(Data); }
public SortedList <DateTime, SvodDataRecord> ReadPumpSvod(string groupName, double start, double stop) { SqlConnection con = ReportOutputFile.getConnection(); string query = String.Format( @"select format(datestart, 'dd.MM.yyyy') as dt, min(dateStart) as mindate, sum(ln1_time) as ln1Time, sum(ln2_time) as ln2Time, sum(dn1_time) as dn1Time, sum(dn2_time) as dn2Time, sum(mnu1_time) as mnu1Time, sum(mnu2_time) as mnu2Time, sum(mnu3_time) as mnu3Time, sum(ln1_pusk) as ln1Pusk, sum(ln2_pusk) as ln2Pusk, sum(dn1_pusk) as dn1Pusk, sum(dn2_pusk) as dn2Pusk, sum(mnu1_pusk) as mnu1Pusk, sum(mnu2_pusk) as mnu2Pusk, sum(mnu3_pusk) as mnu3Pusk from SvodTable where dateStart>='{0}' and dateEnd<='{1}' and {2}>={3} and {2}<={4} group by format(datestart, 'dd.MM.yyyy') order by mindate", StartDate.ToString(DateFormat), EndDate.ToString(DateFormat), groupName, start.ToString().Replace(",", "."), stop.ToString().Replace(",", ".")); SqlCommand com = con.CreateCommand(); com.CommandText = query; SortedList <DateTime, SvodDataRecord> Data = new SortedList <DateTime, SvodDataRecord>(); SqlDataReader reader = com.ExecuteReader(); while (reader.Read()) { SvodDataRecord rec = new SvodDataRecord(); rec.DateStart = reader.GetDateTime(reader.GetOrdinal("mindate")); rec.DateEnd = rec.DateStart.AddDays(1); rec.IsUst = true; rec.LN1Time = reader.GetDouble(reader.GetOrdinal("ln1Time")); rec.LN2Time = reader.GetDouble(reader.GetOrdinal("ln2Time")); rec.DN1Time = reader.GetDouble(reader.GetOrdinal("dn1Time")); rec.DN2Time = reader.GetDouble(reader.GetOrdinal("dn2Time")); rec.MNU1Time = reader.GetDouble(reader.GetOrdinal("mnu1Time")); rec.MNU2Time = reader.GetDouble(reader.GetOrdinal("mnu2Time")); rec.MNU3Time = reader.GetDouble(reader.GetOrdinal("mnu3Time")); rec.LN1Pusk = reader.GetInt32(reader.GetOrdinal("ln1Pusk")); rec.LN2Pusk = reader.GetInt32(reader.GetOrdinal("ln2Pusk")); rec.DN1Pusk = reader.GetInt32(reader.GetOrdinal("dn1Pusk")); rec.DN2Pusk = reader.GetInt32(reader.GetOrdinal("dn2Pusk")); rec.MNU1Pusk = reader.GetInt32(reader.GetOrdinal("mnu1Pusk")); rec.MNU2Pusk = reader.GetInt32(reader.GetOrdinal("mnu2Pusk")); rec.MNU3Pusk = reader.GetInt32(reader.GetOrdinal("mnu3Pusk")); Data.Add(rec.DateStart, rec); } return(Data); }
public bool ReadData() { Data = new SortedList <DateTime, SvodDataRecord>(); PumpTypeEnum type = PumpTypeEnum.Drenage; ReportOutputFile outFile = new ReportOutputFile(FileName); try { outFile.ReadData(); } catch (Exception e) { Logger.Info("Ошибка инициализации файла " + FileName); Logger.Info(e.ToString()); return(false); } for (int i = 2; i < outFile.Data.Count; i++) { try { Dictionary <int, string> fileRec = outFile.Data[i]; SvodDataRecord rec = new SvodDataRecord(); rec.DateStart = ReportOutputFile.getDate(fileRec[0]); rec.DateEnd = ReportOutputFile.getDate(fileRec[1]); rec.PMin = ReportOutputFile.getDouble(fileRec[2]); rec.PMax = ReportOutputFile.getDouble(fileRec[3]); rec.PAvg = ReportOutputFile.getDouble(fileRec[4]); rec.LN1Time = ReportOutputFile.getDouble(fileRec[5]); rec.LN2Time = ReportOutputFile.getDouble(fileRec[6]); rec.DN1Time = ReportOutputFile.getDouble(fileRec[7]); rec.DN2Time = ReportOutputFile.getDouble(fileRec[8]); rec.MNU1Time = ReportOutputFile.getDouble(fileRec[9]); rec.MNU2Time = ReportOutputFile.getDouble(fileRec[10]); rec.MNU3Time = ReportOutputFile.getDouble(fileRec[11]); rec.LN1Pusk = (int)ReportOutputFile.getDouble(fileRec[12]); rec.LN2Pusk = (int)ReportOutputFile.getDouble(fileRec[13]); rec.DN1Pusk = (int)ReportOutputFile.getDouble(fileRec[14]); rec.DN2Pusk = (int)ReportOutputFile.getDouble(fileRec[15]); rec.MNU1Pusk = (int)ReportOutputFile.getDouble(fileRec[16]); rec.MNU2Pusk = (int)ReportOutputFile.getDouble(fileRec[17]); rec.MNU3Pusk = (int)ReportOutputFile.getDouble(fileRec[18]); rec.GPHot = ReportOutputFile.getDouble(fileRec[19]); rec.GPCold = ReportOutputFile.getDouble(fileRec[20]); rec.GPLevel = ReportOutputFile.getDouble(fileRec[21]); rec.PPHot = ReportOutputFile.getDouble(fileRec[22]); rec.PPCold = ReportOutputFile.getDouble(fileRec[23]); rec.PPLevel = ReportOutputFile.getDouble(fileRec[24]); double GPLevelMin = ReportOutputFile.getDouble(fileRec[25]); double PPLevelMin = ReportOutputFile.getDouble(fileRec[26]); double GPLevelMax = ReportOutputFile.getDouble(fileRec[27]); double PPLevelMax = ReportOutputFile.getDouble(fileRec[28]); double gpHotMin = ReportOutputFile.getDouble(fileRec[29]); double gpColdMin = ReportOutputFile.getDouble(fileRec[30]); double gpHotMax = ReportOutputFile.getDouble(fileRec[31]); double gpColdMax = ReportOutputFile.getDouble(fileRec[32]); double ppHotMin = ReportOutputFile.getDouble(fileRec[33]); double ppColdMin = ReportOutputFile.getDouble(fileRec[34]); double ppHotMax = ReportOutputFile.getDouble(fileRec[35]); double ppColdMax = ReportOutputFile.getDouble(fileRec[36]); double gpOhlAvg = ReportOutputFile.getDouble(fileRec[37]); double gpOhlMin = ReportOutputFile.getDouble(fileRec[38]); double gpOhlMax = ReportOutputFile.getDouble(fileRec[39]); double ppOhlAvg1 = ReportOutputFile.getDouble(fileRec[40]); double ppOhlMin1 = ReportOutputFile.getDouble(fileRec[41]); double ppOhlMax1 = ReportOutputFile.getDouble(fileRec[42]); double ppOhlAvg2 = ReportOutputFile.getDouble(fileRec[43]); double ppOhlMin2 = ReportOutputFile.getDouble(fileRec[44]); double ppOhlMax2 = ReportOutputFile.getDouble(fileRec[45]); rec.GPOhlRashod = gpOhlAvg; rec.PPOhlRashod = ppOhlAvg1 + ppOhlAvg2; rec.IsUst = IsUst(rec.PMin, rec.PMax, rec.PAvg, 2); rec.IsUstGP = IsUst(GPLevelMin, GPLevelMax, rec.GPLevel, 3); rec.IsUstGP = rec.IsUstGP && IsUst(gpHotMin, gpHotMax, rec.GPHot, 3); rec.IsUstGP = rec.IsUstGP && IsUst(gpColdMin, gpColdMax, rec.GPCold, 3); rec.IsUstPP = IsUst(PPLevelMin, PPLevelMax, rec.PPLevel, 3); rec.IsUstPP = rec.IsUstPP && IsUst(ppHotMin, ppHotMax, rec.PPHot, 3); rec.IsUstPP = rec.IsUstPP && IsUst(ppColdMin, ppColdMax, rec.PPCold, 3); rec.IsUstGPOhl = IsUst(gpOhlMin, gpOhlMax, gpOhlAvg, 5); rec.IsUstPPOhl = IsUst(ppOhlMin1, ppOhlMax1, ppOhlAvg1, 5) && IsUst(ppOhlMin2, ppOhlMax2, ppOhlAvg2, 5); while (Data.ContainsKey(rec.DateStart)) { rec.DateStart = rec.DateStart.AddMilliseconds(1); } Data.Add(rec.DateStart, rec); } catch (Exception e) { Logger.Info("ошибка при разборе строки"); Logger.Info(e.ToString()); return(false); } } return(true); }
public bool writeToDB(int gg) { if (Data.Count == 0) { return(true); } SqlConnection con = ReportOutputFile.getConnection(); try { List <string> insQueries = new List <string>(); string delQ = String.Format("delete FROM SvodTable where DateStart>='{0}' and DateStart<='{1}' and GG={2}", Data.Keys.Min().ToString(DateFormat), Data.Keys.Max().ToString(DateFormat), gg); foreach (KeyValuePair <DateTime, SvodDataRecord> de in Data) { string ins = string.Format(InsertIntoFormat, de.Value.DateStart.ToString(DateFormat), de.Value.DateEnd.ToString(DateFormat), gg, de.Value.PMin.ToString().Replace(",", "."), de.Value.PMax.ToString().Replace(",", "."), de.Value.PAvg.ToString().Replace(",", "."), de.Value.IsUst ? 1 : 0, de.Value.LN1Time.ToString().Replace(",", "."), de.Value.LN2Time.ToString().Replace(",", "."), de.Value.DN1Time.ToString().Replace(",", "."), de.Value.DN2Time.ToString().Replace(",", "."), de.Value.MNU1Time.ToString().Replace(",", "."), de.Value.MNU2Time.ToString().Replace(",", "."), de.Value.MNU3Time.ToString().Replace(",", "."), de.Value.LN1Pusk.ToString().Replace(",", "."), de.Value.LN2Pusk.ToString().Replace(",", "."), de.Value.DN1Pusk.ToString().Replace(",", "."), de.Value.DN2Pusk.ToString().Replace(",", "."), de.Value.MNU1Pusk.ToString().Replace(",", "."), de.Value.MNU2Pusk.ToString().Replace(",", "."), de.Value.MNU3Pusk.ToString().Replace(",", "."), de.Value.GPHot.ToString().Replace(",", "."), de.Value.GPCold.ToString().Replace(",", "."), de.Value.GPLevel.ToString().Replace(",", "."), de.Value.PPHot.ToString().Replace(",", "."), de.Value.PPCold.ToString().Replace(",", "."), de.Value.PPLevel.ToString().Replace(",", "."), de.Value.IsUstGP ? 1 : 0, de.Value.IsUstPP ? 1 : 0, de.Value.GPOhlRashod.ToString().Replace(",", "."), de.Value.PPOhlRashod.ToString().Replace(",", "."), de.Value.IsUstGPOhl ? 1 : 0, de.Value.IsUstPPOhl ? 1 : 0 ); insQueries.Add(ins); } SqlTransaction trans = con.BeginTransaction(); SqlCommand com = con.CreateCommand(); com.CommandText = delQ; com.Transaction = trans; com.ExecuteNonQuery(); string insertStr = String.Format("{0} {1}", InsertIntoHeader, String.Join("\nUNION ALL\n", insQueries)); //Logger.Info(insertStr); SqlCommand comIns = con.CreateCommand(); comIns.CommandText = insertStr; comIns.Transaction = trans; comIns.ExecuteNonQuery(); trans.Commit(); con.Close(); return(true); } catch (Exception e) { Logger.Info(e.ToString()); return(false); } finally { try { con.Close(); } catch { } } }
public bool ReadData() { Data = new SortedList <DateTime, PumpDataRecord>(); PumpTypeEnum type = PumpTypeEnum.Drenage; int pumpNumber = 0; ReportOutputFile outFile = new ReportOutputFile(FileName); try { outFile.ReadData(); FileInfo fi = new FileInfo(FileName); if (fi.Name.Contains("DN_")) { type = PumpTypeEnum.Drenage; } else if (fi.Name.Contains("MNU_")) { type = PumpTypeEnum.MNU; } else if (fi.Name.Contains("LN_")) { type = PumpTypeEnum.Leakage; } else { Logger.Info("Не определен тип насоса в файле " + FileName); return(false); } switch (type) { case PumpTypeEnum.Drenage: pumpNumber = (int)ReportOutputFile.getDouble(outFile.Data[0][3]); break; case PumpTypeEnum.Leakage: pumpNumber = (int)ReportOutputFile.getDouble(outFile.Data[0][3]); break; case PumpTypeEnum.MNU: pumpNumber = (int)ReportOutputFile.getDouble(outFile.Data[0][4]); break; } if (pumpNumber < 0) { Logger.Info("pumpNumber=" + pumpNumber); } }catch (Exception e) { Logger.Info("Ошибка инициализации файла " + FileName); Logger.Info(e.ToString()); return(false); } for (int i = 2; i < outFile.Data.Count; i++) { try { Dictionary <int, string> fileRec = outFile.Data[i]; PumpDataRecord rec = new PumpDataRecord(); rec.DateStart = ReportOutputFile.getDate(fileRec[0]); rec.DateEnd = ReportOutputFile.getDate(fileRec[1]); rec.PAvg = ReportOutputFile.getDouble(fileRec[2]); rec.PMin = ReportOutputFile.getDouble(fileRec[3]); rec.PMax = ReportOutputFile.getDouble(fileRec[4]); rec.RunTime = ReportOutputFile.getDouble(fileRec[5]); rec.LevelStart = ReportOutputFile.getDouble(fileRec[6]); rec.LevelStop = ReportOutputFile.getDouble(fileRec[7]); rec.PumpType = type; rec.PumpNum = pumpNumber; double diff = rec.PAvg / 100 * 2; rec.IsUst = Math.Abs(rec.PAvg - rec.PMax) <= diff; rec.IsUst = rec.IsUst && Math.Abs(rec.PAvg - rec.PMin) <= diff; while (Data.ContainsKey(rec.DateStart)) { rec.DateStart = rec.DateStart.AddMilliseconds(1); } Data.Add(rec.DateStart, rec); }catch (Exception e) { Logger.Info("ошибка при разборе строки"); Logger.Info(e.ToString()); return(false); } } return(true); }
public bool writeToDB(int gg) { if (Data.Count == 0) { return(true); } SqlConnection con = ReportOutputFile.getConnection(); try { Dictionary <PumpTypeEnum, List <DateTime> > DelDates = new Dictionary <PumpTypeEnum, List <DateTime> >(); Dictionary <PumpTypeEnum, string> DelQueries = new Dictionary <PumpTypeEnum, string>(); List <string> insQueries = new List <string>(); foreach (PumpDataRecord rec in Data.Values) { if (!DelDates.ContainsKey(rec.PumpType)) { DelDates.Add(rec.PumpType, new List <DateTime>()); } DelDates[rec.PumpType].Add(rec.DateStart); } foreach (PumpTypeEnum pumpType in DelDates.Keys) { List <DateTime> dd = DelDates[pumpType]; string del = String.Format("delete FROM PumpTable where PumpType='{0}' and DateStart>='{1}' and DateStart<='{2}' and GG={3}", pumpType, dd.Min().ToString(DateFormat), dd.Max().ToString(DateFormat), gg); DelQueries.Add(pumpType, del); } foreach (KeyValuePair <DateTime, PumpDataRecord> de in Data) { string ins = string.Format(InsertIntoFormat, de.Value.DateStart.ToString(DateFormat), de.Value.DateEnd.ToString(DateFormat), gg, de.Value.PumpType.ToString(), de.Value.PumpNum, de.Value.RunTime.ToString().Replace(",", "."), de.Value.LevelStart.ToString().Replace(",", "."), de.Value.LevelStop.ToString().Replace(",", "."), de.Value.PAvg.ToString().Replace(",", "."), de.Value.PMin.ToString().Replace(",", "."), de.Value.PMax.ToString().Replace(",", "."), de.Value.IsUst ? 1 : 0); insQueries.Add(ins); } SqlTransaction trans = con.BeginTransaction(); foreach (string delStr in DelQueries.Values) { SqlCommand com = con.CreateCommand(); com.CommandText = delStr; //Logger.Info(delStr); com.Transaction = trans; com.ExecuteNonQuery(); } string insertStr = String.Format("{0} {1}", InsertIntoHeader, String.Join("\nUNION ALL\n", insQueries)); //Logger.Info(insertStr); SqlCommand comIns = con.CreateCommand(); comIns.CommandText = insertStr; comIns.Transaction = trans; comIns.ExecuteNonQuery(); trans.Commit(); con.Close(); return(true); } catch (Exception e) { Logger.Info(e.ToString()); return(false); } finally { try { con.Close(); } catch { } } }