/**
* 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;
}
}
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();
}
}