#include "SysPersonDao.h"


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

}

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

    // 查询语句
    QString sql = "SELECT * FROM SYS_DEPT WHERE PID != '-1'";

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

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

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

    // 遍历查询结果
    while (query.next()) {
        QVariantMap item;
        count++;
        item.insert("id", query.value("id").toString());
        item.insert("pid", query.value("pid").toString());
        item.insert("pids", query.value("pids").toString());
        item.insert("fullname", query.value("fullname").toString());
        QStringList vector =  query.value("pids").toString().split(",");
        if (vector.count() == 2) {   //集团
            result.append(item);
        } else {
            for (int i = 0; i < result.size(); i++) {
                if (result[i].value("id") == query.value("pid").toString()) {
                    result.insert(i + 1, item);
                    break;
                }
            }
        }
    }

    LOG(DEBUG) << QString("查询表[SYS_DEPT]的所有记录[%1][%2]").arg(count).arg(sql).toStdString();

    return result;
}

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;
}

QVector<QVariantMap> SysPersonDao::findRecordsByNameAndDept(QString name, QString dept)
{
    // 新建查询
    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.NAME = '%1' AND SYS_PERSON.DEPTID = '%2'";
    sql = sql.arg(name).arg(dept);

    // 执行查询
    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][%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, qulonglong 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(qulonglong 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;
}