aesqloperation.cpp

OTL是一个通用的可以跨平台的ODBC访问源

	{
		filter.GetEventString(cEventString);

		if (cConditionCount == 0)
		{
			sprintf(temp, "%s where EventString like '%s'", pConstStr, cEventString);
			strcat(sqlstm, temp);
		} 
		else
		{
			sprintf(temp, " and EventString like '%s'", cEventString);
			strcat(sqlstm, temp);
		}

		cConditionCount++;
	}

	int len = strlen(sqlstm);
	sqlstr[len] = '\0';

    if (len == 0)
    {
		strcat(sqlstr, pConstStr);
    }
	else
	{
        memcpy(sqlstr, sqlstm, sizeof(psChar) * len);
	}		

}

// 按照条件查询，并将查询结果返回给链表
psInt32 CAESQLOperation::SelectAEInfo(CAEFilterInfo filter, CAEFieldInfo fieldInfo, CAEInfoList *pAEInfoList)
{
#ifdef _DEBUG
	if (pAEInfoList == NULL)
	{
		return ERR_PARAMETER;
	}
#endif

	// 接收读取的各个字段的数据
	psInt64		    unEventNumber;						// 事件编号（唯一标识）
	psBool			bRemote;							// 事件远程写入的标志（1：远程；0：本地）

	psUInt32		temp;								// 事件区域（廖经理意思存储时不存储）
	psUInt32		unItemID;							// 事件源测点ID
	psChar			cSource[SOURCE_LENGTH];				// 事件源（非测点相关中应用）
	psUInt16		usComputerID;						// 计算机标识号
	psUInt16		usApplicationID;					// 程序标识号
	psUInt16		usUserID;							// 用户标识号

	TimeInfo 		Timestamp;							// 时间戳（发出报警时间）
	TimeInfo		StartTime;							// 产生开始时间（产生报警的时间）
	TimeInfo		EndTime;							// 报警结束时间

	psUInt8			ucEventTopic;						// 事件主题
	psUInt8			ucEventCategory;					// 事件类别（只有报警有）
	psUInt8			ucSubCategory;	     				// 子类别（只有报警有）

	psUInt16		ucEventClass;						// 事件级别
	psChar			ucEventString[EVENTSTRING_LENGTH];	// 事件内容
	psUInt8			ucQuality;							// 产生事件测点相关的质量戳

	psUInt8			ucAcked;							// 事件产生后应答标志
	psUInt16		usActorID;							// 应答者ID
	TimeInfo		AckTime;							// 应答时间

	psUInt8			ucNumberOfSubstitutions;			// 附加属性个数
	psChar			cSubstitutions[SUBSTITUTION_LENGTH];// 附加属性内容

	psChar          sqlstr[SQL_STR_LENGTH];


	this->SqlCondition(sqlstr, filter);

	int len = strlen(sqlstr);

	CAEInfo Info;

	try
	{   
		// 取出查询结果，并构造事件对象
		otl_stream o(50, sqlstr, db);

		while (!o.eof())
		{
			// 读取事件编号
			o>>unEventNumber;
			if (fieldInfo.GetAllField() == 1||fieldInfo.GetEventNumber() == 1)
			{
				Info.SetEventNumber(unEventNumber);
			}

			// 读取是否远程写入
			o>>bRemote;
			if (fieldInfo.GetAllField() == 1||fieldInfo.GetRemote() == 1)
			{
				Info.SetRemote(bRemote);
			}
			
			// 读取测点ID号
			o>>unItemID;
			if (fieldInfo.GetAllField() == 1||fieldInfo.GetItemID() == 1)
			{
				Info.SetItemID(unItemID);
			}

			// 读取事件源
			o>>cSource;
			if (fieldInfo.GetAllField() == 1||fieldInfo.GetSource() == 1)
			{
				Info.SetSource(cSource);
			}

			// 读取计算机ID
			o>>temp;
			usComputerID = (psUInt16)temp;
			if (fieldInfo.GetAllField() == 1||fieldInfo.GetComputerID() == 1)
			{
				Info.SetComputerID(usComputerID);
			}

			// 读取程序ID号
			o>>temp;
			usApplicationID = (psUInt16)temp;
			if (fieldInfo.GetAllField() == 1||fieldInfo.GetApplicationID() == 1)
			{
				Info.SetApplicationID(usApplicationID);
			}
	
			//  读取用户ID
			o>>temp;
			usUserID = (psUInt16)temp;
			if (fieldInfo.GetAllField() == 1||fieldInfo.GetUserID() == 1)
			{
				Info.SetUserID(usUserID);
			}

			// 读取时间戳
			o>>Timestamp.tTime;
			o>>temp;
			Timestamp.shMill = (psUInt16)temp;
			if (fieldInfo.GetAllField() == 1||fieldInfo.GetTimestamp() == 1)
			{
				Info.SetTimestamp(Timestamp);
			}

			// 读取报警开始时间
			o>>StartTime.tTime;
			o>>temp;
			StartTime.shMill = (psUInt16)temp;

			if (fieldInfo.GetAllField() == 1||fieldInfo.GetStartTime() == 1)
			{
				Info.SetStartTime(StartTime);
			}

			// 读取报警结束时间
			o>>EndTime.tTime;
			o>>temp;
			EndTime.shMill = (psUInt16)temp;
			if (fieldInfo.GetAllField() == 1||fieldInfo.GetEndTime() == 1)
			{
				Info.SetEndTime(EndTime);
			}
	
			// 读取事件主题
			o>>temp;
			ucEventTopic = (psUInt8)temp;
			if (fieldInfo.GetAllField() == 1||fieldInfo.GetEventTopic() == 1)
			{
				Info.SetEventTopic(ucEventTopic);
			}

			// 读取事件类别
			o>>temp;
			ucEventCategory = (psUInt8)temp;
			if (fieldInfo.GetAllField() == 1||fieldInfo.GetEventCategory() == 1)
			{
				Info.SetEventCategory(ucEventCategory);
			}

			// 读取事件子类别
			o>>temp;
			ucSubCategory = (psUInt8)temp;
			if (fieldInfo.GetAllField() == 1||fieldInfo.GetSubCategory() == 1)
			{
				Info.SetSubCategory(ucSubCategory);
			}

			// 读取事件级别
			o>>temp;
			ucEventClass = (psUInt16)temp;
			if (fieldInfo.GetAllField() == 1||fieldInfo.GetEventClass() == 1)
			{
				Info.SetEventClass(ucEventClass);
			}

			// 读取报警事件内容
			o>>ucEventString;
			if (fieldInfo.GetAllField() == 1||fieldInfo.GetEventString() == 1)
			{
				Info.SetEventString(ucEventString);
			}

			// 读取质量戳
			o>>temp;
			ucQuality = (psUInt8)temp;
			if (fieldInfo.GetAllField() == 1||fieldInfo.GetQuality() == 1)
			{
				Info.SetQuality(ucQuality);
			}

			// 读取应答信息
			o>>temp;
			ucAcked = (psUInt8)temp;
			if (fieldInfo.GetAllField() == 1||fieldInfo.GetAcked() == 1)
			{
				Info.SetAcked(ucAcked);
			}

			// 读取应答者
			o>>temp;
			usActorID = (psUInt16)temp;
			if (fieldInfo.GetAllField() == 1||fieldInfo.GetActorID() == 1)
			{
				Info.SetActorID(usActorID);
			}
	
			// 读取应答时间
			o>>AckTime.tTime;
			o>>temp;
			AckTime.shMill = (psUInt16)temp;
			if (fieldInfo.GetAllField() == 1||fieldInfo.GetAckTime() == 1)
			{
				Info.SetAckTime(AckTime);
			}

			// 读取附加属性字段个数
			o>>temp;
			ucNumberOfSubstitutions = (psUInt8)temp;
			if (fieldInfo.GetAllField() == 1||fieldInfo.GetNumberOfSubstitutions() == 1)
			{
				Info.SetNumberOfSubstitutions(ucNumberOfSubstitutions);
			}

			// 读取附加属性信息
			o>>cSubstitutions;
			if (fieldInfo.GetAllField() == 1||fieldInfo.GetSubstitutions() == 1)
			{
				Info.SetSubstitutions(cSubstitutions);
			}

			// 插入到链表中
			pAEInfoList->InsertAEInfo(Info);
		}       
	}

	// 获取读数据异常
	catch (otl_exception& p)
	{
		// 删除查询出来链表的信息
        
		// 数据库连接断开
		if (db.connected == 0)
		{
			return ERR_CONNECT_SQL_FAIL;
		}
        
		// 数据表不存在
		if (p.code == 1146)
		{
			return ERR_TABLE_NOT_EXIST;
		}

		return ERR_SQL_ERROR;
	}

	return SUCCESS;
}

//写入应答信息
psInt32 CAESQLOperation::UpDateAckedSQL(psUInt64 ulEventNumber, psUInt16 usActorID, TimeInfo AckTime, psUInt8 ucAcked)
{
	psChar *sqlstm="update aeinfo set";

	psChar strSql[SQL_STR_LENGTH];

	sprintf(strSql,"%s ActorID=%u, AckSec=%lld, AckMil=%u, Acked=%u where EventNumber=%llu",
		sqlstm, usActorID, AckTime.tTime, AckTime.shMill, ucAcked, ulEventNumber);

	try
	{
		otl_stream o(1, strSql, db);
	}

	// 获取更新异常
	catch (otl_exception& p)
	{   
		// 数据库连接断开
		if (db.connected == 0)
		{
			return ERR_CONNECT_SQL_FAIL;
		}

		// 数据表不存在
		if (p.code == 1146)
		{
			return ERR_TABLE_NOT_EXIST;
		}
		
		return ERR_SQL_ERROR;
	}

	return SUCCESS;
}

// 写入结束时间,前三个参数唯一确定一个报警，最后是写入的结束时间
psInt32 CAESQLOperation::UpdateEndTimeSQL(psUInt32 unItemID, TimeInfo StartTime, psUInt8 ucEventCategory, TimeInfo EndTime)
{
	psChar  *sqlstm="update aeinfo set";
	psChar  strSql[SQL_STR_LENGTH];
	psChar  strTemp[SQL_STR_LENGTH];
	psUInt32 unAcked;

	sprintf(strTemp,"select Acked from aeinfo where ItemID=%u and StartSec=%lld and StartMil=%u and EventCategory=%u",
		unItemID, StartTime.tTime, StartTime.shMill, ucEventCategory);

	try
	{
		// 获取Acked字段信息
		otl_stream temp(1, strTemp, db);
		while (!temp.eof())
		{
			temp>>unAcked;
		}
		
		// 对Acked字段信息进行改动
		unAcked = unAcked |0x4;
        
		sprintf(strSql, "%s EndSec=%lld, EndMil=%u, Acked=%u where ItemID=%u and StartSec=%lld and StartMil=%u and EventCategory=%u",
			sqlstm, EndTime.tTime, EndTime.shMill, unAcked, unItemID, StartTime.tTime, StartTime.shMill, ucEventCategory);
		
		otl_stream o(1, strSql, db);
	}
	catch (otl_exception& p)
	{
		// 数据库连接断开
		if (db.connected == 0)
		{
			return ERR_CONNECT_SQL_FAIL;
		}

		// 数据表不存在
		if (p.code == 1146)
		{
			return ERR_TABLE_NOT_EXIST;
		}

		return ERR_SQL_ERROR;
	}

	return SUCCESS;
}

// 删除指定测点的事件
psInt32 CAESQLOperation::DeleteByItemID(psUInt32 unItemID)
{
	psChar sqlstm[SQL_STR_LENGTH];

	sprintf(sqlstm, "delete from aeinfo where ItemID=%u", unItemID);
	try
	{
		otl_stream o(1, sqlstm, db);
	}
	catch (otl_exception& p)
	{
		// 数据库连接断开
		if (db.connected == 0)
		{
			return ERR_CONNECT_SQL_FAIL;
		}

		// 数据表不存在
		if (p.code == 1146)
		{
			return ERR_TABLE_NOT_EXIST;
		}

	   return ERR_SQL_ERROR;	
	}
    return SUCCESS;    
}

// 用于单元测试的函数,清空表中的数据
psInt32 CAESQLOperation::DeleteInfo()
{
   psChar *sqlstr="delete from aeinfo";
   try
   {
	   otl_stream o(10, sqlstr, db);  
   }
   catch (otl_exception& p)
   {   
	   // 数据库连接断开
	   if (db.connected == 0)
	   {
		   return ERR_CONNECT_SQL_FAIL;
	   }

	   // 数据表不存在
	   if (p.code == 1146)
	   {
		   return ERR_TABLE_NOT_EXIST;
	   }

	   return ERR_SQL_ERROR;

   }
    return SUCCESS;
}

// 删除表
psVoid CAESQLOperation::DropTable()
{
	otl_cursor::direct_exec
		(
		db,
		"drop table aeinfo",
		otl_exception::disabled // disable OTL exceptions
		); // drop table
}