package com.happy.action;

import com.happy.Until.ResponseUtil;
import jxl.Sheet;
import jxl.Workbook;
import net.sf.json.JSONObject;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.struts2.ServletActionContext;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@WebServlet("/upload")
public class Upload extends HttpServlet {

    private static final long serialVersionUID = 1L;

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=utf-8");
        /*
        InputStream in = request.getInputStream();
        BufferedReader br = new BufferedReader(new InputStreamReader(in));
        String str = "";
        while((str=br.readLine())!=null){
            System.out.println(str);
        }
        */
        System.out.println("[-------------------------------------------------------]");
        String filepath=request.getServletContext().getRealPath("/download/");
        File file = new File(filepath);
        if(!file.exists()) {
            file.mkdir();
        }
        DiskFileItemFactory factory = new DiskFileItemFactory();
        ServletFileUpload upload = new ServletFileUpload(factory);
        try {
            List<FileItem> items= upload.parseRequest(request);
            String ex = "";
            for(FileItem item: items) {
                ex = filepath+item.getName();
                if(!item.isFormField()) {
                    item.write(new File(filepath+item.getName()));
                }
                if(item.isFormField()){
                    System.out.println(item.getString());
                    System.out.println(item.getFieldName());
                }
            }
            File files = new File(ex);
            Workbook workbook = Workbook.getWorkbook(files);
            Sheet sheet = workbook.getSheet(0);
            ArrayList<ArrayList> aa = new ArrayList<>();
            for (int i=1; i<sheet.getRows(); i++){
                ArrayList a = new ArrayList();
                for (int j=0; j<sheet.getColumns(); j++){
                    a.add(sheet.getCell(j, i).getContents());
                }
                aa.add(a);
            }
            ApplicationContext ac = new ClassPathXmlApplicationContext(
                    "applicationContext.xml");
            DataSource dataSource = (DataSource) ac.getBean("slaveDataSource");
            Connection conn = null;
            ResultSet rs = null;
            try {
                conn = dataSource.getConnection();
                PreparedStatement ps = conn.prepareStatement("select * from build_water order by id asc");
                rs = ps.executeQuery();
                System.out.println("成功获取数据");
                ArrayList a1 = new ArrayList();
                while (rs.next()){
                    a1.add(rs.getInt("id"));
                }
                for (int i=0; i<aa.size(); i++){
                    if(!a1.contains(Integer.parseInt(String.valueOf(aa.get(i).get(0))))) {
                        String sql = String.format("insert into build_water(id, school, build, floors, dom, user_id) " +
                                "values('%s', '%s', '%s', '%s', '%s', '%s')", aa.get(i).get(0), aa.get(i).get(1), aa.get(i).get(2), aa.get(i).get(3), aa.get(i).get(4), aa.get(i).get(5));
                        PreparedStatement pst = conn.prepareStatement(sql);
                        pst.execute();
                        pst.close();
                    }else{
                        String sql = String.format("update build_water set school='%s', build='%s', floors='%s', dom='%s', user_id='%s' " +
                                "where id='%s'", aa.get(i).get(1), aa.get(i).get(2), aa.get(i).get(3), aa.get(i).get(4), aa.get(i).get(5), aa.get(i).get(0));
                        PreparedStatement pst2 = conn.prepareStatement(sql);
                        pst2.execute();
                        pst2.close();
                    }
                }
            } catch (SQLException e) {
                System.out.println("导入失败！");
                response.setContentType("text/html;charset=utf-8");
                PrintWriter out = response.getWriter();
                out.println("有重复id");
                out.flush();
                out.close();
            } finally {
                try {
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException e) {
                    // TODO: handle exception
                }
            }
        } catch (FileUploadException e) {
            e.printStackTrace();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        System.out.println("导入成功！");
        response.setContentType("text/html;charset=utf-8");
        PrintWriter out = response.getWriter();
        out.println("导入成功");
        out.flush();
        out.close();
    }
}