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 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 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 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 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 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 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 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 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 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 set_rdpoly_mcl_links_in_db(self, rd_pol_id, mcl_ref):
"""
Update the fields of the rdpoly table with values for the given
mcl_ref from the mcl table.
:param rd_pol_id: str, rd_pol_id to update
:param mcl_ref: str, mcl_ref to supply values
"""
if config.DEBUG_MODE:
print(
"DEBUG_MODE: Updating rdpoly {} with data from mcl {}".format(
rd_pol_id, mcl_ref))
# Get update values
mcl_attrs = self.get_mcl_attrs_for_rdpoly(mcl_ref)
mcl_attrs['mcl_ref'] = mcl_ref
mcl_attrs['rd_pol_id'] = rd_pol_id
# Update database
sql = """
UPDATE rdpoly SET part_label = "{part_label}",
mcl_cref = {mcl_cref}
WHERE rd_pol_id = {rd_pol_id}
;""".format(**mcl_attrs)
if config.DEBUG_MODE:
print(sql)
query = QSqlQuery(sql, self.db)
if not query.isActive():
msg = "Failed to update rdpoly with mcl data."
msg += "\n\nSQL command:\n\n{}".format(sql)
msg += "\n\nDatabase reply:\n\n{}".format(query.lastError().text())
raise rn_except.RampRdPolyUpdateFailedPopupError(msg)
def 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 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 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 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 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 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 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 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 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 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 findEPSG(self, parameters=dict()):
'''
Finds the database EPSG
'''
self.checkAndOpenDb()
sql = self.gen.getSrid(parameters=parameters)
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(self.tr("Problem finding EPSG: ")+query.lastError().text())
srid = -1
while query.next():
srid = query.value(0)
return srid
def getAggregationAttributes(self):
'''
Gets complex link columns
'''
self.checkAndOpenDb()
columns = []
sql = self.gen.getAggregationColumn()
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(self.tr("Problem getting aggregation attributes: ")+query.lastError().text())
while query.next():
value = query.value(0)
columns.append(value)
return columns
def findEPSG(self, parameters=dict()):
'''
Finds the database EPSG
'''
self.checkAndOpenDb()
sql = self.gen.getSrid(parameters=parameters)
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem finding EPSG: ") + query.lastError().text())
srid = -1
while query.next():
srid = query.value(0)
return srid
def run_sql(sql, db):
"""
Run SQL query on the database.
:param sql: String of sql
:param db: Open QSql database
return: QSqlQuery object to extract results
"""
if config.DEBUG_MODE:
print(sql)
active_query = QSqlQuery(sql, db)
if active_query.isActive() is False:
raise StandardError('Database query problem: {}'.format(
active_query.lastError().text()))
return active_query
def cleanDb(self):
"""Drops all records"""
try:
query = QSqlQuery(self.conn)
query.prepare("DELETE FROM participants")
query.exec_()
if query.isActive() == False:
print query.lastError().text()
return query.lastError().text()
self.participantModel.select()
query.prepare("DELETE FROM teachers")
query.exec_()
if query.isActive() == False:
print query.lastError().text()
return query.lastError().text()
self.teacherModel.select()
query.prepare("DELETE FROM entries")
query.exec_()
if query.isActive() == False:
print query.lastError().text()
return query.lastError().text()
self.entryModel.select()
query.prepare("DELETE FROM selections")
query.exec_()
if query.isActive() == False:
print query.lastError().text()
return query.lastError().text()
self.pieceModel.select()
return ""
except Exception, e:
# TODO: log this instead of printing to console
print "cleanDb FAILED\n\tquery: {0}\n\terror: {1}".format(query, e)
return e
def makeValueRelationDict(self, valueDict):
"""
Gets the value relation dictionary. This is necessary for multi valued attributes.
"""
ret = dict()
codes = valueDict['FilterExpression'].replace('code in (', '').replace(
')', '').split(',')
in_clause = ','.join(map(str, codes))
keyColumn = valueDict['Key']
valueColumn = valueDict['Value']
table = valueDict['Layer'][:-17] #removing the date-time characters
uri = QgsDataSourceURI(self.currLayer.dataProvider().dataSourceUri())
if uri.host() == '':
db = QSqlDatabase('QSQLITE')
db.setDatabaseName(uri.database())
sql = 'select code, code_name from dominios_%s where code in (%s)' % (
table, in_clause)
else:
db = QSqlDatabase('QPSQL')
db.setHostName(uri.host())
db.setPort(int(uri.port()))
db.setDatabaseName(uri.database())
db.setUserName(uri.username())
db.setPassword(uri.password())
sql = 'select code, code_name from dominios.%s where code in (%s)' % (
table, in_clause)
if not db.open():
db.close()
return ret
query = QSqlQuery(sql, db)
if not query.isActive():
QMessageBox.critical(
self.iface.mainWindow(), self.tr("Error!"),
self.tr("Problem obtaining domain values: ") +
query.lastError().text())
return ret
while query.next():
code = str(query.value(0))
code_name = query.value(1)
ret[code_name] = code
db.close()
return ret
def 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 deleteTeacherFromId(self, tId):
try:
query = QSqlQuery(self.conn)
# Delete the teacher
query.prepare("DELETE FROM teachers WHERE id=:id")
query.bindValue(":id", tId)
query.exec_()
if query.isActive() == False:
print query.lastError().text()
return query.lastError().text()
self.teacherModel.select()
except Exception, e:
# TODO: log this instead of printing to console
print "deleteTeacherFromId FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e)
return e
def 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 getAggregationAttributes(self):
'''
Gets complex link columns
'''
self.checkAndOpenDb()
columns = []
sql = self.gen.getAggregationColumn()
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting aggregation attributes: ") +
query.lastError().text())
while query.next():
value = query.value(0)
columns.append(value)
return columns
def isComplexClass(self, className):
'''
Checks if a class is a complex class
className: class name to be checked
'''
self.checkAndOpenDb()
#getting all complex tables
query = QSqlQuery(self.gen.getComplexTablesFromDatabase(), self.db)
if not query.isActive():
self.db.close()
raise Exception(self.tr("Problem executing query: ")+query.lastError().text())
while query.next():
if query.value(0) == 'complexos_'+className:
return True
return False
def deleteSelectionsFromEntryId(self, entryId):
"""Deletes all selections that reference entryId"""
try:
query = QSqlQuery(self.conn)
query.prepare("DELETE FROM selections \
WHERE entry_id=:id")
query.bindValue(":id", entryId)
query.exec_()
if query.isActive() == False:
print query.lastError().text()
return query.lastError().text()
return ""
except Exception, e:
# TODO: log this instead of printing to console
print "deleteSelectionsFromEntryId FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e)
return e
def run_sql(self, query, kwargs={}):
"""
Run SQL query (defined with key 'query' in self.sql_queries) on the
database.
return: QSqlQuery object to extract results
"""
query = self.sql_queries[query]
sql = query.format(**kwargs)
if config.DEBUG_MODE:
print(sql)
active_query = QSqlQuery(sql, self.db)
if active_query.isActive() is False:
raise StandardError('Database query problem: {}'.format(
active_query.lastError().text()))
return active_query
def getQmlRecordDict(self, inputLayer):
self.checkAndOpenDb()
if isinstance(inputLayer, list):
sql = self.gen.getQmlRecords(inputLayer)
else:
sql = self.gen.getQmlRecords([inputLayer])
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(self.tr("Problem getting qmlRecordDict: ")+query.lastError().text())
qmlDict = dict()
while query.next():
if isinstance(inputLayer, list):
qmlDict[query.value(0)] = query.value(1)
else:
return query.value(1)
return qmlDict
def getTablesFromDatabase(self):
'''
Gets all tables from database
'''
self.checkAndOpenDb()
ret = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(self.tr("Problem getting tables from database: ")+query.lastError().text())
while query.next():
#table name
ret.append(query.value(0))
return ret
def obtainLinkColumn(self, complexClass, aggregatedClass):
'''
Obtains the link column between complex and aggregated class
complexClass: complex class name
aggregatedClass: aggregated class name
'''
self.checkAndOpenDb()
#query to obtain the link column between the complex and the feature layer
sql = self.gen.getLinkColumn(complexClass.replace('complexos_', ''), aggregatedClass)
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(self.tr("Problem obtaining link column: ")+query.lastError().text())
column_name = ""
while query.next():
column_name = query.value(0)
return column_name
def getGeomTypeDict(self, loadCentroids=False):
self.checkAndOpenDb()
sql = self.gen.getGeomByPrimitive()
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting geom types from db: ") +
query.lastError().text())
geomDict = dict()
while query.next():
type = query.value(0)
tableName = query.value(1)
layerName = '_'.join(tableName.split('_')[1::])
if type not in geomDict.keys():
geomDict[type] = []
if layerName not in geomDict[type]:
geomDict[type].append(layerName)
return geomDict
def getGeomColumnDict(self):
'''
Dict in the form 'geomName':[-list of table names-]
'''
self.checkAndOpenDb()
sql = self.gen.getGeomColumnDict()
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(self.tr("Problem getting geom column dict: ")+query.lastError().text())
geomDict = dict()
while query.next():
geomColumn = query.value(0)
tableName = query.value(1)
lyrName = '_'.join(tableName.split('_')[1::])
if geomColumn not in geomDict.keys():
geomDict[geomColumn] = []
geomDict[geomColumn].append(lyrName)
return geomDict
def getQmlRecordDict(self, inputLayer):
self.checkAndOpenDb()
if isinstance(inputLayer, list):
sql = self.gen.getQmlRecords(inputLayer)
else:
sql = self.gen.getQmlRecords([inputLayer])
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting qmlRecordDict: ") +
query.lastError().text())
qmlDict = dict()
while query.next():
if isinstance(inputLayer, list):
qmlDict[query.value(0)] = query.value(1)
else:
return query.value(1)
return qmlDict
def listComplexClassesFromDatabase(self):
'''
Gets a list with complex classes from database
'''
self.checkAndOpenDb()
classList = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(self.tr("Problem listing complex classes: ")+query.lastError().text())
while query.next():
tableName = str(query.value(0))
layerName = tableName
tableSchema = layerName.split('_')[0]
if tableSchema == 'complexos':
classList.append(layerName)
return classList
def isComplexClass(self, className):
'''
Checks if a class is a complex class
className: class name to be checked
'''
self.checkAndOpenDb()
#getting all complex tables
query = QSqlQuery(self.gen.getComplexTablesFromDatabase(), self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem executing query: ") +
query.lastError().text())
while query.next():
if query.value(0) == 'complexos_' + className:
return True
return False
def listGeomClassesFromDatabase(self, primitiveFilter = []):
'''
Gets a list with geometry classes from database
'''
self.checkAndOpenDb()
classList = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(self.tr("Problem listing geom classes: ")+query.lastError().text())
while query.next():
tableName = str(query.value(0))
layerName = tableName
if tableName.split("_")[-1] == "p" or tableName.split("_")[-1] == "l" \
or tableName.split("_")[-1] == "a":
classList.append(layerName)
return classList
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 getTablesFromDatabase(self):
'''
Gets all tables from database
'''
self.checkAndOpenDb()
ret = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem getting tables from database: ") +
query.lastError().text())
while query.next():
#table name
ret.append(query.value(0))
return ret
def listComplexClassesFromDatabase(self):
'''
Gets a list with complex classes from database
'''
self.checkAndOpenDb()
classList = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem listing complex classes: ") +
query.lastError().text())
while query.next():
tableName = str(query.value(0))
layerName = tableName
tableSchema = layerName.split('_')[0]
if tableSchema == 'complexos':
classList.append(layerName)
return classList
def listGeomClassesFromDatabase(self, primitiveFilter=[]):
'''
Gets a list with geometry classes from database
'''
self.checkAndOpenDb()
classList = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem listing geom classes: ") +
query.lastError().text())
while query.next():
tableName = str(query.value(0))
layerName = tableName
if tableName.split("_")[-1] == "p" or tableName.split("_")[-1] == "l" \
or tableName.split("_")[-1] == "a":
classList.append(layerName)
return classList
def getGeomColumnDict(self):
'''
Dict in the form 'geomName':[-list of table names-]
'''
self.checkAndOpenDb()
sql = self.gen.getGeomColumnDict()
query = QSqlQuery(sql, self.db)
if not query.isActive():
raise Exception(
self.tr("Problem getting geom column dict: ") +
query.lastError().text())
geomDict = dict()
while query.next():
geomColumn = query.value(0)
tableName = query.value(1)
lyrName = '_'.join(tableName.split('_')[1::])
if geomColumn not in geomDict.keys():
geomDict[geomColumn] = []
geomDict[geomColumn].append(lyrName)
return geomDict
def obtainLinkColumn(self, complexClass, aggregatedClass):
'''
Obtains the link column between complex and aggregated class
complexClass: complex class name
aggregatedClass: aggregated class name
'''
self.checkAndOpenDb()
#query to obtain the link column between the complex and the feature layer
sql = self.gen.getLinkColumn(complexClass.replace('complexos_', ''),
aggregatedClass)
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem obtaining link column: ") +
query.lastError().text())
column_name = ""
while query.next():
column_name = query.value(0)
return column_name
def get_mcl_cref(self, rd_pol_id):
"""
Get the MCL ref attached to given polygon
:param rd_pol_id:
:return: str, mcl_cref
"""
sql = """
SELECT mcl_cref FROM rdpoly
WHERE rd_pol_id = '{}'
;""".format(rd_pol_id)
query = QSqlQuery(sql, self.db)
if not query.isActive():
msg = "Invalid rd_pol_id:"
msg += "\n\nSQL command:\n\n{}".format(sql)
msg += "\n\nDatabase reply:\n\n{}".format(query.lastError().text())
raise rn_except.RampRdPolyUpdateFailedPopupError(msg)
query.first()
mcl_ref = str(query.record().value('mcl_cref'))
return mcl_ref
def get_mcl_attrs_for_rdpoly(self, mcl_ref):
"""
Get values from database and prepare attributes to insert into rdpoly
table.
:param mcl_ref: str, mcl_ref
:return: dict, mcl_attributes
"""
sql = """
SELECT lor_ref_1 || "/" || lor_ref_2 AS part_label
FROM mcl WHERE mcl_ref={};""".format(mcl_ref)
query = QSqlQuery(sql, self.db)
if not query.isActive():
msg = "Failed to get MCL attributes."
msg += "\n\nSQL command:\n\n{}".format(sql)
msg += "\n\nDatabase reply:\n\n{}".format(query.lastError().text())
raise rn_except.RampRdPolyUpdateFailedPopupError(msg)
query.first()
part_label = query.record().value("part_label")
mcl_attrs = {'mcl_cref': mcl_ref, 'part_label': part_label}
return mcl_attrs
def clear_rdpoly_mcl_fields(self, rd_pol_id):
"""
Clear values in rdpoly that were derived from linked MCL. Used when
MCL is unlinked.
:param rd_pol_id: str, rd_pol_id
"""
sql = """
UPDATE rdpoly SET
element = NULL, hierarchy = NULL,
ref_1 = NULL, ref_2 = NULL, ref_3 = NULL,
desc_1 = NULL, desc_2 = NULL, desc_3 = NULL,
part_label = NULL, label = NULL, label1 = NULL,
feature_length = NULL, r_usrn = NULL, mcl_cref = NULL
WHERE rd_pol_id = {}
;""".format(rd_pol_id)
if config.DEBUG_MODE:
print(sql)
query = QSqlQuery(sql, self.db)
if not query.isActive():
msg = "Problem updating rdpoly with mcl data."
msg += "\n\nSQL command:\n\n{}".format(sql)
msg += "\n\nDatabase reply:\n\n{}".format(query.lastError().text())
raise rn_except.RampRdPolyUpdateFailedPopupError(msg)
def 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 validateWithOutputDatabaseSchema(self, outputAbstractDb):
'''
Validates the conversion with the output database.
It generates a dictionary (invalidated) that stores conversion problems
'''
self.checkAndOpenDb()
invalidated = self.buildInvalidatedDict()
inputdbStructure = self.getStructureDict()
outputdbStructure = outputAbstractDb.getStructureDict()
domainDict = outputAbstractDb.getDomainDict()
classes = self.listClassesWithElementsFromDatabase()
notNullDict = outputAbstractDb.getNotNullDict()
for inputClass in classes.keys():
outputClass = self.translateAbstractDbLayerNameToOutputFormat(
inputClass, outputAbstractDb)
(schema, className) = self.getTableSchema(inputClass)
if outputClass in outputdbStructure.keys():
outputAttrList = self.reorderTupleList(
outputdbStructure[outputClass].keys())
inputAttrList = self.reorderTupleList(
inputdbStructure[inputClass].keys())
sql = self.gen.getFeaturesWithSQL(inputClass, inputAttrList)
query = QSqlQuery(sql, self.db)
if not query.isActive():
self.db.close()
raise Exception(
self.tr("Problem executing query: ") +
query.lastError().text())
while query.next():
id = query.value(0)
#detects null lines
for i in range(len(inputAttrList)):
nullLine = True
value = query.value(i)
if value <> None:
nullLine = False
break
if nullLine:
if cl not in invalidated['nullLine'].keys():
invalidated['nullLine'][inputClass] = 0
invalidated['nullLine'][inputClass] += 1
#validates pks
if id == None and (not nullLine):
if cl not in invalidated['nullPk'].keys():
invalidated['nullPk'][inputClass] = 0
invalidated['nullPk'][inputClass] += 1
for i in range(len(inputAttrList)):
value = query.value(i)
#validates domain
if outputClass in domainDict.keys():
if inputAttrList[i] in domainDict[
outputClass].keys():
if value not in domainDict[outputClass][
inputAttrList[i]] and (not nullLine):
invalidated = self.utils.buildNestedDict(
invalidated, [
'notInDomain', inputClass, id,
inputAttrList[i]
], value)
#validates not nulls
if outputClass in notNullDict.keys():
if outputClass in domainDict.keys():
if inputAttrList[i] in notNullDict[
outputClass] and inputAttrList[
i] not in domainDict[
outputClass].keys():
if (value == None) and (not nullLine) and (
inputAttrList[i] not in
domainDict[outputClass].keys()):
invalidated = self.utils.buildNestedDict(
invalidated, [
'nullAttribute', inputClass,
id, inputAttrList[i]
], value)
else:
if inputAttrList[i] in notNullDict[
outputClass]:
try:
if value.isNull():
invalidated = self.utils.buildNestedDict(
invalidated, [
'nullAttribute',
inputClass, id,
inputAttrList[i]
], value)
except:
if (value == None) and (
not nullLine
) and (inputAttrList[i] not in
domainDict[outputClass].keys()):
invalidated = self.utils.buildNestedDict(
invalidated, [
'nullAttribute',
inputClass, id,
inputAttrList[i]
], value)
if outputClass in domainDict.keys():
if (inputAttrList[i] not in [
'geom', 'GEOMETRY', 'geometry', 'id',
'OGC_FID'
] and schema <> 'complexos') or (
schema == 'complexos'
and inputAttrList[i] <> 'id'):
if inputAttrList[i] not in outputdbStructure[
outputClass].keys():
invalidated = self.utils.buildNestedDict(
invalidated, [
'attributeNotFoundInOutput',
inputClass
], [inputAttrList[i]])
#validates fk field
if 'id_' == inputAttrList[0:3]:
if not self.validateUUID(value):
if inputAttrList[i] not in outputdbStructure[
outputClass].keys():
invalidated = self.utils.buildNestedDict(
invalidated,
['nullComplexFk', inputClass],
[inputAttrList[i]])
else:
invalidated['classNotFoundInOutput'].append(inputAttrList)
return invalidated
def export_esu_line(self):
"""
Export ESUs
:return:
"""
canvas = self.iface.mapCanvas()
clayer = canvas.currentLayer()
# will return 0 if none selected
count = clayer.selectedFeatureCount()
feature_count = clayer.featureCount()
# Get list of selected features
selected_esu_ids = list()
if count > 0:
selectedfeats = clayer.selectedFeatures()
for feat in selectedfeats:
selected_esu_ids.append(int(feat.attribute('esu_id')))
feature_count = clayer.selectedFeatureCount()
self.warn_about_selected_features(feature_count)
# Prepare sql query
if self.unassigned:
nsgexportsql = self.sql_queries['export_all']
else:
nsgexportsql = self.sql_queries['export_assigned_only']
# SQL to filter out selected records
if count > 0:
nsgexportsql += " WHERE esu.esu_id IN ({})".format(', '.join(
map(str, selected_esu_ids)))
# Setup database temporary tables
for table in ['qryType12', 'qryType3', 'qryType4']:
# Drop tables if left behind from last export
args = {'table': table}
query = self.run_sql('drop_table', args)
query = self.run_sql('create_qryType12')
query = self.run_sql('create_qryType3')
query = self.run_sql('create_qryType4')
# Run the main query
if config.DEBUG_MODE:
print(nsgexportsql)
query = QSqlQuery(self.db)
query.setForwardOnly(True)
query.exec_(nsgexportsql)
if query.isActive() is False:
raise StandardError('Database query problem: {}'.format(
query.lastError().text()))
# create layer
vlayer = QgsVectorLayer("multilinestring?crs=EPSG:27700", "temp",
"memory")
vlayer.setCrs(
QgsCoordinateReferenceSystem(
27700, QgsCoordinateReferenceSystem.EpsgCrsId))
provider = vlayer.dataProvider()
# add fields
self.fields = [
QgsField("esu_id", QVariant.String),
QgsField("USRN", QVariant.LongLong),
QgsField("Rec_type", QVariant.Int),
QgsField("DescTxt", QVariant.String),
QgsField("Locality", QVariant.String),
QgsField("Town", QVariant.String),
QgsField("Entry_date", QVariant.Date),
QgsField("Type_3_USRN", QVariant.LongLong),
QgsField("Type_3_Desc", QVariant.String),
QgsField("Type_4_USRN", QVariant.LongLong),
QgsField("Type_4_Desc", QVariant.String)
]
provider.addAttributes(self.fields)
vlayer.updateFields()
# Exit if output file path is invalid
if len(str(self.export_path)) < 0:
return False
if self.check_if_export_file_in_use():
return False
# Run through SQL results creating features from rows
self.progresswin.show()
i = 0
while query.next():
if self.progresswin.wasCanceled():
self.kill_export()
break
record = query.record()
new_feature = self.create_feature_from_record(record)
provider.addFeatures([new_feature])
# Update progress bar
i += 1
diff = feature_count + (
i - feature_count) if i > feature_count else feature_count
percent_complete = (i / float(diff)) * 100
self.progresswin.setValue(percent_complete)
if self.killed:
# Show message and exit if killed
export_error_msg_box = QMessageBox(
QMessageBox.Warning, " ",
"An error occurred while exporting shapefile", QMessageBox.Ok,
None)
export_error_msg_box.setWindowFlags(Qt.CustomizeWindowHint
| Qt.WindowTitleHint)
export_error_msg_box.exec_()
return False
vlayer.updateExtents()
result = QgsVectorFileWriter.writeAsVectorFormat(
vlayer, self.export_path, "utf-8", None, "ESRI Shapefile")
# checks for completed export
if result == 0:
self.progresswin.close()
if config.DEBUG_MODE:
print('DEBUG_MODE: {} features exported'.format(
vlayer.featureCount()))
return True
