interface/reports/ippf_statistics.php

   1 <?php
   2
   3 /**
   4  * This module creates statistical reports related to family planning
   5  * and sexual and reproductive health.
   6  *
   7  * @package   OpenEMR
   8  * @link      http://www.open-emr.org
   9  * @author    Rod Roark <rod@sunsetsystems.com>
  10  * @author    Brady Miller <brady.g.miller@gmail.com>
  11  * @copyright Copyright (c) 2008-2015 Rod Roark <rod@sunsetsystems.com>
  12  * @copyright Copyright (c) 2017-2018 Brady Miller <brady.g.miller@gmail.com>
  13  * @license   https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
  14  */
  15
  16 require_once("../globals.php");
  17 require_once("../../library/patient.inc.php");
  18
  19 use OpenEMR\Common\Acl\AclMain;
  20 use OpenEMR\Common\Csrf\CsrfUtils;
  21 use OpenEMR\Common\Twig\TwigContainer;
  22 use OpenEMR\Core\Header;
  23 use OpenEMR\Services\FacilityService;
  24
  25 if (!empty($_POST)) {
  26     if (!CsrfUtils::verifyCsrfToken($_POST["csrf_token_form"])) {
  27         CsrfUtils::csrfNotVerified();
  28     }
  29 }
  30
  31 // Might want something different here.
  32 //
  33 if (!AclMain::aclCheckCore('acct', 'rep')) {
  34     echo (new TwigContainer(null, $GLOBALS['kernel']))->getTwig()->render('core/unauthorized.html.twig', ['pageTitle' => xl("Report")]);
  35     exit;
  36 }
  37
  38 $facilityService = new FacilityService();
  39
  40 $report_type = empty($_GET['t']) ? 'i' : $_GET['t'];
  41
  42 $from_date = (isset($_POST['form_from_date'])) ? DateToYYYYMMDD($_POST['form_from_date']) : '0000-00-00';
  43 $to_date   = (isset($_POST['form_to_date'])) ? DateToYYYYMMDD($_POST['form_to_date']) : date('Y-m-d');
  44
  45 $form_by       = $_POST['form_by'];     // this is a scalar
  46 $form_show     = $_POST['form_show'];   // this is an array
  47 $form_facility = isset($_POST['form_facility']) ? $_POST['form_facility'] : '';
  48 $form_sexes    = isset($_POST['form_sexes']) ? $_POST['form_sexes'] : '3';
  49 $form_content  = isset($_POST['form_content']) ? $_POST['form_content'] : '1';
  50 $form_output   = isset($_POST['form_output']) ? 0 + $_POST['form_output'] : 1;
  51
  52 if (empty($form_by)) {
  53     $form_by = '1';
  54 }
  55
  56 if (empty($form_show)) {
  57     $form_show = array('1');
  58 }
  59
  60 // One of these is chosen as the left column, or Y-axis, of the report.
  61 //
  62 if ($report_type == 'm') {
  63     $report_title = xl('Member Association Statistics Report');
  64     $arr_by = array(
  65     101 => xl('MA Category'),
  66     102 => xl('Specific Service'),
  67     // 6   => xl('Contraceptive Method'),
  68     // 104 => xl('Specific Contraceptive Service');
  69     17  => xl('Patient'),
  70     9   => xl('Internal Referrals'),
  71     10  => xl('External Referrals'),
  72     103 => xl('Referral Source'),
  73     2   => xl('Total'),
  74     );
  75     $arr_content = array(
  76     1 => xl('Services'),
  77     2 => xl('Unique Clients'),
  78     4 => xl('Unique New Clients'),
  79     // 5 => xl('Contraceptive Products'),
  80     );
  81     $arr_report = array(
  82     // Items are content|row|column|column|...
  83     /*****************************************************************
  84     '2|2|3|4|5|8|11' => xl('Client Profile - Unique Clients'),
  85     '4|2|3|4|5|8|11' => xl('Client Profile - New Clients'),
  86     *****************************************************************/
  87     );
  88 } elseif ($report_type == 'g') {
  89     $report_title = xl('GCAC Statistics Report');
  90     $arr_by = array(
  91     13 => xl('Abortion-Related Categories'),
  92     1  => xl('Total SRH & Family Planning'),
  93     12 => xl('Pre-Abortion Counseling'),
  94     5  => xl('Abortion Method'), // includes surgical and drug-induced
  95     8  => xl('Post-Abortion Followup'),
  96     7  => xl('Post-Abortion Contraception'),
  97     11 => xl('Complications of Abortion'),
  98     10  => xl('External Referrals'),
  99     20  => xl('External Referral Followups'),
 100     );
 101     $arr_content = array(
 102     1 => xl('Services'),
 103     2 => xl('Unique Clients'),
 104     4 => xl('Unique New Clients'),
 105     );
 106     $arr_report = array(
 107     /*****************************************************************
 108     '1|11|13' => xl('Complications by Service Provider'),
 109     *****************************************************************/
 110     );
 111 } else {
 112     $report_title = xl('IPPF Statistics Report');
 113     $arr_by = array(
 114     3  => xl('General Service Category'),
 115     4  => xl('Specific Service'),
 116     104 => xl('Specific Contraceptive Service'),
 117     6  => xl('Contraceptive Method'),
 118     9   => xl('Internal Referrals'),
 119     10  => xl('External Referrals'),
 120     );
 121     $arr_content = array(
 122     1 => xl('Services'),
 123     3 => xl('New Acceptors'),
 124     5 => xl('Contraceptive Products'),
 125     );
 126     $arr_report = array(
 127     );
 128 }
 129
 130 // This will become the array of reportable values.
 131 $areport = array();
 132
 133 // This accumulates the bottom line totals.
 134 $atotals = array();
 135
 136 $arr_show   = array(
 137   '.total' => array('title' => 'Total'),
 138   '.age2'  => array('title' => 'Age Category (2)'),
 139   '.age9'  => array('title' => 'Age Category (9)'),
 140 ); // info about selectable columns
 141
 142 $arr_titles = array(); // will contain column headers
 143
 144 // Query layout_options table to generate the $arr_show table.
 145 // Table key is the field ID.
 146 $lres = sqlStatement("SELECT field_id, title, data_type, list_id, description " .
 147   "FROM layout_options WHERE " .
 148   "form_id = 'DEM' AND uor > 0 AND field_id NOT LIKE 'em%' " .
 149   "ORDER BY group_name, seq, title");
 150 while ($lrow = sqlFetchArray($lres)) {
 151     $fid = $lrow['field_id'];
 152     if ($fid == 'fname' || $fid == 'mname' || $fid == 'lname') {
 153         continue;
 154     }
 155
 156     $arr_show[$fid] = $lrow;
 157     $arr_titles[$fid] = array();
 158 }
 159
 160 // Compute age in years given a DOB and "as of" date.
 161 //
 162 function getAge($dob, $asof = '')
 163 {
 164     if (empty($asof)) {
 165         $asof = date('Y-m-d');
 166     }
 167
 168     $a1 = explode('-', substr($dob, 0, 10));
 169     $a2 = explode('-', substr($asof, 0, 10));
 170     $age = $a2[0] - $a1[0];
 171     if ($a2[1] < $a1[1] || ($a2[1] == $a1[1] && $a2[2] < $a1[2])) {
 172         --$age;
 173     }
 174
 175   // echo "<!-- $dob $asof $age -->\n"; // debugging
 176     return $age;
 177 }
 178
 179 $cellcount = 0;
 180
 181 function genStartRow($att)
 182 {
 183     global $cellcount, $form_output;
 184     if ($form_output != 3) {
 185         echo " <tr $att>\n";
 186     }
 187
 188     $cellcount = 0;
 189 }
 190
 191 function genEndRow()
 192 {
 193     global $form_output;
 194     if ($form_output == 3) {
 195         echo "\n";
 196     } else {
 197         echo " </tr>\n";
 198     }
 199 }
 200
 201 function getListTitle($list, $option)
 202 {
 203     $row = sqlQuery("SELECT title FROM list_options WHERE " .
 204     "list_id = ? AND option_id = ?", array($list, $option));
 205     if (empty($row['title'])) {
 206         return $option;
 207     }
 208
 209     return $row['title'];
 210 }
 211
 212 // Usually this generates one cell, but allows for two or more.
 213 //
 214 function genAnyCell($data, $right = false, $class = '', $colspan = 1)
 215 {
 216     global $cellcount, $form_output;
 217     if (!is_array($data)) {
 218         $data = array(0 => $data);
 219     }
 220
 221     foreach ($data as $datum) {
 222         if ($form_output == 3) {
 223             if ($cellcount) {
 224                 echo ',';
 225             }
 226
 227             echo '"' . $datum . '"';
 228         } else {
 229             echo "  <td";
 230             if ($class) {
 231                 echo " class='" . attr($class) . "'";
 232             }
 233
 234             if ($colspan > 1) {
 235                 echo " colspan='" . attr($colspan) . "' align='center'";
 236             } elseif ($right) {
 237                 echo " align='right'";
 238             }
 239
 240             echo ">" . text($datum) . "</td>\n";
 241         }
 242
 243         ++$cellcount;
 244     }
 245 }
 246
 247 function genHeadCell($data, $right = false, $colspan = 1)
 248 {
 249     genAnyCell($data, $right, 'dehead', $colspan);
 250 }
 251
 252 // Create an HTML table cell containing a numeric value, and track totals.
 253 //
 254 function genNumCell($num, $cnum)
 255 {
 256     global $atotals, $form_output;
 257     $atotals[$cnum] += $num;
 258     if (empty($num) && $form_output != 3) {
 259         $num = '&nbsp;';
 260     }
 261
 262     genAnyCell($num, true, 'detail');
 263 }
 264
 265 // Translate an IPPF code to the corresponding descriptive name of its
 266 // contraceptive method, or to an empty string if none applies.
 267 //
 268 function getContraceptiveMethod($code)
 269 {
 270     $key = '';
 271     if (preg_match('/^111101/', $code)) {
 272         $key = xl('Pills');
 273     } elseif (preg_match('/^11111[1-9]/', $code)) {
 274         $key = xl('Injectables');
 275     } elseif (preg_match('/^11112[1-9]/', $code)) {
 276         $key = xl('Implants');
 277     } elseif (preg_match('/^111132/', $code)) {
 278         $key = xl('Patch');
 279     } elseif (preg_match('/^111133/', $code)) {
 280         $key = xl('Vaginal Ring');
 281     } elseif (preg_match('/^112141/', $code)) {
 282         $key = xl('Male Condoms');
 283     } elseif (preg_match('/^112142/', $code)) {
 284         $key = xl('Female Condoms');
 285     } elseif (preg_match('/^11215[1-9]/', $code)) {
 286         $key = xl('Diaphragms/Caps');
 287     } elseif (preg_match('/^11216[1-9]/', $code)) {
 288         $key = xl('Spermicides');
 289     } elseif (preg_match('/^11317[1-9]/', $code)) {
 290         $key = xl('IUD');
 291     } elseif (preg_match('/^145212/', $code)) {
 292         $key = xl('Emergency Contraception');
 293     } elseif (preg_match('/^121181.13/', $code)) {
 294         $key = xl('Female VSC');
 295     } elseif (preg_match('/^122182.13/', $code)) {
 296         $key = xl('Male VSC');
 297     } elseif (preg_match('/^131191.10/', $code)) {
 298         $key = xl('Awareness-Based');
 299     }
 300
 301     return $key;
 302 }
 303
 304 // Helper function to find a contraception-related IPPF code from
 305 // the related_code element of the given array.
 306 //
 307 function getRelatedContraceptiveCode($row)
 308 {
 309     if (!empty($row['related_code'])) {
 310         $relcodes = explode(';', $row['related_code']);
 311         foreach ($relcodes as $codestring) {
 312             if ($codestring === '') {
 313                 continue;
 314             }
 315
 316             list($codetype, $code) = explode(':', $codestring);
 317             if ($codetype !== 'IPPF') {
 318                 continue;
 319             }
 320
 321             // Check if the related code concerns contraception.
 322             $tmp = getContraceptiveMethod($code);
 323             if (!empty($tmp)) {
 324                 return $code;
 325             }
 326         }
 327     }
 328
 329     return '';
 330 }
 331
 332 // Helper function to find an abortion-method IPPF code from
 333 // the related_code element of the given array.
 334 //
 335 function getRelatedAbortionMethod($row)
 336 {
 337     if (!empty($row['related_code'])) {
 338         $relcodes = explode(';', $row['related_code']);
 339         foreach ($relcodes as $codestring) {
 340             if ($codestring === '') {
 341                 continue;
 342             }
 343
 344             list($codetype, $code) = explode(':', $codestring);
 345             if ($codetype !== 'IPPF') {
 346                 continue;
 347             }
 348
 349             // Check if the related code concerns contraception.
 350             $tmp = getAbortionMethod($code);
 351             if (!empty($tmp)) {
 352                 return $code;
 353             }
 354         }
 355     }
 356
 357     return '';
 358 }
 359
 360 // Translate an IPPF code to the corresponding descriptive name of its
 361 // abortion method, or to an empty string if none applies.
 362 //
 363 function getAbortionMethod($code)
 364 {
 365     $key = '';
 366     if (preg_match('/^25222[34]/', $code)) {
 367         if (preg_match('/^2522231/', $code)) {
 368             $key = xl('D&C');
 369         } elseif (preg_match('/^2522232/', $code)) {
 370             $key = xl('D&E');
 371         } elseif (preg_match('/^2522233/', $code)) {
 372             $key = xl('MVA');
 373         } elseif (preg_match('/^252224/', $code)) {
 374             $key = xl('Medical');
 375         } else {
 376             $key = xl('Other Surgical');
 377         }
 378     }
 379
 380     return $key;
 381 }
 382
 383 /*********************************************************************
 384 // Helper function to look up the GCAC issue associated with a visit.
 385 // Ideally this is the one and only GCAC issue linked to the encounter.
 386 // However if there are multiple such issues, or if only unlinked issues
 387 // are found, then we pick the one with its start date closest to the
 388 // encounter date.
 389 //
 390 function getGcacData($row, $what, $morejoins="") {
 391   $patient_id = $row['pid'];
 392   $encounter_id = $row['encounter'];
 393   $encdate = substr($row['encdate'], 0, 10);
 394   $query = "SELECT $what " .
 395     "FROM lists AS l " .
 396     "JOIN lists_ippf_gcac AS lg ON l.type = 'ippf_gcac' AND lg.id = l.id " .
 397     "LEFT JOIN issue_encounter AS ie ON ie.pid = '$patient_id' AND " .
 398     "ie.encounter = '$encounter_id' AND ie.list_id = l.id " .
 399     "$morejoins " .
 400     "WHERE l.pid = '$patient_id' AND " .
 401     "l.activity = 1 AND l.type = 'ippf_gcac' " .
 402     "ORDER BY ie.pid DESC, ABS(DATEDIFF(l.begdate, '$encdate')) ASC " .
 403     "LIMIT 1";
 404   // Note that reverse-ordering by ie.pid is a trick for sorting
 405   // issues linked to the encounter (non-null values) first.
 406   return sqlQuery($query);
 407 }
 408
 409 // Get the "client status" field from the related GCAC issue.
 410 //
 411 function getGcacClientStatus($row) {
 412   $irow = getGcacData($row, "lo.title", "LEFT JOIN list_options AS lo ON " .
 413     "lo.list_id = 'clientstatus' AND lo.option_id = lg.client_status");
 414   if (empty($irow['title'])) {
 415     $key = xl('Indeterminate');
 416   }
 417   else {
 418     // The client status description should be just fine for this.
 419     $key = $irow['title'];
 420   }
 421   return $key;
 422 }
 423 *********************************************************************/
 424
 425 // Determine if a recent gcac service was performed.
 426 //
 427 function hadRecentAbService($pid, $encdate)
 428 {
 429     $query = "SELECT COUNT(*) AS count " .
 430     "FROM form_encounter AS fe, billing AS b, codes AS c WHERE " .
 431     "fe.pid = ? AND " .
 432     "fe.date <= ? AND " .
 433     "DATE_ADD(fe.date, INTERVAL 14 DAY) > ? AND " .
 434     "b.pid = fe.pid AND " .
 435     "b.encounter = fe.encounter AND " .
 436     "b.activity = 1 AND " .
 437     "b.code_type = 'MA' AND " .
 438     "c.code_type = '12' AND " .
 439     "c.code = b.code AND c.modifier = b.modifier AND " .
 440     "( c.related_code LIKE '%IPPF:252223%' OR c.related_code LIKE '%IPPF:252224%' )";
 441     $tmp = sqlQuery($query, array($pid, $encdate, $encdate));
 442     return !empty($tmp['count']);
 443 }
 444
 445 // Get the "client status" as descriptive text.
 446 //
 447 function getGcacClientStatus($row)
 448 {
 449     $pid = $row['pid'];
 450     $encdate = $row['encdate'];
 451
 452     if (hadRecentAbService($pid, $encdate)) {
 453         return xl('MA Client Accepting Abortion');
 454     }
 455
 456   // Check for a GCAC visit form.
 457   // This will the most recent GCAC visit form for visits within
 458   // the past 2 weeks, although there really should be such a form
 459   // attached to the visit associated with $row.
 460     $query = "SELECT lo.title " .
 461     "FROM forms AS f, form_encounter AS fe, lbf_data AS d, list_options AS lo " .
 462     "WHERE f.pid = ? AND " .
 463     "f.formdir = 'LBFgcac' AND " .
 464     "f.deleted = 0 AND " .
 465     "fe.pid = f.pid AND fe.encounter = f.encounter AND " .
 466     "fe.date <= ? AND " .
 467     "DATE_ADD(fe.date, INTERVAL 14 DAY) > ? AND " .
 468     "d.form_id = f.form_id AND " .
 469     "d.field_id = 'client_status' AND " .
 470     "lo.list_id = 'clientstatus' AND " .
 471     "lo.option_id = d.field_value " .
 472     "ORDER BY d.form_id DESC LIMIT 1";
 473     $irow = sqlQuery($query, array($pid, $encdate, $encdate));
 474     if (!empty($irow['title'])) {
 475         return $irow['title'];
 476     }
 477
 478   // Check for a referred abortion.
 479   /*
 480   $query = "SELECT COUNT(*) AS count " .
 481     "FROM transactions AS t, codes AS c WHERE " .
 482     "t.title = 'Referral' AND " .
 483     "t.refer_date IS NOT NULL AND " .
 484     "t.refer_date <= '$encdate' AND " .
 485     "DATE_ADD(t.refer_date, INTERVAL 14 DAY) > '$encdate' AND " .
 486     "t.refer_related_code LIKE 'REF:%' AND " .
 487     "c.code_type = '16' AND " .
 488     "c.code = SUBSTRING(t.refer_related_code, 5) AND " .
 489     "( c.related_code LIKE '%IPPF:252223%' OR c.related_code LIKE '%IPPF:252224%' )";
 490   */
 491     $query = "SELECT COUNT(*) AS count " .
 492     "FROM transactions AS t " .
 493     "LEFT JOIN codes AS c ON t.refer_related_code LIKE 'REF:%' AND " .
 494     "c.code_type = '16' AND " .
 495     "c.code = SUBSTRING(t.refer_related_code, 5) " .
 496     "WHERE " .
 497     "t.title = 'Referral' AND " .
 498     "t.refer_date IS NOT NULL AND " .
 499     "t.refer_date <= ? AND " .
 500     "DATE_ADD(t.refer_date, INTERVAL 14 DAY) > ? AND " .
 501     "( t.refer_related_code LIKE '%IPPF:252223%' OR " .
 502     "t.refer_related_code LIKE '%IPPF:252224%' OR " .
 503     "( c.related_code IS NOT NULL AND " .
 504     "( c.related_code LIKE '%IPPF:252223%' OR " .
 505     "c.related_code LIKE '%IPPF:252224%' )))";
 506
 507     $tmp = sqlQuery($query, array($encdate, $encdate));
 508     if (!empty($tmp['count'])) {
 509         return xl('Outbound Referral');
 510     }
 511
 512     return xl('Indeterminate');
 513 }
 514
 515 // Helper function called after the reporting key is determined for a row.
 516 //
 517 function loadColumnData($key, $row, $quantity = 1)
 518 {
 519     global $areport, $arr_titles, $form_content, $from_date, $to_date, $arr_show;
 520
 521   // If first instance of this key, initialize its arrays.
 522     if (empty($areport[$key])) {
 523         $areport[$key] = array();
 524         $areport[$key]['.prp'] = 0;       // previous pid
 525         $areport[$key]['.wom'] = 0;       // number of services for women
 526         $areport[$key]['.men'] = 0;       // number of services for men
 527         $areport[$key]['.age2'] = array(0,0);               // age array
 528         $areport[$key]['.age9'] = array(0,0,0,0,0,0,0,0,0); // age array
 529         foreach ($arr_show as $askey => $dummy) {
 530             if (substr($askey, 0, 1) == '.') {
 531                 continue;
 532             }
 533
 534             $areport[$key][$askey] = array();
 535         }
 536     }
 537
 538   // Skip this key if we are counting unique patients and the key
 539   // has already seen this patient.
 540     if ($form_content == '2' && $row['pid'] == $areport[$key]['.prp']) {
 541         return;
 542     }
 543
 544   // If we are counting new acceptors, then require a unique patient
 545   // whose contraceptive start date is within the reporting period.
 546     if ($form_content == '3') {
 547         // if ($row['pid'] == $areport[$key]['prp']) return;
 548         if ($row['pid'] == $areport[$key]['.prp']) {
 549             return;
 550         }
 551
 552         // Check contraceptive start date.
 553         if (
 554             !$row['contrastart'] || $row['contrastart'] < $from_date ||
 555             $row['contrastart'] > $to_date
 556         ) {
 557             return;
 558         }
 559     }
 560
 561   // If we are counting new clients, then require a unique patient
 562   // whose registration date is within the reporting period.
 563     if ($form_content == '4') {
 564         if ($row['pid'] == $areport[$key]['.prp']) {
 565             return;
 566         }
 567
 568         // Check registration date.
 569         if (
 570             !$row['regdate'] || $row['regdate'] < $from_date ||
 571             $row['regdate'] > $to_date
 572         ) {
 573             return;
 574         }
 575     }
 576
 577   // Flag this patient as having been encountered for this report row.
 578   // $areport[$key]['prp'] = $row['pid'];
 579     $areport[$key]['.prp'] = $row['pid'];
 580
 581   // Increment the correct sex category.
 582     if (strcasecmp($row['sex'], 'Male') == 0) {
 583         $areport[$key]['.men'] += $quantity;
 584     } else {
 585         $areport[$key]['.wom'] += $quantity;
 586     }
 587
 588   // Increment the correct age categories.
 589     $age = getAge(fixDate($row['DOB']), $row['encdate']);
 590     $i = min(intval(($age - 5) / 5), 8);
 591     if ($age < 11) {
 592         $i = 0;
 593     }
 594
 595     $areport[$key]['.age9'][$i] += $quantity;
 596     $i = $age < 25 ? 0 : 1;
 597     $areport[$key]['.age2'][$i] += $quantity;
 598
 599     foreach ($arr_show as $askey => $dummy) {
 600         if (substr($askey, 0, 1) == '.') {
 601             continue;
 602         }
 603
 604         $status = empty($row[$askey]) ? 'Unspecified' : $row[$askey];
 605         $areport[$key][$askey][$status] += $quantity;
 606         $arr_titles[$askey][$status] += $quantity;
 607     }
 608 }
 609
 610 // This is called for each IPPF service code that is selected.
 611 //
 612 function process_ippf_code($row, $code, $quantity = 1)
 613 {
 614     global $areport, $arr_titles, $form_by, $form_content;
 615
 616     $key = 'Unspecified';
 617
 618   // SRH including Family Planning
 619   //
 620     if ($form_by === '1') {
 621         if (preg_match('/^1/', $code)) {
 622             $key = xl('SRH - Family Planning');
 623         } elseif (preg_match('/^2/', $code)) {
 624             $key = xl('SRH Non Family Planning');
 625         } else {
 626             if ($form_content != 5) {
 627                 return;
 628             }
 629         }
 630     } elseif ($form_by === '3') { // General Service Category
 631         if (preg_match('/^1/', $code)) {
 632             $key = xl('SRH - Family Planning');
 633         } elseif (preg_match('/^2/', $code)) {
 634             $key = xl('SRH Non Family Planning');
 635         } elseif (preg_match('/^3/', $code)) {
 636             $key = xl('Non-SRH Medical');
 637         } elseif (preg_match('/^4/', $code)) {
 638             $key = xl('Non-SRH Non-Medical');
 639         } else {
 640             $key = xl('Invalid Service Codes');
 641         }
 642     } elseif ($form_by === '13') { // Abortion-Related Category
 643         if (preg_match('/^252221/', $code)) {
 644             $key = xl('Pre-Abortion Counseling');
 645         } elseif (preg_match('/^252222/', $code)) {
 646             $key = xl('Pre-Abortion Consultation');
 647         } elseif (preg_match('/^252223/', $code)) {
 648             $key = xl('Induced Abortion');
 649         } elseif (preg_match('/^252224/', $code)) {
 650             $key = xl('Medical Abortion');
 651         } elseif (preg_match('/^252225/', $code)) {
 652             $key = xl('Incomplete Abortion Treatment');
 653         } elseif (preg_match('/^252226/', $code)) {
 654             $key = xl('Post-Abortion Care');
 655         } elseif (preg_match('/^252227/', $code)) {
 656             $key = xl('Post-Abortion Counseling');
 657         } elseif (preg_match('/^25222/', $code)) {
 658             $key = xl('Other/Generic Abortion-Related');
 659         } else {
 660             if ($form_content != 5) {
 661                 return;
 662             }
 663         }
 664     } elseif ($form_by === '4') { // Specific Services. One row for each IPPF code.
 665         $key = $code;
 666     } elseif ($form_by === '104') { // Specific Contraceptive Services. One row for each IPPF code.
 667         if ($form_content != 5) {
 668             // Skip codes not for contraceptive services.
 669             $tmp = getContraceptiveMethod($code);
 670             if (empty($tmp)) {
 671                 return;
 672             }
 673         }
 674
 675         $key = $code;
 676     } elseif ($form_by === '5') { // Abortion Method.
 677         $key = getAbortionMethod($code);
 678         if (empty($key)) {
 679             if ($form_content != 5) {
 680                 return;
 681             }
 682
 683             $key = 'Unspecified';
 684         }
 685     } elseif ($form_by === '6') { // Contraceptive Method.
 686         $key = getContraceptiveMethod($code);
 687         if (empty($key)) {
 688             if ($form_content != 5) {
 689                 return;
 690             }
 691
 692             $key = 'Unspecified';
 693         }
 694
 695         /*******************************************************************
 696         // Contraceptive method for new contraceptive adoption following abortion.
 697         // Get it from the IPPF code if an abortion issue is linked to the visit.
 698         // Note we are handling this during processing of services rather than
 699         // by enumerating issues, because we need the service date.
 700         //
 701         else if ($form_by === '7') {
 702         $key = getContraceptiveMethod($code);
 703         if (empty($key)) return;
 704         $patient_id = $row['pid'];
 705         $encounter_id = $row['encounter'];
 706         $query = "SELECT COUNT(*) AS count " .
 707         "FROM lists AS l " .
 708         "JOIN issue_encounter AS ie ON ie.pid = '$patient_id' AND " .
 709         "ie.encounter = '$encounter_id' AND ie.list_id = l.id " .
 710         "WHERE l.pid = '$patient_id' AND " .
 711         "l.activity = 1 AND l.type = 'ippf_gcac'";
 712         // echo "<!-- $key: $query -->\n"; // debugging
 713         $irow = sqlQuery($query);
 714         if (empty($irow['count'])) return;
 715         }
 716          *******************************************************************/
 717
 718         // Contraceptive method for new contraceptive adoption following abortion.
 719         // Get it from the IPPF code if there is a suitable recent GCAC form.
 720         //
 721     } elseif ($form_by === '7') {
 722         $key = getContraceptiveMethod($code);
 723         if (empty($key)) {
 724             return;
 725         }
 726
 727         $patient_id = $row['pid'];
 728         $encdate = $row['encdate'];
 729         // Skip this if no recent gcac service nor gcac form with acceptance.
 730         if (!hadRecentAbService($patient_id, $encdate)) {
 731             $query = "SELECT COUNT(*) AS count " .
 732             "FROM forms AS f, form_encounter AS fe, lbf_data AS d " .
 733             "WHERE f.pid = ? AND " .
 734             "f.formdir = 'LBFgcac' AND " .
 735             "f.deleted = 0 AND " .
 736             "fe.pid = f.pid AND fe.encounter = f.encounter AND " .
 737             "fe.date <= ? AND " .
 738             "DATE_ADD(fe.date, INTERVAL 14 DAY) > ? AND " .
 739             "d.form_id = f.form_id AND " .
 740             "d.field_id = 'client_status' AND " .
 741             "( d.field_value = 'maaa' OR d.field_value = 'refout' )";
 742             $irow = sqlQuery($query, array($patient_id, $encdate, $encdate));
 743             if (empty($irow['count'])) {
 744                 return;
 745             }
 746         }
 747     } elseif ($form_by === '8') { // Post-Abortion Care and Followup by Source.
 748         // Requirements just call for counting sessions, but this way the columns
 749         // can be anything - age category, religion, whatever.
 750         if (preg_match('/^25222[567]/', $code)) { // care, followup and incomplete abortion treatment
 751             $key = getGcacClientStatus($row);
 752         } else {
 753             return;
 754         }
 755
 756         /*******************************************************************
 757         // Complications of abortion by abortion method and complication type.
 758         // These may be noted either during recovery or during a followup visit.
 759         // Again, driven by services in order to report by service date.
 760         // Note: If there are multiple complications, they will all be reported.
 761         //
 762         else if ($form_by === '11') {
 763         $compl_type = '';
 764         if (preg_match('/^25222[345]/', $code)) { // all abortions including incomplete
 765         $compl_type = 'rec_compl';
 766         }
 767         else if (preg_match('/^25222[67]/', $code)) { // all post-abortion care and followup
 768         $compl_type = 'fol_compl';
 769         }
 770         else {
 771         return;
 772         }
 773         $irow = getGcacData($row, "lg.$compl_type, lo.title",
 774         "LEFT JOIN list_options AS lo ON lo.list_id = 'in_ab_proc' AND " .
 775         "lo.option_id = lg.in_ab_proc");
 776         if (empty($irow)) return; // this should not happen
 777         if (empty($irow[$compl_type])) return; // ok, no complications
 778         // We have one or more complications.
 779         $abtype = empty($irow['title']) ? xl('Indeterminate') : $irow['title'];
 780         $acompl = explode('|', $irow[$compl_type]);
 781         foreach ($acompl as $compl) {
 782         $crow = sqlQuery("SELECT title FROM list_options WHERE " .
 783         "list_id = 'complication' AND option_id = '$compl'");
 784         $key = "$abtype / " . $crow['title'];
 785         loadColumnData($key, $row);
 786         }
 787         return; // because loadColumnData() is already done.
 788         }
 789          *******************************************************************/
 790
 791         // Pre-Abortion Counseling.  Three possible situations:
 792         //   Provided abortion in the MA clinics
 793         //   Referred to other service providers (govt,private clinics)
 794         //   Decided not to have the abortion
 795         //
 796     } elseif ($form_by === '12') {
 797         if (preg_match('/^252221/', $code)) { // all pre-abortion counseling
 798             $key = getGcacClientStatus($row);
 799         } else {
 800             return;
 801         }
 802     } elseif ($form_by === '17') { // Patient Name.
 803         $key = $row['lname'] . ', ' . $row['fname'] . ' ' . $row['mname'];
 804     } else {
 805         return; // no match, so do nothing
 806     }
 807
 808   // OK we now have the reporting key for this issue.
 809     loadColumnData($key, $row, $quantity);
 810 } // end function process_ippf_code()
 811
 812 // This is called for each MA service code that is selected.
 813 //
 814 function process_ma_code($row)
 815 {
 816     global $form_by, $arr_content, $form_content;
 817
 818     $key = 'Unspecified';
 819
 820   // One row for each service category.
 821   //
 822     if ($form_by === '101') {
 823         if (!empty($row['lo_title'])) {
 824             $key = xl($row['lo_title']);
 825         }
 826     } elseif ($form_by === '102') { // Specific Services. One row for each MA code.
 827         $key = $row['code'];
 828     } elseif ($form_by === '103') { // One row for each referral source.
 829         $key = $row['referral_source'];
 830     } elseif ($form_by === '2') { // Just one row.
 831         $key = $arr_content[$form_content];
 832     } else {
 833         return;
 834     }
 835
 836     loadColumnData($key, $row);
 837 }
 838
 839 function LBFgcac_query($pid, $encounter, $name)
 840 {
 841     $query = "SELECT d.form_id, d.field_value " .
 842     "FROM forms AS f, form_encounter AS fe, lbf_data AS d " .
 843     "WHERE f.pid = ? AND " .
 844     "f.encounter = ? AND " .
 845     "f.formdir = 'LBFgcac' AND " .
 846     "f.deleted = 0 AND " .
 847     "fe.pid = f.pid AND fe.encounter = f.encounter AND " .
 848     "d.form_id = f.form_id AND " .
 849     "d.field_id = ?";
 850     return sqlStatement($query, array($pid, $encounter, $name));
 851 }
 852
 853 function LBFgcac_title($form_id, $field_id, $list_id)
 854 {
 855     $query = "SELECT lo.title " .
 856     "FROM lbf_data AS d, list_options AS lo WHERE " .
 857     "d.form_id = ? AND " .
 858     "d.field_id = ? AND " .
 859     "lo.list_id = ? AND " .
 860     "lo.option_id = d.field_value " .
 861     "LIMIT 1";
 862     $row = sqlQuery($query, array($form_id, $field_id, $list_id));
 863     return empty($row['title']) ? '' : $row['title'];
 864 }
 865
 866 // This is called for each encounter that is selected.
 867 //
 868 function process_visit($row)
 869 {
 870     global $form_by;
 871
 872     if ($form_by !== '7' && $form_by !== '11') {
 873         return;
 874     }
 875
 876   // New contraceptive method following abortion.  These should only be
 877   // present for inbound referrals.
 878   //
 879     if ($form_by === '7') {
 880         // We think this case goes away, but not sure yet.
 881         /*****************************************************************
 882       $dres = LBFgcac_query($row['pid'], $row['encounter'], 'contrameth');
 883       while ($drow = sqlFetchArray($dres)) {
 884         $a = explode('|', $drow['field_value']);
 885         foreach ($a as $methid) {
 886         if (empty($methid)) continue;
 887         $crow = sqlQuery("SELECT title FROM list_options WHERE " .
 888           "list_id = 'contrameth' AND option_id = '$methid'");
 889         $key = $crow['title'];
 890         if (empty($key)) $key = xl('Indeterminate');
 891         loadColumnData($key, $row);
 892         }
 893       }
 894         *****************************************************************/
 895     } elseif ($form_by === '11') { // Complications of abortion by abortion method and complication type.
 896         // These may be noted either during recovery or during a followup visit.
 897         // Note: If there are multiple complications, they will all be reported.
 898         $dres = LBFgcac_query($row['pid'], $row['encounter'], 'complications');
 899         while ($drow = sqlFetchArray($dres)) {
 900             $a = explode('|', $drow['field_value']);
 901             foreach ($a as $complid) {
 902                 if (empty($complid)) {
 903                     continue;
 904                 }
 905
 906                 $crow = sqlQuery("SELECT title FROM list_options WHERE " .
 907                 "list_id = 'complication' AND option_id = ?", array($complid));
 908                 $abtype = LBFgcac_title($drow['form_id'], 'in_ab_proc', 'in_ab_proc');
 909                 if (empty($abtype)) {
 910                     $abtype = xl('Indeterminate');
 911                 }
 912
 913                 $key = "$abtype / " . $crow['title'];
 914                 loadColumnData($key, $row);
 915             }
 916         }
 917     }
 918
 919   // loadColumnData() already done as needed.
 920 }
 921
 922 /*********************************************************************
 923 // This is called for each issue that is selected.
 924 //
 925 function process_issue($row) {
 926   global $form_by;
 927
 928   $key = 'Unspecified';
 929
 930   // Pre-Abortion Counseling.  Three possible rows:
 931   //   Provided abortion in the MA clinics
 932   //   Referred to other service providers (govt,private clinics)
 933   //   Decided not to have the abortion
 934   //
 935   if ($form_by === '12') {
 936
 937     // TBD: Assign one of the 3 keys, or just return.
 938
 939   }
 940
 941   // Others TBD
 942
 943   else {
 944     return;
 945   }
 946
 947   // TBD: Load column data from the issue.
 948   // loadColumnData($key, $row);
 949 }
 950 *********************************************************************/
 951
 952 // This is called for each selected referral.
 953 // Row keys are the first specified MA code, if any.
 954 //
 955 function process_referral($row)
 956 {
 957     global $form_by;
 958     $key = 'Unspecified';
 959
 960   // For followups we care about the actual service provided, otherwise
 961   // the requested service.
 962     $related_code = $form_by === '20' ?
 963     $row['reply_related_code'] : $row['refer_related_code'];
 964
 965     if (!empty($related_code)) {
 966         $relcodes = explode(';', $related_code);
 967         foreach ($relcodes as $codestring) {
 968             if ($codestring === '') {
 969                 continue;
 970             }
 971
 972             list($codetype, $code) = explode(':', $codestring);
 973
 974             if ($codetype == 'REF') {
 975                 // This is the expected case; a direct IPPF code is obsolete.
 976                 $rrow = sqlQuery("SELECT related_code FROM codes WHERE " .
 977                 "code_type = '16' AND code = ? AND active = 1 " .
 978                 "ORDER BY id LIMIT 1", array($code));
 979                 if (!empty($rrow['related_code'])) {
 980                         list($codetype, $code) = explode(':', $rrow['related_code']);
 981                 }
 982             }
 983
 984             if ($codetype !== 'IPPF') {
 985                 continue;
 986             }
 987
 988             if ($form_by === '1') {
 989                 if (preg_match('/^[12]/', $code)) {
 990                     $key = xl('SRH Referrals');
 991                     loadColumnData($key, $row);
 992                     break;
 993                 }
 994             } else { // $form_by is 9 (internal) or 10 or 20 (external) referrals
 995                 $key = $code;
 996                 break;
 997             }
 998         } // end foreach
 999     }
1000
1001     if ($form_by !== '1') {
1002         loadColumnData($key, $row);
1003     }
1004 }
1005
1006 function uses_description($form_by)
1007 {
1008     return ($form_by === '4'  || $form_by === '102' || $form_by === '9' ||
1009     $form_by === '10' || $form_by === '20' || $form_by === '104');
1010 }
1011
1012   // If we are doing the CSV export then generate the needed HTTP headers.
1013   // Otherwise generate HTML.
1014   //
1015 if ($form_output == 3) {
1016     header("Pragma: public");
1017     header("Expires: 0");
1018     header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
1019     header("Content-Type: application/force-download");
1020     header("Content-Disposition: attachment; filename=service_statistics_report.csv");
1021     header("Content-Description: File Transfer");
1022 } else {
1023     ?>
1024 <html>
1025 <head>
1026 <title><?php echo text($report_title); ?></title>
1027
1028     <?php Header::setupHeader('datetime-picker'); ?>
1029
1030 <style>
1031 body       { font-family:sans-serif; font-size:10pt; font-weight:normal }
1032 .dehead    { color:var(--black); font-family:sans-serif; font-size:10pt; font-weight:bold }
1033 .detail    { color:var(--black); font-family:sans-serif; font-size:10pt; font-weight:normal }
1034 </style>
1035
1036 <script>
1037
1038 // Begin experimental code
1039
1040 function selectByValue(sel, val) {
1041 for (var i = 0; i < sel.options.length; ++i) {
1042  if (sel.options[i].value == val) sel.options[i].selected = true;
1043 }
1044 }
1045
1046 function selreport() {
1047 var f = document.forms[0];
1048 var isdis = 'visible';
1049 var s = f.form_report;
1050 var v = (s.selectedIndex < 0) ? '' : s.options[s.selectedIndex].value;
1051 if (v.length > 0) {
1052  isdis = 'hidden';
1053  var a = v.split("|");
1054  f.form_content.selectedIndex = -1;
1055  f.form_by.selectedIndex = -1;
1056  f['form_show[]'].selectedIndex = -1;
1057  selectByValue(f.form_content, a[0]);
1058  selectByValue(f.form_by, a[1]);
1059  for (var i = 2; i < a.length; ++i) {
1060   selectByValue(f['form_show[]'], a[i]);
1061  }
1062 }
1063 f.form_by.style.visibility = isdis;
1064 f.form_content.style.visibility = isdis;
1065 f['form_show[]'].style.visibility = isdis;
1066 }
1067
1068 // End experimental code
1069
1070 $(function () {
1071     $('.datepicker').datetimepicker({
1072         <?php $datetimepicker_timepicker = false; ?>
1073         <?php $datetimepicker_showseconds = false; ?>
1074         <?php $datetimepicker_formatInput = true; ?>
1075         <?php require($GLOBALS['srcdir'] . '/js/xl/jquery-datetimepicker-2-5-4.js.php'); ?>
1076         <?php // can add any additional javascript settings to datetimepicker here; need to prepend first setting with a comma ?>
1077     });
1078 });
1079
1080 </script>
1081 </head>
1082
1083 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0'>
1084
1085 <center>
1086
1087 <h2><?php echo $report_title; ?></h2>
1088
1089 <form name='theform' method='post' action='ippf_statistics.php?t=<?php echo attr_url($report_type); ?>' onsubmit='return top.restoreSession()'>
1090 <input type="hidden" name="csrf_token_form" value="<?php echo attr(CsrfUtils::collectCsrfToken()); ?>" />
1091
1092 <table border='0' cellspacing='5' cellpadding='1'>
1093
1094 <!-- Begin experimental code -->
1095 <tr<?php echo (empty($arr_report)) ? " style='display:none'" : ""; ?>>
1096 <td valign='top' class='dehead' nowrap>
1097     <?php echo xlt('Report'); ?>:
1098 </td>
1099 <td valign='top' class='detail' colspan='3'>
1100  <select name='form_report' title='Predefined reports' onchange='selreport()'>
1101     <?php
1102     echo "    <option value=''>" . xlt('Custom') . "</option>\n";
1103     foreach ($arr_report as $key => $value) {
1104         echo "    <option value='" . attr($key) . "'";
1105         if ($key == $form_report) {
1106             echo " selected";
1107         }
1108
1109         echo ">" . text($value) . "</option>\n";
1110     }
1111     ?>
1112  </select>
1113 </td>
1114 <td valign='top' class='detail'>
1115  &nbsp;
1116 </td>
1117 </tr>
1118 <!-- End experimental code -->
1119
1120 <tr>
1121 <td valign='top' class='dehead' nowrap>
1122     <?php echo xlt('Rows'); ?>:
1123 </td>
1124 <td valign='top' class='detail'>
1125  <select name='form_by' title='Left column of report'>
1126     <?php
1127     foreach ($arr_by as $key => $value) {
1128         echo "    <option value='" . attr($key) . "'";
1129         if ($key == $form_by) {
1130             echo " selected";
1131         }
1132
1133         echo ">" . text($value) . "</option>\n";
1134     }
1135     ?>
1136  </select>
1137 </td>
1138 <td valign='top' class='dehead' nowrap>
1139     <?php echo xlt('Content'); ?>:
1140 </td>
1141 <td valign='top' class='detail'>
1142 <select name='form_content' title='<?php echo xla('What is to be counted?'); ?>'>
1143     <?php
1144     foreach ($arr_content as $key => $value) {
1145         echo "    <option value='" . attr($key) . "'";
1146         if ($key == $form_content) {
1147             echo " selected";
1148         }
1149
1150         echo ">" . text($value) . "</option>\n";
1151     }
1152     ?>
1153  </select>
1154 </td>
1155 <td valign='top' class='detail'>
1156  &nbsp;
1157 </td>
1158 </tr>
1159 <tr>
1160 <td valign='top' class='dehead' nowrap>
1161     <?php echo xlt('Columns'); ?>:
1162 </td>
1163 <td valign='top' class='detail'>
1164  <select name='form_show[]' size='4' multiple
1165 title='<?php echo xla('Hold down Ctrl to select multiple items'); ?>'>
1166     <?php
1167     foreach ($arr_show as $key => $value) {
1168         $title = $value['title'];
1169         if (empty($title) || $key == 'title') {
1170             $title = $value['description'];
1171         }
1172
1173         echo "    <option value='" . attr($key) . "'";
1174         if (is_array($form_show) && in_array($key, $form_show)) {
1175             echo " selected";
1176         }
1177
1178         echo ">" . text($title) . "</option>\n";
1179     }
1180     ?>
1181  </select>
1182 </td>
1183 <td valign='top' class='dehead' nowrap>
1184     <?php echo xlt('Filters'); ?>:
1185 </td>
1186 <td colspan='2' class='detail' style='border-style:solid;border-width:1px;border-color:#cccccc'>
1187  <table>
1188   <tr>
1189    <td valign='top' class='detail' nowrap>
1190     <?php echo xlt('Sex'); ?>:
1191    </td>
1192    <td class='detail' valign='top'>
1193   <select name='form_sexes' title='<?php echo xla('To filter by sex'); ?>'>
1194     <?php
1195     foreach (array(3 => xl('Men and Women'), 1 => xl('Women Only'), 2 => xl('Men Only')) as $key => $value) {
1196         echo "       <option value='" . attr($key) . "'";
1197         if ($key == $form_sexes) {
1198             echo " selected";
1199         }
1200
1201         echo ">" . text($value) . "</option>\n";
1202     }
1203     ?>
1204     </select>
1205    </td>
1206   </tr>
1207   <tr>
1208    <td valign='top' class='detail' nowrap>
1209     <?php echo xlt('Facility'); ?>:
1210    </td>
1211    <td valign='top' class='detail'>
1212     <?php
1213 // Build a drop-down list of facilities.
1214 //
1215     $fres = $facilityService->getAllFacility();
1216     echo "      <select name='form_facility'>\n";
1217     echo "       <option value=''>-- All Facilities --\n";
1218     foreach ($fres as $frow) {
1219         $facid = $frow['id'];
1220         echo "       <option value='" . attr($facid) . "'";
1221         if ($facid == $_POST['form_facility']) {
1222             echo " selected";
1223         }
1224
1225         echo ">" . text($frow['name']) . "\n";
1226     }
1227
1228     echo "      </select>\n";
1229     ?>
1230    </td>
1231   </tr>
1232   <tr>
1233    <td colspan='2' class='detail' nowrap>
1234     <?php echo xlt('From'); ?>
1235   <input type='text' class='datepicker' name='form_from_date' id='form_from_date' size='10' value='<?php echo attr(oeFormatShortDate($from_date)); ?>'>
1236     <?php echo xlt('To{{Range}}'); ?>
1237   <input type='text' class='datepicker' name='form_to_date' id='form_to_date' size='10' value='<?php echo attr(oeFormatShortDate($to_date)); ?>'>
1238    </td>
1239   </tr>
1240  </table>
1241 </td>
1242 </tr>
1243 <tr>
1244 <td valign='top' class='dehead' nowrap>
1245     <?php echo xlt('To{{Destination}}'); ?>:
1246 </td>
1247 <td colspan='3' valign='top' class='detail' nowrap>
1248     <?php
1249     foreach (array(1 => 'Screen', 2 => 'Printer', 3 => 'Export File') as $key => $value) {
1250         echo "   <input type='radio' name='form_output' value='" . attr($key) . "'";
1251         if ($key == $form_output) {
1252             echo ' checked';
1253         }
1254
1255         echo " />" . text($value) . " &nbsp;";
1256     }
1257     ?>
1258 </td>
1259 <td align='right' valign='top' class='detail' nowrap>
1260 <input type='submit' name='form_submit' value='<?php echo xla('Submit'); ?>'
1261 title='<?php echo xla('Click to generate the report'); ?>' />
1262 </td>
1263 </tr>
1264 <tr>
1265 <td colspan='5' height="1">
1266 </td>
1267 </tr>
1268 </table>
1269     <?php
1270 } // end not export
1271
1272 if ($_POST['form_submit']) {
1273     $pd_fields = '';
1274     foreach ($arr_show as $askey => $asval) {
1275         if (substr($askey, 0, 1) == '.') {
1276             continue;
1277         }
1278
1279         if (
1280             $askey == 'regdate' || $askey == 'sex' || $askey == 'DOB' ||
1281             $askey == 'lname' || $askey == 'fname' || $askey == 'mname' ||
1282             $askey == 'contrastart' || $askey == 'referral_source'
1283         ) {
1284             continue;
1285         }
1286
1287         $pd_fields .= ', pd.' . escape_sql_column_name($askey, array('patient_data'));
1288     }
1289
1290     $sexcond = '';
1291     if ($form_sexes == '1') {
1292         $sexcond = "AND pd.sex NOT LIKE 'Male' ";
1293     } elseif ($form_sexes == '2') {
1294         $sexcond = "AND pd.sex LIKE 'Male' ";
1295     }
1296
1297     $sqlBindArray = array();
1298
1299     // In the case where content is contraceptive product sales, we
1300     // scan product sales at the top level because it is important to
1301     // account for each of them only once.  For each sale we determine
1302     // the one and only IPPF code representing the primary related
1303     // contraceptive service, and that might be either a service in
1304     // the Tally Sheet or the IPPF code attached to the product.
1305     //
1306     if ($form_content == 5) { // sales of contraceptive products
1307         $query = "SELECT " .
1308         "ds.pid, ds.encounter, ds.sale_date, ds.quantity, " .
1309         "d.cyp_factor, d.related_code, " .
1310         "pd.regdate, pd.sex, pd.DOB, pd.lname, pd.fname, pd.mname, " .
1311         "pd.contrastart, pd.referral_source$pd_fields, " .
1312         "fe.date AS encdate, fe.provider_id " .
1313         "FROM drug_sales AS ds " .
1314         "JOIN drugs AS d ON d.drug_id = ds.drug_id " .
1315         "JOIN patient_data AS pd ON pd.pid = ds.pid $sexcond" .
1316         "LEFT JOIN form_encounter AS fe ON fe.pid = ds.pid AND fe.encounter = ds.encounter " .
1317         "WHERE ds.sale_date >= ? AND " .
1318         "ds.sale_date <= ? AND " .
1319         "ds.pid > 0 AND ds.quantity != 0";
1320         array_push($sqlBindArray, $from_date, $to_date);
1321
1322         if ($form_facility) {
1323             $query .= " AND fe.facility_id = ?";
1324             array_push($sqlBindArray, $form_facility);
1325         }
1326
1327         $query .= " ORDER BY ds.pid, ds.encounter, ds.drug_id";
1328         $res = sqlStatement($query, $sqlBindArray);
1329
1330         while ($row = sqlFetchArray($res)) {
1331             $desired = false;
1332             $prodcode = '';
1333             if ($row['cyp_factor'] > 0) {
1334                 $desired = true;
1335             }
1336
1337             $tmp = getRelatedContraceptiveCode($row);
1338             if (!empty($tmp)) {
1339                 $desired = true;
1340                 $prodcode = $tmp;
1341             }
1342
1343             if (!$desired) {
1344                 continue; // skip if not a contraceptive product
1345             }
1346
1347             // If there is a visit and it has a contraceptive service use that, else $prodcode.
1348             if (!empty($row['encounter'])) {
1349                 $query = "SELECT " .
1350                 "b.code_type, b.code, c.related_code " .
1351                 "FROM billing AS b " .
1352                 "LEFT OUTER JOIN codes AS c ON c.code_type = '12' AND " .
1353                 "c.code = b.code AND c.modifier = b.modifier " .
1354                 "WHERE b.pid = ? AND " .
1355                 "b.encounter = ? AND " .
1356                 "b.activity = 1 AND b.code_type = 'MA' " .
1357                 "ORDER BY b.code";
1358                 $bres = sqlStatement($query, array((0 + $row['pid']), (0 + $row['encounter'])));
1359                 while ($brow = sqlFetchArray($bres)) {
1360                     $tmp = getRelatedContraceptiveCode($brow);
1361                     if (!empty($tmp)) {
1362                         $prodcode = $tmp;
1363                         break;
1364                     }
1365                 }
1366             }
1367
1368             // At this point $prodcode is the desired IPPF code, or empty if none.
1369             process_ippf_code($row, $prodcode, $row['quantity']);
1370         }
1371     }
1372
1373     // Get referrals and related patient data.
1374     if ($form_content != 5 && ($form_by === '9' || $form_by === '10' || $form_by === '20' || $form_by === '1')) {
1375         $exttest = "t.refer_external = '1'";
1376         $datefld = "t.refer_date";
1377
1378         if ($form_by === '9') {
1379             $exttest = "t.refer_external = '0'";
1380         } elseif ($form_by === '20') {
1381             $datefld = "t.reply_date";
1382         }
1383
1384         $query = "SELECT " .
1385         "t.pid, t.refer_related_code, t.reply_related_code, " .
1386         "pd.regdate, pd.referral_source, " .
1387         "pd.sex, pd.DOB, pd.lname, pd.fname, pd.mname, " .
1388         "pd.contrastart$pd_fields " .
1389         "FROM transactions AS t " .
1390         "JOIN patient_data AS pd ON pd.pid = t.pid $sexcond" .
1391         "WHERE t.title = 'Referral' AND $datefld IS NOT NULL AND " .
1392         "$datefld >= ? AND $datefld <= ? AND $exttest " .
1393         "ORDER BY t.pid, t.id";
1394         $res = sqlStatement($query, array($from_date, $to_date));
1395         while ($row = sqlFetchArray($res)) {
1396             process_referral($row);
1397         }
1398     }
1399
1400     /*****************************************************************
1401     else if ($form_by === '12') {
1402     // We are reporting on a date range, and assume the applicable date is
1403     // the issue start date which is presumably also the date of pre-
1404     // abortion counseling.  The issue end date and the surgery date are
1405     // not of interest here.
1406     $query = "SELECT " .
1407       "l.type, l.begdate, l.pid, " .
1408       "pd.sex, pd.DOB, pd.lname, pd.fname, pd.mname, pd.userlist5, " .
1409       "pd.country_code, pd.status, pd.state, pd.occupation, " .
1410       "lg.client_status, lg.ab_location " .
1411       "FROM lists AS l " .
1412       "JOIN patient_data AS pd ON pd.pid = l.pid $sexcond" .
1413       "LEFT OUTER JOIN lists_ippf_gcac AS lg ON l.type = 'ippf_gcac' AND lg.id = l.id " .
1414       // "LEFT OUTER JOIN lists_ippf_con  AS lc ON l.type = 'contraceptive' AND lc.id = l.id " .
1415       "WHERE l.begdate >= '$from_date' AND l.begdate <= '$to_date' AND " .
1416       "l.activity = 1 AND l.type = 'ippf_gcac' " .
1417       "ORDER BY l.pid, l.id";
1418     $res = sqlStatement($query);
1419     while ($row = sqlFetchArray($res)) {
1420       process_issue($row);
1421     }
1422     }
1423     *****************************************************************/
1424
1425     // else {
1426
1427     /*****************************************************************
1428     if ($form_by === '104' || $form_by === '105') {
1429     $query = "SELECT " .
1430       "d.name, d.related_code, ds.pid, ds.quantity, " .
1431       "pd.regdate, pd.referral_source, " .
1432       "pd.sex, pd.DOB, pd.lname, pd.fname, pd.mname, " .
1433       "pd.contrastart$pd_fields " .
1434       "FROM drug_sales AS ds " .
1435       "JOIN drugs AS d ON d.drug_id = ds.drug_id " .
1436       "JOIN patient_data AS pd ON pd.pid = ds.pid $sexcond" .
1437       "WHERE ds.sale_date IS NOT NULL AND ds.pid != 0 AND " .
1438       "ds.sale_date >= '$from_date' AND ds.sale_date <= '$to_date' " .
1439       "ORDER BY ds.pid, ds.sale_id";
1440     $res = sqlStatement($query);
1441     while ($row = sqlFetchArray($res)) {
1442       $key = "(Unspecified)";
1443       if (!empty($row['related_code'])) {
1444         $relcodes = explode(';', $row['related_code']);
1445         foreach ($relcodes as $codestring) {
1446           if ($codestring === '') continue;
1447           list($codetype, $code) = explode(':', $codestring);
1448           if ($codetype !== 'IPPF') continue;
1449           $key = getContraceptiveMethod($code);
1450           if (!empty($key)) break;
1451           $key = "(No Method)";
1452         }
1453       }
1454       if ($form_by === '104') $key .= " / " . $row['name'];
1455       loadColumnData($key, $row, $row['quantity']);
1456     }
1457     }
1458
1459     if ($form_by !== '9' && $form_by !== '10' && $form_by !== '20' &&
1460     $form_by !== '104' && $form_by !== '105')
1461     *****************************************************************/
1462
1463     if ($form_content != 5 && $form_by !== '9' && $form_by !== '10' && $form_by !== '20') {
1464         $sqlBindArray = array();
1465
1466         // This gets us all MA codes, with encounter and patient
1467         // info attached and grouped by patient and encounter.
1468         $query = "SELECT " .
1469         "fe.pid, fe.encounter, fe.date AS encdate, pd.regdate, " .
1470         "f.user AS provider, " .
1471         "pd.sex, pd.DOB, pd.lname, pd.fname, pd.mname, " .
1472         "pd.contrastart, pd.referral_source$pd_fields, " .
1473         "b.code_type, b.code, c.related_code, lo.title AS lo_title " .
1474         "FROM form_encounter AS fe " .
1475         "JOIN forms AS f ON f.pid = fe.pid AND f.encounter = fe.encounter AND " .
1476         "f.formdir = 'newpatient' AND f.form_id = fe.id AND f.deleted = 0 " .
1477         "JOIN patient_data AS pd ON pd.pid = fe.pid $sexcond" .
1478         "LEFT OUTER JOIN billing AS b ON " .
1479         "b.pid = fe.pid AND b.encounter = fe.encounter AND b.activity = 1 " .
1480         "AND b.code_type = 'MA' " .
1481         "LEFT OUTER JOIN codes AS c ON b.code_type = 'MA' AND c.code_type = '12' AND " .
1482         "c.code = b.code AND c.modifier = b.modifier " .
1483         "LEFT OUTER JOIN list_options AS lo ON " .
1484         "lo.list_id = 'superbill' AND lo.option_id = c.superbill " .
1485         "WHERE fe.date >= ? AND " .
1486         "fe.date <= ? ";
1487         array_push($sqlBindArray, $from_date . ' 00:00:00', $to_date . ' 23:59:59');
1488
1489         if ($form_facility) {
1490             $query .= "AND fe.facility_id = '$form_facility' ";
1491             array_push($sqlBindArray, $form_facility);
1492         }
1493
1494         $query .= "ORDER BY fe.pid, fe.encounter, b.code";
1495         $res = sqlStatement($query, $sqlBindArray);
1496
1497         $prev_encounter = 0;
1498
1499         while ($row = sqlFetchArray($res)) {
1500             if ($row['encounter'] != $prev_encounter) {
1501                 $prev_encounter = $row['encounter'];
1502                 process_visit($row);
1503             }
1504
1505             if ($row['code_type'] === 'MA') {
1506                 process_ma_code($row);
1507                 if (!empty($row['related_code'])) {
1508                     $relcodes = explode(';', $row['related_code']);
1509                     foreach ($relcodes as $codestring) {
1510                         if ($codestring === '') {
1511                             continue;
1512                         }
1513
1514                         list($codetype, $code) = explode(':', $codestring);
1515                         if ($codetype !== 'IPPF') {
1516                             continue;
1517                         }
1518
1519                         process_ippf_code($row, $code);
1520                     }
1521                 }
1522             }
1523         } // end while
1524     } // end if
1525
1526     // Sort everything by key for reporting.
1527     ksort($areport);
1528     foreach ($arr_titles as $atkey => $dummy) {
1529         ksort($arr_titles[$atkey]);
1530     }
1531
1532     if ($form_output != 3) {
1533         echo "<table border='0' cellpadding='1' cellspacing='2' width='98%'>\n";
1534     } // end not csv export
1535
1536     // Generate first column headings line, with category titles.
1537     //
1538     genStartRow("bgcolor='#dddddd'");
1539     // If the key is an MA or IPPF code, then add a column for its description.
1540     if (uses_description($form_by)) {
1541         genHeadCell(array('', ''));
1542     } else {
1543         genHeadCell('');
1544     }
1545
1546     // Generate headings for values to be shown.
1547     foreach ($form_show as $value) {
1548         if ($value == '.total') { // Total Services
1549             genHeadCell('');
1550         } elseif ($value == '.age2') { // Age
1551             genHeadCell($arr_show[$value]['title'], false, 2);
1552         } elseif ($value == '.age9') { // Age
1553             genHeadCell($arr_show[$value]['title'], false, 9);
1554         } elseif ($arr_show[$value]['list_id']) {
1555             genHeadCell($arr_show[$value]['title'], false, count($arr_titles[$value]));
1556         } elseif (!empty($arr_titles[$value])) {
1557             genHeadCell($arr_show[$value]['title'], false, count($arr_titles[$value]));
1558         }
1559     }
1560
1561     if ($form_output != 3) {
1562         genHeadCell('');
1563     }
1564
1565     genEndRow();
1566
1567     // Generate second column headings line, with individual titles.
1568     //
1569     genStartRow("bgcolor='#dddddd'");
1570     // If the key is an MA or IPPF code, then add a column for its description.
1571     if (uses_description($form_by)) {
1572         genHeadCell(array($arr_by[$form_by], xl('Description')));
1573     } else {
1574         genHeadCell($arr_by[$form_by]);
1575     }
1576
1577     // Generate headings for values to be shown.
1578     foreach ($form_show as $value) {
1579         if ($value == '.total') { // Total Services
1580             genHeadCell(xl('Total'));
1581         } elseif ($value == '.age2') { // Age
1582             genHeadCell(xl('0-24'), true);
1583             genHeadCell(xl('25+'), true);
1584         } elseif ($value == '.age9') { // Age
1585             genHeadCell(xl('0-10'), true);
1586             genHeadCell(xl('11-14'), true);
1587             genHeadCell(xl('15-19'), true);
1588             genHeadCell(xl('20-24'), true);
1589             genHeadCell(xl('25-29'), true);
1590             genHeadCell(xl('30-34'), true);
1591             genHeadCell(xl('35-39'), true);
1592             genHeadCell(xl('40-44'), true);
1593             genHeadCell(xl('45+'), true);
1594         } elseif ($arr_show[$value]['list_id']) {
1595             foreach ($arr_titles[$value] as $key => $dummy) {
1596                 genHeadCell(getListTitle($arr_show[$value]['list_id'], $key), true);
1597             }
1598         } elseif (!empty($arr_titles[$value])) {
1599             foreach ($arr_titles[$value] as $key => $dummy) {
1600                 genHeadCell($key, true);
1601             }
1602         }
1603     }
1604
1605     if ($form_output != 3) {
1606         genHeadCell(xl('Total'), true);
1607     }
1608
1609     genEndRow();
1610
1611     $encount = 0;
1612
1613     foreach ($areport as $key => $varr) {
1614         $bgcolor = (++$encount & 1) ? "#ddddff" : "#ffdddd";
1615
1616         $dispkey = $key;
1617
1618       // If the key is an MA or IPPF code, then add a column for its description.
1619         if (uses_description($form_by)) {
1620             $dispkey = array($key, '');
1621             $type = $form_by === '102' ? 12 : 11; // MA or IPPF
1622             $crow = sqlQuery("SELECT code_text FROM codes WHERE " .
1623             "code_type = ? AND code = ? ORDER BY id LIMIT 1", array($type, $key));
1624             if (!empty($crow['code_text'])) {
1625                 $dispkey[1] = $crow['code_text'];
1626             }
1627         }
1628
1629         genStartRow("bgcolor='$bgcolor'");
1630
1631         genAnyCell($dispkey, false, 'detail');
1632
1633       // This is the column index for accumulating column totals.
1634         $cnum = 0;
1635         $totalsvcs = $areport[$key]['.wom'] + $areport[$key]['.men'];
1636
1637       // Generate data for this row.
1638         foreach ($form_show as $value) {
1639             // if ($value == '1') { // Total Services
1640             if ($value == '.total') { // Total Services
1641                 genNumCell($totalsvcs, $cnum++);
1642             } elseif ($value == '.age2') { // Age
1643                 for ($i = 0; $i < 2; ++$i) {
1644                     genNumCell($areport[$key]['.age2'][$i], $cnum++);
1645                 }
1646             } elseif ($value == '.age9') { // Age
1647                 for ($i = 0; $i < 9; ++$i) {
1648                     genNumCell($areport[$key]['.age9'][$i], $cnum++);
1649                 }
1650             } elseif (!empty($arr_titles[$value])) {
1651                 foreach ($arr_titles[$value] as $title => $dummy) {
1652                     genNumCell($areport[$key][$value][$title], $cnum++);
1653                 }
1654             }
1655         }
1656
1657       // Write the Total column data.
1658         if ($form_output != 3) {
1659             $atotals[$cnum] += $totalsvcs;
1660             genAnyCell($totalsvcs, true, 'dehead');
1661         }
1662
1663         genEndRow();
1664     } // end foreach
1665
1666     if ($form_output != 3) {
1667       // Generate the line of totals.
1668         genStartRow("bgcolor='#dddddd'");
1669
1670       // If the key is an MA or IPPF code, then add a column for its description.
1671         if (uses_description($form_by)) {
1672             genHeadCell(array(xl('Totals'), ''));
1673         } else {
1674             genHeadCell(xl('Totals'));
1675         }
1676
1677         for ($cnum = 0; $cnum < count($atotals); ++$cnum) {
1678             genHeadCell($atotals[$cnum], true);
1679         }
1680
1681         genEndRow();
1682       // End of table.
1683         echo "</table>\n";
1684     }
1685 } // end of if refresh or export
1686
1687 if ($form_output != 3) {
1688     ?>
1689 </form>
1690 </center>
1691
1692 <script>
1693 selreport();
1694     <?php if ($form_output == 2) { ?>
1695  var win = top.printLogPrint ? top : opener.top;
1696  win.printLogPrint(window);
1697 <?php } ?>
1698 </script>
1699
1700 </body>
1701 </html>
1702     <?php
1703 } // end not export
1704 ?>