コード例 #1
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public boolean insertProcess(String processName) throws Exception {
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    PreparedStatement prep = null;
    ;
    try {
      init();
      conn = getConnection();

      try {
        stat = conn.createStatement();
        rs = stat.executeQuery("select id  from processes where processName='" + processName + "'");

        while (rs.next()) {
          return false;
        }
      } catch (Exception e) {
        LotteryLogger.getInstance().setError("Error in checking process " + e.getMessage());
        throw new Exception("Error in checking process");

      } finally {
        if (rs != null) rs.close();
        if (stat != null) stat.close();
        if (conn != null) conn.close();
      }
      conn = getConnection();
      conn.setAutoCommit(false);
      prep = conn.prepareStatement("insert into processes values ( null," + "?,?, ?,?);");

      prep.setString(1, processName);

      prep.setString(2, Util.formatDate(new Date()));
      prep.setString(3, Util.formatDate(new Date()));
      prep.addBatch();

      prep.executeBatch();
      conn.commit();
      return true;
    } catch (Exception e) {
      LotteryLogger.getInstance().setError("Error in inserting process" + e.getMessage());

      if (conn != null) conn.rollback();
      throw new Exception("Database Error");
    } finally {
      if (prep != null) prep.close();
      if (conn != null) conn.close();
    }
  }
コード例 #2
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public void insertBumper(String bumper) throws Exception {
    Connection conn = null;
    PreparedStatement prep = null;
    try {
      conn = getConnection();
      conn.setAutoCommit(false);
      prep = conn.prepareStatement("insert or ignore into bumper" + " values(null,?,?,?,?,?);");

      prep.setString(1, bumper);

      prep.setBoolean(2, false);
      prep.setString(3, Util.formatDate(new Date()));
      prep.setString(4, Util.formatDate(new Date()));
      prep.addBatch();

      prep.executeBatch();
      conn.commit();

    } catch (Exception e) {
      LotteryLogger.getInstance().setError("error in saving bumper ," + e.getMessage());
      if (conn != null) conn.rollback();
      throw new Exception("error in saving bumper");
    } finally {
      if (prep != null) prep.close();
      if (conn != null) conn.close();
    }
  }
コード例 #3
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public int getSerialNumber(String db) throws Exception {
    int serialNumber = 0;
    Connection conn = null;
    Statement stat = null;
    try {
      conn = getConnection(db);
      stat = conn.createStatement();

      ResultSet rs = stat.executeQuery("select max(serialNumber) as serialNumber from customer");

      while (rs.next()) {
        serialNumber = rs.getInt("serialNumber");
      }

      rs.close();
      conn.close();
      return ++serialNumber;
    } catch (Exception e) {
      LotteryLogger.getInstance().setError("Error in getting serial Number" + e.getMessage());
      throw new Exception("Error in getting serial Number");
    } finally {
      if (stat != null) stat.close();
      if (conn != null) conn.close();
    }
  }
コード例 #4
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public void createBumperTable() throws Exception {

    Connection conn = null;
    Statement stat = null;
    try {
      conn = getConnection();
      stat = conn.createStatement();
      conn.setAutoCommit(false);
      stat.executeUpdate(
          "create table if not exists bumper ( id INTEGER PRIMARY KEY AUTOINCREMENT,"
              + "name TEXT collate nocase ,isSelected NUMERIC,"
              + "createdDate DATETIME,"
              + "updatedDate DATETIME,"
              + "deletedDate DATETIME);");
      stat.executeUpdate(
          "create table if not exists messageTemplates ( id INTEGER PRIMARY KEY AUTOINCREMENT,"
              + "message TEXT collate nocase);");
      conn.commit();
    } catch (Exception e) {

      LotteryLogger.getInstance().setError("Error in creating message tables, " + e.getMessage());
      throw new Exception("Exception in creating message table");

    } finally {
      if (stat != null) stat.close();
      if (conn != null) conn.close();
    }
  }
コード例 #5
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public List<String> getBumpers() throws Exception {
    List<String> bumpers = new ArrayList<String>();

    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {

      conn = getConnection();
      stat = conn.createStatement();
      rs = stat.executeQuery("select name from bumper where deletedDate is null ORDER BY name");

      while (rs.next()) {

        String s = rs.getString("name") == null ? "" : rs.getString("name");

        bumpers.add(s);
      }

      return bumpers;
    } catch (Exception e) {
      LotteryLogger.getInstance().setError("Error in getting unique numbers " + e.getMessage());
      throw new Exception("Error in getting unique numbers ");
    } finally {
      if (rs != null) rs.close();
      if (stat != null) stat.close();
      if (conn != null) conn.close();
    }
  }
コード例 #6
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public int getCustomersCount() throws Exception {

    int count = 0;
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {

      conn = getConnection();
      stat = conn.createStatement();
      rs = stat.executeQuery("select count(id) as count from customer");

      while (rs.next()) {
        count = rs.getInt("count");
      }

      return count;
    } catch (Exception e) {
      LotteryLogger.getInstance().setError("Error in getting customers count ," + e.getMessage());
      throw new Exception("Error in getting customers count");
    } finally {
      if (rs != null) rs.close();
      if (stat != null) stat.close();
      if (conn != null) conn.close();
    }
  }
コード例 #7
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public String getDefaultMessage() throws Exception {
    String message = "";

    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {

      conn = getConnection();
      stat = conn.createStatement();
      rs = stat.executeQuery("select message from messageTemplates where id=1");

      while (rs.next()) {

        message = rs.getString("message") == null ? "" : rs.getString("message");
      }
      return message;

    } catch (Exception e) {
      LotteryLogger.getInstance()
          .setError("Error in getting message from template message " + e.getMessage());
      throw new Exception("Error in getting default message");
    } finally {
      if (rs != null) rs.close();
      if (stat != null) stat.close();
      if (conn != null) conn.close();
    }
  }
コード例 #8
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public List<String> getProcesses() throws Exception {
    List<String> processes = new ArrayList<String>();
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    PreparedStatement prep = null;

    try {

      conn = getConnection();
      stat = conn.createStatement();
      rs = stat.executeQuery("select * from processes");

      while (rs.next()) {
        processes.add(rs.getString("processName"));
      }

    } catch (Exception e) {
      LotteryLogger.getInstance().setError("Error in getting processes");
      throw new Exception("Error in getting processes");

    } finally {
      if (rs != null) rs.close();
      if (prep != null) prep.close();
      if (conn != null) conn.close();
    }
    return processes;
  }
コード例 #9
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public void deleteProcess(String processName) throws Exception {
    Connection conn = null;
    PreparedStatement prest = null;

    try {
      conn = getConnection();
      String sql =
          "UPDATE processes SET deletedDate = ? ,updatedDate = ? WHERE processName='"
              + processName
              + "'";
      prest = conn.prepareStatement(sql);
      prest.setString(0, Util.formatDate(new Date()));

      prest.setString(1, Util.formatDate(new Date()));

      prest.executeUpdate();
      conn.commit();

    } catch (Exception e) {
      LotteryLogger.getInstance()
          .setError("Error in updating process delete date" + e.getMessage());
      throw new Exception("Error in updating process date");
    } finally {

      if (prest != null) prest.close();
      if (conn != null) conn.close();
    }
  }
コード例 #10
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public String getSelectedBumper() throws Exception {
    String bumper = "";

    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {

      conn = getConnection();
      stat = conn.createStatement();
      rs = stat.executeQuery("select name from bumper where isSelected=1");

      while (rs.next()) {

        bumper = rs.getString("name") == null ? "" : rs.getString("name");
      }
      return bumper;

    } catch (Exception e) {
      LotteryLogger.getInstance().setError("Error in getting unique numbers " + e.getMessage());
      throw new Exception("Error in getting unique numbers ");
    } finally {
      if (rs != null) rs.close();
      if (stat != null) stat.close();
      if (conn != null) conn.close();
    }
  }
コード例 #11
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  private void insertDummyData(Connection conn, String tableName) throws Exception {
    try {
      Statement stat = conn.createStatement();

      stat.executeUpdate(
          "insert or ignore into "
              + tableName
              + "Messages (name, phoneNumber) values('dummy name','1234567890');");
      stat.executeUpdate(
          "insert or ignore into "
              + tableName
              + "Mails (name, emailId) values('dummy name','*****@*****.**');");

      stat.close();
    } catch (Exception e) {
      LotteryLogger.getInstance()
          .setError(
              "Error in dummy Data, where table name is"
                  + ((Util.isStringNullOrEmpty(tableName) ? "" : tableName))
                  + e.getMessage());
      throw new Exception(
          "Error in inserting dummy data, where table name is"
              + ((Util.isStringNullOrEmpty(tableName) ? "" : tableName))
              + e.getMessage());
    }
  }
コード例 #12
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public void updateCustomerMessage(String table, boolean isSend, String status, String emailId)
      throws Exception {
    Connection conn = null;
    PreparedStatement prest = null;
    try {
      conn = getConnection();
      String sql =
          "UPDATE "
              + table
              + "Messages SET isSend = ?  , status = ? ,updatedDate = ? WHERE "
              + Fields.emailId
              + " = ?";
      prest = conn.prepareStatement(sql);
      prest.setBoolean(0, isSend);
      prest.setString(1, status);
      prest.setString(2, Util.formatDate(new Date()));
      prest.setString(3, emailId);
      prest.executeUpdate();
      conn.commit();
    } catch (Exception e) {
      LotteryLogger.getInstance()
          .setError("Error in updating customer message where table is" + table + e.getMessage());
      throw new Exception("Error in updating emails");
    } finally {

      if (prest != null) prest.close();
      if (conn != null) conn.close();
    }
  }
コード例 #13
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public void insertCustomers(List<Customer> customers) throws Exception {
    Connection conn = null;
    PreparedStatement prep = null;
    try {
      init();
      conn = getConnection();
      conn.setAutoCommit(false);
      for (Customer customer : customers) {

        prep =
            conn.prepareStatement(
                "insert into customer values ( null,"
                    + "?,"
                    + "?,"
                    + "?,"
                    + " ?,"
                    + "?,"
                    + " ?,"
                    + "?,"
                    + "? ,"
                    + "? ,"
                    + "?,"
                    + "?,"
                    + "?,"
                    + "? , ?, ?);");

        prep.setInt(1, customer.getSerialNumber());
        prep.setString(2, customer.getSeries());
        prep.setString(3, customer.getTicketNumber());
        prep.setString(4, customer.getName());
        prep.setString(5, customer.getLotteryType());
        prep.setString(6, customer.getBumperName());
        prep.setString(7, customer.getPhoneNumber());
        prep.setString(8, customer.getEmailId());
        prep.setString(9, customer.getAddress());
        prep.setBoolean(10, false);
        prep.setBoolean(11, false);
        prep.setString(12, Constants.backupMessage);
        prep.setString(13, Util.formatDate(customer.getDate()));
        prep.setString(14, Util.formatDate(new Date()));
        prep.setString(15, Util.formatDate(new Date()));
        prep.addBatch();

        prep.executeBatch();

        prep.close();
      }
      conn.commit();

    } catch (Exception e) {
      LotteryLogger.getInstance().setError("Error in inserting customers," + e.getMessage());
      if (conn != null) conn.rollback();
      throw new Exception("Error in inserting customer," + e.getMessage());

    } finally {
      if (prep != null) prep.close();
      if (conn != null) conn.close();
    }
  }
コード例 #14
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
 private Connection getConnection(String dbName) throws Exception {
   Connection connection;
   try {
     Class.forName("org.sqlite.JDBC");
     connection = DriverManager.getConnection("jdbc:sqlite:" + dbName + ".db");
     return connection;
   } catch (Exception e) {
     LotteryLogger.getInstance().setError("Error in creating connection" + e.getMessage());
     throw new Exception("connection error , due to " + e.getMessage());
   }
 }
コード例 #15
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
 private Connection getConnection() throws Exception {
   Connection connection;
   try {
     Class.forName("org.sqlite.JDBC");
     System.out.println(new File("lottery.db").getAbsolutePath());
     connection = DriverManager.getConnection("jdbc:sqlite:lottery.db");
     return connection;
   } catch (Exception e) {
     LotteryLogger.getInstance().setError("Error in creating connection" + e.getMessage());
     throw new Exception("connection error , due to " + e.getMessage());
   }
 }
コード例 #16
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public int getUniqueMessagesCountFromTable(String tableName) throws Exception {

    int count = 0;
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {

      createMessageTable(tableName);
      conn = getConnection();

      stat = conn.createStatement();
      rs =
          stat.executeQuery(
              "select count(DISTINCT phoneNumber) as count from customer where phoneNumber not in "
                  + "(select phoneNumber from "
                  + tableName
                  + "Messages where "
                  + "isSend=1 or status !='"
                  + Constants.failedMessaage
                  + "') and phoneNumber!=''");

      while (rs.next()) {
        count = rs.getInt("count");
      }

      return count;
    } catch (Exception e) {
      LotteryLogger.getInstance()
          .setError(
              "Error in getting messages count from table"
                  + (Util.isStringNullOrEmpty(tableName) ? "" : tableName)
                  + ", "
                  + e.getMessage());
      throw new Exception(
          "Error in getting messages count from table"
              + (Util.isStringNullOrEmpty(tableName) ? "" : tableName)
              + ", "
              + e.getMessage());

    } finally {
      if (rs != null) rs.close();
      if (stat != null) stat.close();
      if (conn != null) conn.close();
    }
  }
コード例 #17
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public void deleteBumper(String bumper) throws Exception {
    Connection conn = null;
    Statement stat = null;
    try {
      conn = getConnection();
      String sql = "delete from bumper where name='" + bumper + "'";
      conn = getConnection();
      conn.setAutoCommit(false);
      stat = conn.createStatement();
      stat.execute(sql);
      conn.commit();
    } catch (Exception e) {

      LotteryLogger.getInstance().setError("Error in deleting bumper, " + e.getMessage());
      throw new Exception("Error in deleting bumper");

    } finally {
      if (stat != null) stat.close();
      if (conn != null) conn.close();
    }
  }
コード例 #18
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public void dropTables(String tableName) throws Exception {

    Connection conn = null;
    Statement stat = null;
    try {

      conn = getConnection();

      stat = conn.createStatement();
      stat.executeUpdate("drop table if exists " + tableName + ";");
      conn.close();

    } catch (Exception e) {
      LotteryLogger.getInstance().setError("Error in droping tables " + e.getMessage());
      throw new Exception("error in droping tables");

    } finally {
      if (stat != null) stat.close();
      if (conn != null) conn.close();
    }
  }
コード例 #19
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public boolean init() {
    try {

      Connection conn = getConnection();
      Statement stat = conn.createStatement();

      stat.executeUpdate(
          "create table if not exists customer( id INTEGER PRIMARY KEY AUTOINCREMENT,"
              + "serialNumber INTEGER,"
              + "series TEXT,"
              + "ticketNumber TEXT,"
              + "name TEXT collate nocase,"
              + "lotteryType TEXT collate nocase,"
              + "bumperName TEXT collate nocase,"
              + "phoneNumber TEXT collate nocase,"
              + "emailId TEXT collate nocase,"
              + "address TEXT collate nocase, "
              + "isMessageSend Numeric,"
              + "isMailSend Numeric,"
              + "messageStatus TEXT collate nocase,"
              + "date DATETIME,"
              + "createdDate DATETIME,"
              + "updatedDate DATETIME,"
              + "deletedDate DATETIME);");
      stat.executeUpdate(
          "create table if not exists processes( id INTEGER PRIMARY KEY AUTOINCREMENT,"
              + "processName TEXT collate nocase,"
              + "createdDate DATETIME,"
              + "updatedDate DATETIME,"
              + "deletedDate DATETIME);");
      stat.close();
      conn.close();

    } catch (Exception e) {
      LotteryLogger.getInstance()
          .setError("Error in creating table customer and prrocesses," + e.getMessage());
      return false;
    }
    return true;
  }
コード例 #20
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public List<Customer> getUniqueMailsFromTable(String tableName) throws Exception {
    List<Customer> customers = new ArrayList<Customer>();
    Connection conn = null;
    Statement stat = null;
    ResultSet rs = null;
    try {

      createMessageTable(tableName);
      conn = getConnection();
      stat = conn.createStatement();
      rs =
          stat.executeQuery(
              "select name,emailId  from customer where phoneNumber not in "
                  + "(select emailId from "
                  + tableName
                  + "Mails where "
                  + "isSend=0 )  and emailId!=''  group by emailId ORDER BY id LIMIT 0, 1000");

      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));
        customers.add(customer);
      }

      return customers;
    } catch (Exception e) {
      LotteryLogger.getInstance()
          .setError("Error in getting unique mails count ," + e.getMessage());
      throw new Exception("Error in getting unique mails count ," + e.getMessage());
    } finally {
      if (rs != null) rs.close();
      if (stat != null) stat.close();
      if (conn != null) conn.close();
    }
  }
コード例 #21
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public void insertMessageData(String tableName, Customer customer, boolean isSend, String status)
      throws Exception {
    Connection conn = null;
    PreparedStatement prep = null;
    try {
      conn = getConnection();
      conn.setAutoCommit(false);
      prep =
          conn.prepareStatement(
              "insert or ignore into " + tableName + "Messages values(null,?,?,?,?,?,?,?);");

      prep.setString(1, customer.getName());

      prep.setString(2, customer.getPhoneNumber());
      prep.setBoolean(3, isSend);
      prep.setString(4, status);
      prep.setString(5, Util.formatDate(new Date()));
      prep.setString(6, Util.formatDate(new Date()));
      prep.addBatch();

      prep.executeBatch();
      conn.commit();

    } catch (Exception e) {
      LotteryLogger.getInstance()
          .setError(
              "error in saving to database process customer, where table name is"
                  + ((Util.isStringNullOrEmpty(tableName) ? "" : tableName))
                  + e.getMessage());
      if (conn != null) conn.rollback();
      throw new Exception(
          "error in saving to database process customer, where table name is"
              + ((Util.isStringNullOrEmpty(tableName) ? "" : tableName)));
    } finally {
      if (prep != null) prep.close();
      if (conn != null) conn.close();
    }
  }
コード例 #22
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public void insertDefaultMessage(String message) throws Exception {
    Connection conn = null;
    PreparedStatement prep = null;
    try {
      conn = getConnection();
      conn.setAutoCommit(false);
      prep = conn.prepareStatement("insert or replace into messageTemplates" + " values(1,?);");

      prep.setString(1, message);
      prep.addBatch();

      prep.executeBatch();
      conn.commit();

    } catch (Exception e) {
      LotteryLogger.getInstance().setError("error in saving default message ," + e.getMessage());
      if (conn != null) conn.rollback();
      throw new Exception("error in saving default message");
    } finally {
      if (prep != null) prep.close();
      if (conn != null) conn.close();
    }
  }
コード例 #23
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public void createMessageTable(String table) throws Exception {

    Connection conn = null;
    Statement stat = null;
    try {
      conn = getConnection();
      stat = conn.createStatement();
      conn.setAutoCommit(false);
      stat.executeUpdate(
          "create table if not exists "
              + table.toLowerCase()
              + "Messages ( id INTEGER PRIMARY KEY AUTOINCREMENT,"
              + "name TEXT collate nocase ,phoneNumber TEXT unique not null,isSend NUMERIC,status TEXT collate nocase,"
              + "createdDate DATETIME,"
              + "updatedDate DATETIME,"
              + "deletedDate DATETIME);");
      stat.executeUpdate(
          "create table if not exists "
              + table.toLowerCase()
              + "Mails ( id INTEGER PRIMARY KEY AUTOINCREMENT,"
              + "name TEXT collate nocase ,emailId TEXT unique not null,isSend NUMERIC,"
              + "createdDate DATETIME,"
              + "updatedDate DATETIME,"
              + "deletedDate DATETIME);");
      insertDummyData(conn, table);
      conn.commit();
    } catch (Exception e) {

      LotteryLogger.getInstance().setError("Error in creating message tables, " + e.getMessage());
      if (conn != null) conn.rollback();
      throw new Exception("Exception in creating message table");

    } finally {
      if (stat != null) stat.close();
      if (conn != null) conn.close();
    }
  }
コード例 #24
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public void savePendings(
      List<Customer> customers, boolean isMessageSend, boolean isMailSend, String status)
      throws Exception {
    Connection conn = null;
    PreparedStatement prest = null;

    try {
      init();

      conn = getConnection();
      conn.setAutoCommit(false);

      for (Customer customer : customers) {
        String sql = "delete from customer where serialNumber=" + customer.getSerialNumber();

        Statement stat = conn.createStatement();
        stat.execute(sql);
        PreparedStatement prep =
            conn.prepareStatement(
                "insert  into customer values ( null,"
                    + "?,"
                    + "?,"
                    + "?,"
                    + " ?,"
                    + "?,"
                    + " ?,"
                    + "?,"
                    + "? ,"
                    + "? ,"
                    + "?,"
                    + "?,"
                    + "?,"
                    + "? , ?, ?,?) ;");

        prep.setInt(1, customer.getSerialNumber());
        prep.setString(2, customer.getSeries());
        prep.setString(3, customer.getTicketNumber());
        prep.setString(4, customer.getName());
        prep.setString(5, customer.getLotteryType());
        prep.setString(6, customer.getBumperName());
        prep.setString(7, customer.getPhoneNumber());
        prep.setString(8, customer.getEmailId());
        prep.setString(9, customer.getAddress());
        prep.setBoolean(10, isMessageSend);
        prep.setBoolean(11, isMailSend);
        prep.setString(12, status);
        prep.setString(13, Util.formatDate(customer.getDate()));
        prep.setString(14, Util.formatDate(new Date()));
        prep.setString(15, Util.formatDate(new Date()));
        prep.addBatch();

        prep.executeBatch();

        prep.close();
      }
      conn.commit();

    } catch (Exception e) {
      LotteryLogger.getInstance().setError("Error in inserting pending customers" + e.getMessage());
      if (conn != null) conn.rollback();
      throw new Exception("Error in inserting pending customers");

    } finally {
      if (conn != null) conn.close();
    }
  }
コード例 #25
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();
    }
  }
コード例 #26
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
  public void mergeDatabase(String mainDb, String toBeMergeDb) throws Exception {

    int serialNumber = getSerialNumber(mainDb);
    Connection db1Conn = getConnection(mainDb);
    Connection db2Conn = getConnection(toBeMergeDb);
    Statement stat = db2Conn.createStatement();
    String query = "select * from customer";
    ResultSet rs = stat.executeQuery(query);
    try {
      while (rs.next()) {
        PreparedStatement prep =
            db1Conn.prepareStatement(
                "insert into customer values ( null,"
                    + "?,"
                    + "?,"
                    + "?,"
                    + " ?,"
                    + "?,"
                    + " ?,"
                    + "?,"
                    + "? ,"
                    + "? ,"
                    + "?,"
                    + "?,"
                    + "?,"
                    + "? , ?, ?,?);");

        prep.setInt(1, serialNumber++);
        prep.setString(2, rs.getString(Fields.series) == null ? "" : rs.getString(Fields.series));
        prep.setString(
            3, rs.getString(Fields.ticketNumber) == null ? "" : rs.getString(Fields.ticketNumber));
        prep.setString(4, rs.getString(Fields.name) == null ? "" : rs.getString(Fields.name));
        prep.setString(
            5, rs.getString(Fields.lotteryType) == null ? "" : rs.getString(Fields.lotteryType));
        prep.setString(
            6, rs.getString(Fields.bumperName) == null ? "" : rs.getString(Fields.bumperName));
        prep.setString(
            7, rs.getString(Fields.phoneNumber) == null ? "" : rs.getString(Fields.phoneNumber));
        prep.setString(8, rs.getString(Fields.emailId) == null ? "" : rs.getString(Fields.emailId));
        prep.setString(9, rs.getString(Fields.address) == null ? "" : rs.getString(Fields.address));
        prep.setBoolean(10, rs.getBoolean("isMessageSend"));
        prep.setBoolean(11, rs.getBoolean("isMailSend"));
        prep.setString(
            12, rs.getString("messageStatus") == null ? "" : rs.getString("messageStatus"));
        prep.setString(13, rs.getString("date") == null ? "" : rs.getString("date"));
        prep.setString(14, rs.getString("createdDate") == null ? "" : rs.getString("createdDate"));
        prep.setString(15, rs.getString("updatedDate") == null ? "" : rs.getString("updatedDate"));
        prep.setString(15, rs.getString("deletedDate") == null ? "" : rs.getString("deletedDate"));
        prep.addBatch();
        prep.executeBatch();
        prep.executeBatch();

        prep.close();
      }
      rs.close();
      db1Conn.commit();

    } catch (Exception e) {
      LotteryLogger.getInstance().setError("Error in inserting pending customers" + e.getMessage());
      if (db1Conn != null) db1Conn.rollback();
      throw new Exception("Error in inserting pending customers");

    } finally {
      if (db2Conn != null) db2Conn.close();
      if (db1Conn != null) db1Conn.close();
    }
  }
コード例 #27
0
ファイル: Dbloader.java プロジェクト: sonuashta/cms
 public Dbloader() {
   LotteryLogger.getInstance().setInfo("dB LOADING");
   init();
 }
コード例 #28
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();
    }
  }