<commit_before>package com.renomad.xenos;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
import java.util.List;
import com.renomad.xenos.Database_access;
import com.renomad.xenos.Requestoffer;
import com.renomad.xenos.Others_Requestoffer;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.CallableStatement;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
  * this class consists of methods that act upon Requestoffers
  */
public final class Requestoffer_utils {

  private Requestoffer_utils() {
    //private constructor.  This class is not allowed to be
    //instantiated.  do nothing here.
  }


  /**
    * put the requestoffer into a 'draft' status,
    * hidden from everyone but the owner
    */
  public static boolean 
    retract_requestoffer(int user_id, int requestoffer_id ) {
    CallableStatement cs = null;
    try {
      Connection conn = Database_access.get_a_connection();
      cs = conn.prepareCall(String.format(
        "{call retract_requestoffer(%d, %d)}" 
        , user_id, requestoffer_id));
      cs.execute();
    } catch (SQLException ex) {
      Database_access.handle_sql_exception(ex);
      return false;
    } finally {
      Database_access.close_statement(cs);
    }
    return true;
  }


  /**
    * put the requestoffer into an 'open' status,
    * so people can potentially handle it.
    */
  public static boolean 
    publish_requestoffer(int requestoffer_id, int user_id) {
    CallableStatement cs = null;
    try {
      Connection conn = Database_access.get_a_connection();
      cs = conn.prepareCall(String.format(
        "{call publish_requestoffer(%d, %d)}" 
        , user_id, requestoffer_id));
      cs.execute();
    } catch (SQLException ex) {
      Database_access.handle_sql_exception(ex);
      return false;
    } finally {
      Database_access.close_statement(cs);
    }
    return true;
  }


  /**
    * returns unviewed messages, localized
    *  from the temporary_messages table
    * or an empty String array if there are none.
    */
  public static String[] 
    get_my_temporary_msgs(int user_id, Localization loc) {
    CallableStatement cs = null;
    try {
      Connection conn = Database_access.get_a_connection();
      cs = conn.prepareCall(String.format(
        "{call get_my_temporary_msgs(%d)}" , user_id ));
      ResultSet resultSet = cs.executeQuery();

      if (Database_access.resultset_is_null_or_empty(resultSet)) {
        return new String[0];
      }

      //keep adding rows of data while there is more data
      ArrayList<String> temp_msgs = new ArrayList<String>();
      while(resultSet.next()) {
        Integer msg_id = resultSet.getInt("message_localization_id");
        String text = resultSet.getNString("text");

        if (!Utils.is_null_or_empty(text)) { // a user message
          temp_msgs.add(text);
        } else if (msg_id != null && msg_id > 0) { // a system message
          temp_msgs.add(loc.get(msg_id,""));
        } else {    // an exceptional situation - shouldn't happen
          temp_msgs.add("ERROR_GETTING_TEMP_MSG");
        }

      }

      //convert arraylist to array
      String[] array_of_msgs = 
        temp_msgs.toArray(new String[temp_msgs.size()]);
			return array_of_msgs;
    } catch (SQLException ex) {
      Database_access.handle_sql_exception(ex);
      return new String[0];
    } finally {
      Database_access.close_statement(cs);
    }
  }

  /**
    * returns a list of localization values for the categories
    * @return List of Integers representing localization values 
    *   for categories, or null if nothing in db. The
    */
  public static ArrayList<Integer> get_all_categories() {
    // 1. set the sql
    String sqlText = 
      "SELECT category_id, requestoffer_category_value "+
      "FROM requestoffer_category; ";
    // 2. set up values we'll need outside the try
    PreparedStatement pstmt = null;
    try {
      // 3. get the connection and set up a statement
      Connection conn = Database_access.get_a_connection();
      pstmt = Database_access.prepare_statement(
          conn, sqlText);     
      // 4. execute a statement
      ResultSet resultSet = pstmt.executeQuery();
      // 5. check that we got results
      if (Database_access.resultset_is_null_or_empty(resultSet)) {
        return null;
      }

      // 6. get values from database and convert to an object
      //keep adding rows of data while there is more data
      ArrayList<Integer> categories = new ArrayList<Integer>();
      while(resultSet.next()) {
        int rcid = resultSet.getInt("category_id");
        categories.add(rcid); 
      }

      return categories;
    } catch (SQLException ex) {
      Database_access.handle_sql_exception(ex);
      return null;
    } finally {
      Database_access.close_statement(pstmt);
    }
  }


  /**
    * this will cancel a transaction in "taken" status, that is, a
    * requestoffer that has someone servicing it.
    */
  public static boolean
    cancel_taken_requestoffer(
        int user_id, int requestoffer_id) {
      CallableStatement cs = null;
      try {
        Connection conn = Database_access.get_a_connection();
        cs = conn.prepareCall(String.format(
          "{call cancel_taken_requestoffer(%d, %d)}" 
          , user_id, requestoffer_id));
        cs.execute();
        return true;
      } catch (SQLException ex) {
        Database_access.handle_sql_exception(ex);
        return false;
      } finally {
        Database_access.close_statement(cs);
      }
    }


  /**
    * indicate that you are interested in handling a particular
    * requestoffer.  This enters an item in service_requestoffer
    */
  public static boolean
    offer_to_take_requestoffer(int user_id, int requestoffer_id) {
    CallableStatement cs = null;
    try {
      Connection conn = Database_access.get_a_connection();
      cs = conn.prepareCall(String.format(
        "{call offer_to_take_requestoffer(%d, %d)}" 
        , user_id, requestoffer_id));
      cs.execute();
    } catch (SQLException ex) {
      Database_access.handle_sql_exception(ex);
      return false;
    } finally {
      Database_access.close_statement(cs);
    }
    return true;

    }

  /**
    * sets the status of the requestoffer to taken for a given user.
    * @param new_handler_id the id of the user who will now handle this.
    * @param requestoffer_id the id of the requestoffer they will handle.
    * @return true if successful at taking, false otherwise.
    */
  public static boolean 
    choose_handler(int new_handler_id, int requestoffer_id) {
    CallableStatement cs = null;
    try {
      Connection conn = Database_access.get_a_connection();
      // see db_scripts/v1_procedures.sql take_requestoffer for
      // details on this stored procedure.
      
      cs = conn.prepareCall(String.format(
        "{call take_requestoffer(%d, %d)}" , new_handler_id, requestoffer_id));
      cs.execute();
    } catch (SQLException ex) {
			String msg = ex.getMessage();
			if (msg.contains("cannot take requestoffer")) {
      	return false;
			}
      Database_access.handle_sql_exception(ex);
      return false;
    } finally {
      Database_access.close_statement(cs);
    }
    return true;
  }


  /**
    * Gets an array of offers I made on requestoffers
    * 
    * @param user_id the user who made offers on requestoffers
    * @return an array of offers, or empty array if failure
    */
  public static Requestoffer[] 
    get_requestoffers_I_offered_to_service(int user_id) {
    
    String sqlText = 
      "SELECT r.requestoffer_id, r.datetime, r.description, r.points,"+
      "rs.status, r.requestoffering_user_id, r.handling_user_id, r.category "+
      "FROM requestoffer r "+
      "JOIN requestoffer_state rs "+
        "ON rs.requestoffer_id = r.requestoffer_id " +
      "JOIN requestoffer_service_request rsr "+
        "ON r.requestoffer_id = rsr.requestoffer_id " +
      "WHERE rsr.user_id = ? AND rsr.status = 106";

    PreparedStatement pstmt = null;
    try {
      Connection conn = Database_access.get_a_connection();
      pstmt = Database_access.prepare_statement(
          conn, sqlText);     
      pstmt.setInt( 1, user_id);
      ResultSet resultSet = pstmt.executeQuery();
      if (Database_access.resultset_is_null_or_empty(resultSet)) {
        return new Requestoffer[0];
      }

      ArrayList<Requestoffer> offers = 
        new ArrayList<Requestoffer>();
      while(resultSet.next()) {
        int rid = resultSet.getInt("requestoffer_id");
        String dt = resultSet.getString("datetime");
        String d = resultSet.getNString("description");
        int p = resultSet.getInt("points");
        int s = resultSet.getInt("status");
        int ru = resultSet.getInt("requestoffering_user_id");
        int hu = resultSet.getInt("handling_user_id");
        int ca = resultSet.getInt("category");
        Requestoffer requestoffer = new Requestoffer(rid,dt,d,p,s,ru,hu,ca);
        offers.add(requestoffer);
      }

      //convert arraylist to array
      Requestoffer[] array_of_offers = 
        offers.toArray(new Requestoffer[offers.size()]);

      return array_of_offers;
    } catch (SQLException ex) {
      Database_access.handle_sql_exception(ex);
      return new Requestoffer[0];
    } finally {
      Database_access.close_statement(pstmt);
    }
  }


  public static class Service_request {

    public final int requestoffer_id;
    public final String desc;
    public final int user_id; // the user offering to handle it
    public final String date_created;
    public final String username;

    public Service_request(
        int requestoffer_id, 
        int user_id, 
        String date_created,
        String desc, String username) {
      this.requestoffer_id = requestoffer_id;
      this.user_id = user_id;
      this.date_created = date_created;
      this.desc = desc;
      this.username = username;
    }
  }


  /**
    * returns true if the user in user_id is indeed offering
    * to service this requestoffer
    * 
    * @param user_id a user potentially offering to service the requestoffer
    * @return true if they are offering to service, false otherwise.
    */
  public static boolean is_offering_to_service(int user_id, int requestoffer_id) {
    
    String sqlText = 
      "SELECT COUNT(*) as the_count " +
      "FROM requestoffer_service_request " +
      "WHERE user_id = ? AND requestoffer_id = ? AND status = 106 ";

    PreparedStatement pstmt = null;
    try {
      Connection conn = Database_access.get_a_connection();
      pstmt = Database_access.prepare_statement(
          conn, sqlText);     
      pstmt.setInt( 1, user_id);
      pstmt.setInt( 2, requestoffer_id);
      ResultSet resultSet = pstmt.executeQuery();
      if (Database_access.resultset_is_null_or_empty(resultSet)) {
        return false;
      }

      resultSet.next();
      int count = resultSet.getInt("the_count");
      if (resultSet.wasNull()) {
        return false;
      }
      return count == 1;
    } catch (SQLException ex) {
      Database_access.handle_sql_exception(ex);
      return false;
    } finally {
      Database_access.close_statement(pstmt);
    }
  }


  /**
    * Gets the list of users that have offered to service my request
    * that are not selected or rejected.
    * 
    * @param user_id the user asking about servicers 
    *   for their requestoffers
    * @return an array of service requests, or empty array if failure
    */
  public static Service_request[] get_service_requests(int user_id) {
    
    String sqlText = 
      "SELECT rsr.requestoffer_id, rsr.user_id, rsr.date_created, " +
      "ro.description, u.username " +
      "FROM requestoffer_service_request rsr " +
      "JOIN requestoffer ro "+
        "ON ro.requestoffer_id = rsr.requestoffer_id " +
      "JOIN user u " +
        "ON u.user_id = rsr.user_id " +
      "WHERE ro.requestoffering_user_id = ? AND rsr.status = 106";

    PreparedStatement pstmt = null;
    try {
      Connection conn = Database_access.get_a_connection();
      pstmt = Database_access.prepare_statement(
          conn, sqlText);     
      pstmt.setInt( 1, user_id);
      ResultSet resultSet = pstmt.executeQuery();
      if (Database_access.resultset_is_null_or_empty(resultSet)) {
        return new Service_request[0];
      }

      ArrayList<Service_request> service_requests = 
        new ArrayList<Service_request>();
      while(resultSet.next()) {
        int rid = resultSet.getInt("requestoffer_id");
        int uid = resultSet.getInt("user_id");
        String dt = resultSet.getString("date_created");
        String desc = resultSet.getNString("description");
        String username = resultSet.getNString("username");
        service_requests.add(new Service_request(rid, uid, dt, desc, username));
      }

      //convert arraylist to array
      Service_request[] array_of_service_requests = 
        service_requests.toArray(
            new Service_request[service_requests.size()]);

      return array_of_service_requests;
    } catch (SQLException ex) {
      Database_access.handle_sql_exception(ex);
      return new Service_request[0];
    } finally {
      Database_access.close_statement(pstmt);
    }
  }


  /**
    * Gets a specific Requestoffer 
    * 
    * @param requestoffer_id the id of a particular Requestoffer
    * @return a single Requestoffer, or null if failure
    */
  public static Requestoffer get_a_requestoffer(int requestoffer_id) {
    
    String sqlText = 
      "SELECT r.requestoffer_id, r.datetime, r.description, r.points,"+
      "rs.status, r.requestoffering_user_id, r.handling_user_id, r.category "+
      "FROM requestoffer r "+
      "JOIN requestoffer_state rs "+
        "ON rs.requestoffer_id = r.requestoffer_id " +
      "WHERE r.requestoffer_id = ? "+
      "GROUP BY requestoffer_id";

    PreparedStatement pstmt = null;
    try {
      Connection conn = Database_access.get_a_connection();
      pstmt = Database_access.prepare_statement(
          conn, sqlText);     
      pstmt.setInt( 1, requestoffer_id);
      ResultSet resultSet = pstmt.executeQuery();
      if (Database_access.resultset_is_null_or_empty(resultSet)) {
        return null;
      }

      resultSet.next();
      int rid = resultSet.getInt("requestoffer_id");
      String dt = resultSet.getString("datetime");
      String d = resultSet.getNString("description");
      int p = resultSet.getInt("points");
      int s = resultSet.getInt("status");
      int ru = resultSet.getInt("requestoffering_user_id");
      int hu = resultSet.getInt("handling_user_id");
      int ca = resultSet.getInt("category");
      Requestoffer requestoffer = new Requestoffer(rid,dt,d,p,s,ru,hu,ca);

      return requestoffer;
    } catch (SQLException ex) {
      Database_access.handle_sql_exception(ex);
      return null;
    } finally {
      Database_access.close_statement(pstmt);
    }
  }


  /**
    * An object used to hold the values
    * necessary for advanced search.  Immutable.
    */
  public static class Search_Object {

    public final String startdate;
    public final String enddate;
    public final String description;

    /**
      * categories as an array of numbers, delimited by commas
      */
    public final String categories;

    /**
      * statuses as an array of numbers, delimited by commas
      */           
    public final String statuses;

    /**
      * user ids as an array of numbers, delimited by commas
      */
    public final String user_ids;

    public final String postcode; // postal code
    public final Double distance;


    public Search_Object(
        String startdate,
        String enddate,
        String categories,
        String description,
        String statuses,
        String user_ids,
        String postcode,
        String distance
        ) {
      this.startdate = startdate;
      this.enddate = enddate;
      this.categories = Utils.is_comma_delimited_numbers(categories) ? 
        categories 
        : null;
      this.description = description;
      this.statuses = Utils.is_comma_delimited_numbers(statuses) ? 
        statuses 
        : null;
      this.user_ids = Utils.is_comma_delimited_numbers(user_ids) ? 
        user_ids 
        : null;
      this.postcode = postcode;
      this.distance = Utils.parse_double(distance);
    }

  }

	/**
		* A helper class to get the Requestoffers and page count to their
		* destination.
		*/
	public static class OR_Package {

		private Others_Requestoffer[] or;
		public int page_count;

		public OR_Package(Others_Requestoffer[] or, int page_count) {
			this.or = or;
			this.page_count = page_count;
		}

		/**
			* Returns a copy of the Requestoffers array.  Why do this?
			* to stay immutable.  For thread safety and performance.  If
			* I am wrong about that, feel free to reconsider this piece.
			*/
		public Others_Requestoffer[] get_requestoffers() {
			Others_Requestoffer[] oro = Arrays.copyOf(or, or.length);
			return oro;
		}
	}


  /**
    * this gives the "stair step" that the user is at for the
    * value of rank_ladder they have.  It's a way to get a sense
    * of their recent rankings.  See youtrack issue X-172 for more info.
    * @return a value between 0 and 6 if valid input, or -1 otherwise.
    */
  public static int get_ladder_step(int rank_ladder_val) {
    if ( rank_ladder_val < -1 ) {
      return 0;
    }
    if ( rank_ladder_val == -1 ) {
      return 1;<commit_msg>BK-#0- realized that it wasn't totally immutable.  Fixed.<commit_after>package com.renomad.xenos;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
import java.util.List;
import com.renomad.xenos.Database_access;
import com.renomad.xenos.Requestoffer;
import com.renomad.xenos.Others_Requestoffer;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.CallableStatement;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
  * this class consists of methods that act upon Requestoffers
  */
public final class Requestoffer_utils {

  private Requestoffer_utils() {
    //private constructor.  This class is not allowed to be
    //instantiated.  do nothing here.
  }


  /**
    * put the requestoffer into a 'draft' status,
    * hidden from everyone but the owner
    */
  public static boolean 
    retract_requestoffer(int user_id, int requestoffer_id ) {
    CallableStatement cs = null;
    try {
      Connection conn = Database_access.get_a_connection();
      cs = conn.prepareCall(String.format(
        "{call retract_requestoffer(%d, %d)}" 
        , user_id, requestoffer_id));
      cs.execute();
    } catch (SQLException ex) {
      Database_access.handle_sql_exception(ex);
      return false;
    } finally {
      Database_access.close_statement(cs);
    }
    return true;
  }


  /**
    * put the requestoffer into an 'open' status,
    * so people can potentially handle it.
    */
  public static boolean 
    publish_requestoffer(int requestoffer_id, int user_id) {
    CallableStatement cs = null;
    try {
      Connection conn = Database_access.get_a_connection();
      cs = conn.prepareCall(String.format(
        "{call publish_requestoffer(%d, %d)}" 
        , user_id, requestoffer_id));
      cs.execute();
    } catch (SQLException ex) {
      Database_access.handle_sql_exception(ex);
      return false;
    } finally {
      Database_access.close_statement(cs);
    }
    return true;
  }


  /**
    * returns unviewed messages, localized
    *  from the temporary_messages table
    * or an empty String array if there are none.
    */
  public static String[] 
    get_my_temporary_msgs(int user_id, Localization loc) {
    CallableStatement cs = null;
    try {
      Connection conn = Database_access.get_a_connection();
      cs = conn.prepareCall(String.format(
        "{call get_my_temporary_msgs(%d)}" , user_id ));
      ResultSet resultSet = cs.executeQuery();

      if (Database_access.resultset_is_null_or_empty(resultSet)) {
        return new String[0];
      }

      //keep adding rows of data while there is more data
      ArrayList<String> temp_msgs = new ArrayList<String>();
      while(resultSet.next()) {
        Integer msg_id = resultSet.getInt("message_localization_id");
        String text = resultSet.getNString("text");

        if (!Utils.is_null_or_empty(text)) { // a user message
          temp_msgs.add(text);
        } else if (msg_id != null && msg_id > 0) { // a system message
          temp_msgs.add(loc.get(msg_id,""));
        } else {    // an exceptional situation - shouldn't happen
          temp_msgs.add("ERROR_GETTING_TEMP_MSG");
        }

      }

      //convert arraylist to array
      String[] array_of_msgs = 
        temp_msgs.toArray(new String[temp_msgs.size()]);
			return array_of_msgs;
    } catch (SQLException ex) {
      Database_access.handle_sql_exception(ex);
      return new String[0];
    } finally {
      Database_access.close_statement(cs);
    }
  }

  /**
    * returns a list of localization values for the categories
    * @return List of Integers representing localization values 
    *   for categories, or null if nothing in db. The
    */
  public static ArrayList<Integer> get_all_categories() {
    // 1. set the sql
    String sqlText = 
      "SELECT category_id, requestoffer_category_value "+
      "FROM requestoffer_category; ";
    // 2. set up values we'll need outside the try
    PreparedStatement pstmt = null;
    try {
      // 3. get the connection and set up a statement
      Connection conn = Database_access.get_a_connection();
      pstmt = Database_access.prepare_statement(
          conn, sqlText);     
      // 4. execute a statement
      ResultSet resultSet = pstmt.executeQuery();
      // 5. check that we got results
      if (Database_access.resultset_is_null_or_empty(resultSet)) {
        return null;
      }

      // 6. get values from database and convert to an object
      //keep adding rows of data while there is more data
      ArrayList<Integer> categories = new ArrayList<Integer>();
      while(resultSet.next()) {
        int rcid = resultSet.getInt("category_id");
        categories.add(rcid); 
      }

      return categories;
    } catch (SQLException ex) {
      Database_access.handle_sql_exception(ex);
      return null;
    } finally {
      Database_access.close_statement(pstmt);
    }
  }


  /**
    * this will cancel a transaction in "taken" status, that is, a
    * requestoffer that has someone servicing it.
    */
  public static boolean
    cancel_taken_requestoffer(
        int user_id, int requestoffer_id) {
      CallableStatement cs = null;
      try {
        Connection conn = Database_access.get_a_connection();
        cs = conn.prepareCall(String.format(
          "{call cancel_taken_requestoffer(%d, %d)}" 
          , user_id, requestoffer_id));
        cs.execute();
        return true;
      } catch (SQLException ex) {
        Database_access.handle_sql_exception(ex);
        return false;
      } finally {
        Database_access.close_statement(cs);
      }
    }


  /**
    * indicate that you are interested in handling a particular
    * requestoffer.  This enters an item in service_requestoffer
    */
  public static boolean
    offer_to_take_requestoffer(int user_id, int requestoffer_id) {
    CallableStatement cs = null;
    try {
      Connection conn = Database_access.get_a_connection();
      cs = conn.prepareCall(String.format(
        "{call offer_to_take_requestoffer(%d, %d)}" 
        , user_id, requestoffer_id));
      cs.execute();
    } catch (SQLException ex) {
      Database_access.handle_sql_exception(ex);
      return false;
    } finally {
      Database_access.close_statement(cs);
    }
    return true;

    }

  /**
    * sets the status of the requestoffer to taken for a given user.
    * @param new_handler_id the id of the user who will now handle this.
    * @param requestoffer_id the id of the requestoffer they will handle.
    * @return true if successful at taking, false otherwise.
    */
  public static boolean 
    choose_handler(int new_handler_id, int requestoffer_id) {
    CallableStatement cs = null;
    try {
      Connection conn = Database_access.get_a_connection();
      // see db_scripts/v1_procedures.sql take_requestoffer for
      // details on this stored procedure.
      
      cs = conn.prepareCall(String.format(
        "{call take_requestoffer(%d, %d)}" , new_handler_id, requestoffer_id));
      cs.execute();
    } catch (SQLException ex) {
			String msg = ex.getMessage();
			if (msg.contains("cannot take requestoffer")) {
      	return false;
			}
      Database_access.handle_sql_exception(ex);
      return false;
    } finally {
      Database_access.close_statement(cs);
    }
    return true;
  }


  /**
    * Gets an array of offers I made on requestoffers
    * 
    * @param user_id the user who made offers on requestoffers
    * @return an array of offers, or empty array if failure
    */
  public static Requestoffer[] 
    get_requestoffers_I_offered_to_service(int user_id) {
    
    String sqlText = 
      "SELECT r.requestoffer_id, r.datetime, r.description, r.points,"+
      "rs.status, r.requestoffering_user_id, r.handling_user_id, r.category "+
      "FROM requestoffer r "+
      "JOIN requestoffer_state rs "+
        "ON rs.requestoffer_id = r.requestoffer_id " +
      "JOIN requestoffer_service_request rsr "+
        "ON r.requestoffer_id = rsr.requestoffer_id " +
      "WHERE rsr.user_id = ? AND rsr.status = 106";

    PreparedStatement pstmt = null;
    try {
      Connection conn = Database_access.get_a_connection();
      pstmt = Database_access.prepare_statement(
          conn, sqlText);     
      pstmt.setInt( 1, user_id);
      ResultSet resultSet = pstmt.executeQuery();
      if (Database_access.resultset_is_null_or_empty(resultSet)) {
        return new Requestoffer[0];
      }

      ArrayList<Requestoffer> offers = 
        new ArrayList<Requestoffer>();
      while(resultSet.next()) {
        int rid = resultSet.getInt("requestoffer_id");
        String dt = resultSet.getString("datetime");
        String d = resultSet.getNString("description");
        int p = resultSet.getInt("points");
        int s = resultSet.getInt("status");
        int ru = resultSet.getInt("requestoffering_user_id");
        int hu = resultSet.getInt("handling_user_id");
        int ca = resultSet.getInt("category");
        Requestoffer requestoffer = new Requestoffer(rid,dt,d,p,s,ru,hu,ca);
        offers.add(requestoffer);
      }

      //convert arraylist to array
      Requestoffer[] array_of_offers = 
        offers.toArray(new Requestoffer[offers.size()]);

      return array_of_offers;
    } catch (SQLException ex) {
      Database_access.handle_sql_exception(ex);
      return new Requestoffer[0];
    } finally {
      Database_access.close_statement(pstmt);
    }
  }


  public static class Service_request {

    public final int requestoffer_id;
    public final String desc;
    public final int user_id; // the user offering to handle it
    public final String date_created;
    public final String username;

    public Service_request(
        int requestoffer_id, 
        int user_id, 
        String date_created,
        String desc, String username) {
      this.requestoffer_id = requestoffer_id;
      this.user_id = user_id;
      this.date_created = date_created;
      this.desc = desc;
      this.username = username;
    }
  }


  /**
    * returns true if the user in user_id is indeed offering
    * to service this requestoffer
    * 
    * @param user_id a user potentially offering to service the requestoffer
    * @return true if they are offering to service, false otherwise.
    */
  public static boolean is_offering_to_service(int user_id, int requestoffer_id) {
    
    String sqlText = 
      "SELECT COUNT(*) as the_count " +
      "FROM requestoffer_service_request " +
      "WHERE user_id = ? AND requestoffer_id = ? AND status = 106 ";

    PreparedStatement pstmt = null;
    try {
      Connection conn = Database_access.get_a_connection();
      pstmt = Database_access.prepare_statement(
          conn, sqlText);     
      pstmt.setInt( 1, user_id);
      pstmt.setInt( 2, requestoffer_id);
      ResultSet resultSet = pstmt.executeQuery();
      if (Database_access.resultset_is_null_or_empty(resultSet)) {
        return false;
      }

      resultSet.next();
      int count = resultSet.getInt("the_count");
      if (resultSet.wasNull()) {
        return false;
      }
      return count == 1;
    } catch (SQLException ex) {
      Database_access.handle_sql_exception(ex);
      return false;
    } finally {
      Database_access.close_statement(pstmt);
    }
  }


  /**
    * Gets the list of users that have offered to service my request
    * that are not selected or rejected.
    * 
    * @param user_id the user asking about servicers 
    *   for their requestoffers
    * @return an array of service requests, or empty array if failure
    */
  public static Service_request[] get_service_requests(int user_id) {
    
    String sqlText = 
      "SELECT rsr.requestoffer_id, rsr.user_id, rsr.date_created, " +
      "ro.description, u.username " +
      "FROM requestoffer_service_request rsr " +
      "JOIN requestoffer ro "+
        "ON ro.requestoffer_id = rsr.requestoffer_id " +
      "JOIN user u " +
        "ON u.user_id = rsr.user_id " +
      "WHERE ro.requestoffering_user_id = ? AND rsr.status = 106";

    PreparedStatement pstmt = null;
    try {
      Connection conn = Database_access.get_a_connection();
      pstmt = Database_access.prepare_statement(
          conn, sqlText);     
      pstmt.setInt( 1, user_id);
      ResultSet resultSet = pstmt.executeQuery();
      if (Database_access.resultset_is_null_or_empty(resultSet)) {
        return new Service_request[0];
      }

      ArrayList<Service_request> service_requests = 
        new ArrayList<Service_request>();
      while(resultSet.next()) {
        int rid = resultSet.getInt("requestoffer_id");
        int uid = resultSet.getInt("user_id");
        String dt = resultSet.getString("date_created");
        String desc = resultSet.getNString("description");
        String username = resultSet.getNString("username");
        service_requests.add(new Service_request(rid, uid, dt, desc, username));
      }

      //convert arraylist to array
      Service_request[] array_of_service_requests = 
        service_requests.toArray(
            new Service_request[service_requests.size()]);

      return array_of_service_requests;
    } catch (SQLException ex) {
      Database_access.handle_sql_exception(ex);
      return new Service_request[0];
    } finally {
      Database_access.close_statement(pstmt);
    }
  }


  /**
    * Gets a specific Requestoffer 
    * 
    * @param requestoffer_id the id of a particular Requestoffer
    * @return a single Requestoffer, or null if failure
    */
  public static Requestoffer get_a_requestoffer(int requestoffer_id) {
    
    String sqlText = 
      "SELECT r.requestoffer_id, r.datetime, r.description, r.points,"+
      "rs.status, r.requestoffering_user_id, r.handling_user_id, r.category "+
      "FROM requestoffer r "+
      "JOIN requestoffer_state rs "+
        "ON rs.requestoffer_id = r.requestoffer_id " +
      "WHERE r.requestoffer_id = ? "+
      "GROUP BY requestoffer_id";

    PreparedStatement pstmt = null;
    try {
      Connection conn = Database_access.get_a_connection();
      pstmt = Database_access.prepare_statement(
          conn, sqlText);     
      pstmt.setInt( 1, requestoffer_id);
      ResultSet resultSet = pstmt.executeQuery();
      if (Database_access.resultset_is_null_or_empty(resultSet)) {
        return null;
      }

      resultSet.next();
      int rid = resultSet.getInt("requestoffer_id");
      String dt = resultSet.getString("datetime");
      String d = resultSet.getNString("description");
      int p = resultSet.getInt("points");
      int s = resultSet.getInt("status");
      int ru = resultSet.getInt("requestoffering_user_id");
      int hu = resultSet.getInt("handling_user_id");
      int ca = resultSet.getInt("category");
      Requestoffer requestoffer = new Requestoffer(rid,dt,d,p,s,ru,hu,ca);

      return requestoffer;
    } catch (SQLException ex) {
      Database_access.handle_sql_exception(ex);
      return null;
    } finally {
      Database_access.close_statement(pstmt);
    }
  }


  /**
    * An object used to hold the values
    * necessary for advanced search.  Immutable.
    */
  public static class Search_Object {

    public final String startdate;
    public final String enddate;
    public final String description;

    /**
      * categories as an array of numbers, delimited by commas
      */
    public final String categories;

    /**
      * statuses as an array of numbers, delimited by commas
      */           
    public final String statuses;

    /**
      * user ids as an array of numbers, delimited by commas
      */
    public final String user_ids;

    public final String postcode; // postal code
    public final Double distance;


    public Search_Object(
        String startdate,
        String enddate,
        String categories,
        String description,
        String statuses,
        String user_ids,
        String postcode,
        String distance
        ) {
      this.startdate = startdate;
      this.enddate = enddate;
      this.categories = Utils.is_comma_delimited_numbers(categories) ? 
        categories 
        : null;
      this.description = description;
      this.statuses = Utils.is_comma_delimited_numbers(statuses) ? 
        statuses 
        : null;
      this.user_ids = Utils.is_comma_delimited_numbers(user_ids) ? 
        user_ids 
        : null;
      this.postcode = postcode;
      this.distance = Utils.parse_double(distance);
    }

  }

	/**
		* A helper class to get the Requestoffers and page count to their
		* destination.
		*/
	public static class OR_Package {

		private Others_Requestoffer[] or;
		public int page_count;

		public OR_Package(Others_Requestoffer[] or, int page_count) {
			this.or = Arrays.copyOf(or, or.length);
			this.page_count = page_count;
		}

		/**
			* Returns a copy of the Requestoffers array.  Why do this?
			* to stay immutable.  For thread safety and performance.  If
			* I am wrong about that, feel free to reconsider this piece.
			*/
		public Others_Requestoffer[] get_requestoffers() {
			Others_Requestoffer[] oro = Arrays.copyOf(or, or.length);
			return oro;
		}
	}


  /**
    * this gives the "stair step" that the user is at for the
    * value of rank_ladder they have.  It's a way to get a sense
    * of their recent rankings.  See youtrack issue X-172 for more info.
    * @return a value between 0 and 6 if valid input, or -1 otherwise.
    */
  public static int get_ladder_step(int rank_ladder_val) {
    if ( rank_ladder_val < -1 ) {
      return 0;
    }
    if ( rank_ladder_val == -1 ) {
      return 1;
{'fraction_non_alphanumeric': 0.09243768722093483, 'fraction_numerical': 0.00223252133612163, 'mean_word_length': 2.6892201834862384, 'pattern_counts': {'":': 0, '<': 23, '<?xml version=': 0, '>': 23, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4033741', 'n_tokens_mistral': 10859, 'n_tokens_neox': 10316, 'n_words': 2735}