drop package TRANSPORT_CHECK
/
/*==============================================================*/
/* Database package: TRANSPORT_CHECK */
/*==============================================================*/
create or replace package TRANSPORT_CHECK as
type OUTLIST is REF CURSOR;
procedure PROCEXCEL (YEARNUM In NUMBER,COUNTYID In Number,RESULT Out outlist);
function LISTPROVINCE (YEARNUM In Number,COUNTYID In Number) return outlist;
function LISTCITY (YEARNUM In Number,COUNTYID In Number) return outlist;
function LISTCOUNTY (YEARNUM In Number,COUNTYID In Number) return outlist;
end TRANSPORT_CHECK;
/
create or replace package body TRANSPORT_CHECK as
procedure PROCEXCEL (YEARNUM In NUMBER,COUNTYID In Number,RESULT Out outlist) as
BEGIN
--Open result for
if Common_Function.GetCountyDegree(CountyId) = 0 then
result := listProvince(YearNum,CountyId);
end if;
if Common_Function.GetCountyDegree(CountyId) = 1 then
result := listCity(YearNum,CountyId);
end if;
if Common_Function.GetCountyDegree(CountyId) = 2 then
result := listCounty(YearNum,CountyId);
end if;
END;
function LISTPROVINCE (YEARNUM In Number,COUNTYID In Number) return outlist as
rc outlist;
Begin
open rc for
select county, common_function.getCountyNameById(county) countyname,'' wkid,'' wkname,sum(chnum) chum,
sum(wzid_wz) wznum,sum(wzid_zhbf) zhbfnum,sum(wzid_wxz) wxznum,sum(wzid_tgwz) tgwznum,sum(wzid_ty) tynum,sum(wzid_qt) qtnum,
sum(Swood) woodcubage,sum(Sbamboo) bambooCubage,sum(Sother) otherCubage,sum(frpass) frpass,
sum(fmon) moneyCount,sum(fmoney) moneyNum,sum(fsave) saveCount,sum(fsavenum) saveNum, sum(men) menNum
from
( SELECT 1884 county,'' cityid,
To_char(common_function.getWorkstationidByUserId(chrg_userid)) as wkid,
count(*) as chnum,
decode(chrg_wzid,'1', count(*)) as wzid_wz, decode(chrg_wzid,'2', count(*)) as wzid_zhbf,
decode(chrg_wzid,'3', count(*)) as wzid_wxz, decode(chrg_wzid,'4', count(*)) as wzid_tgwz,
decode(chrg_wzid,'5', count(*)) as wzid_ty, decode(chrg_wzid,'6', count(*)) as wzid_qt,
sum(chrg.chrg_wztree) Swood, sum(chrg.chrg_wzbamboo) Sbamboo, sum(chrg.chrg_wzother) Sother,
decode(chrg_frpass,'1', count(*)) as frpass,decode(chrg.chrg_fmon,'1', count(*)) as fmon,
decode(chrg.chrg_fsave,'1', count(*)) as fsave,
sum(chrg.chrg_fmoney) fmoney, sum( chrg.chrg_fsavenum) fsavenum,
sum( chrg.chrg_mennum) men
FROM ys_checkregister chrg
where chrg_year=YearNum
group by common_function.GetCityIdByCountyId(chrg.chrg_county) ,common_function.getWorkstationidByUserId(chrg_userid),chrg_wzid,chrg_frpass,chrg.chrg_fmon,chrg.chrg_fsave)
group by county
union
select county, common_function.getCountyNameById(county) countyname,'' wkid,'' wkname,sum(chnum) chum,
sum(wzid_wz) wznum,sum(wzid_zhbf) zhbfnum,sum(wzid_wxz) wxznum,sum(wzid_tgwz) tgwznum,sum(wzid_ty) tynum,sum(wzid_qt) qtnum,
sum(Swood) woodcubage,sum(Sbamboo) bambooCubage,sum(Sother) otherCubage,sum(frpass) frpass,
sum(fmon) moneyCount,sum(fmoney) moneyNum,sum(fsave) saveCount,sum(fsavenum) saveNum, sum(men) menNum
from
( SELECT common_function.GetCityIdByCountyId(chrg.chrg_county) county,'' cityid,
To_char(common_function.getWorkstationidByUserId(chrg_userid)) as wkid,
count(*) as chnum,
decode(chrg_wzid,'1', count(*)) as wzid_wz, decode(chrg_wzid,'2', count(*)) as wzid_zhbf,
decode(chrg_wzid,'3', count(*)) as wzid_wxz, decode(chrg_wzid,'4', count(*)) as wzid_tgwz,
decode(chrg_wzid,'5', count(*)) as wzid_ty, decode(chrg_wzid,'6', count(*)) as wzid_qt,
sum(chrg.chrg_wztree) Swood, sum(chrg.chrg_wzbamboo) Sbamboo, sum(chrg.chrg_wzother) Sother,
decode(chrg_frpass,'1', count(*)) as frpass,decode(chrg.chrg_fmon,'1', count(*)) as fmon,
decode(chrg.chrg_fsave,'1', count(*)) as fsave,
sum(chrg.chrg_fmoney) fmoney, sum( chrg.chrg_fsavenum) fsavenum,
sum( chrg.chrg_mennum) men
FROM ys_checkregister chrg
where chrg_year=YearNum
group by common_function.GetCityIdByCountyId(chrg.chrg_county) ,common_function.getWorkstationidByUserId(chrg_userid),chrg_wzid,chrg_frpass,chrg.chrg_fmon,chrg.chrg_fsave)
group by county
order by 1 asc;
return rc;
End;
function LISTCITY (YEARNUM In Number,COUNTYID In Number) return outlist as
rc outlist;
Begin
open rc for
select county, common_function.getCountyNameById(county) countyname,'' wkid,'' wkname,sum(chnum) chum,
sum(wzid_wz) wznum,sum(wzid_zhbf) zhbfnum,sum(wzid_wxz) wxznum,sum(wzid_tgwz) tgwznum,sum(wzid_ty) tynum,sum(wzid_qt) qtnum,
sum(Swood) woodcubage,sum(Sbamboo) bambooCubage,sum(Sother) otherCubage,sum(frpass) frpass,
sum(fmon) moneyCount,sum(fmoney) moneyNum,sum(fsave) saveCount,sum(fsavenum) saveNum, sum(men) menNum
from
( SELECT common_function.GetCityIdByCountyId(chrg.chrg_county) county,'' cityid,
To_char(common_function.getWorkstationidByUserId(chrg_userid)) as wkid,
count(*) as chnum,
decode(chrg_wzid,'1', count(*)) as wzid_wz, decode(chrg_wzid,'2', count(*)) as wzid_zhbf,
decode(chrg_wzid,'3', count(*)) as wzid_wxz, decode(chrg_wzid,'4', count(*)) as wzid_tgwz,
decode(chrg_wzid,'5', count(*)) as wzid_ty, decode(chrg_wzid,'6', count(*)) as wzid_qt,
sum(chrg.chrg_wztree) Swood, sum(chrg.chrg_wzbamboo) Sbamboo, sum(chrg.chrg_wzother) Sother,
decode(chrg_frpass,'1', count(*)) as frpass,decode(chrg.chrg_fmon,'1', count(*)) as fmon,
decode(chrg.chrg_fsave,'1', count(*)) as fsave,
sum(chrg.chrg_fmoney) fmoney, sum( chrg.chrg_fsavenum) fsavenum,
sum( chrg.chrg_mennum) men
FROM ys_checkregister chrg
where common_function.GetCityIdByCountyId(chrg.chrg_county)=CountyId and chrg_year=YearNum
group by common_function.GetCityIdByCountyId(chrg.chrg_county) ,common_function.getWorkstationidByUserId(chrg_userid),chrg_wzid,chrg_frpass,chrg.chrg_fmon,chrg.chrg_fsave)
group by county
union
select county,common_function.getCountyNameById(county) countyname,'' wkid,'' wkname,sum(chnum) chum,
sum(wzid_wz) wznum,sum(wzid_zhbf) zhbfnum,sum(wzid_wxz) wxznum,sum(wzid_tgwz) tgwznum,sum(wzid_ty) tynum,sum(wzid_qt) qtnum,
sum(Swood) woodcubage,sum(Sbamboo) bambooCubage,sum(Sother) otherCubage,sum(frpass) frpass,
sum(fmon) moneyCount,sum(fmoney) moneyNum,sum(fsave) saveCount,sum(fsavenum) saveNum, sum(men) menNum
from
( SELECT chrg.chrg_county county, common_function.GetCityIdByCountyId(chrg.chrg_county) cityid,
To_char(common_function.getWorkstationidByUserId(chrg_userid)) as wkid,
count(*) as chnum,
decode(chrg_wzid,'1', count(*)) as wzid_wz, decode(chrg_wzid,'2', count(*)) as wzid_zhbf,
decode(chrg_wzid,'3', count(*)) as wzid_wxz, decode(chrg_wzid,'4', count(*)) as wzid_tgwz,
decode(chrg_wzid,'5', count(*)) as wzid_ty, decode(chrg_wzid,'6', count(*)) as wzid_qt,
sum(chrg.chrg_wztree) Swood, sum(chrg.chrg_wzbamboo) Sbamboo, sum(chrg.chrg_wzother) Sother,
decode(chrg_frpass,'1', count(*)) as frpass,decode(chrg.chrg_fmon,'1', count(*)) as fmon,
decode(chrg.chrg_fsave,'1', count(*)) as fsave,
sum(chrg.chrg_fmoney) fmoney, sum( chrg.chrg_fsavenum) fsavenum,
sum( chrg.chrg_mennum) men
FROM ys_checkregister chrg
where common_function.GetCityIdByCountyId(chrg.chrg_county)=CountyId and chrg_year=YearNum
group by chrg.chrg_county,common_function.getWorkstationidByUserId(chrg_userid),chrg_wzid,chrg_frpass,chrg.chrg_fmon,chrg.chrg_fsave)
group by county
;
return rc;
End;
function LISTCOUNTY (YEARNUM In Number,COUNTYID In Number) return outlist as
rc outlist;
Begin
open rc for
select county,common_function.getCountyNameById(county) countyname,'' wkid,'' wkname,sum(chnum) chum,
sum(wzid_wz) wznum,sum(wzid_zhbf) zhbfnum,sum(wzid_wxz) wxznum,sum(wzid_tgwz) tgwznum,sum(wzid_ty) tynum,sum(wzid_qt) qtnum,
sum(Swood) woodcubage,sum(Sbamboo) bambooCubage,sum(Sother) otherCubage,sum(frpass) frpass,
sum(fmon) moneyCount,sum(fmoney) moneyNum,sum(fsave) saveCount,sum(fsavenum) saveNum, sum(men) menNum
from
( SELECT chrg.chrg_county county,
To_char(common_function.getWorkstationidByUserId(chrg_userid)) as wkid,
count(*) as chnum,
decode(chrg_wzid,'1', count(*)) as wzid_wz, decode(chrg_wzid,'2', count(*)) as wzid_zhbf,
decode(chrg_wzid,'3', count(*)) as wzid_wxz, decode(chrg_wzid,'4', count(*)) as wzid_tgwz,
decode(chrg_wzid,'5', count(*)) as wzid_ty, decode(chrg_wzid,'6', count(*)) as wzid_qt,
sum(chrg.chrg_wztree) Swood, sum(chrg.chrg_wzbamboo) Sbamboo, sum(chrg.chrg_wzother) Sother,
decode(chrg_frpass,'1', count(*)) as frpass,decode(chrg.chrg_fmon,'1', count(*)) as fmon,
decode(chrg.chrg_fsave,'1', count(*)) as fsave,
sum(chrg.chrg_fmoney) fmoney, sum( chrg.chrg_fsavenum) fsavenum,
sum( chrg.chrg_mennum) men
FROM ys_checkregister chrg
where chrg.chrg_county =CountyId and chrg_year=YearNum
group by chrg.chrg_county,common_function.getWorkstationidByUserId(chrg_userid),chrg_wzid,chrg_frpass,chrg.chrg_fmon,chrg.chrg_fsave)
group by county
union
select county,common_function.getCountyNameById(county) countyname,wkid,common_function.GetWorkstationNameById(wkid) wkname,
sum(chnum) chum,
sum(wzid_wz) wznum,sum(wzid_zhbf) zhbfnum,sum(wzid_wxz) wxznum,sum(wzid_tgwz) tgwznum,sum(wzid_ty) tynum,sum(wzid_qt) qtnum,
sum(Swood) woodcubage,sum(Sbamboo) bambooCubage,sum(Sother) otherCubage,sum(frpass) frpass,
sum(fmon) moneyCount,sum(fmoney) moneyNum,sum(fsave) saveCount,sum(fsavenum) saveNum, sum(men) menNum
from
( SELECT chrg.chrg_county county,
To_char(common_function.getWorkstationidByUserId(chrg_userid)) as wkid,
count(*) as chnum,
decode(chrg_wzid,'1', count(*)) as wzid_wz, decode(chrg_wzid,'2', count(*)) as wzid_zhbf,
decode(chrg_wzid,'3', count(*)) as wzid_wxz, decode(chrg_wzid,'4', count(*)) as wzid_tgwz,
decode(chrg_wzid,'5', count(*)) as wzid_ty, decode(chrg_wzid,'6', count(*)) as wzid_qt,
sum(chrg.chrg_wztree) Swood, sum(chrg.chrg_wzbamboo) Sbamboo, sum(chrg.chrg_wzother) Sother,
decode(chrg_frpass,'1', count(*)) as frpass,decode(chrg.chrg_fmon,'1', count(*)) as fmon,
decode(chrg.chrg_fsave,'1', count(*)) as fsave,
sum(chrg.chrg_fmoney) fmoney, sum( chrg.chrg_fsavenum) fsavenum,
sum( chrg.chrg_mennum) men
FROM ys_checkregister chrg
where chrg.chrg_county =CountyId and chrg_year=YearNum
group by chrg.chrg_county,common_function.getWorkstationidByUserId(chrg_userid),chrg_wzid,chrg_frpass,chrg.chrg_fmon,chrg.chrg_fsave)
group by county,wkid
order by 3 desc
;
return rc;
End;
end TRANSPORT_CHECK;
/