From b0a6c675e24d049a4b8a00498f84c1d4fe0803ae Mon Sep 17 00:00:00 2001 From: sunsetsystems Date: Fri, 8 Feb 2008 04:42:47 +0000 Subject: [PATCH] a couple of report improvements including export to csv format --- interface/reports/collections_report.php | 167 +++++++++++++++------- interface/reports/insurance_allocation_report.php | 130 ++++++++++++----- 2 files changed, 210 insertions(+), 87 deletions(-) diff --git a/interface/reports/collections_report.php b/interface/reports/collections_report.php index 8065eddfd..eefb05c59 100644 --- a/interface/reports/collections_report.php +++ b/interface/reports/collections_report.php @@ -24,23 +24,28 @@ $form_date = fixDate($_POST['form_date'], ""); $form_to_date = fixDate($_POST['form_to_date'], ""); $is_due_ins = $_POST['form_category'] == xl('Due Ins'); $is_due_pt = $_POST['form_category'] == xl('Due Pt'); +$is_all = $_POST['form_category'] == xl('All'); if ($_POST['form_search'] || $_POST['form_export'] || $_POST['form_csvexport']) { - $form_cb_ssn = $_POST['form_cb_ssn'] ? true : false; - $form_cb_dob = $_POST['form_cb_dob'] ? true : false; - $form_cb_policy = $_POST['form_cb_policy'] ? true : false; - $form_cb_phone = $_POST['form_cb_phone'] ? true : false; - $form_cb_city = $_POST['form_cb_city'] ? true : false; - $form_cb_idays = $_POST['form_cb_idays'] ? true : false; - $form_cb_err = $_POST['form_cb_err'] ? true : false; + $form_cb_ssn = $_POST['form_cb_ssn'] ? true : false; + $form_cb_dob = $_POST['form_cb_dob'] ? true : false; + $form_cb_policy = $_POST['form_cb_policy'] ? true : false; + $form_cb_phone = $_POST['form_cb_phone'] ? true : false; + $form_cb_city = $_POST['form_cb_city'] ? true : false; + $form_cb_ins1 = $_POST['form_cb_ins1'] ? true : false; + $form_cb_referrer = $_POST['form_cb_referrer'] ? true : false; + $form_cb_idays = $_POST['form_cb_idays'] ? true : false; + $form_cb_err = $_POST['form_cb_err'] ? true : false; } else { - $form_cb_ssn = true; - $form_cb_dob = false; - $form_cb_policy = false; - $form_cb_phone = true; - $form_cb_city = false; - $form_cb_idays = false; - $form_cb_err = false; + $form_cb_ssn = true; + $form_cb_dob = false; + $form_cb_policy = false; + $form_cb_phone = true; + $form_cb_city = false; + $form_cb_ins1 = false; + $form_cb_referrer = false; + $form_cb_idays = false; + $form_cb_err = false; } $form_age_cols = (int) $_POST['form_age_cols']; $form_age_inc = (int) $_POST['form_age_inc']; @@ -52,12 +57,14 @@ if ($form_age_cols > 0 && $form_age_cols < 50) { } $initial_colspan = 1; -if ($is_due_ins ) ++$initial_colspan; -if ($form_cb_ssn ) ++$initial_colspan; -if ($form_cb_dob ) ++$initial_colspan; -if ($form_cb_policy) ++$initial_colspan; -if ($form_cb_phone ) ++$initial_colspan; -if ($form_cb_city ) ++$initial_colspan; +if ($is_due_ins ) ++$initial_colspan; +if ($form_cb_ssn ) ++$initial_colspan; +if ($form_cb_dob ) ++$initial_colspan; +if ($form_cb_policy ) ++$initial_colspan; +if ($form_cb_phone ) ++$initial_colspan; +if ($form_cb_city ) ++$initial_colspan; +if ($form_cb_ins1 ) ++$initial_colspan; +if ($form_cb_referrer) ++$initial_colspan; $grand_total_charges = 0; $grand_total_adjustments = 0; @@ -144,6 +151,17 @@ function endPatient($ptrow) { $grand_total_agedbal[$c] += $ptrow['agedbal'][$c]; } } + +// In the case of CSV export only, a download will be forced. +if ($_POST['form_csvexport']) { + header("Pragma: public"); + header("Expires: 0"); + header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); + header("Content-Type: application/force-download"); + header("Content-Disposition: attachment; filename=collections_report.csv"); + header("Content-Description: File Transfer"); +} +else { ?> @@ -195,19 +213,25 @@ function checkAll(checked) {   >   + > +   + > +   >   > -   - - - - + + + +   + + +   ' title=''> @@ -218,7 +242,7 @@ function checkAll(checked) {   \n"; - $alertmsg .= "$export_patient_count patients representing $" . - sprintf("%.2f", $export_dollars) . " have been exported."; + // echo "\n"; + // $alertmsg .= "$export_patient_count patients representing $" . + // sprintf("%.2f", $export_dollars) . " have been exported."; } else { echo " \n"; @@ -692,26 +756,31 @@ function checkAll(checked) { } } // end if form_search SLClose(); -?> +if (!$_POST['form_csvexport']) { + if (!$_POST['form_export']) { +?>

-           -

- + + \ No newline at end of file diff --git a/interface/reports/insurance_allocation_report.php b/interface/reports/insurance_allocation_report.php index be639ff0e..de4632412 100644 --- a/interface/reports/insurance_allocation_report.php +++ b/interface/reports/insurance_allocation_report.php @@ -1,4 +1,4 @@ - @@ -40,8 +58,8 @@ - : - : +   - > + '> +   + ' /> @@ -68,65 +88,96 @@ - + + + + + + + - + - + -= '$from_date' AND " . - "b.date <= '$to_date' " . - "ORDER BY c.name ASC, b.pid ASC, i.date DESC"; - - // echo "\n"; // debugging + $query = "SELECT b.pid, b.encounter, SUM(b.fee) AS charges, " . + "MAX(fe.date) AS date " . + "FROM form_encounter AS fe, billing AS b " . + "WHERE fe.date >= '$from_date' AND fe.date <= '$to_date' " . + "AND b.pid = fe.pid AND b.encounter = fe.encounter " . + "AND b.code_type != 'COPAY' AND b.activity > 0 AND b.fee != 0 " . + "GROUP BY b.pid, b.encounter ORDER BY b.pid, b.encounter"; + $res = sqlStatement($query); $insarr = array(); + $prev_pid = 0; + $patcount = 0; - $prevplan = ''; - $prevpid = 0; while ($row = sqlFetchArray($res)) { - // echo "\n"; // debugging - $plan = $row['name'] ? $row['name'] : '-- No Insurance --'; - if (strcmp($plan, $prevplan) == 0 && $row['pid'] == $prevpid) continue; - $prevplan = $plan; - $prevpid = $row['pid']; - $insarr[$plan] += 1; - $inscount += 1; + $patient_id = $row['pid']; + $encounter_date = $row['date']; + $irow = sqlQuery("SELECT insurance_companies.name " . + "FROM insurance_data, insurance_companies WHERE " . + "insurance_data.pid = $patient_id AND " . + "insurance_data.type = 'primary' AND " . + "insurance_data.date <= '$encounter_date' AND " . + "insurance_companies.id = insurance_data.provider " . + "ORDER BY insurance_data.date DESC LIMIT 1"); + $plan = $irow['name'] ? $irow['name'] : '-- No Insurance --'; + $insarr[$plan]['visits'] += 1; + $insarr[$plan]['charges'] += sprintf('%0.2f', $row['charges']); + if ($patient_id != $prev_pid) { + ++$patcount; + $insarr[$plan]['patients'] += 1; + $prev_pid = $patient_id; + } } + ksort($insarr); + while (list($key, $val) = each($insarr)) { + if ($_POST['form_csvexport']) { + echo '"' . $key . '",'; + echo '"' . sprintf('%0.2f', $val['charges']) . '",'; + echo '"' . $val['visits'] . '",'; + echo '"' . $val['patients'] . '",'; + echo '"' . sprintf("%.1f", $val['patients'] * 100 / $patcount) . '"' . "\n"; + } + else { ?> - + + + + - + - + + + + - @@ -134,3 +185,6 @@ + \ No newline at end of file -- 2.11.4.GIT