def get_nodes(self):
""" Fill fields node_1 and node_2 """
# Get start and end points
polyline = self.feature.geometry().asPolyline()
start_point = polyline[0]
end_point = polyline[len(polyline) - 1]
# Get parameter 'node_proximity' from config table
node_proximity = 1
sql = "SELECT node_proximity FROM " + self.schema_name + ".config"
row = self.controller.get_row(sql)
if row:
node_proximity = row[0]
# Get closest node from selected points
node_1 = self.get_node_from_point(start_point, node_proximity)
node_2 = self.get_node_from_point(end_point, node_proximity)
widget_name = ""
layer_source = self.controller.get_layer_source(
self.iface.activeLayer())
uri_table = layer_source['table']
if uri_table == 'v_edit_man_conduit':
widget_name = 'conduit'
elif uri_table == 'v_edit_man_varc':
widget_name = 'varc'
elif uri_table == 'v_edit_man_siphon':
widget_name = 'siphon'
elif uri_table == 'v_edit_man_waccel':
widget_name = 'waccel'
# Fill fields node_1 and node_2
utils_giswater.setText(widget_name + "_node_1", node_1)
utils_giswater.setText(widget_name + "_node_2", node_2)
def select_config(self, tablename):
""" Get data from table 'config' and fill widgets according to the name of the columns """
sql = "SELECT * FROM " + self.schema_name + "." + tablename
row = self.dao.get_row(sql)
if not row:
self.controller.show_warning("Any data found in table " + tablename)
return None
# Iterate over all columns and populate its corresponding widget
columns = []
for i in range(0, len(row)):
column_name = self.dao.get_column_name(i)
widget_type = utils_giswater.getWidgetType(column_name)
if widget_type is QCheckBox:
utils_giswater.setChecked(column_name, row[column_name])
elif widget_type is QDateEdit:
utils_giswater.setCalendarDate(column_name, datetime.strptime(row[column_name], '%Y-%m-%d'))
elif widget_type is QTimeEdit:
timeparts = str(row[column_name]).split(':')
if len(timeparts) < 3:
timeparts.append("0")
days = int(timeparts[0]) / 24
hours = int(timeparts[0]) % 24
minuts = int(timeparts[1])
seconds = int(timeparts[2])
time = QTime(hours, minuts, seconds)
utils_giswater.setTimeEdit(column_name, time)
utils_giswater.setText(column_name + "_day", days)
else:
utils_giswater.setWidgetText(column_name, row[column_name])
columns.append(column_name)
return columns
def populate_budget(self, psector_id):
sql = ("SELECT DISTINCT(column_name) FROM information_schema.columns"
" WHERE table_name = 'v_" + self.plan_om + "_current_psector'")
rows = self.controller.get_rows(sql)
columns = []
for i in range(0, len(rows)):
column_name = rows[i]
columns.append(str(column_name[0]))
sql = ("SELECT total_arc, total_node, total_other, pem, pec, pec_vat, gexpenses, vat, other, pca"
" FROM " + self.schema_name + ".v_" + self.plan_om + "_current_psector"
" WHERE psector_id = '" + str(psector_id) + "'")
row = self.controller.get_row(sql)
if row:
for column_name in columns:
if column_name in row:
if row[column_name] is not None:
utils_giswater.setText(column_name, row[column_name])
else:
utils_giswater.setText(column_name, 0)
self.calc_pec_pem()
self.calc_pecvat_pec()
self.calc_pca_pecvat()
def loadData(self):
# Tab 'Add. info'
if self.epa_type == 'TANK':
sql = "SELECT vmax, area FROM " + self.schema_name + ".man_tank WHERE node_id = " + self.node_id
row = self.dao.get_row(sql)
if row:
utils_giswater.setText("man_tank_vmax", str(row[0]))
utils_giswater.setText("man_tank_area", str(row[1]))
def loadData(self):
# Tab 'Add. info'
if self.epa_type == 'TANK':
sql = "SELECT vmax, area FROM "+self.schema_name+".man_tank WHERE node_id = "+self.node_id
row = self.dao.get_row(sql)
if row:
utils_giswater.setText("man_tank_vmax", str(row[0]))
utils_giswater.setText("man_tank_area", str(row[1]))
def update_labels(self):
""" Show text in labels from SELECT """
sql = "SELECT infiltration, text FROM " + self.schema_name + ".cat_hydrology"
sql += " WHERE name = '" + str(
self.dlg_hydrology_selector.hydrology.currentText()) + "'"
row = self.dao.get_row(sql)
if row is not None:
utils_giswater.setText("infiltration", row[0])
utils_giswater.setText("descript", row[1])
def show_description(self):
""" Show description of product plan/om _psector as label"""
selected_list = self.dlg.all_rows.selectionModel().selectedRows()
des = ""
for i in range(0, len(selected_list)):
row = selected_list[i].row()
des = self.dlg.all_rows.model().record(row).value('descript')
utils_giswater.setText(self.lbl_descript, des)
def update_total(self, qtable):
""" Show description of product plan/om _psector as label """
selected_list = qtable.model()
if selected_list is None:
return
total = 0
psector_id = utils_giswater.getWidgetText('psector_id')
for x in range(0, selected_list.rowCount()):
if int(qtable.model().record(x).value('psector_id')) == int(psector_id):
if str(qtable.model().record(x).value('total_budget')) != 'NULL':
total += float(qtable.model().record(x).value('total_budget'))
utils_giswater.setText('lbl_total', str(total))
def go2epa_options_get_data(self, tablename, dialog):
""" Get data from selected table """
sql = "SELECT * FROM " + self.schema_name + "." + tablename
row = self.controller.get_row(sql)
if not row:
message = "Any data found in table"
self.controller.show_warning(message, parameter=tablename)
return None
# Iterate over all columns and populate its corresponding widget
columns = []
for i in range(0, len(row)):
column_name = self.dao.get_column_name(i)
widget = dialog.findChild(QWidget, column_name)
widget_type = utils_giswater.getWidgetType(dialog, widget)
if row[column_name] is not None:
if widget_type is QCheckBox:
utils_giswater.setChecked(dialog, widget, row[column_name])
elif widget_type is QComboBox:
utils_giswater.set_combo_itemData(widget, row[column_name], 0)
elif widget_type is QDateEdit:
dateaux = row[column_name].replace('/', '-')
date = QDate.fromString(dateaux, 'dd-MM-yyyy')
utils_giswater.setCalendarDate(dialog, widget, date)
elif widget_type is QTimeEdit:
timeparts = str(row[column_name]).split(':')
if len(timeparts) < 3:
timeparts.append("0")
days = int(timeparts[0]) / 24
hours = int(timeparts[0]) % 24
minuts = int(timeparts[1])
seconds = int(timeparts[2])
time = QTime(hours, minuts, seconds)
utils_giswater.setTimeEdit(dialog, widget, time)
utils_giswater.setText(dialog, column_name + "_day", days)
else:
utils_giswater.setWidgetText(dialog, widget, str(row[column_name]))
columns.append(column_name)
return columns
def click_button_snapping(self, point, btn): # @UnusedVariable
if not self.layer_dimensions:
return
layer = self.layer_dimensions
self.iface.setActiveLayer(layer)
layer.startEditing()
snapper = QgsMapCanvasSnapper(self.canvas)
map_point = self.canvas.getCoordinateTransform().transform(point)
x = map_point.x()
y = map_point.y()
event_point = QPoint(x, y)
# Snapping
(retval, result) = snapper.snapToBackgroundLayers(
event_point) # @UnusedVariable
# That's the snapped point
if result:
# Check feature
for snapped_point in result:
if snapped_point.layer == self.layer_node:
feat_type = 'node'
elif snapped_point.layer == self.layer_connec:
feat_type = 'connec'
else:
continue
# Get the point
point = QgsPoint(snapped_point.snappedVertex)
snapp_feature = next(
snapped_point.layer.getFeatures(
QgsFeatureRequest().setFilterFid(
snapped_point.snappedAtGeometry)))
element_id = snapp_feature.attribute(feat_type + '_id')
# Leave selection
snapped_point.layer.select([snapped_point.snappedAtGeometry])
# Get depth of the feature
if self.project_type == 'ws':
fieldname = "depth"
elif self.project_type == 'ud' and feat_type == 'node':
fieldname = "ymax"
elif self.project_type == 'ud' and feat_type == 'connec':
fieldname = "connec_depth"
sql = ("SELECT " + fieldname + ""
" FROM " + self.schema_name + "." + feat_type + ""
" WHERE " + feat_type + "_id = '" + element_id + "'")
row = self.controller.get_row(sql)
if not row:
return
utils_giswater.setText(self.dialog, "depth", row[0])
utils_giswater.setText(self.dialog, "feature_id", element_id)
utils_giswater.setText(self.dialog, "feature_type",
feat_type.upper())
def get_nodes(self):
""" Fill fields node_1 and node_2 """
# Get start and end points
polyline = self.feature.geometry().asPolyline()
start_point = polyline[0]
end_point = polyline[len(polyline) - 1]
# Get parameter 'node_proximity' from config table
node_proximity = 1
sql = "SELECT node_proximity FROM " + self.schema_name + ".config"
row = self.controller.get_row(sql)
if row:
node_proximity = row[0]
# Get closest node from selected points
node_1 = self.get_node_from_point(start_point, node_proximity)
node_2 = self.get_node_from_point(end_point, node_proximity)
# Fill fields node_1 and node_2
utils_giswater.setText(self.dialog, "node_1", node_1)
utils_giswater.setText(self.dialog, "node_2", node_2)
def fill_workids(self):
""" Auto fill descriptions and workid's """
workcat_id = utils_giswater.getWidgetText(
self.dlg_work_end, self.dlg_work_end.workcat_id_end)
sql = ("SELECT descript, builtdate"
" FROM " + self.controller.schema_name + ".cat_work"
" WHERE id = '" + workcat_id + "'")
row = self.controller.get_row(sql)
if row:
utils_giswater.setText(self.dlg_work_end,
self.dlg_work_end.descript, row['descript'])
utils_giswater.setCalendarDate(self.dlg_work_end,
self.dlg_work_end.builtdate,
row['builtdate'], False)
else:
utils_giswater.setText(self.dlg_work_end,
self.dlg_work_end.descript, '')
utils_giswater.setCalendarDate(self.dlg_work_end,
self.dlg_work_end.builtdate, None,
False)
def insert_or_update_new_psector(self, update, tablename, close_dlg=False):
psector_name = utils_giswater.getWidgetText(self.dlg.name, return_string_null=False)
if psector_name == "":
message = "Mandatory field is missing. Please, set a value"
self.controller.show_warning(message, parameter='Name')
return
rotation = utils_giswater.getWidgetText(self.dlg.rotation, return_string_null=False)
if rotation == "":
utils_giswater.setWidgetText(self.dlg.rotation, 0)
name_exist = self.check_name(psector_name)
if name_exist and not update:
message = "The name is current in use"
self.controller.show_warning(message)
return
else:
self.enable_tabs(True)
self.enable_buttons(True)
sql = ("SELECT column_name FROM information_schema.columns"
" WHERE table_name = '" + "v_edit_" + self.plan_om + "_psector'"
" AND table_schema = '" + self.schema_name.replace('"', '') + "'"
" ORDER BY ordinal_position")
rows = self.controller.get_rows(sql)
columns = []
for i in range(0, len(rows)):
column_name = rows[i]
columns.append(str(column_name[0]))
if update:
if columns:
sql = "UPDATE " + self.schema_name + "." + tablename + " SET "
for column_name in columns:
if column_name != 'psector_id':
widget_type = utils_giswater.getWidgetType(column_name)
if widget_type is QCheckBox:
value = utils_giswater.isChecked(column_name)
elif widget_type is QDateEdit:
date = self.dlg.findChild(QDateEdit, str(column_name))
value = date.dateTime().toString('yyyy-MM-dd HH:mm:ss')
elif (widget_type is QComboBox) and (column_name == 'expl_id' or column_name == 'sector_id'
or column_name == 'result_id' or column_name == 'psector_type'):
combo = utils_giswater.getWidget(column_name)
elem = combo.itemData(combo.currentIndex())
value = str(elem[0])
else:
value = utils_giswater.getWidgetText(column_name)
if value is None or value == 'null':
sql += column_name + " = null, "
else:
if type(value) is not bool:
value = value.replace(",", ".")
sql += column_name + " = '" + str(value) + "', "
sql = sql[:len(sql) - 2]
sql += " WHERE psector_id = '" + utils_giswater.getWidgetText(self.psector_id) + "'"
else:
values = "VALUES("
if columns:
sql = "INSERT INTO " + self.schema_name + "." + tablename + " ("
for column_name in columns:
if column_name != 'psector_id':
widget_type = utils_giswater.getWidgetType(column_name)
if widget_type is not None:
if widget_type is QCheckBox:
value = str(utils_giswater.isChecked(column_name)).upper()
elif widget_type is QDateEdit:
date = self.dlg.findChild(QDateEdit, str(column_name))
values += date.dateTime().toString('yyyy-MM-dd HH:mm:ss') + ", "
elif (widget_type is QComboBox) and (column_name == 'expl_id' or column_name == 'sector_id'
or column_name == 'result_id' or column_name == 'psector_type'):
combo = utils_giswater.getWidget(column_name)
elem = combo.itemData(combo.currentIndex())
value = str(elem[0])
else:
value = utils_giswater.getWidgetText(column_name)
if value is None or value == 'null':
sql += column_name + ", "
values += "null, "
else:
values += "'" + value + "', "
sql += column_name + ", "
sql = sql[:len(sql) - 2] + ") "
values = values[:len(values) - 2] + ")"
sql += values
if not update:
sql += " RETURNING psector_id"
new_psector_id = self.controller.execute_returning(sql, search_audit=False, log_sql=True)
utils_giswater.setText(self.dlg.psector_id, str(new_psector_id[0]))
if new_psector_id and self.plan_om == 'plan':
sql = ("SELECT parameter FROM " + self.schema_name + ".config_param_user "
" WHERE parameter = 'psector_vdefault' AND cur_user = current_user")
row = self.controller.get_row(sql)
if row:
sql = ("UPDATE " + self.schema_name + ".config_param_user "
" SET value = '" + str(new_psector_id[0]) + "' "
" WHERE parameter = 'psector_vdefault'")
else:
sql = ("INSERT INTO " + self.schema_name + ".config_param_user (parameter, value, cur_user) "
" VALUES ('psector_vdefault', '" + str(new_psector_id[0]) + "', current_user)")
self.controller.execute_sql(sql, log_sql=True)
else:
self.controller.execute_sql(sql, log_sql=True)
self.dlg.tabWidget.setTabEnabled(1, True)
self.insert_psector_selector(self.plan_om+'_psector_selector', 'psector_id', utils_giswater.getWidgetText(self.dlg.psector_id))
if close_dlg:
self.reload_states_selector()
self.close_dialog()
def click_button_snapping(self, point, btn): #@UnusedVariable
# Disconnect mouse movement
self.vertex_marker.hide()
self.canvas.disconnect(self.canvas,
SIGNAL("xyCoordinates(const QgsPoint&)"),
self.mouse_move)
if not self.layer_dimensions:
return
layer = self.layer_dimensions
self.iface.setActiveLayer(layer)
layer.startEditing()
# TODO:
node_group = [
"Junction", "Valve", "Reduction", "Tank", "Meter", "Manhole",
"Source", "Hydrant", "Wtp", "Netsamplepoint", "Netelement",
"Flexunion", "Expantank", "Netwjoin", "Register", "Pump",
"Waterwell", "Filter"
]
snapper = QgsMapCanvasSnapper(self.canvas)
map_point = self.canvas.getCoordinateTransform().transform(point)
x = map_point.x()
y = map_point.y()
event_point = QPoint(x, y)
# Snapping
(retval, result) = snapper.snapToBackgroundLayers(
event_point) # @UnusedVariable
# That's the snapped point
if result <> []:
# Check feature
for snap_point in result:
element_type = snap_point.layer.name()
if element_type in node_group:
feat_type = 'node'
else:
continue
# Get the point
point = QgsPoint(snap_point.snappedVertex)
snapp_feature = next(
snap_point.layer.getFeatures(
QgsFeatureRequest().setFilterFid(
snap_point.snappedAtGeometry)))
element_id = snapp_feature.attribute(feat_type + '_id')
# Leave selection
snap_point.layer.select([snap_point.snappedAtGeometry])
# Get depth of the feature
if self.project_type == 'ws':
fieldname = "depth"
elif self.project_type == 'ud' and feat_type == 'node':
fieldname = "ymax"
elif self.project_type == 'ud' and feat_type == 'connec':
fieldname = "connec_depth"
sql = "SELECT " + fieldname
sql += " FROM " + self.schema_name + "." + feat_type
sql += " WHERE " + feat_type + "_id = '" + element_id + "'"
row = self.controller.get_row(sql)
if not row:
return
utils_giswater.setText("depth", row[0])
utils_giswater.setText("feature_id", element_id)
utils_giswater.setText("feature_type", element_type)
def master_new_psector(self, psector_id=None, enable_tabs=False):
""" Button 45: New psector """
# Create the dialog and signals
self.dlg = Plan_psector()
utils_giswater.setDialog(self.dlg)
self.list_elemets = {}
update = False # if false: insert; if true: update
tab_arc_node_other = self.dlg.findChild(QTabWidget, "tabWidget_2")
tab_arc_node_other.setTabEnabled(0, enable_tabs)
tab_arc_node_other.setTabEnabled(1, enable_tabs)
tab_arc_node_other.setTabEnabled(2, enable_tabs)
# tab General elements
self.psector_id = self.dlg.findChild(QLineEdit, "psector_id")
self.priority = self.dlg.findChild(QComboBox, "priority")
sql = "SELECT DISTINCT(id) FROM "+self.schema_name+".value_priority ORDER BY id"
rows = self.dao.get_rows(sql)
utils_giswater.fillComboBox("priority", rows, False)
scale = self.dlg.findChild(QLineEdit, "scale")
scale.setValidator(QDoubleValidator())
rotation = self.dlg.findChild(QLineEdit, "rotation")
rotation.setValidator(QDoubleValidator())
# tab Bugdet
sum_expenses = self.dlg.findChild(QLineEdit, "sum_expenses")
other = self.dlg.findChild(QLineEdit, "other")
other.setValidator(QDoubleValidator())
other_cost = self.dlg.findChild(QLineEdit, "other_cost")
sum_oexpenses = self.dlg.findChild(QLineEdit, "sum_oexpenses")
gexpenses = self.dlg.findChild(QLineEdit, "gexpenses")
gexpenses.setValidator(QDoubleValidator())
gexpenses_cost = self.dlg.findChild(QLineEdit, "gexpenses_cost")
self.dlg.gexpenses_cost.textChanged.connect(partial(self.cal_percent, sum_oexpenses, gexpenses, gexpenses_cost))
sum_gexpenses = self.dlg.findChild(QLineEdit, "sum_gexpenses")
vat = self.dlg.findChild(QLineEdit, "vat")
vat.setValidator(QDoubleValidator())
vat_cost = self.dlg.findChild(QLineEdit, "vat_cost")
self.dlg.gexpenses_cost.textChanged.connect(partial(self.cal_percent, sum_gexpenses, vat, vat_cost))
sum_vexpenses = self.dlg.findChild(QLineEdit, "sum_vexpenses")
self.dlg.other.textChanged.connect(partial(self.cal_percent, sum_expenses, other, other_cost))
self.dlg.other_cost.textChanged.connect(partial(self.sum_total, sum_expenses, other_cost, sum_oexpenses))
self.dlg.gexpenses.textChanged.connect(partial(self.cal_percent, sum_oexpenses, gexpenses, gexpenses_cost))
self.dlg.gexpenses_cost.textChanged.connect(partial(self.sum_total, sum_oexpenses, gexpenses_cost, sum_gexpenses))
self.dlg.vat.textChanged.connect(partial(self.cal_percent, sum_gexpenses, vat, vat_cost))
self.dlg.vat_cost.textChanged.connect(partial(self.sum_total, sum_gexpenses, vat_cost, sum_vexpenses))
# Tables
# tab Elements
tbl_arc_plan = self.dlg.findChild(QTableView, "tbl_arc_plan")
tbl_arc_plan.setSelectionBehavior(QAbstractItemView.SelectRows)
self.fill_table(tbl_arc_plan, self.schema_name + ".plan_arc_x_psector")
tbl_node_plan = self.dlg.findChild(QTableView, "tbl_node_plan")
tbl_node_plan.setSelectionBehavior(QAbstractItemView.SelectRows)
self.fill_table(tbl_node_plan, self.schema_name + ".plan_node_x_psector")
tbl_other_plan = self.dlg.findChild(QTableView, "tbl_other_plan")
tbl_other_plan.setSelectionBehavior(QAbstractItemView.SelectRows)
self.fill_table(tbl_other_plan, self.schema_name + ".plan_other_x_psector")
# tab Elements
self.dlg.btn_add_arc_plan.pressed.connect(partial(self.snapping, "v_edit_arc", "plan_arc_x_psector", tbl_arc_plan, "arc"))
self.dlg.btn_del_arc_plan.pressed.connect(partial(self.multi_rows_delete, tbl_arc_plan, "plan_arc_x_psector", "id"))
self.dlg.btn_add_node_plan.pressed.connect(partial(self.snapping, "v_edit_node", "plan_node_x_psector", tbl_node_plan, "node"))
self.dlg.btn_del_node_plan.pressed.connect(partial(self.multi_rows_delete, tbl_node_plan, "plan_node_x_psector", "id"))
self.dlg.btn_del_other_plan.pressed.connect(partial(self.multi_rows_delete, tbl_other_plan, "plan_other_x_psector", "id"))
##
# if a row is selected from mg_psector_mangement(button 46)
# Si psector_id contiene "1" o "0" python lo toma como boolean, si es True, quiere decir que no contiene valor
# y por lo tanto es uno nuevo. Convertimos ese valor en 0 ya que ningun id va a ser 0. de esta manera si psector_id
# tiene un valor distinto de 0, es que el sector ya existe y queremos hacer un update.
##
if isinstance(psector_id, bool):
psector_id = 0
if psector_id != 0:
sql = "SELECT psector_id, name, priority, descript, text1, text2, observ, atlas_id, scale, rotation "
sql += " FROM " + self.schema_name + ".plan_psector"
sql += " WHERE psector_id = " + str(psector_id)
row = self.dao.get_row(sql)
if row is None:
return
self.psector_id.setText(str(row["psector_id"]))
utils_giswater.setRow(row)
utils_giswater.fillWidget("name")
utils_giswater.fillWidget("descript")
index = self.priority.findText(row["priority"], Qt.MatchFixedString)
if index >= 0:
self.priority.setCurrentIndex(index)
utils_giswater.fillWidget("text1")
utils_giswater.fillWidget("text2")
utils_giswater.fillWidget("observ")
utils_giswater.fillWidget("atlas_id")
utils_giswater.fillWidget("scale")
utils_giswater.fillWidget("rotation")
# Fill tables tbl_arc_plan, tbl_node_plan, tbl_v_plan_other_x_psector with selected filter
expr = " psector_id = " + str(psector_id)
tbl_arc_plan.model().setFilter(expr)
tbl_arc_plan.model().select()
expr = " psector_id = " + str(psector_id)
tbl_node_plan.model().setFilter(expr)
tbl_node_plan.model().select()
# Total other Prices
total_other_price = 0
sql = "SELECT SUM(budget) FROM " + self.schema_name + ".v_plan_other_x_psector"
sql += " WHERE psector_id = '" + str(psector_id) + "'"
row = self.dao.get_row(sql)
if row is not None:
if row[0]:
total_other_price = row[0]
utils_giswater.setText("sum_v_plan_other_x_psector", total_other_price)
# Total arcs
total_arcs = 0
sql = "SELECT SUM(budget) FROM " + self.schema_name + ".v_plan_arc_x_psector"
sql += " WHERE psector_id = '" + str(psector_id) + "'"
row = self.dao.get_row(sql)
if row is not None:
if row[0]:
total_arcs = row[0]
utils_giswater.setText("sum_v_plan_x_arc_psector", total_arcs)
# Total nodes
total_nodes = 0
sql = "SELECT SUM(budget) FROM " + self.schema_name + ".v_plan_node_x_psector"
sql += " WHERE psector_id = '" + str(psector_id) + "'"
row = self.dao.get_row(sql)
if row is not None:
if row[0]:
total_nodes = row[0]
utils_giswater.setText("sum_v_plan_x_node_psector", total_nodes)
sum_expenses = total_other_price + total_arcs + total_nodes
utils_giswater.setText("sum_expenses", sum_expenses)
update = True
# Buttons
self.dlg.btn_accept.pressed.connect(partial(self.insert_or_update_new_psector, update, 'plan_psector'))
self.dlg.btn_cancel.pressed.connect(self.close_dialog)
self.dlg.setWindowFlags(Qt.WindowStaysOnTopHint)
self.dlg.open()
