/*
   * (non-Javadoc)
   *
   * @see com.toolsverse.etl.metadata.Metadata#getTopLevelDbObjects()
   */
  public List<Object> getTopLevelDbObjects() throws Exception {
    if (getConnectionParamsProvider() == null || getDriver() == null) return null;

    String path = getConnectionParamsProvider().getConnectionParams().getUrl();

    if (Utils.isNothing(path)) return null;

    String folder = FilenameUtils.getFullPath(path);

    if (Utils.isNothing(folder)) folder = SystemConfig.instance().getDataFolderName();

    String fName = FilenameUtils.getName(path);

    if (Utils.isNothing(fName)) return null;

    File[] files = FileUtils.getFilesInFolder(folder, fName);

    if (files == null || files.length == 0) return null;

    setHasCatalogs(true);
    setHasSchemas(false);

    List<Object> list = new ArrayList<Object>();

    for (File file : files) {
      list.add(new KeyValue(file.getAbsolutePath(), null));
    }

    setCurrentDatabase(files[0].getAbsolutePath());

    return list;
  }
  /*
   * (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);
    }
  }