class MainWindow(QMainWindow):
def __init__(self):
super(MainWindow, self).__init__(None)
uic.loadUi("mainwindow.ui", self)
self.model = QSqlTableModel(self)
self.model.setTable("student")
self.model.select()
# 设置编辑策略
self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.tableView.setModel(self.model)
# 提交修改按钮
@pyqtSlot()
def on_pushButton_clicked(self):
# 开始事务操作
self.model.database().transaction() # 启动 事物操作
if (self.model.submitAll()):
self.model.database().commit() #提交
else:
self.model.database().rollback() #回滚
QMessageBox.warning(
self, self.tr("tableModel"),
self.tr("数据库错误: %1").arg(self.model.lastError().text()))
# 撤销修改按钮
@pyqtSlot()
def on_pushButton_2_clicked(self):
self.model.revertAll()
# 查询按钮，进行筛选
@pyqtSlot()
def on_pushButton_7_clicked(self):
name = self.lineEdit.text()
#根据姓名进行筛选，一定要使用单引号
#model.setFilter(QString("name = '%1'").arg(name)) # filter 过滤器
self.model.setFilter(
QString("name LIKE '%%%1%%'").arg(name)) # filter过滤器,模糊查询
self.model.select()
# 显示全表按钮
@pyqtSlot()
def on_pushButton_8_clicked(self):
self.model.setTable("student")
self.model.select()
# 按id升序排列按钮
@pyqtSlot()
def on_pushButton_5_clicked(self):
#id属性，即第0列，升序排列
self.model.setSort(0, Qt.AscendingOrder)
self.model.select()
# 按id降序排列按钮
@pyqtSlot()
def on_pushButton_6_clicked(self):
self.model.setSort(0, Qt.DescendingOrder)
self.model.select()
# 删除选中行按钮
@pyqtSlot()
def on_pushButton_4_clicked(self):
# 获取选中的行
curRow = self.tableView.currentIndex().row()
# 删除该行
self.model.removeRow(curRow)
ok = QMessageBox.warning(self, self.tr("删除当前行!"),
self.tr("你确定删除当前行吗？"), QMessageBox.Yes,
QMessageBox.No)
if (ok == QMessageBox.No):
# 如果不删除，则撤销
self.model.revertAll()
else: # 否则提交，在数据库中删除该行
self.model.submitAll()
# 添加记录按钮
@pyqtSlot()
def on_pushButton_3_clicked(self):
# 获得表的行数
rowNum = self.model.rowCount()
id = 10
# 添加一行
self.model.insertRow(rowNum)
self.model.setData(self.model.index(rowNum, 0), id)
class ParentAction():
def __init__(self, iface, settings, controller, plugin_dir):
''' Class constructor '''
# Initialize instance attributes
self.giswater_version = "3.0"
self.iface = iface
self.canvas = self.iface.mapCanvas()
self.settings = settings
self.controller = controller
self.plugin_dir = plugin_dir
self.dao = self.controller.dao
self.schema_name = self.controller.schema_name
self.project_type = None
# Get files to execute giswater jar (only in Windows)
if 'nt' in sys.builtin_module_names:
self.plugin_version = self.get_plugin_version()
self.java_exe = self.get_java_exe()
(self.giswater_file_path,
self.giswater_build_version) = self.get_giswater_jar()
self.gsw_file = self.controller.plugin_settings_value('gsw_file')
def set_controller(self, controller):
""" Set controller class """
self.controller = controller
self.schema_name = self.controller.schema_name
def get_plugin_version(self):
''' Get plugin version from metadata.txt file '''
# Check if metadata file exists
metadata_file = os.path.join(self.plugin_dir, 'metadata.txt')
if not os.path.exists(metadata_file):
message = "Metadata file not found at: " + metadata_file
self.controller.show_warning(message,
10,
context_name='ui_message')
return None
metadata = ConfigParser.ConfigParser()
metadata.read(metadata_file)
plugin_version = metadata.get('general', 'version')
if plugin_version is None:
msg = "Plugin version not found"
self.controller.show_warning(msg, 10, context_name='ui_message')
return plugin_version
def get_giswater_jar(self):
''' Get executable Giswater file and build version from windows registry '''
reg_hkey = "HKEY_LOCAL_MACHINE"
reg_path = "SOFTWARE\\Giswater\\" + self.giswater_version
reg_name = "InstallFolder"
giswater_folder = utils_giswater.get_reg(reg_hkey, reg_path, reg_name)
if giswater_folder is None:
message = "Cannot get giswater folder from windows registry at: " + reg_path
self.controller.show_warning(message,
10,
context_name='ui_message')
return (None, None)
# Check if giswater folder exists
if not os.path.exists(giswater_folder):
message = "Giswater folder not found at: " + giswater_folder
self.controller.show_warning(message,
10,
context_name='ui_message')
return (None, None)
# Check if giswater executable file file exists
giswater_file_path = giswater_folder + "\giswater.jar"
if not os.path.exists(giswater_file_path):
message = "Giswater executable file not found at: " + giswater_file_path
self.controller.show_warning(message,
10,
context_name='ui_message')
return (None, None)
# Get giswater major version
reg_name = "MajorVersion"
major_version = utils_giswater.get_reg(reg_hkey, reg_path, reg_name)
if major_version is None:
message = "Cannot get giswater major version from windows registry at: " + reg_path
self.controller.show_warning(message,
10,
context_name='ui_message')
return (giswater_file_path, None)
# Get giswater minor version
reg_name = "MinorVersion"
minor_version = utils_giswater.get_reg(reg_hkey, reg_path, reg_name)
if minor_version is None:
message = "Cannot get giswater major version from windows registry at: " + reg_path
self.controller.show_warning(message,
10,
context_name='ui_message')
return (giswater_file_path, None)
# Get giswater build version
reg_name = "BuildVersion"
build_version = utils_giswater.get_reg(reg_hkey, reg_path, reg_name)
if build_version is None:
message = "Cannot get giswater build version from windows registry at: " + reg_path
self.controller.show_warning(message,
10,
context_name='ui_message')
return (giswater_file_path, None)
giswater_build_version = major_version + '.' + minor_version + '.' + build_version
return (giswater_file_path, giswater_build_version)
def get_java_exe(self):
''' Get executable Java file from windows registry '''
reg_hkey = "HKEY_LOCAL_MACHINE"
reg_path = "SOFTWARE\\JavaSoft\\Java Runtime Environment"
reg_name = "CurrentVersion"
java_version = utils_giswater.get_reg(reg_hkey, reg_path, reg_name)
# Check if java version exists (64 bits)
if java_version is None:
reg_path = "SOFTWARE\\Wow6432Node\\JavaSoft\\Java Runtime Environment"
java_version = utils_giswater.get_reg(reg_hkey, reg_path, reg_name)
# Check if java version exists (32 bits)
if java_version is None:
message = "Cannot get current Java version from windows registry at: " + reg_path
self.controller.show_warning(message,
10,
context_name='ui_message')
return None
# Get java folder
reg_path += "\\" + java_version
reg_name = "JavaHome"
java_folder = utils_giswater.get_reg(reg_hkey, reg_path, reg_name)
if java_folder is None:
message = "Cannot get Java folder from windows registry at: " + reg_path
self.controller.show_warning(message,
10,
context_name='ui_message')
return None
# Check if java folder exists
if not os.path.exists(java_folder):
message = "Java folder not found at: " + java_folder
self.controller.show_warning(message,
10,
context_name='ui_message')
return None
# Check if java executable file exists
java_exe = java_folder + "/bin/java.exe"
if not os.path.exists(java_exe):
message = "Java executable file not found at: " + java_exe
self.controller.show_warning(message,
10,
context_name='ui_message')
return None
return java_exe
def execute_giswater(self, parameter, index_action):
''' Executes giswater with selected parameter '''
if self.giswater_file_path is None or self.java_exe is None:
return
# Check if gsw file exists. If not giswater will open with the last .gsw file
if self.gsw_file != "" and not os.path.exists(self.gsw_file):
message = "GSW file not found at: " + self.gsw_file
self.controller.show_info(message, 10, context_name='ui_message')
self.gsw_file = ""
# Start program
aux = '"' + self.giswater_file_path + '"'
if self.gsw_file != "":
aux += ' "' + self.gsw_file + '"'
program = [
self.java_exe, "-jar", self.giswater_file_path, self.gsw_file,
parameter
]
else:
program = [
self.java_exe, "-jar", self.giswater_file_path, "", parameter
]
self.controller.start_program(program)
# Compare Java and Plugin versions
if self.plugin_version <> self.giswater_build_version:
msg = "Giswater and plugin versions are different. \n"
msg += "Giswater version: " + self.giswater_build_version
msg += " - Plugin version: " + self.plugin_version
self.controller.show_info(msg, 10, context_name='ui_message')
# Show information message
else:
msg = "Executing... " + aux
self.controller.show_info(msg, context_name='ui_message')
def open_web_browser(self, widget):
""" Display url using the default browser """
url = utils_giswater.getWidgetText(widget)
if url == 'null':
url = 'www.giswater.org'
webbrowser.open(url)
def get_file_dialog(self, widget):
""" Get file dialog """
# Check if selected file exists. Set default value if necessary
file_path = utils_giswater.getWidgetText(widget)
if file_path is None or file_path == 'null' or not os.path.exists(
str(file_path)):
folder_path = self.plugin_dir
else:
folder_path = os.path.dirname(file_path)
# Open dialog to select file
os.chdir(folder_path)
file_dialog = QFileDialog()
file_dialog.setFileMode(QFileDialog.AnyFile)
msg = "Select file"
folder_path = file_dialog.getOpenFileName(
parent=None, caption=self.controller.tr(msg))
if folder_path:
utils_giswater.setWidgetText(widget, str(folder_path))
def get_folder_dialog(self, widget):
""" Get folder dialog """
# Check if selected folder exists. Set default value if necessary
folder_path = utils_giswater.getWidgetText(widget)
if folder_path is None or folder_path == 'null' or not os.path.exists(
folder_path):
folder_path = self.plugin_dir
# Open dialog to select folder
os.chdir(folder_path)
file_dialog = QFileDialog()
file_dialog.setFileMode(QFileDialog.Directory)
msg = "Select folder"
folder_path = file_dialog.getExistingDirectory(
parent=None, caption=self.controller.tr(msg))
if folder_path:
utils_giswater.setWidgetText(widget, str(folder_path))
def load_settings(self, dialog=None):
""" Load QGIS settings related with dialog position and size """
if dialog is None:
dialog = self.dlg
try:
width = self.controller.plugin_settings_value(
dialog.objectName() + "_width", dialog.width())
height = self.controller.plugin_settings_value(
dialog.objectName() + "_height", dialog.height())
x = self.controller.plugin_settings_value(dialog.objectName() +
"_x")
y = self.controller.plugin_settings_value(dialog.objectName() +
"_y")
if x < 0 or y < 0:
dialog.resize(width, height)
else:
dialog.setGeometry(x, y, width, height)
except:
pass
def save_settings(self, dialog=None):
""" Save QGIS settings related with dialog position and size """
if dialog is None:
dialog = self.dlg
self.controller.plugin_settings_set_value(
dialog.objectName() + "_width", dialog.width())
self.controller.plugin_settings_set_value(
dialog.objectName() + "_height", dialog.height())
self.controller.plugin_settings_set_value(dialog.objectName() + "_x",
dialog.pos().x())
self.controller.plugin_settings_set_value(dialog.objectName() + "_y",
dialog.pos().y())
def close_dialog(self, dlg=None):
""" Close dialog """
if dlg is None or type(dlg) is bool:
dlg = self.dlg
try:
self.save_settings(dlg)
dlg.close()
map_tool = self.canvas.mapTool()
# If selected map tool is from the plugin, set 'Pan' as current one
if map_tool.toolName() == '':
self.iface.actionPan().trigger()
except AttributeError:
pass
def multi_row_selector(self, dialog, tableleft, tableright, field_id_left,
field_id_right):
# fill QTableView all_rows
tbl_all_rows = dialog.findChild(QTableView, "all_rows")
tbl_all_rows.setSelectionBehavior(QAbstractItemView.SelectRows)
query_left = "SELECT * FROM " + self.schema_name + "." + tableleft + " WHERE name NOT IN "
query_left += "(SELECT name FROM " + self.schema_name + "." + tableleft
query_left += " RIGHT JOIN " + self.schema_name + "." + tableright + " ON " + tableleft + "." + field_id_left + " = " + tableright + "." + field_id_right
query_left += " WHERE cur_user = current_user)"
self.fill_table_by_query(tbl_all_rows, query_left)
self.hide_colums(tbl_all_rows,
[0, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15])
tbl_all_rows.setColumnWidth(1, 200)
# fill QTableView selected_rows
tbl_selected_rows = dialog.findChild(QTableView, "selected_rows")
tbl_selected_rows.setSelectionBehavior(QAbstractItemView.SelectRows)
query_right = "SELECT name, cur_user, " + tableleft + "." + field_id_left + ", " + tableright + "." + field_id_right + " FROM " + self.schema_name + "." + tableleft
query_right += " JOIN " + self.schema_name + "." + tableright + " ON " + tableleft + "." + field_id_left + " = " + tableright + "." + field_id_right
query_right += " WHERE cur_user = current_user"
self.fill_table_by_query(tbl_selected_rows, query_right)
self.hide_colums(tbl_selected_rows, [1, 2, 3])
tbl_selected_rows.setColumnWidth(0, 200)
# Button select
dialog.btn_select.pressed.connect(
partial(self.multi_rows_selector, tbl_all_rows, tbl_selected_rows,
field_id_left, tableright, "id", query_left, query_right,
field_id_right))
# Button unselect
query_delete = "DELETE FROM " + self.schema_name + "." + tableright
query_delete += " WHERE current_user = cur_user AND " + tableright + "." + field_id_right + "="
dialog.btn_unselect.pressed.connect(
partial(self.unselector, tbl_all_rows, tbl_selected_rows,
query_delete, query_left, query_right, field_id_right))
# QLineEdit
dialog.txt_name.textChanged.connect(
partial(self.query_like_widget_text, dialog.txt_name, tbl_all_rows,
tableleft, tableright, field_id_right))
def hide_colums(self, widget, comuns_to_hide):
for i in range(0, len(comuns_to_hide)):
widget.hideColumn(comuns_to_hide[i])
def unselector(self, qtable_left, qtable_right, query_delete, query_left,
query_right, field_id_right):
""" """
selected_list = qtable_right.selectionModel().selectedRows()
if len(selected_list) == 0:
message = "Any record selected"
self.controller.show_warning(message, context_name='ui_message')
return
expl_id = []
for i in range(0, len(selected_list)):
row = selected_list[i].row()
id_ = str(qtable_right.model().record(row).value(field_id_right))
expl_id.append(id_)
for i in range(0, len(expl_id)):
self.controller.execute_sql(query_delete + str(expl_id[i]))
# Refresh
self.fill_table_by_query(qtable_left, query_left)
self.fill_table_by_query(qtable_right, query_right)
self.refresh_map_canvas()
def multi_rows_selector(self, qtable_left, qtable_right, id_ori,
tablename_des, id_des, query_left, query_right,
field_id):
"""
:param qtable_left: QTableView origin
:param qtable_right: QTableView destini
:param id_ori: Refers to the id of the source table
:param tablename_des: table destini
:param id_des: Refers to the id of the target table, on which the query will be made
:param query_right:
:param query_left:
:param field_id:
"""
selected_list = qtable_left.selectionModel().selectedRows()
if len(selected_list) == 0:
message = "Any record selected"
self.controller.show_warning(message, context_name='ui_message')
return
expl_id = []
curuser_list = []
for i in range(0, len(selected_list)):
row = selected_list[i].row()
id_ = qtable_left.model().record(row).value(id_ori)
expl_id.append(id_)
curuser = qtable_left.model().record(row).value("cur_user")
curuser_list.append(curuser)
for i in range(0, len(expl_id)):
# Check if expl_id already exists in expl_selector
sql = "SELECT DISTINCT(" + id_des + ", cur_user)"
sql += " FROM " + self.schema_name + "." + tablename_des
sql += " WHERE " + id_des + " = '" + str(expl_id[i])
row = self.dao.get_row(sql)
if row:
# if exist - show warning
self.controller.show_info_box(
"Id " + str(expl_id[i]) + " is already selected!", "Info")
else:
sql = 'INSERT INTO ' + self.schema_name + '.' + tablename_des + ' (' + field_id + ', cur_user) '
sql += " VALUES (" + str(expl_id[i]) + ", current_user)"
self.controller.execute_sql(sql)
# Refresh
self.fill_table_by_query(qtable_right, query_right)
self.fill_table_by_query(qtable_left, query_left)
self.refresh_map_canvas()
def fill_table_psector(self, widget, table_name, column_id):
""" Set a model with selected filter.
Attach that model to selected table """
# Set model
self.model = QSqlTableModel()
self.model.setTable(self.schema_name + "." + table_name)
self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.model.setSort(0, 0)
self.model.select()
# Check for errors
if self.model.lastError().isValid():
self.controller.show_warning(self.model.lastError().text())
# Attach model to table view
widget.setModel(self.model)
# put combobox in qtableview
sql = "SELECT * FROM " + self.schema_name + "." + table_name + " ORDER BY " + column_id
rows = self.controller.get_rows(sql)
for x in range(len(rows)):
combo = QComboBox()
sql = "SELECT DISTINCT(priority) FROM " + self.schema_name + "." + table_name
row = self.controller.get_rows(sql)
utils_giswater.fillComboBox(combo, row, False)
row = rows[x]
priority = row[4]
utils_giswater.setSelectedItem(combo, str(priority))
i = widget.model().index(x, 4)
widget.setIndexWidget(i, combo)
#combo.setStyleSheet("background:#F2F2F2")
combo.setStyleSheet("background:#E6E6E6")
combo.currentIndexChanged.connect(
partial(self.update_combobox_values, widget, combo, x))
def update_combobox_values(self, widget, combo, x):
""" Insert combobox.currentText into widget (QTableView) """
index = widget.model().index(x, 4)
widget.model().setData(index, combo.currentText())
def save_table(self, widget, table_name, column_id):
""" Save widget (QTableView) into model"""
if self.model.submitAll():
self.model.database().commit()
else:
self.model.database().rollback()
self.fill_table_psector(widget, table_name, column_id)
def fill_table(self, widget, table_name):
""" Set a model with selected filter.
Attach that model to selected table """
# Set model
self.model = QSqlTableModel()
self.model.setTable(table_name)
self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.model.setSort(0, 0)
self.model.select()
# Check for errors
if self.model.lastError().isValid():
self.controller.show_warning(self.model.lastError().text())
# Attach model to table view
widget.setModel(self.model)
def fill_table_by_query(self, qtable, query):
"""
:param qtable: QTableView to show
:param query: query to set model
"""
model = QSqlQueryModel()
model.setQuery(query)
qtable.setModel(model)
qtable.show()
# Check for errors
if model.lastError().isValid():
self.controller.show_warning(model.lastError().text())
def query_like_widget_text(self, text_line, qtable, tableleft, tableright,
field_id):
""" Fill the QTableView by filtering through the QLineEdit"""
query = text_line.text()
sql = "SELECT * FROM " + self.schema_name + "." + tableleft + " WHERE name NOT IN "
sql += "(SELECT name FROM " + self.schema_name + "." + tableleft
sql += " RIGHT JOIN " + self.schema_name + "." + tableright + " ON " + tableleft + "." + field_id + " = " + tableright + "." + field_id
sql += " WHERE cur_user = current_user) AND name LIKE '%" + query + "%'"
self.fill_table_by_query(qtable, sql)
def set_icon(self, widget, icon):
""" Set @icon to selected @widget """
# Get icons folder
icons_folder = os.path.join(self.plugin_dir, 'icons')
icon_path = os.path.join(icons_folder, str(icon) + ".png")
if os.path.exists(icon_path):
widget.setIcon(QIcon(icon_path))
else:
self.controller.log_info("File not found", parameter=icon_path)
def refresh_map_canvas(self):
""" Refresh all layers present in map canvas """
self.canvas.refreshAllLayers()
for layer_refresh in self.canvas.layers():
layer_refresh.triggerRepaint()
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"
sql += " FROM " + self.schema_name + ".config_client_forms"
sql += " WHERE table_id = '" + table_name + "'"
sql += " 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)
