沉睡森林@漂在北京

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

  BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理 ::
  152 随笔 :: 4 文章 :: 114 评论 :: 0 Trackbacks
drop package CUT_COUNTQUOTA
/

/*==============================================================*/
/* Database package: CUT_COUNTQUOTA                             */
/*==============================================================*/
create or replace package CUT_COUNTQUOTA as
   type OUTLIST 
is REF CURSOR;
   
procedure PROCEXCEL (YEARNUM IN NUMBER,COUNTYID In Number,RESULT Out outlist);
   
function LISTPROVINCEEXCEL (YEARNUM In Number,COUNTYID In Numberreturn outlist;
   
function LISTCITYEXCEL (YEARNUM In Number,COUNTYID In Numberreturn outlist;
   
function LISTCOUNTYEXCEL (YEARNUM In Number,COUNTYID In Numberreturn outlist;
   
function GETXUJIHEJI (YEARNUM In Number,COUNTYID In Numberreturn Number;
   
function GETXUJIHEJIJIEYU (YEARNUM In Number,COUNTYID In Numberreturn Number;
   
function GETCUTTYPENUMBER (YEARNUM In Number,COUNTYID In Number,CUTTYPEID In Numberreturn Number;
   
function GETCUTTYPEJIEYU (YEARNUM In Number,COUNTYID In Number,CUTTYPEID In Numberreturn Number;
   
function GETXIAOHAOJIEGOU (YEARNUM In Number,COUNTYID In Number,TYPEID In numberreturn number;
   
function GETXIAOHAOJIEGOUJIEYU (YEARNUM In Number,COUNTYID In Number,TYPEID In numberreturn number;
   
function GETCHUCAILV (YEARNUM In Number,COUNTYID In Numberreturn Number;
   
function GETRENGONGLIN (YEARNUM In Number,COUNTYID In Number,TYPEID In Numberreturn number;
   
function GETRENGONGLINJIEYU (YEARNUM In Number,COUNTYID In Number,TYPEID In Numberreturn number;
   
function GETTIANRANLIN (YEARNUM In Number,COUNTYID In Number,TYPEID In Numberreturn number;
   
function GETTIANRANLINJIEYU (YEARNUM In Number,COUNTYID In Number,TYPEID In Numberreturn number;
   
function GETMAOZHU (YEARNUM In Number,COUNTYID In Numberreturn Number;
   
function GETMAOZHUJIEYU (YEARNUM In Number,COUNTYID In Numberreturn Number;
end CUT_COUNTQUOTA;
/

create or replace package body CUT_COUNTQUOTA as
   
procedure PROCEXCEL (YEARNUM IN NUMBER,COUNTYID In Number,RESULT Out outlist) as
   
Begin
          
IF Common_Function.GetCountyDegree(CountyId) = 0 THEN
              result :
= listProvinceExcel(yearNum,CountyId);
         
END IF;
         
         
If Common_Function.GetCountyDegree(CountyId) = 1 Then
              result :
= listCityExcel(yearNum,CountyId);
         
End If;
         
         
If Common_Function.GetCountyDegree(CountyId) = 2 Then
              result :
= listCountyExcel(yearNum,CountyId);
         
End If;
    
End;
   
function LISTPROVINCEEXCEL (YEARNUM In Number,COUNTYID In Numberreturn outlist as
   rc outlist;
    
Begin
          
open rc for
           
         
select 
                  Common_Function.GetCountyCodeById(CountyId) 
As countyCode,
                
                  Common_Function.GetCountyNameById(CountyId) 
As countyName,
                
                Common_Function.GetCountyNameById( Common_Function.GetCityIdByCountyId(CountyId) )
As cityName,
                
                getXuJiHeJi(yearNum,CountyId) 
As XuJiHeJi,
                getXuJiHeJiJieYu(yearNum,CountyId)
As XuJiHeJiJieYu,
                
                getCuttypeNumber(yearNum,CountyId,
1As ZhuFa,
                getCuttypeJieYu(yearNum,CountyId,
1As ZhuFaJieYu,
                
                getCuttypeNumber(yearNum,CountyId,
2As FuYu,
                getCuttypeJieYu(yearNum,CountyId,
2As FuYuJieYu,
                
                getCuttypeNumber(yearNum,CountyId,
3As GengXin,
                getCuttypeJieYu(yearNum,CountyId,
3As GengXinJieYu,
                
                getCuttypeNumber(yearNum,CountyId,
4As DiChan,
                getCuttypeJieYu(yearNum,CountyId,
4As DiChanJieYu,
                
                getCuttypeNumber(yearNum,CountyId,
5As QiTa,
                getCuttypeJieYu(yearNum,CountyId,
5As QiTaJieYu,
                
                getXiaoHaoJieGou(yearNum,CountyId,
1as ShangPingCaiFaLiang,
                getXiaoHaoJieGouJieYu(yearNum,CountyId,
1as ShangPingCaiFaLiangJieYu,
                
                
                getXiaoHaoJieGou(yearNum,CountyId,
2as ShangPingChuCaiLiang,
                getXiaoHaoJieGouJieYu(yearNum,CountyId,
2as ShangPingChuCaiLiangJieYu,
                
                getChuCaiLv(yearNum,CountyId) 
as ChuCaiLv,
                
                getXiaoHaoJieGou(yearNum,CountyId,
3as FeiShangPingCai,    
                getXiaoHaoJieGouJieYu(yearNum,CountyId,
3as FeiShangPingCaiJieYu,
                
                
                
                getRenGongLin(yearNum,CountyId,
1as RenGongLinZongLiang,
                getRenGongLinJieYu(yearNum,CountyId,
1As RenGongLinZongLiangJieYu,
           
                getRenGongLin(yearNum,CountyId,
2as RenGongLinSPCaiFaLiang,
                getRenGongLinJieYu(yearNum,CountyId,
2As RenGongLinSPCaiFaLiangJieYu,
                
                getRenGongLin(yearNum,CountyId,
3as RenGongLinSPChuCaiLiang,
                getRenGongLinJieYu(yearNum,CountyId,
3As RenGongLinSPChuCaiLiangJieYu,     
                
                 
                  getRenGongLin(yearNum,CountyId,
4as RenGongLinGYCaiFaLiang,
                getRenGongLinJieYu(yearNum,CountyId,
4As RenGongLinGYCaiFaLiangJieYu,
                
                
                getRenGongLin(yearNum,CountyId,
5as RenGongLinGYChuCaiLiang,
                getRenGongLinJieYu(yearNum,CountyId,
5As RenGongLinGYChuCaiLiangJieYu,
                
                getTianRanLin(yearNum,CountyId,
1)as TianRanLinZongLiang,
                getTianRanLinJieYu(yearNum,CountyId,
1)as TianRanLinZongLiangJieYu,
                
                getTianRanLin(yearNum,CountyId,
2)as TianRanLinSPCaiFaLiang,
                getTianRanLinJieYu(yearNum,CountyId,
2)as TianRanLinSPCaiFaLiangJieYu,
                
                getTianRanLin(yearNum,CountyId,
3)as TianRanLinSPChuCaiLiang,
                getTianRanLinJieYu(yearNum,CountyId,
3)as TianRanLinSPChuCaiLiangJieYu,
                
                getMaoZhu(yearNum,CountyId) 
as MaoZhu,
                getMaoZhuJieYu(yearNum,CountyId)
as MaoZhuJieYu 
                
         
from dual
         
union
         
         
select 
                  Common_Function.GetCountyCodeById(city.COUN_ID) 
As countyCode,
                
                  Common_Function.GetCountyNameById(city.COUN_ID) 
As countyName,
                
                Common_Function.GetCountyNameById( Common_Function.GetCityIdByCountyId(city.COUN_ID) )
As cityName,
                
                getXuJiHeJi(yearNum,city.COUN_ID) 
As XuJiHeJi,
                getXuJiHeJiJieYu(yearNum,city.COUN_ID)
As XuJiHeJiJieYu,
                
                getCuttypeNumber(yearNum,city.COUN_ID,
1As ZhuFa,
                getCuttypeJieYu(yearNum,city.COUN_ID,
1As ZhuFaJieYu,
                
                getCuttypeNumber(yearNum,city.COUN_ID,
2As FuYu,
                getCuttypeJieYu(yearNum,city.COUN_ID,
2As FuYuJieYu,
                
                getCuttypeNumber(yearNum,city.COUN_ID,
3As GengXin,
                getCuttypeJieYu(yearNum,city.COUN_ID,
3As GengXinJieYu,
                
                getCuttypeNumber(yearNum,city.COUN_ID,
4As DiChan,
                getCuttypeJieYu(yearNum,city.COUN_ID,
4As DiChanJieYu,
                
                getCuttypeNumber(yearNum,city.COUN_ID,
5As QiTa,
                getCuttypeJieYu(yearNum,city.COUN_ID,
5As QiTaJieYu,
                
                getXiaoHaoJieGou(yearNum,city.COUN_ID,
1as ShangPingCaiFaLiang,
                getXiaoHaoJieGouJieYu(yearNum,city.COUN_ID,
1as ShangPingCaiFaLiangJieYu,
                
                
                getXiaoHaoJieGou(yearNum,city.COUN_ID,
2as ShangPingChuCaiLiang,
                getXiaoHaoJieGouJieYu(yearNum,city.COUN_ID,
2as ShangPingChuCaiLiangJieYu,
                
                getChuCaiLv(yearNum,city.COUN_ID) 
as ChuCaiLv,
                
                getXiaoHaoJieGou(yearNum,city.COUN_ID,
3as FeiShangPingCai,    
                getXiaoHaoJieGouJieYu(yearNum,city.COUN_ID,
3as FeiShangPingCaiJieYu,
                
                
                
                getRenGongLin(yearNum,city.COUN_ID,
1as RenGongLinZongLiang,
                getRenGongLinJieYu(yearNum,city.COUN_ID,
1As RenGongLinZongLiangJieYu,
           
                getRenGongLin(yearNum,city.COUN_ID,
2as RenGongLinSPCaiFaLiang,
                getRenGongLinJieYu(yearNum,city.COUN_ID,
2As RenGongLinSPCaiFaLiangJieYu,
                
                getRenGongLin(yearNum,city.COUN_ID,
3as RenGongLinSPChuCaiLiang,
                getRenGongLinJieYu(yearNum,city.COUN_ID,
3As RenGongLinSPChuCaiLiangJieYu,     
                
                 
                  getRenGongLin(yearNum,city.COUN_ID,
4as RenGongLinGYCaiFaLiang,
                getRenGongLinJieYu(yearNum,city.COUN_ID,
4As RenGongLinGYCaiFaLiangJieYu,
                
                
                getRenGongLin(yearNum,city.COUN_ID,
5as RenGongLinGYChuCaiLiang,
                getRenGongLinJieYu(yearNum,city.COUN_ID,
5As RenGongLinGYChuCaiLiangJieYu,
                
                getTianRanLin(yearNum,city.COUN_ID,
1)as TianRanLinZongLiang,
                getTianRanLinJieYu(yearNum,city.COUN_ID,
1)as TianRanLinZongLiangJieYu,
                
                getTianRanLin(yearNum,city.COUN_ID,
2)as TianRanLinSPCaiFaLiang,
                getTianRanLinJieYu(yearNum,city.COUN_ID,
2)as TianRanLinSPCaiFaLiangJieYu,
                
                getTianRanLin(yearNum,city.COUN_ID,
3)as TianRanLinSPChuCaiLiang,
                getTianRanLinJieYu(yearNum,city.COUN_ID,
3)as TianRanLinSPChuCaiLiangJieYu,
                
                getMaoZhu(yearNum,city.COUN_ID) 
as MaoZhu,
                getMaoZhuJieYu(yearNum,city.COUN_ID)
as MaoZhuJieYu 
                
         
from dual,GG_COUNTY city
         
where city.COUN_SJID = countyId
         
and (  getXuJiHeJi(yearNum,city.COUN_ID) > 0 or  getMaoZhu(yearNum,city.COUN_ID)  > 0 )
         
         
union 
               
select 
                  Common_Function.GetCountyCodeById(county.COUN_ID) 
As countyCode,
                
                  Common_Function.GetCountyNameById(county.COUN_ID) 
As countyName,
                
                Common_Function.GetCountyNameById( Common_Function.GetCityIdByCountyId(county.COUN_ID) )
As cityName,
                
                getXuJiHeJi(yearNum,county.COUN_ID) 
As XuJiHeJi,
                getXuJiHeJiJieYu(yearNum,county.COUN_ID)
As XuJiHeJiJieYu,
                
                getCuttypeNumber(yearNum,county.COUN_ID,
1As ZhuFa,
                getCuttypeJieYu(yearNum,county.COUN_ID,
1As ZhuFaJieYu,
                
                getCuttypeNumber(yearNum,county.COUN_ID,
2As FuYu,
                getCuttypeJieYu(yearNum,county.COUN_ID,
2As FuYuJieYu,
                
                getCuttypeNumber(yearNum,county.COUN_ID,
3As GengXin,
                getCuttypeJieYu(yearNum,county.COUN_ID,
3As GengXinJieYu,
                
                getCuttypeNumber(yearNum,county.COUN_ID,
4As DiChan,
                getCuttypeJieYu(yearNum,county.COUN_ID,
4As DiChanJieYu,
                
                getCuttypeNumber(yearNum,county.COUN_ID,
5As QiTa,
                getCuttypeJieYu(yearNum,county.COUN_ID,
5As QiTaJieYu,
                
                getXiaoHaoJieGou(yearNum,county.COUN_ID,
1as ShangPingCaiFaLiang,
                getXiaoHaoJieGouJieYu(yearNum,county.COUN_ID,
1as ShangPingCaiFaLiangJieYu,
                
                
                getXiaoHaoJieGou(yearNum,county.COUN_ID,
2as ShangPingChuCaiLiang,
                getXiaoHaoJieGouJieYu(yearNum,county.COUN_ID,
2as ShangPingChuCaiLiangJieYu,
                
                getChuCaiLv(yearNum,county.COUN_ID) 
as ChuCaiLv,
                
                getXiaoHaoJieGou(yearNum,county.COUN_ID,
3as FeiShangPingCai,    
                getXiaoHaoJieGouJieYu(yearNum,county.COUN_ID,
3as FeiShangPingCaiJieYu,
                
                
                
                getRenGongLin(yearNum,county.COUN_ID,
1as RenGongLinZongLiang,
                getRenGongLinJieYu(yearNum,CountyId,
1As RenGongLinZongLiangJieYu,
           
                getRenGongLin(yearNum,county.COUN_ID,
2as RenGongLinSPCaiFaLiang,
                getRenGongLinJieYu(yearNum,county.COUN_ID,
2As RenGongLinSPCaiFaLiangJieYu,
                
                getRenGongLin(yearNum,county.COUN_ID,
3as RenGongLinSPChuCaiLiang,
                getRenGongLinJieYu(yearNum,county.COUN_ID,
3As RenGongLinSPChuCaiLiangJieYu,     
                
                 
                  getRenGongLin(yearNum,county.COUN_ID,
4as RenGongLinGYCaiFaLiang,
                getRenGongLinJieYu(yearNum,county.COUN_ID,
4As RenGongLinGYCaiFaLiangJieYu,
                
                
                getRenGongLin(yearNum,county.COUN_ID,
5as RenGongLinGYChuCaiLiang,
                getRenGongLinJieYu(yearNum,county.COUN_ID,
5As RenGongLinGYChuCaiLiangJieYu,
                
                getTianRanLin(yearNum,county.COUN_ID,
1)as TianRanLinZongLiang,
                getTianRanLinJieYu(yearNum,county.COUN_ID,
1)as TianRanLinZongLiangJieYu,
                
                getTianRanLin(yearNum,county.COUN_ID,
2)as TianRanLinSPCaiFaLiang,
                getTianRanLinJieYu(yearNum,county.COUN_ID,
2)as TianRanLinSPCaiFaLiangJieYu,
                
                getTianRanLin(yearNum,county.COUN_ID,
3)as TianRanLinSPChuCaiLiang,
                getTianRanLinJieYu(yearNum,county.COUN_ID,
3)as TianRanLinSPChuCaiLiangJieYu,
                
                getMaoZhu(yearNum,county.COUN_ID) 
as MaoZhu,
                getMaoZhuJieYu(yearNum,county.COUN_ID)
as MaoZhuJieYu 
                
         
from dual,GG_County county,GG_County ct
         
where county.COUN_SJID = ct.COUN_ID
         
and ct.COUN_SJID = countyId
         
and  ( getXuJiHeJi(yearNum,county.COUN_ID) > 0 or  getMaoZhu(yearNum,county.COUN_ID) > 0 )
         
         
order by 1;
    
          
return rc;
    
End;
   
function LISTCITYEXCEL (YEARNUM In Number,COUNTYID In Numberreturn outlist as
   rc outlist;
    
Begin
          
open rc for
         
select 
                  Common_Function.GetCountyCodeById(CountyId) 
As countyCode,
                
                  Common_Function.GetCountyNameById(CountyId) 
As countyName,
                
                Common_Function.GetCountyNameById( Common_Function.GetCityIdByCountyId(CountyId) )
As cityName,
                
                getXuJiHeJi(yearNum,CountyId) 
As XuJiHeJi,
                getXuJiHeJiJieYu(yearNum,CountyId)
As XuJiHeJiJieYu,
                
                getCuttypeNumber(yearNum,CountyId,
1As ZhuFa,
                getCuttypeJieYu(yearNum,CountyId,
1As ZhuFaJieYu,
                
                getCuttypeNumber(yearNum,CountyId,
2As FuYu,
                getCuttypeJieYu(yearNum,CountyId,
2As FuYuJieYu,
                
                getCuttypeNumber(yearNum,CountyId,
3As GengXin,
                getCuttypeJieYu(yearNum,CountyId,
3As GengXinJieYu,
                
                getCuttypeNumber(yearNum,CountyId,
4As DiChan,
                getCuttypeJieYu(yearNum,CountyId,
4As DiChanJieYu,
                
                getCuttypeNumber(yearNum,CountyId,
5As QiTa,
                getCuttypeJieYu(yearNum,CountyId,
5As QiTaJieYu,
                
                getXiaoHaoJieGou(yearNum,CountyId,
1as ShangPingCaiFaLiang,
                getXiaoHaoJieGouJieYu(yearNum,CountyId,
1as ShangPingCaiFaLiangJieYu,
                
                
                getXiaoHaoJieGou(yearNum,CountyId,
2as ShangPingChuCaiLiang,
                getXiaoHaoJieGouJieYu(yearNum,CountyId,
2as ShangPingChuCaiLiangJieYu,
                
                getChuCaiLv(yearNum,CountyId) 
as ChuCaiLv,
                
                getXiaoHaoJieGou(yearNum,CountyId,
3as FeiShangPingCai,    
                getXiaoHaoJieGouJieYu(yearNum,CountyId,
3as FeiShangPingCaiJieYu,
                
                
                
                getRenGongLin(yearNum,CountyId,
1as RenGongLinZongLiang,
                getRenGongLinJieYu(yearNum,CountyId,
1As RenGongLinZongLiangJieYu,
           
                getRenGongLin(yearNum,CountyId,
2as RenGongLinSPCaiFaLiang,
                getRenGongLinJieYu(yearNum,CountyId,
2As RenGongLinSPCaiFaLiangJieYu,
                
                getRenGongLin(yearNum,CountyId,
3as RenGongLinSPChuCaiLiang,
                getRenGongLinJieYu(yearNum,CountyId,
3As RenGongLinSPChuCaiLiangJieYu,     
                
                 
                  getRenGongLin(yearNum,CountyId,
4as RenGongLinGYCaiFaLiang,
                getRenGongLinJieYu(yearNum,CountyId,
4As RenGongLinGYCaiFaLiangJieYu,
                
                
                getRenGongLin(yearNum,CountyId,
5as RenGongLinGYChuCaiLiang,
                getRenGongLinJieYu(yearNum,CountyId,
5As RenGongLinGYChuCaiLiangJieYu,
                
                getTianRanLin(yearNum,CountyId,
1)as TianRanLinZongLiang,
                getTianRanLinJieYu(yearNum,CountyId,
1)as TianRanLinZongLiangJieYu,
                
                getTianRanLin(yearNum,CountyId,
2)as TianRanLinSPCaiFaLiang,
                getTianRanLinJieYu(yearNum,CountyId,
2)as TianRanLinSPCaiFaLiangJieYu,
                
                getTianRanLin(yearNum,CountyId,
3)as TianRanLinSPChuCaiLiang,
                getTianRanLinJieYu(yearNum,CountyId,
3)as TianRanLinSPChuCaiLiangJieYu,
                
                getMaoZhu(yearNum,CountyId) 
as MaoZhu,
                getMaoZhuJieYu(yearNum,CountyId)
as MaoZhuJieYu 
                
         
from dual
         
         
union 
               
select 
                  Common_Function.GetCountyCodeById(county.COUN_ID) 
As countyCode,
                
                  Common_Function.GetCountyNameById(county.COUN_ID) 
As countyName,
                
                Common_Function.GetCountyNameById( Common_Function.GetCityIdByCountyId(county.COUN_ID) )
As cityName,
                
                getXuJiHeJi(yearNum,county.COUN_ID) 
As XuJiHeJi,
                getXuJiHeJiJieYu(yearNum,county.COUN_ID)
As XuJiHeJiJieYu,
                
                getCuttypeNumber(yearNum,county.COUN_ID,
1As ZhuFa,
                getCuttypeJieYu(yearNum,county.COUN_ID,
1As ZhuFaJieYu,
                
                getCuttypeNumber(yearNum,county.COUN_ID,
2As FuYu,
                getCuttypeJieYu(yearNum,county.COUN_ID,
2As FuYuJieYu,
                
                getCuttypeNumber(yearNum,county.COUN_ID,
3As GengXin,
                getCuttypeJieYu(yearNum,county.COUN_ID,
3As GengXinJieYu,
                
                getCuttypeNumber(yearNum,county.COUN_ID,
4As DiChan,
                getCuttypeJieYu(yearNum,county.COUN_ID,
4As DiChanJieYu,
                
                getCuttypeNumber(yearNum,county.COUN_ID,
5As QiTa,
                getCuttypeJieYu(yearNum,county.COUN_ID,
5As QiTaJieYu,
                
                getXiaoHaoJieGou(yearNum,county.COUN_ID,
1as ShangPingCaiFaLiang,
                getXiaoHaoJieGouJieYu(yearNum,county.COUN_ID,
1as ShangPingCaiFaLiangJieYu,
                
                
                getXiaoHaoJieGou(yearNum,county.COUN_ID,
2as ShangPingChuCaiLiang,
                getXiaoHaoJieGouJieYu(yearNum,county.COUN_ID,
2as ShangPingChuCaiLiangJieYu,
                
                getChuCaiLv(yearNum,county.COUN_ID) 
as ChuCaiLv,
                
                getXiaoHaoJieGou(yearNum,county.COUN_ID,
3as FeiShangPingCai,    
                getXiaoHaoJieGouJieYu(yearNum,county.COUN_ID,
3as FeiShangPingCaiJieYu,
                
                
                
                getRenGongLin(yearNum,county.COUN_ID,
1as RenGongLinZongLiang,
                getRenGongLinJieYu(yearNum,CountyId,
1As RenGongLinZongLiangJieYu,
           
                getRenGongLin(yearNum,county.COUN_ID,
2as RenGongLinSPCaiFaLiang,
                getRenGongLinJieYu(yearNum,county.COUN_ID,
2As RenGongLinSPCaiFaLiangJieYu,
                
                getRenGongLin(yearNum,county.COUN_ID,
3as RenGongLinSPChuCaiLiang,
                getRenGongLinJieYu(yearNum,county.COUN_ID,
3As RenGongLinSPChuCaiLiangJieYu,     
                
                 
                  getRenGongLin(yearNum,county.COUN_ID,
4as RenGongLinGYCaiFaLiang,
                getRenGongLinJieYu(yearNum,county.COUN_ID,
4As RenGongLinGYCaiFaLiangJieYu,
                
                
                getRenGongLin(yearNum,county.COUN_ID,
5as RenGongLinGYChuCaiLiang,
                getRenGongLinJieYu(yearNum,county.COUN_ID,
5As RenGongLinGYChuCaiLiangJieYu,
                
                getTianRanLin(yearNum,county.COUN_ID,
1)as TianRanLinZongLiang,
                getTianRanLinJieYu(yearNum,county.COUN_ID,
1)as TianRanLinZongLiangJieYu,
                
                getTianRanLin(yearNum,county.COUN_ID,
2)as TianRanLinSPCaiFaLiang,
                getTianRanLinJieYu(yearNum,county.COUN_ID,
2)as TianRanLinSPCaiFaLiangJieYu,
                
                getTianRanLin(yearNum,county.COUN_ID,
3)as TianRanLinSPChuCaiLiang,
                getTianRanLinJieYu(yearNum,county.COUN_ID,
3)as TianRanLinSPChuCaiLiangJieYu,
                
                getMaoZhu(yearNum,county.COUN_ID) 
as MaoZhu,
                getMaoZhuJieYu(yearNum,county.COUN_ID)
as MaoZhuJieYu 
                
         
from dual,GG_County county
         
where county.COUN_SJID = countyId
         
         
         
order by 1;
         
          
return rc;
    
End;
   
function LISTCOUNTYEXCEL (YEARNUM In Number,COUNTYID In Numberreturn outlist as
   rc outlist;
    
Begin
          
open rc for
         
select 
                  Common_Function.GetCountyCodeById(CountyId) 
As countyCode,
                
                  Common_Function.GetCountyNameById(CountyId) 
As countyName,
                
                Common_Function.GetCountyNameById( Common_Function.GetCityIdByCountyId(CountyId) )
As cityName,
                
                getXuJiHeJi(yearNum,CountyId) 
As XuJiHeJi,
                getXuJiHeJiJieYu(yearNum,CountyId)
As XuJiHeJiJieYu,
                
                getCuttypeNumber(yearNum,CountyId,
1As ZhuFa,
                getCuttypeJieYu(yearNum,CountyId,
1As ZhuFaJieYu,
                
                getCuttypeNumber(yearNum,CountyId,
2As FuYu,
                getCuttypeJieYu(yearNum,CountyId,
2As FuYuJieYu,
                
                getCuttypeNumber(yearNum,CountyId,
3As GengXin,
                getCuttypeJieYu(yearNum,CountyId,
3As GengXinJieYu,
                
                getCuttypeNumber(yearNum,CountyId,
4As DiChan,
                getCuttypeJieYu(yearNum,CountyId,
4As DiChanJieYu,
                
                getCuttypeNumber(yearNum,CountyId,
5As QiTa,
                getCuttypeJieYu(yearNum,CountyId,
5As QiTaJieYu,
                
                getXiaoHaoJieGou(yearNum,CountyId,
1as ShangPingCaiFaLiang,
                getXiaoHaoJieGouJieYu(yearNum,CountyId,
1as ShangPingCaiFaLiangJieYu,
                
                
                getXiaoHaoJieGou(yearNum,CountyId,
2as ShangPingChuCaiLiang,
                getXiaoHaoJieGouJieYu(yearNum,CountyId,
2as ShangPingChuCaiLiangJieYu,
                
                getChuCaiLv(yearNum,CountyId) 
as ChuCaiLv,
                
                getXiaoHaoJieGou(yearNum,CountyId,
3as FeiShangPingCai,    
                getXiaoHaoJieGouJieYu(yearNum,CountyId,
3as FeiShangPingCaiJieYu,
                
                
                
                getRenGongLin(yearNum,CountyId,
1as RenGongLinZongLiang,
                getRenGongLinJieYu(yearNum,CountyId,
1As RenGongLinZongLiangJieYu,
           
                getRenGongLin(yearNum,CountyId,
2as RenGongLinSPCaiFaLiang,
                getRenGongLinJieYu(yearNum,CountyId,
2As RenGongLinSPCaiFaLiangJieYu,
                
                getRenGongLin(yearNum,CountyId,
3as RenGongLinSPChuCaiLiang,
                getRenGongLinJieYu(yearNum,CountyId,
3As RenGongLinSPChuCaiLiangJieYu,     
                
                 
                  getRenGongLin(yearNum,CountyId,
4as RenGongLinGYCaiFaLiang,
                getRenGongLinJieYu(yearNum,CountyId,
4As RenGongLinGYCaiFaLiangJieYu,
                
                
                getRenGongLin(yearNum,CountyId,
5as RenGongLinGYChuCaiLiang,
                getRenGongLinJieYu(yearNum,CountyId,
5As RenGongLinGYChuCaiLiangJieYu,
                
                getTianRanLin(yearNum,CountyId,
1)as TianRanLinZongLiang,
                getTianRanLinJieYu(yearNum,CountyId,
1)as TianRanLinZongLiangJieYu,
                
                getTianRanLin(yearNum,CountyId,
2)as TianRanLinSPCaiFaLiang,
                getTianRanLinJieYu(yearNum,CountyId,
2)as TianRanLinSPCaiFaLiangJieYu,
                
                getTianRanLin(yearNum,CountyId,
3)as TianRanLinSPChuCaiLiang,
                getTianRanLinJieYu(yearNum,CountyId,
3)as TianRanLinSPChuCaiLiangJieYu,
                
                getMaoZhu(yearNum,CountyId) 
as MaoZhu,
                getMaoZhuJieYu(yearNum,CountyId)
as MaoZhuJieYu 
                
         
from dual;
         
         
         
return rc;
    
End;
   
function GETXUJIHEJI (YEARNUM In Number,COUNTYID In Numberreturn Number as
   lastNum 
number;
    
Begin   
        
select  q.QUOT_XJHJ into lastNum  from CF_QUOTA q   where q.QUOT_BXND = yearNum and q.QUOT_COUNXJID = CountyId;  
       
return lastNum; 
    
End;
   
function GETXUJIHEJIJIEYU (YEARNUM In Number,COUNTYID In Numberreturn Number as
   lastNum 
number;
    
Begin
          
if common_function.GetCountyDegree(countyId) = 2 then
            
select  sum(cuttree.TRSS_XJHJ) 
           
into lastNum  
           
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
           
where cutreply.APAP_COUNID = CountyId
           
and cutreply.APAP_BXND = yearNum
           
and cutreply.APAP_ID = cuttree.TRSS_APAPID
           
and cutreply.APAP_ZT <> 0 
           
and cutreply.APAP_ZT <> 2
           
and cutreply.APAP_ZT <> 4;
         
end if;
       
        
if common_function.GetCountyDegree(countyId) = 1 then
            
select  sum(cuttree.TRSS_XJHJ) 
           
into lastNum  
           
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
           
where Common_Function.GetCityIdByCountyId( cutreply.APAP_COUNID) = CountyId
           
and cutreply.APAP_BXND = yearNum
           
and cutreply.APAP_ID = cuttree.TRSS_APAPID
           
and cutreply.APAP_ZT <> 0 
           
and cutreply.APAP_ZT <> 2
           
and cutreply.APAP_ZT <> 4;
         
end if;
         
         
if common_function.GetCountyDegree(countyId) = 0 then
            
select  sum(cuttree.TRSS_XJHJ) 
           
into lastNum  
           
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
           
where  cutreply.APAP_BXND = yearNum
           
and cutreply.APAP_ID = cuttree.TRSS_APAPID
           
and cutreply.APAP_ZT <> 0 
           
and cutreply.APAP_ZT <> 2
           
and cutreply.APAP_ZT <> 4;
         
end if;
         
           
         
if lastNum is null then
          lastNum :
= 0;
         
end if;
       
         lastNum :
= getXuJiHeJi(yearNum,CountyId) - lastNum/10000;
         
return lastNum;
    
End;
   
function GETCUTTYPENUMBER (YEARNUM In Number,COUNTYID In Number,CUTTYPEID In Numberreturn Number as
   lastNum 
Number;
    
Begin
        
If cuttypeId = 1 then
          
select q.QUOT_ZF into lastNum from CF_QUOTA q where q.QUOT_BXND = yearNum and q.QUOT_COUNXJID = countyId;
       
End if;
       
       
If cuttypeId = 2 then
          
select q.QUOT_HYCF into lastNum from CF_QUOTA q where q.QUOT_BXND = yearNum and q.QUOT_COUNXJID = countyId;
       
End if;
       
       
       
If cuttypeId = 3 then
          
select q.QUOT_GXCF into lastNum from CF_QUOTA q where q.QUOT_BXND = yearNum and q.QUOT_COUNXJID = countyId;
       
End if;
       
       
If cuttypeId = 4 then
          
select q.QUOT_DCGZ into lastNum from CF_QUOTA q where q.QUOT_BXND = yearNum and q.QUOT_COUNXJID = countyId;
       
End if;
       
       
If cuttypeId = 5 then
          
select q.QUOT_QTCF into lastNum from CF_QUOTA q where q.QUOT_BXND = yearNum and q.QUOT_COUNXJID = countyId;
       
End if;
       
        
return lastNum;
    
End;
   
function GETCUTTYPEJIEYU (YEARNUM In Number,COUNTYID In Number,CUTTYPEID In Numberreturn Number as
   lastNum 
Number;
    
Begin
          
if Common_Function.GetCountyDegree(CountyId) = 2 then
              
select  sum(cuttree.TRSS_XJHJ) 
           
into lastNum  
           
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
           
where cutreply.APAP_COUNID = CountyId
           
and cutreply.APAP_BXND = yearNum
           
and cutreply.APAP_ID = cuttree.TRSS_APAPID
           
and cutreply.APAP_ZT <> 0 
           
and cutreply.APAP_ZT <> 2
           
and cutreply.APAP_ZT <> 4
           
and Common_Function.GetCuttypeCodeById(cutreply.APRE_CLASID) = CuttypeId;
         
end if;
    
          
if Common_Function.GetCountyDegree(CountyId) = 1 then
              
select  sum(cuttree.TRSS_XJHJ) 
           
into lastNum  
           
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
           
where Common_Function.GetCityIdByCountyId( cutreply.APAP_COUNID )= CountyId
           
and  cutreply.APAP_BXND = yearNum
           
and cutreply.APAP_ID = cuttree.TRSS_APAPID
           
and cutreply.APAP_ZT <> 0 
           
and cutreply.APAP_ZT <> 2
           
and cutreply.APAP_ZT <> 4
           
and Common_Function.GetCuttypeCodeById(cutreply.APRE_CLASID) = CuttypeId;
         
end if;
         
          
if Common_Function.GetCountyDegree(CountyId) = 0 then
              
select  sum(cuttree.TRSS_XJHJ) 
           
into lastNum  
           
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
           
where  cutreply.APAP_BXND = yearNum
           
and cutreply.APAP_ID = cuttree.TRSS_APAPID
           
and cutreply.APAP_ZT <> 0 
           
and cutreply.APAP_ZT <> 2
           
and cutreply.APAP_ZT <> 4
           
and Common_Function.GetCuttypeCodeById(cutreply.APRE_CLASID) = CuttypeId;
         
end if;
       
       
if lastNum is null then
          lastNum :
= 0;
       
end if;
       
       lastNum :
= getCuttypeNumber(yearNum,CountyId,CuttypeId) - lastNum / 10000;
       
        
return lastNum;
    
End;
   
function GETXIAOHAOJIEGOU (YEARNUM In Number,COUNTYID In Number,TYPEID In numberreturn number as
   lastNum 
number;
    
Begin
          
If TypeId = 1 then
              
select q.QUOT_SCFL into lastNum from CF_QUOTA q where q.QUOT_BXND = yearNum and q.QUOT_COUNXJID = countyId;
         
End if;
         
         
If TypeId = 2 then
              
select  q.QUOT_SCCL  into lastNum from CF_QUOTA q where q.QUOT_BXND = yearNum and q.QUOT_COUNXJID = countyId;
         
End if;
         
         
If TypeId = 3 then
              
select q.QUOT_FSPC into lastNum from CF_QUOTA q where q.QUOT_BXND = yearNum and q.QUOT_COUNXJID = countyId;
         
End if;
         
         
          
return lastnum;
    
End;
   
function GETXIAOHAOJIEGOUJIEYU (YEARNUM In Number,COUNTYID In Number,TYPEID In numberreturn number as
   lastNum 
number;
    
Begin
        
If TypeId = 1 then
           
if Common_Function.GetCountyDegree(countyId) = 0 then
             
select  sum(cuttree.TRSS_SCFL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where  cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4 ;
           
end if;
        
           
if Common_Function.GetCountyDegree(countyId) = 1 then
             
select  sum(cuttree.TRSS_SCFL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where Common_Function.GetCityIdByCountyId( cutreply.APAP_COUNID) = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4 ;
           
end if
        
            
if Common_Function.GetCountyDegree(countyId) = 2 then
             
select  sum(cuttree.TRSS_SCFL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where   cutreply.APAP_COUNID  = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4 ;
           
end if;      
        
       
End if;
       
         
If TypeId = 2 then
           
if Common_Function.GetCountyDegree(countyId) = 0 then
             
select  sum(cuttree.TRSS_SCCL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where  cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4 ;
           
end if;
        
           
if Common_Function.GetCountyDegree(countyId) = 1 then
             
select  sum(cuttree.TRSS_SCCL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where Common_Function.GetCityIdByCountyId( cutreply.APAP_COUNID) = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4 ;
           
end if
        
            
if Common_Function.GetCountyDegree(countyId) = 2 then
             
select  sum(cuttree.TRSS_SCCL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where   cutreply.APAP_COUNID  = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4 ;
           
end if;    
        
       
End if;
    
         
If TypeId = 3 then
                   
if Common_Function.GetCountyDegree(countyId) = 0 then
             
select  sum(cuttree.TRSS_FSPC) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where  cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4 ;
           
end if;
        
           
if Common_Function.GetCountyDegree(countyId) = 1 then
             
select  sum(cuttree.TRSS_FSPC) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where Common_Function.GetCityIdByCountyId( cutreply.APAP_COUNID) = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4 ;
           
end if
        
            
if Common_Function.GetCountyDegree(countyId) = 2 then
             
select  sum(cuttree.TRSS_FSPC) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where   cutreply.APAP_COUNID  = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4 ;
           
end if;    
    
       
End if;
       
       
if lastNum is null then
          lastNum :
= 0;
       
end if;
          
       lastNum :
= getXiaoHaoJieGou(yearNum,CountyId,typeId) - lastNum / 10000;
       
       
return lastnum;
    
End;
   
function GETCHUCAILV (YEARNUM In Number,COUNTYID In Numberreturn Number as
   lastNum 
number;
    
Begin
         
select q.QUOT_SCCN into lastNum from CF_QUOTA q where q.QUOT_BXND = yearNum and q.QUOT_COUNXJID = countyId; 
         
return lastNum;
    
End;
   
function GETRENGONGLIN (YEARNUM In Number,COUNTYID In Number,TYPEID In Numberreturn number as
   lastNum 
number;
    
Begin
         
        
if TypeId = 1 Then
            
select q.QUOT_RLZL into lastNum from CF_QUOTA q where q.QUOT_BXND = yearNum and q.QUOT_COUNXJID = countyId; 
        
End if;
        
        
if TypeId = 2 Then
            
select q.QUOT_RSCL into lastNum from CF_QUOTA q where q.QUOT_BXND = yearNum and q.QUOT_COUNXJID = countyId; 
        
End if;
        
        
if typeId = 3 then
            
select q.QUOT_RSCU into lastNum from CF_QUOTA q where q.QUOT_BXND = yearNum and q.QUOT_COUNXJID = countyId; 
        
end if;
        
        
if typeId = 4 then
            
select q.QUOT_GCFL into lastNum from CF_QUOTA q where q.QUOT_BXND = yearNum and q.QUOT_COUNXJID = countyId;
        
end if;
        
             
        
if typeId = 5 then
            
select q.QUOT_GCCL into lastNum from CF_QUOTA q where q.QUOT_BXND = yearNum and q.QUOT_COUNXJID = countyId;
        
end if;
        
         
return lastNum;
    
End;
   
function GETRENGONGLINJIEYU (YEARNUM In Number,COUNTYID In Number,TYPEID In Numberreturn number as
   lastNum 
number;
    
Begin
         
if TypeId = 1 Then
           
if Common_Function.GetCountyDegree(countyId) = 0 then
             
select  sum(cuttree.TRSS_XJHJ) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where  cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 2;
           
end if;
        
           
if Common_Function.GetCountyDegree(countyId) = 1 then
             
select  sum(cuttree.TRSS_XJHJ) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where Common_Function.GetCityIdByCountyId( cutreply.APAP_COUNID) = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 2;
           
end if
        
            
if Common_Function.GetCountyDegree(countyId) = 2 then
             
select  sum(cuttree.TRSS_XJHJ) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where   cutreply.APAP_COUNID  = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 2;
           
end if
        
        
End if;
        
        
if TypeId = 2 Then
                   
if Common_Function.GetCountyDegree(countyId) = 0 then
             
select  sum(cuttree.TRSS_SCFL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where  cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 2;
           
end if;
        
           
if Common_Function.GetCountyDegree(countyId) = 1 then
             
select  sum(cuttree.TRSS_SCFL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where Common_Function.GetCityIdByCountyId( cutreply.APAP_COUNID) = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 2;
           
end if
        
            
if Common_Function.GetCountyDegree(countyId) = 2 then
             
select  sum(cuttree.TRSS_SCFL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where   cutreply.APAP_COUNID  = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 2;
           
end if
        
          
        
End if;
        
        
if typeId = 3 then
                    
if Common_Function.GetCountyDegree(countyId) = 0 then
             
select  sum(cuttree.TRSS_SCCL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where  cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 2;
           
end if;
        
           
if Common_Function.GetCountyDegree(countyId) = 1 then
             
select  sum(cuttree.TRSS_SCCL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where Common_Function.GetCityIdByCountyId( cutreply.APAP_COUNID) = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 2;
           
end if
        
            
if Common_Function.GetCountyDegree(countyId) = 2 then
             
select  sum(cuttree.TRSS_SCCL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where   cutreply.APAP_COUNID  = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 2;
           
end if
              
        
end if;
        
        
if typeId = 4 then
           
if Common_Function.GetCountyDegree(countyId) = 0 then
             
select  sum(cuttree.TRSS_SCFL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where  cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 2
             
and Common_Function.GetForestCodeById(cutreply.APAP_SOFOID2) = 12;
           
end if;
        
           
if Common_Function.GetCountyDegree(countyId) = 1 then
             
select  sum(cuttree.TRSS_SCFL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where Common_Function.GetCityIdByCountyId( cutreply.APAP_COUNID) = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 2
             
and Common_Function.GetForestCodeById(cutreply.APAP_SOFOID2) = 12;
           
end if
        
            
if Common_Function.GetCountyDegree(countyId) = 2 then
             
select  sum(cuttree.TRSS_SCFL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where   cutreply.APAP_COUNID  = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 2
             
and Common_Function.GetForestCodeById(cutreply.APAP_SOFOID2) = 12;
           
end if;
        
end if;
        
             
        
if typeId = 5 then
           
if Common_Function.GetCountyDegree(countyId) = 0 then
             
select  sum(cuttree.TRSS_SCCL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where  cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 2
             
and Common_Function.GetForestCodeById(cutreply.APAP_SOFOID2) = 12;
           
end if;
        
           
if Common_Function.GetCountyDegree(countyId) = 1 then
             
select  sum(cuttree.TRSS_SCCL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where Common_Function.GetCityIdByCountyId( cutreply.APAP_COUNID) = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 2
             
and Common_Function.GetForestCodeById(cutreply.APAP_SOFOID2) = 12;
           
end if
        
            
if Common_Function.GetCountyDegree(countyId) = 2 then
             
select  sum(cuttree.TRSS_SCCL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where   cutreply.APAP_COUNID  = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 2
             
and Common_Function.GetForestCodeById(cutreply.APAP_SOFOID2) = 12;
           
end if;      
    
        
        
end if;
        
        
if lastNum is null then
            lastNum :
= 0;
        
end if;
        
        lastNum :
= getRenGongLin(yearNum,countyId,typeId) - lastNum / 10000;
        
        
         
return lastNum;
    
End;
   
function GETTIANRANLIN (YEARNUM In Number,COUNTYID In Number,TYPEID In Numberreturn number as
   lastNum 
number;
    
Begin
               
        
if TypeId = 1 Then
            
select q.QUOT_TRZL into lastNum from CF_QUOTA q where q.QUOT_BXND = yearNum and q.QUOT_COUNXJID = countyId; 
        
End if;
        
        
if TypeId = 2 Then
            
select q.QUOT_TSCL into lastNum from CF_QUOTA q where q.QUOT_BXND = yearNum and q.QUOT_COUNXJID = countyId; 
        
End if;
        
        
if typeId = 3 then
            
select q.QUOT_TSCC into lastNum from CF_QUOTA q where q.QUOT_BXND = yearNum and q.QUOT_COUNXJID = countyId; 
        
end if;
        
        
return lastNum;
    
End;
   
function GETTIANRANLINJIEYU (YEARNUM In Number,COUNTYID In Number,TYPEID In Numberreturn number as
   lastNum 
number;
    
Begin
         
if TypeId = 1 Then
            
if Common_Function.GetCountyDegree(countyId) = 0 then
             
select  sum(cuttree.TRSS_XJHJ) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where  cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 1;
           
end if;
           
           
if Common_Function.GetCountyDegree(countyId) = 1 then
             
select  sum(cuttree.TRSS_XJHJ) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where Common_Function.GetCityIdByCountyId( cutreply.APAP_COUNID) = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 1;
           
end if;
           
           
if Common_Function.GetCountyDegree(countyId) = 2 then
             
select  sum(cuttree.TRSS_XJHJ) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where cutreply.APAP_COUNID = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 1;
           
end if;
           
        
End if;
        
        
if TypeId = 2 Then
              
if Common_Function.GetCountyDegree(countyId) = 0 then
             
select  sum(cuttree.TRSS_SCFL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where  cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 1;
           
end if;
           
           
if Common_Function.GetCountyDegree(countyId) = 1 then
             
select  sum(cuttree.TRSS_SCFL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where Common_Function.GetCityIdByCountyId( cutreply.APAP_COUNID) = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 1;
           
end if;
           
           
if Common_Function.GetCountyDegree(countyId) = 2 then
             
select  sum(cuttree.TRSS_SCFL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where cutreply.APAP_COUNID = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 1;
           
end if;
         
        
End if;
        
        
if typeId = 3 then
                     
if Common_Function.GetCountyDegree(countyId) = 0 then
             
select  sum(cuttree.TRSS_SCCL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where  cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 1;
           
end if;
           
           
if Common_Function.GetCountyDegree(countyId) = 1 then
             
select  sum(cuttree.TRSS_SCCL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where Common_Function.GetCityIdByCountyId( cutreply.APAP_COUNID) = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 1;
           
end if;
           
           
if Common_Function.GetCountyDegree(countyId) = 2 then
             
select  sum(cuttree.TRSS_SCCL) 
             
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
             
where cutreply.APAP_COUNID = CountyId
             
and cutreply.APAP_BXND = yearNum
             
and cutreply.APAP_ID = cuttree.TRSS_APAPID
             
and cutreply.APAP_ZT <> 0 
             
and cutreply.APAP_ZT <> 2
             
and cutreply.APAP_ZT <> 4
             
and Common_Function.GetOriginCodeById(cutreply.APAP_ORIGID) = 1;
           
end if;
        
end if;
      
        
if lastNum is null then
            lastNum :
= 0;
        
end if;
        
        lastNum :
= getTianRanLin(yearNum,countyId,typeId) - lastNum / 10000;
         
        
return lastNum;
    
End;
   
function GETMAOZHU (YEARNUM In Number,COUNTYID In Numberreturn Number as
   lastNum 
number;
    
begin
        
select q.QUOT_NZ into lastNum from CF_QUOTA q where q.QUOT_BXND = yearNum and q.QUOT_COUNXJID = countyId;  
        
       
return lastNum;
    
end;
   
function GETMAOZHUJIEYU (YEARNUM In Number,COUNTYID In Numberreturn Number as
   lastNum 
number;
    
begin
          
         
if Common_Function.GetCountyDegree(countyId) = 2 then
             
select  sum(cuttree.TRSS_NZZS) 
            
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
           
where cutreply.APAP_COUNID = CountyId
           
and cutreply.APAP_BXND = yearNum
           
and cutreply.APAP_ID = cuttree.TRSS_APAPID
           
and cutreply.APAP_ZT <> 0 
           
and cutreply.APAP_ZT <> 2
           
and cutreply.APAP_ZT <> 4
           
and Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) = '496';
         
end if;
         
         
         
if Common_Function.GetCountyDegree(countyId) = 1 then
             
select  sum(cuttree.TRSS_NZZS) 
            
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
           
where Common_Function.GetCityIdByCountyId(cutreply.APAP_COUNID) = CountyId
           
and cutreply.APAP_BXND = yearNum
           
and cutreply.APAP_ID = cuttree.TRSS_APAPID
           
and cutreply.APAP_ZT <> 0 
           
and cutreply.APAP_ZT <> 2
           
and cutreply.APAP_ZT <> 4
           
and Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) = '496';
         
end if;  
   
         
if Common_Function.GetCountyDegree(countyId) = 0 then
             
select  sum(cuttree.TRSS_NZZS) 
            
into lastNum  
             
from  CF_TREESPECIES cuttree,CF_APPLYAPPROVE cutreply   
           
where  cutreply.APAP_BXND = yearNum
           
and cutreply.APAP_ID = cuttree.TRSS_APAPID
           
and cutreply.APAP_ZT <> 0 
           
and cutreply.APAP_ZT <> 2
           
and cutreply.APAP_ZT <> 4
           
and Common_Function.GetTreeCodeById(cuttree.TRSS_TRSPID) = '496';
         
end if;
    
         
         
if lastNum is null then
              lastNum :
= 0;
         
end if;
         
         lastNum :
= getMaoZhu(yearNum,countyId) - lastNum / 10000;
         
         
         
return lastNum;
    
End;
end CUT_COUNTQUOTA;
/
posted on 2009-05-21 14:30 王总兵 阅读(208) 评论(0)  编辑  收藏 所属分类: DataBase

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


网站导航: