def SitesHaveFindSpots(db, siteNumbers):
sites = u", ".join(u"'{0}'".format(siteNumber) for siteNumber in siteNumbers)
query = QSqlQuery(db)
query.prepare(u"SELECT COUNT(*) FROM fundstelle WHERE fundortnummer IN ({0})".format(sites))
query.exec_()
query.first()
return query.value(0)
def __init__( self, parentId, parent = None ):
super( AccountsModel, self ).__init__( parent )
query = """
SELECT
cc.codigo,
cc.descripcion,
cc.esdebe,
COUNT(ch.idcuenta) nhijos,
SUM(IFNULL(monto,0)) monto,
cc.padre,
cc.idcuenta
FROM cuentascontables cc
LEFT JOIN cuentascontables ch ON cc.idcuenta = ch.padre
LEFT JOIN cuentasxdocumento cxd ON cc.idcuenta = cxd.idcuenta
WHERE cc.padre = %d
GROUP BY cc.idcuenta
""" % parentId
query = QSqlQuery( query )
query.exec_()
query.first()
self.rootItem = Account(
QModelIndex(),
parentId,
query.value( CODIGO ).toString(),
query.value( DESCRIPCION ).toString(),
Decimal( query.value( MONTO ).toString() ),
query.value( ESDEBE ).toInt()[0],
query.value( HIJOS ).toInt()[0] )
def data( self ):
data = {}
fila = self.tblCuenta.selectionModel().currentIndex().row()
fecha = self.dtPicker.date()
data['banco'] = self.filtermodel.index( fila, 0 ).data().toString()
data['id_cuenta_contable'] = self.filtermodel.index( fila, 4 ).data().toInt()[0]
data['codigo_cuenta_contable'] = self.filtermodel.index( fila, 3 ).data().toString()
data['cuenta_bancaria'] = self.filtermodel.index( fila, 5 ).data().toString()
data['fecha'] = QDate( fecha.year(), fecha.month(), fecha.daysInMonth() )
data['moneda'] = self.filtermodel.index( fila, 2 ).data().toString()
if not QSqlDatabase.database().isOpen() and not QSqlDatabase.open():
raise Exception( QSqlDatabase.lastError() )
query = QSqlQuery()
if not query.exec_( "CALL spSaldoCuenta( %d, %s )" % (
data['id_cuenta_contable'],
QDate( data['fecha'].year(), data['fecha'].month(), data['fecha'].daysInMonth() ).toString( "yyyyMMdd" )
)
):
raise Exception( query.lastError().text() )
query.first()
data['saldo_inicial_libro'] = Decimal( query.value( 0 ).toString() )
return data
def get_info(self, image_date):
query = QSqlQuery(self.db)
query.setForwardOnly(True)
query.prepare('SELECT copyright_info FROM copyright WHERE image_date = ?;')
query.addBindValue(image_date)
if not query.exec_():
self.error.emit('Error getting copyright info', query.lastError().text())
return ''
query.first()
copyright_info = query.value(0).toString()
return copyright_info
def IsFilm(db, filmNumber):
"""
Checks if filmNumber is a filmNumber in film Table
:param db: Database
:param filmNumber: 10 digit filmNumber
:return:
"""
query = QSqlQuery(db)
query.prepare(u"SELECT COUNT(*) FROM film WHERE filmnummer = '{0}'".format(filmNumber))
query.exec_()
query.first()
return query.value(0)
def countDatabaseTableRows(self, tablename):
query = QSqlQuery()
self.updateStatus("Loading database tables")
query.prepare("select count(*) from " + tablename + ";")
status = query.exec_()
if status == False:
self.updateStatus("Could not count entries in table '" + tablename + "'")
self.updateStatus("Database error message: " + query.lastError().text())
query.first()
count, ok = query.value(0).toInt()
# self.updateStatus("There are " + str(count) + " rows in the " + tablename + " table")
return count
def get_from_gaz_metadata(db, column):
"""
Get the gazetteer metadata from the database
(e.g. org name, custodian code)
:rtype: str
:return: metadata item
"""
sql = "SELECT {} FROM tblGazMetadata".format(column)
query = QSqlQuery(sql, db)
query.first()
gaz_metadata = query.value(query.record().indexOf(column))
return gaz_metadata
def on_buttonBox_accepted( self ):
"""
Agrega una apertura de caja
"""
try:
query = QSqlQuery()
query = QSqlQuery( """
SELECT
MAX(CAST(ndocimpreso AS SIGNED))+1
FROM documentos d
WHERE idtipodoc=17
;
""" )
if not query.exec_():
raise Exception( "No se puedo prepara el query del numero de cierre" )
query.first()
ndocimpreso = query.value( 0 ).toString()
if ndocimpreso == "0" :
ndocimpreso = "1"
if not query.prepare( """
INSERT INTO documentos(ndocimpreso,total,fechacreacion,
idtipodoc,idusuario,idcaja,observacion)
VALUES(:ndocimpreso,:total,:fecha,:tipodoc,
:usuario,:caja,:observacion)""" ):
raise Exception( query.lastError().text() )
query.bindValue( ":ndocimpreso", ndocimpreso )
query.bindValue( ":total", self.txtMonto.text() )
query.bindValue( ":fecha", self.dtFechaTime.dateTime().toString( "yyyyMMddhhmmss" ) )
query.bindValue( ":tipodoc", constantes.IDCIERRESESION )
query.bindValue( ":usuario", self.user.uid )
query.bindValue( ":caja", self.query.value( 1 ) )
query.bindValue( ":observacion", self.user2 )
if not query.exec_():
raise Exception( " Insert de cierre " )
idcierre = self.query.lastInsertId().toInt()[0]
if not query.prepare( """
INSERT INTO docpadrehijos(idpadre,idhijo,monto)
VALUES(:idpadre,:idhijo,:monto)""" ):
raise Exception( query.lastError().text() )
query.bindValue( ":idpadre", self.sesion )
query.bindValue( ":idhijo", idcierre )
query.bindValue( ":monto", self.txtMonto.text() )
if not query.exec_():
raise Exception( " Insert de docpadrehijos" )
self.accept()
except Exception as inst:
self.reject()
def export_meta(self):
"""
Exports Metadata records and writes them to CSV in new line
:return:
"""
if self.version == 75:
sqlgazmetadata = "SELECT * from tblGazMetadata;"
query = QSqlQuery(self.db)
query.exec_(sqlgazmetadata)
query.first()
rec = query.record()
now_format = str(datetime.datetime.now().date())
aval = [rec.indexOf("name"),
rec.indexOf("scope"),
rec.indexOf("territory"),
rec.indexOf("owner"),
rec.indexOf("custodian"),
rec.indexOf("coord_sys"),
rec.indexOf("coord_units"),
rec.indexOf("metadata_date"),
rec.indexOf("class_scheme"),
rec.indexOf("code_scheme"),
rec.indexOf("current_date"),
rec.indexOf("gaz_language"),
rec.indexOf("charset"),
rec.indexOf("custodian_code")]
ameta = [29,
query.value(aval[0]),
query.value(aval[1]),
query.value(aval[2]),
query.value(aval[3]),
query.value(aval[4]),
query.value(aval[13]),
query.value(aval[5]),
query.value(aval[6]),
self.format_date(query.value(aval[7])),
query.value(aval[8]),
query.value(aval[9]),
now_format,
query.value(aval[12]),
query.value(aval[11])]
self.csv.writerow(ameta)
else:
pass
def compare_pwd(self):
"""
function that compares the current password to the one the user is trying to change
:return: bool True if password matches False if not
"""
pwd_query = QSqlQuery()
pwd_str = "SELECT userpwd FROM tblUsers WHERE username =:user"
pwd_query.prepare(pwd_str)
pwd_query.bindValue(":user", self.user, QSql.Out)
pwd_query.exec_()
pwd_query.first()
old_pwd = pwd_query.value(0)
if self.old_input_pwd != old_pwd:
return False
else:
return True
def populate_length_lineedit(self, mcl_ref):
"""
Calculate the length of the MCL and populate lineedit with data.
:param mcl_ref: int, id of the MCL to calculate
"""
# Don't do calculation if spatialite version is too low. (libgeos bug)
if lor.get_spatialite_version_as_int(self.db) < 430:
length_text = "Spatialite < 4.3.0"
self.dlg.ui.lengthLineEdit.setText(length_text)
return
# Run query
sql = """
SELECT GLength(geometry) AS length FROM mcl
WHERE mcl_ref = {}
;""".format(mcl_ref)
query = QSqlQuery(sql, self.db)
# Raise exception if query fails
if not query.first():
msg = ("Could not calculate MCL length. Query:\n{}\n"
"Database returned:\n{}".format(sql,
query.lastError().text()))
raise rn_except.MclFormLengthCalculationError(msg)
# Update field
length = query.record().value('length')
length_text = "{:.2f}".format(length)
self.dlg.ui.lengthLineEdit.setText(length_text)
def headerData(self, section, orientation, role=None):
"""Generate the months on the rows and the clients on the columns"""
# pylint: disable-msg=C0103
if role not in (Qt.DisplayRole, Qt.CheckStateRole):
return QVariant()
if orientation == Qt.Horizontal:
query = QSqlQuery(
'SELECT client, machine, selldate, deltamonth, '
'anticiped FROM clients ORDER BY client, machine, '
'selldate', self._db)
if not query.exec_():
raise StandardError('SYNTAX ERROR')
if not query.first():
raise StandardError("Non c'e' manco un risultato?")
if not query.seek(section):
raise StandardError('Not enough elements into the table')
client = Client(query)
return QVariant(
'%s\n%s\n%s\nOgni %s mesi\nPagamento %scipato' %
(client.client, client.machine,
client.selldate.toString('d MMMM yyyy'), client.deltamonth,
'anti' if client.anticiped else 'posti'))
else:
return QVariant(QDate.currentDate().addMonths(
section - self.months_before).toString('MMMM yyyy'))
def save(self):
"""
Guardar el documento actual
"""
query = QSqlQuery(
"""
SELECT
cc.idcuenta,
SUM(IFNULL(monto,0)) monto
FROM cuentascontables cc
LEFT JOIN cuentascontables ch ON cc.idcuenta = ch.padre
LEFT JOIN cuentasxdocumento cxd ON cc.idcuenta = cxd.idcuenta
WHERE cc.idcuenta = %d"""
% (self.cuentabancaria.record(self.cbocuenta.currentIndex()).value("idcuentacontable").toInt()[0])
)
query.exec_()
query.first()
totalcuenta = query.value(1).toString()
if Decimal(str(self.subtotal.value())) > Decimal(totalcuenta):
QMessageBox.warning(self, qApp.organizationName(), "No existe suficiente saldo para crear" + " el cheque")
if (
QMessageBox.question(
self, qApp.organizationName(), u"¿Esta seguro que desea guardar?", QMessageBox.Yes | QMessageBox.No
)
== QMessageBox.Yes
):
if self.editmodel.valid:
if self.editmodel.save():
QMessageBox.information(self, qApp.organizationName(), u"El documento se ha guardado con éxito")
self.editmodel = None
self.updateModels()
self.navigate("last")
self.status = True
else:
QMessageBox.critical(self, qApp.organizationName(), "Ha ocurrido un error al guardar el documento")
else:
try:
QMessageBox.warning(self, qApp.organizationName(), self.editmodel.validError)
except AttributeError:
QMessageBox.warning(
self,
qApp.organizationName(),
u"El documento no puede guardarse ya " + "que la información no esta completa",
)
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 check_credentials(params, password):
"""
Check credentials against database. If correct, return 'role', else
return False.
:param params: Dictionary of parameters, including UserName and db path.
:param password: String of password
:return role / False: role (string), or false if denied.
"""
# Connect to database
if config.DEBUG_MODE:
print('DEBUG_MODE: checking credentials')
db_path = os.path.join(params['RNDataStorePath'], params['DbName'])
username = params['UserName']
db = database.connect_and_open(db_path, 'roadnet_db')
# Query database
login_query = QSqlQuery(db)
login_query.prepare("""SELECT usertype FROM tblUsers
WHERE username =:usr AND userpwd =:pwd""")
login_query.bindValue(":usr", username, QSql.Out)
login_query.bindValue(":pwd", password, QSql.Out)
executed = login_query.exec_()
if not executed:
raise Exception('Database query failed.')
if login_query.first() is True: # i.e. matching record returned
# Correct username or password: get role
role = login_query.value(0)
if role == 'admin':
role = 'editor' # Admin role is no longer used
else:
# Set debug mode settings to thinkwhere and editor and remove lock
if config.DEBUG_MODE:
params['UserName'] = '******'
params['role'] = 'editor'
role = 'editor'
lock_file_path = os.path.join(params['RNDataStorePath'], 'RNLock')
if os.path.isfile(lock_file_path):
os.remove(lock_file_path)
else:
# Wrong username or password: warning message
wrong_login_msg = QMessageBox(QMessageBox.Warning, " ",
"Incorrect username or password",
QMessageBox.Ok, None)
wrong_login_msg.setWindowFlags(Qt.CustomizeWindowHint
| Qt.WindowTitleHint)
wrong_login_msg.exec_()
role = 'init'
# Close database
del (login_query)
connection_name = db.connectionName()
db.close()
del (db)
QSqlDatabase.removeDatabase(connection_name)
if config.DEBUG_MODE:
print('DEBUG_MODE: closing QSqlDatabase {}'.format(connection_name))
# Return role or None
return role
def disable_close(self, usrn):
"""
disables the delete button
in case a street is linked to other categories
:param usrn : [str] the usrn of the current record in widget mapper
"""
usrn_num_str = "SELECT (SELECT COUNT (usrn) FROM tblSPEC_DES WHERE usrn = {} AND currency_flag = 0) + " \
"(SELECT COUNT (usrn) FROM tblMAINT WHERE usrn = {} AND currency_flag = 0) + " \
"(SELECT COUNT (usrn) FROM tblREINS_CAT WHERE usrn = {} AND currency_flag = 0) AS NoUSRN"\
.format(usrn, usrn, usrn)
usrn_num_query = QSqlQuery(usrn_num_str, self.db)
usrn_num_query.first()
linked_usrn_num = usrn_num_query.value(0)
if linked_usrn_num > 0 or self.params['role'] == 'readonly':
self.street_browser.ui.closeOpPushButton.setEnabled(False)
else:
self.street_browser.ui.closeOpPushButton.setEnabled(True)
def on_cbocuenta_currentIndexChanged(self, index):
try:
if not self.database.isOpen():
if not self.database.open():
raise UserWarning("Hubo un error al conectarse con la base de datos")
self.editmodel.setData(
self.editmodel.index(0, 2),
[
self.cuentabancaria.record(index).value("idcuentacontable").toInt()[0],
self.cuentabancaria.record(index).value("codigo").toString(),
self.cuentabancaria.record(index).value("descripcion").toString(),
],
)
self.accountseditdelegate.accounts.setFilterRegExp(
"[^%d]" % self.cuentabancaria.record(index).value("idcuentacontable").toInt()[0]
)
self.editmodel.moneda = self.cuentabancaria.record(index).value("IDMONEDA").toInt()[0]
self.editmodel.simbolo = self.cuentabancaria.record(index).value("simbolo").toString()
# Cargar el numero del cheque actual
if index > -1:
query = QSqlQuery(
"""
CALL spConsecutivo(12,"""
+ self.cuentabancaria.record(index).value("idcuentacontable").toString()
+ ")"
)
if not query.exec_():
raise UserWarning("No se pudo obtener el numero consecutivo del cheque")
query.first()
n = query.value(0).toString()
self.lblncheque.setText(n)
self.editmodel.printedDocumentNumber = n
self.editmodel.setData(self.editmodel.index(0, 3), self.editmodel.totalCordobas)
self.updateTotals()
except UserWarning as inst:
logging.error(inst)
QMessageBox.critical(self, qApp.organizationName(), unicode(inst))
except Exception as inst:
logging.critical(inst)
def columnCount(self, index=None):
"""Tell the view how many machines (columns) the model have"""
# pylint: disable-msg=C0103
assert not index.isValid()
query = QSqlQuery('SELECT COUNT(*) FROM clients', self._db)
if not query.exec_():
raise StandardError('SYNTAX ERROR')
if not query.first():
raise StandardError('No table found (or no elements)')
return query.value(0).toInt()[0]
def cargarNumeros( self, recibo ):
# Cargar el numero de el Recibo actual
idrec = str( constantes.IDRECIBO )
idret = str( constantes.IDRETENCION )
query = QSqlQuery( "CALL spConsecutivo(" + idrec + ",null)" )
if not query.exec_():
print( query.lastError().text() )
query.first()
n = query.value( 0 ).toString()
recibo.lblnrec.setText( n )
self.printedDocumentNumber = n
query = QSqlQuery( "CALL spConsecutivo(" + idret + ",null)" )
if not query.exec_():
print( query.lastError().text() )
query.first()
n = query.value( 0 ).toString()
recibo.lblnreten.setText( n )
self.retencionNumeroImpreso = n
def get_mcl_cref(self, rd_pol_id):
"""
Get the MCL ref attached to given polygon
:param rd_pol_id:
:return: str, mcl_cref
"""
sql = """
SELECT mcl_cref FROM rdpoly
WHERE rd_pol_id = '{}'
;""".format(rd_pol_id)
query = QSqlQuery(sql, self.db)
if not query.isActive():
msg = "Invalid rd_pol_id:"
msg += "\n\nSQL command:\n\n{}".format(sql)
msg += "\n\nDatabase reply:\n\n{}".format(query.lastError().text())
raise rn_except.RampRdPolyUpdateFailedPopupError(msg)
query.first()
mcl_ref = str(query.record().value('mcl_cref'))
return mcl_ref
def read(self, abfrage):
sql = abfrage
query = QSqlQuery(sql,self.db)
datensatz = {}
i = 0
while i < query.record().count():
result = []
query.first()
result.append(self.__pystring(query.value(i)))
lastError = query.lastError().text()
if len(lastError) > 1:
QMessageBox.information(None, self.tr('DB-Error'), lastError)
while query.next():
result.append(self.__pystring(query.value(i)))
lastError = query.lastError().text()
if len(lastError) > 1:
QMessageBox.information(None, self.tr('DB-Error'), lastError)
datensatz.update({str(query.record().fieldName(i)).upper(): result})
i = i + 1
return datensatz
def get_mcl_attrs_for_rdpoly(self, mcl_ref):
"""
Get values from database and prepare attributes to insert into rdpoly
table.
:param mcl_ref: str, mcl_ref
:return: dict, mcl_attributes
"""
sql = """
SELECT lor_ref_1 || "/" || lor_ref_2 AS part_label
FROM mcl WHERE mcl_ref={};""".format(mcl_ref)
query = QSqlQuery(sql, self.db)
if not query.isActive():
msg = "Failed to get MCL attributes."
msg += "\n\nSQL command:\n\n{}".format(sql)
msg += "\n\nDatabase reply:\n\n{}".format(query.lastError().text())
raise rn_except.RampRdPolyUpdateFailedPopupError(msg)
query.first()
part_label = query.record().value("part_label")
mcl_attrs = {'mcl_cref': mcl_ref, 'part_label': part_label}
return mcl_attrs
def 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()
def data(self, index, role=None):
"""return a QVariant saying if exists a payment at index.(row|column)"""
if not index.isValid() or role not in (Qt.DisplayRole,
Qt.CheckStateRole):
return QVariant()
#self.update_db_content()
# find the month from the row number
month_year = QDate().fromString(
self.headerData(index.row(), Qt.Vertical, role).toString(),
'MMMM yyyy')
month = month_year.month()
year = month_year.year()
# find the client from the column number
header_infos = self.headerData(index.column(), Qt.Horizontal,
role).toString().split('\n')
client = header_infos[0]
machine = header_infos[1]
selldate = QDate.fromString(header_infos[2], 'd MMMM yyyy')
deltamonth = int(header_infos[3][5:-5]) # [len('Ogni '):-len(' mesi')]
anticiped = header_infos[4][10:-6] == 'anti' # 'Pagamento ':-'cipato'
query = QSqlQuery(
'SELECT expected_datepayd, effective_datepayd FROM '
'payments WHERE clients_client = :client AND clients_machine = '
':machine AND clients_selldate = :selldate AND '
'expected_datepayd BETWEEN :datebefore AND :dateafter', self._db)
query.bindValue(':client', QVariant(client))
query.bindValue(':machine', QVariant(machine))
query.bindValue(':selldate', QVariant(selldate))
# primo giorno del mese
d = QDate(year, month, 1)
query.bindValue(':datebefore', QVariant(d))
# ultimo giorno del mese
query.bindValue(':dateafter', QVariant(d.addMonths(1).addDays(-1)))
if not query.exec_():
raise StandardError('SYNTAX ERROR')
if not query.first():
return QVariant()
expected_datepayd = query.value(0).toDate()
payed = not query.isNull(1)
effective_datepayd = query.value(1).toDate()
if role == Qt.CheckStateRole:
return QVariant(Qt.Checked if payed else Qt.Unchecked)
else: # DisplayRole
date = effective_datepayd if payed else expected_datepayd
return QVariant(date.toString('d MMMM yyyy'))
def update_db_content(self):
"""Populate the payment table ensuring all "visible" data"""
today = QDate.currentDate()
first_month_day = QDate(today.year(), today.month(), 1)
datebefore = first_month_day.addMonths(-self.months_before)
dateafter = first_month_day.addMonths(self.months_after)
query = QSqlQuery(
'SELECT client, machine, selldate, deltamonth, '
'anticiped FROM clients', self._db)
if not query.exec_():
raise StandardError('SYNTAX ERROR')
while query.next():
client = Client(query)
payments_date = client.selldate.addMonths(
0 if client.anticiped else client.deltamonth)
while payments_date < datebefore: # ignora date non visibili
payments_date = payments_date.addMonths(client.deltamonth)
while payments_date < dateafter:
query2 = QSqlQuery(
'SELECT effective_datepayd FROM payments '
'WHERE clients_client = :client AND clients_machine = '
':machine AND clients_selldate = :selldate AND '
'expected_datepayd = :expected_datepayd', self._db)
query2.bindValue(':client', QVariant(client.client))
query2.bindValue(':machine', QVariant(client.machine))
query2.bindValue(':selldate', QVariant(client.selldate))
query2.bindValue(':expected_datepayd', QVariant(payments_date))
if not query2.exec_():
raise StandardError('SYNTAX ERROR')
if not query2.first():
query3 = QSqlQuery(
'INSERT INTO payments (clients_client, '
'clients_machine, clients_selldate, '
'expected_datepayd) VALUES (:client, :machine, '
':selldate, :expected_datepayd)', self._db)
query3.bindValue(':client', QVariant(client.client))
query3.bindValue(':machine', QVariant(client.machine))
query3.bindValue(':selldate', QVariant(client.selldate))
query3.bindValue(':expected_datepayd',
QVariant(payments_date))
if not query3.exec_():
raise StandardError('SYNTAX ERROR')
payments_date = payments_date.addMonths(client.deltamonth)
self.emit(SIGNAL("layoutChanged()"))
def getPatientInfo(self):
qrySelect = QSqlQuery(QtGui.qApp.db.db)
sqlText = 'SELECT lastName, firstName, patrName, birthDate FROM Client WHERE id = {0}'.format(
self.clientId)
if not (qrySelect.exec_(sqlText)):
QtGui.QMessageBox.critical(self, u'Ошибка 0x001',
u'Ошибка получения данных о пациете.',
QtGui.QMessageBox.Close)
return 1
if not qrySelect.first():
QtGui.QMessageBox.critical(self, u'Ошибка 0x002',
u'Ошибка получения данных о пациете.',
QtGui.QMessageBox.Close)
return 2
self.lastName = unicode(qrySelect.value(0).toString())
self.firstName = unicode(qrySelect.value(1).toString())
self.patrName = unicode(qrySelect.value(2).toString())
self.birthDate = qrySelect.value(3).toDateTime().toPyDateTime()
return 0
def get_mcl_ref_from_rd_pol_id(self, rd_pol_id):
"""
Query database to get mcl_ref associated with given polygon
:param rd_pol_id: str, id number
:return mcl_ref: str, id number
"""
sql = """
SELECT mcl_cref FROM rdpoly
WHERE rd_pol_id = {}""".format(rd_pol_id)
query = QSqlQuery(sql, self.db)
if not query.first():
msg = "No MCLs are linked to polygon {}".format(rd_pol_id)
raise rn_except.RampNoLinkedPolyPopupError(msg)
mcl_ref = query.record().value('mcl_cref')
if isinstance(mcl_ref, QPyNullVariant):
msg = "No MCLs are linked to polygon {}".format(rd_pol_id)
raise rn_except.RampNoLinkedPolyPopupError(msg)
return str(mcl_ref)
def get_spatialite_version_as_int(db):
"""
Query the database to find spatialite version
:param db: open QSqlDatabase object
:return: Integer form of version number e.g. 411
"""
# Query the database
sql = "SELECT spatialite_version() AS version;"
query = QSqlQuery(sql, db)
query_success = query.first()
if query_success is False:
msg = "Cannot get spatialite version. Database replied: {}".format(
query.lastError().text())
raise RuntimeError(msg)
# Get the version number and convert to int
record = query.record()
version = record.value('version')
version_as_int = int(re.sub('\D', '', version))
return version_as_int
def get_road_length_from_db(sql, db):
"""
Run pre-prepared sql query on database to get length of road
:param sql: str sql selecting by road classification etc
:param db: open QSqlDatabase object
:return: float length of road
"""
query = QSqlQuery(sql, db)
query_success = query.first()
if query_success is False:
msg = ("Length of Roads calculation failed running SQL:\n{}\n"
"Database output: {}".format(sql,
query.lastError().text()))
raise rn_except.LengthOfRoadsCalculationDatabaseError(msg)
# Get result from query
record = query.record()
road_length = record.value('length')
if isinstance(road_length, PyQt4.QtCore.QPyNullVariant):
# Replace NULL from database with zero length
road_length = 0
return road_length
def SiteHasFindSpot(db, siteNumber):
query = QSqlQuery(db)
query.prepare(u"SELECT COUNT(*) FROM fundstelle WHERE fundortnummer = '{0}'".format(siteNumber))
query.exec_()
query.first()
return query.value(0)
def add_lookup(self):
"""
get the max value each table model and increment of 1
add a record to the database when the add button is pressed
with the incremented value
:return: void
"""
ui = self.lsg_lu_dia.ui
if ui.locRadioButton.isChecked():
table = self.tables[0]
ref_col = self.columns[0]
elif ui.townRadioButton.isChecked():
table = self.tables[1]
ref_col = self.columns[1]
elif ui.countyRadioButton.isChecked():
table = self.tables[2]
ref_col = self.columns[2]
new_item = ui.addLookupLineEdit.text().strip()
if new_item == "":
return
# Avoid duplicate insertion on double click on 'Add' button
selection_model = ui.itemsListView.selectionModel()
if selection_model.selectedIndexes():
current_item = selection_model.selectedIndexes()[0]
current_item_text = str(current_item.data())
if current_item_text == new_item:
dup_values_msg_box = QMessageBox(
QMessageBox.Warning, " ", "Cannot add duplicate values",
QMessageBox.Ok, None)
dup_values_msg_box.setWindowFlags(Qt.CustomizeWindowHint
| Qt.WindowTitleHint)
dup_values_msg_box.exec_()
return
# Avoid duplicate insertion by checking database
sql_find_duplicates = """SELECT name FROM {}
WHERE name IS '{}'""".format(table, new_item)
if config.DEBUG_MODE:
print(
'DEBUG_MODE: find_duplicates: {}'.format(sql_find_duplicates))
query = QSqlQuery(sql_find_duplicates, self.db)
if query.first(): # False unless value already found in table
dup_values_msg_box = QMessageBox(QMessageBox.Warning, " ",
"Cannot add duplicate values",
QMessageBox.Ok, None)
dup_values_msg_box.setWindowFlags(Qt.CustomizeWindowHint
| Qt.WindowTitleHint)
dup_values_msg_box.exec_()
return
# Get ID for new item
sql_find_max_ref = """SELECT MAX({}) AS 'max_ref'
FROM {};""".format(ref_col, table)
if config.DEBUG_MODE:
print('DEBUG_MODE: find_max_ref: {}'.format(sql_find_max_ref))
query = QSqlQuery(sql_find_max_ref, self.db)
query.first()
max_ref = query.record().value('max_ref')
if isinstance(max_ref, QPyNullVariant):
max_ref = 0
# Create the record to insert
row_count = self.items_model.rowCount()
new_record = self.items_model.record(row_count)
new_record.setValue(1, max_ref + 1)
new_record.setValue(2, new_item)
new_record.setValue(3, str(""))
self.items_model.insertRecord(row_count, new_record)
if self.items_model.submitAll(): # True if successfully updated
# clear the line input and selects the newly created value
ui.addLookupLineEdit.clear()
index = ui.itemsListView.model().createIndex(row_count, 2)
ui.itemsListView.setCurrentIndex(index)
self.changes_made = True
else:
db_error_msg_box = QMessageBox(
QMessageBox.Warning, " ",
"Error: {} ".format(self.items_model.lastError().text()),
QMessageBox.Ok, None)
db_error_msg_box.setWindowFlags(Qt.CustomizeWindowHint
| Qt.WindowTitleHint)
db_error_msg_box.exec_()
return
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 save( self ):
"""
Verdadero si el documento fue guardado
@rtype: bool
"""
query = QSqlQuery()
# try:
if not QSqlDatabase.database() .isOpen():
raise Exception( "La conexion esta cerrada, no se pueden guardar los datos del documento" )
if not QSqlDatabase.database().transaction():
raise Exception( u"No se pudo comenzar la transacción" )
query.prepare( """
SELECT fnConsecutivo(:tipodoc,:cuenta);
""")
query.bindValue( ":tipodoc", self.tipoDoc )
query.bindValue( ":cuenta", self.cuentaBancaria )
if not query.exec_():
raise Exception("No se pudo obtener el numero de deposito")
query.first()
self.numeroDoc = query.value( 0 ).toString()
if not query.prepare( """
INSERT INTO documentos (ndocimpreso,total, fechacreacion, idconcepto, idtipodoc,observacion,delbanco)
VALUES (:ndocimpreso,:total, :fechacreacion, :idconcepto,:idtipodoc,:observacion,:externo)
""" ):
raise Exception( "No se pudo preparar la consulta para guardar el documento" )
query.bindValue( ":ndocimpreso", self.numeroDoc )
query.bindValue( ":total", self.totalDoc.to_eng_string() )
query.bindValue( ":fechacreacion", self.fechaDoc.toString("yyyyMMddhhmmss"))
query.bindValue( ":idconcepto", self.conceptoId)
query.bindValue( ":idtipodoc",self.tipoDoc)
query.bindValue( ":observacion",self.observacionesDoc)
query.bindValue( ":externo",self.bancoDoc)
if not query.exec_():
raise Exception( "No se pudo ejecutar la consulta para guardar el documento" )
self.idDoc = query.lastInsertId().toString()
if not query.prepare( """
INSERT INTO personasxdocumento (idpersona, iddocumento,idaccion)
VALUES (:usuario, :documento,:accion)
""" ):
raise Exception( "No se pudo preparar la consulta para insertar el usuario" )
query.bindValue( ":usuario", self.autorId )
query.bindValue( ":documento", self.idDoc )
query.bindValue( ":accion", constantes.AUTOR )
if not query.exec_():
raise Exception( u"No se pudo guardar la relacion con el usuario" )
for nLinea, linea in enumerate( self.lineasDoc ):
if linea.valid:
linea.save( self.idDoc, nLinea + 1 )
else:
raise Exception ("Linea Invalida")
if not QSqlDatabase.database().commit():
raise Exception( "No se pudo hacer commit" )
return True
# except Exception as inst:
# logging.critical( query.lastError().text() )
# logging.critical( unicode( inst ) )
# QSqlDatabase.database().rollback()
#
# finally:
# if QSqlDatabase.database().isOpen():
# QSqlDatabase.database().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()
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 checkUser(self, email, password):
query = QSqlQuery()
query_str = "SELECT * FROM users WHERE email='{}' AND password='******'"
query.exec_(query_str.format(email, password))
return query.first()
def main(argv):
print 'I: Starting viewer ...'
app = SingletonApp(argv)
dictOpts = {
'-h': '',
'-p': '5432',
'-U': '',
'-W': '',
'-d': '',
'-s': 'public',
'-t': '',
'-g': '',
'type': 'unknown',
'srid': '',
'col': ''
}
opts, args = getopt.getopt(sys.argv[1:], 'h:p:U:W:d:s:t:g:', [])
dictOpts.update(opts)
if dictOpts['-t'] == '':
print >> sys.stderr, 'E: Table name is required'
print __doc__
sys.exit(1)
d = QSqlDatabase.addDatabase("QPSQL", "PgSQLDb")
d.setHostName(dictOpts['-h'])
d.setPort(int(dictOpts['-p']))
d.setDatabaseName(dictOpts['-d'])
d.setUserName(dictOpts['-U'])
d.setPassword(dictOpts['-W'])
if d.open():
print 'I: Database connection was succesfull'
query = QSqlQuery(d)
query.exec_(
"SELECT Count(srid) FROM raster_columns WHERE r_table_schema = '%s' AND r_table_name = '%s'"
% (dictOpts['-s'], dictOpts['-t']))
if query.next() and query.value(
0).toBool(): # Raster layer (WKTRaster)!
query.exec_("SELECT srid, r_raster_column FROM raster_columns \
WHERE r_table_schema = '%s' AND \
r_table_name = '%s' " %
(dictOpts['-s'], dictOpts['-t']))
if query.next():
dictOpts['srid'] = str(query.value(0).toString())
dictOpts['col'] = str(query.value(1).toString())
dictOpts['type'] = 'raster'
print 'I: Raster layer detected'
else: # Vector layer?
query.exec_("SELECT column_name FROM information_schema.columns \
WHERE table_schema = '%s' AND \
table_name = '%s' AND \
udt_name = 'geometry' LIMIT 1" %
(dictOpts['-s'], dictOpts['-t']))
if not query.next(): # Geography layer?
query.exec_(
"SELECT column_name FROM information_schema.columns \
WHERE table_schema = '%s' AND \
table_name = '%s' AND \
udt_name = 'geography' LIMIT 1" %
(dictOpts['-s'], dictOpts['-t']))
if query.first(): # Vector layer!
dictOpts['-g'] = str(query.value(0).toString())
query.exec_("SELECT srid FROM geometry_columns \
WHERE f_table_schema = '%s' AND \
f_table_name = '%s' " %
(dictOpts['-s'], dictOpts['-t']))
if query.next():
dictOpts['srid'] = str(query.value(0).toString())
dictOpts['type'] = 'vector'
print 'I: Vector layer detected'
if not dictOpts['type'] == 'unknown': # The object is a layer
if app.is_running:
# Application already running, send message to load data
app.send_message(dictOpts)
else:
# Start the Viewer
# QGIS libs init
QgsApplication.setPrefixPath(qgis_prefix, True)
QgsApplication.initQgis()
# Open viewer
wnd = ViewerWnd(app, dictOpts)
wnd.move(100, 100)
wnd.resize(400, 500)
wnd.show()
retval = app.exec_()
# Exit
QgsApplication.exitQgis()
print 'I: Exiting ...'
sys.exit(retval)
else:
show_error(
"Error when opening layer",
"Layer '%s.%s' doesn't exist. Be sure the selected object is either raster or vector layer."
% (dictOpts['-s'], dictOpts['-t']))
else:
show_error("Connection error", "Error when connecting to database.")
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 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 __init__( self, parent, cerrar = False ):
"""
Constructor para agregar un nuevo articulo
"""
super( DlgApertura, self ).__init__( parent )
self.parentWindow = parent
self.setupUi( self )
self.fechaApertura = None
self.capitalMaximo = Decimal(0)
self.editmodel = AperturaModel( parent.datosSesion )
self.cerrar = cerrar
self.txtUsuario.setText( parent.user.user )
self.txtUsuario.setReadOnly( True )
# self.txtUser.setFocus()
self.txtPassword.setEchoMode( QLineEdit.Password )
self.setWindowIcon( QIcon( ":/icons/res/logo.png" ) )
self.txtSaldoC.setAlignment( Qt.AlignRight )
self.txtSaldoD.setAlignment( Qt.AlignRight )
self.txtSaldoC.setPrefix("C$")
self.txtSaldoD.setPrefix("US$")
self.supervisor = None
if cerrar:
self.swcaja.setCurrentIndex( 1 )
self.txtcaja.setText( parent.cajaNombre )
self.txtcaja.setReadOnly( True )
self.editmodel.cajaId = parent.datosSesion.cajaId
else:
self.swcaja.setCurrentIndex( 0 )
self.cajasmodel = QSqlQueryModel()
try:
if not QSqlDatabase.database().isOpen():
if not QSqlDatabase.database().open():
raise UserWarning( u"No se pudo abrir la conexión "
+ "con la base de datos" )
q = """
SELECT
d.idcaja,
c.descripcion,
SUM(IF(m.idtipomoneda = %d,m.monto,0)) AS totalC,
SUM(IF(m.idtipomoneda = %d,m.monto,0)) AS totalD
FROM
movimientoscaja m
JOIN documentos d ON d.iddocumento = m.iddocumento
JOIN cajas c ON c.idcaja = d.idcaja
WHERE d.idcaja is not null AND m.idtipomovimiento=%d
GROUP BY d.idcaja;""" % ( constantes.IDCORDOBAS,
constantes.IDDOLARES, constantes.IDPAGOEFECTIVO)
self.cajasmodel.setQuery( q )
if self.cajasmodel.rowCount() == 0:
q = """
SELECT
c.idcaja,
c.descripcion,
0 AS totalC,
0 AS totalD
FROM cajas c;
"""
self.cajasmodel.setQuery( q )
# FIXME:Esto no funciona en el caso de que hallan varias cajas y solo
if self.cajasmodel.rowCount() == 0:
QMessageBox.critical( self, qApp.organizationName(),
"No existe ninguna caja en la base de datos" )
query = QSqlQuery( """
SELECT
fechacreacion
FROM esquipulasdb.documentos d
LEFT JOIN personasxdocumento pd ON pd.iddocumento = d.iddocumento
WHERE pd.idpersona = %d AND idtipodoc =%d;
""" % ( self.editmodel.datosSesion.usuarioId,
constantes.IDARQUEO ) )
query.first()
self.fechaApertura = query.value( 0 ).toDate()
except UserWarning as inst:
QMessageBox.critical( self, qApp.organizationName(),
unicode( inst ) )
logging.error( unicode( inst ) )
except Exception as inst:
logging.critical( unicode( inst ) )
finally:
if QSqlDatabase.database().isOpen():
QSqlDatabase.database().close()
self.dtFechaTime.setDisplayFormat( 'dd/MM/yyyy' )
self.dtFechaTime.setMaximumDateTime( QDateTime.currentDateTime() )
if self.fechaApertura is not None:
self.dtFechaTime.setMinimumDate( self.fechaApertura )
self.cbcaja.setModel( self.cajasmodel )
self.cbcaja.setModelColumn( 1 )
self.cbcaja.setCurrentIndex( -1 )
self.dtFechaTime.setDateTime( QDateTime.currentDateTime() )
self.buttonBox.rejected.connect( self.reject )
def remove_lookup(self):
"""
this function deletes selected items from the list view and db, only if
the lookups are not used, in this case it prevents the removal
displaying the list of the streets that use the lookup
:return: void
"""
# Setup
ui = self.lsg_lu_dia.ui
if ui.locRadioButton.isChecked():
table = self.tables[0]
ref_col = self.columns[0]
elif ui.townRadioButton.isChecked():
table = self.tables[1]
ref_col = self.columns[1]
elif ui.countyRadioButton.isChecked():
table = self.tables[2]
ref_col = self.columns[2]
# Get selected item
item_text = ui.addLookupLineEdit.text()
table_model = self.items_model
selection_model = ui.itemsListView.selectionModel()
selection_index = selection_model.selectedIndexes()
if not selection_index:
return
# if the selected value is <none> fire an alert
# (<none> cannot be either removed or modified)
selection_index = selection_index[0]
if item_text == "" and selection_index.row() == 0:
not_remove_msg_box = QMessageBox(QMessageBox.Warning, " ",
"This item cannot be removed",
QMessageBox.Ok, None)
not_remove_msg_box.setWindowFlags(Qt.CustomizeWindowHint
| Qt.WindowTitleHint)
not_remove_msg_box.exec_()
return
# Get list of USRNs attached to record; get item_ref first
item_name = selection_index.data()
sql_get_item_ref = """SELECT {0} AS 'item_ref' FROM {1}
WHERE name IS '{2}';""".format(
ref_col, table, item_name)
if config.DEBUG_MODE:
print('DEBUG_MODE: sql_get_item_ref: {}'.format(sql_get_item_ref))
query = QSqlQuery(sql_get_item_ref, self.db)
query.first()
item_ref = query.record().value('item_ref') # Reference of item
# then get USRN list.
sql_get_usrns = """SELECT usrn FROM tblSTREET
WHERE {} = {}
AND currency_flag = 0""".format(ref_col, item_ref)
query = QSqlQuery(sql_get_usrns, self.db)
usrns = []
# collects all street USRN where the item is used
while query.next():
usrns.append(str(query.value(0)))
# Cannot delete a record with attached USRNs
if len(usrns) > 0:
# Create a message
message = ("This item cannot be deleted because is used by the "
"following streets: \n")
usrns_string = ', '.join(usrns[:20])
if len(usrns) > 20:
usrns_string += ' and more...'
long_message = message + usrns_string
# Display warning message in box, then exit
item_not_deletable_msg_box = QMessageBox(QMessageBox.Warning, " ",
long_message,
QMessageBox.Ok, None)
item_not_deletable_msg_box.setWindowFlags(Qt.CustomizeWindowHint
| Qt.WindowTitleHint)
item_not_deletable_msg_box.exec_()
return
# No attached records, so delete the row
table_model.beginRemoveRows(selection_index.parent(),
selection_index.row(),
selection_index.row())
table_model.removeRow(selection_index.row())
table_model.endRemoveRows()
ui.addLookupLineEdit.clear()
# Check the delete was successful
if table_model.submitAll():
# selects the item above the removed one
index = ui.itemsListView.model().createIndex(
selection_index.row() - 1, 2)
ui.itemsListView.setCurrentIndex(index)
self.selection_handler()
self.changes_made = True
else:
db_error_msg_box = QMessageBox(
QMessageBox.Warning, " ",
"Error: {}".format(table_model.lastError.text()),
QMessageBox.Ok, None)
db_error_msg_box.setWindowFlags(Qt.CustomizeWindowHint
| Qt.WindowTitleHint)
db_error_msg_box.exec_()
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 ):
"""
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 save(self):
query = QSqlQuery()
try:
if not self.editModel.valid:
raise UserWarning("El documento no es valido, no se puede guardar")
if not self.database.isOpen():
if not self.database.open():
raise UserWarning("No se pudo conectar con la base de datos")
if not self.database.transaction():
raise Exception(u"No se pudo comenzar la transacción")
# Cargar el numero del asiento actual
query.prepare(
"""
SELECT
MAX(CAST(ndocimpreso AS SIGNED))+1
FROM documentos d
WHERE idtipodoc=24
;
"""
)
query.exec_()
query.first()
n = query.value(0).toString()
if n == "0":
n = "1"
if not query.prepare(
"""
INSERT INTO documentos (ndocimpreso, fechacreacion, idconcepto, idtipodoc)
VALUES (:ndocimpreso, :fechacreacion, :idconcepto, %d)
"""
% constantes.IDAJUSTECONTABLE
):
raise Exception("No se pudo preparar la consulta para guardar el documento")
query.bindValue(":ndocimpreso", n)
query.bindValue(":fechacreacion", self.dtPicker.dateTime().toString("yyyyMMddhhmmss"))
query.bindValue(
":idconcepto", self.conceptsmodel.record(self.cbConcepts.currentIndex()).value("idconcepto").toInt()[0]
)
if not query.exec_():
raise Exception("No se pudo ejecutar la consulta para guardar el asiento")
insertedId = query.lastInsertId().toInt()[0]
if not query.prepare(
"""
INSERT INTO personasxdocumento (idpersona, iddocumento, idaccion)
VALUES (:usuario, :documento, :idaccion)
"""
):
raise Exception("No se pudo preparar la consulta para insertar el usuario")
query.bindValue(":usuario", self.user.uid)
query.bindValue(":documento", insertedId)
query.bindValue(":idaccion", constantes.ACCCREA)
if not query.exec_():
raise Exception(u"No se pudo guardar la relación con el usuario")
for lineid, line in enumerate(self.editModel.lines):
if line.valid:
line.save(insertedId, lineid + 1)
if not self.database.commit():
raise Exception("No se pudo ejecutar la transaccion")
self.tableDetails.setModel(self.detailsproxymodel)
self.updateModels()
self.status = False
except UserWarning as inst:
QMessageBox.critical(self, qApp.organizationName(), unicode(inst))
logging.error(inst)
except Exception as inst:
QMessageBox.critical(self, qApp.organizationName(), "Hubo un error al tratar de guardar su ajuste")
self.database.rollback()
logging.critical(inst)
logging.critical(query.lastError().text())
finally:
if self.database.isOpen():
self.database.close()
def main( argv ):
print 'I: Starting viewer ...'
app = SingletonApp( argv )
dictOpts = { '-h':'', '-p':'5432', '-U':'', '-W':'', '-d':'', '-s':'public',
'-t':'', '-g':'', 'type':'unknown', 'srid':'', 'col':'' }
opts, args = getopt.getopt( sys.argv[1:], 'h:p:U:W:d:s:t:g:', [] )
dictOpts.update( opts )
if dictOpts['-t'] == '':
print >> sys.stderr, 'E: Table name is required'
print __doc__
sys.exit( 1 )
d = QSqlDatabase.addDatabase( "QPSQL", "PgSQLDb" )
d.setHostName( dictOpts['-h'] )
d.setPort( int( dictOpts['-p'] ) )
d.setDatabaseName( dictOpts['-d'] )
d.setUserName( dictOpts['-U'] )
d.setPassword( dictOpts['-W'] )
if d.open():
print 'I: Database connection was succesfull'
query = QSqlQuery( d )
query.exec_( "SELECT Count(srid) FROM raster_columns WHERE r_table_schema = '%s' AND r_table_name = '%s'" % ( dictOpts['-s'], dictOpts['-t'] ) )
if query.next() and query.value( 0 ).toBool(): # Raster layer (WKTRaster)!
query.exec_( "SELECT srid, r_raster_column FROM raster_columns \
WHERE r_table_schema = '%s' AND \
r_table_name = '%s' " % ( dictOpts['-s'], dictOpts['-t'] ) )
if query.next():
dictOpts[ 'srid' ] = str( query.value( 0 ).toString() )
dictOpts[ 'col' ] = str( query.value( 1 ).toString() )
dictOpts['type'] = 'raster'
print 'I: Raster layer detected'
else: # Vector layer?
query.exec_( "SELECT column_name FROM information_schema.columns \
WHERE table_schema = '%s' AND \
table_name = '%s' AND \
udt_name = 'geometry' LIMIT 1" % ( dictOpts['-s'], dictOpts['-t'] ) )
if not query.next(): # Geography layer?
query.exec_( "SELECT column_name FROM information_schema.columns \
WHERE table_schema = '%s' AND \
table_name = '%s' AND \
udt_name = 'geography' LIMIT 1" % ( dictOpts['-s'], dictOpts['-t'] ) )
if query.first(): # Vector layer!
dictOpts[ '-g' ] = str( query.value( 0 ).toString() )
query.exec_( "SELECT srid FROM geometry_columns \
WHERE f_table_schema = '%s' AND \
f_table_name = '%s' " % ( dictOpts['-s'], dictOpts['-t'] ) )
if query.next():
dictOpts[ 'srid' ] = str( query.value( 0 ).toString() )
dictOpts['type'] = 'vector'
print 'I: Vector layer detected'
if not dictOpts[ 'type' ] == 'unknown': # The object is a layer
if app.is_running:
# Application already running, send message to load data
app.send_message( dictOpts )
else:
# Start the Viewer
# QGIS libs init
QgsApplication.setPrefixPath(qgis_prefix, True)
QgsApplication.initQgis()
# Open viewer
wnd = ViewerWnd( app, dictOpts )
wnd.move(100,100)
wnd.resize(400, 500)
wnd.show()
retval = app.exec_()
# Exit
QgsApplication.exitQgis()
print 'I: Exiting ...'
sys.exit(retval)
else:
show_error("Error when opening layer",
"Layer '%s.%s' doesn't exist. Be sure the selected object is either raster or vector layer." % (dictOpts['-s'], dictOpts['-t']))
else:
show_error("Connection error", "Error when connecting to database.")
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 __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 LandmarkToolbox(QDockWidget, Ui_DockWidget):
landmarkMessage = pyqtSignal(unicode, int)
def __init__(self, iface):
QDockWidget.__init__(self)
self.setupUi(self)
self.iface = iface
self.canvas = self.iface.mapCanvas()
self.geoCrs = QgsCoordinateReferenceSystem(4326)
self.btnAddPhoto.setIcon(QIcon(':/icons/camera.svg'))
self.txtPhotoComment.setPlaceholderText(self.tr('Comment'))
self.cmbLayers.setFilters(QgsMapLayerProxyModel.VectorLayer)
self.db = QSqlDatabase.addDatabase('QPSQL')
self.landmarkId = None
self.photoId = None
self.highlight = None
self.model = QStandardItemModel()
self.lstPhotos.setModel(self.model)
self.btnUpdateLandmark.clicked.connect(self.saveLandmark)
self.btnDeleteLandmark.clicked.connect(self.deleteLandmark)
self.btnAddPhoto.clicked.connect(self.addPhoto)
self.btnUpdatePhoto.clicked.connect(self.savePhoto)
self.btnDeletePhoto.clicked.connect(self.removePhoto)
self.lstPhotos.selectionModel().selectionChanged.connect(
self.photoSelected)
self.lstPhotos.doubleClicked.connect(self.showPhoto)
self._enableOrDisableButtons()
self.ToggleToolbox()
def ToggleToolbox(self):
layer_list = self.canvas.layers()
if not layer_list:
self.hide()
return
elif len(layer_list) == 0:
self.hide()
return
self.setVisible(not self.isVisible())
def getLandmarkID(self):
#ランドマークがなかった時の処理
return self.landmarkId
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)
self.thumbpath = os.path.join(self.folderpath, 'thumb')
ret = self.folderpath
else:
ret = ''
self.db.close()
return ret
def landmarkSelected(self, infos):
self.info = infos[0]
ft = self.info[1]
self.landmarkId = ft['id']
self.leLandmarkTitle.setText(ft['title'] if ft['title'] else '')
self.spnLandmarkClass.setValue(
ft['icon_type'] if ft['icon_type'] != None else 0)
self._highlightLandmark()
self.populatePhotos()
self._enableOrDisableButtons()
def populatePhotos(self, index=0):
self.model.clear()
QApplication.setOverrideCursor(Qt.WaitCursor)
# photos is a list of tuples (id, title, imagepath)
photos = self._photosOfLandmark()
for i in photos:
tp = os.path.join(self.thumbpath, str(i[0])) + '.png'
img = self.thumbnailPhoto(i[2], tp)
icon = QIcon(img)
title = i[1] if i[1] else '<unnamed photo> %s' % i[0]
item = QStandardItem(title)
item.setIcon(icon)
item.setData(i[0], Qt.UserRole)
item.setToolTip(title)
self.model.appendRow(item)
lastIdx = self.model.indexFromItem(item)
idx = self.model.createIndex(0, 0)
if self.model.rowCount() > 0:
if index == -1:
idx = lastIdx
elif index > 0:
idx = self.model.createIndex(index, 0)
self.lstPhotos.selectionModel().select(idx,
QItemSelectionModel.Select)
else:
self._clearForm()
QApplication.restoreOverrideCursor()
def thumbnailPhoto(self, imagePath, tp):
if os.path.exists(tp):
return QPixmap(tp)
else:
if os.path.exists(os.path.dirname(tp)) == False:
os.mkdir(os.path.dirname(tp))
pixmap = QPixmap(imagePath).scaled(800, 600).scaled(
75, 50, Qt.IgnoreAspectRatio, Qt.SmoothTransformation)
a = pixmap.save(tp, 'PNG')
return pixmap
def showPhoto(self, index):
if not self.openDatabase():
return
item = self.lstPhotos.model().itemFromIndex(index)
self.query.prepare(
'SELECT filename ,ST_X(geom),ST_Y(geom),geom FROM t_photo WHERE p_id=?;'
)
self.query.addBindValue(item.data(Qt.UserRole))
if self.query.exec_():
self.query.first()
path = os.path.join(self.folderpath, self.query.value(0))
if self.query.value(
3) <> '010100000000000000000000000000000000000000':
lon = self.query.value(1)
lat = self.query.value(2)
point = self._transformPoint(QgsPoint(lon, lat))
self.canvas.freeze(True)
self.canvas.setCenter(point)
self.canvas.freeze(False)
self.canvas.refresh()
dlg = ViewPhotoDialog(path)
dlg.exec_()
else:
a = self.query.lastError().text()
self.db.close()
def saveLandmark(self):
layer = self.info[0]
fid = self.info[1].id()
idxTitle = layer.fieldNameIndex('title')
idxClassification = layer.fieldNameIndex('icon_type')
attrs = {idxTitle: self.leLandmarkTitle.text(),\
idxClassification: self.spnLandmarkClass.value()
}
layer.dataProvider().changeAttributeValues({fid: attrs})
layer.reload()
layer.triggerRepaint()
self.landmarkMessage.emit(self.tr('Landmark updated.'),
QgsMessageBar.INFO)
def deleteLandmark(self):
layer = self.info[0]
fid = self.info[1].id()
layer.dataProvider().deleteFeatures([fid])
layer.reload()
layer.triggerRepaint()
self._clearAllFields()
self.landmarkMessage.emit(self.tr('Landmark deleted.'),
QgsMessageBar.INFO)
def addPhoto(self):
if self.landmarkId is not None:
settings = QSettings('MatsueGkukan', 'Gkukandb')
lastDir = settings.value('lastPhotoDir', '.')
fileName = QFileDialog.getOpenFileName(self,
self.tr('Select photo'),
lastDir,
self._createFilter())
if fileName == '':
return
settings.setValue('lastPhotoDir',
QFileInfo(fileName).absoluteDir().absolutePath())
projectPath = self.GetPhotoFolderPath() + os.sep
photoPath = os.path.basename(fileName)
photoDate = self._photoDate(fileName).toString('yyyy-MM-dd')
if not self.openDatabase():
return
self.query.prepare(
'INSERT INTO t_photo("cdate", "filename", "landmark_id",lon,lat,angle,geomtype,geom) VALUES(?, ?, ?,?,?,?,?,?);'
)
self.query.addBindValue(photoDate)
self.query.addBindValue(photoPath)
self.query.addBindValue(self.landmarkId)
self.query.addBindValue(0)
self.query.addBindValue(0)
self.query.addBindValue(0)
self.query.addBindValue(0)
self.query.addBindValue(
'010100000000000000000000000000000000000000')
if self.query.exec_():
self._copyPhotoToFolder(fileName, self.landmarkId)
self.populatePhotos(-1)
else:
a = self.query.lastError().text()
self.db.close()
else:
self.landmarkMessage.emit(
self.tr('Select landmark before adding a photo.'),
QgsMessageBar.WARNING)
def savePhoto(self):
if not self.openDatabase():
return
self.query.prepare(
'UPDATE t_photo SET film_no=?, keywords=?, keyword1=?, keyword2=?, keyword3=?, notes=?, mdate=?, registrant=?, comment=?, reference=?, angle=? WHERE p_id=?;'
)
self.query.addBindValue(self.lePhotoTitle.text())
self.query.addBindValue(self.leKeywords.text())
self.query.addBindValue(self.leKeyword1.text())
self.query.addBindValue(self.leKeyword2.text())
self.query.addBindValue(self.leKeyword3.text())
self.query.addBindValue(self.txtPhotoComment.toPlainText())
self.query.addBindValue(
self.edPhotoDate.dateTime().toString('yyyy-MM-dd'))
self.query.addBindValue(self.leRegistrant.text())
self.query.addBindValue(self.leComment.text())
self.query.addBindValue(self.lerRference.text())
self.query.addBindValue(self.spnPhotoAngle.value())
self.query.addBindValue(self.photoId)
if self.query.exec_():
self.landmarkMessage.emit(self.tr('Photo updated.'),
QgsMessageBar.INFO)
self.populatePhotos(self.lstPhotos.currentIndex().row())
else:
a = self.query.lastError().text()
self.db.close()
def removePhoto(self):
if not self.openDatabase():
return
self.query.prepare('DELETE FROM t_photo WHERE "p_id"=?;')
self.query.addBindValue(self.photoId)
if self.query.exec_():
self.db.close()
self._removePhotofromFolder()
self.populatePhotos()
def photoSelected(self, current, previous):
if not self.openDatabase():
return
idx = current.indexes()[0]
item = self.lstPhotos.model().itemFromIndex(idx)
self.photoId = item.data(Qt.UserRole)
self.query.prepare(
'SELECT film_no, filename, keywords, keyword1, keyword2, keyword3, notes, mdate, registrant, comment, reference, angle FROM t_photo WHERE p_id=?;'
)
self.query.addBindValue(self.photoId)
if self.query.exec_():
self.query.first()
self.filename = self.query.value(1)
self.lePhotoTitle.setText(
self.query.value(0) if self.query.value(0) else '')
self.txtPhotoComment.setPlainText(
self.query.value(6) if self.query.value(6) else '')
self.leKeywords.setText(
self.query.value(2) if self.query.value(2) else '')
self.leKeyword1.setText(
self.query.value(3) if self.query.value(3) else '')
self.leKeyword2.setText(
self.query.value(4) if self.query.value(4) else '')
self.leKeyword3.setText(
self.query.value(5) if self.query.value(5) else '')
self.leRegistrant.setText(
self.query.value(8) if self.query.value(8) else '')
self.leComment.setText(
self.query.value(9) if self.query.value(9) else '')
self.lerRference.setText(
self.query.value(10) if self.query.value(10) else '')
self.spnPhotoAngle.setValue(
int(self.query.value(11)) if self.query.value(11) else 0)
self.edPhotoDate.setDateTime(
self.query.value(7) if self.query.value(7) else QDateTime.
currentDateTime())
self._enableOrDisableButtons()
self.db.close()
def _photosOfLandmark(self):
projectPath = self.GetPhotoFolderPath()
if not self.openDatabase():
return
photos = []
self.query.prepare(
'SELECT "p_id", "keywords", "filename" FROM t_photo WHERE "landmark_id"=? ORDER BY "p_id";'
)
self.query.addBindValue(self.landmarkId)
if self.query.exec_():
while self.query.next():
photos.append((self.query.value(0), self.query.value(1),
os.path.join(projectPath, self.query.value(2))))
self.db.close()
return photos
def _createFilter(self):
formats = ''
for f in QImageReader.supportedImageFormats():
f = unicode(f)
if f == 'svg':
continue
formats += '*.{} *.{} '.format(f.lower(), f.upper())
return self.tr('Image files (%s);;All files (*.*)' % formats[:-1])
def _clearForm(self):
self.lePhotoTitle.clear()
self.txtPhotoComment.clear()
self.leKeyword1.clear()
self.leKeyword2.clear()
self.leKeyword3.clear()
self.leRegistrant.clear()
self.leComment.clear()
self.lerRference.clear()
self.photoId = None
self.landmarkId = None
def _enableOrDisableButtons(self):
if self.landmarkId is None:
self.btnAddPhoto.setEnabled(False)
else:
self.btnAddPhoto.setEnabled(True)
if self.photoId is None:
self.btnDeletePhoto.setEnabled(False)
self.btnUpdatePhoto.setEnabled(False)
else:
self.btnDeletePhoto.setEnabled(True)
self.btnUpdatePhoto.setEnabled(True)
def _highlightLandmark(self):
self._clearHighlight()
self.highlight = QgsHighlight(self.canvas, self.info[1].geometry(),
self.info[0])
settings = QSettings()
color = QColor(
settings.value('/Map/highlight/color',
QGis.DEFAULT_HIGHLIGHT_COLOR.name()))
alpha = settings.value('/Map/highlight/colorAlpha',
QGis.DEFAULT_HIGHLIGHT_COLOR.alpha(),
type=int)
buffer = settings.value('/Map/highlight/buffer',
QGis.DEFAULT_HIGHLIGHT_BUFFER_MM,
type=float)
minWidth = settings.value('/Map/highlight/minWidth',
QGis.DEFAULT_HIGHLIGHT_MIN_WIDTH_MM,
type=float)
self.highlight.setColor(color)
color.setAlpha(alpha)
self.highlight.setFillColor(color)
self.highlight.setBuffer(buffer)
self.highlight.setMinWidth(minWidth)
self.highlight.show()
def _photoDate(self, path):
with open(path, 'rb') as imgFile:
tags = exifread.process_file(imgFile, details=False)
if 'EXIF GPS GPSDate' in tags:
return QDateTime.fromString(tags['EXIF GPS GPSDate'].values,
'yyyy:MM:dd')
else:
return QDateTime.currentDateTime()
def _clearHighlight(self):
if hasattr(self, 'highlight'):
del self.highlight
self.highlight = None
def _clearAllFields(self):
self.leLandmarkTitle.clear()
self.spnLandmarkClass.clear()
self._clearHighlight()
self._clearForm()
self.model.clear()
def _copyPhotoToFolder(self, path, landmark_id):
projectPath = self.GetPhotoFolderPath()
dst = os.path.join(projectPath, os.path.basename(path))
shutil.copy2(path, dst)
def _removePhotofromFolder(self, path, landmark_id):
projectPath = self.GetPhotoFolderPath()
dst = os.path.join(projectPath, path)
os.remove(dst)
def _transformPoint(self, pnt):
crsDest = self.canvas.mapSettings().destinationCrs()
xform = QgsCoordinateTransform(self.geoCrs, crsDest)
p2 = xform.transform(pnt)
return p2
def add_lookup(self):
"""
get the max value each table model and increment of 1
add a record to the database when the add button is pressed
with the incremented value
:return: void
"""
ui = self.srwr_lu_dia.ui
if ui.desRadioButton.isChecked():
table_id = 0
table = self.tables[table_id]
ref_col = self.columns[0]
elif ui.reinsRadioButton.isChecked():
table_id = 1
table = self.tables[table_id]
ref_col = self.columns[1]
elif ui.statRadioButton.isChecked():
table_id = 2
table = self.tables[table_id]
ref_col = self.columns[2]
# format text and numbers
add_desc = str(ui.typeDescLineEdit.text()).strip()
add_desc.replace("'", "''")
add_code = ui.typeNoSpinBox.value()
if add_desc == "" or add_code is None:
desc_error_msg_box = QMessageBox(
QMessageBox.Warning, " ",
"You must enter the code AND description", QMessageBox.Ok,
None)
desc_error_msg_box.setWindowFlags(Qt.CustomizeWindowHint
| Qt.WindowTitleHint)
desc_error_msg_box.exec_()
return
# avoid duplicate insertion on double click on 'Add' button
sel_model = ui.itemsListView.selectionModel()
if sel_model.selectedIndexes():
# extracts just the id from the string
sel_items = sel_model.selectedIndexes()[0]
item_data = str(sel_items.data())
p = re.compile("([0-9]{1,3})(?=\s:)")
src = p.search(item_data)
item_id = int(src.group(1))
if item_id == add_code:
dups_error_msg_box = QMessageBox(
QMessageBox.Warning, " ", "Cannot add duplicate values",
QMessageBox.Ok, None)
dups_error_msg_box.setWindowFlags(Qt.CustomizeWindowHint
| Qt.WindowTitleHint)
dups_error_msg_box.exec_()
return
# Avoid duplicate insertion by checking database
sql_find_duplicates = """SELECT {0} FROM {1}
WHERE {0} IS '{2}'""".format(
ref_col, table, add_code)
if config.DEBUG_MODE:
print(
'DEBUG_MODE: find_duplicates: {}'.format(sql_find_duplicates))
query = QSqlQuery(sql_find_duplicates, self.db)
if query.first(): # False unless value already found in table
dup_values_msg_box = QMessageBox(QMessageBox.Warning, " ",
"Cannot add duplicate values",
QMessageBox.Ok, None)
dup_values_msg_box.setWindowFlags(Qt.CustomizeWindowHint
| Qt.WindowTitleHint)
dup_values_msg_box.exec_()
return
# create the record to insert
data_model_idx = self.data_model.createIndex(
self.data_model.rowCount() - 1, 0)
insert_record = self.data_model.record(self.items_model.rowCount())
insert_record.setValue(1, add_code)
insert_record.setValue(2, add_desc)
insert_record.setValue(3, str(""))
insert_record.setValue(4, str(""))
self.data_model.insertRecord(self.items_model.rowCount(),
insert_record)
if self.data_model.submitAll():
# clear the line input and selects the newly created value on the list
ui.typeDescLineEdit.clear()
ui.typeNoSpinBox.setValue(1)
self.populate_list(table_id)
index = ui.itemsListView.model().createIndex(
self.items_model.rowCount() - 1, 1)
ui.itemsListView.setCurrentIndex(index)
self.changes_made = True
else:
db_error_msg_box = QMessageBox(
QMessageBox.Warning, " ",
"Error: {}".format(self.data_model.lastError().text()),
QMessageBox.Ok, None)
db_error_msg_box.setWindowFlags(Qt.CustomizeWindowHint
| Qt.WindowTitleHint)
db_error_msg_box.exec_()
return
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 viewFilms(self, films):
#QMessageBox.warning(None, "Test", ";".join(films))
#s = QSettings(QSettings().value("APIS/config_ini"), QSettings.IniFormat)
query = QSqlQuery(self.dbm.db)
table = self.uiFlightPathAvailabilityTable
table.setRowCount(0)
self.filmsDict = {}
chkAvailability = [False, False, False, False]
for film in films:
availability = [False] * 4
flightPathDirectory = self.settings.value("APIS/flightpath_dir") + "\\" + self.yearFromFilm(film)
if os.path.isdir(flightPathDirectory):
# TODO RM availability[0] = os.path.isfile(flightPathDirectory + "\\" + IdToIdLegacy(film) + ".shp") TODO RM LEGACY
availability[0] = os.path.isfile(flightPathDirectory + "\\" + film + ".shp")
if availability[0]:
chkAvailability[0] = True
# TODO RM availability[1] = os.path.isfile(flightPathDirectory + "\\" + IdToIdLegacy(film) + "_lin.shp") TODO RM LEGACY
availability[1] = os.path.isfile(flightPathDirectory + "\\" + film + "_lin.shp")
if availability[1]:
chkAvailability[1] = True
# TODO RM availability[2] = os.path.isfile(flightPathDirectory + "\\" + IdToIdLegacy(film) + "_gps.shp") TODO RM LEGACY
availability[2] = os.path.isfile(flightPathDirectory + "\\" + film + "_gps.shp")
if availability[2]:
chkAvailability[2] = True
# Kartierung
# Input Filmnummer, weise > je nacch weise andere Tabelle (CenterPoint) für Select (COUNT)
# Wenn für Film Bilder kartiert sind ja anzeigen
qryStr = "select weise from film where filmnummer = '{0}'".format(film)
query.exec_(qryStr)
query.first()
fn = query.value(0)
if fn == u"schräg":
self.orientation = "schraeg"
else:
self.orientation = "senk"
qryStr = "select count(*) from luftbild_{0}_cp where filmnummer = '{1}'".format(self.orientation,film)
query.exec_(qryStr)
query.first()
fn = query.value(0)
if fn > 0:
availability[3] = True
chkAvailability[3] = True
rowPosition = table.rowCount()
table.insertRow(rowPosition)
table.setItem(rowPosition , 0, QTableWidgetItem(film))
table.setItem(rowPosition , 1, QTableWidgetItem(unicode(availability[0])))
table.setItem(rowPosition , 2, QTableWidgetItem(unicode(availability[1])))
table.setItem(rowPosition , 3, QTableWidgetItem(unicode(availability[2])))
table.setItem(rowPosition , 4, QTableWidgetItem(unicode(availability[3])))
table.resizeRowsToContents()
table.resizeColumnsToContents()
table.horizontalHeader().setResizeMode(QHeaderView.Stretch)
self.filmsDict[film] = availability
self.uiGpsFlightPointChk.setEnabled(chkAvailability[0])
self.uiGpsFlightLineChk.setEnabled(chkAvailability[1])
self.uiGpsCameraPointChk.setEnabled(chkAvailability[2])
self.uiGpsCameraLineChk.setEnabled(chkAvailability[2])
self.uiMappingPointChk.setEnabled(chkAvailability[3])
self.uiMappingLineChk.setEnabled(chkAvailability[3])
class FrmCierreCaja ( QDialog, Ui_dlgApertura ):
def __init__( self, user, user2, sesion, parent = None ):
super( FrmCierreCaja, self ).__init__( parent )
self.setupUi( self )
self.user = user
self.user2 = user2
self.db = QSqlDatabase.database()
self.query = QSqlQuery( """
SELECT
d.fechacreacion,
c.idcaja,
c.descripcion
FROM documentos d
LEFT JOIN docpadrehijos dp ON dp.idpadre=d.iddocumento
LEFT JOIN documentos hijo ON dp.idhijo=hijo.iddocumento AND hijo.idtipodoc=%d
JOIN cajas c on d.idcaja=c.idcaja
WHERE d.idtipodoc=%d and d.idusuario= %d
AND hijo.iddocumento IS NULL
ORDER BY d.iddocumento DESC
LIMIT 1;""" % ( constantes.IDAPERTURA, constantes.IDAPERTURA, user.uid ) )
if not self.query.exec_():
raise Exception( "No se pudo preparar la Query" )
self.query.first()
self.dtFechaTime.setDate( self.query.value( 0 ).toDate() )
self.cboCaja.addItem( self.query.value( 2 ).toString() )
self.txtUsuario.setText( self.user.user )
self.txtUsuario.setReadOnly( True )
self.dtFechaTime.setReadOnly( True )
self.dtFechaTime.dateTime().toString( "yyyyMMddhhmmss" )
self.sesion = sesion
@pyqtSlot()
def on_buttonBox_accepted( self ):
"""
Agrega una apertura de caja
"""
try:
query = QSqlQuery()
query = QSqlQuery( """
SELECT
MAX(CAST(ndocimpreso AS SIGNED))+1
FROM documentos d
WHERE idtipodoc=17
;
""" )
if not query.exec_():
raise Exception( "No se puedo prepara el query del numero de cierre" )
query.first()
ndocimpreso = query.value( 0 ).toString()
if ndocimpreso == "0" :
ndocimpreso = "1"
if not query.prepare( """
INSERT INTO documentos(ndocimpreso,total,fechacreacion,
idtipodoc,idusuario,idcaja,observacion)
VALUES(:ndocimpreso,:total,:fecha,:tipodoc,
:usuario,:caja,:observacion)""" ):
raise Exception( query.lastError().text() )
query.bindValue( ":ndocimpreso", ndocimpreso )
query.bindValue( ":total", self.txtMonto.text() )
query.bindValue( ":fecha", self.dtFechaTime.dateTime().toString( "yyyyMMddhhmmss" ) )
query.bindValue( ":tipodoc", constantes.IDCIERRESESION )
query.bindValue( ":usuario", self.user.uid )
query.bindValue( ":caja", self.query.value( 1 ) )
query.bindValue( ":observacion", self.user2 )
if not query.exec_():
raise Exception( " Insert de cierre " )
idcierre = self.query.lastInsertId().toInt()[0]
if not query.prepare( """
INSERT INTO docpadrehijos(idpadre,idhijo,monto)
VALUES(:idpadre,:idhijo,:monto)""" ):
raise Exception( query.lastError().text() )
query.bindValue( ":idpadre", self.sesion )
query.bindValue( ":idhijo", idcierre )
query.bindValue( ":monto", self.txtMonto.text() )
if not query.exec_():
raise Exception( " Insert de docpadrehijos" )
self.accept()
except Exception as inst:
self.reject()
def on_buttonBox_cancelled( self ):
"""
Cancela la apertura de caja
"""
self.reject()
def newDocument( self ):
"""
activar todos los controles, llenar los modelos necesarios,
crear el modelo Pago, aniadir una linea a la tabla
"""
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" )
query = QSqlQuery()
try:
# Rellenar el combobox de las CONCEPTOS
self.conceptosModel.setQuery( """
SELECT idconcepto, descripcion FROM conceptos c WHERE idtipodoc = %d;
""" % constantes.IDPAGO )
if self.conceptosModel.rowCount() == 0:
raise UserWarning( u"No existen conceptos en la base de "\
+ "datos para los pagos" )
self.beneficiariosModel = QSqlQueryModel()
self.beneficiariosModel.setQuery( """
SELECT
s.idpersona,
s.nombre
FROM personas s
WHERE s.tipopersona <> %d
ORDER BY s.nombre
""" % constantes.AUTOR )
if self.beneficiariosModel.rowCount() == 0:
raise UserWarning( u"No existen personas en la base de datos" )
# 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 ) )
if self.retencionModel.rowCount() == 0:
raise UserWarning( u"No existe ninguna tasa de retención en "\
+ "la base de datos" )
query = QSqlQuery(
"""
SELECT
SUM(IF(m.idtipomoneda = %d,m.monto,0)) as totalC,
SUM(IF(m.idtipomoneda = %d,m.monto,0)) as totalD
FROM
movimientoscaja m
JOIN documentos d ON d.iddocumento = m.iddocumento
WHERE d.idcaja = %d AND m.idtipomovimiento=%d
;
""" % ( constantes.IDCORDOBAS,
constantes.IDDOLARES,
self.sesion.cajaId,
constantes.IDPAGOEFECTIVO ) )
if not query.exec_():
raise UserWarning( u"No pudo obtenerse de la base de datos la cantidad de dinero en caja" )
query.first()
maxCordoba = Decimal( query.value( 0 ).toString() )
maxDolar = Decimal( query.value( 1 ).toString() )
if maxCordoba <= 0 and maxDolar <= 0:
raise UserWarning( u"No hay Efectivo en Caja" )
query = QSqlQuery( "SELECT fnCONSECUTIVO(%d,null);" % constantes.IDPAGO )
if not query.exec_():
raise UserWarning( u"No pudo obtenerse el número del comprobante" )
query.first()
ndoc = query.value( 0 ).toString()
self.lblnpago.setText( ndoc )
self.txttipocambio.setText( moneyfmt( self.sesion.tipoCambioBanco, 4 ) )
self.cbtasaret.setModel( self.retencionModel )
self.cbtasaret.setModelColumn( 1 )
self.cbtasaret.setCurrentIndex( -1 )
self.retencionId = 0
self.cbbeneficiario.setModel( self.beneficiariosModel )
self.cbbeneficiario.setCurrentIndex( -1 )
self.cbbeneficiario.setModelColumn( 1 )
completer = QCompleter()
completer.setCaseSensitivity( Qt.CaseInsensitive )
completer.setModel( self.beneficiariosModel )
completer.setCompletionColumn( 1 )
self.cbconcepto.setModel( self.conceptosModel )
self.cbconcepto.setCurrentIndex( -1 )
self.cbconcepto.setModelColumn( 1 )
completerconcepto = QCompleter()
completerconcepto.setCaseSensitivity( Qt.CaseInsensitive )
completerconcepto.setModel( self.conceptosModel )
completerconcepto.setCompletionColumn( 1 )
self.editmodel = PagoModel( self.sesion )
self.editmodel.docImpreso = ndoc
self.editmodel.maxCordoba = maxCordoba
self.editmodel.maxDolar = maxDolar
self.sbtotalc.setToolTip( "Max= " + moneyfmt( maxCordoba, 4, 'C$' ) )
self.sbtotald.setToolTip( "Max= " + moneyfmt( maxDolar, 4, 'US$' ) )
self.sbtotalc.setMaximum( maxCordoba )
self.sbtotald.setMaximum( maxDolar )
query = QSqlQuery( """
SELECT idcostoagregado, valorcosto
FROM costosagregados c
WHERE idtipocosto = %d AND activo = 1;
""" % constantes.IVA )
if not query.exec_():
raise UserWarning( u"No pudo obtenerse la tasa de IVA" )
query.first()
self.editmodel.ivaId = query.value( 0 ).toInt()[0]
self.editmodel.ivaTasa = Decimal( query.value( 1 ).toString() )
self.ckiva.setToolTip( query.value( 1 ).toString() + '%' )
self.status = False
except UserWarning as inst:
QMessageBox.critical( self, qApp.organizationName(), unicode( inst ) )
logging.error( unicode( inst ) )
logging.error( query.lastError().text() )
# except Exception as inst:
# QMessageBox.critical( self, qApp.organizationName(),
# "Hubo un problema al tratar de crear"\
# + " el nuevo pago" )
# logging.critical( unicode( inst ) )
# logging.error( query.lastError().text() )
finally:
if QSqlDatabase.database().isOpen():
QSqlDatabase.database().close()
def newDocument( self ):
"""
Slot documentation goes here.
"""
query = QSqlQuery()
try:
if not self.database.isOpen():
if not self.database.open():
raise Exception( u"No se pudo establecer una conexión con la base de datos" )
self.conceptsmodel = QSqlQueryModel()
self.conceptsmodel.setQuery( """
SELECT idconcepto, descripcion
FROM conceptos
WHERE idtipodoc = %d
""" % constantes.IDNC )
if self.conceptsmodel.rowCount() == 0:
raise UserWarning( u"No existen conceptos para devolución" )
dlgbill = DlgSelectInvoice()
if dlgbill.exec_() == QDialog.Accepted:
self.editmodel = DevolucionModel()
row = dlgbill.tblBills.selectionModel().currentIndex().row()
self.editmodel.invoiceId = dlgbill.filtermodel.index(
row, 0 ).data().toInt()[0]
self.editmodel.billPrinted = dlgbill.filtermodel.index(
row, 1 ).data().toString()
self.editmodel.clientName = dlgbill.filtermodel.index(
row, 3 ).data().toString()
self.editmodel.clientId = dlgbill.filtermodel.index(
row, 5 ).data().toInt()[0]
self.editmodel.ivaRate = Decimal( dlgbill.filtermodel.index(
row, 6 ).data().toString() )
self.editmodel.ivaRateId = dlgbill.filtermodel.index(
row, 7 ).data().toInt()[0]
self.editmodel.exchangeRate = Decimal( dlgbill.filtermodel.index(
row, 8 ).data().toString() )
self.editmodel.exchangeRateId = dlgbill.filtermodel.index(
row, 9 ).data().toInt()[0]
self.editmodel.warehouseName = dlgbill.filtermodel.index(
row, 10 ).data().toString()
self.editmodel.warehouseId = dlgbill.filtermodel.index(
row, 11 ).data().toInt()[0]
self.editmodel.uid = self.user.uid
query = QSqlQuery( """
CALL spConsecutivo(%d,NULL);
""" % constantes.IDNC )
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()
query.prepare( """
SELECT
v.idarticulo,
v.descripcion,
facs.costounit,
facs.precioventa,
-1*SUM(unidades) as existencia
FROM articulosxdocumento facs
JOIN vw_articulosdescritos v ON facs.idarticulo = v.idarticulo
LEFT JOIN docpadrehijos devs ON devs.idhijo = facs.iddocumento
WHERE facs.iddocumento = %d OR devs.idpadre = %d
GROUP BY v.idarticulo
""" % ( self.editmodel.invoiceId, self.editmodel.invoiceId ) )
if not query.exec_():
raise Exception( "Ocurrio un error en la consulta" )
while query.next():
linea = LineaDevolucion( self.editmodel )
linea.itemId = query.value( 0 ).toInt()[0]
linea.itemDescription = query.value( 1 ).toString()
linea.itemCost = Decimal( query.value( 2 ).toString() )
linea.itemPrice = Decimal( query.value( 3 ).toString() )
linea.maxquantity = query.value( 4 ).toInt()[0]
row = self.editmodel.rowCount()
self.editmodel.insertRows( row )
self.editmodel.lines[row] = linea
self.cbConcept.setModel( self.conceptsmodel )
self.cbConcept.setModelColumn( 1 )
self.cbConcept.setCurrentIndex( -1 )
self.tabnavigation.setEnabled( False )
self.tabWidget.setCurrentIndex( 0 )
self.tabledetails.setModel( self.editmodel )
delegate = DevolucionDelegate()
self.tabledetails.setItemDelegate( delegate )
self.tabledetails.resizeColumnsToContents()
self.dtPicker.setDateTime( QDateTime.currentDateTime() )
self.editmodel.dataChanged[QModelIndex, QModelIndex].connect( self.updateLabels )
self.txtDocumentNumber.setText( self.editmodel.printedDocumentNumber )
self.txtBill.setText( self.editmodel.billPrinted )
self.txtWarehouse.setText( self.editmodel.warehouseName )
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(),
u"Hubo un error al intentar crear una "\
+ "nueva devolución" )
logging.critical( unicode( inst ) )
self.status = True
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
