package ay.zk; import java.sql.Connection; import java.sql.ResultSet; import ay.db.ConnTools; import ay.db.DbUtil; public class Zk_ayjxjy_sc_nj { public static void main(String args[]) { /** select sch.name,y.name from school sch left join school_yunguan sy on sch.school_id = sy.school_id left join yunguan y on y.yid = sy.yid nj_employed (1)用ayadmin登录， 组织管理-> 运管管理 创建 51100000 内江对应运管处 授用户角色权限 建用户给角色权限 (2)用运管身份登录， 组织管理->驾校管理 51100001 内江对应驾校 (3)用ayadmin登录， 给运管挂大纲(教学大纲管理->教学大纲 添加从业类型 , 然后点击一条从业类型记录，点击“详细信息”，若无，点右边“使用已有方案”,选一条已有方案，点击左上角“选择方案“) (4)用ayadmin登录， 给运管挂方案(题库管理->方案管理->右方选择运管->乐山运管处 ->查询 左方选择方案->点击添加方案 ->保存方案) (5)用ayadmin登录， 题库管理->方案管理-> 选择运管 - 查询 - 右边选择方案 - 添加方案 - 保存方案 (6)用运管身份登录 ， 组织管理->驾校管理 选择驾校，点修改，经营范围(从业类型)，全选，保存 * 导入数据之前先建驾校和运管，以便得到运管ID：tfc_id和驾校ID：ay_id，并按照迁移方案挂大纲 * * 导入数据之前请确认tfc_id和ay_id已经修改 * */ try { /* //如果字段类型是时间戳的字段， 要把数字转日期格式 //---to_char()命令可以将时间戳转换成字符串：select to_char( TO_TIMESTAMP('2014-06-20 12:11:11','YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') from dual; Connection conn_oracle_ayth = ConnTools.make_oracle_ayth_conn(); Connection conn_ppas_ayth = ConnTools.make_ppas_ayth_conn(); DbUtil d = new DbUtil(); String zcode=""; //地区代码(area_code,zcode,zid) sql_select 要带上 where (area_code,zcode,zid)= Zk_ayth_init ayth=new Zk_ayth_init(); ayth.begin(conn_ppas_ayth,zcode); //去脏数据 //d.insert(conn_oracle_ayth, conn_ppas_ayth, "aa", "select a01,a02,a03 from aa ", "insert into aa(a01,a02,a03)", "delete from aa"); ayth.end(conn_ppas_ayth,zcode); //系统初始化:系统参数，用户权限 conn_oracle_ayth.close(); conn_ppas_ayth.close(); */ //如果字段类型是时间戳的字段， 要把数字转日期格式 //---to_char()命令可以将时间戳转换成字符串：select to_char( TO_TIMESTAMP('2014-06-20 12:11:11','YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') from dual; Connection conn_oracle_ayjxjy = ConnTools.make_oracle_ayjxjy_conn(); Connection conn_ppas_ayjxjy = ConnTools.make_ppas_ayjxjy_conn(); /** * 1.用超级管理员登录 * 2.题库管理>方案管理，新增方案，并给创建的应该添加方案 * 3.在教学大纲下添加大纲 * 4.为添加的教学大纲添加方案 * 5.迁移数据 * */ DbUtil d = new DbUtil(); String tfc_id="";//运管ID String school_id = "";//驾校在Oracle库中school_id String dept_id = ""; String zcode="022013"; String zid = "511000";//地区编码 String ay_id = "51100009";//驾校ay_id,用逗号分开 String schoolName ="城客驾培培训站"; //驾校名字,用逗号分开 String[] ay_idArray = ay_id.split(","); String[] schoolNameArray = schoolName.split(","); int ayid_length = ay_idArray.length; // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "BASE_STD_ARCHIVES", // //"SELECT ' ' as ID, '"+ay_idArray[i]+"' as AY_ID , STU.NAME, STU.SEX, '身份证' CREDENTIALS_TYPE, STU.SFZ_NO CREDENTIALS_NO, STU.BIRTHDAY, STU.SFZ_ADDRESS ADDRESS, NULL NOW_ADDRESS, NULL LANDLINE, STU.TEL_MOV PHONE, NULL ZIP_CODE, STU.PIC, NULL VEHICLE_TYPE, NULL STATE, NULL REMARK, NULL CREATE_SCHOOL_ID, NULL CREATE_SCHOOL_NAME, NULL CREATE_USER_ID, NULL CREATE_USER_NAME, NULL CREATE_TIME, NULL MODIFY_USER_ID, NULL MODIFY_USER_NAME, NULL MODIFY_TIME, NULL NATIONALITY, STU.NATION_TYPE NATION, NULL SOURCE, '"+ zid +"' as ZID, NULL USER_ID FROM STUDENT STU left join school sch on sch.school_id=stu.scl_unit_id where sch.name = '"+schoolNameArray[i]+"'", // "SELECT ' ' as ID, NULL , STU.NAME, STU.SEX, '身份证' CREDENTIALS_TYPE, STU.SFZ_NO CREDENTIALS_NO, STU.BIRTHDAY, STU.SFZ_ADDRESS ADDRESS, NULL NOW_ADDRESS, NULL LANDLINE, STU.TEL_MOV PHONE, NULL ZIP_CODE, STU.PIC, NULL VEHICLE_TYPE, NULL STATE, STU.STDT_ID REMARK, NULL CREATE_SCHOOL_ID, NULL CREATE_SCHOOL_NAME, NULL CREATE_USER_ID, NULL CREATE_USER_NAME, NULL CREATE_TIME, NULL MODIFY_USER_ID, NULL MODIFY_USER_NAME, NULL MODIFY_TIME, NULL NATIONALITY, STU.NATION_TYPE NATION, NULL SOURCE, '"+ zid +"' as ZID, NULL USER_ID FROM STUDENT STU left join (select max(us_id) us_id,stdt_id from user_student group by stdt_id) usm on stu.stdt_id = usm.stdt_id left join user_student us on us.us_id = usm.us_id" // + " left join users u on us.create_uid = u.\"UID\" left join school sch on sch.school_id = u.unit_id where sch.ay_id = '"+ay_id+"' ", // "INSERT INTO BASE_STD_ARCHIVES ( ID, AY_ID, NAME, SEX, CREDENTIALS_TYPE, CREDENTIALS_NO, BIRTHDAY, ADDRESS, NOW_ADDRESS, LANDLINE, PHONE, ZIP_CODE, PIC, VEHICLE_TYPE, STATE, REMARK, CREATE_SCHOOL_ID, CREATE_SCHOOL_NAME, CREATE_USER_ID, CREATE_USER_NAME, CREATE_TIME, MODIFY_USER_ID, MODIFY_USER_NAME, MODIFY_TIME, NATIONALITY, NATION, SOURCE, ZID, USER_ID )", // "delete from BASE_STD_ARCHIVES where id in (select stdt_id from tr_period_jxjy where ay_id = '"+ay_id+"')"); // d.update(conn_ppas_ayjxjy,"update BASE_STD_ARCHIVES set pic = null where pic = '^' and ZID = '"+zid+"';"+ // "update BASE_STD_ARCHIVES set sex = null where sex = '^' and ZID = '"+zid+"';"+ // "update BASE_STD_ARCHIVES set address = null where address = '^' and ZID = '"+zid+"';"+ // "update BASE_STD_ARCHIVES set NATION = null where NATION = '^' and ZID = '"+zid+"';" + // "update BASE_STD_ARCHIVES SET PIC = REPLACE(PIC ,'sites/default/files/bm/','/static/upload/stdSfzPhoto/') where pic is not null and ZID = '"+zid+"' "); // d.update(conn_ppas_ayjxjy, "update BASE_STD_ARCHIVES a set a.user_id=b.id from sys_user b where a.CREDENTIALS_NO=b.sfz_no and a.ZID = '"+zid+"' "); //------------------------------------------------------------RES_QUESTIONS 题库表---------------------------------------------------------------------// //SYS_ZONE_TEACHPROJECT 地区题目大纲表 RES_TEACH_PROJECT 题库标题信息 // ResultSet resultSet = d.query(conn_ppas_ayjxjy, "SELECT RTP.id, case RTP.type when 'ky' then '客运' when 'wy' then '危运' when 'czc' then '出租车' when 'hy' then '货运' end FROM SYS_ZONE_TEACHPROJECT SZT LEFT JOIN RES_TEACH_PROJECT RTP ON SZT.TID=RTP.ID WHERE 1=1 AND SZT.TFC_ID='"+tfc_id+"'"); // while(resultSet.next()){ // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "RES_QUESTIONS", // "SELECT ' ' as id , QQ.CONTENT, QQ.ANSWER_CONTENT, QQ.ANSWER, QQ.IMAGE_URL, QQ.TYPE, QQ.IS_USE, '"+ resultSet.getString("id") +"' TID, SYSDATE, NULL, '"+zid+"' as ZID FROM TEACH_PROJECT TP left join questions qq on tp.tid=qq.tid WHERE 1=1 AND QQ.IS_USE = 1 AND TP.MODEL='jxjy' and TP.type='"+ resultSet.getString("case") +"'", // "INSERT INTO RES_QUESTIONS ( ID, CONTENT, ANSWER_CONTENT, ANSWER, IMAGE_URL, TYPE, IS_USE, TID, CREATE_TIME, CREATE_UID, ZID ) ", // "delete from RES_QUESTIONS where zid = '"+zid+"'"); // } // // d.update(conn_ppas_ayjxjy, "update RES_QUESTIONS set ANSWER_CONTENT=substr(ANSWER_CONTENT, 1, length(ANSWER_CONTENT)-1) where substr(ANSWER_CONTENT, length(ANSWER_CONTENT), 1)=';' and zid='"+zid+"';" // + "update RES_QUESTIONS set ANSWER_CONTENT=substr(ANSWER_CONTENT, 1, length(ANSWER_CONTENT)-3) where substr(ANSWER_CONTENT, length(ANSWER_CONTENT)-2, 3)='; ' and zid='"+zid+"';" // + "update RES_QUESTIONS set ANSWER_CONTENT=substr(ANSWER_CONTENT, 1, length(ANSWER_CONTENT)-1) where ascii(substr(ANSWER_CONTENT, length(ANSWER_CONTENT), 1))=10 and zid='"+zid+"';" // + "update RES_QUESTIONS set ANSWER_CONTENT= SUBSTR(ANSWER_CONTENT, 2, LENGTH(ANSWER_CONTENT)-1) where ascii(substr(ANSWER_CONTENT, 1, 1))=10 and zid='"+zid+"';" // + "update RES_QUESTIONS set ANSWER= SUBSTR(ANSWER, 1, LENGTH(ANSWER)-1) where ascii(substr(ANSWER, LENGTH(ANSWER), 1))=10 and zid='"+zid+"';" // + "update RES_QUESTIONS set ANSWER= SUBSTR(ANSWER, 2, LENGTH(ANSWER)-1) where ascii(substr(ANSWER, 1, 1))=10 and zid='"+zid+"';" // + "update RES_QUESTIONS set ANSWER=substr(ANSWER, 1, length(ANSWER)-1) where substr(ANSWER, length(ANSWER), 1)=' ' and zid='"+zid+"';" // + "update RES_QUESTIONS set ANSWER_CONTENT=substr(ANSWER_CONTENT, 1, length(ANSWER_CONTENT)-1) where substr(ANSWER_CONTENT, length(ANSWER_CONTENT), 1)=' ' and zid='"+zid+"';" // + "update RES_QUESTIONS set ANSWER_CONTENT=substr(ANSWER_CONTENT, 1, length(ANSWER_CONTENT)-1) where substr(ANSWER_CONTENT, length(ANSWER_CONTENT), 1)=';' and zid='"+zid+"';" // + "update RES_QUESTIONS set image_url=null where image_url ='^' and zid='"+zid+"';" // + "UPDATE RES_QUESTIONS SET IMAGE_URL=REPLACE(IMAGE_URL,'/sites/default/files','/static/upload/QImages') WHERE IMAGE_URL IS NOT NULL and zid='"+zid+"';"); //-----------------------------------------------------------------END---------------------------------------------------------------------// // //-------------------------------------------------------------SYS_CARD_BOX 学习卡盒 ---------------------------------------------------------------------// // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "SYS_CARD_BOX", // "SELECT ' ' as id, BOX_NO, STATE, BATCH_NO, CARD_CODE_BEG, CARD_CODE_END, CARD_COUNT, \"UID\", CREATE_DATE, '"+zid+"' as ZID, (select name from ZONE t where num ='"+zid+"') as ZNAME FROM CARD_BOX where 1=1 ", // "INSERT INTO SYS_CARD_BOX ( ID, BOX_NO, STATE, BATCH_NO, CARD_CODE_BEG, CARD_CODE_END, CARD_COUNT, USER_ID, CREATE_DATE, ZID, ZNAME ) ", // "delete from SYS_CARD_BOX where zid = '"+zid+"'"); // //-----------------------------------------------------------------END---------------------------------------------------------------------// // // //-------------------------------------------------------------SYS_CARD 学习卡 ---------------------------------------------------------------------// // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "SYS_CARD", // "SELECT ' ' as id, CARD_CODE, PASSWORD, STATE, VALID_TIME, ACTIVE_DATE, ACTIVE_DATE, OVER_DATE, CARD_TYPE, BOX_NO, '"+zid+"' as FROM CARD where 1=1 ", // "INSERT INTO SYS_CARD ( ID, CARD_CODE, PASSWORD, STATE, VALID_TIME, BIND_TIME, ACTIVE_DATE, OVER_DATE, CARD_TYPE, BOX_NO, ZID ) ", // "delete from SYS_CARD where zid = '"+zid+"'"); //// //-----------------------------------------------------------------END---------------------------------------------------------------------// // for(int i = 0; i< ayid_length; i++){ //获得驾校tfc_id ResultSet rs = d.query(conn_ppas_ayjxjy, "select tfc_id from sys_school where ay_id = '"+ay_idArray[i]+"'"); if(rs.getRow()>1){ throw new Exception("AYID:'"+ay_idArray[i]+"'有多条记录！"); }else{ rs.next(); tfc_id = rs.getString("tfc_id"); } d.update(conn_oracle_ayjxjy, "update school set ay_id='"+ ay_idArray[i] +"',school_type = 'new' where name = '"+schoolNameArray[i]+"'");//根据新建的驾校的单位编码（ay_id），修改原始数据school表中的ay_id字段 //获得驾校school_id ResultSet rs1 = d.query(conn_oracle_ayjxjy, "select school_id from school where ay_id = '"+ay_idArray[i]+"'"); if(rs1.getRow()>1){ throw new Exception("AYID:'"+ay_idArray[i]+"'有多条记录！"); }else{ rs1.next(); school_id = rs1.getString("school_id"); } //------------------------------------------------------------BASE_STD_ARCHIVES 学员基础档案信息---------------------------------------------------------------------// // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "BASE_STD_ARCHIVES", // //"SELECT ' ' as ID, '"+ay_idArray[i]+"' as AY_ID , STU.NAME, STU.SEX, '身份证' CREDENTIALS_TYPE, STU.SFZ_NO CREDENTIALS_NO, STU.BIRTHDAY, STU.SFZ_ADDRESS ADDRESS, NULL NOW_ADDRESS, NULL LANDLINE, STU.TEL_MOV PHONE, NULL ZIP_CODE, STU.PIC, NULL VEHICLE_TYPE, NULL STATE, NULL REMARK, NULL CREATE_SCHOOL_ID, NULL CREATE_SCHOOL_NAME, NULL CREATE_USER_ID, NULL CREATE_USER_NAME, NULL CREATE_TIME, NULL MODIFY_USER_ID, NULL MODIFY_USER_NAME, NULL MODIFY_TIME, NULL NATIONALITY, STU.NATION_TYPE NATION, NULL SOURCE, '"+ zid +"' as ZID, NULL USER_ID FROM STUDENT STU left join school sch on sch.school_id=stu.scl_unit_id where sch.name = '"+schoolNameArray[i]+"'", // "SELECT ' ' as ID, NULL , STU.NAME, STU.SEX, '身份证' CREDENTIALS_TYPE, STU.SFZ_NO CREDENTIALS_NO, STU.BIRTHDAY, STU.SFZ_ADDRESS ADDRESS, NULL NOW_ADDRESS, NULL LANDLINE, STU.TEL_MOV PHONE, NULL ZIP_CODE, STU.PIC, NULL VEHICLE_TYPE, NULL STATE, STU.STDT_ID REMARK, NULL CREATE_SCHOOL_ID, NULL CREATE_SCHOOL_NAME, NULL CREATE_USER_ID, NULL CREATE_USER_NAME, NULL CREATE_TIME, NULL MODIFY_USER_ID, NULL MODIFY_USER_NAME, NULL MODIFY_TIME, NULL NATIONALITY, STU.NATION_TYPE NATION, NULL SOURCE, '"+ zid +"' as ZID, NULL USER_ID FROM STUDENT STU left join (select max(us_id) us_id,stdt_id from user_student group by stdt_id) usm on stu.stdt_id = usm.stdt_id left join user_student us on us.us_id = usm.us_id" // + " left join users u on us.create_uid = u.\"UID\" left join school sch on sch.school_id = u.unit_id where sch.ay_id = '"+ay_idArray[i]+"' ", // "INSERT INTO BASE_STD_ARCHIVES ( ID, AY_ID, NAME, SEX, CREDENTIALS_TYPE, CREDENTIALS_NO, BIRTHDAY, ADDRESS, NOW_ADDRESS, LANDLINE, PHONE, ZIP_CODE, PIC, VEHICLE_TYPE, STATE, REMARK, CREATE_SCHOOL_ID, CREATE_SCHOOL_NAME, CREATE_USER_ID, CREATE_USER_NAME, CREATE_TIME, MODIFY_USER_ID, MODIFY_USER_NAME, MODIFY_TIME, NATIONALITY, NATION, SOURCE, ZID, USER_ID )", // "delete from BASE_STD_ARCHIVES where id in (select stdt_id from tr_period_jxjy where ay_id = '"+ay_idArray[i]+"')"); // d.update(conn_ppas_ayjxjy,"update BASE_STD_ARCHIVES set pic = null where pic = '^' and ZID = '"+zid+"';"+ // "update BASE_STD_ARCHIVES set sex = null where sex = '^' and ZID = '"+zid+"';"+ // "update BASE_STD_ARCHIVES set address = null where address = '^' and ZID = '"+zid+"';"+ // "update BASE_STD_ARCHIVES set NATION = null where NATION = '^' and ZID = '"+zid+"';" + // "update BASE_STD_ARCHIVES SET PIC = REPLACE(PIC ,'sites/default/files/bm/','/static/upload/stdSfzPhoto/') where pic is not null and ZID = '"+zid+"' "); // d.update(conn_ppas_ayjxjy, "update BASE_STD_ARCHIVES a set a.user_id=b.id from sys_user b where a.CREDENTIALS_NO=b.sfz_no and a.ZID = '"+zid+"' "); //-----------------------------------------------------------------END---------------------------------------------------------------------// //------------------------------------------------------------SYS_USER 用户表---------------------------------------------------------------------// // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "SYS_USER", // "select '' as id,u.pass as password,u.name as username,null,null,null,st.sfz_no,st.tel_mov,null,null,'normal',null,null,0,0,0,0 FROM student st left join (select max(us_id) us_id,stdt_id from user_student group by stdt_id) usm on st.stdt_id = usm.stdt_id left join user_student us on usm.us_id = us.us_id left join users u on us.\"UID\" = u.\"UID\" where u.unit_id = "+school_id, // "INSERT INTO SYS_USER ( ID, PASSWORD, USERNAME, BIRTHDAY, NAME, ORGANIZATION_ID, SFZ_NO, PHONE, MAIL, CREATE_USER_ID,STATUS, CREATE_USER_NAME, MODIFY_USER_NAME, MODIFY_USER_ID, ISDELETED, BUILDIN, TYPE ) ", // "delete from sys_user where sfz_no in (select bsa.credentials_no from BASE_STD_ARCHIVES bsa,tr_period_jxjy tp where bsa.id = tp.stdt_id and tp.ay_id = '"+ay_idArray[i]+"') " ); // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "SYS_USER", // "select '' as id,a.pass as password,a.name as username,null,a.\"UID\",null,c.sfz_no,c.tel_mov,null,null,'normal',null,null,0,0,0,0 from USERS a,user_student b,student c where a.\"UID\"=b.\"UID\" and b.stdt_id=c.stdt_id AND b.stdt_id in ( select stdt_id from user_student group by stdt_id having count(1)>1) ", // "INSERT INTO SYS_USER_1 ( ID, PASSWORD, USERNAME, BIRTHDAY, NAME, ORGANIZATION_ID, SFZ_NO, PHONE, MAIL, CREATE_USER_ID,STATUS, CREATE_USER_NAME, MODIFY_USER_NAME, MODIFY_USER_ID, ISDELETED, BUILDIN, TYPE ) ", // "delete from sys_user_1" ); // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "TR_PERIOD_JXJY", // "SELECT ' ' AS ID, '"+ay_idArray[i]+"' as AY_ID, HUBEISTU.STDT_ID, HUBEIUS.auto_code as REMARK, SU.\"UID\" CREATE_USER_ID, SU.NAME CREATE_USER_NAME, UNIX_TO_ORACLE(HUBEIUS.CREATE_TIME) CREATE_TIME, HUBEISTU.STDT_ID MODIFY_USER_ID, HUBEIUS.\"UID\" MODIFY_USER_NAME, NULL MODIFY_TIME, CASE WHEN HUBEIUS.AUD_TIME > 0 THEN UNIX_TO_ORACLE(HUBEIUS.AUD_TIME) ELSE NULL END AUD_TIME, case when HUBEIUS.aud_time > 0 then su.\"UID\" else null end AUD_UID, NULL AUD_REMARK, CASE WHEN HUBEIUS.over_time > 0 THEN UNIX_TO_ORACLE(HUBEIUS.over_time) ELSE NULL END OVER_TIME, CASE WHEN HUBEIUS.over_time > 0 THEN su.\"UID\" ELSE NULL END OVER_UID, HUBEIUS.\"UID\" OVER_REMARK, null EXAM_TIME, NULL EXAM_UID, hubeic.card_code EXAM_REMARK, HUBEIUS.pic_one PICTURE, '"+tfc_id+"' AS TFC_ID, UNIX_TO_ORACLE(HUBEIUS.CREATE_TIME) ENTRY_DATE, HUBEIPTN.batch_no GRADE, CASE WHEN HUBEIUS.state = '00' THEN '00' WHEN HUBEIUS.state = '10' THEN '00' WHEN HUBEIUS.STATE = '20' THEN '10' WHEN HUBEIUS.state = '30' THEN '20' END STATE, '1' PAY_STATE FROM STUDENT HUBEISTU JOIN USER_STUDENT HUBEIUS ON HUBEIUS.STDT_ID = HUBEISTU.STDT_ID LEFT JOIN PRINT_TRAIN_NOTICE HUBEIPTN ON HUBEIPTN.\"UID\" = HUBEIUS.\"UID\" LEFT JOIN USER_CARD HUBEIUC ON HUBEIUC.\"UID\" = HUBEIUS.\"UID\" LEFT JOIN card hubeic ON hubeic.c_id = HUBEIUC.c_id LEFT JOIN USERS SU ON HUBEIUS.CREATE_UID = SU.\"UID\" LEFT JOIN SCHOOL SS ON SU.UNIT_ID = SS.SCHOOL_ID where SU.unit_id = "+school_id, // "INSERT INTO TR_PERIOD_JXJY ( ID, AY_ID, STDT_ID, REMARK, CREATE_USER_ID, CREATE_USER_NAME, CREATE_TIME, MODIFY_USER_ID, MODIFY_USER_NAME, MODIFY_TIME, AUD_TIME, AUD_UID, AUD_REMARK, OVER_TIME, OVER_UID, OVER_REMARK, EXAM_TIME, EXAM_UID, EXAM_REMARK, PICTURE, TFC_ID, ENTRY_DATE, GRADE, STATE, PAY_STATE ) ", // "delete from TR_PERIOD_JXJY where AY_ID = '"+ay_idArray[i]+"'");//TR_PERIOD_JXJY的MODIFY_USER_ID保存了ORACLE中STUDENT表的STDT_ID,MODIFY_USER_NAME保存了USER_STUDENT表中的UID // // //用TR_PERIOD_JXJY的STDT_ID与BASE_STD_ARCHIVES中的REMARK字段进行关联，并将STDT_ID更新为BASE_STD_ARCHIVES的ID // d.update(conn_ppas_ayjxjy, "UPDATE TR_PERIOD_JXJY TP SET TP.STDT_ID = BSA.ID FROM BASE_STD_ARCHIVES BSA WHERE TP.STDT_ID = BSA.REMARK AND BSA.ZID = '"+zid+"'"); // // d.update(conn_ppas_ayjxjy, "UPDATE TR_PERIOD_JXJY TP SET TP.CREATE_USER_ID = SU.ID FROM SYS_USER SU WHERE TP.CREATE_USER_NAME = SU.USERNAME AND TP.AY_ID = '"+ay_idArray[i]+"'"); // // d.update(conn_ppas_ayjxjy, "UPDATE TR_PERIOD_JXJY SET PICTURE = REPLACE(PICTURE ,'/sites/default/files/bm/','/static/upload/stdBmPhoto/') WHERE PICTURE IS NOT NULL and AY_ID = '"+ay_idArray[i]+"'"); // //-----------------------------------------------------------------END---------------------------------------------------------------------// // // //------------------------------------------------------------SYS_USER_PAY 绑卡记录---------------------------------------------------------------------// // d.insert3(conn_ppas_ayjxjy, "SYS_USER_PAY", //user_card // "SELECT ' ' as id, TRJ.id as TP_ID, 'card' as pay_type,sc.id as pay_id FROM TR_PERIOD_JXJY TRJ left join sys_card sc on SC.CARD_CODE=TRJ.EXAM_REMARK where trj.ay_id = '"+ ay_idArray[i] +"' and sc.zid = '"+zid+"'", // "INSERT INTO SYS_USER_PAY ( ID, TP_ID, PAY_TYPE, PAY_ID )", // "delete from SYS_USER_PAY where tp_id in (select id from TR_PERIOD_JXJY where AY_ID = '"+ay_idArray[i]+"')"); // //-----------------------------------------------------------------END---------------------------------------------------------------------// // // //------------------------------------------------------------BASE_STD_EXTEND 学员档案扩展表---------------------------------------------------------------------// // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "BASE_STD_EXTEND", // "SELECT ' 'as ID, HUBEISTU.STDT_ID, NULL, HUBEIUS.CASE_TYPE, HUBEIUS.CASE_TYPE, HUBEIUS.CASE_NO, UNIX_TO_ORACLE(HUBEIUS.CREATE_TIME) CREATE_TIME, UNIX_TO_ORACLE(HUBEIUS.CREATE_TIME) CHANGE_TIME, NULL, UNIX_TO_ORACLE(HUBEIUS.GET_TIME) first_TIME FROM STUDENT HUBEISTU LEFT JOIN USER_STUDENT HUBEIUS ON HUBEIUS.STDT_ID = HUBEISTU.STDT_ID LEFT JOIN USERS SU ON HUBEIUS.CREATE_UID = SU.\"UID\" LEFT JOIN SCHOOL SS ON SU.UNIT_ID = SS.SCHOOL_ID WHERE HUBEIUS.us_id IS NOT NULL AND SS.AY_ID= '"+ ay_idArray[i] +"'", // "INSERT INTO BASE_STD_EXTEND ( ID, STDT_ID, CASE_CODE, CASE_NAME, CASE_TYPE, CASE_NO, CREATE_TIME, CHANGE_TIME, LATEST_OVER_TIME, FIRST_TIME ) ", // "delete from BASE_STD_EXTEND where STDT_ID in (select stdt_id from tr_period_jxjy where ay_id = '"+ay_idArray[i]+"')");//BASE_STD_EXTEND中的STDT_ID字段保存了ORACLE中STUDENT表的STDT_ID //用BASE_STD_EXTEND的STDT_ID与BASE_STD_ARCHIVES中的REMARK字段进行关联，并将STDT_ID更新为BASE_STD_ARCHIVES的ID // d.update(conn_ppas_ayjxjy, "UPDATE BASE_STD_EXTEND BSE SET BSE.STDT_ID = BSA.ID FROM BASE_STD_ARCHIVES BSA WHERE BSE.STDT_ID = BSA.REMARK AND BSA.ZID = '"+zid+"'"); //-----------------------------------------------------------------END---------------------------------------------------------------------// //------------------------------------------------------------TR_CASE_TYPE_JXJY 学员培训类型 ---------------------------------------------------------------------// // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "TR_CASE_TYPE_JXJY", // "SELECT ' ' as id, HUBEIUS.STDT_ID, null, HUBEIUS.CASE_TYPE, HUBEIUS.CASE_NO, HUBEIUS.\"UID\", UNIX_TO_ORACLE(HUBEIUS.GET_TIME), NULL, NULL, HUBEIUS.\"UID\", NULL, HUBEIUS.CASE_TYPE, NULL, '1', CASE WHEN HUBEIUS.state = '00' THEN '00' WHEN HUBEIUS.STATE = '10' THEN '10' WHEN HUBEIUS.STATE = '20' THEN '20' WHEN HUBEIUS.STATE = '30' THEN '20' END STATUS FROM USER_STUDENT HUBEIUS LEFT JOIN STUDENT ST ON HUBEIUS.STDT_ID = ST.STDT_ID LEFT JOIN USERS U ON HUBEIUS.\"UID\" = U.\"UID\" LEFT JOIN SCHOOL SCH ON U.UNIT_ID = SCH.SCHOOL_ID WHERE SCH.AY_ID = '"+ay_idArray[i]+"'", // "INSERT INTO TR_CASE_TYPE_JXJY ( ID, TP_ID, YZ_ID, CASE_NAME, CASE_NO, SFZ_NO, FIRST_TIME, EFFECT_BEGIN_TIME, EFFECT_END_TIME, DATA_VER, CASE_CODE, CASE_TYPE, UPLOAD_FLAG, IS_NEED, STATUS )", // "delete from TR_CASE_TYPE_JXJY where tp_id in (select id from tr_period_jxjy where ay_id ='"+ay_idArray[i]+"') ");//TR_CASE_TYPE_JXJY中的TP_ID字段保存了ORACLE中STUDENT表的STDT_ID,SFZ_NO字段保存了USER_STUDENT表的UID // d.update(conn_ppas_ayjxjy, "UPDATE TR_CASE_TYPE_JXJY TCT SET TCT.TP_ID = TP.ID FROM TR_PERIOD_JXJY TP WHERE TCT.SFZ_NO = TP.MODIFY_USER_NAME AND TP.AY_ID = '"+ay_idArray[i]+"'");//用TR_CASE_TYPE_JXJY的SFZ_NO与TR_PERIOD_JXJY中的MODIFY_USER_NAME字段进行关联，并将TP_ID更新为TR_PERIOD_JXJY的ID // d.update(conn_ppas_ayjxjy, "UPDATE TR_CASE_TYPE_JXJY tct SET tct.CASE_CODE = (SELECT SD.CODE FROM SYS_DICT SD WHERE NAME=tct.CASE_TYPE) where tp_id in (select id from tr_period_jxjy where ay_id ='"+ay_idArray[i]+"') "); // //-----------------------------------------------------------------END---------------------------------------------------------------------// //----------------------------------------------TR_STUDYTIMETOTAL_JXJY 学时总表---------------------------------------------------------------------// // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "TR_STUDYTIMETOTAL_JXJY", // "SELECT ' ' as id, HUBEIUS.\"UID\", HUBEITT.KM1, NULL, NULL, UNIX_TO_ORACLE(HUBEITT.CREATED), UNIX_TO_ORACLE(HUBEITT.CHANGED), '"+tfc_id+"' as TFC_ID, NULL, null, NULL, HUBEIUS.CASE_TYPE, 18,0 FROM USER_STUDENT HUBEIUS LEFT JOIN STUDYTIMETOTAL HUBEITT ON HUBEITT.STDT_ID = HUBEIUS.STDT_ID LEFT JOIN USERS U ON HUBEIUS.\"UID\" = U.\"UID\" LEFT JOIN SCHOOL SCH ON U.UNIT_ID = SCH.SCHOOL_ID WHERE HUBEITT.USER_ID = HUBEIUS.\"UID\" AND SCH.AY_ID = '"+ay_idArray[i] +"'", // "INSERT INTO TR_STUDYTIMETOTAL_JXJY ( ID, TCT_ID, KM1, KM2, KM3, CREATED, CHANGED, ZID, AUD_KM1, AUD_KM2, AUD_KM3, CUR_KM, NEED_TIME,TODAY_MINUTE ) ", // "delete from TR_STUDYTIMETOTAL_JXJY where tct_id in (select tct.id from tr_case_type_jxjy tct join tr_period_jxjy tpj on tct.tp_id = tpj.id where tpj.ay_id = '"+ay_idArray[i]+"')");//TR_STUDYTIMETOTAL_JXJY的tct_id字段保存ORACLE的UID // d.update(conn_ppas_ayjxjy, "UPDATE TR_STUDYTIMETOTAL_JXJY TT SET TT.TCT_ID = TCT.ID FROM TR_CASE_TYPE_JXJY TCT WHERE TCT.SFZ_NO = TT.TCT_ID AND TT.ZID = "+zid);//用TR_STUDYTIMETOTAL_JXJY的TCT_ID与TR_CASE_TYPE_JXJY中的SFZ_NO字段进行关联，并将TCT_ID更新为TR_CASE_TYPE_JXJY的ID // // d.update(conn_ppas_ayjxjy, "UPDATE TR_STUDYTIMETOTAL_JXJY TSJ SET TSJ.CUR_KM= (SELECT SC.NEED_TIME from SYS_CASES SC where SC.CASE_TYPE = TSJ.CUR_KM and SC.tfc_id = '"+tfc_id+"') where TSJ.ZID = '"+zid+"';" // + "UPDATE TR_STUDYTIMETOTAL_JXJY SET CUR_KM=round(km1/(CUR_KM*60)*100,0) where tct_id in (select tct.id from tr_case_type_jxjy tct,tr_period_jxjy tp where tct.tp_id = tp.id and tp.ay_id = '"+ay_idArray[i]+"'); " // + "UPDATE TR_STUDYTIMETOTAL_JXJY SET CUR_KM='100' where to_number(CUR_KM)>100 and tct_id in (select tct.id from tr_case_type_jxjy tct,tr_period_jxjy tp where tct.tp_id = tp.id and tp.ay_id = '"+ay_idArray[i]+"');"); //-----------------------------------------------------------------END---------------------------------------------------------------------// //------------------------------------------------------------TR_STUDYTIME_JXJY 学时明细表---------------------------------------------------------------------// // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "TR_STUDYTIME_JXJY", // "SELECT' ' as id, '"+tfc_id+"' as tfc_id, '"+ay_idArray[i]+"', us.stdt_id, us.\"UID\", null, NULL, NULL, null, null, HUBEIST.BEG_DAY, HUBEIST.BEG_MINUTE, UNIX_TO_ORACLE(HUBEIST.BEG_TIME), UNIX_TO_ORACLE(HUBEIST.END_TIME), null, NULL, HUBEIST.TIME_TYPE, NULL, HUBEIST.TR_MINUTE, 'OLD_WEBSITE_1', HUBEIST.DATA_FROM_INFO, UNIX_TO_ORACLE(HUBEIST.COLLECT_DATE), HUBEIST.state, null, NULL, NULL, NULL, NULL, NULL, NULL, null FROM USER_STUDENT us LEFT JOIN studytime HUBEIST ON us.\"UID\" = HUBEIST.user_id LEFT JOIN USERS U ON US.\"UID\" = U.\"UID\" LEFT JOIN SCHOOL SCH ON U.UNIT_ID = SCH.SCHOOL_ID WHERE us.state != '20' AND us.state != '30' AND SCH.AY_ID ='"+ay_idArray[i]+"'", // "INSERT INTO TR_STUDYTIME_JXJY ( ID, TFC_ID, AY_ID, TP_ID, TCT_ID, COACH_ID, STDT_ID, COACH_ICNO, STDT_ICNO, FIELD_CODE, BEG_DAY, BEG_MINUTE, BEG_TIME, END_TIME, PH, KM, TIME_TYPE, IS_SIM, TR_MINUTE, DATA_FROM, DATA_FROM_INFO, COLLECT_DATE, STATE, USER_ID, ZID, CASE_TYPE, CASE_CODE, AUD_TIME, AUD_PASS, AUD_REMARK, CD_ID ) ", //// "delete from TR_STUDYTIME_JXJY where tp_id in (select id from tr_period_jxjy where ay_id = '"+ay_idArray[i]+"')");//TR_STUDYTIME_JXJY的TP_ID保存STDT_ID，TCT_ID保存UID //// d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "TR_STUDYTIME_JXJY", //// "SELECT' ' as id, '"+tfc_id+"' as tfc_id, '"+ay_idArray[i]+"', us.stdt_id, us.\"UID\", null, NULL, NULL, null, null, HUBEIST.BEG_DAY, HUBEIST.BEG_MINUTE, UNIX_TO_ORACLE(HUBEIST.BEG_TIME), UNIX_TO_ORACLE(HUBEIST.END_TIME), null, NULL, HUBEIST.TIME_TYPE, NULL, HUBEIST.TR_MINUTE, 'OLD_WEBSITE', HUBEIST.DATA_FROM_INFO, UNIX_TO_ORACLE(HUBEIST.COLLECT_DATE), HUBEIST.state, null, NULL, NULL, NULL, NULL, NULL, NULL, null FROM USER_STUDENT us LEFT JOIN studytime HUBEIST ON us.\"UID\" = HUBEIST.user_id LEFT JOIN USERS U ON US.\"UID\" = U.\"UID\" LEFT JOIN SCHOOL SCH ON U.UNIT_ID = SCH.SCHOOL_ID WHERE us.state != '20' AND us.state != '30' AND us.\"UID\" = 247017 ", //// "INSERT INTO TR_STUDYTIME_JXJY ( ID, TFC_ID, AY_ID, TP_ID, TCT_ID, COACH_ID, STDT_ID, COACH_ICNO, STDT_ICNO, FIELD_CODE, BEG_DAY, BEG_MINUTE, BEG_TIME, END_TIME, PH, KM, TIME_TYPE, IS_SIM, TR_MINUTE, DATA_FROM, DATA_FROM_INFO, COLLECT_DATE, STATE, USER_ID, ZID, CASE_TYPE, CASE_CODE, AUD_TIME, AUD_PASS, AUD_REMARK, CD_ID ) ", ////// "delete from TR_STUDYTIME_JXJY where tp_id in (select id from tr_period_jxjy where ay_id = '"+ay_idArray[i]+"')");//TR_STUDYTIME_JXJY的TP_ID保存STDT_ID，TCT_ID保存UID // ""); // d.update(conn_ppas_ayjxjy, "UPDATE TR_STUDYTIME_JXJY stt SET stt.TCT_ID = TCT.ID,stt.TP_ID = TP.ID FROM TR_CASE_TYPE_JXJY TCT,TR_PERIOD_JXJY TP WHERE TCT.SFZ_NO = STT.TCT_ID AND STT.TP_ID = TP.MODIFY_USER_ID AND TP.ay_id = '"+ay_idArray[i]+"'");//用TR_STUDYTIME_JXJY的TCT_ID与TR_CASE_TYPE_JXJY中的SFZ_NO字段进行关联，并将TCT_ID更新为TR_CASE_TYPE_JXJY的ID;用TR_STUDYTIME_JXJY的TP_ID与TR_PERIOD_JXJY中的MODIFY_USER_ID字段进行关联，并将TP_ID更新为TR_PERIOD_JXJY的ID // //-----------------------------------------------------------------END---------------------------------------------------------------------// // // //------------------------------------------------------------TR_STD_EXAM_JXJY 考试记录---------------------------------------------------------------------// // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "TR_STD_EXAM_JXJY", // "SELECT ' ' as id, '"+ay_idArray[i]+"', HUBEIUS.\"UID\", HUBEIUS.CASE_NO, '1' STATUS, ST.SFZ_NO, ST.NAME, NULL, HUBEITE.SCORE, UNIX_TO_ORACLE(HUBEITE.CREATE_TIME), CASE WHEN HUBEITE.STATE = '合格' THEN '1' WHEN HUBEITE.STATE = '不合格' THEN '0' else null end, NULL, NULL, NULL, NULL, NULL, UNIX_TO_ORACLE(HUBEITE.CREATE_TIME), NULL, NULL, NULL, HUBEIUS.CASE_TYPE, null FROM USER_STUDENT HUBEIUS LEFT JOIN STUDENT ST ON HUBEIUS.STDT_ID = ST.STDT_ID LEFT JOIN (SELECT TE.*, RANK() OVER(PARTITION BY TE.\"UID\" ORDER BY TE.CREATE_TIME DESC) MM FROM TRAIN_EXAM TE ) HUBEITE ON HUBEITE.\"UID\" = HUBEIUS.\"UID\" LEFT JOIN USERS U ON HUBEIUS.\"UID\" = U.\"UID\" LEFT JOIN SCHOOL SCH ON U.UNIT_ID = SCH.SCHOOL_ID WHERE HUBEITE.MM = 1 AND SCH.AY_ID = '"+ay_idArray[i]+"'", // "INSERT INTO TR_STD_EXAM_JXJY ( ID, AY_ID, TCT_ID, CASE_CODE, STATUS, SFZ_NO, STUDENT_NAME, KM, RESULT, EXAM_TIME, IS_OK, REMARK, CREATE_SCHOOL_ID, CREATE_SCHOOL_NAME, CREATE_USER_ID, CREATE_USER_NAME, CREATE_TIME, MODIFY_USER_ID, MODIFY_USER_NAME, MODIFY_TIME, EXAM_TYPE, CASE_NO ) ", // "delete from TR_STD_EXAM_JXJY where tct_id in (select tct.id from tr_case_type_jxjy tct join tr_period_jxjy tpj on tct.tp_id = tpj.id where tpj.ay_id = '"+ay_idArray[i]+"' )");//TR_STD_EXAM_JXJY的TCT_ID保存了ORACLE中的UID // //关联TR_STD_EXAM_JXJY的TCT_ID与TR_CASE_TYPE_JXJY的SFZ_NO并把TCT_ID更新为TR_CASE_TYPE_JXJY的ID // d.update(conn_ppas_ayjxjy, "UPDATE TR_STD_EXAM_JXJY TSE SET TSE.TCT_ID = TCT.ID FROM TR_CASE_TYPE_JXJY TCT WHERE TSE.TCT_ID = TCT.SFZ_NO AND TSE.AY_ID = '"+ay_idArray[i]+"' "); // //-----------------------------------------------------------------END---------------------------------------------------------------------// // // //-------------------------------------------------------------jqm 考试机器码表 乐山用--------------------------------------------------------------------// //// d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "jqm", //// "select ' ' as id, value, type, created from jqm where 1=1 ", //// "insert into jqm(id, value, type, created) ", //// null); // //-----------------------------------------------------------------END---------------------------------------------------------------------// // // //-------------------------------------------------------------PRINT_TRAIN_NOTICE 打印培训通知单 --------------------------------------------------------------------// // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "PRINT_TRAIN_NOTICE", // "SELECT ' ' AS ID, US.\"UID\", PTN.BATCH_NO, UNIX_TO_ORACLE(PTN.CREATE_TIME), UNIX_TO_ORACLE(PTN.CHANGE_TIME), PTN.ADDRESS, PTN.TEL, PTN.SFZ_NO, PTN.NAME, NULL, NULL, UNIX_TO_ORACLE(PTN.START_TIME), UNIX_TO_ORACLE(PTN.END_TIME), '"+ay_idArray[i]+"' FROM PRINT_TRAIN_NOTICE PTN LEFT JOIN USER_STUDENT US ON PTN.\"UID\" = US.\"UID\" LEFT JOIN USERS U ON US.\"UID\" = U.\"UID\" LEFT JOIN SCHOOL SCH ON U.UNIT_ID = SCH.SCHOOL_ID WHERE SCH.ay_id = '"+ay_idArray[i]+"'", // "INSERT INTO PRINT_TRAIN_NOTICE ( ID, TCT_ID, BATCH_NO, CREATE_TIME, CHANGE_TIME, ADDRESS, TEL, SFZ_NO, NAME, CREATE_USER_ID, CHANGE_USER_ID, START_TIME, END_TIME, AY_ID ) ", // "delete from PRINT_TRAIN_NOTICE WHERE AY_ID='"+ ay_idArray[i] +"'");//PRINT_TRAIN_NOTICE的TCT_ID保存UID // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "sfz_no_add", // "select stu.sfz_no from studytime st left join user_student us on st.user_id = us.\"UID\" left join student stu on stu.stdt_id = us.stdt_id where st.data_from like '%PXZX%' and st.beg_time >1467561600", // "INSERT INTO sfz_no_add (sfz_no) ", // "delete from sfz_no_add ");//PRINT_TRAIN_NOTICE的TCT_ID保存UID // // d.update(conn_ppas_ayjxjy, "UPDATE PRINT_TRAIN_NOTICE PTN SET PTN.TCT_ID = TCT.ID FROM TR_CASE_TYPE_JXJY TCT WHERE PTN.TCT_ID = TCT.SFZ_NO AND PTN.AY_ID = '"+ay_idArray[i]+"' ");//将PRINT_TRAIN_NOTICE的TCT_ID与TR_CASE_TYPE_JXJY的SFZ_NO进行关联并把TCT_ID更新为TR_CASE_TYPE_JXJY的ID // //-----------------------------------------------------------------END---------------------------------------------------------------------// // // //-------------------------------------------------------------tr_printover_jxjy 合格证明打印记录--------------------------------------------------------------------// // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "tr_printover_jxjy", // "SELECT ' ' as id, US.\"UID\", UNIX_TO_ORACLE(PO.CREATE_TIME), PO.NUM FROM PRINT_OVER PO LEFT JOIN USER_STUDENT US ON PO.\"UID\" = US.\"UID\" LEFT JOIN USERS U ON US.\"UID\" = U.\"UID\" LEFT JOIN SCHOOL SCH ON SCH.SCHOOL_ID = U.UNIT_ID WHERE SCH.ay_id = '"+ay_idArray[i]+"' ", // "INSERT INTO tr_printover_jxjy( ID, TCT_ID,CREATETIME,NUM)", // "delete from tr_printover_jxjy WHERE tct_id in (select tct.id from tr_case_type_jxjy tct join tr_period_jxjy tpj on tct.tp_id = tpj.id and tpj.ay_id = '"+ay_idArray[i]+"')"); // // d.update(conn_ppas_ayjxjy, "UPDATE TR_PRINTOVER_JXJY PO SET PO.TCT_ID = TCT.ID FROM TR_CASE_TYPE_JXJY TCT WHERE PO.TCT_ID = TCT.SFZ_NO ");//将TR_PRINTOVER_JXJY的TCT_ID与TR_CASE_TYPE_JXJY的SFZ_NO进行关联并把TCT_ID更新为TR_CASE_TYPE_JXJY的ID // d.update(conn_ppas_ayjxjy, "UPDATE TR_CASE_TYPE_JXJY SET DATA_VER=NULL WHERE TP_ID IN (SELECT ID FROM tr_period_jxjy WHERE ay_id ='"+ay_idArray[i]+"' );"); //// //-----------------------------------------------------------------END---------------------------------------------------------------------// //// ////// //-------------------------------------------------------------stdt_ali_pic 学员阿里云照片路径--------------------------------------------------------------------// d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "STDT_ALI_PIC", "SELECT STDT_ID, USER_ID, PIC, PIC_ONE FROM STDT_ALI_PIC ", "INSERT INTO STDT_ALI_PIC (STDT_ID,USER_ID,PIC,PIC_ONE)", "delete from STDT_ALI_PIC"); //////// d.update(conn_ppas_ayjxjy, "UPDATE BASE_STD_ARCHIVES BSA SET BSA.PIC = SAP.PIC FROM STDT_ALI_PIC SAP WHERE BSA.REMARK = SAP.STDT_ID AND SAP.PIC IS NOT NULL AND ZID = '"+zid+"'");//根据BASE_STD_ARCHIVES的REMARK字段与STDT_ALI_PIC的STDT_ID字段关联更新BASE_STD_ARCHIVES的PICTURE字段 d.update(conn_ppas_ayjxjy, "UPDATE TR_PERIOD_JXJY TP SET TP.PICTURE = SAP.PIC_ONE FROM STDT_ALI_PIC SAP WHERE TP.MODIFY_USER_NAME = SAP.USER_ID AND SAP.PIC_ONE IS NOT NULL AND AY_ID like '5110%'");//根据TR_PERIOD_JXJY的MODIFY_USER_ID字段与STDT_ALI_PIC的STDT_ID字段关联更新TR_PERIOD_JXJY的PICTURE字段 // d.update(conn_ppas_ayjxjy, "UPDATE TR_PERIOD_JXJY TP SET TP.DEPT_ID = DEPT.DEPT_ID FROM SYS_DEPT DEPT WHERE TP.AY_ID = DEPT.AY_ID AND DEPT.AY_ID = '"+ay_idArray[i]+"'"); // d.update(conn_ppas_ayjxjy, "update base_std_archives bsa set bsa.user_id = u.id from sys_user u where u.sfz_no = bsa.credentials_no and bsa.zid = 511000"); // //-----------------------------------------------------------------END---------------------------------------------------------------------// } System.out.println("\n--------------------导库完毕------------------------------"); conn_oracle_ayjxjy.close(); conn_ppas_ayjxjy.close(); } catch (Exception ex) { ex.printStackTrace(); } } }