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;
}
