<%--
  Created by IntelliJ IDEA.
  User: mc
  Date: 2021/7/7
  Time: 15:12
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page import=" java.io.BufferedInputStream" %>
<%@ page import=" java.io.BufferedOutputStream" %>
<%@ page import=" java.io.File" %>
<%@ page import=" java.io.FileInputStream" %>
<%@ page import=" java.io.FileOutputStream" %>
<%@ page import=" java.io.IOException" %>
<%@ page import=" java.io.InputStream" %>
<%@ page import=" java.io.OutputStream" %>
<%@ page import=" java.text.SimpleDateFormat" %>
<%@ page import=" javax.servlet.http.HttpServletResponse" %>
<%@ page import="com.censoft.common.base.Pub" %>
<%@ page import="com.censoft.common.db.ConnectionFactory" %>
<%@ page import="com.censoft.db.Logger" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.util.*" %>
<%@ page import="org.apache.poi.ss.util.CellRangeAddress" %>
<%@ page import="org.apache.poi.xssf.usermodel.*" %>
<%!
    private void download(String path, HttpServletResponse response) {
        try {
            // path是指欲下载的文件的路径。
            File file = new File(path);
            // 取得文件名。
            String filename = file.getName();
            // 以流的形式下载文件。
            InputStream fis = new BufferedInputStream(new FileInputStream(path));
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();
            // 清空response
            response.reset();
            // 设置response的Header
            response.addHeader("Content-Disposition", "attachment;filename="+ new String(filename.getBytes()));
            response.addHeader("Content-Length", "" + file.length());
            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            toClient.write(buffer);
            toClient.flush();
            toClient.close();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }
%>
<%
    request.setCharacterEncoding("UTF-8");
    response.setCharacterEncoding("UTF-8");
    ConnectionFactory cf = new ConnectionFactory();
    Pub pub = new Pub();
    Connection conn = null;
    Vector v_list = null;
    Hashtable ht =null;
    String zc_status ="";
    List list = new ArrayList();
    String[] field = request.getParameterValues("zc_ids");
    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
    String fileName = sdf.format(new Date()) + ".xlsx";
    // 文件路径
    String filePath = fileName;
    try {
        conn = com.censoft.db.DBUtil.getConnection();
        StringBuffer sb = new StringBuffer();
        sb.append("select t1.*,t2.xm_id,t2.ly_dept,t2.ly_user,t2.bg_time,t2.ly_time " +
                "from zc_register t1 left join zc_ly t2 on t1.zc_id=t2.zc_id where 1=1 ");
        sb.append(" and t1.zc_id in (");
        if(field!=null&&field.length>0){
            for(int i=0;i<field.length;i++){
                if(i==field.length-1){
                    sb.append("'" + field[i] + "'");
                }else{
                    sb.append("'" + field[i] + "',");
                }
            }
        }
        sb.append(" )");
        v_list = cf.doQuery(conn, sb.toString(), null, new Object[]{});
        if (v_list == null) {
            System.out.println("查询出错"+cf.getMsg());
            return;
        }
        if (v_list != null && v_list.size() > 0) {
            ht = (Hashtable) v_list.get(0);
            String bg_time = pub.trimNull((String) ht.get("bg_time"));
            zc_status = pub.trimNull((String) ht.get("zc_status"));
            if("0".equals(zc_status)){
                zc_status="闲置";
            }else if("3".equals(zc_status)){
                zc_status="领用";
            }else if("1".equals(zc_status)){
                zc_status="已提交";
            }else if("2".equals(zc_status)){
                zc_status="审核中";
            }else if("4".equals(zc_status)){
                zc_status="盘点中";
            }else if("4_1".equals(zc_status)){
                zc_status="盘点结束";
            }else if("5".equals(zc_status)){
                zc_status="维修中";
            }else if("6".equals(zc_status)){
                zc_status="报废";
            }
            if(!"".equals(bg_time)){
                zc_status="变更";
            }
        }
        // 输出流
        OutputStream os = new FileOutputStream(filePath);
        // 工作区
        XSSFWorkbook wb = new XSSFWorkbook();
        // 工作区名
        XSSFSheet sheet = wb.createSheet("资产卡片");
        XSSFFont font1=wb.createFont();
        XSSFCellStyle style=wb.createCellStyle();
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//居中
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//水平垂直
        font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//加粗
        style.setFont(font1);
        // 创建表头
        XSSFRow row = sheet.createRow(0);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 5));
        //String values="资产编号,资产名称,所属项目;资产国标大类,资产分类,资产分类代码;设备类型,品牌,规格型号;价值类型,价值,管理部门;使用部门,使用人,资产状态;领用时间," +
                //"出厂日期,财务入账日期;会计凭证号,财政性资金,非财政性资金;累计折旧,净值,已提月折旧;是否利旧,车辆识别号,车辆用途分类;发动机号," +
                //"存放地,备注";
        XSSFCell row_1=row.createCell(0);
        row_1.setCellValue("资产卡片信息");
        row_1.setCellStyle(style);
        XSSFRow rows = sheet.createRow(1);
        rows.createCell(0).setCellValue("资产编号");
        rows.createCell(1).setCellValue(pub.trimNull((String) ht.get("zc_id")));
        rows.createCell(2).setCellValue("资产名称");
        rows.createCell(3).setCellValue(pub.trimNull((String) ht.get("zc_name")));
        rows.createCell(4).setCellValue("所属项目");
        rows.createCell(5).setCellValue(pub.trimNull((String) ht.get("xm_id")));
        XSSFRow rows_2 = sheet.createRow(2);
        rows_2.createCell(0).setCellValue("资产国标大类");
        rows_2.createCell(1).setCellValue(pub.trimNull((String) ht.get("zc_gj_type")));
        rows_2.createCell(2).setCellValue("资产分类");
        rows_2.createCell(3).setCellValue(pub.trimNull((String) ht.get("zc_fl")));
        rows_2.createCell(4).setCellValue("资产分类代码");
        rows_2.createCell(5).setCellValue(pub.trimNull((String) ht.get("zc_fl_code")));
        XSSFRow rows_3 = sheet.createRow(3);
        rows_3.createCell(0).setCellValue("设备类型");
        rows_3.createCell(1).setCellValue(pub.trimNull((String) ht.get("sb_type")));
        rows_3.createCell(2).setCellValue("品牌");
        rows_3.createCell(3).setCellValue(pub.trimNull((String) ht.get("pinpai")));
        rows_3.createCell(4).setCellValue("规格型号");
        rows_3.createCell(5).setCellValue(pub.trimNull((String) ht.get("gg_type")));
        XSSFRow rows_4 = sheet.createRow(4);
        rows_4.createCell(0).setCellValue("价值类型");
        rows_4.createCell(1).setCellValue(pub.trimNull((String) ht.get("jz_type")));
        rows_4.createCell(2).setCellValue("价值");
        rows_4.createCell(3).setCellValue(pub.trimNull((String) ht.get("jiazhi")));
        rows_4.createCell(4).setCellValue("管理部门");
        rows_4.createCell(5).setCellValue(pub.trimNull((String) ht.get("dept")));
        XSSFRow rows_5 = sheet.createRow(5);
        rows_5.createCell(0).setCellValue("使用部门");
        rows_5.createCell(1).setCellValue(pub.trimNull((String) ht.get("ly_dept")));
        rows_5.createCell(2).setCellValue("使用人");
        rows_5.createCell(3).setCellValue(pub.trimNull((String) ht.get("ly_user")));
        rows_5.createCell(4).setCellValue("资产状态");
        rows_5.createCell(5).setCellValue(zc_status);
        XSSFRow rows_6 = sheet.createRow(6);
        rows_6.createCell(0).setCellValue("领用时间");
        rows_6.createCell(1).setCellValue(pub.trimNull((String) ht.get("chuchang_date")));
        rows_6.createCell(2).setCellValue("出厂日期");
        rows_6.createCell(3).setCellValue(pub.trimNull((String) ht.get("cw_date")));
        rows_6.createCell(4).setCellValue("财务入账日期");
        rows_6.createCell(5).setCellValue(pub.trimNull((String) ht.get("cw_date")));
        XSSFRow rows_7 = sheet.createRow(7);
        rows_7.createCell(0).setCellValue("会计凭证号");
        rows_7.createCell(1).setCellValue(pub.trimNull((String) ht.get("kj_id")));
        rows_7.createCell(2).setCellValue("财政性资金");
        rows_7.createCell(3).setCellValue(pub.trimNull((String) ht.get("czx_money")));
        rows_7.createCell(4).setCellValue("非财政性资金");
        rows_7.createCell(5).setCellValue(pub.trimNull((String) ht.get("fczx_money")));
        XSSFRow rows_8 = sheet.createRow(8);
        rows_8.createCell(0).setCellValue("累计折旧");
        rows_8.createCell(1).setCellValue(pub.trimNull((String) ht.get("ljzj")));
        rows_8.createCell(2).setCellValue("净值");
        rows_8.createCell(3).setCellValue(pub.trimNull((String) ht.get("jingzhi")));
        rows_8.createCell(4).setCellValue("已提月折旧");
        rows_8.createCell(5).setCellValue(pub.trimNull((String) ht.get("ytyzj")));
        XSSFRow rows_9 = sheet.createRow(9);
        rows_9.createCell(0).setCellValue("是否利旧");
        rows_9.createCell(1).setCellValue(pub.trimNull((String) ht.get("is_lj")));
        rows_9.createCell(2).setCellValue("车辆识别号");
        rows_9.createCell(3).setCellValue(pub.trimNull((String) ht.get("car_id")));
        rows_9.createCell(4).setCellValue("车辆用途分类");
        rows_9.createCell(5).setCellValue(pub.trimNull((String) ht.get("car_type")));
        XSSFRow rows_10 = sheet.createRow(10);
        rows_10.createCell(0).setCellValue("发动机号");
        rows_10.createCell(1).setCellValue(pub.trimNull((String) ht.get("car_fdj_id")));
        rows_10.createCell(2).setCellValue("存放地");
        rows_10.createCell(3).setCellValue(pub.trimNull((String) ht.get("car_address")));
        rows_10.createCell(4).setCellValue("备注");
        rows_10.createCell(5).setCellValue(pub.trimNull((String) ht.get("beizhu")));
        wb.write(os);
        // 关闭输出流
        os.close();
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
            }
        }
    }
    download(filePath, response);
%>