def delete_failure(self, talk_id):
"""Removes a Presentation from the database"""
QtSql.QSqlQuery('''DELETE FROM failures WHERE Id="%s"''' % talk_id)
log.info("Failure %s deleted." % talk_id)
def Insertar(self):
print("entro a insertar")
# db = self.db.CreateConnection(self.usr, self.passw)
# Obtengo un cursor de DB abierto
print("Nombre de la conexión " + self.db.connectionName())
#Obtengo listado de campos de la tabla
campos = "SHOW COLUMNS from alumnos WHERE Field <> 'IdAlumnos' AND Field <> 'Apellido' AND Field <> 'cohorte' AND Field <> 'Grupo_Sanguineo' AND Field <> 'Ántitetanica' AND Field <> 'Presion_Arterial' AND Field <> 'Enfermedades' AND Field <> 'Tratamiento' AND Field <> 'alergias' AND Field <> 'foto'"
print("Nombre de la conexión " + self.db.connectionName())
if self.db.database('inscripciones').isOpen():
q = QtSql.QSqlQuery(self.db.database('inscripciones'))
q.prepare(campos)
q.exec_()
cadena = ""
print("entro al while")
while q.next():
cadena = cadena + q.value(0) + ", "
total = len(cadena.rstrip())
cadena1 = cadena[0:total -1]
print("Cadena " + cadena1)
#Preparo la cadena sql
values = ":nombre, :dni, :lugar, :fenac, :edad, :calle, :numero, :piso, :depto, :civil, :local, :partido, :cp, :tel, :mov, :ecurs, :otros, :trab, :activ, :emer, :os, :sexo, :carrera, :anio, :lunac, :hijos, :flia, :mail, :egreso, :insti, :escuela, :distri, :doc_dni, :doc_Tit, :doc_reg, :doc_fot, :doc_cert"
#sql = "INSERT INTO alumnos (" + cadena1 + ") VALUES (:nombre, :dni, :lugar, :fenac, :edad, :calle, :numero, :piso, :depto, :civil, :local, :partido, :cp, :tel, :mov, :ecurs, :otros, :trab, :activ, :emer, :os, :sexo, :carrera, :anio, :lunac, :hijos, :flia, :mail, :egreso, :insti1, :insti2, :escuela, :distri, :doc_dni, :doc_Tit, :doc_reg, :doc_fot, :doc_cert)"
sql = "INSERT INTO alumnos(" + cadena1 + ") VALUES (" + values + ")"
print(" Este sql " + sql)
# sql = "INSERT INTO alumnos(Nombre, DNI, Lugar_Nacimiento, Fecha_Nacimiento, Edad, Domicilio, numero, piso, depto, Estado_Civil, Localidad, Partido, CP, Telefono, Celular, Estudios_Cursados, Otros, Trabaja, Ocupacion, emergencias, osocial, Sexo, Carrera, ciclo, Nacionalidad, hijos, acargo, mail, egreso, insti_otros, escuela, distrito, doc_dni, doc_Tit, doc_Reg, doc_fot, doc_cert) VALUES (:nombre, :dni, :lugar, :fenac, :edad, :calle, :numero, :piso, :depto, :civil, :local, :partido, :cp, :tel, :mov, :ecurs, :otros, :trab, :activ, :emer, :os, :sexo, :carrera, :anio, :lunac, :hijos, :flia, :mail, :egreso, :insti, :escuela, :distri, :doc_dni, :doc_Tit, :doc_reg, :doc_fot, :doc_cert)" # , Fecha_Nacimiento, Edad, Domicilio, numero, piso, depto, Estado_Civil, Localidad, Partido, CP, Telefono, Celular, Estudios_Cursados, Otros, Trabaja, Ocupacion, emergencias, osocial, Sexo, Carrera, ciclo, Nacionalidad, hijos, acargo, mail, egreso, insti_otros, escuela, distrito, doc_dni, doc_Tit, doc_Reg, doc_fot, doc_cert) VALUES (:nombre,:dni,:lugar,:fenac,:edad,:calle,:numero,:piso,:depto,:civil,:local,:partido,:cp,:tel,:mov,:ecurs,:otros,:trab,:activ,:emer,:os,:sexo,:carrera,:anio,:lunac,:hijos,:flia,:mail,:egreso,:insti,:escuela :distri :doc_dni :doc_Tit :doc_reg :doc_fot)"'''
#Creo el objeto para hacer la consulta
print("Nombre de la conexión " + self.db.connectionName())
q = QtSql.QSqlQuery(self.db.database('inscripciones'))
q.prepare(sql)
q.bindValue(":nombre", self.Nombre)
q.bindValue(":dni", self.dni)
q.bindValue(":lugar", self.lnNa)
q.bindValue(":fenac", self.fenac)
q.bindValue(":edad", self.edad)
q.bindValue(":calle", self.calle)
q.bindValue(":numero", self.numero)
q.bindValue(":piso", self.piso)
q.bindValue(":depto", self.depto)
q.bindValue(":civil", self.civil)
q.bindValue(":local", self.local)
q.bindValue(":partido", self.partido)
q.bindValue(":cp", self.cp)
q.bindValue(":tel", self.tel) # Validador
q.bindValue(":mov", self.movil) # validador
q.bindValue(":ecurs", self.titulo)
q.bindValue(":otros", self.otros)
q.bindValue(":trab", self.trab)
q.bindValue(":activ", self.activ)
q.bindValue(":emer", self.emergencia)
q.bindValue(":os", self.os)
q.bindValue(":sexo", self.sexo)
q.bindValue(":carrera", self.carrera)
q.bindValue(":anio", self.anio)
q.bindValue(":lunac", self.lunac)
q.bindValue(":hijos", self.hijos)
q.bindValue(":flia", self.familia)
q.bindValue(":mail", self.mail)
q.bindValue(":egreso", self.egreso)
institutos = self.insti1 + ", " + self.insti2
q.bindValue(":insti", institutos)
q.bindValue(":escuela", self.escuela)
q.bindValue(":distri", self.distrito)
print(self.fotoDni)
q.bindValue(":doc_dni", self.fotoDni)
q.bindValue(":doc_Tit", self.fotoTit)
q.bindValue(":doc_reg", self.numreg)
q.bindValue(":doc_fot", self.fotos)
q.bindValue(":doc_cert", self.certif)
'''q.bindValue(":hora", self.horario)'''
if self.db.database('inscripciones').isOpen():
print("Damned world")
else:
print(self.db.connectionName() + " esta abierta")
q.prepare(sql)
estado = q.exec_()
pipi = q.executedQuery()
if estado is True:
print("tudobom")
self.close()
else:
print("DNI " + self.dni)
print(pipi)
print((self.db.database('inscripciones').lastError()))
def _slotPushButtonInitDBClicked(self):
self.firstDBConnection()
DBName = self.ui.comboBoxDB.currentText()
s="SELECT count(*) from pg_database\
WHERE datname = 'tempusaccess_"+DBName+"'";
q=QtSql.QSqlQuery(unicode(s), self.caller.db)
q.next()
if (int(q.value(0))>0):
ret = QMessageBox.question(self, "TempusAccess", u"La base de données 'tempusaccess_"+DBName+u"' existe déjà et va être réinitialisée : toutes les données présentes seront écrasées. \n Confirmez-vous cette opération ?", QMessageBox.Ok | QMessageBox.Cancel, QMessageBox.Cancel)
else:
ret = QMessageBox.Ok
if (ret == QMessageBox.Ok):
self.updateDBConnection()
self.prog = QProgressDialog(self)
self.prog.setCancelButton(None)
self.prog.setMinimum(0)
self.prog.setMaximum(100)
self.prog.setAutoClose(True)
self.prog.setWindowTitle(u"En cours...")
self.prog.show()
self.prog.setValue(5)
# Restart database server to be sure deleting "TempusAccess" database will be allowed (avoids still connected applications)
cmd = [ "python", "-m", "pglite", "stop" ]
r = subprocess.call( cmd, shell=True )
cmd = [ "python", "-m", "pglite", "start" ]
r = subprocess.call( cmd, shell=True )
self.prog.setValue(15)
# Delete database (if exists) and (re)create
cmd = [ "dropdb", "-h", self.caller.host, "-p", self.caller.port, self.caller.base ]
r = subprocess.call( cmd, shell=True )
cmd = [ "createdb", "-h", self.caller.host, "-p", self.caller.port, self.caller.base ]
r = subprocess.call( cmd, shell=True )
self.firstDBConnection()
self.refreshDBList()
self.ui.comboBoxDB.setCurrentIndex(self.ui.comboBoxDB.findText(DBName))
self.updateDBConnection()
self.prog.setValue(25)
if (self.caller.db.open() == False): # This should never be the case
self.ui.pushButtonImportDB.setEnabled(False)
self.ui.pushButtonBackupDB.setEnabled(False)
box = QMessageBox()
box.setText(u"La connexion à la base de données a échoué :" + unicode(self.caller.db.lastError().text()))
box.exec_()
else:
# Create data schema "tempus" and "tempus_gtfs"
dbstring = "host="+self.caller.host+" dbname="+self.caller.base+" port="+self.caller.port
cmd = ["python", "C:\\OSGeo4W64\\apps\\Python27\\lib\\site-packages\\tempusloader-1.2.2-py2.7.egg\\tempusloader\\load_tempus.py", "-t", "osm", "-d", dbstring, "-R"]
r = subprocess.call( cmd, shell=True )
self.prog.setValue(50)
# Add to data schema "tempus" et "tempus_gtfs" application specific elements, mainly in tempus_access schema
cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/init_bdd.sql"]
r = subprocess.call( cmd, shell=True )
cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/function_create_pt_stop_indicator_layer.sql"]
r = subprocess.call( cmd, shell=True )
cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/function_create_pt_section_indicator_layer.sql"]
r = subprocess.call( cmd, shell=True )
cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/function_create_pt_trip_indicator_layer.sql"]
r = subprocess.call( cmd, shell=True )
cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/function_create_pt_route_indicator_layer.sql"]
r = subprocess.call( cmd, shell=True )
cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/function_create_pt_agency_indicator_layer.sql"]
r = subprocess.call( cmd, shell=True )
cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/function_create_path_indicator_layer.sql"]
r = subprocess.call( cmd, shell=True )
cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/function_create_path_details_indicator_layer.sql"]
r = subprocess.call( cmd, shell=True )
cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/function_create_paths_tree_indicator_layer.sql"]
r = subprocess.call( cmd, shell=True )
cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/function_create_comb_paths_trees_indicator_layer.sql"]
r = subprocess.call( cmd, shell=True )
cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/function_create_isosurfaces_indicator_layer.sql"]
r = subprocess.call( cmd, shell=True )
self.prog.setValue(80)
# Import holidays definition file
cmd=["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-c", "\copy tempus_access.holidays FROM "+self.caller.data_dir + "/others/holidays.csv CSV HEADER DELIMITER ';'"]
r = subprocess.call( cmd, shell=True )
# Import modalities definition file
cmd=["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-c", "\copy tempus_access.modalities FROM "+self.caller.data_dir + "/system/modalities.csv CSV HEADER DELIMITER ';'"]
r = subprocess.call( cmd, shell=True )
# Import agregates definition file
cmd=["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-c", "\copy tempus_access.agregates FROM "+self.caller.data_dir + "/system/agregates.csv CSV HEADER DELIMITER ';'"]
r = subprocess.call( cmd, shell=True )
# Import areas definition file
cmd=["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-c", "\copy tempus_access.areas_param FROM "+self.caller.data_dir + "/areas/areas_param.csv CSV HEADER DELIMITER ';'"]
r = subprocess.call( cmd, shell=True )
# Import object types file
cmd=["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-c", "\copy tempus_access.obj_type FROM "+self.caller.data_dir + "/system/obj_type.csv CSV HEADER DELIMITER ';'"]
r = subprocess.call( cmd, shell=True )
# Import indicators file
cmd=["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-c", "\copy tempus_access.indicators FROM "+self.caller.data_dir + "/system/indicators.csv CSV HEADER DELIMITER ';'"]
r = subprocess.call( cmd, shell=True )
self.prog.setValue(90)
s="SELECT lib, code, file_name, id_field, name_field, from_srid FROM tempus_access.areas_param\
ORDER BY 2"
q=QtSql.QSqlQuery(self.caller.db)
q.exec_(unicode(s))
while q.next():
cmd=["ogr2ogr.exe", "-f", "PostgreSQL", "PG:dbname="+self.caller.base+" host="+self.caller.host+" port="+self.caller.port, self.caller.data_dir + "/areas/" + q.value(2), "-overwrite", "-lco", "GEOMETRY_NAME=geom", "-s_srs", "EPSG:"+str(q.value(5)), "-t_srs", "EPSG:4326", "-nln", "tempus_access.area_type"+str(q.value(1)), "-nlt", "PROMOTE_TO_MULTI"]
r = subprocess.call( cmd, shell=True )
# Uniformisation of field names and index creation
t="ALTER TABLE tempus_access.area_type"+str(q.value(1))+" RENAME COLUMN "+str(q.value(4))+" TO lib; \
ALTER TABLE tempus_access.area_type"+str(q.value(1))+" RENAME COLUMN "+str(q.value(3))+" TO char_id; \
CREATE INDEX IF NOT EXISTS area_type"+str(q.value(1))+"_lib_idx ON tempus_access.area_type"+str(q.value(1))+" USING gist (lib gist_trgm_ops); \
CREATE INDEX IF NOT EXISTS area_type"+str(q.value(1))+"_char_id_idx ON tempus_access.area_type"+str(q.value(1))+" USING btree (char_id);"
r=QtSql.QSqlQuery(self.caller.db)
r.exec_(unicode(t))
self.caller.modelAreaType.setQuery(unicode(s), self.caller.db)
self.prog.setValue(100)
box = QMessageBox()
box.setText(u"La base a été initialisée. Vous pouvez maintenant y importer des données. " )
box.exec_()
def sauverOpe(self):
# Fonction à lancer quans les boutons "OK" ou "Dernier - Editer CR" sont cliqués
# Entre en base les infos sélectionnées dans QGIS, et saisies dans le formulaire par l'utilisateur
# Gère les erreurs "pas assez de points sélectionnés pour construire une ligne ou un polygone"
# Gère également la transformation géométrique, via le module convert_geoms
# Récupération de la géométrie finale. On en déduit la table où sera stockée l'information, et on gère les erreurs
# "pas assez de points pour faire la transformation"
geom_cbbx = self.ui.trsf_geom.itemText(
self.ui.trsf_geom.currentIndex())
if geom_cbbx == 'Points':
geom_output = QGis.Point
self.nom_table = 'operation_pts'
elif geom_cbbx == 'Lignes':
geom_output = QGis.Line
self.nom_table = 'operation_lgn'
if self.iface.activeLayer().geometryType() == 0:
if self.iface.activeLayer().selectedFeatureCount() < 2:
mess2pts = QtGui.QMessageBox()
mess2pts.setText(u'Pas assez de points sélectionnés')
mess2pts.setInformativeText(
u'Il faut au moins 2 points pour faire une ligne. Merci d\'en sélectionner plus'
)
mess2pts.setIcon(QtGui.QMessageBox.Warning)
mess2pts.setStandardButtons(QtGui.QMessageBox.Ok)
ret = mess2pts.exec_()
return
elif geom_cbbx == 'Surfaces':
geom_output = QGis.Polygon
self.nom_table = 'operation_poly'
if self.iface.activeLayer().geometryType() == 0:
if self.iface.activeLayer().selectedFeatureCount() < 3:
mess3pts = QtGui.QMessageBox()
mess3pts.setText(u'Pas assez de points sélectionnés')
mess3pts.setInformativeText(
u'Il faut au moins 3 points pour faire un polygone. Merci d\'en sélectionner plus'
)
mess3pts.setIcon(QtGui.QMessageBox.Warning)
mess3pts.setStandardButtons(QtGui.QMessageBox.Ok)
ret = mess3pts.exec_()
return
#copie des entités sélectionnées dans une couche "memory". Evite les problèmes avec les types de couches "non éditables" (comme les GPX).
coucheactive = self.iface.activeLayer()
entselect = [
QgsGeometry(feature.geometry())
for feature in coucheactive.selectedFeatures()
]
if entselect[0].type() == QGis.Line:
typegeom = 'LineString'
elif entselect[0].type() == QGis.Point:
typegeom = 'Point'
elif entselect[0].type() == QGis.Polygon:
typegeom = 'Polygon'
else:
print "ce ne sont pas des points, des lignes ou des polygones"
self.iface.actionCopyFeatures().trigger()
if self.iface.activeLayer().crs().authid() == u'EPSG:4326':
memlayer = QgsVectorLayer(
"{zr_typegeom}?crs=epsg:4326".format(zr_typegeom=typegeom),
"memlayer", "memory")
if self.iface.activeLayer().crs().authid() == u'EPSG:2154':
memlayer = QgsVectorLayer(
"{zr_typegeom}?crs=epsg:2154".format(zr_typegeom=typegeom),
"memlayer", "memory")
QgsMapLayerRegistry.instance().addMapLayer(memlayer, False)
root = QgsProject.instance().layerTreeRoot()
memlayerNode = QgsLayerTreeLayer(memlayer)
root.insertChildNode(0, memlayerNode)
self.iface.setActiveLayer(memlayer)
memlayer.startEditing()
self.iface.actionPasteFeatures().trigger()
memlayer.commitChanges()
#lancement de convert_geoms.py pour transformer les entités sélectionnées dans le type d'entités choisi.
#compréhension de liste : [fonction for x in liste]
geom2 = convert_geometries([
QgsGeometry(feature.geometry())
for feature in memlayer.selectedFeatures()
], geom_output)
#export de la géométrie en WKT et transformation de la projection si les données ne sont pas saisies en Lambert 93
if memlayer.crs().authid() == u'EPSG:2154':
thegeom = 'st_setsrid(st_geometryfromtext (\'{zr_geom2}\'), 2154)'.format(
zr_geom2=geom2.exportToWkt())
elif memlayer.crs().authid() == u'EPSG:4326':
thegeom = 'st_transform(st_setsrid(st_geometryfromtext (\'{zr_geom2}\'),4326), 2154)'.format(
zr_geom2=geom2.exportToWkt())
else:
print u'La projection de la couche active n\'est pas supportée'
#lancement de la fonction qui vérifie si l'opération fait partie d'un chantier de volontaires.
self.recupIdChantvol()
#lancement de la requête SQL qui introduit les données géographiques et du formulaire dans la base de données.
querysauvope = QtSql.QSqlQuery(self.db)
query = u"""insert into bdtravaux.{zr_nomtable} (sortie, plangestion, code_gh, descriptio, chantfini, the_geom, ope_chvol) values ({zr_sortie}, '{zr_plangestion}', '{zr_code_gh}', '{zr_libelle}', '{zr_chantfini}', {zr_the_geom}, '{zr_opechvol}')""".format (zr_nomtable=self.nom_table,\
zr_sortie = self.ui.sortie.itemData(self.ui.sortie.currentIndex()),\
zr_plangestion = self.ui.opprev.currentItem().text().split("/")[-1],\
zr_code_gh = self.ui.opprev.currentItem().text().split("/")[1],\
# zr_ope_typ = self.ui.opreal.currentItem().text().replace("\'","\'\'"),\
zr_libelle = self.ui.descriptio.toPlainText().replace("\'","\'\'"),\
zr_chantfini = str(self.ui.chantfini.isChecked()).lower(),\
zr_the_geom = thegeom,\
#geom2.exportToWkt(),\
#st_transform(st_setsrid(st_geometryfromtext ('{zr_the_geom}'),4326), 2154) si besoin de transformer la projection
zr_opechvol = self.id_opechvol)
ok = querysauvope.exec_(query)
if not ok:
QtGui.QMessageBox.warning(self, 'Alerte',
u'Requête sauver Ope ratée')
# print unicode(query)
self.rempliJoinOpe()
self.iface.setActiveLayer(coucheactive)
QgsMapLayerRegistry.instance().removeMapLayer(memlayer.id())
self.ui.buttonBox.button(QtGui.QDialogButtonBox.Ok).setEnabled(0)
self.ui.compoButton.setEnabled(0)
self.close
def Composer(self, idsortie):
print 'dans composeur, id_sortie='+str(idsortie)
#Affichage des contours du site
#Récupération des données de la table "sortie" pour affichage du site et utilisation dans les étiquettes du composeur
self.recupDonnSortie(idsortie)
reqwheresit="""codesite='"""+str(self.codedusite)+"""'"""
self.uri.setDataSource("sites_cen", "t_sitescen", "the_geom", reqwheresit)
self.contours_site=QgsVectorLayer(self.uri.uri(), "contours_site", "postgres")
# Import de la couche contenant les contours du site
root = QgsProject.instance().layerTreeRoot()
if self.contours_site.featureCount()>0:
QgsMapLayerRegistry.instance().addMapLayer(self.contours_site, False)
root.insertLayer(0, self.contours_site)
# Symbologie du contour de site
# create a new single symbol renderer
symbol = QgsSymbolV2.defaultSymbol(self.contours_site.geometryType())
renderer = QgsSingleSymbolRendererV2(symbol)
# create a new simple marker symbol layer, a white circle with a black border
properties = {'color': 'green', 'color_border': 'red'}
symbol_layer = QgsSimpleFillSymbolLayerV2.create(properties)
symbol_layer.setBrushStyle(0) #0 = Qt.NoBrush. Cf doc de QBrush
# assign the symbol layer to the symbol renderer
renderer.symbols()[0].changeSymbolLayer(0, symbol_layer)
# assign the renderer to the layer
self.contours_site.setRendererV2(renderer)
#Appel à la fonction "affiche", qui importe les couches non vides de gestion réalisée, parmi operation_poly, operation_lgn et operation_pts
self.affiche(idsortie)
# Affichage des couches contenant les contours du site et les opérations de gestion saisies, et masquage des autres
self.rendreVisible=[]
layers=iface.legendInterface().layers()
for layer in layers:
if layer.type()==QgsMapLayer.VectorLayer:
if layer.name()=='gestrealpolys' or layer.name()=='gestreallgn' or layer.name()=='gestrealpts' or layer.name()=='contours_site':
iface.legendInterface().setLayerVisible(layer, True)
else:
if iface.legendInterface().isLayerVisible(layer):
self.rendreVisible.append(layer)
iface.legendInterface().setLayerVisible(layer, False)
#Récupération des données de la table "ch_volont" pour utilisation dans les étiquettes du composeur
self.recupDonnChVolont(idsortie)
#COMPOSEUR : Production d'un composeur
beforeList = self.iface.activeComposers()
self.iface.actionPrintComposer().trigger()
afterList = self.iface.activeComposers()
diffList = []
for item in afterList:
if not item in beforeList:
diffList.append(item)
#Intégration du composeur dans le QgsComposerView et création du QgsComposition
self.composerView = diffList[0]
self.composition = self.composerView.composition()
# Adaptation de la composition : 2 pages A3
self.composition.setPaperSize(420, 297)
self.composition.setNumPages(2)
#TEMPLATE : Récupération du template. Intégration des ses éléments dans la carte.
if sys.platform.startswith('linux'):
file1=QtCore.QFile('/home/vincent/.qgis2/python/plugins/bdtravaux/BDT_20130705_T_CART_ComposerTemplate.qpt')
if sys.platform.startswith('win32'):
file1=QtCore.QFile('C:\qgistemplate\BDT_20130705_T_CART_ComposerTemplate.qpt')
doc=QtXml.QDomDocument()
doc.setContent(file1, False)
elem=doc.firstChildElement()
self.composition.loadFromTemplate(doc, substitutionMap=None, addUndoCommands =False)
#CARTE : Récupération de la carte.
maplist=[]
for item in self.composition.composerMapItems():
maplist.append(item)
self.composerMap=maplist[0]
#Taille définie pour la carte
x, y, w, h, mode, frame, page = 5, 15, 408, 270, QgsComposerItem.UpperLeft, False, 1
self.composerMap.setItemPosition(x, y, w, h, mode, frame, page)
# print self.composerMap.page()
#Crée la bbox autour du site pour la carte en cours (fonction mapItemSetBBox l 293)
#self.contours_sites est défini dans la fonction affiche()
self.margin=10
self.composerMapSetBBox(self.contours_site, self.margin)
#(Dé)zoome sur l'ensemble des deux pages du composeur
#self.composition.mActionZoomFullExtent().trigger()
#ETIQUETTES : Modifier les étiquettes du composeur.
# Trouver les étiquettes dans le composeur
labels = [item for item in self.composition.items()\
if item.type() == QgsComposerItem.ComposerLabel]
#trouver les opérations effectuées lors de la sortie et leurs commentaires dans la table postgresql, selon l'id de la sortie sélectionnée dans le module "opération"
# une boucle permet de récupérer et afficher à la suite dans une seule zone de texte toutes les opérations et leurs descriptions
querycomope = QtSql.QSqlQuery(self.db)
qcomope=u"""select operation_id, (select distinct array_to_string(array(select distinct typoperation from bdtravaux.join_typoperation where id_jointyp=id_oper order by typoperation),'; ')) as typope, descriptio, code_gh, round(st_area(the_geom)::numeric,2) as surface, round(st_length(the_geom)::numeric,2) as longueur, ST_NumGeometries(the_geom) as compte, (select distinct array_to_string(array(select distinct operateurs from bdtravaux.join_operateurs where id_joinop=id_oper order by operateurs),'; ')) as operateurs from (select * from bdtravaux.operation_poly UNION select * from bdtravaux.operation_lgn UNION select * from bdtravaux.operation_pts) tables where sortie={zr_sortie} order by typ_operat""".format \
(zr_sortie = idsortie) #self.ui.sortie.itemData(self.ui.sortie.currentIndex())
#print unicode(qcomope)
ok3 = querycomope.exec_(qcomope)
if not ok3:
QtGui.QMessageBox.warning(self, 'Alerte', u'Requête operations ratée')
querycomope.first()
texteope=""
#Requête : Données à récupérer pour chaque opération de la sortie
for i in xrange(0 , querycomope.size()):
#Récupération des autres valeurs de chaque opération
ope=unicode(querycomope.value(1))
descrope=unicode(querycomope.value(2)).replace('\n','<br/>')
ghope=unicode(querycomope.value(3))
surfope=unicode(querycomope.value(4))
longope=unicode(querycomope.value(5))
countope=unicode(querycomope.value(6))
operatope=unicode(querycomope.value(7))
texteope=unicode(texteope+u'<br/>'+u'<b>'+ope+u'</b>'+u'<h style="margin-left:1cm;">('+ ghope+u')<h style="margin-left:0.5cm;">'+u'/'+u'<h style="margin-left:0.5cm;">'+surfope+u' m²'+'<h style="margin-left:0.5cm;">'+u'/'+u'<h style="margin-left:0.5cm;">'+longope+u' ml<h style="margin-left:0.5cm;">'+u'/'+u'<h style="margin-left:0.5cm;">'+operatope+u'<br/>'+descrope+u'<br/>')
querycomope.next()
# Pour chaque étiquette qui contient le mot-clé (comme "$codesite"), remplacer le texte par le code du site concerné
# La methode find() permet de chercher une chaîne dans une autre.
# Elle renvoie le rang du début de la chaîne cherchée. Si = -1, c'est que la chaîne cherchée n'est pas trouvée
for label in labels:
if label.displayText().find("$codesite")>-1:
plac_codesite=label.displayText().find("$codesite")
texte=unicode(label.displayText())
label.setText(texte[0:plac_codesite]+self.codedusite+texte[plac_codesite+9:])
#pr python equiv à VB6 left, mid and right : https://mail.python.org/pipermail/tutor/2004-November/033445.html
if label.displayText().find("$redac")>-1:
plac_redac=label.displayText().find("$redac")
texte=unicode(label.displayText())
label.setText(texte[0:plac_redac]+self.salaries+texte[plac_redac+6:])
if label.displayText().find("$date")>-1:
plac_date=label.displayText().find("$date")
texte=unicode(label.displayText())
label.setText(texte[0:plac_date]+self.datesortie+texte[plac_date+5:])
if label.displayText().find("$datefin")>-1:
plac_date=label.displayText().find("$datefin")
texte=unicode(label.displayText())
label.setText(texte[0:plac_date]+self.datefin+texte[plac_date+8:])
if label.displayText().find("$jourschan")>-1:
plac_date=label.displayText().find("$jourschan")
texte=unicode(label.displayText())
label.setText(texte[0:plac_date]+self.jourschan+texte[plac_date+10:])
if label.displayText().find("$commsortie")>-1:
plac_commsortie=label.displayText().find("$commsortie")
texte=unicode(label.displayText())
label.setText(texte[0:plac_commsortie]+self.sortcom+texte[plac_commsortie+11:])
if label.displayText().find("$nomsite")>-1:
plac_nomsite=label.displayText().find("$nomsite")
texte=unicode(label.displayText())
label.setText(texte[0:plac_nomsite]+self.nomdusite+texte[plac_nomsite+8:])
if label.displayText().find("$commope")>-1:
label.setText(texteope)
if label.displayText().find("$objet")>-1:
plac_objet=label.displayText().find("$objet")
texte=unicode(label.displayText())
label.setText(texte[0:plac_objet]+self.objvisite+texte[plac_objet+6:])
if label.displayText().find("$objvi_autre")>-1:
plac_objautre=label.displayText().find("$objvi_autre")
texte=unicode(label.displayText())
if self.objautre:
label.setText(texte[0:plac_objautre]+self.objautre+texte[plac_objautre+12:])
else:
label.setText(texte[0:plac_objautre]+''+texte[plac_objautre+12:])
if label.displayText().find("$natfaune")>-1:
label.setText(self.natfaune)
if label.displayText().find("$natflore")>-1:
label.setText(self.natflore)
if self.cv_partenaire is not None:
if label.displayText().find("$nbjours")>-1:
plac_nbjours=label.displayText().find("$nbjours")
texte=unicode(label.displayText())
label.setText(texte[0:plac_nbjours]+str(self.cv_nb_jours)+texte[plac_nbjours+8:])
if label.displayText().find("$nbheurch")>-1:
plac_nbheurch=label.displayText().find("$nbheurch")
texte=unicode(label.displayText())
label.setText(texte[0:plac_nbheurch]+str(self.cv_nb_heur_ch)+texte[plac_nbheurch+9:])
if label.displayText().find("$nbheurdec")>-1:
plac_nbheurdec=label.displayText().find("$nbheurdec")
texte=unicode(label.displayText())
label.setText(texte[0:plac_nbheurdec]+str(self.cv_nb_heur_de)+texte[plac_nbheurdec+10:])
if label.displayText().find("$partenair")>-1:
plac_partenair=label.displayText().find("$partenair")
texte=unicode(label.displayText())
label.setText(texte[0:plac_partenair]+self.cv_partenaire+texte[plac_partenair+10:])
# print unicode(texte)
if label.displayText().find("$heberg")>-1:
plac_heberg=label.displayText().find("$heberg")
texte=unicode(label.displayText())
label.setText(texte[0:plac_heberg]+self.cv_heberg+texte[plac_heberg+7:])
if label.displayText().find("$jr1enc_am")>-1:
label.setText(str(self.cv_j1_enc_am))
if label.displayText().find("$jr1enc_pm")>-1:
label.setText(str(self.cv_j1_enc_pm))
if label.displayText().find("$jr1tot_am")>-1:
label.setText(str(self.cv_j1_tot_am))
if label.displayText().find("$jr1tot_pm")>-1:
label.setText(str(self.cv_j1_tot_pm))
if label.displayText().find("$jr1cen_am")>-1:
label.setText(str(self.cv_j1adcen_am))
if label.displayText().find("$jr1cen_pm")>-1:
label.setText(str(self.cv_j1adcen_pm))
if label.displayText().find("$jr1blo_am")>-1:
label.setText(str(self.cv_j1_blon_am))
if label.displayText().find("$jr1blo_pm")>-1:
label.setText(str(self.cv_j1_blon_pm))
if label.displayText().find("$jr2enc_am")>-1:
label.setText(str(self.cv_j2_enc_am))
if label.displayText().find("$jr2enc_pm")>-1:
label.setText(str(self.cv_j2_enc_pm))
if label.displayText().find("$jr2tot_am")>-1:
label.setText(str(self.cv_j2_tot_am))
if label.displayText().find("$jr2tot_pm")>-1:
label.setText(str(self.cv_j2_tot_pm))
if label.displayText().find("$jr2cen_am")>-1:
label.setText(str(self.cv_j2adcen_am))
if label.displayText().find("$jr2cen_pm")>-1:
label.setText(str(self.cv_j2adcen_pm))
if label.displayText().find("$jr2blo_am")>-1:
label.setText(str(self.cv_j2_blon_am))
if label.displayText().find("$jr2blo_pm")>-1:
label.setText(str(self.cv_j2_blon_pm))
if label.displayText().find("$sem_enc")>-1:
label.setText(str(self.cv_sem_enc))
if label.displayText().find("$sem_ben")>-1:
label.setText(str(self.cv_sem_ben))
else:
if re.match("^\$jr",label.displayText()) or re.search("\$sem",label.displayText()) or re.search("\$nb",label.displayText()):
label.setText('0')
if re.search("\$partenair",label.displayText()) or re.search("\$heberg",label.displayText()):
label.setText(' ')
# le module re permet de chercher des chaînes dans du texte avec des expression régulières
# match => le début du texte doit correspondre
#search : on cherche la chaîne quelque-part dans le texte.
#le \ permet d'échapper le $ (qui correspond normalement à une fin de ligne dans une regexp).
#LEGENDE : mettre à jour la légende.
for i in self.composition.items():
if isinstance(i,QgsComposerLegend):
print "mise a jour legende"
legend = i
legend.setAutoUpdateModel(True)
for j in xrange(legend.modelV2().rowCount()):
modelindex=legend.modelV2().index(j, 0)
layertreenode=legend.modelV2().index2node(modelindex)
# print modelindex.data()
# print modelindex.__class__.__name__
# print layertreenode.__class__.__name__
if isinstance(layertreenode, QgsLayerTreeGroup):
layertreenode.setVisible(False)
# print modelindex.data()
# layertreenode.setName("")
# print modelindex.data()
else:
print 'Layer'
legend.setAutoUpdateModel(False)
legend.setLegendFilterByMapEnabled(True)
def nestedTransaction(self):
QtSql.QSqlQuery(self.db).exec_('SAVEPOINT LEVEL_%d' %
(self._openTransactionsCount + 1))
if self.db.lastError().isValid():
raise CDatabaseException(CDatabase.errTransactionError,
self.db.lastError())
# author: Jan Bodnar
# website: zetcode.com
# last edited: October 2009
from PyQt4 import QtSql, QtCore
app = QtCore.QCoreApplication([])
db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("friends.db")
if not db.open():
print "cannot establish a database connection"
else:
query = QtSql.QSqlQuery("SELECT name, age FROM friends")
query.first()
name = query.value(0).toString()
age = query.value(1).toString()
print name, age
query.next()
name = query.value(0).toString()
age = query.value(1).toString()
print name, age
query.last()
name = query.value(0).toString()
age = query.value(1).toString()
print name, age
def query(cn):
return QtSql.QSqlQuery(cn)
def generator(csomazon, genfolder, keszito):
# postgresql connection
try:
# csomag adatok lekérdezése
prow = []
queryGenerate = QtSql.QSqlQuery()
siker = queryGenerate.exec_(
"""SELECT csomag_azon,csomag_nev,mintater,projminter,felmero,hatarido,sorok,oszlopok,letrehozas,
formverzio,oscsomag FROM csomag WHERE csomag_azon = %s""" %
(csomazon))
if siker:
if queryGenerate.size() == 1:
queryGenerate.next()
for i in range(11):
prow.append(queryGenerate.value(i))
else:
QtGui.QMessageBox.warning(None, u"File Generálás",
u"Hiányzó csomag rekord.")
return
else:
if queryGenerate.lastError().type() != 0:
QtGui.QMessageBox.warning(
None, u"Hiba:",
u"Hiba típus: %s" % str(queryGenerate.lastError().text()))
return
print prow
forward = True
pontx = 0
sql_list = []
queryCreates = QtSql.QSqlQuery()
crQuery = """SELECT table_sql FROM csomag_sql WHERE %s= True""" % (
prow[9].strip())
siker = queryCreates.exec_(crQuery)
if siker:
while queryCreates.next():
sql_list.append(queryCreates.value(0))
else:
if queryCreates.lastError().type() != 0:
QtGui.QMessageBox.warning(
None, u"Hiba:",
u"Hiba típus: %s" % str(queryCreates.lastError().text()))
return
if not len(sql_list): return
try:
# felhasználó könyvtárának létrehozása a megadott elérési út alatt - ha még nincs
if not os.path.exists(genfolder + '/' + prow[4]):
print 'Create package folder:', os.path.exists(genfolder +
'/' + prow[4])
os.makedirs(genfolder + '/' + prow[4])
# az SQLite adatbázis létrehozása, és kapcsolódás hozzá
sdb = sqlite3.connect(genfolder + '/' + prow[4] + '/' + prow[1] +
'.db')
# sqlite táblák létrehozásának ciklusa
for sql_rec in sql_list:
scur = sdb.cursor()
scur.execute(sql_rec)
sdb.commit()
# csomag rekord beszúrása
prowselect = (prow[0], prow[1], prow[3], prow[4],
prow[5].toString('yyyy-MM-dd hh:mm:ss'), prow[6],
prow[7], prow[9].strip())
print prowselect
csrec = sdb.cursor()
csrec.execute(
'INSERT INTO csomag(csomag_id,csom_nev,mintater,felmero,hatarido,sor,oszlop,formverzio) VALUES(?,?,?,?,?,?,?,?)',
prowselect)
sdb.commit()
# gpx változó létrehozása
gpx = gpxpy.gpx.GPX()
# ppont rekordok lekérdezése
pponts = []
queryPRec = QtSql.QSqlQuery()
siker = queryPRec.exec_(
"""SELECT ppont_azon,csomag_azon,epont_azon,bpont_azon,sorszam,sor,oszlop,wgs84_lat,wgs84_lon,statusz
FROM ppont WHERE csomag_azon=%s""" % (prow[0]))
if siker:
while queryPRec.next():
prec = []
for i in range(10):
prec.append(queryPRec.value(i))
pponts.append(prec)
pontx = len(pponts)
for ppont in pponts:
# insert ppont records
print ppont
scur = sdb.cursor()
if ppont[9] == 0:
# ha uj csomagról van szó
scur.execute(
'''INSERT INTO ppont(ppont_id,csomag_id,epont_id,bpont_id,sorszam,sor,oszlop,
wgs84_lat,wgs84_lon,statusz) VALUES(?,?,?,?,?,?,?,?,?,1)''',
(ppont[0], ppont[1], ppont[2], ppont[3], ppont[4],
ppont[5], ppont[6], ppont[7], ppont[8]))
else:
# ha hibajavító csomagról van szó
scur.execute(
'''INSERT INTO ppont(ppont_id,csomag_id,epont_id,bpont_id,sorszam,sor,oszlop,
wgs84_lat,wgs84_lon,statusz) VALUES(?,?,?,?,?,?,?,?,?,?)''',
ppont)
sdb.commit()
# GPX waypoint beszúrások a gpx változóba
gpx_wpt = gpxpy.gpx.GPXWaypoint(ppont[7],
ppont[8],
name=str(ppont[4]),
symbol='Navaid, Orange')
gpx.waypoints.append(gpx_wpt)
# gpx fájl létrehozása és lezárása
gpxfilename = genfolder + '/' + prow[4] + '/' + prow[1] + '.gpx'
gpxfile = open(gpxfilename, 'a')
gpxfile.write(gpx.to_xml())
gpxfile.close()
# a Garmin gpi fájl létrehozása GPSBabel program segítségével
gpifilename = genfolder + '/' + prow[4] + '/' + prow[1] + '.gpi'
pluginfolder = os.path.dirname(os.path.abspath(__file__))
babelparancs = ''
if platform == "linux" or platform == "linux2" or platform == "darwin":
# linux and OS X
babelparancs = 'gpsbabel -w -i gpx -f ' + gpxfilename + ' -o garmin_gpi,category=' + prow[1][:6] \
+ prow[1][8:] + ',bitmap="' + pluginfolder + '/pont.bmp",unique=1 -F ' + gpifilename
elif platform == "win32":
# Windows...
babelparancs = 'gpsbabel -w -i gpx -f ' + gpxfilename + ' -o garmin_gpi,category=' + prow[1][:6] \
+ prow[1][8:] + ',bitmap="' + pluginfolder.replace('\\','/') + '/pont.bmp",unique=1 -F ' + gpifilename
# + prow[1][8:] + ",bitmap=d:/dev/pycharm_projects/scriptek/pont.bmp,unique=1 -F " + gpifilename
else:
QtGui.QMessageBox.warning(
None, u"Hiba:",
u"Operációs rendszer függési hiba: %s" % platform)
if babelparancs:
print babelparancs
babelproc = os.system(babelparancs)
print babelproc
sleep(2)
except sqlite3.Error as e:
QtGui.QMessageBox.warning(None, u"Hiba:",
u"Adatbázis hiba: %s" % e)
forward = False
except Exception as e:
QtGui.QMessageBox.warning(None, u"Hiba:",
u"Adatbázis lekérdezés hiba: %s" % e)
forward = False
finally:
if sdb:
sdb.close()
if forward:
# ha az eddigi műveletek sikeresen lezajlottak
fname = genfolder + '/' + prow[4] + '/' + prow[1] + '.db'
if os.path.isfile(fname):
# adatbázis ZIP zömörítés
zip_ok = csomag_zip(prow[1], prow[4], genfolder)
md5_returned = None
if zip_ok:
# MD5 value generálás
md5_returned = csomag_md5(genfolder + '/' + prow[4] + '/' +
prow[1] + '.zip')
if md5_returned is not None:
tom_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
# csomag XML element létrehozása, letárolása
# csomag_azon,csomag_nev,mintater,projminter,felmero,hatarido,sorok,oszlopok,letrehozas,formverzio,oscsomag
# (cs_felm, cs_azon, cs_nev, cs_pontsz, cs_gen, cs_hat, cs_tom, cs_MD5)
logazon, logrend = csomag_XML_elem(
prow[4], prow[0], prow[1], pontx,
prow[8].toString('yyyy-MM-dd hh:mm:ss'),
prow[5].toString('yyyy-MM-dd hh:mm:ss'), tom_time,
md5_returned, genfolder)
if logazon:
# ha első alkalommal törénik a generálás
if logrend == 1:
# a csomag rekord állapotának megváltoztatása a Postgresql-ben
queryCsomStatus = QtSql.QSqlQuery()
siker = queryCsomStatus.exec_(
"""UPDATE csomag SET allapot = True WHERE csomag_azon = %s"""
% (prow[0]))
if siker:
print 'Package state = True'
else:
if queryCsomStatus.lastError().type() != 0:
QtGui.QMessageBox.warning(
None, u"Hiba:", u"Hiba típus: %s" %
str(queryCsomStatus.lastError().text())
)
return
# a ppont rekordok státuszának megváltoztatása a Postgresql-ben
queryPontStatus = QtSql.QSqlQuery()
siker = queryPontStatus.exec_(
"""UPDATE ppont SET statusz = 1 WHERE csomag_azon = %s"""
% (prow[0]))
if siker:
print 'Pponts state: 1'
else:
if queryPontStatus.lastError().type() != 0:
QtGui.QMessageBox.warning(
None, u"Hiba:", u"Hiba típus: %s" %
str(queryPontStatus.lastError().text())
)
return
# a fájlgenerálás bejegyzése a csomag_gen táblába
queryGenLog = QtSql.QSqlQuery()
siker = queryGenLog.exec_(
'''INSERT INTO csomag_gen(package_id,csomag_nev,file_generalas,gen_mod,gen_rend,kezdemenyezo)
VALUES(%s,'%s','%s','manual',%s,'%s') RETURNING gen_id ;'''
% (logazon, prow[1], tom_time, logrend, keszito))
if siker:
if queryGenLog.size() == 1:
queryGenLog.next()
print 'gen_id:', queryGenLog.value(
0), 'package_id:', logazon
else:
QtGui.QMessageBox.warning(
None, u"Csomag-gen",
u"Csomag generálás rekord rögzítési hiba.")
return
else:
if queryGenLog.lastError().type() != 0:
QtGui.QMessageBox.warning(
None, u"Hiba:", u"Hiba típus: %s" %
str(queryGenLog.lastError().text()))
return
else:
# a fájlok törlése építhető be ide, ha sikertelen volt a csomag_log rekord kezelése
pass
else:
QtGui.QMessageBox.warning(None, u"Hiba:",
u"MD5 generálás hiba")
return
QtGui.QMessageBox.warning(
None, u"Csomag generálás",
u"Létrehozásra kerültek a csomag fájljai")
return
QtGui.QMessageBox.warning(None, u"Csomag generálás hiba",
u"A fájlok generálása elakadt.")
return
except Exception as e:
QtGui.QMessageBox.warning(None, u"Hiba:", u"Generálás hiba: %s" % e)
def loadLWR(self):
self.LWRTreeView.clear()
if self.selectionGroup.checkedButton() == self.optionFamilyTree:
searchingBySpecies = False
else:
searchingBySpecies = True
self.queryFamilies = QtSql.QSqlQuery(
'Select distinct family from LWR order by family')
self.queryFamilies.exec_()
while self.queryFamilies.next():
if not searchingBySpecies:
familyText = self.queryFamilies.value(0).toString()
whereClauseText = ("where family='%s'" % familyText)
newFamilyItem = QtGui.QTreeWidgetItem(self.LWRTreeView)
newFamilyItem.setText(0, familyText)
else:
whereClauseText = ''
self.querySpecies = QtSql.QSqlQuery(("""Select distinct
species from LWR %s
order by species""" % whereClauseText))
self.querySpecies.exec_()
while self.querySpecies.next():
speciesText = self.querySpecies.value(0).toString()
if not searchingBySpecies:
treeRoot = newFamilyItem
whereClauseText2 = whereClauseText = (
"""where family='%s' and
species='%s' and
LWR.dimref=DIMS.dimsid and
LWR.biblioref=BIBLIO.biblioid""" % (familyText, speciesText))
else:
treeRoot = self.LWRTreeView
whereClauseText2 = whereClauseText = (
"""where species='%s' and
LWR.dimref=DIMS.dimsid and
LWR.biblioref=BIBLIO.biblioid""" % speciesText)
newSpeciesItem = QtGui.QTreeWidgetItem(treeRoot)
newSpeciesItem.setText(0, speciesText)
self.queryRegions = QtSql.QSqlQuery(("""Select distinct
species,region,dimfull,minlength,maxlength,n,a,b,r2,authors,title
from LWR,DIMS,BIBLIO
%s order by species""" % (whereClauseText2)))
self.queryRegions.exec_()
while self.queryRegions.next():
newRegionsItem = QtGui.QTreeWidgetItem(newSpeciesItem)
newRegionsItem.setText(
0,
self.queryRegions.value(1).toString())
newRegionsItem.setText(
1,
self.queryRegions.value(2).toString())
newRegionsItem.setText(
3,
self.queryRegions.value(3).toString())
newRegionsItem.setText(
4,
self.queryRegions.value(4).toString())
newRegionsItem.setText(
5,
self.queryRegions.value(5).toString())
newRegionsItem.setText(
6,
self.queryRegions.value(6).toString())
newRegionsItem.setText(
7,
self.queryRegions.value(7).toString())
newRegionsItem.setText(
8,
self.queryRegions.value(8).toString())
newRegionsItem.setText(
9,
self.queryRegions.value(9).toString())
newRegionsItem.setText(
10,
self.queryRegions.value(10).toString())
if searchingBySpecies:
break
def log_unmarcked_assignment(self):
sqlquery = QtSql.QSqlQuery("""
SELECT
assignment_submissions.timemodified,
assignment_submissions.timemarked,
assignment.name,
course.fullname,
student.lastname,
teacher.lastname
FROM
assignment_submissions
JOIN assignment ON assignment_submissions.assignment = assignment.id
JOIN course ON course.id = assignment.course
JOIN user as student ON student.id = assignment_submissions.userid
LEFT OUTER JOIN user as teacher ON teacher.id = assignment_submissions.teacher
WHERE
(assignment_submissions.timemarked =0 OR
(assignment_submissions.timemarked !=0 AND
assignment_submissions.timemodified > assignment_submissions.timemarked)
)
ORDER BY assignment_submissions.timemodified DESC;
""")
self.unmarcked_assignment_model = CustomSqlModel()
self.unmarcked_assignment_model.setQuery(sqlquery)
proxyModel = QtGui.QSortFilterProxyModel()
proxyModel.setSourceModel(self.unmarcked_assignment_model)
self.ui.tableView_2.setModel(proxyModel)
self.unmarcked_assignment_model.setHeaderData(0, 1, u"Прислано")
self.unmarcked_assignment_model.setHeaderData(1, 1, u"Перевірено")
self.unmarcked_assignment_model.setHeaderData(2, 1, u"Назва")
self.unmarcked_assignment_model.setHeaderData(3, 1, u"Дисципліна")
self.unmarcked_assignment_model.setHeaderData(4, 1, u"Студент")
self.unmarcked_assignment_model.setHeaderData(5, 1, u"Викладач")
self.ui.tableView_2.setColumnWidth(0, 200)
self.ui.tableView_2.setColumnWidth(1, 200)
self.ui.tableView_2.setColumnWidth(3, 200)
while sqlquery.next():
utc_send, ok = sqlquery.value(0).toInt()
utc_marked, ok = sqlquery.value(1).toInt()
dataSend = QtCore.QDateTime()
dataSend.setTime_t(utc_send)
mess = QtCore.QString(u"<u>[%1]</u>: ")
if utc_marked == 0:
mess.append(u"Надійшла ")
elif utc_marked < utc_send:
mess.append(u"Нова версія ")
mess.append(u"<a href='ya.ru'>%2</a> з %3 студента <b>%4</b>. ")
mess = QtCore.QString(mess).arg(
dataSend.toString("dd.MM.yyyy hh:mm")).arg(
sqlquery.value(2).toString()).arg(
sqlquery.value(3).toString()).arg(
sqlquery.value(4).toString())
dateMustMark = dataSend.addDays(daystomark)
if dateMustMark < QtCore.QDateTime.currentDateTime():
mess.append(
u"<span style='color: red;'>Робота повинна була бути перевірена %1.</span> "
)
mess = QtCore.QString(mess).arg(
dateMustMark.toString("dd.MM.yyyy hh:mm"))
else:
mess.append(u"<u>Кінцева дата перевірки %1.</u> ")
mess = QtCore.QString(mess).arg(
dateMustMark.toString("dd.MM.yyyy hh:mm"))
self.log_dict[utc_send] = mess
#!/usr/bin/python
# ZetCode Advanced PyQt4 tutorial
#
# In this example, we work with an SQL query error
#
# author: Jan Bodnar
# website: zetcode.com
# last edited: October 2009
from PyQt4 import QtSql, QtCore
app = QtCore.QCoreApplication([])
db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("friends.db")
if not db.open():
print "cannot establish a database connection"
else:
query = QtSql.QSqlQuery("SELECT friend, age FROM Friends")
while query.next():
friend = query.value(0).toString()
age = query.value(1).toString()
print friend, age
error = query.lastError()
print error.text()
def insert_recentconn(self, chost, cport, cpass):
"""Insert a failure into the database"""
QtSql.QSqlQuery('''INSERT INTO recentconn VALUES("%s", "%d", "%s")''' % (chost, cport, cpass))
log.info("Recent connection added: %s:%d" % (chost, cport))
def clear_recentconn_table(self):
"""Drops the recentconn (Controller) table from the database"""
QtSql.QSqlQuery('''DROP TABLE IF EXISTS recentconn''')
def run(self):
self.buildGraph()
for d in self.days:
if (self.time_point != "NULL"): # Simple time constraint
if (self.path_tree == False):
s = "SELECT tempus_access.shortest_path2((" + str(
self.road_node_from
) + "), (" + str(self.road_node_to) + "), ARRAY" + str(
self.tran_modes) + ", '" + d + " " + self.time_point[
1:len(self.time_point) -
1] + "'::timestamp, " + str(
self.constraint_date_after) + ");\n"
print s
self.file.write(s)
else:
for node in self.road_nodes: # For each source node
s = "SELECT tempus_access.shortest_paths_tree(("+str(node)+"), ARRAY"+str(self.tran_modes)+", "+str(self.max_cost)+", "+str(self.walking_speed)+", "+str(self.cycling_speed)+", '"+d \
+ " " +self.time_point[1:len(self.time_point)-1]+"'::timestamp, "+str(self.constraint_date_after)+");\n"
print s
self.file.write(s)
else: # Time period constraint
if (
self.all_services == True
): # All possible services of the period - only fo simple paths
current_timestamp = ""
bound_timestamp = ""
bound_time = ""
if (self.constraint_date_after == True):
current_timestamp = d + " " + self.time_start[
1:len(self.time_start) - 1]
bound_timestamp = d + " " + self.time_end[
1:len(self.time_end) - 1]
bound_time = self.time_end
elif (self.constraint_date_after == False):
current_timestamp = d + " " + self.time_end[
1:len(self.time_end) - 1]
bound_timestamp = d + " " + self.time_start[
1:len(self.time_start) - 1]
bound_time = self.time_start
while (current_timestamp != bound_timestamp):
s = "SELECT tempus_access.shortest_path2((" + str(
self.road_node_from
) + "), (" + str(self.road_node_to) + "), ARRAY" + str(
self.tran_modes
) + ", '" + current_timestamp + "'::timestamp, " + str(
self.constraint_date_after) + ");\n"
print s
self.file.write(s)
s1 = "SELECT next_pt_timestamp::character varying FROM tempus_access.next_pt_timestamp(" + bound_time + "::time, '" + str(
d) + "'::date, " + str(
self.constraint_date_after) + ")"
print s1
q1 = QtSql.QSqlQuery(self.db)
q1.exec_(unicode(s1))
while q1.next():
if (current_timestamp == str(q1.value(0))):
current_timestamp = bound_timestamp
else:
current_timestamp = str(q1.value(0))
elif (self.time_interval !=
"NULL"): # Search at a regular time interval
current_timestamp = ""
bound_timestamp = ""
bound_time = ""
if (self.constraint_date_after == True):
current_timestamp = d + " " + self.time_start[
1:len(self.time_start) - 1]
bound_timestamp = d + " " + self.time_end[
1:len(self.time_end) - 1]
bound_time = self.time_end
elif (self.constraint_date_after == False):
current_timestamp = d + " " + self.time_end[
1:len(self.time_end) - 1]
bound_timestamp = d + " " + self.time_start[
1:len(self.time_start) - 1]
bound_time = self.time_start
while (current_timestamp != bound_timestamp):
if (self.path_tree == False):
s = "SELECT tempus_access.shortest_path((" + str(
self.road_node_from
) + "), (" + str(
self.road_node_to
) + "), ARRAY" + str(
self.tran_modes
) + ", '" + current_timestamp + "'::timestamp, " + str(
self.constraint_date_after) + ");\n"
print s
self.file.write(s)
elif (self.path_tree == True):
for node in self.road_nodes: # For each source/target node
s = "SELECT tempus_access.shortest_paths_tree(" + str(
node
) + ", ARRAY" + str(
self.tran_modes
) + ", " + str(self.max_cost) + ", " + str(
self.walking_speed
) + ", " + str(
self.cycling_speed
) + ", '" + current_timestamp + "'::timestamp, " + str(
self.constraint_date_after) + ");\n"
print s
self.file.write(s)
s1 = "SELECT next_timestamp::character varying FROM tempus_access.next_timestamp('" + current_timestamp + "'::timestamp, " + str(
self.time_interval
) + ", '" + bound_timestamp + "'::timestamp, " + str(
self.constraint_date_after) + ")"
print s1
q1 = QtSql.QSqlQuery(self.db)
q1.exec_(unicode(s1))
while q1.next():
current_timestamp = str(q1.value(0))
print(self.query_str)
self.file.write(self.query_str)
self.file.close()
cmd = [
PSQL, "-h",
self.db.hostName(), "-p",
str(self.db.port()), "-d",
self.db.databaseName(), "-U",
self.db.userName(), "-f", self.plugin_dir + "\\scripts\\temp.sql"
]
done = execute_external_cmd(cmd)
res = False
if (done == 0):
res = True
return res
def csomag_XML_elem(cs_felm, cs_azon, cs_nev, cs_pontsz, cs_gen, cs_hat,
cs_tom, cs_MD5, genfolder):
""" XML minidom-document létrehozása a csomag adataival és a csomag_log rekord kezelése"""
doc = Document()
# csomag elem létrehozása
csomag = doc.createElement('csomag')
csomag.setAttribute("azonosito", '%s' % str(cs_azon))
csomag.setAttribute("nev", '%s' % cs_nev)
csomag.setAttribute("pontszam", '%s' % str(cs_pontsz))
doc.appendChild(csomag)
# a csomag gyermek elemeinek létrehozása
generalas = doc.createElement('generalas')
generalas_tartalom = doc.createTextNode('%s' % cs_gen)
generalas.appendChild(generalas_tartalom)
csomag.appendChild(generalas)
hatarido = doc.createElement('hatarido')
hatarido_tartalom = doc.createTextNode('%s' % cs_hat)
hatarido.appendChild(hatarido_tartalom)
csomag.appendChild(hatarido)
muvelet = doc.createElement('muvelet')
csomag.appendChild(muvelet)
hash = doc.createElement('hash')
csomag.appendChild(hash)
# a művelet gyermek elemeinek létrehozása
tomorites = doc.createElement('tomorites')
tomorites_tartalom = doc.createTextNode('%s' % cs_tom)
tomorites.appendChild(tomorites_tartalom)
muvelet.appendChild(tomorites)
# a művelet gyermek elemeinek létrehozása
md5szerver = doc.createElement('MD5_szerver')
md5szerver_tartalom = doc.createTextNode('%s' % cs_MD5)
md5szerver.appendChild(md5szerver_tartalom)
hash.appendChild(md5szerver)
fname = genfolder + '/' + cs_felm + '/' + cs_nev + '.xml'
# XML kiírása
doc.writexml(open(fname, 'w'), indent=" ", addindent=" ", newl='\n')
doc.unlink()
if os.path.isfile(fname):
queryLogId = QtSql.QSqlQuery()
siker = queryLogId.exec_(
'''SELECT package_id FROM csomag_log WHERE csomag_azon = %s;''' %
(cs_azon))
if siker:
if queryLogId.size() == 1:
queryLogId.next()
return queryLogId.value(0), 2
elif queryLogId.size() > 1:
QtGui.QMessageBox.warning(
None, u"Csomag-log",
u"Csomag-log rekord lekérdezési hiba.")
return 0, 0
else:
if queryLogId.lastError().type() != 0:
QtGui.QMessageBox.warning(
None, u"Hiba:",
u"Hiba típus: %s" % str(queryLogId.lastError().text()))
return 0, 0
queryLog = QtSql.QSqlQuery()
siker = queryLog.exec_(
'''INSERT INTO csomag_log(felmero,csomag_azon,csomag_nev,cs_pontszam,cs_generalas,cs_hatarido,m_tomorites,hash_md5_szerver)
VALUES('%s',%s,'%s',%s,'%s','%s','%s','%s') RETURNING package_id ;'''
% (cs_felm, cs_azon, cs_nev, cs_pontsz, cs_gen, cs_hat, cs_tom,
cs_MD5))
if siker:
if queryLog.size() == 1:
queryLog.next()
return queryLog.value(0), 1
else:
QtGui.QMessageBox.warning(
None, u"Csomag-log", u"Csomag-log rekord rögzítési hiba.")
return 0, 0
else:
if queryLog.lastError().type() != 0:
QtGui.QMessageBox.warning(
None, u"Hiba:",
u"Hiba típus: %s" % str(queryLog.lastError().text()))
return 0, 0
else:
print "No XML - No PackMan!"
return 0, 0
def setValue(self, id, field, value):
query = QtSql.QSqlQuery(self.db)
query.prepare('update users set ' + field + ' = ? where id = ?')
query.addBindValue(value)
query.addBindValue(id)
return query.exec_()
def _slotPushButtonDerivedRepGenerateClicked(self):
self.caller.isosurfaces = True
self.caller.buildQuery()
r = QtSql.QSqlQuery(self.caller.db)
done = r.exec_(self.caller.query)
self.caller.resultAvailable(done)
def nestedCommit(self):
QtSql.QSqlQuery(self.db).exec_('RELEASE SAVEPOINT LEVEL_%d' %
self._openTransactionsCount)
if self.db.lastError().isValid():
raise CDatabaseException(CDatabase.errNestedCommitTransactionError,
self.db.lastError())
def _slotpushButtonReqDisplayClicked(self):
size_indic_name = self.caller.modelSizeIndic.record(
self.ui.comboBoxSizeIndic.currentIndex()).value("col_name")
color_indic_name = self.caller.modelColorIndic.record(
self.ui.comboBoxColorIndic.currentIndex()).value("col_name")
self.caller.dlg.ui.labelElapsedTime.setText("")
if (size_indic_name != ""):
s = "SELECT tempus_access.map_indicator('" + self.ui.comboBoxReq.currentText(
) + "', '" + size_indic_name + "', 'size', " + str(
self.ui.spinBoxSizeIndicMinValue.value()) + ", " + str(
self.ui.spinBoxSizeIndicMaxValue.value()) + ", " + str(
self.ui.doubleSpinBoxSize.value()) + ")"
q = QtSql.QSqlQuery(self.db)
q.exec_(unicode(s))
elif (self.obj_def_name != "paths_tree") and (self.obj_def_name !=
"comb_paths_trees"):
s = "UPDATE indic." + self.ui.comboBoxReq.currentText(
) + " SET symbol_size = 1"
q = QtSql.QSqlQuery(self.db)
q.exec_(unicode(s))
if (color_indic_name != ""):
s = "SELECT tempus_access.map_indicator('" + self.ui.comboBoxReq.currentText(
) + "', '" + color_indic_name + "', 'color', " + str(
self.ui.spinBoxColorIndicMinValue.value()) + ", " + str(
self.ui.spinBoxColorIndicMaxValue.value()) + ", 1)"
q = QtSql.QSqlQuery(self.db)
q.exec_(unicode(s))
elif (self.obj_def_name != "paths_tree") and (self.obj_def_name !=
"comb_paths_trees"):
s = "UPDATE indic." + self.ui.comboBoxReq.currentText(
) + " SET symbol_color = 1"
q = QtSql.QSqlQuery(self.db)
q.exec_(unicode(s))
for layer in self.caller.node_indicators.findLayers():
self.iface.legendInterface().setLayerVisible(layer.layer(), False)
# Stop areas or stops
if (((self.obj_def_name == "stops") or
(self.obj_def_name == "stop_areas"))
and self.ui.radioButtonScreenUnit.isChecked()):
self.indicDisplay(
self.ui.comboBoxReq.currentText(),
self.ui.comboBoxReq.currentText(), self.plugin_dir +
'/styles/pt_stop_by_mode_prop_size_prop_color_screen_unit.qml',
'gid', "geom", '')
elif (((self.obj_def_name == "stops") or
(self.obj_def_name == "stop_areas"))
and self.ui.radioButtonMapUnit.isChecked()):
self.indicDisplay(
self.ui.comboBoxReq.currentText(),
self.ui.comboBoxReq.currentText(), self.plugin_dir +
'/styles/pt_stop_by_mode_prop_size_prop_color_map_unit.qml',
'gid', "geom", '')
# Sections
elif ((self.obj_def_name == "sections")
and self.ui.radioButtonScreenUnit.isChecked()):
self.indicDisplay(
self.ui.comboBoxReq.currentText(),
self.ui.comboBoxReq.currentText(), self.plugin_dir +
'/styles/pt_section_by_mode_prop_size_prop_color_screen_unit.qml',
'gid', "geom", '')
elif ((self.obj_def_name == "sections")
and self.ui.radioButtonMapUnit.isChecked()):
self.indicDisplay(
self.ui.comboBoxReq.currentText(),
self.ui.comboBoxReq.currentText(), self.plugin_dir +
'/styles/pt_section_by_mode_prop_size_prop_color_map_unit.qml',
'gid', "geom", '')
# Trips
elif ((self.obj_def_name == "trips")
and self.ui.radioButtonScreenUnit.isChecked()):
self.indicDisplay(
self.ui.comboBoxReq.currentText(),
self.ui.comboBoxReq.currentText(), self.plugin_dir +
'/styles/pt_trip_by_mode_prop_size_prop_color_screen_unit.qml',
'gid', "geom", '')
elif ((self.obj_def_name == "trips")
and self.ui.radioButtonMapUnit.isChecked()):
self.indicDisplay(
self.ui.comboBoxReq.currentText(),
self.ui.comboBoxReq.currentText(), self.plugin_dir +
'/styles/pt_trip_by_mode_prop_size_prop_color_map_unit.qml',
'gid', "geom", '')
# Stops routes
elif ((self.obj_def_name == "stops_routes")
and self.ui.radioButtonScreenUnit.isChecked()):
self.indicDisplay(
self.ui.comboBoxReq.currentText(),
self.ui.comboBoxReq.currentText(), self.plugin_dir +
'/styles/pt_stop_routes_by_mode_prop_size_prop_color_screen_unit.qml',
'gid', "geom", '')
elif ((self.obj_def_name == "stops_routes")
and self.ui.radioButtonMapUnit.isChecked()):
self.indicDisplay(
self.ui.comboBoxReq.currentText(),
self.ui.comboBoxReq.currentText(), self.plugin_dir +
'/styles/pt_stop_routes_by_mode_prop_size_prop_color_map_unit.qml',
'gid', "geom", '')
# Routes
elif (self.obj_def_name == "routes"):
self.indicDisplay(self.ui.comboBoxReq.currentText(),
self.ui.comboBoxReq.currentText(),
self.plugin_dir + '/styles/pt_route_by_mode.qml',
'gid', None, '')
# Agencies
elif (self.obj_def_name == "agencies"):
self.indicDisplay(
self.ui.comboBoxReq.currentText(),
self.ui.comboBoxReq.currentText(),
self.plugin_dir + '/styles/pt_agency_by_mode.qml', 'gid', None,
'')
# Paths
elif (self.obj_def_name == "paths"):
self.indicDisplay(self.ui.comboBoxReq.currentText(),
self.ui.comboBoxReq.currentText(),
self.plugin_dir + "/styles/mm_path.qml", "gid",
"geom", "")
elif (self.obj_def_name == "paths_details"):
self.indicDisplay(self.ui.comboBoxReq.currentText(),
self.ui.comboBoxReq.currentText(),
self.plugin_dir + "/styles/mm_path_by_mode.qml",
"gid", "geom", "")
# Paths tree described by nodes
elif (self.obj_def_name
== "paths_tree") and (self.ui.toolBoxDisplay.currentIndex()
== 0):
self.indicDisplay(self.ui.comboBoxReq.currentText(),
self.ui.comboBoxReq.currentText() + "_edges",
self.plugin_dir + "/styles/mm_isochron_edge.qml",
"d_node", "geom_section", "")
self.indicDisplay(self.ui.comboBoxReq.currentText(),
self.ui.comboBoxReq.currentText() + "_nodes",
self.plugin_dir + "/styles/mm_isochron_node.qml",
"d_node", "geom_point", "")
elif ((self.obj_def_name == "paths_tree") or
(self.obj_def_name == "comb_paths_trees")) and (
self.ui.toolBoxDisplay.currentIndex() == 1):
self.indicDisplay(
self.ui.comboBoxDerivedRep.currentText(),
self.ui.comboBoxDerivedRep.currentText(),
self.plugin_dir + "/styles/mm_isochron_surface.qml", "id",
"geom", "")
elif (self.obj_def_name == "comb_paths_trees") and (
self.ui.toolBoxDisplay.currentIndex() == 0):
self.indicDisplay(self.ui.comboBoxReq.currentText(),
self.ui.comboBoxReq.currentText(),
self.plugin_dir + "/styles/mm_isochron_node.qml",
"id", "geom", "")
if (self.obj_def_name == "paths"
or self.obj_def_name == "paths_details"
or self.obj_def_name == "paths_tree"
or self.obj_def_name == "comb_paths_trees"):
for layer in self.caller.node_indicators.findLayers():
if (layer.name() == "Destinations"
or layer.name() == "Origines"):
self.iface.legendInterface().setLayerVisible(
layer.layer(), True)
model.setEditStrategy(QtSql.QSqlTableModel.OnFieldChange)
model.select()
model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
model.setHeaderData(1, QtCore.Qt.Horizontal, "First name")
model.setHeaderData(2, QtCore.Qt.Horizontal, "Last name")
def createView(title, model):
view = QtGui.QTableView()
view.setModel(model)
view.setWindowTitle(title)
return view
if __name__ == '__main__':
query = QtSql.QSqlQuery()
app = QtGui.QApplication(sys.argv)
db = QtSql.QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('fichadas.db')
model = QtSql.QSqlTableModel()
delrow = -1
initializeModel(model)
projectModel = QSqlQueryModel()
projectModel.setQuery("select * from empleados",db)
projectView = QTableView()
projectView.setModel(projectModel)
form = Form()
form.show()
sys.exit(app.exec_())
def _slotComboBoxReqIndexChanged(self, indexChosenLine):
self.caller.parent_layer = ""
if (indexChosenLine == 0):
# General interface
self.caller.dlg.ui.pushButtonIndicCalculate.setEnabled(True)
self.caller.dlg.ui.pushButtonReinitCalc.setEnabled(True)
self.comments = ""
# 1st tab
self.caller.dlg.ui.groupBoxObjType.setEnabled(True)
self.caller.dlg.ui.groupBoxIndicators.setEnabled(True)
self.caller.dlg.ui.toolBoxPaths.setEnabled(False)
self.caller.dlg.ui.groupBoxPerimetre.setEnabled(True)
self.caller.updateSelectedNodes()
# 2nd tab
self.caller.dlg.ui.groupBoxPTNetworks.setEnabled(True)
self.caller.dlg.ui.groupBoxAgencies.setEnabled(True)
self.caller.dlg.ui.groupBoxTransportModes.setEnabled(True)
self.caller.dlg.ui.groupBoxForcStop.setEnabled(True)
self.caller.dlg.ui.groupBoxForcRoute.setEnabled(True)
# 3rd tab
self.caller.dlg.ui.toolBoxDays.setEnabled(True)
self.caller.dlg.ui.toolBoxTime.setEnabled(True)
self.caller.dlg.ui.radioButtonTimePoint.setEnabled(False)
# 4th tab
self.ui.groupBoxSize.setEnabled(False)
self.ui.groupBoxColor.setEnabled(False)
self.ui.toolBoxDisplay.setItemEnabled(1, False)
self.ui.pushButtonReqDisplay.setEnabled(False)
self.ui.pushButtonReqRename.setEnabled(False)
self.ui.pushButtonReqDelete.setEnabled(False)
self.ui.pushButtonSaveComments.setEnabled(False)
self.ui.comboBoxPathID.setEnabled(False)
self.ui.textEditComments.setPlainText("")
elif (indexChosenLine > 0):
# 1st tab
#####
# "obj_type" field
self.caller.dlg.ui.comboBoxObjType.setCurrentIndex(
self.caller.modelObjType.match(
self.caller.modelObjType.index(0, 1), 0,
self.caller.modelReq.record(indexChosenLine).value(
"obj_type"))[0].row())
self.obj_def_name = self.caller.modelObjType.record(
self.caller.dlg.ui.comboBoxObjType.currentIndex()).value(
"def_name")
# Disables the groupBox since they should not be modified when an already calculated indicator is displayed
self.caller.dlg.ui.groupBoxObjType.setEnabled(False)
self.caller.dlg.ui.groupBoxPaths.setEnabled(False)
self.caller.dlg.ui.groupBoxIndicators.setEnabled(False)
self.caller.dlg.ui.groupBoxPerimetre.setEnabled(False)
self.caller.dlg.ui.pushButtonIndicCalculate.setEnabled(False)
# "area_type" and "areas" fields
if (self.caller.modelReq.record(indexChosenLine).isNull(
"zoning_filter") == True):
self.caller.dlg.ui.comboBoxZoningFilter.setCurrentIndex(0)
else:
self.caller.dlg.ui.comboBoxZoningFilter.setCurrentIndex(
self.caller.modelZoningSource.match(
self.caller.modelZoningSource.index(0, 2), 0,
self.caller.modelReq.record(indexChosenLine).value(
"zoning_filter"))[0].row())
self.caller.dlg.ui.comboBoxZone.setCurrentIndex(0)
if (self.caller.modelReq.record(indexChosenLine).isNull("zones") ==
False):
for id in ((str(
self.caller.modelReq.record(indexChosenLine).value(
"zones"))).translate(None, "{}").split(",")):
self.caller.dlg.ui.comboBoxZone.setCurrentIndex(
self.caller.modelZone.match(
self.caller.modelZone.index(0, 1), 0, id)[0].row())
# "indics" field
for indic in ((str(
self.caller.modelReq.record(indexChosenLine).value(
"indics"))).translate(None, "{}").split(",")):
row = self.caller.modelIndic.match(
self.caller.modelIndic.index(0, 1), 0, indic, 1)[0].row()
self.caller.dlg.ui.listViewIndic.selectionModel().select(
self.caller.modelIndic.index(row, 0),
QItemSelectionModel.Select)
# origin and destination nodes
if (self.caller.modelReq.record(indexChosenLine).isNull(
"node_type") == False):
self.caller.node_type = self.caller.modelReq.record(
indexChosenLine).value("node_type")
self.caller.dlg.ui.comboBoxNodeType.setCurrentIndex(
self.caller.modelNodeType.match(
self.caller.modelNodeType.index(0, 1), 0,
self.caller.node_type)[0].row())
i = 0
if (self.caller.node_type == 0): # Stop areas
i = 4
if (self.obj_def_name == "paths"
or self.obj_def_name == "paths_details"):
if (self.caller.modelReq.record(indexChosenLine).isNull(
"o_node") == False) and (self.caller.modelReq.record(
indexChosenLine).isNull("d_node") == False):
self.caller.dlg.ui.comboBoxOrig.setCurrentIndex(
self.caller.modelNode.match(
self.caller.modelNode.index(0, i), 0,
self.caller.modelReq.record(indexChosenLine).value(
"o_node"), 1)[0].row())
self.caller.dlg.ui.comboBoxDest.setCurrentIndex(
self.caller.modelNode.match(
self.caller.modelNode.index(0, i), 0,
self.caller.modelReq.record(indexChosenLine).value(
"d_node"), 1)[0].row())
if (self.obj_def_name == "paths_tree"):
if (self.caller.modelReq.record(indexChosenLine).isNull(
"o_node") == False):
self.caller.dlg.ui.comboBoxPathsTreeRootNode.setCurrentIndex(
self.caller.modelNode.match(
self.caller.modelNode.index(0, i), 0,
self.caller.modelReq.record(indexChosenLine).value(
"o_node"), 1)[0].row())
self.caller.dlg.ui.comboBoxPathsTreeOD.setCurrentIndex(0)
elif (self.caller.modelReq.record(indexChosenLine).isNull(
"d_node") == False):
self.caller.dlg.ui.comboBoxPathsTreeRootNode.setCurrentIndex(
self.caller.modelNode.match(
self.caller.modelNode.index(0, i), 0,
self.caller.modelReq.record(indexChosenLine).value(
"d_node"), 1)[0].row())
self.caller.dlg.ui.comboBoxPathsTreeOD.setCurrentIndex(1)
if (self.obj_def_name == "comb_paths_trees"):
if (self.caller.modelReq.record(indexChosenLine).isNull(
"nodes_ag") == False):
self.nodes_ag = self.caller.modelReq.record(
indexChosenLine).value("nodes_ag")
self.caller.dlg.ui.comboBoxNodeAg.setCurrentIndex(
self.caller.modelAgreg.match(
self.caller.modelAgreg.index(0, 1), 0,
self.nodes_ag)[0].row())
else:
self.caller.dlg.ui.comboBoxNodeAg.setCurrentIndex(0)
if (self.caller.modelReq.record(indexChosenLine).isNull(
"o_nodes") == False):
nodes = self.caller.modelReq.record(indexChosenLine).value(
"o_nodes")
elif (self.caller.modelReq.record(indexChosenLine).isNull(
"d_nodes") == False):
nodes = self.caller.modelReq.record(indexChosenLine).value(
"d_nodes")
self.root_nodes = []
for node in ((str(nodes)).translate(None, "{}").split(",")):
self.root_nodes.append(node)
self.caller.updateSelectedNodes()
# 2nd tab
#####
self.caller.dlg.ui.groupBoxPTNetworks.setEnabled(False)
self.caller.dlg.ui.groupBoxAgencies.setEnabled(False)
self.caller.dlg.ui.groupBoxTransportModes.setEnabled(False)
self.caller.dlg.ui.groupBoxForcStop.setEnabled(False)
self.caller.dlg.ui.groupBoxForcRoute.setEnabled(False)
# "stop" field
if (self.caller.modelReq.record(indexChosenLine).isNull("stop") ==
False):
self.caller.dlg.ui.comboBoxForcStop.setCurrentIndex(
self.caller.modelStop.match(
self.caller.modelStop.index(0, 1), 0,
self.caller.modelReq.record(indexChosenLine).value(
"stop"))[0].row())
# "route" field
if (self.caller.modelReq.record(indexChosenLine).isNull("route") ==
False):
self.caller.dlg.ui.comboBoxForcRoute.setCurrentIndex(
self.caller.modelRoute.match(
self.caller.modelRoute.index(0, 1), 0,
self.caller.modelReq.record(indexChosenLine).value(
"route"))[0].row())
# "agencies" field
if (self.caller.modelReq.record(indexChosenLine).isNull("agencies")
== False):
for i in ((str(
self.caller.modelReq.record(indexChosenLine).value(
"agencies"))).translate(None, "{}").split(",")):
row = self.caller.modelAgencies.match(
self.caller.modelAgencies.index(0, 3), 0, i,
1)[0].row()
self.caller.dlg.ui.tableViewAgencies.selectionModel(
).select(self.caller.modelAgencies.index(row, 0),
QItemSelectionModel.Select)
else:
self.caller.dlg.ui.tableViewAgencies.clearSelection()
# "gtfs_feeds" field
if (self.caller.modelReq.record(indexChosenLine).isNull(
"gtfs_feeds") == False):
for feed in ((str(
self.caller.modelReq.record(indexChosenLine).value(
"gtfs_feeds"))).translate(None, "{}").split(",")):
row = self.modelPTSources.match(
self.caller.modelIndic.index(0, 1), 0, feed,
1)[0].row()
self.caller.dlg.ui.listViewPTNetworks.selectionModel(
).select(self.modelPTSources.index(row, 0),
QItemSelectionModel.Select)
# "pt_modes" field
if (self.caller.modelReq.record(indexChosenLine).isNull("pt_modes")
== False and self.caller.modelReq.record(
indexChosenLine).value("pt_modes") != "{}"):
for i in ((str(
self.caller.modelReq.record(indexChosenLine).value(
"pt_modes"))).translate(None, "{}").split(",")):
if (i != "NULL"):
row = self.caller.modelPTModes.match(
self.caller.modelPTModes.index(0, 1), 0, i,
1)[0].row()
self.caller.dlg.ui.listViewPTModes.selectionModel(
).select(self.caller.modelPTModes.index(row, 0),
QItemSelectionModel.Select)
# "i_modes" field
for i in ((str(
self.caller.modelReq.record(indexChosenLine).value(
"i_modes"))).translate(None, "{}").split(",")):
if (i != "NULL"):
row = self.caller.modelIModes.match(
self.caller.modelIModes.index(0, 1), 0, i, 1)[0].row()
self.caller.dlg.ui.listViewIModes.selectionModel().select(
self.caller.modelIModes.index(row, 0),
QItemSelectionModel.Select)
# 3rd tab
#####
self.caller.dlg.ui.toolBoxDays.setEnabled(False)
self.caller.dlg.ui.toolBoxTime.setEnabled(False)
self.caller.dlg.ui.radioButtonTimePoint.setEnabled(False)
# "day" / "day_type", "per_start", "per_end", "day_ag" fields
if (self.caller.modelReq.record(indexChosenLine).isNull("day_type")
== True):
for day in ((str(
self.caller.modelReq.record(indexChosenLine).value(
"days"))).translate(None, "{}").split(",")):
self.caller.dlg.ui.calendarWidget.setSelectedDate(
QDate.fromString(day, "yyyy-MM-dd"))
self.caller.dlg.ui.radioButtonPreciseDate.setChecked(True)
else:
self.caller.dlg.ui.comboBoxDayType.setCurrentIndex(
self.caller.modelDayType.match(
self.caller.modelDayType.index(0, 1), 0,
self.caller.modelReq.record(indexChosenLine).value(
"day_type"))[0].row())
self.caller.dlg.ui.radioButtonDayType.setChecked(True)
self.caller.dlg.ui.dateEditPerStart.setDate(
self.caller.modelReq.record(indexChosenLine).value(
"per_start"))
self.caller.dlg.ui.dateEditPerEnd.setDate(
self.caller.modelReq.record(indexChosenLine).value(
"per_end"))
self.caller.dlg.ui.comboBoxPerType.setCurrentIndex(
self.caller.modelPerType.match(
self.caller.modelPerType.index(0, 1), 0,
self.caller.modelReq.record(indexChosenLine).value(
"per_type"))[0].row())
self.caller.dlg.ui.comboBoxDayAg.setCurrentIndex(
self.caller.modelAgreg.match(
self.caller.modelAgreg.index(0, 1), 0,
self.caller.modelReq.record(indexChosenLine).value(
"day_ag"))[0].row())
# "time_start", "time_end", "time_ag", "time_point" fields
if (self.caller.modelReq.record(indexChosenLine).isNull(
"time_start") == False):
self.caller.dlg.ui.timeEditTimeStart.setTime(
self.caller.modelReq.record(indexChosenLine).value(
"time_start"))
self.caller.dlg.ui.radioButtonTimePeriod.setChecked(True)
self.caller.dlg.ui.timeEditTimeEnd.setTime(
self.caller.modelReq.record(indexChosenLine).value(
"time_end"))
elif (self.caller.modelReq.record(indexChosenLine).isNull(
"constraint_date_after") == False):
self.radioButtonTimePoint.setChecked(True)
self.timeEditTimePoint.setTime(
self.caller.modelReq.record(indexChosenLine).value(
"time_point"))
self.caller.dlg.ui.comboBoxTimePointConstraint.setCurrentIndex(
self.caller.modelTimeConst.match(
self.caller.modelTimeConst.index(0, 1), 0,
self.caller.modelReq.record(indexChosenLine).value(
"time_const"))[0].row())
# This dialog
if ((self.obj_def_name == "stop_areas")
or (self.obj_def_name == "stops")
or (self.obj_def_name == "sections")
or (self.obj_def_name == "trips")
or (self.obj_def_name == "stops_routes")):
self.ui.groupBoxGeoQuery.setEnabled(True)
self.ui.groupBoxColor.setEnabled(True)
self.ui.groupBoxSize.setEnabled(True)
self.ui.comboBoxPathID.setEnabled(False)
self.ui.toolBoxDisplay.setItemEnabled(1, False)
elif ((self.obj_def_name == "paths")
or (self.obj_def_name == "paths_details")):
self.ui.groupBoxGeoQuery.setEnabled(False)
self.ui.comboBoxPathID.setEnabled(True)
if (self.obj_def_name == "paths"):
s = "(SELECT 'Tous' as gid, -1 as gid_order) UNION (SELECT gid::character varying, gid FROM indic." + self.caller.modelReq.record(
self.ui.comboBoxReq.currentIndex()).value(
"layer_name") + ") ORDER BY gid_order"
elif (self.obj_def_name == "paths_details"):
s = "(SELECT 'Tous' as path_id, -1 as path_id_order) UNION (SELECT distinct path_id::character varying, path_id FROM indic." + self.caller.modelReq.record(
self.ui.comboBoxReq.currentIndex()).value(
"layer_name") + ") ORDER BY path_id_order"
self.caller.modelPathID.setQuery(unicode(s), self.db)
self.ui.toolBoxDisplay.setItemEnabled(1, False)
elif ((self.obj_def_name == "routes")
or (self.obj_def_name == "agencies")):
self.ui.groupBoxGeoQuery.setEnabled(False)
self.ui.comboBoxPathID.setEnabled(False)
self.ui.toolBoxDisplay.setItemEnabled(1, False)
elif ((self.obj_def_name == "paths_tree")
or (self.obj_def_name == "comb_paths_trees")):
self.ui.groupBoxGeoQuery.setEnabled(True)
self.ui.groupBoxColor.setEnabled(True)
self.ui.groupBoxSize.setEnabled(True)
self.ui.comboBoxPathID.setEnabled(False)
# Update available derived surface representations
self.ui.toolBoxDisplay.setItemEnabled(1, True)
self.caller.parent_layer = self.ui.comboBoxReq.currentText()
self.refreshDerivedRep()
self._slotComboBoxDerivedRepIndexChanged(0)
self.ui.pushButtonReqDisplay.setEnabled(True)
self.ui.pushButtonReqRename.setEnabled(True)
self.ui.pushButtonReqDelete.setEnabled(True)
self.ui.pushButtonSaveComments.setEnabled(True)
# Update color and size indicators
self.updateReqIndicators()
# Display comments of the current layer
s = "SELECT coalesce(pg_catalog.obj_description((SELECT 'indic." + self.caller.modelReq.record(
self.ui.comboBoxReq.currentIndex()).value(
"layer_name") + "'::regclass::oid)), '');"
q = QtSql.QSqlQuery(unicode(s), self.db)
q.next()
self.ui.textEditComments.setPlainText(q.value(0))
def affiche(self, idsortie):
# Fonction affichant dans QGIS les entités de la sortie en cours, présentes en base.
# Pour l'accès à la base de données postgresql/postigs, voir l.52
# Référencer l'arborescence de la TOC (layer tree). Cela permettra de placer la (les) couche(s) où l'on veut à l'intérieur (i.e. en haut, et en dehors d'un groupe)
root = QgsProject.instance().layerTreeRoot()
# Requête qui sera intégrée dans uri.setDataSource() (cf. paragraphe ci-dessous)
reqwhere="""sortie_id="""+str(idsortie)+""" and the_geom IS NOT NULL""" #self.ui.sortie.itemData(self.ui.sortie.currentIndex())
print reqwhere
self.querypoly = QtSql.QSqlQuery(self.db)
qpoly=u"""select operation_id from bdtravaux.operation_poly where sortie={zr_sortie} order by operation_id limit 1""".format (zr_sortie = idsortie) #self.ui.sortie.itemData(self.ui.sortie.currentIndex())
okpoly = self.querypoly.exec_(qpoly)
if not okpoly:
QtGui.QMessageBox.warning(self, 'Alerte', u'Requête existence polygones ratée')
if self.querypoly.size()>0:
print 'taille requete'+str(self.querypoly.size)
# SURFACES : Import de la couche de polygoness si des surfaces sont saisies pour cette sortie
# Configure le schéma, le nom de la table, la colonne géométrique, et un sous-jeu de données (clause WHERE facultative)
self.uri.setDataSource("bdtravaux", "v_bdtravaux_surfaces", "the_geom", reqwhere, "operation_id")
# Instanciation de la couche dans qgis
self.gestrealpolys=QgsVectorLayer(self.uri.uri(), "gestrealpolys", "postgres")
# if self.gestrealpolys.featureCount()>0: #si la couche importée n'est pas vide...
# Intégration dans le Map Layer Registry pour pouvoir l'utiliser, MAIS sans l'importer dans l'arborescence (d'où le False)
QgsMapLayerRegistry.instance().addMapLayer(self.gestrealpolys, False)
# Intégration de la couche dans l'arboresecnce, à l'index 0 (c'est à dire en haut de l'arborescence)
root.insertLayer(0, self.gestrealpolys)
## Attribution de COULEURS différentes aux opérations
# Récupération des valeurs uniques du champ qui servira de base à la symbologie
layer=self.gestrealpolys
field_index = layer.dataProvider().fieldNameIndex('typ_operat')
unique_values = layer.uniqueValues(field_index)
# Définit une correspondance: valeur -> (couleur) au moyen d'un dictionnaire et de la fonction clr_hasard
# Création du dictionnaire au moyen d'une compréhension de dictionnaire
operations={valeurunique : self.clr_hasard() for valeurunique in unique_values}
# Crée une catégorie pour chaque item dans operations, puis les groupe en une liste (operations)
categories = []
for nom_opera, couleur in operations.items():
symbol = QgsSymbolV2.defaultSymbol(layer.geometryType())
symbol.setColor(QtGui.QColor(couleur))
#création de la catégorie. 1er param : l'attribut / 2ème : le symbole à appliquer / 3ème : l'étiquet ds tble matières
category = QgsRendererCategoryV2(nom_opera, symbol,nom_opera)
categories.append(category)
# Crée le renderer et l'assigne à la couche
expression = 'typ_operat' # nom du champ
renderer = QgsCategorizedSymbolRendererV2(expression, categories)
layer.setRendererV2(renderer)
layer.setLayerTransparency(50)
else:
print 'couche de surfaces vide'
# LIGNES : Import de la couche de lignes si des linéaires sont saisis pour cette sortie
self.querylgn = QtSql.QSqlQuery(self.db)
qlgn=u"""select operation_id from bdtravaux.operation_lgn where sortie={zr_sortie} order by operation_id limit 1""".format (zr_sortie = idsortie ) #self.ui.sortie.itemData(self.ui.sortie.currentIndex())
oklgn = self.querylgn.exec_(qlgn)
if not oklgn:
QtGui.QMessageBox.warning(self, 'Alerte', u'Requête existence lignes ratée')
if self.querylgn.size()>0:
self.uri.setDataSource("bdtravaux", "v_bdtravaux_lignes", "the_geom", reqwhere, "operation_id")
self.gestreallgn=QgsVectorLayer(self.uri.uri(), "gestreallgn", "postgres")
# if self.gestreallgn.featureCount()>0:
QgsMapLayerRegistry.instance().addMapLayer(self.gestreallgn, False)
root.insertLayer(0, self.gestreallgn)
layer=self.gestreallgn
field_index = layer.dataProvider().fieldNameIndex('typ_operat')
unique_values = layer.uniqueValues(field_index)
operations={valeurunique : self.clr_hasard() for valeurunique in unique_values}
categories = []
for nom_opera, couleur in operations.items():
symbol = QgsSymbolV2.defaultSymbol(layer.geometryType())
symbol.setColor(QtGui.QColor(couleur))
category = QgsRendererCategoryV2(nom_opera, symbol,nom_opera)
categories.append(category)
expression = 'typ_operat'
renderer = QgsCategorizedSymbolRendererV2(expression, categories)
layer.setRendererV2(renderer)
else :
print 'couche de linéaires vide'
# POINTS : Import de la couche de points si des ponctuels sont saisis pour cette sortie
self.querypts = QtSql.QSqlQuery(self.db)
qpts=u"""select operation_id from bdtravaux.operation_pts where sortie={zr_sortie} order by operation_id limit 1""".format (zr_sortie = idsortie) #self.ui.sortie.itemData(self.ui.sortie.currentIndex())
okpts = self.querypts.exec_(qpts)
if not okpts:
QtGui.QMessageBox.warning(self, 'Alerte', u'Requête existence points ratée')
if self.querypts.size()>0:
self.uri.setDataSource("bdtravaux", "v_bdtravaux_points", "the_geom", reqwhere, "operation_id")
self.gestrealpts=QgsVectorLayer(self.uri.uri(), "gestrealpts", "postgres")
#if self.gestrealpts.featureCount()>0:
QgsMapLayerRegistry.instance().addMapLayer(self.gestrealpts, False)
root.insertLayer(0, self.gestrealpts)
layer=self.gestrealpts
field_index = layer.dataProvider().fieldNameIndex('typ_operat')
unique_values = layer.uniqueValues(field_index)
operations={valeurunique : self.clr_hasard() for valeurunique in unique_values}
categories = []
for nom_opera, couleur in operations.items():
symbol = QgsSymbolV2.defaultSymbol(layer.geometryType())
symbol.setColor(QtGui.QColor(couleur))
category = QgsRendererCategoryV2(nom_opera, symbol,nom_opera)
categories.append(category)
expression = 'typ_operat'
renderer = QgsCategorizedSymbolRendererV2(expression, categories)
layer.setRendererV2(renderer)
else :
print 'couche de ponctuels vide'
def populateDemo(self, omitted_modules=[]):
'''
checks connection is to openmolar_demo, and if so,
adds demo data to the tables.
'''
if not self.isOpen():
return (False, _("no connection"))
LOGGER.info("POPULATING DATABASE WITH DEMO DATA")
## iterate over the ORM modules
## order is important (foreign keys etc)
for module in ADMIN_MODULES:
if module in omitted_modules:
LOGGER.info("Ommitting module %s" % module.__name__)
continue
try:
if not self.isOpen():
return (False, _("no connection"))
builder = module.DemoGenerator(self)
logged = False
total_number_of_queries = builder.length
number_of_queries_executed = 0
for query, values in builder.demo_queries():
if not logged:
LOGGER.info(u"%s..." % query[:77])
logged = True
q_query = QtSql.QSqlQuery(self)
q_query.prepare(query)
for value in values:
q_query.addBindValue(value)
q_query.exec_()
if q_query.lastError().isValid():
error = q_query.lastError().text()
self.emit_(QtCore.SIGNAL("Query Error"), error)
LOGGER.error(error)
break
number_of_queries_executed += 1
progress = int(number_of_queries_executed /
total_number_of_queries * 100)
if progress % 10 == 0:
self.emit_(QtCore.SIGNAL("demo progress"), module,
progress)
else: #some modules have no queries
self.emit_(QtCore.SIGNAL("demo progress"), module, 100)
except Exception as e:
self.emit_(
QtCore.SIGNAL("Query Error"),
'''Error installing demo data from module %s<hr />%s''' %
(module.__name__, e))
LOGGER.error('ERROR INSTALLING DATA from module %s' %
module.__name__)
LOGGER.exception('CRITICAL ERROR')
self.emit_(QtCore.SIGNAL("demo install complete"))
LOGGER.info("DEMO INSTALL COMPLETE")
return True
def add_table_names(self, connection):
result = QtSql.QSqlQuery('SELECT tablename FROM pg_tables', connection)
while result.next():
self.add(result.value(0).toString())
self.prepare()
def sauvSaisie(self):
if self.ui.cbx_habref.itemData(self.ui.cbx_habref.currentIndex())=='0':
return
self.erreurSaisieBase = '0'
#copie des entités sélectionnées dans une couche "memory". Evite les problèmes avec les types de couches "non éditables" (comme les GPX).
coucheactive=self.iface.activeLayer()
# On vérifie que les entités sélectionnées dans la couche active sont bien des polygones (et non des lignes ou des points)
entselect=[QgsGeometry(feature.geometry()) for feature in coucheactive.selectedFeatures()]
if entselect[0].type() == QGis.Polygon:
typegeom='Polygon'
# geom = self.transfoPoly(entselect)
# print "geom=" + str(geom.exportToWkt())
else:
QtGui.QMessageBox.warning(self, 'Alerte', u'Les entités sélectionnées ne sont pas des polygones')
self.close
return
# Création de la couche en mémoire "memlayer" et début de la session d'édition
if coucheactive.crs().authid() == u'EPSG:4326':
memlayer=QgsVectorLayer("{zr_typegeom}?crs=epsg:4326".format(zr_typegeom = typegeom), "memlayer", "memory")
if coucheactive.crs().authid() == u'EPSG:2154':
memlayer=QgsVectorLayer("{zr_typegeom}?crs=epsg:2154".format(zr_typegeom = typegeom), "memlayer", "memory")
else :
QtGui.QMessageBox.warning(self, 'Alerte', u'Les seules projections supportées sont le WGS84 et le Lambert93. Merci de vérifier la projection de votre couche SIG')
return
QgsMapLayerRegistry.instance().addMapLayer(memlayer, False)
root = QgsProject.instance().layerTreeRoot()
memlayerNode = QgsLayerTreeLayer(memlayer)
root.insertChildNode(0, memlayerNode)
self.iface.setActiveLayer(memlayer)
memlayer.startEditing()
# Pour chaque entité sélectionnée, si elle est multipartie, on ajoute chacune de ses parties individuellement à la couche memlayer.
# Sinon, on l'ajoute directement à "memlayer". Puis, on clot la session d'édition.
for feature in coucheactive.selectedFeatures() :
geom = feature.geometry()
temp_feature = QgsFeature(feature)
# check if feature geometry is multipart
if geom.isMultipart():
# if feature is multipart creates a new feature using the geometry of each part
for part in geom.asGeometryCollection ():
temp_feature.setGeometry(part)
memlayer.dataProvider().addFeatures([temp_feature])
memlayer.updateExtents()
# if feature is singlepart, simply adds it to the layer memory
else :
temp_feature.setGeometry(geom)
memlayer.dataProvider().addFeatures([temp_feature])
memlayer.updateExtents()
memlayer.commitChanges()
print "memlayercount="+str(memlayer.featureCount())
#on sélectionne toutes les entités de memlayer pour en faire une liste de géométries, qui sera saisie en base.
memlayer.selectAll()
geomlis = [QgsGeometry(feature.geometry()) for feature in memlayer.selectedFeatures()]
#geomlist = QgsGeometry.fromMultiPolygon([poly.asPolygon() for poly in geomlis]) --- transformation en multipolygone
for entite in geomlis :
#export de la géométrie en WKT et transformation de la projection si les données ne sont pas saisies en Lambert 93
if memlayer.crs().authid() == u'EPSG:2154':
thegeom='st_setsrid(st_geometryfromtext (\'{zr_geom}\'), 2154)'.format(zr_geom=entite.exportToWkt())
elif memlayer.crs().authid() == u'EPSG:4326':
thegeom='st_transform(st_setsrid(st_geometryfromtext (\'{zr_geom}\'),4326), 2154)'.format(zr_geom=entite.exportToWkt())
else :
print u'La projection de la couche active n\'est pas supportée'
#gestion de l'identifiant id_mosaik, servant à regrouper les enregistrements appartenant à une même mosaïque d'habitats
pourcent = int(self.ui.cbx_pourcent.itemText(self.ui.cbx_pourcent.currentIndex()))
if pourcent > 99 :
print ">99"
id_mosaik = 0
else :
querymosaik = QtSql.QSqlQuery(self.db)
qmosaik = u"""SELECT id_hab_ce, annee, pourcent,the_geom, plantation, id_mosaik FROM bd_habnat.t_ce_saisie WHERE the_geom = {zr_thegeom} AND annee = '{zr_annee}' AND plantation = 'f' """.format (\
zr_thegeom = thegeom,\
zr_annee = self.ui.cbx_annee.itemText(self.ui.cbx_annee.currentIndex()))
ok = querymosaik.exec_(qmosaik)
if not ok:
QtGui.QMessageBox.warning(self, 'Alerte', u'Requête Mosaik ratée')
if querymosaik.size() > 0 :
print "on est deja dans la mosaique"
sumprct = 0
while querymosaik.next() :
sumprct += int(querymosaik.value(2))
sumprct += pourcent
if sumprct > 100 :
QtGui.QMessageBox.warning(self, 'Alerte', u'La somme des pourcentages des habitats dépasse 100 % dans la mosaïque')
return
querymosaik.first()
id_mosaik = querymosaik.value(5)
else :
querybiggestid = QtSql.QSqlQuery(self.db)
qbiggestid = u"""SELECT id_mosaik FROM bd_habnat.t_ce_saisie ORDER BY id_mosaik DESC LIMIT 1"""
ok = querybiggestid.exec_(qbiggestid)
if not ok:
QtGui.QMessageBox.warning(self, 'Alerte', u'Requête PlusGrandIdMosaik ratée')
querybiggestid.next()
print "debut de la mosaique"
if self.ui.chx_plantation.isChecked == True :
id_mosaik = 0
else :
id_mosaik = int(querybiggestid.value(0))+1
self.habref = self.ui.cbx_habref.itemData(self.ui.cbx_habref.currentIndex())
# si ref = cbnbl : recup rareté, menace et intérêt patri en fonction du taxon sélectionné par l'utilisateur
if self.habref == 'cbnbl':
queryrarmen = QtSql.QSqlQuery(self.db)
qrarmen = u"""SELECT rarete, menace, interetpatr FROM bd_habnat.t_liste_ref_cbnbl_v12 WHERE hab_lat = '{zr_hablat}'""".format (\
zr_hablat= self.ui.cbx_hablat.itemText(self.ui.cbx_hablat.currentIndex()).replace("\'","\'\'"))
okrarmen=queryrarmen.exec_(qrarmen)
if not okrarmen :
QtGui.QMessageBox.warning(self, 'Alerte', u'Requête RarMen ratée')
queryrarmen.next()
self.rarete = queryrarmen.value(0)
self.menace = queryrarmen.value(1)
self.interetpatr = queryrarmen.value(2)
# construction de pat_cen en fonction de intérêt patri
querypatcen = QtSql.QSqlQuery(self.db)
qpatcen = u""" SELECT CASE WHEN cbnbl.interetpatr IN ('Oui','#') THEN True ELSE False END FROM bd_habnat.t_liste_ref_cbnbl_v12 cbnbl WHERE hab_lat = '{zr_hablat}'""".format (\
zr_hablat= self.ui.cbx_hablat.itemText(self.ui.cbx_hablat.currentIndex()).replace("\'","\'\'"))
okpatcen = querypatcen.exec_(qpatcen)
if not okpatcen :
QtGui.QMessageBox.warning(self, 'Alerte', u'Requête PatCen ratée')
querypatcen.next()
self.pat_cen = querypatcen.value(0)
# si ref != cbnbl : rareté, menace, intérêt patri = '/' et pat_cen = False
else :
self.rarete = '/'
self.menace = '/'
self.interetpatr = '/'
self.pat_cen = 'false'
# aller chercher hab_cod dans la cbx "lat" si self.habref = cbnbl, sinon dans cbx "fr".
# aller chercher code_syntax, code_cahab, code_eunis et code_corine dans itemData cbx_hablat ou cbx_habfr, selon le référentiel choisi
if self.habref == 'cbnbl':
self.habcod = self.ui.cbx_hablat.itemData(self.ui.cbx_hablat.currentIndex())
v_syntax = self.habcod
v_cahab = ''
v_eunis =''
v_corine =''
else :
self.habcod = self.ui.cbx_habfr.itemData(self.ui.cbx_habfr.currentIndex())
if self.habref == 'eur27' :
v_syntax = ''
v_cahab = self.habcod
v_eunis = ''
v_corine = ''
elif self.habref == 'eunis' :
v_syntax = ''
v_cahab = ''
v_eunis = self.habcod
v_corine = ''
elif self.habref == 'corine' :
v_syntax = ''
v_cahab = ''
v_eunis = ''
v_corine = self.habcod
# récupération du code N2000 par txt_codeeur27 s'il est renseigné, sinon cbx_eur27_mep
if self.ui.txt_codeeur27.text() != '':
print 'txt_codeeur27 renseigne'
self.codeeur27 = self.ui.txt_codeeur27.text()
else :
if self.ui.cbx_eur27_mep.currentIndex() == 0 :
self.codeeur27 = ''
else :
self.codeeur27 = self.ui.cbx_eur27_mep.itemText(self.ui.cbx_eur27_mep.currentIndex())
#lancement de la requête SQL qui introduit les données géographiques et du formulaire dans la base de données.
querysauvhab = QtSql.QSqlQuery(self.db)
query = u"""INSERT INTO bd_habnat.t_ce_saisie(codesite, auteur, annee, hab_ref, hab_cod, hab_lat, hab_fr, hab_comment, code_syntax, code_cahab, code_eur27, code_eunis, code_corine, pourcent, rarete, menace, patrimoine, pat_cen, surf_tot, the_geom, plantation, id_mosaik, hab_etat, faciesa, date_crea_poly) VALUES ('{zr_codesite}', '{zr_auteur}', '{zr_annee}', '{zr_habref}','{zr_habcod}', '{zr_hablat}', '{zr_habfr}', '{zr_habcomment}', '{zr_codesyntax}', '{zr_codecahab}', '{zr_codeeur27}', '{zr_codeeunis}', '{zr_codecorine}', '{zr_pourcent}', '{zr_rarete}', '{zr_menace}', '{zr_interetpatr}', {zr_pat_cen}, st_area({zr_thegeom}), {zr_thegeom}, {zr_plantation}, {zr_idmosaik}, '{zr_habetat}', '{zr_faciesa}', current_date)""".format (\
zr_codesite = self.ui.cbx_codesite.itemData(self.ui.cbx_codesite.currentIndex()),\
zr_auteur = self.ui.cbx_auteur.itemData(self.ui.cbx_auteur.currentIndex()),\
zr_annee = self.ui.cbx_annee.itemText(self.ui.cbx_annee.currentIndex()),\
zr_habref = self.ui.cbx_habref.itemData(self.ui.cbx_habref.currentIndex()),\
zr_habcod = self.habcod,\
zr_hablat = self.ui.cbx_hablat.itemText(self.ui.cbx_hablat.currentIndex()).replace("\'","\'\'"),\
zr_habfr = self.ui.cbx_habfr.itemText(self.ui.cbx_habfr.currentIndex()).replace("\'","\'\'"),\
zr_habcomment = self.ui.txt_comment.toPlainText().replace("\'","\'\'"),\
zr_codesyntax = v_syntax,\
zr_codecahab = v_cahab,\
zr_codeeur27 = self.codeeur27,\
zr_codeeunis = v_eunis,\
zr_codecorine = v_corine,\
zr_pourcent = self.ui.cbx_pourcent.itemText(self.ui.cbx_pourcent.currentIndex()),\
zr_rarete = self.rarete,\
zr_menace = self.menace,\
zr_interetpatr = self.interetpatr,\
zr_pat_cen = self.pat_cen,\
zr_thegeom = thegeom,\
zr_plantation = str(self.ui.chx_plantation.isChecked()).lower(),\
zr_idmosaik = id_mosaik,\
zr_habetat = ";".join([unicode(x.text()) for x in self.ui.lst_evol.selectedItems()]),\
zr_faciesa = self.ui.txt_faciesa.text().replace("\'","\'\'"))
ok = querysauvhab.exec_(query)
if not ok:
QtGui.QMessageBox.warning(self, 'Alerte', u'Requête sauver Ope ratée')
self.erreurSaisieBase = '1'
self.iface.setActiveLayer(coucheactive)
QgsMapLayerRegistry.instance().removeMapLayer(memlayer.id())
# Réinitialiser certains contrôles
self.ui.lst_evol.clearSelection()
self.ui.txt_faciesa.setText('')
self.ui.txt_comment.setText('')
self.ui.cbx_eur27_mep.setCurrentIndex(0)
if self.erreurSaisieBase == '0':
QtGui.QMessageBox.information(self, 'Information', u'Données correctement saisies dans la base')
else :
QtGui.QMessageBox.warning(self, 'Alerte', u'Il y a eu une erreur lors de la saisie. Données non saisies en base.')
self.close()
def save_(self):
"""
This function is huge...
This saves all the data associated with the current part.
It starts by saving the basic info to Doyle's database. This is the
slowest part because of the connection problems.
After that, if all the info is filled out, it saves the machining
specific data to Riverview's database. To do this it has to look up the
machine and material to get the right id numbers. If the user enters an
invalid machine, 'N/A' is substituted. For material, the new material
is added to the system and the new id is retrieved and used.
"""
QtGui.QApplication.setOverrideCursor(QtGui.QCursor(QtCore.Qt.WaitCursor))
# Open a connection to Doyle's database.
self.dbw, ok = write_connection(self)
save_qry = QtSql.QSqlQuery()
while ok:
descp = prepare_string(self.description.text())
dest = prepare_string(self.destination.text())
notes = prepare_string(self.notes.toPlainText())
# User must at least have a description and destination to save.
if descp != "" and dest != "":
dest_qry = "Select Dest_ID from inventorysystem.Destination_tbl where Dest_Desc = '{0}'".format(dest)
if save_qry.exec_(dest_qry): # Get the destination id.
if save_qry.first():
dest = save_qry.value(0).toString()
else:
dest = '7'
else:
db_err(save_qry)
dest = '7'
qry = ("update inventorysystem.Parts_tbl set Part_Desc='{0}', Part_Notes='{1}',destination={2} where "
"Part_ID = {3}").format(descp, notes, dest, self.partId.text())
write_qry = QtSql.QSqlQuery(self.dbw)
if not write_qry.exec_(qry):
db_err(write_qry)
break
del self.dbw
mach = self.machine.text()
cycle = self.cycleTime.text()
load = self.loadTime.text()
setup = self.setupTime.text()
mat = self.material.text()
clamp = self.clamp.text()
blank_length = self.blankLength.text()
bar = self.barPull.text()
square = self.squareSet.text()
pos_stop = self.posStop.text()
proc = self.routing.text()
prog_file = self.program.text()
if mach != "" and mat != "" and proc != "":
mach_qry = "Select id from machines where name = '{0}'".format(mach)
if save_qry.exec_(mach_qry):
if save_qry.first():
mach = save_qry.value(0).toString()
else:
mach = "14"
else:
db_err(save_qry)
mach = "14"
mat_qry = "Select id from material where material = '{0}'".format(mat)
if save_qry.exec_(mat_qry):
if save_qry.first():
mat = save_qry.value(0).toString()
else:
if save_qry.exec_("insert into material set material='{0}'".format(mat)):
mat = save_qry.lastInsertId().toString()
else:
db_err(save_qry)
mat = "17"
else:
db_err(save_qry)
mat = "17"
qry2 = ("machine={0}, cycleTime=time_to_Sec('{1}'), loadTime=time_to_Sec('{2}'), "
"setupTime=time_to_Sec('{3}'), material={4}, clampPSI={5}, blankLength={6}, barPull={7}, "
"squareSet={8}, posStop={9}, Process='{10}', fileName='{11}'"
).format(mach, cycle, load, setup, mat, clamp, blank_length, bar, square, pos_stop, proc, prog_file)
if self.index.text() == '0':
part_id = self.partId.text()
qry2 = ("Insert into setupInfo set {0}, partId={1}".format(qry2, part_id))
else:
qry2 = ("Update setupInfo set {0} where id={1}".format(qry2, self.index.text()))
local_qry = QtSql.QSqlQuery()
if not local_qry.exec_(qry2):
db_err(local_qry)
self.load_data(self.partId.text())
QtGui.QApplication.restoreOverrideCursor()
def __init__(self, iface):
"""Constructor."""
QtGui.QDialog.__init__(self)
self.ui = Ui_bdhabnat_dialog()
self.ui.setupUi(self)
# Référencement de iface dans l'interface (iface = interface de QGIS)
self.iface = iface
self.canvas = self.iface.mapCanvas()
# Connexion à la base de données. DB type, host, user, password...
self.db = QtSql.QSqlDatabase.addDatabase("QPSQL") # QPSQL = nom du pilote postgreSQL
self.db.setHostName("192.168.0.10")
self.db.setDatabaseName("sitescsn")
self.db.setUserName("postgres")
self.db.setPassword("postgres")
ok = self.db.open()
if not ok:
QtGui.QMessageBox.warning(self, 'Alerte', u'La connexion est échouée'+self.db.hostName())
# Remplir la combobox "cbx_codesite" avec les codes et noms de sites issus de la table "t_sitescen"
query_codesite = QtSql.QSqlQuery(self.db)
if query_codesite.exec_('select idchamp, codesite, nomsite from sites_cen.t_sitescen order by codesite'):
while query_codesite.next():
self.ui.cbx_codesite.addItem(query_codesite.value(1) + " " + query_codesite.value(2), query_codesite.value(1) )
# Remplir la combobox "cbx_auteurs" avec les noms et prénoms issus de la table "t_liste_obsv"
query_obsv = QtSql.QSqlQuery(self.db)
if query_obsv.exec_('select id_obsv, nom_obsv, prenom_obsv, initiale_obsv from bd_habnat.t_liste_obsv order by nom_obsv, prenom_obsv'):
while query_obsv.next():
self.ui.cbx_auteur.addItem(query_obsv.value(1) + " " + query_obsv.value(2), query_obsv.value(3) )
# Combobox cbx_annee : Affecter l'année en cours par défaut en fonction de la date (changement d'année au 28/2/N)
if datetime.now().strftime('%m/%d') < '04/02' :
self.ui.cbx_annee.setCurrentIndex(self.ui.cbx_annee.findText((str(int(datetime.now().strftime('%Y'))-1)), QtCore.Qt.MatchStartsWith))
else:
self.ui.cbx_annee.setCurrentIndex(self.ui.cbx_annee.findText((datetime.now().strftime('%Y')), QtCore.Qt.MatchStartsWith))
# Remplir la combobox "cbx_habref" avec les noms de référentiels issus de la table "t_liste_ref"
query_ref = QtSql.QSqlQuery(self.db)
if query_ref.exec_('select id_ref, nom_ref, code_ref from bd_habnat.t_liste_ref order by id_ref'):
while query_ref.next():
self.ui.cbx_habref.addItem(query_ref.value(1), query_ref.value(2) )
self.ui.cbx_habref.model().item(0).setEnabled(False)
# Remplir la combobox "cbx_eur27_mep" avec le champs hab_cod de la table "t_liste_ref_eur27"
query_eur27 = QtSql.QSqlQuery(self.db)
if query_eur27.exec_('select hab_cod from bd_habnat.t_liste_ref_eur27 order by hab_cod'):
while query_eur27.next():
self.ui.cbx_eur27_mep.addItem(query_eur27.value(0), query_eur27.value(0) )
# Désactiver le bouton "OK" tant qu'on n'a pas choisi au moins un référentiel.
self.ui.buttonBox.setEnabled(False)
# Connexions signaux - slots
self.ui.cbx_habref.currentIndexChanged.connect(self.listesref)
self.ui.cbx_habfr.currentIndexChanged.connect(self.coreur27)
self.ui.buttonBox.accepted.connect(self.sauvSaisie)
self.ui.buttonBox.rejected.connect(self.close)
self.ui.chx_plantation.stateChanged.connect(self.plantation)
self.ui.cbx_hablat.currentIndexChanged.connect(self.listefran)
self.ui.cbx_habref.currentIndexChanged.connect(self.activButton)
def setFirstName(self, personId, firstName):
query = QtSql.QSqlQuery()
query.prepare('update person set firstname = ? where id = ?')
query.addBindValue(firstName)
query.addBindValue(personId)
return query.exec_()
def insert_failure(self, failure):
"""Inserts a failure into the database"""
QtSql.QSqlQuery(
'''INSERT INTO failures VALUES ("%d", "%s", "%s", %d)''' %
(int(failure.talkId), failure.comment, failure.indicator, failure.release))
log.info("Failure added: %s - %s" % (failure.talkId, failure.comment))
