def set_table_columns(self, widget, table_name):
""" Configuration of tables. Set visibility and width of columns """
widget = utils_giswater.getWidget(widget)
if not widget:
return
# Set width and alias of visible columns
columns_to_delete = []
sql = ("SELECT column_index, width, alias, status"
" FROM " + self.schema_name + ".config_client_forms"
" WHERE table_id = '" + table_name + "'"
" ORDER BY column_index")
rows = self.controller.get_rows(sql, log_info=False)
if not rows:
return
for row in rows:
if not row['status']:
columns_to_delete.append(row['column_index'] - 1)
else:
width = row['width']
if width is None:
width = 100
widget.setColumnWidth(row['column_index'] - 1, width)
widget.model().setHeaderData(row['column_index'] - 1,
Qt.Horizontal, row['alias'])
# Set order
# widget.model().setSort(0, Qt.AscendingOrder)
widget.model().select()
# Delete columns
for column in columns_to_delete:
widget.hideColumn(column)
def set_completer_object(self, dialog, table_object):
""" Set autocomplete of widget @table_object + "_id"
getting id's from selected @table_object
"""
widget = utils_giswater.getWidget(dialog, table_object + "_id")
if not widget:
return
# Set SQL
field_object_id = "id"
if table_object == "element":
field_object_id = table_object + "_id"
sql = ("SELECT DISTINCT(" + field_object_id + ")"
" FROM " + self.schema_name + "." + table_object)
row = self.controller.get_rows(sql, commit=self.autocommit)
for i in range(0, len(row)):
aux = row[i]
row[i] = str(aux[0])
# Set completer and model: add autocomplete in the widget
self.completer = QCompleter()
self.completer.setCaseSensitivity(Qt.CaseInsensitive)
widget.setCompleter(self.completer)
model = QStringListModel()
model.setStringList(row)
self.completer.setModel(model)
def tab_feature_changed(self, dialog, table_object, feature_id=None):
""" Set geom_type and layer depending selected tab
@table_object = ['doc' | 'element' | 'cat_work']
"""
self.get_values_from_form(dialog)
if dialog.tab_feature.currentIndex() == 3:
dialog.btn_snapping.setEnabled(False)
else:
dialog.btn_snapping.setEnabled(True)
tab_position = dialog.tab_feature.currentIndex()
if tab_position == 0:
self.geom_type = "arc"
elif tab_position == 1:
self.geom_type = "node"
elif tab_position == 2:
self.geom_type = "connec"
elif tab_position == 3:
self.geom_type = "element"
elif tab_position == 4:
self.geom_type = "gully"
self.hide_generic_layers()
widget_name = "tbl_" + table_object + "_x_" + str(self.geom_type)
viewname = "v_edit_" + str(self.geom_type)
self.widget = utils_giswater.getWidget(dialog, widget_name)
# Adding auto-completion to a QLineEdit
self.set_completer_feature_id(dialog.feature_id, self.geom_type, viewname)
self.iface.actionPan().trigger()
def master_estimate_result_new_calculate(self, dialog):
""" Execute function 'gw_fct_plan_estimate_result' """
# Get values from form
result_name = utils_giswater.getWidgetText(dialog, "result_name")
combo = utils_giswater.getWidget(dialog, "cmb_result_type")
elem = combo.itemData(combo.currentIndex())
result_type = str(elem[0])
coefficient = utils_giswater.getWidgetText(dialog,
"prices_coefficient")
observ = utils_giswater.getWidgetText(dialog, "observ")
if result_name == 'null':
message = "Please, introduce a result name"
self.controller.show_warning(message)
return
if coefficient == 'null':
message = "Please, introduce a coefficient value"
self.controller.show_warning(message)
return
# Check data executing function 'gw_fct_epa_audit_check_data'
sql = "SELECT " + self.schema_name + ".gw_fct_plan_audit_check_data(" + str(
result_type) + ");"
row = self.controller.get_row(sql, log_sql=True)
if not row:
return
if row[0] > 0:
msg = ("It is not possible to execute the economic result."
"There are errors on your project. Review it!")
if result_type == 1:
fprocesscat_id = 15
else:
fprocesscat_id = 16
sql_details = ("SELECT table_id, column_id, error_message"
" FROM audit_check_data"
" WHERE fprocesscat_id = " + str(fprocesscat_id) +
" AND enabled is false")
inf_text = "For more details execute query:\n" + sql_details
title = "Execute epa model"
self.controller.show_info_box(msg,
title,
inf_text,
parameter=row[0])
return
# Execute function 'gw_fct_plan_result'
sql = ("SELECT " + self.schema_name + ".gw_fct_plan_result('" +
result_name + "', " + result_type + ", '" + coefficient +
"', '" + observ + "');")
status = self.controller.execute_sql(sql)
if status:
message = "Values has been updated"
self.controller.show_info(message)
# Refresh canvas and close dialog
self.iface.mapCanvas().refreshAllLayers()
self.close_dialog(dialog)
def reset_model_psector(self, geom_type):
""" Reset model of the widget """
table_relation = "" + geom_type + "_plan"
widget_name = "tbl_" + table_relation
widget = utils_giswater.getWidget(widget_name)
if widget:
widget.setModel(None)
def reset_model(self, dialog, table_object, geom_type):
""" Reset model of the widget """
table_relation = table_object + "_x_" + geom_type
widget_name = "tbl_" + table_relation
widget = utils_giswater.getWidget(dialog, widget_name)
if widget:
widget.setModel(None)
def reload_qtable(self, dialog, geom_type, plan_om):
""" Reload QtableView """
value = utils_giswater.getWidgetText(dialog, dialog.psector_id)
sql = ("SELECT * FROM " + self.schema_name + "." + plan_om + "_psector_x_" + geom_type + ""
" WHERE psector_id = '" + str(value) + "'")
qtable = utils_giswater.getWidget(dialog, 'tbl_psector_x_' + geom_type)
self.fill_table_by_query(qtable, sql)
self.set_table_columns(dialog, qtable, plan_om + "_psector_x_"+geom_type)
self.refresh_map_canvas()
def set_table_model(self, table_object, geom_type, expr_filter):
""" Sets a TableModel to @widget_name attached to
@table_name and filter @expr_filter
"""
expr = None
if expr_filter:
# Check expression
(is_valid,
expr) = self.check_expression(expr_filter) #@UnusedVariable
if not is_valid:
return expr
# Set a model with selected filter expression
table_name = "v_edit_" + geom_type
if self.schema_name not in table_name:
table_name = self.schema_name + "." + table_name
# Set the model
model = QSqlTableModel()
model.setTable(table_name)
model.setEditStrategy(QSqlTableModel.OnManualSubmit)
model.select()
if model.lastError().isValid():
self.controller.show_warning(model.lastError().text())
return expr
# Attach model to selected widget
if type(table_object) is str:
widget = utils_giswater.getWidget(table_object)
if not widget:
message = "Widget not found"
self.controller.log_info(message, parameter=table_object)
return expr
elif type(table_object) is QTableView:
widget = table_object
else:
message = "Table_object is not a table name or QTableView"
self.controller.log_info(message)
return expr
if expr_filter:
widget.setModel(model)
widget.model().setFilter(expr_filter)
widget.model().select()
else:
widget.setModel(None)
return expr
def update_geom_type(self, geom_type):
""" Get elements from @geom_type and update his corresponding table """
widget = "tbl_cat_work_x_" + geom_type
tablename = "v_edit_" + geom_type
widget = utils_giswater.getWidget(widget)
selected_list = widget.model()
if selected_list is None:
return
sql = ""
for x in range(0, selected_list.rowCount()):
index = selected_list.index(x,0)
sql += ("UPDATE " + self.schema_name + "." + tablename + ""
" SET state = '0', workcat_id_end = '" + str(self.workcat_id_end) + "',"
" enddate = '" + str(self.enddate) + "'"
" WHERE " + geom_type + "_id = '" + str(selected_list.data(index)) + "';\n")
self.controller.execute_sql(sql, log_sql=True)
def reload_table(self, dialog, table_object, geom_type, expr_filter):
""" Reload @widget with contents of @tablename applying selected @expr_filter """
if type(table_object) is str:
widget_name = "tbl_" + table_object + "_x_" + geom_type
widget = utils_giswater.getWidget(dialog, widget_name)
if not widget:
message = "Widget not found"
self.controller.log_info(message, parameter=widget_name)
return None
elif type(table_object) is QTableView:
widget = table_object
else:
message = "Table_object is not a table name or QTableView"
self.controller.log_info(message)
return None
expr = self.set_table_model(dialog, widget, geom_type, expr_filter)
return expr
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 delete_records(self, dialog, table_object, query=False):
""" Delete selected elements of the table """
self.disconnect_signal_selection_changed()
if type(table_object) is str:
widget_name = "tbl_" + table_object + "_x_" + self.geom_type
widget = utils_giswater.getWidget(dialog, widget_name)
if not widget:
message = "Widget not found"
self.controller.show_warning(message, parameter=widget_name)
return
elif type(table_object) is QTableView:
widget = table_object
else:
message = "Table_object is not a table name or QTableView"
self.controller.log_info(message)
return
# Get selected rows
selected_list = widget.selectionModel().selectedRows()
if len(selected_list) == 0:
message = "Any record selected"
self.controller.show_info_box(message)
return
if query:
full_list = widget.model()
for x in range(0, full_list.rowCount()):
self.ids.append(widget.model().record(x).value(str(self.geom_type)+"_id"))
else:
self.ids = self.list_ids[self.geom_type]
field_id = self.geom_type + "_id"
del_id = []
inf_text = ""
list_id = ""
for i in range(0, len(selected_list)):
row = selected_list[i].row()
id_feature = widget.model().record(row).value(field_id)
inf_text += str(id_feature) + ", "
list_id = list_id + "'" + str(id_feature) + "', "
del_id.append(id_feature)
inf_text = inf_text[:-2]
list_id = list_id[:-2]
message = "Are you sure you want to delete these records?"
title = "Delete records"
answer = self.controller.ask_question(message, title, inf_text)
if answer:
for el in del_id:
self.ids.remove(el)
else:
return
expr_filter = None
expr = None
if len(self.ids) > 0:
# Set expression filter with features in the list
expr_filter = "\"" + field_id + "\" IN ("
for i in range(len(self.ids)):
expr_filter += "'" + str(self.ids[i]) + "', "
expr_filter = expr_filter[:-2] + ")"
# Check expression
(is_valid, expr) = self.check_expression(expr_filter) #@UnusedVariable
if not is_valid:
return
# Update model of the widget with selected expr_filter
if query:
self.delete_feature_at_plan(dialog, self.geom_type, list_id)
self.reload_qtable(dialog, self.geom_type, self.plan_om)
else:
self.reload_table(dialog, table_object, self.geom_type, expr_filter)
self.apply_lazy_init(table_object)
# Select features with previous filter
# Build a list of feature id's and select them
self.select_features_by_ids(self.geom_type, expr)
if query:
self.remove_selection()
# Update list
self.list_ids[self.geom_type] = self.ids
self.enable_feature_type(dialog)
self.connect_signal_selection_changed(dialog, table_object)