charge_stat.cpp

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

		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 PAYMENT.pay_serial_nbr,  "
			"PAYMENT.acct_id,   "
			"PAYMENT.chequ_nbr,   "
			"PAYMENT.amount/100.00,   "
			"to_char(PAYMENT.pay_date,'YYYY-MM-DD HH24:MI:SS')  "
    	"FROM PAYMENT  "
   	"WHERE ( PAYMENT.staff_id = %s ) AND   "
        	 "( PAYMENT.state_date >= %s ) AND   "
         	 "( PAYMENT.pay_date < %s ) AND   "
		 "( rtrim(ltrim(PAYMENT.chequ_nbr)) is not null )  "
        ,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);
	}
	cDatabase->Commit();
	if(cDatabase->GetRowCount()==0) StrReturn(100,"没有清单记录!");
	StrReturn(0,cDatabase->GetData());
	
}
//////////////////////////////////////////////////
void CStat::AreaQfList(long StaffID,long AreaID,long type)
{
	char lsSql[2048],lsTemp[1024],lsErr[500];
	int isqlcode;
	char staff_id[20];
	sprintf(staff_id,"%ld",StaffID);
		
    if(type==1)
	{
	sprintf(lsSql,
  	"SELECT  domain_payment_method.PAYMENT_METHOD_NAME, "   
    "    B_ACCT_ITEM_O.BILLING_CYCLE_ID, "   
    "   sum(b_acct_item_o.charge)*0.01, "
    "   count(distinct B_ACCT.acct_id) "
    "   FROM B_ACCT,   "
    "     B_ACCT_ITEM_O, "
    "    domain_payment_method "
    "  WHERE  B_ACCT.ACCT_ID = B_ACCT_ITEM_O.ACCT_ID  and "  
    "     B_ACCT.ACCT_SEQ_NBR = B_ACCT_ITEM_O.ACCT_SEQ_NBR and "
    "     B_ACCT.AREA_ID = %d AND  "
    "     B_ACCT_ITEM_O.STATE in ('20C','20Q','20Y','20T') AND "  
 //   "     B_ACCT.STATE = '10A' and "
    "     domain_payment_method.payment_method=B_ACCT.payment_method  "
    "      GROUP BY domain_payment_method.PAYMENT_METHOD_NAME,"   
    "    B_ACCT_ITEM_O.BILLING_CYCLE_ID "
    "     order by    domain_payment_method.PAYMENT_METHOD_NAME,B_ACCT_ITEM_O.BILLING_CYCLE_ID "
        ,AreaID);
    }
    else
    {
    sprintf(lsSql,
  	"SELECT  domain_payment_method.PAYMENT_METHOD_NAME, "   
    "    B_ACCT_ITEM_O.BILLING_CYCLE_ID, "   
    "   sum(b_acct_item_o.charge)*0.01, "
    "   count(distinct B_ACCT.acct_id) "
    "   FROM B_ACCT,   "
    "     B_ACCT_ITEM_O, "
    "    domain_payment_method "
    "  WHERE  B_ACCT.ACCT_ID = B_ACCT_ITEM_O.ACCT_ID  and "  
    "     B_ACCT.ACCT_SEQ_NBR = B_ACCT_ITEM_O.ACCT_SEQ_NBR and "
    "     B_ACCT.SELL_ID = %d AND  "
    "     B_ACCT_ITEM_O.STATE in ('20C','20Q','20Y','20T') AND "  
//    "     B_ACCT.STATE = '10A' and "
    "     domain_payment_method.payment_method=B_ACCT.payment_method  "
    "      GROUP BY domain_payment_method.PAYMENT_METHOD_NAME,"   
    "    B_ACCT_ITEM_O.BILLING_CYCLE_ID "
    "     order by    domain_payment_method.PAYMENT_METHOD_NAME,B_ACCT_ITEM_O.BILLING_CYCLE_ID "
        ,AreaID);
    }
    
    	
	////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);
	}
	cDatabase->Commit();
	if(cDatabase->GetRowCount()==0) StrReturn(100,"没有营业区欠费记录!");
	StrReturn(0,cDatabase->GetData());
}


void CStat::AreaSfList(long StaffID,long AreaID,char *start_date,char *end_date)
{
	char lsSql[2048],lsTemp[1024],lsErr[500];
	int isqlcode;
	char staff_id[20];
	char ls_start[100],ls_end[100];
    long in,out,late,all;
	sprintf(staff_id,"%ld",StaffID);
	
	sprintf(lsSql,
	"DELETE from DUE_SF where staff_id=%d",
	StaffID);	
    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_SFreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
	sprintf(lsSql,
	"DELETE from DUE_LATE where staff_id=%d",
	StaffID);	
    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_SFreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
	sprintf(ls_start,"to_date('%s','yyyy-mm-dd hh24:mi:ss')",start_date);
	sprintf(ls_end,"to_date('%s','yyyy-mm-dd hh24:mi:ss')",end_date);	
	
	sprintf(lsSql,
  	"insert into DUE_SF "
    "select %d,%d,acct_id,nvl(bill.amount,0),0,0,bill.bill_serial_nbr,0,0 "
    "from bill,b_site,staff "
    "where bill.state='40C' "
    "and   bill.pay_date>=%s "
    "and   bill.pay_date<=%s "
    "and   bill.staff_id=staff.staff_id "
    "and   staff.site_id=b_site.site_id "
    "and   b_site.area_id=%d ",
     StaffID ,AreaID,ls_start,ls_end,AreaID);
        	
	////userlog(lsSql);
	cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
	
	if(isqlcode<0)
	{	
		strcpy(lsTemp,"检索营业区收欠费出错!(实收)\n");
		strcat(lsTemp,cDatabase->GetSqlErrText());
		strcat(lsTemp,"\n");
		cDatabase->Rollback();
		if(WriteLog(cDatabase,staff_id,"w_p_sta_SFreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	//cDatabase->Commit();
	if(cDatabase->GetRowCount()==0) StrReturn(100,"没有营业区收费记录!");
	
	sprintf(lsSql,
	"update due_sf a "
    "set recieve=(select sum(b.charge) from b_acct_item_o b "
    "where a.bill_serial_nbr=b.bill_serial_nbr "
    "and a.staff_id=%d "
    "group by b.bill_serial_nbr) "
    "where rowid in (select a.rowid from b_acct_item_o b "
    "where a.bill_serial_nbr=b.bill_serial_nbr "
    "and a.staff_id=%d "
    "group by b.bill_serial_nbr) " ,
     StaffID,StaffID);
    ////userlog(lsSql);
     cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
	
	if(isqlcode<0)
	{	
		strcpy(lsTemp,"检索营业区收欠费出错!(应收)\n");
		strcat(lsTemp,cDatabase->GetSqlErrText());
		strcat(lsTemp,"\n");
		cDatabase->Rollback();
		if(WriteLog(cDatabase,staff_id,"w_p_sta_SFreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
	sprintf(lsSql,
	"insert into DUE_LATE "
    "select %d,a.bill_serial_nbr,sum(nvl(a.charge,0)) "
    "from    b_acct_item_o a,due_sf b "
    "where a.bill_serial_nbr=b.bill_serial_nbr "
    "and   a.acct_item_type_id=1 "
    "and   b.staff_id=%d "
    "group by a.bill_serial_nbr,a.acct_item_type_id ",
    StaffID,StaffID);
	////userlog(lsSql);
	cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
    ////userlog(lsSql);
    if(isqlcode<0)
	{	
		strcpy(lsTemp,"插入营业区违约金出错!\n");
		strcat(lsTemp,cDatabase->GetSqlErrText());
		strcat(lsTemp,"\n");
		cDatabase->Rollback();
		if(WriteLog(cDatabase,staff_id,"w_p_sta_SFreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
	sprintf(lsSql,
	"update DUE_SF a "
    "set late_fee=(select charge from DUE_LATE b "
    "     where a.staff_id=b.staff_id "
    "     and   a.bill_serial_nbr=b.bill_serial_nbr "
    "     and   a.staff_id=%d ) "
    "where rowid in(select a.rowid from DUE_LATE b "
    "     where a.staff_id=b.staff_id "
    "     and   a.bill_serial_nbr=b.bill_serial_nbr "
    "     and   a.staff_id=%d )",
    StaffID,StaffID);
       
    cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
    ////userlog(lsSql);
    if(isqlcode<0)
	{	
		strcpy(lsTemp,"检索营业区收欠费出错(违约金)!\n");
		strcat(lsTemp,cDatabase->GetSqlErrText());
		strcat(lsTemp,"\n");
		cDatabase->Rollback();
		if(WriteLog(cDatabase,staff_id,"w_p_sta_SFreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
	//转入零钱
	sprintf(lsSql,
	"update due_sf  "
    "set change=due-recieve,flag=1 "
    "where due>=recieve "
    "and   staff_id=%d ",
    StaffID);
    ////userlog(lsSql);
    cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
	
	if(isqlcode<0)
	{	
		strcpy(lsTemp,"检索营业区收欠费出错!(转入零钱)\n");
		strcat(lsTemp,cDatabase->GetSqlErrText());
		strcat(lsTemp,"\n");
		cDatabase->Rollback();
		if(WriteLog(cDatabase,staff_id,"w_p_sta_SFreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	
	//转出零钱
	sprintf(lsSql,
	"update due_sf  "
    "set change=recieve-due,flag=2 "
    "where due<recieve "
    "and   staff_id=%d ",
    StaffID);
    ////userlog(lsSql);
    cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
	
	if(isqlcode<0)
	{	
		strcpy(lsTemp,"检索营业区收欠费出错!(转出零钱)\n");
		strcat(lsTemp,cDatabase->GetSqlErrText());
		strcat(lsTemp,"\n");
		cDatabase->Rollback();
		if(WriteLog(cDatabase,staff_id,"w_p_sta_SFreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	cDatabase->Commit();
	
	sprintf(lsSql,
	"select sum(due),sum(late_fee) from DUE_SF "
	"where staff_id=%d ",
	StaffID);
	cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
	
	if(isqlcode<0)
	{	
		strcpy(lsTemp,"检索营业区收欠费出错!(总费用)\n");
		strcat(lsTemp,cDatabase->GetSqlErrText());
		strcat(lsTemp,"\n");
		cDatabase->Rollback();
		if(WriteLog(cDatabase,staff_id,"w_p_sta_SFreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	all=atol(cDatabase->data->GetColData(1,1));
	late=atol(cDatabase->data->GetColData(1,2));
	
	sprintf(lsSql,
	"select sum(change) from DUE_SF "
	"where staff_id=%d and flag=%d ",
	StaffID,1);
	cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
	
	if(isqlcode<0)
	{	
		strcpy(lsTemp,"检索营业区收欠费出错!(零钱)\n");
		strcat(lsTemp,cDatabase->GetSqlErrText());
		strcat(lsTemp,"\n");
		cDatabase->Rollback();
		if(WriteLog(cDatabase,staff_id,"w_p_sta_SFreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	in=atol(cDatabase->data->GetColData(1,1));
    
    sprintf(lsSql,
	"select sum(change) from DUE_SF "
	"where staff_id=%d and flag=%d ",
	StaffID,2);
	cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
	
	if(isqlcode<0)
	{	
		strcpy(lsTemp,"检索营业区收欠费出错!(零钱)\n");
		strcat(lsTemp,cDatabase->GetSqlErrText());
		strcat(lsTemp,"\n");
		cDatabase->Rollback();
		if(WriteLog(cDatabase,staff_id,"w_p_sta_SFreport",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}
	out=atol(cDatabase->data->GetColData(1,1));	
	
	sprintf(lsTemp,"%ld\t%ld\t%ld\t%ld",
	       all,late,in,out);
	
	StrReturn(0,lsTemp);
}

//------------------------------------------------------------------------
//服务 PBGetPresentHist 实现函数,取历史预付赠费信息
//传入参数 balance.acct_id
//------------------------------------------------------------------------
void CStat::GetPresentHist()
{
	char  lsSql[2048],lsTemp[500],lsErr[500],lsReturn[1024];
	int   i,isqlcode,lpnum,rpnum;
	int   li_count=0;
	char staff_id[20],start_date[100],end_date[100],site_id[10];
	char  *lparm[2];
	
	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);
	cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();
	if(isqlcode<0)
	{
		strcpy(lsTemp,"取预付赠费营业点信息出错!\n");
		strcat(lsTemp,cDatabase->GetSqlErrText());strcat(lsTemp,"\n");
		if(WriteLog(cDatabase,"1","w_p_balance",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}	
     	strcpy(site_id,cDatabase->GetData());
     		
	sprintf(lsSql," select a.balance_hist_admin_id,"
	              " b.name, a.acct_id, "
	              " a.amount*0.01,a.present*0.01,to_char(a.eff_date,'yyyy-mm-dd'), "
	              " a.eff_month "
	              " from balance_hist_eff_exp a ,staff b"
	              " where a.staff_id=b.staff_id "
	              " and a.state='01' "
	              " and a.state_date>=%s"
	              " and a.state_date<%s"
	              " and b.site_id=%s "
	              " order by b.name ",start_date,end_date,site_id);
	
	         
	//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");
		if(WriteLog(cDatabase,"1","w_p_balance",lsTemp,lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}	
     	cDatabase->Commit();
	StrReturn(0,cDatabase->GetData());	
}