So, in the day job I am having to integrate our custom software with the task management software suite ActiveCollab. Depending on what you are wanting to do, this can be a frustrating experience, or an incredibly frustrating experience.

The problem

While ActiveCollab has an API, it is poorly documented and quirky. One of the more glaring omissions with the API is there is no way to assign a user to an already existing ticket. Since that is one of the core needs of our site, I needed to find a way around this limitation. Once I had, I thought I would share it with you internet, in hopes of making someone else's life a little bit easier.

The solution

At this time, the only way I have found to do this is to interact with the database directly. To successfully assign a user to a ticket, you need to do the following:

1. Add a record to the project_users table in your ac db.
2. Add a record to the assignments table in your ac db.
3. Add a record to the subscriptions table in your ac db.

So first lets update the project_users table.

For this part of the process we need to create a record that ties an AC user account to a project in ac and set the role id and permissions.

For my app this is pretty straight foward. We aren't currently using roles, and the permissions set is the same for every user, save the admin tier. So for us, we simply need to issue the following:

"INSERT INTO project_users (user_id, project_id, role_id, permissions) VALUES($user, $project, 0, 'N;')"

Where $user is the ID of the activecollab user and $project is the activecollab project ID. As you can see I am passing 0 and N; for the last two, since we aren't really using them.

So what this does is add our user to the project that our ticket is attached to in AC. The user has to be a part of the project before we can assign them to a ticket in the project. Next, we assign them to the ticket.

So this is how to assign a user to a ticket

Now that our users is part of the project, we can assign them to a ticket that has been added to that project.

"INSERT INTO assignments (user_id, object_id, is_owner) VALUES($user, $id, 1)"

I think this is pretty self explanatory, but just in case its not I will break it down for you. This SQL statement says to create a new record in the assignments table where the user_id matches our user, the object_id matches the ID of our ticket (this is obviously important so don't pass the project id for instance) and finally is this person the owner of the ticket. 1 means yes, 0 means no.

At this point we could stop. Our user has been assigned to the project and the ticket and all is well with the world. However there is one bit left that we might want to do.

ActiveCollab also sets up the owner of a ticket to be notified when there are changes to the ticket. If we want to do this, then you need to also fire off this last SQL statement.

Subscribe our user to updates

To make sure our newly assigned ticket owner gets any and all updates or comments to said ticket, we need to do the following:

"INSERT INTO subscriptions (user_id, parent_id) VALUES($user, $id)"

So at this point we have created a new record that connects our user to the ticket and tells ActiveCollab to send him/her emails whenever the ticket changes.

And in parting

There are some really nice aspects of ActiveCollab. Once you figure out the API it gives you a great amount of freedom and power, in the places that they have provided said power and freedom.

For those places that they don't, you can always go to the DB directly. I hope this has helped someone find a solution to this problem a lot faster than I did.