#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_PowerInfo.voltage; fDiffVoltage=fNowVoltage; } if (fNowFreq_PowerInfo.freq; fDiffFreq=fNowFreq; } if (fNowFactor_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; }