charge_erase.cpp

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

#include "charge_erase.h"

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


void CErase::GetEraseBill(char *StaffID,char *ItemType,char *Code,char *OtherArea,char *max_acct_id,char *min_acct_id)
{
	char lsSql[2048],lsTemp[1024],lsErr[500],lsRetstr[2048];
	int isqlcode,lrow,itemtype;
	char acct_id[15],serv_id[11];
	itemtype = atoi(ItemType);

	//userlog("max_acct=%s,min_acct=%s\n",max_acct_id,min_acct_id);
	
	switch (itemtype)
	{
		case 1:  //按合同号提取

			if(strcmp(OtherArea,"0")==0)
			{
				if(atol(Code)<atol(min_acct_id)||atol(Code)>=atol(max_acct_id))
					StrReturn(-1,"不允许进行异地返销");
			}

			sprintf(lsSql,"SELECT /*+ all_rows */ DISTINCT to_char(a.bill_serial_nbr),to_char(b.cycle_end_date,'yyyy-mm-dd'),"
										"to_char(c.pay_serial_nbr),to_char(c.created_date,'yyyy-mm-dd'),c.print_count, "
										 "c.staff_id,a.acct_id,c.amount,c.change,0 flag "
								"FROM B_ACCT_ITEM_O a, "
									  "B_BILLING_CYCLE b,BILL c "
								"WHERE a.billing_cycle_id = b.billing_cycle_id AND "
										"c.bill_serial_nbr = a.bill_serial_nbr AND "
										"a.acct_id = %s AND  "
										"c.state = '40C'  "
										"order by to_char(b.cycle_end_date,'yyyy-mm-dd') DESC,"
										"to_char(a.bill_serial_nbr) DESC "         
								,Code);
                        //userlog(lsSql);
			cDatabase->DoSql(lsSql);
			isqlcode=cDatabase->GetSqlCode();

			if(isqlcode<0)
			{
				strcpy(lsRetstr,cDatabase->GetSqlErrText());
				strcpy(lsTemp,"按合同号提取销帐记录出错");strcat(lsTemp,"\n");
				if(WriteLog(cDatabase,StaffID,"w_p_charge_erase",strcat(lsTemp,lsRetstr),lsErr)!=1)
					strcat(lsTemp,lsErr);
				cDatabase->Commit();
				StrReturn(-1,lsTemp);
			}

			lrow=cDatabase->GetRowCount();
			cDatabase->Commit();
			if(lrow==0) 
			{
				StrReturn(100,"未找到合同号销帐记录");
			}
			StrReturn(0,cDatabase->GetData());

		case 2:  //按电话号提取

			sprintf(lsSql,"SELECT serv_id,acct_id FROM B_SERV WHERE acc_nbr = '%s' and state in ('F0A','F0O','F0J','F0K','F0L')",Code);

			cDatabase->DoSql(lsSql);
			isqlcode=cDatabase->GetSqlCode();

			if(isqlcode<0)
			{
				strcpy(lsRetstr,cDatabase->GetSqlErrText());
				strcpy(lsTemp,"提取用户serv_id信息失败");strcat(lsTemp,"\n");
				if(WriteLog(cDatabase,StaffID,"w_p_charge_erase",strcat(lsTemp,lsRetstr),lsErr)!=1)
					strcat(lsTemp,lsErr);
				cDatabase->Commit();
				StrReturn(-1,lsTemp);
			}

			lrow=cDatabase->GetRowCount();
			if(lrow==0)
			{
				sprintf(lsSql,"SELECT a.serv_id,a.acct_id FROM B_SERV a,B_NBR_GROUP b WHERE b.begin_nbr <= '%s' AND b.end_nbr >= '%s' AND b.serv_id = a.serv_id AND b.state = 'B0A' AND a.state in ('F0A','F0O','F0J','F0K','F0L')",Code,Code);

				cDatabase->DoSql(lsSql);
				isqlcode=cDatabase->GetSqlCode();

				if(isqlcode<0)
				{
					strcpy(lsRetstr,cDatabase->GetSqlErrText());
					strcpy(lsTemp,"提取用户nbr_group信息失败");strcat(lsTemp,"\n");
					if(WriteLog(cDatabase,StaffID,"w_p_charge_erase",strcat(lsTemp,lsRetstr),lsErr)!=1)
						strcat(lsTemp,lsErr);
					cDatabase->Commit();
					StrReturn(-1,lsTemp);
				}

				lrow=cDatabase->GetRowCount();
				if(lrow==0)
				{
					sprintf(lsSql,"SELECT serv_id,acct_id FROM B_ACCT_ITEM_O WHERE -serv_id = %s and serv_id < 0 and state in ('20P','20R','20X','20U','20W','20V')",Code);

					cDatabase->DoSql(lsSql);
					isqlcode=cDatabase->GetSqlCode();

					if(isqlcode<0)
					{
						strcpy(lsRetstr,cDatabase->GetSqlErrText());
						strcpy(lsTemp,"提取用户nbr_group信息失败");strcat(lsTemp,"\n");
						if(WriteLog(cDatabase,StaffID,"w_p_charge_erase",strcat(lsTemp,lsRetstr),lsErr)!=1)
							strcat(lsTemp,lsErr);
						cDatabase->Commit();
						StrReturn(-1,lsTemp);
					}

					lrow=cDatabase->GetRowCount();
					if(lrow==0)
					{
						cDatabase->Commit();
						StrReturn(100,"未找到用户信息");
					}
					else
					{
						rtrim(ltrim(strcpy(serv_id,cDatabase->data->GetColData(1,1))));
						rtrim(ltrim(strcpy(acct_id,cDatabase->data->GetColData(1,2))));
					}
				}
				else
				{
					rtrim(ltrim(strcpy(serv_id,cDatabase->data->GetColData(1,1))));
					rtrim(ltrim(strcpy(acct_id,cDatabase->data->GetColData(1,2))));
				}
			}
			else
			{
				rtrim(ltrim(strcpy(serv_id,cDatabase->data->GetColData(1,1))));
				rtrim(ltrim(strcpy(acct_id,cDatabase->data->GetColData(1,2))));
			}

			if(strcmp(OtherArea,"0")==0)
			{
				if(atol(acct_id)<atol(min_acct_id)||atol(acct_id)>=atol(max_acct_id))
					StrReturn(-1,"不允许进行异地返销");
			}

			sprintf(lsSql,"SELECT  /*+ all_rows */  DISTINCT to_char(a.bill_serial_nbr),to_char(b.cycle_end_date,'yyyy-mm-dd'), "
										 "to_char(c.pay_serial_nbr),to_char(c.created_date,'yyyy-mm-dd'),c.print_count, "
										 "c.staff_id,a.acct_id,c.amount,c.change,0 flag "
								"FROM B_ACCT_ITEM_O a, "
									  "B_BILLING_CYCLE b,BILL c "
								"WHERE a.billing_cycle_id = b.billing_cycle_id AND "
										"c.bill_serial_nbr = a.bill_serial_nbr AND "
										"a.acct_id = %s AND "
										"c.state = '40C'"
                                  "order by to_char(b.cycle_end_date,'yyyy-mm-dd') DESC,"
                                  "to_char(a.bill_serial_nbr) DESC "
								,acct_id);

			cDatabase->DoSql(lsSql);
			isqlcode=cDatabase->GetSqlCode();

			if(isqlcode<0)
			{
				strcpy(lsRetstr,cDatabase->GetSqlErrText());
				strcpy(lsTemp,"按电话号提取销帐记录出错");strcat(lsTemp,"\n");
				if(WriteLog(cDatabase,StaffID,"w_p_charge_erase",strcat(lsTemp,lsRetstr),lsErr)!=1)
					strcat(lsTemp,lsErr);
				cDatabase->Commit();
				StrReturn(-1,lsTemp);
			}

			lrow=cDatabase->GetRowCount();
			cDatabase->Commit();
			if(lrow==0) StrReturn(100,"未找到电话号销帐记录");
			StrReturn(0,cDatabase->GetData());
             case 3:       //按97合同号
             
	                 sprintf(lsSql," select acct_id from b_acct "
	                               " where acct_nbr_97='%s' "
	                               " and  state='10A' "
	                               , Code);
	                  
	                 cDatabase->DoSql(lsSql);
			isqlcode=cDatabase->GetSqlCode();
	                 
			if(isqlcode<0)
			{
				strcpy(lsRetstr,cDatabase->GetSqlErrText());
				strcpy(lsTemp,"未找到有效合同号出错");strcat(lsTemp,"\n");
				if(WriteLog(cDatabase,StaffID,"w_p_charge_erase",strcat(lsTemp,lsRetstr),lsErr)!=1)
					strcat(lsTemp,lsErr);
				cDatabase->Commit();
				StrReturn(-1,lsTemp);
			}    
			lrow=cDatabase->GetRowCount();
			if(lrow==0)
			{
				cDatabase->Commit();
				StrReturn(100,"未找到相关合同号信息");
			}              
	                strcpy(acct_id,cDatabase->GetData());
	                               
			if(strcmp(OtherArea,"0")==0)
			{
				if(atol(Code)<atol(min_acct_id)||atol(Code)>=atol(max_acct_id))
					StrReturn(-1,"不允许进行异地返销");
			}

			sprintf(lsSql,"SELECT /*+ all_rows */ DISTINCT to_char(a.bill_serial_nbr),to_char(b.cycle_end_date,'yyyy-mm-dd'),"
										"to_char(c.pay_serial_nbr),to_char(c.created_date,'yyyy-mm-dd'),c.print_count, "
										 "c.staff_id,a.acct_id,c.amount,c.change,0 flag "
								"FROM B_ACCT_ITEM_O a, "
									  "B_BILLING_CYCLE b,BILL c "
								"WHERE a.billing_cycle_id = b.billing_cycle_id AND "
										"c.bill_serial_nbr = a.bill_serial_nbr AND "
										"a.acct_id = %s AND  "
										"c.state = '40C'  "
										"order by to_char(b.cycle_end_date,'yyyy-mm-dd') DESC,"
										"to_char(a.bill_serial_nbr) DESC "         
					,acct_id);
                        //userlog(lsSql);
			cDatabase->DoSql(lsSql);
			isqlcode=cDatabase->GetSqlCode();

			if(isqlcode<0)
			{
				strcpy(lsRetstr,cDatabase->GetSqlErrText());
				strcpy(lsTemp,"按合同号提取销帐记录出错");strcat(lsTemp,"\n");
				if(WriteLog(cDatabase,StaffID,"w_p_charge_erase",strcat(lsTemp,lsRetstr),lsErr)!=1)
					strcat(lsTemp,lsErr);
				cDatabase->Commit();
				StrReturn(-1,lsTemp);
			}

			lrow=cDatabase->GetRowCount();
			cDatabase->Commit();
			if(lrow==0) 
			{
				StrReturn(100,"未找到合同号销帐记录");
			}
			StrReturn(0,cDatabase->GetData());

		default:
			StrReturn(-1,"未知的销帐类型");
	}
}

void CErase::GetEraseItem(char *StaffID,char *bill_serial_nbr)
{
	char lsSql[2048],lsTemp[1024],lsErr[500],lsRetstr[2048];
	int isqlcode,lrow;

	sprintf(lsSql,"SELECT /*+RULE*/a.change,b.acct_id,b.acct_seq_nbr,b.billing_cycle_id,"
			    " b.bill_item_id,b.special_acct_flag,sum(b.charge / 100 ),"
			    " to_char(c.pay_date,'yyyy-mm-dd'),c.acc_nbr,c.amount,c.staff_id,d.chequ_nbr,"
			    " d.card_nbr,nvl(d.payment_method,'40') "
		       " FROM B_ACCT a , "
			     "B_ACCT_ITEM_O b , "
			     "BILL c ,PAYMENT d "
		      " WHERE b.acct_id = a.acct_id(+) "
			" AND b.bill_serial_nbr = c.bill_serial_nbr "
			" AND c.pay_serial_nbr = d.pay_serial_nbr(+) "
			" AND c.bill_serial_nbr = %s "
			" AND a.state = '10A' "
			" AND b.state in ('20P','20R','20X','20U','20W','20V') "
		      " GROUP BY a.change,b.acct_id,b.acct_seq_nbr,b.billing_cycle_id, "
				"b.bill_item_id,b.special_acct_flag,to_char(c.pay_date,'yyyy-mm-dd'),c.acc_nbr, "
				"c.amount,c.staff_id,d.chequ_nbr,d.card_nbr,d.payment_method "
	,bill_serial_nbr);
       //userlog(lsSql);
	cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();

	if(isqlcode<0)
	{
		strcpy(lsRetstr,cDatabase->GetSqlErrText());
		strcpy(lsTemp,"按返销详细帐目出错");strcat(lsTemp,"\n");
		if(WriteLog(cDatabase,StaffID,"w_p_charge_erase",strcat(lsTemp,lsRetstr),lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}

	lrow=cDatabase->GetRowCount();
	cDatabase->Commit();
	if(lrow==0) StrReturn(100,"未找到返销详细帐目");
	StrReturn(0,cDatabase->GetData());
}

void CErase::UndoBill(char *StaffID,char *grade,char *unpay,char *otherunpay,char *othersite,char *bill_serial_nbr)
{
	char lsSql[600],lsTemp[1024],lsErr[500],lsRetstr[500],staff_id[7],pay_serial_nbr[19];
	int isqlcode,lrow;
	char acct_id[9],billing_cycle_id[7],charge[11];

	//if(atol(grade)>=4&&atol(unpay)==0)
	if(atol(unpay)==0)
		StrReturn(-1,"员工没有返销操作的权限!");

	strncpy(staff_id,bill_serial_nbr,strlen(bill_serial_nbr)-12);
	staff_id[strlen(bill_serial_nbr)-12]=0;

	//if(atol(grade)>=4&&strcmp(StaffID,staff_id)!=0)
		//StrReturn(-1,"不能反销其它营业员的销帐!");

	//if(atol(grade)>3&&strcmp(StaffID,staff_id)!=0&&atol(otherunpay)==0)
	if(strcmp(StaffID,staff_id)!=0&&atol(otherunpay)==0)
		StrReturn(-1,"不能反销其它营业员的销帐!");

	if(atol(othersite)==0)
	{
		sprintf(lsSql,"SELECT site_id FROM STAFF WHERE staff_id = %s AND state='K0A'",StaffID);

		cDatabase->DoSql(lsSql);
		isqlcode=cDatabase->GetSqlCode();

		if(isqlcode<0)
		{
			strcpy(lsRetstr,cDatabase->GetSqlErrText());
			strcpy(lsTemp,"查找反销员工营业点出错");strcat(lsTemp,"\n");
			if(WriteLog(cDatabase,StaffID,"w_p_charge_erase",strcat(lsTemp,lsRetstr),lsErr)!=1)
				strcat(lsTemp,lsErr);
			cDatabase->Commit();
			StrReturn(-1,lsTemp);
		}

		lrow=cDatabase->GetRowCount();
		if(lrow==0) StrReturn(100,"未找到反销员工营业点");
		sprintf(lsTemp,cDatabase->GetData());

		sprintf(lsSql,"SELECT site_id FROM STAFF WHERE staff_id = %s AND state='K0A'",staff_id);

		cDatabase->DoSql(lsSql);
		isqlcode=cDatabase->GetSqlCode();

		if(isqlcode<0)
		{
			strcpy(lsRetstr,cDatabase->GetSqlErrText());
			strcpy(lsTemp,"查找被反销员工营业点出错");strcat(lsTemp,"\n");
			if(WriteLog(cDatabase,StaffID,"w_p_charge_erase",strcat(lsTemp,lsRetstr),lsErr)!=1)
				strcat(lsTemp,lsErr);
			cDatabase->Commit();
			StrReturn(-1,lsTemp);
		}

		lrow=cDatabase->GetRowCount();
		if(lrow==0) StrReturn(100,"未找到被反销员工营业点");

		if(strcmp(lsTemp,cDatabase->GetData())!=0)
			StrReturn(-1,"不允许反销其他营业点的员工!");
	}

	sprintf(lsSql,"SELECT count(b.bill_serial_nbr) FROM BILL a,BILL b WHERE a.bill_serial_nbr = %s AND b.acct_id = a.acct_id AND b.state_date > a.state_date AND b.state = '40C' and b.settle_date is null ",bill_serial_nbr);

	cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();

	if(isqlcode<0)
	{
		strcpy(lsRetstr,cDatabase->GetSqlErrText());
		strcpy(lsTemp,"销帐流水检查出错");strcat(lsTemp,"\n");
		if(WriteLog(cDatabase,StaffID,"w_p_charge_erase",strcat(lsTemp,lsRetstr),lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}

	sprintf(lsTemp,cDatabase->GetData());
	if(atol(lsTemp)>0)
		StrReturn(-1,"只能反销最近的一次销帐!");

	sprintf(lsSql,"SELECT count(b.bill_serial_nbr) FROM BILL a,BILL b WHERE a.bill_serial_nbr = %s AND b.acct_id = a.acct_id AND b.staff_id = a.staff_id AND b.state_date = a.state_date AND b.bill_serial_nbr > %s AND b.state = '40C'"
	        ,bill_serial_nbr,bill_serial_nbr);

	cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();

	if(isqlcode<0)
	{
		strcpy(lsRetstr,cDatabase->GetSqlErrText());
		strcpy(lsTemp,"销帐流水检查出错");strcat(lsTemp,"\n");
		if(WriteLog(cDatabase,StaffID,"w_p_charge_erase",strcat(lsTemp,lsRetstr),lsErr)!=1)
			strcat(lsTemp,lsErr);
		cDatabase->Commit();
		StrReturn(-1,lsTemp);
	}

	sprintf(lsTemp,cDatabase->GetData());
	if(atol(lsTemp)>0)
		StrReturn(-1,"只能反销最近的一次销帐!");

	sprintf(lsSql,"SELECT acct_id FROM BILL where bill_serial_nbr=%s"
	,bill_serial_nbr);

	cDatabase->DoSql(lsSql);
	isqlcode=cDatabase->GetSqlCode();

	if(isqlcode<0)
	{
		strcpy(lsRetstr,cDatabase->GetSqlErrText());
		strcpy(lsTemp,"销帐流水检查出错");strcat(lsTemp,"\n");
		if(WriteLog(cDatabase,StaffID,"w_p_charge_erase",strcat(lsTemp,lsRetstr),lsErr)!=1)
			strcat(lsTemp,lsErr);