www.pudn.com > RadiusSrv.rar > IPPhoneDB.cpp, change:2003-10-30,size:17598b


#include "IPPhoneDB.h" 
#include "..\common\log.h" 
 
IPPhoneDB::IPPhoneDB() 
{ 
	//DBManagerLink=new DBManager(); 
} 
 
IPPhoneDB::~IPPhoneDB() 
{ 
	IPPhoneDBDestory(); 
//	if(DBManagerLink) 
//		delete DBManagerLink; 
} 
 
void IPPhoneDB::IPPhoneDBDestory() 
{ 
	DBManagerLink.Close(); 
 
	PtrList::iterator it; 
	for (it = IPPriceTable.begin(); it != IPPriceTable.end(); ++it) 
		delete (_IP_PRICE *) *it; 
	IPPriceTable.clear(); 
 
	areaCodeList.clear(); 
} 
 
///////////////////////////////////////////////////////////////////////////////// 
//以下为数据库操作 
bool IPPhoneDB::IPPhoneDBInit(_DB_INFO &dbMaster) 
{ 
	//打开数据库 
	if(!DBManagerLink.Open(dbMaster.connection.c_str(), dbMaster.user.c_str(), dbMaster.passwd.c_str())) 
		return false; 
	 
//	internationalPrefix = dbMaster.internationalPrefix; 
//	internalPrefix = dbMaster.internalPrefix; 
 
	//取得地区列表 
 	getAreaCodeList(areaCodeList); 
 
	//all epinfo 
	_EP_INFO EPInfo; 
	updatePUserState(EPInfo, USER_STATE_OFFLINE); 
 
	return true; 
//return DBManagerLink.DBInit(dbMaster); 
} 
 
//得到指定前缀的价格 
bool IPPhoneDB::getIPPrice(const uint8 &areaCode, const uint8 &priceID, float &price) 
{ 
	//get price from the list 
	if(!IPPriceTable.empty()) 
	{ 
		PtrList::iterator it; 
		//建议: 
		//这里可以通过一点的规则,提高搜索速度 
		for(it=IPPriceTable.begin();it!=IPPriceTable.end();it++) 
		{ 
			_IP_PRICE *IPPrice=(_IP_PRICE *)*it; 
 			if(IPPrice->areaCode==areaCode&&IPPrice->priceID==priceID) 
			{ 
				price=IPPrice->price; 
				return true; 
			} 
		} 
	} 
 
//demo版,最多提供5个国家的计费 
#ifdef _DEMO 
	if(IPPriceTable.size()>5) 
		return false; 
#endif 
	 
	//can't find price, need get the price from the DB 
	string preStmt; 
	char cTmp[50]; 
 
	//get price 
	preStmt="SELECT  price"; 
	sprintf(cTmp,"%d from IPPrice where areaCode=",priceID); 
	preStmt.append(cTmp); 
 
	sprintf(cTmp,"%d",areaCode); 
	preStmt.append(cTmp); 
 
	_RecordsetPtr recordSet = DBManagerLink.executeQuery(preStmt); 
 
	if(recordSet!=NULL && !recordSet->adoEOF) 
	{ 
		_variant_t vPrice; 
		sprintf(cTmp,"price%d",priceID); 
		vPrice=recordSet->GetCollect(cTmp); 
		price=vPrice; 
 
		//insert into list 
		_IP_PRICE *IPPrice=new _IP_PRICE; 
		IPPrice->areaCode=areaCode; 
		IPPrice->priceID=priceID; 
		IPPrice->price=price; 
		 
		IPPriceTable.push_back(IPPrice); 
		return true; 
	} 
	else 
		return false; 
 
	return true; 
 
} 
 
//插入历史记录 
bool IPPhoneDB::insertDialedHistory(_CDR &CDR, uint16 areaCode) 
{ 
	string preStmt; 
	char cTmp[50]; 
 
	preStmt="insert into IPHistory "; 
	preStmt.append("(userID, areaCode, duration, fee, callerIP, calledIP, dialCode, startTime, endTime) "); 
	preStmt.append("values ('"); 
 
	preStmt.append(CDR.EPInfo.h323_ID.c_str()); 
	preStmt.append("',"); 
 
	sprintf(cTmp, "%d,", areaCode); 
	preStmt.append(cTmp); 
 
	sprintf(cTmp, "%d,", CDR.duration); 
	preStmt.append(cTmp); 
 
	//money 
	sprintf(cTmp, "%f,'", CDR.bill); 
	preStmt.append(cTmp); 
 
	preStmt.append(CDR.callerIP.c_str()); 
	preStmt.append("','"); 
 
	preStmt.append(CDR.calledIP.c_str()); 
	preStmt.append("','"); 
 
	preStmt.append(CDR.dialedDigits.c_str()); 
 
	if(strcmp(CDR.startTime.c_str(),"NULL")!=0) 
	{ 
		preStmt.append("','"); 
		 
		//	preStmt.append("2003-3-12 02:30:40"); 
		preStmt.append(CDR.startTime.c_str()); 
		preStmt.append("','"); 
	} 
	else 
	{ 
		preStmt.append("',NULL,'"); 
	} 
 
	preStmt.append(CDR.endTime.c_str()); 
	preStmt.append("')"); 
 
//	printf(preStmt.c_str()); 
//	printf("\n"); 
 
	return DBManagerLink.executeQuery(preStmt); 
} 
 
//更新用户帐号 
bool IPPhoneDB::updatePhoneInfo(_CDR &CDR, float &balance) 
{ 
	balance-=CDR.bill; 
	 
	//更新数据库里的资料 
	string preStmt; 
	 
	preStmt="UPDATE PhoneInfo SET balance="; 
	 
	char cTmp[50]; 
	sprintf(cTmp, "%f where userID='", balance); 
	preStmt.append(cTmp); 
	preStmt.append(CDR.EPInfo.h323_ID.c_str()); 
	preStmt.append("'"); 
	 
	return DBManagerLink.executeQuery(preStmt); 
} 
 
//记录通话时间 
bool IPPhoneDB::updatePhoneTime(_CDR &CDR) 
{ 
	string preStmt; 
 
	//先取得原来时间 
	preStmt="SELECT totalTime from PhoneTime where userID='"; 
	preStmt.append(CDR.EPInfo.h323_ID.c_str()); 
//	preStmt.append("' and areaCode='"); 
	preStmt.append("'"); 
 
	_RecordsetPtr recordSet = DBManagerLink.executeQuery(preStmt); 
 
	if(recordSet!=NULL && !recordSet->adoEOF) 
	{ 
		_variant_t vBalance; 
		vBalance=recordSet->GetCollect("balance"); 
//		balance=vBalance; 
	} 
	else 
		return false; 
 
	return true; 
} 
 
/********************************************************************* 
 * Function Declare : IPPhoneDB::getPhoneInfo 
 * Explain : 得到指定用户名的用户的信息 
 * Parameters :  
 * const char *userID -- 用户名 
 *  float &balance -- 用户余额 
 *  uint8 &priceID -- 用户计费的类型 
 *  uint8 &groupID -- 用户所属的组 
 * Return :  
 * bool  -- 数据库操作正确否 
 * Author : xinbao  
 * Time : 2003-10-31 10:42:29  
*********************************************************************/ 
bool IPPhoneDB::getPhoneInfo(const char *userID, float &balance, 
							 uint8 &priceID, uint16 &groupID) 
{ 
	string preStmt; 
 
	preStmt="select balance, priceID from PhoneInfo where userID='"; 
	preStmt.append(userID); 
	preStmt.append("'"); 
 
	_RecordsetPtr recordSet = DBManagerLink.executeQuery(preStmt); 
 
	if(recordSet!=NULL && !recordSet->adoEOF) 
	{ 
		_variant_t var; 
		var=recordSet->GetCollect("balance"); 
		balance=var; 
 
		var=recordSet->GetCollect("priceID"); 
		priceID=var; 
	} 
	else 
		return false; 
	 
	return true; 
} 
 
//得到国家代码表 
bool IPPhoneDB::getAreaCodeList(Uint16List &areaCodeList) 
{ 
	string preStmt; 
	uint16 areaCode; 
 
	preStmt="select areaCode from IPPrice"; 
 
	_RecordsetPtr recordSet = DBManagerLink.executeQuery(preStmt); 
 
	while(recordSet!=NULL && !recordSet->adoEOF) 
	{ 
		_variant_t var; 
		var=recordSet->GetCollect("areaCode"); 
		areaCode=var.intVal; 
 
		//insert into list		 
		areaCodeList.push_back(areaCode); 
		recordSet->MoveNext(); 
	} 
  
	return true; 
} 
 
 
///////////////////////////////////////////////////////// 
//外部接口 
bool IPPhoneDB::addDialedInfo(_CDR &CDR) 
{ 
	//判断拨打类型 
	uint16 areaCode; 
	areaCode=parseAreaCode(CDR.dialedDigits.c_str()); 
 
	if(areaCode==WANG_NEI) 
		return true; 
	 
	float balance=0.0; 
	uint8 priceID=0; 
	 
	CDR.bill=0; 
 
	//取得帐号余额和总计时间 
	if(!getPhoneInfo(CDR.EPInfo.h323_ID.c_str(), balance, priceID, CDR.EPInfo.groupID)) 
	{ 
		//通话时间不为0,则记录其行为 
		if(CDR.duration!=0) 
		{ 
			LOG(1) ("非法用户通讯!用户名:%s,用户IP:%s", CDR.EPInfo.h323_ID.c_str(),	CDR.callerIP.c_str()); 
			insertDialedHistory(CDR, areaCode); 
		} 
		return false; 
	} 
	 
	float price; 
	if(!getIPPrice(areaCode, priceID, price)) 
		return false; 
 
	//计算通话费用 
	CDR.bill=SecToMin(CDR.duration)*price;	 
 
	//用户的计费 
	updatePhoneInfo(CDR, balance); 
 
	//如果用户拨打计费了,组计费 
	if(CDR.bill!=0) 
		updateGroupInfo(CDR.EPInfo.groupID, areaCode, CDR.duration, CDR.bill); 
	 
	insertDialedHistory(CDR, areaCode); 
	return true; 
} 
 
bool IPPhoneDB::updateGroupInfo(uint16 &groupID, uint16 &areaCode,  
								uint32 &duration, float &userBill) 
{ 
	string preStmt; 
	float profit=0; 
	uint8 priceID=0; 
	float groupPrice;  
 
	//得到组的信息 
	if(!getGroupInfo(groupID, priceID, profit)) 
		return false; 
 
	//得到组的费率 
	if(!getIPPrice(areaCode, priceID, groupPrice)) 
		return false; 
 
	profit=profit+(userBill-groupPrice*SecToMin(duration)); 
	 
	//更新组里数据库的资料 
	preStmt="UPDATE GroupInfo SET profit="; 
	 
	char cTmp[50]; 
	sprintf(cTmp, "%f where groupID=", profit); 
	preStmt.append(cTmp); 
 
	sprintf(cTmp, "%d", groupID); 
	preStmt.append(cTmp); 
	 
	return DBManagerLink.executeQuery(preStmt); 
} 
 
bool IPPhoneDB::getGroupInfo(uint16 &groupID, uint8 &priceID, float &profit) 
{ 
	string preStmt; 
	char szGroupID[10]; 
 
	//取得组的priceID 
	preStmt="select profit, priceID from GroupInfo where groupID="; 
	sprintf(szGroupID, "%d", groupID); 
	preStmt.append(szGroupID); 
 
	_RecordsetPtr recordSet = DBManagerLink.executeQuery(preStmt); 
 
	if(recordSet!=NULL && !recordSet->adoEOF) 
	{ 
		_variant_t var; 
		var=recordSet->GetCollect("profit"); 
		profit=var; 
 
		var=recordSet->GetCollect("priceID"); 
		priceID=var; 
		return true; 
	} 
	else 
		return false; 
} 
 
uint32 IPPhoneDB::getDurationLimit(_ARQ &ARQ) 
{ 
	string preStmt; 
 
	preStmt="select balance from PhoneInfo where userID='"; 
	preStmt.append(ARQ.EPInfo.h323_ID.c_str()); 
 
#ifndef _DEBUG 
	preStmt.append("' and netPhone='"); 
	preStmt.append(ARQ.EPInfo.dialedDigits.c_str()); 
#endif 
 
	preStmt.append("' and Isactive=true"); 
 
	_RecordsetPtr recordSet = DBManagerLink.executeQuery(preStmt); 
 
	float balance; 
 
	if(recordSet!=NULL && !recordSet->adoEOF) 
	{ 
		_variant_t var; 
		balance=var=recordSet->GetCollect("balance"); 
	} 
	else 
		return 0; 
	 
	//判断拨打类型 
	uint16 areaCode; 
	areaCode=parseAreaCode(ARQ.dialedDigits.c_str()); 
 
	float price=0; 
	//如果网内,则price=0 
	if(areaCode==WANG_NEI) 
	{ 
		return 999999; 
	} 
	if(areaCode==AREA_CODE_ERROR) 
	{ 
		return 0; 
	} 
	else 
	{		 
		uint8 priceID=0; 
		 
		//取得帐号余额和总计时间 
		if(!getPhoneInfo(ARQ.EPInfo.h323_ID.c_str(), balance, priceID, ARQ.EPInfo.groupID)) 
			return 0; 
		 
		if(!getIPPrice(areaCode, priceID, price)) 
			return 0; 
	} 
 
	if(balance < price) 
		return 0; 
 
	//用户存在并且帐户里有一点余额 
	return SinToSec(balance/price)-3;	 
} 
 
/********************************************************************* 
 * Function Declare : IPPhoneDB::GetPhonePasswd 
 * Explain : 对于使用H235认证的用户,可以取出数据库中加密了的密码 
 * Parameters :  
 * _EP_INFO &EPInfo -- 用户信息 
 *  char *szPasswd -- 用户暗码 
 * Return :  
 * bool  -- 操作成功与否 
 * Author : xinbao  
 * Time : 2003-10-28 11:28:07  
*********************************************************************/ 
bool IPPhoneDB::GetPhonePasswd(_EP_INFO &EPInfo, char *szPasswd) 
{ 
	string preStmt; 
	char szTmp[10]; 
 
	preStmt="select passwd from PhoneInfo where userID='"; 
	preStmt.append(EPInfo.h323_ID.c_str()); 
	 
	preStmt.append("' and netPhone='"); 
	preStmt.append(EPInfo.dialedDigits.c_str()); 
 
	preStmt.append("' and groupID="); 
	sprintf(szTmp,"%d", EPInfo.groupID); 
	preStmt.append(szTmp); 
 
	preStmt.append(" and Isactive=true"); 
 
	_RecordsetPtr recordSet = DBManagerLink.executeQuery(preStmt); 
 
	if(recordSet!=NULL && !recordSet->adoEOF) 
	{ 
		_variant_t var; 
		var=recordSet->GetCollect("passwd"); 
 
		strcpy(szPasswd, (LPTSTR)(_bstr_t)var); 
 
		return true; 
	} 
	else 
		return false; 
 
	return true; 
} 
 
 
//这里实现用户的验证,可以用密码,帐号,ip等方式进行验证 
bool IPPhoneDB::validateRCF(_RCF &RCF) 
{ 
	string preStmt; 
 
	preStmt="select Isactive from PhoneInfo where userID='"; 
	preStmt.append(RCF.EPInfo.h323_ID.c_str()); 
	preStmt.append("'"); 
 
	_RecordsetPtr recordSet = DBManagerLink.executeQuery(preStmt); 
 
	if(recordSet!=NULL && !recordSet->adoEOF) 
	{ 
		_variant_t var; 
		var=recordSet->GetCollect("Isactive"); 
 
		return var; 
	} 
	else 
		return false; 
 
	return true; 
} 
 
/********************************************************************* 
 * Function Declare : IPPhoneDB::validateUser 
 * Explain : 对于用H323ID和E164的用户进行密码验证,需要使用H323ID,E164 
 *           是否被冻结,所在组和是否需要密码		 
 * Parameters :  
 * _EP_INFO &EPInfo -- 用户信息 
 * Return : 返回验证结果 
 * bool  -- true    验证通过,返回true 
 *          false   1.如果查询没有返回,说明至少一个条件不符合,返回false 
 *					2.通过取回needPasswd字段,如果needPasswd为true,说明设置为 
 *                  需要密码,返回false 
 * Author : xinbao  
 * Time : 2003-10-28 11:19:22  
*********************************************************************/ 
bool IPPhoneDB::validateUser(_EP_INFO &EPInfo) 
{ 
	string preStmt; 
	char szTmp[10]; 
 
	preStmt="select needPasswd from PhoneInfo where userID='"; 
	preStmt.append(EPInfo.h323_ID.c_str()); 
	preStmt.append("' and Isactive=true and netPhone='"); 
	preStmt.append(EPInfo.dialedDigits.c_str()); 
 
	preStmt.append("' and groupID="); 
	sprintf(szTmp,"%d", EPInfo.groupID); 
	preStmt.append(szTmp); 
 
	_RecordsetPtr recordSet = DBManagerLink.executeQuery(preStmt); 
 
	if(recordSet!=NULL && !recordSet->adoEOF) 
	{ 
		_variant_t var; 
		var=recordSet->GetCollect("needPasswd"); 
 
		return !(bool)var; 
	} 
	else 
		return false; 
 
	return true; 
} 
 
////////////////////////////////////////////////////////// 
//解析函数 
int IPPhoneDB::parseAreaCode(const char *dialedDigits) 
{ 
	//remove 0 
	//strncmp 
	uint16 nAreaCode; 
	nAreaCode=skipDailPrefix(dialedDigits); 
	//国际的,需要继续判断 
	if(nAreaCode!=GUO_JI) 
		return nAreaCode; 
 
	//取得国家代码 
	char areaCode[5]; 
	strncpy(areaCode, dialedDigits, sizeof(areaCode)); 
	areaCode[4]='\0'; 
 
	nAreaCode=atoi(areaCode); 
	Uint16List::iterator it; 
 
	//优化建议: 
	//1.对于已经大于areacode的值,可以不用比 
	//2. 
	while(nAreaCode>0) 
	{ 
		for(it=areaCodeList.begin();it!=areaCodeList.end();it++) 
		{ 
			if(nAreaCode==(uint16 )*it) 
				return nAreaCode; 
		} 
		nAreaCode=nAreaCode/10; 
	} 
 
	return AREA_CODE_ERROR; 
} 
 
//得到拨号类型 
uint8 IPPhoneDB::skipDailPrefix(const char *dialedDigits) 
{ 
	//国际 
	if(strncmp("00", dialedDigits, 2)==0) 
	{ 
		dialedDigits+=2; 
		return GUO_JI; 
	} 
	//国内 
	else if(strncmp("0", dialedDigits, 1)==0) 
	{ 
		dialedDigits+=1; 
		return GUO_NEI; 
	} 
	//网内 
	else 
		return WANG_NEI; 
} 
 
//更新GK版本 
bool IPPhoneDB::updateGKVersion(char * version) 
{ 
	string preStmt; 
 
	preStmt="UPDATE GKInfo SET Version='"; 
	preStmt.append(version); 
	preStmt.append("'"); 
 
	return DBManagerLink.executeQuery(preStmt); 
} 
 
//更新GK的当前信息 
bool IPPhoneDB::updateGKInfo(_STATISTICS &statistics) 
{ 
/* 
	uint32 TotalEPs; 
	uint32 Terminals; 
	uint8  Gateways; 
	uint16 NATed; 
	uint32 CachedEPs; 
	uint32 C_Terminals; 
	uint8  C_Gateways; 
	uint32 CurrentCalls; 
	uint32 Acitve; 
	uint32 FromNeighbor; 
	uint32 FromParent; 
	uint32 TotalCalls; 
	uint32 Successful; 
	uint32 S_FromNeighbor; 
	uint32 S_FromParent; 
	string Startup; 
	string Runing; 
*/ 
	char cTmp[50]; 
	string preStmt; 
	 
	preStmt="UPDATE GKInfo SET TotalEPs="; 
	sprintf(cTmp, "%ld",statistics.TotalEPs); 
	preStmt.append(cTmp); 
	preStmt.append(", Terminals="); 
	sprintf(cTmp, "%ld",statistics.Terminals); 
	preStmt.append(cTmp); 
	preStmt.append(", Gateways="); 
	sprintf(cTmp, "%d",statistics.Gateways); 
	preStmt.append(cTmp); 
	preStmt.append(", NATed="); 
	sprintf(cTmp, "%ld",statistics.NATed); 
	preStmt.append(cTmp); 
	preStmt.append(", CachedEPs="); 
	sprintf(cTmp, "%ld",statistics.CachedEPs); 
	preStmt.append(cTmp); 
	preStmt.append(", C_Terminals="); 
	sprintf(cTmp, "%ld",statistics.C_Terminals); 
	preStmt.append(cTmp); 
	preStmt.append(", C_Gateways="); 
	sprintf(cTmp, "%d",statistics.C_Gateways); 
	preStmt.append(cTmp); 
	preStmt.append(", CurrentCalls="); 
	sprintf(cTmp, "%ld",statistics.CurrentCalls); 
	preStmt.append(cTmp); 
 
	if(DBManagerLink.executeQuery(preStmt)==NULL) 
		return false; 
 
	preStmt="UPDATE GKInfo SET TotalEPs="; 
 	sprintf(cTmp, "%ld",statistics.Acitve); 
	preStmt.append(cTmp); 
	preStmt.append(", FromNeighbor="); 
	sprintf(cTmp, "%ld",statistics.FromNeighbor); 
	preStmt.append(cTmp); 
	preStmt.append(", FromParent="); 
	sprintf(cTmp, "%ld",statistics.FromParent); 
	preStmt.append(cTmp); 
	preStmt.append(", TotalCalls="); 
	sprintf(cTmp, "%ld",statistics.TotalCalls); 
	preStmt.append(cTmp); 
	preStmt.append(", Successful="); 
	sprintf(cTmp, "%ld",statistics.Successful); 
	preStmt.append(cTmp); 
	preStmt.append(", S_FromNeighbor="); 
	sprintf(cTmp, "%ld",statistics.S_FromNeighbor); 
	preStmt.append(cTmp); 
	preStmt.append(", S_FromParent="); 
	sprintf(cTmp, "%ld",statistics.S_FromParent); 
	preStmt.append(cTmp); 
	preStmt.append(", Startup='"); 
 	preStmt.append(statistics.Startup.c_str()); 
	preStmt.append("', Running='"); 
 	preStmt.append(statistics.Running.c_str()); 
 	preStmt.append("'"); 
	 
	return DBManagerLink.executeQuery(preStmt); 
} 
 
////////////////////////////////////////////////////////// 
//更新当前用户状态 
bool IPPhoneDB::updatePUserState(_EP_INFO EPInfo, uint8 userState) 
{ 
	string preStmt; 
	char szTmp[2]; 
	 
	preStmt="UPDATE PhoneInfo SET userState="; 
	sprintf(szTmp, "%d", userState); 
	preStmt.append(szTmp); 
 
	if(strcmp(EPInfo.h323_ID.c_str(),"")!=0) 
	{ 
		preStmt.append(" WHERE userID='"); 
		preStmt.append(EPInfo.h323_ID.c_str()); 
		preStmt.append("'"); 
	} 
	else if(strcmp(EPInfo.dialedDigits.c_str(), "")!=0) 
	{ 
		preStmt.append(" WHERE netPhone='"); 
		preStmt.append(EPInfo.dialedDigits.c_str()); 
		preStmt.append("'"); 
//	preStmt.append(" WHERE netPhone='444444'"); 
	} 
 
 
 
	return DBManagerLink.executeQuery(preStmt); 
} 
 
bool IPPhoneDB::updatePUserState(_ACF &ACF, uint8 callType) 
{ 
	string preStmt; 
	char szTmp[2]; 
	 
	preStmt="UPDATE PhoneInfo SET userState="; 
	sprintf(szTmp, "%d", USER_STATE_CALL); 
	preStmt.append(szTmp); 
 
	preStmt.append(", isCaller="); 
	if(strcmp(ACF.dialedDigits.c_str(),"ACF_ACCEPT")==0) 
		preStmt.append("false"); 
	else 
	{ 
		preStmt.append("true"); 
 
		preStmt.append(", dialedDigits='"); 
		preStmt.append(ACF.dialedDigits.c_str()); 
		preStmt.append("'"); 
	} 
 
	preStmt.append(", userIP='"); 
	preStmt.append(ACF.callerIP.c_str()); 
 
	preStmt.append("', callType="); 
	sprintf(szTmp, "%d", callType); 
	preStmt.append(szTmp); 
	 
	preStmt.append(" WHERE userID='"); 
	preStmt.append(ACF.EPInfo.h323_ID.c_str()); 
	preStmt.append("'");	 
 
	return DBManagerLink.executeQuery(preStmt); 
}