List of usage examples for java.sql PreparedStatement setBoolean
void setBoolean(int parameterIndex, boolean x) throws SQLException;
boolean value. From source file:org.apache.hadoop.hive.metastore.MyXid.java
@Override public boolean grantAuthSys(String userName, List<String> privileges) throws NoSuchObjectException, InvalidObjectException, MetaException { Connection con = null;// ww w . j a va 2 s. c o m ; PreparedStatement ps = null; boolean success = false; userName = userName.toLowerCase(); try { con = getGlobalConnection(); } catch (MetaStoreConnectException e1) { LOG.error("grant auth sys error , user=" + userName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("grant auth sys error , user=" + userName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } try { con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); ps = con.prepareStatement("select alter_priv,create_priv, createview_priv, dba_priv " + ",delete_priv, drop_priv, index_priv, insert_priv, select_priv, showview_priv" + ",update_priv from tdwuser where user_name=?"); ps.setString(1, userName); boolean isPrivFind = false; boolean selPriv = false; boolean insertPriv = false; boolean createPriv = false; boolean dropPriv = false; boolean deletePriv = false; boolean alterPriv = false; boolean updatePriv = false; boolean indexPriv = false; boolean showViewPriv = false; boolean createViewPriv = false; boolean dbaPriv = false; ResultSet userSet = ps.executeQuery(); while (userSet.next()) { isPrivFind = true; alterPriv = userSet.getBoolean(1); createPriv = userSet.getBoolean(2); createViewPriv = userSet.getBoolean(3); dbaPriv = userSet.getBoolean(4); deletePriv = userSet.getBoolean(5); dropPriv = userSet.getBoolean(6); indexPriv = userSet.getBoolean(7); insertPriv = userSet.getBoolean(8); selPriv = userSet.getBoolean(9); showViewPriv = userSet.getBoolean(10); updatePriv = userSet.getBoolean(11); break; } userSet.close(); ps.close(); if (!isPrivFind) { throw new NoSuchObjectException("can not find user:" + userName); } for (String priv : privileges) { if (priv.equals("TOK_SELECT_PRI")) { selPriv = true; } else if (priv.equals("TOK_INSERT_PRI")) { insertPriv = true; } else if (priv.equals("TOK_CREATE_PRI")) { createPriv = true; } else if (priv.equals("TOK_DROP_PRI")) { dropPriv = true; } else if (priv.equals("TOK_DELETE_PRI")) { deletePriv = true; } else if (priv.equals("TOK_ALTER_PRI")) { alterPriv = true; } else if (priv.equals("TOK_UPDATE_PRI")) { updatePriv = true; } else if (priv.equals("TOK_INDEX_PRI")) { indexPriv = true; } else if (priv.equals("TOK_CREATEVIEW_PRI")) { createViewPriv = true; } else if (priv.equals("TOK_SHOWVIEW_PRI")) { showViewPriv = true; } else if (priv.equals("TOK_DBA_PRI")) { dbaPriv = true; } else if (priv.equals("TOK_ALL_PRI")) { selPriv = true; insertPriv = true; createPriv = true; dropPriv = true; deletePriv = true; alterPriv = true; updatePriv = true; indexPriv = true; createViewPriv = true; showViewPriv = true; } else { throw new InvalidObjectException("Privilege does not exist: " + priv); } } ps = con.prepareStatement( "update tdwuser set alter_priv=?, create_priv=?, createview_priv=?, dba_priv=?," + " delete_priv=?, drop_priv=?, index_priv=?, insert_priv=?, select_priv=?, showview_priv=?," + " update_priv=? where user_name=?"); ps.setBoolean(1, alterPriv); ps.setBoolean(2, createPriv); ps.setBoolean(3, createViewPriv); ps.setBoolean(4, dbaPriv); ps.setBoolean(5, deletePriv); ps.setBoolean(6, dropPriv); ps.setBoolean(7, indexPriv); ps.setBoolean(8, insertPriv); ps.setBoolean(9, selPriv); ps.setBoolean(10, showViewPriv); ps.setBoolean(11, updatePriv); ps.setString(12, userName); ps.executeUpdate(); con.commit(); success = true; } catch (SQLException ex) { LOG.error("grant auth sys error , user=" + userName + ", msg=" + ex.getMessage()); throw new MetaException(ex.getMessage()); } finally { if (!success) { try { con.rollback(); } catch (SQLException e) { } } closeStatement(ps); closeConnection(con); } return success; }
From source file:org.apache.hadoop.hive.metastore.MyXid.java
@Override public boolean revokeAuthRoleSys(String role, List<String> privileges) throws NoSuchObjectException, InvalidObjectException, MetaException { Connection con = null;// ww w.j a v a2 s . com ; PreparedStatement ps = null; boolean success = false; role = role.toLowerCase(); try { con = getGlobalConnection(); } catch (MetaStoreConnectException e1) { LOG.error("revoke role auth from user error , user=" + role + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("revoke role auth from user error , user=" + role + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } try { con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); ps = con.prepareStatement("select alter_priv,create_priv, createview_priv, dba_priv " + ",delete_priv, drop_priv, index_priv, insert_priv, select_priv, showview_priv" + ",update_priv from tdwrole where role_name=?"); ps.setString(1, role); boolean isPrivFind = false; boolean selPriv = false; boolean insertPriv = false; boolean createPriv = false; boolean dropPriv = false; boolean deletePriv = false; boolean alterPriv = false; boolean updatePriv = false; boolean indexPriv = false; boolean showViewPriv = false; boolean createViewPriv = false; boolean dbaPriv = false; ResultSet userSet = ps.executeQuery(); while (userSet.next()) { isPrivFind = true; alterPriv = userSet.getBoolean(1); createPriv = userSet.getBoolean(2); createViewPriv = userSet.getBoolean(3); dbaPriv = userSet.getBoolean(4); deletePriv = userSet.getBoolean(5); dropPriv = userSet.getBoolean(6); indexPriv = userSet.getBoolean(7); insertPriv = userSet.getBoolean(8); selPriv = userSet.getBoolean(9); showViewPriv = userSet.getBoolean(10); updatePriv = userSet.getBoolean(11); break; } userSet.close(); ps.close(); if (!isPrivFind) { throw new NoSuchObjectException("can not find user:" + role); } for (String priv : privileges) { if (priv.equals("TOK_SELECT_PRI")) { selPriv = false; } else if (priv.equals("TOK_INSERT_PRI")) { insertPriv = false; } else if (priv.equals("TOK_CREATE_PRI")) { createPriv = false; } else if (priv.equals("TOK_DROP_PRI")) { dropPriv = false; } else if (priv.equals("TOK_DELETE_PRI")) { deletePriv = false; } else if (priv.equals("TOK_ALTER_PRI")) { alterPriv = false; } else if (priv.equals("TOK_UPDATE_PRI")) { updatePriv = false; } else if (priv.equals("TOK_INDEX_PRI")) { indexPriv = false; } else if (priv.equals("TOK_CREATEVIEW_PRI")) { createViewPriv = false; } else if (priv.equals("TOK_SHOWVIEW_PRI")) { showViewPriv = false; } else if (priv.equals("TOK_DBA_PRI")) { dbaPriv = false; } else if (priv.equals("TOK_ALL_PRI")) { selPriv = false; insertPriv = false; createPriv = false; dropPriv = false; deletePriv = false; alterPriv = false; updatePriv = false; indexPriv = false; createViewPriv = false; showViewPriv = false; } else { throw new InvalidObjectException("Privilege does not exist: " + priv); } } ps = con.prepareStatement( "update tdwrole set alter_priv=?, create_priv=?, createview_priv=?, dba_priv=?," + " delete_priv=?, drop_priv=?, index_priv=?, insert_priv=?, select_priv=?, showview_priv=?," + " update_priv=? where role_name=?"); ps.setBoolean(1, alterPriv); ps.setBoolean(2, createPriv); ps.setBoolean(3, createViewPriv); ps.setBoolean(4, dbaPriv); ps.setBoolean(5, deletePriv); ps.setBoolean(6, dropPriv); ps.setBoolean(7, indexPriv); ps.setBoolean(8, insertPriv); ps.setBoolean(9, selPriv); ps.setBoolean(10, showViewPriv); ps.setBoolean(11, updatePriv); ps.setString(12, role); ps.executeUpdate(); con.commit(); success = true; } catch (SQLException ex) { LOG.error("revoke auth from role error , user=" + role + ", msg=" + ex.getMessage()); ex.printStackTrace(); throw new MetaException(ex.getMessage()); } finally { if (!success) { try { con.rollback(); } catch (SQLException e) { } } closeStatement(ps); closeConnection(con); } return success; }
From source file:org.apache.hadoop.hive.metastore.MyXid.java
@Override public boolean revokeAuthSys(String userName, List<String> privileges) throws NoSuchObjectException, InvalidObjectException, MetaException { Connection con = null;/*from ww w . j ava 2 s . c o m*/ ; PreparedStatement ps = null; boolean success = false; userName = userName.toLowerCase(); try { con = getGlobalConnection(); } catch (MetaStoreConnectException e1) { LOG.error("revoke auth from user error , user=" + userName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("revoke auth from user error , user=" + userName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } try { con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); ps = con.prepareStatement("select alter_priv,create_priv, createview_priv, dba_priv " + ",delete_priv, drop_priv, index_priv, insert_priv, select_priv, showview_priv" + ",update_priv from tdwuser where user_name=?"); ps.setString(1, userName); boolean isPrivFind = false; boolean selPriv = false; boolean insertPriv = false; boolean createPriv = false; boolean dropPriv = false; boolean deletePriv = false; boolean alterPriv = false; boolean updatePriv = false; boolean indexPriv = false; boolean showViewPriv = false; boolean createViewPriv = false; boolean dbaPriv = false; ResultSet userSet = ps.executeQuery(); while (userSet.next()) { isPrivFind = true; alterPriv = userSet.getBoolean(1); createPriv = userSet.getBoolean(2); createViewPriv = userSet.getBoolean(3); dbaPriv = userSet.getBoolean(4); deletePriv = userSet.getBoolean(5); dropPriv = userSet.getBoolean(6); indexPriv = userSet.getBoolean(7); insertPriv = userSet.getBoolean(8); selPriv = userSet.getBoolean(9); showViewPriv = userSet.getBoolean(10); updatePriv = userSet.getBoolean(11); break; } userSet.close(); ps.close(); if (!isPrivFind) { throw new NoSuchObjectException("can not find user:" + userName); } for (String priv : privileges) { if (priv.equals("TOK_SELECT_PRI")) { selPriv = false; } else if (priv.equals("TOK_INSERT_PRI")) { insertPriv = false; } else if (priv.equals("TOK_CREATE_PRI")) { createPriv = false; } else if (priv.equals("TOK_DROP_PRI")) { dropPriv = false; } else if (priv.equals("TOK_DELETE_PRI")) { deletePriv = false; } else if (priv.equals("TOK_ALTER_PRI")) { alterPriv = false; } else if (priv.equals("TOK_UPDATE_PRI")) { updatePriv = false; } else if (priv.equals("TOK_INDEX_PRI")) { indexPriv = false; } else if (priv.equals("TOK_CREATEVIEW_PRI")) { createViewPriv = false; } else if (priv.equals("TOK_SHOWVIEW_PRI")) { showViewPriv = false; } else if (priv.equals("TOK_DBA_PRI")) { dbaPriv = false; } else if (priv.equals("TOK_ALL_PRI")) { selPriv = false; insertPriv = false; createPriv = false; dropPriv = false; deletePriv = false; alterPriv = false; updatePriv = false; indexPriv = false; createViewPriv = false; showViewPriv = false; } else { throw new InvalidObjectException("Privilege does not exist: " + priv); } } ps = con.prepareStatement( "update tdwuser set alter_priv=?, create_priv=?, createview_priv=?, dba_priv=?," + " delete_priv=?, drop_priv=?, index_priv=?, insert_priv=?, select_priv=?, showview_priv=?," + " update_priv=? where user_name=?"); ps.setBoolean(1, alterPriv); ps.setBoolean(2, createPriv); ps.setBoolean(3, createViewPriv); ps.setBoolean(4, dbaPriv); ps.setBoolean(5, deletePriv); ps.setBoolean(6, dropPriv); ps.setBoolean(7, indexPriv); ps.setBoolean(8, insertPriv); ps.setBoolean(9, selPriv); ps.setBoolean(10, showViewPriv); ps.setBoolean(11, updatePriv); ps.setString(12, userName); ps.executeUpdate(); con.commit(); success = true; } catch (SQLException ex) { LOG.error("revoke auth from user error , user=" + userName + ", msg=" + ex.getMessage()); ex.printStackTrace(); throw new MetaException(ex.getMessage()); } finally { if (!success) { try { con.rollback(); } catch (SQLException e) { } } closeStatement(ps); closeConnection(con); } return success; }
From source file:org.apache.hadoop.hive.metastore.MyXid.java
@Override public boolean revokeAuthOnDb(String who, List<String> privileges, String db) throws NoSuchObjectException, InvalidObjectException, MetaException { Connection con = null;// w ww . j a v a 2s. c o m ; PreparedStatement ps = null; boolean success = false; success = false; who = who.toLowerCase(); db = db.toLowerCase(); try { con = getGlobalConnection(); } catch (MetaStoreConnectException e1) { LOG.error("revoke auth on db error, who=" + who + ", db=" + db + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("revoke auth on db error, who=" + who + ", db=" + db + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } try { con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); ps = con.prepareStatement("select db_name from router where db_name=?"); ps.setString(1, db.toLowerCase()); boolean isDbfind = false; ResultSet dbSet = ps.executeQuery(); while (dbSet.next()) { isDbfind = true; break; } dbSet.close(); if (!isDbfind) { LOG.error("revoke auth on db error, who=" + who + ", db=" + db); throw new NoSuchObjectException("can not find db:" + db); } ps = con.prepareStatement("select alter_priv, create_priv, createview_priv, " + " delete_priv, drop_priv, index_priv, insert_priv, select_priv, showview_priv," + " update_priv from dbpriv where user_name=? and db_name=?"); ps.setString(1, who); ps.setString(2, db); boolean isPrivFind = false; boolean selPriv = false; boolean insertPriv = false; boolean createPriv = false; boolean dropPriv = false; boolean deletePriv = false; boolean alterPriv = false; boolean updatePriv = false; boolean indexPriv = false; boolean showViewPriv = false; boolean createViewPriv = false; ResultSet privSet = ps.executeQuery(); while (privSet.next()) { isPrivFind = true; alterPriv = privSet.getBoolean(1); createPriv = privSet.getBoolean(2); createViewPriv = privSet.getBoolean(3); deletePriv = privSet.getBoolean(4); dropPriv = privSet.getBoolean(5); indexPriv = privSet.getBoolean(6); insertPriv = privSet.getBoolean(7); selPriv = privSet.getBoolean(8); showViewPriv = privSet.getBoolean(9); updatePriv = privSet.getBoolean(10); break; } privSet.close(); ps.close(); if (!isPrivFind) { LOG.error("revoke auth on db error, who=" + who + ", db=" + db); throw new NoSuchObjectException("User " + who + " does not have privileges on db: " + db); } if (privileges == null) { LOG.error("revoke auth on db error, who=" + who + ", db=" + db); throw new InvalidObjectException("No privileges are given!"); } for (String priv : privileges) { if (priv.equals("TOK_SELECT_PRI")) { selPriv = false; } else if (priv.equals("TOK_INSERT_PRI")) { insertPriv = false; } else if (priv.equals("TOK_CREATE_PRI")) { createPriv = false; } else if (priv.equals("TOK_DROP_PRI")) { dropPriv = false; } else if (priv.equals("TOK_DELETE_PRI")) { deletePriv = false; } else if (priv.equals("TOK_ALTER_PRI")) { alterPriv = false; } else if (priv.equals("TOK_UPDATE_PRI")) { updatePriv = false; } else if (priv.equals("TOK_INDEX_PRI")) { indexPriv = false; } else if (priv.equals("TOK_CREATEVIEW_PRI")) { createViewPriv = false; } else if (priv.equals("TOK_SHOWVIEW_PRI")) { showViewPriv = false; } else if (priv.equals("TOK_ALL_PRI")) { selPriv = false; insertPriv = false; createPriv = false; dropPriv = false; deletePriv = false; alterPriv = false; updatePriv = false; indexPriv = false; createViewPriv = false; showViewPriv = false; } else { LOG.error("revoke auth on db error, who=" + who + ", db=" + db); throw new InvalidObjectException("Privilege does not exist: " + priv); } } ps = con.prepareStatement("update dbpriv set alter_priv=?, create_priv=?, createview_priv=?, " + " delete_priv=?, drop_priv=?, index_priv=?, insert_priv=?, select_priv=?, showview_priv=?," + " update_priv=? where user_name=? and db_name=? "); ps.setBoolean(1, alterPriv); ps.setBoolean(2, createPriv); ps.setBoolean(3, createViewPriv); ps.setBoolean(4, deletePriv); ps.setBoolean(5, dropPriv); ps.setBoolean(6, indexPriv); ps.setBoolean(7, insertPriv); ps.setBoolean(8, selPriv); ps.setBoolean(9, showViewPriv); ps.setBoolean(10, updatePriv); ps.setString(11, who); ps.setString(12, db); ps.executeUpdate(); con.commit(); success = true; } catch (SQLException ex) { LOG.error("revoke auth on db error, who=" + who + ", db=" + db); ex.printStackTrace(); throw new MetaException(ex.getMessage()); } finally { if (!success) { try { con.rollback(); } catch (SQLException e) { } } closeStatement(ps); closeConnection(con); } return success; }
From source file:org.apache.hadoop.hive.metastore.MyXid.java
@Override public boolean grantAuthOnDb(String forWho, List<String> privileges, String db) throws NoSuchObjectException, InvalidObjectException, MetaException { Connection con = null;/*from ww w .ja v a 2s. com*/ Statement ps = null; boolean success = false; PreparedStatement pss = null; forWho = forWho.toLowerCase(); db = db.toLowerCase(); try { con = getSegmentConnection(db); } catch (MetaStoreConnectException e1) { LOG.error("grant auth on db error, db=" + db + ", forwho=" + forWho + ", msg=" + e1.getMessage()); throw new MetaException("can not find db:" + db); } catch (SQLException e1) { LOG.error("grant auth on db error, db=" + db + ", forwho=" + forWho + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } if (privileges == null) { throw new InvalidObjectException("No privileges are given!"); } try { ps = con.createStatement(); String sql = "select name from dbs where name='" + db + "'"; boolean isDbfind = false; ResultSet dbSet = ps.executeQuery(sql); while (dbSet.next()) { isDbfind = true; break; } dbSet.close(); if (!isDbfind) { throw new NoSuchObjectException("can not find db:" + db); } } catch (SQLException ex) { LOG.error("grant auth on db error, db=" + db + ", forwho=" + forWho + ", msg=" + ex.getMessage()); ex.printStackTrace(); throw new MetaException(ex.getMessage()); } finally { closeStatement(ps); closeConnection(con); } try { con = getGlobalConnection(); } catch (MetaStoreConnectException e1) { LOG.error("grant auth on db error, db=" + db + ", forwho=" + forWho + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("grant auth on db error, db=" + db + ", forwho=" + forWho + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } try { con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); ps = con.createStatement(); String sql = "select alter_priv,create_priv,createview_priv, " + " delete_priv, drop_priv, index_priv, insert_priv, select_priv, showview_priv," + " update_priv from dbpriv where user_name='" + forWho + "' and db_name='" + db + "'"; boolean isPrivFind = false; boolean selPriv = false; boolean insertPriv = false; boolean createPriv = false; boolean dropPriv = false; boolean deletePriv = false; boolean alterPriv = false; boolean updatePriv = false; boolean indexPriv = false; boolean showViewPriv = false; boolean createViewPriv = false; ResultSet privSet = ps.executeQuery(sql); while (privSet.next()) { isPrivFind = true; alterPriv = privSet.getBoolean(1); createPriv = privSet.getBoolean(2); createViewPriv = privSet.getBoolean(3); deletePriv = privSet.getBoolean(4); dropPriv = privSet.getBoolean(5); indexPriv = privSet.getBoolean(6); insertPriv = privSet.getBoolean(7); selPriv = privSet.getBoolean(8); showViewPriv = privSet.getBoolean(9); updatePriv = privSet.getBoolean(10); break; } privSet.close(); for (String priv : privileges) { if (priv.equals("TOK_SELECT_PRI")) { selPriv = true; } else if (priv.equals("TOK_INSERT_PRI")) { insertPriv = true; } else if (priv.equals("TOK_CREATE_PRI")) { createPriv = true; } else if (priv.equals("TOK_DROP_PRI")) { dropPriv = true; } else if (priv.equals("TOK_DELETE_PRI")) { deletePriv = true; } else if (priv.equals("TOK_ALTER_PRI")) { alterPriv = true; } else if (priv.equals("TOK_UPDATE_PRI")) { updatePriv = true; } else if (priv.equals("TOK_INDEX_PRI")) { indexPriv = true; } else if (priv.equals("TOK_CREATEVIEW_PRI")) { createViewPriv = true; } else if (priv.equals("TOK_SHOWVIEW_PRI")) { showViewPriv = true; } else if (priv.equals("TOK_ALL_PRI")) { selPriv = true; insertPriv = true; createPriv = true; dropPriv = true; deletePriv = true; alterPriv = true; updatePriv = true; indexPriv = true; createViewPriv = true; showViewPriv = true; } else throw new InvalidObjectException("Privilege does not exist: " + priv); } if (!isPrivFind) { pss = con.prepareStatement("insert into dbpriv(alter_priv, create_priv, createview_priv," + "delete_priv, drop_priv, index_priv, insert_priv, select_priv" + ", showview_priv, update_priv, user_name, db_name) values(?,?,?,?,?,?,?,?,?,?,?,?)"); pss.setBoolean(1, alterPriv); pss.setBoolean(2, createPriv); pss.setBoolean(3, createViewPriv); pss.setBoolean(4, deletePriv); pss.setBoolean(5, dropPriv); pss.setBoolean(6, indexPriv); pss.setBoolean(7, insertPriv); pss.setBoolean(8, selPriv); pss.setBoolean(9, showViewPriv); pss.setBoolean(10, updatePriv); pss.setString(11, forWho); pss.setString(12, db); pss.executeUpdate(); pss.close(); } else { pss = con.prepareStatement("update dbpriv set alter_priv=?, create_priv=?, createview_priv=?, " + " delete_priv=?, drop_priv=?, index_priv=?, insert_priv=?, select_priv=?, showview_priv=?," + " update_priv=? where user_name=? and db_name=?"); pss.setBoolean(1, alterPriv); pss.setBoolean(2, createPriv); pss.setBoolean(3, createViewPriv); pss.setBoolean(4, deletePriv); pss.setBoolean(5, dropPriv); pss.setBoolean(6, indexPriv); pss.setBoolean(7, insertPriv); pss.setBoolean(8, selPriv); pss.setBoolean(9, showViewPriv); pss.setBoolean(10, updatePriv); pss.setString(11, forWho); pss.setString(12, db); pss.executeUpdate(); pss.close(); } con.commit(); success = true; } catch (SQLException ex) { LOG.error("grant auth on db error, db=" + db + ", forwho=" + forWho + ", msg=" + ex.getMessage()); ex.printStackTrace(); throw new MetaException(ex.getMessage()); } finally { if (!success) { try { con.rollback(); } catch (SQLException e) { } } closeStatement(ps); closeStatement(pss); closeConnection(con); } return success; }
From source file:org.apache.hadoop.hive.metastore.MyXid.java
@Override public boolean revokeAuthOnTbl(String who, List<String> privileges, String db, String tbl) throws NoSuchObjectException, InvalidObjectException, MetaException { Connection con = null;//w ww . j a v a2 s .co m ; Statement ps = null; boolean success = false; PreparedStatement pss = null; who = who.toLowerCase(); db = db.toLowerCase(); tbl = tbl.toLowerCase(); if (privileges == null) { throw new InvalidObjectException("No privileges are given!"); } try { con = getGlobalConnection(); } catch (MetaStoreConnectException e1) { LOG.error("revoke auth on tbl error, who=" + who + ", db=" + db + ", tbl=" + tbl + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("revoke auth on tbl error, who=" + who + ", db=" + db + ", tbl=" + tbl + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } try { Map<String, TblPrivDesc> tblPrivMap = new HashMap<String, TblPrivDesc>(); con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); ps = con.createStatement(); String sql = null; StringBuilder sb = new StringBuilder(); int size = tbl.length(); for (int i = 0; i < size; i++) { if (tbl.charAt(i) != '\'') { sb.append(tbl.charAt(i)); } } tbl = sb.toString(); tbl = tbl.replace('*', '%'); if (tbl == null || tbl.isEmpty() || tbl.equals(".*") || tbl.equals("*")) { sql = "select alter_priv, create_priv, delete_priv " + ",drop_priv, index_priv, insert_priv, select_priv, update_priv, tbl_name" + " from tblpriv where user_name='" + who + "' and db_name='" + db + "'"; } else { sql = "select alter_priv, create_priv, delete_priv " + ",drop_priv, index_priv, insert_priv, select_priv, update_priv, tbl_name" + " from tblpriv where user_name='" + who + "' and db_name='" + db + "' and tbl_name like '" + tbl + "'"; } ResultSet privSet = ps.executeQuery(sql); while (privSet.next()) { TblPrivDesc privDesc = new TblPrivDesc(); privDesc.alterPriv = privSet.getBoolean(1); privDesc.createPriv = privSet.getBoolean(2); privDesc.deletePriv = privSet.getBoolean(3); privDesc.dropPriv = privSet.getBoolean(4); privDesc.indexPriv = privSet.getBoolean(5); privDesc.insertPriv = privSet.getBoolean(6); privDesc.selPriv = privSet.getBoolean(7); privDesc.updatePriv = privSet.getBoolean(8); String tblName = privSet.getString(9); tblPrivMap.put(tblName, privDesc); } privSet.close(); if (tblPrivMap.isEmpty()) { LOG.error("revoke auth on tbl error, who=" + who + ", db=" + db + ", tbl=" + tbl); throw new NoSuchObjectException( "User " + who + " does not have privileges on table: " + tbl + " in db: " + db); } boolean selPriv = true; boolean insertPriv = true; boolean createPriv = true; boolean dropPriv = true; boolean deletePriv = true; boolean alterPriv = true; boolean updatePriv = true; boolean indexPriv = true; for (String priv : privileges) { if (priv.equals("TOK_SELECT_PRI")) { selPriv = false; } else if (priv.equals("TOK_INSERT_PRI")) { insertPriv = false; } else if (priv.equals("TOK_CREATE_PRI")) { createPriv = false; } else if (priv.equals("TOK_DROP_PRI")) { dropPriv = false; } else if (priv.equals("TOK_DELETE_PRI")) { deletePriv = false; } else if (priv.equals("TOK_ALTER_PRI")) { alterPriv = false; } else if (priv.equals("TOK_UPDATE_PRI")) { updatePriv = false; } else if (priv.equals("TOK_INDEX_PRI")) { indexPriv = false; } else if (priv.equals("TOK_ALL_PRI")) { selPriv = false; insertPriv = false; createPriv = false; dropPriv = false; deletePriv = false; alterPriv = false; updatePriv = false; indexPriv = false; } else { throw new InvalidObjectException("Privilege does not exist: " + priv); } } Collection<TblPrivDesc> tblPrivColl = tblPrivMap.values(); if (!alterPriv) { for (TblPrivDesc entry : tblPrivColl) { entry.alterPriv = false; } } if (!createPriv) { for (TblPrivDesc entry : tblPrivColl) { entry.createPriv = false; } } if (!deletePriv) { for (TblPrivDesc entry : tblPrivColl) { entry.deletePriv = false; } } if (!dropPriv) { for (TblPrivDesc entry : tblPrivColl) { entry.dropPriv = false; } } if (!indexPriv) { for (TblPrivDesc entry : tblPrivColl) { entry.indexPriv = false; } } if (!insertPriv) { for (TblPrivDesc entry : tblPrivColl) { entry.insertPriv = false; } } if (!selPriv) { for (TblPrivDesc entry : tblPrivColl) { entry.selPriv = false; } } if (!updatePriv) { for (TblPrivDesc entry : tblPrivColl) { entry.updatePriv = false; } } pss = con.prepareStatement("update tblpriv set alter_priv=?, create_priv=?, " + " delete_priv=?, drop_priv=?, index_priv=?, insert_priv=?, select_priv=?," + " update_priv=? where user_name=? and db_name=? and tbl_name=?"); for (Entry<String, TblPrivDesc> entry : tblPrivMap.entrySet()) { pss.setBoolean(1, entry.getValue().alterPriv); pss.setBoolean(2, entry.getValue().createPriv); pss.setBoolean(3, entry.getValue().deletePriv); pss.setBoolean(4, entry.getValue().dropPriv); pss.setBoolean(5, entry.getValue().indexPriv); pss.setBoolean(6, entry.getValue().insertPriv); pss.setBoolean(7, entry.getValue().selPriv); pss.setBoolean(8, entry.getValue().updatePriv); pss.setString(9, who); pss.setString(10, db); pss.setString(11, entry.getKey()); pss.addBatch(); } pss.executeBatch(); con.commit(); success = true; } catch (SQLException ex) { LOG.error("revoke auth on tbl error, who=" + who + ", db=" + db + ", tbl=" + tbl + ", msg=" + ex.getMessage()); ex.printStackTrace(); throw new MetaException(ex.getMessage()); } finally { if (!success) { try { con.rollback(); } catch (SQLException e) { } } closeStatement(ps); closeStatement(pss); closeConnection(con); } return success; }
From source file:org.apache.hadoop.hive.metastore.MyXid.java
@Override public boolean grantAuthOnTbl(String forWho, List<String> privileges, String db, String tbl) throws NoSuchObjectException, InvalidObjectException, MetaException { Connection con = null;//from ww w . j a v a 2s. co m ; Statement ps = null; boolean success = false; PreparedStatement pss = null; success = false; forWho = forWho.toLowerCase(); db = db.toLowerCase(); tbl = tbl.toLowerCase(); if (privileges == null) { throw new InvalidObjectException("No privileges are given!"); } try { con = getSegmentConnection(db); } catch (MetaStoreConnectException e1) { LOG.error("grant auth on db error, forWho=" + forWho + ", db=" + db + ", tbl=" + tbl + ", msg=" + e1.getMessage()); throw new MetaException("can not find db:" + db); } catch (SQLException e1) { LOG.error("grant auth on db error, forWho=" + forWho + ", db=" + db + ", tbl=" + tbl + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } Set<String> tblNames = new HashSet<String>(); try { ps = con.createStatement(); String sql = null; StringBuilder sb = new StringBuilder(); int size = tbl.length(); for (int i = 0; i < size; i++) { if (tbl.charAt(i) != '\'') { sb.append(tbl.charAt(i)); } } tbl = sb.toString(); if (tbl == null || tbl.isEmpty() || tbl.equals(".*") || tbl.equals("*")) { sql = "select tbl_name from tbls" + " where tbls.db_name='" + db.toLowerCase() + "'"; } else { tbl = tbl.replace('*', '%'); sql = "select tbl_name from tbls" + " where tbls.db_name='" + db + "' and tbls.tbl_name like '" + tbl + "'"; } LOG.debug("SQL is " + sql); ResultSet tblSet = ps.executeQuery(sql); while (tblSet.next()) { String tblName = tblSet.getString(1); tblNames.add(tblName); } tblSet.close(); con.commit(); success = true; } catch (SQLException ex) { ex.printStackTrace(); LOG.error("grant auth on db error, forWho=" + forWho + ", db=" + db + ", tbl=" + tbl + ", msg=" + ex.getMessage()); LOG.error(ex.getMessage()); } finally { closeStatement(ps); closeConnection(con); } if (tblNames.isEmpty()) { throw new NoSuchObjectException("Table does not exist: " + tbl + " in db: " + db); } try { con = getGlobalConnection(); } catch (MetaStoreConnectException e1) { LOG.error("grant auth on db error, forWho=" + forWho + ", db=" + db + ", tbl=" + tbl + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("grant auth on db error, forWho=" + forWho + ", db=" + db + ", tbl=" + tbl + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } try { con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); ps = con.createStatement(); String sql = null; StringBuilder sb = new StringBuilder(); int size = tbl.length(); for (int i = 0; i < size; i++) { if (tbl.charAt(i) != '\'') { sb.append(tbl.charAt(i)); } } tbl = sb.toString(); if (tbl == null || tbl.isEmpty() || tbl.equals(".*") || tbl.equals("*")) { sql = "select tbl_name, alter_priv, create_priv, delete_priv " + ",drop_priv, index_priv, insert_priv, select_priv, " + " update_priv from tblpriv" + " where db_name='" + db.toLowerCase() + "' and user_name='" + forWho + "'"; } else { tbl = tbl.replace('*', '%'); sql = "select tbl_name, alter_priv, create_priv, delete_priv " + ",drop_priv, index_priv, insert_priv, select_priv, " + " update_priv from tblpriv" + " where db_name='" + db + "' and tbl_name like '" + tbl + "'" + " and user_name='" + forWho + "'"; } LOG.debug("SQL is " + sql); ResultSet tblSet = ps.executeQuery(sql); Map<String, TblPrivDesc> tblPrivMap = new HashMap<String, TblPrivDesc>(); while (tblSet.next()) { String tblName = tblSet.getString(1); TblPrivDesc privDesc = new TblPrivDesc(); privDesc.alterPriv = tblSet.getBoolean(2); privDesc.createPriv = tblSet.getBoolean(3); privDesc.deletePriv = tblSet.getBoolean(4); privDesc.dropPriv = tblSet.getBoolean(5); privDesc.indexPriv = tblSet.getBoolean(6); privDesc.insertPriv = tblSet.getBoolean(7); privDesc.selPriv = tblSet.getBoolean(8); privDesc.updatePriv = tblSet.getBoolean(9); tblPrivMap.put(tblName, privDesc); } tblSet.close(); boolean selPriv = false; boolean insertPriv = false; boolean createPriv = false; boolean dropPriv = false; boolean deletePriv = false; boolean alterPriv = false; boolean updatePriv = false; boolean indexPriv = false; for (String priv : privileges) { if (priv.equals("TOK_SELECT_PRI")) { selPriv = true; } else if (priv.equals("TOK_INSERT_PRI")) { insertPriv = true; } else if (priv.equals("TOK_CREATE_PRI")) { createPriv = true; } else if (priv.equals("TOK_DROP_PRI")) { dropPriv = true; } else if (priv.equals("TOK_DELETE_PRI")) { deletePriv = true; } else if (priv.equals("TOK_ALTER_PRI")) { alterPriv = true; } else if (priv.equals("TOK_UPDATE_PRI")) { updatePriv = true; } else if (priv.equals("TOK_INDEX_PRI")) { indexPriv = true; } else if (priv.equals("TOK_ALL_PRI")) { selPriv = true; insertPriv = true; createPriv = true; dropPriv = true; deletePriv = true; alterPriv = true; updatePriv = true; indexPriv = true; } else { throw new InvalidObjectException("Privilege does not exist: " + priv); } } if (!tblPrivMap.isEmpty()) { Collection<TblPrivDesc> tblPrivColl = tblPrivMap.values(); if (alterPriv) { for (TblPrivDesc entry : tblPrivColl) { entry.alterPriv = true; } } if (createPriv) { for (TblPrivDesc entry : tblPrivColl) { entry.createPriv = true; } } if (deletePriv) { for (TblPrivDesc entry : tblPrivColl) { entry.deletePriv = true; } } if (dropPriv) { for (TblPrivDesc entry : tblPrivColl) { entry.dropPriv = true; } } if (indexPriv) { for (TblPrivDesc entry : tblPrivColl) { entry.indexPriv = true; } } if (insertPriv) { for (TblPrivDesc entry : tblPrivColl) { entry.insertPriv = true; } } if (selPriv) { for (TblPrivDesc entry : tblPrivColl) { entry.selPriv = true; } } if (updatePriv) { for (TblPrivDesc entry : tblPrivColl) { entry.updatePriv = true; } } pss = con.prepareStatement("update tblpriv set alter_priv=?, create_priv=?, " + " delete_priv=?, drop_priv=?, index_priv=?, insert_priv=?, select_priv=?," + " update_priv=? where user_name=? and db_name=? and tbl_name=? "); for (Entry<String, TblPrivDesc> entry : tblPrivMap.entrySet()) { pss.setBoolean(1, entry.getValue().alterPriv); pss.setBoolean(2, entry.getValue().createPriv); pss.setBoolean(3, entry.getValue().deletePriv); pss.setBoolean(4, entry.getValue().dropPriv); pss.setBoolean(5, entry.getValue().indexPriv); pss.setBoolean(6, entry.getValue().insertPriv); pss.setBoolean(7, entry.getValue().selPriv); pss.setBoolean(8, entry.getValue().updatePriv); pss.setString(9, forWho); pss.setString(10, db); pss.setString(11, entry.getKey()); pss.addBatch(); } pss.executeBatch(); } pss = con.prepareStatement("insert into tblpriv(alter_priv, create_priv," + "delete_priv, drop_priv, index_priv, insert_priv, select_priv," + " update_priv, user_name, db_name, tbl_name) values(?,?,?,?,?,?,?,?,?,?,?)"); int needInsertCount = 0; for (String tblName : tblNames) { if (!tblPrivMap.containsKey(tblName)) { pss.setBoolean(1, alterPriv); pss.setBoolean(2, createPriv); pss.setBoolean(3, deletePriv); pss.setBoolean(4, dropPriv); pss.setBoolean(5, indexPriv); pss.setBoolean(6, insertPriv); pss.setBoolean(7, selPriv); pss.setBoolean(8, updatePriv); pss.setString(9, forWho); pss.setString(10, db); pss.setString(11, tblName); pss.addBatch(); needInsertCount++; } } if (needInsertCount > 0) { pss.executeBatch(); } con.commit(); success = true; } catch (SQLException ex) { ex.printStackTrace(); LOG.error("grant auth on db error, forWho=" + forWho + ", db=" + db + ", tbl=" + tbl + ", msg=" + ex.getMessage()); LOG.error(ex.getMessage()); } finally { if (!success) { try { con.rollback(); } catch (SQLException e) { } } closeStatement(ps); closeStatement(pss); closeConnection(con); } return success; }
From source file:com.krawler.esp.servlets.deskeramob.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods. * @param request servlet request//from ww w .j ava 2s.c o m * @param response servlet response */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SessionExpiredException { response.setContentType("text/html;charset=UTF-8"); ResultSet rs = null; ResultSet rsForSubQ = null; PreparedStatement pstmt = null; String result = ""; Connection conn = null; try { conn = DbPool.getConnection(); int action = Integer.parseInt(request.getParameter("action")); int mode = Integer.parseInt(request.getParameter("mode")); switch (action) { case 0: // generate application id String u = request.getParameter("u"); String p = request.getParameter("p"); String d = request.getParameter("d"); String udid = request.getParameter("udid"); result = generateAppID(conn, u, p, d, udid); break; case 1: // dashboard request int limit = 15, offset = 0; String userid = getUserid(conn, request.getParameter("applicationid").toString()); String projectlist = DashboardHandler.getProjectList(conn, userid, 1000, 0, ""); JSONArray projList = null; try { JSONObject projListObj = new JSONObject(projectlist); projList = projListObj.getJSONArray("data"); } catch (JSONException ex) { result = "{\"data\":[{\"success\":false,\"data\":" + ex.getMessage() + "}]}"; } switch (mode) { case 1: // due tasks try { PreparedStatement pstmt1 = null; JSONObject jobj = new JSONObject(); String query = "Select count(post_id) AS count from mailmessages inner join users " + "on users.userid = mailmessages.poster_id where folder = ? and to_id = ? and readflag = false ORDER BY post_time"; pstmt1 = conn.prepareStatement(query); pstmt1.setString(1, "0"); pstmt1.setString(2, userid); ResultSet rs1 = pstmt1.executeQuery(); int count = 0; if (rs1.next()) { count = rs1.getInt("count"); } if (projList == null) { result = "{\"success\":[{\"result\": true}],\"msgcount\":[{\"count\":\"" + Integer.toString(count) + "\"}],\"data\":[]}"; } else { for (int i = 0; i < projList.length(); i++) { JSONObject temp = projList.getJSONObject(i); String projid = temp.getString("id"); String qry = ""; String projName = ""; boolean moderator = DashboardHandler.isModerator(conn, userid, projid); if (!moderator) { qry = "(SELECT taskid FROM proj_task WHERE projectid=? AND taskid NOT IN " + "(SELECT taskid FROM proj_taskresourcemapping)) " + "UNION " + "(SELECT taskid FROM proj_taskresourcemapping WHERE resourceid = ? " + "AND taskid IN (SELECT taskid FROM proj_task WHERE projectid = ?))"; pstmt1 = conn.prepareStatement(qry); pstmt1.setString(1, projid); pstmt1.setString(2, userid); pstmt1.setString(3, projid); } else { qry = "SELECT taskid FROM proj_task WHERE projectid = ?"; pstmt1 = conn.prepareStatement(qry); pstmt1.setString(1, projid); } rs1 = pstmt1.executeQuery(); String tids = ""; while (rs1.next()) { tids += "'" + rs1.getString("taskid") + "',"; } if (tids.length() > 0) { tids = tids.substring(0, (tids.length() - 1)); pstmt1 = conn.prepareStatement( "SELECT projectname FROM project WHERE projectid = ?"); pstmt1.setString(1, projid); rs1 = pstmt1.executeQuery(); if (rs1.next()) { projName = rs1.getString("projectname"); } qry = "SELECT priority,taskname,percentcomplete,DATE_FORMAT(startdate,'%D %b %y') AS startdate,DATE_FORMAT(enddate,'%D %b %y') AS enddate, taskid " + "FROM proj_task " + "WHERE percentcomplete < 100 AND taskid IN (" + tids + ") AND (date(enddate)>=date(now())) AND (date(startdate) <= date(now()))"; // pstmt.setInt(1, limit); pstmt1 = conn.prepareStatement(qry); rs1 = pstmt1.executeQuery(); while (rs1.next()) { JSONObject j = new JSONObject(); j.put("projectname", projName); j.put("taskname", rs1.getString("taskname")); j.put("taskid", rs1.getString("taskid")); j.put("complete", rs1.getString("percentcomplete")); j.put("startdate", rs1.getString("startdate")); j.put("enddate", rs1.getString("enddate")); int ptr = rs1.getInt("priority"); String pStr = "medium"; if (ptr == 0) { pStr = "low"; } else if (ptr == 2) { pStr = "high"; } j.put("priority", pStr); if (moderator) { String res = DashboardHandler.getTaskResources(conn, rs1.getString("taskid"), projid, userid); if (!StringUtil.equal(res, "{}")) { JSONObject jobj1 = new JSONObject(res); JSONArray jarr = jobj1.getJSONArray("data"); String resr = ""; for (int cnt = 0; cnt < jarr.length(); cnt++) { resr += jarr.getJSONObject(cnt).getString("resourcename") + ", "; } resr = resr.substring(0, (resr.length() - 2)); if (!StringUtil.isNullOrEmpty(resr)) { j.put("assignedto", resr); } } } jobj.append("data", j); } } } } if (jobj.has("data")) { JSONObject fin = new JSONObject(); fin.put("count", String.valueOf(count)); jobj.append("msgcount", fin); result = jobj.toString(); } else { result = "{\"success\":[{\"result\": true}],\"msgcount\":[{\"count\":\"" + Integer.toString(count) + "\"}],\"data\":[]}"; } } catch (JSONException ex) { } break; case 0: // overdue tasks try { JSONObject jobj = new JSONObject(); PreparedStatement pstmt1 = null; String query = "Select count(post_id) AS count from mailmessages inner join users " + "on users.userid = mailmessages.poster_id where folder = ? and to_id = ? and readflag = false ORDER BY post_time"; pstmt1 = conn.prepareStatement(query); pstmt1.setString(1, "0"); pstmt1.setString(2, userid); ResultSet rs1 = pstmt1.executeQuery(); int count = 0; if (rs1.next()) { count = rs1.getInt("count"); } if (projList == null) { result = "{\"success\":[{\"result\": true}],\"msgcount\":[{\"count\":\"" + Integer.toString(count) + "\"}],\"data\":[]}"; } else { for (int i = 0; i < projList.length(); i++) { JSONObject temp = projList.getJSONObject(i); String projid = temp.getString("id"); String qry = ""; String projName = ""; boolean moderator = DashboardHandler.isModerator(conn, userid, projid); if (!moderator) { qry = "(SELECT taskid FROM proj_task WHERE projectid=? AND taskid NOT IN " + "(SELECT taskid FROM proj_taskresourcemapping)) " + "UNION " + "(SELECT taskid FROM proj_taskresourcemapping WHERE resourceid = ?)"; pstmt1 = conn.prepareStatement(qry); pstmt1.setString(1, projid); pstmt1.setString(2, userid); } else { qry = "SELECT taskid FROM proj_task WHERE projectid = ?"; pstmt1 = conn.prepareStatement(qry); pstmt1.setString(1, projid); } rs1 = pstmt1.executeQuery(); String tids = ""; while (rs1.next()) { tids += "'" + rs1.getString("taskid") + "',"; } if (tids.length() > 0) { tids = tids.substring(0, (tids.length() - 1)); pstmt1 = conn.prepareStatement( "SELECT projectname FROM project WHERE projectid = ?"); pstmt1.setString(1, projid); rs1 = pstmt1.executeQuery(); if (rs1.next()) { projName = rs1.getString("projectname"); } qry = "SELECT priority,taskname,percentcomplete, datediff(CURRENT_DATE,date(enddate)) as overdueby,DATE_FORMAT(enddate,'%D %b %y') AS enddate, taskid " + "FROM proj_task " + "WHERE percentcomplete < 100 AND taskid IN (" + tids + ") AND date(proj_task.enddate) < date(now()) LIMIT ? OFFSET ?"; pstmt1 = conn.prepareStatement(qry); pstmt1.setInt(1, limit); pstmt1.setInt(2, offset); rs1 = pstmt1.executeQuery(); while (rs1.next()) { JSONObject j = new JSONObject(); j.put("projectname", projName); j.put("taskid", rs1.getString("taskid")); j.put("taskname", rs1.getString("taskname")); j.put("complete", rs1.getString("percentcomplete")); j.put("overdueby", rs1.getString("overdueby")); j.put("enddate", rs1.getString("enddate")); int ptr = rs1.getInt("priority"); String pStr = "medium"; if (ptr == 0) { pStr = "low"; } else if (ptr == 2) { pStr = "high"; } j.put("priority", pStr); if (moderator) { String res = DashboardHandler.getTaskResources(conn, rs1.getString("taskid"), projid, userid); if (!StringUtil.equal(res, "{}")) { JSONObject jobj1 = new JSONObject(res); JSONArray jarr = jobj1.getJSONArray("data"); String resr = ""; for (int cnt = 0; cnt < jarr.length(); cnt++) { resr += jarr.getJSONObject(cnt).getString("resourcename") + ", "; } resr = resr.substring(0, (resr.length() - 2)); if (!StringUtil.isNullOrEmpty(resr)) { j.put("assignedto", resr); } } } jobj.append("data", j); } } } } if (jobj.has("data")) { JSONObject fin = new JSONObject(); fin.put("count", count); jobj.append("msgcount", fin); result = jobj.toString(); } else { result = "{\"success\":[{\"result\": true}],\"msgcount\":[{\"count\":\"" + Integer.toString(count) + "\"}],\"data\":[]}"; } } catch (JSONException ex) { } break; case 2: // calendar events PreparedStatement pstmt1 = null; String query = "Select count(post_id) AS count from mailmessages inner join users " + "on users.userid = mailmessages.poster_id where folder = ? and to_id = ? and readflag = false ORDER BY post_time"; pstmt1 = conn.prepareStatement(query); pstmt1.setString(1, "0"); pstmt1.setString(2, userid); ResultSet rs1 = pstmt1.executeQuery(); int count = 0; if (rs1.next()) { count = rs1.getInt("count"); } String sqlquery = "SELECT calendarevents.subject,project.projectname, DATE_FORMAT(calendarevents.startts,'%D %b %y') AS 'startdate' ," + "DATE_FORMAT(calendarevents.startts,'%h:%i %p') AS 'starttime'," + "CASE calendarevents.priority WHEN 'm' THEN 'Medium' WHEN 'l' THEN 'Low' WHEN 'h' THEN 'High' END AS priority" + " FROM calendarevents INNER JOIN calendars ON calendars.cid =calendarevents.cid INNER JOIN project ON project.projectid = calendars.userid " + " WHERE project.projectid IN (SELECT project.projectid FROM project INNER JOIN projectmembers ON " + " projectmembers.projectid = project.projectid WHERE userid = ?) AND calendars.timestamp> ? " + " AND date(startts)>=CURRENT_DATE AND date(startts)<=(ADDDATE(CURRENT_DATE, 7)) ORDER BY startts LIMIT ? OFFSET ?"; pstmt = conn.prepareStatement(sqlquery); pstmt.setString(1, userid); pstmt.setString(2, "1970-01-01 00:00:00"); pstmt.setInt(3, limit); pstmt.setInt(4, offset); rsForSubQ = pstmt.executeQuery(); try { JSONObject jobj = new JSONObject(); while (rsForSubQ.next()) { JSONObject j = new JSONObject(); j.put("subject", rsForSubQ.getString("subject")); j.put("projectname", rsForSubQ.getString("projectname")); j.put("startdate", rsForSubQ.getString("startdate")); j.put("starttime", rsForSubQ.getString("starttime")); j.put("priority", rsForSubQ.getString("priority")); jobj.append("data", j); } if (jobj.has("data")) { JSONObject fin = new JSONObject(); fin.put("count", String.valueOf(count)); jobj.append("msgcount", fin); result = jobj.toString(); } else { result = "{\"success\":true,\"data\":[]}"; } } catch (JSONException ex) { result = "{\"success\":false,\"data\":" + ex.getMessage() + "}"; } //result = kwljson.GetJsonForGrid(rsForSubQ); break; case 3: // unread personal msgs query = "Select count(post_id) AS count from mailmessages inner join users " + "on users.userid = mailmessages.poster_id where folder = ? and to_id = ? and readflag = false ORDER BY post_time"; pstmt = conn.prepareStatement(query); pstmt.setString(1, "0"); pstmt.setString(2, userid); rsForSubQ = pstmt.executeQuery(); count = 0; if (rsForSubQ.next()) { count = rsForSubQ.getInt("count"); } query = "Select post_id ,concat(fname,' ',lname) as post_fullname,userlogin.username as poster_id , post_text , post_subject ," + " DATE_FORMAT(post_time,'%D %b %y %h:%i%p') as post_time from mailmessages inner join users " + "on users.userid = mailmessages.poster_id inner join userlogin on users.userid =userlogin.userid where folder = ? and to_id = ? and readflag = false ORDER BY post_time DESC"; pstmt = conn.prepareStatement(query); pstmt.setString(1, "0"); pstmt.setString(2, userid); // pstmt.setInt(3, limit); // pstmt.setInt(4, offset); rsForSubQ = pstmt.executeQuery(); //result = kwljson.GetJsonForGrid(rsForSubQ); try { JSONObject jobj = new JSONObject(); String companyid = getCompanyID(conn, userid); String subdomain = CompanyHandler.getCompanySubdomainByCompanyID(conn, companyid); while (rsForSubQ.next()) { JSONObject j = new JSONObject(); j.put("post_id", rsForSubQ.getString("post_id")); j.put("post_fullname", rsForSubQ.getString("post_fullname")); j.put("poster_id", rsForSubQ.getString("poster_id")); j.put("post_text", insertSmiley(rsForSubQ.getString("post_text"), URLUtil.getPageURL(request, com.krawler.esp.web.resource.Links.loginpageFull, subdomain))); j.put("post_subject", rsForSubQ.getString("post_subject")); j.put("post_time", rsForSubQ.getString("post_time")); jobj.append("data", j); } if (jobj.has("data")) { JSONObject temp = new JSONObject(); temp.put("count", String.valueOf(count)); jobj.append("msgcount", temp); result = jobj.toString(); } else { result = "{\"success\":true,\"data\":[]}"; } } catch (JSONException ex) { result = "{\"success\":false,\"data\":" + ex.getMessage() + "}"; } break; case 4: // list of users's projects String companyid = getCompanyID(conn, userid); boolean isSuperUser = DashboardHandler.isSuperUser(conn, companyid, userid); try { JSONObject projectList = new JSONObject( DashboardHandler.getProjectListMember(conn, userid, 10, 0)); JSONArray projArray = projectList.getJSONArray("data"); int prc = projArray.length(); JSONObject jobj = new JSONObject(); if (prc > 0) { for (int i = 0; i < projArray.length(); i++) { JSONObject j = new JSONObject(); j.put("name", projArray.getJSONObject(i).getString("name")); j.put("id", projArray.getJSONObject(i).getString("id")); jobj.append("data", j); } } if (jobj.has("data")) { result = jobj.toString(); } else { result = "{\"success\":true,\"data\":[]}"; } } catch (JSONException ex) { result = "{\"success\":false,\"data\":" + ex.getMessage() + "}"; } break; case 5: // list of project resources String responseText = projdb.getProjectResources(conn, request.getParameter("id")); try { JSONObject jobj = new JSONObject(); if (responseText.compareTo("{data:{}}") != 0) { JSONObject resJOBJ = new JSONObject(responseText); JSONArray jArray = resJOBJ.getJSONArray("data"); int prec = jArray.length(); if (prec > 0) { for (int i = 0; i < prec; i++) { JSONObject j = new JSONObject(); j.put("name", jArray.getJSONObject(i).getString("resourcename")); j.put("id", jArray.getJSONObject(i).getString("resourceid")); jobj.append("data", j); } } } if (jobj.has("data")) { result = jobj.toString(); } else { result = "{\"success\":true,\"data\":[]}"; } } catch (JSONException ex) { result = "{\"success\":false,\"data\":" + ex.getMessage() + "}"; } break; case 6:// display project list companyid = getCompanyID(conn, userid); result = AdminServlet.getProjData(conn, request, companyid, ""); break; case 7:// fetch assigned/unassigned members result = getAssiUnAssiProjctMembers(conn, request.getParameter("projectid")); break; case 8:// isSuperUser companyid = getCompanyID(conn, userid); boolean isSuper = DashboardHandler.isSuperUser(conn, companyid, userid); JSONObject temp = new JSONObject(); JSONObject jobj = new JSONObject(); temp.put("superuser", isSuper); jobj.append("data", temp); result = jobj.toString(); break; case 9:// manage members String userids = request.getParameter("userid"); if (StringUtil.isNullOrEmpty(userids)) { result = "{\"result\":[{\"success\":false}], \"msg\":[{\"msgText\": \"Can not remove all project members.\"}]}"; } else { userids = userids.substring(0, (userids.length() - 1)); String[] uids = userids.split(","); String pid = request.getParameter("projectid"); result = manageMembers(conn, pid, uids); } break; case 10: // company user list companyid = getCompanyID(conn, userid); String companyMembers = AdminServlet.getAdminUserData(conn, request, companyid, "", false); // result = "{\"result\":[{\"success\":false}], \"msg\":[{\"msgText\": \"Could not complete your request.\"}]}"; result = "{\"result\":[{\"success\":true}],\"data\":" + companyMembers + "}"; break; } break; case 2: // Update Records userid = getUserid(conn, request.getParameter("applicationid").toString()); switch (mode) { case 1:// set read flag String post_id = request.getParameter("post_id"); String query = "update mailmessages set readflag=true where post_id = ?"; pstmt = conn.prepareStatement(query); pstmt.setString(1, post_id); int rows = pstmt.executeUpdate(); if (rows > 0) { conn.commit(); result = "{\"success\":true}"; } else { result = "{\"success\":false}"; } break; case 2:// update percent value for record String taskid = request.getParameter("taskid"); String pcomplete = request.getParameter("complete"); query = "update proj_task set percentcomplete = ? where taskid = ?"; pstmt = conn.prepareStatement(query); pstmt.setString(1, pcomplete); pstmt.setString(2, taskid); rows = pstmt.executeUpdate(); if (rows > 0) { conn.commit(); result = "{\"success\":true}"; } else { result = "{\"success\":false}"; } break; case 3:// insert tasks try { String projId = request.getParameter("projectid"); pstmt = conn.prepareStatement( "select max(taskindex) as maxindex from proj_task where projectid=?"); pstmt.setString(1, projId); rs = pstmt.executeQuery(); int rowindex = 0; if (rs.next()) { rowindex = rs.getInt(1) + 1; } java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy/MM/dd"); UUID ud = new UUID(2312, 4123); taskid = ud.randomUUID().toString(); String taskname = request.getParameter("name"); String stdate = request.getParameter("start"); String enddate = request.getParameter("end"); int priority = 1; if (!StringUtil.isNullOrEmpty(request.getParameter("priority"))) { priority = Integer.parseInt(request.getParameter("priority")); } String duration = "1"; String nonworkdays = projdb.getNonWorkWeekdays(conn, projId); String Holidays = projdb.getCmpHolidaydays(conn, projId); JSONObject nmweekObj = new JSONObject(nonworkdays); int nonworkweekArr[] = new int[nmweekObj.getJSONArray("data").length()]; for (int cnt = 0; cnt < nmweekObj.getJSONArray("data").length(); cnt++) { nonworkweekArr[cnt] = Integer .parseInt(nmweekObj.getJSONArray("data").getJSONObject(cnt).getString("day")); } String holidayArr[] = new String[1]; holidayArr[0] = ""; if (Holidays.compareTo("{data:{}}") != 0) { nmweekObj = new JSONObject(Holidays); holidayArr = new String[nmweekObj.getJSONArray("data").length()]; for (int cnt = 0; cnt < nmweekObj.getJSONArray("data").length(); cnt++) { holidayArr[cnt] = nmweekObj.getJSONArray("data").getJSONObject(cnt) .getString("holiday"); } } duration = projectReport.calculateWorkingDays(sdf.parse(stdate), sdf.parse(enddate), nonworkweekArr, holidayArr) + ""; pstmt = conn.prepareStatement( "INSERT INTO proj_task(taskid, taskname, duration, startdate, enddate, projectid, " + "taskindex, level, parent, actualstartdate, actualduration, percentcomplete, notes, priority, " + "isparent) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); pstmt.setString(1, taskid); pstmt.setString(2, taskname); pstmt.setString(3, duration); java.util.Date DateVal = sdf.parse(stdate); Timestamp ts = new Timestamp(DateVal.getTime()); pstmt.setTimestamp(4, ts); DateVal = sdf.parse(enddate); ts = new Timestamp(DateVal.getTime()); pstmt.setTimestamp(5, ts); pstmt.setString(6, projId); pstmt.setString(7, String.valueOf(rowindex)); pstmt.setString(8, "0"); pstmt.setString(9, "0"); DateVal = sdf.parse(stdate); ts = new Timestamp(DateVal.getTime()); pstmt.setTimestamp(10, ts); pstmt.setString(11, duration); pstmt.setString(12, "0"); pstmt.setString(13, ""); pstmt.setInt(14, priority); pstmt.setBoolean(15, false); boolean flag = pstmt.execute(); if (!request.getParameter("assignto").equals("")) { String[] resArray = request.getParameter("assignto").split(","); for (int i = 0; i < resArray.length; i++) { int dur = 0; String rid = resArray[i]; pstmt = conn.prepareStatement( "insert into proj_taskresourcemapping (taskid,resourceid,resduration) values(?,?,?)"); pstmt.setString(1, taskid); pstmt.setString(2, rid); pstmt.setInt(3, dur); pstmt.execute(); } } conn.commit(); result = "{\"success\":[{\"result\":true}]}"; } catch (ParseException ex) { Logger.getLogger(deskeramob.class.getName()).log(Level.SEVERE, null, ex); result = "{\"success\":[{\"result\":false}],\"data\":" + ex.getMessage() + "}"; } catch (JSONException ex) { result = "{\"success\":[{\"result\":false}],\"data\":" + ex.getMessage() + "}"; } break; case 5: // import contacts result = importContacts(conn, request); break; case 6: // export contacts result = exportContacts(conn, request); if (!StringUtil.isNullOrEmpty(result)) { result = "{\"import\":[{\"result\":true}]," + result.substring(1, (result.length() - 1)) + "}"; } else { result = "{\"import\":[{\"result\":true,\"error\":\"There seem to be some problem with server. Could not import contacts.\"}]}"; } break; } break; case 3: // company updates switch (mode) { case 2: //create project userid = getUserid(conn, request.getParameter("applicationid").toString()); String subdomain = CompanyHandler.getCompanySubdomainByUser(conn, userid); String companyid = getCompanyID(conn, userid); result = createProject(conn, request, companyid, subdomain, userid); if (StringUtil.equal("success", result)) { result = "{\"success\":[{\"result\":true}]}"; } else { result = "{\"success\":[{\"result\":false,\"error\":\"" + result + "\"}]}"; } break; case 3: //delete project userid = getUserid(conn, request.getParameter("applicationid").toString()); companyid = getCompanyID(conn, userid); result = AdminServlet.deleteProject(conn, request, companyid, userid, "iPhone"); if (StringUtil.equal("failure", result)) { result = "{\"success\":[{\"result\":false}]}"; DbPool.quietRollback(conn); } else { result = "{\"success\":[{\"result\":true}]}"; conn.commit(); } break; } break; } } catch (JSONException ex) { Logger.getLogger(deskeramob.class.getName()).log(Level.SEVERE, null, ex); result = "{\"success\":[{\"result\":\"1\"}],\"data\":" + ex.getMessage() + "}"; } catch (ServiceException ex) { result = "{\"success\":[{\"result\":\"0\"}],\"data\":" + ex.getMessage() + "}"; } catch (SQLException ex) { result = "{\"success\":[{\"result\":\"0\"}],\"data\":" + ex.getMessage() + "}"; } finally { DbPool.quietClose(conn); response.getWriter().println(result); } response.getWriter().close(); }
From source file:com.krawler.esp.servlets.deskeramob_V1.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods. * @param request servlet request//from ww w.ja v a2 s . co m * @param response servlet response */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SessionExpiredException { response.setContentType("text/html;charset=UTF-8"); ResultSet rs = null; ResultSet rsForSubQ = null; PreparedStatement pstmt = null; String result = ""; boolean android = false; Connection conn = null; try { conn = DbPool.getConnection(); int action = Integer.parseInt(request.getParameter("action")); int mode = Integer.parseInt(request.getParameter("mode")); if (!StringUtil.isNullOrEmpty(request.getParameter("android"))) { android = Boolean.parseBoolean(request.getParameter("android")); } switch (action) { case 0: // generate application id String u = request.getParameter("u"); String p = request.getParameter("p"); String d = request.getParameter("d"); result = authUser(conn, u, p, d); break; case 1: // dashboard request int limit = 15, offset = 0; String userid = request.getParameter("userid").toString(); String projectlist = DashboardHandler.getProjectList(conn, userid, 1000, 0, ""); JSONArray projList = null; try { JSONObject projListObj = new JSONObject(projectlist); projList = projListObj.getJSONArray("data"); } catch (JSONException ex) { result = "{\"data\":[{\"success\":false,\"data\":" + ex.getMessage() + "}]}"; } switch (mode) { case 1: // due tasks try { boolean projMod = false; for (int c = 0; c < projList.length(); c++) { JSONObject te = projList.getJSONObject(c); if (DashboardHandler.isModerator(conn, userid, te.getString("id"))) { projMod = true; break; } } PreparedStatement pstmt1 = null; JSONObject jobj = new JSONObject(); String query = "Select count(post_id) AS count from mailmessages inner join users " + "on users.userid = mailmessages.poster_id where folder = ? and to_id = ? and readflag = false ORDER BY post_time"; pstmt1 = conn.prepareStatement(query); pstmt1.setString(1, "0"); pstmt1.setString(2, userid); ResultSet rs1 = pstmt1.executeQuery(); int count = 0; if (rs1.next()) { count = rs1.getInt("count"); } if (projList == null) { result = "{\"success\":[{\"result\": true}],\"msgcount\":[{\"count\":\"" + Integer.toString(count) + "\"}],\"data\":[]}"; } else { for (int i = 0; i < projList.length(); i++) { JSONObject temp = projList.getJSONObject(i); String projid = temp.getString("id"); String qry = ""; String projName = ""; boolean moderator = DashboardHandler.isModerator(conn, userid, projid); if (!moderator) { qry = "(SELECT taskid FROM proj_task WHERE projectid=? AND taskid NOT IN " + "(SELECT taskid FROM proj_taskresourcemapping)) " + "UNION " + "(SELECT taskid FROM proj_taskresourcemapping WHERE resourceid = ? " + "AND taskid IN (SELECT taskid FROM proj_task WHERE projectid = ?))"; pstmt1 = conn.prepareStatement(qry); pstmt1.setString(1, projid); pstmt1.setString(2, userid); pstmt1.setString(3, projid); } else { qry = "SELECT taskid FROM proj_task WHERE projectid = ?"; pstmt1 = conn.prepareStatement(qry); pstmt1.setString(1, projid); } rs1 = pstmt1.executeQuery(); String tids = ""; while (rs1.next()) { tids += "'" + rs1.getString("taskid") + "',"; } if (tids.length() > 0) { tids = tids.substring(0, (tids.length() - 1)); pstmt1 = conn.prepareStatement( "SELECT projectname FROM project WHERE projectid = ?"); pstmt1.setString(1, projid); rs1 = pstmt1.executeQuery(); if (rs1.next()) { projName = rs1.getString("projectname"); } qry = "SELECT priority,taskname,percentcomplete,DATE_FORMAT(startdate,'%D %b %y') AS startdate,DATE_FORMAT(enddate,'%D %b %y') AS enddate, taskid " + "FROM proj_task " + "WHERE percentcomplete < 100 AND taskid IN (" + tids + ") AND (date(enddate)>=date(now())) AND (date(startdate) <= date(now()))"; // pstmt.setInt(1, limit); pstmt1 = conn.prepareStatement(qry); rs1 = pstmt1.executeQuery(); while (rs1.next()) { JSONObject j = new JSONObject(); j.put("projectname", projName); j.put("taskname", rs1.getString("taskname")); j.put("taskid", rs1.getString("taskid")); j.put("complete", rs1.getString("percentcomplete")); j.put("startdate", rs1.getString("startdate")); j.put("enddate", rs1.getString("enddate")); int ptr = rs1.getInt("priority"); String pStr = "medium"; if (ptr == 0) { pStr = "high"; } else if (ptr == 2) { pStr = "low"; } j.put("priority", pStr); if (moderator) { String res = DashboardHandler.getTaskResources(conn, rs1.getString("taskid"), projid, userid); if (!StringUtil.equal(res, "{}")) { JSONObject jobj1 = new JSONObject(res); JSONArray jarr = jobj1.getJSONArray("data"); String resr = ""; for (int cnt = 0; cnt < jarr.length(); cnt++) { resr += jarr.getJSONObject(cnt).getString("resourcename") + ", "; } resr = resr.substring(0, (resr.length() - 2)); if (!StringUtil.isNullOrEmpty(resr)) { j.put("assignedto", resr); } } } jobj.append("data", j); } } } } if (jobj.has("data")) { JSONObject fin = new JSONObject(); fin.put("count", String.valueOf(count)); jobj.append("msgcount", fin); jobj.put("moderator", projMod); result = jobj.toString(); } else { result = "{\"success\":[{\"result\": true}],\"msgcount\":[{\"count\":\"" + Integer.toString(count) + "\"}],\"data\":[],\"moderator\":" + Boolean.toString(projMod) + "}"; } } catch (JSONException ex) { } break; case 0: // overdue tasks try { JSONObject jobj = new JSONObject(); PreparedStatement pstmt1 = null; String query = "Select count(post_id) AS count from mailmessages inner join users " + "on users.userid = mailmessages.poster_id where folder = ? and to_id = ? and readflag = false ORDER BY post_time"; pstmt1 = conn.prepareStatement(query); pstmt1.setString(1, "0"); pstmt1.setString(2, userid); ResultSet rs1 = pstmt1.executeQuery(); int count = 0; if (rs1.next()) { count = rs1.getInt("count"); } if (projList == null) { result = "{\"success\":[{\"result\": true}],\"msgcount\":[{\"count\":\"" + Integer.toString(count) + "\"}],\"data\":[]}"; } else { for (int i = 0; i < projList.length(); i++) { JSONObject temp = projList.getJSONObject(i); String projid = temp.getString("id"); String qry = ""; String projName = ""; boolean moderator = DashboardHandler.isModerator(conn, userid, projid); if (!moderator) { qry = "(SELECT taskid FROM proj_task WHERE projectid=? AND taskid NOT IN " + "(SELECT taskid FROM proj_taskresourcemapping)) " + "UNION " + "(SELECT taskid FROM proj_taskresourcemapping WHERE resourceid = ?)"; pstmt1 = conn.prepareStatement(qry); pstmt1.setString(1, projid); pstmt1.setString(2, userid); } else { qry = "SELECT taskid FROM proj_task WHERE projectid = ?"; pstmt1 = conn.prepareStatement(qry); pstmt1.setString(1, projid); } rs1 = pstmt1.executeQuery(); String tids = ""; while (rs1.next()) { tids += "'" + rs1.getString("taskid") + "',"; } if (tids.length() > 0) { tids = tids.substring(0, (tids.length() - 1)); pstmt1 = conn.prepareStatement( "SELECT projectname FROM project WHERE projectid = ?"); pstmt1.setString(1, projid); rs1 = pstmt1.executeQuery(); if (rs1.next()) { projName = rs1.getString("projectname"); } qry = "SELECT priority,taskname,percentcomplete, datediff(CURRENT_DATE,date(enddate)) as overdueby,DATE_FORMAT(enddate,'%D %b %y') AS enddate, taskid " + "FROM proj_task " + "WHERE percentcomplete < 100 AND taskid IN (" + tids + ") AND date(proj_task.enddate) < date(now()) LIMIT ? OFFSET ?"; pstmt1 = conn.prepareStatement(qry); pstmt1.setInt(1, limit); pstmt1.setInt(2, offset); rs1 = pstmt1.executeQuery(); while (rs1.next()) { JSONObject j = new JSONObject(); j.put("projectname", projName); j.put("taskid", rs1.getString("taskid")); j.put("taskname", rs1.getString("taskname")); j.put("complete", rs1.getString("percentcomplete")); j.put("overdueby", rs1.getString("overdueby")); j.put("enddate", rs1.getString("enddate")); int ptr = rs1.getInt("priority"); String pStr = "medium"; if (ptr == 0) { pStr = "high"; } else if (ptr == 2) { pStr = "low"; } j.put("priority", pStr); if (moderator) { String res = DashboardHandler.getTaskResources(conn, rs1.getString("taskid"), projid, userid); if (!StringUtil.equal(res, "{}")) { JSONObject jobj1 = new JSONObject(res); JSONArray jarr = jobj1.getJSONArray("data"); String resr = ""; for (int cnt = 0; cnt < jarr.length(); cnt++) { resr += jarr.getJSONObject(cnt).getString("resourcename") + ", "; } resr = resr.substring(0, (resr.length() - 2)); if (!StringUtil.isNullOrEmpty(resr)) { j.put("assignedto", resr); } } } jobj.append("data", j); } } } } if (jobj.has("data")) { JSONObject fin = new JSONObject(); fin.put("count", count); jobj.append("msgcount", fin); result = jobj.toString(); } else { result = "{\"success\":[{\"result\": true}],\"msgcount\":[{\"count\":\"" + Integer.toString(count) + "\"}],\"data\":[]}"; } } catch (JSONException ex) { } break; case 2: // calendar events PreparedStatement pstmt1 = null; String query = "Select count(post_id) AS count from mailmessages inner join users " + "on users.userid = mailmessages.poster_id where folder = ? and to_id = ? and readflag = false ORDER BY post_time"; pstmt1 = conn.prepareStatement(query); pstmt1.setString(1, "0"); pstmt1.setString(2, userid); ResultSet rs1 = pstmt1.executeQuery(); int count = 0; if (rs1.next()) { count = rs1.getInt("count"); } String timezone = "+00:00"; timezone = Timezone.getTimeZone(conn, userid); String sqlquery = "SELECT calendarevents.subject,project.projectname, startts, endts, DATE_FORMAT(calendarevents.startts,'%D %b %y') AS 'startdate' ," + "DATE_FORMAT(calendarevents.startts,'%h:%i %p') AS 'starttime'," + "CASE calendarevents.priority WHEN 'm' THEN 'Medium' WHEN 'l' THEN 'Low' WHEN 'h' THEN 'High' END AS priority" + " FROM calendarevents INNER JOIN calendars ON calendars.cid =calendarevents.cid INNER JOIN project ON project.projectid = calendars.userid " + " WHERE project.projectid IN (SELECT project.projectid FROM project INNER JOIN projectmembers ON " + " projectmembers.projectid = project.projectid WHERE userid = ?) AND calendars.timestamp> ? " + " AND date(startts)>=CURRENT_DATE AND date(startts)<=(ADDDATE(CURRENT_DATE, 7)) ORDER BY startts LIMIT ? OFFSET ?"; pstmt = conn.prepareStatement(sqlquery); pstmt.setString(1, userid); pstmt.setString(2, "1970-01-01 00:00:00"); pstmt.setInt(3, limit); pstmt.setInt(4, offset); rsForSubQ = pstmt.executeQuery(); try { JSONObject jobj = new JSONObject(); while (rsForSubQ.next()) { timezone = timezone.substring(0, 4) + "00"; String startts = rsForSubQ.getString("startts"); startts = Timezone.toUserDefTimezone(conn, startts, timezone); Date startDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(startts); int date = Integer.parseInt(new SimpleDateFormat("d").format(startDate)); String ordinal = getDateOrdinal(date); JSONObject j = new JSONObject(); j.put("subject", rsForSubQ.getString("subject")); j.put("projectname", rsForSubQ.getString("projectname")); j.put("startdate", new SimpleDateFormat("d'".concat(ordinal).concat("' MMM yy")) .format(startDate)); j.put("starttime", new SimpleDateFormat("hh:mm a").format(startDate)); j.put("priority", rsForSubQ.getString("priority")); jobj.append("data", j); } if (jobj.has("data")) { JSONObject fin = new JSONObject(); fin.put("count", String.valueOf(count)); jobj.append("msgcount", fin); result = jobj.toString(); } else { result = "{\"success\":true,\"data\":[]}"; } } catch (ParseException ex) { result = "{\"success\":false,\"data\":" + ex.getMessage() + "}"; } catch (JSONException ex) { result = "{\"success\":false,\"data\":" + ex.getMessage() + "}"; } //result = kwljson.GetJsonForGrid(rsForSubQ); break; case 3: // unread personal msgs query = "Select count(post_id) AS count from mailmessages inner join users " + "on users.userid = mailmessages.poster_id where folder = ? and to_id = ? and readflag = false ORDER BY post_time"; pstmt = conn.prepareStatement(query); pstmt.setString(1, "0"); pstmt.setString(2, userid); rsForSubQ = pstmt.executeQuery(); count = 0; if (rsForSubQ.next()) { count = rsForSubQ.getInt("count"); } query = "Select post_id ,concat(fname,' ',lname) as post_fullname,userlogin.username as poster_id , post_text , post_subject ," + " post_time from mailmessages inner join users " + "on users.userid = mailmessages.poster_id inner join userlogin on users.userid=userlogin.userid where folder = ? and to_id = ? and readflag = false ORDER BY post_time DESC"; pstmt = conn.prepareStatement(query); pstmt.setString(1, "0"); pstmt.setString(2, userid); // pstmt.setInt(3, limit); // pstmt.setInt(4, offset); rsForSubQ = pstmt.executeQuery(); //result = kwljson.GetJsonForGrid(rsForSubQ); try { JSONObject jobj = new JSONObject(); String companyid = getCompanyID(conn, userid); String subdomain = CompanyHandler.getCompanySubdomainByCompanyID(conn, companyid); while (rsForSubQ.next()) { JSONObject j = new JSONObject(); String postTime = Timezone.toCompanyTimezone(conn, rsForSubQ.getString("post_time"), companyid); Date startDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(postTime); int date = Integer.parseInt(new SimpleDateFormat("d").format(startDate)); String ordinal = getDateOrdinal(date); j.put("post_id", rsForSubQ.getString("post_id")); j.put("post_fullname", rsForSubQ.getString("post_fullname")); j.put("poster_id", rsForSubQ.getString("poster_id")); j.put("post_text", insertSmiley(rsForSubQ.getString("post_text"), URLUtil.getPageURL(request, com.krawler.esp.web.resource.Links.loginpageFull, subdomain))); j.put("post_subject", rsForSubQ.getString("post_subject")); j.put("post_time", new SimpleDateFormat( "dd'".concat(ordinal).concat("' MMM yy").concat(" hh:mm a")) .format(startDate)); jobj.append("data", j); } if (jobj.has("data")) { JSONObject temp = new JSONObject(); temp.put("count", String.valueOf(count)); jobj.append("msgcount", temp); result = jobj.toString(); } else { result = "{\"success\":true,\"data\":[]}"; } } catch (ParseException ex) { result = "{\"success\":false,\"data\":" + ex.getMessage() + "}"; } catch (JSONException ex) { result = "{\"success\":false,\"data\":" + ex.getMessage() + "}"; } break; case 4: // list of users's projects String companyid = getCompanyID(conn, userid); // boolean isSuperUser = DashboardHandler.isSuperUser(conn, companyid, userid); try { JSONObject jobj = getUserProjectList(conn, userid); // JSONObject projectList = new JSONObject(DashboardHandler.getProjectListMember(conn, userid, 10, 0)); // JSONArray projArray = projectList.getJSONArray("data"); // int prc = projArray.length(); // JSONObject jobj = new JSONObject(); // if (prc > 0) { // for (int i = 0; i < projArray.length(); i++) { // JSONObject tempProj = projArray.getJSONObject(i); // if(tempProj.getInt("status") > 3){ // JSONObject j = new JSONObject(); // j.put("name", tempProj.getString("name")); // j.put("id", tempProj.getString("id")); // jobj.append("data", j); // } // } // } if (jobj.has("data")) { result = jobj.toString(); } else { result = "{\"success\":true,\"data\":[]}"; } } catch (JSONException ex) { result = "{\"success\":false,\"data\":" + ex.getMessage() + "}"; } break; case 5: // list of project resources try { JSONObject jobj = new JSONObject(); jobj = getProjectResourceList(conn, request); // String responseText = projdb.getProjectResources(conn, request.getParameter("id")); // try { // JSONObject jobj = new JSONObject(); // if (responseText.compareTo("{data:{}}") != 0) { // JSONObject resJOBJ = new JSONObject(responseText); // JSONArray jArray = resJOBJ.getJSONArray("data"); // int prec = jArray.length(); // if (prec > 0) { // for (int i = 0; i < prec; i++) { // JSONObject j = new JSONObject(); // j.put("name", jArray.getJSONObject(i).getString("resourcename")); // j.put("id", jArray.getJSONObject(i).getString("resourceid")); // jobj.append("data", j); // } // } // } if (jobj.has("data")) { result = jobj.toString(); } else { result = "{\"success\":true,\"data\":[]}"; } } catch (JSONException ex) { result = "{\"success\":false,\"data\":" + ex.getMessage() + "}"; } break; case 6:// display project list if (!AdminServlet.isCreator(conn, userid) || android) { result = projectlist; } else { companyid = getCompanyID(conn, userid); result = AdminServlet.getProjData(conn, request, companyid, ""); } break; case 7:// fetch assigned/unassigned members result = getAssiUnAssiProjctMembers(conn, request.getParameter("projectid")); break; case 8:// isSuperUser int projectcount = 0; companyid = getCompanyID(conn, userid); boolean isSuper = DashboardHandler.isSuperUser(conn, companyid, userid); JSONObject temp = new JSONObject(); JSONObject jobj = new JSONObject(); temp.put("superuser", isSuper); jobj.append("data", temp); try { JSONObject projectList = getUserProjectList(conn, userid); JSONArray projectArray = projectList.getJSONArray("data"); for (int c = 0; c < projectArray.length(); c++) { JSONObject te = projectArray.getJSONObject(c); request.setAttribute("id", te.getString("id")); JSONArray member = getProjectResourceList(conn, request).getJSONArray("data"); for (int i = 0; i < member.length(); i++) { JSONObject j = member.getJSONObject(i); jobj.append("projectmember", j); } jobj.append("data", te); projectcount++; } } catch (Exception e) { result = e.toString(); } jobj.put("projectcount", projectcount); jobj.put("userid", userid); result = jobj.toString(); break; case 9:// manage members String userids = request.getParameter("userid"); if (StringUtil.isNullOrEmpty(userids)) { result = "{\"result\":[{\"success\":false}], \"msg\":[{\"msgText\": \"Can not remove all project members.\"}]}"; } else { userids = userids.substring(0, (userids.length() - 1)); String[] uids = userids.split(","); String pid = request.getParameter("projectid"); result = manageMembers(conn, pid, uids, userid); } break; case 10: // company user list companyid = getCompanyID(conn, userid); String companyMembers = AdminServlet.getAdminUserData(conn, request, companyid, "", false); // result = "{\"result\":[{\"success\":false}], \"msg\":[{\"msgText\": \"Could not complete your request.\"}]}"; result = "{\"result\":[{\"success\":true}],\"data\":" + companyMembers + "}"; break; } break; case 2: // Update Records userid = request.getParameter("userid").toString(); switch (mode) { case 1:// set read flag String post_id = request.getParameter("post_id"); String query = "update mailmessages set readflag=true where post_id = ?"; pstmt = conn.prepareStatement(query); pstmt.setString(1, post_id); int rows = pstmt.executeUpdate(); if (rows > 0) { conn.commit(); result = "{\"success\":true}"; } else { result = "{\"success\":false}"; } break; case 2:// update percent value for record String taskid = request.getParameter("taskid"); String pcomplete = request.getParameter("complete"); query = "update proj_task set percentcomplete = ? where taskid = ?"; pstmt = conn.prepareStatement(query); pstmt.setString(1, pcomplete); pstmt.setString(2, taskid); rows = pstmt.executeUpdate(); if (rows > 0) { conn.commit(); result = "{\"success\":true}"; } else { result = "{\"success\":false}"; } break; case 3:// insert tasks try { String projId = request.getParameter("projectid"); pstmt = conn.prepareStatement( "select max(taskindex) as maxindex from proj_task where projectid=?"); pstmt.setString(1, projId); rs = pstmt.executeQuery(); int rowindex = 1; if (rs.next()) { rowindex = rs.getInt(1) + 1; } java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy/MM/dd"); taskid = UUID.randomUUID().toString(); String taskname = request.getParameter("name"); String stdate = request.getParameter("start"); String enddate = request.getParameter("end"); int priority = 1; if (!StringUtil.isNullOrEmpty(request.getParameter("priority"))) { priority = Integer.parseInt(request.getParameter("priority")); } pstmt = conn.prepareStatement( "INSERT INTO proj_task(taskid, taskname, duration, startdate, enddate, projectid, " + "taskindex, level, parent, actualstartdate, actualduration, percentcomplete, notes, priority, " + "isparent) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); pstmt.setString(1, taskid); pstmt.setString(2, taskname); java.util.Date DateVal = sdf.parse(stdate); java.util.Date sDate = sdf.parse(stdate); Timestamp ts = new Timestamp(DateVal.getTime()); pstmt.setTimestamp(4, ts); DateVal = sdf.parse(enddate); int duration = SchedulingUtilities.calculateWorkingDays(conn, projId, sDate, DateVal); pstmt.setString(3, String.valueOf(duration)); ts = new Timestamp(DateVal.getTime()); pstmt.setTimestamp(5, ts); pstmt.setString(6, projId); pstmt.setString(7, String.valueOf(rowindex)); pstmt.setString(8, "0"); pstmt.setString(9, "0"); DateVal = sdf.parse(stdate); ts = new Timestamp(DateVal.getTime()); pstmt.setTimestamp(10, ts); pstmt.setString(11, String.valueOf(duration)); //if(request.getParameter("completed").equals(null)) pstmt.setString(12, "0"); // else // pstmt.setString(12, request.getParameter("completed")); pstmt.setString(13, ""); pstmt.setInt(14, priority); pstmt.setBoolean(15, false); boolean flag = pstmt.execute(); if (!request.getParameter("assignto").equals("")) { String[] resArray = request.getParameter("assignto").split(","); for (int i = 0; i < resArray.length; i++) { int dur = 0; String rid = resArray[i]; pstmt = conn.prepareStatement( "insert into proj_taskresourcemapping (taskid,resourceid,resduration) values(?,?,?)"); pstmt.setString(1, taskid); pstmt.setString(2, rid); pstmt.setInt(3, dur); pstmt.execute(); } } conn.commit(); result = "{\"success\":[{\"result\":true}]}"; } catch (ParseException ex) { Logger.getLogger(deskeramob.class.getName()).log(Level.SEVERE, null, ex); result = "{\"success\":[{\"result\":false}],\"data\":" + ex.getMessage() + "}"; } break; case 4: // create user // String companyid = getCompanyID(conn, userid); // result = createUser(conn, request, companyid, userid); // if (!StringUtil.isNullOrEmpty(result) && result.contains("failure")) { // result = "{\"success\":[{\"result\":false,\"error\":\"Could not complete your request. Please try again later.\"}]}"; // } else { // conn.commit(); // result = "{\"success\":[{\"result\":true,\"error\":\"User created successfully.\"}]}"; // } break; case 5: // import contacts result = importContacts(conn, request); break; case 6: // export contacts result = exportContacts(conn, request); if (!StringUtil.isNullOrEmpty(result)) { result = "{\"import\":[{\"result\":true}]," + result.substring(1, (result.length() - 1)) + "}"; } else { result = "{\"import\":[{\"result\":true,\"error\":\"There seem to be some problem with server. Could not import contacts.\"}]}"; } break; } break; case 3: // company updates switch (mode) { case 1:// create new company // result = createNewCompany(conn, request, response); // JSONObject j = new JSONObject(result); // if (j.getJSONArray("data").getJSONObject(0).has("success") && j.getJSONArray("data").getJSONObject(0).getString("success").equals("true")) { // result = authUser(conn, request.getParameter("u"), request.getParameter("p"), request.getParameter("cdomain")); // } break; case 2: //create project userid = request.getParameter("userid").toString(); String subdomain = CompanyHandler.getCompanySubdomainByUser(conn, userid); String companyid = getCompanyID(conn, userid); result = createProject(conn, request, companyid, subdomain, userid); if (StringUtil.equal("success", result)) { result = "{\"success\":[{\"result\":true}]}"; } else { result = "{\"success\":[{\"result\":false,\"error\":\"" + result + "\"}]}"; } break; case 3: //delete project userid = request.getParameter("userid").toString(); companyid = getCompanyID(conn, userid); result = AdminServlet.deleteProject(conn, request, companyid, userid, "iPhone"); if (StringUtil.equal("failure", result)) { result = "{\"success\":[{\"result\":false}]}"; DbPool.quietRollback(conn); } else { result = "{\"success\":[{\"result\":true}]}"; conn.commit(); } break; } break; case 4: //Project Reports switch (mode) { case 0://Project chart(type="milestones") if (!StringUtil.isNullOrEmpty(request.getParameter("projid")) && !StringUtil.isNullOrEmpty(request.getParameter("uid"))) { result = DashboardHandler.getChartURL(conn, request.getParameter("projid"), request.getParameter("uid"), "progress"); } break; case 1: //milestone(rtype="milestone"), overdue(rtype="overdue"), task in progress(rtype="taskinprogress", percent=-99), un-started task(rtype="taskinprogress", percent=0) if (!StringUtil.isNullOrEmpty(request.getParameter("projid")) && !StringUtil.isNullOrEmpty(request.getParameter("userid"))) { result = projectReport.getProjectReportJson(request, false, false); if (result.compareToIgnoreCase("{data:{}}") == 0) { result = "{\"data\":[]}"; } } break; } break; } } catch (JSONException ex) { Logger.getLogger(deskeramob.class.getName()).log(Level.SEVERE, null, ex); result = "{\"success\":[{\"result\":\"1\"}],\"data\":" + ex.getMessage() + "}"; } catch (ServiceException ex) { result = "{\"success\":[{\"result\":\"0\"}],\"data\":" + ex.getMessage() + "}"; } catch (SQLException ex) { result = "{\"success\":[{\"result\":\"0\"}],\"data\":" + ex.getMessage() + "}"; } finally { DbPool.quietClose(conn); response.getWriter().println(result); } response.getWriter().close(); }