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