updatedataaction.java

基于JAVA的学生就业信息网 实现对信息浏览 检索 审核 修改和删除

/*
 * Created on 2006-7-24
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package org.ug.sztz.webview.structs.actions.oa;

import java.io.FileInputStream;
import java.net.InetAddress;
import java.util.HashMap;
import java.util.Vector;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

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.poifs.filesystem.POIFSFileSystem;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.ug.sztz.domain.assistant.DateProcessor;
import org.ug.sztz.domain.assistant.UrlConvertor;

/**
 * @author Skywalker
 * 
 * TODO To change the template for this generated type comment go to Window -
 * Preferences - Java - Code Style - Code Templates
 */
public class UpdateDataAction extends BaseAction {

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.apache.struts.action.Action#execute(org.apache.struts.action.ActionMapping,
	 *      org.apache.struts.action.ActionForm,
	 *      javax.servlet.http.HttpServletRequest,
	 *      javax.servlet.http.HttpServletResponse)
	 */
	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		// TODO Auto-generated method stub
		InetAddress myAddress = InetAddress.getLocalHost();
		String IP = myAddress.getHostAddress();//获取本机IP
		UrlConvertor urlConvertor = new UrlConvertor(this.getServlet()
				.getServletConfig());
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
				urlConvertor.getAbsoluteFile("/upload/", "data.xls")));//打开上传的文件
		Vector vector = new Vector();
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheetAt(0);
		HSSFRow row = sheet.getRow(0);
		for (int i = 0; row.getCell((short) i) != null; i++) {
			HSSFCell cell = row.getCell((short) i);
			vector.add(cell.getStringCellValue());
		}//取得列名
		int colnum = vector.size();
		int[] flags = new int[colnum];//标志位，为1时字段在bysbase中，为2时字段在bysjy中
		int xhnum = -1;//学号所在的列
		for (int i = 0; i < colnum; i++) {
			String colname = (String) vector.get(i);
			if (colname.equals("xh"))
				xhnum = i; //取得学号所在列的列号
			flags[i] = this.whichTable(colname);//取得该列所在的表的标志位
			//if (flags[i] == 3) {
				//request.setAttribute("info", "xls文件中列名"+vector.get(i)+"非法！"); //如果xls文件中有数据库中没有的字段名时出错退出
				//return mapping.findForward("failure");
			//}
		}
		if (xhnum == -1) {
			request.setAttribute("info", "xls文件中未包含“xh”列！");
			return mapping.findForward("failure");
		}//如果没有学号，退出
		for (int i = 1;; i++) {//SQL语句生成算法
			row = sheet.getRow(i);
			if (row == null) //到最后一列时退出
				break;
			boolean intable1 = false; //有字段位于bysbase表中的标志位
			boolean intable2 = false; //有字段位于bysjy表中的标志位
			String xh = null;
			if (row.getCell((short) xhnum) == null) {
				request.setAttribute("info","第"+i+"行数据无学号！");
				return mapping.findForward("failure");
			} else {
				xh = row.getCell((short) xhnum).getStringCellValue(); //取得学号
			}
			if (xh == null || xh.equals("")) {
				request.setAttribute("info","第"+i+"行数据无学号！");
				return mapping.findForward("failure");
			}
			if (this.stuBaseDao.getStudentBaseInfoByXh(xh) == null) {//如果数据库中无该生信息则执行插入
				String insertfront1 = "insert into bysbase(";
				String insertend1 = ") values(";
				String insertfront2 = "insert into bysjy(";
				String insertend2 = ") values(";
				for (int j = 0; row.getCell((short) j) != null; j++) {
					HSSFCell cell = row.getCell((short) j);
					String cellValue =null;
                    int type = cell.getCellType();
                    if(type==HSSFCell.CELL_TYPE_BLANK){
                    	cellValue = "";
                    }else if(type==HSSFCell.CELL_TYPE_BOOLEAN){
                    	cellValue = new Boolean(cell.getBooleanCellValue()).toString();
                    }else if(type==HSSFCell.CELL_TYPE_NUMERIC){
                    	cellValue = cell.getNumericCellValue()+"";
                    }else{
                    	cellValue = cell.getStringCellValue();
                    }
                    
					if (flags[j] == 1) {

						insertfront1 = insertfront1 + (String) vector.get(j)
								+ ",";
						insertend1 = insertend1 + "'" + cellValue + "',";
					} else if (flags[j] == 2) {

						insertfront2 = insertfront2 + (String) vector.get(j)
								+ ",";
						insertend2 = insertend2 + "'" + cellValue + "',";
					}else if(flags[j]==3){ 
						continue;
					}
					else {

						insertfront1 = insertfront1 + (String) vector.get(j)
								+ ",";
						insertend1 = insertend1 + "'" + cellValue + "',";
						insertfront2 = insertfront2 + (String) vector.get(j)
								+ ",";
						insertend2 = insertend2 + "'" + cellValue + "',";
					}

				}
				insertfront1 = insertfront1 + "xxbgsj,xxbgip";
				insertfront2 = insertfront2 + "xxbgsj,xxbgip";
				insertend1 = insertend1 + "'" + DateProcessor.getStringDate2()
						+ "','" + IP + "')";
				insertend2 = insertend2 + "'" + DateProcessor.getStringDate2()
						+ "','" + IP + "')";
				String insert1 = insertfront1 + insertend1;
				String insert2 = insertfront2 + insertend2;
				System.out.println(insert1);
				System.out.println(insert2);
				HashMap map = new HashMap();
				try {
					map.put("sql", insert1);
					this.stuBaseDao.insertStudentBaseInfoBySQL(map);
					map.put("sql", insert2);
					this.stuJYDao.insertStudentJYInfoBySQL(map);
				} catch (RuntimeException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					request.setAttribute("info", "数据导入失败！");
					return mapping.findForward("failure");
				}

			} else {//如果数据库中存在该生信息则执行更新
				String update1 = "update bysbase set ";
				String update2 = "update bysjy set ";
				for (int j = 0; row.getCell((short) j) != null; j++) {
					HSSFCell cell = row.getCell((short) j);
					String cellValue =null;
                    int type = cell.getCellType();
                    if(type==HSSFCell.CELL_TYPE_BLANK){
                    	cellValue = "";
                    }else if(type==HSSFCell.CELL_TYPE_BOOLEAN){
                    	cellValue = new Boolean(cell.getBooleanCellValue()).toString();
                    }else if(type==HSSFCell.CELL_TYPE_NUMERIC){
                    	cellValue = cell.getNumericCellValue()+"";
                    }else{
                    	cellValue = cell.getStringCellValue();
                    }

					if (flags[j] == 1) {
						intable1 = true;
						update1 = update1 + (String) vector.get(j) + "='"
								+ cellValue + "',";
					} else if (flags[j] == 2) {
						intable2 = true;
						update2 = update2 + (String) vector.get(j) + "='"
								+ cellValue + "',";
					}else if(flags[j]==3){ 
						continue;
					} 
					else {
						intable1 = true;
						intable2 = true;
						update1 = update1 + (String) vector.get(j) + "='"
						+ cellValue + "',";
						update2 = update2 + (String) vector.get(j) + "='"
						+ cellValue + "',";
					}

				}
				update1 = update1 + "xxbgsj='" + DateProcessor.getStringDate2()
						+ "',xxbgip='" + IP + "' where xh = '" + xh + "'";
				update2 = update2 + "xxbgsj='" + DateProcessor.getStringDate2()
						+ "',xxbgip='" + IP + "' where xh = '" + xh + "'";
				System.out.println(update1);
				System.out.println(update2);

				try {
					if (intable1) {
						HashMap map = new HashMap();
						map.put("sql", update1);
						this.stuBaseDao.updateStudentInfoBySQL(map);
					}
					if (intable2) {
						HashMap map = new HashMap();
						map.put("sql", update2);
						this.stuJYDao.updateStudentJYInfoBySQL(map);
					}
				} catch (RuntimeException e) { // TODO Auto-generated catch
					// block
					e.printStackTrace();
					request.setAttribute("info", "数据导入失败！");
					return mapping.findForward("failure");
				}

			}
		}
		return mapping.findForward("success");
	}

	private int whichTable(String colname) {
		if (colname.equals("xh"))
			return 0;
		else if (colname.equals("xm"))
			return 1;
		else if (colname.equals("zy"))
			return 1;
		else if (colname.equals("xb"))
			return 1;
		else if (colname.equals("mz"))
			return 1;
		else if (colname.equals("zzmm"))
			return 1;
		else if (colname.equals("csrq"))
			return 1;
		else if (colname.equals("byrq"))
			return 1;
		else if (colname.equals("pyfs"))
			return 1;
		else if (colname.equals("xz"))
			return 1;
		else if (colname.equals("xl"))
			return 1;
		else if (colname.equals("syszd"))
			return 1;
		else if (colname.equals("xjbd"))
			return 1;
		else if (colname.equals("rxrq"))
			return 1;
		else if (colname.equals("dxhwpdw"))
			return 1;
		else if (colname.equals("sfzh"))
			return 1;
		else if (colname.equals("ksh"))
			return 1;
		else if (colname.equals("bj"))
			return 1;
		else if (colname.equals("xy"))
			return 1;
		else if (colname.equals("xslb"))
			return 1;
		else if (colname.equals("bylb"))
			return 1;
		else if (colname.equals("fdyxm"))
			return 1;
		else if (colname.equals("sjh"))
			return 1;
		else if (colname.equals("ssdh"))
			return 1;
		else if (colname.equals("jtxxdz"))
			return 1;
		else if (colname.equals("jtyzbm"))
			return 1;
		else if (colname.equals("jtlxdh"))
			return 1;
		else if (colname.equals("wyyz"))
			return 1;
		else if (colname.equals("byzx"))
			return 1;
		else if (colname.equals("jbxxbz"))
			return 1;
		else if (colname.equals("byqx"))
			return 2;
		else if (colname.equals("jyxs"))
			return 2;
		else if (colname.equals("lcnr"))
			return 2;
		else if (colname.equals("dwmc"))
			return 2;
		else if (colname.equals("dwszd"))
			return 2;
		else if (colname.equals("dwxz"))
			return 2;
		else if (colname.equals("dwdz"))
			return 2;
		else if (colname.equals("dwyzbm"))
			return 2;
		else if (colname.equals("dwdh"))
			return 2;
		else if (colname.equals("dwcz"))
			return 2;
		else if (colname.equals("dwlxr"))
			return 2;
		else if (colname.equals("dwdzxx"))
			return 2;
		else if (colname.equals("dazjdw"))
			return 2;
		else if (colname.equals("dazjdz"))
			return 2;
		else if (colname.equals("dazjyzbm"))
			return 2;
		else if (colname.equals("hkqydz"))
			return 2;
		else if (colname.equals("lsgzqd"))
			return 2;
		else if (colname.equals("pqsj"))
			return 2;
		else if (colname.equals("bdqx"))
			return 2;
		else if (colname.equals("ljdlbz"))
			return 2;
		else if (colname.equals("bjcg"))
			return 2;
		else if (colname.equals("jtfwcm"))
			return 2;
		else if (colname.equals("dwjjlx"))
			return 2;
		else if (colname.equals("jyzt"))
			return 2;
		else if (colname.equals("lhdwmc"))
			return 2;
		else if (colname.equals("lhdwszd"))
			return 2;
		else if (colname.equals("dctjsj"))
			return 2;
		else if (colname.equals("mqzt"))
			return 2;
		else if (colname.equals("nclfs"))
			return 2;
		else if (colname.equals("qtsm"))
			return 2;
		else if (colname.equals("grdh"))
			return 2;
		else if (colname.equals("qzff"))
			return 2;
		else if (colname.equals("bz1"))
			return 2;
		else if (colname.equals("bz2"))
			return 2;
		else if (colname.equals("bz3"))
			return 2;
		else if (colname.equals("bz4"))
			return 2;
		else if (colname.equals("bz5"))
			return 2;
		else if (colname.equals("bz6"))
			return 2;
		else if (colname.equals("jlsd"))
			return 0;
		else if (colname.equals("xysh"))
			return 2;
		else if (colname.equals("xxsh"))
			return 2;
		else if (colname.equals("fdysm"))
			return 2;
		else if (colname.equals("jlstatus"))
			return 2;
		else if (colname.equals("mm"))
			return 1;
		else if (colname.equals("xsfl1"))
			return 1;
		else if (colname.equals("xsstatus"))
			return 1;
		else if(colname.equals("xsfl2"))
			return 1;
		else
			return 3;

	}
}