def creaStrutturaDB(self):
query = QSqlQuery()
if not ("magamaster" in self.db.tables()):
if not query.exec_("""CREATE TABLE magamaster (
id INTEGER PRIMARY KEY AUTOINCREMENT
UNIQUE NOT NULL,
scaff VARCHAR(10) NOT NULL)"""):
QMessageBox.warning(self, "Magazzino",
QString("Creazione tabella fallita!"))
return False
if not ("magaslave" in self.db.tables()):
if not query.exec_("""CREATE TABLE magaslave (
id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
datains DATE NOT NULL,
abbi VARCHAR(50),
angro VARCHAR(50),
desc VARCHAR(100),
qt INTEGER NOT NULL DEFAULT '1',
imp DOUBLE NOT NULL DEFAULT '0.0',
equiv VARCHAR(100),
mmid INTEGER NOT NULL,
fatt VARCHAR(50),
note VARCHAR(200),
FOREIGN KEY (mmid) REFERENCES magamaster)"""):
QMessageBox.warning(self, "Magazzino",
QString("Creazione tabella fallita!"))
return False
QMessageBox.information(self, "Magazzino",
QString("Database Creato!"))
return True
def __init__( self, parentId, parent = None ):
super( AccountsModel, self ).__init__( parent )
query = """
SELECT
cc.codigo,
cc.descripcion,
cc.esdebe,
COUNT(ch.idcuenta) nhijos,
SUM(IFNULL(monto,0)) monto,
cc.padre,
cc.idcuenta
FROM cuentascontables cc
LEFT JOIN cuentascontables ch ON cc.idcuenta = ch.padre
LEFT JOIN cuentasxdocumento cxd ON cc.idcuenta = cxd.idcuenta
WHERE cc.padre = %d
GROUP BY cc.idcuenta
""" % parentId
query = QSqlQuery( query )
query.exec_()
query.first()
self.rootItem = Account(
QModelIndex(),
parentId,
query.value( CODIGO ).toString(),
query.value( DESCRIPCION ).toString(),
Decimal( query.value( MONTO ).toString() ),
query.value( ESDEBE ).toInt()[0],
query.value( HIJOS ).toInt()[0] )
def __init__( self, parent = None ):
super( ReciboDelegate, self ).__init__( parent )
query = QSqlQuery( """
SELECT
idtipomovimiento,
CONCAT(descripcion, ' ' , moneda) as tipopago,
idtipomoneda,
m.simbolo
FROM tiposmoneda m
JOIN tiposmovimientocaja p
;
""" )
self.filtrados = []
query.exec_()
while query.next():
self.filtrados.append( query.value( 1 ).toString() )
self.abonosmodel = QSqlQueryModel()
self.abonosmodel.setQuery( query )
self.proxymodel = QSortFilterProxyModel()
self.proxymodel.setSourceModel( self.abonosmodel )
self.proxymodel.setFilterKeyColumn( 1 )
self.completer = QCompleter()
self.completer.setModel( self.proxymodel )
self.completer.setCompletionColumn( 1 )
self.completer.setCaseSensitivity( Qt.CaseInsensitive )
self.completer.setCompletionMode( QCompleter.UnfilteredPopupCompletion )
query = QSqlQuery( """
SELECT idbanco,descripcion FROM bancos;
""" )
self.bancosmodel = QSqlQueryModel()
self.bancosmodel.setQuery( query )
def recuperar_datos(self):
query = QSqlQuery()
sql = "select cliente_id,fecha,hora,notas from ventas where venta_id=%d" % self.id
if not query.exec_(sql):
print "Error al obtener venta."
return
g = lambda x:query.value(x).toString()
query.next()
self.cliente_id = int(g(0))
self.fecha = g(1)
self.hora = g(2)
self.notas = g(3)
self.productos = {}
sql = """ select codigo,cantidad,descuento from detalle_ventas where venta_id=%d""" % self.id
query.exec_(sql)
for i in range(query.size()):
query.next()
asd = str(g(0))
self.productos[asd] = producto(g(0))
self.productos[asd].cantidad = int(g(1))
self.productos[asd].descuento = int(g(2))
self.productos[asd].actualizar_subtotal()
self.actualizar_total()
def results_to_csv_row(self, vals, sql, mode):
query = QSqlQuery(self.db)
query.exec_(sql)
rec = query.record()
if mode == "streets":
avals = [
rec.indexOf(vals[0]),
rec.indexOf(vals[1]),
rec.indexOf(vals[2]),
rec.indexOf(vals[3]),
rec.indexOf(vals[4]),
rec.indexOf(vals[5]),
rec.indexOf(vals[6]),
rec.indexOf(vals[7]),
rec.indexOf(vals[8]),
rec.indexOf(vals[9]),
rec.indexOf(vals[10]),
rec.indexOf(vals[11])
]
while query.next():
line = [
query.value(avals[0]),
query.value(avals[1]),
query.value(avals[2]),
query.value(avals[3]),
query.value(avals[4]),
query.value(avals[5]),
self.format_dates(str(query.value(avals[6]))),
self.format_dates(str(query.value(avals[7]))),
self.format_dates(str(query.value(avals[8]))),
self.format_dates(str(query.value(avals[9]))),
query.value(avals[10]),
query.value(avals[11])
]
self.csv.writerow(line)
else:
avals = [
rec.indexOf(vals[0]),
rec.indexOf(vals[1]),
rec.indexOf(vals[2]),
rec.indexOf(vals[3]),
rec.indexOf(vals[4]),
rec.indexOf(vals[5]),
rec.indexOf(vals[6]),
rec.indexOf(vals[7])
]
while query.next():
line = [
query.value(avals[0]),
query.value(avals[1]),
query.value(avals[2]),
query.value(avals[3]),
query.value(avals[4]),
query.value(avals[5]),
query.value(avals[6]),
query.value(avals[7])
]
self.csv.writerow(line)
def list_data(self, name="", type="", limit="", filter=""):
data_list = []
if name == "":
return ""
query = QSqlQuery(self.db)
if limit != "":
limit = " LIMIT " + limit
if filter != "":
try:
filter_split = filter.split('=')
filter = 'AND data LIKE "%{0}: {1}%"'.format(filter_split[0], filter_split[1])
except:
data_list.append("filter: BAD FILTER")
filter = ""
if type != "":
query.exec_("SELECT * FROM DDS WHERE name = \"{0}\""
"AND type LIKE \"{1}%\"".format(name, type) + filter + limit)
else:
query.exec_("SELECT * FROM DDS WHERE name = \"{0}\"".format(name) + filter + limit)
print query.lastQuery()
while query.next():
type = str(query.value(2).toString())
data = str(query.value(3).toString())
data_list.append("type: " + type + '\n' + data)
return data_list
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 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 init_db(self):
print "here"
sql_query = QSqlQuery('''SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'helferlein';''', self.db)
size = sql_query.size()
print size
if size == 0:
print "db anlegen"
sql_query = QSqlQuery("CREATE DATABASE helferlein;", self.db)
print "tabelle schueler anlegen"
sql_query = QSqlQuery('''CREATE TABLE SCHUELER (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name CHAR(100),
Vorname CHAR(100),
Stufe CHAR(2));''', self.db)
print "tabelle fehlzeit anlegen"
sql_query.prepare('''CREATE TABLE FEHLZEIT (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
schueler_id INT,
Grund CHAR(100),
Beginn CHAR(10),
Ende CHAR(10),
Stunden INT,
ist_Beurlaubung INT,
Attest INT,
klausur_verpasst INT,
Schuljahr CHAR(10));''')
sql_query.exec_()
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 validarCierreMensual(self):
try:
query=QSqlQuery()
q=u"""
SELECT d.iddocumento
FROM documentos d
WHERE d.idtipodoc=%d
AND YEAR(d.fechacreacion)=%s""" %(constantes.IDCIERREMENSUAL,self.fecha.toString( "yyyy" ))
query.prepare(q)
if not query.exec_():
raise UserWarning( "No se pudo ejecutar la consulta para determinar si existe algun cierre en el año" )
if query.size()>0:
mes=self.fecha.addMonths(-1)
q=u"""
SELECT d.iddocumento
FROM documentos d
WHERE d.idtipodoc=%d
AND MONTH(fechacreacion)=%s
AND YEAR(d.fechacreacion)=%s""" %(constantes.IDCIERREMENSUAL,mes.toString( "MM"),self.fecha.toString( "yyyy" ))
query.prepare(q)
if not query.exec_():
raise UserWarning( "No se pudo ejecutar la consulta para determinar si se cerro el mes anterior" )
if query.size()==0:
raise UserWarning( "No se ha cerrado el mes anterior" )
#Verifico si existe un cierre para el mes en proceso
q = """
SELECT
d2.iddocumento
FROM documentos d
JOIN docpadrehijos dp ON d.iddocumento=dp.idpadre
JOIN documentos d2 ON d2.iddocumento=dp.idhijo
WHERE d2.idtipodoc=%d and month(d2.fechacreacion)=%s
LIMIT 1
""" % ( constantes.IDCIERREMENSUAL, self.fecha.toString( "MM" ) )
query.prepare( q )
if not query.exec_():
raise UserWarning( "No se pudo ejecutar la consulta para "\
+ "verificar si existe un cierre contable" )
#El mes actual no se puede cerrar
hoy=QDate.currentDate()
if self.fecha.month()==hoy.month() and self.fecha.year() == hoy.year():
raise UserWarning( "No se puede cerrar el mes en proceso" )
return True
except Exception as inst:
logging.critical( unicode( inst ) )
self.toolBar.removeAction( self.actionSave )
QMessageBox.warning( self,
qApp.organizationName(),unicode(inst))
return False
def make_descobjet_dict(self):
query = QSqlQuery(self.db)
query.exec_("SELECT * FROM DescObjet")
objet_dict = dict()
knownGroupe = []
knownEsapce = []
while query.next():
nom = str(query.record().value(0).toString())
libelle = unicode(query.record().value(1).toString(), "utf-8")
type_objet = str(query.record().value(2).toString())
groupe = str(query.record().value(3).toString())
espace = str(query.record().value(4).toString())
if not groupe in knownGroupe:
objet_dict[groupe] = dict()
knownGroupe.append(groupe)
if not espace in knownEsapce:
objet_dict[groupe][espace] = dict()
knownEsapce.append(espace)
objet_dict[groupe][espace][libelle] = dict()
objet_dict[groupe][espace][libelle]["nom"] = nom
objet_dict[groupe][espace][libelle]["type"] = type_objet
return objet_dict
def SitesHaveFindSpots(db, siteNumbers):
sites = u", ".join(u"'{0}'".format(siteNumber) for siteNumber in siteNumbers)
query = QSqlQuery(db)
query.prepare(u"SELECT COUNT(*) FROM fundstelle WHERE fundortnummer IN ({0})".format(sites))
query.exec_()
query.first()
return query.value(0)
def checkDatabaseTables(self):
if not self.db().tables().contains('ComboBoxItems'):
q = QSqlQuery("CREATE TABLE IF NOT EXISTS ComboBoxItems ( control TEXT, value TEXT)")
q.exec_()
if not self.db().tables().contains('DefaultValues'):
q = QSqlQuery("CREATE TABLE IF NOT EXISTS DefaultValues ( control TEXT, value TEXT)")
q.exec_()
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 get_last_lsg_date(self):
"""
Finds the most recent record insert/update date from either ESU's or street records.
:return: date formatted YYYYMMDD
"""
last_date = 0
last_street = "Select max(Update_Date) as LastChange FROM tblStreet"
query = QSqlQuery(self.db)
query.exec_(last_street)
query.seek(0)
rec = query.record()
last_street_date = query.value(rec.indexOf('LastChange'))
last_esu = "SELECT Max([closure_date]) AS LastClose, Max([entry_date]) AS lastEntry FROM tblESU"
query_esu = QSqlQuery(self.db)
query_esu.exec_(last_esu)
query_esu.seek(0)
rec_esu = query_esu.record()
last_esu_closure = query_esu.value(rec_esu.indexOf('LastClose'))
last_esu_entry = query_esu.value(rec_esu.indexOf('lastEntry'))
if last_street_date > last_date:
last_date = last_street_date
if last_esu_closure > last_date:
last_date = last_esu_closure
if last_esu_entry > last_date:
last_date = last_esu_entry
return last_date
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 CadastrarAluguel(aluguel):
conn = ConexaoSQL
db = conn.getConexao()
db.open()
query = QSqlQuery()
query.prepare("UPDATE Veiculo SET Alugado = 'Sim' WHERE CodigoVeic = "+aluguel.CodigoVeic)
query.exec_()
db.commit()
query.prepare("INSERT INTO Aluguel(DataAluguel, DataPrazo, DataDevolucao, ValorAluguel, "
"ValorMulta, KmEntrada, KmSaida, CodigoCli, CodigoVeic) "
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)")
query.addBindValue(aluguel.DataAluguel)
query.addBindValue(aluguel.DataPrazo)
query.addBindValue(aluguel.DataDevolucao)
query.addBindValue(aluguel.ValorAluguel)
query.addBindValue(aluguel.ValorMulta)
query.addBindValue(aluguel.KmEntrada)
query.addBindValue(aluguel.KmSaida)
query.addBindValue(aluguel.CodigoCli)
query.addBindValue(aluguel.CodigoVeic)
query.exec_()
db.commit()
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 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 deleteRecord(self):
index = self.view.currentIndex()
if not index.isValid():
return
record = self.model.record(index.row())
id = record.value(ID).toInt()[0]
table = self.model.tableName()
query = QSqlQuery()
if table == "deps":
query.exec_(QString("SELECT COUNT(*) FROM employee "
"WHERE deo_id = %1").arg(id))
elif table == "cities":
query.exec_(QString("SELECT COUNT(*) FROM employee "
"WHERE city_id = %1").arg(id))
count = 0
if query.next():
count = query.value(0).toInt()[0]
if count:
QMessageBox.information(self,
QString("Delete %1").arg(table),
(QString("Cannot delete %1<br>"
"from the %2 table because it is used by "
"%3 records")
.arg(record.value(NAME).toString())
.arg(table).arg(count)))
return
self.model.removeRow(index.row())
self.model.submitAll()
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 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 accept(self):
rows = self.sModel.rowCount()
cols = self.sModel.columnCount()
model = self.sModel
mlqry = dict()
qry = None
for r in range(rows):
record = model.record(r)
for c in range(1, cols):
if record.value(c).toString():
if not qry:
qry = "("
elif c > 1 and qry :
qry = "%s AND" % qry
qry = "%s %s %s" % (qry, record.fieldName(c),
record.value(c).toString())
if qry :
mlqry[r] = "%s )" % qry
qry = ""
for q in mlqry.keys():
if not qry:
qry = mlqry[q]
continue
qry = "%s OR %s" % (qry, mlqry[q])
self.resultFilter = qry
query = QSqlQuery()
query.exec_("DROP TABLE filtertable")
if self.dbi:
del self.dbi
QDialog.accept(self)
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 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 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 asd_last_date(self):
"""
get the last update date (e.g. latest of entry or updates date) from db to show in ASD label
:return: string
"""
last_update = 0
tbl_names = ["tblMAINT", "tblREINS_CAT", "tblSPEC_DES"]
for tbl_name in tbl_names:
query = QSqlQuery(self.db)
query.exec_(self.get_last_date.format(tbl_name=tbl_name))
query.next()
rec = query.record()
last_entry, last_close = (rec.value('LastEntry'),
rec.value('LastClose'))
if not isinstance(last_entry, QPyNullVariant):
if int(last_entry) > last_update:
last_update = last_entry
if not isinstance(last_close, QPyNullVariant):
if int(last_close) > last_update:
last_update = last_close
last_update_date = datetime.strptime(str(last_update), "%Y%m%d")
asd_date_clean = last_update_date.strftime("%d/%m/%Y")
return asd_date_clean
def DevolverVeiculo(codigoAlug, aluguel):
conn = ConexaoSQL
db = conn.getConexao()
db.open()
select = "SELECT Veiculo.CodigoVeic FROM Aluguel"\
" INNER JOIN Veiculo ON Aluguel.CodigoVeic = Veiculo.CodigoVeic"\
" WHERE Aluguel.CodigoAlug = "+codigoAlug
query = QSqlQuery(select)
while query.next():
codigoVeic = str(query.value(0))
sql = "UPDATE Veiculo SET Alugado = 'Não' WHERE CodigoVeic = "+codigoVeic
query.prepare(sql)
query.exec_()
db.commit()
sql = "UPDATE Aluguel SET DataDevolucao = '"+aluguel.DataDevolucao+"', ValorMulta = '"+aluguel.ValorMulta\
+"', KmSaida = '"+aluguel.KmSaida\
+"' WHERE CodigoAlug = "+codigoAlug
print(sql)
query.prepare(sql)
print
query.exec_()
db.commit()
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 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 setModelData(self,editor,model,index):
curindx = self.editor.currentIndex()
text = self.itemslist[curindx]
model.setData(index, text)
# update the student sn
newClassname = index.sibling(index.row(),1).data()
stusn = index.sibling(index.row(),2).data()
stuname = index.sibling(index.row(),3).data()
query = QSqlQuery(self.db)
strsql = "select count(*), stusn from student where classname='" + newClassname + "' and stuname='" + stuname + "'"
query.exec_(strsql)
query.next()
if query.value(0) == 0: ## the student is not in this class
# print(query.value(0), "count**", strsql)
ret = query.exec_("select max(stusn) from student where classname = '" + newClassname + "'")
query.next()
tmpsn = query.value(0)
if type(tmpsn)== QPyNullVariant:
newstusn = "01"
else:
newstusn = str(int(query.value(0)) + 1).zfill(2)
# if newClassname != self.oldclassname:
model.setData(index.sibling(index.row(), 2), newstusn)
else:
model.setData(index.sibling(index.row(), 2), query.value(1))
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 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 nueva_venta(self, cliente_id): query = QSqlQuery() sql = "select max(venta_id) from ventas" query.exec_(sql) query.next() max_id = int(query.value(0).toString()) + 1 self.cliente_id = cliente_id self.id = max_id
def tableHasField(dbhandle, table, field):
"""does the table contain a column named field?"""
query = QSqlQuery(dbhandle)
query.exec_('select * from %s' % table)
record = query.record()
for idx in range(record.count()):
if record.fieldName(idx) == field:
return True
def list_names(self):
query = QSqlQuery(self.db)
query.exec_("SELECT * FROM DescObjet")
names_list = []
while query.next():
name = str(query.record().value(0).toString())
names_list.append(name)
return names_list
def tableHasField(self, table, field):
"""does the table contain a column named field?"""
query = QSqlQuery(self)
query.exec_('select * from %s' % table)
record = query.record()
for idx in range(record.count()):
if record.fieldName(idx) == field:
return True
def createTables(self):
query = QSqlQuery()
query.exec_("create table tablesManage (tableName varchar(50) primary key)")
query.exec_("create table downloadTable (title varchar(50), progress varchar(20), size varchar(20), remain varchar(20), album varchar(20), songLink varchar(30), musicPath varchar(30),netSpeed varchar(30),musicId varchar(10))")
query.exec_("insert into tablesManage values('在线试听')")
query.exec_("insert into tablesManage values('默认列表')")
query.exec_("insert into tablesManage values('喜欢歌曲')")
query.exec_("commit")
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 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 list_name_libelle_type(self):
query = QSqlQuery(self.db)
query.exec_("SELECT * FROM DescObjet")
names_list = []
while query.next():
name = str(query.record().value(0).toString())
libelle = str(query.record().value(1).toString())
type_objet = str(query.record().value(2).toString())
names_list.append('name: "{0}"\nlibelle: "{1}"\ntypeObjet: "{2}"'.format(name, libelle, type_objet))
return names_list
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 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 getRecord(self, id_):
query = QSqlQuery(self.handle)
query.exec_(self.fetch_query % id_)
result = None
while query.next():
# 22: number of total fields, skip first id field
result = [query.value(i).toString() for i in range(1,22)]
result[12] = query.value(13).toByteArray()
return result
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 make_objet_table(self):
query = QSqlQuery(self.db)
query.exec_("SELECT * FROM DescObjet")
table = dict()
while query.next():
name = str(query.record().value(0).toString())
libelle = unicode(query.record().value(1).toString(), "utf-8")
type_objet = str(query.record().value(2).toString())
table[name] = dict()
table[name]["libelle"] = libelle
table[name]["type"] = type_objet
return table
def AtualizarCliente(codigoCli, cliente):
conn = ConexaoSQL
db = conn.getConexao()
db.open()
query = QSqlQuery()
query.prepare("UPDATE Cliente SET Nome = '" + cliente.Nome +
"', CPF = '" + cliente.CPF + "', Endereco = '" +
cliente.Endereco + "', Email = '" + cliente.Email +
"', Telefone = '" + cliente.Telefone +
"' WHERE CodigoCli = " + codigoCli)
query.exec_()
db.commit()
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 export_meta(self):
"""
Exports Metadata records and writes them to CSV in new line
:return:
"""
if self.version == 75:
sqlgazmetadata = "SELECT * from tblGazMetadata;"
query = QSqlQuery(self.db)
query.exec_(sqlgazmetadata)
query.first()
rec = query.record()
now_format = str(datetime.datetime.now().date())
aval = [rec.indexOf("name"),
rec.indexOf("scope"),
rec.indexOf("territory"),
rec.indexOf("owner"),
rec.indexOf("custodian"),
rec.indexOf("coord_sys"),
rec.indexOf("coord_units"),
rec.indexOf("metadata_date"),
rec.indexOf("class_scheme"),
rec.indexOf("code_scheme"),
rec.indexOf("current_date"),
rec.indexOf("gaz_language"),
rec.indexOf("charset"),
rec.indexOf("custodian_code")]
ameta = [29,
query.value(aval[0]),
query.value(aval[1]),
query.value(aval[2]),
query.value(aval[3]),
query.value(aval[4]),
query.value(aval[13]),
query.value(aval[5]),
query.value(aval[6]),
self.format_date(query.value(aval[7])),
query.value(aval[8]),
query.value(aval[9]),
now_format,
query.value(aval[12]),
query.value(aval[11])]
self.csv.writerow(ameta)
else:
pass
def login(self): # conexion a la base y ejecuta un query
query = QSqlQuery()
query.exec_("select count(*) from Cliente where IdCliente = " +
self.txt_id.text() + " and Contraseña = '" +
self.txt_pass.text() + "';")
while query.next():
count = str(query.value(0))
if count == "1":
self.p.show()
self.hide()
else:
print("Error")
QMessageBox.information(self, "Error:", "Datos incorrectos")
self.txt_id.clear()
self.txt_pass.clear()
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 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 headerData(self, section, orientation, role=None):
"""Generate the months on the rows and the clients on the columns"""
# pylint: disable-msg=C0103
if role not in (Qt.DisplayRole, Qt.CheckStateRole):
return QVariant()
if orientation == Qt.Horizontal:
query = QSqlQuery(
'SELECT client, machine, selldate, deltamonth, '
'anticiped FROM clients ORDER BY client, machine, '
'selldate', self._db)
if not query.exec_():
raise StandardError('SYNTAX ERROR')
if not query.first():
raise StandardError("Non c'e' manco un risultato?")
if not query.seek(section):
raise StandardError('Not enough elements into the table')
client = Client(query)
return QVariant(
'%s\n%s\n%s\nOgni %s mesi\nPagamento %scipato' %
(client.client, client.machine,
client.selldate.toString('d MMMM yyyy'), client.deltamonth,
'anti' if client.anticiped else 'posti'))
else:
return QVariant(QDate.currentDate().addMonths(
section - self.months_before).toString('MMMM yyyy'))
def getEDGVDbsFromServer(self):
#Can only be used in postgres database.
try:
self.checkAndOpenDb()
except:
return []
query = QSqlQuery(self.gen.getDatabasesFromServer(),self.db)
dbList = []
while query.next():
dbList.append(query.value(0))
edvgDbList = []
for database in dbList:
db = None
db = QSqlDatabase("QPSQL")
db.setDatabaseName(database)
db.setHostName(self.db.hostName())
db.setPort(self.db.port())
db.setUserName(self.db.userName())
db.setPassword(self.db.password())
if not db.open():
QgsMessageLog.logMessage('DB :'+database+'| msg: '+db.lastError().databaseText(), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
query2 = QSqlQuery(db)
if query2.exec_(self.gen.getEDGVVersion()):
while query2.next():
version = query2.value(0)
if version:
edvgDbList.append((database,version))
else:
QgsMessageLog.logMessage(self.tr('Problem accessing database: ') +database+'\n'+query2.lastError().text(), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
return edvgDbList
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 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 create_database():
"""Create table function."""
db = CadastaDatabase.open_database()
query_fields = ('id INTEGER PRIMARY KEY AUTOINCREMENT,'
'name varchar(20) NOT NULL,'
'email varchar(20),'
'phone varchar(20)')
query_string = 'create table %(TABLE)s (%(query_field)s)' % \
{
'TABLE': Contact.__name__,
'query_field': query_fields
}
query = QSqlQuery()
query.exec_(query_string)
if db:
db.close()
