package ay.zk; import java.sql.Connection; import java.sql.ResultSet; import ay.db.ConnTools; import ay.db.DbUtil; public class Zk_ayjxjy_sjz { public static void main(String args[]) { /** sjz_emp * */ 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.在新系统中创建相应的运管、驾校、报名点账号 * 6.把新系统index_table中的DA_NUM的值修改为oracle中index_table表里file_no2的值 */ DbUtil d = new DbUtil(); String tfc_id="13010000"; String zcode="005002"; String zid = "130100";//地区编码 String ay_id = "13010001";//驾校ay_id String schoolName ="石家庄市长安区祥运驾驶员职业培训学校"; //驾校名字 String[] ay_idArray = ay_id.split(","); String[] schoolNameArray = schoolName.split(","); int ayid_length = ay_idArray.length; // // -------------------------------------------------------------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---------------------------------------------------------------------// // // //------------------------------------------------------------SYS_USER 用户表---------------------------------------------------------------------// // // d.insert(conn_oracle_ayjxjy, 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,b.create_uid from USERS a,user_student b,student c where a.\"UID\"=b.\"UID\" and b.stdt_id=c.stdt_id and a.utype = 'student' ", // "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,DEPT_ID ) ", // "delete from sys_user where sfz_no in (select credentials_no from BASE_STD_ARCHIVES where zid = '"+zid+"') " ); // 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" ); // // // //------------------------------------------------------------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 ", // "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 ZID = '"+zid+"'"); // 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 (SELECT MAX(ID) ID,SFZ_NO FROM SYS_USER GROUP BY SFZ_NO) B WHERE A.CREDENTIALS_NO=B.SFZ_NO AND a.ZID = '"+zid+"' "); //// //-----------------------------------------------------------------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字段 ////// ////// // //------------------------------------------------------------DEPT_ORG 报名点对照表---------------------------------------------------------------------// // //sys_dept 报名点信息表 // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "DEPT_ORG", // "SELECT SCH.SCHOOL_ID,SCH.NAME,U.NAME,U.\"UID\" FROM SCHOOL SCH LEFT JOIN USERS U ON SCH.SCHOOL_ID = U.UNIT_ID WHERE U.UTYPE in ('point','qxpoint')", // "INSERT INTO DEPT_ORG (UNIT_ID,DEPT_NAME,DEPT_JMS,ORG_UID)", // "DELETE FROM DEPT_ORG" // ); // d.update(conn_ppas_ayjxjy, "UPDATE DEPT_ORG ORG SET ORG.DEPT_ID = SD.DEPT_ID FROM SYS_DEPT SD WHERE ORG.DEPT_NAME = SD.DEPT_NAME"); // // d.update(conn_ppas_ayjxjy, "UPDATE SYS_USER SU SET SU.DEPT_ID = ORG.DEPT_ID FROM DEPT_ORG ORG WHERE SU.USERNAME = ORG.DEPT_JMS"); // // d.update(conn_ppas_ayjxjy, "UPDATE SYS_USER SU SET SU.DEPT_ID = ORG.DEPT_ID FROM DEPT_ORG ORG WHERE SU.DEPT_ID = ORG.ORG_UID "); // //更新在祥运报名的学员 // d.update(conn_ppas_ayjxjy, "update sys_user set dept_id = 1301000101 where id in ( " // + "select su.id from sys_user su left join DEPT_ORG ORG on SU.DEPT_ID = ORG.ORG_UID " // + "where ORG.dept_id is null and su.dept_id is not null and length(su.dept_id)<10)"); // //-----------------------------------------------------------------END---------------------------------------------------------------------// // // // // 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, UNIX_TO_ORACLE(HUBEIUS.EXAM_TIME)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,SS.NAME DEPT_ID,AUTO_CODE2 AS AUTO_CODE 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 ", // "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 ,DEPT_ID,AUTO_CODE) ", // "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,DEPT_ID字段保存了报名点的SCHOOL_NAME // //// //用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]+"'"); //// //更新周期表中的DEPT_ID // d.update(conn_ppas_ayjxjy, "UPDATE TR_PERIOD_JXJY TP SET TP.DEPT_ID = ORG.DEPT_ID FROM DEPT_ORG ORG WHERE TP.DEPT_ID = ORG.DEPT_NAME "); // d.update(conn_ppas_ayjxjy, "UPDATE TR_PERIOD_JXJY SET DEPT_ID = '1301000101' WHERE DEPT_ID = '石家庄市长安区祥运驾驶员职业培训学校'"); // // // //------------------------------------------------------------TR_PERIOD_JXJY_TEMP 历史学员周期表---------------------------------------------------------------------// // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "TR_PERIOD_JXJY_TEMP", // "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, UNIX_TO_ORACLE(HUBEIUS.EXAM_TIME)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,SS.NAME DEPT_ID,AUTO_CODE2 AS AUTO_CODE FROM STUDENT HUBEISTU JOIN USER_STUDENT_MOVE_TEMP 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 ", // "INSERT INTO TR_PERIOD_JXJY_TEMP ( 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 ,DEPT_ID,AUTO_CODE) ", // "delete from TR_PERIOD_JXJY_TEMP where AY_ID = '"+ay_idArray[i]+"'");//TR_PERIOD_JXJY的MODIFY_USER_ID保存了ORACLE中STUDENT表的STDT_ID,MODIFY_USER_NAME保存了USER_STUDENT表中的UID,DEPT_ID字段保存了报名点的SCHOOL_NAME // //// //用TR_PERIOD_JXJY的STDT_ID与BASE_STD_ARCHIVES中的REMARK字段进行关联，并将STDT_ID更新为BASE_STD_ARCHIVES的ID // d.update(conn_ppas_ayjxjy, "UPDATE TR_PERIOD_JXJY_TEMP TP SET TP.STDT_ID = BSA.ID FROM BASE_STD_ARCHIVES_NO_USER BSA WHERE TP.STDT_ID = BSA.REMARK AND BSA.ZID = '"+zid+"'"); // d.update(conn_ppas_ayjxjy, "UPDATE TR_PERIOD_JXJY_TEMP 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_TEMP 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_TEMP SET PICTURE = REPLACE(PICTURE ,'/sites/default/files/bm/','/static/upload/stdBmPhoto/') WHERE PICTURE IS NOT NULL and AY_ID = '"+ay_idArray[i]+"'"); //// //更新周期表中的DEPT_ID // d.update(conn_ppas_ayjxjy, "UPDATE TR_PERIOD_JXJY_TEMP TP SET TP.DEPT_ID = ORG.DEPT_ID FROM DEPT_ORG ORG WHERE TP.DEPT_ID = ORG.DEPT_NAME "); // d.update(conn_ppas_ayjxjy, "UPDATE TR_PERIOD_JXJY_TEMP SET DEPT_ID = '1301000101' WHERE DEPT_ID = '石家庄市长安区祥运驾驶员职业培训学校'"); //// //-----------------------------------------------------------------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]+"')"); // // //------------------------------------------------------------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_TEMP 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_TEMP 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 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.ZID = '"+zid+"'"); ////// //-----------------------------------------------------------------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,UNIX_TO_ORACLE(HUBEIUS.AUD_TIME) 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,XS_AUD_TIME )", //// "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]+"') "); // //------------------------------------------------------------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_TEMP", // "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,UNIX_TO_ORACLE(HUBEIUS.AUD_TIME) FROM USER_STUDENT_MOVE_TEMP 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_TEMP ( 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,XS_AUD_TIME )", // "delete from TR_CASE_TYPE_JXJY_TEMP where tp_id in (select id from TR_PERIOD_JXJY_TEMP 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_TEMP TCT SET TCT.TP_ID = TP.ID FROM TR_PERIOD_JXJY_TEMP 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_TEMP 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_TEMP 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_TEMP", // "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_MOVE_TEMP 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_TEMP ( 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_TEMP TT SET TT.TCT_ID = TCT.ID FROM TR_CASE_TYPE_JXJY_TEMP 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_TEMP 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_TEMP SET CUR_KM=round(km1/(CUR_KM*60)*100,0); " // + "UPDATE TR_STUDYTIMETOTAL_JXJY_TEMP SET CUR_KM='100' where to_number(CUR_KM)>100;"); // // // d.update(conn_ppas_ayjxjy, "UPDATE TR_STUDYTIMETOTAL_JXJY_TEMP 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+"' and TSJ.tct_id in ('e8dfce7b79d3477c94bf733fa4358c26'));" // + "UPDATE TR_STUDYTIMETOTAL_JXJY_TEMP SET CUR_KM=round(km1/(CUR_KM*60)*100,0) where tct_id in ('e8dfce7b79d3477c94bf733fa4358c26'); " // + "UPDATE TR_STUDYTIMETOTAL_JXJY_TEMP SET CUR_KM='100' where to_number(CUR_KM)>100 and tct_id in ('e8dfce7b79d3477c94bf733fa4358c26');"); // //-----------------------------------------------------------------END---------------------------------------------------------------------// ////begin:2017-05-16 17:25:25 // //------------------------------------------------------------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, HUBEIST.DATA_FROM, 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 SCH.AY_ID ='"+ay_idArray[i]+"' and beg_time between 1420041600 and 1451577600", // "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 STUDYTIME_20170516_TEMP 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 // 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, HUBEIST.DATA_FROM, 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 SCH.AY_ID ='"+ay_idArray[i]+"' and US.\"UID\" in(411206,410755) ", // "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 STUDYTIME_20170516_TEMP 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.TCT_ID IN (411206,410755) 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---------------------------------------------------------------------// //// //// //// //-------------------------------------------------------------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.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "TR_PRINTOVER_JXJY_TEMP", // "SELECT ' ' as id, US.\"UID\", UNIX_TO_ORACLE(PO.CREATE_TIME), PO.NUM FROM PRINT_OVER PO LEFT JOIN USER_STUDENT_MOVE_TEMP 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_TEMP( ID, TCT_ID,CREATETIME,NUM)", // "delete from TR_PRINTOVER_JXJY_TEMP WHERE tct_id in (select tct.id from TR_CASE_TYPE_JXJY_TEMP 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_TEMP PO SET PO.TCT_ID = TCT.ID FROM TR_CASE_TYPE_JXJY_TEMP 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_TEMP SET DATA_VER=NULL WHERE TP_ID IN (SELECT ID FROM TR_PERIOD_JXJY_TEMP WHERE ay_id ='"+ay_idArray[i]+"' );"); // //-----------------------------------------------------------------END---------------------------------------------------------------------// // // //------------------------------------------------------------- TH_YY_JHB 培训计划--------------------------------------------------------------------// // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "TH_YY_JHB", // "SELECT ' ' as id, '"+tfc_id+"' as tfc_id, '"+ay_idArray[i]+"',UNIX_TO_ORACLE(TP.BEGIN_TIME)BEGIN_TIME,UNIX_TO_ORACLE(TP.END_TIME)END_TIME,TP.PLAN_NUM JH_NUM,TP.RESERVE_NUM YY_NUM,TP.ACTUAL_NUM IN_NUM,1,0,SCH.NAME DEPT_ID,TP.ID AS TP_ID FROM TRAIN_PROGRAM TP,SCHOOL SCH WHERE TP.UNIT_ID = SCH.SCHOOL_ID AND TP.STATE = 1", // "INSERT INTO TH_YY_JHB( ID,TFC_ID,AY_ID,BEG_TIME, END_TIME, JH_NUM, YY_NUM, IN_NUM, STATE, IS_DEL, DEPT_ID,CREATE_USER_ID)", // "delete from TH_YY_JHB"); //// // d.update(conn_ppas_ayjxjy, "UPDATE TH_YY_JHB TYJ SET TYJ.DEPT_ID = SD.DEPT_ID FROM SYS_DEPT SD WHERE TYJ.DEPT_ID = SD.DEPT_NAME "); // //-----------------------------------------------------------------END---------------------------------------------------------------------// // //// //------------------------------------------------------------- TH_YY_STU 预约明细--------------------------------------------------------------------// // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "TH_YY_STU", // "SELECT ' ' as id, '"+ay_idArray[i]+"', TP_ID,USER_ID,UNIX_TO_ORACLE(RESERVE_TIME) YY_TIME,(CASE WHEN TRAIN_TIME>0 THEN UNIX_TO_ORACLE(TRAIN_TIME) ELSE NULL END) TRAIN_TIME,STATE FROM RESERVE_RECORD WHERE STATE = 1", // "INSERT INTO TH_YY_STU(ID, AY_ID, JH_ID, TP_ID, YY_TIME, TRAIN_TIME, STATE)", // "delete from TH_YY_STU"); // // d.update(conn_ppas_ayjxjy, "UPDATE TH_YY_STU TYS SET TYS.JH_ID = TYJ.ID FROM TH_YY_JHB TYJ WHERE TYS.JH_ID = TYJ.CREATE_USER_ID "); // d.update(conn_ppas_ayjxjy, "UPDATE TH_YY_STU TYS SET TYS.TP_ID = TP.ID FROM TR_PERIOD_JXJY TP WHERE TYS.TP_ID = TP.MODIFY_USER_NAME "); // //-----------------------------------------------------------------END---------------------------------------------------------------------// // // //------------------------------------------------------------- TR_STUDYTIME_DELETE_LOG 学时作废日志--------------------------------------------------------------------// // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "TR_STUDYTIME_DELETE_LOG", // "SELECT ' ' as id,OPERATER_ID,STDT_UID,UNIX_TO_ORACLE(TIME),REMARK FROM STUDYTIME_DELETE_LOG", // "INSERT INTO TR_STUDYTIME_DELETE_LOG (ID, DELETE_USER_ID, TCT_ID,DELETE_TIME, REMARK )", //// "DELETE FROM TR_STUDYTIME_DELETE_LOG"); // ""); // //// d.update(conn_ppas_ayjxjy, "UPDATE TR_STUDYTIME_DELETE_LOG TSDL SET TSDL.OPERATER_ID = SU.ID FROM SYS_USER SU WHERE TSDL.OPERATER_ID = SU.NAME "); // d.update(conn_ppas_ayjxjy, "UPDATE TR_STUDYTIME_DELETE_LOG TSDL SET TSDL.TCT_ID = TCT.ID FROM TR_CASE_TYPE_JXJY TCT WHERE TCT.SFZ_NO = TSDL.TCT_ID "); // d.update(conn_ppas_ayjxjy, "UPDATE TR_STUDYTIME_DELETE_LOG TSDL SET TSDL.TCT_ID = TCT.ID FROM TR_CASE_TYPE_JXJY_TEMP TCT WHERE TCT.SFZ_NO = TSDL.TCT_ID "); // //-----------------------------------------------------------------END---------------------------------------------------------------------// // // //------------------------------------------------------------- SYS_STD_DELETE_LOG_JXJY 学员删除日志--------------------------------------------------------------------// // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "SYS_STD_DELETE_LOG_JXJY_20170605", // "SELECT ' ' as id,ST.SFZ_NO,DL.CARD_CODE,DL.CREATE_UID,DL.LOG_TYPE,UNIX_TO_ORACLE(DL.CREATE_TIME),'"+ ay_idArray[i] +"',ST.STDT_ID,U.ORIGIN_NAME FROM DELETE_LOG DL,STUDENT ST,USERS U WHERE DL.STDT_ID = ST.STDT_ID AND DL.POINT_UID = U.\"UID\" ", // "INSERT INTO SYS_STD_DELETE_LOG_JXJY_20170605(ID, SFZ_NO, CARD_CODE, DELETE_USER_ID, REMARK, DELETE_DATE, AY_ID, TP_ID,DEPT_ID)", // "DELETE FROM SYS_STD_DELETE_LOG_JXJY_20170605"); // // d.update(conn_ppas_ayjxjy, "UPDATE SYS_STD_DELETE_LOG_JXJY_20170605 SSDL SET SSDL.DELETE_USER_ID = SU.ID FROM SYS_USER_1 SU WHERE SSDL.DELETE_USER_ID = SU.NAME "); // d.update(conn_ppas_ayjxjy, "UPDATE SYS_STD_DELETE_LOG_JXJY_20170605 SSDL SET SSDL.TP_ID = TP.ID FROM TR_PERIOD_JXJY TP WHERE SSDL.TP_ID = TP.MODIFY_USER_ID "); // //-----------------------------------------------------------------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 = '"+ay_idArray[i]+"'");//根据TR_PERIOD_JXJY的MODIFY_USER_ID字段与STDT_ALI_PIC的STDT_ID字段关联更新TR_PERIOD_JXJY的PICTURE字段 //入场照片 // d.insert(conn_oracle_ayjxjy, conn_ppas_ayjxjy, "STDT_ALI_PIC_TWO", // "SELECT USER_ID,PIC_TWO FROM STDT_ALI_PIC_TWO ", // "INSERT INTO STDT_ALI_PIC_TWO (USER_ID,PIC_TWO)", // ""); // d.update(conn_ppas_ayjxjy, "UPDATE TR_CASE_TYPE_JXJY_TEMP TCT SET TCT.KSRC_PIC = SAPT.PIC_TWO FROM STDT_ALI_PIC_TWO SAPT WHERE TCT.SFZ_NO = SAPT.USER_ID AND SAPT.PIC_TWO IS NOT NULL "); // //-----------------------------------------------------------------END---------------------------------------------------------------------// // // // } System.out.println("\n--------------------导库完毕------------------------------"); conn_oracle_ayjxjy.close(); conn_ppas_ayjxjy.close(); } catch (Exception ex) { ex.printStackTrace(); } } }