database_plugin_fotomon.cpp

Zobrazit dokumentaci tohoto souboru.

 
#include "database_plugin_fotomon.h"
#include "msettings.h"
#include "msqlquery.h"
#include "json.h"
#include "pdebug.h"
#include <QUuid>
#include <QSqlError>
#include <QCryptographicHash>
#include <QFileInfo>
#include <QFile>
#include <QDir>
 
using namespace Db::Plugins;
 
 
DatabasePluginFotomon::~DatabasePluginFotomon() {
    close();
}
 
 
DatabasePluginFotomon::DatabasePluginFotomon(QObject *parent) : Db::Plugins::DatabasePlugin(parent) {
    Q_ASSERT(parent != NULL);
    setObjectName("DatabasePluginFotomon");
    m_temporaryTableTicketsCreated = false;
}
 
 
bool DatabasePluginFotomon::open() {
    m_db = QSqlDatabase::addDatabase("QPSQL", QUuid::createUuid().toString().toUtf8());
 
    m_db.setDatabaseName ( m_databasename );
    m_db.setHostName     ( m_hostname     );
    m_db.setPort         ( m_port         );
    m_db.setUserName     ( m_username     );
    m_db.setPassword     ( m_password     );
 
    if (!m_db.open()) {
        PDEBUG << "Cannot connect to database Fotomon";
        PDEBUG << m_db.lastError().text();
        return false;
        }
 
    return true;
}
 
 
bool DatabasePluginFotomon::close() {
    m_db.close();
    return true;
}
 
 
void DatabasePluginFotomon::begin() {
    MSqlQuery q(m_db);
    q.exec("begin;");
}
 
 
void DatabasePluginFotomon::commit() {
    MSqlQuery q(m_db);
    q.exec("commit;");
}
 
 
QVariant DatabasePluginFotomon::currval(const QString& sequence) {
    MSqlQuery q(m_db);
    QVariant cv;
    q.prepare(R"'(select currval(:sequence);)'");
    q.bindValue(":sequence", sequence);
    q.exec();
    if (q.next()) {
        cv = q.value(0);
        }
    return cv;
}
 
 
void DatabasePluginFotomon::changePassword(const QString& login, const QString& oldpassword, const QString& newpassword) {
    Q_UNUSED(login);
    Q_UNUSED(oldpassword);
    Q_UNUSED(newpassword);
}
 
  
QList<Dbt::Users> DatabasePluginFotomon::authenticate(const QString& login, const QString& password) {
    QString md5 = QString::fromUtf8(QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Md5).toHex());
    QList<Dbt::Users> list;
    MSqlQuery q(m_db);
    q.prepare(R"X(
        select "user", login, name, language
            from users
            where password = :password
              and login = :login
              and is_active
              and not is_deleted
            ;
        )X");
    q.bindValue(":login", login);
    q.bindValue(":password", md5);
    q.exec();
    while (q.next()) {
        int i=0;
        Dbt::Users x;
        x.user  = q.value(i++).toInt();
        x.login = q.value(i++).toString();
        x.name  = q.value(i++).toString();
        x.lang  = q.value(i++).toString();
        list << x;
        }
    return list;
}
 
 
QList<Dbt::Users> DatabasePluginFotomon::users(int id) {
    Q_UNUSED(id);
    QList<Dbt::Users> list;
    MSqlQuery q(m_db);
    q.prepare(R"X(select "user", login, name, language from users where is_active and not is_deleted;)X");
    q.exec();
    while (q.next()) {
        int i=0;
        Dbt::Users x;
        x.user  = q.value(i++).toInt();
        x.login = q.value(i++).toString();
        x.name  = q.value(i++).toString();
        x.lang  = q.value(i++).toString();
        list << x;
        }
    return list;
}
 
 
void DatabasePluginFotomon::createTemporaryTableTickets(int ticket, bool all) {
    Q_UNUSED(all);
    if (m_temporaryTableTicketsCreated) { return; }
    m_temporaryTableTicketsCreated = true;
    MSqlQuery q(m_db);
    q.exec(R"'(
        create temporary table temporary_tickets (
            ticket      int,
            type        int,
            system      int,
            category    int,
            date        timestamp with time zone,
            "user"      int,
            description text
            );
        )'");
 
    q.prepare(R"'(
        with
        active_tickets as (
            select t.ticket
                from tickets t
                left join lateral (select tn.ticket, tn.status from tickets_notes tn where tn.ticket = t.ticket order by ticket, date desc limit 1) tl using (ticket)
                left join tickets_status ts on (tl.status = ts.status)
                where (:ticket1 <= 0 or :ticket2 = t.ticket)
                  and (ts.closed is null or not ts.closed)
            ),
        users_systems as (
            select us.system 
                from users_systems us, users u, systems s
                where u."user" = :user 
                  and u."user" = us."user"
                  and us.system = s.system
                  and s.show_on_web and s.show_on_panel
                  and u.is_active and not u.is_deleted
            )
        insert into temporary_tickets (ticket, type, system, category, date, "user", description)
        select t.ticket, t.type, t.system, t.category, t.date, t."user", 
            case when description = '' then formal_description->0->'description'->>:lang else description end as description
        from tickets t, users u
        where u."user" = t."user"
          and t.ticket in (select ticket from active_tickets)
          and t.system in (select system from users_systems)
              and (:ticket3 <= 0 or :ticket4 = t.ticket)
        )'");
    q.bindValue(":user", userId());
    q.bindValue(":lang", userLang());
    q.bindValue(":ticket1", ticket);
    q.bindValue(":ticket2", ticket);
    q.bindValue(":ticket3", ticket);
    q.bindValue(":ticket4", ticket);
    q.exec();
}
 
 
void DatabasePluginFotomon::createTemporaryTableCategories() {
    MSqlQuery q(m_db);
 
    q.exec("create temporary table timesheet_categories "
            "(type int, system int, category int, description text, parent_type int)"
            ";");
 
    // Vybere všechny typy knih
    q.prepare("insert into timesheet_categories (type, system, category, description, parent_type) "
            " select type, null, null, formal_description->>:lang, 1 "
            " from tickets_types;");
    q.bindValue(":lang", userLang());   
    q.exec();
 
    // Vybere všechny kombinace záznamů typ-system
    q.prepare("insert into timesheet_categories (type, system, category, description, parent_type) "
            " select distinct on (tt.type, s.system) tt.type, s.system, null, s.description, 2 "
            " from tickets_categories_types_systems tt, systems s "
            " where s.show_on_panel and s.show_on_web "
            " and s.system in (select system from users_systems where \"user\" = :user); "
            );
    q.bindValue(":user", userId());
    q.exec();
 
    // Vybere všechny kombinace záznamů typ-system-kategorie, kde EXISTUJE záznam typ-system
    q.prepare("insert into timesheet_categories (type, system, category, description, parent_type) "
            " select tt.type, s.system, tc.category, tc.formal_description->>:lang, 3 "
            " from tickets_categories_types_systems tt, systems s, tickets_categories tc "
            " where tt.system = s.system "
            " and tc.category = tt.category "
            " and s.show_on_panel and s.show_on_web "
            " and s.system in (select system from users_systems where \"user\" = :user); "
            );
    q.bindValue(":lang", userLang());
    q.bindValue(":user", userId());
    q.exec();
 
    // Vybere všechny kombinace záznamů typ-system-kategorie, kde NEEXISTUJE záznam typ-system
    q.prepare("insert into timesheet_categories (type, system, category, description, parent_type) "
            " select tt.type, s.system, tc.category, tc.formal_description->>:lang, 4 "
            " from systems s, tickets_types tt, tickets_categories tc "
            " where s.show_on_panel and s.show_on_web "
            " and s.system in (select system from users_systems where \"user\" = :user) "
            " and not exists (select 1 "
            "       from tickets_categories_types_systems tcts "
            "      where tcts.type = tt.type "
            "        and tcts.system = s.system ); "
            );
    q.bindValue(":user", userId());
    q.bindValue(":lang", userLang());
    q.exec();
 
}
 
 
QString DatabasePluginFotomon::categoryKey(const QVariant& type, const QVariant& system, const QVariant& category, int parent_type) {
    if (parent_type == 1) { 
        return QString(R"'({"type":%1})'")
            .arg(type.toInt())
            ;
        }
 
    if (parent_type == 2) {
        return QString(R"'({"type":%1,"system":%2})'")
            .arg(type.toInt())
            .arg(system.toInt())
            ;
        }
 
    if (parent_type == 3) {
        return QString(R"'({"type":%1,"system":%2,"category":%3})'")
            .arg(type.toInt())
            .arg(system.toInt())
            .arg(category.toInt())
            ;
        }
 
    if (parent_type == 4) {
        return QString(R"'({"type":%1,"system":%2,"category":%3})'")
            .arg(type.toInt())
            .arg(system.toInt())
            .arg(category.toInt())
            ;
        }
 
    return QString();
}
 
 
QString DatabasePluginFotomon::parentCategoryKey(const QVariant& type, const QVariant& system, const QVariant& category, int parent_type) {
    Q_UNUSED(category);
    if (parent_type == 1) { 
        return QString();
        }
 
    if (parent_type == 2) {
        return QString(R"'({"type":%1})'")
            .arg(type.toInt())
            ;
        }
 
    if (parent_type == 3) {
        return QString(R"'({"type":%1,"system":%2})'")
            .arg(type.toInt())
            .arg(system.toInt())
            ;
        }
 
    if (parent_type == 4) {
        return QString(R"'({"type":%1,"system":%2})'")
            .arg(type.toInt())
            .arg(system.toInt())
            ;
        }
 
    return QString();
}
 
 
QList<Dbt::Categories> DatabasePluginFotomon::categoriesToRoot(const QString& id) {
    Q_UNUSED(id);
    QList<Dbt::Categories> list;
    return list;
}
 
 
QList<Dbt::Categories> DatabasePluginFotomon::categories(const QString& id) {
    Q_UNUSED(id);
    createTemporaryTableCategories();
 
    QList<Dbt::Categories> list;
    MSqlQuery q(m_db);
 
    q.exec("select type, system, category, description, parent_type from timesheet_categories;");
    while (q.next()) {
        Dbt::Categories x;
        QVariant type = q.value(0);
        QVariant system = q.value(1);
        QVariant category = q.value(2);
        QVariant description = q.value(3);
        int parent_type = q.value(4).toInt();
 
        x.category          =       categoryKey(type, system, category, parent_type);
        x.parent_category   = parentCategoryKey(type, system, category, parent_type);
        x.description       = description.toString();
        list << x;
        }
 
    PDEBUG << "pocet kategorii" << list.size();
    return list;
}
 
 
QList<Dbt::Categories> DatabasePluginFotomon::subcategories(const QString& id) {
    Q_UNUSED(id);
    QList<Dbt::Categories> list;
    return list;
}
 
 
QList<Dbt::Categories> DatabasePluginFotomon::siblingcategories(const QString& id) {
    Q_UNUSED(id);
    QList<Dbt::Categories> list;
    return list;
}
 
 
QList<Dbt::StatusOrder> DatabasePluginFotomon::statusOrder(const QString& id) {
    Q_UNUSED(id);
    createTemporaryTableCategories();
 
    QList<Dbt::StatusOrder> list;
    MSqlQuery q(m_db);
 
    q.exec("select c.type, c.system, c.category, c.description, c.parent_type, "
        " s.status_from, s.status_to "
        " from timesheet_categories c, tickets_types_status s"
        " where c.type = s.type "
        ";");
    while (q.next()) {
        Dbt::StatusOrder x;
        QVariant type = q.value(0);
        QVariant system = q.value(1);
        QVariant category = q.value(2);
        QVariant description = q.value(3);
        int parent_type = q.value(4).toInt();
 
        x.previous_status   = q.value(5);
        x.next_status       = q.value(6);
        x.category          = categoryKey(type, system, category, parent_type);
        list << x;
        }
 
    PDEBUG << "pocet prechodu" << list.size();
    return list;
 
}
 
 
QList<Dbt::Statuses> DatabasePluginFotomon::statuses(const QString& id) {
    Q_UNUSED(id);
    QList<Dbt::Statuses> list;
    MSqlQuery q(m_db);
 
    q.prepare("select ts.status, ts.formal_description->>:lang, ts.abbreviation, ts.color "
        " from tickets_status ts "
        ";");
    q.bindValue(":lang", userLang());   
    q.exec();
    while (q.next()) {
        Dbt::Statuses x;
        int i=0;
        x.status = q.value(i++).toString();
        x.description = q.value(i++).toString();
        x.abbreviation = q.value(i++).toString();
        x.color = q.value(i++).toString();
        list << x;
        }
 
    return list;
}
 
 
QList<Dbt::Statuses> DatabasePluginFotomon::statuses(const QString& category, const QString& prevstatus) {
    Q_UNUSED(category);
    Q_UNUSED(prevstatus);
    return statuses(QString());
}
 
 
QList<Dbt::StatusTemplates> DatabasePluginFotomon::statusTemplates(int id) {
    MSqlQuery q(m_db);
    QList<Dbt::StatusTemplates> list;
 
    /* Too large document :-(
    createTemporaryTableCategories();
    q.prepare(R"X(select t.note_template, t.status, c.category, t.code, t.title->>:lang, t.description
        from timesheet_categories c, tickets_notes_templates t
        where c.type = t.type
          and (t.note_template = :id1 or 0 > :id2)
        )X");
    */
 
    q.prepare(R"X(select note_template, status, null, code, title->>:lang, description
        from tickets_notes_templates
        where note_template = :id1 or 0 > :id2;
        )X");
 
 
    q.bindValue(":id1", id);
    q.bindValue(":id2", id);
    q.bindValue(":lang", userLang());
    q.exec();
    while (q.next()) {
        Dbt::StatusTemplates x;
        int i=0;
        x.id = q.value(i++).toInt();
        x.status = q.value(i++).toString();
        x.category = q.value(i++).toString();
        x.code = q.value(i++).toString();
        x.title = q.value(i++).toString();
        x.description = q.value(i++).toString();
        list << x;
        }
 
    return list;
}
 
 
QList<Dbt::Tickets> DatabasePluginFotomon::tickets(bool all) {
    return tickets(-1, all);
}
 
 
QList<Dbt::Tickets> DatabasePluginFotomon::tickets(int ticket, bool all) {
    PDEBUG;
    createTemporaryTableTickets(ticket, all);
    QList<Dbt::Tickets> list;
    MSqlQuery q(m_db);
 
    q.prepare(R"X(select ticket, type, system, 1, date, description from temporary_tickets;)X");
    q.exec();
    while (q.next()) {
        Dbt::Tickets x;
        int i=0;
        x.ticket = q.value(i++);
        QVariant type = q.value(i++);
        QVariant system = q.value(i++);
        QVariant category = q.value(i++);
        x.category = categoryKey(type, system, category, 3);
        x.date = q.value(i++).toDateTime();
        x.description = q.value(i++).toString();
        list << x;
        }
    return list;
}
 
 
QList<Dbt::Tickets> DatabasePluginFotomon::tickets(const Dbt::Categories&, bool all) {
    return tickets(-1, all);
}
 
 
QList<Dbt::TicketsVw> DatabasePluginFotomon::ticketsVw(const Dbt::Categories&, bool all) {
    return ticketsVw(-1, all);
}
 
 
QList<Dbt::TicketsVw> DatabasePluginFotomon::ticketsVw(bool all) {
    Q_UNUSED(all);
    return ticketsVw(-1, all);
}
 
 
QList<Dbt::TicketsVw> DatabasePluginFotomon::ticketsVw(int ticket, bool all) {
    createTemporaryTableTickets(ticket, all);
    QList<Dbt::Tickets> list1;
    QList<Dbt::TicketsVw> list;
    MSqlQuery q(m_db);
 
    q.prepare(R"X(select ticket, type, system, category, date, description, "user" from temporary_tickets;)X");
    q.exec();
    while (q.next()) {
        Dbt::TicketsVw x;
        int i=0;
        x.ticket = q.value(i++);
        QVariant type = q.value(i++);
        QVariant system = q.value(i++);
        QVariant category = q.value(i++);
        x.category = categoryKey(type, system, category, 3);
        x.date = q.value(i++).toDateTime();
        x.description = q.value(i++).toString();
        x.user = q.value(i++).toInt();
        list1 << x;
        }
    for (int i=0; i<list1.size(); i++) {
        Dbt::TicketsVw x = list1[i];
        x.timesheets = ticketTimesheets(list1[i].ticket.toInt(), all);
        x.statuses = ticketStatus(list1[i].ticket.toInt(), all);
        x.values = ticketValues(list1[i].ticket.toInt(), all);
        x.files = ticketFiles(list1[i].ticket.toInt(), all);
        list << x;
        }
    
    return list;
}
 
 
QVariant DatabasePluginFotomon::save(const Dbt::Tickets& data) {
    MSqlQuery q(m_db);
 
    QVariantMap x = JSON::data(data.category.toByteArray()).toMap();
    int type = x["type"].toInt();
    int system = x["system"].toInt();
    int category = x["category"].toInt();
 
    bool found = false;
    if (!data.created) {
        q.prepare(R"'(select 1 from tickets where ticket = :ticket;)'");
        q.bindValue(":ticket", data.ticket);
        q.exec();
        found = q.next();
        }
 
    if (!data.created && found) {
        q.prepare(R"'(
            update tickets set
                type = :type,
                system = :system,
                category = :category,
                date = :date,
                price = :price,
                description = :description,
               "user" = :user
                where ticket = :ticket
            )'");
        q.bindValue(":type", type);
        q.bindValue(":system", system);
        q.bindValue(":category", category);
        q.bindValue(":date", data.date);
        q.bindValue(":price", data.price);
        q.bindValue(":description", data.description);
        q.bindValue(":user", data.user);
        q.bindValue(":ticket", data.ticket);
        q.exec();
        return QVariant(data.ticket);
        }
      
    if (data.created || !found) {
        q.prepare(R"'(
            insert into tickets (type, system, category, date, price, description, "user")
                        values (:type, :system, :category, :date, :price, :description, :user);
            )'");
        q.bindValue(":type", type);
        q.bindValue(":system", system);
        q.bindValue(":category", category);
        q.bindValue(":date", data.date);
        q.bindValue(":price", data.price);
        q.bindValue(":description", data.description);
        q.bindValue(":user", data.user);
        q.exec();
        return currval("tickets_ticket_seq");
        }
 
    Q_UNREACHABLE();
    qFatal("Should not happen");
    return QVariant();
 
}
 
template<typename T>
QList<T> remapTicket(const QList<T>& input, int ticket) {
    QList<T> list;
    QListIterator<T> iterator(input);
    while (iterator.hasNext()) {
        T x = iterator.next();
        x.ticket = ticket;
        if (ticket <= 0) { x.id = 0; }
        list << x;
        }
    return list;
}
 
 
QVariant DatabasePluginFotomon::save(const Dbt::TicketsVw& data) {
    MSqlQuery q(m_db);
    q.begin();
    int ticket = save(dynamic_cast<const Dbt::Tickets&>(data)).toInt();
    save(remapTicket(data.timesheets, ticket));
    save(remapTicket(data.statuses, ticket));
    save(remapTicket(data.values, ticket));
    save(remapTicket(data.files, ticket));
    q.commit();
 
    return QVariant();
}
 
 
void DatabasePluginFotomon::remove(const Dbt::Tickets& id) {
    MSqlQuery q(m_db);
    q.prepare(R"'(delete from tickets where ticket = :id;)'");
    q.bindValue(":id", id.ticket);
    q.exec();
}
 
 
QList<Dbt::TicketStatus> DatabasePluginFotomon::ticketStatus(int ticket, bool all) {
    createTemporaryTableTickets(ticket, all);
    QList<Dbt::TicketStatus> list;
    MSqlQuery q(m_db);
 
    q.prepare(R"'(
        select tn.note, tn.ticket, t."user", tn.date, tn.status,
                case when tn.description != '' then tn.description else tn.formal_description->0->'description'->>'cs' end as description, 
                tn.formal_description,
                ts.description, ts.color
            from tickets_notes tn, temporary_tickets t, tickets_status ts
            where tn.ticket = t.ticket
              and ts.status = tn.status
              and (:ticket1 <= 0 or :ticket2 = t.ticket)
            ;
        )'");
    q.bindValue(":user", userId());
    q.bindValue(":lang", userLang());   
    q.bindValue(":ticket1", ticket);
    q.bindValue(":ticket2", ticket);
    q.exec();
    while (q.next()) {
        Dbt::TicketStatus x;
        int i=0;
        x.id            = q.value(i++).toInt();
        x.ticket        = q.value(i++).toInt();
        x.user          = q.value(i++).toInt();
        x.date          = q.value(i++).toDateTime();
        x.status        = q.value(i++).toString();
        x.description   = q.value(i++).toString();
        x.description2  = JSON::data(q.value(i++).toString().toUtf8()).toMap();
        x.status_description = q.value(i++).toString();
        x.status_color       = q.value(i++).toString();
        // bool status_closed;
        // bool status_can_be_run;
        // bool status_ignored;
        list << x;
        }
    return list;
}
 
 
QList<Dbt::TicketStatus> DatabasePluginFotomon::ticketStatus(bool all) {
    Q_UNUSED(all);
    return ticketStatus(-1, all);
}
 
 
QList<Dbt::TicketStatus> DatabasePluginFotomon::ticketStatus(int id) {
    Q_UNUSED(id);
    QList<Dbt::TicketStatus> list;
    return list;
}
 
 
QVariant DatabasePluginFotomon::save(const Dbt::TicketStatus& data) {
    MSqlQuery q(m_db);
 
    bool found = false;
    if (!data.created) {
        q.prepare(R"'(select 1 from tickets_notes where note = :id;)'");
        q.bindValue(":id", data.id);
        q.exec();
        found = q.next();
        }
 
    if (!data.created && found) {
        QByteArray description2 = JSON::json(data.description2);
        if (description2 == "{}") { description2 = ""; }
        q.prepare(QString(R"'(
            update tickets_notes set
                ticket = :ticket,
               "user" = :user,
                date = :date,
                description = :description,
                formal_description = %1,
                status = :status
                where note = :id
            )'").arg((description2=="") ? "null" : QString("'%1'").arg(QString::fromUtf8(description2)) ) );
        q.bindValue(":id", data.id);
        q.bindValue(":user", data.user);
        q.bindValue(":ticket", data.ticket);
        q.bindValue(":date", data.date);
        q.bindValue(":description", data.description);
        q.bindValue(":status", data.status);
        q.exec();
        return QVariant(data.id);
        }
 
    if (data.created || !found) {
        QByteArray description2 = JSON::json(data.description2);
        if (description2 == "{}") { description2 = ""; }
        q.prepare(QString(R"'(
            insert into tickets_notes (ticket, "user", date, description, status, formal_description)
                values (:ticket, :user, :date, :description, :status, %1)
            )'").arg((description2=="") ? "null" : QString("'%1'").arg(QString::fromUtf8(description2)) ) );
        q.bindValue(":user", data.user);
        q.bindValue(":ticket", data.ticket);
        q.bindValue(":date", data.date);
        q.bindValue(":description", data.description);
        q.bindValue(":status", data.status);
        q.exec();
 
        return currval("tickets_notes_note_seq");
        }
 
    Q_UNREACHABLE();
    return QVariant();
}
 
 
QList<Dbt::TicketTimesheets> DatabasePluginFotomon::ticketTimesheets(int ticket, bool all) {
    createTemporaryTableTickets(ticket, all);
    QList<Dbt::TicketTimesheets> list;
    MSqlQuery q(m_db);
    q.prepare(R"'(
        select tt.id, tt.ticket, tt."user", tt.date_from, tt.date_to
            from temporary_tickets t, ticket_timesheets tt
            where t.ticket = tt.ticket
              and t.ticket = :ticket
            ;
        )'");
    q.bindValue(":user", userId());
    q.bindValue(":ticket", ticket);
    q.exec();
    while (q.next()) {
        int i=0;
        Dbt::TicketTimesheets x;
        x.id            = q.value(i++).toInt();
        x.ticket        = q.value(i++).toInt();
        x.user          = q.value(i++).toInt();
        x.date_from     = q.value(i++).toDateTime();
        x.date_to       = q.value(i++).toDateTime();
        list << x;
        }
    return list;
}
 
 
QList<Dbt::TicketTimesheets> DatabasePluginFotomon::ticketTimesheets(int id) {
    QList<Dbt::TicketTimesheets> list;
    MSqlQuery q(m_db);
    q.prepare(R"'(
        select tt.id, tt.ticket, tt."user", tt.date_from, tt.date_to
            from ticket_timesheets tt, tickets t, users_categories uc
            where t.ticket = tt.ticket
              and t.category = uc.category
              and uc."user" = :user
              and :id = tt.id
            ;
        )'");
    q.bindValue(":user", userId());
    q.bindValue(":id", id);
    q.exec();
    while (q.next()) {
        int i=0;
        Dbt::TicketTimesheets x;
        x.id            = q.value(i++).toInt();
        x.ticket        = q.value(i++).toInt();
        x.user          = q.value(i++).toInt();
        x.date_from     = q.value(i++).toDateTime();
        x.date_to       = q.value(i++).toDateTime();
        list << x;
        }
    return list;
}
 
 
QList<Dbt::TicketTimesheets> DatabasePluginFotomon::ticketTimesheets(bool all) {
    return ticketTimesheets(-1, all);
}
 
 
QList<Dbt::TicketTimesheets>  DatabasePluginFotomon::runningTimesheets(int ticket) {
    Q_UNUSED(ticket);
    return QList<Dbt::TicketTimesheets>();
}
 
 
QList<Dbt::TicketTimesheets>  DatabasePluginFotomon::startTimesheet(int ticket) {
    Q_UNUSED(ticket);
    return QList<Dbt::TicketTimesheets>();
}
 
 
QList<Dbt::TicketTimesheets>  DatabasePluginFotomon::stopTimesheet(int ticket) {
    Q_UNUSED(ticket);
    return QList<Dbt::TicketTimesheets>();
}
 
 
QList<Dbt::TicketTimesheets>  DatabasePluginFotomon::toggleTimesheet(int ticket) {
    Q_UNUSED(ticket);
    return QList<Dbt::TicketTimesheets>();
}
 
 
QVariant DatabasePluginFotomon::save(const Dbt::TicketTimesheets& data) {
    MSqlQuery q(m_db);
 
    bool found = false;
    if (!data.created) {
        q.prepare(R"'(select 1 from ticket_timesheets where id = :id;)'");
        q.bindValue(":id", data.id);
        q.exec();
        found = q.next();
        }
 
    if (!data.created && found) {
        q.prepare(R"'(
            update ticket_timesheets set
                ticket = :ticket,
               "user" = :user,
                date_from = :date_from,
                date_to = :date_to
                where id = :id
            )'");
        q.bindValue(":id", data.id);
        q.bindValue(":user", data.user);
        q.bindValue(":ticket", data.ticket);
        q.bindValue(":date_from", data.date_from);
        q.bindValue(":date_to", data.date_to);
        q.exec();
        return QVariant(data.id);
        }
 
 
    if (data.created || !found) {
        q.prepare(R"'(
            insert into ticket_timesheets (ticket, "user", date_from, date_to)
                values (:ticket, :user, :date_from, :date_to)
            )'");
        q.bindValue(":user", data.user);
        q.bindValue(":ticket", data.ticket);
        q.bindValue(":date_from", data.date_from);
        q.bindValue(":date_to", data.date_to);
        q.exec();
 
        return currval("ticket_timesheets_id_seq");
        }
 
    Q_UNREACHABLE();
    return QVariant();
}
 
 
void DatabasePluginFotomon::remove(const Dbt::TicketTimesheets& id) {
    MSqlQuery q(m_db);
    q.prepare(R"'(delete from ticket_timesheets where id = :id;)'");
    q.bindValue(":id", id.id);
    q.exec();
}
 
 
QByteArray DatabasePluginFotomon::fileContent(const QString& filename) {
    QByteArray content;
    QDir dir(MSETTINGS->dbFilesDirectory());
    QString fullfilename = dir.absoluteFilePath(filename);
    QFile file(fullfilename);
    if (file.open(QIODevice::ReadOnly)) {
        content = file.readAll();
        file.close();
        }
    return content;
}
 
 
QList<Dbt::TicketFiles> DatabasePluginFotomon::ticketFiles(int ticket, bool all) {
    createTemporaryTableTickets(ticket, all);
    QList<Dbt::TicketFiles> list;
    MSqlQuery q(m_db);
 
    q.prepare(R"'(
        select f.file, f.upload_date, tf.ticket, f.origname, f.filetype, f.filename
            from temporary_tickets t, tickets_files tf, files f
            where t.ticket = tf.ticket
              and tf.file = f.file;
            ;
        )'");
    q.exec();
    while (q.next()) {
        int i=0;
        Dbt::TicketFiles x;
        x.id        = q.value(i++).toInt();
        x.user      = userId();
        x.date      = q.value(i++).toDateTime();
        x.ticket    = q.value(i++).toInt();
        x.name      = q.value(i++).toString();
        x.type      = q.value(i++).toString();
        x.content   = DatabasePluginFotomon::fileContent(q.value(i++).toString());
        list << x;
        }
 
    return list;
}
 
 
QList<Dbt::TicketFiles> DatabasePluginFotomon::ticketFiles(int id) {
    QList<Dbt::TicketFiles> list;
    MSqlQuery q(m_db);
 
    q.prepare(R"'(
        select f.file, f.upload_date, tf.ticket, f.origname, f.filetype, f.filename
            from tickets_files tf, files f, users u, tickets t, users_categories uc
            where f.file = :id
              and tf.file = f.file
              and t.ticket = tf.ticket
              and t.category = uc.category
              and t."user" = :user
            ;
        )'");
    q.bindValue(":id", id);
    q.bindValue(":user", userId());
    q.exec();
    while (q.next()) {
        int i=0;
        Dbt::TicketFiles x;
        x.id        = q.value(i++).toInt();
        x.user      = userId();
        x.date      = q.value(i++).toDateTime();
        x.ticket    = q.value(i++).toInt();
        x.name      = q.value(i++).toString();
        x.type      = q.value(i++).toString();
        x.content   = DatabasePluginFotomon::fileContent(q.value(i++).toString());
        list << x;
        }
 
    return list;
}
 
 
QList<Dbt::TicketFiles> DatabasePluginFotomon::ticketFiles(bool all) {
    return ticketFiles(-1, all);
}
 
 
QString DatabasePluginFotomon::suffixFromType(const QString& type) {
    if (type == "image/png") { return ".png"; }
    if (type == "image/jpeg") { return ".jpeg"; }
    if (type == "image/gif") { return ".gif"; }
    return "";
 
}
 
QVariant DatabasePluginFotomon::save(const Dbt::TicketFiles& data) {
    MSqlQuery q(m_db);
 
    bool found = false;
    q.prepare(R"'(select file from tickets_files where file = :file and ticket = :ticket;)'");
    q.bindValue(":id", data.id);
    q.bindValue(":ticket", data.ticket);
    q.exec();
    found = q.next();
 
    // Save file
    QString suffix = suffixFromType(data.type);
    QString webalizedName = QString("ta%1-%2%3")
        .arg(data.ticket.toInt())
        .arg(QUuid::createUuid().toString().remove(QChar('{')).remove(QChar('}')))
        .arg(suffix)
        ;
    QDir dir(MSETTINGS->dbFilesDirectory());
    QString filename = dir.absoluteFilePath(webalizedName);
    QFile file(filename);
    if (file.open(QIODevice::WriteOnly)) {
        file.write(data.content);   
        file.close();
        }
    QFileInfo info(filename);
    PDEBUG << webalizedName << filename << info.fileName();
 
    if (found) {
        PDEBUG << "update files" << data.id << data.ticket;
        q.prepare(R"'(
            update files set 
                filename = :filename,
                upload_date = :date,
                origname = :name,
                filetype = :type,
                filesize = :size
                where file = :fileid
            )'");
        q.bindValue(":filename", info.fileName());
        q.bindValue(":date", data.date);
        q.bindValue(":name", data.name + suffix);
        q.bindValue(":type", data.type);
        q.bindValue(":size", info.size());
        q.bindValue(":fileid", data.id);
        q.bindValue(":ticket", data.ticket);
        q.exec();
        return QVariant(data.id);
        }
 
    if (!found) {
        q.prepare(R"'(
            insert into files (filename, upload_date, origname, filetype, filesize)
                 values (:filename, :date, :name, :type, :sizeticket);
            )'");
        q.bindValue(":filename", info.fileName());
        q.bindValue(":date", data.date);
        q.bindValue(":name", data.name + suffix);
        q.bindValue(":type", data.type);
        q.bindValue(":size", info.size());
        q.exec();
 
        q.prepare(R"'(
            insert into tickets_files (ticket, file) select :ticket, currval('files_file_seq');
            )'");
        q.bindValue(":ticket", data.ticket);
        q.exec();
 
        QVariant rc = currval("files_file_seq");
        PDEBUG << "insert files" << rc.toInt() << data.ticket;
        return rc;
        }
 
    Q_UNREACHABLE();
    return QVariant();
}
 
 
void DatabasePluginFotomon::remove(const Dbt::TicketFiles& id) {
    MSqlQuery q(m_db);
    q.prepare(R"'(delete from ticket_images where id = :id;)'");
    q.bindValue(":id", id.id);
    q.exec();
}
 
 
QList<Dbt::TicketValues> DatabasePluginFotomon::ticketValues(int ticket, bool all) {
    createTemporaryTableTickets(ticket, all);
    QList<Dbt::TicketValues> list;
    MSqlQuery q(m_db);
    q.prepare(R"'(
        select tv.id, tt.ticket, tv.name, tv.value, tv."user", tv.date
            from temporary_tickets tt, ticket_values tv
            where tt.ticket = tv.ticket
            ;
        )'");
    q.exec();
    while (q.next()) {
        int i=0;
        Dbt::TicketValues x;
        x.id            = q.value(i++).toInt();
        x.ticket        = q.value(i++).toInt();
        x.name          = q.value(i++).toString();
        x.value         = JSON::data(q.value(i++).toByteArray());
        x.user          = q.value(i++).toInt();
        x.date          = q.value(i++).toDateTime();
        list << x;
        }
    return list;
}
 
 
QList<Dbt::TicketValues> DatabasePluginFotomon::ticketValues(int id) {
    QList<Dbt::TicketValues> list;
    MSqlQuery q(m_db);
    q.prepare(R"'(
        select tv.id, tv.ticket, tv.date, tv.name, tv.value, tv."user"
            from tickets tt, ticket_values tv, users_categories uc
            where tt.ticket = tv.ticket
              and tt.category = uc.category
              and uc.user = :user
              and :id = tv.id
            ;
        )'");
    q.bindValue(":id", id);
    q.bindValue(":user", userId());
    q.exec();
    while (q.next()) {
        int i=0;
        Dbt::TicketValues x;
        x.id            = q.value(i++).toInt();
        x.ticket        = q.value(i++).toInt();
        x.date          = q.value(i++).toDateTime();
        x.name          = q.value(i++).toString();
        x.value         = JSON::data(q.value(i++).toByteArray());
        x.user          = q.value(i++).toInt();
        list << x;
        }
    return list;
}
 
 
QList<Dbt::TicketValues> DatabasePluginFotomon::ticketValues(bool all) {
    return ticketValues(-1, all);
}
 
 
QVariant DatabasePluginFotomon::save(const Dbt::TicketValues& data) {
    MSqlQuery q(m_db);
 
    bool found = false;
    if (!data.created) {
        q.prepare(R"'(select 1 from ticket_values where id = :id;)'");
        q.bindValue(":id", data.id);
        q.exec();
        found = q.next();
        }
 
    if (!data.created && found) {
        q.prepare(QString(R"'(
            update ticket_values set
                ticket = :ticket,
               "user" = :user,
                date = :date,
                name = :name,
                value = '%1'
                where id = :id
            )'").arg(QString::fromUtf8(JSON::json(data.value))));
        q.bindValue(":id", data.id);
        q.bindValue(":ticket", data.ticket);
        q.bindValue(":user", data.user);
        q.bindValue(":date", data.date);
        q.bindValue(":name", data.name);
        // q.bindValue(":value", JSON::json(data.value));
        q.exec();
        return QVariant(data.id);
        }
 
    if (data.created || !found) {
        q.prepare(QString(R"'(
            insert into ticket_values (ticket, "user", date, name, value)
                select :ticket, :user, :date, :name, '%1'
                where not exists (select 1 from ticket_values where id = :id);
            )'").arg(QString::fromUtf8(JSON::json(data.value))));
        q.bindValue(":id", data.id);
        q.bindValue(":ticket", data.ticket);
        q.bindValue(":user", data.user);
        q.bindValue(":date", data.date);
        q.bindValue(":name", data.name);
        // q.bindValue(":value", JSON::json(data.value));
        q.exec();
 
        return currval("ticket_values_id_seq");
        }
 
    Q_UNREACHABLE();
    return QVariant();
}
 
 
void DatabasePluginFotomon::remove(const Dbt::TicketValues& id) {
    MSqlQuery q(m_db);
    q.prepare(R"'(delete from ticket_values where id = :id;)'");
    q.bindValue(":id", id.id);
    q.exec();
}
 
 
QList<Dbt::UsersCategories> DatabasePluginFotomon::usersCategories(int id, int user, const QString& category) {
    Q_UNUSED(id);
    Q_UNUSED(user);
    Q_UNUSED(category);
    QList<Dbt::UsersCategories> list;
    return list;
}
 
 
QList<Dbt::ClientSettings> DatabasePluginFotomon::clientSettings() {
    QList<Dbt::ClientSettings> list;
    MSqlQuery q(m_db);
 
    q.prepare(R"'(
        select multiple_timesheets, show_price, can_change_category, edit_categories, 
               show_multiple_timesheets, show_show_price, show_can_change_category, show_edit_categories
        from ticket_client_settings;
        )'");
    q.exec();
    while (q.next()) {
        Dbt::ClientSettings x;
        int i = 0;
        x.multiple_timesheets = q.value(i++).toBool();
        x.show_price = q.value(i++).toBool();
        x.can_change_category = q.value(i++).toBool();
        x.edit_categories = q.value(i++).toBool();
        x.show_multiple_timesheets = q.value(i++).toBool();
        x.show_show_price = q.value(i++).toBool();
        x.show_can_change_category = q.value(i++).toBool();
        x.show_edit_categories = q.value(i++).toBool(); 
        list << x;
        }
    return list;
}
 
 
QList<Dbt::CategoriesOverview>  DatabasePluginFotomon::categoriesOverview(const QStringList& statuses) {
    Q_UNUSED(statuses);
    QList<Dbt::CategoriesOverview> list;
    return list;
}
 
 
QList<Dbt::Overview> DatabasePluginFotomon::overview(const QString& category, const QStringList& statuses) {
    Q_UNUSED(statuses);
    QList<Dbt::Overview> list;
    return list;
}
 
 
QList<Dbt::Overview> DatabasePluginFotomon::overview(const QString& overviewId) {
    Q_UNUSED(overviewId);
    QList<Dbt::Overview> list;
    return list;
}
 
 
QList<Dbt::OverviewList> DatabasePluginFotomon::overviewList() {
    QList<Dbt::OverviewList> list;
    return list;
}