def bind_error_function(self, data, c) -> None:
"""Метод для опредления необходимо ли отслеживание аварии для переменной. Происходит расчет начала и конца временного периода аварии"""
self.__accident_last = self.__accident
if 'byte_bind' in c:
self.__accident = int(
self.transform_data_to_bit(offset=int(c['byte_bind']),
bit=int(c['bit_bind']),
data=data))
if self.__accident == 0:
try:
_conn = createConnection()
_c = _conn.cursor()
_c.execute(
f"""UPDATE mvlab_alarms SET status = 0, end_time = '{str(datetime.datetime.now())}'
WHERE status=1 and text_alarm ='останов машин'""")
_conn.commit()
_conn.close()
except:
cprint.err('Error update records alarm')
# проверяем происходило ли событие до этого
if self.__accident == 1 and self.__accident_last != self.__accident:
self.__accident_temp = self.__accident
if self.__accident_start_time == 0:
# если событие происходит в первый раз то сохраняем с какого периода выбрать данные
if not self.write_to_db_alert:
self.write_to_db_alert = True
_conn = createConnection()
_c = _conn.cursor()
_c.execute(
f"""SELECT * FROM mvlab_alarms WHERE status=1 and text_alarm='останов машин'"""
)
records = _c.fetchall()
if len(records) <= 0:
_c.execute(
'''INSERT INTO mvlab_alarms''' \
""" (text_alarm, status,type_alarm,object_alarm) VALUES ('останов машин','""" + str(
1) + """','alarm','""" + str(c['name']) + """');""")
_conn.commit()
_conn.close()
self.__accident_start_time = datetime.datetime.now(
) - datetime.timedelta(minutes=self.deleay)
self.__accident_end_time = datetime.datetime.now(
) + datetime.timedelta(minutes=self.deleay)
if self.__accident_last != self.__accident:
self.__accident_end_time = datetime.datetime.now(
) + datetime.timedelta(minutes=self.deleay)
self.__transfer_accident_data(self.c['name'])
else:
if (self.__accident_end_time == 0 and not self.transfer_start
and self.__accident_start_time == 0
and self.__accident_temp == 0
and self.__last_update < datetime.datetime.now() -
datetime.timedelta(minutes=self.dleay_upd)):
# self.__transfer_data(self.c['name'])
x = threading.Thread(target=self.__transfer_data,
args=(self.c['name'], ))
x.start()
self.transfer_start = True
def _try_to_connect_db(self):
"""Connected to DB"""
try:
self._conn = createConnection()
self._c = self._conn.cursor()
except:
cprint.err('error connection to DB for ', interrupt=False)
def __transfer_data(self, tablename) -> None:
"""Проверяет сколько времени прошло с мометна последеней записи если вышло за рамки __last_update то перезаписываем в основню таблицу"""
f = '%Y-%m-%d %H:%M:%S'
if (self.__accident_end_time == 0 and
self.__accident_start_time == 0 and
self.__accident_temp == 0 and
self.__last_update < datetime.datetime.now() - datetime.timedelta(minutes=self.dleay_upd)):
_conn = createConnection()
_c = _conn.cursor()
#self._try_to_connect_db()
start_update = datetime.datetime.now() - datetime.timedelta(days=7)
end_update = datetime.datetime.now() - datetime.timedelta(minutes=self.dleay_upd)
start_update = start_update.strftime(f)
end_update = end_update.strftime(f)
sql = "WITH temp as (SELECT n as tt from generate_series('" + str(start_update) + "'::timestamp,'" + str(
end_update) + "'::timestamp,'" + str(self.__interval) + " minute'::interval) n ) \
INSERT INTO mvlab_" + str(
tablename) + " (now_time, value) SELECT tt,(SELECT mode() WITHIN GROUP (ORDER BY value) as modevar FROM mvlab_temp_" + str(
tablename) + " r WHERE r.now_time>b.tt and r.now_time<=(b.tt+('" + str(self.__interval) + " minutes'::interval))) as value \
from mvlab_temp_" + str(tablename) + " a LEFT JOIN temp b ON a.now_time>b.tt and a.now_time<=(b.tt+('" + str(
self.__interval) + " minutes'::interval)) WHERE a.value IS NOT NULL GROUP BY tt ORDER BY tt asc;"
_c.execute(sql)
_conn.commit()
_c.execute(
'''DELETE FROM mvlab_temp_''' + tablename + ''' WHERE
"now_time" >= %s AND
"now_time" < %s ;''', [start_update, end_update])
_conn.commit()
self.__last_update = datetime.datetime.now()
_conn.close()
self.transfer_start = False
def __init__(self,
address: str,
rack: int,
slot: int,
db: int,
start_address_db: int,
offset_db: int,
values_list: list = None,
port=102,
name_connect: str = "",
status=[],
count=0):
"""Класс процесса для подключения к ПЛК по адресу address, с портом port (по умолчанию 102) и получения заданных
значений из блока данных db в промежутке с start_address_db по start_address_db+offset_db
(offset_db - количество забираемых byte из блока). После получения данных разбирает bytearray по
списку values_list.
:param address: ip адрес ПЛК
:param rack: линейка ПЛК (смотри в Step7 или Tia POrtal)
:param slot: номер слота ПЛК (смотри в Step7 или Tia POrtal)
:param db: номер ДБ блока данных в ПЛК
:param start_address_db: начальный адрес ДБ в ПЛК
:param offset_db: количество читаемых байт в ДБ
:param values_list: список значений которые нужно разобрать из bytearray в числовые
:param port: номер порта (по умолчанию 102)
:param name_connect: префикс названия таблиц для подключения
"""
if values_list is None:
values_list = []
self.name_connect = name_connect
self.address = address
self.status = status
self.count = count
self.rack = rack
self.slot = slot
self.port = port
self.DB = db
self.start_address_DB = start_address_db
self.offset_DB = offset_db
self.values_list = values_list
self.bind = {}
self.error_read_data = False
self.last_error = ''
self.bytearray_data = bytearray()
self.values = {}
self._conn = createConnection()
self._c = self._conn.cursor()
self.client = snap7.client.Client()
self.client.set_connection_type(3)
try:
self.client.connect(self.address,
self.rack,
self.slot,
tcpport=self.port)
except:
cprint.cprint.err("NotConnect to PLC")
super(StartProcessOpcForConnectToPLC, self).__init__()
def test_connect_to_sql_server(self):
"""test cjnnection to DB"""
try:
cur = createConnection()
cur.cursor().execute('SELECT 1')
a = True
except psycopg2.OperationalError:
a = False
self.assertEqual(a, True)
def get_data_from_plc():
while True:
time.sleep(2)
try:
data = PlcRemoteUse(PLC_init["address"], PLC_init["rack"], PLC_init["slot"], PLC_init["port"])
data1 = data.get_dashboard_teldafax_value_power()
data2 = data.get_status_machine()
data = {"data1": data1, "data2": data2}
globals()['result_query'] = data
_conn = createConnection()
_c = _conn.cursor()
_c.execute('''CREATE TABLE IF NOT EXISTS mvlab_status_var \
(key serial primary key,now_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, \
json_text TEXT, status int)''')
_conn.commit()
_c.execute('''CREATE TABLE IF NOT EXISTS mvlab_status_connections \
(key serial primary key,now_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, \
json_text TEXT, status int)''')
_conn.commit()
_c.execute('SELECT * from mvlab_status_var limit 1')
records = _c.fetchall()
if len(records)==0:
_c.execute('''INSERT INTO mvlab_status_var''' \
""" (json_text) VALUES ('""" + str(json.dumps(data)) + """');""")
_conn.commit()
#_conn.commit()
else:
ts = time.time()
timestamp = datetime.datetime.fromtimestamp(ts).strftime('%Y-%m-%d %H:%M:%S')
_c.execute(f"UPDATE mvlab_status_var SET json_text='{str(json.dumps(data))}', now_time='{timestamp}'")
_conn.commit()
# return data
ss = []
count = 0
for d in list_connections:
ss.append({'connection_name': d['name'], "ip": d['ip'], 'key': count})
count += 1
data = json.dumps(ss).encode('utf-8')
_c.execute('SELECT * from mvlab_status_var limit 1')
records = _c.fetchall()
if len(records) == 0:
_c.execute('''INSERT INTO mvlab_status_connections''' \
""" (json_text) VALUES ('""" + str(data) + """');""")
_conn.commit()
else:
ts = time.time()
timestamp = datetime.datetime.fromtimestamp(ts).strftime('%Y-%m-%d %H:%M:%S')
_c.execute(f"UPDATE mvlab_status_var SET json_text='{data}', now_time='{timestamp}'")
_conn.commit()
_conn.close()
except Exception as e:
cprint.err(e)
globals()['result_query'] = [{"error": 0}]
def add_to_bd_connections():
try:
_conn = createConnection()
_c = _conn.cursor()
except:
cprint.err('error connection to DB for ', interrupt=False)
_c.execute('''CREATE TABLE IF NOT EXISTS mvlab_connections \
(key serial primary key,now_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, \
json_text TEXT)''')
_conn.commit()
res = json.dumps(list_connections)
_c.execute("""INSERT INTO mvlab_connections (json_text) VALUES ('""" +
str(res) + """');""")
_conn.commit()
def add_connection():
"""function for add connection to file"""
if if_autorize():
return redirect(url_for('login', error='token is addled'))
try:
_conn = createConnection()
_c = _conn.cursor()
_conn.close()
except:
return redirect(
url_for('start_page_err', error='no connection with DB'))
if request.method == 'POST':
json_data = None
with open('connections.json') as json_file:
data = json.load(json_file)
json_data = data
countDataArray = len(json_data['Data'])
try:
ip = request.form['ip'].split(':')[0]
port = request.form['ip'].split(':')[1]
except:
ip = request.form['ip']
port = 102
json_data['connections'].append({
"name": request.form['name'],
"ip": ip,
"port": port,
"rack": request.form['rack'],
"slot": request.form['slot'],
"data": countDataArray,
"timeout": request.form['timeout'],
"reconnect": request.form['reconnect'],
"plc": request.form['plc']
})
json_data['Data'].append([])
with open('connections.json', 'w') as outfile:
json.dump(json_data, outfile)
stop_all_thread()
return redirect(url_for('start_page'))
return render_template('addConnection.html')
def get_dashboard_teldafax_value_power(self, db=500, start=0, offset=170):
try:
data_read = self.client.db_read(db, start, offset)
power1 = int(self.transform_data_to_value(160, 2, data_read,
'int')) / 10
power2 = self.transform_data_to_value(162, 2, data_read,
'int') / 10
power3 = self.transform_data_to_value(164, 2, data_read,
'int') / 10
power4 = self.transform_data_to_value(166, 2, data_read,
'int') / 10
if power1 > 6400:
power1 = 0.0
if power2 > 6400:
power2 = 0.0
if power3 > 6400:
power3 = 0.0
if power4 > 6400:
power4 = 0.0
_conn = createConnection()
_c = _conn.cursor()
sum_power = power1 + power2 + power3 + power4
_c.execute(
'''INSERT INTO mvlab_s300_db300_power_sum_all (value) VALUES ('''
+ str(sum_power) + ''');''')
_conn.commit()
_conn.close()
powers = {
"power1": power1,
'power2': power2,
'power3': power3,
'power4': power4,
'sum_power': sum_power
}
return powers
except:
return {"error": "Нет связи с плк"}