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
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() )
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)
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
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)
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
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)
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)
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
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)
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()
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)
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()
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()
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
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
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
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
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_()
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
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
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()
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()
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
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
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
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)
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
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
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.
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" )
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" )
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 )
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
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.
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()
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" )
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
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
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_()
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
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
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
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()
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() )
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
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() )
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'))
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
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(' ', ' ')
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
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())
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
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)
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()
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
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)
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
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()"))