drop package LQ
/
/*==============================================================*/
/* Database package: LQ */
/*==============================================================*/
create or replace package LQ as
type OUTLIST is REF CURSOR;
function GETVILLAGENAMEBYID (VILLAGEID In Number) return Varchar2;
function GETTOWNNAMEBYID (TOWNID In Number) return Varchar2;
function GETCOUNTYNAMEBYID (COUNTYID In Number) return Varchar2;
function GETVILLAGECODEBYID (VILLAGEID In Number) return Varchar2;
function GETTOWNCODEBYID (TOWNID In Number) return Varchar2;
function GETCOUNTYCODEBYID (COUNTYID In Number) return Varchar2;
procedure REPORT3VILLAGE (VILLAGEID IN NUMBER,BEGINDATE IN DATE,ENDDATE IN DATE,RETURN_LIST OUT outlist);
procedure REPORT3TOWN (TOWNID IN NUMBER,BEGINDATE in date,ENDDATE in date,RETURN_LIST OUT outlist);
procedure REPORT3COUNTY (COUNTYID IN NUMBER,BEGINDATE in date,ENDDATE in date,RETURN_LIST OUT outlist);
procedure REPORT3CITY (CITYID IN NUMBER,BEGINDATE in date,ENDDATE in date,RETURN_LIST OUT outlist);
procedure REPORT3PROVINCE (PROVINCEID IN NUMBER,BEGINDATE in date,ENDDATE in date,RETURN_LIST OUT outlist);
function GETTOTALNUMBERVILLAGE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETGUOYOUNUMBERVILLAGE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETNONGCUNNUMBERVILLAGE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETGERENNUMBERVILLAGE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETGONGYOUNUMBERVILLAGE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETFEIGONGYOUNUMBERVILLAGE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETQITANUMBERVILLAGE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETTOTALNUMBERTOWN (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETGUOYOUNUMBERTOWN (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETNONGCUNNUMBERTOWN (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETGERENNUMBERTOWN (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETGONGYOUNUMBERTOWN (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETFEIGONGYOUNUMBERTOWN (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETQITANUMBERTOWN (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETTOTALNUMBERCOUNTY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETGUOYOUNUMBERCOUNTY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETNONGCUNNUMBERCOUNTY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETGERENNUMBERCOUNTY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETGONGYOUNUMBERCOUNTY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETFEIGONGYOUNUMBERCOUNTY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETQITANUMBERCOUNTY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETTOTALNUMBERCITY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETGUOYOUNUMBERCITY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETNONGCUNNUMBERCITY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETGERENNUMBERCITY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETGONGYOUNUMBERCITY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETFEIGONGYOUNUMBERCITY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETQITANUMBERCITY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETTOTALNUMBERPROVINCE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETGUOYOUNUMBERPROVINCE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETNONGCUNNUMBERPROVINCE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETGERENNUMBERPROVINCE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETGONGYOUNUMBERPROVINCE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETFEIGONGYOUNUMBERPROVINCE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
function GETQITANUMBERPROVINCE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number;
end LQ;
/
create or replace package body LQ as
function GETVILLAGENAMEBYID (VILLAGEID In Number) return Varchar2 as
villageName varchar2(50);
Begin
Select VILL_CM Into VillageName from GG_VILLAGE where VILL_ID = VillageId;
return VillageName;
End;
function GETTOWNNAMEBYID (TOWNID In Number) return Varchar2 as
villageName varchar2(50);
Begin
Select TOWN_XZM Into VillageName from GG_Town where TOWN_ID = TownId;
return VillageName;
End;
function GETCOUNTYNAMEBYID (COUNTYID In Number) return Varchar2 as
villageName varchar2(50);
Begin
Select COUN_MC Into VillageName from GG_County where COUN_ID = CountyId;
return VillageName;
End;
function GETVILLAGECODEBYID (VILLAGEID In Number) return Varchar2 as
VillageCode varchar2(50);
TownCode varchar2(50);
CountyCode varchar2(50);
Begin
select VILL_CDM Into VillageCode
From
GG_VILLAGE
where
VILL_ID = VillageId;
select town.TOWN_XZDM Into TownCode
From
GG_Town town,GG_Village village
Where
town.TOWN_ID = village.VILL_XZDMID
And
village.VILL_ID = VillageId;
select county.COUN_DM Into CountyCode
From
GG_County county,GG_town town,GG_village village
Where
village.VILL_ID = VillageId
And
town.TOWN_XDMID = county.COUN_ID
And
village.VILL_XZDMID = town.TOWN_ID;
return CountyCode || TownCode || VillageCode;
End;
function GETTOWNCODEBYID (TOWNID In Number) return Varchar2 as
TownCode varchar2(50);
CountyCode varchar2(50);
Begin
select town.TOWN_XZDM Into TownCode
From
GG_Town town,GG_Village village
Where
town.TOWN_ID = TownId;
select county.COUN_DM Into CountyCode
From
GG_County county,GG_town town
Where
town.TOWN_XDMID = TownId
And
town.TOWN_XDMID = county.COUN_ID;
return CountyCode || TownCode || '000';
End;
function GETCOUNTYCODEBYID (COUNTYID In Number) return Varchar2 as
CountyCode varchar2(50);
Begin
--return 'hello';
select county.COUN_DM Into CountyCode
From
GG_County county
Where
county.COUN_ID = CountyId;
return CountyCode || '000' || '000';
End;
procedure REPORT3VILLAGE (VILLAGEID IN NUMBER,BEGINDATE IN DATE,ENDDATE IN DATE,RETURN_LIST OUT outlist) as
BEGIN
OPEN return_list
FOR
SELECT
GetVillageCodeById(villageId) As addressCode,
getVillageNameById(villageId) As address,
getTotalNumberVillage(villageId,beginDate,endDate) AS totalNumber ,
getGuoYouNumberVillage(villageId,beginDate,endDate) AS gouYouNumber,
getNongCunNumberVillage(villageId,beginDate,endDate) AS NongCunNumber,
getGeRenNumberVillage(villageId,beginDate,endDate) AS GeRenNumber,
getGongYouNumberVillage(villageId,beginDate,endDate) AS GongYouNumber,
getFeiGongYouNumberVillage(villageId,beginDate,endDate) AS FeiGongYouNumber,
getQiTaNumberVillage(villageId,beginDate,endDate) AS QiTaNumber
from dual
Where
getTotalNumberVillage(villageId,beginDate,endDate) > 0
Order By 1 desc
;
END;
procedure REPORT3TOWN (TOWNID IN NUMBER,BEGINDATE in date,ENDDATE in date,RETURN_LIST OUT outlist) as
Begin
Open return_list
For
Select
GetTownCodeById(TownId) as addressCode,
GetTownNameById(townId) As Address,
getTotalNumberTown(TownId,beginDate,endDate) AS totalNumber ,
getGuoYouNumberTown(TownId,beginDate,endDate) AS gouYouNumber,
getNongCunNumberTown(TownId,beginDate,endDate) AS NongCunNumber,
getGeRenNumberTown(TownId,beginDate,endDate) AS GeRenNumber,
getGongYouNumberTown(TownId,beginDate,endDate) AS GongYouNumber,
getFeiGongYouNumberTown(TownId,beginDate,endDate) AS FeiGongYouNumber,
getQiTaNumberTown(TownId,beginDate,endDate) AS QiTaNumber
from dual
Where
getTotalNumberTown(TownId,beginDate,endDate) > 0
--/*
Union
Select
GetVillageCodeById(village.VILL_ID) As addressCode,
GetVillageNameById(village.VILL_ID) As Address,
getTotalNumberVillage(village.VILL_ID,beginDate,endDate) AS totalNumber ,
getGuoYouNumberVillage(village.VILL_ID,beginDate,endDate) AS gouYouNumber,
getNongCunNumberVillage(village.VILL_ID,beginDate,endDate) AS NongCunNumber,
getGeRenNumberVillage(village.VILL_ID,beginDate,endDate) AS GeRenNumber,
getGongYouNumberVillage(village.VILL_ID,beginDate,endDate) AS GongYouNumber,
getFeiGongYouNumberVillage(village.VILL_ID,beginDate,endDate) AS FeiGongYouNumber,
getQiTaNumberVillage(village.VILL_ID,beginDate,endDate) AS QiTaNumber
From dual,GG_VILLAGE village
Where
village.VILL_XZDMID = TownId
And
getTotalNumberVillage(village.VILL_ID,beginDate,endDate) > 0
--*/
order by 1 desc;
End;
procedure REPORT3COUNTY (COUNTYID IN NUMBER,BEGINDATE in date,ENDDATE in date,RETURN_LIST OUT outlist) as
Begin
Open return_list
For
Select
getCountyCodeById(CountyId)as AddressCode,
getCountyNameById(CountyId) As Address,
getTotalNumberCounty(CountyId,beginDate,endDate) AS totalNumber ,
getGuoYouNumberCounty(CountyId,beginDate,endDate) AS gouYouNumber,
getNongCunNumberCounty(CountyId,beginDate,endDate) AS NongCunNumber,
getGeRenNumberCounty(CountyId,beginDate,endDate) AS GeRenNumber,
getGongYouNumberCounty(CountyId,beginDate,endDate) AS GongYouNumber,
getFeiGongYouNumberCounty(CountyId,beginDate,endDate) AS FeiGongYouNumber,
getQiTaNumberCounty(CountyId,beginDate,endDate) AS QiTaNumber
From dual
Where
getTotalNumberCounty(CountyId,beginDate,endDate) > 0
union
Select
getTownCodeById(town.TOWN_ID)as AddressCode,
getTownNameById(town.TOWN_ID) As Address,
getTotalNumberTown(town.TOWN_ID,beginDate,endDate) AS totalNumber ,
getGuoYouNumberTown(town.TOWN_ID,beginDate,endDate) AS gouYouNumber,
getNongCunNumberTown(town.TOWN_ID,beginDate,endDate) AS NongCunNumber,
getGeRenNumberTown(town.TOWN_ID,beginDate,endDate) AS GeRenNumber,
getGongYouNumberTown(town.TOWN_ID,beginDate,endDate) AS GongYouNumber,
getFeiGongYouNumberTown(town.TOWN_ID,beginDate,endDate) AS FeiGongYouNumber,
getQiTaNumberTown(town.TOWN_ID,beginDate,endDate) AS QiTaNumber
From
dual,GG_Town town
Where
town.TOWN_XDMID = CountyId
And
getTotalNumberCounty(town.TOWN_ID,beginDate,endDate) > 0
Union
Select
getVillageCodeById(village.VILL_ID)as AddressCode,
getVillageNameById(village.VILL_ID) As Address,
getTotalNumberVillage(village.VILL_ID,beginDate,endDate) AS totalNumber ,
getGuoYouNumberVillage(village.VILL_ID,beginDate,endDate) AS gouYouNumber,
getNongCunNumberVillage(village.VILL_ID,beginDate,endDate) AS NongCunNumber,
getGeRenNumberVillage(village.VILL_ID,beginDate,endDate) AS GeRenNumber,
getGongYouNumberVillage(village.VILL_ID,beginDate,endDate) AS GongYouNumber,
getFeiGongYouNumberVillage(village.VILL_ID,beginDate,endDate) AS FeiGongYouNumber,
getQiTaNumberVillage(village.VILL_ID,beginDate,endDate) AS QiTaNumber
From
dual,GG_Town town,GG_Village village
Where
town.TOWN_XDMID = CountyId
And
village.VILL_XZDMID = town.TOWN_ID
And
getTotalNumberVillage(village.VILL_ID,beginDate,endDate) > 0
order by 1 asc
;
End;
procedure REPORT3CITY (CITYID IN NUMBER,BEGINDATE in date,ENDDATE in date,RETURN_LIST OUT outlist) as
Begin
Open return_list
For
Select
getCountyCodeById(CityId)as AddressCode,
getCountyNameById(CityId) As Address,
getTotalNumberCity(CityId,beginDate,endDate) AS totalNumber ,
getGuoYouNumberCity(CityId,beginDate,endDate) AS gouYouNumber,
getNongCunNumberCity(CityId,beginDate,endDate) AS NongCunNumber,
getGeRenNumberCity(CityId,beginDate,endDate) AS GeRenNumber,
getGongYouNumberCity(CityId,beginDate,endDate) AS GongYouNumber,
getFeiGongYouNumberCity(CityId,beginDate,endDate) AS FeiGongYouNumber,
getQiTaNumberCity(CityId,beginDate,endDate) AS QiTaNumber
From dual
Where
getTotalNumberCity(CityId,beginDate,endDate) > 0
union
Select
getCountyCodeById(county.COUN_ID)as AddressCode,
getCountyNameById(county.COUN_ID) As Address,
getTotalNumberCounty(county.COUN_ID,beginDate,endDate) AS totalNumber ,
getGuoYouNumberCounty(county.COUN_ID,beginDate,endDate) AS gouYouNumber,
getNongCunNumberCounty(county.COUN_ID,beginDate,endDate) AS NongCunNumber,
getGeRenNumberCounty(county.COUN_ID,beginDate,endDate) AS GeRenNumber,
getGongYouNumberCounty(county.COUN_ID,beginDate,endDate) AS GongYouNumber,
getFeiGongYouNumberCounty(county.COUN_ID,beginDate,endDate) AS FeiGongYouNumber,
getQiTaNumberCounty(county.COUN_ID,beginDate,endDate) AS QiTaNumber
From dual,GG_County county
Where
getTotalNumberCounty(county.COUN_ID,beginDate,endDate) > 0
And
county.COUN_SJID = CityId
union
Select
getTownCodeById(town.TOWN_ID)as AddressCode,
getTownNameById(town.TOWN_ID) As Address,
getTotalNumberTown(town.TOWN_ID,beginDate,endDate) AS totalNumber ,
getGuoYouNumberTown(town.TOWN_ID,beginDate,endDate) AS gouYouNumber,
getNongCunNumberTown(town.TOWN_ID,beginDate,endDate) AS NongCunNumber,
getGeRenNumberTown(town.TOWN_ID,beginDate,endDate) AS GeRenNumber,
getGongYouNumberTown(town.TOWN_ID,beginDate,endDate) AS GongYouNumber,
getFeiGongYouNumberTown(town.TOWN_ID,beginDate,endDate) AS FeiGongYouNumber,
getQiTaNumberTown(town.TOWN_ID,beginDate,endDate) AS QiTaNumber
From
dual,GG_Town town,GG_County county
Where
town.TOWN_XDMID = county.coun_id
And
county.COUN_SJID = CityId
And
getTotalNumberCounty(town.TOWN_ID,beginDate,endDate) > 0
Union
Select
getVillageCodeById(village.VILL_ID)as AddressCode,
getVillageNameById(village.VILL_ID) As Address,
getTotalNumberVillage(village.VILL_ID,beginDate,endDate) AS totalNumber ,
getGuoYouNumberVillage(village.VILL_ID,beginDate,endDate) AS gouYouNumber,
getNongCunNumberVillage(village.VILL_ID,beginDate,endDate) AS NongCunNumber,
getGeRenNumberVillage(village.VILL_ID,beginDate,endDate) AS GeRenNumber,
getGongYouNumberVillage(village.VILL_ID,beginDate,endDate) AS GongYouNumber,
getFeiGongYouNumberVillage(village.VILL_ID,beginDate,endDate) AS FeiGongYouNumber,
getQiTaNumberVillage(village.VILL_ID,beginDate,endDate) AS QiTaNumber
From
dual,GG_Town town,GG_Village village,GG_County county
Where
town.TOWN_XDMID = county.coun_id
And
county.COUN_SJID = CityId
And
village.VILL_XZDMID = town.TOWN_ID
And
getTotalNumberVillage(village.VILL_ID,beginDate,endDate) > 0
order by 1 asc
;
End;
procedure REPORT3PROVINCE (PROVINCEID IN NUMBER,BEGINDATE in date,ENDDATE in date,RETURN_LIST OUT outlist) as
Begin
Open return_list
For
Select
getCountyCodeById(ProvinceId)as AddressCode,
getCountyNameById(ProvinceId) As Address,
getTotalNumberProvince(ProvinceId,beginDate,endDate) AS totalNumber ,
getGuoYouNumberProvince(ProvinceId,beginDate,endDate) AS gouYouNumber,
getNongCunNumberProvince(ProvinceId,beginDate,endDate) AS NongCunNumber,
getGeRenNumberProvince(ProvinceId,beginDate,endDate) AS GeRenNumber,
getGongYouNumberProvince(ProvinceId,beginDate,endDate) AS GongYouNumber,
getFeiGongYouNumberProvince(ProvinceId,beginDate,endDate) AS FeiGongYouNumber,
getQiTaNumberProvince(ProvinceId,beginDate,endDate) AS QiTaNumber
From dual
Union
Select
getCountyCodeById(county.coun_ID)as AddressCode,
getCountyNameById(county.coun_ID) As Address,
getTotalNumberCity(county.coun_ID,beginDate,endDate) AS totalNumber ,
getGuoYouNumberCity(county.coun_ID,beginDate,endDate) AS gouYouNumber,
getNongCunNumberCity(county.coun_ID,beginDate,endDate) AS NongCunNumber,
getGeRenNumberCity(county.coun_ID,beginDate,endDate) AS GeRenNumber,
getGongYouNumberCity(county.coun_ID,beginDate,endDate) AS GongYouNumber,
getFeiGongYouNumberCity(county.coun_ID,beginDate,endDate) AS FeiGongYouNumber,
getQiTaNumberCity(county.coun_ID,beginDate,endDate) AS QiTaNumber
From dual,GG_county county
Where
getTotalNumberCity(county.coun_ID,beginDate,endDate) > 0
And
county.COUN_SJID = ProvinceId
union
Select
getCountyCodeById(county.COUN_ID)as AddressCode,
getCountyNameById(county.COUN_ID) As Address,
getTotalNumberCounty(county.COUN_ID,beginDate,endDate) AS totalNumber ,
getGuoYouNumberCounty(county.COUN_ID,beginDate,endDate) AS gouYouNumber,
getNongCunNumberCounty(county.COUN_ID,beginDate,endDate) AS NongCunNumber,
getGeRenNumberCounty(county.COUN_ID,beginDate,endDate) AS GeRenNumber,
getGongYouNumberCounty(county.COUN_ID,beginDate,endDate) AS GongYouNumber,
getFeiGongYouNumberCounty(county.COUN_ID,beginDate,endDate) AS FeiGongYouNumber,
getQiTaNumberCounty(county.COUN_ID,beginDate,endDate) AS QiTaNumber
From dual,GG_County county ,GG_County city
Where
getTotalNumberCounty(county.COUN_ID,beginDate,endDate) > 0
And
county.COUN_SJID = city.coun_ID
And
city.COUN_SJID = ProvinceId
union
Select
getTownCodeById(town.TOWN_ID)as AddressCode,
getTownNameById(town.TOWN_ID) As Address,
getTotalNumberTown(town.TOWN_ID,beginDate,endDate) AS totalNumber ,
getGuoYouNumberTown(town.TOWN_ID,beginDate,endDate) AS gouYouNumber,
getNongCunNumberTown(town.TOWN_ID,beginDate,endDate) AS NongCunNumber,
getGeRenNumberTown(town.TOWN_ID,beginDate,endDate) AS GeRenNumber,
getGongYouNumberTown(town.TOWN_ID,beginDate,endDate) AS GongYouNumber,
getFeiGongYouNumberTown(town.TOWN_ID,beginDate,endDate) AS FeiGongYouNumber,
getQiTaNumberTown(town.TOWN_ID,beginDate,endDate) AS QiTaNumber
From
dual,GG_Town town,GG_County county,GG_county city
Where
town.TOWN_XDMID = county.coun_id
And
county.COUN_SJID = city.coun_ID
And
city.coun_SJID = provinceId
And
getTotalNumberCounty(town.TOWN_ID,beginDate,endDate) > 0
Union
Select
getVillageCodeById(village.VILL_ID)as AddressCode,
getVillageNameById(village.VILL_ID) As Address,
getTotalNumberVillage(village.VILL_ID,beginDate,endDate) AS totalNumber ,
getGuoYouNumberVillage(village.VILL_ID,beginDate,endDate) AS gouYouNumber,
getNongCunNumberVillage(village.VILL_ID,beginDate,endDate) AS NongCunNumber,
getGeRenNumberVillage(village.VILL_ID,beginDate,endDate) AS GeRenNumber,
getGongYouNumberVillage(village.VILL_ID,beginDate,endDate) AS GongYouNumber,
getFeiGongYouNumberVillage(village.VILL_ID,beginDate,endDate) AS FeiGongYouNumber,
getQiTaNumberVillage(village.VILL_ID,beginDate,endDate) AS QiTaNumber
From
dual,GG_Town town,GG_Village village,GG_County county,GG_COUNTY city
Where
town.TOWN_XDMID = county.coun_id
And
county.COUN_SJID = city.coun_id
and
city.coun_sjId = provinceId
And
village.VILL_XZDMID = town.TOWN_ID
And
getTotalNumberVillage(village.VILL_ID,beginDate,endDate) > 0
order by 1 asc
;
End;
function GETTOTALNUMBERVILLAGE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate ;
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate ;
return totalNumber;
end if;
return totalNumber;
End;
function GETGUOYOUNUMBERVILLAGE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'G' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'G';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'G' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'G';
return totalNumber;
end if;
return totalNumber;
End;
function GETNONGCUNNUMBERVILLAGE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'J' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'J';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'J' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'J';
return totalNumber;
end if;
return totalNumber;
End;
function GETGERENNUMBERVILLAGE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'R' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'R';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'R' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'R';
return totalNumber;
end if;
return totalNumber;
End;
function GETGONGYOUNUMBERVILLAGE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'K' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'K';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'K' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'K';
return totalNumber;
end if;
return totalNumber;
End;
function GETFEIGONGYOUNUMBERVILLAGE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'F' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'F';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'F' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'F';
return totalNumber;
end if;
return totalNumber;
End;
function GETQITANUMBERVILLAGE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'Q' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'Q';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'Q' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_CDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'Q';
return totalNumber;
end if;
return totalNumber;
End;
function GETTOTALNUMBERTOWN (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate ;
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate ;
return totalNumber;
end if;
return totalNumber;
End;
function GETGUOYOUNUMBERTOWN (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'G' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'G';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'G' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'G';
return totalNumber;
end if;
return totalNumber;
End;
function GETNONGCUNNUMBERTOWN (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'J' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'J';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'J' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'J';
return totalNumber;
end if;
return totalNumber;
End;
function GETGERENNUMBERTOWN (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'R' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'R';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'R' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'R';
return totalNumber;
end if;
return totalNumber;
End;
function GETGONGYOUNUMBERTOWN (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'K' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'K';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'K' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'K';
return totalNumber;
end if;
return totalNumber;
End;
function GETFEIGONGYOUNUMBERTOWN (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'F' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'F';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'F' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'F';
return totalNumber;
end if;
return totalNumber;
End;
function GETQITANUMBERTOWN (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'Q' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'Q';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'Q' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XZDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'Q';
return totalNumber;
end if;
return totalNumber;
End;
function GETTOTALNUMBERCOUNTY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate ;
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate ;
return totalNumber;
end if;
return totalNumber;
End;
function GETGUOYOUNUMBERCOUNTY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'G' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'G';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'G' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'G';
return totalNumber;
end if;
return totalNumber;
End;
function GETNONGCUNNUMBERCOUNTY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'J' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'J';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'J' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'J';
return totalNumber;
end if;
return totalNumber;
End;
function GETGERENNUMBERCOUNTY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'R' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'R';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'R' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'R';
return totalNumber;
end if;
return totalNumber;
End;
function GETGONGYOUNUMBERCOUNTY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'K' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'K';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'K' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'K';
return totalNumber;
end if;
return totalNumber;
End;
function GETFEIGONGYOUNUMBERCOUNTY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'F' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'F';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'F' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'F';
return totalNumber;
end if;
return totalNumber;
End;
function GETQITANUMBERCOUNTY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'Q' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'Q';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'Q' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_XDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'Q';
return totalNumber;
end if;
return totalNumber;
End;
function GETTOTALNUMBERCITY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate ;
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate ;
return totalNumber;
end if;
return totalNumber;
End;
function GETGUOYOUNUMBERCITY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'G' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'G';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'G' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'G';
return totalNumber;
end if;
return totalNumber;
End;
function GETNONGCUNNUMBERCITY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'J' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'J';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'J' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'J';
return totalNumber;
end if;
return totalNumber;
End;
function GETGERENNUMBERCITY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'R' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'R';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'R' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'R';
return totalNumber;
end if;
return totalNumber;
End;
function GETGONGYOUNUMBERCITY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'K' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'K';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'K' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'K';
return totalNumber;
end if;
return totalNumber;
End;
function GETFEIGONGYOUNUMBERCITY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'F' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'F';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'F' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'F';
return totalNumber;
end if;
return totalNumber;
End;
function GETQITANUMBERCITY (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_MTYPE = 'Q' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'Q';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'Q' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_SDMID = VillageId And SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'Q';
return totalNumber;
end if;
return totalNumber;
End;
function GETTOTALNUMBERPROVINCE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_FZRQ < endDate ;
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_FZRQ > beginDate ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate ;
return totalNumber;
end if;
return totalNumber;
End;
function GETGUOYOUNUMBERPROVINCE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_MTYPE = 'G' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'G';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'G' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'G';
return totalNumber;
end if;
return totalNumber;
End;
function GETNONGCUNNUMBERPROVINCE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_MTYPE = 'J' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'J';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'J' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'J';
return totalNumber;
end if;
return totalNumber;
End;
function GETGERENNUMBERPROVINCE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_MTYPE = 'R' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'R';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'R' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'R';
return totalNumber;
end if;
return totalNumber;
End;
function GETGONGYOUNUMBERPROVINCE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_MTYPE = 'K' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'K';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'K' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'K';
return totalNumber;
end if;
return totalNumber;
End;
function GETFEIGONGYOUNUMBERPROVINCE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_MTYPE = 'F' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'F';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'F' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'F';
return totalNumber;
end if;
return totalNumber;
End;
function GETQITANUMBERPROVINCE (VILLAGEID In Number,BEGINDATE In Date,ENDDATE In Date) return Number as
totalNumber number;
Begin
--not need to compare begin date and end date
if BeginDate Is Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_MTYPE = 'Q' ;
return totalNumber;
end if;
--only to compare end date
if BeginDate Is null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_FZRQ < endDate And SERE_MTYPE = 'Q';
return totalNumber;
end if;
--only to compare begin date
if BeginDate Is Not Null And EndDate Is Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_MTYPE = 'Q' ;
return totalNumber;
end if;
--compare begin date and end date
if BeginDate Is Not Null And EndDate Is Not Null Then
Select count(*) into totalNumber from LQ_SENDREGISTER where SERE_ZT = 4 And SERE_FZRQ > beginDate And SERE_FZRQ < EndDate And SERE_MTYPE = 'Q';
return totalNumber;
end if;
return totalNumber;
End;
end LQ;
/