3 * Daily Summary Report. (/interface/reports/daily_summary_report.php)
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.
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-2018 Brady Miller <brady.g.miller@gmail.com>
14 * @license https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
18 require_once("../globals.php");
19 require_once "$srcdir/options.inc.php";
20 require_once "$srcdir/appointments.inc.php";
22 use OpenEMR\Core\Header
;
23 use OpenEMR\Services\FacilityService
;
26 if (!verifyCsrfToken($_POST["csrf_token_form"])) {
31 $facilityService = new FacilityService();
33 $from_date = isset($_POST['form_from_date']) ?
DateToYYYYMMDD($_POST['form_from_date']) : date('Y-m-d'); // From date filter
34 $to_date = isset($_POST['form_to_date']) ?
DateToYYYYMMDD($_POST['form_to_date']) : date('Y-m-d'); // To date filter
35 $selectedFacility = isset($_POST['form_facility']) ?
$_POST['form_facility'] : ""; // facility filter
36 $selectedProvider = isset($_POST['form_provider']) ?
$_POST['form_provider'] : ""; // provider filter
42 <title
><?php
echo xlt('Daily Summary Report'); ?
></title
>
44 <?php Header
::setupHeader(['datetime-picker', 'report-helper']); ?
>
46 <script type
="text/javascript">
47 function submitForm() {
48 var fromDate
= $
("#form_from_date").val();
49 var toDate
= $
("#form_to_date").val();
51 if (fromDate
=== '') {
52 alert(<?php
echo xlj('Please select From date'); ?
>);
56 alert(<?php
echo xlj('Please select To date'); ?
>);
59 if (Date
.parse(fromDate
) > Date
.parse(toDate
)) {
60 alert(<?php
echo xlj('From date should be less than To date'); ?
>);
64 $
("#form_refresh").attr("value", "true");
65 $
("#report_form").submit();
69 $
( document
).ready(function(){
70 $
('.datepicker').datetimepicker({
71 <?php
$datetimepicker_timepicker = false; ?
>
72 <?php
$datetimepicker_showseconds = false; ?
>
73 <?php
$datetimepicker_formatInput = true; ?
>
74 <?php
require($GLOBALS['srcdir'] . '/js/xl/jquery-datetimepicker-2-5-4.js.php'); ?
>
75 <?php
// can add any additional javascript settings to datetimepicker here; need to prepend first setting with a comma ?>
83 <body
class="body_top">
85 <span
class='title'><?php
echo xlt('Daily Summary Report'); ?
></span
>
86 <!-- start of search parameters
-->
87 <form method
='post' name
='report_form' id
='report_form' action
='' onsubmit
='return top.restoreSession()'>
88 <input type
="hidden" name
="csrf_token_form" value
="<?php echo attr(collectCsrfToken()); ?>" />
89 <div id
="report_parameters">
90 <table
class="tableonly">
93 <div style
='float: left'>
96 <td
class='control-label'><?php
echo xlt('Facility'); ?
>:</td
>
97 <td
><?php
dropdown_facility($selectedFacility, 'form_facility', false); ?
></td
>
98 <td
class='control-label'><?php
echo xlt('From'); ?
>:</td
>
100 <input type
='text' name
='form_from_date' id
="form_from_date"
101 class='datepicker form-control'
102 size
='10' value
='<?php echo attr(oeFormatShortDate($from_date)); ?>'>
104 <td
class='control-label'><?php
echo xlt('To'); ?
>:</td
>
106 <input type
='text' name
='form_to_date' id
="form_to_date"
107 class='datepicker form-control'
108 size
='10' value
='<?php echo attr(oeFormatShortDate($to_date)); ?>'>
110 <td
class='control-label'><?php
echo xlt('Provider'); ?
>:</td
>
113 generate_form_field(array('data_type' => 10, 'field_id' => 'provider',
114 'empty_title' => '-- All Providers --'), $selectedProvider);
120 <td align
='left' valign
='middle' height
="100%">
121 <table style
='border-left: 1px solid; width: 100%; height: 100%'>
124 <div
class="text-center">
125 <div
class="btn-group" role
="group">
126 <a href
='#' class='btn btn-default btn-save' onclick
='return submitForm();'>
127 <?php
echo xlt('Submit'); ?
>
129 <a href
='' class="btn btn-default btn-refresh" id
='new0' onClick
=" top.restoreSession(); window.location = window.location.href;">
130 <?php
echo xlt('Reset'); ?
>
140 <input type
='hidden' name
='form_refresh' id
='form_refresh' value
='' />
143 <!-- end of search parameters
-->
146 $dateSet = $facilitySet = 0;
147 if (!empty($from_date) && !empty($to_date)) {
151 if (isset($selectedFacility) && !empty($selectedFacility)) {
155 // define all the variables as initial blank array
156 $facilities = $totalAppointment = $totalNewPatient = $totalVisit = $totalPayment = $dailySummaryReport = $totalPaid = array();
158 // define all the where condition variable as initial value set 1=1
159 $whereTotalVisitConditions = $whereTotalPaymentConditions = $wherePaidConditions = $whereNewPatientConditions = '1 = 1 ';
161 // fetch all facility from the table
162 $facilityRecords = $facilityService->getAll();
163 foreach ($facilityRecords as $facilityList) {
164 if (1 === $facilitySet && $facilityList['id'] == $selectedFacility) {
165 $facilities[$facilityList['id']] = $facilityList['name'];
168 if (empty($selectedFacility)) {
169 $facilities[$facilityList['id']] = $facilityList['name'];
173 // define provider and facility as null
174 $providerID = $facilityID = null;
175 // define all the bindarray variables as initial blank array
176 $sqlBindArrayAppointment = $sqlBindArrayTotalVisit = $sqlBindArrayTotalPayment = $sqlBindArrayPaid = $sqlBindArrayNewPatient = array();
178 // make all condition on by default today's date
179 if ($dateSet != 1 && $facilitySet != 1) {
180 $whereNewPatientConditions .= ' AND DATE(`OPE`.`pc_eventDate`) = ? ';
181 array_push($sqlBindArrayNewPatient, date("Y-m-d"));
182 $whereTotalVisitConditions .= ' AND DATE(`fc`.`date`) = ? ';
183 array_push($sqlBindArrayTotalVisit, date("Y-m-d"));
184 $whereTotalPaymentConditions .= ' AND DATE(`b`.`date`) = ? ';
185 array_push($sqlBindArrayTotalPayment, date("Y-m-d"));
186 $wherePaidConditions .= ' AND DATE(`p`.`dtime`) = ? ';
187 array_push($sqlBindArrayPaid, date("Y-m-d"));
190 // if search based on facility then append condition for facility search
191 if (1 === $facilitySet) {
192 $facilityID = $selectedFacility;
193 $whereNewPatientConditions .= ' AND `f`.`id` = ?';
194 array_push($sqlBindArrayNewPatient, $selectedFacility);
195 $whereTotalVisitConditions .= ' AND `f`.`id` = ?';
196 array_push($sqlBindArrayTotalVisit, $selectedFacility);
197 $whereTotalPaymentConditions .= ' AND `f`.`id` = ?';
198 array_push($sqlBindArrayTotalPayment, $selectedFacility);
199 $wherePaidConditions .= ' AND `f`.`id` = ?';
200 array_push($sqlBindArrayPaid, $selectedFacility);
203 // if date range wise search then append condition for date search
204 if (1 === $dateSet) {
205 $whereNewPatientConditions .= ' AND DATE(`OPE`.`pc_eventDate`) BETWEEN ? AND ?';
206 array_push($sqlBindArrayNewPatient, $from_date, $to_date);
207 $whereTotalVisitConditions .= ' AND DATE(`fc`.`date`) BETWEEN ? AND ?';
208 array_push($sqlBindArrayTotalVisit, $from_date, $to_date);
209 $whereTotalPaymentConditions .= ' AND DATE(`b`.`date`) BETWEEN ? AND ?';
210 array_push($sqlBindArrayTotalPayment, $from_date, $to_date);
211 $wherePaidConditions .= ' AND DATE(`p`.`dtime`) BETWEEN ? AND ?';
212 array_push($sqlBindArrayPaid, $from_date, $to_date);
215 // if provider selected then append condition for provider
216 if (isset($selectedProvider) && !empty($selectedProvider)) {
217 $providerID = $selectedProvider;
218 $whereNewPatientConditions .= ' AND `OPE`.`pc_aid` = ?';
219 array_push($sqlBindArrayNewPatient, $selectedProvider);
220 $whereTotalVisitConditions .= ' AND `fc`.`provider_id` = ?';
221 array_push($sqlBindArrayTotalVisit, $selectedProvider);
222 $whereTotalPaymentConditions .= ' AND `fe`.`provider_id` = ?';
223 array_push($sqlBindArrayTotalPayment, $selectedProvider);
224 $wherePaidConditions .= ' AND `fe`.`provider_id` = ?';
225 array_push($sqlBindArrayPaid, $selectedProvider);
228 // pass last parameter as Boolean, which is getting the facility name in the resulted array
229 $totalAppointmentSql = fetchAppointments($from_date, $to_date, null, $providerID, $facilityID);
230 if (count($totalAppointmentSql) > 0) { // check if $totalAppointmentSql array has value
231 foreach ($totalAppointmentSql as $appointment) {
232 $eventDate = $appointment['pc_eventDate'];
233 $facility = $appointment['name'];
234 $providerName = $appointment['ufname'] . ' ' . $appointment['ulname'];
236 // initialize each level of the data structure if it doesn't already exist
237 if (!isset($totalAppointment[$eventDate])) {
238 $totalAppointment[$eventDate] = [];
241 if (!isset($totalAppointment[$eventDate][$facility])) {
242 $totalAppointment[$eventDate][$facility] = [];
245 if (!isset($totalAppointment[$eventDate][$facility][$providerName])) {
246 $totalAppointment[$eventDate][$facility][$providerName] = [];
249 // initialize the number of appointment to 0
250 if (!isset($totalAppointment[$eventDate][$facility][$providerName]['appointments'])) {
251 $totalAppointment[$eventDate][$facility][$providerName]['appointments'] = 0;
254 // increment the number of appointments
255 $totalAppointment[$eventDate][$facility][$providerName]['appointments']++
;
259 //Count Total New Patient
260 $newPatientSql = sqlStatement("SELECT `OPE`.`pc_eventDate` , `f`.`name` AS facility_Name , count( * ) AS totalNewPatient, `PD`.`providerID`, CONCAT( `u`.`fname`, ' ', `u`.`lname` ) AS provider_name
261 FROM `patient_data` AS PD
262 LEFT JOIN `openemr_postcalendar_events` AS OPE ON ( `OPE`.`pc_pid` = `PD`.`pid` )
263 LEFT JOIN `facility` AS f ON ( `OPE`.`pc_facility` = `f`.`id` )
264 LEFT JOIN `users` AS u ON ( `OPE`.`pc_aid` = `u`.`id` )
265 WHERE `OPE`.`pc_title` = 'New Patient'
266 AND $whereNewPatientConditions
267 GROUP BY `f`.`id` , `OPE`.`pc_eventDate`,provider_name
268 ORDER BY `OPE`.`pc_eventDate` ASC", $sqlBindArrayNewPatient);
272 while ($totalNewPatientRecord = sqlFetchArray($newPatientSql)) {
273 $totalNewPatient[$totalNewPatientRecord['pc_eventDate']][$totalNewPatientRecord['facility_Name']][$totalNewPatientRecord['provider_name']]['newPatient'] = $totalNewPatientRecord['totalNewPatient'];
277 $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
278 FROM `form_encounter` AS fc
279 LEFT JOIN `facility` AS f ON ( `fc`.`facility_id` = `f`.`id` )
280 LEFT JOIN `users` AS u ON ( `fc`.`provider_id` = `u`.`id` )
281 WHERE $whereTotalVisitConditions
282 GROUP BY `fc`.`facility_id`, DATE( `fc`.`date` ),provider_name ORDER BY DATE( `fc`.`date` ) ASC", $sqlBindArrayTotalVisit);
284 while ($totalVisitRecord = sqlFetchArray($totalVisitSql)) {
285 $totalVisit[$totalVisitRecord['Date']][$totalVisitRecord['facility_Name']][$totalVisitRecord['provider_name']]['visits'] = $totalVisitRecord['totalVisit'];
288 //Count Total Payments for only active records i.e. activity = 1
289 $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
291 LEFT JOIN `form_encounter` AS fe ON ( `fe`.`facility_id` = `f`.`id` )
292 LEFT JOIN `billing` AS b ON ( `fe`.`encounter` = `b`.`encounter` )
293 LEFT JOIN `users` AS u ON ( `fe`.`provider_id` = `u`.`id` )
294 WHERE `b`.`activity` =1 AND
295 $whereTotalPaymentConditions
296 GROUP BY `b`.`encounter`,Date,provider_name ORDER BY Date ASC", $sqlBindArrayTotalPayment);
298 while ($totalPaymentRecord = sqlFetchArray($totalPaymetsSql)) {
299 $totalPayment[$totalPaymentRecord['Date']][$totalPaymentRecord['facilityName']][$totalPaymentRecord['provider_name']]['payments'] +
= $totalPaymentRecord['totalpayment'];
303 $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
305 LEFT JOIN `form_encounter` AS fe ON ( `fe`.`facility_id` = `f`.`id` )
306 LEFT JOIN `payments` AS p ON ( `fe`.`encounter` = `p`.`encounter` )
307 LEFT JOIN `users` AS u ON ( `fe`.`provider_id` = `u`.`id` )
308 WHERE $wherePaidConditions
309 GROUP BY `p`.`encounter`, Date,provider_name ORDER BY Date ASC", $sqlBindArrayPaid);
312 while ($totalPaidRecord = sqlFetchArray($totalPaidAmountSql)) {
313 $totalPaid[$totalPaidRecord['Date']][$totalPaidRecord['facilityName']][$totalPaidRecord['provider_name']]['paidAmount'] +
= $totalPaidRecord['totalPaidAmount'];
316 // merge all array recursive in to one array
317 $dailySummaryReport = array_merge_recursive($totalAppointment, $totalNewPatient, $totalVisit, $totalPayment, $totalPaid);
320 <div id
="report_results" style
="font-size: 12px">
321 <?php
echo '<b>' . xlt('From') . '</b> ' . text(oeFormatShortDate($from_date)) . ' <b>' . xlt('To') . '</b> ' . text(oeFormatShortDate($to_date)); ?
>
323 <table
class="flowboard" cellpadding
='5' cellspacing
='2' id
="ds_report">
326 <td
><?php
echo xlt('Date'); ?
></td
>
327 <td
><?php
echo xlt('Facility'); ?
></td
>
328 <td
><?php
echo xlt('Provider'); ?
></td
>
329 <td
><?php
echo xlt('Appointments'); ?
></td
>
330 <td
><?php
echo xlt('New Patients'); ?
></td
>
331 <td
><?php
echo xlt('Visited Patients'); ?
></td
>
332 <td
><?php
echo xlt('Total Charges'); ?
></td
>
333 <td
><?php
echo xlt('Total Co-Pay'); ?
></td
>
334 <td
><?php
echo xlt('Balance Payment'); ?
></td
>
337 if (count($dailySummaryReport) > 0) { // check if daily summary array has value
338 foreach ($dailySummaryReport as $date => $dataValue) { // daily summary array which consists different/dynamic values
339 foreach ($facilities as $facility) { // facility array
340 if (isset($dataValue[$facility])) {
341 foreach ($dataValue[$facility] as $provider => $information) { // array which consists different/dynamic values
344 <td
><?php
echo text(oeFormatShortDate($date)); ?
></td
>
345 <td
><?php
echo text($facility); ?
></td
>
346 <td
><?php
echo text($provider); ?
></td
>
347 <td
><?php
echo isset($information['appointments']) ?
text($information['appointments']) : 0; ?
></td
>
348 <td
><?php
echo isset($information['newPatient']) ?
text($information['newPatient']) : 0; ?
></td
>
349 <td
><?php
echo isset($information['visits']) ?
text($information['visits']) : 0; ?
></td
>
350 <td align
="right"><?php
echo isset($information['payments']) ?
text(number_format($information['payments'], 2)) : number_format(0, 2); ?
></td
>
351 <td align
="right"><?php
echo isset($information['paidAmount']) ?
text(number_format($information['paidAmount'], 2)) : number_format(0, 2); ?
></td
>
354 if (isset($information['payments']) ||
isset($information['paidAmount'])) {
355 $dueAmount = number_format(floatval(str_replace(",", "", $information['payments'])) - floatval(str_replace(",", "", $information['paidAmount'])), 2);
357 $dueAmount = number_format(0, 2);
360 echo text($dueAmount);
365 if (count($dailySummaryReport) > 0) { // calculate the total count of the appointments, new patient,visits, payments, paid amount and due amount
366 $totalAppointments +
= $information['appointments'];
367 $totalNewRegisterPatient +
= $information['newPatient'];
368 $totalVisits +
= $information['visits'];
369 $totalPayments +
= floatval(str_replace(",", "", $information['payments']));
370 $totalPaidAmount +
= floatval(str_replace(",", "", $information['paidAmount']));
371 $totalDueAmount +
= $dueAmount;
378 <!--display total count
-->
379 <tr
class="totalrow">
380 <td
><?php
echo xlt("Total"); ?
></td
>
383 <td
><?php
echo text($totalAppointments); ?
></td
>
384 <td
><?php
echo text($totalNewRegisterPatient); ?
></td
>
385 <td
><?php
echo text($totalVisits); ?
></td
>
386 <td align
="right"><?php
echo text(number_format($totalPayments, 2)); ?
></td
>
387 <td align
="right"><?php
echo text(number_format($totalPaidAmount, 2)); ?
></td
>
388 <td align
="right"><?php
echo text(number_format($totalDueAmount, 2)); ?
></td
>
391 } else { // if there are no records then display message
394 <td colspan
="9" style
="text-align:center;font-weight:bold;"> <?php
echo xlt("There are no record(s) found."); ?
></td
>