charge_stat.cpp

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

			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
	retlong = strlen(cDatabase->data->GetColData(1,1));
	late_amount = (char *) malloc(retlong+1);
	strcpy(late_amount,cDatabase->data->GetColData(1,1));
	/////////////////////////////////////////////////////////////
	
	//预付款销帐统计
	sprintf(lsSql,
	"SELECT NVL(sum(BALANCE_HIST_ADMIN.amount/100.00),0),  "
		 "NVL(count(DISTINCT BALANCE_HIST_ADMIN.acct_id),0)  "
	"FROM BALANCE_HIST_ADMIN  "
	"WHERE BALANCE_HIST_ADMIN.use_catg in ( '1','2','3') AND  "
		"BALANCE_HIST_ADMIN.staff_id = %s AND   "
		"BALANCE_HIST_ADMIN.use_date >= %s AND   "
		"BALANCE_HIST_ADMIN.use_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));
	bal_amount = (char *) malloc(retlong+1);
	strcpy(bal_amount,cDatabase->data->GetColData(1,1));
	retlong = strlen(cDatabase->data->GetColData(1,2));
	bal_number = (char *) malloc(retlong+1);
	strcpy(bal_number,cDatabase->data->GetColData(1,2));


	//预付款返销统计
	sprintf(lsSql,
	"SELECT NVL(sum(BALANCE_HIST_ADMIN.amount/100.00),0),  "
		 "NVL(count(DISTINCT BALANCE_HIST_ADMIN.acct_id),0)  "
	"FROM BALANCE_HIST_ADMIN  "
	"WHERE BALANCE_HIST_ADMIN.use_catg in ( '1','2','3') AND  "
		"BALANCE_HIST_ADMIN.staff_id = %s AND   "
		"BALANCE_HIST_ADMIN.state='02' AND "
		"BALANCE_HIST_ADMIN.use_date >= %s AND   "
		"BALANCE_HIST_ADMIN.use_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));
	bal_erase = (char *) malloc(retlong+1);
	strcpy(bal_erase,cDatabase->data->GetColData(1,1));
	retlong = strlen(cDatabase->data->GetColData(1,2));
	bal_erasenum = (char *) malloc(retlong+1);
	strcpy(bal_erasenum,cDatabase->data->GetColData(1,2));
		
	//分期付款结清统计
	sprintf(lsSql,
	"SELECT NVL(sum(INSTALMENT_BALANCE.charge_current/100.00),0),  "
		 "NVL(count(DISTINCT INSTALMENT_BALANCE.acct_id),0)  "
	"FROM INSTALMENT_BALANCE  "
	"WHERE INSTALMENT_BALANCE.operation_type in ( '01','04','05') 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());
	
	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));
	instal_amount = (char *) malloc(retlong+1);
	strcpy(instal_amount,cDatabase->data->GetColData(1,1));
	retlong = strlen(cDatabase->data->GetColData(1,2));
	instal_number = (char *) malloc(retlong+1);
	strcpy(instal_number,cDatabase->data->GetColData(1,2));

	//现金交纳金额统计
/*	sprintf(lsSql,
	"SELECT NVL(sum(PAYMENT.amount/100.00),0)  "
	"FROM PAYMENT   "
	"WHERE PAYMENT.state in ('C0C','C0R','C0S') AND   "
		"PAYMENT.staff_id = %s AND  "
		"PAYMENT.pay_date >= %s AND  "
		"PAYMENT.pay_date < %s AND  "
		"PAYMENT.payment_method in ('11','40','12')  "
          ,staff_id,start_date,end_date);
*/
	sprintf(lsSql,
	"SELECT NVL(sum(PAYMENT.amount/100.00),0)  "
	"FROM PAYMENT   "
	"WHERE PAYMENT.state in ('C0C','C0R','C0S') AND   "
		"PAYMENT.staff_id = %s AND  "
		"PAYMENT.pay_date >= %s AND  "
		"PAYMENT.pay_date < %s AND  "
		"PAYMENT.payment_method not like '5_'  "
          ,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));
	cash_amount = (char *) malloc(retlong+1);
	strcpy(cash_amount,cDatabase->data->GetColData(1,1));
	////userlog("cash_amount:%s\tcash_amount",cash_amount);
	
	
	//现金交纳户数统计
/*	sprintf(lsSql,
	"SELECT NVL(count(DISTINCT BILL.acct_id),0)   "
	"FROM PAYMENT,BILL  "
	"WHERE PAYMENT.pay_serial_nbr = BILL.pay_serial_nbr AND  "
		"PAYMENT.staff_id = %s 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 in ('11','40','12')  "
          ,staff_id,start_date,end_date);
*/
	sprintf(lsSql,
	"SELECT NVL(count(DISTINCT BILL.acct_id),0)   "
	"FROM PAYMENT,BILL  "
	"WHERE PAYMENT.pay_serial_nbr = BILL.pay_serial_nbr AND  "
		"PAYMENT.staff_id = %s 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 not like  '5_'  "
          ,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));
	cash_number = (char *) malloc(retlong+1);
	strcpy(cash_number,cDatabase->data->GetColData(1,1));

	//支票交纳金额统计
	sprintf(lsSql,
	"SELECT NVL(sum(PAYMENT.amount/100.00),0)  "
	"FROM PAYMENT  "
	"WHERE PAYMENT.state in ('C0C','C0R','C0S') AND   "
		"PAYMENT.staff_id = %s AND  "
		"PAYMENT.pay_date >= %s AND   "
		"PAYMENT.pay_date < %s AND   "
		"PAYMENT.payment_method = 'kk'  "
          ,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));
	check_amount = (char *) malloc(retlong+1);
	strcpy(check_amount,cDatabase->data->GetColData(1,1));
	
	
	//支票交纳户数统计
	sprintf(lsSql,
	"SELECT NVL(count(DISTINCT BILL.acct_id),0)  "
		"FROM PAYMENT,BILL  "
	"WHERE PAYMENT.pay_serial_nbr = BILL.pay_serial_nbr AND  "
		"PAYMENT.staff_id = %s 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'  "
          ,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));
	check_number = (char *) malloc(retlong+1);
	strcpy(check_number,cDatabase->data->GetColData(1,1));
	
	//信用卡交纳金额统计
	sprintf(lsSql,
	"SELECT NVL(sum(PAYMENT.amount/100.00),0)  "
		"FROM PAYMENT  "
	"WHERE PAYMENT.state in ('C0C','C0R','C0S') AND  "
		"PAYMENT.staff_id = %s AND  "
		"PAYMENT.pay_date >= %s AND   "
		"PAYMENT.pay_date < %s AND   "
		"PAYMENT.payment_method = 'kk'  "
          ,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));
	card_amount = (char *) malloc(retlong+1);
	strcpy(card_amount,cDatabase->data->GetColData(1,1));
	////userlog("card_amount:%s",card_amount);
	
	
	//信用卡交纳户数统计
	sprintf(lsSql,
	"SELECT NVL(count(DISTINCT BILL.acct_id),0)  "
	"FROM PAYMENT,BILL  "
	"WHERE PAYMENT.pay_serial_nbr = BILL.pay_serial_nbr AND  "
		"PAYMENT.staff_id = %s 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' "
          ,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));
	card_number = (char *) malloc(retlong+1);
	strcpy(card_number,cDatabase->data->GetColData(1,1));
		
	//构造返回数据字符串,连接顺序与客户端数据窗口的外部数据源变量声明顺序相关
	memset(lsTemp,0,1024);
	sprintf(lsTemp,"%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s",
	write_amount,erase_amount,instal_number,bal_amount,cash_amount,card_amount,check_amount,
	write_number,erase_number,instal_amount,bal_number,cash_number,card_number,check_number,late_amount,bal_erase,bal_erasenum);
	////userlog("lsTemp:%s",lsTemp);		
	
	if(write_amount) free(write_amount);
	if(write_number) free(write_number);
	if(erase_amount) free(erase_amount);
	if(erase_number) free(erase_number);
	if(instal_number) free(instal_number);
	if(instal_amount) free(instal_amount);
	if(bal_amount) free(bal_amount);
	if(bal_number) free(bal_number);
	if(cash_amount) free(cash_amount);
	if(cash_number) free(cash_number);
	if(card_amount) free(card_amount);
	if(card_number) free(card_number);
	if(check_amount) free(check_amount);
	if(check_number) free(check_number);
	if(late_amount) free(late_amount);
	if(bal_erase) free(bal_erase);
	if(bal_erasenum) free(bal_erasenum);
	
	cDatabase->Commit();
	StrReturn(0,lsTemp);		
}


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

void CStat::SiteStat()
{
	char lsSql[2048],lsTemp[1024],lsErr[500],lstmp[20];
	char *lparm[5];
	char staff_id[20],start_date[100],end_date[100];
	long site_id;
	int i,isqlcode,lpnum,rpnum;
	int rt,retlong;
	long len;
	
	//返回数据指针变量定义
	char *lpwrite,*lperase,*lpbal,*lpinstal;
	char *lpcash_amt,*lpcash_num,*lpcheck_amt,*lpcheck_num,*lpcard_amt,*lpcard_num;
	char *lpret;
	
	
	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 site_id  "
	  "FROM STAFF  "
	"WHERE staff_id = %s  "
        ,staff_id);
	
	////userlog(lsSql);
	cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
	////userlog("Sqlcode: %d SqlErrText: %s",isqlcode,cDatabase->GetSqlErrText());
	
	if(isqlcode<0 || cDatabase->GetRowCount()!=1)
	{	
		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);
	}
	
	strcpy(lstmp,cDatabase->data->GetColData(1,1));
	site_id=atol(lstmp);

		
	//销帐统计
	sprintf(lsSql,
	"SELECT STAFF.staff_id,STAFF.name,NVL(sum(BILL.amount/100.00),0),NVL(count(DISTINCT BILL.acct_id),0),  "
		"0,0,0,0,0,0,0,0,0,0,0,0  "
	"FROM BILL,STAFF  "
	"WHERE BILL.state in ('40C','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());