QFD2/QFD/dbService/UserService.cpp

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

UserService::UserService(QObject *parent) { }
//////////////////////用户信息表-start////////////////////
int UserService::AddUserInfo(const QFUser &userInfo)
{
    int returnId = -1;
    try {

        // returnId = DBServiceSet::getNextId("t_user_info");
        Transaction t(SqlDBHelper::getDatabase());
        InsertQuery query = t.insertInto("t_user_info (user_name, user_no, password, role, post, "
                                         "major,work_position,education_degree,phone,project_id,write_time,remark)");
        NonQueryResult result =
                query.values(userInfo.userName, userInfo.userNo, userInfo.password, userInfo.role, userInfo.post,
                             userInfo.major, userInfo.workPosition, userInfo.educationDegree, userInfo.phone,
                             userInfo.projectId, userInfo.writeTime, userInfo.remark)
                        .exec();
        t.commit();
        returnId = result.lastInsertId().toInt();

    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
        returnId = -1;
    }
    return returnId;
}
/*暂时用id修改*/
bool UserService::UpdateUserInfo(const QFUser &userInfo)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        t.update("t_user_info")
                .set("user_name", userInfo.userName)
                .set("user_no", userInfo.userNo)
                .set("password", userInfo.password)
                .set("role", userInfo.role)
                .set("post", userInfo.post)
                .set("major", userInfo.major)
                .set("work_position", userInfo.workPosition)
                .set("education_degree", userInfo.educationDegree)
                .set("phone", userInfo.phone)
                .set("remark", userInfo.remark)
                .set("project_id", userInfo.projectId)
                .where("id=?", userInfo.id);
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

bool UserService::UpdateUserPassword(QString userNo, QString password)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        t.update("t_user_info").set("password", password).where("user_no=?", userNo);
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

bool UserService::UpdateUserById(QString userNo, QString password, int id)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase());
        t.update("t_user_info").set("password", password).set("user_no", userNo).where("id=?", id);
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

bool UserService::QueryUserInfoById(QFUser *userInfo, int userId)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("SELECT id, user_name,user_no,password,role,post, "
                                "major,work_position,education_degree,phone,remark,project_id,"
                                "write_time from "
                                "t_user_info WHERE id = %1")
                                .arg(userId);
    if (query.exec(selectSql)) {
        if (query.next()) {
            if (query.isNull(0) == false) {
                userInfo->id              = query.value(0).toInt();
                userInfo->userName        = query.value(1).toString();
                userInfo->userNo          = query.value(2).toString();
                userInfo->password        = query.value(3).toString();
                userInfo->role            = static_cast<QFUser::Role>(query.value(4).toInt());
                userInfo->post            = query.value(5).toString();
                userInfo->major           = query.value(6).toString();
                userInfo->workPosition    = query.value(7).toString();
                userInfo->educationDegree = query.value(8).toString();
                userInfo->phone           = query.value(9).toString();
                userInfo->remark          = query.value(10).toString();
                userInfo->projectId       = query.value(11).toString();
                userInfo->writeTime       = query.value(12).toString();
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool UserService::QueryUserInfoByUnserInfo(const QFUser &paramInfo, QFUser &queryInfo)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("SELECT id, user_name,user_no,password,role,post, "
                                "major,work_position,education_degree,phone,project_id,write_time from "
                                "t_user_info WHERE 1 = 1 ");
    if (!paramInfo.userName.isNull() && !paramInfo.userName.isEmpty()) {
        selectSql += " and user_name = '" + paramInfo.userName + "'";
    }
    if (!paramInfo.userNo.isNull() && !paramInfo.userNo.isEmpty()) {
        selectSql += " and user_no = '" + paramInfo.userNo + "'";
    }
    if (!paramInfo.post.isNull() && !paramInfo.post.isEmpty()) {
        selectSql += " and post = '" + paramInfo.post + "'";
    }
    if (!paramInfo.major.isNull() && !paramInfo.major.isEmpty()) {
        selectSql += " and major = '" + paramInfo.major + "'";
    }
    if (!paramInfo.workPosition.isNull() && !paramInfo.workPosition.isEmpty()) {
        selectSql += " and work_position = '" + paramInfo.workPosition + "'";
    }
    if (!paramInfo.educationDegree.isNull() && !paramInfo.educationDegree.isEmpty()) {
        selectSql += " and education_degree = '" + paramInfo.educationDegree + "'";
    }
    if (!paramInfo.phone.isNull() && !paramInfo.phone.isEmpty()) {
        selectSql += " and phone = '" + paramInfo.phone + "'";
    }
    if (!paramInfo.projectId.isNull() && !paramInfo.projectId.isEmpty()) {
        selectSql += " and project_id = '" + paramInfo.projectId + "'";
    }
    int role = paramInfo.role;
    if (role == 0) {
        selectSql += " and role != '" + QString::number(role) + "'";
    } else {
        selectSql += " and role = '" + QString::number(role) + "'";
    }

    if (query.exec(selectSql)) {
        if (query.next()) {
            queryInfo    = paramInfo;
            queryInfo.id = query.value(0).toInt();
            ret          = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}
/*获取到用户列表*/
bool UserService::QueryUserListByEngineerId(QList<QFUser *> *userInfoList, int engineerId)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("SELECT id, user_name,user_no,password,role,post, "
                                "major,work_position,education_degree,phone,remark,project_id,"
                                "write_time "
                                "from t_user_info"
                                " WHERE id in (select uc.user_id from t_user_config uc where "
                                "uc.engineer_id = %1 and uc.is_valid =1 )")
                                .arg(engineerId);
    // qDebug() << selectSql;
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                QFUser *userInfo          = new QFUser();
                userInfo->id              = query.value(0).toInt();
                userInfo->userName        = query.value(1).toString();
                userInfo->userNo          = query.value(2).toString();
                userInfo->password        = query.value(3).toString();
                userInfo->role            = static_cast<QFUser::Role>(query.value(4).toInt());
                userInfo->post            = query.value(5).toString();
                userInfo->major           = query.value(6).toString();
                userInfo->workPosition    = query.value(7).toString();
                userInfo->educationDegree = query.value(8).toString();
                userInfo->phone           = query.value(9).toString();
                userInfo->remark          = query.value(10).toString();
                userInfo->projectId       = query.value(11).toString();
                userInfo->writeTime       = query.value(12).toString();
                userInfoList->append(userInfo);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool UserService::QueryUserListByColumnAndColumnValue(QList<QFUser *> *userInfoList, QString columnName,
                                                      QString columnValue)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("SELECT id, user_name,user_no,password,role,post, "
                                "major,work_position,education_degree,phone,remark,project_id,"
                                "write_time "
                                "from t_user_info"
                                " WHERE %1 = '%2'")
                                .arg(columnName)
                                .arg(columnValue);
    // qDebug() << selectSql;
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                QFUser *userInfo          = new QFUser();
                userInfo->id              = query.value(0).toInt();
                userInfo->userName        = query.value(1).toString();
                userInfo->userNo          = query.value(2).toString();
                userInfo->password        = query.value(3).toString();
                userInfo->role            = static_cast<QFUser::Role>(query.value(4).toInt());
                userInfo->post            = query.value(5).toString();
                userInfo->major           = query.value(6).toString();
                userInfo->workPosition    = query.value(7).toString();
                userInfo->educationDegree = query.value(8).toString();
                userInfo->phone           = query.value(9).toString();
                userInfo->remark          = query.value(10).toString();
                userInfo->projectId       = query.value(11).toString();
                userInfo->writeTime       = query.value(12).toString();
                userInfoList->append(userInfo);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}
bool UserService::QueryUserList(QList<QFUser *> *userInfoList)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("SELECT id, user_name,user_no,password,role,post, "
                                "major,work_position,education_degree,phone,remark,project_id,"
                                "write_time from t_user_info");
    if (query.exec(selectSql)) {
        while (query.next()) {
            if (query.isNull(0) == false) {
                QFUser *userInfo          = new QFUser();
                userInfo->id              = query.value(0).toInt();
                userInfo->userName        = query.value(1).toString();
                userInfo->userNo          = query.value(2).toString();
                userInfo->password        = query.value(3).toString();
                userInfo->role            = static_cast<QFUser::Role>(query.value(4).toInt());
                userInfo->post            = query.value(5).toString();
                userInfo->major           = query.value(6).toString();
                userInfo->workPosition    = query.value(7).toString();
                userInfo->educationDegree = query.value(8).toString();
                userInfo->phone           = query.value(9).toString();
                userInfo->remark          = query.value(10).toString();
                userInfo->projectId       = query.value(11).toString();
                userInfo->writeTime       = query.value(12).toString();
                userInfoList->append(userInfo);
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool UserService::QueryUserListNotAdmin(QList<QFUser *> *userInfoList)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret = false;
    try {
        QString selectSql = QString("SELECT id, user_name,user_no,password,role,post, "
                                    "major,work_position,education_degree,phone,remark,project_id,"
                                    "write_time from t_user_info where role !=0");
        if (query.exec(selectSql)) {
            while (query.next()) {
                if (query.isNull(0) == false) {
                    QFUser *userInfo          = new QFUser();
                    userInfo->id              = query.value(0).toInt();
                    userInfo->userName        = query.value(1).toString();
                    userInfo->userNo          = query.value(2).toString();
                    userInfo->password        = query.value(3).toString();
                    userInfo->role            = static_cast<QFUser::Role>(query.value(4).toInt());
                    userInfo->post            = query.value(5).toString();
                    userInfo->major           = query.value(6).toString();
                    userInfo->workPosition    = query.value(7).toString();
                    userInfo->educationDegree = query.value(8).toString();
                    userInfo->phone           = query.value(9).toString();
                    userInfo->remark          = query.value(10).toString();
                    userInfo->projectId       = query.value(11).toString();
                    userInfo->writeTime       = query.value(12).toString();
                    userInfoList->append(userInfo);
                }
            }
        } else {
            qDebug() << query.lastError();
        }
        ret = true;

    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}
bool UserService::DeleteUserById(int Id)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString deleteSql = QString("DELETE FROM t_user_info WHERE id = %1").arg(Id);
    if (query.exec(deleteSql)) {
        ret = true;
        qDebug() << "deleteSql success!";
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

//删除除管理管外的所有用户
bool UserService::DeleteUserByRole(int roleType)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString deleteSql = QString("DELETE FROM t_user_info WHERE role != %1").arg(roleType);
    if (query.exec(deleteSql)) {
        ret = true;
        qDebug() << "deleteSql success!";
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool UserService::QueryUserByNoAndPassword(QFUser *userInfo, QString userNo, QString password)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("SELECT id, user_name,user_no,password,role,post, "
                                "major,work_position,education_degree,phone,remark,project_id,"
                                "write_time from "
                                "t_user_info WHERE user_no = %1 and password = %2")
                                .arg("'" + userNo + "'")
                                .arg("'" + password + "'");

    if (query.exec(selectSql)) {
        if (query.next()) {
            if (query.isNull(0) == false) {
                userInfo->id              = query.value(0).toInt();
                userInfo->userName        = query.value(1).toString();
                userInfo->userNo          = query.value(2).toString();
                userInfo->password        = query.value(3).toString();
                userInfo->role            = static_cast<QFUser::Role>(query.value(4).toInt());
                userInfo->post            = query.value(5).toString();
                userInfo->major           = query.value(6).toString();
                userInfo->workPosition    = query.value(7).toString();
                userInfo->educationDegree = query.value(8).toString();
                userInfo->phone           = query.value(9).toString();
                userInfo->remark          = query.value(10).toString();
                userInfo->projectId       = query.value(11).toString();
                userInfo->writeTime       = query.value(12).toString();
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool UserService::QueryUserByNo(QFUser *userInfo, QString userNo)
{
    QSqlDatabase db = SqlDBHelper::getDatabase();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("SELECT id, user_name,user_no,password,role,post, "
                                "major,work_position,education_degree,phone,remark,project_id,"
                                "write_time from "
                                "t_user_info WHERE user_no = %1 ")
                                .arg("'" + userNo + "'");

    if (query.exec(selectSql)) {
        if (query.next()) {
            if (query.isNull(0) == false) {
                userInfo->id              = query.value(0).toInt();
                userInfo->userName        = query.value(1).toString();
                userInfo->userNo          = query.value(2).toString();
                userInfo->password        = query.value(3).toString();
                userInfo->role            = static_cast<QFUser::Role>(query.value(4).toInt());
                userInfo->post            = query.value(5).toString();
                userInfo->major           = query.value(6).toString();
                userInfo->workPosition    = query.value(7).toString();
                userInfo->educationDegree = query.value(8).toString();
                userInfo->phone           = query.value(9).toString();
                userInfo->remark          = query.value(10).toString();
                userInfo->projectId       = query.value(11).toString();
                userInfo->writeTime       = query.value(12).toString();
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool UserService::QueryUserInfoById2(QFUser *userInfo, int userId)
{
    QSqlDatabase db = SqlDBHelper::getDatabase2();
    QSqlQuery query(db);
    bool ret          = false;
    QString selectSql = QString("SELECT id, user_name,user_no,password,role,post, "
                                "major,work_position,education_degree,phone,remark,project_id,"
                                "write_time from "
                                "t_user_info WHERE id = %1")
                                .arg(userId);
    qDebug() << selectSql;
    if (query.exec(selectSql)) {
        if (query.next()) {
            if (query.isNull(0) == false) {
                userInfo->id              = query.value(0).toInt();
                userInfo->userName        = query.value(1).toString();
                userInfo->userNo          = query.value(2).toString();
                userInfo->password        = query.value(3).toString();
                userInfo->role            = static_cast<QFUser::Role>(query.value(4).toInt());
                userInfo->post            = query.value(5).toString();
                userInfo->major           = query.value(6).toString();
                userInfo->workPosition    = query.value(7).toString();
                userInfo->educationDegree = query.value(8).toString();
                userInfo->phone           = query.value(9).toString();
                userInfo->remark          = query.value(10).toString();
                userInfo->projectId       = query.value(11).toString();
                userInfo->writeTime       = query.value(12).toString();
            }
            ret = true;
        }
    } else {
        qDebug() << query.lastError();
    }
    return ret;
}

bool UserService::UpdateUserInfo2(const QFUser &userInfo)
{
    bool ret = false;
    try {
        Transaction t(SqlDBHelper::getDatabase2());
        t.update("t_user_info")
                .set("user_name", userInfo.userName)
                .set("user_no", userInfo.userNo)
                .set("password", userInfo.password)
                .set("role", userInfo.role)
                .set("post", userInfo.post)
                .set("major", userInfo.major)
                .set("work_position", userInfo.workPosition)
                .set("education_degree", userInfo.educationDegree)
                .set("phone", userInfo.phone)
                .set("remark", userInfo.remark)
                .set("project_id", userInfo.projectId)
                .where("id=?", userInfo.id);
        t.commit();
        ret = true;
    } catch (const DBException &ex) {
        qDebug() << ex.lastError.text();
    }
    return ret;
}

int UserService::AddUserInfo2(const QFUser &userInfo)
{
    int returnId = -1;
    try {

        Transaction t(SqlDBHelper::getDatabase2());
        InsertQuery query = t.insertInto("t_user_info (id,user_name, user_no, password, role, post, "
                                         "major,work_position,education_degree,phone,project_id,write_time)");
        NonQueryResult result =
                query.values(userInfo.id, userInfo.userName, userInfo.userNo, userInfo.password, userInfo.role,
                             userInfo.post, userInfo.major, userInfo.workPosition, userInfo.educationDegree,
                             userInfo.phone, userInfo.projectId, userInfo.writeTime)
                        .exec();
        t.commit();
        returnId = result.lastInsertId().toInt();

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

//////////////////////用户信息表-end////////////////////