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 Number) return outlist;
function LISTCITYEXCEL (YEARNUM In Number,COUNTYID In Number) return outlist;
function LISTCOUNTYEXCEL (YEARNUM In Number,COUNTYID In Number) return outlist;
function GETXUJIHEJI (YEARNUM In Number,COUNTYID In Number) return Number;
function GETXUJIHEJIJIEYU (YEARNUM In Number,COUNTYID In Number) return Number;
function GETCUTTYPENUMBER (YEARNUM In Number,COUNTYID In Number,CUTTYPEID In Number) return Number;
function GETCUTTYPEJIEYU (YEARNUM In Number,COUNTYID In Number,CUTTYPEID In Number) return Number;
function GETXIAOHAOJIEGOU (YEARNUM In Number,COUNTYID In Number,TYPEID In number) return number;
function GETXIAOHAOJIEGOUJIEYU (YEARNUM In Number,COUNTYID In Number,TYPEID In number) return number;
function GETCHUCAILV (YEARNUM In Number,COUNTYID In Number) return Number;
function GETRENGONGLIN (YEARNUM In Number,COUNTYID In Number,TYPEID In Number) return number;
function GETRENGONGLINJIEYU (YEARNUM In Number,COUNTYID In Number,TYPEID In Number) return number;
function GETTIANRANLIN (YEARNUM In Number,COUNTYID In Number,TYPEID In Number) return number;
function GETTIANRANLINJIEYU (YEARNUM In Number,COUNTYID In Number,TYPEID In Number) return number;
function GETMAOZHU (YEARNUM In Number,COUNTYID In Number) return Number;
function GETMAOZHUJIEYU (YEARNUM In Number,COUNTYID In Number) return 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 Number) return 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,1) As ZhuFa,
getCuttypeJieYu(yearNum,CountyId,1) As ZhuFaJieYu,
getCuttypeNumber(yearNum,CountyId,2) As FuYu,
getCuttypeJieYu(yearNum,CountyId,2) As FuYuJieYu,
getCuttypeNumber(yearNum,CountyId,3) As GengXin,
getCuttypeJieYu(yearNum,CountyId,3) As GengXinJieYu,
getCuttypeNumber(yearNum,CountyId,4) As DiChan,
getCuttypeJieYu(yearNum,CountyId,4) As DiChanJieYu,
getCuttypeNumber(yearNum,CountyId,5) As QiTa,
getCuttypeJieYu(yearNum,CountyId,5) As QiTaJieYu,
getXiaoHaoJieGou(yearNum,CountyId,1) as ShangPingCaiFaLiang,
getXiaoHaoJieGouJieYu(yearNum,CountyId,1) as ShangPingCaiFaLiangJieYu,
getXiaoHaoJieGou(yearNum,CountyId,2) as ShangPingChuCaiLiang,
getXiaoHaoJieGouJieYu(yearNum,CountyId,2) as ShangPingChuCaiLiangJieYu,
getChuCaiLv(yearNum,CountyId) as ChuCaiLv,
getXiaoHaoJieGou(yearNum,CountyId,3) as FeiShangPingCai,
getXiaoHaoJieGouJieYu(yearNum,CountyId,3) as FeiShangPingCaiJieYu,
getRenGongLin(yearNum,CountyId,1) as RenGongLinZongLiang,
getRenGongLinJieYu(yearNum,CountyId,1) As RenGongLinZongLiangJieYu,
getRenGongLin(yearNum,CountyId,2) as RenGongLinSPCaiFaLiang,
getRenGongLinJieYu(yearNum,CountyId,2) As RenGongLinSPCaiFaLiangJieYu,
getRenGongLin(yearNum,CountyId,3) as RenGongLinSPChuCaiLiang,
getRenGongLinJieYu(yearNum,CountyId,3) As RenGongLinSPChuCaiLiangJieYu,
getRenGongLin(yearNum,CountyId,4) as RenGongLinGYCaiFaLiang,
getRenGongLinJieYu(yearNum,CountyId,4) As RenGongLinGYCaiFaLiangJieYu,
getRenGongLin(yearNum,CountyId,5) as RenGongLinGYChuCaiLiang,
getRenGongLinJieYu(yearNum,CountyId,5) As 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,1) As ZhuFa,
getCuttypeJieYu(yearNum,city.COUN_ID,1) As ZhuFaJieYu,
getCuttypeNumber(yearNum,city.COUN_ID,2) As FuYu,
getCuttypeJieYu(yearNum,city.COUN_ID,2) As FuYuJieYu,
getCuttypeNumber(yearNum,city.COUN_ID,3) As GengXin,
getCuttypeJieYu(yearNum,city.COUN_ID,3) As GengXinJieYu,
getCuttypeNumber(yearNum,city.COUN_ID,4) As DiChan,
getCuttypeJieYu(yearNum,city.COUN_ID,4) As DiChanJieYu,
getCuttypeNumber(yearNum,city.COUN_ID,5) As QiTa,
getCuttypeJieYu(yearNum,city.COUN_ID,5) As QiTaJieYu,
getXiaoHaoJieGou(yearNum,city.COUN_ID,1) as ShangPingCaiFaLiang,
getXiaoHaoJieGouJieYu(yearNum,city.COUN_ID,1) as ShangPingCaiFaLiangJieYu,
getXiaoHaoJieGou(yearNum,city.COUN_ID,2) as ShangPingChuCaiLiang,
getXiaoHaoJieGouJieYu(yearNum,city.COUN_ID,2) as ShangPingChuCaiLiangJieYu,
getChuCaiLv(yearNum,city.COUN_ID) as ChuCaiLv,
getXiaoHaoJieGou(yearNum,city.COUN_ID,3) as FeiShangPingCai,
getXiaoHaoJieGouJieYu(yearNum,city.COUN_ID,3) as FeiShangPingCaiJieYu,
getRenGongLin(yearNum,city.COUN_ID,1) as RenGongLinZongLiang,
getRenGongLinJieYu(yearNum,city.COUN_ID,1) As RenGongLinZongLiangJieYu,
getRenGongLin(yearNum,city.COUN_ID,2) as RenGongLinSPCaiFaLiang,
getRenGongLinJieYu(yearNum,city.COUN_ID,2) As RenGongLinSPCaiFaLiangJieYu,
getRenGongLin(yearNum,city.COUN_ID,3) as RenGongLinSPChuCaiLiang,
getRenGongLinJieYu(yearNum,city.COUN_ID,3) As RenGongLinSPChuCaiLiangJieYu,
getRenGongLin(yearNum,city.COUN_ID,4) as RenGongLinGYCaiFaLiang,
getRenGongLinJieYu(yearNum,city.COUN_ID,4) As RenGongLinGYCaiFaLiangJieYu,
getRenGongLin(yearNum,city.COUN_ID,5) as RenGongLinGYChuCaiLiang,
getRenGongLinJieYu(yearNum,city.COUN_ID,5) As 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,1) As ZhuFa,
getCuttypeJieYu(yearNum,county.COUN_ID,1) As ZhuFaJieYu,
getCuttypeNumber(yearNum,county.COUN_ID,2) As FuYu,
getCuttypeJieYu(yearNum,county.COUN_ID,2) As FuYuJieYu,
getCuttypeNumber(yearNum,county.COUN_ID,3) As GengXin,
getCuttypeJieYu(yearNum,county.COUN_ID,3) As GengXinJieYu,
getCuttypeNumber(yearNum,county.COUN_ID,4) As DiChan,
getCuttypeJieYu(yearNum,county.COUN_ID,4) As DiChanJieYu,
getCuttypeNumber(yearNum,county.COUN_ID,5) As QiTa,
getCuttypeJieYu(yearNum,county.COUN_ID,5) As QiTaJieYu,
getXiaoHaoJieGou(yearNum,county.COUN_ID,1) as ShangPingCaiFaLiang,
getXiaoHaoJieGouJieYu(yearNum,county.COUN_ID,1) as ShangPingCaiFaLiangJieYu,
getXiaoHaoJieGou(yearNum,county.COUN_ID,2) as ShangPingChuCaiLiang,
getXiaoHaoJieGouJieYu(yearNum,county.COUN_ID,2) as ShangPingChuCaiLiangJieYu,
getChuCaiLv(yearNum,county.COUN_ID) as ChuCaiLv,
getXiaoHaoJieGou(yearNum,county.COUN_ID,3) as FeiShangPingCai,
getXiaoHaoJieGouJieYu(yearNum,county.COUN_ID,3) as FeiShangPingCaiJieYu,
getRenGongLin(yearNum,county.COUN_ID,1) as RenGongLinZongLiang,
getRenGongLinJieYu(yearNum,CountyId,1) As RenGongLinZongLiangJieYu,
getRenGongLin(yearNum,county.COUN_ID,2) as RenGongLinSPCaiFaLiang,
getRenGongLinJieYu(yearNum,county.COUN_ID,2) As RenGongLinSPCaiFaLiangJieYu,
getRenGongLin(yearNum,county.COUN_ID,3) as RenGongLinSPChuCaiLiang,
getRenGongLinJieYu(yearNum,county.COUN_ID,3) As RenGongLinSPChuCaiLiangJieYu,
getRenGongLin(yearNum,county.COUN_ID,4) as RenGongLinGYCaiFaLiang,
getRenGongLinJieYu(yearNum,county.COUN_ID,4) As RenGongLinGYCaiFaLiangJieYu,
getRenGongLin(yearNum,county.COUN_ID,5) as RenGongLinGYChuCaiLiang,
getRenGongLinJieYu(yearNum,county.COUN_ID,5) As 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 Number) return 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,1) As ZhuFa,
getCuttypeJieYu(yearNum,CountyId,1) As ZhuFaJieYu,
getCuttypeNumber(yearNum,CountyId,2) As FuYu,
getCuttypeJieYu(yearNum,CountyId,2) As FuYuJieYu,
getCuttypeNumber(yearNum,CountyId,3) As GengXin,
getCuttypeJieYu(yearNum,CountyId,3) As GengXinJieYu,
getCuttypeNumber(yearNum,CountyId,4) As DiChan,
getCuttypeJieYu(yearNum,CountyId,4) As DiChanJieYu,
getCuttypeNumber(yearNum,CountyId,5) As QiTa,
getCuttypeJieYu(yearNum,CountyId,5) As QiTaJieYu,
getXiaoHaoJieGou(yearNum,CountyId,1) as ShangPingCaiFaLiang,
getXiaoHaoJieGouJieYu(yearNum,CountyId,1) as ShangPingCaiFaLiangJieYu,
getXiaoHaoJieGou(yearNum,CountyId,2) as ShangPingChuCaiLiang,
getXiaoHaoJieGouJieYu(yearNum,CountyId,2) as ShangPingChuCaiLiangJieYu,
getChuCaiLv(yearNum,CountyId) as ChuCaiLv,
getXiaoHaoJieGou(yearNum,CountyId,3) as FeiShangPingCai,
getXiaoHaoJieGouJieYu(yearNum,CountyId,3) as FeiShangPingCaiJieYu,
getRenGongLin(yearNum,CountyId,1) as RenGongLinZongLiang,
getRenGongLinJieYu(yearNum,CountyId,1) As RenGongLinZongLiangJieYu,
getRenGongLin(yearNum,CountyId,2) as RenGongLinSPCaiFaLiang,
getRenGongLinJieYu(yearNum,CountyId,2) As RenGongLinSPCaiFaLiangJieYu,
getRenGongLin(yearNum,CountyId,3) as RenGongLinSPChuCaiLiang,
getRenGongLinJieYu(yearNum,CountyId,3) As RenGongLinSPChuCaiLiangJieYu,
getRenGongLin(yearNum,CountyId,4) as RenGongLinGYCaiFaLiang,
getRenGongLinJieYu(yearNum,CountyId,4) As RenGongLinGYCaiFaLiangJieYu,
getRenGongLin(yearNum,CountyId,5) as RenGongLinGYChuCaiLiang,
getRenGongLinJieYu(yearNum,CountyId,5) As 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,1) As ZhuFa,
getCuttypeJieYu(yearNum,county.COUN_ID,1) As ZhuFaJieYu,
getCuttypeNumber(yearNum,county.COUN_ID,2) As FuYu,
getCuttypeJieYu(yearNum,county.COUN_ID,2) As FuYuJieYu,
getCuttypeNumber(yearNum,county.COUN_ID,3) As GengXin,
getCuttypeJieYu(yearNum,county.COUN_ID,3) As GengXinJieYu,
getCuttypeNumber(yearNum,county.COUN_ID,4) As DiChan,
getCuttypeJieYu(yearNum,county.COUN_ID,4) As DiChanJieYu,
getCuttypeNumber(yearNum,county.COUN_ID,5) As QiTa,
getCuttypeJieYu(yearNum,county.COUN_ID,5) As QiTaJieYu,
getXiaoHaoJieGou(yearNum,county.COUN_ID,1) as ShangPingCaiFaLiang,
getXiaoHaoJieGouJieYu(yearNum,county.COUN_ID,1) as ShangPingCaiFaLiangJieYu,
getXiaoHaoJieGou(yearNum,county.COUN_ID,2) as ShangPingChuCaiLiang,
getXiaoHaoJieGouJieYu(yearNum,county.COUN_ID,2) as ShangPingChuCaiLiangJieYu,
getChuCaiLv(yearNum,county.COUN_ID) as ChuCaiLv,
getXiaoHaoJieGou(yearNum,county.COUN_ID,3) as FeiShangPingCai,
getXiaoHaoJieGouJieYu(yearNum,county.COUN_ID,3) as FeiShangPingCaiJieYu,
getRenGongLin(yearNum,county.COUN_ID,1) as RenGongLinZongLiang,
getRenGongLinJieYu(yearNum,CountyId,1) As RenGongLinZongLiangJieYu,
getRenGongLin(yearNum,county.COUN_ID,2) as RenGongLinSPCaiFaLiang,
getRenGongLinJieYu(yearNum,county.COUN_ID,2) As RenGongLinSPCaiFaLiangJieYu,
getRenGongLin(yearNum,county.COUN_ID,3) as RenGongLinSPChuCaiLiang,
getRenGongLinJieYu(yearNum,county.COUN_ID,3) As RenGongLinSPChuCaiLiangJieYu,
getRenGongLin(yearNum,county.COUN_ID,4) as RenGongLinGYCaiFaLiang,
getRenGongLinJieYu(yearNum,county.COUN_ID,4) As RenGongLinGYCaiFaLiangJieYu,
getRenGongLin(yearNum,county.COUN_ID,5) as RenGongLinGYChuCaiLiang,
getRenGongLinJieYu(yearNum,county.COUN_ID,5) As 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 Number) return 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,1) As ZhuFa,
getCuttypeJieYu(yearNum,CountyId,1) As ZhuFaJieYu,
getCuttypeNumber(yearNum,CountyId,2) As FuYu,
getCuttypeJieYu(yearNum,CountyId,2) As FuYuJieYu,
getCuttypeNumber(yearNum,CountyId,3) As GengXin,
getCuttypeJieYu(yearNum,CountyId,3) As GengXinJieYu,
getCuttypeNumber(yearNum,CountyId,4) As DiChan,
getCuttypeJieYu(yearNum,CountyId,4) As DiChanJieYu,
getCuttypeNumber(yearNum,CountyId,5) As QiTa,
getCuttypeJieYu(yearNum,CountyId,5) As QiTaJieYu,
getXiaoHaoJieGou(yearNum,CountyId,1) as ShangPingCaiFaLiang,
getXiaoHaoJieGouJieYu(yearNum,CountyId,1) as ShangPingCaiFaLiangJieYu,
getXiaoHaoJieGou(yearNum,CountyId,2) as ShangPingChuCaiLiang,
getXiaoHaoJieGouJieYu(yearNum,CountyId,2) as ShangPingChuCaiLiangJieYu,
getChuCaiLv(yearNum,CountyId) as ChuCaiLv,
getXiaoHaoJieGou(yearNum,CountyId,3) as FeiShangPingCai,
getXiaoHaoJieGouJieYu(yearNum,CountyId,3) as FeiShangPingCaiJieYu,
getRenGongLin(yearNum,CountyId,1) as RenGongLinZongLiang,
getRenGongLinJieYu(yearNum,CountyId,1) As RenGongLinZongLiangJieYu,
getRenGongLin(yearNum,CountyId,2) as RenGongLinSPCaiFaLiang,
getRenGongLinJieYu(yearNum,CountyId,2) As RenGongLinSPCaiFaLiangJieYu,
getRenGongLin(yearNum,CountyId,3) as RenGongLinSPChuCaiLiang,
getRenGongLinJieYu(yearNum,CountyId,3) As RenGongLinSPChuCaiLiangJieYu,
getRenGongLin(yearNum,CountyId,4) as RenGongLinGYCaiFaLiang,
getRenGongLinJieYu(yearNum,CountyId,4) As RenGongLinGYCaiFaLiangJieYu,
getRenGongLin(yearNum,CountyId,5) as RenGongLinGYChuCaiLiang,
getRenGongLinJieYu(yearNum,CountyId,5) As 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 Number) return 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 Number) return 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 Number) return 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 Number) return 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 number) return 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 number) return 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 Number) return 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 Number) return 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 Number) return 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 Number) return 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 Number) return 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 Number) return 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 Number) return 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;
/