def validarCierreAnual(self):
try:
query=QSqlQuery()
q=u"""
SELECT d.iddocumento
FROM documentos d
WHERE d.idtipodoc=%d
AND YEAR(d.fechacreacion)=%s""" %(constantes.IDCIERREMENSUAL,self.fecha.toString( "yyyy" ))
query.prepare(q)
if not query.exec_():
raise Exception( "No se pudo ejecutar la consulta para determinar si se cerraron todos los meses del año" )
if query.size()<12 and query.size()>0:
raise UserWarning( "No se han cerrado todos los meses del Ejercicio" )
return True
except UserWarning as inst:
logging.error( unicode( inst ) )
QMessageBox.critical( self, qApp.applicationName(), unicode( inst ) )
self.toolBar.removeAction( self.actionSave )
return False
except Exception as inst:
logging.critical( unicode( inst ) )
self.toolBar.removeAction( self.actionSave )
QMessageBox.warning( self,
qApp.organizationName(),unicode(inst))
return False
def validarCierreMensual(self):
try:
query=QSqlQuery()
q=u"""
SELECT d.iddocumento
FROM documentos d
WHERE d.idtipodoc=%d
AND YEAR(d.fechacreacion)=%s""" %(constantes.IDCIERREMENSUAL,self.fecha.toString( "yyyy" ))
query.prepare(q)
if not query.exec_():
raise UserWarning( "No se pudo ejecutar la consulta para determinar si existe algun cierre en el año" )
if query.size()>0:
mes=self.fecha.addMonths(-1)
q=u"""
SELECT d.iddocumento
FROM documentos d
WHERE d.idtipodoc=%d
AND MONTH(fechacreacion)=%s
AND YEAR(d.fechacreacion)=%s""" %(constantes.IDCIERREMENSUAL,mes.toString( "MM"),self.fecha.toString( "yyyy" ))
query.prepare(q)
if not query.exec_():
raise UserWarning( "No se pudo ejecutar la consulta para determinar si se cerro el mes anterior" )
if query.size()==0:
raise UserWarning( "No se ha cerrado el mes anterior" )
#Verifico si existe un cierre para el mes en proceso
q = """
SELECT
d2.iddocumento
FROM documentos d
JOIN docpadrehijos dp ON d.iddocumento=dp.idpadre
JOIN documentos d2 ON d2.iddocumento=dp.idhijo
WHERE d2.idtipodoc=%d and month(d2.fechacreacion)=%s
LIMIT 1
""" % ( constantes.IDCIERREMENSUAL, self.fecha.toString( "MM" ) )
query.prepare( q )
if not query.exec_():
raise UserWarning( "No se pudo ejecutar la consulta para "\
+ "verificar si existe un cierre contable" )
#El mes actual no se puede cerrar
hoy=QDate.currentDate()
if self.fecha.month()==hoy.month() and self.fecha.year() == hoy.year():
raise UserWarning( "No se puede cerrar el mes en proceso" )
return True
except Exception as inst:
logging.critical( unicode( inst ) )
self.toolBar.removeAction( self.actionSave )
QMessageBox.warning( self,
qApp.organizationName(),unicode(inst))
return False
def init( self ):
try:
if not self.database.isOpen():
if not self.database.open():
raise UserWarning( u"No se pudo abrir la conexión "\
+ "con la base de datos" )
query = QSqlQuery( """
SELECT * FROM cuentascontables c
jOIN cuentasxdocumento cd ON c.idcuenta = cd.idcuenta
;
""" )
if not query.exec_():
raise UserWarning( "No se pudo consultar el catalogo de cuentas" )
self.inicial = query.size()==0
except UserWarning as inst:
logging.error( unicode( inst ) )
self.movimientosDisponibles(False)
finally:
if self.database.isOpen():
self.database.close()
self.btnMovements.setText("Balance \n Inicial" if self.inicial else "Ajustes Contables" )
self.status = True
def init_db(self):
print "here"
sql_query = QSqlQuery('''SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'helferlein';''', self.db)
size = sql_query.size()
print size
if size == 0:
print "db anlegen"
sql_query = QSqlQuery("CREATE DATABASE helferlein;", self.db)
print "tabelle schueler anlegen"
sql_query = QSqlQuery('''CREATE TABLE SCHUELER (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name CHAR(100),
Vorname CHAR(100),
Stufe CHAR(2));''', self.db)
print "tabelle fehlzeit anlegen"
sql_query.prepare('''CREATE TABLE FEHLZEIT (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
schueler_id INT,
Grund CHAR(100),
Beginn CHAR(10),
Ende CHAR(10),
Stunden INT,
ist_Beurlaubung INT,
Attest INT,
klausur_verpasst INT,
Schuljahr CHAR(10));''')
sql_query.exec_()
def recuperar_datos(self):
query = QSqlQuery()
sql = "select cliente_id,fecha,hora,notas from ventas where venta_id=%d" % self.id
if not query.exec_(sql):
print "Error al obtener venta."
return
g = lambda x:query.value(x).toString()
query.next()
self.cliente_id = int(g(0))
self.fecha = g(1)
self.hora = g(2)
self.notas = g(3)
self.productos = {}
sql = """ select codigo,cantidad,descuento from detalle_ventas where venta_id=%d""" % self.id
query.exec_(sql)
for i in range(query.size()):
query.next()
asd = str(g(0))
self.productos[asd] = producto(g(0))
self.productos[asd].cantidad = int(g(1))
self.productos[asd].descuento = int(g(2))
self.productos[asd].actualizar_subtotal()
self.actualizar_total()
def llenar_usuarios(self):
query = QSqlQuery()
sql = "select usuario from usuarios"
if query.exec_(sql):
for i in range(query.size()):
query.next()
qn = QListWidgetItem(query.value(0).toString())
self.list_usuarios.addItem(qn)
def load(self, query):
self.nesting = 1
self.root = BranchNode("")
try:
query = QSqlQuery(query)
if not query.exec_():
raise Exception("No se pudieron recuperar las categorias")
self.columns = query.record().count()
print query.size()
if query.size() > 0:
while query.next():
fields = []
for i in range(self.columns):
fields.append(str(query.value(i)))
self.addRecord(fields, False)
except Exception as inst:
print unicode(inst)
return False
def load(self,query):
self.nesting = 1
self.root = BranchNode("")
try:
query = QSqlQuery( query )
if not query.exec_():
raise Exception( "No se pudieron recuperar las categorias" )
self.columns = query.record().count()
print query.size()
if query.size()>0:
while query.next():
fields= []
for i in range (self.columns):
fields.append(str(query.value(i)))
self.addRecord(fields, False)
except Exception as inst:
print unicode( inst )
return False
def on_dtPicker_dateTimeChanged(self, datetime):
"""
Cambiar el tipo de cambio del modelo de edición si cambia la fecha
@param datetime: La fecha contenida en self.dtPicker
@type datetime: QDateTime
"""
query = QSqlQuery()
try:
if not self.database.isOpen():
if not self.database.open():
raise Exception(
"No se pudo conectar a la base de " + "datos para recuperar los tipos " + "de cambio"
)
q = """
SELECT idtc, tasa
FROM tiposcambio
WHERE fecha = %s
LIMIT 1
""" % datetime.toString(
"yyyyMMdd"
)
if not query.exec_(q):
raise UserWarning("No se pudieron recuperar los tipos de " + "cambio")
if not query.size() == 1:
logging.critical(u"La consulta para obtener tipos de " + "cambio no devolvio exactamente un valor")
raise UserWarning(u"Hubo un error al obtener los tipos " + "de cambio")
query.first()
self.editmodel.exchangeRateId = query.value(0).toInt()[0]
self.editmodel.exchangeRate = Decimal(query.value(1).toString())
# self.editmodel.setData( self.editmodel.index( 0, 0 ), self.editmodel.index( 0, 0 ).data() )
self.editmodel.datetime = datetime
except UserWarning as inst:
QMessageBox.critical(self, qApp.organizationName(), unicode(inst))
self.dtPicker.setDateTime(self.editmodel.datetime)
logging.error(inst)
logging.error(query.lastError().text())
except Exception as inst:
QMessageBox.critical(self, qApp.organizationName(), u"Hubo un error al obtener los tipos de" + " cambio")
logging.critical(query.lastError().text())
logging.critical(inst)
self.dtPicker.setDateTime(self.editmodel.datetime)
def recuperar_datos(self):
query = QSqlQuery()
sql = """ select nombre,descripcion,precio,existencia from productos where codigo="%s" """ % self.codigo
query.exec_(sql)
if query.size()<1:
print "Producto no existe"
self.existe = False
return
g = lambda x:query.value(x).toString()
query.next()
self.nombre = g(0)
self.descripcion = g(1)
self.existencia = int(g(3))
self.precio = float(g(2))
self.existe = True
def recuperar_datos(self):
query = QSqlQuery()
sql = "select nombre,telefono,celular,direccion,CP,RFC from clientes where cliente_id=?"
query.prepare(sql)
query.addBindValue(self.id)
query.exec_()
if query.size() < 1:
print "Error en el cliente no existe?"
return
g = lambda x:query.value(x).toString()
query.next()
self.nombre = g(0)
#self.apellidos = g(1)
self.telefono = g(1)
self.celular = g(2)
self.direccion = g(3)
self.CP = g(4)
self.RFC = g(5)
def updateEditModels( self ):
# Rellenar el combobox de los proveedores
self.providersModel.setQuery( """
SELECT idpersona , nombre AS proveedor
FROM personas
WHERE tipopersona = 2
""" )
if not self.providersModel.rowCount( QModelIndex() ) > 0:
raise UserWarning( "No existen proveedores en la base de datos" )
self.cbProvider.setModel( self.providersModel )
self.cbProvider.setModelColumn( 1 )
completer = QCompleter()
completer.setCaseSensitivity( Qt.CaseInsensitive )
completer.setModel( self.providersModel )
completer.setCompletionColumn( 1 )
self.editmodel.providerId = self.providersModel.record(
self.cbProvider.currentIndex()
).value( "idpersona" ).toInt()[0]
query = QSqlQuery( """
SELECT idarticulo, Descripcion as descripcion
FROM vw_articulosdescritos
""" )
if not query.size() > 0:
raise UserWarning( "No existen productos en la base de datos" )
prods = SingleSelectionModel()
query.exec_()
while query.next():
prods.items.append( [
query.value( 0 ).toInt()[0],
query.value( 1 ).toString()
] )
prods.headers = ["idarticulo", "Articulo"]
self.delegate.prods = prods
def on_ckIva_toggled(self, on):
"""
"""
# Verificar IVA
query = QSqlQuery(
"""
SELECT idcostoagregado, valorcosto
FROM costosagregados c
WHERE idtipocosto = 1 AND activo = 1
ORDER BY idtipocosto;
"""
)
query.exec_()
if not query.size() == 1:
QMessageBox.information(self, qApp.organizationName(), "No fue posible obtener el porcentaje " + "del IVA")
if on:
self.editmodel.hasiva = True
query.first()
self.editmodel.ivaId = query.value(0).toInt()[0]
else:
self.editmodel.hasiva = False
self.updateTotals()
class QtSqlDBCursor:
def __init__(self, conn):
self.qry = QSqlQuery(conn)
self.description = None
self.rowcount = -1
self.arraysize = 1
def close(self):
self.qry.finish()
def execute(self, operation, parameters=[]):
if len(parameters) == 0:
if not self.qry.exec_(operation):
raise ExecError(self.qry.lastError().databaseText())
else:
if not self.qry.prepare(operation):
raise ExecError(self.qry.lastError().databaseText())
for i in range(len(parameters)):
self.qry.bindValue(i, parameters[i])
if not self.qry.exec_():
raise ExecError(self.qry.lastError().databaseText())
self.rowcount = self.qry.size()
self.description = []
for c in range(self.qry.record().count()):
f = self.qry.record().field(c)
if f.type() == QVariant.Date:
t = Date
elif f.type() == QVariant.Time:
t = Time
elif f.type() == QVariant.DateTime:
t = Timestamp
elif f.type() == QVariant.Double:
t = float
elif f.type() == QVariant.Int:
t = int
elif f.type() == QVariant.String:
t = unicode
elif f.type() == QVariant.ByteArray:
t = unicode
else:
continue
self.description.append([
f.name(), # name
t, # type_code
f.length(), # display_size
f.length(), # internal_size
f.precision(), # precision
None, # scale
f.requiredStatus() != QSqlField.Required # null_ok
])
def executemany(self, operation, seq_of_parameters):
if len(seq_of_parameters) == 0:
return
if not self.qry.prepare(operation):
raise ExecError(self.qry.lastError().databaseText())
for r in seq_of_parameters:
for i in range(len(r)):
self.qry.bindValue(i, r[i])
if not self.qry.exec_():
raise ExecError(self.qry.lastError().databaseText())
def scroll(self, row):
return self.qry.seek(row)
def fetchone(self):
if not self.qry.next():
return None
row = []
for i in range(len(self.description)):
value = self.qry.value(i)
if (isinstance(value, QDate)
or isinstance(value, QTime)
or isinstance(value, QDateTime)):
value = value.toString()
elif isinstance(value, QByteArray):
value = u"GEOMETRY"
# value = value.toHex()
row.append(value)
return row
def fetchmany(self, size=10):
rows = []
while len(rows) < size:
row = self.fetchone()
if row is None:
break
rows.append(row)
return rows
def fetchall(self):
rows = []
while True:
row = self.fetchone()
if row is None:
break
rows.append(row)
return rows
def setinputsize(self, sizes):
raise ExecError("nyi")
def setoutputsize(self, size, column=None):
raise ExecError("nyi")
def newDocument( self ):
"""
cargar todos los modelos para la edición
"""
query = QSqlQuery()
try:
if not self.database.isOpen():
if not self.database.open():
raise UserWarning( u"No se pudo establecer la conexión con la base de datos" )
for window in self.parentWindow.findChild( QMdiArea ).subWindowList():
if window.widget():
raise UserWarning( u"Por favor cierre las otras pestañas"
+ u" de la aplicación antes de continuar"
+ " con el arqueo" )
self.editmodel = ArqueoModel( self.sesion )
self.editmodel.datetime.setDate( self.sesion.fecha )
self.editmodel.datetime.setTime( QTime.currentTime() )
self.__dolar_proxy.setSourceModel( self.editmodel )
self.__dolar_proxy.setFilterKeyColumn( MONEDA )
self.__dolar_proxy.setFilterRegExp( r"^%d$" % constantes.IDDOLARES )
self.__dolar_proxy.setDynamicSortFilter( True )
self.__cordoba_proxy.setSourceModel( self.editmodel )
self.__cordoba_proxy.setFilterKeyColumn( MONEDA )
self.__cordoba_proxy.setFilterRegExp( r"^%d$" % constantes.IDCORDOBAS )
self.__cordoba_proxy.setDynamicSortFilter( True )
self.tabledetailsC.setModel( self.__cordoba_proxy )
self.tabledetailsD.setModel( self.__dolar_proxy )
if not self.database.isOpen():
if not self.database.open():
raise UserWarning( "No se pudo conectar con la base de datos" )
#verificar si hay documentos pendientes de aprobación
q = """
SELECT
CONCAT_WS(' ', td.descripcion, d.ndocimpreso)
FROM documentos sesion
JOIN docpadrehijos dpd ON dpd.idpadre = sesion.iddocumento
JOIN documentos d ON dpd.idhijo = d.iddocumento
JOIN tiposdoc td ON td.idtipodoc = d.idtipodoc
WHERE d.idestado NOT IN ( %d,%d)
""" % ( constantes.CONFIRMADO,
constantes.ANULADO )
if not query.exec_( q ):
raise Exception( u"No se pudo ejecutar la consulta para "\
+ "determinar si existen documentos "
+ "pendientes de aprobación" )
if not query.size() == 0:
raise UserWarning( u"Existen documentos pendientes de "\
+ "aprobación en la sesión" )
#Obtener los datos de la sesión
q = """
CALL spConsecutivo( %d, NULL )
""" % constantes.IDARQUEO
#query.prepare( q )
if not query.exec_( q ):
raise Exception( u"No se pudo ejecutar la consulta para "\
+ "obtener el numero del arqueo" )
if not query.size() > 0:
raise Exception( u"La consulta para obtener el numero del "\
+ "arqueo no devolvio ningún valor" )
query.first()
self.editmodel.printedDocumentNumber = query.value( 0 ).toString()
self.editmodel.exchangeRateId = self.sesion.tipoCambioId
self.editmodel.exchangeRate = self.sesion.tipoCambioOficial
self.editmodel.datetime.setDate( self.sesion.fecha )
q = """
CALL spTotalesSesion(%d);
""" % self.sesion.sesionId
if not query.exec_( q ):
raise UserWarning( u"No se pudieron calcular los totales"\
+ " de la sesión" )
while query.next():
if query.value( 0 ).toInt()[0] == constantes.IDPAGOEFECTIVO and query.value( 2 ).toInt()[0] == constantes.IDDOLARES:
self.editmodel.expectedCashD = Decimal( query.value( 5 ).toString() )
elif query.value( 0 ).toInt()[0] == constantes.IDPAGOEFECTIVO and query.value( 2 ).toInt()[0] == constantes.IDCORDOBAS:
self.editmodel.expectedCashC = Decimal( query.value( 5 ).toString() )
elif query.value( 0 ).toInt()[0] == constantes.IDPAGOCHEQUE and query.value( 2 ).toInt()[0] == constantes.IDDOLARES:
self.editmodel.expectedCkD = Decimal( query.value( 5 ).toString() )
elif query.value( 0 ).toInt()[0] == constantes.IDPAGOCHEQUE and query.value( 2 ).toInt()[0] == constantes.IDCORDOBAS:
self.editmodel.expectedCkC = Decimal( query.value( 5 ).toString() )
elif query.value( 0 ).toInt()[0] == constantes.IDPAGODEPOSITO and query.value( 2 ).toInt()[0] == constantes.IDDOLARES:
self.editmodel.expectedDepositD = Decimal( query.value( 5 ).toString() )
elif query.value( 0 ).toInt()[0] == constantes.IDPAGODEPOSITO and query.value( 2 ).toInt()[0] == constantes.IDCORDOBAS:
self.editmodel.expectedDepositC = Decimal( query.value( 5 ).toString() )
elif query.value( 0 ).toInt()[0] == constantes.IDPAGOTRANSFERENCIA and query.value( 2 ).toInt()[0] == constantes.IDDOLARES:
self.editmodel.expectedTransferD = Decimal( query.value( 5 ).toString() )
elif query.value( 0 ).toInt()[0] == constantes.IDPAGOTRANSFERENCIA and query.value( 2 ).toInt()[0] == constantes.IDCORDOBAS:
self.editmodel.expectedTransferC = Decimal( query.value( 5 ).toString() )
elif query.value( 0 ).toInt()[0] == constantes.IDPAGOTARJETA and query.value( 2 ).toInt()[0] == constantes.IDDOLARES:
self.editmodel.expectedCardD = Decimal( query.value( 5 ).toString() )
elif query.value( 0 ).toInt()[0] == constantes.IDPAGOTARJETA and query.value( 2 ).toInt()[0] == constantes.IDCORDOBAS:
self.editmodel.expectedCardC = Decimal( query.value( 5 ).toString() )
q = """
SELECT
d.iddenominacion,
CONCAT_WS( ' ',d.valor, m.moneda),
d.valor,
d.idtipomoneda,
m.simbolo
FROM denominaciones d
JOIN tiposmoneda m ON d.idtipomoneda = m.idtipomoneda
WHERE d.activo = 1
ORDER BY d.idtipomoneda, d.valor
"""
if not query.exec_( q ):
raise UserWarning( "No se pudo recuperar la lista de "
+ "denominaciones" )
denominationsmodelC = SingleSelectionModel()
denominationsmodelC.headers = ["Id",
u"Denominación",
"Valor",
"Id Moneda",
"Simbolo"]
denominationsmodelD = SingleSelectionModel()
denominationsmodelD.headers = denominationsmodelC.headers
while query.next():
if query.value( 3 ).toInt()[0] == constantes.IDDOLARES:
denominationsmodelD.items.append( [
query.value( 0 ).toInt()[0], #el id del tipo de denominacion
query.value( 1 ).toString(), #La descripción de la denominación
query.value( 2 ).toString(), # el valor de la denominación
query.value( 3 ).toInt()[0], #El id del tipo de moneda
query.value( 4 ).toString() #El simbolo de la moneda
] )
else:
denominationsmodelC.items.append( [
query.value( 0 ).toInt()[0], #el id del tipo de denominacion
query.value( 1 ).toString(), #La descripción de la denominación
query.value( 2 ).toString() , # el valor de la denominación
query.value( 3 ).toInt()[0], #El id del tipo de moneda
query.value( 4 ).toString() #El simbolo de la moneda
] )
delegateC = ArqueoDelegate( denominationsmodelC )
self.tabledetailsC.setItemDelegate( delegateC )
delegateD = ArqueoDelegate( denominationsmodelD )
self.tabledetailsD.setItemDelegate( delegateD )
self.addLine()
self.addLine()
self.editmodel.setData( self.editmodel.index( 0, MONEDA ), constantes.IDDOLARES )
self.editmodel.setData( self.editmodel.index( 1, MONEDA ), constantes.IDCORDOBAS )
self.dtPicker.setDateTime( self.editmodel.datetime )
self.lblUserName.setText( self.user.fullname )
self.editmodel.dataChanged[QModelIndex, QModelIndex].connect( self.updateLabels )
self.tabledetailsC.setColumnWidth( DENOMINACION, 200 )
self.tabledetailsD.setColumnWidth( DENOMINACION, 200 )
self.updateLabels()
self.status = False
except UserWarning as inst:
logging.error( unicode( inst ) )
logging.error( query.lastError().text() )
QMessageBox.critical( self,
qApp.organizationName(),
unicode( inst ) )
self.status = True
except Exception as inst:
logging.critical( unicode( inst ) )
logging.critical( query.lastError().text() )
QMessageBox.critical( self,
qApp.organizationName(),
"El sistema no pudo iniciar un nuevo arqueo" )
self.status = True
finally:
if self.database.isOpen():
self.database.close()
def load_data(self, default=True, settings=dict()):
'''
Загружает данные из БД по параметрам из settings полученным из окна настроек @see SettingsWindow
или использует параметры по умолчанию
'''
if default:
db = QSqlDatabase.addDatabase('QMYSQL')
db.setHostName('localhost')
db.setDatabaseName('vistamed_test')
db.setUserName('root')
db.setPassword('hero123001')
else:
db = QSqlDatabase.addDatabase('QMYSQL')
db.setHostName(settings['Host Name'])
db.setDatabaseName(settings['DataBase name'])
db.setUserName(settings['User Name'])
db.setPassword(settings['Password'])
ok = db.open()
try:
if (ok):
print('Connected')
else:
print('Connection data is incorrect')
raise Exception()
except Exception:
db.close()
QSqlDatabase.removeDatabase("qt_sql_default_connection")
return
while (self.rowCount() > 0):
self.removeRow(0)
'''
Один запрос на все данные
- ФИО
- дата рождения + возраст
- пол
- информация о действующем полисе ОМС при наличии
- информация о действующем документе (паспорт, свидетельство о рождении и т.д.)
'''
query_client = QSqlQuery(
'select C.firstName, C.lastName, C.patrName, C.sex, C.birthDate, CP.serial, CP.number, CP.endDate, CD.serial, CD.number, CD.date from Client C left\
join ClientPolicy CP on C.id = CP.client_id left join ClientDocument CD on CD.client_id = C.id;'
)
try:
if (query_client.exec_() == False):
raise Exception()
except Exception:
print('Can\'t make correct query to mysql')
db.close()
QSqlDatabase.removeDatabase("qt_sql_default_connectiont")
return
self.setColumnCount(len(self.headers))
self.setRowCount(query_client.size())
current_year = QtCore.QDate().currentDate().year()
index = 0
while (query_client.next()):
full_name = ' '.join([
query_client.value(0),
query_client.value(1),
query_client.value(2)
])
age_birth_date = ' age, '.join([str(current_year - query_client.value(4).year()), \
query_client.value(4).toString()])
sex = u'мужской'
if query_client.value(3) == 2:
sex = u'женский'
if isinstance(query_client.value(8), QtCore.QPyNullVariant):
self.setItem(index, 4, QtGui.QTableWidgetItem(u'Отсутствует'))
else:
passport_data = ' '.join([str(query_client.value(8)), str(query_client.value(9)),\
query_client.value(10).toString()])
self.setItem(index, 4, QtGui.QTableWidgetItem(passport_data))
self.setItem(index, 0, QtGui.QTableWidgetItem(full_name))
self.setItem(index, 1, QtGui.QTableWidgetItem(age_birth_date))
self.setItem(index, 2, QtGui.QTableWidgetItem(sex))
if (isinstance(query_client.value(5), QtCore.QPyNullVariant)):
self.setItem(index, 3, QtGui.QTableWidgetItem(u'Отсутствует'))
else:
policy_data = ' '.join([query_client.value(5), query_client.value(6), \
query_client.value(7).toString()])
self.setItem(index, 3, QtGui.QTableWidgetItem(policy_data))
index += 1
db.close()
QSqlDatabase.removeDatabase("vistamed_test")
def newDocument(self):
"""
activar todos los controles, llenar los modelos necesarios,
crear el modelo ChequeModel
"""
self.tabWidget.setCurrentIndex(0)
query = QSqlQuery()
try:
if not self.database.isOpen():
if not self.database.open():
raise UserWarning(u"No se pudo establecer la conexión " + "con la base de datos")
# Crea modelo para edicion
self.editmodel = ChequeModel()
# Sacar valor porcentaje del IVA
query = QSqlQuery(
"""SELECT
valorcosto
FROM costosagregados c
WHERE activo=1 AND idtipocosto=%d
"""
% (constantes.IVA)
)
if not query.exec_():
raise UserWarning("No se pudo ejecutar la consulta para" + " obtener los valores de los impuestos")
elif not query.size() > 0:
raise UserWarning("No se pudieron obtener los valores" + " de los impuestos")
query.first()
self.editmodel.ivaRate = Decimal(query.value(0).toString())
self.dtPicker.setDateTime(QDateTime.currentDateTime())
self.lbltipocambio.setText(str(self.editmodel.exchangeRate))
# Crea un edit delegate para las cuentas
self.accountseditdelegate = ChequesFiltroDelegate(
QSqlQuery(
"""
SELECT c.idcuenta, c.codigo, c.descripcion
FROM cuentascontables c
JOIN cuentascontables p ON c.padre = p.idcuenta AND p.padre != 1
WHERE c.padre != 1 AND c.idcuenta != 22
"""
)
)
self.tabledetails.setItemDelegate(self.accountseditdelegate)
self.tabledetails.setModel(self.editmodel)
# Rellenar el combobox de las retenciones
self.retencionModel = QSqlQueryModel()
self.retencionModel.setQuery(
"""
SELECT
idcostoagregado,
FORMAT(valorcosto,0) as tasa
FROM costosagregados
WHERE idtipocosto IN (%d,%d) AND
activo=1
ORDER BY valorcosto desc;
"""
% (constantes.RETENCIONPROFESIONALES, constantes.RETENCIONFUENTE)
)
self.cboretencion.setModel(self.retencionModel)
self.cboretencion.setCurrentIndex(-1)
self.cboretencion.setModelColumn(1)
# Rellenar el combobox de los PROVEEDORES
self.proveedoresmodel = QSqlQueryModel()
self.proveedoresmodel.setQuery(
"""
SELECT
p.idpersona,
p.nombre,
p.activo
FROM personas p
where p.tipopersona=%d
group by p.idpersona
ORDER BY p.nombre
;
"""
% constantes.PROVEEDOR
)
self.proveedoresfiltro = QSortFilterProxyModel()
self.proveedoresfiltro.setSourceModel(self.proveedoresmodel)
self.proveedoresfiltro.setFilterKeyColumn(1)
# self.proveedoresfiltro.setFilterRegExp("0")
self.cbobeneficiario.setModel(self.proveedoresfiltro)
self.cbobeneficiario.setCurrentIndex(-1)
self.cbobeneficiario.setModelColumn(1)
completer = QCompleter()
completer.setCaseSensitivity(Qt.CaseInsensitive)
completer.setModel(self.proveedoresmodel)
completer.setCompletionColumn(1)
# Rellenar el combobox de los conceptos
self.conceptosmodel = QSqlQueryModel()
self.conceptosmodel.setQuery(
"""
SELECT idconcepto,descripcion
FROM conceptos c;
"""
)
self.cboconcepto.setModel(self.conceptosmodel)
self.cboconcepto.setCurrentIndex(-1)
self.cboconcepto.setModelColumn(1)
completer = QCompleter()
completer.setCaseSensitivity(Qt.CaseInsensitive)
completer.setModel(self.conceptosmodel)
completer.setCompletionColumn(1)
self.cuentabancaria = QSqlQueryModel()
# self.status = False
# Rellenar el combobox de las CONCEPTOS
self.cuentabancaria.setQuery(
u"""
SELECT
idcuentacontable,
cc.codigo,
CONCAT(cc.descripcion," Moneda: ",tm.moneda) as Descripción,
tm.moneda as Moneda,
tm.simbolo as simbolo,
tm.idtipomoneda as IDMONEDA
FROM cuentasbancarias c
JOIN cuentascontables cc ON cc.idcuenta=c.idcuentacontable
JOIN tiposmoneda tm ON tm.idtipomoneda=c.idtipomoneda
JOIN cuentasxdocumento cd ON cd.idcuenta=cc.idcuenta
GROUP BY cc.idcuenta;
"""
)
if self.cuentabancaria.rowCount() < 0:
QMessageBox.warning(self, qApp.organizationName(), u"Saldo insuficiente en cuentas bancarias")
line = AccountsSelectorLine()
record = self.cuentabancaria.record(self.cbocuenta.currentIndex())
line.itemId = record.value("idcuentacontable").toInt()[0]
line.code = record.value("codigo").toString()
line.name = record.value("descripcion").toString()
line.amount = Decimal(str(self.subtotal.value()))
self.editmodel.insertRow(0)
self.editmodel.lines[0] = line
self.cbocuenta.setModel(self.cuentabancaria)
self.cbocuenta.setCurrentIndex(-1)
self.cbocuenta.setModelColumn(2)
self.tabledetails.resizeColumnsToContents()
self.tabledetails.setColumnHidden(0, True)
completercuenta = QCompleter()
completercuenta.setCaseSensitivity(Qt.CaseInsensitive)
completercuenta.setModel(self.cuentabancaria)
completercuenta.setCompletionColumn(1)
self.lblretencion.setText("")
self.status = False
# self.subtotal.valueChanged[float].connect( self.updateTotals )
except UserWarning as inst:
logging.error(unicode(inst))
QMessageBox.warning(self, qApp.organizationName(), unicode(inst))
self.status = True
except Exception as inst:
QMessageBox.warning(self, qApp.organizationName(), u"No se pudo iniciar la creación " "del nuevo cheque")
logging.critical(unicode(inst))
self.status = True
finally:
if self.database.isOpen():
self.database.close()
print query1.value(0).toInt(), " ", query1.value(1).toString()
# 使用QSqlQuery查询连接2的整张表
db2 = QSqlDatabase.database("connection2")
query2 = QSqlQuery(db2)
print "connection2:"
query2.exec_("select * from student")
while (query2.next()):
print query2.value(0).toInt(), " ", query2.value(1).toString()
# 以下是在例程17-4中添加的代码
# 先判断该数据库驱动是否支持QuerySize特性，如果支持，则可以使用size()函数，
# 如果不支持，那么就使用其他方法来获取总行数
if (db2.driver().hasFeature(QSqlDriver.QuerySize)):
print "has feature: query size"
numRows = query2.size()
else:
print "no feature: query size"
query2.last()
numRows = query2.at() + 1
print "row number: %s " % numRows
# 指向索引为1的记录，即第二条记录
query2.seek(1)
# 返回当前索引值
print "current index: %s " % query2.at()
# 获取当前行的记录
record = query2.record()
# 获取记录中“id”和“name”两个属性的值
id = record.value("id").toInt()
def newDocument( self ):
"""
Slot documentation goes here.
"""
query = QSqlQuery()
try:
if not self.database.isOpen():
if not self.database.open():
raise UserWarning( u"No se pudo establecer una conexión "
+ "con la base de datos" )
query.prepare( """
SELECT idtc
FROM tiposcambio
LIMIT 1
""" )
query.exec_()
if not query.first():
raise UserWarning( u"No existen tipos de cambio en "
+ "la base de datos" )
self.editmodel = LiquidacionModel( self.user.uid )
self.editmodel.applyISO = self.ckISO.isChecked()
self.addLine()
query.prepare( """
SELECT
c.idcostoagregado,
valorcosto,
factorpeso,
idtipocosto
FROM costosagregados c
LEFT JOIN tsim t ON c.idcostoagregado=t.idtsim
WHERE activo=1 AND idtipocosto IN (%d,%d,%d,%d)
LIMIT 4
""" % ( constantes.IVA,
constantes.SPE,
constantes.TSIM,
constantes.ISO ) )
if not query.exec_():
raise UserWarning( "No se pudo ejecutar la consulta para "
+ "obtener los valores de los impuestos" )
elif not query.size() == 4:
raise UserWarning( "No se pudieron obtener los valores "
+ "de los impuestos" )
#TODO: Deberian acaso los valores de iva, spe, tsim, iso cambiar
#cuando cambie la fecha???
while query.next():
if query.value( 3 ).toInt()[0] == 1: #IVA
self.editmodel.ivaId = query.value( 0 ).toInt()[0]
self.editmodel.ivaRate = Decimal( query.value( 1 ).toString() )
elif query.value( 3 ).toInt()[0] == 4: #SPE
self.editmodel.speId = query.value( 0 ).toInt()[0]
self.editmodel.speTotal = Decimal( query.value( 1 ).toString() )
elif query.value( 3 ).toInt()[0] == 5: #TSIM
self.editmodel.tsimId = query.value( 0 ).toInt()[0]
self.editmodel.tsimRate = Decimal( query.value( 1 ).toString() )
self.editmodel.weightFactor = Decimal( query.value( 2 ).toString() )
elif query.value( 3 ).toInt()[0] == 6: #ISO
self.editmodel.isoId = query.value( 0 ).toInt()[0]
self.editmodel.isoRate = Decimal( query.value( 1 ).toString() )
providersModel = QSqlQueryModel()
providersModel.setQuery( """
SELECT
idpersona,
nombre
FROM personas p
WHERE tipopersona = %d AND activo = 1
""" % constantes.PROVEEDOR )
if not providersModel.rowCount() > 0:
raise UserWarning( "No existen proveedores en el sistema" )
self.cbProvider.setModel( providersModel )
self.cbProvider.setModelColumn( 1 )
warehouseModel = QSqlQueryModel()
warehouseModel.setQuery( """
SELECT
idbodega,
nombrebodega
FROM bodegas b
ORDER BY idbodega
""" )
if not warehouseModel.rowCount() > 0:
raise UserWarning( "No existen bodegas en el sistema" )
self.cbWarehouse.setModel( warehouseModel )
self.cbWarehouse.setModelColumn( 1 )
self.editdelegate = LiquidacionDelegate()
self.updateArticleList( query )
if self.editdelegate.prods.rowCount() == 0:
raise UserWarning( u"El sistema no tiene registrado ningún "
+ u"tipo de articulo, por favor añada "
+ u"articulos antes de hacer una "
+ u"liquidación" )
self.tabnavigation.setEnabled( False )
self.tabWidget.setCurrentIndex( 0 )
self.tabledetails.setModel( self.editmodel )
self.tabledetails.setItemDelegate( self.editdelegate )
self.tabledetails.setEditTriggers( QAbstractItemView.EditKeyPressed
| QAbstractItemView.AnyKeyPressed
| QAbstractItemView.DoubleClicked )
self.accountseditdelegate = AccountsSelectorDelegate(
QSqlQuery( """
SELECT
c.idcuenta,
c.codigo,
c.descripcion
FROM cuentascontables c
JOIN cuentascontables p ON c.padre = p.idcuenta AND p.padre != 1
WHERE c.padre != 1 AND c.idcuenta != %s
""" % movimientos.INVENTARIO ), True )
self.dtPicker.setDateTime( QDateTime.currentDateTime() )
self.dtPicker.setMaximumDateTime( QDateTime.currentDateTime() )
self.tabletotals.setModel( self.editmodel.totalsModel )
self.tabledetails.setColumnHidden( IDDOCUMENTOT, False )
self.tableaccounts.setModel( None )
self.tabledetails.setColumnWidth( DESCRIPCION, 250 )
self.status = 2
except UserWarning as inst:
self.status = 1
QMessageBox.critical( self,
qApp.organizationName(), unicode( inst ) )
logging.error( inst )
except Exception as inst:
QMessageBox.critical( self, qApp.organizationName(),
u"Hubo un error al intentar iniciar "
+ u"una nueva liquidación" )
self.status = 1
logging.critical( inst )
finally:
if self.database.isOpen():
self.database.close()
def updateEditModels(self):
"""
Este metodo actualiza los modelos usados en el modo edición
"""
resultado = False
try:
if not self.database.isOpen():
if not self.database.open():
raise UserWarning( u"No se pudo abrir la conexión "\
+ "con la base de datos" )
self.clientesModel.setQuery("""
SELECT idpersona , nombre AS cliente
FROM personas
WHERE escliente = 1
""")
self.cbcliente.setModel(self.clientesModel)
self.cbcliente.setModelColumn(1)
self.clienteCompleter.setCaseSensitivity(Qt.CaseInsensitive)
self.clienteCompleter.setModel(self.clientesModel)
self.clienteCompleter.setCompletionColumn(1)
self.cbcliente.setCompleter(self.clienteCompleter)
self.editmodel = FacturaModel()
# Cargar el numero de la factura actual
query = QSqlQuery("""
SELECT MAX(CAST( IFNULL(referencia,0) AS SIGNED)) FROM documentos d WHERE idtipodoc =%d;
""" % constantes.IDFACTURA)
if not query.exec_():
raise Exception("No se pudo obtener el numero de la factura")
query.first()
if query.size() == 0:
n = 1
else:
n = str(int(query.value(0)) + 1)
self.editmodel.printedDocumentNumber = str(
int(query.value(0)) + 1)
self.lblnumero.setText(n)
# if self.clientesModel.rowCount() == 0:
# raise UserWarning( "No existen clientes en la"\
# + " base de datos" )
# return
self.clienteCompleter.setModel(self.clientesModel)
self.cbcliente.setModel(self.clientesModel)
self.cbcliente.setCompleter(self.clienteCompleter)
# #Crear el delegado con los articulo y verificar si existen articulos
self.existenciaModel.setQuery(
QSqlQuery("""
SELECT
categoria,
descripcion,
precio,
unidadesxcaja,
-- cajas,
100 as cajas,
idprecioproducto
FROM vw_articulos
-- WHERE existencia >0
"""))
self.categoriesview.update("""
SELECT
categoria,
descripcion,
precio,
unidadesxcaja,
-- cajas,
100 as cajas,
idprecioproducto
FROM vw_articulos
WHERE idprecioproducto IS NOT NULL
-- WHERE existencia >0
""")
self.categoriesview.expandAll()
self.categoriesview.setColumnHidden(3, True)
self.categoriesview.setColumnHidden(4, True)
self.categoriesview.setColumnWidth(0, 150)
self.categoriesview.setColumnWidth(1, 60)
self.categoriesview.setColumnWidth(2, 20)
self.proxyexistenciaModel = SingleSelectionModel()
self.proxyexistenciaModel.setSourceModel(self.existenciaModel)
# self.proxyexistenciaModel.setFilterKeyColumn( IDBODEGAEX )
if self.proxyexistenciaModel.rowCount() == 0:
raise UserWarning("No hay articulos en bodega")
delegate = FacturaDelegate(self.proxyexistenciaModel)
self.tabledetails.setItemDelegate(delegate)
self.tabledetails.setModel(self.editmodel)
self.tabledetails.setColumnHidden(0, True)
# self.editmodel.insertRow(1)
self.editmodel.dataChanged[QModelIndex,
QModelIndex].connect(self.updateLabels)
self.txtobservaciones.setPlainText("")
self.dtPicker.setDate(QDate.currentDate().addDays(1))
self.editmodel.fecha = QDate.currentDate().addDays(1)
self.cbcliente.setCurrentIndex(-1)
resultado = True
except UserWarning as inst:
logging.error(unicode(inst))
QMessageBox.critical(self, qApp.organizationName(), unicode(inst))
finally:
if self.database.isOpen():
self.database.close()
return resultado
def __init__( self, user, password ):
self.__user = user
"""
@ivar: el nombre de usuario
@type:string
"""
self.__password = password
u"""
@ivar: La contraseña
@type:string
"""
self.__roles = []
"""
@ivar: La lista de permisos de un usuario
@type: string[]
"""
self.__valid = False
"""
@ivar: si el usuario es valido o no
@type: bool
"""
self.__fullname = ""
"""
@ivar:El nombre completo de este usuario
@type: string
"""
self.__uid = 0
"""
@ivar: El id de este usuario
@type: int
"""
self.error = ""
"""
@ivar:Posibles errores
@type:string
"""
self.database = QSqlDatabase.database()
try:
if not self.database.open():
raise UserWarning( u'Existen problemas de conectividad con '\
+ 'la base de datos' )
else:
query = QSqlQuery()
if not query.prepare( """
SELECT
u.idusuario AS uid,
p.nombre,
GROUP_CONCAT(r.nombre) as roles
FROM usuarios u
JOIN personas p ON p.idpersona = u.idusuario
JOIN usuarios_has_roles ur ON u.idusuario = ur.idusuario
JOIN roles r ON r.idrol = ur.idrol
WHERE u.estado = 1
AND u.username LIKE BINARY :user
AND u.password LIKE BINARY SHA1(:password)
GROUP BY u.idusuario
LIMIT 1
""" ):
raise UserWarning( "No se pudo preparar la consulta para "\
+ "validar el usuario" )
query.bindValue( ":user", self.user )
query.bindValue( ":password", self.password + self.secret )
if not query.exec_():
raise Exception( "La consulta no se pudo ejecutar" )
if query.size() != 1:
raise UserWarning( "No se ha podido autenticar al usuario %s" % self.user )
else:
logging.info( u"El usuario %s se ha autenticado" % self.user )
query.first()
self.__valid = True
self.__uid = query.value( UID ).toInt()[0]
self.__fullname = query.value( FULLNAME ).toString()
self.__roles = query.value( ROLE ).toString().split( "," )
except UserWarning as inst:
self.error = unicode( inst )
logging.error( unicode( inst ) )
except Exception as inst:
logging.critical( unicode( inst ) )
finally:
if self.database.isOpen():
self.database.close()
class GkukanMusiumdbDockWidget(QtGui.QDockWidget, FORM_CLASS):
closingPlugin = pyqtSignal()
GKukanMusiumMessage = pyqtSignal(unicode, int)
def __init__(self, iface, land, parent=None):
"""Constructor."""
super(GkukanMusiumdbDockWidget, self).__init__(parent)
# Set up the user interface from Designer.
# After setupUI you can access any designer object by doing
# self.<objectname>, and you can use autoconnect slots - see
# http://qt-project.org/doc/qt-4.8/designer-using-a-ui-file.html
# #widgets-and-dialogs-with-auto-connect
self.setupUi(self)
self.loadListCount = 0
self.loadmax = self.tblPhotos.columnCount()
self.tblPhotos.setEditTriggers(QtGui.QAbstractItemView.NoEditTriggers)
self.iface = iface
self.land = land #Landmark toolbox
self.canvas = self.iface.mapCanvas()
self.geoCrs = QgsCoordinateReferenceSystem(4326)
self.db = QSqlDatabase.addDatabase('QPSQL')
self.photolist = []
self.filtermode = False
self.btnReloadphoto.clicked.connect(self.FilterMode)
self.btnLoadMorePhoto.clicked.connect(self.AddMorePhotosToList)
self.btnSetLandmark.clicked.connect(self.AddSelectiontoLandmark)
self.btnClearSelection.clicked.connect(self.ClearSelection)
self.btnSetToPoint.clicked.connect(self.SetPointToSelection)
self.tblPhotos.cellDoubleClicked.connect(self.clickPhoto)
self.btnLoadMorePhoto.setEnabled(False)
self.btnSetLandmark.setEnabled(False)
self.btnSetToPoint.setEnabled(False)
def closeEvent(self, event):
self.closingPlugin.emit()
event.accept()
def clickPhoto(self, r, c):
tbl = self.tblPhotos
p = tbl.item(1, c)
if hasattr(p, 'data') == False:
return
a = p.data(32)
if not a:
return
if r == 0 or (a.lon == 0 and a.lat == 0):
dlg = ViewPhotoDialog(a.filePath)
dlg.exec_()
return
point = self._transformPoint(QgsPoint(a.lon, a.lat))
self.canvas.freeze(True)
self.canvas.setCenter(point)
self.canvas.freeze(False)
self.canvas.refresh()
def SetPointToSelection(self):
pass
if self._checkLoadLayer() == False:
return
tbl = self.tblPhotos
if len(tbl.selectedItems()) == 0:
return
pl = []
for p in tbl.selectedItems():
a = p.data(32)
if a is not None:
pl.append(a)
if self.AskUpdatePoint(pl) is False:
return
if len(pl):
self.canvas.freeze(True)
self.SetPhotoPosition(pl, self.canvas.center())
self.canvas.freeze(False)
self.canvas.refresh()
i = 0
for p in tbl.selectedItems():
if p.row() == 1:
p.setBackground(QBrush(QColor('#FFFFFF')))
def AskUpdatePoint(self, pl):
i = False
for p in pl:
if p.lat:
if p.lon:
i = True
if i:
reply = QtGui.QMessageBox.question(self, u'位置情報変更の確認',
u"画像に紐づく位置情報を変更してもよろしいですか?",
QtGui.QMessageBox.Yes,
QtGui.QMessageBox.No)
if reply == QtGui.QMessageBox.Yes:
return True
else:
return False
else:
return True
def AddSelectiontoLandmark(self):
if self.loadListCount == 0:
return
tbl = self.tblPhotos
if len(tbl.selectedItems()) == 0:
return
lid = self.land.toolbox.getLandmarkID()
if lid is None:
return
pl = []
for p in tbl.selectedItems():
a = p.data(32)
if a is not None:
pl.append(a)
if self.AskUpdateLandmark(lid, pl) is False:
return
if len(pl):
if self.SetLandMarkID(lid, pl):
self.land.toolbox._highlightLandmark()
self.land.toolbox.populatePhotos()
def AskUpdateLandmark(self, lid, pl):
i = False
for p in pl:
if p.landmark_id:
if p.landmark_id <> lid:
i = True
if i:
reply = QtGui.QMessageBox.question(self, u'史料情報変更の確認',
u"画像に紐づく史料情報を変更してもよろしいですか?",
QtGui.QMessageBox.Yes,
QtGui.QMessageBox.No)
if reply == QtGui.QMessageBox.Yes:
return True
else:
return False
else:
return True
def FilterMode(self):
self.filtermode = self.FilterNoEdit.isChecked()
self.ResetTable()
self.AddPhotosToList()
self.btnLoadMorePhoto.setEnabled(True)
self.btnSetLandmark.setEnabled(True)
self.btnSetToPoint.setEnabled(True)
def ResetTable(self):
self.tblPhotos.clear()
del self.photolist[:]
self.loadListCount = 0
self.tblPhotos.setColumnCount(20)
self.tblPhotos.setBackgroundRole(QtGui.QPalette.Dark)
self.loadmax = self.tblPhotos.columnCount()
self.tblPhotos.setEditTriggers(QtGui.QAbstractItemView.NoEditTriggers)
def ScrollTableLeft(self):
tbl = self.tblPhotos
tbl.setColumnCount(self.loadListCount + 5)
self.LoadMorePhotoThread(5)
def LoadPhotosToList(self):
self.loadListCount = 0
self.AddPhotosToList()
def AddMorePhotosToList(self):
oldst = self.loadListCount
n = 100
QApplication.setOverrideCursor(Qt.WaitCursor)
if not self.GetPhotosList(self.loadListCount + 1,
self.loadListCount + n):
QApplication.restoreOverrideCursor()
return
tbl = self.tblPhotos
tbl.verticalHeader().resizeSection(0, 50)
for i in range(oldst, self.loadListCount):
fn = os.path.join(self.folderpath, self.photolist[i].filename)
self.photolist[i].filePath = fn
tp = os.path.join(os.path.join(self.folderpath, 'thumb'),
str(self.photolist[i].p_id) + '.png')
self.photolist[i].thumbPath = tp
item = ImgWidget(fn, tp, self)
hd = QTableWidgetItem(str(self.photolist[i].p_id))
tbl.insertColumn(i)
tbl.setHorizontalHeaderItem(i, hd)
tbl.setCellWidget(0, i, item)
b = QtGui.QTableWidgetItem(self.photolist[i].filename)
b.setData(32, self.photolist[i])
tbl.setItem(1, i, b)
if self.photolist[i].lon == 0 and self.photolist[i].lat == 0:
tbl.item(1, i).setBackground(QBrush(QColor('#CCFFFF')))
if self.photolist[i].keywords:
tbl.setItem(2, i,
QtGui.QTableWidgetItem(self.photolist[i].keywords))
QgsMessageLog.logMessage(u"LoadPhoto..." + str(i),
tag="LoadMorePhotoThread",
level=QgsMessageLog.INFO)
QApplication.restoreOverrideCursor()
def AddPhotosToList(self):
oldst = self.loadListCount
QApplication.setOverrideCursor(Qt.WaitCursor)
if not self.GetPhotosList(self.loadListCount + 1,
self.loadListCount + self.loadmax):
QApplication.restoreOverrideCursor()
return
tbl = self.tblPhotos
tbl.verticalHeader().resizeSection(0, 50)
self.ClearTableCells()
for i in range(oldst, self.loadListCount):
fn = os.path.join(self.folderpath, self.photolist[i].filename)
self.photolist[i].filePath = fn
tp = os.path.join(os.path.join(self.folderpath, 'thumb'),
str(self.photolist[i].p_id) + '.png')
self.photolist[i].thumbPath = tp
item = ImgWidget(fn, tp, self)
pos = i - oldst
hd = QTableWidgetItem(str(i + 1))
tbl.setHorizontalHeaderItem(pos, hd)
tbl.setCellWidget(0, pos, item)
b = QtGui.QTableWidgetItem(self.photolist[i].filename)
b.setData(32, self.photolist[i])
tbl.setItem(1, i, b)
if self.photolist[i].lon == 0 and self.photolist[i].lat == 0:
tbl.item(1, i).setBackground(QBrush(QColor('#CCFFFF')))
if self.photolist[i].keywords:
tbl.setItem(2, pos,
QtGui.QTableWidgetItem(self.photolist[i].keywords))
QgsMessageLog.logMessage(u"LoadPhoto..." + str(i),
tag="LoadPhotoThread",
level=QgsMessageLog.INFO)
QApplication.restoreOverrideCursor()
def ClearSelection(self):
tbl = self.tblPhotos
if len(tbl.selectedItems()) == 0:
return
tbl.selectionModel().clearSelection()
def ClearTableCells(self):
tbl = self.tblPhotos
tbl.clear()
def openDatabase(self):
if self.db.isValid():
settings = QSettings('MatsueGkukan', 'Gkukandb')
dbHostName = settings.value('hostname')
dbDatabaseName = settings.value('databasename')
dbUserName = settings.value('username')
dbPassword = settings.value('dbpassword')
self.db.setHostName(dbHostName)
self.db.setDatabaseName(dbDatabaseName)
self.db.setUserName(dbUserName)
self.db.setPassword(dbPassword)
if not self.db.open():
self.GKukanMusiumMessage.emit(
self.tr('Can not open GKukanMusium database'),
QgsMessageBar.WARNING)
return False
self.query = QSqlQuery(self.db)
return True
else:
settings = QSettings('MatsueGkukan', 'Gkukandb')
dbHostName = settings.value('hostname')
dbDatabaseName = settings.value('databasename')
dbUserName = settings.value('username')
dbPassword = settings.value('dbpassword')
self.db.removeDatabase(dbDatabaseName)
del self.db
self.db = None
self.db = QSqlDatabase.addDatabase('QPSQL')
self.db.setHostName(dbHostName)
self.db.setDatabaseName(dbDatabaseName)
self.db.setUserName(dbUserName)
self.db.setPassword(dbPassword)
if not self.db.open():
self.GKukanMusiumMessage.emit(
self.tr('Can not open GKukanMusium database'),
QgsMessageBar.WARNING)
return False
self.query = QSqlQuery(self.db)
return True
return False
def GetPhotoFolderPath(self):
if not self.openDatabase():
return False
if self.query.exec_(u'select * from m_folder'):
self.query.first()
self.folderpath = self.query.value(2)
ret = self.folderpath
else:
ret = ''
self.db.close()
return ret
def GetPhotosList(self, st, ed):
folder = self.GetPhotoFolderPath()
if not self.openDatabase():
return False
l = ed - st + 1
if self.filtermode:
sql = u'select * from (select row_number() over() as rid, * from t_photo where t_photo.lat=0 and t_photo.lon=0 order by p_id) as p where (p.rid between %s and %s ) limit %s;' % (
str(st), str(ed), str(l))
else:
sql = u'select * from (select row_number() over() as rid, * from t_photo order by p_id) as p where p.rid between %s and %s limit %s;' % (
str(st), str(ed), str(l))
if self.query.exec_(sql):
self.query.first()
for i in range(self.query.size()):
v = self.query.value
self.photolist.append(
PhotoData(v(1), v(2), v(3), v(4), v(5), v(6), v(7), v(8),
v(9), v(10), v(11), v(12), v(13), v(14), v(15),
v(16), v(17), v(18), v(19), v(20), v(21), v(22)))
self.query.next()
self.loadListCount += self.query.size()
self.query.clear()
self.db.close()
return True
else:
self.db.close()
return False
def SetLandMarkID(self, lid, pl):
if not len(pl):
return False
if not self.openDatabase():
return False
a = []
for p in pl:
a.append(p.p_id)
sp = ",".join(map(str, a))
sql = u'UPDATE t_photo SET landmark_id=%s WHERE p_id in (%s);' % (
str(lid), str(sp))
print sql
if self.query.exec_(sql):
self.query.clear()
self.db.close()
else:
self.db.close()
return False
return True
def SetPhotoPosition(self, pl, pos):
if not len(pl):
return False
a = []
for p in pl:
a.append(p.p_id)
sp = ",".join(map(str, a))
pos = self._transformMapToPoint(pos)
geom = QgsGeometry.fromPoint(pos)
x = geom.asQPointF().x()
y = geom.asQPointF().y()
gt = "'" + geom.exportToWkt() + "'"
sql = u'UPDATE t_photo SET lon=%s,lat=%s,geom=ST_GeomFromText(%s,4326) WHERE p_id in (%s);' % (
str(x), str(y), gt, str(sp))
v = self.convertGeomFromPostGis(geom)
self.updatePhotoDataXY(a, x, y, v)
if not self.openDatabase():
return False
if self.query.exec_(sql):
self.query.clear()
self.db.close()
else:
a = self.query.lastError().text()
self.db.close()
return False
return True
def updatePhotoDataXY(self, pl, x, y, v):
pass
pp = self.photolist
for i in range(len(pp)):
if pp[i].p_id in pl:
self.photolist[i].x = x
self.photolist[i].y = y
self.photolist[i].geom = v
return
def decodeBinary(self, wkb):
"""Decode the binary wkb and return as a hex string"""
value = binascii.a2b_hex(wkb)
value = value[::-1]
value = binascii.b2a_hex(value)
return value
def _checkLoadLayer(self):
pass
return True
def _transformMapToPoint(self, pnt):
crsDest = self.canvas.mapSettings().destinationCrs()
xform = QgsCoordinateTransform(crsDest, self.geoCrs)
p2 = xform.transform(pnt)
return p2
def _transformPoint(self, pnt):
crsDest = self.canvas.mapSettings().destinationCrs()
xform = QgsCoordinateTransform(self.geoCrs, crsDest)
p2 = xform.transform(pnt)
return p2
def convertGeomFromPostGis(self, geom):
if not self.openDatabase():
return None
gt = "'" + geom.exportToWkt() + "'"
sql = u'SELECT ST_GeomFromText(%s,4326);' % (gt)
if self.query.exec_(sql):
self.query.first()
v = self.query.value
self.db.close()
return v
else:
a = self.query.lastError().text()
self.db.close()
return False
def newDocument( self ):
"""
activar todos los controles, llenar los modelos necesarios,
crear el modelo EntradaCompraModel, aniadir una linea a la tabla
"""
try:
if not self.database.isOpen():
if not self.database.open():
raise UserWarning( u"No se pudo establecer la conexión "\
+ "con la base de datos" )
self.editmodel = EntradaCompraModel()
self.editmodel.uid = self.user.uid
self.tabledetails.setModel( self.editmodel )
self.tabledetails.setItemDelegate( self.delegate )
query = QSqlQuery( """
SELECT idcostoagregado, valorcosto
FROM costosagregados c
WHERE idtipocosto = %d AND activo = 1
LIMIT 1
""" % constantes.IVA )
if not query.exec_():
raise UserWarning( "No se pudo obtener el valor del IVA "\
+ "para iniciar la entrada compra" )
if not query.size() == 1:
raise UserWarning( "No se pudo obtener el valor del IVA "\
+ "para iniciar la entrada compra" )
query.first()
self.editmodel.idIVA = query.value( 0 ).toInt()[0]
self.editmodel.rateIVA = Decimal( query.value( 1 ).toString() )
self.updateEditModels()
self.rbCash.click()
self.addLine()
self.dtPicker.setDateTime( QDateTime.currentDateTime() )
self.editmodel.providerId = self.providersModel.record(
self.cbProvider.currentIndex()
).value( "idpersona" ).toInt()[0]
self.editmodel.dataChanged[QModelIndex,
QModelIndex].connect( self.updateLabels )
self.tabledetails.setColumnWidth( DESCRIPCION, 250 )
self.status = False
except UserWarning as inst:
QMessageBox.critical( self, qApp.organizationName(),
unicode( inst ) )
logging.error( unicode( inst ) )
# self.status = True
except Exception as inst:
QMessageBox.critical( self, qApp.organizationName(),
"No se pudo iniciar una nueva entrada compra" )
logging.error( unicode( inst ) )
# self.status = True
if self.database.isOpen():
self.database.close()
class QtSqlDBCursor:
def __init__(self, conn):
self.qry = QSqlQuery(conn)
self.description = None
self.rowcount = -1
self.arraysize = 1
def close(self):
self.qry.finish()
def execute(self, operation, parameters=[]):
if len(parameters) == 0:
if not self.qry.exec_(operation):
raise ExecError(self.qry.lastError().databaseText())
else:
if not self.qry.prepare(operation):
raise ExecError(self.qry.lastError().databaseText())
for i in range(len(parameters)):
self.qry.bindValue(i, parameters[i])
if not self.qry.exec_():
raise ExecError(self.qry.lastError().databaseText())
self.rowcount = self.qry.size()
self.description = []
for c in range(self.qry.record().count()):
f = self.qry.record().field(c)
if f.type() == QVariant.Date:
t = Date
elif f.type() == QVariant.Time:
t = Time
elif f.type() == QVariant.DateTime:
t = Timestamp
elif f.type() == QVariant.Double:
t = float
elif f.type() == QVariant.Int:
t = int
elif f.type() == QVariant.String:
t = unicode
elif f.type() == QVariant.ByteArray:
t = unicode
else:
continue
self.description.append([
f.name(), # name
t, # type_code
f.length(), # display_size
f.length(), # internal_size
f.precision(), # precision
None, # scale
f.requiredStatus() != QSqlField.Required # null_ok
])
def executemany(self, operation, seq_of_parameters):
if len(seq_of_parameters) == 0:
return
if not self.qry.prepare(operation):
raise ExecError(self.qry.lastError().databaseText())
for r in seq_of_parameters:
for i in range(len(r)):
self.qry.bindValue(i, r[i])
if not self.qry.exec_():
raise ExecError(self.qry.lastError().databaseText())
def scroll(self, row):
return self.qry.seek(row)
def fetchone(self):
if not self.qry.next():
return None
row = []
for i in range(len(self.description)):
value = self.qry.value(i)
if (isinstance(value, QDate) or isinstance(value, QTime)
or isinstance(value, QDateTime)):
value = value.toString()
elif isinstance(value, QByteArray):
value = u"GEOMETRY"
# value = value.toHex()
row.append(value)
return row
def fetchmany(self, size=10):
rows = []
while len(rows) < size:
row = self.fetchone()
if row is None:
break
rows.append(row)
return rows
def fetchall(self):
rows = []
while True:
row = self.fetchone()
if row is None:
break
rows.append(row)
return rows
def setinputsize(self, sizes):
raise ExecError("nyi")
def setoutputsize(self, size, column=None):
raise ExecError("nyi")
def updateEditModels( self ):
"""
Este metodo actualiza los modelos usados en el modo edición
"""
resultado = False
try:
if not self.database.isOpen():
if not self.database.open():
raise UserWarning( u"No se pudo abrir la conexión "\
+ "con la base de datos" )
self.clientesModel.setQuery( """
SELECT idpersona , nombre AS cliente
FROM personas
WHERE escliente = 1
""")
self.cbcliente.setModel( self.clientesModel )
self.cbcliente.setModelColumn( 1 )
self.clienteCompleter.setCaseSensitivity( Qt.CaseInsensitive )
self.clienteCompleter.setModel( self.clientesModel )
self.clienteCompleter.setCompletionColumn( 1 )
self.cbcliente.setCompleter( self.clienteCompleter )
self.editmodel = FacturaModel( )
# Cargar el numero de la factura actual
query = QSqlQuery( """
SELECT MAX(CAST( IFNULL(referencia,0) AS SIGNED)) FROM documentos d WHERE idtipodoc =%d;
""" % constantes.IDFACTURA )
if not query.exec_():
raise Exception( "No se pudo obtener el numero de la factura" )
query.first()
if query.size()==0:
n =1
else:
n = str(int(query.value(0)) + 1)
self.editmodel.printedDocumentNumber = str(int(query.value(0)) + 1)
self.lblnumero.setText(n)
# if self.clientesModel.rowCount() == 0:
# raise UserWarning( "No existen clientes en la"\
# + " base de datos" )
# return
self.clienteCompleter.setModel(self.clientesModel)
self.cbcliente.setModel(self.clientesModel)
self.cbcliente.setCompleter(self.clienteCompleter)
# #Crear el delegado con los articulo y verificar si existen articulos
self.existenciaModel.setQuery( QSqlQuery( """
SELECT
categoria,
descripcion,
precio,
unidadesxcaja,
-- cajas,
100 as cajas,
idprecioproducto
FROM vw_articulos
-- WHERE existencia >0
""" ) )
self.categoriesview.update("""
SELECT
categoria,
descripcion,
precio,
unidadesxcaja,
-- cajas,
100 as cajas,
idprecioproducto
FROM vw_articulos
WHERE idprecioproducto IS NOT NULL
-- WHERE existencia >0
""")
self.categoriesview.expandAll()
self.categoriesview.setColumnHidden(3,True)
self.categoriesview.setColumnHidden(4,True)
self.categoriesview.setColumnWidth(0,150)
self.categoriesview.setColumnWidth(1,60)
self.categoriesview.setColumnWidth(2,20)
self.proxyexistenciaModel = SingleSelectionModel()
self.proxyexistenciaModel.setSourceModel( self.existenciaModel )
# self.proxyexistenciaModel.setFilterKeyColumn( IDBODEGAEX )
if self.proxyexistenciaModel.rowCount() == 0:
raise UserWarning( "No hay articulos en bodega" )
delegate = FacturaDelegate( self.proxyexistenciaModel )
self.tabledetails.setItemDelegate( delegate )
self.tabledetails.setModel( self.editmodel )
self.tabledetails.setColumnHidden(0,True)
# self.editmodel.insertRow(1)
self.editmodel.dataChanged[QModelIndex, QModelIndex].connect( self.updateLabels )
self.txtobservaciones.setPlainText( "" )
self.dtPicker.setDate(QDate.currentDate().addDays(1))
self.editmodel.fecha = QDate.currentDate().addDays(1)
self.cbcliente.setCurrentIndex( -1 )
resultado = True
except UserWarning as inst:
logging.error( unicode( inst ) )
QMessageBox.critical( self, qApp.organizationName(), unicode( inst ) )
finally:
if self.database.isOpen():
self.database.close()
return resultado
def on_btnApertura_clicked( self ):
"""
Llamar a el formulario apertura para intentar crear una nueva sesión
de caja ó continuar con una sesión anterior
"""
self.datosSesion.usuarioId = self.user.uid
estado = not self.abierto
query = QSqlQuery()
if estado:
# try:
if not self.database.isOpen():
if not self.database.open():
raise UserWarning( u"No se pudo abrir la conexión con la"\
+ " base de datos" )
q = """
SELECT
apertura.iddocumento,
apertura.idtipocambio,
tc.fecha,
tc.tasa,
IFNULL(tc.tasabanco,tc.tasa) as tasabanco,
apertura.idcaja
FROM `esquipulasdb`.`documentos` apertura
JOIN tiposcambio tc ON tc.idtc = apertura.idtipocambio
JOIN personasxdocumento pd ON pd.iddocumento = apertura.iddocumento AND pd.idaccion=%d
LEFT JOIN docpadrehijos ph ON apertura.iddocumento=ph.idpadre
LEFT JOIN documentos cierre ON cierre.iddocumento = ph.idhijo AND cierre.idtipodoc=%d
WHERE apertura.idtipodoc=%d AND pd.idpersona=%d
GROUP BY apertura.iddocumento
HAVING SUM(IFNULL(cierre.idtipodoc,0)) = 0;
""" % ( constantes.AUTOR,
constantes.IDARQUEO,
constantes.IDAPERTURA,
self.datosSesion.usuarioId )
if not query.prepare( q ):
raise Exception( u"No se pudo preparar la consulta para "\
+ "recuperar la información de la sesión" )
if not query.exec_():
raise Exception( u"No se pudo ejecutar la consulta para"\
+ " recuperar la información de la sesión" )
# Si existe al menos una sesion abierta no muestra el dialogo
# de iniciar caja
if query.size() > 0:
reply = QMessageBox.question( self,
qApp.organizationName(),
u"Usted tiene una sesión de caja"\
+ " abierta. Desea continuar?",
QMessageBox.Yes, QMessageBox.No )
if reply == QMessageBox.Yes:
query.first()
self.datosSesion.usuarioId = self.user.uid
self.datosSesion.sesionId = query.value( 0 ).toInt()[0]
self.datosSesion.tipoCambioId = query.value( 1 ).toInt()[0]
self.datosSesion.fecha = query.value( 2 ).toDate()
self.datosSesion.tipoCambioOficial = Decimal ( query.value( 3 ).toString() )
self.datosSesion.tipoCambioBanco = Decimal ( query.value( 4 ).toString() )
self.datosSesion.cajaId = query.value( 5 ).toInt()[0]
if self.datosSesion.valid:
self.status = estado
logging.info( u"El usuario %s ha continuado una sesión de caja" % self.user.user )
else:
QMessageBox.critical( self,
qApp.organizationName(),
u"No fue posible abrir la "
+ "sesión anterior. Por favor"
+ " contacte al administrador"
+ " del sistema" )
logging.error( u"No se pudo continuar con la sesión"
+ " de caja del usuario" )
else:
apertura = DlgApertura( self )
if apertura.exec_() == QDialog.Accepted:
self.status = estado
if self.database.isOpen():
self.database.close()
else:
arqueo = FrmArqueo( self.datosSesion, self,True )
arqueo.show()
def newDocument( self ):
"""
Slot documentation goes here.
"""
query = QSqlQuery()
try:
if not self.database.isOpen():
if not self.database.open():
raise UserWarning( u"No se pudo establecer una conexión"\
+ " con la base de datos" )
dlgdoc = dlgSelectDoc( self.tiposdoc )
if dlgdoc.exec_() == QDialog.Accepted:
self.editmodel = KardexModel()
row = dlgdoc.tblBills.selectionModel().currentIndex().row()
self.editmodel.parentId = dlgdoc.filtermodel.index( row, 0
).data().toInt()[0]
self.editmodel.uid = self.user.uid
self.editmodel.parentPrinted = dlgdoc.filtermodel.index( row, 1
).data().toString()
self.editmodel.warehouseId = dlgdoc.filtermodel.index( row, 4
).data().toInt()[0]
self.editmodel.warehouseName = dlgdoc.filtermodel.index( row, 2
).data().toString()
self.txtDocObservation.setPlainText(
dlgdoc.filtermodel.index( row, 5 )
.data().toString() )
self.txtParentPrintedDocumentNumber.setText(
self.editmodel.parentPrinted )
self.txtWarehouse.setText( self.editmodel.warehouseName )
if not query.prepare( """
SELECT
axd.idarticulo,
vw.descripcion,
axd.unidades,
cxa.valor
FROM articulosxdocumento axd
JOIN costosarticulo cxa ON cxa.idarticulo = axd.idarticulo AND cxa.activo = 1
JOIN vw_articulosdescritos vw ON vw.idarticulo = axd.idarticulo
WHERE axd.iddocumento = %d
""" % self.editmodel.parentId ):
raise Exception( "No se pudo preparar la consulta para "\
+ "obtener las lineas del documento" )
if not query.exec_():
raise Exception( "No se pudo ejecutar la consulta para"\
+ " obtener las lineas del documento" )
if not query.size() > 0:
raise Exception( "La consulta para las lineas del "\
+ "documento no devolvio nada" )
while query.next():
linea = LineaKardex()
linea.itemId = query.value( 0 ).toInt()[0]
linea.itemDescription = query.value( 1 ).toString()
linea.numdoc = query.value( 2 ).toInt()[0]
linea.itemCost = Decimal( query.value( 3 ).toString() )
row = self.editmodel.rowCount()
self.editmodel.insertRows( row )
self.editmodel.lines[row] = linea
# Cargar el numero de kardex
query = QSqlQuery( """
CALL spConsecutivo(%d,NULL);
""" % constantes.IDKARDEX )
if not query.exec_():
raise UserWarning( u"No se pudo calcular el numero de"\
+ " la devolución" )
query.first()
self.editmodel.printedDocumentNumber = query.value( 0 ).toString()
self.txtPrintedDocumentNumber.setText(
self.editmodel.printedDocumentNumber )
self.status = False
self.tabnavigation.setEnabled( False )
self.tabWidget.setCurrentIndex( 0 )
self.tabledetails.setModel( self.editmodel )
delegate = KardexDelegate()
self.tabledetails.setItemDelegate( delegate )
self.dtPicker.setDateTime( QDateTime.currentDateTime() )
self.editmodel.dataChanged[QModelIndex, QModelIndex].connect( self.updateLabels )
self.tabledetails.resizeColumnsToContents()
except UserWarning as inst:
QMessageBox.critical( self, qApp.organizationName(),
unicode( inst ) )
logging.warning( inst )
self.cancel()
except Exception as inst:
QMessageBox.critical( self, qApp.organizationName(),
"No se pudo iniciar el documento kardex" )
logging.critical( inst )
self.cancel()
finally:
if self.database.isOpen():
self.database.close()
def updateEditModels( self ):
"""
Este metodo actualiza los modelos usados en el modo edición
"""
resultado = False
try:
if not self.database.isOpen():
if not self.database.open():
raise UserWarning( u"No se pudo abrir la conexión "\
+ "con la base de datos" )
self.editmodel = FacturaModel( self.parentWindow.datosSesion )
# Cargar el numero de la factura actual
query = QSqlQuery( """
SELECT fnConsecutivo(%d,NULL);
""" % constantes.IDFACTURA )
if not query.exec_():
raise Exception( "No se pudo obtener el numero de la factura" )
query.first()
n = query.value( 0 ).toString()
self.editmodel.printedDocumentNumber = n
self.clientesModel.setQuery( """
SELECT idpersona , nombre AS cliente
FROM personas
WHERE tipopersona = %d
""" % constantes.CLIENTE )
if self.clientesModel.rowCount() == 0:
raise UserWarning( "No existen clientes en la"\
+ " base de datos" )
return
# Rellenar el combobox de los vendedores
self.vendedoresModel.setQuery( """
SELECT
idpersona,
nombre AS Vendedor
FROM personas
WHERE tipopersona = %d
""" % constantes.VENDEDOR )
#Verificar si existen clientes
if self.vendedoresModel.rowCount() == 0:
raise UserWarning( "No existen vendedores en la "\
+ "base de datos" )
#Crear el delegado con los articulo y verificar si existen articulos
self.existenciaModel.setQuery( QSqlQuery( """
SELECT
idarticulo,
descripcion,
precio,
ROUND(costo,4) as costo,
Existencia,
idbodega
FROM vw_articulosenbodegas
WHERE existencia >0
""" ) )
self.proxyexistenciaModel = SingleSelectionModel()
self.proxyexistenciaModel.setSourceModel( self.existenciaModel )
self.proxyexistenciaModel.setFilterKeyColumn( IDBODEGAEX )
if self.proxyexistenciaModel.rowCount() == 0:
raise UserWarning( "No hay articulos en bodega" )
delegate = FacturaDelegate( self.proxyexistenciaModel )
# Rellenar el combobox de las BODEGAS
self.bodegasModel.setQuery( """
SELECT
b.idbodega,
b.nombrebodega as Bodega
FROM bodegas b
JOIN documentos d ON b.idbodega=d.idbodega
JOIN docpadrehijos ph ON ph.idpadre =d.iddocumento
JOIN documentos k ON ph.idhijo = k.iddocumento AND k.idtipodoc = %d
JOIN articulosxdocumento ad ON ad.iddocumento=d.iddocumento
GROUP BY b.idbodega
HAVING SUM(ad.unidades)>0
""" % constantes.IDKARDEX )
#Verificar si existen bodegas
if self.bodegasModel.rowCount() == 0:
raise UserWarning( "No existe ninguna bodega "\
+ "en la base de datos" )
#Verificar IVA
query = QSqlQuery( """
SELECT idcostoagregado, valorcosto
FROM costosagregados c
WHERE idtipocosto = 1 AND activo = 1
ORDER BY idtipocosto;
""" )
query.exec_()
if not query.size() == 1:
raise UserWarning( "No fue posible obtener el "\
+ "porcentaje del IVA" )
query.first()
self.editmodel.ivaId = query.value( 0 ).toInt()[0]
self.lbltasaiva.setText( ( '0' if self.editmodel.bodegaId != 1 else str( self.editmodel.ivaTasa ) ) + '%' )
self.editmodel.ivaTasa = Decimal( query.value( 1 ).toString() )
self.tabledetails.setItemDelegate( delegate )
self.cbcliente.setModel( self.clientesModel )
self.cbcliente.setCurrentIndex( -1 )
self.cbcliente.setFocus()
self.cbcliente.setModelColumn( 1 )
self.completer.setCaseSensitivity( Qt.CaseInsensitive )
self.completer.setModel( self.clientesModel )
self.completer.setCompletionColumn( 1 )
self.cbcliente.setCompleter( self.completer )
self.cbbodega.setModel( self.bodegasModel )
self.cbbodega.setCurrentIndex( -1 )
self.cbbodega.setModelColumn( 1 )
self.completerbodega = QCompleter()
self.completerbodega.setCaseSensitivity( Qt.CaseInsensitive )
self.completerbodega.setModel( self.bodegasModel )
self.completerbodega.setCompletionColumn( 1 )
self.cbbodega.setCompleter( self.completerbodega )
self.cbvendedor.setModel( self.vendedoresModel )
self.cbvendedor.setCurrentIndex( -1 )
self.cbvendedor.setModelColumn( 1 )
self.completerVendedor.setCaseSensitivity( Qt.CaseInsensitive )
self.completerVendedor.setModel( self.vendedoresModel )
self.completerVendedor.setCompletionColumn( 1 )
self.cbvendedor.setCompleter( self.completerVendedor )
self.tabledetails.setModel( self.editmodel )
self.addLine()
self.editmodel.dataChanged[QModelIndex, QModelIndex].connect( self.updateLabels )
self.rbcontado.setChecked( True )
self.txtobservaciones.setPlainText( "" )
resultado = True
except UserWarning as inst:
logging.error( unicode( inst ) )
QMessageBox.critical( self, qApp.organizationName(), unicode( inst ) )
finally:
if self.database.isOpen():
self.database.close()
return resultado
def save( self ):
query = QSqlQuery()
resultado = False
try:
if not QSqlDatabase.database().isOpen():
if not QSqlDatabase.database().open():
raise UserWarning( u"No se pudo conectar con la base de datos" )
if not QSqlDatabase.database().transaction():
raise Exception( u"No se pudo comenzar la transacción" )
fecha = self.datosSesion.fecha.toString( "yyyyMMdd" )
#extraer el tipo de cambio de acuerdo a la fecha junto con su id
query.prepare( "SELECT idtc,tasa,IFNULL(tasabanco,tasa) as tasabanco FROM tiposcambio t where fecha=DATE('" + fecha + "')" )
if not query.exec_():
raise Exception( "No existe una tasa de cambio para la fecha " + fecha )
if query.size() == 0:
self.error = u"La sesión de caja no fue abierta porque no existe un tipo de cambio para la fecha actual"
return False
query.first()
self.datosSesion.tipoCambioId = query.value( 0 ).toInt()[0]
self.datosSesion.tipoCambioOficial = Decimal( query.value( 1 ).toString() )
self.datosSesion.tipoCambioBanco = Decimal( query.value( 2 ).toString() )
query = QSqlQuery( "CALL spConsecutivo(%d,NULL);" % constantes.IDAPERTURA )
if not query.exec_():
raise Exception( "No pudo ser cargado el consecutivo del documento" )
query.first()
ndocimpreso = query.value( 0 ).toString()
query.prepare( """INSERT INTO documentos(ndocimpreso,total,fechacreacion,idtipodoc,idcaja,idtipocambio)
VALUES (:ndocimpreso,:total,:fecha,:tipodoc,:caja,:tipocambio)""" )
total = self.total
query.bindValue( ":ndocimpreso", ndocimpreso )
query.bindValue( ":total", total.to_eng_string() )
query.bindValue( ":fecha", fecha + QDateTime.currentDateTime().toString( "hhmmss" ) )
query.bindValue( ":tipodoc", constantes.IDAPERTURA )
query.bindValue( ":caja", self.datosSesion.cajaId )
query.bindValue( ":tipocambio", self.datosSesion.tipoCambioId )
if not query.exec_():
raise Exception( query.lastError().text() )
self.datosSesion.sesionId = query.lastInsertId().toInt()[0]
insertedId = str( self.datosSesion.sesionId )
if not query.prepare( "INSERT INTO personasxdocumento (idpersona,iddocumento,idaccion) VALUES" +
"(:usuario," + insertedId + ", " + str( constantes.ACCCREA ) + " ), "
"(:supervisor," + insertedId + "," + str( constantes.ACCAUTORIZA ) + " )" ):
raise Exception( query.lastError().text() )
query.bindValue( ":usuario", self.datosSesion.usuarioId )
query.bindValue ( ":supervisor", self.supervisorId )
if not query.exec_():
raise Exception( query.lastError().text() )
if not query.prepare( "INSERT INTO movimientoscaja(iddocumento,idtipomovimiento,idtipomoneda,monto) VALUES " +
"(" + insertedId + ",1,1,:totalCordoba), "
"(" + insertedId + ",1,2,:totalDolar)" ):
raise Exception( query.lastError().text() )
query.bindValue( ":totalCordoba", str( self.saldoCordoba ) )
query.bindValue ( ":totalDolar", str( self.saldoDolar ) )
if not query.exec_():
raise Exception( query.lastError().text() )
if not QSqlDatabase.database().commit():
raise Exception( "No se pudo hacer commit" )
resultado = True
except Exception as inst:
logging.critical( unicode( inst ) )
logging.critical( query.lastError().text() )
QSqlDatabase.database().rollback()
self.error = u"Hubo un error al guardar la sesión de caja en "\
+ "la base de datos"
finally:
if QSqlDatabase.database().isOpen():
QSqlDatabase.database().close()
return resultado
def updateTextBrowser(self):
# check to make sure we have a feature selected in our selectList -- note that there might be more than one feature
phoutput = ""
moistureoutput = ""
if self.phList:
# ############ EXAMPLE 1 EDITS GO HERE ####################
''' write code that will output ALL selected feature attributes for a single feature into the Text Browser'''
''' instead of using the dataProvider.select() function get the actual QgsFeature using dataProvider.featureAtId() '''
# get the feature by passing in empty Feature
request = QgsFeatureRequest(self.phList[0])
for f in self.pHLayer.getFeatures(request):
phoutput = "Soil pH Level: %s \n" % (f['pH'])
if self.MoistureList:
request = QgsFeatureRequest(self.MoistureList[0])
for f in self.MoistureLayer.getFeatures(request):
moistureoutput = "Soil Moisture Level: %s \n" % (f['moisture_level'])
output = phoutput + moistureoutput
self.dlg.setTextBrowser(output)
provider = self.pHLayer.dataProvider()
if provider.name() == 'postgres':
# get the URI containing the connection parameters
uri = QgsDataSourceURI(provider.dataSourceUri())
print uri.uri()
# create a PostgreSQL connection using QSqlDatabase
db = QSqlDatabase.addDatabase('QPSQL')
# check to see if it is valid
if db.isValid():
print "QPSQL db is valid"
# set the parameters needed for the connection
db.setHostName(uri.host())
db.setDatabaseName(uri.database())
db.setPort(int(uri.port()))
db.setUserName(uri.username())
db.setPassword(uri.password())
# open (create) the connection
if db.open():
print "Opened %s" % uri.uri()
# execute a simple query
#query = db.exec_("""select genus,species from botanical_name where genus = 'Prunus'""")
query = QSqlQuery ("""select genus,species from botanical_name where genus = 'Prunus'""")
self.dlg.tblPlants.clear()
self.dlg.tblPlants.setRowCount(query.size())
self.dlg.tblPlants.setColumnCount(query.record().count())
self.dlg.tblPlants.setHorizontalHeaderLabels(["Genus", "Species"])
self.dlg.tblPlants.setSelectionMode(QTableWidget.SingleSelection)
self.dlg.tblPlants.setSelectionBehavior(QTableWidget.SelectRows)
# loop through the result set and print the name
index=0
while query.next():
record = query.record()
self.dlg.tblPlants.setItem(index, 0, QTableWidgetItem(query.value(0)))
self.dlg.tblPlants.setItem(index, 1, QTableWidgetItem(query.value(1)))
index = index+1
# print record.field('name').value().toString()
self.dlg.tblPlants.resizeColumnsToContents()
else:
err = db.lastError()
print err.driverText()
