package ay.zk; import java.sql.Connection; import java.sql.ResultSet; import ay.db.ConnTools; import ay.db.DbUtil; /******************************************************************************* * * 项目名称：转库 * * 版权所有： 重庆安运科技 * * * * 模块名称:主程序 * * 模块功能: * * 编写人员:张德亮 * * 编写日期:2015.12.13 * * * 备注: * * ******************************************************************************/ public class Zk_main { public static void main(String args[]) { /************************************************ 2016.5.25 河南周口转库步骤 1. 所有题库图片转阿里云 2. 所有登记照片转阿里云 3. 所有培训记录照片转阿里云 4. 周口出租车系统数据转新库 5. 周口老系统总库转新库 : (a)创建临时表 (b)用户表USERS,用户权限表 USERS_ROLES,地区表 ZONE,学员卡表CARD,学员卡盒表CARD_BOX,制卡索引表 INDEX_TABLE (c)card表只转周口激活卡表 6. 周口老系统分库转新库 ***********************************************/ 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 users where utype = 'yunguan' select * from 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="41160001";//周口 运管ID String zcode = "017015";//地区编码 String zid="411600"; String ay_id = "41160003";//驾校ay_id String schoolName ="周口市交通技校汽车驾驶员培训学校"; //驾校名字 周口市交通技校汽车驾驶员培训学校 String fkmc="hnzhk_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 FROM CARD where card_code in(select name from 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,null from 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_TIME, MODIFY_TIME, CREATE_USER_ID, CREATE_USER_NAME, STATUS, 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+"%') " ); // d.insert2(conn_ppas_ayjxjy, conn_oracle_ayjxjy, "TEMP_SYS_USER", // "select 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 from SYS_USER where sfz_no in (select credentials_no from BASE_STD_ARCHIVES where ay_id like '"+zid+"%') '", // "insert into TEMP_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 TEMP_SYS_USER where 1=1 "); //三、导分库数据 // // 不用分驾校导入数据 // //------------------------------------------------------------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 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 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, 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 ", "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]+"'"); 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, BSA.AY_ID, BSA.ID, HUBEIUS.auto_code as REMARK, SU.\"UID\" CREATE_USER_ID, SU.NAME CREATE_USER_NAME, UNIX_TO_ORACLE(HUBEIUS.CREATE_TIME) CREATE_TIME, null 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 TEMP_BASE_STD_ARCHIVES BSA LEFT JOIN USERS SU ON BSA.AY_ID = SU.NAME LEFT JOIN SCHOOL SS ON BSA.AY_ID = SS.AY_ID LEFT JOIN STUDENT HUBEISTU ON HUBEISTU.SFZ_NO = BSA.CREDENTIALS_NO 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", "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]+"'"); 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.insert(conn_ppas_ayjxjy, 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, BSA.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 temp_BASE_STD_ARCHIVES BSA LEFT JOIN USERS SU ON BSA.AY_ID = SU.NAME LEFT JOIN SCHOOL SS ON BSA.AY_ID = SS.AY_ID LEFT JOIN STUDENT HUBEISTU ON HUBEISTU.SFZ_NO = BSA.CREDENTIALS_NO LEFT JOIN USER_STUDENT HUBEIUS ON HUBEIUS.STDT_ID = HUBEISTU.STDT_ID where HUBEIUS.us_id is not null and bsa.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]+"')"); //-----------------------------------------------------------------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, TPJ.ID, null, HUBEIUS.CASE_TYPE, HUBEIUS.CASE_NO, BSA.CREDENTIALS_NO, 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 TEMP_TR_PERIOD_JXJY TPJ LEFT JOIN USER_STUDENT HUBEIUS ON TPJ.OVER_REMARK = HUBEIUS.\"UID\" LEFT JOIN TEMP_BASE_STD_ARCHIVES BSA ON BSA.ID = TPJ.STDT_ID where TPJ.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]+"') "); 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, TCT.ID, 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, null FROM temp_TR_PERIOD_JXJY TPJ left join temp_TR_CASE_TYPE_JXJY tct on tct.tp_id = TPJ.id LEFT JOIN USER_STUDENT HUBEIUS ON TPJ.OVER_REMARK = HUBEIUS.\"UID\" LEFT JOIN STUDYTIMETOTAL HUBEITT ON HUBEITT.STDT_ID = HUBEIUS.STDT_ID left join school ss on ss.ay_id = TPJ.ay_id where HUBEITT.USER_ID = HUBEIUS.\"UID\" and TPJ.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 ) ", "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]+"')"); 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, TPJ.AY_ID, TPJ.id, TCT.id, 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 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 TCT.TP_ID = TPJ.ID left join studytime HUBEIST on TPJ.OVER_REMARK = HUBEIST.user_id where us.state != '20' and us.state != '30' and tpj.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]+"')"); //-----------------------------------------------------------------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, TPJ.AY_ID, TCT.ID, TCT.CASE_NO, '1' STATUS, TCT.SFZ_NO, BSA.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, TCT.CASE_NAME, null FROM temp_TR_PERIOD_JXJY TPJ LEFT JOIN temp_TR_CASE_TYPE_JXJY TCT ON TCT.TP_ID = TPJ.ID LEFT JOIN temp_BASE_STD_ARCHIVES BSA ON TPJ.STDT_ID = BSA.ID LEFT JOIN USER_STUDENT HUBEIUS ON TPJ.OVER_REMARK = HUBEIUS.\"UID\" 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\" WHERE HUBEITE.MM = 1 and TPJ.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]+"' )"); //-----------------------------------------------------------------END---------------------------------------------------------------------// //-------------------------------------------------------------jqm 考试机器码表 乐山用--------------------------------------------------------------------// if (ay_id.substring(0,4).equals("5111")){ 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, TCT.ID, PTN.BATCH_NO, UNIX_TO_ORACLE(PTN.CREATE_TIME), UNIX_TO_ORACLE(PTN.CHANGE_TIME), PTN.ADDRESS, PTN.TEL, PTN.SFZ_NO, NAME, NULL, NULL, UNIX_TO_ORACLE(PTN.START_TIME), UNIX_TO_ORACLE(PTN.END_TIME), tpj.ay_id FROM PRINT_TRAIN_NOTICE PTN JOIN TEMP_TR_CASE_TYPE_JXJY TCT ON PTN.\"UID\"=TCT.DATA_VER join temp_TR_PERIOD_JXJY tpj on tct.tp_id = tpj.id where tpj.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] +"'"); //-----------------------------------------------------------------END---------------------------------------------------------------------// //-------------------------------------------------------------tr_printover_jxjy 合格证明打印记录--------------------------------------------------------------------// d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "tr_printover_jxjy", "SELECT ' ' as id, TCT.ID, UNIX_TO_ORACLE(PO.CREATE_TIME), PO.NUM, PO.PRINT_TYPE FROM PRINT_OVER PO JOIN temp_TR_CASE_TYPE_JXJY TCT ON PO.\"UID\" = TCT.DATA_VER join temp_TR_PERIOD_JXJY tpj on tct.tp_id = tpj.id where tpj.ay_id = '"+ay_idArray[i]+"' ", "INSERT INTO tr_printover_jxjy( ID, TCT_ID,CREATETIME,NUM,PRINT_TYPE)", "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_CASE_TYPE_JXJY SET DATA_VER=NULL WHERE TP_ID IN (SELECT ID FROM tr_period_jxjy WHERE ay_id ='"+ay_idArray[i]+"' );"); //-----------------------------------------------------------------END---------------------------------------------------------------------// //-----------------tr_case_type_detail_jxjy user_student_detail 河南 周口 发送继续教育培训结果 -------------------------------------------------- if (ay_id.substring(0,4).equals("4116")){ d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "tr_printover_jxjy", " select id,us_id,yz_id,case_no,case_type,unix_to_oracle(first_time),sfz_no,unix_to_oracle(create_time),unix_to_oracle(effect_begin_time),unix_to_oracle(effect_end_time),upload_flag,data_ver from user_student_detail where upload_flag='0' and yz_id like '"+zid+"%' ", " insert into tr_case_type_detail_jxjy(id,tp_id,yz_id,case_no,case_type,first_time,sfz_no,create_time,effect_begin_time,effect_end_time,upload_flag,data_ver) ", "delete from tr_printover_jxjy where yz_id like '"+zid+"%'"); } } System.out.println("\n--------------------导库完毕------------------------------"); conn_oracle_ayjxjy_zk.close(); conn_oracle_ayjxjy.close(); conn_ppas_ayjxjy.close(); } catch (Exception ex) { ex.printStackTrace(); } } }