1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
| package ay.zk;
|
| import java.sql.Connection;
| import java.sql.ResultSet;
|
| import ay.db.ConnTools;
| import ay.db.DbUtil;
|
| public class Zk_ayth_sjz {
| /**
| * 石家庄在线理论转库程序
| *
| * 步骤:
| * 1、所有题库图片传阿里云
| * 2、所有签到签退图片所在文件夹放到新系统下
| * 3、管理端用aykj登录，组织机构->单位列表，添加对应的驾校及运管；用户管理->用户列表，添加对应的驾校用户及运管用户
| * 4、在驾校表中更新对应驾校的xkz_no字段为oracle库中的data_from字段的值
| * 5、系统管理->学时管理，为石家庄地区创建学时信息
| * 6、学员档案里的人脸模板图片上传阿里云
| * 7、运行数据库中create_new_exam_paper的存储过程生成试卷
| * 8、把对应驾校的人脸特征码文件夹保存到新系统中
| * 9、为对应运管在数据库中复制一份参数
| */
|
| public static void main(String args[]) {
| try {
|
| Connection conn_oracle_ayth_zk = ConnTools.make_oracle_ayth_conn();
| Connection conn_ppas_ayth_zk = ConnTools.make_ppas_ayth_conn();
|
| DbUtil d = new DbUtil();
|
|
|
| String ay_ids = "13010172"; //驾校AYID
| String ay_id = "";
| String tfc_id = ""; //运管ID
| // String org_yid = "61";//在oracle数据库中运管的ID
| String yid = "";//在PPAS数据库中运管的ID
| String school_id = "";//在PPAS数据库中驾校的ID
| String zid = "130100";
|
| String[] ay_idArray = ay_ids.split(",");
| int ayid_length = ay_idArray.length;
| //// //导教学大纲
| // d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "TEACH_PROJECT",
| // " SELECT TID, NAME, CONTENT, TARGET, 1, KM, TYPE, MODEL FROM TEACH_PROJECT where model = 'motor' ",
| // " INSERT INTO teach_project(id, name, content, target, status, km, type, model) ",
| // " delete from teach_project ");
| //
| //// //导题库
| // d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "QUESTIONS",
| // " SELECT QID, CONTENT, ANSWER_CONTENT, ANSWER, IMAGE_URL, TYPE, IS_USE, TID FROM QUESTIONS where is_use = 1",
| // " INSERT INTO questions(id, content, answer_content, answer, image_url, type, is_use, tid)",
| // " delete from questions");
| //
| // //------------------------------------------------------------studytimeneed 需学学时表---------------------------------------------------------------------//
| // d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk,"studytimeneed",
| // " SELECT S_ID,CAR_TYPE,KM, VALUE,TRAIN_CODE, 602 ZID, 130100 ZCODE,'河北省石家庄市' ZNAME FROM STUDYTIMENEED where zname like '%石家庄%' ",
| // " insert into studytimeneed (id,car_type,km,value,train_code,zid,zcode,zname)",
| // " delete from studytimeneed where zcode = '"+zid+"'");
|
|
| for (int i = 0; i < ayid_length; i++) {
| ResultSet rs = d.query(conn_ppas_ayth_zk, "select sch.tfc_id,y.id yid,sch.id school_id from school sch,school y where sch.tfc_id = y.ayid and sch.unit_type = '0401' and y.unit_type = '0101' and sch.ayid = '"+ay_idArray[i]+"'");
| if(rs.getRow()>1){
| throw new Exception("AYID:'"+ay_idArray[i]+"'有多条记录！");
| }else{
| rs.next();
| ay_id = ay_idArray[i];
| tfc_id = rs.getString("tfc_id");
| yid = rs.getString("yid");
| school_id = rs.getString("school_id");
| }
|
| // //------------------------------------------------------------更新ORACLE库中驾校状态---------------------------------------------------------------------//
| // d.update(conn_oracle_ayth_zk, "UPDATE SCHOOL SET SCHOOL_TYPE = 'new' where AY_ID = '"+ay_id+"'");
| //
| //// //导入驾校绑卡记录
| // d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "user_pay",
| // " select uc.u_c_id,st.stdt_id,'card',uc.c_id from user_card uc,student st where uc.\"UID\" = st.\"UID\" and st.ay_id = '"+ay_id+"'",
| // " insert into user_pay (id,stdt_id,pay_type,pay_id)",
| // " delete from user_pay where stdt_id in (select id from file_student where ayid = '"+ay_id+"' and id <100000000 )");
| //
| // //导入驾校已激活的学习卡
| // d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk,"card",
| // " select c.c_id,c.card_code,c.password,c.state,c.valid_time/12,c.active_date,c.active_date,c.over_date,c.card_type,c.box_no,'"+zid+"' "
| // +" from card c,student st where c.card_code = st.ssjd and st.ay_id = '"+ay_id+"'",
| // " insert into card (id,card_code,password,state,valid_time,bind_time,active_date,over_date,card_type,box_no,zcode)",
| // " delete from card where id in (select up.pay_id from user_pay up,file_student st where up.stdt_id = st.id and st.ayid = '"+ay_id+"' and st.id <100000000)");
| //// //------------------------------------------------------------file_student 学员基础档案信息---------------------------------------------------------------------//
| // d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "file_student",
| // "select stdt_id,id,'"+tfc_id+"','"+ay_id+"',stdt_icno,ic_state,stdt_no,name,(case when ssjd is not null then '01' else null end),drv_cartype,in_date,state,sfz_no,link_address,(case when sex='男' then 'M' else 'F' end) sex,'' nation_type,tel_mov,in_date from student where ay_id = '"+ay_id+"' and state = '00'",
| // "insert into file_student(id,org_stdt_id,tfc_id,ayid,ic_no,ic_state,student_no,name,chs_py,vehicle_type,entry_date,state,credentials_no,address,sex,nation,phone,create_time)",
| //// "delete from file_student where ayid = '"+ay_id+"'");
| // "delete from file_student where ayid = '"+ay_id+"' and id <100000000");
| ////
| //// //------------------------------------------------------------file_student_extend 学员信息扩展表---------------------------------------------------------------------//
| // d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "file_student_extend",
| // "select stdt_id,stdt_id,'"+ay_id+"',(case when temp_address like '%avatarrltz%' then 'public/upload/'||temp_address else temp_address end) temp_address,(case when jwh like '%avatarrltz%' then 'public/upload/'||jwh else jwh end) jwh,paper_no,paper_type,(case when jwh is not null then lastmod_time else null end) from student where ay_id = '"+ay_id+"' ",
| // "insert into file_student_extend(id,stdt_id,ayid,rltzz,rltp,export2,export3,face_update_time)",
| // "delete from file_student_extend where stdt_id in (select id from file_student where ayid = '"+ay_id+"' and id <100000000)");
| //
| // //------------------------------------------------------------file_coach 教练表---------------------------------------------------------------------//
| // d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "file_coach",
| // "select id,'"+ay_id+"','"+tfc_id+"',coach_icno,coach_no,name,(case when sex='男' and sex is not null then 'M' else 'F' end) sex,sfz_no,state,drv_cartype from coaches where ay_id = '"+ay_id+"'",
| // "insert into file_coach(id,ayid,tfc_id,ic_no,coach_no,name,sex,credentials_no,state,vehicle_type)",
| // "delete from file_coach where ayid = '"+ay_id+"'");
|
| //------------------------------------------------------------audit_app 学时审核表---------------------------------------------------------------------//
| // d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "audit_app",
| // "select aa.aid,aa.stdt_id,"+school_id+",unix_to_oracle(aa.create_time) create_time,"+yid+",unix_to_oracle(aa.audit_time) audit_time,aa.state,aa.remark,aa.remark1,'"+zid+"' from audit_app aa,school sch where aa.create_school_id = sch.school_id and sch.ay_id ="+ay_id,
| // "INSERT INTO audit_app(id, stdt_id, create_school_id, create_time, audit_yid, audit_time, state, remark, remark1,zcode)",
| // "delete from audit_app where create_school_id = "+school_id);
|
| //------------------------------------------------------------导出日志与导出批次表不导入到新系统中------------------------------------------------------------//
| // //------------------------------------------------------------export_log 导出日志表---------------------------------------------------------------------//
| // d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "export_log",
| // " select el.el_id,el.batch_no,el.stdt_id from export_log el,student st where el.stdt_id = to_char(st.stdt_id) and st.ay_id = '"+ay_id+"'",
| // " insert into export_log (id,batch_no,stdt_id)",
| // " delete from export_log where stdt_id in (select id from file_student where ayid = '"+ay_id+"')");
| ////
| //// //------------------------------------------------------------export_batch 导出批次表---------------------------------------------------------------------//
| // d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "export_batch",
| // " select eb_id,'"+tfc_id+"',batch_no,file_address,'11',unix_to_oracle(export_time),type from export_batch where yid = "+org_yid,
| // " insert into export_batch (id,yid,batch_no,file_address,state,export_time,type)",
| // " delete from export_batch where yid = '"+tfc_id+"'");
|
| //------------------------------------------------------------studytime_temp 集中培训学时临时表---------------------------------------------------------------------//
| // d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "studytime_temp",
| // " select temp.data_id,st.stdt_id,temp.ay_id,"+tfc_id+",temp.coach_id,temp.beg_day,temp.beg_minute,unix_to_oracle(temp.beg_time),unix_to_oracle(temp.end_time),temp.time_type,temp.is_sim,temp.tr_minute,temp.data_from,temp.data_from_info,unix_to_oracle(temp.collect_date),replace(temp.data_ck1,'signInOutPhoto','public/sign'),replace(temp.data_ck2,'signInOutPhoto','public/sign'),temp.aud_remark,"
| // +" unix_to_oracle(temp.aud_pass),temp.state,temp.session_id,'130100' zcode,temp.upload_flag,temp.comment_out,temp.comment_in,unix_to_oracle(temp.actual_beg_time),unix_to_oracle(temp.actual_end_time),temp.lession_id,replace(temp.photo_in,'signInOutPhoto','public/sign'),replace(temp.photo_out,'signInOutPhoto','public/sign'),temp.apply_status,unix_to_oracle(temp.need_check)"
| // +" from studytime_temp temp LEFT JOIN student st ON temp.user_id = st.\"UID\" LEFT JOIN export_log el ON TO_CHAR(st.stdt_id) = TO_CHAR(el.stdt_id)LEFT JOIN export_batch eb ON el.batch_no = eb.batch_no "
| // +" where temp.user_id = st.\"UID\" and temp.ay_id = '"+ay_id+"' AND (st.paper_type IS NULL OR (eb.type = 'B' AND eb.export_time >=1472659200)) ",
| // " insert into studytime_temp(id,stdt_id,ay_id,tfc_id,coach_id,beg_day,beg_minute,beg_time,end_time,time_type,is_sim,tr_minute,data_from,data_from_info,collect_date,data_ck1,data_ck2,aud_remark,"
| // +" aud_pass,state,session_id,zcode,upload_flag,comment_out,comment_in,actual_beg_time,actual_end_time,lession_id,photo_in,photo_out,apply_status,need_check)",
| //// " delete from studytime_temp where ay_id = '"+ay_id+"'");
| // " delete from studytime_temp where stdt_id in (select id from file_student where ayid = '"+ay_id+"' and id <100000000)");
|
| ////
| // //------------------------------------------------------------lessions 集中培训课程状态表---------------------------------------------------------------------//
| // d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "lessions",
| // "SELECT ID, AY_ID, COACH_ID, COACH_NAME, BEGIN_DATE, END_DATE, SIGN_IN_DATE, SIGN_OFF_DATE,'SIGNOUT' LESSION_STATE, unix_to_oracle(STUDENT_CHECK) FROM LOCAL_LESSIONS WHERE AY_ID = '"+ay_id+"'",
| // "insert into lessions (ID, AYID, COACH_ID, COACH_NAME, BEGIN_DATE, END_DATE, SIGN_IN_DATE, SIGN_OFF_DATE,LESSION_STATE, STUDENT_CHECK)",
| // "delete from lessions where ayid = '"+ay_id+"'");
|
|
| // //------------------------------------------------------------stduser 学员用户表---------------------------------------------------------------------//
| // d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "stduser",
| // "select u.\"UID\",st.stdt_id,u.name,u.pass,st.name,substr(st.tel_mov,0,11) tel_mov,unix_to_oracle(u.created),'normal',0,0,'"+zid+"' from users u,student st where u.\"UID\" = st.\"UID\" and st.ssjd is not null and st.ay_id = '"+ay_id+"'",
| // "insert into stduser(id,stdt_id,cardcode,password,name,phone,create_time,status,isdeleted,buildin,zid)",
| //// "delete from stduser where stdt_id in (select id from file_student where ayid = '"+ay_id+"')");
| // "delete from stduser where stdt_id in (select id from file_student where ayid = '"+ay_id+"')");
| //
| //
| // //------------------------------------------------------------studytime 学时明细表---------------------------------------------------------------------//
| // d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "studytime",
| // "select stt.data_id,stt.ay_id,stt.tfc_id,stt.coach_id,st.stdt_id,'2016',stt.stdt_icno,stt.field_code,stt.beg_day,stt.beg_minute,unix_to_oracle(stt.beg_time),unix_to_oracle(stt.end_time),stt.ph,stt.km,"
| // + "stt.time_type,stt.is_sim,stt.tr_minute,stt.data_from,stt.data_from_info,unix_to_oracle(stt.collect_date),stt.state,stt.save_flag,'"+zid+"',stt.photo_in,stt.photo_out "
| // + " FROM studytime stt LEFT JOIN student st ON stt.user_id = st.\"UID\" LEFT JOIN export_log el ON TO_CHAR(st.stdt_id) = TO_CHAR(el.stdt_id)"
| // + "LEFT JOIN export_batch eb ON el.batch_no = eb.batch_no WHERE 1 =1 AND stt.state = 1 AND stt.ay_id = '"+ay_id+"' AND (st.paper_type IS NULL OR (eb.type = 'B' AND eb.export_time >=1472659200)) ",
| // "insert into studytime(id,ay_id,tfc_id,coach_id,stdt_id,car_id,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,save_flag,zcode,photo_in,photo_out)",
| //// "delete from studytime where ay_id = '"+ay_id+"'");
| // "delete from studytime where stdt_id in (select id from file_student where ayid = '"+ay_id+"' and id <100000000)");
| //
| // //------------------------------------------------------------studytimetotal 学时总表---------------------------------------------------------------------//
| // d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "studytimetotal",
| // " select total_id,stdt_id,km1,km2,km3,'"+zid+"','"+zid+"',km2_sim,aud_km1,aud_km2,aud_km3,aud_km2_sim,replace(cur_km,'KM2ZT=未通过','KM2ZT=通过') as cur_km from studytimetotal "
| // +" where stdt_id in (select stdt_id from student where ay_id = '"+ay_id+"') ",
| // " insert into studytimetotal (id,stdt_id,km1,km2,km3,zid,zcode,km2_sim,aud_km1,aud_km2,aud_km3,aud_km2_sim,cur_km) ",
| //// " delete from studytimetotal where stdt_id in (select id from file_student where ayid = '"+ay_id+"')");
| // " delete from studytimetotal where stdt_id in (select id from file_student where ayid = '"+ay_id+"' and id <100000000)");
| //
| //// -------------------------------------------------------------stdt_ali_pic 学员阿里云照片路径--------------------------------------------------------------------//
| // d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "STDT_ALI_PIC",
| // "SELECT STDT_ID, RL_PIC,AY_ID FROM STDT_ALI_PIC WHERE AY_ID = '"+ay_id+"' ",
| // "INSERT INTO STDT_ALI_PIC (STDT_ID,RL_PIC,AY_ID)",
| // "delete from STDT_ALI_PIC WHERE AY_ID = '"+ay_id+"'");
| //
| // d.update(conn_ppas_ayth_zk, "UPDATE FILE_STUDENT_EXTEND EX SET EX.RLTP = SAP.RL_PIC FROM STDT_ALI_PIC SAP WHERE EX.STDT_ID = SAP.STDT_ID AND SAP.AY_ID = '"+ay_id+"'");
| //
| // d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "import_school_log",
| // " select '"+ay_id+"',sysdate from dual",
| // " insert into import_school_log(ayid,create_time)",
| // "");
| //
| }
| //
| // //-------------------------------------------------------------补传部分--------------------------------------------------------------------//
| // d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "studytime_temp",
| // " select temp.data_id,st.stdt_id,temp.ay_id,"+tfc_id+",temp.coach_id,temp.beg_day,temp.beg_minute,unix_to_oracle(temp.beg_time),unix_to_oracle(temp.end_time),temp.time_type,temp.is_sim,temp.tr_minute,temp.data_from,temp.data_from_info,unix_to_oracle(temp.collect_date),replace(temp.data_ck1,'signInOutPhoto','public/sign'),replace(temp.data_ck2,'signInOutPhoto','public/sign'),temp.aud_remark,"
| // +" unix_to_oracle(temp.aud_pass),temp.state,temp.session_id,'130100' zcode,temp.upload_flag,temp.comment_out,temp.comment_in,unix_to_oracle(temp.actual_beg_time),unix_to_oracle(temp.actual_end_time),temp.lession_id,replace(temp.photo_in,'signInOutPhoto','public/sign'),replace(temp.photo_out,'signInOutPhoto','public/sign'),temp.apply_status,unix_to_oracle(temp.need_check)"
| // +" from studytime_temp temp LEFT JOIN student st ON temp.user_id = st.\"UID\" LEFT JOIN export_log el ON TO_CHAR(st.stdt_id) = TO_CHAR(el.stdt_id)LEFT JOIN export_batch eb ON el.batch_no = eb.batch_no "
| // +" where temp.user_id = st.\"UID\" and temp.ay_id = '"+ay_id+"' AND (st.paper_type IS NULL OR (eb.type = 'B' AND eb.export_time >=1472659200)) AND temp.beg_time >1479639600",
| // " insert into studytime_temp(id,stdt_id,ay_id,tfc_id,coach_id,beg_day,beg_minute,beg_time,end_time,time_type,is_sim,tr_minute,data_from,data_from_info,collect_date,data_ck1,data_ck2,aud_remark,"
| // +" aud_pass,state,session_id,zcode,upload_flag,comment_out,comment_in,actual_beg_time,actual_end_time,lession_id,photo_in,photo_out,apply_status,need_check)",
| // " ");
| // //补传课程
| // d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "lessions",
| // "SELECT ID, AY_ID, COACH_ID, COACH_NAME, BEGIN_DATE, END_DATE, SIGN_IN_DATE, SIGN_OFF_DATE,'SIGNOUT' LESSION_STATE, unix_to_oracle(STUDENT_CHECK) FROM LOCAL_LESSIONS WHERE AY_ID = '"+ay_id+"' AND TO_CHAR(BEGIN_DATE,'yyyy-MM-dd HH:mm:ss') > '2016-11-20 19:00:00'",
| // "insert into lessions (ID, AYID, COACH_ID, COACH_NAME, BEGIN_DATE, END_DATE, SIGN_IN_DATE, SIGN_OFF_DATE,LESSION_STATE, STUDENT_CHECK)",
| // "");
| // //补传学时
| //130631198304151215
| //132323197708040141
| //130728199012305528
| //13012719910421152X
| //620523198207251098
| //132229197612261268
| //130184198901205336
| //130104199807292114
| //130181198612148524
| //130825199010310039
| //130182199202152935
| //130133199204072114
| //632123196812300049
| //130123199107246319
| //130125198909263581
| //130123199506280047
| //130182198705241927
| //632123196812300049
| //13232319760323082X
| //130106199205122727
|
| //
| d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "studytime",
| "select stt.data_id,stt.ay_id,stt.tfc_id,stt.coach_id,st.stdt_id,stt.stdt_icno,stt.field_code,stt.beg_day,stt.beg_minute,unix_to_oracle(stt.beg_time),unix_to_oracle(stt.end_time),stt.ph,stt.km,"
| + "stt.time_type,stt.is_sim,stt.tr_minute,stt.data_from,stt.data_from_info,unix_to_oracle(stt.collect_date),stt.state,stt.save_flag,'"+zid+"',stt.photo_in,stt.photo_out "
| // + " FROM studytime stt LEFT JOIN student st ON stt.user_id = st.\"UID\" LEFT JOIN export_log el ON TO_CHAR(st.stdt_id) = TO_CHAR(el.stdt_id)"
| // + "LEFT JOIN export_batch eb ON el.batch_no = eb.batch_no WHERE 1 =1 AND stt.state = 1 AND stt.ay_id in(13010135,13010046,13010037,13010068,13010047) AND (st.paper_type IS NULL OR (eb.type = 'B' AND eb.export_time >=1472659200)) AND STT.beg_time >1479722400 ",
| + " FROM studytime stt LEFT JOIN student st ON stt.user_id = st.\"UID\" "
| + " WHERE 1 =1 AND stt.state = 1 AND st.sfz_no in ('142303199704251131') ",
| "insert into studytime(id,ay_id,tfc_id,coach_id,stdt_id,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,save_flag,zcode,photo_in,photo_out)",
| // "delete from studytime where stdt_id in(1099901) ");
| "");
| } catch (Exception e) {
| e.printStackTrace();
| }
| }
|
| }
|
|
