#include #include "sqlite_handle.h" #include "websocket_handle.h" #include "elog.h" #include "time.h" #include "paras.h" #include "cfg.h" #include "smtp.h" #include "mqtt.h" #include "sys.h" #include "switch_ctrl.h" #define TOTAL_COUNT 60//统计总数 enum{ DB_MIN=0, DB_MAX, }; typedef struct { sqlite3 *db; pthread_mutex_t mutex; }sql_handle_t; static sql_handle_t sqlHandle={0}; static int overwrite_flag=0; static int time_to_utc(char *time, uint32_t *utc) { return 0; } static int utc_to_time(uint32_t *utc, char *time) { return 0; } static int get_count(sqlite3 *db, char *table) { int r; char temp[1024]; sqlite3_stmt *stmt=NULL; sprintf(temp, "SELECT * FROM %s", table); r = sqlite3_prepare_v2(db, temp, -1, &stmt, 0); if (r != SQLITE_OK) { log_e("Failed to prepare statement: %s\n", sqlite3_errmsg(db)); return -1; } int count = 0; while (sqlite3_step(stmt) == SQLITE_ROW) { count++; } sqlite3_finalize(stmt); return count; } static int get_count_ex(sqlite3 *db, char *sql) { int r; sqlite3_stmt *stmt=NULL; r = sqlite3_prepare_v2(db, sql, -1, &stmt, 0); if (r != SQLITE_OK) { log_e("Failed to prepare statement: %s\n", sqlite3_errmsg(db)); return -1; } int count = 0; while (sqlite3_step(stmt) == SQLITE_ROW) { count++; } sqlite3_finalize(stmt); return count; } static int get_key(sqlite3 *db, char *table, char *key, int mm) { int r,id=-1; char temp[1024]; sqlite3_stmt *stmt=NULL; if(mm==DB_MIN) { sprintf(temp, "SELECT MIN(%s) FROM %s;", key, table); } else { sprintf(temp, "SELECT MAX(%s) FROM %s;", key, table); } r = sqlite3_prepare_v2(db, temp, -1, &stmt, 0); if (r != SQLITE_OK) { log_e("___get_extr_key, sqlite3_prepare_v2 failed, %s\n", sqlite3_errmsg(db)); return -1; } while (sqlite3_step(stmt) == SQLITE_ROW) { id = sqlite3_column_int(stmt, 0); break; } sqlite3_finalize(stmt); return id; } /// @brief 初始化SQLite数据库 /// @param db 数据库接口 /// @param daba_name 数据库名称 /// @return int sqlite_init(sqlite3** db,pthread_mutex_t* pDBMutex, const char* daba_name) { int ret = 0; char *errorMsg = NULL; //sql_handle_t *h=&sqlHandle; if (pthread_mutex_init(pDBMutex, NULL) != 0) { log_d("DB Mutex init has failed\n"); return -1; } 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,pthread_mutex_t* pDBMutex) { int ret = 0; ret = sqlite3_close(db); pthread_mutex_destroy(pDBMutex); if(ret != SQLITE_OK) return; return; } /// @brief 关闭SQLite数据库 /// @param db 数据库接口 /* void sqlite_deinit(sqlite3 *db) { int ret = 0; ret = sqlite3_close(db); if(ret != SQLITE_OK) return; return; }*/ /// @brief 查询函数带锁用于防止写入冲突 /// @param db 数据库接口 /// @param _globalDeviceInfo 返回数据接口 int sqlite_SQL_RUN(sqlite3 *db,const char *select_sql, char ***pResult, int *nrow, int *ncolumn, char **err_msg) { int res=0; pthread_mutex_lock(&__globalDeviceManage.dbThreadlock); res = sqlite3_get_table(db, select_sql, pResult, nrow, ncolumn, err_msg); pthread_mutex_unlock(&__globalDeviceManage.dbThreadlock); return res; } /// @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) { log_e("sqlite handle error: %s \n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } //提取数据 if(nrow==0) { sqlite3_free_table(pResult); 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(_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->cascade.mode = atoi(pResult[rowIndex*ncolumn+10]); //获取波特率 _globalDeviceInfo->cascade.baudrate = atoi(pResult[rowIndex*ncolumn+11]); //获取从Modbus地址信息 _globalDeviceInfo->cascade.addr = atoi(pResult[rowIndex*ncolumn+12]); //获取继电器控制端口号 strcpy(_globalDeviceInfo->relay.path,pResult[rowIndex*ncolumn+13]); 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[2000] = {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 = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s \n%s\n", err_msg,select_sql); sqlite3_free(err_msg); 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) { if (_globalDeviceInfo == NULL || db == NULL) { return -1; } int res ; char select_sql[2000] = {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 WHERE product_id=%d;", _globalDeviceInfo->cascade.mode, _globalDeviceInfo->cascade.baudrate, _globalDeviceInfo->cascade.addr, //_globalDeviceInfo->_modbus_info.path,//只改级联的串口配置，继电器采集和传感器采集端口不变 _globalDeviceInfo->product.id); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s \n%s\n", err_msg,select_sql); sqlite3_free(err_msg); 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[1024] = {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, %s\n", select_sql); sqlite3_free(err_msg); return -1 ; } if(nrow==0) { log_e("sqlite search no data."); sqlite3_free_table(pResult); 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[1024] = {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 = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); dev_sql_VACCUM(db); 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\n", err_msg,select_sql); sqlite3_free(err_msg); 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[1024] = {0}; char *errorMsg = NULL; int nrow = 0; //满足条件的记录数目 int ncolumn = 0; //每条记录包含的字段数据 char** pResult = NULL; //用来指向sql执行结果的指针 char* err_msg=NULL; char *table="Table_PowerInfo"; GlobalPowerInfo *pInfo=_globalPowerChnInfo; if(overwrite_flag) { sprintf(select_sql,"UPDATE %s SET product_Index=%d,product_id=%d,product_ch_id=%d,product_ch_status=%d," "product_ch_voltage=%.2f,product_ch_current=%.2f,product_ch_power=%.2f,product_ch_power_freq=%.2f," "product_ch_consumption=%.2f,product_ch_power_factor=%.2f,product_samp_time='%s' " "WHERE product_Index=(SELECT MIN(product_Index) FROM %s);", table, *nIndex, pInfo->product_id, pInfo->product_ch_id, pInfo->product_status, pInfo->_power_info.voltage, pInfo->_power_info.current, pInfo->_power_info.power, pInfo->_power_info.freq, pInfo->_power_info.consumption, pInfo->_power_info.factor, pInfo->samp_time, table ); } else { sprintf(select_sql,"INSERT INTO %s VALUES ('%s',%d,%d,%d,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%d);", table, pInfo->samp_time, pInfo->product_id, pInfo->product_ch_id, pInfo->product_status, pInfo->_power_info.voltage, pInfo->_power_info.current, pInfo->_power_info.power, pInfo->_power_info.freq, pInfo->_power_info.consumption, pInfo->_power_info.factor, *nIndex); } res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } (*nIndex) += 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[1024] = {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, %s\n", select_sql); sqlite3_free(err_msg); return -1 ; } if(nrow==0) { log_e("sqlite search no data."); sqlite3_free_table(pResult); 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]); _globalPowerInfo->_power_info.NF_status=0; sqlite3_free_table(pResult); return 0; } /// @brief 获取所有采集的最新值 /// @param db /// @param _globalPowerManger /// @return int dev_search_latest_power_All_info(GlobalDeviceManager* globalDeviceManger,_OverChnPwrAckInfo* __overChnPwrAckInfo, int dev_addr) { GlobalPowerManger* _globalPowerMangerTemp = NULL ; char* upgrade_msg = NULL ; static netswitch_info_t swInfo; port_info_t *info=NULL; int idx=0; if(list_empty(&globalDeviceManger->_globalPowerManger.list)) { return -1; } if(dev_addr==0) { netswitch_get(&swInfo); } list_for_each_entry(_globalPowerMangerTemp, &globalDeviceManger->_globalPowerManger.list, list) { if (!_globalPowerMangerTemp) { continue; /* code */ } if (_globalPowerMangerTemp->product_ch_type==TREE_AC_TYPE||globalDeviceManger->_globalDevInfo.product.pwr_type == SmartPDU_Tree_AC_One_B) { GlobalTreeACManager* _TreeACTemp=NULL; int nStatus=-1;//-1则状态不变，0、1则是子状态 //判断是否三相单输出情况下 if(_globalPowerMangerTemp->product_ch_type==TREE_AC_TYPE||globalDeviceManger->_globalDevInfo.product.pwr_type == SmartPDU_Tree_AC_One_B) { list_for_each_entry(_TreeACTemp, &_globalPowerMangerTemp->list_Tree_AC, list_Tree_AC) { if (2 == _TreeACTemp->product_ph_outputType &&1==_TreeACTemp->product_ph_outputStatus) {//单项并且状态为输出 nStatus=_TreeACTemp->_PowerInfo.status; } } } list_for_each_entry(_TreeACTemp, &_globalPowerMangerTemp->list_Tree_AC, list_Tree_AC) { _OverChnPwrAckInfo *_overChnPwrBackInfoTemp = (_OverChnPwrAckInfo *)malloc(sizeof(_OverChnPwrAckInfo)); if (_overChnPwrBackInfoTemp == NULL) { log_e("_overChnPwrBackInfoTemp malloc err."); return -1; } memset(_overChnPwrBackInfoTemp,0,sizeof(_OverChnPwrAckInfo)); _overChnPwrBackInfoTemp->product_name = globalDeviceManger->_globalDevInfo.product.name; _overChnPwrBackInfoTemp->product_number = globalDeviceManger->_globalDevInfo.product.number; _overChnPwrBackInfoTemp->product_id = globalDeviceManger->_globalDevInfo.product.id; _overChnPwrBackInfoTemp->productChName = _globalPowerMangerTemp->product_ch_name; _overChnPwrBackInfoTemp->productChId = _globalPowerMangerTemp->product_ch_id; _overChnPwrBackInfoTemp->status = _globalPowerMangerTemp->_PowerInfo.status; _overChnPwrBackInfoTemp->NF_status = _globalPowerMangerTemp->_PowerInfo.NF_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; _overChnPwrBackInfoTemp->product_type=_globalPowerMangerTemp->product_ch_type; _overChnPwrBackInfoTemp->product_phType = _TreeACTemp->product_ph_type; _overChnPwrBackInfoTemp->ph_voltage = _TreeACTemp->_PowerInfo.voltage; _overChnPwrBackInfoTemp->ph_current = _TreeACTemp->_PowerInfo.current; _overChnPwrBackInfoTemp->ph_power = _TreeACTemp->_PowerInfo.power; _overChnPwrBackInfoTemp->ph_consumption = _TreeACTemp->_PowerInfo.consumption; _overChnPwrBackInfoTemp->ph_outputType = _TreeACTemp->product_ph_outputType; _overChnPwrBackInfoTemp->ph_outputStatus = _TreeACTemp->product_ph_outputStatus; if ((globalDeviceManger->_globalDevInfo.product.pwr_type == SmartPDU_Tree_AC_One ||globalDeviceManger->_globalDevInfo.product.pwr_type == SmartPDU_Tree_AC_One_B) && _TreeACTemp->product_ph_outputType == 2 && _TreeACTemp->product_ph_outputStatus == 2) // 如果是单项输出并且则非输出通道数据为0 { // 3-1模式 单项状态，不输出则数据为空 _overChnPwrBackInfoTemp->ph_voltage = 0.0; _overChnPwrBackInfoTemp->ph_current = 0.0; _overChnPwrBackInfoTemp->ph_power = 0.0; _overChnPwrBackInfoTemp->ph_consumption = 0.0; continue; } if (nStatus>=0) { _overChnPwrBackInfoTemp->status = nStatus; }else{ _overChnPwrBackInfoTemp->status = _globalPowerMangerTemp->_PowerInfo.status; } #ifdef USE_NETSWITCH if(dev_addr==0) { idx = _overChnPwrBackInfoTemp->productChId-1; info = &swInfo.port[idx]; } else { info = &_globalPowerMangerTemp->_PowerInfo.port; } _overChnPwrBackInfoTemp->mac_addr = info->mac[0]?info->mac:""; _overChnPwrBackInfoTemp->ip_addr = info->ip[0]?info->ip:""; #endif list_add_tail(&_overChnPwrBackInfoTemp->list, &__overChnPwrAckInfo->list); } } else { _OverChnPwrAckInfo *_overChnPwrBackInfoTemp = (_OverChnPwrAckInfo *)malloc(sizeof(_OverChnPwrAckInfo)); if (_overChnPwrBackInfoTemp == NULL) { log_e("_overChnPwrBackInfoTemp malloc err."); return -1; } memset(_overChnPwrBackInfoTemp,0,sizeof(_OverChnPwrAckInfo)); // 填数据 _overChnPwrBackInfoTemp->product_name = globalDeviceManger->_globalDevInfo.product.name; _overChnPwrBackInfoTemp->product_number = globalDeviceManger->_globalDevInfo.product.number; _overChnPwrBackInfoTemp->product_id = globalDeviceManger->_globalDevInfo.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; #ifdef USE_NETSWITCH if(dev_addr==0) { idx = _overChnPwrBackInfoTemp->productChId-1; info = &swInfo.port[idx]; } else { info = &_globalPowerMangerTemp->_PowerInfo.port; } _overChnPwrBackInfoTemp->mac_addr = info->mac[0]?info->mac:""; _overChnPwrBackInfoTemp->ip_addr = info->ip[0]?info->ip:""; #endif list_add_tail(&_overChnPwrBackInfoTemp->list, &__overChnPwrAckInfo->list); } } return 0; } /// @brief 清除历史记录 /// @param db /// @param PowerInfo /// @return int dev_delete_power_info_all(sqlite3 *db) { int res ; char delete_sql[1024] = {0}; char *errorMsg = NULL; int nrow = 0; //满足条件的记录数目 int ncolumn = 0; //每条记录包含的字段数据 char** pResult = NULL; //用来指向sql执行结果的指针 char* err_msg=NULL; sprintf(delete_sql,"DELETE FROM Table_PowerInfo;"); res = sqlite_SQL_RUN(db, delete_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,delete_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); dev_sql_VACCUM(db); return 0; } /// @brief 获取所有采集统计的最新值 /// @param db /// @param _globalPowerManger /// @return int dev_search_latest_power_statistic_info(GlobalDeviceManager* globalDeviceManger, _OverAllPwrAckInfo *all) { 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(all,0,sizeof(_OverAllPwrAckInfo)); all->product_name = globalDeviceManger->_globalDevInfo.product.name; all->product_id = globalDeviceManger->_globalDevInfo.product.id; all->product_number = globalDeviceManger->_globalDevInfo.product.number; int nTempSum=0; if(list_empty(&globalDeviceManger->_globalPowerManger.list)) { return -1; } list_for_each_entry(_globalPowerMangerTemp, &globalDeviceManger->_globalPowerManger.list, list) { //有通道电压不为0的才记录在内 if (_globalPowerMangerTemp->_PowerInfo.voltage > 0) { faverageVoltage += _globalPowerMangerTemp->_PowerInfo.voltage; faverageFreq += _globalPowerMangerTemp->_PowerInfo.freq; faverageFactor += _globalPowerMangerTemp->_PowerInfo.factor; nTempSum++; } } if (nTempSum>0) { faverageVoltage /= nTempSum; faverageFreq /= nTempSum; faverageFactor /= nTempSum; } nTempSum=0; list_for_each_entry(_globalPowerMangerTemp, &globalDeviceManger->_globalPowerManger.list, list) { all->consumption += _globalPowerMangerTemp->_PowerInfo.consumption ; //有通道电压不为0的才记录在内 if (_globalPowerMangerTemp->_PowerInfo.voltage <= 0) { continue; } 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; } } // 填数据 all->current += _globalPowerMangerTemp->_PowerInfo.current ; all->power += _globalPowerMangerTemp->_PowerInfo.power ; nTempSum++; } all->voltage = fSaveVoltage ; all->freq = fSaveFreq ; all->factor = fSaveFactor; all->power = all->power; return 0; } #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[2000] = {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, %s\n", select_sql); sqlite3_free(err_msg); return -1 ; } if(nrow==0) { log_e("sqlite search no data."); sqlite3_free_table(pResult); 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]); } } //电流统计 else if(total_type == TOTAL_CURRENT_TYPE) { for(int i=1;i < nCount; i++) { _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++) { _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++) { _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; } int dev_search_time_t_ac_power_info(sqlite3 *db,int product_id,int product_ch_id,int product_ph_id,int total_type,GlobalPowerInfo* _globalPowerInfo,char* start_time,char* end_time) { int res ; char select_sql[2000] = {0}; char *errorMsg = NULL; int nrow = 0; //满足条件的记录数目 int ncolumn = 0; //每条记录包含的字段数据 char** pResult = NULL; //用来指向sql执行结果的指针 char* err_msg=NULL; log_d("GET AC Totalinfo product_id = %d product_ch_id=%d product_ph_id=%d total_type=%d",product_id,product_ch_id,product_ph_id,total_type); snprintf(select_sql, sizeof(select_sql), "SELECT * FROM Table_PhasePowerInfo WHERE (product_id=%d) AND (product_ch_id_belong=%d) AND (product_ph_id=%d) AND (product_ph_samp_time BETWEEN '%s' AND '%s') ORDER BY product_Index DESC LIMIT 60;", product_id, product_ch_id, product_ph_id, start_time, end_time ); res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error, %s\n", select_sql); sqlite3_free(err_msg); return -1 ; } if(nrow==0) { log_e("sqlite search no data."); sqlite3_free_table(pResult); 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+10]); } } //电流统计 else if(total_type == TOTAL_CURRENT_TYPE) { for(int i=1;i < nCount; i++) { _globalPowerInfo->_total_current.ydata[i] = atof(pResult[(nCount-i)*ncolumn+5]); strcpy(_globalPowerInfo ->_total_current.xAxis[i],pResult[(nCount-i)*ncolumn+10]); } } //功率统计 else if(total_type == TOTAL_POWER_TYPE) { for(int i=0;i < nCount; i++) { _globalPowerInfo->_total_power.ydata[i] = atof(pResult[(nCount-i)*ncolumn+6]); strcpy(_globalPowerInfo ->_total_power.xAxis[i],pResult[(nCount-i)*ncolumn+10]); } } //耗电量统计 else if(total_type == TOTAL_POWER_CONS_TYPE) { for(int i=0;i < nCount; i++) { _globalPowerInfo->_total_consumption.ydata[i] = atof(pResult[(nCount-i)*ncolumn+8]); strcpy(_globalPowerInfo ->_total_consumption.xAxis[i],pResult[(nCount-i)*ncolumn+10]); } } sqlite3_free_table(pResult); return 0; } int dev_search_latest_t_ac_power_statistic_info(int nCh, int nType, _OverAllPwrAckInfo *__overChnPwrAckInfo,GlobalDeviceManager* globalDeviceManger) { GlobalTreeACManager *_pTreeACPowerMangerTemp = NULL; if(list_empty(&globalDeviceManger->_globalPowerManger.list_Tree_AC)) { return -1; } list_for_each_entry(_pTreeACPowerMangerTemp, &globalDeviceManger->_globalPowerManger.list_Tree_AC, list_Tree_AC) { if (_pTreeACPowerMangerTemp->product_ch_id != nCh ||_pTreeACPowerMangerTemp->product_ph_type!=nType) { continue; } __overChnPwrAckInfo->voltage = _pTreeACPowerMangerTemp->_PowerInfo.voltage; __overChnPwrAckInfo->current = _pTreeACPowerMangerTemp->_PowerInfo.current; __overChnPwrAckInfo->power = _pTreeACPowerMangerTemp->_PowerInfo.power; __overChnPwrAckInfo->freq = _pTreeACPowerMangerTemp->_PowerInfo.freq; __overChnPwrAckInfo->consumption = _pTreeACPowerMangerTemp->_PowerInfo.consumption; __overChnPwrAckInfo->factor = _pTreeACPowerMangerTemp->_PowerInfo.factor; __overChnPwrAckInfo->product_name = globalDeviceManger->_globalDevInfo.product.name; __overChnPwrAckInfo->product_number = globalDeviceManger->_globalDevInfo.product.number; __overChnPwrAckInfo->product_id = globalDeviceManger->_globalDevInfo.product.id; return 0; break; } return -1; } /// @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[1024] = {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 OR alarmType=%d OR alarmType=%d ) and (alarmDate BETWEEN '%s' AND '%s');",0,1,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 Table_AlarmManage handle error."); sqlite3_free(err_msg); return -1 ; } if(nrow==0) { log_e("sqlite search Table_AlarmManage no data."); sqlite3_free_table(pResult); return -1 ; } __SystemTotalInfo->warn_total = atoi(pResult[1]); sqlite3_free_table(pResult); //查询日志总数 snprintf(select_sql,sizeof(select_sql),"SELECT count (*) FROM Table_AlarmManage where (alarmType=%d) and (alarmDate BETWEEN '%s' AND '%s');",4,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."); sqlite3_free(err_msg); return -1 ; } if(nrow==0) { log_e("sqlite search run_log no data."); sqlite3_free_table(pResult); return -1 ; } __SystemTotalInfo->log_total = atoi(pResult[1]); sqlite3_free_table(pResult); //查询传感器总数 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."); sqlite3_free(err_msg); return -1 ; } if(nrow==0) { log_e("sqlite search Table_SensorInfo no data."); sqlite3_free_table(pResult); 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[1024] = {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: %s \n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } if(nrow==0) { log_e("sqlite search no data."); sqlite3_free_table(pResult); 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++) { float PowerTemp=atof(pResult[(nCount-i)*ncolumn+6]); float PowerFactor=atof(pResult[(nCount-i)*ncolumn+9]); if (PowerFactor<0.05) { PowerFactor=1.0; } _globalPowerInfo->_power_trend_info.ydata[POWER_MERITORIOUS_TYPE][i] = PowerTemp; _globalPowerInfo->_power_trend_info.ydata[POWER_REACTIVE_TYPE][i] = PowerTemp / PowerFactor - PowerTemp; _globalPowerInfo->_power_trend_info.ydata[POWER_APPARENT_TYPE][i] = PowerTemp / PowerFactor; 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 三相功率趋势图接口(有功功率、无功功率以及视在功率) /// @param db /// @param product_id /// @param product_ch_id /// @param total_type /// @param _globalPowerInfo /// @return int dev_search_power_AC_trend_info(sqlite3 *db,int product_id,int product_ch_id,int product_ph_id,GlobalPowerInfo* _globalPowerInfo,char* start_time,char* end_time) { int res ; char select_sql[1024] = {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_PhasePowerInfo WHERE (product_id=%d) AND (product_ph_id=%d) AND (product_ch_id_belong=%d) AND (product_ph_samp_time BETWEEN '%s' AND '%s') ORDER BY product_index DESC LIMIT 60;", product_id, product_ph_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: %s \n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } if(nrow==0) { log_e("sqlite search no data."); sqlite3_free_table(pResult); 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++) { float PowerTemp=atof(pResult[(nCount-i)*ncolumn+6]); float PowerFactor=atof(pResult[(nCount-i)*ncolumn+9]); if (PowerFactor<0.05) { PowerFactor=1.0; } _globalPowerInfo->_power_trend_info.ydata[POWER_MERITORIOUS_TYPE][i] = PowerTemp; _globalPowerInfo->_power_trend_info.ydata[POWER_REACTIVE_TYPE][i] = PowerTemp / PowerFactor - PowerTemp; _globalPowerInfo->_power_trend_info.ydata[POWER_APPARENT_TYPE][i] = PowerTemp / PowerFactor; 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[1024] = {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) { log_e("sqlite handle error: %s \n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } if(nrow==0) { sqlite3_free_table(pResult); 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; } int dev_check_power_manage_info(sqlite3 *db, int cnt) { int r=0,n; char temp[200]; char *errmsg=NULL; char *table="Table_PowerManage"; n = get_count(db, table); if(n!=cnt) { sprintf(temp, "DELETE FROM %s", table); r = sqlite3_exec(db, temp, NULL, 0, &errmsg); if(r != SQLITE_OK) { log_e("___sqlite3_exec failed, %s, %s\n\n", temp, errmsg); sqlite3_free(errmsg); r = -1; } dev_sql_VACCUM(db); } return r; } /// @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[2000] = {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 = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s \n%s\n", err_msg,select_sql); sqlite3_free(err_msg); 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[1024] = {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) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -2 ; } //提取数据 if(nrow==0) { sqlite3_free_table(pResult); 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_insert_t_ac_power_manage_info(sqlite3 *db,int product_id,int product_ch_id,GlobalTreeACManager* _globalPowerChnManger) { 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_PhasePowerManage VALUES (%d,%d,%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_ph_id, _globalPowerChnManger->product_ph_type, _globalPowerChnManger->product_ch_id, _globalPowerChnManger->product_saddr, _globalPowerChnManger->product_ch_addr, _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_ph_outputType, _globalPowerChnManger->product_ph_outputStatus ); printf("select_sql:%s\n",select_sql); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); return 0; } /// @brief 获取三项通道基本管理信息 /// @param db /// @param product_id /// @param _globalPowerManger /// @return int dev_get_t_ac_power_manage_info(sqlite3 *db,int product_id,int product_addr,int product_ch_id,int product_ph_type,GlobalTreeACManager* _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,"SELECT* FROM Table_PhasePowerManage WHERE product_id=%d AND product_ch_addr_id=%d and product_ph_order=%d;",product_id,product_addr,product_ph_type); res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -2 ; } //提取数据 if(nrow==0) { sqlite3_free_table(pResult); return -1 ; } //获取产品ID _globalPowerChnManger->product_id = atoi(pResult[rowIndex*ncolumn+0]); //获取三相通道ID _globalPowerChnManger->product_ph_id = atoi(pResult[rowIndex*ncolumn+1]); //获取三相通道类型 _globalPowerChnManger->product_ph_type = atoi(pResult[rowIndex*ncolumn+2]); //获取绑定通道信息 _globalPowerChnManger->product_ch_id = atoi(pResult[rowIndex*ncolumn+3]); //获取实际三相通道位置版id _globalPowerChnManger->product_saddr = atoi(pResult[rowIndex*ncolumn+4]); //获取实际三相通道位置偏移 _globalPowerChnManger->product_ch_addr = atoi(pResult[rowIndex*ncolumn+5]); //获取电压超上限使能 _globalPowerChnManger->global_over_manager->product_vol_upper_enable = atoi(pResult[rowIndex*ncolumn+6]); //获取电压上限范围 _globalPowerChnManger->global_over_manager->product_vol_upper_threshold = atof(pResult[rowIndex*ncolumn+7]); //获取电压上限超限动作 _globalPowerChnManger->global_over_manager->product_vol_over_upper_threshold_ctrl = atoi(pResult[rowIndex*ncolumn+8]); //获取电压上限超限关联参数 _globalPowerChnManger->global_over_manager->product_vol_over_upper_threshold_ctrl_para = atoi(pResult[rowIndex*ncolumn+9]); //获取电压超下限使能 _globalPowerChnManger->global_over_manager->product_vol_lower_enable = atoi(pResult[rowIndex*ncolumn+10]); //获取电压下限范围 _globalPowerChnManger->global_over_manager->product_vol_lower_threshold = atof(pResult[rowIndex*ncolumn+11]); //获取电压下限超限动作 _globalPowerChnManger->global_over_manager->product_vol_over_lower_threshold_ctrl = atoi(pResult[rowIndex*ncolumn+12]); //超限关联参数 _globalPowerChnManger->global_over_manager->product_vol_over_lower_threshold_ctrl_para = atoi(pResult[rowIndex*ncolumn+13]); //获取电流超上限使能 _globalPowerChnManger->global_over_manager->product_cur_upper_enable = atoi(pResult[rowIndex*ncolumn+14]); //获取电流上限范围 _globalPowerChnManger->global_over_manager->product_cur_upper_threshold = atof(pResult[rowIndex*ncolumn+15]); //获取电流上限超限动作 _globalPowerChnManger->global_over_manager->product_cur_over_upper_threshold_ctrl = atoi(pResult[rowIndex*ncolumn+16]); //超限关联参数 _globalPowerChnManger->global_over_manager->product_cur_over_upper_threshold_ctrl_para = atoi(pResult[rowIndex*ncolumn+17]); //获取功耗超上限使能 _globalPowerChnManger->global_over_manager->product_pwr_upper_enable = atoi(pResult[rowIndex*ncolumn+18]); //获取功耗上限范围 _globalPowerChnManger->global_over_manager->product_pwr_upper_threshold = atof(pResult[rowIndex*ncolumn+19]); //获取功耗上限超限动作 _globalPowerChnManger->global_over_manager->product_pwr_over_upper_threshold_ctrl = atoi(pResult[rowIndex*ncolumn+20]); //超限关联参数 _globalPowerChnManger->global_over_manager->product_pwr_over_upper_threshold_ctrl_para = atoi(pResult[rowIndex*ncolumn+21]); //获取耗电量超上限使能 _globalPowerChnManger->global_over_manager->product_pwrcon_upper_enable = atoi(pResult[rowIndex*ncolumn+22]); //获取耗电量上限范围 _globalPowerChnManger->global_over_manager->product_pwrcon_upper_threshold = atof(pResult[rowIndex*ncolumn+23]); //获取耗电量上限超限动作 _globalPowerChnManger->global_over_manager->product_pwrcon_over_upper_threshold_ctrl = atoi(pResult[rowIndex*ncolumn+24]); //超限关联参数 _globalPowerChnManger->global_over_manager->product_pwrcon_over_upper_threshold_ctrl_para = atoi(pResult[rowIndex*ncolumn+25]); _globalPowerChnManger->product_ph_outputType = atoi(pResult[rowIndex*ncolumn+26]); _globalPowerChnManger->product_ph_outputStatus = atoi(pResult[rowIndex*ncolumn+27]); sqlite3_free_table(pResult); return 0; } /// @brief 更新三项通道基本管理信息 /// @param db /// @param product_id /// @param _globalPowerManger /// @return int dev_update_t_ac_power_manage_info(sqlite3 *db,GlobalTreeACManager* _globalPowerChnInfo) { 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, "UPDATE Table_PhasePowerManage SET product_ph_outputType=%d,product_ph_outputStatus=%d,product_ch_id_belong=%d,product_ph_id=%d where product_ch_addr_id=%d and product_ch_addr_ch_id=%d and product_ph_order=%d", _globalPowerChnInfo->product_ph_outputType, _globalPowerChnInfo->product_ph_outputStatus, _globalPowerChnInfo->product_ch_id, _globalPowerChnInfo->product_ph_id, _globalPowerChnInfo->product_saddr, _globalPowerChnInfo->product_ch_addr, _globalPowerChnInfo->product_ph_type ); printf("UPDATE_sql:%s\n",select_sql); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); return 0; } //超限报警信息更新 int dev_update_t_ac_power_manage_over_info(sqlite3 *db,GlobalTreeACManager* _globalPowerChnManger) { int res; char select_sql[2048] = {0}; char *errorMsg = NULL; int nrow = 0; // 满足条件的记录数目 int ncolumn = 0; // 每条记录包含的字段数据 char **pResult = NULL; // 用来指向sql执行结果的指针 char *err_msg = NULL; sprintf(select_sql, "UPDATE Table_PhasePowerManage SET \ product_ph_vol_upper_enable = %d,\ product_ph_vol_upper_threshold = %.2f,\ product_ph_vol_over_upper_threshold_ctrl = %d,\ product_ph_vol_over_upper_threshold_ctrl_para = %d,\ product_ph_vol_lower_enable = %d,\ product_ph_vol_lower_threshold = %.2f,\ product_ph_vol_over_lower_threshold_ctrl = %d,\ product_ph_vol_over_lower_threshold_ctrl_para = %d,\ product_ph_cur_upper_enable = %d,\ product_ph_cur_upper_threshold = %.2f,\ product_ph_cur_over_upper_threshold_ctrl = %d,\ product_ph_cur_over_upper_threshold_ctrl_para = %d,\ product_ph_pwr_upper_enable = %d,\ product_ph_pwr_upper_threshold = %.2f,\ product_ph_pwr_over_upper_threshold_ctrl = %d,\ product_ph_pwr_over_upper_threshold_ctrl_para = %d,\ product_ph_pwrcon_upper_enable = %d,\ product_ph_pwrcon_upper_threshold =%.2f,\ product_ph_pwrcon_over_upper_threshold = %d,\ product_ph_pwrcon_over_upper_threshold_para = %d\ where product_ph_outputType=%d and product_ph_outputStatus=%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, _globalPowerChnManger->product_ph_outputType, _globalPowerChnManger->product_ph_outputStatus); printf("select_sql:%s\n", select_sql); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if (res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg, select_sql); sqlite3_free(err_msg); return -1; } sqlite3_free_table(pResult); return 0; } /// @brief 插入三项通子通道记录 /// @param db /// @param product_id /// @param _globalPowerManger /// @return int dev_insert_t_ac_power_info(sqlite3 *db,GlobalTreeACManager* _globalPowerChnInfo,int* nIndex,char* pTimeData) { int res ; char select_sql[1024] = {0}; char *errorMsg = NULL; int nrow = 0; //满足条件的记录数目 int ncolumn = 0; //每条记录包含的字段数据 char** pResult = NULL; //用来指向sql执行结果的指针 char* err_msg=NULL; char *table="Table_PhasePowerInfo"; GlobalTreeACManager *pInfo=_globalPowerChnInfo; if(overwrite_flag) { sprintf(select_sql,"UPDATE %s SET product_index=%d,product_id=%d,product_ph_id=%d,product_ch_id_belong=%d," "product_ph_voltage=%.2f,product_ph_current=%.2f,product_ph_power=%.2f,product_ph_power_freq=%.2f," "product_ph_consumption=%.2f,product_ph_power_factor=%.2f,product_ph_samp_time='%s' " "WHERE product_index=(SELECT MIN(product_index) FROM %s);", table, *nIndex, pInfo->product_id, pInfo->product_ph_type, pInfo->product_ch_id, pInfo->_PowerInfo.voltage, pInfo->_PowerInfo.current, pInfo->_PowerInfo.power, pInfo->_PowerInfo.freq, pInfo->_PowerInfo.consumption, pInfo->_PowerInfo.factor, pTimeData, table ); } else { sprintf(select_sql,"INSERT INTO %s VALUES (%d,%d,%d,%d,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,'%s');", table, *nIndex, pInfo->product_id, pInfo->product_ph_type, pInfo->product_ch_id, pInfo->_PowerInfo.voltage, pInfo->_PowerInfo.current, pInfo->_PowerInfo.power, pInfo->_PowerInfo.freq, pInfo->_PowerInfo.consumption, pInfo->_PowerInfo.factor, pTimeData ); } // if(_globalPowerChnInfo->product_ch_id==0) //log_d(select_sql); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } (*nIndex) += 1; sqlite3_free_table(pResult); return 0; } /// @brief 清除历史记录 /// @param db /// @param PowerInfo /// @return int dev_delete_ac_power_info_all(sqlite3 *db) { int res ; char delete_sql[1024] = {0}; char *errorMsg = NULL; int nrow = 0; //满足条件的记录数目 int ncolumn = 0; //每条记录包含的字段数据 char** pResult = NULL; //用来指向sql执行结果的指针 char* err_msg=NULL; sprintf(delete_sql,"DELETE FROM Table_PhasePowerInfo;"); res = sqlite_SQL_RUN(db, delete_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,delete_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); dev_sql_VACCUM(db); 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 = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); return 0 ; } /// @brief 更新通道基本管理信息 /// @param db /// @param product_id /// @param _globalPowerManger /// @return int dev_update_power_manage_genera_info_addr(sqlite3 *db,int product_id,int addr,int addr_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_addr_id=%d AND product_ch_addr_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, addr, addr_ch_id ); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); 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_d("%s\n",select_sql); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); return 0 ; } /// @brief get breakers manager infomation /// @param db /// @param product_id /// @param _globalBreakerManager /// @return int dev_get_breaker_manage_info(sqlite3 *db,int product_id,GlobalBreakerManager* _globalBreakerManager) { 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}; GlobalBreakerManager* _globalBreakerManagerTemp = NULL; sprintf(select_sql,"SELECT* FROM Table_BreakerManage WHERE product_id=%d;",product_id); res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -2 ; } if(nrow==0) { sqlite3_free_table(pResult); return -1 ; } for (size_t i = 1; i <= nrow; i++) { _globalBreakerManagerTemp = (GlobalBreakerManager*)malloc(sizeof(GlobalBreakerManager)); if(_globalBreakerManagerTemp==NULL) return -1 ; memset(_globalBreakerManagerTemp,0,sizeof(GlobalBreakerManager)); _globalBreakerManagerTemp->product_id = atoi(pResult[i*ncolumn + 0]); _globalBreakerManagerTemp->breaker_id = atoi(pResult[i*ncolumn + 1]); strcpy(_globalBreakerManagerTemp->breaker_gather_type,pResult[i*ncolumn+2]); strcpy(_globalBreakerManagerTemp->breaker_com,pResult[i*ncolumn+3]); _globalBreakerManagerTemp->breaker_gather_addr = atoi(pResult[i*ncolumn+4]); _globalBreakerManagerTemp->breaker_chn = atoi(pResult[i*ncolumn+5]); printf("%d %d %s %s %d %d\n",_globalBreakerManagerTemp->product_id, _globalBreakerManagerTemp->breaker_id, _globalBreakerManagerTemp->breaker_gather_type, _globalBreakerManagerTemp->breaker_com, _globalBreakerManagerTemp->breaker_gather_addr, _globalBreakerManagerTemp->breaker_chn); if(pResult[i*ncolumn+6]) strcpy(_globalBreakerManagerTemp->breaker_switch_name,pResult[i*ncolumn+6]); if(pResult[i*ncolumn+7]) strcpy(_globalBreakerManagerTemp->breaker_chns_name,pResult[i*ncolumn+7]); printf("isnot failed!!!\n"); list_add_tail(&_globalBreakerManagerTemp->list, &_globalBreakerManager->list); } sqlite3_free_table(pResult); return 0 ; } /// @brief delete a breaker manager /// @param db /// @param product_id /// @param breaker_id /// @return int dev_delete_breaker_manage(sqlite3 *db,int product_id,int breaker_id) { 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,"DELETE FROM Table_BreakerManage WHERE product_id=%d AND breaker_id=%d;", product_id, breaker_id ); res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); dev_sql_VACCUM(db); return 0; } /// @brief insert a breaker infomation /// @param db /// @param _globalBreakerManger /// @return int dev_insert_breaker_genera_manage(sqlite3 *db,GlobalBreakerManager* _globalBreakerManager) { int res ; char select_sql[2000] = {0}; char *errorMsg = NULL; int nrow = 0; //满足条件的记录数目 int ncolumn = 0; //每条记录包含的字段数据 char** pResult = NULL; //用来指向sql执行结果的指针 char* err_msg=NULL; sprintf(select_sql,"INSERT INTO Table_BreakerManage " "(product_id,breaker_id,breaker_gather_type," "breaker_com,breaker_gather_addr,breaker_chn," "breaker_switch_name,breaker_chns_name)" "VALUES (%d,%d,'%s','%s',%d,%d,'%s','%s');", _globalBreakerManager->product_id, // _globalSensorManger->sensor_id, _globalBreakerManager->breaker_id, _globalBreakerManager->breaker_gather_type, _globalBreakerManager->breaker_com, _globalBreakerManager->breaker_gather_addr, _globalBreakerManager->breaker_chn, _globalBreakerManager->breaker_switch_name, _globalBreakerManager->breaker_chns_name ); log_d(select_sql); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); return 0; } /// @brief update a breaker information /// @param db /// @param product_id /// @param breaker_id /// @param _globalBreakerManger /// @return int dev_update_breaker_genera_manage(sqlite3 *db,int product_id,int breaker_id,GlobalBreakerManager* _globalBreakerManager) { 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_BreakerManage SET product_id=%d,breaker_id=%d,\ breaker_gather_type='%s',breaker_com='%s',breaker_gather_addr=%d,\ breaker_chn=%d,breaker_switch_name='%s',breaker_chns_name='%s' WHERE product_id=%d AND breaker_id=%d;", _globalBreakerManager->product_id, _globalBreakerManager->breaker_id, _globalBreakerManager->breaker_gather_type, _globalBreakerManager->breaker_com, _globalBreakerManager->breaker_gather_addr, _globalBreakerManager->breaker_chn, _globalBreakerManager->breaker_switch_name, _globalBreakerManager->breaker_chns_name, product_id, breaker_id); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); 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); res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -2 ; } if(nrow==0) { sqlite3_free_table(pResult); return -1 ; } for (size_t i = 1; i <= nrow; i++) { GlobalSensorManger* _globalSensorMangerTemp = (GlobalSensorManger*)malloc(sizeof(GlobalSensorManger)); if(_globalSensorMangerTemp==NULL) return -1 ; memset(_globalSensorMangerTemp,0,sizeof(GlobalSensorManger)); _globalSensorMangerTemp->over_manager = (GlobalSensorOverManage*)malloc(sizeof(GlobalSensorOverManage)); if(_globalSensorMangerTemp->over_manager==NULL) return -1 ; _globalSensorMangerTemp->product_id = atoi(pResult[i*ncolumn+1]); _globalSensorMangerTemp->sensor_id = atoi(pResult[i*ncolumn+0]); _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_name,language_sensor_name[__globalDeviceManage._globalDevInfo.product.language][(int)_globalSensorMangerTemp->sensor_type]); 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 ; if (pResult[i * ncolumn + 7]) { _globalSensorMangerTemp->sensor_addr = atoi(pResult[i * ncolumn + 7]); } else { _globalSensorMangerTemp->sensor_addr = 0; } } _globalSensorMangerTemp->sensor_status = 1;//atoi(pResult[i*ncolumn+8]); _globalSensorMangerTemp->sensor_val_count = atoi(pResult[i*ncolumn+9]); // 记录超限信息 memset(_globalSensorMangerTemp->over_manager->sensor_val1_upper_threshold_ctrl_para, 0 ,sizeof(char)*512); memset(_globalSensorMangerTemp->over_manager->sensor_val1_lower_threshold_ctrl_para, 0 ,sizeof(char)*512); memset(_globalSensorMangerTemp->over_manager->sensor_val2_lower_threshold_ctrl_para, 0 ,sizeof(char)*512); memset(_globalSensorMangerTemp->over_manager->sensor_val2_upper_threshold_ctrl_para, 0 ,sizeof(char)*512); _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]); if(pResult[i*ncolumn+13])strcpy(_globalSensorMangerTemp->over_manager->sensor_val1_upper_threshold_ctrl_para ,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]); if(pResult[i*ncolumn+17])strcpy(_globalSensorMangerTemp->over_manager->sensor_val1_lower_threshold_ctrl_para , 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]); if(pResult[i*ncolumn+21])strcpy(_globalSensorMangerTemp->over_manager->sensor_val2_upper_threshold_ctrl_para , 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]); if(pResult[i*ncolumn+25])strcpy(_globalSensorMangerTemp->over_manager->sensor_val2_lower_threshold_ctrl_para , 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[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_SensorInfo VALUES (%d,'%s',%d,%d,%.2f,%.2f);", *nIndex, _globalSensorInfo->samp_time, _globalSensorInfo->product_id, _globalSensorInfo->sensor_id, _globalSensorInfo->val1, _globalSensorInfo->val2); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); 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[1024] = {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: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } if(nrow==0) { log_e("sqlite search no data."); sqlite3_free_table(pResult); 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[1024] = {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) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } if(nrow==0) { sqlite3_free_table(pResult); return -1 ; } //初始化头 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+1]); _globalSensorInfoTemp->product_id = atoi(pResult[i*ncolumn+2]); _globalSensorInfoTemp->sensor_id = atoi(pResult[i*ncolumn+3]); if(pResult[i*ncolumn+4]!=NULL) _globalSensorInfoTemp->val1 = atof(pResult[i*ncolumn+4]); if(pResult[i*ncolumn+5]!=NULL) _globalSensorInfoTemp->val2 = atof(pResult[i*ncolumn+5]); //尾部插入 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[1024] = {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) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); dev_sql_VACCUM(db); return 0; } int dev_delete_sensor_info_all(sqlite3 *db) { 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,"DELETE FROM Table_SensorInfo;"); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); dev_sql_VACCUM(db); return 0; } /// @brief 插入一条传感器管理信息 /// @param db /// @param _globalSensorManger /// @return int dev_insert_sensor_genera_manage(sqlite3 *db,GlobalSensorManger* _globalSensorManger) { int res ; char select_sql[2000] = {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 = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); 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 = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); 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[3000] = {0}; sprintf(select_sql, "UPDATE Table_SensorManage SET \ sensor_val1_u_enable=%d,\ sensor_val1_u_threshold=%0.2f,\ sensor_val1_over_u_threshold_ctrl=%d,\ sensor_val1_over_u_threshold_ctrl_para='%s',\ sensor_val1_l_enable=%d,\ sensor_val1_l_threshold=%0.2f,\ sensor_val1_over_l_threshold_ctrl=%d,\ sensor_val1_over_l_threshold_ctrl_para='%s',\ sensor_val2_u_enable=%d,\ sensor_val2_u_threshold=%0.2f,\ sensor_val2_over_u_threshold_ctrl=%d,\ sensor_val2_over_u_threshold_ctrl_para='%s',\ sensor_val2_l_enable=%d,\ sensor_val2_l_threshold=%0.2f,\ sensor_val2_over_l_threshold_ctrl=%d,\ sensor_val2_over_l_threshold_ctrl_para='%s'\ WHERE product_id=%d AND sensor_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_val1_lower_enabled, _globalSensorManger->over_manager->sensor_val1_lower_threshold, _globalSensorManger->over_manager->sensor_val1_lower_threshold_ctrl, _globalSensorManger->over_manager->sensor_val1_lower_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, _globalSensorManger->over_manager->sensor_val2_lower_enabled, _globalSensorManger->over_manager->sensor_val2_lower_threshold, _globalSensorManger->over_manager->sensor_val2_lower_threshold_ctrl, _globalSensorManger->over_manager->sensor_val2_lower_threshold_ctrl_para, product_id, sensor_id); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); 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,time_data_t 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}; char *table="Table_AlarmManage"; if(overwrite_flag) { int alarmNumber=get_key(db, table, "alarmNumber", DB_MAX); if(alarmNumber<0) { return -1; } alarmNumber++; sprintf(select_sql,"UPDATE %s SET alarmNumber=%d,alarmDate='%s',alarmType=%d,product_id=%d,alarmContent='%s' " "WHERE alarmNumber=(SELECT MIN(alarmNumber) FROM %s);", table, alarmNumber, date_time, alarm_type, product_id, alarm_content, table); } else { sprintf(select_sql,"INSERT INTO %s (alarmDate,alarmType,product_id,alarmContent) VALUES ('%s',%d,%d,'%s');", table, date_time, alarm_type, product_id, alarm_content); } res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); //smtp_send(); return 0; } /// @brief 查询电源 传感器以及网络告警信息 /// @param db /// @param product_id /// @param alarm_type /// @param _alarm_ack_info /// @return 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 where (alarmType=%d) or (alarmType=%d) or (alarmType=%d);",ALARM_TYPE_POWER,ALARM_TYPE_SENSOR,ALARM_TYPE_NETWORK); }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 handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } if(nrow==0) { log_e("sqlite search Table_AlarmManage no data."); sqlite3_free_table(pResult); 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) and ((alarmType=%d) or (alarmType=%d) or (alarmType=%d)) order by alarmDate desc LIMIT %d OFFSET %d ", product_id, ALARM_TYPE_POWER,ALARM_TYPE_SENSOR,ALARM_TYPE_NETWORK, 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) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } if(nrow==0) { log_e("dev_serarch_all_alarm nrow is 0\n"); sqlite3_free_table(pResult); return -1 ; } for (size_t i = 1; i <= nrow; i++) { _AlarmAckInfo* _alarmTemp = (_AlarmAckInfo*)malloc(sizeof( _AlarmAckInfo)); if(_alarmTemp==NULL) { log_e("alarm malloc failed"); sqlite3_free_table(pResult); return -1; } memset(_alarmTemp, 0, sizeof(_AlarmAckInfo)); 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._globalDevInfo.product.number); strcpy(_alarmTemp->deviceName,__globalDeviceManage._globalDevInfo.product.name); strcpy(_alarmTemp->alarmContent,pResult[i*ncolumn+4]); //尾部插入 list_add_tail(&_alarmTemp->list, &_alarm_ack_info->list); } sqlite3_free_table(pResult); return 0; } /// @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 int dev_search_alarm_info(sqlite3* db, int product_id, int alarm_type, time_data_t start_time, time_data_t 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 (alarmType=%d) or (alarmType=%d) or (alarmType=%d);",ALARM_TYPE_POWER,ALARM_TYPE_SENSOR,ALARM_TYPE_NETWORK); }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); } #if 1 res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } if(nrow==0) { log_e("sqlite search Table_AlarmManage no data."); sqlite3_free_table(pResult); return -1 ; } *nAlarmSum = atoi(pResult[1]); #else int cnt = get_count_ex(db, select_sql); if(cnt<0) { return -1; } *nAlarmSum = cnt; #endif if (alarm_type<0)//小于0加载所有的 { sprintf(select_sql,"SELECT* FROM Table_AlarmManage WHERE (product_id=%d) and ((alarmType=%d) or (alarmType=%d) or (alarmType=%d)) order by alarmDate desc LIMIT %d OFFSET %d ;", product_id, ALARM_TYPE_POWER,ALARM_TYPE_SENSOR,ALARM_TYPE_NETWORK, 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) 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) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } if(nrow==0) { sqlite3_free_table(pResult); 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._globalDevInfo.product.number); strcpy(_alarmTemp->deviceName,__globalDeviceManage._globalDevInfo.product.name); strcpy(_alarmTemp->alarmContent,pResult[i*ncolumn+4]); //尾部插入 list_add_tail(&_alarmTemp->list, &_alarm_ack_info->list); } sqlite3_free_table(pResult); return 0; } /// @brief 清除历史记录 /// @param db /// @param product_id /// @param alarm_type /// @return int dev_delete_alarm_info_all(sqlite3* db,int alarm_type) { 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,"DELETE FROM Table_AlarmManage WHERE alarmType=%d;",alarm_type); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); dev_sql_VACCUM(db); return 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._globalDevInfo.product.id); res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } if(nrow==0) { sqlite3_free_table(pResult); return -2 ; } //获取ID _globalNTPInfo->product_id = atoi(pResult[rowIndex*ncolumn+0]); _globalNTPInfo->mode = atoi(pResult[rowIndex*ncolumn+1]); strcpy(_globalNTPInfo->ntpName,pResult[rowIndex*ncolumn+2]); strcpy(_globalNTPInfo->ntpaddress ,pResult[rowIndex*ncolumn+3]); _globalNTPInfo->port = atoi(pResult[rowIndex*ncolumn+4]); 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; } 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 = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); return 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 = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); return 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._globalDevInfo.product.id); res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } if(nrow==0) { sqlite3_free_table(pResult); 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]); strcpy(_globalSNMPInfo->public,pResult[rowIndex*ncolumn+3]); _globalSNMPInfo->trapmode = atoi(pResult[rowIndex*ncolumn+4]); if(_globalSNMPInfo->mode==2) { if(pResult[rowIndex*ncolumn+5]) strcpy(_globalSNMPInfo->crypt.user,pResult[rowIndex*ncolumn+5]); if(pResult[rowIndex*ncolumn+6]) strcpy(_globalSNMPInfo->crypt.auth.alg,pResult[rowIndex*ncolumn+6]); if(pResult[rowIndex*ncolumn+7]) strcpy(_globalSNMPInfo->crypt.auth.passwd,pResult[rowIndex*ncolumn+7]); if(pResult[rowIndex*ncolumn+8]) strcpy(_globalSNMPInfo->crypt.priv.alg,pResult[rowIndex*ncolumn+8]); if(pResult[rowIndex*ncolumn+9]) strcpy(_globalSNMPInfo->crypt.priv.passwd,pResult[rowIndex*ncolumn+9]); } sqlite3_free_table(pResult); return 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, " "user, auth_alg, auth_passwd, priv_alg, priv_passwd) VALUES (%d,%d,%d,'%s',%d,'%s',%s,'%s',%s,'%s');", _globalSNMPInfo->product_id, _globalSNMPInfo->version, _globalSNMPInfo->mode, _globalSNMPInfo->public, _globalSNMPInfo->trapmode, _globalSNMPInfo->crypt.user, _globalSNMPInfo->crypt.auth.alg, _globalSNMPInfo->crypt.auth.passwd, _globalSNMPInfo->crypt.priv.alg, _globalSNMPInfo->crypt.priv.passwd ); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); return 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, " "user='%s',auth_alg=%s,auth_passwd='%s',priv_alg=%s,priv_passwd='%s' WHERE product_id=%d;", _globalSNMPInfo->version, _globalSNMPInfo->mode, _globalSNMPInfo->public, _globalSNMPInfo->trapmode, _globalSNMPInfo->crypt.user, _globalSNMPInfo->crypt.auth.alg, _globalSNMPInfo->crypt.auth.passwd, _globalSNMPInfo->crypt.priv.alg, _globalSNMPInfo->crypt.priv.passwd, product_id); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); return 0; } /// @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) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); dev_sql_VACCUM(db); 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 = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); return 0; } int dev_get_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, "SELECT* from Table_GroupManage WHERE product_id = %d;", __globalDeviceManage._globalDevInfo.product.id); res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } //提取数据 if(nrow==0) { sqlite3_free_table(pResult); return -2 ; } for(int i = 1; i <= nrow; i++) { // 获取ID GroupInfo *new_group = (GroupInfo *)malloc(sizeof(GroupInfo)); // 清空数据 memset(new_group, 0, sizeof(GroupInfo)); if (!new_group) { break; } new_group->product_id = __globalDeviceManage._globalDevInfo.product.id; new_group->GroupID = atoi(pResult[i*ncolumn+1]); strcpy(new_group->GroupName,pResult[i*ncolumn+2]); strcpy(new_group->GroupChList,pResult[i*ncolumn+3]); list_add_tail(&new_group->list, &_globalgroupInfo->list); } sqlite3_free_table(pResult); return 0; } int dev_update_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}; if (_globalgroupInfo == NULL) { return -1; } sprintf(select_sql, "UPDATE Table_GroupManage SET groupName='%s' WHERE groupID=%d AND product_id=%d;",_globalgroupInfo->GroupName,_globalgroupInfo->GroupID, __globalDeviceManage._globalDevInfo.product.id); res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); return 0; } ///用户管理 int dev_update_user_info(sqlite3 *db, UserInfo* _globaluserInfo) { 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_Userinfo SET userName='%s',userPW='%s',nickName='%s',roleID=%d WHERE userID=%d AND product_id=%d;", _globaluserInfo->userName, _globaluserInfo->userPW, _globaluserInfo->nickName, _globaluserInfo->roleID, _globaluserInfo->userID, _globaluserInfo->product_id); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); return 0; } int dev_insert_user_info(sqlite3 *db,UserInfo* _globaluserInfo) { 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_Userinfo (product_id, userID, userName, userPW, nickName, roleID, createTime) VALUES (%d,%d,'%s','%s','%s',%d,'%s');", _globaluserInfo->product_id, _globaluserInfo->userID, _globaluserInfo->userName, _globaluserInfo->userPW, _globaluserInfo->nickName, _globaluserInfo->roleID, _globaluserInfo->createTime ); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); return 0; } int dev_get_user_info(sqlite3 *db, UserInfo* _globaluserInfo) { 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_Userinfo WHERE product_id = %d;", __globalDeviceManage._globalDevInfo.product.id); res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } //提取数据 if(nrow==0) { sqlite3_free_table(pResult); return -2 ; } for(int i = 1; i <= nrow; i++) { // 获取ID UserInfo *new_user = (UserInfo *)malloc(sizeof(UserInfo)); // 清空数据 memset(new_user, 0, sizeof(UserInfo)); if (!new_user) { break; } new_user->product_id = __globalDeviceManage._globalDevInfo.product.id; new_user->userID = atoi(pResult[i*ncolumn+1]); new_user->userName = malloc(256); strcpy(new_user->userName,pResult[i*ncolumn+2]); new_user->userPW = malloc(256); strcpy(new_user->userPW,pResult[i*ncolumn+3]); new_user->nickName = malloc(256); strcpy(new_user->nickName,pResult[i*ncolumn+4]); new_user->roleID = atoi(pResult[i*ncolumn+5]); new_user->createTime = malloc(64); strcpy(new_user->createTime,pResult[i*ncolumn+6]); list_add_tail(&new_user->list, &_globaluserInfo->list); } sqlite3_free_table(pResult); return 0; } /// @brief 删除用户信息 int dev_delete_user_info(sqlite3 *db,int userID) { 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_Userinfo WHERE userID=%d;", userID ); res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); dev_sql_VACCUM(db); return 0; } ///角色管理 int dev_update_role_info(sqlite3 *db, RoleInfo* _globalRoleInfo) { 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_RoleInfo SET roleName='%s',roleLevel=%d,roleAuthority='%s',roleSort=%d,WHERE roleID=%d AND product_id=%d;", _globalRoleInfo->roleName, _globalRoleInfo->roleLevel, _globalRoleInfo->roleAuthority, _globalRoleInfo->roleSort, _globalRoleInfo->roleID, _globalRoleInfo->product_id ); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); return 0; } int dev_insert_role_info(sqlite3 *db,RoleInfo* _globalRoleInfo) { 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_RoleInfo (product_id, roleID, roleName, roleLevel, roleAuthority, roleSort, createTime) VALUES (%d,%d,'%s',%d,'%s',%d,'%s');", _globalRoleInfo->product_id, _globalRoleInfo->roleID, _globalRoleInfo->roleName, _globalRoleInfo->roleLevel, _globalRoleInfo->roleAuthority, _globalRoleInfo->roleSort, _globalRoleInfo->createTime ); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); return 0; } int dev_get_role_info(sqlite3 *db, RoleInfo* _globalRoleInfo) { 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_RoleInfo WHERE product_id = %d;", __globalDeviceManage._globalDevInfo.product.id); res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } //提取数据 if(nrow==0) { sqlite3_free_table(pResult); return -2 ; } for(int i = 1; i <= nrow; i++) { // 获取ID RoleInfo *new_role = (RoleInfo *)malloc(sizeof(RoleInfo)); // 清空数据 memset(new_role, 0, sizeof(RoleInfo)); if (!new_role) { break; } new_role->product_id = __globalDeviceManage._globalDevInfo.product.id; new_role->roleID = atoi(pResult[i*ncolumn+1]); new_role->roleName = malloc(256); strcpy(new_role->roleName,pResult[i*ncolumn+2]); new_role->roleLevel = atoi(pResult[i*ncolumn+3]); new_role->roleAuthority = malloc(256); strcpy(new_role->roleAuthority,pResult[i*ncolumn+4]); new_role->roleSort = atoi(pResult[i*ncolumn+5]); new_role->createTime = malloc(64); strcpy(new_role->createTime,pResult[i*ncolumn+6]); list_add_tail(&new_role->list, &_globalRoleInfo->list); } sqlite3_free_table(pResult); return 0; } //定时器设置 int dev_get_power_ds(sqlite3 *db,GlobalPowerManger* _powerAll,char* dateSelect) { 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}; GlobalPowerManger* _globalPowerMangerTemp=NULL; list_for_each_entry(_globalPowerMangerTemp, &_powerAll->list, list) { INIT_LIST_HEAD(&_globalPowerMangerTemp->list_DS); } if (dateSelect) { sprintf(select_sql, "SELECT* from Table_ChnTimerManage WHERE product_id = %d and date=date('%s');", __globalDeviceManage._globalDevInfo.product.id,dateSelect); }else{ sprintf(select_sql, "SELECT* from Table_ChnTimerManage WHERE product_id = %d;", 1); } res = sqlite3_get_table(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } if(nrow==0) { sqlite3_free_table(pResult); return -2 ; } for(int i = 1; i <= nrow; i++) { // 获取ID _PowerDSManage_t *new_data = (_PowerDSManage_t *)malloc(sizeof(_PowerDSManage_t)); // 清空数据 if (!new_data) { break; } memset(new_data, 0, sizeof(_PowerDSManage_t)); int nProduct_id = atoi(pResult[i * ncolumn + 0]); int nProduct_chn = atoi(pResult[i * ncolumn + 1]); strcpy(new_data->ds_date, pResult[i * ncolumn + 2]); strcpy(new_data->ds_time, pResult[i * ncolumn + 3]); new_data->set_stats = atoi(pResult[i * ncolumn + 4]); new_data->ds_is_triggered = atoi(pResult[i * ncolumn + 5]); list_for_each_entry(_globalPowerMangerTemp,&_powerAll->list, list) { if (_globalPowerMangerTemp->product_ch_id==nProduct_chn) { list_add_tail(&new_data->list, &_globalPowerMangerTemp->list_DS); break; } } } sqlite3_free_table(pResult); return 0; } int dev_update_power_ds(sqlite3 *db,int product_id,int nChn,_PowerDSManage_t* _powerDS) { int res ; char select_sql[512] = {0}; char *errorMsg = NULL; int nrow = 0; //满足条件的记录数目 int ncolumn = 0; //每条记录包含的字段数据 char** pResult = NULL; //用来指向sql执行结果的指针 char* err_msg=NULL; if (!_powerDS) { return -1; } sprintf(select_sql, "UPDATE Table_ChnTimerManage SET status = %d,is_triggered=%d WHERE product_id=%d and product_ch_id=%d and date=date('%s') and time=time('%s');", _powerDS->set_stats, _powerDS->ds_is_triggered, product_id, nChn, _powerDS->ds_date, _powerDS->ds_time ); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); return 0; } int dev_insert_power_ds(sqlite3 *db,int product_id,int nChn,_PowerDSManage_t* _powerDS) { int res ; char select_sql[512] = {0}; char *errorMsg = NULL; int nrow = 0; //满足条件的记录数目 int ncolumn = 0; //每条记录包含的字段数据 char** pResult = NULL; //用来指向sql执行结果的指针 char* err_msg=NULL; if (!_powerDS) { return -1; } sprintf(select_sql,"INSERT INTO Table_ChnTimerManage VALUES (%d,%d,date('%s'),time('%s'),%d,%d);", product_id, nChn, _powerDS->ds_date, _powerDS->ds_time, _powerDS->set_stats, _powerDS->ds_is_triggered ); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); return 0; } int dev_delete_power_ds(sqlite3 *db,int product_id,int nChn,_PowerDSManage_t* _powerDS) { int res; char select_sql[512] = {0}; char *errorMsg = NULL; int nrow = 0; // 满足条件的记录数目 int ncolumn = 0; // 每条记录包含的字段数据 char **pResult = NULL; // 用来指向sql执行结果的指针 char *err_msg = NULL; if (!_powerDS) { return -1; } sprintf(select_sql, "DELETE FROM Table_ChnTimerManage WHERE product_id=%d and product_ch_id=%d and date=date('%s') and time=time('%s');", product_id, nChn, _powerDS->ds_date, _powerDS->ds_time); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if (res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1; } sqlite3_free_table(pResult); dev_sql_VACCUM(db); return 0; } void get_message_CtrlType(int nCtrlType,char* message) { switch (nCtrlType) { case 1: sprintf(message, "|$通道$|$关闭$"); break; default: break; } } /** * @brief 发送设备告警信息 * * 根据指定的告警类型，构建告警信息并发送。 * * @param globalDeviceManger 全局设备管理器指针 * @param __globalPowerTemp 全局电源管理器指针 * @param _AlarmType 告警类型 * * @return 返回值为0表示发送成功，非0表示发送失败 */ int dev_Alarm_send_message(GlobalDeviceManager* globalDeviceManger,GlobalPowerManger* __globalPowerTemp,int _AlarmType) { char AlarmText[256]; int languagesID = 0; struct tm *t; char CtrlData[64]; int alarm_action=-1; memset(CtrlData, 0, sizeof(CtrlData)); switch (_AlarmType) { case WARNING_V_UP: { alarm_action = __globalPowerTemp->global_over_manager->product_vol_over_upper_threshold_ctrl; get_message_CtrlType(alarm_action,CtrlData); sprintf(AlarmText,"$通道$|%s |$%s$|$%s$| %s",__globalPowerTemp->product_ch_name,language_alarm_V[languagesID],language_alarm_Up[languagesID],CtrlData); } break; case WARNING_V_DOWN: { alarm_action = __globalPowerTemp->global_over_manager->product_vol_over_lower_threshold_ctrl; get_message_CtrlType(alarm_action,CtrlData); sprintf(AlarmText,"$通道$|%s |$%s$|$%s$| %s",__globalPowerTemp->product_ch_name,language_alarm_V[languagesID],language_alarm_Down[languagesID],CtrlData); } break; case WARNING_A_UP: { alarm_action = __globalPowerTemp->global_over_manager->product_cur_over_upper_threshold_ctrl; get_message_CtrlType(alarm_action,CtrlData); sprintf(AlarmText,"$通道$|%s |$%s$|$%s$| %s",__globalPowerTemp->product_ch_name,language_alarm_A[languagesID],language_alarm_Up[languagesID],CtrlData); } break; case WARNING_W_UP: { alarm_action = __globalPowerTemp->global_over_manager->product_pwr_over_upper_threshold_ctrl; get_message_CtrlType(alarm_action,CtrlData); sprintf(AlarmText,"$通道$|%s |$%s$|$%s$| %s",__globalPowerTemp->product_ch_name,language_alarm_W[languagesID],language_alarm_Up[languagesID],CtrlData); } break; case WARNING_P_UP: { alarm_action = __globalPowerTemp->global_over_manager->product_pwrcon_over_upper_threshold_ctrl; get_message_CtrlType(alarm_action,CtrlData); sprintf(AlarmText,"$通道$|%s |$%s$|$%s$| %s",__globalPowerTemp->product_ch_name,language_alarm_P[languagesID],language_alarm_Up[languagesID],CtrlData); } break; case 0: { sprintf(AlarmText, "$通道$|%d |$%s$", __globalPowerTemp->product_ch_id, language_alarm_phaseA_lose[languagesID]); } break; case 1: { sprintf(AlarmText, "$通道$|%d |$%s$", __globalPowerTemp->product_ch_id, language_alarm_phaseB_lose[languagesID]); } break; case 2: { sprintf(AlarmText, "$通道$|%d |$%s$", __globalPowerTemp->product_ch_id, language_alarm_phaseC_lose[languagesID]); } break; default: sprintf(AlarmText,"$通道$|%s |$%s$",__globalPowerTemp->product_ch_name,language_alarm_NULL_ERROR[languagesID]); break; } 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(globalDeviceManger->db,__globalPowerTemp->product_id,0,buffer,AlarmText); smtp_post_alarm(ALARM_TYPE_POWER, 0, _AlarmType, AlarmText); alarm_para_t para; //dev_search_last_Index(globalDeviceManger->db, ); para.id = __globalPowerTemp->product_ch_id; para.nalarm = 1; para.action = alarm_action; para.actionId = -1; mqtt_post_alarm(ALARM_TYPE_POWER, 0, AlarmText, ¶); /* int ret = -1 ; int nchID = __globalPowerTemp->product_ch_id; if (__globalPowerTemp->global_over_manager) { switch (_AlarmType) { case WARNING_V_UP: { if (__globalPowerTemp->global_over_manager->product_vol_over_upper_threshold_ctrl == ALARM_CTRL_CLOSE_CH) { ret = g_switch_set_all_chn_ctrl(&globalDeviceManger->_globalRelaySampManger, __globalPowerTemp, __globalPowerTemp->product_saddr, __globalPowerTemp->product_ch_addr, // 多版端口余8 0, false); } } break; case WARNING_V_DOWN: { if (__globalPowerTemp->global_over_manager->product_vol_over_lower_threshold_ctrl == ALARM_CTRL_CLOSE_CH) { ret = g_switch_set_all_chn_ctrl(&globalDeviceManger->_globalRelaySampManger, __globalPowerTemp, __globalPowerTemp->product_saddr, __globalPowerTemp->product_ch_addr, // 多版端口余8 0, false); } } break; case WARNING_A_UP: { if (__globalPowerTemp->global_over_manager->product_cur_over_upper_threshold_ctrl == ALARM_CTRL_CLOSE_CH) { ret = g_switch_set_all_chn_ctrl(&globalDeviceManger->_globalRelaySampManger, __globalPowerTemp, __globalPowerTemp->product_saddr, __globalPowerTemp->product_ch_addr, // 多版端口余8 0, false); } } break; case WARNING_W_UP: { if (__globalPowerTemp->global_over_manager->product_pwr_over_upper_threshold_ctrl == ALARM_CTRL_CLOSE_CH) { ret = g_switch_set_all_chn_ctrl(&globalDeviceManger->_globalRelaySampManger, __globalPowerTemp, __globalPowerTemp->product_saddr, __globalPowerTemp->product_ch_addr, // 多版端口余8 0, false); } } break; case WARNING_P_UP: { if (__globalPowerTemp->global_over_manager->product_pwrcon_over_upper_threshold_ctrl == ALARM_CTRL_CLOSE_CH) { ret = g_switch_set_all_chn_ctrl(&globalDeviceManger->_globalRelaySampManger, __globalPowerTemp, __globalPowerTemp->product_saddr, __globalPowerTemp->product_ch_addr, // 多版端口余8 0, false); } } break; default: break; } }*/ return 0; } /** * @brief 发送 AC 设备告警信息 * * 根据给定的告警类型，生成对应的告警文本，并将其插入到告警信息数据库中。 * * @param globalDeviceManger 全局设备管理器指针 * @param __globalPowerTemp 全局 AC 管理器指针 * @param _AlarmType 告警类型 * * @return 返回值为 0 表示成功，其他值表示失败 */ int dev_Alarm_T_AC_message(GlobalDeviceManager* globalDeviceManger, GlobalTreeACManager* __globalPowerTemp,int _AlarmType) { char AlarmText[256]; int languagesID=0; struct tm *t; char CtrlData[64]; int alarm_action=-1; memset(CtrlData,0,sizeof(CtrlData)); switch (_AlarmType) { case ENABLE_TAC_V_UP: { alarm_action = __globalPowerTemp->global_over_manager->product_vol_over_upper_threshold_ctrl; get_message_CtrlType(alarm_action,CtrlData); sprintf(AlarmText, "$通道$|CH%d%c |$%s$|$%s$| %s", __globalPowerTemp->product_ch_id, 'A' + __globalPowerTemp->product_ph_type, language_alarm_V[languagesID], language_alarm_Up[languagesID],CtrlData); } break; case ENABLE_TAC_V_DOWN: { alarm_action = __globalPowerTemp->global_over_manager->product_vol_over_lower_threshold_ctrl; get_message_CtrlType(alarm_action,CtrlData); sprintf(AlarmText, "$通道$|CH%d%c |$%s$|$%s$| %s", __globalPowerTemp->product_ch_id, 'A' + __globalPowerTemp->product_ph_type, language_alarm_V[languagesID], language_alarm_Down[languagesID],CtrlData); } break; case ENABLE_TAC_A_UP: { alarm_action = __globalPowerTemp->global_over_manager->product_cur_over_upper_threshold_ctrl; get_message_CtrlType(alarm_action,CtrlData); sprintf(AlarmText, "$通道$|CH%d%c |$%s$|$%s$| %s", __globalPowerTemp->product_ch_id, 'A' + __globalPowerTemp->product_ph_type, language_alarm_A[languagesID], language_alarm_Up[languagesID],CtrlData); } break; case ENABLE_TAC_W_UP: { alarm_action = __globalPowerTemp->global_over_manager->product_pwr_over_upper_threshold_ctrl; get_message_CtrlType(alarm_action,CtrlData); sprintf(AlarmText, "$通道$|CH%d%c |$%s$|$%s$| %s", __globalPowerTemp->product_ch_id, 'A' + __globalPowerTemp->product_ph_type, language_alarm_W[languagesID], language_alarm_Up[languagesID],CtrlData); } break; case ENABLE_TAC_P_UP: { alarm_action = __globalPowerTemp->global_over_manager->product_pwrcon_over_upper_threshold_ctrl; get_message_CtrlType(alarm_action,CtrlData); sprintf(AlarmText, "$通道$|CH%d%c |$%s$|$%s$| %s", __globalPowerTemp->product_ch_id, 'A' + __globalPowerTemp->product_ph_type, language_alarm_P[languagesID], language_alarm_Up[languagesID],CtrlData); } break; case ENABLE_TAC_STIME: { if ( __globalPowerTemp->product_ph_type==0) { sprintf(AlarmText, "$通道$|CH%d |$%s$", __globalPowerTemp->product_ch_id, language_alarm_phaseA_lose[languagesID]); } else if (__globalPowerTemp->product_ph_type==1) { sprintf(AlarmText, "$通道$|CH%d |$%s$", __globalPowerTemp->product_ch_id, language_alarm_phaseB_lose[languagesID]); } else if (__globalPowerTemp->product_ph_type==2) { sprintf(AlarmText, "$通道$|CH%d |$%s$", __globalPowerTemp->product_ch_id, language_alarm_phaseC_lose[languagesID]); } } break; case ENABLE_TAC_ETIME: default: sprintf(AlarmText, "$通道$|CH%d%c |$%s$", __globalPowerTemp->product_ch_id, 'A' + __globalPowerTemp->product_ph_type, language_alarm_NULL_ERROR[languagesID]); break; } 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(globalDeviceManger->db,__globalPowerTemp->product_id,0,buffer,AlarmText); smtp_post_alarm(ALARM_TYPE_POWER, 1, _AlarmType, AlarmText); alarm_para_t para; para.id = __globalPowerTemp->product_ch_id; para.nalarm = 1; para.action = alarm_action; para.actionId = -1; mqtt_post_alarm(ALARM_TYPE_POWER, 1, AlarmText, ¶); return 0; } /** * @brief 发送传感器告警信息 * * 根据给定的全局设备管理器、全局传感器管理器和告警类型，发送传感器告警信息。 * * @param globalDeviceManger 全局设备管理器指针 * @param __globalPowerTemp 全局传感器管理器指针 * @param _AlarmType 告警类型 * * @return 成功返回0，失败返回-1 */ int dev_sensor_Alarm_send_message(GlobalDeviceManager* globalDeviceManger,GlobalSensorManger* __globalPowerTemp,int _AlarmType) { char AlarmText[256]; int languagesID=0; struct tm* t ; if (!__globalPowerTemp) { return -1; } switch (__globalPowerTemp->sensor_type) { case SENSOR_TYPE_TEMPERATURE: { switch (_AlarmType) { case SENSOR_VAL1_UPPER: sprintf(AlarmText, "%s |$温度$|$%s$", __globalPowerTemp->sensor_name, language_alarm_Up[languagesID]); break; case SENSOR_VAL1_LOWER: sprintf(AlarmText, "%s |$温度$|$%s$", __globalPowerTemp->sensor_name, language_alarm_Down[languagesID]); break; case SENSOR_VAL2_UPPER: sprintf(AlarmText, "%s |$湿度$|$%s$", __globalPowerTemp->sensor_name, language_alarm_Up[languagesID]); break; case SENSOR_VAL2_LOWER: sprintf(AlarmText, "%s |$湿度$|$%s$", __globalPowerTemp->sensor_name, language_alarm_Down[languagesID]); break; default: sprintf(AlarmText, "%s |$%s$", __globalPowerTemp->sensor_name, language_alarm_NULL_ERROR[languagesID]); break; } } break; case SENSOR_TYPE_DoubleT: { switch (_AlarmType) { case SENSOR_VAL1_UPPER: sprintf(AlarmText, "%s |$温度$|1|$%s$", __globalPowerTemp->sensor_name, language_alarm_Up[languagesID]); break; case SENSOR_VAL1_LOWER: sprintf(AlarmText, "%s |$温度$|1|$%s$", __globalPowerTemp->sensor_name, language_alarm_Down[languagesID]); break; case SENSOR_VAL2_UPPER: sprintf(AlarmText, "%s |$温度$|2|$%s$", __globalPowerTemp->sensor_name, language_alarm_Up[languagesID]); break; case SENSOR_VAL2_LOWER: sprintf(AlarmText, "%s |$温度$|2|$%s$", __globalPowerTemp->sensor_name, language_alarm_Down[languagesID]); break; default: sprintf(AlarmText, "%s |$%s$", __globalPowerTemp->sensor_name, language_alarm_NULL_ERROR[languagesID]); break; } } break; default: sprintf(AlarmText,"%s |$%s$",__globalPowerTemp->sensor_name,language_alarm_NULL_ERROR[languagesID]); break; } 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(globalDeviceManger->db,__globalPowerTemp->product_id,1,buffer,AlarmText); int subtype=__globalPowerTemp->sensor_type; smtp_post_alarm(ALARM_TYPE_SENSOR, subtype, _AlarmType, AlarmText); alarm_para_t para; para.id = __globalPowerTemp->sensor_id; para.nalarm = 1; para.action = -1; para.actionId = -1; mqtt_post_alarm(ALARM_TYPE_POWER, subtype, AlarmText, ¶); return 0; } int dev_Alarm_Run_message(GlobalDeviceManager* globalDeviceManger, const char* strRunMSG, const char *strOther) { char AlarmText[256]; int languagesID=0; struct tm* t ; sprintf(AlarmText,"$%s$| %s ",strRunMSG,strOther); 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(globalDeviceManger->db,0,4,buffer,AlarmText); return 0; } /// @brief //////////////////////////////////////////////////////////// #define SWITCH_TABLE "Table_SwitchInfo" static int tab_exists(sqlite3 *db, char *table) { int r,exists = 0; sqlite3_stmt *stmt; char sql[1024]; snprintf(sql, sizeof(sql), "SELECT name FROM sqlite_master WHERE type='table' AND name='%s';", table); r = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL); if (r == SQLITE_OK) { r = sqlite3_step(stmt); if (r == SQLITE_ROW) { exists = 1; } else if (r == SQLITE_DONE) { exists = 0; } else { log_e("Error: %s\n", sqlite3_errmsg(db)); } sqlite3_finalize(stmt); } else { log_e("Error: %s\n", sqlite3_errmsg(db)); } return exists; } static int sw_load(sqlite3 *db, netswitch_info_t *sw) { int i=0,r; char temp[1024],*p; char *table=SWITCH_TABLE; sqlite3_stmt *stmt=NULL; sprintf(temp, "SELECT * FROM %s;", table); #if 0 r = sqlite3_prepare_v2(db, temp, -1, &stmt, 0); if (r != SQLITE_OK) { log_e("___switch_load, sqlite3_prepare_v2 failed\n"); return -1; } memset(sw, 0, sizeof(netswitch_info_t)); while (sqlite3_step(stmt) == SQLITE_ROW) { port_info_t *pt=&sw->port[i]; pt->id = sqlite3_column_int(stmt, 0); pt->up = sqlite3_column_int(stmt, 1); p = (char*)sqlite3_column_text(stmt, 2); if(p) strcpy(pt->ip, p); p = (char*)sqlite3_column_text(stmt, 3); if(p) strcpy(pt->mac, p); p = (char*)sqlite3_column_text(stmt, 4); if(p) strcpy(pt->desc, p); i++; } sqlite3_finalize(stmt); #else int nrow = 0; //满足条件的记录数目 int ncol = 0; //每条记录包含的字段数据 char** pResult = NULL; //用来指向sql执行结果的指针 char *err_msg=NULL; r = sqlite3_get_table(db, temp, &pResult, &nrow, &ncol, &err_msg); if(r != SQLITE_OK) { log_e("sqlite handle error: %s \n%s\n", temp, err_msg); sqlite3_free(err_msg); return -1 ; } for(i=1; i<=nrow; i++) { port_info_t *pt=&sw->port[i-1]; pt->id = atoi(pResult[i*ncol+0]); pt->up = atoi(pResult[i*ncol+1]); strcpy(pt->ip, pResult[i*ncol+2]); strcpy(pt->mac, pResult[i*ncol+3]); strcpy(pt->desc, pResult[i*ncol+4]); } sqlite3_free_table(pResult); #endif return 0; } static int sw_update_port(sqlite3 *db, port_info_t *pt) { int i,r=0; char temp[1024]; sqlite3_stmt *stmt; char *err_msg=NULL; char *table=SWITCH_TABLE; sprintf(temp, "UPDATE %s SET up=%d,ip='%s',mac='%s',desc='%s' WHERE id=%d;", table,pt->up,pt->ip,pt->mac,pt->desc,pt->id); r = sqlite3_exec(db, temp, NULL, 0, &err_msg); if(r != SQLITE_OK) { log_e("___sw_update_port failed, %s, %s\n\n", temp, err_msg); sqlite3_free(err_msg); return -1; } return 0; } static int sw_update_all(sqlite3 *db, netswitch_info_t *sw) { int i,r=0; char temp[1024]; sqlite3_stmt *stmt; char *table=SWITCH_TABLE; sprintf(temp, "REPLACE INTO %s (id,up,ip,mac,desc) VALUES (?,?,?,?,?);", table); r = sqlite3_prepare_v2(db, temp, -1, &stmt, 0); if (r != SQLITE_OK) { log_e("___sw_update_all, sqlite3_prepare_v2 failed\n"); return -1; } for(i=0; iport[i]; pt->id = i; sqlite3_bind_int(stmt, 1, pt->id); sqlite3_bind_int(stmt, 2, pt->up); sqlite3_bind_text(stmt, 3, pt->ip, -1, NULL); sqlite3_bind_text(stmt, 4, pt->mac, -1, NULL); sqlite3_bind_text(stmt, 5, pt->desc, -1, NULL); r = sqlite3_step(stmt); if (r != SQLITE_DONE) { log_e("___sw_update_all, sqlite3_step port %d failed\n", i); r = -1; break; } sqlite3_reset(stmt); } sqlite3_finalize(stmt); return r; } int dev_switch_init(sqlite3 *db, netswitch_info_t *sw) { int r=-1; #ifdef USE_NETSWITCH char *table=SWITCH_TABLE; if(!tab_exists(db, table)) { char temp[1024] = {0}; char *err_msg = NULL; sprintf(temp, "CREATE TABLE IF NOT EXISTS %s(" "id INTEGER PRIMARY KEY," "up INTEGER," "ip TEXT," "mac TEXT," "desc TEXT);", table); r = sqlite3_exec(db, temp, 0, 0, &err_msg); if(r!=SQLITE_OK) { log_e("___ %s create failed, %s\n%s\n", table, temp, err_msg); sqlite3_free(err_msg); return -1; } memset(sw, 0, sizeof(netswitch_info_t)); sw_update_all(db, sw); } r = sw_load(db, sw); #endif return r; } int dev_switch_update_port(sqlite3 *db, port_info_t *pt) { int r=-1; #ifdef USE_NETSWITCH pthread_mutex_lock(&__globalDeviceManage.dbThreadlock); r = sw_update_port(db, pt); pthread_mutex_unlock(&__globalDeviceManage.dbThreadlock); #endif return r; } int dev_switch_update_all(sqlite3 *db, netswitch_info_t *sw) { int r=-1; #ifdef USE_NETSWITCH pthread_mutex_lock(&__globalDeviceManage.dbThreadlock); r = sw_update_all(db, sw); pthread_mutex_unlock(&__globalDeviceManage.dbThreadlock); #endif return r; } ////////////////smtp///////////////////////////////////////////// #define SMTP_MANAGER_TABLE "Table_SMTPManage" #define SMTP_RECVEIVE_TABLE "Table_SMTPReceive" int dev_smtp_init(sqlite3 *db, smtp_info_t *info) { int i,r=-1; #ifdef USE_SMTP char temp[1024]; char *err_msg=NULL; char *table=NULL; int nrow,ncol; char** pResult = NULL; table = SMTP_MANAGER_TABLE; if(!tab_exists(db, table)) { sprintf(temp, "CREATE TABLE IF NOT EXISTS %s(" "id INTEGER PRIMARY KEY," "mode INTEGER," "account TEXT," "password TEXT," "server TEXT," "port TEXT," "auth TEXT);", table); r = sqlite3_exec(db, temp, 0, 0, &err_msg); if(r!=SQLITE_OK) { log_e("___ %s create failed, %s\n%s\n", table, temp, err_msg); sqlite3_free(err_msg); return -1; } #ifdef SMTP_SEND_BUILTIN extern smtp_send_t DFLT_SMTP_SEND; dev_smtp_update_send(db, &DFLT_SMTP_SEND); #endif } if(info) { memset(info, 0, sizeof(smtp_info_t)); } nrow = ncol = 0; sprintf(temp, "SELECT * FROM %s;", table); r = sqlite3_get_table(db, temp, &pResult, &nrow, &ncol, &err_msg); if(r != SQLITE_OK) { log_e("sqlite handle error: %s \n%s\n", temp, err_msg); sqlite3_free(err_msg); return -1 ; } if(info && ncol>0) { smtp_send_t *send=&info->send; memset(send, 0, sizeof(smtp_send_t)); send->id = atoi(pResult[ncol+0]); send->mode = atoi(pResult[ncol+1]); strcpy(send->account, pResult[ncol+2]); strcpy(send->password, pResult[ncol+3]); strcpy(send->server, pResult[ncol+4]); strcpy(send->port, pResult[ncol+5]); strcpy(send->auth, pResult[ncol+6]); } //////////////////////////////////////////////////////////////////////// table = SMTP_RECVEIVE_TABLE; if(!tab_exists(db, table)) { sprintf(temp, "CREATE TABLE IF NOT EXISTS %s(" "idx INTEGER PRIMARY KEY," "use INTEGER," "account TEXT);", table); r = sqlite3_exec(db, temp, 0, 0, &err_msg); if(r!=SQLITE_OK) { log_e("___ %s create failed, %s\n%s\n", table, temp, err_msg); sqlite3_free(err_msg); return -1; } } nrow = ncol = 0; sprintf(temp, "SELECT * FROM %s;", table); r = sqlite3_get_table(db, temp, &pResult, &nrow, &ncol, &err_msg); if(r != SQLITE_OK) { log_e("sqlite handle error: %s \n%s\n", temp, err_msg); sqlite3_free(err_msg); return -1 ; } if(info && ncol>0) { smtp_recv_t *recv=info->recv; for(i=1; i<=nrow; i++) { recv[i-1].idx = atoi(pResult[i*ncol+0]); recv[i-1].use = atoi(pResult[i*ncol+1]); strcpy(recv[i-1].account, pResult[i*ncol+2]); } } sqlite3_free_table(pResult); #endif return 0; } int dev_smtp_update_send(sqlite3 *db, smtp_send_t *send) { int i,r=-1,exist=0,cnt=0; #ifdef USE_SMTP char temp[1024]; sqlite3_stmt *stmt; char *err_msg=NULL; char *table=SMTP_MANAGER_TABLE; cnt = get_count(db, table); if(cnt>0) { sprintf(temp, "UPDATE %s SET id=%d, mode=%d, account='%s', password='%s', server='%s', port='%s', auth='%s' WHERE id=%d;", table, send->id, send->mode, send->account, send->password, send->server, send->port, send->auth, send->id); r = sqlite3_exec(db, temp, NULL, 0, &err_msg); if(r != SQLITE_OK) { log_e("___sw_update_port failed, %s, %s\n\n", temp, err_msg); sqlite3_free(err_msg); } else { exist = sqlite3_changes(db); } } if(exist==0) { sprintf(temp, "INSERT INTO %s (id,mode,account,password,server,port,auth) VALUES (?,?,?,?,?,?,?);", table); r = sqlite3_prepare_v2(db, temp, -1, &stmt, 0); if (r != SQLITE_OK) { log_e("___dev_smtp_update_man, sqlite3_prepare_v2 failed, %s\n", sqlite3_errmsg(db)); r = -1; goto quit; } sqlite3_bind_int(stmt, 1, send->id); sqlite3_bind_int(stmt, 2, send->mode); sqlite3_bind_text(stmt, 3, send->account, -1, NULL); sqlite3_bind_text(stmt, 4, send->password, -1, NULL); sqlite3_bind_text(stmt, 5, send->server, -1, NULL); sqlite3_bind_text(stmt, 6, send->port, -1, NULL); sqlite3_bind_text(stmt, 7, send->auth, -1, NULL); r = sqlite3_step(stmt); sqlite3_finalize(stmt); if (r != SQLITE_DONE) { log_e("___sw_update_all, sqlite3_step failed\n"); r = -1; } else { r = 0; } } quit: #endif return r; } int dev_smtp_update_recv(sqlite3 *db, smtp_recv_t *recv) { int i,r=-1,exist=0,cnt=0; #ifdef USE_SMTP char temp[1024]; sqlite3_stmt *stmt; char *err_msg=NULL; char *table=SMTP_RECVEIVE_TABLE; cnt = get_count(db, table); if(cnt>0) { sprintf(temp, "UPDATE %s SET idx=%d, use=%d, account='%s' WHERE idx=%d;", table, recv->idx, recv->use, recv->account, recv->idx); r = sqlite3_exec(db, temp, NULL, 0, &err_msg); if(r != SQLITE_OK) { log_e("___sw_update_port failed, %s, %s\n\n", temp, err_msg); sqlite3_free(err_msg); } else { exist = sqlite3_changes(db); } } if(exist==0) { sprintf(temp, "INSERT INTO %s (idx,use,account) VALUES (?,?,?);", table); r = sqlite3_prepare_v2(db, temp, -1, &stmt, 0); if (r != SQLITE_OK) { log_e("___dev_smtp_update_recv, sqlite3_prepare_v2 failed, %s\n", sqlite3_errmsg(db)); r = -1; goto quit; } sqlite3_bind_int(stmt, 1, recv->idx); sqlite3_bind_int(stmt, 2, recv->use); sqlite3_bind_text(stmt, 3, recv->account, -1, NULL); r = sqlite3_step(stmt); sqlite3_finalize(stmt); if (r != SQLITE_DONE) { log_e("___sw_update_all, sqlite3_step failed\n"); r = -1; } } quit: #endif return r; } ////////////////mqtt///////////////////////////////////////////// #define MQTT_MANAGER_TABLE "Table_MQTTManage" int dev_mqtt_init(sqlite3 *db, mqtt_info_t *info) { int i,r; #ifdef USE_MQTT int idx=0; char temp[1024]; char *errmsg=NULL; char *table=NULL; char** pResult=NULL; int nrow,ncol; sqlite3_stmt *stmt=NULL; mqtt_server_t *ser=info->ser; table = MQTT_MANAGER_TABLE; if(!tab_exists(db, table)) { sprintf(temp, "CREATE TABLE IF NOT EXISTS %s(" "id INTEGER PRIMARY KEY," "mode INTEGER," "name TEXT," "server TEXT," "port TEXT," "cid TEXT," "user TEXT," "password TEXT," "cert TEXT);", table); r = sqlite3_exec(db, temp, 0, 0, &errmsg); if(r!=SQLITE_OK) { log_e("___ %s create failed, %s\n%s\n", table, temp, errmsg); sqlite3_free(errmsg); return -1; } #ifdef MQTT_SERVER_BUILTIN extern mqtt_info_t DFLT_MQTT; dev_mqtt_update_info(db, &DFLT_MQTT); #endif } sprintf(temp, "SELECT * FROM %s;", table); r = sqlite3_prepare_v2(db, temp, -1, &stmt, 0); if (r != SQLITE_OK) { log_e("___mqtt_load, sqlite3_prepare_v2 failed\n"); return -1; } memset(info, 0, sizeof(mqtt_info_t)); char *p=NULL; while (sqlite3_step(stmt) == SQLITE_ROW) { ser[idx].id = sqlite3_column_int(stmt, 0); ser[idx].mode = sqlite3_column_int(stmt, 1); p = (char*)sqlite3_column_text(stmt, 2); if(p) strcpy(ser[idx].name, p); p = (char*)sqlite3_column_text(stmt, 3); if(p) strcpy(ser[idx].server, p); p = (char*)sqlite3_column_text(stmt, 4); if(p) strcpy(ser[idx].port, p); p = (char*)sqlite3_column_text(stmt, 5); if(p) strcpy(ser[idx].cid, p); p = (char*)sqlite3_column_text(stmt, 6); if(p) strcpy(ser[idx].user, p); p = (char*)sqlite3_column_text(stmt, 7); if(p) strcpy(ser[idx].password, p); p = (char*)sqlite3_column_text(stmt, 8); if(p) strcpy(ser[idx].cert, p); idx++; if(idx>=MQTT_SER_MAX) { break; } } sqlite3_finalize(stmt); #endif return 0; } int dev_mqtt_update_ser(sqlite3 *db, mqtt_server_t *ser) { int i,r=0,exist=0,cnt=0; char mac[10]; #ifdef USE_MQTT sqlite3_stmt *stmt; char *errmsg=NULL; char *table=MQTT_MANAGER_TABLE; char *pbuf=malloc(sizeof(mqtt_server_t)+1024); if(!db || !ser || !pbuf || ser->id<0) { return -1; } if(sys_get_mac(mac, sizeof(mac))==0) { sprintf(ser->cid, "smartPDU_%s", mac); } cnt = get_count(db, table); if(cnt>0) { sprintf(pbuf, "UPDATE %s SET id=%d, mode=%d, name='%s', server='%s', port='%s', cid='%s', user='%s', password='%s', cert='%s' WHERE id=%d;", table, ser->id, ser->mode, ser->name, ser->server, ser->port, ser->cid, ser->user, ser->password, ser->cert?ser->cert:"", ser->id); r = sqlite3_exec(db, pbuf, NULL, 0, &errmsg); if(r != SQLITE_OK) { log_e("___dev_mqtt_update_ser, sqlite3_exec failed, %s\n\n", errmsg); sqlite3_free(errmsg); } else { exist = sqlite3_changes(db); } } if(exist==0) { sprintf(pbuf, "INSERT INTO %s (id,mode,name,server,port,cid,user,password,cert) VALUES (?,?,?,?,?,?,?,?,?);", table); r = sqlite3_prepare_v2(db, pbuf, -1, &stmt, 0); if (r != SQLITE_OK) { log_e("___dev_mqtt_update_ser, sqlite3_prepare_v2 failed, %s\n", sqlite3_errmsg(db)); r = -1; goto quit; } sqlite3_bind_int(stmt, 1, ser->id); sqlite3_bind_int(stmt, 2, ser->mode); sqlite3_bind_text(stmt, 3, ser->name, -1, NULL); sqlite3_bind_text(stmt, 4, ser->server, -1, NULL); sqlite3_bind_text(stmt, 5, ser->port, -1, NULL); sqlite3_bind_text(stmt, 6, ser->cid, -1, NULL); sqlite3_bind_text(stmt, 7, ser->user, -1, NULL); sqlite3_bind_text(stmt, 8, ser->password, -1, NULL); sqlite3_bind_text(stmt, 9, ser->cert, -1, NULL); r = sqlite3_step(stmt); sqlite3_finalize(stmt); if (r != SQLITE_DONE) { log_e("___dev_mqtt_update_ser, sqlite3_step failed\n"); r = -1; } r = 0; } quit: free(pbuf); #endif return r; } int dev_mqtt_update_info(sqlite3 *db, mqtt_info_t *info) { int i,r=-1; #ifdef USE_MQTT for(i=0; iser[i]; r = dev_mqtt_update_ser(db, ser); if(r) { break; } } #endif return r; } int dev_sql_VACCUM(sqlite3 *db) { 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,"VACUUM;" ); res = sqlite_SQL_RUN(db, select_sql, &pResult, &nrow, &ncolumn, &err_msg); if(res != SQLITE_OK) { log_e("sqlite handle error: %s\n%s\n", err_msg,select_sql); sqlite3_free(err_msg); return -1 ; } sqlite3_free_table(pResult); return 0; } int dev_sql_set_overwrite(int flag) { overwrite_flag = flag; return 0; }