Challenge Overview
Topcoder has been asked by a client to develop a scheduling tool for a medical setting. The tool needs to 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. 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 attached data model definitely has a Salesforce.com flavor to it. However, the booking engine that we’re developing here is going to be developed in Java as a web application using Heroku and PostgreSQL. Ultimately the booking engine will be integrated with data from Salesforce.com and will take advantage of the Heroku Connect and Salesforce.com Canvas functionality to integrate with SFDC.
The app will be hosted in a SFDC frame so you should design the screens to be less than full screen. The default width for Canvas Apps is 800px and the default height is 900px. These can be adjusted but just providing those stats to indicate that we should be conservative with screen space. Here are the screens we’ll be developing and the functionality that will be associated with each page:
-
Work Rota Entry - The scheduling staff at the clinic need a simple interface to enter availability data for Doctors, Nurses, and Technicians. The page should allow the staff to select one of the medical staff. Medical staff are identified by Contact records with the Record Type of ‘Acme Resource’ in the interface. The data fields for the Work Rota object and entry form should correspond to the following fields in the database:
ScheduleStartDate: Date
ScheduleEndDate: Date
WorkingHoursStartTime: Time
WorkingHoursEndTime: Time
SiteId: FK to Site object
DayOfTheWeek: int(1): 0 = Sunday .. 6 = Saturday
AutomaticallyScheduleBreaks: Boolean - A flag which indicates if the application should automatically schedule a lunch hour after four hours of work and a rest break for 15 minutes every 2 hours.
-
When the scheduling staff member clicks save, the application should save records to the WorkRota table and set the Active flag on the WorkRota record to true.
-
When an active rota is saved (step 1 above) the application must create ContactAvailability records, 4 per hour (representing 15 minutes timeslots) from ScheduleStartDate to ScheduleEndDate
-
The relevant ContactAvailablity records between WorkingHoursStartTime and WorkingHoursEndTime have Status = Available
-
Outside of these times, Status = Unavailable
-
The ContactAvailability table is the real heart of the application functionality where resource capacity is documented and managed.
-
Outside of the booking engine, medical tests and services will be selected for particular patients. These medical tests and services are stored in the Product2 table in Salesforce.com. This table will be available as readonly in the PostgreSQL data model as well. The patient for the clinic are also stored as Contact records. These records have a Record Type = ‘Client’. Both the patient Contact Ids, the Product Ids, and a Case Id (18 character strings for SFDC primary keys) will be passed to our application through request parameters (e.g. /booking?contactid=0035000000XRFYl&productid=a3y500000000NEa&caseid=5005000000iDhwH). For the initial version of the app, we should just process a single product id for single patient but in future versions of the app there will be multiple tests per individual.
-
The task of the booking service is to combine the availability data associated with the medical staff with the time required to complete a particular product to determine when there are available appointments. We’re hoping to provide a simple interface like the following so staff can create the bookings:
Step 1:
Step 2:
Step 3:
The current plan is to model the schedule in the database using the fields defined ContactAvailability table. The schema for the table is defined below. Each row in the table represents the availability status of each contact for a given date and 15 minute timeslot. Therefore for each contact there will be 4 x 24 records per day. If you have other ideas for modeling and managing the booking process you may make suggestions in the project forums.
There is business logic associated with each of the records in the Product2 table. The business logic is tracked in the Capability_required__c and the Capability_master__c tables. For example in the sample data documented here:
Rows 1 and 2 of the sample Capability_required data document the resources required for completing an International Assignment Medical. This requires 30 minutes of technician time and 15 minutes of doctor time.
The application should scan the ContactAvailability table for the designated time period to show where there might be available timeslots for the procedures selected.
4. The Case object in SFDC is being used to host the appointment data in the system. It will join the time returned by the booking engine with the patient and product information. When a booking is confirmed in the UI above, the application will:
1. Create assignment records 1 per resource needed
a. Assignment__c.ownerId = contact.userId__c
b. Booking__c = case.Id
c. Status = “Confirmed”
2. Update case
a. Status = “Confirmed”
b. Appointment time = Date & time of appointment
c. Contact__c = Contact ID of the client attending the clinic
3. Update ContactAvailabilty record(s), setting status = “Unavailable” for the relevant contacts and updating Why to the ID of the relevant assignment record
Data dictionary:
-
ContactAvailability: Heroku PG table: not in SFDC
-
ScheduleId: auto increment, PK
-
ContactId: varchar(18)
-
Date: date indexed
-
Slot start time: time
-
Slot end time: time = slot start time + 15 (configurable via system global setting)
-
Status = {Available | Unavailable}
-
Why: varchar(18) ID of assignment record
-
-
WorkRota: Heroku PG table: not in SFDC
-
Id: auto increment PK
-
ScheduleStartDate: Date
-
ScheduleEndDate: Date
-
WorkingHoursStartTime: Time
-
WorkingHoursEndTime: Time
-
SiteId: FK to Site object
-
DayOfTheWeek: int(1): 0 = Sunday .. 6 = Saturday
-
AutomaticallyScheduleBreaks: Boolean - A flag which indicates if the application should automatically schedule a lunch hour after four hours of work and a rest break for 15 minutes every 2 hours.
-
Active: Boolean
-
-
Assignment: Salesforce object
-
Id: varchar(18): PK: Salesforce ID
-
Case__c: varchar(18): FK: Salesforce ID of parent case in M:D relationship
-
Assigned_to__c: varchar(18): FK: SFDC ID of resource (contact)
-
Estimated_start_time__c: dateTime
-
Estimated_end_time__c: dateTime
-
Status: varchar(255): picklist = “Confirmed | Cancelled”
-
Sample data for the objects in this data model can be found on this google sheet
A salesforce ant style metadata package can be found here.
Alternatively click on this link to install a salesforce unmanaged package into you salesforce developer edition org. This will also seed your org with some sample data http://apps.topcoder.com/forums/?module=Announcement&annID=2949
Final Submission Guidelines
-
Please provide your source code, sql scripts, and deployment code in a .zip file.
-
You should use Java based web technologies to complete this application. A .war file is mandatory deliverable for this application. It will be deployed to Heroku. Your application should be built using the OpenJDK 1.8 (Java 8).
-
You have flexibility about the front-end web frameworks and javascript used for the application.
-
Maven is the required build tool for this application.
-
Your submission should include unit test code. In future challenges, we will be developing additional functionality using this codebase and robust testing to validate existing functionality will be extremely helpful.
-
You should provide detailed instructions on how to build, run and deploy your application.
- No screen sharing video is required for this challenge.