沉睡森林@漂在北京

本处文章除注明“转载”外均为原创,转载请注明出处。

  BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理 ::
  152 随笔 :: 4 文章 :: 114 评论 :: 0 Trackbacks
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 Numberreturn outlist;
   
function LISTCITY (YEARNUM In number,CITYID In Numberreturn outlist;
   
function LISTPROVINCEEXCEL (YEARNUM In number,PROVINCEID In Numberreturn outlist;
   
function LISTCITYEXCEL (YEARNUM In number,CITYID In Numberreturn outlist;
   
function LISTCOUNTY (YEARNUM In number,COUNTYID In Numberreturn outlist;
   
function GETCUTTYPENUMBER (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number,CUTTYPEID In Numberreturn Number;
   
function GETCUTTYPECOUNT (YEARNUM In Number,IDNUM In Number,DEGREENUM In Number,CUTTYPEID In Numberreturn Number;
   
function GETTOTALNUMBER (YEARNUM In Number,IDNUM In Number,DEGREENUM In Numberreturn Number;
   
function GETTOTALCOUNT (YEARNUM In Number,IDNUM In Number,DEGREENUM In Numberreturn Number;
   
function GETBAMBOONUMBER (YEARNUM In Number,IDNUM In Number,DEGREENUM In Numberreturn Number;
   
function GETBAMBOOCOUNT (YEARNUM In Number,IDNUM In Number,DEGREENUM In Numberreturn number;
   
function GETFUYUMIANJI (YEARNUM In Number,IDNUM In Number,DEGREENUM In Numberreturn number;
   
function GETFUYUCHUCAILIANG (YEARNUM In Number,IDNUM In Number,DEGREENUM In Numberreturn number;
   
function GETSHANGPINGCAIFA (YEARNUM In Number,IDNUM In Number,DEGREENUM In Numberreturn Number;
   
function GETSHANGPINGCHUCAI (YEARNUM In Number,IDNUM In Number,DEGREENUM In Numberreturn Number;
   
function GETFEISHANGPING (YEARNUM In Number,IDNUM In Number,DEGREENUM In Numberreturn 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 Numberreturn outlist as
   rc outlist;
    
Begin
         
open rc 
        
for 
           
select 
                Common_Function.GetCountyCodeById(provinceId) 
as addressCode,
                Common_Function.GetCountyNameById(provinceId) 
as address,
               
                getTotalNumber(yearNum,provinceId,
0As TotalNumber,
               getTotalCount(yearNum,provinceId,
0)As TotalCount,
               
               getCuttypeNumber(yearNum,provinceId,
0,1As zhuFaNumber,
               getCuttypeCount(yearNum,provinceId,
0,1As zhuFaCount,
               
               getCuttypeNumber(yearNum,provinceId,
0,2As fuYuNumber,
               getCuttypeCount(yearNum,provinceId,
0,2As fuYuCaiFaCount,
               
               getCuttypeNumber(yearNum,provinceId,
0,3As diChanNumber,
               getCuttypeCount(yearNum,provinceId,
0,3As diChanCount,
               
               getCuttypeNumber(yearNum,provinceId,
0,4As gengXinNumber,
               getCuttypeCount(yearNum,provinceId,
0,4As gengXinCount,
               
               getCuttypeNumber(yearNum,provinceId,
0,5As qiTaNumber,
               getCuttypeCount(yearNum,provinceId,
0,5As qiTaCaiFaCount,
               
               getBambooNumber(yearNum,provinceId,
0As bambooNumber,
               getBambooCount(yearNum,provinceId,
0As BambooCount,
               
               getShangPingCaiFa(yearNum,provinceId,
0As shangPinCaiFaCount,
               getShangpingChuCai(yearNum,provinceId,
0As shangPinChuCaiCount,
               getFeiShangPing(yearNum,provinceId,
0As  feiShangPinCount,
               
               getFuYuMianJi(yearNum,provinceId,
0as fuYuMianJiCount,
               getFuYuChuCaiLiang(yearNum,provinceId,
0as 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,
1As TotalNumber,
               getTotalCount(yearNum,city.COUN_ID,
1)As TotalCount,
               
               getCuttypeNumber(yearNum,city.COUN_ID,
1,1As zhuFaNumber,
               getCuttypeCount(yearNum,city.COUN_ID,
1,1As zhuFaCount,
               
               getCuttypeNumber(yearNum,city.COUN_ID,
1,2As fuYuNumber,
               getCuttypeCount(yearNum,city.COUN_ID,
1,2As fuYuCaiFaCount,
               
               getCuttypeNumber(yearNum,city.COUN_ID,
1,3As diChanNumber,
               getCuttypeCount(yearNum,city.COUN_ID,
1,3As diChanCount,
               
               getCuttypeNumber(yearNum,city.COUN_ID,
1,4As gengXinNumber,
               getCuttypeCount(yearNum,city.COUN_ID,
1,4As gengXinCount,
               
               getCuttypeNumber(yearNum,city.COUN_ID,
1,5As qiTaNumber,
               getCuttypeCount(yearNum,city.COUN_ID,
1,5As qiTaCaiFaCount,
               
               getBambooNumber(yearNum,city.COUN_ID,
1As bambooNumber,
               getBambooCount(yearNum,city.COUN_ID,
1As BambooCount,
               
               getShangPingCaiFa(yearNum,city.COUN_ID,
1As shangPinCaiFaCount,
               getShangpingChuCai(yearNum,city.COUN_ID,
1As shangPinChuCaiCount,
               getFeiShangPing(yearNum,city.COUN_ID,
1As  feiShangPinCount,
               
               getFuYuMianJi(yearNum,city.COUN_ID,
1as fuYuMianJiCount,
               getFuYuChuCaiLiang(yearNum,city.COUN_ID,
1as 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 Numberreturn outlist as
   rc outlist;
    
Begin
         
open rc 
        
for 
          
select 
                Common_Function.GetCountyCodeById(CityId) 
as addressCode,
                Common_Function.GetCountyNameById(CityId) 
as address,
               
                getTotalNumber(yearNum,CityId,
1As TotalNumber,
               getTotalCount(yearNum,CityId,
1)As TotalCount,
               
               getCuttypeNumber(yearNum,CityId,
1,1As zhuFaNumber,
               getCuttypeCount(yearNum,CityId,
1,1As zhuFaCount,
               
               getCuttypeNumber(yearNum,CityId,
1,2As fuYuNumber,
               getCuttypeCount(yearNum,CityId,
1,2As fuYuCaiFaCount,
               
               getCuttypeNumber(yearNum,CityId,
1,3As diChanNumber,
               getCuttypeCount(yearNum,CityId,
1,3As diChanCount,
               
               getCuttypeNumber(yearNum,CityId,
1,4As gengXinNumber,
               getCuttypeCount(yearNum,CityId,
1,4As gengXinCount,
               
               getCuttypeNumber(yearNum,CityId,
1,5As qiTaNumber,
               getCuttypeCount(yearNum,CityId,
1,5As qiTaCaiFaCount,
               
               getBambooNumber(yearNum,CityId,
1As bambooNumber,
               getBambooCount(yearNum,CityId,
1As BambooCount,
               
               getShangPingCaiFa(yearNum,CityId,
1As shangPinCaiFaCount,
               getShangpingChuCai(yearNum,CityId,
1As shangPinChuCaiCount,
               getFeiShangPing(yearNum,CityId,
1As  feiShangPinCount,
               
               getFuYuMianJi(yearNum,CityId,
1as fuYuMianJiCount,
               getFuYuChuCaiLiang(yearNum,CityId,
1as 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,
2As TotalNumber,
               getTotalCount(yearNum,county.COUN_ID,
2)As TotalCount,
               
               getCuttypeNumber(yearNum,county.COUN_ID,
2,1As zhuFaNumber,
               getCuttypeCount(yearNum,county.COUN_ID,
2,1As zhuFaCount,
               
               getCuttypeNumber(yearNum,county.COUN_ID,
2,2As fuYuNumber,
               getCuttypeCount(yearNum,county.COUN_ID,
2,2As fuYuCaiFaCount,
               
               getCuttypeNumber(yearNum,county.COUN_ID,
2,3As diChanNumber,
               getCuttypeCount(yearNum,county.COUN_ID,
2,3As diChanCount,
               
               getCuttypeNumber(yearNum,county.COUN_ID,
2,4As gengXinNumber,
               getCuttypeCount(yearNum,county.COUN_ID,
2,4As gengXinCount,
               
               getCuttypeNumber(yearNum,county.COUN_ID,
2,5As qiTaNumber,
               getCuttypeCount(yearNum,county.COUN_ID,
2,5As qiTaCaiFaCount,
               
               getBambooNumber(yearNum,county.COUN_ID,
2As bambooNumber,
               getBambooCount(yearNum,county.COUN_ID,
2As BambooCount,
               
               getShangPingCaiFa(yearNum,county.COUN_ID,
2As shangPinCaiFaCount,
               getShangpingChuCai(yearNum,county.COUN_ID,
2As shangPinChuCaiCount,
               getFeiShangPing(yearNum,county.COUN_ID,
2As  feiShangPinCount,
               
               getFuYuMianJi(yearNum,county.COUN_ID,
2as fuYuMianJiCount,
               getFuYuChuCaiLiang(yearNum,county.COUN_ID,
2as 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 Numberreturn outlist as
   rc outlist;
    
Begin
         
open rc 
        
for 
           
select 
                Common_Function.GetCountyCodeById(provinceId) 
as addressCode,
                Common_Function.GetCountyNameById(provinceId) 
as address,
               
                getTotalNumber(yearNum,provinceId,
0As TotalNumber,
               getTotalCount(yearNum,provinceId,
0)As TotalCount,
               
               getCuttypeNumber(yearNum,provinceId,
0,1As zhuFaNumber,
               getCuttypeCount(yearNum,provinceId,
0,1As zhuFaCount,
               
               getCuttypeNumber(yearNum,provinceId,
0,2As fuYuNumber,
               getCuttypeCount(yearNum,provinceId,
0,2As fuYuCaiFaCount,
               
               getCuttypeNumber(yearNum,provinceId,
0,3As diChanNumber,
               getCuttypeCount(yearNum,provinceId,
0,3As diChanCount,
               
               getCuttypeNumber(yearNum,provinceId,
0,4As gengXinNumber,
               getCuttypeCount(yearNum,provinceId,
0,4As gengXinCount,
               
               getCuttypeNumber(yearNum,provinceId,
0,5As qiTaNumber,
               getCuttypeCount(yearNum,provinceId,
0,5As qiTaCaiFaCount,
               
               getBambooNumber(yearNum,provinceId,
0As bambooNumber,
               getBambooCount(yearNum,provinceId,
0As BambooCount,
               
               getShangPingCaiFa(yearNum,provinceId,
0As shangPinCaiFaCount,
               getShangpingChuCai(yearNum,provinceId,
0As shangPinChuCaiCount,
               getFeiShangPing(yearNum,provinceId,
0As  feiShangPinCount,
               
               getFuYuMianJi(yearNum,provinceId,
0as fuYuMianJiCount,
               getFuYuChuCaiLiang(yearNum,provinceId,
0as 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,
1As TotalNumber,
               getTotalCount(yearNum,city.COUN_ID,
1)As TotalCount,
               
               getCuttypeNumber(yearNum,city.COUN_ID,
1,1As zhuFaNumber,
               getCuttypeCount(yearNum,city.COUN_ID,
1,1As zhuFaCount,
               
               getCuttypeNumber(yearNum,city.COUN_ID,
1,2As fuYuNumber,
               getCuttypeCount(yearNum,city.COUN_ID,
1,2As fuYuCaiFaCount,
               
               getCuttypeNumber(yearNum,city.COUN_ID,
1,3As diChanNumber,
               getCuttypeCount(yearNum,city.COUN_ID,
1,3As diChanCount,
               
               getCuttypeNumber(yearNum,city.COUN_ID,
1,4As gengXinNumber,
               getCuttypeCount(yearNum,city.COUN_ID,
1,4As gengXinCount,
               
               getCuttypeNumber(yearNum,city.COUN_ID,
1,5As qiTaNumber,
               getCuttypeCount(yearNum,city.COUN_ID,
1,5As qiTaCaiFaCount,
               
               getBambooNumber(yearNum,city.COUN_ID,
1As bambooNumber,
               getBambooCount(yearNum,city.COUN_ID,
1As BambooCount,
               
               getShangPingCaiFa(yearNum,city.COUN_ID,
1As shangPinCaiFaCount,
               getShangpingChuCai(yearNum,city.COUN_ID,
1As shangPinChuCaiCount,
               getFeiShangPing(yearNum,city.COUN_ID,
1As  feiShangPinCount,
               
               getFuYuMianJi(yearNum,city.COUN_ID,
1as fuYuMianJiCount,
               getFuYuChuCaiLiang(yearNum,city.COUN_ID,
1as 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,
2As TotalNumber,
               getTotalCount(yearNum,county.COUN_ID,
2)As TotalCount,
               
               getCuttypeNumber(yearNum,county.COUN_ID,
2,1As zhuFaNumber,
               getCuttypeCount(yearNum,county.COUN_ID,
2,1As zhuFaCount,
               
               getCuttypeNumber(yearNum,county.COUN_ID,
2,2As fuYuNumber,
               getCuttypeCount(yearNum,county.COUN_ID,
2,2As fuYuCaiFaCount,
               
               getCuttypeNumber(yearNum,county.COUN_ID,
2,3As diChanNumber,
               getCuttypeCount(yearNum,county.COUN_ID,
2,3As diChanCount,
               
               getCuttypeNumber(yearNum,county.COUN_ID,
2,4As gengXinNumber,
               getCuttypeCount(yearNum,county.COUN_ID,
2,4As gengXinCount,
               
               getCuttypeNumber(yearNum,county.COUN_ID,
2,5As qiTaNumber,
               getCuttypeCount(yearNum,county.COUN_ID,
2,5As qiTaCaiFaCount,
               
               getBambooNumber(yearNum,county.COUN_ID,
2As bambooNumber,
               getBambooCount(yearNum,county.COUN_ID,
2As BambooCount,
               
               getShangPingCaiFa(yearNum,county.COUN_ID,
2As shangPinCaiFaCount,
               getShangpingChuCai(yearNum,county.COUN_ID,
2As shangPinChuCaiCount,
               getFeiShangPing(yearNum,county.COUN_ID,
2As  feiShangPinCount,
               
               getFuYuMianJi(yearNum,county.COUN_ID,
2as fuYuMianJiCount,
               getFuYuChuCaiLiang(yearNum,county.COUN_ID,
2as 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,
3As TotalNumber,
               getTotalCount(yearNum,town.TOWN_ID,
3)As TotalCount,
               
               getCuttypeNumber(yearNum,town.TOWN_ID,
3,1As zhuFaNumber,
               getCuttypeCount(yearNum,town.TOWN_ID,
3,1As zhuFaCount,
               
               getCuttypeNumber(yearNum,town.TOWN_ID,
3,2As fuYuNumber,
               getCuttypeCount(yearNum,town.TOWN_ID,
3,2As fuYuCaiFaCount,
               
               getCuttypeNumber(yearNum,town.TOWN_ID,
3,3As diChanNumber,
               getCuttypeCount(yearNum,town.TOWN_ID,
3,3As diChanCount,
               
               getCuttypeNumber(yearNum,town.TOWN_ID,
3,4As gengXinNumber,
               getCuttypeCount(yearNum,town.TOWN_ID,
3,4As gengXinCount,
               
               getCuttypeNumber(yearNum,town.TOWN_ID,
3,5As qiTaNumber,
               getCuttypeCount(yearNum,town.TOWN_ID,
3,5As qiTaCaiFaCount,
               
               getBambooNumber(yearNum,town.TOWN_ID,
3As bambooNumber,
               getBambooCount(yearNum,town.TOWN_ID,
3As BambooCount,
               
               getShangPingCaiFa(yearNum,town.TOWN_ID,
3As shangPinCaiFaCount,
               getShangpingChuCai(yearNum,town.TOWN_ID,
3As shangPinChuCaiCount,
               getFeiShangPing(yearNum,town.TOWN_ID,
3As  feiShangPinCount,
               
               getFuYuMianJi(yearNum,town.TOWN_ID,
3as fuYuMianJiCount,
               getFuYuChuCaiLiang(yearNum,town.TOWN_ID,
3as 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 Numberreturn outlist as
   rc outlist;
    
Begin
         
open rc 
        
for 
          
select 
                Common_Function.GetCountyCodeById(CityId) 
as addressCode,
                Common_Function.GetCountyNameById(CityId) 
as address,
               
                getTotalNumber(yearNum,CityId,
1As TotalNumber,
               getTotalCount(yearNum,CityId,
1)As TotalCount,
               
               getCuttypeNumber(yearNum,CityId,
1,1As zhuFaNumber,
               getCuttypeCount(yearNum,CityId,
1,1As zhuFaCount,
               
               getCuttypeNumber(yearNum,CityId,
1,2As fuYuNumber,
               getCuttypeCount(yearNum,CityId,
1,2As fuYuCaiFaCount,
               
               getCuttypeNumber(yearNum,CityId,
1,3As diChanNumber,
               getCuttypeCount(yearNum,CityId,
1,3As diChanCount,
               
               getCuttypeNumber(yearNum,CityId,
1,4As gengXinNumber,
               getCuttypeCount(yearNum,CityId,
1,4As gengXinCount,
               
               getCuttypeNumber(yearNum,CityId,
1,5As qiTaNumber,
               getCuttypeCount(yearNum,CityId,
1,5As qiTaCaiFaCount,
               
               getBambooNumber(yearNum,CityId,
1As bambooNumber,
               getBambooCount(yearNum,CityId,
1As BambooCount,
               
               getShangPingCaiFa(yearNum,CityId,
1As shangPinCaiFaCount,
               getShangpingChuCai(yearNum,CityId,
1As shangPinChuCaiCount,
               getFeiShangPing(yearNum,CityId,
1As  feiShangPinCount,
               
               getFuYuMianJi(yearNum,CityId,
1as fuYuMianJiCount,
               getFuYuChuCaiLiang(yearNum,CityId,
1as 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,
2As TotalNumber,
               getTotalCount(yearNum,county.COUN_ID,
2)As TotalCount,
               
               getCuttypeNumber(yearNum,county.COUN_ID,
2,1As zhuFaNumber,
               getCuttypeCount(yearNum,county.COUN_ID,
2,1As zhuFaCount,
               
               getCuttypeNumber(yearNum,county.COUN_ID,
2,2As fuYuNumber,
               getCuttypeCount(yearNum,county.COUN_ID,
2,2As fuYuCaiFaCount,
               
               getCuttypeNumber(yearNum,county.COUN_ID,
2,3As diChanNumber,
               getCuttypeCount(yearNum,county.COUN_ID,
2,3As diChanCount,
               
               getCuttypeNumber(yearNum,county.COUN_ID,
2,4As gengXinNumber,
               getCuttypeCount(yearNum,county.COUN_ID,
2,4As gengXinCount,
               
               getCuttypeNumber(yearNum,county.COUN_ID,
2,5As qiTaNumber,
               getCuttypeCount(yearNum,county.COUN_ID,
2,5As qiTaCaiFaCount,
               
               getBambooNumber(yearNum,county.COUN_ID,
2As bambooNumber,
               getBambooCount(yearNum,county.COUN_ID,
2As BambooCount,
               
               getShangPingCaiFa(yearNum,county.COUN_ID,
2As shangPinCaiFaCount,
               getShangpingChuCai(yearNum,county.COUN_ID,
2As shangPinChuCaiCount,
               getFeiShangPing(yearNum,county.COUN_ID,
2As  feiShangPinCount,
               
               getFuYuMianJi(yearNum,county.COUN_ID,
2as fuYuMianJiCount,
               getFuYuChuCaiLiang(yearNum,county.COUN_ID,
2as 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,
3As TotalNumber,
               getTotalCount(yearNum,town.TOWN_ID,
3)As TotalCount,
               
               getCuttypeNumber(yearNum,town.TOWN_ID,
3,1As zhuFaNumber,
               getCuttypeCount(yearNum,town.TOWN_ID,
3,1As zhuFaCount,
               
               getCuttypeNumber(yearNum,town.TOWN_ID,
3,2As fuYuNumber,
               getCuttypeCount(yearNum,town.TOWN_ID,
3,2As fuYuCaiFaCount,
               
               getCuttypeNumber(yearNum,town.TOWN_ID,
3,3As diChanNumber,
               getCuttypeCount(yearNum,town.TOWN_ID,
3,3As diChanCount,
               
               getCuttypeNumber(yearNum,town.TOWN_ID,
3,4As gengXinNumber,
               getCuttypeCount(yearNum,town.TOWN_ID,
3,4As gengXinCount,
               
               getCuttypeNumber(yearNum,town.TOWN_ID,
3,5As qiTaNumber,
               getCuttypeCount(yearNum,town.TOWN_ID,
3,5As qiTaCaiFaCount,
               
               getBambooNumber(yearNum,town.TOWN_ID,
3As bambooNumber,
               getBambooCount(yearNum,town.TOWN_ID,
3As BambooCount,
               
               getShangPingCaiFa(yearNum,town.TOWN_ID,
3As shangPinCaiFaCount,
               getShangpingChuCai(yearNum,town.TOWN_ID,
3As shangPinChuCaiCount,
               getFeiShangPing(yearNum,town.TOWN_ID,
3As  feiShangPinCount,
               
               getFuYuMianJi(yearNum,town.TOWN_ID,
3as fuYuMianJiCount,
               getFuYuChuCaiLiang(yearNum,town.TOWN_ID,
3as 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 Numberreturn outlist as
   rc outlist;
    
Begin
         
open rc 
        
for 
          
select 
                Common_Function.GetCountyCodeById(countyId) 
as addressCode,
                Common_Function.GetCountyNameById(countyId) 
as address,
               
                getTotalNumber(yearNum,countyId,
2As TotalNumber,
               getTotalCount(yearNum,countyId,
2)As TotalCount,
               
               getCuttypeNumber(yearNum,countyId,
2,1As zhuFaNumber,
               getCuttypeCount(yearNum,countyId,
2,1As zhuFaCount,
               
               getCuttypeNumber(yearNum,countyId,
2,2As fuYuNumber,
               getCuttypeCount(yearNum,countyId,
2,2As fuYuCaiFaCount,
               
               getCuttypeNumber(yearNum,countyId,
2,3As diChanNumber,
               getCuttypeCount(yearNum,countyId,
2,3As diChanCount,
               
               getCuttypeNumber(yearNum,countyId,
2,4As gengXinNumber,
               getCuttypeCount(yearNum,countyId,
2,4As gengXinCount,
               
               getCuttypeNumber(yearNum,countyId,
2,5As qiTaNumber,
               getCuttypeCount(yearNum,countyId,
2,5As qiTaCaiFaCount,
               
               getBambooNumber(yearNum,countyId,
2As bambooNumber,
               getBambooCount(yearNum,countyId,
2As BambooCount,
               
               getShangPingCaiFa(yearNum,countyId,
2As shangPinCaiFaCount,
               getShangpingChuCai(yearNum,countyId,
2As shangPinChuCaiCount,
               getFeiShangPing(yearNum,countyId,
2As  feiShangPinCount,
               
               getFuYuMianJi(yearNum,countyId,
2as fuYuMianJiCount,
               getFuYuChuCaiLiang(yearNum,countyId,
2as 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,
3As TotalNumber,
               getTotalCount(yearNum,town.TOWN_ID,
3)As TotalCount,
               
               getCuttypeNumber(yearNum,town.TOWN_ID,
3,1As zhuFaNumber,
               getCuttypeCount(yearNum,town.TOWN_ID,
3,1As zhuFaCount,
               
               getCuttypeNumber(yearNum,town.TOWN_ID,
3,2As fuYuNumber,
               getCuttypeCount(yearNum,town.TOWN_ID,
3,2As fuYuCaiFaCount,
               
               getCuttypeNumber(yearNum,town.TOWN_ID,
3,3As diChanNumber,
               getCuttypeCount(yearNum,town.TOWN_ID,
3,3As diChanCount,
               
               getCuttypeNumber(yearNum,town.TOWN_ID,
3,4As gengXinNumber,
               getCuttypeCount(yearNum,town.TOWN_ID,
3,4As gengXinCount,
               
               getCuttypeNumber(yearNum,town.TOWN_ID,
3,5As qiTaNumber,
               getCuttypeCount(yearNum,town.TOWN_ID,
3,5As qiTaCaiFaCount,
               
               getBambooNumber(yearNum,town.TOWN_ID,
3As bambooNumber,
               getBambooCount(yearNum,town.TOWN_ID,
3As BambooCount,
               
               getShangPingCaiFa(yearNum,town.TOWN_ID,
3As shangPinCaiFaCount,
               getShangpingChuCai(yearNum,town.TOWN_ID,
3As shangPinChuCaiCount,
               getFeiShangPing(yearNum,town.TOWN_ID,
3As  feiShangPinCount,
               
               getFuYuMianJi(yearNum,town.TOWN_ID,
3as fuYuMianJiCount,
               getFuYuChuCaiLiang(yearNum,town.TOWN_ID,
3as 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 Numberreturn 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 Numberreturn 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 Numberreturn 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 Numberreturn 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 Numberreturn Number as
   totalNum 
number;
    
Begin
          
-- province
          if degreeNum = 0    then
            
select   countdistinct (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   countdistinct (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  countdistinct (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  countdistinct (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 Numberreturn 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 Numberreturn 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 Numberreturn 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 Numberreturn 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 Numberreturn 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 Numberreturn 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;
/
posted on 2009-05-21 14:36 王总兵 阅读(93) 评论(0)  编辑  收藏 所属分类: DataBase

只有注册用户登录后才能发表评论。


网站导航: