Пример #1
0
 def insertNewRecord(self, data_dict):
     query = QSqlQuery(self.handle)
     if query.prepare(self.insert_query % data_dict):
         query.bindValue(":photo", data_dict['photo'])
         return query.exec_()
     else:
         return False
Пример #2
0
def set_comision( value, article_id ):
    """
    Actualiza el costo agregado COMISION de un articulo
    @param article_id: El Index del record del tableView
    @param value: El Valor a guardar en el record del Index        
    """
    query = QSqlQuery()
    if not query.exec_( """
    UPDATE costosagregados 
    SET activo=0
    WHERE idarticulo=%d AND idtipocosto=%d
    """ % ( article_id,
           constantes.COMISION ) ):
        raise Exception( query.lastError().text() )

    if not query.prepare( """
    INSERT INTO costosagregados 
    (valorcosto,activo,idtipocosto,idarticulo) 
     VALUES (:valor,1,%d,%d) 
     """ % ( constantes.COMISION,
            article_id ) ):
        raise Exception( query.lastError().text() )
    query.bindValue( ":valor", value )

    if not query.exec_():
        raise Exception( query.lastError().text() )
Пример #3
0
 def replace_all_links_to_this_word(self):
     remote_query = QSqlQuery(self.remote_cn)
     local_query = QSqlQuery(self.local_cn)
     if self.onlyClassified:
         remote_query.prepare(replace_uuid_in_links_query_OC(self.uuid, self.new_uuid))
     else:
         remote_query.prepare(replace_uuid_in_links_query(self.uuid, self.new_uuid))
     local_query.prepare(full_delete_local_termin_by_uuid)
     local_query.bindValue(":uuid", self.uuid)
     s1, s2 = False, False
     QtSql.QSqlDatabase.database('SQLiteConnection').transaction()
     QtSql.QSqlDatabase.database('PGconnection').transaction()
     if remote_query.exec_():
         s1 = True
     else:
         print(remote_query.lastError().text())
         print(remote_query.lastQuery())
     if local_query.exec_():
         s2 = True
     else:
         print(local_query.lastError().text())
         print(local_query.lastQuery())
     if s1 and s2:
         QtSql.QSqlDatabase.database('SQLiteConnection').commit()
         QtSql.QSqlDatabase.database('PGconnection').commit()
     else:
         QtSql.QSqlDatabase.database('SQLiteConnection').rollback()
         QtSql.QSqlDatabase.database('PGconnection').rollback()
     self.finishTrigger.emit(True)
Пример #4
0
def prepareLinkFromWordInDB(word, html, exclude_uuid):
    try:
        html = html.split("<!--StartFragment-->")[1].split(
            "<!--EndFragment-->")[0]
    except:
        html = word
    str = stem_str(word)
    remote_sql = QSqlQuery(get_local_connection())
    remote_sql.prepare(count_word_in_db_query)
    # remote_sql.prepare(search_word_in_db_query)
    remote_sql.bindValue(":word", str)
    remote_sql.bindValue(":exclude_uuid", exclude_uuid)
    if remote_sql.exec_():
        if remote_sql.next():
            num = remote_sql.value(0)
            if num == 0:
                return None
            elif num == 1:
                return {
                    'link':
                    "<a href='termin##" + remote_sql.value(1) +
                    "##status##1##word##" + word + "##inithtml##" + html +
                    "' style='color:green'>" + html + "</a>"
                }
            elif num > 1:
                return {
                    'link':
                    "<a href='termin##" + str + "##status##2##word##" + word +
                    "##inithtml##" + html + "' style='color:red'>" + html +
                    "</a>"
                }
        else:
            return None
    else:
        return None
Пример #5
0
 def getTeachersWithName(self, first, last):
     """Looks for teachers with the given name"""
     tList = []
     try:
         query = QSqlQuery(self.conn)
         query.prepare("SELECT first_name, last_name, address, city, postal_code, daytime_phone, \
             evening_phone, email \
             FROM teachers WHERE first_name=:first AND last_name=:last")
         query.bindValue(":first", first)
         query.bindValue(":last", last)
         query.exec_()
         if query.isActive() == False:
             print query.lastError().text()
             return query.lastError().text()
         while query.next() == True:
             first = str(query.value(0).toString())
             last = str(query.value(1).toString())
             address = str(query.value(2).toString())
             city = str(query.value(3).toString())
             postal = str(query.value(4).toString())
             daytimePhone = str(query.value(5).toString())
             eveningPhone = str(query.value(6).toString())
             email = str(query.value(7).toString())
             tList.append(Teacher(first, last, address, city, postal, daytimePhone, eveningPhone, email))
         return tList
     except Exception, e:
         # TODO: log this instead of printing to console
         print "getTeachersWithName FAILED\n\tquery: {0}\
             \n\terror: {1}".format(query.lastQuery(), e)
Пример #6
0
 def getEntryFromId(self, entryId):
     """Retrieve Entry from specified id."""
     try:
         query = QSqlQuery(self.conn)
         query.prepare("SELECT participant_id, teacher_id, discipline, level, class_number, \
             class_name, instrument, years_of_instruction, scheduling_requirements FROM entries \
             WHERE id=:id")
         query.bindValue(":id", entryId)
         query.exec_()
         if query.isActive() == False:
             print query.lastError().text()
             return query.lastError().text()
         query.next()
         participantID = str(query.value(0).toString())
         teacherID = str(query.value(1).toString())
         discipline = str(query.value(2).toString())
         level = str(query.value(3).toString())
         classNumber = str(query.value(4).toString())
         className = str(query.value(5).toString())
         instrument = str(query.value(6).toString())
         yearsOfInstruction = str(query.value(7).toString())
         schedulingRequirements = str(query.value(8).toString())
         # get associated selections
         selections = self.getSelectionsFromEntryId(entryId)
         ee = Entry(participantID, teacherID, discipline, level, yearsOfInstruction, classNumber, className, instrument, selections, schedulingRequirements)
         return ee
     except Exception, e:
         # TODO: log this instead of printing to console
         print "getEntryFromId FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e)
         return e
Пример #7
0
 def getParticipantsWithName(self, first, last):
     """Looks for participants with the given name"""
     pList = []
     try:
         query = QSqlQuery(self.conn)
         query.prepare("SELECT first_name, last_name, address, town, postal_code, home_phone, \
             cell_phone, email, date_of_birth, school_attending, parent \
             FROM soloparticipants WHERE first_name=:first AND last_name=:last")
         query.bindValue(":first", first)
         query.bindValue(":last", last)
         query.exec_()
         if query.isActive() == False:
             print query.lastError().text()
             return query.lastError().text()
         while query.next() == True:
             first = str(query.value(0).toString())
             last = str(query.value(1).toString())
             address = str(query.value(2).toString())
             town = str(query.value(3).toString())
             postal = str(query.value(4).toString())
             home = str(query.value(5).toString())
             cell = str(query.value(6).toString())
             email = str(query.value(7).toString())
             dob = str(query.value(8).toString())
             schoolAttending = str(query.value(9).toString())
             parent = str(query.value(10).toString())
             pList.append(Participant(first, last, address, town, postal, home, cell, email, dob, schoolAttending, parent))
         return pList
     except Exception, e:
         # TODO: log this instead of printing to console
         print "getParticipantsWithName FAILED\n\tquery: {0}\
             \n\terror: {1}".format(query.lastQuery(), e)
Пример #8
0
 def getTeacherFromId(self, teacherId):
     """Retrieve the appropriate Teacher from the given id"""
     try:
         query = QSqlQuery(self.conn)
         query.prepare("SELECT first_name, last_name, address, city, postal_code, daytime_phone, evening_phone, email \
                 FROM teachers WHERE id=:id")
         numericId = teacherId
         query.bindValue(":id", numericId)
         query.exec_()
         if query.isActive() == False:
             print query.lastError().text()
             return query.lastError().text()
         # Now turn it into the appropriate object
         query.next()
         first = str(query.value(0).toString())
         last = str(query.value(1).toString())
         address = str(query.value(2).toString())
         city = str(query.value(3).toString())
         postal = str(query.value(4).toString())
         daytimePhone = str(query.value(5).toString())
         eveningPhone = str(query.value(6).toString())
         email = str(query.value(7).toString())
         retrievedTeacher = Teacher(first, last, address, city, postal, daytimePhone, eveningPhone, email)
         return retrievedTeacher
     except Exception, e:
         # TODO: log this instead of printing to console
         print "getTeacherFromId FAILED\n\tquery: {0}\
             \n\tvalues: {1}\n\terror: {2}".format(query.lastQuery(), numericId, e)
Пример #9
0
    def setData( self, column, value ):
        value = value.toList()
        if column < 0:
            return False
        if column == 0:
            try:
                if not QSqlDatabase.database().isOpen():
                    if not QSqlDatabase.database().open():
                        raise Exception( "No se pudo conectar con la base de datos" )
                query = QSqlQuery()
                if not query.prepare( """
                UPDATE categorias SET nombre = :nombre
                WHERE idcategoria = %d 
                """ % self.itemData[1] ):
                    raise Exception( "No se pudo preparar la consulta para actualizar la categoria" )

                query.bindValue( ":nombre", value[column].toString().strip() )
                if not query.exec_():
                    raise Exception( "No se pudo actualizar la categoria" )

            except Exception as inst:
                logging.error( unicode( inst ) )
                return False
            self.itemData[column] = value[column]
        return True
Пример #10
0
 def get_song_listmodel(self, key = None, parent = None):
     """Create and return a QStringListModel of songs in database
     By default all songs are included
     @param key: Include only songs in given key
     @param key: String
     @return: List model to be passed to QListView
     @type return: QStringListModel"""
     if key == None:
         query = QSqlQuery()
         query.prepare("SELECT song FROM Patterns")
         success = query.exec_()
         if not success:
             pass  # TODO
         songs = [], 
         while query.next():
             songs.append(query.value(0))
     else:
         query = QSqlQuery()
         query.prepare("SELECT song FROM Patterns WHERE key == :key")
         query.bindValue(":key", key)
         success = query.exec_()
         if not success:
             pass  # TODO
         songs = [], 
         while query.next():
             songs.append(query.value(0))  # Ajatusvirhe
     return QStringListModel(songs, parent)
Пример #11
0
    def removeRelationship(self):
        actlayer = qgis.utils.iface.activeLayer()
        index = self.modelt.index(0,0)
        columnindex=0
        for i in range(0,self.modelt.columnCount(index)):
            qresult = self.modelt.headerData(i, Qt.Horizontal, 0)
            if self.keycolumn == qresult:
                columnindex=i
                break
        idlist = []

        for row in range(self.modelt.rowCount()):
            index = self.modelt.index(row,columnindex)
            id = self.modelt.data(index,columnindex)
            idlist.append(id)

        queryinsert = QSqlQuery()
        querystr3 = "SELECT " + self.schema + ".del_row(:schema, :ref_id);" # "DELETE FROM " + self.schema+ " prodser.user_cim WHERE " self.keycolumn + " IN (" + stringlist + ");"

        for id in idlist:
            # create an item with a caption
            #print "id="+str(id)+", curruid="+str(curruid)+", fullname="+fullname
            queryinsert.prepare(querystr3)
            queryinsert.bindValue(":schema", self.schema)
            queryinsert.bindValue(":ref_id", id)
            testquery = queryinsert.exec_()
            if testquery:
                print "deleted: ", id
            else:
                print "not deleted: " + id + queryinsert.lastError().text()
                print querystr3

        actlayer.setSubsetString("")
        self.dlg.accept()
Пример #12
0
 def get_song_listmodel(self, key=None, parent=None):
     """Create and return a QStringListModel of songs in database
     By default all songs are included
     @param key: Include only songs in given key
     @param key: String
     @return: List model to be passed to QListView
     @type return: QStringListModel"""
     if key == None:
         query = QSqlQuery()
         query.prepare("SELECT song FROM Patterns")
         success = query.exec_()
         if not success:
             pass  # TODO
         songs = [],
         while query.next():
             songs.append(query.value(0))
     else:
         query = QSqlQuery()
         query.prepare("SELECT song FROM Patterns WHERE key == :key")
         query.bindValue(":key", key)
         success = query.exec_()
         if not success:
             pass  # TODO
         songs = [],
         while query.next():
             songs.append(query.value(0))  # Ajatusvirhe
     return QStringListModel(songs, parent)
Пример #13
0
 def delDettRecord(self):
     if not self.db.isOpen():
         self.statusbar.showMessage(
             "Database non aperto...",
             5000)
         return
     selrows = self.sItmSelModel.selectedRows()
     if not selrows:
         self.statusbar.showMessage(
             "No articles selected to delete...",
             5000)
         return
     if(QMessageBox.question(self, "Cancellazione righe",
             "Vuoi cancellare: {0} righe?".format(len(selrows)),
             QMessageBox.Yes|QMessageBox.No) ==
             QMessageBox.No):
         return
     QSqlDatabase.database().transaction()
     query = QSqlQuery()
     query.prepare("DELETE FROM fattslave WHERE id = :val")
     for i in selrows:
         if i.isValid():
             query.bindValue(":val", QVariant(i.data().toInt()[0]))
             query.exec_()
     QSqlDatabase.database().commit()
     self.sModel.revertAll()
     self.mmUpdate()
Пример #14
0
 def delCliRecord(self):
     if not self.db.isOpen():
         self.statusbar.showMessage(
             "Database non aperto...",
             5000)
         return
     selrows = self.cItmSelModel.selectedRows()
     if not selrows:
         self.statusbar.showMessage(
             "No selected customers to delete...",
             5000)
         return
     if(QMessageBox.question(self, "Delete Customers",
             "Do you want to delete: {0} customer(s)?".format(len(selrows)),
             QMessageBox.Yes|QMessageBox.No) ==
             QMessageBox.No):
         return
     QSqlDatabase.database().transaction()
     query = QSqlQuery()
     query.prepare("DELETE FROM clienti WHERE id = :val")
     for i in selrows:
         if i.isValid():
             query.bindValue(":val", QVariant(i.data().toInt()[0]))
             query.exec_()
     QSqlDatabase.database().commit()
     self.cModel.select()
Пример #15
0
 def setData(self, index, value, role=None):
     if not index.isValid() or role not in (Qt.EditRole, Qt.CheckStateRole):
         return False
     expected_datepayd = QDate.fromString(index.data().toString(),
                                          'd MMMM yyyy')
     if role == Qt.EditRole:
         effective_datepayd = value.toDate()
     elif value.toInt()[0] == Qt.Checked:
         effective_datepayd = expected_datepayd
     else:
         print 'Error: Non puoi annullare i pagamenti'
         return False
     query = QSqlQuery(
         'UPDATE payments SET effective_datepayd = '
         ':effective_datepayd WHERE clients_client = :clients_client '
         'AND clients_machine = :clients_machine AND clients_selldate = '
         ':clients_selldate AND expected_datepayd = :expected_datepayd',
         self._db)
     # TODO: trovare client, machine, selldate
     header_data = self.headerData(index.column(), Qt.Horizontal,
                                   Qt.DisplayRole).toString().split('\n')
     client = header_data[0]
     machine = header_data[1]
     selldate = QDate.fromString(header_data[2], 'd MMMM yyyy')
     query.bindValue(':effective_datepayd', QVariant(effective_datepayd))
     query.bindValue(':clients_client', QVariant(client))
     query.bindValue(':clients_machine', QVariant(machine))
     query.bindValue(':clients_selldate', QVariant(selldate))
     query.bindValue(':expected_datepayd', QVariant(expected_datepayd))
     if not query.exec_():
         raise StandardError('SYNTAX ERROR')
     self.emit(SIGNAL("dataChanged()"), index, index)
     return True
Пример #16
0
 def getAllEntriesInDiscipline(self, discipline):
     """Returns all the Entries for the given discipline"""
     entryList = []
     try:
         query = QSqlQuery(self.conn)
         query.prepare("SELECT participant_id, teacher_id, discipline, level, class_number, \
             class_name, instrument, years_of_instruction, scheduling_requirements, id FROM entries \
             WHERE discipline=:discipline")
         query.bindValue(":discipline", discipline)
         query.exec_()
         if query.isActive() == False:
             print query.lastError().text()
             return query.lastError().text()
         while query.next() == True:
             participantID = str(query.value(0).toString())
             teacherID = str(query.value(1).toString())
             discipline = str(query.value(2).toString())
             level = str(query.value(3).toString())
             classNumber = str(query.value(4).toString())
             className = str(query.value(5).toString())
             instrument = str(query.value(6).toString())
             yearsOfInstruction = str(query.value(7).toString())
             schedulingRequirements = str(query.value(8).toString())
             entryId = str(query.value(9).toString())
             # get associated selections
             selections = self.getSelectionsFromEntryId(entryId)
             ee = Entry(participantID, teacherID, discipline, level, yearsOfInstruction, classNumber, className, instrument, selections, schedulingRequirements)
             entryList.append(ee)
         return entryList
     except Exception, e:
         # TODO: log this instead of printing to console
         print "getAllEntriesInDiscipline FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e)
         return e
Пример #17
0
def check_credentials(params, password):
    """
    Check credentials against database.  If correct, return 'role', else
    return False.

    :param params: Dictionary of parameters, including UserName and db path.
    :param password: String of password
    :return role / False: role (string), or false if denied.
    """
    # Connect to database
    if config.DEBUG_MODE:
        print('DEBUG_MODE: checking credentials')
    db_path = os.path.join(params['RNDataStorePath'], params['DbName'])
    username = params['UserName']
    db = database.connect_and_open(db_path, 'roadnet_db')

    # Query database
    login_query = QSqlQuery(db)
    login_query.prepare("""SELECT usertype FROM tblUsers
           WHERE username =:usr AND userpwd =:pwd""")
    login_query.bindValue(":usr", username, QSql.Out)
    login_query.bindValue(":pwd", password, QSql.Out)
    executed = login_query.exec_()
    if not executed:
        raise Exception('Database query failed.')
    if login_query.first() is True:  # i.e. matching record returned
        # Correct username or password: get role
        role = login_query.value(0)
        if role == 'admin':
            role = 'editor'  # Admin role is no longer used
    else:
        # Set debug mode settings to thinkwhere and editor and remove lock
        if config.DEBUG_MODE:
            params['UserName'] = '******'
            params['role'] = 'editor'
            role = 'editor'
            lock_file_path = os.path.join(params['RNDataStorePath'], 'RNLock')
            if os.path.isfile(lock_file_path):
                os.remove(lock_file_path)
        else:
            # Wrong username or password: warning message
            wrong_login_msg = QMessageBox(QMessageBox.Warning, " ",
                                          "Incorrect username or password",
                                          QMessageBox.Ok, None)
            wrong_login_msg.setWindowFlags(Qt.CustomizeWindowHint
                                           | Qt.WindowTitleHint)
            wrong_login_msg.exec_()
            role = 'init'

    # Close database
    del (login_query)
    connection_name = db.connectionName()
    db.close()
    del (db)
    QSqlDatabase.removeDatabase(connection_name)
    if config.DEBUG_MODE:
        print('DEBUG_MODE: closing QSqlDatabase {}'.format(connection_name))
    # Return role or None
    return role
Пример #18
0
 def _query(self, querystring, **mappings):
     query = QSqlQuery(self.db)
     query.prepare(querystring)
     for key, value in mappings.iteritems():
         bindvalue = ":{}".format(key)
         if bindvalue in querystring:
             query.bindValue(bindvalue, value)
     return query
Пример #19
0
 def saveDefault(self, control, value):
     self.removeDefault(control)
     name = control.objectName()
     query = QSqlQuery()
     query.prepare("INSERT INTO DefaultValues (control, value)" "VALUES (:control,:value)")
     query.bindValue(":control", name)
     query.bindValue(":value", value)
     query.exec_()
Пример #20
0
 def _query(self, querystring, **mappings):
     querystring = querystring.replace(r"\r\n", " ")
     query = QSqlQuery(self.db)
     query.prepare(querystring)
     for key, value in mappings.iteritems():
         bindvalue = ":{}".format(key)
         if bindvalue in querystring:
             query.bindValue(bindvalue, value)
     return query
Пример #21
0
 def _query(self, querystring, **mappings):
     querystring = querystring.replace(r"\r\n", " ")
     query = QSqlQuery(self.db)
     query.prepare(querystring)
     for key, value in mappings.iteritems():
         bindvalue = ":{}".format(key)
         if re.search(r"{}\b".format(bindvalue), querystring):
             query.bindValue(bindvalue, value)
     return query
Пример #22
0
    def ExcluirCliente(codigoCli):
        conn = ConexaoSQL
        db = conn.getConexao()
        db.open()

        query = QSqlQuery()
        query.prepare("DELETE FROM Cliente WHERE CodigoCli=:codigoCli")
        query.bindValue(":codigoCli", codigoCli)
        query.exec_()
        db.commit()
Пример #23
0
    def ExcluirVeiculo(codigoVeic):
        conn = ConexaoSQL
        db = conn.getConexao()
        db.open()

        query = QSqlQuery()
        query.prepare("DELETE FROM Veiculo WHERE CodigoVeic=:codigoVeic")
        query.bindValue(":codigoVeic", codigoVeic)
        query.exec_()
        db.commit()
Пример #24
0
    def login(self, username, password):
        sql = 'SELECT username, password FROM users WHERE username=:username AND password=:password'
        q = QSqlQuery()
        q.prepare(sql)
        q.bindValue(':username', username)
        q.bindValue(':password', password)

        q.exec_()
        if q.next():
            print "Sesion correcta para %s" % username
            return True
Пример #25
0
 def remove_song(self, song_name):
     """Removes given song from database
     @param song_name: name of the song to be removed
     @type song_name: String"""
     query = QSqlQuery()
     query.prepare("DELETE FROM Patterns WHERE song == :song")
     query.bindValue(":song", song_name)
     success = query.exec_()
     if not success:
         return False  # TODO
     return True
Пример #26
0
 def remove_song(self, song_name):
     """Removes given song from database
     @param song_name: name of the song to be removed
     @type song_name: String"""
     query = QSqlQuery()
     query.prepare("DELETE FROM Patterns WHERE song == :song")
     query.bindValue(":song", song_name)
     success = query.exec_()
     if not success:
         return  False # TODO
     return True
Пример #27
0
 def getParticipantFromId(self, participantId):
     """Retrieve the appropriate Participant from the given id"""
     try:
         query = QSqlQuery(self.conn)
         # if participantId[0] == 's':
         #     query.prepare("SELECT first_name, last_name, address, town, postal_code, home_phone, cell_phone, email, date_of_birth, school_attending, parent \
         #         FROM soloparticipants WHERE id=:id")
         # else:
         #     query.prepare("SELECT group_name, group_size, school_grade, average_age, participants, contact \
         #         FROM groupparticipants WHERE id=:id")
         query.prepare("SELECT first_name, last_name, address, city, postal_code, home_phone, cell_phone, email, date_of_birth, school_attending, parent, age, school_grade, group_name, number_participants, earliest_time, latest_time, group_participants, average_age, contact \
             FROM participants WHERE id=:id")
         # numericId = participantId[1:]
         query.bindValue(":id", participantId)
         query.exec_()
         if query.isActive() == False:
             print query.lastError().text()
             return query.lastError().text()
         # Now turn it into the appropriate object
         query.next()
         retrievedParticipant = None
         # if participantId[0] == 's':
         first = str(query.value(0).toString())
         last = str(query.value(1).toString())
         address = str(query.value(2).toString())
         city = str(query.value(3).toString())
         postal = str(query.value(4).toString())
         home = str(query.value(5).toString())
         cell = str(query.value(6).toString())
         email = str(query.value(7).toString())
         dob = str(query.value(8).toString())
         schoolAttending = str(query.value(9).toString())
         parent = str(query.value(10).toString())
             # retrievedParticipant = SoloParticipant(first, last, address, town, postal, home, cell, email, dob, schoolAttending, parent)
         # else:
         age = str(query.value(11).toString())
         schoolGrade = str(query.value(12).toString())
         groupName = str(query.value(13).toString())
         groupSize = str(query.value(14).toString())
         earliestTime = str(query.value(15).toString())
         latestTime = str(query.value(16).toString())
         participants = str(query.value(17).toString())            
         averageAge = str(query.value(18).toString())
         contact = str(query.value(19).toString())
             # retrievedParticipant = GroupParticipant(groupName, groupSize, schoolGrade, averageAge, participants, contact)
         retrievedParticipant = Participant(first=first, last=last, address=address, city=city, postal=postal, home=home,
             cell=cell, email=email, dob=dob, schoolAttending=schoolAttending, parent=parent, age=age, schoolGrade=schoolGrade,
             groupName=groupName, numberParticipants=groupSize, averageAge=averageAge, participants=participants, contact=contact,
             earliestPerformanceTime=earliestTime, latestPerformanceTime=latestTime)
         return retrievedParticipant
     except Exception, e:
         # TODO: log this instead of printing to console
         print "getParticipantFromId FAILED\n\tquery: {0}\
             \n\terror: {1}".format(query.lastQuery(), e)
Пример #28
0
def updateDefinition(uuid, definition, oc):
    remote_sql = QSqlQuery(get_remote_connection())
    if oc:
        remote_sql.prepare(update_definition_query_OC)
    else:
        remote_sql.prepare(update_definition_query)
    remote_sql.bindValue(":uuid", uuid.__str__())
    remote_sql.bindValue(":definition", definition.__str__())
    if remote_sql.exec_():
        return True
    else:
        return False
Пример #29
0
    def updateRecord(self, id_, data_dict):
        # Register id too
        data_dict['id_'] = id_

        # Prepare query
        query = QSqlQuery(self.handle)

        if query.prepare(self.update_query % data_dict):
            query.bindValue(":photo", data_dict['photo'])
            return query.exec_()
        else:
            return False
Пример #30
0
 def get_song_pattern(self, song_name):
     """Get chord pattern of given song
     @param song_name: name of the song in database
     @type song_name: String
     @return: pattern
     @type return: String"""
     query = QSqlQuery()
     query.prepare("SELECT pattern FROM Progressions WHERE song == :song")
     query.bindValue(":song", song_name)
     success = query.exec_()
     if not success:
         pass  # TODO
     query.next()  # Get only the first result as song name is primary key.
     return query.value(0) # pattern is the only column in result set.
Пример #31
0
    def save( self, iddocumento, nlinea ):
        """
        Este metodo guarda la linea en la base de datos
        @param iddocumento: el id del documento al que esta enlazada la linea
        @rtype: bool
        @return: Si se pudo o no guardar el documento
        """
        if not self.valid:
            raise Exception( "Se intento guardar una linea no valida" )

        query = QSqlQuery()
        if not query.prepare( 
        """
        INSERT INTO articulosxdocumento (iddocumento, idarticulo, unidades, costounit, nlinea) 
        VALUES( :iddocumento, :idarticulo, :unidades, :costounit, :linea )
        """ ):
            raise Exception( "No se pudo preparar la consulta para insertar una de las lineas del documento" )
        query.bindValue( ":iddocumento ", iddocumento )
        query.bindValue( ":idarticulo", self.itemId )
        query.bindValue( ":unidades", self.quantity )
        query.bindValue( ":costounit", self.itemPriceD.to_eng_string() )
        query.bindValue( ":linea", nlinea )


        if not query.exec_():
            raise Exception( "Hubo un error al guardar una linea" )
Пример #32
0
    def save( self, iddocumento, linea ):
        """
        Este metodo guarda el movimiento 
        @param iddocumento: El id del documento al que esta asociado el movimiento
        @param linea: El numero de la linea en el documento
        """
        if not self.valid:
            raise Exception( "Se intento guardar un movimiento no valido" )

        query = QSqlQuery()

        if not query.prepare( """
        INSERT INTO cuentasxdocumento (idcuenta, iddocumento, monto, nlinea)
        VALUES ( :idcuenta, :iddocumento, :monto, :nlinea)
        """ ):
            raise Exception( "No se pudo preparar la consulta para insertar un movimiento" )
        query.bindValue( ":idcuenta", self.itemId )
        query.bindValue( ":iddocumento", iddocumento )
        query.bindValue( ":monto", self.amount.to_eng_string() )
        query.bindValue( ":nlinea", linea )

        if not query.exec_():
            logging.critical( query.lastError().text() )

            raise Exception( "Error al insertar uno de los movimientos" )
Пример #33
0
    def save( self, iddocumento, linea ):
        """
        Este metodo guarda la linea en la base de datos
        @param iddocumento: el id del documento al que esta enlazada la linea
        """
        if not self.valid:
            raise Exception( "Se intento guardar una linea no valida" )

        query = QSqlQuery()
        if not query.prepare( 
        """
        INSERT INTO productosxdocumento (iddocumento, idprecioproducto,cantidadcajas,linea) 
        VALUES( :iddocumento, :idarticulo, :unidades,:linea)
        """ ):
            raise Exception( "no esta preparada" )

        query.bindValue( ":iddocumento", iddocumento )
        query.bindValue( ":idarticulo", self.itemId )
        query.bindValue( ":unidades", self.quantity * -1 )
        query.bindValue( ":linea", linea )
        

        if not query.exec_():
            print( query.lastError().text() )
            raise Exception( "line %d" % self.itemId )
Пример #34
0
def checkTerminExists(uuid, oc):
    remote_sql = QSqlQuery(get_remote_connection())
    if oc:
        remote_sql.prepare(get_definition_query_OC)
    else:
        remote_sql.prepare(get_definition_query)
    remote_sql.bindValue(":uuid", uuid.__str__())
    if remote_sql.exec_():
        if remote_sql.next():
            return True
        else:
            return False
    else:
        return False
Пример #35
0
 def get_song_pattern(self, song_name):
     """Get chord pattern of given song
     @param song_name: name of the song in database
     @type song_name: String
     @return: pattern
     @type return: String"""
     query = QSqlQuery()
     query.prepare("SELECT pattern FROM Progressions WHERE song == :song")
     query.bindValue(":song", song_name)
     success = query.exec_()
     if not success:
         pass  # TODO
     query.next()  # Get only the first result as song name is primary key.
     return query.value(0)  # pattern is the only column in result set.
Пример #36
0
    def fill_fields(self):

        search_word = self.filterEdit.text()
        if search_word.strip() != '':
            search_str = stem_str(search_word)
        else:
            search_str = ''

        query = QSqlQuery(self.local_cn)
        LIMIT = 100
        OFFSET = 0
        query.prepare(show_termins_in_link_selector_query)
        query.bindValue(':search_str', search_str + '%')
        query.bindValue(':linked', 1)
        query.bindValue(':limit', LIMIT.__str__())
        query.bindValue(':offset', OFFSET.__str__())
        if query.exec_():
            self.root.takeChildren()
            while query.next():
                c = QTreeWidgetItem()
                c.setText(0, query.value(0))  # Заглавное слово
                c.setData(1, 0, query.value(1))  # uuid
                self.root.addChild(c)
        else:
            print(query.lastError().text())
            print("not exec")
        self.treeWidget.scrollToTop()
Пример #37
0
def movFacturaCredito( iddoc, subtotal, impuesto, totalcosto ):
    '''
    MOVIMIENTOS CONTABLE PARA LA FACTURA AL CONTADO
    
    (-)subtotal             > entra a Ventas netas: id=173, cod=410 003 000 000
    
    (-)impuesto             > entra a impuesto por pagar:id=133, cod=210 008 001 001 000
    
    (+)total factura                           > entra aCuentas por cobar: id=14 , cod= 110 002 001 000 

    (-)total precio costo   > Sale de Inventario:id=22, cod=110 003 001 000 000
    
    (+)total precio costo                      > entra a Costos de Ventas:id=182, cod=510 001 000 000 000
    
    @param iddoc: El id del documento que genera este movimiento
    @type iddoc: int
    
    @param subtotal: TODO
    @type subtotal: Decimal
    @param impuesto: TODO
    @type impuesto: Decimal
    @param totalcosto: TODO
    @type totalcosto: Decimal

    
    '''
    totalcosto = redondear(totalcosto)
    totalcosto = totalcosto.to_eng_string()
    
    subtotal = redondear(subtotal)
    impuesto = redondear(impuesto)
    
    iddoc = str( iddoc )
    query = QSqlQuery()
    query.prepare( "INSERT INTO cuentasxdocumento (idcuenta,iddocumento,monto) values " +
    "(" + VENTASNETAS + "," + iddoc + ",-:subtotal)," +
    ( "" if impuesto == 0 else "(" + IMPUESTOSXPAGAR + "," + iddoc + ",-:impuesto)," ) +
    "(" + CXCCLIENTE + "," + iddoc + ",:totalpagar)," +
    "(" + INVENTARIO + "," + iddoc + ",-" + totalcosto + ")," +
    "(" + COSTOSVENTAS + "," + iddoc + "," + totalcosto + ")" )
   
    query.bindValue( ":subtotal", subtotal.to_eng_string() )
    query.bindValue( ":impuesto", impuesto.to_eng_string() )
    query.bindValue( ":totalpagar", ( subtotal + impuesto ).to_eng_string() )
    
    
    if not query.exec_():
        print( query.lastError().text() )
        raise Exception( "NO SE PUDIERON INSERTAR LAS CUENTAS CONTABLES" )
Пример #38
0
 def deleteTeacherFromId(self, tId):
     try:
         query = QSqlQuery(self.conn)
         # Delete the teacher
         query.prepare("DELETE FROM teachers WHERE id=:id")
         query.bindValue(":id", tId)
         query.exec_()
         if query.isActive() == False:
             print query.lastError().text()
             return query.lastError().text()
         self.teacherModel.select()
     except Exception, e:
         # TODO: log this instead of printing to console
         print "deleteTeacherFromId FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e)
         return e
Пример #39
0
 def get_by_key(self, key):
     """Get all chord patterns in given key
     @param key: Song key e.g. C major
     @type key: String
     @return: all patterns in key
     @type return: list"""
     query = QSqlQuery()
     query.prepare("SELECT pattern FROM Progressions WHERE key == :key")
     query.bindValue(":key", key)
     success = query.exec_()
     if not success:
         pass  # TODO
     patterns = [] 
     while query.next():
         patterns.append(query.value(0))
     return patterns
Пример #40
0
def main():
    db = QSqlDatabase.addDatabase("QMYSQL")
    db.setHostName(connectionInfo['host'])
    db.setPort(connectionInfo['port'])
    db.setDatabaseName(connectionInfo['database'])
    db.setUserName(connectionInfo['user'])
    db.setPassword(connectionInfo['password'])
    db.open()
    query = QSqlQuery()
    for i in range(1, 101):
        for k in xrange(random.randint(1, 10)):
            query.prepare("INSERT INTO Cure (client_id, Services_id) "
                          "VALUES (:client_id, :Services_id)")
            query.bindValue(":client_id", i)
            query.bindValue(":Services_id", random.randint(1, 200))
            query.exec_()
Пример #41
0
 def deleteSelectionsFromEntryId(self, entryId):
     """Deletes all selections that reference entryId"""
     try:
         query = QSqlQuery(self.conn)
         query.prepare("DELETE FROM selections \
             WHERE entry_id=:id")
         query.bindValue(":id", entryId)
         query.exec_()
         if query.isActive() == False:
             print query.lastError().text()
             return query.lastError().text()
         return ""
     except Exception, e:
         # TODO: log this instead of printing to console
         print "deleteSelectionsFromEntryId FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e)
         return e
Пример #42
0
 def get_by_key(self, key):
     """Get all chord patterns in given key
     @param key: Song key e.g. C major
     @type key: String
     @return: all patterns in key
     @type return: list"""
     query = QSqlQuery()
     query.prepare("SELECT pattern FROM Progressions WHERE key == :key")
     query.bindValue(":key", key)
     success = query.exec_()
     if not success:
         pass  # TODO
     patterns = []
     while query.next():
         patterns.append(query.value(0))
     return patterns
Пример #43
0
 def compare_pwd(self):
     """
     function that compares the current password to the one the user is trying to change
     :return: bool True if password matches False if not
     """
     pwd_query = QSqlQuery()
     pwd_str = "SELECT userpwd FROM tblUsers WHERE username =:user"
     pwd_query.prepare(pwd_str)
     pwd_query.bindValue(":user", self.user, QSql.Out)
     pwd_query.exec_()
     pwd_query.first()
     old_pwd = pwd_query.value(0)
     if self.old_input_pwd != old_pwd:
         return False
     else:
         return True
Пример #44
0
 def add_new_machine(self, client, machine, selldate, deltamonth,
                     anticiped):
     """Insert a single row into the CLIENTS table"""
     query = QSqlQuery(
         'INSERT INTO clients (client, machine, selldate, '
         'deltamonth, anticiped) VALUES (:client, :machine, :selldate, '
         ':deltamonth, :anticiped)'
         '', self._db)
     query.bindValue(':client', QVariant(client))
     query.bindValue(':machine', QVariant(machine))
     query.bindValue(':selldate', QVariant(selldate))
     query.bindValue(':deltamonth', QVariant(deltamonth))
     query.bindValue(':anticiped', QVariant(anticiped))
     if not query.exec_():
         raise StandardError('SYNTAX ERROR')
     self.update_db_content()
Пример #45
0
def set_ganancia( value, article_id ):
    """
    Actualiza el costo agregado GANANCIA de un articulo
    @param article_id: El Index del record del tableView
    @param value: El Valor a guardar en el record del Index        
    """
    query = QSqlQuery()
    if not query.prepare( """
    UPDATE articulos 
    SET ganancia= :value 
    WHERE idarticulo= %d
    """ % article_id ):
        raise Exception( query.lastError().text() )
    query.bindValue( ":value", value )

    if not query.exec_():
        raise Exception( query.lastError().text() )
Пример #46
0
    def updateDescription( self, description ):
        if self.itemData[IDCUENTA] == 0:
            return False
        query = QSqlQuery()
        if not query.prepare( """
        UPDATE cuentascontables SET descripcion = :desc WHERE idcuenta = :id LIMIT 1
        """ ):
            return False
        query.bindValue( ":desc", description.strip() )
        query.bindValue( ":id", self.itemData[IDCUENTA] )
        if not query.exec_():
            return False
        if query.numRowsAffected() < 1:
            return False

        self.description = description
        return True
Пример #47
0
def set_activo( value, _id ):
    """
    Actualiza el estado de un articulo
    @param id: EL index del record del tableview 
    @param value: El valor a guardar en el record del index 
    """
    query = QSqlQuery()
    if not query.prepare( """
    UPDATE articulos 
    SET activo=:value 
    WHERE idarticulo=:idarticulo"""
     ):
        raise Exception( query.lastError().text() )
    query.bindValue( ":value", value )
    query.bindValue( ":idarticulo", _id )
    if not query.exec_():
        raise Exception( query.lastError().text() )
Пример #48
0
 def data(self, index, role=None):
     """return a QVariant saying if exists a payment at index.(row|column)"""
     if not index.isValid() or role not in (Qt.DisplayRole,
                                            Qt.CheckStateRole):
         return QVariant()
     #self.update_db_content()
     # find the month from the row number
     month_year = QDate().fromString(
         self.headerData(index.row(), Qt.Vertical, role).toString(),
         'MMMM yyyy')
     month = month_year.month()
     year = month_year.year()
     # find the client from the column number
     header_infos = self.headerData(index.column(), Qt.Horizontal,
                                    role).toString().split('\n')
     client = header_infos[0]
     machine = header_infos[1]
     selldate = QDate.fromString(header_infos[2], 'd MMMM yyyy')
     deltamonth = int(header_infos[3][5:-5])  # [len('Ogni '):-len(' mesi')]
     anticiped = header_infos[4][10:-6] == 'anti'  # 'Pagamento ':-'cipato'
     query = QSqlQuery(
         'SELECT expected_datepayd, effective_datepayd FROM '
         'payments WHERE clients_client = :client AND clients_machine = '
         ':machine AND clients_selldate = :selldate AND '
         'expected_datepayd BETWEEN :datebefore AND :dateafter', self._db)
     query.bindValue(':client', QVariant(client))
     query.bindValue(':machine', QVariant(machine))
     query.bindValue(':selldate', QVariant(selldate))
     # primo giorno del mese
     d = QDate(year, month, 1)
     query.bindValue(':datebefore', QVariant(d))
     # ultimo giorno del mese
     query.bindValue(':dateafter', QVariant(d.addMonths(1).addDays(-1)))
     if not query.exec_():
         raise StandardError('SYNTAX ERROR')
     if not query.first():
         return QVariant()
     expected_datepayd = query.value(0).toDate()
     payed = not query.isNull(1)
     effective_datepayd = query.value(1).toDate()
     if role == Qt.CheckStateRole:
         return QVariant(Qt.Checked if payed else Qt.Unchecked)
     else:  # DisplayRole
         date = effective_datepayd if payed else expected_datepayd
         return QVariant(date.toString('d MMMM yyyy'))
Пример #49
0
    def get_dds_data_list(self, id_objet, topic_name):
        query = QSqlQuery(self.db)

        # "DATA LIKE" to match things like AlarmeV2 even when the topic_name passed is Alarme
        query.prepare(
            'SELECT * FROM DDS WHERE idObjet = :idObjet AND topicName LIKE "{0}%"'
            .format(topic_name))
        query.bindValue(":idObjet", id_objet)
        # query.bindValue(":topicName", topic_name)

        if not query.exec_():
            logging.error("%s", query.lastError().text())

        dds_data_list = []
        while query.next():
            dds_data = unicode(query.record().value(3).toString())
            dds_data_list.append(dds_data)

        return dds_data_list
Пример #50
0
def getDefinition(uuid, oc):
    remote_sql = QSqlQuery(get_remote_connection())
    if oc:
        remote_sql.prepare(get_definition_query_OC)
    else:
        remote_sql.prepare(get_definition_query)
    remote_sql.bindValue(":uuid", uuid.__str__())
    if remote_sql.exec_():
        if remote_sql.next():
            definition = remote_sql.value(0).__str__()
            definition = definition.replace('&nbsp;', ' ')
            definition = definition.replace(' </A>', '</A> ')
            fs = re.findall(r'<[Aa][^>]*> ', definition)
            for f in fs:
                new_f = f.strip()
                definition = definition.replace(f, new_f)
            return definition
        else:
            return None
    else:
        return None
Пример #51
0
 def fill_fields(self):
     query = QSqlQuery(get_remote_connection())
     if self.onlyClassified:
         query.prepare(get_name_and_short_def_query_OC)
     else:
         query.prepare(get_name_and_short_def_query)
     query.bindValue(':uuid', self.uuid)
     if query.exec_():
         if query.next():
             self.mainWordLabel.setText('Ссылка на термин: <b>' + query.value(0) + "</b>")
             short_def = remove_all_tags(query.value(1))
             if len(query.value(1)) < 299:
                 self.definitionLabel.setText("Определение: " + short_def)
             else:
                 self.definitionLabel.setText("Определение: " + short_def + '...')
         else:
             self.mainWordLabel.setText('Термин не найден')
             self.definitionLabel.setText("Возможно термин, на который ведет данная ссылка был удален или"
                                          " Вы подключились к другой БД. Пожалуйста, обновите список терминов.")
     else:
         print(query.lastError().text())
         print(query.lastQuery())
Пример #52
0
    def save(self):
        resultado = False
        try:
            if not self.database.isOpen():
                if not self.database.open():
                    raise UserWarning( u"No se pudo abrir la conexión "\
                                       + "con la base de datos" )
            
            if not self.database.transaction():
                raise Exception( u"No se pudo comenzar la transacción" )
            
                    
                    
            
            query = QSqlQuery()
            

            if not query.prepare( """
            UPDATE preciosproducto 
            SET activo = 0
            WHERE idproducto = :id;
            """ ):
                raise Exception( "No se pudo preparar la consulta para actualizar" )
            query.bindValue( ":id", self.articuloId)
            
            if not query.exec_():
                raise Exception( "No se pudo desactivar el precio actual" )
            
            
            if not query.prepare( """
            INSERT INTO preciosproducto(idproducto,precio,unidadesxcaja)
            VALUES (:id,:precio,:cantidad);
            """ ):
                raise Exception( "No se pudo preparar la consulta para insertar los nuevos precios" )
            query.bindValue( ":id", self.articuloId)
            query.bindValue( ":precio", self.precio)
            query.bindValue( ":unidades", self.unidades )

            if not query.exec_():
                raise Exception( "No se pudo insertar el nuevo precio" )
     
            if not self.database.commit():
                raise Exception( "No se pudo hacer commit" )

            resultado = True
        except UserWarning as inst:
            self.database.rollback()
            print  unicode( inst ) 
            QMessageBox.critical( self, qApp.organizationName(), unicode( inst ) )
            resultado = False
        finally:
            if self.database.isOpen():
                self.database.close()
        return resultado
Пример #53
0
    def save(self, iddocumento, linea):
        """
        Este metodo guarda la linea en la base de datos
        @param iddocumento: el id del documento al que esta enlazada la linea
        """
        if not self.valid:
            raise Exception("Se intento guardar una linea no valida")

        query = QSqlQuery()
        if not query.prepare("""
        INSERT INTO productosxdocumento (iddocumento, idprecioproducto,cantidadcajas,linea) 
        VALUES( :iddocumento, :idarticulo, :unidades,:linea)
        """):
            raise Exception("no esta preparada")

        query.bindValue(":iddocumento", iddocumento)
        query.bindValue(":idarticulo", self.itemId)
        query.bindValue(":unidades", self.quantity * -1)
        query.bindValue(":linea", linea)

        if not query.exec_():
            print(query.lastError().text())
            raise Exception("line %d" % self.itemId)
Пример #54
0
    def show_brokenlinks_in_list(self, page):
        # фильтрация списка терминов
        search_word = self.brokenlinksSearhLine.text()
        if search_word.strip() != '':
            search_str = search_word
        else:
            search_str = ''

        query = QSqlQuery(self.local_cn)
        LIMIT = int(self.brokenlinksComboBox.currentText())
        OFFSET = (page - 1) * LIMIT
        query.prepare(show_brokenlinks_in_list_query)
        query.bindValue(':search_str', search_str + '%')
        query.bindValue(':limit', LIMIT.__str__())
        query.bindValue(':offset', OFFSET.__str__())
        if query.exec_():
            self.root_brokenlinks.takeChildren()
            while query.next():
                c = QTreeWidgetItem()
                c.setText(0, query.value(0))  # Заглавное слово
                c.setData(1, 0, query.value(1))  # uuid
                self.root_brokenlinks.addChild(c)

            pages = 1
            query.prepare(show_brokenlinks_in_list_count_query)
            query.bindValue(':search_str', search_str + '%')
            if query.exec_() and query.next():
                try:
                    pages = math.ceil(query.value(0) / LIMIT)
                except:
                    pages = 1

            self.draw_brokenlinks_paginator(pages, page)
        else:
            print(query.lastError().text())
            print("not exec")
        self.brokenLinksTree.scrollToTop()
Пример #55
0
 def add_song(self, song_name, pattern, key):
     """Add a new song to database
     @param song_name: name of the song to be shown in QListView
     @type song_name: String
     @param pattern: chord pattern of the song
     @type pattern: String
     @param key: key of the song. Used as filter parameter
     @type key: String"""
     query = QSqlQuery()
     query.prepare(
         "INSERT INTO Patterns(song, pattern, key) VALUES (:song, :pattern, :key)"
     )
     query.bindValue(":song", song_name)
     query.bindValue(":pattern", pattern)
     query.bindValue(":key", key)
     success = query.exec_()
     if not success:
         return False  # TODO
     return True
Пример #56
0
 def delete_all_links_to_this_word(self, uuid):
     remote_query = QSqlQuery(self.remote_cn)
     remote_query2 = QSqlQuery(self.remote_cn)
     local_query = QSqlQuery(self.local_cn)
     if self.onlyClassified:
         remote_query.prepare(find_all_termins_contains_link_to_uuid_OC(uuid))
     else:
         remote_query.prepare(find_all_termins_contains_link_to_uuid(uuid))
     local_query.prepare(full_delete_local_termin_by_uuid)
     local_query.bindValue(":uuid", uuid)
     s1, s2 = False, False
     QtSql.QSqlDatabase.database('SQLiteConnection').transaction()
     QtSql.QSqlDatabase.database('PGconnection').transaction()
     if remote_query.exec_():
         s1 = True
         while remote_query.next():
             # update definition
             new_definition = delete_uuid_from_definition(uuid, remote_query.value(1))
             if self.onlyClassified:
                 remote_query2.prepare(update_definition_query_OC)
             else:
                 remote_query2.prepare(update_definition_query)
             remote_query2.bindValue(":uuid", remote_query.value(0))
             remote_query2.bindValue(":definition", new_definition)
     else:
         print(remote_query.lastError().text())
         print(remote_query.lastQuery())
     if local_query.exec_():
         s2 = True
     else:
         print(local_query.lastError().text())
         print(local_query.lastQuery())
     if s1 and s2:
         QtSql.QSqlDatabase.database('SQLiteConnection').commit()
         QtSql.QSqlDatabase.database('PGconnection').commit()
     else:
         QtSql.QSqlDatabase.database('SQLiteConnection').rollback()
         QtSql.QSqlDatabase.database('PGconnection').rollback()
     self.finishTrigger.emit(True)
Пример #57
0
 def _query(self, querystring, **mappings):
     query = QSqlQuery(self.db)
     query.prepare(querystring)
     for key, value in mappings.iteritems():
         query.bindValue(":{}".format(key), value)
     return query
Пример #58
0
 def update_db_content(self):
     """Populate the payment table ensuring all "visible" data"""
     today = QDate.currentDate()
     first_month_day = QDate(today.year(), today.month(), 1)
     datebefore = first_month_day.addMonths(-self.months_before)
     dateafter = first_month_day.addMonths(self.months_after)
     query = QSqlQuery(
         'SELECT client, machine, selldate, deltamonth, '
         'anticiped FROM clients', self._db)
     if not query.exec_():
         raise StandardError('SYNTAX ERROR')
     while query.next():
         client = Client(query)
         payments_date = client.selldate.addMonths(
             0 if client.anticiped else client.deltamonth)
         while payments_date < datebefore:  # ignora date non visibili
             payments_date = payments_date.addMonths(client.deltamonth)
         while payments_date < dateafter:
             query2 = QSqlQuery(
                 'SELECT effective_datepayd FROM payments '
                 'WHERE clients_client = :client AND clients_machine = '
                 ':machine AND clients_selldate = :selldate AND '
                 'expected_datepayd = :expected_datepayd', self._db)
             query2.bindValue(':client', QVariant(client.client))
             query2.bindValue(':machine', QVariant(client.machine))
             query2.bindValue(':selldate', QVariant(client.selldate))
             query2.bindValue(':expected_datepayd', QVariant(payments_date))
             if not query2.exec_():
                 raise StandardError('SYNTAX ERROR')
             if not query2.first():
                 query3 = QSqlQuery(
                     'INSERT INTO payments (clients_client, '
                     'clients_machine, clients_selldate, '
                     'expected_datepayd) VALUES (:client, :machine, '
                     ':selldate, :expected_datepayd)', self._db)
                 query3.bindValue(':client', QVariant(client.client))
                 query3.bindValue(':machine', QVariant(client.machine))
                 query3.bindValue(':selldate', QVariant(client.selldate))
                 query3.bindValue(':expected_datepayd',
                                  QVariant(payments_date))
                 if not query3.exec_():
                     raise StandardError('SYNTAX ERROR')
             payments_date = payments_date.addMonths(client.deltamonth)
     self.emit(SIGNAL("layoutChanged()"))