modularization/src/tool/server_fwupdate/fwdb.cpp

#include "fwdb.h"

#include <iostream>

fwdb::fwdb()
{
    mpthread = new std::thread(&fwdb::threaddb,this);
}

fwdb::~fwdb()
{
    mbthreadrun = false;
    mpthread->join();
}

void fwdb::OpenDataBase()
{

    mstrdbpath = "fw.db";

    if(QSqlDatabase::contains("sqliteadc"))
        mdatabase = QSqlDatabase::database("sqliteadc");
    else
        mdatabase = QSqlDatabase::addDatabase("QSQLITE","sqliteadc");
//    mdatabase = QSqlDatabase::addDatabase("QSQLITE");


//    if(QSqlDatabase::contains("sqlite"))
//        mdatabase = QSqlDatabase::database("sqlite");
//    else
//        mdatabase = QSqlDatabase::addDatabase("QSQLITE","sqlite");



    mdatabase.setDatabaseName(mstrdbpath.data());
    if (!mdatabase.open())
    {
        qDebug("Error: Failed to connect database. error is %s ",mdatabase.lastError());
        return;
    }
    else
    {

        qDebug("Succeed to connect database.");
    }



    QSqlQuery sql_query(mdatabase);

    bool bHaveTable = true;

    //查询数据
    sql_query.exec("select count(*)  from sqlite_master where type='table' and name = 'accountdata'");
    if(!sql_query.exec())
    {
        qDebug("%s",sql_query.lastError());
        return;
    }
    else
    {

        if(sql_query.next())
        {
            int count = sql_query.value(0).toInt();
//            qDebug("count %d ",count);
            if(count < 1)bHaveTable = false;
        }
    }



    if(bHaveTable == false)
    {
        if(!sql_query.exec("create table accountdata(id INTEGER primary key AUTOINCREMENT,username TEXT, password TEXT,"
                           "phone TEXT, passneedchange BOOL, passerrorcount INTEGER,"
                           "uservalidtime DATETIME, usercreatetime DATETIME,lastloginerrortime DATETIME)"))
        {
            qDebug() << "Error: Fail to create table."<< sql_query.lastError();
            return;
        }
//        if(!sql_query.exec("CREATE INDEX index_vehid ON groupdata (vehid)"))
//        {
//            qDebug() << "Error: Fail to create index_vehid."<< sql_query.lastError();
//            return;
//        }
//        if(!sql_query.exec("CREATE INDEX index_recvtime ON groupdata (recvtime)"))
//        {
//            qDebug() << "Error: Fail to create index_recvtime."<< sql_query.lastError();
//            return;
//        }
//        if(!sql_query.exec("CREATE INDEX index_recordid ON groupdata (recordid)"))
//        {
//            qDebug() << "Error: Fail to create index_recordid."<< sql_query.lastError();
//            return;
//        }
        char strsen[1000];
        QDateTime now = QDateTime::currentDateTime();

        QString strnow = now.toString("yyyy-MM-dd hh:mm:ss");
        snprintf(strsen,1000,"INSERT INTO accountdata(username,password,passerrorcount,uservalidtime,usercreatetime,lastloginerrortime)"
                 " VALUES(\"%s\",\"%s\",%d,\"%s\",\"%s\",\"%s\")",
                                     "admin","adc",0,"2099-12-31 23:59:59",strnow.toLatin1().data(),"1970-1-1 1:00:00");
        sql_query.exec(strsen);
        qDebug("Create Table groupdata successfully.");
    }
}

void fwdb::threaddb()
{
    OpenDataBase();
}

int fwdb::CheckAuth(std::string strusername, std::string strpassword ,std::string & strerrorcode)
{
    int nrtn = 0;
    mmutexdb.lock();
    QSqlQuery query(mdatabase);
    char strsen[1000];


    snprintf(strsen,1000,"select * from accountdata where((accountdata.username = \"%s\"))",
             strusername.data(),strpassword.data());
    query.exec(strsen);
    if(!query.exec(strsen))
    {
        std::cout<<query.lastError().text().toLatin1().data()<<std::endl;
        strerrorcode = "SQL Error.";
        mmutexdb.unlock();
        return 0;
    }
    else
    {
        if(query.next())
        {
            int nerrorcount = query.value("passerrorcount").toInt();
            QString  strerrortime = query.value("lastloginerrortime").toString();

            QDateTime dtlasterr =  QDateTime::fromString(strerrortime,"yyyy-MM-dd hh:mm:ss");

            int64_t nseclasterr = dtlasterr.toSecsSinceEpoch();
            int64_t nsecnow = QDateTime::currentSecsSinceEpoch();

            int64_t ndiff = nsecnow - nseclasterr ;


            if((nerrorcount >= 5) &&(ndiff < 1800))
            {
                strerrorcode = "PassWord Error 5 times, Please Wait 30 minutes.";
                mmutexdb.unlock();
                return 0;
            }

        }
    }

    snprintf(strsen,1000,"select * from accountdata where((accountdata.username = \"%s\") &(accountdata.password = \"%s\"))",
             strusername.data(),strpassword.data());
    query.exec(strsen);
    if(!query.exec(strsen))
    {
        std::cout<<query.lastError().text().toLatin1().data()<<std::endl;
        strerrorcode = "SQL Error.";
        mmutexdb.unlock();
        return 0;
    }
    else
    {
        if(query.next())
        {
            nrtn = 1;
            std::cout<<" auth ok. "<<std::endl;

            QString strvalidtime = query.value("uservalidtime").toString();
            QDateTime dtvalid = QDateTime::fromString(strvalidtime,"yyyy-MM-dd hh:mm:ss");

            int64_t nsecvalid = dtvalid.toSecsSinceEpoch();
            int64_t nsecnow = QDateTime::currentSecsSinceEpoch();

            int nerrorcount = query.value("passerrorcount").toInt();

            int64_t ndiff = nsecvalid - nsecnow;

            if(ndiff <0)
            {
                strerrorcode = "Account Expire.";
                mmutexdb.unlock();
                return 0;

            }


            if(nerrorcount != 0)
            {
                snprintf(strsen,1000,"update accountdata set passerrorcount = %d  where((accountdata.username = \"%s\") )",
                         0, strusername.data());
                query.exec(strsen);
                if(!query.exec(strsen))
                {
                    std::cout<<query.lastError().text().toLatin1().data()<<std::endl;
                }
            }

//            int id = query.value(0).toInt();
//            qint64 recordid = query.value(1).toLongLong();
//            mql_xvectorrecordtime.push_back(recordid);
//            std::string strvehid = query.value(3).toString().toStdString();

        }
        else
        {
            snprintf(strsen,1000,"select * from accountdata where((accountdata.username = \"%s\") )",
                     strusername.data());
            query.exec(strsen);
            if(!query.exec(strsen))
            {
                std::cout<<query.lastError().text().data()<<std::endl;
            }
            else
            {
                if(query.next())
                {
                    int nerrorcount = 0;
                    nerrorcount = query.value(5).toInt();
                    snprintf(strsen,1000,"update accountdata set lastloginerrortime = \"%s\",passerrorcount = %d  where(accountdata.username = \"%s\" )",
                             QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss").toLatin1().data(),
                             nerrorcount+1, strusername.data());
                    query.exec(strsen);
                    if(!query.exec(strsen))
                    {
                        std::cout<<query.lastError().text().toLatin1().data()<<std::endl;
                    }
                }
            }
        }
    }
    mmutexdb.unlock();
    return nrtn;
}