#include "mainwindow.h"
#include "ui_mainwindow.h"

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

    dbState = 0;
    ui->tabWidget->setCurrentIndex(1);
    on_butCODB_clicked();
    on_butTableShow_clicked();

    //this->setGeometry(0,0,480,272);
    this->setGeometry(100,100,480,272); //For Windows
}

MainWindow::~MainWindow()
{
    delete ui;
}

void MainWindow::on_butCODB_clicked()
{
    if(!dbState)
    {
        dbState = 1;
        db = QSqlDatabase::addDatabase("QSQLITE");  //use sqlite driver
        db.setDatabaseName("TEST01.db");  //Set db name
        bool dbOpenState = db.open();  //Try to connect db
        if (dbOpenState)
        {
            //Connect SQl success
            ui->tbShow->append("Connect Success!");
        }
        else
        {
            //Open db fail,show the describe from the db
            ui->tbShow->append(db.lastError().databaseText());
        }
    }
    else
    {
        dbState = 0;
        db.close();
        ui->tbShow->append("Database Closed!");
    }
}

void MainWindow::on_butShow_clicked()
{
    QSqlQuery query;  //Create a new search object
    if (query.exec("select * from FOOD order by Id ASC"))   //Try to list all value from FOOD table
    {
        //Search success
        ui->tbShow->append("Search Success!");

        numRows = 0;  //询问数据库驱动，是否驱动含有某种特性
        if (db.driver()->hasFeature(QSqlDriver::QuerySize))
        {
            numRows = query.size();  //如果支持结果影响的行数，那么直接记录下来
        }
        else
        {
            query.last(); //否则定位到结果最后，qt 文档说，这个方法非常慢
            numRows = query.at() + 1;
            query.seek(-1);
        }

        QString id,name,num;
        ui->tbShow->append("================");
        while(query.next())//定位结果到下一条记录
        {
            id = query.value(0).toString();
            name = query.value(1).toString();
            num = query.value(2).toString();
            QString result = id + " | " + name + " | " + num;
            ui->tbShow->append(result);
        }
        ui->tbShow->append("================");
        ui->tbShow->append(QString("totally %1 rows").arg(numRows));

        query.finish();
    }
    else
    {
        //Search fail,show the reason
        QSqlError error = query.lastError();
        ui->tbShow->append(error.databaseText());
    }
}

void MainWindow::on_butTableShow_clicked()
{
    ui->cbChange->clear();
    ui->tableShow->clear();
    ui->tableShow->setColumnCount(4);

    dbHeader[0] = "Id";
    dbHeader[1] = "Name";
    dbHeader[2] = "Number";
    dbHeader[3] = "Describe";

    QStringList header;
    header<<dbHeader[0]<<dbHeader[1]<<dbHeader[2]<<dbHeader[3];
    ui->tableShow->verticalHeader()->setVisible(false);

    ui->tableShow->setColumnWidth(0,40);
    ui->tableShow->setColumnWidth(1,80);
    ui->tableShow->setColumnWidth(2,80);
    ui->tableShow->setColumnWidth(3,100);

    ui->lblChange1->setText(dbHeader[1]);
    ui->lblChange2->setText(dbHeader[2]);
    ui->lblChange3->setText(dbHeader[3]);

    QSqlQuery queryTable;
    if (queryTable.exec("select * from FOOD order by Id ASC"))
    {
        //Search success
        ui->tbDebugT->append("Search Success!");

        if (db.driver()->hasFeature(QSqlDriver::QuerySize))
        {
            numRows = queryTable.size();
        }
        else
        {
            queryTable.last();
            numRows = queryTable.at() + 1;
            queryTable.seek(-1);
        }

        ui->tableShow->setRowCount(numRows);

        int i=0;
        QString SqlUpdateId;
        QSqlQuery queryTableU;
        while(queryTable.next())    //Refresh the table and reset id with sequential arrangement
        {
            ui->tableShow->setItem(i,0,new QTableWidgetItem(queryTable.value(0).toString()));
            ui->tableShow->setItem(i,1,new QTableWidgetItem(queryTable.value(1).toString()));
            ui->tableShow->setItem(i,2,new QTableWidgetItem(queryTable.value(2).toString()));
            ui->tableShow->setItem(i,3,new QTableWidgetItem(queryTable.value(3).toString()));

            SqlUpdateId = "update FOOD set " + dbHeader[0] + " = :value where id = :id";
            queryTableU.prepare(SqlUpdateId);
            queryTableU.bindValue(":value", i+1);
            queryTableU.bindValue(":id", ui->tableShow->item(i,0)->text().toInt());
            if(!queryTableU.exec())
            {
                QSqlError error = queryTableU.lastError();
                ui->tbDebugT->append(error.databaseText());
            }

            ui->cbChange->addItem(queryTable.value(0).toString());
            ui->tableShow->setItem(i,0,new QTableWidgetItem(queryTable.value(0).toString()));
            ui->tableShow->setItem(i,1,new QTableWidgetItem(queryTable.value(1).toString()));
            ui->tableShow->setItem(i,2,new QTableWidgetItem(queryTable.value(2).toString()));
            ui->tableShow->setItem(i,3,new QTableWidgetItem(queryTable.value(3).toString()));

            i++;
            if(i>=numRows)
            {
                i=0;
                break;
            }
        }
        if(numRows<0)
            ui->tableShow->setRowCount(0);      //Clean the remaining row
        ui->tableShow->setHorizontalHeaderLabels(header);
        ui->tableShow->show();
        queryTable.finish();
    }
    else
    {
        //Search fail,show the reason
        QSqlError error = queryTable.lastError();
        ui->tbDebugT->append(error.databaseText());
    }
}

void MainWindow::on_butDelete_clicked()
{
    QSqlQuery queryDelete;
    QString SqlDelete = "delete from FOOD where id = ?";
    queryDelete.prepare(SqlDelete);
    queryDelete.addBindValue(ui->tableShow->currentIndex().row()+1);
    if(!queryDelete.exec())
    {
        QSqlError error = queryDelete.lastError();
        ui->tbDebugC->append(error.databaseText());
    }
    else
    {
        ui->tbDebugT->append("Delete success!");
    }
    on_butTableShow_clicked();      //Delete and then refresh the table
    on_butTableShow_clicked();
}

void MainWindow::on_butClear_clicked()
{
    QSqlQuery queryClear;
    QString SqlDelete = "delete from FOOD";
    queryClear.prepare(SqlDelete);
    if(!queryClear.exec())
    {
        QSqlError error = queryClear.lastError();
        ui->tbDebugC->append(error.databaseText());
    }
    else
    {
        ui->tbDebugT->append("Clear success!");
    }
    on_butTableShow_clicked();
}

void MainWindow::on_butInsert_clicked()
{
    QSqlQuery queryInsert;
    QString insertValue = "insert into FOOD values (?, ?, ?, ?)";
    queryInsert.prepare(insertValue);
    if(numRows<0)
        numRows = 0;
    queryInsert.addBindValue(numRows+1);
    queryInsert.addBindValue(ui->leChange1->text());
    queryInsert.addBindValue(ui->leChange2->text());
    queryInsert.addBindValue(ui->leChange3->text());

    if(!queryInsert.exec())
    {
        QSqlError error = queryInsert.lastError();
        ui->tbDebugC->append(error.databaseText());
    }
    else
    {
        ui->tbDebugC->append("Inserted success!");
        numRows++;
        on_butTableShow_clicked();
        queryInsert.finish();
    }
}

void MainWindow::on_butChange_clicked()
{
    QString SqlUpdate;

    if(!ui->leChange1->text().isEmpty())
    {
        QSqlQuery queryChange1;
        SqlUpdate = "update FOOD set " + dbHeader[1] + " = :value where id = :id";
        queryChange1.prepare(SqlUpdate);
        queryChange1.bindValue(":value", ui->leChange1->text());
        queryChange1.bindValue(":id", ui->cbChange->currentIndex()+1);
        if(!queryChange1.exec())
        {
            QSqlError error = queryChange1.lastError();
            ui->tbDebugC->append(error.databaseText());
        }
        else
        {
            ui->tbDebugC->append("Update " + dbHeader[1] + ui->cbChange->currentText() + " success!");
            queryChange1.finish();
        }
    }
    if(!ui->leChange2->text().isEmpty())
    {
        QSqlQuery queryChange2;
        SqlUpdate = "update FOOD set " + dbHeader[2] + " = :value where id = :id";
        queryChange2.prepare(SqlUpdate);
        queryChange2.bindValue(":value", ui->leChange2->text());
        queryChange2.bindValue(":id", ui->cbChange->currentIndex()+1);
        if(!queryChange2.exec())
        {
            QSqlError error = queryChange2.lastError();
            ui->tbDebugC->append(error.databaseText());
        }
        else
        {
            ui->tbDebugC->append("Update " + dbHeader[2] + ui->cbChange->currentText() + " success!");
            queryChange2.finish();
        }
    }
    if(!ui->leChange3->text().isEmpty())
    {
        QSqlQuery queryChange3;
        SqlUpdate = "update FOOD set " + dbHeader[3] + " = :value where id = :id";
        queryChange3.prepare(SqlUpdate);
        queryChange3.bindValue(":value", ui->leChange3->text());
        queryChange3.bindValue(":id", ui->cbChange->currentIndex()+1);
        if(!queryChange3.exec())
        {
            QSqlError error = queryChange3.lastError();
            ui->tbDebugC->append(error.databaseText());
        }
        else
        {
            ui->tbDebugC->append("Update " + dbHeader[3] + ui->cbChange->currentText() + " success!");
            queryChange3.finish();
        }

    }
    on_butTableShow_clicked();
    ui->tabWidget->setCurrentIndex(1);      //Show the table tab
}

void MainWindow::on_butChangeT_clicked()    //Get the seleted data in table to the updata tab
{
    ui->cbChange->setCurrentIndex(ui->tableShow->item(ui->tableShow->currentIndex().row(),0)->text().toInt()-1);
    ui->leChange1->setText(ui->tableShow->item(ui->tableShow->currentIndex().row(),1)->text());
    ui->leChange2->setText(ui->tableShow->item(ui->tableShow->currentIndex().row(),2)->text());
    ui->leChange3->setText(ui->tableShow->item(ui->tableShow->currentIndex().row(),3)->text());
    ui->tabWidget->setCurrentIndex(2);
}

void MainWindow::on_butReboot_clicked()
{
    QProcess *newApp = new QProcess;
    newApp->start("/home/QtSqlTT01 -qws");
    QApplication::exit(0);
}

void MainWindow::on_butCreateT_clicked()    //Create FOOD table in db
{
    QSqlQuery queryCreate;
    QString SqlCreate = "create table FOOD (Id INT PRIMARY KEY NOT NULL, Name TEXT NOT NULL, Number INT NOT NULL, Describe TEXT NOT NULL)";
    queryCreate.prepare(SqlCreate);
    if(!queryCreate.exec())
    {
        QSqlError error = queryCreate.lastError();
        ui->tbShow->append(error.databaseText());
    }
    else
    {
        ui->tbShow->append("Table FOOD created!");
    }
}