/**
 * Created by Kane on 2017/3/28.
 */

package com.cpic.telsale.DataImport;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.*;
import com.cpic.telsale.TelsalePolicyRecord;
import com.cpic.util.staff.*;

public class DataImport
{
    private DataImport()
    {
    }

    /**
     * @Description: 从xlsx文件读取电销转介绍保单记录，保存在一个ArrayList中并返回。
     * @Param: fileName xlsx文件的路径
     * @return: java.util.ArrayList<com.cpic.telsale.TelsalePolicyRecord>
     * @Author: 王炜
     * @Date: 2021/6/21
     */
    private static ArrayList<TelsalePolicyRecord> readTelsalePolicyDataFromXlsx( String fileName, int sheetIndex )
            throws IOException,
                   XlsFileFormatException
    {
        ArrayList<TelsalePolicyRecord> policyList = new ArrayList<TelsalePolicyRecord>();

        TelsalePolicyRecord record = null;

        FileInputStream xlsxFile = null;
        HSSFWorkbook    workBook = null;
        HSSFSheet       sheet    = null;
        HSSFRow         row      = null;

        int    rowIndex     = 0;
        int    rowCount     = 0;
        int    errorCount   = 0;
        String errorMessage = "数据错误，请修正后重新上传！<br>\n";

        try
        {
            xlsxFile = new FileInputStream( fileName );
            workBook = new HSSFWorkbook( xlsxFile );
            sheet    = workBook.getSheetAt( sheetIndex );
            rowIndex = sheet.getFirstRowNum();
            rowCount = sheet.getLastRowNum();

            while ( rowIndex <= rowCount )
            {
                //用第一个单元格判断是不是空行，如果cell为null视为空行
                row = sheet.getRow( rowIndex );
                HSSFCell cell = row.getCell( 0 );

                if ( cell == null )
                {
                    rowIndex++;
                    continue;
                }

                String policyNo     = cell.getStringCellValue();
                String operatorCode = getStringValueFromHSSFCell( row.getCell( 1 ) );

                //判断数据是否有效
                if ( policyNo.length() != 20 || operatorCode.length() == 0 || operatorCode.length() > 6 )
                {
                    errorMessage += "第" + ( rowIndex + 1 ) + "行，";

                    if ( policyNo.length() != 20 )
                    {
                        //以后再加入正则表达式判断
                        errorMessage += "保单号" + policyNo + "错误!";
                    }

                    if ( operatorCode.length() == 0 || operatorCode.length() > 6 )
                    {
                        errorMessage += "经办人" + operatorCode + "错误！";
                    }

                    errorMessage += "<br>\n";

                    rowIndex++;
                    errorCount++;

                    continue;
                }

                //读取数据
                record = new TelsalePolicyRecord( policyNo, operatorCode );

                policyList.add( record );

                rowIndex++;
            }
        }
        finally
        {
            if ( workBook != null )
            {
                workBook.close();
                xlsxFile.close();
            }
        }

        //判断是否要抛出读取错误
        if ( errorCount > 0 )
        {
            throw new XlsFileFormatException( errorMessage );
        }

        return policyList;
    }

    /**
     * @Description: 读取单元格的数据，以字符串形式返回值。
     * @Param: poi的单元格对象
     * @return: java.lang.String 返回字符串形式的值
     * @Author: 王炜
     * @Date: 2021/6/27  **/
    private static String getStringValueFromHSSFCell( HSSFCell cell )
    {
        String value = null;

        if ( cell == null )
        {
            return "";
        }

        switch ( cell.getCellType() )
        {
            case HSSFCell.CELL_TYPE_NUMERIC:
            {
                //判断是不是整数
                double dValue = cell.getNumericCellValue();

                if ( dValue == (int) dValue )
                {
                    value = String.valueOf( (int) dValue );
                }
                else
                {
                    value = String.valueOf( dValue );
                }

                break;
            }
            case HSSFCell.CELL_TYPE_STRING:
            {
                value = cell.getStringCellValue();
                break;
            }
            case HSSFCell.CELL_TYPE_BLANK:
            {
                value = "";
                break;
            }
            default:
            {
                value = "";
                break;
            }
        }

        return value;
    }

    /***
     * @Description: 将电销保单数据写入到oracle数据库，通过数据库验证保单和经办人有效性，并获取经办人和录入人详细信息。
     *               要么全表成功，要么全表失败。
     *               错误信息保存在errorMessage，全表完成后检查错误数量，有错误就抛出errorMessage。
     * @Param: dbURL oracle的链接字符串
     * @Param: userName 用户名
     * @Param: password 密码
     * @Param: policyList 保存电销保单数据的ArrayList
     * @return: void
     * @Author: 王炜
     * @Date: 2021/6/23
     */
    private static void saveTelsalePolicyDataToOracle( Connection oracleConnection,
                                                       ArrayList<TelsalePolicyRecord> policyList )
            throws NullPointerException,
                   TelsalePolicyDataSaveToOracleException,
                   SQLException

    {
        CallableStatement callStmt = null;

        String callProcedureSql = "{call telsale_policy_check_pkg.save_policy(?,?,?,?,?,?,?,?,?,?,?,?,?)}";
        String errorMessage     = "写入oracle数据库错误，请按照提示修正后重新上传！<br>\n";

        //用来记录异常记录的数量
        int errorCount = 0;

        if ( policyList == null )
        {
            throw new NullPointerException( "policyList参数为Null" );
        }

        try
        {
            Class.forName( "oracle.jdbc.driver.OracleDriver" );
            callStmt = oracleConnection.prepareCall( callProcedureSql );

            for ( TelsalePolicyRecord recordIterator : policyList )
            {
                callStmt.setString( 1, recordIterator.getPolicyNo() );
                callStmt.setString( 2, recordIterator.getOperatorCode() );
                callStmt.registerOutParameter( 3, Types.VARCHAR );
                callStmt.registerOutParameter( 4, Types.VARCHAR );
                callStmt.registerOutParameter( 5, Types.VARCHAR );
                callStmt.registerOutParameter( 6, Types.VARCHAR );
                callStmt.registerOutParameter( 7, Types.VARCHAR );
                callStmt.setString( 8, recordIterator.getEntryStaffCode() );
                callStmt.registerOutParameter( 9, Types.VARCHAR );
                callStmt.registerOutParameter( 10, Types.VARCHAR );
                callStmt.registerOutParameter( 11, Types.VARCHAR );
                callStmt.registerOutParameter( 12, Types.VARCHAR );
                callStmt.registerOutParameter( 13, Types.VARCHAR );

                try
                {
                    callStmt.execute();

                    recordIterator.setOperatorName( callStmt.getString( 3 ) );
                    recordIterator.setOperatorSectionOfficeCode( callStmt.getString( 4 ) );
                    recordIterator.setOperatorSectionOfficeName( callStmt.getString( 5 ) );
                    recordIterator.setOperatorDepartmentCode( callStmt.getString( 6 ) );
                    recordIterator.setOperatorDepartmentName( callStmt.getString( 7 ) );
                    recordIterator.setEntryStaffName( callStmt.getString( 9 ) );
                    recordIterator.setEntryStaffSectionOfficeCode( callStmt.getString( 10 ) );
                    recordIterator.setEntryStaffSectionOfficeName( callStmt.getString( 11 ) );
                    recordIterator.setEntryStaffDepartmentCode( callStmt.getString( 12 ) );
                    recordIterator.setEntryStaffDepartmentName( callStmt.getString( 13 ) );
                }
                catch ( SQLException error )
                {
                    //写入时发生错误，记录下错误！
                    switch ( error.getErrorCode() )
                    {
                        case -20001:
                        {
                            errorMessage += "保单号" +
                                    recordIterator.getPolicyNo() +
                                    "，已存在，如需修改数据请联系信息技术部。<br> \n";
                        }
                        default:
                        {
                            errorMessage += "保单号" +
                                    recordIterator.getPolicyNo() +
                                    "保存失败，错误提示：" +
                                    error.getMessage() + "<br>\n";
                        }
                    }

                    errorCount++;
                }
            }
        }
        catch ( ClassNotFoundException error )
        {
            error.printStackTrace();
        }

        //根据错误数量，判断是否抛出异常
        if ( errorCount > 0 )
        {
            throw new TelsalePolicyDataSaveToOracleException( errorMessage );
        }
    }

    private static void saveTelsalePolicyDataToInformix( Connection conn, ArrayList<TelsalePolicyRecord> policyList )
            throws ClassNotFoundException,
                   SQLException,
                   TelsalePolicyDataSaveToInformixException
    {
        if ( policyList == null )
        {
            throw new NullPointerException( "policyList参数为null！" );
        }

        //Connection        conn      = null;
        PreparedStatement statement = null;

        int    errorCount   = 0;
        String errorMessage = "写入数据错误，请根据提示修正后重新上传！";
        String insertSQL =
                "INSERT INTO dxbd_test \n" +
                        "    (bdh,\n" +
                        "     zhjywy,\n" +
                        "     zhjywymc,\n" +
                        "     kshdm,\n" +
                        "     kshmc,\n" +
                        "     zhjywybm,\n" +
                        "     zhjywybmm,\n" +
                        "     khjl,\n" +
                        "     khjllx,\n" +
                        "     jjbj,\n" +
                        "     czydm,\n" +
                        "     czrq)\n" +
                        "VALUES\n" +
                        "    (?,\n" +
                        "     ?,\n" +
                        "     ?,\n" +
                        "     ?,\n" +
                        "     ?,\n" +
                        "     ?,\n" +
                        "     ?,\n" +
                        "     0,\n" +
                        "     0,\n" +
                        "     1,\n" +
                        "     '800',\n" +
                        "     today)";


        statement = conn.prepareStatement( insertSQL );

        for ( TelsalePolicyRecord recordIterator : policyList )
        {
            statement.setString( 1, recordIterator.getPolicyNo() );
            statement.setString( 2, recordIterator.getOperatorCode() );
            statement.setString( 3, recordIterator.getOperatorName() );
            statement.setString( 4, recordIterator.getOperatorSectionOfficeCode() );
            statement.setString( 5, recordIterator.getOperatorSectionOfficeName() );
            statement.setString( 6, recordIterator.getOperatorDepartmentCode() );
            statement.setString( 7, recordIterator.getOperatorDepartmentName() );

            try
            {
                statement.execute();
            }
            catch ( SQLException error )
            {
                //捕捉写入错误
                errorMessage += "保单号" +
                        recordIterator.getPolicyNo() +
                        "保存失败，错误提示：" +
                        error.getMessage() + "<br>\n";

                errorCount++;
            }
        }

        //检查错误数量，如果有错误就抛出异常
        if ( errorCount > 0 )
        {
            throw new TelsalePolicyDataSaveToInformixException( errorMessage );
        }
    }

    /**
     * @Description: 读取excel文件，将数据写入oracle和informix数据库。
     * 数据库连接关闭自动提交，在两个数据库都写入成功后提交。
     * 只要有一个数据库写入抛出异常，回滚两个数据库的事务。
     * @Param:
     * @return: void
     * @Author: 王炜
     * @Date: 2021/6/23
     */
    public static void saveTelsalePolicyData( String filePath,
                                              String oracleURL,
                                              String oracleUserName,
                                              String oraclePassword,
                                              String informixURL )
            throws ClassNotFoundException,
                   IOException,
                   XlsFileFormatException,
                   SQLException,
                   TelsalePolicyDataSaveToDatabaseException
    {

        //读取文件
        ArrayList<TelsalePolicyRecord> policyList = null;

        policyList = readTelsalePolicyDataFromXlsx( filePath, 0 );

        Class.forName( "com.informix.jdbc.IfxDriver" );
        Class.forName( "oracle.jdbc.driver.OracleDriver" );

        Connection oracleConnection   = null;
        Connection informixConnection = null;

        //打开连接
        oracleConnection   = DriverManager.getConnection( oracleURL, oracleUserName, oraclePassword );
        informixConnection = DriverManager.getConnection( informixURL );

        try
        {

            oracleConnection.setAutoCommit( false );
            informixConnection.setAutoCommit( false );

            saveTelsalePolicyDataToOracle( oracleConnection, policyList );
            saveTelsalePolicyDataToInformix( informixConnection, policyList );

            oracleConnection.commit();
            informixConnection.commit();
        }
        catch ( Exception error )
        {
            //出现任何错误，回滚两个数据库，再抛出异常
            oracleConnection.rollback();
            informixConnection.rollback();

            throw new TelsalePolicyDataSaveToDatabaseException( error.getMessage() );
        }
        finally
        {
            //无论如何要关闭连接
            try
            {
                oracleConnection.close();
                informixConnection.close();
            }
            catch ( SQLException error )
            {
            }
        }
    }
}


