package ay.zk;

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

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;

public class Zk_ayth_hn {
    /**
     * 福建在线理论转库程序
     * <p>
     * 步骤:
     * 1、为对应运管在数据库中复制一份参数
     * 2、为对应运管在数据库中复制一份大纲
     * 3、为对应运管在数据库中复制一套studytimeneed
     * 4、运行数据库中create_new_exam_paper的存储过程生成试卷
     * 5、为对应运管在数据库中复制一套tfc_teach_project
     */
    private static void hn_zk(String ay_id, String tfc_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();
//------------------------------------------------------------user_pay 学习卡绑定记录表---------------------------------------------------------------------//
        //导入驾校绑卡记录
//        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-08-30 00:00:00','yyyy-MM-dd hh24:mi:ss') )");
//------------------------------------------------------------card   学习卡表---------------------------------------------------------------------//
        //导入驾校已激活的学习卡
//        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-08-30 00:00:00','yyyy-MM-dd hh24:mi:ss') )");
//------------------------------------------------------------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-09-06 00:00:00','yyyy-MM-dd hh24:mi:ss')) ");
////		//------------------------------------------------------------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),'normal',1,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 + "' and create_time<to_date('2017-09-06 00:00:00','yyyy-MM-dd hh24:mi:ss'))");
////		
////		//------------------------------------------------------------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>1498814400 ",
                "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 + "'");
//		
//		//------------------------------------------------------------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,zname,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-09-06 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)", 
				"");*/
    }

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

            ResultSet rs = d.query(conn_oracle_ayth_zk, "select ay_id from school where tfc_id = '41650000' order by ay_id");
            List list = new ArrayList();
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            while (rs.next()) {
                Map rowData = new HashMap();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(md.getColumnName(i), rs.getObject(i));
                }
                list.add(rowData.get("AY_ID"));
            }
//	        System.out.println(list.get(0));
            String ay_ids = list.toString();
            ay_ids = ay_ids.substring(1, ay_ids.length() - 1);
//			String ay_ids = "41050007";  //驾校AYID
            String ay_id = "";
            String tfc_id = "41650000"; //运管ID
//			String org_yid = "61";//在oracle数据库中运管的ID
            String yid = "";//在PPAS数据库中运管的ID
            String school_id = "";//在PPAS数据库中驾校的ID
            final String zid = "416500";

            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 rs1 = 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].trim() + "'");
                if (rs1.getRow() > 1) {
                    throw new Exception("AYID:'" + ay_idArray[i] + "'有多条记录！");
                } else {
                    rs1.next();
                    ay_id = ay_idArray[i].trim();
                    System.out.println(ay_id);
                    final String j = ay_id;
                    tfc_id = rs1.getString("tfc_id");
                    school_id = rs1.getString("school_id");
					/*try{
						hn_zk(j, "41650000",zid);
					}catch(Exception e){
						e.printStackTrace();
					}*/
                    fixedThreadPool.execute(new Runnable() {
                        @Override
                        public void run() {
                            try {
                                hn_zk(j, "41650000", zid);
                                Thread.sleep(10);
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                        }
                    });
                    System.out.println("执行完成：ayid驾校" + ay_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,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+"' and to_char(create_time,'yyyy-MM-dd') < '2017-06-27' ", 
//					"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 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,'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),'normal',0,0,'"+zid+"' from users u,student st where u.\"UID\" = st.\"UID\" and st.state = '00' and to_char(st.create_time,'yyyy-MM-dd') < '2017-06-27' 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,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 between 1495814400 and 1498492800 ",
//					"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 to_char(st.create_time,'yyyy-MM-dd') < '2017-06-27' 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+"')");
////					" 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+"' ");
////					"");
            //挂机等待线程结束
			//Thread.sleep(8*60*60*1000);
            System.exit(0);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}