dbadmin.cpp

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

	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;
		}

	}
	catch (_com_error &e) {
		printf (e.Description());
		DBSQLError();
		return 1;
	}
	
	return 0;

}

/********************************************************************
*function:	查询某一电话号码的打印情况,是否达到限制的次数
*parameters:	电话号码串
*return:	ATTAINLIMITED（1100）：不许打印，：0：sucess,允许打印，
×		<0：失败.结果集--封装data into socket_client
*by:		zhangjinde 2002/6/30
********************************************************************/

int CDBAdmin::DBQueryPhonePrintInfo(char *szPhoneNumber, char *szAcctMonth,char *szCallType, int *pnPrintedCount)
{
/*	SELECT NVL(COUNT(*),0) FROM printinfo pi 
		WHERE pi.phone_number = phone
			AND TO_CHAR(pi.acct_month,'YYYYMM')=TO_CHAR(TO_DATE(print_month,'YYYYMM'),'YYYYMM');
*/
	char pchSQL[516];
	_CommandPtr pCmd;
	_RecordsetPtr pRs = NULL;
	HRESULT hr;
	_variant_t	paramval;

	int nPrintLimit;


	DBGetSystemPara("0","010103",pchSQL,NULL);
	nPrintLimit = atoi(pchSQL);
	if(nPrintLimit < 1)
		nPrintLimit = 1;


	sprintf(pchSQL,"SELECT NVL(COUNT(*),0) FROM printinfo pi WHERE pi.phone_number = '%s' "
		"AND to_char(pi.acct_month,'YYYYMM')='%s' "
		"AND CALLTYPE_CODE = '%s' ;",
		szPhoneNumber,szAcctMonth,szCallType);

	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;
		*pnPrintedCount = atoi((char*)_bstr_t(paramval));

	}
	catch (_com_error &e) {
		*pnPrintedCount = 1;
		printf (e.Description());
		return 1;
	}

	if(nPrintLimit <= *pnPrintedCount)
		*pnPrintedCount = 0;
	else
		*pnPrintedCount = 1;

	return 0;
}


int CDBAdmin::DBQueryPhonePrintInfoReal(char *szPhoneNumber, char *szAcctMonth, int *pnPrintedCount)
{
	return 1;
}

/********************************************************************
*function:	统计某一电话号码的打印情况
*parameters:	电话号码串
*return:	0：sucess，<0：失败.结果集--封装data into socket_client
*by:		zhangjinde 2002/6/30
*
********************************************************************/

int CDBAdmin::DBPhonePrintInfoStat(char *szPhoneNumber, char **rs)
{
	return 1;
}


/********************************************************************
*function:	查询全网的打印情况
*parameters:	查询方式:0:day,1-12:month
*return:	查询结果集（按协议填充待定）
*by:		zhangjinde 2002/6/30
*
********************************************************************/
////////////////////////////////////////////////////////////////////////////////////////
//	   中国移动北京     总共打印终端的台数     日期范围
//	--------------------------------------------------------------
//	营业厅	终端号	总人次	总行数	总用纸量
//	--------------------------------------------------------------
//	营业厅1	终端号1	总人次	总行数	总用纸量
//		终端号2	总人次	总行数	总用纸量
//	营业厅2	终端号3	总人次	总行数	总用纸量
//		终端号4	总人次	总行数	总用纸量
//	-------------------------------------------------------------
////////////////////////////////////////////////////////////////////////////////////////

int CDBAdmin::DBQueryAllPrintInfo(int nMode, char *szTime, char **rs)
{
	return 0;
}

/********************************************************************
*function:	查询营业厅的打印情况
*parameters:	营业厅标识号,查询方式:0:day,1-12:month
*return:	查询结果集（按协议填充待定）
*by:		zhangjinde 2002/6/30
*
********************************************************************/
////////////////////////////////////////////////////////////////////////////////////////
//		营业厅		总共打印终端的台数	日期范围
//	--------------------------------------------------------------
//		终端名称	总人次	总行数	总用纸量
//	--------------------------------------------------------------
//		终端名称1	总人次	总行数	总用纸量
//		终端名称2	总人次	总行数	总用纸量
//		终端名称3	总人次	总行数	总用纸量
//		终端名称4	总人次	总行数	总用纸量
//	-------------------------------------------------------------
////////////////////////////////////////////////////////////////////////////////////////

int CDBAdmin::DBQueryBusinessPrintInfo(long lBusinessId, int nMode, char *szTime, char **rs)
{
	return 1;
}


/********************************************************************
*function:	查询打印终端的打印信息
*parameters:	打印终端的标识号，查询方式:0:day,other:month
*return:	查询结果集（按协议填充待定）
*by:		zhangjinde 2002/6/30
*
*		可能会有号段的统计要求
********************************************************************/
//////////////////////////////////////////////////////////////////////
//打印信息统计模块应可按号码段、时间段、打印机编号等生成统计打印报表，
//对打印记录数、打印人次、打印用纸量、打印机有效工作时长等进行统计
///////////////////////////////////////////////////////////////////////
//		报表头
//	----------------------------
//	人数，行数，用纸量，日期范围
//	----------------------------
//
//	----------------------------
//		报表尾
///////////////////////////////////////////////////////

int CDBAdmin::DBQueryPrtposPrintInfo(long lPrintposId, int nMode, char *szTime, char **rs)
{
	long	nErrCode = 0;
	int  nTailCommentLine = 0,nHeadTitleLine,nUsagePaperOfLine,nLen = 0;
	long nLinecnt,nPeopleTimes,nBillFee,lUsedPaper,nBuffLen = 0;
	char *rsbuff;
	char chTailLine[20],chOutDay[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;
	nBuffLen = 10240;

	//表尾行数；
	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 {
		hr = pCmd.CreateInstance(__uuidof(Command));
		if(FAILED(hr))
			return 0;
		pCmd->ActiveConnection = m_pCon;
		pCmd->CommandType = adCmdText;

		if (nMode==0)    //按日查询
		{
			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_CHAR(pi.print_date,'YYYYMMDD')=TO_CHAR(TO_DATE('%s','YYYYMMDD'),'YYYYMMDD');"
				 ,lPrintposId,szTime);

			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 = atoi((char*)_bstr_t(paramval));
			//总人数
			paramval.Clear();
			paramval = pRs->Fields->GetItem(1l)->Value;
			nPeopleTimes = atoi((char*)_bstr_t(paramval));
			//总费用
			paramval.Clear();
			paramval = pRs->Fields->GetItem(2l)->Value;
			nBillFee = atoi((char*)_bstr_t(paramval));
			//填充socket packet databody
			lUsedPaper =  (nLinecnt+nHeadTitleLine+nTailCommentLine)*nUsagePaperOfLine;

			nLen += sprintf(rsbuff + nLen,"%ld\t%ld\t%ld\t%0.2f\r\n",nPeopleTimes,nLinecnt,nLinecnt*nUsagePaperOfLine+nPeopleTimes*lUsedPaper,nBillFee/100.0);

		}
		else
		{

			sprintf(pchSQL,"SELECT TO_CHAR(pi.print_date,'YYYYMMDD'),SUM(NVL(pi.line_count,0)) ,NVL(COUNT(*),0), SUM(NVL(pi.bill_fee,0)) \
				 FROM printinfo pi, printpos pp \
				 WHERE pp.pos_no = %d \
				 AND pi.prtpos_id = pp.pos_no \
				 AND TO_CHAR(pi.print_date,'YYYYMM')=TO_CHAR(TO_DATE('%s','YYYYMM'),'YYYYMM') \
				 GROUP BY TO_CHAR(pi.print_date,'YYYYMMDD');"
				 ,lPrintposId,szTime);

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

			if (pRs == NULL )
			{
				free(*rs);
				return -1;
			}
			pRs->MoveFirst();
			while(!(pRs->ADOEOF)){
				paramval.Clear();
				paramval = pRs->Fields->GetItem(0l)->Value;
				strcpy(chOutDay ,(char*)_bstr_t(paramval));

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

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

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

				lUsedPaper =  (nLinecnt+nHeadTitleLine+nTailCommentLine)*nUsagePaperOfLine;

				nLen += sprintf(rsbuff + nLen,"%s\t%ld\t%ld\t%ld\t%0.2f\r\n",
					chOutDay,nPeopleTimes,nLinecnt,nLinecnt*nUsagePaperOfLine+nPeopleTimes*lUsedPaper,nBillFee/100.0);

				if(nLen > (nBuffLen - 100))
				{
					nBuffLen *= 2;
					rsbuff = (char*)realloc(rsbuff,nBuffLen);
					*rs = rsbuff;
				}

				pRs->MoveNext();
			}

			//总计:
			sprintf(pchSQL,"SELECT sum(NVL(pi.line_count,0)) lc,NVL(count(*),0) sm ,sum(NVL(pi.bill_fee,0)) fe \
				 FROM printinfo pi,businesspos bp, printpos pp \
				 WHERE pp.pos_no = %d \
				 AND ltrim(rtrim(pi.prtpos_id)) = ltrim(rtrim(pp.pos_no)) \
				 AND bp.business_id = pp.business_id \
				 AND TO_CHAR(pi.print_date,'YYYYMM')=TO_CHAR(TO_DATE('%s','YYYYMM'),'YYYYMM');"
				 ,lPrintposId,szTime);

				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));

				nLen += sprintf(rsbuff + nLen,"总计:\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;
}

/********************************************************************
*function:	更新打印终端的状态
*parameters:	打印终端的标识号，状态码
*return:
*by:		zhangjinde 2002/6/30
*
********************************************************************/


int CDBAdmin::DBUpdatePrintposStatus(long lPrintposId, char *szStatusCode)
{
	return 1;
}

/********************************************************************
*function:	查询打印终端的状态
*parameters:	打印终端的标识号
*return:	状态码
*by:		zhangjinde 2002/6/30
*
********************************************************************/

int CDBAdmin::DBQueryPrintposStatus(long lPrintposId, char *szStatusCode)
{
	return 1;
}

/********************************************************************
*function:	取得打印终端功能模块的信息
*parameters:	功能模块标识，
*return:	版本号，路径，
*by:		zhangjinde 2002/6/30
*
********************************************************************/


int CDBAdmin::DBGetFuncInfo(long lFuncId,char *szFuncVer, char *szFuncPath)
{
	return 1;
}

/********************************************************************
*function:	查询系统参数的值
*parameters:	系统参数码
*return:	系统参数的值，详细描述
*by:		zhangjinde 2002/6/30
*
********************************************************************/

int CDBAdmin::DBGetSystemPara(char *szAreaCode,char *szSysParaCode, char *szSysParaValue, char *szSysParaDesc)
{

	char pchSQL[516];