Duke's custom UI allows you to graph the membership history of a group. You can specify the interval and the number of occurrences to graph. And you can compare it with a second group. Here is an example of one. A Jira has been created to add something similar to the Grouper UI - GRP-2279
Here is the code to query the membership count at a given point in time.
/** * @param pitGroupId * @param date * @return count of unique subject ids in group at the specified time */ public static int getPITMembershipCount(final String pitGroupId, final Date date) { return (Integer)HibernateSession.callbackHibernateSession(GrouperTransactionType.READONLY_OR_USE_EXISTING, AuditControl.WILL_NOT_AUDIT, new HibernateHandler() { public Object callback(HibernateHandlerBean hibernateHandlerBean) throws GrouperDAOException { HibernateSession hibernateSession = hibernateHandlerBean.getHibernateSession(); String timestamp = "" + date.getTime() + "000"; Connection connection = ((SessionImpl)hibernateSession.getSession()).connection(); PreparedStatement ps = null; ResultSet rs = null; try { String sql = "select count(distinct pm.subject_id) from grouper_pit_memberships_all_v pma, grouper_pit_fields pf, grouper_pit_members pm " + " where pma.field_id = pf.id and pma.member_id = pm.id " + " and NOT pm.subject_source='g:gsa' and pf.name='members' " + " and pma.owner_group_id = ? " + " and pma.group_set_start_time < ? and pma.membership_start_time < ?" + " and (pma.group_set_end_time is null or pma.group_set_end_time > ?)" + " and (pma.membership_end_time is null or pma.membership_end_time > ?)" + " and (pma.membership_end_time is null or pma.membership_end_time > pma.group_set_start_time)" + " and (pma.group_set_end_time is null or pma.group_set_end_time > pma.membership_start_time)"; ps = connection.prepareStatement(sql); ps.setString(1, pitGroupId); ps.setString(2, timestamp); ps.setString(3, timestamp); ps.setString(4, timestamp); ps.setString(5, timestamp); rs = ps.executeQuery(); rs.next(); return rs.getInt(1); } catch (SQLException e) { throw new RuntimeException(e); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { // ignore } } if (ps != null) { try { ps.close(); } catch (SQLException e) { // ignore } } } } }); } |