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();
		}
	}
	
}