def updateTeacher(self, teacherId, teacher):
"""Updates a Teacher record"""
try:
query = QSqlQuery(self.conn)
query.prepare("UPDATE teachers \
SET first_name=:first, last_name=:last, address=:address, city=:city, postal_code=:postal,\
daytime_phone=:daytimePhone, evening_phone=:eveningPhone, email=:email \
WHERE id=:id")
query.bindValue(":first", teacher.first)
query.bindValue(":last", teacher.last)
query.bindValue(":address", teacher.address)
query.bindValue(":city", teacher.city)
query.bindValue(":postal", teacher.postal)
query.bindValue(":daytimePhone", teacher.daytimePhone)
query.bindValue(":eveningPhone", teacher.eveningPhone)
query.bindValue(":email", teacher.email)
query.bindValue(":id", teacherId)
query.exec_()
if query.isActive() == False:
print query.lastError().text()
return query.lastError().text()
self.teacherModel.select()
return ""
except Exception, e:
# TODO: log this instead of printing to console
print "updateTeacher FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e)
return 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 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 saveComboValues(self, combobox, text):
"""
Save the value of the combo box into the form settings values.
Only saves new values.
"""
comboitems = [combobox.itemText(i) for i in range(combobox.count())]
name = combobox.objectName()
query = QSqlQuery()
query.prepare("SELECT value FROM ComboBoxItems WHERE control = :contorl")
query.bindValue(":control", name)
query.exec_()
log("LAST ERROR")
log(query.lastError().text())
items = []
while query.next():
value = query.value(0).toString()
if not value.isEmpty():
items.append(str(value))
if not text in comboitems and not text in items:
query = QSqlQuery()
query.prepare("INSERT INTO ComboBoxItems (control, value)" "VALUES (:control,:value)")
query.bindValue(":control", name)
query.bindValue(":value", text)
query.exec_()
log("LAST ERROR FOR INSERT")
log(query.lastError().text())
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 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 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 updateGroupParticipant(self, participantId, participant):
"""Updates GroupParticipant record"""
try:
query = QSqlQuery(self.conn)
query.prepare("UPDATE groupparticipants \
SET group_name=:groupName, group_size=:groupSize, school_grade=:schoolGrade,\
average_age=:averageAge, participants=:participants, contact=:contact, earliest_performance_time=:earliestPerformanceTime, latest_performance_time=:latestPerformanceTime \
WHERE id=:id")
query.bindValue(":groupName", participant.groupName)
query.bindValue(":groupSize", participant.groupSize)
query.bindValue(":schoolGrade", participant.schoolGrade)
query.bindValue(":averageAge", participant.averageAge)
query.bindValue(":participants", participant.participants)
query.bindValue(":contact", participant.contact)
query.bindValue(":earliestPerformanceTime", participant.earliestPerformanceTime)
query.bindValue(":latestPerformanceTime", participant.latestPerformanceTime)
query.bindValue(":id", participantId)
query.exec_()
if query.isActive() == False:
print query.lastError().text()
return query.lastError().text()
self.groupParticipantModel.select()
return ""
except Exception, e:
# TODO: log this instead of printing to console
print "updateGroupParticipant FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e)
return e
def addTeacher(self, t):
"""Adds a new Teacher record to the db"""
try:
query = QSqlQuery(self.conn)
query.prepare("INSERT INTO teachers \
(first_name, last_name, address, city, postal_code, daytime_phone, evening_phone, email) \
VALUES (:first, :last, :address, :city, :postal, :daytimePhone, :eveningPhone, :email)")
query.bindValue(":first", t.first)
query.bindValue(":last", t.last)
query.bindValue(":address", t.address)
query.bindValue(":city", t.city)
query.bindValue(":postal", t.postal)
query.bindValue(":daytimePhone", t.daytimePhone)
query.bindValue(":eveningPhone", t.eveningPhone)
query.bindValue(":email", t.email)
query.exec_()
if query.isActive() == False:
print query.lastError().text()
return query.lastError().text()
self.teacherModel.select()
return ""
except Exception, e:
# TODO: log this instead of printing to console
print "addTeacher FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e)
return e
def addGroupParticipant(self, gp):
"""Adds a new GroupParticipant record to the db"""
try:
query = QSqlQuery(self.conn)
query.prepare("INSERT INTO groupparticipants \
(group_name, group_size, school_grade, average_age, participants, contact, earliest_performance_time, latest_performance_time) \
VALUES (:groupName, :groupSize, :schoolGrade, :averageAge, :participants, :contact, :earliestPerformanceTime, :latestPerformanceTime)")
query.bindValue(":groupName", gp.groupName)
query.bindValue(":groupSize", gp.groupSize)
query.bindValue(":schoolGrade", gp.schoolGrade)
query.bindValue(":averageAge", gp.averageAge)
query.bindValue(":participants", gp.participants)
query.bindValue(":contact", gp.contact)
query.bindValue(":earliestPerformanceTime", gp.earliestPerformanceTime)
query.bindValue(":latestPerformanceTime", gp.latestPerformanceTime)
query.exec_()
if query.isActive() == False:
print query.lastError().text()
return query.lastError().text()
self.groupParticipantModel.select()
return ""
except Exception, e:
# TODO: log this instead of printing to console
print "addGroupParticipant FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e)
return e
def loadDefaultData(self, table, data):
query = QSqlQuery()
insertList= "', '".join(data.tableCols)
insertList= "'" + insertList + "'"
colCount= len(data.tableCols)
quest= ""
for i in range(0, colCount):
quest = quest + "?,"
quest= quest[:-1]
sql= "INSERT INTO " + table + "(" + insertList + ") VALUES (" + quest + ");"
# self.updateStatus(sql)
status= query.prepare(sql)
if status == False:
self.updateStatus("Could not prepare material property database table " + table)
self.updateStatus("Database error message: " + query.lastError().text())
for row in data.propDict:
for prop in data.tableCols:
propval= data.propDict[row][prop]
if data.tableColSQLTypes[prop] == 'TEXT':
query.addBindValue(QVariant(QString(propval)))
# self.updateStatus("Setting TEXT property " + prop + " to value " + str(propval) + " in row " + str(row))
elif data.tableColSQLTypes[prop] == 'real':
if (propval == '-'):
propreal= -999.9
else:
propreal= float(propval)
query.addBindValue(QVariant(propreal))
# self.updateStatus("Setting real property " + prop + " to value " + str(propreal) + " in row " + str(row))
status= query.exec_()
if status == False:
self.updateStatus("Could not load property database table " + table + " with " + str(row))
self.updateStatus("Database error message: " + 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 updateParticipant(self, participantId, participant):
"""Updates a Participant record"""
try:
query = QSqlQuery(self.conn)
query.prepare("""UPDATE participants
SET
first_name=:first,
last_name=:last,
address=:address,
city=:city,
postal_code=:postal,
home_phone=:home,
cell_phone=:cell,
email=:email,
date_of_birth=:dob,
school_attending=:schoolAttending,
parent=:parent,
age=:age,
school_grade=:schoolGrade,
group_name=:groupName,
number_participants=:numberParticipants,
earliest_time=:earliestTime,
latest_time=:latestTime,
group_participants=:groupParticipants,
average_age=:averageAge,
contact=:contact
WHERE
id=:id""")
query.bindValue(":first", participant.first)
query.bindValue(":last", participant.last)
query.bindValue(":address", participant.address)
query.bindValue(":city", participant.city)
query.bindValue(":postal", participant.postal)
query.bindValue(":home", participant.home)
query.bindValue(":cell", participant.cell)
query.bindValue(":email", participant.email)
query.bindValue(":dob", participant.dob)
query.bindValue(":schoolAttending", participant.schoolAttending)
query.bindValue(":parent", participant.parent)
query.bindValue(":age", participant.age)
query.bindValue(":schoolGrade", participant.schoolGrade)
query.bindValue(":groupName", participant.groupName)
query.bindValue(":numberParticipants", participant.numberParticipants)
query.bindValue(":earliestTime", participant.earliestPerformanceTime)
query.bindValue(":latestTime", participant.latestPerformanceTime)
query.bindValue(":groupParticipants", participant.participants)
query.bindValue(":averageAge", participant.averageAge)
query.bindValue(":contact", participant.contact)
query.bindValue(":id", participantId)
query.exec_()
if query.isActive() == False:
print query.lastError().text()
return query.lastError().text()
self.participantModel.select()
return ""
except Exception, e:
# TODO: log this instead of printing to console
print "updateParticipant FAILED\n\tquery: {0}\n\terror: {1}".format(query, e)
return e
def updateArticles( self ):
"""
Actualizar la lista de articulos
"""
query = QSqlQuery()
try:
if not self.database.isOpen():
if not self.database.open():
raise UserWarning( u"No se pudo conectar con la "
+ "base de datos" )
self.updateArticleList( query )
self.editmodel.updateLines( query )
providers_model = QSqlQueryModel()
providers_model.setQuery( """
SELECT idpersona, nombre
FROM personas p
WHERE tipopersona = 2 AND activo = 1
""" )
if not providers_model.rowCount() > 0:
raise UserWarning( "No existen proveedores en el sistema" )
self.cbProvider.setModel( providers_model )
self.cbProvider.setModelColumn( 1 )
warehouse_model = QSqlQueryModel()
warehouse_model.setQuery( """
SELECT idbodega, nombrebodega
FROM bodegas b
ORDER BY idbodega
""" )
if not warehouse_model.rowCount() > 0:
raise UserWarning( "No existen bodegas en el sistema" )
self.cbWarehouse.setModel( warehouse_model )
self.cbWarehouse.setModelColumn( 1 )
self.cbWarehouse.setCurrentIndex( -1 )
self.cbProvider.setCurrentIndex( -1 )
except UserWarning as inst:
QMessageBox.warning( self, qApp.organizationName(),
unicode( inst ) )
logging.error( query.lastError().text() )
logging.error( unicode( inst ) )
self.cancel()
except Exception as inst:
QMessageBox.critical( self, qApp.organizationName(),
"Hubo un error fatal al tratar de actualizar la lista " \
+ "de articulos, el sistema no puede recuperarse" \
+ " y sus cambios se han perdido" )
logging.error( query.lastError().text() )
logging.critical( unicode( inst ) )
self.cancel()
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 movAbonoDeCliente( iddoc, total, retencion,ganancia ):
'''
MOVIMIENTOS CONTABLE PARA LA UN RECIBO
(-)total > entra a Ventas netas: id=173, cod=410 003 000 000
(+)retencion > entra a retencion pagadas por anticipado: id=118, cod=210 006 000 000 000
(+)total pagado > entra a caja genera:id=5, cod=110 001 001 000
@param iddoc: El id del documento que genera este documento
@type iddoc: int
@param total: TODO
@type total: Decimal
@param retencion: TODO
@type retencion: Decimal
'''
total = redondear (total)
retencion = redondear(retencion)
ganancia = redondear (ganancia)
iddoc = str( iddoc )
query = QSqlQuery()
query.prepare( "INSERT INTO cuentasxdocumento (idcuenta,iddocumento,monto) values " +
"(" + CXCCLIENTE + "," + iddoc + ",-:total)," +
"(" + CAJAGENERAL + "," + iddoc + ",:totalpagar)" )
query.bindValue( ":total", total.to_eng_string() )
query.bindValue( ":totalpagar", ( total - retencion + ganancia ).to_eng_string() )
if not query.exec_():
print( iddoc )
print( query.lastError().text() )
raise Exception( "No fue posible insertar las cuentas Caja, Clientes para el recibo" )
if ganancia != 0 :
query.prepare( "INSERT INTO cuentasxdocumento (idcuenta,iddocumento,monto) values " +
"(" + PRODUCTOSFINANCIEROS + "," + iddoc + ",-:ganancia)")
query.bindValue( ":ganancia", ganancia.to_eng_string() )
if not query.exec_():
print( query.lastError().text() )
raise Exception( "No fue posible insertar las cuentas Productos financieros para el recibo" )
if retencion !=0 :
query.prepare( "INSERT INTO cuentasxdocumento (idcuenta,iddocumento,monto) values " +
"(" + RETENCIONPAGADA + "," + iddoc + ",:retencion)")
query.bindValue( ":retencion", retencion.to_eng_string() )
if not query.exec_():
print( query.lastError().text() )
raise Exception( "No fue posible insertar las cuentas Rencion Pagada para el recibo" )
def loadAssociatedFeatures(self, complex):
'''
Loads all the features associated to the complex
complex: complex class name
'''
self.checkAndOpenDb()
associatedDict = dict()
#query to get the possible links to the selected complex in the combobox
complexName = complex.replace('complexos_', '')
sql = self.gen.getComplexLinks(complexName)
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(self.tr("Problem loading associated features: ")+query.lastError().text())
while query.next():
#setting the variables
complex_schema = query.value(0)
complex = query.value(1)
aggregated_schema = query.value(2)
aggregated_class = query.value(3)
column_name = query.value(4)
if aggregated_class.split('_')[-1] not in ['p', 'l', 'a']:
continue
#query to obtain the created complexes
sql = self.gen.getComplexData(complex_schema, complex)
complexQuery = QSqlQuery(sql, self.db)
if not complexQuery.isActive():
self.db.close()
raise Exception(self.tr("Problem executing query: ")+complexQuery.lastError().text())
while complexQuery.next():
complex_uuid = complexQuery.value(0)
name = complexQuery.value(1)
if not (complex_uuid and name):
continue
associatedDict = self.utils.buildNestedDict(associatedDict, [name, complex_uuid, aggregated_class], [])
#query to obtain the id of the associated feature
sql = self.gen.getAssociatedFeaturesData(aggregated_schema, aggregated_class, column_name, complex_uuid)
associatedQuery = QSqlQuery(sql, self.db)
if not associatedQuery.isActive():
self.db.close()
raise Exception(self.tr("Problem executing query: ")+associatedQuery.lastError().text())
while associatedQuery.next():
ogc_fid = associatedQuery.value(0)
associatedDict = self.utils.buildNestedDict(associatedDict, [name, complex_uuid, aggregated_class], [ogc_fid])
return associatedDict
def on_buttonBox_accepted( self ):
"""
Agrega una apertura de caja
"""
try:
query = QSqlQuery()
query = QSqlQuery( """
SELECT
MAX(CAST(ndocimpreso AS SIGNED))+1
FROM documentos d
WHERE idtipodoc=17
;
""" )
if not query.exec_():
raise Exception( "No se puedo prepara el query del numero de cierre" )
query.first()
ndocimpreso = query.value( 0 ).toString()
if ndocimpreso == "0" :
ndocimpreso = "1"
if not query.prepare( """
INSERT INTO documentos(ndocimpreso,total,fechacreacion,
idtipodoc,idusuario,idcaja,observacion)
VALUES(:ndocimpreso,:total,:fecha,:tipodoc,
:usuario,:caja,:observacion)""" ):
raise Exception( query.lastError().text() )
query.bindValue( ":ndocimpreso", ndocimpreso )
query.bindValue( ":total", self.txtMonto.text() )
query.bindValue( ":fecha", self.dtFechaTime.dateTime().toString( "yyyyMMddhhmmss" ) )
query.bindValue( ":tipodoc", constantes.IDCIERRESESION )
query.bindValue( ":usuario", self.user.uid )
query.bindValue( ":caja", self.query.value( 1 ) )
query.bindValue( ":observacion", self.user2 )
if not query.exec_():
raise Exception( " Insert de cierre " )
idcierre = self.query.lastInsertId().toInt()[0]
if not query.prepare( """
INSERT INTO docpadrehijos(idpadre,idhijo,monto)
VALUES(:idpadre,:idhijo,:monto)""" ):
raise Exception( query.lastError().text() )
query.bindValue( ":idpadre", self.sesion )
query.bindValue( ":idhijo", idcierre )
query.bindValue( ":monto", self.txtMonto.text() )
if not query.exec_():
raise Exception( " Insert de docpadrehijos" )
self.accept()
except Exception as inst:
self.reject()
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 getLastGroupParticipantId(self):
"""Get the id of the most recently added GroupParticipant"""
try:
query = QSqlQuery(self.conn)
query.exec_("SELECT MAX(id) FROM groupparticipants")
if query.isActive() == False:
print query.lastError().text()
return query.lastError().text()
query.next()
participantId = str(query.value(0).toString())
return "g" + participantId
except Exception, e:
# TODO: log this instead of printing to console
print "getLastGroupParticipantId FAILED\n\tquery: {0}\
\n\terror: {1}".format(query.lastQuery(), e)
def getLastEntryId(self):
"""Get the id of the most recently added Entry"""
try:
query = QSqlQuery(self.conn)
query.prepare("SELECT MAX(id) FROM entries")
query.exec_()
if query.isActive() == False:
print query.lastError().text()
return query.lastError().text()
query.next()
entryId = str(query.value(0).toString())
return entryId
except Exception, e:
# TODO: log this instead of printing to console
print "getLastEntryId FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e)
def refresh(self):
"""
Refreshes the network graph. It will force a refresh of the materialized views in the database and then reload
and recreate the graph.
"""
uri = QgsDataSourceURI(self.nodeLayer.dataProvider().dataSourceUri())
db = QSqlDatabase.addDatabase(
"QPSQL") # Name of the driver -- doesn't change
str_connect_option = "requiressl=0;service=" + uri.service()
db.setConnectOptions(str_connect_option)
if not db.open():
self.iface.messageBar().pushMessage(self.tr("Warning"),
db.lastError().text(),
level=QgsMessageBar.CRITICAL)
query_template = "REFRESH MATERIALIZED VIEW qgep.vw_network_segment;"
query = QSqlQuery(db)
if not query.exec_(query_template):
str_result = query.lastError().text()
self.iface.messageBar().pushMessage(self.tr("Warning"),
str_result,
level=QgsMessageBar.CRITICAL)
else:
self.iface.messageBar().pushMessage(
self.tr("Success"),
"vw_network_segment successfully updated",
level=QgsMessageBar.SUCCESS,
duration=2)
query_template = "REFRESH MATERIALIZED VIEW qgep.vw_network_node;"
query = QSqlQuery(db)
if not query.exec_(query_template):
str_result = query.lastError().text()
self.iface.messageBar().pushMessage(self.tr("Warning"),
str_result,
level=QgsMessageBar.CRITICAL)
else:
self.iface.messageBar().pushMessage(
self.tr("Success"),
"vw_network_node successfully updated",
level=QgsMessageBar.SUCCESS,
duration=2)
# recreate networkx graph
self.graph.clear()
self.createGraph()
def dropRole(self, role):
try:
self.checkAndOpenDb()
except:
return
sql = self.gen.dropRole(role)
split = sql.split('#')
query = QSqlQuery(self.db)
for inner in split:
if not query.exec_(inner):
if '2BP01' in query.lastError().text():
#In this case the role is still used by other databases, therefore it shouldn't be dropped.
continue
else:
raise Exception(self.tr('Problem removing profile: ') +role+'\n'+query.lastError().text())
def createRole(self, role, dict):
try:
self.checkAndOpenDb()
except:
return
#making this so the instaciated permissions stay with different names
uuid = str(uuid4()).replace('-', '_')
role += '_'+uuid
sql = self.gen.createRole(role, dict)
split = sql.split(';')
query = QSqlQuery(self.db)
#try to revoke the permissions
try:
self.dropRole(role)
except:
pass
for inner in split:
if not query.exec_(inner):
if '42710' in query.lastError().text():
#In this case the role is already created (duplicate object error). We just need to proceed executing the grants.
continue
else:
raise Exception(self.tr('Problem assigning profile: ') +role+'\n'+query.lastError().text())
def populate_length_lineedit(self, mcl_ref):
"""
Calculate the length of the MCL and populate lineedit with data.
:param mcl_ref: int, id of the MCL to calculate
"""
# Don't do calculation if spatialite version is too low. (libgeos bug)
if lor.get_spatialite_version_as_int(self.db) < 430:
length_text = "Spatialite < 4.3.0"
self.dlg.ui.lengthLineEdit.setText(length_text)
return
# Run query
sql = """
SELECT GLength(geometry) AS length FROM mcl
WHERE mcl_ref = {}
;""".format(mcl_ref)
query = QSqlQuery(sql, self.db)
# Raise exception if query fails
if not query.first():
msg = ("Could not calculate MCL length. Query:\n{}\n"
"Database returned:\n{}".format(sql,
query.lastError().text()))
raise rn_except.MclFormLengthCalculationError(msg)
# Update field
length = query.record().value('length')
length_text = "{:.2f}".format(length)
self.dlg.ui.lengthLineEdit.setText(length_text)
def movArqueo(iddoc, difference):
"""
MOVIMIENTO CONTABLE PARA UN ARQUEO
@param iddoc: El id del documento arqueo que genera estos movimientos
@type iddoc: int
@param difference: La diferencia que existe entre el arqueo y el total de la sesión
@type difference: Decimal
"""
query = QSqlQuery()
iddoc = str(iddoc)
if difference == 0:
raise Exception("Se trato de hacer un movimiento contable de arqueo de monto 0")
elif difference > 0:
query.prepare("INSERT INTO cuentasxdocumento ( idcuenta, iddocumento, monto) VALUES " +
" ( " + CAJAGENERAL + " , " + iddoc + " , " + difference.to_eng_string() + " ) , " +
" ( " + OTROSINGRESOS + " , " + iddoc + " , -" + difference.to_eng_string() + " ) "
)
else:
query.prepare("INSERT INTO cuentasxdocumento ( idcuenta, iddocumento, monto) VALUES " +
" ( " + CAJAGENERAL + " , " + iddoc + " , " + difference.to_eng_string() + " ) , " +
" ( " + PERDIDAS + " , " + iddoc + " , -" + difference.to_eng_string() + " ) "
)
if not query.exec_():
logging.critical(query.lastError().text())
raise Exception("No se pudo ejecutar la consulta para el movimiento contable de arqueo")
def getGeomDict(self, getCentroids=False):
'''
returns a dict like this:
{'tablePerspective' : {
'layerName' :
'''
self.checkAndOpenDb()
sql = self.gen.getGeomTablesFromGeometryColumns()
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting geom tables from db: ") +
query.lastError().text())
geomDict = dict()
geomDict['primitivePerspective'] = self.getGeomTypeDict()
geomDict['tablePerspective'] = dict()
while query.next():
isCentroid = False
srid = query.value(0)
geometryType = query.value(2)
tableName = query.value(3)
tableSchema = tableName.split('_')[0]
geometryColumn = query.value(1)
layerName = '_'.join(tableName.split('_')[1::])
if layerName not in geomDict['tablePerspective'].keys():
geomDict['tablePerspective'][layerName] = dict()
geomDict['tablePerspective'][layerName]['schema'] = tableSchema
geomDict['tablePerspective'][layerName]['srid'] = str(srid)
geomDict['tablePerspective'][layerName][
'geometryColumn'] = geometryColumn
geomDict['tablePerspective'][layerName][
'geometryType'] = geometryType
geomDict['tablePerspective'][layerName][
'tableName'] = tableName
return geomDict
def getStructureDict(self):
'''
Gets database structure according to the edgv version
'''
self.checkAndOpenDb()
classDict = dict()
sql = self.gen.getStructure(self.getDatabaseVersion())
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem getting database structure: ") +
query.lastError().text())
while query.next():
className = str(query.value(0))
classSql = str(query.value(1))
if className.split('_')[0] == 'complexos' or className.split(
'_')[-1] in ['p', 'l', 'a']:
if className not in classDict.keys():
classDict[className] = dict()
classSql = classSql.split(className)[1]
sqlList = classSql.replace('(', '').replace(')', '').replace(
'\"', '').replace('\'', '').split(',')
for s in sqlList:
fieldName = str(s.strip().split(' ')[0])
classDict[className][fieldName] = fieldName
if 'GEOMETRY' in classDict[className].keys():
classDict[className]['GEOMETRY'] = 'geom'
if 'geometry' in classDict[className].keys():
classDict[className]['geometry'] = 'geom'
if 'OGC_FID' in classDict[className].keys():
classDict[className]['OGC_FID'] = 'id'
return classDict
def getGeomColumnTupleList(self, showViews=False):
"""
list in the format [(table_schema, table_name, geometryColumn, geometryType, tableType)]
centroids are hidden by default
"""
self.checkAndOpenDb()
edgvVersion = self.getDatabaseVersion()
sql = self.gen.getGeomColumnTupleList(edgvVersion)
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting geom tuple list: ") +
query.lastError().text())
geomList = []
while query.next():
if edgvVersion in ['2.1.3', 'FTer_2a_Ed']:
geomList.append((query.value(0).split('_')[0],
'_'.join(query.value(0).split('_')[1::]),
query.value(1), query.value(2), 'BASE TABLE'))
else:
geomList.append(
(query.value(0).split('_')[0],
'_'.join(query.value(0).split('_')[1::]), query.value(1),
self.getResolvedGeomType(int(query.value(2))),
'BASE TABLE'))
return geomList
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 getGeomDict(self, getCentroids = False):
'''
returns a dict like this:
{'tablePerspective' : {
'layerName' :
'''
self.checkAndOpenDb()
edgvVersion = self.getDatabaseVersion()
sql = self.gen.getGeomTablesFromGeometryColumns(edgvVersion)
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(self.tr("Problem getting geom tables from db: ")+query.lastError().text())
geomDict = dict()
geomDict['primitivePerspective'] = self.getGeomTypeDict()
geomDict['tablePerspective'] = dict()
while query.next():
isCentroid = False
srid = query.value(0)
if edgvVersion in ('2.1.3','FTer_2a_Ed'):
geometryType = query.value(2)
else:
geometryType = self.getResolvedGeomType(query.value(2))
tableName = query.value(3)
tableSchema = tableName.split('_')[0]
geometryColumn = query.value(1)
layerName = '_'.join(tableName.split('_')[1::])
if layerName not in geomDict['tablePerspective'].keys():
geomDict['tablePerspective'][layerName] = dict()
geomDict['tablePerspective'][layerName]['schema'] = tableSchema
geomDict['tablePerspective'][layerName]['srid'] = str(srid)
geomDict['tablePerspective'][layerName]['geometryColumn'] = geometryColumn
geomDict['tablePerspective'][layerName]['geometryType'] = geometryType
geomDict['tablePerspective'][layerName]['tableName'] = tableName
return geomDict
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 getStructureDict(self):
'''
Gets database structure according to the edgv version
'''
self.checkAndOpenDb()
classDict = dict()
sql = self.gen.getStructure(self.getDatabaseVersion())
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(self.tr("Problem getting database structure: ")+query.lastError().text())
while query.next():
className = str(query.value(0))
classSql = str(query.value(1))
if className.split('_')[0] == 'complexos' or className.split('_')[-1] in ['p','l','a']:
if className not in classDict.keys():
classDict[className]=dict()
classSql = classSql.split(className)[1]
sqlList = classSql.replace('(','').replace(')','').replace('\"','').replace('\'','').split(',')
for s in sqlList:
fieldName = str(s.strip().split(' ')[0])
classDict[className][fieldName]=fieldName
if 'GEOMETRY' in classDict[className].keys():
classDict[className]['GEOMETRY'] = 'geom'
if 'geometry' in classDict[className].keys():
classDict[className]['geometry'] = 'geom'
if 'OGC_FID' in classDict[className].keys():
classDict[className]['OGC_FID'] = 'id'
return classDict
def movDeposito( iddoc, deposito, ctabanco ):
'''
MOVIMIENTOS CONTABLE PARA UNA ENTRADA COMPRA
(-)deposito > sale de Caja genera:id=5, cod=110 001 001 000
(+)deposito > entra a cuenta del banco ctabanco
@param iddoc: El id del documento que genera estos movimientos
@type iddoc: int
@param deposito: TODO
@type deposito: Decimal
@param ctabanco: TODO
@type ctabanco: Decimal
'''
iddoc = str( iddoc )
total = deposito.to_eng_string()
ctabanco = str( ctabanco )
query = QSqlQuery()
query.prepare( "INSERT INTO cuentasxdocumento (idcuenta,iddocumento,monto) values " +
"(" + CAJAGENERAL + "," + iddoc + ",-" + total + ")," +
"(" + ctabanco + "," + iddoc + "," + total + ")" )
if not query.exec_():
print( iddoc )
print( query.lastError().text() )
raise Exception( "NO SE PUDIERON INSERTAR LAS CUENTAS CONTABLES" )
def movKardex(iddoc, total):
'''
MOVIMIENTO CONTABLE PARA UNA ENTRADA DE BODEGA POR LIQUIDACION O ENTRADA LOCAL
@param iddoc: El id del documento que genera estos movimientos
@type iddoc: int
@param total: El total del movimiento
@type total: Decimal
'''
query = QSqlQuery()
iddoc = str(iddoc)
if total == 0:
raise Exception("Se trato de hacer un movimiento de monto 0")
elif total > 0:
query.prepare("INSERT INTO cuentasxdocumento ( idcuenta, iddocumento, monto) VALUES " +
" ( " + INVENTARIO + " , " + iddoc + " , " + total.to_eng_string() + " ) , " +
" ( " + OTROSINGRESOS + " , " + iddoc + " , -" + total.to_eng_string() + " ) ")
else:
query.prepare("INSERT INTO cuentasxdocumento ( idcuenta, iddocumento, monto) VALUES " +
" ( " + INVENTARIO + " , " + iddoc + " , " + total.to_eng_string() + " ) , " +
" ( " + PERDIDAS + " , " + iddoc + " , -" + total.to_eng_string() + " ) ")
if not query.exec_():
logging.critical(query.lastError().text())
raise Exception("No se pudo ejecutar la consulta para el movimiento contable de kardex")
def data( self ):
data = {}
fila = self.tblCuenta.selectionModel().currentIndex().row()
fecha = self.dtPicker.date()
data['banco'] = self.filtermodel.index( fila, 0 ).data().toString()
data['id_cuenta_contable'] = self.filtermodel.index( fila, 4 ).data().toInt()[0]
data['codigo_cuenta_contable'] = self.filtermodel.index( fila, 3 ).data().toString()
data['cuenta_bancaria'] = self.filtermodel.index( fila, 5 ).data().toString()
data['fecha'] = QDate( fecha.year(), fecha.month(), fecha.daysInMonth() )
data['moneda'] = self.filtermodel.index( fila, 2 ).data().toString()
if not QSqlDatabase.database().isOpen() and not QSqlDatabase.open():
raise Exception( QSqlDatabase.lastError() )
query = QSqlQuery()
if not query.exec_( "CALL spSaldoCuenta( %d, %s )" % (
data['id_cuenta_contable'],
QDate( data['fecha'].year(), data['fecha'].month(), data['fecha'].daysInMonth() ).toString( "yyyyMMdd" )
)
):
raise Exception( query.lastError().text() )
query.first()
data['saldo_inicial_libro'] = Decimal( query.value( 0 ).toString() )
return data
def set_rdpoly_mcl_links_in_db(self, rd_pol_id, mcl_ref):
"""
Update the fields of the rdpoly table with values for the given
mcl_ref from the mcl table.
:param rd_pol_id: str, rd_pol_id to update
:param mcl_ref: str, mcl_ref to supply values
"""
if config.DEBUG_MODE:
print(
"DEBUG_MODE: Updating rdpoly {} with data from mcl {}".format(
rd_pol_id, mcl_ref))
# Get update values
mcl_attrs = self.get_mcl_attrs_for_rdpoly(mcl_ref)
mcl_attrs['mcl_ref'] = mcl_ref
mcl_attrs['rd_pol_id'] = rd_pol_id
# Update database
sql = """
UPDATE rdpoly SET part_label = "{part_label}",
mcl_cref = {mcl_cref}
WHERE rd_pol_id = {rd_pol_id}
;""".format(**mcl_attrs)
if config.DEBUG_MODE:
print(sql)
query = QSqlQuery(sql, self.db)
if not query.isActive():
msg = "Failed to update rdpoly with mcl data."
msg += "\n\nSQL command:\n\n{}".format(sql)
msg += "\n\nDatabase reply:\n\n{}".format(query.lastError().text())
raise rn_except.RampRdPolyUpdateFailedPopupError(msg)
def getDbsFromServer(self, name):
"""
Gets server databases
name: server name
"""
gen = self.factory.createSqlGenerator(False)
(host, port, user, password) = self.getServerConfiguration(name)
database = 'postgres'
db = self.getPostGISDatabaseWithParams(database, host, port, user,
password)
if not db.open():
QgsMessageLog.logMessage(db.lastError().text(), "DSG Tools Plugin",
QgsMessageLog.CRITICAL)
QMessageBox.critical(
self.iface.mainWindow(), self.tr('Critical'),
self.tr('A problem occurred! Check log for details.'))
query = QSqlQuery(gen.getDatabasesFromServer(), db)
if not query.isActive():
QMessageBox.critical(
self.iface.mainWindow(), self.tr('Critical'),
self.tr("Problem executing query: ") +
query.lastError().text())
dbList = []
while query.next():
dbList.append(query.value(0))
return self.browseServer(dbList, host, port, user, password)
def disassociateComplexFromComplex(self, aggregated_class, link_column,
id):
sql = self.gen.disassociateComplexFromComplex(
'complexos_' + aggregated_class, link_column, id)
query = QSqlQuery(self.db)
if not query.exec_(sql):
raise Exception(
self.tr('Problem disassociating complex from complex: ') +
'\n' + query.lastError().text())
def revokeRole(self, user, role):
try:
self.checkAndOpenDb()
except:
return
sql = self.gen.revokeRole(user, role)
query = QSqlQuery(self.db)
if not query.exec_(sql):
raise Exception(self.tr('Problem revoking profile: ') +role+'\n'+query.lastError().text())
def getTableSchemaFromDb(self, table):
self.checkAndOpenDb()
sql = self.gen.getFullTablesName(table)
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting full table name: ") +
query.lastError().text())
while query.next():
return query.value(0).split('_')[0]
def createUser(self, user, password, isSuperUser):
try:
self.checkAndOpenDb()
except:
return
sql = self.gen.createUser(user, password, isSuperUser)
query = QSqlQuery(self.db)
if not query.exec_(sql):
raise Exception(self.tr('Problem creating user: '******'\n'+query.lastError().text())
def dropDatabase(self, candidateName):
try:
self.checkAndOpenDb()
except:
return
if self.checkSuperUser():
sql = self.gen.dropDatabase(candidateName)
query = QSqlQuery(self.db)
if not query.exec_(sql):
raise Exception(self.tr('Problem dropping database: ') + query.lastError().text())
def removeUser(self, user):
try:
self.checkAndOpenDb()
except:
return
sql = self.gen.removeUser(user)
query = QSqlQuery(self.db)
if not query.exec_(sql):
raise Exception(self.tr('Problem removing user: '******'\n'+query.lastError().text())
def get_descobjet_list(self):
query = QSqlQuery(self.db)
if not query.exec_("SELECT * FROM DescObjet"):
logging.error("%s", query.lastError().text())
descobjet_list = []
while query.next():
dds_data = unicode(query.record().value(0).toString())
descobjet_list.append(dds_data)
return descobjet_list
def show_termins_in_list(self, page):
self.currentPage = page
# фильтрация списка терминов
search_word = self.searchLineEdit.text()
if search_word.strip() != '':
search_str = search_word
else:
search_str = ''
# показ уже обработанных терминов
if self.showLinkedCheck.isChecked():
show_linked = 1
else:
show_linked = 0
query = QSqlQuery(self.local_cn)
LIMIT = int(self.terminsPerPageComboBos.currentText())
OFFSET = (page - 1) * LIMIT
query.prepare(show_termins_in_list_query)
query.bindValue(':search_str', search_str + '%')
query.bindValue(':linked', show_linked)
query.bindValue(':limit', LIMIT.__str__())
query.bindValue(':offset', OFFSET.__str__())
if query.exec_():
self.root.takeChildren()
i = 1
f = QFont()
f.setBold(True)
while query.next():
c = QTreeWidgetItem()
c.setText(0, query.value(0)) # Заглавное слово
c.setData(1, 0, query.value(1)) # uuid
c.setData(2, 0, i) # номерок
if query.value(2) == 1:
c.setFont(0, f)
self.root.addChild(c)
i += 1
pages = 1
query.prepare(show_termins_in_list_count_query)
query.bindValue(':search_str', search_str + '%')
query.bindValue(':linked', show_linked)
if query.exec_() and query.next():
try:
pages = math.ceil(query.value(0) / LIMIT)
except:
pages = 1
self.draw_paginator(pages, page)
else:
print(query.lastError().text())
print("not exec")
self.terminsTreeWidget.scrollToTop()
def createDatabase(self, name):
sql = self.gen.getCreateDatabase(name)
db = self.getDatabase()
#creating the database
query = QSqlQuery(db)
if not query.exec_(sql):
QMessageBox.warning(self, self.tr("Warning!"), query.lastError().text())
db.close()
return False
db.close()
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 findEPSG(self, parameters=dict()):
'''
Finds the database EPSG
'''
self.checkAndOpenDb()
sql = self.gen.getSrid(parameters=parameters)
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem finding EPSG: ") + query.lastError().text())
srid = -1
while query.next():
srid = query.value(0)
return srid
def run_sql(sql, db):
"""
Run SQL query on the database.
:param sql: String of sql
:param db: Open QSql database
return: QSqlQuery object to extract results
"""
if config.DEBUG_MODE:
print(sql)
active_query = QSqlQuery(sql, db)
if active_query.isActive() is False:
raise StandardError('Database query problem: {}'.format(
active_query.lastError().text()))
return active_query
def read(self, abfrage):
sql = abfrage
query = QSqlQuery(sql,self.db)
datensatz = {}
i = 0
while i < query.record().count():
result = []
query.first()
result.append(self.__pystring(query.value(i)))
lastError = query.lastError().text()
if len(lastError) > 1:
QMessageBox.information(None, self.tr('DB-Error'), lastError)
while query.next():
result.append(self.__pystring(query.value(i)))
lastError = query.lastError().text()
if len(lastError) > 1:
QMessageBox.information(None, self.tr('DB-Error'), lastError)
datensatz.update({str(query.record().fieldName(i)).upper(): result})
i = i + 1
return datensatz
def makeValueRelationDict(self, valueDict):
"""
Gets the value relation dictionary. This is necessary for multi valued attributes.
"""
ret = dict()
codes = valueDict['FilterExpression'].replace('code in (', '').replace(
')', '').split(',')
in_clause = ','.join(map(str, codes))
keyColumn = valueDict['Key']
valueColumn = valueDict['Value']
table = valueDict['Layer'][:-17] #removing the date-time characters
uri = QgsDataSourceURI(self.currLayer.dataProvider().dataSourceUri())
if uri.host() == '':
db = QSqlDatabase('QSQLITE')
db.setDatabaseName(uri.database())
sql = 'select code, code_name from dominios_%s where code in (%s)' % (
table, in_clause)
else:
db = QSqlDatabase('QPSQL')
db.setHostName(uri.host())
db.setPort(int(uri.port()))
db.setDatabaseName(uri.database())
db.setUserName(uri.username())
db.setPassword(uri.password())
sql = 'select code, code_name from dominios.%s where code in (%s)' % (
table, in_clause)
if not db.open():
db.close()
return ret
query = QSqlQuery(sql, db)
if not query.isActive():
QMessageBox.critical(
self.iface.mainWindow(), self.tr("Error!"),
self.tr("Problem obtaining domain values: ") +
query.lastError().text())
return ret
while query.next():
code = str(query.value(0))
code_name = query.value(1)
ret[code_name] = code
db.close()
return ret
def getAggregationAttributes(self):
'''
Gets complex link columns
'''
self.checkAndOpenDb()
columns = []
sql = self.gen.getAggregationColumn()
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting aggregation attributes: ") +
query.lastError().text())
while query.next():
value = query.value(0)
columns.append(value)
return columns
def disassociateComplexFromComplex(self, aggregated_class, link_column,
id):
'''
Disassociates a complex from another complex
aggregated_class: aggregated class that will be disassociated
link_column: link column between complex and its aggregated class
id: complex id (uid) to be disassociated
'''
sql = self.gen.disassociateComplexFromComplex(
'complexos_' + aggregated_class, link_column, id)
query = QSqlQuery(self.db)
if not query.exec_(sql):
self.db.close()
raise Exception(
self.tr('Problem disassociating complex from complex: ') +
'\n' + query.lastError().text())
def run_sql(self, query, kwargs={}):
"""
Run SQL query (defined with key 'query' in self.sql_queries) on the
database.
return: QSqlQuery object to extract results
"""
query = self.sql_queries[query]
sql = query.format(**kwargs)
if config.DEBUG_MODE:
print(sql)
active_query = QSqlQuery(sql, self.db)
if active_query.isActive() is False:
raise StandardError('Database query problem: {}'.format(
active_query.lastError().text()))
return active_query
def countElements(self, layers):
try:
self.checkAndOpenDb()
except:
return []
listaQuantidades = []
for layer in layers:
(table,schema)=self.getTableSchema(layer)
if layer.split('_')[-1] in ['p','l','a'] or schema == 'complexos':
sql = self.gen.getElementCountFromLayer(layer)
query = QSqlQuery(sql,self.db)
query.next()
number = query.value(0)
if not query.exec_(sql):
QgsMessageLog.logMessage(self.tr("Problem counting elements: ")+query.lastError().text(), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
listaQuantidades.append([layer, number])
return listaQuantidades
def isComplexClass(self, className):
'''
Checks if a class is a complex class
className: class name to be checked
'''
self.checkAndOpenDb()
#getting all complex tables
query = QSqlQuery(self.gen.getComplexTablesFromDatabase(), self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem executing query: ") +
query.lastError().text())
while query.next():
if query.value(0) == 'complexos_' + className:
return True
return False
