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       $sql = "SELECT * FROM tc_indiv where valid=1";
1008       $this->db->query($sql,__LINE__,__FILE__);
1009       $i=0;
1010       while ($this->db->next_record())
1011         {
1012           $indiv_name[$i] = $this->db->f('name');
1013           $indiv_id[$i] = $this->db->f('indiv');
1014           $i++;
1015         }
1016       array_multisort($indiv_name, $indiv_id);
1017
1018       $sql = "SELECT * FROM tc_activity ORDER BY date DESC";
1019       $this->db->query($sql,__LINE__,__FILE__);
1020       $total_records = $this->db->num_rows();
1021
1022       $i = 0;
1023       while ($this->db->next_record())
1024         {
1025           $activity_list[$i]['assignment'] = $this->db->f('assignment');
1026           $activity_list[$i]['date'] = $this->db->f('date');
1027           $activity_list[$i]['activity']  = $this->db->f('activity');
1028           $i++;
1029         }
1030
1031       $sql = "SELECT * FROM tc_assignment ORDER BY name ASC";
1032       $this->db->query($sql,__LINE__,__FILE__);
1033       $i=0;
1034       while($this->db->next_record())
1035         {
1036           $assignment_list[$i]['assignment'] = $this->db->f('assignment');
1037           $assignment_list[$i]['name'] = $this->db->f('name');
1038           $assignment_list[$i]['code'] = $this->db->f('code');
1039           $i++;
1040         }
1041
1042       $indiv_width=300; $part_width=25; $assignment_width=50;
1043       $total_width=$indiv_width+$part_width;
1044       for ($i=0; $i < count($assignment_list); $i++) {
1045         $this->t->set_var('assignment_name',$assignment_list[$i]['name']);
1046         $this->t->set_var('assignment_code',$assignment_list[$i]['code']);
1047         $this->t->fp('list1','header_list',True);
1048         $total_width += $assignment_width;
1049       }
1050
1051       for ($i=0; $i < count($indiv_id); $i++) {
1052         $participated=0; $part_table = '';
1053         $this->nextmatchs->template_alternate_row_color(&$this->t);
1054         $this->t->set_var('indiv_name',$indiv_name[$i]);
1055         for ($j=0; $j < count($assignment_list); $j++) {
1056           $date = "0000-00-00"; $checkmark=0; $num_matches=0;
1057           for ($k=0; $k < count($activity_list); $k++) {
1058             if($assignment_list[$j]['assignment'] == $activity_list[$k]['assignment']) {
1059               $sql = "SELECT * FROM tc_participation where "
1060                  . " activity=" . $activity_list[$k]['activity']
1061                  . " AND indiv=" . $indiv_id[$i];
1062               $this->db->query($sql,__LINE__,__FILE__);
1063               while($this->db->next_record()) {
1064                 if($activity_list[$k]['date'] > $date) {
1065                   $date = $activity_list[$k]['date'];
1066                 }
1067                 $checkmark=1;
1068                 $num_matches++;
1069                 $participated++;
1070               }
1071             }
1072           }
1073           if($checkmark) {
1074             $part_table .= '<td align=center><img src="images/checkmark.gif">';
1075             $part_table .= '<font size=-2>'.$num_matches.'</font><br>';
1076             $part_table .= '<font size=-2>'.$date.'</font></td>';
1077           } else {
1078             $part_table .= '<td>&nbsp;</td>';
1079           }
1080         }
1081         if($participated) { $part_table .= '<td align=center><img src="images/checkmark.gif">'.$participated.'</td>'; }
1082         else { $part_table .= '<td>&nbsp;</td>'; }
1083         $this->t->set_var('part_table',$part_table);
1084         $this->t->fp('list2','indiv_list',True);
1085       }
1086       $this->t->set_var('total_width',$total_width);
1087       $this->t->set_var('indiv_width',$indiv_width);
1088       $this->t->set_var('part_width',$part_width);
1089       $this->t->set_var('act_width',$act_width);
1090       $this->t->pfp('out','par_view_t');
1091       $this->save_sessiondata();
1092     }
1093
1094     function willing_view()
1095     {
1096       $this->t->set_file(array('willing_view_t' => 'willing_view.tpl'));
1097       $this->t->set_block('willing_view_t','header_list','list1');
1098       $this->t->set_block('willing_view_t','indiv_list','list2');
1099
1100       $this->t->set_var('lang_filter','Filter');
1101       $this->t->set_var('lang_filter_unwilling','Filter out unwilling individuals:');
1102
1103       $filter_unwilling = get_var('filter_unwilling',array('POST'));
1104       $this->t->set_var('filter_unwilling',$filter_unwilling);
1105
1106       if($filter_unwilling == 'y' || $filter_unwilling == '') {
1107         $filter_input = "<input type=\"radio\" name=\"filter_unwilling\" value=\"y\" checked>Y";
1108         $filter_input.= "<input type=\"radio\" name=\"filter_unwilling\" value=\"n\">N";
1109         $filter_input.= "&nbsp;&nbsp;";
1110       } else {
1111         $filter_input = "<input type=\"radio\" name=\"filter_unwilling\" value=\"y\">Y";
1112         $filter_input.= "<input type=\"radio\" name=\"filter_unwilling\" value=\"n\" checked>N";
1113         $filter_input.= "&nbsp;&nbsp;";
1114       }
1115       $this->t->set_var('filter_input',$filter_input);
1116
1117       $sql = "SELECT * FROM tc_indiv where valid=1";
1118       $this->db->query($sql,__LINE__,__FILE__);
1119       $i=0;
1120       while ($this->db->next_record())
1121         {
1122           $indiv_name[$i] = $this->db->f('name');
1123           $indiv_id[$i] = $this->db->f('indiv');
1124           $indiv_phone[$indiv_id[$i]] = $this->db->f('phone');
1125           $i++;
1126         }
1127       array_multisort($indiv_name, $indiv_id);
1128
1129       $sql = "SELECT * FROM tc_assignment ORDER BY name ASC";
1130       $this->db->query($sql,__LINE__,__FILE__);
1131       $i=0;
1132       while($this->db->next_record())
1133         {
1134           $assignment_list[$i]['assignment'] = $this->db->f('assignment');
1135           $assignment_list[$i]['name'] = $this->db->f('name');
1136           $assignment_list[$i]['code'] = $this->db->f('code');
1137           $i++;
1138         }
1139
1140       $sql = "SELECT * FROM tc_activity ORDER BY date DESC";
1141       $this->db->query($sql,__LINE__,__FILE__);
1142       $total_records = $this->db->num_rows();
1143
1144       $i = 0;
1145       while ($this->db->next_record())
1146         {
1147           $activity_list[$i]['assignment'] = $this->db->f('assignment');
1148           $activity_list[$i]['date'] = $this->db->f('date');
1149           $activity_list[$i]['activity']  = $this->db->f('activity');
1150           $i++;
1151         }
1152
1153       $indiv_width=275; $willing_width=40; $assignment_width=50;
1154       $total_width=$indiv_width+$willing_width;
1155
1156       for ($i=0; $i < count($assignment_list); $i++) {
1157         $this->t->set_var('assignment_name',$assignment_list[$i]['name']);
1158         $this->t->set_var('assignment_code',$assignment_list[$i]['code']);
1159         $this->t->fp('list1','header_list',True);
1160         $total_width += $assignment_width;
1161         $total_willing[$i] = 0;
1162       }
1163
1164       for ($i=0; $i < count($indiv_id); $i++) {
1165         $willing_table = ''; $indiv_willing=0;
1166         $this->t->set_var('indiv_name',$indiv_name[$i]);
1167         $this->t->set_var('indiv_phone',$indiv_phone[$indiv_id[$i]]);
1168         $this->t->set_var('editurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.willing_update&indiv_id='
1169                                                             . $indiv_id[$i] . '&action=' . 'edit'));
1170         for ($j=0; $j < count($assignment_list); $j++) {
1171           $found_willingness=0;
1172           $sql = "SELECT * FROM tc_willingness where "
1173              . " assignment=" . $assignment_list[$j]['assignment']
1174              . " AND indiv=" . $indiv_id[$i];
1175           $this->db->query($sql,__LINE__,__FILE__);
1176           while($this->db->next_record()) {
1177             $found_willingness=1;
1178             $date_part="";
1179             $sql = "SELECT * FROM tc_activity where "
1180                . " assignment=". $assignment_list[$j]['assignment']
1181                . " ORDER by date DESC";
1182             $this->db2->query($sql,__LINE__,__FILE__);
1183             if($this->db2->next_record()) {
1184               $activity = $this->db2->f('activity');
1185               $date = $this->db2->f('date');
1186               $sql = "SELECT * FROM tc_participation where "
1187                  . " activity=" . $activity
1188                  . " AND indiv=". $indiv_id[$i];
1189               $this->db3->query($sql,__LINE__,__FILE__);
1190               if($this->db3->next_record()) {
1191                 $date_part = $date;
1192               }
1193             }
1194
1195             if($this->db->f('willing') == 'y') {
1196               $total_willing[$j]++;
1197               $indiv_willing=1;
1198               $willing_table .= '<td align=center><img src="images/checkmark.gif"><br><font size=-2>'.$date_part.'</font></td></td>';
1199             }
1200             else if($this->db->f('willing') == 'n') {
1201               $willing_table .= '<td align=center><img src="images/x.gif"></td>';
1202             }
1203             else {
1204               $indiv_willing=1;
1205               $willing_table .= "<td>&nbsp;</td>";
1206             }
1207           }
1208           if(!$found_willingness) {
1209             $indiv_willing=1;
1210             $willing_table .= "<td>&nbsp;</td>";
1211           }
1212         }
1213         if(($indiv_willing == 1) || ($filter_unwilling == 'n')) {
1214           $this->t->set_var('willing_table',$willing_table);
1215           $this->t->fp('list2','indiv_list',True);
1216           $this->nextmatchs->template_alternate_row_color(&$this->t);
1217         }
1218       }
1219
1220       $stat_table = '<td><b>Total Willing to Serve</b></td>';
1221       for ($j=0; $j < count($assignment_list); $j++) {
1222         $stat_table .= "<td align=center><b>".$total_willing[$j]."</b></td>";
1223       }
1224       $this->t->set_var('stat_table',$stat_table);
1225
1226       $this->t->set_var('total_width',$total_width);
1227       $this->t->set_var('indiv_width',$indiv_width);
1228       $this->t->set_var('willing_width',$willing_width);
1229       $this->t->pfp('out','willing_view_t');
1230       $this->save_sessiondata();
1231     }
1232
1233   function willing_update()
1234     {
1235       //print "<font color=red>Willingness Update Under Constrcution</font>";
1236       //$this->willing_view();
1237       //return false;
1238
1239       $this->t->set_file(array('willing_update_t' => 'willing_update.tpl'));
1240       $this->t->set_block('willing_update_t','assignment_list','list');
1241       $this->t->set_block('willing_update_t','save','savehandle');
1242
1243       $indiv_id = get_var('indiv_id',array('GET','POST'));
1244       $this->t->set_var('indiv_id',$indiv_id);
1245       $action = get_var('action',array('GET','POST'));
1246
1247       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.willing_view'));
1248       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.willing_update&action=save'));
1249       $this->t->set_var('lang_done','Cancel');
1250       $this->t->set_var('title','Willingness Update ');
1251
1252       if($action == 'save')
1253         {
1254           // Delete all the previous willingness entries for this individual
1255           $this->db->query("DELETE from tc_willingness where indiv=" . $indiv_id ,__LINE__,__FILE__);
1256
1257           // Now, add the assignment willingness that is checked for this individual
1258           $new_data = get_var('willingness',array('POST'));
1259           foreach ($new_data as $data)
1260             {
1261               $data_array = explode("/",$data);
1262               $assignment = $data_array[0];
1263               $willing = $data_array[1];
1264               //print "indiv_id: $indiv_id assignment: $assignment willing: $willing<br>";
1265               $this->db->query("INSERT INTO tc_willingness (indiv,assignment,willing) "
1266                                . "VALUES (" . $indiv_id .",". $assignment .",'". $willing . "')",__LINE__,__FILE__);
1267             }
1268           $this->willing_view();
1269           return false;
1270         }
1271
1272       $assignment_width=300; $willing_width=25; $table_width=$assignment_width + $willing_width;
1273       $table_data="";
1274
1275       // Find out the individual's name
1276       $sql = "SELECT * FROM tc_indiv WHERE indiv=".$indiv_id." AND valid=1";
1277       $this->db->query($sql,__LINE__,__FILE__);
1278       if($this->db->next_record()) {
1279         $indiv_name = $this->db->f('name');
1280         $this->t->set_var('indiv_name',$indiv_name);
1281       }
1282
1283       // Select all the assignments
1284       $sql = "SELECT * FROM tc_assignment ORDER by name ASC";
1285       $this->db->query($sql,__LINE__,__FILE__);
1286
1287       while ($this->db->next_record())
1288         {
1289           $assignment = $this->db->f('assignment');
1290           $assignment_name = $this->db->f('name');
1291           $assignment_code = $this->db->f('code');
1292
1293           $this->nextmatchs->template_alternate_row_color(&$this->t);
1294           $table_data.="<tr bgcolor=". $this->t->get_var('tr_color') ."><td>$assignment_name</td>";
1295
1296           $header_row="<th width=$comp_width><font size=-2>Assignments</th><th>Willingness</th>";
1297           $sql = "SELECT * FROM tc_willingness WHERE indiv=".$indiv_id." AND assignment=".$assignment;
1298           $this->db2->query($sql,__LINE__,__FILE__);
1299           $value = $assignment;
1300
1301           if($this->db2->next_record()) {
1302               if($this->db2->f('willing') == 'y') {
1303                 $table_data .= '<td width=100 align=center>';
1304                 $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/y" checked>Y';
1305                 $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/n">N';
1306                 $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/"> ';
1307                 $table_data .= '</td>';
1308               } else if($this->db2->f('willing') == 'n') {
1309                 $table_data .= '<td width=100 align=center>';
1310                 $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/y">Y';
1311                 $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/n" checked>N';
1312                 $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/">';
1313                 $table_data .= '</td>';
1314               } else {
1315                 $table_data .= '<td width=100 align=center>';
1316                 $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/y">Y';
1317                 $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/n">N';
1318                 $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/" checked> ';
1319                 $table_data .= '</td>';
1320               }
1321             }
1322           else {
1323             $table_data .= '<td width=100 align=center>';
1324             $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/y">Y';
1325             $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/n">N';
1326             $table_data .= '<input type="radio" name="willingness['.$assignment.']" value="'.$value.'/" checked> ';
1327             $table_data .= '</td>';
1328           }
1329
1330           $table_data .= "\n";
1331           $table_data .= "</tr>";
1332           $table_data .= "<tr><td colspan=20></td></tr>";
1333         }
1334
1335       $table_data .= "<tr><td colspan=20><hr></td></tr>";
1336
1337       $this->t->set_var('table_width',$table_width);
1338       $this->t->set_var('header_row',$header_row);
1339       $this->t->set_var('table_data',$table_data);
1340       $this->t->fp('list','assignment_list',True);
1341
1342       $this->t->set_var('lang_reset','Clear Form');
1343       $this->t->set_var('lang_save','Save Changes');
1344       $this->t->set_var('savehandle','');
1345
1346       $this->t->pfp('out','willing_update_t');
1347       $this->t->pfp('addhandle','save');
1348
1349       $this->save_sessiondata();
1350     }
1351
1352
1353     function ppi_sched()
1354       {
1355       $this->t->set_file(array('ppi_sched_t' => 'ppi_sched.tpl'));
1356       $this->t->set_block('ppi_sched_t','indiv_list','indivlist');
1357       $this->t->set_block('ppi_sched_t','appt_list','apptlist');
1358       $action = get_var('action',array('GET','POST'));
1359
1360       $this->t->set_var('lang_save','Save Appt / Pri / Notes');
1361       $this->t->set_var('lang_reset','Clear Changes');
1362
1363       $this->t->set_var('ppi_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_view'));
1364       $this->t->set_var('ppi_link_title','Yearly PPIs');
1365
1366       $this->t->set_var('schedule_ppi_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_sched'));
1367       $this->t->set_var('schedule_ppi_link_title','Schedule Yearly PPIs');
1368
1369       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_sched&action=save'));
1370       $this->t->set_var('title','Yearly PPI Scheduler');
1371
1372       $indiv_width=500; $phone_width=25; $pri_width=10; $notes_width=128; $ppi_date_width=20;
1373       $table_width=$indiv_width + $phone_width + $pri_width + $notes_width + $ppi_date_width;
1374       $header_row = "<th width=$indiv_width><font size=-2>individual Name</th>";
1375       $header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
1376       $header_row.= "<th width=$pri_width><font size=-2>Priority</th>";
1377       $header_row.= "<th width=$ppi_date_width><font size=-2>Last PPI</th>";
1378       $header_row.= "<th width=$notes_width><font size=-2>Scheduling Notes</th>";
1379       $table_data=""; $completed_data=""; $totals_data="";
1380
1381       $year = date('Y');
1382
1383       // Get the EQ President
1384       $sql = "SELECT * FROM tc_presidency where president=1 and valid=1";
1385       $this->db->query($sql,__LINE__,__FILE__);
1386       if($this->db->next_record()) {
1387         $president_name = $this->db->f('name');
1388         $president_name_array = explode(",",$president_name);
1389         $president_last_name = $president_name_array[0];
1390         $president_id = $this->db->f('indiv');
1391         $presidency_id = $this->db->f('presidency');
1392         $interviewer = $this->db->f('indiv');
1393         $district_number = '*';
1394         $district_name = $president_name;
1395         $sql = "SELECT * FROM tc_indiv where indiv='$president_id'";
1396         $this->db2->query($sql,__LINE__,__FILE__);
1397         if($this->db2->next_record()) {
1398           $indiv_id = $this->db2->f('indiv_id');
1399         }
1400         $sql = "SELECT * FROM tc_indiv where indiv_id='$indiv_id'";
1401         $this->db2->query($sql,__LINE__,__FILE__);
1402         if($this->db2->next_record()) {
1403           $president_address = $this->db2->f('address');
1404         }
1405       } else {
1406         print "<hr><font color=red><h3>-E- Unable to locate President in tc_presidency table</h3></font></hr>";
1407         return;
1408       }
1409
1410       if($action == 'save')
1411         {
1412           // Save any changes made to the appointment table
1413           $new_data = get_var('appt_notes',array('POST'));
1414           if($new_data != "") {
1415             foreach ($new_data as $entry)
1416               {
1417                 $indiv = $entry['indiv'];
1418                 $appointment = $entry['appointment'];
1419                 $location = $entry['location'];
1420                 if($location == "") { $location = "$president_last_name"." home ($president_address)"; }
1421                 if($indiv == 0) { $location = ""; }
1422
1423                 //Only perform a database update if we have made a change to this appointment
1424                 $sql = "SELECT * FROM tc_appointment where appointment='$appointment' and indiv='$indiv' and location='$location'";
1425                 $this->db->query($sql,__LINE__,__FILE__);
1426                 if(!$this->db->next_record()) {
1427                   // Perform database save actions here
1428                   $this->db->query("UPDATE tc_appointment set " .
1429                                    " indiv='" . $indiv . "'" .
1430                                    ",location='" . $location . "'" .
1431                                    " WHERE appointment=" . $appointment,__LINE__,__FILE__);
1432                   // Email the appointment
1433                   $this->email_appt($appointment);
1434                 }
1435
1436               }
1437           }
1438
1439           // Save any changes made to the ppi notes table
1440           $new_data = get_var('ppi_notes',array('POST'));
1441           foreach ($new_data as $entry)
1442            {
1443              $ppi_notes = $entry['notes'];
1444              $indiv_id = $entry['indiv_id'];
1445              $ppi_pri = $entry['pri'];
1446
1447              // Perform database save actions here
1448              $this->db->query("UPDATE tc_indiv set " .
1449                               " ppi_notes='" . $ppi_notes . "'" .
1450                               ",ppi_pri='" . $ppi_pri . "'" .
1451                               " WHERE indiv=" . $indiv_id,__LINE__,__FILE__);
1452
1453            }
1454
1455           $take_me_to_url = $GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_sched');
1456           //Header('Location: ' . $take_me_to_url);
1457         }
1458
1459       // create the individual id -> individual name mapping
1460       $sql = "SELECT * FROM tc_indiv where valid=1 ORDER BY name ASC";
1461       $this->db->query($sql,__LINE__,__FILE__);
1462       $i=0;
1463       $indiv_id = NULL;
1464       $indiv_name = NULL;
1465       while ($this->db->next_record())
1466         {
1467           $indiv_name[$i] = $this->db->f('name');
1468           $indiv_id[$i] = $this->db->f('indiv');
1469           $i++;
1470         }
1471       array_multisort($indiv_name, $indiv_id);
1472
1473       // APPOINTMENT TABLE
1474       $date_width=250; $time_width=100; $indiv_width=200; $location_width=100;
1475       $appt_table_width=$date_width + $time_width + $indiv_width + $location_width;
1476       $appt_header_row = "<th width=$date_width><font size=-2>Date</th>";
1477       $appt_header_row.= "<th width=$time_width><font size=-2>Time</th>";
1478       $appt_header_row.= "<th width=$indiv_width><font size=-2>indiv</th>";
1479       $appt_header_row.= "<th width=$location_width><font size=-2>Location</th>";
1480       $appt_table_data = "";
1481
1482       $total_indivs=0; $indivs_with_yearly_ppi=0;
1483
1484       // Display a scheduling table for the EQ President
1485       $table_data=""; $appt_table_data="";
1486       $table_title = "District ".$district_number.": ".$district_name.": All indivs with Yearly PPI Not Completed";
1487       $appt_table_title = "District ".$district_number.": ".$district_name.": Yearly PPI Appointment Slots";
1488       $this->t->set_var('table_title',$table_title);
1489       $this->t->set_var('appt_table_title',$appt_table_title);
1490
1491       // query the database for all the appointments
1492       $sql = "SELECT * FROM tc_appointment where presidency=".$presidency_id." and date>=CURDATE() ORDER BY date ASC, time ASC";
1493       $this->db->query($sql,__LINE__,__FILE__);
1494
1495       while ($this->db->next_record())
1496         {
1497           $appointment = $this->db->f('appointment');
1498           $indiv = $this->db->f('indiv');
1499           $location = $this->db->f('location');
1500           if(($location == "") && ($indiv > 0)) { $location = "$president_last_name"." home ($president_address)"; }
1501
1502           $date = $this->db->f('date');
1503           $date_array = explode("-",$date);
1504           $year = $date_array[0]; $month = $date_array[1]; $day = $date_array[2];
1505           $day_string = date("l d-M-Y", mktime(0,0,0,$month,$day,$year));
1506
1507           $time = $this->db->f('time');
1508           $time_array = explode(":",$time);
1509           $time_string = date("g:i a", mktime($time_array[0], $time_array[1], $time_array[2]));
1510
1511           $appt_table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1512           $appt_table_data.= "<td align=center>$day_string</td>";
1513           $appt_table_data.= "<td align=center>$time_string</td>";
1514
1515           $appt_table_data.= '<td align=center><select name=appt_notes['.$appointment.'][indiv]>';
1516           $appt_table_data.= '<option value=0></option>';
1517           for ($i=0; $i < count($indiv_id); $i++) {
1518             $id = $indiv_id[$i];
1519             $name = $indiv_name[$i];
1520             if($indiv_id[$i] == $indiv) { $selected[$id] = 'selected="selected"'; } else { $selected[$id] = ''; }
1521             $appt_table_data.= '<option value='.$id.' '.$selected[$id].'>'.$name.'</option>';
1522           }
1523           $appt_table_data.='</select></td>';
1524
1525           $appt_table_data.= '<td align=center><input type=text size="35" maxlength="120" ';
1526           $appt_table_data.= 'name="appt_notes['.$appointment.'][location]" value="'.$location.'">';
1527
1528           $appt_table_data.= '<input type=hidden name="appt_notes['.$appointment.'][appointment]" value="'.$appointment.'">';
1529
1530           $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1531           $this->t->set_var('tr_color',$tr_color);
1532         }
1533
1534       $this->t->set_var('appt_table_data',$appt_table_data);
1535       $this->t->set_var('appt_header_row',$appt_header_row);
1536       $this->t->set_var('appt_table_width',$appt_table_width);
1537
1538       // PPI SCHEDULING TABLE
1539       $sql = "SELECT * FROM tc_indiv where valid=1 ORDER BY ppi_pri ASC";
1540       $this->db->query($sql,__LINE__,__FILE__);
1541
1542       $i=0;
1543       $indiv_id = NULL;
1544       while ($this->db->next_record())
1545         {
1546           $indiv_id[$i] = $this->db->f('indiv');
1547           $indiv_name[$i] = $this->db->f('name');
1548           $indiv_phone[$indiv_id[$i]] = $this->db->f('phone');
1549           $indiv_ppi_pri[$indiv_id[$i]] = $this->db->f('ppi_pri');
1550           $indiv_ppi_notes[$indiv_id[$i]] = $this->db->f('ppi_notes');
1551           $i++;
1552           $total_indivs++;
1553         }
1554
1555       $max = count($indiv_id);
1556
1557       for($i=0; $i < $max; $i++) {
1558           $id = $indiv_id[$i];
1559           $name = $indiv_name[$i];
1560           $phone = $indiv_phone[$id];
1561           $ppi_pri = $indiv_ppi_pri[$id];
1562           $ppi_notes = $indiv_ppi_notes[$id];
1563
1564           // If this individual has had a yearly PPI this year, don't show him on the schedule list
1565           $year_start = $year - 1 . "-12-31"; $year_end = $year + 1 . "-01-01";
1566           $sql = "SELECT * FROM tc_interview WHERE date > '$year_start' AND date < '$year_end' ".
1567              "AND indiv=" . $id . " AND interview_type='ppi'";
1568           $this->db2->query($sql,__LINE__,__FILE__);
1569
1570           if(!$this->db2->next_record()) {
1571             $sql = "SELECT * FROM tc_interview WHERE indiv=" . $id . " AND interview_type='ppi' ORDER BY date DESC";
1572             $this->db->query($sql,__LINE__,__FILE__);
1573             if($this->db->next_record()) { $date = $this->db->f('date'); } else { $date = ""; }
1574             $link_data['menuaction'] = 'tc.tc.ppi_update';
1575             $link_data['indiv'] = $id;
1576             $link_data['name'] = $name;
1577             $link_data['interview'] = '';
1578             $link_data['interview_type'] = 1;
1579             $link_data['action'] = 'add';
1580             $link_data['interviewer'] = $interviewer;
1581             $link = $GLOBALS['phpgw']->link('/tc/index.php',$link_data);
1582             $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1583             $this->t->set_var('tr_color',$tr_color);
1584             $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
1585             $table_data.= "<td align=center>$phone</td>";
1586             //$table_data.= "<td align=center>$ppi_pri</td>";
1587             $table_data.= "<td align=center>";
1588             $table_data.= '<select name=ppi_notes['.$i.'][pri]>';
1589             foreach(range(0,6) as $num) {
1590               if($num == 0) { $num = 1; } else {$num = $num*5; }
1591               if($ppi_pri == $num) { $selected[$num] = 'selected="selected"'; } else { $selected[$num] = ''; }
1592               $table_data.= '<option value='.$num.' '.$selected[$num].'>'.$num.'</option>';
1593             }
1594             $table_data.= '</select></td>';
1595             $table_data.= "<td align=center>$date</td>";
1596             $table_data.= '<td><input type=text size="50" maxlength="128" name="ppi_notes['.$i.'][notes]" value="'.$ppi_notes.'">';
1597             $table_data.= '<input type=hidden name="ppi_notes['.$i.'][indiv_id]" value="'.$id.'">';
1598             $table_data.= '<input type=hidden name="ppi_notes['.$i.'][indiv_name]" value="'.$name.'">';
1599             $table_data.= '</td>';
1600             $table_data.= '</tr>';
1601           } else {
1602             $link_data['menuaction'] = 'tc.tc.ppi_update';
1603             $link_data['interviewer'] = $this->db2->f('interviewer');
1604             $link_data['indiv'] = $this->db2->f('indiv');
1605             $link_data['name'] = $name;
1606             $link_data['interview'] = $this->db2->f('interview');
1607             $link_data['interview_type'] = $this->db2->f('interview_type');
1608             $link_data['action'] = 'view';
1609             $link = $GLOBALS['phpgw']->link('/tc/index.php',$link_data);
1610             $indivs_with_yearly_ppi++;
1611             $date = $this->db2->f('date');
1612             $ppi_notes = $this->db2->f('notes');
1613             if(strlen($ppi_notes) > 40) { $ppi_notes = substr($ppi_notes,0,40) . "..."; }
1614             $tr_color2 = $this->nextmatchs->alternate_row_color($tr_color2);
1615             $this->t->set_var('tr_color2',$tr_color2);
1616             $completed_data.= "<tr bgcolor=". $this->t->get_var('tr_color2') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
1617             $completed_data.= "<td align=center>$phone</td>";
1618             $completed_data.= "<td align=center><a href=".$link.">$date</a></td>";
1619             $completed_data.= "<td align=left>$ppi_notes</td>";
1620             $completed_data.= '</tr>';
1621           }
1622       } // End for individuals Loop
1623
1624       $name_width=175; $phone_width=100; $date_width=100; $notes_width=300;
1625       $completed_table_width=$name_width + $phone_width + $date_width + $notes_width;
1626       $completed_header_row = "<th width=$name_width><font size=-2>Individual Name</th>";
1627       $completed_header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
1628       $completed_header_row.= "<th width=$date_width><font size=-2>Date</th>";
1629       $completed_header_row.= "<th width=$notes_width><font size=-2>PPI Notes</th>";
1630
1631       $this->t->set_var('table_width',$table_width);
1632       $this->t->set_var('header_row',$header_row);
1633       $this->t->set_var('table_data',$table_data);
1634       $this->t->set_var('completed_header_row',$completed_header_row);
1635       $this->t->set_var('completed_table_width',$completed_table_width);
1636       $this->t->set_var('completed',$completed_data);
1637       $this->t->fp('indivlist','indiv_list',True);
1638
1639       $indivs_width=300; $totals_width=100;
1640       $totals_table_width=$indivs_width + $totals_width;
1641       $totals_header_row = "<th width=$indivs_width><font size=-2>Individuals</th>";
1642       $totals_header_row.= "<th width=$totals_width><font size=-2>$year</th>";
1643       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1644       $totals_data.= "<td align=left><font size=-2><b>Total Individuals with yearly PPIs completed:</b></font></td>";
1645       $totals_data.= "<td align=center><font size=-2><b>$indivs_with_yearly_ppi / $total_indivs</b></font></td>";
1646       $percent = ceil(($indivs_with_yearly_ppi / $total_indivs)*100);
1647       $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1648       $this->t->set_var('tr_color',$tr_color);
1649       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1650       $totals_data.= "<td align=left><font size=-2><b>Percentage:</b></font></td>";
1651       $totals_data.= "<td align=center><font size=-2><b>$percent%</b></font></td>";
1652       $totals_data.= "</tr>";
1653
1654       $this->t->set_var('totals',$totals_data);
1655       $this->t->set_var('totals_header_row',$totals_header_row);
1656       $this->t->set_var('totals_table_width',$totals_table_width);
1657
1658       $this->t->pfp('out','ppi_sched_t');
1659       $this->save_sessiondata();
1660
1661     }
1662
1663   function int_sched()
1664     {
1665       $this->t->set_file(array('int_sched_t' => 'int_sched.tpl'));
1666       $this->t->set_block('int_sched_t','indiv_list','indivlist');
1667       $this->t->set_block('int_sched_t','appt_list','apptlist');
1668       $action = get_var('action',array('GET','POST'));
1669
1670       $this->t->set_var('lang_save','Save Appt / Pri / Notes');
1671       $this->t->set_var('lang_reset','Clear Changes');
1672
1673       $this->t->set_var('int_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_view'));
1674       $this->t->set_var('int_link_title','Hometeaching Interviews');
1675
1676       $this->t->set_var('schedule_int_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_sched'));
1677       $this->t->set_var('schedule_int_link_title','Schedule Hometeaching Interviews');
1678
1679       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_sched&action=save'));
1680       $this->t->set_var('title','Hometeaching Interviews Scheduler');
1681
1682       $indiv_width=500; $phone_width=25; $pri_width=10; $notes_width=128; $int_date_width=20;
1683       $table_width=$indiv_width + $phone_width + $pri_width + $notes_width + $int_date_width;
1684       $header_row = "<th width=$indiv_width><font size=-2>individual Name</th>";
1685       $header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
1686       $header_row.= "<th width=$pri_width><font size=-2>Priority</th>";
1687       $header_row.= "<th width=$int_date_width><font size=-2>Last Interview</th>";
1688       $header_row.= "<th width=$notes_width><font size=-2>Scheduling Notes</th>";
1689       $table_data=""; $completed_data=""; $totals_data="";
1690
1691       $year = date('Y');
1692       $month = date('m');
1693       $nextyear = $year + 1;
1694       if($month >= 1 && $month <= 3) { $quarter_start=$year."-01-01"; $quarter_end=$year."-04-01"; }
1695       if($month >= 4 && $month <= 6) { $quarter_start=$year."-04-01"; $quarter_end=$year."-07-01"; }
1696       if($month >= 7 && $month <= 9) { $quarter_start=$year."-07-01"; $quarter_end=$year."-10-01"; }
1697       if($month >= 10 && $month <= 12) { $quarter_start=$year."-10-01"; $quarter_end=$nextyear."-01-01"; }
1698       //print "year: $year month: $month quarter_start: $quarter_start quarter_end: $quarter_end<br>";
1699
1700       // create the individual id -> individual name mapping
1701       $sql = "SELECT * FROM tc_indiv where valid=1 ORDER BY name ASC";
1702       $this->db->query($sql,__LINE__,__FILE__);
1703       $i=0;
1704       $indiv_id_data = NULL;
1705       $indiv_name_data = NULL;
1706       while ($this->db->next_record())
1707         {
1708           $indiv_name_data[$i] = $this->db->f('name');
1709           $indiv_id_data[$i] = $this->db->f('indiv');
1710           $individ2name[$indiv_id_data[$i]] = $indiv_name_data[$i];
1711           $i++;
1712         }
1713       array_multisort($indiv_name_data, $indiv_id_data);
1714
1715       if($action == 'save')
1716         {
1717           // Save any changes made to the appointment table
1718           $new_data = get_var('appt_notes',array('POST'));
1719           if($new_data != "") {
1720             foreach ($new_data as $entry)
1721               {
1722                 $indiv = $entry['indiv'];
1723                 $appointment = $entry['appointment'];
1724                 $location = $entry['location'];
1725                 if($location == "") {
1726                   $supervisor = $entry['supervisor'];
1727                   $supervisor_array = explode(",", $individ2name[$supervisor]);
1728                   $supervisor_last_name = $supervisor_array[0];
1729                   $sql = "SELECT * FROM tc_indiv where indiv='$supervisor'";
1730                   $this->db2->query($sql,__LINE__,__FILE__);
1731                   if($this->db2->next_record()) {
1732                     $indiv_id = $this->db2->f('indiv_id');
1733                   }
1734                   $sql = "SELECT * FROM tc_indiv where indiv_id='$indiv_id'";
1735                   $this->db2->query($sql,__LINE__,__FILE__);
1736                   if($this->db2->next_record()) {
1737                     $supervisor_address = $this->db2->f('address');
1738                   }
1739                   $location = "$supervisor_last_name"." home ($supervisor_address)";
1740                 }
1741                 if($indiv == 0) { $location = ""; }
1742
1743                 //print "indiv: $indiv appointment: $appointment <br>";
1744                 //Only perform a database update if we have made a change to this appointment
1745                 $sql = "SELECT * FROM tc_appointment where appointment='$appointment' and indiv='$indiv' and location='$location'";
1746                 $this->db->query($sql,__LINE__,__FILE__);
1747                 if(!$this->db->next_record()) {
1748                   // Perform database save actions here
1749                   $this->db->query("UPDATE tc_appointment set " .
1750                                    " indiv='" . $indiv . "'" .
1751                                    ",location='" . $location . "'" .
1752                                    " WHERE appointment=" . $appointment,__LINE__,__FILE__);
1753
1754                   // Email the appointment
1755                   $this->email_appt($appointment);
1756                 }
1757               }
1758           }
1759
1760           // Save any changes made to the int notes table
1761           $new_data = get_var('int_notes',array('POST'));
1762           foreach ($new_data as $entry)
1763            {
1764              $int_notes = $entry['notes'];
1765              $indiv_id = $entry['indiv_id'];
1766              $indiv_name = $entry['indiv_name'];
1767              $int_pri = $entry['pri'];
1768              //print "int_notes: $int_notes indiv_name: $indiv_name <Br>";
1769              // Perform database save actions here
1770              $this->db->query("UPDATE tc_indiv set " .
1771                               " int_notes='" . $int_notes . "'" .
1772                               ",int_pri='" . $int_pri . "'" .
1773                               " WHERE indiv=" . $indiv_id,__LINE__,__FILE__);
1774
1775            }
1776
1777           $take_me_to_url = $GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_sched');
1778           //Header('Location: ' . $take_me_to_url);
1779         }
1780
1781       // Get the Districts
1782       $sql = "SELECT * FROM tc_district where valid=1 ORDER BY district ASC";
1783       $this->db->query($sql,__LINE__,__FILE__);
1784       $i=0;
1785       while ($this->db->next_record())
1786         {
1787           $district = $this->db->f('district');
1788           $districts[$i]['district'] = $this->db->f('district');
1789           $districts[$i]['name'] = $this->db->f('name');
1790           $districts[$i]['supervisor'] = $this->db->f('supervisor');
1791           $sql = "SELECT * FROM tc_presidency where district=$district and valid=1";
1792           $this->db2->query($sql,__LINE__,__FILE__);
1793           if($this->db2->next_record()) {
1794             $districts[$i]['presidency'] = $this->db2->f('presidency');
1795           }
1796           $i++;
1797         }
1798
1799       // APPOINTMENT TABLE
1800       $district = 1;
1801       $date_width=250; $time_width=100; $indiv_width=200; $location_width=100;
1802       $appt_table_width=$date_width + $time_width + $indiv_width + $location_width;
1803       $appt_header_row = "<th width=$date_width><font size=-2>Date</th>";
1804       $appt_header_row.= "<th width=$time_width><font size=-2>Time</th>";
1805       $appt_header_row.= "<th width=$indiv_width><font size=-2>Individual</th>";
1806       $appt_header_row.= "<th width=$location_width><font size=-2>Location</th>";
1807       $appt_table_data = "";
1808
1809       $total_comps=0; $comps_with_quarterly_int=0;
1810
1811       // Display a scheduling table for each district
1812       for ($d=0; $d < count($districts); $d++) {
1813       $table_data=""; $appt_table_data="";
1814       $this->t->set_var('district_number',$districts[$d]['district']);
1815       $this->t->set_var('district_name',$districts[$d]['name']);
1816       $supervisor = $districts[$d]['supervisor'];
1817       $supervisor_array = explode(",", $supervisor);
1818       $supervisor_last_name = $supervisor_array[0];
1819       $sql = "SELECT * FROM tc_indiv where indiv='$supervisor'";
1820       $this->db2->query($sql,__LINE__,__FILE__);
1821       if($this->db2->next_record()) {
1822         $indiv_id = $this->db2->f('indiv_id');
1823       }
1824       $sql = "SELECT * FROM tc_indiv where indiv_id='$indiv_id'";
1825       $this->db2->query($sql,__LINE__,__FILE__);
1826       if($this->db2->next_record()) {
1827         $supervisor_address = $this->db2->f('address');
1828       }
1829       $location = "$supervisor_last_name"." home ($supervisor_address)";
1830       $table_title = "District ".$districts[$d]['district'].": ".$districts[$d]['name'].": All Individuals with Interviews Not Completed";
1831       $appt_table_title = "District ".$districts[$d]['district'].": ".$districts[$d]['name'].": Interview Appointment Slots";
1832       $this->t->set_var('table_title',$table_title);
1833       $this->t->set_var('appt_table_title',$appt_table_title);
1834
1835       // query the database for all the appointments
1836       $sql = "SELECT * FROM tc_appointment where presidency=".$districts[$d]['presidency']." and date>=CURDATE() ORDER BY date ASC, time ASC";
1837       $this->db->query($sql,__LINE__,__FILE__);
1838
1839       while ($this->db->next_record())
1840         {
1841           $appointment = $this->db->f('appointment');
1842           $indiv = $this->db->f('indiv');
1843           $location = $this->db->f('location');
1844           if(($location == "") && ($indiv > 0)) { $location = "$supervisor_last_name"." home ($supervisor_address)"; }
1845
1846           $date = $this->db->f('date');
1847           $date_array = explode("-",$date);
1848           $year = $date_array[0]; $month = $date_array[1]; $day = $date_array[2];
1849           $day_string = date("l d-M-Y", mktime(0,0,0,$month,$day,$year));
1850
1851           $time = $this->db->f('time');
1852           $time_array = explode(":",$time);
1853           $time_string = date("g:i a", mktime($time_array[0], $time_array[1], $time_array[2]));
1854
1855           $appt_table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
1856           $appt_table_data.= "<td align=center>$day_string</td>";
1857           $appt_table_data.= "<td align=center>$time_string</td>";
1858
1859           $appt_table_data.= '<td align=center><select name=appt_notes['.$appointment.'][indiv]>';
1860           $appt_table_data.= '<option value=0></option>';
1861           for ($i=0; $i < count($indiv_id_data); $i++) {
1862             $id = $indiv_id_data[$i];
1863             $name = $indiv_name_data[$i];
1864             if($indiv_id_data[$i] == $indiv) { $selected[$id] = 'selected="selected"'; } else { $selected[$id] = ''; }
1865             $appt_table_data.= '<option value='.$id.' '.$selected[$id].'>'.$name.'</option>';
1866           }
1867           $appt_table_data.='</select></td>';
1868
1869           $appt_table_data.= '<td align=center><input type=text size="35" maxlength="120" ';
1870           $appt_table_data.= 'name="appt_notes['.$appointment.'][location]" value="'.$location.'">';
1871
1872           $appt_table_data.= '<input type=hidden name="appt_notes['.$appointment.'][appointment]" value="'.$appointment.'">';
1873           $appt_table_data.= '<input type=hidden name="appt_notes['.$appointment.'][supervisor]" value="'.$supervisor.'">';
1874
1875           $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1876           $this->t->set_var('tr_color',$tr_color);
1877         }
1878
1879       $this->t->set_var('appt_table_data',$appt_table_data);
1880       $this->t->set_var('appt_header_row',$appt_header_row);
1881       $this->t->set_var('appt_table_width',$appt_table_width);
1882
1883       // INTERVIEW SCHEDULING TABLE
1884
1885       // Select all the unique companionship numbers for this district
1886       $sql = "SELECT distinct companionship FROM tc_companionship where valid=1 and district=". $districts[$d]['district'];
1887       $this->db->query($sql,__LINE__,__FILE__);
1888       $j=0; $unique_companionships = '';
1889       while ($this->db->next_record())
1890         {
1891           $unique_companionships[$j]['companionship'] = $this->db->f('companionship');
1892           $j++;
1893         }
1894
1895       $i=0;
1896       for ($j=0; $j < count($unique_companionships); $j++) {
1897         // Select all the companions from each companionship
1898         $sql = "SELECT * FROM tc_companionship where valid=1 and ".
1899            "companionship=". $unique_companionships[$j]['companionship'];
1900         $this->db->query($sql,__LINE__,__FILE__);
1901         $k=0; $int_completed=0;
1902         $comp = $unique_companionships[$j]['companionship'];
1903         $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1904         $this->t->set_var('tr_color',$tr_color);
1905         $total_comps++;
1906         while ($this->db->next_record())
1907             {
1908               // Get this companions information
1909               $indiv_id = $this->db->f('indiv');
1910
1911               $sql = "SELECT * FROM tc_indiv where indiv=$indiv_id";
1912               $this->db2->query($sql,__LINE__,__FILE__);
1913               $indiv_id = $this->db2->f('indiv');
1914               $indiv_name = $this->db2->f('name');
1915               $indiv_phone[$indiv_id] = $this->db2->f('phone');
1916               $indiv_int_pri[$indiv_id] = $this->db2->f('int_pri');
1917               $indiv_int_notes[$indiv_id] = $this->db2->f('int_notes');
1918
1919               $id = $indiv_id;
1920               $name = $indiv_name;
1921               $phone = $indiv_phone[$id];
1922               $int_pri = $indiv_int_pri[$id];
1923               $int_notes = $indiv_int_notes[$id];
1924
1925               // If the companionship has already had its quarterly interview,
1926               // Skip the other companion in the companionship.
1927               if($int_completed == 1) {
1928                 $completed_data.= "<tr bgcolor=". $this->t->get_var('tr_color2') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
1929                 $completed_data.= "<td align=center>$phone</td>";
1930                 $completed_data.= "<td align=center><a href=".$link.">$date</a></td>";
1931                 $completed_data.= "<td align=left>$int_notes</td>";
1932                 $completed_data.= '</tr>';
1933                 $tr_color2 = $this->nextmatchs->alternate_row_color($tr_color2);
1934                 $this->t->set_var('tr_color2',$tr_color2);
1935                 $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
1936                 $this->t->set_var('tr_color',$tr_color);
1937                 continue;
1938               }
1939
1940               // If this companionship has had a hometeaching interview this quarter, don't show them on the schedule list
1941               $sql = "SELECT * FROM tc_interview WHERE date >= '$quarter_start' AND date < '$quarter_end' ".
1942                  "AND indiv=" . $id;
1943               $this->db2->query($sql,__LINE__,__FILE__);
1944
1945               if(!$this->db2->next_record()) {
1946                 $sql = "SELECT * FROM tc_interview WHERE indiv=" . $id . " ORDER BY date DESC";
1947                 $this->db3->query($sql,__LINE__,__FILE__);
1948                 if($this->db3->next_record()) { $date = $this->db3->f('date'); } else { $date = ""; }
1949                 $link_data['menuaction'] = 'tc.tc.int_update';
1950                 $link_data['indiv'] = $id;
1951                 $link_data['name'] = $name;
1952                 $link_data['interview'] = '';
1953                 $link_data['action'] = 'add';
1954                 $link_data['interviewer'] = $districts[$d]['supervisor'];
1955                 $link = $GLOBALS['phpgw']->link('/tc/index.php',$link_data);
1956                 $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
1957                 $table_data.= "<td align=center>$phone</td>";
1958                 $table_data.= "<td align=center>";
1959                 $table_data.= '<select name=int_notes['.$i.'][pri]>';
1960                 foreach(range(0,6) as $num) {
1961                   if($num == 0) { $num = 1; } else {$num = $num*5; }
1962                   if($int_pri == $num) { $selected[$num] = 'selected="selected"'; } else { $selected[$num] = ''; }
1963                   $table_data.= '<option value='.$num.' '.$selected[$num].'>'.$num.'</option>';
1964                 }
1965                   $table_data.= '</select></td>';
1966                 $table_data.= "<td align=center>$date</td>";
1967                 $table_data.= '<td><input type=text size="50" maxlength="128" name="int_notes['.$i.'][notes]" value="'.$int_notes.'">';
1968                 $table_data.= '<input type=hidden name="int_notes['.$i.'][indiv_id]" value="'.$id.'">';
1969                 $table_data.= '<input type=hidden name="int_notes['.$i.'][indiv_name]" value="'.$name.'">';
1970                 $table_data.= '</td>';
1971                 $table_data.= '</tr>'."\n";
1972                 $i++;
1973               } else {
1974                 $link_data['menuaction'] = 'tc.tc.int_update';
1975                 $link_data['interviewer'] = $this->db2->f('interviewer');
1976                 $link_data['indiv'] = $this->db2->f('indiv');
1977                 $link_data['name'] = $name;
1978                 $link_data['interview'] = $this->db2->f('interview');
1979                 $link_data['action'] = 'view';
1980                 $link = $GLOBALS['phpgw']->link('/tc/index.php',$link_data);
1981                 $comps_with_quarterly_int++;
1982                 $int_completed=1;
1983                 $date = $this->db2->f('date');
1984                 $int_notes = $this->db2->f('notes');
1985                 if(strlen($int_notes) > 40) { $int_notes = substr($int_notes,0,40) . "..."; }
1986                 $completed_data.= "<tr bgcolor=". $this->t->get_var('tr_color2') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
1987                 $completed_data.= "<td align=center>$phone</td>";
1988                 $completed_data.= "<td align=center><a href=".$link.">$date</a></td>";
1989                 $completed_data.= "<td align=left>$int_notes</td>";
1990                 $completed_data.= '</tr>';
1991               }
1992             }
1993       }
1994
1995       $name_width=175; $phone_width=100; $date_width=100; $notes_width=300;
1996       $completed_table_width=$name_width + $phone_width + $date_width + $notes_width;
1997       $completed_header_row = "<th width=$name_width><font size=-2>Individual Name</th>";
1998       $completed_header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
1999       $completed_header_row.= "<th width=$date_width><font size=-2>Date</th>";
2000       $completed_header_row.= "<th width=$notes_width><font size=-2>Interview Notes</th>";
2001
2002       $this->t->set_var('table_width',$table_width);
2003       $this->t->set_var('header_row',$header_row);
2004       $this->t->set_var('table_data',$table_data);
2005       $this->t->set_var('completed_header_row',$completed_header_row);
2006       $this->t->set_var('completed_table_width',$completed_table_width);
2007       $this->t->set_var('completed',$completed_data);
2008       $this->t->fp('indivlist','indiv_list',True);
2009
2010       } // End for each district loop
2011
2012
2013       $indivs_width=300; $totals_width=100;
2014       $totals_table_width=$indivs_width + $totals_width;
2015       $totals_header_row = "<th width=$indivs_width><font size=-2>Individuals</th>";
2016       $totals_header_row.= "<th width=$totals_width><font size=-2>$year</th>";
2017       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
2018       $totals_data.= "<td align=left><font size=-2><b>Total Companionships with interviews completed:</b></font></td>";
2019       $totals_data.= "<td align=center><font size=-2><b>$comps_with_quarterly_int / $total_comps</b></font></td>";
2020       $percent = ceil(($comps_with_quarterly_int / $total_comps)*100);
2021       $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
2022       $this->t->set_var('tr_color',$tr_color);
2023       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
2024       $totals_data.= "<td align=left><font size=-2><b>Percentage:</b></font></td>";
2025       $totals_data.= "<td align=center><font size=-2><b>$percent%</b></font></td>";
2026       $totals_data.= "</tr>";
2027
2028       $this->t->set_var('totals',$totals_data);
2029       $this->t->set_var('totals_header_row',$totals_header_row);
2030       $this->t->set_var('totals_table_width',$totals_table_width);
2031
2032       $this->t->pfp('out','int_sched_t');
2033       $this->save_sessiondata();
2034
2035     }
2036
2037   function vis_sched()
2038     {
2039       $this->t->set_file(array('vis_sched_t' => 'vis_sched.tpl'));
2040       $this->t->set_block('vis_sched_t','family_list','familylist');
2041       $this->t->set_block('vis_sched_t','appt_list','apptlist');
2042       $action = get_var('action',array('GET','POST'));
2043
2044       $this->t->set_var('lang_save','Save Appt / Pri / Notes');
2045       $this->t->set_var('lang_reset','Clear Changes');
2046
2047       $this->t->set_var('vis_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_view'));
2048       $this->t->set_var('vis_link_title','View Yearly Visits');
2049
2050       $this->t->set_var('schedule_vis_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_sched'));
2051       $this->t->set_var('schedule_vis_link_title','Schedule Yearly Visits');
2052
2053       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_sched&action=save'));
2054       $this->t->set_var('title','Presidency Yearly Visit Scheduler');
2055
2056       $family_width=500; $phone_width=40; $pri_width=10; $notes_width=128; $visit_date_width=20;
2057       $table_width=$family_width + $phone_width + $pri_width + $notes_width + $visit_date_width;
2058       $header_row = "<th width=$family_width><font size=-2>Family Name</th>";
2059       $header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
2060       $header_row.= "<th width=$pri_width><font size=-2>Priority</th>";
2061       $header_row.= "<th width=$visit_date_width><font size=-2>Last Visit</th>";
2062       $header_row.= "<th width=$notes_width><font size=-2>Scheduling Notes</th>";
2063       $table_data=""; $completed_data=""; $totals_data="";
2064
2065       $year = date('Y');
2066
2067       // create the family id -> family name mapping
2068       $sql = "SELECT * FROM tc_family where valid=1 and indiv_id != 0 ORDER BY name ASC";
2069       $this->db->query($sql,__LINE__,__FILE__);
2070       $i=0;
2071       $family_id = NULL;
2072       while ($this->db->next_record())
2073         {
2074           $family_id[$i] = $this->db->f('family');
2075           $family_name[$i] = $this->db->f('name');
2076           $familyid2name[$family_id[$i]] = $family_name[$i];
2077           $sql = "SELECT * FROM tc_indiv where family='$family_id[$i]'";
2078           $this->db2->query($sql,__LINE__,__FILE__);
2079           if($this->db2->next_record()) {
2080             $familyid2address[$family_id[$i]] = $this->db2->f('address');
2081           }
2082           $i++;
2083         }
2084       array_multisort($family_name, $family_id);
2085
2086       if($action == 'save')
2087         {
2088           // Save any changes made to the appointment table
2089           $new_data = get_var('appt_notes',array('POST'));
2090           if($new_data != "") {
2091             foreach ($new_data as $entry)
2092               {
2093                 $family = $entry['family'];
2094                 $appointment = $entry['appointment'];
2095                 $location = $entry['location'];
2096                 if($location == "") {
2097                   $family_name_array = explode(",", $familyid2name[$family]);
2098                   $family_last_name = $family_name_array[0];
2099                   $family_address = $familyid2address[$family];
2100                   $location = "$family_last_name"." home ($family_address)";
2101                 }
2102                 if($family == 0) { $location = ""; }
2103
2104                 //Only perform a database update if we have made a change to this appointment
2105                 $sql = "SELECT * FROM tc_appointment where appointment='$appointment' and family='$family' and location='$location'";
2106                 $this->db->query($sql,__LINE__,__FILE__);
2107                 if(!$this->db->next_record()) {
2108                   // Perform database save actions here
2109                   $this->db->query("UPDATE tc_appointment set " .
2110                                    " family='" . $family . "'" .
2111                                    ",location='" . $location . "'" .
2112                                    " WHERE appointment=" . $appointment,__LINE__,__FILE__);
2113
2114                   // Email the appointment
2115                   $this->email_appt($appointment);
2116                 }
2117               }
2118           }
2119
2120           // Save any changes made to the visit notes table
2121           $new_data = get_var('vis_notes',array('POST'));
2122           foreach ($new_data as $entry)
2123            {
2124              $visit_notes = $entry['notes'];
2125              $family = $entry['family_id'];
2126              $visit_pri = $entry['pri'];
2127
2128              // Perform database save actions here
2129              $this->db->query("UPDATE tc_family set " .
2130                               " visit_notes='" . $visit_notes . "'" .
2131                               ",visit_pri='" . $visit_pri . "'" .
2132                               " WHERE family=" . $family,__LINE__,__FILE__);
2133
2134            }
2135
2136           $take_me_to_url = $GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_sched');
2137           //Header('Location: ' . $take_me_to_url);
2138         }
2139
2140       // APPOINTMENT TABLE
2141       $date_width=250; $time_width=100; $family_width=250; $location_width=100;
2142       $appt_table_width=$date_width + $time_width + $family_width + $location_width;
2143       $appt_header_row = "<th width=$date_width><font size=-2>Date</th>";
2144       $appt_header_row.= "<th width=$time_width><font size=-2>Time</th>";
2145       $appt_header_row.= "<th width=$family_width><font size=-2>Family</th>";
2146       $appt_header_row.= "<th width=$location_width><font size=-2>Location</th>";
2147       $appt_table_data = "";
2148
2149       // Find out what the EQ Presidency ID is
2150       $sql = "SELECT * FROM tc_presidency where eqpres=1 and valid=1";
2151       $this->db->query($sql,__LINE__,__FILE__);
2152       if($this->db->next_record()) {
2153         $presidency_name = $this->db->f('name');
2154         $presidency_id = $this->db->f('presidency');
2155       } else {
2156         print "<hr><font color=red><h3>-E- Unable to locate Presidency in tc_presidency table</h3></font></hr>";
2157         return;
2158       }
2159
2160       // query the database for all the appointments
2161       $sql = "SELECT * FROM tc_appointment where presidency=$presidency_id and date>=CURDATE() ORDER BY date ASC, time ASC";
2162       $this->db->query($sql,__LINE__,__FILE__);
2163
2164       while ($this->db->next_record())
2165         {
2166           $appointment = $this->db->f('appointment');
2167           $family = $this->db->f('family');
2168           $location = $this->db->f('location');
2169           $family_name_array = explode(",", $familyid2name[$family]);
2170           $family_last_name = $family_name_array[0];
2171           $family_address = $familyid2address[$family];
2172           if(($location == "") && ($family > 0)) { $location = "$family_last_name"." home ($family_address)"; }
2173
2174           $date = $this->db->f('date');
2175           $date_array = explode("-",$date);
2176           $year = $date_array[0]; $month = $date_array[1]; $day = $date_array[2];
2177           $day_string = date("l d-M-Y", mktime(0,0,0,$month,$day,$year));
2178
2179           $time = $this->db->f('time');
2180           $time_array = explode(":",$time);
2181           $time_string = date("g:i a", mktime($time_array[0], $time_array[1], $time_array[2]));
2182
2183           $appt_table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
2184           $appt_table_data.= "<td align=center>$day_string</td>";
2185           $appt_table_data.= "<td align=center>$time_string</td>";
2186
2187           $appt_table_data.= '<td align=center><select name=appt_notes['.$appointment.'][family]>';
2188           $appt_table_data.= '<option value=0></option>';
2189           for ($i=0; $i < count($family_id); $i++) {
2190             $id = $family_id[$i];
2191             $name = $family_name[$i];
2192             if($family_id[$i] == $family) { $selected[$id] = 'selected="selected"'; } else { $selected[$id] = ''; }
2193             $appt_table_data.= '<option value='.$id.' '.$selected[$id].'>'.$name.' Family</option>';
2194           }
2195           $appt_table_data.='</select></td>';
2196
2197           $appt_table_data.= '<td align=center><input type=text size="35" maxlength="120" ';
2198           $appt_table_data.= 'name="appt_notes['.$appointment.'][location]" value="'.$location.'">';
2199
2200           $appt_table_data.= '<input type=hidden name="appt_notes['.$appointment.'][appointment]" value="'.$appointment.'">';
2201
2202           $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
2203           $this->t->set_var('tr_color',$tr_color);
2204         }
2205
2206       $this->t->set_var('appt_table_data',$appt_table_data);
2207       $this->t->set_var('appt_header_row',$appt_header_row);
2208       $this->t->set_var('appt_table_width',$appt_table_width);
2209
2210
2211       // VISIT SCHEDULING TABLE
2212       $sql = "SELECT * FROM tc_family where valid=1 and indiv_id != 0 ORDER BY visit_pri ASC";
2213       $this->db->query($sql,__LINE__,__FILE__);
2214
2215       $total_families=0; $families_with_yearly_visit=0;
2216
2217       $i=0;
2218       $family_id = NULL;
2219       $family_name = NULL;
2220       $family_phone = NULL;
2221       $family_visit_pri = NULL;
2222       $family_visit_notes = NULL;
2223       while ($this->db->next_record())
2224         {
2225           $family_id[$i] = $this->db->f('family');
2226           $family_name[$i] = $this->db->f('name');
2227           $family_phone[$family_id[$i]] = $family_id[$i] . " ERROR";
2228           $family_visit_pri[$family_id[$i]] = $this->db->f('visit_pri');
2229           $family_visit_notes[$family_id[$i]] = $this->db->f('visit_notes');
2230           $i++;
2231           $total_families++;
2232         }
2233
2234       $sql = "SELECT * FROM tc_indiv where valid=1";
2235       $this->db->query($sql,__LINE__,__FILE__);
2236       while ($this->db->next_record())
2237         {
2238           $family = $this->db->f('family');
2239           $phone = $this->db->f('phone');
2240           $family_phone[$family] = $phone;
2241         }
2242
2243       $max = count($family_id);
2244
2245       for($i=0; $i < $max; $i++) {
2246           $id = $family_id[$i];
2247           $name = $family_name[$i];
2248           $phone = $family_phone[$id];
2249           $vis_pri = $family_visit_pri[$id];
2250           $vis_notes = $family_visit_notes[$id];
2251
2252           // If this family has had a yearly visit this year, don't show them on the schedule list
2253           $year_start = $year - 1 . "-12-31"; $year_end = $year + 1 . "-01-01";
2254           $sql = "SELECT * FROM tc_visit WHERE date > '$year_start' AND date < '$year_end' ".
2255              "AND family=" . $id . " AND companionship=0";
2256           $this->db2->query($sql,__LINE__,__FILE__);
2257
2258           if(!$this->db2->next_record()) {
2259             $sql = "SELECT * FROM tc_visit WHERE family=" . $id . " AND companionship=0 ORDER BY date DESC";
2260             $this->db->query($sql,__LINE__,__FILE__);
2261             if($this->db->next_record()) { $date = $this->db->f('date'); } else { $date = ""; }
2262             $link_data['menuaction'] = 'tc.tc.vis_update';
2263             $link_data['visit'] = '';
2264             $link_data['family'] = $id;
2265             $link_data['name'] = $name;
2266             $link_data['action'] = 'add';
2267             $link = $GLOBALS['phpgw']->link('/tc/index.php',$link_data);
2268             $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') ."><td title=\"$phone\"><a href=$link>$name Family</a></td>";
2269             $table_data.= "<td align=center>$phone</td>";
2270             $table_data.= "<td align=center>";
2271             $table_data.= '<select name=vis_notes['.$i.'][pri]>';
2272             foreach(range(0,6) as $num) {
2273               if($num == 0) { $num = 1; } else {$num = $num*5; }
2274               if($vis_pri == $num) { $selected[$num] = 'selected="selected"'; } else { $selected[$num] = ''; }
2275               $table_data.= '<option value='.$num.' '.$selected[$num].'>'.$num.'</option>';
2276             }
2277             $table_data.= '</select></td>';
2278             $table_data.= "<td align=center>$date</td>";
2279             $table_data.= '<td><input type=text size="50" maxlength="128" name="vis_notes['.$i.'][notes]" value="'.$vis_notes.'">';
2280             $table_data.= '<input type=hidden name="vis_notes['.$i.'][family_id]" value="'.$id.'">';
2281             $table_data.= '<input type=hidden name="vis_notes['.$i.'][family_name]" value="'.$name.'">';
2282             $table_data.= '</td>';
2283             $table_data.= '</tr>';
2284             $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
2285             $this->t->set_var('tr_color',$tr_color);
2286           } else {
2287             $link_data['menuaction'] = 'tc.tc.vis_update';
2288             $link_data['visit'] = $this->db2->f('visit');
2289             $link_data['family'] = $this->db2->f('family');
2290             $link_data['name'] = $name;
2291             $link_data['date'] = $this->db2->f('date');
2292             $link_data['action'] = 'view';
2293             $link = $GLOBALS['phpgw']->link('/tc/index.php',$link_data);
2294             $families_with_yearly_visit++;
2295             $date = $this->db2->f('date');
2296             $vis_notes = $this->db2->f('notes');
2297             if(strlen($vis_notes) > 40) { $vis_notes = stripslashes(substr($vis_notes,0,40) . "..."); }
2298             $completed_data.= "<tr bgcolor=". $this->t->get_var('tr_color2') ."><td title=\"$phone\"><a href=$link>$name Family</a></td>";
2299             $completed_data.= "<td align=center>$phone</td>";
2300             $completed_data.= "<td align=center><a href=".$link.">$date</a></td>";
2301             $completed_data.= "<td align=left>$vis_notes</td>";
2302             $completed_data.= '</tr>';
2303             $tr_color2 = $this->nextmatchs->alternate_row_color($tr_color2);
2304             $this->t->set_var('tr_color2',$tr_color2);
2305           }
2306       }
2307
2308       $name_width=190; $phone_width=100; $date_width=100; $notes_width=300;
2309       $completed_table_width=$name_width + $phone_width + $date_width + $notes_width;
2310       $completed_header_row = "<th width=$name_width><font size=-2>Family Name</th>";
2311       $completed_header_row.= "<th width=$phone_width><font size=-2>Phone</th>";
2312       $completed_header_row.= "<th width=$date_width><font size=-2>Date</th>";
2313       $completed_header_row.= "<th width=$notes_width><font size=-2>Visit Notes</th>";
2314
2315       $family_width=300; $totals_width=100;
2316       $totals_table_width=$family_width + $totals_width;
2317       $totals_header_row = "<th width=$family_width><font size=-2>Families</th>";
2318       $totals_header_row.= "<th width=$totals_width><font size=-2>$year</th>";
2319       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
2320       $totals_data.= "<td align=left><font size=-2><b>Total Families with yearly Visits completed:</b></font></td>";
2321       $totals_data.= "<td align=center><font size=-2><b>$families_with_yearly_visit / $total_families</b></font></td>";
2322       $percent = ceil(($families_with_yearly_visit / $total_families)*100);
2323       $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
2324       $this->t->set_var('tr_color',$tr_color);
2325       $totals_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
2326       $totals_data.= "<td align=left><font size=-2><b>Percentage:</b></font></td>";
2327       $totals_data.= "<td align=center><font size=-2><b>$percent%</b></font></td>";
2328       $totals_data.= "</tr>";
2329
2330       $this->t->set_var('table_width',$table_width);
2331       $this->t->set_var('header_row',$header_row);
2332       $this->t->set_var('table_data',$table_data);
2333       $this->t->set_var('totals_header_row',$totals_header_row);
2334       $this->t->set_var('totals_table_width',$totals_table_width);
2335       $this->t->set_var('completed_header_row',$completed_header_row);
2336       $this->t->set_var('completed_table_width',$completed_table_width);
2337       $this->t->set_var('completed',$completed_data);
2338       $this->t->set_var('totals',$totals_data);
2339       $this->t->fp('familylist','family_list',True);
2340       $this->t->fp('apptlist','appt_list',True);
2341
2342       $this->t->pfp('out','vis_sched_t');
2343       $this->save_sessiondata();
2344
2345     }
2346
2347   function ppi_view()
2348     {
2349       $this->t->set_file(array('ppi_view_t' => 'ppi_view.tpl'));
2350       $this->t->set_block('ppi_view_t','district_list','list');
2351
2352       $this->t->set_var('linkurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_view'));
2353       $num_months = get_var('num_months',array('GET','POST'));
2354       if($num_months == '') { $num_months = $this->default_ppi_num_months; }
2355       $this->t->set_var('num_months',$num_months);
2356       if($num_months == 1) { $this->t->set_var('lang_num_months','Month of History'); }
2357       else {  $this->t->set_var('lang_num_months','Months of History'); }
2358       $this->t->set_var('lang_filter','Filter');
2359       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_view'));
2360
2361       $this->t->set_var('ppi_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_view'));
2362       $this->t->set_var('ppi_link_title','Yearly PPIs');
2363
2364       $this->t->set_var('schedule_ppi_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_sched'));
2365       $this->t->set_var('schedule_ppi_link_title','Schedule Yearly PPIs');
2366
2367       $this->t->set_var('title','Yearly PPIs');
2368       $num_months = get_var('num_months',array('GET','POST'));
2369       if($num_months == '') { $num_months = $this->default_ppi_num_years; }
2370       $this->t->set_var('num_months',$num_months);
2371       if($num_months == 1) { $this->t->set_var('lang_num_months','Year of History'); }
2372       else { $this->t->set_var('lang_num_months','Years of History'); }
2373
2374       $sql = "SELECT * FROM tc_presidency where president=1 and valid=1";
2375       $this->db->query($sql,__LINE__,__FILE__);
2376       if($this->db->next_record()) {
2377         $president_name = $this->db->f('name');
2378         $interviewer = $this->db->f('indiv');
2379         $interview_type = 'ppi';
2380       } else {
2381         print "<hr><font color=red><h3>-E- Unable to locate President in tc_presidency table</h3></font></hr>";
2382         return;
2383       }
2384       $this->t->set_var('district_number','*');
2385       $this->t->set_var('district_name',$president_name);
2386
2387       $sql = "SELECT * FROM tc_indiv where valid=1 ORDER BY indiv ASC";
2388       $this->db->query($sql,__LINE__,__FILE__);
2389       $i=0;
2390       while ($this->db->next_record())
2391         {
2392           $indiv_id[$i] = $this->db->f('indiv');
2393           $indiv_name[$i] = $this->db->f('name');
2394           $indiv_phone[$indiv_id[$i]] = $this->db->f('phone');
2395           $indiv_ppi_pri[$indiv_id[$i]] = $this->db->f('ppi_pri');
2396           $indiv_ppi_notes[$indiv_id[$i]] = $this->db->f('ppi_notes');
2397           $i++;
2398         }
2399       $total_indivs=$i;
2400       array_multisort($indiv_name, $indiv_id);
2401       //var_dump($indiv_name); print "<br><br>"; var_dump($indiv_id);
2402
2403       $header_row="<th width=$comp_width><font size=-2>Individual Name</th>";
2404
2405       $indiv_width=400; $ppi_width=75; $table_width=$indiv_width + $num_months*$ppi_width;
2406       $table_data="";
2407       for($m=$num_months; $m >= 0; $m--) {
2408         $year = date('Y') - $m;
2409         $header_row .= "<th width=150><font size=-2>$year</th>";
2410         $ppis[$m] = 0;
2411       }
2412
2413       for ($j=0; $j < count($indiv_id); $j++) {
2414         $id = $indiv_id[$j];
2415         $name = $indiv_name[$j];
2416         $phone = $indiv_phone[$id];
2417
2418         $link_data['menuaction'] = 'tc.tc.ppi_update';
2419         $link_data['interviewer'] = $interviewer;
2420         $link_data['indiv'] = $id;
2421         $link_data['name'] = $name;
2422         $link_data['interview'] = '';
2423         $link_data['interview_type'] = $interview_type;
2424         $link_data['action'] = 'add';
2425         $link = $GLOBALS['phpgw']->link('/tc/index.php',$link_data);
2426         $this->nextmatchs->template_alternate_row_color(&$this->t);
2427         $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
2428
2429         // Find out how many times PPIs were performed in the past $num_months for this individual
2430         for($m=$num_months; $m >= 0; $m--) {
2431           $year = date('Y') - $m;
2432           $year_start = $year - 1 . "-12-31"; $year_end = $year + 1 . "-01-01";
2433           $sql = "SELECT * FROM tc_interview WHERE date > '$year_start' AND date < '$year_end' ".
2434              "AND indiv=" . $id . " AND interview_type='ppi'";
2435           $this->db2->query($sql,__LINE__,__FILE__);
2436
2437           if(!$total_ppis[$m]) { $total_ppis[$m] = 0; }
2438           if($this->db2->next_record()) {
2439             $ppis[$m]++; $total_ppis[$m]++; $ppi_recorded[$companionship][$m]=1;
2440             $link_data['menuaction'] = 'tc.tc.ppi_update';
2441             $link_data['companionship'] = $companionship;
2442             $link_data['interviewer'] = $this->db2->f('interviewer');
2443             $link_data['indiv'] = $id;
2444             $link_data['name'] = $name;
2445             $link_data['interview'] = $this->db2->f('interview');
2446             $link_data['interview_type'] = $interview_type;
2447             $link_data['action'] = 'view';
2448             $date = $this->db2->f('date');
2449             $date_array = explode("-",$date);
2450             $month = $date_array[1];
2451             $day   = $date_array[2];
2452             $link = $GLOBALS['phpgw']->link('/tc/index.php',$link_data);
2453             $table_data .= '<td align=center><a href='.$link.'><img src="images/checkmark.gif">&nbsp;'.$month.'-'.$day.'</a></td>';
2454           }
2455           else { $table_data .= "<td>&nbsp;</td>"; }
2456         }
2457         $table_data .= "</tr>\n";
2458       }
2459       $table_data .= "<tr><td colspan=20><hr></td></tr>";
2460
2461       $stat_data = "<tr><td><b><font size=-2>$total_indivs Individuals<br>PPI Totals:</font></b></td>";
2462       for($m=$num_months; $m >=0; $m--) {
2463         $percent = ceil(($ppis[$m] / $total_indivs)*100);
2464         $stat_data .= "<td align=center><font size=-2><b>$ppis[$m]<br>$percent%</font></b></td>";
2465       }
2466       $stat_data .= "</tr>";
2467
2468       $this->t->set_var('table_width',$table_width);
2469       $this->t->set_var('header_row',$header_row);
2470       $this->t->set_var('table_data',$table_data);
2471       $this->t->set_var('stat_data',$stat_data);
2472       $this->t->pfp('out','ppi_view_t');
2473       $this->save_sessiondata();
2474     }
2475
2476   function ppi_update()
2477     {
2478       $this->t->set_file(array('form' => 'ppi_update.tpl'));
2479       $this->t->set_block('form','interviewer_list','int_list');
2480       $this->t->set_block('form','add','addhandle');
2481       $this->t->set_block('form','edit','edithandle');
2482
2483       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_view'));
2484       $this->t->set_var('readonly','');
2485       $this->t->set_var('disabled','');
2486
2487       $action = get_var('action',array('GET','POST'));
2488       $companionship = get_var('companionship',array('GET','POST'));
2489       $interviewer = get_var('interviewer',array('GET','POST'));
2490       $name = get_var('name',array('GET','POST'));
2491       $interview = get_var('interview',array('GET','POST'));
2492       $indiv = get_var('indiv',array('GET','POST'));
2493       $date = get_var('date',array('GET','POST'));
2494       $notes = get_var('notes',array('GET','POST'));
2495       $interview_type = get_var('interview_type',array('GET','POST'));
2496
2497       $sql = "SELECT * FROM tc_presidency where valid=1 and (president=1 or counselor=1 or secretary=1)";
2498       $this->db2->query($sql,__LINE__,__FILE__);
2499       while ($this->db2->next_record())
2500       {
2501         $indiv = $this->db2->f('indiv');
2502         $interviewer_name = $this->db2->f('name');
2503         if($indiv == $interviewer) {
2504           $this->t->set_var('interviewer',$interviewer . ' selected');
2505         } else {
2506           $this->t->set_var('interviewer',$interviewer);
2507         }
2508         $this->t->set_var('interviewer_name',$interviewer_name);
2509         $this->t->set_var('interview_type_checked','');
2510         $this->t->fp('int_list','interviewer_list',True);
2511       }
2512
2513       if($action == 'save')
2514         {
2515           $notes = get_var('notes',array('POST'));
2516           $this->db->query("UPDATE tc_interview set " .
2517                      "   interview='" . $interview . "'" .
2518                     ", interviewer='" . $interviewer . "'" .
2519                           ", indiv='" . $indiv . "'" .
2520                            ", date='" . $date . "'" .
2521                           ", notes='" . $notes . "'" .
2522                  ", interview_type='" . $interview_type . "'" .
2523                            " WHERE interview=" . $interview,__LINE__,__FILE__);
2524           $this->ppi_view();
2525           return false;
2526         }
2527
2528       if($action == 'insert')
2529         {
2530           $notes = get_var('notes',array('POST'));
2531           $this->db->query("INSERT INTO tc_interview (interviewer,indiv,date,notes,interview_type) "
2532                            . "VALUES ('" . $interviewer . "','" . $indiv . "','"
2533                            . $date . "','" . $notes . "','" . $interview_type  ."')",__LINE__,__FILE__);
2534           $this->ppi_view();
2535           return false;
2536         }
2537
2538       if($action == 'add')
2539         {
2540           $this->t->set_var('cal_date',$this->jscal->input('date','','','','','','',$this->cal_options));
2541           $this->t->set_var('interview', '');
2542           $this->t->set_var('interviewer', $interviewer);
2543           $this->t->set_var('name',$name);
2544           $this->t->set_var('indiv',$indiv);
2545           $this->t->set_var('date','');
2546           $this->t->set_var('notes','');
2547           $this->t->set_var('interview_type',$interview_type);
2548           $this->t->set_var('interview_type_checked','checked');
2549           $this->t->set_var('lang_done','Cancel');
2550           $this->t->set_var('lang_action','Adding New PPI');
2551           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_update&interview='
2552                                                                 . $interview . '&action=' . 'insert'));
2553         }
2554
2555       if($action == 'edit' || $action == 'view')
2556         {
2557           $sql = "SELECT * FROM tc_interview WHERE interview=".$interview;
2558           $this->db->query($sql,__LINE__,__FILE__);
2559           $this->db->next_record();
2560           $this->t->set_var('interview',$interview);
2561           $this->t->set_var('name',$name);
2562           $this->t->set_var('interviewer', $this->db->f('interviewer'));
2563           $this->t->set_var('indiv',$this->db->f('indiv'));
2564           $this->t->set_var('date',$this->db->f('date'));
2565           $this->t->set_var('notes',$this->db->f('notes'));
2566           $this->t->set_var('interview_type',$this->db->f('interview_type'));
2567           if($this->db->f('interview_type') == 1) { $this->t->set_var('interview_type_checked','checked'); }
2568         }
2569
2570       if($action == 'edit')
2571         {
2572           $this->t->set_var('cal_date',$this->jscal->input('date',$date,'','','','','',$this->cal_options));
2573           $this->t->set_var('lang_done','Cancel');
2574           $this->t->set_var('lang_action','Editing PPI');
2575           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_update&interview='
2576                                                                 . $interview . '&action=' . 'save'));
2577         }
2578
2579       if($action == 'view')
2580         {
2581           $date = $this->db->f('date');
2582           $this->t->set_var('cal_date','<input type=text size="10" maxlength="10" name="date" value="'.$date.'" readonly>');
2583           $this->t->set_var('readonly','READONLY');
2584           $this->t->set_var('disabled','DISABLED');
2585           $this->t->set_var('lang_done','Done');
2586           $this->t->set_var('lang_action','Viewing PPI');
2587           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_update&interview='
2588                                                                 . $interview . '&action=' . 'edit'));
2589         }
2590
2591       $this->t->set_var('lang_reset','Clear Form');
2592       $this->t->set_var('lang_add','Add PPI');
2593       $this->t->set_var('lang_save','Save Changes');
2594       $this->t->set_var('edithandle','');
2595       $this->t->set_var('addhandle','');
2596
2597       $this->t->pfp('out','form');
2598
2599       if($action == 'view') { $this->t->set_var('lang_save','Edit PPI'); }
2600       if($action == 'edit' || $action == 'view') { $this->t->pfp('addhandle','edit'); }
2601       if($action == 'add') { $this->t->pfp('addhandle','add'); }
2602
2603       $this->save_sessiondata();
2604     }
2605
2606     function int_view()
2607     {
2608       $this->t->set_file(array('int_view_t' => 'int_view.tpl'));
2609       $this->t->set_block('int_view_t','district_list','list');
2610
2611       $this->t->set_var('linkurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_view'));
2612       $num_quarters = get_var('num_quarters',array('GET','POST'));
2613       if($num_quarters == '') { $num_quarters = $this->default_int_num_quarters; }
2614       $this->t->set_var('num_quarters',$num_quarters);
2615       if($num_quarters == 1) { $this->t->set_var('lang_num_quarters','Quarter of History'); }
2616       else {  $this->t->set_var('lang_num_quarters','Quarters of History'); }
2617       $this->t->set_var('lang_filter','Filter');
2618
2619       $this->t->set_var('int_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_view'));
2620       $this->t->set_var('int_link_title','Hometeaching Interviews');
2621
2622       $this->t->set_var('schedule_int_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_sched'));
2623       $this->t->set_var('schedule_int_link_title','Schedule Hometeaching Interviews');
2624
2625       $this->t->set_var('title','Hometeaching Interviews');
2626
2627       $num_months = $num_quarters * 3 - 1;
2628       $current_month = $this->current_month;
2629       if($current_month >= 1 && $current_month <= 3) { $current_month=3; }
2630       else if($current_month >= 4 && $current_month <= 6) { $current_month=6; }
2631       else if($current_month >= 7 && $current_month <= 9) { $current_month=9; }
2632       else if($current_month >= 10 && $current_month <= 12) { $current_month=12; }
2633
2634       $sql = "SELECT * FROM tc_district where valid=1 ORDER BY district ASC";
2635       $this->db->query($sql,__LINE__,__FILE__);
2636       $i=0;
2637       while ($this->db->next_record())
2638         {
2639           $districts[$i]['district'] = $this->db->f('district');
2640           $districts[$i]['name'] = $this->db->f('name');
2641           $districts[$i]['supervisor'] = $this->db->f('supervisor');
2642           $i++;
2643         }
2644
2645       $sql = "SELECT * FROM tc_indiv where valid=1 ORDER BY indiv ASC";
2646       $this->db->query($sql,__LINE__,__FILE__);
2647       $i=0;
2648       while ($this->db->next_record())
2649         {
2650           $indiv_id[$i] = $this->db->f('indiv');
2651           $indiv_name[$i] = $this->db->f('name');
2652           $indiv_phone[$indiv_id[$i]] = $this->db->f('phone');
2653           $i++;
2654         }
2655       array_multisort($indiv_name, $indiv_id);
2656       for($i=0; $i < count($indiv_id); $i++) {
2657           $id = $indiv_id[$i];
2658           $indivs[$id] = $indiv_name[$i];
2659       }
2660
2661       $total_companionships = 0;
2662       $this->nextmatchs->template_alternate_row_color(&$this->t);
2663       for ($i=0; $i < count($districts); $i++) {
2664         $this->t->set_var('district_number',$districts[$i]['district']);
2665         $this->t->set_var('district_name',$districts[$i]['name']);
2666         $supervisor = $districts[$i]['supervisor'];
2667
2668         // Select all the unique companionship numbers for this district
2669         $sql = "SELECT distinct companionship FROM tc_companionship where valid=1 and district=". $districts[$i]['district'];
2670         $this->db->query($sql,__LINE__,__FILE__);
2671         $j=0; $unique_companionships = '';
2672         while ($this->db->next_record())
2673           {
2674             $unique_companionships[$j]['companionship'] = $this->db->f('companionship');
2675             $j++;
2676           }
2677
2678         $comp_width=250; $int_width=75; $table_width=$comp_width + $num_months*$int_width;
2679         $table_data=""; $num_companionships = $j; $num_indivs = 0;
2680         for($m=$num_months; $m >= 0; $m--) { $ints[$m] = 0; }
2681         for ($j=0; $j < count($unique_companionships); $j++) {
2682           // Select all the companions in each companionship
2683           $sql = "SELECT * FROM tc_companionship where valid=1 and ".
2684              "companionship=". $unique_companionships[$j]['companionship'];
2685           $this->db->query($sql,__LINE__,__FILE__);
2686           $k=0;
2687           $comp = $unique_companionships[$j]['companionship'];
2688           for($m=$num_months; $m >= 0; $m--) { $int_recorded[$comp][$m] = 0; }
2689           while ($this->db->next_record())
2690             {
2691               // Get this companions information
2692               $num_indivs++;
2693               $companionship = $this->db->f('companionship');
2694               $indiv_id = $this->db->f('indiv');
2695               $name = $indivs[$indiv_id];
2696               $phone = $indiv_phone[$indiv_id];
2697               $link_data['menuaction'] = 'tc.tc.int_update';
2698               $link_data['companionship'] = $companionship;
2699               $link_data['interviewer'] = $supervisor;
2700               $link_data['indiv'] = $indiv_id;
2701               $link_data['name'] = $name;
2702               $link_data['interview'] = '';
2703               $link_data['action'] = 'add';
2704               $link = $GLOBALS['phpgw']->link('/tc/index.php',$link_data);
2705               $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') ."><td title=\"$phone\"><a href=$link>$name</a></td>";
2706
2707               // Find out how many times Interviews were performed in the past $num_months for this individual
2708               $header_row="<th width=$comp_width><font size=-2>Companionship</th>";
2709               for($m=$num_months; $m >= 0; $m--) {
2710                 $month = $current_month - $m;
2711                 $year = $this->current_year;
2712                 if($month <= 0) { $remainder = $month; $month = 12 + $remainder; $year=$year-1; }
2713                 if($month < 10) { $month = "0"."$month"; }
2714                 $month_start = "$year"."-"."$month"."-"."01";
2715                 $month_end = "$year"."-"."$month"."-"."31";
2716                 $month = "$month"."/"."$year";
2717                 $sql = "SELECT * FROM tc_interview WHERE date >= '$month_start' AND date <= '$month_end' ".
2718                    "AND indiv=" . $indiv_id;
2719                 $this->db2->query($sql,__LINE__,__FILE__);
2720                 $header_row .= "<th width=$int_width><font size=-2>$month</th>";
2721
2722                 if(!$total_ints[$m]) { $total_ints[$m] = 0; }
2723                 if($this->db2->next_record()) {
2724                   if(!$int_recorded[$companionship][$m]) {
2725                     $ints[$m]++; $total_ints[$m]++; $int_recorded[$companionship][$m]=1;
2726                   }
2727                   $link_data['menuaction'] = 'tc.tc.int_update';
2728                   $link_data['companionship'] = $companionship;
2729                   $link_data['interviewer'] = $this->db2->f('interviewer');
2730                   $link_data['indiv'] = $indiv_id;
2731                   $link_data['name'] = $name;
2732                   $link_data['interview'] = $this->db2->f('interview');
2733                   $link_data['action'] = 'view';
2734                   $date = $this->db2->f('date');
2735                   $date_array = explode("-",$date);
2736                   $month = $date_array[1];
2737                   $day   = $date_array[2];
2738                   $link = $GLOBALS['phpgw']->link('/tc/index.php',$link_data);
2739                   $table_data .= '<td align=center><a href='.$link.'><img src="images/checkmark.gif">&nbsp;'.$month.'-'.$day.'</a></td>';
2740                 }
2741                 else { $table_data .= "<td>&nbsp;</td>"; }
2742               }
2743               $table_data .= "</tr>";
2744               $k++;
2745             }
2746           $table_data .= "<tr><td colspan=20><hr></td></tr>";
2747         }
2748         $total_companionships += $num_companionships;
2749         $stat_data = "<tr><td><b><font size=-2>$num_companionships Companionships<br>Interview Totals:</font></b></td>";
2750
2751         // Print the hometeaching interview stats
2752         for($m=$num_months; $m >=0; $m--) {
2753           $month = $current_month - $m;
2754           if($month < 0) { $month = 12 + $month; } // Handle going backwards over a year boundary
2755           $month_begins = $month % $this->monthly_hometeaching_interview_stats;
2756           //print "$month % $this->monthly_hometeaching_interview_stats = $month_begins <br>";
2757           if($this->monthly_hometeaching_interview_stats == 1) { $month_begins = 1; }
2758           if(($month_begins) == 1) { $total = $ints[$m]; }
2759           else { $total += $ints[$m]; }
2760           $percent = ceil(($total / $num_companionships)*100);
2761           $stat_data .= "<td align=center><font size=-2><b>$total<br>$percent%</font></b></td>";
2762         }
2763         $stat_data .= "</tr>";
2764
2765         $this->t->set_var('table_width',$table_width);
2766         $this->t->set_var('header_row',$header_row);
2767         $this->t->set_var('table_data',$table_data);
2768         $this->t->set_var('stat_data',$stat_data);
2769         $this->t->fp('list','district_list',True);
2770       }
2771
2772       // Display the totals
2773       $total = 0;
2774       $totals = "<tr><td><b><font size=-2>$total_companionships Total Comps<br>Interview Totals:</font></b></td>";
2775       for($m=$num_months; $m >=0; $m--) {
2776         $month = $current_month - $m;
2777         if($month < 0) { $month = 12 + $month; } // Handle going backwards over a year boundary
2778         $month_begins = $month % $this->monthly_hometeaching_interview_stats;
2779         if($this->monthly_hometeaching_interview_stats == 1) { $month_begins = 1; }
2780         if(($month_begins) == 1) { $total = $total_ints[$m]; }
2781         else { $total += $total_ints[$m]; }
2782         $percent = ceil(($total / $total_companionships)*100);
2783         $totals .= "<td align=center><font size=-2><b>$total<br>$percent%</font></b></td>";
2784       }
2785       $totals .= "</tr>";
2786
2787       $this->t->set_var('totals',$totals);
2788       $this->t->pfp('out','int_view_t');
2789       $this->save_sessiondata();
2790     }
2791
2792   function int_update()
2793     {
2794       $this->t->set_file(array('form' => 'int_update.tpl'));
2795       $this->t->set_block('form','interviewer_list','int_list');
2796       $this->t->set_block('form','add','addhandle');
2797       $this->t->set_block('form','edit','edithandle');
2798
2799       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_view'));
2800       $this->t->set_var('readonly','');
2801       $this->t->set_var('disabled','');
2802       $this->t->set_var('interview_type_checked','');
2803
2804       $action = get_var('action',array('GET','POST'));
2805       $companionship = get_var('companionship',array('GET','POST'));
2806       $interviewer = get_var('interviewer',array('GET','POST'));
2807       $name = get_var('name',array('GET','POST'));
2808       $interview = get_var('interview',array('GET','POST'));
2809       $indiv = get_var('indiv',array('GET','POST'));
2810       $date = get_var('date',array('GET','POST'));
2811       $notes = get_var('notes',array('GET','POST'));
2812       $interview_type = get_var('interview_type',array('GET','POST'));
2813
2814       $sql = "SELECT * FROM tc_presidency where valid=1 and (president=1 or counselor=1 or secretary=1 or district!=0)";
2815       $this->db2->query($sql,__LINE__,__FILE__);
2816       while ($this->db2->next_record())
2817       {
2818         $indiv = $this->db2->f('indiv');
2819         $interviewer_name = $this->db2->f('name');
2820         if($indiv == $interviewer) {
2821           $this->t->set_var('interviewer',$interviewer . ' selected');
2822         } else {
2823           $this->t->set_var('interviewer',$interviewer);
2824         }
2825         $this->t->set_var('interviewer_name',$interviewer_name);
2826         $this->t->fp('int_list','interviewer_list',True);
2827       }
2828
2829       if($action == 'save')
2830         {
2831           $notes = get_var('notes',array('POST'));
2832           $this->db->query("UPDATE tc_interview set " .
2833                      "   interview='" . $interview . "'" .
2834                     ", interviewer='" . $interviewer . "'" .
2835                           ", indiv='" . $indiv . "'" .
2836                            ", date='" . $date . "'" .
2837                           ", notes='" . $notes . "'" .
2838                  ", interview_type='" . $interview_type . "'" .
2839                            " WHERE interview=" . $interview,__LINE__,__FILE__);
2840           $this->int_view();
2841           return false;
2842         }
2843
2844       if($action == 'insert')
2845         {
2846           $notes = get_var('notes',array('POST'));
2847           $this->db->query("INSERT INTO tc_interview (interviewer,indiv,date,notes,interview_type) "
2848                            . "VALUES ('" . $interviewer . "','" . $indiv . "','"
2849                            . $date . "','" . $notes ."','" . $interview_type . "')",__LINE__,__FILE__);
2850           $this->int_view();
2851           return false;
2852         }
2853
2854       if($action == 'add')
2855         {
2856           $this->t->set_var('cal_date',$this->jscal->input('date','','','','','','',$this->cal_options));
2857           $this->t->set_var('interview', '');
2858           $this->t->set_var('interviewer', $interviewer);
2859           $this->t->set_var('name',$name);
2860           $this->t->set_var('indiv',$indiv);
2861           $this->t->set_var('date','');
2862           $this->t->set_var('notes','');
2863           $this->t->set_var('lang_done','Cancel');
2864           $this->t->set_var('lang_action','Adding New Interview');
2865           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_update&interview='
2866                                                                 . $interview . '&action=' . 'insert'));
2867         }
2868
2869       if($action == 'edit' || $action == 'view')
2870         {
2871           $sql = "SELECT * FROM tc_interview WHERE interview=".$interview;
2872           $this->db->query($sql,__LINE__,__FILE__);
2873           $this->db->next_record();
2874           $this->t->set_var('interview',$interview);
2875           $this->t->set_var('name',$name);
2876           $this->t->set_var('interviewer', $this->db->f('interviewer'));
2877           $this->t->set_var('indiv',$this->db->f('indiv'));
2878           $this->t->set_var('date',$this->db->f('date'));
2879           $this->t->set_var('notes',$this->db->f('notes'));
2880           if($this->db->f('interview_type') == 1) { $this->t->set_var('interview_type_checked','checked'); }
2881         }
2882
2883       if($action == 'edit')
2884         {
2885           $this->t->set_var('cal_date',$this->jscal->input('date',$date,'','','','','',$this->cal_options));
2886           $this->t->set_var('lang_done','Cancel');
2887           $this->t->set_var('lang_action','Editing Interview');
2888           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_update&interview='
2889                                                                 . $interview . '&action=' . 'save'));
2890         }
2891
2892       if($action == 'view')
2893         {
2894           $date = $this->db->f('date');
2895           $this->t->set_var('cal_date','<input type=text size="10" maxlength="10" name="date" value="'.$date.'" readonly>');
2896           $this->t->set_var('readonly','READONLY');
2897           $this->t->set_var('disabled','DISABLED');
2898           $this->t->set_var('lang_done','Done');
2899           $this->t->set_var('lang_action','Viewing Interview');
2900           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_update&interview='
2901                                                                 . $interview . '&action=' . 'edit'));
2902         }
2903
2904       $this->t->set_var('lang_reset','Clear Form');
2905       $this->t->set_var('lang_add','Add Interview');
2906       $this->t->set_var('lang_save','Save Changes');
2907       $this->t->set_var('edithandle','');
2908       $this->t->set_var('addhandle','');
2909
2910       $this->t->pfp('out','form');
2911
2912       if($action == 'view') { $this->t->set_var('lang_save','Edit Interview'); }
2913       if($action == 'edit' || $action == 'view') { $this->t->pfp('addhandle','edit'); }
2914       if($action == 'add') { $this->t->pfp('addhandle','add'); }
2915
2916       $this->save_sessiondata();
2917     }
2918
2919   function vis_view()
2920     {
2921       $this->t->set_file(array('vis_view_t' => 'vis_view.tpl'));
2922       $this->t->set_block('vis_view_t','visit_list','list1');
2923       $this->t->set_block('vis_view_t','family_list','list2');
2924
2925       $this->t->set_var('lang_name','Family Name');
2926       $this->t->set_var('lang_date','Date');
2927
2928       $this->t->set_var('vis_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_view'));
2929       $this->t->set_var('vis_link_title','View Yearly Visits');
2930
2931       $this->t->set_var('schedule_vis_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_sched'));
2932       $this->t->set_var('schedule_vis_link_title','Schedule Yearly Visits');
2933
2934       $this->t->set_var('linkurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_view'));
2935       $num_years = get_var('num_years',array('GET','POST'));
2936       if($num_years == '') { $num_years = $this->default_vis_num_years; }
2937       $this->t->set_var('num_years',$num_years);
2938       if($num_years == 1) { $this->t->set_var('lang_num_years','Year of History'); }
2939       else {  $this->t->set_var('lang_num_years','Years of History'); }
2940       $this->t->set_var('lang_filter','Filter');
2941
2942       $year = date('Y') - $num_years + 1;
2943       $year_start = $year - 1 . "-12-31"; $year_end = $year + 1 . "-01-01";
2944
2945       $sql = "SELECT * FROM tc_visit WHERE companionship=0 and date > '$year_start' ORDER BY date DESC";
2946       $this->db->query($sql,__LINE__,__FILE__);
2947       $total_records = $this->db->num_rows();
2948
2949       $i = 0;
2950       while ($this->db->next_record())
2951         {
2952           $visit_list[$i]['visit'] = $this->db->f('visit');
2953           $visit_list[$i]['family'] = $this->db->f('family');
2954           $visit_list[$i]['date']  = $this->db->f('date');
2955           $i++;
2956         }
2957
2958       for ($i=0; $i < count($visit_list); $i++)
2959         {
2960           $this->nextmatchs->template_alternate_row_color(&$this->t);
2961
2962           $sql = "SELECT * FROM tc_family WHERE family=".$visit_list[$i]['family'];
2963           $this->db->query($sql,__LINE__,__FILE__);
2964           $this->db->next_record();
2965
2966           $this->t->set_var('family',$visit_list[$i]['family']);
2967           $this->t->set_var('family_name',$this->db->f('name'));
2968           $this->t->set_var('date',$visit_list[$i]['date']);
2969
2970           $link_data['menuaction'] = 'tc.tc.vis_update';
2971           $link_data['visit'] = $visit_list[$i]['visit'];
2972           $link_data['name'] = $this->db->f('name');
2973           $link_data['date'] = $visit_list[$i]['date'];
2974           $link_data['action'] = 'view';
2975           $this->t->set_var('view',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
2976           $this->t->set_var('lang_view','View');
2977
2978           $link_data['menuaction'] = 'tc.tc.vis_update';
2979           $link_data['visit'] = $visit_list[$i]['visit'];
2980           $link_data['name'] = $this->db->f('name');
2981           $link_data['date'] = $visit_list[$i]['date'];
2982           $link_data['action'] = 'edit';
2983           $this->t->set_var('edit',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
2984           $this->t->set_var('lang_edit','Edit');
2985
2986           $this->t->fp('list1','visit_list',True);
2987         }
2988
2989       // List the families that are available to record a visit against
2990       $sql = "SELECT * FROM tc_family WHERE valid=1";
2991       $this->db->query($sql,__LINE__,__FILE__);
2992       $total_records = $this->db->num_rows();
2993
2994       $i = 0;
2995       while ($this->db->next_record())
2996         {
2997           $family_names[$i] = $this->db->f('name');
2998           $family_ids[$i] = $this->db->f('family');
2999           $i++;
3000         } array_multisort($family_names, $family_ids);
3001
3002       for ($i=0; $i < count($family_names); $i++)
3003         {
3004           $link_data['menuaction'] = 'tc.tc.vis_update';
3005           $link_data['visit'] = '';
3006           $link_data['family'] = $family_ids[$i];
3007           $link_data['action'] = 'add';
3008           $link_data['name'] = $family_names[$i];
3009           $this->t->set_var('add',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
3010
3011           $this->t->set_var('name',$family_names[$i]);
3012           if(($i+1) % 3 == 0) { $this->t->set_var('table_sep',"</td></tr><tr>"); }
3013           else { $this->t->set_var('table_sep',"</td>"); }
3014           if(($i) % 3 == 0) { $this->nextmatchs->template_alternate_row_color(&$this->t); }
3015
3016           $this->t->fp('list2','family_list',True);
3017         }
3018
3019       $this->t->pfp('out','vis_view_t');
3020       $this->save_sessiondata();
3021     }
3022
3023   function vis_update()
3024     {
3025       $this->t->set_file(array('form' => 'vis_update.tpl'));
3026       $this->t->set_block('form','add','addhandle');
3027       $this->t->set_block('form','edit','edithandle');
3028
3029       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_view'));
3030       $this->t->set_var('readonly','');
3031       $this->t->set_var('disabled','');
3032
3033       $action = get_var('action',array('GET','POST'));
3034       $visit = get_var('visit',array('GET','POST'));
3035       $family = get_var('family',array('GET','POST'));
3036       $name = get_var('name',array('GET','POST'));
3037       $date = get_var('date',array('GET','POST'));
3038       $notes = get_var('notes',array('GET','POST'));
3039       $companionship = 0;
3040
3041       if($action == 'save')
3042         {
3043           $notes = get_var('notes',array('POST'));
3044           $this->db->query("UPDATE tc_visit set " .
3045                            "  date='" . $date . "'" .
3046                           ", notes='" . $notes . "'" .
3047                            " WHERE visit=" . $visit,__LINE__,__FILE__);
3048           $this->vis_view();
3049           return false;
3050         }
3051
3052       if($action == 'insert')
3053         {
3054           $notes = get_var('notes',array('POST'));
3055           $this->db->query("INSERT INTO tc_visit (family,companionship,date,notes) "
3056                            . "VALUES ('" . $family . "','" . $companionship . "','"
3057                            . $date . "','" . $notes . "')",__LINE__,__FILE__);
3058           $this->vis_view();
3059           return false;
3060         }
3061
3062       if($action == 'add')
3063         {
3064           $this->t->set_var('cal_date',$this->jscal->input('date','','','','','','',$this->cal_options));
3065           $this->t->set_var('family', $family);
3066           $this->t->set_var('visit', '');
3067           $this->t->set_var('name', $name);
3068           $this->t->set_var('date','');
3069           $this->t->set_var('notes','');
3070           $this->t->set_var('lang_done','Cancel');
3071           $this->t->set_var('lang_action','Adding New Visit');
3072           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_update&family='
3073                                                                 . $family . '&action=' . 'insert'));
3074         }
3075
3076       if($action == 'edit' || $action == 'view')
3077         {
3078           $sql = "SELECT * FROM tc_visit WHERE visit=".$visit;
3079           $this->db->query($sql,__LINE__,__FILE__);
3080           $this->db->next_record();
3081           $this->t->set_var('visit',$visit);
3082           $this->t->set_var('name',$name);
3083           $this->t->set_var('family', $family);
3084           $this->t->set_var('date',$this->db->f('date'));
3085           $this->t->set_var('notes',$this->db->f('notes'));
3086         }
3087
3088       if($action == 'edit')
3089         {
3090           $this->t->set_var('cal_date',$this->jscal->input('date',$date,'','','','','',$this->cal_options));
3091           $this->t->set_var('lang_done','Cancel');
3092           $this->t->set_var('lang_action','Editing Visit');
3093           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_update&visit='
3094                                                                 . $visit . '&action=' . 'save'));
3095         }
3096
3097       if($action == 'view')
3098         {
3099           $date = $this->db->f('date');
3100           $this->t->set_var('cal_date','<input type=text size="10" maxlength="10" name="date" value="'.$date.'" readonly>');
3101           $this->t->set_var('readonly','READONLY');
3102           $this->t->set_var('disabled','DISABLED');
3103           $this->t->set_var('lang_done','Done');
3104           $this->t->set_var('lang_action','Viewing Visit');
3105           $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_update&visit='
3106                                                                 . $visit . '&action=' . 'edit'));
3107         }
3108
3109       $this->t->set_var('lang_reset','Clear Form');
3110       $this->t->set_var('lang_add','Add Visit');
3111       $this->t->set_var('lang_save','Save Changes');
3112       $this->t->set_var('edithandle','');
3113       $this->t->set_var('addhandle','');
3114
3115       $this->t->pfp('out','form');
3116
3117       if($action == 'view') { $this->t->set_var('lang_save','Edit Visit'); }
3118       if($action == 'edit' || $action == 'view') { $this->t->pfp('addhandle','edit'); }
3119       if($action == 'add') { $this->t->pfp('addhandle','add'); }
3120
3121       $this->save_sessiondata();
3122     }
3123
3124   function att_view()
3125     {
3126       $monthnum['Jan']=1; $monthnum['Feb']=2; $monthnum['Mar']=3; $monthnum['Apr']=4;
3127       $monthnum['May']=5; $monthnum['Jun']=6; $monthnum['Jul']=7; $monthnum['Aug']=8;
3128       $monthnum['Sep']=9; $monthnum['Oct']=10; $monthnum['Nov']=11; $monthnum['Dec']=12;
3129
3130       $this->t->set_file(array('att_view_t' => 'att_view.tpl'));
3131       $this->t->set_block('att_view_t','act_list','list');
3132
3133       $this->t->set_block('att_view_t','month_list','list1');
3134       $this->t->set_block('att_view_t','header_list','list2');
3135       $this->t->set_block('att_view_t','indiv_list','list3');
3136
3137       $this->t->set_var('linkurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.att_view'));
3138       $num_quarters = get_var('num_quarters',array('GET','POST'));
3139       if($num_quarters == '') { $num_quarters = $this->default_att_num_quarters; }
3140       $this->t->set_var('num_quarters',$num_quarters);
3141       $this->t->set_var('lang_filter','Filter');
3142       if($num_quarters == 1) { $this->t->set_var('lang_num_quarters','Quarter of History'); }
3143       else { $this->t->set_var('lang_num_quarters','Quarters of History'); }
3144
3145       $num_months = $num_quarters * 3;
3146       $current_month = $this->current_month;
3147       if($current_month >= 1 && $current_month <= 3) { $current_month=3; }
3148       else if($current_month >= 4 && $current_month <= 6) { $current_month=6; }
3149       else if($current_month >= 7 && $current_month <= 9) { $current_month=9; }
3150       else if($current_month >= 10 && $current_month <= 12) { $current_month=12; }
3151
3152       $sql = "SELECT * FROM tc_indiv where valid=1";
3153       $this->db->query($sql,__LINE__,__FILE__);
3154       $i=0;
3155       while ($this->db->next_record())
3156         {
3157           $indiv_name[$i] = $this->db->f('name');
3158           $indiv_id[$i] = $this->db->f('indiv');
3159           $i++;
3160         }
3161       array_multisort($indiv_name, $indiv_id);
3162
3163       // Create a list of sunday dates for a window of 3 months back and current month
3164       $i=0;
3165       $last_time = 0;
3166       $found_sunday = 0;
3167       $sunday_list[0]['date'] = date("Y-m-d", mktime(0, 0, 0, ($current_month-$num_months)+1, 1, date("y")));
3168       $last_date = explode("-",$sunday_list[0]['date']);
3169       $last_time = mktime(0, 0, 0, $last_date[1], $last_date[2], $last_date[0]);
3170       $time_limit = mktime(0, 0, 0, $current_month, 31, date("y"));
3171       while($last_time < $time_limit)
3172       {
3173         $day = date("w",$last_time);
3174         if(date("w",$last_time) == 0) {
3175           $sunday_list[$i]['date'] = date("Y-m-d", $last_time);
3176           $last_date = explode("-",$sunday_list[$i]['date']);
3177           $last_time = mktime(0, 0, 0, $last_date[1], $last_date[2], $last_date[0]);
3178           $sunday_list[$i]['day'] = $last_date[2];
3179           $sunday_list[$i]['month'] = date("M",$last_time);
3180           $sunday_list[$i]['year'] = $last_date[0];
3181           $found_sunday = 1;
3182           $last_date = $sunday_list[$i]['date'];
3183         }
3184         $last_time += 90000;
3185         if($found_sunday) { $i++; $found_sunday=0; }
3186       }
3187
3188       $total_indivs = count($indiv_id);
3189       $old_month=$sunday_list[0]['month']; $span=0;
3190       for ($i=0; $i < count($sunday_list); $i++) {
3191         $date = $sunday_list[$i]['date'];
3192         $this->t->set_var('date',$sunday_list[$i]['date']);
3193         $this->t->set_var('day',$sunday_list[$i]['day']);
3194         if(($old_month != $sunday_list[$i]['month']) || $i == count($sunday_list)-1) {
3195           if($i == count($sunday_list)-1) { $span++; }
3196           $cur_month = $sunday_list[$i]['month'];
3197           $old_month = $sunday_list[$i]['month'];
3198           $link_data['menuaction'] = 'tc.tc.att_update';
3199           $link_data['month'] = $sunday_list[$i-1]['month'];
3200           $link_data['year'] = $sunday_list[$i-1]['year'];
3201           $link_data['action'] = 'update_month';
3202           $cur_month = $sunday_list[$i-1]['month'];
3203           $cur_year = $sunday_list[$i-1]['year'];
3204           $header_row .= "<th><font size=-3>$cur_month&nbsp;$cur_year</font></th>";
3205           $this->t->set_var('update_month',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
3206           $this->t->set_var('month',$sunday_list[$i-1]['month']);
3207           $this->t->set_var('year',$sunday_list[$i-1]['year']);
3208           $this->t->set_var('span',$span); $span=0;
3209           $this->t->fp('list1','month_list',True);
3210         } $span++;
3211       }
3212       $this->t->set_var('total_indivs',$total_indivs);
3213       $this->t->set_var('header_row',$header_row);
3214
3215       $indiv_width=200; $att_width=25; $total_width=$indiv_width;
3216       for ($i=0; $i < count($sunday_list); $i++) {
3217         $link_data['menuaction'] = 'tc.tc.att_update';
3218         $link_data['month'] = $sunday_list[$i]['month'];
3219         $link_data['year'] = $sunday_list[$i]['year'];
3220         $link_data['day'] = $sunday_list[$i]['day'];
3221         $link_data['date'] = $sunday_list[$i]['date'];
3222         $link_data['action'] = 'update_day';
3223         $this->t->set_var('update_day',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
3224         $this->t->set_var('date',$sunday_list[$i]['date']);
3225         $this->t->set_var('day',$sunday_list[$i]['day']);
3226         $this->t->set_var('month',$sunday_list[$i]['month']);
3227         $this->t->set_var('year',$sunday_list[$i]['year']);
3228         $this->t->fp('list2','header_list',True);
3229         $total_width += $att_width;
3230         $attendance[$monthnum[$sunday_list[$i]['month']]]=0;
3231       }
3232
3233       for ($i=0; $i < count($indiv_id); $i++) {
3234         $att_table = "";
3235         $this->nextmatchs->template_alternate_row_color(&$this->t);
3236         $this->t->set_var('indiv_name',$indiv_name[$i]);
3237         #print "checking for indiv: " . $indiv_id[$i] . "<br>";
3238         for ($j=0; $j < count($sunday_list); $j++) {
3239           #print "checking for date: " .  $sunday_list[$j]['date'] . "<br>";
3240           #print "SELECT * FROM tc_attendance WHERE date='"
3241           #  . $sunday_list[$j]['date'] . "' AND indiv=" . $indiv_id[$i] . "<br>";
3242           $sql = "SELECT * FROM tc_attendance WHERE date='"
3243              . $sunday_list[$j]['date'] . "' AND indiv=" . $indiv_id[$i];
3244           $this->db->query($sql,__LINE__,__FILE__);
3245           if($this->db->next_record()) {
3246             $cur_month = $sunday_list[$j]['month'];
3247             if($attended[$i][$cur_month] != 1) {
3248               $attended[$i][$cur_month]=1;
3249               $attendance[$monthnum[$cur_month]]++;
3250             }
3251             $att_table .= '<td align=center><img src="images/checkmark.gif"></td>';
3252           } else {
3253             $att_table .= '<td>&nbsp;</td>';
3254           }
3255         }
3256         $this->t->set_var('att_table',$att_table);
3257         $this->t->fp('list3','indiv_list',True);
3258       }
3259       $this->t->set_var('total_width',$total_width);
3260       $this->t->set_var('indiv_width',$indiv_width);
3261       $this->t->set_var('att_width',$att_width);
3262
3263       # Now calculate attendance for these months
3264       $attendance_str = "";
3265       $nonattendance_str = "";
3266       $aveattendance_str = "";
3267       $avenonattendance_str = "";
3268       $num_months=0;
3269       $ave_total_attended=0;
3270       ksort($attendance);
3271       foreach($attendance as $att => $value) {
3272         $total_attended = $attendance[$att];
3273         $ave_total_attended += $attendance[$att]; $num_months++;
3274         $percent = ceil(($total_attended / $total_indivs)*100);
3275         $attendance_str.="<td align=center><font size=-2><b>$total_attended ($percent%)</b></font></td>";
3276         $total_nonattended = $total_indivs - $total_attended;
3277         $percent = ceil(($total_nonattended / $total_indivs)*100);
3278         $nonattendance_str.="<td align=center><font size=-2><b>$total_nonattended ($percent%)</b></font></td>";
3279
3280         $total_attended = ceil(($ave_total_attended / $num_months));
3281         $percent = ceil(($total_attended / $total_indivs)*100);
3282         $aveattendance_str .= "<td align=center><font size=-2><b>$total_attended ($percent%)</b></font></td>";
3283         $total_attended = $total_indivs - ceil(($ave_total_attended / $num_months));
3284         $percent = ceil(($total_attended / $total_indivs)*100);
3285         $avenonattendance_str .= "<td align=center><font size=-2><b>$total_attended ($percent%)</b></font></td>";
3286       }
3287
3288       $this->t->set_var('attendance',$attendance_str);
3289       $this->t->set_var('aveattendance',$aveattendance_str);
3290       $this->t->set_var('nonattendance',$nonattendance_str);
3291       $this->t->set_var('avenonattendance',$avenonattendance_str);
3292
3293       $this->t->pfp('out','att_view_t');
3294       $this->save_sessiondata();
3295     }
3296
3297   function att_update()
3298     {
3299       $monthnum['Jan']=1; $monthnum['Feb']=2; $monthnum['Mar']=3; $monthnum['Apr']=4;
3300       $monthnum['May']=5; $monthnum['Jun']=6; $monthnum['Jul']=7; $monthnum['Aug']=8;
3301       $monthnum['Sep']=9; $monthnum['Oct']=10; $monthnum['Nov']=11; $monthnum['Dec']=12;
3302
3303       $this->t->set_file(array('form' => 'att_update.tpl'));
3304       $this->t->set_block('form','edit','edithandle');
3305
3306       $this->t->set_block('form','month_list','list1');
3307       $this->t->set_block('form','header_list','list2');
3308       $this->t->set_block('form','indiv_list','list3');
3309
3310       $this->t->set_var('done_action',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.att_view'));
3311
3312       $action = get_var('action',array('GET','POST'));
3313       $month = get_var('month',array('GET','POST'));
3314       $year = get_var('year',array('GET','POST'));
3315       $day = get_var('day',array('GET','POST'));
3316       $date = get_var('date',array('GET','POST'));
3317
3318       if($action == 'save_month' || $action == 'save_day')
3319         {
3320            $new_data = get_var('indivs_attended',array('POST'));
3321            $month = $monthnum[$month]; if($month < 10) { $month = "0" . $month; }
3322
3323            if($action == 'save_month') {
3324              $this->db->query("DELETE from tc_attendance where date LIKE '".$year."-".$month."-%'",__LINE__,__FILE__);
3325            }
3326
3327            if($action == 'save_day') {
3328              $this->db->query("DELETE from tc_attendance where date LIKE '".$year."-".$month."-".$day."'",__LINE__,__FILE__);
3329            }
3330
3331            foreach ($new_data as $data)
3332            {
3333               $data_array = explode("-",$data);
3334               $indiv = $data_array[0];
3335               $date  = "$data_array[1]-$data_array[2]-$data_array[3]";
3336               $this->db->query("INSERT INTO tc_attendance (indiv,date) "
3337                                . "VALUES (" . $indiv . ",'". $date . "')",__LINE__,__FILE__);
3338            }
3339
3340          $this->att_view();
3341          return false;
3342         }
3343
3344       $sql = "SELECT * FROM tc_indiv where valid=1";
3345       $this->db->query($sql,__LINE__,__FILE__);
3346       $i=0;
3347       while ($this->db->next_record())
3348         {
3349           $indiv_name[$i] = $this->db->f('name');
3350           $indiv_id[$i] = $this->db->f('indiv');
3351           $indiv_attending[$indiv_id[$i]] = $this->db->f('attending');
3352           $i++;
3353         }
3354       array_multisort($indiv_name, $indiv_id);
3355
3356       if($action == 'update_month')
3357       {
3358         $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.att_update&action=save_month'));
3359         $i=0;
3360         $last_time = 0;
3361         $found_sunday = 0;
3362         $sunday_list[0]['date'] = date("Y-m-d", mktime(0, 0, 0, $monthnum[$month], 1, $year));
3363         $last_date = explode("-",$sunday_list[0]['date']);
3364         $last_time = mktime(0, 0, 0, $last_date[1], $last_date[2], $last_date[0]);
3365         $time_limit = mktime(0, 0, 0, $monthnum[$month], 31, $year);
3366         while($last_time <= $time_limit)
3367         {
3368           $day = date("w",$last_time);
3369           if(date("w",$last_time) == 0) {
3370             $sunday_list[$i]['date'] = date("Y-m-d", $last_time);
3371             $last_date = explode("-",$sunday_list[$i]['date']);
3372             $last_time = mktime(0, 0, 0, $last_date[1], $last_date[2], $last_date[0]);
3373             $sunday_list[$i]['day'] = $last_date[2];
3374             $sunday_list[$i]['month'] = date("M",$last_time);
3375             $sunday_list[$i]['year'] = $last_date[0];
3376             $found_sunday = 1;
3377           }
3378           $last_time += 90000;
3379           if($found_sunday) { $i++; $found_sunday=0; }
3380         }
3381
3382         $this->t->set_var('span', $i);
3383         $this->t->set_var('month',$sunday_list[$i-1]['month']);
3384         $this->t->set_var('year',$sunday_list[$i-1]['year']);
3385         $this->t->fp('list1','month_list',True);
3386         $indiv_width=200; $att_width=25; $total_width=$indiv_width;
3387         for ($i=0; $i < count($sunday_list); $i++) {
3388           $link_data['menuaction'] = 'tc.tc.att_update';
3389           $link_data['month'] = $sunday_list[$i]['month'];
3390           $link_data['year'] = $sunday_list[$i]['year'];
3391           $link_data['day'] = $sunday_list[$i]['day'];
3392           $link_data['date'] = $sunday_list[$i]['date'];
3393           $link_data['action'] = 'update_day';
3394           $this->t->set_var('update_day',$GLOBALS['phpgw']->link('/tc/index.php',$link_data));
3395           $this->t->set_var('date',$sunday_list[$i]['date']);
3396           $this->t->set_var('day',$sunday_list[$i]['day']);
3397           $this->t->set_var('month',$sunday_list[$i]['month']);
3398           $this->t->set_var('year',$sunday_list[$i]['year']);
3399           $this->t->fp('list2','header_list',True);
3400           $total_width += $att_width;
3401         }
3402       }
3403
3404       if($action == 'update_day')
3405       {
3406         $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.att_update&action=save_day'));
3407         $sunday_list[0]['date'] = date("Y-m-d", mktime(0, 0, 0, $monthnum[$month], $day, $year));
3408         $this->t->set_var('month',$month);
3409         $this->t->set_var('year',$year);
3410         $this->t->fp('list1','month_list',True);
3411         $this->t->set_var('date',$date);
3412         $this->t->set_var('day',$day);
3413         $this->t->set_var('month',$month);
3414         $this->t->set_var('year',$year);
3415         $this->t->fp('list2','header_list',True);
3416       }
3417
3418       for ($i=0; $i < count($indiv_id); $i++) {
3419         $att_table = "";
3420         $this->nextmatchs->template_alternate_row_color(&$this->t);
3421         $this->t->set_var('indiv_name',$indiv_name[$i]);
3422         for ($j=0; $j < count($sunday_list); $j++) {
3423           $sql = "SELECT * FROM tc_attendance WHERE date='"
3424              . $sunday_list[$j]['date'] . "' AND indiv=" . $indiv_id[$i];
3425           $this->db->query($sql,__LINE__,__FILE__);
3426           $value = $indiv_id[$i] . "-" . $sunday_list[$j]['date'];
3427           if($this->db->next_record()) {
3428             $att_table .= '<td align=center><input type="checkbox" name="indivs_attended[]" value="'.$value.'" checked></td>';
3429           } else if($indiv_attending[$indiv_id[$i]] == 1) {
3430             $att_table .= '<td align=center><input type="checkbox" name="indivs_attended[]" value="'.$value.'" checked></td>';
3431           } else {
3432             $att_table .= '<td align=center><input type="checkbox" name="indivs_attended[]" value="'.$value.'"></td>';
3433           }
3434         }
3435         $this->t->set_var('att_table',$att_table);
3436         $this->t->fp('list3','indiv_list',True);
3437       }
3438
3439       $this->t->set_var('lang_done', 'Cancel');
3440       $this->t->set_var('lang_reset','Clear Form');
3441       $this->t->set_var('lang_save','Save Changes');
3442
3443       $this->t->pfp('out','form');
3444       $this->t->pfp('addhandle','edit');
3445
3446       $this->save_sessiondata();
3447     }
3448
3449   function dir_view()
3450     {
3451       $this->t->set_file(array('dir_view_t' => 'dir_view.tpl'));
3452       $this->t->set_block('dir_view_t','dir_list','list');
3453
3454       $sql = "SELECT * FROM tc_indiv where valid=1 and hh_position='Head of Household' ORDER BY name ASC";
3455       $this->db->query($sql,__LINE__,__FILE__);
3456       $i=0;
3457       while ($this->db->next_record())
3458         {
3459           $parent[$i]['id'] = $this->db->f('indiv');
3460           $parent[$i]['name'] = $this->db->f('name');
3461           $parent[$i]['phone'] = $this->db->f('phone');
3462           $parent[$i]['address'] = $this->db->f('address');
3463           $i++;
3464         }
3465
3466       for ($i=0; $i < count($parent); $i++)
3467       {
3468         $name = $parent[$i]['name'];
3469         $phone = $parent[$i]['phone'];
3470         $address = $parent[$i]['address'];
3471         $this->t->set_var('name', $name);
3472         $this->t->set_var('address', $address);
3473         $this->t->set_var('phone', $phone);
3474         $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
3475         $this->t->set_var('tr_color',$tr_color);
3476         $this->t->fp('list','dir_list',True);
3477         //print "$phone $name $address<br>";
3478       }
3479       $this->t->pfp('out','dir_view_t');
3480       $this->save_sessiondata();
3481     }
3482
3483   function org_view()
3484     {
3485       $this->t->set_file(array('org_view_t' => 'org_view.tpl'));
3486       $this->t->set_block('org_view_t','calling_list','list1');
3487       $this->t->set_block('org_view_t','org_list','list2');
3488
3489       # Display a list ordered alphabetically
3490       $sql = "SELECT * FROM tc_calling ORDER BY name ASC";
3491       $this->db->query($sql,__LINE__,__FILE__);
3492       $i=0;
3493       while ($this->db->next_record())
3494         {
3495           $calling[$i]['id'] = $this->db->f('indiv_id');
3496           $calling[$i]['name'] = $this->db->f('name');
3497           $calling[$i]['position'] = $this->db->f('position');
3498           $calling[$i]['sustained'] = $this->db->f('sustained');
3499           $calling[$i]['organization'] = $this->db->f('organization');
3500           $i++;
3501         }
3502       for ($i=0; $i < count($calling); $i++)
3503       {
3504         $name = $calling[$i]['name'];
3505         $position = $calling[$i]['position'];
3506         $sustained = $calling[$i]['sustained'];
3507         $organization = $calling[$i]['organization'];
3508         $this->t->set_var('name', $name);
3509         $this->t->set_var('position', $position);
3510         $this->t->set_var('sustained', $sustained);
3511         $this->t->set_var('organization', $organization);
3512         $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
3513         $this->t->set_var('tr_color',$tr_color);
3514         $this->t->fp('list1','calling_list',True);
3515       }
3516
3517       # Display a list ordered by organization
3518       $sql = "SELECT * FROM tc_calling ORDER BY sequence ASC";
3519       $this->db->query($sql,__LINE__,__FILE__);
3520       $i=0;
3521       while ($this->db->next_record())
3522         {
3523           $calling[$i]['id'] = $this->db->f('indiv_id');
3524           $calling[$i]['name'] = $this->db->f('name');
3525           $calling[$i]['position'] = $this->db->f('position');
3526           $calling[$i]['sustained'] = $this->db->f('sustained');
3527           $calling[$i]['organization'] = $this->db->f('organization');
3528           $i++;
3529         }
3530       for ($i=0; $i < count($calling); $i++)
3531       {
3532         $name = $calling[$i]['name'];
3533         $position = $calling[$i]['position'];
3534         $sustained = $calling[$i]['sustained'];
3535         $organization = $calling[$i]['organization'];
3536         $this->t->set_var('name', $name);
3537         $this->t->set_var('position', $position);
3538         $this->t->set_var('sustained', $sustained);
3539         $this->t->set_var('organization', $organization);
3540         $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
3541         $this->t->set_var('tr_color',$tr_color);
3542         $this->t->fp('list2','org_list',True);
3543       }
3544
3545       $this->t->pfp('out','org_view_t');
3546       $this->save_sessiondata();
3547     }
3548
3549   function schedule()
3550     {
3551       $this->t->set_file(array('sched_t' => 'schedule.tpl'));
3552       $this->t->set_block('sched_t','presidency_list','list');
3553
3554       $action = get_var('action',array('GET','POST'));
3555
3556       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.schedule&action=save'));
3557       $this->t->set_var('title','Scheduling Tool');
3558
3559       $this->t->set_var('lang_save','Save Schedule');
3560       $this->t->set_var('lang_reset','Cancel');
3561
3562       $this->t->set_var('schedule_vis_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_sched'));
3563       $this->t->set_var('schedule_vis_link_title','Schedule Yearly Visits');
3564
3565       $this->t->set_var('schedule_int_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_sched'));
3566       $this->t->set_var('schedule_int_link_title','Schedule Hometeaching Interviews');
3567
3568       $this->t->set_var('schedule_ppi_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_sched'));
3569       $this->t->set_var('schedule_ppi_link_title','Schedule Yearly PPIs');
3570
3571       $date_width=150; $time_width=220; $indiv_width=170; $family_width=180; $location_width=100;
3572       $table_width=$date_width + $time_width + $indiv_width + $family_width + $location_width;
3573       $header_row = "<th width=$date_width><font size=-2>Date</th>";
3574       $header_row.= "<th width=$time_width><font size=-2>Time</th>";
3575       $header_row.= "<th width=$indiv_width><font size=-2>individual</th>";
3576       $header_row.= "<th width=$family_width><font size=-2>Family</th>";
3577       $header_row.= "<th width=$location_width><font size=-2>Location</th>";
3578       $table_data = "";
3579
3580       $sql = "SELECT * FROM tc_presidency where valid=1";
3581       $this->db->query($sql,__LINE__,__FILE__);
3582       $i=0;
3583       while ($this->db->next_record())
3584         {
3585           $presidency_data[$i]['id'] = $this->db->f('presidency');
3586           $presidency_data[$i]['name'] = $this->db->f('name');
3587           $presidency_data[$i]['indiv'] = $this->db->f('indiv');
3588           $presidency2name[$presidency_data[$i]['id']] = $presidency_data[$i]['name'];
3589           $presidency2indiv[$presidency_data[$i]['id']] = $presidency_data[$i]['indiv'];
3590           $i++;
3591         }
3592
3593       $sql = "SELECT * FROM tc_family where valid=1 and indiv_id != 0 ORDER BY name ASC";
3594       $this->db->query($sql,__LINE__,__FILE__);
3595       $i=0;
3596       while ($this->db->next_record())
3597         {
3598           $family_id[$i] = $this->db->f('family');
3599           $family_name[$i] = $this->db->f('name');
3600           $familyid2name[$family_id[$i]] = $family_name[$i];
3601           $sql = "SELECT * FROM tc_indiv where family='$family_id[$i]' and hh_position='Head of Household'";
3602           $this->db2->query($sql,__LINE__,__FILE__);
3603           if($this->db2->next_record()) {
3604             $familyid2address[$family_id[$i]] = $this->db2->f('address');
3605           }
3606           $i++;
3607         }
3608       array_multisort($family_name, $family_id);
3609
3610       if($action == 'save')
3611         {
3612           $new_data = get_var('sched',array('POST'));
3613           foreach ($new_data as $presidency_array)
3614            {
3615              foreach ($presidency_array as $entry)
3616                {
3617                  $presidency = $entry['presidency'];
3618                  $appointment = $entry['appointment'];
3619                  $location = $entry['location'];
3620                  $date = $entry['date'];
3621                  $hour = $entry['hour'];
3622                  $minute = $entry['minute'];
3623                  $pm = $entry['pm'];
3624                  $indiv = $entry['indiv'];
3625                  $family = $entry['family'];
3626                  $location = $entry['location'];
3627                  if($pm) { $hour = $hour + 12; }
3628                  $time = $hour.':'.$minute.':'.'00';
3629                  $uid = 0;
3630
3631                  // Update our location
3632                  if($location == "") {
3633                    if($family > 0) {
3634                      $family_name_array = explode(",", $familyid2name[$family]);
3635                      $family_last_name = $family_name_array[0];
3636                      $family_address = $familyid2address[$family];
3637                      $location = "$family_last_name"." home ($family_address)";
3638                    }
3639                    else if($indiv > 0) {
3640                      $supervisor_name_array = explode(",",$presidency2name[$presidency]);
3641                      $supervisor_last_name = $supervisor_name_array[0];
3642                      $sql = "SELECT * FROM tc_indiv where indiv='$presidency2indiv[$presidency]'";
3643                      $this->db2->query($sql,__LINE__,__FILE__);
3644                      if($this->db2->next_record()) {
3645                        $indiv_id = $this->db2->f('indiv_id');
3646                      }
3647                      $sql = "SELECT * FROM tc_indiv where indiv_id='$indiv_id'";
3648                      $this->db2->query($sql,__LINE__,__FILE__);
3649                      if($this->db2->next_record()) {
3650                        $supervisor_address = $this->db2->f('address');
3651                      }
3652                      $location = "$supervisor_last_name"." home ($supervisor_address)";
3653                    }
3654                  }
3655
3656                  // Zero out the family or individual if date = NULL
3657                  if($date == "") {
3658                    $indiv = 0;
3659                    $family = 0;
3660                    $location = "";
3661                  }
3662
3663                  if(($indiv == 0) && ($family == 0)) { $location = ""; }
3664
3665                  // Update an existing appointment
3666                  if($appointment < $this->max_appointments)
3667                    {
3668                      //Only perform a database update if we have made a change to this appointment
3669                      $sql = "SELECT * FROM tc_appointment where " .
3670                         "appointment='$appointment'" .
3671                         " and presidency='$presidency'" .
3672                         " and indiv='$indiv'" .
3673                         " and family='$family'" .
3674                         " and date='$date'" .
3675                         " and time='$time'" .
3676                         " and location='$location'";
3677                      $this->db->query($sql,__LINE__,__FILE__);
3678                      if(!$this->db->next_record()) {
3679                        $old_date = $this->db->f('date');
3680                        $old_time = $this->db->f('time');
3681                        $this->db2->query("UPDATE tc_appointment set" .
3682                                         " family=" . $family .
3683                                         " ,indiv=" . $indiv .
3684                                         " ,date='" . $date . "'" .
3685                                         " ,time='" . $time . "'" .
3686                                         " ,location='" . $location . "'" .
3687                                         " ,presidency='" . $presidency . "'" .
3688                                         " WHERE appointment=" . $appointment,__LINE__,__FILE__);
3689
3690                        // Email the appointment
3691                        $this->email_appt($appointment);
3692                      }
3693                    }
3694
3695                  // Add a new appointment
3696                  else if(($appointment >= $this->max_appointments) && ($date != "") && ($time != ""))
3697                    {
3698                      //print "adding entry: appt=$appointment date: $date time: $time indiv: $indiv family: $family<br>";
3699                      $this->db2->query("INSERT INTO tc_appointment (appointment,presidency,family,indiv,date,time,location,uid) "
3700                            . "VALUES (NULL,'" . $presidency . "','" . $family . "','" . $indiv . "','"
3701                            . $date . "','" . $time  . "','" . $location . "','" . $uid ."')",__LINE__,__FILE__);
3702
3703                      // Now reselect this entry from the database to see if we need
3704                      // to send an appointment out for it.
3705                      $sql = "SELECT * FROM tc_appointment where " .
3706                         "indiv='$indiv'" .
3707                         " and family='$family'" .
3708                         " and presidency='$presidency'" .
3709                         " and date='$date'" .
3710                         " and time='$time'" .
3711                         " and uid='$uid'" .
3712                         " and location='$location'";
3713                      $this->db3->query($sql,__LINE__,__FILE__);
3714                      if($this->db3->next_record()) {
3715                        // Email the appointment if warranted
3716                        if(($date != "") && ($time != "") && (($indiv > 0) || $family > 0)) {
3717                          $this->email_appt($this->db3->f('appointment'));
3718                        }
3719                      }
3720                    }
3721                }
3722            }
3723
3724           $take_me_to_url = $GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.schedule');
3725           //Header('Location: ' . $take_me_to_url);
3726         }
3727
3728       $sql = "SELECT * FROM tc_indiv where valid=1 ORDER BY indiv ASC";
3729       $this->db->query($sql,__LINE__,__FILE__);
3730       $i=0;
3731       while ($this->db->next_record())
3732         {
3733           $indiv_id[$i] = $this->db->f('indiv');
3734           $indiv_name[$i] = $this->db->f('name');
3735           $indiv_phone[$indiv_id[$i]] = $this->db->f('phone');
3736           $i++;
3737         }
3738       array_multisort($indiv_name, $indiv_id);
3739
3740       for ($i=0; $i < count($presidency_data); $i++) {
3741         $presidency = $presidency_data[$i]['id'];
3742         $interviewer = $presidency_data[$i]['indiv'];
3743         $name = $presidency_data[$i]['name'];
3744         $this->t->set_var('presidency_name',$name);
3745         $table_data="";
3746
3747         // query the database for all the appointments
3748         $sql = "SELECT * FROM tc_appointment where presidency=$presidency and date>=CURDATE() ORDER BY date ASC, time ASC";
3749         $this->db->query($sql,__LINE__,__FILE__);
3750
3751         // Prefill any existing appointment slots
3752         while ($this->db->next_record())
3753           {
3754             $appointment = $this->db->f('appointment');
3755             $indiv = $this->db->f('indiv');
3756             $family = $this->db->f('family');
3757             $location = $this->db->f('location');
3758
3759             if($location == "") {
3760               if($family > 0) {
3761                 $family_name_array = explode(",", $familyid2name[$family]);
3762                 $family_last_name = $family_name_array[0];
3763                 $family_address = $familyid2address[$family];
3764                 $location = "$family_last_name"." home ($family_address)";
3765               }
3766               else if($indiv > 0) {
3767                 $supervisor_name_array = explode(",",$presidency2name[$presidency]);
3768                 $supervisor_last_name = $supervisor_name_array[0];
3769                 $sql = "SELECT * FROM tc_indiv where indiv='$presidency2indiv[$presidency]'";
3770                 $this->db2->query($sql,__LINE__,__FILE__);
3771                 if($this->db2->next_record()) {
3772                   $indiv_id = $this->db2->f('indiv_id');
3773                 }
3774                 $sql = "SELECT * FROM tc_indiv where indiv_id='$indiv_id'";
3775                 $this->db2->query($sql,__LINE__,__FILE__);
3776                 if($this->db2->next_record()) {
3777                   $supervisor_address = $this->db2->f('address');
3778                 }
3779                 $location = "$supervisor_last_name"." home ($supervisor_address)";
3780               }
3781             }
3782
3783             $date = $this->db->f('date');
3784             $date_array = explode("-",$date);
3785             $year = $date_array[0]; $month = $date_array[1]; $day = $date_array[2];
3786             $day_string = date("l d-M-Y", mktime(0,0,0,$month,$day,$year));
3787
3788             $time = $this->db->f('time');
3789             $time_array = explode(":",$time);
3790             $hour = $time_array[0];
3791             $minute = $time_array[1];
3792             $pm = 0;
3793             if($hour > 12) { $pm=1; $hour = $hour - 12; }
3794             $time_string = date("g:i a", mktime($time_array[0], $time_array[1], $time_array[2]));
3795
3796             $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
3797
3798             // Date selection
3799             $table_data.= '<td align=left>';
3800             $table_data.= $this->jscal->input('sched['.$presidency.']['.$appointment.'][date]',$date,'','','','','',$this->cal_options);
3801             $table_data.= '</td>';
3802
3803             // Hour & Minutes selection
3804             $table_data.= "<td align=center>";
3805             $table_data .= $this->get_time_selection_form($hour, $minute, $pm, $presidency, $appointment);
3806             $table_data.= "</td>";
3807
3808             // individual drop down list (for PPIs)
3809             $table_data.= '<td align=center><select name=sched['.$presidency.']['.$appointment.'][indiv] STYLE="font-size : 8pt">';
3810             $table_data.= '<option value=0></option>';
3811             for ($j=0; $j < count($indiv_id); $j++) {
3812               $id = $indiv_id[$j];
3813               $name = $indiv_name[$j];
3814               if($indiv_id[$j] == $indiv) { $selected[$id] = 'selected="selected"'; } else { $selected[$id] = ''; }
3815               $table_data.= '<option value='.$id.' '.$selected[$id].'>'.$name.'</option>';
3816             }
3817             $table_data.='</select></td>';
3818
3819             // Family drop down list (for Visits)
3820             $table_data.= '<td align=center><select name=sched['.$presidency.']['.$appointment.'][family] STYLE="font-size : 8pt">';
3821             $table_data.= '<option value=0></option>';
3822             for ($j=0; $j < count($indiv_id); $j++) {
3823               $id = $family_id[$j];
3824               $name = $family_name[$j];
3825               if($family_id[$j] == $family) { $selected[$id] = 'selected="selected"'; } else { $selected[$id] = ''; }
3826               $table_data.= '<option value='.$id.' '.$selected[$id].'>'.$name.' Family</option>';
3827             }
3828             $table_data.='</select></td>';
3829
3830             // Location text box
3831             $table_data.= '<td align=center><input type=text size="25" maxlength="120" ';
3832             $table_data.= 'name="sched['.$presidency.']['.$appointment.'][location]" value="'.$location.'" STYLE="font-size : 8pt">';
3833
3834             $table_data.= '<input type=hidden name="sched['.$presidency.']['.$appointment.'][appointment]" value="'.$appointment.'">';
3835             $table_data.= '<input type=hidden name="sched['.$presidency.']['.$appointment.'][presidency]" value="'.$presidency.'">';
3836
3837             $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
3838             $this->t->set_var('tr_color',$tr_color);
3839
3840           }
3841
3842         // Create blank appointment slot
3843         for ($b=0; $b < 4; $b++) {
3844           $appointment = $this->max_appointments + $b;
3845           $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
3846
3847           // Date selection
3848           $table_data.= '<td align=left>';
3849           $table_data.= $this->jscal->input('sched['.$presidency.']['.$appointment.'][date]','','','','','','',$this->cal_options);
3850           $table_data.= '</td>';
3851
3852           // Time selection
3853           $table_data.= "<td align=center>";
3854           $table_data .= $this->get_time_selection_form(0, 0, 0, $presidency, $appointment);
3855           $table_data.= "</td>";
3856
3857           // individual drop down list
3858           $table_data.= '<td align=center><select name=sched['.$presidency.']['.$appointment.'][indiv] STYLE="font-size : 8pt">';
3859           $table_data.= '<option value=0></option>';
3860           for ($j=0; $j < count($indiv_id); $j++) {
3861             $id = $indiv_id[$j];
3862             $name = $indiv_name[$j];
3863             $table_data.= '<option value='.$id.'>'.$name.'</option>';
3864           }
3865           $table_data.='</select></td>';
3866
3867           // Family drop down list
3868           $table_data.= '<td align=center><select name=sched['.$presidency.']['.$appointment.'][family] STYLE="font-size : 8pt">';
3869           $table_data.= '<option value=0></option>';
3870           for ($j=0; $j < count($indiv_id); $j++) {
3871             $id = $family_id[$j];
3872             $name = $family_name[$j];
3873             $table_data.= '<option value='.$id.'>'.$name.' Family</option>';
3874           }
3875           $table_data.='</select></td>';
3876
3877           // Location text box
3878           $table_data.= '<td align=center><input type=text size="25" maxlength="120" ';
3879           $table_data.= 'name="sched['.$presidency.']['.$appointment.'][location]" value="" STYLE="font-size : 8pt">';
3880
3881           $table_data.= '<input type=hidden name="sched['.$presidency.']['.$appointment.'][appointment]" value="'.$appointment.'">';
3882           $table_data.= '<input type=hidden name="sched['.$presidency.']['.$appointment.'][presidency]" value="'.$presidency.'">';
3883
3884           $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
3885           $this->t->set_var('tr_color',$tr_color);
3886         }
3887
3888         $this->t->set_var('table_data',$table_data);
3889         $this->t->set_var('header_row',$header_row);
3890         $this->t->set_var('table_width',$table_width);
3891         $this->t->fp('list','presidency_list',True);
3892
3893       }
3894
3895       $this->t->pfp('out','sched_t');
3896       $this->save_sessiondata();
3897     }
3898
3899   function email()
3900     {
3901       $this->t->set_file(array('email_t' => 'email.tpl'));
3902       $this->t->set_block('email_t','indiv_list','list');
3903
3904       $action = get_var('action',array('GET','POST'));
3905
3906       $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.email'));
3907       $this->t->set_var('title','Email Tool');
3908
3909       $this->t->set_var('lang_email','Send Email');
3910       $this->t->set_var('lang_reset','Cancel');
3911
3912       $this->t->set_var('email_member_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.email&action=member'));
3913       $this->t->set_var('email_member_link_title','Email Quorum Member');
3914
3915       $this->t->set_var('email_quorum_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.email&action=quorum'));
3916       $this->t->set_var('email_quorum_link_title','Email Quorum');
3917
3918       $this->t->set_var('email_reminder_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.email&action=reminder'));
3919       $this->t->set_var('email_reminder_link_title','Email Reminders');
3920
3921       $this->t->set_var('email_edit_link',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.email&action=edit'));
3922       $this->t->set_var('email_edit_link_title','Edit Email Addresses');
3923
3924       $table_width=600;
3925       $this->t->set_var('table_width',$table_width);
3926
3927       $this->t->pfp('out','email_t');
3928       $this->save_sessiondata();
3929     }
3930
3931   function admin()
3932     {
3933       $this->t->set_file(array('admin_t' => 'admin.tpl'));
3934       $this->t->set_block('admin_t','upload','uploadhandle');
3935       $this->t->set_block('admin_t','admin','adminhandle');
3936       $this->t->set_block('admin_t','cmd','cmdhandle');
3937       $this->t->set_block('admin_t','presidency','presidencyhandle');
3938
3939       $this->t->set_var('upload_action',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.admin&action=upload'));
3940       $this->t->set_var('presidency_action',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.admin&action=presidency'));
3941
3942       $action = get_var('action',array('GET','POST'));
3943
3944       $this->t->pfp('out','admin_t');
3945
3946       $sql = "SELECT * FROM tc_indiv where valid=1 ORDER BY indiv ASC";
3947       $this->db->query($sql,__LINE__,__FILE__);
3948       $i=0;
3949       while ($this->db->next_record())
3950         {
3951           $indiv_id[$i] = $this->db->f('indiv');
3952           $indiv_name[$i] = $this->db->f('name');
3953           $indiv2name[$indiv_id[$i]] = $indiv_name[$i];
3954           $i++;
3955         }
3956       array_multisort($indiv_name, $indiv_id);
3957
3958       if($action == 'upload')
3959         {
3960           $target_path = $this->upload_target_path . '/' . basename( $_FILES['uploadedfile']['name']);
3961
3962           if(($_FILES['uploadedfile']['type'] == "application/zip") ||
3963              ($_FILES['uploadedfile']['type'] == "application/x-zip-compressed") ||
3964              ($_FILES['uploadedfile']['type'] == "application/x-zip") ||
3965              ($_FILES['uploadedfile']['type'] == "application/octet-stream")) {
3966
3967             if(!move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path)) {
3968               $uploadstatus = "<b><font color=red> -E- Unable to move the uploaded file to ";
3969               $uploadstatus.= "the target path (check the path and permissions) of: $target_path</font></b>";
3970               $uploadstatus = "<b>The following file was uploaded successfully: </b><br><br>";
3971               $uploadstatus.= "Tmp Filename : " . $_FILES['uploadedfile']['tmp_name'] . "<br>";
3972               $uploadstatus.= "Filename     : " . $_FILES['uploadedfile']['name'] . "<br>";
3973               $uploadstatus.= "Type         : " . $_FILES['uploadedfile']['type'] . "<br>";
3974               $uploadstatus.= "Size         : " . $_FILES['uploadedfile']['size'] . "<br>";
3975               $uploadstatus.= "Error        : " . $_FILES['uploadedfile']['error'] . "<br>";
3976               $this->t->set_var('uploadstatus',$uploadstatus);
3977               $this->t->pfp('uploadhandle','upload',True);
3978               return 0;
3979             }
3980
3981             $uploadstatus = "<b>The following file was uploaded successfully: </b><br><br>";
3982             $uploadstatus.= "Filename : " . $_FILES['uploadedfile']['name'] . "<br>";
3983             $uploadstatus.= "Type     : " . $_FILES['uploadedfile']['type'] . "<br>";
3984             $uploadstatus.= "Size     : " . $_FILES['uploadedfile']['size'] . "<br>";
3985             $this->t->set_var('uploadstatus',$uploadstatus);
3986             $this->t->pfp('uploadhandle','upload');
3987             $this->t->set_var('uploadhandle','');
3988             print "<table border=1 width=80%><tr><td>\n<pre>";
3989
3990             # make a directory for this data to be stored in
3991             $date="data_" . date("Y_m_d");
3992             $data_dir = $this->upload_target_path . '/' . $date;
3993             print "-> Making the data directory: $date<br>\n";
3994             exec('mkdir -p ' . $data_dir . ' 2>&1', $result, $return_code);
3995             if($return_code != 0) {
3996               print implode('\n',$result) . "<br>";
3997               print "<b><font color=red>";
3998               print "-E- Unable to create the data directory. Aborting import.";
3999               print "</font></b>";
4000               return 0;
4001             }
4002
4003             # move the file uploaded into this directory
4004             print "-> Moving the uploaded file into the data dir<br>\n";
4005             exec('mv ' . $target_path . ' ' . $data_dir . '/' . ' 2>&1', $result, $return_code);
4006             if($return_code != 0) {
4007               print implode('\n',$result) . "<br>";
4008               print "<b><font color=red>";
4009               print "-E- Unable to move the uploaded file into the data dir. Aborting import.";
4010               print "</font></b>";
4011               return 0;
4012             }
4013
4014             # unzip the data into this directory
4015             print "-> Unzipping the data<br>\n";
4016             exec($this->unzip_path .' -u '. $data_dir . '/*.zip -d ' . $data_dir . ' 2>&1', $result, $return_code);
4017             if($return_code != 0) {
4018               print implode('\n',$result) . "<br>";
4019               print "<b><font color=red>";
4020               print "-E- Unable to unzip the uploaded file into the data dir: $data_dir. Aborting import.";
4021               print "</font></b>";
4022               return 0;
4023             }
4024             exec('mv ' . $data_dir . '/*/* '. $data_dir . ' 2>&1', $result, $return_code);
4025
4026             # update the data_latest link to point to this new directory
4027             print "-> Updating the latest data dir link<br>\n";
4028             $data_latest = $this->upload_target_path . '/data_latest';
4029             exec('rm ' . $data_latest. '; ln -s ' . $data_dir .' '. $data_latest .' 2>&1', $result, $return_code);
4030             if($return_code != 0) {
4031               print implode('\n',$result) . "<br>";
4032               print "<b><font color=red>";
4033               print "-E- Unable to update the data latest link. Aborting import.";
4034               print "</font></b>";
4035               return 0;
4036             }
4037
4038             # run the import perl script to encorporate it into the DB
4039             ob_start('ob_logstdout', 2);
4040             print "-> Importing the data into the database<br>\n";
4041             ob_flush(); flush(); sleep(1);
4042             $import_log = $this->upload_target_path . '/import.log';
4043             $data_log = $this->upload_target_path . '/data.log';
4044             $import_cmd = $this->script_path . '/import_ward_data ' . $data_latest . ' 2>&1 | tee ' . $import_log;
4045             $parse_cmd = $this->script_path . '/parse_ward_data -v ' . $data_latest . ' > ' . $data_log . '2>&1';
4046             #print "import_cmd: $import_cmd<br>";
4047             #print "parse_cmd: $parse_cmd<br>";
4048             ob_start('ob_logstdout', 2);
4049             passthru($import_cmd);
4050             passthru($parse_cmd);
4051             ob_flush(); flush(); sleep(1);
4052
4053             # fix the permissions of the data dir
4054             exec('chmod -R o-rwx ' . $data_dir, $result, $return_code);
4055
4056             $this->t->pfp('cmdhandle','cmd');
4057             print "</pre></td></tr></table>";
4058
4059           } else if(($_FILES['uploadedfile']['type'] != "application/zip") &&
4060                     ($_FILES['uploadedfile']['type'] != "application/x-zip-compressed") &&
4061                     ($_FILES['uploadedfile']['type'] != "application/x-zip") &&
4062                     ($_FILES['uploadedfile']['type'] != "application/octet-stream")) {
4063             $uploadstatus = "<b><font color=red>The file format must be a .zip file, please try again! </font></b>";
4064             $uploadstatus.= "<br><br><b>Detected file format: " . $_FILES['uploadedfile']['type'] . "</b>";
4065             $this->t->set_var('uploadstatus',$uploadstatus);
4066             $this->t->pfp('uploadhandle','upload',True);
4067           } else {
4068             $uploadstatus = "<b><font color=red> There was an error (" . $_FILES['uploadedfile']['error'];
4069             $uploadstatus.= ") uploading the file, please try again! </font></b>";
4070             $this->t->set_var('uploadstatus',$uploadstatus);
4071             $this->t->pfp('uploadhandle','upload',True);
4072           }
4073         }
4074       else if($action == "presidency")
4075         {
4076           $new_data = get_var('eqpres',array('POST'));
4077           foreach ($new_data as $entry)
4078            {
4079              $id = $entry['id'];
4080              $email = $entry['email'];
4081              $indiv = $entry['indiv'];
4082              $name = $entry['name'];
4083              $district = $entry['district'];
4084              $president = $entry['president'];
4085              $counselor = $entry['counselor'];
4086              $secretary = $entry['secretary'];
4087              $eqpresidency = $entry['eqpresidency'];
4088              // Set the individual id to 0 for EQ Presidency tagged entry
4089              if($eqpresidency == 1) { $indiv="0"; }
4090              // Re-look up the individual name for the ID if we aren't an EQ Presidency tagged entry
4091              else { $name = $indiv2name[$indiv]; }
4092              //print "id=$id indiv=$indiv name=$name email=$email district=$district president=$president ";
4093              //print "counselor=$counselor secretary=$secretary eqpres=$eqpresidency<br>";
4094
4095              if(($indiv > 0) || ($name != "")) {
4096                if($id < $this->max_presidency_members) {
4097                  //print "Updating Existing Entry<br>";
4098                  $this->db2->query("UPDATE tc_presidency set" .
4099                                    " indiv=" . $indiv .
4100                                    " ,district=" . $district .
4101                                    " ,name='" . $name . "'" .
4102                                    " ,email='" . $email . "'" .
4103                                    " ,president='" . $president . "'" .
4104                                    " ,counselor='" . $counselor . "'" .
4105                                    " ,secretary='" . $secretary . "'" .
4106                                    " ,eqpres='" . $eqpresidency . "'" .
4107                                    " WHERE presidency=" . $id,__LINE__,__FILE__);
4108
4109                } else {
4110                  //print "Adding New Entry<br>";
4111                  $this->db2->query("INSERT INTO tc_presidency (presidency,indiv,district,name,"
4112                                    . "email,president,counselor,secretary,eqpres,valid) "
4113                                    . "VALUES (NULL,'" . $indiv . "','" . $district . "','"
4114                                    . $name . "','" . $email . "','" . $president  . "','"
4115                                    . $counselor . "','" . $secretary . "','" . $eqpres  . "','1'"
4116                                    .")",__LINE__,__FILE__);
4117                }
4118              } else {
4119                //print "Ignoring Blank Entry<br>";
4120              }
4121            }
4122
4123           // Now update the tc_district table appropriately
4124
4125           // Delete all the previous district entries from the table
4126           $this->db->query("DELETE from tc_district where valid=1",__LINE__,__FILE__);
4127           $this->db->query("DELETE from tc_district where valid=0",__LINE__,__FILE__);
4128
4129           // Always add a "District 0" assigned to the High Priests Group
4130           $district = 0;
4131           $name = "High Priests";
4132           $indiv = 0;
4133           $valid = 0;
4134           $this->db2->query("INSERT INTO tc_district (district,name,supervisor,valid) "
4135                             . "VALUES ('" . $district . "','" . $name . "','"
4136                             . $indiv . "','" . $valid . "'"
4137                             .")",__LINE__,__FILE__);
4138
4139
4140           // Requery the tc_presidency table
4141           $sql = "SELECT * FROM tc_presidency where valid=1";
4142           $this->db->query($sql,__LINE__,__FILE__);
4143           while ($this->db->next_record())
4144             {
4145               // Extract the data for each presidency record
4146               $id = $this->db->f('presidency');
4147               $indiv = $this->db->f('indiv');
4148               $name = $this->db->f('name');
4149               $district = $this->db->f('district');
4150               $name = $this->db->f('name');
4151               $valid = 1;
4152
4153               // If we have a valid district, add it to the district table
4154               if($district > 0) {
4155                 $this->db2->query("INSERT INTO tc_district (district,name,supervisor,valid) "
4156                                   . "VALUES ('" . $district . "','" . $name . "','"
4157                                   . $indiv . "','" . $valid . "'"
4158                                   .")",__LINE__,__FILE__);
4159               }
4160
4161             }
4162
4163           $this->t->set_var('adminhandle','');
4164           $this->t->pfp('adminhandle','admin');
4165         }
4166       else
4167         {
4168           $this->t->set_var('adminhandle','');
4169           $this->t->pfp('adminhandle','admin');
4170         }
4171
4172       // Now save off the data needed for an EQ Presidency Table Update
4173
4174       $sql = "SELECT * FROM tc_presidency where valid=1";
4175       $this->db->query($sql,__LINE__,__FILE__);
4176       $table_data = "";
4177       $header_row = "<th>Individual</th><th>Email</th><th>District</th><th>President</th><th>Counselor</th><th>Secretary</th><th>Presidency</th>";
4178       while ($this->db->next_record())
4179         {
4180           // Extract the data for each presidency record
4181           $id = $this->db->f('presidency');
4182           $indiv = $this->db->f('indiv');
4183           $district = $this->db->f('district');
4184           $name = $this->db->f('name');
4185           $email = $this->db->f('email');
4186           $president = $this->db->f('president');
4187           $counselor = $this->db->f('counselor');
4188           $secretary = $this->db->f('secretary');
4189           $eqpresidency = $this->db->f('eqpres');
4190
4191           // Create the forms needed in the table
4192           $table_data .= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
4193
4194           // Presidency ID
4195           $table_data .= '<input type=hidden name="eqpres['.$id.'][id]" value="'.$id.'">';
4196
4197           // individual
4198           if($eqpresidency == 0) {
4199             $table_data.= '<td align=center><select name="eqpres['.$id.'][indiv]">';
4200             $table_data.= '<option value=0></option>';
4201             for ($j=0; $j < count($indiv_id); $j++) {
4202               $tmp_id = $indiv_id[$j];
4203               $name = $indiv_name[$j];
4204               if($indiv_id[$j] == $indiv) { $indivname = $name; $selected = 'selected="selected"'; } else { $selected = ''; }
4205               $table_data.= '<option value='.$tmp_id.' '.$selected.'>'.$name.'</option>';
4206             }
4207             $table_data.='</select></td>';
4208             $table_data.='<input type=hidden name="eqpres['.$id.'][name]" value="'.$indivname.'">';
4209           } else {
4210             $table_data.= '<td align=left><input type=text size="20" name="eqpresname" value="Presidency"></td>';
4211             $table_data.= '<input type=hidden name="eqpres['.$id.'][name]" value="Presidency">';
4212           }
4213
4214           // Email Address
4215           $table_data .= '<td><input type="text" size="50" name="eqpres['.$id.'][email]" value="'.$email.'"></td>';
4216
4217           // District
4218           $table_data.= '<td align=center><select name="eqpres['.$id.'][district]">';
4219           $table_data.= '<option value=0></option>';
4220           for ($j=0; $j <= $this->max_num_districts; $j++) {
4221             if($district == $j) { $selected = 'selected="selected"'; } else { $selected = ''; }
4222             $table_data.= '<option value='.$j.' '.$selected.'>'.$j.'</option>';
4223           }
4224           $table_data.='</select></td>';
4225
4226           // President
4227           $table_data.= '<td align=center><select name="eqpres['.$id.'][president]">';
4228           if($president == 1) { $table_data .= '<option value=0>0</option><option value=1 selected="selected">1</option>'; }
4229           else { $table_data .= '<option value=0 selected="selected">0</option><option value=1>1</option>'; }
4230           $table_data.='</select></td>';
4231
4232           // Counselor
4233           $table_data.= '<td align=center><select name="eqpres['.$id.'][counselor]">';
4234           if($counselor == 1) { $table_data .= '<option value=0>0</option><option value=1 selected="selected">1</option>'; }
4235           else { $table_data .= '<option value=0 selected="selected">0</option><option value=1>1</option>'; }
4236           $table_data.='</select></td>';
4237
4238           // Secretary
4239           $table_data.= '<td align=center><select name="eqpres['.$id.'][secretary]">';
4240           if($secretary == 1) { $table_data .= '<option value=0>0</option><option value=1 selected="selected">1</option>'; }
4241           else { $table_data .= '<option value=0 selected="selected">0</option><option value=1>1</option>'; }
4242           $table_data.='</select></td>';
4243
4244           // Presidency
4245           $table_data.= '<td align=center><select name="eqpres['.$id.'][eqpresidency]">';
4246           if($eqpresidency == 1) { $table_data .= '<option value=0>0</option><option value=1 selected="selected">1</option>'; }
4247           else { $table_data .= '<option value=0 selected="selected">0</option><option value=1>1</option>'; }
4248           $table_data.='</select></td>';
4249
4250           // End of ROW
4251           $table_data .= "</tr>\n";
4252           $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
4253           $this->t->set_var('tr_color',$tr_color);
4254         }
4255
4256       // Now create 1 blank row to always have a line available to add a new individual with
4257       $id = $this->max_presidency_members;
4258       $table_data .= "<tr bgcolor=". $this->t->get_var('tr_color') .">";
4259       // Presidency ID
4260       $table_data .= '<input type=hidden name="eqpres['.$id.'][id]" value="'.$id.'">';
4261       // individual
4262       $table_data.= '<td align=center><select name="eqpres['.$id.'][indiv]">';
4263       $table_data.= '<option value=0></option>';
4264       for ($j=0; $j < count($indiv_id); $j++) {
4265         $tmp_id = $indiv_id[$j];
4266         $name = $indiv_name[$j];
4267         $table_data.= '<option value='.$tmp_id.'>'.$name.'</option>';
4268       }
4269       $table_data.='</select></td>';
4270       $table_data.='<input type=hidden name="eqpres['.$id.'][name]" value="">';
4271       // Email Address
4272       $table_data.='<td><input type="text" size="50" name="eqpres['.$id.'][email]" value=""></td>';
4273       // District
4274       $table_data.= '<td align=center><select name="eqpres['.$id.'][district]">';
4275       $table_data.= '<option value=0></option>';
4276       for ($j=0; $j <= $this->max_num_districts; $j++) {
4277         if($j == 0) { $selected = 'selected="selected"'; } else { $selected = ''; }
4278         $table_data.= '<option value='.$j.' '.$selected.'>'.$j.'</option>';
4279       }
4280       $table_data.='</select></td>';
4281       // President
4282       $table_data.= '<td align=center><select name="eqpres['.$id.'][president]">';
4283       $table_data.= '<option value=0>0</option><option value=1>1</option>';
4284       $table_data.='</select></td>';
4285       // Counselor
4286       $table_data.= '<td align=center><select name="eqpres['.$id.'][counselor]">';
4287       $table_data.= '<option value=0>0</option><option value=1>1</option>';
4288       $table_data.='</select></td>';
4289       // Secretary
4290       $table_data.= '<td align=center><select name="eqpres['.$id.'][secretary]">';
4291       $table_data.= '<option value=0>0</option><option value=1>1</option>';
4292       $table_data.='</select></td>';
4293       // Presidency
4294       $table_data.= '<td align=center><select name="eqpres['.$id.'][eqpresidency]">';
4295       $table_data.= '<option value=0>0</option><option value=1>1</option>';
4296       $table_data.='</select></td>';
4297       // End of ROW
4298       $table_data .= "</tr>\n";
4299       $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
4300       $this->t->set_var('tr_color',$tr_color);
4301
4302       $this->t->set_var('header_row',$header_row);
4303       $this->t->set_var('table_data',$table_data);
4304       $this->t->pfp('presidencyhandle','presidency',True);
4305
4306       $this->save_sessiondata();
4307     }
4308
4309   function email_appt($appointment)
4310     {
4311       //print "Emailing notification of appointment: $appointment <br>";
4312
4313       $sql = "SELECT * FROM tc_appointment where appointment='$appointment'";
4314       $this->db->query($sql,__LINE__,__FILE__);
4315
4316       while ($this->db->next_record())
4317         {
4318           $appointment = $this->db->f('appointment');
4319           $presidency = $this->db->f('presidency');
4320           $location = $this->db->f('location');
4321           $interviewer = "";
4322           $email = "";
4323           $indiv = $this->db->f('indiv');
4324           $indiv_name = "";
4325           $family = $this->db->f('family');
4326           $family_name = "";
4327           $appt_name = "";
4328           $phone = "";
4329           $uid = $this->db->f('uid');
4330
4331           // Extract the year, month, day, hours, minutes, seconds from the appointment time
4332           $appt_date = $this->db->f('date');
4333           $date_array = explode("-",$appt_date);
4334           $year = $date_array[0]; $month = $date_array[1]; $day = $date_array[2];
4335           $appt_time = $this->db->f('time');
4336           $time_array = explode(":",$appt_time);
4337           $hour = $time_array[0]; $minute = $time_array[1]; $seconds = $time_array[2];
4338
4339           // Format the appointment time into an iCal UTC equivalent
4340           $dtstamp = gmdate("Ymd"."\T"."His"."\Z");
4341           $dtstart = gmdate("Ymd"."\T"."His"."\Z", mktime($hour,$minute,$seconds,$month,$day,$year));
4342           $dtstartstr = date("l, F d, o g:i A", mktime($hour,$minute,$seconds,$month,$day,$year));
4343
4344           $sql = "SELECT * FROM tc_presidency where presidency='$presidency'";
4345           $this->db2->query($sql,__LINE__,__FILE__);
4346           if($this->db2->next_record()) {
4347             $email = $this->db2->f('email');
4348             $interviewer = $this->db2->f('name');
4349           }
4350
4351           // Set the email address of the interviewer
4352           $from = $email;
4353
4354           if($indiv > 0) {
4355             $sql = "SELECT * FROM tc_indiv where indiv='$indiv'";
4356             $this->db2->query($sql,__LINE__,__FILE__);
4357             if($this->db2->next_record()) {
4358               $indiv_name = $this->db2->f('name');
4359               $phone = $this->db2->f('phone');
4360               $appt_name = $indiv_name . " Interview";
4361               $duration = $this->default_ppi_appt_duration * 60;
4362             }
4363           }
4364
4365           if($family > 0) {
4366             $sql = "SELECT * FROM tc_family where family='$family'";
4367             $this->db2->query($sql,__LINE__,__FILE__);
4368             if($this->db2->next_record()) {
4369               $family_name = $this->db2->f('name');
4370               $phone = $this->db2->f('phone');
4371               $indiv_id = $this->db2->f('indiv_id');
4372               $appt_name = $family_name . " Family Visit";
4373               $sql = "SELECT * FROM tc_indiv where indiv='$indiv_id'";
4374               $this->db3->query($sql,__LINE__,__FILE__);
4375               if($this->db3->next_record()) {
4376                 $phone = $this->db3->f('phone');
4377               }
4378               $duration = $this->default_visit_appt_duration * 60;
4379             }
4380           }
4381
4382           $dtend = gmdate("Ymd"."\T"."His"."\Z", mktime($hour,$minute,$seconds+$duration,$month,$day,$year));
4383           $dtendstr = date("g:i A", mktime($hour,$minute,$seconds+$duration,$month,$day,$year));
4384           $date = $dtstartstr . "-" . $dtendstr;
4385           $description = "$appt_name : $phone";
4386
4387           if(($uid == 0) && ($appt_name != "")) {
4388             // Create a new calendar item for this appointment, since this must be the first time we
4389             // are sending it out.
4390             print "Sent new appointment to " . $interviewer . " at " . $email . " for " . $appt_name . "<br>";
4391             $uid = rand() . rand(); // Generate a random identifier for this appointment
4392             $subject = "Created: $appt_name";
4393
4394             $this->db->query("UPDATE tc_appointment set" .
4395                              " uid=" . $uid .
4396                              " WHERE appointment=" . $appointment,__LINE__,__FILE__);
4397
4398             $action = "PUBLISH";
4399             $this->send_ical_appt($action, $email, $from, $subject, $dtstamp, $dtstart,
4400                                   $dtend, $date, $location, $appt_name, $description, $uid);
4401
4402           } else if(($uid != 0) && ($appt_name == "")) {
4403             // Remove the calendar item for this appointment since it has already been sent
4404             // and there is no name we have changed it to.
4405             print "Sent deleted appointment to " . $interviewer . " at " . $email . " for " . $appt_date . " " . $appt_time . "<br>";
4406             $subject = "Canceled: $appt_date $appt_time";
4407
4408             $this->db->query("UPDATE tc_appointment set" .
4409                              " uid=0" .
4410                              " WHERE appointment=" . $appointment,__LINE__,__FILE__);
4411
4412             $action = "CANCEL";
4413             $this->send_ical_appt($action, $email, $from, $subject, $dtstamp, $dtstart,
4414                                   $dtend, $date, $location, $subject, $subject, $uid);
4415
4416           } else if($uid != 0) {
4417             // Update the existing appointment since we have changed it
4418             print "Sent updated appointment to " . $interviewer . " at " . $email . " for " . $appt_name . "<br>";
4419
4420             $subject = "Canceled: $appt_date $appt_time";
4421             $action = "CANCEL";
4422             $this->send_ical_appt($action, $email, $from, $subject, $dtstamp, $dtstart,
4423                                   $dtend, $date, $location, $subject, $subject, $uid);
4424
4425             $uid = rand() . rand(); // Generate a random identifier for this appointment
4426             $this->db->query("UPDATE tc_appointment set" .
4427                              " uid=" . $uid .
4428                              " WHERE appointment=" . $appointment,__LINE__,__FILE__);
4429
4430             $subject = "Updated: $appt_name";
4431             $action = "PUBLISH";
4432             $this->send_ical_appt($action, $email, $from, $subject, $dtstamp, $dtstart,
4433                                   $dtend, $date, $location, $appt_name, $description, $uid);
4434           }
4435
4436         }
4437
4438       return true;
4439     }
4440
4441   function send_ical_appt($action, $to, $from, $subject, $dtstamp, $dtstart, $dtend, $date, $location, $summary, $description, $uid)
4442     {
4443       // Initialize our local variables
4444       $boundary = "=MIME_APPOINTMENT_BOUNDARY";
4445       $message = "";
4446       $headers = "";
4447
4448       // Form the headers for the email message
4449       $headers.="X-Mailer: PHP/" . phpversion() . "\n";
4450       $headers.="Mime-Version: 1.0\n";
4451       $headers.="Content-Type: multipart/mixed; boundary=\"$boundary\"\n";
4452       $headers.="Content-Disposition: inline\n";
4453       $headers.="Reply-To: $from\n";
4454       $headers.="From: $from\n";
4455
4456       // Print the plaintext version of the appointment
4457       $message.="--$boundary\n";
4458       $message.="Content-Type: text/plain; charset=us-ascii\n";
4459       $message.="Content-Disposition: inline\n";
4460       $message.="\n";
4461       $message.="What: $description\n";
4462       $message.="When: $date\n";
4463       $message.="Where: $location\n";
4464       $message.="\n";
4465
4466       // Print the .ics attachment version of the appointment
4467       $message.="--$boundary\n";
4468       $message.="Content-Type: text/calendar; charset=us-ascii\n";
4469       $message.="Content-Disposition: attachment; filename=\"appointment.ics\"\n";
4470       $message.="\n";
4471       $message.="BEGIN:VCALENDAR" . "\n";
4472       $message.="VERSION:2.0" . "\n";
4473       $message.="PRODID:-//Microsoft Corporation//Outlook 11.0 MIMEDIR//EN" . "\n";
4474       $message.="METHOD:$action" . "\n";
4475       $message.="BEGIN:VEVENT" . "\n";
4476       $message.="ORGANIZER:MAILTO:$from". "\n";
4477       $message.="DTSTAMP:$dtstamp" . "\n";
4478       $message.="DTSTART:$dtstart" . "\n";
4479       $message.="DTEND:$dtend" . "\n";
4480       $message.="SUMMARY:$summary" . "\n";
4481       $message.="DESCRIPTION:$description" . "\n";
4482       $message.="LOCATION:$location" . "\n";
4483       $message.="UID:$uid" ."\n";
4484       $message.="TRANSP:OPAQUE" . "\n";
4485       $message.="SEQUENCE:0" . "\n";
4486       $message.="CLASS:PUBLIC" . "\n";
4487       $message.="END:VEVENT" . "\n";
4488       $message.="END:VCALENDAR" . "\n";
4489
4490       // Complete the message
4491       $message.="--$boundary\n";
4492
4493       // Send the message
4494       mail($to, $subject, $message, $headers);
4495
4496     }
4497
4498   function get_time_selection_form($hour, $minute, $pm, $presidency, $appointment)
4499     {
4500       $form_data = "";
4501       $blank = 0;
4502
4503       if($hour == 0) { $blank = 1; }
4504
4505       if($this->time_drop_down_lists == 1) {
4506         // Create drop down lists to get the time
4507         $form_data.= '<select name=sched['.$presidency.']['.$appointment.'][hour]>';
4508         if($blank == 1) { $form_data.= '<option value=""></option>'; }
4509         foreach(range(1,12) as $num) {
4510           if($hour == $num) { $selected = 'selected="selected"'; } else { $selected = ''; }
4511           $form_data.= '<option value='.$num.' '.$selected.'>'.$num.'</option>';
4512         }
4513         $form_data.= '</select>';
4514         $form_data.= '&nbsp;:&nbsp;';
4515         $form_data.= '<select name=sched['.$presidency.']['.$appointment.'][minute]>';
4516         if($blank == 1) { $form_data.= '<option value=""></option>'; }
4517         $num = 0;
4518         while($num < 60) {
4519           if($num < 10) { $num = "0" . "$num"; }
4520           if($minute == $num) { $selected = 'selected="selected"'; } else { $selected = ''; }
4521           if($blank == 1) { $selected = ""; }
4522           $form_data.= '<option value='.$num.' '.$selected.'>'.$num.'</option>';
4523           $num = $num + $this->time_drop_down_list_inc;
4524         }
4525         $form_data.= '</select>';
4526       } else {
4527         // Use free form text fields to get the time
4528         if($blank == 1) { $hour = ""; $minute = ""; $ampm = ""; }
4529         $form_data.= '<input type=text size=2 name=sched['.$presidency.']['.$appointment.'][hour] value='.$hour.'>';
4530         $form_data.= ':';
4531         $form_data.= '<input type=text size=2 name=sched['.$presidency.']['.$appointment.'][minute] value='.$minute.'>';
4532         $form_data.= '&nbsp;';
4533       }
4534       // Always use a drop-down select form for am/pm
4535       $form_data.= '<select name=sched['.$presidency.']['.$appointment.'][pm]>';
4536       if($blank == 0) {
4537         if($pm == 0) { $form_data.= '<option value=0 selected>am</option>'; $form_data.= '<option value=1>pm</option>'; }
4538         if($pm == 1) { $form_data.= '<option value=0>am</option>'; $form_data.= '<option value=1 selected>pm</option>'; }
4539       } else {
4540         $form_data.= '<option value=""></option>';
4541         $form_data.= '<option value=0>am</option>';
4542         $form_data.= '<option value=1>pm</option>';
4543       }
4544       $form_data.= '</select>';
4545
4546       return $form_data;
4547     }
4548 }
4549
4550 ?>