inc/class.eq.inc.php

   1 <?php
   2   /**************************************************************************\
   3   * phpGroupWare - eq                                                        *
   4   * http://www.phpgroupware.org                                              *
   5   * -----------------------------------------------                          *
   6   *  This program is free software; you can redistribute it and/or modify it *
   7   *  under the terms of the GNU General Public License as published by the   *
   8   *  Free Software Foundation; either version 2 of the License, or (at your  *
   9   *  option) any later version.                                              *
  10   \**************************************************************************/
  11         /* $Id: class.eq.inc.php,v 1.1.1.1 2001/05/20 07:40:32 seek3r Exp $ */
  12
  13 class eq
  14 {
  15   var $db;
  16   var $db2;
  17   var $t;
  18   var $nextmatchs;
  19   var $grants;
  20   var $jscal;
  21   var $cal_options;
  22   var $default_ht_num_months;
  23   var $default_ppi_num_months;
  24   var $default_ppi_num_years;
  25   var $default_att_num_months;
  26   var $current_year;
  27   var $current_month;
  28   var $upload_target_path;
  29   var $script_path;
  30
  31   var $public_functions = array
  32     (
  33      'ht_view'    => True,
  34      'ht_update'  => True,
  35      'act_list'   => True,
  36      'act_view'   => True,
  37      'act_update' => True,
  38      'par_view'   => True,
  39      'ppi_view'   => True,
  40      'ppi_update' => True,
  41      'ppi_sched'  => True,
  42      'vis_sched'  => True,
  43      'vis_view'   => True,
  44      'vis_update' => True,
  45      'att_view'   => True,
  46      'att_update' => True,
  47      'dir_view'   => True,
  48      'org_view'   => True,
  49      'schedule'   => True,
  50      'admin'      => True
  51      );
  52
  53   function eq()
  54     {
  55       $this->default_ht_num_months = 3;
  56       $this->default_ppi_num_months = 3;
  57       $this->default_ppi_num_years = 0;
  58       $this->default_att_num_months = 3;
  59       $this->upload_target_path = "/home/users/eqpres/eq_data/";
  60       $this->script_path = "/usr/share/phpgroupware/eq/";
  61
  62       $this->db         = $GLOBALS['phpgw']->db;
  63       $this->db2        = $this->db;
  64       $this->nextmatchs = CreateObject('phpgwapi.nextmatchs');
  65       $this->t          = $GLOBALS['phpgw']->template;
  66       $this->account    = $GLOBALS['phpgw_info']['user']['account_id'];
  67       $this->grants     = $GLOBALS['phpgw']->acl->get_grants('eq');
  68       $this->grants[$this->account] = PHPGW_ACL_READ + PHPGW_ACL_ADD + PHPGW_ACL_EDIT + PHPGW_ACL_DELETE;
  69
  70       $this->jscal = CreateObject('phpgwapi.jscalendar');   // before phpgw_header() !!!
  71       $this->cal_options = 'daFormat    : "%Y-%m-%d",
  72                                 ifFormat    : "%Y-%m-%d",
  73                                 mondayFirst : false,
  74                                 weekNumbers : false';
  75
  76       $GLOBALS['phpgw_info']['flags']['app_header'] = 'Elders Quorum Tools';
  77       $GLOBALS['phpgw']->common->phpgw_header();
  78
  79       $this->current_day = `date '+%d'`;
  80       $this->current_day = $this->current_day-0; // Make it numeric
  81       $this->current_month = `date '+%m'`;
  82       $this->current_month = $this->current_month-0; // Make it numeric
  83       $this->current_year = `date '+%Y'`;
  84       $this->current_year = $this->current_year-0; // Make it numeric
  85
  86       echo parse_navbar();
  87       $this->display_app_header();
  88     }
  89
  90   function save_sessiondata()
  91     {
  92
  93     }
  94
  95   function display_app_header()
  96     {
  97       $this->t->set_file(array('eq_header' => 'header.tpl'));
  98
  99       if (isset($phpgw_info['user']['preferences']['eq']['eq_font']))
 100         {
 101           $font = $phpgw_info['user']['preferences']['eq']['eq_font'];
 102         }
 103       else
 104         {
 105           $font = 'Arial';
 106         }
 107
 108       $this->t->set_var('bg_color',$phpgw_info['theme']['th_bg']);
 109       $this->t->set_var('font',$font);
 110       $link_data['menuaction'] = 'eq.eq.ht_view';
 111       $this->t->set_var('link_hometeaching',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 112       $this->t->set_var('lang_hometeaching','HomeTeaching');
 113       $link_data['menuaction'] = 'eq.eq.act_list';
 114       $this->t->set_var('link_activity',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 115       $this->t->set_var('lang_activity','Activities');
 116       $link_data['menuaction'] = 'eq.eq.par_view';
 117       $this->t->set_var('link_participation',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 118       $this->t->set_var('lang_participation','Participation');
 119       $link_data['menuaction'] = 'eq.eq.ppi_view';
 120       $this->t->set_var('link_ppi',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 121       $this->t->set_var('lang_ppi','PPIs');
 122       $link_data['menuaction'] = 'eq.eq.vis_view';
 123       $this->t->set_var('link_visit',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 124       $this->t->set_var('lang_visit','Visits');
 125       $link_data['menuaction'] = 'eq.eq.att_view';
 126       $this->t->set_var('link_attendance',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 127       $this->t->set_var('lang_attendance','Attendance');
 128       $link_data['menuaction'] = 'eq.eq.dir_view';
 129       $this->t->set_var('link_dir',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 130       $this->t->set_var('lang_dir','Directory');
 131       $link_data['menuaction'] = 'eq.eq.org_view';
 132       $this->t->set_var('link_org',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 133       $this->t->set_var('lang_org','Callings');
 134       $link_data['menuaction'] = 'eq.eq.admin';
 135       $this->t->set_var('link_admin',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 136       $this->t->set_var('lang_admin','Admin');
 137       $link_data['menuaction'] = 'eq.eq.schedule';
 138       $this->t->set_var('link_schedule',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 139       $this->t->set_var('lang_schedule','Scheduling');
 140
 141       $this->t->pparse('out','eq_header');
 142     }
 143
 144   function ht_view()
 145     {
 146       $this->t->set_file(array('ht_view_t' => 'ht_view.tpl'));
 147       $this->t->set_block('ht_view_t','district_list','list');
 148
 149       $this->t->set_var('linkurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ht_view'));
 150       $num_months = get_var('num_months',array('GET','POST'));
 151       if($num_months == '') { $num_months = $this->default_ht_num_months; }
 152       $this->t->set_var('num_months',$num_months);
 153       $this->t->set_var('lang_filter','Filter');
 154       if($num_months == 1) { $this->t->set_var('lang_num_months','Month of History'); }
 155       else {  $this->t->set_var('lang_num_months','Months of History'); }
 156
 157       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ht_view'));
 158       $this->t->set_var('title','Hometeaching');
 159
 160       $sql = "SELECT * FROM eq_district where valid=1 ORDER BY district ASC";
 161       $this->db->query($sql,__LINE__,__FILE__);
 162       $i=0;
 163       while ($this->db->next_record())
 164         {
 165           $districts[$i]['district'] = $this->db->f('district');
 166           $districts[$i]['name'] = $this->db->f('name');
 167           $districts[$i]['supervisor'] = $this->db->f('supervisor');
 168           $i++;
 169         }
 170
 171       $sql = "SELECT * FROM eq_elder where valid=1 ORDER BY elder ASC";
 172       $this->db->query($sql,__LINE__,__FILE__);
 173       $i=0;
 174       while ($this->db->next_record())
 175         {
 176           $elder_id[$i] = $this->db->f('elder');
 177           $elder_name[$i] = $this->db->f('name');
 178           $elder_phone[$elder_id[$i]] = $this->db->f('phone');
 179           $i++;
 180         }
 181       array_multisort($elder_name, $elder_id);
 182
 183       // Make an array mapping elder_ids to elder_names
 184       for($i=0; $i < count($elder_id); $i++) {
 185           $id = $elder_id[$i];
 186           $elders[$id] = $elder_name[$i];
 187       }
 188
 189       $sql = "SELECT * FROM eq_aaronic where valid=1 ORDER BY aaronic ASC";
 190       $this->db->query($sql,__LINE__,__FILE__);
 191       while ($this->db->next_record())
 192         {
 193           $aaronic_id = $this->db->f('aaronic');
 194           $aaronic[$aaronic_id]['name'] = $this->db->f('name');
 195           $aaronic[$aaronic_id]['phone'] = $this->db->f('phone');
 196         }
 197
 198       $total_families = 0;
 199       $this->nextmatchs->template_alternate_row_color(&$this->t);
 200       for ($i=0; $i < count($districts); $i++) {
 201         $this->t->set_var('district_number',$districts[$i]['district']);
 202         $this->t->set_var('district_name',$districts[$i]['name']);
 203         $supervisor = $districts[$i]['supervisor'];
 204         $unique_companionships='';
 205
 206         // Select all the unique companionship numbers for this district
 207         $sql = "SELECT distinct companionship FROM eq_companionship where valid=1 and district=". $districts[$i]['district'];
 208         $this->db->query($sql,__LINE__,__FILE__);
 209         $j=0;
 210         while ($this->db->next_record())
 211           {
 212             $unique_companionships[$j]['companionship'] = $this->db->f('companionship');
 213             $j++;
 214           }
 215
 216         $comp_width=450; $visit_width=25; $table_width=$comp_width + $num_months*$visit_width;
 217         $table_data=""; $num_companionships = 0; $num_families = 0;
 218         for($m=$num_months; $m >= 0; $m--) { $visits[$m] = 0; }
 219         for ($j=0; $j < count($unique_companionships); $j++) {
 220           $companion_table_entry = "";
 221           // Select all the companions in each companionship
 222           $sql = "SELECT * FROM eq_companionship where valid=1 and ".
 223                  "companionship=". $unique_companionships[$j]['companionship'];
 224           $this->db->query($sql,__LINE__,__FILE__);
 225
 226           while ($this->db->next_record())
 227             {
 228               // Get this companions information
 229               if($companion_table_entry != "") { $companion_table_entry .= "<td>&nbsp;/&nbsp;</td>"; }
 230               $companionship = $this->db->f('companionship');
 231               $elder_id = $this->db->f('elder');
 232               $aaronic_id = $this->db->f('aaronic');
 233               if($elder_id) {
 234                 $name = $elders[$elder_id];
 235                 $phone = $elder_phone[$elder_id];
 236               }
 237               else if($aaronic_id) {
 238                 $name = $aaronic[$aaronic_id]['name'];
 239                 $phone = $aaronic[$aaronic_id]['phone'];
 240               }
 241               $companion_table_entry .= "<td title=\"$phone\"><b>$name</b></td>";
 242             }
 243           $table_data.= "<tr bgcolor=#d3dce3><td colspan=20><table><tr>$companion_table_entry</tr></table><hr></td></tr>";
 244
 245           // Get the names of the families assigned this home teaching companionship
 246           $sql = "SELECT * from eq_family where valid=1 AND companionship=".$unique_companionships[$j]['companionship'];
 247           $sql = $sql . " ORDER BY name ASC";
 248           $this->db->query($sql,__LINE__,__FILE__);
 249           $k=0;
 250           while ($this->db->next_record())
 251             {
 252               $num_families++; $total_families++;
 253               $family_name = $this->db->f('name');
 254               $family_id = $this->db->f('family');
 255               $this->nextmatchs->template_alternate_row_color(&$this->t);
 256               $table_data.="<tr bgcolor=". $this->t->get_var('tr_color') ."><td>$family_name Family</td>";
 257               // Find out how many times Visits were performed by this companionship
 258               // in the past $num_months for this Family
 259               $header_row="<th width=$comp_width><font size=-2>Families</th>";
 260               for($m=$num_months; $m >= 0; $m--) {
 261                 $month = $this->current_month - $m;
 262                 $year = $this->current_year;
 263                 if($month <= 0) { $remainder = $month; $month = 12 + $remainder; $year=$year-1; }
 264                 if($month < 10) { $month = "0"."$month"; }
 265                 $month_start = "$year"."-"."$month"."-"."01";
 266                 $month_end = "$year"."-"."$month"."-"."31";
 267                 $month = "$month"."/"."$year";
 268                 //print "m: $m month: $month year: $year month_start: $month_start month_end: $month_end<br>";
 269                 // Add this to the query to filter on only visits made by this companionship:
 270                 // " AND companionship=" . $unique_companionships[$j]['companionship'].
 271                 $sql = "SELECT * FROM eq_visit WHERE date >= '$month_start' AND date <= '$month_end' ".
 272                        " AND companionship!=0".
 273                        " AND family=". $family_id;
 274                 $this->db2->query($sql,__LINE__,__FILE__);
 275                 $link_data['menuaction'] = 'eq.eq.ht_update';
 276                 $link_data['date'] = $month_start;
 277                 $link_data['month_start'] = $month_start;
 278                 $link_data['month_end'] = $month_end;
 279                 $link_data['month'] = $month;
 280                 $link_data['district'] = $districts[$i]['district'];
 281                 $link_data['district_name'] = $districts[$i]['name'];
 282                 $link_data['action'] = 'view';
 283                 $link = $GLOBALS['phpgw']->link('/eq/index.php',$link_data);
 284                 $header_row .= "<th width=$visit_width><font size=-2><a href=$link>$month</a></th>";
 285                 if(!$total_visits[$m]) { $total_visits[$m] = 0; }
 286                 if($this->db2->next_record()) {
 287                   if($this->db2->f('visited') == 'y') {
 288                     $visits[$m]++; $total_visits[$m]++;
 289                     $table_data .= '<td align=center><a href="'.$link.'"><img src="checkmark.gif"></a></td>';
 290                   }
 291                   else if($this->db2->f('visited') == 'n') {
 292                     $table_data .= '<td align=center><a href="'.$link.'"><img src="x.gif"></a></td>';
 293                   }
 294                   else {
 295                     $visits[$m]++; $total_visits[$m]++;
 296                     $table_data .= "<td>&nbsp;</td>";
 297                   }
 298                 }
 299                 else {
 300                   $visits[$m]++; $total_visits[$m]++;
 301                   $table_data .= "<td>&nbsp;</td>";
 302                 }
 303               }
 304               $table_data .= "</tr>";
 305               $k++;
 306             }
 307           $table_data .= "<tr><td colspan=20></td></tr>";
 308         }
 309         $table_data .= "<tr><td colspan=20><hr></td></tr>";
 310         $stat_data = "<tr><td><b><font size=-2>$num_families Families<br>Visit Totals:</font></b></td>";
 311
 312         for($m=$num_months; $m >=0; $m--) {
 313           $percent = ceil(($visits[$m] / $num_families)*100);
 314           $stat_data .= "<td align=center><font size=-2><b>$visits[$m]<br>$percent%</font></b></td>";
 315         }
 316         $stat_data .= "</tr>";
 317
 318         $this->t->set_var('table_width',$table_width);
 319         $this->t->set_var('header_row',$header_row);
 320         $this->t->set_var('table_data',$table_data);
 321         $this->t->set_var('stat_data',$stat_data);
 322         $this->t->fp('list','district_list',True);
 323       }
 324
 325       $totals = "<tr><td><b><font size=-2>$total_families Total Families<br>Visit Totals:</font></b></td>";
 326       for($m=$num_months; $m >=0; $m--) {
 327         $percent = ceil(($total_visits[$m] / $total_families)*100);
 328         $totals .= "<td align=center><font size=-2><b>$total_visits[$m]<br>$percent%</font></b></td>";
 329       }
 330       $totals .= "</tr>";
 331
 332       $this->t->set_var('totals',$totals);
 333
 334       $this->t->pfp('out','ht_view_t');
 335       $this->save_sessiondata();
 336     }
 337
 338
 339   function ht_update()
 340     {
 341       $this->t->set_file(array('ht_update_t' => 'ht_update.tpl'));
 342       $this->t->set_block('ht_update_t','district_list','list');
 343       $this->t->set_block('ht_update_t','save','savehandle');
 344
 345       $district = get_var('district',array('GET','POST'));
 346       $district_name = get_var('district_name',array('GET','POST'));
 347       $date = get_var('date',array('GET','POST'));
 348       $month = get_var('month',array('GET','POST'));
 349       $month_start = get_var('month_start',array('GET','POST'));
 350       $month_end = get_var('month_end',array('GET','POST'));
 351       $action = get_var('action',array('GET','POST'));
 352
 353       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ht_view'));
 354       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ht_update&action=save'));
 355       $this->t->set_var('lang_done','Cancel');
 356       $this->t->set_var('district_name',$district_name);
 357       $this->t->set_var('district_number',$district);
 358       $this->t->set_var('title','Hometeaching Update ' . $month);
 359       $this->t->set_var('date',$date);
 360
 361       if($action == 'save')
 362         {
 363           // Get a list of all the companionships in this district
 364           $sql = "SELECT distinct companionship FROM eq_companionship where valid=1 and district=". $district;
 365           $this->db->query($sql,__LINE__,__FILE__);
 366           $j=0;
 367           while ($this->db->next_record())
 368             {
 369               $unique_companionships[$j]['companionship'] = $this->db->f('companionship');
 370               $j++;
 371             }
 372           for ($j=0; $j < count($unique_companionships); $j++)
 373             {
 374               // FIXME: We won't be able to go back and edit history on families that have been
 375               // reassigned to a different companionship. The following delete command will not delete
 376               // the history of visits under an older companionship, only the ones for the existing
 377               // companionship. This will lead to duplicate visits being entered for an older
 378               // month for the same family, making it impossible to change the past history once
 379               // a family is reassigned. However, you will be able to view the history just fine.
 380
 381               //$comp=$unique_companionships[$j]['companionship'];
 382               //print "deleting from eq_visit where companionship=$comp and date=$date and district=$district<br>";
 383               // Delete all the visits that have taken place for all families for this month
 384               $this->db->query("DELETE from eq_visit where companionship=" . $unique_companionships[$j]['companionship'] .
 385                                " AND " . "date='" . $date . "'",__LINE__,__FILE__);
 386             }
 387
 388           // Now, add the visits that are checked for this month
 389           $new_data = get_var('family_visited',array('POST'));
 390           foreach ($new_data as $family)
 391            {
 392              foreach ($family as $data)
 393                {
 394                  //print "family_visited: $data <br>";
 395                  $data_array = explode("/",$data);
 396                  $family_id = $data_array[0];
 397                  $companionship = $data_array[1];
 398                  $date = $data_array[2];
 399                  $visited = $data_array[3];
 400                  if($visited == "") { $visited = $data_array[4]; }
 401                  //print "family_id: $family_id companionship: $companionship date: $date visited: $visited<br>";
 402                  $this->db->query("INSERT INTO eq_visit (family,companionship,date,notes,visited) "
 403                                   . "VALUES (" . $family_id .",". $companionship .",'". $date ."','','". $visited ."')",__LINE__,__FILE__);
 404                }
 405            }
 406           $this->ht_view();
 407           return false;
 408         }
 409
 410       $sql = "SELECT * FROM eq_elder where valid=1 ORDER BY elder ASC";
 411       $this->db->query($sql,__LINE__,__FILE__);
 412       $i=0;
 413       while ($this->db->next_record())
 414         {
 415           $elder_id[$i] = $this->db->f('elder');
 416           $elder_name[$i] = $this->db->f('name');
 417           $elder_phone[$elder_id[$i]] = $this->db->f('phone');
 418           $i++;
 419         }
 420       array_multisort($elder_name, $elder_id);
 421
 422       // Make an array mapping elder_ids to elder_names
 423       for($i=0; $i < count($elder_id); $i++) {
 424           $id = $elder_id[$i];
 425           $elders[$id] = $elder_name[$i];
 426       }
 427
 428       $sql = "SELECT * FROM eq_aaronic where valid=1 ORDER BY aaronic ASC";
 429       $this->db->query($sql,__LINE__,__FILE__);
 430       while ($this->db->next_record())
 431         {
 432           $aaronic_id = $this->db->f('aaronic');
 433           $aaronic[$aaronic_id]['name'] = $this->db->f('name');
 434           $aaronic[$aaronic_id]['phone'] = $this->db->f('phone');
 435         }
 436
 437       // Select all the unique companionship numbers for this district
 438       $sql = "SELECT distinct companionship FROM eq_companionship where valid=1 and district=". $district;
 439       $this->db->query($sql,__LINE__,__FILE__);
 440       $j=0; $unique_companionships='';
 441       while ($this->db->next_record())
 442         {
 443           $unique_companionships[$j]['companionship'] = $this->db->f('companionship');
 444           $j++;
 445         }
 446
 447       $comp_width=300; $visit_width=25; $table_width=$comp_width + $visit_width;
 448       $table_data=""; $num_companionships = 0; $num_families = 0; $visits=0;
 449       for ($j=0; $j < count($unique_companionships); $j++) {
 450         $companion_table_entry = "";
 451         // Select all the companions in each companionship
 452         $sql = "SELECT * FROM eq_companionship where valid=1 and ".
 453                "companionship=". $unique_companionships[$j]['companionship'];
 454         $this->db->query($sql,__LINE__,__FILE__);
 455
 456         while ($this->db->next_record())
 457           {
 458             // Get this companions information
 459             if($companion_table_entry != "") { $companion_table_entry .= "<td>&nbsp;/&nbsp;</td>"; }
 460             $companionship = $this->db->f('companionship');
 461             $elder_id = $this->db->f('elder');
 462             $aaronic_id = $this->db->f('aaronic');
 463             if($elder_id) {
 464               $name = $elders[$elder_id];
 465               $phone = $elder_phone[$elder_id];
 466             }
 467             else if($aaronic_id) {
 468               $name = $aaronic[$aaronic_id]['name'];
 469               $phone = $aaronic[$aaronic_id]['phone'];
 470             }
 471             $companion_table_entry .= "<td title=\"$phone\"><b>$name</b></td>";
 472           }
 473         $table_data.= "<tr bgcolor=#d3dce3><td colspan=20><table><tr>$companion_table_entry</tr></table><hr></td></tr>";
 474
 475         // Get the names of the families assigned this home teaching companionship
 476         $sql = "SELECT * from eq_family where valid=1 AND companionship=".$unique_companionships[$j]['companionship'];
 477         $sql = $sql . " ORDER BY name ASC";
 478         $this->db->query($sql,__LINE__,__FILE__);
 479         while ($this->db->next_record())
 480           {
 481             $num_families++; $total_families++;
 482             $family_name = $this->db->f('name');
 483             $family_id = $this->db->f('family');
 484             $this->nextmatchs->template_alternate_row_color(&$this->t);
 485             $table_data.="<tr bgcolor=". $this->t->get_var('tr_color') ."><td>$family_name Family</td>";
 486
 487             $header_row="<th width=$comp_width><font size=-2>Families</th>";
 488             $sql = "SELECT * FROM eq_visit WHERE date >= '$month_start' AND date <= '$month_end' ".
 489                    " AND companionship!=0".
 490                    " AND family=". $family_id;
 491             $this->db2->query($sql,__LINE__,__FILE__);
 492             $value = $family_id . "/" . $unique_companionships[$j]['companionship'] . "/" . $date;
 493             $header_row .= "<th width=$visit_width><font size=-2><a href=$link>$month</a></th>";
 494             if(!$total_visits) { $total_visits = 0; }
 495             if($this->db2->next_record()) {
 496               if($this->db2->f('visited') == 'y') {
 497                 $visits++; $total_visits++;
 498                 $table_data .= '<td width=100 align=center>';
 499                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/y" checked>Y';
 500                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/n">N';
 501                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/"> ';
 502                 $table_data .= '</td>';
 503               } else if($this->db2->f('visited') == 'n') {
 504                 $table_data .= '<td width=100 align=center>';
 505                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/y">Y';
 506                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/n" checked>N';
 507                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/">';
 508                 $table_data .= '</td>';
 509               } else {
 510                 $table_data .= '<td width=100 align=center>';
 511                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/y">Y';
 512                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/n">N';
 513                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/" checked> ';
 514                 $table_data .= '</td>';
 515               }
 516             }
 517             else {
 518               $value .= "/";
 519               $table_data .= '<td width=100 align=center>';
 520               $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/y">Y';
 521               $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/n">N';
 522               $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/" checked> ';
 523               $table_data .= '</td>';
 524             }
 525           }
 526         $table_data .= "</tr>";
 527         $table_data .= "<tr><td colspan=20></td></tr>";
 528       }
 529       $table_data .= "<tr><td colspan=20><hr></td></tr>";
 530       $stat_data = "<tr><td><b><font size=-2>$num_families Families<br>Visit Totals:</font></b></td>";
 531
 532       $percent = ceil(($visits / $num_families)*100);
 533       $stat_data .= "<td align=center><font size=-2><b>$visits<br>$percent%</font></b></td>";
 534       $stat_data .= "</tr>";
 535
 536       $this->t->set_var('table_width',$table_width);
 537       $this->t->set_var('header_row',$header_row);
 538       $this->t->set_var('table_data',$table_data);
 539       $this->t->set_var('stat_data',$stat_data);
 540       $this->t->fp('list','district_list',True);
 541
 542       $this->t->set_var('lang_reset','Clear Form');
 543       $this->t->set_var('lang_save','Save Changes');
 544       $this->t->set_var('savehandle','');
 545
 546       $this->t->pfp('out','ht_update_t');
 547       $this->t->pfp('addhandle','save');
 548
 549       $this->save_sessiondata();
 550     }
 551
 552   function act_list()
 553     {
 554       $this->t->set_file(array('act_list_t' => 'act_list.tpl'));
 555       $this->t->set_block('act_list_t','act_list','list');
 556
 557       $this->t->set_var('lang_name','Activity Name');
 558       $this->t->set_var('lang_date','Date');
 559
 560       $sql = "SELECT * FROM eq_activity ORDER BY date DESC";
 561       $this->db->query($sql,__LINE__,__FILE__);
 562       $total_records = $this->db->num_rows();
 563
 564       $i = 0;
 565       while ($this->db->next_record())
 566         {
 567           $activity_list[$i]['name'] = $this->db->f('name');
 568           $activity_list[$i]['date']  = $this->db->f('date');
 569           $activity_list[$i]['activity']  = $this->db->f('activity');
 570           $i++;
 571         }
 572
 573       for ($i=0; $i < count($activity_list); $i++)
 574         {
 575           $this->nextmatchs->template_alternate_row_color(&$this->t);
 576           $this->t->set_var('name',$activity_list[$i]['name']);
 577           $this->t->set_var('date',$activity_list[$i]['date']);
 578
 579           $link_data['menuaction'] = 'eq.eq.act_view';
 580           $link_data['activity'] = $activity_list[$i]['activity'];
 581           $link_data['action'] = 'view';
 582           $this->t->set_var('view',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 583           $this->t->set_var('lang_view','View');
 584
 585           $link_data['menuaction'] = 'eq.eq.act_update';
 586           $link_data['activity'] = $activity_list[$i]['activity'];
 587           $link_data['action'] = 'edit';
 588           $this->t->set_var('edit',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 589           $this->t->set_var('lang_edit','Edit');
 590
 591           $link_data['menuaction'] = 'eq.eq.act_update';
 592           $link_data['activity'] = '0';
 593           $link_data['action'] = 'add';
 594           $this->t->set_var('add','<form method="POST" action="' . $GLOBALS['phpgw']->link('/eq/index.php',$link_data)
 595                            . '"><input type="submit" name="Add" value="' . 'Add Activity' .'"></font></form>');
 596
 597           $this->t->fp('list','act_list',True);
 598         }
 599
 600       $this->t->pfp('out','act_list_t');
 601       $this->save_sessiondata();
 602     }
 603
 604   function act_view()
 605     {
 606       $this->t->set_file(array('act_view_t' => 'act_view.tpl'));
 607       $this->t->set_block('act_view_t','part_list','list');
 608
 609       $sql = "SELECT * FROM eq_activity WHERE activity=" . intval(get_var('activity',array('GET','POST')));
 610       $this->db->query($sql,__LINE__,__FILE__);
 611       $this->db->next_record();
 612       $this->t->set_var('name', $this->db->f('name'));
 613       $this->t->set_var('date', $this->db->f('date'));
 614       $this->t->set_var('notes', $this->db->f('notes'));
 615
 616       $this->t->set_var('lang_name','Activity Name');
 617       $this->t->set_var('lang_date','Date');
 618       $this->t->set_var('lang_notes','Notes');
 619       $this->t->set_var('lang_done','Done');
 620       $this->t->set_var('lang_action','View');
 621
 622       $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
 623       $this->t->set_var('tr_color',$tr_color);
 624
 625       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.act_list'));
 626
 627       $link_data['menuaction'] = 'eq.eq.act_update';
 628       $link_data['activity'] = get_var('activity',array('GET','POST'));
 629       $link_data['action'] = 'edit';
 630       $this->t->set_var('edit',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 631       $this->t->set_var('lang_edit','Edit');
 632       $this->t->set_var('cal_date',$this->db->f('date'));
 633
 634       // Now find out which elders participated in this activity
 635       $sql = "SELECT * FROM eq_participation WHERE activity=" . intval(get_var('activity',array('GET','POST')));
 636       $this->db->query($sql,__LINE__,__FILE__);
 637       $total_records = $this->db->num_rows();
 638
 639       $i = 0;
 640       while ($this->db->next_record())
 641         {
 642           $part_list[$i]['elder']  = $this->db->f('elder');
 643           $i++;
 644         }
 645
 646       for ($i=0; $i < count($part_list); $i++)
 647         {
 648           $sql = "SELECT * FROM eq_elder WHERE elder=" . $part_list[$i]['elder'];
 649           $this->db->query($sql,__LINE__,__FILE__);
 650           $this->db->next_record();
 651           $names[$i] = $this->db->f('name');
 652         } sort($names);
 653
 654       for ($i=0; $i < count($names); $i++)
 655         {
 656           //$this->nextmatchs->template_alternate_row_color(&$this->t);
 657           $this->t->set_var('elder_name',$names[$i]);
 658           if(($i+1) % 3 == 0) { $this->t->set_var('table_sep',"</td></tr><tr>"); }
 659           else { $this->t->set_var('table_sep',"</td>"); }
 660           if(($i) % 3 == 0) { $this->nextmatchs->template_alternate_row_color(&$this->t); }
 661           $this->t->fp('list','part_list',True);
 662         }
 663
 664       $this->t->pfp('out','act_view_t');
 665       $this->save_sessiondata();
 666     }
 667
 668   function act_update()
 669     {
 670       $this->t->set_file(array('form' => 'act_update.tpl'));
 671       $this->t->set_block('form','elder_list','list');
 672       $this->t->set_block('form','add','addhandle');
 673       $this->t->set_block('form','edit','edithandle');
 674       $this->t->set_var('lang_done','Done');
 675
 676       $action = get_var('action',array('GET','POST'));
 677       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.act_list'));
 678       $activity['activity'] = intval(get_var('activity',array('GET','POST')));
 679
 680       if($action == 'save')
 681         {
 682           $activity['name'] = $this->db->db_addslashes(get_var('name',array('POST')));
 683           $activity['date'] = $this->db->db_addslashes(get_var('date',array('POST')));
 684           $activity['notes']= $this->db->db_addslashes(get_var('notes',array('POST')));
 685           $this->db->query("UPDATE eq_activity set " .
 686                            "   name='" . $activity['name'] .
 687                            "', date='" . $activity['date'] . "'" .
 688                            ", notes='" . $activity['notes'] . "'" .
 689                            " WHERE activity=" . $activity['activity'],__LINE__,__FILE__);
 690
 691           // Delete all the elders who have particiapted in this activity
 692           $this->db->query("DELETE from eq_participation where activity=".$activity['activity'],__LINE__,__FILE__);
 693
 694           // Re-add the elders who are checked as having participated in this activity
 695           $elders = get_var('elder_name',array('POST'));
 696           foreach ($elders as $elder)
 697             {
 698               $this->db->query("INSERT INTO eq_participation (elder,activity) "
 699                                . "VALUES (" . $elder . ",". $activity['activity'] . ")",__LINE__,__FILE__);
 700             }
 701
 702           $this->act_list();
 703           return false;
 704         }
 705
 706       if($action == 'insert')
 707         {
 708           $activity['name'] = $this->db->db_addslashes(get_var('name',array('POST')));
 709           $activity['date'] = $this->db->db_addslashes(get_var('date',array('POST')));
 710           $activity['notes']= $this->db->db_addslashes(get_var('notes',array('POST')));
 711           $this->db->query("INSERT INTO eq_activity (name,date,notes) "
 712                            . "VALUES ('" . $activity['name'] . "','"
 713                            . $activity['date'] . "','" . $activity['notes'] . "')",__LINE__,__FILE__);
 714           $this->act_list();
 715           return false;
 716         }
 717
 718       if($action == 'add')
 719         {
 720           $activity['activity'] = 0;
 721           $this->t->set_var('cal_date',$this->jscal->input('date','','','','','','',$this->cal_options));
 722           $this->t->set_var('name','');
 723           $this->t->set_var('date','');
 724           $this->t->set_var('notes','');
 725           $this->t->set_var('lang_done','Cancel');
 726           $this->t->set_var('lang_action','Adding New Activity');
 727           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.act_update&activity='
 728                                                                 . $activity['activity'] . '&action=' . 'insert'));
 729         }
 730
 731       if($action == 'edit')
 732         {
 733           $sql = "SELECT * FROM eq_activity WHERE activity=" . $activity['activity'];
 734           $this->db->query($sql,__LINE__,__FILE__);
 735           $this->db->next_record();
 736           $this->t->set_var('cal_date',$this->jscal->input('date',$this->db->f('date'),'','','','','',$this->cal_options));
 737           $this->t->set_var('name', $this->db->f('name'));
 738           $this->t->set_var('date', $this->db->f('date'));
 739           $this->t->set_var('notes', $this->db->f('notes'));
 740           $this->t->set_var('lang_done','Cancel');
 741           $this->t->set_var('lang_action','Editing Activity');
 742           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.act_update&activity='
 743                                                                 . $activity['activity'] . '&action=' . 'save'));
 744
 745         }
 746
 747       $sql = "SELECT * FROM eq_elder";
 748       $this->db->query($sql,__LINE__,__FILE__);
 749       $i=0;
 750       while ($this->db->next_record())
 751         {
 752           if($this->db->f('valid') == 1 || $action != 'add') {
 753             $elder_name[$i] = $this->db->f('name');
 754             $elder_id[$i] = $this->db->f('elder');
 755             $elder_valid[$i] = $this->db->f('valid');
 756             $i++;
 757           }
 758         }
 759       array_multisort($elder_name, $elder_id, $elder_valid);
 760
 761       $j=0;
 762       for ($i=0; $i < count($elder_id); $i++)
 763         {
 764           //$this->nextmatchs->template_alternate_row_color(&$this->t);
 765           $sql = "SELECT * FROM eq_participation where activity=". $activity['activity'] . " AND elder=" . $elder_id[$i];
 766           $this->db->query($sql,__LINE__,__FILE__);
 767           if($this->db->next_record()) { $this->t->set_var('checked','checked'); $checked=1; }
 768           else { $this->t->set_var('checked',''); $checked=0; }
 769           if($checked || $elder_valid[$i] == 1) {
 770             $this->t->set_var('elder_name',$elder_name[$i]);
 771             $this->t->set_var('elder',$elder_id[$i]);
 772             if(($j+1) % 3 == 0) { $this->t->set_var('table_sep',"</td></tr><tr>"); }
 773             else { $this->t->set_var('table_sep',"</td>"); }
 774             if(($j) % 3 == 0) { $this->nextmatchs->template_alternate_row_color(&$this->t); }
 775             $this->t->fp('list','elder_list',True);
 776             $j++;
 777           }
 778         }
 779
 780       $this->t->set_var('lang_reset','Clear Form');
 781       $this->t->set_var('lang_add','Add Activity');
 782       $this->t->set_var('lang_save','Save Changes');
 783       $this->t->set_var('edithandle','');
 784       $this->t->set_var('addhandle','');
 785
 786       $this->t->pfp('out','form');
 787       if($action == 'edit') { $this->t->pfp('addhandle','edit'); }
 788       if($action == 'add') { $this->t->pfp('addhandle','add'); }
 789
 790       $this->save_sessiondata();
 791     }
 792
 793   function par_view()
 794     {
 795       $this->t->set_file(array('par_view_t' => 'par_view.tpl'));
 796       $this->t->set_block('par_view_t','header_list','list1');
 797       $this->t->set_block('par_view_t','elder_list','list2');
 798
 799       $sql = "SELECT * FROM eq_elder where valid=1";
 800       $this->db->query($sql,__LINE__,__FILE__);
 801       $i=0;
 802       while ($this->db->next_record())
 803         {
 804           $elder_name[$i] = $this->db->f('name');
 805           $elder_id[$i] = $this->db->f('elder');
 806           $i++;
 807         }
 808       array_multisort($elder_name, $elder_id);
 809
 810       $sql = "SELECT * FROM eq_activity ORDER BY date DESC";
 811       $this->db->query($sql,__LINE__,__FILE__);
 812       $total_records = $this->db->num_rows();
 813
 814       $i = 0;
 815       while ($this->db->next_record())
 816         {
 817           $activity_list[$i]['name'] = $this->db->f('name');
 818           $activity_list[$i]['date'] = $this->db->f('date');
 819           $activity_list[$i]['activity']  = $this->db->f('activity');
 820           if($activity_list[$i]['date'] == '0000-00-00') { $activity_list[$i]['date']=""; }
 821           $i++;
 822         }
 823
 824       $elder_width=250; $part_width=25; $act_width=50;
 825       $total_width=$elder_width+$part_width;
 826       for ($i=0; $i < count($activity_list); $i++) {
 827         $this->t->set_var('activity_name',$activity_list[$i]['name']);
 828         $this->t->set_var('activity_date',$activity_list[$i]['date']);
 829         $this->t->fp('list1','header_list',True);
 830         $total_width += $act_width;
 831       }
 832
 833       for ($i=0; $i < count($elder_id); $i++) {
 834         $participated=0; $part_table = '';
 835         $this->nextmatchs->template_alternate_row_color(&$this->t);
 836         $this->t->set_var('elder_name',$elder_name[$i]);
 837         for ($j=0; $j < count($activity_list); $j++) {
 838           $sql = "SELECT * FROM eq_participation where activity="
 839              . $activity_list[$j]['activity'] . " AND elder=" . $elder_id[$i];
 840           $this->db->query($sql,__LINE__,__FILE__);
 841           if($this->db->next_record()) {
 842             $part_table .= '<td align=center><img src="checkmark.gif"></td>';
 843             $participated++;
 844           } else {
 845             $part_table .= '<td>&nbsp;</td>';
 846           }
 847         }
 848         if($participated) { $part_table .= '<td align=center><img src="checkmark.gif">'.$participated.'</td>'; }
 849         else { $part_table .= '<td>&nbsp;</td>'; }
 850         $this->t->set_var('part_table',$part_table);
 851         $this->t->fp('list2','elder_list',True);
 852       }
 853       $this->t->set_var('total_width',$total_width);
 854       $this->t->set_var('elder_width',$elder_width);
 855       $this->t->set_var('part_width',$part_width);
 856       $this->t->set_var('act_width',$act_width);
 857       $this->t->pfp('out','par_view_t');
 858       $this->save_sessiondata();
 859     }
 860
 861   function ppi_sched()
 862     {
 863       $this->t->set_file(array('ppi_sched_t' => 'ppi_sched.tpl'));
 864       $this->t->set_block('ppi_sched_t','elder_list','elderlist');
 865       $this->t->set_block('ppi_sched_t','appt_list','apptlist');
 866       $action = get_var('action',array('GET','POST'));
 867
 868       $this->t->set_var('lang_save','Save Appt / Pri / Notes');
 869       $this->t->set_var('lang_reset','Clear Changes');
 870
 871       $this->t->set_var('ppi_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_view'));
 872       $this->t->set_var('ppi_link_title','Hometeaching PPIs');
 873
 874       $this->t->set_var('eqpres_ppi_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_view&eqpresppi=1'));
 875       $this->t->set_var('eqpres_ppi_link_title','EQ President Yearly PPIs');
 876
 877       $this->t->set_var('schedule_ppi_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_sched'));
 878       $this->t->set_var('schedule_ppi_link_title','Schedule Yearly PPIs');
 879
 880       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_sched&action=save'));
 881       $this->t->set_var('title','EQ President Yearly PPIs Scheduler');
 882
 883       $elder_width=500; $phone_width=25; $pri_width=10; $notes_width=128; $ppi_date_width=20;
 884       $table_width=$elder_width + $phone_width + $pri_width + $notes_width + $ppi_date_width;
 885       $header_row = "<th width=$elder_width><font size=-2>Elder Name</th>";
 886       $header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
 887       $header_row.= "<th width=$pri_width><font size=-2>Priority</th>";
 888       $header_row.= "<th width=$ppi_date_width><font size=-2>Last PPI</th>";
 889       $header_row.= "<th width=$notes_width><font size=-2>Scheduling Notes</th>";
 890       $table_data=""; $completed_data=""; $totals_data="";
 891
 892       $year = date('Y');
 893
 894       if($action == 'save')
 895         {
 896           // Save any changes made to the appointment table
 897           $new_data = get_var('appt_notes',array('POST'));
 898           foreach ($new_data as $entry)
 899            {
 900              $elder = $entry['elder'];
 901              $appointment = $entry['appointment'];
 902
 903              //print "elder: $elder appointment: $appointment <br>";
 904
 905              // Perform database save actions here
 906              $this->db->query("UPDATE eq_appointment set " .
 907                               " elder='" . $elder . "'" .
 908                               " WHERE appointment=" . $appointment,__LINE__,__FILE__);
 909
 910            }
 911
 912           // Save any changes made to the ppi notes table
 913           $new_data = get_var('ppi_notes',array('POST'));
 914           foreach ($new_data as $entry)
 915            {
 916              $ppi_notes = $entry['notes'];
 917              $elder_id = $entry['elder_id'];
 918              $ppi_pri = $entry['pri'];
 919
 920              // Perform database save actions here
 921              $this->db->query("UPDATE eq_elder set " .
 922                               " ppi_notes='" . $ppi_notes . "'" .
 923                               ",ppi_pri='" . $ppi_pri . "'" .
 924                               " WHERE elder=" . $elder_id,__LINE__,__FILE__);
 925
 926            }
 927
 928           $take_me_to_url = $GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_sched');
 929           Header('Location: ' . $take_me_to_url);
 930         }
 931
 932       // APPOINTMENT TABLE
 933       $district = 1;
 934       $date_width=150; $time_width=100; $elder_width=200;
 935       $appt_table_width=$date_width + $time_width + $elder_width;
 936       $appt_header_row = "<th width=$date_width><font size=-2>Date</th>";
 937       $appt_header_row.= "<th width=$time_width><font size=-2>Time</th>";
 938       $appt_header_row.= "<th width=$elder_width><font size=-2>Elder</th>";
 939       $appt_table_data = "";
 940
 941       // create the elder id -> elder name mapping
 942       $sql = "SELECT * FROM eq_elder where valid=1 ORDER BY name ASC";
 943       $this->db->query($sql,__LINE__,__FILE__);
 944       $i=0;
 945       $elder_id = NULL;
 946       $elder_name = NULL;
 947       while ($this->db->next_record())
 948         {
 949           $elder_name[$i] = $this->db->f('name');
 950           $elder_id[$i] = $this->db->f('elder');
 951           $i++;
 952         }
 953       array_multisort($elder_name, $elder_id);
 954
 955       // query the database for all the appointments
 956       $sql = "SELECT * FROM eq_appointment where district=$district and date>=CURDATE() ORDER BY date ASC, time ASC";
 957       $this->db->query($sql,__LINE__,__FILE__);
 958
 959       while ($this->db->next_record())
 960         {
 961           $appointment = $this->db->f('appointment');
 962           $elder = $this->db->f('elder');
 963
 964           $date = $this->db->f('date');
 965           $date_array = explode("-",$date);
 966           $year = $date_array[0]; $month = $date_array[1]; $day = $date_array[2];
 967           $day_string = date("l d-M-Y", mktime(0,0,0,$month,$day,$year));
 968
 969           $time = $this->db->f('time');
 970           $time_array = explode(":",$time);
 971           $time_string = date("g:i a", mktime($time_array[0], $time_array[1], $time_array[2]));
 972
 973           $appt_table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
 974           $appt_table_data.= "<td align=center>$day_string</td>";
 975           $appt_table_data.= "<td align=center>$time_string</td>";
 976
 977           $appt_table_data.= '<td align=center><select name=appt_notes['.$appointment.'][elder]>';
 978           $appt_table_data.= '<option value=0></option>';
 979           for ($i=0; $i < count($elder_id); $i++) {
 980             $id = $elder_id[$i];
 981             $name = $elder_name[$i];
 982             if($elder_id[$i] == $elder) { $selected[$id] = 'selected="selected"'; } else { $selected[$id] = ''; }
 983             $appt_table_data.= '<option value='.$id.' '.$selected[$id].'>'.$name.'</option>';
 984           }
 985           $appt_table_data.='</select></td>';
 986
 987           $appt_table_data.= '<input type=hidden name="appt_notes['.$appointment.'][appointment]" value="'.$appointment.'">';
 988
 989           $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
 990           $this->t->set_var('tr_color',$tr_color);
 991         }
 992
 993       $this->t->set_var('appt_table_data',$appt_table_data);
 994       $this->t->set_var('appt_header_row',$appt_header_row);
 995       $this->t->set_var('appt_table_width',$appt_table_width);
 996
 997
 998       // PPI SCHEDULING TABLE
 999       $sql = "SELECT * FROM eq_elder where valid=1 ORDER BY ppi_pri ASC";
1000       $this->db->query($sql,__LINE__,__FILE__);
1001
1002       $total_elders=0; $elders_with_yearly_ppi=0;
1003
1004       $i=0;
1005       $elder_id = NULL;
1006       while ($this->db->next_record())
1007         {
1008           $elder_id[$i] = $this->db->f('elder');
1009           $elder_name[$i] = $this->db->f('name');
1010           $elder_phone[$elder_id[$i]] = $this->db->f('phone');
1011           $elder_ppi_pri[$elder_id[$i]] = $this->db->f('ppi_pri');
1012           $elder_ppi_notes[$elder_id[$i]] = $this->db->f('ppi_notes');
1013           $i++;
1014           $total_elders++;
1015         }
1016
1017       $max = count($elder_id);
1018
1019       for($i=0; $i < $max; $i++) {
1020           $id = $elder_id[$i];
1021           $name = $elder_name[$i];
1022           $phone = $elder_phone[$id];
1023           $ppi_pri = $elder_ppi_pri[$id];
1024           $ppi_notes = $elder_ppi_notes[$id];
1025
1026           // If this elder has had a yearly PPI this year, don't show him on the schedule list
1027           $year_start = $year - 1 . "-12-31"; $year_end = $year + 1 . "-01-01";
1028           $sql = "SELECT * FROM eq_ppi WHERE date > '$year_start' AND date < '$year_end' ".
1029              "AND elder=" . $id . " AND eqpresppi=1";
1030           $this->db2->query($sql,__LINE__,__FILE__);
1031
1032           if(!$this->db2->next_record()) {
1033             $sql = "SELECT * FROM eq_ppi WHERE elder=" . $id . " AND eqpresppi=1 ORDER BY date DESC";
1034             $this->db->query($sql,__LINE__,__FILE__);
1035             if($this->db->next_record()) { $date = $this->db->f('date'); } else { $date = ""; }
1036             $link_data['menuaction'] = 'eq.eq.ppi_update';
1037             $link_data['elder'] = $id;
1038             $link_data['aaronic'] = 0;
1039             $link_data['name'] = $name;
1040             $link_data['ppi'] = '';
1041             $link_data['eqpresppi'] = 1;
1042             $link_data['action'] = 'add';
1043             $link = $GLOBALS['phpgw']->link('/eq/index.php',$link_data);
1044             $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
1045             $table_data.= "<td align=center>$phone</td>";
1046             //$table_data.= "<td align=center>$ppi_pri</td>";
1047             $table_data.= "<td align=center>";
1048             $table_data.= '<select name=ppi_notes['.$i.'][pri]>';
1049             foreach(range(0,6) as $num) {
1050               if($num == 0) { $num = 1; } else {$num = $num*5; }
1051               if($ppi_pri == $num) { $selected[$num] = 'selected="selected"'; } else { $selected[$num] = ''; }
1052               $table_data.= '<option value='.$num.' '.$selected[$num].'>'.$num.'</option>';
1053             }
1054             $table_data.= '</select></td>';
1055             $table_data.= "<td align=center>$date</td>";
1056             $table_data.= '<td><input type=text size="50" maxlength="128" name="ppi_notes['.$i.'][notes]" value="'.$ppi_notes.'">';
1057             $table_data.= '<input type=hidden name="ppi_notes['.$i.'][elder_id]" value="'.$id.'">';
1058             $table_data.= '<input type=hidden name="ppi_notes['.$i.'][elder_name]" value="'.$name.'">';
1059             $table_data.= '</td>';
1060             $table_data.= '</tr>';
1061             $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1062             $this->t->set_var('tr_color',$tr_color);
1063           } else {
1064             $link_data['menuaction'] = 'eq.eq.ppi_update';
1065             $link_data['interviewer'] = $this->db2->f('interviewer');
1066             $link_data['elder'] = $this->db2->f('elder');
1067             $link_data['aaronic'] = $this->db2->f('aaronic');
1068             $link_data['name'] = $name;
1069             $link_data['ppi'] = $this->db2->f('ppi');
1070             $link_data['eqpresppi'] = $this->db2->f('eqpresppi');
1071             $link_data['action'] = 'view';
1072             $link = $GLOBALS['phpgw']->link('/eq/index.php',$link_data);
1073             $elders_with_yearly_ppi++;
1074             $date = $this->db2->f('date');
1075             $ppi_notes = $this->db2->f('notes');
1076             if(strlen($ppi_notes) > 40) { $ppi_notes = substr($ppi_notes,0,40) . "..."; }
1077             $completed_data.= "<tr bgcolor=". $this->t->get_var('tr_color2') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
1078             $completed_data.= "<td align=center>$phone</td>";
1079             $completed_data.= "<td align=center><a href=".$link.">$date</a></td>";
1080             $completed_data.= "<td align=left>$ppi_notes</td>";
1081             $completed_data.= '</tr>';
1082             $tr_color2 = $this->nextmatchs->alternate_row_color($tr_color2);
1083             $this->t->set_var('tr_color2',$tr_color2);
1084           }
1085       }
1086
1087       $name_width=175; $phone_width=100; $date_width=100; $notes_width=300;
1088       $completed_table_width=$name_width + $phone_width + $date_width + $notes_width;
1089       $completed_header_row = "<th width=$name_width><font size=-2>Elder Name</th>";
1090       $completed_header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
1091       $completed_header_row.= "<th width=$date_width><font size=-2>Date</th>";
1092       $completed_header_row.= "<th width=$notes_width><font size=-2>PPI Notes</th>";
1093
1094       $elders_width=300; $totals_width=100;
1095       $totals_table_width=$elders_width + $totals_width;
1096       $totals_header_row = "<th width=$elders_width><font size=-2>Elders</th>";
1097       $totals_header_row.= "<th width=$totals_width><font size=-2>$year</th>";
1098       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1099       $totals_data.= "<td align=left><font size=-2><b>Total Elders with yearly PPIs completed:</b></font></td>";
1100       $totals_data.= "<td align=center><font size=-2><b>$elders_with_yearly_ppi / $total_elders</b></font></td>";
1101       $percent = ceil(($elders_with_yearly_ppi / $total_elders)*100);
1102       $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1103       $this->t->set_var('tr_color',$tr_color);
1104       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1105       $totals_data.= "<td align=left><font size=-2><b>Percentage:</b></font></td>";
1106       $totals_data.= "<td align=center><font size=-2><b>$percent%</b></font></td>";
1107       $totals_data.= "</tr>";
1108
1109       $this->t->set_var('table_width',$table_width);
1110       $this->t->set_var('header_row',$header_row);
1111       $this->t->set_var('table_data',$table_data);
1112       $this->t->set_var('totals_header_row',$totals_header_row);
1113       $this->t->set_var('totals_table_width',$totals_table_width);
1114       $this->t->set_var('completed_header_row',$completed_header_row);
1115       $this->t->set_var('completed_table_width',$completed_table_width);
1116       $this->t->set_var('completed',$completed_data);
1117       $this->t->set_var('totals',$totals_data);
1118       $this->t->fp('elderlist','elder_list',True);
1119       $this->t->fp('apptlist','appt_list',True);
1120
1121       $this->t->pfp('out','ppi_sched_t');
1122       $this->save_sessiondata();
1123
1124     }
1125
1126   function vis_sched()
1127     {
1128       $this->t->set_file(array('vis_sched_t' => 'vis_sched.tpl'));
1129       $this->t->set_block('vis_sched_t','family_list','familylist');
1130       $this->t->set_block('vis_sched_t','appt_list','apptlist');
1131       $action = get_var('action',array('GET','POST'));
1132
1133       $this->t->set_var('lang_save','Save Appt / Pri / Notes');
1134       $this->t->set_var('lang_reset','Clear Changes');
1135
1136       $this->t->set_var('vis_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_view'));
1137       $this->t->set_var('vis_link_title','View Yearly Visits');
1138
1139       $this->t->set_var('schedule_vis_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_sched'));
1140       $this->t->set_var('schedule_vis_link_title','Schedule Yearly Visits');
1141
1142       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_sched&action=save'));
1143       $this->t->set_var('title','EQ Presidency Yearly Visit Scheduler');
1144
1145       $family_width=500; $phone_width=40; $pri_width=10; $notes_width=128; $visit_date_width=20;
1146       $table_width=$family_width + $phone_width + $pri_width + $notes_width + $visit_date_width;
1147       $header_row = "<th width=$family_width><font size=-2>Family Name</th>";
1148       $header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
1149       $header_row.= "<th width=$pri_width><font size=-2>Priority</th>";
1150       $header_row.= "<th width=$visit_date_width><font size=-2>Last Visit</th>";
1151       $header_row.= "<th width=$notes_width><font size=-2>Scheduling Notes</th>";
1152       $table_data=""; $completed_data=""; $totals_data="";
1153
1154       $year = date('Y');
1155
1156       if($action == 'save')
1157         {
1158           // Save any changes made to the appointment table
1159           $new_data = get_var('appt_notes',array('POST'));
1160           foreach ($new_data as $entry)
1161            {
1162              $family = $entry['family'];
1163              $appointment = $entry['appointment'];
1164
1165              // Perform database save actions here
1166              $this->db->query("UPDATE eq_appointment set " .
1167                               " family='" . $family . "'" .
1168                               " WHERE appointment=" . $appointment,__LINE__,__FILE__);
1169
1170            }
1171
1172           // Save any changes made to the visit notes table
1173           $new_data = get_var('vis_notes',array('POST'));
1174           foreach ($new_data as $entry)
1175            {
1176              $visit_notes = $entry['notes'];
1177              $family_id = $entry['family_id'];
1178              $visit_pri = $entry['pri'];
1179
1180              // Perform database save actions here
1181              $this->db->query("UPDATE eq_family set " .
1182                               " visit_notes='" . $visit_notes . "'" .
1183                               ",visit_pri='" . $visit_pri . "'" .
1184                               " WHERE family=" . $family_id,__LINE__,__FILE__);
1185
1186            }
1187
1188           $take_me_to_url = $GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_sched');
1189           Header('Location: ' . $take_me_to_url);
1190         }
1191
1192       // APPOINTMENT TABLE
1193       $district = 4;
1194       $date_width=150; $time_width=100; $family_width=250;
1195       $appt_table_width=$date_width + $time_width + $family_width;
1196       $appt_header_row = "<th width=$date_width><font size=-2>Date</th>";
1197       $appt_header_row.= "<th width=$time_width><font size=-2>Time</th>";
1198       $appt_header_row.= "<th width=$family_width><font size=-2>Family</th>";
1199       $appt_table_data = "";
1200
1201       // create the family id -> family name mapping
1202       $sql = "SELECT * FROM eq_family where valid=1 and elder_id != 0 ORDER BY name ASC";
1203       $this->db->query($sql,__LINE__,__FILE__);
1204       $i=0;
1205       $family_id = NULL;
1206       while ($this->db->next_record())
1207         {
1208           $family_id[$i] = $this->db->f('family');
1209           $family_name[$i] = $this->db->f('name');
1210           $i++;
1211         }
1212       array_multisort($family_name, $family_id);
1213
1214       // query the database for all the appointments
1215       $sql = "SELECT * FROM eq_appointment where district=$district and date>=CURDATE() ORDER BY date ASC, time ASC";
1216       $this->db->query($sql,__LINE__,__FILE__);
1217
1218       while ($this->db->next_record())
1219         {
1220           $appointment = $this->db->f('appointment');
1221           $family = $this->db->f('family');
1222
1223           $date = $this->db->f('date');
1224           $date_array = explode("-",$date);
1225           $year = $date_array[0]; $month = $date_array[1]; $day = $date_array[2];
1226           $day_string = date("l d-M-Y", mktime(0,0,0,$month,$day,$year));
1227
1228           $time = $this->db->f('time');
1229           $time_array = explode(":",$time);
1230           $time_string = date("g:i a", mktime($time_array[0], $time_array[1], $time_array[2]));
1231
1232           $appt_table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1233           $appt_table_data.= "<td align=center>$day_string</td>";
1234           $appt_table_data.= "<td align=center>$time_string</td>";
1235
1236           $appt_table_data.= '<td align=center><select name=appt_notes['.$appointment.'][family]>';
1237           $appt_table_data.= '<option value=0></option>';
1238           for ($i=0; $i < count($family_id); $i++) {
1239             $id = $family_id[$i];
1240             $name = $family_name[$i];
1241             if($family_id[$i] == $family) { $selected[$id] = 'selected="selected"'; } else { $selected[$id] = ''; }
1242             $appt_table_data.= '<option value='.$id.' '.$selected[$id].'>'.$name.' Family</option>';
1243           }
1244           $appt_table_data.='</select></td>';
1245
1246           $appt_table_data.= '<input type=hidden name="appt_notes['.$appointment.'][appointment]" value="'.$appointment.'">';
1247
1248           $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1249           $this->t->set_var('tr_color',$tr_color);
1250         }
1251
1252       $this->t->set_var('appt_table_data',$appt_table_data);
1253       $this->t->set_var('appt_header_row',$appt_header_row);
1254       $this->t->set_var('appt_table_width',$appt_table_width);
1255
1256
1257       // VISIT SCHEDULING TABLE
1258       $sql = "SELECT * FROM eq_family where valid=1 and elder_id != 0 ORDER BY visit_pri ASC";
1259       $this->db->query($sql,__LINE__,__FILE__);
1260
1261       $total_families=0; $families_with_yearly_visit=0;
1262
1263       $i=0;
1264       $family_id = NULL;
1265       $family_name = NULL;
1266       $family_phone = NULL;
1267       $family_visit_pri = NULL;
1268       $family_visit_notes = NULL;
1269       while ($this->db->next_record())
1270         {
1271           $family_id[$i] = $this->db->f('family');
1272           $family_name[$i] = $this->db->f('name');
1273           $family_phone[$family_id[$i]] = $family_id[$i] . " ERROR";
1274           $family_visit_pri[$family_id[$i]] = $this->db->f('visit_pri');
1275           $family_visit_notes[$family_id[$i]] = $this->db->f('visit_notes');
1276           $i++;
1277           $total_families++;
1278         }
1279
1280       $sql = "SELECT * FROM eq_parent where valid=1";
1281       $this->db->query($sql,__LINE__,__FILE__);
1282       while ($this->db->next_record())
1283         {
1284           $family = $this->db->f('family');
1285           $phone = $this->db->f('phone');
1286           $family_phone[$family] = $phone;
1287         }
1288
1289       $max = count($family_id);
1290
1291       for($i=0; $i < $max; $i++) {
1292           $id = $family_id[$i];
1293           $name = $family_name[$i];
1294           $phone = $family_phone[$id];
1295           $vis_pri = $family_visit_pri[$id];
1296           $vis_notes = $family_visit_notes[$id];
1297
1298           // If this family has had a yearly visit this year, don't show them on the schedule list
1299           $year_start = $year - 1 . "-12-31"; $year_end = $year + 1 . "-01-01";
1300           $sql = "SELECT * FROM eq_visit WHERE date > '$year_start' AND date < '$year_end' ".
1301              "AND family=" . $id . " AND companionship=0";
1302           $this->db2->query($sql,__LINE__,__FILE__);
1303
1304           if(!$this->db2->next_record()) {
1305             $sql = "SELECT * FROM eq_visit WHERE family=" . $id . " AND companionship=0 ORDER BY date DESC";
1306             $this->db->query($sql,__LINE__,__FILE__);
1307             if($this->db->next_record()) { $date = $this->db->f('date'); } else { $date = ""; }
1308             $link_data['menuaction'] = 'eq.eq.vis_update';
1309             $link_data['visit'] = '';
1310             $link_data['family'] = $id;
1311             $link_data['name'] = $name;
1312             $link_data['action'] = 'add';
1313             $link = $GLOBALS['phpgw']->link('/eq/index.php',$link_data);
1314             $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') ."><td title=\"$phone\"><a href=$link>$name Family</a></td>";
1315             $table_data.= "<td align=center>$phone</td>";
1316             $table_data.= "<td align=center>";
1317             $table_data.= '<select name=vis_notes['.$i.'][pri]>';
1318             foreach(range(0,6) as $num) {
1319               if($num == 0) { $num = 1; } else {$num = $num*5; }
1320               if($vis_pri == $num) { $selected[$num] = 'selected="selected"'; } else { $selected[$num] = ''; }
1321               $table_data.= '<option value='.$num.' '.$selected[$num].'>'.$num.'</option>';
1322             }
1323             $table_data.= '</select></td>';
1324             $table_data.= "<td align=center>$date</td>";
1325             $table_data.= '<td><input type=text size="50" maxlength="128" name="vis_notes['.$i.'][notes]" value="'.$vis_notes.'">';
1326             $table_data.= '<input type=hidden name="vis_notes['.$i.'][family_id]" value="'.$id.'">';
1327             $table_data.= '<input type=hidden name="vis_notes['.$i.'][family_name]" value="'.$name.'">';
1328             $table_data.= '</td>';
1329             $table_data.= '</tr>';
1330             $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1331             $this->t->set_var('tr_color',$tr_color);
1332           } else {
1333             $link_data['menuaction'] = 'eq.eq.vis_update';
1334             $link_data['visit'] = $this->db2->f('visit');
1335             $link_data['family'] = $this->db2->f('family');
1336             $link_data['name'] = $name;
1337             $link_data['date'] = $this->db2->f('date');
1338             $link_data['action'] = 'view';
1339             $link = $GLOBALS['phpgw']->link('/eq/index.php',$link_data);
1340             $families_with_yearly_visit++;
1341             $date = $this->db2->f('date');
1342             $vis_notes = $this->db2->f('notes');
1343             if(strlen($vis_notes) > 40) { $vis_notes = substr($vis_notes,0,40) . "..."; }
1344             $completed_data.= "<tr bgcolor=". $this->t->get_var('tr_color2') ."><td title=\"$phone\"><a href=$link>$name Family</a></td>";
1345             $completed_data.= "<td align=center>$phone</td>";
1346             $completed_data.= "<td align=center><a href=".$link.">$date</a></td>";
1347             $completed_data.= "<td align=left>$vis_notes</td>";
1348             $completed_data.= '</tr>';
1349             $tr_color2 = $this->nextmatchs->alternate_row_color($tr_color2);
1350             $this->t->set_var('tr_color2',$tr_color2);
1351           }
1352       }
1353
1354       $name_width=175; $phone_width=100; $date_width=100; $notes_width=300;
1355       $completed_table_width=$name_width + $phone_width + $date_width + $notes_width;
1356       $completed_header_row = "<th width=$name_width><font size=-2>Family Name</th>";
1357       $completed_header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
1358       $completed_header_row.= "<th width=$date_width><font size=-2>Date</th>";
1359       $completed_header_row.= "<th width=$notes_width><font size=-2>Visit Notes</th>";
1360
1361       $family_width=300; $totals_width=100;
1362       $totals_table_width=$family_width + $totals_width;
1363       $totals_header_row = "<th width=$family_width><font size=-2>Families</th>";
1364       $totals_header_row.= "<th width=$totals_width><font size=-2>$year</th>";
1365       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1366       $totals_data.= "<td align=left><font size=-2><b>Total Families with yearly Visits completed:</b></font></td>";
1367       $totals_data.= "<td align=center><font size=-2><b>$families_with_yearly_visit / $total_families</b></font></td>";
1368       $percent = ceil(($families_with_yearly_visit / $total_families)*100);
1369       $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1370       $this->t->set_var('tr_color',$tr_color);
1371       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1372       $totals_data.= "<td align=left><font size=-2><b>Percentage:</b></font></td>";
1373       $totals_data.= "<td align=center><font size=-2><b>$percent%</b></font></td>";
1374       $totals_data.= "</tr>";
1375
1376       $this->t->set_var('table_width',$table_width);
1377       $this->t->set_var('header_row',$header_row);
1378       $this->t->set_var('table_data',$table_data);
1379       $this->t->set_var('totals_header_row',$totals_header_row);
1380       $this->t->set_var('totals_table_width',$totals_table_width);
1381       $this->t->set_var('completed_header_row',$completed_header_row);
1382       $this->t->set_var('completed_table_width',$completed_table_width);
1383       $this->t->set_var('completed',$completed_data);
1384       $this->t->set_var('totals',$totals_data);
1385       $this->t->fp('familylist','family_list',True);
1386       $this->t->fp('apptlist','appt_list',True);
1387
1388       $this->t->pfp('out','vis_sched_t');
1389       $this->save_sessiondata();
1390
1391     }
1392
1393   function ppi_view()
1394     {
1395       $this->t->set_file(array('ppi_view_t' => 'ppi_view.tpl'));
1396       $this->t->set_block('ppi_view_t','district_list','list');
1397
1398       $this->t->set_var('linkurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_view'));
1399       $num_months = get_var('num_months',array('GET','POST'));
1400       if($num_months == '') { $num_months = $this->default_ppi_num_months; }
1401       $this->t->set_var('num_months',$num_months);
1402       if($num_months == 1) { $this->t->set_var('lang_num_months','Month of History'); }
1403       else {  $this->t->set_var('lang_num_months','Months of History'); }
1404       $this->t->set_var('lang_filter','Filter');
1405       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_view'));
1406
1407       $this->t->set_var('ppi_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_view'));
1408       $this->t->set_var('ppi_link_title','Hometeaching PPIs');
1409
1410       $this->t->set_var('eqpres_ppi_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_view&eqpresppi=1'));
1411       $this->t->set_var('eqpres_ppi_link_title','EQ President Yearly PPIs');
1412       $eqpresppi = get_var('eqpresppi',array('GET','POST'));
1413
1414       $this->t->set_var('schedule_ppi_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_sched'));
1415       $this->t->set_var('schedule_ppi_link_title','Schedule Yearly PPIs');
1416
1417       if($eqpresppi == 1) {
1418         $this->t->set_var('title','EQ President Yearly PPIs');
1419         $num_months = get_var('num_months',array('GET','POST'));
1420         if($num_months == '') { $num_months = $this->default_ppi_num_years; }
1421         $this->t->set_var('num_months',$num_months);
1422         if($num_months == 1) { $this->t->set_var('lang_num_months','Year of History'); }
1423         else { $this->t->set_var('lang_num_months','Years of History'); }
1424       }
1425       else {
1426         $this->t->set_var('title','Hometeaching PPIs');
1427       }
1428
1429       $sql = "SELECT * FROM eq_district where valid=1 ORDER BY district ASC";
1430       $this->db->query($sql,__LINE__,__FILE__);
1431       $i=0;
1432       while ($this->db->next_record())
1433         {
1434           if(($eqpresppi == 1) && ($this->db->f('district') == 1)) {
1435             $districts[$i]['district'] = $this->db->f('district');
1436             $districts[$i]['name'] = $this->db->f('name');
1437             $districts[$i]['supervisor'] = $this->db->f('supervisor');
1438           } else if($eqpresppi == 0) {
1439             $districts[$i]['district'] = $this->db->f('district');
1440             $districts[$i]['name'] = $this->db->f('name');
1441             $districts[$i]['supervisor'] = $this->db->f('supervisor');
1442             $i++;
1443           }
1444         }
1445
1446       $sql = "SELECT * FROM eq_elder where valid=1 ORDER BY elder ASC";
1447       $this->db->query($sql,__LINE__,__FILE__);
1448       $i=0;
1449       while ($this->db->next_record())
1450         {
1451           $elder_id[$i] = $this->db->f('elder');
1452           $elder_name[$i] = $this->db->f('name');
1453           $elder_phone[$elder_id[$i]] = $this->db->f('phone');
1454           $elder_ppi_pri[$elder_id[$i]] = $this->db->f('ppi_pri');
1455           $elder_ppi_notes[$elder_id[$i]] = $this->db->f('ppi_notes');
1456           $i++;
1457         }
1458       array_multisort($elder_name, $elder_id);
1459       for($i=0; $i < count($elder_id); $i++) {
1460           $id = $elder_id[$i];
1461           $elders[$id] = $elder_name[$i];
1462       }
1463
1464       $sql = "SELECT * FROM eq_aaronic where valid=1 ORDER BY aaronic ASC";
1465       $this->db->query($sql,__LINE__,__FILE__);
1466       while ($this->db->next_record())
1467         {
1468           $aaronic_id = $this->db->f('aaronic');
1469           $aaronic[$aaronic_id]['name'] = $this->db->f('name');
1470           $aaronic[$aaronic_id]['phone'] = $this->db->f('phone');
1471         }
1472
1473       $total_companionships = 0;
1474       $this->nextmatchs->template_alternate_row_color(&$this->t);
1475       for ($i=0; $i < count($districts); $i++) {
1476         if($eqpresppi == 1) {
1477           $this->t->set_var('district_number','*');
1478           $this->t->set_var('district_name','EQ President');
1479         } else {
1480           $this->t->set_var('district_number',$districts[$i]['district']);
1481           $this->t->set_var('district_name',$districts[$i]['name']);
1482         }
1483         $supervisor = $districts[$i]['supervisor'];
1484         $unique_companionships='';
1485
1486         // Select all the unique companionship numbers for this district
1487         if($eqpresppi == 1) {
1488           $sql = "SELECT distinct companionship FROM eq_companionship where valid=1";
1489         }
1490         else {
1491           $sql = "SELECT distinct companionship FROM eq_companionship where valid=1 and district=". $districts[$i]['district'];
1492         }
1493         $this->db->query($sql,__LINE__,__FILE__);
1494         $j=0;
1495         while ($this->db->next_record())
1496           {
1497             $unique_companionships[$j]['companionship'] = $this->db->f('companionship');
1498             $j++;
1499           }
1500
1501         $comp_width=400; $ppi_width=25; $table_width=$comp_width + $num_months*$ppi_width;
1502         $table_data=""; $num_companionships = $j; $num_elders = 0;
1503         for($m=$num_months; $m >= 0; $m--) { $ppis[$m] = 0; }
1504         for ($j=0; $j < count($unique_companionships); $j++) {
1505           // Select all the companions in each companionship
1506           if($eqpresppi) {
1507             $sql = "SELECT * FROM eq_companionship where valid=1 and aaronic=0 and ".
1508                    "companionship=". $unique_companionships[$j]['companionship'];
1509           }
1510           else {
1511             $sql = "SELECT * FROM eq_companionship where valid=1 and ".
1512                    "companionship=". $unique_companionships[$j]['companionship'];
1513           }
1514           $this->db->query($sql,__LINE__,__FILE__);
1515           $k=0;
1516           $comp = $unique_companionships[$j]['companionship'];
1517           for($m=$num_months; $m >= 0; $m--) { $ppi_recorded[$comp][$m] = 0; }
1518           while ($this->db->next_record())
1519             {
1520               // Get this companions information
1521               $num_elders++;
1522               $companionship = $this->db->f('companionship');
1523               $elder_id = $this->db->f('elder');
1524               $aaronic_id = $this->db->f('aaronic');
1525               if($elder_id) {
1526                 $name = $elders[$elder_id];
1527                 $phone = $elder_phone[$elder_id];
1528               }
1529               else if($aaronic_id) {
1530                 $name = $aaronic[$aaronic_id]['name'];
1531                 $phone = $aaronic[$aaronic_id]['phone'];
1532               }
1533               $link_data['menuaction'] = 'eq.eq.ppi_update';
1534               $link_data['companionship'] = $companionship;
1535               $link_data['interviewer'] = $supervisor;
1536               $link_data['elder'] = $elder_id;
1537               $link_data['aaronic'] = $aaronic_id;
1538               $link_data['name'] = $name;
1539               $link_data['ppi'] = '';
1540               $link_data['eqpresppi'] = $eqpresppi;
1541               $link_data['action'] = 'add';
1542               $link = $GLOBALS['phpgw']->link('/eq/index.php',$link_data);
1543               $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
1544
1545               // Find out how many times PPIs were performed in the past $num_months for this Elder
1546               $header_row="<th width=$comp_width><font size=-2>Companionship</th>";
1547               for($m=$num_months; $m >= 0; $m--) {
1548                 if($eqpresppi == 1) {
1549                   $year = date('Y') - $m;
1550                   $year_start = $year - 1 . "-12-31"; $year_end = $year + 1 . "-01-01";
1551                   $sql = "SELECT * FROM eq_ppi WHERE date > '$year_start' AND date < '$year_end' ".
1552                          "AND elder=" . $elder_id . " AND aaronic=" . $aaronic_id . " AND eqpresppi=1";
1553                   $this->db2->query($sql,__LINE__,__FILE__);
1554                   $header_row .= "<th width=150><font size=-2>$year</th>";
1555                 }
1556                 else {
1557                   $month = $this->current_month - $m;
1558                   $year = $this->current_year;
1559                   if($month <= 0) { $remainder = $month; $month = 12 + $remainder; $year=$year-1; }
1560                   if($month < 10) { $month = "0"."$month"; }
1561                   $month_start = "$year"."-"."$month"."-"."01";
1562                   $month_end = "$year"."-"."$month"."-"."31";
1563                   $month = "$month"."/"."$year";
1564                   $sql = "SELECT * FROM eq_ppi WHERE date >= '$month_start' AND date <= '$month_end' ".
1565                          "AND elder=" . $elder_id . " AND aaronic=" . $aaronic_id . " AND eqpresppi=0";
1566                   $this->db2->query($sql,__LINE__,__FILE__);
1567                   $header_row .= "<th width=$ppi_width><font size=-2>$month</th>";
1568                 }
1569                 if(!$total_ppis[$m]) { $total_ppis[$m] = 0; }
1570                 if($this->db2->next_record()) {
1571                   if(!$ppi_recorded[$companionship][$m]) {
1572                     $ppis[$m]++; $total_ppis[$m]++; $ppi_recorded[$companionship][$m]=1;
1573                   }
1574                   $link_data['menuaction'] = 'eq.eq.ppi_update';
1575                   $link_data['companionship'] = $companionship;
1576                   $link_data['interviewer'] = $this->db2->f('interviewer');
1577                   $link_data['elder'] = $elder_id;
1578                   $link_data['aaronic'] = $aaronic_id;
1579                   $link_data['name'] = $name;
1580                   $link_data['ppi'] = $this->db2->f('ppi');
1581                   $link_data['eqpresppi'] = $eqpresppi;
1582                   $link_data['action'] = 'view';
1583                   $date = $this->db2->f('date');
1584                   $date_array = explode("-",$date);
1585                   $month = $date_array[1];
1586                   $day   = $date_array[2];
1587                   $link = $GLOBALS['phpgw']->link('/eq/index.php',$link_data);
1588                   $table_data .= '<td align=center><a href='.$link.'><img src="checkmark.gif"><br>'.$month.'-'.$day.'</a></td>';
1589                 }
1590                 else { $table_data .= "<td>&nbsp;</td>"; }
1591               }
1592               $table_data .= "</tr>";
1593               $k++;
1594             }
1595           $table_data .= "<tr><td colspan=20><hr></td></tr>";
1596         }
1597         // Now add Elders not assigned to any companionship to the table if we are in eqpresppi mode
1598         if($eqpresppi == 1) {
1599           $table_data .= "<tr bgcolor=\"#c9c9c9\"><td colspan=20><b>Unassigned Potential Home Teachers</b><hr></td></tr>";
1600           foreach($elders as $elder_id => $value) {
1601             $sql = "SELECT * FROM eq_companionship where valid=1 and elder=".$elder_id;
1602             $this->db->query($sql,__LINE__,__FILE__);
1603             if(!$this->db->next_record()) {
1604               // We found an Elder not in a companionship, add them to the table
1605               $num_elders++;
1606               $companionship=0;
1607               $name = $elders[$elder_id];
1608               $link_data['menuaction'] = 'eq.eq.ppi_update';
1609               $link_data['companionship'] = $companionship;
1610               $link_data['interviewer'] = $supervisor;
1611               $link_data['elder'] = $elder_id;
1612               $link_data['name'] = $name;
1613               $link_data['ppi'] = '';
1614               $link_data['eqpresppi'] = $eqpresppi;
1615               $link_data['action'] = 'add';
1616               $link = $GLOBALS['phpgw']->link('/eq/index.php',$link_data);
1617               $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') ."><td><a href=$link>$name</a></td>";
1618               for($m=$num_months; $m >= 0; $m--) {
1619                 $year = date('Y') - $m;
1620                 $year_start = $year - 1 . "-12-31"; $year_end = $year + 1 . "-01-01";
1621                 $sql = "SELECT * FROM eq_ppi WHERE date > '$year_start' AND date < '$year_end' ".
1622                        "AND elder=" . $elder_id . " AND eqpresppi=1";
1623                 $this->db2->query($sql,__LINE__,__FILE__);
1624                 if(!$total_ppis[$m]) { $total_ppis[$m] = 0; }
1625                 if($this->db2->next_record()) {
1626                   $ppis[$m]++; $total_ppis[$m]++;
1627                   $link_data['menuaction'] = 'eq.eq.ppi_update';
1628                   $link_data['companionship'] = $companionship;
1629                   $link_data['interviewer'] = $supervisor;
1630                   $link_data['elder'] = $elder_id;
1631                   $link_data['name'] = $name;
1632                   $link_data['ppi'] = $this->db2->f('ppi');
1633                   $link_data['eqpresppi'] = $eqpresppi;
1634                   $link_data['action'] = 'view';
1635                   $date = $this->db2->f('date');
1636                   $link = $GLOBALS['phpgw']->link('/eq/index.php',$link_data);
1637                   $table_data .= '<td align=center><a href='.$link.'><img src="checkmark.gif"><br>'.$date.'</a></td>';
1638                 }
1639                 else { $table_data .= "<td>&nbsp;</td>"; }
1640               }
1641               $table_data .= "</tr>";
1642             }
1643           }
1644         }
1645         $total_companionships += $num_companionships;
1646         if($eqpresppi == 1) {
1647           $stat_data = "<tr><td><b><font size=-2>$num_elders Elders<br>PPI Totals:</font></b></td>";
1648         } else {
1649           $stat_data = "<tr><td><b><font size=-2>$num_companionships Companionships<br>PPI Totals:</font></b></td>";
1650         }
1651         for($m=$num_months; $m >=0; $m--) {
1652           if($eqpresppi == 1) { $percent = ceil(($ppis[$m] / $num_elders)*100); }
1653           else { $percent = ceil(($ppis[$m] / $num_companionships)*100); }
1654           $stat_data .= "<td align=center><font size=-2><b>$ppis[$m]<br>$percent%</font></b></td>";
1655         }
1656         $stat_data .= "</tr>";
1657
1658         $this->t->set_var('table_width',$table_width);
1659         $this->t->set_var('header_row',$header_row);
1660         $this->t->set_var('table_data',$table_data);
1661         $this->t->set_var('stat_data',$stat_data);
1662         $this->t->fp('list','district_list',True);
1663       }
1664
1665       $totals = "<tr><td><b><font size=-2>$total_companionships Total Comps<br>PPI Totals:</font></b></td>";
1666       for($m=$num_months; $m >=0; $m--) {
1667         $percent = ceil(($total_ppis[$m] / $total_companionships)*100);
1668         $totals .= "<td align=center><font size=-2><b>$total_ppis[$m]<br>$percent%</font></b></td>";
1669       }
1670       $totals .= "</tr>";
1671
1672       $this->t->set_var('totals',$totals);
1673       $this->t->pfp('out','ppi_view_t');
1674       $this->save_sessiondata();
1675     }
1676
1677   function ppi_update()
1678     {
1679       $this->t->set_file(array('form' => 'ppi_update.tpl'));
1680       $this->t->set_block('form','interviewer_list','int_list');
1681       $this->t->set_block('form','add','addhandle');
1682       $this->t->set_block('form','edit','edithandle');
1683
1684       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_view'));
1685       $this->t->set_var('readonly','');
1686       $this->t->set_var('disabled','');
1687
1688       $action = get_var('action',array('GET','POST'));
1689       $companionship = get_var('companionship',array('GET','POST'));
1690       $interviewer = get_var('interviewer',array('GET','POST'));
1691       $name = get_var('name',array('GET','POST'));
1692       $ppi = get_var('ppi',array('GET','POST'));
1693       $elder = get_var('elder',array('GET','POST'));
1694       $aaronic = get_var('aaronic',array('GET','POST'));
1695       $date = get_var('date',array('GET','POST'));
1696       $notes = get_var('notes',array('GET','POST'));
1697       $eqpresppi = get_var('eqpresppi',array('GET','POST'));
1698
1699       $sql = "SELECT * FROM eq_district where valid=1 ORDER BY district ASC";
1700       $this->db->query($sql,__LINE__,__FILE__);
1701       while ($this->db->next_record())
1702         {
1703           $supervisor = $this->db->f('supervisor');
1704           $sql = "SELECT * FROM eq_elder WHERE elder=" . $supervisor;
1705           $this->db2->query($sql,__LINE__,__FILE__);
1706           $this->db2->next_record();
1707           $interviewer_name = $this->db2->f('name');
1708
1709           if($supervisor == $interviewer) {
1710             $this->t->set_var('interviewer',$supervisor . ' selected');
1711           } else {
1712             $this->t->set_var('interviewer',$interviewer);
1713           }
1714           $this->t->set_var('interviewer_name',$interviewer_name);
1715           $this->t->fp('int_list','interviewer_list',True);
1716         }
1717
1718       if($action == 'save')
1719         {
1720           $notes = $this->db->db_addslashes(get_var('notes',array('POST')));
1721           $this->db->query("UPDATE eq_ppi set " .
1722                            "   ppi='" . $ppi . "'" .
1723                     ", interviewer='" . $interviewer . "'" .
1724                           ", elder='" . $elder . "'" .
1725                         ", aaronic='" . $aaronic . "'" .
1726                            ", date='" . $date . "'" .
1727                           ", notes='" . $notes . "'" .
1728                       ", eqpresppi='" . $eqpresppi . "'" .
1729                            " WHERE ppi=" . $ppi,__LINE__,__FILE__);
1730           $this->ppi_view();
1731           return false;
1732         }
1733
1734       if($action == 'insert')
1735         {
1736           $notes = $this->db->db_addslashes(get_var('notes',array('POST')));
1737           $this->db->query("INSERT INTO eq_ppi (interviewer,elder,aaronic,date,notes,eqpresppi) "
1738                            . "VALUES ('" . $interviewer . "','" . $elder . "','" . $aaronic . "','"
1739                            . $date . "','" . $notes . "','" . $eqpresppi  ."')",__LINE__,__FILE__);
1740           $this->ppi_view();
1741           return false;
1742         }
1743
1744       if($action == 'add')
1745         {
1746           $this->t->set_var('cal_date',$this->jscal->input('date','','','','','','',$this->cal_options));
1747           $this->t->set_var('ppi', '');
1748           $this->t->set_var('interviewer', $interviewer);
1749           $this->t->set_var('name',$name);
1750           $this->t->set_var('elder',$elder);
1751           $this->t->set_var('aaronic',$aaronic);
1752           $this->t->set_var('date','');
1753           $this->t->set_var('notes','');
1754           $this->t->set_var('eqpresppi',$eqpresppi);
1755           $this->t->set_var('lang_done','Cancel');
1756           $this->t->set_var('lang_action','Adding New PPI');
1757           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_update&ppi='
1758                                                                 . $ppi . '&action=' . 'insert'));
1759         }
1760
1761       if($action == 'edit' || $action == 'view')
1762         {
1763           $sql = "SELECT * FROM eq_ppi WHERE ppi=".$ppi;
1764           $this->db->query($sql,__LINE__,__FILE__);
1765           $this->db->next_record();
1766           $this->t->set_var('ppi',$ppi);
1767           $this->t->set_var('name',$name);
1768           $this->t->set_var('interviewer', $this->db->f('interviewer'));
1769           $this->t->set_var('elder',$this->db->f('elder'));
1770           $this->t->set_var('aaronic',$this->db->f('aaronic'));
1771           $this->t->set_var('date',$this->db->f('date'));
1772           $this->t->set_var('notes',$this->db->f('notes'));
1773           $this->t->set_var('eqpresppi',$this->db->f('eqpresppi'));
1774         }
1775
1776       if($action == 'edit')
1777         {
1778           $this->t->set_var('cal_date',$this->jscal->input('date',$date,'','','','','',$this->cal_options));
1779           $this->t->set_var('lang_done','Cancel');
1780           $this->t->set_var('lang_action','Editing PPI');
1781           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_update&ppi='
1782                                                                 . $ppi . '&action=' . 'save'));
1783         }
1784
1785       if($action == 'view')
1786         {
1787           $date = $this->db->f('date');
1788           $this->t->set_var('cal_date','<input type=text size="10" maxlength="10" name="date" value="'.$date.'" readonly>');
1789           $this->t->set_var('readonly','READONLY');
1790           $this->t->set_var('disabled','DISABLED');
1791           $this->t->set_var('lang_done','Done');
1792           $this->t->set_var('lang_action','Viewing PPI');
1793           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_update&ppi='
1794                                                                 . $ppi . '&action=' . 'edit'));
1795         }
1796
1797       $this->t->set_var('lang_reset','Clear Form');
1798       $this->t->set_var('lang_add','Add PPI');
1799       $this->t->set_var('lang_save','Save Changes');
1800       $this->t->set_var('edithandle','');
1801       $this->t->set_var('addhandle','');
1802
1803       $this->t->pfp('out','form');
1804
1805       if($action == 'view') { $this->t->set_var('lang_save','Edit PPI'); }
1806       if($action == 'edit' || $action == 'view') { $this->t->pfp('addhandle','edit'); }
1807       if($action == 'add') { $this->t->pfp('addhandle','add'); }
1808
1809       $this->save_sessiondata();
1810     }
1811
1812   function vis_view()
1813     {
1814       $this->t->set_file(array('vis_view_t' => 'vis_view.tpl'));
1815       $this->t->set_block('vis_view_t','visit_list','list1');
1816       $this->t->set_block('vis_view_t','family_list','list2');
1817
1818       $this->t->set_var('lang_name','Family Name');
1819       $this->t->set_var('lang_date','Date');
1820
1821       $this->t->set_var('vis_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_view'));
1822       $this->t->set_var('vis_link_title','View Yearly Visits');
1823
1824       $this->t->set_var('schedule_vis_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_sched'));
1825       $this->t->set_var('schedule_vis_link_title','Schedule Yearly Visits');
1826
1827
1828       $sql = "SELECT * FROM eq_visit WHERE companionship=0 ORDER BY date DESC";
1829       $this->db->query($sql,__LINE__,__FILE__);
1830       $total_records = $this->db->num_rows();
1831
1832       $i = 0;
1833       while ($this->db->next_record())
1834         {
1835           $visit_list[$i]['visit'] = $this->db->f('visit');
1836           $visit_list[$i]['family'] = $this->db->f('family');
1837           $visit_list[$i]['date']  = $this->db->f('date');
1838           $i++;
1839         }
1840
1841       for ($i=0; $i < count($visit_list); $i++)
1842         {
1843           $this->nextmatchs->template_alternate_row_color(&$this->t);
1844
1845           $sql = "SELECT * FROM eq_family WHERE family=".$visit_list[$i]['family'];
1846           $this->db->query($sql,__LINE__,__FILE__);
1847           $this->db->next_record();
1848
1849           $this->t->set_var('family',$visit_list[$i]['family']);
1850           $this->t->set_var('family_name',$this->db->f('name'));
1851           $this->t->set_var('date',$visit_list[$i]['date']);
1852
1853           $link_data['menuaction'] = 'eq.eq.vis_update';
1854           $link_data['visit'] = $visit_list[$i]['visit'];
1855           $link_data['name'] = $this->db->f('name');
1856           $link_data['date'] = $visit_list[$i]['date'];
1857           $link_data['action'] = 'view';
1858           $this->t->set_var('view',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
1859           $this->t->set_var('lang_view','View');
1860
1861           $link_data['menuaction'] = 'eq.eq.vis_update';
1862           $link_data['visit'] = $visit_list[$i]['visit'];
1863           $link_data['name'] = $this->db->f('name');
1864           $link_data['date'] = $visit_list[$i]['date'];
1865           $link_data['action'] = 'edit';
1866           $this->t->set_var('edit',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
1867           $this->t->set_var('lang_edit','Edit');
1868
1869           $this->t->fp('list1','visit_list',True);
1870         }
1871
1872       // List the families that are available to record a visit against
1873       $sql = "SELECT * FROM eq_family WHERE valid=1";
1874       $this->db->query($sql,__LINE__,__FILE__);
1875       $total_records = $this->db->num_rows();
1876
1877       $i = 0;
1878       while ($this->db->next_record())
1879         {
1880           $family_names[$i] = $this->db->f('name');
1881           $family_ids[$i] = $this->db->f('family');
1882           $i++;
1883         } array_multisort($family_names, $family_ids);
1884
1885       for ($i=0; $i < count($family_names); $i++)
1886         {
1887           $link_data['menuaction'] = 'eq.eq.vis_update';
1888           $link_data['visit'] = '';
1889           $link_data['family'] = $family_ids[$i];
1890           $link_data['action'] = 'add';
1891           $link_data['name'] = $family_names[$i];
1892           $this->t->set_var('add',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
1893
1894           $this->t->set_var('name',$family_names[$i]);
1895           if(($i+1) % 3 == 0) { $this->t->set_var('table_sep',"</td></tr><tr>"); }
1896           else { $this->t->set_var('table_sep',"</td>"); }
1897           if(($i) % 3 == 0) { $this->nextmatchs->template_alternate_row_color(&$this->t); }
1898
1899           $this->t->fp('list2','family_list',True);
1900         }
1901
1902       $this->t->pfp('out','vis_view_t');
1903       $this->save_sessiondata();
1904     }
1905
1906   function vis_update()
1907     {
1908       $this->t->set_file(array('form' => 'vis_update.tpl'));
1909       $this->t->set_block('form','add','addhandle');
1910       $this->t->set_block('form','edit','edithandle');
1911
1912       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_view'));
1913       $this->t->set_var('readonly','');
1914       $this->t->set_var('disabled','');
1915
1916       $action = get_var('action',array('GET','POST'));
1917       $visit = get_var('visit',array('GET','POST'));
1918       $family = get_var('family',array('GET','POST'));
1919       $name = get_var('name',array('GET','POST'));
1920       $date = get_var('date',array('GET','POST'));
1921       $notes = get_var('notes',array('GET','POST'));
1922       $companionship = 0;
1923
1924       if($action == 'save')
1925         {
1926           $notes = $this->db->db_addslashes(get_var('notes',array('POST')));
1927           $this->db->query("UPDATE eq_visit set " .
1928                            "  date='" . $date . "'" .
1929                           ", notes='" . $notes . "'" .
1930                            " WHERE visit=" . $visit,__LINE__,__FILE__);
1931           $this->vis_view();
1932           return false;
1933         }
1934
1935       if($action == 'insert')
1936         {
1937           $notes = $this->db->db_addslashes(get_var('notes',array('POST')));
1938           $this->db->query("INSERT INTO eq_visit (family,companionship,date,notes) "
1939                            . "VALUES ('" . $family . "','" . $companionship . "','"
1940                            . $date . "','" . $notes . "')",__LINE__,__FILE__);
1941           $this->vis_view();
1942           return false;
1943         }
1944
1945       if($action == 'add')
1946         {
1947           $this->t->set_var('cal_date',$this->jscal->input('date','','','','','','',$this->cal_options));
1948           $this->t->set_var('family', $family);
1949           $this->t->set_var('visit', '');
1950           $this->t->set_var('name', $name);
1951           $this->t->set_var('date','');
1952           $this->t->set_var('notes','');
1953           $this->t->set_var('lang_done','Cancel');
1954           $this->t->set_var('lang_action','Adding New Visit');
1955           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_update&family='
1956                                                                 . $family . '&action=' . 'insert'));
1957         }
1958
1959       if($action == 'edit' || $action == 'view')
1960         {
1961           $sql = "SELECT * FROM eq_visit WHERE visit=".$visit;
1962           $this->db->query($sql,__LINE__,__FILE__);
1963           $this->db->next_record();
1964           $this->t->set_var('visit',$visit);
1965           $this->t->set_var('name',$name);
1966           $this->t->set_var('family', $family);
1967           $this->t->set_var('date',$this->db->f('date'));
1968           $this->t->set_var('notes',$this->db->f('notes'));
1969         }
1970
1971       if($action == 'edit')
1972         {
1973           $this->t->set_var('cal_date',$this->jscal->input('date',$date,'','','','','',$this->cal_options));
1974           $this->t->set_var('lang_done','Cancel');
1975           $this->t->set_var('lang_action','Editing Visit');
1976           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_update&visit='
1977                                                                 . $visit . '&action=' . 'save'));
1978         }
1979
1980       if($action == 'view')
1981         {
1982           $date = $this->db->f('date');
1983           $this->t->set_var('cal_date','<input type=text size="10" maxlength="10" name="date" value="'.$date.'" readonly>');
1984           $this->t->set_var('readonly','READONLY');
1985           $this->t->set_var('disabled','DISABLED');
1986           $this->t->set_var('lang_done','Done');
1987           $this->t->set_var('lang_action','Viewing Visit');
1988           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_update&visit='
1989                                                                 . $visit . '&action=' . 'edit'));
1990         }
1991
1992       $this->t->set_var('lang_reset','Clear Form');
1993       $this->t->set_var('lang_add','Add Visit');
1994       $this->t->set_var('lang_save','Save Changes');
1995       $this->t->set_var('edithandle','');
1996       $this->t->set_var('addhandle','');
1997
1998       $this->t->pfp('out','form');
1999
2000       if($action == 'view') { $this->t->set_var('lang_save','Edit Visit'); }
2001       if($action == 'edit' || $action == 'view') { $this->t->pfp('addhandle','edit'); }
2002       if($action == 'add') { $this->t->pfp('addhandle','add'); }
2003
2004       $this->save_sessiondata();
2005     }
2006
2007   function att_view()
2008     {
2009       $this->t->set_file(array('att_view_t' => 'att_view.tpl'));
2010       $this->t->set_block('att_view_t','act_list','list');
2011
2012       $this->t->set_block('att_view_t','month_list','list1');
2013       $this->t->set_block('att_view_t','header_list','list2');
2014       $this->t->set_block('att_view_t','elder_list','list3');
2015
2016       $this->t->set_var('linkurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.att_view'));
2017       $num_months = get_var('num_months',array('GET','POST'));
2018       if($num_months == '') { $num_months = $this->default_att_num_months; }
2019       $this->t->set_var('num_months',$num_months);
2020       $this->t->set_var('lang_filter','Filter');
2021       if($num_months == 1) { $this->t->set_var('lang_num_months','Month of History'); }
2022       else {  $this->t->set_var('lang_num_months','Months of History'); }
2023
2024       $sql = "SELECT * FROM eq_elder where valid=1";
2025       $this->db->query($sql,__LINE__,__FILE__);
2026       $i=0;
2027       while ($this->db->next_record())
2028         {
2029           $elder_name[$i] = $this->db->f('name');
2030           $elder_id[$i] = $this->db->f('elder');
2031           $i++;
2032         }
2033       array_multisort($elder_name, $elder_id);
2034
2035
2036       // Create a list of sunday dates for a window of 3 months back and current month
2037       $i=0;
2038       $last_time = 0;
2039       $found_sunday = 0;
2040       $sunday_list[0]['date'] = date("Y-m-d", mktime(0, 0, 0, date("m")-$num_months, 1, date("y")));
2041       $last_date = explode("-",$sunday_list[0]['date']);
2042       $last_time = mktime(0, 0, 0, $last_date[1], $last_date[2], $last_date[0]);
2043       $time_limit = mktime(0, 0, 0, date("m"), date("t"), date("y"));
2044       while($last_time < $time_limit)
2045       {
2046         $day = date("w",$last_time);
2047         if(date("w",$last_time) == 0) {
2048           $sunday_list[$i]['date'] = date("Y-m-d", $last_time);
2049           $last_date = explode("-",$sunday_list[$i]['date']);
2050           $last_time = mktime(0, 0, 0, $last_date[1], $last_date[2], $last_date[0]);
2051           $sunday_list[$i]['day'] = $last_date[2];
2052           $sunday_list[$i]['month'] = date("M",$last_time);
2053           $sunday_list[$i]['year'] = $last_date[0];
2054           $found_sunday = 1;
2055         }
2056         $last_time += 90000;
2057         if($found_sunday) { $i++; $found_sunday=0; }
2058       }
2059
2060       $total_elders = count($elder_id);
2061       $old_month=$sunday_list[0]['month']; $span=0;
2062       for ($i=0; $i < count($sunday_list); $i++) {
2063         $date = $sunday_list[$i]['date'];
2064         $this->t->set_var('date',$sunday_list[$i]['date']);
2065         $this->t->set_var('day',$sunday_list[$i]['day']);
2066         if(($old_month != $sunday_list[$i]['month']) || $i == count($sunday_list)-1) {
2067           if($i == count($sunday_list)-1) { $span++; }
2068           $cur_month = $sunday_list[$i]['month'];
2069           $old_month = $sunday_list[$i]['month'];
2070           $link_data['menuaction'] = 'eq.eq.att_update';
2071           $link_data['month'] = $sunday_list[$i-1]['month'];
2072           $link_data['year'] = $sunday_list[$i-1]['year'];
2073           $link_data['action'] = 'update_month';
2074           $cur_month = $sunday_list[$i-1]['month'];
2075           $cur_year = $sunday_list[$i-1]['year'];
2076           $header_row .= "<th><font size=-3>$cur_month&nbsp;$cur_year</font></th>";
2077           $this->t->set_var('update_month',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
2078           $this->t->set_var('month',$sunday_list[$i-1]['month']);
2079           $this->t->set_var('year',$sunday_list[$i-1]['year']);
2080           $this->t->set_var('span',$span); $span=0;
2081           $this->t->fp('list1','month_list',True);
2082         } $span++;
2083       }
2084       $this->t->set_var('total_elders',$total_elders);
2085       $this->t->set_var('header_row',$header_row);
2086
2087       $elder_width=200; $att_width=25; $total_width=$elder_width;
2088       for ($i=0; $i < count($sunday_list); $i++) {
2089         $link_data['menuaction'] = 'eq.eq.att_update';
2090         $link_data['month'] = $sunday_list[$i]['month'];
2091         $link_data['year'] = $sunday_list[$i]['year'];
2092         $link_data['day'] = $sunday_list[$i]['day'];
2093         $link_data['date'] = $sunday_list[$i]['date'];
2094         $link_data['action'] = 'update_day';
2095         $this->t->set_var('update_day',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
2096         $this->t->set_var('date',$sunday_list[$i]['date']);
2097         $this->t->set_var('day',$sunday_list[$i]['day']);
2098         $this->t->set_var('month',$sunday_list[$i]['month']);
2099         $this->t->set_var('year',$sunday_list[$i]['year']);
2100         $this->t->fp('list2','header_list',True);
2101         $total_width += $att_width;
2102       }
2103
2104       for ($i=0; $i < count($elder_id); $i++) {
2105         $att_table = "";
2106         $this->nextmatchs->template_alternate_row_color(&$this->t);
2107         $this->t->set_var('elder_name',$elder_name[$i]);
2108         #print "checking for elder: " . $elder_id[$i] . "<br>";
2109         for ($j=0; $j < count($sunday_list); $j++) {
2110           #print "checking for date: " .  $sunday_list[$j]['date'] . "<br>";
2111           #print "SELECT * FROM eq_attendance WHERE date='"
2112           #  . $sunday_list[$j]['date'] . "' AND elder=" . $elder_id[$i] . "<br>";
2113           $sql = "SELECT * FROM eq_attendance WHERE date='"
2114              . $sunday_list[$j]['date'] . "' AND elder=" . $elder_id[$i];
2115           $this->db->query($sql,__LINE__,__FILE__);
2116           if($this->db->next_record()) {
2117             $cur_month = $sunday_list[$j]['month'];
2118             if($attended[$i][$cur_month] != 1) {
2119               $attended[$i][$cur_month]=1;
2120               $attendance[$cur_month]++;
2121             }
2122             $att_table .= '<td align=center><img src="checkmark.gif"></td>';
2123           } else {
2124             $att_table .= '<td>&nbsp;</td>';
2125           }
2126         }
2127         $this->t->set_var('att_table',$att_table);
2128         $this->t->fp('list3','elder_list',True);
2129       }
2130       $this->t->set_var('total_width',$total_width);
2131       $this->t->set_var('elder_width',$elder_width);
2132       $this->t->set_var('att_width',$att_width);
2133
2134       # Now calculate attendance for these months
2135       $attendance_str = "";
2136       $nonattendance_str = "";
2137       $aveattendance_str = "";
2138       $avenonattendance_str = "";
2139       $num_months=0;
2140       $ave_total_attended=0;
2141       foreach($attendance as $att => $value) {
2142         $total_attended = $attendance[$att];
2143         $ave_total_attended += $attendance[$att]; $num_months++;
2144         $percent = ceil(($total_attended / $total_elders)*100);
2145         $attendance_str.="<td align=center><font size=-2><b>$total_attended ($percent%)</b></font></td>";
2146         $total_nonattended = $total_elders - $total_attended;
2147         $percent = ceil(($total_nonattended / $total_elders)*100);
2148         $nonattendance_str.="<td align=center><font size=-2><b>$total_nonattended ($percent%)</b></font></td>";
2149
2150         $total_attended = ceil(($ave_total_attended / $num_months));
2151         $percent = ceil(($total_attended / $total_elders)*100);
2152         $aveattendance_str .= "<td align=center><font size=-2><b>$total_attended ($percent%)</b></font></td>";
2153         $total_attended = $total_elders - ceil(($ave_total_attended / $num_months));
2154         $percent = ceil(($total_attended / $total_elders)*100);
2155         $avenonattendance_str .= "<td align=center><font size=-2><b>$total_attended ($percent%)</b></font></td>";
2156       }
2157
2158       $this->t->set_var('attendance',$attendance_str);
2159       $this->t->set_var('aveattendance',$aveattendance_str);
2160       $this->t->set_var('nonattendance',$nonattendance_str);
2161       $this->t->set_var('avenonattendance',$avenonattendance_str);
2162
2163       $this->t->pfp('out','att_view_t');
2164       $this->save_sessiondata();
2165     }
2166
2167   function att_update()
2168     {
2169       $monthnum['Jan']=1; $monthnum['Feb']=2; $monthnum['Mar']=3; $monthnum['Apr']=4;
2170       $monthnum['May']=5; $monthnum['Jun']=6; $monthnum['Jul']=7; $monthnum['Aug']=8;
2171       $monthnum['Sep']=9; $monthnum['Oct']=10; $monthnum['Nov']=11; $monthnum['Dec']=12;
2172
2173       $this->t->set_file(array('form' => 'att_update.tpl'));
2174       $this->t->set_block('form','edit','edithandle');
2175
2176       $this->t->set_block('form','month_list','list1');
2177       $this->t->set_block('form','header_list','list2');
2178       $this->t->set_block('form','elder_list','list3');
2179
2180       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.att_view'));
2181
2182       $action = get_var('action',array('GET','POST'));
2183       $month = get_var('month',array('GET','POST'));
2184       $year = get_var('year',array('GET','POST'));
2185       $day = get_var('day',array('GET','POST'));
2186       $date = get_var('date',array('GET','POST'));
2187
2188       if($action == 'save_month' || $action == 'save_day')
2189         {
2190            $new_data = get_var('elders_attended',array('POST'));
2191            $month = $monthnum[$month]; if($month < 10) { $month = "0" . $month; }
2192
2193            if($action == 'save_month') {
2194              $this->db->query("DELETE from eq_attendance where date LIKE '".$year."-".$month."-%'",__LINE__,__FILE__);
2195            }
2196
2197            if($action == 'save_day') {
2198              $this->db->query("DELETE from eq_attendance where date LIKE '".$year."-".$month."-".$day."'",__LINE__,__FILE__);
2199            }
2200
2201            foreach ($new_data as $data)
2202            {
2203               $data_array = explode("-",$data);
2204               $elder = $data_array[0];
2205               $date  = "$data_array[1]-$data_array[2]-$data_array[3]";
2206               $this->db->query("INSERT INTO eq_attendance (elder,date) "
2207                                . "VALUES (" . $elder . ",'". $date . "')",__LINE__,__FILE__);
2208            }
2209
2210          $this->att_view();
2211          return false;
2212         }
2213
2214       $sql = "SELECT * FROM eq_elder where valid=1";
2215       $this->db->query($sql,__LINE__,__FILE__);
2216       $i=0;
2217       while ($this->db->next_record())
2218         {
2219           $elder_name[$i] = $this->db->f('name');
2220           $elder_id[$i] = $this->db->f('elder');
2221           $i++;
2222         }
2223       array_multisort($elder_name, $elder_id);
2224
2225       if($action == 'update_month')
2226       {
2227         $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.att_update&action=save_month'));
2228         $i=0;
2229         $last_time = 0;
2230         $found_sunday = 0;
2231         $sunday_list[0]['date'] = date("Y-m-d", mktime(0, 0, 0, $monthnum[$month], 1, $year));
2232         $last_date = explode("-",$sunday_list[0]['date']);
2233         $last_time = mktime(0, 0, 0, $last_date[1], $last_date[2], $last_date[0]);
2234         $time_limit = mktime(0, 0, 0, $monthnum[$month], 31, $year);
2235         while($last_time <= $time_limit)
2236         {
2237           $day = date("w",$last_time);
2238           if(date("w",$last_time) == 0) {
2239             $sunday_list[$i]['date'] = date("Y-m-d", $last_time);
2240             $last_date = explode("-",$sunday_list[$i]['date']);
2241             $last_time = mktime(0, 0, 0, $last_date[1], $last_date[2], $last_date[0]);
2242             $sunday_list[$i]['day'] = $last_date[2];
2243             $sunday_list[$i]['month'] = date("M",$last_time);
2244             $sunday_list[$i]['year'] = $last_date[0];
2245             $found_sunday = 1;
2246           }
2247           $last_time += 90000;
2248           if($found_sunday) { $i++; $found_sunday=0; }
2249         }
2250
2251         $this->t->set_var('span', $i);
2252         $this->t->set_var('month',$sunday_list[$i-1]['month']);
2253         $this->t->set_var('year',$sunday_list[$i-1]['year']);
2254         $this->t->fp('list1','month_list',True);
2255         $elder_width=200; $att_width=25; $total_width=$elder_width;
2256         for ($i=0; $i < count($sunday_list); $i++) {
2257           $link_data['menuaction'] = 'eq.eq.att_update';
2258           $link_data['month'] = $sunday_list[$i]['month'];
2259           $link_data['year'] = $sunday_list[$i]['year'];
2260           $link_data['day'] = $sunday_list[$i]['day'];
2261           $link_data['date'] = $sunday_list[$i]['date'];
2262           $link_data['action'] = 'update_day';
2263           $this->t->set_var('update_day',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
2264           $this->t->set_var('date',$sunday_list[$i]['date']);
2265           $this->t->set_var('day',$sunday_list[$i]['day']);
2266           $this->t->set_var('month',$sunday_list[$i]['month']);
2267           $this->t->set_var('year',$sunday_list[$i]['year']);
2268           $this->t->fp('list2','header_list',True);
2269           $total_width += $att_width;
2270         }
2271       }
2272
2273       if($action == 'update_day')
2274       {
2275         $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.att_update&action=save_day'));
2276         $sunday_list[0]['date'] = date("Y-m-d", mktime(0, 0, 0, $monthnum[$month], $day, $year));
2277         $this->t->set_var('month',$month);
2278         $this->t->set_var('year',$year);
2279         $this->t->fp('list1','month_list',True);
2280         $this->t->set_var('date',$date);
2281         $this->t->set_var('day',$day);
2282         $this->t->set_var('month',$month);
2283         $this->t->set_var('year',$year);
2284         $this->t->fp('list2','header_list',True);
2285       }
2286
2287       for ($i=0; $i < count($elder_id); $i++) {
2288         $att_table = "";
2289         $this->nextmatchs->template_alternate_row_color(&$this->t);
2290         $this->t->set_var('elder_name',$elder_name[$i]);
2291         for ($j=0; $j < count($sunday_list); $j++) {
2292           $sql = "SELECT * FROM eq_attendance WHERE date='"
2293              . $sunday_list[$j]['date'] . "' AND elder=" . $elder_id[$i];
2294           $this->db->query($sql,__LINE__,__FILE__);
2295           $value = $elder_id[$i] . "-" . $sunday_list[$j]['date'];
2296           if($this->db->next_record()) {
2297             $att_table .= '<td align=center><input type="checkbox" name="elders_attended[]" value="'.$value.'" checked></td>';
2298           } else {
2299             $att_table .= '<td align=center><input type="checkbox" name="elders_attended[]" value="'.$value.'"></td>';
2300           }
2301         }
2302         $this->t->set_var('att_table',$att_table);
2303         $this->t->fp('list3','elder_list',True);
2304       }
2305
2306       $this->t->set_var('lang_done', 'Cancel');
2307       $this->t->set_var('lang_reset','Clear Form');
2308       $this->t->set_var('lang_save','Save Changes');
2309
2310       $this->t->pfp('out','form');
2311       $this->t->pfp('addhandle','edit');
2312
2313       $this->save_sessiondata();
2314     }
2315
2316   function dir_view()
2317     {
2318       $this->t->set_file(array('dir_view_t' => 'dir_view.tpl'));
2319       $this->t->set_block('dir_view_t','dir_list','list');
2320
2321       $sql = "SELECT * FROM eq_parent where valid=1 ORDER BY name ASC";
2322       $this->db->query($sql,__LINE__,__FILE__);
2323       $i=0;
2324       while ($this->db->next_record())
2325         {
2326           $parent[$i]['id'] = $this->db->f('parent');
2327           $parent[$i]['name'] = $this->db->f('name');
2328           $parent[$i]['phone'] = $this->db->f('phone');
2329           $parent[$i]['address'] = $this->db->f('address');
2330           $i++;
2331         }
2332
2333       for ($i=0; $i < count($parent); $i++)
2334       {
2335         $name = $parent[$i]['name'];
2336         $phone = $parent[$i]['phone'];
2337         $address = $parent[$i]['address'];
2338         $this->t->set_var('name', $name);
2339         $this->t->set_var('address', $address);
2340         $this->t->set_var('phone', $phone);
2341         $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
2342         $this->t->set_var('tr_color',$tr_color);
2343         $this->t->fp('list','dir_list',True);
2344         //print "$phone $name $address<br>";
2345       }
2346       $this->t->pfp('out','dir_view_t');
2347       $this->save_sessiondata();
2348     }
2349
2350   function org_view()
2351     {
2352       $this->t->set_file(array('org_view_t' => 'org_view.tpl'));
2353       $this->t->set_block('org_view_t','calling_list','list1');
2354       $this->t->set_block('org_view_t','org_list','list2');
2355
2356       # Display a list ordered alphabetically
2357       $sql = "SELECT * FROM eq_calling ORDER BY name ASC";
2358       $this->db->query($sql,__LINE__,__FILE__);
2359       $i=0;
2360       while ($this->db->next_record())
2361         {
2362           $calling[$i]['id'] = $this->db->f('indiv_id');
2363           $calling[$i]['name'] = $this->db->f('name');
2364           $calling[$i]['position'] = $this->db->f('position');
2365           $calling[$i]['sustained'] = $this->db->f('sustained');
2366           $calling[$i]['organization'] = $this->db->f('organization');
2367           $i++;
2368         }
2369       for ($i=0; $i < count($calling); $i++)
2370       {
2371         $name = $calling[$i]['name'];
2372         $position = $calling[$i]['position'];
2373         $sustained = $calling[$i]['sustained'];
2374         $organization = $calling[$i]['organization'];
2375         $this->t->set_var('name', $name);
2376         $this->t->set_var('position', $position);
2377         $this->t->set_var('sustained', $sustained);
2378         $this->t->set_var('organization', $organization);
2379         $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
2380         $this->t->set_var('tr_color',$tr_color);
2381         $this->t->fp('list1','calling_list',True);
2382       }
2383
2384       # Display a list ordered by organization
2385       $sql = "SELECT * FROM eq_calling ORDER BY sequence ASC";
2386       $this->db->query($sql,__LINE__,__FILE__);
2387       $i=0;
2388       while ($this->db->next_record())
2389         {
2390           $calling[$i]['id'] = $this->db->f('indiv_id');
2391           $calling[$i]['name'] = $this->db->f('name');
2392           $calling[$i]['position'] = $this->db->f('position');
2393           $calling[$i]['sustained'] = $this->db->f('sustained');
2394           $calling[$i]['organization'] = $this->db->f('organization');
2395           $i++;
2396         }
2397       for ($i=0; $i < count($calling); $i++)
2398       {
2399         $name = $calling[$i]['name'];
2400         $position = $calling[$i]['position'];
2401         $sustained = $calling[$i]['sustained'];
2402         $organization = $calling[$i]['organization'];
2403         $this->t->set_var('name', $name);
2404         $this->t->set_var('position', $position);
2405         $this->t->set_var('sustained', $sustained);
2406         $this->t->set_var('organization', $organization);
2407         $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
2408         $this->t->set_var('tr_color',$tr_color);
2409         $this->t->fp('list2','org_list',True);
2410       }
2411
2412       $this->t->pfp('out','org_view_t');
2413       $this->save_sessiondata();
2414     }
2415
2416   function schedule()
2417     {
2418       $this->t->set_file(array('sched_t' => 'schedule.tpl'));
2419       $this->t->set_block('sched_t','district_list','list');
2420
2421       $action = get_var('action',array('GET','POST'));
2422
2423       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.schedule&action=save'));
2424       $this->t->set_var('title','EQ Scheduling Tool');
2425
2426       $this->t->set_var('lang_save','Save Schedule');
2427       $this->t->set_var('lang_reset','Cancel');
2428
2429       $this->t->set_var('schedule_ppi_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_sched'));
2430       $this->t->set_var('schedule_ppi_link_title','Schedule Yearly PPIs');
2431
2432       $this->t->set_var('schedule_vis_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_sched'));
2433       $this->t->set_var('schedule_vis_link_title','Schedule Yearly Visits');
2434
2435       $date_width=150; $time_width=200; $elder_width=200; $family_width=200;
2436       $table_width=$date_width + $time_width + $elder_width + $family_width;
2437       $header_row = "<th width=$date_width><font size=-2>Date</th>";
2438       $header_row.= "<th width=$time_width><font size=-2>Time</th>";
2439       $header_row.= "<th width=$elder_width><font size=-2>Elder</th>";
2440       $header_row.= "<th width=$family_width><font size=-2>Family</th>";
2441       $table_data = "";
2442
2443       if($action == 'save')
2444         {
2445           $new_data = get_var('sched',array('POST'));
2446           foreach ($new_data as $district_array)
2447            {
2448              foreach ($district_array as $entry)
2449                {
2450                  $district = $entry['district'];
2451                  $appointment = $entry['appointment'];
2452                  $date = $entry['date'];
2453                  $hour = $entry['hour'];
2454                  $minute = $entry['minute'];
2455                  $pm = $entry['pm'];
2456                  $elder = $entry['elder'];
2457                  $family = $entry['family'];
2458                  if($pm) { $hour = $hour + 12; }
2459                  $time = $hour.':'.$minute.':'.'00';
2460
2461                  // Update an existing appointment
2462                  if($appointment != 0)
2463                    {
2464                      $this->db->query("UPDATE eq_appointment set" .
2465                               " family=" . $family .
2466                               " ,elder=" . $elder .
2467                               " ,date='" . $date . "'" .
2468                               " ,time='" . $time . "'" .
2469                               " WHERE appointment=" . $appointment,__LINE__,__FILE__);
2470
2471                      //print "updating entry: appt=$appointment date: $date time: $time elder: $elder family: $family<br>";
2472                    }
2473
2474                  // Add a new appointment
2475                  else if(($appointment == 0) && ($date != "") && ($time != ""))
2476                    {
2477                      $this->db->query("INSERT INTO eq_appointment (appointment,district,family,elder,date,time) "
2478                            . "VALUES ('" . $appointment . "','" . $district . "','" . $family . "','"
2479                            . $elder . "','" . $date . "','" . $time  ."')",__LINE__,__FILE__);
2480
2481                      //print "adding entry: appt=$appointment date: $date time: $time elder: $elder family: $family<br>";
2482                    }
2483                }
2484            }
2485
2486           $take_me_to_url = $GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.schedule');
2487           Header('Location: ' . $take_me_to_url);
2488         }
2489
2490       $sql = "SELECT * FROM eq_district where district != 0 ORDER BY district ASC";
2491       $this->db->query($sql,__LINE__,__FILE__);
2492       $i=0;
2493       while ($this->db->next_record())
2494         {
2495           $districts[$i]['district'] = $this->db->f('district');
2496           $districts[$i]['name'] = $this->db->f('name');
2497           $districts[$i]['supervisor'] = $this->db->f('supervisor');
2498           $i++;
2499         }
2500
2501       $sql = "SELECT * FROM eq_elder where valid=1 ORDER BY elder ASC";
2502       $this->db->query($sql,__LINE__,__FILE__);
2503       $i=0;
2504       while ($this->db->next_record())
2505         {
2506           $elder_id[$i] = $this->db->f('elder');
2507           $elder_name[$i] = $this->db->f('name');
2508           $elder_phone[$elder_id[$i]] = $this->db->f('phone');
2509           $i++;
2510         }
2511       array_multisort($elder_name, $elder_id);
2512
2513       $sql = "SELECT * FROM eq_family where valid=1 and elder_id != 0 ORDER BY name ASC";
2514       $this->db->query($sql,__LINE__,__FILE__);
2515       $i=0;
2516       while ($this->db->next_record())
2517         {
2518           $family_id[$i] = $this->db->f('family');
2519           $family_name[$i] = $this->db->f('name');
2520           $i++;
2521         }
2522       array_multisort($family_name, $family_id);
2523
2524       for ($i=0; $i < count($districts); $i++) {
2525         $district = $districts[$i]['district'];
2526         $this->t->set_var('district_number',$districts[$i]['district']);
2527         $this->t->set_var('district_name',$districts[$i]['name']);
2528         $supervisor = $districts[$i]['supervisor'];
2529         $table_data="";
2530
2531         // query the database for all the appointments
2532         $sql = "SELECT * FROM eq_appointment where district=$district and date>=CURDATE() ORDER BY date ASC, time ASC";
2533         $this->db->query($sql,__LINE__,__FILE__);
2534
2535         // Prefill any existing appointment slots
2536         while ($this->db->next_record())
2537           {
2538             $appointment = $this->db->f('appointment');
2539             $elder = $this->db->f('elder');
2540             $family = $this->db->f('family');
2541
2542             $date = $this->db->f('date');
2543             $date_array = explode("-",$date);
2544             $year = $date_array[0]; $month = $date_array[1]; $day = $date_array[2];
2545             $day_string = date("l d-M-Y", mktime(0,0,0,$month,$day,$year));
2546
2547             $time = $this->db->f('time');
2548             $time_array = explode(":",$time);
2549             $hour = $time_array[0];
2550             $minute = $time_array[1];
2551             $pm = 0;
2552             if($hour > 12) { $pm=1; $hour = $hour - 12; }
2553             $time_string = date("g:i a", mktime($time_array[0], $time_array[1], $time_array[2]));
2554
2555             $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
2556
2557             // Date selection
2558             $table_data.= '<td align=left>';
2559             $table_data.= $this->jscal->input('sched['.$district.']['.$appointment.'][date]',$date,'','','','','',$this->cal_options);
2560             $table_data.= '</td>';
2561
2562             // Hour & Minutes selection
2563             $table_data.= "<td align=center>";
2564             $table_data.= '<select name=sched['.$district.']['.$appointment.'][hour]>';
2565             foreach(range(1,12) as $num) {
2566               if($hour == $num) { $selected[$num] = 'selected="selected"'; } else { $selected[$num] = ''; }
2567               $table_data.= '<option value='.$num.' '.$selected[$num].'>'.$num.'</option>';
2568             }
2569             $table_data.= '</select>';
2570             $table_data.= '&nbsp;:&nbsp;';
2571             $table_data.= '<select name=sched['.$district.']['.$appointment.'][minute]>';
2572             foreach(range(0,3) as $num) {
2573               $num = $num * 15; if($num == 0) { $num = "00"; }
2574               if($minute == $num) { $selected[$num] = 'selected="selected"'; } else { $selected[$num] = ''; }
2575               $table_data.= '<option value='.$num.' '.$selected[$num].'>'.$num.'</option>';
2576             }
2577             $table_data.= '</select>';
2578             $table_data.= '<select name=sched['.$district.']['.$appointment.'][pm]>';
2579             if($pm == 0) { $table_data.= '<option value=0 selected>am</option>'; $table_data.= '<option value=1>pm</option>'; }
2580             else { $table_data.= '<option value=0>am</option>'; $table_data.= '<option value=1 selected>pm</option>'; }
2581             $table_data.= '</select>';
2582             $table_data.= "</td>";
2583
2584             // Elder drop down list (for PPIs)
2585             $table_data.= '<td align=center><select name=sched['.$district.']['.$appointment.'][elder]>';
2586             $table_data.= '<option value=0></option>';
2587             for ($j=0; $j < count($elder_id); $j++) {
2588               $id = $elder_id[$j];
2589               $name = $elder_name[$j];
2590               if($elder_id[$j] == $elder) { $selected[$id] = 'selected="selected"'; } else { $selected[$id] = ''; }
2591               $table_data.= '<option value='.$id.' '.$selected[$id].'>'.$name.'</option>';
2592             }
2593             $table_data.='</select></td>';
2594
2595             // Family drop down list (for Visits)
2596             $table_data.= '<td align=center><select name=sched['.$district.']['.$appointment.'][family]>';
2597             $table_data.= '<option value=0></option>';
2598             for ($j=0; $j < count($elder_id); $j++) {
2599               $id = $family_id[$j];
2600               $name = $family_name[$j];
2601               if($family_id[$j] == $family) { $selected[$id] = 'selected="selected"'; } else { $selected[$id] = ''; }
2602               $table_data.= '<option value='.$id.' '.$selected[$id].'>'.$name.' Family</option>';
2603             }
2604             $table_data.='</select></td>';
2605
2606             $table_data.= '<input type=hidden name="sched['.$district.']['.$appointment.'][appointment]" value="'.$appointment.'">';
2607             $table_data.= '<input type=hidden name="sched['.$district.']['.$appointment.'][district]" value="'.$district.'">';
2608
2609             $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
2610             $this->t->set_var('tr_color',$tr_color);
2611
2612           }
2613
2614         // Create blank appointment slot
2615         $appointment = 0;
2616         $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
2617
2618         // Date selection
2619         $table_data.= '<td align=left>';
2620         $table_data.= $this->jscal->input('sched['.$district.']['.$appointment.'][date]','','','','','','',$this->cal_options);
2621         $table_data.= '</td>';
2622
2623         // Time selection
2624         $table_data.= "<td align=center>";
2625         $table_data.= '<select name=sched['.$district.']['.$appointment.'][hour]>';
2626         $table_data.= '<option value=""></option>';
2627         foreach(range(1,12) as $num) {
2628           $table_data.= '<option value='.$num.' '.$selected[$num].'>'.$num.'</option>';
2629         }
2630         $table_data.= '</select>';
2631         $table_data.= '&nbsp;:&nbsp;';
2632         $table_data.= '<select name=sched['.$district.']['.$appointment.'][minute]>';
2633         $table_data.= '<option value=""></option>';
2634         foreach(range(0,3) as $num) {
2635           $num = $num * 15; if($num == 0) { $num = "00"; }
2636           $table_data.= '<option value='.$num.'>'.$num.'</option>';
2637         }
2638         $table_data.= '</select>';
2639         $table_data.= '<select name=sched['.$district.']['.$appointment.'][pm]>';
2640         $table_data.= '<option value=""></option>';
2641         $table_data.= '<option value=0>am</option>';
2642         $table_data.= '<option value=1>pm</option>';
2643         $table_data.= '</select>';
2644         $table_data.= "</td>";
2645
2646         // Elder drop down list
2647         $table_data.= '<td align=center><select name=sched['.$district.']['.$appointment.'][elder]>';
2648         $table_data.= '<option value=0></option>';
2649         for ($j=0; $j < count($elder_id); $j++) {
2650           $id = $elder_id[$j];
2651           $name = $elder_name[$j];
2652           $table_data.= '<option value='.$id.'>'.$name.'</option>';
2653         }
2654         $table_data.='</select></td>';
2655
2656         // Family drop down list
2657         $table_data.= '<td align=center><select name=sched['.$district.']['.$appointment.'][family]>';
2658         $table_data.= '<option value=0></option>';
2659         for ($j=0; $j < count($elder_id); $j++) {
2660           $id = $family_id[$j];
2661           $name = $family_name[$j];
2662           $table_data.= '<option value='.$id.'>'.$name.' Family</option>';
2663         }
2664         $table_data.='</select></td>';
2665
2666         $table_data.= '<input type=hidden name="sched['.$district.']['.$appointment.'][appointment]" value="'.$appointment.'">';
2667         $table_data.= '<input type=hidden name="sched['.$district.']['.$appointment.'][district]" value="'.$district.'">';
2668
2669         $this->t->set_var('table_data',$table_data);
2670         $this->t->set_var('header_row',$header_row);
2671         $this->t->set_var('table_width',$table_width);
2672         $this->t->fp('list','district_list',True);
2673
2674       }
2675
2676       $this->t->pfp('out','sched_t');
2677       $this->save_sessiondata();
2678     }
2679
2680   function admin()
2681     {
2682       $this->t->set_file(array('admin_t' => 'admin.tpl'));
2683       $this->t->set_block('admin_t','upload','uploadhandle');
2684       $this->t->set_block('admin_t','admin','adminhandle');
2685       $this->t->set_block('admin_t','cmd','cmdhandle');
2686
2687       $this->t->set_var('upload_action',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.admin&action=upload'));
2688
2689       $action = get_var('action',array('GET','POST'));
2690
2691       $this->t->pfp('out','admin_t');
2692
2693       if($action == 'upload')
2694         {
2695           $target_path = $this->upload_target_path . basename( $_FILES['uploadedfile']['name']);
2696
2697           if((($_FILES['uploadedfile']['type'] == "application/zip") ||
2698               ($_FILES['uploadedfile']['type'] == "application/x-zip-compressed") ||
2699               ($_FILES['uploadedfile']['type'] == "application/x-zip") ||
2700               ($_FILES['uploadedfile']['type'] == "application/octet-stream")) &&
2701              (move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path))) {
2702             $uploadstatus = "<b>The following file was uploaded successfully: </b><br><br>";
2703             $uploadstatus.= "Filename : " . $_FILES['uploadedfile']['name'] . "<br>";
2704             $uploadstatus.= "Type     : " . $_FILES['uploadedfile']['type'] . "<br>";
2705             $uploadstatus.= "Size     : " . $_FILES['uploadedfile']['size'] . "<br>";
2706             $this->t->set_var('uploadstatus',$uploadstatus);
2707             $this->t->pfp('uploadhandle','upload');
2708             $this->t->set_var('uploadhandle','');
2709             print "<table border=1 width=80%><tr><td>\n<pre>";
2710
2711             # make a directory for this data to be stored in
2712             $date="data_" . date("Y_m_d");
2713             $data_dir = $this->upload_target_path . $date;
2714             print "-> Making the data directory: $date<br>\n";
2715             exec('mkdir ' . $data_dir . ' 2>&1', $result, $return_code);
2716             if($return_code != 0) {
2717               print implode('\n',$result) . "<br>";
2718               print "<b><font color=red>";
2719               print "-E- Unable to create the data directory. Aborting import.";
2720               print "</font></b>";
2721               return 0;
2722             }
2723
2724             # move the file uploaded into this directory
2725             print "-> Moving the uploaded file into the data dir<br>\n";
2726             exec('mv ' . $target_path . ' ' . $data_dir . '/' . ' 2>&1', $result, $return_code);
2727             if($return_code != 0) {
2728               print implode('\n',$result) . "<br>";
2729               print "<b><font color=red>";
2730               print "-E- Unable to move the uploaded file into the data dir. Aborting import.";
2731               print "</font></b>";
2732               return 0;
2733             }
2734
2735             # unzip the data into this directory
2736             print "-> Unzipping the data<br>\n";
2737             $data_file = $data_dir . '';
2738             exec('unzip ' . $data_dir . '/*.zip -d ' . $data_dir . ' 2>&1', $result, $return_code);
2739             if($return_code != 0) {
2740               print implode('\n',$result) . "<br>";
2741               print "<b><font color=red>";
2742               print "-E- Unable to unzip the uploaded file into the data dir. Aborting import.";
2743               print "</font></b>";
2744               return 0;
2745             }
2746             exec('mv ' . $data_dir . '/*/* '. $data_dir . ' 2>&1', $result, $return_code);
2747
2748             # update the data_latest link to point to this new directory
2749             print "-> Updating the latest data dir link<br>\n";
2750             $data_latest = $this->upload_target_path . 'data_latest';
2751             exec('rm ' . $data_latest. '; ln -s ' . $data_dir .' '. $data_latest .' 2>&1', $result, $return_code);
2752             if($return_code != 0) {
2753               print implode('\n',$result) . "<br>";
2754               print "<b><font color=red>";
2755               print "-E- Unable to update the data latest link. Aborting import.";
2756               print "</font></b>";
2757               return 0;
2758             }
2759
2760             # run the import perl script to encorporate it into the DB
2761             ob_start('ob_logstdout', 2);
2762             print "-> Importing the data into the EQ database<br>\n";
2763             ob_flush(); flush(); sleep(1);
2764             $import_log = $this->upload_target_path . '/import.log';
2765             $data_log = $this->upload_target_path . '/data.log';
2766             $import_cmd = $this->script_path . 'import_ward_data ' . $data_latest . ' | tee ' . $import_log;
2767             $parse_cmd = $this->script_path . 'parse_ward_data -v ' . $data_latest . ' > ' . $data_log;
2768             #print "import_cmd: $import_cmd<br>";
2769             #print "parse_cmd: $parse_cmd<br>";
2770             ob_start('ob_logstdout', 2);
2771             passthru($import_cmd);
2772             passthru($parse_cmd);
2773             ob_flush(); flush(); sleep(1);
2774
2775             # fix the permissions of the data dir
2776             exec('chmod -R o-rwx ' . $data_dir, $result, $return_code);
2777
2778             $this->t->pfp('cmdhandle','cmd');
2779             print "</pre></td></tr></table>";
2780
2781           } else if(($_FILES['uploadedfile']['type'] != "application/zip") &&
2782                     ($_FILES['uploadedfile']['type'] != "application/x-zip-compressed") &&
2783                     ($_FILES['uploadedfile']['type'] != "application/x-zip") &&
2784                     ($_FILES['uploadedfile']['type'] != "application/octet-stream")) {
2785             $uploadstatus = "<b><font color=red>The file format must be a .zip file, please try again! </font></b>";
2786             $uploadstatus.= "<br><br><b>Detected file format: " . $_FILES['uploadedfile']['type'] . "</b>";
2787             $this->t->set_var('uploadstatus',$uploadstatus);
2788             $this->t->pfp('uploadhandle','upload',True);
2789           } else {
2790             $uploadstatus = "<b><font color=red> There was an error (" . $_FILES['uploadedfile']['error'];
2791             $uploadstatus.= ") uploading the file, please try again! </font></b>";
2792             $this->t->set_var('uploadstatus',$uploadstatus);
2793             $this->t->pfp('uploadhandle','upload',True);
2794           }
2795         }
2796       else
2797         {
2798           $this->t->set_var('adminhandle','');
2799           $this->t->pfp('adminhandle','admin');
2800         }
2801
2802       $this->save_sessiondata();
2803     }
2804
2805 }
2806
2807 ?>