3 * Daily Summary Report. (/interface/reports/daily_summary_report.php)
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.
9 * Copyright (C) 2016 Rishabh Software
11 * LICENSE: This program is free software; you can redistribute it and/or
12 * modify it under the terms of the GNU General Public License
13 * as published by the Free Software Foundation; either version 3
14 * of the License, or (at your option) any later version.
15 * This program is distributed in the hope that it will be useful,
16 * but WITHOUT ANY WARRANTY; without even the implied warranty of
17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18 * GNU General Public License for more details.
19 * You should have received a copy of the GNU General Public License
20 * along with this program. If not, see <http://opensource.org/licenses/gpl-license.php>;.
23 * @author Rishabh Software
24 * @link http://www.open-emr.org
28 $fake_register_globals = false;
29 $sanitize_all_escapes = true;
31 require_once("../globals.php");
32 require_once "$srcdir/options.inc.php";
33 require_once "$srcdir/appointments.inc.php";
36 $selectedFromDate = isset($_POST['form_from_date']) ?
$_POST['form_from_date'] : date('Y-m-d'); // From date filter
37 $selectedToDate = isset($_POST['form_to_date']) ?
$_POST['form_to_date'] : date('Y-m-d'); // To date filter
38 $selectedFacility = isset($_POST['form_facility']) ?
$_POST['form_facility'] : ""; // facility filter
39 $selectedProvider = isset($_POST['form_provider']) ?
$_POST['form_provider'] : ""; // provider filter
41 $from_date = fixDate($selectedFromDate, date('Y-m-d'));
42 $to_date = fixDate($selectedToDate, date('Y-m-d'));
47 <?php
html_header_show(); ?
>
48 <link rel
="stylesheet" href
="<?php echo $css_header; ?>" type
="text/css">
49 <script type
="text/javascript" src
="../../library/textformat.js"></script
>
50 <script type
="text/javascript" src
="<?php echo $GLOBALS['assets_static_relative']; ?>/jquery-min-1-3-2/index.js"></script
>
51 <script type
="text/javascript">
54 var mypcc
= '<?php echo $GLOBALS['phone_country_code
'] ?>';
56 function submitForm() {
57 var fromDate
= $
("#form_from_date").val();
58 var toDate
= $
("#form_to_date").val();
60 if (fromDate
=== '') {
61 alert("<?php echo xls('Please select From date'); ?>");
65 alert("<?php echo xls('Please select To date'); ?>");
68 if (Date
.parse(fromDate
) > Date
.parse(toDate
)) {
69 alert("<?php echo xls('From date should be less than To date'); ?>");
73 $
("#form_refresh").attr("value", "true");
74 $
("#report_form").submit();
81 <body
class="body_top">
83 <span
class='title'><?php
echo xlt('Daily Summary Report'); ?
></span
>
84 <!-- start of search parameters
-->
85 <form method
='post' name
='report_form' id
='report_form' action
='' onsubmit
='return top.restoreSession()'>
86 <div id
="report_parameters">
87 <table
class="tableonly">
90 <div style
='float: left'>
93 <td
class='label'><?php
echo xlt('Facility'); ?
>:</td
>
94 <td
><?php
dropdown_facility($selectedFacility, 'form_facility', false); ?
></td
>
95 <td
class='label'><?php
echo xlt('From'); ?
>:</td
>
97 <input type
='text' name
='form_from_date' id
="form_from_date"
98 size
='10' value
='<?php echo attr($from_date) ?>'
99 onkeyup
='datekeyup(this, mypcc)' onblur
='dateblur(this, mypcc)'
100 title
='yyyy-mm-dd'> <img src
='../pic/show_calendar.gif'
101 align
='absbottom' width
='24' height
='22' id
='img_from_date'
102 border
='0' alt
='[?]' style
='cursor: pointer'
103 title
='<?php echo xla('Click here to choose a date
'); ?>'>
105 <td
class='label'><?php
echo xlt('To'); ?
>:</td
>
107 <input type
='text' name
='form_to_date' id
="form_to_date"
108 size
='10' value
='<?php echo attr($to_date) ?>'
109 onkeyup
='datekeyup(this, mypcc)' onblur
='dateblur(this, mypcc)'
110 title
='yyyy-mm-dd'> <img src
='../pic/show_calendar.gif'
111 align
='absbottom' width
='24' height
='22' id
='img_to_date'
112 border
='0' alt
='[?]' style
='cursor: pointer'
113 title
='<?php echo xla('Click here to choose a date
'); ?>'>
118 <td
class='label'><?php
echo xlt('Provider'); ?
>:</td
>
121 generate_form_field(array('data_type' => 10, 'field_id' => 'provider',
122 'empty_title' => '-- All Providers --'), $selectedProvider);
125 <td align
='left' valign
='middle' height
="100%">
126 <table style
='border-left: 1px solid; width: 100%; height: 100%'>
129 <div style
='margin-left: 15px'>
130 <a href
='#' class='css_button' onclick
='return submitForm();'>
131 <span
> <?php
echo xlt('Submit'); ?
> </span
>
133 <a href
='' class="css_button" id
='new0' onClick
=" return top.window.parent.left_nav.loadFrame2('new0', 'RTop', 'reports/daily_summary_report.php')">
134 <span
><?php
echo xlt('Reset'); ?
></span
>
143 <input type
='hidden' name
='form_refresh' id
='form_refresh' value
='' />
146 <!-- end of search parameters
-->
149 $dateSet = $facilitySet = 0;
150 if (!empty($selectedFromDate) && !empty($selectedToDate)) {
153 if (isset($selectedFacility) && !empty($selectedFacility)) {
157 // define all the variables as initial blank array
158 $facilities = $totalAppointment = $totalNewPatient = $totalVisit = $totalPayment = $dailySummaryReport = $totalPaid = array();
160 // define all the where condition variable as initial value set 1=1
161 $whereTotalVisitConditions = $whereTotalPaymentConditions = $wherePaidConditions = $whereNewPatientConditions = '1 = 1 ';
163 // fetch all facility from the table
164 $facilityReacords = sqlStatement("SELECT `id`,`name` from facility");
165 while ($facilityList = sqlFetchArray($facilityReacords)) {
166 if (1 === $facilitySet && $facilityList['id'] == $selectedFacility) {
167 $facilities[$facilityList['id']] = $facilityList['name'];
169 if (empty($selectedFacility)) {
170 $facilities[$facilityList['id']] = $facilityList['name'];
174 // define provider and facility as null
175 $providerID = $facilityID = NULL;
176 // define all the bindarray variables as initial blank array
177 $sqlBindArrayAppointment = $sqlBindArrayTotalVisit = $sqlBindArrayTotalPayment = $sqlBindArrayPaid = $sqlBindArrayNewPatient = array();
179 // make all condition on by default today's date
180 if ($dateSet != 1 && $facilitySet != 1) {
181 $whereNewPatientConditions .= ' AND DATE(`OPE`.`pc_eventDate`) = ? ';
182 array_push($sqlBindArrayNewPatient, date("Y-m-d"));
183 $whereTotalVisitConditions .= ' AND DATE(`fc`.`date`) = ? ';
184 array_push($sqlBindArrayTotalVisit, date("Y-m-d"));
185 $whereTotalPaymentConditions .= ' AND DATE(`b`.`date`) = ? ';
186 array_push($sqlBindArrayTotalPayment, date("Y-m-d"));
187 $wherePaidConditions .= ' AND DATE(`p`.`dtime`) = ? ';
188 array_push($sqlBindArrayPaid, date("Y-m-d"));
191 // if search based on facility then append condition for facility search
192 if (1 === $facilitySet) {
193 $facilityID = $selectedFacility;
194 $whereNewPatientConditions .= ' AND `f`.`id` = ?';
195 array_push($sqlBindArrayNewPatient, $selectedFacility);
196 $whereTotalVisitConditions .= ' AND `f`.`id` = ?';
197 array_push($sqlBindArrayTotalVisit, $selectedFacility);
198 $whereTotalPaymentConditions .= ' AND `f`.`id` = ?';
199 array_push($sqlBindArrayTotalPayment, $selectedFacility);
200 $wherePaidConditions .= ' AND `f`.`id` = ?';
201 array_push($sqlBindArrayPaid, $selectedFacility);
204 // if date range wise search then append condition for date search
205 if (1 === $dateSet) {
206 $whereNewPatientConditions .= ' AND DATE(`OPE`.`pc_eventDate`) BETWEEN ? AND ?';
207 array_push($sqlBindArrayNewPatient, $selectedFromDate, $selectedToDate);
208 $whereTotalVisitConditions .= ' AND DATE(`fc`.`date`) BETWEEN ? AND ?';
209 array_push($sqlBindArrayTotalVisit, $selectedFromDate, $selectedToDate);
210 $whereTotalPaymentConditions .= ' AND DATE(`b`.`date`) BETWEEN ? AND ?';
211 array_push($sqlBindArrayTotalPayment, $selectedFromDate, $selectedToDate);
212 $wherePaidConditions .= ' AND DATE(`p`.`dtime`) BETWEEN ? AND ?';
213 array_push($sqlBindArrayPaid, $selectedFromDate, $selectedToDate);
216 // if provider selected then append condition for provider
217 if (isset($selectedProvider) && !empty($selectedProvider)) {
218 $providerID = $selectedProvider;
219 $whereNewPatientConditions .= ' AND `OPE`.`pc_aid` = ?';
220 array_push($sqlBindArrayNewPatient, $selectedProvider);
221 $whereTotalVisitConditions .= ' AND `fc`.`provider_id` = ?';
222 array_push($sqlBindArrayTotalVisit, $selectedProvider);
223 $whereTotalPaymentConditions .= ' AND `fe`.`provider_id` = ?';
224 array_push($sqlBindArrayTotalPayment, $selectedProvider);
225 $wherePaidConditions .= ' AND `fe`.`provider_id` = ?';
226 array_push($sqlBindArrayPaid, $selectedProvider);
229 // pass last parameter as Boolean, which is getting the facility name in the resulted array
230 $totalAppointmentSql = fetchAppointments($from_date, $to_date, null, $providerID, $facilityID);
231 if (count($totalAppointmentSql) > 0) { // check if $totalAppointmentSql array has value
232 foreach ($totalAppointmentSql as $appointment) {
234 $eventDate = $appointment['pc_eventDate'];
235 $facility = $appointment['name'];
236 $providerName = $appointment['ufname'] . ' ' . $appointment['ulname'];
238 // initialize each level of the data structure if it doesn't already exist
239 if (!isset($totalAppointment[$eventDate])) {
240 $totalAppointment[$eventDate] = [];
242 if (!isset($totalAppointment[$eventDate][$facility])) {
243 $totalAppointment[$eventDate][$facility] = [];
245 if (!isset($totalAppointment[$eventDate][$facility][$providerName])) {
246 $totalAppointment[$eventDate][$facility][$providerName] = [];
248 // initialize the number of appointment to 0
249 if (!isset($totalAppointment[$eventDate][$facility][$providerName]['appointments'])) {
250 $totalAppointment[$eventDate][$facility][$providerName]['appointments'] = 0;
252 // increment the number of appointments
253 $totalAppointment[$eventDate][$facility][$providerName]['appointments']++
;
257 //Count Total New Patient
258 $newPatientSql = sqlStatement("SELECT `OPE`.`pc_eventDate` , `f`.`name` AS facility_Name , count( * ) AS totalNewPatient, `PD`.`providerID`, CONCAT( `u`.`fname`, ' ', `u`.`lname` ) AS provider_name
259 FROM `patient_data` AS PD
260 LEFT JOIN `openemr_postcalendar_events` AS OPE ON ( `OPE`.`pc_pid` = `PD`.`pid` )
261 LEFT JOIN `facility` AS f ON ( `OPE`.`pc_facility` = `f`.`id` )
262 LEFT JOIN `users` AS u ON ( `OPE`.`pc_aid` = `u`.`id` )
263 WHERE `OPE`.`pc_title` = 'New Patient'
264 AND $whereNewPatientConditions
265 GROUP BY `f`.`id` , `OPE`.`pc_eventDate`,provider_name
266 ORDER BY `OPE`.`pc_eventDate` ASC", $sqlBindArrayNewPatient);
270 while ($totalNewPatientRecord = sqlFetchArray($newPatientSql)) {
271 $totalNewPatient[$totalNewPatientRecord['pc_eventDate']][$totalNewPatientRecord['facility_Name']][$totalNewPatientRecord['provider_name']]['newPatient'] = $totalNewPatientRecord['totalNewPatient'];
275 $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
276 FROM `form_encounter` AS fc
277 LEFT JOIN `facility` AS f ON ( `fc`.`facility_id` = `f`.`id` )
278 LEFT JOIN `users` AS u ON ( `fc`.`provider_id` = `u`.`id` )
279 WHERE $whereTotalVisitConditions
280 GROUP BY `fc`.`facility_id`, DATE( `fc`.`date` ),provider_name ORDER BY DATE( `fc`.`date` ) ASC", $sqlBindArrayTotalVisit);
282 while ($totalVisitRecord = sqlFetchArray($totalVisitSql)) {
283 $totalVisit[$totalVisitRecord['Date']][$totalVisitRecord['facility_Name']][$totalVisitRecord['provider_name']]['visits'] = $totalVisitRecord['totalVisit'];
286 //Count Total Payments for only active records i.e. activity = 1
287 $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
289 LEFT JOIN `form_encounter` AS fe ON ( `fe`.`facility_id` = `f`.`id` )
290 LEFT JOIN `billing` AS b ON ( `fe`.`encounter` = `b`.`encounter` )
291 LEFT JOIN `users` AS u ON ( `fe`.`provider_id` = `u`.`id` )
292 WHERE `b`.`activity` =1 AND
293 $whereTotalPaymentConditions
294 GROUP BY `b`.`encounter`,Date,provider_name ORDER BY Date ASC", $sqlBindArrayTotalPayment);
296 while ($totalPaymentRecord = sqlFetchArray($totalPaymetsSql)) {
297 $totalPayment[$totalPaymentRecord['Date']][$totalPaymentRecord['facilityName']][$totalPaymentRecord['provider_name']]['payments'] +
= $totalPaymentRecord['totalpayment'];
301 $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
303 LEFT JOIN `form_encounter` AS fe ON ( `fe`.`facility_id` = `f`.`id` )
304 LEFT JOIN `payments` AS p ON ( `fe`.`encounter` = `p`.`encounter` )
305 LEFT JOIN `users` AS u ON ( `fe`.`provider_id` = `u`.`id` )
306 WHERE $wherePaidConditions
307 GROUP BY `p`.`encounter`, Date,provider_name ORDER BY Date ASC", $sqlBindArrayPaid);
310 while ($totalPaidRecord = sqlFetchArray($totalPaidAmountSql)) {
311 $totalPaid[$totalPaidRecord['Date']][$totalPaidRecord['facilityName']][$totalPaidRecord['provider_name']]['paidAmount'] +
= $totalPaidRecord['totalPaidAmount'];
314 // merge all array recursive in to one array
315 $dailySummaryReport = array_merge_recursive($totalAppointment, $totalNewPatient, $totalVisit, $totalPayment, $totalPaid);
318 <div id
="report_results" style
="font-size: 12px">
319 <?php
echo '<b>' . xlt('From') . '</b> ' . $from_date . ' <b>' . xlt('To') . '</b> ' . $to_date; ?
>
321 <table
class="flowboard" cellpadding
='5' cellspacing
='2' id
="ds_report">
324 <td
><?php
echo xlt('Date'); ?
></td
>
325 <td
><?php
echo xlt('Facility'); ?
></td
>
326 <td
><?php
echo xlt('Provider'); ?
></td
>
327 <td
><?php
echo xlt('Appointments'); ?
></td
>
328 <td
><?php
echo xlt('New Patients'); ?
></td
>
329 <td
><?php
echo xlt('Visited Patients'); ?
></td
>
330 <td
><?php
echo xlt('Total Charges'); ?
></td
>
331 <td
><?php
echo xlt('Total Co-Pay'); ?
></td
>
332 <td
><?php
echo xlt('Balance Payment'); ?
></td
>
335 if (count($dailySummaryReport) > 0) { // check if daily summary array has value
336 foreach ($dailySummaryReport as $date => $dataValue) { // daily summary array which consists different/dynamic values
337 foreach ($facilities as $facility) { // facility array
338 if (isset($dataValue[$facility])) {
339 foreach ($dataValue[$facility] as $provider => $information) { // array which consists different/dynamic values
342 <td
><?php
echo text($date) ?
></td
>
343 <td
><?php
echo text($facility); ?
></td
>
344 <td
><?php
echo text($provider); ?
></td
>
345 <td
><?php
echo isset($information['appointments']) ?
text($information['appointments']) : 0; ?
></td
>
346 <td
><?php
echo isset($information['newPatient']) ?
text($information['newPatient']) : 0; ?
></td
>
347 <td
><?php
echo isset($information['visits']) ?
text($information['visits']) : 0; ?
></td
>
348 <td align
="right"><?php
echo isset($information['payments']) ?
text(number_format($information['payments'], 2)) : number_format(0, 2); ?
></td
>
349 <td align
="right"><?php
echo isset($information['paidAmount']) ?
text(number_format($information['paidAmount'], 2)) : number_format(0, 2); ?
></td
>
352 if (isset($information['payments']) ||
isset($information['paidAmount'])) {
353 $dueAmount = number_format(str_replace(",", "", $information['payments']) - str_replace(",", "", $information['paidAmount']), 2);
355 $dueAmount = number_format(0, 2);
357 echo text($dueAmount);
362 if (count($dailySummaryReport) > 0) { // calculate the total count of the appointments, new patient,visits, payments, paid amount and due amount
363 $totalAppointments +
= $information['appointments'];
364 $totalNewRegisterPatient +
= $information['newPatient'];
365 $totalVisits +
= $information['visits'];
366 $totalPayments +
= str_replace(",", "", $information['payments']);
367 $totalPaidAmount +
= str_replace(",", "", $information['paidAmount']);
368 $totalDueAmount +
= $dueAmount;
375 <!--display total count
-->
376 <tr
class="totalrow">
377 <td
><?php
echo xlt("Total"); ?
></td
>
380 <td
><?php
echo text($totalAppointments); ?
></td
>
381 <td
><?php
echo text($totalNewRegisterPatient); ?
></td
>
382 <td
><?php
echo text($totalVisits); ?
></td
>
383 <td align
="right"><?php
echo text(number_format($totalPayments, 2)); ?
></td
>
384 <td align
="right"><?php
echo text(number_format($totalPaidAmount, 2)); ?
></td
>
385 <td align
="right"><?php
echo text(number_format($totalDueAmount, 2)); ?
></td
>
388 } else { // if there are no records then display message
391 <td colspan
="9" style
="text-align:center;font-weight:bold;"> <?php
echo xlt("There are no record(s) found."); ?
></td
>
399 <!-- stuff
for the popup calendar
-->
400 <style type
="text/css">@import
url(../../library
/dynarch_calendar
.css
);</style
>
401 <script type
="text/javascript" src
="../../library/dynarch_calendar.js"></script
>
402 <?php
include_once("{$GLOBALS['srcdir']}/dynarch_calendar_en.inc.php"); ?
>
403 <script type
="text/javascript" src
="../../library/dynarch_calendar_setup.js"></script
>
404 <script type
="text/javascript">
405 Calendar
.setup({inputField
: "form_from_date", ifFormat
: "%Y-%m-%d", button
: "img_from_date"});
406 Calendar
.setup({inputField
: "form_to_date", ifFormat
: "%Y-%m-%d", button
: "img_to_date"});