interface/reports/absences_report.php

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