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