儿童、计免排队表迁移 (112).docx

1、儿童、计免排队表迁移说明：鉴于服务器稳定性要求，将儿童、计免排队表数据迁移到氏院HIS数据库。1. 表结构修改1.1.新建表SQ_PDDLIFOBJECT_ID('dbc.SQ_PD1)L')ISNOTNULLDROPTABLEdbo.SQPDDLGOCREATETABLEdbo.SQ_PDDLCONSTRAINTPK_SQ_PDDLPRIMARYKEY(JLXH)(JLXHPMIDPDRQNUMERIC(18)NOTNULL,NUMERIC(2)NOTNULL,DATETIMENOTNULL,PDLBNUMERIC(2)NOTNULL,PDHMNUMERIC(4)NOTNUL

2、L,GRBMVARCHAR(30)NOTNULL,BRXMVARCHAR(10)NOTNULL,DZXWVARCHAR(40)NOTNULL,JHFSNUMERIC(1)DEFAULT0,YFSBNUMERIC(3)NULL,YYIIMNUMERIC(4)NULL,YWLBNUMERIC(2)NULL,PMIPVARCHAR(50)NULL,PB_PMIDVARCHAR(10)NULL,)GOCREATENONCLUSTEREDINDEXIDX_SQ_PDDL_YFSB_PMIDONSQ_PDDL(YFSB,PMID)1.2.新建表SQ_PDDL_EBIFOBJECTED('SQPDD

3、L_EB')ISNOTNULLDROPTABLESQ_PDDL_EBGOinto:llpdhm,:Isxm,:SQL_PDB;into:1l_pdhm,:1s_xm,:SQLPDB;,"timer”,0,sqlca)endifloopgf_commit_transaction(SQLPDB)returntruep_error:gfrollbacktransaction(SQLPDB)returnfalse4.3.对象w_pdjh_eb_gm(古美儿保)4.3.1.修改timer事件selecttop1a.PDHM,a.BRXM,a.PDLB,a.GRBM,a.YYHM,a.J

4、LXH1Ipdlb,:ls_grbm,:1l_yyhm,:1IjlxhfromSQPDDLawherea.YFSB=:giyfsbandPMIDin(selectPMIDfromStmpPMID)orderbya.JLXHusinggfbegintransaction(SQLPDB)deleteSQ_PDDLwhereJLXH=:1l_jlxhusingSQL_PI)B;IFSQLPDB.SQLCode<>0THENgf_ro11backtransaction(SQLPDB)ENDIFgfcommit_transaction(SQLPDB)4.4.对象w_pdjh_eb_xz(莘庄

5、儿保)4.4.1.修改timer事件selecttop1a.PDHM,a.BRXM,a.PM11),a.YYHM,a.JLXH,a.DZXWllpmid,:ll_yyhm,:ll_jlxh,:ls_dzxwfromSQPDDLawherea.YFSB=:gi_yfsbandPMIDin(selectPMIDfrom#tmp_PMID)orderbya.JLXHusinggf.begin_transaction(SQLPDB)deletefromSQPDDLwhereJLXH=:ll_jlxhusingSQLPDB;ifSQL_PDB.sqlcode<>0thengserrtext=

6、SQLPDB.sqlerrtextgfrollbacktransaction(SQLPDB)gf_inserterrlog_trans(99,gs_errtext,this,classname0,"eventelsegf_commit_transciction(SQL_PDB)endif4.5.对象w_pdjh_jm_gm(古美计免)4.5.1.修改u_timing_jh事件SELECTMin(JLXH)INTO:ll_jlxhFROMSQ_PDDLWheredatediff(Day,PDRQ,getdateO)=0ANDYFSB=:gi_yfsbandPMIDin(selectPM

7、IDfrom#tmp_PMID)usingSQLPDB;/=IFIsNulKllJlxh)THENRETURNSELECTPDHM,BRXM,DZXW,pdlb,pmid,jhfsINTO:11pdhm,:ls_brxm,:ls_dzxw,:11pdlb,:11pmid,:li_jhfsFROMSQ_PDDLWhereJLXII=:ll_jlxhusingSQL_PDB;gfbegintransaction(SQLPDB)DELETESQ.PDDLWhereJLXH=:ll_jlxhusingSQL_PDB;IFSQLPDB.SQLCode<>0THENgs_errtext=SQL

8、PDB.SQLErrTextGOTOErrorENDIFgf_commit_transaction(SQLPDB)RETURNError:gf_rollback_transaction(SQLPDB)4.6.CREATETABLESQ_PDDL_EB(GRBMVARCHAR(40)NOTNULL,PDHMVARCHAR(10)NOTNULL,XMVARCHAR(12)NOTNULL,P【)I)LINTNOTNULL,YYHMNUMERIC(4)NULL,CONSTRAINTPK_PDDL_EB_GRBMPRIMARYKEYNONCLUSTERED(GRB.M)GO2. HIS中新增参数参数名称

9、默认值说明SQP1)BSJKSQ社区排队表数据库，如为H1S则连接H1S,否则为SQ3. 门急诊医生站3. 1.新增全局变量社区BTransactionsql_sq,sql_lis,SQL_TJ,SQL_PDB3.2.对象clinic3.2.1.修改open事件IIScript-openforclinic/Reason:社区一排队表迁移连接数据库/ModifiedBy:RichardDate:2014.07.22/SQL_PDB=createtransactionifgf_getpara一trans（0,"SQPDBSJK”,"SQ",”社区_排队表数据库，如为H

10、IS则连接HIS,否则为SQ",SQLCA）="HIS"thenSQL_PDB=SQLCAelseSQL_PDB=SQL_SQendif/二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二Open(w_cliniclogin)3.2.2.修改close事件/=二=/Script-closeforclinic/Reason:社区排队表迁移断开数据库连接/ModifiedBy:RichardDate:2014.07.22/ifisvalid(SQL_PDB)the

11、ndisconnectusingSQLPDB;destroySQLPDBendif3.3.对象gf_ebtj_pdjhll_jlxh=gf_get_max(SQL_PI)B,'SQ_P1)DL',1)gfbegintransaction(SQLPDB)INSERTSQ_PDDL(JLXH,PMID,PDRQ,PDLB,PDHM,GRBM,BRXM,DZXW,JHFS,YFSB,YYHM,YWLB,pmip,pb_pmid)VALUES(:ll_jlxh,:1i_pmid,:ldt_now,:ai_pdlb,:al_pdhm,:as_grbm,:ls_brxm,:ls_dzxw,

12、:ai_jhfs,:giyfsb,:alyyhm,6,:Ispmip,:Isjhmyebpmid)USINGSQL_P1)B;IFSQLPDB.SQLCode<>0THENgsorrtext=SQLPDB.SQLErrTcxtGOTOp_errorENDIFgf_commit_transaction(SQLPDB)perror:gf_ro11back_transaction(SQLPDB)3.4.对象gf_ebtj_pdjh_fysll_jlxh=gf_get_max(SQL_PI)B,'SQ_P1)I)L',1)gfbegintransaction(SQLPDB)

13、INSERTSQ.PDDL(JLXH,PMID,PDRQ,PDLB,PDHM,GRBM,BRXM,DZXW,JIIFS,YPSB,YWLB)VALUES(:lljlxh,:lipmid,:ldt_now,:aipdlb,:alpdhm,:asgrbm,:Isbrxm,:Isdzxw,:ai_jhfs,:gi_yfsb,6)USINGSQL_PDB;IFSQL_PDB.SQLCode<>0THENgserrtext=SQLPDB.SQLErrTextGOTOperrorENDIFgfcommit_transaction(SQLPDB)perror:gf_rollback_transa

14、ction(SQLPDB)3.5.对象gf_ebtjjpdjh_fys2ll_jlxh=gf_get_max(SQLPDB,'SQ_PDDL',1)gf_begin_transaction(sql_sq)gfbegintransaction(SQLPDB)INSERTSQ_PDDL(JLXH,PMID,PDRQ,PDLB,PDHM,GRBM,BRXM,DZXW,JHFS,YFSB,YWLB)VALUES(:ll_jlxh,:li_pmid,:ldt_now,:ai_pdlb,:alpdhm,:as_grbm,:1s_brxm,:ls_dzxw,:ai_jhfs,:giyfsb,

15、6)USINGSQLPDB;IFSQL_PDB.SQLCode<>0THENgs_errtexl=SQL_PDB.SQLErrTextGOTOperrorENDIFupdateebpdlbJcsetyjbz=1whereGRBM=:as_grbmandqhlx=:aipdlbusingsql_sq;IFsql_sq.SQLCode<>0THENgserrtext=sql_sq.SQLErrTextGOTOperrorENDIFgfcommittransaction(sqlsq)gfcommit_transaction(SQLPDB)RETURNTRUEp一error:g

16、frollbacktransaction(sqlsq)gf_ro11back_transaction(SQL_PDB)3.6.对象gf_jhmy_eb_pdjhll_jlxh=gf_get_max(SQL_PDB,'SQ_PDDL',1)gf_begin_transaction(sql_sq)gf_begin_transaetion(SQL_PDB)UPDATEJHMY_DASetYJBZ=:li_jqbhWheregrbm=:as_grbmUSINGsql_sq;IFsql_sq.SQLCode<>0THENgserrtext=sqlsq.SQLErrTextGO

17、TOperrorENDIF/增加业务类别7:计免INSERTSQ_PDDL(JLXH,PMID,PDRQ,PDLB,PDHM,GRBM,BRXV,DZXW,JHFS,YFSB,YWLB,PMIP,yyhm,pb_pmid)VALUES(:ll_jlxh,:li_pmid,:ldt_now,:ai_pdlb,:alpdhm,:as_grbm,:ls_brxm,:ls_dzxw,:ai_jhfs,:giyfsb,7,:ls_pmip,:ll_yyhm,:ls_jhmy_eb_pmid)USINGSQLPDB;IFSQL_PDB.SQLCode<>0THENgs_errtext=SQL_

18、PDB.SQLErrTextGOTOperrorENDIFgfcommittransaction(sqlsq)gf_commit_transaction(SQLPDB)小屏幕叫号f_eb_jhmy_jh(ls_brxm,string(alpdhm)RETURNTRUEp_error:gfrollbacktransaction(sqlsq)gfrol1backtransaction(SQLPDB)3.7.对象gf_jhmy_pdjhll_jlxh=gf_get_max(SQL_PDB,'SQ_PDDL',1)gf_begin_transaction(sql_sq)gfbegint

19、ransaction(SQLPDB)UPDATEJHMY_DASetYJBZ=:li_jqbhWheregrbm=:as_grbmUSINGsql_sq;IFsql_sq.SQLCode<>0THENgserrtext=sql_sq.SQLErrTextGOTOperrorENDIF/增加业务类别7:计免INSERTSQ_PDDL(JLXH,PM11),PDRQ,PDLB,PDHM,GRBM,BRXM,DZXW,JHFS,YFSB,YWLB,PMIP,yyhm)VALUES(:ll_jlxh,:lipmid,:ldt_now,:aipdlb,:alpdhm,:as_grbm,:Is

20、brxm,:ls_dzxw,:ai_jhfs,:giyfsb,7,:Ispmip,:llyyhm)USINGSQL.PDB;IFSQL_PDB.SQLCodc<>0THENgs_errtext=SQL_PDB.SQLErrTextGOTOperrorENDIFgfcommittransaction(sqlsq)gfcommittransaction(SQLPDB)/小屏幕叫号f_eb_jhmy_jh(1s_brxm,string(alpdhm)RETURNTRUEp_error:gfrollbacktransaction(sqlsq)gfrollbacktransaction(SQ

21、LPDB)3.8.对象w_eb_pdjh_l3.8.1.修改cb_jh.clicked事件gfbegintransaction(SQLPDB)insertintoSQ_PDDL_EB(GRBM,PDHM,XM,PDDL,YYHM)Values(:Isgrbm,:Ispdhm,:Isxm,:ilpdlb,:1iyyhm)usingSQLPDB;ifSQL_PDB.sqlcode<>0thengs_errtext=SQL_PDB.sqlerrtextgotoperrorendifgf_commit_transaction(SQLPDB)cb_refresh.triggerevent(c

22、licked!)returnp_error:gfrollbacktransaction(SQLPDB)3.9.对象w_eb_pdjh_fys3.9.1.修改cb_jh.clicked事件gfbegintransaction(SQLPDB)insertintoSQPDDL_EB(GRBM,PDHM,XM,PDDL,YYHM)&Values(:Isgrbm,:Ispdhm,:Isxm,:il_pdlb,:liyyhm)usingSQL_PDB;ifSQL_PDB.sqlcode<>0thengserrtext=SQLPDB.sqlerrtextgotop_errorendifg

23、fcomniit_transaction(SQLPDB)cbrefresh,triggerevent(clicked!)returnperror:gf_rollback_transaction(SQL_PDB)10.对象w_ebjpdjh_new3.10.1.修改cb_jh.clicked事件gf_begin_transaction(SQLPDB)insertintoSQPDDL_EB(GRBM,PDHM,XM,PDDL,YYHM)&Values(:Isgrbm,:Ispdhm,:Isxm,:il_pdlb,:1i_yyhm)usingSQLPDB;ifSQL_PDB.sqlcode&

24、lt;>0thengserrtext=SQLPDB.sqlerrtextgotop_errorendifgfccmmitransaction(SQLPDB)cbrefresh,triggerevent(clicked!)returnperror:gf_rollback_transaction(SQI,_PDB)社区排队叫号4. 1.新增全局变量transactiontransactionSQL_his,SQLPDB4.2.对象pdjh4.2.1.修改open事件/=/Script-openforpdjh/Reason:社区排队表迁移连接数据库/ModifiedBy:RichardDate:2014.07.22SQLPDB=createtransactionifgf_getpara_trans(O,SQ_PI)BSJK","SQ",“社区_排队表数据库，如为HIS则连接HIS,否则为SQ",SQLHIS)="HIS"thenSQL_PDB=SQLJIISelseSQL_PDB=SQLCAendif/=deleteSQPDDLwheredatediff(day,P