Sponsorenverwaltung - Team StarCraft e.V.
 All Data Structures Files Functions Variables
functions_sponsoring.php
Go to the documentation of this file.
1 <?php
2  /**
3  * @file functions_sponsoring.php
4  *
5  * @brief Holds most of the functions used for sponsormanagement.
6  *
7  * @details
8  * This file is one of the core-files of the whole package. It provides
9  * a huge number of functions to manipulate sponsors, sponsorcars and other
10  * persistent data. The manipulation include adding, deleting, updating and
11  * managing data in a lot of ways. Please consider reading the documentation
12  * of a single function if you want to know more about the functionalities
13  * provided by this script-file.
14  *
15  * This file is linked with the other functions via inc/common.php.
16  *
17  * @copyright (c) 2013, Team StarCraft e.V.
18  * @version 1.0.0
19  * @author Daniel Seichter
20  * @author Alexander Vorndran
21  * @author Joachim Wagner
22  * @date 02.07.2013
23  *
24  */
25 
26  //! editsection locktime in seconds (35 ≤ x ≤ inf)
27  define('EDIT_SECTION_LOCK_DURATION', 300);
28  //! maximal number of changes that are shown when the user click 'Verlauf anzeigen'
29  define('NUMBER_OF_CHANGES_SHOWN_PER_EDIT_SECTION', 5);
30 
31 
32  /**
33  * Fetches all the details of the sponsors in an alphabetical order
34  * @return mixed
35  * - array containing the details of the sponsors
36  * - FALSE if an error occured
37  * @author Daniel Seichter
38  */
39  function fetchAllSponsors() {
40  $time = microtime();
41  global $mysqli, $db;
42  $stmt = $mysqli->prepare('SELECT
43  sponsor_id,
44  sponsor_name,
45  street,
46  house_number,
47  zip_code,
48  town,
49  country,
50  website,
51  sponsor_hints,
52  sponsor_logo,
53  last_contact_date,
54  last_contact_person,
55  sponsor.status_id,
56  status_name
57  FROM '.$db['sponsor'].' AS sponsor LEFT JOIN '.$db['status'].' AS status ON sponsor.status_id = status.status_id
58  ORDER BY sponsor_name');
59  $stmt->execute();
60 
61  $stmt->bind_result($id, $name, $street, $houseNumber, $zipCode, $town, $country, $website, $hints, $sponsorLogo, $lastContactDate, $lastContactPerson, $statusId, $statusName);
62  $stmt->store_result();
63 
64  while ($stmt->fetch()) {
65  $lastChange = fetchSponsorLatestEditData($id);
66  $rows [] = array('id' => $id,
67  'name' => $name,
68  'street' => $street,
69  'houseNumber' => $houseNumber,
70  'zipCode' => $zipCode,
71  'town' => $town,
72  'country' => $country,
73  'website' => $website,
74  'hints' => $hints,
75  'sponsorLogo' => $sponsorLogo,
76  'lastContactDateTimestamp' => $lastContactDate,
77  'lastContactPerson' => $lastContactPerson,
78  'statusId' => $statusId,
79  'statusName' => $statusName,
80  'lastChangeDateTimestamp' => $lastChange['timestamp'],
81  'lastChangeUserId' => $lastChange['userId'],
82  'lastChangeUserName' => $lastChange['userName']);
83  }
84  $stmt->close();
85  return isset($rows) ? $rows : FALSE;
86  }
87 
88  /**
89  * Fetches all the details of a sponsor
90  * @param integer $sponsorId id of the sponsor
91  * @param boolean $includingLastEdit [optional]
92  * - including last edit data of the sponsor
93  * - Default: TRUE
94  * @return mixed
95  * - array containing the details of the given sponsor
96  * - FALSE if an error occured
97  * @author Daniel Seichter
98  */
99  function fetchSponsorDetails($sponsorId, $includingLastEdit = TRUE) {
100  global $mysqli, $db;
101  $stmt = $mysqli->prepare('SELECT
102  sponsor_id,
103  sponsor_name,
104  street,
105  house_number,
106  zip_code,
107  town,
108  country,
109  website,
110  sponsor_hints,
111  sponsor_logo,
112  last_contact_date,
113  last_contact_person,
114  sponsor.status_id,
115  status_name
116  FROM '.$db['sponsor'].' AS sponsor LEFT JOIN '.$db['status'].' AS status ON sponsor.status_id = status.status_id
117  WHERE sponsor_id = ?');
118  $stmt->bind_param('i', $sponsorId);
119  $stmt->execute();
120 
121  $stmt->bind_result($sponsorId, $name, $street, $houseNumber, $zipCode, $town, $country, $website, $hints, $sponsorLogo, $lastContactDate, $lastContactPerson, $statusId, $statusName);
122  $stmt->store_result();
123 
124  if ($stmt->fetch()) {
125  $row = array('id' => $sponsorId,
126  'name' => $name,
127  'street' => $street,
128  'houseNumber' => $houseNumber,
129  'zipCode' => $zipCode,
130  'town' => $town,
131  'country' => $country,
132  'website' => $website,
133  'hints' => $hints,
134  'sponsorLogo' => $sponsorLogo,
135  'lastContactDateTimestamp' => $lastContactDate,
136  'lastContactPerson' => $lastContactPerson,
137  'statusId' => $statusId,
138  'statusName' => $statusName);
139  if ($includingLastEdit === TRUE) {
140  $lastChange = fetchSponsorLatestEditData($sponsorId);
141  $row ['lastChangeDateTimestamp'] = $lastChange['timestamp'];
142  $row ['lastChangeUserId'] = $lastChange['userId'];
143  $row ['lastChangeUserName'] = $lastChange['userName'];
144  }
145  }
146  $stmt->close();
147  return isset($row) ? $row : FALSE;
148  }
149 
150  /**
151  * Fetches the details a sponsor was last modified (all edit sections)
152  * @param integer $sponsorId id of the sponsor
153  * @return mixed
154  * - array containing timestamp, user id and user name
155  * - FALSE if an error occured
156  * @author Daniel Seichter
157  */
158  function fetchSponsorLatestEditData($sponsorId) {
159  global $mysqli, $db, $dbBackup;
160 
161  $stmt = $mysqli->prepare('SELECT *
162  FROM(
163  (SELECT bup_last_change_at, bup_last_change_by
164  FROM '.$dbBackup['sponsor'].' AS t1
165  WHERE bup_sponsor_id = ?
166  ORDER BY t1.bup_last_change_at DESC LIMIT 1)
167  UNION
168 
169  (SELECT bup_last_change_at, bup_last_change_by
170  FROM '.$dbBackup['contact_person'].' AS t2
171  WHERE bup_sponsor_id = ?
172  ORDER BY t2.bup_last_change_at DESC LIMIT 1)
173  UNION
174 
175  (SELECT bup_last_change_at, bup_last_change_by
176  FROM '.$dbBackup['sp_production_facility'].' AS t3
177  WHERE bup_sponsor_id = ?
178  ORDER BY t3.bup_last_change_at DESC LIMIT 1)
179  UNION
180 
181  (SELECT bup_last_change_at, bup_last_change_by
182  FROM(
183  (SELECT bup_last_change_at, bup_last_change_by, bup_sponsor_car_id
184  FROM '.$dbBackup['sponsor_car'].' AS t4
185  ORDER BY t4.bup_last_change_at DESC LIMIT 1)
186  UNION
187 
188  (SELECT bup_last_change_at, bup_last_change_by, bup_sponsor_car_id
189  FROM '.$dbBackup['sc_event'].' AS t5
190  ORDER BY t5.bup_last_change_at DESC LIMIT 1)
191  UNION
192 
193  (SELECT bup_last_change_at, bup_last_change_by, bup_sponsor_car_id
194  FROM '.$dbBackup['sc_attachment'].' AS t6
195  ORDER BY t6.bup_last_change_at DESC LIMIT 1)
196  UNION
197 
198  (SELECT bup_last_change_at, bup_last_change_by, bup_sponsor_car_id
199  FROM '.$dbBackup['sc_financial_donation'].' AS t7
200  ORDER BY t7.bup_last_change_at DESC LIMIT 1)
201  UNION
202 
203  (SELECT bup_last_change_at, bup_last_change_by, bup_sponsor_car_id
204  FROM '.$dbBackup['sc_material_donation'].' AS t8
205  ORDER BY t8.bup_last_change_at DESC LIMIT 1)
206  UNION
207 
208  (SELECT bup_last_change_at, bup_last_change_by, bup_sponsor_car_id
209  FROM '.$dbBackup['sc_produced_part'].' As t9
210  ORDER BY t9.bup_last_change_at DESC LIMIT 1)
211  )AS t10
212  WHERE bup_sponsor_car_id IN(
213  SELECT sponsor_car_id
214  FROM '.$db['sponsor_car'].' AS t11
215  WHERE t11.sponsor_id = ?)
216  ORDER BY t10.bup_last_change_at DESC LIMIT 1)
217 
218  )AS t12 ORDER BY t12.bup_last_change_at DESC LIMIT 1');
219  $stmt->bind_param('iiii', $sponsorId, $sponsorId, $sponsorId, $sponsorId);
220  $stmt->execute();
221 
222  $stmt->bind_result($timestamp, $userId);
223  $stmt->store_result();
224 
225  if ($stmt->fetch()) {
226  $row = array('timestamp' => $timestamp,
227  'userId' => $userId,
228  'userName' => (!empty($userId)) ? fetchUsername($userId) : '-');
229  }
230  $stmt->close();
231  return isset($row) ? $row : FALSE;
232  }
233 
234  /**
235  * Fetches the a list of modification details of a sponsor (each edit section separately)
236  * @param integer $sponsorId id of the sponsor
237  * @param integer $cnt number of entries in the list for each edit section
238  * @return mixed
239  * - array containing timestamp, user id and user name for each edit section
240  * - FALSE if an error occured
241  * @author Daniel Seichter
242  */
243  function fetchSponsorEditDataForEditSections($sponsorId, $cnt) {
244  global $mysqli, $db, $dbBackup;
245  $result = array();
246  // edit section sponsor details, last contact details, hints
247  $stmt = $mysqli->prepare('SELECT bup_last_change_at, bup_last_change_by
248  FROM '.$dbBackup['sponsor'].'
249  WHERE bup_sponsor_id = ?
250  ORDER BY bup_last_change_at DESC LIMIT ?');
251  $stmt->bind_param('ii', $sponsorId, $cnt);
252  $stmt->execute();
253 
254  $stmt->bind_result($timestamp, $userId);
255  $stmt->store_result();
256 
257  while ($stmt->fetch()) {
258  $result[1][] = array('timestamp' => $timestamp,
259  'userId' => $userId,
260  'userName' => (!empty($userId)) ? fetchUsername($userId) : '-');
261  }
262  $stmt->close();
263 
264  // edit section contact person details
265  $stmt = $mysqli->prepare('SELECT bup_last_change_at, bup_last_change_by
266  FROM '.$dbBackup['contact_person'].'
267  WHERE bup_sponsor_id = ?
268  ORDER BY bup_last_change_at DESC LIMIT ?');
269  $stmt->bind_param('ii', $sponsorId, $cnt);
270  $stmt->execute();
271 
272  $stmt->bind_result($timestamp, $userId);
273  $stmt->store_result();
274 
275  while ($stmt->fetch()) {
276  $result[2][] = array('timestamp' => $timestamp,
277  'userId' => $userId,
278  'userName' => (!empty($userId)) ? fetchUsername($userId) : '-');
279  }
280  $stmt->close();
281 
282  // edit section production facilities
283  $stmt = $mysqli->prepare('SELECT bup_last_change_at, bup_last_change_by
284  FROM '.$dbBackup['sp_production_facility'].'
285  WHERE bup_sponsor_id = ?
286  ORDER BY bup_last_change_at DESC LIMIT ?');
287  $stmt->bind_param('ii', $sponsorId, $cnt);
288  $stmt->execute();
289 
290  $stmt->bind_result($timestamp, $userId);
291  $stmt->store_result();
292 
293  while ($stmt->fetch()) {
294  $result[3][] = array('timestamp' => $timestamp,
295  'userId' => $userId,
296  'userName' => (!empty($userId)) ? fetchUsername($userId) : '-');
297  }
298  $stmt->close();
299 
300 
301  // edit section sponsor cars (lock only entry-point to the cars)
302  $stmt = $mysqli->prepare('SELECT bup_last_change_at, bup_last_change_by
303  FROM(
304  (SELECT bup_last_change_at, bup_last_change_by, bup_sponsor_car_id
305  FROM '.$dbBackup['sponsor_car'].' AS t1
306  ORDER BY t1.bup_last_change_at DESC LIMIT ?)
307  UNION
308 
309  (SELECT bup_last_change_at, bup_last_change_by, bup_sponsor_car_id
310  FROM '.$dbBackup['sc_event'].' AS t2
311  ORDER BY t2.bup_last_change_at DESC LIMIT ?)
312  UNION
313 
314  (SELECT bup_last_change_at, bup_last_change_by, bup_sponsor_car_id
315  FROM '.$dbBackup['sc_attachment'].' AS t3
316  ORDER BY t3.bup_last_change_at DESC LIMIT ?)
317  UNION
318 
319  (SELECT bup_last_change_at, bup_last_change_by, bup_sponsor_car_id
320  FROM '.$dbBackup['sc_financial_donation'].' AS t4
321  ORDER BY t4.bup_last_change_at DESC LIMIT ?)
322  UNION
323 
324  (SELECT bup_last_change_at, bup_last_change_by, bup_sponsor_car_id
325  FROM '.$dbBackup['sc_material_donation'].' AS t5
326  ORDER BY t5.bup_last_change_at DESC LIMIT ?)
327  UNION
328 
329  (SELECT bup_last_change_at, bup_last_change_by, bup_sponsor_car_id
330  FROM '.$dbBackup['sc_produced_part'].' As t6
331  ORDER BY t6.bup_last_change_at DESC LIMIT ?)
332  )AS t7
333  WHERE bup_sponsor_car_id IN(
334  SELECT sponsor_car_id
335  FROM '.$db['sponsor_car'].' AS t8
336  WHERE t8.sponsor_id = ?)
337  ORDER BY t7.bup_last_change_at DESC LIMIT ?');
338  $stmt->bind_param('iiiiiiii', $cnt, $cnt, $cnt, $cnt, $cnt, $cnt, $sponsorId, $cnt);
339  $stmt->execute();
340 
341  $stmt->bind_result($timestamp, $userId);
342  $stmt->store_result();
343 
344  while ($stmt->fetch()) {
345  $result[4][] = array('timestamp' => $timestamp,
346  'userId' => $userId,
347  'userName' => (!empty($userId)) ? fetchUsername($userId) : '-');
348  }
349  $stmt->close();
350 
351  return !empty($result) ? $result : FALSE;;
352  }
353 
354  /**
355  * Fetches all the production facilities of a sponsor
356  * @param integer $sponsorId id of the sponsor
357  * @return mixed
358  * - array containing the production facilities
359  * - FALSE if an error occured
360  * @author Daniel Seichter
361  */
362  function fetchSponsorProductionFacilities($sponsorId) {
363  global $mysqli, $db;
364  $stmt = $mysqli->prepare('SELECT
365  sp_production_facility_id,
366  t1.production_facility_id,
367  production_facility_name,
368  comments
369  FROM '.$db['sp_production_facility'].' AS t1 LEFT JOIN '.$db['production_facility'].' AS t2 ON t1.production_facility_id = t2.production_facility_id
370  WHERE sponsor_id = ?');
371  $stmt->bind_param('i', $sponsorId);
372  $stmt->execute();
373 
374  $stmt->bind_result($id, $productionFacilityId, $name, $comments);
375 
376  while ($stmt->fetch()) {
377  $rows [] = array('id' => $id,
378  'productionFacilityId' => $productionFacilityId,
379  'name' => $name,
380  'comments' => $comments);
381  }
382  $stmt->close();
383  return isset($rows) ? $rows : FALSE;
384  }
385 
386  /**
387  * Fetches all the contact persons of a sponsor
388  * @param integer $sponsorId id of the sponsor
389  * @return mixed
390  * - array containing the contact persons
391  * - FALSE if an error occured
392  * @author Daniel Seichter
393  */
394  function fetchSponsorContactPersons($sponsorId) {
395  global $mysqli, $db;
396  $stmt = $mysqli->prepare('SELECT
397  contact_person_id,
398  name,
399  position,
400  telephone_number,
401  mobile_phone_number,
402  email,
403  newsletter,
404  sponsor_id
405  FROM '.$db['contact_person'].
406  ' WHERE sponsor_id = ?');
407  $stmt->bind_param('i', $sponsorId);
408  $stmt->execute();
409 
410  $stmt->bind_result($id, $name, $position, $telephone, $mobile, $email, $newsletter, $sponsorId);
411 
412  while ($stmt->fetch()) {
413  $row[] = array('id' => $id,
414  'name' => $name,
415  'position' => $position,
416  'phone' => $telephone,
417  'mobile' => $mobile,
418  'email' => $email,
419  'newsletter' => $newsletter,
420  'sponsorId' => $sponsorId);
421  }
422  $stmt->close();
423  return isset($row) ? $row : FALSE;
424  }
425 
426  /**
427  * Fetches all the information needed for a vCard
428  * @param integer $contactPersonId id of a contact person
429  * @return mixed
430  * - array containing the information
431  * - FALSE if an error occured
432  * @author Alexander Vorndran
433  */
434  function fetchContactPersonDetailsForVCard($contactPersonId) {
435  global $mysqli, $db;
436  $qry =
437  "SELECT cp.name, cp.email, cp.mobile_phone_number, cp.telephone_number, cp.position, cp.newsletter,
438  sp.sponsor_name, sp.street, sp.house_number, sp.zip_code, sp.town, sp.country
439  FROM ".$db['contact_person']." AS cp LEFT JOIN ".$db['sponsor']." AS sp ON cp.sponsor_id = sp.sponsor_id
440  WHERE contact_person_id = ?";
441  $stmt = $mysqli->prepare($qry);
442  $stmt->bind_param('i', $contactPersonId);
443  $stmt->execute();
444 
445  $stmt->bind_result($name, $email, $mobile, $telephone, $position, $newsletter,
446  $sponsor, $street, $house_number, $zip_code, $town, $country);
447 
448  while ($stmt->fetch()) {
449  $data = array(
450  'name' => $name,
451  'email' => $email,
452  'mobileNumber' => $mobile,
453  'phoneNumber' => $telephone,
454  'position' => $position,
455  'sponsor' => $sponsor,
456  'street' => $street,
457  'house_number'=> $house_number,
458  'zip_code' => $zip_code,
459  'town' => $town,
460  'country' => $country,
461  'newsletter' => ($newsletter == 1) ? 'Newsletter: Ja' : 'Newsletter: Nein'
462  );
463  }
464  $stmt->close();
465  return isset($data) ? $data : FALSE;
466  }
467 
468  /**
469  * Checks if a contact person exists
470  * @param integer $contactPersonId id of a contact person
471  * @return boolean TRUE if contact person exists, else FALSE
472  * @author Alexander Vorndran
473  */
474  function contactPersonExists($contactPersonId) {
475  global $mysqli, $db;
476  $qry = "SELECT COUNT(contact_person_id) FROM ".$db['contact_person']." WHERE contact_person_id = ?";
477  $stmt = $mysqli->prepare($qry);
478  $stmt->bind_param('i', $contactPersonId);
479  $stmt->execute();
480 
481  $stmt->bind_result($num);
482 
483  while ($stmt->fetch()) {
484  $res = $num;
485  }
486  $stmt->close();
487  return ($res == 1) ? TRUE : FALSE;
488  }
489 
490  /**
491  * Fetches all the car names of a sponsor
492  * @param integer $sponsorId id of the sponsor
493  * @return mixed
494  * - array containing the car names
495  * - FALSE if an error occured
496  * @author Daniel Seichter
497  */
498  function fetchSponsorCarNames($sponsorId) {
499  global $mysqli, $db;
500  $stmt = $mysqli->prepare('SELECT
501  t1.car_id,
502  car_name
503  FROM '.$db['sponsor_car'].' AS t1 LEFT JOIN '.$db['car'].' AS t2 ON t1.car_id = t2.car_id
504  WHERE sponsor_id = ?
505  ORDER BY t1.car_id DESC');
506  $stmt->bind_param('i', $sponsorId);
507  $stmt->execute();
508 
509  $stmt->bind_result($id, $name);
510 
511  while ($stmt->fetch()) {
512  $rows [] = array('id' => $id,
513  'name' => $name);
514  }
515  $stmt->close();
516  return isset($rows) ? $rows : FALSE;
517  }
518 
519  /**
520  * Fetches all the cars, which are not noted in a sponsor
521  * @param integer $sponsorId id of the sponsor
522  * @return mixed
523  * - array containing the possible new cars
524  * - FALSE if an error occured
525  * @author Daniel Seichter
526  */
527  function fetchPossibleNewSponsorCars($sponsorId) {
528  global $mysqli, $db;
529  $stmt = $mysqli->prepare('SELECT
530  t1.car_id,
531  t1.car_name
532  FROM '.$db['car'].' AS t1
533  WHERE t1.car_id NOT IN (SELECT car_id FROM '.$db['sponsor_car'].' WHERE sponsor_id = ?)');
534  $stmt->bind_param('i', $sponsorId);
535  $stmt->execute();
536 
537  $stmt->bind_result($id, $name);
538 
539  while ($stmt->fetch()) {
540  $rows [] = array('id' => $id,
541  'name' => $name);
542  }
543  $stmt->close();
544  return isset($rows) ? $rows : FALSE;
545  }
546 
547 
548  /**
549  * Updates all the details of a sponsor
550  * @param array $sponsorArray new sponsor details including the sponsor id
551  * @return boolean FALSE if an error occured
552  * @author Daniel Seichter
553  */
554  function updateSponsorDetails($sponsorArray) {
555  global $mysqli, $db, $loggedInUser;
556 
557  $stmt = $mysqli->prepare('UPDATE '.$db['sponsor'].' SET
558  sponsor_name = ?,
559  street = ?,
560  house_number = ?,
561  zip_code = ?,
562  town = ?,
563  country = ?,
564  website = ?,
565  sponsor_hints = ?,
566  sponsor_logo = ?,
567  last_contact_date = ?,
568  last_contact_person = ?,
569  status_id = ?,
570  lock_time = 0,
571  lock_user_id = ?
572  WHERE sponsor_id = ?');
573  $stmt->bind_param("sssssssssssiii",
574  $sponsorArray['name'],
575  $sponsorArray['street'],
576  $sponsorArray['houseNumber'],
577  $sponsorArray['zipCode'],
578  $sponsorArray['town'],
579  $sponsorArray['country'],
580  $sponsorArray['website'],
581  $sponsorArray['hints'],
582  $sponsorArray['sponsorLogo'],
583  $sponsorArray['lastContactDateTimestamp'],
584  $sponsorArray['lastContactPerson'],
585  $sponsorArray['statusId'],
586  $loggedInUser->userId,
587  $sponsorArray['id']);
588  $result = $stmt->execute();
589  $stmt->close();
590  return $result;
591  }
592 
593  /**
594  * Deletes a production facility
595  * @param integer $sponsorProductionFacilityId id of the production facility
596  * @param boolean $updateLockUserIdBefore [optional]
597  * - update lock user id before delete
598  * @return boolean FALSE if an error occured
599  * @author Daniel Seichter
600  */
601  function deleteSponsorProductionFacility($sponsorProductionFacilityId, $updateLockUserIdBefore = FALSE) {
602  global $mysqli, $db, $loggedInUser;
603 
604  if ($updateLockUserIdBefore === TRUE) {
605  //update lock_user_id before delete
606  $stmt = $mysqli->prepare('UPDATE '.$db['sp_production_facility'].' SET
607  lock_time = -1,
608  lock_user_id = ?
609  WHERE sp_production_facility_id = ?');
610  $stmt->bind_param("ii",
611  $loggedInUser->userId,
612  $sponsorProductionFacilityId);
613  $stmt->execute();
614  $stmt->close();
615  }
616  //delete
617  $stmt = $mysqli->prepare("DELETE FROM " . $db['sp_production_facility'] . "
618  WHERE sp_production_facility_id = ?");
619  $stmt->bind_param("i",$sponsorProductionFacilityId);
620  $result = $stmt->execute();
621  $stmt->close();
622  return $result;
623  }
624 
625  /**
626  * Updates all the production facilities of a sponsor
627  * @param array $sponsorProductionFacilities new details of the production facilities
628  * @return boolean FALSE if an error occured
629  * @author Daniel Seichter
630  */
631  function updateSponsorProductionFacilities($sponsorProductionFacilities) {
632  global $mysqli, $db, $loggedInUser;
633  foreach ($sponsorProductionFacilities as $sponsorProductionFacility) {
634  $stmt = $mysqli->prepare('UPDATE '.$db['sp_production_facility'].' SET
635  production_facility_id = ?,
636  comments = ?,
637  lock_time = 0,
638  lock_user_id = ?
639  WHERE sp_production_facility_id = ?');
640  $stmt->bind_param("isii",
641  $sponsorProductionFacility['productionFacilityId'],
642  $sponsorProductionFacility['comments'],
643  $loggedInUser->userId,
644  $sponsorProductionFacility['id']);
645 
646  $result = $stmt->execute();
647  if ($result == FALSE) {
648  return FALSE;
649  }
650  $stmt->close();
651  }
652  return TRUE;
653  }
654 
655  /**
656  * Adds a new production facility
657  * @param array $sponsorProductionFacility details of the production facility including the sponsor id
658  * @return boolean FALSE if an error occured
659  * @author Daniel Seichter
660  */
661  function addSponsorProductionFacility($sponsorProductionFacility) {
662  global $mysqli, $db, $loggedInUser;
663  $stmt = $mysqli->prepare("INSERT INTO " . $db['sp_production_facility'] . "
664  (production_facility_id, sponsor_id, comments, lock_time, lock_user_id)
665  VALUES
666  (?, ?, ?, 0, ?)");
667  $stmt->bind_param("iisi", $sponsorProductionFacility['productionFacilityId'],
668  $sponsorProductionFacility['sponsor_id'],
669  $sponsorProductionFacility['comments'],
670  $loggedInUser->userId);
671  $result = $stmt->execute();
672  $stmt->close();
673  return $result;
674  }
675 
676  /**
677  * Deletes a contact person
678  * @param integer $contactPersonId id of the contact person
679  * @param boolean $updateLockUserIdBefore [optional]
680  * - update lock user id before delete
681  * @return boolean FALSE if an error occured
682  * @author Daniel Seichter
683  */
684  function deleteSponsorContactPerson($contactPersonId, $updateLockUserIdBefore = FALSE) {
685  global $mysqli, $db, $loggedInUser;
686 
687  if ($updateLockUserIdBefore === TRUE) {
688  //update lock_user_id before delete
689  $stmt = $mysqli->prepare('UPDATE '.$db['contact_person'].' SET
690  lock_time = -1,
691  lock_user_id = ?
692  WHERE contact_person_id = ?');
693  $stmt->bind_param("ii",
694  $loggedInUser->userId,
695  $contactPersonId);
696  $stmt->execute();
697  $stmt->close();
698  }
699  //delete
700  $stmt = $mysqli->prepare("DELETE FROM " . $db['contact_person'] . "
701  WHERE contact_person_id = ?");
702  $stmt->bind_param("i", $contactPersonId);
703  $result = $stmt->execute();
704  $stmt->close();
705  return $result;
706  }
707 
708  /**
709  * Updates all the contact persons of a sponsor
710  * @param array $sponsorContactPersons new details of the contact persons
711  * @return boolean FALSE if an error occured
712  * @author Daniel Seichter
713  */
714  function updateSponsorContactPersons($sponsorContactPersons) {
715  global $mysqli, $db, $loggedInUser;
716  foreach ($sponsorContactPersons as $sponsorContactPerson) {
717  $stmt = $mysqli->prepare('UPDATE '.$db['contact_person'].' SET
718  name = ?,
719  position = ?,
720  telephone_number = ?,
721  mobile_phone_number = ?,
722  email = ?,
723  newsletter = ?,
724  sponsor_id = ?,
725  lock_time = 0,
726  lock_user_id = ?
727  WHERE contact_person_id = ?');
728  $stmt->bind_param("sssssiiii",
729  $sponsorContactPerson['name'],
730  $sponsorContactPerson['position'],
731  $sponsorContactPerson['phone'],
732  $sponsorContactPerson['mobile'],
733  $sponsorContactPerson['email'],
734  $sponsorContactPerson['newsletter'],
735  $sponsorContactPerson['sponsorId'],
736  $loggedInUser->userId,
737  $sponsorContactPerson['id']);
738 
739  $result = $stmt->execute();
740  if ($result == FALSE) {
741  return FALSE;
742  }
743  $stmt->close();
744  }
745  return TRUE;
746  }
747 
748  /**
749  * Adds a new contact person
750  * @param array $sponsorContactPerson details of the contact person including the sponsor id
751  * @return boolean FALSE if an error occured
752  * @author Daniel Seichter
753  */
754  function addSponsorContactPerson($sponsorContactPerson) {
755  global $mysqli, $db, $loggedInUser;
756  $stmt = $mysqli->prepare("INSERT INTO " . $db['contact_person'] . "
757  (name, position, telephone_number, mobile_phone_number, email, newsletter, sponsor_id, lock_time, lock_user_id)
758  VALUES
759  (?, ?, ?, ?, ?, ?, ?, 0, ?)");
760  $stmt->bind_param("sssssiii", $sponsorContactPerson['name'],
761  $sponsorContactPerson['position'],
762  $sponsorContactPerson['phone'],
763  $sponsorContactPerson['mobile'],
764  $sponsorContactPerson['email'],
765  $sponsorContactPerson['newsletter'],
766  $sponsorContactPerson['sponsor_id'],
767  $loggedInUser->userId);
768  $result = $stmt->execute();
769  $stmt->close();
770  return $result;
771  }
772 
773  /**
774  * Adds a new sponsor
775  * @param array $sponsor details of the sponsor
776  * @return mixed
777  * id of the new sponsor
778  * FALSE if an error occured
779  * @author Daniel Seichter
780  */
781  function addSponsor($sponsor) {
782  global $mysqli, $db, $loggedInUser;
783  $stmt = $mysqli->prepare("INSERT INTO " . $db['sponsor'] . "
784  (sponsor_name, street, house_number, zip_code, town, country, status_id, lock_user_id)
785  VALUES
786  (?, ?, ?, ?, ?, ?, ?, ?)");
787  $stmt->bind_param("ssssssii", $sponsor['name'],
788  $sponsor['street'],
789  $sponsor['houseNumber'],
790  $sponsor['zipCode'],
791  $sponsor['town'],
792  $sponsor['country'],
793  $sponsor['statusId'],
794  $loggedInUser->userId);
795  $result = $stmt->execute();
796  $stmt->close();
797  return ($result === TRUE) ? $mysqli->insert_id : FALSE;
798  }
799 
800 
801 
802 
803  // sponsor car function:
804 
805 
806  /**
807  * Fetches all the details of a sponsor car
808  * @param integer $sponsorId id of the sponsor
809  * @param integer $sponsorCarId [optional]
810  * - id of the sponsor car
811  * @return mixed
812  * - array containing the details of all the sponsor cars, if sponsor id is given
813  * - array containing the details of one sponsor cars, if sponsor car id is given
814  * - FALSE if an error occured
815  * @author Daniel Seichter
816  */
817  function fetchSponsorCarDetails($sponsorId, $sponsorCarId = null) {
818  global $mysqli, $db;
819  if (isset($sponsorId)) {
820  $stmt = $mysqli->prepare('SELECT
821  sponsor_car_id,
822  t1.car_id,
823  car_name,
824  co_determination,
825  hints,
826  t1.category_id,
827  category_name
828  FROM '.$db['sponsor_car'].' AS t1
829  LEFT JOIN '.$db['car'].' AS t2 ON t1.car_id = t2.car_id
830  LEFT JOIN '.$db['category'].' AS t3 ON t1.category_id = t3.category_id
831  WHERE sponsor_id = ?
832  ORDER BY t1.car_id DESC');
833  $stmt->bind_param('i', $sponsorId);
834  $stmt->execute();
835 
836  $stmt->bind_result($id, $carId, $name, $codetermination, $hints, $categoryId, $categoryName);
837 
838  while ($stmt->fetch()) {
839  $rows [] = array('id' => $id,
840  'carId' => $carId,
841  'name' => $name,
842  'codetermination' => $codetermination,
843  'hints' => $hints,
844  'categoryId' => $categoryId,
845  'categoryName' => $categoryName);
846  }
847  $stmt->close();
848  return isset($rows) ? $rows : FALSE;
849  }
850  elseif (isset($sponsorCarId)) {
851  $stmt = $mysqli->prepare('SELECT
852  sponsor_car_id,
853  t1.car_id,
854  car_name,
855  co_determination,
856  hints,
857  t1.category_id,
858  category_name
859  FROM '.$db['sponsor_car'].' AS t1
860  LEFT JOIN '.$db['car'].' AS t2 ON t1.car_id = t2.car_id
861  LEFT JOIN '.$db['category'].' AS t3 ON t1.category_id = t3.category_id
862  WHERE sponsor_car_id = ?
863  ORDER BY t1.car_id DESC');
864  $stmt->bind_param('i', $sponsorCarId);
865  $stmt->execute();
866 
867  $stmt->bind_result($id, $carId, $name, $codetermination, $hints, $categoryId, $categoryName);
868 
869  if ($stmt->fetch()) {
870  $row = array('id' => $id,
871  'carId' => $carId,
872  'name' => $name,
873  'codetermination' => $codetermination,
874  'hints' => $hints,
875  'categoryId' => $categoryId,
876  'categoryName' => $categoryName);
877  }
878  $stmt->close();
879  return isset($row) ? $row : FALSE;
880  }
881  else {
882  return FALSE;
883  }
884  }
885 
886  /**
887  * Fetches all the events of a sponsor car
888  * @param integer $sponsorCarId id of the sponsor car
889  * @return mixed
890  * - array containing the events of the sponsor car
891  * - FALSE if an error occured
892  * @author Daniel Seichter
893  */
894  function fetchSponsorCarEvents($sponsorCarId) {
895  global $mysqli, $db;
896  $stmt = $mysqli->prepare('SELECT
897  sc_event_id,
898  event,
899  date
900  FROM '.$db['sc_event'].'
901  WHERE sponsor_car_id = ?
902  ORDER BY date DESC');
903  $stmt->bind_param('i', $sponsorCarId);
904  $stmt->execute();
905 
906  $stmt->bind_result($id, $event, $date);
907  while ($stmt->fetch()) {
908  $rows [] = array('id' => $id,
909  'event' => $event,
910  'timestamp' => $date);
911  }
912  $stmt->close();
913  return isset($rows) ? $rows : FALSE;
914  }
915 
916  /**
917  * Fetches all the financial donations of a sponsor car
918  * @param integer $sponsorCarId id of the sponsor car
919  * @return mixed
920  * - array containing the financial donations of the sponsor car
921  * - FALSE if an error occured
922  * @author Daniel Seichter
923  */
924  function fetchSponsorCarFinancialDonations($sponsorCarId) {
925  global $mysqli, $db;
926  $stmt = $mysqli->prepare('SELECT
927  sc_financial_donation_id,
928  amount,
929  date
930  FROM '.$db['sc_financial_donation'].'
931  WHERE sponsor_car_id = ?
932  ORDER BY date DESC');
933  $stmt->bind_param('i', $sponsorCarId);
934  $stmt->execute();
935 
936  $stmt->bind_result($id, $amount, $date);
937  while ($stmt->fetch()) {
938  $rows [] = array('id' => $id,
939  'amount' => $amount,
940  'timestamp' => $date);
941  }
942  $stmt->close();
943  return isset($rows) ? $rows : FALSE;
944  }
945 
946  /**
947  * Fetches all the material donations of a sponsor car
948  * @param integer $sponsorCarId id of the sponsor car
949  * @return mixed
950  * - array containing the material donations of the sponsor car
951  * - FALSE if an error occured
952  * @author Daniel Seichter
953  */
954  function fetchSponsorCarMaterialDonation($sponsorCarId) {
955  global $mysqli, $db;
956  $stmt = $mysqli->prepare('SELECT
957  sc_material_donation_id,
958  object,
959  value,
960  date
961  FROM '.$db['sc_material_donation'].'
962  WHERE sponsor_car_id = ?
963  ORDER BY date DESC');
964  $stmt->bind_param('i', $sponsorCarId);
965  $stmt->execute();
966 
967  $stmt->bind_result($id, $object, $amount, $date);
968  while ($stmt->fetch()) {
969  $rows [] = array('id' => $id,
970  'object' => $object,
971  'amount' => $amount,
972  'timestamp' => $date);
973  }
974  $stmt->close();
975  return isset($rows) ? $rows : FALSE;
976  }
977 
978  /**
979  * Fetches all the produced parts of a sponsor car
980  * @param integer $sponsorCarId id of the sponsor car
981  * @return mixed
982  * - array containing the produced parts of the sponsor car
983  * - FALSE if an error occured
984  * @author Daniel Seichter
985  */
986  function fetchSponsorCarProducedParts($sponsorCarId) {
987  global $mysqli, $db;
988  $stmt = $mysqli->prepare('SELECT
989  sc_produced_part_id,
990  produced_part_name,
991  produced_part_value,
992  date
993  FROM '.$db['sc_produced_part'].'
994  WHERE sponsor_car_id = ?
995  ORDER BY date DESC');
996  $stmt->bind_param('i', $sponsorCarId);
997  $stmt->execute();
998 
999  $stmt->bind_result($id, $name, $value, $date);
1000  while ($stmt->fetch()) {
1001  $rows [] = array('id' => $id,
1002  'name' => $name,
1003  'value' => $value,
1004  'timestamp' => $date);
1005  }
1006  $stmt->close();
1007  return isset($rows) ? $rows : FALSE;
1008  }
1009 
1010  /**
1011  * Fetches all the attachments of a sponsor car
1012  * @param integer $sponsorCarId id of the sponsor car
1013  * @return mixed
1014  * - array containing the attachments of the sponsor car
1015  * - FALSE if an error occured
1016  * @author Daniel Seichter
1017  */
1018  function fetchSponsorCarAttachments($sponsorCarId) {
1019  global $mysqli, $db;
1020  $stmt = $mysqli->prepare('SELECT
1021  sc_attachment_id,
1022  file_name,
1023  path,
1024  date
1025  FROM '.$db['sc_attachment'].'
1026  WHERE sponsor_car_id = ?
1027  ORDER BY file_name ASC');
1028  $stmt->bind_param('i', $sponsorCarId);
1029  $stmt->execute();
1030 
1031  $stmt->bind_result($id, $filename, $path, $date);
1032  while ($stmt->fetch()) {
1033  $rows [] = array('id' => $id,
1034  'filename' => $filename,
1035  'path' => $path,
1036  'timestamp' => $date);
1037  }
1038  $stmt->close();
1039  return isset($rows) ? $rows : FALSE;
1040  }
1041 
1042  /**
1043  * Updates all the details of a sponsor car
1044  * @param array $sponsorCar new sponsor car details including the sponsor car id
1045  * @return boolean FALSE if an error occured
1046  * @author Daniel Seichter
1047  */
1048  function updateSponsorCarDetails($sponsorCar) {
1049  global $mysqli, $db, $loggedInUser;
1050 
1051  $stmt = $mysqli->prepare('UPDATE '.$db['sponsor_car'].' SET
1052  co_determination = ?,
1053  hints = ?,
1054  category_id = ?,
1055  lock_time = 0,
1056  lock_user_id = ?
1057  WHERE sponsor_car_id = ?');
1058  $stmt->bind_param("isiii",
1059  $sponsorCar['codetermination'],
1060  $sponsorCar['hints'],
1061  $sponsorCar['categoryId'],
1062  $loggedInUser->userId,
1063  $sponsorCar['id']);
1064 
1065  $result = $stmt->execute();
1066  $stmt->close();
1067  return $result;
1068  }
1069 
1070  /**
1071  * Adds a new sponsor car
1072  * @param integer $carId id of the car (used to assign car name)
1073  * @param integer $sponsorId id of the sponsor
1074  * @return mixed
1075  * - id of the new sponsor car
1076  * - FALSE if an error occured
1077  * @author Daniel Seichter
1078  */
1079  function addSponsorCar($carId, $sponsorId) {
1080  global $mysqli, $db, $loggedInUser;
1081  $stmt = $mysqli->prepare("INSERT INTO " . $db['sponsor_car'] . "
1082  (co_determination, hints, car_id, sponsor_id, category_id, lock_time, lock_user_id)
1083  VALUES
1084  (-1, '', ?, ?, -1, 0, ?)");
1085  $stmt->bind_param("iii", $carId, $sponsorId, $loggedInUser->userId);
1086  $result = $stmt->execute();
1087  $stmt->close();
1088  return ($result === TRUE) ? $mysqli->insert_id : FALSE;
1089  }
1090 
1091  /**
1092  * Updates all the events of a sponsor car
1093  * @param array $events new details of the events
1094  * @return boolean FALSE if an error occured
1095  * @author Daniel Seichter
1096  */
1097  function updateSponsorCarEvents($events) {
1098  global $mysqli, $db, $loggedInUser;
1099  foreach ($events as $event) {
1100  $stmt = $mysqli->prepare('UPDATE '.$db['sc_event'].' SET
1101  event = ?,
1102  date = ?,
1103  lock_time = 0,
1104  lock_user_id = ?
1105  WHERE sc_event_id = ?');
1106  $stmt->bind_param("siii",
1107  $event['event'],
1108  $event['timestamp'],
1109  $loggedInUser->userId,
1110  $event['id']);
1111 
1112  $result = $stmt->execute();
1113  if ($result == FALSE) {
1114  return FALSE;
1115  }
1116  $stmt->close();
1117  }
1118  return TRUE;
1119  }
1120 
1121  /**
1122  * Adds a new event
1123  * @param array $event details of the event including the sponsor car id
1124  * @return mixed
1125  * - id of the new event
1126  * - FALSE if an error occured
1127  * @author Daniel Seichter
1128  */
1129  function addSponsorCarEvent($event) {
1130  global $mysqli, $db, $loggedInUser;
1131  $stmt = $mysqli->prepare("INSERT INTO " . $db['sc_event'] . "
1132  (sponsor_car_id, event, date, lock_time, lock_user_id)
1133  VALUES
1134  (?, ?, ?, 0, ?)");
1135  $stmt->bind_param("isii", $event['sponsorCarId'],
1136  $event['event'],
1137  $event['timestamp'],
1138  $loggedInUser->userId);
1139  $result = $stmt->execute();
1140  $stmt->close();
1141  return ($result === TRUE) ? $mysqli->insert_id : FALSE;
1142  }
1143 
1144  /**
1145  * Deletes an event
1146  * @param integer $eventId id of the event
1147  * @return boolean FALSE if an error occured
1148  * @author Daniel Seichter
1149  */
1150  function deleteSponsorCarEvent($eventId) {
1151  global $mysqli, $db, $loggedInUser;
1152  //update lock_user_id before delete
1153  $stmt = $mysqli->prepare('UPDATE '.$db['sc_event'].' SET
1154  lock_time = -1,
1155  lock_user_id = ?
1156  WHERE sc_event_id = ?');
1157  $stmt->bind_param("ii",
1158  $loggedInUser->userId,
1159  $eventId);
1160  $stmt->execute();
1161  $stmt->close();
1162 
1163  //delete
1164  $stmt = $mysqli->prepare("DELETE FROM " . $db['sc_event'] . "
1165  WHERE sc_event_id = ?");
1166  $stmt->bind_param("i",$eventId);
1167  $result = $stmt->execute();
1168  $stmt->close();
1169  return $result;
1170  }
1171 
1172  /**
1173  * Updates all the financial donations of a sponsor car
1174  * @param array $financialDonations new details of the financial donations
1175  * @return boolean FALSE if an error occured
1176  * @author Daniel Seichter
1177  */
1178  function updateSponsorCarFinancialDonations($financialDonations) {
1179  global $mysqli, $db, $loggedInUser;
1180  foreach ($financialDonations as $financialDonation) {
1181  $stmt = $mysqli->prepare('UPDATE '.$db['sc_financial_donation'].' SET
1182  amount = ?,
1183  date = ?,
1184  lock_time = 0,
1185  lock_user_id = ?
1186  WHERE sc_financial_donation_id = ?');
1187  $stmt->bind_param("diii",
1188  $financialDonation['amount'],
1189  $financialDonation['timestamp'],
1190  $loggedInUser->userId,
1191  $financialDonation['id']);
1192 
1193  $result = $stmt->execute();
1194  if ($result == FALSE) {
1195  return FALSE;
1196  }
1197  $stmt->close();
1198  }
1199  return TRUE;
1200  }
1201 
1202  /**
1203  * Adds a new financial donation
1204  * @param array $financialDonation details of the financial donation including the sponsor car id
1205  * @return mixed
1206  * - id of the new financial donation
1207  * - FALSE if an error occured
1208  * @author Daniel Seichter
1209  */
1210  function addSponsorCarFinancialDonation($financialDonation) {
1211  global $mysqli, $db, $loggedInUser;
1212  $stmt = $mysqli->prepare("INSERT INTO " . $db['sc_financial_donation'] . "
1213  (sponsor_car_id, amount, date, lock_time, lock_user_id)
1214  VALUES
1215  (?, ?, ?, 0, ?)");
1216  $stmt->bind_param("idii", $financialDonation['sponsorCarId'],
1217  $financialDonation['amount'],
1218  $financialDonation['timestamp'],
1219  $loggedInUser->userId);
1220  $result = $stmt->execute();
1221  $stmt->close();
1222  return ($result === TRUE) ? $mysqli->insert_id : FALSE;
1223  }
1224 
1225  /**
1226  * Deletes a financial donation
1227  * @param integer $financialDonationId id of the financial donation
1228  * @return boolean FALSE if an error occured
1229  * @author Daniel Seichter
1230  */
1231  function deleteSponsorCarFinancialDonation($financialDonationId) {
1232  global $mysqli, $db, $loggedInUser;
1233  //update lock_user_id before delete
1234  $stmt = $mysqli->prepare('UPDATE '.$db['sc_financial_donation'].' SET
1235  lock_time = -1,
1236  lock_user_id = ?
1237  WHERE sc_financial_donation_id = ?');
1238  $stmt->bind_param("ii",
1239  $loggedInUser->userId,
1240  $financialDonationId);
1241  $stmt->execute();
1242  $stmt->close();
1243 
1244  //delete
1245  $stmt = $mysqli->prepare("DELETE FROM " . $db['sc_financial_donation'] . "
1246  WHERE sc_financial_donation_id = ?");
1247  $stmt->bind_param("i",$financialDonationId);
1248  $result = $stmt->execute();
1249  $stmt->close();
1250  return $result;
1251  }
1252 
1253  /**
1254  * Updates all the material donations of a sponsor car
1255  * @param array $materialDonations new details of the material donations
1256  * @return boolean FALSE if an error occured
1257  * @author Daniel Seichter
1258  */
1259  function updateSponsorCarMaterialDonations($materialDonations) {
1260  global $mysqli, $db, $loggedInUser;
1261  foreach ($materialDonations as $materialDonation) {
1262  $stmt = $mysqli->prepare('UPDATE '.$db['sc_material_donation'].' SET
1263  object = ?,
1264  value = ?,
1265  date = ?,
1266  lock_time = 0,
1267  lock_user_id = ?
1268  WHERE sc_material_donation_id = ?');
1269  $stmt->bind_param("sdiii",
1270  $materialDonation['object'],
1271  $materialDonation['amount'],
1272  $materialDonation['timestamp'],
1273  $loggedInUser->userId,
1274  $materialDonation['id']);
1275 
1276  $result = $stmt->execute();
1277  if ($result == FALSE) {
1278  return FALSE;
1279  }
1280  $stmt->close();
1281  }
1282  return TRUE;
1283  }
1284 
1285  /**
1286  * Adds a new material donation
1287  * @param array $materialDonation details of the material donation including the sponsor car id
1288  * @return mixed
1289  * - id of the new material donation
1290  * - FALSE if an error occured
1291  * @author Daniel Seichter
1292  */
1293  function addSponsorCarMaterialDonation($materialDonation) {
1294  global $mysqli, $db, $loggedInUser;
1295  $stmt = $mysqli->prepare("INSERT INTO " . $db['sc_material_donation'] . "
1296  (sponsor_car_id, object, value, date, lock_time, lock_user_id)
1297  VALUES
1298  (?, ?, ?, ?, 0, ?)");
1299  $stmt->bind_param("isdii", $materialDonation['sponsorCarId'],
1300  $materialDonation['object'],
1301  $materialDonation['amount'],
1302  $materialDonation['timestamp'],
1303  $loggedInUser->userId);
1304  $result = $stmt->execute();
1305  $stmt->close();
1306  return ($result === TRUE) ? $mysqli->insert_id : FALSE;
1307  }
1308 
1309  /**
1310  * Deletes a material donation
1311  * @param integer $materialDonationId id of the material donation
1312  * @return boolean FALSE if an error occured
1313  * @author Daniel Seichter
1314  */
1315  function deleteSponsorCarMaterialDonation($materialDonationId) {
1316  global $mysqli, $db, $loggedInUser;
1317  //update lock_user_id before delete
1318  $stmt = $mysqli->prepare('UPDATE '.$db['sc_material_donation'].' SET
1319  lock_time = -1,
1320  lock_user_id = ?
1321  WHERE sc_material_donation_id = ?');
1322  $stmt->bind_param("ii",
1323  $loggedInUser->userId,
1324  $materialDonationId);
1325  $stmt->execute();
1326  $stmt->close();
1327 
1328  //delete
1329  $stmt = $mysqli->prepare("DELETE FROM " . $db['sc_material_donation'] . "
1330  WHERE sc_material_donation_id = ?");
1331  $stmt->bind_param("i", $materialDonationId);
1332  $result = $stmt->execute();
1333  $stmt->close();
1334  return $result;
1335  }
1336 
1337  /**
1338  * Updates all the produced parts of a sponsor car
1339  * @param array $producedParts new details of the produced parts
1340  * @return boolean FALSE if an error occured
1341  * @author Daniel Seichter
1342  */
1343  function updateSponsorCarProducedParts($producedParts) {
1344  global $mysqli, $db, $loggedInUser;
1345  foreach ($producedParts as $producedPart) {
1346  $stmt = $mysqli->prepare('UPDATE '.$db['sc_produced_part'].' SET
1347  produced_part_name = ?,
1348  produced_part_value = ?,
1349  date = ?,
1350  lock_time = 0,
1351  lock_user_id = ?
1352  WHERE sc_produced_part_id = ?');
1353  $stmt->bind_param("sdiii",
1354  $producedPart['name'],
1355  $producedPart['value'],
1356  $producedPart['timestamp'],
1357  $loggedInUser->userId,
1358  $producedPart['id']);
1359  $result = $stmt->execute();
1360  if ($result == FALSE) {
1361  return FALSE;
1362  }
1363  $stmt->close();
1364  }
1365  return TRUE;
1366  }
1367 
1368  /**
1369  * Adds a new produced part
1370  * @param array $producedPart details of the produced part including the sponsor car id
1371  * @return mixed
1372  * - id of the new produced part
1373  * - FALSE if an error occured
1374  * @author Daniel Seichter
1375  */
1376  function addSponsorCarProducedPart($producedPart) {
1377  global $mysqli, $db, $loggedInUser;
1378  $stmt = $mysqli->prepare("INSERT INTO " . $db['sc_produced_part'] . "
1379  (sponsor_car_id, produced_part_name, produced_part_value, date, lock_time, lock_user_id)
1380  VALUES
1381  (?, ?, ?, ?, 0, ?)");
1382  $stmt->bind_param("isdii", $producedPart['sponsorCarId'],
1383  $producedPart['name'],
1384  $producedPart['value'],
1385  $producedPart['timestamp'],
1386  $loggedInUser->userId);
1387  $result = $stmt->execute();
1388  $stmt->close();
1389  return ($result === TRUE) ? $mysqli->insert_id : FALSE;
1390  }
1391 
1392  /**
1393  * Deletes a produced part
1394  * @param integer $producedPartId id of the produced part
1395  * @return boolean FALSE if an error occured
1396  * @author Daniel Seichter
1397  */
1398  function deleteSponsorCarProducedPart($producedPartId) {
1399  global $mysqli, $db, $loggedInUser;
1400  //update lock_user_id before delete
1401  $stmt = $mysqli->prepare('UPDATE '.$db['sc_produced_part'].' SET
1402  lock_time = -1,
1403  lock_user_id = ?
1404  WHERE sc_produced_part_id = ?');
1405  $stmt->bind_param("ii",
1406  $loggedInUser->userId,
1407  $producedPartId);
1408  $stmt->execute();
1409  $stmt->close();
1410 
1411  //delete
1412  $stmt = $mysqli->prepare("DELETE FROM " . $db['sc_produced_part'] . "
1413  WHERE sc_produced_part_id = ?");
1414  $stmt->bind_param("i", $producedPartId);
1415  $result = $stmt->execute();
1416  $stmt->close();
1417  return $result;
1418 
1419  }
1420 
1421  /**
1422  * Updates all the attachments of a sponsor car
1423  * @param array $attachments new details of the attachments
1424  * @return boolean FALSE if an error occured
1425  * @author Daniel Seichter
1426  */
1427  function updateSponsorCarAttachments($attachments) {
1428  global $mysqli, $db, $loggedInUser;
1429  foreach ($attachments as $attachment) {
1430  $stmt = $mysqli->prepare('UPDATE '.$db['sc_attachment'].' SET
1431  file_name = ?,
1432  path = ?,
1433  lock_time = 0,
1434  lock_user_id = ?
1435  WHERE sc_attachment_id = ?');
1436  $stmt->bind_param("ssii",
1437  $attachment['filename'],
1438  $attachment['path'],
1439  $loggedInUser->userId,
1440  $attachment['id']);
1441  $result = $stmt->execute();
1442  if ($result == FALSE) {
1443  return FALSE;
1444  }
1445  $stmt->close();
1446  }
1447  return TRUE;
1448  }
1449 
1450  /**
1451  * Adds a new attachment
1452  * @param array $attachment details of the attachment including the sponsor car id
1453  * @return mixed
1454  * - id of the new attachment
1455  * - FALSE if an error occured
1456  * @author Daniel Seichter
1457  */
1458  function addSponsorCarAttachment($attachment) {
1459  global $mysqli, $db, $loggedInUser;
1460  $stmt = $mysqli->prepare("INSERT INTO " . $db['sc_attachment'] . "
1461  (sponsor_car_id, file_name, path, date, lock_time, lock_user_id)
1462  VALUES
1463  (?, ?, ?, UNIX_TIMESTAMP(), 0, ?)");
1464  $stmt->bind_param("issi", $attachment['sponsorCarId'],
1465  $attachment['filename'],
1466  $attachment['path'],
1467  $loggedInUser->userId);
1468  $result = $stmt->execute();
1469  $stmt->close();
1470  return ($result === TRUE) ? $mysqli->insert_id : FALSE;
1471  }
1472 
1473  /**
1474  * Deletes an attachment
1475  * @param integer $attachmentId id of the attachment
1476  * @return boolean FALSE if an error occured
1477  * @author Daniel Seichter
1478  */
1479  function deleteSponsorCarAttachment($attachmentId) {
1480  global $mysqli, $db, $loggedInUser;
1481  //update lock_user_id before delete
1482  $stmt = $mysqli->prepare('UPDATE '.$db['sc_attachment'].' SET
1483  lock_time = -1,
1484  lock_user_id = ?
1485  WHERE sc_attachment_id = ?');
1486  $stmt->bind_param("ii",
1487  $loggedInUser->userId,
1488  $attachmentId);
1489  $stmt->execute();
1490  $stmt->close();
1491 
1492  //delete
1493  $stmt = $mysqli->prepare("DELETE FROM " . $db['sc_attachment'] . "
1494  WHERE sc_attachment_id = ?");
1495  $stmt->bind_param("i", $attachmentId);
1496  $result = $stmt->execute();
1497  $stmt->close();
1498  return $result;
1499 
1500  }
1501 
1502 
1503  // dropdown fields:
1504 
1505  /**
1506  * Fetches all the details of the cars (dropdown fields)
1507  * @return mixed
1508  * - array containing the details of the cars
1509  * - FALSE if an error occured
1510  * @author Daniel Seichter
1511  * @author Joachim Wagner
1512  */
1513  function fetchAllCars(){
1514  global $mysqli, $db;
1515  $stmt = $mysqli->prepare("SELECT
1516  car_id,
1517  car_name
1518  FROM " . $db['car'].'
1519  ORDER BY car_id DESC');
1520  $stmt->execute();
1521  $stmt->bind_result($carId, $carName);
1522  while ($stmt->fetch()) {
1523  $rows[] = array('id' => $carId, 'name' => $carName);
1524  }
1525  $stmt->close();
1526  return isset($rows) ? $rows : FALSE;
1527  }
1528 
1529  /**
1530  * Fetches all the details of the production facilities (dropdown fields)
1531  * @return mixed
1532  * - array containing the details of the cars
1533  * - FALSE if an error occured
1534  * @author Daniel Seichter
1535  * @author Joachim Wagner
1536  */
1538  global $mysqli, $db;
1539  $stmt = $mysqli->prepare("SELECT
1540  production_facility_id,
1541  production_facility_name
1542  FROM " . $db['production_facility'].'
1543  ORDER BY production_facility_name ASC');
1544  $stmt->execute();
1545  $stmt->bind_result($productionFacilityId, $name);
1546 
1547  while ($stmt->fetch()) {
1548  $rows[] = array('id' => $productionFacilityId, 'name' => $name);
1549  }
1550  $stmt->close();
1551  return isset($rows) ? $rows : FALSE;
1552  }
1553 
1554  /**
1555  * Fetches all the details of the status (dropdown fields)
1556  * @return mixed
1557  * - array containing the details of the status
1558  * - FALSE if an error occured
1559  * @author Daniel Seichter
1560  * @author Joachim Wagner
1561  */
1562  function fetchAllStatus(){
1563  global $mysqli, $db;
1564  $stmt = $mysqli->prepare("SELECT
1565  status_id,
1566  status_name
1567  FROM " . $db['status'].'
1568  ORDER BY status_name ASC');
1569  $stmt->execute();
1570  $stmt->bind_result($statusId, $statusName);
1571 
1572  while ($stmt->fetch()) {
1573  $rows[] = array('id' => $statusId, 'name' => $statusName);
1574  }
1575  $stmt->close();
1576  return isset($rows) ? $rows : FALSE;
1577  }
1578 
1579  /**
1580  * Fetches all the details of the categories (dropdown fields)
1581  * @return mixed
1582  * - array containing the details of the categories
1583  * - FALSE if an error occured
1584  * @author Daniel Seichter
1585  * @author Joachim Wagner
1586  */
1588  global $mysqli, $db;
1589  $stmt = $mysqli->prepare("SELECT
1590  category_id,
1591  category_name
1592  FROM " . $db['category'].'
1593  ORDER BY category_name ASC');
1594  $stmt->execute();
1595  $stmt->bind_result($categoryId, $categoryName);
1596 
1597  while ($stmt->fetch()) {
1598  $rows[] = array('id' => $categoryId, 'name' => $categoryName);
1599  }
1600  $stmt->close();
1601  return isset($rows) ? $rows : FALSE;
1602  }
1603 
1604 
1605  /**
1606  * Fetches all the details of a production facility (dropdown fields)
1607  * @param integer $productionFacilityId id of the production facility
1608  * @return mixed
1609  * - array containing the details of the production facility
1610  * - FALSE if an error occured
1611  * @author Joachim Wagner
1612  */
1613  function fetchProductionFacilityDetails($productionFacilityId) {
1614  global $mysqli, $db;
1615  if (empty($productionFacilityId)) {
1616  /* error occured */
1617  return FALSE;
1618  }
1619  $stmt = $mysqli->prepare("SELECT
1620  production_facility_id,
1621  production_facility_name
1622  FROM " . $db['production_facility']."
1623  WHERE
1624  production_facility_id = ?");
1625  $stmt->bind_param("i", $productionFacilityId);
1626  $stmt->execute();
1627  $stmt->bind_result($productionFacilityId, $name);
1628  while ($stmt->fetch()) {
1629  $row = array('id' => $productionFacilityId, 'name' => $name);
1630  }
1631  $stmt->close();
1632  return isset($row) ? $row : FALSE;
1633  }
1634 
1635  /**
1636  * Fetches all the details of a status (dropdown fields)
1637  * @param integer $statusId id of the status
1638  * @return mixed
1639  * - array containing the details of the status
1640  * - FALSE if an error occured
1641  * @author Joachim Wagner
1642  */
1643  function fetchStatusDetails($statusId) {
1644  global $mysqli, $db;
1645  if (empty($statusId)) {
1646  /* error occured */
1647  return FALSE;
1648  }
1649  $stmt = $mysqli->prepare("SELECT
1650  status_id,
1651  status_name
1652  FROM " . $db['status']."
1653  WHERE
1654  status_id = ?");
1655  $stmt->bind_param("i", $statusId);
1656  $stmt->execute();
1657  $stmt->bind_result($statusId, $statusName);
1658  while ($stmt->fetch()) {
1659  $row = array('id' => $statusId, 'name' => $statusName);
1660  }
1661  $stmt->close();
1662  return isset($row) ? $row : FALSE;
1663  }
1664 
1665  /**
1666  * Fetches all the details of a category (dropdown fields)
1667  * @param integer $categoryId id of the category
1668  * @return mixed
1669  * - array containing the details of the category
1670  * - FALSE if an error occured
1671  * @author Joachim Wagner
1672  */
1673  function fetchCategoryDetails($categoryId) {
1674  global $mysqli, $db;
1675  if (empty($categoryId)) {
1676  /* error occured */
1677  return FALSE;
1678  }
1679  $stmt = $mysqli->prepare("SELECT
1680  category_id,
1681  category_name
1682  FROM " . $db['category']."
1683  WHERE
1684  category_id = ?");
1685  $stmt->bind_param("i", $categoryId);
1686  $stmt->execute();
1687  $stmt->bind_result($categoryId, $categoryName);
1688  while ($stmt->fetch()) {
1689  $row = array('id' => $categoryId, 'name' => $categoryName);
1690  }
1691  $stmt->close();
1692  return isset($row) ? $row : FALSE;
1693  }
1694 
1695  /**
1696  * Fetches all the details of a car (dropdown fields)
1697  * @param integer $carId id of the car
1698  * @return mixed
1699  * - array containing the details of the car
1700  * - FALSE if an error occured
1701  * @author Joachim Wagner
1702  */
1703  function fetchCarDetails($carId) {
1704  global $mysqli, $db;
1705  if (empty($carId)) {
1706  /* error occured */
1707  return FALSE;
1708  }
1709  $stmt = $mysqli->prepare("SELECT
1710  car_id,
1711  car_name
1712  FROM " . $db['car']."
1713  WHERE
1714  car_id = ?");
1715  $stmt->bind_param("i", $carId);
1716  $stmt->execute();
1717  $stmt->bind_result($carId, $carName);
1718  while ($stmt->fetch()) {
1719  $row = array('id' => $carId, 'name' => $carName);
1720  }
1721  $stmt->close();
1722  return isset($row) ? $row : FALSE;
1723  }
1724 
1725  /**
1726  * Adds a new production facility (dropdown fields)
1727  * @param string $name name of the production facility
1728  * @return boolean FALSE if an error occured
1729  * @author Joachim Wagner
1730  */
1731  function addProductionFacility($name) {
1732  global $mysqli, $db, $loggedInUser;
1733  $stmt = $mysqli->prepare("INSERT INTO " . $db['production_facility'] . "(
1734  production_facility_name, lock_time, lock_user_id)
1735  VALUES(
1736  ?, 0, ?
1737  )");
1738  $stmt->bind_param("si", $name, $loggedInUser->userId);
1739  $result = $stmt->execute();
1740  $stmt->close();
1741  return $result;
1742  }
1743 
1744  /**
1745  * Adds a new status (dropdown fields)
1746  * @param string $name name of the status
1747  * @return boolean FALSE if an error occured
1748  * @author Joachim Wagner
1749  */
1750  function addStatus($name) {
1751  global $mysqli, $db, $loggedInUser;
1752  $stmt = $mysqli->prepare("INSERT INTO " . $db['status'] . "(
1753  status_name, lock_time, lock_user_id)
1754  VALUES(
1755  ?, 0, ?
1756  )");
1757  $stmt->bind_param("si", $name, $loggedInUser->userId);
1758  $result = $stmt->execute();
1759  $stmt->close();
1760  return $result;
1761  }
1762 
1763  /**
1764  * Adds a new category (dropdown fields)
1765  * @param string $name name of the category
1766  * @return boolean FALSE if an error occured
1767  * @author Joachim Wagner
1768  */
1769  function addCategory($name) {
1770  global $mysqli, $db, $loggedInUser;
1771  $stmt = $mysqli->prepare("INSERT INTO " . $db['category'] . "(
1772  category_name, lock_time, lock_user_id)
1773  VALUES(
1774  ?, 0, ?
1775  )");
1776  $stmt->bind_param("si", $name, $loggedInUser->userId);
1777  $result = $stmt->execute();
1778  $stmt->close();
1779  return $result;
1780  }
1781 
1782  /**
1783  * Adds a new car (dropdown fields)
1784  * @param string $name name of the car
1785  * @return boolean FALSE if an error occured
1786  * @author Joachim Wagner
1787  */
1788  function addCar($name) {
1789  global $mysqli, $db, $loggedInUser;
1790  $stmt = $mysqli->prepare("INSERT INTO " . $db['car'] . "(
1791  car_name, lock_time, lock_user_id)
1792  VALUES(
1793  ?, 0, ?
1794  )");
1795  $stmt->bind_param("si", $name, $loggedInUser->userId);
1796  $result = $stmt->execute();
1797  $stmt->close();
1798  return $result;
1799  }
1800 
1801  /**
1802  * Updates a production facility (dropdown fields)
1803  * @param string $name new name of the production facility
1804  * @param integer $id id of the production facility to update
1805  * @return boolean FALSE if an error occured
1806  * @author Joachim Wagner
1807  */
1808  function updateProductionFacility($name, $id) {
1809  global $mysqli, $db, $loggedInUser;
1810  $stmt = $mysqli->prepare("UPDATE " . $db['production_facility'] . "
1811  SET production_facility_name = ?,
1812  lock_time = 0,
1813  lock_user_id = ?
1814  WHERE
1815  production_facility_id = ?");
1816  $stmt->bind_param("sii", $name, $loggedInUser->userId, $id);
1817  $result = $stmt->execute();
1818  $stmt->close();
1819  return $result;
1820  }
1821 
1822  /**
1823  * Updates a status (dropdown fields)
1824  * @param string $name new name of the status
1825  * @param integer $id id of the status to update
1826  * @return boolean FALSE if an error occured
1827  * @author Joachim Wagner
1828  */
1829  function updateStatus($name, $id) {
1830  global $mysqli, $db, $loggedInUser;
1831  $stmt = $mysqli->prepare("UPDATE " . $db['status'] . "
1832  SET status_name = ?,
1833  lock_time = 0,
1834  lock_user_id = ?
1835  WHERE
1836  status_id = ?");
1837  $stmt->bind_param("sii", $name, $loggedInUser->userId, $id);
1838  $result = $stmt->execute();
1839  $stmt->close();
1840  return $result;
1841  }
1842 
1843  /**
1844  * Updates a catagory (dropdown fields)
1845  * @param string $name new name of the catagory
1846  * @param integer $id id of the catagory to update
1847  * @return boolean FALSE if an error occured
1848  * @author Joachim Wagner
1849  */
1850  function updateCategory($name, $id) {
1851  global $mysqli, $db, $loggedInUser;
1852  $stmt = $mysqli->prepare("UPDATE " . $db['category'] . "
1853  SET category_name = ?,
1854  lock_time = 0,
1855  lock_user_id = ?
1856  WHERE
1857  category_id = ?");
1858  $stmt->bind_param("sii", $name, $loggedInUser->userId, $id);
1859  $result = $stmt->execute();
1860  $stmt->close();
1861  return $result;
1862  }
1863 
1864  /**
1865  * Updates a car (dropdown fields)
1866  * @param string $name new name of the car
1867  * @param integer $id id of the car to update
1868  * @return boolean FALSE if an error occured
1869  * @author Joachim Wagner
1870  */
1871  function updateCar($name, $id) {
1872  global $mysqli, $db, $loggedInUser;
1873  $stmt = $mysqli->prepare("UPDATE " . $db['car'] . "
1874  SET car_name = ?,
1875  lock_time = 0,
1876  lock_user_id = ?
1877  WHERE
1878  car_id = ?");
1879  $stmt->bind_param("sii", $name, $loggedInUser->userId, $id);
1880  $result = $stmt->execute();
1881  $stmt->close();
1882  return $result;
1883  }
1884 
1885  /**
1886  * Deletes a production facility (dropdown fields)
1887  * @param integer $id id of the production facility
1888  * @return boolean FALSE if it is still used or an error occured
1889  * @author Joachim Wagner
1890  * @author Daniel Seichter
1891  */
1893  global $mysqli, $db;
1894  $stmt = $mysqli->prepare("SELECT
1895  COUNT(sp_production_facility_id)
1896  FROM ".$db['sp_production_facility']."
1897  WHERE production_facility_id = ?
1898  ");
1899  $stmt->bind_param("i",$id);
1900  $stmt->execute();
1901  $stmt->bind_result($cnt);
1902  if ($stmt->fetch()) {
1903  if ($cnt == 0) {
1904  // not used
1905  $stmt->close();
1906  $stmt = $mysqli->prepare("DELETE FROM " . $db['production_facility'] . "
1907  WHERE production_facility_id = ?");
1908  $stmt->bind_param("i",$id);
1909  $result = $stmt->execute();
1910  $stmt->close();
1911  return $result;
1912  }
1913  }
1914  //return default
1915  return FALSE;
1916  }
1917 
1918  /**
1919  * Deletes a status (dropdown fields)
1920  * @param integer $id id of the status
1921  * @return boolean FALSE if it is still used or an error occured
1922  * @author Joachim Wagner
1923  * @author Daniel Seichter
1924  */
1925  function deleteStatus($id) {
1926  global $mysqli, $db;
1927  $stmt = $mysqli->prepare("SELECT
1928  COUNT(sponsor_id)
1929  FROM ".$db['sponsor']."
1930  WHERE status_id = ?
1931  ");
1932  $stmt->bind_param("i",$id);
1933  $stmt->execute();
1934  $stmt->bind_result($cnt);
1935  if ($stmt->fetch()) {
1936  if ($cnt == 0) {
1937  // not used
1938  $stmt->close();
1939  $stmt = $mysqli->prepare("DELETE FROM " . $db['status'] . "
1940  WHERE status_id = ?");
1941  $stmt->bind_param("i",$id);
1942  $result = $stmt->execute();
1943  $stmt->close();
1944  return $result;
1945  }
1946  }
1947  //return default
1948  return FALSE;
1949  }
1950 
1951  /**
1952  * Deletes a category (dropdown fields)
1953  * @param integer $id id of the category
1954  * @return boolean FALSE if it is still used or an error occured
1955  * @author Joachim Wagner
1956  * @author Daniel Seichter
1957  */
1958  function deleteCategory($id) {
1959  global $mysqli, $db;
1960  $stmt = $mysqli->prepare("SELECT
1961  COUNT(sponsor_car_id)
1962  FROM ".$db['sponsor_car']."
1963  WHERE category_id = ?
1964  ");
1965  $stmt->bind_param("i",$id);
1966  $stmt->execute();
1967  $stmt->bind_result($cnt);
1968  if ($stmt->fetch()) {
1969  if ($cnt == 0) {
1970  // not used
1971  $stmt->close();
1972  $stmt = $mysqli->prepare("DELETE FROM " . $db['category'] . "
1973  WHERE category_id = ?");
1974  $stmt->bind_param("i",$id);
1975  $result = $stmt->execute();
1976  $stmt->close();
1977  return $result;
1978  }
1979  }
1980  //return default
1981  return FALSE;
1982  }
1983 
1984  /**
1985  * Deletes a car (dropdown fields)
1986  * @param integer $id id of the car
1987  * @return boolean FALSE if it is still used or an error occured
1988  * @author Joachim Wagner
1989  * @author Daniel Seichter
1990  */
1991  function deleteCar($id) {
1992  global $mysqli, $db;
1993  $stmt = $mysqli->prepare("SELECT
1994  COUNT(sponsor_car_id)
1995  FROM ".$db['sponsor_car']."
1996  WHERE car_id = ?
1997  ");
1998  $stmt->bind_param("i",$id);
1999  $stmt->execute();
2000  $stmt->bind_result($cnt);
2001  if ($stmt->fetch()) {
2002  if ($cnt == 0) {
2003  // not used
2004  $stmt->close();
2005  $stmt = $mysqli->prepare("DELETE FROM " . $db['car'] . "
2006  WHERE car_id = ?");
2007  $stmt->bind_param("i",$id);
2008  $result = $stmt->execute();
2009  $stmt->close();
2010  return $result;
2011  }
2012  }
2013  //return default
2014  return FALSE;
2015  }
2016 ?>