Support Incident Tracker GIT4.x
report_feedback_contact.inc.php
Go to the documentation of this file.
00001 <?php
00002 // feedback3.php - Feedback scores by contact
00003 //
00004 // SiT (Support Incident Tracker) - Support call tracking system
00005 // Copyright (C) 2010-2011 The Support Incident Tracker Project
00006 // Copyright (C) 2000-2009 Salford Software Ltd. and Contributors
00007 //
00008 // This software may be used and distributed according to the terms
00009 // of the GNU General Public License, incorporated herein by reference.
00010 //
00011 
00012 // Author: Ivan Lucas <ivanlucas[at]users.sourceforge.net>
00013 // Hacked: Tom Gerrard <tom.gerrard[at]salfordsoftware.co.uk>
00014 // Converted: Paul Heaney <paulheaney[at]users.sourceforge.net>
00015 
00016 // Prevent script from being run directly (ie. it must always be included
00017 if (realpath(__FILE__) == realpath($_SERVER['SCRIPT_FILENAME']))
00018 {
00019     exit;
00020 }
00021 
00022 
00023 $maxscore = $CONFIG['feedback_max_score'];
00024 $formid = $CONFIG['feedback_form'];
00025 $now = time();
00026 
00027 echo "<div style='margin: 20px'>";
00028 echo "<h2><a href='{$CONFIG['application_webpath']}report_feedback.php'>{$strFeedback}</a> {$strScores}: {$strByContact}</h2>";
00029 echo feedback_between_dates();
00030 echo "<p>{$strCustomerFeedbackReportSiteMsg}:</p>";
00031 
00032 $qsql = "SELECT * FROM `{$dbFeedbackQuestions}` WHERE formid='{$formid}' AND type='rating' ORDER BY taborder";
00033 $qresult = mysql_query($qsql);
00034 if (mysql_error()) trigger_error(mysql_error(), E_USER_WARNING);
00035 while ($qrow = mysql_fetch_object($qresult))
00036 {
00037     $q[$qrow->taborder] = $qrow;
00038 }
00039 
00040 
00041 $msql = "SELECT *, cs.name AS closingstatusname, s.name AS sitename, s.id as siteid, (i.closed - i.opened) AS duration, \n";
00042 $msql .= "fr.id AS reportid, c.id AS contactid ";
00043 $msql .= "FROM `{$dbFeedbackRespondents}` AS fr, `{$dbIncidents}` AS i, `{$dbContacts}` AS c, `{$dbSites}` AS s, `{$dbClosingStatus}` AS cs WHERE fr.incidentid = i.id \n";
00044 $msql .= "AND i.contact = c.id ";
00045 $msql .= "AND c.siteid = s.id ";
00046 $msql .= "AND i.closingstatus = cs.id ";
00047 $msql .= "AND fr.incidentid > 0 \n";
00048 $msql .= "AND fr.completed = 'yes' \n";
00049 
00050 if (!empty($startdate))
00051 {
00052     if ($dates == 'feedbackin')
00053     {
00054         $msql .= "AND fr.created >= '{$startdate}' ";
00055     }
00056     elseif ($dates == 'closedin')
00057     {
00058         $msql .= "AND i.closed >= '{$startdate}' ";
00059     }
00060 
00061     //echo "DATES {$dates}";
00062 }
00063 
00064 if (!empty($enddate))
00065 {
00066     if ($dates == 'feedbackin')
00067     {
00068         $msql .= "AND fr.created <= '{$enddate}' ";
00069     }
00070     elseif ($dates == 'closedin')
00071     {
00072         $msql .= "AND i.closed <= '{$enddate}' ";
00073     }
00074 }
00075 
00076 if (!empty($id)) $msql .= "AND i.contact='{$id}' \n";
00077 else $msql .= "ORDER BY c.surname ASC, c.forenames ASC, i.contact ASC , i.id ASC \n";
00078 $mresult = mysql_query($msql);
00079 if (mysql_error()) trigger_error(mysql_error(), E_USER_WARNING);
00080 
00081 if (mysql_num_rows($mresult) >= 1)
00082 {
00083     $prevcontactid = 0;
00084     $countcontacts = 0;
00085     for ($i = 0; $i <= 10; $i++)
00086     {
00087       $counter[$i] = 0;
00088     }
00089 
00090     $surveys = 0;
00091     debug_log("Feedback report sql: ".$msql, TRUE);
00092 
00093 
00094     $firstrun = 0;
00095     while ($mrow = mysql_fetch_object($mresult))
00096     {
00097         // Only print if we have a value ({$prevcontactid} / {$mrow->contactid})
00098         if ($prevcontactid != $mrow->contactid AND $firstrun != 0)
00099         {
00100             $numones = count($storeone);
00101             if ($numones > 0)
00102             {
00103                 for($c = 1; $c <= $numones; $c++)
00104                 {
00105                     if ($storeone[$c] > 0) $qr = number_format($storeone[$c] / $storetwo[$c], 2);
00106                     else $qr=0;
00107                     if ($storeone[$c] > 0) $qp = number_format((($qr -1) * (100 / ($maxscore - 1))), 0);
00108                     else $qp = 0;
00109                     $html .= "Q$c: {$q[$c]->question} {$qr} <strong>({$qp}%)</strong><br />";
00110                     $gtotal += $qr;
00111                 }
00112                 if ($c>0) $c--;
00113                 $total_average = number_format($gtotal / $c,2);
00114                 // $total_percent=number_format((($gtotal / ($maxscore * $c)) * 100), 0);
00115                 $total_percent = number_format((($total_average -1) * (100 / ($maxscore -1))), 0);
00116                 if ($total_percent < 0) $total_percent = 0;
00117 
00118                 $html .= "<p>{$strPositivity}: {$total_average} <strong>({$total_percent}%)</strong>, ".sprintf($strAfterXSurveys,$surveys)."</p>";
00119                 if ($surveys<>1) $html .= 's';
00120                 $html .= "</p><br /><br />";
00121             }
00122             else $html = '';
00123 
00124             if ($total_average>0)
00125             {
00126                 echo "{$html}";
00127                 $countcontacts++;
00128 
00129                 $counter[floor($total_percent / 10)] ++;
00130             }
00131             unset($qavgavg);
00132             unset($qanswer);
00133             unset($storeone);
00134             unset($storetwo);
00135             unset($gtotal);
00136             $surveys=0;
00137         }
00138         $firstrun=1;
00139 
00140         // Loop through reports
00141         $totalresult = 0;
00142         $numquestions = 0;
00143         $surveys++;
00144         $html = "<h4 style='text-align: left;'><a href='../contact_details.php?id={$mrow->contactid}' title='Jump to Contact'>{$mrow->forenames} {$mrow->surname}</a>, <a href='../site_details.php?id={$mrow->siteid}&action=show' title='Jump to site'>{$mrow->sitename}</a></h4>";
00145         $csql = "SELECT * FROM `{$dbFeedbackQuestions}` WHERE formid='{$formid}' AND type='text' ORDER BY id DESC";
00146         $cresult = mysql_query($csql);
00147         if (mysql_error()) trigger_error(mysql_error(), E_USER_WARNING);
00148         $crow = mysql_fetch_object($cresult);
00149         $textquestion = $crow->id;
00150         $csql = "SELECT DISTINCT i.id as incidentid, result, i.title as title
00151                  FROM `{$dbFeedbackRespondents}` AS fr, `{$dbIncidents}` AS i, `{$dbUsers}` AS u, `{$dbFeedbackResults}` AS r
00152                  WHERE fr.incidentid = i.id
00153                  AND i.owner = u.id
00154                  AND fr.id = r.respondentid
00155                  AND r.questionid = '$textquestion'
00156                  AND fr.id = '$mrow->reportid' ";
00157         if (!empty($startdate))
00158         {
00159             if ($dates == 'feedbackin')
00160             {
00161                 $csql .= "AND fr.created >= '{$startdate}' ";
00162             }
00163             elseif ($dates == 'closedin')
00164             {
00165                 $csql .= "AND i.closed >= '{$startdate}' ";
00166             }
00167         }
00168 
00169         if (!empty($enddate))
00170         {
00171             if ($dates == 'feedbackin')
00172             {
00173                 $csql .= "AND fr.created <= '{$enddate}' ";
00174             }
00175             elseif ($dates == 'closedin')
00176             {
00177                 $csql .= "AND i.closed <= '{$enddate}' ";
00178             }
00179         }
00180         $csql .= "ORDER BY i.contact, i.id";
00181         $cresult = mysql_query($csql);
00182 
00183         if (mysql_error()) trigger_error(mysql_error(), E_USER_WARNING);
00184 
00185         while ($crow = mysql_fetch_object($cresult))
00186         {
00187             if ($crow->result != '')
00188             {
00189                 $html.= "<p>{$crow->result}<br /><em><a href=\"javascript:incident_details_window(\'{$crow->incidentid}\','sit_popup', false)\">{$crow->incidentid}</a> {$crow->title}</em></p>";
00190             }
00191         }
00192 
00193         $qsql = "SELECT * FROM `{$dbFeedbackQuestions}` WHERE formid='{$formid}' AND type='rating' ORDER BY taborder";
00194         $qresult = mysql_query($qsql);
00195         if (mysql_error()) trigger_error(mysql_error(), E_USER_WARNING);
00196         while ($qrow = mysql_fetch_object($qresult))
00197         {
00198             $numquestions++;
00199             $sql = "SELECT * FROM `{$dbFeedbackRespondents}` AS fr, `{$dbIncidents}` AS i, `{$dbUsers}` AS u, `{$dbFeedbackResults}` AS r ";
00200             $sql .= "WHERE fr.incidentid = i.id ";
00201             $sql .= "AND i.owner = u.id ";
00202             $sql .= "AND fr.id = r.respondentid ";
00203             $sql .= "AND r.questionid = '$qrow->id' ";
00204             $sql .= "AND fr.id = '$mrow->reportid' ";
00205 
00206             if (!empty($startdate))
00207             {
00208                 if ($dates == 'feedbackin')
00209                 {
00210                     $sql .= "AND fr.created >= '{$startdate}' ";
00211                 }
00212                 elseif ($dates == 'closedin')
00213                 {
00214                     $sql .= "AND i.closed >= '{$startdate}' ";
00215                 }
00216             }
00217 
00218             if (!empty($enddate))
00219             {
00220                 if ($dates == 'feedbackin')
00221                 {
00222                     $sql .= "AND fr.created <= '{$enddate}' ";
00223                 }
00224                 elseif ($dates == 'closedin')
00225                 {
00226                     $sql .= "AND i.closed <= '{$enddate}' ";
00227                 }
00228             }
00229 
00230             $sql .= "ORDER BY i.contact, i.id";
00231             $result = mysql_query($sql);
00232             if (mysql_error()) trigger_error(mysql_error(), E_USER_WARNING);
00233             $numresults = 0;
00234             $cumul = 0;
00235             $percent = 0;
00236             $average = 0;
00237             $answercount = mysql_num_rows($result);
00238 
00239             if ($answercount > 0)
00240             {
00241                 while ($row = mysql_fetch_object($result))
00242                 {
00243                     // Loop through the results
00244                     if (!empty($row->result))
00245                     {
00246                         $cumul += $row->result;
00247                         $numresults++;
00248                         $storeone[$qrow->taborder] += $row->result;
00249                         $storetwo[$qrow->taborder]++;
00250                         $storethree[$qrow->taborder]=$qrow->id;
00251                     }
00252                 }
00253             }
00254 
00255             if ($numresults > 0) $average = number_format(($cumul / $numresults), 2);
00256             $percent = number_format((($average / $maxscore ) * 100), 0);
00257             $totalresult += $average;
00258 
00259             $qanswer[$qrow->taborder] += $average;
00260             $qavgavg = $qanswer[$qrow->taborder];
00261         }
00262 
00263         $prevcontactid = $mrow->contactid;
00264     }
00265     echo "<h2>{$strSummary}</h2><p>{$strShowPositivityGraph}:</p>";
00266 
00267     $adjust = 13;
00268     $min = 4;
00269     for ($i = 0; $i <= 10; $i++)
00270     {
00271         if ($countcontacts > 0) $weighted = number_format((($counter[$i] / $countcontacts) * 100), 0);
00272         else $weighted = 0;
00273         echo "<div style='background: #B";
00274         echo dechex(floor($i*1.5));
00275         echo "0; color: #FFF; float:left; width: ".($min + ($weighted * $adjust))."px;'>&nbsp;</div>&nbsp; ";
00276         echo ($i * 10);
00277         if ($i < 10)
00278         {
00279             echo " - ";
00280             echo ($i*10) + 9;
00281         }
00282         echo "% ({$weighted}%)<br />";
00283     }
00284 }
00285 else
00286 {
00287     echo user_alert($strNoFeedbackFound, E_USER_WARNING);
00288 }
00289 
00290 echo "</div>\n";
00291 
00292 ?>