DROPPROCEDUREIFEXISTS newdb.checklist_procedure;CREATEPROCEDURE newdb.checklist_procedure()BEGINSELECT
olddb.checklist.CHECK_NOFROM
olddb.checklistWHERE
olddb.checklist.CHECK_NOIN(SELECT
newdb.checklist.check_noFROM
newdb.checklist);DROPTABLEIFEXISTS newdb.`multi_data_checkno`;CREATETABLE newdb.`multi_data_checkno`(`CHECK_NO`VARCHAR(25)NOTNULL,PRIMARYKEY(`CHECK_NO`));INSERTINTO newdb.multi_data_checkno(CHECK_NO)SELECT a.CHECK_NOFROM(SELECT
olddb.checklist.CHECK_NOFROM
olddb.checklistWHERE
olddb.checklist.CHECK_NOIN(SELECT
newdb.checklist.check_noFROM
newdb.checklist))AS a;UPDATE olddb.checklistSET olddb.checklist.CHECK_NO=CONCAT(olddb.checklist.CHECK_NO,'a')WHERE olddb.checklist.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);UPDATE olddb.assigntempSET olddb.assigntemp.CHECK_NO=CONCAT(olddb.assigntemp.CHECK_NO,'a')WHERE olddb.assigntemp.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);UPDATE olddb.checkpaySET olddb.checkpay.CHECK_NO= CONCAT(olddb.checkpay.CHECK_NO,'a')WHERE olddb.checkpay.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);UPDATE olddb.checkrefundtempSET olddb.checkrefundtemp.CHECK_NO= CONCAT(olddb.checkrefundtemp.CHECK_NO,'a')WHERE olddb.checkrefundtemp.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);UPDATE olddb.endoscopicreportSET olddb.endoscopicreport.CHECK_NO= CONCAT(olddb.endoscopicreport.CHECK_NO,'a')WHERE olddb.endoscopicreport.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);UPDATE olddb.imagefileSET olddb.imagefile.CHECK_NO= CONCAT(olddb.imagefile.CHECK_NO,'a')WHERE olddb.imagefile.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);UPDATE olddb.imagedetailSET olddb.imagedetail.CHECK_NO= CONCAT(olddb.imagedetail.CHECK_NO,'a')WHERE olddb.imagedetail.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);UPDATE olddb.medicalrecordSET olddb.medicalrecord.CHECK_NO= CONCAT(olddb.medicalrecord.CHECK_NO,'a')WHERE olddb.medicalrecord.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);UPDATE olddb.operatingrecordSET olddb.operatingrecord.CHECK_NO= CONCAT(olddb.operatingrecord.CHECK_NO,'a')WHERE olddb.operatingrecord.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);UPDATE olddb.orderpaySET olddb.orderpay.CHECK_NO= CONCAT(olddb.orderpay.CHECK_NO,'a')WHERE olddb.orderpay.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);UPDATE olddb.readorderSET olddb.readorder.CHECK_NO= CONCAT(olddb.readorder.CHECK_NO,'a')WHERE olddb.readorder.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);ALTERTABLE newdb.`case`MODIFY patient_noVARCHAR(20)NULL;ALTERTABLE newdb.`case`MODIFY inspection_subjectsVARCHAR(20)NULL;ALTERTABLE newdb.`case`MODIFY check_datedatetimeNULL;ALTERTABLE newdb.`case`MODIFY patient_idintNULL;ALTERTABLE newdb.`case`MODIFY inspection_idintNULL;ALTERTABLE newdb.`case`MODIFY inspection_organization_idintNULL;ALTERTABLE newdb.`case`MODIFY imcd_idintNULL;ALTERTABLE newdb.patientMODIFY nameVARCHAR(100)NULL;ALTERTABLE newdb.patientMODIFY sexintNULL;ALTERTABLE newdb.patientMODIFY phoneVARCHAR(11)NULL;ALTERTABLE newdb.patientMODIFY user_idintNULL;ALTERTABLE newdb.patientMODIFY organization_idintNULL;ALTERTABLE newdb.patientMODIFY m_c_d_idintNULL;ALTERTABLE newdb.patientMODIFY create_timedatetimeNULL;ALTERTABLE newdb.patientMODIFY city_nameVARCHAR(20)NULL;ALTERTABLE newdb.patientMODIFY district_nameVARCHAR(20)NULL;ALTERTABLE newdb.patientMODIFY province_nameVARCHAR(20)NULL;SET@case_id_temp=1000000;SET@patient_id_temp=1000000;SET@user_id_temp='';SET@user_id_temp_int='';SET@patient_name_temp='';SET@organization_id_temp='';SET@check_item_temp='';SET@status_temp='';SET@is_pay_temp='';SET@patient_age_temp='';SET@patient_sex_temp='';SET@patient_phone_temp='';SET@patient_no_temp='';SET@check_date_temp='';SET@patient_describe_temp='';SET@is_doctor=0;SET@is_expert=0;SET@is_operator='';SET@capsule_no_temp='';SET@audit_id_temp='';SET@paytype_temp='';SET@medicalrecord_id='';INSERT newdb.checklist(ID, check_no,money,pay_state,create_time, refund_reason,audit_note,upload_flag,refund_name, refund_date,audit_time,audit_name,type)SELECT olddb.checklist.ID+1000000,olddb.checklist.CHECK_NO,olddb.checklist.MONEY,olddb.checklist.IS_PAY+1,olddb.checklist.CREATE_TIME,olddb.checklist.REFUND_REASON,olddb.checklist.AUDIT_NOTE,olddb.checklist.UPLOAD_FLAG,olddb.checklist.REFUND_NAME,olddb.checklist.REFUND_DATE,olddb.checklist.AUDIT_TIME,olddb.checklist.AUDIT_NAME,olddb.checklist.TYPEFROM olddb.checklist;end;DROPTRIGGERIFEXISTS newdb.checklist_trigger;CREATETRIGGER newdb.checklist_trigger BEFOREINSERTON newdb.checklistFOR EACH ROWBEGINDECLARE old_checklist_idINT;SET old_checklist_id= NEW.ID-1000000;SET NEW.case_id=@case_id_temp;SET NEW.patient_id=@patient_id_temp;INSERTINTO newdb.`case`(id,patient_id)VALUES(@case_id_temp,@patient_id_temp);INSERTINTO newdb.patient(id)VALUES(@patient_id_temp);SELECT USER_IDINTO@user_id_tempFROM olddb.checklistWHERE ID= old_checklist_id;SELECT increment_idINTO@user_id_temp_intFROM doctortemWHERE ID=@user_id_temp;UPDATE newdb.caseSET inspection_id=@user_id_temp_intWHERE id=@case_id_temp;SELECT PATIENT_NAMEINTO@patient_name_tempFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.patientSET name=@patient_name_tempWHERE id=@patient_id_temp;SELECT ORGANIZATIONIDINTO@organization_id_tempFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.caseSET inspection_organization_id=@organization_id_tempWHERE id=@case_id_temp;SELECT CHECK_ITEMINTO@check_item_tempFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.caseSET inspection_subjects=@check_item_tempWHERE id=@case_id_temp;SELECTSTATUSINTO@status_tempFROM olddb.checklistWHERE ID= old_checklist_id;IF@status_temp<5THENSET NEW.status=@status_temp+1;ELSEIF@status_temp=5THENSET NEW.status=7;ELSEIF@status_temp=6THENSET NEW.status=10;ELSEIF@status_temp=7THENSET NEW.status=11;ENDIF;SELECT IS_PAYINTO@is_pay_tempFROM olddb.checklistWHERE ID= old_checklist_id;SET NEW.pay_state=@is_pay_temp+1;SELECT PATIENT_AGEINTO@patient_age_tempFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.patientSET age=@patient_age_tempWHERE id=@patient_id_temp;SELECT PATIENT_SEXINTO@patient_sex_tempFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.patientSET sex=@patient_sex_tempWHERE id=@patient_id_temp;SELECT PATIENT_PHONEINTO@patient_phone_tempFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.patientSET phone=@patient_phone_tempWHERE id=@patient_id_temp;SELECT PATIENT_NOINTO@patient_no_tempFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.caseSET patient_no=@patient_no_tempWHERE id=@case_id_temp;SELECT CHECK_DATEINTO@check_date_tempFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.caseSET check_date=@check_date_tempWHERE id=@case_id_temp;SELECT PATIENT_DESCRIBEINTO@patient_describe_tempFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.caseSET symptom_complaint=@patient_describe_tempWHERE id=@case_id_temp;SELECT increment_idINTO@is_doctorFROM newdb.doctortemWHERE newdb.doctortem.ID=(SELECT olddb.checklist.REFUND_IDFROM olddb.checklistWHERE olddb.checklist.ID= old_checklist_id);SELECT increment_idINTO@is_expertFROM newdb.experttempWHERE newdb.experttemp.ID=(SELECT olddb.checklist.REFUND_IDFROM olddb.checklistWHERE olddb.checklist.ID= old_checklist_id);SELECT REFUND_NAMEINTO@is_operatorFROM olddb.checklistWHERE ID= old_checklist_id;IF@is_doctor>0THENSET NEW.refund_id=@is_doctor;ELSEIF@is_expert>0THENSET NEW.refund_id=@is_expert;ELSEIF@is_operator='超级管理员'THENSET NEW.refund_id=1;ENDIF;SELECT CAPSULE_NOINTO@capsule_no_tempFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.caseSET capsule_no=@capsule_no_tempWHERE id=@case_id_temp;SELECT increment_idINTO@audit_id_tempFROM newdb.sys_users_tempWHERE newdb.sys_users_temp.ID=(SELECT olddb.checklist.AUDIT_IDFROM olddb.checklistWHERE olddb.checklist.ID= old_checklist_id);SET NEW.audit_id=@audit_id_temp;SELECT PAYTYPEINTO@paytypeFROM olddb.checklistWHERE ID= old_checklist_id;IF@paytype=0THENSET NEW.pay_type=1;ELSEIF@paytype=1THENSET NEW.pay_type=3;ENDIF;SELECT MEDICALRECORD_IDINTO@medicalrecord_idFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.caseSET medicalrecord_id=@medicalrecord_idWHERE id=@case_id_temp;SET@case_id_temp=@case_id_temp+1;SET@patient_id_temp=@patient_id_temp+1;END;CALL newdb.checklist_procedure();DROPPROCEDUREIFEXISTS newdb.checkpay_procedure;CREATEPROCEDURE newdb.checkpay_procedure()BEGINDECLARE doneBOOLEANDEFAULT0;DECLARE var_account_typeVARCHAR(8);DECLARE var_check_noVARCHAR(64);DECLARE var_pay_datedatetime;DECLARE checkpay_cursorCURSORFORSELECT
olddb.checkpay.ACCOUNT_TYPEAS account_type,
olddb.checkpay.CHECK_NOAS check_no,
olddb.checkpay.CREATE_TIMEAS pay_dateFROM olddb.checkpay;DECLARECONTINUEHANDLERFOR SQLSTATE'02000'