一、包规范
1、report包
- CREATE OR REPLACE package report as
- type cursorType is ref cursor;
- type CommInfo is record(
- id_ varchar2(60),
- name_ varchar2(200),
- comm_category_name_ varchar2(60),
- city_company_name_ varchar2(200),
- district_name_ varchar2(100),
- county_company_name_ varchar2(200),
- contact_ varchar2(60),
- mobile_phone_ varchar2(60),
- person_id_ varchar2(60),
- bank_num_ varchar2(60)
- );
- type CardType is record(
- id_ varchar2(60),
- type_ varchar2(60),
- card_brand_ varchar2(60),
- card_brand_name_ varchar2(60)
- );
- type QuantityBean is record(
- commId number,
- pkl number := 0,
- jhl number := 0,
- jfl number := 0,
- zsl number := 0
- );
- function genPiKaDataSet(p_fgs varchar2, p_fwt varchar2, p_comm varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return varchar2;
- end;
CREATE OR REPLACE package report as
type cursorType is ref cursor;
type CommInfo is record(
id_ varchar2(60),
name_ varchar2(200),
comm_category_name_ varchar2(60),
city_company_name_ varchar2(200),
district_name_ varchar2(100),
county_company_name_ varchar2(200),
contact_ varchar2(60),
mobile_phone_ varchar2(60),
person_id_ varchar2(60),
bank_num_ varchar2(60)
);
type CardType is record(
id_ varchar2(60),
type_ varchar2(60),
card_brand_ varchar2(60),
card_brand_name_ varchar2(60)
);
type QuantityBean is record(
commId number,
pkl number := 0,
jhl number := 0,
jfl number := 0,
zsl number := 0
);
function genPiKaDataSet(p_fgs varchar2, p_fwt varchar2, p_comm varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return varchar2;
end;
2、reportFunc包
- CREATE OR REPLACE package reportFunc as
- function getCommSQL(p_fgs varchar2, p_fwt varchar2, p_comm varchar2) return varchar2;
- function getCardTypeSQL return varchar2;
- function getQuantityBean(p_comm varchar2, p_type varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return report.QuantityBean;
- end;
CREATE OR REPLACE package reportFunc as
function getCommSQL(p_fgs varchar2, p_fwt varchar2, p_comm varchar2) return varchar2;
function getCardTypeSQL return varchar2;
function getQuantityBean(p_comm varchar2, p_type varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return report.QuantityBean;
end;
二、包体
1、report包体
- CREATE OR REPLACE package body report as
- --生成数据集
- function genPiKaDataSet(p_fgs varchar2, p_fwt varchar2, p_comm varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return varchar2 is
- c1 cursorType;
- c2 cursorType;
- v_CommInfo CommInfo;
- v_CardType CardType;
- pkl_ number := 0; -- 值
- jhl_ number := 0;
- jfl_ number := 0;
- zsl_ number := 0;
- jhRate number := 0;
- jfRate number := 0;
- v_pk_total_ number := 0; -- 值合计
- v_jh_total_ number := 0;
- v_jf_total_ number := 0;
- v_zs_total_ number := 0;
- v_jhl_total_ number := 0;
- v_jfl_total_ number := 0;
- pk_sum number := 0; -- 值合计的合计
- jh_sum number := 0;
- jf_sum number := 0;
- zs_sum number := 0;
- jhl_sum number := 0;
- jfl_sum number := 0;
- bean QuantityBean;
- result varchar2(200);
- lsh number := 0;
- createDate date := sysdate;
- begin
- delete from R_PIKA_STAT_ where create_date_<createDate-1;
- commit;
- select SEQ_LSH.nextval into lsh from dual;
- --代办点
- open c1 for reportFunc.getCommSQL(p_fgs, p_fwt, p_comm);
- loop
- v_pk_total_ := 0;
- v_jh_total_ := 0;
- v_jf_total_ := 0;
- v_zs_total_ := 0;
- v_jhl_total_ := 0;
- v_jfl_total_ := 0;
- fetch c1 into v_CommInfo;
- exit when c1%notfound;
- --卡类型
- open c2 for reportFunc.getCardTypeSQL;
- loop
- fetch c2 into v_CardType;
- exit when c2%notfound;
- bean := reportFunc.getQuantityBean(v_CommInfo.id_, v_CardType.id_, p_date1, p_date2, p_status);
- --批卡量
- pkl_ := bean.pkl;
- v_pk_total_ := v_pk_total_ + pkl_;
- insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,
- county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,
- card_brand_,card_brand_name_,data_type_,amount_,create_date_)
- values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,
- v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,
- v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),
- v_CardType.card_brand_,v_CardType.card_brand_name_,'1.批卡量(套数)', pkl_, createDate);
- --激活量
- jhl_ := bean.jhl;
- v_jh_total_ := v_jh_total_ + jhl_;
- insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,
- county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,
- card_brand_,card_brand_name_,data_type_,amount_,create_date_)
- values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,
- v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,
- v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),
- v_CardType.card_brand_,v_CardType.card_brand_name_,'2.激活量(套数)', jhl_, createDate);
- --成功积分登记量
- jfl_ := bean.jfl;
- v_jf_total_ := v_jf_total_ + jfl_;
- insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,
- county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,
- card_brand_,card_brand_name_,data_type_,amount_,create_date_)
- values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,
- v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,
- v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),
- v_CardType.card_brand_,v_CardType.card_brand_name_,'3.成功积分登记量(套数)', jfl_, createDate);
- --激活率
- jhRate := 0;
- if pkl_ > 0 then
- jhRate := (1.0 * jhl_) / pkl_;
- else
- if jhl_ > 0 then
- jhRate := 1.0;
- end if;
- end if;
- insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,
- county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,
- card_brand_,card_brand_name_,data_type_,amount_,create_date_)
- values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,
- v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,
- v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),
- v_CardType.card_brand_,v_CardType.card_brand_name_,'4.激活率(%)', jhRate, createDate);
- --积分率 = 已积分数量 / (已激活数量-赠送号激活量)
- zsl_ := bean.zsl;
- v_zs_total_ := v_zs_total_ + zsl_;
- jfRate := 0;
- if (jhl_ - zsl_) > 0 then
- jfRate := (1.0 * jfl_) / (jhl_ - zsl_);
- else
- if jfl_ > 0 then
- jfRate := 1.0;
- end if;
- end if;
- insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,
- county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,
- card_brand_,card_brand_name_,data_type_,amount_,create_date_)
- values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,
- v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,
- v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),
- v_CardType.card_brand_,v_CardType.card_brand_name_,'5.积分率(%)', jfRate, createDate);
- end loop;
- close c2;
- --去掉值为0的代办点
- if v_pk_total_<=0 and v_jh_total_<=0 and v_jf_total_<=0 then
- delete from R_PIKA_STAT_ where comm_id_=v_CommInfo.id_;
- else
- --批卡合计
- update R_PIKA_STAT_ set pk_total_=v_pk_total_ where comm_id_=v_CommInfo.id_;
- pk_sum := pk_sum + v_pk_total_;
- --激活合计
- update R_PIKA_STAT_ set jh_total_=v_jh_total_ where comm_id_=v_CommInfo.id_;
- jh_sum := jh_sum + v_jh_total_;
- --积分量合计
- update R_PIKA_STAT_ set jf_total_=v_jf_total_ where comm_id_=v_CommInfo.id_;
- jf_sum := jf_sum + v_jf_total_;
- --激活率合计
- v_jhl_total_ := 0;
- if v_pk_total_ > 0 then
- v_jhl_total_ := (1.0 * v_jh_total_) / v_pk_total_;
- else
- if v_jh_total_ > 0 then
- v_jhl_total_ := 1.0;
- end if;
- end if;
- update R_PIKA_STAT_ set jhl_total_=v_jhl_total_ where comm_id_=v_CommInfo.id_;
- --积分率合计
- v_jfl_total_ := 0;
- if (v_jh_total_ - v_zs_total_) > 0 then
- v_jfl_total_ := (1.0 * v_jf_total_) / (v_jh_total_ - v_zs_total_);
- else
- if v_jf_total_ > 0 then
- v_jfl_total_ := 1.0;
- end if;
- end if;
- update R_PIKA_STAT_ set jfl_toal_=v_jfl_total_ where comm_id_=v_CommInfo.id_;
- zs_sum := zs_sum + v_zs_total_;
- end if;
- end loop;
- close c1;
- --合计的合计
- if pk_sum > 0 then
- jhl_sum := (1.0 * jh_sum) / pk_sum;
- else
- if jh_sum > 0 then
- jhl_sum := 1.0;
- end if;
- end if;
- if (jh_sum - zs_sum) > 0 then
- jfl_sum := (1.0 * jf_sum) / (jh_sum - zs_sum);
- else
- if jf_sum > 0 then
- jfl_sum := 1.0;
- end if;
- end if;
- result := lsh || ',' || pk_sum || ',' || jh_sum || ',' || jf_sum || ',' || jhl_sum || ',' || jfl_sum;
- commit;
- return result;
- end;
- end;
CREATE OR REPLACE package body report as
--生成数据集
function genPiKaDataSet(p_fgs varchar2, p_fwt varchar2, p_comm varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return varchar2 is
c1 cursorType;
c2 cursorType;
v_CommInfo CommInfo;
v_CardType CardType;
pkl_ number := 0; -- 值
jhl_ number := 0;
jfl_ number := 0;
zsl_ number := 0;
jhRate number := 0;
jfRate number := 0;
v_pk_total_ number := 0; -- 值合计
v_jh_total_ number := 0;
v_jf_total_ number := 0;
v_zs_total_ number := 0;
v_jhl_total_ number := 0;
v_jfl_total_ number := 0;
pk_sum number := 0; -- 值合计的合计
jh_sum number := 0;
jf_sum number := 0;
zs_sum number := 0;
jhl_sum number := 0;
jfl_sum number := 0;
bean QuantityBean;
result varchar2(200);
lsh number := 0;
createDate date := sysdate;
begin
delete from R_PIKA_STAT_ where create_date_<createDate-1;
commit;
select SEQ_LSH.nextval into lsh from dual;
--代办点
open c1 for reportFunc.getCommSQL(p_fgs, p_fwt, p_comm);
loop
v_pk_total_ := 0;
v_jh_total_ := 0;
v_jf_total_ := 0;
v_zs_total_ := 0;
v_jhl_total_ := 0;
v_jfl_total_ := 0;
fetch c1 into v_CommInfo;
exit when c1%notfound;
--卡类型
open c2 for reportFunc.getCardTypeSQL;
loop
fetch c2 into v_CardType;
exit when c2%notfound;
bean := reportFunc.getQuantityBean(v_CommInfo.id_, v_CardType.id_, p_date1, p_date2, p_status);
--批卡量
pkl_ := bean.pkl;
v_pk_total_ := v_pk_total_ + pkl_;
insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,
county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,
card_brand_,card_brand_name_,data_type_,amount_,create_date_)
values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,
v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,
v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),
v_CardType.card_brand_,v_CardType.card_brand_name_,'1.批卡量(套数)', pkl_, createDate);
--激活量
jhl_ := bean.jhl;
v_jh_total_ := v_jh_total_ + jhl_;
insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,
county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,
card_brand_,card_brand_name_,data_type_,amount_,create_date_)
values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,
v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,
v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),
v_CardType.card_brand_,v_CardType.card_brand_name_,'2.激活量(套数)', jhl_, createDate);
--成功积分登记量
jfl_ := bean.jfl;
v_jf_total_ := v_jf_total_ + jfl_;
insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,
county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,
card_brand_,card_brand_name_,data_type_,amount_,create_date_)
values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,
v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,
v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),
v_CardType.card_brand_,v_CardType.card_brand_name_,'3.成功积分登记量(套数)', jfl_, createDate);
--激活率
jhRate := 0;
if pkl_ > 0 then
jhRate := (1.0 * jhl_) / pkl_;
else
if jhl_ > 0 then
jhRate := 1.0;
end if;
end if;
insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,
county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,
card_brand_,card_brand_name_,data_type_,amount_,create_date_)
values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,
v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,
v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),
v_CardType.card_brand_,v_CardType.card_brand_name_,'4.激活率(%)', jhRate, createDate);
--积分率 = 已积分数量 / (已激活数量-赠送号激活量)
zsl_ := bean.zsl;
v_zs_total_ := v_zs_total_ + zsl_;
jfRate := 0;
if (jhl_ - zsl_) > 0 then
jfRate := (1.0 * jfl_) / (jhl_ - zsl_);
else
if jfl_ > 0 then
jfRate := 1.0;
end if;
end if;
insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,
county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,
card_brand_,card_brand_name_,data_type_,amount_,create_date_)
values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,
v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,
v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),
v_CardType.card_brand_,v_CardType.card_brand_name_,'5.积分率(%)', jfRate, createDate);
end loop;
close c2;
--去掉值为0的代办点
if v_pk_total_<=0 and v_jh_total_<=0 and v_jf_total_<=0 then
delete from R_PIKA_STAT_ where comm_id_=v_CommInfo.id_;
else
--批卡合计
update R_PIKA_STAT_ set pk_total_=v_pk_total_ where comm_id_=v_CommInfo.id_;
pk_sum := pk_sum + v_pk_total_;
--激活合计
update R_PIKA_STAT_ set jh_total_=v_jh_total_ where comm_id_=v_CommInfo.id_;
jh_sum := jh_sum + v_jh_total_;
--积分量合计
update R_PIKA_STAT_ set jf_total_=v_jf_total_ where comm_id_=v_CommInfo.id_;
jf_sum := jf_sum + v_jf_total_;
--激活率合计
v_jhl_total_ := 0;
if v_pk_total_ > 0 then
v_jhl_total_ := (1.0 * v_jh_total_) / v_pk_total_;
else
if v_jh_total_ > 0 then
v_jhl_total_ := 1.0;
end if;
end if;
update R_PIKA_STAT_ set jhl_total_=v_jhl_total_ where comm_id_=v_CommInfo.id_;
--积分率合计
v_jfl_total_ := 0;
if (v_jh_total_ - v_zs_total_) > 0 then
v_jfl_total_ := (1.0 * v_jf_total_) / (v_jh_total_ - v_zs_total_);
else
if v_jf_total_ > 0 then
v_jfl_total_ := 1.0;
end if;
end if;
update R_PIKA_STAT_ set jfl_toal_=v_jfl_total_ where comm_id_=v_CommInfo.id_;
zs_sum := zs_sum + v_zs_total_;
end if;
end loop;
close c1;
--合计的合计
if pk_sum > 0 then
jhl_sum := (1.0 * jh_sum) / pk_sum;
else
if jh_sum > 0 then
jhl_sum := 1.0;
end if;
end if;
if (jh_sum - zs_sum) > 0 then
jfl_sum := (1.0 * jf_sum) / (jh_sum - zs_sum);
else
if jf_sum > 0 then
jfl_sum := 1.0;
end if;
end if;
result := lsh || ',' || pk_sum || ',' || jh_sum || ',' || jf_sum || ',' || jhl_sum || ',' || jfl_sum;
commit;
return result;
end;
end;
2、reportFunc包体
- CREATE OR REPLACE package body reportFunc as
- --返回获取代办点信息的SQL
- function getCommSQL(p_fgs varchar2, p_fwt varchar2, p_comm varchar2) return varchar2 is
- sql_1 varchar2(2000);
- begin
- sql_1 := 'select a.id_,a.name_,e.name_ comm_category_name_,b.name_ city_company_name_,d.name_ district_name_,c.name_ county_company_name_,';
- sql_1 := sql_1 || 'case when (select name_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') is null then a.contact_ else (select name_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') end contact_,';
- sql_1 := sql_1 || 'case when (select tel_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') is null then a.mobile_phone_ else (select tel_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') end mobile_phone_,';
- sql_1 := sql_1 || 'case when (select person_id_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') is null then a.person_id_ else (select person_id_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') end person_id_,';
- sql_1 := sql_1 || 'case when (select bank_num_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') is null then a.bank_num_ else (select bank_num_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') end bank_num_';
- sql_1 := sql_1 || ' from commission_info_ a';
- sql_1 := sql_1 || ' left join organization_ b on a.city_company_id_=b.party_id_';
- sql_1 := sql_1 || ' left join organization_ c on a.county_company_id_=c.party_id_';
- sql_1 := sql_1 || ' left join district_ d on a.district_=d.id_';
- sql_1 := sql_1 || ' left join commission_category_ e on a.comm_cagegory_=e.id_';
- sql_1 := sql_1 || ' where a.id_ is not null';
- if p_fgs is not null then
- sql_1 := sql_1 || ' and a.city_company_id_=''' || p_fgs || '''';
- end if;
- if p_fwt is not null then
- sql_1 := sql_1 || ' and a.county_company_id_=''' || p_fwt || '''';
- end if;
- if p_comm is not null then
- sql_1 := sql_1 || ' and a.id_ in(' || p_comm || ')';
- end if;
- sql_1 := sql_1 || ' order by a.id_ asc';
- return sql_1;
- end;
- --返回获取卡类型信息的SQL
- function getCardTypeSQL return varchar2 is
- sql_2 varchar2(1000);
- begin
- sql_2 := 'select a.id_,a.type_,a.card_brand_,b.description_ card_brand_name_';
- sql_2 := sql_2 || ' from sim_card_type_ a inner join enumeration_ b';
- sql_2 := sql_2 || ' on a.card_brand_=b.enum_id_ order by a.id_ asc';
- return sql_2;
- end;
- -- 数量Bean
- function getQuantityBean(p_comm varchar2, p_type varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return report.QuantityBean is
- s varchar2(2000);
- bean report.QuantityBean;
- c1 report.cursorType;
- begin
- s := 'select a.id_,';
- s := s || '(select count(id_) from sim_card_detail_ where commission_id_=a.id_ and status_ in(' || p_status || ') and card_type_id_=' || p_type;
- if p_date1 is not null then
- s := s || ' and status_date_>=' || p_date1;
- end if;
- if p_date2 is not null then
- s := s || ' and status_date_<' || p_date2;
- end if;
- s := s || ') pkl,';
- s := s || '(select count(id_) from sim_card_detail_ where commission_id_=a.id_ and card_type_id_=' || p_type;
- if p_date1 is not null then
- s := s || ' and active_date_>=' || p_date1;
- end if;
- if p_date2 is not null then
- s := s || ' and active_date_<' || p_date2;
- end if;
- s := s || ') jhl,';
- s := s || '(select count(id_) from sim_card_detail_ where is_integral_=1 and commission_id_=a.id_ and card_type_id_=' || p_type;
- if p_date1 is not null then
- s := s || ' and active_date_>=' || p_date1;
- end if;
- if p_date2 is not null then
- s := s || ' and active_date_<' || p_date2;
- end if;
- s := s || ') jfl,';
- s := s || '(select count(id_) from sim_card_detail_ where status_=''RESOURCE_STATUS_13'' and commission_id_=a.id_ and card_type_id_=' || p_type;
- if p_date1 is not null then
- s := s || ' and active_date_>=' || p_date1;
- end if;
- if p_date2 is not null then
- s := s || ' and active_date_<' || p_date2;
- end if;
- s := s || ') zsl';
- s := s || ' from commission_info_ a where a.id_=' || p_comm;
- open c1 for s;
- loop
- fetch c1 into bean;
- exit when c1%notfound;
- end loop;
- close c1;
- return bean;
- end;
- end;