interface/reports/absences_report.php

   1 <?php
   2 // Copyright (C) 2007-2009 Rod Roark <rod@sunsetsystems.com>
   3 //
   4 // This program is free software; you can redistribute it and/or
   5 // modify it under the terms of the GNU General Public License
   6 // as published by the Free Software Foundation; either version 2
   7 // of the License, or (at your option) any later version.
   8
   9 // This module is for team sports use and reports on absences by
  10 // injury type (diagnosis) for a given time period.
  11
  12 require_once("../globals.php");
  13 require_once("$srcdir/patient.inc");
  14 require_once("$srcdir/acl.inc");
  15 require_once("../../custom/code_types.inc.php");
  16 require_once("$srcdir/calendar_events.inc.php");
  17
  18 // Might want something different here.
  19 //
  20 // if (! acl_check('acct', 'rep')) die("Unauthorized access.");
  21
  22 $from_date = fixDate($_POST['form_from_date']);
  23 $to_date   = fixDate($_POST['form_to_date'], date('Y-m-d'));
  24 $form_by   = $_POST['form_by'];
  25 ?>
  26 <html>
  27 <head>
  28 <?php html_header_show();?>
  29 <title><?php xl('Absences by Diagnosis','e'); ?></title>
  30 <script type="text/javascript" src="../../library/overlib_mini.js"></script>
  31 <script type="text/javascript" src="../../library/textformat.js"></script>
  32 <script language="JavaScript">
  33  var mypcc = '<?php  echo $GLOBALS['phone_country_code'] ?>';
  34 </script>
  35 <link rel="stylesheet" href="<?php echo $css_header;?>" type="text/css">
  36 </head>
  37
  38 <body class="body_top">
  39
  40 <!-- Required for the popup date selectors -->
  41 <div id="overDiv" style="position:absolute; visibility:hidden; z-index:1000;"></div>
  42
  43 <center>
  44
  45 <h2><?php  xl('Days and Games Missed','e'); ?></h2>
  46
  47 <form name='theform' method='post' action='absences_report.php'>
  48
  49 <table border='0' cellpadding='3'>
  50
  51  <tr>
  52   <td>
  53   <?php  xl('By:','e'); ?>
  54    <input type='radio' name='form_by' value='d'
  55     <?php  echo ($form_by == 'p') ? '' : 'checked' ?> /><?php  xl('Diagnosis','e'); ?>&nbsp;
  56    <input type='radio' name='form_by' value='p'
  57     <?php  echo ($form_by == 'p') ? 'checked' : '' ?> /><?php  xl('Player','e'); ?> &nbsp;
  58    <?php  xl('From:','e'); ?>
  59    <input type='text' name='form_from_date' id='form_from_date' size='10' value='<?php  echo $from_date ?>'
  60     onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)' title='yyyy-mm-dd'>
  61    <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
  62     id='img_from_date' border='0' alt='[?]' style='cursor:pointer'
  63     title='<?php xl('Click here to choose a date','e'); ?>'>
  64    &nbsp;<?php  xl('To:','e'); ?>
  65    <input type='text' name='form_to_date' id='form_to_date' size='10' value='<?php  echo $to_date ?>'
  66     onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)' title='yyyy-mm-dd'>
  67    <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
  68     id='img_to_date' border='0' alt='[?]' style='cursor:pointer'
  69     title='<?php xl('Click here to choose a date','e'); ?>'>
  70    &nbsp;
  71    <input type='submit' name='form_refresh' value='<?php  xl('Refresh','e'); ?>'>
  72   </td>
  73  </tr>
  74
  75  <tr>
  76   <td height="1">
  77   </td>
  78  </tr>
  79
  80 </table>
  81
  82 <table border='0' cellpadding='1' cellspacing='2' width='98%'>
  83
  84  <tr bgcolor="#dddddd">
  85 <?php   if ($form_by == 'p') { ?>
  86   <td class="dehead">
  87    <?php  xl('Name','e'); ?>
  88   </td>
  89 <?php  } else { ?>
  90   <td class="dehead">
  91    <?php  xl('Code','e'); ?>
  92   </td>
  93   <td class="dehead">
  94    <?php  xl('Description','e'); ?>
  95   </td>
  96 <?php  } ?>
  97   <!--
  98   <td class='dehead' align='right'>
  99    <?php  xl('Issues','e'); ?>
 100   </td>
 101   -->
 102   <td class='dehead' align='right'>
 103    <?php  xl('Days Missed','e'); ?>
 104   </td>
 105   <td class='dehead' align='right'>
 106    <?php  xl('Games Missed','e'); ?>
 107   </td>
 108  </tr>
 109 <?php
 110  if ($_POST['form_refresh']) {
 111   $from_date = fixDate($_POST['form_from_date']);
 112   $to_date   = fixDate($_POST['form_to_date'], date('Y-m-d'));
 113   $areport = array();
 114
 115   $eres = sqlStatement("SELECT e.pc_eid, e.pc_apptstatus, " .
 116     "e.pc_eventDate, e.pc_endDate, e.pc_startTime, " .
 117     "e.pc_duration, e.pc_recurrtype, e.pc_recurrspec, " .
 118     "c.pc_catdesc " .
 119     "FROM openemr_postcalendar_events AS e " .
 120     "JOIN openemr_postcalendar_categories AS c ON " .
 121     "c.pc_catdesc LIKE 'Squad=%' AND c.pc_catid = e.pc_catid " .
 122     "WHERE ((e.pc_endDate >= '$from_date' AND e.pc_eventDate <= '$to_date') OR " .
 123     "(e.pc_endDate = '0000-00-00' AND e.pc_eventDate >= '$from_date' AND " .
 124     "e.pc_eventDate <= '$to_date'))");
 125
 126   while ($erow = sqlFetchArray($eres)) {
 127     $estart = $erow['pc_eventDate'];
 128     if ($estart < $from_date) $estart = $from_date;
 129     $eend = ($erow['pc_endDate'] > '0000-00-00') ? $erow['pc_endDate'] : $erow['pc_eventDate'];
 130     if ($eend > $to_date) $eend = $to_date;
 131     $time = mktime(0, 0, 0, substr($estart, 5, 2),
 132       substr($estart, 8, 2), substr($estart, 0, 4));
 133     $endtime = mktime(0, 0, 0, substr($eend, 5, 2),
 134       substr($eend, 8, 2), substr($eend, 0, 4)) + (24 * 60 * 60);
 135
 136     $eid = $erow['pc_eid'];
 137     $squad = substr($erow['pc_catdesc'], 6);
 138     $duration = $erow['pc_duration'] / 60;
 139
 140     for (; $time < $endtime; $time += 24 * 60 * 60) {
 141       $date = date('Y-m-d', $time);
 142       if (!eventMatchesDay($erow, $date)) continue;
 143
 144       $pres = sqlStatement("SELECT pd.lname, pd.fname, pd.mname, " .
 145         "pd.pid, pe.minutes, pe.fitness_related, df.issue_id, l.diagnosis " .
 146         "FROM patient_data AS pd " .
 147         "LEFT JOIN player_event AS pe ON pe.pid = pd.pid AND pe.date = '$date' AND pe.pc_eid = '$eid' " .
 148         "LEFT JOIN daily_fitness AS df ON df.pid = pd.pid AND df.date = '$date' " .
 149         "LEFT JOIN lists AS l ON l.id = df.issue_id " .
 150         "WHERE pd.squad = '$squad'");
 151
 152       while ($prow = sqlFetchArray($pres)) {
 153
 154         // Each iteration of this loop is for a particular player and a
 155         // particular event on a particular day.
 156         //
 157         // For each ($key,$date) accumulate event minutes and missed minutes.
 158         // Then we can total fractions of days missed just before sorting.
 159
 160         $fitness_related = isset($prow['fitness_related']) ? $prow['fitness_related'] :
 161           !empty($prow['issue_id']);
 162
 163         if ($form_by == 'p') {
 164           $key = trim($prow['lname'] . ', ' . $prow['fname'] . ' ' . $prow['mname']);
 165         } else {
 166           $key = $fitness_related ? $prow['diagnosis'] : 'z';
 167         }
 168         if (empty($areport[$key])) {
 169           $areport[$key] = array();
 170           $areport[$key]['days'] = array();
 171           $areport[$key]['gmissed'] = 0;
 172         }
 173         if (empty($areport[$key]['days'][$date])) {
 174           $areport[$key]['days'][$date] = array();
 175           $areport[$key]['days'][$date][0] = 0; // total event minutes
 176           $areport[$key]['days'][$date][1] = 0; // missed event minutes
 177         }
 178
 179         $missed = isset($prow['minutes']) ? ($duration - $prow['minutes']) : 0;
 180
 181         if ($duration && $erow['pc_apptstatus'] == 'G') { // if this is a game event
 182           $areport[$key]['gmissed'] += $missed / $duration;
 183         }
 184
 185         // echo "<!-- key='$key' eid='$eid' date='$date' duration='$duration' missed='$missed' -->\n"; // debugging
 186
 187         // Event minutes and those missed are totaled by day, so that we
 188         // can later total up the fractions of days missed for each $key.
 189         $areport[$key]['days'][$date][0] += $duration;
 190         $areport[$key]['days'][$date][1] += $missed;
 191       }
 192     }
 193   }
 194
 195   // echo "<!--\n";      // debugging
 196   // print_r($areport);
 197   // echo "-->\n";
 198
 199   foreach ($areport as $key => $arr) {
 200     $areport[$key]['dmissed'] = 0;
 201     foreach ($arr['days'] as $date => $pair) {
 202       if ($pair[0]) {
 203         $areport[$key]['dmissed'] += $pair[1] / $pair[0];
 204       }
 205     }
 206     unset($areport[$key]['days']);
 207   }
 208
 209   /*******************************************************************
 210   $query = "SELECT lists.diagnosis, lists.pid, lists.type, " .
 211     "lists.extrainfo AS gmissed, " .
 212     "lists.begdate, lists.enddate, lists.returndate, " .
 213     "pd.lname, pd.fname, pd.mname " .
 214     "FROM lists " .
 215     "JOIN patient_data AS pd ON pd.pid = lists.pid " .
 216     "WHERE ( lists.returndate IS NULL OR lists.returndate >= '$from_date' ) AND " .
 217     "( lists.begdate IS NULL OR lists.begdate <= '$to_date' )" .
 218     "ORDER BY pd.lname, pd.fname, pd.mname, lists.pid, lists.begdate";
 219   $res = sqlStatement($query);
 220
 221   $areport = array();
 222   $last_listid  = 0;
 223   $last_pid     = 0;
 224   $last_endsecs = 0;
 225
 226   while ($row = sqlFetchArray($res)) {
 227     $thispid = $row['pid'];
 228     // Compute days missed.  Force non-overlap of multiple issues for the
 229     // same player.  This logic assumes sorting on begdate within pid.
 230     $begsecs = $row['begdate'] ? strtotime($row['begdate']) : 0;
 231     $endsecs = $row['returndate'] ? strtotime($row['returndate']) : time();
 232     if ($thispid == $last_pid) {
 233       if ($begsecs < $last_endsecs) {
 234         $begsecs = $last_endsecs;
 235       }
 236     }
 237     else {
 238       $last_pid = $thispid;
 239       $last_endsecs = 0;
 240       $ptname = trim($row['lname'] . ', ' . $row['fname'] . ' ' . $row['mname']);
 241     }
 242     $daysmissed = 0;
 243     if ($row['begdate']) {
 244       if ($begsecs > $endsecs) $begsecs = $endsecs;
 245       if ($last_endsecs < $endsecs) $last_endsecs = $endsecs;
 246       $daysmissed = round(($endsecs - $begsecs) / (60 * 60 * 24));
 247     }
 248     if ($form_by == 'p') {
 249       $key = $ptname;
 250     } else {
 251       $key = $row['diagnosis'];
 252     }
 253     if (empty($areport[$key])) {
 254       $areport[$key] = array();
 255       $areport[$key]['count']   = 0;
 256       $areport[$key]['dmissed'] = 0;
 257       $areport[$key]['gmissed'] = 0;
 258     }
 259     $areport[$key]['count']   += 1;
 260     $areport[$key]['dmissed'] += $daysmissed;
 261     $areport[$key]['gmissed'] += $row['gmissed'];
 262   }
 263   *******************************************************************/
 264
 265   ksort($areport);
 266
 267   foreach ($areport as $key => $row) {
 268 ?>
 269
 270  <tr>
 271 <?php if ($form_by == 'p') { ?>
 272   <td class='detail'>
 273    <?php echo $key ?>
 274   </td>
 275 <?php } else { ?>
 276   <td class='detail'>
 277    <?php echo $key == 'z' ? '' : $key; ?>
 278   </td>
 279   <td class='detail'>
 280 <?php
 281     if (empty($key))
 282       echo xl('Undiagnosed');
 283     else if ($key == 'z')
 284       echo xl('No injury/illness');
 285     else
 286       echo lookup_code_descriptions($key);
 287 ?>
 288   </td>
 289 <?php  } ?>
 290   <!--
 291   <td class='detail' align='right'>
 292    <?php // echo $row['count'] ?>
 293   </td>
 294   -->
 295   <td class='detail' align='right'>
 296    <?php echo sprintf('%0.2f', $row['dmissed']) ?>
 297   </td>
 298   <td class='detail' align='right'>
 299    <?php echo sprintf('%0.2f', $row['gmissed']) ?>
 300   </td>
 301  </tr>
 302 <?php
 303   }
 304  }
 305 ?>
 306
 307 </table>
 308 </form>
 309 </center>
 310 </body>
 311 <!-- stuff for the popup calendar -->
 312 <style type="text/css">@import url(../../library/dynarch_calendar.css);</style>
 313 <script type="text/javascript" src="../../library/dynarch_calendar.js"></script>
 314 <?php include_once("{$GLOBALS['srcdir']}/dynarch_calendar_en.inc.php"); ?>
 315 <script type="text/javascript" src="../../library/dynarch_calendar_setup.js"></script>
 316 <script language="Javascript">
 317  Calendar.setup({inputField:"form_from_date", ifFormat:"%Y-%m-%d", button:"img_from_date"});
 318  Calendar.setup({inputField:"form_to_date", ifFormat:"%Y-%m-%d", button:"img_to_date"});
 319 </script>
 320 </html>