The Problem

The HR team wished to use Objective to store the files they have on each employee. With several hundred employees each needing five or six files, this was something we definitely wanted to automate.

The Solution

We designed the security model first. We then worked out how to create folders and files. Along the way we learnt a number of other techniques. You can find explanations on this site.

On this page we put it altogether into a single Java bean:

package hr2objective;

import com.objective.oji.OjiAlias;
import com.objective.oji.OjiApplication;
import com.objective.oji.OjiDisposalSchedule;
import com.objective.oji.OjiException;
import com.objective.oji.OjiFolder;
import com.objective.oji.OjiGroup;

import com.objective.oji.OjiObject;
import com.objective.oji.OjiPhysicalFile;
import com.objective.oji.OjiPrivilege;
import com.objective.oji.OjiSearch;
import com.objective.oji.OjiSession;

import com.objective.oji.OjiUser;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.Collection;
import java.util.HashSet;

import java.util.Iterator;

import javax.faces.component.html.HtmlOutputText;

import oracle.jdbc.pool.OracleDataSource;

public class HR2eRDMS
{


  // debug tells us how it went
  String debug;
  String result = "success"; // navigate to success or failure

  // staff and manager details
  private String employeeNumber;
  String networkId = "";
  String managerNetworkId = "";
  String staffName = "";
  String staffEmail = "";
  String managerEmail = "";

  // get manager from per_all_assignemnts_f table
  String managerStaffNumber = "";

  // Objective connect info
  String ojiUser = "XXXXX";
  String ojiPassword = "XXXX";
  String ojiServer = "xxxx";
  int ojiPort = 9999;
  OjiUser ojiApiUser = null;

  // connect to objective
  OjiApplication objective = null;
  OjiSession apiSession = null;
  OjiObject searchResult = null;
  OjiUser ojiStaffUser = null;
  OjiUser ojiManagerUser = null;
  OjiSearch newSearch = null;
  Collection results = null;
  Iterator i = null;
  OjiGroup staffCaveatGroup =null;
  OjiGroup managerCaveatGroup = null;
  OjiFolder staffFolders = null; // root folder containing everyone's folders
  OjiFolder staffFilesFolder = null; // folder for this person

  //  connect info
  String User = "apps";
  String Password = "xxxx";
  String SQL = ""; // used in select statements
  Connection Conn = null;
  Statement State = null;
  ResultSet RS = null;
  OracleDataSource Ods = null;

  // live2 connect info
  OracleDataSource ods = null;
  String oraUser = "xxxx";
  String oraPassword = "xxxx";
  Connection oraConn = null;
  Statement oraState = null;
  ResultSet oraRS = null;

  // HR teams
  String allHR = "";
  String HROperations = "";
  String HRPayroll = "";
  String HRTraining = "";
  String hrStaffNumber = "";
  String hrEmailAddress = "";
  String hrNetworkId = "";

  // HR Caveat Groups
  OjiGroup hrCaveatGroup = null; 
  OjiGroup hrManagerGroup = null; // Head of HR Services
  OjiGroup hrOperationsGroup = null;
  OjiGroup hrPayrollGroup = null;
  OjiGroup hrTrainingGroup = null;
  String teamName = "";
  Collection hrManagerSet = new HashSet();
  Collection hrOperationsSet = new HashSet();
  Collection hrTrainingSet = new HashSet();
  Collection hrPayrollSet = new HashSet();

  // Disposal schedule
  String disposalScheduleName = "";
  OjiDisposalSchedule disposalSchedule = null;
  Collection disposalSchedules = null;
  Collection collect = null;

  // Objective Object Ids - must be configured for the system TODO
  String hrCaveatGroupId = "gA9999";
  String hrManagerGroupId = "gA9999";
  String hrOperationsGroupId = "gA9999";
  String hrPayrollGroupId = "gA9999";
  String hrTrainingGroupId = "gA9999";
  String rootStaffFilesFolder = "fA9999";
  String activeUsersFolder = "gA9999";
  String ojiApiUserId = "uA9999";

  private HtmlOutputText errorMessage;

  public HR2eRDMS()
  {
    errorMessage = new HtmlOutputText();
  }

  public String onePerson()
  {
    // Add event code here...
    debug = "Debug messages for onePerson function.";
    System.out.println("Employee Number: " + employeeNumber);
    connectTo();
    getManager();
    System.out.println("Manager Number: " + managerStaffNumber);
    connectToObjective();
    updateHRCaveatGroups();
    defineObjectiveUserIds();
    setupStaffCaveatGroup();
    connectToLive();
    
    createStaffFiles();
    
    disconnectFromLive();
    disconnectFromObjective();
    closeConnection();
    return "failure"; //result;
  }
  
  private void connectTo()
  {
    debug = debug + " connectTo.";
    try
    {
      Ods = new OracleDataSource();
      Ods.setURL("jdbc:oracle:thin:@xxxx:9999:xxxx");
      Ods.setUser(User);
      Ods.setPassword(Password);
      Conn = Ods.getConnection();
      debug = debug + ".. Ok.";
    }
    catch (SQLException e)
    {
      debug = debug + "Error connecting to : " + e.getMessage();
      System.out.println("Error connecting to : " + e.getMessage());
      result = "failure";
    }
  }
  
  private void closeConnection()
  {
    try
    {
      Conn.close();
    }
    catch (SQLException e)
    {
      System.out.println("Error closing connection to : " + e.getMessage());
      result = "failure";
    }
  }
  
  private void connectToLive()
  {
    debug = debug + " connectToLive.";
    try
    {
      ods = new OracleDataSource();
      ods.setURL("jdbc:oracle:thin:@xxxx:9999:xxxx");
      ods.setUser(oraUser);
      ods.setPassword(oraPassword);
      oraConn = ods.getConnection();
      debug = debug + ".. Ok.";
    }
    catch (SQLException e)
    {
      debug = debug + "Error creating connection to Live: " + e.getMessage();
      System.out.println("Error creating connection to Live: " + e.getMessage());
      result = "failure";
    }
  }
  
  private void disconnectFromLive()
  {
    try
    {
      oraConn.close();
    }
    catch (SQLException e)
    {
      System.out.println("Error closing connection to Live: " + e.getMessage());
      result = "failure";
    }
  }

  public void setEmployeeNumber(String employeeNumber)
  {
    this.employeeNumber = employeeNumber;
  }

  public String getEmployeeNumber()
  {
    return employeeNumber;
  }
  
  private void getManager()
  {
    debug = debug + " getManager.";
    try
    {
      SQL = "select distinct mgr.employee_number,"
        + "  nvl(staff.known_as, staff.first_name) || ' ' || staff.last_name staff_name, "
        + "  staff.email_address staff_email, "
        + "  mgr.email_address manager_email "
        + "from hr.per_all_people_f staff,"
        + "  hr.per_all_people_f mgr,"
        + "  hr.per_all_assignments_f asg"
        + " where sysdate between asg.effective_start_date and asg.effective_end_date"
        + "   and staff.employee_number = '" + employeeNumber + "' "
        + "   and asg.person_id = staff.person_id"
        + "   and asg.supervisor_id = mgr.person_id";
      Conn = Ods.getConnection();
      State = Conn.createStatement();
      RS = State.executeQuery(SQL);
      while( RS.next() )
      {
        managerStaffNumber = RS.getString("employee_number");
        staffName = RS.getString("staff_name");
        staffEmail = RS.getString("staff_email");
        managerEmail = RS.getString("manager_email");
      }
      debug = debug + ".. Manager Number: " + managerStaffNumber;
    }
    catch (Exception e)
    {
      debug = debug + "Error in reading data from : " + e.getMessage();
      System.out.println("Error in reading data from : " + e.getMessage());
      result = "failure";
    }

  }
  
  private void connectToObjective()
  {
    debug = debug + " connectToObjective.";
    try 
    {
      objective = new OjiApplication();
      apiSession = objective.loginUser(ojiUser, ojiPassword, ojiServer, ojiPort);
      debug = debug + ".. Ok.";
      
      ojiApiUser = (OjiUser) apiSession.getObject(ojiApiUserId);
      if (ojiApiUser != null) 
      {
        debug = debug + " ojiApiUser: " + ojiApiUser.getName();
      }
      else
      {
        debug = debug + " ojiApiUser is null.";
      }
      
      try
      {
        // set caveat groups
        hrCaveatGroup = (OjiGroup) apiSession.getObject(hrCaveatGroupId);
        hrManagerGroup = (OjiGroup) apiSession.getObject(hrManagerGroupId);
        hrOperationsGroup = (OjiGroup) apiSession.getObject(hrOperationsGroupId);
        hrPayrollGroup = (OjiGroup) apiSession.getObject(hrPayrollGroupId);
        hrTrainingGroup = (OjiGroup) apiSession.getObject(hrTrainingGroupId);
        debug = debug + " HR caveat group objects set.";
      }
      catch (Exception e)
      {
        debug = debug + "
Error in setting caveat groups: " + e.getMessage(); System.out.println("Error in setting caveat groups: " + e.getMessage()); result = "failure"; } try { // get disposal schedules newSearch = apiSession.initSearch(); //newSearch.setCriteriaRelation(OjiSearch.AND_RELATION); newSearch.setObjectSearchType(apiSession.getTypeDefnByName("disposal schedule")); newSearch.execute(); disposalSchedules = newSearch.getResults(); debug = debug + "
Disposal schedules: count=" + disposalSchedules.size(); } catch (Exception e) { debug = debug + "
Error in reading Disposal Schedules: " + e.getMessage(); System.out.println("Error in reading Disposal Schedules: " + e.getMessage()); result = "failure"; } //System.out.println("Connected to Objective..."); } catch(OjiException e) { debug = debug + "Something went wrong in eRDMS: " + e.getMessage(); e.printStackTrace(); result = "failure"; } } private void disconnectFromObjective() { apiSession.logout(); } private void updateHRCaveatGroups() { debug = debug + " updateHRCaveatGroups."; try { // clear out HR Caveat Groups collect = hrCaveatGroup.getContents(); debug = debug + " HR Caveat Group contains: " + collect.size() + " elements."; for (Object o : collect) { if( ((OjiObject) o).getTypeDefinition().getInternalName().startsWith("alias") ) { ((OjiAlias) o).delete(); } } debug = debug + " -> Now deleted!"; collect = hrOperationsGroup.getContents(); for (Object o : collect) { if( ((OjiObject) o).getTypeDefinition().getInternalName().startsWith("alias") ) { ((OjiAlias) o).delete(); } } collect = hrPayrollGroup.getContents(); for (Object o : collect) { if( ((OjiObject) o).getTypeDefinition().getInternalName().startsWith("alias") ) { ((OjiAlias) o).delete(); } } collect = hrTrainingGroup.getContents(); for (Object o : collect) { if( ((OjiObject) o).getTypeDefinition().getInternalName().startsWith("alias") ) { ((OjiAlias) o).delete(); } } } catch (Exception e) { debug = debug + "
Error in clearing HR caveat groups: " + e.getMessage(); result = "failure"; } try { SQL = "select " + " p.person_id, " + " p.employee_number, " + " p.full_name, " + " asg.assignment_id, " + " org.name, " + " p.email_address " + "from " + " hr.per_all_assignments_f asg, " + " hr.per_all_people_f p, " + " ( select team.organization_id, team.name " + " from " + " hr.hr_all_organization_units unit, " + " hr.hr_all_organization_units team, " + " hr.per_org_structure_elements elem " + " where unit.organization_id = elem.ORGANIZATION_ID_PARENT " + " and team.organization_id = elem.ORGANIZATION_ID_CHILD " + " and unit.name = 'HR Services' " + " union " + " select organization_id, name " + " from hr.hr_all_organization_units " + " where name = 'HR Services') org " + "where org.organization_id = asg.organization_id " + " and asg.person_id = p.person_id " + " and sysdate between asg.effective_start_date and asg.effective_end_date " + " and sysdate between p.effective_start_date and p.effective_end_date "; RS = State.executeQuery(SQL); //hrStaffNumber = "("; while( RS.next() ) { hrStaffNumber = RS.getString("employee_number"); hrEmailAddress = RS.getString("email_address"); debug = debug + " hrStaffNumber: " + hrStaffNumber; if( hrStaffNumber != null ) { try { debug = debug + " Searching Objective for " + hrEmailAddress; newSearch = apiSession.initSearch(); //newSearch.setCriteriaRelation(OjiSearch.AND_RELATION); newSearch.setObjectSearchType(apiSession.getTypeDefnByName("user")); newSearch.addCriteria("email address", "begins", hrEmailAddress); //newSearch.addCriteria("Staff Number", "ends", hrStaffNumber); //newSearch.addCriteria("parent group", "is", activeUsersFolder); // Active Users newSearch.execute(); results = newSearch.getResults(); debug = debug + " and found " + results.size() + " hits."; if( results != null ) { i = results.iterator(); while (i.hasNext()) { ojiStaffUser = (OjiUser) i.next(); ojiStaffUser.createAlias( hrCaveatGroup ); debug = debug + " alias " + ojiStaffUser.getObjId() + " created in HR Services "; teamName = RS.getString("name"); if( teamName.equals("HR - Operations") ) { ojiStaffUser.createAlias( hrOperationsGroup ); } if( teamName.equals("HR - Training") ) { ojiStaffUser.createAlias( hrTrainingGroup ); } if( teamName.equals("Payroll") ) { ojiStaffUser.createAlias( hrPayrollGroup ); } } } } catch (Exception e) { debug = debug + "
Problem in searching for user " + hrStaffNumber + " in Objective " + e.getMessage(); result = "failure"; } } } ojiApiUser.createAlias( hrCaveatGroup ); } catch (Exception e) { debug = debug + "
Error in reading HR teams from : " + e.getMessage(); result = "failure"; } } public void setErrorMessage(HtmlOutputText errorMessage) { this.errorMessage = errorMessage; } public HtmlOutputText getErrorMessage() { errorMessage.setValue(debug); return errorMessage; } public void defineObjectiveUserIds() { try { // get objectiver user objects if( employeeNumber.length() > 0 ) { newSearch = apiSession.initSearch(); newSearch.setCriteriaRelation(OjiSearch.AND_RELATION); newSearch.setObjectSearchType(apiSession.getTypeDefnByName("user")); //newSearch.addCriteria("Staff Number", "ends", employeeNumber); newSearch.addCriteria("email address", "begins", staffEmail); newSearch.addCriteria("parent folder", "is", "gA1"); // Active Users newSearch.execute(); results = newSearch.getResults(); i = results.iterator(); while (i.hasNext()) { ojiStaffUser = (OjiUser) i.next(); debug = debug + "
Staff Objective Id: " + ojiStaffUser.getObjId(); networkId = ojiStaffUser.getLoginId(); } } } catch (Exception e) { debug = debug + "
Error in getting objective user id: " + e.getMessage(); result = "failure"; } try { if( managerStaffNumber.length() > 0 ) { newSearch = apiSession.initSearch(); newSearch.setCriteriaRelation(OjiSearch.AND_RELATION); newSearch.setObjectSearchType(apiSession.getTypeDefnByName("user")); //newSearch.addCriteria("Staff Number", "ends", managerStaffNumber); newSearch.addCriteria("email address", "begins", managerEmail); newSearch.addCriteria("parent folder", "is", "gA1"); // Active Users newSearch.execute(); results = newSearch.getResults(); i = results.iterator(); while (i.hasNext()) { ojiManagerUser = (OjiUser) i.next(); debug = debug + "
Manager Objective Id: " + ojiManagerUser.getObjId(); managerNetworkId = ojiManagerUser.getLoginId(); } } } catch (Exception e) { debug = debug + "
Error in getting objective user id: " + e.getMessage(); result = "failure"; } } private void setupStaffCaveatGroup() { debug = debug + " setupStaffCaveatGroup"; try { // now find the caveat groups for this staff member if( ojiStaffUser != null ) { debug = debug + " network id = " + networkId; newSearch = apiSession.initSearch(); newSearch.setCriteriaRelation(OjiSearch.AND_RELATION); newSearch.setObjectSearchType(apiSession.getTypeDefnByName("group")); newSearch.addCriteria("name", "ends", "Z_" + networkId); newSearch.addCriteria("parent folder", "is", "gA32"); // HR Staff Files Security newSearch.execute(); results = newSearch.getResults(); i = results.iterator(); while (i.hasNext()) { staffCaveatGroup = (OjiGroup) i.next(); debug = debug + " Staff Caveat Id: " + staffCaveatGroup.getObjId(); } } else { debug = debug + " ojiStaffUser is null."; } } catch (Exception e) { debug = debug + " Error in getting staff caveat group: " + e.getMessage(); result = "failure"; } try { if( ojiManagerUser != null ) { newSearch = apiSession.initSearch(); newSearch.setCriteriaRelation(OjiSearch.AND_RELATION); newSearch.setObjectSearchType(apiSession.getTypeDefnByName("group")); newSearch.addCriteria("name", "ends", "Z_" + managerNetworkId); newSearch.addCriteria("parent folder", "is", "gA32"); // HR Staff Files Security newSearch.execute(); results = newSearch.getResults(); i = results.iterator(); while (i.hasNext()) { managerCaveatGroup = (OjiGroup) i.next(); debug = debug + "
Manager Caveat Id: " + managerCaveatGroup.getObjId(); } } else { debug = debug + " ojiManagerUser is null."; } } catch (Exception e) { debug = debug + "
Error in getting manager caveat group: " + e.getMessage(); result = "failure"; } try { debug = debug + " do we need to create a staffCaveatGroup?"; if( staffCaveatGroup == null ) { // we need to create it debug = debug + " networkId=" + networkId; staffCaveatGroup = apiSession.initGroup(); staffCaveatGroup.setName("Z_" + networkId); staffCaveatGroup.setParent( (OjiGroup) apiSession.getObject("gA32") ); staffCaveatGroup.create(); debug = debug + "
Staff Caveat Group Id: " + staffCaveatGroup.getObjId(); } else { // it already exists, so we should clear it out collect = staffCaveatGroup.getContents(); debug = debug + " Staff Caveat Group contains: " + collect.size() + " elements."; for (Object o : collect) { debug = debug + "
Staff Caveat Group contains: " + ((OjiObject) o).getObjId() + " name: " + ((OjiObject) o).getName() + " type: " + ((OjiObject) o).getTypeDefinition().getInternalName(); if( ((OjiObject) o).getTypeDefinition().getInternalName().startsWith("alias") ) { ((OjiAlias) o).delete(); debug = debug + " -> Deleted!"; } } } } catch (Exception e) { debug = debug + "
Error in creating staff caveat group: " + e.getMessage(); result = "failure"; } try { if( staffCaveatGroup != null ) { // Now lets fill this caveat group, starting with the API User apiSession.getCurrentUser().createAlias( staffCaveatGroup ); if( ojiStaffUser != null ) { ojiStaffUser.createAlias( staffCaveatGroup ); } if( managerCaveatGroup != null ) { managerCaveatGroup.createAlias( staffCaveatGroup ); } // now add HR services to this caveat group ((OjiGroup) apiSession.getObject("gA33")).createAlias( staffCaveatGroup ); } else { debug = debug + " staffCaveatGroup is null."; } } catch (Exception e) { debug = debug + "
Error in populating caveat group: " + e.getMessage(); result = "failure"; } } private void createStaffFiles() { // --------------------------------------------------------------------- // creating the staff files debug = debug + " createStaffFiles"; try { // find the root folder for this persons staff files staffFolders = (OjiFolder) apiSession.getObject("fA371"); newSearch = apiSession.initSearch(); newSearch.setCriteriaRelation(OjiSearch.AND_RELATION); newSearch.setObjectSearchType(apiSession.getTypeDefnByName("folder")); newSearch.addCriteria("name", "ends", employeeNumber); newSearch.addCriteria("parent folder", "is", rootStaffFilesFolder); // Root for all Staff Folders newSearch.execute(); results = newSearch.getResults(); i = results.iterator(); while (i.hasNext()) { staffFilesFolder = (OjiFolder) i.next(); debug = debug + " Staff Files Folder Id: " + staffFilesFolder.getObjId(); } if( staffFilesFolder == null ) { debug = debug + " Staff Files Folder does not exist."; } } catch (Exception e) { debug = debug + " Error in finding staff folder: " + e.getMessage(); result = "failure"; } try { if( staffFilesFolder == null ) { // doesn't exist - we need to create it debug = debug + " Creating Staff Files Folder ..."; staffFilesFolder = apiSession.initFolder(); staffFilesFolder.setName( staffName + " - " + employeeNumber ); staffFilesFolder.setParent( staffFolders ); staffFilesFolder.setCommentText( "Created by HR2Objective interface" ); staffFilesFolder.setTypeDefinition( apiSession.getTypeDefnByName("File Folder") ); staffFilesFolder.create(); debug = debug + " New Staff Files Folder Id: " + staffFilesFolder.getObjId(); } } catch (Exception e) { debug = debug + " Error in creating staff folder: " + e.getMessage(); result = "failure"; } try { SQL = "select * from snhlive.obj_staff_files where active_flag = 'Y'"; oraState = oraConn.createStatement(); oraRS = oraState.executeQuery(SQL); // get the contents of the folder for this person Collection staffFiles = staffFilesFolder.getContents(); boolean fileExists; while( oraRS.next() ) { debug = debug + " Checking file " + oraRS.getString("FILE_NAME"); // see if this file has already been created fileExists = false; if( staffFiles != null ) { //for (int f=0; f < ((Set) staffFilesFolder.getContents()).size() ; f++) for (Object o : staffFiles) { if( ((OjiObject) o).getName().startsWith( oraRS.getString("FILE_NAME") ) ) { fileExists = true; debug = debug + " -> File exists."; } } } else { debug = debug + " -> File does not exist: folder is empty."; } debug = debug + " File Name: " + oraRS.getString("FILE_NAME"); if( ! fileExists ) { debug = debug + " : file does not exist"; // not found, need to create it. OjiPhysicalFile staffFile = apiSession.initPhysicalFile( OjiPhysicalFile.VIRTUAL ); staffFile.setName( oraRS.getString("FILE_NAME") ); if( oraRS.getString("STAFF_ACCESS").startsWith("Y") ) { // grant access to staff caveat group debug = debug + " Staff Caveat group"; if( staffCaveatGroup != null ) { staffFile.addCaveat( staffCaveatGroup ); debug = debug + " access granted."; } else { // grant access to manager caveat group instead debug = debug + " Manager Caveat group"; if( managerCaveatGroup != null ) { staffFile.addCaveat( managerCaveatGroup ); debug = debug + " access granted."; } else { // grant access to only HR at least staffFile.addCaveat( (OjiGroup) apiSession.getObject(hrCaveatGroupId) ); debug = debug + " HR Caveat group access granted."; } } } else { if( oraRS.getString("MANAGER_ACCESS").startsWith("Y") ) { // grant access to manager caveat group debug = debug + " Manager Caveat group"; if( managerCaveatGroup != null ) { staffFile.addCaveat( managerCaveatGroup ); debug = debug + " access granted."; } else { // grant access to only HR staffFile.addCaveat( (OjiGroup) apiSession.getObject(hrCaveatGroupId) ); debug = debug + " HR Caveat group access granted."; } } else { // grant access to only HR staffFile.addCaveat( (OjiGroup) apiSession.getObject(hrCaveatGroupId) ); debug = debug + "HR Caveat group access granted."; } } debug = debug + " 1 "; debug = debug + " 2 "; staffFile.setParent(staffFilesFolder); debug = debug + " 3 "; staffFile.setFieldValueByName("Management Unit", "CD - HR Services (341)"); debug = debug + " 4 "; staffFile.setHomeLocation( apiSession.getObject("rA1") ); debug = debug + " 5 "; // staffFile.create(); debug = debug + "... File Created. Id: " + staffFile.getObjId(); // get disposal schedule disposalScheduleName = oraRS.getString("DISPOSAL_SCHEDULE"); for (Object o : disposalSchedules) { if( ((OjiObject) o).getName().startsWith(disposalScheduleName) ) { staffFile.refreshfromDatabase(); staffFile.setDisposalSchedule( (OjiDisposalSchedule) o ); staffFile.setDisposalScheduleSetting("Y"); staffFile.update(); debug = debug + " Disposal schedule added."; } } // access to the file is granted to the whole HR team at the caveat level // privildges are also assigned to further limit visibility to specific // teams in HR Services. if( oraRS.getString("HR_OPS_ACCESS").startsWith("Y") ) { OjiPrivilege priv = staffFile.addPrivilege(hrOperationsGroup); priv.setEdit(true); priv.setCreate(true); priv.setBrowse(true); priv.setDelete(true); } //staffFile.update(); } else { debug = debug + " File exists."; } } } catch (Exception e) { debug = debug + "
Error in setting up staff files: " + e.getMessage(); result = "failure"; } } }

Phew!

Patrick Haston
22 November 2007