I have been leveraging the CiviCase component of CiviCRM to help a crisis response organization transition to a paperless process. I was originally tasked with "fixing" a Microsoft implementation of a custom web application written in VBScript and MS SQL Server but after fighting with the former developers horrible code I finally decided to migrate the system over to an open source LAMP implementation leveraging J! 1.5 and CiviCRM 3.1.
My client is a leading provider of crisis psychiatric care and system management services to public and private entities. In addition they are a non-profit that manages mental health outreach and psychiatric recovery services (www.thesantegroup.org). Some of the services they provide include:
- Crisis Response Services
- Hotline telephone support for individuals and family members in crisis, mental health first responders to support law enforcement and emergency personnel, critical incident stress management teams
- Operate 24 / 7 / 365
- Maintain comprehensive database of community-based mental health and social service referral resources
- Provide follow-up on crisis interventions with wrap-around services
- Treatment and Recovery Services
- Medical evaluation and management
- Senior outreach services
- Provide non-emergency proactive mental health educational and counseling services to senior populations
The group includes the following different departments consisting of:
Operations Support (OPS) - handles incoming calls and creates new cases. Assigns cases to appropriate team
Mobile Crisis Team (MCT) - team is dispatched for suicidal subjects and situational crisis
In-House Intervention Team (IHIT) - provides in-home or in community setting services, appointment based
Critical Incident and Stress Management (CISM) - trained for managing stress following a critical incident
Urgent Care Center Services (UCC) - offers both assessments and medicating evaluations
The technical architecture includes leveraging Joomla 1.5 and CiviCRM 3.1. It is deployed with Rack Space dedicated hosting. In order to maintain HIPPA compliance we have 1 database server, 1 application server and a firewall server to separate the public web traffic from the database. The servers are Dell 2950's, running Red Hat Enterprise Linux Server 5 ES 64-Bit, Single Processor, Quad Core Intel 1.86 Ghz.
In addition to a number of requirements to help facilitate providing the services they offer (described above) my client also had a requirement to support multiple office locations by leveraging the same code base. The client has 5 separate offices leveraging the same code but each office using separate installations of CiviCRM...all installations running on the same server (architecture described above). With each separate office having anywhere from 5 to 20 clinicians entering cases and 1 to 2 doctors providing medical evaluations there could potentially be ~50 people hitting the server at the same time (but using separate installations) .
Two of the offices are still currently using the old VBScript+MS SQL implementation so I am in the middle of an ETL migration project. I have been using Pentaho's Kettle project (http://kettle.pentaho.com) in order to facility the migration. I will be migrating over approximately 10,000 separate cases from the old web application into CiviCRM. There is a discussion about sharing experiences regarding ETL tools here (http://civicrm.org/blogs/joe-murray/sharing-experiences-etl-tools). I had to create approximately 71 custom Case Activities for each of their forms and tasks they track which consist of ~1K+ custom fields. I configured 2 different case types (OPS and MCT) and 9 different case statuses which the clinicians use to move cases between departments.
I'm also leveraging the CiviReports components to handle the reporting requirements which I ended up creating over 100+ MySQL views to handle my client's custom reporting requirements. In total the database now has ~1600 tables+views.
Additionally, one of the requirements was to support Topaz signature pads (www.topazsystems.com) and handle electronic signatures. I ended up implementing 2 types of signature capture mechanisms; 1 is Flash based which allows a user using a touch screen enabled laptop to enter the signatures. The other is a plugin used to accept electronic signatures from the Topaz signature pads. If the Topaz signature pad is used, the signature data is stored in the database but if the Flash based on-screen signature capture method is used, the image is saved as a jpeg to the server and is created as a file attachment to the Case Activity.
Another requirement was to develop a custom desktop application which their mobile teams would use while out in the field. Because the mobile teams use ToughBook laptops and wireless broadband cards they have the potential to be disconnected from the network while out in the field at times. So the Smart Client application I built allows them to enter data into forms, save the data locally and then upload to the server at a later time, whenever they have a good network connection. Its a real simply .NET/C# desktop application which leverages the Smart Client pattern and talks to the CiviCRM REST API to upload the data. Although, I think I want to move to a cross platform solution such as ExtJs/Air/Flex in order to help consolidate development work across different platforms (e.g. Desktop, Web, Mac, PC, Mobile devices, etc).
We should be finished with the data migration and launching all the offices by the fall. One of my goals is to make sure it will be easy to maintain the code base across all the different office installations as the organization gets bigger and potentially opens up new offices thus requiring new installations. I'm interested in exploring some of CiviCRM's options for multi-site support and at one point I considered how I could rework some of the code and db schema in order to facilitate a multi-tenant data architecture. For now I have implemented some strategies to help make maintaining the code across all the offices easier....which I plan to blog about in subsequent posts!
I would love to get feed back...let me know if you are interested in poking around one of our staging or development environments ...
That sounds really cool - but, did you really use 3.1 not 3.4?
Yes, I started this project almost a year ago and have not had a chance to upgrade yet. Its running CiviCRM 3.1.beta5...
Thanks for sharing! This sounds like one of the more complex CiviCase implementations. Would be great to get more details at some point as to how the UI is working for the staff, whether you wound up making any changes or improvements in data presentation, workflows etc. Also, I think the reporting is one of the less developed areas for the CiviCase component - so it would be helpful to get an overview of some of the new reports you've implemented (perhaps with screenshots using dummy data). Assuming you're able to provide access to a public repository with the source code for some / all of the reports - that could really help other folks who need additional reporting (and might "suggest" some more reports to include out of the box).
Finally, I'm a bit wide-eyed that you could do this project on 3.1 / Joomla! given the major limitations in access control on that version. In any case, when you're able to upgrade to Civi 4.x with Joomla 1.6 - you'll have a lot more built-in options for controlling access.
thanks for sharing this, sounds like a really interesting project! The mult-site part of it makes it a challenging one I can imagine. Look forward to hearing more about it, and sharing some experiences with CiviCase.