#include "db_syncthread.h"
#include "ytServiceShm.h"

extern YT_SERVICE_SHM *ytShm;

DB_SyncThread::DB_SyncThread(QObject *parent) :
    QThread(parent)
{
    isWorking = false;
    mdb = QSqlDatabase::addDatabase("QMYSQL","jdxf_db");
    mdb.setDatabaseName("jdxf");
    mdb.setHostName("localhost");
    mdb.setUserName("root");
    mdb.setPassword("Yt2018IoT");
    mdbOpened = mdb.open();
}

bool DB_SyncThread::userInfoSync(QString devid,QString deviceCode, uint sync_id,QString tbname,QDate chkday,uint ID)
{
    QString qrysql,sql;
    QString upsql_0="select 1;",upsql_1="select 1;";
    bool found = false;
    bool updated = false;
    int count=0;

    QSqlQuery qry2 = mdb.exec(QString("select count(*) as count, sum(clzt) as sum from sp_hj2017 where device_code='%1' and data1<>'U44'").arg(devid));
    if(qry2.next()){
        mdb.exec(QString("update sp_device_sync set alm_count=%1, alm_confirm=%2 where device_id='%3'")
                 .arg(qry2.value(0).toInt())
                 .arg(qry2.value(0).toInt()>0?qry2.value(1).toInt():0)
                 .arg(devid));
    }
    qry2.clear();
    qry2 = mdb.exec(QString("select count(*) as count, sum(clzt) as sum from sp_hj2017 where device_code='%1' and data1='U44'").arg(devid));
    if(qry2.next()){
        mdb.exec(QString("update sp_device_sync set com_count=%1, com_confirm=%2 where device_id='%3'")
                 .arg(qry2.value(0).toInt())
                 .arg(qry2.value(0).toInt()>0?qry2.value(1).toInt():0)
                 .arg(devid));
    }
    qry2.clear();
    for(int i=0;i<dList.length();i++){
        if(dList.at(i).compare(deviceCode)==0)
        {
            found = true;
            break;
        }
    }
    if(!found){
        mdb.exec(QString("insert into sp_owner_status (id,device_id,point_name,point_code,point_data,data_time,content) values (NULL,'%1','','0','','1970-01-01 08:00:00','');").arg(deviceCode));
        mdb.exec(QString("insert into sp_owner_status (id,device_id,point_name,point_code,point_data,data_time,content) values (NULL,'%1','','1','','1970-01-01 08:00:00','');").arg(deviceCode));
        dList.append(deviceCode);
        pcList.append(0);
        dList.append(deviceCode);
        pcList.append(1);
    }
    qrysql = QString("select id, data3, data5, time, data2, data4 from `%1` where id> %2 and time>'%3-%4-%5 00:00:00' order by id").arg(tbname).arg(sync_id).arg(chkday.year(),4,10,QChar('0')).arg(chkday.month(),2,10,QChar('0')).arg(chkday.day(),2,10,QChar('0'));
    qry2 = mdb.exec(qrysql);
    while(qry2.next()){
        updated = true;
        if(count==0)
            sql = QString("insert into sp_owner_sync_data (id, native_id, device_id, point_code, point_data, data_time, content) values (NULL,%1,'%2','%3','%4','%5','%6 %7')")
                    .arg(qry2.value(0).toUInt()).arg(devid).arg(qry2.value(1).isNull()?"0":"1")
                    .arg(qry2.value(2).isNull()?"":qry2.value(2).toString()).arg(qry2.value(3).isNull()?"1970-01-01 08:00:00":qry2.value(3).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                    .arg(qry2.value(4).isNull()?"":qry2.value(4).toString()).arg(qry2.value(5).isNull()?"":qry2.value(5).toString());
        else
            sql += QString(", (NULL,%1,'%2','%3','%4','%5','%6 %7')")
                    .arg(qry2.value(0).toUInt()).arg(devid).arg(qry2.value(1).isNull()?"0":"1")
                    .arg(qry2.value(2).isNull()?"":qry2.value(2).toString()).arg(qry2.value(3).isNull()?"1970-01-01 08:00:00":qry2.value(3).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                    .arg(qry2.value(4).isNull()?"":qry2.value(4).toString()).arg(qry2.value(5).isNull()?"":qry2.value(5).toString());
        if(qry2.value(1).isNull())
            upsql_0 = QString::fromUtf8("update sp_owner_status set point_data='%1', data_time='%2', content='%3 %4', point_name='通信报警',dwtype=%5 where device_id='%6' and point_code='%7';")
                    .arg(qry2.value(2).isNull()?"":qry2.value(2).toString()).arg(qry2.value(3).isNull()?"1970-01-01 08:00:00":qry2.value(3).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                    .arg(qry2.value(4).isNull()?"":qry2.value(4).toString()).arg(qry2.value(5).isNull()?"":qry2.value(5).toString())
                    .arg(1).arg(devid).arg("0");
        else
            upsql_1 = QString::fromUtf8("update sp_owner_status set point_data='%1', data_time='%2', content='%3 %4', point_name='主机报警',dwtype=%5 where device_id='%6' and point_code!='0';")
                    .arg(qry2.value(2).isNull()?"":qry2.value(2).toString()).arg(qry2.value(3).isNull()?"1970-01-01 08:00:00":qry2.value(3).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                    .arg(qry2.value(4).isNull()?"":qry2.value(4).toString()).arg(qry2.value(5).isNull()?"":qry2.value(5).toString())
                    .arg(1).arg(devid);
        sync_id = qry2.value(0).toUInt();
        count++;
        if(count>=100){
            time_t tmp_time = time((time_t *)NULL);
            ytShm->updatetime[DB_THREAD] = tmp_time;
            sql.append(";");
            mdb.exec(sql);
            mdb.exec(QString("update sp_owner_sync set sync_id=%1 where id=%2").arg(sync_id).arg(ID));
            mdb.exec(upsql_0);
            mdb.exec(upsql_1);
            sql = "";
            count=0;
        }
    }
    if(count>0){
        time_t tmp_time = time((time_t *)NULL);
        ytShm->updatetime[DB_THREAD] = tmp_time;
        sql.append(";");
        mdb.exec(sql);
        mdb.exec(QString("update sp_owner_sync set sync_id=%1 where id=%2").arg(sync_id).arg(ID));
        mdb.exec(upsql_0);
        mdb.exec(upsql_1);
        sql = "";
        count=0;
    }
    return updated;
}

bool DB_SyncThread::waterInfoSync(QString devid, QString deviceCode, uint sync_id, QString tbname, QDate chkday, uint ID,int dwtype)
{
    QString qrysql,sql,upsql;
    int count=0;
    bool updated = false;
    bool found = false;
    QSqlQuery qry2 = mdb.exec(QString("select count(*) as count, sum(clzt) as sum from sp_sj2017 where device_code='%1' and data1<>'WP4' and data1<>'LL4' ").arg(devid));
    if(qry2.next()){
        mdb.exec(QString("update sp_device_sync set alm_count=%1, alm_confirm=%2 where device_id='%3'")
                 .arg(qry2.value(0).toInt())
                 .arg(qry2.value(0).toInt()>0?qry2.value(1).toInt():0)
                 .arg(devid));
    }
    qry2.clear();
    qry2 = mdb.exec(QString("select count(*) as count, sum(clzt) as sum from sp_sj2017 where device_code='%1' and ( data1='WP4' or data1='LL4' )").arg(devid));
    if(qry2.next()){
        mdb.exec(QString("update sp_device_sync set com_count=%1, com_confirm=%2 where device_id='%3'")
                 .arg(qry2.value(0).toInt())
                 .arg(qry2.value(0).toInt()>0?qry2.value(1).toInt():0)
                 .arg(devid));
    }
    qry2.clear();
    for(int i=0;i<dList.length();i++){
        if(dList.at(i).compare(deviceCode)==0)
        {
            found = true;
            break;
        }
    }
    if(!found){
        mdb.exec(QString("insert into sp_owner_status (id,device_id,point_name,point_code,point_data,data_time,content) values (NULL,'%1','','1','','1970-01-01 08:00:00','');").arg(deviceCode));
        mdb.exec(QString("insert into sp_owner_status (id,device_id,point_name,point_code,point_data,data_time,content) values (NULL,'%1','','2','','1970-01-01 08:00:00','');").arg(deviceCode));
        mdb.exec(QString("insert into sp_owner_status (id,device_id,point_name,point_code,point_data,data_time,content) values (NULL,'%1','','3','','1970-01-01 08:00:00','');").arg(deviceCode));
        mdb.exec(QString("insert into sp_owner_status (id,device_id,point_name,point_code,point_data,data_time,content) values (NULL,'%1','','4','','1970-01-01 08:00:00','');").arg(deviceCode));
        dList.append(deviceCode);
        pcList.append(1);
        dList.append(deviceCode);
        pcList.append(2);
        dList.append(deviceCode);
        pcList.append(3);
        dList.append(deviceCode);
        pcList.append(4);
    }
    qrysql = QString("select id, time, data1, data2, data3, data4 from `%1` where id>%2 and time>'%3-%4-01 00:00:00' order by id ").arg(tbname).arg(sync_id).arg(chkday.year(),4,10,QChar('0')).arg(chkday.month(),2,10,QChar('0'));
    qry2 = mdb.exec(qrysql);
    while(qry2.next()){
        updated = true;
        if(count==0){
            sql = QString("insert into sp_owner_sync_data (id, native_id, device_id, point_code, point_data, data_time, content) values (NULL,%1,'%2','%3','%4','%5','%6')")
                    .arg(qry2.value(0).toUInt()).arg(devid).arg(1).arg(qry2.value(2).isNull()?"0":qry2.value(2).toString())
                    .arg(qry2.value(1).isNull()?"1970-01-01 08:00:00":qry2.value(1).toDateTime().toString("yyyy-MM_dd HH:mm:ss")).arg("");
            sql += QString(",  (NULL,%1,'%2','%3','%4','%5','%6')")
                    .arg(qry2.value(0).toUInt()).arg(devid).arg(2).arg(qry2.value(3).isNull()?"0":qry2.value(3).toString())
                    .arg(qry2.value(1).isNull()?"1970-01-01 08:00:00":qry2.value(1).toDateTime().toString("yyyy-MM_dd HH:mm:ss")).arg("");
            sql += QString(", (NULL,%1,'%2','%3','%4','%5','%6')")
                    .arg(qry2.value(0).toUInt()).arg(devid).arg(3).arg(qry2.value(4).isNull()?"0":qry2.value(4).toString())
                    .arg(qry2.value(1).isNull()?"1970-01-01 08:00:00":qry2.value(1).toDateTime().toString("yyyy-MM_dd HH:mm:ss")).arg("");
            sql += QString(",  (NULL,%1,'%2','%3','%4','%5','%6')")
                    .arg(qry2.value(0).toUInt()).arg(devid).arg(4).arg(qry2.value(5).isNull()?"0":qry2.value(5).toString())
                    .arg(qry2.value(1).isNull()?"1970-01-01 08:00:00":qry2.value(1).toDateTime().toString("yyyy-MM_dd HH:mm:ss")).arg("");
        }else{
            sql += QString(",  (NULL,%1,'%2','%3','%4','%5','%6')")
                    .arg(qry2.value(0).toUInt()).arg(devid).arg(1).arg(qry2.value(2).isNull()?"0":qry2.value(2).toString())
                    .arg(qry2.value(1).isNull()?"1970-01-01 08:00:00":qry2.value(1).toDateTime().toString("yyyy-MM_dd HH:mm:ss")).arg("");
            sql += QString(",  (NULL,%1,'%2','%3','%4','%5','%6')")
                    .arg(qry2.value(0).toUInt()).arg(devid).arg(2).arg(qry2.value(3).isNull()?"0":qry2.value(3).toString())
                    .arg(qry2.value(1).isNull()?"1970-01-01 08:00:00":qry2.value(1).toDateTime().toString("yyyy-MM_dd HH:mm:ss")).arg("");
            sql += QString(", (NULL,%1,'%2','%3','%4','%5','%6')")
                    .arg(qry2.value(0).toUInt()).arg(devid).arg(3).arg(qry2.value(4).isNull()?"0":qry2.value(4).toString())
                    .arg(qry2.value(1).isNull()?"1970-01-01 08:00:00":qry2.value(1).toDateTime().toString("yyyy-MM_dd HH:mm:ss")).arg("");
            sql += QString(",  (NULL,%1,'%2','%3','%4','%5','%6')")
                    .arg(qry2.value(0).toUInt()).arg(devid).arg(4).arg(qry2.value(5).isNull()?"0":qry2.value(5).toString())
                    .arg(qry2.value(1).isNull()?"1970-01-01 08:00:00":qry2.value(1).toDateTime().toString("yyyy-MM_dd HH:mm:ss")).arg("");
        }
        QString wp = qry2.value(2).toString();
        if(wp.compare("WP0")==0)
            upsql = QString::fromUtf8("update sp_owner_status set point_data='%1', data_time='%2', content='%3', point_name='状态',dwtype=%4 where device_id='%5' and point_code='%6';")
                    .arg(0)
                    .arg(qry2.value(1).isNull()?"1970-01-01 08:00:00":qry2.value(1).toDateTime().toString("yyyy-MM_dd HH:mm:ss"))
                    .arg(QString::fromUtf8("正常")).arg(dwtype).arg(devid).arg(1);
        else if(wp.compare("WP1")==0)
            upsql = QString::fromUtf8("update sp_owner_status set point_data='%1', data_time='%2', content='%3', point_name='状态',dwtype=%4 where device_id='%5' and point_code='%6';")
                    .arg(1)
                    .arg(qry2.value(1).isNull()?"1970-01-01 08:00:00":qry2.value(1).toDateTime().toString("yyyy-MM_dd HH:mm:ss"))
                    .arg(QString::fromUtf8("低压")).arg(dwtype).arg(devid).arg(1);
        else if(wp.compare("WP2")==0)
            upsql = QString::fromUtf8("update sp_owner_status set point_data='%1', data_time='%2', content='%3', point_name='状态',dwtype=%4 where device_id='%5' and point_code='%6';")
                    .arg(2)
                    .arg(qry2.value(1).isNull()?"1970-01-01 08:00:00":qry2.value(1).toDateTime().toString("yyyy-MM_dd HH:mm:ss"))
                    .arg(QString::fromUtf8("高压")).arg(dwtype).arg(devid).arg(1);
        else if(wp.compare("WP3")==0)
            upsql = QString::fromUtf8("update sp_owner_status set point_data='%1', data_time='%2', content='%3', point_name='状态',dwtype=%4 where device_id='%5' and point_code='%6';")
                    .arg(3)
                    .arg(qry2.value(1).isNull()?"1970-01-01 08:00:00":qry2.value(1).toDateTime().toString("yyyy-MM_dd HH:mm:ss"))
                    .arg(QString::fromUtf8("故障")).arg(dwtype).arg(devid).arg(1);
        else if(wp.compare("WP4")==0)
            upsql = QString::fromUtf8("update sp_owner_status set point_data='%1', data_time='%2', content='%3', point_name='状态',dwtype=%4 where device_id='%5' and point_code='%6';")
                    .arg(4)
                    .arg(qry2.value(1).isNull()?"1970-01-01 08:00:00":qry2.value(1).toDateTime().toString("yyyy-MM_dd HH:mm:ss"))
                    .arg(QString::fromUtf8("离线")).arg(dwtype).arg(devid).arg(1);
        upsql.append(QString::fromUtf8("update sp_owner_status set point_data='%1', data_time='%2', content='%3', point_name='剩余电量',dwtype=%4 where device_id='%5' and point_code='%6';")
                .arg(qry2.value(3).isNull()?"0":qry2.value(3).toString())
                .arg(qry2.value(1).isNull()?"1970-01-01 08:00:00":qry2.value(1).toDateTime().toString("yyyy-MM_dd HH:mm:ss"))
                .arg("").arg(dwtype).arg(devid).arg(2));
        upsql.append(QString::fromUtf8("update sp_owner_status set point_data='%1', data_time='%2', content='%3', point_name='无线信号',dwtype=%4 where device_id='%5' and point_code='%6';")
                     .arg(qry2.value(4).isNull()?"0":qry2.value(4).toString())
                     .arg(qry2.value(1).isNull()?"1970-01-01 08:00:00":qry2.value(1).toDateTime().toString("yyyy-MM_dd HH:mm:ss"))
                     .arg("").arg(dwtype).arg(devid).arg(3));
        upsql.append(QString::fromUtf8("update sp_owner_status set point_data='%1', data_time='%2', content='%3', point_name='水压/水位',dwtype=%4 where device_id='%5' and point_code='%6';")
                     .arg(qry2.value(5).isNull()?"0":qry2.value(5).toString())
                     .arg(qry2.value(1).isNull()?"1970-01-01 08:00:00":qry2.value(1).toDateTime().toString("yyyy-MM_dd HH:mm:ss"))
                     .arg("").arg(dwtype).arg(devid).arg(4));
        count += 4;
        sync_id = qry2.value(0).toUInt();
        if(count>=100){
            time_t tmp_time = time((time_t *)NULL);
            ytShm->updatetime[DB_THREAD] = tmp_time;
            sql.append(";");
            mdb.exec(sql);
            mdb.exec(QString("update sp_owner_sync set sync_id=%1 where id=%2").arg(sync_id).arg(ID));
            mdb.exec(upsql);
            count=0;
            sql="";
        }
    }
    if(count>0){
        time_t tmp_time = time((time_t *)NULL);
        ytShm->updatetime[DB_THREAD] = tmp_time;
        sql.append(";");
        mdb.exec(sql);
        mdb.exec(QString("update sp_owner_sync set sync_id=%1 where id=%2").arg(sync_id).arg(ID));
        mdb.exec(upsql);
        count=0;
        sql="";
    }
    return updated;
}

bool DB_SyncThread::water2InfoSync(QString devid, QString deviceCode, uint sync_id, QString tbname, QDate chkday, uint ID)
{
    QString qrysql,sql,upsql;
    int count=0;
    bool found = false;
    bool updated = false;
    for(int i=0;i<dList.length();i++){
        if(dList.at(i).compare(deviceCode)==0)
        {
            found = true;
            break;
        }
    }
    if(!found){
        mdb.exec(QString("insert into sp_owner_status (id,device_id,point_name,point_code,point_data,data_time,content) values (NULL,'%1','','1','','1970-01-01 08:00:00','');").arg(deviceCode));
        mdb.exec(QString("insert into sp_owner_status (id,device_id,point_name,point_code,point_data,data_time,content) values (NULL,'%1','','2','','1970-01-01 08:00:00','');").arg(deviceCode));
        mdb.exec(QString("insert into sp_owner_status (id,device_id,point_name,point_code,point_data,data_time,content) values (NULL,'%1','','3','','1970-01-01 08:00:00','');").arg(deviceCode));
        dList.append(deviceCode);
        pcList.append(1);
        dList.append(deviceCode);
        pcList.append(2);
        dList.append(deviceCode);
        pcList.append(3);
    }
    qrysql = QString("select id, DeviceStatus, BatteryLevel, SensorLevel, storeTime from %1 where id>%2 and storeTime>'%3-%4-01 00:00:00' and DeviceType=1")
            .arg(tbname).arg(sync_id).arg(chkday.year(),4,10,QChar('0')).arg(chkday.month(),2,10,QChar('0'));
    QSqlQuery qry2 = mdb.exec(qrysql);
    while(qry2.next()){
        updated = true;
        int sta = qry2.value(1).toInt();
        if(count==0){
            sql = QString::fromUtf8("insert into sp_owner_sync_data (id, native_id, device_id, point_code, point_data, data_time, content) values (NULL,%1,'%2','%3','%4','%5','%6')")
                    .arg(qry2.value(0).toUInt()).arg(devid).arg(1).arg(qry2.value(1).toString())
                    .arg(qry2.value(4).isNull()?"1970-01-01 08:00:00":qry2.value(4).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                    .arg(sta==1?QString::fromUtf8("报警")
                              :(sta==2?QString::fromUtf8("报警静音")
                                     :(sta==4?QString::fromUtf8("低压")
                                            :(sta==5?QString::fromUtf8("故障")
                                                   :(sta==7?QString::fromUtf8("正常")
                                                          :"")))));
            sql += QString(",  (NULL,%1,'%2','%3','%4','%5','%6')")
                    .arg(qry2.value(0).toUInt()).arg(devid).arg(2).arg(qry2.value(2).toString())
                    .arg(qry2.value(4).isNull()?"1970-01-01 08:00:00":qry2.value(4).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                    .arg("");
            sql += QString(",  (NULL,%1,'%2','%3','%4','%5','%6')")
                    .arg(qry2.value(0).toUInt()).arg(devid).arg(3).arg(qry2.value(3).toString())
                    .arg(qry2.value(4).isNull()?"1970-01-01 08:00:00":qry2.value(4).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                    .arg("");
        }else{
            sql += QString::fromUtf8(",  (NULL,%1,'%2','%3','%4','%5','%6')")
                    .arg(qry2.value(0).toUInt()).arg(devid).arg(1).arg(qry2.value(1).toString())
                    .arg(qry2.value(4).isNull()?"1970-01-01 08:00:00":qry2.value(4).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                    .arg(sta==1?QString::fromUtf8("报警")
                              :(sta==2?QString::fromUtf8("报警静音")
                                     :(sta==4?QString::fromUtf8("低压")
                                            :(sta==5?QString::fromUtf8("故障")
                                                   :(sta==7?QString::fromUtf8("正常")
                                                          :"")))));
            sql += QString(",  (NULL,%1,'%2','%3','%4','%5','%6')")
                    .arg(qry2.value(0).toUInt()).arg(devid).arg(2).arg(qry2.value(2).toString())
                    .arg(qry2.value(4).isNull()?"1970-01-01 08:00:00":qry2.value(4).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                    .arg("");
            sql += QString(",  (NULL,%1,'%2','%3','%4','%5','%6')")
                    .arg(qry2.value(0).toUInt()).arg(devid).arg(3).arg(qry2.value(3).toString())
                    .arg(qry2.value(4).isNull()?"1970-01-01 08:00:00":qry2.value(4).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                    .arg("");
        }
        upsql = QString::fromUtf8("update sp_owner_status set point_data='%1', data_time='%2', content='%3', point_name='状态',dwtype=%4 where device_id='%5' and point_code='%6';")
                .arg(sta==7?0:sta)
                .arg(qry2.value(4).isNull()?"1970-01-01 08:00:00":qry2.value(4).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                .arg(sta==1?QString::fromUtf8("报警")
                          :(sta==2?QString::fromUtf8("报警静音")
                                 :(sta==4?QString::fromUtf8("低压")
                                        :(sta==5?QString::fromUtf8("故障")
                                               :(sta==7?QString::fromUtf8("正常")
                                                      :"")))))
                .arg(3).arg(devid).arg(1);
        upsql.append(QString::fromUtf8("update sp_owner_status set point_data='%1', data_time='%2', content='%3', point_name='剩余电量',dwtype=%4 where device_id='%5' and point_code='%6';")
                     .arg(qry2.value(2).toString())
                     .arg(qry2.value(4).isNull()?"1970-01-01 08:00:00":qry2.value(4).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                     .arg("").arg(3).arg(devid).arg(2));
        upsql.append(QString::fromUtf8("update sp_owner_status set point_data='%1', data_time='%2', content='%3', point_name='无线信号',dwtype=%4 where device_id='%5' and point_code='%6';")
                     .arg(qry2.value(3).toString())
                     .arg(qry2.value(4).isNull()?"1970-01-01 08:00:00":qry2.value(4).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                     .arg("").arg(3).arg(devid).arg(3));
        count += 3;
        sync_id = qry2.value(0).toUInt();
        if(count>=100){
            time_t tmp_time = time((time_t *)NULL);
            ytShm->updatetime[DB_THREAD] = tmp_time;
            sql.append(";");
            mdb.exec(sql);
            mdb.exec(QString("update sp_owner_sync set sync_id=%1 where id=%2").arg(sync_id).arg(ID));
            mdb.exec(upsql);
            count=0;
            sql="";
        }
    }
    if(count>0){
        time_t tmp_time = time((time_t *)NULL);
        ytShm->updatetime[DB_THREAD] = tmp_time;
        sql.append(";");
        mdb.exec(sql);
        mdb.exec(QString("update sp_owner_sync set sync_id=%1 where id=%2").arg(sync_id).arg(ID));
        mdb.exec(upsql);
        count=0;
        sql="";
    }
    return updated;
}

bool DB_SyncThread::rtuInfoSync(QString devid, QString deviceCode, uint sync_id, QString tbname, QDate chkday, uint ID)
{
    QString qrysql,sql,upsql="";
    int count=0;
    bool found = false;
    bool updated= false;
    QSqlQuery qry2 = mdb.exec(QString("select count(*) as count, sum(clzt) as sum from sp_rtu2017 where device_code='%1' and status<>'44'").arg(devid));
    if(qry2.next()){
        mdb.exec(QString("update sp_device_sync set alm_count=%1, alm_confirm=%2 where device_id='%3'")
                 .arg(qry2.value(0).toInt())
                 .arg(qry2.value(0).toInt()>0?qry2.value(1).toInt():0)
                 .arg(devid));
    }
    qry2.clear();
    qry2 = mdb.exec(QString("select count(*) as count, sum(clzt) as sum from sp_rtu2017 where device_code='%1' and status='44'").arg(devid));
    if(qry2.next()){
        mdb.exec(QString("update sp_device_sync set com_count=%1, com_confirm=%2 where device_id='%3'")
                 .arg(qry2.value(0).toInt())
                 .arg(qry2.value(0).toInt()>0?qry2.value(1).toInt():0)
                 .arg(devid));
    }
    qry2.clear();
    found = false;
    for(int i=0;i<dList.length();i++){
        if(dList.at(i).compare(deviceCode)==0){
            found = true;
            break;
        }
    }
    if(!found){
        mdb.exec(QString("insert into sp_owner_status (id,device_id,point_name,point_code,point_data,data_time,content) values (NULL,'%1','','%2','','1970-01-01 08:00:00','');").arg(deviceCode).arg(0));
        dList.append(deviceCode);
        pcList.append(0);
    }
    qrysql = QString("select id, status, port, ncmd, data3, data2, data4, time, data1 from %1 where id>%2 and time>'%3-%4-%5 00:00:00'")
            .arg(tbname).arg(sync_id).arg(chkday.year(),4,10,QChar('0')).arg(chkday.month(),2,10,QChar('0')).arg(chkday.day(),2,10,QChar('0'));
    qry2 = mdb.exec(qrysql);
    QSqlQuery qry3 = mdb.exec(QString("select iotype,ports,ioname from sp_owner_port where owner_code='%1'").arg(devid));
    QStringList nameList;
    QList<int> typeList,portList;
    while(qry3.next()){
        typeList.append(qry3.value(0).toInt());
        portList.append(qry3.value(1).toInt());
        nameList.append(qry3.value(2).toString());
        found = false;
        if(qry3.value(0).toInt()==1){
            for(int i=0;i<dList.length();i++){
                if(dList.at(i).compare(deviceCode)==0){
                    if(pcList.at(i)==qry3.value(1).toInt()){
                        found = true;
                        break;
                    }
                }
            }
            if(found)
                mdb.exec(QString("update sp_owner_status set point_name='%1',dwtype=6 where device_id='%2' and point_code='%3'")
                         .arg(qry3.value(2).toString()).arg(devid).arg(qry3.value(1).toInt()));
            else{
                mdb.exec(QString("insert into sp_owner_status (id,device_id,point_name,point_code,point_data,data_time,content) values (NULL,'%1','%2','%3','','1970-01-01 08:00:00','');").arg(deviceCode).arg(qry3.value(2).toString()).arg(qry3.value(1).toInt()));
                dList.append(deviceCode);
                pcList.append(qry3.value(1).toInt());
            }
        }else{
            for(int i=0;i<dList.length();i++){
                if(dList.at(i).compare(deviceCode)==0){
                    if(pcList.at(i)==(qry3.value(1).toInt()+64)){
                        found = true;
                        break;
                    }
                }
            }
            if(found)
                mdb.exec(QString("update sp_owner_status set point_name='%1',dwtype=6 where device_id='%2' and point_code='%3'")
                         .arg(qry3.value(2).toString()).arg(devid).arg(qry3.value(1).toInt()+64));
            else{
                mdb.exec(QString("insert into sp_owner_status (id,device_id,point_name,point_code,point_data,data_time,content) values (NULL,'%1','%2','%3','','1970-01-01 08:00:00','');").arg(deviceCode).arg(qry3.value(2).toString()).arg(qry3.value(1).toInt()+64));
                dList.append(deviceCode);
                pcList.append(qry3.value(1).toInt()+64);
            }
        }
    }
    qry3.clear();
    while(qry2.next()){
        updated = true;
        if(qry2.value(1).toString().compare("44")==0)
        {
            if(count==0)
                sql = QString("insert into sp_owner_sync_data (id, native_id, device_id, point_code, point_data, data_time, content) values (NULL,%1,'%2','%3','%4','%5','%6 %7')")
                        .arg(qry2.value(0).toUInt()).arg(devid).arg(0).arg(1).arg(qry2.value(7).isNull()?"1970-01-01 00:00:00":qry2.value(7).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                        .arg(qry2.value(5).toString()).arg(qry2.value(6).toString());
            else
                sql += QString(",  (NULL,%1,'%2','%3','%4','%5','%6 %7')")
                        .arg(qry2.value(0).toUInt()).arg(devid).arg(0).arg(1).arg(qry2.value(7).isNull()?"1970-01-01 00:00:00":qry2.value(7).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                        .arg(qry2.value(5).toString()).arg(qry2.value(6).toString());
            upsql = QString::fromUtf8("update sp_owner_status set point_data='%1', data_time='%2', content='%3 %4', point_name='通信报警',dwtype=%5 where device_id='%6' and point_code='%7';")
                    .arg(0).arg(qry2.value(7).isNull()?"1970-01-01 00:00:00":qry2.value(7).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                    .arg(qry2.value(5).toString()).arg(qry2.value(6).toString()).arg(6).arg(devid).arg(0);
            mdb.exec(upsql);
        }else{
            upsql = QString::fromUtf8("update sp_owner_status set point_data='',content='' where device_id='%1' and point_code='0'").arg(devid);
            mdb.exec(upsql);
            if(!qry2.value(2).isNull()){
                if(qry2.value(2).toString().compare("E3")==0){
                    if(count==0)
                        sql = QString("insert into sp_owner_sync_data (id, native_id, device_id, point_code, point_data, data_time, content) values (NULL,%1,'%2','%3','%4','%5','%6 %7')")
                                .arg(qry2.value(0).toUInt()).arg(devid).arg(qry2.value(3).toInt()).arg(qry2.value(1).toString())
                                .arg(qry2.value(7).isNull()?"1970-01-01 00:00:00":qry2.value(7).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                               .arg(qry2.value(5).toString()).arg(qry2.value(6).toString());
                    else
                        sql += QString(",  (NULL,%1,'%2','%3','%4','%5','%6 %7')")
                                .arg(qry2.value(0).toUInt()).arg(devid).arg(qry2.value(3).toInt()).arg(qry2.value(1).toString())
                                .arg(qry2.value(7).isNull()?"1970-01-01 00:00:00":qry2.value(7).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                               .arg(qry2.value(5).toString()).arg(qry2.value(6).toString());
                    int tmpIdx = qry2.value(3).toInt();
                    if(tmpIdx>0){
                        QString pname = "";
                        for(int Idx=0;Idx<nameList.length();Idx++){
                            if((typeList.at(Idx)==1)&&(portList.at(Idx)==tmpIdx)){
                                pname = nameList.at(Idx);
                                break;
                            }
                        }
                        upsql = QString::fromUtf8("update sp_owner_status set point_data='%1', data_time='%2', content='%3 %4', point_name='%5',dwtype=%6 where device_id='%7' and point_code='%8';")
                                .arg(qry2.value(1).toString())
                                .arg(qry2.value(7).isNull()?"1970-01-01 00:00:00":qry2.value(7).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                                .arg(qry2.value(5).toString()).arg(qry2.value(6).toString()).arg(pname)
                                .arg(6).arg(devid).arg(tmpIdx);
                        mdb.exec(upsql);
                    }
                }else if(qry2.value(2).toString().compare("E6")==0){
                    if(count==0)
                        sql = QString("insert into sp_owner_sync_data (id, native_id, device_id, point_code, point_data, data_time, content) values (NULL,%1,'%2','%3','%4','%5','%6 %7')")
                                .arg(qry2.value(0).toUInt()).arg(devid).arg(qry2.value(3).toInt()+64).arg(qry2.value(8).toString())
                                .arg(qry2.value(7).isNull()?"1970-01-01 00:00:00":qry2.value(7).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                               .arg(qry2.value(5).toString()).arg(qry2.value(6).toString());
                    else
                        sql += QString(", (NULL,%1,'%2','%3','%4','%5','%6 %7')")
                                .arg(qry2.value(0).toUInt()).arg(devid).arg(qry2.value(3).toInt()+64).arg(qry2.value(8).toString())
                                .arg(qry2.value(7).isNull()?"1970-01-01 00:00:00":qry2.value(7).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                               .arg(qry2.value(5).toString()).arg(qry2.value(6).toString());
                    int tmpIdx = qry2.value(3).toInt();
                    if(tmpIdx>0){
                        QString pname = "";
                        for(int Idx=0;Idx<nameList.length();Idx++){
                            if((typeList.at(Idx)==2)&&(portList.at(Idx)==tmpIdx)){
                                pname = nameList.at(Idx);
                                break;
                            }
                        }
                        upsql = QString::fromUtf8("update sp_owner_status set point_data='%1', data_time='%2', content='%3 %4 %5',dwtype=%6 where device_id='%7' and point_code='%8';")
                                .arg(qry2.value(8).toString())
                                .arg(qry2.value(7).isNull()?"1970-01-01 00:00:00":qry2.value(7).toDateTime().toString("yyyy-MM-dd HH:mm:ss"))
                                .arg(pname).arg(qry2.value(5).toString())
                                .arg(qry2.value(6).toString())
                                .arg(6)
                                .arg(devid)
                                .arg(tmpIdx+64);
                        mdb.exec(upsql);
                    }
                }
            }
        }
        count++;
        sync_id = qry2.value(0).toUInt();
        if(count>=100){
            time_t tmp_time = time((time_t *)NULL);
            ytShm->updatetime[DB_THREAD] = tmp_time;
            sql.append(";");
            mdb.exec(sql);
            mdb.exec(QString("update sp_owner_sync set sync_id=%1 where id=%2").arg(sync_id).arg(ID));
//            mdb.exec(upsql);
            count=0;
            sql="";
            upsql="";
        }
    }
    if(count>0){
        time_t tmp_time = time((time_t *)NULL);
        ytShm->updatetime[DB_THREAD] = tmp_time;
        sql.append(";");
        mdb.exec(sql);
        mdb.exec(QString("update sp_owner_sync set sync_id=%1 where id=%2").arg(sync_id).arg(ID));
//        mdb.exec(upsql);
        count=0;
        sql="";
        upsql="";
    }
    return updated;
}

bool DB_SyncThread::efireInfoSync(QString devid, QString deviceCode, uint sync_id, QString tbname, QDate chkday, uint ID)
{
    QString upsql1, upsql2, upsql3, upsql4,upsql5,upsql6,sql;
    int count=0;
    bool updated = false;
    QSqlQuery qry2 = mdb.exec(QString("select count(*) as count, sum(clzt) as sum from sp_ef2017 where device_code='%1' and data1='EF9'").arg(devid));
    if(qry2.next()){
        mdb.exec(QString("update sp_device_sync set com_count=%1, com_confirm=%2 where device_id='%3'")
                 .arg(qry2.value(0).toInt())
                 .arg(qry2.value(0).toInt()>0?qry2.value(1).toInt():0)
                 .arg(devid));
    }
    qry2.clear();
    qry2 = mdb.exec(QString("select count(*) as count, sum(clzt) as sum from sp_ef2017 where device_code='%1' and data1<>'EF9'").arg(devid));
    if(qry2.next()){
        mdb.exec(QString("update sp_device_sync set alm_count=%1, alm_confirm=%2 where device_id='%3'")
                 .arg(qry2.value(0).toInt())
                 .arg(qry2.value(0).toInt()>0?qry2.value(1).toInt():0)
                 .arg(devid));
    }
    qry2.clear();
    qry2 =mdb.exec(QString("select count(*) from sp_owner_status where device_id='%1'").arg(devid));
    if(qry2.next()){
        if(qry2.value(0).toInt()==0){
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'供电过压',1,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'供电低压',2,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'供电过流',3,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'漏电报警',4,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'A相线缆温度超高',5,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'B相线缆温度超高',6,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'C相线缆温度超高',7,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'A相电压',65,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'B相电压',66,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'C相电压',67,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'A相电流',68,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'B相电流',69,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'C相电流',70,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'A相线缆温度',71,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'B相线缆温度',72,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'C相线缆温度',73,0,'1970-01-01 08:00:00','')").arg(devid));
        }else if(qry2.value(0).toInt()==6){
            mdb.exec(QString::fromUtf8("delete from sp_owner_status where device_id='%1'").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'供电过压',1,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'供电低压',2,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'供电过流',3,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'漏电报警',4,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'A相线缆温度超高',5,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'B相线缆温度超高',6,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'C相线缆温度超高',7,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'A相电压',65,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'B相电压',66,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'C相电压',67,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'A相电流',68,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'B相电流',69,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'C相电流',70,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'A相线缆温度',71,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'B相线缆温度',72,0,'1970-01-01 08:00:00','')").arg(devid));
            mdb.exec(QString::fromUtf8("insert into sp_owner_status (id, device_id, dwtype, point_name, point_code, point_data, data_time, content) values (NULL,'%1',7,'C相线缆温度',73,0,'1970-01-01 08:00:00','')").arg(devid));
        }
    }
    qry2.clear();
    qry2 = mdb.exec(QString("select loop_hl, category,alarm_state, measured_value,a_voltage,b_voltage,c_voltage, a_electric_current,b_electric_current,c_electric_current,acquisition_time,id,a_current_angle,b_current_angle,c_current_angle from %1 where id>%2 and acquisition_time>'%3-%4-%5 00:00:00'")
                    .arg(tbname).arg(sync_id).arg(chkday.year(),4,10,QChar('0')).arg(chkday.month(),2,10,QChar('0')).arg(chkday.day(),2,10,QChar('0')));
    while(qry2.next()){
        updated = true;
        switch(qry2.value(0).toInt()){
        case 1:
            if(count==0){
                sql = QString("insert into sp_owner_sync_data (id, native_id, device_id, point_code, point_data, data_time, content) values (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(4).arg(qry2.value(2).toInt())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(", (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(1).arg(qry2.value(12).toInt())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(", (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(2).arg(qry2.value(13).toInt())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(", (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(3).arg(qry2.value(14).toInt())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(", (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(65).arg(qry2.value(4).toFloat())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(", (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(66).arg(qry2.value(5).toFloat())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(", (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(67).arg(qry2.value(6).toFloat())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(", (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(68).arg(qry2.value(7).toFloat())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(", (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(69).arg(qry2.value(8).toFloat())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(", (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(70).arg(qry2.value(9).toFloat())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
            }else{
                sql += QString(",(NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(4).arg(qry2.value(2).toInt())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(", (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(1).arg(qry2.value(12).toInt())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(", (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(2).arg(qry2.value(13).toInt())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(", (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(3).arg(qry2.value(14).toInt())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(", (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(65).arg(qry2.value(4).toFloat())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(", (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(66).arg(qry2.value(5).toFloat())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(", (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(67).arg(qry2.value(6).toFloat())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(", (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(68).arg(qry2.value(7).toFloat())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(", (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(69).arg(qry2.value(8).toFloat())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(", (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(70).arg(qry2.value(9).toFloat())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
            }
            count += 10;
            upsql1 = QString::fromUtf8("update sp_owner_status set point_data=%1, data_time='%2',point_name='供电过压' where device_id='%3' and point_code=1;")
                    .arg(qry2.value(12).toInt()).arg(qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss")).arg(devid);
            upsql1 += QString::fromUtf8("update sp_owner_status set point_data=%1, data_time='%2', point_name='供电低压' where device_id='%3' and point_code=2; ")
                    .arg(qry2.value(13).toInt()).arg(qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss")).arg(devid);
            upsql1 += QString::fromUtf8("update sp_owner_status set point_data=%1, data_time='%2', point_name='供电过流' where device_id='%3' and point_code=3; ")
                    .arg(qry2.value(14).toInt()).arg(qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss")).arg(devid);
            upsql1 += QString::fromUtf8("update sp_owner_status set point_data=%1, data_time='%2', point_name='漏电报警' where device_id='%3' and point_code=4; ")
                    .arg(qry2.value(2).toInt()).arg(qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss")).arg(devid);
            upsql2 = QString::fromUtf8("update sp_owner_status set point_data=%1, data_time='%2',point_name='A相电压'  where device_id='%3' and point_code=65;")
                    .arg(qry2.value(4).toFloat()).arg(qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss")).arg(devid);
            upsql2 += QString::fromUtf8("update sp_owner_status set point_data=%1, data_time='%2',point_name='B相电压'  where device_id='%3' and point_code=66;")
                    .arg(qry2.value(5).toFloat()).arg(qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss")).arg(devid);
            upsql2 += QString::fromUtf8("update sp_owner_status set point_data=%1, data_time='%2',point_name='C相电压'  where device_id='%3' and point_code=67;")
                    .arg(qry2.value(6).toFloat()).arg(qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss")).arg(devid);
            upsql3 = QString::fromUtf8("update sp_owner_status set point_data=%1, data_time='%2',point_name='A相电流' where device_id='%3' and point_code=68; ")
                    .arg(qry2.value(7).toFloat()).arg(qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss")).arg(devid);
            upsql3 += QString::fromUtf8("update sp_owner_status set point_data=%1, data_time='%2',point_name='B相电流' where device_id='%3' and point_code=69; ")
                    .arg(qry2.value(8).toFloat()).arg(qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss")).arg(devid);
            upsql3 += QString::fromUtf8("update sp_owner_status set point_data=%1, data_time='%2',point_name='C相电流' where device_id='%3' and point_code=70; ")
                    .arg(qry2.value(9).toFloat()).arg(qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss")).arg(devid);
            break;
        case 2:
            if(count==0){
                sql = QString("insert into sp_owner_sync_data (id, native_id, device_id, point_code, point_data, data_time, content) values (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(5).arg(qry2.value(2).toInt())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(",(NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(71).arg(qry2.value(3).toFloat())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
            }else{
                sql += QString(",(NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(5).arg(qry2.value(2).toInt())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(",(NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(71).arg(qry2.value(3).toFloat())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
            }
            count += 2;
            upsql4 = QString::fromUtf8("update sp_owner_status set point_data=%1, data_time='%2',point_name='A相线缆温度超高'  where device_id='%3' and point_code=5;")
                    .arg(qry2.value(2).toInt()).arg(qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss")).arg(devid);
            upsql4 += QString::fromUtf8("update sp_owner_status set point_data=%1, data_time='%2',point_name='A相线缆温度'  where device_id='%3' and point_code=71;")
                    .arg(qry2.value(3).toFloat()).arg(qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss")).arg(devid);
            break;
        case 3:
            if(count==0){
                sql = QString("insert into sp_owner_sync_data (id, native_id, device_id, point_code, point_data, data_time, content) values (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(6).arg(qry2.value(2).toInt())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(",(NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(72).arg(qry2.value(3).toFloat())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
            }else{
                sql += QString(",(NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(6).arg(qry2.value(2).toInt())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(",(NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(72).arg(qry2.value(3).toFloat())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
            }
            count += 2;
            upsql5 = QString::fromUtf8("update sp_owner_status set point_data=%1, data_time='%2',point_name='B相线缆温度超高'  where device_id='%3' and point_code=6;")
                    .arg(qry2.value(2).toInt()).arg(qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss")).arg(devid);
            upsql5 += QString::fromUtf8("update sp_owner_status set point_data=%1, data_time='%2',point_name='B相线缆温度'  where device_id='%3' and point_code=72;")
                    .arg(qry2.value(3).toFloat()).arg(qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss")).arg(devid);
            break;
        case 4:
            if(count==0){
                sql = QString("insert into sp_owner_sync_data (id, native_id, device_id, point_code, point_data, data_time, content) values (NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(7).arg(qry2.value(2).toInt())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(",(NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(73).arg(qry2.value(3).toFloat())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
            }else{
                sql += QString(",(NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(7).arg(qry2.value(2).toInt())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
                sql += QString(",(NULL,%1,'%2','%3','%4','%5','')")
                        .arg(qry2.value(11).toInt()).arg(devid).arg(73).arg(qry2.value(3).toFloat())
                        .arg(qry2.value(10).isNull()?"1970-01-01 08:00:00":qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss"));
            }
            count += 2;
            upsql6 = QString::fromUtf8("update sp_owner_status set point_data=%1, data_time='%2',point_name='C相线缆温度超高'  where device_id='%3' and point_code=7;")
                    .arg(qry2.value(2).toInt()).arg(qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss")).arg(devid);
            upsql6 += QString::fromUtf8("update sp_owner_status set point_data=%1, data_time='%2',point_name='C相线缆温度'  where device_id='%3' and point_code=73;")
                    .arg(qry2.value(3).toFloat()).arg(qry2.value(10).toDateTime().toString("yyyy-MM-dd HH:mm:ss")).arg(devid);
            break;
        }
        sync_id = qry2.value(6).toUInt();
        if(count>=100){
            time_t tmp_time = time((time_t *)NULL);
            ytShm->updatetime[DB_THREAD] = tmp_time;
            sql.append(";");
            mdb.exec(sql);
            mdb.exec(QString("update sp_owner_sync set sync_id=%1 where id=%2").arg(sync_id).arg(ID));
            mdb.exec(upsql1);
            mdb.exec(upsql2);
            mdb.exec(upsql3);
            mdb.exec(upsql4);
            mdb.exec(upsql5);
            mdb.exec(upsql6);
            count=0;
            sql="";
            upsql1="";
            upsql2="";
            upsql3="";
            upsql4="";
            upsql5="";
            upsql6="";
        }
    }
    if(count>0){
        time_t tmp_time = time((time_t *)NULL);
        ytShm->updatetime[DB_THREAD] = tmp_time;
        sql.append(";");
        mdb.exec(sql);
        mdb.exec(QString("update sp_owner_sync set sync_id=%1 where id=%2").arg(sync_id).arg(ID));
        mdb.exec(upsql1);
        mdb.exec(upsql2);
        mdb.exec(upsql3);
        mdb.exec(upsql4);
        mdb.exec(upsql5);
        mdb.exec(upsql6);
        count=0;
        sql="";
        upsql1="";
        upsql2="";
        upsql3="";
        upsql4="";
        upsql5="";
        upsql6="";
    }
    return updated;
}

void DB_SyncThread::chkInList(QString devid)
{
    QSqlQuery qry;
    QString sql;
     for(int i=0;i<syncDevList.length();i++){
        if(devid.compare(syncDevList.at(i).OwnerCode)==0){
            if(syncDevList.at(i).ObjType.compare("YTFC")==0){
//                system(QString("echo \"%1\" >> /root/test.log").arg(QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss")).toUtf8().data());
//                system(QString("echo \"[YTFC] dev:%1 obj:%2 start:%3 rec:%4\">> /root/test.log").arg(syncDevList.at(i).OwnerCode).arg(syncDevList.at(i).ObjCode).arg(syncDevList.at(i).StartPort).arg(syncDevList.at(i).RecCur).toUtf8().data());
                QDateTime dataTime = QDateTime::fromTime_t(0);
                QString PowerAlarm="off";
                QString ManualAct = "off";
                QString DevWorking01 = "off";
                QString DevWorking02 = "off";
                QString DevAlarm01 = "off";
                QString DevAlarm02 = "off";
                QString FireAutoAct = "off";
                sql = QString("select dwtype,point_code,point_data,data_time from sp_owner_status where device_id='%1'").arg(devid);
//                system(QString("echo \"[YTFC] %1\" >> /root/test.log").arg(sql).toUtf8().data());
//                qDebug()<<sql.toUtf8().data();
                qry = mdb.exec(sql);
                while(qry.next()){
                    int code = qry.value(1).toInt();
                    if((code>0)&&(code<8)){
                        int value = qry.value(2).toInt();
                        if(dataTime.toTime_t()<qry.value(3).toDateTime().toTime_t())
                            dataTime = qry.value(3).toDateTime();
                        if(code==1){
                            if(value==0)
                                PowerAlarm = "on";
                        }else if(code==2){
                            if(value==0)
                                ManualAct = "on";
                        }else if(code==3){
                            if(value==0)
                                DevWorking01 = "on";
                        }else if(code==4){
                            if(value==0)
                                DevWorking02 = "on";
                        }else if(code==5){
                            if(value==0)
                                DevAlarm01 = "on";
                        }else if(code==6){
                            if(value==0)
                                DevAlarm02 = "on";
                        }else if(code==7){
                            if(value==0)
                                FireAutoAct = "on";
                        }
                    }
                }
//                if(dataTime.toTime_t()>syncDevList.at(i).RecCur)
                {
                    mdb.exec(QString("update sp_yangpu_share set rec_cur=%1 where object_code='%2'").arg(dataTime.toTime_t()).arg(syncDevList.at(i).ObjCode));
                    QString jsonstr = QString("{\"dataCode\":\"DATA_FIRECONT\",\"deviceId\":\"%1\",\"postTime\":\"%2\",\"data\":{\"PowerAlarm\":\"%3\",\"ManualAct\":\"%4\",\"DevWorking01\":\"%5\",\"DevWorking02\":\"%6\",\"DevAlarm01\":\"%7\",\"DevAlarm02\":\"%8\",\"FireAutoAct\":\"%9\"}}")
                            .arg(syncDevList.at(i).ObjCode).arg(dataTime.toString("yyyy-MM-dd HH:mm:ss")).arg(PowerAlarm).arg(ManualAct).arg(DevWorking01).arg(DevWorking02).arg(DevAlarm01).arg(DevAlarm02).arg(FireAutoAct);
//                    system(QString("echo \"[YTFC] %1\" >> /root/test.log").arg(jsonstr).toUtf8().data());
                    emit SyncData(QUuid::createUuid().toString().replace("{","").replace("}",""), jsonstr);
                }
            }else if(syncDevList.at(i).ObjType.compare("YTCI")==0){
//                system(QString("echo \"%1\" >> /root/test.log").arg(QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss")).toUtf8().data());
//                system(QString("echo \"[YTCI] dev:%1 obj:%2 start:%3 rec:%4\">> /root/test.log").arg(syncDevList.at(i).OwnerCode).arg(syncDevList.at(i).ObjCode).arg(syncDevList.at(i).StartPort).arg(syncDevList.at(i).RecCur).toUtf8().data());
                QString Temperature = "0.0";
                QString Humidity = "0.0";
                QDateTime dataTime = QDateTime::fromTime_t(0);
                sql = QString("select dwtype,point_code,point_data,data_time from sp_owner_status where device_id='%1' and point_code>%2 order by point_code limit 0,2").arg(devid).arg((syncDevList.at(i).StartPort>0)?(syncDevList.at(i).StartPort+63):0);
//                system(QString("echo \"[YTCI]%1\" >> /root/test.log").arg(sql).toUtf8().data());
//                qDebug()<<sql.toUtf8().data();
                qry = mdb.exec(sql);
                int nrow = 0;
                while(qry.next()){
                    if(dataTime.toTime_t()<qry.value(3).toDateTime().toTime_t())
                        dataTime = qry.value(3).toDateTime();
                    if(nrow==0)
                        Temperature = qry.value(2).toString();
                    else if(nrow==1)
                        Humidity = qry.value(2).toString();
                    nrow++;
                }
//                if(dataTime.toTime_t()>syncDevList.at(i).RecCur)
                {
                    mdb.exec(QString("update sp_yangpu_share set rec_cur=%1 where object_code='%2'").arg(dataTime.toTime_t()).arg(syncDevList.at(i).ObjCode));
                    QString jsonstr = QString("{\"dataCode\":\"DATA_ENVIRONM\",\"deviceId\":\"%1\",\"postTime\":\"%2\",\"data\":{\"Temperature\":%3,\"Humidity\":%4}}")
                            .arg(syncDevList.at(i).ObjCode).arg(dataTime.toString("yyyy-MM-dd HH:mm:ss")).arg(Temperature).arg(Humidity);
//                    system(QString("echo \"[YTCI] %1\" >> /root/test.log").arg(jsonstr).toUtf8().data());
                    emit SyncData(QUuid::createUuid().toString().replace("{","").replace("}",""), jsonstr);
                }
            }else if(syncDevList.at(i).ObjType.compare("YTWP")==0){
//                system(QString("echo \"%1\" >> /root/test.log").arg(QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss")).toUtf8().data());
//                system(QString("echo \"[YTWP] dev:%1 obj:%2 start:%3 rec:%4\">> /root/test.log").arg(syncDevList.at(i).OwnerCode).arg(syncDevList.at(i).ObjCode).arg(syncDevList.at(i).StartPort).arg(syncDevList.at(i).RecCur).toUtf8().data());
                QString WaterPressure = "0.0";
                QDateTime dataTime = QDateTime::fromTime_t(0);
                sql = QString("select dwtype,point_code,point_data,data_time from sp_owner_status where device_id='%1' and point_code>%2 order by point_code limit 0,1").arg(devid).arg((syncDevList.at(i).StartPort>0)?(syncDevList.at(i).StartPort+63):3);
//                system(QString("echo \"[YTWP]%1\" >> /root/test.log").arg(sql).toUtf8().data());
//                qDebug()<<sql.toUtf8().data();
                qry = mdb.exec(sql);
                if(qry.next()){
                    if(dataTime.toTime_t()<qry.value(3).toDateTime().toTime_t())
                        dataTime = qry.value(3).toDateTime();
                    WaterPressure = qry.value(2).toString();
//                    if(dataTime.toTime_t()>syncDevList.at(i).RecCur)
                    {
                        mdb.exec(QString("update sp_yangpu_share set rec_cur=%1 where object_code='%2'").arg(dataTime.toTime_t()).arg(syncDevList.at(i).ObjCode));
                        QString jsonstr = QString("{\"dataCode\":\"DATA_WATERPRE\",\"deviceId\":\"%1\",\"postTime\":\"%2\",\"data\":{\"WaterPressure\":%3}}")
                                .arg(syncDevList.at(i).ObjCode).arg(dataTime.toString("yyyy-MM-dd HH:mm:ss")).arg(WaterPressure);
//                        system(QString("echo \"[YTWP] %1\" >> /root/test.log").arg(jsonstr).toUtf8().data());
                        emit SyncData(QUuid::createUuid().toString().replace("{","").replace("}",""), jsonstr);
                    }
                }
            }else if(syncDevList.at(i).ObjType.compare("YTLL")==0){
//                system(QString("echo \"%1\" >> /root/test.log").arg(QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss")).toUtf8().data());
//                system(QString("echo \"[YTLL] dev:%1 obj:%2 start:%3 rec:%4\">> /root/test.log").arg(syncDevList.at(i).OwnerCode).arg(syncDevList.at(i).ObjCode).arg(syncDevList.at(i).StartPort).arg(syncDevList.at(i).RecCur).toUtf8().data());
                QString LiquidLevel = "0.0";
                QDateTime dataTime = QDateTime::fromTime_t(0);
                sql = QString("select dwtype,point_code,point_data,data_time from sp_owner_status where device_id='%1' and point_code>%2 order by point_code limit 0,1").arg(devid).arg((syncDevList.at(i).StartPort>0)?(syncDevList.at(i).StartPort+63):3);
//                system(QString("echo \"[YTLL]%1\" >> /root/test.log").arg(sql).toUtf8().data());
//                qDebug()<<sql.toUtf8().data();
                qry = mdb.exec(sql);
                if(qry.next()){
                    if(dataTime.toTime_t()<qry.value(3).toDateTime().toTime_t())
                        dataTime = qry.value(3).toDateTime();
                    LiquidLevel = qry.value(2).toString();
//                    if(dataTime.toTime_t()>syncDevList.at(i).RecCur)
                    {
                        mdb.exec(QString("update sp_yangpu_share set rec_cur=%1 where object_code='%2'").arg(dataTime.toTime_t()).arg(syncDevList.at(i).ObjCode));
                        QString jsonstr = QString("{\"dataCode\":\"DATA_LIQUIDLE\",\"deviceId\":\"%1\",\"postTime\":\"%2\",\"data\":{\"LiquidLevel\":%3}}")
                                .arg(syncDevList.at(i).ObjCode).arg(dataTime.toString("yyyy-MM-dd HH:mm:ss")).arg(LiquidLevel);
//                        system(QString("echo \"[YTLL] %1\" >> /root/test.log").arg(jsonstr).toUtf8().data());
                        emit SyncData(QUuid::createUuid().toString().replace("{","").replace("}",""), jsonstr);
                    }
                }
            }else if(syncDevList.at(i).ObjType.compare("YTEF")==0){
//                system(QString("echo \"%1\" >> /root/test.log").arg(QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss")).toUtf8().data());
//                system(QString("echo \"[YTEF] dev:%1 obj:%2 start:%3 rec:%4\">> /root/test.log").arg(syncDevList.at(i).OwnerCode).arg(syncDevList.at(i).ObjCode).arg(syncDevList.at(i).StartPort).arg(syncDevList.at(i).RecCur).toUtf8().data());
                QString HighVoltageAlarm ="off";
                QString LowVoltageAlarm = "off";
                QString HighCurrentAlarm = "off";
                QString ElectricityLeakage = "off";
                QString HighTemperatureA = "off";
                QString HighTemperatureB = "off";
                QString HighTemperatureC = "off";
                QString VoltageA = "0.0";
                QString VoltageB = "0.0";
                QString VoltageC = "0.0";
                QString CurrentA = "0.0";
                QString CurrentB = "0.0";
                QString CurrentC = "0.0";
                QString TemperatureA = "0.0";
                QString TemperatureB = "0.0";
                QString TemperatureC = "0.0";
                QDateTime dataTime = QDateTime::fromTime_t(0);
                sql =QString("select dwtype,point_code,point_data,data_time from sp_owner_status where device_id='%1'").arg(devid);
//                system(QString("echo \"[YTEF]%1\" >> /root/test.log").arg(sql).toUtf8().data());
//                qDebug()<<sql.toUtf8().data();
                qry = mdb.exec(sql);
                while(qry.next()){
                    if(dataTime.toTime_t()<qry.value(3).toDateTime().toTime_t())
                        dataTime = qry.value(3).toDateTime();
                    int code = qry.value(1).toInt();
                    if(code==1){
                        if(qry.value(2).toInt()==1)
                            HighVoltageAlarm = "on";
                    }else if(code==2){
                        if(qry.value(2).toInt()==1)
                            LowVoltageAlarm = "on";
                    }else if(code==3){
                        if(qry.value(2).toInt()==1)
                            HighCurrentAlarm = "on";
                    }else if(code==4){
                        if(qry.value(2).toInt()==1)
                            ElectricityLeakage = "on";
                    }else if(code==5){
                        if(qry.value(2).toInt()==1)
                            HighTemperatureA = "on";
                    }else if(code==6){
                        if(qry.value(2).toInt()==1)
                            HighTemperatureB = "on";
                    }else if(code==7){
                        if(qry.value(2).toInt()==1)
                            HighTemperatureC = "on";
                    }else if(code==65)
                        VoltageA = qry.value(2).toString();
                    else if(code==66)
                        VoltageB = qry.value(2).toString();
                    else if(code==67)
                        VoltageC = qry.value(2).toString();
                    else if(code==68)
                        CurrentA = qry.value(2).toString();
                    else if(code==69)
                        CurrentB = qry.value(2).toString();
                    else if(code==70)
                        CurrentC = qry.value(2).toString();
                    else if(code==71)
                        TemperatureA = qry.value(2).toString();
                    else if(code==72)
                        TemperatureB = qry.value(2).toString();
                    else if(code==73)
                        TemperatureC = qry.value(2).toString();
                }
//                if(dataTime.toTime_t()>syncDevList.at(i).RecCur)
                {
                    mdb.exec(QString("update sp_yangpu_share set rec_cur=%1 where object_code='%2'").arg(dataTime.toTime_t()).arg(syncDevList.at(i).ObjCode));
                    QString jsonstr = QString("{\"dataCode\":\"DATA_ElECTRIC\",\"deviceId\":\"%1\",\"postTime\":\"%2\",\"data\":{\"HighVoltageAlarm\":\"%3\",\"LowVoltageAlarm\":\"%4\",\"HighCurrentAlarm\":\"%5\",\"ElectricityLeakage\":\"%6\",\"HighTemperatureA\":\"%7\",\"HighTemperatureB\":\"%8\",\"HighTemperatureC\":\"%9\",\"VoltageA\":%10,\"VoltageB\":%11,\"VoltageC\":%12,\"CurrentA\":%13,\"CurrentB\":%14,\"CurrentC\":%15,\"TemperatureA\":%16,\"TemperatureB\":%17,\"TemperatureC\":%18}}")
                            .arg(syncDevList.at(i).ObjCode).arg(dataTime.toString("yyyy-MM-dd HH:mm:ss")).arg(HighVoltageAlarm).arg(LowVoltageAlarm).arg(HighCurrentAlarm).arg(ElectricityLeakage).arg(HighTemperatureA).arg(HighTemperatureB).arg(HighTemperatureC).arg(VoltageA).arg(VoltageB).arg(VoltageC).arg(CurrentA).arg(CurrentB).arg(CurrentC).arg(TemperatureA).arg(TemperatureB).arg(TemperatureC);
//                    system(QString("echo \"[YTEF] %1\" >> /root/test.log").arg(jsonstr).toUtf8().data());
                    emit SyncData(QUuid::createUuid().toString().replace("{","").replace("}",""), jsonstr);
                }
            }else if(syncDevList.at(i).ObjType.compare("YTVA")==0){
//                system(QString("echo \"%1\" >> /root/test.log").arg(QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss")).toUtf8().data());

            }
        }
    }
}

void DB_SyncThread::time_out()
{
    QSqlQuery qry,qry2;
    QString qrysql;
    if(!isWorking)
    {
        isWorking = true;
        if(!mdbOpened)
        {
            mdbOpened = mdb.open();
        }
        else
        {
//            if(sqlList.length()>0){
//                while(sqlList.length()>0){
//                    system(QString("echo \"%1\" >> /root/test.sql").arg(sqlList.first()).toUtf8().data());
//                    mdb.exec(sqlList.first());
//                    sqlList.removeFirst();
//                    usleep(10000);
//                }
//            }
            syncDevList.clear();
            qry = mdb.exec("select owner_code,dwtype,object_code,start_port,rec_cur from sp_yangpu_share");
            while(qry.next()){
                syncDevList.append(YPSyncDev(qry.value(0).toString(),
                                             qry.value(1).toString(),
                                             qry.value(2).toString(),
                                             qry.value(3).toInt(),
                                             qry.value(4).toUInt()));
            }
            qry.clear();
            qry2 = mdb.exec("select device_id,point_code from sp_owner_status");
            dList.clear();
            pcList.clear();
            while(qry2.next()){
                dList.append(qry2.value(0).toString());
                pcList.append(qry2.value(1).toInt());
            }
            qry2.clear();
            qry = mdb.exec("select id, owner_code, owner_name, unitinfo, install_time, dwtype, company, rtmp from sp_owner");
            while(qry.next())
            {
                time_t tmp_time = time((time_t *)NULL);
                ytShm->updatetime[DB_THREAD] = tmp_time;
                int dwtype = qry.value(5).toInt();
                uint ID = qry.value(0).toUInt();
                QDate chkday = QDate::currentDate().addDays(-7);
                bool need_sync = false;
                qry2 =mdb.exec(QString("select count(*) from  sp_device_sync where device_id='%1'").arg(qry.value(1).toString()));
                if(qry2.next()){
                    if(qry2.value(0).toInt()==0){
                        mdb.exec(QString("insert into sp_device_sync (device_id,com_count,com_confirm,alm_count,alm_confirm) values ('%1',0,0,0,0)").arg(qry.value(1).toString()));
                    }
                }
                qry2.clear();
                qrysql = QString("select count(*),table_name,sync_id from sp_owner_sync where id=%1").arg(ID);
                qry2 = mdb.exec(qrysql);
                if(qry2.next()){
                    QString deviceCode = qry.value(1).toString();
                    QString tbname = QString("sp_%1%2").arg(qry.value(5).toInt()==3?"y":"d").arg(deviceCode);
                    uint sync_id = 0;
                    if(qry2.value(0).toInt()==0){
                        mdb.exec(QString("insert into sp_owner_sync  (id, owner_code, owner_name, unitinfo, install_time, dwtype, company, rtmp, table_name, sync_id) values (%1,'%2','%3', '%4', '%5', %6, '%7', '%8', '%9', %10) ")
                                 .arg(qry.value(0).toInt()).arg(qry.value(1).toString()).arg(qry.value(2).toString()).arg(qry.value(3).toString()).arg(qry.value(4).toString())
                                 .arg(qry.value(5).toInt()).arg(qry.value(6).toString()).arg(qry.value(7).toString()).arg(tbname).arg(sync_id));

                    }else{
                        sync_id = qry2.value(2).toUInt();
                    }
                    qry2.clear();
                    switch(dwtype){
                    case 1://userinfo
                        need_sync = userInfoSync(qry.value(1).toString(),deviceCode,sync_id, tbname, chkday,ID);
                        break;
                    case 2://water
                    case 5:
                        need_sync = waterInfoSync(qry.value(1).toString(),deviceCode,sync_id,tbname,chkday,ID,dwtype);
                        break;
                    case 3:
                        need_sync = water2InfoSync(qry.value(1).toString(),deviceCode,sync_id,tbname,chkday,ID);
                        break;
                    case 6:
                        need_sync = rtuInfoSync(qry.value(1).toString(),deviceCode,sync_id,tbname,chkday,ID);
                        break;
                    case 7:
                        need_sync = efireInfoSync(qry.value(1).toString(),deviceCode,sync_id,tbname,chkday,ID);
                        break;
                    }
                    qry2.clear();
                    if(need_sync)
                        chkInList(qry.value(1).toString());
                }
                usleep(5000);
            }
            qry.clear();
            qry = mdb.exec("select a.company as company, a.dwtype as dwtype, sum(a.devCount) as devCount, sum(a.com_count) as comCount, sum(a.com_confirm) as comConfirm, sum(a.alm_count) as almCount, sum(a.alm_confirm) as almConfirm from (select sp_owner.company, sp_device_sync.device_id, sp_owner.dwtype, 1 as devCount, sp_device_sync.com_count, sp_device_sync.com_confirm, sp_device_sync.alm_count, sp_device_sync.alm_confirm from sp_owner, sp_device_sync where sp_owner.owner_code = sp_device_sync.device_id) a where a.dwtype in (1,2,5,6,7) and a.company<>'' group by a.company, a.dwtype");
            while(qry.next()){
                QString sql2="";
                qry2 = mdb.exec(QString("select count(*) from sp_company_sync where company_code='%1'").arg(qry.value(0).toString()));
                if(qry2.next()){
                    if(qry2.value(0).toInt()==0){
                        sql2 = QString("insert into sp_company_sync (company_code, device_00_count, device_00_out, device_00_out_confirm, device_00_alarm, device_00_alarm_confirm,device_01_count, device_01_out, device_01_out_confirm, device_01_alarm, device_01_alarm_confirm, device_02_count, device_02_out, device_02_out_confirm, device_02_alarm, device_02_alarm_confirm,device_06_count, device_06_out, device_06_out_confirm, device_06_alarm, device_06_alarm_confirm,device_07_count, device_07_out, device_07_out_confirm, device_07_alarm, device_07_alarm_confirm) values ('%1',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);")
                                .arg(qry.value(0).toString());
                    }
                }
                qry2.clear();
                if(qry.value(1).toInt()==1)
                    sql2 += QString("update sp_company_sync set device_01_count=%1, device_01_out=%2, device_01_out_confirm=%3, device_01_alarm=%4, device_01_alarm_confirm=%5 where company_code='%6'")
                            .arg(qry.value(2).toInt()).arg(qry.value(3).toInt()).arg(qry.value(4).toInt()).arg(qry.value(5).toInt()).arg(qry.value(6).toInt()).arg(qry.value(0).toString());
                else if(qry.value(1).toInt()==2)
                    sql2 += QString("update sp_company_sync set device_02_count=%1, device_02_out=%2, device_02_out_confirm=%3, device_02_alarm=%4, device_02_alarm_confirm=%5 where company_code='%6'")
                             .arg(qry.value(2).toInt()).arg(qry.value(3).toInt()).arg(qry.value(4).toInt()).arg(qry.value(5).toInt()).arg(qry.value(6).toInt()).arg(qry.value(0).toString());
                else if(qry.value(1).toInt()==5)
                    sql2 += QString("update sp_company_sync set device_00_count=%1, device_00_out=%2, device_00_out_confirm=%3, device_00_alarm=%4, device_00_alarm_confirm=%5 where company_code='%6'")
                             .arg(qry.value(2).toInt()).arg(qry.value(3).toInt()).arg(qry.value(4).toInt()).arg(qry.value(5).toInt()).arg(qry.value(6).toInt()).arg(qry.value(0).toString());
                else if(qry.value(1).toInt()==6)
                    sql2 += QString("update sp_company_sync set device_06_count=%1, device_06_out=%2, device_06_out_confirm=%3, device_06_alarm=%4, device_06_alarm_confirm=%5 where company_code='%6'")
                             .arg(qry.value(2).toInt()).arg(qry.value(3).toInt()).arg(qry.value(4).toInt()).arg(qry.value(5).toInt()).arg(qry.value(6).toInt()).arg(qry.value(0).toString());
                else if(qry.value(1).toInt()==7)
                    sql2 += QString("update sp_company_sync set device_07_count=%1, device_07_out=%2, device_07_out_confirm=%3, device_07_alarm=%4, device_07_alarm_confirm=%5 where company_code='%6'")
                             .arg(qry.value(2).toInt()).arg(qry.value(3).toInt()).arg(qry.value(4).toInt()).arg(qry.value(5).toInt()).arg(qry.value(6).toInt()).arg(qry.value(0).toString());
                mdb.exec(sql2);
                qry2.clear();
                usleep(5000);
            }
            qry.clear();
            QDate d_day = QDate::currentDate().addDays(-7);
            QString delsql = QString("delete from sp_owner_sync_data where data_time<'%1-%2-%3 00:00:00'").arg(d_day.year(),4,10,QChar('0')).arg(d_day.month(),2,10,QChar('0')).arg(d_day.day(),2,10,QChar('0'));
            mdb.exec(delsql);
            qry = mdb.exec("select sp_owner_status.id, sp_owner_status.device_id, sp_owner_status.data_time, sp_owner.dwtype from sp_owner_status, sp_owner where sp_owner_status.device_id=sp_owner.owner_code and sp_owner.dwtype=1 and sp_owner_status.point_data !='0'");
            while(qry.next()){
                qry2 = mdb.exec(QString("select clzt from sp_hj2017 where device_code=%1 and time='%2'")
                                .arg(qry.value(1).toString()).arg(qry.value(2).toDateTime().toString("yyyy-MM-dd HH:mm:ss")));
                if((qry2.next())&&(qry2.value(0).toInt()==1))
                    mdb.exec(QString("update sp_owner_status set point_data='0' where id=%1").arg(qry.value(0).toInt()));
                qry.clear();
                usleep(5000);
            }
            qry.clear();
            qry = mdb.exec("select sp_owner_status.id, sp_owner_status.device_id, sp_owner_status.data_time, sp_owner_status.point_data from sp_owner_status, sp_owner where sp_owner_status.point_code=1 and sp_owner_status.device_id=sp_owner.owner_code and (sp_owner.dwtype=2 or sp_owner.dwtype=5) and sp_owner_status.point_data !='0' and sp_owner_status.point_data !='WP0'");
            while(qry.next()){
                qry2 = mdb.exec(QString("select clzt from sp_sj2017 where device_code=%1 and time='%2'")
                                .arg(qry.value(1).toString()).arg(qry.value(2).toDateTime().toString("yyyy-MM-dd HH:mm:ss")));
                if((qry2.next())&&(qry2.value(0).toInt()==1))
                    mdb.exec(QString("update sp_owner_status set point_data='0',data_time='%1' where device_id='%2' and point_code=0").arg(qry.value(2).toDateTime().toString("yyyy-MM-dd HH:mm:ss")).arg(qry.value(1).toString()));
                else
                    mdb.exec(QString("update sp_owner_status set point_data='%1',data_time='%2' where device_id='%3' and point_code=0").arg(qry.value(3).toString()).arg(qry.value(2).toDateTime().toString("yyyy-MM-dd HH:mm:ss")).arg(qry.value(1).toString()));
                usleep(5000);
            }
            mdb.close();
            mdbOpened = false;
        }
        isWorking = false;
    }

}

void DB_SyncThread::run()
{
    while(1)
    {
        time_t tmp_time = time((time_t *)NULL);
        ytShm->updatetime[DB_THREAD] = tmp_time;
        time_out();
        sleep(5);
    }
}