深大3系统转换过程(1).docx

深大sulmis3系统数据库设置
数据库帐密：sa 19701103

在一台windows机器上安装sybase,oracle,sqlserver2000标准版,plsql
通过dbtour连接sybase
启动sybase,进入server config-adptive-configure adptive server-continue-continue-是
1. 配置好sybase的Dsedit.
2.
2.配置odbc驱动，可通过点击dbtour的database-open-ODBC Source-添加-选择sybase驱动程序-Data_Source_name(sybase),Description(sybase),Server Name(填Dsedit里的Servername),Database Name(用户的数据库名称，一定要填正确）
;

一、 处理图书
从 深大系统 数据转换-导出 带馆藏的marc（图书、期刊、非书资料）

905a 馆藏地点
905b 登录号（条码号）
905d/e 索书号
905P 价格
905S 馆藏状态
905B 装帧
905v 卷册信息
998b 文献流通类型

以下入库时间都点击“入库日期”改为不限，
1、中文图书

2、中文期刊

3、中文声像

4、中文民族

5、西文图书

6、西文期刊

7、民族西文

馆藏状态有2位，先接收到vendorno书商代码，后面再更新。

----------流通数据转换--------------------
导出导入工具导进oracle
C_Patron 读者表
C_PatronOrg 读者单位表
C_PatronRank 读者级别表
C_Loan 流通库、流通日志
C_Payment 赔书表
P_user 操作员
B_servSource 3950地址
B_rectype 资料类型
B_booktype资料类型
B_barcode 馆藏表
P_Org 馆藏地点表
C_class 班级代码

1、流通表（C_Loan）
id对应读者表（c_patron）的id
org对应读者表（c_patron）的org /org是单位字段
rank对应读者表（c_patron）的rank /rank是级别 如学生，教师
超期款对应fine字段
未归还的书DateRetu字段为NULL
借期对应DateLoan
应还期对应DateDue
2、读者表（C_patron) 余款对应pBalance字段
3、读者库单位表（C_PatronOrg）
4、读者库级别表（C_PatronRank)
5、赔书表（C_Payment）
6、期刊订购表（S_DG)
7、期刊周期表（S_plinit）

------------重命名-------------------
rename "C_Loan" to C_Loan;
rename "C_Patron" to C_Patron;
rename "C_PatronOrg" to C_PatronOrg;
rename "C_PatronRank" to C_PatronRank;
rename "C_Payment" to C_Payment;
rename "P_USER" to cuser;

---处理性别----
select count(*),sex from c_patron group by sex having count(*)>0;

delete from c_patron where sex like '%20%';
--------------------
1男，2女，空默认是女
--------------------

update c_patron set sex=2 where sex is null;

update c_patron set sex=0 where sex=2;
commit;

---读者类型错误处理（org字段和rank字段相反导致班别错误）---

更新空的终止时间
select * from c_patron where expire is null;
update c_patron set expire=lastmodidate where expire is null;
commit;

处理错误日期
select count(*),substr(creadate,1,4) from c_patron group by substr(creadate,1,4) ;

select * from c_patron where creadate like '0000%' and lastmodidate is null;
update c_patron set creadate=transpass where creadate like '0000%' and lastmodidate is null;
commit;

---更新到reader读者表---

insert into reader(
rdid,
rdloginid,
rdpasswd,
rdname,
rdcertify,
rdborndate,
rdsex,
rdcfstate,
rdtype,
rdglobal,
rdlibtype,
rdlib,
rdstartdate,
rdenddate,
rdaddress,
rdpostcode,
rdemail,
rdphone,
rdunit,
rdremark,
rdinterest,
rdspecialty,
rdsort1,
rdsort2,
rdsort3,
rdsort4,
rdsort5,
rdnation,
rdnative,
rdintime,
rdscore,
totalloannum,
totalresnum,
totalrenewnum,
totalinterlibnum,
totalpeccancy,
operator,
DUETIME,
sprule,
rdcenter,
centerrule,
RDREGISTERNAME,
NOTGETBOOKNUM,
workcardno
)
select cardno,'',ppasswd,pname,personalid,'',sex,1,rank,'','','LIBRARY',creadate,expire,addr,'',email,phone,addr,'',status,'','','','','','','','',creadate,
'','','','','','',creauser,lastmodidate,'','','','','',studentno from c_patron;

---更新读者证状态----
select count(*),status from c_patron group by status having count(*)>0;

状态查询分析：

1借满+超期
128退证
2超期
68暂停+失效
96挂失+失效
32挂失
64失效
192失效+退证
3借满+超期
36暂停+挂失
0正常

----interlib的读者状态：1-有效、2-验证、3-挂失、4-暂停、5-注销----

update reader set rdcfstate=1 where rdinterest='1';
update reader set rdcfstate=5 where rdinterest='128';
update reader set rdcfstate=1 where rdinterest='2';
update reader set rdcfstate=4 where rdinterest='68';
update reader set rdcfstate=3 where rdinterest='96';
update reader set rdcfstate=3 where rdinterest='32';
update reader set rdcfstate=4 where rdinterest='64';
update reader set rdcfstate=5 where rdinterest='192';
update reader set rdcfstate=1 where rdinterest='3';
update reader set rdcfstate=3 where rdinterest='36';
update reader set rdcfstate=1 where rdinterest'0';
commit;

select count(*),rdcfstate from reader group by rdcfstate having count(*)>0;

update reader set rdinterest='';
commit;

---更新押金表---
select count(*) from c_patron where pbalance<>0;
insert into rdaccount(rdid,deposit,prepay,arrearage) select rdid,0,0,0 from reader;
commit;

---处理c_loan表的时间---
alter table c_loan add(ldate timestamp(6));
alter table c_loan add(rdate timestamp(6));
alter table c_loan add(fdate timestamp(6));
commit

关联读者证号
alter table c_loan add(rdid NVARCHAR2(100));
commit;
update c_loan a set rdid=(select cardno from c_patron b where a.id=b.id);
commit;

---处理财经表---
---财经还有一部分，是在流通表记录表（c_loan) 里的，处理如下：
---把 c_loan 库里 fine 不为0 的 记录生成一个c_aa表
create table c_aa as select * from c_loan where fine<>0;
commit;

alter table c_aa add(userid NVARCHAR2(100));;

update c_aa a set userid=(select userlogname from c_user b where a.retuuserno=b.userno);

---添加到财经表-----
alter trigger TRIGGER_CIR_FIN_RDTYPE disable;
insert into cir_fin(
rdid,
feetype,
fee,
regtime,
regman,
reglib,
orglib,
localcode,
barcode,
bookrecno,
paysign,
feememo,
paytype,
loandate,
returndate
)
select rdid,'201',fine,datedue,userid,'LIBRARY','LIBRARY','',barcode,'',1,'sulcmis3',1,dateloan,dateretu from c_aa;
commit;

alter trigger TRIGGER_CIR_FIN_RDTYPE enable;

---处理流通表---

create table c_lt as select * from c_loan where dateretu is null;
insert into loan_work(
rdid,
barcode,
rulestate,
loancount,
loandate,
returndate,
duetime,
attachment
)
select rdid,barcode,1,0,dateloan,dateretu,dateloan,0 from c_lt;
commit;
update loan_work set barcode=trim(barcode);
commit;

---处理流通日志---
insert into log_cir(
logtype,
libcode,
userid,
ipaddr,
tablename,
data1,
data2,
data3,
data4,
regtime
)
select '30001','LIBRARY','','','','',rdid,barcode,loanuserno,dateloan from c_loan;
commit;

insert into log_cir(
logtype,
libcode,
userid,
ipaddr,
tablename,
data1,
data2,
data3,
data4,
regtime
)
select '30002','LIBRARY','','','','',rdid,barcode,retuuserno,dateretu from cloan where dateretu is not null;
commit;

update log_cir a set userid=(select userlogname from c_user b where a.data4=b.userno);
commit;
update log_cir set data4='';
commit;

---期刊订购、记到处理---

--深大S_DG表--
dgtype字段
1正常
2赠送
3换出
4换入
5增刊
6补缺

sl订购份数
导出导入工具将S_DG导进oracle
Rename “SDG” to s_dg;

alter table s_dg add(bkrecno NVARCHAR2(100));
commit;
create table ser_bb as select bookrecno,controlno,frequency from biblios where booktype=2;
commit;
alter table ser_bb add(ctrlnew NUMBER(7));
commit;

---为保证控制号和S_DG的ctrlno一样---

update ser_bb set ctrlnew=substr(controlno,5,6);
commit;
select ctrlno from s_dg where ctrlno not in(select ctrlnew from ser_bb);

update s_dg s set bkrecno=(select bookrecno from ser_bb b where b.ctrlnew=s.ctrlno) where exists(select ctrlnew from ser_bb b where b.ctrlnew=s.ctrlno);
commit;

--更新期刊预订表---

insert into ser_order(
orderrecno,
ordertype,
bookrecno,
libcode,
ordyear,
vendorno,
orderno,
batchno,
currency,
curprice,
singleprice,
wholeprice,
cyclecode,
seriesnum,
copies,
local,
memoinfo,
finsource,
booksource,
seriestype,
bindinfo,
orderman,
ordertime,
mediatype
)
select s_dgrowno,1,bkrecno,'LIBRARY',dgnf,dls,'','','CNY',price,price,price,pl,'',sl,'','',funo,'','a','平装','','','纸张' from s_dg;

commit;

---更新出版周期---

select count(*),cyclecode from ser_order group by cyclecode having count(*)>0;

update ser_order set memoinfo=cyclecode;
commit;

update ser_order set cyclecode='w2s' where memoinfo='b';
update ser_order set cyclecode='w3s' where memoinfo='n';
update ser_order set cyclecode='dm' where memoinfo='g';
update ser_order set cyclecode='x' where memoinfo='z';
update ser_order set cyclecode='q' where memoinfo='h';
update ser_order set cyclecode='t1' where memoinfo='o';
update ser_order set cyclecode='d' where memoinfo='a';
update ser_order set cyclecode='m' where memoinfo='f';
update ser_order set cyclecode='hm' where memoinfo='e';
update ser_order set cyclecode='w1' where memoinfo='c';
update ser_order set cyclecode='dw' where memoinfo='d';
update ser_order set cyclecode='hy' where memoinfo='j';
commit;

update ser_order set memoinfo='';
commit;

---期刊记到处理---

从深大系统的期刊-打印-登到或装订清单里输出记到信息到sql文件

---插入记到信息---
---先处理ser_cc表---
运行sql文件，将数据插入到oracle,修改创建表语句为
CREATE TABLE ser_cc (
Barcode nvarchar2(15),
Monetary nvarchar2(20),
Price decimal(16, 2),
Volume nvarchar2(250),
Bindery nvarchar2(30),
YearScope nvarchar2(250),
CtrlNo float,
CallNo nvarchar2(250),
Codes nvarchar2(250),
F200 nvarchar2(255),
F1xxa nvarchar2(20),
EditionType nvarchar2(1),
OrgName nvarchar2(250),
L5 nvarchar2(1),
Status float);

Rename xhqk to ser_cc;

delete from ser_cc where ctrlno like '%种数%';

commit;

alter table ser_cc add(bkrecno NVARCHAR2(100));
commit;
update ser_cc s set bkrecno=(select bookrecno from ser_bb where ctrlnew=s.ctrlno) where exists(select ctrlnew from ser_bb sb where sb.ctrlnew=s.ctrlno);
commit;

alter table ser_cc add(orderrecno number(10));

update ser_cc s set orderrecno=(select orderrecno from ser_order where bookrecno=s.bkrecno and rownum=1 and ordyear=substr(s.yearscope,1,4));
commit;

delete ser_cc where length(substr(yearscope,1,8))!=8;
update ser_cc set yearscope=(substr(yearscope,1,8));

update ser_cc set yearscope=substr(yearscope,1,6)||'30' where substr(yearscope,5,2) in('04','06','09','11') and substr(yearscope,7,2)='31';

update ser_cc set yearscope=substr(yearscope,1,6)||'28' where substr(yearscope,5,2)='02' and substr(yearscope,7,2) in('29','30','31');
commit;
---添加记到信息---
insert into ser_check(
checkrecno,
seqnum,
orderrecno,
checktype,
bookrecno,
volrecno,
regyear,
libcode,
vendorno,
orderno,
batchno,
regstate,
currency,
curprice,
singleprice,
ordernum,
copies,
year,
vol,
serno,
nosum,
liblocal
volnum,
finsource,
booksource,
mediatype,
bindinfo,
checkman,
intenddate,
checkdate,
bindnum,
mergesign,
mergerecno,
attachment
)
select ser_check_sq.nextval,'',orderrecno,1,bkrecno,'',substr(yearscope,1,4),'LIBRARY','','','',1,'CNY',price,price,
'','',substr(yearscope,1,4),'',substr(volume,instr(volume,'.')+1,2),'','','','','订购','纸张','平装','',to_date(substr(yearscope,1,8),'yyyymmdd'),to_date(substr(yearscope,1,8),'yyyymmdd'),'','','','' from ser_cc;
Commit;

---更新记到份数---
update ser_check s set ordernum=(select copies from ser_order where orderrecno=s.orderrecno),copies=(select copies from ser_order where orderrecno=s.orderrecno);
commit;

---删除有期刊预订无记到记录---
delete from ser_order s where not exists(select orderrecno from ser_check where orderrecno=s.orderrecno);

---更新记到期数---
select count(*),cyclecode from ser_order group by cyclecode having count(*)>0;

update ser_order a set seriesnum=(select cyclenum from p_cycle b where a.cyclecode=b.cyclecode);
commit;

--深大的支付价是年价，所以更新原价单价---
update ser_order a set memoinfo=(select cyclenum from p_cycle b where a.cyclecode=b.cyclecode);
update ser_order set singleprice=round(wholeprice/memoinfo,2);

update ser_order set curprice=singleprice;
update ser_order set memoinfo='';
commit;

--------更新装订表----------
---送装订---
---送装订---
insert into ser_bind(
bindrecno,
barcode,
bookrecno,
libcode,
vendorno,
batchno,
currency,
curprice,
price,
local,
volinfo,
booksource,
mediatype,

bindinfo,
bindstat,
bindman,
bindtime,
callno,
cirtype
)
select ser_bind_sq.nextval,barcode,bookrecno,orglib,vendorno,substr(volinfo,1,4),'',singleprice,totalprice,curlocal,volinfo,booksource,mediatype,bindinfo,1,'',regdate,callno,cirtype from holding where memoinfo='送装订';
commit;

---装订入藏（交送）---
insert into ser_bind(
bindrecno,
barcode,
bookrecno,
libcode,
vendorno,
batchno,
currency,
curprice,
price,
local,
volinfo,
booksource,
mediatype,
bindinfo,
bindstat,
bindman,
bindtime,
callno,
cirtype
)
select ser_bind_sq.nextval,barcode,bookrecno,orglib,vendorno,substr(volinfo,1,4),'',singleprice,totalprice,curlocal,volinfo,booksource,mediatype,bindinfo,9,'',regdate,callno,cirtype from holding where volinfo is not null and (volinfo like '%,%' or volinfo like '%-%') and cirtype='999_CN02' and state=2;
commit;