create or replace package body AutoLocateUtil is
procedure AutoLocate is
TYPE RefCurTyp IS REF CURSOR;
cv RefCurTyp;
nLib number;
nNewLib number;
nShelf number;
nBook number;
oCurDate date;
nCurCode number;
sCurNum varchar2(40);
begin
--(1) 检查当前定位位置
CheckCurrentLocation(oCurDate, nLib, nShelf, nBook);
--(2) 获取所有符合条件未定位的照片
open cv for 'select photo_plate_num,photo_plate_code
from dom_plate_record
where photo_plate_code = 99
and ARRIVAL_SIGNDATE >= :1
and ARRIVAL_SIGNDATE < :2
and photo_sourcetype=3
and photo_libid is null order by photo_plate_num'
using oCurDate, oCurDate + 1;
loop
fetch cv
into sCurNum, nCurCode;
exit when cv%NOTFOUND;
nNewLib := CheckLibName(nLib, oCurDate);
if (nNewLib = nLib) then
if (nBook >= 100) then
nShelf := nShelf + 1;
nBook := 1;
else
nBook := nBook + 1;
end if;
else
nLib := nNewLib;
nShelf := 1;
nBook := 1;
end if;
execute immediate 'update DOM_PLATE_RECORD set PHOTO_LIBID=:1,PHOTO_SHELF=:2,PHOTO_BOOK=:3 where PHOTO_PLATE_CODE=:4 and PHOTO_PLATE_NUM=:5 and PHOTO_SOURCETYPE=3'
using nLib, nShelf, nBook, nCurCode, sCurNum;
--满一百就要在定位表中添加新本记录
if (nBook = 100) then
execute immediate 'update PHOTO_PLATE_LOCATION set page=:1 where LIB=:2 and BOOK=:3'
using nBook, nLib, nShelf;
elsif (nBook = 1) then
execute immediate 'insert into PHOTO_PLATE_LOCATION values(:1,:2,1)'
using nLib, nShelf;
end if;
--commit;
end loop;
--最后一次循环结束后,如果不是100或1,则这里执行最后的更新,如果是100或1,则本次更新无效
execute immediate 'update PHOTO_PLATE_LOCATION set page=:1 where LIB=:2 and BOOK=:3'
using nBook, nLib, nShelf;
-- 必须重新计算架和本
-- 因为 Arrival_date的序和Photo_plate_num的序不一定一致,所以只有重新计算才可信
select max(photo_libid) into nLib from dom_plate_record ;
select max(photo_shelf) into nShelf from dom_plate_record where photo_libid = nLib;
select max(photo_book) into nBook from dom_plate_record where photo_libid = nLib and photo_shelf = nShelf;
execute immediate 'update PLATE_CURR_LOCATION set PLATE_LIB_ID=:1,PLATE_BOOK_ID=:2,PLATE_PAGE_ID=:3,PLATE_IMPORT_DATE=:4'
using nLib, nShelf, nBook, oCurDate + 1;
close cv;
commit;
end;
procedure AutoCleanLocate(sDate in varchar2) is
nPlateCode number;
sPlateNum varchar2(40);
clearDate Date;
currDate Date;
lib number;
shelf number;
book number;
TYPE RefCurTyp IS REF CURSOR;
cv RefCurTyp;
begin
select to_date(sDate, 'yyyy-mm-dd') into clearDate from dual;
open cv for 'select plate_import_date from plate_curr_location';
fetch cv
into currDate;
--如果没有找到定位指针,退出
if (cv%NOTFOUND) then
return;
end if;
close cv;
--还没有定位到的时间,马上结束
if (clearDate >= currDate) then
return;
end if;
--
open cv for 'select PHOTO_PLATE_CODE,
PHOTO_PLATE_NUM,
PHOTO_LIBID,
PHOTO_SHELF,
PHOTO_BOOK
from DOM_PLATE_RECORD
where PHOTO_PLATE_CODE = 99
and photo_libid > 0
AND arrival_signdate >= :1
and photo_sourcetype=3
group by PHOTO_PLATE_CODE,
PHOTO_PLATE_NUM,
PHOTO_LIBID,
PHOTO_SHELF,
PHOTO_BOOK'
using clearDate;
loop
fetch cv
into nPlateCode, sPlateNum, lib, shelf, book;
exit when cv%NOTFOUND;
execute immediate 'update DOM_PLATE_RECORD set PHOTO_LIBID=NULL,PHOTO_SHELF=NULL,PHOTO_BOOK=NULL where PHOTO_PLATE_CODE=:1 and PHOTO_PLATE_NUM=:2'
using nPlateCode, sPlateNum;
execute immediate 'update PHOTO_PLATE_LOCATION set PAGE=PAGE-1 where LIB=:1 and BOOK=:2'
using lib, shelf;
commit;
end loop;
close cv;
open cv for 'select PHOTO_LIBID, PHOTO_SHELF, PHOTO_BOOK from dom_plate_record where ARRIVAL_SIGNDATE in (select max(arrival_signdate) from dom_plate_record where PHOTO_PLATE_CODE = 99 and PHOTO_LIBID > 0 and ARRIVAL_SIGNDATE<:1) order by photo_shelf desc, photo_book desc'
using clearDate + 1;
fetch cv
into lib, shelf, book;
if (cv%NOTFOUND) then
execute immediate 'update plate_curr_location set plate_lib_id=null,plate_book_id=null,plate_page_id=null,PLATE_IMPORT_DATE=null';
else
-- 必须重新计算架和本
-- 因为 Arrival_date的序和Photo_plate_num的序不一定一致,所以只有重新计算才可信
select max(photo_libid) into lib from dom_plate_record ;
select max(photo_shelf) into shelf from dom_plate_record where photo_libid = lib;
select max(photo_book) into book from dom_plate_record where photo_libid = lib and photo_shelf = shelf;
execute immediate 'update plate_curr_location set plate_lib_id=:1,plate_book_id=:2,plate_page_id=:3,PLATE_IMPORT_DATE=:4'
using lib, shelf, book, clearDate;
end if;
close cv;
execute immediate 'delete from PHOTO_PLATE_LOCATION where PAGE<=0';
end;
procedure CheckCurrentLocation(oDate out date,
nLib out number,
nShelf out number,
nBook out number) is
TYPE RefCurTyp IS REF CURSOR;
cv RefCurTyp;
bExist number;
begin
nLib := 0;
nShelf := 0;
nBook := 0;
bExist := 1;
open cv for 'select PLATE_LIB_ID,PLATE_BOOK_ID,PLATE_PAGE_ID,PLATE_IMPORT_DATE from PLATE_CURR_LOCATION where rownum=1';
fetch cv
into nLib, nShelf, nBook, oDate;
if (cv%NOTFOUND) then
bExist := 0;
end if;
close cv;
if (bExist = 0) then
open cv for 'select arrival_signdate
from DOM_PLATE_RECORD
where PHOTO_PLATE_CODE = 99
and PHOTO_LIBID is null
order by arrival_signdate asc';
fetch cv
into oDate;
close cv;
end if;
end;
function CheckLibName(nLib in number, oDate in date) return number as
sYear varchar2(4);
sLibName varchar2(4);
nNewLib number;
begin
--select to_char(oDate + 1, 'yyyy') into sYear from dual;
select to_char(oDate, 'yyyy') into sYear from dual;
sYear := substr(sYear, 3);
sLibName := '';
execute immediate 'select entry_name from category_other where wt_type = :1 and entry_code = :2'
into sLibName
using 4, nLib;
if (sLibName <> sYear) then
execute immediate 'select entry_code from category_other where wt_type = :1 and entry_name = :2'
into nNewLib
using 4, sYear;
return nNewLib;
else
return nLib;
end if;
end;
end AutoLocateUtil;