viewkaoqinservlet.java

学生考勤管理系统————分为课程选择

package com.ccniit.kaoqin.servlet;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.jsp.JspFactory;
import javax.servlet.jsp.PageContext;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.lxh.smart.SmartUpload;
import org.lxh.smart.SmartUploadException;

import com.ccniit.kaoqin.db.classes.Classes;
import com.ccniit.kaoqin.db.classes.ClassesDAO;
import com.ccniit.kaoqin.db.course.Course;
import com.ccniit.kaoqin.db.course.CourseDAO;
import com.ccniit.kaoqin.db.seekaoqin.SeeKaoQinView;
import com.ccniit.kaoqin.db.seekaoqin.SeeKaoQinViewDAO;
import com.ccniit.kaoqin.db.student.Student;
import com.ccniit.kaoqin.db.student.StudentDAO;
import com.ccniit.kaoqin.db.teacher.Teacher;
import com.ccniit.kaoqin.db.util.TimeAndDate;


@SuppressWarnings("serial")
public class ViewKaoQinServlet extends HttpServlet {

	
	
	
	
	
public String getFileName(int course_id,int class_id){
	String excelname=null;
	 try {
		Classes classes=new Classes();
		  ClassesDAO classesDAO=new ClassesDAO();
		  classes=classesDAO.getClassById(class_id);
		 CourseDAO courseDAO=new CourseDAO();
			Course course=   courseDAO.getCourseByIdClassandCourseId(course_id, class_id);
			

			 excelname=course.getSelectCourse().getSelectCourse_year()+"年度第"+course.getSelectCourse().getSelectCourse_term()+"学期"+course.getCourse_name()+"课程"+classes.getClass_name()+"考勤表";
	} catch (ClassNotFoundException e) {
		// TODO 自动生成 catch 块
		e.printStackTrace();
	} catch (SQLException e) {
		// TODO 自动生成 catch 块
		e.printStackTrace();
	}
	
	return excelname;
	
}
	
	
	/**
	 *  创建Excel
	 * @param course_id
	 * @param class_id
	 * @throws IOException
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 * @throws ParseException
	 */
	@SuppressWarnings("deprecation")
	public  void Creat(int course_id,int class_id,String appPath) throws IOException, ClassNotFoundException, SQLException, ParseException{
	
		 
	     StudentDAO	studentDAO=new StudentDAO();
	     SeeKaoQinViewDAO	seeKaoQinViewDAO=new SeeKaoQinViewDAO();
	
	
		  Classes classes=new Classes();
		  ClassesDAO classesDAO=new ClassesDAO();
		  classes=classesDAO.getClassById(class_id);
		 ArrayList students=studentDAO.getStudentByClassId(class_id);
          
		 CourseDAO courseDAO=new CourseDAO();
		Course course=   courseDAO.getCourseByIdClassandCourseId(course_id, class_id);
		

		String excelname=course.getSelectCourse().getSelectCourse_year()+"年度第"+course.getSelectCourse().getSelectCourse_term()+"学期"+course.getCourse_name()+"课程"+classes.getClass_name()+"考勤表";
		//		 创建新的Excel工作簿 
		   
		HSSFWorkbook workbook = new HSSFWorkbook(); 
//	在excel中新建一个工作表
		HSSFSheet  sheet=workbook.createSheet(classes.getClass_name());
//		创建第一行
	    HSSFRow row = sheet.createRow(0);

//	创建第一列
	    HSSFCell cell = row.createCell((short)0);

	    sheet.addMergedRegion(new Region(0,(short)0,0,(short)6));
	    cell.setCellValue(excelname);
    
	    //创建第2行
	    row=sheet.createRow(1);
	    
		 //创建第2列
		cell = row.createCell((short)0);
		//合并单元格
		sheet.addMergedRegion(new Region(1,(short)0,2,(short)0));
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("序号");

		  //--------数据------
		//创建第二行
		 
		 cell = row.createCell((short)1);
		 sheet.addMergedRegion(new Region(1,(short)1,2,(short)1));
		 
		 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("学号");
			//-------------数据
			
		    //第二行第三列为姓名
			cell=row.createCell((short)2);
			 sheet.addMergedRegion(new Region(1,(short)2,2,(short)2));
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("姓名");
			//-------------数据
			
		  cell=row.createCell((short)3);
		  cell.setCellValue("日期");
		  
		  
		   for(int i=0;i<1;i++){
			   Student student=(Student)students.get(i);
			   ArrayList seeKaoQin1= seeKaoQinViewDAO.getSeeKaoQinViewById(course_id, class_id, student.getStudent_NO());
		  
			   for(int j=4;j<seeKaoQin1.size()+4;j++){
				   
				   cell=row.createCell((short)j);
				   SeeKaoQinView seeKaoQinView=(SeeKaoQinView)seeKaoQin1.get(j-4);
				   String month=seeKaoQinView.getKaoqin_date();
					cell.setCellValue(month.substring(5,10));
				   
			      }
		   
		   }
		 
		  
			//-------数据---------
		   
           //创建第2行
		    row=sheet.createRow(2);
		    
		    cell = row.createCell((short)3);
		    
		    cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("星期/节次");
			
			
			
			  for(int i=0;i<1;i++){
				   Student student=(Student)students.get(i);
				   ArrayList seeKaoQin1= seeKaoQinViewDAO.getSeeKaoQinViewById(course_id, class_id, student.getStudent_NO());
			  
				   for(int j=4;j<seeKaoQin1.size()+4;j++){
					   
					   cell=row.createCell((short)j);
					   SeeKaoQinView seeKaoQinView=(SeeKaoQinView)seeKaoQin1.get(j-4);
					   String month=seeKaoQinView.getKaoqin_date();
					   String jieci=TimeAndDate.getDateOfMonday(month)+"/"+seeKaoQinView.getKaoqin_time();
						cell.setCellValue(jieci);
					   
				      }
			   
			   }
			
		   //数据的输入
			 for(int i=3;i<students.size()+3;i++){
					row=sheet.createRow(i);
					cell = row.createCell((short)0);
					cell.setCellValue(i-2);
					
					Student student=(Student)students.get(i-3);
					
					cell = row.createCell((short)1);
					cell.setCellValue(student.getStudent_NO());
					
					cell = row.createCell((short)2);
					cell.setCellValue(student.getStudent_name());
					
			 ArrayList seeKaoQin1= seeKaoQinViewDAO.getSeeKaoQinViewById(course_id, class_id, student.getStudent_NO());
			 for(int j=0;j<seeKaoQin1.size();j++){
			 SeeKaoQinView seeKaoQinView=(SeeKaoQinView)seeKaoQin1.get(j);
					
					cell = row.createCell((short)(j+4));
					
					  String kaoqin=seeKaoQinView.getKaoqin_data();
						 if(kaoqin.equals("到勤")){
							 cell.setCellValue("√"); 
						   }else if(kaoqin.equals("缺勤"))  {  
						    	 cell.setCellValue("X");
						   }else if(kaoqin.equals("迟到")){
							   cell.setCellValue("△"); 
						   }else if(kaoqin.equals("早退")){
							   cell.setCellValue("○");   
						    
						   }else if(kaoqin.equals("病假")){ 
							   cell.setCellValue(" ▲");     
						    }else if(kaoqin.equals("事假")){ 
							 cell.setCellValue(" ■");  
						    } 
					}	
			 }
	 
	
	
	 
	 String outpath = appPath +"kaoqinExcel" +File.separator+  excelname+ ".xls";

			 
			 System.out.println("bushi:"+outpath);
			 
	
	    //新建一输出流
	    FileOutputStream fout = new FileOutputStream(outpath);
	    
	    //存盘
	    workbook.write(fout);
	    
	    fout.flush();
	    //结束关闭
	    fout.close();
        
		   
	   }
		
	/**
	 * The doGet method of the servlet. <br>
	 *
	 * This method is called when a form has its tag value method equals to get.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 * @throws SQLException 
	 * @throws ClassNotFoundException 
	 * @throws ParseException 
	 */
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		 String functionStr =request.getPathInfo();
		 CourseDAO courseDAO=null;
		 ClassesDAO classessDAO=null;
		 StudentDAO studentDAO=null;
		 SeeKaoQinViewDAO seeKaoQinViewDAO=null;
         try {
			if(functionStr.equals("/OpenMain")){
				 
				 
				 String successPage="/kaoqin/viewKaoqin.jsp";
				 
				      Teacher teacher=(Teacher)request.getSession().getAttribute("teacher");
				 	  courseDAO=new CourseDAO();
				 	  ArrayList courses=new ArrayList();
			          courses=courseDAO.getCourseByTeacherId(teacher.getTeacher_id());
			          request.setAttribute("courses", courses); 
			          request.getRequestDispatcher(successPage).forward(request, response);
				 
				 
			  
			 }else if(functionStr.equals("/SelectClass")){
                   String successPage="OpenMain";
				 
				 String course_idStr=request.getParameter("course_id");
				 int course_id=Integer.parseInt(course_idStr);
				 
				  classessDAO=new ClassesDAO();
				  ArrayList classess=new ArrayList();
				  classess=classessDAO.getClassesByCourseId(course_id);
				   
				  
				  request.setAttribute("classess", classess);
				  
				
				  request.setAttribute("select", course_idStr);
				  
				  request.getRequestDispatcher(successPage).forward(request, response);
 
				 
			 }else if(functionStr.equals("/List")){
				 String successPage="SelectClass";
				 String course_idStr=request.getParameter("course_id");
				 int course_id=Integer.parseInt(course_idStr);
				 
				 String class_idStr=request.getParameter("class_id");
				 int class_id=Integer.parseInt(class_idStr);
				 
				 studentDAO=new StudentDAO();
				 seeKaoQinViewDAO=new SeeKaoQinViewDAO();
				 
				 ArrayList students=studentDAO.getStudentByClassId(class_id);
				 for(int i=0;i<students.size();i++){
					 Student student=(Student)students.get(i);
					 
					 ArrayList seeKaoQin=seeKaoQinViewDAO.getSeeKaoQinViewById(course_id, class_id,student.getStudent_NO());
					 request.setAttribute("seeKaoQin_"+student.getStudent_NO(), seeKaoQin);
				 }
					  
				 
					  
				  
				  
				  request.setAttribute("class_ids", class_idStr);
				 
				 request.setAttribute("students", students);	
				 
				 request.getRequestDispatcher(successPage).forward(request, response);
				 
				 
			 }else if(functionStr.equals("/SaveExcle")){
				 
				 String successPage="DownLoad";
			     String course_idStr=request.getParameter("course_id");
				 int course_id=Integer.parseInt(course_idStr);
				 
				 
				 String class_idStr=request.getParameter("class_id");
				 int class_id=Integer.parseInt(class_idStr);
				 
				 //---------------------------------------------------------------------
				String appPath = request.getSession().getServletContext().getRealPath("/");
				 //-----------------------------------------------------------------------
				 
					 //创建Excel
					Creat(course_id, class_id,appPath);
					String filename=getFileName(course_id, class_id);
					
					String path=appPath + "kaoqinExcel" +File.separator+ filename+ ".xls";
					
					  
				    request.setAttribute("download",path);
				 request.getRequestDispatcher(successPage).forward(request, response);
				 
				 
				 
				 
			 }else if(functionStr.equals("/DownLoad")){
				 
				// String successPage="OpenMain";
				 String downfile=(String)request.getAttribute("download");
            
                   
                    
				// downfile=new String (downfile.getBytes("ISO-8859-1"),"gb2312");
			
				 SmartUpload smart=new SmartUpload();
                 
				 JspFactory _jspxFactory = null;
			       PageContext pageContext = null;
			       _jspxFactory = JspFactory.getDefaultFactory();
			       pageContext = _jspxFactory.getPageContext(this,request,response,"",true,8192,true);
                  smart.initialize(pageContext);
                  smart.downloadFile(downfile,"application/vnd.ms-excel","kaoqin.xls");
				 
                 // pageContext.forward("/kaoqin/viewKaoqin.jsp");
                  return;
                 // request.getRequestDispatcher(successPage).forward(request, response);
				 
			 }
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} catch (ParseException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} catch (SmartUploadException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} finally{
			try {
				if(courseDAO!=null)
					courseDAO.release();
				if(classessDAO!=null)
					classessDAO.release();
				if(studentDAO!=null)
					studentDAO.release();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
	}

	/**
	 * The doPost method of the servlet. <br>
	 *
	 * This method is called when a form has its tag value method equals to post.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 */
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		  request.setCharacterEncoding("gb2312");
	      this.doGet(request, response);
	}

}