📄 charge_stat——0802.cpp
字号:
lpwrite=(char *)malloc(retlong+1);
strcpy(lpwrite,cDatabase->ret);
len=strlen(lpwrite);
//反销统计
sprintf(lsSql,
"SELECT STAFF.staff_id,STAFF.name,0,0,NVL(sum(BILL.amount/100.00),0),NVL(count(DISTINCT BILL.acct_id),0), "
"0,0,0,0,0,0,0,0,0,0 "
"FROM BILL,STAFF "
"WHERE BILL.state = '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());
lperase=(char *)malloc(retlong+1);
strcpy(lperase,cDatabase->ret);
len=len+strlen(lperase);
//预付款统计
sprintf(lsSql,
"SELECT STAFF.staff_id, "
"STAFF.name, "
"0,0,0,0, "
"NVL(sum(BALANCE_HIST_ADMIN.amount/100.00),0), "
"NVL(count(DISTINCT BALANCE_HIST_ADMIN.acct_id),0), "
"0,0,0,0,0,0,0,0 "
"FROM BALANCE_HIST_ADMIN,STAFF "
"WHERE BALANCE_HIST_ADMIN.use_catg in ( '1','2','3') AND "
"BALANCE_HIST_ADMIN.staff_id = STAFF.staff_id AND "
"BALANCE_HIST_ADMIN.state='01' AND "
"STAFF.site_id = %d AND "
"BALANCE_HIST_ADMIN.use_date >= %s AND "
"BALANCE_HIST_ADMIN.use_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());
lpbal=(char *)malloc(retlong+1);
strcpy(lpbal,cDatabase->ret);
len=len+strlen(lpbal);
//分期付款结清统计
sprintf(lsSql,
"SELECT STAFF.staff_id, "
"STAFF.name, "
"0,0,0,0,0,0, "
"NVL(sum(INSTALMENT_BALANCE.charge_current/100.00),0), "
"NVL(count(DISTINCT INSTALMENT_BALANCE.acct_id),0), "
"0,0,0,0,0,0 "
"FROM INSTALMENT_BALANCE,STAFF "
"WHERE INSTALMENT_BALANCE.operation_type in ( '01','04','05') AND "
"INSTALMENT_BALANCE.staff_id = STAFF.staff_id AND "
"STAFF.site_id = %d AND "
"INSTALMENT_BALANCE.state_date >= %s AND "
"INSTALMENT_BALANCE.state_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());
lpinstal=(char *)malloc(retlong+1);
strcpy(lpinstal,cDatabase->ret);
len=len+strlen(lpinstal);
//现金交纳金额统计
sprintf(lsSql,
"SELECT STAFF.staff_id,STAFF.name, "
"0,0,0,0,0,0,0,0, "
"NVL(sum(PAYMENT.amount/100.00),0), "
"0,0,0,0,0 "
"FROM PAYMENT,STAFF "
"WHERE PAYMENT.state in ('C0C','C0R','C0S') AND "
"PAYMENT.staff_id = STAFF.staff_id AND "
"STAFF.site_id = %d AND "
"PAYMENT.pay_date >= %s AND "
"PAYMENT.pay_date < %s AND "
"PAYMENT.payment_method = '11' "
"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());
lpcash_amt=(char *)malloc(retlong+1);
strcpy(lpcash_amt,cDatabase->ret);
len=len+strlen(lpcash_amt);
//现金交纳户数统计
sprintf(lsSql,
"SELECT STAFF.staff_id,STAFF.name, "
"0,0,0,0,0,0,0,0,0, "
"NVL(count(DISTINCT BILL.acct_id),0), "
"0,0,0,0 "
"FROM PAYMENT,BILL,STAFF "
"WHERE PAYMENT.pay_serial_nbr = BILL.pay_serial_nbr AND "
"PAYMENT.staff_id = STAFF.staff_id AND "
"STAFF.site_id = %d 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 = '11' "
"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());
lpcash_num=(char *)malloc(retlong+1);
strcpy(lpcash_num,cDatabase->ret);
len=len+strlen(lpcash_num);
//支票交纳金额统计
sprintf(lsSql,
"SELECT STAFF.staff_id, "
"STAFF.name, "
"0,0,0,0,0,0,0,0,0,0, "
"NVL(sum(PAYMENT.amount/100.00),0), "
"0,0,0 "
"FROM PAYMENT,STAFF "
"WHERE PAYMENT.state in ('C0C','C0R','C0S') AND "
"PAYMENT.staff_id = STAFF.staff_id AND "
"STAFF.site_id = %d AND "
"PAYMENT.pay_date >= %s AND "
"PAYMENT.pay_date < %s AND "
"PAYMENT.payment_method = '0' "
"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());
lpcheck_amt=(char *)malloc(retlong+1);
strcpy(lpcheck_amt,cDatabase->ret);
len=len+strlen(lpcheck_amt);
//支票交纳户数统计
sprintf(lsSql,
"SELECT STAFF.staff_id, "
"STAFF.name, "
"0,0,0,0,0,0,0,0,0,0,0, "
"NVL(count(DISTINCT BILL.acct_id),0), "
"0,0 "
"FROM PAYMENT,BILL,STAFF "
"WHERE PAYMENT.pay_serial_nbr = BILL.pay_serial_nbr AND "
"PAYMENT.staff_id = STAFF.staff_id AND "
"STAFF.site_id = %d 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 = '0' "
"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());
lpcheck_num=(char *)malloc(retlong+1);
strcpy(lpcheck_num,cDatabase->ret);
len=len+strlen(lpcheck_num);
//信用卡交纳金额统计
sprintf(lsSql,
"SELECT STAFF.staff_id, "
"STAFF.name, "
"0,0,0,0,0,0,0,0,0,0,0,0, "
"NVL(sum(PAYMENT.amount/100.00),0), "
"0 "
"FROM PAYMENT,STAFF "
"WHERE PAYMENT.state in ('C0C','C0R','C0S') AND "
"PAYMENT.staff_id = STAFF.staff_id AND "
"STAFF.site_id = %d AND "
"PAYMENT.pay_date >= %s AND "
"PAYMENT.pay_date < %s AND "
"PAYMENT.payment_method = 'kk' "
"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());
lpcard_amt=(char *)malloc(retlong+1);
strcpy(lpcard_amt,cDatabase->ret);
len=len+strlen(lpcard_amt);
//信用卡交纳户数统计
sprintf(lsSql,
"SELECT STAFF.staff_id, "
"STAFF.name, "
"0,0,0,0,0,0,0,0,0,0,0,0,0, "
"NVL(count(DISTINCT BILL.acct_id),0) "
"FROM PAYMENT,BILL,STAFF "
"WHERE PAYMENT.pay_serial_nbr = BILL.pay_serial_nbr AND "
"PAYMENT.staff_id = STAFF.staff_id AND "
"STAFF.site_id = %d 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' "
"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());
lpcard_num=(char *)malloc(retlong+1);
strcpy(lpcard_num,cDatabase->ret);
len=len+strlen(lpcard_num);
//构造返回数据字符串
if (cDatabase->ret) free(cDatabase->ret);
cDatabase->ret=(char *)malloc(len+10);
memset(cDatabase->ret,0,len+10);
//strcpy(lpret,NULL);
rtrim(ltrim(lpwrite));
if (strcmp(lpwrite,NULL)!=0&&strcmp(lpwrite,"")!=0)
{
strcpy(cDatabase->ret,lpwrite);
strcat(cDatabase->ret,"\n");
free(lpwrite);
}
rtrim(ltrim(lperase));
if (strcmp(lperase,NULL)!=0&&strcmp(lperase,"")!=0)
{
strcat(cDatabase->ret,lperase);
strcat(cDatabase->ret,"\n");
free(lperase);
}
rtrim(ltrim(lpbal));
if (strcmp(lpbal,NULL)!=0&&strcmp(lpbal,"")!=0)
{
strcat(cDatabase->ret,lpbal);
strcat(cDatabase->ret,"\n");
free(lpbal);
}
rtrim(ltrim(lpinstal));
if (strcmp(lpinstal,NULL)!=0&&strcmp(lpinstal,"")!=0)
{
strcat(cDatabase->ret,lpinstal);
strcat(cDatabase->ret,"\n");
free(lpinstal);
}
rtrim(ltrim(lpcash_amt));
if (strcmp(lpcash_amt,NULL)!=0&&strcmp(lpcash_amt,"")!=0)
{
strcat(cDatabase->ret,lpcash_amt);
strcat(cDatabase->ret,"\n");
free(lpcash_amt);
}
rtrim(ltrim(lpcash_num));
if (strcmp(lpcash_num,NULL)!=0&&strcmp(lpcash_num,"")!=0)
{
strcat(cDatabase->ret,lpcash_num);
strcat(cDatabase->ret,"\n");
free(lpcash_num);
}
rtrim(ltrim(lpcheck_amt));
if (strcmp(lpcheck_amt,NULL)!=0&&strcmp(lpcheck_amt,"")!=0)
{
strcat(cDatabase->ret,lpcheck_amt);
strcat(cDatabase->ret,"\n");
free(lpcheck_amt);
}
rtrim(ltrim(lpcheck_num));
if (strcmp(lpcheck_num,NULL)!=0&&strcmp(lpcheck_num,"")!=0)
{
strcat(cDatabase->ret,lpcheck_num);
strcat(cDatabase->ret,"\n");
free(lpcheck_num);
}
rtrim(ltrim(lpcard_amt));
if (strcmp(lpcard_amt,NULL)!=0&&strcmp(lpcard_amt,"")!=0)
{
strcat(cDatabase->ret,lpcard_amt);
strcat(cDatabase->ret,"\n");
free(lpcard_amt);
}
rtrim(ltrim(lpcard_num));
if (strcmp(lpcard_num,NULL)!=0&&strcmp(lpcard_num,"")!=0)
{
strcat(cDatabase->ret,lpcard_num);
strcat(cDatabase->ret,"\n");
free(lpcard_num);
}
cDatabase->Commit();
StrReturn(0,cDatabase->ret);
// free(lpret);
}
//-----------------------------------------------------------------
//服务PSCheckBill实现函数，传入参数staff_id,start_date,end_date
//-----------------------------------------------------------------
void CStat::CheckBillList()
{
char lsSql[2048],lsTemp[1024],lsErr[500];
char *lparm[5];
char staff_id[20],start_date[100],end_date[100];
int i,isqlcode,lpnum,rpnum;
int rt;
lpnum=3;
rpnum=GetInputParm(pInp,lparm,50);
if(rpnum!=lpnum)
{
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -