Sponsorenverwaltung - Team StarCraft e.V.
 All Data Structures Files Functions Variables
functions_delete.php
Go to the documentation of this file.
1 <?php
2 
3  /**
4  * @file functions_delete.php
5  *
6  * @brief This file holds some of the functions used for (non-)permanent delete.
7  *
8  * @details
9  * This script holds functions to delete sponsors and sponsorcars. This covers
10  * non permanent delete functions as well as functions capable of deleting
11  * the given sponsor respectively sponsorcar and all of its stored backup-
12  * entries from the database. Due to the power of these functions most of them
13  * realize a hardcoded access-controll.
14  *
15  * This file is linked with the other funcions via the inc/common.php and it needs
16  * lots of globals defined in inc/common.php or its includes.
17  *
18  * @copyright (c) 2013, Team StarCraft e.V.
19  * @version 1.0.0
20  * @author Daniel Seichter
21  * @author Alexander Vorndran
22  * @author Florian Wirthmüller
23  * @date 02.07.2013
24  */
25 
26  /**
27  * Deletes a sponsor car from the database
28  * @param integer $sponsorCarId the id of the sponsor car
29  * @author Alexander Vorndran
30  * @author Daniel Seichter
31  */
32  function deleteSponsorCar($sponsorCarId) {
33  if(isUserLoggedIn()) {
34  $carDetails = fetchSponsorCarDetails(NULL, $sponsorCarId);
35  // delete all the attachements
36  $attachments = fetchSponsorCarAttachments($sponsorCarId);
37  if(is_array($attachments)) {
38  foreach ($attachments as $attachment) {
39  if(deleteSponsorCarAttachment($attachment['id'])===FALSE) {
40  return FALSE;
41  }
42  }
43  }
44 
45  // delete all the material donations
46  $materialDonations = fetchSponsorCarMaterialDonation($sponsorCarId);
47  if(is_array($materialDonations)) {
48  foreach($materialDonations as $materialDonation) {
49  if(deleteSponsorCarMaterialDonation($materialDonation['id'])===FALSE) {
50  return FALSE;
51  }
52  }
53  }
54 
55  // delete all the financial donations
56  $financialDonations = fetchSponsorCarMaterialDonation($sponsorCarId);
57  if(is_array($financialDonations)) {
58  foreach ($financialDonations as $financialDonation) {
59  if(deleteSponsorCarFinancialDonation($financialDonation['id'])===FALSE) {
60  return FALSE;
61  }
62  }
63  }
64 
65  // delete all the produced parts
66  $producedParts = fetchSponsorCarProducedParts($sponsorCarId);
67  if(is_array($producedParts)) {
68  foreach($producedParts as $producedPart) {
69  if(deleteSponsorCarProducedPart($producedPart['id'])===FALSE) {
70  return FALSE;
71  }
72  }
73  }
74 
75  // delete all the events
76  $events = fetchSponsorCarEvents($sponsorCarId);
77  if(is_array($events)) {
78  foreach ($events as $event) {
79  if(deleteSponsorCarEvent($event['id'])===FALSE) {
80  return FALSE;
81  }
82  }
83  }
84 
85  // at last delete the sponsorcar entry itself
86  global $mysqli, $db, $loggedInUser;
87  //update lock_user_id before delete
88  $stmt = $mysqli->prepare('UPDATE '.$db['sponsor_car'].' SET
89  lock_time = -1,
90  lock_user_id = ?
91  WHERE sponsor_car_id = ?');
92  $stmt->bind_param("ii", $loggedInUser->userId, $sponsorCarId);
93  $stmt->execute();
94  $stmt->close();
95 
96  //delete
97  $stmt = $mysqli->prepare("DELETE FROM " . $db['sponsor_car'] . "
98  WHERE sponsor_car_id = ?");
99  $stmt->bind_param("i", $sponsorCarId);
100  $result = $stmt->execute();
101  $stmt->close();
102  return $result;
103  } else {
104  return FALSE;
105  }
106  }
107 
108  /**
109  * Delete a sponsor and all its related information from the normal database tables.
110  * @param integer $sponsorId
111  * @return mixed
112  * - FALSE if the query failed or the given $sponsorId was not valid
113  * - -1 if their are unresolved locks on the sponsor
114  * - -2 if an error occurred in the process of deletion
115  * @author Daniel Seichter
116  * @author Alexander Vorndran
117  */
118  function deleteSponsor($sponsorId) {
119  if(isUserLoggedIn() && is_numeric($sponsorId)) {
120  // check if any section is locked
121  for ($i = 1; $i <= 4; $i++) {
122  if(isSponsorEditSectionLocked($i, $sponsorId)) {
123  // if a section is locked revert all the locks set before
124  for ($j = $i; $j > 0; $j--) {
125  unlockSponsorEditSection($j, $sponsorId);
126  }
127  return -1;
128  } else {
129  // lock the section so that no one can start editing
130  lockSponsorEditSection($i, $sponsorId);
131  }
132  }
133  // no one is editing and all the sections are now locked for the deletion
134  // delete all production facilities
135  $productionFacilities = fetchSponsorProductionFacilities($sponsorId);
136  if(is_array($productionFacilities)) {
137  foreach ($productionFacilities as $productionFacility) {
138  if(deleteSponsorProductionFacility($productionFacility['productionFacilityId'],true)===FALSE) {
139  for ($j = 4; $j > 0; $j--) {
140  unlockSponsorEditSection($j, $sponsorId);
141  }
142  return -2;
143  }
144  }
145  }
146 
147  // delete all contact persons
148  $contactPersons = fetchSponsorContactPersons($sponsorId);
149  if(is_array($contactPersons)) {
150  foreach ($contactPersons as $contactPerson) {
151  if(deleteSponsorContactPerson($contactPerson['id'],true)===FALSE) {
152  for ($j = 4; $j > 0; $j--) {
153  unlockSponsorEditSection($j, $sponsorId);
154  }
155  return -2;
156  }
157  }
158  }
159 
160  //delete all the sponsor cars
161  $sponsorCars = fetchSponsorCarDetails($sponsorId);
162  if(is_array($sponsorCars)) {
163  foreach ($sponsorCars as $sponsorCar) {
164  if(deleteSponsorCar($sponsorCar['carId'])===FALSE) {
165  for ($j = 4; $j > 0; $j--) {
166  unlockSponsorEditSection($j, $sponsorId);
167  }
168  return -2;
169  }
170  }
171  }
172 
173 
174  // at last delete the sponsor itself
175  global $mysqli, $db, $loggedInUser;
176  // update lock_user_id before delete
177  $stmt = $mysqli->prepare('UPDATE '.$db['sponsor'].' SET
178  lock_time = -1,
179  lock_user_id = ?
180  WHERE sponsor_id = ?');
181  $stmt->bind_param("ii", $loggedInUser->userId, $sponsorId);
182  $stmt->execute();
183  $stmt->close();
184 
185  //delete
186  $stmt = $mysqli->prepare("DELETE FROM " . $db['sponsor'] . "
187  WHERE sponsor_id = ?");
188  $stmt->bind_param("i", $sponsorId);
189  $result = $stmt->execute();
190  $stmt->close();
191  return $result;
192  } else {
193  return FALSE;
194  }
195  }
196 
197  /**
198  * This function deletes the backup entries of a sponsor completly und permanently.
199  * @param integer $sponsorId
200  * @return boolean
201  * - TRUE on success
202  * - FALSE on failure
203  * @author Alexander Vorndran
204  * @author Daniel Seichter
205  */
206  function deleteSponsorPermanently($sponsorId) {
207  if(isUserRoot()) {
208  global $mysqli, $dbBackup;
209  // do not delete backups for an undeleted sponsor
210  if(isSponsorDeleted($sponsorId)) {
211  $totalRemoved = 0;
212  // try to fetch all cars that where once associated to that sponsor
213  $cars = fetchAllDeletedSponsorCarsForSponsor($sponsorId);
214  if(is_array($cars)) {
215  // it seems as if there are backup entries for sponsorcars
216  // delete them
218  }
219  // all the backups for the sponsorcars are now deleted
220  // delete contact persons
221  $qry = "DELETE FROM ".$dbBackup['contact_person']." WHERE bup_sponsor_id = ?";
222  $stmt = $mysqli->prepare($qry);
223  $stmt->bind_param('i',$sponsorId);
224  $result = $stmt->execute();
225  $stmt->close();
226  if($result===FALSE) {
227  return FALSE;
228  }
229 
230  // delete production facilities
231  $qry = "DELETE FROM ".$dbBackup['sp_production_facility']." WHERE bup_sponsor_id = ?";
232  $stmt = $mysqli->prepare($qry);
233  $stmt->bind_param('i',$sponsorId);
234  $result = $stmt->execute();
235  $stmt->close();
236  if($result===FALSE) {
237  return FALSE;
238  }
239 
240  // remove the sponsorlogo from the servers filesystem
241  $qry = "SELECT `bup_sponsor_logo`
242  FROM ".$dbBackup['sponsor']."
243  WHERE bup_sponsor_id = ?";
244  $stmt = $mysqli->prepare($qry);
245  $stmt->bind_param('i',$sponsorId);
246  $stmt->execute();
247  $stmt->bind_result($sponsorLogo);
248 
249  while($stmt->fetch()) {
250  if (file_exists(ABS_PATH.$sponsorLogo) && !empty($sponsorLogo)) {
251  //unlink original file
252  unlink(ABS_PATH.$sponsorLogo);
253  //unlink scaled images
254  $pathParts = pathinfo($sponsorLogo);
255  $files = glob(ABS_PATH.$pathParts['dirname'].'/_scaled/'.$pathParts['filename'].'*.'.$pathParts['extension']);
256  foreach($files as $file){
257  if (file_exists($file)) {
258  unlink($file);
259  }
260  }
261  }
262  }
263  $stmt->close();
264 
265 
266  // delete the sponsor itself
267  $qry = "DELETE FROM ".$dbBackup['sponsor']." WHERE bup_sponsor_id = ?";
268  $stmt = $mysqli->prepare($qry);
269  $stmt->bind_param('i',$sponsorId);
270  $result = $stmt->execute();
271  $stmt->close();
272  if($result===FALSE) {
273  return FALSE;
274  } else {
275  return TRUE;
276  }
277  return $totalRemoved;
278  } else {
279  return FALSE;
280  }
281  }
282  }
283 
284 
285  /**
286  * Deletes a sponsor car and it's associated information from the backup-tables
287  * @param array $cars array with the key 'id' which should hold the sponsorcar id
288  * @param integer $carId
289  * @return mixed
290  * - on success the number of successful deletions
291  * - FALSE on failure
292  * @author Alexander Vorndran
293  * @author Daniel Seichter
294  */
295  function deleteSponsorCarPermanently($cars = NULL, $carId = NULL) {
296  if(isUserAdministrator()) {
297  global $mysqli, $dbBackup;
298  if(isset($cars)) {
299  if(is_array($cars)) {
300  // first retrieve and unlink all the attachement files if they still exist
301  foreach ($cars as $car) {
302  $stmt = $mysqli->prepare('
303  SELECT bup_path
304  FROM '.$dbBackup['sc_attachment'].'
305  WHERE bup_sponsor_car_id = ?');
306  $stmt->bind_param('i', $car['id']);
307  $stmt->execute();
308 
309  $stmt->bind_result($path);
310  while ($stmt->fetch()) {
311  $attachmentsToDelete[] = array('path' => $path);
312  }
313  $stmt->close();
314  if (is_array($attachmentsToDelete)) {
315  foreach ($attachmentsToDelete as $attachment) {
316  //delete files
317  if (file_exists(ABS_PATH.$attachment['path']) && !empty($attachment['path'])) {
318  unlink(ABS_PATH.$attachment['path']);
319  }
320  }
321  }
322  }
323 
324  // form the where clause so that all these sponsorcars are deleted
325  $deleteClause = "";
326  foreach ($cars as $car) {
327  if(is_numeric($car['id'])) {
328  $deleteClause .= 'bup_sponsor_car_id = '.$car['id'].' OR ';
329  }
330  }
331  // use substr to get rid of the last OR
332  $deleteClause = substr($deleteClause, 0, strlen($deleteClause)-4);
333 
334  $totalRemoved = 0;
335  // iterate over all tables that are accessed via the sponsor_car_id
336  $tables = array('sc_attachment','sc_event','sc_financial_donation','sc_material_donation','sc_produced_part','sponsor_car');
337  foreach ($tables as $table) {
338  $qry = "DELETE FROM ".MYSQL_PREFIX_BACKUP.$table." WHERE ".$deleteClause;
339  $stmt = $mysqli->prepare($qry);
340  $result = $stmt->execute();
341  $stmt->close();
342  if($result===FALSE) {
343  return FALSE;
344  } else {
345  $totalRemoved += $result;
346  }
347  }
348  return $totalRemoved;
349  }
350  } else if(isset($carId)) {
351  if(is_numeric($carId)) {
352  $qry =
353  $stmt = $mysqli->prepare('
354  SELECT bup_path
355  FROM '.$dbBackup['sc_attachment'].'
356  WHERE bup_sponsor_car_id = ?');
357  $stmt->bind_param('i', $carId);
358  $stmt->execute();
359 
360  $stmt->bind_result($path);
361  while ($stmt->fetch()) {
362  $attachmentsToDelete[] = array('path' => $path);
363  }
364  $stmt->close();
365  if (is_array($attachmentsToDelete)) {
366  foreach ($attachmentsToDelete as $attachment) {
367  //delete files
368  if (file_exists(ABS_PATH.$attachment['path'])) {
369  unlink(ABS_PATH.$attachment['path']);
370  }
371  }
372  }
373 
374  $totalRemoved = 0;
375  // iterate over all tables that are accessed via the sponsor_car_id
376  $tables = array('sc_attachment','sc_event','sc_financial_donation','sc_material_donation','sc_produced_part','sponsor_car');
377  foreach ($tables as $table) {
378  $qry = "DELETE FROM ".MYSQL_PREFIX_BACKUP.$table." WHERE bup_sponsor_car_id = ?";
379  $stmt = $mysqli->prepare($qry);
380  $stmt->bind_param('i',$carId);
381  $result = $stmt->execute();
382  $stmt->close();
383  if($result===FALSE) {
384  return FALSE;
385  } else {
386  $totalRemoved += $result;
387  }
388  }
389  return $totalRemoved;
390  }
391  }
392  }
393  }
394 
395  /**
396  * Fetches a list of all deleted sponsorcars that belong to a sponsor
397  * @param integer $sponsorId id of the sponsor
398  * @param boolean $fetchTimestamp
399  * - TRUE: the filestamp of the deletion will be included in the resulting array
400  * - FALSE: the filestamp of the deletion won't be included in the resulting array
401  * @return mixed
402  * - an array containg all the sponsorcar ids
403  * - FALSE on failure
404  * @author Alexander Vorndran
405  */
406  function fetchAllDeletedSponsorCarsForSponsor($sponsorId, $fetchTimestamp = FALSE) {
407  global $mysqli, $dbBackup;
408  if (isset($sponsorId)) {
409  $qry = 'SELECT DISTINCT bup_sponsor_car_id, bup_car_name
410  FROM '.$dbBackup['sponsor_car'].' AS sc LEFT JOIN '.$dbBackup['car'].' AS c ON sc.bup_car_id = c.bup_car_id
411  WHERE bup_sponsor_id = ?';
412  $stmt = $mysqli->prepare($qry);
413  $stmt->bind_param('i', $sponsorId);
414  $stmt->execute();
415 
416  $stmt->bind_result($id,$name);
417 
418 
419  while ($stmt->fetch()) {
420  $deletedSponsorCars [] = array('id' => $id,'name'=>$name);
421  }
422  $stmt->close();
423  if($fetchTimestamp) {
424  if(is_array($deletedSponsorCars)) {
425  foreach ($deletedSponsorCars as &$deletedSponsorCar) {
426  $qry = "SELECT `bup_last_change_at`,`bup_last_change_by`
427  FROM ".$dbBackup['sponsor_car']."
428  WHERE `bup_sponsor_car_id` = ?
429  ORDER BY `bup_last_change_at` DESC LIMIT 1";
430  $stmt = $mysqli->prepare($qry);
431  $stmt->bind_param('i',$deletedSponsorCar['id']);
432  $stmt->execute();
433  $stmt->bind_result($timestamp,$userId);
434  $stmt->store_result();
435  if($stmt->fetch()) {
436  $deletedSponsorCar['timestamp'] = $timestamp;
437  $deletedSponsorCar['user'] = array('id'=>$userId, 'name'=> fetchUsername($userId));
438  }
439  $stmt->close();
440  }
441  }
442  }
443  return isset($deletedSponsorCars) ? $deletedSponsorCars : false;
444  }
445  }
446 
447  /**
448  * Fetches a list of deleted sponsorcars with their last known name that belong to a sponsor
449  * @param integer $sponsorId id of the sponsor
450  * @param boolean $fetchTimestamp
451  * - TRUE: the filestamp of the deletion will be included in the resulting array
452  * - FALSE: the filestamp of the deletion won't be included in the resulting array
453  * @return mixed
454  * - an array containg all the sponsorcar ids
455  * - FALSE on failure
456  * @author Alexander Vorndran
457  */
458  function fetchDeletedSponsorCars($sponsorId, $fetchTimestamp = FALSE) {
459  global $mysqli, $dbBackup;
460  if (is_numeric($sponsorId)) {
461  $qry = 'SELECT DISTINCT sc.bup_sponsor_car_id, c.bup_car_id, c.bup_car_name
462  FROM '.$dbBackup['sponsor_car'].' AS sc LEFT JOIN '.$dbBackup['car'].' AS c ON sc.bup_car_id = c.bup_car_id
463  WHERE bup_sponsor_id = ? AND c.bup_car_name = (
464  SELECT c2.bup_car_name
465  FROM '.$dbBackup['car'].' AS c2
466  WHERE c2.bup_car_id = c.bup_car_id
467  ORDER BY c2.bup_last_change_at DESC LIMIT 1)
468  ORDER BY c.bup_car_name';
469  $stmt = $mysqli->prepare($qry);
470  $stmt->bind_param('i', $sponsorId);
471  $stmt->execute();
472 
473  $stmt->bind_result($id,$carId,$name);
474 
475  while ($stmt->fetch()) {
476  $deletedSponsorCars [] = array('id' => $id,'name'=>$name);
477  }
478 
479  $stmt->close();
480  if($fetchTimestamp) {
481  if(is_array($deletedSponsorCars)) {
482  foreach ($deletedSponsorCars as &$deletedSponsorCar) {
483  $qry = "SELECT `bup_last_change_at`,`bup_last_change_by`
484  FROM ".$dbBackup['sponsor_car']."
485  WHERE `bup_sponsor_car_id` = ?
486  ORDER BY `bup_last_change_at` DESC LIMIT 1";
487  $stmt = $mysqli->prepare($qry);
488  $stmt->bind_param('i',$deletedSponsorCar['id']);
489  $stmt->execute();
490  $stmt->bind_result($timestamp,$userId);
491  $stmt->store_result();
492  if($stmt->fetch()) {
493  $deletedSponsorCar['timestamp'] = $timestamp;
494  $deletedSponsorCar['user'] = array('id'=>$userId, 'name'=> fetchUsername($userId));
495  }
496  $stmt->close();
497  }
498  }
499  }
500  return isset($deletedSponsorCars) ? $deletedSponsorCars : false;
501  }
502  }
503 
504  /**
505  * Retrieves a list of all deleted sponsors from the backup tables
506  * @return mixed
507  * - a list of the deleted sponsors
508  * - FALSE failure
509  * @author Alexander Vorndran
510  * @author Daniel Seichter
511  * @author Florian Wirthmüller
512  */
514  global $mysqli, $dbBackup;
515  $qry = "SELECT t6.`bup_sponsor_id`, t6.`bup_sponsor_name`, t6.`bup_sponsor_logo` FROM(
516  SELECT *
517  FROM ".$dbBackup['sponsor']." AS t4
518  WHERE t4.bup_sponsor_id IN (
519  SELECT DISTINCT t5.bup_sponsor_id
520  FROM ".$dbBackup['sponsor']." AS t5
521  WHERE t5.bup_sponsor_name like '*deleted*')
522  AND t4.bup_sponsor_name NOT LIKE '*deleted*'
523  ORDER BY t4.id DESC
524  ) AS t6
525  GROUP BY t6.bup_sponsor_id";
526  $stmt = $mysqli->prepare($qry);
527  $stmt->execute();
528  $stmt->bind_result($id,$name, $logoPath);
529  while($stmt->fetch()) {
530  $deletedSponsors[] = array('id' => $id, 'name' => $name, 'sponsorLogo' => $logoPath);
531  }
532 
533  $stmt->close();
534  if(isset($deletedSponsors)) {
535  if(is_array($deletedSponsors)) {
536  foreach ($deletedSponsors as &$deletedSponsor) {
537  $qry = "SELECT `bup_last_change_at`,`bup_last_change_by`
538  FROM ".$dbBackup['sponsor']."
539  WHERE `bup_sponsor_id` = ?
540  ORDER BY `bup_last_change_at` DESC LIMIT 1";
541  $stmt = $mysqli->prepare($qry);
542  $stmt->bind_param('i',$deletedSponsor['id']);
543  $stmt->execute();
544  $stmt->bind_result($timestamp,$userId);
545  $stmt->store_result();
546  if($stmt->fetch()) {
547  $deletedSponsor['timestamp'] = $timestamp;
548  $deletedSponsor['user'] = array('id' => $userId, 'name' => fetchUsername($userId));
549  }
550  $stmt->free_result();
551  $stmt->close();
552  }
553  }
554  return ($deletedSponsors);
555  } else {
556  return array();
557  }
558  }
559 
560  /**
561  * Checks if a sponsor with a given id has been deleted
562  * @param integer $sponsorId the id of the sponsor
563  * @return boolean
564  * - TRUE if the sponsor was deleted
565  * - FALSE if not
566  * @author Alexander Vorndran
567  */
568  function isSponsorDeleted($sponsorId) {
569  if (is_numeric($sponsorId)) {
570  global $mysqli, $dbBackup;
571  $stmt = $mysqli->prepare("SELECT bup_sponsor_name
572  FROM " . $dbBackup['sponsor'] . "
573  WHERE bup_sponsor_id = ? AND bup_sponsor_name = '*deleted*'
574  LIMIT 1");
575  $stmt->bind_param("i", $sponsorId);
576  $stmt->execute();
577  $stmt->store_result();
578  $num_returns = $stmt->num_rows;
579  $stmt->close();
580 
581  if ($num_returns > 0) {
582  return true;
583  } else {
584  return false;
585  }
586  } else {
587  return false;
588  }
589  }
590 
591  /**
592  * Fetches a list of all non deleted sponsors with deleted sponsorcars
593  * @return array an array containing id and name of all the sponsors
594  * @author Alexander Vorndran
595  */
597  global $mysqli, $dbBackup, $db;
598  $qry = "SELECT t1.`sponsor_id`, t1.`sponsor_name`, t1.`sponsor_logo`
599  FROM ".$db['sponsor']." AS t1
600  WHERE t1.`sponsor_id` IN (
601  SELECT DISTINCT t2.`bup_sponsor_id`
602  FROM ".$dbBackup['sponsor_car']." AS t2
603  WHERE t2.`bup_hints` like \"*deleted*\")
604  AND t1.`sponsor_id` != 0
605  GROUP BY t1.`sponsor_id`
606  ORDER BY t1.`sponsor_name` ASC";
607  $stmt = $mysqli->prepare($qry);
608  $stmt->execute();
609  $stmt->bind_result($id,$name, $logoPath);
610  while($stmt->fetch()) {
611  $sponsorsWithDeletedCars[] = array('id' => $id, 'name' => $name, 'sponsorLogo' => $logoPath);
612  }
613 
614  $stmt->close();
615  if(isset($sponsorsWithDeletedCars)) {
616  return ($sponsorsWithDeletedCars);
617  } else {
618  return array();
619  }
620  }
621 
622 ?>