charge_stat.cpp

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

#include "charge_stat.h"
#include "payment.flds.h"

//-----------------------------------------------------------------
//构造函数
//-----------------------------------------------------------------

CStat::CStat(char *pcInp,COracle *pcDatabase)
{
	cDatabase = pcDatabase;
	pInp = pcInp;
}


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

void CStat::StaffBillList()
{
	char lsSql[2048],lsTemp[1024],lsErr[500];
	char *lparm[5],*lpret;
	char *lpwrite=NULL,*lperase=NULL,*lpbalance=NULL,*lpinstal=NULL,*lpbalerase=NULL;
	char staff_id[20],start_date[100],end_date[100];
	int i,isqlcode,lpnum,rpnum;
	int rt;
	long len=0,retlong=0;
	
	lpnum=3;	
	rpnum=GetInputParm(pInp,lparm,50);
	if(rpnum!=lpnum)
	{
		lpfree(lparm,rpnum);
		strcpy(lsTemp,"解析客户端传入参数失败，可能参数传输不正确!\n");
		if(WriteLog(cDatabase,"-1","w_p_sta_dayreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-2,lsTemp);
	}

	strcpy(staff_id,lparm[0]);
	sprintf(start_date,"to_date('%s','yyyy-mm-dd hh24:mi:ss')",lparm[1]);
	sprintf(end_date,"to_date('%s','yyyy-mm-dd hh24:mi:ss')",lparm[2]);
	lpfree(lparm,rpnum);	
		
	
	//销帐数据检索
	sprintf(lsSql,
  "SELECT  '1',  "
          "'40C',  "
           "BILL.bill_serial_nbs ,  "
           "BILL.acct_id ,  "
           "B_ACCT.customer_name ,  "
           "BILL.amount/100,  "
           "BILL.area_code ,  "
           "B_ACCT.acc_nbr ,    "
           "to_char(BILL.created_date,'YYYY-MM-DD HH24:MI:SS') " 
        "FROM BILL ,  "
           "B_ACCT     "
        "WHERE ( BILL.acct_id = B_ACCT.acct_id(+)) and   "
//          "( B_ACCT.state = '10A' or B_ACCT.state is null ) and   "
          "( BILL.staff_id = %s ) and   "
          "( BILL.state in ('40C','40R') ) and   "
          "( BILL.pay_date >= %s ) and   "
          "( BILL.pay_date < %s )   "
          ,staff_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());
	if(retlong>=1)
	{
		lpwrite=(char *)malloc(retlong+1);
		strcpy(lpwrite,cDatabase->ret);
		len=strlen(lpwrite);
	}
       // //userlog(lpwrite);
		
	//反销数据检索
	sprintf(lsSql,
  "SELECT  '1',  "
          "'40R',  "
           "BILL.bill_serial_nbs ,  "
           "BILL.acct_id ,  "
           "B_ACCT.customer_name ,   "
           "-BILL.amount/100,  "
           "BILL.area_code ,  "
           "B_ACCT.acc_nbr  ,   "
           "to_char(BILL.created_date,'YYYY-MM-DD HH24:MI:SS') "           
        "FROM BILL ,  "
           "B_ACCT      "
        "WHERE ( BILL.acct_id = B_ACCT.acct_id(+)) and  "
//          "( B_ACCT.state = '10A' or B_ACCT.state is null ) and  "
          "( BILL.staff_id = %s ) and   "
          "( BILL.state = '40R' ) and   "
          "( BILL.pay_date >= %s ) and   "
          "( BILL.pay_date < %s )    "
	,staff_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());
	if(retlong>=1)
	{
		lperase=(char *)malloc(retlong+1);
		strcpy(lperase,cDatabase->ret);
		len=len+strlen(lperase);
	}


	//预付款数据检索
	sprintf(lsSql,
  "SELECT '2', "
          "BALANCE_HIST_ADMIN.use_catg, "
	 "BALANCE_HIST_ADMIN.balance_hist_admin_id,   "
         "BALANCE_HIST_ADMIN.acct_id,   "
         "B_ACCT.customer_name,   "
         "BALANCE_HIST_ADMIN.amount/100,   "
         "'',  "
         "B_ACCT.acc_nbr,   "
         "to_char(use_date,'YYYY-MM-DD HH24:MI:SS') "
    "FROM BALANCE_HIST_ADMIN,   "
         "B_ACCT  "
   "WHERE ( BALANCE_HIST_ADMIN.acct_id = B_ACCT.acct_id(+) ) and   "
         "( BALANCE_HIST_ADMIN.use_catg in ('1','2','3') ) and  "
//         "( B_ACCT.state='10A' ) and "
         "( ( BALANCE_HIST_ADMIN.staff_id = %s ) AND "
         "( BALANCE_HIST_ADMIN.use_date >= %s) AND "
         "( BALANCE_HIST_ADMIN.use_date < %s ) ) "
         " order by BALANCE_HIST_ADMIN.use_catg asc "
	,staff_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());
	if(retlong>=1)
	{
		lpbalance=(char *)malloc(retlong+1);
		strcpy(lpbalance,cDatabase->ret);
		//userlog(lpbalance);
		len=len+strlen(lpbalance);
	}
	
  //预付款返销数据检索
  sprintf(lsSql,
  "SELECT '2', "
         "BALANCE_HIST_ADMIN.state, "
	 "BALANCE_HIST_ADMIN.balance_hist_admin_id,   "
         "BALANCE_HIST_ADMIN.acct_id,   "
         "B_ACCT.customer_name,   "
         " - BALANCE_HIST_ADMIN.amount/100,   "
         "'',  "
         "B_ACCT.acc_nbr,   "
         "to_char(use_date,'YYYY-MM-DD HH24:MI:SS') "
    "FROM BALANCE_HIST_ADMIN,   "
         "B_ACCT  "
   "WHERE ( BALANCE_HIST_ADMIN.acct_id = B_ACCT.acct_id(+) ) and   "
         "( BALANCE_HIST_ADMIN.use_catg in ('1','2','3') ) and  "
//         "( B_ACCT.state='10A' ) and "
         "( BALANCE_HIST_ADMIN.state='02') and"
         "( ( BALANCE_HIST_ADMIN.staff_id = %s ) AND "
         "( BALANCE_HIST_ADMIN.use_date >= %s) AND "
         "( BALANCE_HIST_ADMIN.use_date < %s ) ) "
         " order by BALANCE_HIST_ADMIN.use_catg asc "
	,staff_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());
	if(retlong>=1)
	{
		lpbalerase=(char *)malloc(retlong+1);
		strcpy(lpbalerase,cDatabase->ret);
		//userlog(lpbalance);
		len=len+strlen(lpbalerase);
	}
	
	//分期付款数据检索
	sprintf(lsSql,
	 "SELECT '3',   "
         "INSTALMENT_BALANCE.operation_type,     "
			"INSTALMENT_BALANCE.instalment_his_id,   "
         "INSTALMENT_BALANCE.acct_id,   "
         "B_ACCT.customer_name,   "
         "INSTALMENT_BALANCE.charge_current/100,   "
         "'',   "
         "B_ACCT.acc_nbr,	"
       	 "to_char(INSTALMENT_BALANCE.state_date,'YYYY-MM-DD HH24:MI:SS') "
    "FROM INSTALMENT_BALANCE,   "
         "B_ACCT  "
   "WHERE ( INSTALMENT_BALANCE.acct_id = B_ACCT.acct_id(+) ) and   "
         "( INSTALMENT_BALANCE.operation_type in ( '01','04','05') ) and  "
//         "( B_ACCT.state='10A') and "
         "( ( INSTALMENT_BALANCE.staff_id = %s ) AND    "
         "( INSTALMENT_BALANCE.state_date >= %s ) AND  "
         "( INSTALMENT_BALANCE.state_date < %s ) )    "
	,staff_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());
	if(retlong>=1)
	{
		lpinstal=(char *)malloc(retlong+1);
		strcpy(lpinstal,cDatabase->ret);
		len=len+strlen(lpinstal);
	}
	//构造返回数据字符串
	if (cDatabase->ret) free(cDatabase->ret);
	
	cDatabase->ret=(char *)malloc(len+10);

	memset(cDatabase->ret,0,len+10);
   
	if(lpwrite)
	{
		strcat(cDatabase->ret,lpwrite);
		strcat(cDatabase->ret,"\r\n");
	}
      
	if(lperase)
	{
		strcat(cDatabase->ret,lperase);
		strcat(cDatabase->ret,"\r\n");
	}

	if(lpbalance)
	{
		strcat(cDatabase->ret,lpbalance);
		strcat(cDatabase->ret,"\r\n");
	}
	
        if(lpbalerase)
        {
        	strcat(cDatabase->ret,lpbalerase);
        	strcat(cDatabase->ret,"\r\n");
        }
        
	strcat(cDatabase->ret,lpinstal);
     
	if (lpwrite) free(lpwrite);
	if (lperase) free(lperase);
	if (lpbalance) free(lpbalance);
	if (lpbalerase) free(lpbalerase);
	if (lpinstal) free(lpinstal);

	if(cDatabase->ret==NULL)
	{
		cDatabase->Commit();
		free(cDatabase->ret);
		StrReturn(100,NULL);
	}
   
	StrReturn(0,cDatabase->ret);
	
//	free(lpret);	
}


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

void CStat::StaffStat()
{
	char lsSql[2048],lsTemp[1024],lsErr[500],lstmp[20];
	char *lparm[5];
	char staff_id[40],start_date[100],end_date[100];
	int i,isqlcode,lpnum,rpnum;
	int rt,retlong;
	long len;
	
	//统计对象变量声明
	char *write_amount,*write_number,*erase_amount,*erase_number;	
	char *bal_amount,*bal_number,*instal_amount,*instal_number;
	char *cash_amount,*cash_number,*card_amount,*card_number;
	char *check_amount,*check_number,*bal_erase,*bal_erasenum; 
/*	char write_amount[20],write_number[20],erase_amount[20],erase_number[20];	
	char bal_amount[20],bal_number[20],instal_amount[20],instal_number[20];
	char cash_amount[20],cash_number[20],card_amount[20],card_number[20];
	char check_amount[20],check_number[20];*/
	
        char *late_amount;  // 违约金
	lpnum=3;	
	rpnum=GetInputParm(pInp,lparm,50);
	if(rpnum!=lpnum)
	{
		lpfree(lparm,rpnum);
		strcpy(lsTemp,"解析客户端传入参数失败，可能参数传输不正确!\n");
		if(WriteLog(cDatabase,"-1","w_p_sta_dayreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-2,lsTemp);
	}

	sprintf(staff_id,"%s",lparm[0]);
	sprintf(start_date,"to_date('%s','yyyy-mm-dd hh24:mi:ss')",lparm[1]);
	sprintf(end_date,"to_date('%s','yyyy-mm-dd hh24:mi:ss')",lparm[2]);
	lpfree(lparm,rpnum);	
		
	
	//销帐统计
	sprintf(lsSql,
	"SELECT NVL(sum(BILL.amount/100.00),0),NVL(count(DISTINCT BILL.acct_id),0)  "
	"FROM BILL  "
	"WHERE BILL.state in ('40C','40R') AND  "
		"BILL.staff_id = %s AND  "
		"BILL.pay_date >= %s AND  "
		"BILL.pay_date < %s  "
          ,staff_id,start_date,end_date);
	
//	//userlog(lsSql);
	cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
//	//userlog("Sqlcode: %d SqlErrText: %s",isqlcode,cDatabase->GetSqlErrText());
	
	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->data->GetColData(1,1));
	write_amount = (char *) malloc(retlong+1);
	strcpy(write_amount,cDatabase->data->GetColData(1,1));
	retlong = strlen(cDatabase->data->GetColData(1,2));
	write_number = (char *) malloc(retlong+1);
	strcpy(write_number,cDatabase->data->GetColData(1,2));
	
	//反销统计
	sprintf(lsSql,
	"SELECT NVL(sum(BILL.amount/100.00),0),NVL(count(DISTINCT BILL.acct_id),0)  "
	"FROM BILL  "
	"WHERE BILL.state = '40R' AND  "
		"BILL.staff_id = %s AND   "
		"BILL.pay_date >= %s AND  "
		"BILL.pay_date < %s   "
          ,staff_id,start_date,end_date);
	
	////userlog(lsSql);
	cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
	////userlog("Sqlcode: %d SqlErrText: %s",isqlcode,cDatabase->GetSqlErrText());
	
	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->data->GetColData(1,1));
	erase_amount = (char *) malloc(retlong+1);
	strcpy(erase_amount,cDatabase->data->GetColData(1,1));
	retlong = strlen(cDatabase->data->GetColData(1,2));
	erase_number = (char *) malloc(retlong+1);
	strcpy(erase_number,cDatabase->data->GetColData(1,2));
	
	//////////////////////////////////////////////////////////////
	//违约金统计
	sprintf(lsSql,
	"SELECT NVL(sum(B_ACCT_ITEM_O.charge/100.00),0)  "
	"FROM BILL,B_ACCT_ITEM_O "
	"WHERE BILL.state = '40C' AND  "
		"BILL.staff_id = %s AND   "
		"BILL.pay_date >= %s AND  "
		"BILL.pay_date < %s  AND  "
		"BILL.bill_serial_nbr = B_ACCT_ITEM_O.bill_serial_nbr AND "
		"B_ACCT_ITEM_O.acct_item_type_id=1"
          ,staff_id,start_date,end_date);
	
	////userlog(lsSql);
	cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
	////userlog("Sqlcode: %d SqlErrText: %s",isqlcode,cDatabase->GetSqlErrText());
	
	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)