DataProcessService/DataProcessService/ytWarterMkProcess/databasethread.cpp

#include "databasethread.h"
#include "waterMk01.h"
#include "AlarmReport.h"

#define HostName  "47.98.201.187"
#define HostPort 3306
#define UserName "uskyIOT"
#define PassWord "uskyIOT"
#define DatabaseName "jdxf"


#define sql_debug

extern MksensorList *mkList;


DatabaseThread::DatabaseThread(QObject *parent) :
    QThread(parent)
{
    keep = false;
    hour = 255;
    sqlList.clear();
    alarmRepList.clear();
    db = QSqlDatabase::addDatabase("QMYSQL","mksensor_db");
    db.setHostName(QString(HostName));
    db.setPort(HostPort);
    db.setUserName(QString(UserName));
    db.setPassword(QString(PassWord));
    db.setDatabaseName(QString(DatabaseName));
    db.open();
    mcdb = QSqlDatabase::addDatabase("QMYSQL","mksensor_sdb");
    mcdb.setHostName(QString("47.111.81.118"));
    mcdb.setPort(3306);
    mcdb.setUserName(QString("root"));
    mcdb.setPassword(QString("Yt2021"));
    mcdb.setDatabaseName(QString("jx_cover"));
//    mcdb.open();
}

void DatabaseThread::appendSql(QString sql,int type)
{
    if(type==1){
        sqlList.append(sql);
    }else if (type==2) {
        mcsqlList.append(sql);
    }
}

void DatabaseThread::appendAlarm(AlarmRep rep,int type)
{
    if(type==1){
        alarmRepList.append(rep);
    }else if (type==2) {
        mcalarmRepList.append(rep);
    }
}

bool DatabaseThread::chktable(QString tbname)
{
    QStringList tblist = db.tables();
    for(int i=0;i<tblist.size();i++){
        if(QString::compare(tblist.at(i),tbname)==0)
            return true;
    }
    return false;
}

void DatabaseThread::check_devicelist()
{
    printf("database thread check_devicelist()\n");
    uint nowTime = QDateTime::currentDateTime().toTime_t();
    hour=QTime::currentTime().hour();
    QString sql = QString("select owner_code, owner_name, unitinfo, owner_xh, company, dwtype from sp_owner where dwtype in (2,4,128) and owner_xh !=''");
    QSqlQuery qry = db.exec(sql);
    int nrow= 0;
    while(qry.next()){
        if(nrow>10239)
            break;
//        QString tbname = QString("sp_d%1").arg(qry.value(0).toString());
//        if(!chktable(tbname)){
//            db.exec(QString::fromUtf8("create table `%1` (`id` bigint(20) unsigned not null auto_increment primary key, `device_code` varchar(30) not null, `port` varchar(55) default null, `time` datetime default null, `status` varchar(50) default null, `address` varchar(50) default null, `ncmd` varchar(50) default null, `data1` varchar(50) default null, `data2` varchar(255) default null, `data3` varchar(255) default null, `data4` varchar(255) default null, `data5` varchar(255) default null )").arg(tbname));
//            db.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (null, '%1', %2, '状态', 1, '', '1970-01-01 08:00:00','')").arg(qry.value(0).toString()).arg(qry.value(5).toInt()));
//            db.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (null, '%1', %2, '剩余电量', 2, '', '1970-01-01 08:00:00','')").arg(qry.value(0).toString()).arg(qry.value(5).toInt()));
//            db.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (null, '%1', %2, '无线信号', 3, '', '1970-01-01 08:00:00','')").arg(qry.value(0).toString()).arg(qry.value(5).toInt()));
//            db.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (null, '%1', %2, '水压/水位', 4, '', '1970-01-01 08:00:00','')").arg(qry.value(0).toString()).arg(qry.value(5).toInt()));
//        }
        sql = QString("select true_status from sp_devices_status where deviceid='%1'")
                .arg(qry.value(0).toString());
        QSqlQuery qry2 = db.exec(sql);

        mkList->sensors[nrow].Enabled=0x01;
        if(qry2.next()){
            if(qry2.value(0).toUInt()==0)
                mkList->sensors[nrow].LastCommtime = nowTime;
        }
        sprintf(mkList->sensors[nrow].Device_Code,"%s",qry.value(0).toString().toUtf8().data());
        sprintf(mkList->sensors[nrow].Device_Name,"%s",qry.value(1).toString().toUtf8().data());
        sprintf(mkList->sensors[nrow].Device_Info,"%s",qry.value(2).toString().toUtf8().data());
        sprintf(mkList->sensors[nrow].Device_Type,"%s",qry.value(5).toString().toUtf8().data());
        sprintf(mkList->sensors[nrow].Company_Code,"%s",qry.value(4).toString().toUtf8().data());
        printf("Device_Code:%s\n",QString(mkList->sensors[nrow].Device_Code).toUtf8().data());
        printf("Device_Code:%s\n",QString(mkList->sensors[nrow].Company_Code).toUtf8().data());
        nrow++;
    }
    for(int i=nrow;i<10240;i++){
        mkList->sensors[i].Enabled=0x00;
        mkList->sensors[i].LastCommtime = 0;
    }

}

void DatabaseThread::stop()
{
    keep = false;
}

void DatabaseThread::run()
{
    db.open();
    QString ip,port,name,passwd;
    QString mqttsql = QString("select ip, port, username, passwd from yt_dataprocessservice_mqtt where type = 'ytDP0008';");
    QSqlQuery mqttqry = db.exec(mqttsql);
    while(mqttqry.next())
    {
        ip = mqttqry.value(0).toString();
        port = mqttqry.value(1).toString();
        name = mqttqry.value(2).toString();
        passwd = mqttqry.value(3).toString();
    }
    emit mqttInfo(ip,port,name,passwd);
    mqttqry.clear();
    db.close();

    keep = true;
    printf("database thread start\n");
    while(keep){
        if(!db.isOpen()){
            db.open();
        }else{
            if(QTime::currentTime().hour()!=hour){
                check_devicelist();
            }
            if(alarmRepList.length()>0){
                while(alarmRepList.length()>0){
                    AlarmRep rep = alarmRepList.first();
                    QSqlQuery qry = db.exec(rep.Sql);
#ifdef sql_debug
                    emit SqlLog(QString("[ %1 sql] ").arg(QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss.zzz"))+rep.Sql);
#endif
                    quint64 lastid = qry.lastInsertId().toULongLong()&0xffffffffffffffff;
                    if(lastid>0){
                        emit AlarmReport(rep.DeviceId,lastid,rep.AlarmType,rep.AlarmTime);
                    }
                    alarmRepList.removeFirst();
                    usleep(1000);
                }
            }
            if(sqlList.length()>0){
                db.exec(sqlList.first());
#ifdef sql_debug
                emit SqlLog(QString("[ %1 sql ] ").arg(QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss.zzz"))+sqlList.first());
#endif
                sqlList.removeFirst();
            }
            db.close();
        }
        usleep(10000);
    }
    if(db.isOpen())
        db.close();
}