def put(self, project_db, id):
args = get_con_args()
try:
SetupProjectDatabase.init(project_db)
m = Rout_unit_con.get(Rout_unit_con.id == id)
m.name = args['name']
m.area = args['area']
m.lat = args['lat']
m.lon = args['lon']
m.elev = args['elev']
m.rtu_id = self.get_id_from_name(Rout_unit_rtu, args['rtu_name'])
if args['wst_name'] is not None:
m.wst_id = self.get_id_from_name(Weather_sta_cli,
args['wst_name'])
result = m.save()
if result > 0:
return 200
abort(400,
message='Unable to update Routing Unit {id}.'.format(id=id))
except IntegrityError:
abort(400, message='Routing unit name must be unique.')
except Rout_unit_con.DoesNotExist:
abort(404, message='Rout_unit {id} does not exist'.format(id=id))
except Rout_unit_rtu.DoesNotExist:
abort(400, message=invalid_name_msg.format(name=args['rtu_name']))
except Weather_sta_cli.DoesNotExist:
abort(400, message=invalid_name_msg.format(name=args['wst_name']))
except Exception as ex:
abort(400, message="Unexpected error {ex}".format(ex=ex))
def get(self, project_db):
SetupProjectDatabase.init(project_db)
if Rout_unit_con.select().count() > 0:
m = Rout_unit_con.select(
fn.Max(Rout_unit_con.lat).alias('n'),
fn.Min(Rout_unit_con.lat).alias('s'),
fn.Max(Rout_unit_con.lon).alias('e'),
fn.Min(Rout_unit_con.lon).alias('w')).scalar(as_tuple=True)
return {'n': m[0], 's': m[1], 'e': m[2], 'w': m[3]}
elif Chandeg_con.select().count() > 0: # Quick fix for lte
m = Chandeg_con.select(
fn.Max(Chandeg_con.lat).alias('n'),
fn.Min(Chandeg_con.lat).alias('s'),
fn.Max(Chandeg_con.lon).alias('e'),
fn.Min(Chandeg_con.lon).alias('w')).scalar(as_tuple=True)
return {'n': m[0], 's': m[1], 'e': m[2], 'w': m[3]}
else:
abort(404, message='No routing unit connections in database.')
def write(self):
table = db.Object_cnt
order_by = db.Object_cnt.id
if table.select().count() > 0:
with open(self.file_name, 'w') as file:
self.write_meta_line(file)
header_cols = [col(table.name, direction="left"),
col("ls_area", not_in_db=True, padding_override=utils.DEFAULT_NUM_PAD),
col("tot_area", not_in_db=True, padding_override=utils.DEFAULT_NUM_PAD),
col(table.obj),
col(table.hru),
col(table.lhru),
col(table.rtu),
col(table.mfl),
col(table.aqu),
col(table.cha),
col(table.res),
col(table.rec),
col(table.exco),
col(table.dlr),
col(table.can),
col(table.pmp),
col(table.out),
col(table.lcha),
col(table.aqu2d),
col(table.hrd),
col(table.wro)]
self.write_headers(file, header_cols)
file.write("\n")
for row in table.select().order_by(order_by):
ls_area = Ls_unit_def.select(fn.Sum(Ls_unit_def.area)).scalar()
tot_area = Rout_unit_con.select(fn.Sum(Rout_unit_con.area)).scalar()
file.write(utils.string_pad(row.name, direction="left"))
file.write(utils.num_pad(ls_area))
file.write(utils.num_pad(tot_area))
hru = self.get_value_or_count(row.hru, Hru_con)
lhru = self.get_value_or_count(row.lhru, Hru_lte_con)
rtu = self.get_value_or_count(row.rtu, Rout_unit_con)
mfl = self.get_value_or_count(row.mfl, Modflow_con)
aqu = self.get_value_or_count(row.aqu, Aquifer_con)
cha = self.get_value_or_count(row.cha, Channel_con)
res = self.get_value_or_count(row.res, Reservoir_con)
#rec = self.get_value_or_count(row.rec, Recall_con)
#exco = self.get_value_or_count(row.exco, Exco_con)
#exco = Recall_con.select().join(Recall_rec).join(Recall_dat).where((Recall_rec.rec_typ == 4) & (Recall_dat.flo != 0)).count()
#rec = Recall_con.select().join(Recall_rec).join(Recall_dat).where(Recall_rec.rec_typ != 4).count()
rec = Recall_rec.select().where(Recall_rec.rec_typ != 4).count()
exco = Recall_dat.select().join(Recall_rec).where((Recall_rec.rec_typ == 4) & (Recall_dat.flo != 0)).count()
dlr = self.get_value_or_count(row.dlr, Delratio_con)
out = self.get_value_or_count(row.out, Outlet_con)
lcha = self.get_value_or_count(row.lcha, Chandeg_con)
aqu2d = self.get_value_or_count(row.aqu2d, Aquifer2d_con)
obj_tot = hru + lhru + rtu + mfl + aqu + cha + res + rec + exco + dlr + out + lcha + aqu2d
obj_tot += row.can + row.pmp + row.hrd + row.wro
file.write(utils.int_pad(obj_tot))
file.write(utils.int_pad(hru))
file.write(utils.int_pad(lhru))
file.write(utils.int_pad(rtu))
file.write(utils.int_pad(mfl))
file.write(utils.int_pad(aqu))
file.write(utils.int_pad(cha))
file.write(utils.int_pad(res))
file.write(utils.int_pad(rec))
file.write(utils.int_pad(exco))
file.write(utils.int_pad(dlr))
file.write(utils.int_pad(row.can))
file.write(utils.int_pad(row.pmp))
file.write(utils.int_pad(out))
file.write(utils.int_pad(lcha))
file.write(utils.int_pad(aqu2d))
file.write(utils.int_pad(row.hrd))
file.write(utils.int_pad(row.wro))
file.write("\n")
def add_wgn_stations_db(self, start_prog, total_prog):
if self.__abort: return
conn = sqlite3.connect(self.wgn_database)
conn.row_factory = sqlite3.Row
monthly_table = "{}_mon".format(self.wgn_table)
if not db_lib.exists_table(conn, self.wgn_table):
raise ValueError("Table {table} does not exist in {file}.".format(
table=self.wgn_table, file=self.wgn_database))
if not db_lib.exists_table(conn, monthly_table):
raise ValueError("Table {table} does not exist in {file}.".format(
table=monthly_table, file=self.wgn_database))
if Rout_unit_con.select().count() > 0:
coords = Rout_unit_con.select(
fn.Min(Rout_unit_con.lat).alias("min_lat"),
fn.Max(Rout_unit_con.lat).alias("max_lat"),
fn.Min(Rout_unit_con.lon).alias("min_lon"),
fn.Max(Rout_unit_con.lon).alias("max_lon")).get()
query = "select * from {table_name} where lat between ? and ? and lon between ? and ? order by name".format(
table_name=self.wgn_table)
tol = 0.5
cursor = conn.cursor().execute(
query, (coords.min_lat - tol, coords.max_lat + tol,
coords.min_lon - tol, coords.max_lon + tol))
elif Chandeg_con.select().count() > 0:
coords = Chandeg_con.select(
fn.Min(Chandeg_con.lat).alias("min_lat"),
fn.Max(Chandeg_con.lat).alias("max_lat"),
fn.Min(Chandeg_con.lon).alias("min_lon"),
fn.Max(Chandeg_con.lon).alias("max_lon")).get()
query = "select * from {table_name} where lat between ? and ? and lon between ? and ? order by name".format(
table_name=self.wgn_table)
tol = 0.5
cursor = conn.cursor().execute(
query, (coords.min_lat - tol, coords.max_lat + tol,
coords.min_lon - tol, coords.max_lon + tol))
else:
query = "select * from {table_name} order by name".format(
table_name=self.wgn_table)
cursor = conn.cursor().execute(query)
wgns = []
ids = []
data = cursor.fetchall()
records = len(data)
#print(records)
i = 1
print('\t - Preparing weather generator')
for row in data:
if self.__abort: return
try:
existing = Weather_wgn_cli.get(
Weather_wgn_cli.name == row['name'])
except Weather_wgn_cli.DoesNotExist:
prog = round(i * (total_prog / 2) / records) + start_prog
# self.emit_progress(prog, "Preparing weather generator {name}...".format(name=row['name']))
i += 1
ids.append(row['id'])
wgn = {
"id": row['id'],
"name": row['name'],
"lat": row['lat'],
"lon": row['lon'],
"elev": row['elev'],
"rain_yrs": row['rain_yrs']
}
wgns.append(wgn)
prog = start_prog if records < 1 else round(i * (total_prog / 2) /
records) + start_prog
# self.emit_progress(prog, "Inserting {total} weather generators...".format(total=len(ids)))
db_lib.bulk_insert(project_base.db, Weather_wgn_cli, wgns)
# Chunk the id array so we don't hit the SQLite parameter limit!
max_length = 999
id_chunks = [
ids[i:i + max_length] for i in range(0, len(ids), max_length)
]
i = 1
start_prog = start_prog + (total_prog / 2)
mon_count_query = "select count(*) from {table_name}".format(
table_name=monthly_table)
total_mon_rows = conn.cursor().execute(mon_count_query).fetchone()[0]
current_total = 0
for chunk in id_chunks:
monthly_values = []
mon_query = "select * from {table_name} where wgn_id in ({ids})".format(
table_name=monthly_table, ids=",".join('?' * len(chunk)))
mon_cursor = conn.cursor().execute(mon_query, chunk)
mon_data = mon_cursor.fetchall()
mon_records = len(mon_data)
i = 1
for row in mon_data:
if self.__abort: return
if i == 1 or (i % 12 == 0):
prog = round(i *
(total_prog / 2) / mon_records) + start_prog
# self.emit_progress(prog, "Preparing monthly values {i}/{total}...".format(i=i, total=mon_records))
i += 1
mon = {
"weather_wgn_cli": row['wgn_id'],
"month": row['month'],
"tmp_max_ave": row['tmp_max_ave'],
"tmp_min_ave": row['tmp_min_ave'],
"tmp_max_sd": row['tmp_max_sd'],
"tmp_min_sd": row['tmp_min_sd'],
"pcp_ave": row['pcp_ave'],
"pcp_sd": row['pcp_sd'],
"pcp_skew": row['pcp_skew'],
"wet_dry": row['wet_dry'],
"wet_wet": row['wet_wet'],
"pcp_days": row['pcp_days'],
"pcp_hhr": row['pcp_hhr'],
"slr_ave": row['slr_ave'],
"dew_ave": row['dew_ave'],
"wnd_ave": row['wnd_ave']
}
monthly_values.append(mon)
prog = round(i * (total_prog / 2) / mon_records) + start_prog
current_total = current_total + mon_records
# self.emit_progress(prog, "Inserting monthly values {rec}/{total}...".format(rec=current_total, total=total_mon_rows))
db_lib.bulk_insert(project_base.db, Weather_wgn_cli_mon,
monthly_values)
def post(self, project_db):
args = get_con_args()
SetupProjectDatabase.init(project_db)
try:
e = Rout_unit_con.get(Rout_unit_con.name == args['name'])
abort(
400,
message=
'Routing unit name must be unique. Routing unit with this name already exists.'
)
except Rout_unit_con.DoesNotExist:
try:
m = Rout_unit_con()
m.name = args['name']
m.area = args['area']
m.lat = args['lat']
m.lon = args['lon']
m.elev = args['elev']
m.ovfl = 0
m.rule = 0
m.rtu_id = self.get_id_from_name(Rout_unit_rtu,
args['rtu_name'])
if args['wst_name'] is not None:
m.wst_id = self.get_id_from_name(Weather_sta_cli,
args['wst_name'])
result = m.save()
if result > 0:
return model_to_dict(m), 201
abort(400, message='Unable to create Routingunit.')
except IntegrityError:
abort(400, message='Routing unit name must be unique.')
except Rout_unit_rtu.DoesNotExist:
abort(400,
message=invalid_name_msg.format(name=args['rtu_name']))
except Weather_sta_cli.DoesNotExist:
abort(400,
message=invalid_name_msg.format(name=args['wst_name']))
except Exception as ex:
abort(400, message="Unexpected error {ex}".format(ex=ex))