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)