/****************************************************************************
** qdiscos es un cliente a la base de datos SQLite discos.db
**---------------------------------------------------------------------------
** qdiscos - main.cpp, qdiscos.cpp, qdiscos.h, qdiscos.qrc, qdiscos.ui
**===========================================================================
** FJA - neocipres@gmail.com                Abril de 2011   (14/04/2011)
**===========================================================================
** This file may be used under the terms of the GNU General Public
** License version 3.0 as published by the Free Software Foundation
** and appearing in the file LICENSE.GPL included in the packaging of
** this file.  Please review the following information to ensure GNU
** General Public Licensing requirements will be met:
**
**
** This file is provided AS IS with NO WARRANTY OF ANY KIND, INCLUDING THE
** WARRANTY OF DESIGN, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
**
****************************************************************************/

#include "qdiscos.h"
#include "ui_qdiscos.h"
#include <QtSql>
#include <QSqlDatabase>
#include <QSqlQueryModel>
#include <QSqlRecord>
#include <QSqlQuery>
#include <QMessageBox>
#include <QPrinter>
#include <QPrintDialog>
#include <QPainter>
#include <QHeaderView>

//La DB discos.db se crea con:
//$ sqlite3 discos.db  "create table t1 (t1key INTEGER PRIMARY KEY, autor TEXT, ndisco TEXT, tipo TEXT, gro TEXT, ae TEXT, codigo TEXT, tca1 TEXT, tca2 TEXT, ob TEXT);"

QString rutaDB = "/home/paco/prgdgnu/qt4/qdiscos/discos.db";

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    model = new QSqlQueryModel;
    campo = QString::null;
    texto = QString::null;
    campos = " autor,ndisco,tipo,gro,ae,codigo,tca1,tca2,ob ";
    sumaReg = false;

    connect(ui->actionAcerca_de_Qt, SIGNAL(triggered()), qApp, SLOT(aboutQt()));
    connect(ui->actionConectar, SIGNAL(triggered()), this, SLOT(conectarDb()));
    connect(ui->pBAceptar, SIGNAL(clicked()), this, SLOT(buscar()));
    connect(ui->lETexto, SIGNAL(returnPressed()), this, SLOT(buscar()));
    connect(ui->tVTabla, SIGNAL(clicked(QModelIndex )), this, SLOT(editarReg(QModelIndex )));
    connect(ui->tVTabla, SIGNAL(activated(QModelIndex )), this, SLOT(editarReg(QModelIndex )));
    connect(ui->pBGrabar, SIGNAL(clicked()), this, SLOT(grabarReg()));
    connect(ui->pBAgregar, SIGNAL(clicked()), this, SLOT(agregarReg()));
    connect(ui->pBEliminar, SIGNAL(clicked()), this, SLOT(eliminarReg()));
    connect(ui->pBContar, SIGNAL(clicked()), this, SLOT(contarReg()));
    connect(ui->actionAcerca_de_qDiscos, SIGNAL(triggered()), this, SLOT(acercade()));
    connect(ui->actionImprimir, SIGNAL(triggered()), this, SLOT(imprimir()));

}

MainWindow::~MainWindow()
{
    if (db.isOpen()) {db.close();}
    delete ui;
}

void MainWindow::conectarDb()
{
    db = QSqlDatabase::addDatabase("QSQLITE" , "discos");
    db.setDatabaseName(rutaDB);
    bool okdb = db.open();
    if (okdb){
        if(db.isOpen()){
            ui->actionConectar->setEnabled(false);
            cargarTabla(campo, texto);
            ui->statusBar->showMessage("Conectado a " + db.connectionName(), 3000);
        }
         else {
             ui->statusBar->showMessage("No conectado");
         }
     }
    else {ui->statusBar->showMessage("Error: " + db.lastError().text() + " <=> Drivers: " + QSqlDatabase::drivers().join(", "));
    }
}

void MainWindow::cargarTabla(QString c, QString txt)
{
    QString sql = QString::null;

    if (c.isEmpty() || txt.isEmpty()){
        sql = "SELECT "+ campos + "FROM t1 ORDER BY autor, ndisco";
        model->setQuery(sql, db);
        ui->tVTabla->setModel(model);
    }
    if (!c.isEmpty() && !txt.isEmpty()){
        if (c == "temas"){
            sql = "SELECT " + campos + " FROM t1 where tca1 like '%"+txt+"%' OR tca2 like '%"+txt+"%' ORDER BY autor, ndisco";
            model->setQuery(sql, db);
            ui->tVTabla->setModel(model);
        }
        else {
            sql = "SELECT " + campos + "FROM t1 where "+c+" like '%"+txt+"%' ORDER BY autor, ndisco";
            model->setQuery(sql, db);
            ui->tVTabla->setModel(model);
        }
    }

    while(model->canFetchMore())  // Estas dos líneas de código necesarias para contar un nº de regs > 256
    model->fetchMore();

    int nRegs = model->rowCount();
    ui->statusBar->showMessage("Registros listados: "+QString::number(nRegs));
}


void MainWindow::buscar()
{
    campo = ui->cBCampos->currentText();
    texto = ui->lETexto->text();
    limpiar();
    if (db.isOpen()) {cargarTabla(campo, texto);}
        else {ui->statusBar->showMessage("Error: base de datos no abierta");}
}

void MainWindow::editarReg(QModelIndex i)
{
    int nreg = i.row();
    QSqlRecord reg = model->record(nreg);
    ui->lEAutor->setText(reg.value(0).toString());
    ui->lETitulo->setText(reg.value(1).toString());
    ui->lETipo->setText(reg.value(2).toString());
    ui->lEGro->setText(reg.value(3).toString());
    ui->lEFecha->setText(reg.value(4).toString());
    ui->lECodigo->setText(reg.value(5).toString());
    ui->tETca1->setText(reg.value(6).toString());
    ui->tETca2->setText(reg.value(7).toString());
    ui->tEOb->setText(reg.value(8).toString());

    ui->statusBar->showMessage("Registro: "+QString::number(nreg + 1));
}

QString MainWindow::ultimoCodigo()
{
    QString cod = QString::null;
    QStringList listaCod;
    QString sql = "SELECT codigo, autor, ndisco FROM t1 GROUP BY codigo";
    QSqlQuery  consulta = QSqlQuery(db);
    consulta.exec(sql);
    while (consulta.next())
    {
        cod = consulta.value(0).toString().remove(0,2);
        listaCod << cod;
    }
    listaCod.sort();
    cod = listaCod.last();
    return cod;
}

void MainWindow::limpiar()
{
    if (!db.isOpen()) return;
    int nuevoCod = ultimoCodigo().toInt() + 1;

    ui->lEAutor->clear();
    ui->lETitulo->clear();
    ui->lEFecha->clear();
    ui->lEGro->clear();
    ui->lETipo->clear();
    ui->tETca1->clear();
    ui->tETca2->clear();
    ui->tEOb->clear();
    if (sumaReg) {ui->lECodigo->setText("**0"+QString::number(nuevoCod));}
    else {ui->lECodigo->clear();}

}

void MainWindow::agregarReg()
{
    if (!db.isOpen()) return;
    int nRegs = 0;
    sumaReg = true;

    limpiar();
    QSqlQuery  consulta = QSqlQuery(db);
    consulta.exec("SELECT * FROM t1;");
    while (consulta.next())
    {
        nRegs++;
    }

    ui->statusBar->showMessage("Registros: "+QString::number(nRegs), 3000);


}

void MainWindow::grabarReg()
{
    if (!db.isOpen() || ui->lECodigo->text().isEmpty()) return;
    int nRegs = 0;
    QString tca1 = ui->tETca1->toPlainText();
    QString tca2 = ui->tETca2->toPlainText();

    QSqlQuery  consulta = QSqlQuery(db);
    //consulta.exec("SELECT * FROM t1;");
    if ( sumaReg ) {

         switch( QMessageBox::warning( this, "Agregar registro",
                                           "Quiere agregar el registro?\n\n"+
                                           ui->lECodigo->text()+"\n\n"+
                                           ui->lEAutor->text()+"\n"+
                                           ui->lETitulo->text()+"\n\n",
                                           QString::fromUtf8("Sí"),
                                           "No", 0, 1, 1 ) ) {
             case 0: // The user clicked the Sí again button or pressed Enter
                 if ( !consulta.exec( "INSERT INTO t1 ("+ campos +")  VALUES ( '" +ui->lEAutor->text().toUpper()
                                                                                  +"', '"
                                                                                  +ui->lETitulo->text().toUpper()
                                                                                  +"', '"
                                                                                  +ui->lETipo->text().toUpper()
                                                                                  +"', '"
                                                                                  +ui->lEGro->text().toUpper()
                                                                                  +"', '"
                                                                                  +ui->lEFecha->text()
                                                                                  +"','"
                                                                                  +ui->lECodigo->text().toUpper()
                                                                                  +"','"
                                                                                  +tca1.replace("'"," ",Qt::CaseInsensitive)
                                                                                  +"','"
                                                                                  +tca2.replace("'"," ",Qt::CaseInsensitive)
                                                                                  +"','"
                                                                                  +ui->tEOb->toPlainText() + "' );" ))
                   {QMessageBox::warning(0,
                                        tr("Error - agregar registros"),
                                        tr("No se puede agregar el registro \n\n")+
                                        consulta.lastError().text(),
                                        tr("Aceptar"));  }

                 break;
             case 1: // The user clicked the Quit or pressed Escape
                        // exit
                 break;
         } // switch 1

         sumaReg = false;
         cargarTabla("","" );
         consulta.exec("SELECT * FROM t1;");
         while (consulta.next())
             {
                 nRegs++;
             }
         limpiar();
         ui->statusBar->showMessage("Registros: "+QString::number(nRegs), 3000);
         } // agregar  registro
    else {
        QString codActual = ui->lECodigo->text();
        ui->lECodigo->setEnabled(false);
        switch( QMessageBox::warning( this, "Actualizar registro",
                                              "Quiere actualizar el registro?\n\n"+
                                              ui->lECodigo->text()+"\n\n"+
                                              ui->lEAutor->text()+"\n"+
                                              ui->lETitulo->text()+"\n\n",
                                              QString::fromUtf8("Sí"),
                                              "No", 0, 1, 1 ) ) {
                case 0: // The user clicked the Sí again button or pressed Enter

                    if ( !consulta.exec("UPDATE t1 SET autor='" +ui->lEAutor->text().toUpper()
                                                                +"', ndisco='"+ui->lETitulo->text().toUpper()
                                                                +"', tipo='"+ui->lETipo->text().toUpper()
                                                                +"', gro='"+ui->lEGro->text().toUpper()
                                                                +"', ae='"+ui->lEFecha->text()
                                                                +"', tca1='"+tca1.replace("'"," ",Qt::CaseInsensitive)
                                                                +"', tca2='"+tca2.replace("'"," ",Qt::CaseInsensitive)
                                                                +"', ob='"+ui->tEOb->toPlainText()
                                                                +"' where codigo='"+codActual+"';") )
                      {QMessageBox::warning(0,
                                           tr("Error - actualizar registros"),
                                           tr("No se puede actualizar el registro \n\n")+
                                           consulta.lastError().text(),
                                           tr("Aceptar"));  }

                    break;
                case 1: // The user clicked the Quit or pressed Escape
                           // exit
                    break;

            } // switch 2
        cargarTabla("codigo", codActual);
        ui->lECodigo->setEnabled(true);
        limpiar();
    } // actualizar registro

} // grabarReg()

void MainWindow::eliminarReg()
{
    if (!db.isOpen() || ui->lECodigo->text().isEmpty()) return;
    QSqlQuery  consulta = QSqlQuery (db);
            switch( QMessageBox::warning( this, "Eliminar registro",
                                         "Quiere eliminar el registro?\n\n"+
                                          ui->lECodigo->text()+"\n\n"+
                                          ui->lEAutor->text()+"\n"+
                                          ui->lETitulo->text()+"\n\n",
                                          QString::fromUtf8("Sí"),
                                          "No", 0, 1, 1 ) ) {
            case 0: // The user clicked the Sí again button or pressed Enter
                if ( !consulta.exec("DELETE FROM t1 WHERE codigo='"+ui->lECodigo->text()+"';") )
                  {QMessageBox::warning(0,
                                       tr("Error - eliminar registros"),
                                       tr("No se puede eliminar el registro: ")+ ui->lECodigo->text()+"\n\n"+
                                       consulta.lastError().text(),
                                       tr("Aceptar"));  }
                else {ui->statusBar->showMessage("Eliminado el registro: "+ui->lECodigo->text(), 3000);}

                break;
            case 1: // The user clicked the Quit or pressed Escape
                       // exit
                break;
        }
            cargarTabla("codigo", ui->lECodigo->text());
            limpiar();
}

void MainWindow::contarReg()
{
    if (!db.isOpen()) return;
    limpiar();
    campo = ui->cBCampos->currentText();
    QSqlQuery consulta = QSqlQuery (db);
    if ( !consulta.exec("SELECT "+campo+", COUNT(*) FROM t1 GROUP BY "+campo+";") ) {
                QMessageBox::warning(0,
                                     tr("Error - contar registros"),
                                     tr("No se pueden contar \n\n")+
                                     consulta.lastError().text(),
                                     tr("Aceptar"));
    }
    QString html = QString::null;
    html.append("<br><hr>");
    html.append("<h3> Contar por "+campo+"</h3>");
    html.append("<hr>");
    ui->tETca1->setHtml(html);
    ui->tETca2->append("==============");
    while ( consulta.next() ) {

        ui->tETca2->append(" "+consulta.value(0).toString()+": "+consulta.value(1).toString());

        }
    ui->tETca2->append("==============");
}

void MainWindow::acercade()
{
    QString separador = QString::null;
     separador.fill('=', 37);
     QString nomAp = "<p><b><BIG><BIG>qDiscos</BIG></BIG></b></p>";
     QString desAp1 = "<p><b>"+QString::fromUtf8("Cliente a la DB discos.db")+"</b></p>";
     QString desAp2 = "<p>"+QString::fromUtf8("La base de datos es <a href= http://www.sqlite.org>SQLite</a>. Es un sistema de gestión de bases de datos relacional.")+"</p>";
     QString autor =  "<p><b>FJA</b> - neocipres@gmail.com</p>";
     QString version = QString::fromUtf8("<p>Versión: 0.9.7</p>");
     QString modificado = "<p>Abril de 2011  -  Modificado el 14 de Abr/2011</p>";
     QString licencia = QString::fromUtf8("<p>Distribuido de acuerdo a los términos de la <a href= http://www.viti.es/gnu/licenses/gpl.html>licencia GNU/GPL</a></p>");
     QString  gnu = "<p><a href= http://www.gnu.org/home.es.html>Proyecto GNU</a></p>";
     QMessageBox::about(ui->centralWidget, "Acerca de ...",nomAp+desAp1+desAp2+separador+autor+modificado+separador+version+licencia+gnu);

}

void MainWindow::imprimir()
{
    if (!db.isOpen()) return;
    QPrinter printer(QPrinter::HighResolution);
    qreal left;
    qreal top;
    qreal right;
    qreal bottom;
    int  numP = 1;
    QSqlRecord reg;

    QPrintDialog *dlg = new QPrintDialog(&printer, this);
    dlg->setWindowTitle(tr("Imprimir Tabla"));
    if (dlg->exec() != QDialog::Accepted) return;

    QPainter p;
    if (!p.begin(&printer)) return;

    printer.getPageMargins(&left, &top, &right, &bottom, QPrinter::DevicePixel);
    QRectF papel = printer.pageRect(QPrinter::DevicePixel);
    int filas = model->rowCount(QModelIndex());

    p.setFont(QFont::QFont("Helvetica", 9, QFont::Normal, false )); // Nimbus Sans L, Arial

    QFontMetrics fm = p.fontMetrics();
    int yPos = fm.lineSpacing();

    for( int i = 0; i < filas; i++ ) {
        if ( top + yPos > papel.height() - bottom ) {
            QString msg( "Imprimiendo (pag " );
            msg += QString::number( ++numP );
            msg += ")...";
            ui->statusBar->showMessage(msg);
            printer.newPage();
            yPos = fm.lineSpacing();

        }
        if (yPos == fm.lineSpacing()) {
            if (campo.isEmpty() && texto.isEmpty()) p.drawText(left, 0, "Listado general de discos.db");
             else p.drawText(left, 0, "Listado por "+campo+" con el contenido "+texto);
            p.drawText(papel.width() - right, 0, QString::number(numP));
        }
        reg = model->record(i);
        p.drawText(left, top + yPos, reg.value(0).toString());
        p.drawText(20*left, top + yPos, reg.value(1).toString());
        p.drawText(papel.width()-10*right, top + yPos, reg.value(4).toString());
        p.drawText(papel.width()-6*right, top + yPos, reg.value(2).toString());

        yPos = yPos + fm.lineSpacing();
    }
    p.drawText(left, papel.height() - fm.lineSpacing(), "Registros listados: "+QString::number(filas));
    p.end();
    ui->statusBar->showMessage("Registros enviados a la impresora: "+QString::number(filas), 5000);

   } //imprimir