interface/reports/daily_summary_report.php

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