/** Test of deleteByDateAndCode method, of class ChecksumHistoryDAOImpl. */
  @Test
  public void testDeleteByDateAndCode() throws Exception {
    System.out.println("deleteByDateAndCode");

    GregorianCalendar cal = new GregorianCalendar();
    Date retentionDate = cal.getTime();
    ChecksumResultCode resultCode = ChecksumResultCode.CHECKSUM_MATCH;

    // Create two older rows
    HibernateDBConnection dbc = (HibernateDBConnection) CoreHelpers.getDBConnection(context);
    Query qry =
        dbc.getSession()
            .createSQLQuery(
                "INSERT INTO checksum_history"
                    + "(check_id, process_end_date, result, bitstream_id)"
                    + " VALUES (:id, :date, :result, :bitstream)");
    int checkId = 0;

    // Row with matching result code
    BitstreamService bss = ContentServiceFactory.getInstance().getBitstreamService();
    InputStream is = new ByteArrayInputStream(new byte[0]);
    Bitstream bs = bss.create(context, is);
    context.turnOffAuthorisationSystem();
    bss.update(context, bs);
    context.restoreAuthSystemState();

    cal.roll(Calendar.DATE, -1);
    Date matchDate = cal.getTime();
    checkId++;
    qry.setInteger("id", checkId);
    qry.setDate("date", matchDate);
    qry.setString("result", ChecksumResultCode.CHECKSUM_MATCH.name());
    qry.setString("bitstream", bs.getID().toString()); // FIXME identifier not being set???
    qry.executeUpdate();

    // Row with nonmatching result code
    cal.roll(Calendar.DATE, -1);
    Date noMatchDate = cal.getTime();
    checkId++;
    qry.setInteger("id", checkId);
    qry.setDate("date", noMatchDate);
    qry.setString("result", ChecksumResultCode.CHECKSUM_NO_MATCH.name());
    qry.setString("bitstream", bs.getID().toString());
    qry.executeUpdate();

    // Create one newer row
    cal.roll(Calendar.DATE, +3);
    Date futureDate = cal.getTime();
    checkId++;
    qry.setInteger("id", checkId);
    qry.setDate("date", new java.sql.Date(futureDate.getTime()));
    qry.setString("result", ChecksumResultCode.CHECKSUM_MATCH.name());
    qry.setString("bitstream", bs.getID().toString());
    qry.executeUpdate();

    // Test!
    ChecksumHistoryDAOImpl instance = new ChecksumHistoryDAOImpl();
    int expResult = 1;
    int result = instance.deleteByDateAndCode(context, retentionDate, resultCode);
    assertEquals(expResult, result);

    // See if matching old row is gone.
    qry =
        dbc.getSession()
            .createQuery("SELECT COUNT(*) FROM ChecksumHistory WHERE process_end_date = :date");
    long count;

    qry.setDate("date", matchDate);
    count = (Long) qry.uniqueResult();
    assertEquals("Should find no row at matchDate", count, 0);

    // See if nonmatching old row is still present.
    qry.setDate("date", noMatchDate);
    count = (Long) qry.uniqueResult();
    assertEquals("Should find one row at noMatchDate", count, 1);

    // See if new row is still present.
    qry.setDate("date", futureDate);
    count = (Long) qry.uniqueResult();
    assertEquals("Should find one row at futureDate", count, 1);
  }
  /**
   * <b>sqlCommandProcessing</b><br>
   * Create the sql command beginning with the sql command specified in the web.xml file and
   * applying all the options <br>
   * specified by the user. This command also apply a limit in the results to avoid to overload the
   * server
   */
  private void sqlCommandProcessing(
      HttpSession session,
      int optionNumberInt,
      String page,
      String numberResultsByPage,
      String databaseType) {
    int parameterNumber = 1;
    reInitSqlCommand(session);
    String currentSqlCommand = (String) session.getAttribute("currentSqlCommand");
    Vector currentSqlParams = new Vector();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    if (logger.isDebugEnabled()) {
      logger.debug("SQL-BASE: " + currentSqlCommand);
    }

    // If search criteria was entered, build the WHERE clause to
    // include the filter data entered
    if (map != null && optionNumberInt > 0) {
      String commandTemp = currentSqlCommand;
      if (currentSqlCommand.toLowerCase().indexOf("where") > -1) {
        commandTemp += " AND ";
      } else {
        commandTemp += " WHERE ";
      }

      while (parameterNumber <= optionNumberInt) {
        if (map.containsKey("option" + parameterNumber)) {
          if (map.containsKey("and-or" + parameterNumber)) {
            commandTemp += " " + map.get("and-or" + parameterNumber) + " ";
          }

          String parameterName = map.get("option" + parameterNumber).toString();

          if (parameterName.equals("ip")) {
            commandTemp += " main.ip LIKE ?";
            currentSqlParams.add(
                TableModel.setSqlParam(
                    TableModel.STRING, "%" + map.get("value" + parameterNumber).toString() + "%"));

          } else if (parameterName.equals("pass")) {
            commandTemp += " main.pass = ?";
            currentSqlParams.add(
                TableModel.setSqlParam(
                    TableModel.BOOLEAN, map.get("value" + parameterNumber).toString()));

          } else if (parameterName.equals("test")) {
            commandTemp += "  main.test = ?";
            currentSqlParams.add(
                TableModel.setSqlParam(
                    TableModel.STRING, map.get("value" + parameterNumber).toString()));

          } else if (parameterName.equals("company")) {
            commandTemp += "  ip.company_name = ?";
            currentSqlParams.add(
                TableModel.setSqlParam(
                    TableModel.STRING, map.get("value" + parameterNumber).toString()));

          } else if (parameterName.equals("date1")) {
            String date1 = map.get("value" + parameterNumber).toString().trim();
            java.util.Date d1;
            GregorianCalendar endday = new GregorianCalendar();
            try {
              d1 = sdf.parse(date1);
              if (map.containsKey("option" + (parameterNumber + 1))
                  && map.get("option" + (parameterNumber + 1)).toString().equals("date2")) {
                if (map.containsKey("value" + (parameterNumber + 1))) {
                  String date2 = map.get("value" + (parameterNumber + 1)).toString().trim();
                  java.util.Date d2 = sdf.parse(date2);
                  if (d2.getTime() > d1.getTime()) {
                    endday.setTimeInMillis(d2.getTime());
                    endday.roll(Calendar.DAY_OF_MONTH, +1);
                    d2 = endday.getTime();
                    commandTemp += " timereceived >= ? and timereceived < ?";
                  } else if (d2.getTime() < d1.getTime()) {
                    endday.setTimeInMillis(d1.getTime());
                    endday.roll(Calendar.DAY_OF_MONTH, +1);
                    d1 = endday.getTime();
                    commandTemp += " timereceived < ? and timereceived >= ?";
                  } else {
                    // From Date same as To Date
                    endday.setTimeInMillis(d2.getTime());
                    endday.roll(Calendar.DAY_OF_MONTH, +1);
                    d2 = endday.getTime();
                    commandTemp += " timereceived >= ? and timereceived < ?";
                  }
                  currentSqlParams.add(TableModel.setSqlParam(TableModel.DATE, d1));
                  currentSqlParams.add(TableModel.setSqlParam(TableModel.DATE, d2));

                  map.remove("value" + (parameterNumber + 1));
                  map.remove("option" + (parameterNumber + 1));
                  if (map.containsKey("and-or" + (parameterNumber + 1))) {
                    map.remove("and-or" + (parameterNumber + 1));
                  }
                }
              } else {
                // To Date not provided
                commandTemp += " timereceived >= ? ";
                currentSqlParams.add(TableModel.setSqlParam(TableModel.DATE, d1));
              }
            } catch (ParseException e) {
              logger.error("Date Parsing Error: " + e);
            }
          } else if (parameterName.equals("date2")) {
            // From Date not provided
            String date1 = map.get("value" + parameterNumber).toString();
            java.util.Date d1 = new java.util.Date();
            try {
              d1 = sdf.parse(date1);
            } catch (ParseException ex) {
              logger.error("Date Parsing Error: " + ex);
            }
            GregorianCalendar endday = new GregorianCalendar();
            endday.setTime(d1);
            endday.roll(Calendar.DAY_OF_MONTH, +1);
            commandTemp += " timereceived < ?";
            currentSqlParams.add(TableModel.setSqlParam(TableModel.DATE, endday.getTime()));

          } else if (parameterName.equals("date")) {
            String value = map.get("value" + parameterNumber).toString();
            GregorianCalendar today = new GregorianCalendar();
            GregorianCalendar endday = new GregorianCalendar();

            if (value.equals("today")) {
              endday.roll(Calendar.DAY_OF_MONTH, +1);
            } else if (value.equals("yesterday")) {
              today.roll(Calendar.DAY_OF_MONTH, -1);
            } else if (value.equals("2days")) {
              today.roll(Calendar.DAY_OF_MONTH, -2);
              endday.roll(Calendar.DAY_OF_MONTH, -1);
            } else if (value.equals("3days")) {
              today.roll(Calendar.DAY_OF_MONTH, -3);
              endday.roll(Calendar.DAY_OF_MONTH, -2);
            }
            commandTemp += " timereceived >= ? and timereceived < ?";
            currentSqlParams.add(TableModel.setSqlParam(TableModel.DATE, today.getTime()));
            currentSqlParams.add(TableModel.setSqlParam(TableModel.DATE, endday.getTime()));
          }
        }
        parameterNumber++;
      }
      if (logger.isDebugEnabled()) {
        logger.debug("SQL-WITH-FILTERS: " + commandTemp);
      }
      currentSqlCommand = commandTemp;
    }

    // Save SQL for count(*) command
    session.setAttribute("countSqlCommand", currentSqlCommand);
    session.setAttribute("countSqlParams", currentSqlParams);

    // Add Paging logic to the SQL Statement
    if (logger.isDebugEnabled()) {
      logger.debug("GetTableServlet: SQLRequest before paging: >" + currentSqlCommand);
    }

    int nbResByPage = MAX_RESULTS_BY_PAGE;
    if (numberResultsByPage != null) {
      nbResByPage = new Integer(numberResultsByPage).intValue();
    }
    int pageNumber = 0;
    if (page != null) {
      pageNumber = new Integer(page).intValue();
    }

    // Add database specific paging logic to the SQL command
    HashMap sqlMap =
        TableModel.getSQLWithPaging(
            databaseType, currentSqlCommand, currentSqlParams, pageNumber, nbResByPage);
    StringBuffer completeSQL = (StringBuffer) sqlMap.get("completeSQL");
    Vector<HashMap> completeSqlParams = (Vector<HashMap>) sqlMap.get("completeSqlParams");

    // Save the SQL Command
    session.setAttribute("currentSqlCommand", completeSQL.toString());
    session.setAttribute("currentSqlParams", completeSqlParams);
    if (logger.isInfoEnabled()) {
      logger.debug("LogBrowser Page: " + page);
      logger.info("LogBrowser SQL (with paging): " + completeSQL);
    }
  }
  /**
   * Método que obtiene los ultimos 5 movimientos de una cuenta.
   *
   * @param cliente ClienteBean.
   * @param datosMovimientos Datos para la consulta.
   * @param bitacoraBean Objeto para bitacorizar.
   * @param tipoProducto Tipo de producto a consultar (CC, TC, FI, IV)
   * @throws BusinessException Excepción lanzada si no se puede ejecutar el servicio.
   */
  public Object getUltMvtos(ClienteBean cliente, DatosUltMvtos datos, BitacoraBean bitacoraBean)
      throws BusinessException, ParseException {

    log.debug("*****************ENTRO A CONSULTA ULTIMOS 5 MOVIMIENTOS************");
    if (cliente == null) {
      throw new BusinessException("MOVIMIENTOS");
    }

    String tipoConsulta = datos.getTipoConsulta();
    boolean restaranio = false;

    // Obtencion de fechas inicial y final
    String sdiaFrom = "", smesFrom = "", sanioFrom = "";
    String sdiaTo = "", smesTo = "", sanioTo = "";

    GregorianCalendar gc = new GregorianCalendar();
    Date trialTime = new Date();
    gc.setTime(trialTime);

    // se obtiene fecha inicial
    int idiaTo = gc.get(Calendar.DAY_OF_MONTH);
    int imesTo = gc.get(Calendar.MONTH);
    int ianioTo = gc.get(Calendar.YEAR);

    sdiaTo = Integer.toString(idiaTo);
    sanioTo = Integer.toString(ianioTo);
    smesTo = getMesFromNumber(imesTo);

    if (imesTo <= 2) {
      restaranio = true;
    }

    // Se reduce la fecha tres meses
    gc.roll(Calendar.MONTH, -2);

    // se obtiene la fecha final
    int idiaFrom = gc.get(Calendar.DAY_OF_MONTH);
    int imesFrom = gc.get(Calendar.MONTH);
    int ianioFrom = gc.get(Calendar.YEAR);

    if (restaranio) {
      ianioFrom -= 1;
    }

    sdiaFrom = Integer.toString(idiaFrom);
    sanioFrom = Integer.toString(ianioFrom);
    smesFrom = getMesFromNumber(imesFrom);

    String from = sdiaFrom + "/" + smesFrom + "/" + sanioFrom;
    String to = sdiaTo + "/" + smesTo + "/" + sanioTo;

    System.out.println("From: " + from);
    System.out.println("To: " + to);

    datos.setFecha(to);
    datos.setFechaDia(to);
    datos.setFechaHistorica(from);

    if (tipoConsulta.equals("CC") || tipoConsulta.equals("IV")) {
      log.debug("*********************ENTRA EN CONSULTA DE CUENTA DE CHEQUES*********************");
      DatosConsultaMovimientosBean bean = new DatosConsultaMovimientosBean();

      bean.setCuenta(datos.getCuenta());
      bean.setFechaDia(datos.getFechaDia());
      bean.setFechaHistorica(datos.getFechaHistorica());
      bean.setImporte(datos.getImporte());
      bean.setTipoCuenta(datos.getTipoCuenta());
      bean.setTipoMovimiento(datos.getTipoMovimiento());

      return getMovimientoCuentasChequeras(cliente, bean, bitacoraBean);
    }
    if (tipoConsulta.equals("TC")) {
      log.debug(
          "*********************ENTRA EN CONSULTA DE TARJETAS DE CREDITO*********************");
      DatosEntradaMovimientosBean bean = new DatosEntradaMovimientosBean();

      bean.setCuenta(datos.getCuenta());
      bean.setDespliega(datos.getDespliega());
      bean.setExtracto(datos.getExtracto());
      bean.setFecha(datos.getFecha());
      bean.setFechaHistorica(datos.getFechaHistorica());
      bean.setNumeroExtracto(datos.getNumeroExtracto());
      bean.setTasaAnualizada(datos.getTasaAnualizada());
      bean.setTipoOperacion(datos.getTipoOperacion());
      bean.setTipoProducto(datos.getTipoProducto());

      return getMovimientoTarjetacredito(cliente, bean, bitacoraBean);
    }
    if (tipoConsulta.equals("FI")) {
      log.debug(
          "*********************ENTRA EN CONSULTA DE FONDOS DE INVERSION*********************");

      DatosEntradaMovimientosBean bean = new DatosEntradaMovimientosBean();

      bean.setCuenta(datos.getCuenta());
      bean.setDespliega(datos.getDespliega());
      bean.setExtracto(datos.getExtracto());
      bean.setFecha(datos.getFecha());
      bean.setFechaHistorica(datos.getFechaHistorica());
      bean.setNumeroExtracto(datos.getNumeroExtracto());
      bean.setTasaAnualizada(datos.getTasaAnualizada());
      bean.setTipoOperacion(datos.getTipoOperacion());
      bean.setTipoProducto(datos.getTipoProducto());

      return getMovimientoFondoInversion(cliente, bean, bitacoraBean);
    }
    if (tipoConsulta.equals("IV")) {
      log.debug(
          "*********************ENTRA EN CONSULTA DE INVERSION VISTA*************************");
    }
    return null;
  }