def reload_table_relations(self): """ Reload @widget with contents of @tablename applying selected @expr_filter """ standard_model = self.tbl_relation.model() feature_type = utils_giswater.get_item_data(self.dlg_lot, self.dlg_lot.feature_type, 1).lower() lot_id = utils_giswater.getWidgetText(self.dlg_lot, self.lot_id) id_list = self.get_table_values(self.tbl_relation, feature_type) layer_name = 'v_edit_' + utils_giswater.get_item_data( self.dlg_lot, self.dlg_lot.feature_type, 0).lower() field_id = utils_giswater.get_item_data( self.dlg_lot, self.dlg_lot.feature_type, 0).lower() + str('_id') layer = self.controller.get_layer_by_tablename(layer_name) for feature_id in self.ids: feature = self.get_feature_by_id(layer, feature_id, field_id) item = [] if feature_id not in id_list: row = [] item.append(lot_id) item.append(feature_id) item.append(feature.attribute('code')) item.append(0) for value in item: row.append(QStandardItem(str(value))) if len(row) > 0: standard_model.appendRow(row) self.insert_single_checkbox(self.tbl_relation)
def insert_row(self): """ Inser single row into QStandardItemModel """ standard_model = self.tbl_relation.model() feature_id = utils_giswater.getWidgetText(self.dlg_lot, self.dlg_lot.feature_id) lot_id = utils_giswater.getWidgetText(self.dlg_lot, self.lot_id) layer_name = 'v_edit_' + utils_giswater.get_item_data( self.dlg_lot, self.dlg_lot.feature_type, 0).lower() field_id = utils_giswater.get_item_data( self.dlg_lot, self.dlg_lot.feature_type, 0).lower() + str('_id') layer = self.controller.get_layer_by_tablename(layer_name) feature = self.get_feature_by_id(layer, feature_id, field_id) if feature is False: return if feature_id not in self.ids: item = [lot_id, feature_id, feature.attribute('code'), 0] row = [] for value in item: if value not in ('', None) and type(value) != QPyNullVariant: row.append(QStandardItem(str(value))) else: row.append(QStandardItem(None)) if len(row) > 0: standard_model.appendRow(row) self.ids.append(feature_id) self.insert_single_checkbox(self.tbl_relation)
def reload_table_visit(self): feature_type = utils_giswater.get_item_data( self.dlg_lot, self.dlg_lot.cmb_visit_class, 2) object_id = utils_giswater.getWidgetText(self.dlg_lot, self.dlg_lot.txt_filter) visit_start = self.dlg_lot.date_event_from.date() visit_end = self.dlg_lot.date_event_to.date() # Get selected dates date_from = visit_start.toString('yyyyMMdd 00:00:00') date_to = visit_end.toString('yyyyMMdd 23:59:59') if date_from > date_to: message = "Selected date interval is not valid" self.controller.show_warning(message) return visit_class_id = utils_giswater.get_item_data( self.dlg_lot, self.dlg_lot.cmb_visit_class, 0) sql = ("SELECT visitclass_id, formname, tablename FROM " + self.schema_name + ".config_api_visit " " WHERE visitclass_id ='" + str(visit_class_id) + "'") row = self.controller.get_row(sql, log_sql=False) table_name = row['tablename'] if self.schema_name not in table_name: table_name = self.schema_name + "." + table_name # Create interval dates format_low = 'yyyy-MM-dd 00:00:00.000' format_high = 'yyyy-MM-dd 23:59:59.999' interval = "'{}'::timestamp AND '{}'::timestamp".format( visit_start.toString(format_low), visit_end.toString(format_high)) expr_filter = ("(startdate BETWEEN {0}) AND (enddate BETWEEN {0})". format(interval)) if object_id != 'null': expr_filter += " AND " + str( feature_type) + "_id::TEXT ILIKE '%" + str(object_id) + "%'" expr_filter += " AND " + str(feature_type) + "_id IN ('0', " for i in range(len(self.ids)): expr_filter += "'" + str(self.ids[i]) + "', " expr_filter = expr_filter[:-2] + ")" model = QSqlTableModel() model.setTable(table_name) model.setEditStrategy(QSqlTableModel.OnManualSubmit) model.setFilter(expr_filter) model.sort(0, 1) model.select() # Check for errors if model.lastError().isValid(): self.controller.show_warning(model.lastError().text()) # Attach model to table view self.dlg_lot.tbl_visit.setModel(model)
def save_hydrology(self): sql = ("SELECT cur_user FROM " + self.schema_name + ".inp_selector_hydrology " " WHERE cur_user = current_user") row = self.controller.get_row(sql) if row: sql = ("UPDATE " + self.schema_name + ".inp_selector_hydrology " " SET hydrology_id = "+str(utils_giswater.get_item_data(self.dlg_hydrology_selector, self.dlg_hydrology_selector.hydrology, 1))+"" " WHERE cur_user = current_user") else: sql = ("INSERT INTO " + self.schema_name + ".inp_selector_hydrology (hydrology_id, cur_user)" " VALUES('" +str(utils_giswater.get_item_data(self.dlg_hydrology_selector, self.dlg_hydrology_selector.hydrology, 1))+"', current_user)") self.controller.execute_sql(sql) message = "Values has been update" self.controller.show_info(message) self.close_dialog(self.dlg_hydrology_selector)
def set_values(self, lot_id): sql = ("SELECT * FROM " + self.schema_name + ".om_visit_lot " " WHERE id ='" + str(lot_id) + "'") lot = self.controller.get_row(sql, log_sql=False) if lot is not None: utils_giswater.setWidgetText(self.dlg_lot, 'txt_idval', lot['idval']) utils_giswater.setCalendarDate(self.dlg_lot, 'startdate', lot['startdate']) utils_giswater.setCalendarDate(self.dlg_lot, 'enddate', lot['enddate']) utils_giswater.set_combo_itemData(self.dlg_lot.cmb_visit_class, lot['visitclass_id'], 0) utils_giswater.set_combo_itemData(self.dlg_lot.cmb_assigned_to, lot['team_id'], 0) utils_giswater.setWidgetText(self.dlg_lot, 'descript', lot['descript']) utils_giswater.set_combo_itemData(self.dlg_lot.cmb_status, lot['status'], 0) self.controller.log_info(str(lot['status'])) if lot['status'] not in (0, None): self.dlg_lot.cmb_status.setEnabled(False) utils_giswater.set_combo_itemData(self.dlg_lot.feature_type, lot['feature_type'], 0) feature_type = utils_giswater.get_item_data(self.dlg_lot, self.dlg_lot.feature_type, 1).lower() table_name = "v_edit_" + str(feature_type) self.set_headers(self.tbl_relation) self.set_table_columns(self.dlg_lot, self.dlg_lot.tbl_relation, table_name)
def update_id_list(self): feature_type = utils_giswater.get_item_data(self.dlg_lot, self.dlg_lot.feature_type, 1).lower() list_ids = self.get_table_values(self.tbl_relation, feature_type) for id_ in list_ids: if id_ not in self.ids: self.ids.append(id_)
def set_active_layer(self): self.current_layer = self.iface.activeLayer() # Set active layer layer_name = 'v_edit_' + utils_giswater.get_item_data( self.dlg_lot, self.visit_class, 2).lower() self.layer_lot = self.controller.get_layer_by_tablename(layer_name) self.iface.setActiveLayer(self.layer_lot) self.iface.legendInterface().setLayerVisible(self.layer_lot, True)
def get_dialog(self): visit_class_id = utils_giswater.get_item_data( self.dlg_lot, self.dlg_lot.cmb_visit_class, 0) sql = ("SELECT visitclass_id, formname, tablename FROM " + self.schema_name + ".config_api_visit " " WHERE visitclass_id ='" + str(visit_class_id) + "'") row = self.controller.get_row(sql, log_sql=True) self.controller.log_info(str("TODO: ABRIR FORMULARIOS")) self.controller.log_info(str(row))
def update_table(self, tablename, dialog): """ INSERT or UPDATE tables according :param update""" sql = "SELECT * FROM " + self.schema_name + "." + tablename row = self.controller.get_row(sql) columns = [] for i in range(0, len(row)): column_name = self.dao.get_column_name(i) columns.append(column_name) if columns is not None: sql = "UPDATE " + self.schema_name + "." + tablename + " SET " for column_name in columns: if column_name != 'id': widget = dialog.findChild(QWidget, column_name) widget_type = utils_giswater.getWidgetType(dialog, widget) if widget_type is QCheckBox: value = utils_giswater.isChecked(dialog, widget) elif widget_type is QDateEdit: date = dialog.findChild(QDateEdit, str(column_name)) value = date.dateTime().toString('dd/MM/yyyy') elif widget_type is QTimeEdit: aux = 0 widget_day = str(column_name) + "_day" day = utils_giswater.getText(dialog, widget_day) if day != "null": aux = int(day) * 24 time = dialog.findChild(QTimeEdit, str(column_name)) timeparts = time.dateTime().toString('HH:mm:ss').split(':') h = int(timeparts[0]) + int(aux) aux = str(h) + ":" + str(timeparts[1]) + ":" + str(timeparts[2]) value = aux elif widget_type is QSpinBox: x = dialog.findChild(QSpinBox, str(column_name)) value = x.value() elif widget_type is QComboBox: value = utils_giswater.get_item_data(dialog, widget) else: value = utils_giswater.getWidgetText(dialog, widget) if value == 'null': sql += column_name + " = null, " elif value is None: pass else: if type(value) is not bool and widget_type is not QSpinBox: value = value.replace(",", ".") sql += column_name + " = '" + str(value) + "', " sql = sql[:len(sql) - 2] self.controller.execute_sql(sql) dialog.close()
def manage_leave_visit_tab(self): """ manage all the action when leaving the VisitTab A) Manage sync between GUI values and Visit record in DB.""" # A) fill Visit basing on GUI values self.current_visit.id = int(self.visit_id.text()) self.current_visit.startdate = self.dlg.startdate.date().toString(Qt.ISODate) self.current_visit.enddate = self.dlg.enddate.date().toString(Qt.ISODate) self.current_visit.user_name = self.user_name.text() self.current_visit.ext_code = self.ext_code.text() self.current_visit.visitcat_id = utils_giswater.get_item_data(self.dlg.visitcat_id, 0) self.current_visit.descript = self.dlg.descript.text() # update or insert but without closing the transaction: autocommit=False self.current_visit.upsert(commit=self.autocommit)
def set_headers(self, qtable): feature_type = utils_giswater.get_item_data( self.dlg_lot, self.dlg_lot.cmb_visit_class, 2).lower() columns_name = self.controller.get_columns_list('om_visit_lot_x_' + str(feature_type)) columns_name.append(['validate']) standard_model = QStandardItemModel() qtable.setModel(standard_model) qtable.horizontalHeader().setStretchLastSection(True) # # Get headers headers = [] for x in columns_name: headers.append(x[0]) # Set headers standard_model.setHorizontalHeaderLabels(headers)
def populate_table_relations(self, lot_id): standard_model = self.tbl_relation.model() feature_type = utils_giswater.get_item_data( self.dlg_lot, self.dlg_lot.cmb_visit_class, 2).lower() sql = ("SELECT * FROM " + self.schema_name + ".om_visit_lot_x_" + str(feature_type) + "" " WHERE lot_id ='" + str(lot_id) + "'") rows = self.controller.get_rows(sql, log_sql=True) for row in rows: item = [] for value in row: if value is not None: item.append(QStandardItem(str(value))) else: item.append(QStandardItem(None)) if len(row) > 0: standard_model.appendRow(item)
def set_dates(self): visit_class_id = utils_giswater.get_item_data( self.dlg_lot, self.dlg_lot.cmb_visit_class, 0) sql = ("SELECT visitclass_id, formname, tablename FROM " + self.schema_name + ".config_api_visit " " WHERE visitclass_id ='" + str(visit_class_id) + "'") row = self.controller.get_row(sql, log_sql=True) sql = ("SELECT MIN(startdate), MAX(enddate)" " FROM {}.{}".format(self.schema_name, row['tablename'])) row = self.controller.get_row(sql) if row: if row[0]: self.dlg_lot.date_event_from.setDate(row[0]) else: current_date = QDate.currentDate() self.dlg_lot.date_event_from.setDate(current_date) if row[1]: self.dlg_lot.date_event_to.setDate(row[1]) else: current_date = QDate.currentDate() self.dlg_lot.date_event_to.setDate(current_date)
def zoom_to_feature(self): feature_type = utils_giswater.get_item_data(self.dlg_lot, self.visit_class, 2).lower() selected_list = self.tbl_relation.selectionModel().selectedRows() if len(selected_list) == 0: message = "Any record selected" self.controller.show_warning(message) return index = selected_list[0] row = index.row() column_index = utils_giswater.get_col_index_by_col_name( self.tbl_relation, feature_type + '_id') feature_id = index.sibling(row, column_index).data() expr_filter = '"{}_id" IN ({})'.format(self.geom_type, "'" + feature_id + "'") # Check expression (is_valid, expr) = self.check_expression(expr_filter) self.select_features_by_ids(feature_type, expr) self.iface.actionZoomToSelected().trigger()
def upsert(self, combo, tablename): # Check if table exists if not self.controller.check_table(tablename): message = "Table not found" self.controller.show_warning(message, parameter=tablename) return result_id = utils_giswater.get_item_data(combo, 1) sql = ("DELETE FROM " + self.schema_name + "." + tablename + " WHERE current_user = cur_user;" "\nINSERT INTO " + self.schema_name + "." + tablename + " (result_id, cur_user)" " VALUES(" + str(result_id) + ", current_user);") status = self.controller.execute_sql(sql) if status: message = "Values has been updated" self.controller.show_info(message) # Refresh canvas self.iface.mapCanvas().refreshAllLayers() self.close_dialog(self.dlg)
def remove_selection(self, dialog, qtable): self.disconnect_signal_selection_changed() feature_type = utils_giswater.get_item_data(self.dlg_lot, self.dlg_lot.feature_type, 0).lower() # Get selected rows index_list = qtable.selectionModel().selectedRows() if len(index_list) == 0: message = "Any record selected" self.controller.show_info_box(message) return index = index_list[0] model = qtable.model() for i in range(len(index_list) - 1, -1, -1): row = index_list[i].row() column_index = utils_giswater.get_col_index_by_col_name( qtable, feature_type + '_id') feature_id = index.sibling(row, column_index).data() self.ids.remove(feature_id) model.takeRow(row) self.enable_combos(dialog)
def manage_lot(self, lot_id=None, is_new=True, visitclass_id=None): # turnoff autocommit of this and base class. Commit will be done at dialog button box level management self.autocommit = True self.remove_ids = False self.is_new_lot = is_new self.chk_position = 5 # Variable used to set the position of the QCheckBox in the relations table # Get layers of every geom_type self.reset_lists() self.reset_layers() self.layers['arc'] = [ self.controller.get_layer_by_tablename('v_edit_arc') ] self.layers['node'] = [ self.controller.get_layer_by_tablename('v_edit_node') ] self.layers['connec'] = [ self.controller.get_layer_by_tablename('v_edit_connec') ] # Remove 'gully' for 'WS' if self.controller.get_project_type() != 'ws': self.layers['gully'] = self.controller.get_group_layers('gully') self.dlg_lot = AddLot() self.load_settings(self.dlg_lot) self.dropdown = self.dlg_lot.findChild(QToolButton, 'action_selector') self.dropdown.setPopupMode(QToolButton.MenuButtonPopup) # Create action and put into QToolButton action_by_expression = self.create_action('action_by_expression', self.dlg_lot.action_selector, '204', 'Select by expression') action_by_polygon = self.create_action('action_by_polygon', self.dlg_lot.action_selector, '205', 'Select by polygon') self.dropdown.addAction(action_by_expression) self.dropdown.addAction(action_by_polygon) self.dropdown.setDefaultAction(action_by_expression) self.dlg_lot.open() # Set icons self.set_icon(self.dlg_lot.btn_feature_insert, "111") self.set_icon(self.dlg_lot.btn_feature_delete, "112") self.set_icon(self.dlg_lot.btn_feature_snapping, "137") self.lot_id = self.dlg_lot.findChild(QLineEdit, "lot_id") self.id_val = self.dlg_lot.findChild(QLineEdit, "txt_idval") self.user_name = self.dlg_lot.findChild(QLineEdit, "user_name") self.visit_class = self.dlg_lot.findChild(QComboBox, "cmb_visit_class") # Tab 'Relations' self.feature_type = self.dlg_lot.findChild(QComboBox, "feature_type") self.tbl_relation = self.dlg_lot.findChild(QTableView, "tbl_relation") utils_giswater.set_qtv_config(self.tbl_relation) utils_giswater.set_qtv_config(self.dlg_lot.tbl_visit) self.feature_type.setEnabled(False) # Fill QWidgets of the form self.fill_fields() new_lot_id = lot_id if lot_id is None: new_lot_id = self.get_next_id('om_visit_lot', 'id') utils_giswater.setWidgetText(self.dlg_lot, self.lot_id, new_lot_id) self.geom_type = utils_giswater.get_item_data(self.dlg_lot, self.visit_class, 2).lower() viewname = "v_edit_" + self.geom_type self.set_completer_feature_id(self.dlg_lot.feature_id, self.geom_type, viewname) self.clear_selection() # Set actions signals action_by_expression.triggered.connect( partial(self.activate_selection, self.dlg_lot, action_by_expression, 'mActionSelectByExpression')) action_by_polygon.triggered.connect( partial(self.activate_selection, self.dlg_lot, action_by_polygon, 'mActionSelectPolygon')) # Set widgets signals self.dlg_lot.btn_feature_insert.clicked.connect( partial(self.insert_row)) self.dlg_lot.btn_feature_delete.clicked.connect( partial(self.remove_selection, self.dlg_lot, self.tbl_relation)) self.dlg_lot.btn_feature_snapping.clicked.connect( partial(self.set_active_layer)) self.dlg_lot.btn_feature_snapping.clicked.connect( partial(self.selection_init, self.dlg_lot)) self.dlg_lot.cmb_visit_class.currentIndexChanged.connect( self.set_feature_type_cmb) self.dlg_lot.cmb_visit_class.currentIndexChanged.connect( self.set_active_layer) self.dlg_lot.cmb_visit_class.currentIndexChanged.connect( partial(self.event_feature_type_selected, self.dlg_lot)) self.dlg_lot.cmb_visit_class.currentIndexChanged.connect( partial(self.reload_table_visit)) self.dlg_lot.txt_filter.textChanged.connect( partial(self.reload_table_visit)) self.dlg_lot.date_event_from.dateChanged.connect( partial(self.reload_table_visit)) self.dlg_lot.date_event_to.dateChanged.connect( partial(self.reload_table_visit)) self.dlg_lot.tbl_relation.doubleClicked.connect( partial(self.zoom_to_feature)) self.dlg_lot.btn_cancel.clicked.connect(partial(self.manage_rejected)) self.dlg_lot.rejected.connect(partial(self.manage_rejected)) self.dlg_lot.btn_accept.clicked.connect(partial(self.save_lot)) self.set_headers(self.tbl_relation) if lot_id is not None: utils_giswater.set_combo_itemData(self.visit_class, str(visitclass_id), 0) self.geom_type = utils_giswater.get_item_data( self.dlg_lot, self.visit_class, 2).lower() self.set_values(lot_id) self.populate_table_relations(lot_id) self.update_id_list() sql = ("SELECT * FROM " + self.schema_name + ".om_visit_lot_x_" + str(self.geom_type) + "" " WHERE lot_id ='" + str(lot_id) + "'") rows = self.controller.get_rows(sql, log_sql=True) self.put_checkbox(self.tbl_relation, rows, 'status', 3) self.set_dates() self.reload_table_visit() self.enable_feature_type(self.dlg_lot) self.set_feature_type_cmb() # Set autocompleters of the form self.set_completers() # Set model signals self.tbl_relation.model().rowsInserted.connect(self.set_dates) self.tbl_relation.model().rowsInserted.connect(self.reload_table_visit) self.tbl_relation.model().rowsRemoved.connect(self.set_dates) self.tbl_relation.model().rowsRemoved.connect(self.reload_table_visit) # Open the dialog self.open_dialog(self.dlg_lot, dlg_name="add_lot")
def save_lot(self): lot = {} lot['idval'] = utils_giswater.getWidgetText(self.dlg_lot, 'txt_idval', False, False) lot['startdate'] = utils_giswater.getCalendarDate( self.dlg_lot, 'startdate') lot['enddate'] = utils_giswater.getCalendarDate( self.dlg_lot, 'enddate') lot['visitclass_id'] = utils_giswater.get_item_data( self.dlg_lot, self.dlg_lot.cmb_visit_class, 0) lot['descript'] = utils_giswater.getWidgetText(self.dlg_lot, 'descript', False, False) lot['status'] = utils_giswater.get_item_data(self.dlg_lot, self.dlg_lot.cmb_status, 0) lot['feature_type'] = utils_giswater.get_item_data( self.dlg_lot, self.dlg_lot.cmb_visit_class, 2).lower() lot['team_id'] = utils_giswater.get_item_data( self.dlg_lot, self.dlg_lot.cmb_assigned_to, 0) keys = "" values = "" update = "" for key, value in lot.items(): if value != '': keys += "" + key + ", " if type(value) in (int, bool): values += "$$" + str(value) + "$$, " update += str(key) + "=$$" + str(value) + "$$, " else: values += "$$" + value + "$$, " update += str(key) + "=$$" + value + "$$, " keys = keys[:-2] values = values[:-2] update = update[:-2] if self.is_new_lot is True: sql = ("INSERT INTO " + self.schema_name + ".om_visit_lot(" + keys + ") " " VALUES (" + values + ") RETURNING id") row = self.controller.execute_returning(sql, log_sql=False) lot_id = row[0] else: lot_id = utils_giswater.getWidgetText(self.dlg_lot, 'lot_id', False, False) sql = ("UPDATE " + self.schema_name + ".om_visit_lot " " SET " + str(update) + "" " WHERE id = '" + str(lot_id) + "'; \n") self.controller.execute_sql(sql, log_sql=False) sql = ("DELETE FROM " + self.schema_name + ".om_visit_lot_x_" + lot['feature_type'] + " " " WHERE lot_id = '" + str(lot_id) + "'; \n") model_rows = self.read_standaritemmodel(self.tbl_relation) for item in model_rows: keys = "lot_id, " values = "$$" + str(lot_id) + "$$, " for key, value in item.items(): if key != 'lot_id': if value not in ('', None) and type(value) != QPyNullVariant: keys += "" + key + ", " if type(value) in (int, bool): values += "$$" + str(value) + "$$, " else: values += "$$" + value + "$$, " keys = keys[:-2] values = values[:-2] sql += ("INSERT INTO " + self.schema_name + ".om_visit_lot_x_" + lot['feature_type'] + "(" + keys + ") " " VALUES (" + values + "); \n") status = self.controller.execute_sql(sql, log_sql=False) if status: self.manage_rejected()
def set_feature_type_cmb(self): feature_type = utils_giswater.get_item_data( self.dlg_lot, self.dlg_lot.cmb_visit_class, 2) utils_giswater.set_combo_itemData(self.feature_type, feature_type, 1) self.feature_type.setEnabled(False)