dbadmin.cpp

本源码为移动公司话费查询中间件TUXEDO使用的实例

	_CommandPtr pCmd;
	_RecordsetPtr pRs = NULL;
	HRESULT hr;
	_variant_t	paramval;

	sprintf(pchSQL,"select ltrim(rtrim(a.syspara_value)),ltrim(rtrim(a.syspara_desc)) from systemparameter a ,area b "
		"where a.syspara_code = '%s' AND a.area_id=b.area_id AND ltrim(rtrim(b.area_code)) = "
		"ltrim(rtrim('%s'));",szSysParaCode,szAreaCode);
//	sprintf(pchSQL,"select a.syspara_value,a.syspara_desc from systemparameter a ,area b "
//		"where a.syspara_code = '%s' AND a.area_id=b.area_id;",szSysParaCode);
	try {
		hr = pCmd.CreateInstance(__uuidof(Command));
		if(FAILED(hr))
			return 0;
		pCmd->ActiveConnection = m_pCon;
		pCmd->CommandType = adCmdText;
		pCmd->CommandText = _bstr_t(pchSQL);

		pRs = pCmd->Execute(NULL, NULL, adCmdText);////adCmdUnknown

		if (pRs == NULL )
		{
			return -1;
		}

		pRs->MoveFirst();


		paramval.Clear();
		paramval = pRs->Fields->GetItem(0l)->Value;
		if(paramval.vt != VT_NULL)
			strcpy(szSysParaValue,(char*)_bstr_t(paramval));
		else
			szSysParaValue[0] = '\0';

		if(szSysParaDesc)
		{
			paramval.Clear();
			paramval = pRs->Fields->GetItem(1l)->Value;
			if(paramval.vt != VT_NULL)
				strcpy(szSysParaDesc,(char*)_bstr_t(paramval));
			else
				szSysParaDesc[0] = '\0';
		}
	}
	catch (_com_error &e) {
		szSysParaValue[0] = '\0';
		printf (e.Description());
		return 1;
	}
	
	return 0;
}

//获取话单类型
int CDBAdmin::DBGetCallType(char *szAreaCode,char *szRetParaString, int *pnParaCount)
{
	char pchSQL[516];
	_CommandPtr pCmd;
	_RecordsetPtr pRs = NULL;
	HRESULT hr;
	int nLen = 0;
	_variant_t	paramval;

	*pnParaCount = 0;
	sprintf(pchSQL,"SELECT ltrim(rtrim(a.TYPE_NAME)),ltrim(rtrim(a.EXE_CODE)) from calltype a,"
		" area b WHERE ltrim(rtrim(status)) = '1' AND a.area_id = b.area_id "
		"AND ltrim(rtrim(b.area_code)) = ltrim(rtrim('%s')) ORDER BY pos_key ASC;",szAreaCode);
	try {
		hr = pCmd.CreateInstance(__uuidof(Command));
		if(FAILED(hr))
			return 0;
		pCmd->ActiveConnection = m_pCon;
		pCmd->CommandType = adCmdText;
		pCmd->CommandText = _bstr_t(pchSQL);

		pRs = pCmd->Execute(NULL, NULL, adCmdText);////adCmdUnknown

		if (pRs == NULL )
		{
			return -1;
		}

		pRs->MoveFirst();
		while(!(pRs->ADOEOF)){
			paramval.Clear();
			paramval = pRs->Fields->GetItem(0l)->Value;
			nLen += sprintf(szRetParaString+nLen,"%s\t",(char*)_bstr_t(paramval));

			paramval.Clear();
			paramval = pRs->Fields->GetItem(1l)->Value;
			nLen += sprintf(szRetParaString+nLen,"%s\r\n",(char*)_bstr_t(paramval));

			(*pnParaCount)++;
			pRs->MoveNext();
		}
	}
	catch (_com_error &e) {
		szRetParaString[0] = '\0';
		printf (e.Description());
		return 1;
	}
	
	return 0;
}

/********************************************************************
*function:	增加一营业厅
*parameters:	营业厅地址（名称）
*return:	成功返回此营业厅的ID，
*by:		zhangjinde 2002/6/30
*
********************************************************************/

int CDBAdmin::DBAddBusiness(int &nBusinessId, char *szBusinessAddr)
{
	return 1;
}


/********************************************************************
*function:	统计出在某一打印终端打印的所有人打印详单
*parameters:
*return:	成功 0，
*by:		zhangjinde 2002/6/30
*
********************************************************************/

int CDBAdmin::DBQueryPrtposPrintInfoDetail(long lPrintposId, char *szTime, char **rs)
{
	char *rsbuff;
	long lFields,nLen = 0,nFee,nMallocSize;
	//ADO
	char pchSQL[516];
	_CommandPtr pCmd;
	_RecordsetPtr pRs = NULL;
	HRESULT hr;
	_variant_t	paramval;


	sprintf(pchSQL,"SELECT pi.phone_number,TO_CHAR(pi.print_date,'YYYYMMDD HH24:MI:SS'),NVL(TO_CHAR(pi.acct_month,'YYYYMM'),' '),NVL(pi.line_count,0),NVL(pi.bill_fee,0) \
		 FROM printinfo pi\
		 WHERE pi.prtpos_id = %d \
		 AND TO_CHAR(pi.print_date,'YYYYMMDD')=TO_CHAR(TO_DATE('%s','YYYYMMDD'),'YYYYMMDD');"
		 ,lPrintposId,szTime);
	//ADO
	try {
		hr = pCmd.CreateInstance(__uuidof(Command));
		if(FAILED(hr))
			return 0;
		if ( (rsbuff = (char *)malloc(10240)) == NULL )
		{
			*rs = NULL;
			return 1;
		}
		*rs = rsbuff;
		nMallocSize = 10240;

		pCmd->ActiveConnection = m_pCon;
		pCmd->CommandText = _bstr_t(pchSQL);
		pRs = pCmd->Execute(NULL, NULL, adCmdText);

		if (pRs == NULL )
		{
			free(*rs);
			return -1;
		}
		pRs->MoveFirst();
		lFields = pRs->Fields->Count;
		while(!(pRs->ADOEOF)){
			for(long i =0l ;i<lFields-1;i++){
				paramval.Clear();
				paramval = pRs->Fields->GetItem(i)->Value;
				nLen += sprintf(rsbuff+nLen,"%s\t",(char*)_bstr_t(paramval));
				if(nLen > (nMallocSize - 100))
				{
					nMallocSize *= 2;
					rsbuff = (char*)realloc(rsbuff,nMallocSize);
					*rs = rsbuff;
				}

			}

			paramval.Clear();
			paramval = pRs->Fields->GetItem(i)->Value;
			nFee = atol((char*)_bstr_t(paramval));
			nLen += sprintf(rsbuff+nLen,"%0.2f\r\n",nFee/100.0);

			pRs->MoveNext();
		}
	}
	catch (_com_error &e) {
		free(*rs);
		*rs = NULL;
		printf (e.Description());
		return 1;
	}
	return 0;

}

int CDBAdmin::DBQueryPrtposPrintInfoScope(long lPrintposId, char *szBeginTime, char *szEndTime, char **rs)
{
	long	nErrCode = 0;
	int  nTailCommentLine = 0,nHeadTitleLine,nUsagePaperOfLine,nLen = 0;
	long nLinecnt,nPeopleTimes,nBillFee,lUsedPaper,nBuffLen = 0;
	char *rsbuff;
	char chTailLine[20];

	//ADO
	char pchSQL[516];
	_CommandPtr pCmd;
	_RecordsetPtr pRs = NULL;
	HRESULT hr;
	_variant_t	paramval;


	if ( (rsbuff = (char *)malloc(10240))==NULL )
	{
		*rs = NULL;
		return -1;
	}
	else
		*rs = rsbuff;

	//表尾行数；
	if (DBGetSystemPara("0",(char*)"010102",chTailLine,(char*)NULL) != 0)
	{
		nTailCommentLine = 0;
	}
	else
		nTailCommentLine = atoi(chTailLine);
	//表头行数；
	if (DBGetSystemPara("0",(char*)"010101",chTailLine,(char*)NULL) != 0)
	{
		nHeadTitleLine = 0;
	}
	else
		nHeadTitleLine = atoi(chTailLine);
	
	//打印一行的用纸量(mm)
	if (DBGetSystemPara("0",(char*)"010100",chTailLine,(char*)NULL) != 0)
	{
		nUsagePaperOfLine = 0;
	}
	else
		nUsagePaperOfLine = atoi(chTailLine);


	//ADO
	try {
		sprintf(pchSQL,"SELECT sum(NVL(pi.line_count,0)) lc,NVL(count(*),0) sm ,sum(NVL(pi.bill_fee,0))\
		 FROM printinfo pi,businesspos bp, printpos pp \
		 WHERE pp.pos_no = %d \
		 AND pi.prtpos_id = pp.pos_no \
		 AND bp.business_id = pp.business_id \
                           	 AND (TO_DATE(TO_CHAR(pi.print_date,'YYYYMMDD'),'YYYYMMDD') >=TO_DATE('%s','YYYYMMDD')) \
                             AND (TO_DATE(TO_CHAR(pi.print_date,'YYYYMMDD'),'YYYYMMDD') <=TO_DATE('%s','YYYYMMDD')) ;"
							 ,lPrintposId,szBeginTime,szEndTime);
		hr = pCmd.CreateInstance(__uuidof(Command));
		if(FAILED(hr))
			return 0;
		pCmd->ActiveConnection = m_pCon;
		pCmd->CommandType = adCmdText;
		pCmd->CommandText = _bstr_t(pchSQL);
		pRs = pCmd->Execute(NULL, NULL, adCmdText);

		if (pRs == NULL )
		{
			free(*rs);
			return -1;
		}
		pRs->MoveFirst();

		paramval.Clear();
		paramval = pRs->Fields->GetItem(0l)->Value;
		nLinecnt = atol((char*)_bstr_t(paramval));

 		paramval.Clear();
		paramval = pRs->Fields->GetItem(1l)->Value;
		nPeopleTimes = atol((char*)_bstr_t(paramval));

  		paramval.Clear();
		paramval = pRs->Fields->GetItem(2l)->Value;
		nBillFee = atol((char*)_bstr_t(paramval));


		lUsedPaper =  (nLinecnt+nHeadTitleLine+nTailCommentLine)*nUsagePaperOfLine;
		sprintf(rsbuff,"总计:\t%ld\t%ld\t%ld\t%0.2f\r\n",nPeopleTimes,nLinecnt,nLinecnt*nUsagePaperOfLine+nPeopleTimes*lUsedPaper,nBillFee/100.0);
	}
	catch (_com_error &e) {
		printf (e.Description());
		return 1;
	}

	return 0;

	
}


int CDBAdmin::DBQueryPrtposPrintInfoDetailScope(long lPrintposId, char *szBeginTime, char *szEndTime, char **rs)
{

	char pchSQL[516];
	_CommandPtr pCmd;
	_RecordsetPtr pRs = NULL;
	HRESULT hr;
	_variant_t	paramval;

	char *rsbuff;

	long lFields ,nLen = 0,nMallocSize,nFee;

	sprintf(pchSQL,"SELECT pi.phone_number,TO_CHAR(pi.print_date,'YYYYMMDD HH24:MI:SS'),NVL(TO_CHAR(pi.acct_month,'YYYYMM'),' '),pi.line_count,pi.bill_fee \
		 FROM printinfo pi \
		 WHERE pi.prtpos_id = %d \
		 AND (TO_DATE(TO_CHAR(pi.print_date,'YYYYMMDD'),'YYYYMMDD') >=TO_DATE('%s','YYYYMMDD')) \
                             AND (TO_DATE(TO_CHAR(pi.print_date,'YYYYMMDD'),'YYYYMMDD') <=TO_DATE('%s','YYYYMMDD')) ;",
			lPrintposId,szBeginTime,szEndTime);

	try {
		hr = pCmd.CreateInstance(__uuidof(Command));
		if(FAILED(hr))
			return 0;

		pCmd->ActiveConnection = m_pCon;
		pCmd->CommandText = _bstr_t(pchSQL);
		pRs = pCmd->Execute(NULL, NULL, adCmdText);

		if (pRs == NULL )
		{
			return -1;
		}
		if ( (rsbuff = (char *)malloc(10240)) == NULL )
		{
			*rs = NULL;
			return 1;
		}
		nMallocSize = 10240;
		pRs->MoveFirst();
		lFields = pRs->Fields->Count;
		while(!(pRs->ADOEOF)){
			for(long i =0l ;i<lFields-1;i++){
				paramval.Clear();
				paramval = pRs->Fields->GetItem(i)->Value;
				nLen += sprintf(rsbuff+nLen,"%s\t",(char*)_bstr_t(paramval));
				if(nLen > (nMallocSize - 100))
				{
					nMallocSize *= 2;
					rsbuff = (char*)realloc(rsbuff,nMallocSize);
					*rs = rsbuff;
				}

			}

			paramval.Clear();
			paramval = pRs->Fields->GetItem(i)->Value;
			nFee = atol((char*)_bstr_t(paramval));
			nLen += sprintf(rsbuff+nLen,"%0.2f\r\n",nFee/100.0);

			pRs->MoveNext();
		}
	}
	catch (_com_error &e) {
		free(*rs);
		*rs = NULL;
		printf (e.Description());
		return 1;
	}
	return 0;
}


void CDBAdmin::DBFreeBuff(char *pBuff)
{
	free(pBuff);
}
int CDBAdmin::DBQuerysddrlist(char *phone,char *wsaddrlist)
{
	char pchSQL[256];
	_CommandPtr pCmd;
	_RecordsetPtr pRs = NULL;
	HRESULT hr;
	_variant_t	paramval;
	
	int nPrintLimit = 0;
	
	sprintf(pchSQL,"SELECT addrlist FROM newwsaddrlist WHERE phone= '%s';",phone);
	
	try {
		hr = pCmd.CreateInstance(__uuidof(Command));
		if(FAILED(hr))
			return 0;
		pCmd->ActiveConnection = m_pCon;
		pCmd->CommandType = adCmdText;
		pCmd->CommandText = _bstr_t(pchSQL);
		
		pRs = pCmd->Execute(NULL, NULL, adCmdText);
		
		if (pRs == NULL )
		{
			return -1;
		}		
		pRs->MoveFirst();		
		paramval.Clear();
		paramval = pRs->Fields->GetItem(0l)->Value;
		if(paramval.vt != VT_NULL)
		{
			strcpy(wsaddrlist,(char*)_bstr_t(paramval));
			return 0;
		}
		else
		{
			wsaddrlist = NULL;
			return -1;
		}		
	}
	catch (_com_error &e) {
		printf (e.Description());
		return 1;
	}
	
	return 0;
}