/**
   * Adds a new task in the user's todos. Returns the external id given by the external todo system.
   */
  @Override
  public void assignTask(Task task, User delegator) throws WorkflowException {
    String componentId = task.getProcessInstance().getModelId();
    ComponentInst compoInst = null;

    try {
      compoInst = AdminReference.getAdminService().getComponentInst(componentId);
    } catch (AdminException e) {
      throw new WorkflowException(
          "TaskManagerImpl.assignTask", "workflowEngine.EX_GET_COMPONENT_INST", e);
    }

    TodoDetail todo = new TodoDetail();
    todo.setId(task.getProcessInstance().getInstanceId());
    todo.setSpaceId(compoInst.getDomainFatherId());
    todo.setComponentId(componentId);
    todo.setName("activite : " + task.getState().getLabel(task.getUserRoleName(), "fr"));
    if (delegator != null) {
      todo.setDelegatorId(delegator.getUserId());
    } else {
      SilverTrace.error(
          "workflowEngine",
          "TaskManagerImpl.assignTask",
          "root.MSG_GEN_PARAM_VALUE",
          "Undefined delegator for new task : " + todo.getName());
    }

    TodoBackboneAccess todoBBA = new TodoBackboneAccess();
    Vector<Attendee> attendees = new Vector<Attendee>();
    if (task.getUser() != null) {
      // add todo to specified user
      attendees.add(new Attendee(task.getUser().getUserId()));
      todo.setAttendees(attendees);
      todo.setExternalId(getExternalId(task));
      todoBBA.addEntry(todo);
    } else {
      List<User> users = null;
      if (StringUtil.isDefined(task.getGroupId())) {
        // get users according to group
        users = task.getProcessInstance().getUsersInGroup(task.getGroupId());
      } else {
        // get users according to role
        users = task.getProcessInstance().getUsersInRole(task.getUserRoleName());
      }
      for (User user : users) {
        attendees.clear();
        attendees.add(new Attendee(user.getUserId()));
        todo.setAttendees(attendees);
        todo.setExternalId(getExternalId(task, user.getUserId()));
        todoBBA.addEntry(todo);
      }
    }
  }
  /** Removes a task. */
  @Override
  public void unAssignTask(Task task) throws WorkflowException {
    String componentId = task.getProcessInstance().getModelId();
    ComponentInst compoInst = null;

    try {
      compoInst = AdminReference.getAdminService().getComponentInst(componentId);
    } catch (AdminException e) {
      throw new WorkflowException(
          "TaskManagerImpl.unassignTask", "workflowEngine.EX_GET_COMPONENT_INST", e);
    }

    TodoBackboneAccess todoBBA = new TodoBackboneAccess();

    if (task.getUser() != null) {
      todoBBA.removeEntriesFromExternal(
          compoInst.getDomainFatherId(), componentId, getExternalId(task));
    } else {
      String role = task.getUserRoleName();
      List<User> usersInRole = task.getProcessInstance().getUsersInRole(role);
      for (User userInRole : usersInRole) {
        TaskImpl taskImpl =
            new TaskImpl(userInRole, role, task.getProcessInstance(), task.getState());
        todoBBA.removeEntriesFromExternal(
            compoInst.getDomainFatherId(),
            componentId,
            getExternalId(taskImpl, userInRole.getUserId()));
      }
    }
  }
  /**
   * Notify user that an action has been done
   *
   * @throws WorkflowException
   */
  @Override
  public void notifyActor(Task task, User sender, User user, String text) throws WorkflowException {
    String componentId = task.getProcessInstance().getModelId();
    List<String> userIds = new ArrayList<String>();
    if (user != null) {
      userIds.add(user.getUserId());
    } else if (StringUtil.isDefined(task.getGroupId())) {
      List<User> usersInGroup = task.getProcessInstance().getUsersInGroup(task.getGroupId());
      for (User userInGroup : usersInGroup) {
        userIds.add(userInGroup.getUserId());
      }
    } else {
      String role = task.getUserRoleName();
      List<User> usersInRole = task.getProcessInstance().getUsersInRole(role);
      for (User userInRole : usersInRole) {
        userIds.add(userInRole.getUserId());
      }
    }

    NotificationSender notifSender = notificationSenders.get(componentId);
    if (notifSender == null) {
      notifSender = new NotificationSender(componentId);
      notificationSenders.put(componentId, notifSender);
    }

    for (String userId : userIds) {
      try {
        String title = task.getProcessInstance().getTitle(task.getUserRoleName(), "");

        DataRecord data = task.getProcessInstance().getAllDataRecord(task.getUserRoleName(), "");
        text = DataRecordUtil.applySubstitution(text, data, "");

        NotificationMetaData notifMetaData =
            new NotificationMetaData(NotificationParameters.NORMAL, title, text);
        if (sender != null) {
          notifMetaData.setSender(sender.getUserId());
        } else {
          notifMetaData.setSender(userId);
        }
        notifMetaData.addUserRecipient(new UserRecipient(userId));
        String link =
            "/RprocessManager/"
                + componentId
                + "/searchResult?Type=ProcessInstance&Id="
                + task.getProcessInstance().getInstanceId()
                + "&role="
                + task.getUserRoleName();
        notifMetaData.setLink(link);
        notifSender.notifyUser(notifMetaData);
      } catch (WorkflowException e) {
        SilverTrace.warn(
            "workflowEngine",
            "TaskManagerImpl.notifyUser()",
            "workflowEngine.EX_ERR_NOTIFY",
            "user = "******"workflowEngine",
            "TaskManagerImpl.notifyUser()",
            "workflowEngine.EX_ERR_NOTIFY",
            "user = " + userId,
            e);
      }
    }
  }
  public ProcessInstance[] getProcessInstances(
      String peasId, User user, String role, String[] userRoles, String[] userGroupIds)
      throws WorkflowException {
    SilverTrace.info(
        "worflowEngine",
        "ProcessInstanceManagerImpl.getProcessInstances()",
        "root.MSG_GEN_ENTER_METHOD",
        "peasId = " + peasId + ", user = "******", role = " + role);
    Connection con = null;
    PreparedStatement prepStmt = null;
    ResultSet rs = null;
    StringBuffer selectQuery = new StringBuffer();
    Vector<ProcessInstanceImpl> instances = new Vector<ProcessInstanceImpl>();

    try {
      // Need first to make a SQL query to find all concerned instances ids
      // Due to the operator EXISTS that is not yet supported by Castor OQL->SQL
      // translator
      con = this.getConnection();

      if (role.equals("supervisor")) {
        selectQuery.append("select * from SB_Workflow_ProcessInstance instance where modelId = ?");
        prepStmt = con.prepareStatement(selectQuery.toString());
        prepStmt.setString(1, peasId);
      } else {
        selectQuery
            .append("select ")
            .append(COLUMNS)
            .append(" from SB_Workflow_ProcessInstance I ");
        selectQuery.append("where I.modelId = ? ");
        selectQuery.append("and exists (");
        selectQuery.append(
            "select instanceId from SB_Workflow_InterestedUser intUser where I.instanceId = intUser.instanceId and (");
        selectQuery.append("intUser.userId = ? ");
        if ((userRoles != null) && (userRoles.length > 0)) {
          selectQuery.append(" or intUser.usersRole in (");
          selectQuery.append(getSQLClauseIn(userRoles));
          selectQuery.append(")");
        }
        if (userGroupIds != null && userGroupIds.length > 0) {
          selectQuery.append(" or (intUser.groupId is not null");
          selectQuery.append(" and intUser.groupId in (");
          selectQuery.append(getSQLClauseIn(userGroupIds));
          selectQuery.append("))");
        }
        selectQuery.append(") and intUser.role = ? ");
        selectQuery.append("union ");
        selectQuery.append(
            "select instanceId from SB_Workflow_WorkingUser wkUser where I.instanceId = wkUser.instanceId and (");
        selectQuery.append("wkUser.userId = ? ");
        if ((userRoles != null) && (userRoles.length > 0)) {
          selectQuery.append(" or wkUser.usersRole in (");
          selectQuery.append(getSQLClauseIn(userRoles));
          selectQuery.append(")");
        }
        if (userGroupIds != null && userGroupIds.length > 0) {
          selectQuery.append(" or (wkUser.groupId is not null");
          selectQuery.append(" and wkUser.groupId in (");
          selectQuery.append(getSQLClauseIn(userGroupIds));
          selectQuery.append("))");
        }
        selectQuery.append(") and ");

        // role can be multiple (e.g: "role1,role2,...,roleN")
        selectQuery.append("( wkUser.role = ? ");
        selectQuery.append(" or wkUser.role like ? ");
        selectQuery.append(" or wkUser.role like ? ");
        selectQuery.append(" or wkUser.role like ? ");
        selectQuery.append(")");

        selectQuery.append(")");
        selectQuery.append("order by I.instanceId desc");

        SilverTrace.info(
            "worflowEngine",
            "ProcessInstanceManagerImpl.getProcessInstances()",
            "root.MSG_GEN_PARAM_VALUE",
            "SQL query = " + selectQuery.toString());

        prepStmt = con.prepareStatement(selectQuery.toString());
        prepStmt.setString(1, peasId);
        prepStmt.setString(2, user.getUserId());
        prepStmt.setString(3, role);
        prepStmt.setString(4, user.getUserId());
        prepStmt.setString(5, role);
        prepStmt.setString(6, "%," + role);
        prepStmt.setString(7, role + ",%");
        prepStmt.setString(8, "%," + role + ",%");
      }
      rs = prepStmt.executeQuery();

      ProcessInstanceImpl instance = null;
      while (rs.next()) {
        instance = new ProcessInstanceImpl();
        instance.setInstanceId(rs.getString(1));
        instance.setModelId(rs.getString(2));
        instance.setLockedByAdmin(rs.getBoolean(3));
        instance.setErrorStatus(rs.getBoolean(4));
        instance.setTimeoutStatus(rs.getBoolean(5));

        instances.add(instance);
      }

      // getHistory
      prepStmt =
          con.prepareStatement(
              "select * from SB_Workflow_HistoryStep where instanceId = ? order by id asc");

      for (int i = 0; i < instances.size(); i++) {
        instance = instances.get(i);

        prepStmt.setInt(1, Integer.parseInt(instance.getInstanceId()));

        rs = prepStmt.executeQuery();
        HistoryStepImpl historyStep = null;
        while (rs.next()) {
          historyStep = new HistoryStepImpl();
          historyStep.setId(String.valueOf(rs.getInt(2)));
          historyStep.setUserId(rs.getString(3));
          historyStep.setUserRoleName(rs.getString(4));
          historyStep.setAction(rs.getString(5));
          historyStep.setActionDate(rs.getDate(6));
          historyStep.setResolvedState(rs.getString(7));
          historyStep.setResultingState(rs.getString(8));
          historyStep.setActionStatus(rs.getInt(9));
          historyStep.setProcessInstance(instance);

          instance.addHistoryStep(historyStep);
        }
      }

      // getActiveStates
      Vector<ActiveState> states = null;
      prepStmt =
          con.prepareStatement(
              "select * from SB_Workflow_ActiveState where instanceId = ? order by id asc");

      for (int i = 0; i < instances.size(); i++) {
        instance = (ProcessInstanceImpl) instances.get(i);

        prepStmt.setInt(1, Integer.parseInt(instance.getInstanceId()));

        rs = prepStmt.executeQuery();
        ActiveState state = null;
        states = new Vector<ActiveState>();
        while (rs.next()) {
          state = new ActiveState();
          state.setId(String.valueOf(rs.getInt(1)));
          state.setState(rs.getString(3));
          state.setBackStatus(rs.getBoolean(4));
          state.setTimeoutStatus(rs.getInt(5));
          state.setProcessInstance(instance);

          states.add(state);
        }
        instance.castor_setActiveStates(states);
      }

      SilverTrace.info(
          "workflowEngine",
          "ProcessInstanceManagerImpl",
          "root.MSG_GEN_PARAM_VALUE",
          " nb instances : " + instances.size());
      return (ProcessInstance[]) instances.toArray(new ProcessInstance[0]);
    } catch (SQLException se) {
      throw new WorkflowException(
          "ProcessInstanceManagerImpl.getProcessInstances",
          "EX_ERR_CASTOR_GET_INSTANCES",
          "sql query : " + selectQuery,
          se);
    } finally {
      try {
        DBUtil.close(rs, prepStmt);
        if (con != null) con.close();
      } catch (SQLException se) {
        SilverTrace.error(
            "workflowEngine",
            "ProcessInstanceManagerImpl.getProcessInstances",
            "root.EX_RESOURCE_CLOSE_FAILED",
            se);
      }
    }
  }