Export select calender information in an ics file

Developers
2014-08-02
2014-08-02
  • Craig Tucker

    Craig Tucker - 2014-08-02

    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

    Craig Tucker - 2014-08-02

    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.

     
    Last edit: Craig Tucker 2014-08-04

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks