Topcoder has been asked by a client to develop a scheduling tool for a medical setting. In a previous challenge, we developed a tool which will allow scheduling staff at the clinic to enter availability of medical personnel and to handle the actual scheduling of appointments. The staff in this clinic manage a variety of medical tests and procedures which are called products (and stored in the Product2 table in Salesforce.com). Each product is associated with different medical staff and has different time requirements. There are some other business rules as well -- for example, some products/tests can only be administered by males nurses to male patients or by females nurses to female patients.
The application as a whole - a medical practice management app - is primarily going to be developed in Salesforce.com. You will see that the data model for the application definitely has a Salesforce.com flavor to it. However, the booking engine that we’re developing here has been developed in Java as a web application using Heroku and PostgreSQL. This booking engine is integrated with data from Salesforce.com and will take advantage of the Heroku Connect and Salesforce.com Canvas functionality to integrate with SFDC. Our first submission has done an outstanding job of documenting the setup and configuration required to get the Canvas application up and running. The app has been designed to function within a SFDC frame -- less than full screen. The default width for Canvas Apps is 800px and the default height is 900px.
In this challenge we're going to tackle a series of functional enhancements and implement the business rules required by the medical practice. Here is the additional functionality we need add:
1. SEQUENCING OF ASSIGNMENTS
When our booking engine currently schedules resources for a particular product it looks to see if time is available for a particular time slot for all personnel (Contacts) and if the time available the booking flags that particular time (9:00 AM - 9:30 AM) as unavailable for the particular resources. What we really need to do is a bit more complicated. Rather than scheduling all the resources at the same time for a particular product we need to schedule them in sequence. The sequence of the resources required is determined by the capability_required.order__c value for a particular product. The capability_required.est_visit_time_mins__c defines how long a visit is required for each resource. For example, a particular test might require 30 mins from a technician (9:00 - 9:30), followed by 30 mins from a nurse the same gender as the patient (9:30-10:00 AM), followed by 30 mins for a doctor (10:00 - 10:30 AM).
2. MULTIPLE PRODUCT SUPPORT
The booking engine should be able handle the scheduling of multiple products at once. The reality of the situation is the clients of the health clinic often have multiple tests and procedures to complete in a single visit to the clinic. One of the requirements for this capability is that the customers (Contacts with record type = "Client") should be able to schedule all their procedures on a single day with a maximum gap of 45 mins between timeslots with clinic staff on a particular day.
"Multiple" products is defined as 1 or more products. In theory there is no limit on the number of products but in practice you can assume that there will be no more than 8 if this makes the coding easier.
For clarity:
2.1 A booking is made for 2 products
Product 1: 15 minutes technician THEN 30 minutes nurse THEN 30 minutes doctor
Product 2: 30 minutes nurse THEN 30 minutes doctor
Note that the order is derived product2.capability_required__c.order__c as described in the previous point
Say the booking starts at 9am and the clinic is not busy so the assignments are contiguous, then the assignments will be as follows
Assignment 1: Technician at 9am for 15 minutes (product 1)
Assignment 2: Nurse at 9:15 for 60 minutes (product 1 + 2)
Assignment 3: Doctor at 10:15 for 60 minutes (product 1 + 2)
2.2 Bounary condition where the ordering is different
Product 1: 15 minutes technician THEN 30 minutes nurse THEN 30 minutes doctor
Product 2: 15 minutes doctor THEN 30 minutes nurse THEN 30 minutes doctor
This case is hard to manage and the customer's requirements are not 100% clear on this point, so the following ordering is acceptable
Say the booking starts at 9am and the clinic is not busy so the assignments are contiguous, then the assignments will be as follows
Assignment 1: Technician at 9am for 15 minutes (product 1)
Assignment 2: Nurse at 9:15 for 60 minutes (product 1 + 2)
Assignment 3: Doctor at 10:15 for 75 minutes (product 1 + both visits for doctor 2)
The optimisation of this will be changed in the future so comments in your code must clearly state where this boundary condition is being handled.
3. GET AND POST PARMS
We have a bit of a workaround for bringing up the Availability screen powered by the booking engine. It uses a custom button which brings up the availability related to a particular case and a hard-coded product id. We need to update the application so that the product ID(s) should be accepted via GET or POST params rather than being hardcoded in the custom button. This will facilitate wrapping the application in a salesforce canvas app.
4. EXPECTED DATA VOLUMES & SCALABILITY
Expected data volumes are
- Contact (resource): ~100 records
- Capability required: ~5-10 per product
- Capability obtained: ~20-30 per contact
- ContactAvailability: ~100 resources x 4 slots per hour x 8 hours per day x 5 days per week x ~10 weeks of availability records in the future = ~160k records
The solution must be capable of scaling to 10x these volumes while remaining reasonably performant.
Where possible, use cursors and paging of results in the DB which can moved through when users click on the next & previous buttons. The use of SQL procedures, functions, view and indexed is permitted, so long as they are compatible with Heroku postgres.
Note that the application should only retrieve 5 days worth of data from the database at a time in order to ensure that there are no memory / timeout issues associated with retrieving large result sets. The use of paging / cursors for the next/prev buttons is suggested but not essential as long as there are no potential memory issues.
Final Submission Guidelines
1. Please use the code from the previous challenge as the basis for your submission. The code can be found in the following GitHub repository: https://github.com/cloudspokes/medical-booking-engine.
2. You should provide your GitHub ID to the challenge forums and you'll be added a collaborator for the repository listed above. You should fork the repository to pull down the latest version of the code. If you win the competition you'll be asked to submit a pull request.
3. There is some setup required for this challenge but the Deployment Guide does a good job of outlining the steps required to get the app up and running in an SFDC developer org.
4. As part of your submission, please provide a document which discusses the changes made and the code updated to implement each of the requirements defined above.