package ay.zk;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

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

public class Zk_ayth_nx {

	public static void nx_zk(String ay_id,String zid){
		Connection conn_oracle_ayth_zk = ConnTools.make_oracle_ayth_conn();
		Connection conn_ppas_ayth_zk = ConnTools.make_ppas_ayth_conn();

		DbUtil d = new DbUtil();

		//导入驾校绑卡记录
		d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "user_pay",
				" select uc.u_c_id,st.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 create_time<to_date('2017-07-12 00:00:00','yyyy-MM-dd hh24:mi:ss') )");
//
//			//导入驾校已激活的学习卡
		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,user_card uc where uc.\"UID\" = st.\"UID\" and c.c_id = uc.c_id 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.create_time<to_date('2017-07-12 00:00:00','yyyy-MM-dd hh24:mi:ss') )");
////			//------------------------------------------------------------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,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,id,'"+ay_id+"' from student where ay_id = '"+ay_id+"' ",
				"insert into file_student_extend(id,stdt_id,ayid)",
				"delete from file_student_extend where stdt_id in (select id from file_student where ayid = '"+ay_id+"' and create_time<to_date('2017-07-12 00:00:00','yyyy-MM-dd hh24:mi:ss'))");
////
//			//------------------------------------------------------------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.id,u.name,u.pass,st.sfz_no,u.pass,st.name,substr(st.tel_mov,0,11) tel_mov,unix_to_oracle(u.created),'unbind',0,0,'"+zid+"' from users u,student st where u.\"UID\" = st.\"UID\" and st.state = '00' 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+"' and create_time<to_date('2017-07-12 00:00:00','yyyy-MM-dd hh24:mi:ss') )");
		d.update(conn_ppas_ayth_zk, "update stduser a set a.status = 'normal' from user_pay b where a.stdt_id = b.stdt_id and b.stdt_id is not null ");
////
////
////			//------------------------------------------------------------studytime    学时明细表---------------------------------------------------------------------//
		d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "studytime",
				"select stt.data_id,stt.ay_id,stt.tfc_id,replace(stt.coach_id,'^',''),st.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 st.ay_id = '"+ay_id+"' AND stt.state = 1 AND STOTAL.cur_km != 'km3' and stt.beg_time>1495814400 ",
				"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+"')");
//
//			//------------------------------------------------------------studytimetotal    学时总表---------------------------------------------------------------------//
		d.insert(conn_oracle_ayth_zk, conn_ppas_ayth_zk, "studytimetotal",
				" select stt.total_id,st.id,stt.km1,stt.km2,stt.km3,'"+zid+"','"+zid+"',stt.km2_sim,stt.aud_km1,stt.aud_km2,stt.aud_km3,stt.aud_km2_sim,stt.cur_km from studytimetotal stt,student st "
						+" where stt.stdt_id = st.stdt_id and st.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+"' and create_time<to_date('2017-07-12 00:00:00','yyyy-MM-dd hh24:mi:ss'))");

		//记录导入日志
		/*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)",
				"");*/
	}

	/**
	 * 福建在线理论转库程序
	 * 
	 * 步骤:
	 * 1、为对应运管在数据库中复制一份参数
	 * 2、为对应运管在数据库中复制一份大纲
	 * 3、为对应运管在数据库中复制一套studytimeneed
	 * 4、运行数据库中create_new_exam_paper的存储过程生成试卷
	 * 5、为对应运管在数据库中复制一套tfc_teach_project
	 */
	
	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();


			ExecutorService fixedThreadPool = Executors.newFixedThreadPool(4);
			//String ay_ids = "64010028";  //驾校AYID
			ResultSet rsAyids = d.query(conn_oracle_ayth_zk, "select ay_id from school where tfc_id LIKE '64%' order by ay_id");
			List list = new ArrayList();
			ResultSetMetaData md = rsAyids.getMetaData();
			int columnCount = md.getColumnCount();
			while (rsAyids.next()) {
				Map rowData = new HashMap();
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(md.getColumnName(i), rsAyids.getObject(i));
				}
				list.add(rowData.get("AY_ID"));
			}
			//String ay_ids =list.toString();
			//String tfc_id = "64010000"; //运管ID
//			String org_yid = "61";//在oracle数据库中运管的ID
			//String yid = "";//在PPAS数据库中运管的ID
			/*String school_id = "";//在PPAS数据库中驾校的ID
			String tfc_id = "";
			String ay_id = "";
			String zid = "";*/
			String[] ay_idArray = new String[list.size()];
			for (int i = 0; i < list.size(); i++) {
				ay_idArray[i] = list.get(i).toString();
			}
			//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 {
					try {
						rs.next();
						/*ay_id = ay_idArray[i];
						tfc_id = rs.getString("tfc_id");
						school_id = rs.getString("school_id");
						zid = tfc_id.substring(0,6);*/
						final String ay_id = ay_idArray[i].trim();
						final String tfc_id = rs.getString("tfc_id");
						final String zid = rs.getString("tfc_id").substring(0, 6);
						nx_zk(ay_id, zid);
						System.out.println("执行完成：ayid驾校" + ay_id);
					} catch (Exception e) {
						System.err.println("执行失败："+e.getMessage());
						continue;//e.printStackTrace();
					}
					/*fixedThreadPool.execute(new Runnable() {
						@Override
						public void run() {
							try {
								nx_zk(ay_id, zid);
							} catch (Exception e) {
								e.printStackTrace();
							}
						}
					});*/
				}
			}

			//Thread.sleep(8*60*60*1000);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
}