user_listexcel.jsp

来自「远程教育系统中完整的学生管理系统,包括学生注册,学生管理等最新代码,推荐」· JSP 代码 · 共 728 行 · 第 1/2 页

<%
	/*---------------------------------------------
	功能描述:学生excel报表
	编写时间:2003-08-20
	编写人:侯雄飞
	email:[e-mail]
	
	修改情况记录
	修改时间：  修改内容：  修改人：
	-----------------------------------------------*/
%>

<%@ page language="java" %>
<%@ page contentType="text/html;charset=gb2312" %>

<%
//////////////////////////////////////////////////////////////
// Privilege Guarding

	String 	PrivXP="浏览学生";
	String 	PrivME="00104";
%>
<%@ include file="../pub/privGuarding.jsp" %>	
<%
// Privilege Guarding
//////////////////////////////////////////////////////////////
%>

<jsp:useBean id="work" scope="application" class="com.dbConnection.dbpool"/>

<%!
	//判断字符串为空的话，赋值为"不详"
	String fixnull(String str)
	{
	    if(str == null || str.equals("null") || str.equals("null"))
			str = "不详";
			return str;
	
	}
	String fixnull2(String str)
	{
	    if(str == null || str.equals("null") || str.equals("null"))
			str = "0";
			return str;
	
	}
%>

<%
	String site_id = request.getParameter("site_id");
	String major_id = request.getParameter("major_id");
	String edu_type_id = request.getParameter("edu_type_id");
	String grade_id = request.getParameter("grade_id");
	String name = request.getParameter("name");
	String reg_no = request.getParameter("temp_reg_no");
	String id_card = request.getParameter("id_card");
	String phone = request.getParameter("phone");
	//out.print(grade_id);
	String[] info = request.getParameterValues("info");
	String[] infoname = new String[100];
	
	String major_name = "";
	String grade_name = "";
	String edu_type = "";
	String site_name = "";
	
	String sql_major = "";
	String sql_grade = "";
	String sql_site = "";
	String sql_edu_type = "";
	String msg = "";
	String link="";
	String condition = "";
	String sql = "";
	boolean is_first = true;
	String select_str = "";
	
	int totalItems = 0;
	com.dbConnection.MyResultSet rs;
	if(info != null)
	{
		for(int i = 0 ;i < info.length;i++)
		{
			String colsvalue = info[i].substring(0,info[i].indexOf("|"));
			select_str = select_str +","+colsvalue;
			String colsname = info[i].substring(info[i].indexOf("|")+1,info[i].length());
			infoname[i] = colsname;
		}
		select_str = select_str.substring(1,select_str.length());
	}
	//out.print(select_str);
	
	sql = "select name from lrn_major_info where id = '" + major_id + "'";
	totalItems = work.countselect(sql);
	if(totalItems < 1)
		major_name = "所有专业";
	else
	{
		rs = work.executeQuery(sql);
		while(rs.next())
		{
			major_name = rs.getString("name");
		}
		work.close(rs);
	}	
	
	sql = "select name from lrn_grade_info where id = '" + grade_id + "'";
	totalItems = work.countselect(sql);
	if(totalItems < 1)
		grade_name = "所有年级";
	else
	{
		rs = work.executeQuery(sql);
		while(rs.next())
		{
			grade_name = rs.getString("name");
		}
		work.close(rs);
	}	
	
	sql = "select name from lrn_edu_type where id = '" + edu_type_id + "'";
	totalItems = work.countselect(sql);
	if(totalItems < 1)
		edu_type = "所有层次";
	else
	{
		rs = work.executeQuery(sql);
		while(rs.next())
		{
			edu_type = rs.getString("name");
		}
		work.close(rs);
	}	
	
	sql = "select name from lrn_site_info where id = '" + site_id + "' order by name";
	totalItems = work.countselect(sql);
	if(totalItems < 1)
		site_name = "所有校外学习中心";
	else
	{
		rs = work.executeQuery(sql);
		while(rs.next())
		{
			site_name = rs.getString("name");
		}
		work.close(rs);
	}	
	
	msg = site_name + " " + major_name + " " +edu_type + " " + grade_name + " " + "学员列表";
	
	//生成查询教学站的语句	
	if(site_id != null && !site_id.equals(""))
	{
		sql_site = "(select id,name from lrn_site_info where id='" + site_id + "') s";
	}
	else
	{
		sql_site = "(select id,name from lrn_site_info) s";
	}
	
	//生成查询专业的语句
	if(major_id != null && !major_id.equals(""))
	{
		sql_major = "(select id,name from lrn_major_info where id='" + major_id + "') m";
	}
	else
	{
		sql_major = "(select id,name from lrn_major_info where id<>'0') m";
	}
		
	//生成查询层次的语句	
	if(edu_type_id != null && !edu_type_id.equals(""))
	{
		sql_edu_type = "(select id,name from lrn_edu_type where id='" + edu_type_id + "') e";
	}
	else
	{
		sql_edu_type = "(select id,name from lrn_edu_type) e";
	}
	
	//生成查询年级的语句			
	if(grade_id != null && !grade_id.equals(""))
	{
		sql_grade = "(select id,name from lrn_grade_info where id='" + grade_id + "') g";
	}
	else
	{
		sql_grade = "(select id,name from lrn_grade_info) g";
	}
	
	if(name != null && !name.equals(""))
	{
		condition = "name like '%" + name.trim() + "%'";
		is_first = false;
	}
	
	if (reg_no != null && !reg_no.equals(""))
	{
		if (is_first)
		{
			condition = "reg_no like '%" + reg_no.trim() + "%'";
		}
		else
		{
			condition = condition + " and reg_no like '%" + reg_no.trim() + "%'";
		}
		is_first = false;
	}
	
	if (id_card != null && !id_card.equals(""))
	{
		if (is_first)
		{
			condition = "id_card like '%" + id_card.trim() + "%'";
		}
		else
		{
			condition = condition + " and id_card like '%" + id_card.trim() + "%'";
		}
		is_first = false;
	}
	
	if (phone != null && !phone.equals(""))
	{
		if (is_first)
		{
			condition = "phone like '%" + phone.trim() + "%'";
		}
		else
		{
			condition = condition + " and phone like '%" + phone.trim() + "%'";
		}
		is_first = false;
	}
	
	//生成总SQL语句	
	if (is_first)
	{
		sql = "select id,u_name,password,id_card,email,phone,address,zip_address,gender,work_place,position,title,edu,red_date,comfirm_date,user_type,status,birthday,note,graduated_time,graduated_sch,graduated_major,folk,zzmm,hometown,degree_flag,nickname,reg_no,entrance_date,begin_date,alteration,remark,email_flag,m_name,g_name,e_name,s_name,s_id,oldsite from (select u.id as id,u.name as u_name,u.password as password,u.id_card as id_card,u.email as email,u.phone as phone,u.address as address,u.zip_address as zip_address,u.gender as gender,u.work_place as work_place,u.position as position,u.title as title,u.edu as edu,u.red_date as red_date,u.comfirm_date as comfirm_date,u.user_type as user_type,u.status as status,u.birthday as birthday,u.note as note,u.graduated_time as graduated_time,u.graduated_sch as graduated_sch,u.graduated_major as graduated_major,u.folk as folk,u.zzmm as zzmm,u.hometown as hometown,u.degree_flag as degree_flag,u.nickname as nickname,u.reg_no as reg_no,u.entrance_date as entrance_date,u.begin_date as begin_date,u.alteration as alteration,u.remark as remark,u.email_flag as email_flag,m.name as m_name,g.name as g_name,e.name as e_name,s.name as s_name,s.id as s_id,s.name as oldsite from (select id,name,password,id_card,email,phone,address,zip_address,gender,work_place,position,title,edu,red_date,comfirm_date,user_type,status,login_num,last_login_time,last_login_ip,birthday,site,note,graduated_time,graduated_sch,graduated_major,folk,zzmm,hometown,degree_flag,nickname,isdelete,isgraduated,reg_no,grade_id,entrance_date,begin_date,alteration,remark,email_flag,edu_type_id,major_id,oldsite from lrn_user_info where isgraduated = '0' and status = '0000') u," + sql_major + "," + sql_grade + "," + sql_edu_type + "," + sql_site + " where u.major_id=m.id and u.grade_id=g.id and u.site=s.id and u.edu_type_id=e.id)";
	}
	else
	{
		sql = "select id,u_name,password,id_card,email,phone,address,zip_address,gender,work_place,position,title,edu,red_date,comfirm_date,user_type,status,birthday,note,graduated_time,graduated_sch,graduated_major,folk,zzmm,hometown,degree_flag,nickname,reg_no,entrance_date,begin_date,alteration,remark,email_flag,m_name,g_name,e_name,s_name,s_id,oldsite from (select u.id as id,u.name as u_name,u.password as password,u.id_card as id_card,u.email as email,u.phone as phone,u.address as address,u.zip_address as zip_address,u.gender as gender,u.work_place as work_place,u.position as position,u.title as title,u.edu as edu,u.red_date as red_date,u.comfirm_date as comfirm_date,u.user_type as user_type,u.status as status,u.birthday as birthday,u.note as note,u.graduated_time as graduated_time,u.graduated_sch as graduated_sch,u.graduated_major as graduated_major,u.folk as folk,u.zzmm as zzmm,u.hometown as hometown,u.degree_flag as degree_flag,u.nickname as nickname,u.reg_no as reg_no,u.entrance_date as entrance_date,u.begin_date as begin_date,u.alteration as alteration,u.remark as remark,u.email_flag as email_flag,m.name as m_name,g.name as g_name,e.name as e_name,s.name as s_name,s.id as s_id,s.name as oldsite from (select id,name,password,id_card,email,phone,address,zip_address,gender,work_place,position,title,edu,red_date,comfirm_date,user_type,status,login_num,last_login_time,last_login_ip,birthday,site,note,graduated_time,graduated_sch,graduated_major,folk,zzmm,hometown,degree_flag,nickname,isdelete,isgraduated,reg_no,grade_id,entrance_date,begin_date,alteration,remark,email_flag,edu_type_id,major_id,oldsite from lrn_user_info where isgraduated = '0' and status = '0000') u," + sql_major + "," + sql_grade + "," + sql_edu_type + "," + sql_site + " where u.major_id=m.id and u.grade_id=g.id and u.site=s.id and u.edu_type_id=e.id)";
	}
	//out.print(sql);
	totalItems = work.countselect(sql);
	if(totalItems < 1)
	{
		out.print("没有符合要求的查询结果！");
		out.print("<a href=javascript:window.close()>关闭</a>");
		return;
	}
	rs = work.executeQuery(sql);
%>
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/html; charset=GB2312">
<meta name=ProgId content=FrontPage.Editor.Document>
<meta name=Generator content="Microsoft FrontPage 4.0">
<link rel=File-List href="./formtable_excel.files/filelist.xml">
<link href="../css/style.css" rel=stylesheet type=text/css>
</head>

<body>
<form name="stuinfo" method="post" action="user_listexcel.jsp">
<table width="80%" border="0" align="center" cellpadding="2" cellspacing="1" bgcolor="3F6C61">
	<tr>
		<td colspan="7" class="t08_bgwhite_p2">
			学生信息
		</td>
	</tr>
	<tr>
		<td class="t08_bgwhite_p2" >
		<input type="checkbox" name="info" value="address|地址" <%if(select_str.indexOf("address")>=0) out.print("checked");%>>地址
		</td>
		<td class="t08_bgwhite_p2">
		<input type="checkbox" name="info" value="id_card|身份证号" <%if(select_str.indexOf("id_card")>=0) out.print("checked");%>>身份证号
		</td>
		<td class="t08_bgwhite_p2">
		<input type="checkbox" name="info" value="gender|性别" <%if(select_str.indexOf("gender")>=0) out.print("checked");%>>性别
		</td>
		<td class="t08_bgwhite_p2">
		<input type="checkbox" name="info" value="birthday|出生日期" <%if(select_str.indexOf("birthday")>=0) out.print("checked");%>>出生日期
		</td>
		<td class="t08_bgwhite_p2">
		<input type="checkbox" name="info" value="email|Email" <%if(select_str.indexOf("email")>=0) out.print("checked");%>>Email
		</td>
		<td class="t08_bgwhite_p2">
		<input type="checkbox" name="info" value="entrance_date|入学时间" <%if(select_str.indexOf("entrance_date")>=0) out.print("checked");%>>入学时间
		</td>
		<td class="t08_bgwhite_p2">
		<input type="checkbox" name="info" value="zzmm|政治面貌" <%if(select_str.indexOf("zzmm")>=0) out.print("checked");%>>政治面貌
		</td>
		
	</tr>
	<tr>
		
		<td class="t08_bgwhite_p2">
		<input type="checkbox" name="info" value="hometown|籍贯" <%if(select_str.indexOf("hometown")>=0) out.print("checked");%>>籍贯
		</td>
		<!--<td class="t08_bgwhite_p2">
		<input type="checkbox" name="info" value="address|通信地址" <%if(select_str.indexOf("address")>=0) out.print("checked");%>>通信地址
		</td>-->
		<td class="t08_bgwhite_p2">
		<input type="checkbox" name="info" value="zip_address|邮政编码" <%if(select_str.indexOf("zip_address")>=0) out.print("checked");%>>邮政编码
		</td>
		<td class="t08_bgwhite_p2" > 
		<input type="checkbox" name="info" value="phone|联系电话" <%if(select_str.indexOf("phone")>=0) out.print("checked");%>>联系电话
		</td>
		<td class="t08_bgwhite_p2">
		<input type="checkbox" name="info" value="edu|入学前学历" <%if(select_str.indexOf("edu")>=0) out.print("checked");%>>入学前学历
		</td>
		<td class="t08_bgwhite_p2">
		<input type="checkbox" name="info" value="graduated_sch|原毕业学校" <%if(select_str.indexOf("graduated_sch")>=0) out.print("checked");%>>原毕业学校
		</td>
		<td class="t08_bgwhite_p2">
		<input type="checkbox" name="info" value="folk|民族"  <%if(select_str.indexOf("folk")>=0) out.print("checked");%>>民族
		</td>
		<td class="t08_bgwhite_p2">
		<input type="checkbox" name="info" value="oldsite|原校外学习中心"  <%if(select_str.indexOf("oldsite")>=0) out.print("checked");%>>原校外学习中心
		</td>
	</tr>
	<tr>
		
		<td class="t08_bgwhite_p2">
		<input type="checkbox" name="info" value="work_place|工作单位"  <%if(select_str.indexOf("work_place")>=0) out.print("checked");%>>工作单位
		</td>
		<td class="t08_bgwhite_p2">
		<input type="checkbox" name="info" value="position|职务" <%if(select_str.indexOf("position")>=0) out.print("checked");%>>职务
		</td>
		<td class="t08_bgwhite_p2">
		<input type="checkbox" name="info" value="title|职称" <%if(select_str.indexOf("title")>=0) out.print("checked");%>>职称
		</td>
		<td class="t08_bgwhite_p2" > 
		<input type="checkbox" name="info" value="begin_date|从事本专业开始时间" <%if(select_str.indexOf("begin_date")>=0) out.print("checked");%>>从事本专业开始时间
		</td>
		<td class="t08_bgwhite_p2">
		<input type="checkbox" name="info" value="alteration|学籍异动" <%if(select_str.indexOf("alteration")>=0) out.print("checked");%>>学籍异动
		</td>
		<td class="t08_bgwhite_p2">
		<input type="checkbox" name="info" value="graduated_time|毕业时间" <%if(select_str.indexOf("graduated_time")>=0) out.print("checked");%>>毕业时间
		</td>
		<td class="t08_bgwhite_p2">
		<input type="checkbox" name="info" value="graduated_major|原毕业专业" <%if(select_str.indexOf("graduated_major")>=0) out.print("checked");%>>原毕业专业
		</td>
	</tr>
	<tr>
		<td colspan="7"  class="t08_bgwhite_p2">
			<p align="center">
			<input type="hidden" name="major_id" value="<%=major_id%>">
			<input type="hidden" name="grade_id" value="<%=grade_id%>">
			<input type="hidden" name="edu_type_id" value="<%=edu_type_id%>">
			<input type="hidden" name="site_id" value="<%=site_id%>">
			<input type="hidden" name="name" value="<%=name%>">
			<input type="hidden" name="reg_no" value="<%=reg_no%>">
			<input type="hidden" name="id_card" value="<%=id_card%>">
			<input type="hidden" name="phone" value="<%=phone%>">
			<input type="submit" name="sub" value="查看">
			</p>
		</td>
	</tr>