#include "databases.h"
#include <QMessageBox>
#include <QDebug>

Databases::Databases(QObject *parent) :
    QObject(parent)
{
    Utils::Profiles::instance()->setDefault("QuerySQL","DatabaseType","QODBC");
    Utils::Profiles::instance()->setDefault("QuerySQL","DatabaseName","AccessData");
    Utils::Profiles::instance()->setDefault("QuerySQL","HostName","127.0.0.1");
    Utils::Profiles::instance()->setDefault("QuerySQL","Port","-1");
    Utils::Profiles::instance()->setDefault("QuerySQL","UserName","sa");
    Utils::Profiles::instance()->setDefault("QuerySQL","Password","123456");

    dbStruct.databaseType = Utils::Profiles::instance()->value("QuerySQL","DatabaseType").toString();
    dbStruct.databaseName = Utils::Profiles::instance()->value("QuerySQL","DatabaseName").toString();
    dbStruct.hostName = Utils::Profiles::instance()->value("QuerySQL","HostName").toString();
    dbStruct.portNumber = Utils::Profiles::instance()->value("QuerySQL","Port").toInt();
    dbStruct.userName = Utils::Profiles::instance()->value("QuerySQL","UserName").toString();
    dbStruct.password = Utils::Profiles::instance()->value("QuerySQL","Password").toString();
}
/*-------------------------------------------
 *
 * 打开数据库
 *
---------------------------------------------*/
QString Databases::openDatabase()
{
    if(!db.isOpen())
    {
        db = QSqlDatabase::addDatabase( dbStruct.databaseType,"db1" );
        QString dsn = QStringLiteral("DRIVER={SQL Server};SERVER=%1;DATABASE=%2").arg(dbStruct.hostName).arg(dbStruct.databaseName);
        db.setDatabaseName(dsn);
        db.setUserName(dbStruct.userName);
        db.setPassword(dbStruct.password);
        if(db.open())
        {
            return QString("");
        }else
        {
            return db.lastError().text();
        }
    }
    return db.lastError().text();
}
/*-------------------------------------------
 *
 * 数据库是否打开
 *
---------------------------------------------*/
bool Databases::isOpen()
{
    return db.isOpen();
}
/*-------------------------------------------
 *
 * 判断用户是否存在
 *
---------------------------------------------*/
int Databases::consumerIsExists(const ConsumerStruct &consumers) const
{
    if(db.isOpen())
    {
        int consumerId = 0;
        QSqlQuery query(db);
        query.prepare("SELECT t_b_Consumer.f_ConsumerName,t_b_Consumer_Other.f_ConsumerID,t_b_Consumer_Other.f_CertificateID FROM t_b_Consumer LEFT JOIN t_b_Consumer_Other ON t_b_Consumer.f_ConsumerID = t_b_Consumer_Other.f_ConsumerID WHERE t_b_Consumer_Other.f_CertificateID LIKE ?");
        query.addBindValue(QStringLiteral("%%1%").arg(consumers.certificateID));
        query.exec();
        while(query.next())
        {
            consumerId = query.value(1).toInt();
            break;
        }
        return consumerId;
    }else
    {
        return 0;
    }
}
/*-------------------------------------------
 *
 * 保存用户信息
 *
---------------------------------------------*/
QString Databases::saveConsumerDb(const ConsumerStruct &consumers) const
{
    if(db.isOpen())
    {
        if(!consumers.consumerName.isEmpty())
        {
            if(consumerIsExists(consumers) > 0)
            {
                QMessageBox msgBox( QMessageBox::Warning,QStringLiteral("提示"),
                                    QStringLiteral("用户名：%1，身份证号码%2已存在，是否跳过。").arg(consumers.consumerName).arg(consumers.certificateID),
                                    QMessageBox::NoButton,
                                    0
                                    );
                msgBox.addButton(QStringLiteral("替换"), QMessageBox::AcceptRole);
                msgBox.addButton(QStringLiteral("跳过"), QMessageBox::RejectRole);
                if(msgBox.exec() == QMessageBox::AcceptRole)
                {
                    if(dealConsumers(consumers))
                    {
                        int result = saveConsumers(consumers);
                        if(result>0)
                        {
                            return saveConsumerOther(result,consumers);
                        }else
                        {
                            QMessageBox msgBox( QMessageBox::Warning,QStringLiteral("错误信息"),
                                                QStringLiteral("保存用户信息失败，名称名：%1，身份证号：%2。").arg(consumers.consumerName).arg(consumers.certificateID),
                                                QMessageBox::NoButton,
                                                0
                                                );
                            msgBox.addButton(QStringLiteral("确定"), QMessageBox::AcceptRole);
                            msgBox.exec();
                            exit(0);
                        }
                    }
                }
            }else
            {
                int result = saveConsumers(consumers);
                if(result>0)
                {
                    return saveConsumerOther(result,consumers);
                }else
                {
                    QMessageBox msgBox( QMessageBox::Warning,QStringLiteral("错误信息"),
                                        QStringLiteral("保存用户信息失败，名称名：%1，身份证号：%2。").arg(consumers.consumerName).arg(consumers.certificateID),
                                        QMessageBox::NoButton,
                                        0
                                        );
                    msgBox.addButton(QStringLiteral("确定"), QMessageBox::AcceptRole);
                    msgBox.exec();
                    exit(0);
                }
            }
        }
    }else
    {
        return QStringLiteral("Databases Not Open.");
    }
}
/*-------------------------------------------
 *
 * 得到最大工号
 *
---------------------------------------------*/
int Databases::getMaxConsumerNO() const
{
    QSqlQuery query(db);
    query.prepare("SELECT MAX(CONVERT(int,f_ConsumerNO)) AS ConsumerNO FROM t_b_Consumer");
    query.exec();
    int result = 1;
    while(query.next())
    {
        result = query.value(0).toInt()+1;
        break;
    }
    return result;
}
/*-------------------------------------------
 *
 * 保存基本信息
 *
---------------------------------------------*/
int Databases::saveConsumers(const ConsumerStruct &consumers) const
{
    int result = 0;
    QString consumerNO = QStringLiteral("        %1").arg(getMaxConsumerNO()).right(10);
    QSqlQuery query(db);
    query.prepare("INSERT INTO t_b_Consumer(f_ConsumerNO,f_ConsumerName) VALUES (?,?)");
    query.addBindValue(consumerNO);
    query.addBindValue(consumers.consumerName);
    if(query.exec())
    {
        QSqlQuery query2(db);
        query2.prepare("SELECT f_ConsumerID,f_ConsumerNO,f_ConsumerName FROM t_b_Consumer WHERE f_ConsumerNO LIKE ?");
        query2.addBindValue(consumerNO);
        query2.exec();
        while(query2.next())
        {
            result = query2.value(0).toInt();
            break;
        }
    }
    return result;
}
/*-------------------------------------------
 *
 * 返回工号
 *
---------------------------------------------*/
QString Databases::getConsumerNO(const ConsumerStruct &consumers) const
{
    if(db.isOpen())
    {
        QString consumerId = 0;
        QSqlQuery query(db);
        query.prepare("SELECT t_b_Consumer.f_ConsumerNO,t_b_Consumer_Other.f_ConsumerID,t_b_Consumer_Other.f_CertificateID FROM t_b_Consumer LEFT JOIN t_b_Consumer_Other ON t_b_Consumer.f_ConsumerID = t_b_Consumer_Other.f_ConsumerID WHERE t_b_Consumer_Other.f_CertificateID LIKE ?");
        query.addBindValue(QStringLiteral("%%1%").arg(consumers.certificateID));
        query.exec();
        while(query.next())
        {
            consumerId = query.value(0).toString();
            break;
        }
        return consumerId;
    }else
    {
        return "";
    }
}
/*-------------------------------------------
 *
 * 删除用户
 *
---------------------------------------------*/
bool Databases::dealConsumers(const ConsumerStruct &consumers) const
{
    if(db.isOpen())
    {
        QSqlQuery query(db);
        query.prepare("DELETE FROM t_b_Consumer WHERE f_ConsumerNO LIKE ?");
        query.addBindValue(getConsumerNO(consumers));
        if(query.exec())
        {
            QSqlQuery query1(db);
            query1.prepare("DELETE FROM t_b_Consumer_Other WHERE f_CertificateID LIKE ?");
            query1.addBindValue(consumers.certificateID);
            return query1.exec();
        }
        return false;
    }else
    {
        return false;
    }
}
/*-------------------------------------------
 *
 * 保存其他信息
 *
---------------------------------------------*/
QString Databases::saveConsumerOther(int consumerId, const ConsumerStruct &consumers) const
{
    QSqlQuery query(db);
    query.prepare("INSERT INTO t_b_Consumer_Other(f_ConsumerID,f_Note,f_Birthday,f_CertificateType,f_CertificateID,f_Addr,f_Sex,f_Nationality) VALUES (?,?,?,?,?,?,?,?)");
    query.addBindValue(consumerId);
    query.addBindValue(consumers.certificateUnit+consumers.certificateDate);      //签证机关+证件有效期
    query.addBindValue(consumers.birthday);                                         //出生年月
    query.addBindValue(consumers.certificateType);                                  //证件类型
    query.addBindValue(consumers.certificateID);                                    //身份证号
    query.addBindValue(consumers.addr);                                             //地址
    query.addBindValue(consumers.sex);                                              //性别
    query.addBindValue(consumers.nationality);                                      //民族
    query.exec();
    return query.lastError().text();
}