Sponsorenverwaltung - Team StarCraft e.V.
 All Data Structures Files Functions Variables
functions_export.php
Go to the documentation of this file.
1 <?php
2  /**
3  * @file functions_export.php
4  *
5  * @brief Functions used for export
6  *
7  * @details
8  * Provides a wide range of functions to fetch specific information from the
9  * database as well as functions for forming the output as .csv-file.
10  *
11  * @copyright (c) 2013, Team StarCraft e.V.
12  * @version 1.0.0
13  * @author Niklas Engelhardt
14  * @author Clemens Heinrich
15  * @author Alexander Vorndran
16  * @date 02.07.2013
17  */
18 
19  /**
20  * Writes the content of the array to a .csv-file in php://output
21  * @param array $array an array to write down to the .csv-file
22  * @param boolean $headline [optional]
23  * - TRUE if a headline should be created out of the keys of the given array
24  * - FALSE if there should be no headline
25  * - Default: TRUE
26  * @param string $delimiter [optional] the delimiter that should be used for the .csv-file
27  * - Default: ","
28  * @param boolean $windowsLinebreak [optional]
29  * - TRUE if an additional Windows-linebreak should be added
30  * - FALSE if not
31  * - Default: TRUE
32  * @return boolean FALSE if the array was empty
33  * @author Niklas Engelhardt
34  * @author Clemens Heinrich
35  * @author Alexander Vorndran
36  */
37  function writeCsvFile(&$array,$headline = TRUE, $delimiter = "," ,$windowsLinebreak = TRUE) {
38  if(isUserLoggedIn()) {
39  if (count($array) == 0) {
40  return FALSE;
41  }
42  $df = fopen("php://output", 'w');
43  if($headline) {
44  //creates headline
45  $keys = array_keys(reset($array));
46  $keys = array_map(function($in) {return iconv('utf-8','iso-8859-1',$in);},$keys);
47  fputcsv($df, $keys,$delimiter);
48  if($windowsLinebreak) {
49  fwrite($df,"\r\n");
50  }
51  }
52  foreach ($array as $row) {
53  $toWrite = array_map(
54  function($in) {
55  // converts string encoded in UTF-8 to ISO-8859-1 and special HTML entities back to characters
56  return iconv('utf-8','iso-8859-1',
57  htmlspecialchars_decode($in,ENT_QUOTES));},$row);
58  fputcsv($df, $toWrite, $delimiter);
59  if(count($toWrite)==1) {
60  fwrite($df, "; ");
61  } else {
62  if($windowsLinebreak) {
63  fwrite($df,"\r\n");
64  }
65  }
66  }
67  fclose($df);
68  }
69  }
70 
71  /**
72  * Fetches mail addresses that are used for newsletter from the database and writes them into an array
73  * @param array $sponsorIds [optional]
74  * - NULL: fetches all mail addresses that are used for newsletter from the db
75  * - Else: fetches mail addresses that are used for newsletter for the contact persons with the given ids
76  * - Default: NULL
77  * @return array $mailAddresses an array containing the information
78  * @author Niklas Engelhardt
79  * @author Clemens Heinrich
80  * @author Alexander Vorndran
81  */
82  function fetchMailAdressesNewsletter($sponsorIds = NULL) {
83  if($sponsorIds===NULL) {
84  global $mysqli, $db;
85  $stmt = $mysqli->prepare("SELECT email
86  FROM ". $db['contact_person']. " WHERE newsletter=1" );
87  $stmt->execute();
88 
89  $stmt->bind_result($email);
90 
91  while ($stmt->fetch()) {
92 
93  $row[] = array(($email));
94  }
95  $stmt->close();
96  return ($row);
97  } else {
98  $sponsors = $sponsorIds;
99  if(!$sponsors===FALSE) {
100  $mailAddresses = array();
101  foreach($sponsors as $sponsorId) {
102  $contactPersons = fetchSponsorContactPersons($sponsorId);
103  foreach ($contactPersons as $contactPerson) {
104  if($contactPerson['newsletter']==1) {
105  $mailAddresses[] = array(($contactPerson['email']));
106  }
107  }
108  }
109  return $mailAddresses;
110  } else {
111  exitWithErrorTemplate(array("Es ist ein Fehler aufgetreten. Zugriff auf das Ergebnis nicht möglich."));
112  }
113  }
114  }
115 
116  /**
117  * Fetches mail addresses from the database and writes them into an array
118  * @param array $sponsorIds [optional]
119  * - NULL: fetches all mail addresses from the db
120  * - Else: fetches mail addresses for the contact persons with the given ids
121  * - Default: NULL
122  * @return array $mailAddresses an array containing the information
123  * @author Niklas Engelhardt
124  * @author Clemens Heinrich
125  * @author Alexander Vorndran
126  */
127  function fetchMailAdresses($sponsorIds=NULL) {
128  if($sponsorIds===NULL) {
129  global $mysqli, $db;
130  $stmt = $mysqli->prepare("SELECT email
131  FROM " . $db['contact_person']);
132  $stmt->execute();
133  $stmt->bind_result($email);
134 
135  while ($stmt->fetch()) {
136  if (empty($email)) {
137  continue;
138  }
139 
140  $row[] = array(($email));
141  }
142  $stmt->close();
143  return ($row);
144  } else {
145  $sponsors = $sponsorIds;
146  if(!$sponsors===FALSE) {
147  $mailAddresses = array();
148  foreach($sponsors as $sponsorId) {
149  $contactPersons = fetchSponsorContactPersons($sponsorId);
150  foreach ($contactPersons as $contactPerson) {
151  $mailAddresses[] = array(($contactPerson['email']));
152  }
153  }
154  return $mailAddresses;
155  } else {
156  exitWithErrorTemplate(array("Es ist ein Fehler aufgetreten. Zugriff auf das Ergebnis nicht möglich."));
157  }
158  }
159  }
160 
161  /**
162  * Fetches contact information of contact persons from the database to be used for newsletter and writes them into an array
163  * @param array $sponsorIdList [optional]
164  * - NULL: fetches contact persons that are used for newsletter and their contact information from the db
165  * - Else: fetches the contact information for the contact persons with the given ids
166  * - Default: NULL
167  * @return array $mailAddressesNewsletter an array containing the information
168  * @author Niklas Engelhardt
169  * @author Clemens Heinrich
170  * @author Alexander Vorndran
171  */
172  function fetchSponsorMailNewsletter($sponsorIdList = NULL) {
173  if ($sponsorIdList == NULL) {
174  global $mysqli, $db;
175  $stmt = $mysqli->prepare(
176  "SELECT name, sponsor_name, position, email, telephone_number, mobile_phone_number
177  FROM " . $db['sponsor'] . "," . $db['contact_person'] . "
178  WHERE " . $db['sponsor'] . ".sponsor_id = " . $db['contact_person'] . ".sponsor_id" . " AND newsletter = 1");
179  $stmt->execute();
180  $stmt->bind_result($name, $sponsor_name, $position, $email, $telephone_number, $mobile_phone_number);
181 
182  while ($stmt->fetch()) {
183  $rows[] = array(
184 
185  "Anzeigename" => $name,
186  "Organisation" => $sponsor_name,
187  "Position" => $position,
188  "E-Mail" => $email,
189  "Telefon" => $telephone_number, "Mobil" => $mobile_phone_number);
190  }
191  $stmt->close();
192  return ($rows);
193  } else {
194  $sponsors = $sponsorIdList;
195  if(!$sponsors===FALSE) {
196  $mailAddressesNewsletter = array();
197 
198  foreach($sponsors as $sponsorId) {
199  $contactPersons = fetchSponsorContactPersons($sponsorId);
200  $sponsor = fetchSponsorDetails($sponsorId);
201 
202  foreach ($contactPersons as $contactPerson) {
203  if($contactPerson['newsletter']==1) {
204  $mailAddressesNewsletter[] = array(
205  "Anzeigename"=>$contactPerson['name'],
206  "Organisation"=>$sponsor['name'],
207  "Position"=>$contactPerson['position'],
208  "E-Mail"=>$contactPerson['email'],
209  "Telefon"=>$contactPerson['phone'], "Mobil"=>$contactPerson['mobile']
210  );
211  }
212  }
213  }
214  return $mailAddressesNewsletter;
215  } else {
216  exitWithErrorTemplate(array("Es ist ein Fehler aufgetreten. Zugriff auf das Ergebnis nicht möglich."));
217  }
218  }
219  }
220 
221  /**
222  * Fetches contact information of contact persons from the database and writes them into an array
223  * @param array $sponsorIds [optional]
224  * - NULL: fetches all contact persons and their contact information from the db
225  * - Else: fetches the contact information for the contact persons with the given ids
226  * - Default: NULL
227  * @return array $mailAddresses an array containing the information
228  * @author Niklas Engelhardt
229  * @author Clemens Heinrich
230  * @author Alexander Vorndran
231  */
232  function fetchContactPersonsWithMailAdresses($sponsorIds=NULL) {
233  if($sponsorIds===NULL) {
234  global $mysqli, $db;
235  $stmt = $mysqli->prepare(
236  "SELECT name, sponsor_name, position, email, telephone_number, mobile_phone_number
237  FROM " . $db['sponsor'] . "," . $db['contact_person'] . "
238  WHERE " . $db['sponsor'] . ".sponsor_id = " . $db['contact_person'] . ".sponsor_id");
239  $stmt->execute();
240  $stmt->bind_result($name, $sponsor_name, $position, $email, $telephone_number, $mobile_phone_number);
241 
242  while ($stmt->fetch()) {
243  $row[] = array(
244 
245  "Anzeigename" => $name,
246  "Organisation" => $sponsor_name,
247  "Position" => $position,
248  "E-Mail" => $email,
249  "Telefon" => $telephone_number, "Mobil" => $mobile_phone_number);
250  }
251  $stmt->close();
252  return ($row);
253  } else {
254  $sponsors = $sponsorIds;
255  if(!$sponsors===FALSE) {
256  $mailAddresses = array();
257  foreach($sponsors as $sponsorId) {
258  $contactPersons = fetchSponsorContactPersons($sponsorId);
259  $sponsor = fetchSponsorDetails($sponsorId);
260  foreach ($contactPersons as $contactPerson) {
261  $mailAddresses[] = array(
262  "Anzeigename"=>$contactPerson['name'],
263  "Organisation"=>$sponsor['name'],
264  "Position"=>$contactPerson['position'],
265  "E-Mail"=>$contactPerson['email'],
266  "Telefon"=>$contactPerson['phone'], "Mobil"=>$contactPerson['mobile']
267  );
268  }
269  }
270  return $mailAddresses;
271  } else {
272  exitWithErrorTemplate(array("Es ist ein Fehler aufgetreten. Zugriff auf das Ergebnis nicht möglich."));
273  }
274  }
275  }
276 
277  /**
278  * Fetches addresses of sponsors from the database and writes them into an array
279  * @details Function is used in export_addresses
280  * @param array $sponsorIds [optional]
281  * - NULL: fetches all sponsors and their addressdata from the db
282  * - Else: fetches the addressdata for the sponsors with the given ids
283  * - Default: NULL
284  * @return array $addresses an array containing the information
285  * @author Niklas Engelhardt
286  * @author Clemens Heinrich
287  * @author Alexander Vorndran
288  */
289  function fetchSponsorAddresses($sponsorIds=NULL) {
290  if($sponsorIds===NULL) {
291  global $mysqli, $db;
292  $stmt = $mysqli->prepare("SELECT name, sponsor_name, street, house_number, zip_code, town, country
293  FROM ". $db['sponsor'].",". $db['contact_person']." WHERE ". $db['sponsor'].".sponsor_id = ".
294  $db['contact_person'].".sponsor_id");
295  $stmt->execute();
296 
297  $stmt->bind_result($contact_person_name, $sponsor_name, $street, $house_number, $zip_code, $town, $country);
298 
299  while ($stmt->fetch()) {
300  $row[] = array(
301 
302  "Sponsorname"=>$sponsor_name,
303  "Name"=>$contact_person_name,
304  "Straße"=>$street,
305  "Hausnummer"=>$house_number,
306  "PLZ"=> $zip_code,
307  "Stadt"=>$town,
308  "Land"=> $country);
309  }
310  $stmt->close();
311  return ($row);
312  } else {
313  $addresses = array();
314  $sponsors = $sponsorIds;
315  if(!$sponsors===FALSE) {
316  foreach($sponsors as $sponsorId) {
317  $sponsor = fetchSponsorDetails($sponsorId);
318  $addresses[] = array(
319  "Sponsorname"=>$sponsor['name'],
320  "Straße"=>$sponsor['street'],
321  "Hausnummer"=>$sponsor['houseNumber'],
322  "PLZ"=>$sponsor['zipCode'],
323  "Stadt"=>$sponsor['town'],
324  "Land"=>$sponsor['country']);
325  }
326  return $addresses;
327  } else {
328  exitWithErrorTemplate(array("Es ist ein Fehler aufgetreten. Zugriff auf das Ergebnis nicht möglich."));
329  }
330  }
331  }
332 
333  /**
334  * Provides some header information for a downloadable .csv-file
335  * @param string $filename the desired filename
336  * @author Niklas Engelhardt
337  * @author Clemens Heinrich
338  */
340  header("Pragma: private");
341  header("Expires: 0");
342  header("Cache-Control: must-revalidate, post-check=0, pre- check=0");
343  header("Content-Type: application/force-download");
344  header("Content-Type: application/octet-stream");
345  header("Content-Type: application/download");
346  header("Content-Disposition: attachment;filename=$filename");
347  header("Content-Transfer-Encoding: binary");
348  }
349 ?>