#include "sqliteassistant.h"

#include <QApplication>
#include <QDir>
#include <QPluginLoader>
#include <QDebug>
#include <myhelper.h>
SqliteAssistant::SqliteAssistant(QObject *parent) : QObject(parent)
{

}

long SqliteAssistant::connectSql()
{
    long ret =0;
    QString path = QDir::currentPath();
    QApplication::addLibraryPath(path+QString("\\plugins"));
    QPluginLoader loader(path+QString("\\plugins\\sqldrivers\\qsqlite.dll"));
    database = QSqlDatabase::addDatabase("QSQLITE","old_db");
    database.setDatabaseName("./JM_DATA");
//    database.setUserName("root");
//    database.setPassword("jm123456");
    if (!database.open())
    {
        myHelper::ShowMessageBoxError(database.lastError().text());
        ret =-1;
    }
    else
    {
        qDebug() << "Succeed to connect database.";
        m_pSqlHandle =new QSqlQuery(database);
        ret =0;
    }
    return ret;
}

long SqliteAssistant::Insert_GoodTypeInfo(QString data)
{
    if(data.isEmpty())
    {
        myHelper::ShowMessageBoxError("货物类型不能为空");
        return -2;
    }
    QSqlQuery query(database);
    char sql[1024*4] = {0};
    sprintf(sql,"insert into GoodTypeInfo (Name) values ('%s')",data.toStdString().c_str());
    bool succ = query.exec(sql);
    if(succ)
    {
        myHelper::ShowMessageBoxInfo("货物信息保存成功");
        return 0;
    }
    else
    {
        if(query.lastError().databaseText().left(6) == "UNIQUE"){
            myHelper::ShowMessageBoxError("货物信息已存在");
        }
        return -1;
    }
}

QStringList SqliteAssistant::Select_GoodTypeInfo()
{
    QStringList list;
    QSqlQuery query(database);
    char sql[1024*4] = {0};
    sprintf(sql,"select * from GoodTypeInfo");
    bool succ = query.exec(sql);
    if(succ){
    }
    while(query.next())
    {
        list.append(query.value(0).toString());
    }
    return list;
}

long SqliteAssistant::Insert_CustomerInfo(QString data)
{
    if(data.isEmpty())
    {
        myHelper::ShowMessageBoxError("用户信息不能为空");
        return -2;
    }
    QSqlQuery query(database);
    char sql[1024*4] = {0};
    sprintf(sql,"insert into CustomerInfo (Name) values ('%s')",data.toStdString().c_str());
    bool succ = query.exec(sql);
    if(succ)
    {
        myHelper::ShowMessageBoxInfo("客户信息保存成功");
        return 0;
    }
    else
    {
        if(query.lastError().databaseText().left(6) == "UNIQUE"){
            myHelper::ShowMessageBoxError("客户信息已存在");
        }
        return -1;
    }
}

QStringList SqliteAssistant::Select_CustomerInfo()
{
    QStringList list;
    QSqlQuery query(database);
    char sql[1024*4] = {0};
    sprintf(sql,"select * from CustomerInfo");
    bool succ = query.exec(sql);
    if(succ){
    }
    while(query.next())
    {
        list.append(query.value(0).toString());
    }
    return list;
}

QStringList SqliteAssistant::Select_EqptInfo()
{
    QStringList list;
    QSqlQuery query(database);
    char sql[1024*4] = {0};
    sprintf(sql,"select Code from EquipmentInfo");
    bool succ = query.exec(sql);
    if(succ){
    }
    while(query.next())
    {
        list.append(query.value(0).toString());
    }
    return list;
}

long SqliteAssistant::Delete_CustomerInfo(QString data)
{
    if(data.isEmpty())
    {
        myHelper::ShowMessageBoxError("删除信息不能为空");
        return -2;
    }
    QSqlQuery query(database);
    char sql[1024*4] = {0};
    sprintf(sql,"delete from CustomerInfo where Name = '%s'",data.toStdString().c_str());
    bool succ = query.exec(sql);
    if(succ)
    {
        myHelper::ShowMessageBoxInfo("客户信息删除成功");
        return 0;
    }
    else
    {
        myHelper::ShowMessageBoxError(query.lastError().databaseText());
        return -1;
    }
}

long SqliteAssistant::Delete_GoodTypeInfo(QString data)
{
    if(data.isEmpty())
    {
        myHelper::ShowMessageBoxError("货物类型不能为空");
        return -2;
    }
    QSqlQuery query(database);
    char sql[1024*4] = {0};
    sprintf(sql,"delete from GoodTypeInfo where Name = '%s'",data.toStdString().c_str());
    bool succ = query.exec(sql);
    if(succ)
    {
        myHelper::ShowMessageBoxInfo("货物信息删除成功");
        return 0;
    }
    else
    {
        myHelper::ShowMessageBoxError(query.lastError().databaseText());
        return -1;
    }
}

long SqliteAssistant::Insert_EquipmentInfo(QString data, QString data2)
{
    if(data.isEmpty())
    {
        myHelper::ShowMessageBoxError("设备信息不能为空");
        return -2;
    }
    QSqlQuery query(database);
    char sql[1024*4] = {0};
    sprintf(sql,"insert into EquipmentInfo (Code,RegistrationKey) values ('%s','%s')",
            data.toStdString().c_str(),data2.toStdString().c_str());
    bool succ = query.exec(sql);
    if(succ)
    {
        myHelper::ShowMessageBoxInfo("设备信息保存成功");
        return 0;
    }
    else
    {
        if(query.lastError().databaseText().left(6) == "UNIQUE"){
            myHelper::ShowMessageBoxError("设备信息已存在");
        }
        return -1;
    }
}

long SqliteAssistant::Delete_EquipmentInfo(QString data)
{
    if(data.isEmpty())
    {
        myHelper::ShowMessageBoxError("设备号不能为空");
        return -2;
    }
    QSqlQuery query(database);
    char sql[1024*4] = {0};
    sprintf(sql,"delete from EquipmentInfo where Code = '%s'",data.toStdString().c_str());
    bool succ = query.exec(sql);
    if(succ)
    {
        myHelper::ShowMessageBoxInfo("设备信息删除成功");
        return 0;
    }
    else
    {
        myHelper::ShowMessageBoxError(query.lastError().databaseText());
        return -1;
    }
}

long SqliteAssistant::Insert_SystemConfigInfo(QString data, QString data2)
{
    if(data.isEmpty())
    {
        myHelper::ShowMessageBoxError("配置信息不能为空");
        return -2;
    }
    QSqlQuery query(database);
    char sql[1024*4] = {0};
    sprintf(sql,"insert into SystemConfigInfo (Key,Value) values ('%s','%s')",
            data.toStdString().c_str(),data2.toStdString().c_str());
    bool succ = query.exec(sql);
    if(succ)
    {
        //myHelper::ShowMessageBoxInfo("配置信息保存成功");
        return 0;
    }
    else
    {
        if(query.lastError().databaseText().left(6) == "UNIQUE"){
            //如果已经存在,更新配置
            char sql[1024*4] = {0};
            sprintf(sql,"UPDATE SystemConfigInfo SET VALUE = '%s' WHERE Key = '%s'",
                    data2.toStdString().c_str(),data.toStdString().c_str());
            bool succ = query.exec(sql);
            if(succ)
            {
                //myHelper::ShowMessageBoxInfo("配置信息更新成功");
                return 0;
            }
        }
        return -1;
    }
}

QString SqliteAssistant::Select_SystemConfigInfo(QString data)
{
    QString list;
    if(data.isEmpty())
    {
        myHelper::ShowMessageBoxError("配置信息不能为空");
        return nullptr;
    }
    QSqlQuery query(database);
    char sql[1024*4] = {0};
    sprintf(sql,"select Value from SystemConfigInfo where Key = '%s'",data.toStdString().c_str());
    bool succ = query.exec(sql);
    if(succ){
    }
    while(query.next())
    {
        list.append(query.value(0).toString());
    }
    return list;
}

QString SqliteAssistant::Select_AdminInfo(QString data)
{
    QString list;
    if(data.isEmpty())
    {
        myHelper::ShowMessageBoxError("不能为空");
        return nullptr;
    }
    QSqlQuery query(database);
    char sql[1024*4] = {0};
    sprintf(sql,"select Password from AdminInfo where Name = '%s'",data.toStdString().c_str());
    bool succ = query.exec(sql);
    if(succ){
    }
    while(query.next())
    {
        list.append(query.value(0).toString());
    }
    return list;
}

QString SqliteAssistant::Select_AdminInfoName()
{
    QString list;
    QSqlQuery query(database);
    char sql[1024*4] = {0};
    sprintf(sql,"select Name from AdminInfo");
    bool succ = query.exec(sql);
    if(succ){
    }
    while(query.next())
    {
        list.append(query.value(0).toString());
    }
    return list;
}

long SqliteAssistant::Insert_UserInfo(QString data, QString data2, QString data3)
{
    if(data.isEmpty())
    {
        myHelper::ShowMessageBoxError("用户名不能为空");
        return -2;
    }
    QSqlQuery query(database);
    char sql[1024*4] = {0};
    sprintf(sql,"insert into UserInfo (Code,Name,Password) values ('%s','%s','%s')",
            data.toStdString().c_str(),data2.toStdString().c_str(),data3.toStdString().c_str());
    bool succ = query.exec(sql);
    if(succ)
    {
        myHelper::ShowMessageBoxInfo("用户信息保存成功");
        return 0;
    }
    else
    {
        if(query.lastError().databaseText().left(6) == "UNIQUE"){
            myHelper::ShowMessageBoxError("用户编号已存在,请更改用户编号");
        }
        return -1;
    }
}

QStringList SqliteAssistant::Select_UserInfo()
{
    QStringList list;
    QSqlQuery query(database);
    char sql[1024*4] = {0};
    sprintf(sql,"select Name from UserInfo");
    bool succ = query.exec(sql);
    if(succ){
    }
    while(query.next())
    {
        list.append(query.value(0).toString());
    }
    return list;
}

QStringList SqliteAssistant::Select_UserInfo2()
{
    QStringList list;
    QSqlQuery query(database);
    char sql[1024*4] = {0};
    sprintf(sql,"select Code from UserInfo");
    bool succ = query.exec(sql);
    if(succ){
    }
    while(query.next())
    {
        list.append(query.value(0).toString());
    }
    return list;
}

QString SqliteAssistant::Select_UserInfo3(QString data)
{
    QString list;
    QSqlQuery query(database);
    char sql[1024*4] = {0};
    sprintf(sql,"select Password from UserInfo where Name = '%s'",data.toStdString().c_str());
    bool succ = query.exec(sql);
    if(succ){
    }
    while(query.next())
    {
        list.append(query.value(0).toString());
    }
    return list;
}

long SqliteAssistant::Delete_UserInfo(QString data)
{
    if(data.isEmpty())
    {
        myHelper::ShowMessageBoxError("用户选择失败");
        return -2;
    }
    QSqlQuery query(database);
    char sql[1024*4] = {0};
    sprintf(sql,"delete from UserInfo where Code = '%s'",data.toStdString().c_str());
    bool succ = query.exec(sql);
    if(succ)
    {
        myHelper::ShowMessageBoxInfo("用户信息删除成功");
        return 0;
    }
    else
    {
        myHelper::ShowMessageBoxError(query.lastError().databaseText());
        return -1;
    }
}

long SqliteAssistant::Updata_AdminInfo(QString data)
{
    QSqlQuery query(database);
    //如果已经存在,更新配置
    char sql[1024*4] = {0};
    sprintf(sql,"UPDATE AdminInfo SET Password = '%s'",
            data.toStdString().c_str());
    bool succ = query.exec(sql);
    if(succ)
    {
        myHelper::ShowMessageBoxInfo("密码更新成功,请勿忘记");
        return 0;
    }else{
        myHelper::ShowMessageBoxError(query.lastError().text());
        return -1;
    }

}

long SqliteAssistant::Insert_WeighingRun(QString data, QString data2, QString data3, QString data4, QString data5, QString data6, QString data7, QString data8, QString data9, QString data10)
{
    if(data4.isEmpty())
    {
        myHelper::ShowMessageBoxError("称重信息不能为空");
        return -2;
    }
    QSqlQuery query(database);
    char sql[1024*4] = {0};
    sprintf(sql,"insert into WeighingRun (ECode,Time,"
                "SerialNo,Weight,"
                "Type,Pound,"
                "RealCache,Operator,"
                "GoodsType,Customer,"
                "Notes) "
                "values "
                "(NULL,'%s',"
                "'%s',%.2f,"
                "'%s','%s',"
                "'%s','%s',"
                "'%s','%s',"
                "'%s')",
            data.toStdString().c_str(),data2.toStdString().c_str(),data3.toDouble(),
            data4.toStdString().c_str(),data5.toStdString().c_str(),data6.toStdString().c_str(),
            data7.toStdString().c_str(),data8.toStdString().c_str(),data9.toStdString().c_str(),
            data10.toStdString().c_str());
    bool succ = query.exec(sql);
    if(succ)
    {
        //myHelper::ShowMessageBoxInfo("设备信息保存成功");
        return 0;
    }
    else
    {
        if(query.lastError().databaseText().left(6) == "UNIQUE"){
            myHelper::ShowMessageBoxError("重量信息已存在");
        }
        return -1;
    }
}

long SqliteAssistant::select_ui_WeighingRun(QTableWidget *weight, int sumCount)
{
    QSqlQuery query(database);
    int i = 0, j = 0, nColumn, nRow;
    char sql[1024*4] = {0};
    sprintf(sql,"select "
                "ECode,Operator,"
                "Pound,Weight,Type,"
                "Time,RealCache,"
                "GoodsType,Customer,"
                "Notes "
                "from "
                "WeighingRun "
                "ORDER BY "
                "ECode DESC");
    bool succ = query.exec(sql);
    if(succ){
    }    else
    {
        myHelper::ShowMessageBoxError(query.lastError().databaseText());
        return -1;
    }
    query.last();
    nRow = query.at() + 1;
    if(nRow>=sumCount)
    {
        nRow =sumCount;
    }
    if(nRow<0)
    {
        weight->setRowCount(0);
        return 0;
    }
    weight->setRowCount(nRow);
    nColumn = weight->columnCount();
    query.first();
    while(j<nRow)
    {
        float over =query.value(5).toFloat();
        for (i = 0; i<nColumn; i++)
        {
            if(over>0)
            {
                weight->setItem(j, i, new QTableWidgetItem(query.value(i).toString()));
                weight->item(j, i)->setForeground(QBrush(QColor(255, 0, 0)));
            }
            else
            {
                weight->setItem(j, i, new QTableWidgetItem(query.value(i).toString()));
                //weight->item(j, i)->setForeground(QBrush(QColor(0, 255, 0)));
            }
        }
        j++;
        query.next();
    }
    return 0;
}

long SqliteAssistant::Delete_WeighingRun(int data)
{
    QSqlQuery query(database);
    char sql[1024*4] = {0};
    sprintf(sql,"delete from WeighingRun where ECode = %d",data);
    bool succ = query.exec(sql);
    if(succ)
    {
        myHelper::ShowMessageBoxInfo("数据删除成功");
        return 0;
    }
    else
    {
        myHelper::ShowMessageBoxError(query.lastError().databaseText());
        return -1;
    }
}

int SqliteAssistant::GetRowounts(QString tableName)
{
    int count = 0;
    QSqlQuery query(database);
    char sql[1024*4] = {0};
    sprintf(sql,"SELECT count(*) FROM %s",tableName.toStdString().c_str());
    bool succ = query.exec(sql);
    if(succ){
        query.next();
        count = query.value(0).toInt();
        return count;
    }else{
        qDebug()<<query.lastError().text();
    }

}

long SqliteAssistant::SelectInfo_sqliteView(QTableWidget *weight, SelectInfor &infor, int sumCount)
{
    QSqlQuery query(database);
    int i = 0, j = 0, nColumn, nRow;
    char sql[1024*4] = {0};
    string start=infor.startTime;
    string stop =infor.stopTime;
    string remark =infor.remark;
    sprintf(sql,"select "
                "ECode,Operator,"
                "Pound,Weight,"
                "Type,Time,"
                "RealCache,GoodsType,"
                "Customer,Notes "
                "FROM "
                "WeighingRun "
                "where "
                "Time between '%s' "
                "AND '%s' "
            "AND "
            "Operator LIKE '%s'"
                "ORDER BY "
                "Time "
                "DESC",
            infor.startTime.c_str(),infor.stopTime.c_str(),infor.Operator.c_str());
//sprintf(sql,"SELECT * FROM WeighingRun");
    bool succ =query.exec(sql);
    if(succ)
    {
        //qDebug() << "select_vehicle_information ok";
    }
    else
    {
        //qDebug() << "select_vehicle_information failed"<<query.lastError();
        return -1;
    }
    query.last();
    nRow = query.at() + 1;
    if(nRow>=sumCount)
    {
        nRow =sumCount;
    }
    if(nRow<0)
    {
        weight->setRowCount(0);
        return 0;
    }
    weight->setRowCount(nRow);
    nColumn = weight->columnCount();
    query.first();
    while(j<nRow)
    {
        float over =query.value(5).toFloat();
        for (i = 0; i<nColumn; i++)
        {
            if(over>0)
            {
                weight->setItem(j, i, new QTableWidgetItem(query.value(i).toString()));
                weight->item(j, i)->setForeground(QBrush(QColor(255, 0, 0)));
            }
            else
            {
                weight->setItem(j, i, new QTableWidgetItem(query.value(i).toString()));
                weight->item(j, i)->setForeground(QBrush(QColor(0, 255, 0)));
            }
        }
        j++;
        query.next();
    }
    return 0;
}
