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 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 update_config(self, tablename, dialog):
""" Update table @tablename from values get from @dialog """
sql = "SELECT * FROM " + self.schema_name + "." + tablename
row = self.dao.get_row(sql)
columns = []
for i in range(0, len(row)):
column_name = self.dao.get_column_name(i)
if column_name != 'id':
columns.append(column_name)
if columns is None:
return
sql = "UPDATE " + self.schema_name + "." + tablename + " SET "
for column_name in columns:
widget_type = utils_giswater.getWidgetType(column_name)
if widget_type is QCheckBox:
value = utils_giswater.isChecked(column_name)
elif widget_type is QDateEdit:
date = dialog.findChild(QDateEdit, str(column_name))
value = date.dateTime().toString('yyyy-MM-dd')
elif widget_type is QTimeEdit:
aux = 0
widget_day = str(column_name) + "_day"
day = utils_giswater.getText(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]) + ":00"
value = aux
elif widget_type is QSpinBox:
x = dialog.findChild(QSpinBox, str(column_name))
value = x.value()
else:
value = utils_giswater.getWidgetText(column_name)
if value is not None:
if value == 'null':
sql += column_name + " = null, "
else:
if type(value) is not bool and widget_type is not QSpinBox:
value = value.replace(",", ".")
sql += column_name + " = '" + str(value) + "', "
sql = sql[:- 2]
self.controller.execute_sql(sql)
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 mg_config_get_data(self, tablename):
''' Get data from selected table '''
sql = "SELECT *"
sql+= " 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])
else:
utils_giswater.setWidgetText(column_name, row[column_name])
columns.append(column_name)
return columns
def mg_config_accept_table(self, tablename, columns):
''' Update values of selected 'tablename' with the content of 'columns' '''
if columns is not None:
sql = "UPDATE "+self.schema_name+"."+tablename+" SET "
for column_name in columns:
if column_name != 'id':
widget_type = utils_giswater.getWidgetType(column_name)
if widget_type is QCheckBox:
value = utils_giswater.isChecked(column_name)
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[:-2]
self.dao.execute_sql(sql)
def insert_or_update(self, update, tablename, dialog):
""" INSERT or UPDATE tables according :param update"""
sql = "SELECT *"
sql += " FROM " + self.schema_name + "." + tablename
row = self.dao.get_row(sql)
columns = []
for i in range(0, len(row)):
column_name = self.dao.get_column_name(i)
columns.append(column_name)
if update:
if columns is not None:
sql = "UPDATE " + self.schema_name + "." + tablename + " SET "
for column_name in columns:
if column_name != '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 = dialog.findChild(QDateEdit,
str(column_name))
value = date.dateTime().toString('yyyy-MM-dd')
elif widget_type is QTimeEdit:
aux = 0
widget_day = str(column_name) + "_day"
day = utils_giswater.getText(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]) + ":00"
value = aux
elif widget_type is QSpinBox:
x = dialog.findChild(QSpinBox, str(column_name))
value = x.value()
else:
value = utils_giswater.getWidgetText(column_name)
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]
else:
values = "VALUES("
if columns is not None:
sql = "INSERT INTO " + self.schema_name + "." + tablename + " ("
for column_name in columns:
if column_name != 'id':
widget_type = utils_giswater.getWidgetType(column_name)
if widget_type is not None:
if widget_type is QCheckBox:
values += utils_giswater.isChecked(
column_name) + ", "
elif widget_type is QDateEdit:
date = dialog.findChild(
QDateEdit, str(column_name))
values += date.dateTime().toString(
'yyyy-MM-dd') + ", "
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
self.controller.execute_sql(sql)
dialog.close()
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 insert_or_update_new_psector(self, update, tablename):
sql = "SELECT *"
sql += " FROM " + self.schema_name + "." + tablename
row = self.dao.get_row(sql)
columns = []
for i in range(0, len(row)):
column_name = self.dao.get_column_name(i)
columns.append(column_name)
if update:
if columns is not None:
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')
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 = '" + self.psector_id.text() + "'"
else:
values = "VALUES("
if columns is not None:
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:
values += utils_giswater.isChecked(column_name)+", "
elif widget_type is QDateEdit:
date = self.dlg.findChild(QDateEdit, str(column_name))
values += date.dateTime().toString('yyyy-MM-dd HH:mm:ss')+", "
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
self.controller.execute_sql(sql)
self.close_dialog()
