def data(self, item, role):
if role == Qt.ForegroundRole:
if QSqlQueryModel.data(self, self.index(item.row(), 2),
Qt.DisplayRole) == 'Sell':
return QColor(27, 120, 55)
else:
return QColor(227, 26, 28)
if role == Qt.BackgroundRole:
return QColor(217, 217, 217)
return QSqlQueryModel.data(self, item, role)
def data(self, item, role):
if role == Qt.BackgroundRole:
if QSqlQueryModel.data(self, self.index(item.row(), 3),
Qt.DisplayRole):
return QBrush(Qt.yellow)
if role == Qt.DisplayRole:
if item.column() == 3:
return True if QSqlQueryModel.data(
self, item, Qt.DisplayRole) == 1 else False
return QSqlQueryModel.data(self, item, role)
def data(self, item, role):
if role == Qt.BackgroundRole:
if QSqlQueryModel.data(self, self.index(item.row(), 4),
Qt.DisplayRole) == 0:
return QBrush(Qt.red)
elif QSqlQueryModel.data(self, self.index(item.row(), 4),
Qt.DisplayRole) == 1:
return QBrush(Qt.yellow)
elif QSqlQueryModel.data(self, self.index(item.row(), 4),
Qt.DisplayRole) == 2:
return QBrush(Qt.green)
return QSqlQueryModel.data(self, item, role)
def data(self, index, role=QtCore.Qt.DisplayRole):
if not index.isValid():
return QSqlQueryModel.data(self, index, role)
column = index.column()
row = index.row()
if role == QtCore.Qt.TextAlignmentRole:
return QtCore.Qt.AlignCenter
if role == QtCore.Qt.TextColorRole:
for _, what in enumerate(self._model_data):
d = QSqlQueryModel.data(
self, self.index(row, self._model_data[what][1]),
QtCore.Qt.DisplayRole)
if column == self._model_data[what][1] and what in d:
return self._model_data[what][0]
return QSqlQueryModel.data(self, index, role)
class QueryDialog(QDialog):
"""a dialog to choose an item from a query
"""
choice = pyqtSignal(str)
def __init__(self, query):
super().__init__()
self.query = query
self.create_model()
self.init_UI()
def create_model(self):
"""creates the model as QSqlQueryModel,
using the given query
"""
self.model = QSqlQueryModel()
q = QSqlQuery()
q.exec_(self.query)
self.model.setQuery(q)
def init_UI(self):
"""setup the UI
"""
layout = QVBoxLayout()
self.setLayout(layout)
self.resize(200,200)
self.title = "Choose an existing project"
self.list = QListView(self)
layout.addWidget(self.list)
self.list.setModel(self.model)
self.list.setWhatsThis("Choose a project by clicking on it")
self.btn = QPushButton("Accept", self)
layout.addWidget(self.btn)
self.btn.clicked.connect(self.on_btn_clicked)
self.btn.setWhatsThis("Click here to accept your selection (works only if a project has been selected)")
def on_btn_clicked(self):
"""when self.btn is clicked, accept the choice and emit it as self.choice
"""
selected = self.list.selectedIndexes()
if selected:
index = selected[0]
chosen = self.model.data(index, Qt.DisplayRole)
self.choice.emit(chosen)
self.close()
self.choice.emit("")
self.close()
def write_csv(model: QtSql.QSqlQueryModel):
filename, _ = QFileDialog \
.getSaveFileName(None, "Export data to CSV",
".",
CSV_FILE_FILTER, CSV_FILE_FILTER)
data = list()
with open(filename, "a") as f:
# process headers
for i in range(0, model.columnCount()):
header = str(
model.headerData(i, QtCore.Qt.Horizontal,
QtCore.Qt.DisplayRole))
data.append('"' + header + '"')
# write headers
f.write(";".join(data))
f.write("\n")
# process rows
for i in range(model.rowCount()):
data.clear()
for j in range(model.columnCount()):
cell = str(model.data(model.index(i, j)))
data.append('"' + cell + '"')
f.write(";".join(data))
f.write("\n")
class DatabaseSearchWidget(QWidget, Ui_DatabaseSearchWidget):
# Signal to be emitted when user wants item to be added
signal_add_item = QtCore.pyqtSignal(int)
# Signal to be emitted when user selection changes
signal_current_selection = QtCore.pyqtSignal(int)
def __init__(self, queries, headers, parent=None):
super(DatabaseSearchWidget, self).__init__(parent)
self.setupUi(self)
# Setup database connection
self.database = pyqt_database_connection()
self.database.open()
self.databaseModel = QSqlQueryModel(self)
self.dataView_search_results.setModel(self.databaseModel)
# Store bound queries for usage
self.queries = queries
self.combo_search_options.addItems(sorted(queries.keys()))
# Connect signals
self.dataView_search_results.selectionModel().selectionChanged.connect(
self.slot_emit_selection_changed)
self.dataView_search_results.doubleClicked.connect(
self.slot_emit_add_item)
self.pushButton_search.clicked.connect(self.update_query)
self.combo_search_options.currentIndexChanged.connect(
self.update_query)
# Setup header
for element in headers:
self.databaseModel.setHeaderData(0, QtCore.Qt.Horizontal, element)
self.installEventFilter(self)
@QtCore.pyqtSlot()
def update_query(self):
""" Update the query with the search term
Returns
-------
None
"""
LOGGER.debug("Updating search query..")
query = self.queries[self.combo_search_options.currentText()]
# Todo: Slow query setting freezes ui
self.databaseModel.setQuery(
query.format(input=self.lineEdit_search_input.text().strip()))
LOGGER.debug(str(self.databaseModel.query().executedQuery()))
LOGGER.debug("Search complete.")
def selected_id(self):
""" Return the id of the currently selected item
Returns
-------
int
"""
selection = self.dataView_search_results.get_selected_rows()
if len(selection) != 1:
return -1
else:
return int(
self.databaseModel.data(
self.databaseModel.index(selection[0], 0)))
def eventFilter(self, source, event):
if event.type() == QtCore.QEvent.KeyPress and event.key() in (
QtCore.Qt.Key_Return, QtCore.Qt.Key_Enter):
self.update_query()
return True
return False
@QtCore.pyqtSlot()
def slot_emit_selection_changed(self):
""" Emit database id of the currently selected item
Returns
-------
None
"""
self.signal_current_selection.emit(self.selected_id())
@QtCore.pyqtSlot()
def slot_emit_add_item(self):
""" Emit database id of the currently selected item
Returns
-------
None
"""
self.signal_add_item.emit(self.selected_id())
class RecordsViewer(QDialog):
def __init__(self):
super(RecordsViewer, self).__init__()
self.resize(800, 500)
self.setWindowTitle('myOCR——我的识别历史')
self.setWindowIcon(QIcon('./images/icon.png'))
self.queryModel = None
self.tableView = None
self.currentPage = 0
self.totalPage = 0
self.totalRecord = 0
self.pageRecord = 10
self.initUI()
def initUI(self):
self.vbox = QVBoxLayout()
self.h1box = QHBoxLayout()
self.h2box = QHBoxLayout()
self.searchEdit = QLineEdit()
self.searchEdit.setFixedHeight(32)
self.searchEdit.setFont(QFont("苏新诗柳楷繁", 15))
self.searchButton = QPushButton("查询")
self.searchButton.setFixedHeight(32)
self.searchButton.setFont(QFont("苏新诗柳楷繁", 15))
self.condisionComboBox = QComboBox()
searchCondision = ['按识别编号查询', '按识别时间查询', '按识别类型查询', '按识别文字查询']
self.condisionComboBox.setFixedHeight(32)
self.condisionComboBox.setFont(QFont("苏新诗柳楷繁", 15))
self.condisionComboBox.addItems(searchCondision)
self.h1box.addWidget(self.searchEdit)
self.h1box.addWidget(self.condisionComboBox)
self.h1box.addWidget(self.searchButton)
self.jumpToLabel = QLabel(self)
self.jumpToLabel.setText("跳转到第")
self.jumpToLabel.setFont(QFont("苏新诗柳楷繁", 12))
self.jumpToLabel.setFixedWidth(90)
self.pageEdit = QLineEdit()
self.pageEdit.setFixedWidth(30)
self.pageEdit.setFont(QFont("苏新诗柳楷繁", 12))
s = "/" + str(self.totalPage) + "页"
self.pageLabel = QLabel(s)
self.pageLabel.setFont(QFont("苏新诗柳楷繁", 12))
self.pageLabel.setFixedWidth(40)
self.jumpToButton = QPushButton(self)
self.jumpToButton.setText("跳转")
self.jumpToButton.setFont(QFont("苏新诗柳楷繁", 12))
self.jumpToButton.setFixedHeight(30)
self.jumpToButton.setFixedWidth(60)
self.prevButton = QPushButton("前一页")
self.prevButton.setFont(QFont("苏新诗柳楷繁", 12))
self.prevButton.setFixedHeight(30)
self.prevButton.setFixedWidth(80)
self.backButton = QPushButton("后一页")
self.backButton.setFont(QFont("苏新诗柳楷繁", 12))
self.backButton.setFixedHeight(30)
self.backButton.setFixedWidth(80)
self.detailbutton = QPushButton(self)
self.detailbutton.setText("详细信息")
self.detailbutton.setFixedWidth(90)
self.detailbutton.setFont(QFont("苏新诗柳楷繁", 12))
self.detailbutton.clicked.connect(self.detailInfo)
self.hbox = QHBoxLayout()
self.hbox.addWidget(self.jumpToLabel)
self.hbox.addWidget(self.pageEdit)
self.hbox.addWidget(self.pageLabel)
self.hbox.addWidget(self.jumpToButton)
self.hbox.addStretch(1)
self.hbox.addWidget(self.detailbutton)
self.hbox.addStretch(1)
self.hbox.addWidget(self.prevButton)
self.hbox.addWidget(self.backButton)
widget = QWidget()
widget.setLayout(self.hbox)
widget.setFixedWidth(600)
self.h2box.addWidget(widget)
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName('./db/myOCR.db')
self.db.open()
self.tableView = QTableView()
self.tableView.horizontalHeader().setStretchLastSection(True)
self.tableView.setEditTriggers(QAbstractItemView.NoEditTriggers)
self.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)
self.tableView.setFont(QFont("苏新诗柳楷繁", 12))
self.tableView.horizontalHeader().setFont(QFont("苏新诗柳楷繁", 12))
self.queryModel = QSqlQueryModel()
self.searchButtonClicked()
self.tableView.setModel(self.queryModel)
self.queryModel.setHeaderData(0, Qt.Horizontal, "识别编号")
self.queryModel.setHeaderData(1, Qt.Horizontal, "识别时间")
self.queryModel.setHeaderData(2, Qt.Horizontal, "识别类型")
self.queryModel.setHeaderData(3, Qt.Horizontal, "识别文字")
self.vbox.addLayout(self.h1box)
self.vbox.addWidget(self.tableView)
self.vbox.addLayout(self.h2box)
self.setLayout(self.vbox)
self.searchButton.clicked.connect(self.searchButtonClicked)
self.prevButton.clicked.connect(self.prevButtonClicked)
self.backButton.clicked.connect(self.backButtonClicked)
self.jumpToButton.clicked.connect(self.jumpToButtonClicked)
self.searchEdit.returnPressed.connect(self.searchButtonClicked)
def setButtonStatus(self):
if (self.currentPage == self.totalPage):
self.prevButton.setEnabled(True)
self.backButton.setEnabled(False)
if (self.currentPage == 1):
self.backButton.setEnabled(True)
self.prevButton.setEnabled(False)
if (self.currentPage < self.totalPage and self.currentPage > 1):
self.prevButton.setEnabled(True)
self.backButton.setEnabled(True)
def getTotalRecordCount(self):
self.queryModel.setQuery(
"select RecordId,RecordTime,RecordType,RecordText from records")
self.totalRecord = self.queryModel.rowCount()
return
def getPageCount(self):
self.getTotalRecordCount()
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
return
def recordQuery(self, index):
conditionChoice = self.condisionComboBox.currentText()
if (conditionChoice == "按识别编号查询"):
conditionChoice = 'RecordId'
elif (conditionChoice == "按识别时间查询"):
conditionChoice = 'RecordTime'
elif (conditionChoice == "按识别类型查询"):
conditionChoice = 'RecordType'
else:
conditionChoice = 'RecordText'
if (self.searchEdit.text() == ""):
queryCondition = "select RecordId,RecordTime,RecordType,RecordText from records order by RecordTime DESC"
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
queryCondition = (
"select RecordId,RecordTime,RecordType,RecordText from records order by %s desc limit %d,%d "
% (conditionChoice, index, self.pageRecord))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
temp = self.searchEdit.text()
s = '%'
for i in range(0, len(temp)):
s = s + temp[i] + "%"
queryCondition = (
"select RecordId,RecordTime,RecordType,RecordText from records where %s like '%s' order by %s desc"
% (conditionChoice, s, conditionChoice))
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
if (self.totalRecord == 0):
print(
QMessageBox.information(self, "提醒", "查询无记录", QMessageBox.Yes,
QMessageBox.Yes))
queryCondition = "select RecordId,RecordTime,RecordType,RecordText from records order by RecordTime DESC"
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
queryCondition = (
"select RecordId,RecordTime,RecordType,RecordText from records order by %s desc limit %d,%d "
% (conditionChoice, index, self.pageRecord))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
queryCondition = (
"select RecordId,RecordTime,RecordType,RecordText from records where %s like '%s' order by %s desc limit %d,%d "
% (conditionChoice, s, conditionChoice, index, self.pageRecord))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
def searchButtonClicked(self):
self.currentPage = 1
self.pageEdit.setText(str(self.currentPage))
self.getPageCount()
s = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(s)
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
def prevButtonClicked(self):
self.currentPage -= 1
if (self.currentPage <= 1):
self.currentPage = 1
self.pageEdit.setText(str(self.currentPage))
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
def backButtonClicked(self):
self.currentPage += 1
if (self.currentPage >= int(self.totalPage)):
self.currentPage = int(self.totalPage)
self.pageEdit.setText(str(self.currentPage))
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
def jumpToButtonClicked(self):
if (self.pageEdit.text().isdigit()):
self.currentPage = int(self.pageEdit.text())
if (self.currentPage > self.totalPage):
self.currentPage = self.totalPage
if (self.currentPage <= 1):
self.currentPage = 1
else:
self.currentPage = 1
index = (self.currentPage - 1) * self.pageRecord
self.pageEdit.setText(str(self.currentPage))
self.recordQuery(index)
return
def detailInfo(self):
index_ = self.tableView.currentIndex().row()
if (index_ == -1):
print(
QMessageBox.warning(self, "警告", "您没有选中任何记录", QMessageBox.Yes,
QMessageBox.Yes))
return
else:
str = self.queryModel.data(self.queryModel.index(index_, 0))
type = self.queryModel.data(self.queryModel.index(index_, 2))
if (type == '身份证识别'):
self.recordDetail_id(str)
else:
self.recordDetail(str)
def recordDetail(self, RecordId):
recorddetaildialog = RecordDetailDialog(RecordId)
recorddetaildialog.show()
recorddetaildialog.exec_()
def recordDetail_id(self, RecordId):
recorddetail_id = RecordDetailDialog_id(RecordId)
recorddetail_id.show()
recorddetail_id.exec_()
class Window(QMainWindow, Ui_MainWindow):
def __init__(self):
super().__init__()
self.setupUi(self)
self.model = QSqlQueryModel()
self.connectToSql()
# Обработка действий по меню
# нажата кнопка меню "Справочники -> Подразделения"
self.unit_action.triggered.connect(self.unit_dialog)
# нажата кнопка меню "Справочники -> Сотрудники"
self.user_action.triggered.connect(self.user_dialog)
# нажата кнопка меню "Справочники -> Сервисные службы"
self.services_action.triggered.connect(self.services_dialog)
# Работа с основной формой
# Нажата кнопка "Добавить строку" на форме
self.btProblemAdd.clicked.connect(self.bt_add)
# Нажата кнопка "Обновить строку" на форме
self.btProblemUpdate.clicked.connect(self.bt_upd)
# Нажата кнопка "Удалить строку" на форме
self.btProblemDel.clicked.connect(self.bt_del)
# Найти заявку
self.btProblemFind.clicked.connect(self.findText)
# Сбросить фильтр
self.btProblemReset.clicked.connect(self.reset)
def connectToSql(self):
"""
Открываем базу и создаем запрос и соединяем модель с таблицей
"""
self.db = QtSql.QSqlDatabase.addDatabase(
"QSQLITE") # создаём подключение
self.db.setDatabaseName("ProblemDB.db")
if self.db.open():
self.qry = QSqlQuery(self.db)
self.qry.prepare(
"""SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d
Left Join services s
on d.idServices = s.id
left join User u
on d.idUser = u.id""")
self.qry.exec()
self.model.setQuery(self.qry)
self.tableView.setModel(self.model)
self.tableView.horizontalHeader().setSectionResizeMode(
QHeaderView.ResizeToContents)
self.model.setHeaderData(0, Qt.Horizontal, '№ заявки')
self.model.setHeaderData(1, Qt.Horizontal, 'Дата')
self.model.setHeaderData(2, Qt.Horizontal, 'Служба')
self.model.setHeaderData(3, Qt.Horizontal, 'Текст заявки')
self.model.setHeaderData(4, Qt.Horizontal, 'ФИО заявителя')
else:
QMessageBox.critical(self, 'error', self.model.lastError().text())
def bt_add(self):
"""
Вызывается по нажатию кнопки "Добавить строку"
"""
dialog_add = Ed_win(self)
# Устанавливаем пустые значения в поля на форме
dialog_add.edit_text.setText = ''
dialog_add.cb_ser_name.setCurrentIndex(-1)
dialog_add.cb_fio.setCurrentIndex(-1)
# открываем окно добавления
dialog_add.show()
# Если нажата кнопка ОК на форме добавления
if dialog_add.exec() == QDialog.Accepted:
ser = dialog_add.cb_ser_name.currentText()
user = dialog_add.cb_fio.currentText()
self.db = QtSql.QSqlDatabase.addDatabase(
"QSQLITE") # создаём подключение
self.db.setDatabaseName("ProblemDB.db")
if self.db.open():
qry1 = QSqlQuery(self.db)
qry1.prepare("""SELECT s.id FROM services s
where s.name =:ser""")
qry1.bindValue(":ser", ser)
qry1.exec()
qry1.next()
servId = qry1.value(0)
qry1.prepare("""SELECT u.id FROM User u
where u.FIO =:user""")
qry1.bindValue(":user", user)
qry1.exec()
qry1.next()
userId = qry1.value(0)
qry1.prepare(
"""INSERT INTO def_message(idServices, text,idUser)
VALUES(:idService, :text,:idUser)""")
qry1.bindValue(":idService", servId)
qry1.bindValue(":text", dialog_add.edit_text.text())
qry1.bindValue(":idUser", userId)
qry1.exec()
self.qry = QSqlQuery(self.db)
self.qry.prepare("""SELECT d.id, dat, s.name, d.text, u.FIO
FROM def_message d
Left Join services s
on d.idServices = s.id
left join User u
on d.idUser = u.id""")
self.qry.exec()
self.model.setQuery(self.qry)
self.tableView.setModel(self.model)
else:
QMessageBox.critical(self, 'error',
self.model.lastError().text())
def bt_upd(self):
"""
Вызывается по нажатию кнопки "Изменить строку"
"""
dialog_add = Ed_win(self)
# определяем индекс строки в таблице
index = self.tableView.currentIndex().row()
print(index)
if index == -1:
QMessageBox.information(self, 'Ошибка', 'Строка не выбрана')
else:
# определяем id строки в таблице в базе данных
nom = str(
self.model.data(self.model.index(index, 0), Qt.DisplayRole))
# устанавливаем текст заявки в поле edit_text
dialog_add.edit_text.setText(
str(self.model.data(self.model.index(index, 3),
Qt.DisplayRole)))
# Данные по сервисной службе
index_serv = dialog_add.cb_ser_name.findText(
str(self.model.data(self.model.index(index, 2),
Qt.DisplayRole)))
if index_serv > -1:
dialog_add.cb_ser_name.setCurrentIndex(index_serv)
else:
dialog_add.cb_ser_name.setCurrentIndex(-1)
# Данные по сотруднику, который подал заявку
index_fio = dialog_add.cb_fio.findText(
str(self.model.data(self.model.index(index, 4),
Qt.DisplayRole)))
if index_fio > -1:
dialog_add.cb_fio.setCurrentIndex(index_fio)
else:
dialog_add.cb_fio.setCurrentIndex(-1)
# ОТкрываем окно с данными для редактирования
dialog_add.show()
# если нажата кнопка ОК в окне для редактирования строки
if dialog_add.exec() == QDialog.Accepted:
ser = dialog_add.cb_ser_name.currentText()
user = dialog_add.cb_fio.currentText()
self.db = QtSql.QSqlDatabase.addDatabase(
"QSQLITE") # создаём подключение
self.db.setDatabaseName("ProblemDB.db")
if self.db.open():
qry1 = QSqlQuery(self.db)
qry1.prepare("""SELECT s.id FROM services s
where s.name =:ser""")
qry1.bindValue(":ser", ser)
qry1.exec()
qry1.next()
servId = qry1.value(0)
qry1.prepare("""SELECT u.id FROM User u
where u.FIO =:user""")
qry1.bindValue(":user", user)
qry1.exec()
qry1.next()
userId = qry1.value(0)
qry1.prepare(
"""UPDATE def_message SET idServices=:servId, text=:txt,idUser=:userId WHERE id=:id"""
)
qry1.bindValue(":servId", servId)
qry1.bindValue(":txt", dialog_add.edit_text.text())
qry1.bindValue(":userId", userId)
qry1.bindValue(":id", nom)
qry1.exec()
# снова делаем выборку данных после проведения обновления данных
self.qry = QSqlQuery(self.db)
self.qry.prepare(
"""SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d
Left Join services s
on d.idServices = s.id
left join User u
on d.idUser = u.id""")
self.qry.exec()
self.model.setQuery(self.qry)
self.tableView.setModel(self.model)
else:
QMessageBox.critical(self, 'error',
self.model.lastError().text())
def bt_del(self):
"""
Вызывается по нажатию кнопки "Удалить строку"
"""
quit_msg = "Подтвердите удаление записи"
reply = QMessageBox.question(self, 'Confirm', quit_msg,
QMessageBox.Yes, QMessageBox.No)
if reply == QMessageBox.Yes:
index = self.tableView.currentIndex().row()
nom = str(
self.model.data(self.model.index(index, 0), Qt.DisplayRole))
self.db = QtSql.QSqlDatabase.addDatabase(
"QSQLITE") # создаём подключение
self.db.setDatabaseName("ProblemDB.db")
if self.db.open():
qry1 = QSqlQuery(self.db)
qry1.prepare("""DELETE FROM def_message
where id =:id""")
qry1.bindValue(":id", nom)
qry1.exec()
self.qry = QSqlQuery(self.db)
self.qry.prepare(
"""SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d
Left Join services s
on d.idServices = s.id
left join User u
on d.idUser = u.id"""
)
self.qry.exec()
self.model.setQuery(self.qry)
self.tableView.setModel(self.model)
else:
QMessageBox.critical(self, 'error',
self.model.lastError().text())
def findText(self):
"""
Вызывается по кнопке "Найти заявку"
"""
self.db = QtSql.QSqlDatabase.addDatabase(
"QSQLITE") # создаём подключение
self.db.setDatabaseName("ProblemDB.db")
if self.db.open():
self.qry = QSqlQuery(self.db)
self.qry.prepare(
"""SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d
Left Join services s
on d.idServices = s.id
left join User u
on d.idUser = u.id where d.text Like :txt """)
self.qry.bindValue(":txt", '%' + self.EditFind.text() + '%')
self.qry.exec()
self.model.setQuery(self.qry)
self.tableView.setModel(self.model)
else:
QMessageBox.critical(self, 'error', self.model.lastError().text())
def reset(self):
"""
Вызывается по кнопке "Сбросить фильтр"
"""
self.db = QtSql.QSqlDatabase.addDatabase(
"QSQLITE") # создаём подключение
self.db.setDatabaseName("ProblemDB.db")
if self.db.open():
self.qry = QSqlQuery(self.db)
self.qry.prepare(
"""SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d
Left Join services s
on d.idServices = s.id
left join User u
on d.idUser = u.id """)
self.qry.exec()
self.model.setQuery(self.qry)
self.tableView.setModel(self.model)
else:
QMessageBox.critical(self, 'error', self.model.lastError().text())
def unit_dialog(self):
"""
Функция выполняется при нажатии кнопки меню "Справочники -> Подразделения"
:return: Открывает окно с таблицей информации по подразделениям (Unit_win)
"""
dialog_unit = Unit_win(self)
dialog_unit.show()
# Скрываем основное окно программы
wnd.hide()
def user_dialog(self):
"""
Функция выполняется при нажатии кнопки меню "Справочники -> Сотрудники"
:return: Открывает окно с таблицей информации по отрудникам (User_win)
"""
dialog_user = User_win(self)
dialog_user.show()
# Скрываем основное окно программы
wnd.hide()
def services_dialog(self):
"""
Функция выполняется при нажатии кнопки меню "Справочники -> Сервисные службы"
:return: Открывает окно с таблицей информации по сервисным службам (Services_win)
"""
dialog = Services_win(self)
dialog.show()
# Скрываем основное окно программы
wnd.hide()
class FileToSQL(QMainWindow):
def __init__(self):
super(FileToSQL, self).__init__()
uic.loadUi('pot.ui', self)
self.setWindowTitle('Hugh Chungus')
self.load_param()
self.thread = File2SQLThread(self.param)
self.progress_bar = self.findChild(QProgressBar, 'progressBar')
self.sql_table = self.findChild(PotTableRightClick, 'sql_table')
self.sql_table.open_location_signal.connect(self.open_in_explorer)
self.sql_table.doubleClicked.connect(self.playlist)
self.sql_table.clicked.connect(self.launched_notif)
self.sql_table.setSelectionBehavior(QTableView.SelectRows)
self.queryModel = QSqlQueryModel()
self.sql_table.setModel(self.queryModel)
self.log_textedit = self.findChild(QTextEdit, 'log_textedit')
self.random_check = self.findChild(QCheckBox, 'random_check')
self.sql_line = QLineEdit(self)
self.query_button = self.findChild(QPushButton, 'query_button')
self.query_button.clicked.connect(self.query)
self.playlist_button = self.findChild(QPushButton, 'playlist_button')
self.playlist_button.clicked.connect(self.playlist)
self.tasksMenu = self.menuBar().addMenu('&Tasks')
self.ins_menu_action('Set Path', self.set_path, self.tasksMenu)
self.ins_menu_action('Scan Path', self.scan_db, self.tasksMenu)
self.combobox = self.findChild(QComboBox, 'comboBox')
self.combobox.addItems([
'',
'ORDER BY ctime DESC',
# 'SELECT DISTINCT a.path AS path1, b.path AS path2, a.size FROM dedup AS a, dedup as b WHERE a.size == b.size AND a.path != b.path;',
'SELECT DISTINCT a.path AS path1, b.path AS path2, a.size FROM dedup AS a JOIN dedup as b ON a.size == b.size AND a.path != b.path',
])
self.combobox.setLineEdit(self.sql_line)
self.column_combobox = self.findChild(QComboBox, 'column_combobox')
self.tablename_combobox = self.findChild(QComboBox,
'tablename_comboBox')
self.final_query_lineedit = self.findChild(QLineEdit,
'final_query_lineEdit')
self.tablename_combobox.addItems([
'media_path',
'dedup',
'path_tags',
])
self.tablename_combobox.setCurrentIndex(0)
#textChanged(str)
self.tablename_combobox.editTextChanged.connect(self.guess_query)
self.column_combobox.editTextChanged.connect(self.guess_query)
self.tablename_combobox.currentIndexChanged.connect(
self.update_column_selection)
self.sql_line.textChanged.connect(self.guess_query)
self.show()
self.refresh_view()
self.thread.tableview_refresh.connect(self.refresh_view)
self.thread.progressbar_update.connect(self.progress_bar_update)
self.thread.log_update.connect(self.log_update)
self.update_column_selection()
self.guess_query()
def update_column_selection(self, index=0):
self.column_combobox.clear()
table = self.tablename_combobox.currentText()
if table == 'media_path':
self.column_combobox.addItems([
'name, path',
'name',
'path',
'ctime',
])
elif table == 'dedup':
self.column_combobox.addItems([
'path',
'size',
'key1',
'key2',
])
elif table == 'path_tags':
self.column_combobox.addItems([
'path',
'class',
'value',
])
self.guess_query()
def log_update(self, text):
self.log_textedit.insertPlainText(text + '\n')
def open_in_explorer(self, index):
#print(r'explorer /select,"{}"'.format(self.get_data(index, 1)))
subprocess.Popen(r'explorer /select,"{}"'.format(
self.get_data(index, 1)))
def launched_notif(self):
self.progress_bar_update(0)
def refresh_view(self, query=None):
if query:
self.set_query(query)
else:
self.set_query(self.thread.SELECT_MEDIA_PATH_SQL)
self.resize_column()
def progress_bar_update(self, i):
self.progress_bar.setValue(int(i))
def resize_column(self):
self.sql_table.setColumnWidth(
self.thread.MEDIA_PATH_SQL_COL_NUM['name'], self.sql_table.width())
def resizeEvent(self, event):
self.resize_column()
def set_query(self, query):
self.queryModel.setQuery(query, self.thread.database())
def ins_menu_action(self, name, func, menu):
action = QAction(name, self)
action.triggered.connect(func)
menu.addAction(action)
def set_path(self):
self.gc_placeholder = VertTextDiag(self.param)
def scan_db(self):
self.thread.start()
def query(self):
self.refresh_view(self.guess_query())
def guess_query(self, *args): #self.final_query_lineedit
string = ''
after_where = self.sql_line.text()
b4_where = self.tablename_combobox.currentText()
columns = self.column_combobox.currentText()
if columns == '':
columns = '*'
#logic of guess query: if b4where == '', if after where doesnt start by SELECT then return nothing; else retunr after where
#if len(b4_where) == 0:
if after_where.find("SELECT") == 0:
string = after_where
elif len(
b4_where) > 0: #then string starts with SELECT * FROM b4_where
string = "SELECT {} FROM {}".format(columns, b4_where)
if len(after_where) > 0:
if after_where.find('ORDER BY') != -1 or after_where.find(
'WHERE') != -1:
string += ' ' + after_where
else:
string += ' WHERE name LIKE "%{}%";'.format(after_where)
if string[-1] != ';':
string += ';'
self.final_query_lineedit.setText(string)
return string
if len(string) == 0:
return string
if not string.find('ORDER BY') == -1:
return 'SELECT * FROM media_path ' + string
elif string.find("SELECT") == -1:
return 'SELECT * FROM media_path WHERE name LIKE "%{}%"'.format(
string)
return string
def keyPressEvent(self, e):
if (e.key() == QtCore.Qt.Key_Return or e.key() == QtCore.Qt.Key_Enter):
self.query()
def playlist(self):
play = None
if self.get_current_row() > -1:
play = self.get_current_data(
self.thread.MEDIA_PATH_SQL_COL_NUM['path'])
make_potplayer_playlist(self.tableview_to_list(), play)
subprocess.Popen([
r'C:\Program Files\DAUM\PotPlayer\potplayermini64.exe',
r'{}\playlist.dpl'.format(os.getcwd())
])
self.progress_bar_update(100)
def get_current_data(self, col=0):
return self.get_data(self.get_current_row(), col)
#return self.queryModel.data(self.mk_querymodel_index(self.get_current_row(), col))
def get_data(self, row, col=0):
return self.queryModel.data(self.mk_querymodel_index(row, col))
def mk_querymodel_index(self, row, col):
return self.queryModel.createIndex(row, col)
def get_current_row(self):
return self.sql_table.selectionModel().currentIndex().row()
def tableview_to_list(self):
a = self.queryModel.query()
a.first()
r = []
while a.isValid():
r.append(a.value(self.thread.MEDIA_PATH_SQL_COL_NUM['path']))
if not a.next():
break
if self.random_check.checkState() == Qt.Checked:
random.shuffle(r)
return r
def load_param(self):
self.param = {
'gallery_path': [],
'sub_folder': False,
}
if os.path.exists('settings.json'):
try:
with open('settings.json') as json_file:
paramR = json.load(json_file)
for i in paramR.keys():
self.param[i] = paramR[i]
except:
'okay'
def save_param(self):
with open('settings.json', 'w') as outfile:
json.dump(self.param, outfile)
def closeEvent(self, event):
self.save_param()
try:
self.gc_placeholder.close()
except:
'okay'
class ProjectsOverview(FilterableTable):
"""a widget to display a complete overview over
all projects
"""
changed_projects = pyqtSignal(str)
change_view = pyqtSignal(int)
deleted_project = pyqtSignal()
submit_to_ENA = pyqtSignal(str)
submit_to_IPD = pyqtSignal(str)
open_new_allele_form = pyqtSignal(str)
def __init__(self, log, mydb, parent = None):
super().__init__(log, mydb)
if parent:
self.settings = parent.settings
else:
import GUI_login
self.settings = GUI_login.get_settings("admin", self.log)
self.enhance_UI()
self.show_closed = False
self.filter_closed()
self.table.resizeColumnsToContents()
self.table.horizontalHeader().setSectionResizeMode(6, QHeaderView.Stretch)
self.table.horizontalHeader().setSectionResizeMode(7, QHeaderView.Stretch)
self.add_headers()
self.update_filterbox()
def enhance_UI(self):
self.toggle_btn = QPushButton(self)
self.toggle_btn.setText("Show closed projects!")
self.toggle_btn.setCheckable(True)
self.show_closed = False
self.grid.addWidget(self.toggle_btn, 1, 0)
self.toggle_btn.toggled.connect(self.on_toggleBtn_clicked)
self.table.customContextMenuRequested.connect(self.open_menu)
def add_headers(self):
headers = ["Project Name", "Project Status", "Creation Date", "User Name",
"Gene", "Pool", "Title", "Description", "Number of Alleles"]
for (i, column) in enumerate(headers):
self.proxy.setHeaderData(i, Qt.Horizontal, column)
def create_model(self):
"""creates the table model
"""
self.log.debug("Creating the table model...")
self.model = QSqlQueryModel()
self.query_open = """
SELECT projects.project_name, project_status, creation_date,
username, projects.gene, pool, title, description,
count(alleles.local_name) as nr_alleles
FROM projects
LEFT OUTER JOIN alleles
ON projects.project_name = alleles.project_name
WHERE project_status = 'Open'
GROUP BY projects.project_name, project_status, creation_date,
username, projects.gene, pool, title, description
ORDER BY projects.project_name desc
"""
self.query_all = """
SELECT projects.project_name, project_status, creation_date,
username, projects.gene, pool, title, description,
count(alleles.local_name) as nr_alleles
FROM projects
LEFT OUTER JOIN alleles
ON projects.project_name = alleles.project_name
GROUP BY projects.project_name, project_status, creation_date,
username, projects.gene, pool, title, description
ORDER BY projects.project_name desc
"""
self.q = QSqlQuery()
self.log.debug("\t=> Done!")
@pyqtSlot()
def filter_closed(self):
"""filters the table either for only open projects (default)
or all projects
"""
if self.show_closed:
self.log.debug("Filtering for all projects...")
query = self.query_all
else:
self.log.debug("Filtering for only open projects...")
query = self.query_open
self.q.exec_(query)
self.check_error(self.q)
self.model.setQuery(self.q)
def on_toggleBtn_clicked(self, state):
"""when "ShowAll!" button is toggled, reset query
to appropriate filter
"""
self.show_closed = state
self.filter_closed()
def open_menu(self, pos):
"""provides a context menu
"""
menu = QMenu()
new_act = menu.addAction("Add new Target Allele")
open_act = menu.addAction("Open Project View")
select_act = menu.addAction("Select as current Project")
ena_act = menu.addAction("Submit Project to ENA")
ipd_act = menu.addAction("Submit Project to IPD")
del_act = menu.addAction("Delete project if empty")
action = menu.exec_(self.table.mapToGlobal(pos))
if action:
row = self.table.indexAt(pos).row()
myindex = self.model.index(row, 0)
project = self.model.data(myindex)
if action == select_act:
self.changed_projects.emit(project)
self.log.debug("ProjectsOverview emitted changed_projects")
elif action == open_act:
self.log.debug("ProjectsOverview emitted changed_projects & change_view")
self.changed_projects.emit(project)
self.change_view.emit(3)
elif action == new_act:
self.open_new_allele_form.emit(project)
elif action == ena_act:
self.submit_to_ENA.emit(project)
elif action == ipd_act:
self.submit_to_IPD.emit(project)
elif action == del_act:
self.delete_project(project, row)
@pyqtSlot(str, int)
def delete_project(self, project, row):
"""delete a project from the database & file system if it's empty
"""
self.log.debug("Attempting to delete project '{}' from database...".format(project))
q = QSqlQuery()
count_query = "select count(*) from alleles where project_name = '{}'".format(project)
q.exec_(count_query)
check_error(q, self.mydb, self.log)
alleles = 0
while q.next():
alleles = q.value(0)
if alleles == 0:
delete_query = "delete from projects where project_name = '{}'".format(project)
q.exec_(delete_query)
self.log.debug("\t=> Successfully deleted empty project")
self.log.debug("Attempting to delete project '{}' from file system...".format(project))
project_dir = os.path.join(self.settings["projects_dir"], project)
try:
shutil.rmtree(project_dir)
except Exception as E:
self.log.debug("=> File deletion did not work:")
self.log.error(E)
self.log.exception(E)
self.log.debug("=> Project '{}' successfully deleted from database and file system".format(project))
self.deleted_project.emit()
else:
self.log.debug("\t=> Project contains {} alleles => cannot delete!".format(alleles))
def refresh(self):
"""refreshes the table using setQuery
=> take care, this might lead to performance issues
"""
self.model.setQuery(self.model.query().lastQuery())