def recuperar_datos(self):
query = QSqlQuery()
sql = "select cliente_id,fecha,hora,notas from ventas where venta_id=%d" % self.id
if not query.exec_(sql):
print "Error al obtener venta."
return
g = lambda x:query.value(x).toString()
query.next()
self.cliente_id = int(g(0))
self.fecha = g(1)
self.hora = g(2)
self.notas = g(3)
self.productos = {}
sql = """ select codigo,cantidad,descuento from detalle_ventas where venta_id=%d""" % self.id
query.exec_(sql)
for i in range(query.size()):
query.next()
asd = str(g(0))
self.productos[asd] = producto(g(0))
self.productos[asd].cantidad = int(g(1))
self.productos[asd].descuento = int(g(2))
self.productos[asd].actualizar_subtotal()
self.actualizar_total()
def execSql( self, strQuery ):
strQuery = QString(strQuery)
query = QSqlQuery( self.sqlDatabase() )
if query.exec_( strQuery ) != True:
print "Failed execute ", strQuery.toLatin1().data(), ' error:' , query.lastError().databaseText().toLatin1().data()
return query
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 init( self ):
try:
if not self.database.isOpen():
if not self.database.open():
raise UserWarning( u"No se pudo abrir la conexión "\
+ "con la base de datos" )
query = QSqlQuery( """
SELECT * FROM cuentascontables c
jOIN cuentasxdocumento cd ON c.idcuenta = cd.idcuenta
;
""" )
if not query.exec_():
raise UserWarning( "No se pudo consultar el catalogo de cuentas" )
self.inicial = query.size()==0
except UserWarning as inst:
logging.error( unicode( inst ) )
self.movimientosDisponibles(False)
finally:
if self.database.isOpen():
self.database.close()
self.btnMovements.setText("Balance \n Inicial" if self.inicial else "Ajustes Contables" )
self.status = True
def __init__( self, parentId, parent = None ):
super( AccountsModel, self ).__init__( parent )
query = """
SELECT
cc.codigo,
cc.descripcion,
cc.esdebe,
COUNT(ch.idcuenta) nhijos,
SUM(IFNULL(monto,0)) monto,
cc.padre,
cc.idcuenta
FROM cuentascontables cc
LEFT JOIN cuentascontables ch ON cc.idcuenta = ch.padre
LEFT JOIN cuentasxdocumento cxd ON cc.idcuenta = cxd.idcuenta
WHERE cc.padre = %d
GROUP BY cc.idcuenta
""" % parentId
query = QSqlQuery( query )
query.exec_()
query.first()
self.rootItem = Account(
QModelIndex(),
parentId,
query.value( CODIGO ).toString(),
query.value( DESCRIPCION ).toString(),
Decimal( query.value( MONTO ).toString() ),
query.value( ESDEBE ).toInt()[0],
query.value( HIJOS ).toInt()[0] )
def findEPSG(self):
sql = self.gen.getSrid()
query = QSqlQuery(sql, self.db)
srids = []
while query.next():
srids.append(query.value(0))
return srids[0]
def isComplexClass(self, className):
#getting all complex tables
query = QSqlQuery(self.gen.getComplexTablesFromDatabase(), self.db)
while query.next():
if query.value(0) == className:
return True
return False
def accept(self):
rows = self.sModel.rowCount()
cols = self.sModel.columnCount()
model = self.sModel
mlqry = dict()
qry = None
for r in range(rows):
record = model.record(r)
for c in range(1, cols):
if record.value(c).toString():
if not qry:
qry = "("
elif c > 1 and qry :
qry = "%s AND" % qry
qry = "%s %s %s" % (qry, record.fieldName(c),
record.value(c).toString())
if qry :
mlqry[r] = "%s )" % qry
qry = ""
for q in mlqry.keys():
if not qry:
qry = mlqry[q]
continue
qry = "%s OR %s" % (qry, mlqry[q])
self.resultFilter = qry
query = QSqlQuery()
query.exec_("DROP TABLE filtertable")
if self.dbi:
del self.dbi
QDialog.accept(self)
def creaStrutturaDB(self):
query = QSqlQuery()
if not ("magamaster" in self.db.tables()):
if not query.exec_("""CREATE TABLE magamaster (
id INTEGER PRIMARY KEY AUTOINCREMENT
UNIQUE NOT NULL,
scaff VARCHAR(10) NOT NULL)"""):
QMessageBox.warning(self, "Magazzino",
QString("Creazione tabella fallita!"))
return False
if not ("magaslave" in self.db.tables()):
if not query.exec_("""CREATE TABLE magaslave (
id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
datains DATE NOT NULL,
abbi VARCHAR(50),
angro VARCHAR(50),
desc VARCHAR(100),
qt INTEGER NOT NULL DEFAULT '1',
imp DOUBLE NOT NULL DEFAULT '0.0',
equiv VARCHAR(100),
mmid INTEGER NOT NULL,
fatt VARCHAR(50),
note VARCHAR(200),
FOREIGN KEY (mmid) REFERENCES magamaster)"""):
QMessageBox.warning(self, "Magazzino",
QString("Creazione tabella fallita!"))
return False
QMessageBox.information(self, "Magazzino",
QString("Database Creato!"))
return True
def insertNewRecord(self, data_dict):
query = QSqlQuery(self.handle)
if query.prepare(self.insert_query % data_dict):
query.bindValue(":photo", data_dict['photo'])
return query.exec_()
else:
return False
def movDeposito( iddoc, deposito, ctabanco ):
'''
MOVIMIENTOS CONTABLE PARA UNA ENTRADA COMPRA
(-)deposito > sale de Caja genera:id=5, cod=110 001 001 000
(+)deposito > entra a cuenta del banco ctabanco
@param iddoc: El id del documento que genera estos movimientos
@type iddoc: int
@param deposito: TODO
@type deposito: Decimal
@param ctabanco: TODO
@type ctabanco: Decimal
'''
iddoc = str( iddoc )
total = deposito.to_eng_string()
ctabanco = str( ctabanco )
query = QSqlQuery()
query.prepare( "INSERT INTO cuentasxdocumento (idcuenta,iddocumento,monto) values " +
"(" + CAJAGENERAL + "," + iddoc + ",-" + total + ")," +
"(" + ctabanco + "," + iddoc + "," + total + ")" )
if not query.exec_():
print( iddoc )
print( query.lastError().text() )
raise Exception( "NO SE PUDIERON INSERTAR LAS CUENTAS CONTABLES" )
def createRole(self, role, dict):
try:
self.checkAndOpenDb()
except:
return
#making this so the instaciated permissions stay with different names
uuid = str(uuid4()).replace('-', '_')
role += '_'+uuid
sql = self.gen.createRole(role, dict)
split = sql.split(';')
query = QSqlQuery(self.db)
#try to revoke the permissions
try:
self.dropRole(role)
except:
pass
for inner in split:
if not query.exec_(inner):
if '42710' in query.lastError().text():
#In this case the role is already created (duplicate object error). We just need to proceed executing the grants.
continue
else:
raise Exception(self.tr('Problem assigning profile: ') +role+'\n'+query.lastError().text())
def 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 getDomainDict(self):
try:
self.checkAndOpenDb()
except:
return dict()
if self.getDatabaseVersion() == '2.1.3':
schemaList = ['cb', 'complexos', 'dominios']
elif self.getDatabaseVersion() == 'FTer_2a_Ed':
schemaList = ['pe','ge','ct', 'complexos']
else:
QgsMessageLog.logMessage(self.tr('Operation not defined for this database version!'), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
return
sql = self.gen.validateWithDomain(schemaList)
query = QSqlQuery(sql, self.db)
classDict = dict()
domainDict = dict()
while query.next():
schemaName = str(query.value(0))
className = str(query.value(1))
attName = str(query.value(2))
domainName = str(query.value(3))
domainTable = str(query.value(4))
domainQuery = str(query.value(5))
cl = schemaName+'.'+className
query2 = QSqlQuery(domainQuery,self.db)
while query2.next():
value = int(query2.value(0))
classDict = self.utils.buildNestedDict(classDict,[str(cl),str(attName)],[value])
return classDict
def setData( self, column, value ):
value = value.toList()
if column < 0:
return False
if column == 0:
try:
if not QSqlDatabase.database().isOpen():
if not QSqlDatabase.database().open():
raise Exception( "No se pudo conectar con la base de datos" )
query = QSqlQuery()
if not query.prepare( """
UPDATE categorias SET nombre = :nombre
WHERE idcategoria = %d
""" % self.itemData[1] ):
raise Exception( "No se pudo preparar la consulta para actualizar la categoria" )
query.bindValue( ":nombre", value[column].toString().strip() )
if not query.exec_():
raise Exception( "No se pudo actualizar la categoria" )
except Exception as inst:
logging.error( unicode( inst ) )
return False
self.itemData[column] = value[column]
return True
def findEPSG(self, db):
gen = self.factory.createSqlGenerator(self.isSpatialiteDB(db))
sql = gen.getSrid()
query = QSqlQuery(sql, db)
srids = []
while query.next():
srids.append(query.value(0))
return srids[0]
def getDatabaseVersion(self, db):
gen = self.factory.createSqlGenerator(self.isSpatialiteDB(db))
sqlVersion = gen.getEDGVVersion()
queryVersion = QSqlQuery(sqlVersion, db)
version = '2.1.3'
while queryVersion.next():
version = queryVersion.value(0)
return version
def tableHasField(dbhandle, table, field):
"""does the table contain a column named field?"""
query = QSqlQuery(dbhandle)
query.exec_('select * from %s' % table)
record = query.record()
for idx in range(record.count()):
if record.fieldName(idx) == field:
return True
def _query(self, querystring, **mappings):
query = QSqlQuery(self.db)
query.prepare(querystring)
for key, value in mappings.iteritems():
bindvalue = ":{}".format(key)
if bindvalue in querystring:
query.bindValue(bindvalue, value)
return query
def create_table(self):
"""Create table Progression(song, pattern, key) if not exist already"""
# Tablen nimen saa vaihtaa
query = QSqlQuery()
query.prepare("CREATE TABLE IF NOT EXISTS Progressions(song TEXT PRIMARY KEY, pattern TEXT, key TEXT)")
success = query.exec_()
if not success:
pass # TODO
def eliminar_producto(self):
query=QSqlQuery()
sql="delete from productos where codigo='%s'"%self.codigo
if query.exec_(sql):
return True
else:
return False
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 showFeilds_mysql(self):
defaultDB = QSqlDatabase.database()
query = QSqlQuery("desc {}".format(self.listWidgetTables.currentItem().text()))
qe = query.exec_()
if qe:
self.listWidgetFeilds.clear()
while query.next():
self.listWidgetFeilds.addItem(query.value(0).toString())
def obtainLinkColumn(self, complexClass, aggregatedClass):
#query to obtain the link column between the complex and the feature layer
sql = self.gen.getLinkColumn(complexClass, aggregatedClass)
query = QSqlQuery(sql, self.db)
column_name = ""
while query.next():
column_name = query.value(0)
return column_name
def updateArticles( self ):
"""
Actualizar la lista de articulos
"""
query = QSqlQuery()
try:
if not self.database.isOpen():
if not self.database.open():
raise UserWarning( u"No se pudo conectar con la "
+ "base de datos" )
self.updateArticleList( query )
self.editmodel.updateLines( query )
providers_model = QSqlQueryModel()
providers_model.setQuery( """
SELECT idpersona, nombre
FROM personas p
WHERE tipopersona = 2 AND activo = 1
""" )
if not providers_model.rowCount() > 0:
raise UserWarning( "No existen proveedores en el sistema" )
self.cbProvider.setModel( providers_model )
self.cbProvider.setModelColumn( 1 )
warehouse_model = QSqlQueryModel()
warehouse_model.setQuery( """
SELECT idbodega, nombrebodega
FROM bodegas b
ORDER BY idbodega
""" )
if not warehouse_model.rowCount() > 0:
raise UserWarning( "No existen bodegas en el sistema" )
self.cbWarehouse.setModel( warehouse_model )
self.cbWarehouse.setModelColumn( 1 )
self.cbWarehouse.setCurrentIndex( -1 )
self.cbProvider.setCurrentIndex( -1 )
except UserWarning as inst:
QMessageBox.warning( self, qApp.organizationName(),
unicode( inst ) )
logging.error( query.lastError().text() )
logging.error( unicode( inst ) )
self.cancel()
except Exception as inst:
QMessageBox.critical( self, qApp.organizationName(),
"Hubo un error fatal al tratar de actualizar la lista " \
+ "de articulos, el sistema no puede recuperarse" \
+ " y sus cambios se han perdido" )
logging.error( query.lastError().text() )
logging.critical( unicode( inst ) )
self.cancel()
def getDatabaseVersion(self, db):
version = '-1'
if not db.open():
return version
sqlVersion = 'SELECT edgvversion FROM db_metadata LIMIT 1'
queryVersion = QSqlQuery(sqlVersion, db)
while queryVersion.next():
version = queryVersion.value(0)
return version
def prepare(self, query_string):
query = QSqlQuery(self.db)
query.setForwardOnly(True)
# weird; QSqlQuery instances don't appear to be GC'ed by the time the DB is closed - so I capture them in a strong-ref'd
# set and finish them up myself manually.
self.__refQuery(query)
if query.prepare(query_string):
return query
raise SyntaxError("failed to prepare query, sql was: '{}', db error: {}".format(query_string, query.lastError().text()))
def _query(self, querystring, **mappings):
querystring = querystring.replace(r"\r\n", " ")
query = QSqlQuery(self.db)
query.prepare(querystring)
for key, value in mappings.iteritems():
bindvalue = ":{}".format(key)
if re.search(r"{}\b".format(bindvalue), querystring):
query.bindValue(bindvalue, value)
return query
def enviar_comandos(self):
query=QSqlQuery()
r=True
for comando in self.comandos:
if not query.exec_(comando):
r=False
return r
def nuevo_producto(self, datos):
query = QSqlQuery()
sql = """ insert into productos values(null,"%s","%s","%s",%.2f,%d)""" % datos
if not query.exec_(sql):
print "Error en algun dato de producto"
return
self.codigo = datos[0]
def save(self):
resultado = False
try:
if not self.database.isOpen():
if not self.database.open():
raise UserWarning( u"No se pudo abrir la conexión "\
+ "con la base de datos" )
if not self.database.transaction():
raise Exception( u"No se pudo comenzar la transacción" )
query = QSqlQuery()
if not query.prepare( """
UPDATE preciosproducto
SET activo = 0
WHERE idproducto = :id;
""" ):
raise Exception( "No se pudo preparar la consulta para actualizar" )
query.bindValue( ":id", self.articuloId)
if not query.exec_():
raise Exception( "No se pudo desactivar el precio actual" )
if not query.prepare( """
INSERT INTO preciosproducto(idproducto,precio,unidadesxcaja)
VALUES (:id,:precio,:cantidad);
""" ):
raise Exception( "No se pudo preparar la consulta para insertar los nuevos precios" )
query.bindValue( ":id", self.articuloId)
query.bindValue( ":precio", self.precio)
query.bindValue( ":unidades", self.unidades )
if not query.exec_():
raise Exception( "No se pudo insertar el nuevo precio" )
if not self.database.commit():
raise Exception( "No se pudo hacer commit" )
resultado = True
except UserWarning as inst:
self.database.rollback()
print unicode( inst )
QMessageBox.critical( self, qApp.organizationName(), unicode( inst ) )
resultado = False
finally:
if self.database.isOpen():
self.database.close()
return resultado
def exec_query(self, q_string):
query = QSqlQuery(self.db)
query.exec_(q_string)
return query
query.exec_("insert into person values(102, 'Christine', 'Holand')")
query.exec_("insert into person values(103, 'Lars', 'Gordon')")
query.exec_("insert into person values(104, 'Roberto', 'Robitaille')")
query.exec_("insert into person values(105, 'Maria', 'Papadopoulos')")
query.exec_("select id, firstname from person")
return query
#query = setup_db_sqlite()
from pyscada.gui.qt_dbhelp import dburl_to_qsqldb
try:
# Se lo chantamos así nomás
qApp.db_con = dburl_to_qsqldb(
'mysql://*****:*****@localhost:3306/dsem')
q = QSqlQuery("SET NAMES 'utf8'", qApp.db_con)
print q.exec_()
except Exception, e:
import traceback
QMessageBox.critical(
None, "Error",
u"<h2>%s</h2></pre>%s</pre>" % (type(e), traceback.format_exc()))
sys.exit()
query = QSqlQuery('''
SELECT CONCAT(UC.nombre_uc, '(' ,UC.id_UC,')'),
UC.id FROM UC WHERE UC.co_id = %(co_id)d
''' % {'co_id': 1})
print "Error?", query.lastError().databaseText()
def updateEditModels(self):
"""
Este metodo actualiza los modelos usados en el modo edición
"""
resultado = False
try:
if not self.database.isOpen():
if not self.database.open():
raise UserWarning( u"No se pudo abrir la conexión "\
+ "con la base de datos" )
self.clientesModel.setQuery("""
SELECT idpersona , nombre AS cliente
FROM personas
WHERE escliente = 1
""")
self.cbcliente.setModel(self.clientesModel)
self.cbcliente.setModelColumn(1)
self.clienteCompleter.setCaseSensitivity(Qt.CaseInsensitive)
self.clienteCompleter.setModel(self.clientesModel)
self.clienteCompleter.setCompletionColumn(1)
self.cbcliente.setCompleter(self.clienteCompleter)
self.editmodel = FacturaModel()
# Cargar el numero de la factura actual
query = QSqlQuery("""
SELECT MAX(CAST( IFNULL(referencia,0) AS SIGNED)) FROM documentos d WHERE idtipodoc =%d;
""" % constantes.IDFACTURA)
if not query.exec_():
raise Exception("No se pudo obtener el numero de la factura")
query.first()
if query.size() == 0:
n = 1
else:
n = str(int(query.value(0)) + 1)
self.editmodel.printedDocumentNumber = str(
int(query.value(0)) + 1)
self.lblnumero.setText(n)
# if self.clientesModel.rowCount() == 0:
# raise UserWarning( "No existen clientes en la"\
# + " base de datos" )
# return
self.clienteCompleter.setModel(self.clientesModel)
self.cbcliente.setModel(self.clientesModel)
self.cbcliente.setCompleter(self.clienteCompleter)
# #Crear el delegado con los articulo y verificar si existen articulos
self.existenciaModel.setQuery(
QSqlQuery("""
SELECT
categoria,
descripcion,
precio,
unidadesxcaja,
-- cajas,
100 as cajas,
idprecioproducto
FROM vw_articulos
-- WHERE existencia >0
"""))
self.categoriesview.update("""
SELECT
categoria,
descripcion,
precio,
unidadesxcaja,
-- cajas,
100 as cajas,
idprecioproducto
FROM vw_articulos
WHERE idprecioproducto IS NOT NULL
-- WHERE existencia >0
""")
self.categoriesview.expandAll()
self.categoriesview.setColumnHidden(3, True)
self.categoriesview.setColumnHidden(4, True)
self.categoriesview.setColumnWidth(0, 150)
self.categoriesview.setColumnWidth(1, 60)
self.categoriesview.setColumnWidth(2, 20)
self.proxyexistenciaModel = SingleSelectionModel()
self.proxyexistenciaModel.setSourceModel(self.existenciaModel)
# self.proxyexistenciaModel.setFilterKeyColumn( IDBODEGAEX )
if self.proxyexistenciaModel.rowCount() == 0:
raise UserWarning("No hay articulos en bodega")
delegate = FacturaDelegate(self.proxyexistenciaModel)
self.tabledetails.setItemDelegate(delegate)
self.tabledetails.setModel(self.editmodel)
self.tabledetails.setColumnHidden(0, True)
# self.editmodel.insertRow(1)
self.editmodel.dataChanged[QModelIndex,
QModelIndex].connect(self.updateLabels)
self.txtobservaciones.setPlainText("")
self.dtPicker.setDate(QDate.currentDate().addDays(1))
self.editmodel.fecha = QDate.currentDate().addDays(1)
self.cbcliente.setCurrentIndex(-1)
resultado = True
except UserWarning as inst:
logging.error(unicode(inst))
QMessageBox.critical(self, qApp.organizationName(), unicode(inst))
finally:
if self.database.isOpen():
self.database.close()
return resultado
def save_modifications(self):
"""
Insert unmodified copy of the record, then save changes and deal with currency flag.
"""
unmod_query = QSqlQuery(self.db)
unmod_query.exec_(self.record_copy_sql)
# Save changes from dropdowns by directly editing model
current = self.mapper.currentIndex()
for idx, qitem in self.update_dict.iteritems():
combo = qitem[0]
try:
data = combo.itemData(combo.currentIndex())
except AttributeError:
if idx == 5:
# Desc text
data = qitem[1].toPlainText()
elif idx == 2:
# Version num
data = qitem[1].text()
else:
# Otherwise its a date
data_raw = combo.text()
date_obj = datetime.datetime.strptime(data_raw, "%d/%m/%Y")
data = str(date_obj.strftime("%Y%m%d"))
# Add modified data to the model
if not self.model.setData(self.model.createIndex(current, idx),
data):
msg = "Error updating database model\n"
msg += "Row: {}, Column: {}, Data: {}\n".format(
current, idx, data)
msg += self.model.lastError().text()
raise BaseException(msg)
# Compare model row before and after edit session
map_lookup = MapLookupValues(self.model)
updated_model = map_lookup.all_mapped_fields(current, 2, 1)
dirty = self.is_coords_dirty(self.cur_model, updated_model)
self.cur_model = None
coord_dirt = dirty[0]
other_dirt = dirty[1]
if not coord_dirt and not other_dirt:
# No edits so revert
self.cancel_modifications()
elif coord_dirt and not other_dirt:
# Only coords edited, so no need to to duplicate record, ver no, etc
self.model.submitAll()
self.cancel_modifications()
# Some data in the model has changed so submit and increment version number + updatedate
else:
# Auto set current date (update date)
data = str(datetime.datetime.now().strftime("%Y%m%d"))
self.model.setData(self.model.createIndex(current, 7), data)
# Change updated by
self.model.setData(self.model.createIndex(current, 23),
self.params['UserName'])
# Increment version number
cur_ver = int(self.street_browser.ui.versionLineEdit.text())
ver_plus_one = cur_ver + 1
self.model.setData(self.model.createIndex(current, 2),
str(ver_plus_one))
# Save esu links
self.update_esu_link(cur_ver, ver_plus_one)
# Commit to db
self.model.submitAll()
# Select all rows in the model again (else filtering fails)
self.post_model_submit(current)
def read(self, abfrage):
sql = abfrage
query = QSqlQuery(sql,self.db)
datensatz = {}
i = 0
while i < query.record().count():
result = []
query.first()
result.append(self.__pystring(query.value(i)))
lastError = query.lastError().text()
if len(lastError) > 1:
QMessageBox.information(None, self.tr('DB-Error'), lastError)
while query.next():
result.append(self.__pystring(query.value(i)))
lastError = query.lastError().text()
if len(lastError) > 1:
QMessageBox.information(None, self.tr('DB-Error'), lastError)
datensatz.update({str(query.record().fieldName(i)).upper(): result})
i = i + 1
return datensatz
def export_lor(self):
"""
Export list of roads to CSV.
"""
strstatussql = "" # List of road status codes to be included
strstreettypesql = "1,2" # List of street types to be included
strincludefieldsql = "" # SQL with additional fields to export
strordersql = "tblSTREET.Description" # SQL with additional sorting order
if self.any_radio:
if self.pub_chx:
strstatussql += "1"
if self.pro_chx:
if strstatussql != "":
if strstatussql != "":
strstatussql += ","
strstatussql += "2"
if self.priv_chx:
if strstatussql != "":
strstatussql += ","
strstatussql += "3"
if self.trunk_chx:
if strstatussql != "":
strstatussql += ","
strstatussql += "4"
else:
strstatussql = "1"
if self.town_chx:
strordersql = "tlkpTOWN.Name," + strordersql
if self.inc_t3_chx or self.inc_t4_chx:
if self.inc_t3_chx:
strstreettypesql += ",3"
if self.inc_t4_chx:
strstreettypesql += ",4"
strincludefieldsql += ", tblStreet.Street_Ref_Type as Type"
strordersql = "tblStreet.Street_Ref_Type," + strordersql
if self.inc_lor_chx:
strincludefieldsql += ", tblMaint.Lor_No as Road_No"
if self.usrn_cx:
strincludefieldsql += ", tblSTREET.USRN as USRN"
sql_lor = "SELECT DISTINCT tblSTREET.Description AS Name, tlkpTOWN.Name AS Town," \
" tblMAINT.Location_Text as Description, tlkpROAD_STATUS.Description as Status" \
" %s FROM (tlkpLOCALITY INNER JOIN (tlkpTOWN INNER JOIN tblSTREET" \
" ON tlkpTOWN.Town_Ref = tblSTREET.Town_Ref) ON tlkpLOCALITY.Loc_Ref = tblSTREET.Loc_Ref)" \
" INNER JOIN (tblMAINT INNER JOIN tlkpROAD_STATUS" \
" ON tblMAINT.Road_status_ref = tlkpROAD_STATUS.Road_status_ref) ON tblSTREET.USRN = tblMAINT.USRN" \
" WHERE ((tblMaint.Road_Status_Ref in ( %s ))" \
" AND (tblSTREET.Street_ref_type In ( %s )) AND (tblMAINT.Currency_flag=0)" \
" AND (tblSTREET.Currency_flag=0)) ORDER BY %s ;" % \
(strincludefieldsql, strstatussql, strstreettypesql, strordersql)
query = QSqlQuery(self.db)
query.exec_(sql_lor)
rec = query.record()
try:
# create the output file
if self.csv_chx:
self.csv_filename = os.path.join(self.path,
"{}LOR.csv".format(self.code))
output_file = open(self.csv_filename, "wb")
self.csv = csv.writer(output_file,
delimiter=',',
quotechar='"',
quoting=csv.QUOTE_NONNUMERIC,
lineterminator='\r')
else:
self.csv_filename = os.path.join(self.path,
"{}.txt".format(self.code))
output_file = open(self.csv_filename, "wb")
# loop over each result and write it the text or CSV
while query.next():
vals = [
query.value(rec.indexOf('Name')),
query.value(rec.indexOf('Town')),
query.value(rec.indexOf('Description')),
query.value(rec.indexOf('Status'))
]
if self.usrn_cx:
vals.append(query.value(rec.indexOf('USRN')))
if self.inc_lor_chx:
vals.append(query.value(rec.indexOf('Road_No')))
if self.csv_chx:
self.csv.writerow(list(vals))
else:
line = str()
for v in vals:
if type(v) != unicode:
v = unicode(v)
line += v + " "
line += "\n"
output_file.write(line)
output_file.close()
return None
except IOError:
return self.csv_filename
def results_to_txt_row(self, vals, sql, mode):
query_model = QSqlQueryModel()
query_model.setQuery(sql)
model_index = QModelIndex()
row_count = query_model.rowCount(model_index)
if row_count < 1:
self.file.write(self.no_content)
else:
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])
]
n = 0
# write content
headers = self.headers[0]
while n <= len(headers) - 1:
if n == len(headers) - 1:
self.file.write(str(headers[n]) + "\n")
else:
self.file.write(str(headers[n]) + " ")
n += 1
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]),
query.value(avals[8]),
query.value(avals[9]),
query.value(avals[10]),
query.value(avals[11])
]
self.file.write(
str(line[0]) + " , " + str(line[1]) + " , " +
str(line[2]) + " , " + str(line[3]) + " , " +
str(line[4]) + " , " + str(line[5]) + " , " +
self.format_dates(str(line[6])) + " , " +
self.format_dates(str(line[7])) + " , " +
self.format_dates(str(line[8])) + " , " +
self.format_dates(str(line[9])) + " , " +
str(line[10]) + " " + str(line[11]) + "\n")
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])
]
m = 0
headers = self.headers[1]
while m <= len(headers) - 1:
if m == len(headers) - 1:
self.file.write(str(headers[m]) + "\n")
else:
self.file.write(str(headers[m]) + " ")
m += 1
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.file.write(
str(line[0]) + " , " + str(line[1]) + " , " +
str(line[2]) + " , " + str(line[3]) + " , " +
str(line[4]) + " , " + str(line[5]) + " , " +
str(line[6]) + " , " + str(line[7]) + "\n")
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 updateTextBrowser(self):
# check to make sure we have a feature selected in our selectList -- note that there might be more than one feature
phoutput = ""
moistureoutput = ""
if self.phList:
# ############ EXAMPLE 1 EDITS GO HERE ####################
''' write code that will output ALL selected feature attributes for a single feature into the Text Browser'''
''' instead of using the dataProvider.select() function get the actual QgsFeature using dataProvider.featureAtId() '''
# get the feature by passing in empty Feature
request = QgsFeatureRequest(self.phList[0])
for f in self.pHLayer.getFeatures(request):
phoutput = "Soil pH Level: %s \n" % (f['pH'])
if self.MoistureList:
request = QgsFeatureRequest(self.MoistureList[0])
for f in self.MoistureLayer.getFeatures(request):
moistureoutput = "Soil Moisture Level: %s \n" % (f['moisture_level'])
output = phoutput + moistureoutput
self.dlg.setTextBrowser(output)
provider = self.pHLayer.dataProvider()
if provider.name() == 'postgres':
# get the URI containing the connection parameters
uri = QgsDataSourceURI(provider.dataSourceUri())
print uri.uri()
# create a PostgreSQL connection using QSqlDatabase
db = QSqlDatabase.addDatabase('QPSQL')
# check to see if it is valid
if db.isValid():
print "QPSQL db is valid"
# set the parameters needed for the connection
db.setHostName(uri.host())
db.setDatabaseName(uri.database())
db.setPort(int(uri.port()))
db.setUserName(uri.username())
db.setPassword(uri.password())
# open (create) the connection
if db.open():
print "Opened %s" % uri.uri()
# execute a simple query
#query = db.exec_("""select genus,species from botanical_name where genus = 'Prunus'""")
query = QSqlQuery ("""select genus,species from botanical_name where genus = 'Prunus'""")
self.dlg.tblPlants.clear()
self.dlg.tblPlants.setRowCount(query.size())
self.dlg.tblPlants.setColumnCount(query.record().count())
self.dlg.tblPlants.setHorizontalHeaderLabels(["Genus", "Species"])
self.dlg.tblPlants.setSelectionMode(QTableWidget.SingleSelection)
self.dlg.tblPlants.setSelectionBehavior(QTableWidget.SelectRows)
# loop through the result set and print the name
index=0
while query.next():
record = query.record()
self.dlg.tblPlants.setItem(index, 0, QTableWidgetItem(query.value(0)))
self.dlg.tblPlants.setItem(index, 1, QTableWidgetItem(query.value(1)))
index = index+1
# print record.field('name').value().toString()
self.dlg.tblPlants.resizeColumnsToContents()
else:
err = db.lastError()
print err.driverText()
def run(self, fid = 0):
##################################
###### PEGA A LAYER ATIVA ########
##################################
parametros = self.layer.source().split(" ") # recebe todos os parametros em uma lista ( senha, porta, password etc..)
####################################
###### INICIANDO CONEXÃO DB ########
####################################
# Outra opção para isso, seria usar ex: self.dbname.. self.host.. etc.. direto dentro do laço for.
dbname = ""
host = ""
port = 0
user = ""
password = ""
for i in parametros:
part = i.split("=")
# Recebe os parametros guardados na própria Layer
if "dbname" in part[0]:
dbname = part[1].replace("'", "")
elif "host" in part[0]:
host = part[1].replace("'", "")
elif "port" in part[0]:
port = int(part[1].replace("'", ""))
elif "user" in part[0]:
user = part[1].replace("'", "")
elif "password" in part[0]:
password = part[1].split("|")[0].replace("'", "")
print dbname, host, port, user, password
# Testa se os parametros receberam os valores pretendidos, caso não, apresenta a mensagem informando..
if len(dbname) == 0 or len(host) == 0 or port == 0 or len(user) == 0 or len(password) == 0:
self.iface.messageBar().pushMessage("Erro", u'Um dos parametros não foram devidamente recebidos!', level=QgsMessageBar.CRITICAL, duration=4)
return
####################################
#### SETA VALORES DE CONEXÃO DB ####
####################################
connection = QSqlDatabase.addDatabase('QPSQL')
connection.setHostName(host)
connection.setPort(port)
connection.setUserName(user)
connection.setPassword(password)
connection.setDatabaseName(dbname)
if not connection.isOpen(): # Testa se a conexão esta recebendo os parametros adequadamente.
if not connection.open():
print 'Error connecting to database!'
self.iface.messageBar().pushMessage("Erro", u'Error connecting to database!', level=QgsMessageBar.CRITICAL, duration=4)
print connection.lastError().text()
return
####################################
###### CRIAÇÃO DE MEMORY LAYER #####
####################################
layerCrs = self.layer.crs().authid() # Passa o formato (epsg: numeros)
flagsLayerName = self.layer.name() + "_flags"
flagsLayerExists = False
for l in QgsMapLayerRegistry.instance().mapLayers().values(): # Recebe todas as camadas que estão abertas
if l.name() == flagsLayerName: # ao encontrar o nome pretendido..
self.flagsLayer = l # flagslayer vai receber o nome..
self.flagsLayerProvider = l.dataProvider()
flagsLayerExists = True # se encontrado os parametros buscados, recebe True.
break
if flagsLayerExists == False: # se não encontrado os parametros buscados, recebe False.
tempString = "Point?crs="
tempString += str(layerCrs)
self.flagsLayer = QgsVectorLayer(tempString, flagsLayerName, "memory")
self.flagsLayerProvider = self.flagsLayer.dataProvider()
self.flagsLayerProvider.addAttributes([QgsField("flagId", QVariant.String), QgsField("geomId", QVariant.String), QgsField("motivo", QVariant.String)])
self.flagsLayer.updateFields()
if fid == 0: # Se for 0 então está iniciando e limpa, caso contrário não.
self.flagsLayer.startEditing()
ids = [feat.id() for feat in self.flagsLayer.getFeatures()]
self.flagsLayer.deleteFeatures(ids)
self.flagsLayer.commitChanges()
lista_fid = [] # Iniciando lista
for f in self.layer.getFeatures():
lista_fid.append(str(f.id())) # Guarda na lista. A lista de Feature ids passa tipo "int", foi convertido e guardado como "str".
source = self.layer.source().split(" ")
self.tableName = "" # Inicia vazio
layerExistsInDB = False
for i in source:
if "table=" in i or "layername=" in i: # Se encontrar os atributos pretendidos dentre todos do for
self.tableName = source[source.index(i)].split(".")[1] # Faz split em ponto e pega a segunda parte.
self.tableName = self.tableName.replace('"', '')
layerExistsInDB = True
break
if layerExistsInDB == False:
self.iface.messageBar().pushMessage("Erro", u"Provedor da camada corrente não provem do banco de dados!", level=QgsMessageBar.CRITICAL, duration=4)
return
geomType = self.layer.geometryType()
if geomType == QGis.Line:
# Busca através do SQL
sql = """
WITH result AS (SELECT points."{4}", points.anchor, (degrees
(
ST_Azimuth(points.anchor, points.pt1) - ST_Azimuth(points.anchor, points.pt2)
)::decimal + 360) % 360 as angle
FROM
(SELECT
ST_PointN("{3}", generate_series(1, ST_NPoints("{3}")-2)) as pt1,
ST_PointN("{3}", generate_series(2, ST_NPoints("{3}")-1)) as anchor,
ST_PointN("{3}", generate_series(3, ST_NPoints("{3}"))) as pt2,
linestrings."{4}" as "{4}"
FROM
(SELECT "{4}" as "{4}", (ST_Dump("{3}")).geom as "{3}"
FROM only "{0}"."{1}"
) AS linestrings WHERE ST_NPoints(linestrings."{3}") > 2 ) as points)
select distinct "{4}", ST_AsText(anchor), angle from result where (result.angle % 360) < {2} or result.angle > (360.0 - ({2} % 360.0)) and result.{4} in ({5})""".format( self.tableSchema, self.tableName, self.angle, self.geometryColumn, self.keyColumn,",".join(lista_fid))
elif geomType == QGis.Polygon:
sql = """
WITH result AS (SELECT points."{4}", points.anchor, (degrees
(
ST_Azimuth(points.anchor, points.pt1) - ST_Azimuth(points.anchor, points.pt2)
)::decimal + 360) % 360 as angle
FROM
(SELECT
ST_PointN("{3}", generate_series(1, ST_NPoints("{3}")-1)) as pt1,
ST_PointN("{3}", generate_series(1, ST_NPoints("{3}")-1) % (ST_NPoints("{3}")-1)+1) as anchor,
ST_PointN("{3}", generate_series(2, ST_NPoints("{3}")) % (ST_NPoints("{3}")-1)+1) as pt2,
linestrings."{4}" as "{4}"
FROM
(SELECT "{4}" as "{4}", (ST_Dump(ST_Boundary(ST_ForceRHR((ST_Dump("{3}")).geom)))).geom as "{3}"
FROM only "{0}"."{1}"
) AS linestrings WHERE ST_NPoints(linestrings."{3}") > 2 ) as points)
select distinct "{4}", ST_AsText(anchor), angle from result where (result.angle % 360) < {2} or result.angle > (360.0 - ({2} % 360.0)) and result.{4} in ({5})""".format( self.tableSchema, self.tableName, self.angle, self.geometryColumn, self.keyColumn,",".join(lista_fid) )
query = QSqlQuery(sql)
self.flagsLayer.startEditing()
flagCount = fid # iniciando contador que será referência para os IDs da camada de memória.
listaFeatures = []
while query.next():
id = query.value(0)
local = query.value(1)
angulo = query.value(2)
flagId = str(flagCount)
flagFeat = QgsFeature()
flagFeat.setFields(self.flagsLayer.fields()) # passa quais atributos serão usados.
flagGeom = QgsGeometry.fromWkt(local) # passa o local onde foi localizado o erro.
flagFeat.setGeometry(flagGeom)
flagFeat.initAttributes(3)
flagFeat.setAttribute(0,flagId) # insere o id definido para a coluna 0 da layer de memória.
flagFeat.setAttribute(1, id) # insere o id da geometria para a coluna 1 da layer de memória.
flagFeat.setAttribute(2, u"Ângle-ExcededBound")
listaFeatures.append(flagFeat)
flagCount += 1 # incrementando o contador a cada iteração.
self.flagsLayerProvider.addFeatures(listaFeatures)
self.flagsLayer.commitChanges() # Aplica as alterações à camada.
QgsMapLayerRegistry.instance().addMapLayer(self.flagsLayer) # Adicione a camada no mapa.
return flagCount
def CadastrarCliente(cliente):
conn = ConexaoSQL
db = conn.getConexao()
db.open()
query = QSqlQuery()
query.prepare(
"INSERT INTO Cliente(Nome, CPF, Endereco, Email, Telefone) "
"VALUES (?, ?, ?, ?, ?)")
query.addBindValue(cliente.Nome)
query.addBindValue(cliente.CPF)
query.addBindValue(cliente.Endereco)
query.addBindValue(cliente.Email)
query.addBindValue(cliente.Telefone)
query.exec_()
db.commit()
class GkukanMusiumdbDockWidget(QtGui.QDockWidget, FORM_CLASS):
closingPlugin = pyqtSignal()
GKukanMusiumMessage = pyqtSignal(unicode, int)
def __init__(self, iface, land, parent=None):
"""Constructor."""
super(GkukanMusiumdbDockWidget, self).__init__(parent)
# Set up the user interface from Designer.
# After setupUI you can access any designer object by doing
# self.<objectname>, and you can use autoconnect slots - see
# http://qt-project.org/doc/qt-4.8/designer-using-a-ui-file.html
# #widgets-and-dialogs-with-auto-connect
self.setupUi(self)
self.loadListCount = 0
self.loadmax = self.tblPhotos.columnCount()
self.tblPhotos.setEditTriggers(QtGui.QAbstractItemView.NoEditTriggers)
self.iface = iface
self.land = land #Landmark toolbox
self.canvas = self.iface.mapCanvas()
self.geoCrs = QgsCoordinateReferenceSystem(4326)
self.db = QSqlDatabase.addDatabase('QPSQL')
self.photolist = []
self.filtermode = False
self.btnReloadphoto.clicked.connect(self.FilterMode)
self.btnLoadMorePhoto.clicked.connect(self.AddMorePhotosToList)
self.btnSetLandmark.clicked.connect(self.AddSelectiontoLandmark)
self.btnClearSelection.clicked.connect(self.ClearSelection)
self.btnSetToPoint.clicked.connect(self.SetPointToSelection)
self.tblPhotos.cellDoubleClicked.connect(self.clickPhoto)
self.btnLoadMorePhoto.setEnabled(False)
self.btnSetLandmark.setEnabled(False)
self.btnSetToPoint.setEnabled(False)
def closeEvent(self, event):
self.closingPlugin.emit()
event.accept()
def clickPhoto(self, r, c):
tbl = self.tblPhotos
p = tbl.item(1, c)
if hasattr(p, 'data') == False:
return
a = p.data(32)
if not a:
return
if r == 0 or (a.lon == 0 and a.lat == 0):
dlg = ViewPhotoDialog(a.filePath)
dlg.exec_()
return
point = self._transformPoint(QgsPoint(a.lon, a.lat))
self.canvas.freeze(True)
self.canvas.setCenter(point)
self.canvas.freeze(False)
self.canvas.refresh()
def SetPointToSelection(self):
pass
if self._checkLoadLayer() == False:
return
tbl = self.tblPhotos
if len(tbl.selectedItems()) == 0:
return
pl = []
for p in tbl.selectedItems():
a = p.data(32)
if a is not None:
pl.append(a)
if self.AskUpdatePoint(pl) is False:
return
if len(pl):
self.canvas.freeze(True)
self.SetPhotoPosition(pl, self.canvas.center())
self.canvas.freeze(False)
self.canvas.refresh()
i = 0
for p in tbl.selectedItems():
if p.row() == 1:
p.setBackground(QBrush(QColor('#FFFFFF')))
def AskUpdatePoint(self, pl):
i = False
for p in pl:
if p.lat:
if p.lon:
i = True
if i:
reply = QtGui.QMessageBox.question(self, u'位置情報変更の確認',
u"画像に紐づく位置情報を変更してもよろしいですか?",
QtGui.QMessageBox.Yes,
QtGui.QMessageBox.No)
if reply == QtGui.QMessageBox.Yes:
return True
else:
return False
else:
return True
def AddSelectiontoLandmark(self):
if self.loadListCount == 0:
return
tbl = self.tblPhotos
if len(tbl.selectedItems()) == 0:
return
lid = self.land.toolbox.getLandmarkID()
if lid is None:
return
pl = []
for p in tbl.selectedItems():
a = p.data(32)
if a is not None:
pl.append(a)
if self.AskUpdateLandmark(lid, pl) is False:
return
if len(pl):
if self.SetLandMarkID(lid, pl):
self.land.toolbox._highlightLandmark()
self.land.toolbox.populatePhotos()
def AskUpdateLandmark(self, lid, pl):
i = False
for p in pl:
if p.landmark_id:
if p.landmark_id <> lid:
i = True
if i:
reply = QtGui.QMessageBox.question(self, u'史料情報変更の確認',
u"画像に紐づく史料情報を変更してもよろしいですか?",
QtGui.QMessageBox.Yes,
QtGui.QMessageBox.No)
if reply == QtGui.QMessageBox.Yes:
return True
else:
return False
else:
return True
def FilterMode(self):
self.filtermode = self.FilterNoEdit.isChecked()
self.ResetTable()
self.AddPhotosToList()
self.btnLoadMorePhoto.setEnabled(True)
self.btnSetLandmark.setEnabled(True)
self.btnSetToPoint.setEnabled(True)
def ResetTable(self):
self.tblPhotos.clear()
del self.photolist[:]
self.loadListCount = 0
self.tblPhotos.setColumnCount(20)
self.tblPhotos.setBackgroundRole(QtGui.QPalette.Dark)
self.loadmax = self.tblPhotos.columnCount()
self.tblPhotos.setEditTriggers(QtGui.QAbstractItemView.NoEditTriggers)
def ScrollTableLeft(self):
tbl = self.tblPhotos
tbl.setColumnCount(self.loadListCount + 5)
self.LoadMorePhotoThread(5)
def LoadPhotosToList(self):
self.loadListCount = 0
self.AddPhotosToList()
def AddMorePhotosToList(self):
oldst = self.loadListCount
n = 100
QApplication.setOverrideCursor(Qt.WaitCursor)
if not self.GetPhotosList(self.loadListCount + 1,
self.loadListCount + n):
QApplication.restoreOverrideCursor()
return
tbl = self.tblPhotos
tbl.verticalHeader().resizeSection(0, 50)
for i in range(oldst, self.loadListCount):
fn = os.path.join(self.folderpath, self.photolist[i].filename)
self.photolist[i].filePath = fn
tp = os.path.join(os.path.join(self.folderpath, 'thumb'),
str(self.photolist[i].p_id) + '.png')
self.photolist[i].thumbPath = tp
item = ImgWidget(fn, tp, self)
hd = QTableWidgetItem(str(self.photolist[i].p_id))
tbl.insertColumn(i)
tbl.setHorizontalHeaderItem(i, hd)
tbl.setCellWidget(0, i, item)
b = QtGui.QTableWidgetItem(self.photolist[i].filename)
b.setData(32, self.photolist[i])
tbl.setItem(1, i, b)
if self.photolist[i].lon == 0 and self.photolist[i].lat == 0:
tbl.item(1, i).setBackground(QBrush(QColor('#CCFFFF')))
if self.photolist[i].keywords:
tbl.setItem(2, i,
QtGui.QTableWidgetItem(self.photolist[i].keywords))
QgsMessageLog.logMessage(u"LoadPhoto..." + str(i),
tag="LoadMorePhotoThread",
level=QgsMessageLog.INFO)
QApplication.restoreOverrideCursor()
def AddPhotosToList(self):
oldst = self.loadListCount
QApplication.setOverrideCursor(Qt.WaitCursor)
if not self.GetPhotosList(self.loadListCount + 1,
self.loadListCount + self.loadmax):
QApplication.restoreOverrideCursor()
return
tbl = self.tblPhotos
tbl.verticalHeader().resizeSection(0, 50)
self.ClearTableCells()
for i in range(oldst, self.loadListCount):
fn = os.path.join(self.folderpath, self.photolist[i].filename)
self.photolist[i].filePath = fn
tp = os.path.join(os.path.join(self.folderpath, 'thumb'),
str(self.photolist[i].p_id) + '.png')
self.photolist[i].thumbPath = tp
item = ImgWidget(fn, tp, self)
pos = i - oldst
hd = QTableWidgetItem(str(i + 1))
tbl.setHorizontalHeaderItem(pos, hd)
tbl.setCellWidget(0, pos, item)
b = QtGui.QTableWidgetItem(self.photolist[i].filename)
b.setData(32, self.photolist[i])
tbl.setItem(1, i, b)
if self.photolist[i].lon == 0 and self.photolist[i].lat == 0:
tbl.item(1, i).setBackground(QBrush(QColor('#CCFFFF')))
if self.photolist[i].keywords:
tbl.setItem(2, pos,
QtGui.QTableWidgetItem(self.photolist[i].keywords))
QgsMessageLog.logMessage(u"LoadPhoto..." + str(i),
tag="LoadPhotoThread",
level=QgsMessageLog.INFO)
QApplication.restoreOverrideCursor()
def ClearSelection(self):
tbl = self.tblPhotos
if len(tbl.selectedItems()) == 0:
return
tbl.selectionModel().clearSelection()
def ClearTableCells(self):
tbl = self.tblPhotos
tbl.clear()
def openDatabase(self):
if self.db.isValid():
settings = QSettings('MatsueGkukan', 'Gkukandb')
dbHostName = settings.value('hostname')
dbDatabaseName = settings.value('databasename')
dbUserName = settings.value('username')
dbPassword = settings.value('dbpassword')
self.db.setHostName(dbHostName)
self.db.setDatabaseName(dbDatabaseName)
self.db.setUserName(dbUserName)
self.db.setPassword(dbPassword)
if not self.db.open():
self.GKukanMusiumMessage.emit(
self.tr('Can not open GKukanMusium database'),
QgsMessageBar.WARNING)
return False
self.query = QSqlQuery(self.db)
return True
else:
settings = QSettings('MatsueGkukan', 'Gkukandb')
dbHostName = settings.value('hostname')
dbDatabaseName = settings.value('databasename')
dbUserName = settings.value('username')
dbPassword = settings.value('dbpassword')
self.db.removeDatabase(dbDatabaseName)
del self.db
self.db = None
self.db = QSqlDatabase.addDatabase('QPSQL')
self.db.setHostName(dbHostName)
self.db.setDatabaseName(dbDatabaseName)
self.db.setUserName(dbUserName)
self.db.setPassword(dbPassword)
if not self.db.open():
self.GKukanMusiumMessage.emit(
self.tr('Can not open GKukanMusium database'),
QgsMessageBar.WARNING)
return False
self.query = QSqlQuery(self.db)
return True
return False
def GetPhotoFolderPath(self):
if not self.openDatabase():
return False
if self.query.exec_(u'select * from m_folder'):
self.query.first()
self.folderpath = self.query.value(2)
ret = self.folderpath
else:
ret = ''
self.db.close()
return ret
def GetPhotosList(self, st, ed):
folder = self.GetPhotoFolderPath()
if not self.openDatabase():
return False
l = ed - st + 1
if self.filtermode:
sql = u'select * from (select row_number() over() as rid, * from t_photo where t_photo.lat=0 and t_photo.lon=0 order by p_id) as p where (p.rid between %s and %s ) limit %s;' % (
str(st), str(ed), str(l))
else:
sql = u'select * from (select row_number() over() as rid, * from t_photo order by p_id) as p where p.rid between %s and %s limit %s;' % (
str(st), str(ed), str(l))
if self.query.exec_(sql):
self.query.first()
for i in range(self.query.size()):
v = self.query.value
self.photolist.append(
PhotoData(v(1), v(2), v(3), v(4), v(5), v(6), v(7), v(8),
v(9), v(10), v(11), v(12), v(13), v(14), v(15),
v(16), v(17), v(18), v(19), v(20), v(21), v(22)))
self.query.next()
self.loadListCount += self.query.size()
self.query.clear()
self.db.close()
return True
else:
self.db.close()
return False
def SetLandMarkID(self, lid, pl):
if not len(pl):
return False
if not self.openDatabase():
return False
a = []
for p in pl:
a.append(p.p_id)
sp = ",".join(map(str, a))
sql = u'UPDATE t_photo SET landmark_id=%s WHERE p_id in (%s);' % (
str(lid), str(sp))
print sql
if self.query.exec_(sql):
self.query.clear()
self.db.close()
else:
self.db.close()
return False
return True
def SetPhotoPosition(self, pl, pos):
if not len(pl):
return False
a = []
for p in pl:
a.append(p.p_id)
sp = ",".join(map(str, a))
pos = self._transformMapToPoint(pos)
geom = QgsGeometry.fromPoint(pos)
x = geom.asQPointF().x()
y = geom.asQPointF().y()
gt = "'" + geom.exportToWkt() + "'"
sql = u'UPDATE t_photo SET lon=%s,lat=%s,geom=ST_GeomFromText(%s,4326) WHERE p_id in (%s);' % (
str(x), str(y), gt, str(sp))
v = self.convertGeomFromPostGis(geom)
self.updatePhotoDataXY(a, x, y, v)
if not self.openDatabase():
return False
if self.query.exec_(sql):
self.query.clear()
self.db.close()
else:
a = self.query.lastError().text()
self.db.close()
return False
return True
def updatePhotoDataXY(self, pl, x, y, v):
pass
pp = self.photolist
for i in range(len(pp)):
if pp[i].p_id in pl:
self.photolist[i].x = x
self.photolist[i].y = y
self.photolist[i].geom = v
return
def decodeBinary(self, wkb):
"""Decode the binary wkb and return as a hex string"""
value = binascii.a2b_hex(wkb)
value = value[::-1]
value = binascii.b2a_hex(value)
return value
def _checkLoadLayer(self):
pass
return True
def _transformMapToPoint(self, pnt):
crsDest = self.canvas.mapSettings().destinationCrs()
xform = QgsCoordinateTransform(crsDest, self.geoCrs)
p2 = xform.transform(pnt)
return p2
def _transformPoint(self, pnt):
crsDest = self.canvas.mapSettings().destinationCrs()
xform = QgsCoordinateTransform(self.geoCrs, crsDest)
p2 = xform.transform(pnt)
return p2
def convertGeomFromPostGis(self, geom):
if not self.openDatabase():
return None
gt = "'" + geom.exportToWkt() + "'"
sql = u'SELECT ST_GeomFromText(%s,4326);' % (gt)
if self.query.exec_(sql):
self.query.first()
v = self.query.value
self.db.close()
return v
else:
a = self.query.lastError().text()
self.db.close()
return False
if __name__ == "__main__":
app = QCoreApplication(sys.argv)
parser = OptionParser()
parser.add_option("-m", "--month", dest="month", help="El mes a descargar")
parser.add_option("-y", "--year", dest="year", help=u"El año a descargar")
(options, args) = parser.parse_args()
db = QSqlDatabase.addDatabase("QMYSQL")
db.setDatabaseName("esquipulasdb")
db.setUserName("root")
db.setHostName("localhost")
db.setPassword("root")
query = QSqlQuery()
try:
year = int(options.year if not options.year is None else datetime.date.
today().year)
month = int(options.month if not options.month is None else datetime.
date.today().month)
url = "http://www.elpueblopresidente.com/servicios/wsmoneda.php?formato=jsonvalido&ano=%d&mes=%d" % (
year, month)
print url
file = urllib.urlopen(url)
data = json.loads(file.read())
if not db.open():
raise Exception(u"No se pudo abrir la conexión a la base de datos")
def __init__(self,
cmdList,
args=None,
dbHandle=None,
silent=False,
mayFail=False):
"""we take a list of sql statements. Only the last one is allowed to be
a select statement.
Do prepared queries by passing a single query statement in cmdList
and the parameters in args. If args is a list of lists, execute the
prepared query for every sublist.
If dbHandle is passed, use that for db access.
Else if the default dbHandle (DBHandle.default) is defined, use it."""
# pylint: disable=R0912
# pylint says too many branches
silent |= not Debug.sql
self.dbHandle = dbHandle or DBHandle.default
preparedQuery = not isinstance(cmdList, list) and bool(args)
self.query = QSqlQuery(self.dbHandle)
self.msg = None
self.records = []
if not isinstance(cmdList, list):
cmdList = list([cmdList])
self.cmdList = cmdList
for cmd in cmdList:
retryCount = 0
while retryCount < 100:
self.lastError = None
if preparedQuery:
self.query.prepare(cmd)
if not isinstance(args[0], list):
args = list([args])
for dataSet in args:
if not silent:
_, utf8Args = xToUtf8(u'', dataSet)
logDebug("{cmd} [{args}]".format(
cmd=cmd, args=", ".join(utf8Args)))
for value in dataSet:
self.query.addBindValue(QVariant(value))
self.success = self.query.exec_()
if not self.success:
break
else:
if not silent:
logDebug('%s %s' % (self.dbHandle.name, cmd))
self.success = self.query.exec_(cmd)
if self.success or self.query.lastError().number() not in (5,
6):
# 5: database locked, 6: table locked. Where can we get symbols for this?
break
time.sleep(0.1)
retryCount += 1
if not self.success:
self.lastError = unicode(self.query.lastError().text())
self.msg = 'ERROR in %s: %s' % (self.dbHandle.databaseName(),
self.lastError)
if mayFail:
if not silent:
logDebug(self.msg)
else:
logException(self.msg)
return
self.records = None
self.fields = None
if self.query.isSelect():
self.retrieveRecords()
class Query(object):
"""a more pythonic interface to QSqlQuery. We could instead use
the python sqlite3 module but then we would either have to do
more programming for the model/view tables, or we would have
two connections to the same database.
For selecting queries we fill a list with ALL records.
Every record is a list of all fields. q.records[0][1] is record 0, field 1.
For select, we also convert to python data
types - as far as we need them"""
localServerName = m18ncE('kajongg name for local game server',
'Local Game')
def __init__(self,
cmdList,
args=None,
dbHandle=None,
silent=False,
mayFail=False):
"""we take a list of sql statements. Only the last one is allowed to be
a select statement.
Do prepared queries by passing a single query statement in cmdList
and the parameters in args. If args is a list of lists, execute the
prepared query for every sublist.
If dbHandle is passed, use that for db access.
Else if the default dbHandle (DBHandle.default) is defined, use it."""
# pylint: disable=R0912
# pylint says too many branches
silent |= not Debug.sql
self.dbHandle = dbHandle or DBHandle.default
preparedQuery = not isinstance(cmdList, list) and bool(args)
self.query = QSqlQuery(self.dbHandle)
self.msg = None
self.records = []
if not isinstance(cmdList, list):
cmdList = list([cmdList])
self.cmdList = cmdList
for cmd in cmdList:
retryCount = 0
while retryCount < 100:
self.lastError = None
if preparedQuery:
self.query.prepare(cmd)
if not isinstance(args[0], list):
args = list([args])
for dataSet in args:
if not silent:
_, utf8Args = xToUtf8(u'', dataSet)
logDebug("{cmd} [{args}]".format(
cmd=cmd, args=", ".join(utf8Args)))
for value in dataSet:
self.query.addBindValue(QVariant(value))
self.success = self.query.exec_()
if not self.success:
break
else:
if not silent:
logDebug('%s %s' % (self.dbHandle.name, cmd))
self.success = self.query.exec_(cmd)
if self.success or self.query.lastError().number() not in (5,
6):
# 5: database locked, 6: table locked. Where can we get symbols for this?
break
time.sleep(0.1)
retryCount += 1
if not self.success:
self.lastError = unicode(self.query.lastError().text())
self.msg = 'ERROR in %s: %s' % (self.dbHandle.databaseName(),
self.lastError)
if mayFail:
if not silent:
logDebug(self.msg)
else:
logException(self.msg)
return
self.records = None
self.fields = None
if self.query.isSelect():
self.retrieveRecords()
def rowcount(self):
"""how many rows were affected?"""
return self.query.numRowsAffected()
def retrieveRecords(self):
"""get all records from SQL into a python list"""
record = self.query.record()
self.fields = [record.field(x) for x in range(record.count())]
self.records = []
while self.query.next():
self.records.append(
[self.__convertField(x) for x in range(record.count())])
def __convertField(self, idx):
"""convert a QSqlQuery field into a python value"""
result = self.query.value(idx).toPyObject()
if isinstance(result, QString):
result = unicode(result)
if isinstance(result,
long) and -sys.maxint - 1 <= result <= sys.maxint:
result = int(result)
return result
def loadDatabaseStructure(self, edgvPath):
"""
Loads the database structure
edgvPath: path to the databse sql
"""
commands = []
hasTemplate = self.abstractDb.checkTemplate(self.version)
if not hasTemplate:
file = codecs.open(edgvPath, encoding='utf-8', mode="r")
sql = file.read()
sql = sql.replace('[epsg]', '4674')
file.close()
commands = sql.split('#')
# Progress bar steps calculated
self.signals.rangeCalculated.emit(len(commands)+4, self.getId())
if not hasTemplate:
try:
self.abstractDb.createTemplateDatabase(self.version)
self.signals.stepProcessed.emit(self.getId())
self.connectToTemplate()
self.signals.stepProcessed.emit(self.getId())
except Exception as e:
return (0, self.messenger.getProblemFeedbackMessage()+'\n'+':'.join(e.args))
self.db.open()
self.db.transaction()
query = QSqlQuery(self.db)
for command in commands:
if not self.stopped[0]:
if not query.exec_(command):
QgsMessageLog.logMessage(self.messenger.getProblemMessage(command, query), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
self.db.rollback()
self.db.close()
self.dropDatabase(self.db)
return (0, self.messenger.getProblemFeedbackMessage())
# Updating progress
self.signals.stepProcessed.emit(self.getId())
else:
self.db.rollback()
self.db.close()
self.dropDatabase(self.db)
QgsMessageLog.logMessage(self.messenger.getUserCanceledFeedbackMessage(), "DSG Tools Plugin", QgsMessageLog.INFO)
return (-1, self.messenger.getUserCanceledFeedbackMessage())
self.db.commit()
if self.version == '2.1.3':
sql = 'ALTER DATABASE %s SET search_path = "$user", public, topology,\'cb\',\'complexos\',\'dominios\';' % self.db.databaseName()
elif self.version == 'FTer_2a_Ed':
sql = 'ALTER DATABASE %s SET search_path = "$user", public, topology,\'pe\',\'ge\',\'complexos\',\'dominios\';' % self.db.databaseName()
if not query.exec_(sql):
QgsMessageLog.logMessage(self.messenger.getProblemMessage(command, query), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
return (0, self.messenger.getProblemFeedbackMessage())
#this commit was missing, so alter database statement was not commited.
self.db.commit()
self.db.close()
self.abstractDb.setDbAsTemplate(self.version)
#creates from template
if not self.stopped[0]:
templateName = self.abstractDb.getTemplateName(self.version)
self.abstractDb.createDbFromTemplate(self.dbName, templateName, parentWidget = self.parent)
self.signals.stepProcessed.emit(self.getId())
#5. alter spatial structure
createdDb = self.dbFactory.createDbFactory('QPSQL')
createdDb.connectDatabaseWithParameters(self.abstractDb.db.hostName(), self.abstractDb.db.port(), self.dbName, self.abstractDb.db.userName(), self.abstractDb.db.password())
errorTuple = createdDb.updateDbSRID(self.epsg, parentWidget = self.parent, threading = True)
# if an error occur during the thread we should pass the message to the main thread
if errorTuple:
QgsMessageLog.logMessage(self.messenger.getProblemMessage(errorTuple[0], errorTuple[1]), "DSG Tools Plugin", QgsMessageLog.CRITICAL)
return (0, self.messenger.getProblemFeedbackMessage())
self.signals.stepProcessed.emit(self.getId())
else:
QgsMessageLog.logMessage(self.messenger.getUserCanceledFeedbackMessage(), "DSG Tools Plugin", QgsMessageLog.INFO)
return (-1, self.messenger.getUserCanceledFeedbackMessage())
QgsMessageLog.logMessage(self.messenger.getSuccessFeedbackMessage(), "DSG Tools Plugin", QgsMessageLog.INFO)
return (1, self.messenger.getSuccessFeedbackMessage())
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()
class DataProcessor:
def __init__(self, source):
self.layer = None
self.dataFile = None
regex = re.compile("dbname='(.+)'")
r = regex.search(source)
self.source = r.groups()[0]
#QPSQL
#self.db = QSqlDatabase.addDatabase('QPSQL')
self.db = QSqlDatabase.addDatabase('QSQLITE')
encoding = locale.getdefaultlocale()[1]
if encoding is None:
self.csvEncoding = 'utf-8'
else:
self.csvEncoding = encoding.lower()
def setLayer(self, layer):
self.layer = layer
def setDataFile(self, fileName):
self.dataFile = fileName
def exportData(self):
landmarks = self._exportLayer()
if landmarks is None:
return False
photos = self._exportPhotos()
if photos is None:
return False
zf = zipfile.ZipFile(self.dataFile, 'w')
zf.write(landmarks, 'landmarks.csv')
zf.write(photos, 'photos.csv')
zf.close()
return True
def importData(self):
if not self._importLayer():
return False
if not self._importPhotos():
return False
return True
def _openDatabase(self):
self.db.setDatabaseName(self.source)
if not self.db.open():
return False
self.query = QSqlQuery()
return True
def _exportLayer(self):
fileName = self._tempFileName()
err = QgsVectorFileWriter.writeAsVectorFormat(
self.layer,
fileName,
self.csvEncoding,
self.layer.crs(),
'CSV',
layerOptions=['GEOMETRY=AS_XY'])
if err != QgsVectorFileWriter.NoError:
return None
return fileName
def _importLayer(self):
ids = []
titles = []
types = []
angles = []
x = []
y = []
result = False
zf = zipfile.ZipFile(self.dataFile, 'r')
fh = StringIO.StringIO(zf.read('landmarks.csv'))
reader = UnicodeReader(fh,
dialect='excel',
quotechar='"',
encoding=self.csvEncoding)
reader.next()
for r in reader:
x.append(float(r[0]) if r[0] else None)
y.append(float(r[1]) if r[1] else None)
ids.append(int(r[2]) if r[2] else None)
titles.append(r[3])
types.append(int(r[4]) if r[4] else None)
angles.append(float(r[5]) if r[5] else None)
if not self._openDatabase():
return False
self.query.prepare(
'INSERT OR REPLACE INTO landmark("id", "title", "icon_type", "label_angle", "the_geom") VALUES(?, ?, ?, ?, MakePoint(?, ?, 4326));'
)
self.query.addBindValue(ids)
self.query.addBindValue(titles)
self.query.addBindValue(types)
self.query.addBindValue(angles)
self.query.addBindValue(x)
self.query.addBindValue(y)
if self.query.execBatch():
result = True
self.db.close()
return result
def _exportPhotos(self):
if not self._openDatabase():
return None
result = None
fileName = self._tempFileName()
self.query.prepare(
'SELECT "id", "angle", "date", "title", "comment", "imagepath", "landmark_id" FROM photo;'
)
if self.query.exec_():
result = fileName
with open(fileName, 'wb') as f:
writer = UnicodeWriter(f,
dialect='excel',
quotechar='"',
encoding=self.csvEncoding)
writer.writerow(('id', 'angle', 'date', 'title', 'comment',
'imagepath', 'landmark_id'))
while self.query.next():
pid = unicode(self.query.value(
0)) if self.query.value(0) != None else ''
angle = unicode(
self.query.value(1)) if self.query.value(1) else ''
date = self.query.value(2) if self.query.value(2) else ''
title = self.query.value(3) if self.query.value(3) else ''
comment = self.query.value(4) if self.query.value(
4) else ''
imgpath = self.query.value(5) if self.query.value(
5) else ''
landmarkid = unicode(self.query.value(
6)) if self.query.value(6) != None else ''
writer.writerow((pid, angle, date, title, comment, imgpath,
landmarkid))
self.db.close()
return result
def _importPhotos(self):
pids = []
angles = []
dates = []
titles = []
comments = []
paths = []
ids = []
result = False
zf = zipfile.ZipFile(self.dataFile, 'r')
fh = StringIO.StringIO(zf.read('photos.csv'))
reader = UnicodeReader(fh,
dialect='excel',
quotechar='"',
encoding=self.csvEncoding)
reader.next()
for r in reader:
pids.append(int(r[0]) if r[0] else None)
angles.append(float(r[1]) if r[1] else None)
dates.append(r[2])
titles.append(r[3])
comments.append(r[4])
paths.append(r[5])
ids.append(int(r[6]) if r[6] else None)
if not self._openDatabase():
return False
self.query.prepare(
'INSERT OR REPLACE INTO photo("id", "angle", "date", "title", "comment", "imagepath", "landmark_id") VALUES(?, ?, ?, ?, ?, ?, ?);'
)
self.query.addBindValue(pids)
self.query.addBindValue(angles)
self.query.addBindValue(dates)
self.query.addBindValue(titles)
self.query.addBindValue(comments)
self.query.addBindValue(paths)
self.query.addBindValue(ids)
if self.query.execBatch():
result = True
self.db.close()
return result
def _tempDirectory(self):
tmp = unicode(os.path.join(QDir.tempPath(), 'landmark'))
if not os.path.exists(tmp):
QDir().mkpath(tmp)
return os.path.abspath(tmp)
def _tempFileName(self):
tmpDir = self._tempDirectory()
fName = os.path.join(tmpDir,
str(uuid.uuid4()).replace('-', '') + '.csv')
return fName
def validateWithOutputDatabaseSchema(self, outputAbstractDb):
try:
self.checkAndOpenDb()
except:
return dict()
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)
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]:
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', '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]])
else:
invalidated['classNotFoundInOutput'].append(inputAttrList)
return invalidated
def _open_database(self, dbPath):
"""Opens a database.
Checks if there are geometry_columns and spatial_ref_sys
tables in the database, if not it creates and fills those tables.
Checks if there are all PU columns in PAR table,
if it it creates and fills those columns.
Args:
dbPath (str): A full path to the database.
Raises:
dw.puError: When SQLITE database driver is not available
or when database connection fails.
"""
if not QSqlDatabase.isDriverAvailable('QSQLITE'):
raise self.dW.puError(
self.dW, self, u'SQLITE database driver is not available.',
u'Databázový ovladač QSQLITE není dostupný.',
u'Databázový ovladač QSQLITE není dostupný.')
connectionName = QUuid.createUuid().toString()
db = QSqlDatabase.addDatabase('QSQLITE', connectionName)
db.setDatabaseName(dbPath)
db.open()
if not db.open():
raise self.dW.puError(self.dW, self,
u'Database connection failed.',
u'Nepodařilo se připojit k databázi.',
u'Nepodařilo se připojit k databázi.')
self.set_text_statusbar.emit(u'Kontroluji tabulky a sloupce...', 0,
False)
QgsApplication.processEvents()
sqlQuery = QSqlQuery(db)
sqlDir = QDir(self.pluginDir.path() + u'/data/sql')
query = self._read_text_from_file(sqlDir.filePath(u'check_gc_srs.sql'))
sqlQuery.exec_(query)
QgsApplication.processEvents()
checkGcSrsSize = 0
while sqlQuery.next():
checkGcSrsSize += 1
if checkGcSrsSize < 2:
queries = self._read_text_from_file(
sqlDir.filePath(u'create_fill_gc_srs.sql')).split(';')
for query in queries:
sqlQuery.exec_(query)
QgsApplication.processEvents()
query = self._read_text_from_file(
sqlDir.filePath(u'check_pu_columns_PAR.sql'))
sqlQuery.exec_(query)
QgsApplication.processEvents()
columns = []
while sqlQuery.next():
record = sqlQuery.record()
name = str(record.value('name'))
columns.append(name)
if not all(column in columns for column in self.dW.allPuColumns):
queries = self._read_text_from_file(
sqlDir.filePath(u'add_pu_columns_PAR.sql')).split(';')
for query in queries:
sqlQuery.exec_(query)
QgsApplication.processEvents()
queries = self._read_text_from_file(
sqlDir.filePath(u'create_sobr_spol.sql')).split(';')
for query in queries:
sqlQuery.exec_(query)
db.close()
def createConnection():
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("my.db")
if not db.open():
QMessageBox.critical(0, "Cannot open database1",
"Unable to establish a database connection.",
QMessageBox.Cancel)
return False
query = QSqlQuery()
# 创建student表
query.exec_(
QString("create table student (id int primary key, "
"name varchar, course int)"))
query.exec_(QString("insert into student values(1, '李强', 11)"))
query.exec_(QString("insert into student values(2, '马亮', 11)"))
query.exec_(QString("insert into student values(3, '孙红', 12)"))
# 创建course表
query.exec_(
QString("create table course (id int primary key, "
"name varchar, teacher varchar)"))
query.exec_(QString("insert into course values(10, '数学', '王老师')"))
query.exec_(QString("insert into course values(11, '英语', '张老师')"))
query.exec_(QString("insert into course values(12, '计算机', '白老师')"))
return True
def loadAssociatedFeatures(self, complex):
try:
self.checkAndOpenDb()
except:
return dict()
associatedDict = dict()
#query to get the possible links to the selected complex in the combobox
complexName = complex.replace('complexos_', '')
sql = self.gen.getComplexLinks(complexName)
query = QSqlQuery(sql, self.db)
while query.next():
#setting the variables
complex_schema = query.value(0)
complex = query.value(1)
aggregated_schema = query.value(2)
aggregated_class = query.value(3)
column_name = query.value(4)
#query to obtain the created complexes
sql = self.gen.getComplexData(complex_schema, complex)
complexQuery = QSqlQuery(sql, self.db)
while complexQuery.next():
complex_uuid = complexQuery.value(0)
name = complexQuery.value(1)
if not (complex_uuid and name):
continue
associatedDict = self.utils.buildNestedDict(
associatedDict, [name, complex_uuid, aggregated_class], [])
#query to obtain the id of the associated feature
sql = self.gen.getAssociatedFeaturesData(
aggregated_schema, aggregated_class, column_name,
complex_uuid)
associatedQuery = QSqlQuery(sql, self.db)
while associatedQuery.next():
ogc_fid = associatedQuery.value(0)
associatedDict = self.utils.buildNestedDict(
associatedDict, [name, complex_uuid, aggregated_class],
[ogc_fid])
return associatedDict
def export_polys(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_poly_ids = list()
if count > 0:
selectedfeats = clayer.selectedFeatures()
for feat in selectedfeats:
selected_poly_ids.append(int(feat.attribute('rd_pol_id')))
feature_count = clayer.selectedFeatureCount()
self.warn_about_selected_features(feature_count)
# Prepare sql query
if self.unassigned:
polyexportsql = self.sql_queries['export_all']
else:
polyexportsql = self.sql_queries['export_assigned_only']
# SQL to filter out unselected and/or public road records
if count > 0:
polyexportsql += " WHERE rd_pol_id IN ({})".format(', '.join(
map(str, selected_poly_ids)))
else:
polyexportsql += " WHERE rd_pol_id IS NOT NULL"
if self.public_only:
polyexportsql += " AND is_public = 1"
# Setup database temporary tables
for table in ['maint_records']:
# Drop tables if left behind from last export
args = {'table': table}
query = self.run_sql('drop_table', args)
query = self.run_sql('create_maint_records')
# Run the main query
if config.DEBUG_MODE:
print(polyexportsql)
query = QSqlQuery(self.db)
query.setForwardOnly(True)
query.exec_(polyexportsql)
if query.isActive() is False:
raise StandardError('Database query problem: {}'.format(
query.lastError().text()))
# create layer
vlayer = QgsVectorLayer("multipolygon?crs=EPSG:27700", "temp",
"memory")
vlayer.setCrs(
QgsCoordinateReferenceSystem(
27700, QgsCoordinateReferenceSystem.EpsgCrsId))
provider = vlayer.dataProvider()
# add fields
self.fields = [
QgsField("poly_id", QVariant.String),
QgsField("RefNo", QVariant.LongLong),
QgsField("rec_type", QVariant.Int),
QgsField("desctxt", QVariant.String),
QgsField("Locality", QVariant.String),
QgsField("Town", QVariant.String),
QgsField("LocTxt", QVariant.String),
QgsField("RdStatus", QVariant.LongLong),
QgsField("Swa_org", QVariant.String),
QgsField("Adopt_Date", QVariant.Date),
QgsField("Entry_Date", QVariant.Date),
QgsField("lor_no", QVariant.Int),
QgsField("route", QVariant.String),
QgsField("symbol", QVariant.Int),
QgsField("element", QVariant.String),
QgsField("hierarchy", 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 records
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
self.percent_complete = (i / float(diff)) * 100
self.progresswin.setValue(self.percent_complete)
if self.killed:
# Show message and exit if killed
export_error_msg_box = QMessageBox(
QMessageBox.Warning, " ",
"An error occurred while exporting the"
"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
def save(self, save_not_accepted=False):
text = self.textEdit.toInitialHtmlFormat()
links = re.findall(r'<[Aa][^>]*>.*?</[Aa]>', text)
for l in links:
print("LINK - " + l)
if "termin##" in l:
href = re.findall(r'href=([^>]*?)>', l)[0]
if "status##1##" in l:
if save_not_accepted:
print("HREF - " + href)
new_href = '"termin;' + href.split(
"##")[1].__str__() + '"'
print("NEW HREF - " + new_href)
new_l = l.replace(href, new_href)
print("NEW LINK - " + new_l)
# заменить и сохранить ссылку
text = text.replace(l, new_l)
else:
new_l = re.findall(r'<[Aa][^>]*>(.*?)</[Aa]>', l)[0]
print("RES TEXT - " + new_l)
text = text.replace(l, new_l)
elif "status##2##" in l:
new_l = re.findall(r'<[Aa][^>]*>(.*?)</[Aa]>', l)[0]
print("RES TEXT - " + new_l)
# удалить ссылку, потому что не однозначная
text = text.replace(l, new_l)
elif "status##" not in l:
print("HREF - " + href)
new_href = '"termin;' + href.split("##")[1].__str__() + '"'
print("NEW HREF - " + new_href)
new_l = l.replace(href, new_href)
print("NEW LINK - " + new_l)
text = text.replace(l, new_l)
# заменить и сохранить ссылку, т.к. она принята
print('\n')
print(text)
local_update = QSqlQuery(self.local_cn)
local_update.prepare(update_local_termin_query)
local_update.bindValue(":linked", 1)
local_update.bindValue(":uuid", self.current_uuid)
remote_update = QSqlQuery(self.remote_cn)
if self.onlyClassified:
remote_update.prepare(update_remote_termin_query_OC)
else:
remote_update.prepare(update_remote_termin_query)
remote_update.bindValue(":definition", text)
remote_update.bindValue(":uuid", self.current_uuid)
QtSql.QSqlDatabase.database('SQLiteConnection').transaction()
QtSql.QSqlDatabase.database('PGconnection').transaction()
local_status = False
remote_status = False
if remote_update.exec_():
remote_status = True
else:
print(remote_update.lastQuery())
print(remote_update.lastError().text())
if local_update.exec_():
local_status = True
else:
print(local_update.lastQuery())
print(local_update.lastError().text())
if local_status and remote_status:
QtSql.QSqlDatabase.database('SQLiteConnection').commit()
QtSql.QSqlDatabase.database('PGconnection').commit()
return True
else:
QtSql.QSqlDatabase.database('SQLiteConnection').rollback()
QtSql.QSqlDatabase.database('PGconnection').rollback()
return False
def run(self):
##################################
###### PEGA A LAYER ATIVA ########
##################################
layer = self.iface.activeLayer()
if not layer:
self.iface.messageBar().pushMessage("Erro", u"Esperando uma Active Layer!", level=QgsMessageBar.CRITICAL, duration=4)
return
if layer.featureCount() == 0:
self.iface.messageBar().pushMessage("Erro", u"a camada não possui feições!", level=QgsMessageBar.CRITICAL, duration=4)
return
parametros = layer.source().split(" ") # recebe todos os parametros em uma lista ( senha, porta, password etc..)
####################################
###### INICIANDO CONEXÃO DB ########
####################################
# Outra opção para isso, seria usar ex: self.dbname.. self.host.. etc.. direto dentro do laço for.
dbname = ""
host = ""
port = 0
user = ""
password = ""
for i in parametros:
part = i.split("=")
# Recebe os parametros guardados na própria Layer
if "dbname" in part[0]:
dbname = part[1].replace("'", "")
elif "host" in part[0]:
host = part[1].replace("'", "")
elif "port" in part[0]:
port = int(part[1].replace("'", ""))
elif "user" in part[0]:
user = part[1].replace("'", "")
elif "password" in part[0]:
password = part[1].split("|")[0].replace("'", "")
#print dbname, host, port, user, password
# Testa se os parametros receberam os valores pretendidos, caso não, apresenta a mensagem informando..
if len(dbname) == 0 or len(host) == 0 or port == 0 or len(user) == 0 or len(password) == 0:
self.iface.messageBar().pushMessage("Erro", u'Um dos parametros não foram devidamente recebidos!', level=QgsMessageBar.CRITICAL, duration=4)
return
####################################
#### SETA VALORES DE CONEXÃO DB ####
####################################
connection = QSqlDatabase.addDatabase('QPSQL')
connection.setHostName(host)
connection.setPort(port)
connection.setUserName(user)
connection.setPassword(password)
connection.setDatabaseName(dbname)
if not connection.isOpen(): # Testa se a conexão esta recebendo os parametros adequadamente.
if not connection.open():
print 'Error connecting to database!'
self.iface.messageBar().pushMessage("Erro", u'Error connecting to database!', level=QgsMessageBar.CRITICAL, duration=4)
print connection.lastError().text()
return
####################################
###### CRIAÇÃO DE MEMORY LAYER #####
####################################
layerCrs = layer.crs().authid() # Passa o formato (epsg: numeros)
flagsLayerName = layer.name() + "_flags"
flagsLayerExists = False
for l in QgsMapLayerRegistry.instance().mapLayers().values(): # Recebe todas as camadas que estão abertas
if l.name() == flagsLayerName: # ao encontrar o nome pretendido..
self.flagsLayer = l # flagslayer vai receber o nome..
self.flagsLayerProvider = l.dataProvider()
flagsLayerExists = True # se encontrado os parametros buscados, recebe True.
break
if flagsLayerExists == False: # se não encontrado os parametros buscados, recebe False.
tempString = "Point?crs="
tempString += str(layerCrs)
self.flagsLayer = QgsVectorLayer(tempString, flagsLayerName, "memory")
self.flagsLayerProvider = self.flagsLayer.dataProvider()
self.flagsLayerProvider.addAttributes([QgsField("id", QVariant.Int), QgsField("motivo", QVariant.String)])
self.flagsLayer.updateFields()
self.flagsLayer.startEditing()
ids = [feat.id() for feat in self.flagsLayer.getFeatures()]
self.flagsLayer.deleteFeatures(ids)
self.flagsLayer.commitChanges()
lista_fid = [] # Iniciando lista
for f in layer.getFeatures():
lista_fid.append(str(f.id())) # Guarda na lista. A lista de Feature ids passa tipo "int", foi convertido e guardado como "str".
source = layer.source().split(" ")
self.tableName = "" # Inicia vazio
layerExistsInDB = False
for i in source:
if "table=" in i or "layername=" in i: # Se encontrar os atributos pretendidos dentre todos do for
self.tableName = source[source.index(i)].split(".")[1] # Faz split em ponto e pega a segunda parte.
self.tableName = self.tableName.replace('"', '')
layerExistsInDB = True
break
if layerExistsInDB == False:
self.iface.messageBar().pushMessage("Erro", u"Provedor da camada corrente não provem do banco de dados!", level=QgsMessageBar.CRITICAL, duration=4)
return
# Busca através do SQL
query_string = '''select distinct (reason(ST_IsValidDetail(f."{2}",0))) AS motivo, '''
query_string += '''ST_AsText(ST_Multi(ST_SetSrid(location(ST_IsValidDetail(f."{2}",0)), ST_Srid(f.{2})))) as local from '''
query_string += '''(select "{3}", "{2}" from only "{0}"."{1}" where ST_IsValid("{2}") = 'f' and {3} in ({4})) as f'''
query_string = query_string.format(self.tableSchema, self.tableName, self.geometryColumn, self.keyColumn, ",".join(lista_fid))
query = QSqlQuery(query_string)
self.flagsLayer.startEditing()
flagCount = 0 # iniciando contador que será referência para os IDs da camada de memória.
listaFeatures = []
while query.next():
motivo = query.value(0)
local = query.value(1)
flagId = flagCount
flagFeat = QgsFeature()
flagGeom = QgsGeometry.fromWkt(local) # passa o local onde foi localizado o erro.
flagFeat.setGeometry(flagGeom)
flagFeat.initAttributes(2)
flagFeat.setAttribute(0,flagId) # insere o id definido para a coluna 0 da layer de memória.
flagFeat.setAttribute(1, motivo) # insere o motivo/razão pré-definida para a coluna 1 da layer de memória.
listaFeatures.append(flagFeat)
flagCount += 1 # incrementando o contador a cada iteração
self.flagsLayerProvider.addFeatures(listaFeatures)
self.flagsLayer.commitChanges() # Aplica as alterações à camada.
QgsMapLayerRegistry.instance().addMapLayer(self.flagsLayer) # Adicione a camada no mapa
##################################
##### TROCA MARCADOR DA LAYER ####
##################################
# # AQUI PRETENDO PERSONALIZAR O MARCADOR DA MEMORY LAYER, POREM A PRINCIPIO SÓ ESTA ALTERANDO A LAYER ORIGINAL.
# for lay in QgsMapLayerRegistry.instance().mapLayers().values():
# if lay == flagsLayerName and not layer.name():
# # Estilo base.
# line = QgsLineSymbolV2()
# # Crie uma linha de marcadores.
# marker_line = QgsMarkerLineSymbolLayerV2()
# marker_line.setInterval(10)
# # Configure o marcador.
# simple_marker = QgsSimpleMarkerSymbolLayerV2()
# simple_marker.setShape(QgsSimpleMarkerSymbolLayerBase.HalfSquare) # Formato
# simple_marker.setSize(3)
# simple_marker.setAngle(180)
# simple_marker.setColor(QColor('red')) # cor
# simple_marker.setOutlineColor(QColor('red')) # cor
# # O marcador tem sua própria camada de símbolo.
# marker = QgsMarkerSymbolV2()
# marker.changeSymbolLayer(0, simple_marker)
# # Adicione a camada à camada do marcador.
# marker_line.setSubSymbol(marker)
# # Finalmente, substitua a camada de símbolo no estilo base.
# line.changeSymbolLayer(0, marker_line)
# # Adicione o estilo à camada de linha.
# renderer = QgsSingleSymbolRendererV2(line)
# layer.setRendererV2(renderer)
if flagCount == 0:
QgsMapLayerRegistry.instance().removeMapLayer(self.flagsLayer.id())
self.iface.messageBar().pushMessage("Aviso", u"Não foi encontrado Flags em \"" + layer.name() + "\" !", level=QgsMessageBar.CRITICAL, duration=4)
return
if len(query.lastError().text()) == 1:
self.iface.messageBar().pushMessage("Aviso", "foram geradas " + str(flagCount) + " flags para a camada \"" + layer.name() + "\" !", level=QgsMessageBar.INFO, duration=4)
else:
self.iface.messageBar().pushMessage("Erro", u"a geração de flags falhou!", level=QgsMessageBar.CRITICAL, duration=4)
print query.lastError().text()
def setup_db_sqlite():
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName(":memory:")
if not db.open():
QMessageBox.critical(None, "No se puede acceder",
"No se puede acceder a la DB")
sys.exit(2)
query = QSqlQuery() # Toma la DB definida...
query.exec_("create table person(id int primary key, "
"firstname varchar(20), lastname varchar(20))")
query.exec_("insert into person values(102, 'Christine', 'Holand')")
query.exec_("insert into person values(103, 'Lars', 'Gordon')")
query.exec_("insert into person values(104, 'Roberto', 'Robitaille')")
query.exec_("insert into person values(105, 'Maria', 'Papadopoulos')")
query.exec_("select id, firstname from person")
return query
