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