def volumesEntre(reservatId, inicio, fim):
query = """SELECT data_informacao, volume FROM tb_monitoramento WHERE id_reservatorio=""" + str(
reservatId) + """ AND
data_informacao BETWEEN '""" + str(inicio) + """' AND '""" + str(
fim) + """' ORDER BY data_informacao ASC"""
rows = aux_collection_insert.consulta_BD(query)
return rows
def evap(reservatId):
mes_evap = int(mes)
ano_evap = int(data.year)
mes_dic = {
'1': 'jan',
'2': 'fev',
'3': 'mar',
'4': 'abr',
'5': 'mai',
'6': 'jun',
'7': 'jul',
'8': 'ago',
'9': 'set',
'10': 'out',
'11': 'nov',
'12': 'dez'
}
query = 'SELECT eva_' + mes_dic[str(
mes_evap)] + ' FROM tb_reservatorio WHERE id = ' + str(reservatId)
rows = aux_collection_insert.consulta_BD(query)
evaporacao = rows[0][0]
if len(rows) < 0 or evaporacao == None:
evaporacao = 0.0
if mes_evap == 2:
if ((ano_evap % 4) == 0 and
(ano_evap % 100) != 0) or (ano_evap % 400) == 0:
return (evaporacao / 1000.0) / 29
return (evaporacao / 1000.0) / 28
elif (mes_evap % 2) == 0:
return (evaporacao / 1000.0) / 31
elif mes_evap == 7:
return (evaporacao / 1000.0) / 31
return (evaporacao / 1000.0) / 30
def evap(mes, reservatId):
mes_dic = {'1' : 'jan', '2' : 'fev', '3' : 'mar', '4' : 'abr', '5' : 'mai', '6' : 'jun',
'7' : 'jul', '8' : 'ago', '9' : 'set', '10' : 'out', '11' : 'nov', '12' : 'dez'}
query = 'SELECT eva_' + mes_dic[str(mes)] + ' FROM tb_reservatorio WHERE id = '+str(reservatId)
rows = aux_collection_insert.consulta_BD(query)
evaporacao = rows[0][0] if len(rows) > 0 else 0.0
return (evaporacao / 1000.0) / evapDiv if evaporacao != None else 0.0
def getSeries(reservatId, data):
query = 'SELECT volume FROM tb_monitoramento WHERE id_reservatorio = ' + str(reservatId) + ' AND data_informacao <= \'' + str(data) + '\''
row = aux_collection_insert.consulta_BD(query)
series = []
for vol in row:
series.append(float(vol[0]))
return series
def volumes(reservatId):
query = 'SELECT volume FROM tb_cav WHERE id_reservatorio = ' + str(reservatId)
volumes = aux_collection_insert.consulta_BD(query)
aux = list(volumes)
list_of_volumes = []
for value in aux:
list_of_volumes.append(value[0])
return list_of_volumes
def getSeries(reservatId, data):
query = 'SELECT volume FROM tb_monitoramento WHERE id_reservatorio = ' + str(
reservatId) + ' AND data_informacao <= \'' + str(data) + '\''
row = aux_collection_insert.consulta_BD(query)
series = []
for vol in row:
series.append(float(vol[0]))
return series
def areas(reservatId):
query = 'SELECT area FROM tb_cav WHERE id_reservatorio = ' + str(reservatId)
areas = aux_collection_insert.consulta_BD(query)
aux = list(areas)
list_of_areas = []
for value in aux:
list_of_areas.append(value[0])
return list_of_areas
def areas(reservatId):
query = 'SELECT area FROM tb_cav WHERE id_reservatorio = ' + str(
reservatId)
areas = aux_collection_insert.consulta_BD(query)
aux = list(areas)
list_of_areas = []
for value in aux:
list_of_areas.append(value[0])
return list_of_areas
def volumes(reservatId):
query = 'SELECT volume FROM tb_cav WHERE id_reservatorio = ' + str(
reservatId)
volumes = aux_collection_insert.consulta_BD(query)
aux = list(volumes)
list_of_volumes = []
for value in aux:
list_of_volumes.append(value[0])
return list_of_volumes
def nomes_PB():
query = """SELECT DISTINCT res.nome, res.id
FROM INSA.tb_reservatorio res,
INSA.tb_reservatorio_municipio rm,
INSA.tb_municipio mu,
INSA.tb_estado es
WHERE (
res.id = rm.id_reservatorio AND
rm.id_municipio = mu.id AND
mu.id_estado = es.id AND
es.sigla = 'PB'
)
ORDER BY res.nome ASC"""
rows = aux_collection_insert.consulta_BD(query)
return rows
def evap(mes, reservatId):
mes_dic = {
'1': 'jan',
'2': 'fev',
'3': 'mar',
'4': 'abr',
'5': 'mai',
'6': 'jun',
'7': 'jul',
'8': 'ago',
'9': 'set',
'10': 'out',
'11': 'nov',
'12': 'dez'
}
query = 'SELECT eva_' + mes_dic[str(
mes)] + ' FROM tb_reservatorio WHERE id = ' + str(reservatId)
rows = aux_collection_insert.consulta_BD(query)
evaporacao = rows[0][0] if len(rows) > 0 else 0.0
return (evaporacao / 1000.0) / evapDiv if evaporacao != None else 0.0
def evap(reservatId):
mes_evap = int(mes)
ano_evap = int(data.year)
mes_dic = {'1' : 'jan', '2' : 'fev', '3' : 'mar', '4' : 'abr', '5' : 'mai', '6' : 'jun',
'7' : 'jul', '8' : 'ago', '9' : 'set', '10' : 'out', '11' : 'nov', '12' : 'dez'}
query = 'SELECT eva_' + mes_dic[str(mes_evap)] + ' FROM tb_reservatorio WHERE id = ' + str(reservatId)
rows = aux_collection_insert.consulta_BD(query)
evaporacao = rows[0][0]
if len(rows) < 0 or evaporacao == None:
evaporacao = 0.0
if mes_evap == 2:
if ((ano_evap % 4) == 0 and (ano_evap % 100) != 0) or (ano_evap % 400) == 0:
return (evaporacao / 1000.0) / 29
return (evaporacao / 1000.0) / 28
elif (mes_evap % 2) == 0:
return (evaporacao / 1000.0) / 31
elif mes_evap == 7:
return (evaporacao / 1000.0) / 31
return (evaporacao / 1000.0) / 30
def demandas(data, reservatId):
mes_atual = int(data.month)
mes_limite = mes_atual - 12
ano_atual = int(data.year)
if mes_limite > 0:
ano_limite = ano_atual
else:
ano_limite = ano_atual - 1
mes_limite = 12 + mes_limite
ld = []
query = """SELECT data_informacao, volume FROM tb_monitoramento WHERE id_reservatorio="""+str(reservatId)+""" AND
data_informacao BETWEEN '"""+str(ano_limite)+"""-"""+str(mes_limite)+"""-01' AND '"""+str(ano_atual)+"""-"""+str(mes_atual)+"""-31' ORDER BY data_informacao ASC"""
rows = rowsToList(aux_collection_insert.consulta_BD(query))
if (len(listaAreas) <= 0) or (len(listaVolumes) <= 0):
demanda_res = "NULL"
query = """UPDATE tb_reservatorio SET demanda="""+str(demanda_res)+""" WHERE id="""+str(reservatId)
aux_collection_insert.update_BD(query)
elif len(rows) > 0:
lista_datas = []
lista_dias = []
lista_volumes = []
for i in range(0, len(rows)-3, 2):
data_inicial = rows[i]
data_final = rows[i+2]
vol_inicial = rows[i+1]
vol_final = rows[i+3]
dif_datas = data_final - data_inicial
if (float(vol_inicial) > float(vol_final)) and (dif_datas.days <= 30):
if data_inicial not in lista_datas:
lista_dias.append(data_inicial)
if data_final not in lista_datas:
lista_dias.append(data_final)
lista_volumes.append([vol_inicial, vol_final])
lista_datas.append([data_inicial, data_final])
lista_dem = []
for j in range(len(lista_volumes)):
evapDiv_inicial = 0
evapDiv_final = 0
mes_inicial = int(lista_datas[j][0].month)
for data in lista_dias:
evapDiv_inicial += 1 if mes_inicial == int(data.month) else 0
global evapDiv
evapDiv = evapDiv_inicial
vp_inicial = volumeParcial(mes_inicial, float(lista_volumes[j][0]) * 1000000.00, reservatId)
mes_final = int(lista_datas[j][1].month)
for data in lista_dias:
evapDiv_final += 1 if mes_final == int(data.month) else 0
global evapDiv
evapDiv = evapDiv_inicial
vp_final = volumeParcial(mes_final, float(lista_volumes[j][1]) * 1000000.00, reservatId)
dif_datas = lista_datas[j][1] - lista_datas[j][0]
dem = (vp_inicial - vp_final) / dif_datas.days
lista_dem.append(dem)
demanda_res = sum(lista_dem) / len(lista_dem) if len(lista_dem) > 0 else "NULL"
return demanda_res
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sys
from bs4 import BeautifulSoup
import requests
from datetime import datetime
import time
import aux_collection_insert
reload(sys)
sys.setdefaultencoding('utf8')
ultimos_monitoramentos = aux_collection_insert.consulta_BD("SELECT mon.id, mo.cota, mo.volume, mo.volume_percentual, date_format(mo.data_informacao,'%d-%m-%Y') FROM tb_monitoramento mo RIGHT JOIN (SELECT r.id, max(m.data_informacao) AS maior_data FROM tb_reservatorio r LEFT OUTER JOIN tb_monitoramento m ON r.id=m.id_reservatorio GROUP BY r.id) mon ON mo.id_reservatorio=mon.id AND mon.maior_data=mo.data_informacao;")
formato_data_1 = '%d/%m/%Y'
formato_data_2 = '%d-%m-%Y'
formato_data_3 = '%Y-%m-%d'
data_final = str(datetime.now().strftime(formato_data_2))
cabecalho = ['Codigo','Reservatorio','Cota','Capacidade','Volume','VolumePercentual','DataInformacao']
# aux_collection_insert.update_BD("UPDATE tb_user_reservatorio SET atualizacao_reservatorio = 0;")
for monitoramento in ultimos_monitoramentos:
to_insert = []
reserv = str(monitoramento[0])
if reserv in ('19123','19116','19122','19124','19125','19121','19126'):
continue
def volumeAtual(reservatId):
query = 'SELECT volume FROM mv_monitoramento WHERE id_reservatorio = ' + str(
reservatId)
row = aux_collection_insert.consulta_BD(query)[0][0]
return float(row) * 1000000.0
def volumePassado(reservatId, ultimaData):
query = 'SELECT volume FROM tb_monitoramento WHERE id_reservatorio = ' + str(
reservatId) + ' ORDER BY ABS(DATEDIFF(data_informacao, \'' + str(
ultimaData) + '\')) LIMIT 1'
row = aux_collection_insert.consulta_BD(query)[0][0]
return float(row) * 1000000.0
def outorga(reservatId):
query = """SELECT outorga FROM tb_reservatorio WHERE id="""+str(reservatId)
dem = aux_collection_insert.consulta_BD(query)
return float(dem[0][0]) if len(dem) > 0 and dem[0][0] != None else None
def volumePassado(reservatId, ultimaData):
query = 'SELECT volume FROM tb_monitoramento WHERE id_reservatorio = ' + str(reservatId) + ' ORDER BY ABS(DATEDIFF(data_informacao, \'' + str(ultimaData) + '\')) LIMIT 1'
row = aux_collection_insert.consulta_BD(query)[0][0]
return float(row) * 1000000.0
from datetime import datetime
import aux_collection_insert
reload(sys)
sys.setdefaultencoding('utf8')
def remove_accents(txt):
if (type(txt) is str):
txt= unicode(txt, "utf-8")
return normalize('NFKD', txt).encode('ASCII','ignore').decode('ASCII')
paraiba = aux_collection_insert.consulta_BD("SELECT DISTINCT r.id id_reservatorio, r.nome nome_reservatorio, r.reservat reservat, r.capacidade capacidade,"
" mo2.data_info data_info, mo2.cota cota, mo2.volume volume, mo2.volume_percentual volumePercentual "
"FROM tb_estado e, tb_municipio mu, tb_reservatorio_municipio rm, tb_reservatorio r, "
"(SELECT mon.id id_reservatorio, mo.cota, mo.volume, mo.volume_percentual, date_format(mo.data_informacao,'%d-%m-%Y') data_info "
"FROM tb_monitoramento mo RIGHT JOIN (SELECT r.id, max(m.data_informacao) AS maior_data FROM tb_reservatorio r "
"LEFT OUTER JOIN tb_monitoramento m ON r.id=m.id_reservatorio GROUP BY r.id) mon ON mo.id_reservatorio=mon.id "
"AND mon.maior_data=mo.data_informacao) mo2 WHERE e.id = mu.id_estado and mu.id=rm.id_municipio and rm.id_reservatorio=r.id "
"and r.id=mo2.id_reservatorio and e.sigla='PB';")
url = "http://www.aesa.pb.gov.br/aesa-website/resources/data/volumeAcudes/ultimosVolumes/data.json"
response = urllib.urlopen(url)
to_insert = []
aesa = json.loads(response.read())
for json_reservatorio in aesa:
for reserv in paraiba:
similaridade_acude = fuzz.token_set_ratio(remove_accents(reserv[1]),remove_accents(json_reservatorio["acude"]))
apelido = re.sub(remove_accents(reserv[1])+'|acude|barragem|lagoa|[()-]| do | da | de ','',remove_accents(reserv[2]), flags=re.IGNORECASE).strip()
similaridade_apelido = fuzz.token_set_ratio(remove_accents(apelido),remove_accents(json_reservatorio["acude"]))
capacidade = round(float(json_reservatorio["capacidade"])/1000000,2)
def getClosestDate(reservatId, date):
query = "select data_informacao from INSA.tb_monitoramento WHERE data_informacao >= '" + str(
date) + "' AND id_reservatorio = " + str(
reservatId) + " ORDER BY data_informacao LIMIT 1"
date = aux_collection_insert.consulta_BD(query)[0][0]
return date
import requests
import xml.etree.ElementTree as et
import aux_collection_insert
from datetime import datetime
last_dates = {}
formato_data_1 = '%d/%m/%Y'
formato_data_2 = '%d-%m-%Y'
formato_data_3 = '%Y-%m-%dT%H:%M:%S'
ultimos_monitoramentos = aux_collection_insert.consulta_BD("SELECT mon.id, mo.cota, mo.volume, mo.volume_percentual, date_format(mo.data_informacao,'%d-%m-%Y') FROM tb_monitoramento mo RIGHT JOIN (SELECT r.id, max(m.data_informacao) AS maior_data FROM tb_reservatorio r LEFT OUTER JOIN tb_monitoramento m ON r.id=m.id_reservatorio GROUP BY r.id) mon ON mo.id_reservatorio=mon.id AND mon.maior_data=mo.data_informacao WHERE mon.id IN (19123,19116,19122,19124,19125,19121,19126);")
for monit in ultimos_monitoramentos:
if monit[-1] is not None:
last_dates[monit[0]] = datetime.strptime(monit[-1],formato_data_2)
data_final = datetime.now()
uhes = ['19123','19116','19122','19124','19125','19121','19126']
cabecalho = ['Codigo','Reservatorio','Cota','Capacidade','Volume','VolumePercentual','DataInformacao']
to_insert = []
to_insert_monitoring = []
reserv_info = aux_collection_insert.consulta_BD("SELECT id, capacidade, volume_minimo, volume_util FROM INSA.tb_reservatorio where id in (19123,19116,19122,19124,19125,19121,19126);")
# capacidade, volume_minimo, volume_util
uhe_info = {}
for row in reserv_info:
uhe_info[str(row[0])] = [float(row[1])] + [row[2]] + [row[3]]
for uhe in uhes:
if uhe not in last_dates:
start = datetime.strptime('01/01/1970',formato_data_1)
else:
def getCapacidade(reservatId):
query = "SELECT capacidade FROM tb_reservatorio WHERE id="""+str(reservatId)
cap = aux_collection_insert.consulta_BD(query)[0][0]
return cap
def volumeMorto(reservatId):
query = 'SELECT volume_morto FROM tb_reservatorio WHERE id = ' + str(reservatId)
row = aux_collection_insert.consulta_BD(query)[0][0]
return row
def getClosestDate(reservatId, date):
query = "select data_informacao from INSA.tb_monitoramento WHERE data_informacao >= '"+str(date)+"' AND id_reservatorio = "+str(reservatId)+" ORDER BY data_informacao LIMIT 1"
date = aux_collection_insert.consulta_BD(query)[0][0]
return date
def getDate(reservatId):
query = "SELECT data_informacao FROM INSA.mv_monitoramento WHERE id_reservatorio="+str(reservatId)
date = aux_collection_insert.consulta_BD(query)[0][0]
return date
def outorga(reservatId):
query = """SELECT outorga FROM tb_reservatorio WHERE id=""" + str(
reservatId)
dem = aux_collection_insert.consulta_BD(query)
return float(dem[0][0]) if len(dem) > 0 and dem[0][0] != None else None
def getCapacidade(reservatId):
query = "SELECT capacidade FROM tb_reservatorio WHERE id=" "" + str(
reservatId)
cap = aux_collection_insert.consulta_BD(query)[0][0]
return cap
def getDate(reservatId):
query = "SELECT data_informacao FROM INSA.mv_monitoramento WHERE id_reservatorio=" + str(
reservatId)
date = aux_collection_insert.consulta_BD(query)[0][0]
return date
def demandas(data, reservatId):
mes_atual = int(data.month)
mes_limite = mes_atual - 12
ano_atual = int(data.year)
if mes_limite > 0:
ano_limite = ano_atual
else:
ano_limite = ano_atual - 1
mes_limite = 12 + mes_limite
ld = []
query = """SELECT data_informacao, volume FROM tb_monitoramento WHERE id_reservatorio=""" + str(
reservatId) + """ AND
data_informacao BETWEEN '""" + str(ano_limite) + """-""" + str(
mes_limite) + """-01' AND '""" + str(ano_atual) + """-""" + str(
mes_atual) + """-31' ORDER BY data_informacao ASC"""
rows = rowsToList(aux_collection_insert.consulta_BD(query))
if (len(listaAreas) <= 0) or (len(listaVolumes) <= 0):
demanda_res = "NULL"
query = """UPDATE tb_reservatorio SET demanda=""" + str(
demanda_res) + """ WHERE id=""" + str(reservatId)
aux_collection_insert.update_BD(query)
elif len(rows) > 0:
lista_datas = []
lista_dias = []
lista_volumes = []
for i in range(0, len(rows) - 3, 2):
data_inicial = rows[i]
data_final = rows[i + 2]
vol_inicial = rows[i + 1]
vol_final = rows[i + 3]
dif_datas = data_final - data_inicial
if (float(vol_inicial) > float(vol_final)) and (dif_datas.days <=
30):
if data_inicial not in lista_datas:
lista_dias.append(data_inicial)
if data_final not in lista_datas:
lista_dias.append(data_final)
lista_volumes.append([vol_inicial, vol_final])
lista_datas.append([data_inicial, data_final])
lista_dem = []
for j in range(len(lista_volumes)):
evapDiv_inicial = 0
evapDiv_final = 0
mes_inicial = int(lista_datas[j][0].month)
for data in lista_dias:
evapDiv_inicial += 1 if mes_inicial == int(data.month) else 0
global evapDiv
evapDiv = evapDiv_inicial
vp_inicial = volumeParcial(mes_inicial,
float(lista_volumes[j][0]) * 1000000.00,
reservatId)
mes_final = int(lista_datas[j][1].month)
for data in lista_dias:
evapDiv_final += 1 if mes_final == int(data.month) else 0
global evapDiv
evapDiv = evapDiv_inicial
vp_final = volumeParcial(mes_final,
float(lista_volumes[j][1]) * 1000000.00,
reservatId)
dif_datas = lista_datas[j][1] - lista_datas[j][0]
dem = (vp_inicial - vp_final) / dif_datas.days
lista_dem.append(dem)
demanda_res = sum(lista_dem) / len(lista_dem) if len(
lista_dem) > 0 else "NULL"
return demanda_res
import requests
import xml.etree.ElementTree as et
import aux_collection_insert
from datetime import datetime
last_dates = {}
formato_data_1 = '%d/%m/%Y'
formato_data_2 = '%d-%m-%Y'
formato_data_3 = '%Y-%m-%dT%H:%M:%S'
ultimos_monitoramentos = aux_collection_insert.consulta_BD(
"SELECT mon.id, mo.cota, mo.volume, mo.volume_percentual, date_format(mo.data_informacao,'%d-%m-%Y') FROM tb_monitoramento mo RIGHT JOIN (SELECT r.id, max(m.data_informacao) AS maior_data FROM tb_reservatorio r LEFT OUTER JOIN tb_monitoramento m ON r.id=m.id_reservatorio GROUP BY r.id) mon ON mo.id_reservatorio=mon.id AND mon.maior_data=mo.data_informacao WHERE mon.id IN (19123,19116,19122,19124,19125,19121,19126);"
)
for monit in ultimos_monitoramentos:
if monit[-1] is not None:
last_dates[monit[0]] = datetime.strptime(monit[-1], formato_data_2)
data_final = datetime.now()
uhes = ['19123', '19116', '19122', '19124', '19125', '19121', '19126']
cabecalho = [
'Codigo', 'Reservatorio', 'Cota', 'Capacidade', 'Volume',
'VolumePercentual', 'DataInformacao'
]
to_insert = []
to_insert_monitoring = []
reserv_info = aux_collection_insert.consulta_BD(
"SELECT id, capacidade, volume_minimo, volume_util FROM INSA.tb_reservatorio where id in (19123,19116,19122,19124,19125,19121,19126);"
)
# capacidade, volume_minimo, volume_util
uhe_info = {}
def volumesEntre(reservatId, inicio, fim):
query = """SELECT data_informacao, volume FROM tb_monitoramento WHERE id_reservatorio="""+str(reservatId)+""" AND
data_informacao BETWEEN '"""+str(inicio)+"""' AND '"""+str(fim)+"""' ORDER BY data_informacao ASC"""
rows = aux_collection_insert.consulta_BD(query)
return rows
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sys
from bs4 import BeautifulSoup
import requests
from datetime import datetime
import time
import aux_collection_insert
reload(sys)
sys.setdefaultencoding('utf8')
ultimos_monitoramentos = aux_collection_insert.consulta_BD(
"SELECT mon.id, mo.cota, mo.volume, mo.volume_percentual, date_format(mo.data_informacao,'%d-%m-%Y') FROM tb_monitoramento mo RIGHT JOIN (SELECT r.id, max(m.data_informacao) AS maior_data FROM tb_reservatorio r LEFT OUTER JOIN tb_monitoramento m ON r.id=m.id_reservatorio GROUP BY r.id) mon ON mo.id_reservatorio=mon.id AND mon.maior_data=mo.data_informacao;"
)
formato_data_1 = '%d/%m/%Y'
formato_data_2 = '%d-%m-%Y'
formato_data_3 = '%Y-%m-%d'
data_final = str(datetime.now().strftime(formato_data_2))
cabecalho = [
'Codigo', 'Reservatorio', 'Cota', 'Capacidade', 'Volume',
'VolumePercentual', 'DataInformacao'
]
# aux_collection_insert.update_BD("UPDATE tb_user_reservatorio SET atualizacao_reservatorio = 0;")
for monitoramento in ultimos_monitoramentos:
to_insert = []
def volumeMorto(reservatId):
query = 'SELECT volume_morto FROM tb_reservatorio WHERE id = ' + str(
reservatId)
row = aux_collection_insert.consulta_BD(query)[0][0]
return row
def get_users(self): query = 'SELECT * FROM tb_user' rows = aux_collection_insert.consulta_BD(query) return rows
def volumeAtual(reservatId):
query = 'SELECT volume FROM mv_monitoramento WHERE id_reservatorio = ' + str(reservatId)
row = aux_collection_insert.consulta_BD(query)[0][0]
return float(row) * 1000000.0
