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