/*
   * (non-Javadoc)
   *
   * @see
   * com.toolsverse.etl.connector.DataSetConnector#persist(com.toolsverse.
   * etl.connector.DataSetConnectorParams, com.toolsverse.etl.common.DataSet,
   * com.toolsverse.etl.driver.Driver)
   */
  public ConnectorResult persist(ExcelConnectorParams params, DataSet dataSet, Driver driver)
      throws Exception {
    if (dataSet == null
        || params == null
        || (driver == null && dataSet.getDriver() == null)
        || Utils.isNothing(dataSet.getName())
        || dataSet.getFieldCount() == 0
        || (params.isFileNameRequired() && Utils.isNothing(params.getFileName()))) {
      ConnectorResult result = new ConnectorResult();
      result.setRetCode(ConnectorResult.VALIDATION_FAILED_CODE);

      if (dataSet == null)
        result.addResult(ConnectorResource.VALIDATION_ERROR_DATA_SET_NULL.getValue());
      if (driver == null && dataSet.getDriver() == null)
        result.addResult(ConnectorResource.VALIDATION_ERROR_DRIVER_NULL.getValue());
      if (params == null)
        result.addResult(ConnectorResource.VALIDATION_ERROR_PARAMS_NULL.getValue());
      if (dataSet != null && dataSet.getFieldCount() == 0)
        result.addResult(ConnectorResource.VALIDATION_ERROR_DATA_SET_NO_FIELDS.getValue());
      if (dataSet != null && Utils.isNothing(dataSet.getName()))
        result.addResult(ConnectorResource.VALIDATION_ERROR_DATA_SET_NO_NAME.getValue());
      if (params.isFileNameRequired() && Utils.isNothing(params.getFileName()))
        result.addResult(FileConnectorResource.VALIDATION_ERROR_FILE_NAME_NOT_SPECIFIED.getValue());

      return result;
    }

    if (!params.isSilent())
      Logger.log(
          Logger.INFO,
          EtlLogger.class,
          EtlResource.PERSISTING_DATASET_MSG.getValue() + dataSet.getName() + "...");

    try {
      prePersist(params, dataSet, driver);

      int records = dataSet.getRecordCount();

      // data
      for (int row = 0; row < records; row++) {
        DataSetRecord record = dataSet.getRecord(row);

        inlinePersist(params, dataSet, driver, record, row, records);
      }

      postPersist(params, dataSet, driver);

      ConnectorResult connectorResult = new ConnectorResult();

      connectorResult.addResult(
          Utils.format(
              FileConnectorResource.FILE_PERSISTED.getValue(),
              new String[] {FilenameUtils.getName(params.getRealFileName())}));

      return connectorResult;
    } finally {
      cleanUp(params, dataSet, driver);
    }
  }
    /**
     * Instantiates a new XlsProcessor.
     *
     * @param params the parameters
     * @param dataSet the data set
     * @param driver the driver
     */
    public XlsProcessor(ExcelConnectorParams params, DataSet dataSet, Driver driver) {
      super();

      _params = params;
      _dataSet = dataSet;
      _driver = driver;

      _types = new HashMap<Integer, Boolean>();

      _lastRowNumber = -1;
      _dataSetRecord = null;

      _index = 1;

      _sheetNames = new ArrayList<String>();

      _currentSheetIndex = -1;

      _hasKey = !Utils.isNothing(dataSet.getKeyFields());

      _sheetName =
          (!Utils.isNothing(dataSet.getOwnerName()) && !Utils.isNothing(dataSet.getObjectName())
                  || Utils.isNothing(_params.getSheetName()))
              ? dataSet.getName()
              : params.getSheetName();

      _sheetFound = false;
    }
  /*
   * (non-Javadoc)
   *
   * @see
   * com.toolsverse.etl.connector.DataSetConnector#prePersist(com.toolsverse
   * .etl.connector.DataSetConnectorParams, com.toolsverse.etl.common.DataSet,
   * com.toolsverse.etl.driver.Driver)
   */
  @SuppressWarnings("resource")
  public void prePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver)
      throws Exception {
    String fileName = null;

    OutputStream out = null;

    if (params.getOutputStream() == null) {
      fileName =
          SystemConfig.instance()
              .getPathUsingAppFolders(
                  params.getFileName(
                      dataSet.getOwnerName() != null ? dataSet.getOwnerName() : dataSet.getName(),
                      ".xls",
                      true));

      params.setRealFileName(fileName);

      out = new FileOutputStream(fileName);

      if (params.getTransactionMonitor() != null) params.getTransactionMonitor().addFile(fileName);
    } else out = params.getOutputStream();

    params.setOut(out);

    Workbook workbook = new HSSFWorkbook();

    params.setWorkbook(workbook);

    Sheet sheet =
        workbook.createSheet(
            Utils.isNothing(params.getSheetName()) ? dataSet.getName() : params.getSheetName());

    params.setSheet(sheet);

    Font labelFont = workbook.createFont();
    labelFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    CellStyle labelCellStyle = workbook.createCellStyle();
    labelCellStyle.setFont(labelFont);

    DataFormat dateTimeFormat = workbook.createDataFormat();
    CellStyle dateTimeCellStyle = workbook.createCellStyle();
    dateTimeCellStyle.setDataFormat(dateTimeFormat.getFormat(params.getDateTimeFormat()));

    params.setDateTimeCellStyle(dateTimeCellStyle);

    DataFormat dateFormat = workbook.createDataFormat();
    CellStyle dateCellStyle = workbook.createCellStyle();
    dateCellStyle.setDataFormat(dateFormat.getFormat(params.getDateFormat()));

    params.setDateCellStyle(dateCellStyle);

    DataFormat timeFormat = workbook.createDataFormat();
    CellStyle timeCellStyle = workbook.createCellStyle();
    timeCellStyle.setDataFormat(timeFormat.getFormat(params.getTimeFormat()));

    params.setTimeCellStyle(timeCellStyle);

    // column names
    Row excelRow = sheet.createRow(0);

    // metadata
    int col = 0;
    for (FieldDef fieldDef : dataSet.getFields().getList()) {
      if (!fieldDef.isVisible()) continue;

      Cell labelCell = excelRow.createCell(col++, Cell.CELL_TYPE_STRING);
      labelCell.setCellStyle(labelCellStyle);
      labelCell.setCellValue(fieldDef.getName());
    }

    params.setPrePersistOccured(true);
  }
  /*
   * (non-Javadoc)
   *
   * @see
   * com.toolsverse.etl.connector.DataSetConnector#populate(com.toolsverse
   * .etl.connector.DataSetConnectorParams, com.toolsverse.etl.common.DataSet,
   * com.toolsverse.etl.driver.Driver)
   */
  public ConnectorResult populate(ExcelConnectorParams params, DataSet dataSet, Driver driver)
      throws Exception {
    if (dataSet == null
        || params == null
        || Utils.isNothing(dataSet.getName())
        || (driver == null && dataSet.getDriver() == null)) {
      ConnectorResult result = new ConnectorResult();
      result.setRetCode(ConnectorResult.VALIDATION_FAILED_CODE);

      if (dataSet == null)
        result.addResult(ConnectorResource.VALIDATION_ERROR_DATA_SET_NULL.getValue());
      if (driver == null && dataSet.getDriver() == null)
        result.addResult(ConnectorResource.VALIDATION_ERROR_DRIVER_NULL.getValue());
      if (params == null)
        result.addResult(ConnectorResource.VALIDATION_ERROR_PARAMS_NULL.getValue());
      if (dataSet != null && Utils.isNothing(dataSet.getName()))
        result.addResult(ConnectorResource.VALIDATION_ERROR_DATA_SET_NO_NAME.getValue());

      return result;
    }

    dataSet.clear();

    driver = driver != null ? driver : dataSet.getDriver();

    if (!params.isSilent())
      Logger.log(
          Logger.INFO,
          EtlLogger.class,
          EtlResource.LOADING_DATASET_MSG.getValue() + dataSet.getName() + "...");

    FileInputStream fin = null;
    POIFSFileSystem poifs = null;

    try {
      String fileName = null;

      if (params.getInputStream() == null) {
        fileName =
            SystemConfig.instance()
                .getPathUsingAppFolders(
                    params.getFileName(
                        dataSet.getOwnerName() != null ? dataSet.getOwnerName() : dataSet.getName(),
                        ".xls",
                        true));

        fin = new FileInputStream(fileName);

        poifs = new POIFSFileSystem(fin);
      } else poifs = new POIFSFileSystem(params.getInputStream());

      XlsProcessor xlsProcessor = new XlsProcessor(params, dataSet, driver);

      MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(xlsProcessor);
      FormatTrackingHSSFListener formatListener = new FormatTrackingHSSFListener(listener);

      xlsProcessor.setFormatListener(formatListener);

      HSSFRequest request = new HSSFRequest();
      request.addListenerForAllRecords(formatListener);

      HSSFEventFactory factory = new HSSFEventFactory();

      try {
        factory.processWorkbookEvents(request, poifs);
      } catch (Exception ex) {
        if (!params.isMaxRowsExceededException(ex) && !params.isSheetAlreadyExatractedException(ex))
          throw ex;
      }

      if (dataSet.getFieldCount() > 0
          && dataSet.getRecordCount() == 0
          && params.getAddRecordCallback() != null) {
        params.getAddRecordCallback().onAddRecord(dataSet, driver, null, 0);
      }

      ConnectorResult connectorResult = new ConnectorResult();

      connectorResult.addResult(
          Utils.format(
              FileConnectorResource.FILE_POPULATED.getValue(),
              new String[] {FilenameUtils.getName(fileName)}));

      return connectorResult;

    } finally {
      if (fin != null) fin.close();

      if (params.getInputStream() != null && params.isCloseInput()) params.getInputStream().close();

      if (params.getAfterCallback() != null) params.getAfterCallback().onAfter(dataSet, driver);
    }
  }
  /*
   * (non-Javadoc)
   *
   * @see
   * com.toolsverse.etl.connector.DataSetConnector#inlinePersist(com.toolsverse
   * .etl.connector.DataSetConnectorParams, com.toolsverse.etl.common.DataSet,
   * com.toolsverse.etl.driver.Driver,
   * com.toolsverse.etl.common.DataSetRecord, int, int)
   */
  public void inlinePersist(
      ExcelConnectorParams params,
      DataSet dataSet,
      Driver driver,
      DataSetRecord record,
      int row,
      int records)
      throws Exception {
    if (record == null) return;

    int currentRow = params.getCurrentRow();

    Row excelRow = params.getSheet().createRow(currentRow);

    params.setCurrentRow(++currentRow);

    int colCount = dataSet.getFieldCount();

    for (int col = 0; col < colCount; col++) {
      FieldDef fieldDef = dataSet.getFields().get(col);

      if (!fieldDef.isVisible()) continue;

      Object fieldValue = record.get(col);
      int fType = fieldDef.getSqlDataType();
      String value = null;
      Cell dataCell;

      if (fieldValue != null) {
        value = dataSet.encode(fieldDef, fieldValue, driver, params.getParams(), false);
      }

      if (SqlUtils.isNumber(fType)) {
        dataCell = excelRow.createCell(col, Cell.CELL_TYPE_NUMERIC);

        dataCell.setCellValue(value);
      } else if (SqlUtils.isDateOnly(fType)) {
        dataCell = excelRow.createCell(col, Cell.CELL_TYPE_NUMERIC);

        dataCell.setCellStyle(params.getDateCellStyle());

        if (fieldValue instanceof java.util.Date)
          dataCell.setCellValue((java.util.Date) fieldValue);
        else dataCell.setCellValue(value);

      } else if (SqlUtils.isTime(fType)) {
        dataCell = excelRow.createCell(col, Cell.CELL_TYPE_NUMERIC);

        dataCell.setCellStyle(params.getTimeCellStyle());

        if (fieldValue instanceof java.util.Date)
          dataCell.setCellValue((java.util.Date) fieldValue);
        else dataCell.setCellValue(value);

      } else if (SqlUtils.isTimestamp(fType)) {
        dataCell = excelRow.createCell(col, Cell.CELL_TYPE_NUMERIC);

        dataCell.setCellStyle(params.getDateTimeCellStyle());

        if (fieldValue instanceof java.util.Date)
          dataCell.setCellValue((java.util.Date) fieldValue);
        else dataCell.setCellValue(value);

      } else if (SqlUtils.isBoolean(fType)) {
        dataCell = excelRow.createCell(col, Cell.CELL_TYPE_BOOLEAN);

        if (fieldValue instanceof Boolean) dataCell.setCellValue((Boolean) fieldValue);
        else dataCell.setCellValue(value);

      } else {
        dataCell = excelRow.createCell(col, Cell.CELL_TYPE_STRING);
        dataCell.setCellValue(value);
      }
    }

    if (row >= 0
        && records >= 0
        && !params.isSilent()
        && params.getLogStep() > 0
        && (row % params.getLogStep()) == 0)
      Logger.log(
          Logger.INFO,
          EtlLogger.class,
          dataSet.getName()
              + ": "
              + EtlResource.PERSITING_RECORD.getValue()
              + row
              + " out of "
              + records);
  }
    /*
     * (non-Javadoc)
     *
     * @see
     * org.apache.poi.hssf.eventusermodel.HSSFListener#processRecord(org
     * .apache.poi.hssf.record.Record)
     */
    public void processRecord(Record record) {
      int row = -1;
      int column = -1;
      int fType = Types.VARCHAR;
      Object cellValue = null;
      boolean isNewValue = false;
      TypedKeyValue<Integer, Number> typeAndValue;

      if (BOFRecord.sid == record.getSid()) {
        BOFRecord bof = (BOFRecord) record;

        if (bof.getType() == BOFRecord.TYPE_WORKSHEET) {
          _currentSheetIndex++;
        }
      }

      boolean isFound =
          _currentSheetIndex >= 0 && _currentSheetIndex == _sheetNames.indexOf(_sheetName);

      _sheetFound = _sheetFound || isFound;

      if (_currentSheetIndex >= 0 && !isFound) {
        if (!_sheetFound) return;

        throw new RuntimeException(ExcelConnectorParams.SHEET_ALREADY_EXTRACTED_EXCEPTION);
      }

      switch (record.getSid()) {
        case BoundSheetRecord.sid:
          BoundSheetRecord bsr = (BoundSheetRecord) record;

          _sheetNames.add(bsr.getSheetname());

          break;
        case SSTRecord.sid:
          _sstRecord = (SSTRecord) record;
          break;
        case BlankRecord.sid:
          BlankRecord brec = (BlankRecord) record;

          row = brec.getRow();
          column = brec.getColumn();
          cellValue = null;
          fType = Types.VARCHAR;
          isNewValue = true;

          break;
        case BoolErrRecord.sid:
          BoolErrRecord berec = (BoolErrRecord) record;

          row = berec.getRow();
          column = berec.getColumn();
          cellValue = berec.getBooleanValue();
          isNewValue = true;

          fType = Types.BOOLEAN;

          break;
        case FormulaRecord.sid:
          FormulaRecord frec = (FormulaRecord) record;

          row = frec.getRow();
          column = frec.getColumn();

          if (Double.isNaN(frec.getValue())) {
            // Formula result is a string
            // This is stored in the next record
            _outputNextStringRecord = true;
            _nextRow = frec.getRow();
            _nextColumn = frec.getColumn();
          } else {
            cellValue = Utils.str2Number(_formatListener.formatNumberDateCell(frec), null);

            fType = Types.NUMERIC;
            isNewValue = true;
          }
          break;
        case StringRecord.sid:
          if (_outputNextStringRecord) {
            // String for formula
            StringRecord srec = (StringRecord) record;
            cellValue = srec.getString();
            row = _nextRow;
            column = _nextColumn;
            _outputNextStringRecord = false;
            fType = Types.VARCHAR;
            isNewValue = true;
          }
          break;
        case LabelRecord.sid:
          LabelRecord lrec = (LabelRecord) record;

          row = lrec.getRow();
          column = lrec.getColumn();
          cellValue = lrec.getValue();
          fType = Types.VARCHAR;
          isNewValue = true;

          break;
        case LabelSSTRecord.sid:
          LabelSSTRecord lsrec = (LabelSSTRecord) record;

          if (_sstRecord == null) break;

          row = lsrec.getRow();
          column = lsrec.getColumn();

          fType = Types.VARCHAR;

          cellValue = _sstRecord.getString(lsrec.getSSTIndex()).toString();

          typeAndValue = SqlUtils.getNumberTypeAndValue((String) cellValue);

          if (typeAndValue != null) {
            fType = typeAndValue.getKey();
            cellValue = typeAndValue.getValue();
          }

          isNewValue = true;

          break;
        case NoteRecord.sid:
          break;
        case NumberRecord.sid:
          NumberRecord numrec = (NumberRecord) record;

          row = numrec.getRow();
          column = numrec.getColumn();

          int fIndex = numrec.getXFIndex();

          String formatString = _formatListener.getFormatString(numrec);

          if (_params.isDateTimeFormat(formatString)) {
            cellValue =
                Utils.str2Date(
                    Utils.date2Str(
                        DateUtil.getJavaDate(numrec.getValue()), _params.getDateTimeFormat()),
                    null,
                    _params.getDateTimeFormat());

            fType = Types.TIMESTAMP;
          } else if (_params.isDateFormat(formatString)) {
            cellValue =
                Utils.str2Date(
                    Utils.date2Str(
                        DateUtil.getJavaDate(numrec.getValue()), _params.getDateFormat()),
                    null,
                    _params.getDateFormat());

            fType = Types.DATE;
          } else if (_params.isTimeFormat(formatString)) {
            cellValue =
                Utils.str2Date(
                    Utils.date2Str(
                        DateUtil.getJavaDate(numrec.getValue()), _params.getTimeFormat()),
                    null,
                    _params.getTimeFormat());

            fType = Types.TIME;
          } else if (DateUtil.isADateFormat(fIndex, formatString)) {
            cellValue = DateUtil.getJavaDate(numrec.getValue());

            if (cellValue instanceof Date
                && (Utils.getDate((Date) cellValue, Calendar.YEAR, null) != 1900))
              fType = Types.TIMESTAMP;
            else {
              typeAndValue =
                  SqlUtils.getNumberTypeAndValue(_formatListener.formatNumberDateCell(numrec));

              if (typeAndValue == null) {
                fType = Types.NUMERIC;
                cellValue = null;
              } else {
                fType = typeAndValue.getKey();
                cellValue = typeAndValue.getValue();
              }
            }
          } else {
            typeAndValue =
                SqlUtils.getNumberTypeAndValue(_formatListener.formatNumberDateCell(numrec));

            if (typeAndValue == null) {
              fType = Types.NUMERIC;
              cellValue = null;
            } else {
              fType = typeAndValue.getKey();
              cellValue = typeAndValue.getValue();
            }
          }

          isNewValue = true;

          break;
        case RKRecord.sid:
          break;
        default:
          break;
      }

      // Handle new row
      if (row > 0 && row != _lastRowNumber) {
        try {
          if (row == 1 && _params.getBeforeCallback() != null)
            _params.getBeforeCallback().onBefore(_dataSet, _driver);
        } catch (Exception ex) {
          new RuntimeException(ex);
        }

        _dataSetRecord = new DataSetRecord();

        if (!_params.isSilent() && _params.getLogStep() > 0 && (_index % _params.getLogStep()) == 0)
          Logger.log(
              Logger.INFO,
              EtlLogger.class,
              _dataSet.getName() + ": " + _index + EtlResource.READING_DATASET_MSG.getValue());
        _index++;
      }

      // Handle missing column
      if (record instanceof MissingCellDummyRecord) {
        MissingCellDummyRecord mc = (MissingCellDummyRecord) record;
        row = mc.getRow();
        column = mc.getColumn();
        cellValue = "";
        fType = Types.VARCHAR;
        isNewValue = true;
      }

      // If we got something to add, do so
      if (isNewValue && row >= 0 && column >= 0) {
        FieldDef fieldDef = null;

        // fields defs
        if (row == 0) {
          fieldDef = new FieldDef();
          fieldDef.setName(cellValue != null ? cellValue.toString() : "field" + column);

          _dataSet.addField(fieldDef);
        } else if (_dataSet.getFieldCount() > column) {
          fieldDef = _dataSet.getFieldDef(column);

          if (fieldDef != null) {
            if (!Utils.isEmpty(cellValue)) {
              int type = fieldDef.getSqlDataType();

              fType = SqlUtils.getFieldType(fType, type, _types.containsKey(column));

              fieldDef.setSqlDataType(fType);
              fieldDef.setNativeDataType(
                  _driver.getType(new FieldDef(fType, "VARCHAR"), null, null));

              _types.put(column, true);
            } else cellValue = null;

            if (_dataSetRecord != null) {
              try {
                if (_params.getAddFieldValueCallback() != null)
                  _params
                      .getAddFieldValueCallback()
                      .onAddFieldValue(_dataSet, _driver, _dataSetRecord, fieldDef);
              } catch (Exception ex) {
                new RuntimeException(ex);
              }

              addValue(cellValue, _dataSetRecord, _dataSet);
            }
          }
        }
      }

      // Update column and row count
      if (row > 0) _lastRowNumber = row;

      // Handle end of row
      if (record instanceof LastCellOfRowDummyRecord) {
        // We're onto a new row
        if (_dataSetRecord != null) {
          if (_params.getMaxRows() >= 0 && _dataSet.getRecordCount() >= _params.getMaxRows()) {
            throw new RuntimeException(DataSetConnectorParams.MAX_ROWS_EXCEEDED_EXCEPTION);
          }

          boolean added = _dataSet.addRecord(_dataSetRecord);

          try {
            if (added && _params.getAddRecordCallback() != null)
              _params
                  .getAddRecordCallback()
                  .onAddRecord(_dataSet, _driver, _dataSetRecord, _index - 1);
          } catch (Exception ex) {
            new RuntimeException(ex);
          }
        }
      }
    }