예제 #1
0
파일: Dbloader.java 프로젝트: sonuashta/cms
  public SearchResult getCustomers(SearchFilter searchFilter) throws Exception {
    SearchResult searchResult = new SearchResult();
    List<Customer> customers = new ArrayList<Customer>();
    String query = "select * from customer where id>=1 ";
    if (!Util.isStringNullOrEmpty(searchFilter.getName()))
      query += "and name= '" + searchFilter.getName() + "' ";
    if (searchFilter.getFromdate() != null)
      query +=
          "and date >= '"
              + Util.formatDate(Util.getStartingdate(searchFilter.getFromdate()))
              + "' and date < '"
              + Util.getNextDate(searchFilter.getToDate())
              + "'";
    if (!Util.isStringNullOrEmpty(searchFilter.getBumperName())
        && !Util.isStringNullOrEmpty(searchFilter.getBumperName())) {
      if (searchFilter.isBothSelected())
        query +=
            " and (lotteryType='monthly' or bumperName='" + searchFilter.getBumperName() + "' )";
      else {
        query += " and bumperName='" + searchFilter.getBumperName() + "' ";
      }
    } else if (!Util.isStringNullOrEmpty(searchFilter.getBumperName())) {
      query += " and bumperName='" + searchFilter.getBumperName() + "' ";
    } else {
      query += " and lotteryType='monthly'";
    }

    if (!Util.isStringNullOrEmpty(searchFilter.getPhoneNumber())) {
      query += " and phoneNumber='" + searchFilter.getPhoneNumber() + "' ";
    }
    if (!Util.isStringNullOrEmpty(searchFilter.getTicketNumber())) {
      query += " and ticketNumber='" + searchFilter.getTicketNumber() + "' ";
    }
    if (!Util.isStringNullOrEmpty(searchFilter.getEmailId())) {
      query += " and emailId='" + searchFilter.getEmailId() + "' ";
    }
    if (!Util.isStringNullOrEmpty(searchFilter.getAddress())) {
      query += " and address='" + searchFilter.getAddress() + "' ";
    }
    if (searchFilter.isMessageSend()) {
      query += "and (isMessageSend=1 )";
    }
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {

      conn = getConnection();
      stat = conn.createStatement();
      rs =
          stat.executeQuery(
              query
                  + " ORDER BY id LIMIT "
                  + searchFilter.getPageCount() * (searchFilter.getPageNumber() - 1)
                  + ", "
                  + (searchFilter.getPageCount()));

      while (rs.next()) {
        Customer customer = new Customer();

        customer.setName(rs.getString(Fields.name) == null ? "" : rs.getString(Fields.name));
        customer.setEmailId(
            rs.getString(Fields.emailId) == null ? "" : rs.getString(Fields.emailId));
        customer.setAddress(
            rs.getString(Fields.address) == null ? "" : rs.getString(Fields.address));
        customer.setBumperName(
            rs.getString(Fields.bumperName) == null ? "" : rs.getString(Fields.bumperName));
        customer.setDate(
            rs.getString(Fields.date) == null
                ? Util.getDate("")
                : Util.getDate(rs.getString(Fields.date)));
        customer.setLotteryType(
            rs.getString(Fields.lotteryType) == null ? "" : rs.getString(Fields.lotteryType));
        customer.setPhoneNumber(
            rs.getString(Fields.phoneNumber) == null ? "" : rs.getString(Fields.phoneNumber));
        customer.setSerialNumber(rs.getInt(Fields.serialNumber));
        customer.setTicketNumber(
            rs.getString(Fields.ticketNumber) == null ? "" : rs.getString(Fields.ticketNumber));
        customers.add(customer);
      }
      searchResult.totalResults = searchResultCount(searchFilter);
      searchResult.customers = customers;
      searchResult.searchFilter = searchFilter;
      return searchResult;
    } catch (Exception e) {
      LotteryLogger.getInstance()
          .setError("Error in getting filtered customers ," + e.getMessage());
      throw new Exception("Error in getting filtered customers,");
    } finally {
      if (rs != null) rs.close();
      if (stat != null) stat.close();
      if (conn != null) conn.close();
    }
  }
예제 #2
0
파일: Dbloader.java 프로젝트: sonuashta/cms
  public int searchResultCount(SearchFilter searchFilter) throws Exception {

    String query = "select count(id) as count from customer where id>=1 ";
    if (!Util.isStringNullOrEmpty(searchFilter.getName()))
      query += "and name= '" + searchFilter.getName() + "' ";
    if (searchFilter.getFromdate() != null)
      query +=
          "and date >= '"
              + Util.formatDate(Util.getStartingdate(searchFilter.getFromdate()))
              + "' and date < '"
              + Util.getNextDate(searchFilter.getToDate())
              + "'";
    if (!Util.isStringNullOrEmpty(searchFilter.getBumperName())
        && !Util.isStringNullOrEmpty(searchFilter.getBumperName())) {
      if (searchFilter.isBothSelected())
        query +=
            " and (lotteryType='monthly' or bumperName='" + searchFilter.getBumperName() + "' )";
      else {
        query += " and bumperName='" + searchFilter.getBumperName() + "' ";
      }
    } else if (!Util.isStringNullOrEmpty(searchFilter.getBumperName())) {
      query += " and bumperName='" + searchFilter.getBumperName() + "' ";
    } else {
      query += " and lotteryType='monthly'";
    }

    if (!Util.isStringNullOrEmpty(searchFilter.getPhoneNumber())) {
      query += " and phoneNumber='" + searchFilter.getPhoneNumber() + "' ";
    }
    if (!Util.isStringNullOrEmpty(searchFilter.getTicketNumber())) {
      query += " and ticketNumber='" + searchFilter.getTicketNumber() + "' ";
    }
    if (!Util.isStringNullOrEmpty(searchFilter.getEmailId())) {
      query += " and emailId='" + searchFilter.getEmailId() + "' ";
    }
    if (!Util.isStringNullOrEmpty(searchFilter.getAddress())) {
      query += " and address='" + searchFilter.getAddress() + "' ";
    }
    if (searchFilter.isMessageSend()) {
      query += "and (isMessageSend=1)";
    }
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {

      conn = getConnection();
      stat = conn.createStatement();
      rs = stat.executeQuery(query);
      int count = 0;
      while (rs.next()) {
        count = rs.getInt("count");
      }

      return count;
    } catch (Exception e) {
      LotteryLogger.getInstance()
          .setError("Error in getting filtered customers ," + e.getMessage());
      throw new Exception("Error in getting filtered customers,");
    } finally {
      if (rs != null) rs.close();
      if (stat != null) stat.close();
      if (conn != null) conn.close();
    }
  }