charge_stat.cpp

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

	lpwrite=(char *)malloc(retlong+1);
	strcpy(lpwrite,cDatabase->ret);
	len=strlen(lpwrite);
	
	
	//反销统计
	sprintf(lsSql,
	"SELECT STAFF.staff_id,STAFF.name,0,0,NVL(sum(BILL.amount/100.00),0),NVL(count(DISTINCT BILL.acct_id),0),  "
		"0,0,0,0,0,0,0,0,0,0  "
	"FROM BILL,STAFF  "
	"WHERE BILL.state = '40R' AND  "
		"STAFF.staff_id = BILL.staff_id AND  "
		"STAFF.site_id = %d AND  "
		"BILL.pay_date >= %s AND  "
		"BILL.pay_date < %s  "
	"GROUP BY STAFF.staff_id,STAFF.name  "
        ,site_id,start_date,end_date);
	
	////userlog(lsSql);
	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");
		cDatabase->Rollback();
		if(WriteLog(cDatabase,staff_id,"w_p_sta_dayreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
	retlong=strlen(cDatabase->GetData());
	lperase=(char *)malloc(retlong+1);
	strcpy(lperase,cDatabase->ret);
	len=len+strlen(lperase);
	
	//预付款统计
	sprintf(lsSql,
	"SELECT STAFF.staff_id,  "
		"STAFF.name,  "
		"0,0,0,0,  "
		"NVL(sum(BALANCE_HIST_ADMIN.amount/100.00),0),  "
		"NVL(count(DISTINCT BALANCE_HIST_ADMIN.acct_id),0),  "
		"0,0,0,0,0,0,0,0  "
	"FROM BALANCE_HIST_ADMIN,STAFF  "
	"WHERE BALANCE_HIST_ADMIN.use_catg in ( '1','2','3') AND  "
		"BALANCE_HIST_ADMIN.staff_id = STAFF.staff_id AND  "
		"BALANCE_HIST_ADMIN.state='01' AND "
		"STAFF.site_id = %d AND  "
		"BALANCE_HIST_ADMIN.use_date >= %s AND  "
		"BALANCE_HIST_ADMIN.use_date < %s  "
	"GROUP BY STAFF.staff_id,STAFF.name  "
          ,site_id,start_date,end_date);
	
	////userlog(lsSql);
	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");
		cDatabase->Rollback();
		if(WriteLog(cDatabase,staff_id,"w_p_sta_dayreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
	retlong=strlen(cDatabase->GetData());
	lpbal=(char *)malloc(retlong+1);
	strcpy(lpbal,cDatabase->ret);
	len=len+strlen(lpbal);

	
	//分期付款结清统计
	sprintf(lsSql,
	"SELECT STAFF.staff_id,  "
		"STAFF.name,  "
		"0,0,0,0,0,0,  "
		"NVL(sum(INSTALMENT_BALANCE.charge_current/100.00),0),  "
		"NVL(count(DISTINCT INSTALMENT_BALANCE.acct_id),0),  "
		"0,0,0,0,0,0  "
	"FROM INSTALMENT_BALANCE,STAFF  "
	"WHERE INSTALMENT_BALANCE.operation_type in ( '01','04','05') AND  "
		"INSTALMENT_BALANCE.staff_id = STAFF.staff_id AND  "
		"STAFF.site_id = %d AND  "
		"INSTALMENT_BALANCE.state_date >= %s AND  "
		"INSTALMENT_BALANCE.state_date < %s  "
	"GROUP BY STAFF.staff_id,STAFF.name  "
          ,site_id,start_date,end_date);
	
	////userlog(lsSql);
	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");
		cDatabase->Rollback();
		if(WriteLog(cDatabase,staff_id,"w_p_sta_dayreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
	retlong=strlen(cDatabase->GetData());
	lpinstal=(char *)malloc(retlong+1);
	strcpy(lpinstal,cDatabase->ret);
	len=len+strlen(lpinstal);

	//现金交纳金额统计
	sprintf(lsSql,
	"SELECT STAFF.staff_id,STAFF.name,  "
		"0,0,0,0,0,0,0,0,  "
		"NVL(sum(PAYMENT.amount/100.00),0),  "
		"0,0,0,0,0  "
	"FROM PAYMENT,STAFF  "
	"WHERE PAYMENT.state in ('C0C','C0R','C0S') AND    "
		"PAYMENT.staff_id = STAFF.staff_id AND  "
		"STAFF.site_id = %d AND  "
		"PAYMENT.pay_date >= %s AND    "
		"PAYMENT.pay_date < %s  AND   "
		"PAYMENT.payment_method = '11'  "
	"GROUP BY STAFF.staff_id,STAFF.name  "
          ,site_id,start_date,end_date);
	
	////userlog(lsSql);
	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");
		cDatabase->Rollback();
		if(WriteLog(cDatabase,staff_id,"w_p_sta_dayreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
	retlong=strlen(cDatabase->GetData());
	lpcash_amt=(char *)malloc(retlong+1);
	strcpy(lpcash_amt,cDatabase->ret);
	len=len+strlen(lpcash_amt);
	
	
	//现金交纳户数统计
	sprintf(lsSql,
	"SELECT STAFF.staff_id,STAFF.name,  "
		"0,0,0,0,0,0,0,0,0,  "
		"NVL(count(DISTINCT BILL.acct_id),0),  "
		"0,0,0,0  "
	"FROM PAYMENT,BILL,STAFF  "
	"WHERE PAYMENT.pay_serial_nbr = BILL.pay_serial_nbr AND  "
		"PAYMENT.staff_id = STAFF.staff_id AND  "
		"STAFF.site_id = %d AND  "
		"BILL.state = '40C' AND  "
		"PAYMENT.state in ('C0C','C0R','C0S') AND    "
		"PAYMENT.pay_date >= %s AND    "
		"PAYMENT.pay_date < %s  AND  "
		"PAYMENT.payment_method = '11'  "
	"GROUP BY STAFF.staff_id,STAFF.name  "
          ,site_id,start_date,end_date);
	
	////userlog(lsSql);
	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");
		cDatabase->Rollback();
		if(WriteLog(cDatabase,staff_id,"w_p_sta_dayreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
	retlong=strlen(cDatabase->GetData());
	lpcash_num=(char *)malloc(retlong+1);
	strcpy(lpcash_num,cDatabase->ret);
	len=len+strlen(lpcash_num);

	//支票交纳金额统计
	sprintf(lsSql,
	"SELECT STAFF.staff_id,  "
		"STAFF.name,  "
		"0,0,0,0,0,0,0,0,0,0,  "
		"NVL(sum(PAYMENT.amount/100.00),0),  "
		"0,0,0  "
	"FROM PAYMENT,STAFF  "
	"WHERE PAYMENT.state in ('C0C','C0R','C0S') AND    "
		"PAYMENT.staff_id = STAFF.staff_id AND  "
		"STAFF.site_id = %d AND  "
		"PAYMENT.pay_date >= %s AND    "
		"PAYMENT.pay_date < %s AND    "
		"PAYMENT.payment_method = '0'  "
	"GROUP BY STAFF.staff_id,STAFF.name  "
          ,site_id,start_date,end_date);
	
	////userlog(lsSql);
	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");
		cDatabase->Rollback();
		if(WriteLog(cDatabase,staff_id,"w_p_sta_dayreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
	retlong=strlen(cDatabase->GetData());
	lpcheck_amt=(char *)malloc(retlong+1);
	strcpy(lpcheck_amt,cDatabase->ret);
	len=len+strlen(lpcheck_amt);
	
	
	//支票交纳户数统计
	sprintf(lsSql,
	"SELECT STAFF.staff_id,  "
		"STAFF.name,  "
		"0,0,0,0,0,0,0,0,0,0,0,  "
		"NVL(count(DISTINCT BILL.acct_id),0),  "
		"0,0  "
		"FROM PAYMENT,BILL,STAFF  "
	"WHERE PAYMENT.pay_serial_nbr = BILL.pay_serial_nbr AND  "
		"PAYMENT.staff_id = STAFF.staff_id AND  "
		"STAFF.site_id = %d AND  "
		"BILL.state = '40C' AND  "
		"PAYMENT.state in ('C0C','C0R','C0S') AND    "
		"PAYMENT.pay_date >= %s AND    "
		"PAYMENT.pay_date < %s AND    "
		"PAYMENT.payment_method = '0'  "
	"GROUP BY STAFF.staff_id,STAFF.name     "
          ,site_id,start_date,end_date);
	
	////userlog(lsSql);
	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");
		cDatabase->Rollback();
		if(WriteLog(cDatabase,staff_id,"w_p_sta_dayreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
	retlong=strlen(cDatabase->GetData());
	lpcheck_num=(char *)malloc(retlong+1);
	strcpy(lpcheck_num,cDatabase->ret);
	len=len+strlen(lpcheck_num);	
	
	//信用卡交纳金额统计
	sprintf(lsSql,
	"SELECT STAFF.staff_id,  "
		"STAFF.name,  "
		"0,0,0,0,0,0,0,0,0,0,0,0,  "
		"NVL(sum(PAYMENT.amount/100.00),0),  "
		"0  "
		"FROM PAYMENT,STAFF  "
	"WHERE PAYMENT.state in ('C0C','C0R','C0S') AND    "
		"PAYMENT.staff_id = STAFF.staff_id AND  "
		"STAFF.site_id = %d AND  "
		"PAYMENT.pay_date >= %s AND    "
		"PAYMENT.pay_date < %s AND    "
		"PAYMENT.payment_method = 'kk'  "
	"GROUP BY STAFF.staff_id,STAFF.name  "
          ,site_id,start_date,end_date);
	
	////userlog(lsSql);
	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");
		cDatabase->Rollback();
		if(WriteLog(cDatabase,staff_id,"w_p_sta_dayreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
	retlong=strlen(cDatabase->GetData());
	lpcard_amt=(char *)malloc(retlong+1);
	strcpy(lpcard_amt,cDatabase->ret);
	len=len+strlen(lpcard_amt);
	
	
	//信用卡交纳户数统计
	sprintf(lsSql,
	"SELECT STAFF.staff_id,  "
		"STAFF.name,  "
		"0,0,0,0,0,0,0,0,0,0,0,0,0,  "
		"NVL(count(DISTINCT BILL.acct_id),0)  "
	"FROM PAYMENT,BILL,STAFF  "
	"WHERE PAYMENT.pay_serial_nbr = BILL.pay_serial_nbr AND  "
		"PAYMENT.staff_id = STAFF.staff_id AND  "
		"STAFF.site_id = %d AND  "
		"BILL.state = '40C' AND  "
		"PAYMENT.state in ('C0C','C0R','C0S') AND    "
		"PAYMENT.pay_date >= %s AND    "
		"PAYMENT.pay_date < %s AND    "
		"PAYMENT.payment_method = 'kk'  "
	"GROUP BY STAFF.staff_id,STAFF.name  "
          ,site_id,start_date,end_date);
	
	////userlog(lsSql);
	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");
		cDatabase->Rollback();
		if(WriteLog(cDatabase,staff_id,"w_p_sta_dayreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
	retlong=strlen(cDatabase->GetData());
	lpcard_num=(char *)malloc(retlong+1);
	strcpy(lpcard_num,cDatabase->ret);
	len=len+strlen(lpcard_num);
	
	//构造返回数据字符串
	if (cDatabase->ret) free(cDatabase->ret);
	cDatabase->ret=(char *)malloc(len+10);
	memset(cDatabase->ret,0,len+10);
	//strcpy(lpret,NULL);
	rtrim(ltrim(lpwrite));
	if (strcmp(lpwrite,NULL)!=0&&strcmp(lpwrite,"")!=0)
	{
		strcpy(cDatabase->ret,lpwrite);
		strcat(cDatabase->ret,"\n");
		free(lpwrite);
	}
	rtrim(ltrim(lperase));	
	if (strcmp(lperase,NULL)!=0&&strcmp(lperase,"")!=0)
	{ 	
		strcat(cDatabase->ret,lperase);
		strcat(cDatabase->ret,"\n");
		free(lperase);
	}
	rtrim(ltrim(lpbal));	
	if (strcmp(lpbal,NULL)!=0&&strcmp(lpbal,"")!=0)
	{ 	
		strcat(cDatabase->ret,lpbal);
		strcat(cDatabase->ret,"\n");
		free(lpbal);
	}
	rtrim(ltrim(lpinstal));		
	if (strcmp(lpinstal,NULL)!=0&&strcmp(lpinstal,"")!=0)
	{ 	
		strcat(cDatabase->ret,lpinstal);
		strcat(cDatabase->ret,"\n");
		free(lpinstal);
	}
	rtrim(ltrim(lpcash_amt));		
	if (strcmp(lpcash_amt,NULL)!=0&&strcmp(lpcash_amt,"")!=0)
	{ 	
		strcat(cDatabase->ret,lpcash_amt);
		strcat(cDatabase->ret,"\n");
		free(lpcash_amt);
	}
	rtrim(ltrim(lpcash_num));	
	if (strcmp(lpcash_num,NULL)!=0&&strcmp(lpcash_num,"")!=0)
	{ 	
		strcat(cDatabase->ret,lpcash_num);
		strcat(cDatabase->ret,"\n");
		free(lpcash_num);
	}
	rtrim(ltrim(lpcheck_amt));					
	if (strcmp(lpcheck_amt,NULL)!=0&&strcmp(lpcheck_amt,"")!=0)
	{ 	
		strcat(cDatabase->ret,lpcheck_amt);
		strcat(cDatabase->ret,"\n");
		free(lpcheck_amt);
	}
	rtrim(ltrim(lpcheck_num));	
	if (strcmp(lpcheck_num,NULL)!=0&&strcmp(lpcheck_num,"")!=0)
	{ 	
		strcat(cDatabase->ret,lpcheck_num);
		strcat(cDatabase->ret,"\n");
		free(lpcheck_num);
	}
	rtrim(ltrim(lpcard_amt));	
	if (strcmp(lpcard_amt,NULL)!=0&&strcmp(lpcard_amt,"")!=0)
	{ 	
		strcat(cDatabase->ret,lpcard_amt);
		strcat(cDatabase->ret,"\n");
		free(lpcard_amt);
	}
	rtrim(ltrim(lpcard_num));	
	if (strcmp(lpcard_num,NULL)!=0&&strcmp(lpcard_num,"")!=0)
	{ 	
		strcat(cDatabase->ret,lpcard_num);
		strcat(cDatabase->ret,"\n");
		free(lpcard_num);
	}		
			
	cDatabase->Commit();
	StrReturn(0,cDatabase->ret);
//	free(lpret);
			
}


//-----------------------------------------------------------------
//服务PSCheckBill实现函数，传入参数staff_id,start_date,end_date
//-----------------------------------------------------------------

void CStat::CheckBillList()
{
	char lsSql[2048],lsTemp[1024],lsErr[500];
	char *lparm[5];
	char staff_id[20],start_date[100],end_date[100];
	int i,isqlcode,lpnum,rpnum;
	int rt;
	

	lpnum=3;	
	rpnum=GetInputParm(pInp,lparm,50);
	if(rpnum!=lpnum)
	{