package com.bcxin.ars.web.util;


import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.bcxin.ars.util.DES3;
import com.bcxin.ars.util.DateUtil;

import java.io.*;
import java.net.URL;
import java.net.URLEncoder;
import java.sql.*;


/**
 * 打印保安员证找不到头像处理
 * 手动补照片。注意照片格式必须是jpg
 *
 * 本地库先将照片保存到personimg
 * 再复制表，复制后表导成sql，比如personimg_0906.sql
 * ###广西内网
     #1、先执行 personimg_0906.sql，再执行下面的
     #2、更新头像
     delete from personimg where idNumber in ('452731198611220032');
     insert INTO personimg (active,create_time,update_time,update_by,idNumber,idImage)
     select active,now(),now(),'System',idNumber,idImage from personimg_0906 where idNumber in ('452731198611220032');
     #3、验证结果
     select * from personimg where idNumber in ('452731198611220032');
     #4、修改头像状态
     update security_certificate set havephoto=true where id in (63966);
     #5、广西内网正式环境
     用超管帐号登陆，保安员资格证，查询身份证：452731198611220032，点击“打印”，查看头像是否正常显示
     #6、还原打印状态
     update security_certificate set isprint=0 where id in (63966);
     #7删除临时表
     DROP TABLE personimg_0906;
 * @author linqinglin
 * @date 2018/08/21 14:09
 */
public class PersonImgTest {
    public static void main(String[] args) throws SQLException {
        //getimg();
        //Map<String,String> map = new HashMap<>();
        //map.put("45252219590520001X","img/1.jpg");//罗家驹
        ////map.put("452502197806082015","img/2.jpg");//黄寿顶
        ////map.put("450802199409058699","img/3.jpg");//李创
        //
        //for (String key : map.keySet()) {
        //    System.out.println("Key = " + key);
        //    System.out.println("value = " + map.get(key));
        //    save(key,getFilePath(map.get(key)));
        //}
        //save("452731198611220032","e:/1.jpg");
        try {
            System.out.println(DES3.encode(JSON.toJSONString(20000321L, SerializerFeature.WriteDateUseDateFormat)));
            System.out.println(DES3.encode(JSON.toJSONString(25028275L, SerializerFeature.WriteDateUseDateFormat)));
        } catch (Exception e) {
            e.printStackTrace();
        }
        createEleUrl(20015424L);

        //securityCertificateOld();
    }

    private static String getFilePath(String fileName) {
        URL url = ClassLoader.getSystemResource(fileName);
        /**
         * url.getFile() 得到这个文件的绝对路径
         */
        System.out.println(url.getFile());
        return url.getFile();
    }

    private static void save(String idNo,String imgPath) throws SQLException{
        Connection connection = null;
        String connectionURL = "jdbc:mysql://localhost:3306/bj_out_test";
        ResultSet rs = null;
        PreparedStatement psmnt = null;
        FileInputStream fis;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection(connectionURL, "root", "gogofis");
                File image = new File(imgPath);
                psmnt = connection.prepareStatement("INSERT INTO `personimg` (`active`, `create_time`, `update_time`, `update_by`, `idNumber`, `idImage`) VALUES (?,?,?,?,?,?);");
                psmnt.setBoolean(1, true);
                psmnt.setString(2, DateUtil.getCurrentDate(DateUtil.FORMAT1));
                psmnt.setString(3, DateUtil.getCurrentDate(DateUtil.FORMAT1));
                psmnt.setString(4, "System");
                psmnt.setString(5, idNo);
                fis = new FileInputStream(image);
                psmnt.setBinaryStream(6, (InputStream) fis, (int) (image.length()));
                int s = psmnt.executeUpdate();
                if (s > 0) {
                    System.out.println("Uploaded successfully !");
                } else {
                    System.out.println("unsucessfull to upload image.");
                }
        }
        catch (Exception ex) {
            System.out.println("Found some error : " + ex);
        } finally {
            connection.close();
            psmnt.close();
        }
    }

    private static void getimg() throws SQLException{
        Connection connection = null;
        String connectionURL = "jdbc:mysql://localhost:3306/gx_in_0815";
        ResultSet rs = null;
        PreparedStatement psmnt = null;
        // declare FileInputStream object to store binary stream of given image.
        FileOutputStream fos;
        try {
            File imageout = new File("D:/1111.png");
            fos=new FileOutputStream(imageout);

            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection(connectionURL, "root", "gogofis");
            psmnt = connection
                    .prepareStatement("select idImage from personimg_0821 where idNumber=? ");
            psmnt.setString(1, "452502197806082015");
            rs=psmnt.executeQuery();
            rs.next();
            Blob image_blob=rs.getBlob("idImage");
            InputStream is=image_blob.getBinaryStream();

            try {
            byte[] buffer = new byte[is.available()];
            is.read(buffer);
            fos.write(buffer);


            System.out.println(is.toString());

            } catch (IOException e1) {
                e1.printStackTrace();
            } finally{

                fos.close();
                is.close();
            }
        }
        // catch if found any exception during rum time.
        catch (Exception ex) {
            System.out.println("Found some error : " + ex);
        } finally {
            // close all the connections.
            System.out.println("Found success!");
            connection.close();
            psmnt.close();
        }

    }

    private static void securityCertificateOld() throws SQLException {
        Connection connection = null;
        String connectionURL = "jdbc:mysql://localhost:3306/bj_out_test?useUnicode=true&characterEncoding=utf8&useCompression=true";
        ResultSet rs = null;
        PreparedStatement psmnt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection(connectionURL, "root", "gogofis");
            psmnt = connection.prepareStatement("select xm,idnum,zsbh,fzrq,fzjgmc,securitypersonid from security_certificate_old");
            rs=psmnt.executeQuery();
            String electronCerUrl = "http://www.bktang.cn/pxxtfront/bbdapp/#/certificate?certificateId={electronCerUrl}$http://bjapi.baibaodun.cn:8803/";
            while (rs.next()){
                String data = DES3.encode(JSON.toJSONString(rs.getLong("securitypersonid"), SerializerFeature.WriteDateUseDateFormat));

                psmnt = connection.prepareStatement("INSERT INTO `security_certificate_olddata` (`xm`, `idnum`, `zsbh`, `fzrq`, `fzjgmc`, `electronCerUrl`) VALUES (?,?,?,?,?,?);");
                psmnt.setString(1, rs.getString("xm"));
                psmnt.setString(2, rs.getString("idnum"));
                psmnt.setString(3, rs.getString("zsbh"));
                psmnt.setString(4, rs.getString("fzrq"));
                psmnt.setString(5, rs.getString("fzjgmc"));
                psmnt.setString(6, electronCerUrl.replace("{electronCerUrl}",URLEncoder.encode(data, "utf-8")));
                int s = psmnt.executeUpdate();
                if (s > 0) {
                    System.out.println("Uploaded successfully !");
                } else {
                    System.out.println("unsucessfull to upload image.");
                }
            }
        }catch (Exception ex) {
            System.out.println("Found some error : " + ex);
        } finally {
            // close all the connections.
            System.out.println("Found success!");
            connection.close();
            psmnt.close();
        }
    }

    private static String createEleUrl(Long personId){
        try{
        String electronCerUrl = "http://www.bktang.cn/pxxtfront/bbdapp/#/certificate?certificateId={electronCerUrl}$http://bjapi.baibaodun.cn:8803/";
        String data = DES3.encode(JSON.toJSONString(personId, SerializerFeature.WriteDateUseDateFormat));
        electronCerUrl = electronCerUrl.replace("{electronCerUrl}",URLEncoder.encode(data, "utf-8"));
        System.out.println(electronCerUrl);
        return electronCerUrl;
        }catch (Exception e){

        }

        return null;
    }
}