package com.gyee;


import net.coobird.thumbnailator.Thumbnails;

import javax.imageio.ImageIO;
import java.awt.geom.AffineTransform;
import java.awt.image.AffineTransformOp;
import java.awt.image.BufferedImage;
import java.io.*;
import java.sql.*;
import java.util.*;

/**
 * 图形开票 人员电子签名  数据库人员处理
 */
public class SqlSeverTicket {

    public static void main(String []args) throws SQLException {

        String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        //加载JDBC驱动
        String dbURL = "jdbc:sqlserver://10.155.32.2:1433;DatabaseName=fdeam";
        //连接服务器和数据库
        String userName = "sa"; //默认用户名
        String userPwd = "Gyee@321#!"; //密码
        Connection conn = null;
        try {
//            Class.forName(driverName);
            conn = DriverManager.getConnection(dbURL, userName, userPwd);
            //System.out.println("Connection Successful!");
            //如果连接成功 控制台输出Connection  Successful!
            Map<String, List> labor = getLabor(conn);
            setLabor(conn, labor);
//            List<String> list = getLabors(conn);
//            for(int i = 0; i < list.size(); i++){
//                setImage(conn, "D:/document/两票相关材料/人员电子签名/电子签名thumb2/" + list.get(i).toLowerCase() +".png", list.get(i).toLowerCase());
//            }
//            setImage(conn, "D:/document/两票相关材料/zhanghy.png", "zhanghy");

            //        String fileName = "wangbing.png";
//
        String filePath1 = "D:/document/两票相关材料/人员电子签名/";
        String filePath2 = "D:/document/两票相关材料/";
//        batchThumb(filePath1, filePath2);
//        singleThumb(filePath1, filePath2, "chenl.png");
        setImage(conn, "D:/document/两票相关材料/chenl.png", "chenl");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            conn.close();
        }
    }

    // 获取人员信息
    public static Map<String, List> getLabor(Connection conn){
        Map<String, List> labors = new HashMap<>();

        try {
            PreparedStatement ps = conn.prepareStatement("select * from LABOR");
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                List<String> list = new ArrayList<>();
                String laborNum = rs.getString("LABORNUM");
                String laborName = rs.getString("LABORNAME");
                String deptNum = rs.getString("DEPTNUM");
                list.add(0, laborName);
                list.add(1, deptNum);
                labors.put(laborNum, list);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return labors;
    }

    public static List<String> getLabors(Connection conn){
        List<String> list = new ArrayList<>();

        try {
            PreparedStatement ps = conn.prepareStatement("select * from SIGNPICTURE");
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                String laborNum = rs.getString("LABORNUM");
                list.add(laborNum);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    // signpicture表插入人员信息
    public static void setLabor(Connection conn, Map map) throws SQLException {
        List<String> lists = new ArrayList<>();
        try {
            PreparedStatement ps = conn.prepareStatement("select * from SIGNPICTURE");
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                String laborNum = rs.getString("LABORNUM").toLowerCase();
                lists.add(laborNum);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement("Insert into SIGNPICTURE (LABORNUM,LABORNAME,DEPTNUM) values (?,?,?)");
            Iterator<Map.Entry<String, List>> entries = map.entrySet().iterator();
            while(entries.hasNext()){
                Map.Entry<String, List> entry = entries.next();
                String key = entry.getKey();
                if(!lists.contains(key.toLowerCase()))
                {
                    List list = entry.getValue();
                    ps.setString(1,key);
                    ps.setString(2, String.valueOf(list.get(0)));
                    ps.setString(3, String.valueOf(list.get(1)));
//                ps.setBinaryStream(3,fis,(int)file.length());
                    ps.executeUpdate();
                }
            }
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ps.close();
        }
    }

    /**
     * 写入图片
     * @param conn
     * @param filePath
     */
    public static void setImage(Connection conn, String filePath, String laborNum)
    {
        FileInputStream fis=null;
        File file = new File(filePath);
        try{
            fis = new FileInputStream(file);
        }catch(FileNotFoundException e){
            //System.out.println("Not find file!");
        }
        try{
//            PreparedStatement ps = conn.prepareStatement("Insert into SIGNPICTURE (IMAGE) values (?)");
            PreparedStatement ps = conn.prepareStatement("update SIGNPICTURE set IMAGE = ? where LABORNUM = '"+laborNum+"'");
            ps.setBinaryStream(1,fis,(int)file.length());
            ps.executeUpdate();
            ps.close();
            fis.close();
            //System.out.println("写进去了！");
        }catch(Exception e){
            //System.out.println("fis cann't cloase!");
        }
    }


    // 图片签名处理
    private static void batchThumb(String filePath1, String filePath2){
        File file = new File(filePath1);
        File[] files = file.listFiles();
        if(files != null){
            for(int i = 0; i < files.length; i++){
                String fileName = files[i].getName();

                try {
                    Thumbnails.of(filePath1 + fileName)
                            .size(100, 40)
                            .toFile(filePath2 + fileName);
                } catch (IOException e) {
                    e.printStackTrace();
                } finally {
                    continue;
                }
            }
        }
    }

    // 图片签名处理
    private static void singleThumb(String filePath1, String filePath2, String fileName) throws IOException {
        Thumbnails.of(filePath1 + fileName)
                .size(100, 40)
                .toFile(filePath2 + fileName);
    }
}