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 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 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 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 get_last_lsg_date(self):
"""
Finds the most recent record insert/update date from either ESU's or street records.
:return: date formatted YYYYMMDD
"""
last_date = 0
last_street = "Select max(Update_Date) as LastChange FROM tblStreet"
query = QSqlQuery(self.db)
query.exec_(last_street)
query.seek(0)
rec = query.record()
last_street_date = query.value(rec.indexOf('LastChange'))
last_esu = "SELECT Max([closure_date]) AS LastClose, Max([entry_date]) AS lastEntry FROM tblESU"
query_esu = QSqlQuery(self.db)
query_esu.exec_(last_esu)
query_esu.seek(0)
rec_esu = query_esu.record()
last_esu_closure = query_esu.value(rec_esu.indexOf('LastClose'))
last_esu_entry = query_esu.value(rec_esu.indexOf('lastEntry'))
if last_street_date > last_date:
last_date = last_street_date
if last_esu_closure > last_date:
last_date = last_esu_closure
if last_esu_entry > last_date:
last_date = last_esu_entry
return last_date
def 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 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 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 __init__( self, parentId, parent = None ):
super( AccountsModel, self ).__init__( parent )
query = """
SELECT
cc.codigo,
cc.descripcion,
cc.esdebe,
COUNT(ch.idcuenta) nhijos,
SUM(IFNULL(monto,0)) monto,
cc.padre,
cc.idcuenta
FROM cuentascontables cc
LEFT JOIN cuentascontables ch ON cc.idcuenta = ch.padre
LEFT JOIN cuentasxdocumento cxd ON cc.idcuenta = cxd.idcuenta
WHERE cc.padre = %d
GROUP BY cc.idcuenta
""" % parentId
query = QSqlQuery( query )
query.exec_()
query.first()
self.rootItem = Account(
QModelIndex(),
parentId,
query.value( CODIGO ).toString(),
query.value( DESCRIPCION ).toString(),
Decimal( query.value( MONTO ).toString() ),
query.value( ESDEBE ).toInt()[0],
query.value( HIJOS ).toInt()[0] )
def 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 lsg_last_date(self):
"""
get the last update date from db to show in LSG label
:return: string
"""
qry_lsg = QSqlQuery(self.get_last_street, self.db) # THIS
while qry_lsg.next():
try:
date_lsg_obj = datetime.strptime(str(qry_lsg.value(0)),
"%Y%m%d")
except ValueError:
date_lsg_obj = '000000'
qry_esu = QSqlQuery(self.get_last_esu, self.db)
while qry_esu.next():
try:
date_esu_closure_obj = datetime.strptime(
str(qry_esu.value(0)), "%Y%m%d")
except ValueError:
date_esu_closure_obj = '000000'
try:
date_esu_entry_obj = datetime.strptime(str(qry_esu.value(1)),
"%Y%m%d")
except ValueError:
date_esu_entry_obj = '00000'
last_lsg_change = max(date_lsg_obj, date_esu_closure_obj,
date_esu_entry_obj).date()
date_clean = str(last_lsg_change.strftime("%d/%m/%Y"))
return date_clean
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 results_to_csv_row(self, vals, sql, mode):
query = QSqlQuery(self.db)
query.exec_(sql)
rec = query.record()
if mode == "streets":
avals = [
rec.indexOf(vals[0]),
rec.indexOf(vals[1]),
rec.indexOf(vals[2]),
rec.indexOf(vals[3]),
rec.indexOf(vals[4]),
rec.indexOf(vals[5]),
rec.indexOf(vals[6]),
rec.indexOf(vals[7]),
rec.indexOf(vals[8]),
rec.indexOf(vals[9]),
rec.indexOf(vals[10]),
rec.indexOf(vals[11])
]
while query.next():
line = [
query.value(avals[0]),
query.value(avals[1]),
query.value(avals[2]),
query.value(avals[3]),
query.value(avals[4]),
query.value(avals[5]),
self.format_dates(str(query.value(avals[6]))),
self.format_dates(str(query.value(avals[7]))),
self.format_dates(str(query.value(avals[8]))),
self.format_dates(str(query.value(avals[9]))),
query.value(avals[10]),
query.value(avals[11])
]
self.csv.writerow(line)
else:
avals = [
rec.indexOf(vals[0]),
rec.indexOf(vals[1]),
rec.indexOf(vals[2]),
rec.indexOf(vals[3]),
rec.indexOf(vals[4]),
rec.indexOf(vals[5]),
rec.indexOf(vals[6]),
rec.indexOf(vals[7])
]
while query.next():
line = [
query.value(avals[0]),
query.value(avals[1]),
query.value(avals[2]),
query.value(avals[3]),
query.value(avals[4]),
query.value(avals[5]),
query.value(avals[6]),
query.value(avals[7])
]
self.csv.writerow(line)
def 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 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 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 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 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 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 exportNotice(self):
query = QSqlQuery(self.db)
query.exec_("select stusn, stuname, classname, rightquestions, wrongquestions from student" )
lstInfo = [["学号","姓名", "班级", "回答正确次数", "回答错误次数"]]
while(query.next()):
lstInfo.append([query.value(0),query.value(1),query.value(2),query.value(3),query.value(4)])
from xlwt import Workbook,easyxf
book = Workbook(encoding='ascii')
# 'pattern: pattern solid, fore_colour white;'
style = easyxf(
'font: height 280, name 黑体;'
'align: vertical center, horizontal center;'
)
style2 = easyxf('font: height 260, name 仿宋_GB2312, bold True; align: vertical center, horizontal left;')
style3 = easyxf('font: height 260, name 仿宋_GB2312, bold True; align: vertical center, horizontal left, wrap True;')
sheet1 = book.add_sheet('学生提问情况汇总',cell_overwrite_ok=True)
# sheet1.write(0,7,flagtxt, easyxf('font: height 200, name 黑体;align: vertical center, horizontal right;'))
sheet1.write_merge(0,0,0,4, '学生提问情况汇总表',style)
sheet1.row(0).height_mismatch = 1
sheet1.row(0).height = 5*256
sheet1.col(0).width = 10*256
sheet1.col(1).width = 25*256
sheet1.col(2).width = 25*256
sheet1.col(3).width = 20*256
sheet1.col(4).width = 20*256
tmprows = 1
for item in lstInfo:
stusn = item[0]
stuname = item[1]
classname = item[2]
rightquestions = item[3]
wrongquestions = item[4]
sheet1.write(tmprows,0,stusn, style2)
sheet1.write(tmprows,1,stuname, style2)
sheet1.write(tmprows,2,classname, style2)
sheet1.write(tmprows,3,rightquestions, style2)
sheet1.write(tmprows,4,wrongquestions, style2)
tmprows += 1
# print(tmprows)
sheet1.header_str = "".encode()
sheet1.footer_str = "".encode()
# book.save('d:/simple.xls')
# print(QDir.home().dirName() , QDir.homePath ())
filename = QDir.homePath () + "\学生提问情况汇总表.xls"
try:
book.save(filename)
except Exception as e:
QMessageBox.warning(self, "写入错误", "错误号："+str(e.errno)+"\n错误描述："+e.strerror+"\n请关闭已经打开的%s文档!" % filename)
QMessageBox.about (self, "导出成功", "请查看文档：%s" % filename)
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 load(self, error=True):
s = QSettings("norBIT", "norGIS-ALKIS-Erweiterung")
s.setValue("service", self.leSERVICE.text())
s.setValue("host", self.leHOST.text())
s.setValue("port", self.lePORT.text())
s.setValue("dbname", self.leDBNAME.text())
s.setValue("uid", self.leUID.text())
s.setValue("pwd", self.lePWD.text())
modelle = s.value("modellarten", ['DLKM', 'DKKM1000'])
(db, conninfo) = self.plugin.opendb()
if db:
qry = QSqlQuery(db)
if qry.exec_("""
SELECT modell,count(*)
FROM (
SELECT unnest(modell) AS modell FROM po_points UNION ALL
SELECT unnest(modell) AS modell FROM po_lines UNION ALL
SELECT unnest(modell) AS modell FROM po_polygons UNION ALL
SELECT unnest(modell) AS modell from po_lines UNION ALL
SELECT unnest(modell) AS modell from po_labels
) AS foo
GROUP BY modell
ORDER BY count(*) DESC
"""):
self.twModellarten.clearContents()
res = {}
while qry.next():
res[qry.value(0)] = qry.value(1)
self.twModellarten.setRowCount(len(res))
i = 0
for k, n in sorted(res.iteritems(),
key=operator.itemgetter(1),
reverse=True):
item = QTableWidgetItem(k)
item.setCheckState(Qt.Checked if (
item.text() in modelle) else Qt.Unchecked)
self.twModellarten.setItem(i, 0, item)
item = QTableWidgetItem(str(n))
self.twModellarten.setItem(i, 1, item)
i += 1
self.twModellarten.resizeColumnsToContents()
elif error:
modelle = []
self.twModellarten.clearContents()
self.twModellarten.setDisabled(True)
else:
modelle = []
elif error:
QMessageBox.critical(None, "ALKIS",
u"Datenbankverbindung schlug fehl.")
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 makeValueRelationDict(self, table, codes):
ret = dict()
in_clause = '(%s)' % ",".join(map(str, codes))
query = QSqlQuery('select code, code_name from dominios.%s where code in %s' % (table, in_clause), self.db)
while query.next():
code = str(query.value(0))
code_name = query.value(1)
ret[code_name] = code
return ret
def getRecord(self, id_):
query = QSqlQuery(self.handle)
query.exec_(self.fetch_query % id_)
result = None
while query.next():
# 22: number of total fields, skip first id field
result = [query.value(i).toString() for i in range(1,22)]
result[12] = query.value(13).toByteArray()
return result
def on_pbSearchStr_clicked(self):
qry = QSqlQuery(self.db)
self.cbxStrassen.clear()
if qry.exec_( u"SELECT k.schluesselgesamt, k.bezeichnung || coalesce(', ' || g.bezeichnung,'') FROM ax_lagebezeichnungkatalogeintrag k LEFT OUTER JOIN ax_gemeinde g ON k.land=g.land AND k.regierungsbezirk=g.regierungsbezirk AND k.kreis=g.kreis AND k.gemeinde::int=g.gemeinde::int AND g.endet IS NULL WHERE k.bezeichnung LIKE {0} AND k.endet IS NULL ORDER BY k.bezeichnung || coalesce(', ' || g.bezeichnung,'')".format( quote(self.leSuchbegriff.text()+'%') ) ):
while qry.next():
self.cbxStrassen.addItem( qry.value(1), qry.value(0) )
self.lblStrassen.setVisible( self.cbxStrassen.count() > 0 )
self.cbxStrassen.setVisible( self.cbxStrassen.count() > 0 )
self.on_cbxStrassen_currentIndexChanged(0)
def get_all_info(self):
info = {}
query = QSqlQuery(self.db)
query.setForwardOnly(True)
query.prepare('SELECT image_date, copyright_info FROM copyright;')
if not query.exec_():
self.error.emit('Error getting all copyright info', query.lastError().text())
return info
while query.next():
image_date = str(query.value(0).toString())
copyright_info = unicode(query.value(1).toString())
info[image_date] = copyright_info
return info
def category_validation_sql(self):
"""
Populate a dict with validation rules for different record categories
:return: dict of cateogry rules
"""
sql = "SELECT reinstatement_code, category FROM tlkpREINS_CAT;"
rules = {}
query = QSqlQuery(sql, self.db)
while query.next():
code = query.value(0)
category = query.value(1)
rules[code] = category
return rules
def makeValueRelationDict(self, table, codes):
'''
Makes the value relation dictionary (multi valued attributes)
'''
self.checkAndOpenDb()
ret = dict()
in_clause = '(%s)' % ",".join(map(str, codes))
sql = self.gen.makeRelationDict(table, in_clause)
query = QSqlQuery(sql, self.db)
while query.next():
code = str(query.value(0))
code_name = query.value(1)
ret[code_name] = code
return ret
def makeValueRelationDict(self, table, codes):
'''
Makes the value relation dictionary (multi valued attributes)
'''
self.checkAndOpenDb()
ret = dict()
in_clause = '(%s)' % ",".join(map(str, codes))
sql = self.gen.makeRelationDict(table, in_clause)
query = QSqlQuery(sql, self.db)
while query.next():
code = str(query.value(0))
code_name = query.value(1)
ret[code_name] = code
return ret
def validation_sql(self):
"""
Builds a dict of the validation rules and what the date label should be, depending on the designation.
:return: dict [designation code] = (mandatory field, date field name)
"""
sql = "SELECT designation_code, date_text, mandatory_fields FROM tlkpSPEC_DES;"
rules = {}
query = QSqlQuery(sql, self.db)
while query.next():
code = int(query.value(0))
date_text = query.value(1)
mand_fields = query.value(2)
rules[code] = (mand_fields, date_text)
return rules
def makeValueRelationDict(self, table, codes):
ret = dict()
in_clause = '(%s)' % ",".join(map(str, codes))
query = QSqlQuery(
'select code, code_name from dominios.%s where code in %s' %
(table, in_clause), self.db)
while query.next():
code = str(query.value(0))
code_name = query.value(1)
ret[code_name] = code
return ret
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 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 getDefaults(self):
query = QSqlQuery("SELECT control, value FROM DefaultValues")
query.exec_()
defaults = {}
while query.next():
try:
name = query.value(0).toString()
control = self.getControl(name)
except ControlNotFound:
continue
value = query.value(1)
defaults[control] = value
return defaults
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 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 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 load(self, error=True):
s = QSettings( "norBIT", "norGIS-ALKIS-Erweiterung" )
s.setValue( "service", self.leSERVICE.text() )
s.setValue( "host", self.leHOST.text() )
s.setValue( "port", self.lePORT.text() )
s.setValue( "dbname", self.leDBNAME.text() )
s.setValue( "uid", self.leUID.text() )
s.setValue( "pwd", self.lePWD.text() )
modelle = s.value( "modellarten", ['DLKM','DKKM1000'] )
(db,conninfo) = self.plugin.opendb()
if db:
qry = QSqlQuery(db)
if qry.exec_( """
SELECT modell,count(*)
FROM (
SELECT unnest(modell) AS modell FROM po_points UNION ALL
SELECT unnest(modell) AS modell FROM po_lines UNION ALL
SELECT unnest(modell) AS modell FROM po_polygons UNION ALL
SELECT unnest(modell) AS modell from po_lines UNION ALL
SELECT unnest(modell) AS modell from po_labels
) AS foo
GROUP BY modell
ORDER BY count(*) DESC
""" ):
self.twModellarten.clearContents()
res = {}
while qry.next():
res[ qry.value(0) ] = qry.value(1)
self.twModellarten.setRowCount( len(res) )
i = 0
for k,n in sorted(res.iteritems(), key=operator.itemgetter(1), reverse=True):
item = QTableWidgetItem( k )
item.setCheckState( Qt.Checked if (item.text() in modelle) else Qt.Unchecked )
self.twModellarten.setItem( i, 0, item )
item = QTableWidgetItem( str(n) )
self.twModellarten.setItem( i, 1, item )
i += 1
self.twModellarten.resizeColumnsToContents()
elif error:
modelle = []
self.twModellarten.clearContents()
self.twModellarten.setDisabled( True )
else:
modelle = []
elif error:
QMessageBox.critical( None, "ALKIS", u"Datenbankverbindung schlug fehl." )
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 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 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 commit_esu_link(self):
"""
Updates existing esu links on edit and deal with adding/remove links via editing
"""
usrn = str(self.new_usrn_no)
if self.edit_esu:
# get new set of esu links
esus = self.edit_esu.get_final_selection()
final = esus[0]
else:
# No esu edits made so query for existing esu links
final = self.esu_version.query_esu(usrn)
date = str(datetime.datetime.now().strftime("%Y%m%d"))
try:
for esu in final:
query_str = "SELECT version_no FROM tblESU WHERE esu_id = %s AND currency_flag = 0;" % esu
query = QSqlQuery(query_str, self.db)
seek = query.seek(0)
if seek:
esu_ver = query.value(0)
else:
esu_ver = str(1)
# Create new links
insert_sql = "INSERT INTO lnkESU_STREET (esu_id, usrn, esu_version_no, usrn_version_no, currency_flag," \
" entry_date, update_date) VALUES (%s, %s, %s, 1, 0, %s, %s)" \
% (esu, usrn, esu_ver, date, date)
new_lnk_query = QSqlQuery(insert_sql, self.db)
except TypeError:
# No esu's attached to record
pass
def SitesHaveFindSpots(db, siteNumbers):
sites = u", ".join(u"'{0}'".format(siteNumber) for siteNumber in siteNumbers)
query = QSqlQuery(db)
query.prepare(u"SELECT COUNT(*) FROM fundstelle WHERE fundortnummer IN ({0})".format(sites))
query.exec_()
query.first()
return query.value(0)
def __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 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 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 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 listGeomClassesFromDatabase(self):
try:
self.checkAndOpenDb()
except:
return []
classList = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
while query.next():
tableSchema = query.value(0)
tableName = query.value(1)
layerName = tableSchema+'.'+tableName
if tableName.split("_")[-1] == "p" or tableName.split("_")[-1] == "l" \
or tableName.split("_")[-1] == "a":
classList.append(layerName)
return classList
def listComplexClassesFromDatabase(self):
try:
self.checkAndOpenDb()
except:
return []
classList = []
sql = self.gen.getTablesFromDatabase()
query = QSqlQuery(sql, self.db)
while query.next():
tableSchema = query.value(0)
tableName = query.value(1)
layerName = tableSchema+'.'+tableName
if tableSchema == 'complexos':
classList.append(layerName)
classList.sort()
return classList
def data( self ):
data = {}
fila = self.tblCuenta.selectionModel().currentIndex().row()
fecha = self.dtPicker.date()
data['banco'] = self.filtermodel.index( fila, 0 ).data().toString()
data['id_cuenta_contable'] = self.filtermodel.index( fila, 4 ).data().toInt()[0]
data['codigo_cuenta_contable'] = self.filtermodel.index( fila, 3 ).data().toString()
data['cuenta_bancaria'] = self.filtermodel.index( fila, 5 ).data().toString()
data['fecha'] = QDate( fecha.year(), fecha.month(), fecha.daysInMonth() )
data['moneda'] = self.filtermodel.index( fila, 2 ).data().toString()
if not QSqlDatabase.database().isOpen() and not QSqlDatabase.open():
raise Exception( QSqlDatabase.lastError() )
query = QSqlQuery()
if not query.exec_( "CALL spSaldoCuenta( %d, %s )" % (
data['id_cuenta_contable'],
QDate( data['fecha'].year(), data['fecha'].month(), data['fecha'].daysInMonth() ).toString( "yyyyMMdd" )
)
):
raise Exception( query.lastError().text() )
query.first()
data['saldo_inicial_libro'] = Decimal( query.value( 0 ).toString() )
return data
def 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 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 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())
