Fixed bug in schedule yearly visit table. Bad index being used in vis_notes array.
[eq/.git] / inc / class.tc.inc.php
index 8de0e856b0d29cded864d91961f4f7b7a5ec61ea..a372ec072e7d178432a0d6ecee8381a123f49ebd 100644 (file)
@@ -109,6 +109,18 @@ class tc
                echo parse_navbar();
                $this->display_app_header();    
        }
+       
+       function logToFile($func, $msg)
+       {
+               // open file
+               $fd = fopen($this->upload_target_path . "/tc_trace.log", "a");
+               // append date/time to message
+               $str = "[" . date("Y/m/d h:i:s", mktime()) . "] [" . $func . "] " . $msg;
+               // write string
+               fwrite($fd, $str . "\n");
+               // close file
+               fclose($fd);
+       }
   
        function save_sessiondata()
        {
@@ -191,7 +203,7 @@ class tc
                $this->t->set_var('actionurl',$GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ht_view'));
                $this->t->set_var('title','Hometeaching'); 
 
-               $sql = "SELECT * FROM tc_district where valid=1 ORDER BY district ASC";
+               $sql = "SELECT * FROM tc_district AS td JOIN tc_individual AS ti WHERE td.supervisor=ti.individual AND td.valid=1 ORDER BY td.district ASC";
                $this->db->query($sql,__LINE__,__FILE__);
                $i=0;
                while ($this->db->next_record()) {
@@ -680,7 +692,7 @@ class tc
 
                for ($i=0; $i < count($names); $i++) {
                        //$this->nextmatchs->template_alternate_row_color(&$this->t);
-                       $this->t->set_var('indiv_name',$names[$i]);
+                       $this->t->set_var('individual_name',$names[$i]);
                        if(($i+1) % 3 == 0) {
                                $this->t->set_var('table_sep',"</td></tr><tr>"); 
                        } else { 
@@ -722,7 +734,7 @@ class tc
                        // Re-add the individuals who are checked as having participated in this activity
                        $indivs = get_var('individual_name',array('POST'));
                        if(is_array($indivs)) { // Only do the foreach loop if we have a valid array of indivs to work with
-                               foreach ($indivs as $indiv) {
+                               foreach ($indivs as $individual) {
                                        $this->db->query("INSERT INTO tc_participation (individual,activity) " .
                                                         "VALUES (" . $individual . ",". $activity['activity'] . ")",__LINE__,__FILE__);
                                }
@@ -749,7 +761,7 @@ class tc
                        }
 
                        $indivs = get_var('individual_name',array('POST'));
-                       foreach ($indivs as $indiv)
+                       foreach ($indivs as $individual)
                        {
                                $this->db->query("INSERT INTO tc_participation (individual,activity) " .
                                                 "VALUES (" . $individual . ",". $activity['activity'] . ")",__LINE__,__FILE__);
@@ -1354,7 +1366,7 @@ class tc
                $year = date('Y');
 
                // Get the President
-               $sql = "SELECT * FROM tc_presidency where president=1 and valid=1";
+               $sql = "SELECT * FROM tc_presidency AS tp JOIN tc_individual AS ti where tp.individual=ti.individual AND tp.president=1 AND tp.valid=1";
                $this->db->query($sql,__LINE__,__FILE__);
                if($this->db->next_record()) {
                        $president_name = $this->db->f('name');
@@ -1365,16 +1377,7 @@ class tc
                        $interviewer = $this->db->f('individual');
                        $district_number = '*';
                        $district_name = $president_name;
-                       $sql = "SELECT * FROM tc_individual where individual='$president_id'";
-                       $this->db2->query($sql,__LINE__,__FILE__);
-                       if($this->db2->next_record()) {
-                               $mls_id = $this->db2->f('mls_id');
-                       }
-                       $sql = "SELECT * FROM tc_individual where mls_id='$mls_id'";
-                       $this->db2->query($sql,__LINE__,__FILE__);
-                       if($this->db2->next_record()) {
-                               $president_address = $this->db2->f('address');
-                       }
+                       $president_address = $this->db->f('address');
                } else {
                        print "<hr><font color=red><h3>-E- Unable to locate President in tc_presidency table</h3></font></hr>";
                        return;
@@ -1418,17 +1421,9 @@ class tc
                                $this->db->query($sql,__LINE__,__FILE__);
                                if ($this->db->next_record()) {
                                        $scheduling_priority = $this->db->f('scheduling_priority');
-                                       $this->db2->query("UPDATE tc_scheduling_priority SET priority='$priority' AND notes=\"$notes\" WHERE scheduling_priority='$scheduling_priority'", __LINE__, __FILE__);
+                                       //$this->logToFile("ppi_sched", "UPDATE tc_scheduling_priority SET priority='$priority', notes=\"$notes\" WHERE scheduling_priority='$scheduling_priority'");
+                                       $this->db2->query("UPDATE tc_scheduling_priority SET priority='$priority', notes=\"$notes\" WHERE scheduling_priority='$scheduling_priority'", __LINE__, __FILE__);
                                }
-
-/*
-                               $this->db->query("UPDATE tc_individual set " .
-                                                " notes='" . $notes . "'" .
-                                                ",priority='" . $priority . "'" .
-                                                " WHERE individual=" . $individual,__LINE__,__FILE__);
-*/
-
-
                        }
 
                        $take_me_to_url = $GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.ppi_sched');
@@ -1519,7 +1514,7 @@ class tc
 
                // PPI SCHEDULING TABLE
                // TODO:  changed this so it picks the quorum dynamically
-               $sql = "SELECT * FROM tc_individual as ti JOIN tc_scheduling_priority as tsp where ti.scheduling_priority=tsp.scheduling_priority and steward='Elder' and valid=1 ORDER by tsp.priority ASC, ti.name ASC";
+               $sql = "SELECT * FROM tc_individual AS ti JOIN tc_scheduling_priority AS tsp WHERE ti.scheduling_priority=tsp.scheduling_priority AND steward='Elder' AND valid=1 ORDER BY tsp.priority ASC, ti.name ASC";
                $this->db->query($sql,__LINE__,__FILE__);
 
                $i=0; 
@@ -1770,14 +1765,9 @@ class tc
                                $this->db->query("SELECT * FROM tc_companionship WHERE individual=$individual and valid=1",__LINE__,__FILE__);
                                if ($this->db->next_record()) {
                                        $scheduling_priority = $this->db->f('scheduling_priority');
-                                       $this->db2->query("UPDATE tc_scheduling_priority SET priority='$hti_pri' and notes=\"hti_notes\" where scheduling_priority=$scheduling_priority",__LINE__,__FILE__);
+                                       //$this->logToFile("int_sched", "UPDATE tc_scheduling_priority SET priority='$hti_pri', notes=\"$hti_notes\" WHERE scheduling_priority='$scheduling_priority'");
+                                       $this->db2->query("UPDATE tc_scheduling_priority SET priority='$hti_pri', notes=\"$hti_notes\" WHERE scheduling_priority='$scheduling_priority'",__LINE__,__FILE__);
                                }
-/*
-                               $this->db->query("UPDATE tc_individual set " .
-                                                " hti_notes='" . $hti_notes . "'" .
-                                                ",hti_pri='" . $hti_pri . "'" .
-                                                " WHERE individual=" . $individual,__LINE__,__FILE__);
-*/
                        }
 
                        $take_me_to_url = $GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.int_sched');
@@ -1785,7 +1775,7 @@ class tc
                }
 
                // Get the Districts
-               $sql = "SELECT * FROM tc_district where valid=1 ORDER BY district ASC";
+               $sql = "SELECT * FROM tc_district AS td JOIN (tc_presidency AS tp, tc_individual AS ti) WHERE td.district=tp.district AND td.supervisor=ti.individual AND td.valid=1 ORDER BY td.district ASC";
                $this->db->query($sql,__LINE__,__FILE__);
                $i=0;
                while ($this->db->next_record()) {
@@ -1793,11 +1783,7 @@ class tc
                        $districts[$i]['district'] = $this->db->f('district');
                        $districts[$i]['name'] = $this->db->f('name');
                        $districts[$i]['supervisor'] = $this->db->f('supervisor');
-                       $sql = "SELECT * FROM tc_presidency where district=$district and valid=1";
-                       $this->db2->query($sql,__LINE__,__FILE__);
-                       if($this->db2->next_record()) {
-                               $districts[$i]['presidency'] = $this->db2->f('presidency');
-                       }
+                       $districts[$i]['presidency'] = $this->db->f('presidency');
                        $i++;
                }
 
@@ -1903,7 +1889,7 @@ class tc
                        $i=0;
                        for ($j=0; $j < count($unique_companionships); $j++) {
                                // Select all the companions from each companionship
-                               $sql = "SELECT * FROM tc_companionship AS tc JOIN (tc_scheduling_priority AS tsp, tc_individual as ti) WHERE tc.scheduling_priority=tsp.scheduling_priority AND tc.individual=ti.individual AND tc.valid=1 AND tc.companionship=". $unique_companionships[$j]['companionship'];
+                               $sql = "SELECT * FROM tc_companionship AS tc JOIN (tc_scheduling_priority AS tsp, tc_individual AS ti) WHERE tc.scheduling_priority=tsp.scheduling_priority AND tc.individual=ti.individual AND tc.valid=1 AND tc.companionship=". $unique_companionships[$j]['companionship'];
                                $this->db->query($sql,__LINE__,__FILE__);
                                $k=0; $int_completed=0;
                                $comp = $unique_companionships[$j]['companionship'];
@@ -2120,20 +2106,14 @@ class tc
                        foreach ($new_data as $entry) {
                                $visit_notes = $entry['notes'];
                                $family = $entry['family_id'];
-                               $visit_pri = $entry['pri'];
-
+                               $visit_pri = $entry['pri'];
                                // Perform database save actions here
                                $this->db->query("SELECT * FROM tc_family WHERE family='$family'",__LINE__,__FILE__);
                                if ($this->db->next_record()) {
                                        $scheduling_priority = $this->db->f('scheduling_priority');
-                                       $this->db2->query("UPDATE tc_scheduling_priority SET priority='$visit_pri' AND notes=\"$visit_notes\" WHERE scheduling_priority='$scheduling_priority'",__LINE__,__FILE__);
+                                       //$this->logToFile("vis_sched", "UPDATE tc_scheduling_priority SET priority='$visit_pri', notes=\"$visit_notes\" WHERE scheduling_priority='$scheduling_priority'");
+                                       $this->db2->query("UPDATE tc_scheduling_priority SET priority='$visit_pri', notes=\"$visit_notes\" WHERE scheduling_priority='$scheduling_priority'", __LINE__, __FILE__);
                                }
-/*
-                               $this->db->query("UPDATE tc_family set " .
-                                                " visit_notes='" . $visit_notes . "'" .
-                                                ",visit_pri='" . $visit_pri . "'" .
-                                                " WHERE family=" . $family,__LINE__,__FILE__);
-*/
                        }
 
                        $take_me_to_url = $GLOBALS['phpgw']->link('/tc/index.php','menuaction=tc.tc.vis_sched');
@@ -2150,7 +2130,7 @@ class tc
                $appt_table_data = ""; 
 
                // Find out what the President ID is
-               $sql = "SELECT * FROM tc_presidency where president=1 and valid=1";
+               $sql = "SELECT * FROM tc_presidency AS tp JOIN tc_individual AS ti WHERE tp.individual=ti.individual AND tp.president=1 AND tp.valid=1";
                $this->db->query($sql,__LINE__,__FILE__);
                if($this->db->next_record()) {
                        $presidency_name = $this->db->f('name');
@@ -2251,7 +2231,7 @@ class tc
                                $table_data.= "<tr bgcolor=". $this->t->get_var('tr_color') ."><td title=\"$phone\"><a href=$link>$name Family</a></td>";
                                $table_data.= "<td align=center>$phone</td>";
                                $table_data.= "<td align=center>";
-                               $table_data.= '<select name=vis_notes['.$i.'][pri]>';
+                               $table_data.= '<select name=vis_notes['.$id.'][pri]>';
                                foreach(range(0,6) as $num) {
                                        if($num == 0) { $num = 1; } else {$num = $num*5; }
                                        if($vis_pri == $num) { 
@@ -2263,9 +2243,9 @@ class tc
                                }
                                $table_data.= '</select></td>';
                                $table_data.= "<td align=center>$date</td>";
-                               $table_data.= '<td><input type=text size="50" maxlength="128" name="vis_notes['.$i.'][notes]" value="'.$vis_notes.'">';
-                               $table_data.= '<input type=hidden name="vis_notes['.$i.'][family_id]" value="'.$id.'">';
-                               $table_data.= '<input type=hidden name="vis_notes['.$i.'][family_name]" value="'.$name.'">';
+                               $table_data.= '<td><input type=text size="50" maxlength="128" name="vis_notes['.$id.'][notes]" value="'.$vis_notes.'">';
+                               $table_data.= '<input type=hidden name="vis_notes['.$id.'][family_id]" value="'.$id.'">';
+                               $table_data.= '<input type=hidden name="vis_notes['.$id.'][family_name]" value="'.$name.'">';
                                $table_data.= '</td>';
                                $table_data.= '</tr>';
                                $tr_color = $this->nextmatchs->alternate_row_color($tr_color);
@@ -2363,7 +2343,7 @@ class tc
                        $this->t->set_var('lang_num_months','Years of History');
                }
 
-               $sql = "SELECT * FROM tc_presidency where president=1 and valid=1";
+               $sql = "SELECT * FROM tc_presidency AS tp JOIN tc_individual AS ti WHERE tp.individual=ti.individual AND tp.president=1 AND tp.valid=1";
                $this->db->query($sql,__LINE__,__FILE__);
                if($this->db->next_record()) {
                        $president_name = $this->db->f('name');
@@ -2487,7 +2467,7 @@ class tc
                $notes = get_var('notes',array('GET','POST'));
                $interview_type = get_var('interview_type',array('GET','POST'));
 
-               $sql = "SELECT * FROM tc_presidency where valid=1 and (president=1 or counselor=1 or secretary=1)";
+               $sql = "SELECT * FROM tc_presidency AS tp JOIN tc_individual AS ti WHERE tp.individual=ti.individual AND tp.valid=1 AND (tp.president=1 OR tp.counselor=1 OR tp.secretary=1)";
                $this->db2->query($sql,__LINE__,__FILE__);
                while ($this->db2->next_record()) {
                        $indiv = $this->db2->f('individual');
@@ -2620,7 +2600,7 @@ class tc
                else if($current_month >= 7 && $current_month <= 9) { $current_month=9; }
                else if($current_month >= 10 && $current_month <= 12) { $current_month=12; }
 
-               $sql = "SELECT * FROM tc_district where valid=1 ORDER BY district ASC";
+               $sql = "SELECT * FROM tc_district AS td JOIN tc_individual AS ti WHERE td.supervisor=ti.individual AND td.valid=1 ORDER BY td.district ASC";
                $this->db->query($sql,__LINE__,__FILE__);
                $i=0;
                while ($this->db->next_record()) {
@@ -2810,7 +2790,7 @@ class tc
                $notes = get_var('notes',array('GET','POST'));
                $interview_type = get_var('interview_type',array('GET','POST'));
 
-               $sql = "SELECT * FROM tc_presidency where valid=1 and (president=1 or counselor=1 or secretary=1 or district!=0)";
+               $sql = "SELECT * FROM tc_presidency AS tp JOIN tc_individual AS ti WHERE tp.individual=ti.individual AND tp.valid=1 AND (tp.president=1 OR tp.counselor=1 OR tp.secretary=1 OR tp.district!=0)";
                $this->db2->query($sql,__LINE__,__FILE__);
                while ($this->db2->next_record()) {
                        $indiv = $this->db2->f('individual');
@@ -3555,7 +3535,7 @@ class tc
                $header_row.= "<th width=$location_width><font size=-2>Location</th>";
                $table_data = "";
 
-               $sql = "SELECT * FROM tc_presidency where valid=1 GROUP BY individual ORDER BY name ASC";
+               $sql = "SELECT * FROM tc_presidency AS tp JOIN tc_individual AS ti WHERE tp.individual=ti.individual AND tp.valid=1 GROUP BY tp.individual ORDER BY ti.name ASC";
                $this->db->query($sql,__LINE__,__FILE__);
                $i=0;
                while ($this->db->next_record()) {
@@ -4062,7 +4042,6 @@ class tc
                                                $this->db2->query("UPDATE tc_presidency set" .
                                                                  " individual=" . $indiv . 
                                                                  " ,district=" . $district . 
-                                                                 " ,name='" . $name . "'" .
                                                                  " ,email='" . $email . "'" .
                                                                  " ,president='" . $president . "'" .
                                                                  " ,counselor='" . $counselor . "'" .
@@ -4070,10 +4049,10 @@ class tc
                                                                  " WHERE presidency=" . $id,__LINE__,__FILE__);
                                        } else {
                                                //print "Adding New Entry<br>";
-                                               $this->db2->query("INSERT INTO tc_presidency (presidency,individual,district,name," .
+                                               $this->db2->query("INSERT INTO tc_presidency (presidency,individual,district," .
                                                                  "email,president,counselor,secretary,valid) " .
                                                                  "VALUES (NULL,'" . $indiv . "','" . $district . "','" .
-                                                                 $name . "','" . $email . "','" . $president  . "','" .
+                                                                 $email . "','" . $president  . "','" .
                                                                  $counselor . "','" . $secretary . "','1'" .
                                                                  ")",__LINE__,__FILE__);
                                        }
@@ -4093,13 +4072,13 @@ class tc
                        $name = "High Priests";
                        $indiv = 0;
                        $valid = 0;
-                       $this->db2->query("INSERT INTO tc_district (district,name,supervisor,valid) " .
-                                         "VALUES ('" . $district . "','" . $name . "','" .
+                       $this->db2->query("INSERT INTO tc_district (district,supervisor,valid) " .
+                                         "VALUES ('" . $district . "','" . 
                                          $indiv . "','" . $valid . "'" .
                                          ")",__LINE__,__FILE__);
 
                        // Requery the tc_presidency table
-                       $sql = "SELECT * FROM tc_presidency where valid=1";
+                       $sql = "SELECT * FROM tc_presidency AS tp JOIN tc_individual AS ti WHERE tp.individual=ti.individual AND tp.valid=1";
                        $this->db->query($sql,__LINE__,__FILE__);
                        while ($this->db->next_record()) {
                                // Extract the data for each presidency record
@@ -4107,13 +4086,12 @@ class tc
                                $indiv = $this->db->f('individual');
                                $name = $this->db->f('name');
                                $district = $this->db->f('district');
-                               $name = $this->db->f('name');
                                $valid = 1;
 
                                // If we have a valid district, add it to the district table
                                if($district > 0) {
-                                       $this->db2->query("INSERT INTO tc_district (district,name,supervisor,valid) " .
-                                                         "VALUES ('" . $district . "','" . $name . "','" .
+                                       $this->db2->query("INSERT INTO tc_district (district,supervisor,valid) " .
+                                                         "VALUES ('" . $district . "','" . 
                                                          $indiv . "','" . $valid . "'" .
                                                          ")",__LINE__,__FILE__);
                                }
@@ -4130,7 +4108,7 @@ class tc
 
                // Now save off the data needed for a Presidency Table Update
 
-               $sql = "SELECT * FROM tc_presidency where valid=1";
+               $sql = "SELECT tp.*, ti.name FROM tc_presidency AS tp JOIN tc_individual AS ti WHERE tp.individual=ti.individual AND tp.valid=1";
                $this->db->query($sql,__LINE__,__FILE__);
                $table_data = "";
                $header_row = "<th>Individual</th><th>Email</th><th>District</th><th>President</th><th>Counselor</th><th>Secretary</th>";
@@ -4300,7 +4278,7 @@ class tc
                        $dtstart = gmdate("Ymd"."\T"."His"."\Z", mktime($hour,$minute,$seconds,$month,$day,$year));
                        $dtstartstr = date("l, F d, o g:i A", mktime($hour,$minute,$seconds,$month,$day,$year));
 
-                       $sql = "SELECT * FROM tc_presidency where presidency='$presidency'";
+                       $sql = "SELECT * FROM tc_presidency AS tp JOIN tc_individual AS ti WHERE tp.individual=ti.individual AND tp.presidency='$presidency'";
                        $this->db2->query($sql,__LINE__,__FILE__);
                        if($this->db2->next_record()) {
                                $email = $this->db2->f('email');