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)
Exemple #2
0
 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)
Exemple #3
0
 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)
Exemple #4
0
    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)
Exemple #5
0
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")
Exemple #7
0
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())
Exemple #8
0
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_()
Exemple #9
0
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()
Exemple #10
0
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())