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