dbsmgw.cpp

电信的97接口程序,用于把话单入库。这里是采用FTP方式采集话单

			// Could be a nonfatal error
			RTRACE(2, "SQL FAIL:");
			RTRACE(2, "\t" << sql);
			RTRACE(2, "\t" << sql2);
			iMoreResults = 0;
			iRes = -1;
			dbcancel(m_dbproc);
			break;
		default:
			RTRACE(2, "SQL UNKNOWN:");
			RTRACE(2, "\t" << sql);
			RTRACE(2, "\t" << sql2);
			iMoreResults = 0;  // unknown
			iRes = -2;
			dbcancel(m_dbproc);
			break;
		}
	} // while(iMoreResults && FALSE == DBDEAD(pdbproc))

	if(iRes == 0 && m_state == 0)
		m_userNum ++; //Operation of Insert new user successfully. So UserNumber + 1;
	return iRes;
}

//generate update sql
void CInterfaceContent::GetUpdateSQL()
{
	char tmp[2];
	m_state = 2; //the operation is Update;

	m_inheritGroup = 0; //not inherit
	if(strcmp(m_aCLICP, "") == 0  && strcmp(m_aCLContent, "") == 0 && m_sendRecv == 0)
		m_inheritGroup = 1;

	RTRACE(3, "begin Update SQL " << m_inheritGroup);
	strcpy(m_sql, "update UserAcct set Password = '");
	strcat(m_sql, userPassword);
	strcat(m_sql, "', AccountsType = '");
	strcat(m_sql, m_userGroup);
	strcat(m_sql, "', Service = ");
	if(m_inheritGroup == 0)
		strcat(m_sql, "0");
	else
		strcat(m_sql, cTSI_Service);
		
	strcat(m_sql, ", UserType = '");
	strcat(m_sql, m_userType);
	strcat(m_sql, "', UserTel = '");
	strcat(m_sql, m_password);
//	strcat(m_sql, "', CreateTime = '");
//	strcat(m_sql, m_genTime);
	strcat(m_sql, "', EndTime = ");
	char endTimeTemp[22];
	TransferEndTime(m_endTime, endTimeTemp);
	strcat(m_sql, endTimeTemp);
	strcat(m_sql, ", UseGroup = ");
	sprintf(tmp, "%d", m_inheritGroup);
	strcat(m_sql, tmp);
	strcat(m_sql, ", CreditAccount = '");
	strcat(m_sql, m_serviceId);
	strcat(m_sql, "', ModifyTime = getdate() ");
	strcat(m_sql, " where UserAccounts = '");
	strcat(m_sql, m_areaNo);
	strcat(m_sql, m_teleNumber);
	strcat(m_sql, "' ");

	if(m_inheritGroup == 0) // Not inherit the properties of the group
	{
		char buf[41];
		sprintf(buf, "%s%s", m_areaNo, m_teleNumber);
		if(GetAdsiAttr(buf) == -1) // Can't find the record or sql execute error
		{
			strcpy(m_sql2, "insert into AdsiAttr(UserAccounts, ACLICP, ACLContent, ");
			strcat(m_sql2, "resv1) values('");
			strcat(m_sql2, m_areaNo);
			strcat(m_sql2, m_teleNumber);
			strcat(m_sql2, "', '");
			strcat(m_sql2, m_aCLICP);
			strcat(m_sql2, "', '");
			strcat(m_sql2, m_aCLContent);
			strcat(m_sql2, "', '");
			sprintf(tmp, "%d", ((m_sendRecv == 1 || m_sendRecv == 2)?m_sendRecv:3));
			strcat(m_sql2, tmp);
			strcat(m_sql2, "') ");	
		}
		else
		{
			strcpy(m_sql2, "update AdsiAttr set ACLICP = '");
			strcat(m_sql2, m_aCLICP);
			strcat(m_sql2, "', ACLContent = '");
			strcat(m_sql2, m_aCLContent);
			strcat(m_sql2, "', resv1 = '");
			sprintf(tmp, "%d", ((m_sendRecv == 1 || m_sendRecv == 2)?m_sendRecv:3));
			strcat(m_sql2, tmp);
			strcat(m_sql2, "' where UserAccounts = '");
			strcat(m_sql2, m_areaNo);
			strcat(m_sql2, m_teleNumber);
			strcat(m_sql2, "' ");
		}
	}
	else
	{
		strcpy(m_sql2, "delete from AdsiAttr where UserAccounts = '");
		strcat(m_sql2, m_areaNo);
		strcat(m_sql2, m_teleNumber);
		strcat(m_sql2, "' ");
	}
}

//generate Insert sql
void CInterfaceContent::GetInsertSQL(int isLink)
{
    if (isLink == 1 )
        GetInsertSQL(m_areaNo, m_teleNumber, isLink);
    else
        GetInsertSQL(m_areaNo, m_teleNumber);
}

//generate Insert sql
void CInterfaceContent::GetInsertSQL(char * areaNo, char * telNumber, int isLink)
{
	char tmp[2];
	
	m_state = 0; // the operation is Insert

	m_inheritGroup = 0; //not inherit
	if(strcmp(m_aCLICP, "") == 0  && strcmp(m_aCLContent, "") == 0 && m_sendRecv == 0)
		m_inheritGroup = 1;

	RTRACE(3, "Begin Insert SQL");
	if ((CGlobal::Instance()->m_areacode == 3) || (CGlobal::Instance()->m_areacode == 4)||(CGlobal::Instance()->m_areacode==8))
        //上海97，江苏97，用EmailAddress保存97流水号
		strcpy(m_sql, "insert into UserAcct(UserAccounts, Password, EmailAddress, AccountsType, Service");
	else
		strcpy(m_sql, "insert into UserAcct(UserAccounts, Password, AccountsType, Service");
	//strcat(m_sql, ", UserType, UserTel, CreateTime, ModifyTime, EndTime, UseGroup, CreditAccount, ");
    strcat(m_sql, ", UserType, UserTel, CreateTime, ModifyTime, EndTime, UseGroup,  ");
    strcat(m_sql, " IsLink, UserSource, GroupList, ProtocolType, "); //add by zjl 2003-09-03
	strcat(m_sql, " UserStatus)");
	strcat(m_sql, " values('");
	strcat(m_sql, areaNo);
	strcat(m_sql, telNumber);
	strcat(m_sql, "', '");
	strcat(m_sql, userPassword);
	if ((CGlobal::Instance()->m_areacode == 3) || (CGlobal::Instance()->m_areacode == 4)||(CGlobal::Instance()->m_areacode==8))
	{
		strcat(m_sql, "', '");
		strcat(m_sql, m_addString);
		strcat(m_sql, "', '");
	}
	else
		strcat(m_sql, "', '");
	strcat(m_sql, m_userGroup);
	strcat(m_sql, "', ");
	if(m_inheritGroup == 0)
		strcat(m_sql, "0");
	else
		strcat(m_sql, cTSI_Service);
	strcat(m_sql, ", '");
	strcat(m_sql, m_userType);
	strcat(m_sql, "', '");
	strcat(m_sql, m_password);
	strcat(m_sql, "', ");
	////////btrmg 2005.02.22 for jiangsu socket
//	strcat(m_sql, m_genTime); // BeginTime = now time, ModifyTime = now time
	strcat(m_sql, "getdate(), getdate(), ");
//	strcat(m_sql, "', ");
//	strcat(m_sql, "getdate()");
	//strcat(m_sql, ", ");
	char endTimeTemp[22];
	TransferEndTime(m_endTime, endTimeTemp);
	strcat(m_sql, endTimeTemp);

	strcat(m_sql, ",");
	sprintf(tmp, "%d", m_inheritGroup);
	strcat(m_sql, tmp);

    char strTmp[81] = {0};
    sprintf(strTmp, ", %d, %d, '%s', %d, 1 ) ", isLink, m_userSource, m_groupList, m_protocolType );
    strTmp[80] = 0;
    strcat(m_sql, strTmp );

	if(m_inheritGroup == 0)
	{
		strcpy(m_sql2, "insert into AdsiAttr(UserAccounts, ACLICP, ACLContent, ");
		strcat(m_sql2, "resv1) values('");
		strcat(m_sql2, areaNo);
		strcat(m_sql2, telNumber);
		strcat(m_sql2, "', '");
		strcat(m_sql2, m_aCLICP);
		strcat(m_sql2, "', '");
		strcat(m_sql2, m_aCLContent);
		strcat(m_sql2, "', '");
		sprintf(tmp, "%d", ((m_sendRecv == 1 || m_sendRecv == 2)?m_sendRecv:3));
		strcat(m_sql2, tmp);
		strcat(m_sql2, "') ");	
	}
	else
		strcpy(m_sql2, "select 1");
}

//generate Delete sql for deleting the records
void CInterfaceContent::GetDeleteSQL()
{
	m_state = 1; //the operation is delete
	RTRACE(3, "begin Delete SQL");
	strcpy(m_sql, "delete from UserAcct where UserAccounts = '");
	strcat(m_sql, m_areaNo);
	strcat(m_sql, m_teleNumber);
	strcat(m_sql, "'");

	strcpy(m_sql2, "delete from AdsiAttr where UserAccounts = '");
	strcat(m_sql2, m_areaNo);
	strcat(m_sql2, m_teleNumber);
	strcat(m_sql2, "'");
}

//generate Delete sql for deleting the records
void CInterfaceContent::GetDeleteSQL(char * tel)
{
	m_state = 1; //the operation is delete
	RTRACE(3, "begin Delete SQL");
	strcpy(m_sql, "delete from UserAcct where UserAccounts = '");
	strcat(m_sql, tel);
	strcat(m_sql, "'");

	strcpy(m_sql2, "delete from AdsiAttr where UserAccounts = '");
	strcat(m_sql2, tel);
	strcat(m_sql2, "'");
}

void CInterfaceContent::SetUserStatus(int userStatus)
{
	SetUserStatus(userStatus, m_areaNo, m_teleNumber);
}

void CInterfaceContent::SetUserStatus(int userStatus, char * areaNo, char * telNumber)
{
	char tmp[2];
	char buffer[41];
	RTRACE(3, "Change the status of the UserAcct");
	strcpy(m_sql, "update UserAcct set UserStatus = ");
	sprintf(tmp, "%d", userStatus);
	strcat(m_sql, tmp);
	strcat(m_sql, " , ModifyTime = getdate() where UserAccounts = '");
	sprintf(buffer, "%s%s", areaNo, telNumber);
	strcat(m_sql, buffer);
	strcat(m_sql, "'");
	m_sql2[0] = '\0';
	return;
}

void CInterfaceContent::LinkInsert(char * account, char * linkAccount)
{
	char buffer[41];
	time_t ltime;
	struct tm * tm1;

    time(&ltime);
    tm1 = localtime(&ltime);
	strftime(buffer, 128, "%Y-%m-%d %H:%M:%S", tm1);
	strcpy(m_sql, "insert into LinkAcct(Account, Linked, CreateTime, ModifyTime) values('");
	strcat(m_sql, account);
	strcat(m_sql, "', '");
	strcat(m_sql, linkAccount);
	strcat(m_sql, "', '");
	strcat(m_sql, buffer);
	strcat(m_sql, "', getdate())");
	m_sql2[0] = '\0';
	return;
}

void CInterfaceContent::LinkDelete(char * account)
{
	strcpy(m_sql, "delete from LinkAcct where Account = '");
	strcat(m_sql, account);
	strcat(m_sql, "'");
	m_sql2[0] = '\0';
	return;
}

void CInterfaceContent::LinkedDelete(char * account)
{
	strcpy(m_sql, "delete from LinkAcct where Linked = '");
	strcat(m_sql, account);
	strcat(m_sql, "'");
	m_sql2[0] = '\0';
	return;
}


// When some accounts link to oldTel, you can excute LinkChange
// and they will link to newTel
void CInterfaceContent::LinkChange(char * oldTel, char * newTel)
{
	char sql[SQL_SIZE];
	
	RTRACE(6, "Enter function LinkChange");

	strcpy(sql, "select Account from LinkAcct where Linked = '");
	strcat(sql, oldTel);
	strcat(sql, "'");
	RTRACE(5, "Execute sql in LinkChange: " << sql);
	dbclrbuf(m_dbproc, sizeof(sql));
	dbcmd(m_dbproc, sql);
	if(dbsqlexec(m_dbproc) != SUCCEED)
	{
		RTRACE(4, "Record LinkAcct record fail");
		while ( (m_retRow = dbnextrow(m_dbproc)) != NO_MORE_ROWS )
			if ( m_retRow == FAIL )
				break;
		dbcancel(m_dbproc);
		return ; // error! Can't delete
	}
	dbresults(m_dbproc);

	DBCHAR dbAccount[21];
	char accountLink[21];
	vector<SqlString> sqlSet;
	SqlString tmp;

	dbbind(m_dbproc, 1, STRINGBIND, (DBINT) 0, (unsigned char *)dbAccount);

	while(dbnextrow(m_dbproc) != NO_MORE_ROWS)
	{
		strcpy(accountLink, (char *)dbAccount);
		RTRACE(5, "The linking account is " << accountLink);

		LinkDelete(accountLink);
		strncpy(tmp.str, m_sql, sizeof(m_sql));
		sqlSet.push_back(tmp);

		LinkInsert(accountLink, newTel);
		strncpy(tmp.str, m_sql, sizeof(m_sql));
		sqlSet.push_back(tmp);
	}

	for(int i=0;i<sqlSet.size();i++)
	{
		strncpy(m_sql, sqlSet[i].str, sizeof(m_sql));
		ExecSQL(m_sql, "");
	}
	ExecSQL("delete from LinkAcct where Account = Linked", "");
	dbcancel(m_dbproc);
}

//Whether to delete the record from UserAcct that it has been depicted deleted
int CInterfaceContent::DeleteRecord(char * userAccounts)
{
	RTRACE(6, "Enter the function of DeleteRecord");

	if(CGlobal::Instance()->m_useNow == 1) // delete now
		return 0;

	char sql[SQL_SIZE];
	int defaultTime; //the default time for charging
	int iRes = 0;
	int nowDay, nowMonth, modifyDay, modifyMonth; // the number of day and month 
	int nowYear, modifyYear;
	DBINT dbDay;
	DBINT dbNowDay, dbNowMonth, dbModifyDay, dbModifyMonth;
	DBINT dbNowYear, dbModifyYear;

	RTRACE(5, "Enter DeleteRecord");
	strcpy(sql, "select day =								\
						case EndTime						\
						when NULL then DefaultTime/60/24	\
						else DATEPART(dd, EndTime)			\
						end									\
						from StatEndTime					\
						");
	RTRACE(5, "Execute sql : " << sql);
	dbclrbuf(m_dbproc, sizeof(sql));
	dbcmd(m_dbproc, sql);
	if(dbsqlexec(m_dbproc) != SUCCEED)
	{
		RTRACE(3, "Record StatEndTime record fail");
		while ( (m_retRow = dbnextrow(m_dbproc)) != NO_MORE_ROWS )
			if ( m_retRow == FAIL )
				break;
		dbcancel(m_dbproc);
		return (-1); // error! Can't delete
	}
	dbresults(m_dbproc);
	dbbind(m_dbproc, 1, INTBIND, (DBINT) 0, (BYTE *)&dbDay);
	while(dbnextrow(m_dbproc) != NO_MORE_ROWS)
		defaultTime = (int)dbDay;
	dbcancel(m_dbproc);
	RTRACE(4, "The default time is " << defaultTime);

	strcpy(sql, "select DATEPART(dd, ModifyTime), DATEPART(mm, ModifyTime), ");
	strcat(sql, "DATEPART(dd, getdate()), DATEPART(mm, getdate()), ");
	strcat(sql, "DATEPART(yy, ModifyTime), DATEPART(yy, getdate()) from UserAcct ");
	strcat(sql, "where UserAccounts = '");
	strcat(sql, userAccounts);
	strcat(sql, "'");
	dbclrbuf(m_dbproc, sizeof(sql));
	RTRACE(5, "Execute sql : " << sql);
	dbcmd(m_dbproc, sql);
	if(dbsqlexec(m_dbproc) != SUCCEED)
	{
		RTRACE(3, "Record UserAcct record fail");
		while ( (m_retRow = dbnextrow(m_dbproc)) != NO_MORE_ROWS )
			if ( m_retRow == FAIL )
				break;
		dbcancel(m_dbproc);
		return (-1); // error! Can't delete
	}
	dbresults(m_dbproc);
	dbbind(m_dbproc, 1, INTBIND, (DBINT) 0, (BYTE *)&dbModifyDay);
	dbbind(m_dbproc, 2, INTBIND, (DBINT) 0, (BYTE *)&dbModifyMonth);
	dbbind(m_dbproc, 3, INTBIND, (DBINT) 0, (BYTE *)&dbNowDay);
	dbbind(m_dbproc, 4, INTBIND, (DBINT) 0, (BYTE *)&dbNowMonth);
	dbbind(m_dbproc, 5, INTBIND, (DBINT) 0, (BYTE *)&dbModifyYear);
	dbbind(m_dbproc, 6, INTBIND, (DBINT) 0, (BYTE *)&dbNowYear);
	while(dbnextrow(m_dbproc) != NO_MORE_ROWS)
	{
		nowDay = (int)dbNowDay;
		nowMonth = (int)dbNowMonth;
		modifyDay = (int)dbModifyDay;
		modifyMonth = (int)dbModifyMonth;
		modifyYear = (int)dbModifyYear;
		nowYear = (int)dbNowYear;
	}

	RTRACE(4, "The month is " << nowMonth);
	RTRACE(4, "The day is " << nowDay);
	RTRACE(4, "The year is " << nowYear);
	RTRACE(4, "The modified month is " << modifyMonth);
	RTRACE(4, "The modified day is " << modifyDay);
	RTRACE(4, "The modified year is " << modifyYear);

	if(nowYear == modifyYear && 
			nowMonth == modifyMonth && 
			((nowDay >= defaultTime && modifyDay >= defaultTime) ||
			(nowDay <= defaultTime && modifyDay <= defaultTime)))
		iRes = -1;
	else if(nowYear == modifyYear && 
		nowMonth - modifyMonth == 1 && 
		nowDay <= 20 && modifyDay >= 20)
		iRes = -1;
	else if(nowYear - modifyYear == 1 && 
		nowMonth == 1 && modifyMonth == 12 && 
		nowDay <= defaultTime && modifyDay >= defaultTime)
		iRes = -1;
	else if(nowYear < modifyYear) // Illegal date type
		iRes = -1;
	else if(nowYear == modifyYear && nowMonth < modifyMonth) // Illegal date type
		iRes = -1;
	else if(nowYear == modifyYear && nowMonth == modifyMonth && nowDay < modifyDay) // Illegal date type
		iRes = -1;
	else
		iRes = 0;
	dbcancel(m_dbproc);
	RTRACE(5, "Can update : " << iRes);
	return iRes;
}

void CInterfaceContent::TransferEndTime(char * endTime, char * buf)
{
	if(strcmp(endTime, "") == 0)
		strcpy(buf, "NULL");
	else
		sprintf(buf, "'%s'", endTime);
}

int CInterfaceContent::GetLicenseInfo()
{
	char sql[SQL_SIZE];
	DBINT dbUserNum, dbTotalNum;
	
	RTRACE(6, "Goto GetLicenseInfo");
	
	strcpy(sql, "select UserNumber from Version");
	RTRACE(5, "Execute sql:" << sql);

	if(m_license == -1) //first, get license number
	{
		//clear db buffer
	    dbclrbuf(m_dbproc, sizeof(sql));
		dbcmd(m_dbproc, sql);
		if(dbsqlexec(m_dbproc) != SUCCEED)
		{