package com.bcxin.ars.web.util;


import com.bcxin.ars.util.DES3;

import java.io.FileInputStream;
import java.net.URLDecoder;
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 PersonCerTest {
    public static void main(String[] args) throws SQLException {
        read();
    }


    private static void read() throws SQLException{
        Connection connection = null;
        String connectionURL = "jdbc:mysql://localhost:3306/bj_in_test";
        ResultSet rs = null;
        PreparedStatement psmnt = null;
        FileInputStream fis;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection(connectionURL, "root", "gogofis");
            psmnt = connection.prepareStatement("SELECT perCerId,arsId from per_cer_info_confirm where arsId is not null and cerId is null");

            ResultSet resultSet = psmnt.executeQuery();
            while (resultSet.next()){
                String arsId = resultSet.getString("arsId");

                try {

                    arsId = URLDecoder.decode(arsId,"utf-8");
                    arsId = DES3.decode(arsId);

                    System.out.println(arsId);

                    save(resultSet.getString("perCerId"),arsId);
                }catch (Exception e){

                }
            }
        }
        catch (Exception ex) {
            System.out.println("Found some error : " + ex);
        } finally {
            connection.close();
            psmnt.close();
        }
    }


    private static void save(String perCerId,String arsId) throws SQLException{
        Connection connection = null;
        String connectionURL = "jdbc:mysql://localhost:3306/bj_in_test";
        ResultSet rs = null;
        PreparedStatement psmnt = null;
        FileInputStream fis;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection(connectionURL, "root", "gogofis");
            psmnt = connection.prepareStatement("update per_cer_info_confirm set cerId =? where perCerId =?");
            psmnt.setString(1, arsId);
            psmnt.setString(2, perCerId);
            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();
        }
    }
}