package ay.zk; import java.sql.Connection; import java.sql.ResultSet; import ay.db.ConnTools; import ay.db.DbUtil; public class Zk_ayjxjy_hn_py { public static void main(String args[]) { /************************************************ 2016.5.25 河南濮阳转库步骤 1. 所有题库图片转阿里云 2. 所有登记照片转阿里云 3. 所有培训记录照片转阿里云 4 濮阳老系统总库转新库 : (a)创建临时表 (b)用户表USERS,用户权限表 ALL_EMPLOYED.USERS_ROLES,地区表 ZONE,学员卡表CARD,学员卡盒表CARD_BOX,制卡索引表 INDEX_TABLE (c)card表只转濮阳激活卡表 5. 濮阳老系统分库转新库 ***********************************************/ 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_zk = ConnTools.make_oracle_ayjxjy_zk_conn(); Connection conn_oracle_ayjxjy = ConnTools.make_oracle_ayjxjy_conn(); Connection conn_ppas_ayjxjy = ConnTools.make_ppas_ayjxjy_conn(); /** select * from school select * from ALL_EMPLOYED.USERS where utype = 'yunguan' select * from ALL_EMPLOYED.USERS where utype = 'school' select * from zone where name like '%乐山%' --511100 乐山运管处 (51110000) 三禾驾校(51110001) ls_employed (1)用ayadmin登录， 组织管理-> 运管管理 创建 51110000 乐山运管处 授用户角色权限 建用户给角色权限 (2)用运管身份登录， 组织管理->驾校管理 51110001 三禾驾校 (3)用ayadmin登录， 给运管挂大纲(教学大纲管理->教学大纲 添加从业类型 , 然后点击一条从业类型记录，点击“详细信息”，若无，点右边“使用已有方案”,选一条已有方案，点击左上角“选择方案“) (4)用ayadmin登录， 给运管挂方案(题库管理->方案管理->右方选择运管->乐山运管处 ->查询 左方选择方案->点击添加方案 ->保存方案) (5)用ayadmin登录， 题库管理->方案管理-> 选择运管 - 查询 - 右边选择方案 - 添加方案 - 保存方案 (6)用运管身份登录 ， 组织管理->驾校管理 选择驾校，点修改，经营范围(从业类型)，全选，保存 * 导入数据之前先建驾校和运管，以便得到运管ID：tfc_id和驾校ID：ay_id，并按照迁移方案挂大纲 * * 导入数据之前请确认tfc_id和ay_id已经修改 * */ /** * 1.用超级管理员登录 * 2.题库管理>方案管理，新增方案，并给创建的应该添加方案 * 3.在教学大纲下添加大纲 * 4.为添加的教学大纲添加方案 * 5.迁移数据 * */ DbUtil d = new DbUtil(); // String tfc_id="51110000";//乐山 运管ID // String zcode=""; // String zid = tfc_id.trim().substring(0, 6);//地区编码 // String ay_id = "51110001";//驾校ay_id // String schoolName ="乐山市客货危运输驾驶员继续教育培训中心"; //驾校名字 String tfc_id="41090001";//濮阳 运管ID String zcode = "017014";//地区编码 String zid="410900"; String ay_id = "41090002,41090003,41090004";//驾校ay_id String schoolName ="濮阳市道路运输协会,中国石化集团中原石油勘探局培训中心,濮阳市汽车运输公司汽校"; //驾校名字 濮阳市道路运输协会 濮阳市任丘路油田驾校 濮阳市汽车运输公司汽校 String fkmc="hnpy_employed"; String[] ay_idArray = ay_id.split(","); String[] schoolNameArray = schoolName.split(","); int ayid_length = ay_idArray.length; /* // 一、创建oracle临时表 //学员档案表 d.update(conn_oracle_ayjxjy, "create table TEMP_BASE_STD_ARCHIVES( id VARCHAR2(100) not null, ay_id VARCHAR2(200), name VARCHAR2(500) not null, sex VARCHAR2(6), credentials_type VARCHAR2(500), credentials_no VARCHAR2(500) not null, birthday TIMESTAMP(6), address VARCHAR2(500), now_address VARCHAR2(500), landline VARCHAR2(500), phone VARCHAR2(500), zip_code VARCHAR2(500), pic VARCHAR2(500), vehicle_type VARCHAR2(500), state VARCHAR2(500), remark VARCHAR2(500), create_school_id INTEGER, create_school_name VARCHAR2(500), create_user_id INTEGER, create_user_name VARCHAR2(500), create_time TIMESTAMP(6), modify_user_id INTEGER, modify_user_name VARCHAR2(500), modify_time TIMESTAMP(6), nationality VARCHAR2(500), nation VARCHAR2(500), source VARCHAR2(500), zid VARCHAR2(100), user_id VARCHAR2(100), c_stdt_id INTEGER )"); //用户表 d.update(conn_oracle_ayjxjy, "create table TEMP_SYS_USER ( id VARCHAR2(100) not null, password VARCHAR2(255), username VARCHAR2(255), birthday TIMESTAMP(6), name VARCHAR2(255), organization_id VARCHAR2(100), sfz_no VARCHAR2(40), phone VARCHAR2(40), mail VARCHAR2(100), create_time TIMESTAMP(6), modify_time TIMESTAMP(6), create_user_id VARCHAR2(50), create_user_name VARCHAR2(100), status VARCHAR2(20), modify_user_name VARCHAR2(100), modify_user_id VARCHAR2(100), isdeleted NUMBER(1), buildin NUMBER(10), type NUMBER(10), c_user_id INTEGER )"); //培训周期表 d.update(conn_oracle_ayjxjy, "create table TEMP_TR_PERIOD_JXJY ( id VARCHAR2(100) not null, ay_id VARCHAR2(100), stdt_id VARCHAR2(100), remark VARCHAR2(1000), create_user_id VARCHAR2(100), create_user_name VARCHAR2(50), create_time TIMESTAMP(6), modify_user_id VARCHAR2(100), modify_user_name VARCHAR2(50), modify_time TIMESTAMP(6), aud_time TIMESTAMP(6), aud_uid VARCHAR2(100), aud_remark VARCHAR2(1000), over_time TIMESTAMP(6), over_uid VARCHAR2(100), over_remark VARCHAR2(1000), exam_time TIMESTAMP(6), exam_uid VARCHAR2(100), exam_remark VARCHAR2(1000), picture VARCHAR2(200), tfc_id VARCHAR2(100), entry_date TIMESTAMP(6), grade VARCHAR2(100), state VARCHAR2(50), pay_state VARCHAR2(20), auto_code VARCHAR2(40), study_type INTEGER,data_from varchar(100) )"); //学员培训类型 d.update(conn_oracle_ayjxjy, "create table TEMP_TR_CASE_TYPE_JXJY ( id VARCHAR2(100) not null, tp_id VARCHAR2(100), yz_id VARCHAR2(100), case_name VARCHAR2(100), case_no VARCHAR2(100), sfz_no VARCHAR2(40), first_time TIMESTAMP(6), effect_begin_time TIMESTAMP(6), effect_end_time TIMESTAMP(6), data_ver INTEGER, case_code VARCHAR2(50), case_type VARCHAR2(100), upload_flag INTEGER, is_need VARCHAR2(100), status VARCHAR2(40), cyz_no VARCHAR2(100), tct_id_ll VARCHAR2(100) )"); //继续教育从业类型大纲要求学时 d.update(conn_oracle_ayjxjy, "create table TEMP_SYS_CASES ( id VARCHAR2(100) not null, case_type VARCHAR2(100), code VARCHAR2(200), description VARCHAR2(100), weight VARCHAR2(100), is_use VARCHAR2(20) not null, tfc_id VARCHAR2(100), create_time TIMESTAMP(6), change_time TIMESTAMP(6), need_time NUMBER(5), is_jzpx VARCHAR2(20) )"); //继续教育从业类型大纲要求学时明细 d.update(conn_oracle_ayjxjy, "create table TEMP_SYS_CASE_DETAIL ( id VARCHAR2(100) not null, case_id VARCHAR2(100), code VARCHAR2(100), km VARCHAR2(100), content VARCHAR2(1000), time_type VARCHAR2(100), weight INTEGER, p_id VARCHAR2(100), need_time INTEGER, tfc_id VARCHAR2(100), create_time TIMESTAMP(6), change_time TIMESTAMP(6), vf_id VARCHAR2(100), video_name VARCHAR2(1000), unit_weight INTEGER )"); System.out.println("建表完成！"); */ //二、导河南总库数据 //导卡库( 河南只导一次 ) // d.insert(conn_oracle_ayjxjy_zk, 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+"'"); //导激活过的卡 SYS_CARD // d.insert(conn_oracle_ayjxjy_zk, 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 ZID FROM CARD where card_code in(select name from ALL_EMPLOYED.USERS where zcode='"+zcode+"') ", // "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+"'"); // //导用户 SYS_USER d.insert(conn_oracle_ayjxjy_zk, conn_ppas_ayjxjy, "SYS_USER", "select '' as id,a.pass as password,a.name as username,null,null,null,c.sfz_no,c.tel_mov,null,null,'normal',null,null,0,0,0,0 from ALL_EMPLOYED.USERS a,"+fkmc+".user_student b,"+fkmc+".student c where a.\"UID\"=b.\"UID\" and b.stdt_id=c.stdt_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 credentials_no from BASE_STD_ARCHIVES where ay_id like '"+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 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 tid = '"+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---------------------------------------------------------------------// // // //-------------------------------------------------------------temp_SYS_CASES 继续教育从业类型大纲要求学时---------------------------------------------------------------------// // d.insert2(conn_ppas_ayjxjy, conn_oracle_ayjxjy, "temp_SYS_CASES", // "select * from sys_cases where tfc_id='"+tfc_id+"'", // "insert into temp_SYS_CASES ", // "delete from temp_SYS_CASES where tfc_id ='"+tfc_id+"'"); // //-----------------------------------------------------------------END---------------------------------------------------------------------// // // //-------------------------------------------------------------temp_SYS_CASE_DETAIL 继续教育从业类型大纲要求学时明细---------------------------------------------------------------------// // d.insert2(conn_ppas_ayjxjy, conn_oracle_ayjxjy, "temp_SYS_CASE_DETAIL", // "select * from SYS_CASE_DETAIL where tfc_id='"+tfc_id+"'", // "insert into temp_SYS_CASE_DETAIL ", // "delete from temp_SYS_CASE_DETAIL where tfc_id='"+tfc_id+"'"); // //-----------------------------------------------------------------END---------------------------------------------------------------------// // // //------------------------------------------------------------- 导入运管用户 --------------------------------------------------------------------// // ResultSet resultSet3 =d.query(conn_ppas_ayjxjy, "select id, organization_id from sys_user where isdeleted = '0' and username ='"+ tfc_id +"'"); // if(resultSet3.getRow() > 1){ // throw new Exception("运管tfc_id："+tfc_id+"有多条记录！"); // }else{ // resultSet3.next(); // String id = resultSet3.getString("id");//运管用户ID // String organization_id = resultSet3.getString("organization_id");//组织ID // // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "导入运管用户", // "select ' ' as id,pass, name, null,null, '"+ organization_id +"',null,null,mail,null,null,null,null,case when status=1 then 'normal' else 'suspend' end,null,null,'0' as isdeleted,'1' as buildin, '2' as type from ALL_EMPLOYED.USERS where utype = 'yunguan'", // "insert into sys_user(id , password , username, birthday, name , organization_id, sfz_no, phone, mail, create_time, modify_time, create_user_id, create_user_name, status, modify_user_name, modify_user_id, isdeleted, buildin, type )", // "delete from sys_user_role where userid in (select id from sys_user where organization_id = '"+organization_id+"' and username !='"+tfc_id+"' and create_user_id is null);"+ // "delete from sys_user WHERE organization_id = '"+organization_id+"' and username !='"+tfc_id+"' and create_user_id is null;"); // // d.insert2(conn_ppas_ayjxjy,conn_ppas_ayjxjy, "为运管用户赋予角色", // "select ' ' as id,(select roleid from sys_user_role where userid = '"+ id +"') as roleid,id as userid from sys_user WHERE organization_id = '"+organization_id+"' and username !='"+tfc_id+"' and create_user_id is null", // "insert into sys_user_role(id, roleid, userid)", // null); // } // //-----------------------------------------------------------------END---------------------------------------------------------------------// // // //-------------------------------------------------------------导入驾校用户--------------------------------------------------------------------// // ResultSet resultSet4 =d.query(conn_ppas_ayjxjy, "select id, organization_id from sys_user where isdeleted = '0' and username ='"+ ay_idArray[0] +"'"); // if(resultSet4.getRow() > 1){ // throw new Exception("驾校ay_id："+ay_idArray[0]+"有多条记录！"); // }else{ // resultSet4.next(); // String id = resultSet4.getString("id");//驾校用户ID // String organization_id = resultSet4.getString("organization_id");//组织ID // // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "导入驾校用户", // "select ' ' as id,pass, name, null,null, '"+ organization_id +"',null,null,mail,null,null,null,null,case when status=1 then 'normal' else 'suspend' end,null,null,'0' as isdeleted,'1' as buildin, '1' as type from ALL_EMPLOYED.USERS where utype = 'school'", // "insert into sys_user(id , password , username, birthday, name , organization_id, sfz_no, phone, mail, create_time, modify_time, create_user_id, create_user_name, status, modify_user_name, modify_user_id, isdeleted, buildin, type )", // "delete from sys_user_role where userid in (select id from sys_user where organization_id = '"+organization_id+"' and username !='"+ay_idArray[0]+"' and create_user_id is null);"+ // "delete from sys_user WHERE organization_id = '"+organization_id+"' and username !='"+ay_idArray[0]+"' and create_user_id is null;"); // // d.insert2(conn_ppas_ayjxjy,conn_ppas_ayjxjy, "为运管用户赋予角色", // "select ' ' as id,(select roleid from sys_user_role where userid = '"+ id +"') as roleid,id as userid from sys_user WHERE organization_id = '"+organization_id+"' and username !='"+ay_idArray[0]+"' and create_user_id is null", // "insert into sys_user_role(id, roleid, userid)", // null); // } // //-----------------------------------------------------------------END---------------------------------------------------------------------// // for(int i = 0; i< ayid_length; i++){ // d.update(conn_oracle_ayjxjy, "update school set ay_id='"+ ay_idArray[i] +"' where name = '"+schoolNameArray[i]+"'");//根据新建的驾校的单位编码（ay_id），修改原始数据school表中的ay_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, '"+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, 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 ", "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 AY_ID = '"+ay_idArray[i]+"'");//用BASE_STD_ARCHIVES表中的REMARK字段记录学员在oracle数据库中的stdt_id d.update(conn_ppas_ayjxjy,"update BASE_STD_ARCHIVES set pic = null where pic = '^' and AY_ID = '"+ay_idArray[i]+"';"+ "update BASE_STD_ARCHIVES set sex = null where sex = '^' and AY_ID = '"+ay_idArray[i]+"';"+ "update BASE_STD_ARCHIVES set address = null where address = '^' and AY_ID = '"+ay_idArray[i]+"';"+ "update BASE_STD_ARCHIVES set NATION = null where NATION = '^' and AY_ID = '"+ay_idArray[i]+"';" + "update BASE_STD_ARCHIVES SET PIC = REPLACE(PIC ,'sites/default/files/bm/','/static/upload/stdSfzPhoto/') where pic is not null and AY_ID = '"+ay_idArray[i]+"' "); 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.ay_id like '"+zid+"%' "); //-----------------------------------------------------------------END---------------------------------------------------------------------// // // //------------------------------------------------------------TR_PERIOD_JXJY 培训周期---------------------------------------------------------------------// //// d.insert2(conn_ppas_ayjxjy, conn_oracle_ayjxjy, "TEMP_BASE_STD_ARCHIVES", //// "select 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,c_stdt_id from BASE_STD_ARCHIVES where ay_id='"+ay_idArray[i]+"'", //// "insert into TEMP_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,c_stdt_id) ", //// "delete from TEMP_BASE_STD_ARCHIVES where AY_ID = '"+ay_idArray[i]+"'"); // 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, NULL 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 ALL_EMPLOYED.USERS SU ON HUBEIUS.CREATE_UID = SU.\"UID\" LEFT JOIN SCHOOL SS ON SU.UNIT_ID = SS.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 //用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.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] +"'", "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 ALL_EMPLOYED.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 id from BASE_STD_ARCHIVES 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.AY_ID = '"+ay_idArray[i]+"'"); // //-----------------------------------------------------------------END---------------------------------------------------------------------// // // //------------------------------------------------------------TR_CASE_TYPE_JXJY 学员培训类型 ---------------------------------------------------------------------// //// d.insert2(conn_ppas_ayjxjy, conn_oracle_ayjxjy, "TEMP_TR_PERIOD_JXJY", //// "select * from TR_PERIOD_JXJY where ay_id='"+ay_idArray[i]+"'", //// "insert into TEMP_TR_PERIOD_JXJY ", //// "delete from TEMP_TR_PERIOD_JXJY where ay_id='"+ay_idArray[i]+"'"); // 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 ALL_EMPLOYED.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.TP_ID = TP.MODIFY_USER_ID AND TP.AY_ID = '"+ay_idArray[i]+"'");//用TR_CASE_TYPE_JXJY的TP_ID与TR_PERIOD_JXJY中的MODIFY_USER_ID字段进行关联，并将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.insert2(conn_ppas_ayjxjy, conn_oracle_ayjxjy, "TEMP_TR_CASE_TYPE_JXJY", //// "select tct.* from TR_CASE_TYPE_JXJY tct join tr_period_jxjy tpj on tct.tp_id = tpj.id where tpj.ay_id='"+ay_idArray[i]+"'", //// "insert into TEMP_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,cyz_no,tct_id_ll ) ", //// "delete from TEMP_TR_CASE_TYPE_JXJY where tp_id in (select id from temp_tr_period_jxjy where ay_id ='"+ay_idArray[i]+"') "); // 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, 24,0 FROM USER_STUDENT HUBEIUS LEFT JOIN STUDYTIMETOTAL HUBEITT ON HUBEITT.STDT_ID = HUBEIUS.STDT_ID LEFT JOIN ALL_EMPLOYED.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");//用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 tfc_id = '"+tfc_id+"');" + "UPDATE TR_STUDYTIMETOTAL_JXJY SET CUR_KM=round(km1/(CUR_KM*60)*100,0); " + "UPDATE TR_STUDYTIMETOTAL_JXJY SET CUR_KM='100' where to_number(CUR_KM)>100;"); // //-----------------------------------------------------------------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', 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 ALL_EMPLOYED.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.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---------------------------------------------------------------------// // // //------------------------------------------------------------SYS_USER 用户表---------------------------------------------------------------------// // //// d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "SYS_USER", //// "SELECT ' ' as id, 'e10adc3949ba59abbe56e057f20f883e' PASSWORD, bsa.PHONE, NULL, NULL, NULL, BSA.CREDENTIALS_NO, BSA.PHONE, null, NULL, NULL, NULL, NULL, 'normal', NULL, NULL, 0, 0, 0 from temp_BASE_STD_ARCHIVES bsa where 1=1 ", //// "INSERT INTO SYS_USER ( ID, PASSWORD, USERNAME, BIRTHDAY, NAME, ORGANIZATION_ID, SFZ_NO, PHONE, MAIL, CREATE_TIME, MODIFY_TIME, CREATE_USER_ID, CREATE_USER_NAME, STATUS, MODIFY_USER_NAME, MODIFY_USER_ID, ISDELETED, BUILDIN, TYPE ) ", //// "delete from sys_user_role where userid in (select id from sys_user where sfz_no in (select CREDENTIALS_NO from BASE_STD_ARCHIVES where ay_id='"+ay_idArray[i]+"'));" //// + "delete from sys_user where sfz_no in (select CREDENTIALS_NO from BASE_STD_ARCHIVES where ay_id='"+ay_idArray[i]+"')"); //// d.update(conn_ppas_ayjxjy, "UPDATE BASE_STD_ARCHIVES BSA SET bsa.user_id=(select su.id from sys_user su where SU.SFZ_NO=BSA.CREDENTIALS_NO and su.sfz_no is not null ) where bsa.ay_id='"+ay_idArray[i]+"';"); // // //-----------------------------------------------------------------END---------------------------------------------------------------------// // // //------------------------------------------------------------TR_VIDEO_LOG_JXJY 用户视频日志---------------------------------------------------------------------// //// d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "TR_VIDEO_LOG_JXJY", //// "SELECT ' ' as id , TCT.id, SCD.ID, 0, 0, 0 FROM temp_TR_PERIOD_JXJY TPJ left join USER_STUDENT us on tpj.over_remark = us.\"UID\" LEFT JOIN temp_TR_CASE_TYPE_JXJY TCT ON TPJ.ID=TCT.TP_ID LEFT JOIN temp_SYS_CASES SC ON SC.TFC_ID=TPJ.TFC_ID LEFT JOIN temp_SYS_CASE_DETAIL SCD ON SCD.CASE_ID=SC.ID WHERE 1=1 AND TCT.CASE_CODE=SCD.CODE and scd.p_id is not null and us.state != '20' and us.state != '30' and TPJ.ay_id ='"+ay_idArray[i]+"'", //// "INSERT INTO TR_VIDEO_LOG_JXJY ( ID, TCT_ID, CD_ID, IS_OVER, PLAY_PERCENT, START_TIME ) ", //// "delete from TR_VIDEO_LOG_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]+"')"); // //-----------------------------------------------------------------END---------------------------------------------------------------------// // // //------------------------------------------------------------TR_USER_VIDEO_JXJY 用户当前观看视频--------------------------------------------------------------------// //// d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "TR_USER_VIDEO_JXJY", //// "SELECT ' ' as id, TCT.ID, SCD.id FROM temp_TR_PERIOD_JXJY TPJ LEFT JOIN temp_TR_CASE_TYPE_JXJY TCT ON TPJ.ID = TCT.TP_ID LEFT JOIN temp_SYS_CASES SC ON SC.TFC_ID = TPJ.TFC_ID LEFT JOIN temp_SYS_CASE_DETAIL SCD ON SCD.CASE_ID = SC.ID WHERE 1 = 1 AND TCT.CASE_CODE = SCD.CODE AND SCD.P_ID IS NOT NULL AND SCD.UNIT_WEIGHT = 1 AND SCD.WEIGHT = 1 AND TPJ.AY_ID ='"+ay_idArray[i]+"' ORDER BY TCT.ID, SCD.UNIT_WEIGHT, SCD.WEIGHT ", //// "INSERT INTO TR_USER_VIDEO_JXJY (ID, TCT_ID, CD_ID )", //// "delete from TR_USER_VIDEO_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]+"')"); // //-----------------------------------------------------------------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 ALL_EMPLOYED.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---------------------------------------------------------------------// // // //-------------------------------------------------------------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 ALL_EMPLOYED.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.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 ALL_EMPLOYED.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_ID = SAP.STDT_ID AND SAP.PIC IS NOT NULL AND AY_ID = '"+ay_idArray[i]+"'");//根据TR_PERIOD_JXJY的MODIFY_USER_ID字段与STDT_ALI_PIC的STDT_ID字段关联更新TR_PERIOD_JXJY的PICTURE字段 // //-----------------------------------------------------------------END---------------------------------------------------------------------// } System.out.println("\n--------------------导库完毕------------------------------"); conn_oracle_ayjxjy_zk.close(); conn_oracle_ayjxjy.close(); conn_ppas_ayjxjy.close(); } catch (Exception ex) { ex.printStackTrace(); } } }