private int Read_SQL_data_from_meter()
{
try
{
string sqlselect = String.Format("select * from dumpmeters where addr={0} and id={1}", comboBox_addressMeter.Text, comboBox_serialNumber.Text);
DataTable dt = sqlhandler_.ReadSqlTable(sqlselect);
if (dt.Rows.Count > 0)
{
string objSerStr = dt.Rows[0]["dump"].ToString();
DataContractJsonSerializer serializer = new DataContractJsonSerializer(typeof(Mercury230_DatabaseSignals));
Mercury230_DatabaseSignals meterLocal =
(Mercury230_DatabaseSignals)serializer.ReadObject(new System.IO.MemoryStream(Encoding.ASCII.GetBytes(objSerStr)));
WriteParametersFromMeter(meterLocal);
}
else
{
MessageBox.Show("Будут взяты данные по умолчанию\n\r", "Нет информации в БД", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(0);
}
return(1);
}
catch (Exception exc)
{
MessageBox.Show(exc.Message, "Ошибка при чтении БД", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(-1);
}
}
public DataRow ElectMeter_GiveSqlDataRow(int addr, DateTime dt_start, DateTime dt_end, bool forMonth = false, bool alldaysum = false)
{
string formatstr = $"call sum_energy({addr}, '{dt_start:yyyy-MM-dd}', '{dt_end:yyyy-MM-dd}', {forMonth} , {alldaysum})";
try
{
DataTable table = sqlHandler.ReadSqlTable(formatstr);
if (table.Rows.Count > 0)
{
return(table.Rows[0]);
}
return(null);
}
catch
{
return(null);
}
}
static void RefreshMetersLimits()
{
refreshTimer.Enabled = false;
if (Sqlhandler_ == null)
{
return;
}
try
{
var sqlhandlerlocal_ = new SQLhandler(Sqlhandler_.database, Sqlhandler_.DataSource, Sqlhandler_.UserId, Sqlhandler_.Password);
//sqlhandler2_.myConnec.
DataContractJsonSerializer serializer = new DataContractJsonSerializer(typeof(Mercury230_DatabaseSignals));
foreach (Mercury230_DatabaseSignals meter in Meter230_arr)
{
string sqlselect = String.Format("select * from dumpmeters where addr={0} and id={1}", meter.address, meter.serial_number);
DataTable dt = sqlhandlerlocal_.ReadSqlTable(sqlselect);
if (dt.Rows.Count > 0)
{
string objSerStr = dt.Rows[0]["dump"].ToString();
try
{
Mercury230_DatabaseSignals meterLocal =
(Mercury230_DatabaseSignals)serializer.ReadObject(new System.IO.MemoryStream(Encoding.ASCII.GetBytes(objSerStr)));
meter.Phases[0].current.CopyLimits(meterLocal.Phases[0].current);
meter.Phases[1].current.CopyLimits(meterLocal.Phases[1].current);
meter.Phases[2].current.CopyLimits(meterLocal.Phases[2].current);
meter.Phases[0].voltage.CopyLimits(meterLocal.Phases[0].voltage);
meter.Phases[1].voltage.CopyLimits(meterLocal.Phases[1].voltage);
meter.Phases[2].voltage.CopyLimits(meterLocal.Phases[2].voltage);
meter.Phases[0].power.CopyLimits(meterLocal.Phases[0].power);
meter.Phases[1].power.CopyLimits(meterLocal.Phases[1].power);
meter.Phases[2].power.CopyLimits(meterLocal.Phases[2].power);
meter.CommonActivePower.CopyLimits(meterLocal.CommonActivePower);
meter.CommonPower.CopyLimits(meterLocal.CommonPower);
}
catch (Exception exc)
{
Console.WriteLine("Ошибка чтения БД : {0}", exc.Message);
}
}
}
//else
//{
// MessageBox.Show("Будут взяты данные по умолчанию\n\r", "Нет информации в БД", MessageBoxButtons.OK, MessageBoxIcon.Error);
//}
}
catch (Exception exc)
{
logger.Error("Ошибка при чтении БД " + exc.Message);
return;
}
logger.Debug("-----------Лимиты счетчиков обновлены--------------");
}
public void LoadWaterMeters()
{
Action <DataGridViewTextBoxCell, XmlNode, string> writeCellsValue =
(datagridCell, nodes, atr) =>
{
//if (nodes.Count == 0)
// return;
try
{
datagridCell.Value = nodes.Attributes[atr].Value;
}
catch (ArgumentException)
{
datagridCell.Value = "#error#";
return;
}
catch (NullReferenceException)
{
datagridCell.Value = "н/д";
return;
}
};
XmlDocument xmlDocument = new XmlDocument();
// try
// {
xmlDocument.Load("waterMeter_conf.xml");
// Настройка связи с БД
XmlNodeList Nodes = xmlDocument.SelectNodes("//DataBaseSQL");
if (Nodes.Count > 0)
{
sqlHandler = new SQLhandler(Nodes[0].Attributes["Database"].Value, Nodes[0].Attributes["DataSource"].Value, Nodes[0].Attributes["UserId"].Value, Nodes[0].Attributes["Password"].Value);
TableWatermetersData = Nodes[0].Attributes["tabledata"].Value;
TableWatermetersNameLink = Nodes[0].Attributes["tableLinkName"].Value;
}
//
Nodes = xmlDocument.SelectNodes("//watermeter");
WaterMeters = new WaterMeter_Cells[Nodes.Count];
DataGridViewRow row;
if (Nodes.Count > 0)
{
for (int i = 0; i < Nodes.Count; i++)
{
row = new DataGridViewRow();
WaterMeters[i] = new WaterMeter_Cells();
row.Cells.AddRange(
//WaterMeters[i].Status,
WaterMeters[i].NameNode,
WaterMeters[i].Watermeter_SerialNumber,
WaterMeters[i].LocationNode,
WaterMeters[i].Consumer,
WaterMeters[i].Function,
WaterMeters[i].Data
);
this.dataGridView_watermeter.Rows.Add(row);
writeCellsValue(WaterMeters[i].NameNode, Nodes[i].ParentNode /*для имени нужен родительский узел*/, "name");
writeCellsValue(WaterMeters[i].LocationNode, Nodes[i].ParentNode /*нужен родительский узел*/, "location");
writeCellsValue(WaterMeters[i].Consumer, Nodes[i].ParentNode, "consumer");
writeCellsValue(WaterMeters[i].Watermeter_SerialNumber, Nodes[i], "snumber");
writeCellsValue(WaterMeters[i].Function, Nodes[i], "function");
// Подчитаываем сдвиг показаний
if (Nodes[i].ChildNodes != null)
{
foreach (XmlNode nd in Nodes[i].ChildNodes)
{
if (nd.Name == "point")
{
try
{
WaterMeters[i].offsetPoint.SetTimePointFromStrings(nd.Attributes["time"].Value, nd.Attributes["data"].Value);
}
catch (ArgumentException)
{
Console.WriteLine("Ошибка чтения сдвига для счетчика №{0}", WaterMeters[i].Watermeter_SerialNumber.Value);
}
}
}
}
}
}
dataGridView_watermeter.AutoResizeColumns();
/*
* Перекресный запрос по номеру счетчика
* SELECT mdate.* FROM meternamelink mlink, float_watermeter_table_test mdate
* where locate('55144578', mlink.NameMeter) > 0 and mlink.ChID = mdate.chID and flagsMask = 0 ;
*
*
* */
// Корректируем сдвиг показаний, сопоставля показания с прибора учета и базы данных
Task readOffsetFromSql = Task.Factory.StartNew(delegate
{
for (int i = 0; i < WaterMeters.Length; i++)
{
string sqlselect = "SELECT mdate.* FROM " + TableWatermetersNameLink
+ " mlink, " + TableWatermetersData + " mdate where locate('" + WaterMeters[i].Watermeter_SerialNumber.Value.ToString() + "', mlink.NameMeter) > 0 and mlink.ChID = mdate.chID and mdate.flagsMask = 0 and mdate.oleDT < " +
WaterMeters[i].offsetPoint.TimePoint.ToOADate().ToString(System.Globalization.CultureInfo.InvariantCulture) + " order by Id desc limit 1";
DataTable dt = new DataTable();
try
{
dt = sqlHandler.ReadSqlTable(sqlselect);
}
catch (Exception e) {
Console.WriteLine("Ошибка чтения к базе {0}", e.Message);
break;
}
if (dt.Rows.Count > 0)
{
//Console.WriteLine(" Счетчик {0} - Значение доп сдвига {1} время {2}", WaterMeters[i].Watermeter_SerialNumber.Value,
// dt.Rows[0]["Value"],
// DateTime.FromOADate((double)dt.Rows[0]["oleDT"]));
WaterMeters[i].offsetPoint.OffSetFromBase = (float)dt.Rows[0]["Value"];
}
}
Console.WriteLine("Сдвиги водосчетчиков считаны");
});
ReloadWaterMetersTimer = new System.Timers.Timer(3000);
ReloadWaterMetersTimer.Elapsed += this.OnReloadWaterMetersFromMySql;
ReloadWaterMetersTimer.AutoReset = false;
//XmlNodeList devices = xmlDocument.SelectNodes("/watermeters/DataBaseSQL");
//if (devices.Count > 0)
}