SmartPDCode/485/src/sqlite_handle.c

#include "sqlite_handle.h"
#include "elog.h"
#include "time.h"
#include "math.h"
#define	TOTAL_COUNT	60//统计总数

/// @brief 初始化SQLite数据库
/// @param db 数据库接口          
/// @param daba_name 数据库名称
/// @return 
int sqlite_init(sqlite3** db, const char* daba_name)
{
    int ret = 0;
    char *errorMsg = NULL;
    ret = sqlite3_open(daba_name,db);
    if(ret != SQLITE_OK)
        return -1 ;
    char SetData[]="PRAGMA cache_size = 0;";
    ret = sqlite3_exec(*db,SetData,NULL,NULL,&errorMsg);
    return 0;
}


/// @brief 关闭SQLite数据库
/// @param db 数据库接口          
void sqlite_deinit(sqlite3 *db)
{
    int ret = 0;
    ret = sqlite3_close(db);
    if(ret != SQLITE_OK)
        return;
    return;
}

int dev_update_485_consumer(sqlite3 *db,float consumer,GlobalDeviceInfo* _globalDeviceInfo)
{
    int res ;
    char select_sql[256] = {0};
    //// char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    char buff[200] = {0};
    sprintf(buff,"%.2f",consumer);
    sprintf(select_sql,"UPDATE Table_DeviceInfo SET product_consumer=%s WHERE product_id=%d;",buff,_globalDeviceInfo->product_id);
    //printf("consumer=%2f. product_id = %d\n",consumer,_globalDeviceInfo->product_id);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK){
        printf("UPDATE dasdasdasdasdasdasdas FAIELD\n");
        return -1 ;  
    }
        
    sqlite3_free_table(pResult);
}


int dev_set_485_saddr(sqlite3 *db,uint16_t saddr,GlobalDeviceInfo* _globalDeviceInfo)
{
    int res ;
    char select_sql[256] = {0};
    //// char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    sprintf(select_sql,"UPDATE Table_DeviceInfo SET product_modbus_addr=%d WHERE product_id=%d;",saddr,_globalDeviceInfo->product_id);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;  
    sqlite3_free_table(pResult);
    return 0;
}

/// @brief 查询设备信息表
/// @param db 数据库接口          
/// @param _globalDeviceInfo 返回数据接口
/// @return 0:正常
int dev_get_device_info(sqlite3 *db,GlobalDeviceInfo* _globalDeviceInfo)
{
    int res = 0 ;
    int rowIndex = 1 ;
   // // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    const char* select_sql = "SELECT* from Table_DeviceInfo;";
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;   
    //提取数据
    if(nrow==0) 
        return -1 ;

    //获取ID
    _globalDeviceInfo->product_id = atoi(pResult[rowIndex*ncolumn+0]); 
    //获取产品编号
    strcpy(_globalDeviceInfo->product_number,pResult[rowIndex*ncolumn+1]);
    //获取产品名称
    strcpy(_globalDeviceInfo->product_name,pResult[rowIndex*ncolumn+2]);
    //获取产品类型
    _globalDeviceInfo->product_type = atoi(pResult[rowIndex*ncolumn+3]); 
    //获取电源类型
    _globalDeviceInfo->product_pwr_type = atoi(pResult[rowIndex*ncolumn+4]); 
    //获取版本号
    strcpy((char *)(_globalDeviceInfo->product_version),pResult[rowIndex*ncolumn+5]);
    //多国语言
    _globalDeviceInfo->product_language = atoi(pResult[rowIndex*ncolumn+6]); 
    //获取级联设备数
    _globalDeviceInfo->product_count = atoi(pResult[rowIndex*ncolumn+7]); 
    //获取采样间隔时间
    _globalDeviceInfo->product_samp_time = atoi(pResult[rowIndex*ncolumn+8]); 
    //获取采样存储时间
    _globalDeviceInfo->product_save_time = atoi(pResult[rowIndex*ncolumn+9]); 

    //获取modbus主从模式
    _globalDeviceInfo->_modbus_info.product_modbus_type = atoi(pResult[rowIndex*ncolumn+10]); 
    //获取波特率
    _globalDeviceInfo->_modbus_info.product_modbus_baud = atoi(pResult[rowIndex*ncolumn+11]);
    //获取从Modbus地址信息
    _globalDeviceInfo->_modbus_info.product_modbus_addr = atoi(pResult[rowIndex*ncolumn+12]); 
    //获取继电器控制端口号
    strcpy(_globalDeviceInfo->_modbus_info.product_relay_ctrl_port,pResult[rowIndex*ncolumn+13]);
    _globalDeviceInfo->consumer = atof(pResult[rowIndex*ncolumn+14]);
    //_globalDeviceInfo->consumer = 0;
    sqlite3_free_table(pResult);
    return 0;
} 



/// @brief 更新设备信息表
/// @param db 数据库接口      
/// @param _globalDeviceInfo 更新的数据接口
/// @return 
int dev_update_device_genera_info(sqlite3 *db,GlobalDeviceInfo* _globalDeviceInfo)
{
    int res ;
    char select_sql[256] = {0};
    //// char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    sprintf(select_sql,"UPDATE Table_DeviceInfo SET product_number='%s',product_name='%s',product_type=%d,product_pwr_type=%d,product_version='%s',product_language=%d,product_count=%d,product_samp_time=%d,product_save_time=%d WHERE product_id=%d;",
                    _globalDeviceInfo->product_number,
                    _globalDeviceInfo->product_name,
                    _globalDeviceInfo->product_type,
                    _globalDeviceInfo->product_pwr_type,
                    _globalDeviceInfo->product_version,
                    _globalDeviceInfo->product_language,
                    _globalDeviceInfo->product_count,
                    _globalDeviceInfo->product_samp_time,
                    _globalDeviceInfo->product_save_time,
                    _globalDeviceInfo->product_id);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;  
    sqlite3_free_table(pResult);
    return 0;
}

/// @brief 更新modbus相关控制接口
/// @param db 
/// @param _globalDeviceInfo 
/// @return 
int dev_update_device_modus_info(sqlite3 *db,GlobalDeviceInfo* _globalDeviceInfo)
{
    int res ;
    char select_sql[256] = {0};
  //  // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;    

    sprintf(select_sql,"UPDATE Table_DeviceInfo SET product_modbus_type=%d,product_modbus_baud=%d,product_modbus_addr=%d,product_relay_ctrl_port='%s' WHERE product_id=%d;",
        _globalDeviceInfo->_modbus_info.product_modbus_type,
        _globalDeviceInfo->_modbus_info.product_modbus_baud,
        _globalDeviceInfo->_modbus_info.product_modbus_addr,
        _globalDeviceInfo->_modbus_info.product_relay_ctrl_port,
        _globalDeviceInfo->product_id);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;  
    sqlite3_free_table(pResult);
    return 0;
}

/// @brief 获取最大的Index
/// @param db 
int dev_search_last_Index(sqlite3 *db,int* nIndex,char* tableName)
{
    int res ;
    char select_sql[512] = {0};
   // // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;      //用来指向sql执行结果的指针
    char* err_msg=NULL;
    sprintf(select_sql,"SELECT count(*),max(product_Index) FROM %s;",tableName);

    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
    {
        log_e("sqlite handle error.");
        return -1 ;  
    }    
    if(nrow==0)
    {
        log_e("sqlite search no data.");
        return -1 ;
    }

    if (!pResult[nrow * ncolumn])
    {
        *nIndex = 1;
    }
    else
    {
        int nCount = atoi(pResult[nrow * ncolumn]);
        if (nCount > 0)
        {
            *nIndex = atoi(pResult[nrow * ncolumn + 1]);
        }
        *nIndex += 1;
    }
    

    sqlite3_free_table(pResult);
    return 0;
}
/// @brief 删除最早的数据将所有数据平移。
/// @param db 
int dev_change_last_Index(sqlite3 *db,int* nIndex,int nDeleteNumber,char* tableName)
{
    int res ;
    char select_sql[512] = {0};
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;      //用来指向sql执行结果的指针
    char* err_msg=NULL;
    sprintf(select_sql,"delete FROM %s where product_Index<%d;",tableName,nDeleteNumber);

    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
    {
        log_e("sqlite handle error: %s \n%s", err_msg,select_sql);
        return -1 ;  
    }    
    sqlite3_free_table(pResult);

    sprintf(select_sql,"UPDATE %s SET product_Index = product_Index - %d;",tableName,nDeleteNumber);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
    {
        log_e("sqlite handle error: %s \n%s", err_msg,select_sql);
        return -1 ;  
    }    
    *nIndex-=nDeleteNumber;
    sqlite3_free_table(pResult);
    return 0;
}
/// @brief 插入一条电源监控信息
/// @param db 
/// @param _globalPowerChnInfo 
/// @return 
int dev_insert_power_info(sqlite3 *db,GlobalPowerInfo* _globalPowerChnInfo,int* nIndex)
{
    int res ;
    char select_sql[512] = {0};
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    
    sprintf(select_sql,"INSERT INTO Table_PowerInfo VALUES ('%s',%d,%d,%d,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%d);",
                        _globalPowerChnInfo->samp_time,
                        _globalPowerChnInfo->product_id,
                        _globalPowerChnInfo->product_ch_id,
                        _globalPowerChnInfo->product_status,
                        _globalPowerChnInfo->_power_info.voltage,
                        _globalPowerChnInfo->_power_info.current,
                        _globalPowerChnInfo->_power_info.power,
                        _globalPowerChnInfo->_power_info.freq,
                        _globalPowerChnInfo->_power_info.consumption,
                        _globalPowerChnInfo->_power_info.factor,
                        *nIndex);
    (*nIndex)+=1;    
    // if(_globalPowerChnInfo->product_ch_id==0)
    //log_d(select_sql);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
    {
        log_e("sqlite handle error: %s \n%s", err_msg,select_sql);
        return -1 ; 
    }
         
    sqlite3_free_table(pResult);

    return 0;
}

/// @brief 获取采集的最新值
/// @param db 
/// @param product_id 
/// @param product_ch_id 
/// @param _globalPowerInfo 
/// @return 
int dev_search_latest_power_info(sqlite3 *db,int product_id,int product_ch_id,GlobalPowerInfo* _globalPowerInfo)
{
    int res ;
    char select_sql[512] = {0};
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;      //用来指向sql执行结果的指针
    char* err_msg=NULL;

    // SELECT *   
    // FROM Table_PowerInfo   
    // WHERE product_id = 1 AND product_ch_id = 0   
    // ORDER BY product_samp_time DESC   
    // LIMIT 1;

    // sprintf(select_sql,"SELECT * FROM Table_PowerInfo WHERE product_id=%d AND product_ch_id=%d ORDER BY strftime('%%Y-%%m-%%d %%H:%%M:%%S',product_samp_time || '.000') DESC LIMIT 1;",
    //                     product_id,
    //                     product_ch_id);

    sprintf(select_sql,"SELECT * FROM Table_PowerInfo WHERE product_id=%d AND product_ch_id=%d ORDER BY product_samp_time DESC LIMIT 1;",
                        product_id,
                        product_ch_id);

    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
    {
        log_e("sqlite handle error.");
        return -1 ;  
    }    
    if(nrow==0)
    {
        log_e("sqlite search no data.");
        return -1 ;
    }
    _globalPowerInfo->product_id = atoi(pResult[nrow*ncolumn+1]);
    _globalPowerInfo->product_ch_id =  atoi(pResult[nrow*ncolumn+2]);
    _globalPowerInfo->_power_info.status =  atoi(pResult[nrow*ncolumn+3]);
    _globalPowerInfo->_power_info.voltage = atof(pResult[nrow*ncolumn+4]);
    _globalPowerInfo->_power_info.current = atof(pResult[nrow*ncolumn+5]);
    _globalPowerInfo->_power_info.power = atof(pResult[nrow*ncolumn+6]);
    _globalPowerInfo->_power_info.freq = atof(pResult[nrow*ncolumn+7]);
    _globalPowerInfo->_power_info.consumption = atof(pResult[nrow*ncolumn+8]);
    _globalPowerInfo->_power_info.factor = atof(pResult[nrow*ncolumn+9]);

    sqlite3_free_table(pResult);

    return 0;
}

/// @brief 获取所有采集的最新值
/// @param db 
/// @param _globalPowerManger 
/// @return 
#if 0
int dev_search_latest_power_All_info(GlobalDeviceManager* __globalDeviceManger,_OverChnPwrAckInfo* __overChnPwrAckInfo)
{
    GlobalPowerManger* _globalPowerMangerTemp = NULL ;
    char* upgrade_msg = NULL ;
    list_for_each_entry(_globalPowerMangerTemp, &__globalDeviceManger->_globalPowerManger.list, list)
    {
        _OverChnPwrAckInfo *_overChnPwrBackInfoTemp = (_OverChnPwrAckInfo *)malloc(sizeof(_OverChnPwrAckInfo));
        if (_overChnPwrBackInfoTemp == NULL)
        {
            log_e("_overChnPwrBackInfoTemp malloc err.");
            return -1;
        }
        // 填数据
        _overChnPwrBackInfoTemp->product_name = __globalDeviceManage._global_device_info->product_name;
        _overChnPwrBackInfoTemp->product_number = __globalDeviceManage._global_device_info->product_number;
        _overChnPwrBackInfoTemp->product_id = __globalDeviceManage._global_device_info->product_id;
        _overChnPwrBackInfoTemp->productChName = _globalPowerMangerTemp->product_ch_name;
        _overChnPwrBackInfoTemp->productChId = _globalPowerMangerTemp->product_ch_id;
        _overChnPwrBackInfoTemp->status =  _globalPowerMangerTemp->_PowerInfo.status;
        _overChnPwrBackInfoTemp->voltage = _globalPowerMangerTemp->_PowerInfo.voltage;
        _overChnPwrBackInfoTemp->current = _globalPowerMangerTemp->_PowerInfo.current;
        _overChnPwrBackInfoTemp->power = _globalPowerMangerTemp->_PowerInfo.power;
        _overChnPwrBackInfoTemp->freq = _globalPowerMangerTemp->_PowerInfo.freq;
        _overChnPwrBackInfoTemp->consumption = _globalPowerMangerTemp->_PowerInfo.consumption;
        _overChnPwrBackInfoTemp->factor = _globalPowerMangerTemp->_PowerInfo.factor;
        _overChnPwrBackInfoTemp->product_ch_start_delay=_globalPowerMangerTemp->product_ch_start_delay;
        _overChnPwrBackInfoTemp->product_ch_stop_delay=_globalPowerMangerTemp->product_ch_stop_delay;
        list_add_tail(&_overChnPwrBackInfoTemp->list, &__overChnPwrAckInfo->list);
    }
    return 0;
}
#endif
/// @brief 获取所有采集统计的最新值
/// @param db 
/// @param _globalPowerManger 
/// @return 
#if 0
int dev_search_latest_power_statistic_info(GlobalDeviceManager* __globalDeviceManger)
{
    _OverAllPwrAckInfo _over_all_pwr_back_info; //设备电源信息
    GlobalPowerManger* _globalPowerMangerTemp = NULL ;
    char* upgrade_msg = NULL ;
    float fSaveVoltage =0.0f,fSaveFreq =0.0f,fSaveFactor =0.0f;
    float faverageVoltage =0.0f,faverageFreq =0.0f,faverageFactor =0.0f;
    float fDiffVoltage =0.0f,fDiffFreq =0.0f,fDiffFactor =0.0f;
    float fNowVoltage =0.0f,fNowFreq =0.0f,fNowFactor =0.0f;
    memset(&_over_all_pwr_back_info,0,sizeof(_OverAllPwrAckInfo));
    _over_all_pwr_back_info.product_name = __globalDeviceManage._global_device_info->product_name;
    _over_all_pwr_back_info.product_id = __globalDeviceManage._global_device_info->product_id;
    _over_all_pwr_back_info.product_number = __globalDeviceManage._global_device_info->product_number;
    int nTempSum=0;
    list_for_each_entry(_globalPowerMangerTemp, &__globalDeviceManger->_globalPowerManger.list, list)
    {
        faverageVoltage += _globalPowerMangerTemp->_PowerInfo.voltage;
        faverageFreq += _globalPowerMangerTemp->_PowerInfo.freq;
        faverageFactor += _globalPowerMangerTemp->_PowerInfo.factor;
        nTempSum++;
    }
    if (nTempSum>0)
    {
        faverageVoltage /= nTempSum;
        faverageFreq /= nTempSum;
        faverageFactor /= nTempSum;
    }else
    {
        return -1;    
    }
    nTempSum=0;
    list_for_each_entry(_globalPowerMangerTemp, &__globalDeviceManger->_globalPowerManger.list, list)
    {
        if (nTempSum==0)
        {   
            fSaveVoltage=_globalPowerMangerTemp->_PowerInfo.voltage;
            fSaveFreq=_globalPowerMangerTemp->_PowerInfo.freq;
            fSaveFactor=_globalPowerMangerTemp->_PowerInfo.factor;
            fDiffVoltage=abs(faverageVoltage-fSaveVoltage);
            fDiffFreq=abs(faverageFreq-fSaveFreq);
            fDiffFactor=abs(faverageFactor-fSaveFactor);            
        }else
        {   //其他位则计算偏差值
            fNowVoltage=abs(faverageVoltage-_globalPowerMangerTemp->_PowerInfo.voltage);
            fNowFreq=abs(faverageFreq-_globalPowerMangerTemp->_PowerInfo.freq);
            fNowFactor=abs(faverageFactor-_globalPowerMangerTemp->_PowerInfo.factor);  
            if (fNowVoltage<fDiffVoltage)
            {
                fSaveVoltage=_globalPowerMangerTemp->_PowerInfo.voltage;
                fDiffVoltage=fNowVoltage;
            }
            if (fNowFreq<fDiffFreq)
            {
                fSaveFreq=_globalPowerMangerTemp->_PowerInfo.freq;
                fDiffFreq=fNowFreq;
            }
            if (fNowFactor<fDiffFactor)
            {
                fSaveFactor=_globalPowerMangerTemp->_PowerInfo.voltage;
                fDiffFactor=fNowFactor;
            }            
        }        
        // 填数据     
        _over_all_pwr_back_info.current += _globalPowerMangerTemp->_PowerInfo.current ;
        _over_all_pwr_back_info.power +=  _globalPowerMangerTemp->_PowerInfo.power ;
        _over_all_pwr_back_info.consumption += _globalPowerMangerTemp->_PowerInfo.consumption ;
        nTempSum++;
    }
    _over_all_pwr_back_info.voltage = fSaveVoltage ; 
    _over_all_pwr_back_info.freq =  fSaveFreq ;
    _over_all_pwr_back_info.factor = fSaveFactor;      

    upgrade_msg = ws_over_status_ack_to_json(0,&_over_all_pwr_back_info);

    //struct mg_connection *WStemp = WSLinklist;
    //while (WStemp != NULL)
    //{
       // mg_ws_send(WStemp->ws_client, upgrade_msg, strlen(upgrade_msg), WEBSOCKET_OP_TEXT);
      //  WStemp = WStemp->next;
    // }
    if (__globalDeviceManger->ws_client != NULL)
    {
        mg_ws_send(__globalDeviceManger->ws_client, upgrade_msg, strlen(upgrade_msg), WEBSOCKET_OP_TEXT);
    }
    //释放资源,一定要记得释放json字符串
    free(upgrade_msg);
    return 0;
}
#endif

#define DEBUG_TEST 0
/// @brief 获取一段时间内采集的最新值
/// @param db 
/// @param product_id 
/// @param product_ch_id 
/// @param total_type
/// @param _globalPowerInfo 
/// @return 
int dev_search_time_power_info(sqlite3 *db,int product_id,int product_ch_id,int total_type,GlobalPowerInfo* _globalPowerInfo,char* start_time,char* end_time)
{
    int res ;
    char select_sql[512] = {0};
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;      //用来指向sql执行结果的指针
    char* err_msg=NULL;

    // SELECT *   
    // FROM Table_PowerInfo   
    // WHERE product_id = 1 AND product_ch_id = 0   
    // ORDER BY product_samp_time DESC   
    // LIMIT 1;

    // sprintf(select_sql,"SELECT * FROM Table_PowerInfo WHERE product_id=%d AND product_ch_id=%d ORDER BY strftime('%%Y-%%m-%%d %%H:%%M:%%S',product_samp_time || '.000') DESC LIMIT 1;",
    //                     product_id,
    //                     product_ch_id);

    log_d("product_id = %d product_ch_id=%d ",product_id,product_ch_id); 
    //数据库查询 1MIN一个点
  // snprintf(select_sql,sizeof(select_sql),"SELECT * FROM Table_PowerInfo WHERE product_id=%d AND product_ch_id=%d ORDER BY product_Index DESC LIMIT 60;",
     //                   product_id,
     //                   product_ch_id);

    snprintf(select_sql, sizeof(select_sql), "SELECT * FROM Table_PowerInfo WHERE (product_id=%d) AND (product_ch_id=%d) AND (product_samp_time BETWEEN '%s' AND '%s') ORDER BY product_Index DESC LIMIT 60;",
             product_id,
            product_ch_id,
             start_time,
             end_time);

    //300是秒数，即5分钟的秒数为300秒，这个间隔数可以自己定义
    // select id,cameraName,checkPointName,datetime(strftime('%s', countTime)/300*300,'unixepoch') 
    //     as countTime,sum(todayCount) as 'todaycount' 
    // from cardetectinfo where countTime>='2022-04-01 01:01:01' and countTime<='2022-08-31 01:01:01' group by strftime('%s', countTime)/300 order by countTime limit 0,20

    // SELECT TO_CHAR(TRUNC(SYSDATE, 'DD') + (1 / (24 * 60) * ROWNUM),
    //             'YYYY-MM-DD hh24:MI:SS')
    // FROM ALL_OBJECTS
    // WHERE ROWNUM <= 1440 / 1;

    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
    {
        log_e("sqlite handle error.");
        return -1 ;  
    }    
    if(nrow==0)
    {
        log_e("sqlite search no data.");
        return -1 ;
    }

    int nCount=1;
    if (nrow<=TOTAL_COUNT)
    {
        nCount=nrow;
    }
    else nCount=TOTAL_COUNT;
    

    //电压统计
	if(total_type == TOTAL_VOLTAGE_TYPE)
	{
        
		for(int i=0;i < nCount; i++)
		{
			_globalPowerInfo ->_total_voltage.ydata[i] = atof(pResult[(nCount-i)*ncolumn+4]);
            strcpy(_globalPowerInfo ->_total_voltage.xAxis[i],pResult[(nCount-i)*ncolumn+0]);
            #if DEBUG_TEST
			_globalPowerInfo ->_total_voltage.ydata[i] = 220.0+i*0.8;
            sprintf(data_time_str,"2023-01-23:%d",i+4);
            strcpy(&_globalPowerInfo ->_total_voltage.xAxis[i][0],data_time_str);            
			#endif  
		}
	}
    //电流统计
	else if(total_type == TOTAL_CURRENT_TYPE)
	{
	    for(int i=1;i < nCount; i++)
		{
			#if DEBUG_TEST
            _globalPowerInfo ->_total_current.ydata[i] = i+2.7;
            sprintf(data_time_str,"2023-01-23:%d",i+6);
            strcpy(&_globalPowerInfo ->_total_current.xAxis[i][0],data_time_str);    
            #endif
			_globalPowerInfo->_total_current.ydata[i] = atof(pResult[(nCount-i)*ncolumn+5]);
			strcpy(_globalPowerInfo ->_total_current.xAxis[i],pResult[(nCount-i)*ncolumn+0]);
		}
	}
    //功率统计
	else if(total_type == TOTAL_POWER_TYPE)
	{
		for(int i=0;i < nCount; i++)
		{
            #if DEBUG_TEST
            _globalPowerInfo ->_total_power.ydata[i] = i+5.8;
            sprintf(data_time_str,"2023-01-23:%d",i+12);
            strcpy(&_globalPowerInfo ->_total_power.xAxis[i][0],data_time_str);   
            #endif
			_globalPowerInfo->_total_power.ydata[i]	= atof(pResult[(nCount-i)*ncolumn+6]);
			strcpy(_globalPowerInfo ->_total_power.xAxis[i],pResult[(nCount-i)*ncolumn+0]);
		}
	}
    //耗电量统计
	else if(total_type == TOTAL_POWER_CONS_TYPE)
	{
		for(int i=0;i < nCount; i++)
		{
            #if DEBUG_TEST
           _globalPowerInfo ->_total_consumption.ydata[i] = i+7.2;
            sprintf(data_time_str,"2023-01-23:%d",i+10);
            strcpy(&_globalPowerInfo ->_total_consumption.xAxis[i][0],data_time_str);   
            #endif
			_globalPowerInfo->_total_consumption.ydata[i] = atof(pResult[(nCount-i)*ncolumn+8]);
			strcpy(_globalPowerInfo ->_total_consumption.xAxis[i],pResult[(nCount-i)*ncolumn+0]);
		}
	}
	
    sqlite3_free_table(pResult);

    return 0;
}

/// @brief 获取系统总数信息
/// @param db 
/// @param product_id 
/// @param product_ch_id 
/// @param _globalPowerInfo 
/// @return 
int dev_get_system_total_info(sqlite3 *db,int product_id,int product_ch_id, SystemTotalInfo* __SystemTotalInfo,char* start_time,char* end_time)
{
    int res ;
    char select_sql[512] = {0};
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;      //用来指向sql执行结果的指针
    char* err_msg=NULL;

	__SystemTotalInfo->log_total=0;
    __SystemTotalInfo->sensor_total=0;
	__SystemTotalInfo->warn_total=0;

    //查询告警总数
    snprintf(select_sql,sizeof(select_sql),"SELECT count (*) FROM Table_AlarmManage where (alarmType=%d) and (alarmDate BETWEEN '%s' AND '%s');",0,start_time,end_time);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
    {
        log_e("sqlite select Table_AlarmManage handle error.");
        return -1 ;  
    }   
    if(nrow==0)
    {
        log_e("sqlite search Table_AlarmManage no data.");
        return -1 ;
    }
    __SystemTotalInfo->warn_total = atoi(pResult[1]);
    //查询日志总数
    snprintf(select_sql,sizeof(select_sql),"SELECT count (*) FROM Table_AlarmManage where (alarmType=%d) and (alarmDate BETWEEN '%s' AND '%s');",2,start_time,end_time);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
    {
        log_e("sqlite select run_log handle error.");
        return -1 ;  
    }
    if(nrow==0)
    {
        log_e("sqlite search run_log no data.");
        return -1 ;
    }   
    __SystemTotalInfo->log_total = atoi(pResult[1]);
    //查询传感器总数
    snprintf(select_sql,sizeof(select_sql),"SELECT count (*) FROM Table_SensorManage;");
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
    {
        log_e("sqlite select Table_SensorInfo handle error.");
        return -1 ;  
    }
    if(nrow==0)
    {
        log_e("sqlite search Table_SensorInfo no data.");
        return -1 ;
    }   
    __SystemTotalInfo->sensor_total = atoi(pResult[1]);

#if 0 
    __SystemTotalInfo->channel_total =
#endif 

    sqlite3_free_table(pResult);

    return 0;
}


/// @brief 功率趋势图接口(有功功率、无功功率以及视在功率)
/// @param db 
/// @param product_id 
/// @param product_ch_id 
/// @param total_type
/// @param _globalPowerInfo 
/// @return 
int dev_search_power_trend_info(sqlite3 *db,int product_id,int product_ch_id,GlobalPowerInfo* _globalPowerInfo,char* start_time,char* end_time)
{
    int res ;
    char select_sql[512] = {0};
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;      //用来指向sql执行结果的指针
    char* err_msg=NULL;

    // SELECT *   
    // FROM Table_PowerInfo   
    // WHERE product_id = 1 AND product_ch_id = 0   
    // ORDER BY product_samp_time DESC   
    // LIMIT 1;

    // sprintf(select_sql,"SELECT * FROM Table_PowerInfo WHERE product_id=%d AND product_ch_id=%d ORDER BY strftime('%%Y-%%m-%%d %%H:%%M:%%S',product_samp_time || '.000') DESC LIMIT 1;",
    //                     product_id,
    //                     product_ch_id);

    // 数据库查询 1MIN一个点
    snprintf(select_sql, sizeof(select_sql), "SELECT * FROM Table_PowerInfo WHERE (product_id=%d) AND (product_ch_id=%d) AND (product_samp_time BETWEEN '%s' AND '%s') ORDER BY product_Index DESC LIMIT 60;",
             product_id,
             product_ch_id,
             start_time,
             end_time);

    //300是秒数，即5分钟的秒数为300秒，这个间隔数可以自己定义
    // select id,cameraName,checkPointName,datetime(strftime('%s', countTime)/300*300,'unixepoch') 
    //     as countTime,sum(todayCount) as 'todaycount' 
    // from cardetectinfo where countTime>='2022-04-01 01:01:01' and countTime<='2022-08-31 01:01:01' group by strftime('%s', countTime)/300 order by countTime limit 0,20

    // SELECT TO_CHAR(TRUNC(SYSDATE, 'DD') + (1 / (24 * 60) * ROWNUM),
    //             'YYYY-MM-DD hh24:MI:SS')
    // FROM ALL_OBJECTS
    // WHERE ROWNUM <= 1440 / 1;


    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
    {
        log_e("sqlite handle error.");
        return -1 ;  
    }    
    if(nrow==0)
    {
        log_e("sqlite search no data.");
        return -1 ;
    }

    int nCount=1;
    if (nrow<=TOTAL_COUNT)
    {
        nCount=nrow;
    }
    else nCount=TOTAL_COUNT;

   // char data_time_str[32]={0};

    for(int i=0;i < nCount; i++)
    {
         _globalPowerInfo ->_power_trend_info.ydata[POWER_MERITORIOUS_TYPE][i] = atof(pResult[(nCount-i)*ncolumn+6]);
         _globalPowerInfo ->_power_trend_info.ydata[POWER_REACTIVE_TYPE][i] =atof(pResult[(nCount-i)*ncolumn+6])*atof(pResult[(nCount-i)*ncolumn+9]) - atof(pResult[(nCount-i)*ncolumn+6]);
         _globalPowerInfo ->_power_trend_info.ydata[POWER_APPARENT_TYPE][i] = atof(pResult[(nCount-i)*ncolumn+6])*atof(pResult[(nCount-i)*ncolumn+9]);
         strcpy(_globalPowerInfo ->_power_trend_info.xAxis[i],pResult[(nCount-i)*ncolumn+0]);
		#if DEBUG_TEST
        sprintf(data_time_str,"2023-01-23:%d",i+4);
        strcpy(&_globalPowerInfo ->_power_trend_info.xAxis[i][0],data_time_str);

        _globalPowerInfo ->_power_trend_info.ydata[POWER_MERITORIOUS_TYPE][i] = i+1.5;
        _globalPowerInfo ->_power_trend_info.ydata[POWER_REACTIVE_TYPE][i] = i+2;
        _globalPowerInfo ->_power_trend_info.ydata[POWER_APPARENT_TYPE][i] = i+2.5;
		#endif
		
    }

    sqlite3_free_table(pResult);

    return 0;
}


/// @brief 查询电源监控信息表 查询条件 时间与ID号
/// @param db 
/// @param product_id 查询产品ID号
/// @param start_time 查询起始时间
/// @param stop_time  查询结束时间
/// @param size       查询数据个数
/// @param _globalPowerInfo 返回信息
/// @return 
int dev_search_power_info(sqlite3 *db,
                        int product_id,
                        int product_ch_id,
                        char* start_time,
                        char* stop_time,
                        int page,
                        int pageSize,
                        int* size,
                        GlobalPowerInfo* _globalPowerInfo)
{
    //按时间段查询语句
    //SELECT* FROM Table_PowerInfo WHERE product_id=product_id AND (product_samp_time BETWEEN 'start_time' AND 'stop_time');
    int res ;
    char select_sql[512] = {0};
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
 
    sprintf(select_sql,"SELECT* FROM Table_PowerInfo WHERE (product_id=%d AND product_ch_id=%d) AND (product_samp_time BETWEEN '%s' AND '%s') LIMIT %d OFFSET %d;",
                        product_id,
                        product_ch_id,
                        start_time,
                        stop_time,
                        pageSize,
                        page);

    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;  
    if(nrow==0)
        return -1 ;
    //log_d(select_sql);
    //log_d("total_num: %d", nrow-1);
    //初始化头
    // int k = 0 ;
    for (size_t i = 1; i < nrow + 1; i++)
    {
        GlobalPowerInfo* _globalPowerInfoTemp = (GlobalPowerInfo*)malloc(sizeof(GlobalPowerInfo));
        if(_globalPowerInfoTemp==NULL)
                return -1 ;
        strcpy(_globalPowerInfoTemp->samp_time,pResult[i*ncolumn+0]);//i=0 10 
        _globalPowerInfoTemp->product_id = atoi(pResult[i*ncolumn+1]);
        _globalPowerInfoTemp->product_ch_id =  atoi(pResult[i*ncolumn+2]);
        _globalPowerInfoTemp->product_status =  atoi(pResult[i*ncolumn+3]);
        _globalPowerInfoTemp->_power_info.voltage = atof(pResult[i*ncolumn+4]);
        _globalPowerInfoTemp->_power_info.current = atof(pResult[i*ncolumn+5]);
        _globalPowerInfoTemp->_power_info.power = atof(pResult[i*ncolumn+6]);
        _globalPowerInfoTemp->_power_info.freq = atof(pResult[i*ncolumn+7]);
        _globalPowerInfoTemp->_power_info.consumption = atof(pResult[i*ncolumn+8]);
        _globalPowerInfoTemp->_power_info.factor = atof(pResult[i*ncolumn+9]);
        //尾部插入
        list_add_tail(&_globalPowerInfoTemp->list, &_globalPowerInfo->list);
    }
    *size = nrow;
    sqlite3_free_table(pResult);
    return 0;
}


/// @brief 添加通道管理信息
/// @param db 
/// @param product_id 
/// @param product_ch_id 
/// @param _globalPowerChnManger 
/// @return 
int dev_insert_power_manage_info(sqlite3 *db,int product_id,int product_ch_id,GlobalPowerManger* _globalPowerChnManger)
{
    int res ;
    char select_sql[512] = {0};
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    
    sprintf(select_sql,"INSERT INTO Table_PowerManage VALUES (%d,%d,'%s',%d,%d,%d,%d,%d,%.2f,%d,%d,%d,%.2f,%d,%d,%d,%.2f,%d,%d,%d,%.2f,%d,%d,%d,%.2f,%d,%d,%d,%d);",
                    _globalPowerChnManger->product_id,
                    _globalPowerChnManger->product_ch_id,
                    _globalPowerChnManger->product_ch_name,
                    _globalPowerChnManger->product_ch_type,
                    _globalPowerChnManger->product_ch_status,
                    _globalPowerChnManger->product_ch_start_delay,
                    _globalPowerChnManger->product_ch_stop_delay,
                    _globalPowerChnManger->global_over_manager->product_vol_upper_enable,
                    _globalPowerChnManger->global_over_manager->product_vol_upper_threshold,
                    _globalPowerChnManger->global_over_manager->product_vol_over_upper_threshold_ctrl,
                    _globalPowerChnManger->global_over_manager->product_vol_over_upper_threshold_ctrl_para,
                    _globalPowerChnManger->global_over_manager->product_vol_lower_enable,
                    _globalPowerChnManger->global_over_manager->product_vol_lower_threshold,
                    _globalPowerChnManger->global_over_manager->product_vol_over_lower_threshold_ctrl,
                    _globalPowerChnManger->global_over_manager->product_vol_over_lower_threshold_ctrl_para,
                    _globalPowerChnManger->global_over_manager->product_cur_upper_enable,
                    _globalPowerChnManger->global_over_manager->product_cur_upper_threshold,
                    _globalPowerChnManger->global_over_manager->product_cur_over_upper_threshold_ctrl,      
                    _globalPowerChnManger->global_over_manager->product_cur_over_upper_threshold_ctrl_para,  
                    _globalPowerChnManger->global_over_manager->product_pwr_upper_enable,
                    _globalPowerChnManger->global_over_manager->product_pwr_upper_threshold,
                    _globalPowerChnManger->global_over_manager->product_pwr_over_upper_threshold_ctrl, 
                    _globalPowerChnManger->global_over_manager->product_pwr_over_upper_threshold_ctrl_para, 
                    _globalPowerChnManger->global_over_manager->product_pwrcon_upper_enable,
                    _globalPowerChnManger->global_over_manager->product_pwrcon_upper_threshold,
                    _globalPowerChnManger->global_over_manager->product_pwrcon_over_upper_threshold_ctrl,   
                    _globalPowerChnManger->global_over_manager->product_pwrcon_over_upper_threshold_ctrl_para,
                    _globalPowerChnManger->product_saddr,
                    _globalPowerChnManger->product_ch_addr
                    );
    printf("select_sql:%s\n",select_sql);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;  
    sqlite3_free_table(pResult);
    return 0;
}


/// @brief 查询电源通道管理信息
/// @param db 
/// @param product_id 
/// @param _globalPowerManger 
/// @return 
int dev_get_power_manage_info(sqlite3 *db,int product_id,int product_addr,int product_ch_id,GlobalPowerManger* _globalPowerChnManger)
{
    int res = 0 ;
    int rowIndex = 1 ;
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    char select_sql[256] = {0};
    sprintf(select_sql,"SELECT* FROM Table_PowerManage WHERE product_id=%d AND product_ch_addr_id=%d and product_ch_addr_ch_id=%d;",product_id,product_addr,product_ch_id);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -2 ;   
    //提取数据
    if(nrow==0)
        return -1 ; 
    //获取产品ID
    _globalPowerChnManger->product_id = atoi(pResult[rowIndex*ncolumn+0]); 
    //获取产品ID
    _globalPowerChnManger->product_ch_id = atoi(pResult[rowIndex*ncolumn+1]); 
    //获取通道名称
    strcpy(_globalPowerChnManger->product_ch_name,pResult[rowIndex*ncolumn+2]);
    //获取通道类型
    _globalPowerChnManger->product_ch_type = atoi(pResult[rowIndex*ncolumn+3]);
    //获取通道状态
    _globalPowerChnManger->product_ch_status = atoi(pResult[rowIndex*ncolumn+4]);  
    //获取通道延时
    _globalPowerChnManger->product_ch_start_delay = atoi(pResult[rowIndex*ncolumn+5]);     
    //获取通道延时
    _globalPowerChnManger->product_ch_stop_delay = atoi(pResult[rowIndex*ncolumn+6]);       
    if (_globalPowerChnManger->_PowerSXManage == NULL) _globalPowerChnManger->_PowerSXManage = (_PowerSXManage_t*)malloc(sizeof(_PowerSXManage_t));
    if (__globalSxIdx >= pow(2, 32)) __globalSxIdx = 0;
    _globalPowerChnManger->_PowerSXManage->firingSortno = __globalSxIdx++;
    _globalPowerChnManger->_PowerSXManage->closeSortno = __globalSxIdx++;
    //获取电压超上限使能
    _globalPowerChnManger->global_over_manager->product_vol_upper_enable = atoi(pResult[rowIndex*ncolumn+7]); 
    //获取电压上限范围
    _globalPowerChnManger->global_over_manager->product_vol_upper_threshold = atof(pResult[rowIndex*ncolumn+8]);
    //获取电压上限超限动作
    _globalPowerChnManger->global_over_manager->product_vol_over_upper_threshold_ctrl = atoi(pResult[rowIndex*ncolumn+9]);
    //获取电压上限超限关联参数
    _globalPowerChnManger->global_over_manager->product_vol_over_upper_threshold_ctrl_para = atoi(pResult[rowIndex*ncolumn+10]);
    //获取电压超下限使能
    _globalPowerChnManger->global_over_manager->product_vol_lower_enable = atoi(pResult[rowIndex*ncolumn+11]); 
    //获取电压下限范围
    _globalPowerChnManger->global_over_manager->product_vol_lower_threshold = atof(pResult[rowIndex*ncolumn+12]);
    //获取电压下限超限动作
    _globalPowerChnManger->global_over_manager->product_vol_over_lower_threshold_ctrl = atoi(pResult[rowIndex*ncolumn+13]); 
    //超限关联参数
    _globalPowerChnManger->global_over_manager->product_vol_over_lower_threshold_ctrl_para = atoi(pResult[rowIndex*ncolumn+14]);
    //获取电流超上限使能
    _globalPowerChnManger->global_over_manager->product_cur_upper_enable = atoi(pResult[rowIndex*ncolumn+15]); 
    //获取电流上限范围
    _globalPowerChnManger->global_over_manager->product_cur_upper_threshold = atof(pResult[rowIndex*ncolumn+16]);
    //获取电流上限超限动作
    _globalPowerChnManger->global_over_manager->product_cur_over_upper_threshold_ctrl = atoi(pResult[rowIndex*ncolumn+17]); 
    //超限关联参数
    _globalPowerChnManger->global_over_manager->product_cur_over_upper_threshold_ctrl_para = atoi(pResult[rowIndex*ncolumn+18]);
    //获取功耗超上限使能
    _globalPowerChnManger->global_over_manager->product_pwr_upper_enable = atoi(pResult[rowIndex*ncolumn+19]); 
    //获取功耗上限范围
    _globalPowerChnManger->global_over_manager->product_pwr_upper_threshold = atof(pResult[rowIndex*ncolumn+20]);
    //获取功耗上限超限动作
    _globalPowerChnManger->global_over_manager->product_pwr_over_upper_threshold_ctrl = atoi(pResult[rowIndex*ncolumn+21]); 
    //超限关联参数
    _globalPowerChnManger->global_over_manager->product_pwr_over_upper_threshold_ctrl_para = atoi(pResult[rowIndex*ncolumn+22]);
    //获取耗电量超上限使能
    _globalPowerChnManger->global_over_manager->product_pwrcon_upper_enable = atoi(pResult[rowIndex*ncolumn+23]); 
    //获取耗电量上限范围
    _globalPowerChnManger->global_over_manager->product_pwrcon_upper_threshold = atof(pResult[rowIndex*ncolumn+24]);
    //获取耗电量上限超限动作
    _globalPowerChnManger->global_over_manager->product_pwrcon_over_upper_threshold_ctrl = atoi(pResult[rowIndex*ncolumn+25]); 
    //超限关联参数
    _globalPowerChnManger->global_over_manager->product_pwrcon_over_upper_threshold_ctrl_para = atoi(pResult[rowIndex*ncolumn+26]); 
    //获取板ID
    _globalPowerChnManger->product_saddr = atoi(pResult[rowIndex*ncolumn+27]); 
    //获取板TYPE
    _globalPowerChnManger->product_ch_addr = atoi(pResult[rowIndex*ncolumn+28]); 
    sqlite3_free_table(pResult);
    return 0;
}



/// @brief 更新通道基本管理信息
/// @param db 
/// @param product_id 
/// @param _globalPowerManger 
/// @return 
int dev_update_power_manage_genera_info(sqlite3 *db,int product_id,int product_ch_id,GlobalPowerManger* _globalPowerChnManger)
{
    int res = 0 ;
    // int rowIndex = 1 ;
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    char select_sql[1024] = {0};
    sprintf(select_sql,"UPDATE Table_PowerManage SET product_id=%d,product_ch_id=%d,product_ch_name='%s',product_ch_type=%d,product_ch_status=%d,product_ch_start_delay=%d,product_ch_stop_delay=%d WHERE product_id=%d AND product_ch_id=%d;",
        _globalPowerChnManger->product_id,
        _globalPowerChnManger->product_ch_id,
        _globalPowerChnManger->product_ch_name,
        _globalPowerChnManger->product_ch_type,
        _globalPowerChnManger->product_ch_status,
        _globalPowerChnManger->product_ch_start_delay,
        _globalPowerChnManger->product_ch_stop_delay,
        product_id,
        product_ch_id);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
    {
        return -1 ;  
    }
         
 sqlite3_free_table(pResult);
    return 0 ;
}


/// @brief 更新超限信息
/// @param db 
/// @param product_id 
/// @param product_ch_id 
/// @param _globalPowerChnManger 
/// @return 
int dev_update_power_manage_over_info(sqlite3 *db,int product_id,int product_ch_id,GlobalPowerManger* _globalPowerChnManger)
{
    int res = 0 ;
    // int rowIndex = 1 ;
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    char select_sql[1024] = {0};
    sprintf(select_sql,"UPDATE Table_PowerManage SET product_vol_u_enable=%d,product_vol_u_threshold=%0.2f,product_vol_over_u_threshold_ctrl=%d,product_vol_over_u_threshold_para=%d,product_vol_l_enable=%d,product_vol_l_threshold=%0.2f,product_vol_over_l_threshold_ctrl=%d,product_vol_over_l_threshold_para=%d,product_cur_u_enable=%d,product_cur_u_threshold=%0.2f,product_cur_over_u_threshold_ctrl=%d,product_cur_over_u_threshold_para=%d,product_pwr_u_enable=%d,product_pwr_u_threshold=%0.2f,product_pwr_over_u_threshold_ctrl=%d,product_pwr_over_u_threshold_para=%d,product_pwrcon_u_enable=%d,product_pwrcon_u_threshold=%0.2f,product_pwrcon_over_u_threshold_ctrl=%d,product_pwrcon_over_u_threshold_para=%d WHERE product_id=%d AND product_ch_id=%d;",
        _globalPowerChnManger->global_over_manager->product_vol_upper_enable,
        _globalPowerChnManger->global_over_manager->product_vol_upper_threshold,
        _globalPowerChnManger->global_over_manager->product_vol_over_upper_threshold_ctrl,
        _globalPowerChnManger->global_over_manager->product_vol_over_upper_threshold_ctrl_para,
        _globalPowerChnManger->global_over_manager->product_vol_lower_enable,
        _globalPowerChnManger->global_over_manager->product_vol_lower_threshold,
        _globalPowerChnManger->global_over_manager->product_vol_over_lower_threshold_ctrl,
        _globalPowerChnManger->global_over_manager->product_vol_over_lower_threshold_ctrl_para,
        _globalPowerChnManger->global_over_manager->product_cur_upper_enable,
        _globalPowerChnManger->global_over_manager->product_cur_upper_threshold,
        _globalPowerChnManger->global_over_manager->product_cur_over_upper_threshold_ctrl,      
        _globalPowerChnManger->global_over_manager->product_cur_over_upper_threshold_ctrl_para,  
        _globalPowerChnManger->global_over_manager->product_pwr_upper_enable,
        _globalPowerChnManger->global_over_manager->product_pwr_upper_threshold,
        _globalPowerChnManger->global_over_manager->product_pwr_over_upper_threshold_ctrl, 
        _globalPowerChnManger->global_over_manager->product_pwr_over_upper_threshold_ctrl_para, 
        _globalPowerChnManger->global_over_manager->product_pwrcon_upper_enable,
        _globalPowerChnManger->global_over_manager->product_pwrcon_upper_threshold,
        _globalPowerChnManger->global_over_manager->product_pwrcon_over_upper_threshold_ctrl,   
        _globalPowerChnManger->global_over_manager->product_pwrcon_over_upper_threshold_ctrl_para,  
        product_id,
        product_ch_id);
        // log_i(select_sql);
        printf("%s\n",select_sql);
        res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
        if(res != SQLITE_OK)
            return -1 ;   
 sqlite3_free_table(pResult);
    return 0 ;
}


/// @brief 获取传感器管理信息
/// @param db 
/// @param product_id 
/// @param _globalSensorManger 
/// @return 
int dev_get_sensor_manage_info(sqlite3 *db,int product_id,GlobalSensorManger* _globalSensorManger)
{
    int res = 0 ;
    // int rowIndex = 1 ;
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    char select_sql[1024] = {0};
    //GlobalSensorManger* _globalSensorMangerTemp = NULL;
    sprintf(select_sql,"SELECT* FROM Table_SensorManage WHERE product_id=%d;",product_id);
    printf("product_id =%d\n",product_id);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -2 ;   
    //提取数据
    if(nrow==0)
        return -1 ;   

    for (size_t i = 1; i <= nrow; i++)
    {
        GlobalSensorManger* _globalSensorMangerTemp = (GlobalSensorManger*)malloc(sizeof(GlobalSensorManger));
        if(_globalSensorMangerTemp==NULL)
            return -1 ;
        _globalSensorMangerTemp->over_manager = (GlobalSensorOverManage*)malloc(sizeof(GlobalSensorOverManage));
        if(_globalSensorMangerTemp->over_manager==NULL)
            return -1 ;
        _globalSensorMangerTemp->product_id = atoi(pResult[i*ncolumn+0]);
        _globalSensorMangerTemp->sensor_id = atoi(pResult[i*ncolumn+1]);
        _globalSensorMangerTemp->sensor_type = atoi(pResult[i*ncolumn+2]);
        _globalSensorMangerTemp->sensor_interface_type = atoi(pResult[i*ncolumn+3]);

        strcpy(_globalSensorMangerTemp->sensor_name,pResult[i*ncolumn+4]);
        strcpy(_globalSensorMangerTemp->sensor_port_name,pResult[i*ncolumn+5]);
        if( _globalSensorMangerTemp->sensor_interface_type==1)
        {
            _globalSensorMangerTemp->sensor_baud =  atoi(pResult[i*ncolumn+6]);
            _globalSensorMangerTemp->sensor_addr =  atoi(pResult[i*ncolumn+7]);
        }
        else
        {
            _globalSensorMangerTemp->sensor_baud = 0 ;
            _globalSensorMangerTemp->sensor_addr = 0 ;
        }
        _globalSensorMangerTemp->sensor_status = 1;//atoi(pResult[i*ncolumn+8]);
        _globalSensorMangerTemp->sensor_val_count = atoi(pResult[i*ncolumn+9]);
        //记录超限信息
        _globalSensorMangerTemp->over_manager->sensor_val1_upper_enabled = atoi(pResult[i*ncolumn+10]);
        _globalSensorMangerTemp->over_manager->sensor_val1_upper_threshold = atof(pResult[i*ncolumn+11]);
        _globalSensorMangerTemp->over_manager->sensor_val1_upper_threshold_ctrl = atoi(pResult[i*ncolumn+12]);
        _globalSensorMangerTemp->over_manager->sensor_val1_upper_threshold_ctrl_para = atoi(pResult[i*ncolumn+13]);
        _globalSensorMangerTemp->over_manager->sensor_val1_lower_enabled = atoi(pResult[i*ncolumn+14]);
        _globalSensorMangerTemp->over_manager->sensor_val1_lower_threshold = atof(pResult[i*ncolumn+15]);
        _globalSensorMangerTemp->over_manager->sensor_val1_lower_threshold_ctrl = atoi(pResult[i*ncolumn+16]);
        _globalSensorMangerTemp->over_manager->sensor_val1_lower_threshold_ctrl_para = atoi(pResult[i*ncolumn+17]);
        _globalSensorMangerTemp->over_manager->sensor_val2_upper_enabled = atoi(pResult[i*ncolumn+18]);
        _globalSensorMangerTemp->over_manager->sensor_val2_upper_threshold = atof(pResult[i*ncolumn+19]);
        _globalSensorMangerTemp->over_manager->sensor_val2_upper_threshold_ctrl = atoi(pResult[i*ncolumn+20]);
        _globalSensorMangerTemp->over_manager->sensor_val2_upper_threshold_ctrl_para = atoi(pResult[i*ncolumn+21]);
        _globalSensorMangerTemp->over_manager->sensor_val2_lower_enabled = atoi(pResult[i*ncolumn+22]);
        _globalSensorMangerTemp->over_manager->sensor_val2_lower_threshold = atof(pResult[i*ncolumn+23]);
        _globalSensorMangerTemp->over_manager->sensor_val2_lower_threshold_ctrl = atoi(pResult[i*ncolumn+24]);
        _globalSensorMangerTemp->over_manager->sensor_val2_lower_threshold_ctrl_para = atoi(pResult[i*ncolumn+25]);
        //尾部插入
        list_add_tail(&_globalSensorMangerTemp->list, &_globalSensorManger->list);
    }
    sqlite3_free_table(pResult);
    return 0 ;  
}

/// @brief 插入一条传感器值
/// @param db 
/// @param _globalSensorInfo 
/// @return 
int dev_insert_sensor_info(sqlite3 *db,GlobalSensorInfo* _globalSensorInfo,int *nIndex)
{
    int res ;
    char select_sql[256] = {0};
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    
    sprintf(select_sql,"INSERT INTO Table_SensorInfo VALUES (%d,'%s',%d,%d,%.2f,%.2f);",
                        *nIndex,
                        _globalSensorInfo->samp_time,
                        _globalSensorInfo->product_id,
                        _globalSensorInfo->sensor_id,
                        _globalSensorInfo->val1,
                        _globalSensorInfo->val2);
    
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    
    if(res != SQLITE_OK)
    {
        printf("errmsg  = %s %s\n",err_msg,select_sql);
         return -1 ;  
    }   
    sqlite3_free_table(pResult);
    *nIndex+=1;
    return 0;
}

/// @brief 返回传感器的最新值
/// @param db 
/// @param product_id 
/// @param sensor_id 
/// @param sensor_type 
/// @param _globalSensorInfo 
/// @return 
int dev_serach_latest_sensor_info(sqlite3 *db,int product_id,int sensor_id,GlobalSensorInfo* _globalSensorInfo)
{
    int res ;
    char select_sql[256] = {0};
    // char *errorMsg = NULL;
    int nrow = 1;               //满足条件的记录数目
    int ncolumn = 1;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;

    sprintf(select_sql,"SELECT * FROM Table_SensorInfo WHERE product_id=%d AND sensor_id=%d ORDER BY sensor_samp_time DESC LIMIT 1;",
                        product_id,
                        sensor_id);

    //log_d(select_sql);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
    {
        log_e("sqlite handle error.");
        return -1 ;  
    }    
    if(nrow==0)
    {
        log_e("sqlite search no data.");
        return -1 ;
    }
    printf("nrow  = %d   ncolumen = %d\n",nrow,ncolumn);
    strcpy(_globalSensorInfo->samp_time,pResult[nrow*ncolumn+1]);
    _globalSensorInfo->product_id = atoi(pResult[nrow*ncolumn+2]);
    _globalSensorInfo->sensor_id = atoi(pResult[nrow*ncolumn+3]);
    _globalSensorInfo->val1 = atof(pResult[nrow*ncolumn+4]);
    _globalSensorInfo->val2 = atof(pResult[nrow*ncolumn+5]);
    sqlite3_free_table(pResult);
    return 0;
}


/// @brief 查询传感器信息
/// @param db 
/// @param product_id 
/// @param sensor_id 
/// @param start_time 
/// @param stop_time 
/// @param size 
/// @param _globalSensorInfo 
/// @return 
int dev_search_sensor_info(sqlite3 *db,
                        int product_id,
                        int sensor_id,
                        char* start_time,
                        char* stop_time,
                        int pageNum,
                        int pageSize,
                        int* size,
                        GlobalSensorInfo* _globalSensorInfo)
{
    //按时间段查询语句
    //SELECT* FROM Table_PowerInfo WHERE product_id=product_id AND (product_samp_time BETWEEN 'start_time' AND 'stop_time');

    int res ;
    char select_sql[256] = {0};
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
 
    sprintf(select_sql,"SELECT* FROM Table_SensorInfo WHERE (product_id=%d) AND (sensor_id=%d) AND (sensor_samp_time BETWEEN '%s' AND '%s') LIMIT %d OFFSET %d;",
                        product_id,
                        sensor_id,
                        start_time,
                        stop_time,
                        pageSize,
                        pageNum);
    log_d(select_sql);

    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;
    if(nrow==0)
        return -1 ;
    
    //初始化头
    //GlobalSensorInfo* _temp = _globalSensorInfo;
    for (size_t i = 1; i <= nrow; i++)
    {
        GlobalSensorInfo* _globalSensorInfoTemp = (GlobalSensorInfo*)malloc(sizeof(GlobalSensorInfo));
        if(_globalSensorInfoTemp==NULL)
            return -1 ;
        strcpy(_globalSensorInfoTemp->samp_time,pResult[i*ncolumn+2]);
        _globalSensorInfoTemp->product_id = atoi(pResult[i*ncolumn+3]);
        _globalSensorInfoTemp->sensor_id = atoi(pResult[i*ncolumn+4]);
        if(pResult[i*ncolumn+5]!=NULL)
            _globalSensorInfoTemp->val1 = atof(pResult[i*ncolumn+5]);
        if(pResult[i*ncolumn+6]!=NULL)    
        _globalSensorInfoTemp->val2 = atof(pResult[i*ncolumn+6]);
        //尾部插入
        list_add_tail(&_globalSensorInfoTemp->list, &_globalSensorInfo->list);
    }
    sqlite3_free_table(pResult);
    return 0;
}

/// @brief 删除一条传感器信息
/// @param db 
/// @param product_id 
/// @param sensor_id 
/// @return 
int dev_delete_sensor_manage(sqlite3 *db,int product_id,int sensor_id)
{
    int res ;
    char select_sql[256] = {0};
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;   

    sprintf(select_sql,"DELETE FROM Table_SensorManage WHERE product_id=%d AND sensor_id=%d;",
                product_id,
                sensor_id
    );
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;  
    sqlite3_free_table(pResult);
    return 0;
}




/// @brief 插入一条传感器管理信息
/// @param db 
/// @param _globalSensorManger 
/// @return 
int dev_insert_sensor_genera_manage(sqlite3 *db,GlobalSensorManger* _globalSensorManger)
{
    int res ;
    char select_sql[1024] = {0};
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;



    sprintf(select_sql,"INSERT INTO Table_SensorManage (product_id,sensor_type,sensor_interface_type,sensor_name,sensor_port_name,sensor_baud,sensor_addr,sensor_status,sensor_val_count,sensor_val1_u_enable,sensor_val1_u_threshold,sensor_val1_over_u_threshold_ctrl,sensor_val1_over_u_threshold_ctrl_para,sensor_val1_l_enable,sensor_val1_l_threshold,sensor_val1_over_l_threshold_ctrl,sensor_val1_over_l_threshold_ctrl_para,sensor_val2_u_enable,sensor_val2_u_threshold,sensor_val2_over_u_threshold_ctrl,sensor_val2_over_u_threshold_ctrl_para,sensor_val2_l_enable,sensor_val2_l_threshold,sensor_val2_over_l_threshold_ctrl,sensor_val2_over_l_threshold_ctrl_para) VALUES (%d,%d,%d,'%s','%s',%d,%d, %d,%d,%d,%0.2f,%d,%d,%d,%0.2f,%d,%d,%d,%0.2f,%d,%d,%d,%0.2f,%d,%d);",
                _globalSensorManger->product_id,
                // _globalSensorManger->sensor_id,
                _globalSensorManger->sensor_type,
                _globalSensorManger->sensor_interface_type,

                _globalSensorManger->sensor_name,
                _globalSensorManger->sensor_port_name,
                _globalSensorManger->sensor_baud,

                _globalSensorManger->sensor_addr,
                _globalSensorManger->sensor_status,
                _globalSensorManger->sensor_val_count,
                0,
                0.0,
                0,
                0,
                0,
                0.0,
                0,
                0,
                0,
                0.0,
                0,
                0,
                0,
                0.0,
                0,
                0);

    log_d(select_sql);

    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;  
    sqlite3_free_table(pResult);
    return 0;
}

/// @brief 更新传感器基本信息
/// @param db 
/// @param product_id 
/// @param sensor_id 
/// @param _globalSensorManger 
/// @return 
int dev_update_sensor_genera_manage(sqlite3 *db,int product_id,int sensor_id,GlobalSensorManger* _globalSensorManger)
{
    int res = 0 ;
    // int rowIndex = 1 ;
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    char select_sql[1024] = {0};
    sprintf(select_sql,"UPDATE Table_SensorManage SET product_id=%d,sensor_id=%d,sensor_type=%d,sensor_interface_type=%d,sensor_name='%s',sensor_port_name='%s',sensor_baud=%d,sensor_addr=%d,sensor_status=%d,sensor_val_count=%d WHERE product_id=%d AND sensor_id=%d;",
            _globalSensorManger->product_id,
            _globalSensorManger->sensor_id,
            _globalSensorManger->sensor_type,
            _globalSensorManger->sensor_interface_type,
            _globalSensorManger->sensor_name,
            _globalSensorManger->sensor_port_name,
            _globalSensorManger->sensor_baud,
            _globalSensorManger->sensor_addr,
            _globalSensorManger->sensor_status,
            _globalSensorManger->sensor_val_count,
            product_id,
            sensor_id);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ; 
    sqlite3_free_table(pResult);  
    return 0 ;
}


/// @brief 更新一条传感器超限信息
/// @param db 
/// @param product_id 
/// @param sensor_id 
/// @param _globalSensorManger 
/// @return 
int dev_update_sensor_over_manage(sqlite3 *db,int product_id,int sensor_id,GlobalSensorManger* _globalSensorManger)
{
    int res = 0 ;
    // int rowIndex = 1 ;
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    char select_sql[1024] = {0};
    sprintf(select_sql,"UPDATE Table_SensorManage SET sensor_val1_u_enabled=%d,sensor_val1_u_threshold=%0.2f,sensor_val1_u_threshold_ctrl=%d,sensor_val1_u_threshold_ctrl_para=%d,sensor_val2_u_enabled=%d,sensor_val2_u_threshold=%0.2f,sensor_val2_u_threshold_ctrl=%d,sensor_val2_u_threshold_ctrl_para=%d WHERE product_id=%d AND product_ch_id=%d;",
        _globalSensorManger->over_manager->sensor_val1_upper_enabled,
        _globalSensorManger->over_manager->sensor_val1_upper_threshold,
        _globalSensorManger->over_manager->sensor_val1_upper_threshold_ctrl,
        _globalSensorManger->over_manager->sensor_val1_upper_threshold_ctrl_para,
        _globalSensorManger->over_manager->sensor_val2_upper_enabled,
        _globalSensorManger->over_manager->sensor_val2_upper_threshold,
        _globalSensorManger->over_manager->sensor_val2_upper_threshold_ctrl,
        _globalSensorManger->over_manager->sensor_val2_upper_threshold_ctrl_para, 
        product_id,
        sensor_id);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;   
    sqlite3_free_table(pResult);
    return 0 ;
}

/// @brief 插入一条告警信息
/// @param db 
/// @param product_id 
/// @param alarm_type 
/// @param alarm_content 
/// @return 
int dev_insert_alarm_info(sqlite3* db,int product_id,int alarm_type,char* date_time,char* alarm_content)
{
    int res = 0 ;
    // int rowIndex = 1 ;
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    char select_sql[1024] = {0};

    sprintf(select_sql,"INSERT INTO Table_AlarmManage (alarmDate,alarmType,product_id,alarmContent) VALUES ('%s',%d,%d,'%s');",
        date_time,
        alarm_type,
        product_id,
        alarm_content);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;   
    sqlite3_free_table(pResult);
    return 0;
}

/// @brief 查询电源 传感器以及网络告警信息
/// @param db 
/// @param product_id 
/// @param alarm_type 
/// @param _alarm_ack_info 
/// @return 
#if 0
int dev_serarch_all_alarm(sqlite3* db,
                        int product_id,
                        int alarm_type,
                        int page_num,
                        int page_size,
                        _AlarmAckInfo* _alarm_ack_info,int* nAlarmSum)
{
    int res = 0 ;
    // int rowIndex = 1 ;
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    char select_sql[1024] = {0};

    //查询告警总数
    if (alarm_type<0)//小于0加载所有的
    {
        snprintf(select_sql,sizeof(select_sql),"SELECT count (*) FROM Table_AlarmManage);");
    }else{
        snprintf(select_sql,sizeof(select_sql),"SELECT count (*) FROM Table_AlarmManage where (alarmType=%d) ;",alarm_type);
    }
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
    {
        log_e("sqlite select Table_AlarmManage handle error.");
        return -1 ;  
    }   
    if(nrow==0)
    {
        log_e("sqlite search Table_AlarmManage no data.");
        return -1 ;
    }
    *nAlarmSum = atoi(pResult[1]);
    sqlite3_free_table(pResult);

    if (alarm_type<0)//小于0加载所有的
    {   
        sprintf(select_sql,"SELECT* FROM Table_AlarmManage WHERE (product_id=%d) order by alarmType desc LIMIT %d OFFSET %d ",
                        product_id,
                        page_size,
                        page_num>0?(page_num-1)*page_size:0);
    }else
    {
        sprintf(select_sql,"SELECT* FROM Table_AlarmManage WHERE (product_id=%d) AND (alarmType=%d) LIMIT %d OFFSET %d",
                        product_id,
                        alarm_type,
                        page_size,
                        page_num>0?(page_num-1)*page_size:0);
    }  
    //log_d(select_sql);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;  
    if(nrow==0)
        return -1 ;
    //初始化头
    for (size_t i = 1; i <= nrow; i++)
    {
        _AlarmAckInfo* _alarmTemp = (_AlarmAckInfo*)malloc(sizeof( _AlarmAckInfo));
        if(_alarmTemp==NULL)
            log_e("alarm malloc failed");
        strcpy(_alarmTemp->alarmNumber,pResult[i*ncolumn+0]);
        strcpy(_alarmTemp->alarmDate,pResult[i*ncolumn+1]);
        _alarmTemp->alarmType = atoi(pResult[i*ncolumn+2]);
        strcpy(_alarmTemp->deviceNumber,__globalDeviceManage._global_device_info->product_number);
        strcpy(_alarmTemp->deviceName,__globalDeviceManage._global_device_info->product_name);
        strcpy(_alarmTemp->alarmContent,pResult[i*ncolumn+4]);
        //尾部插入
        list_add_tail(&_alarmTemp->list, &_alarm_ack_info->list);
    }
    sqlite3_free_table(pResult);
}

#endif

/// @brief 查询告警历史记录
/// @param db 
/// @param product_id 
/// @param alarm_type 
/// @param start_time 
/// @param end_time 
/// @param page_num 
/// @param page_size 
/// @param _alarm_ack_info 
/// @return 
#if 0
int dev_search_alarm_info(sqlite3* db,
                        int product_id,
                        int alarm_type,
                        char* start_time,
                        char* end_time,
                        int page_num,
                        int page_size,
                        _AlarmAckInfo* _alarm_ack_info,
                        int *nAlarmSum
                        )
{
    int res = 0 ;
    // int rowIndex = 1 ;
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    char select_sql[1024] = {0};
    
    //查询告警总数
    if (alarm_type<0)//小于0加载所有的
    {
        snprintf(select_sql,sizeof(select_sql),"SELECT count (*) FROM Table_AlarmManage where (alarmDate BETWEEN '%s' AND '%s');",start_time,end_time);
    }else{
        snprintf(select_sql,sizeof(select_sql),"SELECT count (*) FROM Table_AlarmManage where (alarmType=%d) and (alarmDate BETWEEN '%s' AND '%s');",alarm_type,start_time,end_time);
    }
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
    {
        log_e("sqlite select Table_AlarmManage handle error.");
        return -1 ;  
    }   
    if(nrow==0)
    {
        log_e("sqlite search Table_AlarmManage no data.");
        return -1 ;
    }
    *nAlarmSum = atoi(pResult[1]);

    sprintf(select_sql,"SELECT* FROM Table_AlarmManage WHERE (product_id=%d) AND (alarmType=%d) AND (alarmDate BETWEEN '%s' AND '%s') LIMIT %d OFFSET %d;",
                        product_id,
                        alarm_type,
                        start_time,
                        end_time,
                        page_size,
                        page_num>0?(page_num-1)*page_size:0);
    log_d(select_sql);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;  
    if(nrow==0)
        return -1 ;
    //初始化头
    for (size_t i = 1; i <= nrow; i++)
    {
        _AlarmAckInfo* _alarmTemp = (_AlarmAckInfo*)malloc(sizeof( _AlarmAckInfo));
        if(_alarmTemp==NULL)
            log_e("alarm malloc failed");
        strcpy(_alarmTemp->alarmNumber,pResult[i*ncolumn+0]);
        strcpy(_alarmTemp->alarmDate,pResult[i*ncolumn+1]);
        _alarmTemp->alarmType = atoi(pResult[i*ncolumn+2]);
        strcpy(_alarmTemp->deviceNumber,__globalDeviceManage._global_device_info->product_number);
        strcpy(_alarmTemp->deviceName,__globalDeviceManage._global_device_info->product_name);
        strcpy(_alarmTemp->alarmContent,pResult[i*ncolumn+4]);
        //尾部插入
        list_add_tail(&_alarmTemp->list, &_alarm_ack_info->list);
    }
    sqlite3_free_table(pResult);
    return 0;
}
#endif
#if 0
int dev_get_ntp_info(sqlite3 *db,NTPManager* _globalNTPInfo)
{
    int res = 0 ;
    // int rowIndex = 1 ;
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    char select_sql[1024] = {0};

    sprintf(select_sql, "SELECT* from Table_NTPManage WHERE product_id = %d;", __globalDeviceManage._global_device_info->product_id);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;   
    //提取数据
    if(nrow==0)
        return -2 ;
    //获取ID
    _globalNTPInfo->product_id = atoi(pResult[rowIndex*ncolumn+0]); 
    _globalNTPInfo->mode = atoi(pResult[rowIndex*ncolumn+1]); 
   _globalNTPInfo->ntpName= malloc(64);
    strcpy(_globalNTPInfo->ntpName,pResult[rowIndex*ncolumn+2]);
    _globalNTPInfo->ntpaddress= malloc(64);
    strcpy(_globalNTPInfo->ntpaddress ,pResult[rowIndex*ncolumn+3]);
    _globalNTPInfo->port = atoi(pResult[rowIndex*ncolumn+4]); 
     _globalNTPInfo->time= malloc(64);
    strcpy(_globalNTPInfo->time,pResult[rowIndex*ncolumn+5]);
    _globalNTPInfo->weekday = atoi(pResult[rowIndex*ncolumn+6]); 
    _globalNTPInfo->tzone = atoi(pResult[rowIndex*ncolumn+7]); 

    sqlite3_free_table(pResult);
    return 0;
} 
#endif
#if 0

int dev_update_ntp_info(sqlite3 *db, int product_id, NTPManager* _globalNTPInfo)                     
{
    int res = 0 ;
    // int rowIndex = 1 ;
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    char select_sql[1024] = {0};
    sprintf(select_sql,"UPDATE Table_NTPManage SET enable=%d,name='%s',address='%s',port=%d,time='%s',weekday=%d,timezone=%d WHERE product_id=%d ;",
        _globalNTPInfo->mode,
        _globalNTPInfo->ntpName,
        _globalNTPInfo->ntpaddress,
        _globalNTPInfo->port,
        _globalNTPInfo->time,
        _globalNTPInfo->weekday,
        _globalNTPInfo->tzone,
        product_id);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;   
    sqlite3_free_table(pResult);

    return 0;
}
#endif
#if 0
int dev_insert_ntp_info(sqlite3 *db,NTPManager* _globalNTPInfo)
{
    int res = 0 ;
    // int rowIndex = 1 ;
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    char select_sql[1024] = {0};

    sprintf(select_sql,"INSERT INTO Table_NTPManage (product_id, enable, name, address, port, time, weekday, timezone) VALUES (%d,%d,'%s','%s', %d,'%s',%d,%d);",
        _globalNTPInfo->product_id,
        _globalNTPInfo->mode,
        _globalNTPInfo->ntpName,
        _globalNTPInfo->ntpaddress,
        _globalNTPInfo->port,
        _globalNTPInfo->time,
        _globalNTPInfo->weekday,
        _globalNTPInfo->tzone);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;   
    sqlite3_free_table(pResult);
    return 0;
}

#endif
#if 0
int dev_get_snmp_info(sqlite3 *db, SNMPManager* _globalSNMPInfo)
{
    int res = 0 ;
    // int rowIndex = 1 ;
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    char select_sql[1024] = {0};

    sprintf(select_sql, "SELECT* from Table_SNMPManage WHERE product_id = %d;", __globalDeviceManage._global_device_info->product_id);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;   
    //提取数据
    if(nrow==0)
        return -2 ;
    //获取ID
    _globalSNMPInfo->product_id = atoi(pResult[rowIndex*ncolumn+0]); 
    _globalSNMPInfo->version= atoi(pResult[rowIndex*ncolumn+1]);
    _globalSNMPInfo->mode = atoi(pResult[rowIndex*ncolumn+2]); 
    _globalSNMPInfo->public= malloc(64);
    strcpy(_globalSNMPInfo->public,pResult[rowIndex*ncolumn+3]);
    _globalSNMPInfo->trapmode = atoi(pResult[rowIndex*ncolumn+4]); 

    sqlite3_free_table(pResult);
    return 0;
}

#endif
#if 0
int dev_insert_snmp_info(sqlite3 *db, SNMPManager* _globalSNMPInfo)
{
    int res = 0 ;
    // int rowIndex = 1 ;
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    char select_sql[1024] = {0};

    sprintf(select_sql,"INSERT INTO Table_SNMPManage (product_id, proto_type, enable, public, TrapEnable) VALUES (%d,%d,%d,'%s',%d);",
        _globalSNMPInfo->product_id,
        _globalSNMPInfo->version,
        _globalSNMPInfo->mode,
        _globalSNMPInfo->public,
        _globalSNMPInfo->trapmode);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;   
    sqlite3_free_table(pResult);
    return 0;
}
#endif

#if 0
int dev_update_snmp_info(sqlite3 *db, int product_id, SNMPManager* _globalSNMPInfo)
{
    int res = 0 ;
    // int rowIndex = 1 ;
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    char select_sql[1024] = {0};
    sprintf(select_sql,"UPDATE Table_SNMPManage SET proto_type='%d',enable=%d,public='%s',TrapEnable=%d WHERE product_id=%d;",
        _globalSNMPInfo->version,
        _globalSNMPInfo->mode,
        _globalSNMPInfo->public,
        _globalSNMPInfo->trapmode,
        product_id);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;   
    sqlite3_free_table(pResult);

    return 0;
}
#endif
#if 0
int dev_get_smtp_info(sqlite3 *db, SMTPManager* _globalSMTPInfo)
{
    int res = 0 ;
    // int rowIndex = 1 ;
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    char select_sql[1024] = {0};

    sprintf(select_sql, "SELECT* from Table_SMTPManage WHERE product_id = %d;", __globalDeviceManage._global_device_info->product_id);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;   
    //提取数据
    if(nrow==0)
        return -2 ;
    //获取ID
    _globalSMTPInfo->product_id = atoi(pResult[rowIndex*ncolumn+0]);
    _globalSMTPInfo->mode = atoi(pResult[rowIndex*ncolumn+1]);
    strcpy(_globalSMTPInfo->account,pResult[rowIndex*ncolumn+2]);
    strcpy(_globalSMTPInfo->password,pResult[rowIndex*ncolumn+3]);
    strcpy(_globalSMTPInfo->server,pResult[rowIndex*ncolumn+4]);
    _globalSMTPInfo->port = atoi(pResult[rowIndex*ncolumn+5]); 
    strcpy(_globalSMTPInfo->auth,pResult[rowIndex*ncolumn+6]);

    sqlite3_free_table(pResult);
    return 0;
}
#endif
#if 0
int dev_get_smtp_recv_user_list(sqlite3 *db, SMTPreceiveAccount* _globalSMTPreceiveAccount)
{
    int res = 0 ;
    // int rowIndex = 1 ;
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    char select_sql[1024] = {0};

    sprintf(select_sql, "SELECT* from Table_SMTPRecAccount;");
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;   
    //提取数据
    if(nrow==0)
        return -2 ;
    for(int i = 1; i <= nrow; i++)
    {
        SMTPreceiveAccount* iterator = (SMTPreceiveAccount*)malloc(sizeof(SMTPreceiveAccount));
        iterator->idx = atoi(pResult[i*ncolumn+0]);
        iterator->receive_account = malloc(128);
        strcpy(iterator->receive_account,pResult[i*ncolumn+1]);
        iterator->receive_account_info = malloc(128);
        strcpy(iterator->receive_account_info,pResult[i*ncolumn+2]);
        list_add_tail(&iterator->list, &_globalSMTPreceiveAccount->list);
    }

    sqlite3_free_table(pResult);
    return 0;
}
#endif


/// @brief 插入一条报警信息
/// @param db 
/// @param product_id 
/// @param alarm_content 
/// @return 
int dev_insert_alarm_ctrl(sqlite3* db,int product_id,int nAlarmType,char* alarm_content)
{
    struct tm* t ;
    time_t now;
    time(&now);
    t = localtime(&now);

    char buffer[80];
    strftime(buffer, sizeof(buffer), "%Y-%m-%d %H:%M:%S", t);    
    dev_insert_alarm_info(db,product_id,nAlarmType,buffer,alarm_content);
    return 0;
}
/// @brief 删除分组信息
int dev_delete__group_info(sqlite3 *db,int product_id)
{
    int res ;
    char select_sql[256] = {0};
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;   

    sprintf(select_sql,"DELETE FROM Table_GroupManage WHERE product_id=%d;",
                product_id
    );
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;  
    sqlite3_free_table(pResult);
    return 0;
}

int dev_insert_group_info(sqlite3 *db,GroupInfo* _globalgroupInfo)
{
    int res = 0 ;
    // int rowIndex = 1 ;
    // char *errorMsg = NULL;
    int nrow = 0;               //满足条件的记录数目
    int ncolumn = 0;            //每条记录包含的字段数据
    char** pResult = NULL;  //用来指向sql执行结果的指针
    char* err_msg=NULL;
    char select_sql[1024] = {0};

    sprintf(select_sql,"INSERT INTO Table_GroupManage (product_id, groupID, groupName, groupChList) VALUES (%d,%d,'%s','%s');",
        _globalgroupInfo->product_id,
        _globalgroupInfo->GroupID,
        _globalgroupInfo->GroupName,
        _globalgroupInfo->GroupChList);
    res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg);
    if(res != SQLITE_OK)
        return -1 ;   
    sqlite3_free_table(pResult);
    return 0;
}