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 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 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 getParamCheckList():
query = ' SELECT prp_id FROM "Tepl"."Task_cnt" WHERE tsk_typ = 2 AND "Aktiv_tsk" = True '
result = db.fetchAll(query)
if len(result) == 0:
raise Exception('Нет ни одного параметра')
else:
params = []
for item in result:
params.append(item[0])
return params
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 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 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('Архивное значение параметра не найдено')