package ay.zk;

import java.sql.Connection;
import java.sql.ResultSet;

import ay.db.ConnTools;
import ay.db.DbUtil;

public class Zk_ayth_fj {
	/**
	 * 福建在线理论转库程序
	 * 
	 * 步骤:
	 * 1、所有题库图片传阿里云
	 * 2、管理端用aykj登录，组织机构->单位列表，添加对应的驾校及运管；用户管理->用户列表，添加对应的驾校用户及运管用户
	 * 3、运行数据库中create_new_exam_paper的存储过程生成试卷
	 * 4、为对应运管在数据库中复制一份参数
	 * 5、为对应运管在数据库中复制一份大纲
	 * 6、为对应运管在数据库中复制一套studytimeneed
	 */
	
	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 = "41050007";  //驾校AYID
			String ay_id = "";
			String tfc_id = "41050000"; //运管ID
//			String org_yid = "61";//在oracle数据库中运管的ID
			String yid = "";//在PPAS数据库中运管的ID
			String school_id = "";//在PPAS数据库中驾校的ID
			String zid = "410500";
			
			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.is_dept = 0 and y.is_dept = 0 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");
					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+"','"+zid+"',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,area_code,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 seq_student_stdt_id.nextval,stdt_id,'"+ay_id+"',class_type from student where ay_id = '"+ay_id+"' ", 
					"insert into file_student_extend(id,stdt_id,ayid,teacher)", 
					"delete from file_student_extend where stdt_id in (select id from file_student where ayid = '"+ay_id+"' and id <100000000)");
//			d.update(conn_ppas_ayth_zk, "UPDATE file_student_extend set teacher = '' where teacher = '^' ");
//			
			//------------------------------------------------------------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,'th' 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,coath_type)", 
//					"delete from file_coach 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.sfz_no,'e10adc3949ba59abbe56e057f20f883e',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,sfzno,sfzpwd,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,'2017',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+"' "
					+ " FROM studytime stt LEFT JOIN student st ON stt.user_id = st.\"UID\" LEFT JOIN STUDYTIMETOTAL STOTAL ON ST.STDT_ID = STOTAL.STDT_ID WHERE 1 =1 AND stt.state = 1 AND stt.ay_id = '"+ay_id+"' AND STOTAL.cur_km != 'KM1ZT=通过,KM2ZT=通过,KM3ZT=通过' ",
					"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)", 
//					"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)");
//	
//			//------------------------------------------------------------studytimetotal_org    学时总表原始表---------------------------------------------------------------------//
//			d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "studytimetotal_org", 
//					" 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_org (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_org 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", 
//					"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+"' "
//					+ " FROM studytime stt LEFT JOIN student st ON stt.user_id = st.\"UID\" WHERE 1 =1 AND stt.state = 1 AND stt.ay_id = '"+ay_id+"'",
////					+ " 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)", 
//					"delete from studytime where ay_id = '"+ay_id+"' ");
////					"");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
}