QFD2/QFD/dbService/UserConfigService.cpp

#include "UserConfigService.h"

#include "SqlDBHelper.h"
#include <QDebug>

UserConfigService::UserConfigService(QObject *parent) { }

//////////////////////用户工程配置-start////////////////////
int UserConfigService::AddUserConfig(const UserConfig &cfg)
{
    int returnId = -1;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        InsertQuery query     = t.insertInto("t_user_config (user_id, engineer_id, "
                                         "weight, create_time, update_time,user_name, is_valid)");
        NonQueryResult result = query.values(cfg.userId, cfg.engineerId, cfg.weight, cfg.createTime, cfg.updateTime,
                                             cfg.userName, cfg.isValid)
                                        .exec();
        t.commit();
        returnId = result.lastInsertId().toInt();

    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
        returnId = -1;
    }
    return returnId;
}

//添加配置信息
bool UserConfigService::AddUserConfigList(QList<UserConfig *> cfgList)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        for (auto cfg : cfgList) {
            InsertQuery query     = t.insertInto("t_user_config (user_id, engineer_id, "
                                             "weight, create_time, update_time,user_name)");
            NonQueryResult result = query.values(cfg->userId, cfg->engineerId, cfg->weight * 100, cfg->createTime,
                                                 cfg->updateTime, cfg->userName)
                                            .exec();
            t.commit();
        }
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

int UserConfigService::QueryUserWeightSum(int engineerId)
{
    int returnId = -1;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        QString selectSql = QString("SELECT sum(weight) from t_user_config "
                                    " WHERE  engineer_id = %1 and is_valid =1")
                                    .arg(engineerId);
        QueryResult queryResult = t.execQuery(selectSql);
        if (queryResult.next()) {
            returnId = queryResult.value(0).toInt();
        }

    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
        returnId = -1;
    }
    return returnId;
}

int UserConfigService::QueryUserWeightSum(int engineerId, int userId)
{
    int returnId = -1;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        QString selectSql = QString("SELECT sum(weight) from t_user_config "
                                    " WHERE  engineer_id = %1 and user_id != %2 and is_valid =1")
                                    .arg(engineerId)
                                    .arg(userId);
        QueryResult queryResult = t.execQuery(selectSql);
        if (queryResult.next()) {
            returnId = queryResult.value(0).toInt();
        }

    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
        returnId = -1;
    }
    return returnId;
}

bool UserConfigService::UpdateUserConfig(const UserConfig &cfg)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        t.update("t_user_config")
                .set("user_id", cfg.userId)
                .set("engineer_id", cfg.engineerId)
                .set("weight", cfg.weight)
                .set("user_name", cfg.userName)
                .set("update_time", QDateTime::currentDateTime().toString("yyyy.MM.dd hh:mm:ss.zzz"))
                .where("id=?", cfg.id);
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

bool UserConfigService::UpdateUserValid(int userId, int flag)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        t.update("t_user_config")
                .set("is_valid", flag)
                .set("update_time", QDateTime::currentDateTime().toString("yyyy.MM.dd hh:mm:ss.zzz"))
                .where("user_id=?", userId);
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

bool UserConfigService::QueryUserConfigInfoById(UserConfig *cfg, int cfgId)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        QString selectSql = QString("SELECT id, user_id, engineer_id, weight, "
                                    "create_time, update_time,user_name from t_user_config "
                                    " WHERE id = %1")
                                    .arg(cfgId);
        QueryResult queryResult = t.execQuery(selectSql);
        if (queryResult.next()) {
            cfg->id         = queryResult.value(0).toInt();
            cfg->userId     = queryResult.value(1).toInt();
            cfg->engineerId = queryResult.value(2).toInt();
            cfg->weight     = queryResult.value(3).toDouble();
            cfg->createTime = queryResult.value(4).toString();
            cfg->updateTime = queryResult.value(5).toString();
            cfg->userName   = queryResult.value(6).toString();
            ret             = true;
        }
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

bool UserConfigService::QueryUserConfigListInfoByUserIdAndEngineerId(UserConfig *cfg, int userId, int engineerId)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        QString selectSql = QString("SELECT id, user_id, engineer_id, weight, "
                                    "create_time, update_time,user_name from t_user_config "
                                    " WHERE user_id = %1 and engineer_id = %2")
                                    .arg(userId)
                                    .arg(engineerId);
        QueryResult queryResult = t.execQuery(selectSql);
        if (queryResult.next()) {
            cfg->id         = queryResult.value(0).toInt();
            cfg->userId     = queryResult.value(1).toInt();
            cfg->engineerId = queryResult.value(2).toInt();
            cfg->weight     = queryResult.value(3).toDouble();
            cfg->createTime = queryResult.value(4).toString();
            cfg->updateTime = queryResult.value(5).toString();
            cfg->userName   = queryResult.value(6).toString();
            ret             = true;
        }
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}
bool UserConfigService::QueryUserConfigListInfoByEngineerId(QList<UserConfig *> *userCfgList, int engineerId)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        QString selectSql = QString("SELECT id, user_id, engineer_id, weight, "
                                    "create_time, update_time,user_name from t_user_config"
                                    " WHERE engineer_id = %1 and is_valid =1 ")
                                    .arg(engineerId);
        QueryResult queryResult = t.execQuery(selectSql);
        while (queryResult.next()) {
            UserConfig *cfg = new UserConfig();
            cfg->id         = queryResult.value(0).toInt();
            cfg->userId     = queryResult.value(1).toInt();
            cfg->engineerId = queryResult.value(2).toInt();
            cfg->weight     = queryResult.value(3).toDouble();
            cfg->createTime = queryResult.value(4).toString();
            cfg->updateTime = queryResult.value(5).toString();
            cfg->userName   = queryResult.value(6).toString();
            userCfgList->append(cfg);
        }
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

bool UserConfigService::QueryUserConfigListInfoByUserId(QList<UserConfig *> *userCfgList, int userId)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        QString selectSql = QString("SELECT id, user_id, engineer_id, weight, "
                                    "create_time, update_time,user_name from t_user_config"
                                    " WHERE user_id = %1 and is_valid=1 ")
                                    .arg(userId);
        QueryResult queryResult = t.execQuery(selectSql);
        while (queryResult.next()) {
            UserConfig *cfg = new UserConfig();
            cfg->id         = queryResult.value(0).toInt();
            cfg->userId     = queryResult.value(1).toInt();
            cfg->engineerId = queryResult.value(2).toInt();
            cfg->weight     = queryResult.value(3).toDouble();
            cfg->createTime = queryResult.value(4).toString();
            cfg->updateTime = queryResult.value(5).toString();
            cfg->userName   = queryResult.value(6).toString();
            userCfgList->append(cfg);
        }
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

bool UserConfigService::QueryAllUserConfig(QList<UserConfig *> *userCfgList)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        QString selectSql       = QString("SELECT id, user_id, engineer_id, weight, "
                                    "create_time, update_time,user_name from t_user_config");
        QueryResult queryResult = t.execQuery(selectSql);
        while (queryResult.next()) {
            UserConfig *cfg = new UserConfig();
            cfg->id         = queryResult.value(0).toInt();
            cfg->userId     = queryResult.value(1).toInt();
            cfg->engineerId = queryResult.value(2).toInt();
            cfg->weight     = queryResult.value(3).toDouble();
            cfg->createTime = queryResult.value(4).toString();
            cfg->updateTime = queryResult.value(5).toString();
            cfg->userName   = queryResult.value(6).toString();
            userCfgList->append(cfg);
        }
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

bool UserConfigService::DeleteUserConfigById(int cfgId)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString deleteSql = QString("DELETE FROM t_user_config WHERE id = %1").arg(cfgId);
    if (query.exec(deleteSql)) {
        ret = true;
        qDebug() << "deleteSql success!";
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool UserConfigService::DeleteUserConfigByEngineerId(int enjId)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString deleteSql = QString("DELETE FROM t_user_config WHERE engineer_id = %1").arg(enjId);
    if (query.exec(deleteSql)) {
        ret = true;
        qDebug() << "deleteSql success!";
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool UserConfigService::DeleteUserConfigByUserId(int userId)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString deleteSql = QString("DELETE FROM t_user_config WHERE user_id = %1").arg(userId);
    if (query.exec(deleteSql)) {
        ret = true;
        qDebug() << "deleteSql success!";
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}
//////////////////////用户工程配置-end////////////////////