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 // Get the "client status" as descriptive text.
 377 //
 378 function getGcacClientStatus($row) {
 379   $pid = $row['pid'];
 380   $encdate = $row['encdate'];
 381
 382   // Check for abortion service in Tally Sheet.
 383   $query = "SELECT COUNT(*) AS count " .
 384     "FROM form_encounter AS fe, billing AS b, codes AS c WHERE " .
 385     "fe.pid = '$pid' AND " .
 386     "fe.date <= '$encdate' AND " .
 387     "DATE_ADD(fe.date, INTERVAL 14 DAY) > '$encdate' AND " .
 388     "b.pid = fe.pid AND " .
 389     "b.encounter = fe.encounter AND " .
 390     "b.activity = 1 AND " .
 391     "b.code_type = 'MA' AND " .
 392     "c.code_type = '12' AND " .
 393     "c.code = b.code AND c.modifier = b.modifier AND " .
 394     "( c.related_code LIKE '%IPPF:252223%' OR c.related_code LIKE '%IPPF:252224%' )";
 395   $tmp = sqlQuery($query);
 396   if (!empty($tmp['count'])) return xl('MA Client Accepting Abortion');
 397
 398   // Check for a GCAC visit form.
 399   // This will the most recent GCAC visit form for visits within
 400   // the past 2 weeks, although there really should be such a form
 401   // attached to the visit associated with $row.
 402   $query = "SELECT lo.title " .
 403     "FROM forms AS f, form_encounter AS fe, lbf_data AS d, list_options AS lo " .
 404     "WHERE f.pid = '$pid' AND " .
 405     "f.formdir = 'LBFgcac' AND " .
 406     "f.deleted = 0 AND " .
 407     "fe.pid = f.pid AND fe.encounter = f.encounter AND " .
 408     "fe.date <= '$encdate' AND " .
 409     "DATE_ADD(fe.date, INTERVAL 14 DAY) > '$encdate' AND " .
 410     "d.form_id = f.form_id AND " .
 411     "d.field_id = 'client_status' AND " .
 412     "lo.list_id = 'clientstatus' AND " .
 413     "lo.option_id = d.field_value " .
 414     "ORDER BY d.form_id DESC LIMIT 1";
 415   $irow = sqlQuery($query);
 416   if (!empty($irow['title'])) return $irow['title'];
 417
 418   // Check for a referred abortion.
 419   /*
 420   $query = "SELECT COUNT(*) AS count " .
 421     "FROM transactions AS t, codes AS c WHERE " .
 422     "t.title = 'Referral' AND " .
 423     "t.refer_date IS NOT NULL AND " .
 424     "t.refer_date <= '$encdate' AND " .
 425     "DATE_ADD(t.refer_date, INTERVAL 14 DAY) > '$encdate' AND " .
 426     "t.refer_related_code LIKE 'REF:%' AND " .
 427     "c.code_type = '16' AND " .
 428     "c.code = SUBSTRING(t.refer_related_code, 5) AND " .
 429     "( c.related_code LIKE '%IPPF:252223%' OR c.related_code LIKE '%IPPF:252224%' )";
 430   */
 431   $query = "SELECT COUNT(*) AS count " .
 432     "FROM transactions AS t " .
 433     "LEFT JOIN codes AS c ON t.refer_related_code LIKE 'REF:%' AND " .
 434     "c.code_type = '16' AND " .
 435     "c.code = SUBSTRING(t.refer_related_code, 5) " .
 436     "WHERE " .
 437     "t.title = 'Referral' AND " .
 438     "t.refer_date IS NOT NULL AND " .
 439     "t.refer_date <= '$encdate' AND " .
 440     "DATE_ADD(t.refer_date, INTERVAL 14 DAY) > '$encdate' AND " .
 441     "( t.refer_related_code LIKE '%IPPF:252223%' OR " .
 442     "t.refer_related_code LIKE '%IPPF:252224%' OR " .
 443     "( c.related_code IS NOT NULL AND " .
 444     "( c.related_code LIKE '%IPPF:252223%' OR " .
 445     "c.related_code LIKE '%IPPF:252224%' )))";
 446
 447   $tmp = sqlQuery($query);
 448   if (!empty($tmp['count'])) return xl('Outbound Referral');
 449
 450   return xl('Indeterminate');
 451 }
 452
 453 // Helper function called after the reporting key is determined for a row.
 454 //
 455 function loadColumnData($key, $row, $quantity=1) {
 456   global $areport, $arr_titles, $form_content, $from_date, $to_date, $arr_show;
 457
 458   // If first instance of this key, initialize its arrays.
 459   if (empty($areport[$key])) {
 460     $areport[$key] = array();
 461     $areport[$key]['.prp'] = 0;       // previous pid
 462     $areport[$key]['.wom'] = 0;       // number of services for women
 463     $areport[$key]['.men'] = 0;       // number of services for men
 464     $areport[$key]['.age2'] = array(0,0);               // age array
 465     $areport[$key]['.age9'] = array(0,0,0,0,0,0,0,0,0); // age array
 466     foreach ($arr_show as $askey => $dummy) {
 467       if (substr($askey, 0, 1) == '.') continue;
 468       $areport[$key][$askey] = array();
 469     }
 470   }
 471
 472   // Skip this key if we are counting unique patients and the key
 473   // has already seen this patient.
 474   if ($form_content == '2' && $row['pid'] == $areport[$key]['.prp']) return;
 475
 476   // If we are counting new acceptors, then require a unique patient
 477   // whose contraceptive start date is within the reporting period.
 478   if ($form_content == '3') {
 479     // if ($row['pid'] == $areport[$key]['prp']) return;
 480     if ($row['pid'] == $areport[$key]['.prp']) return;
 481     // Check contraceptive start date.
 482     if (!$row['contrastart'] || $row['contrastart'] < $from_date ||
 483       $row['contrastart'] > $to_date) return;
 484   }
 485
 486   // If we are counting new clients, then require a unique patient
 487   // whose registration date is within the reporting period.
 488   if ($form_content == '4') {
 489     if ($row['pid'] == $areport[$key]['.prp']) return;
 490     // Check registration date.
 491     if (!$row['regdate'] || $row['regdate'] < $from_date ||
 492       $row['regdate'] > $to_date) return;
 493   }
 494
 495   // Flag this patient as having been encountered for this report row.
 496   // $areport[$key]['prp'] = $row['pid'];
 497   $areport[$key]['.prp'] = $row['pid'];
 498
 499   // Increment the correct sex category.
 500   if (strcasecmp($row['sex'], 'Male') == 0)
 501     $areport[$key]['.men'] += $quantity;
 502   else
 503     $areport[$key]['.wom'] += $quantity;
 504
 505   // Increment the correct age categories.
 506   $age = getAge(fixDate($row['DOB']), $row['encdate']);
 507   $i = min(intval(($age - 5) / 5), 8);
 508   if ($age < 11) $i = 0;
 509   $areport[$key]['.age9'][$i] += $quantity;
 510   $i = $age < 25 ? 0 : 1;
 511   $areport[$key]['.age2'][$i] += $quantity;
 512
 513   foreach ($arr_show as $askey => $dummy) {
 514     if (substr($askey, 0, 1) == '.') continue;
 515     $status = empty($row[$askey]) ? 'Unspecified' : $row[$askey];
 516     $areport[$key][$askey][$status] += $quantity;
 517     $arr_titles[$askey][$status] += $quantity;
 518   }
 519 }
 520
 521 // This is called for each IPPF service code that is selected.
 522 //
 523 function process_ippf_code($row, $code, $quantity=1) {
 524   global $areport, $arr_titles, $form_by, $form_content;
 525
 526   $key = 'Unspecified';
 527
 528   // SRH including Family Planning
 529   //
 530   if ($form_by === '1') {
 531     if (preg_match('/^1/', $code)) {
 532       $key = xl('SRH - Family Planning');
 533     }
 534     else if (preg_match('/^2/', $code)) {
 535       $key = xl('SRH Non Family Planning');
 536     }
 537     else {
 538       if ($form_content != 5) return;
 539     }
 540   }
 541
 542   // General Service Category
 543   //
 544   else if ($form_by === '3') {
 545     if (preg_match('/^1/', $code)) {
 546       $key = xl('SRH - Family Planning');
 547     }
 548     else if (preg_match('/^2/', $code)) {
 549       $key = xl('SRH Non Family Planning');
 550     }
 551     else if (preg_match('/^3/', $code)) {
 552       $key = xl('Non-SRH Medical');
 553     }
 554     else if (preg_match('/^4/', $code)) {
 555       $key = xl('Non-SRH Non-Medical');
 556     }
 557     else {
 558       $key = xl('Invalid Service Codes');
 559     }
 560   }
 561
 562   // Abortion-Related Category
 563   //
 564   else if ($form_by === '13') {
 565     if (preg_match('/^252221/', $code)) {
 566       $key = xl('Pre-Abortion Counseling');
 567     }
 568     else if (preg_match('/^252222/', $code)) {
 569       $key = xl('Pre-Abortion Consultation');
 570     }
 571     else if (preg_match('/^252223/', $code)) {
 572       $key = xl('Induced Abortion');
 573     }
 574     else if (preg_match('/^252224/', $code)) {
 575       $key = xl('Medical Abortion');
 576     }
 577     else if (preg_match('/^252225/', $code)) {
 578       $key = xl('Incomplete Abortion Treatment');
 579     }
 580     else if (preg_match('/^252226/', $code)) {
 581       $key = xl('Post-Abortion Care');
 582     }
 583     else if (preg_match('/^252227/', $code)) {
 584       $key = xl('Post-Abortion Counseling');
 585     }
 586     else if (preg_match('/^25222/', $code)) {
 587       $key = xl('Other/Generic Abortion-Related');
 588     }
 589     else {
 590       if ($form_content != 5) return;
 591     }
 592   }
 593
 594   // Specific Services. One row for each IPPF code.
 595   //
 596   else if ($form_by === '4') {
 597     $key = $code;
 598   }
 599
 600   // Specific Contraceptive Services. One row for each IPPF code.
 601   //
 602   else if ($form_by === '104') {
 603     if ($form_content != 5) {
 604       // Skip codes not for contraceptive services.
 605       $tmp = getContraceptiveMethod($code);
 606       if (empty($tmp)) return;
 607     }
 608     $key = $code;
 609   }
 610
 611   // Abortion Method.
 612   //
 613   else if ($form_by === '5') {
 614     $key = getAbortionMethod($code);
 615     if (empty($key)) {
 616       if ($form_content != 5) return;
 617       $key = 'Unspecified';
 618     }
 619   }
 620
 621   // Contraceptive Method.
 622   //
 623   else if ($form_by === '6') {
 624     $key = getContraceptiveMethod($code);
 625     if (empty($key)) {
 626       if ($form_content != 5) return;
 627       $key = 'Unspecified';
 628     }
 629   }
 630
 631   /*******************************************************************
 632   // Contraceptive method for new contraceptive adoption following abortion.
 633   // Get it from the IPPF code if an abortion issue is linked to the visit.
 634   // Note we are handling this during processing of services rather than
 635   // by enumerating issues, because we need the service date.
 636   //
 637   else if ($form_by === '7') {
 638     $key = getContraceptiveMethod($code);
 639     if (empty($key)) return;
 640     $patient_id = $row['pid'];
 641     $encounter_id = $row['encounter'];
 642     $query = "SELECT COUNT(*) AS count " .
 643       "FROM lists AS l " .
 644       "JOIN issue_encounter AS ie ON ie.pid = '$patient_id' AND " .
 645       "ie.encounter = '$encounter_id' AND ie.list_id = l.id " .
 646       "WHERE l.pid = '$patient_id' AND " .
 647       "l.activity = 1 AND l.type = 'ippf_gcac'";
 648     // echo "<!-- $key: $query -->\n"; // debugging
 649     $irow = sqlQuery($query);
 650     if (empty($irow['count'])) return;
 651   }
 652   *******************************************************************/
 653
 654   // Contraceptive method for new contraceptive adoption following abortion.
 655   // Get it from the IPPF code if there is a suitable recent GCAC form.
 656   //
 657   else if ($form_by === '7') {
 658     $key = getContraceptiveMethod($code);
 659     if (empty($key)) return;
 660     $patient_id = $row['pid'];
 661     $encdate = $row['encdate'];
 662     $query = "SELECT COUNT(*) AS count " .
 663       "FROM forms AS f, form_encounter AS fe, lbf_data AS d " .
 664       "WHERE f.pid = '$patient_id' AND " .
 665       "f.formdir = 'LBFgcac' AND " .
 666       "f.deleted = 0 AND " .
 667       "fe.pid = f.pid AND fe.encounter = f.encounter AND " .
 668       "fe.date <= '$encdate' AND " .
 669       "DATE_ADD(fe.date, INTERVAL 14 DAY) > '$encdate' AND " .
 670       "d.form_id = f.form_id AND " .
 671       "d.field_id = 'client_status' AND " .
 672       "( d.field_value = 'maaa' OR d.field_value = 'refout' )";
 673     // echo "<!-- $key: $query -->\n"; // debugging
 674     $irow = sqlQuery($query);
 675     if (empty($irow['count'])) return;
 676   }
 677
 678   // Post-Abortion Care and Followup by Source.
 679   // Requirements just call for counting sessions, but this way the columns
 680   // can be anything - age category, religion, whatever.
 681   //
 682   else if ($form_by === '8') {
 683     if (preg_match('/^25222[567]/', $code)) { // care, followup and incomplete abortion treatment
 684       $key = getGcacClientStatus($row);
 685     } else {
 686       return;
 687     }
 688   }
 689
 690   /*******************************************************************
 691   // Complications of abortion by abortion method and complication type.
 692   // These may be noted either during recovery or during a followup visit.
 693   // Again, driven by services in order to report by service date.
 694   // Note: If there are multiple complications, they will all be reported.
 695   //
 696   else if ($form_by === '11') {
 697     $compl_type = '';
 698     if (preg_match('/^25222[345]/', $code)) { // all abortions including incomplete
 699       $compl_type = 'rec_compl';
 700     }
 701     else if (preg_match('/^25222[67]/', $code)) { // all post-abortion care and followup
 702       $compl_type = 'fol_compl';
 703     }
 704     else {
 705       return;
 706     }
 707     $irow = getGcacData($row, "lg.$compl_type, lo.title",
 708       "LEFT JOIN list_options AS lo ON lo.list_id = 'in_ab_proc' AND " .
 709       "lo.option_id = lg.in_ab_proc");
 710     if (empty($irow)) return; // this should not happen
 711     if (empty($irow[$compl_type])) return; // ok, no complications
 712     // We have one or more complications.
 713     $abtype = empty($irow['title']) ? xl('Indeterminate') : $irow['title'];
 714     $acompl = explode('|', $irow[$compl_type]);
 715     foreach ($acompl as $compl) {
 716       $crow = sqlQuery("SELECT title FROM list_options WHERE " .
 717         "list_id = 'complication' AND option_id = '$compl'");
 718       $key = "$abtype / " . $crow['title'];
 719       loadColumnData($key, $row);
 720     }
 721     return; // because loadColumnData() is already done.
 722   }
 723   *******************************************************************/
 724
 725   // Pre-Abortion Counseling.  Three possible situations:
 726   //   Provided abortion in the MA clinics
 727   //   Referred to other service providers (govt,private clinics)
 728   //   Decided not to have the abortion
 729   //
 730   else if ($form_by === '12') {
 731     if (preg_match('/^252221/', $code)) { // all pre-abortion counseling
 732       $key = getGcacClientStatus($row);
 733     } else {
 734       return;
 735     }
 736   }
 737
 738   // Patient Name.
 739   //
 740   else if ($form_by === '17') {
 741     $key = $row['lname'] . ', ' . $row['fname'] . ' ' . $row['mname'];
 742   }
 743
 744   else {
 745     return; // no match, so do nothing
 746   }
 747
 748   // OK we now have the reporting key for this issue.
 749   loadColumnData($key, $row, $quantity);
 750
 751 } // end function process_ippf_code()
 752
 753 // This is called for each MA service code that is selected.
 754 //
 755 function process_ma_code($row) {
 756   global $form_by, $arr_content, $form_content;
 757
 758   $key = 'Unspecified';
 759
 760   // One row for each service category.
 761   //
 762   if ($form_by === '101') {
 763     if (!empty($row['lo_title'])) $key = xl($row['lo_title']);
 764   }
 765
 766   // Specific Services. One row for each MA code.
 767   //
 768   else if ($form_by === '102') {
 769     $key = $row['code'];
 770   }
 771
 772   // One row for each referral source.
 773   //
 774   else if ($form_by === '103') {
 775     $key = $row['referral_source'];
 776   }
 777
 778   // Just one row.
 779   //
 780   else if ($form_by === '2') {
 781     $key = $arr_content[$form_content];
 782   }
 783
 784   else {
 785     return;
 786   }
 787
 788   loadColumnData($key, $row);
 789 }
 790
 791 function LBFgcac_query($pid, $encounter, $name) {
 792   $query = "SELECT d.form_id, d.field_value " .
 793     "FROM forms AS f, form_encounter AS fe, lbf_data AS d " .
 794     "WHERE f.pid = '$pid' AND " .
 795     "f.encounter = '$encounter' AND " .
 796     "f.formdir = 'LBFgcac' AND " .
 797     "f.deleted = 0 AND " .
 798     "fe.pid = f.pid AND fe.encounter = f.encounter AND " .
 799     "d.form_id = f.form_id AND " .
 800     "d.field_id = '$name'";
 801   return sqlStatement($query);
 802 }
 803
 804 function LBFgcac_title($form_id, $field_id, $list_id) {
 805   $query = "SELECT lo.title " .
 806     "FROM lbf_data AS d, list_options AS lo WHERE " .
 807     "d.form_id = '$form_id' AND " .
 808     "d.field_id = '$field_id' AND " .
 809     "lo.list_id = '$list_id' AND " .
 810     "lo.option_id = d.field_value " .
 811     "LIMIT 1";
 812   $row = sqlQuery($query);
 813   return empty($row['title']) ? '' : $row['title'];
 814 }
 815
 816 // This is called for each encounter that is selected.
 817 //
 818 function process_visit($row) {
 819   global $form_by;
 820
 821   if ($form_by !== '7' && $form_by !== '11') return;
 822
 823   // New contraceptive method following abortion.  These should only be
 824   // present for inbound referrals.
 825   //
 826   if ($form_by === '7') {
 827     // We think this case goes away, but not sure yet.
 828     /*****************************************************************
 829     $dres = LBFgcac_query($row['pid'], $row['encounter'], 'contrameth');
 830     while ($drow = sqlFetchArray($dres)) {
 831       $a = explode('|', $drow['field_value']);
 832       foreach ($a as $methid) {
 833         if (empty($methid)) continue;
 834         $crow = sqlQuery("SELECT title FROM list_options WHERE " .
 835           "list_id = 'contrameth' AND option_id = '$methid'");
 836         $key = $crow['title'];
 837         if (empty($key)) $key = xl('Indeterminate');
 838         loadColumnData($key, $row);
 839       }
 840     }
 841     *****************************************************************/
 842   }
 843
 844   // Complications of abortion by abortion method and complication type.
 845   // These may be noted either during recovery or during a followup visit.
 846   // Note: If there are multiple complications, they will all be reported.
 847   //
 848   else if ($form_by === '11') {
 849     $dres = LBFgcac_query($row['pid'], $row['encounter'], 'complications');
 850     while ($drow = sqlFetchArray($dres)) {
 851       $a = explode('|', $drow['field_value']);
 852       foreach ($a as $complid) {
 853         if (empty($complid)) continue;
 854         $crow = sqlQuery("SELECT title FROM list_options WHERE " .
 855           "list_id = 'complication' AND option_id = '$complid'");
 856         $abtype = LBFgcac_title($drow['form_id'], 'in_ab_proc', 'in_ab_proc');
 857         if (empty($abtype)) $abtype = xl('Indeterminate');
 858         $key = "$abtype / " . $crow['title'];
 859         loadColumnData($key, $row);
 860       }
 861     }
 862   }
 863
 864   // loadColumnData() already done as needed.
 865 }
 866
 867 /*********************************************************************
 868 // This is called for each issue that is selected.
 869 //
 870 function process_issue($row) {
 871   global $form_by;
 872
 873   $key = 'Unspecified';
 874
 875   // Pre-Abortion Counseling.  Three possible rows:
 876   //   Provided abortion in the MA clinics
 877   //   Referred to other service providers (govt,private clinics)
 878   //   Decided not to have the abortion
 879   //
 880   if ($form_by === '12') {
 881
 882     // TBD: Assign one of the 3 keys, or just return.
 883
 884   }
 885
 886   // Others TBD
 887
 888   else {
 889     return;
 890   }
 891
 892   // TBD: Load column data from the issue.
 893   // loadColumnData($key, $row);
 894 }
 895 *********************************************************************/
 896
 897 // This is called for each selected referral.
 898 // Row keys are the first specified MA code, if any.
 899 //
 900 function process_referral($row) {
 901   global $form_by;
 902   $key = 'Unspecified';
 903
 904   // For followups we care about the actual service provided, otherwise
 905   // the requested service.
 906   $related_code = $form_by === '20' ?
 907     $row['reply_related_code'] : $row['refer_related_code'];
 908
 909   if (!empty($related_code)) {
 910     $relcodes = explode(';', $related_code);
 911     foreach ($relcodes as $codestring) {
 912       if ($codestring === '') continue;
 913       list($codetype, $code) = explode(':', $codestring);
 914
 915       if ($codetype == 'REF') {
 916         // This is the expected case; a direct IPPF code is obsolete.
 917         $rrow = sqlQuery("SELECT related_code FROM codes WHERE " .
 918           "code_type = '16' AND code = '$code' AND active = 1 " .
 919           "ORDER BY id LIMIT 1");
 920         if (!empty($rrow['related_code'])) {
 921           list($codetype, $code) = explode(':', $rrow['related_code']);
 922         }
 923       }
 924
 925       if ($codetype !== 'IPPF') continue;
 926
 927       if ($form_by === '1') {
 928         if (preg_match('/^[12]/', $code)) {
 929           $key = xl('SRH Referrals');
 930           loadColumnData($key, $row);
 931           break;
 932         }
 933       }
 934       else { // $form_by is 9 (internal) or 10 or 20 (external) referrals
 935         $key = $code;
 936         break;
 937       }
 938     } // end foreach
 939   }
 940
 941   if ($form_by !== '1') loadColumnData($key, $row);
 942 }
 943
 944 function uses_description($form_by) {
 945   return ($form_by === '4'  || $form_by === '102' || $form_by === '9' ||
 946     $form_by === '10' || $form_by === '20' || $form_by === '104');
 947 }
 948
 949   // If we are doing the CSV export then generate the needed HTTP headers.
 950   // Otherwise generate HTML.
 951   //
 952   if ($form_output == 3) {
 953     header("Pragma: public");
 954     header("Expires: 0");
 955     header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
 956     header("Content-Type: application/force-download");
 957     header("Content-Disposition: attachment; filename=service_statistics_report.csv");
 958     header("Content-Description: File Transfer");
 959   }
 960   else {
 961 ?>
 962 <html>
 963 <head>
 964 <?php html_header_show(); ?>
 965 <title><?php echo $report_title; ?></title>
 966 <style type="text/css">@import url(../../library/dynarch_calendar.css);</style>
 967 <style type="text/css">
 968  body       { font-family:sans-serif; font-size:10pt; font-weight:normal }
 969  .dehead    { color:#000000; font-family:sans-serif; font-size:10pt; font-weight:bold }
 970  .detail    { color:#000000; font-family:sans-serif; font-size:10pt; font-weight:normal }
 971 </style>
 972 <script type="text/javascript" src="../../library/textformat.js"></script>
 973 <script type="text/javascript" src="../../library/dynarch_calendar.js"></script>
 974 <script type="text/javascript" src="../../library/dynarch_calendar_en.js"></script>
 975 <script type="text/javascript" src="../../library/dynarch_calendar_setup.js"></script>
 976 <script language="JavaScript">
 977  var mypcc = '<?php echo $GLOBALS['phone_country_code'] ?>';
 978
 979  // Begin experimental code
 980
 981  function selectByValue(sel, val) {
 982   for (var i = 0; i < sel.options.length; ++i) {
 983    if (sel.options[i].value == val) sel.options[i].selected = true;
 984   }
 985  }
 986
 987  function selreport() {
 988   var f = document.forms[0];
 989   var isdis = 'visible';
 990   var s = f.form_report;
 991   var v = (s.selectedIndex < 0) ? '' : s.options[s.selectedIndex].value;
 992   if (v.length > 0) {
 993    isdis = 'hidden';
 994    var a = v.split("|");
 995    f.form_content.selectedIndex = -1;
 996    f.form_by.selectedIndex = -1;
 997    f['form_show[]'].selectedIndex = -1;
 998    selectByValue(f.form_content, a[0]);
 999    selectByValue(f.form_by, a[1]);
1000    for (var i = 2; i < a.length; ++i) {
1001     selectByValue(f['form_show[]'], a[i]);
1002    }
1003   }
1004   f.form_by.style.visibility = isdis;
1005   f.form_content.style.visibility = isdis;
1006   f['form_show[]'].style.visibility = isdis;
1007  }
1008
1009  // End experimental code
1010
1011 </script>
1012 </head>
1013
1014 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0'>
1015
1016 <center>
1017
1018 <h2><?php echo $report_title; ?></h2>
1019
1020 <form name='theform' method='post'
1021  action='ippf_statistics.php?t=<?php echo $report_type ?>'>
1022
1023 <table border='0' cellspacing='5' cellpadding='1'>
1024
1025  <!-- Begin experimental code -->
1026  <tr<?php if (empty($arr_report)) echo " style='display:none'"; ?>>
1027   <td valign='top' class='dehead' nowrap>
1028    <?php xl('Report','e'); ?>:
1029   </td>
1030   <td valign='top' class='detail' colspan='3'>
1031    <select name='form_report' title='Predefined reports' onchange='selreport()'>
1032 <?php
1033   echo "    <option value=''>" . xl('Custom') . "</option>\n";
1034   foreach ($arr_report as $key => $value) {
1035     echo "    <option value='$key'";
1036     if ($key == $form_report) echo " selected";
1037     echo ">" . $value . "</option>\n";
1038   }
1039 ?>
1040    </select>
1041   </td>
1042   <td valign='top' class='detail'>
1043    &nbsp;
1044   </td>
1045  </tr>
1046  <!-- End experimental code -->
1047
1048  <tr>
1049   <td valign='top' class='dehead' nowrap>
1050    <?php xl('Rows','e'); ?>:
1051   </td>
1052   <td valign='top' class='detail'>
1053    <select name='form_by' title='Left column of report'>
1054 <?php
1055   foreach ($arr_by as $key => $value) {
1056     echo "    <option value='$key'";
1057     if ($key == $form_by) echo " selected";
1058     echo ">" . $value . "</option>\n";
1059   }
1060 ?>
1061    </select>
1062   </td>
1063   <td valign='top' class='dehead' nowrap>
1064    <?php xl('Content','e'); ?>:
1065   </td>
1066   <td valign='top' class='detail'>
1067    <select name='form_content' title='<?php xl('What is to be counted?','e'); ?>'>
1068 <?php
1069   foreach ($arr_content as $key => $value) {
1070     echo "    <option value='$key'";
1071     if ($key == $form_content) echo " selected";
1072     echo ">$value</option>\n";
1073   }
1074 ?>
1075    </select>
1076   </td>
1077   <td valign='top' class='detail'>
1078    &nbsp;
1079   </td>
1080  </tr>
1081  <tr>
1082   <td valign='top' class='dehead' nowrap>
1083    <?php xl('Columns','e'); ?>:
1084   </td>
1085   <td valign='top' class='detail'>
1086    <select name='form_show[]' size='4' multiple
1087     title='<?php xl('Hold down Ctrl to select multiple items','e'); ?>'>
1088 <?php
1089   foreach ($arr_show as $key => $value) {
1090     $title = $value['title'];
1091     if (empty($title) || $key == 'title') $title = $value['description'];
1092     echo "    <option value='$key'";
1093     if (is_array($form_show) && in_array($key, $form_show)) echo " selected";
1094     echo ">$title</option>\n";
1095   }
1096 ?>
1097    </select>
1098   </td>
1099   <td valign='top' class='dehead' nowrap>
1100    <?php xl('Filters','e'); ?>:
1101   </td>
1102   <td colspan='2' class='detail' style='border-style:solid;border-width:1px;border-color:#cccccc'>
1103    <table>
1104     <tr>
1105      <td valign='top' class='detail' nowrap>
1106       <?php xl('Sex','e'); ?>:
1107      </td>
1108      <td class='detail' valign='top'>
1109       <select name='form_sexes' title='<?php xl('To filter by sex','e'); ?>'>
1110 <?php
1111   foreach (array(3 => xl('Men and Women'), 1 => xl('Women Only'), 2 => xl('Men Only')) as $key => $value) {
1112     echo "       <option value='$key'";
1113     if ($key == $form_sexes) echo " selected";
1114     echo ">$value</option>\n";
1115   }
1116 ?>
1117       </select>
1118      </td>
1119     </tr>
1120     <tr>
1121      <td valign='top' class='detail' nowrap>
1122       <?php xl('Facility','e'); ?>:
1123      </td>
1124      <td valign='top' class='detail'>
1125 <?php
1126  // Build a drop-down list of facilities.
1127  //
1128  $query = "SELECT id, name FROM facility ORDER BY name";
1129  $fres = sqlStatement($query);
1130  echo "      <select name='form_facility'>\n";
1131  echo "       <option value=''>-- All Facilities --\n";
1132  while ($frow = sqlFetchArray($fres)) {
1133   $facid = $frow['id'];
1134   echo "       <option value='$facid'";
1135   if ($facid == $_POST['form_facility']) echo " selected";
1136   echo ">" . $frow['name'] . "\n";
1137  }
1138  echo "      </select>\n";
1139 ?>
1140      </td>
1141     </tr>
1142     <tr>
1143      <td colspan='2' class='detail' nowrap>
1144       <?php xl('From','e'); ?>
1145       <input type='text' name='form_from_date' id='form_from_date' size='10' value='<?php echo $from_date ?>'
1146        onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)' title='Start date yyyy-mm-dd'>
1147       <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
1148        id='img_from_date' border='0' alt='[?]' style='cursor:pointer'
1149        title='<?php xl('Click here to choose a date','e'); ?>'>
1150       <?php xl('To','e'); ?>
1151       <input type='text' name='form_to_date' id='form_to_date' size='10' value='<?php echo $to_date ?>'
1152        onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)' title='End date yyyy-mm-dd'>
1153       <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
1154        id='img_to_date' border='0' alt='[?]' style='cursor:pointer'
1155        title='<?php xl('Click here to choose a date','e'); ?>'>
1156      </td>
1157     </tr>
1158    </table>
1159   </td>
1160  </tr>
1161  <tr>
1162   <td valign='top' class='dehead' nowrap>
1163    <?php xl('To','e'); ?>:
1164   </td>
1165   <td colspan='3' valign='top' class='detail' nowrap>
1166 <?php
1167 foreach (array(1 => 'Screen', 2 => 'Printer', 3 => 'Export File') as $key => $value) {
1168   echo "   <input type='radio' name='form_output' value='$key'";
1169   if ($key == $form_output) echo ' checked';
1170   echo " />$value &nbsp;";
1171 }
1172 ?>
1173   </td>
1174   <td align='right' valign='top' class='detail' nowrap>
1175    <input type='submit' name='form_submit' value='<?php xl('Submit','e'); ?>'
1176     title='<?php xl('Click to generate the report','e'); ?>' />
1177   </td>
1178  </tr>
1179  <tr>
1180   <td colspan='5' height="1">
1181   </td>
1182  </tr>
1183 </table>
1184 <?php
1185   } // end not export
1186
1187   if ($_POST['form_submit']) {
1188     $pd_fields = '';
1189     foreach ($arr_show as $askey => $asval) {
1190       if (substr($askey, 0, 1) == '.') continue;
1191       if ($askey == 'regdate' || $askey == 'sex' || $askey == 'DOB' ||
1192         $askey == 'lname' || $askey == 'fname' || $askey == 'mname' ||
1193         $askey == 'contrastart' || $askey == 'referral_source') continue;
1194       $pd_fields .= ', pd.' . $askey;
1195     }
1196
1197     $sexcond = '';
1198     if ($form_sexes == '1') $sexcond = "AND pd.sex NOT LIKE 'Male' ";
1199     else if ($form_sexes == '2') $sexcond = "AND pd.sex LIKE 'Male' ";
1200
1201     // In the case where content is contraceptive product sales, we
1202     // scan product sales at the top level because it is important to
1203     // account for each of them only once.  For each sale we determine
1204     // the one and only IPPF code representing the primary related
1205     // contraceptive service, and that might be either a service in
1206     // the Tally Sheet or the IPPF code attached to the product.
1207     //
1208     if ($form_content == 5) { // sales of contraceptive products
1209       $query = "SELECT " .
1210         "ds.pid, ds.encounter, ds.sale_date, ds.quantity, " .
1211         "d.cyp_factor, d.related_code, " .
1212         "pd.regdate, pd.sex, pd.DOB, pd.lname, pd.fname, pd.mname, " .
1213         "pd.contrastart, pd.referral_source$pd_fields, " .
1214         "fe.date AS encdate, fe.provider_id " .
1215         "FROM drug_sales AS ds " .
1216         "JOIN drugs AS d ON d.drug_id = ds.drug_id " .
1217         "JOIN patient_data AS pd ON pd.pid = ds.pid $sexcond" .
1218         "LEFT JOIN form_encounter AS fe ON fe.pid = ds.pid AND fe.encounter = ds.encounter " .
1219         "WHERE ds.sale_date >= '$from_date' AND " .
1220         "ds.sale_date <= '$to_date' AND " .
1221         "ds.pid > 0 AND ds.quantity != 0";
1222
1223       if ($form_facility) {
1224         $query .= " AND fe.facility_id = '$form_facility'";
1225       }
1226       $query .= " ORDER BY ds.pid, ds.encounter, ds.drug_id";
1227       $res = sqlStatement($query);
1228
1229       while ($row = sqlFetchArray($res)) {
1230         $desired = false;
1231         $prodcode = '';
1232         if ($row['cyp_factor'] > 0) {
1233           $desired = true;
1234         }
1235         $tmp = getRelatedContraceptiveCode($row);
1236         if (!empty($tmp)) {
1237           $desired = true;
1238           $prodcode = $tmp;
1239         }
1240         if (!$desired) continue; // skip if not a contraceptive product
1241
1242         // If there is a visit and it has a contraceptive service use that, else $prodcode.
1243         if (!empty($row['encounter'])) {
1244           $query = "SELECT " .
1245             "b.code_type, b.code, c.related_code " .
1246             "FROM billing AS b " .
1247             "LEFT OUTER JOIN codes AS c ON c.code_type = '12' AND " .
1248             "c.code = b.code AND c.modifier = b.modifier " .
1249             "WHERE b.pid = " . (0 + $row['pid']) . " AND " .
1250             "b.encounter = " . (0 + $row['encounter']) . " AND " .
1251             "b.activity = 1 AND b.code_type = 'MA' " .
1252             "ORDER BY b.code";
1253           $bres = sqlStatement($query);
1254           while ($brow = sqlFetchArray($bres)) {
1255             $tmp = getRelatedContraceptiveCode($brow);
1256             if (!empty($tmp)) {
1257               $prodcode = $tmp;
1258               break;
1259             }
1260           }
1261         }
1262
1263         // At this point $prodcode is the desired IPPF code, or empty if none.
1264         process_ippf_code($row, $prodcode, $row['quantity']);
1265
1266       }
1267     }
1268
1269     // Get referrals and related patient data.
1270     if ($form_content != 5 && ($form_by === '9' || $form_by === '10' || $form_by === '20' || $form_by === '1')) {
1271
1272       $exttest = "t.refer_external = '1'";
1273       $datefld = "t.refer_date";
1274
1275       if ($form_by === '9') {
1276         $exttest = "t.refer_external = '0'";
1277       }
1278       else if ($form_by === '20') {
1279         $datefld = "t.reply_date";
1280       }
1281
1282       $query = "SELECT " .
1283         "t.pid, t.refer_related_code, t.reply_related_code, " .
1284         "pd.regdate, pd.referral_source, " .
1285         "pd.sex, pd.DOB, pd.lname, pd.fname, pd.mname, " .
1286         "pd.contrastart$pd_fields " .
1287         "FROM transactions AS t " .
1288         "JOIN patient_data AS pd ON pd.pid = t.pid $sexcond" .
1289         "WHERE t.title = 'Referral' AND $datefld IS NOT NULL AND " .
1290         "$datefld >= '$from_date' AND $datefld <= '$to_date' AND $exttest " .
1291         "ORDER BY t.pid, t.id";
1292       $res = sqlStatement($query);
1293       while ($row = sqlFetchArray($res)) {
1294         process_referral($row);
1295       }
1296     }
1297     /*****************************************************************
1298     else if ($form_by === '12') {
1299       // We are reporting on a date range, and assume the applicable date is
1300       // the issue start date which is presumably also the date of pre-
1301       // abortion counseling.  The issue end date and the surgery date are
1302       // not of interest here.
1303       $query = "SELECT " .
1304         "l.type, l.begdate, l.pid, " .
1305         "pd.sex, pd.DOB, pd.lname, pd.fname, pd.mname, pd.userlist5, " .
1306         "pd.country_code, pd.status, pd.state, pd.occupation, " .
1307         "lg.client_status, lg.ab_location " .
1308         "FROM lists AS l " .
1309         "JOIN patient_data AS pd ON pd.pid = l.pid $sexcond" .
1310         "LEFT OUTER JOIN lists_ippf_gcac AS lg ON l.type = 'ippf_gcac' AND lg.id = l.id " .
1311         // "LEFT OUTER JOIN lists_ippf_con  AS lc ON l.type = 'contraceptive' AND lc.id = l.id " .
1312         "WHERE l.begdate >= '$from_date' AND l.begdate <= '$to_date' AND " .
1313         "l.activity = 1 AND l.type = 'ippf_gcac' " .
1314         "ORDER BY l.pid, l.id";
1315       $res = sqlStatement($query);
1316       while ($row = sqlFetchArray($res)) {
1317         process_issue($row);
1318       }
1319     }
1320     *****************************************************************/
1321
1322     // else {
1323
1324     /*****************************************************************
1325     if ($form_by === '104' || $form_by === '105') {
1326       $query = "SELECT " .
1327         "d.name, d.related_code, ds.pid, ds.quantity, " .
1328         "pd.regdate, pd.referral_source, " .
1329         "pd.sex, pd.DOB, pd.lname, pd.fname, pd.mname, " .
1330         "pd.contrastart$pd_fields " .
1331         "FROM drug_sales AS ds " .
1332         "JOIN drugs AS d ON d.drug_id = ds.drug_id " .
1333         "JOIN patient_data AS pd ON pd.pid = ds.pid $sexcond" .
1334         "WHERE ds.sale_date IS NOT NULL AND ds.pid != 0 AND " .
1335         "ds.sale_date >= '$from_date' AND ds.sale_date <= '$to_date' " .
1336         "ORDER BY ds.pid, ds.sale_id";
1337       $res = sqlStatement($query);
1338       while ($row = sqlFetchArray($res)) {
1339         $key = "(Unspecified)";
1340         if (!empty($row['related_code'])) {
1341           $relcodes = explode(';', $row['related_code']);
1342           foreach ($relcodes as $codestring) {
1343             if ($codestring === '') continue;
1344             list($codetype, $code) = explode(':', $codestring);
1345             if ($codetype !== 'IPPF') continue;
1346             $key = getContraceptiveMethod($code);
1347             if (!empty($key)) break;
1348             $key = "(No Method)";
1349           }
1350         }
1351         if ($form_by === '104') $key .= " / " . $row['name'];
1352         loadColumnData($key, $row, $row['quantity']);
1353       }
1354     }
1355
1356     if ($form_by !== '9' && $form_by !== '10' && $form_by !== '20' &&
1357       $form_by !== '104' && $form_by !== '105')
1358     *****************************************************************/
1359
1360     if ($form_content != 5 && $form_by !== '9' && $form_by !== '10' && $form_by !== '20')
1361     {
1362       // This gets us all MA codes, with encounter and patient
1363       // info attached and grouped by patient and encounter.
1364       $query = "SELECT " .
1365         "fe.pid, fe.encounter, fe.date AS encdate, pd.regdate, " .
1366         "f.user AS provider, " .
1367         "pd.sex, pd.DOB, pd.lname, pd.fname, pd.mname, " .
1368         "pd.contrastart, pd.referral_source$pd_fields, " .
1369         "b.code_type, b.code, c.related_code, lo.title AS lo_title " .
1370         "FROM form_encounter AS fe " .
1371         "JOIN forms AS f ON f.pid = fe.pid AND f.encounter = fe.encounter AND " .
1372         "f.formdir = 'newpatient' AND f.form_id = fe.id AND f.deleted = 0 " .
1373         "JOIN patient_data AS pd ON pd.pid = fe.pid $sexcond" .
1374         "LEFT OUTER JOIN billing AS b ON " .
1375         "b.pid = fe.pid AND b.encounter = fe.encounter AND b.activity = 1 " .
1376         "AND b.code_type = 'MA' " .
1377         "LEFT OUTER JOIN codes AS c ON b.code_type = 'MA' AND c.code_type = '12' AND " .
1378         "c.code = b.code AND c.modifier = b.modifier " .
1379         "LEFT OUTER JOIN list_options AS lo ON " .
1380         "lo.list_id = 'superbill' AND lo.option_id = c.superbill " .
1381         "WHERE fe.date >= '$from_date 00:00:00' AND " .
1382         "fe.date <= '$to_date 23:59:59' ";
1383
1384       if ($form_facility) {
1385         $query .= "AND fe.facility_id = '$form_facility' ";
1386       }
1387       $query .= "ORDER BY fe.pid, fe.encounter, b.code";
1388       $res = sqlStatement($query);
1389
1390       $prev_encounter = 0;
1391
1392       while ($row = sqlFetchArray($res)) {
1393         if ($row['encounter'] != $prev_encounter) {
1394           $prev_encounter = $row['encounter'];
1395           process_visit($row);
1396         }
1397         if ($row['code_type'] === 'MA') {
1398           process_ma_code($row);
1399           if (!empty($row['related_code'])) {
1400             $relcodes = explode(';', $row['related_code']);
1401             foreach ($relcodes as $codestring) {
1402               if ($codestring === '') continue;
1403               list($codetype, $code) = explode(':', $codestring);
1404               if ($codetype !== 'IPPF') continue;
1405               process_ippf_code($row, $code);
1406             }
1407           }
1408         }
1409       } // end while
1410     } // end if
1411
1412     // Sort everything by key for reporting.
1413     ksort($areport);
1414     foreach ($arr_titles as $atkey => $dummy) ksort($arr_titles[$atkey]);
1415
1416     if ($form_output != 3) {
1417       echo "<table border='0' cellpadding='1' cellspacing='2' width='98%'>\n";
1418     } // end not csv export
1419
1420     // Generate first column headings line, with category titles.
1421     //
1422     genStartRow("bgcolor='#dddddd'");
1423     // If the key is an MA or IPPF code, then add a column for its description.
1424     if (uses_description($form_by)) {
1425       genHeadCell(array('', ''));
1426     } else {
1427       genHeadCell('');
1428     }
1429     // Generate headings for values to be shown.
1430     foreach ($form_show as $value) {
1431       if ($value == '.total') { // Total Services
1432         genHeadCell('');
1433       }
1434       else if ($value == '.age2') { // Age
1435         genHeadCell($arr_show[$value]['title'], false, 2);
1436       }
1437       else if ($value == '.age9') { // Age
1438         genHeadCell($arr_show[$value]['title'], false, 9);
1439       }
1440       else if ($arr_show[$value]['list_id']) {
1441         genHeadCell($arr_show[$value]['title'], false, count($arr_titles[$value]));
1442       }
1443       else if (!empty($arr_titles[$value])) {
1444         genHeadCell($arr_show[$value]['title'], false, count($arr_titles[$value]));
1445       }
1446     }
1447     if ($form_output != 3) {
1448       genHeadCell('');
1449     }
1450     genEndRow();
1451
1452     // Generate second column headings line, with individual titles.
1453     //
1454     genStartRow("bgcolor='#dddddd'");
1455     // If the key is an MA or IPPF code, then add a column for its description.
1456     if (uses_description($form_by)) {
1457       genHeadCell(array($arr_by[$form_by], xl('Description')));
1458     } else {
1459       genHeadCell($arr_by[$form_by]);
1460     }
1461     // Generate headings for values to be shown.
1462     foreach ($form_show as $value) {
1463       if ($value == '.total') { // Total Services
1464         genHeadCell(xl('Total'));
1465       }
1466       else if ($value == '.age2') { // Age
1467         genHeadCell(xl('0-24' ), true);
1468         genHeadCell(xl('25+'  ), true);
1469       }
1470       else if ($value == '.age9') { // Age
1471         genHeadCell(xl('0-10' ), true);
1472         genHeadCell(xl('11-14'), true);
1473         genHeadCell(xl('15-19'), true);
1474         genHeadCell(xl('20-24'), true);
1475         genHeadCell(xl('25-29'), true);
1476         genHeadCell(xl('30-34'), true);
1477         genHeadCell(xl('35-39'), true);
1478         genHeadCell(xl('40-44'), true);
1479         genHeadCell(xl('45+'  ), true);
1480       }
1481       else if ($arr_show[$value]['list_id']) {
1482         foreach ($arr_titles[$value] as $key => $dummy) {
1483           genHeadCell(getListTitle($arr_show[$value]['list_id'],$key), true);
1484         }
1485       }
1486       else if (!empty($arr_titles[$value])) {
1487         foreach ($arr_titles[$value] as $key => $dummy) {
1488           genHeadCell($key, true);
1489         }
1490       }
1491     }
1492     if ($form_output != 3) {
1493       genHeadCell(xl('Total'), true);
1494     }
1495     genEndRow();
1496
1497     $encount = 0;
1498
1499     foreach ($areport as $key => $varr) {
1500       $bgcolor = (++$encount & 1) ? "#ddddff" : "#ffdddd";
1501
1502       $dispkey = $key;
1503
1504       // If the key is an MA or IPPF code, then add a column for its description.
1505       if (uses_description($form_by)) {
1506         $dispkey = array($key, '');
1507         $type = $form_by === '102' ? 12 : 11; // MA or IPPF
1508         $crow = sqlQuery("SELECT code_text FROM codes WHERE " .
1509           "code_type = '$type' AND code = '$key' ORDER BY id LIMIT 1");
1510         if (!empty($crow['code_text'])) $dispkey[1] = $crow['code_text'];
1511       }
1512
1513       genStartRow("bgcolor='$bgcolor'");
1514
1515       genAnyCell($dispkey, false, 'detail');
1516
1517       // This is the column index for accumulating column totals.
1518       $cnum = 0;
1519       $totalsvcs = $areport[$key]['.wom'] + $areport[$key]['.men'];
1520
1521       // Generate data for this row.
1522       foreach ($form_show as $value) {
1523         // if ($value == '1') { // Total Services
1524         if ($value == '.total') { // Total Services
1525           genNumCell($totalsvcs, $cnum++);
1526         }
1527         else if ($value == '.age2') { // Age
1528           for ($i = 0; $i < 2; ++$i) {
1529             genNumCell($areport[$key]['.age2'][$i], $cnum++);
1530           }
1531         }
1532         else if ($value == '.age9') { // Age
1533           for ($i = 0; $i < 9; ++$i) {
1534             genNumCell($areport[$key]['.age9'][$i], $cnum++);
1535           }
1536         }
1537         else if (!empty($arr_titles[$value])) {
1538           foreach ($arr_titles[$value] as $title => $dummy) {
1539             genNumCell($areport[$key][$value][$title], $cnum++);
1540           }
1541         }
1542       }
1543
1544       // Write the Total column data.
1545       if ($form_output != 3) {
1546         $atotals[$cnum] += $totalsvcs;
1547         genAnyCell($totalsvcs, true, 'dehead');
1548       }
1549
1550       genEndRow();
1551     } // end foreach
1552
1553     if ($form_output != 3) {
1554       // Generate the line of totals.
1555       genStartRow("bgcolor='#dddddd'");
1556
1557       // If the key is an MA or IPPF code, then add a column for its description.
1558       if (uses_description($form_by)) {
1559         genHeadCell(array(xl('Totals'), ''));
1560       } else {
1561         genHeadCell(xl('Totals'));
1562       }
1563
1564       for ($cnum = 0; $cnum < count($atotals); ++$cnum) {
1565         genHeadCell($atotals[$cnum], true);
1566       }
1567       genEndRow();
1568       // End of table.
1569       echo "</table>\n";
1570     }
1571
1572   } // end of if refresh or export
1573
1574   if ($form_output != 3) {
1575 ?>
1576 </form>
1577 </center>
1578
1579 <script language='JavaScript'>
1580  selreport();
1581  Calendar.setup({inputField:"form_from_date", ifFormat:"%Y-%m-%d", button:"img_from_date"});
1582  Calendar.setup({inputField:"form_to_date", ifFormat:"%Y-%m-%d", button:"img_to_date"});
1583 <?php if ($form_output == 2) { ?>
1584  window.print();
1585 <?php } ?>
1586 </script>
1587
1588 </body>
1589 </html>
1590 <?php
1591   } // end not export
1592 ?>