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