/**
   * Method to get the workgroups to which the user with given username has at least colony manage
   * access.
   *
   * @param userName - the requesters username
   * @return - list of workgroup names
   */
  public List<String> getColonyManageWorkgroups(String userName) {
    List<String> owners = new ArrayList<String>();
    String queryString =
        "SELECT DISTINCT w.WorkgroupName "
            + "FROM Workgroup AS w "
            + "JOIN WorkgroupUser AS wu "
            + "ON wu._Workgroup_key = w._Workgroup_key "
            + "JOIN User AS u "
            + "ON u._User_key = wu._User_key "
            + "JOIN WorkgroupUserFunctionalArea AS wufa "
            + "ON wu._WorkgroupUser_key = wufa._WorkgroupUser_key "
            + "JOIN FunctionalArea AS fa "
            + "ON wufa._FunctionalArea_key = fa._FunctionalArea_key "
            + "WHERE u.UserName = ? "
            + "AND (u.IsActive = 1 OR u.IsActive = -1) "
            + "AND (fa.FunctionalArea = 'ColonyManagement'"
            + "OR fa.FunctionalArea = 'Administration')";

    PreparedStatement query;
    ResultSet rs;
    Result resultData = null;
    Connection con = this.getConnection();
    try {
      // establish connection and initialize prepared statement
      query = con.prepareStatement(queryString);
      // set ? in queryString to userName
      query.setString(1, userName);

      rs = query.executeQuery();

      rs.beforeFirst();

      // Very Very Very Important.  Doesn't convert to Result with out the
      // following line ->  myResult.beforeFirst();
      if (rs != null) {
        resultData = ResultSupport.toResult(rs);
        for (SortedMap workgroup : resultData.getRows()) {
          owners.add(this.myGet("WorkgroupName", workgroup));
        }
      }
    } catch (Exception e) {
      System.out.println("ERROR: " + e);
    }
    return owners;
  }
  public Result servletUserAll(String sql, Object[] object) {

    try {
      conn = Dbhelp.getConnection();
      pst = conn.prepareStatement(sql);
      for (int i = 0; i < object.length; i++) {
        pst.setObject(i + 1, object[i]);
      }

      resultSet = pst.executeQuery();
      result = ResultSupport.toResult(resultSet);
      return result;
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
      return null;
    }
  }
  public Result servletUserALl(String sql) {

    try {
      // 得到一个连接;
      conn = Dbhelp.getConnection();
      // 获得preparedStatement对象;
      pst = conn.prepareStatement(sql);
      // 执行sql语句
      resultSet = pst.executeQuery();
      // 把resutlSet对象转换成result结果集对象;
      result = ResultSupport.toResult(resultSet);
      // 返回结果集
      return result;
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
      return null;
    }
  }
  protected void doGet(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {

    System.out.println(
        "data got frm jsp manger: "
            + request.getParameter("fromdate").toString()
            + "to date: "
            + request.getParameter("todate"));

    try {

      Connection con1 = (Connection) getServletContext().getAttribute("DBConnection");

      PreparedStatement ps = null;
      ResultSet rs = null;

      @SuppressWarnings("unused")
      HttpSession session = request.getSession();

      String frm = request.getParameter("fromdate").toString() + " 00:00:00";
      String to = request.getParameter("todate").toString() + " 00:00:00";

      fromdate = Timestamp.valueOf(frm);
      todate = Timestamp.valueOf(to);

      ps = con1.prepareStatement(mgr_query);
      ps.setTimestamp(1, fromdate);
      ps.setTimestamp(2, todate);
      rs = ps.executeQuery();
      System.out.println("Result" + rs.toString());

      Result result = ResultSupport.toResult(rs);
      request.setAttribute("result", result);
      RequestDispatcher rd = request.getRequestDispatcher("/manager_main.jsp");
      rd.forward(request, response);

    } catch (Exception e) {

      e.printStackTrace();
    }
  }
  /**
   * Method to execute a query against the JCMS database.
   *
   * @param theQuery - the query the user wishes to execute
   * @return - A result for theQuery
   */
  protected Result executeJCMSQuery(String theQuery) {
    Connection con = null;
    ResultSet myResultSet;
    Result myResult;

    try {
      con = this.getConnection();

      // Create the stmt used for updatable result set.
      Statement stmt =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

      myResultSet = stmt.executeQuery(theQuery);

      myResultSet.beforeFirst();
      myResult = ResultSupport.toResult(myResultSet);
      this.closeConnection(con);
      return myResult;
    } catch (SQLException ex) {
      Logger.getLogger(AdministrationDAO.class.getName()).log(Level.SEVERE, null, ex);
      this.closeConnection(con);
      return null;
    }
  }
Example #6
0
  protected void doGet(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {

    int roleInp = Integer.parseInt(request.getParameter("myradio"));
    try {

      Connection con1 = (Connection) getServletContext().getAttribute("DBConnection");

      PreparedStatement ps = null;
      ResultSet rs = null;

      @SuppressWarnings("unused")
      HttpSession session = request.getSession();

      String frm = request.getParameter("fromdate").toString();
      String to = request.getParameter("todate").toString();

      fromdate = Date.valueOf(frm);
      todate = Date.valueOf(to);

      switch (roleInp) {
        case 0:
          ps = con1.prepareStatement(daily_summary);
          ps.setDate(1, fromdate);
          ps.setDate(2, todate);
          rs = ps.executeQuery();
          System.out.println("Daily summary" + rs.toString());
          break;
        case 1:
          ps = con1.prepareStatement(weekly_summary);
          ps.setDate(1, fromdate);
          ps.setDate(2, todate);
          rs = ps.executeQuery();
          System.out.println("weekly summary" + rs.toString());
          break;
        case 2:
          ps = con1.prepareStatement(monthly_summary);
          ps.setDate(1, fromdate);
          ps.setDate(2, todate);
          rs = ps.executeQuery();
          System.out.println("monthly summary" + rs.toString());
          break;
      }

      Result res = ResultSupport.toResult(rs);
      request.setAttribute("summary", res);

      ps = con1.prepareStatement(mgr_query);
      ps.setDate(1, fromdate);
      ps.setDate(2, todate);
      rs = ps.executeQuery();
      System.out.println("Result" + rs.toString());

      Result result = ResultSupport.toResult(rs);
      request.setAttribute("result", result);
      RequestDispatcher rd = request.getRequestDispatcher("/manager_main.jsp");
      rd.forward(request, response);

    } catch (Exception e) {

      e.printStackTrace();
    }
  }