def get_data():
"""
retrieve data from DB
"""
from os.path import join
import pyodbc
from db_con_str import con_str
import mpl
cstr = con_str(par.DB)
con = pyodbc.connect(cstr)
cur = con.cursor()
cur.execute(par.S_IDS)
ids = [[row.ID, row.X, row.Y] for row in cur]
ylabel = 'Z (m)'
for id1 in ids:
cur.execute(par.S_D, id1[0])
gpsd = [[row.ID, row.FECHA, row.Z] for row in cur]
if len(gpsd) < 2:
continue
print(id1[0])
xa = [row[1] for row in gpsd]
ya = [row[2] for row in gpsd]
stitle = 'Evolucion de Z en punto GPS {0:d}'.format(id1[0])
namef = 'GPS_{0:03d}'.format(id1[0])
dst = join(par.DIR_OUT, namef)
XYt_1(xa, ya, stitle, ylabel, dst)
break
cur.close()
con.close()
def make_graphs(project):
"""
selecciona los patos para los gráficos y llama a lafunción que dibuja
los gráficos con el modulo matplotlib
input
project: es el tag del fichero XYplus_parameters.f_xml seleccionado
en XYplus_main
"""
from copy import deepcopy
from os.path import join
import pyodbc
import db_con_str
import XYplus_parameters as par
db = project.find('db').text
con = pyodbc.connect(db_con_str.con_str(db))
cur = con.cursor()
select_master = project.find('select_master').text.strip()
cur.execute(select_master)
id_col = int(project.find('select_master').get('id_column')) - 1
cur2 = con.cursor()
fecha_col = int(project.find('select_data').get('fecha_column')) - 1
value_col = int(project.find('select_data').get('value_column')) - 1
dir_out = project.find('dir_out').text.strip()
ylabel = project.find('graph').get('y_axis_name')
for row in cur:
# datos de la serie principal
print(row[id_col])
tmp = _serie_get(project, row, cur2, row[id_col], fecha_col, value_col)
if tmp is None:
continue
series_4xy = [tmp]
# datos de los umbrales. Son series especiales
if par.show_hl == 1:
tss = _umbrales_get(project, row[id_col], cur2, tmp.fechas[0],
tmp.fechas[-1])
for tss1 in tss:
if tss1:
series_4xy.append(deepcopy(tss1))
# elementos adicionales del gráfico
stitle = get_title(project, row)
file_name = file_name_get(project, row)
dst = join(dir_out, file_name)
# dibuja el gráfico
XYt_1(series_4xy, stitle, ylabel, dst)
con.close()
def get_data():
"""
retrieve data from DB
"""
from db_con_str import con_str
cstr = con_str(par.DB)
con = pyodbc.connect(cstr)
cur = con.cursor()
cur.execute(par.SELECT_PS)
gps_s = [[row.ID, row.FECHA, row.Z, row.NOMBRE, row.X, row.Y]
for row in cur]
cur.execute(par.SELECT_D)
gps_all = [[row.ID, row.FECHA, row.Z, row.NOMBRE, row.X, row.Y]
for row in cur]
cur.close()
con.close()
return gps_s, gps_all
def make_graphs(project):
"""
select data
call graph maker
"""
from os.path import join
import pyodbc
import db_con_str
db = project.find('db').text
con = pyodbc.connect(db_con_str.con_str(db))
cur = con.cursor()
select_master = project.find('select_master').text.strip()
cur.execute(select_master)
select_data = project.find('select_data').text.strip()
npar = select_data.count('?')
if npar != 1:
raise ValueError('select_data debe tener un signo ?')
id_col = int(project.find('select_master').get('id_column')) - 1
select_umbrales = project.find('select_umbrales').text.strip()
if select_umbrales.count('?') != 3:
raise ValueError('select_umbrales debe tener 3 signos ?')
umbral_col = int(project.find('select_umbrales').get('umbral_column')) - 1
cur2 = con.cursor()
fecha_col = int(project.find('select_data').get('fecha_column')) - 1
value_col = int(project.find('select_data').get('value_column')) - 1
dir_out = project.find('dir_out').text.strip()
ylabel = project.find('graph').get('y_axis_name')
for row in cur:
# datos de la serie
print(row[id_col])
cur2.execute(select_data, row[id_col])
xy = [(row_data[fecha_col], row_data[value_col]) for row_data in cur2]
if len(xy) == 0:
print('{0} no tiene datos'.format(row[id_col]))
continue
fechas = [xy1[0] for xy1 in xy]
values = [xy1[1] for xy1 in xy]
# datos de los umbrales
values_u = []
legends_umbrales = []
for i, umbral in enumerate(project.findall('select_umbrales/umbral')):
parametro = umbral.get('parametro').strip()
cod_u = umbral.get('cod').strip()
cur2.execute(select_umbrales, (row[id_col], cod_u, parametro))
row1_u = cur2.fetchone()
if row1_u is None:
msg = '{} no tiene umbral: {}, {}'.format(row[id_col],
cod_u, parametro)
print(msg)
lf.write(msg)
continue
values_u.append([row1_u[umbral_col], row1_u[umbral_col]])
legends_umbrales.append(legends_umbrales_get(project, row1_u, i))
if not values_u:
print('{0} no tiene umbrales'.format(row[id_col]))
continue
# todos los umbrales se ponen en el rango de datos de cada sondeo
# si se desea ponerlo en su rango específico debe escribirse una
# función específica
fechas_u = [fechas[0], fechas[-1]]
stitle = get_title(project, row)
legend_master = legend_master_get(project, row)
file_name = file_name_get(project, row)
dst = join(dir_out, file_name)
XYt_1(fechas, values, legend_master, fechas_u, values_u,
legends_umbrales, stitle, ylabel, dst)
con.close()
def control_umbrales(project):
"""
selecciona los datos y hace los análisis
input:
project: tag proyecto seleccionado
"""
from os.path import join
import pyodbc
import numpy as np
import piezo_CTH_parameters as par
import db_con_str
db = project.find('db').text.strip()
tag_pozos = project.findall('point')
tag_umbrales = project.findall('umbral')
cods_u = [tag_u.get('cod').strip() for tag_u in tag_umbrales]
params_u = [tag_u.get('parametro').strip() for tag_u in tag_umbrales]
selected2xy = [int(tag_u.get('selected2xy')) for tag_u in tag_umbrales]
selects_dp = [tag_u.find('select_data_param').text.strip()
for tag_u in tag_umbrales]
select_umbral = project.find('select_umbral').text.strip()
fecha1 = str_to_date(par.fecha1)
fecha2 = str_to_date(par.fecha2)
headers = ['Id pozo', 'Nombre', 'X m', 'Y m', 'Z msnm']
for param_u in params_u:
headers.append('Umbral m ' + param_u)
headers.append('Media m ' + param_u)
headers.append('Media - umbral m ' + param_u)
headers.append('Últ. medida - umbral m ' + param_u)
headers.append('Índice ' + param_u)
headers.append('Oscilación máx NP m')
fmt1 = '{}\t{}\t'+3*'{:0.2f}\t'
# ojo, si se modifican las columnas media, media-umbral y Ultmed-umbral
# hay que cambiar posiblemente fmt2
fmt2 = 3*'\t{:0.2f}'+'\t'
pozos_iue, pozos_iud, values_iue, values_iud = [], [], [], []
fo = open(join(par.dir_out, par.file_out), 'w')
fo.write('\t'.join(headers) + '\n')
con = pyodbc.connect(db_con_str.con_str(db))
cur = con.cursor()
for tag in tag_pozos:
cod = tag.get('cod').strip()
print(cod)
i = 0
maximun = -99999999.
minimun = 99999999.
n_series = 0
for cod_u, param_u, select_dp, toxy in zip(cods_u, params_u,
selects_dp,
selected2xy):
if toxy == 1:
if param_u == 'CNP ND':
pozos = pozos_iud
indexes = values_iud
elif param_u == 'CNP NE':
pozos = pozos_iue
indexes = values_iue
cur.execute(select_umbral, (cod, cod_u, param_u))
row = cur.fetchone()
if row is None:
raise ValueError('{} no tiene umbrales de {} {}'
.format(cod, cod_u, param_u))
toponimia = row.TOPONIMIA
x = row.X_UTM
y = row.Y_UTM
z = row.Z
umbral = row.UMBRAL
cur.execute(select_dp, (cod, fecha1, fecha2))
tmp = [row.CNP for row in cur]
cnp = np.array(tmp, dtype=FLOAT_PRECISION)
if cnp.size > 0:
n_series += 1
mean = np.mean(cnp)
maximun = max(maximun, np.max(cnp))
minimun = min(minimun, np.min(cnp))
else:
lf.write('{}; no tiene datos {}, {} entre las fechas {} y {}'
.format(cod, cod_u, param_u,
fecha1.strftime('%d/%m/%Y'),
fecha2.strftime('%d/%m/%Y')))
if i == 0:
fo.write(fmt1.format(cod, toponimia, x, y, z))
fo.write('{:0.2f}'.format(umbral))
if cnp.size > 0:
y1 = cnp[-1]
fo.write(fmt2.format(mean, mean-umbral, mean-y1))
else:
fo.write('\tNaN\tNaN\tNaN\t')
# clasificación del comportamiento piezométrico
if cnp.size > 2:
median = np.median(cnp[:-1]) - umbral
cnp_index = par.coef1 * median + par.coef2 * (cnp[-1] - umbral)
fo.write('{:0.2f}\t'.format(cnp_index))
else:
cnp_index = None
fo.write('NaN\t')
if toxy == 1:
pozos.append(cod)
indexes.append(cnp_index)
i += 1
if i == len(cods_u):
if n_series > 0:
rango = abs(maximun - minimun)
fo.write('{:0.2f}\n'.format(rango))
else:
fo.write('NaN\n')
con.close()
fo.close()
boreholes, ines, inds = [], [], []
if pozos_iue:
for pozo, ne, nd in zip(pozos_iue, values_iue, values_iud):
if ne is None or nd is None:
continue
boreholes.append(pozo)
ines.append(ne)
inds.append(nd)
_made_xy_graph(boreholes, ines, inds)
def dif_grapfs(project):
"""
graba gráficos de diferencias (serie -piezométrica- - umbral)
input:
project: tag proyecto seleccionado
"""
from os.path import join
import numpy as np
import pyodbc
import piezo_CTH_parameters as par
import db_con_str
from piezo_CTH_XY import Time_series, XYt_1
lf.write('\n Gráficos de diferencias')
db = project.find('db').text.strip()
tag_pozos = project.findall('point')
tag_umbrales = project.findall('umbral')
cods_u = [tag_u.get('cod').strip() for tag_u in tag_umbrales]
params_u = [tag_u.get('parametro').strip() for tag_u in tag_umbrales]
selects_dp = [tag_u.find('select_data_param').text.strip()
for tag_u in tag_umbrales]
ylegends = [tag_u.get('ylegend').strip()
for tag_u in tag_umbrales]
select_umbral = project.find('select_umbral').text.strip()
fecha1 = str_to_date(par.fecha1)
fecha2 = str_to_date(par.fecha2)
con = pyodbc.connect(db_con_str.con_str(db))
cur = con.cursor()
print('gráficos xy')
for tag in tag_pozos:
cod = tag.get('cod').strip()
print(cod)
for cod_u, param_u, select_dp, ylegend in zip(cods_u, params_u,
selects_dp,
ylegends):
cur.execute(select_umbral, (cod, cod_u, param_u))
row = cur.fetchone()
if row is None:
raise ValueError('{} no tiene umbrales de {} {}'
.format(cod, cod_u, param_u))
toponimia = row.TOPONIMIA
umbral = row.UMBRAL
cur.execute(select_dp, (cod, fecha1, fecha2))
rows = [row for row in cur]
if len(rows) < 2:
lf.write('{}; tiene < 2 datos {}, {} entre las fechas {} y {}'
.format(cod, cod_u, param_u,
fecha1.strftime('%d/%m/%Y'),
fecha2.strftime('%d/%m/%Y')))
continue
fechas = [row.FECHA for row in rows]
values = [row.CNP for row in rows]
values = np.array(values, dtype=FLOAT_PRECISION)
values = values - umbral
s1 = Time_series(fechas, values, ylegend, '.')
stitle = '{} ({})\nDiferencia serie vs umbral {} ({})' \
.format(cod, toponimia, param_u, cod_u)
tmp = cod + '_' + param_u + '_' + cod_u + '.png'
tmp2 = tmp.replace(' ', '_')
file_name = tmp2.replace('/', '_')
dst = join(par.dir_out, file_name)
XYt_1([s1], stitle, ylegend, dst)
con.close()