def getDailyMessage(date):
stats = {}
query = ' SELECT COUNT(id) FROM "Tepl"."Alert_cnt" WHERE status = \'active\' '
args = {'date': date}
result = db.fetchAll(query)
if result:
stats['active'] = result[0]
query = ' SELECT COUNT(id) FROM "Tepl"."Alert_cnt" WHERE date(created_at) = $date '
query = db.queryPrepare(query, args)
result = db.fetchAll(query)
if result:
stats['created'] = result[0]
query = ' SELECT COUNT(id) FROM "Tepl"."Alert_cnt" WHERE status = \'autoclosed\' and date(updated_at) = $date '
query = db.queryPrepare(query, args)
result = db.fetchAll(query)
if result:
stats['autoclosed'] = result[0]
query = ' SELECT COUNT(id) FROM "Tepl"."Alert_cnt" WHERE status = \'closed\' and date(updated_at) = $date '
query = db.queryPrepare(query, args)
result = db.fetchAll(query)
if result:
stats['closed'] = result[0]
dailyReportMessage = fillTemplate(config.dailyReportNoticeTemplate, stats)
return dailyReportMessage
def updateIncidentRegisterDate(self, param_id, date, regtype):
query = ' SELECT count(*) FROM "Tepl"."Alerts_register" where param_id = $param_id and regtype = $regtype'
args = {'param_id': param_id, 'regtype': regtype}
query = db.queryPrepare(query, args)
result = db.fetchAll(query)
args = {'param_id': param_id, 'lastchecked_time': date, 'regtype': regtype}
if result[0] == 0 :
query = ' INSERT INTO "Tepl"."Alerts_register"(param_id, lastchecked_time, regtype) VALUES ($param_id, $lastchecked_time, $regtype) '
else:
query = ' UPDATE "Tepl"."Alerts_register" SET lastchecked_time = $lastchecked_time WHERE param_id = $param_id and regtype= $regtype '
query = db.queryPrepare(query, args)
db.executeInsertUpdate(query)
def getAverageValue(self, timerange):
if timerange[1] - timerange[0] > timedelta(hours=24):
rangetype = "1 day"
else:
rangetype = "1 hour"
query = '\
DROP TABLE IF EXISTS date_range_tmp; \
CREATE TEMPORARY TABLE date_range_tmp("DateValue" timestamp without time zone); \
INSERT INTO date_range_tmp SELECT "Tepl"."GetDateRange"($date_s, $date_e, $rangetype);\
SELECT * FROM date_range_tmp;\
SELECT SUM(CASE WHEN $parameterType = 1 THEN "Delta" ELSE "DataValue" END)/(SELECT COUNT(*) FROM date_range_tmp) FROM "Tepl"."Arhiv_cnt"\
WHERE pr_id = $param_id AND typ_arh = 1 AND "DateValue" IN (SELECT * FROM date_range_tmp);'
args = {
'date_s': timerange[0],
'date_e': timerange[1],
'rangetype': rangetype,
'parameterType': self.parameterType,
'param_id': self.param_id
}
query = db.queryPrepare(query, args)
result = db.fetchAll(query)
if result:
if result[0] < 0:
raise Exception(
'Среднее значение имеет отрицательную величину')
else:
self.lastAverageValue = result[0]
return result[0]
else:
raise Exception('Среднее значение не определено')
def getBalanceMessage(self):
if self.getBalanceAvailability():
iHandler = incidentHandler()
iHandler.updateIncidentRegisterDate(self.param_id, self.date,
'balance')
file = open(config.balanceQueryFile, 'r')
query = file.read()
arguments = {
'date_s': self.date_prev,
'date_e': self.date,
'place': self.place_id,
'type_a': 2
}
query = db.queryPrepare(query, arguments)
result = db.fetchAll(query)
balance = []
if result:
for item in result:
if type(item).__name__ == 'float':
item = round(item, 1)
balance.append(item)
iHandler.createIncident({
'incidentType': 6,
'description': 'Небаланс.',
'self': self
})
return stuff.fillTemplate(config.balanceNoticeTemplate,
{'balance': balance})
return False
else:
return stuff.fillTemplate(config.balanceLackTemplate,
self.balanceLackData)
def getLackOfBalanceData(self, date):
if self.placeType == 1:
query = ' \
SELECT places."Name" \
FROM "Tepl"."ParamResPlc_cnt" prp \
INNER JOIN "Tepl"."Places_cnt" places on prp.plc_id = places.plc_id \
LEFT JOIN "Tepl"."Places_cnt" parentplaces on places.plc_id = parentplaces.place_id \
WHERE places."Name" not in ( \
SELECT places."Name" \
FROM "Tepl"."ParamResPlc_cnt" prp \
INNER JOIN "Tepl"."Places_cnt" places on prp.plc_id = places.plc_id \
LEFT JOIN "Tepl"."Places_cnt" parentplaces on places.plc_id = parentplaces.place_id \
LEFT JOIN "Tepl"."Arhiv_cnt" arhiv on prp.prp_id = arhiv.pr_id \
WHERE places.place_id = $place_id and prp."ParamRes_id" = 1 and arhiv."DateValue" = $date and arhiv.typ_arh = 2 \
) and places.place_id = $place_id and prp."ParamRes_id" = 1; '
args = {'place_id': self.place_id, 'date': date}
query = db.queryPrepare(query, args)
result = db.fetchAll(query)
if not result:
return False
else:
adresses = []
for item in result:
adresses.append(item[0])
return adresses
def getExistingIncidentIsCompleted(self, incident_id):
query = 'SELECT is_completed FROM "Tepl"."Alert_cnt" WHERE id = $incident_id'
args = {'incident_id': incident_id}
query = db.queryPrepare(query, args)
result = db.fetchAll(query)
if result:
return result[0]
return False
def getExistingNotCompletedIncident(self, param_id, incident_type):
query = 'SELECT MAX(id) FROM "Tepl"."Alert_cnt" WHERE status = \'active\' and param_id = $param_id and type = $incident_type and is_completed = False'
args = {'param_id': param_id, 'incident_type': incident_type}
query = db.queryPrepare(query, args)
result = db.fetchAll(query)
if result:
return result[0]
return False
def checkParameterExists(self):
query = ' SELECT prp_id FROM "Tepl"."ParamResPlc_cnt" WHERE prp_id = $param_id '
args = {'param_id': self.param_id}
query = db.queryPrepare(query, args)
result = db.fetchAll(query)
if result:
return True
return False
def closeIncident(self, incident_id, close_type):
if close_type == 1:
status = 'autoclosed'
else:
status = 'closed'
query = 'UPDATE "Tepl"."Alert_cnt" SET status = $status WHERE id = $incident_id '
args = {'status': status, 'incident_id': incident_id}
query = db.queryPrepare(query, args)
db.executeInsertUpdate(query)
def setNewestArchiveTime(self):
query = ' SELECT MAX("DateValue") FROM "Tepl"."Arhiv_cnt" WHERE pr_id = $param_id AND typ_arh = 1 '
args = {'param_id': self.param_id}
query = db.queryPrepare(query, args)
result = db.fetchAll(query)
if result:
self.newestArchiveTime = result[0]
else:
raise Exception('Нет архивных данных')
def getIncidentRegisterDate(self, param_id, regtype):
query = ' SELECT lastchecked_time FROM "Tepl"."Alerts_register" where param_id = $param_id and regtype = $regtype '
args = {'param_id': param_id, 'regtype': regtype}
query = db.queryPrepare(query, args)
result = db.fetchAll(query)
if result:
if len(result) == 1:
return result[0]
else:
raise Exception('Ошибка в регистре')
return False
def setParameterMetadata(self):
query = '\
SELECT paramlist.prp_id as _paramId_, \
paramlist."ParamRes_id" as _paramTypeId, \
paramres."Name" as _paramName, \
place.plc_id as _placeId, \
place."Name" as _placeName, \
place.typ_id as _placeTypeId, \
placetype."Name" as _placeTypeName, \
place.plc_id as _parentPlaceId, \
parentplace."Name" as _parentPlaceName, \
parentplace.typ_id as _parentPlaceTypeId, \
parentplacetype."Name" as _parentPlaceTypeName, \
task."DateStart" as _paramStartDate, \
prop."ValueProp" as _placeCoord, \
paramres."NameGroup" as _placeNameGroup \
FROM "Tepl"."ParamResPlc_cnt" paramlist \
LEFT JOIN "Tepl"."ParametrResourse" paramres on paramlist."ParamRes_id" = paramres."ParamRes_id" \
LEFT JOIN "Tepl"."Places_cnt" place on paramlist.plc_id = place.plc_id \
LEFT JOIN "Tepl"."PlaceTyp_cnt" placetype on place.typ_id = placetype.typ_id \
LEFT JOIN "Tepl"."Places_cnt" parentplace on place.place_id = parentplace.plc_id \
LEFT JOIN "Tepl"."PlaceTyp_cnt" parentplacetype on parentplace.typ_id = parentplacetype.typ_id \
LEFT JOIN (SELECT * FROM "Tepl"."Task_cnt" WHERE tsk_typ = 2 AND "Aktiv_tsk" = True) task on paramlist.prp_id = task.prp_id \
LEFT JOIN (SELECT * FROM "Tepl"."PropPlc_cnt" WHERE prop_id IN (72, 73, 74)) prop on place.plc_id = prop.plc_id \
WHERE paramlist.prp_id = $param_id'
args = {'param_id': self.param_id}
query = db.queryPrepare(query, args)
result = db.fetchAll(query)
if result:
coords = 'https://static-maps.yandex.ru/1.x/?ll=_coords_&l=map&size=450,350&pt=_coords_,flag&z=12'
if not result[12] == None:
placeCoord = coords.replace('_coords_', result[12])
else:
placeCoord = 'https://tsc96.ru/upload/iblock/a5a/a5a129ed8c830e2dcafec7426d4c95d1.jpg'
data = {
'paramTypeId': result[1],
'paramName': result[2],
'placeId': result[3],
'placeName': result[4],
'placeTypeId': result[5],
'placeTypeName': result[6],
'parentPlaceId': result[7],
'parentPlaceName': result[8],
'parentPlaceTypeId': result[9],
'parentPlaceTypeName': result[10],
'paramStartDate': result[11].replace(tzinfo=None),
'placeCoord': placeCoord,
'placeNameGroup': result[13]
}
self.metadata = data
else:
raise Exception('Ошибка загрузки метаданных')
def getCurrenArchiveValue(self):
query = ' SELECT "DataValue", "Delta" FROM "Tepl"."Arhiv_cnt" WHERE pr_id = $param_id AND typ_arh = 1 AND "DateValue" = $date '
args = {'param_id': self.param_id, 'date': self.date}
query = db.queryPrepare(query, args)
result = db.fetchAll(query)
if result:
if self.parameterType == 1:
self.lastArchiveData = round(result[1], 2)
return round(result[1], 2)
if self.parameterType == 2:
self.lastArchiveData = round(result[0], 2)
return round(result[0], 2)
raise Exception('Этот тип параметра не учитывается')
raise Exception('Архивное значение параметра не найдено')
def createIncident(self, incident):
query = 'INSERT INTO "Tepl"."Alert_cnt"("time", param_id, type, status, param_name, place_id, "PARENT", "CHILD", description, staticmap, namegroup, lastarchivedata, is_completed) \
VALUES ($time, $param_id, $type, $status, $param_name, $place_id, $PARENT, $CHILD, $description, $staticmap, $namegroup, $lastarchivedata, $is_completed); '
args = {
'time': incident['self'].date,
'param_id': incident['self'].param_id,
'type': incident['incidentType'],
'status': 'active',
'param_name': incident['self'].metadata['paramName'],
'place_id': incident['self'].metadata['placeId'],
'PARENT': incident['self'].metadata['parentPlaceTypeName'] + ' ' + incident['self'].metadata['parentPlaceName'],
'CHILD': incident['self'].metadata['placeTypeName'] + ' ' + incident['self'].metadata['placeName'],
'description': incident['description'],
'staticmap': incident['self'].metadata['placeCoord'],
'namegroup': incident['self'].metadata['placeNameGroup'],
'lastarchivedata': incident['self'].lastArchiveData,
'lastaverage': incident['self'].lastAverageValue,
'is_completed': False
}
query = db.queryPrepare(query, args)
db.executeInsertUpdate(query)
def updateExistingIncidentIsCompleted(self, incident_id):
query = 'UPDATE "Tepl"."Alert_cnt" SET is_completed = TRUE WHERE id = $incident_id '
args = {'incident_id': incident_id}
query = db.queryPrepare(query, args)
db.executeInsertUpdate(query)