package com.qiankun.controller;
 
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;
import java.util.Set;

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

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PaperSize;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFPrintSetup;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;

import com.qiankun.annotation.Auth;
import com.qiankun.controller.base.BaseController;
import com.qiankun.dao.core.Hints;
import com.qiankun.entity.Blood;
import com.qiankun.entity.Contacts;
import com.qiankun.entity.User;
import com.qiankun.pages.IPageList;
import com.qiankun.service.UserService;
import com.qiankun.utils.JwtTokenUtil;
import com.qiankun.utils.MD5;
import com.qiankun.utils.SessionUtils;
import com.qiankun.utils.UserUtils;
import com.qiankun.vo.UserVo;
 
@Controller
public class UserController extends BaseController<Object>{
    
    @Autowired
	private UserService userService;
    @Autowired
    private JwtTokenUtil tokenUtil;
    
    
    
    @Auth(verifyLogin = false, verifyURL = false)
    @RequestMapping("/login")
    public ModelAndView login() throws Exception {
        ModelAndView t_view = new ModelAndView();
        t_view.setViewName("login");
        return t_view;
    }  
    
    
    
    @RequestMapping("/user/add")
    public ModelAndView userAdd(String id) throws Exception {
        ModelAndView view = new ModelAndView();
        UserVo vo = new UserVo();
        if(StringUtils.isNotBlank(id)) {
        	User user = userService.findById(id);
        	vo.setId(user.getId());
        	vo.setUserName(user.getMobile());
        	vo.setUserCode(user.getUserCode());
        	vo.setName(user.getName());
        	vo.setGender(user.getGender());
        	vo.setBirthday(user.getBirthday());
        	vo.setPaperType(user.getPaperType());
        	vo.setPaperId(user.getPaperId());
        	vo.setNation(user.getNation());
        	vo.setNativePlace(user.getNativePlace());
        	vo.setNationality(user.getNationality());
        	vo.setEducation(user.getEducation());
        	vo.setDomicilePlace(user.getDomicilePlace());
        	vo.setAddr(user.getAddr());
        	vo.setUnit(user.getUnit());
        	vo.setUnitAddr(user.getUnitAddr());
        	vo.setUnitTel(user.getUnitTel());
        	vo.setProfession(user.getProfession());
        	vo.setMobile(user.getMobile());    	
        	vo.setEmail(user.getEmail());
        	vo.setTel(user.getTel());
        	vo.setQq(user.getQq());
        	vo.setWeixin(user.getWeixin());
        	vo.setDonateBloodCount(user.getDonateBloodCount());
        	vo.setBloodType(user.getBloodType());
        	vo.setHeight(user.getHeight());
        	vo.setWeight(user.getWeight());
        	vo.setRemark(user.getRemark());
        	vo.setRole(user.getRole());
        	vo.setStatus(user.getStatus());
        	
        	
        	Set<Contacts> contacts = user.getContacts();
        	Iterator<Contacts> its = contacts.iterator();
        	Integer count = 0;
        	while (its.hasNext()) {
        		Contacts c = its.next();
        		if (count == 0) {
        			vo.setContactsAddr1(c.getAddr());
        			vo.setContactsName1(c.getName());
        			vo.setContactsMobile1(c.getMobile());
        			vo.setContactsRelation1(c.getRelation());
        			vo.setContactsWeixin1(c.getWeixin());
        			vo.setContactsQQ1(c.getQq());
        			vo.setLastContactsName1(c.getLastName());
        			vo.setLastContactsMobile1(c.getLastMobile());
        			count ++;
        		} else {
        			vo.setContactsAddr2(c.getAddr());
        			vo.setContactsName2(c.getName());
        			vo.setContactsMobile2(c.getMobile());
        			vo.setContactsRelation2(c.getRelation());
        			vo.setContactsWeixin2(c.getWeixin());
        			vo.setContactsQQ2(c.getQq());
        			vo.setLastContactsName2(c.getLastName());
        			vo.setLastContactsMobile2(c.getLastMobile());
        		}
        		
        		
        	}
        	
        }
        
        
        view.setViewName("user/saveuser");
        view.getModel().put("dict", dict());
        view.getModel().put("user", vo);
        return view;
    }
    
    @Auth(verifyLogin = false,verifyURL = false)
    @RequestMapping(value = "/wx/api/user/save",method = RequestMethod.POST)
    public @ResponseBody Map<String,Object> saveUserformWX(@RequestBody UserVo userVo, HttpServletResponse response, HttpServletRequest request) throws Exception{
    	userVo.setOrigin("1");
    	return this.saveUser(userVo, response, request);
    }
    
    
    
    @RequestMapping("/api/user/save")
    public Map<String, Object> saveUser(UserVo userVo, HttpServletResponse response, HttpServletRequest request) throws Exception {
    	
        Map<String, Object> map = new HashMap<>();
        
    	User user = new User();
    	Date date = new Date();	
    	Contacts contacts1 = new Contacts();
    	Contacts contacts2 = new Contacts();
    	

    	if (StringUtils.isEmpty(userVo.getId())) {
    		
    		Boolean userIsExistPaperId = userService.validateUserExistByPaperId(userVo.getPaperId());
        	if (userIsExistPaperId) {
        		map.put("status", 0);
        		map.put("message", "userIsExist_paperId");
        		return map;
        	}
        	
        	Boolean userIsExistUserName = userService.validateUserExistByUserName(userVo.getUserName());
        	if (userIsExistUserName) {
        		map.put("status", 0);
        		map.put("message", "userIsExist_userName");
        		return map;
        	}
        	user.setLastMobile(userVo.getMobile());
        	contacts1.setLastName(userVo.getContactsName1());
        	contacts1.setLastMobile(userVo.getContactsMobile1());
        	contacts2.setLastName(userVo.getContactsName2());
        	contacts2.setLastMobile(userVo.getContactsMobile2());
        	
			user.setCreateTime(date);
			map.put("status", 1);
        } else {

        	user = userService.findById(userVo.getId());
        	
        	if (StringUtils.isNotBlank(userVo.getPaperId()) && !userVo.getPaperId().equals(user.getPaperId())) {
        		Boolean userIsExistPaperId = userService.validateUserExistByPaperId(userVo.getPaperId());
    			if (userIsExistPaperId) {
    				map.put("status", 0);
    				map.put("message", "userIsExist_paperId");
    				return map;
    			}
        	}
        	
        	if (StringUtils.isNotBlank(userVo.getUserName()) && !userVo.getUserName().equals(user.getUserName())) {
        		Boolean userIsExistUserName = userService.validateUserExistByUserName(userVo.getUserName());
    			if (userIsExistUserName) {
    				map.put("status", 0);
    				map.put("message", "userIsExist_userName");
    				return map;
    			}
        	}

        	user.setUpdateTime(date);	
        	
        	contacts1.setLastName(userVo.getLastContactsName1());
        	contacts1.setLastMobile(userVo.getLastContactsMobile1());
        	contacts2.setLastName(userVo.getLastContactsName2());
        	contacts2.setLastMobile(userVo.getLastContactsMobile2());
        	
        	user.getContacts().clear();
        	map.put("status", 2);
        }  

    	user.setOrigin(userVo.getOrigin());
    	user.setUserName(userVo.getMobile());
        user.setName(userVo.getName());
        user.setOpenid(userVo.getOpenid());
        user.setGender(userVo.getGender());
        user.setBirthday(userVo.getBirthday());
        user.setPaperType(userVo.getPaperType());
        user.setPaperId(userVo.getPaperId());
        user.setNation(userVo.getNation());
        user.setNativePlace(userVo.getNativePlace());
        user.setNationality(userVo.getNationality());
        user.setEducation(userVo.getEducation());
        user.setDomicilePlace(userVo.getDomicilePlace());
        user.setAddr(userVo.getAddr());
        user.setUnit(userVo.getUnit());
        user.setUnitAddr(userVo.getUnitAddr());
        user.setUnitTel(userVo.getUnitTel());
        user.setProfession(userVo.getProfession());
        user.setMobile(userVo.getMobile());    	
    	user.setEmail(userVo.getEmail());
    	user.setTel(userVo.getTel());
    	user.setQq(userVo.getQq());
    	user.setWeixin(userVo.getWeixin());
    	user.setDonateBloodCount(userVo.getDonateBloodCount());
    	user.setBloodType(userVo.getBloodType());
    	user.setHeight(userVo.getHeight());
    	user.setWeight(userVo.getWeight());
    	user.setRemark(userVo.getRemark());
    	user.setRole(userVo.getRole());
    	user.setStatus(userVo.getStatus());
    	
    	contacts1.setName(userVo.getContactsName1());
    	contacts1.setAddr(userVo.getContactsAddr1());
    	contacts1.setMobile(userVo.getContactsMobile1());
    	contacts1.setRelation(userVo.getContactsRelation1());
    	contacts1.setWeixin(userVo.getContactsWeixin1());
    	contacts1.setQq(userVo.getContactsQQ1());
    	contacts1.setUpdateTime(date);        	
    	contacts1.setUser(user);
    	user.getContacts().add(contacts1);
    	    	
    	contacts2.setName(userVo.getContactsName2());
    	contacts2.setAddr(userVo.getContactsAddr2());
    	contacts2.setMobile(userVo.getContactsMobile2());
    	contacts2.setRelation(userVo.getContactsRelation2());
    	contacts2.setWeixin(userVo.getContactsWeixin2());
    	contacts2.setQq(userVo.getContactsQQ2());
    	contacts2.setUpdateTime(date);        	
    	contacts2.setUser(user);
    	user.getContacts().add(contacts2);
    	
        User u = userService.save(user);
        map.put("user", UserUtils.converToVo(u));
        map.put("sKey",tokenUtil.generateToken(u.getOpenid()));
        return map;
    }
    
    @Auth(verifyLogin = false, verifyURL = false)
	@RequestMapping("/api/signin")
	public ModelAndView signin(HttpServletRequest request,
			final HttpServletResponse response, String userName, String pwd)
			throws Exception {
		ModelAndView view = new ModelAndView();
		ResourceBundle rb = ResourceBundle.getBundle("system");
		String superAdmin = rb.getString("account");
		
		if (userName.equals(superAdmin)) {
			
	        String p = rb.getString("password");
	        if (p.equals(pwd)) {
	        	User user = new User();
	        	user.setMobile(superAdmin);
	        	user.setPwd(MD5.digest(pwd));
	        	SessionUtils.setUser(request, user);		
	        	view.getModelMap().addAttribute("status", 1);
	        } else {
	        	view.getModelMap().addAttribute("status", 0);
	        }
        		
		} else {
			User user = userService.userLogin(userName, pwd);
			if (user != null) {
				// 设置User到Session
				if ("1".equals(user.getRole())){
					SessionUtils.setUser(request, user);		
		 			view.getModelMap().addAttribute("status", 1);
				} else {
					view.getModelMap().addAttribute("status", 0);
				}
	 			
			} else {
				view.getModelMap().addAttribute("status", 0);			
			}
		}
		
		
		return view;
	}
    
    @RequestMapping("/user/list")
    public ModelAndView userList(UserVo userVo, HttpServletResponse response, HttpServletRequest request) throws Exception {
        ModelAndView view = new ModelAndView();
        IPageList<User> userPage = userService.findByUsers(userVo.getSearchStr(), userVo.getRole(), userVo.getStatus(), userVo.getChanged(), userVo.getOrder(), userVo.getSort(), 
					new Hints(getStartRow(request), getPageCount(request)));
			
		view.getModelMap().addAttribute("userPage", userPage);
		view.getModelMap().addAttribute("pageCount", getPageCount(request));
		view.getModelMap().addAttribute("pageNumber", getPageNumber(request));
		view.getModelMap().addAttribute("userVo", userVo);
		view.setViewName("user/userlist");
		
        
        return view;
    }
    
    @RequestMapping("/api/user/del")
    public ModelAndView delUser(String id, HttpServletResponse response, HttpServletRequest request) throws Exception {
    	ModelAndView view = new ModelAndView();
    	
		User user = userService.findById(id);
		user.setRemove(true);
		userService.save(user);     		
    	
    	return view;
    }
    
    @RequestMapping("/api/user/changed")
    public ModelAndView changedUser(String id, HttpServletResponse response, HttpServletRequest request) throws Exception {
    	ModelAndView view = new ModelAndView();
    	
		User user = userService.findById(id);
		user.setLastMobile(user.getMobile());

    	Set<Contacts> contacts = user.getContacts();
    	Iterator<Contacts> its = contacts.iterator();
    	//user.getContacts().clear();
    	while ( its.hasNext()) {
    		Contacts c = its.next();
    		c.setLastName(c.getName());
    		c.setLastMobile(c.getMobile());
    		//user.getContacts().add(c);
    	}
		
		
		userService.save(user);     		
    	
    	return view;
    }
    
    
    @RequestMapping("/user/importpage")
    public ModelAndView userImportPage(HttpServletResponse response, HttpServletRequest request) throws Exception {
        ModelAndView view = new ModelAndView();
        view.setViewName("user/import");
        return view;
     
    }
    
    @RequestMapping(value = "/user/import", method = RequestMethod.POST)
    public ModelAndView userImport(@RequestParam(value = "file", required = false) MultipartFile file, Integer type, HttpServletResponse response, HttpServletRequest request) throws Exception {
        ModelAndView view = new ModelAndView();
        
        if (file == null) {
        	view.getModelMap().addAttribute("status", "fileNotExist");
        	return view;
        } else {

        	InputStream excelFileIo = file.getInputStream();
        	
        	XSSFWorkbook xssfWorkbook = new XSSFWorkbook(excelFileIo);
        	XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);

        	int rowstart = xssfSheet.getFirstRowNum()+1;
        	int rowEnd = xssfSheet.getLastRowNum();
        	
        	XSSFRow row1 = xssfSheet.getRow(rowstart);
			if (null == row1) {
				xssfWorkbook.close();
				return null;
			}
			
        	Date date = new Date();
        	for (int i = rowstart + 1; i <= rowEnd; i++) {
        		XSSFRow row = xssfSheet.getRow(i);
        		if (null == row)
        			continue;
        		int cellStart = row.getFirstCellNum();
        		int cellEnd = row.getLastCellNum();
        		if (cellStart < 0 || cellEnd < 0) {
        		continue;
        		}
        		
        		String status = "2";
        		User u = null;
        		Blood b = new Blood();
        		Contacts c1 = new Contacts();
        		Contacts c2 = new Contacts();
        		if (type == 1) {
        			String paperId = row.getCell(7).getStringCellValue();
        			if (paperId.length()==15) {
        				paperId = UserUtils.get18Ic(paperId);
        			}
        			
        			u = userService.findByPaperId(paperId);
        			if (u == null) {
        				u = new User();
        			} else {
        				u.getBlood().clear();
        				u.getContacts().clear();
        			}
        			
        			
        			u.setMarrowCode(row.getCell(0).getStringCellValue());        			
        			u.setName(row.getCell(2).getStringCellValue());
        			u.setGender(row.getCell(4).getStringCellValue());
        			u.setBirthday(row.getCell(5).getStringCellValue());
        			u.setPaperType(row.getCell(6).getStringCellValue());
        			u.setPaperId(paperId);
        			u.setNation(row.getCell(8).getStringCellValue());
        			u.setNativePlace(row.getCell(9).getStringCellValue());
        			u.setNationality(row.getCell(10).getStringCellValue());
        			u.setEducation(row.getCell(11).getStringCellValue());
        			u.setDomicilePlace(row.getCell(12).getStringCellValue());
        			u.setAddr(row.getCell(14).getStringCellValue());
        			u.setUnit(row.getCell(16).getStringCellValue());
        			u.setProfession(row.getCell(17).getStringCellValue());
        			u.setUnitAddr(row.getCell(18).getStringCellValue());
        			u.setUnitTel(row.getCell(20).getStringCellValue());
        			
        			u.setMobile(row.getCell(21).getStringCellValue());
        			u.setLastMobile(row.getCell(21).getStringCellValue());
        			
        			u.setTel(row.getCell(22).getStringCellValue());
        			u.setQq(row.getCell(23).getStringCellValue());
        			u.setEmail(row.getCell(24).getStringCellValue());
        			if (StringUtils.isNotBlank(row.getCell(25).getStringCellValue())) {
        				u.setDonateBloodCount(Integer.valueOf(row.getCell(25).getStringCellValue()));
        			}
        			u.setBloodType(row.getCell(26).getStringCellValue());
        			if (StringUtils.isNotBlank(row.getCell(27).getStringCellValue())) {
        				u.setHeight(Integer.valueOf(row.getCell(27).getStringCellValue()));
        			}
        			if (StringUtils.isNotBlank(row.getCell(28).getStringCellValue())) {
        				u.setWeight(Integer.valueOf(row.getCell(28).getStringCellValue()));
        			}
        			u.setWeixin(row.getCell(29).getStringCellValue());
        			u.setRemark(row.getCell(30).getStringCellValue());
        			
        			c1.setName(row.getCell(31).getStringCellValue());
        			c1.setLastName(row.getCell(31).getStringCellValue());
        			c1.setRelation(row.getCell(32).getStringCellValue());
        			c1.setAddr(row.getCell(33).getStringCellValue());
        			c1.setMobile(row.getCell(35).getStringCellValue());
        			c1.setLastMobile(row.getCell(35).getStringCellValue());
        			c1.setQq(row.getCell(36).getStringCellValue());
        			c1.setWeixin(row.getCell(38).getStringCellValue());
        			c1.setUser(u);
        			c2.setName(row.getCell(40).getStringCellValue());
        			c2.setLastName(row.getCell(40).getStringCellValue());
        			c2.setRelation(row.getCell(41).getStringCellValue());
        			c2.setAddr(row.getCell(42).getStringCellValue());
        			c2.setMobile(row.getCell(44).getStringCellValue());
        			c2.setLastMobile(row.getCell(44).getStringCellValue());
        			c2.setQq(row.getCell(45).getStringCellValue());
        			c2.setWeixin(row.getCell(47).getStringCellValue());
        			c2.setUser(u);
        			
        			u.getContacts().add(c1);
        			u.getContacts().add(c2);
        			
        			b.setBloodId(row.getCell(1).getStringCellValue());
        			b.setBloodCollect(row.getCell(49).getStringCellValue());
        			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        			String d = row.getCell(50).getStringCellValue();
        			if (d != null) {
        				b.setUpdateTime(sdf.parse(d));
        			}
        			
        			b.setReview(row.getCell(53).getStringCellValue());
        			b.setUser(u);
        			
        			u.getBlood().add(b);
        			
        			
        			
        		} else if (type == 2) {
        			
        			String paperId = row.getCell(5).getStringCellValue();
        			if (paperId.length()==15) {
        				paperId = UserUtils.get18Ic(paperId);
        			}
        			
        			u = userService.findByPaperId(paperId);
        			if (u == null) {
        				u = new User();
        			} else {
        				u.getBlood().clear();
        				u.getContacts().clear();
        			}
        			
        			u.setName(row.getCell(2).getStringCellValue());
        			u.setGender(row.getCell(3).getStringCellValue());
        			u.setPaperType(row.getCell(4).getStringCellValue());
        			u.setPaperId(row.getCell(5).getStringCellValue());
        			u.setBirthday(row.getCell(6).getStringCellValue());
        			u.setNativePlace(row.getCell(7).getStringCellValue());
        			u.setNationality(row.getCell(8).getStringCellValue());
        			u.setNation(row.getCell(9).getStringCellValue());
        			u.setDomicilePlace(row.getCell(10).getStringCellValue());
        			u.setAddr(row.getCell(12).getStringCellValue());
        			u.setUnit(row.getCell(14).getStringCellValue());
        			u.setProfession(row.getCell(15).getStringCellValue());
        			u.setUnitAddr(row.getCell(16).getStringCellValue());
        			u.setTel(row.getCell(18).getStringCellValue());
        			
        			u.setMobile(row.getCell(19).getStringCellValue());
        			u.setLastMobile(row.getCell(19).getStringCellValue());
        			
        			u.setUnitTel(row.getCell(20).getStringCellValue());
        			u.setEmail(row.getCell(21).getStringCellValue());
        			if (StringUtils.isNotBlank(row.getCell(22).getStringCellValue())) {
        				u.setDonateBloodCount(Integer.valueOf(row.getCell(22).getStringCellValue()));
        			}
        			u.setQq(row.getCell(23).getStringCellValue());
        			u.setWeixin(row.getCell(24).getStringCellValue());
        			u.setBloodType(row.getCell(25).getStringCellValue());
        			if (StringUtils.isNotBlank(row.getCell(27).getStringCellValue())) {
        				u.setHeight(Integer.valueOf(row.getCell(27).getStringCellValue()));        				
        			}
        			
    				if (StringUtils.isNotBlank(row.getCell(28).getStringCellValue())) {
    					u.setWeight(Integer.valueOf(row.getCell(28).getStringCellValue()));
    				}
        				
        			
        			u.setRemark(row.getCell(29).getStringCellValue());
        			u.setEducation(row.getCell(31).getStringCellValue());
        			       			
        			c1.setName(row.getCell(35).getStringCellValue());
        			c1.setLastName(row.getCell(35).getStringCellValue());
        			c1.setRelation(row.getCell(36).getStringCellValue());
        			c1.setMobile(row.getCell(37).getStringCellValue());
        			c1.setLastMobile(row.getCell(37).getStringCellValue());
        			c1.setQq(row.getCell(38).getStringCellValue());
        			
        			c1.setWeixin(row.getCell(40).getStringCellValue());
        			c1.setAddr(row.getCell(41).getStringCellValue());
        			c1.setUser(u);
        			
        			c2.setName(row.getCell(44).getStringCellValue());
        			c2.setRelation(row.getCell(45).getStringCellValue());
        			c2.setMobile(row.getCell(46).getStringCellValue());
        			c2.setLastMobile(row.getCell(46).getStringCellValue());
        			c2.setQq(row.getCell(47).getStringCellValue());
        			
        			c2.setWeixin(row.getCell(49).getStringCellValue());
        			c2.setAddr(row.getCell(50).getStringCellValue());
        			c2.setUser(u);
        			u.getContacts().add(c1);
        			u.getContacts().add(c2);
 		
        			b.setBloodCollect(row.getCell(32).getStringCellValue());
        			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        			String d = row.getCell(26).getStringCellValue();
        			if (d != null) {
        				b.setUpdateTime(sdf.parse(d));
        			}
        			b.setBloodId(row.getCell(1).getStringCellValue());
        			b.setUser(u);
        			u.getBlood().add(b);
        		} else {
        			view.getModelMap().addAttribute("status", 0);
        			break;
        		}
        		
        		u.setStatus(status);
        		u.setRole("2");
        		u.setCreateTime(date);
        		u.setOrigin("3");
        		userService.save(u);
        		
        		
        	}
        	xssfWorkbook.close();
        }
        
		return view;
    }
    
    @RequestMapping("/user/exportpage")
    public ModelAndView userExportPage(HttpServletResponse response, HttpServletRequest request) throws Exception {
        ModelAndView view = new ModelAndView();
        view.setViewName("user/export");
        return view;
     
    }
    
    
    @RequestMapping("/user/export")
	public void export(UserVo userVo, HttpServletResponse response, HttpServletRequest request) throws Exception {
		
		try {
			
			request.setCharacterEncoding("UTF-8");
			response.setCharacterEncoding("UTF-8");
			response.setContentType("application/x-download");
	
			String fileName = "user.xlsx";
			fileName = URLEncoder.encode(fileName, "UTF-8");
			response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
	
			XSSFWorkbook wb = new XSSFWorkbook();
			XSSFSheet sheet = wb.createSheet("志愿者基本信息模板");
			sheet.setMargin(HSSFSheet.TopMargin,( double ) 0.5 ); // 上边距
			sheet.setMargin(HSSFSheet.BottomMargin,( double ) 0.5 ); // 下边距
			sheet.setMargin(HSSFSheet.LeftMargin,( double ) 0.2 ); // 左边距
			sheet.setMargin(HSSFSheet.RightMargin,( double ) 0.0 ); // 右边距

			
			XSSFPrintSetup print =  sheet.getPrintSetup(); 
			print.setLandscape(true);
			print.setPaperSize(PaperSize.A4_PAPER);
			  
			sheet.setDefaultRowHeight((short) (2 * 256));//设置行高
			sheet.setColumnWidth(0,3000);//设置列宽
			sheet.setColumnWidth(1,3000);
			sheet.setColumnWidth(2,3000);
			sheet.setColumnWidth(3,3000);
			sheet.setColumnWidth(4,3000);
			sheet.setColumnWidth(5,3000);
			sheet.setColumnWidth(6,3000);
			sheet.setColumnWidth(7,3000);
			sheet.setColumnWidth(8,3000);
			sheet.setColumnWidth(9,3000);
			sheet.setColumnWidth(10,3000);
			sheet.setColumnWidth(11,3000);
			sheet.setColumnWidth(12,3000);
			sheet.setColumnWidth(13,3000);
			sheet.setColumnWidth(14,3000);
			sheet.setColumnWidth(15,3000);
			sheet.setColumnWidth(16,3000);
			sheet.setColumnWidth(17,3000);
			sheet.setColumnWidth(18,3000);
			sheet.setColumnWidth(19,3000);
			sheet.setColumnWidth(20,3000);
			sheet.setColumnWidth(21,3000);
			sheet.setColumnWidth(22,3000);
			sheet.setColumnWidth(23,3000);
			sheet.setColumnWidth(24,3000);
			sheet.setColumnWidth(25,3000);
			sheet.setColumnWidth(26,3000);
			sheet.setColumnWidth(27,3000);
			sheet.setColumnWidth(28,3000);
			sheet.setColumnWidth(29,3000);
			sheet.setColumnWidth(30,3000);
			sheet.setColumnWidth(31,3000);
			sheet.setColumnWidth(32,3000);
			sheet.setColumnWidth(33,3000);
			sheet.setColumnWidth(34,3000);
			sheet.setColumnWidth(35,3000);
			sheet.setColumnWidth(36,3000);
			sheet.setColumnWidth(37,3000);
			sheet.setColumnWidth(38,3000);
			sheet.setColumnWidth(39,3000);
			sheet.setColumnWidth(40,3000);
			sheet.setColumnWidth(41,3000);
			sheet.setColumnWidth(42,3000);
			sheet.setColumnWidth(43,3000);
			sheet.setColumnWidth(44,3000);
			sheet.setColumnWidth(45,3000);
			sheet.setColumnWidth(46,3000);
			sheet.setColumnWidth(47,3000);
			sheet.setColumnWidth(48,3000);
			sheet.setColumnWidth(49,3000);
			sheet.setColumnWidth(50,3000);
			sheet.setColumnWidth(51,3000);
			sheet.setColumnWidth(52,3000);
		
			XSSFFont topfont = wb.createFont();
			topfont.setFontName("宋体");
			topfont.setFontHeightInPoints((short) 11);
			//topfont.setBold(true);
			//topfont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		
			XSSFCellStyle cellTopStyle=wb.createCellStyle();     
			cellTopStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
			cellTopStyle.setBorderBottom(BorderStyle.THIN); //setBorderBottom(CellStyle.BORDER_THIN);
			cellTopStyle.setBorderLeft(BorderStyle.THIN);//setBorderLeft(CellStyle.BORDER_THIN);
			cellTopStyle.setBorderRight(BorderStyle.THIN);//setBorderRight(CellStyle.BORDER_THIN);
			cellTopStyle.setBorderTop(BorderStyle.THIN);//setBorderTop(CellStyle.BORDER_THIN);
			cellTopStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);  
			cellTopStyle.setFont(topfont);
			
			cellTopStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); 
			
			XSSFRow row = sheet.createRow(0);
			row.setHeightInPoints(20); 
			row.setRowStyle(cellTopStyle);
			XSSFCell cell = row.createCell(0);
			cell.setCellValue("个人信息");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(29);
			cell.setCellValue("最亲近的家属或朋友资料-1");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(38);
			cell.setCellValue("最亲近的家属或朋友资料-2");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(47);
			cell.setCellValue("录入与审核信息");
			cell.setCellStyle(cellTopStyle);
			
			sheet.addMergedRegion(new CellRangeAddress(0,0,0,28)); 
			sheet.addMergedRegion(new CellRangeAddress(0,0,29,37)); 
			sheet.addMergedRegion(new CellRangeAddress(0,0,38,46)); 
			sheet.addMergedRegion(new CellRangeAddress(0,0,47,52)); 
			
			row = sheet.createRow(1);
			row.setHeightInPoints(30); 
			
			cell = row.createCell(0);
			cell.setCellValue("*姓名");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(1);
			cell.setCellValue("是否用于科研");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(2);
			cell.setCellValue("*性别");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(3);
			cell.setCellValue("*出生日期");
			cell.setCellStyle(cellTopStyle);
			
			
			cell = row.createCell(4);
			cell.setCellValue("*证件类型");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(5);
			cell.setCellValue("*证件号码");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(6);
			cell.setCellValue("民族");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(7);
			cell.setCellValue("*籍贯");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(8);
			cell.setCellValue("国籍");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(9);
			cell.setCellValue("*学历");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(10);
			cell.setCellValue("户籍所在地");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(11);
			cell.setCellValue("户籍所在地邮编");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(12);
			cell.setCellValue("经常居住地");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(13);
			cell.setCellValue("经常居住地邮编");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(14);
			cell.setCellValue("工作单位/学校");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(15);
			cell.setCellValue("职业");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(16);
			cell.setCellValue("单位/学校地址");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(17);
			cell.setCellValue("单位/学校邮编");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(18);
			cell.setCellValue("单位/学校电话");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(19);
			cell.setCellValue("*手机");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(20);
			cell.setCellValue("固定电话");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(21);
			cell.setCellValue("*QQ");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(22);
			cell.setCellValue("E-mail");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(23);
			cell.setCellValue("无偿献血次数");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(24);
			cell.setCellValue("*血型");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(25);
			cell.setCellValue("*身高");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(26);
			cell.setCellValue("*体重");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(27);
			cell.setCellValue("微信");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(28);
			cell.setCellValue("备注");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(29);
			cell.setCellValue("*姓名");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(30);
			cell.setCellValue("*关系");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(31);
			cell.setCellValue("住址");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(32);
			cell.setCellValue("邮编");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(33);
			cell.setCellValue("*手机");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(34);
			cell.setCellValue("QQ");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(35);
			cell.setCellValue("固定电话");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(36);
			cell.setCellValue("微信");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(37);
			cell.setCellValue("备注");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(38);
			cell.setCellValue("姓名");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(39);
			cell.setCellValue("关系");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(40);
			cell.setCellValue("住址");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(41);
			cell.setCellValue("邮编");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(42);
			cell.setCellValue("手机");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(43);
			cell.setCellValue("QQ");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(44);
			cell.setCellValue("固定电话");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(45);
			cell.setCellValue("微信");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(46);
			cell.setCellValue("备注");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(47);
			cell.setCellValue("*采样编号");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(48);
			cell.setCellValue("采血者");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(49);
			cell.setCellValue("*采样时间");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(50);
			cell.setCellValue("信息初审者");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(51);
			cell.setCellValue("数据录入者");
			cell.setCellStyle(cellTopStyle);
			
			cell = row.createCell(52);
			cell.setCellValue("数据审核者");
			cell.setCellStyle(cellTopStyle);
			
			
			XSSFCellStyle cellStyle=wb.createCellStyle();     
			cellStyle.setWrapText(true);  
			
			cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
			cellStyle.setBorderBottom(BorderStyle.THIN); //setBorderBottom(CellStyle.BORDER_THIN);
			cellStyle.setBorderLeft(BorderStyle.THIN);//setBorderLeft(CellStyle.BORDER_THIN);
			cellStyle.setBorderRight(BorderStyle.THIN);//setBorderRight(CellStyle.BORDER_THIN);
			cellStyle.setBorderTop(BorderStyle.THIN);//setBorderTop(CellStyle.BORDER_THIN);
			cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);  
			cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); 
			
			XSSFFont cellfont = wb.createFont();
			cellfont.setFontName("宋体");
			cellfont.setFontHeightInPoints((short) 10);
			cellStyle.setFont(cellfont);
			

			List<User> user = userService.findUserPage(userVo.getName(), userVo.getPaperId(),
					userVo.getStart(), userVo.getEnd(), userVo.getBloodId(), userVo.getStartbloodId(),
					userVo.getEndbloodId(), userVo.getOrder(), userVo.getSort(), new Hints(0));
			XSSFRow rows;
    		XSSFCell cells;
    		for (int i = 0; i < user.size(); i++) {
    			
    			rows = sheet.createRow(i+2);
    			//rows.setHeightInPoints(70); 
    			
    			User data = user.get(i);
    			
    			cells = rows.createCell(0);
    			cells.setCellValue(data.getName());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(1);
    			cells.setCellValue("");
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(2);
    			cells.setCellValue(data.getGender());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(3);
    			cells.setCellValue(data.getBirthday());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(4);
    			cells.setCellValue(data.getPaperType());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(5);
    			cells.setCellValue(data.getPaperId());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(6);
    			cells.setCellValue(data.getNation());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(7);
    			cells.setCellValue(data.getNativePlace());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(8);
    			cells.setCellValue(data.getNationality());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(9);
    			cells.setCellValue(data.getEducation());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(10);
    			cells.setCellValue(data.getDomicilePlace());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(11);
    			cells.setCellValue("");
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(12);
    			cells.setCellValue(data.getAddr());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(13);
    			cells.setCellValue("");
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(14);
    			cells.setCellValue(data.getUnit());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(15);
    			cells.setCellValue(data.getProfession());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(16);
    			cells.setCellValue(data.getUnitAddr());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(17);
    			cells.setCellValue("");
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(18);
    			cells.setCellValue(data.getUnitTel());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(19);
    			cells.setCellValue(data.getMobile());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(20);
    			cells.setCellValue(data.getTel());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(21);
    			cells.setCellValue(data.getQq());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(22);
    			cells.setCellValue(data.getEmail());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(23);
    			if (data.getDonateBloodCount()==null) {
    				cells.setCellValue(0);
    			} else {
    				cells.setCellValue(data.getDonateBloodCount());    				
    			}
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(24);
    			cells.setCellValue(data.getBloodType());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(25);
    			cells.setCellValue(data.getHeight());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(26);
    			cells.setCellValue(data.getWeight());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(27);
    			cells.setCellValue(data.getWeixin());
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(28);
    			cells.setCellValue(data.getRemark());
    			cells.setCellStyle(cellStyle);
    			
    			
    			Set<Contacts> contacts = data.getContacts();
            	Iterator<Contacts> its = contacts.iterator();
            	Integer count = 0;
            	while (its.hasNext()) {
            		Contacts c = its.next();
            		if (count == 0) {
            			
            			cells = rows.createCell(29);
            			cells.setCellValue(c.getName());
            			cells.setCellStyle(cellStyle);
            			
            			cells = rows.createCell(30);
            			cells.setCellValue(c.getRelation());
            			cells.setCellStyle(cellStyle);
            			
            			cells = rows.createCell(31);
            			cells.setCellValue(c.getAddr());
            			cells.setCellStyle(cellStyle);
            			
            			cells = rows.createCell(32);
            			cells.setCellValue("");
            			cells.setCellStyle(cellStyle);
            			
            			cells = rows.createCell(33);
            			cells.setCellValue(c.getMobile());
            			cells.setCellStyle(cellStyle);
            			
            			cells = rows.createCell(34);
            			cells.setCellValue(c.getQq());
            			cells.setCellStyle(cellStyle);
            			
            			cells = rows.createCell(35);
            			cells.setCellValue("");
            			cells.setCellStyle(cellStyle);
            			
            			cells = rows.createCell(36);
            			cells.setCellValue(c.getWeixin());
            			cells.setCellStyle(cellStyle);
            			
            			cells = rows.createCell(37);
            			cells.setCellValue("");
            			cells.setCellStyle(cellStyle);
            			
            			
            			count ++;
            		} else {
            			cells = rows.createCell(38);
            			cells.setCellValue(c.getName());
            			cells.setCellStyle(cellStyle);
            			
            			cells = rows.createCell(39);
            			cells.setCellValue(c.getRelation());
            			cells.setCellStyle(cellStyle);
            			
            			cells = rows.createCell(40);
            			cells.setCellValue(c.getAddr());
            			cells.setCellStyle(cellStyle);
            			
            			cells = rows.createCell(41);
            			cells.setCellValue("");
            			cells.setCellStyle(cellStyle);
            			
            			cells = rows.createCell(42);
            			cells.setCellValue(c.getMobile());
            			cells.setCellStyle(cellStyle);
            			
            			cells = rows.createCell(43);
            			cells.setCellValue(c.getQq());
            			cells.setCellStyle(cellStyle);
            			
            			cells = rows.createCell(44);
            			cells.setCellValue("");
            			cells.setCellStyle(cellStyle);
            			
            			cells = rows.createCell(45);
            			cells.setCellValue(c.getWeixin());
            			cells.setCellStyle(cellStyle);
            			
            			cells = rows.createCell(46);
            			cells.setCellValue("");
            			cells.setCellStyle(cellStyle);
            		}
            		
            		
            	}
    			
            	cells = rows.createCell(47);
    			cells.setCellValue("");
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(48);
    			cells.setCellValue("");
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(49);
    			cells.setCellValue("");
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(50);
    			cells.setCellValue("");
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(51);
    			cells.setCellValue("");
    			cells.setCellStyle(cellStyle);
    			
    			cells = rows.createCell(52);
    			cells.setCellValue("");
    			cells.setCellStyle(cellStyle);
    			
            	Set<Blood> blood = data.getBlood();
            	Iterator<Blood> bits = blood.iterator();
            	while (bits.hasNext()) {
            		Blood b = bits.next();
            		cells = rows.createCell(47);
        			cells.setCellValue(b.getBloodId());
        			cells.setCellStyle(cellStyle);
        			
        			cells = rows.createCell(48);
        			cells.setCellValue(b.getBloodCollect());
        			cells.setCellStyle(cellStyle);
        			
        			cells = rows.createCell(49);
        			
        			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        			cells.setCellValue(sdf.format(b.getUpdateTime()));
        			cells.setCellStyle(cellStyle);
        			
        			cells = rows.createCell(50);
        			cells.setCellValue("");
        			cells.setCellStyle(cellStyle);
        			
        			cells = rows.createCell(51);
        			cells.setCellValue("");
        			cells.setCellStyle(cellStyle);
        			
        			cells = rows.createCell(52);
        			cells.setCellValue(b.getReview());
        			cells.setCellStyle(cellStyle);
            	}
    			
    			
    			
    			
    			
    		}
    			
				
			OutputStream out = response.getOutputStream();
			wb.write(out);
			out.close();
			wb.close();
		
			
			
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	}

}