void mantenimientoTitulo::on_btmBuscar_clicked() { if(m_ui->btmTitulo->isChecked()) { QSqlQueryModel *model = new QSqlQueryModel(m_ui->titulos); model->setQuery("SELECT tituloObra, isbn FROM titulo WHERE tituloObra LIKE '%"+m_ui->busqueda->text()+"%';", QSqlDatabase::database("sibcues")); if (model->lastError().isValid()) qDebug() << model->lastError(); model->setHeaderData(0, Qt::Horizontal, QObject::tr("Titulo Material Bibliografico")); model->setHeaderData(1, Qt::Horizontal, QObject::tr("ISBN")); m_ui->titulos->setModel(model); connect(m_ui->titulos->selectionModel(), SIGNAL(currentRowChanged(QModelIndex,QModelIndex)), this, SLOT(on_titulos_activated(QModelIndex))); m_ui->labelTitulo->setText("Titulos: "+QString::number(model->rowCount())+" resultados."); } else { QSqlQueryModel *model = new QSqlQueryModel(m_ui->titulos); model->setQuery("SELECT titulo.tituloObra, titulo.isbn FROM obrade left join titulo on titulo.idTitulo=obrade.idTitulo left join autor on obrade.idAutor=autor.idAutor WHERE autor.nombreAutor LIKE '%"+m_ui->busqueda->text()+"%';", QSqlDatabase::database("sibcues")); if (model->lastError().isValid()) qDebug() << model->lastError(); model->setHeaderData(0, Qt::Horizontal, QObject::tr("Titulo Material Bibliografico")); model->setHeaderData(1, Qt::Horizontal, QObject::tr("ISBN")); m_ui->titulos->setModel(model); connect(m_ui->titulos->selectionModel(), SIGNAL(currentRowChanged(QModelIndex,QModelIndex)), this, SLOT(on_titulos_activated(QModelIndex))); m_ui->labelTitulo->setText("Titulos: "+QString::number(model->rowCount())+" resultados."); } }
void Browser::exec() { last_query = sqlEdit->toPlainText(); tablelisten.clear(); QSqlDatabase currendb(connectionWidget->currentDatabase()); tablelisten = currendb.tables(); for (int i = 0; i < tablelisten.size(); ++i) { QString tabi = tablelisten.at(i); if (last_query.contains(tabi,Qt::CaseInsensitive)) { runningtable = tabi; } } QSqlQueryModel *model = new QSqlQueryModel(table); model->setQuery(QSqlQuery(last_query,currendb)); table->setModel(model); if (model->lastError().type() != QSqlError::NoError) { emit statusMessage(model->lastError().text()); } else if (model->query().isSelect()) { emit statusMessage(tr("Query OK on table %1").arg(runningtable)); emit registerQuerySuccess(last_query,runningtable); /* take last register table name .... */ } else { emit statusMessage(tr("Query OK, number of affected rows: %1").arg( model->query().numRowsAffected())); } updateActions(); }
mantenimientoTitulo::mantenimientoTitulo(int idUnidad, QWidget *parent) : QDialog(parent), m_ui(new Ui::mantenimientoTitulo) { m_ui->setupUi(this); m_ui->btmTitulo->setChecked(true); Persistencia::Persistencia *servicioPersistencia=new Persistencia(idUnidad); QSqlQueryModel *model = new QSqlQueryModel(m_ui->titulos); model->setQuery("SELECT tituloObra, isbn FROM titulo;", QSqlDatabase::database("sibcues")); if (model->lastError().isValid()) qDebug() << model->lastError(); //model->setTable("titulo"); //model->select(); model->setHeaderData(0, Qt::Horizontal, QObject::tr("Titulo Material Bibliografico")); model->setHeaderData(1, Qt::Horizontal, QObject::tr("ISBN")); m_ui->titulos->setModel(model); m_ui->titulos->alternatingRowColors(); //m_ui->titulos->hideColumn(0); m_ui->titulos->horizontalHeader()->resizeSection(0, 350); m_ui->titulos->setSelectionMode(QAbstractItemView::SingleSelection); connect(m_ui->titulos->selectionModel(), SIGNAL(currentRowChanged(QModelIndex,QModelIndex)), this, SLOT(on_titulos_activated(QModelIndex))); m_ui->labelTitulo->setText("Titulos: "+QString::number(model->rowCount())+" resultados."); }
void AddressWindowImpl::refreshCategories() { qDebug("AddressWindowImpl::refreshCategories"); QSqlQueryModel *query = new QSqlQueryModel(); query->setQuery("SELECT category FROM Addresses2Categories WHERE address_id="+currentRecord.value("id").toString()); if(query->lastError().isValid()) qDebug() << query->lastError(); categoryList->setModel(query); query = new QSqlQueryModel(); query->setQuery("SELECT DISTINCT category FROM Addresses2Categories"); if(query->lastError().isValid()) qDebug() << query->lastError(); newCategoryCombo->setModel(query); }//end refreshCategories
void Browser::exec() { QSqlQueryModel *model = new QSqlQueryModel(table); model->setQuery(QSqlQuery(sqlEdit->toPlainText(), connectionWidget->currentDatabase())); table->setModel(model); if (model->lastError().type() != QSqlError::NoError) emit statusMessage(model->lastError().text()); else if (model->query().isSelect()) emit statusMessage(tr("Query OK.")); else emit statusMessage(tr("Query OK, number of affected rows: %1").arg( model->query().numRowsAffected())); updateActions(); }
void BairesWindow::runQuery(QString queryString) { QSqlQueryModel *model = new QSqlQueryModel(ui.resultsTable); QSqlQuery q(queryString, db); model->setQuery(q); if (model->lastError().type() != QSqlError::NoError) { qDebug() << model->lastError().text(); qDebug() << tr("Query Error."); } else if (model->query().isSelect()) qDebug() << tr("Query OK"); else qDebug() << tr("Query OK, number of affected rows: %1").arg(model->query().numRowsAffected()); ui.resultsTable->setModel(model); ui.resultsTable->horizontalHeader()->setStretchLastSection(true); ui.resultsTable->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch); }
void mantenimientoTitulo::on_titulos_activated(QModelIndex index) { QString tituloSelecc=index.data(0).toString(); QSqlQueryModel *model = new QSqlQueryModel(m_ui->listaAutores); model->setQuery("SELECT autor.nombreAutor FROM obrade left join titulo on titulo.idTitulo=obrade.idTitulo left join autor on obrade.idAutor=autor.idAutor WHERE titulo.tituloObra=\""+tituloSelecc+"\";", QSqlDatabase::database("sibcues")); if (model->lastError().isValid()) qDebug() << model->lastError(); //model->setTable("titulo"); //model->select(); model->setHeaderData(0, Qt::Horizontal, QObject::tr("Titulo Material Bibliografico")); //model->setEditStrategy(QSqlTableModel::OnRowChange); //m_ui->titulos->setEditTriggers(QAbstractItemView::DoubleClicked|QAbstractItemView::EditKeyPressed); m_ui->listaAutores->setModel(model); m_ui->titulos->alternatingRowColors(); }
void PgConsole::showView(QString cmd) { if(cmd.trimmed().compare("") == 0) { return; } QSqlQueryModel* model = new QSqlQueryModel; model->setQuery(cmd); //model->setEditStrategy(QSqlQueryModel::OnManualSubmit); //model->select(); if (model->lastError().isValid()) { QMessageBox::critical(this, qApp->tr("Query error"), model->lastError().text(), QMessageBox::Cancel); return; } QueryView* view = new QueryView(0, model, cmd, 0, Qt::WA_DeleteOnClose); view->show(); }
bool QueryHolder::runQuery(IDataSource::DatasourceMode mode) { m_mode = mode; QSqlDatabase db = QSqlDatabase::database(m_connectionName); if (!db.isValid()) { setLastError(QObject::tr("Invalid connection! %1").arg(m_connectionName)); return false; } if (!m_prepared){ extractParams(); if (!m_prepared) return false; } if (!m_query){ m_query = new QSqlQuery(db); m_query->prepare(m_preparedSQL); } fillParams(m_query); m_query->exec(); QSqlQueryModel *model = new QSqlQueryModel; model->setQuery(*m_query); while (model->canFetchMore()) model->fetchMore(); if (model->lastError().isValid()){ if (m_dataSource) m_dataSource.clear(); setLastError(model->lastError().text()); delete model; return false; } else setLastError(""); setDatasource(IDataSource::Ptr(new ModelToDataSource(model,true))); return true; }
void menusViewDialog::getMenusList(){ QSqlDatabase::database(); QSqlQueryModel *getMenus = new QSqlQueryModel; getMenus->setQuery("SELECT " "m.menu_id, " "m.name, " "m.altname, " "m.price, " "c.name, " "g.group_name, " "m.category_id, " "m.menu_group_id, " "g.deleted, " "c.deleted, " "m.deleted " "FROM " "menus m " "left join categories c on c.category_id = m.category_id " "left join menu_groups g on g.menu_group_id = m.menu_group_id " "WHERE " "c.deleted = 'false' " "AND g.deleted = 'false' " "AND m.deleted = 'false' "); getMenus->setHeaderData(0,Qt::Horizontal,QObject::trUtf8("Menu ID")); getMenus->setHeaderData(1,Qt::Horizontal,QObject::trUtf8("Menu Name")); getMenus->setHeaderData(2,Qt::Horizontal,QObject::trUtf8("Alt Name")); getMenus->setHeaderData(3,Qt::Horizontal,QObject::trUtf8("Price")); getMenus->setHeaderData(4,Qt::Horizontal,QObject::trUtf8("Category Name")); getMenus->setHeaderData(5,Qt::Horizontal,QObject::trUtf8("Menu Group Name")); getMenus->setHeaderData(6,Qt::Horizontal,QObject::trUtf8("Category ID")); getMenus->setHeaderData(7,Qt::Horizontal,QObject::trUtf8("Menu Group ID")); getMenus->setHeaderData(8,Qt::Horizontal,QObject::trUtf8("Group Deleted")); getMenus->setHeaderData(9,Qt::Horizontal,QObject::trUtf8("Category Deleted")); getMenus->setHeaderData(10,Qt::Horizontal,QObject::trUtf8("Menu Deleted")); // if(getMenus->lastError().isValid()) qDebug() << getMenus->lastError(); ui->menusTableView->setModel(getMenus); ui->menusTableView->hideColumn(6); ui->menusTableView->hideColumn(7); ui->menusTableView->hideColumn(8); ui->menusTableView->hideColumn(9); ui->menusTableView->hideColumn(10); qDebug() << "Get Menu List"; }
bool DatabaseBackup::readContents(const QString &filename) { qDebug() << "Read contents from " + filename; QFile file(filename); QSqlQueryModel query; bool res = false; if (file.open(QIODevice::ReadOnly)) { QJsonDocument doc = QJsonDocument::fromJson(file.readAll()); if (doc.isObject()) { res = true; QJsonArray tables = doc.object().take("database").toObject().take("tables").toArray(); QJsonArray::const_iterator i = tables.constBegin(); while (i != tables.constEnd()) { QString tableName = (*i).toObject().take("name").toString(); qDebug() << tableName; QJsonArray records = (*i).toObject().take("records").toArray(); QJsonArray::const_iterator recordIterator = records.constBegin(); while (recordIterator != records.constEnd()) { QVariantMap record = (*recordIterator).toObject().toVariantMap(); QStringList fields = record.keys(); QStringList values; QStringList::const_iterator fieldIterator = fields.constBegin(); while (fieldIterator != fields.constEnd()) { values << record.take(*fieldIterator).toString(); ++fieldIterator; } qDebug() << "INSERT INTO " + tableName + " (" + fields.join(',') + ")" + " VALUES ('" + values.join("','") + "')"; query.setQuery( QSqlQuery("INSERT INTO " + tableName + " (" + fields.join(',') + ")" + " VALUES ('" + values.join("','") + "')") ); qDebug() << "Errors?" << query.lastError(); ++ recordIterator; } ++i; } } query.submit(); } file.close(); return res; }
QSqlQueryModel *DatabaseManager::getPrestable(int idtransaccion,QWidget* parentWidget) { QSqlQueryModel *model = new QSqlQueryModel(parentWidget); if(!db.open()){ QMessageBox::critical(0,"Database Error",db.lastError().text()); return model; } QSqlQuery preQuery; preQuery.exec(QString("SELECT * FROM transacciones WHERE idtransaccion = '%1' LIMIT 1;" ).arg(idtransaccion)); if(!preQuery.next()) { //There is a record in the table with this same information qDebug() << "Transaction does not exist"; return model; } //TODO what the hell is going on here? model->setQuery(QString("SELECT marca,modelo,observaciones,idserie,dimension FROM prestables right join balanzas on prestables.balanzas_idbalanza = balanzas.idbalanza where idprestable = (select transacciones.prestables_idprestable from transacciones where idtransaccion = '2');")); // model->setQuery(QString("SELECT * FROM transacciones;")); bool ret = true; if(ret) { qDebug() << "Row count" << model->rowCount(); return model; } else { qDebug() << model->lastError().text(); return model; } }
QSqlQueryModel *Indications::ModelPokazanie(int id_apartament, int month, int year) { QSqlQueryModel *model = new QSqlQueryModel; QString str; DateOfUnixFormat date(year,month,1); str = " SELECT p.id_pokazanie, u.name, p.pokazanie_home, p.pokazanie_end, t.tariff, t.tariff2, t.norm " " FROM list_app_usluga lau, usluga u, tariff t, pokazanie p " " WHERE " " lau.id_usluga=u.id_usluga " " AND t.id_usluga=u.id_usluga " " AND u.type_usluga=1 " " AND lau.id_apartament="+QString::number(id_apartament)+" " " AND p.id_list_app_usluga=lau.id_list_app_usluga " " AND t.tariff_date=p.date_pokazanie " " AND p.date_pokazanie="+QString::number(date.Second()); model->setQuery(QSqlQuery(str)); if(model->lastError().number() != -1){ qDebug()<<"e825d464c306efe892a28669bdcefb13"<<model->lastError(); } if(model->rowCount()==0){// если строки не найдены, скорее всего нет тарифов - сделаем без них QString str2 = "SELECT p.id_pokazanie, u.name, p.pokazanie_home, p.pokazanie_end " "FROM list_app_usluga lau, usluga u, pokazanie p " "WHERE " "lau.id_usluga=u.id_usluga " "AND u.type_usluga=1 " "AND lau.id_apartament="+QString::number(id_apartament)+" " "AND p.id_list_app_usluga=lau.id_list_app_usluga " "AND p.date_pokazanie="+QString::number(date.Second()); model->setQuery(QSqlQuery(str2)); } if(model->lastError().number() != -1){ qDebug()<<"e825d464c306efe823628669bdcefb13"<<model->lastError(); } if(model->rowCount()==0){ //ООо всё равно пусто, то тогда добавим строчки QString str2; QSqlQuery query2; QStringList column, values; column << "id_list_app_usluga" << "date_pokazanie" << "pokazanie_home" << "pokazanie_end"; str2 = "SELECT id_list_app_usluga FROM list_app_usluga WHERE id_apartament=%1"; //все Услуги квартиры str2 = str2.arg(id_apartament); if (query2.exec(str2)){ if (query2.size() == -1){ return model; } while (query2.next()){ // переберём все строчки values.clear(); values << query2.value(0).toString() << QString::number(date.Second()) << QString::number(0) << QString::number(0); BD::add("pokazanie",column,values); } } else{ qDebug()<<query2.lastError(); } model = ModelPokazanie(id_apartament, month, year); // И теперь попробуем ещё раз получить не пустую модель } if(model->lastError().number() != -1){ qDebug()<<"e8237664c306efe892a28669bdcefb13"<<model->lastError(); } model->setHeaderData(1,Qt::Horizontal,QObject::trUtf8("Счётчик")); model->setHeaderData(2,Qt::Horizontal,QObject::trUtf8("Пок посл")); model->setHeaderData(3,Qt::Horizontal,QObject::trUtf8("Пок тек")); model->setHeaderData(4,Qt::Horizontal,QObject::trUtf8("Тариф")); model->setHeaderData(5,Qt::Horizontal,QObject::trUtf8("Тариф2")); model->setHeaderData(6,Qt::Horizontal,QObject::trUtf8("Норма")); return model; }