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_int_num_quarters;
  26   var $default_int_num_years;
  27   var $default_vis_num_years;
  28   var $default_att_num_quarters;
  29   var $current_year;
  30   var $current_month;
  31   var $upload_target_path;
  32   var $script_path;
  33
  34   var $public_functions = array
  35     (
  36      'ht_view'    => True,
  37      'ht_update'  => True,
  38      'act_list'   => True,
  39      'act_view'   => True,
  40      'act_update' => True,
  41      'par_view'   => True,
  42      'ppi_view'   => True,
  43      'ppi_update' => True,
  44      'ppi_sched'  => True,
  45      'int_view'   => True,
  46      'int_update' => True,
  47      'int_sched'  => True,
  48      'vis_sched'  => True,
  49      'vis_view'   => True,
  50      'vis_update' => True,
  51      'att_view'   => True,
  52      'att_update' => True,
  53      'dir_view'   => True,
  54      'org_view'   => True,
  55      'schedule'   => True,
  56      'admin'      => True,
  57      'email_appt' => True,
  58      'send_ical_appt' => True
  59      );
  60
  61   function eq()
  62     {
  63       $this->default_ht_num_months = 3;
  64       $this->default_ppi_num_months = 3;
  65       $this->default_ppi_num_years = 0;
  66       $this->default_int_num_quarters = 1;
  67       $this->default_int_num_years = 0;
  68       $this->default_att_num_quarters = 1;
  69       $this->default_vis_num_years = 1;
  70       $this->upload_target_path = "/home/users/eqpres/eq_data/";
  71       $this->script_path = "/usr/share/phpgroupware/eq/";
  72
  73       $this->db         = $GLOBALS['phpgw']->db;
  74       $this->db2        = $this->db;
  75       $this->db3        = $this->db;
  76       $this->nextmatchs = CreateObject('phpgwapi.nextmatchs');
  77       $this->t          = $GLOBALS['phpgw']->template;
  78       $this->account    = $GLOBALS['phpgw_info']['user']['account_id'];
  79       $this->grants     = $GLOBALS['phpgw']->acl->get_grants('eq');
  80       $this->grants[$this->account] = PHPGW_ACL_READ + PHPGW_ACL_ADD + PHPGW_ACL_EDIT + PHPGW_ACL_DELETE;
  81
  82       $this->jscal = CreateObject('phpgwapi.jscalendar');   // before phpgw_header() !!!
  83       $this->cal_options = 'daFormat    : "%Y-%m-%d",
  84                                 ifFormat    : "%Y-%m-%d",
  85                                 mondayFirst : false,
  86                                 weekNumbers : false';
  87
  88       $GLOBALS['phpgw_info']['flags']['app_header'] = 'Elders Quorum Tools';
  89       $GLOBALS['phpgw']->common->phpgw_header();
  90
  91       $this->current_day = `date '+%d'`;
  92       $this->current_day = $this->current_day-0; // Make it numeric
  93       $this->current_month = `date '+%m'`;
  94       $this->current_month = $this->current_month-0; // Make it numeric
  95       $this->current_year = `date '+%Y'`;
  96       $this->current_year = $this->current_year-0; // Make it numeric
  97
  98       echo parse_navbar();
  99       $this->display_app_header();
 100     }
 101
 102   function save_sessiondata()
 103     {
 104
 105     }
 106
 107   function display_app_header()
 108     {
 109       $this->t->set_file(array('eq_header' => 'header.tpl'));
 110
 111       if (isset($phpgw_info['user']['preferences']['eq']['eq_font']))
 112         {
 113           $font = $phpgw_info['user']['preferences']['eq']['eq_font'];
 114         }
 115       else
 116         {
 117           $font = 'Arial';
 118         }
 119
 120       $this->t->set_var('bg_color',$phpgw_info['theme']['th_bg']);
 121       $this->t->set_var('font',$font);
 122       $link_data['menuaction'] = 'eq.eq.ht_view';
 123       $this->t->set_var('link_hometeaching',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 124       $this->t->set_var('lang_hometeaching','HomeTeaching');
 125       $link_data['menuaction'] = 'eq.eq.act_list';
 126       $this->t->set_var('link_activity',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 127       $this->t->set_var('lang_activity','Activities');
 128       $link_data['menuaction'] = 'eq.eq.par_view';
 129       $this->t->set_var('link_participation',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 130       $this->t->set_var('lang_participation','Participation');
 131       $link_data['menuaction'] = 'eq.eq.ppi_view';
 132       $this->t->set_var('link_ppi',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 133       $this->t->set_var('lang_ppi','PPIs');
 134       $link_data['menuaction'] = 'eq.eq.int_view';
 135       $this->t->set_var('link_int',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 136       $this->t->set_var('lang_int','Interviews');
 137       $link_data['menuaction'] = 'eq.eq.vis_view';
 138       $this->t->set_var('link_visit',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 139       $this->t->set_var('lang_visit','Visits');
 140       $link_data['menuaction'] = 'eq.eq.att_view';
 141       $this->t->set_var('link_attendance',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 142       $this->t->set_var('lang_attendance','Attendance');
 143       $link_data['menuaction'] = 'eq.eq.dir_view';
 144       $this->t->set_var('link_dir',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 145       $this->t->set_var('lang_dir','Directory');
 146       $link_data['menuaction'] = 'eq.eq.org_view';
 147       $this->t->set_var('link_org',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 148       $this->t->set_var('lang_org','Callings');
 149       $link_data['menuaction'] = 'eq.eq.admin';
 150       $this->t->set_var('link_admin',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 151       $this->t->set_var('lang_admin','Admin');
 152       $link_data['menuaction'] = 'eq.eq.schedule';
 153       $this->t->set_var('link_schedule',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 154       $this->t->set_var('lang_schedule','Scheduling');
 155
 156       $this->t->pparse('out','eq_header');
 157     }
 158
 159   function ht_view()
 160     {
 161       $this->t->set_file(array('ht_view_t' => 'ht_view.tpl'));
 162       $this->t->set_block('ht_view_t','district_list','list');
 163
 164       $this->t->set_var('linkurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ht_view'));
 165       $num_months = get_var('num_months',array('GET','POST'));
 166       if($num_months == '') { $num_months = $this->default_ht_num_months; }
 167       $this->t->set_var('num_months',$num_months);
 168       $this->t->set_var('lang_filter','Filter');
 169       if($num_months == 1) { $this->t->set_var('lang_num_months','Month of History'); }
 170       else {  $this->t->set_var('lang_num_months','Months of History'); }
 171
 172       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ht_view'));
 173       $this->t->set_var('title','Hometeaching');
 174
 175       $sql = "SELECT * FROM eq_district where valid=1 ORDER BY district ASC";
 176       $this->db->query($sql,__LINE__,__FILE__);
 177       $i=0;
 178       while ($this->db->next_record())
 179         {
 180           $districts[$i]['district'] = $this->db->f('district');
 181           $districts[$i]['name'] = $this->db->f('name');
 182           $districts[$i]['supervisor'] = $this->db->f('supervisor');
 183           $i++;
 184         }
 185
 186       $sql = "SELECT * FROM eq_elder where valid=1 ORDER BY elder ASC";
 187       $this->db->query($sql,__LINE__,__FILE__);
 188       $i=0;
 189       while ($this->db->next_record())
 190         {
 191           $elder_id[$i] = $this->db->f('elder');
 192           $elder_name[$i] = $this->db->f('name');
 193           $elder_phone[$elder_id[$i]] = $this->db->f('phone');
 194           $i++;
 195         }
 196       array_multisort($elder_name, $elder_id);
 197
 198       // Make an array mapping elder_ids to elder_names
 199       for($i=0; $i < count($elder_id); $i++) {
 200           $id = $elder_id[$i];
 201           $elders[$id] = $elder_name[$i];
 202       }
 203
 204       $sql = "SELECT * FROM eq_aaronic where valid=1 ORDER BY aaronic ASC";
 205       $this->db->query($sql,__LINE__,__FILE__);
 206       while ($this->db->next_record())
 207         {
 208           $aaronic_id = $this->db->f('aaronic');
 209           $aaronic[$aaronic_id]['name'] = $this->db->f('name');
 210           $aaronic[$aaronic_id]['phone'] = $this->db->f('phone');
 211         }
 212
 213       $total_families = 0;
 214       $this->nextmatchs->template_alternate_row_color(&$this->t);
 215       for ($i=0; $i < count($districts); $i++) {
 216         $this->t->set_var('district_number',$districts[$i]['district']);
 217         $this->t->set_var('district_name',$districts[$i]['name']);
 218         $supervisor = $districts[$i]['supervisor'];
 219         $unique_companionships='';
 220
 221         // Select all the unique companionship numbers for this district
 222         $sql = "SELECT distinct companionship FROM eq_companionship where valid=1 and district=". $districts[$i]['district'];
 223         $this->db->query($sql,__LINE__,__FILE__);
 224         $j=0;
 225         while ($this->db->next_record())
 226           {
 227             $unique_companionships[$j]['companionship'] = $this->db->f('companionship');
 228             $j++;
 229           }
 230
 231         $comp_width=450; $visit_width=25; $table_width=$comp_width + $num_months*$visit_width;
 232         $table_data=""; $num_companionships = 0; $num_families = 0;
 233         for($m=$num_months; $m >= 0; $m--) { $visits[$m] = 0; }
 234         for ($j=0; $j < count($unique_companionships); $j++) {
 235           $companion_table_entry = "";
 236           // Select all the companions in each companionship
 237           $sql = "SELECT * FROM eq_companionship where valid=1 and ".
 238                  "companionship=". $unique_companionships[$j]['companionship'];
 239           $this->db->query($sql,__LINE__,__FILE__);
 240
 241           while ($this->db->next_record())
 242             {
 243               // Get this companions information
 244               if($companion_table_entry != "") { $companion_table_entry .= "<td>&nbsp;/&nbsp;</td>"; }
 245               $companionship = $this->db->f('companionship');
 246               $elder_id = $this->db->f('elder');
 247               $aaronic_id = $this->db->f('aaronic');
 248               if($elder_id) {
 249                 $name = $elders[$elder_id];
 250                 $phone = $elder_phone[$elder_id];
 251               }
 252               else if($aaronic_id) {
 253                 $name = $aaronic[$aaronic_id]['name'];
 254                 $phone = $aaronic[$aaronic_id]['phone'];
 255               }
 256               $companion_table_entry .= "<td title=\"$phone\"><b>$name</b></td>";
 257             }
 258           $table_data.= "<tr bgcolor=#d3dce3><td colspan=20><table><tr>$companion_table_entry</tr></table><hr></td></tr>";
 259
 260           // Get the names of the families assigned this home teaching companionship
 261           $sql = "SELECT * from eq_family where valid=1 AND companionship=".$unique_companionships[$j]['companionship'];
 262           $sql = $sql . " ORDER BY name ASC";
 263           $this->db->query($sql,__LINE__,__FILE__);
 264           $k=0;
 265           while ($this->db->next_record())
 266             {
 267               $num_families++; $total_families++;
 268               $family_name = $this->db->f('name');
 269               $family_id = $this->db->f('family');
 270               $this->nextmatchs->template_alternate_row_color(&$this->t);
 271               $table_data.="<tr bgcolor=". $this->t->get_var('tr_color') ."><td>$family_name Family</td>";
 272               // Find out how many times Visits were performed by this companionship
 273               // in the past $num_months for this Family
 274               $header_row="<th width=$comp_width><font size=-2>Families</th>";
 275               for($m=$num_months; $m >= 0; $m--) {
 276                 $month = $this->current_month - $m;
 277                 $year = $this->current_year;
 278                 if($month <= 0) { $remainder = $month; $month = 12 + $remainder; $year=$year-1; }
 279                 if($month < 10) { $month = "0"."$month"; }
 280                 $month_start = "$year"."-"."$month"."-"."01";
 281                 $month_end = "$year"."-"."$month"."-"."31";
 282                 $month = "$month"."/"."$year";
 283                 //print "m: $m month: $month year: $year month_start: $month_start month_end: $month_end<br>";
 284                 // Add this to the query to filter on only visits made by this companionship:
 285                 // " AND companionship=" . $unique_companionships[$j]['companionship'].
 286                 $sql = "SELECT * FROM eq_visit WHERE date >= '$month_start' AND date <= '$month_end' ".
 287                        " AND companionship!=0".
 288                        " AND family=". $family_id;
 289                 $this->db2->query($sql,__LINE__,__FILE__);
 290                 $link_data['menuaction'] = 'eq.eq.ht_update';
 291                 $link_data['date'] = $month_start;
 292                 $link_data['month_start'] = $month_start;
 293                 $link_data['month_end'] = $month_end;
 294                 $link_data['month'] = $month;
 295                 $link_data['district'] = $districts[$i]['district'];
 296                 $link_data['district_name'] = $districts[$i]['name'];
 297                 $link_data['action'] = 'view';
 298                 $link = $GLOBALS['phpgw']->link('/eq/index.php',$link_data);
 299                 $header_row .= "<th width=$visit_width><font size=-2><a href=$link>$month</a></th>";
 300                 if(!$total_visits[$m]) { $total_visits[$m] = 0; }
 301                 if($this->db2->next_record()) {
 302                   if($this->db2->f('visited') == 'y') {
 303                     $visits[$m]++; $total_visits[$m]++;
 304                     $table_data .= '<td align=center><a href="'.$link.'"><img src="checkmark.gif"></a></td>';
 305                   }
 306                   else if($this->db2->f('visited') == 'n') {
 307                     $table_data .= '<td align=center><a href="'.$link.'"><img src="x.gif"></a></td>';
 308                   }
 309                   else {
 310                     $visits[$m]++; $total_visits[$m]++;
 311                     $table_data .= "<td>&nbsp;</td>";
 312                   }
 313                 }
 314                 else {
 315                   $visits[$m]++; $total_visits[$m]++;
 316                   $table_data .= "<td>&nbsp;</td>";
 317                 }
 318               }
 319               $table_data .= "</tr>";
 320               $k++;
 321             }
 322           $table_data .= "<tr><td colspan=20></td></tr>";
 323         }
 324         $table_data .= "<tr><td colspan=20><hr></td></tr>";
 325         $stat_data = "<tr><td><b><font size=-2>$num_families Families<br>Visit Totals:</font></b></td>";
 326
 327         for($m=$num_months; $m >=0; $m--) {
 328           $percent = ceil(($visits[$m] / $num_families)*100);
 329           $stat_data .= "<td align=center><font size=-2><b>$visits[$m]<br>$percent%</font></b></td>";
 330         }
 331         $stat_data .= "</tr>";
 332
 333         $this->t->set_var('table_width',$table_width);
 334         $this->t->set_var('header_row',$header_row);
 335         $this->t->set_var('table_data',$table_data);
 336         $this->t->set_var('stat_data',$stat_data);
 337         $this->t->fp('list','district_list',True);
 338       }
 339
 340       $totals = "<tr><td><b><font size=-2>$total_families Total Families<br>Visit Totals:</font></b></td>";
 341       for($m=$num_months; $m >=0; $m--) {
 342         $percent = ceil(($total_visits[$m] / $total_families)*100);
 343         $totals .= "<td align=center><font size=-2><b>$total_visits[$m]<br>$percent%</font></b></td>";
 344       }
 345       $totals .= "</tr>";
 346
 347       $this->t->set_var('totals',$totals);
 348
 349       $this->t->pfp('out','ht_view_t');
 350       $this->save_sessiondata();
 351     }
 352
 353
 354   function ht_update()
 355     {
 356       $this->t->set_file(array('ht_update_t' => 'ht_update.tpl'));
 357       $this->t->set_block('ht_update_t','district_list','list');
 358       $this->t->set_block('ht_update_t','save','savehandle');
 359
 360       $district = get_var('district',array('GET','POST'));
 361       $district_name = get_var('district_name',array('GET','POST'));
 362       $date = get_var('date',array('GET','POST'));
 363       $month = get_var('month',array('GET','POST'));
 364       $month_start = get_var('month_start',array('GET','POST'));
 365       $month_end = get_var('month_end',array('GET','POST'));
 366       $action = get_var('action',array('GET','POST'));
 367
 368       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ht_view'));
 369       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ht_update&action=save'));
 370       $this->t->set_var('lang_done','Cancel');
 371       $this->t->set_var('district_name',$district_name);
 372       $this->t->set_var('district_number',$district);
 373       $this->t->set_var('title','Hometeaching Update ' . $month);
 374       $this->t->set_var('date',$date);
 375
 376       if($action == 'save')
 377         {
 378           // Get a list of all the companionships in this district
 379           $sql = "SELECT distinct companionship FROM eq_companionship where valid=1 and district=". $district;
 380           $this->db->query($sql,__LINE__,__FILE__);
 381           $j=0;
 382           while ($this->db->next_record())
 383             {
 384               $unique_companionships[$j]['companionship'] = $this->db->f('companionship');
 385               $j++;
 386             }
 387           for ($j=0; $j < count($unique_companionships); $j++)
 388             {
 389               // FIXME: We won't be able to go back and edit history on families that have been
 390               // reassigned to a different companionship. The following delete command will not delete
 391               // the history of visits under an older companionship, only the ones for the existing
 392               // companionship. This will lead to duplicate visits being entered for an older
 393               // month for the same family, making it impossible to change the past history once
 394               // a family is reassigned. However, you will be able to view the history just fine.
 395
 396               //$comp=$unique_companionships[$j]['companionship'];
 397               //print "deleting from eq_visit where companionship=$comp and date=$date and district=$district<br>";
 398               // Delete all the visits that have taken place for all families for this month
 399               $this->db->query("DELETE from eq_visit where companionship=" . $unique_companionships[$j]['companionship'] .
 400                                " AND " . "date='" . $date . "'",__LINE__,__FILE__);
 401             }
 402
 403           // Now, add the visits that are checked for this month
 404           $new_data = get_var('family_visited',array('POST'));
 405           foreach ($new_data as $family)
 406            {
 407              foreach ($family as $data)
 408                {
 409                  //print "family_visited: $data <br>";
 410                  $data_array = explode("/",$data);
 411                  $family_id = $data_array[0];
 412                  $companionship = $data_array[1];
 413                  $date = $data_array[2];
 414                  $visited = $data_array[3];
 415                  if($visited == "") { $visited = $data_array[4]; }
 416                  //print "family_id: $family_id companionship: $companionship date: $date visited: $visited<br>";
 417                  $this->db->query("INSERT INTO eq_visit (family,companionship,date,notes,visited) "
 418                                   . "VALUES (" . $family_id .",". $companionship .",'". $date ."','','". $visited ."')",__LINE__,__FILE__);
 419                }
 420            }
 421           $this->ht_view();
 422           return false;
 423         }
 424
 425       $sql = "SELECT * FROM eq_elder where valid=1 ORDER BY elder ASC";
 426       $this->db->query($sql,__LINE__,__FILE__);
 427       $i=0;
 428       while ($this->db->next_record())
 429         {
 430           $elder_id[$i] = $this->db->f('elder');
 431           $elder_name[$i] = $this->db->f('name');
 432           $elder_phone[$elder_id[$i]] = $this->db->f('phone');
 433           $i++;
 434         }
 435       array_multisort($elder_name, $elder_id);
 436
 437       // Make an array mapping elder_ids to elder_names
 438       for($i=0; $i < count($elder_id); $i++) {
 439           $id = $elder_id[$i];
 440           $elders[$id] = $elder_name[$i];
 441       }
 442
 443       $sql = "SELECT * FROM eq_aaronic where valid=1 ORDER BY aaronic ASC";
 444       $this->db->query($sql,__LINE__,__FILE__);
 445       while ($this->db->next_record())
 446         {
 447           $aaronic_id = $this->db->f('aaronic');
 448           $aaronic[$aaronic_id]['name'] = $this->db->f('name');
 449           $aaronic[$aaronic_id]['phone'] = $this->db->f('phone');
 450         }
 451
 452       // Select all the unique companionship numbers for this district
 453       $sql = "SELECT distinct companionship FROM eq_companionship where valid=1 and district=". $district;
 454       $this->db->query($sql,__LINE__,__FILE__);
 455       $j=0; $unique_companionships='';
 456       while ($this->db->next_record())
 457         {
 458           $unique_companionships[$j]['companionship'] = $this->db->f('companionship');
 459           $j++;
 460         }
 461
 462       $comp_width=300; $visit_width=25; $table_width=$comp_width + $visit_width;
 463       $table_data=""; $num_companionships = 0; $num_families = 0; $visits=0;
 464       for ($j=0; $j < count($unique_companionships); $j++) {
 465         $companion_table_entry = "";
 466         // Select all the companions in each companionship
 467         $sql = "SELECT * FROM eq_companionship where valid=1 and ".
 468                "companionship=". $unique_companionships[$j]['companionship'];
 469         $this->db->query($sql,__LINE__,__FILE__);
 470
 471         while ($this->db->next_record())
 472           {
 473             // Get this companions information
 474             if($companion_table_entry != "") { $companion_table_entry .= "<td>&nbsp;/&nbsp;</td>"; }
 475             $companionship = $this->db->f('companionship');
 476             $elder_id = $this->db->f('elder');
 477             $aaronic_id = $this->db->f('aaronic');
 478             if($elder_id) {
 479               $name = $elders[$elder_id];
 480               $phone = $elder_phone[$elder_id];
 481             }
 482             else if($aaronic_id) {
 483               $name = $aaronic[$aaronic_id]['name'];
 484               $phone = $aaronic[$aaronic_id]['phone'];
 485             }
 486             $companion_table_entry .= "<td title=\"$phone\"><b>$name</b></td>";
 487           }
 488         $table_data.= "<tr bgcolor=#d3dce3><td colspan=20><table><tr>$companion_table_entry</tr></table><hr></td></tr>";
 489
 490         // Get the names of the families assigned this home teaching companionship
 491         $sql = "SELECT * from eq_family where valid=1 AND companionship=".$unique_companionships[$j]['companionship'];
 492         $sql = $sql . " ORDER BY name ASC";
 493         $this->db->query($sql,__LINE__,__FILE__);
 494         while ($this->db->next_record())
 495           {
 496             $num_families++; $total_families++;
 497             $family_name = $this->db->f('name');
 498             $family_id = $this->db->f('family');
 499             $this->nextmatchs->template_alternate_row_color(&$this->t);
 500             $table_data.="<tr bgcolor=". $this->t->get_var('tr_color') ."><td>$family_name Family</td>";
 501
 502             $header_row="<th width=$comp_width><font size=-2>Families</th>";
 503             $sql = "SELECT * FROM eq_visit WHERE date >= '$month_start' AND date <= '$month_end' ".
 504                    " AND companionship!=0".
 505                    " AND family=". $family_id;
 506             $this->db2->query($sql,__LINE__,__FILE__);
 507             $value = $family_id . "/" . $unique_companionships[$j]['companionship'] . "/" . $date;
 508             $header_row .= "<th width=$visit_width><font size=-2><a href=$link>$month</a></th>";
 509             if(!$total_visits) { $total_visits = 0; }
 510             if($this->db2->next_record()) {
 511               if($this->db2->f('visited') == 'y') {
 512                 $visits++; $total_visits++;
 513                 $table_data .= '<td width=100 align=center>';
 514                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/y" checked>Y';
 515                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/n">N';
 516                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/"> ';
 517                 $table_data .= '</td>';
 518               } else if($this->db2->f('visited') == 'n') {
 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" checked>N';
 522                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/">';
 523                 $table_data .= '</td>';
 524               } else {
 525                 $table_data .= '<td width=100 align=center>';
 526                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/y">Y';
 527                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/n">N';
 528                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/" checked> ';
 529                 $table_data .= '</td>';
 530               }
 531             }
 532             else {
 533               $value .= "/";
 534               $table_data .= '<td width=100 align=center>';
 535               $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/y">Y';
 536               $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/n">N';
 537               $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/" checked> ';
 538               $table_data .= '</td>';
 539             }
 540           }
 541         $table_data .= "</tr>";
 542         $table_data .= "<tr><td colspan=20></td></tr>";
 543       }
 544       $table_data .= "<tr><td colspan=20><hr></td></tr>";
 545       $stat_data = "<tr><td><b><font size=-2>$num_families Families<br>Visit Totals:</font></b></td>";
 546
 547       $percent = ceil(($visits / $num_families)*100);
 548       $stat_data .= "<td align=center><font size=-2><b>$visits<br>$percent%</font></b></td>";
 549       $stat_data .= "</tr>";
 550
 551       $this->t->set_var('table_width',$table_width);
 552       $this->t->set_var('header_row',$header_row);
 553       $this->t->set_var('table_data',$table_data);
 554       $this->t->set_var('stat_data',$stat_data);
 555       $this->t->fp('list','district_list',True);
 556
 557       $this->t->set_var('lang_reset','Clear Form');
 558       $this->t->set_var('lang_save','Save Changes');
 559       $this->t->set_var('savehandle','');
 560
 561       $this->t->pfp('out','ht_update_t');
 562       $this->t->pfp('addhandle','save');
 563
 564       $this->save_sessiondata();
 565     }
 566
 567   function act_list()
 568     {
 569       $this->t->set_file(array('act_list_t' => 'act_list.tpl'));
 570       $this->t->set_block('act_list_t','act_list','list');
 571
 572       $this->t->set_var('lang_name','Activity Name');
 573       $this->t->set_var('lang_date','Date');
 574
 575       $sql = "SELECT * FROM eq_activity ORDER BY date DESC";
 576       $this->db->query($sql,__LINE__,__FILE__);
 577       $total_records = $this->db->num_rows();
 578
 579       $i = 0;
 580       while ($this->db->next_record())
 581         {
 582           $activity_list[$i]['name'] = $this->db->f('name');
 583           $activity_list[$i]['date']  = $this->db->f('date');
 584           $activity_list[$i]['activity']  = $this->db->f('activity');
 585           $i++;
 586         }
 587
 588       for ($i=0; $i < count($activity_list); $i++)
 589         {
 590           $this->nextmatchs->template_alternate_row_color(&$this->t);
 591           $this->t->set_var('name',$activity_list[$i]['name']);
 592           $this->t->set_var('date',$activity_list[$i]['date']);
 593
 594           $link_data['menuaction'] = 'eq.eq.act_view';
 595           $link_data['activity'] = $activity_list[$i]['activity'];
 596           $link_data['action'] = 'view';
 597           $this->t->set_var('view',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 598           $this->t->set_var('lang_view','View');
 599
 600           $link_data['menuaction'] = 'eq.eq.act_update';
 601           $link_data['activity'] = $activity_list[$i]['activity'];
 602           $link_data['action'] = 'edit';
 603           $this->t->set_var('edit',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 604           $this->t->set_var('lang_edit','Edit');
 605
 606           $link_data['menuaction'] = 'eq.eq.act_update';
 607           $link_data['activity'] = '0';
 608           $link_data['action'] = 'add';
 609           $this->t->set_var('add','<form method="POST" action="' . $GLOBALS['phpgw']->link('/eq/index.php',$link_data)
 610                            . '"><input type="submit" name="Add" value="' . 'Add Activity' .'"></font></form>');
 611
 612           $this->t->fp('list','act_list',True);
 613         }
 614
 615       $this->t->pfp('out','act_list_t');
 616       $this->save_sessiondata();
 617     }
 618
 619   function act_view()
 620     {
 621       $this->t->set_file(array('act_view_t' => 'act_view.tpl'));
 622       $this->t->set_block('act_view_t','part_list','list');
 623
 624       $sql = "SELECT * FROM eq_activity WHERE activity=" . intval(get_var('activity',array('GET','POST')));
 625       $this->db->query($sql,__LINE__,__FILE__);
 626       $this->db->next_record();
 627       $this->t->set_var('name', $this->db->f('name'));
 628       $this->t->set_var('date', $this->db->f('date'));
 629       $this->t->set_var('notes', $this->db->f('notes'));
 630
 631       $this->t->set_var('lang_name','Activity Name');
 632       $this->t->set_var('lang_date','Date');
 633       $this->t->set_var('lang_notes','Notes');
 634       $this->t->set_var('lang_done','Done');
 635       $this->t->set_var('lang_action','View');
 636
 637       $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
 638       $this->t->set_var('tr_color',$tr_color);
 639
 640       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.act_list'));
 641
 642       $link_data['menuaction'] = 'eq.eq.act_update';
 643       $link_data['activity'] = get_var('activity',array('GET','POST'));
 644       $link_data['action'] = 'edit';
 645       $this->t->set_var('edit',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
 646       $this->t->set_var('lang_edit','Edit');
 647       $this->t->set_var('cal_date',$this->db->f('date'));
 648
 649       // Now find out which elders participated in this activity
 650       $sql = "SELECT * FROM eq_participation WHERE activity=" . intval(get_var('activity',array('GET','POST')));
 651       $this->db->query($sql,__LINE__,__FILE__);
 652       $total_records = $this->db->num_rows();
 653
 654       $i = 0;
 655       while ($this->db->next_record())
 656         {
 657           $part_list[$i]['elder']  = $this->db->f('elder');
 658           $i++;
 659         }
 660
 661       for ($i=0; $i < count($part_list); $i++)
 662         {
 663           $sql = "SELECT * FROM eq_elder WHERE elder=" . $part_list[$i]['elder'];
 664           $this->db->query($sql,__LINE__,__FILE__);
 665           $this->db->next_record();
 666           $names[$i] = $this->db->f('name');
 667         } sort($names);
 668
 669       for ($i=0; $i < count($names); $i++)
 670         {
 671           //$this->nextmatchs->template_alternate_row_color(&$this->t);
 672           $this->t->set_var('elder_name',$names[$i]);
 673           if(($i+1) % 3 == 0) { $this->t->set_var('table_sep',"</td></tr><tr>"); }
 674           else { $this->t->set_var('table_sep',"</td>"); }
 675           if(($i) % 3 == 0) { $this->nextmatchs->template_alternate_row_color(&$this->t); }
 676           $this->t->fp('list','part_list',True);
 677         }
 678
 679       $this->t->pfp('out','act_view_t');
 680       $this->save_sessiondata();
 681     }
 682
 683   function act_update()
 684     {
 685       $this->t->set_file(array('form' => 'act_update.tpl'));
 686       $this->t->set_block('form','elder_list','list');
 687       $this->t->set_block('form','add','addhandle');
 688       $this->t->set_block('form','edit','edithandle');
 689       $this->t->set_var('lang_done','Done');
 690
 691       $action = get_var('action',array('GET','POST'));
 692       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.act_list'));
 693       $activity['activity'] = intval(get_var('activity',array('GET','POST')));
 694
 695       if($action == 'save')
 696         {
 697           $activity['name'] = $this->db->db_addslashes(get_var('name',array('POST')));
 698           $activity['date'] = $this->db->db_addslashes(get_var('date',array('POST')));
 699           $activity['notes']= $this->db->db_addslashes(get_var('notes',array('POST')));
 700           $this->db->query("UPDATE eq_activity set " .
 701                            "   name='" . $activity['name'] .
 702                            "', date='" . $activity['date'] . "'" .
 703                            ", notes='" . $activity['notes'] . "'" .
 704                            " WHERE activity=" . $activity['activity'],__LINE__,__FILE__);
 705
 706           // Delete all the elders who have particiapted in this activity
 707           $this->db->query("DELETE from eq_participation where activity=".$activity['activity'],__LINE__,__FILE__);
 708
 709           // Re-add the elders who are checked as having participated in this activity
 710           $elders = get_var('elder_name',array('POST'));
 711           foreach ($elders as $elder)
 712             {
 713               $this->db->query("INSERT INTO eq_participation (elder,activity) "
 714                                . "VALUES (" . $elder . ",". $activity['activity'] . ")",__LINE__,__FILE__);
 715             }
 716
 717           $this->act_list();
 718           return false;
 719         }
 720
 721       if($action == 'insert')
 722         {
 723           $activity['name'] = $this->db->db_addslashes(get_var('name',array('POST')));
 724           $activity['date'] = $this->db->db_addslashes(get_var('date',array('POST')));
 725           $activity['notes']= $this->db->db_addslashes(get_var('notes',array('POST')));
 726           $this->db->query("INSERT INTO eq_activity (name,date,notes) "
 727                            . "VALUES ('" . $activity['name'] . "','"
 728                            . $activity['date'] . "','" . $activity['notes'] . "')",__LINE__,__FILE__);
 729           $this->act_list();
 730           return false;
 731         }
 732
 733       if($action == 'add')
 734         {
 735           $activity['activity'] = 0;
 736           $this->t->set_var('cal_date',$this->jscal->input('date','','','','','','',$this->cal_options));
 737           $this->t->set_var('name','');
 738           $this->t->set_var('date','');
 739           $this->t->set_var('notes','');
 740           $this->t->set_var('lang_done','Cancel');
 741           $this->t->set_var('lang_action','Adding New Activity');
 742           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.act_update&activity='
 743                                                                 . $activity['activity'] . '&action=' . 'insert'));
 744         }
 745
 746       if($action == 'edit')
 747         {
 748           $sql = "SELECT * FROM eq_activity WHERE activity=" . $activity['activity'];
 749           $this->db->query($sql,__LINE__,__FILE__);
 750           $this->db->next_record();
 751           $this->t->set_var('cal_date',$this->jscal->input('date',$this->db->f('date'),'','','','','',$this->cal_options));
 752           $this->t->set_var('name', $this->db->f('name'));
 753           $this->t->set_var('date', $this->db->f('date'));
 754           $this->t->set_var('notes', $this->db->f('notes'));
 755           $this->t->set_var('lang_done','Cancel');
 756           $this->t->set_var('lang_action','Editing Activity');
 757           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.act_update&activity='
 758                                                                 . $activity['activity'] . '&action=' . 'save'));
 759
 760         }
 761
 762       $sql = "SELECT * FROM eq_elder";
 763       $this->db->query($sql,__LINE__,__FILE__);
 764       $i=0;
 765       while ($this->db->next_record())
 766         {
 767           if($this->db->f('valid') == 1 || $action != 'add') {
 768             $elder_name[$i] = $this->db->f('name');
 769             $elder_id[$i] = $this->db->f('elder');
 770             $elder_valid[$i] = $this->db->f('valid');
 771             $i++;
 772           }
 773         }
 774       array_multisort($elder_name, $elder_id, $elder_valid);
 775
 776       $j=0;
 777       for ($i=0; $i < count($elder_id); $i++)
 778         {
 779           //$this->nextmatchs->template_alternate_row_color(&$this->t);
 780           $sql = "SELECT * FROM eq_participation where activity=". $activity['activity'] . " AND elder=" . $elder_id[$i];
 781           $this->db->query($sql,__LINE__,__FILE__);
 782           if($this->db->next_record()) { $this->t->set_var('checked','checked'); $checked=1; }
 783           else { $this->t->set_var('checked',''); $checked=0; }
 784           if($checked || $elder_valid[$i] == 1) {
 785             $this->t->set_var('elder_name',$elder_name[$i]);
 786             $this->t->set_var('elder',$elder_id[$i]);
 787             if(($j+1) % 3 == 0) { $this->t->set_var('table_sep',"</td></tr><tr>"); }
 788             else { $this->t->set_var('table_sep',"</td>"); }
 789             if(($j) % 3 == 0) { $this->nextmatchs->template_alternate_row_color(&$this->t); }
 790             $this->t->fp('list','elder_list',True);
 791             $j++;
 792           }
 793         }
 794
 795       $this->t->set_var('lang_reset','Clear Form');
 796       $this->t->set_var('lang_add','Add Activity');
 797       $this->t->set_var('lang_save','Save Changes');
 798       $this->t->set_var('edithandle','');
 799       $this->t->set_var('addhandle','');
 800
 801       $this->t->pfp('out','form');
 802       if($action == 'edit') { $this->t->pfp('addhandle','edit'); }
 803       if($action == 'add') { $this->t->pfp('addhandle','add'); }
 804
 805       $this->save_sessiondata();
 806     }
 807
 808   function par_view()
 809     {
 810       $this->t->set_file(array('par_view_t' => 'par_view.tpl'));
 811       $this->t->set_block('par_view_t','header_list','list1');
 812       $this->t->set_block('par_view_t','elder_list','list2');
 813
 814       $sql = "SELECT * FROM eq_elder where valid=1";
 815       $this->db->query($sql,__LINE__,__FILE__);
 816       $i=0;
 817       while ($this->db->next_record())
 818         {
 819           $elder_name[$i] = $this->db->f('name');
 820           $elder_id[$i] = $this->db->f('elder');
 821           $i++;
 822         }
 823       array_multisort($elder_name, $elder_id);
 824
 825       $sql = "SELECT * FROM eq_activity ORDER BY date DESC";
 826       $this->db->query($sql,__LINE__,__FILE__);
 827       $total_records = $this->db->num_rows();
 828
 829       $i = 0;
 830       while ($this->db->next_record())
 831         {
 832           $activity_list[$i]['name'] = $this->db->f('name');
 833           $activity_list[$i]['date'] = $this->db->f('date');
 834           $activity_list[$i]['activity']  = $this->db->f('activity');
 835           if($activity_list[$i]['date'] == '0000-00-00') { $activity_list[$i]['date']=""; }
 836           $i++;
 837         }
 838
 839       $elder_width=250; $part_width=25; $act_width=50;
 840       $total_width=$elder_width+$part_width;
 841       for ($i=0; $i < count($activity_list); $i++) {
 842         $this->t->set_var('activity_name',$activity_list[$i]['name']);
 843         $this->t->set_var('activity_date',$activity_list[$i]['date']);
 844         $this->t->fp('list1','header_list',True);
 845         $total_width += $act_width;
 846       }
 847
 848       for ($i=0; $i < count($elder_id); $i++) {
 849         $participated=0; $part_table = '';
 850         $this->nextmatchs->template_alternate_row_color(&$this->t);
 851         $this->t->set_var('elder_name',$elder_name[$i]);
 852         for ($j=0; $j < count($activity_list); $j++) {
 853           $sql = "SELECT * FROM eq_participation where activity="
 854              . $activity_list[$j]['activity'] . " AND elder=" . $elder_id[$i];
 855           $this->db->query($sql,__LINE__,__FILE__);
 856           if($this->db->next_record()) {
 857             $part_table .= '<td align=center><img src="checkmark.gif"></td>';
 858             $participated++;
 859           } else {
 860             $part_table .= '<td>&nbsp;</td>';
 861           }
 862         }
 863         if($participated) { $part_table .= '<td align=center><img src="checkmark.gif">'.$participated.'</td>'; }
 864         else { $part_table .= '<td>&nbsp;</td>'; }
 865         $this->t->set_var('part_table',$part_table);
 866         $this->t->fp('list2','elder_list',True);
 867       }
 868       $this->t->set_var('total_width',$total_width);
 869       $this->t->set_var('elder_width',$elder_width);
 870       $this->t->set_var('part_width',$part_width);
 871       $this->t->set_var('act_width',$act_width);
 872       $this->t->pfp('out','par_view_t');
 873       $this->save_sessiondata();
 874     }
 875
 876   function ppi_sched()
 877     {
 878       $this->t->set_file(array('ppi_sched_t' => 'ppi_sched.tpl'));
 879       $this->t->set_block('ppi_sched_t','elder_list','elderlist');
 880       $this->t->set_block('ppi_sched_t','appt_list','apptlist');
 881       $action = get_var('action',array('GET','POST'));
 882
 883       $this->t->set_var('lang_save','Save Appt / Pri / Notes');
 884       $this->t->set_var('lang_reset','Clear Changes');
 885
 886       $this->t->set_var('ppi_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_view'));
 887       $this->t->set_var('ppi_link_title','Yearly PPIs');
 888
 889       $this->t->set_var('schedule_ppi_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_sched'));
 890       $this->t->set_var('schedule_ppi_link_title','Schedule Yearly PPIs');
 891
 892       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_sched&action=save'));
 893       $this->t->set_var('title','Yearly PPI Scheduler');
 894
 895       $elder_width=500; $phone_width=25; $pri_width=10; $notes_width=128; $ppi_date_width=20;
 896       $table_width=$elder_width + $phone_width + $pri_width + $notes_width + $ppi_date_width;
 897       $header_row = "<th width=$elder_width><font size=-2>Elder Name</th>";
 898       $header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
 899       $header_row.= "<th width=$pri_width><font size=-2>Priority</th>";
 900       $header_row.= "<th width=$ppi_date_width><font size=-2>Last PPI</th>";
 901       $header_row.= "<th width=$notes_width><font size=-2>Scheduling Notes</th>";
 902       $table_data=""; $completed_data=""; $totals_data="";
 903
 904       $year = date('Y');
 905
 906       if($action == 'save')
 907         {
 908           // Save any changes made to the appointment table
 909           $new_data = get_var('appt_notes',array('POST'));
 910           foreach ($new_data as $entry)
 911            {
 912              $elder = $entry['elder'];
 913              $appointment = $entry['appointment'];
 914
 915              //print "elder: $elder appointment: $appointment <br>";
 916
 917              // Perform database save actions here
 918              $this->db->query("UPDATE eq_appointment set " .
 919                               " elder='" . $elder . "'" .
 920                               " WHERE appointment=" . $appointment,__LINE__,__FILE__);
 921
 922              // Email the appointment
 923              $this->email_appt($appointment);
 924
 925            }
 926
 927           // Save any changes made to the ppi notes table
 928           $new_data = get_var('ppi_notes',array('POST'));
 929           foreach ($new_data as $entry)
 930            {
 931              $ppi_notes = $entry['notes'];
 932              $elder_id = $entry['elder_id'];
 933              $ppi_pri = $entry['pri'];
 934
 935              // Perform database save actions here
 936              $this->db->query("UPDATE eq_elder set " .
 937                               " ppi_notes='" . $ppi_notes . "'" .
 938                               ",ppi_pri='" . $ppi_pri . "'" .
 939                               " WHERE elder=" . $elder_id,__LINE__,__FILE__);
 940
 941            }
 942
 943           $take_me_to_url = $GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_sched');
 944           Header('Location: ' . $take_me_to_url);
 945         }
 946
 947       // Get the EQ President
 948       $sql = "SELECT * FROM eq_presidency where president=1 and valid=1";
 949       $this->db->query($sql,__LINE__,__FILE__);
 950       if($this->db->next_record()) {
 951         $president_name = $this->db->f('name');
 952         $president_id = $this->db->f('elder');
 953         $presidency_id = $this->db->f('presidency');
 954         $district_number = '*';
 955         $district_name = $president_name;
 956       } else {
 957         print "<hr><font color=red><h3>-E- Unable to locate EQ President in eq_presidency table</h3></font></hr>";
 958         return;
 959       }
 960
 961       // create the elder id -> elder name mapping
 962       $sql = "SELECT * FROM eq_elder where valid=1 ORDER BY name ASC";
 963       $this->db->query($sql,__LINE__,__FILE__);
 964       $i=0;
 965       $elder_id = NULL;
 966       $elder_name = NULL;
 967       while ($this->db->next_record())
 968         {
 969           $elder_name[$i] = $this->db->f('name');
 970           $elder_id[$i] = $this->db->f('elder');
 971           $i++;
 972         }
 973       array_multisort($elder_name, $elder_id);
 974
 975       // APPOINTMENT TABLE
 976       $date_width=150; $time_width=100; $elder_width=200;
 977       $appt_table_width=$date_width + $time_width + $elder_width;
 978       $appt_header_row = "<th width=$date_width><font size=-2>Date</th>";
 979       $appt_header_row.= "<th width=$time_width><font size=-2>Time</th>";
 980       $appt_header_row.= "<th width=$elder_width><font size=-2>Elder</th>";
 981       $appt_table_data = "";
 982
 983       $total_elders=0; $elders_with_yearly_ppi=0;
 984
 985       // Display a scheduling table for the EQ President
 986       $table_data=""; $appt_table_data="";
 987       $table_title = "District ".$district_number.": ".$district_name.": All Elders with Yearly PPI Not Completed";
 988       $appt_table_title = "District ".$district_number.": ".$district_name.": Yearly PPI Appointment Slots";
 989       $this->t->set_var('table_title',$table_title);
 990       $this->t->set_var('appt_table_title',$appt_table_title);
 991
 992       // query the database for all the appointments
 993       $sql = "SELECT * FROM eq_appointment where presidency=".$presidency_id." and date>=CURDATE() ORDER BY date ASC, time ASC";
 994       $this->db->query($sql,__LINE__,__FILE__);
 995
 996       while ($this->db->next_record())
 997         {
 998           $appointment = $this->db->f('appointment');
 999           $elder = $this->db->f('elder');
1000
1001           $date = $this->db->f('date');
1002           $date_array = explode("-",$date);
1003           $year = $date_array[0]; $month = $date_array[1]; $day = $date_array[2];
1004           $day_string = date("l d-M-Y", mktime(0,0,0,$month,$day,$year));
1005
1006           $time = $this->db->f('time');
1007           $time_array = explode(":",$time);
1008           $time_string = date("g:i a", mktime($time_array[0], $time_array[1], $time_array[2]));
1009
1010           $appt_table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1011           $appt_table_data.= "<td align=center>$day_string</td>";
1012           $appt_table_data.= "<td align=center>$time_string</td>";
1013
1014           $appt_table_data.= '<td align=center><select name=appt_notes['.$appointment.'][elder]>';
1015           $appt_table_data.= '<option value=0></option>';
1016           for ($i=0; $i < count($elder_id); $i++) {
1017             $id = $elder_id[$i];
1018             $name = $elder_name[$i];
1019             if($elder_id[$i] == $elder) { $selected[$id] = 'selected="selected"'; } else { $selected[$id] = ''; }
1020             $appt_table_data.= '<option value='.$id.' '.$selected[$id].'>'.$name.'</option>';
1021           }
1022           $appt_table_data.='</select></td>';
1023
1024           $appt_table_data.= '<input type=hidden name="appt_notes['.$appointment.'][appointment]" value="'.$appointment.'">';
1025
1026           $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1027           $this->t->set_var('tr_color',$tr_color);
1028         }
1029
1030       $this->t->set_var('appt_table_data',$appt_table_data);
1031       $this->t->set_var('appt_header_row',$appt_header_row);
1032       $this->t->set_var('appt_table_width',$appt_table_width);
1033
1034       // PPI SCHEDULING TABLE
1035       $sql = "SELECT * FROM eq_elder where valid=1 ORDER BY ppi_pri ASC";
1036       $this->db->query($sql,__LINE__,__FILE__);
1037
1038       $i=0;
1039       $elder_id = NULL;
1040       while ($this->db->next_record())
1041         {
1042           $elder_id[$i] = $this->db->f('elder');
1043           $elder_name[$i] = $this->db->f('name');
1044           $elder_phone[$elder_id[$i]] = $this->db->f('phone');
1045           $elder_ppi_pri[$elder_id[$i]] = $this->db->f('ppi_pri');
1046           $elder_ppi_notes[$elder_id[$i]] = $this->db->f('ppi_notes');
1047           $i++;
1048           $total_elders++;
1049         }
1050
1051       $max = count($elder_id);
1052
1053       for($i=0; $i < $max; $i++) {
1054           $id = $elder_id[$i];
1055           $name = $elder_name[$i];
1056           $phone = $elder_phone[$id];
1057           $ppi_pri = $elder_ppi_pri[$id];
1058           $ppi_notes = $elder_ppi_notes[$id];
1059
1060           // If this elder has had a yearly PPI this year, don't show him on the schedule list
1061           $year_start = $year - 1 . "-12-31"; $year_end = $year + 1 . "-01-01";
1062           $sql = "SELECT * FROM eq_ppi WHERE date > '$year_start' AND date < '$year_end' ".
1063              "AND elder=" . $id;
1064           $this->db2->query($sql,__LINE__,__FILE__);
1065
1066           if(!$this->db2->next_record()) {
1067             $sql = "SELECT * FROM eq_ppi WHERE elder=" . $id . " ORDER BY date DESC";
1068             $this->db->query($sql,__LINE__,__FILE__);
1069             if($this->db->next_record()) { $date = $this->db->f('date'); } else { $date = ""; }
1070             $link_data['menuaction'] = 'eq.eq.ppi_update';
1071             $link_data['elder'] = $id;
1072             $link_data['name'] = $name;
1073             $link_data['ppi'] = '';
1074             $link_data['eqpresppi'] = 1;
1075             $link_data['action'] = 'add';
1076             $link = $GLOBALS['phpgw']->link('/eq/index.php',$link_data);
1077             $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1078             $this->t->set_var('tr_color',$tr_color);
1079             $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
1080             $table_data.= "<td align=center>$phone</td>";
1081             //$table_data.= "<td align=center>$ppi_pri</td>";
1082             $table_data.= "<td align=center>";
1083             $table_data.= '<select name=ppi_notes['.$i.'][pri]>';
1084             foreach(range(0,6) as $num) {
1085               if($num == 0) { $num = 1; } else {$num = $num*5; }
1086               if($ppi_pri == $num) { $selected[$num] = 'selected="selected"'; } else { $selected[$num] = ''; }
1087               $table_data.= '<option value='.$num.' '.$selected[$num].'>'.$num.'</option>';
1088             }
1089             $table_data.= '</select></td>';
1090             $table_data.= "<td align=center>$date</td>";
1091             $table_data.= '<td><input type=text size="50" maxlength="128" name="ppi_notes['.$i.'][notes]" value="'.$ppi_notes.'">';
1092             $table_data.= '<input type=hidden name="ppi_notes['.$i.'][elder_id]" value="'.$id.'">';
1093             $table_data.= '<input type=hidden name="ppi_notes['.$i.'][elder_name]" value="'.$name.'">';
1094             $table_data.= '</td>';
1095             $table_data.= '</tr>';
1096           } else {
1097             $link_data['menuaction'] = 'eq.eq.ppi_update';
1098             $link_data['interviewer'] = $this->db2->f('interviewer');
1099             $link_data['elder'] = $this->db2->f('elder');
1100             $link_data['name'] = $name;
1101             $link_data['ppi'] = $this->db2->f('ppi');
1102             $link_data['eqpresppi'] = $this->db2->f('eqpresppi');
1103             $link_data['action'] = 'view';
1104             $link = $GLOBALS['phpgw']->link('/eq/index.php',$link_data);
1105             $elders_with_yearly_ppi++;
1106             $date = $this->db2->f('date');
1107             $ppi_notes = $this->db2->f('notes');
1108             if(strlen($ppi_notes) > 40) { $ppi_notes = substr($ppi_notes,0,40) . "..."; }
1109             $tr_color2 = $this->nextmatchs->alternate_row_color($tr_color2);
1110             $this->t->set_var('tr_color2',$tr_color2);
1111             $completed_data.= "<tr bgcolor=". $this->t->get_var('tr_color2') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
1112             $completed_data.= "<td align=center>$phone</td>";
1113             $completed_data.= "<td align=center><a href=".$link.">$date</a></td>";
1114             $completed_data.= "<td align=left>$ppi_notes</td>";
1115             $completed_data.= '</tr>';
1116           }
1117       } // End for Elders Loop
1118
1119       $name_width=175; $phone_width=100; $date_width=100; $notes_width=300;
1120       $completed_table_width=$name_width + $phone_width + $date_width + $notes_width;
1121       $completed_header_row = "<th width=$name_width><font size=-2>Elder Name</th>";
1122       $completed_header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
1123       $completed_header_row.= "<th width=$date_width><font size=-2>Date</th>";
1124       $completed_header_row.= "<th width=$notes_width><font size=-2>PPI Notes</th>";
1125
1126       $this->t->set_var('table_width',$table_width);
1127       $this->t->set_var('header_row',$header_row);
1128       $this->t->set_var('table_data',$table_data);
1129       $this->t->set_var('completed_header_row',$completed_header_row);
1130       $this->t->set_var('completed_table_width',$completed_table_width);
1131       $this->t->set_var('completed',$completed_data);
1132       $this->t->fp('elderlist','elder_list',True);
1133
1134       $elders_width=300; $totals_width=100;
1135       $totals_table_width=$elders_width + $totals_width;
1136       $totals_header_row = "<th width=$elders_width><font size=-2>Elders</th>";
1137       $totals_header_row.= "<th width=$totals_width><font size=-2>$year</th>";
1138       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1139       $totals_data.= "<td align=left><font size=-2><b>Total Elders with yearly PPIs completed:</b></font></td>";
1140       $totals_data.= "<td align=center><font size=-2><b>$elders_with_yearly_ppi / $total_elders</b></font></td>";
1141       $percent = ceil(($elders_with_yearly_ppi / $total_elders)*100);
1142       $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1143       $this->t->set_var('tr_color',$tr_color);
1144       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1145       $totals_data.= "<td align=left><font size=-2><b>Percentage:</b></font></td>";
1146       $totals_data.= "<td align=center><font size=-2><b>$percent%</b></font></td>";
1147       $totals_data.= "</tr>";
1148
1149       $this->t->set_var('totals',$totals_data);
1150       $this->t->set_var('totals_header_row',$totals_header_row);
1151       $this->t->set_var('totals_table_width',$totals_table_width);
1152
1153       $this->t->pfp('out','ppi_sched_t');
1154       $this->save_sessiondata();
1155
1156     }
1157
1158   function int_sched()
1159     {
1160       $this->t->set_file(array('int_sched_t' => 'int_sched.tpl'));
1161       $this->t->set_block('int_sched_t','elder_list','elderlist');
1162       $this->t->set_block('int_sched_t','appt_list','apptlist');
1163       $action = get_var('action',array('GET','POST'));
1164
1165       $this->t->set_var('lang_save','Save Appt / Pri / Notes');
1166       $this->t->set_var('lang_reset','Clear Changes');
1167
1168       $this->t->set_var('int_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.int_view'));
1169       $this->t->set_var('int_link_title','Hometeaching Interviews');
1170
1171       $this->t->set_var('schedule_int_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.int_sched'));
1172       $this->t->set_var('schedule_int_link_title','Schedule Hometeaching Interviews');
1173
1174       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.int_sched&action=save'));
1175       $this->t->set_var('title','Hometeaching Interviews Scheduler');
1176
1177       $elder_width=500; $phone_width=25; $pri_width=10; $notes_width=128; $int_date_width=20;
1178       $table_width=$elder_width + $phone_width + $pri_width + $notes_width + $int_date_width;
1179       $header_row = "<th width=$elder_width><font size=-2>Elder Name</th>";
1180       $header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
1181       $header_row.= "<th width=$pri_width><font size=-2>Priority</th>";
1182       $header_row.= "<th width=$int_date_width><font size=-2>Last Interview</th>";
1183       $header_row.= "<th width=$notes_width><font size=-2>Scheduling Notes</th>";
1184       $table_data=""; $completed_data=""; $totals_data="";
1185
1186       $year = date('Y');
1187       $month = date('m');
1188       $nextyear = $year + 1;
1189       if($month >= 1 && $month <= 3) { $quarter_start=$year."-01-01"; $quarter_end=$year."-04-01"; }
1190       if($month >= 4 && $month <= 6) { $quarter_start=$year."-04-01"; $quarter_end=$year."-07-01"; }
1191       if($month >= 7 && $month <= 9) { $quarter_start=$year."-07-01"; $quarter_end=$year."-10-01"; }
1192       if($month >= 10 && $month <= 12) { $quarter_start=$year."-10-01"; $quarter_end=$nextyear."-01-01"; }
1193       //print "year: $year month: $month quarter_start: $quarter_start quarter_end: $quarter_end<br>";
1194
1195       if($action == 'save')
1196         {
1197           // Save any changes made to the appointment table
1198           $new_data = get_var('appt_notes',array('POST'));
1199           foreach ($new_data as $entry)
1200            {
1201              $elder = $entry['elder'];
1202              $appointment = $entry['appointment'];
1203
1204              //print "elder: $elder appointment: $appointment <br>";
1205
1206              // Perform database save actions here
1207              $this->db->query("UPDATE eq_appointment set " .
1208                               " elder='" . $elder . "'" .
1209                               " WHERE appointment=" . $appointment,__LINE__,__FILE__);
1210
1211              // Email the appointment
1212              $this->email_appt($appointment);
1213            }
1214
1215           // Save any changes made to the int notes table
1216           $new_data = get_var('int_notes',array('POST'));
1217           foreach ($new_data as $entry)
1218            {
1219              $int_notes = $entry['notes'];
1220              $elder_id = $entry['elder_id'];
1221              $elder_name = $entry['elder_name'];
1222              $int_pri = $entry['pri'];
1223              $aaronic = $entry['aaronic'];
1224              if($aaronic == 0) {
1225                // Perform database save actions here
1226                $this->db->query("UPDATE eq_elder set " .
1227                                 " int_notes='" . $int_notes . "'" .
1228                                 ",int_pri='" . $int_pri . "'" .
1229                                 " WHERE elder=" . $elder_id,__LINE__,__FILE__);
1230              }
1231
1232            }
1233
1234           $take_me_to_url = $GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.int_sched');
1235           Header('Location: ' . $take_me_to_url);
1236         }
1237
1238       // Get the Districts
1239       $sql = "SELECT * FROM eq_district where valid=1 ORDER BY district ASC";
1240       $this->db->query($sql,__LINE__,__FILE__);
1241       $i=0;
1242       while ($this->db->next_record())
1243         {
1244           $district = $this->db->f('district');
1245           $districts[$i]['district'] = $this->db->f('district');
1246           $districts[$i]['name'] = $this->db->f('name');
1247           $districts[$i]['supervisor'] = $this->db->f('supervisor');
1248           $sql = "SELECT * FROM eq_presidency where district=$district and valid=1";
1249           $this->db2->query($sql,__LINE__,__FILE__);
1250           if($this->db2->next_record()) {
1251             $districts[$i]['presidency'] = $this->db2->f('presidency');
1252           }
1253           $i++;
1254         }
1255
1256       // create the elder id -> elder name mapping
1257       $sql = "SELECT * FROM eq_elder where valid=1 ORDER BY name ASC";
1258       $this->db->query($sql,__LINE__,__FILE__);
1259       $i=0;
1260       $elder_id_data = NULL;
1261       $elder_name_data = NULL;
1262       while ($this->db->next_record())
1263         {
1264           $elder_name_data[$i] = $this->db->f('name');
1265           $elder_id_data[$i] = $this->db->f('elder');
1266           $i++;
1267         }
1268       array_multisort($elder_name_data, $elder_id_data);
1269
1270       // APPOINTMENT TABLE
1271       $district = 1;
1272       $date_width=150; $time_width=100; $elder_width=200;
1273       $appt_table_width=$date_width + $time_width + $elder_width;
1274       $appt_header_row = "<th width=$date_width><font size=-2>Date</th>";
1275       $appt_header_row.= "<th width=$time_width><font size=-2>Time</th>";
1276       $appt_header_row.= "<th width=$elder_width><font size=-2>Elder</th>";
1277       $appt_table_data = "";
1278
1279       $total_comps=0; $comps_with_quarterly_int=0;
1280
1281       // Display a scheduling table for each district
1282       for ($d=0; $d < count($districts); $d++) {
1283       $table_data=""; $appt_table_data="";
1284       $this->t->set_var('district_number',$districts[$d]['district']);
1285       $this->t->set_var('district_name',$districts[$d]['name']);
1286       $supervisor = $districts[$d]['supervisor'];
1287       $table_title = "District ".$districts[$d]['district'].": ".$districts[$d]['name'].": All Elders with Interviews Not Completed";
1288       $appt_table_title = "District ".$districts[$d]['district'].": ".$districts[$d]['name'].": Interview Appointment Slots";
1289       $this->t->set_var('table_title',$table_title);
1290       $this->t->set_var('appt_table_title',$appt_table_title);
1291
1292       // query the database for all the appointments
1293       $sql = "SELECT * FROM eq_appointment where presidency=".$districts[$d]['presidency']." and date>=CURDATE() ORDER BY date ASC, time ASC";
1294       $this->db->query($sql,__LINE__,__FILE__);
1295
1296       while ($this->db->next_record())
1297         {
1298           $appointment = $this->db->f('appointment');
1299           $elder = $this->db->f('elder');
1300
1301           $date = $this->db->f('date');
1302           $date_array = explode("-",$date);
1303           $year = $date_array[0]; $month = $date_array[1]; $day = $date_array[2];
1304           $day_string = date("l d-M-Y", mktime(0,0,0,$month,$day,$year));
1305
1306           $time = $this->db->f('time');
1307           $time_array = explode(":",$time);
1308           $time_string = date("g:i a", mktime($time_array[0], $time_array[1], $time_array[2]));
1309
1310           $appt_table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1311           $appt_table_data.= "<td align=center>$day_string</td>";
1312           $appt_table_data.= "<td align=center>$time_string</td>";
1313
1314           $appt_table_data.= '<td align=center><select name=appt_notes['.$appointment.'][elder]>';
1315           $appt_table_data.= '<option value=0></option>';
1316           for ($i=0; $i < count($elder_id_data); $i++) {
1317             $id = $elder_id_data[$i];
1318             $name = $elder_name_data[$i];
1319             if($elder_id_data[$i] == $elder) { $selected[$id] = 'selected="selected"'; } else { $selected[$id] = ''; }
1320             $appt_table_data.= '<option value='.$id.' '.$selected[$id].'>'.$name.'</option>';
1321           }
1322           $appt_table_data.='</select></td>';
1323
1324           $appt_table_data.= '<input type=hidden name="appt_notes['.$appointment.'][appointment]" value="'.$appointment.'">';
1325
1326           $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1327           $this->t->set_var('tr_color',$tr_color);
1328         }
1329
1330       $this->t->set_var('appt_table_data',$appt_table_data);
1331       $this->t->set_var('appt_header_row',$appt_header_row);
1332       $this->t->set_var('appt_table_width',$appt_table_width);
1333
1334       // INTERVIEW SCHEDULING TABLE
1335
1336       // Select all the unique companionship numbers for this district
1337       $sql = "SELECT distinct companionship FROM eq_companionship where valid=1 and district=". $districts[$d]['district'];
1338       $this->db->query($sql,__LINE__,__FILE__);
1339       $j=0;
1340       while ($this->db->next_record())
1341         {
1342           $unique_companionships[$j]['companionship'] = $this->db->f('companionship');
1343           $j++;
1344         }
1345
1346       $i=0;
1347       for ($j=0; $j < count($unique_companionships); $j++) {
1348         // Select all the companions from each companionship
1349         $sql = "SELECT * FROM eq_companionship where valid=1 and ".
1350            "companionship=". $unique_companionships[$j]['companionship'];
1351         $this->db->query($sql,__LINE__,__FILE__);
1352         $k=0; $int_completed=0;
1353         $comp = $unique_companionships[$j]['companionship'];
1354         $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1355         $this->t->set_var('tr_color',$tr_color);
1356         $total_comps++;
1357         while ($this->db->next_record())
1358             {
1359               // Get this companions information
1360               $elder_id = $this->db->f('elder');
1361               $aaronic_id = $this->db->f('aaronic');
1362
1363               $sql = "SELECT * FROM eq_elder where elder=$elder_id";
1364               $this->db2->query($sql,__LINE__,__FILE__);
1365               if($this->db2->next_record())
1366                 {
1367                   $elder_id = $this->db2->f('elder');
1368                   $elder_name = $this->db2->f('name');
1369                   $elder_phone[$elder_id] = $this->db2->f('phone');
1370                   $elder_int_pri[$elder_id] = $this->db2->f('int_pri');
1371                   $elder_int_notes[$elder_id] = $this->db2->f('int_notes');
1372                   $elder_aaronic = 0;
1373                 }
1374               else {
1375                 $sql = "SELECT * FROM eq_aaronic where aaronic=$aaronic_id";
1376                 $this->db2->query($sql,__LINE__,__FILE__);
1377                 if($this->db2->next_record())
1378                   {
1379                     $elder_id = $this->db2->f('aaronic');
1380                     $elder_name = $this->db2->f('name');
1381                     $elder_phone[$elder_id] = $this->db2->f('phone');
1382                     $elder_aaronic = 1;
1383                   }
1384               }
1385
1386               $id = $elder_id;
1387               $name = $elder_name;
1388               $phone = $elder_phone[$id];
1389               $int_pri = $elder_int_pri[$id];
1390               $int_notes = $elder_int_notes[$id];
1391               $aaronic = $elder_aaronic;
1392
1393               // If the companionship has already had its quarterly interview,
1394               // Skip the other companion in the companionship.
1395               if($int_completed == 1) {
1396                 $completed_data.= "<tr bgcolor=". $this->t->get_var('tr_color2') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
1397                 $completed_data.= "<td align=center>$phone</td>";
1398                 $completed_data.= "<td align=center><a href=".$link.">$date</a></td>";
1399                 $completed_data.= "<td align=left>$int_notes</td>";
1400                 $completed_data.= '</tr>';
1401                 $tr_color2 = $this->nextmatchs->alternate_row_color($tr_color2);
1402                 $this->t->set_var('tr_color2',$tr_color2);
1403                 $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1404                 $this->t->set_var('tr_color',$tr_color);
1405                 continue;
1406               }
1407
1408               // If this companionship has had a hometeaching interview this quarter, don't show them on the schedule list
1409               $sql = "SELECT * FROM eq_interview WHERE date >= '$quarter_start' AND date < '$quarter_end' ".
1410                  "AND elder=" . $id;
1411               $this->db2->query($sql,__LINE__,__FILE__);
1412
1413               if(!$this->db2->next_record()) {
1414                 $sql = "SELECT * FROM eq_interview WHERE elder=" . $id . " ORDER BY date DESC";
1415                 $this->db3->query($sql,__LINE__,__FILE__);
1416                 if($this->db3->next_record()) { $date = $this->db3->f('date'); } else { $date = ""; }
1417                 $link_data['menuaction'] = 'eq.eq.int_update';
1418                 $link_data['elder'] = $id;
1419                 $link_data['aaronic'] = 0;
1420                 $link_data['name'] = $name;
1421                 $link_data['interview'] = '';
1422                 $link_data['action'] = 'add';
1423                 $link = $GLOBALS['phpgw']->link('/eq/index.php',$link_data);
1424                 $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
1425                 $table_data.= "<td align=center>$phone</td>";
1426                 $table_data.= "<td align=center>";
1427                 $table_data.= '<select name=int_notes['.$i.'][pri]>';
1428                 foreach(range(0,6) as $num) {
1429                   if($num == 0) { $num = 1; } else {$num = $num*5; }
1430                   if($int_pri == $num) { $selected[$num] = 'selected="selected"'; } else { $selected[$num] = ''; }
1431                   $table_data.= '<option value='.$num.' '.$selected[$num].'>'.$num.'</option>';
1432                 }
1433                 $table_data.= '</select></td>';
1434                 $table_data.= "<td align=center>$date</td>";
1435                 $table_data.= '<td><input type=text size="50" maxlength="128" name="int_notes['.$i.'][notes]" value="'.$int_notes.'">';
1436                 $table_data.= '<input type=hidden name="int_notes['.$i.'][elder_id]" value="'.$id.'">';
1437                 $table_data.= '<input type=hidden name="int_notes['.$i.'][elder_name]" value="'.$name.'">';
1438                 $table_data.= '<input type=hidden name="int_notes['.$i.'][aaronic]" value="'.$aaronic.'">';
1439                 $table_data.= '</td>';
1440                 $table_data.= '</tr>';
1441                 $i++;
1442               } else {
1443                 $link_data['menuaction'] = 'eq.eq.int_update';
1444                 $link_data['interviewer'] = $this->db2->f('interviewer');
1445                 $link_data['elder'] = $this->db2->f('elder');
1446                 $link_data['aaronic'] = $this->db2->f('aaronic');
1447                 $link_data['name'] = $name;
1448                 $link_data['interview'] = $this->db2->f('int');
1449                 $link_data['action'] = 'view';
1450                 $link = $GLOBALS['phpgw']->link('/eq/index.php',$link_data);
1451                 $comps_with_quarterly_int++;
1452                 $int_completed=1;
1453                 $date = $this->db2->f('date');
1454                 $int_notes = $this->db2->f('notes');
1455                 if(strlen($int_notes) > 40) { $int_notes = substr($int_notes,0,40) . "..."; }
1456                 $completed_data.= "<tr bgcolor=". $this->t->get_var('tr_color2') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
1457                 $completed_data.= "<td align=center>$phone</td>";
1458                 $completed_data.= "<td align=center><a href=".$link.">$date</a></td>";
1459                 $completed_data.= "<td align=left>$int_notes</td>";
1460                 $completed_data.= '</tr>';
1461               }
1462             }
1463       }
1464
1465       $name_width=175; $phone_width=100; $date_width=100; $notes_width=300;
1466       $completed_table_width=$name_width + $phone_width + $date_width + $notes_width;
1467       $completed_header_row = "<th width=$name_width><font size=-2>Elder Name</th>";
1468       $completed_header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
1469       $completed_header_row.= "<th width=$date_width><font size=-2>Date</th>";
1470       $completed_header_row.= "<th width=$notes_width><font size=-2>Interview Notes</th>";
1471
1472       $this->t->set_var('table_width',$table_width);
1473       $this->t->set_var('header_row',$header_row);
1474       $this->t->set_var('table_data',$table_data);
1475       $this->t->set_var('completed_header_row',$completed_header_row);
1476       $this->t->set_var('completed_table_width',$completed_table_width);
1477       $this->t->set_var('completed',$completed_data);
1478       $this->t->fp('elderlist','elder_list',True);
1479
1480       } // End for each district loop
1481
1482
1483       $elders_width=300; $totals_width=100;
1484       $totals_table_width=$elders_width + $totals_width;
1485       $totals_header_row = "<th width=$elders_width><font size=-2>Elders</th>";
1486       $totals_header_row.= "<th width=$totals_width><font size=-2>$year</th>";
1487       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1488       $totals_data.= "<td align=left><font size=-2><b>Total Companionships with interviews completed:</b></font></td>";
1489       $totals_data.= "<td align=center><font size=-2><b>$comps_with_quarterly_int / $total_comps</b></font></td>";
1490       $percent = ceil(($comps_with_quarterly_int / $total_comps)*100);
1491       $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1492       $this->t->set_var('tr_color',$tr_color);
1493       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1494       $totals_data.= "<td align=left><font size=-2><b>Percentage:</b></font></td>";
1495       $totals_data.= "<td align=center><font size=-2><b>$percent%</b></font></td>";
1496       $totals_data.= "</tr>";
1497
1498       $this->t->set_var('totals',$totals_data);
1499       $this->t->set_var('totals_header_row',$totals_header_row);
1500       $this->t->set_var('totals_table_width',$totals_table_width);
1501
1502       $this->t->pfp('out','int_sched_t');
1503       $this->save_sessiondata();
1504
1505     }
1506
1507   function vis_sched()
1508     {
1509       $this->t->set_file(array('vis_sched_t' => 'vis_sched.tpl'));
1510       $this->t->set_block('vis_sched_t','family_list','familylist');
1511       $this->t->set_block('vis_sched_t','appt_list','apptlist');
1512       $action = get_var('action',array('GET','POST'));
1513
1514       $this->t->set_var('lang_save','Save Appt / Pri / Notes');
1515       $this->t->set_var('lang_reset','Clear Changes');
1516
1517       $this->t->set_var('vis_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_view'));
1518       $this->t->set_var('vis_link_title','View Yearly Visits');
1519
1520       $this->t->set_var('schedule_vis_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_sched'));
1521       $this->t->set_var('schedule_vis_link_title','Schedule Yearly Visits');
1522
1523       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_sched&action=save'));
1524       $this->t->set_var('title','EQ Presidency Yearly Visit Scheduler');
1525
1526       $family_width=500; $phone_width=40; $pri_width=10; $notes_width=128; $visit_date_width=20;
1527       $table_width=$family_width + $phone_width + $pri_width + $notes_width + $visit_date_width;
1528       $header_row = "<th width=$family_width><font size=-2>Family Name</th>";
1529       $header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
1530       $header_row.= "<th width=$pri_width><font size=-2>Priority</th>";
1531       $header_row.= "<th width=$visit_date_width><font size=-2>Last Visit</th>";
1532       $header_row.= "<th width=$notes_width><font size=-2>Scheduling Notes</th>";
1533       $table_data=""; $completed_data=""; $totals_data="";
1534
1535       $year = date('Y');
1536
1537       if($action == 'save')
1538         {
1539           // Save any changes made to the appointment table
1540           $new_data = get_var('appt_notes',array('POST'));
1541           foreach ($new_data as $entry)
1542            {
1543              $family = $entry['family'];
1544              $appointment = $entry['appointment'];
1545
1546              // Perform database save actions here
1547              $this->db->query("UPDATE eq_appointment set " .
1548                               " family='" . $family . "'" .
1549                               " WHERE appointment=" . $appointment,__LINE__,__FILE__);
1550
1551              // Email the appointment
1552              $this->email_appt($appointment);
1553            }
1554
1555           // Save any changes made to the visit notes table
1556           $new_data = get_var('vis_notes',array('POST'));
1557           foreach ($new_data as $entry)
1558            {
1559              $visit_notes = $entry['notes'];
1560              $family_id = $entry['family_id'];
1561              $visit_pri = $entry['pri'];
1562
1563              // Perform database save actions here
1564              $this->db->query("UPDATE eq_family set " .
1565                               " visit_notes='" . $visit_notes . "'" .
1566                               ",visit_pri='" . $visit_pri . "'" .
1567                               " WHERE family=" . $family_id,__LINE__,__FILE__);
1568
1569            }
1570
1571           $take_me_to_url = $GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_sched');
1572           Header('Location: ' . $take_me_to_url);
1573         }
1574
1575       // APPOINTMENT TABLE
1576       $date_width=150; $time_width=100; $family_width=250;
1577       $appt_table_width=$date_width + $time_width + $family_width;
1578       $appt_header_row = "<th width=$date_width><font size=-2>Date</th>";
1579       $appt_header_row.= "<th width=$time_width><font size=-2>Time</th>";
1580       $appt_header_row.= "<th width=$family_width><font size=-2>Family</th>";
1581       $appt_table_data = "";
1582
1583       // Find out what the EQ Presidency ID is
1584       $sql = "SELECT * FROM eq_presidency where president=0 and counselor=0 and secretary=0 and valid=1";
1585       $this->db->query($sql,__LINE__,__FILE__);
1586       if($this->db->next_record()) {
1587         $presidency_name = $this->db->f('name');
1588         $presidency_id = $this->db->f('presidency');
1589       } else {
1590         print "<hr><font color=red><h3>-E- Unable to locate EQ Presidency in eq_presidency table</h3></font></hr>";
1591         return;
1592       }
1593
1594       // create the family id -> family name mapping
1595       $sql = "SELECT * FROM eq_family where valid=1 and elder_id != 0 ORDER BY name ASC";
1596       $this->db->query($sql,__LINE__,__FILE__);
1597       $i=0;
1598       $family_id = NULL;
1599       while ($this->db->next_record())
1600         {
1601           $family_id[$i] = $this->db->f('family');
1602           $family_name[$i] = $this->db->f('name');
1603           $i++;
1604         }
1605       array_multisort($family_name, $family_id);
1606
1607       // query the database for all the appointments
1608       $sql = "SELECT * FROM eq_appointment where presidency=$presidency_id and date>=CURDATE() ORDER BY date ASC, time ASC";
1609       $this->db->query($sql,__LINE__,__FILE__);
1610
1611       while ($this->db->next_record())
1612         {
1613           $appointment = $this->db->f('appointment');
1614           $family = $this->db->f('family');
1615
1616           $date = $this->db->f('date');
1617           $date_array = explode("-",$date);
1618           $year = $date_array[0]; $month = $date_array[1]; $day = $date_array[2];
1619           $day_string = date("l d-M-Y", mktime(0,0,0,$month,$day,$year));
1620
1621           $time = $this->db->f('time');
1622           $time_array = explode(":",$time);
1623           $time_string = date("g:i a", mktime($time_array[0], $time_array[1], $time_array[2]));
1624
1625           $appt_table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1626           $appt_table_data.= "<td align=center>$day_string</td>";
1627           $appt_table_data.= "<td align=center>$time_string</td>";
1628
1629           $appt_table_data.= '<td align=center><select name=appt_notes['.$appointment.'][family]>';
1630           $appt_table_data.= '<option value=0></option>';
1631           for ($i=0; $i < count($family_id); $i++) {
1632             $id = $family_id[$i];
1633             $name = $family_name[$i];
1634             if($family_id[$i] == $family) { $selected[$id] = 'selected="selected"'; } else { $selected[$id] = ''; }
1635             $appt_table_data.= '<option value='.$id.' '.$selected[$id].'>'.$name.' Family</option>';
1636           }
1637           $appt_table_data.='</select></td>';
1638
1639           $appt_table_data.= '<input type=hidden name="appt_notes['.$appointment.'][appointment]" value="'.$appointment.'">';
1640
1641           $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1642           $this->t->set_var('tr_color',$tr_color);
1643         }
1644
1645       $this->t->set_var('appt_table_data',$appt_table_data);
1646       $this->t->set_var('appt_header_row',$appt_header_row);
1647       $this->t->set_var('appt_table_width',$appt_table_width);
1648
1649
1650       // VISIT SCHEDULING TABLE
1651       $sql = "SELECT * FROM eq_family where valid=1 and elder_id != 0 ORDER BY visit_pri ASC";
1652       $this->db->query($sql,__LINE__,__FILE__);
1653
1654       $total_families=0; $families_with_yearly_visit=0;
1655
1656       $i=0;
1657       $family_id = NULL;
1658       $family_name = NULL;
1659       $family_phone = NULL;
1660       $family_visit_pri = NULL;
1661       $family_visit_notes = NULL;
1662       while ($this->db->next_record())
1663         {
1664           $family_id[$i] = $this->db->f('family');
1665           $family_name[$i] = $this->db->f('name');
1666           $family_phone[$family_id[$i]] = $family_id[$i] . " ERROR";
1667           $family_visit_pri[$family_id[$i]] = $this->db->f('visit_pri');
1668           $family_visit_notes[$family_id[$i]] = $this->db->f('visit_notes');
1669           $i++;
1670           $total_families++;
1671         }
1672
1673       $sql = "SELECT * FROM eq_parent where valid=1";
1674       $this->db->query($sql,__LINE__,__FILE__);
1675       while ($this->db->next_record())
1676         {
1677           $family = $this->db->f('family');
1678           $phone = $this->db->f('phone');
1679           $family_phone[$family] = $phone;
1680         }
1681
1682       $max = count($family_id);
1683
1684       for($i=0; $i < $max; $i++) {
1685           $id = $family_id[$i];
1686           $name = $family_name[$i];
1687           $phone = $family_phone[$id];
1688           $vis_pri = $family_visit_pri[$id];
1689           $vis_notes = $family_visit_notes[$id];
1690
1691           // If this family has had a yearly visit this year, don't show them on the schedule list
1692           $year_start = $year - 1 . "-12-31"; $year_end = $year + 1 . "-01-01";
1693           $sql = "SELECT * FROM eq_visit WHERE date > '$year_start' AND date < '$year_end' ".
1694              "AND family=" . $id . " AND companionship=0";
1695           $this->db2->query($sql,__LINE__,__FILE__);
1696
1697           if(!$this->db2->next_record()) {
1698             $sql = "SELECT * FROM eq_visit WHERE family=" . $id . " AND companionship=0 ORDER BY date DESC";
1699             $this->db->query($sql,__LINE__,__FILE__);
1700             if($this->db->next_record()) { $date = $this->db->f('date'); } else { $date = ""; }
1701             $link_data['menuaction'] = 'eq.eq.vis_update';
1702             $link_data['visit'] = '';
1703             $link_data['family'] = $id;
1704             $link_data['name'] = $name;
1705             $link_data['action'] = 'add';
1706             $link = $GLOBALS['phpgw']->link('/eq/index.php',$link_data);
1707             $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') ."><td title=\"$phone\"><a href=$link>$name Family</a></td>";
1708             $table_data.= "<td align=center>$phone</td>";
1709             $table_data.= "<td align=center>";
1710             $table_data.= '<select name=vis_notes['.$i.'][pri]>';
1711             foreach(range(0,6) as $num) {
1712               if($num == 0) { $num = 1; } else {$num = $num*5; }
1713               if($vis_pri == $num) { $selected[$num] = 'selected="selected"'; } else { $selected[$num] = ''; }
1714               $table_data.= '<option value='.$num.' '.$selected[$num].'>'.$num.'</option>';
1715             }
1716             $table_data.= '</select></td>';
1717             $table_data.= "<td align=center>$date</td>";
1718             $table_data.= '<td><input type=text size="50" maxlength="128" name="vis_notes['.$i.'][notes]" value="'.$vis_notes.'">';
1719             $table_data.= '<input type=hidden name="vis_notes['.$i.'][family_id]" value="'.$id.'">';
1720             $table_data.= '<input type=hidden name="vis_notes['.$i.'][family_name]" value="'.$name.'">';
1721             $table_data.= '</td>';
1722             $table_data.= '</tr>';
1723             $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1724             $this->t->set_var('tr_color',$tr_color);
1725           } else {
1726             $link_data['menuaction'] = 'eq.eq.vis_update';
1727             $link_data['visit'] = $this->db2->f('visit');
1728             $link_data['family'] = $this->db2->f('family');
1729             $link_data['name'] = $name;
1730             $link_data['date'] = $this->db2->f('date');
1731             $link_data['action'] = 'view';
1732             $link = $GLOBALS['phpgw']->link('/eq/index.php',$link_data);
1733             $families_with_yearly_visit++;
1734             $date = $this->db2->f('date');
1735             $vis_notes = $this->db2->f('notes');
1736             if(strlen($vis_notes) > 40) { $vis_notes = substr($vis_notes,0,40) . "..."; }
1737             $completed_data.= "<tr bgcolor=". $this->t->get_var('tr_color2') ."><td title=\"$phone\"><a href=$link>$name Family</a></td>";
1738             $completed_data.= "<td align=center>$phone</td>";
1739             $completed_data.= "<td align=center><a href=".$link.">$date</a></td>";
1740             $completed_data.= "<td align=left>$vis_notes</td>";
1741             $completed_data.= '</tr>';
1742             $tr_color2 = $this->nextmatchs->alternate_row_color($tr_color2);
1743             $this->t->set_var('tr_color2',$tr_color2);
1744           }
1745       }
1746
1747       $name_width=175; $phone_width=100; $date_width=100; $notes_width=300;
1748       $completed_table_width=$name_width + $phone_width + $date_width + $notes_width;
1749       $completed_header_row = "<th width=$name_width><font size=-2>Family Name</th>";
1750       $completed_header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
1751       $completed_header_row.= "<th width=$date_width><font size=-2>Date</th>";
1752       $completed_header_row.= "<th width=$notes_width><font size=-2>Visit Notes</th>";
1753
1754       $family_width=300; $totals_width=100;
1755       $totals_table_width=$family_width + $totals_width;
1756       $totals_header_row = "<th width=$family_width><font size=-2>Families</th>";
1757       $totals_header_row.= "<th width=$totals_width><font size=-2>$year</th>";
1758       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1759       $totals_data.= "<td align=left><font size=-2><b>Total Families with yearly Visits completed:</b></font></td>";
1760       $totals_data.= "<td align=center><font size=-2><b>$families_with_yearly_visit / $total_families</b></font></td>";
1761       $percent = ceil(($families_with_yearly_visit / $total_families)*100);
1762       $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1763       $this->t->set_var('tr_color',$tr_color);
1764       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1765       $totals_data.= "<td align=left><font size=-2><b>Percentage:</b></font></td>";
1766       $totals_data.= "<td align=center><font size=-2><b>$percent%</b></font></td>";
1767       $totals_data.= "</tr>";
1768
1769       $this->t->set_var('table_width',$table_width);
1770       $this->t->set_var('header_row',$header_row);
1771       $this->t->set_var('table_data',$table_data);
1772       $this->t->set_var('totals_header_row',$totals_header_row);
1773       $this->t->set_var('totals_table_width',$totals_table_width);
1774       $this->t->set_var('completed_header_row',$completed_header_row);
1775       $this->t->set_var('completed_table_width',$completed_table_width);
1776       $this->t->set_var('completed',$completed_data);
1777       $this->t->set_var('totals',$totals_data);
1778       $this->t->fp('familylist','family_list',True);
1779       $this->t->fp('apptlist','appt_list',True);
1780
1781       $this->t->pfp('out','vis_sched_t');
1782       $this->save_sessiondata();
1783
1784     }
1785
1786   function ppi_view()
1787     {
1788       $this->t->set_file(array('ppi_view_t' => 'ppi_view.tpl'));
1789       $this->t->set_block('ppi_view_t','district_list','list');
1790
1791       $this->t->set_var('linkurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_view'));
1792       $num_months = get_var('num_months',array('GET','POST'));
1793       if($num_months == '') { $num_months = $this->default_ppi_num_months; }
1794       $this->t->set_var('num_months',$num_months);
1795       if($num_months == 1) { $this->t->set_var('lang_num_months','Month of History'); }
1796       else {  $this->t->set_var('lang_num_months','Months of History'); }
1797       $this->t->set_var('lang_filter','Filter');
1798       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_view'));
1799
1800       $this->t->set_var('ppi_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_view'));
1801       $this->t->set_var('ppi_link_title','Yearly PPIs');
1802
1803       $this->t->set_var('schedule_ppi_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_sched'));
1804       $this->t->set_var('schedule_ppi_link_title','Schedule Yearly PPIs');
1805
1806       $this->t->set_var('title','Yearly PPIs');
1807       $num_months = get_var('num_months',array('GET','POST'));
1808       if($num_months == '') { $num_months = $this->default_ppi_num_years; }
1809       $this->t->set_var('num_months',$num_months);
1810       if($num_months == 1) { $this->t->set_var('lang_num_months','Year of History'); }
1811       else { $this->t->set_var('lang_num_months','Years of History'); }
1812
1813       $sql = "SELECT * FROM eq_presidency where president=1 and valid=1";
1814       $this->db->query($sql,__LINE__,__FILE__);
1815       if($this->db->next_record()) {
1816         $president_name = $this->db->f('name');
1817         $interviewer = $this->db->f('elder');
1818         $eqpresppi = 1;
1819       } else {
1820         print "<hr><font color=red><h3>-E- Unable to locate EQ President in eq_presidency table</h3></font></hr>";
1821         return;
1822       }
1823       $this->t->set_var('district_number','*');
1824       $this->t->set_var('district_name',$president_name);
1825
1826       $sql = "SELECT * FROM eq_elder where valid=1 ORDER BY elder ASC";
1827       $this->db->query($sql,__LINE__,__FILE__);
1828       $i=0;
1829       while ($this->db->next_record())
1830         {
1831           $elder_id[$i] = $this->db->f('elder');
1832           $elder_name[$i] = $this->db->f('name');
1833           $elder_phone[$elder_id[$i]] = $this->db->f('phone');
1834           $elder_ppi_pri[$elder_id[$i]] = $this->db->f('ppi_pri');
1835           $elder_ppi_notes[$elder_id[$i]] = $this->db->f('ppi_notes');
1836           $i++;
1837         }
1838       $total_elders=$i;
1839       array_multisort($elder_name, $elder_id);
1840       //var_dump($elder_name); print "<br><br>"; var_dump($elder_id);
1841
1842       $header_row="<th width=$comp_width><font size=-2>Elder Name</th>";
1843
1844       $elder_width=400; $ppi_width=75; $table_width=$elder_width + $num_months*$ppi_width;
1845       $table_data="";
1846       for($m=$num_months; $m >= 0; $m--) {
1847         $year = date('Y') - $m;
1848         $header_row .= "<th width=150><font size=-2>$year</th>";
1849         $ppis[$m] = 0;
1850       }
1851
1852       for ($j=0; $j < count($elder_id); $j++) {
1853         $id = $elder_id[$j];
1854         $name = $elder_name[$j];
1855         $phone = $elder_phone[$id];
1856
1857         $link_data['menuaction'] = 'eq.eq.ppi_update';
1858         $link_data['interviewer'] = $interviewer;
1859         $link_data['elder'] = $id;
1860         $link_data['name'] = $name;
1861         $link_data['ppi'] = '';
1862         $link_data['eqpresppi'] = $eqpresppi;
1863         $link_data['action'] = 'add';
1864         $link = $GLOBALS['phpgw']->link('/eq/index.php',$link_data);
1865         $this->nextmatchs->template_alternate_row_color(&$this->t);
1866         $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
1867
1868         // Find out how many times PPIs were performed in the past $num_months for this Elder
1869         for($m=$num_months; $m >= 0; $m--) {
1870           $year = date('Y') - $m;
1871           $year_start = $year - 1 . "-12-31"; $year_end = $year + 1 . "-01-01";
1872           $sql = "SELECT * FROM eq_ppi WHERE date > '$year_start' AND date < '$year_end' ".
1873              "AND elder=" . $id;
1874           $this->db2->query($sql,__LINE__,__FILE__);
1875
1876           if(!$total_ppis[$m]) { $total_ppis[$m] = 0; }
1877           if($this->db2->next_record()) {
1878             $ppis[$m]++; $total_ppis[$m]++; $ppi_recorded[$companionship][$m]=1;
1879             $link_data['menuaction'] = 'eq.eq.ppi_update';
1880             $link_data['companionship'] = $companionship;
1881             $link_data['interviewer'] = $this->db2->f('interviewer');
1882             $link_data['elder'] = $id;
1883             $link_data['name'] = $name;
1884             $link_data['ppi'] = $this->db2->f('ppi');
1885             $link_data['eqpresppi'] = $eqpresppi;
1886             $link_data['action'] = 'view';
1887             $date = $this->db2->f('date');
1888             $date_array = explode("-",$date);
1889             $month = $date_array[1];
1890             $day   = $date_array[2];
1891             $link = $GLOBALS['phpgw']->link('/eq/index.php',$link_data);
1892             $table_data .= '<td align=center><a href='.$link.'><img src="checkmark.gif">&nbsp;'.$month.'-'.$day.'</a></td>';
1893           }
1894           else { $table_data .= "<td>&nbsp;</td>"; }
1895         }
1896         $table_data .= "</tr>\n";
1897       }
1898       $table_data .= "<tr><td colspan=20><hr></td></tr>";
1899
1900       $stat_data = "<tr><td><b><font size=-2>$total_elders Elders<br>PPI Totals:</font></b></td>";
1901       for($m=$num_months; $m >=0; $m--) {
1902         $percent = ceil(($ppis[$m] / $total_elders)*100);
1903         $stat_data .= "<td align=center><font size=-2><b>$ppis[$m]<br>$percent%</font></b></td>";
1904       }
1905       $stat_data .= "</tr>";
1906
1907       $this->t->set_var('table_width',$table_width);
1908       $this->t->set_var('header_row',$header_row);
1909       $this->t->set_var('table_data',$table_data);
1910       $this->t->set_var('stat_data',$stat_data);
1911       $this->t->pfp('out','ppi_view_t');
1912       $this->save_sessiondata();
1913     }
1914
1915   function ppi_update()
1916     {
1917       $this->t->set_file(array('form' => 'ppi_update.tpl'));
1918       $this->t->set_block('form','interviewer_list','int_list');
1919       $this->t->set_block('form','add','addhandle');
1920       $this->t->set_block('form','edit','edithandle');
1921
1922       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_view'));
1923       $this->t->set_var('readonly','');
1924       $this->t->set_var('disabled','');
1925
1926       $action = get_var('action',array('GET','POST'));
1927       $companionship = get_var('companionship',array('GET','POST'));
1928       $interviewer = get_var('interviewer',array('GET','POST'));
1929       $name = get_var('name',array('GET','POST'));
1930       $ppi = get_var('ppi',array('GET','POST'));
1931       $elder = get_var('elder',array('GET','POST'));
1932       $aaronic = get_var('aaronic',array('GET','POST'));
1933       $date = get_var('date',array('GET','POST'));
1934       $notes = get_var('notes',array('GET','POST'));
1935       $eqpresppi = get_var('eqpresppi',array('GET','POST'));
1936
1937       $sql = "SELECT * FROM eq_district where valid=1 ORDER BY district ASC";
1938       $this->db->query($sql,__LINE__,__FILE__);
1939       while ($this->db->next_record())
1940         {
1941           $supervisor = $this->db->f('supervisor');
1942           $sql = "SELECT * FROM eq_elder WHERE elder=" . $supervisor;
1943           $this->db2->query($sql,__LINE__,__FILE__);
1944           $this->db2->next_record();
1945           $interviewer_name = $this->db2->f('name');
1946
1947           if($supervisor == $interviewer) {
1948             $this->t->set_var('interviewer',$supervisor . ' selected');
1949           } else {
1950             $this->t->set_var('interviewer',$interviewer);
1951           }
1952           $this->t->set_var('interviewer_name',$interviewer_name);
1953           $this->t->fp('int_list','interviewer_list',True);
1954         }
1955
1956       if($action == 'save')
1957         {
1958           $notes = $this->db->db_addslashes(get_var('notes',array('POST')));
1959           $this->db->query("UPDATE eq_ppi set " .
1960                            "   ppi='" . $ppi . "'" .
1961                     ", interviewer='" . $interviewer . "'" .
1962                           ", elder='" . $elder . "'" .
1963                            ", date='" . $date . "'" .
1964                           ", notes='" . $notes . "'" .
1965                       ", eqpresppi='" . $eqpresppi . "'" .
1966                            " WHERE ppi=" . $ppi,__LINE__,__FILE__);
1967           $this->ppi_view();
1968           return false;
1969         }
1970
1971       if($action == 'insert')
1972         {
1973           $notes = $this->db->db_addslashes(get_var('notes',array('POST')));
1974           $this->db->query("INSERT INTO eq_ppi (interviewer,elder,date,notes,eqpresppi) "
1975                            . "VALUES ('" . $interviewer . "','" . $elder . "','"
1976                            . $date . "','" . $notes . "','" . $eqpresppi  ."')",__LINE__,__FILE__);
1977           $this->ppi_view();
1978           return false;
1979         }
1980
1981       if($action == 'add')
1982         {
1983           $this->t->set_var('cal_date',$this->jscal->input('date','','','','','','',$this->cal_options));
1984           $this->t->set_var('ppi', '');
1985           $this->t->set_var('interviewer', $interviewer);
1986           $this->t->set_var('name',$name);
1987           $this->t->set_var('elder',$elder);
1988           $this->t->set_var('date','');
1989           $this->t->set_var('notes','');
1990           $this->t->set_var('eqpresppi',$eqpresppi);
1991           $this->t->set_var('lang_done','Cancel');
1992           $this->t->set_var('lang_action','Adding New PPI');
1993           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_update&ppi='
1994                                                                 . $ppi . '&action=' . 'insert'));
1995         }
1996
1997       if($action == 'edit' || $action == 'view')
1998         {
1999           $sql = "SELECT * FROM eq_ppi WHERE ppi=".$ppi;
2000           $this->db->query($sql,__LINE__,__FILE__);
2001           $this->db->next_record();
2002           $this->t->set_var('ppi',$ppi);
2003           $this->t->set_var('name',$name);
2004           $this->t->set_var('interviewer', $this->db->f('interviewer'));
2005           $this->t->set_var('elder',$this->db->f('elder'));
2006           $this->t->set_var('date',$this->db->f('date'));
2007           $this->t->set_var('notes',$this->db->f('notes'));
2008           $this->t->set_var('eqpresppi',$this->db->f('eqpresppi'));
2009         }
2010
2011       if($action == 'edit')
2012         {
2013           $this->t->set_var('cal_date',$this->jscal->input('date',$date,'','','','','',$this->cal_options));
2014           $this->t->set_var('lang_done','Cancel');
2015           $this->t->set_var('lang_action','Editing PPI');
2016           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_update&ppi='
2017                                                                 . $ppi . '&action=' . 'save'));
2018         }
2019
2020       if($action == 'view')
2021         {
2022           $date = $this->db->f('date');
2023           $this->t->set_var('cal_date','<input type=text size="10" maxlength="10" name="date" value="'.$date.'" readonly>');
2024           $this->t->set_var('readonly','READONLY');
2025           $this->t->set_var('disabled','DISABLED');
2026           $this->t->set_var('lang_done','Done');
2027           $this->t->set_var('lang_action','Viewing PPI');
2028           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_update&ppi='
2029                                                                 . $ppi . '&action=' . 'edit'));
2030         }
2031
2032       $this->t->set_var('lang_reset','Clear Form');
2033       $this->t->set_var('lang_add','Add PPI');
2034       $this->t->set_var('lang_save','Save Changes');
2035       $this->t->set_var('edithandle','');
2036       $this->t->set_var('addhandle','');
2037
2038       $this->t->pfp('out','form');
2039
2040       if($action == 'view') { $this->t->set_var('lang_save','Edit PPI'); }
2041       if($action == 'edit' || $action == 'view') { $this->t->pfp('addhandle','edit'); }
2042       if($action == 'add') { $this->t->pfp('addhandle','add'); }
2043
2044       $this->save_sessiondata();
2045     }
2046
2047     function int_view()
2048     {
2049       $this->t->set_file(array('int_view_t' => 'int_view.tpl'));
2050       $this->t->set_block('int_view_t','district_list','list');
2051
2052       $this->t->set_var('linkurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.int_view'));
2053       $num_quarters = get_var('num_quarters',array('GET','POST'));
2054       if($num_quarters == '') { $num_quarters = $this->default_int_num_quarters; }
2055       $this->t->set_var('num_quarters',$num_quarters);
2056       if($num_quarters == 1) { $this->t->set_var('lang_num_quarters','Quarter of History'); }
2057       else {  $this->t->set_var('lang_num_quarters','Quarters of History'); }
2058       $this->t->set_var('lang_filter','Filter');
2059
2060       $this->t->set_var('int_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.int_view'));
2061       $this->t->set_var('int_link_title','Hometeaching Interviews');
2062
2063       $this->t->set_var('schedule_int_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.int_sched'));
2064       $this->t->set_var('schedule_int_link_title','Schedule Hometeaching Interviews');
2065
2066       $this->t->set_var('title','Hometeaching Interviews');
2067
2068       $num_months = $num_quarters * 3 - 1;
2069       $current_month = $this->current_month;
2070       if($current_month >= 1 && $current_month <= 3) { $current_month=3; }
2071       else if($current_month >= 4 && $current_month <= 6) { $current_month=6; }
2072       else if($current_month >= 7 && $current_month <= 9) { $current_month=9; }
2073       else if($current_month >= 10 && $current_month <= 12) { $current_month=12; }
2074
2075       $sql = "SELECT * FROM eq_district where valid=1 ORDER BY district ASC";
2076       $this->db->query($sql,__LINE__,__FILE__);
2077       $i=0;
2078       while ($this->db->next_record())
2079         {
2080           $districts[$i]['district'] = $this->db->f('district');
2081           $districts[$i]['name'] = $this->db->f('name');
2082           $districts[$i]['supervisor'] = $this->db->f('supervisor');
2083           $i++;
2084         }
2085
2086       $sql = "SELECT * FROM eq_elder where valid=1 ORDER BY elder ASC";
2087       $this->db->query($sql,__LINE__,__FILE__);
2088       $i=0;
2089       while ($this->db->next_record())
2090         {
2091           $elder_id[$i] = $this->db->f('elder');
2092           $elder_name[$i] = $this->db->f('name');
2093           $elder_phone[$elder_id[$i]] = $this->db->f('phone');
2094           $i++;
2095         }
2096       array_multisort($elder_name, $elder_id);
2097       for($i=0; $i < count($elder_id); $i++) {
2098           $id = $elder_id[$i];
2099           $elders[$id] = $elder_name[$i];
2100       }
2101
2102       $sql = "SELECT * FROM eq_aaronic where valid=1 ORDER BY aaronic ASC";
2103       $this->db->query($sql,__LINE__,__FILE__);
2104       while ($this->db->next_record())
2105         {
2106           $aaronic_id = $this->db->f('aaronic');
2107           $aaronic[$aaronic_id]['name'] = $this->db->f('name');
2108           $aaronic[$aaronic_id]['phone'] = $this->db->f('phone');
2109         }
2110
2111       $total_companionships = 0;
2112       $this->nextmatchs->template_alternate_row_color(&$this->t);
2113       for ($i=0; $i < count($districts); $i++) {
2114         $this->t->set_var('district_number',$districts[$i]['district']);
2115         $this->t->set_var('district_name',$districts[$i]['name']);
2116         $supervisor = $districts[$i]['supervisor'];
2117         $unique_companionships='';
2118
2119         // Select all the unique companionship numbers for this district
2120         $sql = "SELECT distinct companionship FROM eq_companionship where valid=1 and district=". $districts[$i]['district'];
2121
2122         $this->db->query($sql,__LINE__,__FILE__);
2123         $j=0;
2124         while ($this->db->next_record())
2125           {
2126             $unique_companionships[$j]['companionship'] = $this->db->f('companionship');
2127             $j++;
2128           }
2129
2130         $comp_width=250; $int_width=75; $table_width=$comp_width + $num_months*$int_width;
2131         $table_data=""; $num_companionships = $j; $num_elders = 0;
2132         for($m=$num_months; $m >= 0; $m--) { $ints[$m] = 0; }
2133         for ($j=0; $j < count($unique_companionships); $j++) {
2134           // Select all the companions in each companionship
2135           $sql = "SELECT * FROM eq_companionship where valid=1 and ".
2136              "companionship=". $unique_companionships[$j]['companionship'];
2137           $this->db->query($sql,__LINE__,__FILE__);
2138           $k=0;
2139           $comp = $unique_companionships[$j]['companionship'];
2140           for($m=$num_months; $m >= 0; $m--) { $int_recorded[$comp][$m] = 0; }
2141           while ($this->db->next_record())
2142             {
2143               // Get this companions information
2144               $num_elders++;
2145               $companionship = $this->db->f('companionship');
2146               $elder_id = $this->db->f('elder');
2147               $aaronic_id = $this->db->f('aaronic');
2148               if($elder_id) {
2149                 $name = $elders[$elder_id];
2150                 $phone = $elder_phone[$elder_id];
2151               }
2152               else if($aaronic_id) {
2153                 $name = $aaronic[$aaronic_id]['name'];
2154                 $phone = $aaronic[$aaronic_id]['phone'];
2155               }
2156               $link_data['menuaction'] = 'eq.eq.int_update';
2157               $link_data['companionship'] = $companionship;
2158               $link_data['interviewer'] = $supervisor;
2159               $link_data['elder'] = $elder_id;
2160               $link_data['aaronic'] = $aaronic_id;
2161               $link_data['name'] = $name;
2162               $link_data['interview'] = '';
2163               $link_data['action'] = 'add';
2164               $link = $GLOBALS['phpgw']->link('/eq/index.php',$link_data);
2165               $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
2166
2167               // Find out how many times Interviews were performed in the past $num_months for this Elder
2168               $header_row="<th width=$comp_width><font size=-2>Companionship</th>";
2169               for($m=$num_months; $m >= 0; $m--) {
2170                 $month = $current_month - $m;
2171                 $year = $this->current_year;
2172                 if($month <= 0) { $remainder = $month; $month = 12 + $remainder; $year=$year-1; }
2173                 if($month < 10) { $month = "0"."$month"; }
2174                 $month_start = "$year"."-"."$month"."-"."01";
2175                 $month_end = "$year"."-"."$month"."-"."31";
2176                 $month = "$month"."/"."$year";
2177                 $sql = "SELECT * FROM eq_interview WHERE date >= '$month_start' AND date <= '$month_end' ".
2178                    "AND elder=" . $elder_id . " AND aaronic=" . $aaronic_id;
2179                 $this->db2->query($sql,__LINE__,__FILE__);
2180                 $header_row .= "<th width=$int_width><font size=-2>$month</th>";
2181
2182                 if(!$total_ints[$m]) { $total_ints[$m] = 0; }
2183                 if($this->db2->next_record()) {
2184                   if(!$int_recorded[$companionship][$m]) {
2185                     $ints[$m]++; $total_ints[$m]++; $int_recorded[$companionship][$m]=1;
2186                   }
2187                   $link_data['menuaction'] = 'eq.eq.int_update';
2188                   $link_data['companionship'] = $companionship;
2189                   $link_data['interviewer'] = $this->db2->f('interviewer');
2190                   $link_data['elder'] = $elder_id;
2191                   $link_data['aaronic'] = $aaronic_id;
2192                   $link_data['name'] = $name;
2193                   $link_data['interview'] = $this->db2->f('interview');
2194                   $link_data['action'] = 'view';
2195                   $date = $this->db2->f('date');
2196                   $date_array = explode("-",$date);
2197                   $month = $date_array[1];
2198                   $day   = $date_array[2];
2199                   $link = $GLOBALS['phpgw']->link('/eq/index.php',$link_data);
2200                   $table_data .= '<td align=center><a href='.$link.'><img src="checkmark.gif">&nbsp;'.$month.'-'.$day.'</a></td>';
2201                 }
2202                 else { $table_data .= "<td>&nbsp;</td>"; }
2203               }
2204               $table_data .= "</tr>";
2205               $k++;
2206             }
2207           $table_data .= "<tr><td colspan=20><hr></td></tr>";
2208         }
2209         $total_companionships += $num_companionships;
2210         $stat_data = "<tr><td><b><font size=-2>$num_companionships Companionships<br>Interview Quarterly Totals:</font></b></td>";
2211
2212         for($m=$num_months; $m >=0; $m--) {
2213           $month = $current_month - $m;
2214           if(($month % 3) == 1) { $quarter_total = $ints[$m]; }
2215           else { $quarter_total += $ints[$m]; }
2216           $percent = ceil(($quarter_total / $num_companionships)*100);
2217           $stat_data .= "<td align=center><font size=-2><b>$quarter_total<br>$percent%</font></b></td>";
2218         }
2219         $stat_data .= "</tr>";
2220
2221         $this->t->set_var('table_width',$table_width);
2222         $this->t->set_var('header_row',$header_row);
2223         $this->t->set_var('table_data',$table_data);
2224         $this->t->set_var('stat_data',$stat_data);
2225         $this->t->fp('list','district_list',True);
2226       }
2227
2228       // Display the totals, cummulative per quarter
2229       $quarter_total = 0;
2230       $totals = "<tr><td><b><font size=-2>$total_companionships Total Comps<br>Interview Quarterly Totals:</font></b></td>";
2231       for($m=$num_months; $m >=0; $m--) {
2232         $month = $current_month - $m;
2233         if(($month % 3) == 1) { $quarter_total = $total_ints[$m]; }
2234         else { $quarter_total += $total_ints[$m]; }
2235         $percent = ceil(($quarter_total / $total_companionships)*100);
2236         $totals .= "<td align=center><font size=-2><b>$quarter_total<br>$percent%</font></b></td>";
2237       }
2238       $totals .= "</tr>";
2239
2240       $this->t->set_var('totals',$totals);
2241       $this->t->pfp('out','int_view_t');
2242       $this->save_sessiondata();
2243     }
2244
2245   function int_update()
2246     {
2247       $this->t->set_file(array('form' => 'int_update.tpl'));
2248       $this->t->set_block('form','interviewer_list','int_list');
2249       $this->t->set_block('form','add','addhandle');
2250       $this->t->set_block('form','edit','edithandle');
2251
2252       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.int_view'));
2253       $this->t->set_var('readonly','');
2254       $this->t->set_var('disabled','');
2255
2256       $action = get_var('action',array('GET','POST'));
2257       $companionship = get_var('companionship',array('GET','POST'));
2258       $interviewer = get_var('interviewer',array('GET','POST'));
2259       $name = get_var('name',array('GET','POST'));
2260       $interview = get_var('interview',array('GET','POST'));
2261       $elder = get_var('elder',array('GET','POST'));
2262       $aaronic = get_var('aaronic',array('GET','POST'));
2263       $date = get_var('date',array('GET','POST'));
2264       $notes = get_var('notes',array('GET','POST'));
2265
2266       $sql = "SELECT * FROM eq_district where valid=1 ORDER BY district ASC";
2267       $this->db->query($sql,__LINE__,__FILE__);
2268       while ($this->db->next_record())
2269         {
2270           $supervisor = $this->db->f('supervisor');
2271           $sql = "SELECT * FROM eq_elder WHERE elder=" . $supervisor;
2272           $this->db2->query($sql,__LINE__,__FILE__);
2273           $this->db2->next_record();
2274           $interviewer_name = $this->db2->f('name');
2275
2276           if($supervisor == $interviewer) {
2277             $this->t->set_var('interviewer',$supervisor . ' selected');
2278           } else {
2279             $this->t->set_var('interviewer',$interviewer);
2280           }
2281           $this->t->set_var('interviewer_name',$interviewer_name);
2282           $this->t->fp('int_list','interviewer_list',True);
2283         }
2284
2285       if($action == 'save')
2286         {
2287           $notes = $this->db->db_addslashes(get_var('notes',array('POST')));
2288           $this->db->query("UPDATE eq_interview set " .
2289                      "   interview='" . $interview . "'" .
2290                     ", interviewer='" . $interviewer . "'" .
2291                           ", elder='" . $elder . "'" .
2292                         ", aaronic='" . $aaronic . "'" .
2293                            ", date='" . $date . "'" .
2294                           ", notes='" . $notes . "'" .
2295                            " WHERE interview=" . $interview,__LINE__,__FILE__);
2296           $this->int_view();
2297           return false;
2298         }
2299
2300       if($action == 'insert')
2301         {
2302           $notes = $this->db->db_addslashes(get_var('notes',array('POST')));
2303           $this->db->query("INSERT INTO eq_interview (interviewer,elder,aaronic,date,notes) "
2304                            . "VALUES ('" . $interviewer . "','" . $elder . "','" . $aaronic . "','"
2305                            . $date . "','" . $notes ."')",__LINE__,__FILE__);
2306           $this->int_view();
2307           return false;
2308         }
2309
2310       if($action == 'add')
2311         {
2312           $this->t->set_var('cal_date',$this->jscal->input('date','','','','','','',$this->cal_options));
2313           $this->t->set_var('interview', '');
2314           $this->t->set_var('interviewer', $interviewer);
2315           $this->t->set_var('name',$name);
2316           $this->t->set_var('elder',$elder);
2317           $this->t->set_var('aaronic',$aaronic);
2318           $this->t->set_var('date','');
2319           $this->t->set_var('notes','');
2320           $this->t->set_var('lang_done','Cancel');
2321           $this->t->set_var('lang_action','Adding New Interview');
2322           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.int_update&interview='
2323                                                                 . $interview . '&action=' . 'insert'));
2324         }
2325
2326       if($action == 'edit' || $action == 'view')
2327         {
2328           $sql = "SELECT * FROM eq_interview WHERE interview=".$interview;
2329           $this->db->query($sql,__LINE__,__FILE__);
2330           $this->db->next_record();
2331           $this->t->set_var('interview',$interview);
2332           $this->t->set_var('name',$name);
2333           $this->t->set_var('interviewer', $this->db->f('interviewer'));
2334           $this->t->set_var('elder',$this->db->f('elder'));
2335           $this->t->set_var('aaronic',$this->db->f('aaronic'));
2336           $this->t->set_var('date',$this->db->f('date'));
2337           $this->t->set_var('notes',$this->db->f('notes'));
2338         }
2339
2340       if($action == 'edit')
2341         {
2342           $this->t->set_var('cal_date',$this->jscal->input('date',$date,'','','','','',$this->cal_options));
2343           $this->t->set_var('lang_done','Cancel');
2344           $this->t->set_var('lang_action','Editing Interview');
2345           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.int_update&interview='
2346                                                                 . $interview . '&action=' . 'save'));
2347         }
2348
2349       if($action == 'view')
2350         {
2351           $date = $this->db->f('date');
2352           $this->t->set_var('cal_date','<input type=text size="10" maxlength="10" name="date" value="'.$date.'" readonly>');
2353           $this->t->set_var('readonly','READONLY');
2354           $this->t->set_var('disabled','DISABLED');
2355           $this->t->set_var('lang_done','Done');
2356           $this->t->set_var('lang_action','Viewing Interview');
2357           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.int_update&interview='
2358                                                                 . $interview . '&action=' . 'edit'));
2359         }
2360
2361       $this->t->set_var('lang_reset','Clear Form');
2362       $this->t->set_var('lang_add','Add Interview');
2363       $this->t->set_var('lang_save','Save Changes');
2364       $this->t->set_var('edithandle','');
2365       $this->t->set_var('addhandle','');
2366
2367       $this->t->pfp('out','form');
2368
2369       if($action == 'view') { $this->t->set_var('lang_save','Edit Interview'); }
2370       if($action == 'edit' || $action == 'view') { $this->t->pfp('addhandle','edit'); }
2371       if($action == 'add') { $this->t->pfp('addhandle','add'); }
2372
2373       $this->save_sessiondata();
2374     }
2375
2376   function vis_view()
2377     {
2378       $this->t->set_file(array('vis_view_t' => 'vis_view.tpl'));
2379       $this->t->set_block('vis_view_t','visit_list','list1');
2380       $this->t->set_block('vis_view_t','family_list','list2');
2381
2382       $this->t->set_var('lang_name','Family Name');
2383       $this->t->set_var('lang_date','Date');
2384
2385       $this->t->set_var('vis_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_view'));
2386       $this->t->set_var('vis_link_title','View Yearly Visits');
2387
2388       $this->t->set_var('schedule_vis_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_sched'));
2389       $this->t->set_var('schedule_vis_link_title','Schedule Yearly Visits');
2390
2391       $this->t->set_var('linkurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_view'));
2392       $num_years = get_var('num_years',array('GET','POST'));
2393       if($num_years == '') { $num_years = $this->default_vis_num_years; }
2394       $this->t->set_var('num_years',$num_years);
2395       if($num_years == 1) { $this->t->set_var('lang_num_years','Year of History'); }
2396       else {  $this->t->set_var('lang_num_years','Years of History'); }
2397       $this->t->set_var('lang_filter','Filter');
2398
2399       $year = date('Y') - $num_years + 1;
2400       $year_start = $year - 1 . "-12-31"; $year_end = $year + 1 . "-01-01";
2401
2402       $sql = "SELECT * FROM eq_visit WHERE companionship=0 and date > '$year_start' ORDER BY date DESC";
2403       $this->db->query($sql,__LINE__,__FILE__);
2404       $total_records = $this->db->num_rows();
2405
2406       $i = 0;
2407       while ($this->db->next_record())
2408         {
2409           $visit_list[$i]['visit'] = $this->db->f('visit');
2410           $visit_list[$i]['family'] = $this->db->f('family');
2411           $visit_list[$i]['date']  = $this->db->f('date');
2412           $i++;
2413         }
2414
2415       for ($i=0; $i < count($visit_list); $i++)
2416         {
2417           $this->nextmatchs->template_alternate_row_color(&$this->t);
2418
2419           $sql = "SELECT * FROM eq_family WHERE family=".$visit_list[$i]['family'];
2420           $this->db->query($sql,__LINE__,__FILE__);
2421           $this->db->next_record();
2422
2423           $this->t->set_var('family',$visit_list[$i]['family']);
2424           $this->t->set_var('family_name',$this->db->f('name'));
2425           $this->t->set_var('date',$visit_list[$i]['date']);
2426
2427           $link_data['menuaction'] = 'eq.eq.vis_update';
2428           $link_data['visit'] = $visit_list[$i]['visit'];
2429           $link_data['name'] = $this->db->f('name');
2430           $link_data['date'] = $visit_list[$i]['date'];
2431           $link_data['action'] = 'view';
2432           $this->t->set_var('view',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
2433           $this->t->set_var('lang_view','View');
2434
2435           $link_data['menuaction'] = 'eq.eq.vis_update';
2436           $link_data['visit'] = $visit_list[$i]['visit'];
2437           $link_data['name'] = $this->db->f('name');
2438           $link_data['date'] = $visit_list[$i]['date'];
2439           $link_data['action'] = 'edit';
2440           $this->t->set_var('edit',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
2441           $this->t->set_var('lang_edit','Edit');
2442
2443           $this->t->fp('list1','visit_list',True);
2444         }
2445
2446       // List the families that are available to record a visit against
2447       $sql = "SELECT * FROM eq_family WHERE valid=1";
2448       $this->db->query($sql,__LINE__,__FILE__);
2449       $total_records = $this->db->num_rows();
2450
2451       $i = 0;
2452       while ($this->db->next_record())
2453         {
2454           $family_names[$i] = $this->db->f('name');
2455           $family_ids[$i] = $this->db->f('family');
2456           $i++;
2457         } array_multisort($family_names, $family_ids);
2458
2459       for ($i=0; $i < count($family_names); $i++)
2460         {
2461           $link_data['menuaction'] = 'eq.eq.vis_update';
2462           $link_data['visit'] = '';
2463           $link_data['family'] = $family_ids[$i];
2464           $link_data['action'] = 'add';
2465           $link_data['name'] = $family_names[$i];
2466           $this->t->set_var('add',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
2467
2468           $this->t->set_var('name',$family_names[$i]);
2469           if(($i+1) % 3 == 0) { $this->t->set_var('table_sep',"</td></tr><tr>"); }
2470           else { $this->t->set_var('table_sep',"</td>"); }
2471           if(($i) % 3 == 0) { $this->nextmatchs->template_alternate_row_color(&$this->t); }
2472
2473           $this->t->fp('list2','family_list',True);
2474         }
2475
2476       $this->t->pfp('out','vis_view_t');
2477       $this->save_sessiondata();
2478     }
2479
2480   function vis_update()
2481     {
2482       $this->t->set_file(array('form' => 'vis_update.tpl'));
2483       $this->t->set_block('form','add','addhandle');
2484       $this->t->set_block('form','edit','edithandle');
2485
2486       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_view'));
2487       $this->t->set_var('readonly','');
2488       $this->t->set_var('disabled','');
2489
2490       $action = get_var('action',array('GET','POST'));
2491       $visit = get_var('visit',array('GET','POST'));
2492       $family = get_var('family',array('GET','POST'));
2493       $name = get_var('name',array('GET','POST'));
2494       $date = get_var('date',array('GET','POST'));
2495       $notes = get_var('notes',array('GET','POST'));
2496       $companionship = 0;
2497
2498       if($action == 'save')
2499         {
2500           $notes = $this->db->db_addslashes(get_var('notes',array('POST')));
2501           $this->db->query("UPDATE eq_visit set " .
2502                            "  date='" . $date . "'" .
2503                           ", notes='" . $notes . "'" .
2504                            " WHERE visit=" . $visit,__LINE__,__FILE__);
2505           $this->vis_view();
2506           return false;
2507         }
2508
2509       if($action == 'insert')
2510         {
2511           $notes = $this->db->db_addslashes(get_var('notes',array('POST')));
2512           $this->db->query("INSERT INTO eq_visit (family,companionship,date,notes) "
2513                            . "VALUES ('" . $family . "','" . $companionship . "','"
2514                            . $date . "','" . $notes . "')",__LINE__,__FILE__);
2515           $this->vis_view();
2516           return false;
2517         }
2518
2519       if($action == 'add')
2520         {
2521           $this->t->set_var('cal_date',$this->jscal->input('date','','','','','','',$this->cal_options));
2522           $this->t->set_var('family', $family);
2523           $this->t->set_var('visit', '');
2524           $this->t->set_var('name', $name);
2525           $this->t->set_var('date','');
2526           $this->t->set_var('notes','');
2527           $this->t->set_var('lang_done','Cancel');
2528           $this->t->set_var('lang_action','Adding New Visit');
2529           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_update&family='
2530                                                                 . $family . '&action=' . 'insert'));
2531         }
2532
2533       if($action == 'edit' || $action == 'view')
2534         {
2535           $sql = "SELECT * FROM eq_visit WHERE visit=".$visit;
2536           $this->db->query($sql,__LINE__,__FILE__);
2537           $this->db->next_record();
2538           $this->t->set_var('visit',$visit);
2539           $this->t->set_var('name',$name);
2540           $this->t->set_var('family', $family);
2541           $this->t->set_var('date',$this->db->f('date'));
2542           $this->t->set_var('notes',$this->db->f('notes'));
2543         }
2544
2545       if($action == 'edit')
2546         {
2547           $this->t->set_var('cal_date',$this->jscal->input('date',$date,'','','','','',$this->cal_options));
2548           $this->t->set_var('lang_done','Cancel');
2549           $this->t->set_var('lang_action','Editing Visit');
2550           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_update&visit='
2551                                                                 . $visit . '&action=' . 'save'));
2552         }
2553
2554       if($action == 'view')
2555         {
2556           $date = $this->db->f('date');
2557           $this->t->set_var('cal_date','<input type=text size="10" maxlength="10" name="date" value="'.$date.'" readonly>');
2558           $this->t->set_var('readonly','READONLY');
2559           $this->t->set_var('disabled','DISABLED');
2560           $this->t->set_var('lang_done','Done');
2561           $this->t->set_var('lang_action','Viewing Visit');
2562           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_update&visit='
2563                                                                 . $visit . '&action=' . 'edit'));
2564         }
2565
2566       $this->t->set_var('lang_reset','Clear Form');
2567       $this->t->set_var('lang_add','Add Visit');
2568       $this->t->set_var('lang_save','Save Changes');
2569       $this->t->set_var('edithandle','');
2570       $this->t->set_var('addhandle','');
2571
2572       $this->t->pfp('out','form');
2573
2574       if($action == 'view') { $this->t->set_var('lang_save','Edit Visit'); }
2575       if($action == 'edit' || $action == 'view') { $this->t->pfp('addhandle','edit'); }
2576       if($action == 'add') { $this->t->pfp('addhandle','add'); }
2577
2578       $this->save_sessiondata();
2579     }
2580
2581   function att_view()
2582     {
2583       $monthnum['Jan']=1; $monthnum['Feb']=2; $monthnum['Mar']=3; $monthnum['Apr']=4;
2584       $monthnum['May']=5; $monthnum['Jun']=6; $monthnum['Jul']=7; $monthnum['Aug']=8;
2585       $monthnum['Sep']=9; $monthnum['Oct']=10; $monthnum['Nov']=11; $monthnum['Dec']=12;
2586
2587       $this->t->set_file(array('att_view_t' => 'att_view.tpl'));
2588       $this->t->set_block('att_view_t','act_list','list');
2589
2590       $this->t->set_block('att_view_t','month_list','list1');
2591       $this->t->set_block('att_view_t','header_list','list2');
2592       $this->t->set_block('att_view_t','elder_list','list3');
2593
2594       $this->t->set_var('linkurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.att_view'));
2595       $num_quarters = get_var('num_quarters',array('GET','POST'));
2596       if($num_quarters == '') { $num_quarters = $this->default_att_num_quarters; }
2597       $this->t->set_var('num_quarters',$num_quarters);
2598       $this->t->set_var('lang_filter','Filter');
2599       if($num_quarters == 1) { $this->t->set_var('lang_num_quarters','Quarter of History'); }
2600       else { $this->t->set_var('lang_num_quarters','Quarters of History'); }
2601
2602       $num_months = $num_quarters * 3;
2603       $current_month = $this->current_month;
2604       if($current_month >= 1 && $current_month <= 3) { $current_month=3; }
2605       else if($current_month >= 4 && $current_month <= 6) { $current_month=6; }
2606       else if($current_month >= 7 && $current_month <= 9) { $current_month=9; }
2607       else if($current_month >= 10 && $current_month <= 12) { $current_month=12; }
2608
2609       $sql = "SELECT * FROM eq_elder where valid=1";
2610       $this->db->query($sql,__LINE__,__FILE__);
2611       $i=0;
2612       while ($this->db->next_record())
2613         {
2614           $elder_name[$i] = $this->db->f('name');
2615           $elder_id[$i] = $this->db->f('elder');
2616           $i++;
2617         }
2618       array_multisort($elder_name, $elder_id);
2619
2620       // Create a list of sunday dates for a window of 3 months back and current month
2621       $i=0;
2622       $last_time = 0;
2623       $found_sunday = 0;
2624       $sunday_list[0]['date'] = date("Y-m-d", mktime(0, 0, 0, $current_month-$num_months, 1, date("y")));
2625       $last_date = explode("-",$sunday_list[0]['date']);
2626       $last_time = mktime(0, 0, 0, $last_date[1], $last_date[2], $last_date[0]);
2627       $time_limit = mktime(0, 0, 0, date("m"), date("t"), date("y"));
2628       while($last_time < $time_limit)
2629       {
2630         $day = date("w",$last_time);
2631         if(date("w",$last_time) == 0) {
2632           $sunday_list[$i]['date'] = date("Y-m-d", $last_time);
2633           $last_date = explode("-",$sunday_list[$i]['date']);
2634           $last_time = mktime(0, 0, 0, $last_date[1], $last_date[2], $last_date[0]);
2635           $sunday_list[$i]['day'] = $last_date[2];
2636           $sunday_list[$i]['month'] = date("M",$last_time);
2637           $sunday_list[$i]['year'] = $last_date[0];
2638           $found_sunday = 1;
2639         }
2640         $last_time += 90000;
2641         if($found_sunday) { $i++; $found_sunday=0; }
2642       }
2643
2644       $total_elders = count($elder_id);
2645       $old_month=$sunday_list[0]['month']; $span=0;
2646       for ($i=0; $i < count($sunday_list); $i++) {
2647         $date = $sunday_list[$i]['date'];
2648         $this->t->set_var('date',$sunday_list[$i]['date']);
2649         $this->t->set_var('day',$sunday_list[$i]['day']);
2650         if(($old_month != $sunday_list[$i]['month']) || $i == count($sunday_list)-1) {
2651           if($i == count($sunday_list)-1) { $span++; }
2652           $cur_month = $sunday_list[$i]['month'];
2653           $old_month = $sunday_list[$i]['month'];
2654           $link_data['menuaction'] = 'eq.eq.att_update';
2655           $link_data['month'] = $sunday_list[$i-1]['month'];
2656           $link_data['year'] = $sunday_list[$i-1]['year'];
2657           $link_data['action'] = 'update_month';
2658           $cur_month = $sunday_list[$i-1]['month'];
2659           $cur_year = $sunday_list[$i-1]['year'];
2660           $header_row .= "<th><font size=-3>$cur_month&nbsp;$cur_year</font></th>";
2661           $this->t->set_var('update_month',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
2662           $this->t->set_var('month',$sunday_list[$i-1]['month']);
2663           $this->t->set_var('year',$sunday_list[$i-1]['year']);
2664           $this->t->set_var('span',$span); $span=0;
2665           $this->t->fp('list1','month_list',True);
2666         } $span++;
2667       }
2668       $this->t->set_var('total_elders',$total_elders);
2669       $this->t->set_var('header_row',$header_row);
2670
2671       $elder_width=200; $att_width=25; $total_width=$elder_width;
2672       for ($i=0; $i < count($sunday_list); $i++) {
2673         $link_data['menuaction'] = 'eq.eq.att_update';
2674         $link_data['month'] = $sunday_list[$i]['month'];
2675         $link_data['year'] = $sunday_list[$i]['year'];
2676         $link_data['day'] = $sunday_list[$i]['day'];
2677         $link_data['date'] = $sunday_list[$i]['date'];
2678         $link_data['action'] = 'update_day';
2679         $this->t->set_var('update_day',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
2680         $this->t->set_var('date',$sunday_list[$i]['date']);
2681         $this->t->set_var('day',$sunday_list[$i]['day']);
2682         $this->t->set_var('month',$sunday_list[$i]['month']);
2683         $this->t->set_var('year',$sunday_list[$i]['year']);
2684         $this->t->fp('list2','header_list',True);
2685         $total_width += $att_width;
2686         $attendance[$monthnum[$sunday_list[$i]['month']]]=0;
2687       }
2688
2689       for ($i=0; $i < count($elder_id); $i++) {
2690         $att_table = "";
2691         $this->nextmatchs->template_alternate_row_color(&$this->t);
2692         $this->t->set_var('elder_name',$elder_name[$i]);
2693         #print "checking for elder: " . $elder_id[$i] . "<br>";
2694         for ($j=0; $j < count($sunday_list); $j++) {
2695           #print "checking for date: " .  $sunday_list[$j]['date'] . "<br>";
2696           #print "SELECT * FROM eq_attendance WHERE date='"
2697           #  . $sunday_list[$j]['date'] . "' AND elder=" . $elder_id[$i] . "<br>";
2698           $sql = "SELECT * FROM eq_attendance WHERE date='"
2699              . $sunday_list[$j]['date'] . "' AND elder=" . $elder_id[$i];
2700           $this->db->query($sql,__LINE__,__FILE__);
2701           if($this->db->next_record()) {
2702             $cur_month = $sunday_list[$j]['month'];
2703             if($attended[$i][$cur_month] != 1) {
2704               $attended[$i][$cur_month]=1;
2705               $attendance[$monthnum[$cur_month]]++;
2706             }
2707             $att_table .= '<td align=center><img src="checkmark.gif"></td>';
2708           } else {
2709             $att_table .= '<td>&nbsp;</td>';
2710           }
2711         }
2712         $this->t->set_var('att_table',$att_table);
2713         $this->t->fp('list3','elder_list',True);
2714       }
2715       $this->t->set_var('total_width',$total_width);
2716       $this->t->set_var('elder_width',$elder_width);
2717       $this->t->set_var('att_width',$att_width);
2718
2719       # Now calculate attendance for these months
2720       $attendance_str = "";
2721       $nonattendance_str = "";
2722       $aveattendance_str = "";
2723       $avenonattendance_str = "";
2724       $num_months=0;
2725       $ave_total_attended=0;
2726       ksort($attendance);
2727       foreach($attendance as $att => $value) {
2728         $total_attended = $attendance[$att];
2729         $ave_total_attended += $attendance[$att]; $num_months++;
2730         $percent = ceil(($total_attended / $total_elders)*100);
2731         $attendance_str.="<td align=center><font size=-2><b>$total_attended ($percent%)</b></font></td>";
2732         $total_nonattended = $total_elders - $total_attended;
2733         $percent = ceil(($total_nonattended / $total_elders)*100);
2734         $nonattendance_str.="<td align=center><font size=-2><b>$total_nonattended ($percent%)</b></font></td>";
2735
2736         $total_attended = ceil(($ave_total_attended / $num_months));
2737         $percent = ceil(($total_attended / $total_elders)*100);
2738         $aveattendance_str .= "<td align=center><font size=-2><b>$total_attended ($percent%)</b></font></td>";
2739         $total_attended = $total_elders - ceil(($ave_total_attended / $num_months));
2740         $percent = ceil(($total_attended / $total_elders)*100);
2741         $avenonattendance_str .= "<td align=center><font size=-2><b>$total_attended ($percent%)</b></font></td>";
2742       }
2743
2744       $this->t->set_var('attendance',$attendance_str);
2745       $this->t->set_var('aveattendance',$aveattendance_str);
2746       $this->t->set_var('nonattendance',$nonattendance_str);
2747       $this->t->set_var('avenonattendance',$avenonattendance_str);
2748
2749       $this->t->pfp('out','att_view_t');
2750       $this->save_sessiondata();
2751     }
2752
2753   function att_update()
2754     {
2755       $monthnum['Jan']=1; $monthnum['Feb']=2; $monthnum['Mar']=3; $monthnum['Apr']=4;
2756       $monthnum['May']=5; $monthnum['Jun']=6; $monthnum['Jul']=7; $monthnum['Aug']=8;
2757       $monthnum['Sep']=9; $monthnum['Oct']=10; $monthnum['Nov']=11; $monthnum['Dec']=12;
2758
2759       $this->t->set_file(array('form' => 'att_update.tpl'));
2760       $this->t->set_block('form','edit','edithandle');
2761
2762       $this->t->set_block('form','month_list','list1');
2763       $this->t->set_block('form','header_list','list2');
2764       $this->t->set_block('form','elder_list','list3');
2765
2766       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.att_view'));
2767
2768       $action = get_var('action',array('GET','POST'));
2769       $month = get_var('month',array('GET','POST'));
2770       $year = get_var('year',array('GET','POST'));
2771       $day = get_var('day',array('GET','POST'));
2772       $date = get_var('date',array('GET','POST'));
2773
2774       if($action == 'save_month' || $action == 'save_day')
2775         {
2776            $new_data = get_var('elders_attended',array('POST'));
2777            $month = $monthnum[$month]; if($month < 10) { $month = "0" . $month; }
2778
2779            if($action == 'save_month') {
2780              $this->db->query("DELETE from eq_attendance where date LIKE '".$year."-".$month."-%'",__LINE__,__FILE__);
2781            }
2782
2783            if($action == 'save_day') {
2784              $this->db->query("DELETE from eq_attendance where date LIKE '".$year."-".$month."-".$day."'",__LINE__,__FILE__);
2785            }
2786
2787            foreach ($new_data as $data)
2788            {
2789               $data_array = explode("-",$data);
2790               $elder = $data_array[0];
2791               $date  = "$data_array[1]-$data_array[2]-$data_array[3]";
2792               $this->db->query("INSERT INTO eq_attendance (elder,date) "
2793                                . "VALUES (" . $elder . ",'". $date . "')",__LINE__,__FILE__);
2794            }
2795
2796          $this->att_view();
2797          return false;
2798         }
2799
2800       $sql = "SELECT * FROM eq_elder where valid=1";
2801       $this->db->query($sql,__LINE__,__FILE__);
2802       $i=0;
2803       while ($this->db->next_record())
2804         {
2805           $elder_name[$i] = $this->db->f('name');
2806           $elder_id[$i] = $this->db->f('elder');
2807           $elder_attending[$elder_id[$i]] = $this->db->f('attending');
2808           $i++;
2809         }
2810       array_multisort($elder_name, $elder_id);
2811
2812       if($action == 'update_month')
2813       {
2814         $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.att_update&action=save_month'));
2815         $i=0;
2816         $last_time = 0;
2817         $found_sunday = 0;
2818         $sunday_list[0]['date'] = date("Y-m-d", mktime(0, 0, 0, $monthnum[$month], 1, $year));
2819         $last_date = explode("-",$sunday_list[0]['date']);
2820         $last_time = mktime(0, 0, 0, $last_date[1], $last_date[2], $last_date[0]);
2821         $time_limit = mktime(0, 0, 0, $monthnum[$month], 31, $year);
2822         while($last_time <= $time_limit)
2823         {
2824           $day = date("w",$last_time);
2825           if(date("w",$last_time) == 0) {
2826             $sunday_list[$i]['date'] = date("Y-m-d", $last_time);
2827             $last_date = explode("-",$sunday_list[$i]['date']);
2828             $last_time = mktime(0, 0, 0, $last_date[1], $last_date[2], $last_date[0]);
2829             $sunday_list[$i]['day'] = $last_date[2];
2830             $sunday_list[$i]['month'] = date("M",$last_time);
2831             $sunday_list[$i]['year'] = $last_date[0];
2832             $found_sunday = 1;
2833           }
2834           $last_time += 90000;
2835           if($found_sunday) { $i++; $found_sunday=0; }
2836         }
2837
2838         $this->t->set_var('span', $i);
2839         $this->t->set_var('month',$sunday_list[$i-1]['month']);
2840         $this->t->set_var('year',$sunday_list[$i-1]['year']);
2841         $this->t->fp('list1','month_list',True);
2842         $elder_width=200; $att_width=25; $total_width=$elder_width;
2843         for ($i=0; $i < count($sunday_list); $i++) {
2844           $link_data['menuaction'] = 'eq.eq.att_update';
2845           $link_data['month'] = $sunday_list[$i]['month'];
2846           $link_data['year'] = $sunday_list[$i]['year'];
2847           $link_data['day'] = $sunday_list[$i]['day'];
2848           $link_data['date'] = $sunday_list[$i]['date'];
2849           $link_data['action'] = 'update_day';
2850           $this->t->set_var('update_day',$GLOBALS['phpgw']->link('/eq/index.php',$link_data));
2851           $this->t->set_var('date',$sunday_list[$i]['date']);
2852           $this->t->set_var('day',$sunday_list[$i]['day']);
2853           $this->t->set_var('month',$sunday_list[$i]['month']);
2854           $this->t->set_var('year',$sunday_list[$i]['year']);
2855           $this->t->fp('list2','header_list',True);
2856           $total_width += $att_width;
2857         }
2858       }
2859
2860       if($action == 'update_day')
2861       {
2862         $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.att_update&action=save_day'));
2863         $sunday_list[0]['date'] = date("Y-m-d", mktime(0, 0, 0, $monthnum[$month], $day, $year));
2864         $this->t->set_var('month',$month);
2865         $this->t->set_var('year',$year);
2866         $this->t->fp('list1','month_list',True);
2867         $this->t->set_var('date',$date);
2868         $this->t->set_var('day',$day);
2869         $this->t->set_var('month',$month);
2870         $this->t->set_var('year',$year);
2871         $this->t->fp('list2','header_list',True);
2872       }
2873
2874       for ($i=0; $i < count($elder_id); $i++) {
2875         $att_table = "";
2876         $this->nextmatchs->template_alternate_row_color(&$this->t);
2877         $this->t->set_var('elder_name',$elder_name[$i]);
2878         for ($j=0; $j < count($sunday_list); $j++) {
2879           $sql = "SELECT * FROM eq_attendance WHERE date='"
2880              . $sunday_list[$j]['date'] . "' AND elder=" . $elder_id[$i];
2881           $this->db->query($sql,__LINE__,__FILE__);
2882           $value = $elder_id[$i] . "-" . $sunday_list[$j]['date'];
2883           if($this->db->next_record()) {
2884             $att_table .= '<td align=center><input type="checkbox" name="elders_attended[]" value="'.$value.'" checked></td>';
2885           } else if($elder_attending[$elder_id[$i]] == 1) {
2886             $att_table .= '<td align=center><input type="checkbox" name="elders_attended[]" value="'.$value.'" checked></td>';
2887           } else {
2888             $att_table .= '<td align=center><input type="checkbox" name="elders_attended[]" value="'.$value.'"></td>';
2889           }
2890         }
2891         $this->t->set_var('att_table',$att_table);
2892         $this->t->fp('list3','elder_list',True);
2893       }
2894
2895       $this->t->set_var('lang_done', 'Cancel');
2896       $this->t->set_var('lang_reset','Clear Form');
2897       $this->t->set_var('lang_save','Save Changes');
2898
2899       $this->t->pfp('out','form');
2900       $this->t->pfp('addhandle','edit');
2901
2902       $this->save_sessiondata();
2903     }
2904
2905   function dir_view()
2906     {
2907       $this->t->set_file(array('dir_view_t' => 'dir_view.tpl'));
2908       $this->t->set_block('dir_view_t','dir_list','list');
2909
2910       $sql = "SELECT * FROM eq_parent where valid=1 ORDER BY name ASC";
2911       $this->db->query($sql,__LINE__,__FILE__);
2912       $i=0;
2913       while ($this->db->next_record())
2914         {
2915           $parent[$i]['id'] = $this->db->f('parent');
2916           $parent[$i]['name'] = $this->db->f('name');
2917           $parent[$i]['phone'] = $this->db->f('phone');
2918           $parent[$i]['address'] = $this->db->f('address');
2919           $i++;
2920         }
2921
2922       for ($i=0; $i < count($parent); $i++)
2923       {
2924         $name = $parent[$i]['name'];
2925         $phone = $parent[$i]['phone'];
2926         $address = $parent[$i]['address'];
2927         $this->t->set_var('name', $name);
2928         $this->t->set_var('address', $address);
2929         $this->t->set_var('phone', $phone);
2930         $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
2931         $this->t->set_var('tr_color',$tr_color);
2932         $this->t->fp('list','dir_list',True);
2933         //print "$phone $name $address<br>";
2934       }
2935       $this->t->pfp('out','dir_view_t');
2936       $this->save_sessiondata();
2937     }
2938
2939   function org_view()
2940     {
2941       $this->t->set_file(array('org_view_t' => 'org_view.tpl'));
2942       $this->t->set_block('org_view_t','calling_list','list1');
2943       $this->t->set_block('org_view_t','org_list','list2');
2944
2945       # Display a list ordered alphabetically
2946       $sql = "SELECT * FROM eq_calling ORDER BY name ASC";
2947       $this->db->query($sql,__LINE__,__FILE__);
2948       $i=0;
2949       while ($this->db->next_record())
2950         {
2951           $calling[$i]['id'] = $this->db->f('indiv_id');
2952           $calling[$i]['name'] = $this->db->f('name');
2953           $calling[$i]['position'] = $this->db->f('position');
2954           $calling[$i]['sustained'] = $this->db->f('sustained');
2955           $calling[$i]['organization'] = $this->db->f('organization');
2956           $i++;
2957         }
2958       for ($i=0; $i < count($calling); $i++)
2959       {
2960         $name = $calling[$i]['name'];
2961         $position = $calling[$i]['position'];
2962         $sustained = $calling[$i]['sustained'];
2963         $organization = $calling[$i]['organization'];
2964         $this->t->set_var('name', $name);
2965         $this->t->set_var('position', $position);
2966         $this->t->set_var('sustained', $sustained);
2967         $this->t->set_var('organization', $organization);
2968         $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
2969         $this->t->set_var('tr_color',$tr_color);
2970         $this->t->fp('list1','calling_list',True);
2971       }
2972
2973       # Display a list ordered by organization
2974       $sql = "SELECT * FROM eq_calling ORDER BY sequence ASC";
2975       $this->db->query($sql,__LINE__,__FILE__);
2976       $i=0;
2977       while ($this->db->next_record())
2978         {
2979           $calling[$i]['id'] = $this->db->f('indiv_id');
2980           $calling[$i]['name'] = $this->db->f('name');
2981           $calling[$i]['position'] = $this->db->f('position');
2982           $calling[$i]['sustained'] = $this->db->f('sustained');
2983           $calling[$i]['organization'] = $this->db->f('organization');
2984           $i++;
2985         }
2986       for ($i=0; $i < count($calling); $i++)
2987       {
2988         $name = $calling[$i]['name'];
2989         $position = $calling[$i]['position'];
2990         $sustained = $calling[$i]['sustained'];
2991         $organization = $calling[$i]['organization'];
2992         $this->t->set_var('name', $name);
2993         $this->t->set_var('position', $position);
2994         $this->t->set_var('sustained', $sustained);
2995         $this->t->set_var('organization', $organization);
2996         $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
2997         $this->t->set_var('tr_color',$tr_color);
2998         $this->t->fp('list2','org_list',True);
2999       }
3000
3001       $this->t->pfp('out','org_view_t');
3002       $this->save_sessiondata();
3003     }
3004
3005   function schedule()
3006     {
3007       $this->t->set_file(array('sched_t' => 'schedule.tpl'));
3008       $this->t->set_block('sched_t','presidency_list','list');
3009
3010       $action = get_var('action',array('GET','POST'));
3011
3012       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.schedule&action=save'));
3013       $this->t->set_var('title','EQ Scheduling Tool');
3014
3015       $this->t->set_var('lang_save','Save Schedule');
3016       $this->t->set_var('lang_reset','Cancel');
3017
3018       $this->t->set_var('schedule_vis_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.vis_sched'));
3019       $this->t->set_var('schedule_vis_link_title','Schedule Yearly Visits');
3020
3021       $this->t->set_var('schedule_int_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.int_sched'));
3022       $this->t->set_var('schedule_int_link_title','Schedule Hometeaching Interviews');
3023
3024       $this->t->set_var('schedule_ppi_link',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.ppi_sched'));
3025       $this->t->set_var('schedule_ppi_link_title','Schedule Yearly PPIs');
3026
3027       $date_width=150; $time_width=200; $elder_width=200; $family_width=200;
3028       $table_width=$date_width + $time_width + $elder_width + $family_width;
3029       $header_row = "<th width=$date_width><font size=-2>Date</th>";
3030       $header_row.= "<th width=$time_width><font size=-2>Time</th>";
3031       $header_row.= "<th width=$elder_width><font size=-2>Elder</th>";
3032       $header_row.= "<th width=$family_width><font size=-2>Family</th>";
3033       $table_data = "";
3034
3035       if($action == 'save')
3036         {
3037           $new_data = get_var('sched',array('POST'));
3038           foreach ($new_data as $presidency_array)
3039            {
3040              foreach ($presidency_array as $entry)
3041                {
3042                  $presidency = $entry['presidency'];
3043                  $appointment = $entry['appointment'];
3044                  $date = $entry['date'];
3045                  $hour = $entry['hour'];
3046                  $minute = $entry['minute'];
3047                  $pm = $entry['pm'];
3048                  $elder = $entry['elder'];
3049                  $family = $entry['family'];
3050                  if($pm) { $hour = $hour + 12; }
3051                  $time = $hour.':'.$minute.':'.'00';
3052                  $uid = 0;
3053
3054                  // Update an existing appointment
3055                  if($appointment != 0)
3056                    {
3057                      $this->db->query("UPDATE eq_appointment set" .
3058                               " family=" . $family .
3059                               " ,elder=" . $elder .
3060                               " ,date='" . $date . "'" .
3061                               " ,time='" . $time . "'" .
3062                               " WHERE appointment=" . $appointment,__LINE__,__FILE__);
3063
3064                      // Email the appointment
3065                      $this->email_appt($appointment);
3066                    }
3067
3068                  // Add a new appointment
3069                  else if(($appointment == 0) && ($date != "") && ($time != ""))
3070                    {
3071                      $this->db->query("INSERT INTO eq_appointment (appointment,presidency,family,elder,date,time) "
3072                            . "VALUES ('" . $appointment . "','" . $presidency . "','" . $family . "','"
3073                            . $elder . "','" . $date . "','" . $time  . "','" . $uid ."')",__LINE__,__FILE__);
3074
3075                      //print "adding entry: appt=$appointment date: $date time: $time elder: $elder family: $family<br>";
3076                    }
3077                }
3078            }
3079
3080           $take_me_to_url = $GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.schedule');
3081           Header('Location: ' . $take_me_to_url);
3082         }
3083
3084       $sql = "SELECT * FROM eq_presidency where valid=1";
3085       $this->db->query($sql,__LINE__,__FILE__);
3086       $i=0;
3087       while ($this->db->next_record())
3088         {
3089           $presidency_data[$i]['id'] = $this->db->f('presidency');
3090           $presidency_data[$i]['name'] = $this->db->f('name');
3091           $presidency_data[$i]['elder'] = $this->db->f('elder');
3092           $i++;
3093         }
3094
3095       $sql = "SELECT * FROM eq_elder where valid=1 ORDER BY elder ASC";
3096       $this->db->query($sql,__LINE__,__FILE__);
3097       $i=0;
3098       while ($this->db->next_record())
3099         {
3100           $elder_id[$i] = $this->db->f('elder');
3101           $elder_name[$i] = $this->db->f('name');
3102           $elder_phone[$elder_id[$i]] = $this->db->f('phone');
3103           $i++;
3104         }
3105       array_multisort($elder_name, $elder_id);
3106
3107       $sql = "SELECT * FROM eq_family where valid=1 and elder_id != 0 ORDER BY name ASC";
3108       $this->db->query($sql,__LINE__,__FILE__);
3109       $i=0;
3110       while ($this->db->next_record())
3111         {
3112           $family_id[$i] = $this->db->f('family');
3113           $family_name[$i] = $this->db->f('name');
3114           $i++;
3115         }
3116       array_multisort($family_name, $family_id);
3117
3118       for ($i=0; $i < count($presidency_data); $i++) {
3119         $presidency = $presidency_data[$i]['id'];
3120         $interviewer = $presidency_data[$i]['elder'];
3121         $name = $presidency_data[$i]['name'];
3122         $this->t->set_var('presidency_name',$name);
3123         $table_data="";
3124
3125         // query the database for all the appointments
3126         $sql = "SELECT * FROM eq_appointment where presidency=$presidency and date>=CURDATE() ORDER BY date ASC, time ASC";
3127         $this->db->query($sql,__LINE__,__FILE__);
3128
3129         // Prefill any existing appointment slots
3130         while ($this->db->next_record())
3131           {
3132             $appointment = $this->db->f('appointment');
3133             $elder = $this->db->f('elder');
3134             $family = $this->db->f('family');
3135
3136             $date = $this->db->f('date');
3137             $date_array = explode("-",$date);
3138             $year = $date_array[0]; $month = $date_array[1]; $day = $date_array[2];
3139             $day_string = date("l d-M-Y", mktime(0,0,0,$month,$day,$year));
3140
3141             $time = $this->db->f('time');
3142             $time_array = explode(":",$time);
3143             $hour = $time_array[0];
3144             $minute = $time_array[1];
3145             $pm = 0;
3146             if($hour > 12) { $pm=1; $hour = $hour - 12; }
3147             $time_string = date("g:i a", mktime($time_array[0], $time_array[1], $time_array[2]));
3148
3149             $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
3150
3151             // Date selection
3152             $table_data.= '<td align=left>';
3153             $table_data.= $this->jscal->input('sched['.$presidency.']['.$appointment.'][date]',$date,'','','','','',$this->cal_options);
3154             $table_data.= '</td>';
3155
3156             // Hour & Minutes selection
3157             $table_data.= "<td align=center>";
3158             $table_data.= '<select name=sched['.$presidency.']['.$appointment.'][hour]>';
3159             foreach(range(1,12) as $num) {
3160               if($hour == $num) { $selected[$num] = 'selected="selected"'; } else { $selected[$num] = ''; }
3161               $table_data.= '<option value='.$num.' '.$selected[$num].'>'.$num.'</option>';
3162             }
3163             $table_data.= '</select>';
3164             $table_data.= '&nbsp;:&nbsp;';
3165             $table_data.= '<select name=sched['.$presidency.']['.$appointment.'][minute]>';
3166             foreach(range(0,3) as $num) {
3167               $num = $num * 15; if($num == 0) { $num = "00"; }
3168               if($minute == $num) { $selected[$num] = 'selected="selected"'; } else { $selected[$num] = ''; }
3169               $table_data.= '<option value='.$num.' '.$selected[$num].'>'.$num.'</option>';
3170             }
3171             $table_data.= '</select>';
3172             $table_data.= '<select name=sched['.$presidency.']['.$appointment.'][pm]>';
3173             if($pm == 0) { $table_data.= '<option value=0 selected>am</option>'; $table_data.= '<option value=1>pm</option>'; }
3174             else { $table_data.= '<option value=0>am</option>'; $table_data.= '<option value=1 selected>pm</option>'; }
3175             $table_data.= '</select>';
3176             $table_data.= "</td>";
3177
3178             // Elder drop down list (for PPIs)
3179             $table_data.= '<td align=center><select name=sched['.$presidency.']['.$appointment.'][elder]>';
3180             $table_data.= '<option value=0></option>';
3181             for ($j=0; $j < count($elder_id); $j++) {
3182               $id = $elder_id[$j];
3183               $name = $elder_name[$j];
3184               if($elder_id[$j] == $elder) { $selected[$id] = 'selected="selected"'; } else { $selected[$id] = ''; }
3185               $table_data.= '<option value='.$id.' '.$selected[$id].'>'.$name.'</option>';
3186             }
3187             $table_data.='</select></td>';
3188
3189             // Family drop down list (for Visits)
3190             $table_data.= '<td align=center><select name=sched['.$presidency.']['.$appointment.'][family]>';
3191             $table_data.= '<option value=0></option>';
3192             for ($j=0; $j < count($elder_id); $j++) {
3193               $id = $family_id[$j];
3194               $name = $family_name[$j];
3195               if($family_id[$j] == $family) { $selected[$id] = 'selected="selected"'; } else { $selected[$id] = ''; }
3196               $table_data.= '<option value='.$id.' '.$selected[$id].'>'.$name.' Family</option>';
3197             }
3198             $table_data.='</select></td>';
3199
3200             $table_data.= '<input type=hidden name="sched['.$presidency.']['.$appointment.'][appointment]" value="'.$appointment.'">';
3201             $table_data.= '<input type=hidden name="sched['.$presidency.']['.$appointment.'][presidency]" value="'.$presidency.'">';
3202
3203             $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
3204             $this->t->set_var('tr_color',$tr_color);
3205
3206           }
3207
3208         // Create blank appointment slot
3209         $appointment = 0;
3210         $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
3211
3212         // Date selection
3213         $table_data.= '<td align=left>';
3214         $table_data.= $this->jscal->input('sched['.$presidency.']['.$appointment.'][date]','','','','','','',$this->cal_options);
3215         $table_data.= '</td>';
3216
3217         // Time selection
3218         $table_data.= "<td align=center>";
3219         $table_data.= '<select name=sched['.$presidency.']['.$appointment.'][hour]>';
3220         $table_data.= '<option value=""></option>';
3221         foreach(range(1,12) as $num) {
3222           $table_data.= '<option value='.$num.' '.$selected[$num].'>'.$num.'</option>';
3223         }
3224         $table_data.= '</select>';
3225         $table_data.= '&nbsp;:&nbsp;';
3226         $table_data.= '<select name=sched['.$presidency.']['.$appointment.'][minute]>';
3227         $table_data.= '<option value=""></option>';
3228         foreach(range(0,3) as $num) {
3229           $num = $num * 15; if($num == 0) { $num = "00"; }
3230           $table_data.= '<option value='.$num.'>'.$num.'</option>';
3231         }
3232         $table_data.= '</select>';
3233         $table_data.= '<select name=sched['.$presidency.']['.$appointment.'][pm]>';
3234         $table_data.= '<option value=""></option>';
3235         $table_data.= '<option value=0>am</option>';
3236         $table_data.= '<option value=1>pm</option>';
3237         $table_data.= '</select>';
3238         $table_data.= "</td>";
3239
3240         // Elder drop down list
3241         $table_data.= '<td align=center><select name=sched['.$presidency.']['.$appointment.'][elder]>';
3242         $table_data.= '<option value=0></option>';
3243         for ($j=0; $j < count($elder_id); $j++) {
3244           $id = $elder_id[$j];
3245           $name = $elder_name[$j];
3246           $table_data.= '<option value='.$id.'>'.$name.'</option>';
3247         }
3248         $table_data.='</select></td>';
3249
3250         // Family drop down list
3251         $table_data.= '<td align=center><select name=sched['.$presidency.']['.$appointment.'][family]>';
3252         $table_data.= '<option value=0></option>';
3253         for ($j=0; $j < count($elder_id); $j++) {
3254           $id = $family_id[$j];
3255           $name = $family_name[$j];
3256           $table_data.= '<option value='.$id.'>'.$name.' Family</option>';
3257         }
3258         $table_data.='</select></td>';
3259
3260         $table_data.= '<input type=hidden name="sched['.$presidency.']['.$appointment.'][appointment]" value="'.$appointment.'">';
3261         $table_data.= '<input type=hidden name="sched['.$presidency.']['.$appointment.'][presidency]" value="'.$presidency.'">';
3262
3263         $this->t->set_var('table_data',$table_data);
3264         $this->t->set_var('header_row',$header_row);
3265         $this->t->set_var('table_width',$table_width);
3266         $this->t->fp('list','presidency_list',True);
3267
3268       }
3269
3270       $this->t->pfp('out','sched_t');
3271       $this->save_sessiondata();
3272     }
3273
3274   function admin()
3275     {
3276       $this->t->set_file(array('admin_t' => 'admin.tpl'));
3277       $this->t->set_block('admin_t','upload','uploadhandle');
3278       $this->t->set_block('admin_t','admin','adminhandle');
3279       $this->t->set_block('admin_t','cmd','cmdhandle');
3280
3281       $this->t->set_var('upload_action',$GLOBALS['phpgw']->link('/eq/index.php','menuaction=eq.eq.admin&action=upload'));
3282
3283       $action = get_var('action',array('GET','POST'));
3284
3285       $this->t->pfp('out','admin_t');
3286
3287       if($action == 'upload')
3288         {
3289           $target_path = $this->upload_target_path . basename( $_FILES['uploadedfile']['name']);
3290
3291           if((($_FILES['uploadedfile']['type'] == "application/zip") ||
3292               ($_FILES['uploadedfile']['type'] == "application/x-zip-compressed") ||
3293               ($_FILES['uploadedfile']['type'] == "application/x-zip") ||
3294               ($_FILES['uploadedfile']['type'] == "application/octet-stream")) &&
3295              (move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path))) {
3296             $uploadstatus = "<b>The following file was uploaded successfully: </b><br><br>";
3297             $uploadstatus.= "Filename : " . $_FILES['uploadedfile']['name'] . "<br>";
3298             $uploadstatus.= "Type     : " . $_FILES['uploadedfile']['type'] . "<br>";
3299             $uploadstatus.= "Size     : " . $_FILES['uploadedfile']['size'] . "<br>";
3300             $this->t->set_var('uploadstatus',$uploadstatus);
3301             $this->t->pfp('uploadhandle','upload');
3302             $this->t->set_var('uploadhandle','');
3303             print "<table border=1 width=80%><tr><td>\n<pre>";
3304
3305             # make a directory for this data to be stored in
3306             $date="data_" . date("Y_m_d");
3307             $data_dir = $this->upload_target_path . $date;
3308             print "-> Making the data directory: $date<br>\n";
3309             exec('mkdir ' . $data_dir . ' 2>&1', $result, $return_code);
3310             if($return_code != 0) {
3311               print implode('\n',$result) . "<br>";
3312               print "<b><font color=red>";
3313               print "-E- Unable to create the data directory. Aborting import.";
3314               print "</font></b>";
3315               return 0;
3316             }
3317
3318             # move the file uploaded into this directory
3319             print "-> Moving the uploaded file into the data dir<br>\n";
3320             exec('mv ' . $target_path . ' ' . $data_dir . '/' . ' 2>&1', $result, $return_code);
3321             if($return_code != 0) {
3322               print implode('\n',$result) . "<br>";
3323               print "<b><font color=red>";
3324               print "-E- Unable to move the uploaded file into the data dir. Aborting import.";
3325               print "</font></b>";
3326               return 0;
3327             }
3328
3329             # unzip the data into this directory
3330             print "-> Unzipping the data<br>\n";
3331             $data_file = $data_dir . '';
3332             exec('unzip ' . $data_dir . '/*.zip -d ' . $data_dir . ' 2>&1', $result, $return_code);
3333             if($return_code != 0) {
3334               print implode('\n',$result) . "<br>";
3335               print "<b><font color=red>";
3336               print "-E- Unable to unzip the uploaded file into the data dir. Aborting import.";
3337               print "</font></b>";
3338               return 0;
3339             }
3340             exec('mv ' . $data_dir . '/*/* '. $data_dir . ' 2>&1', $result, $return_code);
3341
3342             # update the data_latest link to point to this new directory
3343             print "-> Updating the latest data dir link<br>\n";
3344             $data_latest = $this->upload_target_path . 'data_latest';
3345             exec('rm ' . $data_latest. '; ln -s ' . $data_dir .' '. $data_latest .' 2>&1', $result, $return_code);
3346             if($return_code != 0) {
3347               print implode('\n',$result) . "<br>";
3348               print "<b><font color=red>";
3349               print "-E- Unable to update the data latest link. Aborting import.";
3350               print "</font></b>";
3351               return 0;
3352             }
3353
3354             # run the import perl script to encorporate it into the DB
3355             ob_start('ob_logstdout', 2);
3356             print "-> Importing the data into the EQ database<br>\n";
3357             ob_flush(); flush(); sleep(1);
3358             $import_log = $this->upload_target_path . '/import.log';
3359             $data_log = $this->upload_target_path . '/data.log';
3360             $import_cmd = $this->script_path . 'import_ward_data ' . $data_latest . ' | tee ' . $import_log;
3361             $parse_cmd = $this->script_path . 'parse_ward_data -v ' . $data_latest . ' > ' . $data_log;
3362             #print "import_cmd: $import_cmd<br>";
3363             #print "parse_cmd: $parse_cmd<br>";
3364             ob_start('ob_logstdout', 2);
3365             passthru($import_cmd);
3366             passthru($parse_cmd);
3367             ob_flush(); flush(); sleep(1);
3368
3369             # fix the permissions of the data dir
3370             exec('chmod -R o-rwx ' . $data_dir, $result, $return_code);
3371
3372             $this->t->pfp('cmdhandle','cmd');
3373             print "</pre></td></tr></table>";
3374
3375           } else if(($_FILES['uploadedfile']['type'] != "application/zip") &&
3376                     ($_FILES['uploadedfile']['type'] != "application/x-zip-compressed") &&
3377                     ($_FILES['uploadedfile']['type'] != "application/x-zip") &&
3378                     ($_FILES['uploadedfile']['type'] != "application/octet-stream")) {
3379             $uploadstatus = "<b><font color=red>The file format must be a .zip file, please try again! </font></b>";
3380             $uploadstatus.= "<br><br><b>Detected file format: " . $_FILES['uploadedfile']['type'] . "</b>";
3381             $this->t->set_var('uploadstatus',$uploadstatus);
3382             $this->t->pfp('uploadhandle','upload',True);
3383           } else {
3384             $uploadstatus = "<b><font color=red> There was an error (" . $_FILES['uploadedfile']['error'];
3385             $uploadstatus.= ") uploading the file, please try again! </font></b>";
3386             $this->t->set_var('uploadstatus',$uploadstatus);
3387             $this->t->pfp('uploadhandle','upload',True);
3388           }
3389         }
3390       else
3391         {
3392           $this->t->set_var('adminhandle','');
3393           $this->t->pfp('adminhandle','admin');
3394         }
3395
3396       $this->save_sessiondata();
3397     }
3398
3399   function email_appt($appointment)
3400     {
3401       //print "Emailing notification of appointment: $appointment <br>";
3402
3403       $sql = "SELECT * FROM eq_appointment where appointment='$appointment'";
3404       $this->db->query($sql,__LINE__,__FILE__);
3405
3406       while ($this->db->next_record())
3407         {
3408           $appointment = $this->db->f('appointment');
3409           $presidency = $this->db->f('presidency');
3410           $interviewer = "";
3411           $email = "";
3412           $elder = $this->db->f('elder');
3413           $elder_name = "";
3414           $family = $this->db->f('family');
3415           $family_name = "";
3416           $appt_name = "";
3417           $phone = "";
3418           $location = "";
3419           $uid = $this->db->f('uid');
3420
3421           // Extract the year, month, day, hours, minutes, seconds from the appointment time
3422           $appt_date = $this->db->f('date');
3423           $date_array = explode("-",$appt_date);
3424           $year = $date_array[0]; $month = $date_array[1]; $day = $date_array[2];
3425           $appt_time = $this->db->f('time');
3426           $time_array = explode(":",$appt_time);
3427           $hour = $time_array[0]; $minute = $time_array[1]; $seconds = $time_array[2];
3428
3429           // Format the appointment time into an iCal UTC equivalent
3430           $dtstamp = gmdate("Ymd"."\T"."His"."\Z");
3431           $dtstart = gmdate("Ymd"."\T"."His"."\Z", mktime($hour,$minute,$seconds,$month,$day,$year));
3432
3433           // Set the email address of the person making the appointment
3434           $from = $GLOBALS['phpgw_info']['user']['fullname'] . "<" .
3435                   $GLOBALS['phpgw_info']['user']['preferences']['email']['address'] . ">";
3436
3437           $sql = "SELECT * FROM eq_presidency where presidency='$presidency'";
3438           $this->db2->query($sql,__LINE__,__FILE__);
3439           if($this->db2->next_record()) {
3440             $email = $this->db2->f('email');
3441             $interviewer = $this->db2->f('name');
3442           }
3443
3444           if($elder > 0) {
3445             $sql = "SELECT * FROM eq_elder where elder='$elder'";
3446             $this->db2->query($sql,__LINE__,__FILE__);
3447             if($this->db2->next_record()) {
3448               $elder_name = $this->db2->f('name');
3449               $phone = $this->db2->f('phone');
3450               $appt_name = $elder_name . " Interview";
3451               $location = "$interviewer"."'s home";
3452               $duration = 1800; // 30 minutes
3453             }
3454           }
3455
3456           if($family > 0) {
3457             $sql = "SELECT * FROM eq_family where family='$family'";
3458             $this->db2->query($sql,__LINE__,__FILE__);
3459             if($this->db2->next_record()) {
3460               $family_name = $this->db2->f('name');
3461               $phone = $this->db2->f('phone');
3462               $elder_id = $this->db2->f('elder_id');
3463               $appt_name = $family_name . " Family Visit";
3464               $sql = "SELECT * FROM eq_elder where elder='$elder_id'";
3465               $this->db3->query($sql,__LINE__,__FILE__);
3466               if($this->db3->next_record()) {
3467                 $phone = $this->db3->f('phone');
3468               }
3469               $location="";
3470               $duration = 2700; // 45 minutes
3471             }
3472           }
3473
3474           $dtend = gmdate("Ymd"."\T"."His"."\Z", mktime($hour,$minute,$seconds+$duration,$month,$day,$year));
3475           $description = "$appt_name : $phone";
3476
3477           if(($uid == 0) && ($appt_name != "")) {
3478             // Create a new calendar item for this appointment, since this must be the first time we
3479             // are sending it out.
3480             print "Sent new appointment to " . $interviewer . " at " . $email . " for " . $appt_name . "<br>";
3481             $uid = rand() . rand(); // Generate a random identifier for this appointment
3482             $subject = "Created: $appt_name";
3483
3484             $this->db->query("UPDATE eq_appointment set" .
3485                              " uid=" . $uid .
3486                              " WHERE appointment=" . $appointment,__LINE__,__FILE__);
3487
3488             $action = "PUBLISH";
3489             $this->send_ical_appt($action, $email, $from, $subject, $dtstamp, $dtstart,
3490                                   $dtend, $location, $appt_name, $description, $uid);
3491
3492           } else if(($uid != 0) && ($appt_name == "")) {
3493             // Remove the calendar item for this appointment since it has already been sent
3494             // and there is no name we have changed it to.
3495             print "Sent deleted appointment to " . $interviewer . " at " . $email . " for " . $appt_date . " " . $appt_time . "<br>";
3496             $subject = "Canceled: $appt_date $appt_time";
3497
3498             $this->db->query("UPDATE eq_appointment set" .
3499                              " uid=0" .
3500                              " WHERE appointment=" . $appointment,__LINE__,__FILE__);
3501
3502             $action = "CANCEL";
3503             $this->send_ical_appt($action, $email, $from, $subject, $dtstamp, $dtstart,
3504                                   $dtend, $location, $appt_name, $description, $uid);
3505
3506           } else if($uid != 0) {
3507             // Update the existing appointment since we have changed it
3508             print "Sent updated appointment to " . $interviewer . " at " . $email . " for " . $appt_name . "<br>";
3509
3510             $subject = "Canceled: $appt_date $appt_time";
3511             $action = "CANCEL";
3512             $this->send_ical_appt($action, $email, $from, $subject, $dtstamp, $dtstart,
3513                                   $dtend, $location, $appt_name, $description, $uid);
3514
3515             $uid = rand() . rand(); // Generate a random identifier for this appointment
3516             $this->db->query("UPDATE eq_appointment set" .
3517                              " uid=" . $uid .
3518                              " WHERE appointment=" . $appointment,__LINE__,__FILE__);
3519
3520             $subject = "Updated: $appt_name";
3521             $action = "PUBLISH";
3522             $this->send_ical_appt($action, $email, $from, $subject, $dtstamp, $dtstart,
3523                                   $dtend, $location, $appt_name, $description, $uid);
3524           }
3525
3526         }
3527
3528       return true;
3529     }
3530
3531   function send_ical_appt($action, $to, $from, $subject, $dtstamp, $dtstart, $dtend, $location, $summary, $description, $uid)
3532     {
3533       $headers = 'From: ' . "$from" . "\n" .
3534          'Reply-To: ' . "$from" . "\n" .
3535          'X-Mailer: PHP/' . phpversion() . "\n" .
3536          'Content-Type: text/calendar;' . "\n" .
3537          'Content-Transfer-Encoding: 7bit' . "\n";
3538
3539       //$message = "phone: $phone date: $date time: $time";
3540       $message ="";
3541       $message.="BEGIN:VCALENDAR" . "\n";
3542       $message.="VERSION:2.0" . "\n";
3543       $message.="PRODID:-//Microsoft Corporation//Outlook 11.0 MIMEDIR//EN" . "\n";
3544       $message.="METHOD:$action" . "\n";
3545       $message.="BEGIN:VEVENT" . "\n";
3546       $message.="ORGANIZER:MAILTO:$from". "\n";
3547       $message.="DTSTAMP:$dtstamp" . "\n";
3548       $message.="DTSTART:$dtstart" . "\n";
3549       $message.="DTEND:$dtend" . "\n";
3550       $message.="SUMMARY:$summary" . "\n";
3551       $message.="DESCRIPTION:$description" . "\n";
3552       $message.="LOCATION:$location" . "\n";
3553       $message.="UID:$uid" ."\n";
3554       $message.="TRANSP:OPAQUE" . "\n";
3555       $message.="SEQUENCE:0" . "\n";
3556       $message.="CLASS:PUBLIC" . "\n";
3557       $message.="END:VEVENT" . "\n";
3558       $message.="END:VCALENDAR" . "\n";
3559
3560       mail($to, $subject, $message, $headers);
3561
3562     }
3563 }
3564
3565 ?>