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 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 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_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 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 getDomainDict(self):
try:
self.checkAndOpenDb()
except:
return dict()
if self.getDatabaseVersion() == '2.1.3':
schemaList = ['cb', 'complexos', 'dominios']
elif self.getDatabaseVersion() == 'FTer_2a_Ed':
schemaList = ['pe','ge','ct', 'complexos']
else:
QgsMessageLog.logMessage(self.tr('Operation not defined for this database version!'), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
return
sql = self.gen.validateWithDomain(schemaList)
query = QSqlQuery(sql, self.db)
classDict = dict()
domainDict = dict()
while query.next():
schemaName = str(query.value(0))
className = str(query.value(1))
attName = str(query.value(2))
domainName = str(query.value(3))
domainTable = str(query.value(4))
domainQuery = str(query.value(5))
cl = schemaName+'.'+className
query2 = QSqlQuery(domainQuery,self.db)
while query2.next():
value = int(query2.value(0))
classDict = self.utils.buildNestedDict(classDict,[str(cl),str(attName)],[value])
return classDict
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 btns_click(self, btnid):
# curclassname = self.tabWidget.tabText(0)
query = QSqlQuery(self.db)
# cur = conn.cursor()
today = datetime.date.today()
self.g_curbtn = str(btnid).zfill(2)
if self.g_curbtn not in self.dict_choices:
self.btngroup.button(int(self.g_curbtn)).setStyleSheet(stylesheetstr_new)
query.exec_("select count(*) from tmprecord where stusn='" + str(self.g_curbtn) + "'")
query.next()
if query.value(0) == 0:
query.prepare("insert into tmprecord (classname, stusn, datequestion) values (:classname, :stusn, :datequestion)")
query.bindValue(":classname", self.g_curClassName)
query.bindValue(":stusn", self.g_curbtn)
query.bindValue(":datequestion", today)
query.exec_()
self.dict_choices[self.g_curbtn] = "111"
else:
self.btngroup.button(int(self.g_curbtn)).setStyleSheet(stylesheetstr_old)
self.btngroup.button(int(self.g_curbtn)).setIcon(QIcon())
query.exec_("delete from tmprecord where stusn='"+ str(self.g_curbtn) + "'")
self.dict_choices.pop(self.g_curbtn)
self.btnSysMenu.setFocus()
def llenar_usuarios(self):
query = QSqlQuery()
sql = "select usuario from usuarios"
if query.exec_(sql):
for i in range(query.size()):
query.next()
qn = QListWidgetItem(query.value(0).toString())
self.list_usuarios.addItem(qn)
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 getLayersWithElements(self, layerList):
self.checkAndOpenDb()
lyrWithElemList = []
for lyr in layerList:
schema = self.getTableSchemaFromDb(lyr)
sql = self.gen.getElementCountFromLayer(schema, lyr)
query = QSqlQuery(sql, self.db)
query.next()
if query.value(0) > 1:
lyrWithElemList.appen(lyr)
return lyrWithElemList
def countElements(self, layers):
listaQuantidades = []
for layer in layers:
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 checkSuperUser(self):
try:
self.checkAndOpenDb()
except:
return False
query = QSqlQuery(self.db)
if query.exec_(self.gen.isSuperUser(self.db.userName())):
query.next()
value = query.value(0)
return value
return False
def getLayersWithElements(self, layerList):
self.checkAndOpenDb()
lyrWithElemList = []
for lyr in layerList:
schema=self.getTableSchemaFromDb(lyr)
sql = self.gen.getElementCountFromLayer(schema,lyr)
query = QSqlQuery(sql,self.db)
query.next()
if query.value(0) > 1:
lyrWithElemList.appen(lyr)
return lyrWithElemList
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 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 cols(self, abfrage):
sql = abfrage
query = QSqlQuery(sql,self.db)
query.next()
result = []
i = 0
while i < query.record().count():
result.append(str(query.record().fieldName(i)))
i = i + 1
return result
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 colType(self, abfrage):
sql = abfrage
query = QSqlQuery(sql,self.db)
query.next()
datensatz = {}
i = 0
while i < query.record().count():
fieldType = query.record().field(i).type()
datensatz.update({str(query.record().fieldName(i)).upper(): query.record().field(i).value().typeToName(fieldType)})
i = i + 1
return datensatz
def exito(self):
query=QSqlQuery()
query.exec_("select usuario_id from usuarios where usuario='%s'"%str(self.line_usuario.text()))
query.next()
self.usuario_id=int(query.value(0).toString())
self.qvpdv = ventana_punto_de_venta()
self.qvpdv.usuario_id = self.usuario_id
self.qvpdv.venta.cliente_id=1
self.qvpdv.actualizar_usuario()
self.qvpdv.showMaximized()
self.close()
def setupModelData( self, parent ):
try:
if not QSqlDatabase.database().isOpen():
if not QSqlDatabase.database().open():
raise Exception( "No se pudo conectar con la base"\
+ " de datos" )
parents = [parent]
query = """
SELECT
p.nombre,
p.idcategoria,
COUNT(h.idcategoria) as hijos
FROM categorias p
LEFT JOIN categorias h ON p.idcategoria = h.padre
WHERE p.padre IS NULL
GROUP BY p.idcategoria
"""
query = QSqlQuery( query )
if not query.exec_():
raise Exception( "No se pudieron recuperar las categorias" )
x = 0
while query.next():
parent = parents[-1]
parent.insertChildren( x, 1, [query.value( 0 ).toString(),
query.value( 1 ).toInt()[0]] )
if query.value( 2 ) > 0:
y = 0
childquery = """
SELECT
p.nombre ,
p.idcategoria
FROM categorias p
WHERE p.padre = %d
""" % query.value( 1 ).toInt()[0]
childquery = QSqlQuery( childquery )
childquery.exec_()
while childquery.next():
parent.child( x ).insertChildren( y, 1, [
childquery.value( 0 ).toString(),
childquery.value( 1 ).toInt()[0]] )
y += 1
x += 1
return True
except Exception as inst:
logging.error( unicode( inst ) )
return False
def get_song_pattern(self, song_name):
"""Get chord pattern of given song
@param song_name: name of the song in database
@type song_name: String
@return: pattern
@type return: String"""
query = QSqlQuery()
query.prepare("SELECT pattern FROM Progressions WHERE song == :song")
query.bindValue(":song", song_name)
success = query.exec_()
if not success:
pass # TODO
query.next() # Get only the first result as song name is primary key.
return query.value(0) # pattern is the only column in result set.
def get_song_pattern(self, song_name):
"""Get chord pattern of given song
@param song_name: name of the song in database
@type song_name: String
@return: pattern
@type return: String"""
query = QSqlQuery()
query.prepare("SELECT pattern FROM Progressions WHERE song == :song")
query.bindValue(":song", song_name)
success = query.exec_()
if not success:
pass # TODO
query.next() # Get only the first result as song name is primary key.
return query.value(0) # pattern is the only column in result set.
def startChoice(self, usernum="", oldbtn=""):
# print(oldbtn, 1)
if oldbtn != "":
flag = str(oldbtn)
else:
self.dict_choices = {}
strwhere = " and classname like '" + self.g_curClassName + "'"
allstudent = []
lstrecord = []
query = QSqlQuery(self.db)
query.exec_("select stusn from tmprecord where 1=1 " + strwhere)
while(query.next()):
lstrecord.append(query.value(0))
# print(lstrecord, 'record', "select stusn from student where stusn not in " + str(tuple(lstrecord)))
query.exec_("select stusn from student where stusn not in " + str(tuple(lstrecord)) + strwhere)
while(query.next()):
allstudent.append(query.value(0))
if usernum == "":
nums = int(self.choicenum_text.currentText())
else:
nums = usernum
if nums >= len(allstudent):
query.exec_("delete from tmprecord where 1=1 " + strwhere) #delete tmp date no today
allstudent = []
query.exec_("select stusn from student where 1=1 " + strwhere)
while(query.next()):
allstudent.append(query.value(0))
if oldbtn == "":
random.seed()
lstchoices = random.sample(allstudent, nums)
for ibtn in lstchoices:
self.dict_choices[ibtn] = "111"
self.group_animation.start()
QTimer.singleShot(1200, self.stopmovie)
else:
random.seed()
otherbtn = random.sample(allstudent, 1)[0]
# self.btngroup.button(int(otherbtn)).setFocus()
self.dict_choices.pop(oldbtn)
self.dict_choices[otherbtn] = '111'
self.stopmovie()
self.btnSysMenu.setFocus()
def changeTab(self):
# pass
curtab = self.tabWidget.currentIndex()
# print(curtab, "-")
if curtab == 1: ## when click the second tab page ,then pass.
return
# cur = conn.cursor()
query = QSqlQuery(self.db)
## if current classname is null, then set current tabpage display the first class of classtable
if self.g_curClassName == "":
ret = query.exec_("select classname from classtable")
query.next()
self.g_curClassName = query.value(0)
self.tabWidget.setTabText(0, self.g_curClassName)
# print(1)
strwhere = " and classname like '" + self.g_curClassName + "' ORDER BY stusn"
self.g_curbtn = ""
self.dict_choices = {}
self.studentSnlst = []
## clearn the question data of temp record .
ret= query.exec_("delete from tmprecord where 1=1")
ret = query.exec_("select stusn, stuname from student where 1=1 " + strwhere)
## now update the global data "self.btngroup"
for indx in range(0, 56):
self.btngroup.button(indx+1).setText("")
self.btngroup.button(indx+1).setMyarg(None)
self.btngroup.button(indx+1).setStyleSheet(stylesheetstr_old)
self.btngroup.button(indx+1).setIcon(QIcon())
self.btngroup.button(indx+1).setEnabled(False)
self.studentSnlst.append([indx+1,])
inum = 0
while (query.next()):
inum += 1
self.btngroup.button(inum).setText(query.value(1))
self.btngroup.button(inum).setMyarg(query.value(0))
self.btngroup.button(inum).setStyleSheet(stylesheetstr_old)
self.btngroup.button(inum).setIcon(QIcon())
self.btngroup.button(inum).setEnabled(True)
# print(inum, len(self.btngroup.buttons()))
self.group_animation = groupAnimation(self.studentSnlst, self.btngroup)
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 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 zoom_to_record(self):
"""
Zoom map to extent of street (comprising of multiple esu's)
"""
tbl_idx = self.table_view.selectedIndexes()
if tbl_idx:
source_idx = self.proxy.mapToSource(tbl_idx[0])
usrn = source_idx.data()
# Query db for ESU's which make up the street
if usrn and usrn != 0:
query_str = "SELECT esu_id FROM lnkESU_STREET WHERE usrn = %s AND currency_flag = 0;" % usrn
query = QSqlQuery(query_str, self.db)
# Build query string
q_string = ""
while query.next():
q_string += '"esu_id" = %s OR ' % str(query.value(0))
q_string = q_string[:-3]
# Get ref to ESU layer
esu_layer = QgsMapLayerRegistry.instance().mapLayersByName(
'ESU Graphic')[0]
# Select ESU's + get extent
feats = esu_layer.getFeatures(
QgsFeatureRequest().setFilterExpression(q_string))
feat_ids = []
for feature in feats:
f_id = feature.id()
feat_ids.append(f_id)
esu_layer.setSelectedFeatures(feat_ids)
esu_bbox = esu_layer.boundingBoxOfSelected()
# Set new extent
esu_bbox.scale(1.1) # Zoom out slightly for context
self.parent.iface.mapCanvas().setExtent(esu_bbox)
self.parent.iface.mapCanvas().refresh()
# Close connection to db and goto_record
self.goto_record(close=False)
def populate_comboboxes(self):
"""
Populate comboboxes from db and add blank 0 index
"""
ui_s = self.filter_dlg.ui
query_lst = [("""SELECT name FROM tlkpTOWN
ORDER BY name DESC""", ui_s.townComboBox),
("""SELECT state_desc FROM tlkpSTREET_STATE
ORDER BY state_desc DESC""", ui_s.stateComboBox),
("""SELECT name FROM tlkpLOCALITY
ORDER BY name DESC""", ui_s.localityComboBox),
("""SELECT description FROM tlkpSTREET_REF_TYPE
ORDER BY description DESC""", ui_s.recordTypeComboBox)
]
for query in query_lst:
sql = query[0]
combo = query[1]
combo.clear()
query = QSqlQuery(sql)
while query.next():
value = str(query.value(0))
combo.insertItem(0, value)
combo.insertItem(0, "")
combo.setCurrentIndex(0)
combo.setSizeAdjustPolicy(QComboBox.AdjustToContents)
def getStructureDict(self):
try:
self.checkAndOpenDb()
except:
return dict()
classDict = dict()
sql = self.gen.getStructure(self.getDatabaseVersion())
query = QSqlQuery(sql, self.db)
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 'OGC_FID' in classDict[className].keys():
classDict[className]['OGC_FID'] = 'id'
return classDict
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 isComplexClass(self, className):
#getting all complex tables
query = QSqlQuery(self.gen.getComplexTablesFromDatabase(), self.db)
while query.next():
if query.value(0) == className:
return True
return False
def getStructureDict(self):
try:
self.checkAndOpenDb()
except:
return dict()
classDict = dict()
sql = self.gen.getStructure(self.getDatabaseVersion())
query = QSqlQuery(sql, self.db)
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 'OGC_FID' in classDict[className].keys():
classDict[className]['OGC_FID'] = 'id'
return classDict
def makeValueRelationDict(self, valueDict):
ret = dict()
codes = valueDict['FilterExpression'].replace('code in (', '').replace(')','').split(',')
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())
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())
if not db.open():
db.close()
return ret
in_clause = ','.join(map(str, codes))
query = QSqlQuery('select code, code_name from dominios.%s where code in (%s)' % (table, in_clause), db)
while query.next():
code = str(query.value(0))
code_name = query.value(1)
ret[code_name] = code
db.close()
return ret
def populate_combos(self):
"""
Populate all comboboxes from db lookup tables. Combos sorted alphabetically with a 0 (default None) value at
the top.
"""
for query_str, combo in self.query_lst.iteritems():
query = QSqlQuery(query_str)
all_items = {}
while query.next():
text = str(query.value(0))
value = str(query.value(1))
all_items[text] = value
if '0' in all_items.values():
sorted_items = sorted(all_items.iteritems(),
key=operator.itemgetter(0),
reverse=True)
else:
# The only lookup without a 0 as 'None' item is the street ref type, also this is sorted on the type
sorted_items = sorted(all_items.iteritems(),
key=operator.itemgetter(1),
reverse=True)
default_none = None
for item in sorted_items:
text = item[0]
data = item[1]
if int(data) != 0:
combo.insertItem(0, text, userData=data)
else:
default_none = item
if default_none:
combo.insertItem(0, default_none[0], userData=default_none[1])
combo.setCurrentIndex(0)
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 validate(self, usrn, final_esu_selection=None):
"""
Validates that all ESU's are only attached to a single type-1 or type-2 usrn
:param usrn: USRN
:param final_esu_selection: list of esu's
:return: true if esu links are valid
"""
record_type_combo = self.street_browser.ui.recordTypeComboBox
if final_esu_selection:
esu_list = final_esu_selection
else:
esu_list = self.get_esu_selection(usrn)
# only need to check type 1 and 2 records
record_type = int(
record_type_combo.itemData(record_type_combo.currentIndex()))
if record_type == 1 or record_type == 2: # Type 1 or Type 2 hardcoded!
bad_esu = []
for esu_key in esu_list:
sql = """SELECT tblSTREET.usrn, tblSTREET.street_ref_type FROM lnkESU_STREET
INNER JOIN tblSTREET ON lnkESU_STREET.usrn_version_no = tblSTREET.version_no
AND lnkESU_STREET.usrn = tblSTREET.usrn WHERE lnkESU_STREET.esu_id = %s
AND lnkESU_STREET.currency_flag = 0 AND tblSTREET.currency_flag = 0 AND tblSTREET.usrn != %s""" \
% (esu_key, usrn)
query = QSqlQuery(sql, self.db)
while query.next():
typee = int(query.value(1))
if typee == 1 or typee == 2:
bad_esu.append(esu_key)
if bad_esu:
# esu's found
return False
else:
return True
else:
return True
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 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 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 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 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 __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 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 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 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 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 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 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 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 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 showFeilds_mysql(self):
defaultDB = QSqlDatabase.database()
query = QSqlQuery("desc {}".format(
self.listWidgetTables.currentItem().text()))
qe = query.exec_()
if qe:
self.listWidgetFeilds.clear()
while query.next():
self.listWidgetFeilds.addItem(query.value(0).toString())
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 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 loadAssociatedFeatures(self, complex):
try:
self.checkAndOpenDb()
except:
return dict()
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)
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)
#query to obtain the created complexes
sql = self.gen.getComplexData(complex_schema, complex)
complexQuery = QSqlQuery(sql, self.db)
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)
while associatedQuery.next():
ogc_fid = associatedQuery.value(0)
associatedDict = self.utils.buildNestedDict(
associatedDict, [name, complex_uuid, aggregated_class],
[ogc_fid])
return associatedDict
def getDatabaseVersion(self):
try:
self.checkAndOpenDb()
except:
return '-1'
sqlVersion = self.gen.getEDGVVersion()
queryVersion = QSqlQuery(sqlVersion, self.db)
while queryVersion.next():
version = queryVersion.value(0)
return version
def __init__(self, parent=None, filename=None):
self.installThreads = {}
self.processes = set()
if filename is None:
filename = DataModel.defaultDbFile
self.db = QSqlDatabase.addDatabase('QSQLITE')
print >> sys.stderr, "Open database at %s" % filename
self.db.setDatabaseName(filename)
self.db.open()
query = QSqlQuery(
'SELECT COUNT(*) '
'FROM sqlite_master '
'WHERE type="table" AND tbl_name="models"', self.db)
if not query.next() or query.value(0).toInt()[0] < 1:
# Create new table.
print >> sys.stderr, "Table not find, create the table"
query = QSqlQuery(
'CREATE TABLE models ('
'ID INTEGER PRIMARY KEY AUTOINCREMENT, '
'name TEXT, '
'status TEXT, '
'srclang TEXT, '
'trglang TEXT, '
'date DATE, '
'path TEXT, '
'mosesini TEXT, '
'origin TEXT, '
'originMode TEXT, '
'deleted TEXT)', self.db)
if query.next():
print >> sys.stderr, query.value(0).toString()
# TODO: shoudn't design the deletion checking like this
# Change all deleted models into not deleted in case it failed last
# time.
query = QSqlQuery(
'UPDATE models SET deleted="False" WHERE deleted="True"', self.db)
query = QSqlQuery('UPDATE models SET status="READY" WHERE status="ON"',
self.db)
super(DataModel, self).__init__(parent, self.db)
self.setTable("models")
self.select()
self.setEditStrategy(QSqlTableModel.OnFieldChange)
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 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
