#include "sql_db.hpp"
#include <unistd.h>
#include <boost/algorithm/string.hpp>
#include <zlog.h>
#include <json/json.h>
#include "mqttclient/mqtt_client.h"
#include "common/global.hpp"
#include "utility/calculation.hpp"
#include "scheduler/schedule.hpp"

extern zlog_category_t *zct;
extern zlog_category_t *zbt;
char  resend_mqttData[512000] = {0};
// clang-format off
const char *T_SENSOR_INFO1[] = {"t_sensor_info", "dataNodeNo",     "dataNodeName",    "initFlag",       "accFlag",       "zigbeeFlag",      "temTopFlag",   "temBotFlag",   "equipSta", "hardVersion", "softVersion",      "bpNo",
                                      "serialNo",      "firstPowerTime", "configFlag",      "startBrands",    "stopBrands",    "featureInterVal", "waveInterVal", "samplingRate", "scope",    "range",       "envelopeBandPass", "faultFrequency",
                                      "zigbeePanId",   "zigbeeChannel",  "zigbeeShortAddr", "zigbeeLongAddr", "zigbeeDesAddr", "status",          "timeStamp",    "viff",         "RSSI"};

const char *T_DATA_INFO1[] = {"t_data_info", "dataNodeNo", "channelID", "diagnosisPk", "integratPk", "integratRMS", "rmsValues",   "envelopEnergy", "Amp1",    "Amp2",      "Amp3",
                                    "Amp4",        "Amp5",       "Phase1",    "Phase2",      "Phase3",     "Phase4",      "StaticIndex", "timeStamp",     "sendMsg", "nodeResend","nodeTimestamp"};

const char *T_DATASTATIC_INFO1[] = {"t_datastatic_info", "dataNodeNo", "channelID", "temTop", "temBot", "dip", "voltage", "StaticIndex", "timeStamp", "sendMsg", "nodeResend","nodeTimestamp"};

const char *T_DATANODE_TIME1[] = {"t_datanode_time", "dataNodeNo", "shortaddr", "staticcycle", "wavecycle", "nodegroup", "nodeindex", "nodewaveindex", "statictime", "staticstarttime"};

const char *T_GATEWAY_INFO1[] = {"t_gateway_info", "gatewayMAC",     "sensorVersion", "gatewayLocation", "zigbeePanID", "zigbeeChannel", "communicationType", "signal", "localIP",
                                       "systemVersion",  "programVersion", "webVersion",    "serverIP",        "serverPort",  "status",        "gateWayUpdate",     "MAC2"};

const char *T_BATTERY_INFO1[] = {"t_battery_info", "dataNodeNo", "Dip", "temBot", "nodeWorkTime", "nodeSendTime", "batteryVoltage", "batteryUsage", "batteryRemain", "timeStamp"};
// clang-format on

SqliteDB::SqliteDB() {}

SqliteDB::~SqliteDB() {}

void SqliteDB::SqliteInit(const char *pDbName) {
    char sql_exec[2048];

    //创建传感器信息存储表
    memset(sql_exec, 0, 2048);
    sprintf(sql_exec, "create table if not exists %s(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);",
						T_SENSOR_INFO(TNAME),
						T_SENSOR_INFO(DATANODENO),
						T_SENSOR_INFO(DATANODENAME), 
					    T_SENSOR_INFO(INITFLAG), 
						T_SENSOR_INFO(ACCFLAG), 
						T_SENSOR_INFO(ZIGBEEFLAG), 
						T_SENSOR_INFO(TEMTOPFLAG),
						T_SENSOR_INFO(TEMBOTFLAG),
						T_SENSOR_INFO(EQUIPSTA),
						T_SENSOR_INFO(HARDVERSION),
						T_SENSOR_INFO(SOFTVERSION),
						T_SENSOR_INFO(BPNO),
						T_SENSOR_INFO(SERIALNO),
						T_SENSOR_INFO(FIRSTPOWERNO),
						"WakeupTime",
						"StaticTime",
						"WaveTime",
						"BateryV",
						"ProductNo",
				        T_SENSOR_INFO(CONFIGFLAG),
						T_SENSOR_INFO(STARTBRANDS),
						T_SENSOR_INFO(STOPBRANDS),
						T_SENSOR_INFO(FEATUREINTERVAL),
						T_SENSOR_INFO(WAVEINTERVAL),
						T_SENSOR_INFO(SAMPLINGRATE),
						T_SENSOR_INFO(SCOPE),
						T_SENSOR_INFO(RANGE),
						T_SENSOR_INFO(ENVELOPEBANDPASS),
						T_SENSOR_INFO(FAULTFREQUENCY),
						T_SENSOR_INFO(ZIGBEEPANID),
						T_SENSOR_INFO(ZIGBEECHANNEL),
						T_SENSOR_INFO(ZIGBEESHORTADDR),
						T_SENSOR_INFO(ZIGBEELONGADDR),
						T_SENSOR_INFO(ZIGBEEDESADDR),
						"ZigbeePower",
						"ZigbeeRetry",
						"ZigbeeRetryGap",
						"ACCSampleTime",
						T_SENSOR_INFO(STATUS),
						T_SENSOR_INFO(TIMESTAMP),
						T_SENSOR_INFO(VIFF),
    					T_SENSOR_INFO(RSSI),
						"UpdateFlag",
						"LooseValue",
						"batteryPower");
    CreateTable(sql_exec);

    int iRet = GetTableRows(" sqlite_master "," name = 't_sensor_info' and sql LIKE '%LooseValue%' ");
    if(iRet == 0){
    	CreateTable("ALTER TABLE t_sensor_info ADD COLUMN 'LooseValue'");
    }

    iRet = GetTableRows(" sqlite_master "," name = 't_sensor_info' and sql LIKE '%batteryPower%' ");
    if(iRet == 0){
        CreateTable("ALTER TABLE t_sensor_info ADD COLUMN 'batteryPower'");
    }
	iRet = GetTableRows(" sqlite_master "," name = 't_sensor_info' and sql LIKE '%MeasurementID%' ");
    if(iRet == 0){
        CreateTable("ALTER TABLE t_sensor_info ADD COLUMN 'MeasurementID'");
    }
	iRet = GetTableRows(" sqlite_master "," name = 't_sensor_info' and sql LIKE '%NodeWaveSend%' ");
    if(iRet == 0){
        CreateTable("ALTER TABLE t_sensor_info ADD COLUMN 'NodeWaveSend'");
    }
    iRet = GetTableRows(" sqlite_master "," name = 't_sensor_info' and sql LIKE '%upgradeStatus%' ");
    if(iRet == 0){
        CreateTable("ALTER TABLE t_sensor_info ADD COLUMN 'upgradeStatus'");
    }
    iRet = GetTableRows(" sqlite_master "," name = 'receive_wave_status' and sql LIKE '%resend%' ");
    if(iRet == 0){
        CreateTable("ALTER TABLE receive_wave_status ADD COLUMN 'resend'");
    }

	memset(sql_exec, 0, 2048);
	sprintf(sql_exec,"update t_sensor_info set MeasurementID = dataNodeNo where MeasurementID IS NULL ;");
	UpdateTableData(sql_exec);
	memset(sql_exec, 0, 2048);
	sprintf(sql_exec,"update t_sensor_info set MeasurementID = dataNodeNo where MeasurementID = '' ;");
	UpdateTableData(sql_exec);
    memset(sql_exec, 0, 2048);
	sprintf(sql_exec,"update t_sensor_info set NodeWaveSend = '0,0,0' where NodeWaveSend = '' ;");
	UpdateTableData(sql_exec);
	memset(sql_exec, 0, 2048);
	sprintf(sql_exec,"update t_sensor_info set NodeWaveSend = '0,0,0' where NodeWaveSend IS NULL ;");
	UpdateTableData(sql_exec);

    sprintf(sql_exec,"update t_sensor_info set upgradeStatus = '0' where upgradeStatus IS NULL ;");
	UpdateTableData(sql_exec);
    sprintf(sql_exec,"update t_sensor_info set upgradeStatus = '0' where upgradeStatus = '' ;");
	UpdateTableData(sql_exec);
    
    //创建传感器数据存储表
    memset(sql_exec, 0, 2048);
    // clang-format off
    sprintf(sql_exec, "create table if not exists %s(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s integer,%s,%s,%s,%s);",
                       T_DATA_INFO(TNAME),
                       T_DATA_INFO(DATANODENO),
                       T_DATA_INFO(CHANNELID), 
                       T_DATA_INFO(DIAGNOSISEAK), 
                       T_DATA_INFO(INTEGRATPK), 
                       T_DATA_INFO(INTEGRATRMS), 
                       T_DATA_INFO(RMSVALUES),
                       T_DATA_INFO(ENVELOPNERGY),
                       T_DATA_INFO(AMP1),
                       T_DATA_INFO(AMP2),
                       T_DATA_INFO(AMP3),
                       T_DATA_INFO(AMP4),
                       T_DATA_INFO(AMP5),
                       T_DATA_INFO(PHASE1),
                       T_DATA_INFO(PHASE2),
                       T_DATA_INFO(PHASE3),
                       T_DATA_INFO(PHASE4),
					   T_DATA_INFO(STATICINDEX),
                       T_DATA_INFO(TIMESTAMP),
					   T_DATA_INFO(SENDMSG),
					   T_DATA_INFO(NODERESEND),
                       T_DATA_INFO(NODETIMESTAMP)
                    );
    // clang-format on
    CreateTable(sql_exec);

    iRet = GetTableRows(" sqlite_master "," name = 't_data_info' and sql LIKE '%nodeResend%' ");
	if(iRet == 0){
		CreateTable("ALTER TABLE t_data_info ADD COLUMN 'nodeResend'");
	}
    iRet = GetTableRows(" sqlite_master "," name = 't_data_info' and sql LIKE '%nodeTimeStamp%' ");
	if(iRet == 0){
		CreateTable("ALTER TABLE t_data_info ADD COLUMN 'nodeTimeStamp'");
	}
    iRet = GetTableRows(" sqlite_master "," name = 't_data_info' and sql LIKE '%kurtosis%' ");
	if(iRet == 0){
		CreateTable("ALTER TABLE t_data_info ADD COLUMN 'kurtosis'");
	}
    iRet = GetTableRows(" sqlite_master "," name = 't_data_info' and sql LIKE '%IntegratRMSMENS%' ");
	if(iRet == 0){
		CreateTable("ALTER TABLE t_data_info ADD COLUMN 'IntegratRMSMENS'");
	}

    // 创建传感器静态数据存储表
    memset(sql_exec, 0, 2048);
    sprintf(sql_exec, "create table if not exists %s(%s,%s,%s,%s,%s,%s,%s,%s integer,%s,%s,%s,%s);",
                       T_DATASTATIC_INFO(TNAME),
                       T_DATASTATIC_INFO(DATANODENO),
                       T_DATASTATIC_INFO(CHANNELID), 
                       T_DATASTATIC_INFO(TEMTOP), 
                       T_DATASTATIC_INFO(TEMBOT), 
                       T_DATASTATIC_INFO(DIP), 
                       T_DATASTATIC_INFO(VOLTAGE),
					   "Battery",
					   T_DATASTATIC_INFO(STATICINDEX),
                       T_DATASTATIC_INFO(TIMESTAMP),
					   T_DATASTATIC_INFO(SENDMSG),
					   T_DATASTATIC_INFO(NODERESEND),
                       T_DATASTATIC_INFO(NODETIMESTAMP)
                    );
    CreateTable(sql_exec);
    iRet = GetTableRows(" sqlite_master "," name = 't_datastatic_info' and sql LIKE '%nodeResend%' ");
	if(iRet == 0){
		CreateTable("ALTER TABLE t_datastatic_info ADD COLUMN 'nodeResend'");
	}
    iRet = GetTableRows(" sqlite_master "," name = 't_datastatic_info' and sql LIKE '%nodeTimeStamp%' ");
	if(iRet == 0){
		CreateTable("ALTER TABLE t_datastatic_info ADD COLUMN 'nodeTimeStamp'");
	}

    memset(sql_exec, 0, 2048);
    sprintf(sql_exec, "create table if not exists %s(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);",
    					T_GATEWAY_INFO(TNAME),
    					T_GATEWAY_INFO(GATEWAYMAC),
						T_GATEWAY_INFO(SENSORVER),
						T_GATEWAY_INFO(GATEWAYLOCATION),
						T_GATEWAY_INFO(ZIGBEEPANID),
						T_GATEWAY_INFO(ZIGBEECHANNEL),
						T_GATEWAY_INFO(COMMUNICATIONTYPE),
						T_GATEWAY_INFO(SIGNAL),
						T_GATEWAY_INFO(LOCALIP),
						T_GATEWAY_INFO(SYSTEMVERSION),
						T_GATEWAY_INFO(PROGRAMVERSION),
						T_GATEWAY_INFO(WEBVERSION),
						T_GATEWAY_INFO(SERVERIP),
						T_GATEWAY_INFO(SERVERPORT),
						T_GATEWAY_INFO(STATUS),
						T_GATEWAY_INFO(GATEWAYUPDATE),
						T_GATEWAY_INFO(MAC2));
   CreateTable(sql_exec);

   memset(sql_exec, 0, 2048);
   sprintf(sql_exec, "create table if not exists %s(%s,%s,%s,%s,%s);",
    		   	   "t_data_waveSend","channelID","waveName","timeStamp","sendMsg","save");
   CreateTable(sql_exec);

   iRet = GetTableRows(" sqlite_master "," name = 't_data_waveSend' and sql LIKE '%sendMsg%' ");
   if(iRet == 0){
   		CreateTable("ALTER TABLE t_data_waveSend ADD COLUMN 'sendMsg'");
   }
   iRet = GetTableRows(" sqlite_master "," name = 't_data_waveSend' and sql LIKE '%save%' ");
   if(iRet == 0){
	   CreateTable("ALTER TABLE t_data_waveSend ADD COLUMN 'save'");
   }
   iRet = GetTableRows(" sqlite_master "," name = 't_data_waveSend' and sql LIKE '%error_code%' ");
   if(iRet == 0){
	   CreateTable("ALTER TABLE t_data_waveSend ADD COLUMN 'error_code'");
   }

   memset(sql_exec, 0, 2048);
   sprintf(sql_exec, "create table if not exists %s(%s,%s,%s,%s,%s,%s,%s,%s,%s);",
    					T_BATTERY_INFO(TNAME),
    					T_BATTERY_INFO(DATANODENO),
						T_BATTERY_INFO(DIP),
						T_BATTERY_INFO(TEMBOT),
						T_BATTERY_INFO(NODEWORKTIME),
						T_BATTERY_INFO(NODESENDTIME),
						T_BATTERY_INFO(BATTERYVOLTAGE),
						T_BATTERY_INFO(BATTERYUSAGE),
						T_BATTERY_INFO(BATTERYREMAIN),
						T_BATTERY_INFO(TIMESTAMP));
    CreateTable(sql_exec);

    memset(sql_exec, 0, 2048);
	sprintf(sql_exec, "create table if not exists %s(%s,%s,%s,%s,%s,%s,%s,%s,%s);",
    					"t_battery_history",
    					T_BATTERY_INFO(DATANODENO),
						T_BATTERY_INFO(DIP),
						T_BATTERY_INFO(TEMBOT),
						T_BATTERY_INFO(NODEWORKTIME),
						T_BATTERY_INFO(NODESENDTIME),
						T_BATTERY_INFO(BATTERYVOLTAGE),
						T_BATTERY_INFO(BATTERYUSAGE),
						T_BATTERY_INFO(BATTERYREMAIN),
						T_BATTERY_INFO(TIMESTAMP));
   CreateTable(sql_exec);

   execute_sql_file("/opt/configenv/firmware_upgrade.sql");
        
   execute_sql_file("/opt/configenv/receive_wave_status.sql");

   execute_sql_file("/opt/configenv/reboot_record.sql");

   execute_sql_file("/opt/configenv/blueteeth_info.sql");

   execute_sql_file("/opt/configenv/t_sensor_bt_info.sql");

   execute_sql_file("/opt/configenv/t_system_info.sql");

   execute_sql_file("/opt/configenv/t_process_info.sql");

   execute_sql_file("/opt/configenv/t_shutdown_info.sql");

   execute_sql_file("/opt/configenv/t_wave_triger_info.sql");

   execute_sql_file("/opt/configenv/t_debug_info.sql");

   vec_t vetRes = sqlite_db_ctrl::instance().GetDataMultiLineOfOneColumn(T_SENSOR_INFO(TNAME), "MeasurementID", NULL);
    for(size_t i = 0; i < vetRes.size() && vetRes.size() > 0; i++){
        std::string strMeasurementID = boost::algorithm::trim_copy(vetRes[i]);
        if(strMeasurementID.size() > 0){
            std::string strTmp = "";
            char sztableName[100] = {0x00},sql[1024] = {0x00};
            sprintf(sztableName, "t_dataStatic_%s", strMeasurementID.c_str());
            strTmp = "name = '" + std::string(sztableName) + "' and sql LIKE '%nodeTimeStamp%' ";
            iRet = GetTableRows(" sqlite_master ",strTmp.c_str());
	        if(iRet == 0){
                memset(sql, 0, 1024);
                sprintf(sql,"ALTER TABLE t_dataStatic_%s ADD COLUMN 'nodeTimeStamp'", strMeasurementID.c_str());
		        CreateTable(sql);
	        }
            memset(sql, 0, 1024);
            memset(sztableName, 0, 100);
            sprintf(sztableName, "t_data_%s", strMeasurementID.c_str());
            strTmp = "name = '" + std::string(sztableName) + "' and sql LIKE '%nodeTimeStamp%' ";
            iRet = GetTableRows(" sqlite_master ",strTmp.c_str());
	        if(iRet == 0){
                memset(sql, 0, 1024);
                sprintf(sql,"ALTER TABLE t_data_%s ADD COLUMN 'nodeTimeStamp'", strMeasurementID.c_str());
		        CreateTable(sql);
	        }
        }
    }
}

void SqliteDB::Createtable(const char *ptableName) {
    char sql_exec[2048];
    //创建传感器数据存储表
    memset(sql_exec, 0, 2048);
    sprintf(sql_exec, "create table if not exists %s(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s integer,%s,%s,%s,%s,%s,%s);", ptableName, T_DATA_INFO(DATANODENO), T_DATA_INFO(CHANNELID), T_DATA_INFO(DIAGNOSISEAK), T_DATA_INFO(INTEGRATPK), T_DATA_INFO(INTEGRATRMS),
            T_DATA_INFO(RMSVALUES), T_DATA_INFO(ENVELOPNERGY), T_DATA_INFO(AMP1), T_DATA_INFO(AMP2), T_DATA_INFO(AMP3), T_DATA_INFO(AMP4), T_DATA_INFO(AMP5), T_DATA_INFO(PHASE1), T_DATA_INFO(PHASE2), T_DATA_INFO(PHASE3), T_DATA_INFO(PHASE4), "StaticIndex", T_DATA_INFO(TIMESTAMP),
            "sendMsg", "nodeResend", "nodeTimestamp","kurtosis","IntegratRMSMENS");
    CreateTable(sql_exec);
    memset(sql_exec, 0, 2048);
    sprintf(sql_exec, "CREATE INDEX %s_1 ON %s (%s)", ptableName, ptableName, T_DATA_INFO(DATANODENO));
    CreateTable(sql_exec);
}

void SqliteDB::SqliteInitDel(const char *pDbName) {
    Deletetable(T_DATA_INFO(TNAME));
    Deletetable(T_DATASTATIC_INFO(TNAME));
    vec_t vecResult = GetDataMultiLineOfOneColumn(T_SENSOR_INFO(TNAME), " MeasurementID ", NULL);
    for (size_t i = 0; i < vecResult.size() && vecResult.size() > 0; i++) {
        char sztableName[100] = {0x00};
        sprintf(sztableName, "t_data_%s", vecResult[i].c_str());
        Deletetable(sztableName);
        sprintf(sztableName, "t_dataStatic_%s", vecResult[i].c_str());
        Deletetable(sztableName);
        
    }
}

void SqliteDB::Deletetable(const char *ptableName) {
    char sql_exec[2048];
    //创建传感器数据存储表
    memset(sql_exec, 0, 2048);
    sprintf(sql_exec, "DROP TABLE  %s ;", ptableName);
    int iRet = sqlite3_exec(GetDbHandle(), sql_exec, 0, 0, NULL);
    if (iRet != SQLITE_OK) {
        zlog_error(zbt, "fail to delete table:%s", ptableName);
    }
}

void SqliteDB::CreatedataStatictable(const char *ptableName) {
    char sql_exec[2048];
    //创建传感器数据存储表
    memset(sql_exec, 0, 2048);
    sprintf(sql_exec, "create table if not exists %s(%s,%s,%s,%s,%s,%s,%s,%s integer,%s,%s,%s,%s integer,%s,%s,%s,%s,%s,%s);", ptableName, T_DATASTATIC_INFO(DATANODENO), T_DATASTATIC_INFO(CHANNELID), T_DATASTATIC_INFO(TEMTOP), T_DATASTATIC_INFO(TEMBOT), T_DATASTATIC_INFO(DIP),
            T_DATASTATIC_INFO(VOLTAGE), "zigbeeSignal", "StaticIndex", T_DATASTATIC_INFO(TIMESTAMP), "sendMsg", "nodeResend", "zigbeeSignalNode", "statisticType", "timing", "nodeTimestamp","minmumBatteryVoltageType","instantaneousBatteryVoltage","comprehensiveRSSI");
    CreateTable(sql_exec);
    memset(sql_exec, 0, 2048);
    sprintf(sql_exec, "CREATE INDEX %s_1 ON %s (%s)", ptableName, ptableName, T_DATA_INFO(DATANODENO));
    CreateTable(sql_exec);
}

bool SqliteDB::OpenDB(const char *pDbName) {
    int ret = 0;
    ret = sqlite3_open(pDbName, &mDBAcess);
    if (ret == SQLITE_OK) {
        sqlite3_exec(mDBAcess, "PRAGMA synchronous = NORMAL;", 0, 0, 0);
        sqlite3_exec(mDBAcess, "PRAGMA cache_size=8000;", 0, 0, 0);
        sqlite3_exec(mDBAcess, "PRAGMA temp_store = MEMORY;", 0, 0, 0);
        sqlite3_exec(mDBAcess, "PRAGMA auto_vacuum = 1;", 0, 0, 0);
        zlog_info(zbt, "Success To Open DataBase!");
    } else {
        zlog_error(zbt, "Fail To Open DataBase:%s, ret:%d", pDbName, ret);
        return false;
    }

    return true;
}

int SqliteDB::CloseDB() { return sqlite3_close(mDBAcess); }

sqlite3 *SqliteDB::GetDbHandle() { return mDBAcess; }

int SqliteDB::CreateTable(const char *sql) {
    zlog_info(zct, "%s", sql);
    char *msg;
    int iRet = sqlite3_exec(GetDbHandle(), sql, 0, 0, &msg);
    if (iRet != SQLITE_OK) {
        zlog_error(zbt, "[CreateTable] sqlite3 error: code=%d msg=%s sql=[%s]", iRet, msg, sql);
        sqlite3_free(msg);
    }
    return iRet;
}

int SqliteDB::ExeSql(const char *sql) {
    zlog_info(zct, "%s", sql);
    char *msg;
    int iRet = sqlite3_exec(GetDbHandle(), sql, 0, 0, &msg);
    if (iRet != SQLITE_OK) {
        zlog_error(zct, "sqlite3 error: code=%d msg=%s sql=[%s]", iRet, msg, sql);
        sqlite3_free(msg);
    }
    return iRet;
}

int SqliteDB::GetTableColumnCount(const char *tablename) {
    std::string strSql = "select * from ";
    int count = 0;
    sqlite3_stmt *stmt;
    strSql = strSql + tablename + ";";
    zlog_info(zct, "%s", strSql.c_str());
    sqlite3_prepare_v2(GetDbHandle(), strSql.c_str(), -1, &stmt, 0);
    count = sqlite3_column_count(stmt);
    sqlite3_finalize(stmt);
    return count;
}

int SqliteDB::GetTableRows(const char *tablename, const char *whereCon) {
    int nRow = 0;
    std::string strSql = "select count(*) from ";
    if (whereCon != NULL) {
        strSql = strSql + tablename + " where " + whereCon + ";";
    } else {
        strSql = strSql + tablename + ";";
    }
    zlog_info(zct, "[GetTableRows] sql:%s", strSql.c_str());
    sqlite3_stmt *stmt;
    if (sqlite3_prepare_v2(mDBAcess, strSql.c_str(), -1, &stmt, 0) != SQLITE_OK) {
        zlog_error(zct, "sqlite3_prepare_v2:%s, sql:%s", sqlite3_errmsg(mDBAcess), strSql.c_str());
        sqlite3_finalize(stmt);
        return -1;
    }
    int retStep = sqlite3_step(stmt);
    if (retStep == SQLITE_ROW) {
        nRow = sqlite3_column_int(stmt, 0);
    }
    sqlite3_finalize(stmt);
    return nRow;
}

int SqliteDB::AlterTable(const char *tablename, const char *column, bool isAdd) {
    int iRet = SQLITE_ERROR;
    if (isAdd) {
        std::string strSql = "alter table ";
        strSql = strSql + tablename + " add " + column + ";";
        zlog_info(zct, "[AlterTable] sql:%s", strSql.c_str());
        iRet = sqlite3_exec(GetDbHandle(), strSql.c_str(), 0, 0, NULL);
        if (iRet != SQLITE_OK) {
            zlog_error(zct, "sqlite3_exec ret:%d, sql:[%s]", iRet, strSql.c_str());
        }
    }
    return iRet;
}

vec_t SqliteDB::GetDataSingleLine(const char *tablename, const char *column, const char *whereCon) {
    vec_t vecResult;
    std::string strSql = "select ";
    if (whereCon != NULL) {
        strSql = strSql + column + " from " + tablename + " where " + whereCon + ";";
    } else {
        strSql = strSql + column + " from " + tablename + ";";
    }
    zlog_info(zct, "[GetDataSingleLine] sql:%s", strSql.c_str());
    sqlite3_stmt *stmt;
    if (sqlite3_prepare_v2(mDBAcess, strSql.c_str(), -1, &stmt, 0) != SQLITE_OK) {
        zlog_error(zct, "sqlite3_prepare_v2:%s, sql:[%s]", sqlite3_errmsg(mDBAcess), strSql.c_str());
        sqlite3_finalize(stmt);
        return vecResult;
    }
    int retStep = sqlite3_step(stmt);
    int column_count = sqlite3_column_count(stmt);
    if (retStep == SQLITE_ROW) {
        for (int iCol = 0; iCol < column_count; iCol++) {
            char *columninfo = (char *)sqlite3_column_text(stmt, iCol);
            std::string str = columninfo != NULL ? columninfo : "";
            vecResult.push_back(str);
        }
    }
    sqlite3_finalize(stmt);
    return vecResult;
}
vec_t SqliteDB::GetDataSingleLine(const char *sql) {
    vec_t vecResult;
    zlog_info(zct, "[GetDataSingleLine] sql:%s", sql);
    sqlite3_stmt *stmt;
    if (sqlite3_prepare_v2(mDBAcess, sql, -1, &stmt, 0) != SQLITE_OK) {
        zlog_error(zct, "sqlite3_prepare_v2:%s, sql:[%s]", sqlite3_errmsg(mDBAcess), sql);
        sqlite3_finalize(stmt);
        return vecResult;
    }
    int retStep = sqlite3_step(stmt);
    int column_count = sqlite3_column_count(stmt);
    if (retStep == SQLITE_ROW) {
        for (int iCol = 0; iCol < column_count; iCol++) {
            char *columninfo = (char *)sqlite3_column_text(stmt, iCol);
            std::string str = columninfo != NULL ? columninfo : "";
            vecResult.push_back(str);
        }
    }
    sqlite3_finalize(stmt);
    return vecResult;
}
std::string SqliteDB::GetData(const char *tablename, const char *column, const char *whereCon) {
    std::string strRes = "";
    std::string strSql = "select ";
    if (whereCon != NULL) {
        strSql = strSql + column + " from " + tablename + " where " + whereCon + ";";
    } else {
        strSql = strSql + column + " from " + tablename + ";";
    }
    zlog_info(zct, "[GetData] sql:%s", strSql.c_str());
    sqlite3_stmt *stmt;

    mtx_.lock();
    if (sqlite3_prepare_v2(mDBAcess, strSql.c_str(), -1, &stmt, 0) != SQLITE_OK) {
        zlog_error(zct, "sqlite3_prepare_v2:%s, sql:[%s]", sqlite3_errmsg(mDBAcess), strSql.c_str());
        sqlite3_finalize(stmt);
        mtx_.unlock();
        return strRes;
    }
    int retStep = sqlite3_step(stmt);
    if (retStep == SQLITE_ROW) {
        char *columninfo = (char *)sqlite3_column_text(stmt, 0);
        strRes = columninfo != NULL ? columninfo : "";
    }
    sqlite3_finalize(stmt);
    mtx_.unlock();
    zlog_info(zct, "[GetData] sql123:%s", strSql.c_str());
    return strRes;
}
array_t SqliteDB::GetDataMultiLine(const char *sql){
    array_t arrResult;
    zlog_info(zct, "[GetDataMultiLine] sql:%s", sql);
    sqlite3_stmt *stmt;
    mtx_.lock();
    if (sqlite3_prepare_v2(mDBAcess, sql, -1, &stmt, 0) != SQLITE_OK) {
        zlog_error(zct, "sqlite3_prepare_v2:%s, sql:[%s]", sqlite3_errmsg(mDBAcess), sql);
        sqlite3_finalize(stmt);
        mtx_.unlock();
        return arrResult;
    }
    int retStep = sqlite3_step(stmt);
    int column_count = sqlite3_column_count(stmt);
    while (retStep == SQLITE_ROW) {
        vec_t vecResult;
        for (int iCol = 0; iCol < column_count; iCol++) {
            char *columninfo = (char *)sqlite3_column_text(stmt, iCol);
            std::string str = columninfo != NULL ? columninfo : "";
            vecResult.push_back(str);
        }
        arrResult.push_back(vecResult);
        retStep = sqlite3_step(stmt);
    }
    sqlite3_finalize(stmt);
    mtx_.unlock();
    return arrResult;
}
array_t SqliteDB::GetDataMultiLine(const char *tablename, const char *column, const char *whereCon) {
    array_t arrResult;
    std::string strSql = "select ";
    if (whereCon != NULL) {
        strSql = strSql + column + " from " + tablename + " where " + whereCon + ";";
    } else {
        strSql = strSql + column + " from " + tablename + ";";
    }

    zlog_info(zct, "[GetDataMultiLine] sql:%s", strSql.c_str());
    sqlite3_stmt *stmt;
    mtx_.lock();
    if (sqlite3_prepare_v2(mDBAcess, strSql.c_str(), -1, &stmt, 0) != SQLITE_OK) {
        zlog_error(zct, "sqlite3_prepare_v2:%s, sql:[%s]", sqlite3_errmsg(mDBAcess), strSql.c_str());
        sqlite3_finalize(stmt);
        mtx_.unlock();
        return arrResult;
    }
    int retStep = sqlite3_step(stmt);
    int column_count = sqlite3_column_count(stmt);
    while (retStep == SQLITE_ROW) {
        vec_t vecResult;
        for (int iCol = 0; iCol < column_count; iCol++) {
            char *columninfo = (char *)sqlite3_column_text(stmt, iCol);
            std::string str = columninfo != NULL ? columninfo : "";
            vecResult.push_back(str);
        }
        arrResult.push_back(vecResult);
        retStep = sqlite3_step(stmt);
    }
    sqlite3_finalize(stmt);
    mtx_.unlock();
    return arrResult;
}

array_t SqliteDB::GetDataMultiLineTransaction(const char *tablename, const char *column, const char *whereCon) {
    array_t arrResult;
    std::string strSql = "select ";
    if (whereCon != NULL) {
        strSql = strSql + column + " from " + tablename + " where " + whereCon + ";";
    } else {
        strSql = strSql + column + " from " + tablename + ";";
    }
    zlog_info(zct, "[GetDataMultiLineTransaction] sql:%s", strSql.c_str());
    sqlite3_exec(mDBAcess, "BEGIN", 0, 0, NULL);
    sqlite3_stmt *stmt;
    if (sqlite3_prepare_v2(mDBAcess, strSql.c_str(), -1, &stmt, 0) != SQLITE_OK) {
        zlog_error(zct, "sqlite3_prepare_v2:%s, sql:[%s]", sqlite3_errmsg(mDBAcess), strSql.c_str());
        sqlite3_finalize(stmt);
        return arrResult;
    }
    sqlite3_reset(stmt);
    sqlite3_bind_int(stmt, 1, 0);
    int retStep = sqlite3_step(stmt);
    int column_count = sqlite3_column_count(stmt);
    while (retStep == SQLITE_ROW) {
        vec_t vecResult;
        for (int iCol = 0; iCol < column_count; iCol++) {
            char *columninfo = (char *)sqlite3_column_text(stmt, iCol);
            std::string str = columninfo != NULL ? columninfo : "";
            vecResult.push_back(str);
        }
        arrResult.push_back(vecResult);
        retStep = sqlite3_step(stmt);
    }
    sqlite3_finalize(stmt);
    sqlite3_exec(mDBAcess, "COMMIT", 0, 0, NULL);
    return arrResult;
}

vec_t SqliteDB::GetDataMultiLineOfOneColumn(const char *tablename, const char *column, const char *whereCon) {
    vec_t vecResult;
    std::string strSql = "select ";
    if (whereCon != NULL) {
        strSql = strSql + column + " from " + tablename + " where " + whereCon + ";";
    } else {
        strSql = strSql + column + " from " + tablename + ";";
    }
    zlog_info(zct, "[GetDataMultiLineOfOneColumn] sql:%s", strSql.c_str());
    sqlite3_stmt *stmt;
    if (sqlite3_prepare_v2(mDBAcess, strSql.c_str(), -1, &stmt, 0) != SQLITE_OK) {
        zlog_error(zct, "sqlite3_prepare_v2:%s, sql:[%s]", sqlite3_errmsg(mDBAcess), strSql.c_str());
        sqlite3_finalize(stmt);
        return vecResult;
    }
    int retStep = sqlite3_step(stmt);
    while (retStep == SQLITE_ROW) {
        char *columninfo = (char *)sqlite3_column_text(stmt, 0);
        std::string str = columninfo != NULL ? columninfo : "";
        vecResult.push_back(str);
        retStep = sqlite3_step(stmt);
    }
    sqlite3_finalize(stmt);
    return vecResult;
}

vec_Value SqliteDB::GetDataMultiLineOfOneColumnDouble(const char *tablename, const char *column, const char *whereCon) {
    vec_Value vecResult;
    std::string strSql = "select ";
    if (whereCon != NULL) {
        strSql = strSql + column + " from " + tablename + " where " + whereCon + ";";
    } else {
        strSql = strSql + column + " from " + tablename + ";";
    }

    zlog_info(zct, "[GetDataMultiLineOfOneColumnDouble] sql:%s", strSql.c_str());
    sqlite3_stmt *stmt;
    if (sqlite3_prepare_v2(mDBAcess, strSql.c_str(), -1, &stmt, 0) != SQLITE_OK) {
        zlog_error(zct, "sqlite3_prepare_v2:%s, sql:[%s]", sqlite3_errmsg(mDBAcess), strSql.c_str());
        sqlite3_finalize(stmt);
        return vecResult;
    }
    int retStep = sqlite3_step(stmt);
    while (retStep == SQLITE_ROW) {
        double columninfo = sqlite3_column_double(stmt, 0);
        vecResult.push_back(columninfo);
        retStep = sqlite3_step(stmt);
    }
    sqlite3_finalize(stmt);
    return vecResult;
}

int SqliteDB::DeleteTableData(const char *tablename, const char *whereCond) {
    std::string strSql = "delete from ";
    if (whereCond != NULL) {
        strSql = strSql + tablename + " where " + whereCond + ";";
    } else {
        strSql = strSql + tablename + ";";
    }

    zlog_info(zct, "[DeleteTableData] sql:%s", strSql.c_str());
    char *msg;
    int iRet = sqlite3_exec(GetDbHandle(), strSql.c_str(), 0, 0, &msg);

    if (iRet != SQLITE_OK) {
        zlog_error(zct, "sqlite3 error: code=%d msg=%s sql=[%s]", iRet, msg, strSql.c_str());
        sqlite3_free(msg);
    }
    return iRet;
}

int SqliteDB::DeleteTableDataOneConditon(const char *tablename, const char *condColumnName, const char *condColumnValue) {
    std::string strSql = "delete from ";
    if (condColumnName != NULL) {
        strSql = strSql + tablename + " where " + condColumnName + "='" + condColumnValue + "';";
    } else {
        strSql = strSql + tablename + ";";
    }
    zlog_info(zct, "[DeleteTableDataOneConditon] sql:%s", strSql.c_str());
    char *msg;
    int iRet = sqlite3_exec(GetDbHandle(), strSql.c_str(), 0, 0, &msg);

    if (iRet != SQLITE_OK) {
        zlog_error(zct, "sqlite3 error: code=%d msg=%s sql=[%s]", iRet, msg, strSql.c_str());
        sqlite3_free(msg);
    }
    return iRet;
}

int SqliteDB::UpdateNodeNameData(const char *tablename, const char *updateColumn, const char *whereCond) {
    std::string strSql = "update ";
    char szSql[1024] = {0x00};
    strcat(szSql, "update ");
    if (whereCond != NULL) {
        strcat(szSql, tablename);
        strcat(szSql, " set ");
        strcat(szSql, updateColumn);
        strcat(szSql, " where ");
        strcat(szSql, whereCond);
        strcat(szSql, ";");
        strSql = strSql + tablename + " set " + updateColumn + " where " + whereCond + ";";
    } else {
        strSql = strSql + tablename + " set " + updateColumn + ";";
    }
    zlog_info(zct, "[UpdateNodeNameData] sql:%s", strSql.c_str());
    char *msg;
    int iRet = sqlite3_exec(GetDbHandle(), szSql, 0, 0, &msg);

    if (iRet != SQLITE_OK) {
        zlog_error(zct, "sqlite3 error: code=%d msg=%s sql=[%s]", iRet, msg, strSql.c_str());
        sqlite3_free(msg);
    }
    return iRet;
}

int SqliteDB::UpdateTableData(const char *tablename, const char *updateColumn, const char *whereCond) {
    std::string strSql = "update ";
    if (whereCond != NULL) {
        strSql = strSql + tablename + " set " + updateColumn + " where " + whereCond + ";";
    } else {
        strSql = strSql + tablename + " set " + updateColumn + ";";
    }
    zlog_info(zct, "[UpdateTableData] sql:%s", strSql.c_str());
    char *msg;
    mtx_.lock();
    int iRet = sqlite3_exec(GetDbHandle(), strSql.c_str(), 0, 0, &msg);

    if (iRet != SQLITE_OK) {
        zlog_error(zct, "sqlite3 error: code=%d msg=%s sql=[%s]", iRet, msg, strSql.c_str());
        sqlite3_free(msg);
    }
    mtx_.unlock();
    return iRet;
}

int SqliteDB::UpdateTableData(const char *directSql) {
    zlog_info(zct, "[UpdateTableData] sql:%s", directSql);
    char *msg;
    int iRet = sqlite3_exec(GetDbHandle(), directSql, 0, 0, &msg);
    if (iRet != SQLITE_OK) {
        zlog_error(zct, "sqlite3 error: code=%d msg=%s sql=[%s]", iRet, msg, directSql);
        sqlite3_free(msg);
    }
    return iRet;
}

int SqliteDB::UpdateTableDataOneColumn(const char *tablename, const char *columnName, const char *columnValue, const char *whereColName, const char *whereColValue) {
    std::string strSql = "update ";
    if (whereColName != NULL) {
        strSql = strSql + tablename + " set " + columnName + "='" + columnValue + "'" + " where " + whereColName + "='" + whereColValue + "';";
    } else {
        strSql = strSql + tablename + " set " + columnName + "='" + columnValue + "';";
    }
    zlog_info(zct, "[UpdateTableDataOneColumn] sql:%s", strSql.c_str());
    char *msg;
    int iRet = sqlite3_exec(GetDbHandle(), strSql.c_str(), 0, 0, &msg);
    if (iRet != SQLITE_OK) {
        zlog_error(zct, "sqlite3 error: code=%d msg=%s sql=[%s]", iRet, msg, strSql.c_str());
        sqlite3_free(msg);
    }
    return iRet;
}
int callback(void *data, int argc, char **argv, char **azColName) {
    // 如果需要处理查询结果，可以在此回调函数中进行操作
    for (int i = 0; i < argc; i++) {
        zlog_error(zct,"%s = %s", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    return 0;
}

int SqliteDB::execute_sql_file(const char *filename) {
    FILE *file = fopen(filename, "r");
    if (file == NULL) {
        zlog_error(zct,"无法打开 SQL 文件");
        return 1;
    }

    // 获取文件大小
    fseek(file, 0, SEEK_END);
    long file_size = ftell(file);
    fseek(file, 0, SEEK_SET);

    // 为文件内容分配内存
    char *sql = (char *)malloc(file_size + 1);
    if (sql == NULL) {
        zlog_error(zct,"内存分配失败");
        fclose(file);
        return 2;
    }

    // 读取文件内容到内存
    fread(sql, 1, file_size, file);
    sql[file_size] = '\0';  // 确保字符串结尾

    fclose(file);

    // 执行 SQL 文件中的语句
    char *err_msg = NULL;
    if (sqlite3_exec(GetDbHandle(), sql, callback, 0, &err_msg) != SQLITE_OK) {
        zlog_error(zct, "执行 SQL 错误: %s", err_msg);
        sqlite3_free(err_msg);
        free(sql);
        return 3;
    }
    free(sql);
    return 0;
}
int SqliteDB::InsertData(const char *tablename, const char *insertValues, int replace, bool expandable) {
    char *msg;
    int iRet = 0;
    if (expandable) {
        char *strSql = (char *)malloc(4096);
        if (strSql == NULL) {
            zlog_error(zct, "malloc error");
            abort();
        }
        char strReplace[] = "replace into ";
        memset(strSql, 0, 4096);
        if (replace == 0) {
            memset(strReplace, 0, sizeof(strSql));
        }
        sprintf(strSql, "%s %s%s %s %s %s", "insert into", strReplace, tablename, "values(", insertValues, ");");
        zlog_info(zct, "[InsertData] expandable sql:%s", strSql);

        iRet = sqlite3_exec(GetDbHandle(), strSql, 0, 0, &msg);
        if (iRet != SQLITE_OK) {
            zlog_error(zct, "sqlite3 error: code=%d msg=%s sql=[%s]", iRet, msg, strSql);
            sqlite3_free(msg);
        }
        free(strSql);
    } else {
        std::string strSql = "insert into ";
        if (replace != 0) {
            strSql = "replace into ";
        }
        strSql = strSql + tablename + " values(" + insertValues + ");";
        zlog_info(zct, "[InsertData] nonexpandable sql:%s", strSql.c_str());

        iRet = sqlite3_exec(GetDbHandle(), strSql.c_str(), 0, 0, &msg);
        if (iRet != SQLITE_OK) {
            zlog_error(zct, "sqlite3 error: code=%d msg=%s sql=[%s]", iRet, msg, strSql.c_str());
            sqlite3_free(msg);
        }
    }

    return iRet;
}

int SqliteDB::InsertData(const char *insertSql) {
    char *msg;
    int iRet = sqlite3_exec(GetDbHandle(), insertSql, 0, 0, &msg);
    if (iRet != SQLITE_OK) {
        zlog_error(zct, "sqlite3 error: code=%d msg=%s sql=[%s]", iRet, msg, insertSql);
        sqlite3_free(msg);
    }
    return iRet;
}

int SqliteDB::CalculateBattery() {
    zlog_info(zct, "CalculateBattery start");
    char whereCon[1024] = {0};
    char selectSql[1024] = {0};
    memset(whereCon, 0x00, sizeof(whereCon));
    memset(selectSql, 0x00, sizeof(selectSql));
    char updateSql[1024] = {0};
    int res = 0;
    sprintf(selectSql, " MeasurementID,StaticTime,WaveTime,featureInterVal,waveInterVal,samplingRate,batteryPower ");
    array_t vecRes = GetDataMultiLine(T_SENSOR_INFO(TNAME), selectSql, NULL);
    zlog_info(zct, "Size = %d", vecRes.size());
    if (vecRes.size() > 0) {
        for (size_t i = 0; i < vecRes.size(); i++) {
            float capacity = 0.0, startCapacity = 0.0;
            memset(whereCon, 0x00, sizeof(whereCon));
            memset(selectSql, 0x00, sizeof(selectSql));
            sprintf(whereCon, " dataNodeNo = '%s' and batteryRemain <> '' order by timeStamp desc limit 0,1 ", vecRes[i][0].c_str());
            vec_t vecResSig = sqlite_db_ctrl::instance().GetDataSingleLine(T_BATTERY_INFO(TNAME), " * ", whereCon);
            std::vector<std::string> vParam;
            boost::split(vParam, vecRes[i][6], boost::is_any_of(","), boost::token_compress_on);
            std::string total_battery = "" , remain_battery = "";
            if(vParam.size() > 1){
                total_battery = vParam[0];
                remain_battery = vParam[1];
            }
            if (vParam.size() <= 0 || vecResSig.size() <= 0 || total_battery == "" || remain_battery == "") { // 第一次计算
                memset(whereCon, 0x00, sizeof(whereCon));
                sprintf(whereCon, "dataNodeNo = '%s' ", vecRes[i][0].c_str());
                std::string Dip = sqlite_db_ctrl::instance().GetData(T_DATASTATIC_INFO(TNAME), " dip ", whereCon);
                if (Dip == "") {
                    continue;
                }
                capacity = (0.9 + 0.1 * (90 - atoi(Dip.c_str())) / 90) * 19000; // mAh 电池总量
                startCapacity = capacity;

                sprintf(updateSql, "batteryPower = '%f,%f' ", startCapacity, startCapacity);
                memset(whereCon, 0x00, sizeof(whereCon));
                sprintf(whereCon, "MeasurementID = '%s' ", vecRes[i][0].c_str());
                res = UpdateTableData(T_SENSOR_INFO(TNAME), updateSql, whereCon);
                if(res !=0 ){
                    zlog_error(zct, "res = %d", res);
                    continue;
                }
                memset(whereCon, 0x00, sizeof(whereCon));
                sprintf(whereCon, " dataNodeNo = '%s' order by timeStamp asc limit 0,1 ", vecRes[i][0].c_str());
                vecResSig = sqlite_db_ctrl::instance().GetDataSingleLine(T_BATTERY_INFO(TNAME), " * ", whereCon);
                if (vecResSig.size() <= 0) { // 一条数据都没有
                    continue;
                }
            } else {
                capacity = atof(vecResSig[7].c_str());
            }

            zlog_info(zct, "dip = %d", atoi(vecResSig[1].c_str()));

            zlog_info(zct, "capacity = %f", capacity);
            sprintf(whereCon, " dataNodeNo = '%s' and timeStamp > '%s'", vecRes[i][0].c_str(), vecResSig[8].c_str());
            array_t vecResbattery = GetDataMultiLine(T_BATTERY_INFO(TNAME), " * ", whereCon);
            zlog_info(zct, "vecResbattery size = %d", vecResbattery.size());
            if (vecResbattery.size() <= 0) {
                continue;
            }
            int y10_mins = 10 * 365 * 24 * 60;
            int d200_mins = 200 * 24 * 60;
            int x1 = 25, x2 = 60;
            int y1 = 2;
            float y2 = (float)y10_mins / (float)d200_mins;

            float k = (y2 - y1) / (x2 - x1);
            std::vector<float> vecb;
            std::vector<long> vecworkTime;
            std::vector<long> vecsendTime;
            float to_math = 0.0;
            zlog_info(zct, "vecResbattery = %d,temp = %s", vecResbattery.size(), vecResbattery[0][2].c_str());

            for (size_t j = 0; j < vecResbattery.size(); j++) {
                float b = 2 - 25 * k;
                float dpm = k * atoi(vecResbattery[j][2].c_str()) + b; // 温度
                float cost_e = dpm * atoi(vecRes[i][3].c_str());       // 特征值时间间隔
                float cost_h = cost_e / 60;
                vecworkTime.push_back(atol(vecResbattery[j][3].c_str()));
                vecsendTime.push_back(atol(vecResbattery[j][4].c_str()));
                to_math += cost_h;
            }

            zlog_info(zct, "self discharge = %f", to_math);
            long sumworkTime = 0.0, sumsendTime = 0.0;
            for (size_t j = 0; j < vecworkTime.size(); j++) {
                sumworkTime += vecworkTime[j];
            }
            for (size_t j = 0; j < vecsendTime.size(); j++) {
                sumsendTime += vecsendTime[j];
            }
            zlog_info(zct, "sumworkTime = %ld,sumsendTime = %ld", sumworkTime, sumsendTime);

            float usageworkTime = ((float)sumworkTime / 3600000) * 4;
            float usagesendTime = ((float)sumsendTime / 3600000) * 39;
            zlog_info(zct, "work = %f,send = %f", usageworkTime, usagesendTime);

            if (to_math < 0) to_math = 0;
            float usageBattery = usageworkTime + usagesendTime + to_math;
            zlog_info(zct, "have used = %f", atof(vecResSig[6].c_str()));

            float remainBattery = capacity - usageBattery * 0.2;
            if (remainBattery < 10) {
                remainBattery = 10;
            }

            zlog_info(zct, "dataNodeNo = %s,batteryUsage = %f,batteryRemain = %f", vecRes[i][0].c_str(), atof(vecResSig[6].c_str()), remainBattery);
            memset(whereCon, 0x00, sizeof(whereCon));
            sprintf(whereCon, " dataNodeNo = '%s'  order by timeStamp desc limit 0,1 ", vecRes[i][0].c_str());
            std::string strtimeStamp = GetData(T_BATTERY_INFO(TNAME), " timeStamp ", whereCon);

            sprintf(updateSql, "batteryUsage='%f',batteryRemain='%f'", usageBattery, remainBattery);
            memset(whereCon, 0x00, sizeof(whereCon));
            sprintf(whereCon, "dataNodeNo ='%s' and timeStamp = '%s'", vecRes[i][0].c_str(), strtimeStamp.c_str());
            res = UpdateTableData(T_BATTERY_INFO(TNAME), updateSql, whereCon);
            if(res !=0 ){
                zlog_error(zct, "res = %d", res);
                continue;
            }
            memset(whereCon, 0x00, sizeof(whereCon));
            memset(updateSql, 0x00, sizeof(updateSql));

            char insertSql[1024] = {0x00}, deleteSql[1024] = {0x00};
            sprintf(insertSql, "insert into t_battery_history select * from t_battery_info where timeStamp < '%s' and dataNodeNo = '%s'", strtimeStamp.c_str(), vecRes[i][0].c_str());
            res = ExeSql(insertSql);
            if(res !=0 ){
                zlog_error(zct, "res = %d", res);
                continue;
            }
            sprintf(deleteSql, "delete  from t_battery_info where timeStamp < '%s' and dataNodeNo = '%s'", strtimeStamp.c_str(), vecRes[i][0].c_str());
            res = ExeSql(deleteSql);
            if(res !=0 ){
                zlog_error(zct, "res = %d", res);
                continue;
            }
            sprintf(whereCon, "MeasurementID = '%s' ", vecRes[i][0].c_str());
            if (startCapacity > 0) {
                sprintf(updateSql, "batteryPower = '%f,%f' ", startCapacity, remainBattery);
            } else {
                sprintf(updateSql, "batteryPower = '%s,%f' ", total_battery.c_str(), remainBattery);
            }

            UpdateTableData(T_SENSOR_INFO(TNAME), updateSql, whereCon);
            std::string strData = sqlite_db_ctrl::instance().GetNodeConfigureInfor(whereCon);
            res = data_publish(strData.c_str(), GlobalConfig::Topic_G.mPubConfig.c_str());
            if(res !=0 ){
                zlog_error(zct, "data_publish res = %d", res);
                continue;
            }
        }
    }
    zlog_info(zct, "CalculateBattery end");
    return res;
}

int SqliteDB::CalculateDip() {
    char whereCon[1024] = {0};
    char selectSql[1024] = {0};
    char updateSql[1024] = {0};
    memset(whereCon, 0x00, sizeof(whereCon));
    memset(selectSql, 0x00, sizeof(selectSql));
    sprintf(selectSql, " DataNodeNo");
    array_t vecRes;
    char looseValue[10] = {0x00};
    char localtimestamp[32] = {0};
    GetTimeNet(localtimestamp, 1);
    int res = 0;
    readStringValue("config", "loose", looseValue, (char *)GlobalConfig::Config_G.c_str());
    vecRes = GetDataMultiLine(T_SENSOR_INFO(TNAME), " * ", NULL);
    zlog_info(zct, "vecRes111 = %d", vecRes.size());
    for (size_t i = 0; i < vecRes.size(); i++) {
        std::vector<std::string> vParam;
        zlog_info(zct, "vecRes = %s", vecRes[i][42].c_str());
        boost::split(vParam, vecRes[i][42], boost::is_any_of(","), boost::token_compress_on);
        zlog_info(zct, "vParam size = %d", vParam.size());
        if (vParam.size() < 2) {
            sprintf(updateSql, "LooseValue = '%f,0' ", atof(vParam[0].c_str()));
            sprintf(whereCon, "dataNodeNo = '%s' ", vecRes[i][0].c_str());
            res = UpdateTableData(T_SENSOR_INFO(TNAME), updateSql, whereCon);
            if(res !=0 ){
                zlog_error(zct, "res = %d", res);
                break;
            }
        } else {
            char szTablename[32] = {0x00};
            memset(whereCon, 0x00, sizeof(whereCon));
            if (vParam[1] == "2") { //人工干预
                sprintf(whereCon, " timeStamp > '%ld' ", atol(vParam[2].c_str()));
            } else if (vParam[1] == "0") {                                              // 正常状态
                sprintf(whereCon, " timeStamp > '%ld' ", atol(localtimestamp) - 86400); // 一天数据
            } else if (vParam[1] == "1") {                                              // 松动状态
                continue;
            }
            zlog_info(zct, "vParam[0]=%s,vParam[1]=%s", vParam[0].c_str(), vParam[1].c_str());

            sprintf(szTablename, "t_dataStatic_%s", vecRes[i][0].c_str());
            vec_Value vecResDip = GetDataMultiLineOfOneColumnDouble(szTablename, " Dip ", whereCon);
            float sample_variance = Calculation::getSample_variance(vecResDip);
            zlog_info(zct, "sample_variance = %f", sample_variance);
            memset(whereCon, 0x00, sizeof(whereCon));
            memset(updateSql, 0x00, sizeof(updateSql));
            sprintf(whereCon, "dataNodeNo = '%s' ", vecRes[i][0].c_str());
            if (vParam[1] == "0") {
                if (sample_variance > atol(looseValue)) {
                    sprintf(updateSql, "LooseValue = '%f,1' ", sample_variance);
                } else {
                    sprintf(updateSql, "LooseValue = '%f,0' ", sample_variance);
                }
            } else if (vParam[1] == "1") {
            } else if (vParam[1] == "2") {
                zlog_info(zct, "localtimestamp = %ld,vParam[2]=%ld,%ld", atol(localtimestamp), atol(vParam[2].c_str()), atol(localtimestamp) - atol(vParam[2].c_str()));
                if (atol(localtimestamp) - atol(vParam[2].c_str()) > 86400) {
                    sprintf(updateSql, "LooseValue = '%f,0' ", atof(vParam[0].c_str()));
                } else {
                    sprintf(updateSql, "LooseValue = '%f,2,", atof(vParam[0].c_str()));
                    std::string strUpdateSql = std::string(updateSql) + vParam[2] + "' ";
                    memset(updateSql, 0x00, sizeof(updateSql));
                    memcpy(updateSql, strUpdateSql.c_str(), sizeof(updateSql));
                }
                if (sample_variance > atol(looseValue)) {
                    sprintf(updateSql, "LooseValue = '%f,1' ", sample_variance);
                }
            }
        }

        res = UpdateTableData(T_SENSOR_INFO(TNAME), updateSql, whereCon);
        if(res !=0 ){
            zlog_error(zct, "res = %d", res);
            break;
        }
    }
    zlog_info(zct, "CalculateDip");
    return res;
}

int SqliteDB::InintGateway() {
    // 更新网关配置表
    std::string strIP = GetGwIp_("eth0");
    std::string strServerIP = ReadStrByOpt(SERVERCONFIG, "Server", "localServerIpAddress");
    std::string strServerPort = ReadStrByOpt(SERVERCONFIG, "Server", "localServerPort");
    std::string strwebVersion = ReadStrByOpt(SYSTEMINFOFILE, "Version", "WebVersion");
    std::string strsystemVersion = ReadStrByOpt(SYSTEMINFOFILE, "Version", "SystemVersion");
    std::string strGatewayVersion = ReadStrByOpt(SYSTEMINFOFILE, "Version", "GateWayVersion");
    std::string strchan = ReadStrByOpt(ZIGBEECONFIG, "Zigbee", "channel");
    std::string strPanID = ReadStrByOpt(ZIGBEECONFIG, "Zigbee", "PanID");
    int res = 0;
    if (strPanID == "") {
        strPanID = GlobalConfig::MacAddr_G.substr(8);
    }
    if (0 == sqlite_db_ctrl::instance().GetTableRows(T_GATEWAY_INFO(TNAME), NULL)) {
        char strSql[1024] = {0};
        sprintf(strSql, "insert into t_gateway_info(gatewayMAC,zigbeePanID,zigbeeChannel,\
	    		localIP,systemVersion,programVersion,webVersion,serverIP,serverPort,MAC2)\
	    		values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');",
                GlobalConfig::MacAddr_G.c_str(), strPanID.c_str(), strchan.c_str(), strIP.c_str(), strsystemVersion.c_str(), strGatewayVersion.c_str(), strwebVersion.c_str(), strServerIP.c_str(), strServerPort.c_str(), GlobalConfig::MacAddr_G2.c_str());
        res = sqlite_db_ctrl::instance().InsertData(strSql);
        if(res !=0 ){
            zlog_error(zct, "res = %d", res);
        }
        zlog_info(zct, "strSql = %s", strSql);
    } else {
        char whereCon[1024] = {0};
        char updateSql[1024] = {0};
        sprintf(updateSql, "zigbeePanID = '%s',zigbeeChannel = '%s',localIP = '%s',systemVersion='%s',programVersion='%s',webVersion='%s',serverIP='%s',serverPort='%s'", strPanID.c_str(), strchan.c_str(), strIP.c_str(), strsystemVersion.c_str(), strGatewayVersion.c_str(),
                strwebVersion.c_str(), strServerIP.c_str(), strServerPort.c_str());
        sprintf(whereCon, "gatewayMAC='%s'", GlobalConfig::MacAddr_G.c_str());
        res = sqlite_db_ctrl::instance().UpdateTableData(T_GATEWAY_INFO(TNAME), updateSql, whereCon);
        if(res !=0 ){
            zlog_error(zct, "res = %d", res);
        }
    }

    // 上传网关配置到MQTT
    Json::Value jsSystemSetting;
    Json::Value jsBody;
    Json::FastWriter showValue;
    Json::Value jsonVal;
    jsonVal.clear();

    jsonVal["cmd"] = "23";
    jsonVal["dataNodeGatewayNo"] = GlobalConfig::MacAddr_G;
    jsSystemSetting["WebVersion"] = strwebVersion;
    jsSystemSetting["SystemVersion"] = strsystemVersion;
    jsSystemSetting["GateWayVersion"] = strGatewayVersion;
    jsBody["localServerIpAddress"] = ReadStrByOpt(SERVERCONFIG, "Server", "localServerIpAddress");
    jsBody["localServerPort"] = atoi(ReadStrByOpt(SERVERCONFIG, "Server", "localServerPort").c_str());
    jsBody["CommMode"] = atoi(ReadStrByOpt(SERVERCONFIG, "Server", "CommMode").c_str());
    jsBody["Password"] = (ReadStrByOpt(SERVERCONFIG, "Server", "Password"));
    jsBody["UserName"] = (ReadStrByOpt(SERVERCONFIG, "Server", "UserName"));
    std::string dataBody = showValue.write(jsBody);
    jsonVal["cmdBody"] = dataBody;
    res = data_publish(showValue.write(jsonVal).c_str(), GlobalConfig::Topic_G.mPubConfig.c_str());
    if(res !=0 ){
        zlog_warn(zct, "data_publish res = %d", res);
    }
    jsBody.clear();
    jsonVal["cmd"] = "25";
    jsBody["dnsName"] = ReadStrByOpt(NETWORKCONFIG, "Net", "dnsName");
    jsBody["networkPortStatus"] = ReadStrByOpt(NETWORKCONFIG, "Net", "networkPortStatus");
    jsBody["gateway"] = ReadStrByOpt(NETWORKCONFIG, "Net", "gateway");
    jsBody["subnetMask"] = ReadStrByOpt(NETWORKCONFIG, "Net", "subnetMask");
    jsBody["dataWatchIpAddress"] = ReadStrByOpt(NETWORKCONFIG, "Net", "ipAddress");
    jsBody["hostName"] = ReadStrByOpt(NETWORKCONFIG, "Net", "hostName");

    dataBody = showValue.write(jsBody);
    jsonVal["cmdBody"] = dataBody;
    res = data_publish(showValue.write(jsonVal).c_str(), GlobalConfig::Topic_G.mPubConfig.c_str());
    if(res !=0 ){
        zlog_warn(zct, "data_publish res = %d", res);
    }
    return res;
}

std::string SqliteDB::GetNodeConfigureInfor(const char *whereCon) {
    Json::Value jsonVal;
    Json::FastWriter showValue;
    jsonVal.clear();
    jsonVal["cmd"] = "26";
    jsonVal["dataNodeGatewayNo"] = GlobalConfig::MacAddr_G;
    jsonVal["success"] = true;
    jsonVal["message"] = "查询成功";
    Json::Value jsArray;
    array_t arrRes;
    arrRes = sqlite_db_ctrl::instance().GetDataMultiLine(T_SENSOR_INFO(TNAME), "*", whereCon);
    int iResult = arrRes.size();
    if (iResult > 0) {
        for (int j = 0; j < iResult; j++) {
            Json::Value jsSensorData;
            jsSensorData["dataNodeNo"] = arrRes[j][44];
            jsSensorData["dataNodeName"] = arrRes[j][1];
            jsSensorData["initFlag"] = atoi(arrRes[j][2].c_str());
            jsSensorData["accFlag"] = atoi(arrRes[j][3].c_str());
            jsSensorData["zigbeeFlag"] = atoi(arrRes[j][4].c_str());
            jsSensorData["temTopFlag"] = atoi(arrRes[j][5].c_str());
            jsSensorData["temBotFlag"] = atoi(arrRes[j][6].c_str());
            jsSensorData["equipsta"] = atoi(arrRes[j][7].c_str());
            jsSensorData["hardVersion"] = arrRes[j][8];
            jsSensorData["softVersion"] = arrRes[j][9];
            jsSensorData["bpNo"] = arrRes[j][10];
            jsSensorData["serialNo"] = arrRes[j][11];
            jsSensorData["firstPowerTime"] = arrRes[j][12];
            jsSensorData["WakeupTime"] = atoi(arrRes[j][13].c_str());
            jsSensorData["StaticTime"] = atoi(arrRes[j][14].c_str());
            jsSensorData["WaveTime"] = atoi(arrRes[j][15].c_str());
            jsSensorData["BateryV"] = arrRes[j][16];
            jsSensorData["ProductNo"] = arrRes[j][17];
            jsSensorData["configFlag"] = atoi(arrRes[j][18].c_str());
            jsSensorData["startBrands"] = arrRes[j][19];
            jsSensorData["stopBrands"] = arrRes[j][20];
            jsSensorData["featureInterVal"] = (arrRes[j][21]);
            jsSensorData["waveInterVal"] = atoi(arrRes[j][22].c_str());
            jsSensorData["samplingRate"] = atoi(arrRes[j][23].c_str());
            jsSensorData["range"] = atoi(arrRes[j][25].c_str());
            jsSensorData["envelopeBandPass"] = arrRes[j][26];
            jsSensorData["faultFrequency"] = arrRes[j][27];
            jsSensorData["zigbeePanId"] = arrRes[j][28];
            jsSensorData["zigbeeChannel"] = (arrRes[j][29]);
            jsSensorData["zigbeeAddr"] = arrRes[j][30];
            jsSensorData["zigbeeLongAddr"] = arrRes[j][31];
            jsSensorData["zigbeeDesAddr"] = arrRes[j][32];
            jsSensorData["ZigbeePower"] = atoi(arrRes[j][33].c_str());
            jsSensorData["ZigbeeRetry"] = atoi(arrRes[j][34].c_str());
            jsSensorData["ZigbeeRetryGap"] = atoi(arrRes[j][35].c_str());
            jsSensorData["ACCSampleTime"] = atoi(arrRes[j][36].c_str());
            jsSensorData["status"] = atoi(arrRes[j][37].c_str());
            jsSensorData["timeStamp"] = arrRes[j][38];
            jsSensorData["viff"] = atoi(arrRes[j][39].c_str());
            jsSensorData["RSSI"] = arrRes[j][40];
            jsSensorData["Update"] = atoi(arrRes[j][41].c_str());
            jsSensorData["looseValue"] = arrRes[j][42];
            jsSensorData["battery"] = arrRes[j][43];
            jsSensorData["MeasurementID"] = arrRes[j][44];
            jsSensorData["nodeWaveSend"] = arrRes[j][45];
            jsArray.append(jsSensorData);
        }
    } else {
        jsArray.resize(0);
        jsonVal["success"] = false;
        jsonVal["message"] = "fail to query";
    }

    Json::Value jsBody;
    jsBody["dataNodeGatewayNo"] = GlobalConfig::MacAddr_G;
    jsBody["dataNodeArray"] = jsArray;
    std::string dataBody = showValue.write(jsBody);
    jsonVal["cmdBody"] = dataBody;
    return showValue.write(jsonVal);
}

int SqliteDB::QueryofflineData() {
    int res = 0;
    array_t arrRetdataNode = GetDataMultiLine(T_SENSOR_INFO(TNAME), " dataNodeNo,MeasurementID ", NULL);
    for (size_t i = 0; i < arrRetdataNode.size(); i++) {
        char StaticTableName[50] = {0x00};
        sprintf(StaticTableName, "t_dataStatic_%s", arrRetdataNode[i][0].c_str());

        array_t arrRetData = GetDataMultiLine(StaticTableName, "*", "sendMsg = '0' order by timeStamp asc");
        zlog_info(zct, "mqttresend check datanodeNo %s,data count %d", arrRetdataNode[i][0].c_str(), arrRetData.size());
        if (arrRetData.size() < 1) continue;
        for (size_t j = 0; j < arrRetData.size(); j++) {
            char dataTableName[50] = {0x00};
            sprintf(dataTableName, "t_data_%s", arrRetdataNode[i][0].c_str());
            char tmpWhere[128] = {0x00};
            sprintf(tmpWhere, "sendMsg = '0' and timeStamp = '%s'", arrRetData[j][8].c_str());
            array_t arrRet = GetDataMultiLine(dataTableName, "*", "sendMsg = '0'");
            if (arrRet.size() > 0) {
                Json::Value valNodeData;
                Json::Value valNodeFeature;
                for (size_t k = 0; k < arrRet.size(); k++) {
                    valNodeFeature["dataNodeNo"] = arrRetdataNode[i][1].c_str();
                    valNodeFeature["ChannelId"] = arrRet[k][1].c_str();
                    valNodeFeature["diagnosisPk"] = atof(arrRet[k][2].c_str());
                    valNodeFeature["integratPk"] = atof(arrRet[k][3].c_str());
                    valNodeFeature["integratRMS"] = atof(arrRet[k][4].c_str());
                    valNodeFeature["rmsValues"] = atof(arrRet[k][5].c_str());
                    valNodeFeature["envelopEnergy"] = atof(arrRet[k][6].c_str());
                    valNodeFeature["Amp1"] = atof(arrRet[k][7].c_str());
                    valNodeFeature["Amp2"] = atof(arrRet[k][8].c_str());
                    valNodeFeature["Amp3"] = atof(arrRet[k][9].c_str());
                    valNodeFeature["Amp4"] = atof(arrRet[k][10].c_str());
                    valNodeFeature["Amp5"] = atof(arrRet[k][1].c_str());
                    valNodeFeature["Phase1"] = atof(arrRet[k][12].c_str());
                    valNodeFeature["Phase2"] = atof(arrRet[k][13].c_str());
                    valNodeFeature["Phase3"] = atof(arrRet[k][14].c_str());
                    valNodeFeature["Phase4"] = atof(arrRet[k][15].c_str());
                    valNodeFeature["timeStamp"] = atoi(arrRet[k][17].c_str());
                    valNodeData.append(valNodeFeature);
                }
            }
            // 无线传感器信息
            Json::Value root;
            Json::Value valdatastatic;
            Json::Value valNodeData;
            valdatastatic["TemperatureTop"] = atof(arrRetData[j][2].c_str());
            valdatastatic["TemperatureBot"] = atof(arrRetData[j][3].c_str());
            valdatastatic["Dip"] = atof(arrRetData[j][4].c_str());
            valdatastatic["Voltage"] = atof(arrRetData[j][5].c_str());
            valdatastatic["ChannelType"] = "STATUS";
            valdatastatic["ChannelId"] = (arrRetData[j][1].c_str());
            valdatastatic["TimeStamp"] = atoi(arrRetData[j][8].c_str());

            valdatastatic["dataNodeNo"] = arrRetData[j][0].c_str();
            valNodeData.append(valdatastatic);
            valNodeData.append(valNodeData);

            root["data"] = valNodeData;
            root["TimeStamp"] = atoi(arrRetData[j][8].c_str());
            root["dataNodeNo"] = arrRetdataNode[i][1].c_str();
            root["dataNodeGatewayNo"] = GlobalConfig::MacAddr_G;
            root["status"] = "resend";
            Json::FastWriter featureValue;
            std::string strstatisticData = featureValue.write(root);

            int iRet = data_publish(strstatisticData.c_str(), GlobalConfig::Topic_G.mPubData.c_str());
            if (iRet == 0) {
                char updateSql[1024] = {0};
                char whereCon[64] = {0};
                memset(whereCon, 0, 64);
                sprintf(whereCon, "dataNodeNo = '%s' and TimeStamp = '%s'", arrRetData[j][0].c_str(), arrRetData[j][8].c_str());
                memcpy(updateSql, "sendMsg='1'", 11);
                res = sqlite_db_ctrl::instance().UpdateTableData(StaticTableName, updateSql, whereCon);
                if(res !=0 ){
                    zlog_error(zct, "res = %d", res);
                }
                res = sqlite_db_ctrl::instance().UpdateTableData(dataTableName, updateSql, whereCon);
                if(res !=0 ){
                    zlog_error(zct, "res = %d", res);
                }
            }else{
                zlog_error(zct, "data_publish res = %d", res);    
            }
        }
    }
    
    float frTemp = 0.0f;
    std::string sample;
    char whereCon[1024] = {0x00};
    char buf[30]={0};
    sprintf(whereCon, " SendMsg = 0 ");
    array_t arrRetData = GetDataMultiLine("t_data_waveSend", "*", whereCon);
    zlog_info(zct, "mqttresend check wave count %d", arrRetData.size());
    if (arrRetData.size() > 0) {
        for (size_t i = 0; i < arrRetData.size(); i++) {
            std::string strWaveData = "";
            if (access(arrRetData[i][1].c_str(), 0) >= 0) {
                std::vector<float> vecWave;
                char localtimestamp[32] = {0};
                float fTemp = 0;
                std::ifstream inFile(arrRetData[i][1].c_str(), std::ios::in | std::ios::binary);
                inFile.read((char *)localtimestamp, sizeof(localtimestamp));
                while (inFile.read((char *)&fTemp, sizeof(fTemp))) {
                    vecWave.push_back(fTemp);
                }
                memset(whereCon,0,sizeof(whereCon));
                std::string MeasurementID = arrRetData[i][0].erase(arrRetData[i][0].length() - 2);
                sprintf(whereCon," MeasurementID = '%s'",MeasurementID.c_str());
                sample = GetData(T_SENSOR_INFO(TNAME), " samplingRate ", whereCon);
                

                int id =  0;
                memset(resend_mqttData,0,sizeof(resend_mqttData));
                for (size_t i = 0; i < vecWave.size(); i++) {
                    memset(buf, 0x00, sizeof(buf));
                    sprintf(buf, "%.2f", frTemp);
                    if (i != vecWave.size() -1){
                        strncpy(resend_mqttData + id ,buf,strlen(buf));
                        id = id + strlen(buf);
                        strncpy(resend_mqttData + id,",",1);
                        id = id + 1;
                    }else{
                        strncpy(resend_mqttData + id ,buf,strlen(buf));
                    }
                }

                inFile.close();
            }
            Json::Value valWaveData;
            valWaveData["number"] = atoi(sample.c_str());
            valWaveData["channelId"] = arrRetData[i][0];
            valWaveData["dataNodeNo"] = arrRetData[i][0].substr(0, arrRetData[i][0].length() - 2);
            valWaveData["dataNodeGatewayNo"] = GlobalConfig::MacAddr_G;
            valWaveData["SensorEngineeringUnit"] = "";
            valWaveData["timeStamp"] = arrRetData[i][2];
            valWaveData["waveData"] = resend_mqttData;
            Json::FastWriter WaveValue;
            std::string WaveData = WaveValue.write(valWaveData);
            int iRet = data_publish(WaveData.c_str(), GlobalConfig::Topic_G.mPubWaveData.c_str());
            if (iRet == 0) {
                char whereCon[1024] = {0x00};
                char updateSql[1024] = {0x00};
                sprintf(whereCon, "channelID='%s' and timeStamp = '%s' ", arrRetData[i][0].c_str(), arrRetData[i][2].c_str());
                sprintf(updateSql, "SendMsg = 1");
                res = sqlite_db_ctrl::instance().UpdateTableData("t_data_waveSend", updateSql, whereCon);
                if(res !=0 ){
                    zlog_error(zct, "res = %d", res);
                }
                std::string strCmd = "rm " + arrRetData[i][1];
                system(strCmd.c_str());
            }else{
                zlog_error(zct, "data_publish res = %d", res);
            }
        }
    }
    return res;
}
int SqliteDB::ClearExpireData(){
    char whereCon[128] = {0};
    char deleteSql[1024] = {0};
    char selectSql[128] = {0};
    memset(whereCon, 0x00, sizeof(whereCon));
    memset(deleteSql, 0x00, sizeof(deleteSql));
    memset(selectSql, 0x00, sizeof(selectSql));
    sprintf(selectSql, " MeasurementID ");
    array_t vecRes;
    char localtimestamp[32] = {0};
    GetTimeNet(localtimestamp, 1);
    vecRes = GetDataMultiLine(T_SENSOR_INFO(TNAME), selectSql, NULL);
    for (size_t i = 0; i < vecRes.size(); i++) {
        sprintf(whereCon, " timeStamp < %ld",  atol(localtimestamp) - 2592000);//前一个月
        sprintf(deleteSql, "delete from t_dataStatic_%s where %s", vecRes[i][0].c_str(), whereCon);
        ExeSql(deleteSql);
        sprintf(deleteSql, "delete from t_data_%s where %s", vecRes[i][0].c_str(), whereCon);
        ExeSql(deleteSql);
    }
    return 0;
}
void SqliteDB::SaveSystemHardStatus(){
    Json::Value jsData;
    std::string sysStatus = GetSysStatus(jsData);
    char insertSql[1024] = {0};
    float cpu_use = jsData["cpuUserUse"].asDouble();
    float mem_use_rate = jsData["memoryUse"].asDouble();
    int mem_free = jsData["memoryFree"].asInt();
    int mem_total = jsData["memoryTotal"].asInt();
    int mem_use = mem_total - mem_free;
    float disk_free = jsData["hardDiskFree"].asDouble();
    float disk_total = jsData["hardDiskTotal"].asDouble();
    float disk_use_rate = jsData["hardDiskUse"].asDouble();
    int temp = jsData["temperature"].asDouble();
    zlog_info(zct,"cpu_use = %f,mem_use = %d,mem_free = %d,mem_rate = %f,total = %f,free = %f,rate = %f",cpu_use,mem_use,mem_free,mem_use_rate,disk_total,disk_free,disk_use_rate);
    
    std::string updateTime = jsData["updateTime"].asString();
    sprintf(insertSql, " '%f','%d','%d','%f','%f','%f','%f','%d','%s' ",
        cpu_use,
        mem_use,mem_free,mem_use_rate,
        disk_total-disk_free,disk_free,disk_use_rate,
        temp,
        updateTime.c_str());
    sqlite_db_ctrl::instance().InsertData("t_system_info", insertSql);
}
/*  新电池识别
    采集计算平均电压：Vc
    特征值发送瞬时最低电压：Ve
    波形发送瞬时最低电压：Vw
    在传感器上线后，第一次波形电压获取到Vw后进行计算，需要同时满足两个条件：
    1. (Vwp+Vcp+Vep)-(Vwn+Vcn+Ven) > 0.6V
    2. Vwp>Vw_min
    Vwn、Vcn、Ven : 网关最后记录24小时Vw、Vcn、Ven平均值
    Vwp、Vcp、Vep : 当前Vw、Vc、Ve电压值
    Vw_min : 电池型号相关，如ER34615C=2.8V 
*/

void SqliteDB::NewBatteryIdentify(){
    char localtimestamp[32] = { 0 };
    GetTimeNet(localtimestamp, 1);
    float capacity = 0.0, startCapacity = 0.0;
    array_t arrRes;
    arrRes = sqlite_db_ctrl::instance().GetDataMultiLine(T_SENSOR_INFO(TNAME), "*", NULL);
    int count = arrRes.size();
    if (count > 0) {
        for (size_t i = 0; i < count; i++)
        {
            if(atol(localtimestamp) - atol(arrRes[i][38].c_str()) < 90000){ // 每天计算一次，计算前25h内有没有新上线的传感器
                char tablename[100] = {0},whereCon[150] = {0},updateSql[100] = {0};
                sprintf(tablename,"t_dataStatic_%s",arrRes[i][44].c_str());
                sprintf(whereCon, "timeStamp < '%s' ORDER BY timeStamp DESC limit 0,300;", arrRes[i][38].c_str()); // 获取在传感器上线之前的300条电压数据
                array_t voltage_data = sqlite_db_ctrl::instance().GetDataMultiLine(tablename, " voltage,instantaneousBatteryVoltage ", whereCon);
                if (voltage_data.size() < 1) {
                    continue;
                }
                long total_num = 0;
                for (size_t i = 0; i < voltage_data.size(); i++)
                {
                    int temp = atoi(voltage_data[i][0].c_str()) + atoi(voltage_data[i][1].c_str());
                    total_num += temp;
                }
                int mean_voltage = total_num / (voltage_data.size() * 2);
                memset(whereCon,0,sizeof(whereCon));
                sprintf(whereCon, "minmumBatteryVoltageType =  '2' ORDER BY timeStamp DESC limit 0,1;"); 
                vec_t voltage_data_new = sqlite_db_ctrl::instance().GetDataSingleLine(tablename, " voltage,instantaneousBatteryVoltage ", whereCon);
                memset(whereCon,0,sizeof(whereCon));
                sprintf(whereCon, "minmumBatteryVoltageType =  '1' ORDER BY timeStamp DESC limit 0,1;"); 
                std::string voltage = sqlite_db_ctrl::instance().GetData(tablename, " instantaneousBatteryVoltage ", whereCon);
                
                if(voltage_data_new.size() > 0){
                    int voltage_diff =  (atoi(voltage_data_new[0].c_str()) + atoi(voltage_data_new[1].c_str()) + atoi(voltage.c_str())) - mean_voltage;
                    zlog_info(zct,"mean_voltage = %d,voltage = %d,instantaneousBatteryVoltage = %d,instantaneousBatteryVoltage_s = %d diff = %d ",
                        mean_voltage,atoi(voltage.c_str()),atoi(voltage_data_new[0].c_str()),atoi(voltage_data_new[1].c_str()),voltage_diff);
                    if(voltage_diff > 600 && (atoi(voltage_data_new[1].c_str()) > 2800)){
                        memset(whereCon,0,sizeof(whereCon));
                        sprintf(whereCon, "%s= '%s'", T_SENSOR_INFO(DATANODENO), arrRes[i][44].c_str());
                        sqlite_db_ctrl::instance().DeleteTableData(T_BATTERY_INFO(TNAME), whereCon);
                        sqlite_db_ctrl::instance().DeleteTableData(" t_battery_history ", whereCon);
                        memset(whereCon, 0x00, sizeof(whereCon));
                        sprintf(whereCon, "dataNodeNo = '%s' ", arrRes[i][44].c_str());
                        std::string Dip = sqlite_db_ctrl::instance().GetData(T_DATASTATIC_INFO(TNAME), " dip ", whereCon);
                        if (Dip == "") {
                            continue;
                        }
                        capacity = (0.9 + 0.1 * (90 - atoi(Dip.c_str())) / 90) * 19000; // mAh 电池总量
                        startCapacity = capacity;
                        sprintf(updateSql, "batteryPower = '%f,%f' ", startCapacity, startCapacity);
                        memset(whereCon, 0x00, sizeof(whereCon));
                        sprintf(whereCon, "MeasurementID = '%s' ", arrRes[i][44].c_str());
                        UpdateTableData(T_SENSOR_INFO(TNAME), updateSql, whereCon);
                    }
                }
            }
        }
    }
}
void SqliteDB::ShutdownCheck(){
    int shutdownDetection = readIntValue("config", "shutdownDetection", (char *)GlobalConfig::Config_G.c_str());
    if (!shutdownDetection)
    {
        return ;
    }
    
    int checkNumber = readIntValue("config", "shutdownNumber", (char *)GlobalConfig::Config_G.c_str());
    char whereCon[1024] = {0};
    char updateSql[1024] = {0};
    char tablename[100] = {0};
    array_t arrRes = sqlite_db_ctrl::instance().GetDataMultiLine(T_SENSOR_INFO(TNAME), "*", NULL);
    if (arrRes.size() > 0) {
        for (size_t i = 0; i < arrRes.size(); i++){
            memset(updateSql, 0x00, sizeof(updateSql));
            memset(whereCon, 0x00, sizeof(whereCon));
            memset(tablename,0,sizeof(tablename));
            sprintf(tablename,"t_data_%s",arrRes[i][44].c_str());
            sprintf(whereCon, "MeasurementID = '%s' ", arrRes[i][44].c_str());
            vec_t shutdownCheck = sqlite_db_ctrl::instance().GetDataSingleLine("t_shutdown_info", " * ", whereCon);
            if (shutdownCheck.size() < 1)
            {
                continue;
            }
            
            array_t arrData;
            if(shutdownCheck[2] == "1"){ 
                if (shutdownCheck[3] == "0")//加速度有效值
                {
                    memset(whereCon, 0x00, sizeof(whereCon));
                    sprintf(whereCon,"dataNodeNo  = '%s' order by timeStamp desc limit 0,%d;",arrRes[i][44].c_str(),checkNumber);
                    arrData = sqlite_db_ctrl::instance().GetDataMultiLine(tablename, " rmsValues ", whereCon);
                }else if(shutdownCheck[3] == "1"){ //速度有效值
                    memset(whereCon, 0x00, sizeof(whereCon));
                    sprintf(whereCon,"dataNodeNo  = '%s' order by timeStamp desc limit 0,%d;",arrRes[i][44].c_str(),checkNumber);
                    arrData = sqlite_db_ctrl::instance().GetDataMultiLine(tablename, " integratRMS ", whereCon);
                }       
            }else{
                continue;
            }
            int shutdownFlag = 0;
            if (arrData.size() == checkNumber)
            {
                size_t i = 0;
                for (i = 0; i < arrData.size(); i++)
                {
                    if (atof(arrData[i][0].c_str()) > atof(shutdownCheck[4].c_str()))
                    {
                        break;
                    }else{
                        shutdownFlag = 1;
                    }      
                }
                if(i < arrData.size() - 1){
                    continue;
                }
            }
            memset(whereCon, 0x00, sizeof(whereCon));
            if(shutdownFlag == 1){
                sprintf(updateSql,"effect = '1'");
            }else{
                sprintf(updateSql,"effect = '0'");
            }
            sprintf(whereCon, "MeasurementID = '%s' ", arrRes[i][44].c_str());
            sqlite_db_ctrl::instance().UpdateTableData("t_shutdown_info", updateSql, whereCon);
        }
    }
    
}
int SqliteDB::TransBegin() { return sqlite3_exec(mDBAcess, "begin;", 0, 0, 0); }

int SqliteDB::TransRollback() { return sqlite3_exec(mDBAcess, "rollback;", 0, 0, 0); }

int SqliteDB::TransCommit() { return sqlite3_exec(mDBAcess, "commit;", 0, 0, 0); }
