#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").toLongLong());
        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").toLongLong());
        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").toLongLong());
        item.insert("deptname", query.value("fullname").toString());
        result.append(item);
    }

    LOG(DEBUG) << 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 = '%1'").arg(id);

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

    // 返回结果
    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").toLongLong());
        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());
    }

    LOG(DEBUG) << sql.toStdString();
    LOG(DEBUG) << QString("根据id查询SYS_PERSON表的记录[%1]").arg(count).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();

    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").toLongLong());
        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").toLongLong());
        item.insert("deptname", query.value("fullname").toString());

        result.append(item);
    }

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

    return result;
}

QVector<QVariantMap> SysPersonDao::findRecordsByProperties(QVariantMap conditions)
{
    // 新建查询
    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'";
    QVariantMap::iterator it;
    for (it = conditions.begin(); it != conditions.end(); it++)
    {
        sql += QString(" AND SYS_PERSON.%1 = %2").arg(it.key()).arg(it.value().toString());
    }

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

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

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

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

        count++;

        item.insert("id", query.value("id").toLongLong());
        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").toLongLong());
        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").toLongLong());
        item.insert("deptname", query.value("fullname").toString());

        result.append(item);
    }

    LOG(DEBUG) << sql.toStdString();
    LOG(DEBUG) << QString("根据属性值查询SYS_PERSON表的记录[%1]").arg(count).toStdString();
    return result;
}

QVector<QVariantMap> SysPersonDao::findRecordsByProperty(QString properName, QVariant properValue)
{
    // 新建查询
    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' AND SYS_PERSON.%1 = '%2'";
    sql = sql.arg(properName).arg(properValue.toString());

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

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

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

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

        count++;

        item.insert("id", query.value("id").toLongLong());
        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").toLongLong());
        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").toLongLong());
        item.insert("deptname", query.value("fullname").toString());

        result.append(item);
    }

    LOG(DEBUG) << sql.toStdString();
    LOG(DEBUG) << QString("根据属性值查询SYS_PERSON表的记录[%1]").arg(count).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) "
                  "VALUES ('%1', '0', '%2', '%2', '%3', '%4', '%5', '%6', '%7', '%8', '%9')")
            .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());

    LOG(DEBUG) << sql.toStdString();

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

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

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

    // 返回结果
    if (success == true)
    {
        LOG(DEBUG) << QString("保存SYS_PERSON记录成功[ID = %1]").arg(id).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").toULongLong()));
    }
    if (newObject.contains("person_code"))
    {
        sql.append(QString(", PERSON_CODE = '%1'").arg(newObject.value("person_code").toString()));
    }

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

    LOG(DEBUG) << sql.toStdString();

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

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

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

    // 返回结果
    return success;
}

bool SysPersonDao::dele(QString id)
{
    // 新建查询
    QSqlQuery query(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 = '%1', DELFLAG = '%2' WHERE ID = '%3'")
            .arg(tm).arg(tmms).arg(id);

    LOG(DEBUG) << sql.toStdString();

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

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

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

    // 返回结果
    return success;
}