package com.casic.birmm;

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

import java.sql.Connection;
import java.util.List;

public class CompareTwoDatabase {
    public static void main(String[] args) {

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

        try {
            String queryFromMysql = "select name, sex, simplename, p.ID_CARD_NO, " +
                    "left_iris_code1, right_iris_code1, LEFT_IRIS_CODE2, RIGHT_IRIS_CODE2, LEFT_IRIS_CODE3, RIGHT_IRIS_CODE3 " +
                    "from sys_person p, iris_data i, sys_dept d where p.id = i.PERSON_ID and p.DEPTID = d.id order by SIMPLENAME, p.ID";

            Connection connMysql = ConnectionManager.getMySQLConnectionFromC3P0();
            connMysql.setAutoCommit(false);

            Connection connSqlserver = ConnectionManager.getSQLServerConnectionFromC3P0();
            connSqlserver.setAutoCommit(false);

            // 从MySQL中查询所有的人
            QueryRunner runner = new QueryRunner();
            List<Object[]> personList = (List<Object[]>) runner.query(connMysql, queryFromMysql, new ArrayListHandler());
            System.out.println(personList.size());
            for (int i = 0; i < personList.size(); i++) {
                Object[] person = personList.get(i);
                String name = (String) person[0];
                String gender = (String) person[1];
                if (person[1].equals("1")) {
                    gender = "男";
                } else if (person[1].equals("2")) {
                    gender = "女";
                }
                String dept = (String) person[2];

                // 输出姓名
                logger.info(i + "\t" + name + "\t" + gender + "\t" + dept);

                String leftIris1 = "";
                if (person[4] != null) {
                    leftIris1 = ByteUtil.binToHexString((byte[]) person[4]);
                }
                String rightIris1 = "";
                if (person[5] != null) {
                    rightIris1 = ByteUtil.binToHexString((byte[]) person[5]);
                }
                String leftIris2 = "";
                if (person[6] != null) {
                    leftIris2 = ByteUtil.binToHexString((byte[]) person[6]);
                }
                String rightIris2 = "";
                if (person[7] != null) {
                    rightIris2 = ByteUtil.binToHexString((byte[]) person[7]);
                }
                String leftIris3 = "";
                if (person[8] != null) {
                    leftIris3 = ByteUtil.binToHexString((byte[]) person[8]);
                }
                String rightIris3 = "";
                if (person[9] != null) {
                    rightIris3 = ByteUtil.binToHexString((byte[]) person[9]);
                }

                // 从SQL server中查询人员
                String queryStr = "SELECT IId, Id, Name, Gender, Department, IDCardNo, " +
                        "LIrisCodeData, RIrisCodeData, LIrisCodeData2, RIrisCodeData2, LIrisCodeData3, RIrisCodeData3 " +
                        "FROM dbo.person where Name='" + name + "' and Gender='" + gender + "' and Department='" + dept + "'";
                List<Object[]> tempList = (List<Object[]>) runner.query(connSqlserver, queryStr, new ArrayListHandler());
                if (tempList != null && tempList.size() == 1) {
                    Object[] personInSQLServer = tempList.get(0);

                    String leftIris1SQLServer = "";
                    if (personInSQLServer[6] != null) {
                        leftIris1SQLServer = ByteUtil.binToHexString((byte[]) personInSQLServer[6]);
                    }
                    if (leftIris1.equals(leftIris1SQLServer) == false) {
                        logger.info("M: " + leftIris1);
                        logger.info("S: " + leftIris1SQLServer);
                    }

                    String rightIris1SQLServer = "";
                    if (personInSQLServer[7] != null) {
                        rightIris1SQLServer = ByteUtil.binToHexString((byte[]) personInSQLServer[7]);
                    }
                    if (rightIris1.equals(rightIris1SQLServer) == false) {
                        logger.info("M: " + rightIris1);
                        logger.info("S: " + rightIris1SQLServer);
                    }

                    String leftIris2SQLServer = "";
                    if (personInSQLServer[8] != null) {
                        leftIris2SQLServer = ByteUtil.binToHexString((byte[]) personInSQLServer[8]);
                    }
                    if (leftIris2.equals(leftIris2SQLServer) == false) {
                        logger.info("M: " + leftIris2);
                        logger.info("S: " + leftIris2SQLServer);
                    }

                    String rightIris2SQLServer = "";
                    if (personInSQLServer[9] != null) {
                        rightIris2SQLServer = ByteUtil.binToHexString((byte[]) personInSQLServer[9]);
                    }
                    if (rightIris2.equals(rightIris2SQLServer) == false) {
                        logger.info("M: " + rightIris2);
                        logger.info("S: " + rightIris2SQLServer);
                    }

                    String leftIris3SQLServer = "";
                    if (personInSQLServer[10] != null) {
                        leftIris3SQLServer = ByteUtil.binToHexString((byte[]) personInSQLServer[10]);
                    }
                    if (leftIris3.equals(leftIris3SQLServer) == false) {
                        logger.info("M: " + leftIris3);
                        logger.info("S: " + leftIris3SQLServer);
                    }

                    String rightIris3SQLServer = "";
                    if (personInSQLServer[11] != null) {
                        rightIris3SQLServer = ByteUtil.binToHexString((byte[]) personInSQLServer[11]);
                    }
                    if (rightIris3.equals(rightIris3SQLServer) == false) {
                        logger.info("M: " + rightIris3);
                        logger.info("S: " + rightIris3SQLServer);
                    }
                } else if (tempList.size() > 1) {
                    logger.info("same name: " + name);
                } else {
                    logger.info("missing: " + name + gender + dept + tempList.size());
                    logger.info(queryStr);
                }
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}