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 { }
}
}
