package com.casic.birmm;

import com.casic.birmm.util.ByteUtil;
import com.casic.birmm.util.ConnectionManager;
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.io.*;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author TAN YUE
 * 集团总部SQL server库转MySQL库
 */
public class TransCasicDatabase {

    private Map<String, Long> deptMap = new HashMap<>();

    public static void main(String[] args) {
        Connection conn = null;
        Connection connMysql = null;

        Logger logger = Logger.getLogger(TransCasicDatabase.class.getName());

        TransCasicDatabase tcd = new TransCasicDatabase();
        tcd.initDept();

        String queryStr = "SELECT IId, Id, Name, Gender, Department, IDCardNo, FaceImage, " +
                "LIrisCodeData, RIrisCodeData, LIrisCodeData2, RIrisCodeData2, LIrisCodeData3, RIrisCodeData3, " +
                "LIrisCodeData4, RIrisCodeData4, LIrisCodeData5, RIrisCodeData5, LIrisCodeData6, RIrisCodeData6 " +
                "FROM dbo.person order by IId";

        String base = "E:\\重装系统备份\\casic\\photo\\";

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

            connMysql = ConnectionManager.getMySQLConnectionFromC3P0();
            connMysql.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);

                Integer id = (Integer) person[0];
                String name = (String) person[2];
                String gender = (String) person[3];
                String department = (String) person[4];
                String faceImage  = (String) person[6];

                logger.info(id + "\t" + name + "\t" + gender + "\t" + department + "\t" + faceImage);

                // 1、插入人员表sys_person
                String insertPersonStr = "INSERT INTO SYS_PERSON (ID, DELFLAG, NAME, SEX, DEPTID, ID_CARD_NO, PERSON_TYPE, CARD_TYPE) VALUES (?, '0', ?, ?, ?, ?, '1', '111')";
                Object[] insertPersonParams = new Object[5];
                insertPersonParams[0] = id;
                insertPersonParams[1] = name;
                insertPersonParams[2] = gender.equals("男") ? "1" : "2";
                insertPersonParams[3] = tcd.deptMap.containsKey(department) ? tcd.deptMap.get(department) : tcd.deptMap.get("其他");
                insertPersonParams[4] = id;

                int personCount = runner.update(connMysql, insertPersonStr, insertPersonParams);
                System.out.println("insert person: " + personCount);

                // 2、插入人员图片
                File file = new File(base + faceImage);
                if (file.exists() == true) {
                    String base64Str = UpdatePersonPhoto.byteConverterBASE64(file);

                    if (null != base64Str && base64Str.equals("") == false) {
                        String insertPhotoStr = "insert into iris_person_photo (person_id, photo_data) values (?, ?)";

                        Object[] insertPhotoParams = new Object[2];
                        insertPhotoParams[0] = id;
                        insertPhotoParams[1] = "data:image/jpeg;base64," + base64Str;

                        int photoCount = runner.update(connMysql, insertPhotoStr, insertPhotoParams);
                        System.out.println("insert photo: " + photoCount);
                    }
                }

                // 3、插入虹膜数据
                String leftIris1 = "";
                if (person[7] != null) {
                    leftIris1 = ByteUtil.binToHexString((byte[]) person[7]);
                }
                logger.info("left1: " + leftIris1);

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

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

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

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

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

                if (null != person[7] && null != person[8] && null != person[9] &&
                    null != person[10] && null != person[11] && null != person[12]) {
                    String insertIrisStr = "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] = id;
                    insertIrisParams[1] = id;
                    insertIrisParams[2] = id;
                    insertIrisParams[3] = (byte[]) person[7];
                    insertIrisParams[4] = (byte[]) person[9];
                    insertIrisParams[5] = (byte[]) person[11];
                    insertIrisParams[6] = (byte[]) person[8];
                    insertIrisParams[7] = (byte[]) person[10];
                    insertIrisParams[8] = (byte[]) person[12];

                    int irisCount = runner.update(connMysql, insertIrisStr, insertIrisParams);
                    System.out.println("insert iris: " + irisCount);
                }

                logger.info("");
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                DbUtils.commitAndClose(connMysql);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private void initDept() {
        deptMap.put("集团领导", 1236854643826184195L);
        deptMap.put("办公厅(总助)", 1236854643826184196L);
        deptMap.put("办公厅", 1236854643826184197L);
        deptMap.put("计划发展部", 1236854643826184198L);
        deptMap.put("科研生产部", 1236854643826184199L);
        deptMap.put("科技与质量部", 1236854643826184200L);
        deptMap.put("空间工程部", 1236854643826184201L);
        deptMap.put("财务部", 1236854643826184202L);
        deptMap.put("资产运营部", 1236854643826184203L);
        deptMap.put("经济合作部", 1236854643826184204L);
        deptMap.put("国际业务部", 1236854643826184205L);
        deptMap.put("人力资源部", 1236854643826184206L);
        deptMap.put("安全保障部", 1236854643826184207L);
        deptMap.put("审计与风险管理部", 1236854643826184208L);
        deptMap.put("纪检监察部", 1236854643826184209L);
        deptMap.put("党群工作部", 1236854643826184210L);
        deptMap.put("科信部", 1236854643826184211L);
        deptMap.put("新闻中心", 1236854643826184212L);
        deptMap.put("总部车队", 1236854643826184213L);
        deptMap.put("其他", 1236854643826184214L);
    }
}