package com.asiainfo.action;

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

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.poi.ss.usermodel.Workbook;
import org.apache.struts2.ServletActionContext;

import com.asiainfo.dao.UserDao;
import com.asiainfo.model.PageBean;
import com.asiainfo.model.User;
import com.asiainfo.util.DbUtil;
import com.asiainfo.util.ExcelUtil;
import com.asiainfo.util.JsonUtil;
import com.asiainfo.util.ResponseUtil;
import com.asiainfo.util.StringUtil;
import com.opensymphony.xwork2.ActionSupport;

public class UserAction extends ActionSupport {

	private static final long serialVersionUID = 1L;

	private String page;
	private String rows;
	private String id;
	private User user;
	private String delId;
	private String s_name = "";

	private File userUploadFile;

	public String getPage() {
		return page;
	}

	public void setPage(String page) {
		this.page = page;
	}

	public String getRows() {
		return rows;
	}

	public void setRows(String rows) {
		this.rows = rows;
	}

	public String getDelId() {
		return delId;
	}

	public void setDelId(String delId) {
		this.delId = delId;
	}

	public User getUser() {
		return user;
	}

	public void setUser(User user) {
		this.user = user;
	}

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public File getUserUploadFile() {
		return userUploadFile;
	}

	public void setUserUploadFile(File userUploadFile) {
		this.userUploadFile = userUploadFile;
	}

	public String getS_name() {
		return s_name;
	}

	public void setS_name(String s_name) {
		this.s_name = s_name;
	}

	DbUtil dbUtil = new DbUtil();
	UserDao userDao = new UserDao();

	public String list() throws Exception {
		Connection con = null;
		PageBean pageBean = new PageBean(Integer.parseInt(page), Integer.parseInt(rows));
		try {
			if(user == null){
				user = new User();
			}
			user.setName(s_name);
			System.out.println(user);
			con = dbUtil.getCon();
			JSONObject result = new JSONObject();
			JSONArray jsonArray = JsonUtil.formatRsToJsonArray(userDao.userList(con, pageBean,user));
			int total = userDao.userCount(con,user);
			result.put("rows", jsonArray);
			result.put("total", total);
			ResponseUtil.write(ServletActionContext.getResponse(), result);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				dbUtil.closeCon(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}

	public String save() throws Exception {
		if (StringUtil.isNotEmpty(id)) {
			user.setId(Integer.parseInt(id));
		}
		Connection con = null;
		try {
			con = dbUtil.getCon();
			int saveNums = 0;
			JSONObject result = new JSONObject();
			if (StringUtil.isNotEmpty(id)) {
				saveNums = userDao.userModify(con, user);
			} else {
				saveNums = userDao.userAdd(con, user);
			}
			if (saveNums > 0) {
				result.put("success", "true");
			} else {
				result.put("success", "true");
				result.put("errorMsg", "保存失败");
			}
			ResponseUtil.write(ServletActionContext.getResponse(), result);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				dbUtil.closeCon(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}

	public String delete() throws Exception {
		Connection con = null;
		try {
			con = dbUtil.getCon();
			JSONObject result = new JSONObject();
			int delNums = userDao.userDelete(con, delId);
			if (delNums == 1) {
				result.put("success", "true");
			} else {
				result.put("errorMsg", "删除失败");
			}
			ResponseUtil.write(ServletActionContext.getResponse(), result);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				dbUtil.closeCon(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}

	/**
	 * 直接导出数据
	 * 
	 * @return 返回null，因为是以响应头，流的形式输出的
	 * @throws Exception
	 */
	public String export() throws Exception {
		Connection con = null;
		try {
			con = dbUtil.getCon();
			Workbook wb = new HSSFWorkbook(); // 定义一个工作簿
			String headers[] = { "编号", "姓名", "电话", "Email", "QQ" }; // 表的字段
			ResultSet rs = userDao.userList(con, null, user); // 查询出的结果集
			ExcelUtil.fillExcelData(rs, wb, headers);
			ResponseUtil.export(ServletActionContext.getResponse(), wb, "导出excel.xls"); // 将excel导出
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				dbUtil.closeCon(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}

	/**
	 * 利用模版导出数据
	 * 
	 * @return
	 * @throws Exception
	 */
	public String exportByTemplate() throws Exception {
		Connection con = null;
		try {
			if(user == null){
				user = new User();
			}
			String s = new String(s_name.getBytes("iso-8859-1"),"utf-8");
			user.setName(s);
			con = dbUtil.getCon();
			System.out.println(user);
			ResultSet rs = userDao.userList(con, null, user);
			Workbook wb = ExcelUtil.fillExcelDataByTemplate(userDao.userList(con, null, user), "userExporTemplate.xls");
			ResponseUtil.export(ServletActionContext.getResponse(), wb, "通过模版导出的数据.xls");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				dbUtil.closeCon(con);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	/**
	 * @return
	 * @throws Exception
	 */
	public String upload() throws Exception {
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(userUploadFile));
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet hssfSheet = wb.getSheetAt(0); // 获取第一个Sheet页

		if (hssfSheet != null) {
			for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow == null) {
					continue;
				}
				User user = new User();
				user.setName(ExcelUtil.formatCell(hssfRow.getCell(0)));
				user.setPhone(ExcelUtil.formatCell(hssfRow.getCell(1)));
				user.setEmail(ExcelUtil.formatCell(hssfRow.getCell(2)));
				user.setQq(ExcelUtil.formatCell(hssfRow.getCell(3)));
				Connection con = null;
				try {
					con = dbUtil.getCon();
					userDao.userAdd(con, user);
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					dbUtil.closeCon(con);
				}
			}
		}
		JSONObject result = new JSONObject();
		result.put("success", "true");
		ResponseUtil.write(ServletActionContext.getResponse(), result);
		return null;
	}

}