package com.casic.birmm;

import com.casic.birmm.util.ByteUtil;
import com.casic.birmm.util.ConnectionManager;
import com.casic.birmm.util.IdWorker;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

public class ExtractPersonDataFromSQLServer {

    public static void main(String[] args) {
        Connection conn = null;
        Connection mysqlConn = null;
        Logger logger = Logger.getLogger(ExtractPersonDataFromSQLServer.class.getName());

        UpdatePersonIdCard upi = new UpdatePersonIdCard();
        upi.initDept();

        String queryStr = "SELECT IId, Id, Name, Gender, Department, CreationTime, " +
                "LIrisCodeData, RIrisCodeData, LIrisCodeData2, RIrisCodeData2, LIrisCodeData3, RIrisCodeData3, FaceImage " +
                "FROM dbo.person WHERE IId = 1895";

        IdWorker worker = new IdWorker(1);

        try {
            conn = ConnectionManager.getSQLServerConnectionFromC3P0();
            conn.setAutoCommit(false);

            mysqlConn = ConnectionManager.getMySQLConnectionFromC3P0();
            mysqlConn.setAutoCommit(false);

            QueryRunner runner = new QueryRunner();
            List<Object[]> tempList = (List<Object[]>) runner.query(conn, queryStr, new ArrayListHandler());

            System.out.println(tempList.size());
            for (int i = 0; i < tempList.size(); i++) {
                Object[] person = tempList.get(i);
                String content = "";

                content += i + "\t" + person[2] + "\t" + person[3] + "\t" + person[4] + "\t" + person[5];
                logger.info(content);

                // 插入sys_person表
                String insertPerson = "INSERT INTO SYS_PERSON (ID, DELFLAG, NAME, SEX, DEPTID, ID_CARD_NO, PERSON_TYPE, PERSON_CODE, OPRATION_ID, DUTY, NATION, CARD_TYPE) " +
                        "VALUES (?, '0', ?, ?, ?, ?, '1', ?, '1', '0', '0', '111')";
                Object[] insertPersonParams = new Object[6];
                insertPersonParams[0] = worker.nextId(); // id
                insertPersonParams[1] = (String) person[2]; // 姓名
                insertPersonParams[2] = ((String) person[3]).equalsIgnoreCase("男") ? "1" : "2"; // 性别
                insertPersonParams[3] = 1273597266669056002L; // 所在部门
                insertPersonParams[4] = "110106199507280022"; // 身份证号
                insertPersonParams[5] = insertPersonParams[0];
                runner.update(mysqlConn, insertPerson, insertPersonParams);


                // 插入sys_person_ext表
                String insertExt = "INSERT INTO SYS_PERSON_EXT (ID, TARGET_ID) VALUES (?, ?)";
                Object[] insertExtParams = new Object[2];
                insertExtParams[0] = insertPersonParams[0];
                insertExtParams[1] = insertPersonParams[0];
                runner.update(mysqlConn, insertExt, insertExtParams);

                String leftIris1 = "";
                if (person[6] != null) {
                    leftIris1 = ByteUtil.binToHexString((byte[]) person[6]);
                }
                logger.info("left1: " + leftIris1);

                String rightIris1 = "";
                if (person[7] != null) {
                    rightIris1 = ByteUtil.binToHexString((byte[]) person[7]);
                }
                logger.info("right1: " + rightIris1);

                String leftIris2 = "";
                if (person[8] != null) {
                    leftIris2 = ByteUtil.binToHexString((byte[]) person[8]);
                }
                logger.info("left2: " + leftIris2);

                String rightIris2 = "";
                if (person[9] != null) {
                    rightIris2 = ByteUtil.binToHexString((byte[]) person[9]);
                }
                logger.info("right2: " + rightIris2);

                String leftIris3 = "";
                if (person[10] != null) {
                    leftIris3 = ByteUtil.binToHexString((byte[]) person[10]);
                }
                logger.info("left3: " + leftIris3);

                String rightIris3 = "";
                if (person[11] != null) {
                    rightIris3 = ByteUtil.binToHexString((byte[]) person[11]);
                }
                logger.info("right3: " + rightIris3);

                // 插入iris_data表
                String insertIris = "INSERT INTO IRIS_DATA (ID, PERSON_ID, ID_CARD_NO, LEFT_IRIS_CODE1, LEFT_IRIS_CODE2, LEFT_IRIS_CODE3, RIGHT_IRIS_CODE1, RIGHT_IRIS_CODE2, RIGHT_IRIS_CODE3) " +
                        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
                Object[] insertIrisParams = new Object[9];
                insertIrisParams[0] = worker.nextId();
                insertIrisParams[1] = insertPersonParams[0];
                insertIrisParams[2] = insertPersonParams[4];
                insertIrisParams[3] = (byte[]) person[6];
                insertIrisParams[4] = (byte[]) person[8];
                insertIrisParams[5] = (byte[]) person[10];
                insertIrisParams[6] = (byte[]) person[7];
                insertIrisParams[7] = (byte[]) person[9];
                insertIrisParams[8] = (byte[]) person[11];
                runner.update(mysqlConn, insertIris, insertIrisParams);
/*
                String faceImageBytes = "";
                if (person[12] != null) {
                    String queryFaceImage = "select ImageBytes FROM dbo.Image where Guid = '" + (String) person[12] + "'";
                    List<Object[]> faceImage = (List<Object[]>) runner.query(conn, queryFaceImage, new ArrayListHandler());
                    if (faceImage != null && faceImage.size() == 1) {
                        String base64Str = Base64.getEncoder().encodeToString((byte[]) faceImage.get(0)[0]);
                        logger.info("faceImage: " + "data:image/png;base64," + base64Str);

                        // 插入iris_person_photo表
                        String insertPhoto = "INSERT INTO IRIS_PERSON_PHOTO (PERSON_ID, PHOTO_DATA) VALUES (?, ?)";
                        Object[] insertPhotoParams = new Object[2];
                        insertPhotoParams[0] = insertPersonParams[0];
                        insertPhotoParams[1] = "data:image/png;base64," + base64Str;
                        runner.update(mysqlConn, insertPhoto, insertPhotoParams);
                    }
                }
*/
                logger.info("\n");
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        finally {
            try {
                DbUtils.commitAndClose(mysqlConn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}