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