Appointment schedules

Feb 24 at 2:47 PM
Edited Feb 24 at 3:01 PM
Hi,

I know this can't be done with ESAPI v11, but can anyone recommend a simple way to extract patient appointment scheduling information?

Thanks!
Feb 24 at 7:12 PM
Do you have a class to query the database?

I use the following two queries. Its been awhile since Ive investigated the utility of these and I know they can definitely be improved upon (and this is probably not the easiest way to do it). At the very least they could be augmented to include the 'LookupTable'. (In the database, there is a table called 'LookupTable' that stores name changes for various things, one of which is activities. These queries do not use the lookuptable, so the ActivityCode returned (i.e. the activity name) might be a historic name if it has changed since the activity was created. ) If you cant get the PatientSer, then the methods could easily be changed to use PatientId (mrn).

Another way is to use visual studio to create an EDM and use that for database access. I pretty sure someone published a demo of how to set this up. Then you could use the scheduleActivities.Where(...) to filter by PatientSer.


Here are the queries
public virtual string ScheduleForSinglePatientBySer(Int64 PatSer)
    {
        return "SELECT DISTINCT ScheduledActivity.ScheduledActivitySer,  Activity.ActivityCode,ScheduledActivity.ScheduledStartTime," +
                "ScheduledActivity.ScheduledActivityCode,ScheduledActivity.PatientSer," +
                "ActualStartDate,ActualEndDate, ScheduledActivity.HstryDateTime, Activity.ActivitySer," +
                "Patient.PatientSer,Patient.LastName,Patient.FirstName, Patient.PatientId " +

                "FROM ScheduledActivity " +

                "JOIN Patient on ScheduledActivity.PatientSer=Patient.PatientSer " +
                "JOIN ActivityInstance on ScheduledActivity.ActivityInstanceSer=ActivityInstance.ActivityInstanceSer " +
                "JOIN Activity on ActivityInstance.ActivitySer=Activity.ActivitySer " +
                "JOIN Attendee on ActivityInstance.ActivityInstanceSer=Attendee.ActivityInstanceSer " +
                "JOIN Resource on Attendee.ResourceSer=Resource.ResourceSer " +

                "WHERE ScheduledActivity.ObjectStatus = 'Active' and Patient.PatientSer=" + PatSer +

                " Order By ScheduledActivity.ScheduledStartTime ASC";


    }

    public virtual string TaskPadForSinglePatientBySer(Int64 patSer)
    {
        return "SELECT DISTINCT NonScheduledActivity.NonScheduledActivitySer,   Activity.ActivityCode, NonScheduledActivity.DueDateTime," +
                "NonScheduledActivity.NonScheduledActivityCode,NonScheduledActivity.PatientSer, "+
                "NonScheduledActivity.HstryDateTime, NonScheduledActivity.ActivityNote,Activity.ActivitySer,"+
                "Patient.PatientSer,Patient.LastName,Patient.FirstName, Patient.PatientId "+

                "FROM NonScheduledActivity "+

                "JOIN Patient on NonScheduledActivity.PatientSer=Patient.PatientSer "+
                "JOIN ActivityInstance on NonScheduledActivity.ActivityInstanceSer=ActivityInstance.ActivityInstanceSer "+
                "JOIN Activity on ActivityInstance.ActivitySer=Activity.ActivitySer "+
                "JOIN Attendee on ActivityInstance.ActivityInstanceSer=Attendee.ActivityInstanceSer "+
                "JOIN Resource on Attendee.ResourceSer=Resource.ResourceSer "+

                "WHERE NonScheduledActivity.ObjectStatus = 'Active' and Patient.PatientSer="+ patSer +

                " Order By NonScheduledActivity.DueDateTime ASC"; 

    }