📄 charge_stat——0802.cpp
字号:
#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)
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -