DataPlayer/ChaosDataPlayer/sqlitedb.cpp

#include "sqlitedb.h"


SqliteDB::SqliteDB()
{


}

int SqliteDB::OpenDataBase()
{
    QDir pluginsDir(QLibraryInfo::location(QLibraryInfo::PluginsPath));
    QString path1 = pluginsDir.absolutePath();
    qDebug("Plugins path : %s", qPrintable(path1));

    QString path = QDir::currentPath();
    QApplication::addLibraryPath(path);
    QPluginLoader loader(QString("./plugins/sqldrivers/qsqlite.dll"));


    database = QSqlDatabase::addDatabase("QSQLITE");
    database.setDatabaseName(QCoreApplication::applicationDirPath() + "\\DB\\analyse.db");
    if (!database.open())
    {
        qDebug() << "Error: Failed to connect database." << database.lastError();
        return -1;
    }
    else
    {
        qDebug() << "Succeed to connect database." ;
        return 0;
    }
}
int SqliteDB::CreateDataBase()
{
    //创建表格
    QSqlQuery sql_query;
    if(!sql_query.exec("create table if not exists t_Device(DeviceMac VARCHAR(32) primary key, DeviceName VARCHAR(100),\
                        DeviceIP VARCHAR(32),DeviceType VARCHAR(32),Online VARCHAR(1))"))
    {
        qDebug() << "Error: Fail to create table."<< sql_query.lastError();
        return -1;
    }
    else
    {
        qDebug() << "Table created!";
        return 0;
    }

}
int SqliteDB::QueryData(QString& tablename, QString& column, QString& whereCon)
{
    QSqlQuery sql_query;
    QString strSql = "select ";
    if (whereCon != NULL) {
        strSql = strSql + column + " from " + tablename + " where " + whereCon + ";";
    }
    else {
        strSql = strSql + column + " from " + tablename + ";";
    }
    int count = -1;
    sql_query.exec(strSql);
    if(!sql_query.exec())
    {
          qDebug()<<sql_query.lastError();
    }
    else
    {
         while(sql_query.next())
         {
               count = sql_query.value(0).toInt();
         }
    }
    return count;
}
array_t SqliteDB::GetDataMultiLine(QString& tablename, QString& column, QString whereCon)
 {
     QSqlQuery sql_query;

     QVector<vec_t> vecResult;
     QString strSql = "select ";
     if (whereCon != "") {
         strSql = strSql + column  + ", DeviceMac , DeviceName" + " from " + tablename + " where " + whereCon + ";";
     }
     else {
         strSql = strSql + column  + ", DeviceMac , DeviceName" + " from " + tablename + ";";
     }
     qDebug() << "strSql" << strSql << endl;
     sql_query.exec(strSql);
     while(sql_query.next()){
            vec_t arrResult;
            QString strIP = sql_query.value(0).toString();
            arrResult.push_back(strIP);
            arrResult.push_back(sql_query.value(1).toString());
            arrResult.push_back(sql_query.value(2).toString());
            vecResult.push_back(arrResult);
     }
     return vecResult;
 }
int SqliteDB::InsertData(QString& tablename,QString& sql)
{
    QSqlQuery sql_query;
    QString strSql = "INSERT INTO ";
    strSql = strSql + tablename + sql;
    if(!sql_query.exec(strSql))
    {
        qDebug() << sql_query.lastError();
    }
    else
    {
        qDebug() << "";
    }

}
int SqliteDB::UpdataData(QString& tablename, QString& columnName, QString& columnValue, QString whereColName, QString whereColValue)
{
    QSqlQuery sql_query;
    int iRet = -1;
    QString strSql = "update ";
    if (whereColName != NULL) {
        strSql = strSql + tablename + " set " + columnName + "='" + columnValue + "'" + " where " + whereColName + "='" + whereColValue + "';";
    }
    else {
        strSql = strSql + tablename + " set " + columnName + "='" + columnValue + "';";
    }

    if(!sql_query.exec(strSql))
    {
       qDebug() << sql_query.lastError();
    }
    else
    {
       iRet = 0;
       qDebug() << "";
    }
    return iRet;
}
int SqliteDB::UpdataDataCol(QString& tablename, QString& updateColumn, QString& whereCond)
{
    QSqlQuery sql_query;
    int iRet = -1;
    QString strSql = "update ";
    if (whereCond != NULL) {
        strSql = strSql + tablename + " set " + updateColumn + " where " + whereCond;
    }
    else {
        strSql = strSql + tablename + " set " + updateColumn ;
    }
    qDebug() << "strSql" << strSql << endl;
    if(!sql_query.exec(strSql))
    {
       qDebug() << sql_query.lastError();
    }
    else
    {
       iRet = 0;
       qDebug() << "";
    }
    return iRet;
}

int SqliteDB::DeleteData(QString& tablename, QString& condColumnName, QString& condColumnValue)
{
    QSqlQuery sql_query;
    int iRet = -1;
    QString strSql = "delete from ";
    if (condColumnName != NULL) {
        strSql = strSql + tablename + " where " + condColumnName + "='" + condColumnValue + "';";
    }
    else {
        strSql = strSql + tablename + ";";
    }
    if(!sql_query.exec(strSql))
    {
        qDebug() << sql_query.lastError();
    }
    else
    {
        iRet = 0;
        qDebug() << "";
    }
    return iRet;
}
int SqliteDB::InitDevice()
{
    QString tableName = "t_Device ";
    QString column = "Online";
    QString columnValue = "0";
    UpdataData(tableName,column,columnValue);
}
int SqliteDB::OperateDeviceData(DEVICE_INFO& device_info)
{
    if(device_info.DeviceMac == "" || device_info.DeviceIP == "")
        return 0;
    QString whereCon = QString("DeviceMac = '%1'").arg(device_info.DeviceMac);
    QString tableName = "t_Device ";
    QString column = "count(*)";
    int iRet = QueryData(tableName,column,whereCon);
    qDebug() << "QueryData " <<iRet  << device_info.DeviceIP << endl;
    if(iRet == 0){
        QString sql = QString("values('%1','%2','%3','%4',1);").arg(device_info.DeviceMac,device_info.DeviceName,device_info.DeviceIP,\
                                                         device_info.DeviceType);
        qDebug() << "InsertData" <<sql << endl;
        InsertData(tableName,sql);
    }else{
        column = "DeviceIP";
        QString columnValue = device_info.DeviceIP;
        whereCon = "DeviceMac";
        QString whereValue = device_info.DeviceMac;
        UpdataData(tableName,column,columnValue,whereCon,whereValue);
    }
    return  0;
}