interface/reports/daily_summary_report.php

   1 <?php
   2 /**
   3  *  Daily Summary Report. (/interface/reports/daily_summary_report.php)
   4  *
   5  *  This report shows date wise numbers of the Appointments Scheduled,
   6  *  New Patients, Visited patients, Total Charges, Total Co-pay and Balance amount for the selected facility & providers wise.
   7  *
   8  * @package   OpenEMR
   9  * @link      http://www.open-emr.org
  10  * @author    Rishabh Software
  11  * @author    Brady Miller <brady.g.miller@gmail.com>
  12  * @copyright Copyright (c) 2016 Rishabh Software
  13  * @copyright Copyright (c) 2017 Brady Miller <brady.g.miller@gmail.com>
  14  * @license   https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
  15  */
  16
  17
  18 require_once("../globals.php");
  19 require_once "$srcdir/options.inc.php";
  20 require_once "$srcdir/appointments.inc.php";
  21
  22 use OpenEMR\Core\Header;
  23 use OpenEMR\Services\FacilityService;
  24
  25 $facilityService = new FacilityService();
  26
  27 $from_date = isset($_POST['form_from_date']) ? DateToYYYYMMDD($_POST['form_from_date']) : date('Y-m-d'); // From date filter
  28 $to_date = isset($_POST['form_to_date']) ? DateToYYYYMMDD($_POST['form_to_date']) : date('Y-m-d');   // To date filter
  29 $selectedFacility = isset($_POST['form_facility']) ? $_POST['form_facility'] : "";  // facility filter
  30 $selectedProvider = isset($_POST['form_provider']) ? $_POST['form_provider'] : "";  // provider filter
  31 ?>
  32
  33 <html>
  34     <head>
  35
  36         <title><?php echo xlt('Daily Summary Report'); ?></title>
  37
  38         <?php Header::setupHeader(['datetime-picker', 'report-helper']); ?>
  39
  40         <script type="text/javascript">
  41             function submitForm() {
  42                 var fromDate = $("#form_from_date").val();
  43                 var toDate = $("#form_to_date").val();
  44
  45                 if (fromDate === '') {
  46                     alert("<?php echo xls('Please select From date'); ?>");
  47                     return false;
  48                 }
  49                 if (toDate === '') {
  50                     alert("<?php echo xls('Please select To date'); ?>");
  51                     return false;
  52                 }
  53                 if (Date.parse(fromDate) > Date.parse(toDate)) {
  54                     alert("<?php echo xls('From date should be less than To date'); ?>");
  55                     return false;
  56                 }
  57                 else {
  58                     $("#form_refresh").attr("value", "true");
  59                     $("#report_form").submit();
  60                 }
  61             }
  62
  63             $( document ).ready(function(){
  64                 $('.datepicker').datetimepicker({
  65                     <?php $datetimepicker_timepicker = false; ?>
  66                     <?php $datetimepicker_showseconds = false; ?>
  67                     <?php $datetimepicker_formatInput = true; ?>
  68                     <?php require($GLOBALS['srcdir'] . '/js/xl/jquery-datetimepicker-2-5-4.js.php'); ?>
  69                     <?php // can add any additional javascript settings to datetimepicker here; need to prepend first setting with a comma ?>
  70                 });
  71             });
  72
  73         </script>
  74
  75     </head>
  76
  77     <body class="body_top">
  78
  79         <span class='title'><?php echo xlt('Daily Summary Report'); ?></span>
  80         <!-- start of search parameters -->
  81         <form method='post' name='report_form' id='report_form' action='' onsubmit='return top.restoreSession()'>
  82             <div id="report_parameters">
  83                 <table class="tableonly">
  84                     <tr>
  85                         <td width='745px'>
  86                             <div style='float: left'>
  87                                 <table class='text'>
  88                                     <tr>
  89                                         <td class='control-label'><?php echo xlt('Facility'); ?>:</td>
  90                                         <td><?php dropdown_facility($selectedFacility, 'form_facility', false); ?></td>
  91                                         <td class='control-label'><?php echo xlt('From'); ?>:</td>
  92                                         <td>
  93                                             <input type='text' name='form_from_date' id="form_from_date"
  94                                                    class='datepicker form-control'
  95                                                    size='10' value='<?php echo oeFormatShortDate($from_date); ?>'>
  96                                         </td>
  97                                         <td class='control-label'><?php echo xlt('To'); ?>:</td>
  98                                         <td>
  99                                             <input type='text' name='form_to_date' id="form_to_date"
 100                                                    class='datepicker form-control'
 101                                                    size='10' value='<?php echo oeFormatShortDate($to_date); ?>'>
 102                                         </td>
 103                                         <td class='control-label'><?php echo xlt('Provider'); ?>:</td>
 104                                         <td>
 105                                             <?php
 106                                             generate_form_field(array('data_type' => 10, 'field_id' => 'provider',
 107                                             'empty_title' => '-- All Providers --'), $selectedProvider);
 108                                             ?>
 109                                         </td>
 110                                 </table>
 111                             </div>
 112                         </td>
 113                         <td align='left' valign='middle' height="100%">
 114                             <table style='border-left: 1px solid; width: 100%; height: 100%'>
 115                                 <tr>
 116                                     <td>
 117                                         <div class="text-center">
 118                                             <div class="btn-group" role="group">
 119                                                 <a href='#' class='btn btn-default btn-save' onclick='return submitForm();'>
 120                                                     <?php echo xlt('Submit'); ?>
 121                                                 </a>
 122                                                 <a href='' class="btn btn-default btn-refresh" id='new0' onClick=" top.restoreSession(); window.location = window.location.href;">
 123                                                     <?php echo xlt('Reset'); ?>
 124                                                 </a>
 125                                             </div>
 126                                         </div>
 127                                     </td>
 128                                 </tr>
 129                             </table>
 130                         </td>
 131                     </tr>
 132                 </table>
 133                 <input type='hidden' name='form_refresh' id='form_refresh' value='' />
 134             </div>
 135         </form>
 136         <!-- end of search parameters -->
 137
 138         <?php
 139         $dateSet = $facilitySet = 0;
 140         if (!empty($from_date) && !empty($to_date)) {
 141             $dateSet = 1;
 142         }
 143
 144         if (isset($selectedFacility) && !empty($selectedFacility)) {
 145             $facilitySet = 1;
 146         }
 147
 148         // define all the variables as initial blank array
 149         $facilities = $totalAppointment = $totalNewPatient = $totalVisit = $totalPayment = $dailySummaryReport = $totalPaid = array();
 150
 151         // define all the where condition variable as initial value set 1=1
 152         $whereTotalVisitConditions = $whereTotalPaymentConditions = $wherePaidConditions = $whereNewPatientConditions = '1 = 1 ';
 153
 154         // fetch all facility from the table
 155         $facilityRecords = $facilityService->getAll();
 156         foreach ($facilityRecords as $facilityList) {
 157             if (1 === $facilitySet && $facilityList['id'] == $selectedFacility) {
 158                 $facilities[$facilityList['id']] = $facilityList['name'];
 159             }
 160
 161             if (empty($selectedFacility)) {
 162                 $facilities[$facilityList['id']] = $facilityList['name'];
 163             }
 164         }
 165
 166         // define provider and facility as null
 167         $providerID = $facilityID = null;
 168         // define all the bindarray variables as initial blank array
 169         $sqlBindArrayAppointment = $sqlBindArrayTotalVisit = $sqlBindArrayTotalPayment = $sqlBindArrayPaid = $sqlBindArrayNewPatient = array();
 170
 171         // make all condition on by default today's date
 172         if ($dateSet != 1 && $facilitySet != 1) {
 173             $whereNewPatientConditions .= ' AND DATE(`OPE`.`pc_eventDate`) = ? ';
 174             array_push($sqlBindArrayNewPatient, date("Y-m-d"));
 175             $whereTotalVisitConditions .= ' AND DATE(`fc`.`date`) = ? ';
 176             array_push($sqlBindArrayTotalVisit, date("Y-m-d"));
 177             $whereTotalPaymentConditions .= ' AND DATE(`b`.`date`)  = ? ';
 178             array_push($sqlBindArrayTotalPayment, date("Y-m-d"));
 179             $wherePaidConditions .= ' AND DATE(`p`.`dtime`)  = ? ';
 180             array_push($sqlBindArrayPaid, date("Y-m-d"));
 181         }
 182
 183         // if search based on facility then append condition for facility search
 184         if (1 === $facilitySet) {
 185             $facilityID = $selectedFacility;
 186             $whereNewPatientConditions .= ' AND `f`.`id` = ?';
 187             array_push($sqlBindArrayNewPatient, $selectedFacility);
 188             $whereTotalVisitConditions .= ' AND `f`.`id` = ?';
 189             array_push($sqlBindArrayTotalVisit, $selectedFacility);
 190             $whereTotalPaymentConditions .= ' AND `f`.`id` = ?';
 191             array_push($sqlBindArrayTotalPayment, $selectedFacility);
 192             $wherePaidConditions .= ' AND `f`.`id` = ?';
 193             array_push($sqlBindArrayPaid, $selectedFacility);
 194         }
 195
 196         // if date range wise search then append condition for date search
 197         if (1 === $dateSet) {
 198             $whereNewPatientConditions .= ' AND DATE(`OPE`.`pc_eventDate`) BETWEEN ? AND ?';
 199             array_push($sqlBindArrayNewPatient, $from_date, $to_date);
 200             $whereTotalVisitConditions .= ' AND DATE(`fc`.`date`) BETWEEN ? AND ?';
 201             array_push($sqlBindArrayTotalVisit, $from_date, $to_date);
 202             $whereTotalPaymentConditions .= ' AND DATE(`b`.`date`) BETWEEN ? AND ?';
 203             array_push($sqlBindArrayTotalPayment, $from_date, $to_date);
 204             $wherePaidConditions .= ' AND DATE(`p`.`dtime`) BETWEEN ? AND ?';
 205             array_push($sqlBindArrayPaid, $from_date, $to_date);
 206         }
 207
 208         // if provider selected then append condition for provider
 209         if (isset($selectedProvider) && !empty($selectedProvider)) {
 210             $providerID = $selectedProvider;
 211             $whereNewPatientConditions .= ' AND `OPE`.`pc_aid` = ?';
 212             array_push($sqlBindArrayNewPatient, $selectedProvider);
 213             $whereTotalVisitConditions .= ' AND `fc`.`provider_id` = ?';
 214             array_push($sqlBindArrayTotalVisit, $selectedProvider);
 215             $whereTotalPaymentConditions .= ' AND `fe`.`provider_id` = ?';
 216             array_push($sqlBindArrayTotalPayment, $selectedProvider);
 217             $wherePaidConditions .= ' AND `fe`.`provider_id` = ?';
 218             array_push($sqlBindArrayPaid, $selectedProvider);
 219         }
 220
 221         // pass last parameter as Boolean,  which is getting the facility name in the resulted array
 222         $totalAppointmentSql = fetchAppointments($from_date, $to_date, null, $providerID, $facilityID);
 223         if (count($totalAppointmentSql) > 0) { // check if $totalAppointmentSql array has value
 224             foreach ($totalAppointmentSql as $appointment) {
 225                 $eventDate = $appointment['pc_eventDate'];
 226                 $facility = $appointment['name'];
 227                 $providerName = $appointment['ufname'] . ' ' . $appointment['ulname'];
 228
 229                 // initialize each level of the data structure if it doesn't already exist
 230                 if (!isset($totalAppointment[$eventDate])) {
 231                     $totalAppointment[$eventDate] = [];
 232                 }
 233
 234                 if (!isset($totalAppointment[$eventDate][$facility])) {
 235                     $totalAppointment[$eventDate][$facility] = [];
 236                 }
 237
 238                 if (!isset($totalAppointment[$eventDate][$facility][$providerName])) {
 239                     $totalAppointment[$eventDate][$facility][$providerName] = [];
 240                 }
 241
 242                 // initialize the number of appointment to 0
 243                 if (!isset($totalAppointment[$eventDate][$facility][$providerName]['appointments'])) {
 244                     $totalAppointment[$eventDate][$facility][$providerName]['appointments'] = 0;
 245                 }
 246
 247                 // increment the number of appointments
 248                 $totalAppointment[$eventDate][$facility][$providerName]['appointments']++;
 249             }
 250         }
 251
 252         //Count Total New Patient
 253         $newPatientSql = sqlStatement("SELECT `OPE`.`pc_eventDate` , `f`.`name` AS facility_Name , count( * ) AS totalNewPatient, `PD`.`providerID`, CONCAT( `u`.`fname`, ' ', `u`.`lname` ) AS provider_name
 254                                         FROM `patient_data` AS PD
 255                                         LEFT JOIN `openemr_postcalendar_events` AS OPE ON ( `OPE`.`pc_pid` = `PD`.`pid` )
 256                                         LEFT JOIN `facility` AS f ON ( `OPE`.`pc_facility` = `f`.`id` )
 257                                         LEFT JOIN `users` AS u ON ( `OPE`.`pc_aid` = `u`.`id` )
 258                                         WHERE `OPE`.`pc_title` = 'New Patient'
 259                                         AND  $whereNewPatientConditions
 260                                         GROUP BY `f`.`id` , `OPE`.`pc_eventDate`,provider_name
 261                                         ORDER BY `OPE`.`pc_eventDate` ASC", $sqlBindArrayNewPatient);
 262
 263
 264
 265         while ($totalNewPatientRecord = sqlFetchArray($newPatientSql)) {
 266             $totalNewPatient[$totalNewPatientRecord['pc_eventDate']][$totalNewPatientRecord['facility_Name']][$totalNewPatientRecord['provider_name']]['newPatient'] = $totalNewPatientRecord['totalNewPatient'];
 267         }
 268
 269         //Count Total Visit
 270         $totalVisitSql = sqlStatement("SELECT DATE( `fc`.`date` ) AS Date,`f`.`name` AS facility_Name, count( * ) AS totalVisit, `fc`.`provider_id`, CONCAT( `u`.`fname`, ' ', `u`.`lname` ) AS provider_name
 271                                                                     FROM `form_encounter` AS fc
 272                                                                     LEFT JOIN `facility` AS f ON ( `fc`.`facility_id` = `f`.`id` )
 273                                                                     LEFT JOIN `users` AS u ON ( `fc`.`provider_id` = `u`.`id` )
 274                                                                     WHERE $whereTotalVisitConditions
 275                                                                     GROUP BY `fc`.`facility_id`, DATE( `fc`.`date` ),provider_name ORDER BY DATE( `fc`.`date` ) ASC", $sqlBindArrayTotalVisit);
 276
 277         while ($totalVisitRecord = sqlFetchArray($totalVisitSql)) {
 278             $totalVisit[$totalVisitRecord['Date']][$totalVisitRecord['facility_Name']][$totalVisitRecord['provider_name']]['visits'] = $totalVisitRecord['totalVisit'];
 279         }
 280
 281         //Count Total Payments for only active records i.e. activity = 1
 282         $totalPaymetsSql = sqlStatement("SELECT DATE( `b`.`date` ) AS Date, `f`.`name` AS facilityName, SUM( `b`.`fee` ) AS totalpayment, `fe`.`provider_id`, CONCAT( `u`.`fname`, ' ', `u`.`lname` ) AS provider_name
 283                                                                     FROM `facility` AS f
 284                                                                     LEFT JOIN `form_encounter` AS fe ON ( `fe`.`facility_id` = `f`.`id` )
 285                                                                     LEFT JOIN `billing` AS b ON ( `fe`.`encounter` = `b`.`encounter` )
 286                                                                     LEFT JOIN `users` AS u ON ( `fe`.`provider_id` = `u`.`id` )
 287                                                                     WHERE `b`.`activity` =1 AND
 288                                                                     $whereTotalPaymentConditions
 289                                                                     GROUP BY `b`.`encounter`,Date,provider_name ORDER BY Date ASC", $sqlBindArrayTotalPayment);
 290
 291         while ($totalPaymentRecord = sqlFetchArray($totalPaymetsSql)) {
 292             $totalPayment[$totalPaymentRecord['Date']][$totalPaymentRecord['facilityName']][$totalPaymentRecord['provider_name']]['payments'] += $totalPaymentRecord['totalpayment'];
 293         }
 294
 295         // total paid amount
 296         $totalPaidAmountSql = sqlStatement("SELECT DATE( `p`.`dtime` ) AS Date,`f`.`name` AS facilityName, SUM( `p`.`amount1` ) AS totalPaidAmount, `fe`.`provider_id`, CONCAT( `u`.`fname`, ' ', `u`.`lname` ) AS provider_name
 297                                                                         FROM `facility` AS f
 298                                                                         LEFT JOIN `form_encounter` AS fe ON ( `fe`.`facility_id` = `f`.`id` )
 299                                                                         LEFT JOIN `payments` AS p ON ( `fe`.`encounter` = `p`.`encounter` )
 300                                                                         LEFT JOIN `users` AS u ON ( `fe`.`provider_id` = `u`.`id` )
 301                                                                         WHERE $wherePaidConditions
 302                                                                         GROUP BY `p`.`encounter`, Date,provider_name ORDER BY Date ASC", $sqlBindArrayPaid);
 303
 304
 305         while ($totalPaidRecord = sqlFetchArray($totalPaidAmountSql)) {
 306             $totalPaid[$totalPaidRecord['Date']][$totalPaidRecord['facilityName']][$totalPaidRecord['provider_name']]['paidAmount'] += $totalPaidRecord['totalPaidAmount'];
 307         }
 308
 309         // merge all array recursive in to one array
 310         $dailySummaryReport = array_merge_recursive($totalAppointment, $totalNewPatient, $totalVisit, $totalPayment, $totalPaid);
 311         ?>
 312
 313         <div id="report_results" style="font-size: 12px">
 314             <?php echo '<b>' . xlt('From') . '</b> ' . oeFormatShortDate($from_date) . ' <b>' . xlt('To') . '</b> ' . oeFormatShortDate($to_date); ?>
 315
 316             <table class="flowboard" cellpadding='5' cellspacing='2' id="ds_report">
 317                 <tr class="head">
 318
 319                     <td><?php echo xlt('Date'); ?></td>
 320                     <td><?php echo xlt('Facility'); ?></td>
 321                     <td><?php echo xlt('Provider'); ?></td>
 322                     <td><?php echo xlt('Appointments'); ?></td>
 323                     <td><?php echo xlt('New Patients'); ?></td>
 324                     <td><?php echo xlt('Visited Patients'); ?></td>
 325                     <td><?php echo xlt('Total Charges'); ?></td>
 326                     <td><?php echo xlt('Total Co-Pay'); ?></td>
 327                     <td><?php echo xlt('Balance Payment'); ?></td>
 328                 </tr>
 329                 <?php
 330                 if (count($dailySummaryReport) > 0) { // check if daily summary array has value
 331                     foreach ($dailySummaryReport as $date => $dataValue) { //   daily summary array which consists different/dynamic values
 332                         foreach ($facilities as $facility) { // facility array
 333                             if (isset($dataValue[$facility])) {
 334                                 foreach ($dataValue[$facility] as $provider => $information) { // array which consists different/dynamic values
 335                                     ?>
 336                                     <tr>
 337                                         <td><?php echo oeFormatShortDate($date) ?></td>
 338                                         <td><?php echo text($facility); ?></td>
 339                                         <td><?php echo text($provider); ?></td>
 340                                         <td><?php echo isset($information['appointments']) ? text($information['appointments']) : 0; ?></td>
 341                                         <td><?php echo isset($information['newPatient']) ? text($information['newPatient']) : 0; ?></td>
 342                                         <td><?php echo isset($information['visits']) ? text($information['visits']) : 0; ?></td>
 343                                         <td align="right"><?php echo isset($information['payments']) ? text(number_format($information['payments'], 2)) : number_format(0, 2); ?></td>
 344                                         <td align="right"><?php echo isset($information['paidAmount']) ? text(number_format($information['paidAmount'], 2)) : number_format(0, 2); ?></td>
 345                                         <td align="right">
 346                                             <?php
 347                                             if (isset($information['payments']) || isset($information['paidAmount'])) {
 348                                                 $dueAmount = number_format(floatval(str_replace(",", "", $information['payments'])) - floatval(str_replace(",", "", $information['paidAmount'])), 2);
 349                                             } else {
 350                                                 $dueAmount = number_format(0, 2);
 351                                             }
 352
 353                                             echo text($dueAmount);
 354                                             ?>
 355                                         </td>
 356                                     </tr>
 357                                     <?php
 358                                     if (count($dailySummaryReport) > 0) { // calculate the total count of the appointments, new patient,visits, payments, paid amount and due amount
 359                                         $totalAppointments += $information['appointments'];
 360                                         $totalNewRegisterPatient += $information['newPatient'];
 361                                         $totalVisits += $information['visits'];
 362                                         $totalPayments += floatval(str_replace(",", "", $information['payments']));
 363                                         $totalPaidAmount += floatval(str_replace(",", "", $information['paidAmount']));
 364                                         $totalDueAmount += $dueAmount;
 365                                     }
 366                                 }
 367                             }
 368                         }
 369                     }
 370                     ?>
 371                     <!--display total count-->
 372                     <tr class="totalrow">
 373                         <td><?php echo xlt("Total"); ?></td>
 374                         <td>-</td>
 375                         <td>-</td>
 376                         <td><?php echo text($totalAppointments); ?></td>
 377                         <td><?php echo text($totalNewRegisterPatient); ?></td>
 378                         <td><?php echo text($totalVisits); ?></td>
 379                         <td align="right"><?php echo text(number_format($totalPayments, 2)); ?></td>
 380                         <td align="right"><?php echo text(number_format($totalPaidAmount, 2)); ?></td>
 381                         <td align="right"><?php echo text(number_format($totalDueAmount, 2)); ?></td>
 382                     </tr>
 383                     <?php
 384                 } else { // if there are no records then display message
 385                     ?>
 386                     <tr>
 387                         <td colspan="9" style="text-align:center;font-weight:bold;"> <?php echo xlt("There are no record(s) found."); ?></td>
 388                     </tr><?php
 389                 } ?>
 390
 391             </table>
 392         </div>
 393     </body>
 394 </html>