Sponsorenverwaltung - Team StarCraft e.V.
 All Data Structures Files Functions Variables
sponsors_search.php
Go to the documentation of this file.
1 <?php
2  /**
3  * @file sponsors_search.php
4  *
5  * @brief This script handles fulltext-search.
6  *
7  * @details
8  * This script handles fulltext search. It's capable of searching the database
9  * for one or more searchterms, storing the results in and perform additional
10  * tasks as sorting or preparation for export on these stored results.
11  * Currently it's not capable to perform highlighting tasks on its results.
12  *
13  * This file depends on inc/common.php and inc/templates/sponsors_search.tpl
14  *
15  * @copyright (c) 2013, Team StarCraft e.V.
16  * @version 1.0.0
17  * @author Alexander Vorndran
18  * @author Daniel Seichter
19  * @date 02.07.2013
20  */
21 
22  //! Marker for extended search on the database
23  define('SEARCH_EXTENDED',1);
24  //! Marker for normal search on the database
25  define('SEARCH_NORMAL',2);
26  //! Marker for normal search on the database
27  define('SEARCH_STRICT',3);
28 
29  //! Marker for linking results with AND
30  define('LOGIC_AND',(-1));
31  //! Marker for linking results with OR
32  define('LOGIC_OR',1);
33  //! Marker for nor splitting the result in linkable partzs
34  define('LOGIC_NONE',0);
35 
36  //! Marker to indicate that the user wants to use highlighting
37  define('HIGHLIGHT_ON',1);
38  //! Marker to indicate that the user doesn't want to use highlighting
39  define('HIGHLIGHT_OFF',0);
40 
41  /// @cond MAINPART
42  // include
43  include("inc/common.php");
44 
45 
46  // Usercake
47  if(!accessGranted($_SERVER['PHP_SELF'])) {
48  if (isUserLoggedIn()) {
49  exitWithErrorTemplate(array('Die angeforderte Seite ist gesperrt.'));
50  }
51  else {
52  exitWithErrorTemplateAndRedirect(array('Die angeforderte Seite ist gesperrt oder geschützt.'), 'login.php', 2);
53  }
54  }
55 
56 
57 
58  // prevents errors if the user goes back to the result page
59  header("Cache-Control: max-age=600");
60 
61  // the user tried sorting a previous result
62  if(!empty($_GET['result'])) {
63  $resultIdentifier = $_GET['result'];
64  }
65 
66  // the user has posted a searchrequest
67  if(!empty($_POST['searchterm'])) {
68  // begin time measuring
69  $timeStart = microtime(true);
70  // read post
71  $searchTerm = $_POST['searchterm'];
72  $modifiedTerm = trim($searchTerm);
73  if(!empty($_POST['similar'])) {
74  $similar = $_POST['similar'];
75  // evaluate post
76  if(strcmp($similar, 'similar')==0) {
77  // the first radiobutton was checked => extended search
78  $searchMode = SEARCH_EXTENDED;
79  } else if (strcmp($similar, 'strict')==0) {
80  // the third radiobutton was checked => strict search
81  $searchMode = SEARCH_STRICT;
82  } else {
83  // the second radiobutton was checked => normal search
84  $searchMode = SEARCH_NORMAL;
85  }
86  } else {
87  $searchMode = SEARCH_NORMAL;
88  }
89 
90  if(!empty($_POST['logicmode'])) {
91  $logicMode = $_POST['logicmode'];
92  if(strcmp($logicMode, 'or')==0) {
93  // the first radiobutton was checked => link with OR
94  $logicMode = LOGIC_OR;
95  } else if (strcmp($logicMode, 'and')==0) {
96  // the third radiobutton was checked => link with AND
97  $logicMode = LOGIC_AND;
98  } else {
99  // the second radion button was checked => don't split search term
100  $logicMode = LOGIC_NONE;
101  }
102  } else {
103  // if the post got lost assume default value
104  $logicMode = LOGIC_NONE;
105  }
106 
107  if(!empty($_POST['highlight'])) {
108  $highlightMode = $_POST['highlight'];
109  if(strcmp($highlightMode, 'yes')==0) {
110  // the first radiobutton was checked => use highlighting
111  $highlightMode = HIGHLIGHT_ON;
112  } else if (strcmp($highlightMode, 'no')==0) {
113  // the second radiobutton was checked => don't use highlighting
114  $highlightMode = HIGHLIGHT_OFF;
115  } else {
116  // this is in case the user has manipulated the form => use highlighting
117  $highlightMode = HIGHLIGHT_ON;
118  }
119  } else {
120  // use highlighting if the information got lost
121  $highlightMode = HIGHLIGHT_ON;
122  }
123 
124  //! holds all the columnnames where the search will look for the searchterm(s)
125  $searchColumns = array('name', 'position', 'telephone_number', 'mobile_phone_number',
126  'email', 'name', 'position', 'telephone_number', 'mobile_phone_number', 'email',
127  'sponsor_name', 'street', 'house_number', 'zip_code', 'town', 'country',
128  'website', 'sponsor_hints', 'sponsor_logo','status_name', 'last_contact_person',
129  'comments','production_facility_name', 'hints','co_determination', 'car_name',
130  'category_name', 'file_name','event', 'object', 'value', 'produced_part_name',
131  'amount');
132 
133  $sponsors = searchDatabase($searchColumns,$modifiedTerm,$searchMode,$logicMode);
134 
135  // list view of the matched entries
136  if(!empty($sponsors)) {
137  $tmp = array();
138  foreach ($sponsors as &$sponsor) {
139  $sponsor['productionFacilities'] = fetchSponsorProductionFacilities($sponsor['id']);
140  $sponsor['cars'] = fetchSponsorCarNames($sponsor['id']);
141  $tmp[] = $sponsor['id'];
142  }
143 
144  // store the result of the query for possible export and sorting
145  // this result identifier is the key to obtain the data later
146  $resultIdentifier = $loggedInUser->storeSearchResult($tmp);
147  // assign identifier for the saved resource
148  $smarty->assign('resultIdentifier',$resultIdentifier);
149 
150  // sort
151  if(isset($_GET['sort']) && ctype_digit($_GET['sort'])) {
152  $columnIdx = $_GET['sort'];
153  if(isset($_GET['dir']) && ctype_digit($_GET['dir'])) {
154  $direction = ($_GET['dir'] == 0) ? SORT_ASC : SORT_DESC;
155 
156  kdsort($sponsors, $columnIdx, $direction);
157 
158  $smarty->assign('sort', array('colIdx' => $columnIdx,
159  'direction' => $_GET['dir']));
160  }
161  }
162  }
163  // stop time measuring
164  $timeSpent = microtime(true)-$timeStart;
165 
166  // tell the user how many results he got for his search
167  if(!empty($sponsors)) {
168  $successes[] = (count($sponsors)==1?"Ein Ergebnis":count($sponsors)." Ergebnisse")." in ".$timeSpent." s";
169  } else {
170  $successes[] = "Leeres Ergebnis in ".$timeSpent." s";
171  }
172 
173  // display template
174  // preserve old settings if they were valid
175  $smarty->assign('sponsors',$sponsors);
176  $smarty->assign('oldTerm',$searchTerm);
177  $smarty->assign('searchMode', $searchMode);
178  $smarty->assign('logicMode', $logicMode);
179  $smarty->assign('highlightMode',$highlightMode);
180  displayTemplateWithErrorsAndSuccesses('Sponsoren - Suche','sponsors_search.tpl');
181  } else if(isset($resultIdentifier)&&$loggedInUser->isResultAvailable($resultIdentifier)) {
182  // the result seems to be available try to load it
183  $sponsorIds = $loggedInUser->loadResult($resultIdentifier);
184  if(!$sponsorIds==FALSE) {
185 
186  $sponsors = array();
187  foreach($sponsorIds as $sponsorId) {
188  $sponsors[] = fetchSponsorDetails($sponsorId);
189  }
190  // list view of the matched entries
191  if(!empty($sponsors)) {
192  $tmp = array();
193  foreach ($sponsors as &$sponsor) {
194  $sponsor['productionFacilities'] = fetchSponsorProductionFacilities($sponsor['id']);
195  $sponsor['cars'] = fetchSponsorCarNames($sponsor['id']);
196  $tmp[] = $sponsor['id'];
197  }
198 
199  // sort
200  if(isset($_GET['sort']) && ctype_digit($_GET['sort'])) {
201  $columnIdx = $_GET['sort'];
202  if(isset($_GET['dir']) && ctype_digit($_GET['dir'])) {
203  $direction = ($_GET['dir'] == 0) ? SORT_ASC : SORT_DESC;
204 
205  kdsort($sponsors, $columnIdx, $direction);
206 
207  $smarty->assign('sort', array('colIdx' => $columnIdx,
208  'direction' => $_GET['dir']));
209  }
210  }
211  }
212 
213  // display template
214  // preserve old settings if they were valid
215  $smarty->assign('sponsors',$sponsors);
216  $smarty->assign('oldTerm',"");
217  $smarty->assign('searchMode', SEARCH_NORMAL);
218  $smarty->assign('logicMode', LOGIC_NONE);
219 // $smarty->assign('highlightMode', HIGHLIGHT_ON);
220  $smarty->assign('highlightMode', HIGHLIGHT_OFF);
221  $smarty->assign('resultIdentifier',$resultIdentifier);
222  displayTemplateWithErrorsAndSuccesses('Sponsoren - Suche','sponsors_search.tpl');
223  } else {
224  exitWithErrorTemplateAndRedirect(array("Es ist ein Fehler aufgetreten. Sie werden in Kürze weitergeleitet"),'sponsors_search.php',2);
225  }
226  } else {
227  // display the initial view with default settings
228  $smarty->assign('searchMode', SEARCH_NORMAL);
229  $smarty->assign('logicMode', LOGIC_NONE);
230 // $smarty->assign('highlightMode',HIGHLIGHT_ON);
231  $smarty->assign('highlightMode',HIGHLIGHT_OFF);
232  displayTemplateWithErrorsAndSuccesses('Sponsoren - Suche','sponsors_search.tpl');
233  }
234  /// @endcond
235 
236  /**
237  * Generates a where clause according to the given information
238  * @param array $columns the names of the columns
239  * @param string $searchterm the actual search term
240  * @param integer $findSimilar
241  * - SEARCH_EXTENDED uses SOUNDS LIKE in addition, adds trailing and leading %
242  * - SEARCH_NORMAL just adds trailing and leading %
243  * - SEARCH_STRICT doesn't add trailing and leading %
244  * @return string the where clause based on the given information
245  * @author Alexander Vorndran
246  */
247  function generateWhereClause($columns, $searchterm, $findSimilar = SEARCH_NORMAL) {
248  global $mysqli;
249  // encode html-specialchars to match the database entries
250  $searchterm = htmlspecialchars($searchterm, ENT_QUOTES);
251  // escape MySQL special characters to prevent SQL-injections
252  $searchterm = ($findSimilar<>SEARCH_STRICT?"*":"").$mysqli->real_escape_string($searchterm).($findSimilar<>SEARCH_STRICT?"*":"");
253  // replace wildcards from the entry by their SQL-equivalents
254  $searchterm = str_replace(array('%','_','?','*'), array('\%','\_','_','%'), $searchterm);
255  $numElems = count($columns);
256  if($numElems>0) {
257  $i = 0;
258  $retVal = "";
259  foreach ($columns as $column) {
260  $i++;
261  $retVal = $retVal.(($i<>1)?"OR ":"")."`".$column."` LIKE '".$searchterm."'\n";
262  if($findSimilar==SEARCH_EXTENDED) {
263  $retVal = $retVal."OR `".$column."` SOUNDS LIKE '".$searchterm."'\n";
264  }
265  }
266  return $retVal;
267  } else {
268  return "1";
269  }
270  }
271 
272  /**
273  * Search the sponsoring database
274  * @param array $searchColumns the names of the colums that should be used for the search
275  * @param string $searchTerm the keywords to search for
276  * @param integer $findSimilar [optional]
277  * - SEARCH_EXTENDED uses SOUNDS LIKE in addition, adds trailing and leading %
278  * - SEARCH_STRICT doesn't add trailing and leading %
279  * - Default: SEARCH_NORMAL just adds trailing and leading %
280  * @param integer $logicMode [optional]
281  * - LOGIC_OR split and link with OR
282  * - LOGIC_AND split and link with AND
283  * - Default: LOGIC_NONE don't split the searchterm
284  * @return mixed
285  * - a list of the matching sponsors on success
286  * - FALSE on failure
287  * @author Alexander Vorndran
288  */
289  function searchDatabase($searchColumns, $searchTerm, $findSimilar = SEARCH_NORMAL, $logicMode = LOGIC_NONE) {
290  global $mysqli, $db;
291  $whereClause = "";
292  // sanitize searchterm
293  $searchTerm = preg_replace("/\s{1,}/", " ", $searchTerm);
294  if($logicMode == LOGIC_OR) {
295  // the user chose linking the terms with OR
296  // split into several searchterms
297  $searchTerms = explode(" ", preg_replace("/\s{1,}/"," ", strtolower($searchTerm)));
298  // remove duplicate searchterms
299  $searchTerms = array_unique($searchTerms);
300  // iterate over the searchterms
301  for ($i = 0; $i < count($searchTerms); $i++) {
302  $whereClause = $whereClause."(".
303  generateWhereClause($searchColumns, $searchTerms[$i], $findSimilar).")";
304  if($i < count($searchTerms)-1) {
305  $whereClause .= " OR\n";
306  }
307  }
308  } else if($logicMode == LOGIC_AND) {
309  // the user chose linking the terms with AND
310  // split into several searchterms
311  $searchTerms = explode(" ", preg_replace("/\s{1,}/"," ", strtolower($searchTerm)));
312  // remove duplicate searchterms
313  $searchTerms = array_unique($searchTerms);
314  // iterate over the search terms
315  for ($i = 0; $i < count($searchTerms); $i++) {
316  $whereClause = $whereClause."(".
317  generateWhereClause($searchColumns, $searchTerms[$i], $findSimilar).")";
318  if($i < count($searchTerms)-1) {
319  $whereClause .= " AND\n";
320  }
321  }
322  } else {
323  // the user chose strict search
324  $whereClause = generateWhereClause($searchColumns, $searchTerm, $findSimilar);
325  }
326  // build the query
327  $qry = 'SELECT
328  sponsor.sponsor_id,
329  sponsor.sponsor_name,
330  sponsor.street,
331  sponsor.house_number,
332  sponsor.zip_code,
333  sponsor.town,
334  sponsor.country,
335  sponsor.website,
336  sponsor.sponsor_hints,
337  sponsor.sponsor_logo,
338  sponsor.last_contact_date,
339  sponsor.last_contact_person,
340  status.status_id,
341  status.status_name,
342  COUNT(*) AS hits
343  FROM '.$db['sponsor'].' AS sponsor LEFT JOIN '.$db['status'].' AS status ON sponsor.status_id = status.status_id
344  LEFT JOIN '.$db['contact_person'].' AS person ON sponsor.sponsor_id = person.sponsor_id
345  LEFT JOIN '.$db['sp_production_facility'].' AS spfc ON sponsor.sponsor_id = spfc.sponsor_id
346  LEFT JOIN '.$db['production_facility'].' AS pfc ON spfc.production_facility_id = pfc.production_facility_id
347  LEFT JOIN '.$db['sponsor_car'].' AS sc ON sc.sponsor_id = sponsor.sponsor_id
348  LEFT JOIN '.$db['car'].' AS car ON sc.car_id = car.car_id
349  LEFT JOIN '.$db['category'].' AS cat ON cat.category_id = sc.category_id
350  LEFT JOIN '.$db['sc_attachment'].' AS att ON att.sponsor_car_id = sc.sponsor_car_id
351  LEFT JOIN '.$db['sc_event'].' AS evt ON evt.sponsor_car_id = sc.sponsor_car_id
352  LEFT JOIN '.$db['sc_material_donation'].' AS mat ON mat.sponsor_car_id = sc.sponsor_car_id
353  LEFT JOIN '.$db['sc_financial_donation'].' AS fin ON fin.sponsor_car_id = sc.sponsor_car_id
354  LEFT JOIN '.$db['sc_produced_part'].' AS prp ON prp.sponsor_car_id = sc.sponsor_car_id
355  WHERE '.$whereClause.
356  'GROUP BY sponsor.sponsor_name
357  ORDER BY sponsor.sponsor_name ASC';
358  $stmt = $mysqli->prepare($qry);
359  $stmt->execute();
360 
361  $stmt->bind_result($id, $name, $street, $houseNumber, $zipCode, $town, $country, $website, $hints, $sponsorLogo, $lastContactDate, $lastContactPerson, $statusId, $statusName, $hits);
362  $stmt->store_result();
363 
364  while ($stmt->fetch()) {
365  $lastChange = fetchSponsorLatestEditData($id);
366  $rows [] = array('id' => $id,
367  'name' => $name,
368  'street' => $street,
369  'houseNumber' => $houseNumber,
370  'zipCode' => $zipCode,
371  'town' => $town,
372  'country' => $country,
373  'website' => $website,
374  'hints' => $hints,
375  'sponsorLogo' => $sponsorLogo,
376  'lastContactDateTimestamp' => $lastContactDate,
377  'lastContactPerson' => $lastContactPerson,
378  'statusId' => $statusId,
379  'statusName' => $statusName,
380  'lastChangeDateTimestamp' => $lastChange['timestamp'],
381  'lastChangeUserId' => $lastChange['userId'],
382  'lastChangeUserName' => $lastChange['userName'],
383  'hits' => $hits);
384  }
385  $stmt->close();
386  return isset($rows) ? $rows : false;
387  }
388 
389 ?>