Export select calender information in an ics file

[Craig Tucker]

I have seen some discussion about export to google calendar or outlook but I do not see anything done about this. I understand the security issues. This should be minimized with export only and if patient data is excluded. I would like this on my phone callendar. I would also like this on my website so potential patients can quickly see my availability.

It seems like it should be a simple process.

I would like to export select calendar information every few minutes to an ICS file to sync to another calendar. I do not want to export any patient information. I just want to export spots that are filled.

As I understand the process--

I need to export a date range (the next 60 days for example):
openemr_postcalendar_events
pc_eventDate
pc_startTime
pc_endTime

Convert this to ics format in some way (perhaps I can do this conversion in PHP?). I think I would use a dummy title like "Scheduled" instead of exporting pc_title. I think this would be 100% safe and HIPPA compliant. I think pc_title may even be ok but I do not want that on the website. Perhaps I could have two different calendar exports. One for website and one for me.

I would then mail the information out to in internet calendar every 5 min or so to refresh the calendar. I think that this is actually initiated by the calendar program calling to my server to send the information. Or can I just mail it out every 5 min.?

Am I on the right track? Has anyone already done this?

I know just a little about PHP and SQL. I learn fast and understand coding concepts. Small tips will send me off to work.

[Craig Tucker]

I looked at this:
http://www.a2zwebhelp.com/export-data-to-ics.

I created an the following directory system:
/openemr/custom/ICSexport/includes/

In /openemr/custom/ICSexport/:

export2ics.php:

<?php

include("/[your path to OpenEMR]/openemr/custom/ICSexport/includes/connection.php");

# Select Database
$sql = mysql_query( "SELECT * FROM openemr_postcalendar_events");

$ics_data = "BEGIN:VCALENDAR\n";
$ics_data .= "VERSION:2.0\n";
$ics_data .= "PRODID:PHP\n";
$ics_data .= "METHOD:PUBLISH\n";
$ics_data .= "X-WR-CALNAME:Schedule\n";

# Change the timezone if needed
$ics_data .= "X-WR-TIMEZONE:US/Pacific\n";
$ics_data .= "BEGIN:VTIMEZONE\n";
$ics_data .= "TZID:US/Pacific\n";
$ics_data .= "BEGIN:DAYLIGHT\n";
$ics_data .= "TZOFFSETFROM:-0500\n";
$ics_data .= "TZOFFSETTO:-0400\n";
$ics_data .= "DTSTART:1403086496\n";
$ics_data .= "RRULE:FREQ=YEARLY;BYMONTH=3;BYDAY=2SU\n";
$ics_data .= "TZNAME:EDT\n";
$ics_data .= "END:DAYLIGHT\n";
$ics_data .= "BEGIN:STANDARD\n";
$ics_data .= "TZOFFSETFROM:-0400\n";
$ics_data .= "TZOFFSETTO:-0500\n";
$ics_data .= "DTSTART:1403086496\n";
$ics_data .= "RRULE:FREQ=YEARLY;BYMONTH=11;BYDAY=1SU\n";
$ics_data .= "TZNAME:EST\n";
$ics_data .= "END:STANDARD\n";
$ics_data .= "END:VTIMEZONE\n";

while ($event_details = mysql_fetch_assoc($sql)) {
$id = $event_details['pc_eid'];
$start_date = $event_details['pc_eventDate'];
$start_time = $event_details['pc_startTime'];
$end_date = $event_details['pc_eventDate'];
$end_time = $event_details['pc_endTime'];
$name = $event_details['pc_title'];
$location = $event_details['pc_facility'];
$description = $event_details['pc_title'];

# Replace HTML tags
$search = array("/<br>/","/&amp;/","/&rarr;/","/&larr;/","/,/","/;/");
$replace = array("\\n","&","-->","<--","\\,","\\;"); 

$name = preg_replace($search, $replace, $name);
$location = preg_replace($search, $replace, $location);
$description = preg_replace($search, $replace, $description);

# Change TimeZone if needed
$ics_data .= "BEGIN:VEVENT\n";
$ics_data .= "DTSTART;TZID=Asia/Kolkata:".$start_date."T".$start_time."\n";
$ics_data .= "DTEND:" . $end_date . "T" . $end_time . "\n";
$ics_data .= "DTSTAMP:" . date('Ymd') . "T" . date('His') . "Z\n";
$ics_data .= "LOCATION:" . $location . "\n";
$ics_data .= "DESCRIPTION:" . $description . "\n";
$ics_data .= "SUMMARY:" . $name . "\n";
$ics_data .= "UID:" . $id . "\n";
$ics_data .= "SEQUENCE:0\n";
$ics_data .= "END:VEVENT\n";
}
$ics_data .= "END:VCALENDAR\n";

# Download the File
$filename = "event_calendar.ics";
header("Content-type:text/calendar");
header("Content-Disposition: attachment; filename=$filename");
echo $ics_data;
exit;
?>

In /openemr/custom/ICSexport/includes/
connection.php

<?php
$host="localhost";// Your Host name
$uname="openemr"; // Your Database  User name
$pass="**********"; // Your Database password 
$database = "openemr"; // Your Database name

$connection=mysql_connect($host,$uname,$pass) 
or die("Database Connection Failed");
$selectdb=mysql_select_db($database) or die("Database could not be selected"); 
$result=mysql_select_db($database)
or die("database cannot be selected <br>");
?>

With this I put the following in my Outlook Web Callendar set up:

https://www.[my path to OpenEMR]/custom/ICSexport/export2ics.php

Now my Outloook calender shows all my appointments for the day minus the client names. --But, they are all my past appointments plus new ones that are not recurring. Recurring appointments do not export accurately. I have to work that out.

In practice think I would use a different file name than export2ics.php. I would probably give it a long random mix of numbers and letters.

I think I would also make different exports for different purposes with differing levels of information.

Now I need to limit the date span it will export with some type of Where statment.

I wonder about security issues with the connection.php file given that it contains the openemr password. Are there better ways to deal with this.