package ay.zk; import java.sql.Connection; import ay.db.ConnTools; import ay.db.DbUtil_sqlserver; public class Zk_ayjxjy_sx_ll { /* //山西吕梁转库 http://cqwt.safeluck.com:8085/cy_ayjp/login/login gl 65472925 113.207.65.87 sa mxYtgv_4850Pqed_bxma (1)用ayadmin登录， 组织管理-> 运管管理 创建 14110000 山西吕梁市培训中心 授用户角色权限 建用户给角色权限 (2)用运管身份登录， 组织管理->驾校管理 14110001 - 14110016 (3)用ayadmin登录， 给运管挂大纲(教学大纲管理->教学大纲 添加从业类型 , 然后点击一条从业类型记录，点右边“使用已有方案”,选一条已有方案，点击左上角“选择方案“) (4)用ayadmin登录， 给运管挂方案(题库管理->方案管理->右方选择运管->乐山运管处 ->查询 左方选择方案->点击添加方案 ->保存方案) (5)用ayadmin登录， 题库管理->方案管理-> 选择运管 - 查询 - 右边选择方案 - 添加方案 - 保存方案 (6)用运管身份登录 ， 组织管理->驾校管理 选择驾校，点修改，经营范围(从业类型)，全选，保存 50600000 吕梁市培训中心 14110000 50600001 孝义安顺驾校 14110001 50600002 交城交通驾校 14110002 50600003 柳林县欣达驾校 14110003 50600004 石楼县安泰驾校 14110004 50600005 临县星海驾校 14110005 50600006 兴县宏恩驾校 14110006 50600007 岚县通翔驾校 14110007 50600008 汾阳市省运驾校 14110008 50600009 汾阳市阳光驾校 14110009 50600010 文水县萨能驾校 14110010 50600011 文水县吉宏达驾校 14110011 50600012 吕梁市驾校 14110012 50600013 中阳县瑞昶驾校 14110013 50600014 交口县道尔驾校 14110014 50600015 方山县相当好驾校 14110015 50600016 汾阳市汾顺机动车驾驶员培训学校 14110016 50600000 GL 系统管理员 65472925 50600001 GL 系统管理员 02365472925 ...... */ public static void main(String args[]) { try{ Connection conn_sqlserver_ayjxjy = ConnTools.make_sqlserver_conn(); Connection conn_ppas_ayjxjy = ConnTools.make_ppas_ayjxjy_conn(); String del_where=""; String tfc_id="14110000"; //50600000 吕梁市培训中心 //String ay_id="14110001"; //50600001 - 50600016 String zid="14110"; DbUtil_sqlserver d = new DbUtil_sqlserver(); /* //转非管理员用户 create_user_name暂存ay_id d.insert(conn_sqlserver_ayjxjy, conn_ppas_ayjxjy, "sys_user", "select '' id,pwd password,yhm,'1411'+substring(cast(ay_id as varchar),5,8) ay_id,getdate() create_time,'normal' status,0 is_deleted,0 buildin,1 type,'1411'+substring(cast(ay_id as varchar),5,8) ayid from sys_user where ay_id like '5060%' and yhm!='GL' ", "insert into sys_user(id,password,username,organization_id,create_time,status,isdeleted,buildin,type,create_user_name) ", "delete from sys_user where organization_id in (select id from sys_school where ay_id like '"+zid+"%') and username not like '"+zid+"%' "); //organization_id d.update(conn_ppas_ayjxjy, "update sys_user a set a.organization_id=b.id from sys_school b where a.organization_id=b.ay_id and a.organization_id like '"+zid+"%' "); //mysql update sys_user a inner join sys_school b on a.organization_id=b.ay_id and a.organization_id like '"+zid+"%' set a.organization_id=b.id //sys_user_role d.update(conn_ppas_ayjxjy, "delete from sys_user_role where id in (select id from sys_user where create_user_name like '"+zid+"%' and name not like '"+zid+"%')"); d.update(conn_ppas_ayjxjy, "insert into sys_user_role(id,roleid,userid) select a.id id,a.id roleid,b.id userid from sys_role a,sys_user b where a.code=b.create_user_name and b.username not like '"+zid+"%' and b.create_user_name like '"+zid+"%'"); //BASE_STD_ARCHIVES 学员基础档案信息 d.insert(conn_sqlserver_ayjxjy, conn_ppas_ayjxjy, "base_std_archives", "select cast(id as varchar) id1,'1411'+substring(cast(ay_id as varchar),5,8) ay_id,name,'身份证' CREDENTIALS_TYPE,sfz_no,birthday,sfz_address,link_address,'' LANDLINE,tel_mov,sfz_area_code," + "pic,'' VEHICLE_TYPE,state,'' REMARK,create_unit_name,create_user_name,create_time,nationality,'汉' NATION,'' SOURCE,'141100' ZID,'' user_id from fil_stdt_base where ay_id like '5060%' and sfz_no is not null ", "insert into base_std_archives(ID,AY_ID,NAME,CREDENTIALS_TYPE,CREDENTIALS_NO,BIRTHDAY,ADDRESS,NOW_ADDRESS,LANDLINE,PHONE,ZIP_CODE,PIC,VEHICLE_TYPE,STATE,REMARK,CREATE_SCHOOL_NAME,CREATE_USER_NAME,CREATE_TIME,NATIONALITY,NATION,SOURCE,ZID,USER_ID) ", "delete from base_std_archives where "+del_where+" ay_id like '"+zid+"%' "); //BASE_STD_EXTEND 学员档案扩展表 C1 客车(29) C2货车(29) A1危险品(28) d.insert(conn_sqlserver_ayjxjy, conn_ppas_ayjxjy, "base_std_extend", "select cast(id as varchar) id1,cast(id as varchar) stdt_id,case drv_cartype when 'C1' then 'ky' when 'C2' then 'hy' when 'A1' then 'wy' end case_code, " + "case drv_cartype when 'C1' then '道路旅客运输驾驶员' when 'C2' then '普通货物运输驾驶员' when 'A1' then '危险品货物运输驾驶员' end case_name," + "case drv_cartype when 'C1' then '道路旅客运输驾驶员' when 'C2' then '普通货物运输驾驶员' when 'A1' then '危险品货物运输驾驶员' end case_type," + " sfz_no,getdate() create_time from fil_stdt_base where ay_id like '5060%' ", "insert into base_std_extend(id,stdt_id,case_code,case_name,case_type,case_no,create_time)", "delete from base_std_extend where "+del_where+" stdt_id in (select id from base_std_archives where ay_id like '"+zid+"%') "); //TR_PERIOD_JXJY 培训周期 d.insert(conn_sqlserver_ayjxjy, conn_ppas_ayjxjy, "tr_period_jxjy", "select '' id,'1411'+substring(cast(ay_id as varchar),5,8) ay_id,cast(id as varchar) stdt_id,'' remark,getdate() create_time,'14110001' tfc_id,'201601' grade,'00' state,'1' pay_state from fil_stdt_base where ay_id like '5060%' ", "insert into tr_period_jxjy(id,ay_id,stdt_id,remark,create_time,tfc_id,grade,state,pay_state)", "delete from base_std_extend where "+del_where+" stdt_id in (select id from base_std_archives where ay_id like '"+zid+"%') "); //INSERT INTO TR_PERIOD_JXJY ( ID, AY_ID, STDT_ID, REMARK,CREATE_TIME, AUD_TIME, AUD_UID, AUD_REMARK, OVER_TIME, OVER_UID, OVER_REMARK, EXAM_TIME, EXAM_UID, EXAM_REMARK, PICTURE, TFC_ID, ENTRY_DATE, GRADE, STATE, PAY_STATE ) //TR_CASE_TYPE_JXJY 学员培训类型 tr_case_type_jxjy 用yz_id暂存 stdt_id d.insert(conn_sqlserver_ayjxjy, conn_ppas_ayjxjy, "tr_case_type_jxjy", "select '' id,cast(id as varchar) yz_id,case drv_cartype when 'C1' then '道路旅客运输驾驶员' when 'C2' then '普通货物运输驾驶员' when 'A1' then '危险品货物运输驾驶员' end case_name,sfz_no case_no,sfz_no,in_date," + "case drv_cartype when 'C1' then 'ky' when 'C2' then 'hy' when 'A1' then 'wy' end case_code,case drv_cartype when 'C1' then '道路旅客运输驾驶员' when 'C2' then '普通货物运输驾驶员' when 'A1' then '危险品货物运输驾驶员' end case_type,'1' is_need,'20' status from fil_stdt_base where ay_id like '5060%' ", "insert into tr_case_type_jxjy(id,yz_id,case_name,case_no,sfz_no,first_time,case_code,case_type,is_need,status)", "delete from tr_case_type_jxjy where "+del_where+" yz_id in (select id from base_std_archives where ay_id like '"+zid+"%') "); //tp_id d.update(conn_ppas_ayjxjy, "update tr_case_type_jxjy a set tp_id=b.id from tr_period_jxjy b where a.yz_id=b.stdt_id and b.ay_id like '"+zid+"%' "); //tr_studytimetotal_jxjy 学时总表 用cur_km暂存 stdt_id d.insert(conn_sqlserver_ayjxjy, conn_ppas_ayjxjy, "tr_studytimetotal_jxjy", "select '' id,all_minute,beg_date,'1411'+substring(cast(ay_id as varchar),5,8) ay_id,cast(stdt_id as varchar) stdt_id from ph_stdt_time where ay_id like '5060%' and phkm='km1' ", "insert into tr_studytimetotal_jxjy(id,km1,created,zid,cur_km)", "delete from tr_studytimetotal_jxjy where "+del_where+" zid like '"+zid+"%' "); //tct_id d.update(conn_ppas_ayjxjy, "update tr_studytimetotal_jxjy a set a.tct_id=b.id from tr_case_type_jxjy b where a.cur_km=b.yz_id and a.zid like '"+zid+"%' "); //TR_STUDYTIME_JXJY 学时明细表 d.insert(conn_sqlserver_ayjxjy, conn_ppas_ayjxjy, "tr_studytime_jxjy", "select cast(id as varchar) id1,'1411'+substring(cast(ay_id as varchar),5,8) ay_id,stdt_id,beg_day,beg_minute,beg_time,end_time,time_type,tr_minute,collect_date,'1' state from ph_tr_data_org where ay_id like '5060%' ", "insert into tr_studytime_jxjy(id,ay_id,stdt_id,beg_day,beg_minute,beg_time,end_time,time_type,tr_minute,collect_date,state)", "delete from tr_studytime_jxjy where "+del_where+" ay_id like '"+zid+"%' "); //tp_id d.update(conn_ppas_ayjxjy, "update tr_studytime_jxjy a set a.tp_id=b.id from tr_period_jxjy b where to_char(a.stdt_id)=b.stdt_id and a.ay_id like '"+zid+"%' "); //tct_id d.update(conn_ppas_ayjxjy, "update tr_studytime_jxjy a set a.tfc_id=b.id from tr_case_type_jxjy b where to_char(a.stdt_id)=b.yz_id and a.ay_id like '"+zid+"%' "); //TR_VIDEO_LOG_JXJY 用户视频日志(无) //TR_USER_VIDEO_JXJY 用户当前观看视频(无) //TR_STD_EXAM_JXJY 考试记录(无) d.update(conn_sqlserver_ayjxjy, "select sfz,max(id) id into #exam_stdt_1 from exam_stdt group by sfz "); d.insert(conn_sqlserver_ayjxjy, conn_ppas_ayjxjy, "tr_std_exam_jxjy", "select '' id,'1411'+substring(cast(a.ay_id as varchar),5,8) ay_id,b.sfz_no,'1' status,b.sfz_no,b.name,cast(a.score as varchar),a.start_time,'1' is_ok,a.start_time," + " case case_type when 'C1' then '道路旅客运输驾驶员' when 'C2' then '普通货物运输驾驶员' when 'A1' then '危险品货物运输驾驶员' end exam_type from exam_stdt a,fil_stdt_base b where a.stdt_id=b.id and b.ay_id like '5060%' and a.finish='2' and a.id in (select id from #exam_stdt_1) ", "insert into tr_std_exam_jxjy(id,ay_id,case_code,status,sfz_no,student_name,result,exam_time,is_ok,create_time,exam_type)", "delete from tr_std_exam_jxjy where "+del_where+" ay_id like '"+zid+"%' "); //tct_id d.update(conn_ppas_ayjxjy, "update tr_std_exam_jxjy a set a.tct_id=b.id from tr_case_type_jxjy b where a.sfz_no=b.sfz_no and a.ay_id like '"+zid+"%' "); */ /* //sys_user 用户表 -------------------------- 只转没培训完的 123456 d.insert(conn_sqlserver_ayjxjy, conn_ppas_ayjxjy, "sys_user", "select '' id,'e10adc3949ba59abbe56e057f20f883e' pwd,a.sfz_no username,a.sfz_no,a.tel_mov,'normal',0,0,0 from fil_stdt_base a,ph_stdt_time b where a.id=b.stdt_id and b.phkm='km9' and a.ay_id like '5060%' and a.state='00' and b.all_minute<1440 ", "insert into sys_user(id,password,username,sfz_no,phone,status,isdeleted,buildin,type)", "delete from sys_user where "+del_where+" sfz_no in (select sfz_no from base_std_archives where ay_id like '"+zid+"%' ) "); // 更新user_id d.update(conn_ppas_ayjxjy, "update base_std_archives a set a.user_id=b.id from sys_user b where a.credentials_no=b.sfz_no and a.ay_id like '"+zid+"%'"); //更新 cur_km d.update(conn_ppas_ayjxjy, "update tr_studytimetotal_jxjy set cur_km=round((km1/need_time)*100) where length(cur_km)>10 "); */ conn_sqlserver_ayjxjy.close(); conn_ppas_ayjxjy.close(); }catch(Exception ex){ex.printStackTrace();} } }