//
// Syntilista - debt list/management database program
// Programmed and designed by Matti Hämäläinen <ccr@tnsp.org>
// (C) Copyright 2017 Tecnic Software productions (TNSP)
//
// Distributed under 3-clause BSD style license, refer to
// included file "COPYING" for exact terms.
//
#include <QApplication>
#include <QMessageBox>
#include <QSettings>
#include <QPrintDialog>
#include <QPrintPreviewDialog>
#include <QStandardPaths>
#include "main.h"
#include "ui_mainwindow.h"
#include "ui_editperson.h"
#include "ui_aboutwindow.h"


//
// Application settings struct
//
struct
{
    QPoint uiPos;
    QSize  uiSize;
    double uiScale;              // Global UI scale factor

    QString dataPath;            // Application data path/directory
    QString dbBackupURL;
    QString dbBackupSecret;
} settings;



//
// Convert QString to a double value, replacing comma
//
double slMoneyStrToValue(const QString &str)
{
    QString str2 = str;
    return str2.replace(",", ".").toDouble();
}


//
// Convert double value to formatted QString
//
QString slMoneyValueToStr(double val)
{
    return QStringLiteral("%1").arg(val, 1, 'f', 2);
}


//
// Trim and cleanup given QString (removing double whitespace etc.)
//
QString slCleanupStr(const QString &str)
{
    return str.simplified().trimmed();
}


//
// Manipulate given QDateTime value to get desired
// correct timestamp.
//
const QDateTime slDateTimeToLocal(const QDateTime &val)
{
    QDateTime tmp = val;
    tmp.setOffsetFromUtc(0);
    return tmp.toLocalTime();
}


//
// Return a string representation of given QDateTime
// converted to local time.
//
const QString slDateTimeToStr(const QDateTime &val)
{
    return slDateTimeToLocal(val).toString(QStringLiteral("yyyy-MM-dd hh:mm"));
}


//
// Error logging
//
void slLog(const QString &mtype, const QString &msg)
{
    QString filename = settings.dataPath + QDir::separator() + APP_LOG_FILE;
    QFile fh(filename);
    if (fh.open(QIODevice::WriteOnly | QIODevice::Append | QIODevice::Text))
    {
        QTextStream out(&fh);
        out <<
            slDateTimeToLocal(QDateTime::currentDateTimeUtc()).
            toString(QStringLiteral("yyyy-MM-dd hh:mm:ss"))
            << " [" << mtype << "]: " << msg << "\n";
        fh.close();
    }
}


//
// Display an error dialog with given title and message
//
int slErrorMsg(QString title, QString msg)
{
    QMessageBox dlg;

    slLog("ERROR", msg);

    dlg.setText(title);
    dlg.setInformativeText(msg);
    dlg.setTextFormat(Qt::RichText);
    dlg.setIcon(QMessageBox::Critical);
    dlg.setStandardButtons(QMessageBox::Ok);
    dlg.setDefaultButton(QMessageBox::Ok);

    return dlg.exec();
}


//
// Check if an SQL error has occured (for given QSqlError) and
// report it to stdout if so. Return "false" if error has occured,
// true otherwise.
//
bool slCheckAndReportSQLError(const QString where, const QSqlError &err, bool report = false)
{
    if (err.isValid())
    {
        slLog("ERROR",
            QStringLiteral("SQL %1: %2").
            arg(where).arg(err.text()));
        return false;
    }
    else
    {
        if (report)
        {
            slLog("NOTE",
                QStringLiteral("SQL OK %1").arg(where));
        }
        return true;
    }
}


void PersonInfo::dump()
{
    printf(
        "PersonInfo() #%lld '%s %s' (added=%s, updated=%s, balance %1.2f)\n#%s#\n",
        id,
        firstName.toUtf8().constData(),
        lastName.toUtf8().constData(),
        slDateTimeToStr(added).toUtf8().constData(),
        slDateTimeToStr(updated).toUtf8().constData(),
        balance,
        extraInfo.toUtf8().constData());
}


//
// Get PersonInfo record from SQL query object
//
void slGetPersonInfoRec(QSqlQuery &query, PersonInfo &info)
{
    info.id         = query.value(0).toInt();
    info.firstName  = query.value(1).toString();
    info.lastName   = query.value(2).toString();
    info.extraInfo  = query.value(3).toString();
    info.added      = query.value(4).toDateTime();
    info.updated    = query.value(5).toDateTime();
    info.balance    = query.value(6).toDouble();
}


//
// Get PersonInfo record from SQL database for specified person ID #
//
bool slGetPersonInfo(qint64 id, PersonInfo &info)
{
    QSqlQuery query;
    query.prepare(
        "SELECT id,first_name,last_name,extra_info,added,updated, "
        "(SELECT TOTAL(value) FROM transactions WHERE transactions.person=people.id) AS balance "
        "FROM people WHERE id=?");

    query.addBindValue(id);
    query.exec();
    if (!query.next())
        return false;

    slGetPersonInfoRec(query, info);
    query.finish();
    return true;
}


void slSetCommonStyleSheet(QWidget *widget)
{
    // Clamp scale value
    if (settings.uiScale < 0.5f)
        settings.uiScale = 0.5f;

    if (settings.uiScale > 3.0f)
        settings.uiScale = 3.0f;

    // Set the stylesheet
    widget->setStyleSheet(
        QStringLiteral(
        "* { font-size: %1pt; }"
        "QPushButton { font-size: %2pt; padding: 0.25em; }"
        "#button_AddDebt[enabled='true'] { font-size: %3pt; background-color: #900; color: white; }"
        "#button_PayDebt[enabled='true'] { font-size: %3pt; background-color: #090; color: white; }"
        "#button_PayFullDebt[enabled='true'] { background-color: #060; color: white; }"

        "#button_AddDebt[enabled='false'] { font-size: %3pt; background-color: #622; color: black; }"
        "#button_PayDebt[enabled='false'] { font-size: %3pt; background-color: #262; color: black; }"
        "#button_PayFullDebt[enabled='false'] { background-color: #131; color: black; }"

        "#label_PersonName { font-size: %5pt; font-weight: bold;  }"
        "#label_BalanceValue { font-size: %4pt; font-weight: bold; }"
        "#label_EUR { font-size: %4pt; font-weight: bold; }"
        "#edit_Amount { font-size: %4pt; margin: 0.5em; padding: 0.5em; }"
        "#label_NumPeopleValue { color: green; }"
        ).
        arg(12 * settings.uiScale).
        arg(14 * settings.uiScale).
        arg(16 * settings.uiScale).
        arg(18 * settings.uiScale).
        arg(20 * settings.uiScale)
        );
}


int main(int argc, char *argv[])
{
    QApplication sapp(argc, argv);
    QSettings tmpst(APP_VENDOR, APP_ID);

    // Read configuration settings
    settings.uiPos = tmpst.value("pos", QPoint(100, 100)).toPoint();
    settings.uiSize = tmpst.value("size", QSize(1000, 600)).toSize();
    settings.uiScale = tmpst.value("scale", 1.0f).toDouble();
    settings.dbBackupURL = tmpst.value("dbBackupURL", QString()).toString();
    settings.dbBackupSecret = tmpst.value("dbBackupSecret", QString()).toString();

    // Check commandline arguments for configuring backup settings
    if (argc >= 4 && strcmp(argv[1], "config") == 0)
    {
        settings.dbBackupURL = QString(argv[2]);
        settings.dbBackupSecret = QString(argv[3]);
    }

    //
    // Create logfile and data directory
    //
    settings.dataPath = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation);
    QDir path(settings.dataPath);
    if (!path.exists(settings.dataPath))
        path.mkpath(settings.dataPath);

    //
    // Initialize / open SQL database connection
    //
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(settings.dataPath + QDir::separator() + APP_SQLITE_FILE);

    if (!db.open())
    {
        slErrorMsg(
            QObject::tr("Tietokantaa ei voitu avata"),
            QObject::tr("Yhteyttä SQL-tietokantaan ei saatu.<br><br>Virhe: %1<br><br>").
            arg(db.lastError().text())
            );
        return 1;
    }

    QSqlQuery query;
    if (!db.tables().contains("people"))
    {
        query.exec(QStringLiteral(
            "CREATE TABLE people (id INTEGER PRIMARY KEY, "
            "first_name VARCHAR(%1) NOT NULL, "
            "last_name VARCHAR(%2) NOT NULL, "
            "extra_info VARCHAR(%3), "
            "added DATETIME NOT NULL, "
            "updated DATETIME NOT NULL)").
            arg(SQL_LEN_FIRST_NAME).
            arg(SQL_LEN_LAST_NAME).
            arg(SQL_LEN_EXTRA_INFO));

        slCheckAndReportSQLError("CREATE TABLE people", query.lastError(), true);
    }

    if (!db.tables().contains("transactions"))
    {
        query.exec(QStringLiteral(
            "CREATE TABLE transactions ("
            "id INTEGER PRIMARY KEY, "
            "person INT NOT NULL, "
            "value REAL, "
            "added DATETIME NOT NULL)"));

        slCheckAndReportSQLError("CREATE TABLE transactions", query.lastError(), true);
    }

    query.finish();

    SyntilistaMainWindow swin;
    swin.show();
    return sapp.exec();
}


//
// Main application window code
//
SyntilistaMainWindow::SyntilistaMainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::SyntilistaMainWindow)
{
    // Setup UI
    ui->setupUi(this);

    // Restore window size and position
    move(settings.uiPos);
    resize(settings.uiSize);

    // Setup application icon and window title
    setWindowIcon(QIcon(QPixmap(":/img/icon-64.png")));
    setWindowTitle(tr("%1 versio %3").
        arg(tr(APP_NAME)).
        arg(tr(APP_VERSION)));

    // Setup large logo in the main window
    QPixmap logoImage(":/img/logo.png");
    ui->button_LogoImage->setPixmap(logoImage);
    ui->button_LogoImage->setAlignment(Qt::AlignCenter);

    slSetCommonStyleSheet(this);

    // Validator for amount input
    //ui->edit_Amount->setValidator(new QDoubleValidator(0, 1000, 2, this));
    QRegExp vregex("\\d{0,4}[,.]\\d{0,2}|\\d{0,4}");
    ui->edit_Amount->setValidator(new QRegExpValidator(vregex, this));

    // Setup person list filtering and sorting
    peopleSortIndex = 1;
    peopleSortOrder = Qt::AscendingOrder;
    peopleFilter = "";

    model_People = new PersonSQLModel();
    updatePersonList();

    ui->tableview_People->setModel(model_People);
    ui->tableview_People->setColumnHidden(0, true);
    ui->tableview_People->setItemDelegate(new QSqlRelationalDelegate(ui->tableview_People));
    ui->tableview_People->verticalHeader()->setVisible(false);
    ui->tableview_People->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    ui->tableview_People->setSortingEnabled(true);
    ui->tableview_People->sortByColumn(peopleSortIndex, peopleSortOrder);

    connect(
        ui->tableview_People->selectionModel(),
        SIGNAL(currentChanged(const QModelIndex &, const QModelIndex &)),
        this,
        SLOT(selectedPersonChanged(const QModelIndex &, const QModelIndex &)));

    connect(
        ui->tableview_People->horizontalHeader(),
        SIGNAL(sortIndicatorChanged(int, Qt::SortOrder)),
        this,
        SLOT(updateSortOrder(int, Qt::SortOrder)));

    ui->tableview_People->horizontalHeader()->setSortIndicator(1, Qt::AscendingOrder);

    model_Latest = new TransactionSQLModel();
    ui->tableview_Latest->setModel(model_Latest);
    ui->tableview_Latest->setItemDelegate(new QSqlRelationalDelegate(ui->tableview_Latest));
    ui->tableview_Latest->verticalHeader()->setVisible(false);
    ui->tableview_Latest->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);

    setActivePerson(-1);

    // Keyboard shortcuts
    ui->button_Quit->setShortcut(QKeySequence(Qt::Key_F10));
    new QShortcut(QKeySequence(Qt::CTRL + Qt::Key_Q), this, SLOT(on_button_Quit_clicked()));

    ui->button_AddPerson->setShortcut(QKeySequence(Qt::Key_F5));
    ui->button_DeletePerson->setShortcut(QKeySequence(Qt::Key_F8));
    ui->button_EditPerson->setShortcut(QKeySequence(Qt::Key_F6));
    ui->button_ClearFilter->setShortcut(QKeySequence(Qt::Key_Escape));
    ui->button_About->setShortcut(QKeySequence(Qt::Key_F1));

    new QShortcut(QKeySequence(QKeySequence::ZoomIn), this, SLOT(changeUIZoomIn()));
    new QShortcut(QKeySequence(QKeySequence::ZoomOut), this, SLOT(changeUIZoomOut()));
    new QShortcut(QKeySequence(Qt::CTRL + Qt::KeypadModifier + Qt::Key_Plus), this, SLOT(changeUIZoomIn()));
    new QShortcut(QKeySequence(Qt::CTRL + Qt::KeypadModifier + Qt::Key_Minus), this, SLOT(changeUIZoomOut()));
    new QShortcut(QKeySequence(Qt::CTRL + Qt::KeypadModifier + Qt::Key_0), this, SLOT(changeUIZoomReset()));
    new QShortcut(QKeySequence(Qt::CTRL + Qt::Key_0), this, SLOT(changeUIZoomReset()));

    new QShortcut(QKeySequence(Qt::Key_PageUp), this, SLOT(selectRowPrev()));
    new QShortcut(QKeySequence(Qt::Key_PageDown), this, SLOT(selectRowNext()));

    new QShortcut(QKeySequence(Qt::CTRL + Qt::Key_Return), this, SLOT(focusDebtEdit()));

    new QShortcut(QKeySequence(Qt::CTRL + Qt::Key_P), this, SLOT(on_button_Print_clicked()));
}


//
// Application main window destructor
//
SyntilistaMainWindow::~SyntilistaMainWindow()
{
    QSettings tmpst(APP_VENDOR, APP_ID);

    // Save window size and position
    tmpst.setValue("pos", pos());
    tmpst.setValue("size", size());

    // Other settings
    tmpst.setValue("scale", settings.uiScale);
    tmpst.setValue("dbBackupURL", settings.dbBackupURL);
    tmpst.setValue("dbBackupSecret", settings.dbBackupSecret);

    // Free resources
    delete ui;
    delete model_People;
    delete model_Latest;

    // Commit and close database
    QSqlDatabase::database().commit();
    QSqlDatabase::database().close();

    // Back up the database
    backupDatabase();
}


void SyntilistaMainWindow::backupDatabase()
{
    QString dbFilename = settings.dataPath + QDir::separator() + APP_SQLITE_FILE;
    QString backupFilename = APP_SQLITE_FILE;
    backupReply = NULL;
    backupDialog = NULL;

    if (settings.dbBackupURL == QString() || settings.dbBackupURL == "")
    {
        slLog("ERROR", QStringLiteral("Database backup URL not set in configuration."));
        return;
    }

    if (settings.dbBackupSecret == QString() || settings.dbBackupSecret == "")
    {
        slLog("ERROR", QStringLiteral("Database backup secret key not set in configuration."));
        return;
    }

    // Check for network access
    QNetworkAccessManager *manager = new QNetworkAccessManager();
    if (manager->networkAccessible() != QNetworkAccessManager::Accessible)
    {
        slLog("ERROR", QStringLiteral("Network not available, cannot backup the database."));
        return;
    }

    // Attempt to open the database file 
    QFile *file = new QFile(dbFilename);
    if (!file->open(QIODevice::ReadOnly))
    {
        slLog("ERROR", QStringLiteral("Failed to open database file '%1' for backup.").arg(dbFilename));
        return;
    }

    // Okay, we seem to be "go" ..
    slLog("INFO",
        QStringLiteral("Attempting database backup from '%1' to '%2'.").
        arg(dbFilename).arg(settings.dbBackupURL));

    // Create the HTTP POST request
    QHttpMultiPart *multiPart = new QHttpMultiPart(QHttpMultiPart::FormDataType);

    // The "secret" key as POST parameter
    QHttpPart postPart;
    postPart.setHeader(QNetworkRequest::ContentDispositionHeader,
        QVariant("form-data; name=\"secret\";"));
    postPart.setBody(QByteArray(settings.dbBackupSecret.toUtf8()));

    // Actual data as binary octet-stream
    QHttpPart dataPart;
    dataPart.setHeader(QNetworkRequest::ContentTypeHeader,
        QVariant("binary/octet-stream"));

    dataPart.setHeader(QNetworkRequest::ContentDispositionHeader,
        QVariant("form-data; name=\"file\"; filename=\""+ backupFilename +"\""));

    dataPart.setBodyDevice(file);
    file->setParent(multiPart); // we cannot delete the QFile object now, so delete it with the multiPart

    multiPart->append(postPart);
    multiPart->append(dataPart);

    // Attempt to POST the whole thing
    QUrl url(settings.dbBackupURL);
    QNetworkRequest request(url);
    backupReply = manager->post(request, multiPart);
    multiPart->setParent(backupReply);

    connect(
        backupReply,
        SIGNAL(finished()),
        this,
        SLOT(backupFinished()));

    connect(
        backupReply,
        SIGNAL(uploadProgress(qint64, qint64)),
        this,
        SLOT(backupProgress(qint64, qint64)));

    connect(
        backupReply,
        SIGNAL(error(QNetworkReply::NetworkError)),
        this,
        SLOT(backupError(QNetworkReply::NetworkError)));

    // Create progress dialog
    backupDialog = new QProgressDialog(
        tr("Varmuuskopioidaan tietokantaa ..."),
        QString(),
        0,
        100,
        this);

    backupDialog->setAttribute(Qt::WA_DeleteOnClose);
    backupDialog->setAutoClose(false);
    backupDialog->setWindowModality(Qt::ApplicationModal);
    backupDialog->exec();
}


void SyntilistaMainWindow::backupProgress(qint64 bytesSent, qint64 bytesTotal)
{
    if (bytesTotal > 0)
    {
        slLog("INFO",
            QStringLiteral("Backup sent %1 / %2 bytes.").
            arg(bytesSent).
            arg(bytesTotal));

        backupDialog->setValue((bytesSent * 100) / bytesTotal);
    }
}


void SyntilistaMainWindow::backupError(QNetworkReply::NetworkError code)
{
    slLog("ERROR",
        QStringLiteral("Backup failed with network error %1.\n").
        arg(code)
        );
}


void SyntilistaMainWindow::backupFinished()
{
    if (backupReply)
    {
        QVariant status = backupReply->attribute(QNetworkRequest::HttpStatusCodeAttribute);
        if (status.isValid())
        {
            int code = status.toInt();
            switch (code)
            {
                case 200:
                    slLog("INFO", "Backup successful.");
                    break;

                case 403:
                    slLog("ERROR", "Backup server authentication failed. Wrong secret or other invalid settings.");
                    break;

                default:
                    slLog("ERROR",
                        QStringLiteral("Backup server responded with error:\n")+
                        QString::fromUtf8(backupReply->readAll()));
                    break;
            }
        }
    }
    else
    {
        slLog("WARNING", "Backup finished prematurely (failed).");
    }

    backupDialog->close();
}


//
// Helper function for showing messages in the statusbar/line
//
void SyntilistaMainWindow::statusMsg(const QString &msg)
{
    slLog("STATUS", msg);
    ui->statusbar->showMessage(msg);
}


//
// Window scale / zoom changing
//
void SyntilistaMainWindow::changeUIZoomIn()
{
    settings.uiScale += 0.1f;
    slSetCommonStyleSheet(this);
}


void SyntilistaMainWindow::changeUIZoomOut()
{
    settings.uiScale -= 0.1f;
    slSetCommonStyleSheet(this);
}


void SyntilistaMainWindow::changeUIZoomReset()
{
    settings.uiScale = 1.0f;
    slSetCommonStyleSheet(this);
}


//
// Slot for changed selection of person entry
//
void SyntilistaMainWindow::selectedPersonChanged(const QModelIndex &curr, const QModelIndex &prev)
{
    (void) prev;
    int row = curr.row();
    if (row >= 0)
    {
        const QAbstractItemModel *model = curr.model();
        setActivePerson(model->data(model->index(row, 0)).toInt());
        focusDebtEdit();
    }
    else
        setActivePerson(-1);
}


//
// Set currently active person to given SQL id
//
void SyntilistaMainWindow::setActivePerson(qint64 id)
{
    currPerson.id = id;

    ui->button_EditPerson->setEnabled(id >= 0);

    if (id >= 0)
    {
        if (!slGetPersonInfo(id, currPerson))
        {
            statusMsg(tr("Virhe! Ei henkilöä ID:llä #%1").arg(id));
        }
        else
        {
            ui->personGB->setEnabled(true);
            ui->label_PersonName->setText(currPerson.lastName +", "+ currPerson.firstName);

            ui->label_BalanceValue->setText(slMoneyValueToStr(currPerson.balance));
            ui->label_BalanceValue->setStyleSheet(currPerson.balance < 0 ? "color: red;" : "color: green;");
            ui->button_PayFullDebt->setEnabled(currPerson.balance < 0);

            QSqlQuery query;
            query.prepare("SELECT id,value,added FROM transactions WHERE person=? ORDER BY added DESC LIMIT 5");
            query.addBindValue(id);
            query.exec();
            slCheckAndReportSQLError("SELECT transactions for tableview_Latest", query.lastError());

            model_Latest->setQuery(query);

            model_Latest->setHeaderData(0, Qt::Horizontal, tr("ID"));
            model_Latest->setHeaderData(1, Qt::Horizontal, tr("Summa"));
            model_Latest->setHeaderData(2, Qt::Horizontal, tr("Aika"));

            ui->tableview_Latest->setModel(model_Latest);
            ui->tableview_Latest->setColumnHidden(0, true);
            ui->tableview_Latest->verticalHeader()->setVisible(false);
            ui->tableview_Latest->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);

            slSetCommonStyleSheet(this);
            return; // Ugly
        }
    }

    // In case of id < 0 or errors ..
    ui->personGB->setEnabled(false);
    ui->edit_Amount->clear();
    ui->label_BalanceValue->setText("--");
    ui->label_BalanceValue->setStyleSheet(NULL);
    ui->label_PersonName->setText("???");
    ui->tableview_Latest->setModel(NULL);
    slSetCommonStyleSheet(this);
}


//
// Slot for changing person list sort order
//
void SyntilistaMainWindow::updateSortOrder(int index, Qt::SortOrder order)
{
    peopleSortIndex = index;
    peopleSortOrder = order;
    updatePersonList();
}


void SyntilistaMainWindow::on_button_Quit_clicked()
{
    close();
}


void SyntilistaMainWindow::on_button_About_clicked()
{
    new AboutWindow(this);
}


void SyntilistaMainWindow::on_button_Print_clicked()
{
    // Create a printer object and force some basic settings
    QPrinter printer(QPrinter::HighResolution);
    printer.setPageSize(QPageSize(QPageSize::A4));
    printer.setColorMode(QPrinter::GrayScale);
    printer.setResolution(300);

    // We need to get the page count here, and also need it again in
    // printDocument(), but there is no sane way to pass that there,
    // so some code duplication is unfortunately necessary
    SLPageInfo pinfo;
    pinfo.npages = 0;
    pinfo.nlinesPerPage = 0;

    QPixmap tmpPixmap(1000, 1300);
    QPainter tmpPainter;
    tmpPainter.begin(&tmpPixmap);
    bool ret = printDocumentPage(pinfo, true, -1, &tmpPainter, &printer);
    tmpPainter.end();

    if (!ret)
    {
        // Some kind of error occured
        return;
    }


    // Set available pages
    printer.setFromTo(1, pinfo.npages);

    // Create print preview dialog and show it
    QPrintPreviewDialog preview(&printer, this);
    preview.setWindowModality(Qt::ApplicationModal);
    preview.setSizeGripEnabled(true);

    connect(
        &preview,
        SIGNAL(paintRequested(QPrinter *)),
        this,
        SLOT(printDocument(QPrinter *)));

    preview.exec();
}


void SyntilistaMainWindow::printDocument(QPrinter *printer)
{
    // Create progress dialog
    QProgressDialog progress(
        tr("Tulostetaan ..."),
        tr("Peruuta"),
        0,
        1,
        this);

    progress.setWindowModality(Qt::ApplicationModal);

    // Again, get the page info here .. we need the number of lines per page
    SLPageInfo pinfo;
    pinfo.npages = 0;
    pinfo.nlinesPerPage = 0;

    QPixmap tmpPixmap(1000, 1300);
    QPainter tmpPainter;
    tmpPainter.begin(&tmpPixmap);
    bool ret = printDocumentPage(pinfo, true, -1, &tmpPainter, printer);
    tmpPainter.end();

    if (!ret)
        return;

    // If from and to are 0, we are supposed to print all pages
    if (printer->fromPage() == 0 && printer->toPage() == 0)
        printer->setFromTo(1, pinfo.npages);

    // Setup rest of the progress dialog here
    progress.setMinimum(printer->fromPage() - 1);
    progress.setMaximum(printer->toPage());

    // Begin painting to the printer (or preview)
    QPainter painter;
    painter.begin(printer);

    bool firstPage = true;
    for (int page = printer->fromPage(); page <= printer->toPage(); page++)
    {
        if (!firstPage)
            printer->newPage();

        qApp->processEvents();
        if (progress.wasCanceled())
            break;

        printDocumentPage(pinfo, false, page, &painter, printer);
        progress.setValue(page);
        firstPage = false;
    }

    painter.end();
}


bool SyntilistaMainWindow::printDocumentPage(SLPageInfo &pinfo, const bool getPageInfo, const int npage, QPainter *pt, QPrinter *printer)
{
    // Form the SQL query for list of users
    QString querystr = QStringLiteral(
        "SELECT id,first_name,last_name,extra_info,added,updated, "
        "(SELECT TOTAL(value) FROM transactions WHERE transactions.person=people.id) AS balance "
        "FROM people ORDER BY last_name ASC,first_name ASC");

    // If we are fetching page info, we need to process all entries
    if (!getPageInfo)
    {
        // Otherwise we can limit to given page number
        querystr += QStringLiteral(" LIMIT %1 OFFSET %2").
            arg(pinfo.nlinesPerPage).
            arg((npage - 1) * pinfo.nlinesPerPage);
    }

    QSqlQuery query;
    query.prepare(querystr);
    query.setForwardOnly(true);
    query.exec();

    if (!slCheckAndReportSQLError("printDocumentPage()", query.lastError()))
    {
        slErrorMsg(
            tr("SQL-tietokantavirhe"),
            tr("Tietokantaa selattaessa tapahtui virhe."));

        return false;
    }

    pt->save();
    if (!getPageInfo)
    {
        pt->scale(
            printer->pageRect().width() / 1000.0f,
            printer->pageRect().height() / 1300.0f);
    }

    QFont font1("Arial", 5);
    SLDrawContext ctx(pt);
    ctx.setFont(font1);

    int nline = 0;
    while (query.next())
    {
        PersonInfo info;
        slGetPersonInfoRec(query, info);

        // Check for end of page
        // KLUDGE for now
        if (getPageInfo && ctx.lfq(10) >= 1300.0f)
        {
            if (nline > pinfo.nlinesPerPage)
                pinfo.nlinesPerPage = nline;

            pinfo.npages++;
            nline = 0;
        }

        if (nline == 0)
        {
            // If we are at the start of the page, we shall draw a header
            pt->setBrush(QBrush(Qt::black));
            pt->setPen(QPen(Qt::black, 3, Qt::SolidLine, Qt::RoundCap, Qt::RoundJoin));

            ctx.setPos(0, 0);
            ctx.drawText(   5,  180, tr("Etunimi"));
            ctx.drawText( 200,  230, tr("Sukunimi"));
            ctx.drawText( 450,  190, tr("Lisätty"));
            ctx.drawText( 650,  190, tr("Päivitetty"));
            ctx.drawText( 870,  120, tr("Tase"));
            ctx.lf();

            pt->drawLine(0, ctx.m_pos.y(), 1000, ctx.m_pos.y());

            ctx.move(0, 5);
        }

        // Draw a gray bar under every second line
        if (nline % 2 == 0)
        {
            pt->fillRect(
                0,
                ctx.m_pos.y() - 1,
                1000,
                ctx.boundRect().height() + 4,
                QColor(0, 0, 0, 40));
        }

        ctx.drawText(   5,  180, info.firstName);
        ctx.drawText( 200,  230, info.lastName);
        ctx.drawText( 450,  190, slDateTimeToStr(info.added));
        ctx.drawText( 650,  190, slDateTimeToStr(info.updated));
        ctx.drawText( 870,  120, slMoneyValueToStr(info.balance), Qt::AlignRight);

        ctx.lf(10);
        nline++;
    }

    query.finish();

    if (getPageInfo)
    {
        if (nline > pinfo.nlinesPerPage)
            pinfo.nlinesPerPage = nline;

        pinfo.npages++;
    }
    else
    {
        ctx.setPos(0, 1240);
        ctx.drawText(0, 1000,
            tr("Sivu %1 / %2 (%3 / %4)").
            arg(npage - printer->fromPage() + 1).
            arg(printer->toPage() - printer->fromPage() + 1).
            arg(npage).
            arg(printer->toPage()),
            Qt::AlignHCenter);
    }

    pt->restore();
    return true;
}


void SyntilistaMainWindow::on_button_DeletePerson_clicked()
{
    if (currPerson.id <= 0)
    {
        statusMsg(tr("Ei valittua henkilöä!"));
        return;
    }

    PersonInfo info;
    if (!slGetPersonInfo(currPerson.id, info))
    {
        statusMsg(tr("Virhe! Ei henkilöä ID:llä #%1").arg(currPerson.id));
        return;
    }

    QMessageBox dlg;
    slSetCommonStyleSheet(&dlg);
    dlg.setText(tr("Varmistus"));
    dlg.setInformativeText(
        tr("<br>Haluatko varmasti poistaa henkilön:<br>"
        "<br>"
        "<b>'%1, %2'</b> <i>(ID #%3)</i>?<br>"
        "<br>"
        "Tämä poistaa sekä henkilön ja hänen koko tapahtumahistoriansa PYSYVÄSTI!<br>").
        arg(info.lastName).arg(info.firstName).arg(info.id));

    dlg.setTextFormat(Qt::RichText);
    dlg.setIcon(QMessageBox::Question);
    dlg.setStandardButtons(QMessageBox::Yes | QMessageBox::No);
    dlg.setButtonText(QMessageBox::Yes, tr("Kyllä"));
    dlg.setButtonText(QMessageBox::No, tr("Ei / peruuta"));
    dlg.setDefaultButton(QMessageBox::No);

    if (dlg.exec() == QMessageBox::Yes)
    {
        int rv = model_People->deletePerson(info.id);
        updatePersonList();
        setActivePerson(-1);
        if (rv != 0)
        {
            slErrorMsg(tr("SQL-tietokantavirhe"),
                tr("Henkilön tietoja poistettaessa tapahtui virhe #%1.").
                arg(rv));
        }
        else
        {
            statusMsg(tr("Henkilö '%1 %2' (ID #%3) poistettu.").
                arg(info.firstName).arg(info.lastName).
                arg(info.id));
        }
    }
}


void SyntilistaMainWindow::on_button_AddPerson_clicked()
{
    EditPerson *person = new EditPerson(this);
    person->setPerson(-1);
}


void SyntilistaMainWindow::on_button_EditPerson_clicked()
{
    if (currPerson.id >= 0)
    {
        EditPerson *person = new EditPerson(this);
        person->setPerson(currPerson.id);
    }
}


void SyntilistaMainWindow::on_tableview_People_doubleClicked(const QModelIndex &curr)
{
    int row = curr.row();
    if (row >= 0)
    {
        const QAbstractItemModel *model = curr.model();
        setActivePerson(model->data(model->index(row, 0)).toInt());

        EditPerson *person = new EditPerson(this);
        person->setPerson(currPerson.id);
    }
    else
        setActivePerson(-1);
}


void SyntilistaMainWindow::on_button_ClearFilter_clicked()
{
    ui->edit_PersonFilter->clear();
    ui->edit_PersonFilter->setFocus(Qt::ShortcutFocusReason);
}


void SyntilistaMainWindow::focusDebtEdit()
{
    if (currPerson.id >= 0)
        ui->edit_Amount->setFocus(Qt::ShortcutFocusReason);
}


void SyntilistaMainWindow::selectRowPrev()
{
    QItemSelectionModel *sel = ui->tableview_People->selectionModel();
    int row = sel->currentIndex().row() - 1;
    if (row < 0)
        row = 0;

    sel->setCurrentIndex(model_People->index(row, 0),
        QItemSelectionModel::ClearAndSelect|QItemSelectionModel::Rows);
}


void SyntilistaMainWindow::selectRowNext()
{
    QItemSelectionModel *sel = ui->tableview_People->selectionModel();
    int row = sel->currentIndex().row() + 1;
    if (row >= model_People->rowCount())
        row = model_People->rowCount() - 1;

    sel->setCurrentIndex(model_People->index(row, 0),
        QItemSelectionModel::ClearAndSelect|QItemSelectionModel::Rows);
}


//
// Update visible person list/query based on the current
// filtering and sorting settings.
//
void SyntilistaMainWindow::updatePersonList()
{
    static const QString queryBase =
        "SELECT id,last_name,first_name,"
        "(SELECT TOTAL(value) FROM transactions WHERE transactions.person=people.id) AS balance,"
        "updated FROM people";

    QString queryOrderDir, queryOrderBy;

    // Sort order
    if (peopleSortOrder == Qt::AscendingOrder)
        queryOrderDir = QStringLiteral("ASC");
    else
        queryOrderDir = QStringLiteral("DESC");

    // Sort by which column
    switch (peopleSortIndex)
    {
        case 1:
        case 2:
            queryOrderBy = QStringLiteral(" ORDER BY last_name ") + queryOrderDir + QStringLiteral(",first_name ") + queryOrderDir;
            break;

        case 3:
            queryOrderBy = QStringLiteral(" ORDER BY balance ") + queryOrderDir;
            break;

        case 4:
            queryOrderBy = QStringLiteral(" ORDER BY updated ") + queryOrderDir;
            break;

        default:
            queryOrderBy = "";
    }

    // Are we filtering or not?
    QSqlQuery query;
    if (peopleFilter != "")
    {
        // Filter by name(s)
        QString tmp = "%"+ peopleFilter +"%";
        query.prepare(queryBase +" WHERE first_name LIKE ? OR last_name LIKE ?" + queryOrderBy);

        query.addBindValue(tmp);
        query.addBindValue(tmp);
    }
    else
    {
        // No filter
        query.prepare(queryBase + queryOrderBy);
    }

    // Execute the query and update model
    slCheckAndReportSQLError("updatePersonList() before exec", query.lastError());
    query.exec();
    slCheckAndReportSQLError("updatePersonList() after exec", query.lastError());

    model_People->setQuery(query);

    model_People->setHeaderData(0, Qt::Horizontal, tr("ID"));
    model_People->setHeaderData(1, Qt::Horizontal, tr("Sukunimi"));
    model_People->setHeaderData(2, Qt::Horizontal, tr("Etunimi"));
    model_People->setHeaderData(3, Qt::Horizontal, tr("Tase"));
    model_People->setHeaderData(4, Qt::Horizontal, tr("Muutettu"));

    updateMiscValues();
}


//
// Update some values in the UI
//
void SyntilistaMainWindow::updateMiscValues()
{
    // Update total balance value
    QSqlQuery query;
    query.prepare(QStringLiteral("SELECT TOTAL(value) FROM transactions AS balance"));
    query.exec();
    if (slCheckAndReportSQLError("updateMiscValues() get total balance query", query.lastError()) &&
        query.next())
    {
        double balance = query.value(0).toDouble();;
        ui->label_TotalBalanceValue->setText(slMoneyValueToStr(balance));
        ui->label_TotalBalanceValue->setStyleSheet(balance < 0 ? "color: red;" : "color: green;");
    }

    // Update number of people
    query.finish();
    query.prepare(QStringLiteral("SELECT COUNT(*) FROM people"));
    query.exec();
    if (slCheckAndReportSQLError("updateMiscValues() get people count", query.lastError()) &&
        query.next())
    {
        ui->label_NumPeopleValue->setText(query.value(0).toString());
    }
}


//
// Update the list of people when filter parameter changes
//
void SyntilistaMainWindow::on_edit_PersonFilter_textChanged(const QString &str)
{
    peopleFilter = slCleanupStr(str);
    updatePersonList();
}


//
// Add one transaction to given person id
//
int SyntilistaMainWindow::addTransaction(qint64 id, double value, PersonInfo &info)
{
    if (!slGetPersonInfo(id, info))
        return -1;

    QSqlDatabase::database().transaction();

    QSqlQuery query;
    query.prepare("INSERT INTO transactions (person,value,added) VALUES (?,?,?)");
    query.addBindValue(id);
    query.addBindValue(value);
    query.addBindValue(QDateTime::currentDateTimeUtc());
    query.exec();
    if (!slCheckAndReportSQLError(QStringLiteral("addTransaction(%1, %2)").arg(id).arg(value), query.lastError(), true))
    {
        QSqlDatabase::database().rollback();
        return -2;
    }

    query.prepare("UPDATE people SET updated=? WHERE id=?");
    query.addBindValue(QDateTime::currentDateTimeUtc());
    query.addBindValue(id);
    query.exec();
    if (!slCheckAndReportSQLError("addTransaction update timestamp", query.lastError(), true))
    {
        QSqlDatabase::database().rollback();
        return -3;
    }

    QSqlDatabase::database().commit();

    updateMiscValues();

    return 0;
}


int SyntilistaMainWindow::addTransactionGUI(qint64 id, bool debt, double value)
{
    PersonInfo info;

    // Check if person is selected
    if (id <= 0)
        return -1;

    // Check value
    if (value == 0)
    {
        QString tmp = (debt ? "lisätty" : "vähennetty");
        statusMsg("Velkaa ei "+ tmp +" koska summaa ei määritetty.");
        return 1;
    }

    // Perform transaction insert
    int ret = addTransaction(id, debt ? -value : value, info);
    if (ret == 0)
    {
        // All ok, clear amount entry and update person data
        ui->edit_Amount->clear();
        if (info.id == currPerson.id)
            setActivePerson(info.id);

        model_People->updateModel();

        QString str;
        if (debt)
        {
            str = tr("Lisättiin velkaa %1 EUR henkilölle '%2 %3' (#%4).").
                arg(slMoneyValueToStr(value)).
                arg(info.firstName).
                arg(info.lastName).
                arg(info.id);
        }
        else
        {
            str = tr("Vähennettiin velkaa %1 EUR henkilöltä '%2 %3' (#%4).").
                arg(slMoneyValueToStr(value)).
                arg(info.firstName).
                arg(info.lastName).
                arg(info.id);
        }
        statusMsg(str);
    }
    else
    {
        slErrorMsg(
            tr("SQL-tietokantavirhe"),
            tr("Tietokantaan tapahtumaa lisättäessa tapahtui virhe #%1.").
            arg(ret));
    }

    return ret;
}


void SyntilistaMainWindow::on_button_AddDebt_clicked()
{
    addTransactionGUI(currPerson.id, true, slMoneyStrToValue(ui->edit_Amount->text()));
}


void SyntilistaMainWindow::on_button_PayDebt_clicked()
{
    addTransactionGUI(currPerson.id, false, slMoneyStrToValue(ui->edit_Amount->text()));
}


void SyntilistaMainWindow::on_button_PayFullDebt_clicked()
{
    if (currPerson.balance < 0)
        addTransactionGUI(currPerson.id, false, -currPerson.balance);
    else
    {
        statusMsg(
            tr("Valitulla henkilöllä '%1, %2' ei ole velkaa.").
            arg(currPerson.lastName).
            arg(currPerson.firstName));
    }
}


//
// Edit person dialog
//
EditPerson::EditPerson(QWidget *parent) :
    QDialog(parent),
    ui(new Ui::EditPerson)
{
    ui->setupUi(this);

    slSetCommonStyleSheet(this);

    setModal(true);
    setAttribute(Qt::WA_DeleteOnClose);
    show();
    activateWindow();
    raise();
    setFocus();

    model_Transactions = new TransactionSQLModel();
    ui->tableview_Transactions->setModel(model_Transactions);
    ui->tableview_Transactions->setItemDelegate(new QSqlRelationalDelegate(ui->tableview_Transactions));
    ui->tableview_Transactions->verticalHeader()->setVisible(false);
    ui->tableview_Transactions->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);

    ui->edit_FirstName->setMaxLength(SQL_LEN_FIRST_NAME);
    ui->edit_LastName->setMaxLength(SQL_LEN_LAST_NAME);

    connect(
        ui->textedit_ExtraInfo,
        SIGNAL(textChanged()),
        this,
        SLOT(on_textedit_ExtraInfo_textChanged()));

    validateForm();
}


EditPerson::~EditPerson()
{
    delete ui;
    delete model_Transactions;
}


void EditPerson::statusMsg(const QString &msg)
{
    dynamic_cast<SyntilistaMainWindow *>(parent())->statusMsg(msg);
}


bool EditPerson::validateForm()
{
    selPerson.firstName = slCleanupStr(ui->edit_FirstName->text());
    selPerson.lastName = slCleanupStr(ui->edit_LastName->text());
    selPerson.extraInfo = ui->textedit_ExtraInfo->document()->toPlainText();
    bool extraInfoValid = selPerson.extraInfo.length() < SQL_LEN_EXTRA_INFO;

    ui->textedit_ExtraInfo->setStyleSheet(!extraInfoValid ? "background-color: red;" : NULL);
    ui->edit_FirstName->setStyleSheet(selPerson.firstName == "" ? "background-color: red;" : NULL);
    ui->edit_LastName->setStyleSheet(selPerson.lastName == "" ? "background-color: red;" : NULL);

    return selPerson.firstName != "" && selPerson.lastName != "" && extraInfoValid;
}


void EditPerson::on_button_Cancel_clicked()
{
    close();
}


void EditPerson::on_button_OK_clicked()
{
    if (!validateForm())
    {
        slErrorMsg(
            tr("Virhe!"),
            tr("Vaaditut kentät (etunimi, sukunimi) eivät ole täytetty tai lisätietojen pituus on liian suuri."));

        return;
    }

    if (selPerson.id >= 0)
    {
        QSqlQuery person;
        person.prepare("SELECT * FROM people WHERE id <> ? AND first_name=? AND last_name=?");
        person.addBindValue(selPerson.id);
        person.addBindValue(selPerson.firstName);
        person.addBindValue(selPerson.lastName);
        person.exec();

        slCheckAndReportSQLError("SELECT check for existing person by same name (UPDATE)", person.lastError());

        if (person.next())
        {
            slErrorMsg(
                tr("Virhe!"),
                tr("Ei pysty! Samalla nimellä '%1 %2' on olemassa jo henkilö!").
                arg(selPerson.firstName).arg(selPerson.lastName));
            return;
        }

        dynamic_cast<SyntilistaMainWindow *>(parent())->model_People->updatePerson(selPerson);
        dynamic_cast<SyntilistaMainWindow *>(parent())->setActivePerson(selPerson.id);

        statusMsg(tr("Päivitettiin henkilö '%1 %2' (#%3).").
            arg(selPerson.firstName).arg(selPerson.lastName).arg(selPerson.id));
    }
    else
    {
        QSqlQuery person;
        person.prepare("SELECT * FROM people WHERE first_name=? AND last_name=?");
        person.addBindValue(selPerson.firstName);
        person.addBindValue(selPerson.lastName);
        person.exec();

        slCheckAndReportSQLError("SELECT check for existing person by same name (ADD)", person.lastError());

        if (person.next())
        {
            slErrorMsg(
                tr("Virhe!"),
                tr("Ei pysty! Samalla nimellä '%1 %2' on olemassa jo henkilö!").
                arg(selPerson.firstName).arg(selPerson.lastName));

            return;
        }

        qint64 nid = dynamic_cast<SyntilistaMainWindow *>(parent())->model_People->addPerson(selPerson);
        if (nid < 0)
        {
            slErrorMsg(
                tr("Virhe!"),
                tr("Tietokannan käsittelyssä tapahtui virhe (#%1).").
                arg(nid));
        }
        else
        {
            dynamic_cast<SyntilistaMainWindow *>(parent())->updatePersonList();
            dynamic_cast<SyntilistaMainWindow *>(parent())->setActivePerson(nid);
            dynamic_cast<SyntilistaMainWindow *>(parent())->focusDebtEdit();

            statusMsg(tr("Lisättiin uusi henkilö '%1 %2'.").
                arg(selPerson.firstName).arg(selPerson.lastName));
        }
    }

    close();
}


void EditPerson::on_edit_FirstName_textChanged(const QString &arg1)
{
    (void) arg1;
    validateForm();
}


void EditPerson::on_edit_LastName_textChanged(const QString &arg1)
{
    (void) arg1;
    validateForm();
}


void EditPerson::on_textedit_ExtraInfo_textChanged()
{
    validateForm();
}


void EditPerson::clearForm()
{
    ui->edit_FirstName->clear();
    ui->edit_LastName->clear();
    ui->textedit_ExtraInfo->document()->clear();
    ui->edit_FirstName->setFocus();
}


void EditPerson::setPerson(qint64 id)
{
    selPerson.id = id;

    if (id >= 0)
    {
        PersonInfo pinfo;
        if (!slGetPersonInfo(id, pinfo))
        {
            statusMsg(tr("Virhe! Ei henkilöä ID:llä #%1").arg(id));
            // Intentional fall-through below
        }
        else
        {
            ui->edit_FirstName->setText(pinfo.firstName);
            ui->edit_LastName->setText(pinfo.lastName);
            ui->textedit_ExtraInfo->document()->setPlainText(pinfo.extraInfo);

            QSqlQuery query;
            query.prepare("SELECT id,value,added FROM transactions WHERE person=? ORDER BY added DESC");
            query.addBindValue(pinfo.id);
            query.exec();
            slCheckAndReportSQLError("SELECT transactions for tableview_Transactions", query.lastError());

            model_Transactions->setQuery(query);

            model_Transactions->setHeaderData(0, Qt::Horizontal, tr("ID"));
            model_Transactions->setHeaderData(1, Qt::Horizontal, tr("Summa"));
            model_Transactions->setHeaderData(2, Qt::Horizontal, tr("Aika"));

            ui->tableview_Transactions->setModel(model_Transactions);
            ui->tableview_Transactions->setColumnHidden(0, true);

            return; // Ugly
        }
    }

    // In case of id < 0 or errors ..
    clearForm();
    ui->tableview_Transactions->setModel(NULL);
}


//
// Custom SQL models
//
PersonSQLModel::PersonSQLModel(QObject *parent) : QSqlQueryModel(parent)
{
}


QVariant PersonSQLModel::data(const QModelIndex &index, int role) const
{
    QVariant value = QSqlQueryModel::data(index, role);

    if (value.isValid() && role == Qt::DisplayRole)
    {
        switch (index.column())
        {
            case 3:
                return slMoneyValueToStr(value.toDouble());

            case 4:
                return slDateTimeToStr(value.toDateTime());
        }
    }

    if (index.column() == 3 && role == Qt::ForegroundRole)
    {
        double val = QSqlQueryModel::data(index, Qt::DisplayRole).toDouble();
        if (val < 0)
            return QVariant::fromValue(QColor(Qt::red));
        else
            return QVariant::fromValue(QColor(Qt::green));
    }

    return value;
}


int PersonSQLModel::updatePerson(const PersonInfo &info)
{
    QSqlQuery np;

    np.prepare("UPDATE people SET first_name=?,last_name=?,extra_info=?,updated=? WHERE id=?");
    np.addBindValue(info.firstName);
    np.addBindValue(info.lastName);
    np.addBindValue(info.extraInfo);
    np.addBindValue(QDateTime::currentDateTimeUtc());
    np.addBindValue(info.id);
    np.exec();

    if (!slCheckAndReportSQLError("PersonSQLModel::updatePerson()", np.lastError()))
        return -1;

    QSqlDatabase::database().commit();
    updateModel();
    return 0;
}


qint64 PersonSQLModel::addPerson(const PersonInfo &info)
{
//    beginInsertRows(QModelIndex(), rowCount(), rowCount());

    QSqlQuery np;
    np.prepare("INSERT INTO people (first_name,last_name,extra_info,added,updated) VALUES (?,?,?,?,?)");
    np.addBindValue(info.firstName);
    np.addBindValue(info.lastName);
    np.addBindValue(info.extraInfo);
    np.addBindValue(QDateTime::currentDateTimeUtc());
    np.addBindValue(QDateTime::currentDateTimeUtc());
    np.exec();

    if (!slCheckAndReportSQLError("PersonSQLModel::addPerson()", np.lastError()))
        return -1;

    QSqlDatabase::database().commit();

    QVariant idp = np.lastInsertId();

//    endInsertRows();
    updateModel();
    return idp.isValid() ? idp.toInt() : -2;
}


int PersonSQLModel::deletePerson(qint64 id)
{
    QSqlDatabase::database().transaction();
    QSqlQuery del;

    del.prepare("DELETE FROM people WHERE id=?");
    del.addBindValue(id);
    del.exec();

    if (!slCheckAndReportSQLError("delete user", del.lastError()))
    {
        QSqlDatabase::database().rollback();
        return -1;
    }

    del.prepare("DELETE FROM transactions WHERE person=?");
    del.addBindValue(id);
    del.exec();

    if (!slCheckAndReportSQLError("delete user transactions", del.lastError()))
    {
        QSqlDatabase::database().rollback();
        return -2;
    }

    QSqlDatabase::database().commit();
    updateModel();
    return 0;
}


void PersonSQLModel::updateModel()
{
    query().exec();
    emit dataChanged(index(0, 0), index(rowCount(), columnCount()));
}


TransactionSQLModel::TransactionSQLModel(QObject *parent) : QSqlQueryModel(parent)
{
}


QVariant TransactionSQLModel::data(const QModelIndex &index, int role) const
{
    QVariant value = QSqlQueryModel::data(index, role);

    if (value.isValid() && role == Qt::DisplayRole)
    {
        switch (index.column())
        {
            case 1:
                return slMoneyValueToStr(value.toDouble());

            case 2:
                return slDateTimeToStr(value.toDateTime());
        }
    }

    if (index.column() == 1 && role == Qt::ForegroundRole)
    {
        double val = QSqlQueryModel::data(index, Qt::DisplayRole).toDouble();
        if (val < 0)
            return QVariant::fromValue(QColor(Qt::red));
        else
            return QVariant::fromValue(QColor(Qt::green));
    }

    return value;
}


void TransactionSQLModel::updateModel()
{
    query().exec();
    emit dataChanged(QModelIndex(), QModelIndex());
}


//
// About window
//
AboutWindow::AboutWindow(QWidget *parent) :
    QDialog(parent),
    ui(new Ui::AboutWindow)
{
    ui->setupUi(this);

    ui->label_Logo->setPixmap(QPixmap(":/img/icon-64.png"));
    ui->label_Logo->setAlignment(Qt::AlignCenter);

    ui->label_About->setWordWrap(true);
    ui->label_About->setTextFormat(Qt::RichText);
    ui->label_About->setText(tr(
        "<h1>%1 v%2</h1>"
        "<p>"
        "<b>Ohjelmoinut ja kehittänyt Matti Hämäläinen &lt;ccr@tnsp.org&gt;<br>"
        "(C) Copyright 2017 Tecnic Software productions (TNSP)</b>"
        "</p>"
        "<p>"
        "Kehitetty Raahen kaupungin Hanketoiminta ja Kehittäminen -yksikön "
        "alaisuudessa Café Kampuksen käyttöön."
        "</p>"
        "<p>"
        "Ohjelma ja sen lähdekoodi ovat uudemman BSD-tyylisen lisenssin alaisia. "
        "Lue ohjelman mukana tullut tiedosto \"COPYING\" (tai \"COPYING.txt\") "
        "nähdäksesi täydelliset lisenssiehdot."
        "</p>"
        "<p>AppDataPath: %3</p>"
        ).
        arg(tr(APP_NAME)).
        arg(APP_VERSION).
        arg(settings.dataPath)
        );

    ui->label_ShortCuts->setText(tr(
        "<h1>Pikanäppäimet</h1>"
        "<table>"
        "<tr><td><b>F1</b></td><td>Tämä tietoikkuna</td></tr>"
        "<tr><td><b>CTRL + Q</b></td><td>Ohjelman lopetus</td></tr>"
        "<tr><td><b>CTRL + Page Up</b></td><td>Suurenna ohjelman tekstejä/käyttöliittymää</td></tr>"
        "<tr><td><b>CTRL + Page Down</b></td><td>Pienennä ohjelman tekstejä/käyttöliittymää</td></tr>"
        "<tr></tr>"
        "<tr><td><b>Esc</b></td><td>Tyhjennä 'Etsi / suodata' kenttä ja siirry siihen</td></tr>"
        "<tr><td><b>CTRL + Enter</b></td><td>Siirry summan syöttökenttään</td></tr>"
        "<tr><td><b>Page Up</b></td><td>Siirry ylös henkilölistassa</td></tr>"
        "<tr><td><b>Page Down</b></td><td>Siirry alas henkilölistassa</td></tr>"
        "<tr></tr>"
        "<tr><td><b>F5</b></td><td>Lisää uusi henkilö</td></tr>"
        "<tr><td><b>F6</b></td><td>Muokkaa henkilöä</td></tr>"
        "<tr><td><b>F8</b></td><td>Poista henkilö</td></tr>"
        "</table>"
        ));

//    slSetCommonStyleSheet(this);
    setModal(true);
    setAttribute(Qt::WA_DeleteOnClose);
    show();
    activateWindow();
    raise();
    setFocus();
}


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


void AboutWindow::on_button_Close_clicked()
{
    close();
}