def load(layout): """"Loads the file and restores the saved state""" # Restore the first day of usage global_vars.DAYFIRST = datetime.date.fromordinal(int(open(paths.savePath).read().splitlines()[0])) filename = os.path.basename(paths.savePath)[:-8] database.setConnection(filename) layout.initTasks(database.getStatuses()) layout.tab.clearAll() # ### Loading main table # # weekdays = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'] opened = False q = 'SELECT * FROM main' query = QSqlQuery(q) while query.next(): record = query.record() indexes = [] if not record.isEmpty(): for i in range(0, 44): if not record.isNull(i + 1): indexes.append(i) day = str(record.field('weekday').value()).split('_') weekday = day[0] weeknum = int(day[1]) values = {} for index in indexes: q = QSqlQuery("SELECT name FROM tasks WHERE rowid ='" + str(query.value(index + 1)) + "'") q.next() values[index] = q.value(0) if values: opened = True layout.tab.setValues(values, weekdays.index(weekday), weeknum) if not opened: layout.tab.openTab(1) # # ################ # Loading evaluation values # q = 'SELECT * from evaluation' query = QSqlQuery(q) while query.next(): record = query.record() if not record.isEmpty(): week = record.field('week').value() values = [] for day in global_vars.WEEKDAYS: values.append(record.field(day).value()) global_vars.EVAL_VALUES[week] = values database.dropConnection()
def miseAJour(self): u"""Liste les absences pouvant donner lieu à un email de rappel""" self._ui.cbAbsence.clear() # Vérification des mails à envoyer req = QSqlQuery() sql = MailSQL.construitRequeteComptage(self.__conf["duree"]) if req.exec_(sql): req.next() nbMails = req.record().value(0) else: # TODO log print req.lastError().text() print req.lastQuery() print "Erreur de requête" return label = str(nbMails) + " absence" if nbMails == 0: label += " :" self._ui.lAbsence.setText(label) self._ui.leSujet.setText("") self._ui.teCorps.setText("") self.__activerUi(False) self._ui.pbEnvoyer.setText("Envoyer") return else: self.__activerUi(True) if nbMails > 1: label += "s" label += " :" self._ui.lAbsence.setText(label) sql = MailSQL.construitRequeteListe(self.__conf["duree"]) if not req.exec_(sql): print req.lastError().text() print req.lastQuery() print "Erreur de requête" else: self.__absences = [] while (req.next()): absence = {} rec = req.record() absence = {} absence["id"] = rec.value(0) absence["date"] = QDate.fromString(rec.value(1), Qt.ISODate) absence["nom"] = rec.value(2) absence["adresse"] = rec.value(3) self.__absences.append(absence) item = absence["nom"] + " le " item += absence["date"].toString(Qt.SystemLocaleLongDate) self._ui.cbAbsence.addItem(item)
def check_barcode(self, txt): if len(txt) == self.edBarcode.maxLength(): db = Db_Instance("obook_barcode_search").get_instance() try: if db.open(): query = QSqlQuery(db) query.prepare("SELECT * FROM book WHERE barcode = :barcode") query.bindValue(":barcode", txt) query.exec_() if query.next(): self.add_book_from_record(query.record()) self.edBarcode.clear() else: raise RecordNotFoundException else: raise DbUnavailableException except RecordNotFoundException: message = unicode("Código de barra inválido!\n\n""" "O código informado não corresponde a nenhum livro cadastrado".decode('utf-8')) QMessageBox.critical(self, "Seareiros - Livraria", message) except DbUnavailableException: self.log.error(db.lastError().text()) message = unicode("Erro de conexão\n\n""Banco de dados indisponível".decode('utf-8')) QMessageBox.critical(self, "Seareiros - Livraria", message) self.edBarcode.setFocus()
def preSupprVerification(self, index): u"""Vérification à effectuer avant d'autoriser à supprimer un item Renvoit False si la suppression est interdite """ sql = """ SELECT COUNT(*) FROM absence WHERE id_intervenant=""" + str(index.sibling(index.row(), 0).data()) req = QSqlQuery() if req.exec_(sql): req.next() nbAbsences = req.record().value(0) if nbAbsences != 0: pl = "" if nbAbsences != 1: pl = "s" QMessageBox.critical( self, "Impossible de suppprimer", u"L'intervenant a encore " + str(nbAbsences) + u" absence" + pl + u" enregistrée" + pl + "<br />" + u"Il faut les supprimer avant") # TODO trouver un moyen de setter l'onglet de l'application # self._ui.tabWidget.setCurrentIndex(1) return False # TODO gérer le else, au moins logger quelque chose return True
def preSupprVerification(self, index): u"""Vérification à effectuer avant d'autoriser à supprimer un item Renvoit False si la suppression est interdite """ sql = """ SELECT COUNT(*) FROM absence WHERE id_intervenant=""" + str(index.sibling(index.row(), 0).data()) req = QSqlQuery() if req.exec_(sql): req.next() nbAbsences = req.record().value(0) if nbAbsences != 0: pl = "" if nbAbsences != 1: pl = "s" QMessageBox.critical(self, "Impossible de suppprimer", u"L'intervenant a encore " + str(nbAbsences) + u" absence" + pl + u" enregistrée" + pl + "<br />" + u"Il faut les supprimer avant") # TODO trouver un moyen de setter l'onglet de l'application # self._ui.tabWidget.setCurrentIndex(1) return False # TODO gérer le else, au moins logger quelque chose return True
def _get_name_from_id(self, table, id): db = Db_Instance(table + "_fetch_" + str(id) + "_name").get_instance() if not db.open(): return None else: query = QSqlQuery(db) query.prepare("SELECT name FROM %s WHERE id = :id" % table) query.bindValue(":name", id) query.exec_() if query.next(): return query.record().value("name") else: return None
def _get_id_from_name(self, table, name): db = Db_Instance(table + "_fetch_" + name + "_id").get_instance() if not db.open(): return None else: query = QSqlQuery(db) query.prepare("SELECT id FROM %s WHERE name = :name" % table) query.bindValue(":name", name) query.exec_() if query.next(): return query.record().value("id") else: return None
def get_added_record(self): db = Db_Instance("added_book_record").get_instance() if db.open() and self._book_id: query = QSqlQuery(db) query.prepare("SELECT * FROM book WHERE id = :id") query.bindValue(":id", self._book_id) query.exec_() if query.next(): return query.record() else: return None else: return None
def get_added_record(self): """ My workaround to get the last inserted id without any postgres specific queries """ db = Db_Instance("add_associate_last_id").get_instance() if not db.open(): return None else: query = QSqlQuery(db) query.prepare("SELECT * FROM associate WHERE fullname = :fullname") query.bindValue(":fullname", self.edFullName.text()) query.exec_() if query.next(): return query.record() else: return None
def last_id_from_sequence(table, db, id_field="id"): # db has to be passed as parameter as we have to capture the same session for this to work if not db.isOpen(): return None else: query = QSqlQuery(db) query.prepare("SELECT currval(pg_get_serial_sequence(:table,:id))") query.bindValue(":table", table) query.bindValue(":id", id_field) query.exec_() if query.next(): return query.record().value("currval") else: return None
def slot_exec(self, query, params): records = [] sql = QSqlQuery(self.db) sql.prepare(query) if params: for p in params: self.set_value(sql, p) sql.exec_() # number of rows returned from query execution self.numRows.emit(sql.size()) while sql.next(): records.append(sql.record()) #sleep(1) # row read self.readRow.emit() self.result.emit(records, '') self.cleanup.emit(self.db.connectionName())
def load(layout): """"Loads the file and restores the saved state""" # Restore the first day of usage global_vars.DAYFIRST = datetime.date.fromordinal( int(open(paths.savePath).read().splitlines()[0])) filename = os.path.basename(paths.savePath)[:-8] database.setConnection(filename) layout.initTasks(database.getStatuses()) layout.tab.clearAll() # ### Loading main table # # weekdays = [ 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday' ] opened = False q = 'SELECT * FROM main' query = QSqlQuery(q) while query.next(): record = query.record() indexes = [] if not record.isEmpty(): for i in range(0, 44): if not record.isNull(i + 1): indexes.append(i) day = str(record.field('weekday').value()).split('_') weekday = day[0] weeknum = int(day[1]) values = {} for index in indexes: q = QSqlQuery("SELECT name FROM tasks WHERE rowid ='" + str(query.value(index + 1)) + "'") q.next() values[index] = q.value(0) if values: opened = True layout.tab.setValues(values, weekdays.index(weekday), weeknum) if not opened: layout.tab.openTab(1) # # ################ # Loading evaluation values # q = 'SELECT * from evaluation' query = QSqlQuery(q) while query.next(): record = query.record() if not record.isEmpty(): week = record.field('week').value() values = [] for day in global_vars.WEEKDAYS: values.append(record.field(day).value()) global_vars.EVAL_VALUES[week] = values database.dropConnection()