charge_ncpatch.cpp

通信BOSS计费方面的服务器源码,有很多经典的创意,值得借鉴,在UNIX上运行.

		StrReturn(-1,lsTemp);
	}
	
	strcpy(site_id,cDatabase->data->GetColData(1,1));
	
	
	//删除营业员收费统计中间表记录
	sprintf(lsSql,"delete stat_staff_item where stid = %s ",staff_id);
	
	cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
	
	if(isqlcode<0)
	{
		strcpy(lsTemp,"删除营业员收费统计中间表记录出错!\n");
		strcat(lsTemp,cDatabase->GetSqlErrText());strcat(lsTemp,"\n");
		if(WriteLog(cDatabase,staff_id,"w_p_sta_dayreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
	//插入收费明细统计费用
	sprintf(lsSql,"insert into stat_staff_item(stid,staff_id,staff_name,bill_item_id,amount) "
		      "	SELECT %s,c.staff_id,c.name,a.invo_item_id,sum(NVL(a.charge,0))/100.00 "
 			" FROM B_ACCT_ITEM_O a, BILL b,STAFF c "
			" WHERE a.bill_serial_nbr=b.bill_serial_nbr "
			" and b.pay_date >= %s "
		    	" and b.pay_date < %s  "
 		        " and a.state in ('20P','20R','20X','20U') "
         		" and b.state='40C' "
			" and b.staff_id = c.staff_id "
			" and c.site_id = %s "
			" GROUP BY c.staff_id,c.name,a.invo_item_id "
		,staff_id,start_date,end_date,site_id); 
	
	cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
	
	if(isqlcode<0)
	{
		strcpy(lsTemp,"插入收费明细统计数据出错!\n");
		strcat(lsTemp,cDatabase->GetSqlErrText());strcat(lsTemp,"\n");
		if(WriteLog(cDatabase,staff_id,"w_p_sta_dayreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
        //插入预付款用户数据
        sprintf(lsSql,
        "insert into stat_staff_item(stid,staff_id,staff_name,bill_item_id,amount) "
        "select %s,a.staff_id,null,-4000,count(distinct a.acct_id) "
        "from BALANCE_HIST_ADMIN a, staff b "
 	"WHERE a.use_catg in ( '1','2','3') AND  "
		"a.staff_id = b.staff_id AND   "
		"a.use_date >= %s AND "
		"a.use_date < %s  AND "
		"a.state='01' AND "
		"b.site_id = %s "
		"group by a.staff_id "
          ,staff_id,start_date,end_date,site_id);
          
        cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
	
	if(isqlcode<0)
	{
		strcpy(lsTemp,"插入预付金额用户数出错!\n");
		strcat(lsTemp,cDatabase->GetSqlErrText());strcat(lsTemp,"\n");
		if(WriteLog(cDatabase,staff_id,"w_p_sta_dayreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
        //插入预付款统计数据
        sprintf(lsSql,
        "insert into stat_staff_item(stid,staff_id,staff_name,bill_item_id,amount) "
        "select %s,a.staff_id,null,-3000,sum(NVL(a.amount,0))/100.00 "
        "from BALANCE_HIST_ADMIN a,staff b "
 	"WHERE a.use_catg in ( '1','2','3') AND  "
		"a.staff_id =b.staff_id AND   "
		"a.use_date >= %s AND   "
		"a.use_date < %s  AND  "
		"a.state='01' AND "
		"b.site_id = %s "
		"group by a.staff_id "
          ,staff_id,start_date,end_date,site_id);
          
        cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
	
	if(isqlcode<0)
	{
		strcpy(lsTemp,"插入预付金额统计数据出错!\n");
		strcat(lsTemp,cDatabase->GetSqlErrText());strcat(lsTemp,"\n");
		if(WriteLog(cDatabase,staff_id,"w_p_sta_dayreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}

	//插入实收金额统计数据	
	sprintf(lsSql,
	"insert into stat_staff_item(stid,staff_id,staff_name,bill_item_id,amount) "
		       " SELECT  %s,a.staff_id,b.name,-2000,sum(NVL(a.amount,0))/100.00 "
 			" FROM bill a,staff b "
			" where a.state = '40C' "
			" and a.staff_id = b.staff_id "
			" and b.site_id = %s "
			" and a.pay_date >= %s  "
		        " and a.pay_date < %s " 
			" group by a.staff_id ,b.name "
		,staff_id,site_id,start_date,end_date); 
		
	cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
	
	if(isqlcode<0)
	{
		strcpy(lsTemp,"插入实收金额统计数据出错!\n");
		strcat(lsTemp,cDatabase->GetSqlErrText());strcat(lsTemp,"\n");
		if(WriteLog(cDatabase,staff_id,"w_p_sta_dayreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
	//插入实收户数统计数据	
	sprintf(lsSql,
	"insert into stat_staff_item(stid,staff_id,staff_name,bill_item_id,amount) "
		       " SELECT %s, a.staff_id,b.name,-1000,NVL(count(DISTINCT a.acct_id),0) "
 			" FROM bill a,staff b "
			" where a.state = '40C' "
			" and a.staff_id = b.staff_id "
			" and b.site_id = %s "
			" and a.pay_date >= %s  "
		        " and a.pay_date < %s " 
			" group by a.staff_id ,b.name "
		,staff_id,site_id,start_date,end_date);		
		
	cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
	
	if(isqlcode<0)
	{
		strcpy(lsTemp,"插入实收户数统计数据出错!\n");
		strcat(lsTemp,cDatabase->GetSqlErrText());strcat(lsTemp,"\n");
		if(WriteLog(cDatabase,staff_id,"w_p_sta_dayreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}

	//检索营业点收费统计数据
	sprintf(lsSql,"select a.staff_id,a.staff_name,a.bill_item_id,sum(a.amount) "
			" from stat_staff_item a,staff b "
                        " where a.staff_id = b.staff_id "
                        " and a.stid = %s " 
                        " and b.site_id =%s "
                        " group by a.staff_id,a.staff_name, a.bill_item_id "
                        " order by a.staff_id "
		       ,staff_id,site_id );  					
	
	cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();	
	
	if(isqlcode<0)
	{
		strcpy(lsTemp,"检索营业点收费统计数据出错!\n");
		strcat(lsTemp,cDatabase->GetSqlErrText());strcat(lsTemp,"\n");
		if(WriteLog(cDatabase,staff_id,"w_p_sta_dayreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}	
			
	cDatabase->Commit();
	if(isqlcode==0 && cDatabase->GetRowCount()==0) StrReturn(100,NULL);
	StrReturn(0,cDatabase->GetData());
}


//------------------------------------------------------------------------
//服务PPIsMySite调用函数
//取帐目周期数据,参数staff_id,acct_id,acc_nbr
//------------------------------------------------------------------------
void CNCpatch::IsMySiteCharge()
{
	char lsSql[2048],lsTemp[1024],lsErr[500];
	char staff_id[20],acct_id[20],acc_nbr[30];
	char *lparm[3];
	int isqlcode,isqlcode1,lpnum,rpnum;
	int i,j,row=0,lrow=0;
	char exchange_id[10];
				
	lpnum=3;	
	rpnum=GetInputParm(pInp,lparm,50);
	if(rpnum!=lpnum)
	{
		lpfree(lparm,rpnum);
		strcpy(lsTemp,"解析客户端传入参数失败，可能参数数据或传输格式不正确!\n");
		StrReturn(-2,lsTemp);
	}

	strcpy(staff_id,lparm[0]);
	strcpy(acct_id,lparm[1]);
	strcpy(acc_nbr,lparm[2]);	
	
	lpfree(lparm,rpnum);	
	
	sprintf(lsSql,"SELECT exchange_id FROM B_SERV " 
			"WHERE acct_id =  %s "
			"and acc_nbr = '%s' "
			"and state in ('F0A','F0J','F0K','F0L')",acct_id,acc_nbr);
	
	cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
	//userlog("Sqlcode: %d SqlErrText: %s",isqlcode,cDatabase->GetSqlErrText());
	//userlog("检索返回行数：%d",cDatabase->GetRowCount());
	
	if(isqlcode<0)
	{
		strcpy(lsTemp,"取电话号码局向标识出错!\n");
		strcat(lsTemp,cDatabase->GetSqlErrText());strcat(lsTemp,"\n");
		if(WriteLog(cDatabase,staff_id,"w_p_charge",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	row=cDatabase->GetRowCount();
	if(row==0)
	{
		StrReturn(-1,"没有找到电话号码归属局向记录！");	
	}
	
	strcpy(exchange_id,cDatabase->GetData());
	
	ltrim(rtrim(exchange_id));
	
	if(strcmp(exchange_id,NULL)==0||strcmp(exchange_id,"")==0)
		strcpy(exchange_id,"NULL");

	//判断属此局向电话号码是否该到本营业点交费
	sprintf(lsSql,"select a.site_id ,a.exchange_id,a.flag "
			" from b_site_exchange a,staff b "
			" where a.exchange_id=%s "
			" and a.site_id = b.site_id "
 			" and b.staff_id=%s "
 			" and a.flag=0 "
		,exchange_id,staff_id ); 
	
	cDatabase->DoSql(lsSql);
	isqlcode1=cDatabase->GetSqlCode();
	
	if(isqlcode1<0)
	{
		strcpy(lsTemp,"提取局向与营业点交费归属关系出错!\n");
		strcat(lsTemp,cDatabase->GetSqlErrText());strcat(lsTemp,"\n");
		if(WriteLog(cDatabase,staff_id,"w_p_charge",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
	j=0;
	lrow=cDatabase->GetRowCount();
	if(lrow==0)
	{
		//查找正向定义归属关系
		sprintf(lsSql,"select a.site_id ,a.exchange_id,a.flag "
			" from b_site_exchange a,staff b "
			" where a.exchange_id=%s "
			" and a.site_id = b.site_id "
 			" and b.staff_id=%s "
 			" and a.flag=1 "
		,exchange_id,staff_id ); 
	
		cDatabase->DoSql(lsSql);
		isqlcode=cDatabase->GetSqlCode();
		
		if(isqlcode<0)
		{
			strcpy(lsTemp,"提取局向与营业点交费归属关系出错!\n");
			strcat(lsTemp,cDatabase->GetSqlErrText());strcat(lsTemp,"\n");
			if(WriteLog(cDatabase,staff_id,"w_p_charge",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
			cDatabase->Commit();
			StrReturn(-1,lsTemp);
		}
		
		row=cDatabase->GetRowCount();
		if(isqlcode==0&&row>0)
		{
			StrReturn(0,NULL);
			return;
		}
		
		if(row==0)
		{
			sprintf(lsSql,"select b.name "
				" from b_site_exchange a,b_site b "
				" where a.exchange_id=%s "
				" and a.site_id = b.site_id "
 				" and a.flag=1 "
			,exchange_id );
			j=1; 			
		}else
		{
			StrReturn(-1,"提取局向与营业点交费归属关系意外出错!\n");
			return;					
		}	
	}
	
	//取帐户应该交费营业点提示信息
	if(j!=1)
	{
		sprintf(lsSql,"select a.name "
			" from b_site a,exchange b "			
			" where a.area_id = b.area_id "
			" and b.exchange_id = %s "
 			" and a.site_id not in " 
 			" (select c.site_id from b_site_exchange c "
 			"  where c.exchange_id = %s "
 			" and c.flag=0 ) "
		,exchange_id,exchange_id );
	}
	cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
	//userlog("Sqlcode: %d SqlErrText: %s",isqlcode,cDatabase->GetSqlErrText());
	//userlog("检索返回行数：%d",cDatabase->GetRowCount());
	
	if(isqlcode<0)
	{
		strcpy(lsTemp,"提取帐户可以交费营业点信息出错!\n");
		strcat(lsTemp,cDatabase->GetSqlErrText());strcat(lsTemp,"\n");
		if(WriteLog(cDatabase,staff_id,"w_p_charge",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
	row=cDatabase->GetRowCount();
	if(row==0)
	{
		StrReturn(-1,"该帐户不能在该营业点交费,\n并且没有找到可以收取该帐户费用的营业点,\n请速与计费中心联系!");	
	}		
		
	row=cDatabase->GetRowCount();
	if(row>10) row=10;
	
	strcpy(lsTemp,NULL);
	for(i=1;i<=row;i++)
	{