interface/reports/ippf_statistics.php

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