inc/class.tc.inc.php

   1 <?php
   2   /**************************************************************************\
   3   * Application: phpGroupWare - 3rd Counselor                                *
   4   * Framework: http://www.phpgroupware.org                                   *
   5   * Author: Alan J. Pippin (apippin@pippins.net)                             *
   6   * -----------------------------------------------                          *
   7   *  This program is free software; you can redistribute it and/or modify it *
   8   *  under the terms of the GNU General Public License as published by the   *
   9   *  Free Software Foundation; either version 2 of the License, or (at your  *
  10   *  option) any later version.                                              *
  11   \**************************************************************************/
  12       /* $Id: class.tc.inc.php,v 1.1.1.1 2005/07/20 07:40:32 ajp Exp $ */
  13
  14 class tc
  15 {
  16   var $db;
  17   var $db2;
  18   var $t;
  19   var $nextmatchs;
  20   var $grants;
  21   var $jscal;
  22   var $cal_options;
  23   var $default_ht_num_months;
  24   var $default_ppi_num_months;
  25   var $default_ppi_num_years;
  26   var $default_int_num_quarters;
  27   var $default_int_num_years;
  28   var $default_vis_num_years;
  29   var $default_att_num_quarters;
  30   var $max_num_districts;
  31   var $current_year;
  32   var $current_month;
  33   var $upload_target_path;
  34   var $script_path;
  35   var $max_appointments;
  36   var $max_presidency_members;
  37
  38   var $public_functions = array
  39     (
  40      'ht_view'    => True,
  41      'ht_update'  => True,
  42      'act_list'   => True,
  43      'act_view'   => True,
  44      'act_update' => True,
  45      'par_view'   => True,
  46      'ppi_view'   => True,
  47      'ppi_update' => True,
  48      'ppi_sched'  => True,
  49      'int_view'   => True,
  50      'int_update' => True,
  51      'int_sched'  => True,
  52      'vis_sched'  => True,
  53      'vis_view'   => True,
  54      'vis_update' => True,
  55      'att_view'   => True,
  56      'att_update' => True,
  57      'dir_view'   => True,
  58      'org_view'   => True,
  59      'schedule'   => True,
  60      'admin'      => True,
  61      'email'      => True,
  62      'email_appt' => True,
  63      'willing_view'   => True,
  64      'willing_update' => True,
  65      'send_ical_appt' => True,
  66      'assign_view'    => True,
  67      'assign_update'  => True,
  68      'get_time_selection_form' => True,
  69      );
  70
  71   function tc()
  72     {
  73       if(file_exists("setup/tc_config.local")) {
  74         include("setup/tc_config.local");
  75       } else {
  76         include("setup/tc_config");
  77       }
  78
  79       $this->script_path = "$this->application_path"."/bin";
  80       $this->max_presidency_members = 99;
  81       $this->max_appointments = 32768;
  82
  83       $this->db         = $GLOBALS['phpgw']->db;
  84       $this->db2        = $this->db;
  85       $this->db3        = $this->db;
  86       $this->nextmatchs = CreateObject('phpgwapi.nextmatchs');
  87       $this->t          = $GLOBALS['phpgw']->template;
  88       $this->account    = $GLOBALS['phpgw_info']['user']['account_id'];
  89       $this->grants     = $GLOBALS['phpgw']->acl->get_grants('tc');
  90       $this->grants[$this->account] = PHPGW_ACL_READ + PHPGW_ACL_ADD + PHPGW_ACL_EDIT + PHPGW_ACL_DELETE;
  91
  92       $this->jscal = CreateObject('tc.jscalendar');   // before phpgw_header() !!!
  93       $this->cal_options = 'daFormat    : "%Y-%m-%d",
  94                                 ifFormat    : "%Y-%m-%d",
  95                                 mondayFirst : false,
  96                                 weekNumbers : false';
  97
  98       $GLOBALS['phpgw_info']['flags']['app_header'] = 'The 3rd Counselor';
  99       $GLOBALS['phpgw']->common->phpgw_header();
 100
 101       $this->current_day = `date '+%d'`;
 102       $this->current_day = $this->current_day-0; // Make it numeric
 103       $this->current_month = `date '+%m'`;
 104       $this->current_month = $this->current_month-0; // Make it numeric
 105       $this->current_year = `date '+%Y'`;
 106       $this->current_year = $this->current_year-0; // Make it numeric
 107
 108       echo parse_navbar();
 109       $this->display_app_header();
 110     }
 111
 112   function save_sessiondata()
 113     {
 114
 115     }
 116
 117   function display_app_header()
 118     {
 119       $this->t->set_file(array('tc_header' => 'header.tpl'));
 120
 121       if (isset($phpgw_info['user']['preferences']['tc']['tc_font']))
 122         {
 123           $font = $phpgw_info['user']['preferences']['tc']['tc_font'];
 124         }
 125       else
 126         {
 127           $font = 'Arial';
 128         }
 129
 130       $this->t->set_var('bg_color',$phpgw_info['theme']['th_bg']);
 131       $this->t->set_var('font',$font);
 132       $link_data['menuaction'] = 'tc.tc.ht_view';
 133       $this->t->set_var('link_hometeaching',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
 134       $this->t->set_var('lang_hometeaching','HomeTeaching');
 135       $link_data['menuaction'] = 'tc.tc.act_list';
 136       $this->t->set_var('link_activity',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
 137       $this->t->set_var('lang_activity','Activities');
 138       $link_data['menuaction'] = 'tc.tc.willing_view';
 139       $this->t->set_var('link_willing',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
 140       $this->t->set_var('lang_willing','Willingness');
 141       $link_data['menuaction'] = 'tc.tc.assign_view';
 142       $this->t->set_var('link_assignment',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
 143       $this->t->set_var('lang_assignment','Assignments');
 144       $link_data['menuaction'] = 'tc.tc.par_view';
 145       $this->t->set_var('link_participation',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
 146       $this->t->set_var('lang_participation','Participation');
 147       $link_data['menuaction'] = 'tc.tc.ppi_view';
 148       $this->t->set_var('link_ppi',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
 149       $this->t->set_var('lang_ppi','PPIs');
 150       $link_data['menuaction'] = 'tc.tc.int_view';
 151       $this->t->set_var('link_int',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
 152       $this->t->set_var('lang_int','Interviews');
 153       $link_data['menuaction'] = 'tc.tc.vis_view';
 154       $this->t->set_var('link_visit',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
 155       $this->t->set_var('lang_visit','Visits');
 156       $link_data['menuaction'] = 'tc.tc.att_view';
 157       $this->t->set_var('link_attendance',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
 158       $this->t->set_var('lang_attendance','Attendance');
 159       $link_data['menuaction'] = 'tc.tc.dir_view';
 160       $this->t->set_var('link_dir',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
 161       $this->t->set_var('lang_dir','Directory');
 162       $link_data['menuaction'] = 'tc.tc.org_view';
 163       $this->t->set_var('link_org',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
 164       $this->t->set_var('lang_org','Callings');
 165       $link_data['menuaction'] = 'tc.tc.admin';
 166       $this->t->set_var('link_admin',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
 167       $this->t->set_var('lang_admin','Admin');
 168       $link_data['menuaction'] = 'tc.tc.schedule';
 169       $this->t->set_var('link_schedule',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
 170       $this->t->set_var('lang_schedule','Scheduling');
 171       $link_data['menuaction'] = 'tc.tc.email';
 172       $this->t->set_var('link_email',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
 173       $this->t->set_var('lang_email','Email');
 174
 175       $this->t->pparse('out','tc_header');
 176     }
 177
 178   function ht_view()
 179     {
 180       $this->t->set_file(array('ht_view_t' => 'ht_view.tpl'));
 181       $this->t->set_block('ht_view_t','district_list','list');
 182
 183       $this->t->set_var('linkurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ht_view'));
 184       $num_months = get_var('num_months',array('GET','POST'));
 185       if($num_months == '') { $num_months = $this->default_ht_num_months; }
 186       $this->t->set_var('num_months',$num_months);
 187       $this->t->set_var('lang_filter','Filter');
 188       if($num_months == 1) { $this->t->set_var('lang_num_months','Month of History'); }
 189       else {  $this->t->set_var('lang_num_months','Months of History'); }
 190
 191       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ht_view'));
 192       $this->t->set_var('title','Hometeaching');
 193
 194       $sql = "SELECT * FROM tc_district where valid=1 ORDER BY district ASC";
 195       $this->db->query($sql,__LINE__,__FILE__);
 196       $i=0;
 197       while ($this->db->next_record())
 198         {
 199           $districts[$i]['district'] = $this->db->f('district');
 200           $districts[$i]['name'] = $this->db->f('name');
 201           $districts[$i]['supervisor'] = $this->db->f('supervisor');
 202           $i++;
 203         }
 204
 205       $sql = "SELECT * FROM tc_indiv where valid=1 ORDER BY indiv ASC";
 206       $this->db->query($sql,__LINE__,__FILE__);
 207       $i=0;
 208       while ($this->db->next_record())
 209         {
 210           $indiv_id[$i] = $this->db->f('indiv');
 211           $indiv_name[$i] = $this->db->f('name');
 212           $indiv_phone[$indiv_id[$i]] = $this->db->f('phone');
 213           $i++;
 214         }
 215       array_multisort($indiv_name, $indiv_id);
 216
 217       // Make an array mapping indiv_ids to indiv_names
 218       for($i=0; $i < count($indiv_id); $i++) {
 219           $id = $indiv_id[$i];
 220           $indivs[$id] = $indiv_name[$i];
 221       }
 222
 223       $this->nextmatchs->template_alternate_row_color(&$this->t);
 224       for($m=$num_months; $m >= 0; $m--) { $total_families[$m]=0; }
 225       for ($i=0; $i < count($districts); $i++) {
 226         $this->t->set_var('district_number',$districts[$i]['district']);
 227         $this->t->set_var('district_name',$districts[$i]['name']);
 228         $supervisor = $districts[$i]['supervisor'];
 229
 230         // Select all the unique companionship numbers for this district
 231         $sql = "SELECT distinct companionship FROM tc_companionship where valid=1 and district=". $districts[$i]['district'];
 232         $this->db->query($sql,__LINE__,__FILE__);
 233         $j=0; $unique_companionships = '';
 234         while ($this->db->next_record())
 235           {
 236             $unique_companionships[$j]['companionship'] = $this->db->f('companionship');
 237             $j++;
 238           }
 239
 240         $comp_width=450; $visit_width=25; $table_width=$comp_width + $num_months*$visit_width;
 241         $table_data=""; $num_companionships = 0;
 242         for($m=$num_months; $m >= 0; $m--) { $visits[$m]=0; $num_families[$m]=0; }
 243         for ($j=0; $j < count($unique_companionships); $j++) {
 244           $companion_table_entry = "";
 245           // Select all the companions in each companionship
 246           $sql = "SELECT * FROM tc_companionship where valid=1 and ".
 247                  "companionship=". $unique_companionships[$j]['companionship'];
 248           $this->db->query($sql,__LINE__,__FILE__);
 249
 250           while ($this->db->next_record())
 251             {
 252               // Get this companions information
 253               if($companion_table_entry != "") { $companion_table_entry .= "<td>&nbsp;/&nbsp;</td>"; }
 254               $companionship = $this->db->f('companionship');
 255               $indiv_id = $this->db->f('indiv');
 256               $name = $indivs[$indiv_id];
 257               $phone = $indiv_phone[$indiv_id];
 258               $companion_table_entry .= "<td title=\"$phone\"><b>$name</b></td>";
 259             }
 260           $table_data.= "<tr bgcolor=#d3dce3><td colspan=20><table><tr>$companion_table_entry</tr></table><hr></td></tr>";
 261
 262           // Get the names of the families assigned this home teaching companionship
 263           $sql = "SELECT * from tc_family where valid=1 AND companionship=".$unique_companionships[$j]['companionship'];
 264           $sql = $sql . " ORDER BY name ASC";
 265           $this->db->query($sql,__LINE__,__FILE__);
 266           $k=0;
 267           while ($this->db->next_record())
 268             {
 269               $family_name = $this->db->f('name');
 270               $family_id = $this->db->f('family');
 271               $this->nextmatchs->template_alternate_row_color(&$this->t);
 272               $table_data.="<tr bgcolor=". $this->t->get_var('tr_color') ."><td>$family_name Family</td>";
 273               // Find out how many times Visits were performed by this companionship
 274               // in the past $num_months for this Family
 275               $header_row="<th width=$comp_width><font size=-2>Families</th>";
 276               for($m=$num_months; $m >= 0; $m--) {
 277                 $month = $this->current_month - $m;
 278                 $year = $this->current_year;
 279                 if($month <= 0) { $remainder = $month; $month = 12 + $remainder; $year=$year-1; }
 280                 if($month < 10) { $month = "0"."$month"; }
 281                 $month_start = "$year"."-"."$month"."-"."01";
 282                 $month_end = "$year"."-"."$month"."-"."31";
 283                 $month = "$month"."/"."$year";
 284
 285                 //print "m: $m month: $month year: $year month_start: $month_start month_end: $month_end<br>";
 286                 // Add this to the query to filter on only visits made by this companionship:
 287                 // " AND companionship=" . $unique_companionships[$j]['companionship'].
 288
 289                 // First check to see if the currently assigned companionship has visited them
 290                 $sql = "SELECT * FROM tc_visit WHERE date >= '$month_start' AND date <= '$month_end' ".
 291                    " AND companionship=".$unique_companionships[$j]['companionship'].
 292                    " AND family=". $family_id;
 293                 $query_id = $this->db2->query($sql,__LINE__,__FILE__);
 294                 if($this->db2->num_rows($query_id) == 0) {
 295                   // We did not find any visits made by the currently assigned companionship,
 296                   // look for visits made by any other companionship other than 0. (0 == EQ Presidency Visit)
 297                   $sql = "SELECT * FROM tc_visit WHERE date >= '$month_start' AND date <= '$month_end' ".
 298                      " AND companionship!=0".
 299                      " AND family=". $family_id;
 300                   $query_id = $this->db2->query($sql,__LINE__,__FILE__);
 301                 }
 302                 $this->db2->query($sql,__LINE__,__FILE__);
 303                 $link_data['menuaction'] = 'tc.tc.ht_update';
 304                 $link_data['date'] = $month_start;
 305                 $link_data['month_start'] = $month_start;
 306                 $link_data['month_end'] = $month_end;
 307                 $link_data['month'] = $month;
 308                 $link_data['district'] = $districts[$i]['district'];
 309                 $link_data['district_name'] = $districts[$i]['name'];
 310                 $link_data['action'] = 'view';
 311                 $link = $GLOBALS['phpgw']->link('/tc/index.php',$link_data);
 312                 $header_row .= "<th width=$visit_width><font size=-2><a href=$link>$month</a></th>";
 313                 if(!$total_visits[$m]) { $total_visits[$m] = 0; }
 314                 if($this->db2->next_record()) {
 315                   if($this->db2->f('visited') == 'y') {
 316                     $visits[$m]++; $total_visits[$m]++;
 317                     $num_families[$m]++; $total_families[$m]++;
 318                     $table_data .= '<td align=center><a href="'.$link.'"><img src="images/checkmark.gif"></a></td>';
 319                   }
 320                   else if($this->db2->f('visited') == 'n') {
 321                     $num_families[$m]++; $total_families[$m]++;
 322                     $table_data .= '<td align=center><a href="'.$link.'"><img src="images/x.gif"></a></td>';
 323                   }
 324                   else {
 325                     //$visits[$m]++; $total_visits[$m]++;
 326                     $table_data .= "<td>&nbsp;</td>";
 327                   }
 328                 }
 329                 else {
 330                   //$visits[$m]++; $total_visits[$m]++;
 331                   $table_data .= "<td>&nbsp;</td>";
 332                 }
 333               }
 334               $table_data .= "</tr>";
 335               $k++;
 336             }
 337           $table_data .= "<tr><td colspan=20></td></tr>";
 338         }
 339         $table_data .= "<tr><td colspan=20><hr></td></tr>";
 340         $stat_data = "<tr><td><b><font size=-2>Families Hometaught:<br>Hometeaching Percentage:</font></b></td>";
 341
 342         for($m=$num_months; $m >=0; $m--) {
 343           if($num_families[$m] > 0) {
 344             $percent = ceil(($visits[$m] / $num_families[$m])*100);
 345           } else {
 346             $percent = 0;
 347           }
 348           $stat_data .= "<td align=center><font size=-2><b>$visits[$m] / $num_families[$m]<br>$percent%</font></b></td>";
 349         }
 350         $stat_data .= "</tr>";
 351
 352         $this->t->set_var('table_width',$table_width);
 353         $this->t->set_var('header_row',$header_row);
 354         $this->t->set_var('table_data',$table_data);
 355         $this->t->set_var('stat_data',$stat_data);
 356         $this->t->fp('list','district_list',True);
 357       }
 358
 359       $totals = "<tr><td><b><font size=-2>Total Families Hometaught:<br>Total Hometeaching Percentage:</font></b></td>";
 360       for($m=$num_months; $m >=0; $m--) {
 361         if($total_families[$m] > 0) {
 362           $percent = ceil(($total_visits[$m] / $total_families[$m])*100);
 363         } else {
 364           $percent = 0;
 365         }
 366         $totals .= "<td align=center><font size=-2><b>$total_visits[$m] / $total_families[$m]<br>$percent%</font></b></td>";
 367       }
 368       $totals .= "</tr>";
 369
 370       $this->t->set_var('totals',$totals);
 371
 372       $this->t->pfp('out','ht_view_t');
 373       $this->save_sessiondata();
 374     }
 375
 376
 377   function ht_update()
 378     {
 379       $this->t->set_file(array('ht_update_t' => 'ht_update.tpl'));
 380       $this->t->set_block('ht_update_t','district_list','list');
 381       $this->t->set_block('ht_update_t','save','savehandle');
 382
 383       $district = get_var('district',array('GET','POST'));
 384       $district_name = get_var('district_name',array('GET','POST'));
 385       $date = get_var('date',array('GET','POST'));
 386       $month = get_var('month',array('GET','POST'));
 387       $month_start = get_var('month_start',array('GET','POST'));
 388       $month_end = get_var('month_end',array('GET','POST'));
 389       $action = get_var('action',array('GET','POST'));
 390
 391       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ht_view'));
 392       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ht_update&action=save'));
 393       $this->t->set_var('lang_done','Cancel');
 394       $this->t->set_var('district_name',$district_name);
 395       $this->t->set_var('district_number',$district);
 396       $this->t->set_var('title','Hometeaching Update ' . $month);
 397       $this->t->set_var('date',$date);
 398
 399       if($action == 'save')
 400         {
 401           // Get a list of all the companionships in this district
 402           $sql = "SELECT distinct companionship FROM tc_companionship where valid=1 and district=". $district;
 403           $this->db->query($sql,__LINE__,__FILE__);
 404           $j=0; $unique_companionships = '';
 405           while ($this->db->next_record())
 406             {
 407               $unique_companionships[$j]['companionship'] = $this->db->f('companionship');
 408               $j++;
 409             }
 410           for ($j=0; $j < count($unique_companionships); $j++)
 411             {
 412               //$comp=$unique_companionships[$j]['companionship'];
 413               //print "deleting from tc_visit where companionship=$comp and date=$date and district=$district<br>";
 414               // Delete all the visits that have taken place for all families for this companionsthip for this month
 415               $this->db->query("DELETE from tc_visit where companionship=" . $unique_companionships[$j]['companionship'] .
 416                                " AND " . "date='" . $date . "'",__LINE__,__FILE__);
 417             }
 418
 419           // Now, add the visits that are checked for this month
 420           $new_data = get_var('family_visited',array('POST'));
 421           foreach ($new_data as $family)
 422            {
 423              foreach ($family as $data)
 424                {
 425                  //print "family_visited: $data <br>";
 426                  $data_array = explode("/",$data);
 427                  $family_id = $data_array[0];
 428                  $companionship = $data_array[1];
 429                  $date = $data_array[2];
 430                  $visited = $data_array[3];
 431                  if($visited == "") { $visited = $data_array[4]; }
 432                  //print "family_id: $family_id companionship: $companionship date: $date visited: $visited<br>";
 433                  $this->db->query("INSERT INTO tc_visit (family,companionship,date,notes,visited) "
 434                                   . "VALUES (" . $family_id .",". $companionship .",'". $date ."','','". $visited ."')",__LINE__,__FILE__);
 435                }
 436            }
 437           $this->ht_view();
 438           return false;
 439         }
 440
 441       $sql = "SELECT * FROM tc_indiv where valid=1 ORDER BY indiv ASC";
 442       $this->db->query($sql,__LINE__,__FILE__);
 443       $i=0;
 444       while ($this->db->next_record())
 445         {
 446           $indiv_id[$i] = $this->db->f('indiv');
 447           $indiv_name[$i] = $this->db->f('name');
 448           $indiv_phone[$indiv_id[$i]] = $this->db->f('phone');
 449           $i++;
 450         }
 451       array_multisort($indiv_name, $indiv_id);
 452
 453       // Make an array mapping indiv_ids to indiv_names
 454       for($i=0; $i < count($indiv_id); $i++) {
 455           $id = $indiv_id[$i];
 456           $indivs[$id] = $indiv_name[$i];
 457       }
 458
 459       // Select all the unique companionship numbers for this district
 460       $sql = "SELECT distinct companionship FROM tc_companionship where valid=1 and district=". $district;
 461       $this->db->query($sql,__LINE__,__FILE__);
 462       $j=0; $unique_companionships = '';
 463       while ($this->db->next_record())
 464         {
 465           $unique_companionships[$j]['companionship'] = $this->db->f('companionship');
 466           $j++;
 467         }
 468
 469       $comp_width=300; $visit_width=25; $table_width=$comp_width + $visit_width;
 470       $table_data=""; $num_companionships = 0; $num_families = 0; $visits=0;
 471       for ($j=0; $j < count($unique_companionships); $j++) {
 472         $companion_table_entry = "";
 473         // Select all the companions in each companionship
 474         $sql = "SELECT * FROM tc_companionship where valid=1 and ".
 475                "companionship=". $unique_companionships[$j]['companionship'];
 476         $this->db->query($sql,__LINE__,__FILE__);
 477
 478         while ($this->db->next_record())
 479           {
 480             // Get this companions information
 481             if($companion_table_entry != "") { $companion_table_entry .= "<td>&nbsp;/&nbsp;</td>"; }
 482             $companionship = $this->db->f('companionship');
 483             $indiv_id = $this->db->f('indiv');
 484             $name = $indivs[$indiv_id];
 485             $phone = $indiv_phone[$indiv_id];
 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 tc_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             $family_name = $this->db->f('name');
 497             $family_id = $this->db->f('family');
 498             $this->nextmatchs->template_alternate_row_color(&$this->t);
 499             $table_data.="<tr bgcolor=". $this->t->get_var('tr_color') ."><td>$family_name Family</td>";
 500
 501             $header_row="<th width=$comp_width><font size=-2>Families</th>";
 502
 503             // First check to see if the currently assigned companionship has visited them
 504             $sql = "SELECT * FROM tc_visit WHERE date >= '$month_start' AND date <= '$month_end' ".
 505                    " AND companionship=".$unique_companionships[$j]['companionship'].
 506                    " AND family=". $family_id;
 507             $query_id = $this->db2->query($sql,__LINE__,__FILE__);
 508             if($this->db2->num_rows($query_id) == 0) {
 509               // We did not find any visits made by the currently assigned companionship,
 510               // look for visits made by any other companionship other than 0. (0 == EQ Presidency Visit)
 511               $sql = "SELECT * FROM tc_visit WHERE date >= '$month_start' AND date <= '$month_end' ".
 512                    " AND companionship!=0".
 513                    " AND family=". $family_id;
 514               $query_id = $this->db2->query($sql,__LINE__,__FILE__);
 515             }
 516
 517             $value = $family_id . "/" . $unique_companionships[$j]['companionship'] . "/" . $date;
 518             $header_row .= "<th width=$visit_width><font size=-2><a href=$link>$month</a></th>";
 519             if(!$total_visits) { $total_visits = 0; }
 520             if($this->db2->next_record()) {
 521               if($this->db2->f('visited') == 'y') {
 522                 $visits++; $total_visits++; $num_families++;
 523                 $table_data .= '<td width=100 align=center>';
 524                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/y" checked>Y';
 525                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/n">N';
 526                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/"> ';
 527                 $table_data .= '</td>';
 528               } else if($this->db2->f('visited') == 'n') {
 529                 $num_families++;
 530                 $table_data .= '<td width=100 align=center>';
 531                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/y">Y';
 532                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/n" checked>N';
 533                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/">';
 534                 $table_data .= '</td>';
 535               } else {
 536                 $table_data .= '<td width=100 align=center>';
 537                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/y">Y';
 538                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/n">N';
 539                 $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/" checked> ';
 540                 $table_data .= '</td>';
 541               }
 542             }
 543             else {
 544               $value .= "/";
 545               $table_data .= '<td width=100 align=center>';
 546               $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/y">Y';
 547               $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/n">N';
 548               $table_data .= '<input type="radio" name="family_visited['.$family_id.'][]" value="'.$value.'/" checked> ';
 549               $table_data .= '</td>';
 550             }
 551           }
 552         $table_data .= "</tr>";
 553         $table_data .= "<tr><td colspan=20></td></tr>";
 554       }
 555       $table_data .= "<tr><td colspan=20><hr></td></tr>";
 556       $stat_data = "<tr><td><b><font size=-2>Families Hometaught:<br>Hometeaching Percentage:</font></b></td>";
 557
 558       $percent = ceil(($visits / $num_families)*100);
 559       $stat_data .= "<td align=center><font size=-2><b>$visits / $num_families<br>$percent%</font></b></td>";
 560       $stat_data .= "</tr>";
 561
 562       $this->t->set_var('table_width',$table_width);
 563       $this->t->set_var('header_row',$header_row);
 564       $this->t->set_var('table_data',$table_data);
 565       $this->t->set_var('stat_data',$stat_data);
 566       $this->t->fp('list','district_list',True);
 567
 568       $this->t->set_var('lang_reset','Clear Form');
 569       $this->t->set_var('lang_save','Save Changes');
 570       $this->t->set_var('savehandle','');
 571
 572       $this->t->pfp('out','ht_update_t');
 573       $this->t->pfp('addhandle','save');
 574
 575       $this->save_sessiondata();
 576     }
 577
 578   function act_list()
 579     {
 580       $this->t->set_file(array('act_list_t' => 'act_list.tpl'));
 581       $this->t->set_block('act_list_t','act_list','list');
 582
 583       $this->t->set_var('lang_name','Assignment');
 584       $this->t->set_var('lang_date','Date');
 585       $this->t->set_var('lang_notes','Description');
 586
 587       $sql = "SELECT * FROM tc_activity ORDER BY date DESC";
 588       $this->db->query($sql,__LINE__,__FILE__);
 589       $total_records = $this->db->num_rows();
 590
 591       $i = 0;
 592       while ($this->db->next_record())
 593         {
 594           $activity_list[$i]['activity']  = $this->db->f('activity');
 595           $activity_list[$i]['assignment'] = $this->db->f('assignment');
 596           $activity_list[$i]['date']  = $this->db->f('date');
 597           $activity_list[$i]['notes']  = $this->db->f('notes');
 598
 599           $sql = "SELECT * FROM tc_assignment WHERE assignment='" . $activity_list[$i]['assignment'] . "'";
 600           $this->db2->query($sql,__LINE__,__FILE__);
 601           if($this->db2->next_record())
 602             {
 603               $activity_list[$i]['name'] = $this->db2->f('name');
 604               $activity_list[$i]['code'] = $this->db2->f('code');
 605             }
 606           $i++;
 607         }
 608
 609       for ($i=0; $i < count($activity_list); $i++)
 610         {
 611           $this->nextmatchs->template_alternate_row_color(&$this->t);
 612           $this->t->set_var('name',$activity_list[$i]['name']);
 613           $this->t->set_var('date',$activity_list[$i]['date']);
 614           $activity_notes = $activity_list[$i]['notes'];
 615           if(strlen($activity_notes) > 40) { $activity_notes = substr($activity_notes,0,40) . "..."; }
 616           $this->t->set_var('notes',$activity_notes);
 617
 618           $link_data['menuaction'] = 'tc.tc.act_view';
 619           $link_data['activity'] = $activity_list[$i]['activity'];
 620           $link_data['action'] = 'view';
 621           $this->t->set_var('view',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
 622           $this->t->set_var('lang_view','View');
 623
 624           $link_data['menuaction'] = 'tc.tc.act_update';
 625           $link_data['activity'] = $activity_list[$i]['activity'];
 626           $link_data['action'] = 'edit';
 627           $this->t->set_var('edit',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
 628           $this->t->set_var('lang_edit','Edit');
 629
 630           $this->t->fp('list','act_list',True);
 631         }
 632
 633       $link_data['menuaction'] = 'tc.tc.act_update';
 634       $link_data['activity'] = '0';
 635       $link_data['action'] = 'add';
 636       $this->t->set_var('add','<form method="POST" action="' . $GLOBALS['phpgw']->link('/tc/index.php',$link_data)
 637                         . '"><input type="submit" name="Add" value="' . 'Add Activity' .'"></font></form>');
 638
 639       $this->t->pfp('out','act_list_t');
 640       $this->save_sessiondata();
 641     }
 642
 643   function act_view()
 644     {
 645       $this->t->set_file(array('act_view_t' => 'act_view.tpl'));
 646       $this->t->set_block('act_view_t','part_list','list');
 647
 648       $sql = "SELECT * FROM tc_activity WHERE activity=" . intval(get_var('activity',array('GET','POST')));
 649       $this->db->query($sql,__LINE__,__FILE__);
 650       $this->db->next_record();
 651       $this->t->set_var('assignment', $this->db->f('assignment'));
 652       $this->t->set_var('date', $this->db->f('date'));
 653       $this->t->set_var('notes', $this->db->f('notes'));
 654
 655       $sql = "SELECT * FROM tc_assignment WHERE assignment='" . $this->db->f('assignment') . "'";
 656       $this->db2->query($sql,__LINE__,__FILE__);
 657       if($this->db2->next_record())
 658         {
 659           $this->t->set_var('name', $this->db2->f('name'));
 660           $this->t->set_var('code', $this->db2->f('code'));
 661         }
 662       $this->t->set_var('lang_name','Assignment');
 663       $this->t->set_var('lang_date','Date');
 664       $this->t->set_var('lang_notes','Description');
 665       $this->t->set_var('lang_done','Done');
 666       $this->t->set_var('lang_action','View');
 667
 668       $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
 669       $this->t->set_var('tr_color',$tr_color);
 670
 671       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.act_list'));
 672
 673       $link_data['menuaction'] = 'tc.tc.act_update';
 674       $link_data['activity'] = get_var('activity',array('GET','POST'));
 675       $link_data['action'] = 'edit';
 676       $this->t->set_var('edit',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
 677       $this->t->set_var('lang_edit','Edit');
 678       $this->t->set_var('cal_date',$this->db->f('date'));
 679
 680       // Now find out which indivs participated in this activity
 681       $sql = "SELECT * FROM tc_participation WHERE activity=" . intval(get_var('activity',array('GET','POST')));
 682       $this->db->query($sql,__LINE__,__FILE__);
 683       $total_records = $this->db->num_rows();
 684
 685       $i = 0;
 686       while ($this->db->next_record())
 687         {
 688           $part_list[$i]['indiv']  = $this->db->f('indiv');
 689           $i++;
 690         }
 691
 692       for ($i=0; $i < count($part_list); $i++)
 693         {
 694           $sql = "SELECT * FROM tc_indiv WHERE indiv=" . $part_list[$i]['indiv'];
 695           $this->db->query($sql,__LINE__,__FILE__);
 696           $this->db->next_record();
 697           $names[$i] = $this->db->f('name');
 698         } sort($names);
 699
 700       for ($i=0; $i < count($names); $i++)
 701         {
 702           //$this->nextmatchs->template_alternate_row_color(&$this->t);
 703           $this->t->set_var('indiv_name',$names[$i]);
 704           if(($i+1) % 3 == 0) { $this->t->set_var('table_sep',"</td></tr><tr>"); }
 705           else { $this->t->set_var('table_sep',"</td>"); }
 706           if(($i) % 3 == 0) { $this->nextmatchs->template_alternate_row_color(&$this->t); }
 707           $this->t->fp('list','part_list',True);
 708         }
 709
 710       $this->t->pfp('out','act_view_t');
 711       $this->save_sessiondata();
 712     }
 713
 714   function act_update()
 715     {
 716       $this->t->set_file(array('form' => 'act_update.tpl'));
 717       $this->t->set_block('form','indiv_list','list');
 718       $this->t->set_block('form','add','addhandle');
 719       $this->t->set_block('form','edit','edithandle');
 720       $this->t->set_var('lang_done','Done');
 721
 722       $action = get_var('action',array('GET','POST'));
 723       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.act_list'));
 724       $activity['activity'] = intval(get_var('activity',array('GET','POST')));
 725
 726       if($action == 'save')
 727         {
 728           $activity['assignment'] = get_var('assignment',array('POST'));
 729           $activity['date'] = get_var('date',array('POST'));
 730           $activity['notes']= get_var('notes',array('POST'));
 731           $this->db->query("UPDATE tc_activity set " .
 732                            "   assignment='" . $activity['assignment'] .
 733                            "', date='" . $activity['date'] . "'" .
 734                            ", notes='" . $activity['notes'] . "'" .
 735                            " WHERE activity=" . $activity['activity'],__LINE__,__FILE__);
 736
 737           // Delete all the individuals who have particiapted in this activity
 738           $this->db->query("DELETE from tc_participation where activity=".$activity['activity'],__LINE__,__FILE__);
 739
 740           // Re-add the individuals who are checked as having participated in this activity
 741           $indivs = get_var('indiv_name',array('POST'));
 742           if(is_array($indivs)) { // Only do the foreach loop if we have a valid array of indivs to work with
 743             foreach ($indivs as $indiv)
 744               {
 745                 $this->db->query("INSERT INTO tc_participation (indiv,activity) "
 746                                  . "VALUES (" . $indiv . ",". $activity['activity'] . ")",__LINE__,__FILE__);
 747               }
 748           }
 749
 750           $this->act_list();
 751           return false;
 752         }
 753
 754       if($action == 'insert')
 755         {
 756           $activity['assignment'] = get_var('assignment',array('POST'));
 757           $activity['date'] = get_var('date',array('POST'));
 758           $activity['notes']= get_var('notes',array('POST'));
 759           $this->db->query("INSERT INTO tc_activity (assignment,date,notes) "
 760                            . "VALUES ('" . $activity['assignment'] . "','"
 761                            . $activity['date'] . "','" . $activity['notes'] . "')",__LINE__,__FILE__);
 762
 763           $sql = "SELECT * FROM tc_activity WHERE assignment='".$activity['assignment']."' "
 764              . " AND date='".$activity['date']."' AND notes='".$activity['notes']."'";
 765           $this->db->query($sql,__LINE__,__FILE__);
 766           if($this->db->next_record()) {
 767             //print "activity: " . $this->db->f('activity') . "<br>";
 768             $activity['activity'] = $this->db->f('activity');
 769           }
 770
 771           $indivs = get_var('indiv_name',array('POST'));
 772           foreach ($indivs as $indiv)
 773             {
 774               $this->db->query("INSERT INTO tc_participation (indiv,activity) "
 775                                . "VALUES (" . $indiv . ",". $activity['activity'] . ")",__LINE__,__FILE__);
 776             }
 777
 778           $this->act_list();
 779           return false;
 780         }
 781
 782       if($action == 'add')
 783         {
 784           $activity['activity'] = 0;
 785           $this->t->set_var('cal_date',$this->jscal->input('date','','','','','','',$this->cal_options));
 786           $this->t->set_var('assignment','');
 787           $this->t->set_var('date','');
 788           $this->t->set_var('notes','');
 789           $this->t->set_var('lang_done','Cancel');
 790           $this->t->set_var('lang_action','Adding New Activity');
 791           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.act_update&activity='
 792                                                                 . $activity['activity'] . '&action=' . 'insert'));
 793         }
 794
 795       if($action == 'edit')
 796         {
 797           $sql = "SELECT * FROM tc_activity WHERE activity=" . $activity['activity'];
 798           $this->db->query($sql,__LINE__,__FILE__);
 799           $this->db->next_record();
 800           $this->t->set_var('cal_date',$this->jscal->input('date',$this->db->f('date'),'','','','','',$this->cal_options));
 801           $this->t->set_var('assignment', $this->db->f('assignment'));
 802           $assignment = $this->db->f('assignment');
 803           $this->t->set_var('date', $this->db->f('date'));
 804           $this->t->set_var('notes', $this->db->f('notes'));
 805           $this->t->set_var('lang_done','Cancel');
 806           $this->t->set_var('lang_action','Editing Activity');
 807           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.act_update&activity='
 808                                                                 . $activity['activity'] . '&action=' . 'save'));
 809
 810         }
 811
 812       // Create the assignments drop-down list
 813       $sql = "SELECT * FROM tc_assignment ORDER BY name ASC";
 814       $this->db->query($sql,__LINE__,__FILE__);
 815       $i = 0;
 816       while ($this->db->next_record())
 817         {
 818           $assignments[$i]['assignment']  = $this->db->f('assignment');
 819           $assignments[$i]['name'] = $this->db->f('name');
 820           $assignments[$i]['code'] = $this->db->f('code');
 821           $i++;
 822         }
 823
 824       $assignment_data.= '<select name=assignment>';
 825       $assignment_data.= '<option value=0></option>';
 826       for ($j=0; $j < count($assignments); $j++) {
 827         $id = $assignments[$j]['assignment'];
 828         $name = $assignments[$j]['name'];
 829         if($assignments[$j]['assignment'] == $assignment) { $selected[$id] = 'selected="selected"'; } else { $selected[$id] = ''; }
 830         $assignment_data.= '<option value='.$id.' '.$selected[$id].'>'.$name.'</option>';
 831       }
 832       $assignment_data.='</select>';
 833       $this->t->set_var('assignment_data',$assignment_data);
 834
 835       // Create individual selection boxes
 836       $sql = "SELECT * FROM tc_indiv";
 837       $this->db->query($sql,__LINE__,__FILE__);
 838       $i=0;
 839       while ($this->db->next_record())
 840         {
 841           if($this->db->f('valid') == 1 || $action != 'add') {
 842             $indiv_name[$i] = $this->db->f('name');
 843             $indiv_id[$i] = $this->db->f('indiv');
 844             $indiv_valid[$i] = $this->db->f('valid');
 845             $i++;
 846           }
 847         }
 848       array_multisort($indiv_name, $indiv_id, $indiv_valid);
 849
 850       $j=0;
 851       for ($i=0; $i < count($indiv_id); $i++)
 852         {
 853           //$this->nextmatchs->template_alternate_row_color(&$this->t);
 854           $sql = "SELECT * FROM tc_participation where activity=". $activity['activity'] . " AND indiv=" . $indiv_id[$i];
 855           $this->db->query($sql,__LINE__,__FILE__);
 856           if($this->db->next_record()) { $this->t->set_var('checked','checked'); $checked=1; }
 857           else { $this->t->set_var('checked',''); $checked=0; }
 858           if($checked || $indiv_valid[$i] == 1) {
 859             $this->t->set_var('indiv_name',$indiv_name[$i]);
 860             $this->t->set_var('indiv',$indiv_id[$i]);
 861             if(($j+1) % 3 == 0) { $this->t->set_var('table_sep',"</td></tr><tr>"); }
 862             else { $this->t->set_var('table_sep',"</td>"); }
 863             if(($j) % 3 == 0) { $this->nextmatchs->template_alternate_row_color(&$this->t); }
 864             $this->t->fp('list','indiv_list',True);
 865             $j++;
 866           }
 867         }
 868
 869       $this->t->set_var('lang_reset','Clear Form');
 870       $this->t->set_var('lang_add','Add Activity');
 871       $this->t->set_var('lang_save','Save Changes');
 872       $this->t->set_var('edithandle','');
 873       $this->t->set_var('addhandle','');
 874
 875       $this->t->pfp('out','form');
 876       if($action == 'edit') { $this->t->pfp('addhandle','edit'); }
 877       if($action == 'add') { $this->t->pfp('addhandle','add'); }
 878
 879       $this->save_sessiondata();
 880     }
 881
 882     function assign_view()
 883     {
 884       $this->t->set_file(array('assign_view_t' => 'assign_view.tpl'));
 885       $this->t->set_block('assign_view_t','assign_view','list');
 886
 887       $this->t->set_var('lang_name','Assignment Name');
 888       $this->t->set_var('lang_code','Code');
 889
 890       $sql = "SELECT * FROM tc_assignment ORDER BY name ASC";
 891       $this->db->query($sql,__LINE__,__FILE__);
 892       $total_records = $this->db->num_rows();
 893
 894       $i = 0;
 895       while ($this->db->next_record())
 896         {
 897           $assignment_list[$i]['assignment']  = $this->db->f('assignment');
 898           $assignment_list[$i]['name'] = $this->db->f('name');
 899           $assignment_list[$i]['code'] = $this->db->f('code');
 900           $i++;
 901         }
 902
 903       for ($i=0; $i < count($assignment_list); $i++)
 904         {
 905           $this->nextmatchs->template_alternate_row_color(&$this->t);
 906           $this->t->set_var('name',$assignment_list[$i]['name']);
 907           $this->t->set_var('code',$assignment_list[$i]['code']);
 908
 909           $link_data['menuaction'] = 'tc.tc.assign_update';
 910           $link_data['assignment'] = $assignment_list[$i]['assignment'];
 911           $link_data['action'] = 'edit';
 912           $this->t->set_var('edit',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
 913           $this->t->set_var('lang_edit','Edit');
 914
 915           $link_data['menuaction'] = 'tc.tc.assign_update';
 916           $link_data['assignment'] = '0';
 917           $link_data['action'] = 'add';
 918           $this->t->set_var('add','<form method="POST" action="' . $GLOBALS['phpgw']->link('/tc/index.php',$link_data)
 919                            . '"><input type="submit" name="Add" value="' . 'Add Assignment' .'"></font></form>');
 920
 921           $this->t->fp('list','assign_view',True);
 922         }
 923
 924       $this->t->pfp('out','assign_view_t');
 925       $this->save_sessiondata();
 926     }
 927
 928     function assign_update()
 929     {
 930       $this->t->set_file(array('form' => 'assign_update.tpl'));
 931       $this->t->set_block('form','add','addhandle');
 932       $this->t->set_block('form','edit','edithandle');
 933       $this->t->set_var('lang_done','Done');
 934
 935       $action = get_var('action',array('GET','POST'));
 936       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.assign_view'));
 937       $assignment['assignment'] = intval(get_var('assignment',array('GET','POST')));
 938
 939       if($action == 'save')
 940         {
 941           $assignment['name'] = get_var('name',array('POST'));
 942           $assignment['code'] = get_var('code',array('POST'));
 943           $this->db->query("UPDATE tc_assignment set " .
 944                            "  name='" . $assignment['name'] . "'" .
 945                            ", code='" . $assignment['code'] . "'" .
 946                            " WHERE assignment=" . $assignment['assignment'],__LINE__,__FILE__);
 947
 948           $this->assign_view();
 949           return false;
 950         }
 951
 952       if($action == 'insert')
 953         {
 954           $assignment['name'] = get_var('name',array('POST'));
 955           $assignment['code'] = get_var('code',array('POST'));
 956           $this->db->query("INSERT INTO tc_assignment (name,code) "
 957                            . "VALUES ('" . $assignment['name'] . "','"
 958                            . $assignment['code'] . "')",__LINE__,__FILE__);
 959           $this->assign_view();
 960           return false;
 961         }
 962
 963       if($action == 'add')
 964         {
 965           $assignment['assignment'] = 0;
 966           $this->t->set_var('name','');
 967           $this->t->set_var('code','');
 968           $this->t->set_var('lang_done','Cancel');
 969           $this->t->set_var('lang_action','Adding New Assignment');
 970           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.assign_update&assignment='
 971                                                                 . $assignment['assignment'] . '&action=' . 'insert'));
 972         }
 973
 974       if($action == 'edit')
 975         {
 976           $sql = "SELECT * FROM tc_assignment WHERE assignment=" . $assignment['assignment'];
 977           $this->db->query($sql,__LINE__,__FILE__);
 978           $this->db->next_record();
 979           $this->t->set_var('name', $this->db->f('name'));
 980           $this->t->set_var('code', $this->db->f('code'));
 981           $this->t->set_var('lang_done','Cancel');
 982           $this->t->set_var('lang_action','Editing Assignment');
 983           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.assign_update&assignment='
 984                                                                 . $assignment['assignment'] . '&action=' . 'save'));
 985
 986         }
 987
 988       $this->t->set_var('lang_reset','Clear Form');
 989       $this->t->set_var('lang_add','Add Assignment');
 990       $this->t->set_var('lang_save','Save Changes');
 991       $this->t->set_var('edithandle','');
 992       $this->t->set_var('addhandle','');
 993
 994       $this->t->pfp('out','form');
 995       if($action == 'edit') { $this->t->pfp('addhandle','edit'); }
 996       if($action == 'add') { $this->t->pfp('addhandle','add'); }
 997
 998       $this->save_sessiondata();
 999     }
1000
1001   function par_view()
1002     {
1003       $this->t->set_file(array('par_view_t' => 'par_view.tpl'));
1004       $this->t->set_block('par_view_t','header_list','list1');
1005       $this->t->set_block('par_view_t','indiv_list','list2');
1006
1007       // TODO:  changed this so it picks the quorum dynamically
1008       $sql = "SELECT * FROM tc_indiv where steward='Elder' and valid=1";
1009       $this->db->query($sql,__LINE__,__FILE__);
1010       $i=0;
1011       while ($this->db->next_record())
1012         {
1013           $indiv_name[$i] = $this->db->f('name');
1014           $indiv_id[$i] = $this->db->f('indiv');
1015           $i++;
1016         }
1017       array_multisort($indiv_name, $indiv_id);
1018
1019       $sql = "SELECT * FROM tc_activity ORDER BY date DESC";
1020       $this->db->query($sql,__LINE__,__FILE__);
1021       $total_records = $this->db->num_rows();
1022
1023       $i = 0;
1024       while ($this->db->next_record())
1025         {
1026           $activity_list[$i]['assignment'] = $this->db->f('assignment');
1027           $activity_list[$i]['date'] = $this->db->f('date');
1028           $activity_list[$i]['activity']  = $this->db->f('activity');
1029           $i++;
1030         }
1031
1032       $sql = "SELECT * FROM tc_assignment ORDER BY name ASC";
1033       $this->db->query($sql,__LINE__,__FILE__);
1034       $i=0;
1035       while($this->db->next_record())
1036         {
1037           $assignment_list[$i]['assignment'] = $this->db->f('assignment');
1038           $assignment_list[$i]['name'] = $this->db->f('name');
1039           $assignment_list[$i]['code'] = $this->db->f('code');
1040           $i++;
1041         }
1042
1043       $indiv_width=300; $part_width=25; $assignment_width=50;
1044       $total_width=$indiv_width+$part_width;
1045       for ($i=0; $i < count($assignment_list); $i++) {
1046         $this->t->set_var('assignment_name',$assignment_list[$i]['name']);
1047         $this->t->set_var('assignment_code',$assignment_list[$i]['code']);
1048         $this->t->fp('list1','header_list',True);
1049         $total_width += $assignment_width;
1050       }
1051
1052       for ($i=0; $i < count($indiv_id); $i++) {
1053         $participated=0; $part_table = '';
1054         $this->nextmatchs->template_alternate_row_color(&$this->t);
1055         $this->t->set_var('indiv_name',$indiv_name[$i]);
1056         for ($j=0; $j < count($assignment_list); $j++) {
1057           $date = "0000-00-00"; $checkmark=0; $num_matches=0;
1058           for ($k=0; $k < count($activity_list); $k++) {
1059             if($assignment_list[$j]['assignment'] == $activity_list[$k]['assignment']) {
1060               $sql = "SELECT * FROM tc_participation where "
1061                  . " activity=" . $activity_list[$k]['activity']
1062                  . " AND indiv=" . $indiv_id[$i];
1063               $this->db->query($sql,__LINE__,__FILE__);
1064               while($this->db->next_record()) {
1065                 if($activity_list[$k]['date'] > $date) {
1066                   $date = $activity_list[$k]['date'];
1067                 }
1068                 $checkmark=1;
1069                 $num_matches++;
1070                 $participated++;
1071               }
1072             }
1073           }
1074           if($checkmark) {
1075             $part_table .= '<td align=center><img src="images/checkmark.gif">';
1076             $part_table .= '<font size=-2>'.$num_matches.'</font><br>';
1077             $part_table .= '<font size=-2>'.$date.'</font></td>';
1078           } else {
1079             $part_table .= '<td>&nbsp;</td>';
1080           }
1081         }
1082         if($participated) { $part_table .= '<td align=center><img src="images/checkmark.gif">'.$participated.'</td>'; }
1083         else { $part_table .= '<td>&nbsp;</td>'; }
1084         $this->t->set_var('part_table',$part_table);
1085         $this->t->fp('list2','indiv_list',True);
1086       }
1087       $this->t->set_var('total_width',$total_width);
1088       $this->t->set_var('indiv_width',$indiv_width);
1089       $this->t->set_var('part_width',$part_width);
1090       $this->t->set_var('act_width',$act_width);
1091       $this->t->pfp('out','par_view_t');
1092       $this->save_sessiondata();
1093     }
1094
1095     function willing_view()
1096     {
1097       $this->t->set_file(array('willing_view_t' => 'willing_view.tpl'));
1098       $this->t->set_block('willing_view_t','header_list','list1');
1099       $this->t->set_block('willing_view_t','indiv_list','list2');
1100
1101       $this->t->set_var('lang_filter','Filter');
1102       $this->t->set_var('lang_filter_unwilling','Filter out unwilling individuals:');
1103
1104       $filter_unwilling = get_var('filter_unwilling',array('POST'));
1105       $this->t->set_var('filter_unwilling',$filter_unwilling);
1106
1107       if($filter_unwilling == 'y' || $filter_unwilling == '') {
1108         $filter_input = "<input type=\"radio\" name=\"filter_unwilling\" value=\"y\" checked>Y";
1109         $filter_input.= "<input type=\"radio\" name=\"filter_unwilling\" value=\"n\">N";
1110         $filter_input.= "&nbsp;&nbsp;";
1111       } else {
1112         $filter_input = "<input type=\"radio\" name=\"filter_unwilling\" value=\"y\">Y";
1113         $filter_input.= "<input type=\"radio\" name=\"filter_unwilling\" value=\"n\" checked>N";
1114         $filter_input.= "&nbsp;&nbsp;";
1115       }
1116       $this->t->set_var('filter_input',$filter_input);
1117
1118       // TODO:  changed this so it picks the quorum dynamically
1119       $sql = "SELECT * FROM tc_indiv where steward='Elder' and valid=1";
1120       $this->db->query($sql,__LINE__,__FILE__);
1121       $i=0;
1122       while ($this->db->next_record())
1123         {
1124           $indiv_name[$i] = $this->db->f('name');
1125           $indiv_id[$i] = $this->db->f('indiv');
1126           $indiv_phone[$indiv_id[$i]] = $this->db->f('phone');
1127           $i++;
1128         }
1129       array_multisort($indiv_name, $indiv_id);
1130
1131       $sql = "SELECT * FROM tc_assignment ORDER BY name ASC";
1132       $this->db->query($sql,__LINE__,__FILE__);
1133       $i=0;
1134       while($this->db->next_record())
1135         {
1136           $assignment_list[$i]['assignment'] = $this->db->f('assignment');
1137           $assignment_list[$i]['name'] = $this->db->f('name');
1138           $assignment_list[$i]['code'] = $this->db->f('code');
1139           $i++;
1140         }
1141
1142       $sql = "SELECT * FROM tc_activity ORDER BY date DESC";
1143       $this->db->query($sql,__LINE__,__FILE__);
1144       $total_records = $this->db->num_rows();
1145
1146       $i = 0;
1147       while ($this->db->next_record())
1148         {
1149           $activity_list[$i]['assignment'] = $this->db->f('assignment');
1150           $activity_list[$i]['date'] = $this->db->f('date');
1151           $activity_list[$i]['activity']  = $this->db->f('activity');
1152           $i++;
1153         }
1154
1155       $indiv_width=275; $willing_width=40; $assignment_width=50;
1156       $total_width=$indiv_width+$willing_width;
1157
1158       for ($i=0; $i < count($assignment_list); $i++) {
1159         $this->t->set_var('assignment_name',$assignment_list[$i]['name']);
1160         $this->t->set_var('assignment_code',$assignment_list[$i]['code']);
1161         $this->t->fp('list1','header_list',True);
1162         $total_width += $assignment_width;
1163         $total_willing[$i] = 0;
1164       }
1165
1166       for ($i=0; $i < count($indiv_id); $i++) {
1167         $willing_table = ''; $indiv_willing=0;
1168         $this->t->set_var('indiv_name',$indiv_name[$i]);
1169         $this->t->set_var('indiv_phone',$indiv_phone[$indiv_id[$i]]);
1170         $this->t->set_var('editurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.willing_update&indiv_id='
1171                                                             . $indiv_id[$i] . '&action=' . 'edit'));
1172         for ($j=0; $j < count($assignment_list); $j++) {
1173           $found_willingness=0;
1174           $sql = "SELECT * FROM tc_willingness where "
1175              . " assignment=" . $assignment_list[$j]['assignment']
1176              . " AND indiv=" . $indiv_id[$i];
1177           $this->db->query($sql,__LINE__,__FILE__);
1178           while($this->db->next_record()) {
1179             $found_willingness=1;
1180             $date_part="";
1181             $sql = "SELECT * FROM tc_activity where "
1182                . " assignment=". $assignment_list[$j]['assignment']
1183                . " ORDER by date DESC";
1184             $this->db2->query($sql,__LINE__,__FILE__);
1185             if($this->db2->next_record()) {
1186               $activity = $this->db2->f('activity');
1187               $date = $this->db2->f('date');
1188               $sql = "SELECT * FROM tc_participation where "
1189                  . " activity=" . $activity
1190                  . " AND indiv=". $indiv_id[$i];
1191               $this->db3->query($sql,__LINE__,__FILE__);
1192               if($this->db3->next_record()) {
1193                 $date_part = $date;
1194               }
1195             }
1196
1197             if($this->db->f('willing') == 'y') {
1198               $total_willing[$j]++;
1199               $indiv_willing=1;
1200               $willing_table .= '<td align=center><img src="images/checkmark.gif"><br><font size=-2>'.$date_part.'</font></td></td>';
1201             }
1202             else if($this->db->f('willing') == 'n') {
1203               $willing_table .= '<td align=center><img src="images/x.gif"></td>';
1204             }
1205             else {
1206               $indiv_willing=1;
1207               $willing_table .= "<td>&nbsp;</td>";
1208             }
1209           }
1210           if(!$found_willingness) {
1211             $indiv_willing=1;
1212             $willing_table .= "<td>&nbsp;</td>";
1213           }
1214         }
1215         if(($indiv_willing == 1) || ($filter_unwilling == 'n')) {
1216           $this->t->set_var('willing_table',$willing_table);
1217           $this->t->fp('list2','indiv_list',True);
1218           $this->nextmatchs->template_alternate_row_color(&$this->t);
1219         }
1220       }
1221
1222       $stat_table = '<td><b>Total Willing to Serve</b></td>';
1223       for ($j=0; $j < count($assignment_list); $j++) {
1224         $stat_table .= "<td align=center><b>".$total_willing[$j]."</b></td>";
1225       }
1226       $this->t->set_var('stat_table',$stat_table);
1227
1228       $this->t->set_var('total_width',$total_width);
1229       $this->t->set_var('indiv_width',$indiv_width);
1230       $this->t->set_var('willing_width',$willing_width);
1231       $this->t->pfp('out','willing_view_t');
1232       $this->save_sessiondata();
1233     }
1234
1235   function willing_update()
1236     {
1237       //print "<font color=red>Willingness Update Under Constrcution</font>";
1238       //$this->willing_view();
1239       //return false;
1240
1241       $this->t->set_file(array('willing_update_t' => 'willing_update.tpl'));
1242       $this->t->set_block('willing_update_t','assignment_list','list');
1243       $this->t->set_block('willing_update_t','save','savehandle');
1244
1245       $indiv_id = get_var('indiv_id',array('GET','POST'));
1246       $this->t->set_var('indiv_id',$indiv_id);
1247       $action = get_var('action',array('GET','POST'));
1248
1249       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.willing_view'));
1250       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.willing_update&action=save'));
1251       $this->t->set_var('lang_done','Cancel');
1252       $this->t->set_var('title','Willingness Update ');
1253
1254       if($action == 'save')
1255         {
1256           // Delete all the previous willingness entries for this individual
1257           $this->db->query("DELETE from tc_willingness where indiv=" . $indiv_id ,__LINE__,__FILE__);
1258
1259           // Now, add the assignment willingness that is checked for this individual
1260           $new_data = get_var('willingness',array('POST'));
1261           foreach ($new_data as $data)
1262             {
1263               $data_array = explode("/",$data);
1264               $assignment = $data_array[0];
1265               $willing = $data_array[1];
1266               //print "indiv_id: $indiv_id assignment: $assignment willing: $willing<br>";
1267               $this->db->query("INSERT INTO tc_willingness (indiv,assignment,willing) "
1268                                . "VALUES (" . $indiv_id .",". $assignment .",'". $willing . "')",__LINE__,__FILE__);
1269             }
1270           $this->willing_view();
1271           return false;
1272         }
1273
1274       $assignment_width=300; $willing_width=25; $table_width=$assignment_width + $willing_width;
1275       $table_data="";
1276
1277       // Find out the individual's name
1278       $sql = "SELECT * FROM tc_indiv WHERE indiv=".$indiv_id." AND valid=1";
1279       $this->db->query($sql,__LINE__,__FILE__);
1280       if($this->db->next_record()) {
1281         $indiv_name = $this->db->f('name');
1282         $this->t->set_var('indiv_name',$indiv_name);
1283       }
1284
1285       // Select all the assignments
1286       $sql = "SELECT * FROM tc_assignment ORDER by name ASC";
1287       $this->db->query($sql,__LINE__,__FILE__);
1288
1289       while ($this->db->next_record())
1290         {
1291           $assignment = $this->db->f('assignment');
1292           $assignment_name = $this->db->f('name');
1293           $assignment_code = $this->db->f('code');
1294
1295           $this->nextmatchs->template_alternate_row_color(&$this->t);
1296           $table_data.="<tr bgcolor=". $this->t->get_var('tr_color') ."><td>$assignment_name</td>";
1297
1298           $header_row="<th width=$comp_width><font size=-2>Assignments</th><th>Willingness</th>";
1299           $sql = "SELECT * FROM tc_willingness WHERE indiv=".$indiv_id." AND assignment=".$assignment;
1300           $this->db2->query($sql,__LINE__,__FILE__);
1301           $value = $assignment;
1302
1303           if($this->db2->next_record()) {
1304               if($this->db2->f('willing') == 'y') {
1305                 $table_data .= '<td width=100 align=center>';
1306                 $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/y" checked>Y';
1307                 $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/n">N';
1308                 $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/"> ';
1309                 $table_data .= '</td>';
1310               } else if($this->db2->f('willing') == 'n') {
1311                 $table_data .= '<td width=100 align=center>';
1312                 $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/y">Y';
1313                 $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/n" checked>N';
1314                 $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/">';
1315                 $table_data .= '</td>';
1316               } else {
1317                 $table_data .= '<td width=100 align=center>';
1318                 $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/y">Y';
1319                 $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/n">N';
1320                 $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/" checked> ';
1321                 $table_data .= '</td>';
1322               }
1323             }
1324           else {
1325             $table_data .= '<td width=100 align=center>';
1326             $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/y">Y';
1327             $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/n">N';
1328             $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/" checked> ';
1329             $table_data .= '</td>';
1330           }
1331
1332           $table_data .= "\n";
1333           $table_data .= "</tr>";
1334           $table_data .= "<tr><td colspan=20></td></tr>";
1335         }
1336
1337       $table_data .= "<tr><td colspan=20><hr></td></tr>";
1338
1339       $this->t->set_var('table_width',$table_width);
1340       $this->t->set_var('header_row',$header_row);
1341       $this->t->set_var('table_data',$table_data);
1342       $this->t->fp('list','assignment_list',True);
1343
1344       $this->t->set_var('lang_reset','Clear Form');
1345       $this->t->set_var('lang_save','Save Changes');
1346       $this->t->set_var('savehandle','');
1347
1348       $this->t->pfp('out','willing_update_t');
1349       $this->t->pfp('addhandle','save');
1350
1351       $this->save_sessiondata();
1352     }
1353
1354
1355     function ppi_sched()
1356       {
1357       $this->t->set_file(array('ppi_sched_t' => 'ppi_sched.tpl'));
1358       $this->t->set_block('ppi_sched_t','indiv_list','indivlist');
1359       $this->t->set_block('ppi_sched_t','appt_list','apptlist');
1360       $action = get_var('action',array('GET','POST'));
1361
1362       $this->t->set_var('lang_save','Save Appt / Pri / Notes');
1363       $this->t->set_var('lang_reset','Clear Changes');
1364
1365       $this->t->set_var('ppi_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_view'));
1366       $this->t->set_var('ppi_link_title','Yearly PPIs');
1367
1368       $this->t->set_var('schedule_ppi_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_sched'));
1369       $this->t->set_var('schedule_ppi_link_title','Schedule Yearly PPIs');
1370
1371       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_sched&action=save'));
1372       $this->t->set_var('title','Yearly PPI Scheduler');
1373
1374       $indiv_width=500; $phone_width=25; $pri_width=10; $notes_width=128; $ppi_date_width=20;
1375       $table_width=$indiv_width + $phone_width + $pri_width + $notes_width + $ppi_date_width;
1376       $header_row = "<th width=$indiv_width><font size=-2>individual Name</th>";
1377       $header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
1378       $header_row.= "<th width=$pri_width><font size=-2>Priority</th>";
1379       $header_row.= "<th width=$ppi_date_width><font size=-2>Last PPI</th>";
1380       $header_row.= "<th width=$notes_width><font size=-2>Scheduling Notes</th>";
1381       $table_data=""; $completed_data=""; $totals_data="";
1382
1383       $year = date('Y');
1384
1385       // Get the EQ President
1386       $sql = "SELECT * FROM tc_presidency where president=1 and valid=1";
1387       $this->db->query($sql,__LINE__,__FILE__);
1388       if($this->db->next_record()) {
1389         $president_name = $this->db->f('name');
1390         $president_name_array = explode(",",$president_name);
1391         $president_last_name = $president_name_array[0];
1392         $president_id = $this->db->f('indiv');
1393         $presidency_id = $this->db->f('presidency');
1394         $interviewer = $this->db->f('indiv');
1395         $district_number = '*';
1396         $district_name = $president_name;
1397         $sql = "SELECT * FROM tc_indiv where indiv='$president_id'";
1398         $this->db2->query($sql,__LINE__,__FILE__);
1399         if($this->db2->next_record()) {
1400           $indiv_id = $this->db2->f('indiv_id');
1401         }
1402         $sql = "SELECT * FROM tc_indiv where indiv_id='$indiv_id'";
1403         $this->db2->query($sql,__LINE__,__FILE__);
1404         if($this->db2->next_record()) {
1405           $president_address = $this->db2->f('address');
1406         }
1407       } else {
1408         print "<hr><font color=red><h3>-E- Unable to locate President in tc_presidency table</h3></font></hr>";
1409         return;
1410       }
1411
1412       if($action == 'save')
1413         {
1414           // Save any changes made to the appointment table
1415           $new_data = get_var('appt_notes',array('POST'));
1416           if($new_data != "") {
1417             foreach ($new_data as $entry)
1418               {
1419                 $indiv = $entry['indiv'];
1420                 $appointment = $entry['appointment'];
1421                 $location = $entry['location'];
1422                 if($location == "") { $location = "$president_last_name"." home ($president_address)"; }
1423                 if($indiv == 0) { $location = ""; }
1424
1425                 //Only perform a database update if we have made a change to this appointment
1426                 $sql = "SELECT * FROM tc_appointment where appointment='$appointment' and indiv='$indiv' and location='$location'";
1427                 $this->db->query($sql,__LINE__,__FILE__);
1428                 if(!$this->db->next_record()) {
1429                   // Perform database save actions here
1430                   $this->db->query("UPDATE tc_appointment set " .
1431                                    " indiv='" . $indiv . "'" .
1432                                    ",location='" . $location . "'" .
1433                                    " WHERE appointment=" . $appointment,__LINE__,__FILE__);
1434                   // Email the appointment
1435                   $this->email_appt($appointment);
1436                 }
1437
1438               }
1439           }
1440
1441           // Save any changes made to the ppi notes table
1442           $new_data = get_var('ppi_notes',array('POST'));
1443           foreach ($new_data as $entry)
1444            {
1445              $ppi_notes = $entry['notes'];
1446              $indiv_id = $entry['indiv_id'];
1447              $ppi_pri = $entry['pri'];
1448
1449              // Perform database save actions here
1450              $this->db->query("UPDATE tc_indiv set " .
1451                               " ppi_notes='" . $ppi_notes . "'" .
1452                               ",ppi_pri='" . $ppi_pri . "'" .
1453                               " WHERE indiv=" . $indiv_id,__LINE__,__FILE__);
1454
1455            }
1456
1457           $take_me_to_url = $GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_sched');
1458           //Header('Location: ' . $take_me_to_url);
1459         }
1460
1461       // create the individual id -> individual name mapping
1462       // TODO:  changed this so it picks the quorum dynamically
1463       $sql = "SELECT * FROM tc_indiv where valid=1 and steward='Elder' ORDER BY name ASC";
1464       $this->db->query($sql,__LINE__,__FILE__);
1465       $i=0;
1466       $indiv_id = NULL;
1467       $indiv_name = NULL;
1468       while ($this->db->next_record())
1469         {
1470           $indiv_name[$i] = $this->db->f('name');
1471           $indiv_id[$i] = $this->db->f('indiv');
1472           $i++;
1473         }
1474       array_multisort($indiv_name, $indiv_id);
1475
1476       // APPOINTMENT TABLE
1477       $date_width=250; $time_width=100; $indiv_width=200; $location_width=100;
1478       $appt_table_width=$date_width + $time_width + $indiv_width + $location_width;
1479       $appt_header_row = "<th width=$date_width><font size=-2>Date</th>";
1480       $appt_header_row.= "<th width=$time_width><font size=-2>Time</th>";
1481       $appt_header_row.= "<th width=$indiv_width><font size=-2>indiv</th>";
1482       $appt_header_row.= "<th width=$location_width><font size=-2>Location</th>";
1483       $appt_table_data = "";
1484
1485       $total_indivs=0; $indivs_with_yearly_ppi=0;
1486
1487       // Display a scheduling table for the EQ President
1488       $table_data=""; $appt_table_data="";
1489       $table_title = "District ".$district_number.": ".$district_name.": All indivs with Yearly PPI Not Completed";
1490       $appt_table_title = "District ".$district_number.": ".$district_name.": Yearly PPI Appointment Slots";
1491       $this->t->set_var('table_title',$table_title);
1492       $this->t->set_var('appt_table_title',$appt_table_title);
1493
1494       // query the database for all the appointments
1495       $sql = "SELECT * FROM tc_appointment where presidency=".$presidency_id." and date>=CURDATE() ORDER BY date ASC, time ASC";
1496       $this->db->query($sql,__LINE__,__FILE__);
1497
1498       while ($this->db->next_record())
1499         {
1500           $appointment = $this->db->f('appointment');
1501           $indiv = $this->db->f('indiv');
1502           $location = $this->db->f('location');
1503           if(($location == "") && ($indiv > 0)) { $location = "$president_last_name"." home ($president_address)"; }
1504
1505           $date = $this->db->f('date');
1506           $date_array = explode("-",$date);
1507           $year = $date_array[0]; $month = $date_array[1]; $day = $date_array[2];
1508           $day_string = date("l d-M-Y", mktime(0,0,0,$month,$day,$year));
1509
1510           $time = $this->db->f('time');
1511           $time_array = explode(":",$time);
1512           $time_string = date("g:i a", mktime($time_array[0], $time_array[1], $time_array[2]));
1513
1514           $appt_table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1515           $appt_table_data.= "<td align=center>$day_string</td>";
1516           $appt_table_data.= "<td align=center>$time_string</td>";
1517
1518           $appt_table_data.= '<td align=center><select name=appt_notes['.$appointment.'][indiv]>';
1519           $appt_table_data.= '<option value=0></option>';
1520           for ($i=0; $i < count($indiv_id); $i++) {
1521             $id = $indiv_id[$i];
1522             $name = $indiv_name[$i];
1523             if($indiv_id[$i] == $indiv) { $selected[$id] = 'selected="selected"'; } else { $selected[$id] = ''; }
1524             $appt_table_data.= '<option value='.$id.' '.$selected[$id].'>'.$name.'</option>';
1525           }
1526           $appt_table_data.='</select></td>';
1527
1528           $appt_table_data.= '<td align=center><input type=text size="35" maxlength="120" ';
1529           $appt_table_data.= 'name="appt_notes['.$appointment.'][location]" value="'.$location.'">';
1530
1531           $appt_table_data.= '<input type=hidden name="appt_notes['.$appointment.'][appointment]" value="'.$appointment.'">';
1532
1533           $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1534           $this->t->set_var('tr_color',$tr_color);
1535         }
1536
1537       $this->t->set_var('appt_table_data',$appt_table_data);
1538       $this->t->set_var('appt_header_row',$appt_header_row);
1539       $this->t->set_var('appt_table_width',$appt_table_width);
1540
1541       // PPI SCHEDULING TABLE
1542       // TODO:  changed this so it picks the quorum dynamically
1543       $sql = "SELECT * FROM tc_indiv where valid=1 and steward='Elder' ORDER BY ppi_pri ASC";
1544       $this->db->query($sql,__LINE__,__FILE__);
1545
1546       $i=0;
1547       $indiv_id = NULL;
1548       while ($this->db->next_record())
1549         {
1550           $indiv_id[$i] = $this->db->f('indiv');
1551           $indiv_name[$i] = $this->db->f('name');
1552           $indiv_phone[$indiv_id[$i]] = $this->db->f('phone');
1553           $indiv_ppi_pri[$indiv_id[$i]] = $this->db->f('ppi_pri');
1554           $indiv_ppi_notes[$indiv_id[$i]] = $this->db->f('ppi_notes');
1555           $i++;
1556           $total_indivs++;
1557         }
1558
1559       $max = count($indiv_id);
1560
1561       for($i=0; $i < $max; $i++) {
1562           $id = $indiv_id[$i];
1563           $name = $indiv_name[$i];
1564           $phone = $indiv_phone[$id];
1565           $ppi_pri = $indiv_ppi_pri[$id];
1566           $ppi_notes = $indiv_ppi_notes[$id];
1567
1568           // If this individual has had a yearly PPI this year, don't show him on the schedule list
1569           $year_start = $year - 1 . "-12-31"; $year_end = $year + 1 . "-01-01";
1570           $sql = "SELECT * FROM tc_interview WHERE date > '$year_start' AND date < '$year_end' ".
1571              "AND indiv=" . $id . " AND interview_type='ppi'";
1572           $this->db2->query($sql,__LINE__,__FILE__);
1573
1574           if(!$this->db2->next_record()) {
1575             $sql = "SELECT * FROM tc_interview WHERE indiv=" . $id . " AND interview_type='ppi' ORDER BY date DESC";
1576             $this->db->query($sql,__LINE__,__FILE__);
1577             if($this->db->next_record()) { $date = $this->db->f('date'); } else { $date = ""; }
1578             $link_data['menuaction'] = 'tc.tc.ppi_update';
1579             $link_data['indiv'] = $id;
1580             $link_data['name'] = $name;
1581             $link_data['interview'] = '';
1582             $link_data['interview_type'] = 1;
1583             $link_data['action'] = 'add';
1584             $link_data['interviewer'] = $interviewer;
1585             $link = $GLOBALS['phpgw']->link('/tc/index.php',$link_data);
1586             $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1587             $this->t->set_var('tr_color',$tr_color);
1588             $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
1589             $table_data.= "<td align=center>$phone</td>";
1590             //$table_data.= "<td align=center>$ppi_pri</td>";
1591             $table_data.= "<td align=center>";
1592             $table_data.= '<select name=ppi_notes['.$i.'][pri]>';
1593             foreach(range(0,6) as $num) {
1594               if($num == 0) { $num = 1; } else {$num = $num*5; }
1595               if($ppi_pri == $num) { $selected[$num] = 'selected="selected"'; } else { $selected[$num] = ''; }
1596               $table_data.= '<option value='.$num.' '.$selected[$num].'>'.$num.'</option>';
1597             }
1598             $table_data.= '</select></td>';
1599             $table_data.= "<td align=center>$date</td>";
1600             $table_data.= '<td><input type=text size="50" maxlength="128" name="ppi_notes['.$i.'][notes]" value="'.$ppi_notes.'">';
1601             $table_data.= '<input type=hidden name="ppi_notes['.$i.'][indiv_id]" value="'.$id.'">';
1602             $table_data.= '<input type=hidden name="ppi_notes['.$i.'][indiv_name]" value="'.$name.'">';
1603             $table_data.= '</td>';
1604             $table_data.= '</tr>';
1605           } else {
1606             $link_data['menuaction'] = 'tc.tc.ppi_update';
1607             $link_data['interviewer'] = $this->db2->f('interviewer');
1608             $link_data['indiv'] = $this->db2->f('indiv');
1609             $link_data['name'] = $name;
1610             $link_data['interview'] = $this->db2->f('interview');
1611             $link_data['interview_type'] = $this->db2->f('interview_type');
1612             $link_data['action'] = 'view';
1613             $link = $GLOBALS['phpgw']->link('/tc/index.php',$link_data);
1614             $indivs_with_yearly_ppi++;
1615             $date = $this->db2->f('date');
1616             $ppi_notes = $this->db2->f('notes');
1617             if(strlen($ppi_notes) > 40) { $ppi_notes = substr($ppi_notes,0,40) . "..."; }
1618             $tr_color2 = $this->nextmatchs->alternate_row_color($tr_color2);
1619             $this->t->set_var('tr_color2',$tr_color2);
1620             $completed_data.= "<tr bgcolor=". $this->t->get_var('tr_color2') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
1621             $completed_data.= "<td align=center>$phone</td>";
1622             $completed_data.= "<td align=center><a href=".$link.">$date</a></td>";
1623             $completed_data.= "<td align=left>$ppi_notes</td>";
1624             $completed_data.= '</tr>';
1625           }
1626       } // End for individuals Loop
1627
1628       $name_width=175; $phone_width=100; $date_width=100; $notes_width=300;
1629       $completed_table_width=$name_width + $phone_width + $date_width + $notes_width;
1630       $completed_header_row = "<th width=$name_width><font size=-2>Individual Name</th>";
1631       $completed_header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
1632       $completed_header_row.= "<th width=$date_width><font size=-2>Date</th>";
1633       $completed_header_row.= "<th width=$notes_width><font size=-2>PPI Notes</th>";
1634
1635       $this->t->set_var('table_width',$table_width);
1636       $this->t->set_var('header_row',$header_row);
1637       $this->t->set_var('table_data',$table_data);
1638       $this->t->set_var('completed_header_row',$completed_header_row);
1639       $this->t->set_var('completed_table_width',$completed_table_width);
1640       $this->t->set_var('completed',$completed_data);
1641       $this->t->fp('indivlist','indiv_list',True);
1642
1643       $indivs_width=300; $totals_width=100;
1644       $totals_table_width=$indivs_width + $totals_width;
1645       $totals_header_row = "<th width=$indivs_width><font size=-2>Individuals</th>";
1646       $totals_header_row.= "<th width=$totals_width><font size=-2>$year</th>";
1647       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1648       $totals_data.= "<td align=left><font size=-2><b>Total Individuals with yearly PPIs completed:</b></font></td>";
1649       $totals_data.= "<td align=center><font size=-2><b>$indivs_with_yearly_ppi / $total_indivs</b></font></td>";
1650       $percent = ceil(($indivs_with_yearly_ppi / $total_indivs)*100);
1651       $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1652       $this->t->set_var('tr_color',$tr_color);
1653       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1654       $totals_data.= "<td align=left><font size=-2><b>Percentage:</b></font></td>";
1655       $totals_data.= "<td align=center><font size=-2><b>$percent%</b></font></td>";
1656       $totals_data.= "</tr>";
1657
1658       $this->t->set_var('totals',$totals_data);
1659       $this->t->set_var('totals_header_row',$totals_header_row);
1660       $this->t->set_var('totals_table_width',$totals_table_width);
1661
1662       $this->t->pfp('out','ppi_sched_t');
1663       $this->save_sessiondata();
1664
1665     }
1666
1667   function int_sched()
1668     {
1669       $this->t->set_file(array('int_sched_t' => 'int_sched.tpl'));
1670       $this->t->set_block('int_sched_t','indiv_list','indivlist');
1671       $this->t->set_block('int_sched_t','appt_list','apptlist');
1672       $action = get_var('action',array('GET','POST'));
1673
1674       $this->t->set_var('lang_save','Save Appt / Pri / Notes');
1675       $this->t->set_var('lang_reset','Clear Changes');
1676
1677       $this->t->set_var('int_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_view'));
1678       $this->t->set_var('int_link_title','Hometeaching Interviews');
1679
1680       $this->t->set_var('schedule_int_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_sched'));
1681       $this->t->set_var('schedule_int_link_title','Schedule Hometeaching Interviews');
1682
1683       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_sched&action=save'));
1684       $this->t->set_var('title','Hometeaching Interviews Scheduler');
1685
1686       $indiv_width=500; $phone_width=25; $pri_width=10; $notes_width=128; $int_date_width=20;
1687       $table_width=$indiv_width + $phone_width + $pri_width + $notes_width + $int_date_width;
1688       $header_row = "<th width=$indiv_width><font size=-2>individual Name</th>";
1689       $header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
1690       $header_row.= "<th width=$pri_width><font size=-2>Priority</th>";
1691       $header_row.= "<th width=$int_date_width><font size=-2>Last Interview</th>";
1692       $header_row.= "<th width=$notes_width><font size=-2>Scheduling Notes</th>";
1693       $table_data=""; $completed_data=""; $totals_data="";
1694
1695       $year = date('Y');
1696       $month = date('m');
1697       $nextyear = $year + 1;
1698       if($month >= 1 && $month <= 3) { $quarter_start=$year."-01-01"; $quarter_end=$year."-04-01"; }
1699       if($month >= 4 && $month <= 6) { $quarter_start=$year."-04-01"; $quarter_end=$year."-07-01"; }
1700       if($month >= 7 && $month <= 9) { $quarter_start=$year."-07-01"; $quarter_end=$year."-10-01"; }
1701       if($month >= 10 && $month <= 12) { $quarter_start=$year."-10-01"; $quarter_end=$nextyear."-01-01"; }
1702       //print "year: $year month: $month quarter_start: $quarter_start quarter_end: $quarter_end<br>";
1703
1704       // create the individual id -> individual name mapping
1705       // TODO:  changed this so it picks the quorum dynamically
1706       $sql = "SELECT * FROM tc_indiv where steward='Elder' and valid=1 ORDER BY name ASC";
1707       $this->db->query($sql,__LINE__,__FILE__);
1708       $i=0;
1709       $indiv_id_data = NULL;
1710       $indiv_name_data = NULL;
1711       while ($this->db->next_record())
1712         {
1713           $indiv_name_data[$i] = $this->db->f('name');
1714           $indiv_id_data[$i] = $this->db->f('indiv');
1715           $individ2name[$indiv_id_data[$i]] = $indiv_name_data[$i];
1716           $i++;
1717         }
1718       // add any YM that are home teachers
1719       $sql = "SELECT * FROM tc_companionship where valid=1";
1720       $this->db->query($sql,__LINE__,__FILE__);
1721       while ($this->db->next_record())
1722       {
1723           $tmp_indiv_id = $this->db->f('indiv');
1724           $sql = "Select * FROM tc_indiv where indiv='$tmp_indiv_id' and steward='' and valid=1";
1725           $this->db2->query($sql,__LINE__,__FILE__);
1726           while ($this->db2->next_record()) {
1727               $indiv_name_data[$i] = $this->db2->f('name');
1728               $indiv_id_data[$i] = $this->db2->f('indiv');
1729               $individ2name[$indiv_id_data[$i]] = $indiv_name_data[$i];
1730               $i++;
1731           }
1732       }
1733       array_multisort($indiv_name_data, $indiv_id_data);
1734
1735       if($action == 'save')
1736         {
1737           // Save any changes made to the appointment table
1738           $new_data = get_var('appt_notes',array('POST'));
1739           if($new_data != "") {
1740             foreach ($new_data as $entry)
1741               {
1742                 $indiv = $entry['indiv'];
1743                 $appointment = $entry['appointment'];
1744                 $location = $entry['location'];
1745                 if($location == "") {
1746                   $supervisor = $entry['supervisor'];
1747                   $supervisor_array = explode(",", $individ2name[$supervisor]);
1748                   $supervisor_last_name = $supervisor_array[0];
1749                   $sql = "SELECT * FROM tc_indiv where indiv='$supervisor'";
1750                   $this->db2->query($sql,__LINE__,__FILE__);
1751                   if($this->db2->next_record()) {
1752                     $indiv_id = $this->db2->f('indiv_id');
1753                   }
1754                   $sql = "SELECT * FROM tc_indiv where indiv_id='$indiv_id'";
1755                   $this->db2->query($sql,__LINE__,__FILE__);
1756                   if($this->db2->next_record()) {
1757                     $supervisor_address = $this->db2->f('address');
1758                   }
1759                   $location = "$supervisor_last_name"." home ($supervisor_address)";
1760                 }
1761                 if($indiv == 0) { $location = ""; }
1762
1763                 //print "indiv: $indiv appointment: $appointment <br>";
1764                 //Only perform a database update if we have made a change to this appointment
1765                 $sql = "SELECT * FROM tc_appointment where appointment='$appointment' and indiv='$indiv' and location='$location'";
1766                 $this->db->query($sql,__LINE__,__FILE__);
1767                 if(!$this->db->next_record()) {
1768                   // Perform database save actions here
1769                   $this->db->query("UPDATE tc_appointment set " .
1770                                    " indiv='" . $indiv . "'" .
1771                                    ",location='" . $location . "'" .
1772                                    " WHERE appointment=" . $appointment,__LINE__,__FILE__);
1773
1774                   // Email the appointment
1775                   $this->email_appt($appointment);
1776                 }
1777               }
1778           }
1779
1780           // Save any changes made to the int notes table
1781           $new_data = get_var('int_notes',array('POST'));
1782           foreach ($new_data as $entry)
1783            {
1784              $int_notes = $entry['notes'];
1785              $indiv_id = $entry['indiv_id'];
1786              $indiv_name = $entry['indiv_name'];
1787              $int_pri = $entry['pri'];
1788              //print "int_notes: $int_notes indiv_name: $indiv_name <Br>";
1789              // Perform database save actions here
1790              $this->db->query("UPDATE tc_indiv set " .
1791                               " int_notes='" . $int_notes . "'" .
1792                               ",int_pri='" . $int_pri . "'" .
1793                               " WHERE indiv=" . $indiv_id,__LINE__,__FILE__);
1794
1795            }
1796
1797           $take_me_to_url = $GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_sched');
1798           //Header('Location: ' . $take_me_to_url);
1799         }
1800
1801       // Get the Districts
1802       $sql = "SELECT * FROM tc_district where valid=1 ORDER BY district ASC";
1803       $this->db->query($sql,__LINE__,__FILE__);
1804       $i=0;
1805       while ($this->db->next_record())
1806         {
1807           $district = $this->db->f('district');
1808           $districts[$i]['district'] = $this->db->f('district');
1809           $districts[$i]['name'] = $this->db->f('name');
1810           $districts[$i]['supervisor'] = $this->db->f('supervisor');
1811           $sql = "SELECT * FROM tc_presidency where district=$district and valid=1";
1812           $this->db2->query($sql,__LINE__,__FILE__);
1813           if($this->db2->next_record()) {
1814             $districts[$i]['presidency'] = $this->db2->f('presidency');
1815           }
1816           $i++;
1817         }
1818
1819       // APPOINTMENT TABLE
1820       $district = 1;
1821       $date_width=250; $time_width=100; $indiv_width=200; $location_width=100;
1822       $appt_table_width=$date_width + $time_width + $indiv_width + $location_width;
1823       $appt_header_row = "<th width=$date_width><font size=-2>Date</th>";
1824       $appt_header_row.= "<th width=$time_width><font size=-2>Time</th>";
1825       $appt_header_row.= "<th width=$indiv_width><font size=-2>Individual</th>";
1826       $appt_header_row.= "<th width=$location_width><font size=-2>Location</th>";
1827       $appt_table_data = "";
1828
1829       $total_comps=0; $comps_with_quarterly_int=0;
1830
1831       // Display a scheduling table for each district
1832       for ($d=0; $d < count($districts); $d++) {
1833       $table_data=""; $appt_table_data="";
1834       $this->t->set_var('district_number',$districts[$d]['district']);
1835       $this->t->set_var('district_name',$districts[$d]['name']);
1836       $supervisor = $districts[$d]['supervisor'];
1837       $supervisor_array = explode(",", $supervisor);
1838       $supervisor_last_name = $supervisor_array[0];
1839       $sql = "SELECT * FROM tc_indiv where indiv='$supervisor'";
1840       $this->db2->query($sql,__LINE__,__FILE__);
1841       if($this->db2->next_record()) {
1842         $indiv_id = $this->db2->f('indiv_id');
1843       }
1844       $sql = "SELECT * FROM tc_indiv where indiv_id='$indiv_id'";
1845       $this->db2->query($sql,__LINE__,__FILE__);
1846       if($this->db2->next_record()) {
1847         $supervisor_address = $this->db2->f('address');
1848       }
1849       $location = "$supervisor_last_name"." home ($supervisor_address)";
1850       $table_title = "District ".$districts[$d]['district'].": ".$districts[$d]['name'].": All Individuals with Interviews Not Completed";
1851       $appt_table_title = "District ".$districts[$d]['district'].": ".$districts[$d]['name'].": Interview Appointment Slots";
1852       $this->t->set_var('table_title',$table_title);
1853       $this->t->set_var('appt_table_title',$appt_table_title);
1854
1855       // query the database for all the appointments
1856       $sql = "SELECT * FROM tc_appointment where presidency=".$districts[$d]['presidency']." and date>=CURDATE() ORDER BY date ASC, time ASC";
1857       $this->db->query($sql,__LINE__,__FILE__);
1858
1859       while ($this->db->next_record())
1860         {
1861           $appointment = $this->db->f('appointment');
1862           $indiv = $this->db->f('indiv');
1863           $location = $this->db->f('location');
1864           if(($location == "") && ($indiv > 0)) { $location = "$supervisor_last_name"." home ($supervisor_address)"; }
1865
1866           $date = $this->db->f('date');
1867           $date_array = explode("-",$date);
1868           $year = $date_array[0]; $month = $date_array[1]; $day = $date_array[2];
1869           $day_string = date("l d-M-Y", mktime(0,0,0,$month,$day,$year));
1870
1871           $time = $this->db->f('time');
1872           $time_array = explode(":",$time);
1873           $time_string = date("g:i a", mktime($time_array[0], $time_array[1], $time_array[2]));
1874
1875           $appt_table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1876           $appt_table_data.= "<td align=center>$day_string</td>";
1877           $appt_table_data.= "<td align=center>$time_string</td>";
1878
1879           $appt_table_data.= '<td align=center><select name=appt_notes['.$appointment.'][indiv]>';
1880           $appt_table_data.= '<option value=0></option>';
1881           for ($i=0; $i < count($indiv_id_data); $i++) {
1882             $id = $indiv_id_data[$i];
1883             $name = $indiv_name_data[$i];
1884             if($indiv_id_data[$i] == $indiv) { $selected[$id] = 'selected="selected"'; } else { $selected[$id] = ''; }
1885             $appt_table_data.= '<option value='.$id.' '.$selected[$id].'>'.$name.'</option>';
1886           }
1887           $appt_table_data.='</select></td>';
1888
1889           $appt_table_data.= '<td align=center><input type=text size="35" maxlength="120" ';
1890           $appt_table_data.= 'name="appt_notes['.$appointment.'][location]" value="'.$location.'">';
1891
1892           $appt_table_data.= '<input type=hidden name="appt_notes['.$appointment.'][appointment]" value="'.$appointment.'">';
1893           $appt_table_data.= '<input type=hidden name="appt_notes['.$appointment.'][supervisor]" value="'.$supervisor.'">';
1894
1895           $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1896           $this->t->set_var('tr_color',$tr_color);
1897         }
1898
1899       $this->t->set_var('appt_table_data',$appt_table_data);
1900       $this->t->set_var('appt_header_row',$appt_header_row);
1901       $this->t->set_var('appt_table_width',$appt_table_width);
1902
1903       // INTERVIEW SCHEDULING TABLE
1904
1905       // Select all the unique companionship numbers for this district
1906       $sql = "SELECT distinct companionship FROM tc_companionship where valid=1 and district=". $districts[$d]['district'];
1907       $this->db->query($sql,__LINE__,__FILE__);
1908       $j=0; $unique_companionships = '';
1909       while ($this->db->next_record())
1910         {
1911           $unique_companionships[$j]['companionship'] = $this->db->f('companionship');
1912           $j++;
1913         }
1914
1915       $i=0;
1916       for ($j=0; $j < count($unique_companionships); $j++) {
1917         // Select all the companions from each companionship
1918         $sql = "SELECT * FROM tc_companionship where valid=1 and companionship=". $unique_companionships[$j]['companionship'];
1919         $this->db->query($sql,__LINE__,__FILE__);
1920         $k=0; $int_completed=0;
1921         $comp = $unique_companionships[$j]['companionship'];
1922         $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1923         $this->t->set_var('tr_color',$tr_color);
1924         $total_comps++;
1925         while ($this->db->next_record())
1926             {
1927               // Get this companions information
1928               $indiv_id = $this->db->f('indiv');
1929
1930               $sql = "SELECT * FROM tc_indiv where indiv='$indiv_id'";
1931               $this->db2->query($sql,__LINE__,__FILE__);
1932               if($this->db2->next_record())
1933               {
1934                       $indiv_id = $this->db2->f('indiv');
1935                       $indiv_name = $this->db2->f('name');
1936                       $indiv_phone[$indiv_id] = $this->db2->f('phone');
1937                       $indiv_int_pri[$indiv_id] = $this->db2->f('int_pri');
1938                       $indiv_int_notes[$indiv_id] = $this->db2->f('int_notes');
1939               }
1940               $id = $indiv_id;
1941               $name = $indiv_name;
1942               $phone = $indiv_phone[$id];
1943               $int_pri = $indiv_int_pri[$id];
1944               $int_notes = $indiv_int_notes[$id];
1945
1946               // If the companionship has already had its quarterly interview,
1947               // Skip the other companion in the companionship.
1948               if($int_completed == 1) {
1949                 $completed_data.= "<tr bgcolor=". $this->t->get_var('tr_color2') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
1950                 $completed_data.= "<td align=center>$phone</td>";
1951                 $completed_data.= "<td align=center><a href=".$link.">$date</a></td>";
1952                 $completed_data.= "<td align=left>$int_notes</td>";
1953                 $completed_data.= '</tr>';
1954                 $tr_color2 = $this->nextmatchs->alternate_row_color($tr_color2);
1955                 $this->t->set_var('tr_color2',$tr_color2);
1956                 $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1957                 $this->t->set_var('tr_color',$tr_color);
1958                 continue;
1959               }
1960
1961               // If this companionship has had a hometeaching interview this quarter, don't show them on the schedule list
1962               $sql = "SELECT * FROM tc_interview WHERE date >= '$quarter_start' AND date < '$quarter_end' AND indiv='$id'";
1963               $this->db2->query($sql,__LINE__,__FILE__);
1964
1965               if(!$this->db2->next_record()) {
1966                 $sql = "SELECT * FROM tc_interview WHERE indiv='$id' ORDER BY date DESC";
1967                 $this->db3->query($sql,__LINE__,__FILE__);
1968                 if($this->db3->next_record()) { $date = $this->db3->f('date'); } else { $date = ""; }
1969                 $link_data['menuaction'] = 'tc.tc.int_update';
1970                 $link_data['indiv'] = $id;
1971                 $link_data['name'] = $name;
1972                 $link_data['interview'] = '';
1973                 $link_data['action'] = 'add';
1974                 $link_data['interviewer'] = $districts[$d]['supervisor'];
1975                 $link = $GLOBALS['phpgw']->link('/tc/index.php',$link_data);
1976                 $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
1977                 $table_data.= "<td align=center>$phone</td>";
1978                 $table_data.= "<td align=center>";
1979                 $table_data.= '<select name=int_notes['.$i.'][pri]>';
1980                 foreach(range(0,6) as $num) {
1981                   if($num == 0) { $num = 1; } else {$num = $num*5; }
1982                   if($int_pri == $num) { $selected[$num] = 'selected="selected"'; } else { $selected[$num] = ''; }
1983                   $table_data.= '<option value='.$num.' '.$selected[$num].'>'.$num.'</option>';
1984                 }
1985                   $table_data.= '</select></td>';
1986                 $table_data.= "<td align=center>$date</td>";
1987                 $table_data.= '<td><input type=text size="50" maxlength="128" name="int_notes['.$i.'][notes]" value="'.$int_notes.'">';
1988                 $table_data.= '<input type=hidden name="int_notes['.$i.'][indiv_id]" value="'.$id.'">';
1989                 $table_data.= '<input type=hidden name="int_notes['.$i.'][indiv_name]" value="'.$name.'">';
1990                 $table_data.= '</td>';
1991                 $table_data.= '</tr>'."\n";
1992                 $i++;
1993               } else {
1994                 $link_data['menuaction'] = 'tc.tc.int_update';
1995                 $link_data['interviewer'] = $this->db2->f('interviewer');
1996                 $link_data['indiv'] = $this->db2->f('indiv');
1997                 $link_data['name'] = $name;
1998                 $link_data['interview'] = $this->db2->f('interview');
1999                 $link_data['action'] = 'view';
2000                 $link = $GLOBALS['phpgw']->link('/tc/index.php',$link_data);
2001                 $comps_with_quarterly_int++;
2002                 $int_completed=1;
2003                 $date = $this->db2->f('date');
2004                 $int_notes = $this->db2->f('notes');
2005                 if(strlen($int_notes) > 40) { $int_notes = substr($int_notes,0,40) . "..."; }
2006                 $completed_data.= "<tr bgcolor=". $this->t->get_var('tr_color2') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
2007                 $completed_data.= "<td align=center>$phone</td>";
2008                 $completed_data.= "<td align=center><a href=".$link.">$date</a></td>";
2009                 $completed_data.= "<td align=left>$int_notes</td>";
2010                 $completed_data.= '</tr>';
2011               }
2012             }
2013       }
2014
2015       $name_width=175; $phone_width=100; $date_width=100; $notes_width=300;
2016       $completed_table_width=$name_width + $phone_width + $date_width + $notes_width;
2017       $completed_header_row = "<th width=$name_width><font size=-2>Individual Name</th>";
2018       $completed_header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
2019       $completed_header_row.= "<th width=$date_width><font size=-2>Date</th>";
2020       $completed_header_row.= "<th width=$notes_width><font size=-2>Interview Notes</th>";
2021
2022       $this->t->set_var('table_width',$table_width);
2023       $this->t->set_var('header_row',$header_row);
2024       $this->t->set_var('table_data',$table_data);
2025       $this->t->set_var('completed_header_row',$completed_header_row);
2026       $this->t->set_var('completed_table_width',$completed_table_width);
2027       $this->t->set_var('completed',$completed_data);
2028       $this->t->fp('indivlist','indiv_list',True);
2029
2030       } // End for each district loop
2031
2032
2033       $indivs_width=300; $totals_width=100;
2034       $totals_table_width=$indivs_width + $totals_width;
2035       $totals_header_row = "<th width=$indivs_width><font size=-2>Individuals</th>";
2036       $totals_header_row.= "<th width=$totals_width><font size=-2>$year</th>";
2037       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
2038       $totals_data.= "<td align=left><font size=-2><b>Total Companionships with interviews completed:</b></font></td>";
2039       $totals_data.= "<td align=center><font size=-2><b>$comps_with_quarterly_int / $total_comps</b></font></td>";
2040       $percent = ceil(($comps_with_quarterly_int / $total_comps)*100);
2041       $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
2042       $this->t->set_var('tr_color',$tr_color);
2043       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
2044       $totals_data.= "<td align=left><font size=-2><b>Percentage:</b></font></td>";
2045       $totals_data.= "<td align=center><font size=-2><b>$percent%</b></font></td>";
2046       $totals_data.= "</tr>";
2047
2048       $this->t->set_var('totals',$totals_data);
2049       $this->t->set_var('totals_header_row',$totals_header_row);
2050       $this->t->set_var('totals_table_width',$totals_table_width);
2051
2052       $this->t->pfp('out','int_sched_t');
2053       $this->save_sessiondata();
2054
2055     }
2056
2057   function vis_sched()
2058     {
2059       $this->t->set_file(array('vis_sched_t' => 'vis_sched.tpl'));
2060       $this->t->set_block('vis_sched_t','family_list','familylist');
2061       $this->t->set_block('vis_sched_t','appt_list','apptlist');
2062       $action = get_var('action',array('GET','POST'));
2063
2064       $this->t->set_var('lang_save','Save Appt / Pri / Notes');
2065       $this->t->set_var('lang_reset','Clear Changes');
2066
2067       $this->t->set_var('vis_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_view'));
2068       $this->t->set_var('vis_link_title','View Yearly Visits');
2069
2070       $this->t->set_var('schedule_vis_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_sched'));
2071       $this->t->set_var('schedule_vis_link_title','Schedule Yearly Visits');
2072
2073       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_sched&action=save'));
2074       $this->t->set_var('title','Presidency Yearly Visit Scheduler');
2075
2076       $family_width=500; $phone_width=40; $pri_width=10; $notes_width=128; $visit_date_width=20;
2077       $table_width=$family_width + $phone_width + $pri_width + $notes_width + $visit_date_width;
2078       $header_row = "<th width=$family_width><font size=-2>Family Name</th>";
2079       $header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
2080       $header_row.= "<th width=$pri_width><font size=-2>Priority</th>";
2081       $header_row.= "<th width=$visit_date_width><font size=-2>Last Visit</th>";
2082       $header_row.= "<th width=$notes_width><font size=-2>Scheduling Notes</th>";
2083       $table_data=""; $completed_data=""; $totals_data="";
2084
2085       $year = date('Y');
2086
2087       // create the family id -> family name mapping
2088       $sql = "SELECT * FROM tc_family where valid=1 and indiv_id != 0 ORDER BY name ASC";
2089       $this->db->query($sql,__LINE__,__FILE__);
2090       $i=0;
2091       $family_id = NULL;
2092       while ($this->db->next_record())
2093         {
2094           $family_id[$i] = $this->db->f('family');
2095           $family_name[$i] = $this->db->f('name');
2096           $familyid2name[$family_id[$i]] = $family_name[$i];
2097           $sql = "SELECT * FROM tc_indiv where family='$family_id[$i]'";
2098           $this->db2->query($sql,__LINE__,__FILE__);
2099           if($this->db2->next_record()) {
2100             $familyid2address[$family_id[$i]] = $this->db2->f('address');
2101           }
2102           $i++;
2103         }
2104       array_multisort($family_name, $family_id);
2105
2106       if($action == 'save')
2107         {
2108           // Save any changes made to the appointment table
2109           $new_data = get_var('appt_notes',array('POST'));
2110           if($new_data != "") {
2111             foreach ($new_data as $entry)
2112               {
2113                 $family = $entry['family'];
2114                 $appointment = $entry['appointment'];
2115                 $location = $entry['location'];
2116                 if($location == "") {
2117                   $family_name_array = explode(",", $familyid2name[$family]);
2118                   $family_last_name = $family_name_array[0];
2119                   $family_address = $familyid2address[$family];
2120                   $location = "$family_last_name"." home ($family_address)";
2121                 }
2122                 if($family == 0) { $location = ""; }
2123
2124                 //Only perform a database update if we have made a change to this appointment
2125                 $sql = "SELECT * FROM tc_appointment where appointment='$appointment' and family='$family' and location='$location'";
2126                 $this->db->query($sql,__LINE__,__FILE__);
2127                 if(!$this->db->next_record()) {
2128                   // Perform database save actions here
2129                   $this->db->query("UPDATE tc_appointment set " .
2130                                    " family='" . $family . "'" .
2131                                    ",location='" . $location . "'" .
2132                                    " WHERE appointment=" . $appointment,__LINE__,__FILE__);
2133
2134                   // Email the appointment
2135                   $this->email_appt($appointment);
2136                 }
2137               }
2138           }
2139
2140           // Save any changes made to the visit notes table
2141           $new_data = get_var('vis_notes',array('POST'));
2142           foreach ($new_data as $entry)
2143            {
2144              $visit_notes = $entry['notes'];
2145              $family = $entry['family_id'];
2146              $visit_pri = $entry['pri'];
2147
2148              // Perform database save actions here
2149              $this->db->query("UPDATE tc_family set " .
2150                               " visit_notes='" . $visit_notes . "'" .
2151                               ",visit_pri='" . $visit_pri . "'" .
2152                               " WHERE family=" . $family,__LINE__,__FILE__);
2153
2154            }
2155
2156           $take_me_to_url = $GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_sched');
2157           //Header('Location: ' . $take_me_to_url);
2158         }
2159
2160       // APPOINTMENT TABLE
2161       $date_width=250; $time_width=100; $family_width=250; $location_width=100;
2162       $appt_table_width=$date_width + $time_width + $family_width + $location_width;
2163       $appt_header_row = "<th width=$date_width><font size=-2>Date</th>";
2164       $appt_header_row.= "<th width=$time_width><font size=-2>Time</th>";
2165       $appt_header_row.= "<th width=$family_width><font size=-2>Family</th>";
2166       $appt_header_row.= "<th width=$location_width><font size=-2>Location</th>";
2167       $appt_table_data = "";
2168
2169       // Find out what the EQ Presidency ID is
2170       $sql = "SELECT * FROM tc_presidency where eqpres=1 and valid=1";
2171       $this->db->query($sql,__LINE__,__FILE__);
2172       if($this->db->next_record()) {
2173         $presidency_name = $this->db->f('name');
2174         $presidency_id = $this->db->f('presidency');
2175       } else {
2176         print "<hr><font color=red><h3>-E- Unable to locate Presidency in tc_presidency table</h3></font></hr>";
2177         return;
2178       }
2179
2180       // query the database for all the appointments
2181       $sql = "SELECT * FROM tc_appointment where presidency=$presidency_id and date>=CURDATE() ORDER BY date ASC, time ASC";
2182       $this->db->query($sql,__LINE__,__FILE__);
2183
2184       while ($this->db->next_record())
2185         {
2186           $appointment = $this->db->f('appointment');
2187           $family = $this->db->f('family');
2188           $location = $this->db->f('location');
2189           $family_name_array = explode(",", $familyid2name[$family]);
2190           $family_last_name = $family_name_array[0];
2191           $family_address = $familyid2address[$family];
2192           if(($location == "") && ($family > 0)) { $location = "$family_last_name"." home ($family_address)"; }
2193
2194           $date = $this->db->f('date');
2195           $date_array = explode("-",$date);
2196           $year = $date_array[0]; $month = $date_array[1]; $day = $date_array[2];
2197           $day_string = date("l d-M-Y", mktime(0,0,0,$month,$day,$year));
2198
2199           $time = $this->db->f('time');
2200           $time_array = explode(":",$time);
2201           $time_string = date("g:i a", mktime($time_array[0], $time_array[1], $time_array[2]));
2202
2203           $appt_table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
2204           $appt_table_data.= "<td align=center>$day_string</td>";
2205           $appt_table_data.= "<td align=center>$time_string</td>";
2206
2207           $appt_table_data.= '<td align=center><select name=appt_notes['.$appointment.'][family]>';
2208           $appt_table_data.= '<option value=0></option>';
2209           for ($i=0; $i < count($family_id); $i++) {
2210             $id = $family_id[$i];
2211             $name = $family_name[$i];
2212             if($family_id[$i] == $family) { $selected[$id] = 'selected="selected"'; } else { $selected[$id] = ''; }
2213             $appt_table_data.= '<option value='.$id.' '.$selected[$id].'>'.$name.' Family</option>';
2214           }
2215           $appt_table_data.='</select></td>';
2216
2217           $appt_table_data.= '<td align=center><input type=text size="35" maxlength="120" ';
2218           $appt_table_data.= 'name="appt_notes['.$appointment.'][location]" value="'.$location.'">';
2219
2220           $appt_table_data.= '<input type=hidden name="appt_notes['.$appointment.'][appointment]" value="'.$appointment.'">';
2221
2222           $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
2223           $this->t->set_var('tr_color',$tr_color);
2224         }
2225
2226       $this->t->set_var('appt_table_data',$appt_table_data);
2227       $this->t->set_var('appt_header_row',$appt_header_row);
2228       $this->t->set_var('appt_table_width',$appt_table_width);
2229
2230
2231       // VISIT SCHEDULING TABLE
2232       $sql = "SELECT * FROM tc_family where valid=1 and indiv_id != 0 ORDER BY visit_pri ASC";
2233       $this->db->query($sql,__LINE__,__FILE__);
2234
2235       $total_families=0; $families_with_yearly_visit=0;
2236
2237       $i=0;
2238       $family_id = NULL;
2239       $family_name = NULL;
2240       $family_phone = NULL;
2241       $family_visit_pri = NULL;
2242       $family_visit_notes = NULL;
2243       while ($this->db->next_record())
2244         {
2245           $family_id[$i] = $this->db->f('family');
2246           $family_name[$i] = $this->db->f('name');
2247           $family_phone[$family_id[$i]] = $family_id[$i] . " ERROR";
2248           $family_visit_pri[$family_id[$i]] = $this->db->f('visit_pri');
2249           $family_visit_notes[$family_id[$i]] = $this->db->f('visit_notes');
2250           $i++;
2251           $total_families++;
2252         }
2253
2254       $sql = "SELECT * FROM tc_indiv where valid=1";
2255       $this->db->query($sql,__LINE__,__FILE__);
2256       while ($this->db->next_record())
2257         {
2258           $family = $this->db->f('family');
2259           $phone = $this->db->f('phone');
2260           $family_phone[$family] = $phone;
2261         }
2262
2263       $max = count($family_id);
2264
2265       for($i=0; $i < $max; $i++) {
2266           $id = $family_id[$i];
2267           $name = $family_name[$i];
2268           $phone = $family_phone[$id];
2269           $vis_pri = $family_visit_pri[$id];
2270           $vis_notes = $family_visit_notes[$id];
2271
2272           // If this family has had a yearly visit this year, don't show them on the schedule list
2273           $year_start = $year - 1 . "-12-31"; $year_end = $year + 1 . "-01-01";
2274           $sql = "SELECT * FROM tc_visit WHERE date > '$year_start' AND date < '$year_end' ".
2275              "AND family=" . $id . " AND companionship=0";
2276           $this->db2->query($sql,__LINE__,__FILE__);
2277
2278           if(!$this->db2->next_record()) {
2279             $sql = "SELECT * FROM tc_visit WHERE family=" . $id . " AND companionship=0 ORDER BY date DESC";
2280             $this->db->query($sql,__LINE__,__FILE__);
2281             if($this->db->next_record()) { $date = $this->db->f('date'); } else { $date = ""; }
2282             $link_data['menuaction'] = 'tc.tc.vis_update';
2283             $link_data['visit'] = '';
2284             $link_data['family'] = $id;
2285             $link_data['name'] = $name;
2286             $link_data['action'] = 'add';
2287             $link = $GLOBALS['phpgw']->link('/tc/index.php',$link_data);
2288             $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') ."><td title=\"$phone\"><a href=$link>$name Family</a></td>";
2289             $table_data.= "<td align=center>$phone</td>";
2290             $table_data.= "<td align=center>";
2291             $table_data.= '<select name=vis_notes['.$i.'][pri]>';
2292             foreach(range(0,6) as $num) {
2293               if($num == 0) { $num = 1; } else {$num = $num*5; }
2294               if($vis_pri == $num) { $selected[$num] = 'selected="selected"'; } else { $selected[$num] = ''; }
2295               $table_data.= '<option value='.$num.' '.$selected[$num].'>'.$num.'</option>';
2296             }
2297             $table_data.= '</select></td>';
2298             $table_data.= "<td align=center>$date</td>";
2299             $table_data.= '<td><input type=text size="50" maxlength="128" name="vis_notes['.$i.'][notes]" value="'.$vis_notes.'">';
2300             $table_data.= '<input type=hidden name="vis_notes['.$i.'][family_id]" value="'.$id.'">';
2301             $table_data.= '<input type=hidden name="vis_notes['.$i.'][family_name]" value="'.$name.'">';
2302             $table_data.= '</td>';
2303             $table_data.= '</tr>';
2304             $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
2305             $this->t->set_var('tr_color',$tr_color);
2306           } else {
2307             $link_data['menuaction'] = 'tc.tc.vis_update';
2308             $link_data['visit'] = $this->db2->f('visit');
2309             $link_data['family'] = $this->db2->f('family');
2310             $link_data['name'] = $name;
2311             $link_data['date'] = $this->db2->f('date');
2312             $link_data['action'] = 'view';
2313             $link = $GLOBALS['phpgw']->link('/tc/index.php',$link_data);
2314             $families_with_yearly_visit++;
2315             $date = $this->db2->f('date');
2316             $vis_notes = $this->db2->f('notes');
2317             if(strlen($vis_notes) > 40) { $vis_notes = stripslashes(substr($vis_notes,0,40) . "..."); }
2318             $completed_data.= "<tr bgcolor=". $this->t->get_var('tr_color2') ."><td title=\"$phone\"><a href=$link>$name Family</a></td>";
2319             $completed_data.= "<td align=center>$phone</td>";
2320             $completed_data.= "<td align=center><a href=".$link.">$date</a></td>";
2321             $completed_data.= "<td align=left>$vis_notes</td>";
2322             $completed_data.= '</tr>';
2323             $tr_color2 = $this->nextmatchs->alternate_row_color($tr_color2);
2324             $this->t->set_var('tr_color2',$tr_color2);
2325           }
2326       }
2327
2328       $name_width=190; $phone_width=100; $date_width=100; $notes_width=300;
2329       $completed_table_width=$name_width + $phone_width + $date_width + $notes_width;
2330       $completed_header_row = "<th width=$name_width><font size=-2>Family Name</th>";
2331       $completed_header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
2332       $completed_header_row.= "<th width=$date_width><font size=-2>Date</th>";
2333       $completed_header_row.= "<th width=$notes_width><font size=-2>Visit Notes</th>";
2334
2335       $family_width=300; $totals_width=100;
2336       $totals_table_width=$family_width + $totals_width;
2337       $totals_header_row = "<th width=$family_width><font size=-2>Families</th>";
2338       $totals_header_row.= "<th width=$totals_width><font size=-2>$year</th>";
2339       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
2340       $totals_data.= "<td align=left><font size=-2><b>Total Families with yearly Visits completed:</b></font></td>";
2341       $totals_data.= "<td align=center><font size=-2><b>$families_with_yearly_visit / $total_families</b></font></td>";
2342       $percent = ceil(($families_with_yearly_visit / $total_families)*100);
2343       $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
2344       $this->t->set_var('tr_color',$tr_color);
2345       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
2346       $totals_data.= "<td align=left><font size=-2><b>Percentage:</b></font></td>";
2347       $totals_data.= "<td align=center><font size=-2><b>$percent%</b></font></td>";
2348       $totals_data.= "</tr>";
2349
2350       $this->t->set_var('table_width',$table_width);
2351       $this->t->set_var('header_row',$header_row);
2352       $this->t->set_var('table_data',$table_data);
2353       $this->t->set_var('totals_header_row',$totals_header_row);
2354       $this->t->set_var('totals_table_width',$totals_table_width);
2355       $this->t->set_var('completed_header_row',$completed_header_row);
2356       $this->t->set_var('completed_table_width',$completed_table_width);
2357       $this->t->set_var('completed',$completed_data);
2358       $this->t->set_var('totals',$totals_data);
2359       $this->t->fp('familylist','family_list',True);
2360       $this->t->fp('apptlist','appt_list',True);
2361
2362       $this->t->pfp('out','vis_sched_t');
2363       $this->save_sessiondata();
2364
2365     }
2366
2367   function ppi_view()
2368     {
2369       $this->t->set_file(array('ppi_view_t' => 'ppi_view.tpl'));
2370       $this->t->set_block('ppi_view_t','district_list','list');
2371
2372       $this->t->set_var('linkurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_view'));
2373       $num_months = get_var('num_months',array('GET','POST'));
2374       if($num_months == '') { $num_months = $this->default_ppi_num_months; }
2375       $this->t->set_var('num_months',$num_months);
2376       if($num_months == 1) { $this->t->set_var('lang_num_months','Month of History'); }
2377       else {  $this->t->set_var('lang_num_months','Months of History'); }
2378       $this->t->set_var('lang_filter','Filter');
2379       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_view'));
2380
2381       $this->t->set_var('ppi_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_view'));
2382       $this->t->set_var('ppi_link_title','Yearly PPIs');
2383
2384       $this->t->set_var('schedule_ppi_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_sched'));
2385       $this->t->set_var('schedule_ppi_link_title','Schedule Yearly PPIs');
2386
2387       $this->t->set_var('title','Yearly PPIs');
2388       $num_months = get_var('num_months',array('GET','POST'));
2389       if($num_months == '') { $num_months = $this->default_ppi_num_years; }
2390       $this->t->set_var('num_months',$num_months);
2391       if($num_months == 1) { $this->t->set_var('lang_num_months','Year of History'); }
2392       else { $this->t->set_var('lang_num_months','Years of History'); }
2393
2394       $sql = "SELECT * FROM tc_presidency where president=1 and valid=1";
2395       $this->db->query($sql,__LINE__,__FILE__);
2396       if($this->db->next_record()) {
2397         $president_name = $this->db->f('name');
2398         $interviewer = $this->db->f('indiv');
2399         $interview_type = 'ppi';
2400       } else {
2401         print "<hr><font color=red><h3>-E- Unable to locate President in tc_presidency table</h3></font></hr>";
2402         return;
2403       }
2404       $this->t->set_var('district_number','*');
2405       $this->t->set_var('district_name',$president_name);
2406
2407       // TODO:  changed this so it picks the quorum dynamically
2408       $sql = "SELECT * FROM tc_indiv where steward='Elder' and valid=1 ORDER BY indiv ASC";
2409       $this->db->query($sql,__LINE__,__FILE__);
2410       $i=0;
2411       while ($this->db->next_record())
2412         {
2413           $indiv_id[$i] = $this->db->f('indiv');
2414           $indiv_name[$i] = $this->db->f('name');
2415           $indiv_phone[$indiv_id[$i]] = $this->db->f('phone');
2416           $indiv_ppi_pri[$indiv_id[$i]] = $this->db->f('ppi_pri');
2417           $indiv_ppi_notes[$indiv_id[$i]] = $this->db->f('ppi_notes');
2418           $i++;
2419         }
2420       $total_indivs=$i;
2421       array_multisort($indiv_name, $indiv_id);
2422       //var_dump($indiv_name); print "<br><br>"; var_dump($indiv_id);
2423
2424       $header_row="<th width=$comp_width><font size=-2>Individual Name</th>";
2425
2426       $indiv_width=400; $ppi_width=75; $table_width=$indiv_width + $num_months*$ppi_width;
2427       $table_data="";
2428       for($m=$num_months; $m >= 0; $m--) {
2429         $year = date('Y') - $m;
2430         $header_row .= "<th width=150><font size=-2>$year</th>";
2431         $ppis[$m] = 0;
2432       }
2433
2434       for ($j=0; $j < count($indiv_id); $j++) {
2435         $id = $indiv_id[$j];
2436         $name = $indiv_name[$j];
2437         $phone = $indiv_phone[$id];
2438
2439         $link_data['menuaction'] = 'tc.tc.ppi_update';
2440         $link_data['interviewer'] = $interviewer;
2441         $link_data['indiv'] = $id;
2442         $link_data['name'] = $name;
2443         $link_data['interview'] = '';
2444         $link_data['interview_type'] = $interview_type;
2445         $link_data['action'] = 'add';
2446         $link = $GLOBALS['phpgw']->link('/tc/index.php',$link_data);
2447         $this->nextmatchs->template_alternate_row_color(&$this->t);
2448         $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
2449
2450         // Find out how many times PPIs were performed in the past $num_months for this individual
2451         for($m=$num_months; $m >= 0; $m--) {
2452           $year = date('Y') - $m;
2453           $year_start = $year - 1 . "-12-31"; $year_end = $year + 1 . "-01-01";
2454           $sql = "SELECT * FROM tc_interview WHERE date > '$year_start' AND date < '$year_end' ".
2455              "AND indiv=" . $id . " AND interview_type='ppi'";
2456           $this->db2->query($sql,__LINE__,__FILE__);
2457
2458           if(!$total_ppis[$m]) { $total_ppis[$m] = 0; }
2459           if($this->db2->next_record()) {
2460             $ppis[$m]++; $total_ppis[$m]++; $ppi_recorded[$companionship][$m]=1;
2461             $link_data['menuaction'] = 'tc.tc.ppi_update';
2462             $link_data['companionship'] = $companionship;
2463             $link_data['interviewer'] = $this->db2->f('interviewer');
2464             $link_data['indiv'] = $id;
2465             $link_data['name'] = $name;
2466             $link_data['interview'] = $this->db2->f('interview');
2467             $link_data['interview_type'] = $interview_type;
2468             $link_data['action'] = 'view';
2469             $date = $this->db2->f('date');
2470             $date_array = explode("-",$date);
2471             $month = $date_array[1];
2472             $day   = $date_array[2];
2473             $link = $GLOBALS['phpgw']->link('/tc/index.php',$link_data);
2474             $table_data .= '<td align=center><a href='.$link.'><img src="images/checkmark.gif">&nbsp;'.$month.'-'.$day.'</a></td>';
2475           }
2476           else { $table_data .= "<td>&nbsp;</td>"; }
2477         }
2478         $table_data .= "</tr>\n";
2479       }
2480       $table_data .= "<tr><td colspan=20><hr></td></tr>";
2481
2482       $stat_data = "<tr><td><b><font size=-2>$total_indivs Individuals<br>PPI Totals:</font></b></td>";
2483       for($m=$num_months; $m >=0; $m--) {
2484         $percent = ceil(($ppis[$m] / $total_indivs)*100);
2485         $stat_data .= "<td align=center><font size=-2><b>$ppis[$m]<br>$percent%</font></b></td>";
2486       }
2487       $stat_data .= "</tr>";
2488
2489       $this->t->set_var('table_width',$table_width);
2490       $this->t->set_var('header_row',$header_row);
2491       $this->t->set_var('table_data',$table_data);
2492       $this->t->set_var('stat_data',$stat_data);
2493       $this->t->pfp('out','ppi_view_t');
2494       $this->save_sessiondata();
2495     }
2496
2497   function ppi_update()
2498     {
2499       $this->t->set_file(array('form' => 'ppi_update.tpl'));
2500       $this->t->set_block('form','interviewer_list','int_list');
2501       $this->t->set_block('form','add','addhandle');
2502       $this->t->set_block('form','edit','edithandle');
2503
2504       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_view'));
2505       $this->t->set_var('readonly','');
2506       $this->t->set_var('disabled','');
2507
2508       $action = get_var('action',array('GET','POST'));
2509       $companionship = get_var('companionship',array('GET','POST'));
2510       $interviewer = get_var('interviewer',array('GET','POST'));
2511       $name = get_var('name',array('GET','POST'));
2512       $interview = get_var('interview',array('GET','POST'));
2513       $indiv = get_var('indiv',array('GET','POST'));
2514       $date = get_var('date',array('GET','POST'));
2515       $notes = get_var('notes',array('GET','POST'));
2516       $interview_type = get_var('interview_type',array('GET','POST'));
2517
2518       $sql = "SELECT * FROM tc_presidency where valid=1 and (president=1 or counselor=1 or secretary=1)";
2519       $this->db2->query($sql,__LINE__,__FILE__);
2520       while ($this->db2->next_record())
2521       {
2522         $indiv = $this->db2->f('indiv');
2523         $interviewer_name = $this->db2->f('name');
2524         if($indiv == $interviewer) {
2525           $this->t->set_var('interviewer',$interviewer . ' selected');
2526         } else {
2527           $this->t->set_var('interviewer',$interviewer);
2528         }
2529         $this->t->set_var('interviewer_name',$interviewer_name);
2530         $this->t->set_var('interview_type_checked','');
2531         $this->t->fp('int_list','interviewer_list',True);
2532       }
2533
2534       if($action == 'save')
2535         {
2536           $notes = get_var('notes',array('POST'));
2537           $this->db->query("UPDATE tc_interview set " .
2538                      "   interview='" . $interview . "'" .
2539                     ", interviewer='" . $interviewer . "'" .
2540                           ", indiv='" . $indiv . "'" .
2541                            ", date='" . $date . "'" .
2542                           ", notes='" . $notes . "'" .
2543                  ", interview_type='" . $interview_type . "'" .
2544                            " WHERE interview=" . $interview,__LINE__,__FILE__);
2545           $this->ppi_view();
2546           return false;
2547         }
2548
2549       if($action == 'insert')
2550         {
2551           $notes = get_var('notes',array('POST'));
2552           $this->db->query("INSERT INTO tc_interview (interviewer,indiv,date,notes,interview_type) "
2553                            . "VALUES ('" . $interviewer . "','" . $indiv . "','"
2554                            . $date . "','" . $notes . "','" . $interview_type  ."')",__LINE__,__FILE__);
2555           $this->ppi_view();
2556           return false;
2557         }
2558
2559       if($action == 'add')
2560         {
2561           $this->t->set_var('cal_date',$this->jscal->input('date','','','','','','',$this->cal_options));
2562           $this->t->set_var('interview', '');
2563           $this->t->set_var('interviewer', $interviewer);
2564           $this->t->set_var('name',$name);
2565           $this->t->set_var('indiv',$indiv);
2566           $this->t->set_var('date','');
2567           $this->t->set_var('notes','');
2568           $this->t->set_var('interview_type',$interview_type);
2569           $this->t->set_var('interview_type_checked','checked');
2570           $this->t->set_var('lang_done','Cancel');
2571           $this->t->set_var('lang_action','Adding New PPI');
2572           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_update&interview='
2573                                                                 . $interview . '&action=' . 'insert'));
2574         }
2575
2576       if($action == 'edit' || $action == 'view')
2577         {
2578           $sql = "SELECT * FROM tc_interview WHERE interview=".$interview;
2579           $this->db->query($sql,__LINE__,__FILE__);
2580           $this->db->next_record();
2581           $this->t->set_var('interview',$interview);
2582           $this->t->set_var('name',$name);
2583           $this->t->set_var('interviewer', $this->db->f('interviewer'));
2584           $this->t->set_var('indiv',$this->db->f('indiv'));
2585           $this->t->set_var('date',$this->db->f('date'));
2586           $this->t->set_var('notes',$this->db->f('notes'));
2587           $this->t->set_var('interview_type',$this->db->f('interview_type'));
2588           if($this->db->f('interview_type') == 1) { $this->t->set_var('interview_type_checked','checked'); }
2589         }
2590
2591       if($action == 'edit')
2592         {
2593           $this->t->set_var('cal_date',$this->jscal->input('date',$date,'','','','','',$this->cal_options));
2594           $this->t->set_var('lang_done','Cancel');
2595           $this->t->set_var('lang_action','Editing PPI');
2596           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_update&interview='
2597                                                                 . $interview . '&action=' . 'save'));
2598         }
2599
2600       if($action == 'view')
2601         {
2602           $date = $this->db->f('date');
2603           $this->t->set_var('cal_date','<input type=text size="10" maxlength="10" name="date" value="'.$date.'" readonly>');
2604           $this->t->set_var('readonly','READONLY');
2605           $this->t->set_var('disabled','DISABLED');
2606           $this->t->set_var('lang_done','Done');
2607           $this->t->set_var('lang_action','Viewing PPI');
2608           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_update&interview='
2609                                                                 . $interview . '&action=' . 'edit'));
2610         }
2611
2612       $this->t->set_var('lang_reset','Clear Form');
2613       $this->t->set_var('lang_add','Add PPI');
2614       $this->t->set_var('lang_save','Save Changes');
2615       $this->t->set_var('edithandle','');
2616       $this->t->set_var('addhandle','');
2617
2618       $this->t->pfp('out','form');
2619
2620       if($action == 'view') { $this->t->set_var('lang_save','Edit PPI'); }
2621       if($action == 'edit' || $action == 'view') { $this->t->pfp('addhandle','edit'); }
2622       if($action == 'add') { $this->t->pfp('addhandle','add'); }
2623
2624       $this->save_sessiondata();
2625     }
2626
2627     function int_view()
2628     {
2629       $this->t->set_file(array('int_view_t' => 'int_view.tpl'));
2630       $this->t->set_block('int_view_t','district_list','list');
2631
2632       $this->t->set_var('linkurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_view'));
2633       $num_quarters = get_var('num_quarters',array('GET','POST'));
2634       if($num_quarters == '') { $num_quarters = $this->default_int_num_quarters; }
2635       $this->t->set_var('num_quarters',$num_quarters);
2636       if($num_quarters == 1) { $this->t->set_var('lang_num_quarters','Quarter of History'); }
2637       else {  $this->t->set_var('lang_num_quarters','Quarters of History'); }
2638       $this->t->set_var('lang_filter','Filter');
2639
2640       $this->t->set_var('int_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_view'));
2641       $this->t->set_var('int_link_title','Hometeaching Interviews');
2642
2643       $this->t->set_var('schedule_int_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_sched'));
2644       $this->t->set_var('schedule_int_link_title','Schedule Hometeaching Interviews');
2645
2646       $this->t->set_var('title','Hometeaching Interviews');
2647
2648       $num_months = $num_quarters * 3 - 1;
2649       $current_month = $this->current_month;
2650       if($current_month >= 1 && $current_month <= 3) { $current_month=3; }
2651       else if($current_month >= 4 && $current_month <= 6) { $current_month=6; }
2652       else if($current_month >= 7 && $current_month <= 9) { $current_month=9; }
2653       else if($current_month >= 10 && $current_month <= 12) { $current_month=12; }
2654
2655       $sql = "SELECT * FROM tc_district where valid=1 ORDER BY district ASC";
2656       $this->db->query($sql,__LINE__,__FILE__);
2657       $i=0;
2658       while ($this->db->next_record())
2659         {
2660           $districts[$i]['district'] = $this->db->f('district');
2661           $districts[$i]['name'] = $this->db->f('name');
2662           $districts[$i]['supervisor'] = $this->db->f('supervisor');
2663           $i++;
2664         }
2665
2666       $sql = "SELECT * FROM tc_indiv where valid=1 ORDER BY indiv ASC";
2667       $this->db->query($sql,__LINE__,__FILE__);
2668       $i=0;
2669       while ($this->db->next_record())
2670         {
2671           $indiv_id[$i] = $this->db->f('indiv');
2672           $indiv_name[$i] = $this->db->f('name');
2673           $indiv_phone[$indiv_id[$i]] = $this->db->f('phone');
2674           $i++;
2675         }
2676       array_multisort($indiv_name, $indiv_id);
2677       for($i=0; $i < count($indiv_id); $i++) {
2678           $id = $indiv_id[$i];
2679           $indivs[$id] = $indiv_name[$i];
2680       }
2681
2682       $total_companionships = 0;
2683       $this->nextmatchs->template_alternate_row_color(&$this->t);
2684       for ($i=0; $i < count($districts); $i++) {
2685         $this->t->set_var('district_number',$districts[$i]['district']);
2686         $this->t->set_var('district_name',$districts[$i]['name']);
2687         $supervisor = $districts[$i]['supervisor'];
2688
2689         // Select all the unique companionship numbers for this district
2690         $sql = "SELECT distinct companionship FROM tc_companionship where valid=1 and district=". $districts[$i]['district'];
2691         $this->db->query($sql,__LINE__,__FILE__);
2692         $j=0; $unique_companionships = '';
2693         while ($this->db->next_record())
2694           {
2695             $unique_companionships[$j]['companionship'] = $this->db->f('companionship');
2696             $j++;
2697           }
2698
2699         $comp_width=250; $int_width=75; $table_width=$comp_width + $num_months*$int_width;
2700         $table_data=""; $num_companionships = $j; $num_indivs = 0;
2701         for($m=$num_months; $m >= 0; $m--) { $ints[$m] = 0; }
2702         for ($j=0; $j < count($unique_companionships); $j++) {
2703           // Select all the companions in each companionship
2704           $sql = "SELECT * FROM tc_companionship where valid=1 and ".
2705              "companionship=". $unique_companionships[$j]['companionship'];
2706           $this->db->query($sql,__LINE__,__FILE__);
2707           $k=0;
2708           $comp = $unique_companionships[$j]['companionship'];
2709           for($m=$num_months; $m >= 0; $m--) { $int_recorded[$comp][$m] = 0; }
2710           while ($this->db->next_record())
2711             {
2712               // Get this companions information
2713               $num_indivs++;
2714               $companionship = $this->db->f('companionship');
2715               $indiv_id = $this->db->f('indiv');
2716               $name = $indivs[$indiv_id];
2717               $phone = $indiv_phone[$indiv_id];
2718               $link_data['menuaction'] = 'tc.tc.int_update';
2719               $link_data['companionship'] = $companionship;
2720               $link_data['interviewer'] = $supervisor;
2721               $link_data['indiv'] = $indiv_id;
2722               $link_data['name'] = $name;
2723               $link_data['interview'] = '';
2724               $link_data['action'] = 'add';
2725               $link = $GLOBALS['phpgw']->link('/tc/index.php',$link_data);
2726               $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
2727
2728               // Find out how many times Interviews were performed in the past $num_months for this individual
2729               $header_row="<th width=$comp_width><font size=-2>Companionship</th>";
2730               for($m=$num_months; $m >= 0; $m--) {
2731                 $month = $current_month - $m;
2732                 $year = $this->current_year;
2733                 if($month <= 0) { $remainder = $month; $month = 12 + $remainder; $year=$year-1; }
2734                 if($month < 10) { $month = "0"."$month"; }
2735                 $month_start = "$year"."-"."$month"."-"."01";
2736                 $month_end = "$year"."-"."$month"."-"."31";
2737                 $month = "$month"."/"."$year";
2738                 $sql = "SELECT * FROM tc_interview WHERE date >= '$month_start' AND date <= '$month_end' ".
2739                    "AND indiv=" . $indiv_id;
2740                 $this->db2->query($sql,__LINE__,__FILE__);
2741                 $header_row .= "<th width=$int_width><font size=-2>$month</th>";
2742
2743                 if(!$total_ints[$m]) { $total_ints[$m] = 0; }
2744                 if($this->db2->next_record()) {
2745                   if(!$int_recorded[$companionship][$m]) {
2746                     $ints[$m]++; $total_ints[$m]++; $int_recorded[$companionship][$m]=1;
2747                   }
2748                   $link_data['menuaction'] = 'tc.tc.int_update';
2749                   $link_data['companionship'] = $companionship;
2750                   $link_data['interviewer'] = $this->db2->f('interviewer');
2751                   $link_data['indiv'] = $indiv_id;
2752                   $link_data['name'] = $name;
2753                   $link_data['interview'] = $this->db2->f('interview');
2754                   $link_data['action'] = 'view';
2755                   $date = $this->db2->f('date');
2756                   $date_array = explode("-",$date);
2757                   $month = $date_array[1];
2758                   $day   = $date_array[2];
2759                   $link = $GLOBALS['phpgw']->link('/tc/index.php',$link_data);
2760                   $table_data .= '<td align=center><a href='.$link.'><img src="images/checkmark.gif">&nbsp;'.$month.'-'.$day.'</a></td>';
2761                 }
2762                 else { $table_data .= "<td>&nbsp;</td>"; }
2763               }
2764               $table_data .= "</tr>";
2765               $k++;
2766             }
2767           $table_data .= "<tr><td colspan=20><hr></td></tr>";
2768         }
2769         $total_companionships += $num_companionships;
2770         $stat_data = "<tr><td><b><font size=-2>$num_companionships Companionships<br>Interview Totals:</font></b></td>";
2771
2772         // Print the hometeaching interview stats
2773         for($m=$num_months; $m >=0; $m--) {
2774           $month = $current_month - $m;
2775           if($month < 0) { $month = 12 + $month; } // Handle going backwards over a year boundary
2776           $month_begins = $month % $this->monthly_hometeaching_interview_stats;
2777           //print "$month % $this->monthly_hometeaching_interview_stats = $month_begins <br>";
2778           if($this->monthly_hometeaching_interview_stats == 1) { $month_begins = 1; }
2779           if(($month_begins) == 1) { $total = $ints[$m]; }
2780           else { $total += $ints[$m]; }
2781           $percent = ceil(($total / $num_companionships)*100);
2782           $stat_data .= "<td align=center><font size=-2><b>$total<br>$percent%</font></b></td>";
2783         }
2784         $stat_data .= "</tr>";
2785
2786         $this->t->set_var('table_width',$table_width);
2787         $this->t->set_var('header_row',$header_row);
2788         $this->t->set_var('table_data',$table_data);
2789         $this->t->set_var('stat_data',$stat_data);
2790         $this->t->fp('list','district_list',True);
2791       }
2792
2793       // Display the totals
2794       $total = 0;
2795       $totals = "<tr><td><b><font size=-2>$total_companionships Total Comps<br>Interview Totals:</font></b></td>";
2796       for($m=$num_months; $m >=0; $m--) {
2797         $month = $current_month - $m;
2798         if($month < 0) { $month = 12 + $month; } // Handle going backwards over a year boundary
2799         $month_begins = $month % $this->monthly_hometeaching_interview_stats;
2800         if($this->monthly_hometeaching_interview_stats == 1) { $month_begins = 1; }
2801         if(($month_begins) == 1) { $total = $total_ints[$m]; }
2802         else { $total += $total_ints[$m]; }
2803         $percent = ceil(($total / $total_companionships)*100);
2804         $totals .= "<td align=center><font size=-2><b>$total<br>$percent%</font></b></td>";
2805       }
2806       $totals .= "</tr>";
2807
2808       $this->t->set_var('totals',$totals);
2809       $this->t->pfp('out','int_view_t');
2810       $this->save_sessiondata();
2811     }
2812
2813   function int_update()
2814     {
2815       $this->t->set_file(array('form' => 'int_update.tpl'));
2816       $this->t->set_block('form','interviewer_list','int_list');
2817       $this->t->set_block('form','add','addhandle');
2818       $this->t->set_block('form','edit','edithandle');
2819
2820       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_view'));
2821       $this->t->set_var('readonly','');
2822       $this->t->set_var('disabled','');
2823       $this->t->set_var('interview_type_checked','');
2824
2825       $action = get_var('action',array('GET','POST'));
2826       $companionship = get_var('companionship',array('GET','POST'));
2827       $interviewer = get_var('interviewer',array('GET','POST'));
2828       $name = get_var('name',array('GET','POST'));
2829       $interview = get_var('interview',array('GET','POST'));
2830       $indiv = get_var('indiv',array('GET','POST'));
2831       $date = get_var('date',array('GET','POST'));
2832       $notes = get_var('notes',array('GET','POST'));
2833       $interview_type = get_var('interview_type',array('GET','POST'));
2834
2835       $sql = "SELECT * FROM tc_presidency where valid=1 and (president=1 or counselor=1 or secretary=1 or district!=0)";
2836       $this->db2->query($sql,__LINE__,__FILE__);
2837       while ($this->db2->next_record())
2838       {
2839         $indiv = $this->db2->f('indiv');
2840         $interviewer_name = $this->db2->f('name');
2841         if($indiv == $interviewer) {
2842           $this->t->set_var('interviewer',$interviewer . ' selected');
2843         } else {
2844           $this->t->set_var('interviewer',$interviewer);
2845         }
2846         $this->t->set_var('interviewer_name',$interviewer_name);
2847         $this->t->fp('int_list','interviewer_list',True);
2848       }
2849
2850       if($action == 'save')
2851         {
2852           $notes = get_var('notes',array('POST'));
2853           $this->db->query("UPDATE tc_interview set " .
2854                      "   interview='" . $interview . "'" .
2855                     ", interviewer='" . $interviewer . "'" .
2856                           ", indiv='" . $indiv . "'" .
2857                            ", date='" . $date . "'" .
2858                           ", notes='" . $notes . "'" .
2859                  ", interview_type='" . $interview_type . "'" .
2860                            " WHERE interview=" . $interview,__LINE__,__FILE__);
2861           $this->int_view();
2862           return false;
2863         }
2864
2865       if($action == 'insert')
2866         {
2867           $notes = get_var('notes',array('POST'));
2868           $this->db->query("INSERT INTO tc_interview (interviewer,indiv,date,notes,interview_type) "
2869                            . "VALUES ('" . $interviewer . "','" . $indiv . "','"
2870                            . $date . "','" . $notes ."','" . $interview_type . "')",__LINE__,__FILE__);
2871           $this->int_view();
2872           return false;
2873         }
2874
2875       if($action == 'add')
2876         {
2877           $this->t->set_var('cal_date',$this->jscal->input('date','','','','','','',$this->cal_options));
2878           $this->t->set_var('interview', '');
2879           $this->t->set_var('interviewer', $interviewer);
2880           $this->t->set_var('name',$name);
2881           $this->t->set_var('indiv',$indiv);
2882           $this->t->set_var('date','');
2883           $this->t->set_var('notes','');
2884           $this->t->set_var('lang_done','Cancel');
2885           $this->t->set_var('lang_action','Adding New Interview');
2886           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_update&interview='
2887                                                                 . $interview . '&action=' . 'insert'));
2888         }
2889
2890       if($action == 'edit' || $action == 'view')
2891         {
2892           $sql = "SELECT * FROM tc_interview WHERE interview=".$interview;
2893           $this->db->query($sql,__LINE__,__FILE__);
2894           $this->db->next_record();
2895           $this->t->set_var('interview',$interview);
2896           $this->t->set_var('name',$name);
2897           $this->t->set_var('interviewer', $this->db->f('interviewer'));
2898           $this->t->set_var('indiv',$this->db->f('indiv'));
2899           $this->t->set_var('date',$this->db->f('date'));
2900           $this->t->set_var('notes',$this->db->f('notes'));
2901           if($this->db->f('interview_type') == 1) { $this->t->set_var('interview_type_checked','checked'); }
2902         }
2903
2904       if($action == 'edit')
2905         {
2906           $this->t->set_var('cal_date',$this->jscal->input('date',$date,'','','','','',$this->cal_options));
2907           $this->t->set_var('lang_done','Cancel');
2908           $this->t->set_var('lang_action','Editing Interview');
2909           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_update&interview='
2910                                                                 . $interview . '&action=' . 'save'));
2911         }
2912
2913       if($action == 'view')
2914         {
2915           $date = $this->db->f('date');
2916           $this->t->set_var('cal_date','<input type=text size="10" maxlength="10" name="date" value="'.$date.'" readonly>');
2917           $this->t->set_var('readonly','READONLY');
2918           $this->t->set_var('disabled','DISABLED');
2919           $this->t->set_var('lang_done','Done');
2920           $this->t->set_var('lang_action','Viewing Interview');
2921           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_update&interview='
2922                                                                 . $interview . '&action=' . 'edit'));
2923         }
2924
2925       $this->t->set_var('lang_reset','Clear Form');
2926       $this->t->set_var('lang_add','Add Interview');
2927       $this->t->set_var('lang_save','Save Changes');
2928       $this->t->set_var('edithandle','');
2929       $this->t->set_var('addhandle','');
2930
2931       $this->t->pfp('out','form');
2932
2933       if($action == 'view') { $this->t->set_var('lang_save','Edit Interview'); }
2934       if($action == 'edit' || $action == 'view') { $this->t->pfp('addhandle','edit'); }
2935       if($action == 'add') { $this->t->pfp('addhandle','add'); }
2936
2937       $this->save_sessiondata();
2938     }
2939
2940   function vis_view()
2941     {
2942       $this->t->set_file(array('vis_view_t' => 'vis_view.tpl'));
2943       $this->t->set_block('vis_view_t','visit_list','list1');
2944       $this->t->set_block('vis_view_t','family_list','list2');
2945
2946       $this->t->set_var('lang_name','Family Name');
2947       $this->t->set_var('lang_date','Date');
2948
2949       $this->t->set_var('vis_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_view'));
2950       $this->t->set_var('vis_link_title','View Yearly Visits');
2951
2952       $this->t->set_var('schedule_vis_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_sched'));
2953       $this->t->set_var('schedule_vis_link_title','Schedule Yearly Visits');
2954
2955       $this->t->set_var('linkurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_view'));
2956       $num_years = get_var('num_years',array('GET','POST'));
2957       if($num_years == '') { $num_years = $this->default_vis_num_years; }
2958       $this->t->set_var('num_years',$num_years);
2959       if($num_years == 1) { $this->t->set_var('lang_num_years','Year of History'); }
2960       else {  $this->t->set_var('lang_num_years','Years of History'); }
2961       $this->t->set_var('lang_filter','Filter');
2962
2963       $year = date('Y') - $num_years + 1;
2964       $year_start = $year - 1 . "-12-31"; $year_end = $year + 1 . "-01-01";
2965
2966       $sql = "SELECT * FROM tc_visit WHERE companionship=0 and date > '$year_start' ORDER BY date DESC";
2967       $this->db->query($sql,__LINE__,__FILE__);
2968       $total_records = $this->db->num_rows();
2969
2970       $i = 0;
2971       while ($this->db->next_record())
2972         {
2973           $visit_list[$i]['visit'] = $this->db->f('visit');
2974           $visit_list[$i]['family'] = $this->db->f('family');
2975           $visit_list[$i]['date']  = $this->db->f('date');
2976           $i++;
2977         }
2978
2979       for ($i=0; $i < count($visit_list); $i++)
2980         {
2981           $this->nextmatchs->template_alternate_row_color(&$this->t);
2982
2983           $sql = "SELECT * FROM tc_family WHERE family=".$visit_list[$i]['family'];
2984           $this->db->query($sql,__LINE__,__FILE__);
2985           $this->db->next_record();
2986
2987           $this->t->set_var('family',$visit_list[$i]['family']);
2988           $this->t->set_var('family_name',$this->db->f('name'));
2989           $this->t->set_var('date',$visit_list[$i]['date']);
2990
2991           $link_data['menuaction'] = 'tc.tc.vis_update';
2992           $link_data['visit'] = $visit_list[$i]['visit'];
2993           $link_data['name'] = $this->db->f('name');
2994           $link_data['date'] = $visit_list[$i]['date'];
2995           $link_data['action'] = 'view';
2996           $this->t->set_var('view',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
2997           $this->t->set_var('lang_view','View');
2998
2999           $link_data['menuaction'] = 'tc.tc.vis_update';
3000           $link_data['visit'] = $visit_list[$i]['visit'];
3001           $link_data['name'] = $this->db->f('name');
3002           $link_data['date'] = $visit_list[$i]['date'];
3003           $link_data['action'] = 'edit';
3004           $this->t->set_var('edit',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
3005           $this->t->set_var('lang_edit','Edit');
3006
3007           $this->t->fp('list1','visit_list',True);
3008         }
3009
3010       // List the families that are available to record a visit against
3011       $sql = "SELECT * FROM tc_family WHERE valid=1";
3012       $this->db->query($sql,__LINE__,__FILE__);
3013       $total_records = $this->db->num_rows();
3014
3015       $i = 0;
3016       while ($this->db->next_record())
3017         {
3018           $family_names[$i] = $this->db->f('name');
3019           $family_ids[$i] = $this->db->f('family');
3020           $i++;
3021         } array_multisort($family_names, $family_ids);
3022
3023       for ($i=0; $i < count($family_names); $i++)
3024         {
3025           $link_data['menuaction'] = 'tc.tc.vis_update';
3026           $link_data['visit'] = '';
3027           $link_data['family'] = $family_ids[$i];
3028           $link_data['action'] = 'add';
3029           $link_data['name'] = $family_names[$i];
3030           $this->t->set_var('add',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
3031
3032           $this->t->set_var('name',$family_names[$i]);
3033           if(($i+1) % 3 == 0) { $this->t->set_var('table_sep',"</td></tr><tr>"); }
3034           else { $this->t->set_var('table_sep',"</td>"); }
3035           if(($i) % 3 == 0) { $this->nextmatchs->template_alternate_row_color(&$this->t); }
3036
3037           $this->t->fp('list2','family_list',True);
3038         }
3039
3040       $this->t->pfp('out','vis_view_t');
3041       $this->save_sessiondata();
3042     }
3043
3044   function vis_update()
3045     {
3046       $this->t->set_file(array('form' => 'vis_update.tpl'));
3047       $this->t->set_block('form','add','addhandle');
3048       $this->t->set_block('form','edit','edithandle');
3049
3050       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_view'));
3051       $this->t->set_var('readonly','');
3052       $this->t->set_var('disabled','');
3053
3054       $action = get_var('action',array('GET','POST'));
3055       $visit = get_var('visit',array('GET','POST'));
3056       $family = get_var('family',array('GET','POST'));
3057       $name = get_var('name',array('GET','POST'));
3058       $date = get_var('date',array('GET','POST'));
3059       $notes = get_var('notes',array('GET','POST'));
3060       $companionship = 0;
3061
3062       if($action == 'save')
3063         {
3064           $notes = get_var('notes',array('POST'));
3065           $this->db->query("UPDATE tc_visit set " .
3066                            "  date='" . $date . "'" .
3067                           ", notes='" . $notes . "'" .
3068                            " WHERE visit=" . $visit,__LINE__,__FILE__);
3069           $this->vis_view();
3070           return false;
3071         }
3072
3073       if($action == 'insert')
3074         {
3075           $notes = get_var('notes',array('POST'));
3076           $this->db->query("INSERT INTO tc_visit (family,companionship,date,notes) "
3077                            . "VALUES ('" . $family . "','" . $companionship . "','"
3078                            . $date . "','" . $notes . "')",__LINE__,__FILE__);
3079           $this->vis_view();
3080           return false;
3081         }
3082
3083       if($action == 'add')
3084         {
3085           $this->t->set_var('cal_date',$this->jscal->input('date','','','','','','',$this->cal_options));
3086           $this->t->set_var('family', $family);
3087           $this->t->set_var('visit', '');
3088           $this->t->set_var('name', $name);
3089           $this->t->set_var('date','');
3090           $this->t->set_var('notes','');
3091           $this->t->set_var('lang_done','Cancel');
3092           $this->t->set_var('lang_action','Adding New Visit');
3093           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_update&family='
3094                                                                 . $family . '&action=' . 'insert'));
3095         }
3096
3097       if($action == 'edit' || $action == 'view')
3098         {
3099           $sql = "SELECT * FROM tc_visit WHERE visit=".$visit;
3100           $this->db->query($sql,__LINE__,__FILE__);
3101           $this->db->next_record();
3102           $this->t->set_var('visit',$visit);
3103           $this->t->set_var('name',$name);
3104           $this->t->set_var('family', $family);
3105           $this->t->set_var('date',$this->db->f('date'));
3106           $this->t->set_var('notes',$this->db->f('notes'));
3107         }
3108
3109       if($action == 'edit')
3110         {
3111           $this->t->set_var('cal_date',$this->jscal->input('date',$date,'','','','','',$this->cal_options));
3112           $this->t->set_var('lang_done','Cancel');
3113           $this->t->set_var('lang_action','Editing Visit');
3114           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_update&visit='
3115                                                                 . $visit . '&action=' . 'save'));
3116         }
3117
3118       if($action == 'view')
3119         {
3120           $date = $this->db->f('date');
3121           $this->t->set_var('cal_date','<input type=text size="10" maxlength="10" name="date" value="'.$date.'" readonly>');
3122           $this->t->set_var('readonly','READONLY');
3123           $this->t->set_var('disabled','DISABLED');
3124           $this->t->set_var('lang_done','Done');
3125           $this->t->set_var('lang_action','Viewing Visit');
3126           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_update&visit='
3127                                                                 . $visit . '&action=' . 'edit'));
3128         }
3129
3130       $this->t->set_var('lang_reset','Clear Form');
3131       $this->t->set_var('lang_add','Add Visit');
3132       $this->t->set_var('lang_save','Save Changes');
3133       $this->t->set_var('edithandle','');
3134       $this->t->set_var('addhandle','');
3135
3136       $this->t->pfp('out','form');
3137
3138       if($action == 'view') { $this->t->set_var('lang_save','Edit Visit'); }
3139       if($action == 'edit' || $action == 'view') { $this->t->pfp('addhandle','edit'); }
3140       if($action == 'add') { $this->t->pfp('addhandle','add'); }
3141
3142       $this->save_sessiondata();
3143     }
3144
3145   function att_view()
3146     {
3147       $monthnum['Jan']=1; $monthnum['Feb']=2; $monthnum['Mar']=3; $monthnum['Apr']=4;
3148       $monthnum['May']=5; $monthnum['Jun']=6; $monthnum['Jul']=7; $monthnum['Aug']=8;
3149       $monthnum['Sep']=9; $monthnum['Oct']=10; $monthnum['Nov']=11; $monthnum['Dec']=12;
3150
3151       $this->t->set_file(array('att_view_t' => 'att_view.tpl'));
3152       $this->t->set_block('att_view_t','act_list','list');
3153
3154       $this->t->set_block('att_view_t','month_list','list1');
3155       $this->t->set_block('att_view_t','header_list','list2');
3156       $this->t->set_block('att_view_t','indiv_list','list3');
3157
3158       $this->t->set_var('linkurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.att_view'));
3159       $num_quarters = get_var('num_quarters',array('GET','POST'));
3160       if($num_quarters == '') { $num_quarters = $this->default_att_num_quarters; }
3161       $this->t->set_var('num_quarters',$num_quarters);
3162       $this->t->set_var('lang_filter','Filter');
3163       if($num_quarters == 1) { $this->t->set_var('lang_num_quarters','Quarter of History'); }
3164       else { $this->t->set_var('lang_num_quarters','Quarters of History'); }
3165
3166       $num_months = $num_quarters * 3;
3167       $current_month = $this->current_month;
3168       if($current_month >= 1 && $current_month <= 3) { $current_month=3; }
3169       else if($current_month >= 4 && $current_month <= 6) { $current_month=6; }
3170       else if($current_month >= 7 && $current_month <= 9) { $current_month=9; }
3171       else if($current_month >= 10 && $current_month <= 12) { $current_month=12; }
3172
3173       // TODO:  changed this so it picks the quorum dynamically
3174       $sql = "SELECT * FROM tc_indiv where steward='Elder' and valid=1";
3175       $this->db->query($sql,__LINE__,__FILE__);
3176       $i=0;
3177       while ($this->db->next_record())
3178         {
3179           $indiv_name[$i] = $this->db->f('name');
3180           $indiv_id[$i] = $this->db->f('indiv');
3181           $i++;
3182         }
3183       array_multisort($indiv_name, $indiv_id);
3184
3185       // Create a list of sunday dates for a window of 3 months back and current month
3186       $i=0;
3187       $last_time = 0;
3188       $found_sunday = 0;
3189       $sunday_list[0]['date'] = date("Y-m-d", mktime(0, 0, 0, ($current_month-$num_months)+1, 1, date("y")));
3190       $last_date = explode("-",$sunday_list[0]['date']);
3191       $last_time = mktime(0, 0, 0, $last_date[1], $last_date[2], $last_date[0]);
3192       $time_limit = mktime(0, 0, 0, $current_month, 31, date("y"));
3193       while($last_time < $time_limit)
3194       {
3195         $day = date("w",$last_time);
3196         if(date("w",$last_time) == 0) {
3197           $sunday_list[$i]['date'] = date("Y-m-d", $last_time);
3198           $last_date = explode("-",$sunday_list[$i]['date']);
3199           $last_time = mktime(0, 0, 0, $last_date[1], $last_date[2], $last_date[0]);
3200           $sunday_list[$i]['day'] = $last_date[2];
3201           $sunday_list[$i]['month'] = date("M",$last_time);
3202           $sunday_list[$i]['year'] = $last_date[0];
3203           $found_sunday = 1;
3204           $last_date = $sunday_list[$i]['date'];
3205         }
3206         $last_time += 90000;
3207         if($found_sunday) { $i++; $found_sunday=0; }
3208       }
3209
3210       $total_indivs = count($indiv_id);
3211       $old_month=$sunday_list[0]['month']; $span=0;
3212       for ($i=0; $i < count($sunday_list); $i++) {
3213         $date = $sunday_list[$i]['date'];
3214         $this->t->set_var('date',$sunday_list[$i]['date']);
3215         $this->t->set_var('day',$sunday_list[$i]['day']);
3216         if(($old_month != $sunday_list[$i]['month']) || $i == count($sunday_list)-1) {
3217           if($i == count($sunday_list)-1) { $span++; }
3218           $cur_month = $sunday_list[$i]['month'];
3219           $old_month = $sunday_list[$i]['month'];
3220           $link_data['menuaction'] = 'tc.tc.att_update';
3221           $link_data['month'] = $sunday_list[$i-1]['month'];
3222           $link_data['year'] = $sunday_list[$i-1]['year'];
3223           $link_data['action'] = 'update_month';
3224           $cur_month = $sunday_list[$i-1]['month'];
3225           $cur_year = $sunday_list[$i-1]['year'];
3226           $header_row .= "<th><font size=-3>$cur_month&nbsp;$cur_year</font></th>";
3227           $this->t->set_var('update_month',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
3228           $this->t->set_var('month',$sunday_list[$i-1]['month']);
3229           $this->t->set_var('year',$sunday_list[$i-1]['year']);
3230           $this->t->set_var('span',$span); $span=0;
3231           $this->t->fp('list1','month_list',True);
3232         } $span++;
3233       }
3234       $this->t->set_var('total_indivs',$total_indivs);
3235       $this->t->set_var('header_row',$header_row);
3236
3237       $indiv_width=200; $att_width=25; $total_width=$indiv_width;
3238       for ($i=0; $i < count($sunday_list); $i++) {
3239         $link_data['menuaction'] = 'tc.tc.att_update';
3240         $link_data['month'] = $sunday_list[$i]['month'];
3241         $link_data['year'] = $sunday_list[$i]['year'];
3242         $link_data['day'] = $sunday_list[$i]['day'];
3243         $link_data['date'] = $sunday_list[$i]['date'];
3244         $link_data['action'] = 'update_day';
3245         $this->t->set_var('update_day',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
3246         $this->t->set_var('date',$sunday_list[$i]['date']);
3247         $this->t->set_var('day',$sunday_list[$i]['day']);
3248         $this->t->set_var('month',$sunday_list[$i]['month']);
3249         $this->t->set_var('year',$sunday_list[$i]['year']);
3250         $this->t->fp('list2','header_list',True);
3251         $total_width += $att_width;
3252         $attendance[$monthnum[$sunday_list[$i]['month']]]=0;
3253       }
3254
3255       for ($i=0; $i < count($indiv_id); $i++) {
3256         $att_table = "";
3257         $this->nextmatchs->template_alternate_row_color(&$this->t);
3258         $this->t->set_var('indiv_name',$indiv_name[$i]);
3259         #print "checking for indiv: " . $indiv_id[$i] . "<br>";
3260         for ($j=0; $j < count($sunday_list); $j++) {
3261           #print "checking for date: " .  $sunday_list[$j]['date'] . "<br>";
3262           #print "SELECT * FROM tc_attendance WHERE date='"
3263           #  . $sunday_list[$j]['date'] . "' AND indiv=" . $indiv_id[$i] . "<br>";
3264           $sql = "SELECT * FROM tc_attendance WHERE date='"
3265              . $sunday_list[$j]['date'] . "' AND indiv=" . $indiv_id[$i];
3266           $this->db->query($sql,__LINE__,__FILE__);
3267           if($this->db->next_record()) {
3268             $cur_month = $sunday_list[$j]['month'];
3269             if($attended[$i][$cur_month] != 1) {
3270               $attended[$i][$cur_month]=1;
3271               $attendance[$monthnum[$cur_month]]++;
3272             }
3273             $att_table .= '<td align=center><img src="images/checkmark.gif"></td>';
3274           } else {
3275             $att_table .= '<td>&nbsp;</td>';
3276           }
3277         }
3278         $this->t->set_var('att_table',$att_table);
3279         $this->t->fp('list3','indiv_list',True);
3280       }
3281       $this->t->set_var('total_width',$total_width);
3282       $this->t->set_var('indiv_width',$indiv_width);
3283       $this->t->set_var('att_width',$att_width);
3284
3285       # Now calculate attendance for these months
3286       $attendance_str = "";
3287       $nonattendance_str = "";
3288       $aveattendance_str = "";
3289       $avenonattendance_str = "";
3290       $num_months=0;
3291       $ave_total_attended=0;
3292       ksort($attendance);
3293       foreach($attendance as $att => $value) {
3294         $total_attended = $attendance[$att];
3295         $ave_total_attended += $attendance[$att]; $num_months++;
3296         $percent = ceil(($total_attended / $total_indivs)*100);
3297         $attendance_str.="<td align=center><font size=-2><b>$total_attended ($percent%)</b></font></td>";
3298         $total_nonattended = $total_indivs - $total_attended;
3299         $percent = ceil(($total_nonattended / $total_indivs)*100);
3300         $nonattendance_str.="<td align=center><font size=-2><b>$total_nonattended ($percent%)</b></font></td>";
3301
3302         $total_attended = ceil(($ave_total_attended / $num_months));
3303         $percent = ceil(($total_attended / $total_indivs)*100);
3304         $aveattendance_str .= "<td align=center><font size=-2><b>$total_attended ($percent%)</b></font></td>";
3305         $total_attended = $total_indivs - ceil(($ave_total_attended / $num_months));
3306         $percent = ceil(($total_attended / $total_indivs)*100);
3307         $avenonattendance_str .= "<td align=center><font size=-2><b>$total_attended ($percent%)</b></font></td>";
3308       }
3309
3310       $this->t->set_var('attendance',$attendance_str);
3311       $this->t->set_var('aveattendance',$aveattendance_str);
3312       $this->t->set_var('nonattendance',$nonattendance_str);
3313       $this->t->set_var('avenonattendance',$avenonattendance_str);
3314
3315       $this->t->pfp('out','att_view_t');
3316       $this->save_sessiondata();
3317     }
3318
3319   function att_update()
3320     {
3321       $monthnum['Jan']=1; $monthnum['Feb']=2; $monthnum['Mar']=3; $monthnum['Apr']=4;
3322       $monthnum['May']=5; $monthnum['Jun']=6; $monthnum['Jul']=7; $monthnum['Aug']=8;
3323       $monthnum['Sep']=9; $monthnum['Oct']=10; $monthnum['Nov']=11; $monthnum['Dec']=12;
3324
3325       $this->t->set_file(array('form' => 'att_update.tpl'));
3326       $this->t->set_block('form','edit','edithandle');
3327
3328       $this->t->set_block('form','month_list','list1');
3329       $this->t->set_block('form','header_list','list2');
3330       $this->t->set_block('form','indiv_list','list3');
3331
3332       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.att_view'));
3333
3334       $action = get_var('action',array('GET','POST'));
3335       $month = get_var('month',array('GET','POST'));
3336       $year = get_var('year',array('GET','POST'));
3337       $day = get_var('day',array('GET','POST'));
3338       $date = get_var('date',array('GET','POST'));
3339
3340       if($action == 'save_month' || $action == 'save_day')
3341         {
3342            $new_data = get_var('indivs_attended',array('POST'));
3343            $month = $monthnum[$month]; if($month < 10) { $month = "0" . $month; }
3344
3345            if($action == 'save_month') {
3346              $this->db->query("DELETE from tc_attendance where date LIKE '".$year."-".$month."-%'",__LINE__,__FILE__);
3347            }
3348
3349            if($action == 'save_day') {
3350              $this->db->query("DELETE from tc_attendance where date LIKE '".$year."-".$month."-".$day."'",__LINE__,__FILE__);
3351            }
3352
3353            foreach ($new_data as $data)
3354            {
3355               $data_array = explode("-",$data);
3356               $indiv = $data_array[0];
3357               $date  = "$data_array[1]-$data_array[2]-$data_array[3]";
3358               $this->db->query("INSERT INTO tc_attendance (indiv,date) "
3359                                . "VALUES (" . $indiv . ",'". $date . "')",__LINE__,__FILE__);
3360            }
3361
3362          $this->att_view();
3363          return false;
3364         }
3365
3366       // TODO:  changed this so it picks the quorum dynamically
3367       $sql = "SELECT * FROM tc_indiv where steward='Elder' and valid=1";
3368       $this->db->query($sql,__LINE__,__FILE__);
3369       $i=0;
3370       while ($this->db->next_record())
3371         {
3372           $indiv_name[$i] = $this->db->f('name');
3373           $indiv_id[$i] = $this->db->f('indiv');
3374           $indiv_attending[$indiv_id[$i]] = $this->db->f('attending');
3375           $i++;
3376         }
3377       array_multisort($indiv_name, $indiv_id);
3378
3379       if($action == 'update_month')
3380       {
3381         $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.att_update&action=save_month'));
3382         $i=0;
3383         $last_time = 0;
3384         $found_sunday = 0;
3385         $sunday_list[0]['date'] = date("Y-m-d", mktime(0, 0, 0, $monthnum[$month], 1, $year));
3386         $last_date = explode("-",$sunday_list[0]['date']);
3387         $last_time = mktime(0, 0, 0, $last_date[1], $last_date[2], $last_date[0]);
3388         $time_limit = mktime(0, 0, 0, $monthnum[$month], 31, $year);
3389         while($last_time <= $time_limit)
3390         {
3391           $day = date("w",$last_time);
3392           if(date("w",$last_time) == 0) {
3393             $sunday_list[$i]['date'] = date("Y-m-d", $last_time);
3394             $last_date = explode("-",$sunday_list[$i]['date']);
3395             $last_time = mktime(0, 0, 0, $last_date[1], $last_date[2], $last_date[0]);
3396             $sunday_list[$i]['day'] = $last_date[2];
3397             $sunday_list[$i]['month'] = date("M",$last_time);
3398             $sunday_list[$i]['year'] = $last_date[0];
3399             $found_sunday = 1;
3400           }
3401           $last_time += 90000;
3402           if($found_sunday) { $i++; $found_sunday=0; }
3403         }
3404
3405         $this->t->set_var('span', $i);
3406         $this->t->set_var('month',$sunday_list[$i-1]['month']);
3407         $this->t->set_var('year',$sunday_list[$i-1]['year']);
3408         $this->t->fp('list1','month_list',True);
3409         $indiv_width=200; $att_width=25; $total_width=$indiv_width;
3410         for ($i=0; $i < count($sunday_list); $i++) {
3411           $link_data['menuaction'] = 'tc.tc.att_update';
3412           $link_data['month'] = $sunday_list[$i]['month'];
3413           $link_data['year'] = $sunday_list[$i]['year'];
3414           $link_data['day'] = $sunday_list[$i]['day'];
3415           $link_data['date'] = $sunday_list[$i]['date'];
3416           $link_data['action'] = 'update_day';
3417           $this->t->set_var('update_day',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
3418           $this->t->set_var('date',$sunday_list[$i]['date']);
3419           $this->t->set_var('day',$sunday_list[$i]['day']);
3420           $this->t->set_var('month',$sunday_list[$i]['month']);
3421           $this->t->set_var('year',$sunday_list[$i]['year']);
3422           $this->t->fp('list2','header_list',True);
3423           $total_width += $att_width;
3424         }
3425       }
3426
3427       if($action == 'update_day')
3428       {
3429         $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.att_update&action=save_day'));
3430         $sunday_list[0]['date'] = date("Y-m-d", mktime(0, 0, 0, $monthnum[$month], $day, $year));
3431         $this->t->set_var('month',$month);
3432         $this->t->set_var('year',$year);
3433         $this->t->fp('list1','month_list',True);
3434         $this->t->set_var('date',$date);
3435         $this->t->set_var('day',$day);
3436         $this->t->set_var('month',$month);
3437         $this->t->set_var('year',$year);
3438         $this->t->fp('list2','header_list',True);
3439       }
3440
3441       for ($i=0; $i < count($indiv_id); $i++) {
3442         $att_table = "";
3443         $this->nextmatchs->template_alternate_row_color(&$this->t);
3444         $this->t->set_var('indiv_name',$indiv_name[$i]);
3445         for ($j=0; $j < count($sunday_list); $j++) {
3446           $sql = "SELECT * FROM tc_attendance WHERE date='"
3447              . $sunday_list[$j]['date'] . "' AND indiv=" . $indiv_id[$i];
3448           $this->db->query($sql,__LINE__,__FILE__);
3449           $value = $indiv_id[$i] . "-" . $sunday_list[$j]['date'];
3450           if($this->db->next_record()) {
3451             $att_table .= '<td align=center><input type="checkbox" name="indivs_attended[]" value="'.$value.'" checked></td>';
3452           } else if($indiv_attending[$indiv_id[$i]] == 1) {
3453             $att_table .= '<td align=center><input type="checkbox" name="indivs_attended[]" value="'.$value.'" checked></td>';
3454           } else {
3455             $att_table .= '<td align=center><input type="checkbox" name="indivs_attended[]" value="'.$value.'"></td>';
3456           }
3457         }
3458         $this->t->set_var('att_table',$att_table);
3459         $this->t->fp('list3','indiv_list',True);
3460       }
3461
3462       $this->t->set_var('lang_done', 'Cancel');
3463       $this->t->set_var('lang_reset','Clear Form');
3464       $this->t->set_var('lang_save','Save Changes');
3465
3466       $this->t->pfp('out','form');
3467       $this->t->pfp('addhandle','edit');
3468
3469       $this->save_sessiondata();
3470     }
3471
3472   function dir_view()
3473     {
3474       $this->t->set_file(array('dir_view_t' => 'dir_view.tpl'));
3475       $this->t->set_block('dir_view_t','dir_list','list');
3476
3477       $sql = "SELECT * FROM tc_indiv where valid=1 and hh_position='Head of Household' ORDER BY name ASC";
3478       $this->db->query($sql,__LINE__,__FILE__);
3479       $i=0;
3480       while ($this->db->next_record())
3481         {
3482           $parent[$i]['id'] = $this->db->f('indiv');
3483           $parent[$i]['name'] = $this->db->f('name');
3484           $parent[$i]['phone'] = $this->db->f('phone');
3485           $parent[$i]['address'] = $this->db->f('address');
3486           $i++;
3487         }
3488
3489       for ($i=0; $i < count($parent); $i++)
3490       {
3491         $name = $parent[$i]['name'];
3492         $phone = $parent[$i]['phone'];
3493         $address = $parent[$i]['address'];
3494         $this->t->set_var('name', $name);
3495         $this->t->set_var('address', $address);
3496         $this->t->set_var('phone', $phone);
3497         $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
3498         $this->t->set_var('tr_color',$tr_color);
3499         $this->t->fp('list','dir_list',True);
3500         //print "$phone $name $address<br>";
3501       }
3502       $this->t->pfp('out','dir_view_t');
3503       $this->save_sessiondata();
3504     }
3505
3506   function org_view()
3507     {
3508       $this->t->set_file(array('org_view_t' => 'org_view.tpl'));
3509       $this->t->set_block('org_view_t','calling_list','list1');
3510       $this->t->set_block('org_view_t','org_list','list2');
3511
3512       # Display a list ordered alphabetically
3513       $sql = "SELECT * FROM tc_calling ORDER BY name ASC";
3514       $this->db->query($sql,__LINE__,__FILE__);
3515       $i=0;
3516       while ($this->db->next_record())
3517         {
3518           $calling[$i]['id'] = $this->db->f('indiv_id');
3519           $calling[$i]['name'] = $this->db->f('name');
3520           $calling[$i]['position'] = $this->db->f('position');
3521           $calling[$i]['sustained'] = $this->db->f('sustained');
3522           $calling[$i]['organization'] = $this->db->f('organization');
3523           $i++;
3524         }
3525       for ($i=0; $i < count($calling); $i++)
3526       {
3527         $name = $calling[$i]['name'];
3528         $position = $calling[$i]['position'];
3529         $sustained = $calling[$i]['sustained'];
3530         $organization = $calling[$i]['organization'];
3531         $this->t->set_var('name', $name);
3532         $this->t->set_var('position', $position);
3533         $this->t->set_var('sustained', $sustained);
3534         $this->t->set_var('organization', $organization);
3535         $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
3536         $this->t->set_var('tr_color',$tr_color);
3537         $this->t->fp('list1','calling_list',True);
3538       }
3539
3540       # Display a list ordered by organization
3541       $sql = "SELECT * FROM tc_calling ORDER BY sequence ASC";
3542       $this->db->query($sql,__LINE__,__FILE__);
3543       $i=0;
3544       while ($this->db->next_record())
3545         {
3546           $calling[$i]['id'] = $this->db->f('indiv_id');
3547           $calling[$i]['name'] = $this->db->f('name');
3548           $calling[$i]['position'] = $this->db->f('position');
3549           $calling[$i]['sustained'] = $this->db->f('sustained');
3550           $calling[$i]['organization'] = $this->db->f('organization');
3551           $i++;
3552         }
3553       for ($i=0; $i < count($calling); $i++)
3554       {
3555         $name = $calling[$i]['name'];
3556         $position = $calling[$i]['position'];
3557         $sustained = $calling[$i]['sustained'];
3558         $organization = $calling[$i]['organization'];
3559         $this->t->set_var('name', $name);
3560         $this->t->set_var('position', $position);
3561         $this->t->set_var('sustained', $sustained);
3562         $this->t->set_var('organization', $organization);
3563         $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
3564         $this->t->set_var('tr_color',$tr_color);
3565         $this->t->fp('list2','org_list',True);
3566       }
3567
3568       $this->t->pfp('out','org_view_t');
3569       $this->save_sessiondata();
3570     }
3571
3572   function schedule()
3573     {
3574       $this->t->set_file(array('sched_t' => 'schedule.tpl'));
3575       $this->t->set_block('sched_t','presidency_list','list');
3576
3577       $action = get_var('action',array('GET','POST'));
3578
3579       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.schedule&action=save'));
3580       $this->t->set_var('title','Scheduling Tool');
3581
3582       $this->t->set_var('lang_save','Save Schedule');
3583       $this->t->set_var('lang_reset','Cancel');
3584
3585       $this->t->set_var('schedule_vis_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_sched'));
3586       $this->t->set_var('schedule_vis_link_title','Schedule Yearly Visits');
3587
3588       $this->t->set_var('schedule_int_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_sched'));
3589       $this->t->set_var('schedule_int_link_title','Schedule Hometeaching Interviews');
3590
3591       $this->t->set_var('schedule_ppi_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_sched'));
3592       $this->t->set_var('schedule_ppi_link_title','Schedule Yearly PPIs');
3593
3594       $date_width=150; $time_width=220; $indiv_width=170; $family_width=180; $location_width=100;
3595       $table_width=$date_width + $time_width + $indiv_width + $family_width + $location_width;
3596       $header_row = "<th width=$date_width><font size=-2>Date</th>";
3597       $header_row.= "<th width=$time_width><font size=-2>Time</th>";
3598       $header_row.= "<th width=$indiv_width><font size=-2>individual</th>";
3599       $header_row.= "<th width=$family_width><font size=-2>Family</th>";
3600       $header_row.= "<th width=$location_width><font size=-2>Location</th>";
3601       $table_data = "";
3602
3603       $sql = "SELECT * FROM tc_presidency where valid=1";
3604       $this->db->query($sql,__LINE__,__FILE__);
3605       $i=0;
3606       while ($this->db->next_record())
3607         {
3608           $presidency_data[$i]['id'] = $this->db->f('presidency');
3609           $presidency_data[$i]['name'] = $this->db->f('name');
3610           $presidency_data[$i]['indiv'] = $this->db->f('indiv');
3611           $presidency2name[$presidency_data[$i]['id']] = $presidency_data[$i]['name'];
3612           $presidency2indiv[$presidency_data[$i]['id']] = $presidency_data[$i]['indiv'];
3613           $i++;
3614         }
3615
3616       $sql = "SELECT * FROM tc_family where valid=1 and indiv_id != 0 ORDER BY name ASC";
3617       $this->db->query($sql,__LINE__,__FILE__);
3618       $i=0;
3619       while ($this->db->next_record())
3620         {
3621           $family_id[$i] = $this->db->f('family');
3622           $family_name[$i] = $this->db->f('name');
3623           $familyid2name[$family_id[$i]] = $family_name[$i];
3624           $sql = "SELECT * FROM tc_indiv where family='$family_id[$i]' and hh_position='Head of Household'";
3625           $this->db2->query($sql,__LINE__,__FILE__);
3626           if($this->db2->next_record()) {
3627             $familyid2address[$family_id[$i]] = $this->db2->f('address');
3628           }
3629           $i++;
3630         }
3631       array_multisort($family_name, $family_id);
3632
3633       if($action == 'save')
3634         {
3635           $new_data = get_var('sched',array('POST'));
3636           foreach ($new_data as $presidency_array)
3637            {
3638              foreach ($presidency_array as $entry)
3639                {
3640                  $presidency = $entry['presidency'];
3641                  $appointment = $entry['appointment'];
3642                  $location = $entry['location'];
3643                  $date = $entry['date'];
3644                  $hour = $entry['hour'];
3645                  $minute = $entry['minute'];
3646                  $pm = $entry['pm'];
3647                  $indiv = $entry['indiv'];
3648                  $family = $entry['family'];
3649                  $location = $entry['location'];
3650                  if($pm) { $hour = $hour + 12; }
3651                  $time = $hour.':'.$minute.':'.'00';
3652                  $uid = 0;
3653
3654                  // Update our location
3655                  if($location == "") {
3656                    if($family > 0) {
3657                      $family_name_array = explode(",", $familyid2name[$family]);
3658                      $family_last_name = $family_name_array[0];
3659                      $family_address = $familyid2address[$family];
3660                      $location = "$family_last_name"." home ($family_address)";
3661                    }
3662                    else if($indiv > 0) {
3663                      $supervisor_name_array = explode(",",$presidency2name[$presidency]);
3664                      $supervisor_last_name = $supervisor_name_array[0];
3665                      $sql = "SELECT * FROM tc_indiv where indiv='$presidency2indiv[$presidency]'";
3666                      $this->db2->query($sql,__LINE__,__FILE__);
3667                      if($this->db2->next_record()) {
3668                        $indiv_id = $this->db2->f('indiv_id');
3669                      }
3670                      $sql = "SELECT * FROM tc_indiv where indiv_id='$indiv_id'";
3671                      $this->db2->query($sql,__LINE__,__FILE__);
3672                      if($this->db2->next_record()) {
3673                        $supervisor_address = $this->db2->f('address');
3674                      }
3675                      $location = "$supervisor_last_name"." home ($supervisor_address)";
3676                    }
3677                  }
3678
3679                  // Zero out the family or individual if date = NULL
3680                  if($date == "") {
3681                    $indiv = 0;
3682                    $family = 0;
3683                    $location = "";
3684                  }
3685
3686                  if(($indiv == 0) && ($family == 0)) { $location = ""; }
3687
3688                  // Update an existing appointment
3689                  if($appointment < $this->max_appointments)
3690                    {
3691                      //Only perform a database update if we have made a change to this appointment
3692                      $sql = "SELECT * FROM tc_appointment where " .
3693                         "appointment='$appointment'" .
3694                         " and presidency='$presidency'" .
3695                         " and indiv='$indiv'" .
3696                         " and family='$family'" .
3697                         " and date='$date'" .
3698                         " and time='$time'" .
3699                         " and location='$location'";
3700                      $this->db->query($sql,__LINE__,__FILE__);
3701                      if(!$this->db->next_record()) {
3702                        $old_date = $this->db->f('date');
3703                        $old_time = $this->db->f('time');
3704                        $this->db2->query("UPDATE tc_appointment set" .
3705                                         " family=" . $family .
3706                                         " ,indiv=" . $indiv .
3707                                         " ,date='" . $date . "'" .
3708                                         " ,time='" . $time . "'" .
3709                                         " ,location='" . $location . "'" .
3710                                         " ,presidency='" . $presidency . "'" .
3711                                         " WHERE appointment=" . $appointment,__LINE__,__FILE__);
3712
3713                        // Email the appointment
3714                        $this->email_appt($appointment);
3715                      }
3716                    }
3717
3718                  // Add a new appointment
3719                  else if(($appointment >= $this->max_appointments) && ($date != "") && ($time != ""))
3720                    {
3721                      //print "adding entry: appt=$appointment date: $date time: $time indiv: $indiv family: $family<br>";
3722                      $this->db2->query("INSERT INTO tc_appointment (appointment,presidency,family,indiv,date,time,location,uid) "
3723                            . "VALUES (NULL,'" . $presidency . "','" . $family . "','" . $indiv . "','"
3724                            . $date . "','" . $time  . "','" . $location . "','" . $uid ."')",__LINE__,__FILE__);
3725
3726                      // Now reselect this entry from the database to see if we need
3727                      // to send an appointment out for it.
3728                      $sql = "SELECT * FROM tc_appointment where " .
3729                         "indiv='$indiv'" .
3730                         " and family='$family'" .
3731                         " and presidency='$presidency'" .
3732                         " and date='$date'" .
3733                         " and time='$time'" .
3734                         " and uid='$uid'" .
3735                         " and location='$location'";
3736                      $this->db3->query($sql,__LINE__,__FILE__);
3737                      if($this->db3->next_record()) {
3738                        // Email the appointment if warranted
3739                        if(($date != "") && ($time != "") && (($indiv > 0) || $family > 0)) {
3740                          $this->email_appt($this->db3->f('appointment'));
3741                        }
3742                      }
3743                    }
3744                }
3745            }
3746
3747           $take_me_to_url = $GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.schedule');
3748           //Header('Location: ' . $take_me_to_url);
3749         }
3750
3751       // TODO:  changed this so it picks the quorum dynamically
3752       $sql = "SELECT * FROM tc_indiv where steward='Elder' and valid=1 ORDER BY indiv ASC";
3753       $this->db->query($sql,__LINE__,__FILE__);
3754       $i=0;
3755       while ($this->db->next_record())
3756         {
3757           $indiv_id[$i] = $this->db->f('indiv');
3758           $indiv_name[$i] = $this->db->f('name');
3759           $indiv_phone[$indiv_id[$i]] = $this->db->f('phone');
3760           $i++;
3761         }
3762       array_multisort($indiv_name, $indiv_id);
3763
3764       for ($i=0; $i < count($presidency_data); $i++) {
3765         $presidency = $presidency_data[$i]['id'];
3766         $interviewer = $presidency_data[$i]['indiv'];
3767         $name = $presidency_data[$i]['name'];
3768         $this->t->set_var('presidency_name',$name);
3769         $table_data="";
3770
3771         // query the database for all the appointments
3772         $sql = "SELECT * FROM tc_appointment where presidency=$presidency and date>=CURDATE() ORDER BY date ASC, time ASC";
3773         $this->db->query($sql,__LINE__,__FILE__);
3774
3775         // Prefill any existing appointment slots
3776         while ($this->db->next_record())
3777           {
3778             $appointment = $this->db->f('appointment');
3779             $indiv = $this->db->f('indiv');
3780             $family = $this->db->f('family');
3781             $location = $this->db->f('location');
3782
3783             if($location == "") {
3784               if($family > 0) {
3785                 $family_name_array = explode(",", $familyid2name[$family]);
3786                 $family_last_name = $family_name_array[0];
3787                 $family_address = $familyid2address[$family];
3788                 $location = "$family_last_name"." home ($family_address)";
3789               }
3790               else if($indiv > 0) {
3791                 $supervisor_name_array = explode(",",$presidency2name[$presidency]);
3792                 $supervisor_last_name = $supervisor_name_array[0];
3793                 $sql = "SELECT * FROM tc_indiv where indiv='$presidency2indiv[$presidency]'";
3794                 $this->db2->query($sql,__LINE__,__FILE__);
3795                 if($this->db2->next_record()) {
3796                   $indiv_id = $this->db2->f('indiv_id');
3797                 }
3798                 $sql = "SELECT * FROM tc_indiv where indiv_id='$indiv_id'";
3799                 $this->db2->query($sql,__LINE__,__FILE__);
3800                 if($this->db2->next_record()) {
3801                   $supervisor_address = $this->db2->f('address');
3802                 }
3803                 $location = "$supervisor_last_name"." home ($supervisor_address)";
3804               }
3805             }
3806
3807             $date = $this->db->f('date');
3808             $date_array = explode("-",$date);
3809             $year = $date_array[0]; $month = $date_array[1]; $day = $date_array[2];
3810             $day_string = date("l d-M-Y", mktime(0,0,0,$month,$day,$year));
3811
3812             $time = $this->db->f('time');
3813             $time_array = explode(":",$time);
3814             $hour = $time_array[0];
3815             $minute = $time_array[1];
3816             $pm = 0;
3817             if($hour > 12) { $pm=1; $hour = $hour - 12; }
3818             $time_string = date("g:i a", mktime($time_array[0], $time_array[1], $time_array[2]));
3819
3820             $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
3821
3822             // Date selection
3823             $table_data.= '<td align=left>';
3824             $table_data.= $this->jscal->input('sched['.$presidency.']['.$appointment.'][date]',$date,'','','','','',$this->cal_options);
3825             $table_data.= '</td>';
3826
3827             // Hour & Minutes selection
3828             $table_data.= "<td align=center>";
3829             $table_data .= $this->get_time_selection_form($hour, $minute, $pm, $presidency, $appointment);
3830             $table_data.= "</td>";
3831
3832             // individual drop down list (for PPIs)
3833             $table_data.= '<td align=center><select name=sched['.$presidency.']['.$appointment.'][indiv] STYLE="font-size : 8pt">';
3834             $table_data.= '<option value=0></option>';
3835             for ($j=0; $j < count($indiv_id); $j++) {
3836               $id = $indiv_id[$j];
3837               $name = $indiv_name[$j];
3838               if($indiv_id[$j] == $indiv) { $selected[$id] = 'selected="selected"'; } else { $selected[$id] = ''; }
3839               $table_data.= '<option value='.$id.' '.$selected[$id].'>'.$name.'</option>';
3840             }
3841             $table_data.='</select></td>';
3842
3843             // Family drop down list (for Visits)
3844             $table_data.= '<td align=center><select name=sched['.$presidency.']['.$appointment.'][family] STYLE="font-size : 8pt">';
3845             $table_data.= '<option value=0></option>';
3846             for ($j=0; $j < count($indiv_id); $j++) {
3847               $id = $family_id[$j];
3848               $name = $family_name[$j];
3849               if($family_id[$j] == $family) { $selected[$id] = 'selected="selected"'; } else { $selected[$id] = ''; }
3850               $table_data.= '<option value='.$id.' '.$selected[$id].'>'.$name.' Family</option>';
3851             }
3852             $table_data.='</select></td>';
3853
3854             // Location text box
3855             $table_data.= '<td align=center><input type=text size="25" maxlength="120" ';
3856             $table_data.= 'name="sched['.$presidency.']['.$appointment.'][location]" value="'.$location.'" STYLE="font-size : 8pt">';
3857
3858             $table_data.= '<input type=hidden name="sched['.$presidency.']['.$appointment.'][appointment]" value="'.$appointment.'">';
3859             $table_data.= '<input type=hidden name="sched['.$presidency.']['.$appointment.'][presidency]" value="'.$presidency.'">';
3860
3861             $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
3862             $this->t->set_var('tr_color',$tr_color);
3863
3864           }
3865
3866         // Create blank appointment slot
3867         for ($b=0; $b < 4; $b++) {
3868           $appointment = $this->max_appointments + $b;
3869           $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
3870
3871           // Date selection
3872           $table_data.= '<td align=left>';
3873           $table_data.= $this->jscal->input('sched['.$presidency.']['.$appointment.'][date]','','','','','','',$this->cal_options);
3874           $table_data.= '</td>';
3875
3876           // Time selection
3877           $table_data.= "<td align=center>";
3878           $table_data .= $this->get_time_selection_form(0, 0, 0, $presidency, $appointment);
3879           $table_data.= "</td>";
3880
3881           // individual drop down list
3882           $table_data.= '<td align=center><select name=sched['.$presidency.']['.$appointment.'][indiv] STYLE="font-size : 8pt">';
3883           $table_data.= '<option value=0></option>';
3884           for ($j=0; $j < count($indiv_id); $j++) {
3885             $id = $indiv_id[$j];
3886             $name = $indiv_name[$j];
3887             $table_data.= '<option value='.$id.'>'.$name.'</option>';
3888           }
3889           $table_data.='</select></td>';
3890
3891           // Family drop down list
3892           $table_data.= '<td align=center><select name=sched['.$presidency.']['.$appointment.'][family] STYLE="font-size : 8pt">';
3893           $table_data.= '<option value=0></option>';
3894           for ($j=0; $j < count($indiv_id); $j++) {
3895             $id = $family_id[$j];
3896             $name = $family_name[$j];
3897             $table_data.= '<option value='.$id.'>'.$name.' Family</option>';
3898           }
3899           $table_data.='</select></td>';
3900
3901           // Location text box
3902           $table_data.= '<td align=center><input type=text size="25" maxlength="120" ';
3903           $table_data.= 'name="sched['.$presidency.']['.$appointment.'][location]" value="" STYLE="font-size : 8pt">';
3904
3905           $table_data.= '<input type=hidden name="sched['.$presidency.']['.$appointment.'][appointment]" value="'.$appointment.'">';
3906           $table_data.= '<input type=hidden name="sched['.$presidency.']['.$appointment.'][presidency]" value="'.$presidency.'">';
3907
3908           $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
3909           $this->t->set_var('tr_color',$tr_color);
3910         }
3911
3912         $this->t->set_var('table_data',$table_data);
3913         $this->t->set_var('header_row',$header_row);
3914         $this->t->set_var('table_width',$table_width);
3915         $this->t->fp('list','presidency_list',True);
3916
3917       }
3918
3919       $this->t->pfp('out','sched_t');
3920       $this->save_sessiondata();
3921     }
3922
3923   function email()
3924     {
3925       $this->t->set_file(array('email_t' => 'email.tpl'));
3926       $this->t->set_block('email_t','indiv_list','list');
3927
3928       $action = get_var('action',array('GET','POST'));
3929
3930       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.email'));
3931       $this->t->set_var('title','Email Tool');
3932
3933       $this->t->set_var('lang_email','Send Email');
3934       $this->t->set_var('lang_reset','Cancel');
3935
3936       $this->t->set_var('email_member_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.email&action=member'));
3937       $this->t->set_var('email_member_link_title','Email Quorum Member');
3938
3939       $this->t->set_var('email_quorum_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.email&action=quorum'));
3940       $this->t->set_var('email_quorum_link_title','Email Quorum');
3941
3942       $this->t->set_var('email_reminder_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.email&action=reminder'));
3943       $this->t->set_var('email_reminder_link_title','Email Reminders');
3944
3945       $this->t->set_var('email_edit_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.email&action=edit'));
3946       $this->t->set_var('email_edit_link_title','Edit Email Addresses');
3947
3948       $table_width=600;
3949       $this->t->set_var('table_width',$table_width);
3950
3951       $this->t->pfp('out','email_t');
3952       $this->save_sessiondata();
3953     }
3954
3955   function admin()
3956     {
3957       $this->t->set_file(array('admin_t' => 'admin.tpl'));
3958       $this->t->set_block('admin_t','upload','uploadhandle');
3959       $this->t->set_block('admin_t','admin','adminhandle');
3960       $this->t->set_block('admin_t','cmd','cmdhandle');
3961       $this->t->set_block('admin_t','presidency','presidencyhandle');
3962
3963       $this->t->set_var('upload_action',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.admin&action=upload'));
3964       $this->t->set_var('presidency_action',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.admin&action=presidency'));
3965
3966       $action = get_var('action',array('GET','POST'));
3967
3968       $this->t->pfp('out','admin_t');
3969
3970       // TODO:  changed this so it picks the quorum dynamically
3971       $sql = "SELECT * FROM tc_indiv where steward='Elder' and valid=1 ORDER BY indiv ASC";
3972       $this->db->query($sql,__LINE__,__FILE__);
3973       $i=0;
3974       while ($this->db->next_record())
3975         {
3976           $indiv_id[$i] = $this->db->f('indiv');
3977           $indiv_name[$i] = $this->db->f('name');
3978           $indiv2name[$indiv_id[$i]] = $indiv_name[$i];
3979           $i++;
3980         }
3981       array_multisort($indiv_name, $indiv_id);
3982
3983       if($action == 'upload')
3984         {
3985           $target_path = $this->upload_target_path . '/' . basename( $_FILES['uploadedfile']['name']);
3986
3987           if(($_FILES['uploadedfile']['type'] == "application/zip") ||
3988              ($_FILES['uploadedfile']['type'] == "application/x-zip-compressed") ||
3989              ($_FILES['uploadedfile']['type'] == "application/x-zip") ||
3990              ($_FILES['uploadedfile']['type'] == "application/octet-stream")) {
3991
3992             if(!move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path)) {
3993               $uploadstatus = "<b><font color=red> -E- Unable to move the uploaded file to ";
3994               $uploadstatus.= "the target path (check the path and permissions) of: $target_path</font></b>";
3995               $uploadstatus = "<b>The following file was uploaded successfully: </b><br><br>";
3996               $uploadstatus.= "Tmp Filename : " . $_FILES['uploadedfile']['tmp_name'] . "<br>";
3997               $uploadstatus.= "Filename     : " . $_FILES['uploadedfile']['name'] . "<br>";
3998               $uploadstatus.= "Type         : " . $_FILES['uploadedfile']['type'] . "<br>";
3999               $uploadstatus.= "Size         : " . $_FILES['uploadedfile']['size'] . "<br>";
4000               $uploadstatus.= "Error        : " . $_FILES['uploadedfile']['error'] . "<br>";
4001               $this->t->set_var('uploadstatus',$uploadstatus);
4002               $this->t->pfp('uploadhandle','upload',True);
4003               return 0;
4004             }
4005
4006             $uploadstatus = "<b>The following file was uploaded successfully: </b><br><br>";
4007             $uploadstatus.= "Filename : " . $_FILES['uploadedfile']['name'] . "<br>";
4008             $uploadstatus.= "Type     : " . $_FILES['uploadedfile']['type'] . "<br>";
4009             $uploadstatus.= "Size     : " . $_FILES['uploadedfile']['size'] . "<br>";
4010             $this->t->set_var('uploadstatus',$uploadstatus);
4011             $this->t->pfp('uploadhandle','upload');
4012             $this->t->set_var('uploadhandle','');
4013             print "<table border=1 width=80%><tr><td>\n<pre>";
4014
4015             # make a directory for this data to be stored in
4016             $date="data_" . date("Y_m_d");
4017             $data_dir = $this->upload_target_path . '/' . $date;
4018             print "-> Making the data directory: $date<br>\n";
4019             exec('mkdir -p ' . $data_dir . ' 2>&1', $result, $return_code);
4020             if($return_code != 0) {
4021               print implode('\n',$result) . "<br>";
4022               print "<b><font color=red>";
4023               print "-E- Unable to create the data directory. Aborting import.";
4024               print "</font></b>";
4025               return 0;
4026             }
4027
4028             # move the file uploaded into this directory
4029             print "-> Moving the uploaded file into the data dir<br>\n";
4030             exec('mv ' . $target_path . ' ' . $data_dir . '/' . ' 2>&1', $result, $return_code);
4031             if($return_code != 0) {
4032               print implode('\n',$result) . "<br>";
4033               print "<b><font color=red>";
4034               print "-E- Unable to move the uploaded file into the data dir. Aborting import.";
4035               print "</font></b>";
4036               return 0;
4037             }
4038
4039             # unzip the data into this directory
4040             print "-> Unzipping the data<br>\n";
4041             exec($this->unzip_path .' -u '. $data_dir . '/*.zip -d ' . $data_dir . ' 2>&1', $result, $return_code);
4042             if($return_code != 0) {
4043               print implode('\n',$result) . "<br>";
4044               print "<b><font color=red>";
4045               print "-E- Unable to unzip the uploaded file into the data dir: $data_dir. Aborting import.";
4046               print "</font></b>";
4047               return 0;
4048             }
4049             exec('mv ' . $data_dir . '/*/* '. $data_dir . ' 2>&1', $result, $return_code);
4050
4051             # update the data_latest link to point to this new directory
4052             print "-> Updating the latest data dir link<br>\n";
4053             $data_latest = $this->upload_target_path . '/data_latest';
4054             exec('rm ' . $data_latest. '; ln -s ' . $data_dir .' '. $data_latest .' 2>&1', $result, $return_code);
4055             if($return_code != 0) {
4056               print implode('\n',$result) . "<br>";
4057               print "<b><font color=red>";
4058               print "-E- Unable to update the data latest link. Aborting import.";
4059               print "</font></b>";
4060               return 0;
4061             }
4062
4063             # run the import perl script to encorporate it into the DB
4064             ob_start('ob_logstdout', 2);
4065             print "-> Importing the data into the database<br>\n";
4066             ob_flush(); flush(); sleep(1);
4067             $import_log = $this->upload_target_path . '/import.log';
4068             $data_log = $this->upload_target_path . '/data.log';
4069             $import_cmd = $this->script_path . '/import_ward_data ' . $data_latest . ' 2>&1 | tee ' . $import_log;
4070             $parse_cmd = $this->script_path . '/parse_ward_data -v ' . $data_latest . ' > ' . $data_log . '2>&1';
4071             #print "import_cmd: $import_cmd<br>";
4072             #print "parse_cmd: $parse_cmd<br>";
4073             ob_start('ob_logstdout', 2);
4074             passthru($import_cmd);
4075             passthru($parse_cmd);
4076             ob_flush(); flush(); sleep(1);
4077
4078             # fix the permissions of the data dir
4079             exec('chmod -R o-rwx ' . $data_dir, $result, $return_code);
4080
4081             $this->t->pfp('cmdhandle','cmd');
4082             print "</pre></td></tr></table>";
4083
4084           } else if(($_FILES['uploadedfile']['type'] != "application/zip") &&
4085                     ($_FILES['uploadedfile']['type'] != "application/x-zip-compressed") &&
4086                     ($_FILES['uploadedfile']['type'] != "application/x-zip") &&
4087                     ($_FILES['uploadedfile']['type'] != "application/octet-stream")) {
4088             $uploadstatus = "<b><font color=red>The file format must be a .zip file, please try again! </font></b>";
4089             $uploadstatus.= "<br><br><b>Detected file format: " . $_FILES['uploadedfile']['type'] . "</b>";
4090             $this->t->set_var('uploadstatus',$uploadstatus);
4091             $this->t->pfp('uploadhandle','upload',True);
4092           } else {
4093             $uploadstatus = "<b><font color=red> There was an error (" . $_FILES['uploadedfile']['error'];
4094             $uploadstatus.= ") uploading the file, please try again! </font></b>";
4095             $this->t->set_var('uploadstatus',$uploadstatus);
4096             $this->t->pfp('uploadhandle','upload',True);
4097           }
4098         }
4099       else if($action == "presidency")
4100         {
4101           $new_data = get_var('eqpres',array('POST'));
4102           foreach ($new_data as $entry)
4103            {
4104              $id = $entry['id'];
4105              $email = $entry['email'];
4106              $indiv = $entry['indiv'];
4107              $name = $entry['name'];
4108              $district = $entry['district'];
4109              $president = $entry['president'];
4110              $counselor = $entry['counselor'];
4111              $secretary = $entry['secretary'];
4112              $eqpresidency = $entry['eqpresidency'];
4113              // Set the individual id to 0 for EQ Presidency tagged entry
4114              if($eqpresidency == 1) { $indiv="0"; }
4115              // Re-look up the individual name for the ID if we aren't an EQ Presidency tagged entry
4116              else { $name = $indiv2name[$indiv]; }
4117              //print "id=$id indiv=$indiv name=$name email=$email district=$district president=$president ";
4118              //print "counselor=$counselor secretary=$secretary eqpres=$eqpresidency<br>";
4119
4120              if(($indiv > 0) || ($name != "")) {
4121                if($id < $this->max_presidency_members) {
4122                  //print "Updating Existing Entry<br>";
4123                  $this->db2->query("UPDATE tc_presidency set" .
4124                                    " indiv=" . $indiv .
4125                                    " ,district=" . $district .
4126                                    " ,name='" . $name . "'" .
4127                                    " ,email='" . $email . "'" .
4128                                    " ,president='" . $president . "'" .
4129                                    " ,counselor='" . $counselor . "'" .
4130                                    " ,secretary='" . $secretary . "'" .
4131                                    " ,eqpres='" . $eqpresidency . "'" .
4132                                    " WHERE presidency=" . $id,__LINE__,__FILE__);
4133
4134                } else {
4135                  //print "Adding New Entry<br>";
4136                  $this->db2->query("INSERT INTO tc_presidency (presidency,indiv,district,name,"
4137                                    . "email,president,counselor,secretary,eqpres,valid) "
4138                                    . "VALUES (NULL,'" . $indiv . "','" . $district . "','"
4139                                    . $name . "','" . $email . "','" . $president  . "','"
4140                                    . $counselor . "','" . $secretary . "','" . $eqpres  . "','1'"
4141                                    .")",__LINE__,__FILE__);
4142                }
4143              } else {
4144                //print "Ignoring Blank Entry<br>";
4145              }
4146            }
4147
4148           // Now update the tc_district table appropriately
4149
4150           // Delete all the previous district entries from the table
4151           $this->db->query("DELETE from tc_district where valid=1",__LINE__,__FILE__);
4152           $this->db->query("DELETE from tc_district where valid=0",__LINE__,__FILE__);
4153
4154           // Always add a "District 0" assigned to the High Priests Group
4155           $district = 0;
4156           $name = "High Priests";
4157           $indiv = 0;
4158           $valid = 0;
4159           $this->db2->query("INSERT INTO tc_district (district,name,supervisor,valid) "
4160                             . "VALUES ('" . $district . "','" . $name . "','"
4161                             . $indiv . "','" . $valid . "'"
4162                             .")",__LINE__,__FILE__);
4163
4164
4165           // Requery the tc_presidency table
4166           $sql = "SELECT * FROM tc_presidency where valid=1";
4167           $this->db->query($sql,__LINE__,__FILE__);
4168           while ($this->db->next_record())
4169             {
4170               // Extract the data for each presidency record
4171               $id = $this->db->f('presidency');
4172               $indiv = $this->db->f('indiv');
4173               $name = $this->db->f('name');
4174               $district = $this->db->f('district');
4175               $name = $this->db->f('name');
4176               $valid = 1;
4177
4178               // If we have a valid district, add it to the district table
4179               if($district > 0) {
4180                 $this->db2->query("INSERT INTO tc_district (district,name,supervisor,valid) "
4181                                   . "VALUES ('" . $district . "','" . $name . "','"
4182                                   . $indiv . "','" . $valid . "'"
4183                                   .")",__LINE__,__FILE__);
4184               }
4185
4186             }
4187
4188           $this->t->set_var('adminhandle','');
4189           $this->t->pfp('adminhandle','admin');
4190         }
4191       else
4192         {
4193           $this->t->set_var('adminhandle','');
4194           $this->t->pfp('adminhandle','admin');
4195         }
4196
4197       // Now save off the data needed for an EQ Presidency Table Update
4198
4199       $sql = "SELECT * FROM tc_presidency where valid=1";
4200       $this->db->query($sql,__LINE__,__FILE__);
4201       $table_data = "";
4202       $header_row = "<th>Individual</th><th>Email</th><th>District</th><th>President</th><th>Counselor</th><th>Secretary</th><th>Presidency</th>";
4203       while ($this->db->next_record())
4204         {
4205           // Extract the data for each presidency record
4206           $id = $this->db->f('presidency');
4207           $indiv = $this->db->f('indiv');
4208           $district = $this->db->f('district');
4209           $name = $this->db->f('name');
4210           $email = $this->db->f('email');
4211           $president = $this->db->f('president');
4212           $counselor = $this->db->f('counselor');
4213           $secretary = $this->db->f('secretary');
4214           $eqpresidency = $this->db->f('eqpres');
4215
4216           // Create the forms needed in the table
4217           $table_data .= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
4218
4219           // Presidency ID
4220           $table_data .= '<input type=hidden name="eqpres['.$id.'][id]" value="'.$id.'">';
4221
4222           // individual
4223           if($eqpresidency == 0) {
4224             $table_data.= '<td align=center><select name="eqpres['.$id.'][indiv]">';
4225             $table_data.= '<option value=0></option>';
4226             for ($j=0; $j < count($indiv_id); $j++) {
4227               $tmp_id = $indiv_id[$j];
4228               $name = $indiv_name[$j];
4229               if($indiv_id[$j] == $indiv) { $indivname = $name; $selected = 'selected="selected"'; } else { $selected = ''; }
4230               $table_data.= '<option value='.$tmp_id.' '.$selected.'>'.$name.'</option>';
4231             }
4232             $table_data.='</select></td>';
4233             $table_data.='<input type=hidden name="eqpres['.$id.'][name]" value="'.$indivname.'">';
4234           } else {
4235             $table_data.= '<td align=left><input type=text size="20" name="eqpresname" value="Presidency"></td>';
4236             $table_data.= '<input type=hidden name="eqpres['.$id.'][name]" value="Presidency">';
4237           }
4238
4239           // Email Address
4240           $table_data .= '<td><input type="text" size="50" name="eqpres['.$id.'][email]" value="'.$email.'"></td>';
4241
4242           // District
4243           $table_data.= '<td align=center><select name="eqpres['.$id.'][district]">';
4244           $table_data.= '<option value=0></option>';
4245           for ($j=0; $j <= $this->max_num_districts; $j++) {
4246             if($district == $j) { $selected = 'selected="selected"'; } else { $selected = ''; }
4247             $table_data.= '<option value='.$j.' '.$selected.'>'.$j.'</option>';
4248           }
4249           $table_data.='</select></td>';
4250
4251           // President
4252           $table_data.= '<td align=center><select name="eqpres['.$id.'][president]">';
4253           if($president == 1) { $table_data .= '<option value=0>0</option><option value=1 selected="selected">1</option>'; }
4254           else { $table_data .= '<option value=0 selected="selected">0</option><option value=1>1</option>'; }
4255           $table_data.='</select></td>';
4256
4257           // Counselor
4258           $table_data.= '<td align=center><select name="eqpres['.$id.'][counselor]">';
4259           if($counselor == 1) { $table_data .= '<option value=0>0</option><option value=1 selected="selected">1</option>'; }
4260           else { $table_data .= '<option value=0 selected="selected">0</option><option value=1>1</option>'; }
4261           $table_data.='</select></td>';
4262
4263           // Secretary
4264           $table_data.= '<td align=center><select name="eqpres['.$id.'][secretary]">';
4265           if($secretary == 1) { $table_data .= '<option value=0>0</option><option value=1 selected="selected">1</option>'; }
4266           else { $table_data .= '<option value=0 selected="selected">0</option><option value=1>1</option>'; }
4267           $table_data.='</select></td>';
4268
4269           // Presidency
4270           $table_data.= '<td align=center><select name="eqpres['.$id.'][eqpresidency]">';
4271           if($eqpresidency == 1) { $table_data .= '<option value=0>0</option><option value=1 selected="selected">1</option>'; }
4272           else { $table_data .= '<option value=0 selected="selected">0</option><option value=1>1</option>'; }
4273           $table_data.='</select></td>';
4274
4275           // End of ROW
4276           $table_data .= "</tr>\n";
4277           $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
4278           $this->t->set_var('tr_color',$tr_color);
4279         }
4280
4281       // Now create 1 blank row to always have a line available to add a new individual with
4282       $id = $this->max_presidency_members;
4283       $table_data .= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
4284       // Presidency ID
4285       $table_data .= '<input type=hidden name="eqpres['.$id.'][id]" value="'.$id.'">';
4286       // individual
4287       $table_data.= '<td align=center><select name="eqpres['.$id.'][indiv]">';
4288       $table_data.= '<option value=0></option>';
4289       for ($j=0; $j < count($indiv_id); $j++) {
4290         $tmp_id = $indiv_id[$j];
4291         $name = $indiv_name[$j];
4292         $table_data.= '<option value='.$tmp_id.'>'.$name.'</option>';
4293       }
4294       $table_data.='</select></td>';
4295       $table_data.='<input type=hidden name="eqpres['.$id.'][name]" value="">';
4296       // Email Address
4297       $table_data.='<td><input type="text" size="50" name="eqpres['.$id.'][email]" value=""></td>';
4298       // District
4299       $table_data.= '<td align=center><select name="eqpres['.$id.'][district]">';
4300       $table_data.= '<option value=0></option>';
4301       for ($j=0; $j <= $this->max_num_districts; $j++) {
4302         if($j == 0) { $selected = 'selected="selected"'; } else { $selected = ''; }
4303         $table_data.= '<option value='.$j.' '.$selected.'>'.$j.'</option>';
4304       }
4305       $table_data.='</select></td>';
4306       // President
4307       $table_data.= '<td align=center><select name="eqpres['.$id.'][president]">';
4308       $table_data.= '<option value=0>0</option><option value=1>1</option>';
4309       $table_data.='</select></td>';
4310       // Counselor
4311       $table_data.= '<td align=center><select name="eqpres['.$id.'][counselor]">';
4312       $table_data.= '<option value=0>0</option><option value=1>1</option>';
4313       $table_data.='</select></td>';
4314       // Secretary
4315       $table_data.= '<td align=center><select name="eqpres['.$id.'][secretary]">';
4316       $table_data.= '<option value=0>0</option><option value=1>1</option>';
4317       $table_data.='</select></td>';
4318       // Presidency
4319       $table_data.= '<td align=center><select name="eqpres['.$id.'][eqpresidency]">';
4320       $table_data.= '<option value=0>0</option><option value=1>1</option>';
4321       $table_data.='</select></td>';
4322       // End of ROW
4323       $table_data .= "</tr>\n";
4324       $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
4325       $this->t->set_var('tr_color',$tr_color);
4326
4327       $this->t->set_var('header_row',$header_row);
4328       $this->t->set_var('table_data',$table_data);
4329       $this->t->pfp('presidencyhandle','presidency',True);
4330
4331       $this->save_sessiondata();
4332     }
4333
4334   function email_appt($appointment)
4335     {
4336       //print "Emailing notification of appointment: $appointment <br>";
4337
4338       $sql = "SELECT * FROM tc_appointment where appointment='$appointment'";
4339       $this->db->query($sql,__LINE__,__FILE__);
4340
4341       while ($this->db->next_record())
4342         {
4343           $appointment = $this->db->f('appointment');
4344           $presidency = $this->db->f('presidency');
4345           $location = $this->db->f('location');
4346           $interviewer = "";
4347           $email = "";
4348           $indiv = $this->db->f('indiv');
4349           $indiv_name = "";
4350           $family = $this->db->f('family');
4351           $family_name = "";
4352           $appt_name = "";
4353           $phone = "";
4354           $uid = $this->db->f('uid');
4355
4356           // Extract the year, month, day, hours, minutes, seconds from the appointment time
4357           $appt_date = $this->db->f('date');
4358           $date_array = explode("-",$appt_date);
4359           $year = $date_array[0]; $month = $date_array[1]; $day = $date_array[2];
4360           $appt_time = $this->db->f('time');
4361           $time_array = explode(":",$appt_time);
4362           $hour = $time_array[0]; $minute = $time_array[1]; $seconds = $time_array[2];
4363
4364           // Format the appointment time into an iCal UTC equivalent
4365           $dtstamp = gmdate("Ymd"."\T"."His"."\Z");
4366           $dtstart = gmdate("Ymd"."\T"."His"."\Z", mktime($hour,$minute,$seconds,$month,$day,$year));
4367           $dtstartstr = date("l, F d, o g:i A", mktime($hour,$minute,$seconds,$month,$day,$year));
4368
4369           $sql = "SELECT * FROM tc_presidency where presidency='$presidency'";
4370           $this->db2->query($sql,__LINE__,__FILE__);
4371           if($this->db2->next_record()) {
4372             $email = $this->db2->f('email');
4373             $interviewer = $this->db2->f('name');
4374           }
4375
4376           // Set the email address of the interviewer
4377           $from = $email;
4378
4379           if($indiv > 0) {
4380             $sql = "SELECT * FROM tc_indiv where indiv='$indiv'";
4381             $this->db2->query($sql,__LINE__,__FILE__);
4382             if($this->db2->next_record()) {
4383               $indiv_name = $this->db2->f('name');
4384               $phone = $this->db2->f('phone');
4385               $appt_name = $indiv_name . " Interview";
4386               $duration = $this->default_ppi_appt_duration * 60;
4387             }
4388           }
4389
4390           if($family > 0) {
4391             $sql = "SELECT * FROM tc_family where family='$family'";
4392             $this->db2->query($sql,__LINE__,__FILE__);
4393             if($this->db2->next_record()) {
4394               $family_name = $this->db2->f('name');
4395               $phone = $this->db2->f('phone');
4396               $indiv_id = $this->db2->f('indiv_id');
4397               $appt_name = $family_name . " Family Visit";
4398               $sql = "SELECT * FROM tc_indiv where indiv='$indiv_id'";
4399               $this->db3->query($sql,__LINE__,__FILE__);
4400               if($this->db3->next_record()) {
4401                 $phone = $this->db3->f('phone');
4402               }
4403               $duration = $this->default_visit_appt_duration * 60;
4404             }
4405           }
4406
4407           $dtend = gmdate("Ymd"."\T"."His"."\Z", mktime($hour,$minute,$seconds+$duration,$month,$day,$year));
4408           $dtendstr = date("g:i A", mktime($hour,$minute,$seconds+$duration,$month,$day,$year));
4409           $date = $dtstartstr . "-" . $dtendstr;
4410           $description = "$appt_name : $phone";
4411
4412           if(($uid == 0) && ($appt_name != "")) {
4413             // Create a new calendar item for this appointment, since this must be the first time we
4414             // are sending it out.
4415             print "Sent new appointment to " . $interviewer . " at " . $email . " for " . $appt_name . "<br>";
4416             $uid = rand() . rand(); // Generate a random identifier for this appointment
4417             $subject = "Created: $appt_name";
4418
4419             $this->db->query("UPDATE tc_appointment set" .
4420                              " uid=" . $uid .
4421                              " WHERE appointment=" . $appointment,__LINE__,__FILE__);
4422
4423             $action = "PUBLISH";
4424             $this->send_ical_appt($action, $email, $from, $subject, $dtstamp, $dtstart,
4425                                   $dtend, $date, $location, $appt_name, $description, $uid);
4426
4427           } else if(($uid != 0) && ($appt_name == "")) {
4428             // Remove the calendar item for this appointment since it has already been sent
4429             // and there is no name we have changed it to.
4430             print "Sent deleted appointment to " . $interviewer . " at " . $email . " for " . $appt_date . " " . $appt_time . "<br>";
4431             $subject = "Canceled: $appt_date $appt_time";
4432
4433             $this->db->query("UPDATE tc_appointment set" .
4434                              " uid=0" .
4435                              " WHERE appointment=" . $appointment,__LINE__,__FILE__);
4436
4437             $action = "CANCEL";
4438             $this->send_ical_appt($action, $email, $from, $subject, $dtstamp, $dtstart,
4439                                   $dtend, $date, $location, $subject, $subject, $uid);
4440
4441           } else if($uid != 0) {
4442             // Update the existing appointment since we have changed it
4443             print "Sent updated appointment to " . $interviewer . " at " . $email . " for " . $appt_name . "<br>";
4444
4445             $subject = "Canceled: $appt_date $appt_time";
4446             $action = "CANCEL";
4447             $this->send_ical_appt($action, $email, $from, $subject, $dtstamp, $dtstart,
4448                                   $dtend, $date, $location, $subject, $subject, $uid);
4449
4450             $uid = rand() . rand(); // Generate a random identifier for this appointment
4451             $this->db->query("UPDATE tc_appointment set" .
4452                              " uid=" . $uid .
4453                              " WHERE appointment=" . $appointment,__LINE__,__FILE__);
4454
4455             $subject = "Updated: $appt_name";
4456             $action = "PUBLISH";
4457             $this->send_ical_appt($action, $email, $from, $subject, $dtstamp, $dtstart,
4458                                   $dtend, $date, $location, $appt_name, $description, $uid);
4459           }
4460
4461         }
4462
4463       return true;
4464     }
4465
4466   function send_ical_appt($action, $to, $from, $subject, $dtstamp, $dtstart, $dtend, $date, $location, $summary, $description, $uid)
4467     {
4468       // Initialize our local variables
4469       $boundary = "=MIME_APPOINTMENT_BOUNDARY";
4470       $message = "";
4471       $headers = "";
4472
4473       // Form the headers for the email message
4474       $headers.="X-Mailer: PHP/" . phpversion() . "\n";
4475       $headers.="Mime-Version: 1.0\n";
4476       $headers.="Content-Type: multipart/mixed; boundary=\"$boundary\"\n";
4477       $headers.="Content-Disposition: inline\n";
4478       $headers.="Reply-To: $from\n";
4479       $headers.="From: $from\n";
4480
4481       // Print the plaintext version of the appointment
4482       $message.="--$boundary\n";
4483       $message.="Content-Type: text/plain; charset=us-ascii\n";
4484       $message.="Content-Disposition: inline\n";
4485       $message.="\n";
4486       $message.="What: $description\n";
4487       $message.="When: $date\n";
4488       $message.="Where: $location\n";
4489       $message.="\n";
4490
4491       // Print the .ics attachment version of the appointment
4492       $message.="--$boundary\n";
4493       $message.="Content-Type: text/calendar; charset=us-ascii\n";
4494       $message.="Content-Disposition: attachment; filename=\"appointment.ics\"\n";
4495       $message.="\n";
4496       $message.="BEGIN:VCALENDAR" . "\n";
4497       $message.="VERSION:2.0" . "\n";
4498       $message.="PRODID:-//Microsoft Corporation//Outlook 11.0 MIMEDIR//EN" . "\n";
4499       $message.="METHOD:$action" . "\n";
4500       $message.="BEGIN:VEVENT" . "\n";
4501       $message.="ORGANIZER:MAILTO:$from". "\n";
4502       $message.="DTSTAMP:$dtstamp" . "\n";
4503       $message.="DTSTART:$dtstart" . "\n";
4504       $message.="DTEND:$dtend" . "\n";
4505       $message.="SUMMARY:$summary" . "\n";
4506       $message.="DESCRIPTION:$description" . "\n";
4507       $message.="LOCATION:$location" . "\n";
4508       $message.="UID:$uid" ."\n";
4509       $message.="TRANSP:OPAQUE" . "\n";
4510       $message.="SEQUENCE:0" . "\n";
4511       $message.="CLASS:PUBLIC" . "\n";
4512       $message.="END:VEVENT" . "\n";
4513       $message.="END:VCALENDAR" . "\n";
4514
4515       // Complete the message
4516       $message.="--$boundary\n";
4517
4518       // Send the message
4519       mail($to, $subject, $message, $headers);
4520
4521     }
4522
4523   function get_time_selection_form($hour, $minute, $pm, $presidency, $appointment)
4524     {
4525       $form_data = "";
4526       $blank = 0;
4527
4528       if($hour == 0) { $blank = 1; }
4529
4530       if($this->time_drop_down_lists == 1) {
4531         // Create drop down lists to get the time
4532         $form_data.= '<select name=sched['.$presidency.']['.$appointment.'][hour]>';
4533         if($blank == 1) { $form_data.= '<option value=""></option>'; }
4534         foreach(range(1,12) as $num) {
4535           if($hour == $num) { $selected = 'selected="selected"'; } else { $selected = ''; }
4536           $form_data.= '<option value='.$num.' '.$selected.'>'.$num.'</option>';
4537         }
4538         $form_data.= '</select>';
4539         $form_data.= '&nbsp;:&nbsp;';
4540         $form_data.= '<select name=sched['.$presidency.']['.$appointment.'][minute]>';
4541         if($blank == 1) { $form_data.= '<option value=""></option>'; }
4542         $num = 0;
4543         while($num < 60) {
4544           if($num < 10) { $num = "0" . "$num"; }
4545           if($minute == $num) { $selected = 'selected="selected"'; } else { $selected = ''; }
4546           if($blank == 1) { $selected = ""; }
4547           $form_data.= '<option value='.$num.' '.$selected.'>'.$num.'</option>';
4548           $num = $num + $this->time_drop_down_list_inc;
4549         }
4550         $form_data.= '</select>';
4551       } else {
4552         // Use free form text fields to get the time
4553         if($blank == 1) { $hour = ""; $minute = ""; $ampm = ""; }
4554         $form_data.= '<input type=text size=2 name=sched['.$presidency.']['.$appointment.'][hour] value='.$hour.'>';
4555         $form_data.= ':';
4556         $form_data.= '<input type=text size=2 name=sched['.$presidency.']['.$appointment.'][minute] value='.$minute.'>';
4557         $form_data.= '&nbsp;';
4558       }
4559       // Always use a drop-down select form for am/pm
4560       $form_data.= '<select name=sched['.$presidency.']['.$appointment.'][pm]>';
4561       if($blank == 0) {
4562         if($pm == 0) { $form_data.= '<option value=0 selected>am</option>'; $form_data.= '<option value=1>pm</option>'; }
4563         if($pm == 1) { $form_data.= '<option value=0>am</option>'; $form_data.= '<option value=1 selected>pm</option>'; }
4564       } else {
4565         $form_data.= '<option value=""></option>';
4566         $form_data.= '<option value=0>am</option>';
4567         $form_data.= '<option value=1>pm</option>';
4568       }
4569       $form_data.= '</select>';
4570
4571       return $form_data;
4572     }
4573 }
4574
4575 ?>