<%@ page contentType="text/html;charset=UTF-8" %>
<%@ page import="java.io.*,java.util.*,org.apache.commons.fileupload.*" %>
<%@ page import="org.apache.commons.fileupload.disk.DiskFileItemFactory" %>
<%@ page import="org.apache.commons.fileupload.servlet.ServletFileUpload" %>
<%@ page import="com.censoft.common.base.Pub" %>
<%@ page import="java.text.SimpleDateFormat" %>
<%@ page import="java.text.DateFormat" %>
<%@ page import="com.censoft.common.db.ConnectionFactory" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="com.censoft.db.Logger" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="org.apache.poi.poifs.filesystem.POIFSFileSystem" %>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook" %>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFSheet" %>
<%@ page import="org.apache.poi.xssf.usermodel.XSSFWorkbook" %>
<%@ page import="org.apache.poi.xssf.usermodel.XSSFSheet" %>
<%@ page import="org.apache.poi.ss.usermodel.Cell" %>
<%@ page import="org.apache.poi.ss.usermodel.Row" %>
<%@ page import="org.apache.poi.ss.usermodel.Sheet" %>
<%@ page import="javax.servlet.ServletException" %>
<%@ page import="javax.servlet.http.HttpServlet" %>
<%@ page import="javax.servlet.http.HttpServletRequest" %>
<%@ page import="javax.servlet.http.HttpServletResponse" %>
<%@ page import="com.censoft.db.DBUtil" %>
<%!
    public static boolean insertDb(Connection conn,ConnectionFactory cf,List list,String zc_id,String ly_dept,String counts,String add_user,String add_time){
        StringBuffer sb = new StringBuffer();
        DateFormat df = new SimpleDateFormat("yyyyMMdd");
		System.out.println("list=" + list.size());
        boolean is_oper=false;
       // sb.append("insert into zc_register (add_user,add_time,reg_id,zc_id,zc_status," +
                //"zc_name,zc_gj_type,zc_fl," +
                //"zc_fl_code,zc_resource,sb_type,pinpai,gg_type,jz_type,jiazhi,counts,dept," +
               // "chuchang_date,cw_date,kj_id,czx_money,fczx_money,ljzj,jingzhi,ytyzj," +
               // "is_lj,beizhu,car_id,car_type,car_fdj_id,car_address) " +
               // "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		 sb.append("insert into zc_register (add_user,add_time,reg_id,zc_id,zc_gj_type,zc_fl," +
                "zc_name,jz_type,sb_type,jiazhi,zc_resource,cw_date,zc_status,use_dept,beizhu,counts,gg_type) " +
                "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        int count=1;
        if(!"".equals(counts)){
            count=Integer.parseInt(counts);
        }
        list.add(0,add_user);
        list.add(1,add_time);
        Date sys_date = new Date();
        String id = df.format(sys_date)+sys_date.getTime();
        list.add(2,id);
		if("".equals(zc_id)){
			list.add(3,"AB"+id);
		}
		if(!"".equals(ly_dept)&&!"".equals(zc_id)){
			is_oper = cf.executeUpdate(conn, "insert into zc_ly (add_user,add_time,ly_id,zc_id,ly_dept,ly_status) values (?,?,?,?,?,?)",new Object[]{add_user,add_time,id,zc_id,ly_dept,"3"});
			if (!is_oper) {
				System.out.println("领用部门新增出错了" + cf.getMsg());
				return is_oper;
			}
		}
        if(count>1){
            for(int i=0;i<count;i++){
				if("".equals(zc_id)){
					list.set(3,"AB"+id+i);
				}
                is_oper = cf.executeUpdate(conn, sb.toString(), list.toArray());
                if (!is_oper) {
                    System.out.println("批量资产新增出错了" + cf.getMsg());
                    return is_oper;
                }
            }
        }else{
            is_oper = cf.executeUpdate(conn, sb.toString(), list.toArray());
			if (!is_oper) {
				System.out.println("批量资产新增出错了" + cf.getMsg());
				return is_oper;
			}
        }
        return is_oper;
    }
    public static List parseExcel(String path) {
        List list = new ArrayList();
        File file = null;
        InputStream input = null;
        if (path != null && path.length() > 7) {
            // 判断文件是否是Excel(2003、2007)
            String suffix = path
                    .substring(path.lastIndexOf("."), path.length());
            file = new File(path);
            try {
                input = new FileInputStream(file);
            } catch (FileNotFoundException e) {
                System.out.println("未找到指定的文件！");
            }
            // Excel2003
            if (".xls".equals(suffix)) {
                POIFSFileSystem fileSystem = null;
                // 工作簿
                HSSFWorkbook workBook = null;
                try {
                    fileSystem = new POIFSFileSystem(input);
                    workBook = new HSSFWorkbook(fileSystem);
                } catch (IOException e) {
                    e.printStackTrace();
                }
                // 获取第一个工作簿
                HSSFSheet sheet = workBook.getSheetAt(0);
                list = getContent((Sheet) sheet);
                // Excel2007
            } else if (".xlsx".equals(suffix)) {
                XSSFWorkbook workBook = null;
                try {
                    workBook = new XSSFWorkbook(input);
                } catch (IOException e) {
                    e.printStackTrace();
                }
                // 获取第一个工作簿
                XSSFSheet sheet = workBook.getSheetAt(0);
                list = getContent(sheet);
            }
        } else {
            System.out.println("非法的文件路径!");
        }
        return list;
    }
    public static List getContent(Sheet sheet) {
        List list = new ArrayList();
        // Excel数据总行数
        int rowCount = sheet.getPhysicalNumberOfRows();
        // 遍历数据行，略过标题行，从第二行开始
        for (int i = 0; i < rowCount; i++) {
            Row row = sheet.getRow(i);
            list.add(row);

        }
        return list;
    }

%>
<%
    Map map_param = new HashMap();
    List list_file = new ArrayList();
    DateFormat df = new SimpleDateFormat("yyyyMMddmmssdd");
    Pub pub = new Pub();
    List<FileItem> items = null;
    response.setContentType("text/html; charset=UTF-8");
    InputStream in = null;
    Properties props = new Properties();
    try {
        in = DBUtil.class.getResourceAsStream("db.properties");
        props.load(in);
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (in != null) {
            try {
                in.close();
            } catch (Exception e) {
            }
        }
    }
    String fileuploadPath = props.getProperty("fileuploadPath");
    //String fileuploadPath = "E://files/";
    Date sys_date = new Date();
    String id = df.format(sys_date)+sys_date.getTime();
    String file_path ="";
    Connection conn = null;
    ConnectionFactory cf = new ConnectionFactory();
    String add_user = (String) session.getAttribute("session_login_id");

    try{
        boolean isMultipart = ServletFileUpload.isMultipartContent(request);
        //当enctype="multipart/form-data"并且method是post时，isMultipart为真
        if(isMultipart){
            FileItemFactory factory = new DiskFileItemFactory();
            ServletFileUpload upload = new ServletFileUpload(factory);
            upload.setHeaderEncoding("utf-8");
            //转换请求对象
            items=upload.parseRequest(request);
            Iterator<FileItem> iterator = items.iterator();
            while(iterator.hasNext()){
                FileItem item = iterator.next();
                //保存上传文件
                if(item.isFormField()){
                    //处理普通文本字段
                    String fieldName = item.getFieldName();
                    if(map_param.get(fieldName)!=null){
                        map_param.put(fieldName, map_param.get(fieldName)+","+item.getString("utf-8"));
                    }else{
                        map_param.put(fieldName, item.getString("utf-8"));
                    }
                }else{
                    if(!"".equals(item.getName())){
                        list_file.add(item);
                    }
                }
            }
        }
    }catch(Exception e){
        System.out.println("上传失败了"+e);
        return;
    }
    if (list_file!=null && list_file.size()>0) {
        FileItem item = (FileItem) list_file.get(0);
        String file_names = "";
        if (!"".equals(pub.trimNull(item.getName()))) {
            String fileName = pub.trimNull(item.getName());
            fileName = fileName.substring(fileName.lastIndexOf("\\") + 1, fileName.length());
            String file_order = pub.trimNull(item.getFieldName());
            String file_id = id;
            file_path = fileuploadPath + "/" + file_id + fileName.substring(fileName.lastIndexOf("."), fileName.length());
            item.write(new File(file_path));
        }
        try {
            conn = com.censoft.db.DBUtil.getConnection();
            conn.setAutoCommit(false);
            List list_row=parseExcel(file_path);
			
            if(list_row!=null && list_row.size()>0){
                for(int i=1;i<list_row.size();i++){
                    Row row_0 = (Row)list_row.get(0);
                    Row row = (Row)list_row.get(i);
                    int cellCount = row_0.getPhysicalNumberOfCells();
					System.out.println("cellCount="+cellCount);
                    // 遍历行单元格
                    String counts="";
					String zc_id="";
					String ly_dept="";
                    List list = new ArrayList();
                    for (int j = 0; j < cellCount; j++) {
                        Cell cell = row.getCell(j);
                        String value="";
						
                        if(cell!=null){
                            //数量
                            if(j==12){
                                counts=""+(int)cell.getNumericCellValue();
                                value=""+(int)cell.getNumericCellValue();
                                //时间字段
                            }else if(j==9){
								value=cell.getStringCellValue();
								//暂定为已领用状态
								if("在用".equals(value)){
									value="3";
								}else{
									value="3";
								}
							}else if(j==10){
								if(cell.getCellType()==0){
                                    value=""+(int)cell.getNumericCellValue();
									ly_dept=""+(int)cell.getNumericCellValue();
                                }else if(cell.getCellType()==1){
                                    value=cell.getStringCellValue();
									ly_dept=cell.getStringCellValue();
                                }else{
                                    value="";
									ly_dept="";
                                }
							}else if(j==0){
								if(cell.getCellType()==0){
                                    zc_id=""+(int)cell.getNumericCellValue();
									value=""+(int)cell.getNumericCellValue();
                                }else if(cell.getCellType()==1){
                                    zc_id=cell.getStringCellValue();
									value=cell.getStringCellValue();
                                }else{
                                    zc_id="";
									value="";
                                }
							}else{
                                if(cell.getCellType()==0){
                                    value=""+(int)cell.getNumericCellValue();
                                }else if(cell.getCellType()==1){
                                    value=cell.getStringCellValue();
                                }else{
                                    value="";
                                }
                            }
                        }else{
                            value="";
                        }
                        list.add(value);
                    }
                    String add_time = pub.getCurrentServerTime();
                    boolean b=insertDb(conn,cf,list,zc_id,ly_dept,counts,add_user,add_time);
                    if (!b) {
                        conn.rollback();
                        System.out.println("批量资产出错了" + cf.getMsg());
                        out.println("<script>layer.msg('导入失败!', {icon: 5, time: 2000},function(){parent.searchModel();\n" +
                                "        var index = parent.layer.getFrameIndex(window.name);\n" +
                                "        parent.layer.close(index);})</script>");
                        return;
                    }
                }
            }
            Logger.log(conn,"批量资产登记", "新增", "", "", request);
            out.println("<script>layer.msg('导入成功!', {icon: 1, time: 2000},function(){parent.searchModel();\n" +
                    "        var index = parent.layer.getFrameIndex(window.name);\n" +
                    "        parent.layer.close(index);})</script>");
            conn.commit();
            conn.setAutoCommit(true);
            return;
        } catch (Exception e) {
            try {
                if(conn != null){
                    conn.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            return;
        }finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                }
            }
        }
    }


%>
<link rel="stylesheet" href="<%=request.getContextPath()%>/js/zTree/v3/css/zTreeStyle/zTreeStyle.css" type="text/css">
<link rel="stylesheet" href="<%=request.getContextPath()%>/css/pagination.css">
<div class="page-container">
    <div class="content" >
        <div id="export_type"  style="border:1px solid #ddd;background-color: #fff;width:100%;height:300px;">
            <div onclick="exportFile();" style="text-align: center;width:300px;margin:0 auto;border:1px solid #e1e1e1;margin-top: 50px;cursor: pointer;">下载模板</div>
            <div style="text-align: center;width:300px;height:40px;line-height:40px;margin:0 auto;border:1px solid #e1e1e1;margin-top: 30px;cursor: pointer;">
                <div style="text-align: center;width:160px;height:40px;margin:0 auto;cursor: pointer;float:left;">
					<form name="contentForm" id="contentForm" action="./piliang-t.jsp"
						  enctype="multipart/form-data" method="post" >
						<input style="width:160px;" type=file name="file_excel" id="file_excel"  accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel" />
					</form>
				</div>
				<div onclick="operList();" style="text-align: center;width:100px;margin:0 auto;border:1px solid #e1e1e1;cursor: pointer;float:right;">导入数据</div>
            </div>
        </div>
    </div>
</div>
<script type="text/javascript" src="<%=request.getContextPath()%>/js/jquery.pagination.js"></script>
<script type="text/javascript" src="<%=request.getContextPath()%>/js/zTree/v3/js/jquery.ztree.all-3.5.min.js"></script>
<script type="text/javascript">
    function operList() {
		var file_type = $("#file_excel").val();
		if(file_type.indexOf(".xls")>0||file_type.indexOf(".xlsx")>0){
			$("#contentForm").submit();
		}else{
			 layer.msg('只能选择xls、xlsx文件', {
				icon: 5,
				time: 2000 //2秒关闭（如果不配置，默认是3秒）
			 })
		}
        
        //window.setTimeout(shuaxin,5000);
    }
    function shuaxin() {
        parent.searchModel();
        var index = parent.layer.getFrameIndex(window.name);
        parent.layer.close(index);
    }
    function exportFile() {
        window.open('./down_model.jsp');
    }
</script>