#include "SysPersonDao.h"


SysPersonDao::SysPersonDao(QObject *parent) : BaseDao(parent)
{

}

QVector<QVariantMap> SysPersonDao::findAllRecord()
{
    // 新建查询
    QSqlQuery query(ConnectionManager::getInstance()->getConnection());

    // 查询语句
    QString sql = "SELECT * FROM SYS_PERSON LEFT JOIN SYS_DEPT ON SYS_PERSON.DEPTID = SYS_DEPT.ID WHERE SYS_PERSON.DELFLAG = '0'";

    // 执行查询
    query.exec(sql);

    // 获取结果集的大小
    int count = 0;

    // 返回结果
    QVector<QVariantMap> result;

    // 遍历查询结果
    while (query.next()) {
        QVariantMap item;
        count++;
        item.insert("id", query.value("id").toString());
        item.insert("delflag", query.value("delflag").toString());
        item.insert("createtime", query.value("createtime").toString());
        item.insert("updatetime", query.value("updatetime").toString());
        item.insert("name", query.value("name").toString());
        item.insert("gender", query.value("gender").toString());
        item.insert("deptid", query.value("deptid").toString());
        item.insert("id_card_no", query.value("id_card_no").toString());
        item.insert("remarks", query.value("remarks").toString());
        item.insert("person_code", query.value("person_code").toString());
        item.insert("sync_id", query.value("sync_id").toString());
        item.insert("deptname", query.value("fullname").toString());
        result.append(item);
    }

//    LOG(TRACE) << QString("查询SYS_PERSON表的所有记录[%1]").arg(count).toStdString();
//    LOG_TRACE(QString("查询SYS_PERSON表的所有记录[%1]").arg(count).toStdString());

    return result;
}

QVariantMap SysPersonDao::findRecordById(QString id)
{
    // 新建查询
    QSqlQuery query(ConnectionManager::getInstance()->getConnection());

    // 查询语句
    QString sql = QString("SELECT * FROM SYS_PERSON LEFT JOIN SYS_DEPT ON SYS_PERSON.DEPTID = SYS_DEPT.ID WHERE SYS_PERSON.DELFLAG = '0' AND SYS_PERSON.ID = :id");

    query.prepare(sql);
    query.bindValue(":id", id);

    // 执行查询
    query.exec();

    // 返回结果
    QVariantMap result;

    // 获取结果集的大小
    query.last();
    int count = query.at() + 1;

    if (count >=1)
    {
        query.first();

        result.insert("id", query.value("id").toString());
        result.insert("delflag", query.value("delflag").toString());
        result.insert("createtime", query.value("createtime").toString());
        result.insert("updatetime", query.value("updatetime").toString());
        result.insert("name", query.value("name").toString());
        result.insert("gender", query.value("gender").toString());
        result.insert("deptid", query.value("deptid").toString());
        result.insert("id_card_no", query.value("id_card_no").toString());
        result.insert("remarks", query.value("remarks").toString());
        result.insert("person_code", query.value("person_code").toString());
        result.insert("deptname", query.value("fullname").toString());
        result.insert("sync_id", query.value("sync_id").toString());
        result.insert("avatar", query.value("avatar").toString());
    }

//    LOG(TRACE) << QString("根据id查询SYS_PERSON表的记录[id=%1][%2]").arg(id).arg(sql).toStdString();
//    LOG_TRACE(QString("根据id查询SYS_PERSON表的记录[id=%1][%2]").arg(id).arg(sql).toStdString());

    return result;
}

int SysPersonDao::findRecordCountByNameAndDept(QString name, QString dept)
{
    // 新建查询
    QSqlQuery query(ConnectionManager::getInstance()->getConnection());

    // 查询语句
    QString sql = "SELECT COUNT(P.ID) AS RECCT FROM SYS_PERSON P LEFT JOIN SYS_DEPT D ON P.DEPTID = D.ID WHERE P.DELFLAG = '0'";
    if (name.isEmpty() == false)
    {
        sql += " AND P.NAME LIKE '%" + name + "%'";
    }
    if (dept.isEmpty() == false && dept.indexOf("-1") < 0)
    {
        sql += QString(" AND ((P.DEPTID = '%1') OR (D.PIDS LIKE '%,%1,%'))").arg(dept);
    }

    // 执行查询
    query.exec(sql);

    // 返回结果
    int result;

    // 遍历查询结果
    if (query.next()) {
        result = query.value("RECCT").toInt();
    }

//    LOG(TRACE) << QString("根据姓名和部门查询SYS_PERSON记录总数[%1][%2]").arg(result).arg(sql).toStdString();
//    LOG_TRACE(QString("根据姓名和部门查询SYS_PERSON记录总数[%1][%2]").arg(result).arg(sql).toStdString());

    return result;
}

QVector<QVariantMap> SysPersonDao::findRecordsByNameAndDept(QString name, QString dept, qint8 limit, qint16 offset)
{
    // 新建查询
    QSqlQuery query(ConnectionManager::getInstance()->getConnection());

    // 查询语句
    QString sql = "SELECT * FROM SYS_PERSON P LEFT JOIN SYS_DEPT D ON P.DEPTID = D.ID WHERE P.DELFLAG = '0'";
    if (name.isEmpty() == false)
    {
        sql += " AND P.NAME LIKE '%" + name + "%'";
    }
    if (dept.isEmpty() == false && dept.indexOf("-1") < 0)
    {
        sql += QString(" AND ((P.DEPTID = '%1') OR (D.PIDS LIKE '%,%1,%'))").arg(dept);
    }

    sql += QString(" LIMIT %1 OFFSET %2").arg(limit).arg(offset);

    // 执行查询
    query.exec(sql);

    // 获取结果集的大小
    int count = 0;

    // 返回结果
    QVector<QVariantMap> result;

    // 遍历查询结果
    while (query.next()) {
        QVariantMap item;

        count++;

        item.insert("id", query.value("id").toString());
        item.insert("delflag", query.value("delflag").toString());
        item.insert("createtime", query.value("createtime").toString());
        item.insert("updatetime", query.value("updatetime").toString());
        item.insert("name", query.value("name").toString());
        item.insert("gender", query.value("gender").toString());
        item.insert("deptid", query.value("deptid").toString());
        item.insert("id_card_no", query.value("id_card_no").toString());
        item.insert("remarks", query.value("remarks").toString());
        item.insert("person_code", query.value("person_code").toString());
        item.insert("sync_id", query.value("sync_id").toString());
        item.insert("deptname", query.value("fullname").toString());

        result.append(item);
    }

//    LOG(TRACE) << QString("根据姓名和部门分页查询SYS_PERSON表的记录[%1][%2]").arg(count).arg(sql).toStdString();
//    LOG_TRACE(QString("根据姓名和部门分页查询SYS_PERSON表的记录[%1][%2]").arg(count).arg(sql).toStdString());

    return result;
}

QString SysPersonDao::save(QVariantMap object)
{
    // 新建查询
    QSqlQuery query(ConnectionManager::getInstance()->getConnection());

    qulonglong id = ConnectionManager::getInstance()->generateId();
    QString tm = QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss");

    // INSERT语句
    QString sql = QString("INSERT INTO SYS_PERSON "
                  "(ID, DELFLAG, CREATETIME, UPDATETIME, "
                  "NAME, GENDER, DEPTID, ID_CARD_NO, "
                  "REMARKS, PERSON_CODE, SYNC_ID, FACE_VALID, IRIS_VALID) "
                  "VALUES ('%1', '0', '%2', '%2', '%3', '%4', '%5', '%6', '%7', '%8', '%9', 0, 0)")
            .arg(id).arg(tm)
            .arg(object.value("name").toString())
            .arg(object.value("gender").toString())
            .arg(object.value("deptid").toString())
            .arg(object.value("id_card_no").toString())
            .arg(object.value("remarks").toString())
            .arg(object.value("person_code").toString())
            .arg(object.value("sync_id").toString());

    // 开启事务
    ConnectionManager::getInstance()->getConnection().transaction();

    // 执行插入
    bool success = query.exec(sql);

    // 结束事务
    ConnectionManager::getInstance()->getConnection().commit();

    // 返回结果
    if (success == true)
    {
//        LOG(DEBUG) << QString("保存SYS_PERSON记录成功[ID = %1][%2]").arg(id).arg(sql).toStdString();
//        LOG_DEBUG(QString("保存SYS_PERSON记录成功[ID = %1][%2]").arg(id).arg(sql).toStdString());
        return QString("%1").arg(id);
    }
    else
    {
        return "-1";
    }
}

bool SysPersonDao::edit(QVariantMap newObject, QString id)
{
    // 新建查询
    QSqlQuery query(ConnectionManager::getInstance()->getConnection());

    QString tm = QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss");

    // UPDATE语句
    QString sql = QString("UPDATE SYS_PERSON SET UPDATETIME = '%1'").arg(tm);
    if (newObject.contains("name"))
    {
        sql.append(QString(", NAME = '%1'").arg(newObject.value("name").toString()));
    }
    if (newObject.contains("gender"))
    {
        sql.append(QString(", GENDER = '%1'").arg(newObject.value("gender").toString()));
    }
    if (newObject.contains("deptid"))
    {
        sql.append(QString(", DEPTID = '%1'").arg(newObject.value("deptid").toString()));
    }
    if (newObject.contains("person_code"))
    {
        sql.append(QString(", PERSON_CODE = '%1'").arg(newObject.value("person_code").toString()));
    }
    if (newObject.contains("face_valid"))
    {
        sql.append(QString(", FACE_VALID = '%1'").arg(newObject.value("face_valid").toString()));
    }
    if (newObject.contains("iris_valid"))
    {
        sql.append(QString(", IRIS_VALID = '%1'").arg(newObject.value("iris_valid").toString()));
    }

    sql.append(QString(" WHERE ID = '%1'").arg(id));

    // 开启事务
    ConnectionManager::getInstance()->getConnection().transaction();

    // 执行更新
    bool success = query.exec(sql);

    // 结束事务
    ConnectionManager::getInstance()->getConnection().commit();

//    LOG(DEBUG) << QString("编辑人员[ID=%1][%2]").arg(id).arg(sql).toStdString();
//    LOG_DEBUG(QString("编辑人员[ID=%1][%2]").arg(id).arg(sql).toStdString());

    // 返回结果
    return success;
}

bool SysPersonDao::dele(QString id)
{
    // 新建查询
    QSqlQuery query(ConnectionManager::getInstance()->getConnection());
    QSqlQuery irisDel(ConnectionManager::getInstance()->getConnection());

    QString tm = QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss");
    qint64 tmms = QDateTime::currentDateTime().toMSecsSinceEpoch();

    // UPDATE语句
    QString sql = QString("UPDATE SYS_PERSON SET UPDATETIME = :updateTime, DELFLAG = :flag WHERE ID = :id").arg(tm).arg(tmms).arg(id);
    query.prepare(sql);
    query.bindValue(":id", id);
    query.bindValue(":updateTime", tm);
    query.bindValue(":flag", tmms);

    // 物理删除人员的人脸和虹膜数据
    QString delIrisSql = QString("DELETE FROM IRIS_DATA WHERE PERSON_ID = :personId");
    irisDel.prepare(delIrisSql);
    irisDel.bindValue(":personId", id);

    // 开启事务
    ConnectionManager::getInstance()->getConnection().transaction();

    // 执行更新
    bool success = query.exec(sql);
    query.exec(delIrisSql);

    // 结束事务
    ConnectionManager::getInstance()->getConnection().commit();

//    LOG_DEBUG(QString("删除人员SYS_PERSON[ID=%1][%2]").arg(id).arg(success).toStdString());

    // 返回结果
    return success;
}