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)
