drop package CUT_COUNTSENDBOOK
/
/*==============================================================*/
/* Database package: CUT_COUNTSENDBOOK */
/*==============================================================*/
create or replace package CUT_COUNTSENDBOOK as
type OUTLIST is REF CURSOR;
procedure GETCOUNTBOOKLIST (ISEXCEL In Number,COUNTYID IN NUMBER,YEARNUM In Number,RESULT out outlist);
function LISTPROVINCE (YEARNUM In number,PROVINCEID In Number) return outlist;
function LISTCITY (YEARNUM In number,CITYID In Number) return outlist;
function LISTPROVINCEEXCEL (YEARNUM In number,PROVINCEID In Number) return outlist;
function LISTCITYEXCEL (YEARNUM In number,CITYID In Number) return outlist;
function LISTCOUNTY (YEARNUM In number,COUNTYID In Number) return outlist;
function GETCUTTYPENUMBER (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number,CUTTYPEID In Number) return Number;
function GETCUTTYPECOUNT (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number,CUTTYPEID In Number) return Number;
function GETTOTALNUMBER (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number) return Number;
function GETTOTALCOUNT (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number) return Number;
function GETBAMBOONUMBER (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number) return Number;
function GETBAMBOOCOUNT (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number) return number;
function GETFUYUMIANJI (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number) return number;
function GETFUYUCHUCAILIANG (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number) return number;
function GETSHANGPINGCAIFA (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number) return Number;
function GETSHANGPINGCHUCAI (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number) return Number;
function GETFEISHANGPING (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number) return Number;
end CUT_COUNTSENDBOOK;
/
create or replace package body CUT_COUNTSENDBOOK as
procedure GETCOUNTBOOKLIST (ISEXCEL In Number,COUNTYID IN NUMBER,YEARNUM In Number,RESULT out outlist) as
Begin
-- to send data to page
if isExcel = 0 then
if Common_Function.GetCountyDegree(countyId) = 2 then
result := listCounty(yearNum,countyId);
end if;
if Common_Function.GetCountyDegree(countyId) = 1 then
result := listCity( yearNum ,CountyId);
end if;
if Common_Function.GetCountyDegree(CountyId) = 0 then
result := listProvince(yearNum,countyId);
end if;
end if;
-- to send data to excel
if isExcel = 1 then
if Common_Function.GetCountyDegree(countyId) = 2 then
result := listCounty(yearNum,countyId);
end if;
if Common_Function.GetCountyDegree(countyId) = 1 then
result := listCityExcel(yearNum,CountyId);
end if;
if Common_Function.GetCountyDegree(CountyId) = 0 then
result := listProvinceExcel(yearNum,countyId);
end if;
end if;
ENd;
function LISTPROVINCE (YEARNUM In number,PROVINCEID In Number) return outlist as
rc outlist;
Begin
open rc
for
select
Common_Function.GetCountyCodeById(provinceId) as addressCode,
Common_Function.GetCountyNameById(provinceId) as address,
getTotalNumber(yearNum,provinceId,0) As TotalNumber,
getTotalCount(yearNum,provinceId,0)As TotalCount,
getCuttypeNumber(yearNum,provinceId,0,1) As zhuFaNumber,
getCuttypeCount(yearNum,provinceId,0,1) As zhuFaCount,
getCuttypeNumber(yearNum,provinceId,0,2) As fuYuNumber,
getCuttypeCount(yearNum,provinceId,0,2) As fuYuCaiFaCount,
getCuttypeNumber(yearNum,provinceId,0,3) As diChanNumber,
getCuttypeCount(yearNum,provinceId,0,3) As diChanCount,
getCuttypeNumber(yearNum,provinceId,0,4) As gengXinNumber,
getCuttypeCount(yearNum,provinceId,0,4) As gengXinCount,
getCuttypeNumber(yearNum,provinceId,0,5) As qiTaNumber,
getCuttypeCount(yearNum,provinceId,0,5) As qiTaCaiFaCount,
getBambooNumber(yearNum,provinceId,0) As bambooNumber,
getBambooCount(yearNum,provinceId,0) As BambooCount,
getShangPingCaiFa(yearNum,provinceId,0) As shangPinCaiFaCount,
getShangpingChuCai(yearNum,provinceId,0) As shangPinChuCaiCount,
getFeiShangPing(yearNum,provinceId,0) As feiShangPinCount,
getFuYuMianJi(yearNum,provinceId,0) as fuYuMianJiCount,
getFuYuChuCaiLiang(yearNum,provinceId,0) as fuYuChuCaiCount
from dual
union
select
Common_Function.GetCountyCodeById(city.COUN_ID) as addressCode,
Common_Function.GetCountyNameById(city.COUN_ID) as address,
getTotalNumber(yearNum,city.COUN_ID,1) As TotalNumber,
getTotalCount(yearNum,city.COUN_ID,1)As TotalCount,
getCuttypeNumber(yearNum,city.COUN_ID,1,1) As zhuFaNumber,
getCuttypeCount(yearNum,city.COUN_ID,1,1) As zhuFaCount,
getCuttypeNumber(yearNum,city.COUN_ID,1,2) As fuYuNumber,
getCuttypeCount(yearNum,city.COUN_ID,1,2) As fuYuCaiFaCount,
getCuttypeNumber(yearNum,city.COUN_ID,1,3) As diChanNumber,
getCuttypeCount(yearNum,city.COUN_ID,1,3) As diChanCount,
getCuttypeNumber(yearNum,city.COUN_ID,1,4) As gengXinNumber,
getCuttypeCount(yearNum,city.COUN_ID,1,4) As gengXinCount,
getCuttypeNumber(yearNum,city.COUN_ID,1,5) As qiTaNumber,
getCuttypeCount(yearNum,city.COUN_ID,1,5) As qiTaCaiFaCount,
getBambooNumber(yearNum,city.COUN_ID,1) As bambooNumber,
getBambooCount(yearNum,city.COUN_ID,1) As BambooCount,
getShangPingCaiFa(yearNum,city.COUN_ID,1) As shangPinCaiFaCount,
getShangpingChuCai(yearNum,city.COUN_ID,1) As shangPinChuCaiCount,
getFeiShangPing(yearNum,city.COUN_ID,1) As feiShangPinCount,
getFuYuMianJi(yearNum,city.COUN_ID,1) as fuYuMianJiCount,
getFuYuChuCaiLiang(yearNum,city.COUN_ID,1) as fuYuChuCaiCount
from dual,GG_County city
where city.COUN_SJID = provinceId
And getTotalNumber(yearNum,city.COUN_ID,1) > 0
order by 1;
return rc;
ENd;
function LISTCITY (YEARNUM In number,CITYID In Number) return outlist as
rc outlist;
Begin
open rc
for
select
Common_Function.GetCountyCodeById(CityId) as addressCode,
Common_Function.GetCountyNameById(CityId) as address,
getTotalNumber(yearNum,CityId,1) As TotalNumber,
getTotalCount(yearNum,CityId,1)As TotalCount,
getCuttypeNumber(yearNum,CityId,1,1) As zhuFaNumber,
getCuttypeCount(yearNum,CityId,1,1) As zhuFaCount,
getCuttypeNumber(yearNum,CityId,1,2) As fuYuNumber,
getCuttypeCount(yearNum,CityId,1,2) As fuYuCaiFaCount,
getCuttypeNumber(yearNum,CityId,1,3) As diChanNumber,
getCuttypeCount(yearNum,CityId,1,3) As diChanCount,
getCuttypeNumber(yearNum,CityId,1,4) As gengXinNumber,
getCuttypeCount(yearNum,CityId,1,4) As gengXinCount,
getCuttypeNumber(yearNum,CityId,1,5) As qiTaNumber,
getCuttypeCount(yearNum,CityId,1,5) As qiTaCaiFaCount,
getBambooNumber(yearNum,CityId,1) As bambooNumber,
getBambooCount(yearNum,CityId,1) As BambooCount,
getShangPingCaiFa(yearNum,CityId,1) As shangPinCaiFaCount,
getShangpingChuCai(yearNum,CityId,1) As shangPinChuCaiCount,
getFeiShangPing(yearNum,CityId,1) As feiShangPinCount,
getFuYuMianJi(yearNum,CityId,1) as fuYuMianJiCount,
getFuYuChuCaiLiang(yearNum,CityId,1) as fuYuChuCaiCount
from dual
union
select
Common_Function.GetCountyCodeById(county.COUN_ID) as addressCode,
Common_Function.GetCountyNameById(county.COUN_ID) as address,
getTotalNumber(yearNum,county.COUN_ID,2) As TotalNumber,
getTotalCount(yearNum,county.COUN_ID,2)As TotalCount,
getCuttypeNumber(yearNum,county.COUN_ID,2,1) As zhuFaNumber,
getCuttypeCount(yearNum,county.COUN_ID,2,1) As zhuFaCount,
getCuttypeNumber(yearNum,county.COUN_ID,2,2) As fuYuNumber,
getCuttypeCount(yearNum,county.COUN_ID,2,2) As fuYuCaiFaCount,
getCuttypeNumber(yearNum,county.COUN_ID,2,3) As diChanNumber,
getCuttypeCount(yearNum,county.COUN_ID,2,3) As diChanCount,
getCuttypeNumber(yearNum,county.COUN_ID,2,4) As gengXinNumber,
getCuttypeCount(yearNum,county.COUN_ID,2,4) As gengXinCount,
getCuttypeNumber(yearNum,county.COUN_ID,2,5) As qiTaNumber,
getCuttypeCount(yearNum,county.COUN_ID,2,5) As qiTaCaiFaCount,
getBambooNumber(yearNum,county.COUN_ID,2) As bambooNumber,
getBambooCount(yearNum,county.COUN_ID,2) As BambooCount,
getShangPingCaiFa(yearNum,county.COUN_ID,2) As shangPinCaiFaCount,
getShangpingChuCai(yearNum,county.COUN_ID,2) As shangPinChuCaiCount,
getFeiShangPing(yearNum,county.COUN_ID,2) As feiShangPinCount,
getFuYuMianJi(yearNum,county.COUN_ID,2) as fuYuMianJiCount,
getFuYuChuCaiLiang(yearNum,county.COUN_ID,2) as fuYuChuCaiCount
from dual,GG_County county
where county.COUN_SJID = CityId
And getTotalNumber(yearNum,county.COUN_ID,2) > 0
order by 1;
return rc;
ENd;
function LISTPROVINCEEXCEL (YEARNUM In number,PROVINCEID In Number) return outlist as
rc outlist;
Begin
open rc
for
select
Common_Function.GetCountyCodeById(provinceId) as addressCode,
Common_Function.GetCountyNameById(provinceId) as address,
getTotalNumber(yearNum,provinceId,0) As TotalNumber,
getTotalCount(yearNum,provinceId,0)As TotalCount,
getCuttypeNumber(yearNum,provinceId,0,1) As zhuFaNumber,
getCuttypeCount(yearNum,provinceId,0,1) As zhuFaCount,
getCuttypeNumber(yearNum,provinceId,0,2) As fuYuNumber,
getCuttypeCount(yearNum,provinceId,0,2) As fuYuCaiFaCount,
getCuttypeNumber(yearNum,provinceId,0,3) As diChanNumber,
getCuttypeCount(yearNum,provinceId,0,3) As diChanCount,
getCuttypeNumber(yearNum,provinceId,0,4) As gengXinNumber,
getCuttypeCount(yearNum,provinceId,0,4) As gengXinCount,
getCuttypeNumber(yearNum,provinceId,0,5) As qiTaNumber,
getCuttypeCount(yearNum,provinceId,0,5) As qiTaCaiFaCount,
getBambooNumber(yearNum,provinceId,0) As bambooNumber,
getBambooCount(yearNum,provinceId,0) As BambooCount,
getShangPingCaiFa(yearNum,provinceId,0) As shangPinCaiFaCount,
getShangpingChuCai(yearNum,provinceId,0) As shangPinChuCaiCount,
getFeiShangPing(yearNum,provinceId,0) As feiShangPinCount,
getFuYuMianJi(yearNum,provinceId,0) as fuYuMianJiCount,
getFuYuChuCaiLiang(yearNum,provinceId,0) as fuYuChuCaiCount
from dual
union
select
Common_Function.GetCountyCodeById(city.COUN_ID) as addressCode,
Common_Function.GetCountyNameById(city.COUN_ID) as address,
getTotalNumber(yearNum,city.COUN_ID,1) As TotalNumber,
getTotalCount(yearNum,city.COUN_ID,1)As TotalCount,
getCuttypeNumber(yearNum,city.COUN_ID,1,1) As zhuFaNumber,
getCuttypeCount(yearNum,city.COUN_ID,1,1) As zhuFaCount,
getCuttypeNumber(yearNum,city.COUN_ID,1,2) As fuYuNumber,
getCuttypeCount(yearNum,city.COUN_ID,1,2) As fuYuCaiFaCount,
getCuttypeNumber(yearNum,city.COUN_ID,1,3) As diChanNumber,
getCuttypeCount(yearNum,city.COUN_ID,1,3) As diChanCount,
getCuttypeNumber(yearNum,city.COUN_ID,1,4) As gengXinNumber,
getCuttypeCount(yearNum,city.COUN_ID,1,4) As gengXinCount,
getCuttypeNumber(yearNum,city.COUN_ID,1,5) As qiTaNumber,
getCuttypeCount(yearNum,city.COUN_ID,1,5) As qiTaCaiFaCount,
getBambooNumber(yearNum,city.COUN_ID,1) As bambooNumber,
getBambooCount(yearNum,city.COUN_ID,1) As BambooCount,
getShangPingCaiFa(yearNum,city.COUN_ID,1) As shangPinCaiFaCount,
getShangpingChuCai(yearNum,city.COUN_ID,1) As shangPinChuCaiCount,
getFeiShangPing(yearNum,city.COUN_ID,1) As feiShangPinCount,
getFuYuMianJi(yearNum,city.COUN_ID,1) as fuYuMianJiCount,
getFuYuChuCaiLiang(yearNum,city.COUN_ID,1) as fuYuChuCaiCount
from dual,GG_County city
where city.COUN_SJID = provinceId
And getTotalNumber(yearNum,city.COUN_ID,1) > 0
union
select
Common_Function.GetCountyCodeById(county.COUN_ID) as addressCode,
Common_Function.GetCountyNameById(county.COUN_ID) as address,
getTotalNumber(yearNum,county.COUN_ID,2) As TotalNumber,
getTotalCount(yearNum,county.COUN_ID,2)As TotalCount,
getCuttypeNumber(yearNum,county.COUN_ID,2,1) As zhuFaNumber,
getCuttypeCount(yearNum,county.COUN_ID,2,1) As zhuFaCount,
getCuttypeNumber(yearNum,county.COUN_ID,2,2) As fuYuNumber,
getCuttypeCount(yearNum,county.COUN_ID,2,2) As fuYuCaiFaCount,
getCuttypeNumber(yearNum,county.COUN_ID,2,3) As diChanNumber,
getCuttypeCount(yearNum,county.COUN_ID,2,3) As diChanCount,
getCuttypeNumber(yearNum,county.COUN_ID,2,4) As gengXinNumber,
getCuttypeCount(yearNum,county.COUN_ID,2,4) As gengXinCount,
getCuttypeNumber(yearNum,county.COUN_ID,2,5) As qiTaNumber,
getCuttypeCount(yearNum,county.COUN_ID,2,5) As qiTaCaiFaCount,
getBambooNumber(yearNum,county.COUN_ID,2) As bambooNumber,
getBambooCount(yearNum,county.COUN_ID,2) As BambooCount,
getShangPingCaiFa(yearNum,county.COUN_ID,2) As shangPinCaiFaCount,
getShangpingChuCai(yearNum,county.COUN_ID,2) As shangPinChuCaiCount,
getFeiShangPing(yearNum,county.COUN_ID,2) As feiShangPinCount,
getFuYuMianJi(yearNum,county.COUN_ID,2) as fuYuMianJiCount,
getFuYuChuCaiLiang(yearNum,county.COUN_ID,2) as fuYuChuCaiCount
from dual,GG_County county,GG_County ccty
where county.COUN_SJID = ccty.COUN_ID
And ccty.COUN_SJID = provinceId
And getTotalNumber(yearNum,county.COUN_ID,2) > 0
Union
select
Common_Function.GetTownCodeById(town.TOWN_ID) as addressCode,
Common_Function.GetTownNameById(town.TOWN_ID) as address ,
getTotalNumber(yearNum,town.TOWN_ID,3) As TotalNumber,
getTotalCount(yearNum,town.TOWN_ID,3)As TotalCount,
getCuttypeNumber(yearNum,town.TOWN_ID,3,1) As zhuFaNumber,
getCuttypeCount(yearNum,town.TOWN_ID,3,1) As zhuFaCount,
getCuttypeNumber(yearNum,town.TOWN_ID,3,2) As fuYuNumber,
getCuttypeCount(yearNum,town.TOWN_ID,3,2) As fuYuCaiFaCount,
getCuttypeNumber(yearNum,town.TOWN_ID,3,3) As diChanNumber,
getCuttypeCount(yearNum,town.TOWN_ID,3,3) As diChanCount,
getCuttypeNumber(yearNum,town.TOWN_ID,3,4) As gengXinNumber,
getCuttypeCount(yearNum,town.TOWN_ID,3,4) As gengXinCount,
getCuttypeNumber(yearNum,town.TOWN_ID,3,5) As qiTaNumber,
getCuttypeCount(yearNum,town.TOWN_ID,3,5) As qiTaCaiFaCount,
getBambooNumber(yearNum,town.TOWN_ID,3) As bambooNumber,
getBambooCount(yearNum,town.TOWN_ID,3) As BambooCount,
getShangPingCaiFa(yearNum,town.TOWN_ID,3) As shangPinCaiFaCount,
getShangpingChuCai(yearNum,town.TOWN_ID,3) As shangPinChuCaiCount,
getFeiShangPing(yearNum,town.TOWN_ID,3) As feiShangPinCount,
getFuYuMianJi(yearNum,town.TOWN_ID,3) as fuYuMianJiCount,
getFuYuChuCaiLiang(yearNum,town.TOWN_ID,3) as fuYuChuCaiCount
from GG_COUNTY cityy,GG_County ct, GG_TOWN town ,dual
where town.TOWN_XDMID = ct.COUN_ID
And ct.COUN_SJID = cityy.COUN_ID
And cityy.COUN_SJID = provinceId
And getTotalNumber(yearNum,town.TOWN_ID,3) > 0
order by 1;
return rc;
ENd;
function LISTCITYEXCEL (YEARNUM In number,CITYID In Number) return outlist as
rc outlist;
Begin
open rc
for
select
Common_Function.GetCountyCodeById(CityId) as addressCode,
Common_Function.GetCountyNameById(CityId) as address,
getTotalNumber(yearNum,CityId,1) As TotalNumber,
getTotalCount(yearNum,CityId,1)As TotalCount,
getCuttypeNumber(yearNum,CityId,1,1) As zhuFaNumber,
getCuttypeCount(yearNum,CityId,1,1) As zhuFaCount,
getCuttypeNumber(yearNum,CityId,1,2) As fuYuNumber,
getCuttypeCount(yearNum,CityId,1,2) As fuYuCaiFaCount,
getCuttypeNumber(yearNum,CityId,1,3) As diChanNumber,
getCuttypeCount(yearNum,CityId,1,3) As diChanCount,
getCuttypeNumber(yearNum,CityId,1,4) As gengXinNumber,
getCuttypeCount(yearNum,CityId,1,4) As gengXinCount,
getCuttypeNumber(yearNum,CityId,1,5) As qiTaNumber,
getCuttypeCount(yearNum,CityId,1,5) As qiTaCaiFaCount,
getBambooNumber(yearNum,CityId,1) As bambooNumber,
getBambooCount(yearNum,CityId,1) As BambooCount,
getShangPingCaiFa(yearNum,CityId,1) As shangPinCaiFaCount,
getShangpingChuCai(yearNum,CityId,1) As shangPinChuCaiCount,
getFeiShangPing(yearNum,CityId,1) As feiShangPinCount,
getFuYuMianJi(yearNum,CityId,1) as fuYuMianJiCount,
getFuYuChuCaiLiang(yearNum,CityId,1) as fuYuChuCaiCount
from dual
union
select
Common_Function.GetCountyCodeById(county.COUN_ID) as addressCode,
Common_Function.GetCountyNameById(county.COUN_ID) as address,
getTotalNumber(yearNum,county.COUN_ID,2) As TotalNumber,
getTotalCount(yearNum,county.COUN_ID,2)As TotalCount,
getCuttypeNumber(yearNum,county.COUN_ID,2,1) As zhuFaNumber,
getCuttypeCount(yearNum,county.COUN_ID,2,1) As zhuFaCount,
getCuttypeNumber(yearNum,county.COUN_ID,2,2) As fuYuNumber,
getCuttypeCount(yearNum,county.COUN_ID,2,2) As fuYuCaiFaCount,
getCuttypeNumber(yearNum,county.COUN_ID,2,3) As diChanNumber,
getCuttypeCount(yearNum,county.COUN_ID,2,3) As diChanCount,
getCuttypeNumber(yearNum,county.COUN_ID,2,4) As gengXinNumber,
getCuttypeCount(yearNum,county.COUN_ID,2,4) As gengXinCount,
getCuttypeNumber(yearNum,county.COUN_ID,2,5) As qiTaNumber,
getCuttypeCount(yearNum,county.COUN_ID,2,5) As qiTaCaiFaCount,
getBambooNumber(yearNum,county.COUN_ID,2) As bambooNumber,
getBambooCount(yearNum,county.COUN_ID,2) As BambooCount,
getShangPingCaiFa(yearNum,county.COUN_ID,2) As shangPinCaiFaCount,
getShangpingChuCai(yearNum,county.COUN_ID,2) As shangPinChuCaiCount,
getFeiShangPing(yearNum,county.COUN_ID,2) As feiShangPinCount,
getFuYuMianJi(yearNum,county.COUN_ID,2) as fuYuMianJiCount,
getFuYuChuCaiLiang(yearNum,county.COUN_ID,2) as fuYuChuCaiCount
from dual,GG_County county
where county.COUN_SJID = CityId
And getTotalNumber(yearNum,county.COUN_ID,2) > 0
Union
select
Common_Function.GetTownCodeById(town.TOWN_ID) as addressCode,
Common_Function.GetTownNameById(town.TOWN_ID) as address ,
getTotalNumber(yearNum,town.TOWN_ID,3) As TotalNumber,
getTotalCount(yearNum,town.TOWN_ID,3)As TotalCount,
getCuttypeNumber(yearNum,town.TOWN_ID,3,1) As zhuFaNumber,
getCuttypeCount(yearNum,town.TOWN_ID,3,1) As zhuFaCount,
getCuttypeNumber(yearNum,town.TOWN_ID,3,2) As fuYuNumber,
getCuttypeCount(yearNum,town.TOWN_ID,3,2) As fuYuCaiFaCount,
getCuttypeNumber(yearNum,town.TOWN_ID,3,3) As diChanNumber,
getCuttypeCount(yearNum,town.TOWN_ID,3,3) As diChanCount,
getCuttypeNumber(yearNum,town.TOWN_ID,3,4) As gengXinNumber,
getCuttypeCount(yearNum,town.TOWN_ID,3,4) As gengXinCount,
getCuttypeNumber(yearNum,town.TOWN_ID,3,5) As qiTaNumber,
getCuttypeCount(yearNum,town.TOWN_ID,3,5) As qiTaCaiFaCount,
getBambooNumber(yearNum,town.TOWN_ID,3) As bambooNumber,
getBambooCount(yearNum,town.TOWN_ID,3) As BambooCount,
getShangPingCaiFa(yearNum,town.TOWN_ID,3) As shangPinCaiFaCount,
getShangpingChuCai(yearNum,town.TOWN_ID,3) As shangPinChuCaiCount,
getFeiShangPing(yearNum,town.TOWN_ID,3) As feiShangPinCount,
getFuYuMianJi(yearNum,town.TOWN_ID,3) as fuYuMianJiCount,
getFuYuChuCaiLiang(yearNum,town.TOWN_ID,3) as fuYuChuCaiCount
from GG_County ct, GG_TOWN town ,dual
where town.TOWN_XDMID = ct.COUN_ID
And ct.COUN_SJID = CityId
And getTotalNumber(yearNum,town.TOWN_ID,3) > 0
order by 1;
return rc;
ENd;
function LISTCOUNTY (YEARNUM In number,COUNTYID In Number) return outlist as
rc outlist;
Begin
open rc
for
select
Common_Function.GetCountyCodeById(countyId) as addressCode,
Common_Function.GetCountyNameById(countyId) as address,
getTotalNumber(yearNum,countyId,2) As TotalNumber,
getTotalCount(yearNum,countyId,2)As TotalCount,
getCuttypeNumber(yearNum,countyId,2,1) As zhuFaNumber,
getCuttypeCount(yearNum,countyId,2,1) As zhuFaCount,
getCuttypeNumber(yearNum,countyId,2,2) As fuYuNumber,
getCuttypeCount(yearNum,countyId,2,2) As fuYuCaiFaCount,
getCuttypeNumber(yearNum,countyId,2,3) As diChanNumber,
getCuttypeCount(yearNum,countyId,2,3) As diChanCount,
getCuttypeNumber(yearNum,countyId,2,4) As gengXinNumber,
getCuttypeCount(yearNum,countyId,2,4) As gengXinCount,
getCuttypeNumber(yearNum,countyId,2,5) As qiTaNumber,
getCuttypeCount(yearNum,countyId,2,5) As qiTaCaiFaCount,
getBambooNumber(yearNum,countyId,2) As bambooNumber,
getBambooCount(yearNum,countyId,2) As BambooCount,
getShangPingCaiFa(yearNum,countyId,2) As shangPinCaiFaCount,
getShangpingChuCai(yearNum,countyId,2) As shangPinChuCaiCount,
getFeiShangPing(yearNum,countyId,2) As feiShangPinCount,
getFuYuMianJi(yearNum,countyId,2) as fuYuMianJiCount,
getFuYuChuCaiLiang(yearNum,countyId,2) as fuYuChuCaiCount
from dual
where getTotalNumber(yearNum,countyId,2) > 0
Union
select
Common_Function.GetTownCodeById(town.TOWN_ID) as addressCode,
Common_Function.GetTownNameById(town.TOWN_ID) as address ,
getTotalNumber(yearNum,town.TOWN_ID,3) As TotalNumber,
getTotalCount(yearNum,town.TOWN_ID,3)As TotalCount,
getCuttypeNumber(yearNum,town.TOWN_ID,3,1) As zhuFaNumber,
getCuttypeCount(yearNum,town.TOWN_ID,3,1) As zhuFaCount,
getCuttypeNumber(yearNum,town.TOWN_ID,3,2) As fuYuNumber,
getCuttypeCount(yearNum,town.TOWN_ID,3,2) As fuYuCaiFaCount,
getCuttypeNumber(yearNum,town.TOWN_ID,3,3) As diChanNumber,
getCuttypeCount(yearNum,town.TOWN_ID,3,3) As diChanCount,
getCuttypeNumber(yearNum,town.TOWN_ID,3,4) As gengXinNumber,
getCuttypeCount(yearNum,town.TOWN_ID,3,4) As gengXinCount,
getCuttypeNumber(yearNum,town.TOWN_ID,3,5) As qiTaNumber,
getCuttypeCount(yearNum,town.TOWN_ID,3,5) As qiTaCaiFaCount,
getBambooNumber(yearNum,town.TOWN_ID,3) As bambooNumber,
getBambooCount(yearNum,town.TOWN_ID,3) As BambooCount,
getShangPingCaiFa(yearNum,town.TOWN_ID,3) As shangPinCaiFaCount,
getShangpingChuCai(yearNum,town.TOWN_ID,3) As shangPinChuCaiCount,
getFeiShangPing(yearNum,town.TOWN_ID,3) As feiShangPinCount,
getFuYuMianJi(yearNum,town.TOWN_ID,3) as fuYuMianJiCount,
getFuYuChuCaiLiang(yearNum,town.TOWN_ID,3) as fuYuChuCaiCount
from GG_TOWN town ,dual
where town.TOWN_XDMID = countyId
And getTotalNumber(yearNum,town.TOWN_ID,3) > 0
order by 1;
return rc;
ENd;
function GETCUTTYPENUMBER (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number,CUTTYPEID In Number) return Number as
totalnum number;
Begin
-- province
if degreeNum = 0 then
select count(*)
into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And Common_Function.GetCuttypeCodeById(cutreply.APRE_CLASID) = cuttypeId;
end if;
-- city
if degreeNum = 1 then
select count(*)
into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And Common_Function.GetCuttypeCodeById(cutreply.APRE_CLASID) = cuttypeId
And Common_Function.GetCityIdByCountyId(cutreply.APAP_COUNID) = idNum;
end if;
-- county
if degreeNum = 2 then
select count(*)
into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And Common_Function.GetCuttypeCodeById(cutreply.APRE_CLASID) = cuttypeId
And cutreply.APAP_COUNID = idNum;
end if;
-- town
if degreeNum = 3 then
select count(*)
into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And Common_Function.GetCuttypeCodeById(cutreply.APRE_CLASID) = cuttypeId
And cutreply.APAP_TOWNID = idNum;
end if;
return totalNum;
End;
function GETCUTTYPECOUNT (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number,CUTTYPEID In Number) return Number as
totalNum number;
Begin
-- province
if degreeNum = 0 then
select sum(cuttree.TRSS_XJHJ) into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And Common_Function.GetCuttypeCodeById( cutreply.APRE_CLASID ) = cuttypeId;
end if;
-- city
if degreeNum = 1 then
select sum(cuttree.TRSS_XJHJ) into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And Common_Function.GetCuttypeCodeById( cutreply.APRE_CLASID ) = cuttypeId
And Common_Function.GetCityIdByCountyId(cutreply.APAP_COUNID) = idNum;
end if;
-- county
if degreeNum = 2 then
select sum(cuttree.TRSS_XJHJ) into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And Common_Function.GetCuttypeCodeById( cutreply.APRE_CLASID ) = cuttypeId
And cutreply.APAP_COUNID = idNum;
end if;
-- town
if degreeNum = 3 then
select sum(cuttree.TRSS_XJHJ) into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And Common_Function.GetCuttypeCodeById( cutreply.APRE_CLASID ) = cuttypeId
And cutreply.APAP_TOWNID = idNum;
end if;
return totalNum;
End;
function GETTOTALNUMBER (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number) return Number as
totalNum number;
Begin
-- province
if degreeNum = 0 then
select count(* )
into totalNum
from CF_APPLYAPPROVE cutreply ,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And cuttree.TRSS_APAPID = cutreply.APAP_ID
and cutreply.APAP_ZT = 3;
end if;
-- city
if degreeNum = 1 then
select count(*)
into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
And Common_Function.GetCityIdByCountyId(cutreply.APAP_COUNID) = idNum
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And cuttree.TRSS_APAPID = cutreply.APAP_ID
and cutreply.APAP_ZT = 3;
end if;
-- county
if degreeNum = 2 then
select count(*)
into totalNum
from CF_APPLYAPPROVE cutreply ,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
And cutreply.APAP_COUNID = idNum
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And cuttree.TRSS_APAPID = cutreply.APAP_ID
and cutreply.APAP_ZT = 3;
end if;
-- town
if degreeNum = 3 then
select count(*)
into totalNum
from CF_APPLYAPPROVE cutreply ,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
And cutreply.APAP_TOWNID = idNum
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And cuttree.TRSS_APAPID = cutreply.APAP_ID
and cutreply.APAP_ZT = 3;
end if;
return totalNum;
End;
function GETTOTALCOUNT (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number) return Number as
totalNum number;
Begin
-- province
if degreeNum = 0 then
select sum(cuttree.TRSS_XJHJ) into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And cuttree.TRSS_APAPID = cutreply.APAP_ID;
end if;
-- city
if degreeNum = 1 then
select sum(cuttree.TRSS_XJHJ)
into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and Common_Function.GetCityIdByCountyId(cutreply.APAP_COUNID) = idNum
and cutreply.APAP_ZT = 3
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And cuttree.TRSS_APAPID = cutreply.APAP_ID;
end if;
-- county
if degreeNum = 2 then
select sum(cuttree.TRSS_XJHJ)
into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_COUNID = idNum
and cutreply.APAP_ZT = 3
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And cuttree.TRSS_APAPID = cutreply.APAP_ID;
end if;
-- town
if degreeNum = 3 then
select sum(cuttree.TRSS_XJHJ)
into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
And cutreply.APAP_TOWNID = idNum
and cutreply.APAP_ZT = 3
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And cuttree.TRSS_APAPID = cutreply.APAP_ID;
end if;
return totalNum;
End;
function GETBAMBOONUMBER (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number) return Number as
totalNum number;
Begin
-- province
if degreeNum = 0 then
select count( distinct (cutreply.APAP_ID ))
into totalNum
from CF_APPLYAPPROVE cutreply ,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
And cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) = '496' ;
end if;
-- city
if degreeNum = 1 then
select count( distinct (cutreply.APAP_ID ))
into totalNum
from CF_APPLYAPPROVE cutreply ,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
And Common_Function.GetCityIdByCountyId(cutreply.APAP_COUNID) = idNum
and cutreply.APAP_ZT = 3
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) = '496'
And cuttree.trss_apapId = cutreply.apap_id;
end if;
-- county
if degreeNum = 2 then
select count( distinct (cutreply.APAP_ID ))
into totalNum
from CF_APPLYAPPROVE cutreply ,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
And cutreply.APAP_COUNID = idNum
and cutreply.APAP_ZT = 3
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) = '496'
And cuttree.trss_apapId = cutreply.apap_id ;
end if;
-- town
if degreeNum = 3 then
select count( distinct (cutreply.APAP_ID ))
into totalNum
from CF_APPLYAPPROVE cutreply ,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
And cutreply.APAP_TOWNID = idNum
and cutreply.APAP_ZT = 3
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) = '496'
And cuttree.trss_apapId = cutreply.apap_id ;
end if;
return totalNum;
End;
function GETBAMBOOCOUNT (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number) return number as
totalNum number;
begin
-- province
if degreeNum = 0 then
select sum(cuttree.TRSS_NZZS)
into totalNum
from CF_APPLYAPPROVE cutreply ,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) = '496'
And cuttree.trss_apapId = cutreply.apap_id ;
end if;
-- city
if degreeNum = 1 then
select sum(cuttree.TRSS_NZZS)
into totalNum
from CF_APPLYAPPROVE cutreply ,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
And Common_Function.GetCityIdByCountyId(cutreply.APAP_COUNID) = idNum
and cutreply.APAP_ZT = 3
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) = '496'
And cuttree.trss_apapId = cutreply.apap_id ;
end if;
-- county
if degreeNum = 2 then
select sum(cuttree.TRSS_NZZS)
into totalNum
from CF_APPLYAPPROVE cutreply ,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
And cutreply.APAP_COUNID = idNum
and cutreply.APAP_ZT = 3
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) = '496'
And cuttree.trss_apapId = cutreply.apap_id ;
end if;
-- town
if degreeNum = 3 then
select sum(cuttree.TRSS_NZZS)
into totalNum
from CF_APPLYAPPROVE cutreply ,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
And cutreply.APAP_TOWNID = idNum
and cutreply.APAP_ZT = 3
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) = '496'
And cuttree.trss_apapId = cutreply.apap_id ;
end if;
return totalNum;
End;
function GETFUYUMIANJI (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number) return number as
totalNum number;
Begin
-- province
if degreeNum = 0 then
select sum(cutreply.APAP_MJ) into totalNum
from CF_APPLYAPPROVE cutreply ,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And Common_Function.GetCuttypeCodeById( cutreply.APRE_CLASID ) = 2;
end if;
-- city
if degreeNum = 1 then
select sum(cutreply.APAP_MJ) into totalNum
from CF_APPLYAPPROVE cutreply ,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetCuttypeCodeById( cutreply.APRE_CLASID ) = 2
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And Common_Function.GetCityIdByCountyId(cutreply.APAP_COUNID) = idNum;
end if;
-- county
if degreeNum = 2 then
select sum( cutreply.APAP_MJ) into totalNum
from CF_APPLYAPPROVE cutreply ,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetCuttypeCodeById( cutreply.APRE_CLASID ) = 2
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And cutreply.APAP_COUNID = idNum;
end if;
-- town
if degreeNum = 3 then
select sum(cutreply.APAP_MJ) into totalNum
from CF_APPLYAPPROVE cutreply ,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetCuttypeCodeById( cutreply.APRE_CLASID ) = 2
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And cutreply.APAP_TOWNID = idNum;
end if;
return totalNum;
End;
function GETFUYUCHUCAILIANG (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number) return number as
totalNum number;
Begin
-- province
if degreeNum = 0 then
select sum(cuttree.TRSS_SCCL) into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And Common_Function.GetCuttypeCodeById( cutreply.APRE_CLASID ) = 2;
end if;
-- city
if degreeNum = 1 then
select sum(cuttree.TRSS_SCCL) into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetCuttypeCodeById( cutreply.APRE_CLASID ) = 2
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And Common_Function.GetCityIdByCountyId(cutreply.APAP_COUNID) = idNum;
end if;
-- county
if degreeNum = 2 then
select sum(cuttree.TRSS_SCCL) into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetCuttypeCodeById( cutreply.APRE_CLASID ) = 2
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And cutreply.APAP_COUNID = idNum;
end if;
-- town
if degreeNum = 3 then
select sum(cuttree.TRSS_SCCL) into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetCuttypeCodeById( cutreply.APRE_CLASID ) = 2
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And cutreply.APAP_TOWNID = idNum;
end if;
return totalNum;
End;
function GETSHANGPINGCAIFA (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number) return Number as
totalNum number;
Begin
-- province
if degreeNum = 0 then
select sum(cuttree.TRSS_SCFL) into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And cuttree.TRSS_APAPID = cutreply.APAP_ID;
end if;
-- city
if degreeNum = 1 then
select sum(cuttree.TRSS_SCFL) into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And Common_Function.GetCityIdByCountyId(cutreply.APAP_COUNID) = idNum;
end if;
-- county
if degreeNum = 2 then
select sum(cuttree.TRSS_SCFL) into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And cutreply.APAP_COUNID = idNum;
end if;
-- town
if degreeNum = 3 then
select sum(cuttree.TRSS_SCFL) into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And cutreply.APAP_TOWNID = idNum;
end if;
return totalNum;
End;
function GETSHANGPINGCHUCAI (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number) return Number as
totalNum number;
Begin
-- province
if degreeNum = 0 then
select sum(cuttree.TRSS_SCCL) into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And cuttree.TRSS_APAPID = cutreply.APAP_ID;
end if;
-- city
if degreeNum = 1 then
select sum(cuttree.TRSS_SCCL) into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And Common_Function.GetCityIdByCountyId(cutreply.APAP_COUNID) = idNum;
end if;
-- county
if degreeNum = 2 then
select sum(cuttree.TRSS_SCCL) into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And cutreply.APAP_COUNID = idNum;
end if;
-- town
if degreeNum = 3 then
select sum(cuttree.TRSS_SCCL) into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And cutreply.APAP_TOWNID = idNum;
end if;
return totalNum;
End;
function GETFEISHANGPING (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number) return Number as
totalNum number;
Begin
-- province
if degreeNum = 0 then
select sum(cuttree.TRSS_FSPC) into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And cuttree.TRSS_APAPID = cutreply.APAP_ID;
end if;
-- city
if degreeNum = 1 then
select sum(cuttree.TRSS_FSPC) into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And Common_Function.GetCityIdByCountyId(cutreply.APAP_COUNID) = idNum;
end if;
-- county
if degreeNum = 2 then
select sum(cuttree.TRSS_FSPC) into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And cutreply.APAP_COUNID = idNum;
end if;
-- town
if degreeNum = 3 then
select sum(cuttree.TRSS_FSPC) into totalNum
from CF_APPLYAPPROVE cutreply,CF_TREESPECIES cuttree
where cutreply.APAP_BXND = yearNum
and cutreply.APAP_ZT = 3
And cuttree.TRSS_APAPID = cutreply.APAP_ID
And Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) <> '496'
And cutreply.APAP_TOWNID = idNum;
end if;
return totalNum;
End;
end CUT_COUNTSENDBOOK;
/