More improved SQL queries
[mailer.git] / inc / libs / sponsor_functions.php
1 <?php
2 /************************************************************************
3  * MXChange v0.2.1                                    Start: 04/23/2005 *
4  * ===============                              Last change: 05/18/2008 *
5  *                                                                      *
6  * -------------------------------------------------------------------- *
7  * File              : sponsor_functions.php                            *
8  * -------------------------------------------------------------------- *
9  * Short description : Functions for the sponsor area                   *
10  * -------------------------------------------------------------------- *
11  * Kurzbeschreibung  : Funktionen fuer den Sponsorenbereich             *
12  * -------------------------------------------------------------------- *
13  *                                                                      *
14  * -------------------------------------------------------------------- *
15  * Copyright (c) 2003 - 2008 by Roland Haeder                           *
16  * For more information visit: http://www.mxchange.org                  *
17  *                                                                      *
18  * This program is free software. You can redistribute it and/or modify *
19  * it under the terms of the GNU General Public License as published by *
20  * the Free Software Foundation; either version 2 of the License.       *
21  *                                                                      *
22  * This program is distributed in the hope that it will be useful,      *
23  * but WITHOUT ANY WARRANTY; without even the implied warranty of       *
24  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the        *
25  * GNU General Public License for more details.                         *
26  *                                                                      *
27  * You should have received a copy of the GNU General Public License    *
28  * along with this program; if not, write to the Free Software          *
29  * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston,               *
30  * MA  02110-1301  USA                                                  *
31  ************************************************************************/
32
33 // Some security stuff...
34 if (!defined('__SECURITY')) {
35         $INC = substr(dirname(__FILE__), 0, strpos(dirname(__FILE__), "/inc") + 4)."/security.php";
36         require($INC);
37 }
38
39 //
40 function SPONSOR_HANDLE_SPONSOR (&$POST, $NO_UPDATE=false, $MSGs=array(), $RET_STATUS=false) {
41         // Init a lot variables
42         $SAVE = true;
43         $UPDATE = false;
44         $skip = false;
45         $ALREADY = false;
46         $ret = "unused";
47
48         // Skip these entries
49         $SKIPPED = array(
50                 'ok', 'edit', 'terms', 'pay_type'
51         );
52
53         // Save sponsor data
54         $DATA = array(
55                 'keys'   => array(),
56                 'values' => array()
57         );
58
59         // Check if sponsor already exists
60         foreach ($POST as $k => $v) {
61                 if (!(array_search($k, $SKIPPED) > -1)) {
62                         // Check only posted input entries not the submit button
63                         switch ($k)
64                         {
65                         case "email":
66                                 $ALREADY = false;
67                                 if (!VALIDATE_EMAIL($v)) {
68                                         // Email address is not valid
69                                         $SAVE = false;
70                                 } else {
71                                         // Do we want to add a new sponsor or update his data?
72                                         $result = SQL_QUERY_ESC("SELECT id FROM `{!_MYSQL_PREFIX!}_sponsor_data` WHERE email='%s' LIMIT 1",
73                                                 array($POST['email']), __FILE__, __LINE__);
74
75                                         // Is a sponsor alread in the db?
76                                         if (SQL_NUMROWS($result) == 1) {
77                                                 // Yes, he is!
78                                                 if (($GLOBALS['what'] == "add_sponsor") || ($NO_UPDATE)) {
79                                                         // Already found!
80                                                         $ALREADY = true;
81                                                 } else {
82                                                         // Update his data
83                                                         $UPDATE = true;
84                                                 }
85                                         }
86
87                                         // Free memory
88                                         SQL_FREERESULT($result);
89                                 }
90                                 break;
91
92                         case "pass1":
93                                 $k = ""; $v = "";
94                                 break;
95
96                         case "pass2":
97                                 $k = "password"; $v = md5($v);
98                                 break;
99
100                         case "url":
101                                 if (!VALIDATE_URL($v)) $SAVE = false;
102                                 break;
103
104                         default:
105                                 // Test if there is are time selections
106                                 CONVERT_SELECTIONS_TO_TIMESTAMP($POST, $DATA, $k, $skip);
107                                 break;
108                         }
109
110                         if ((!empty($k)) && ($skip == false)) {
111                                 // Add data
112                                 $DATA['keys'][] = $k; $DATA['values'][] = $v;
113                         }
114                 }
115         }
116
117         // Save sponsor?
118         if ($SAVE) {
119                 // Default is no force even when a guest want to abuse this force switch
120                 if ((empty($POST['force'])) || (!IS_ADMIN())) $POST['force'] = 0;
121
122                 // SQL and message string is empty by default
123                 $SQL = ""; $MSG = "";
124
125                 // Update?
126                 if ($UPDATE) {
127                         // Update his data
128                         $SQL = "UPDATE `{!_MYSQL_PREFIX!}_sponsor_data` SET ";
129                         foreach ($DATA['keys'] as $k => $v) {
130                                 $SQL .= $v."='%s', ";
131                         }
132
133                         // Remove last ", " from SQL string
134                         $SQL = substr($SQL, 0, -2)." WHERE id='%s' LIMIT 1";
135                         $DATA['values'][] = bigintval($_GET['id']);
136
137                         // Generate message
138                         $MSG = SPONSOR_GET_MESSAGE(ADMIN_SPONSOR_UPDATED, "updated", $MSGs);
139                         $ret = "updated";
140                 } elseif ((!$ALREADY) || (($POST['force'] == "1") && (IS_ADMIN()))) {
141                         // Add new sponsor, first add more data
142                         $DATA['keys'][] = "sponsor_created"; $DATA['values'][] = time();
143                         $DATA['keys'][] = "status";
144                         if ((!$NO_UPDATE) && (IS_ADMIN()) && ($GLOBALS['what'] == "add_sponsor")) {
145                                 // Only allowed for admin
146                                 $DATA['values'][] = "PENDING";
147                         } elsen{
148                                 // Guest area
149                                 $DATA['values'][] = "UNCONFIRMED";
150
151                                 // Generate hash code
152                                 $DATA['keys'][] = "hash";
153                                 $DATA['values'][] = md5(session_id().":".$POST['email'].":".GET_REMOTE_ADDR().":".GET_USER_AGENT().":".time());
154                                 $DATA['keys'][] = "remote_addr";
155                                 $DATA['values'][] = GET_REMOTE_ADDR();
156                         }
157
158                         // Implode all data into strings
159                         $KEYS   = implode(", "  , $DATA['keys']);
160                         $VALUES = str_repeat("%s', '", count($DATA['values']) - 1);
161
162                         // Generate string
163                         $SQL = "INSERT INTO `{!_MYSQL_PREFIX!}_sponsor_data` (".$KEYS.") VALUES ('".$VALUES."%s')";
164
165                         // Generate message
166                         $MSG = SPONSOR_GET_MESSAGE(ADMIN_SPONSOR_ADDED, "added", $MSGs);
167                         $ret = "added";
168                 } elseif ((!$NO_UPDATE) && (IS_ADMIN())) {
169                         // Add all data as hidden data
170                         $OUT = "";
171                         foreach ($POST as $k => $v) {
172                                 // Do not add 'force' !
173                                 if ($k != "force") {
174                                         $OUT .= "<INPUT type=\"hidden\" name=\"".$k."\" value=\"".stripslashes($v)."\">\n";
175                                 }
176                         }
177                         define('__HIDDEN_DATA', $OUT);
178                         define('__EMAIL'      , $POST['email']);
179
180                         // Ask for adding a sponsor with same email address
181                         LOAD_TEMPLATE("admin_add_sponsor_already");
182                         return;
183                 } else {
184                         // Already added!
185                         $MSG = SPONSOR_ALREADY_FOUND_1.$POST['email'].SPONSOR_ALREADY_FOUND_2;
186                         $ret = "already";
187                 }
188
189                 if (!empty($SQL)) {
190                         // Run SQL command
191                         $result = SQL_QUERY_ESC($SQL, $DATA['values'], __FILE__, __LINE__);
192                 }
193
194                 // Output message
195                 if ((!$NO_UPDATE) && (IS_ADMIN())) {
196                         LOAD_TEMPLATE("admin_settings_saved", false, $MSG);
197                 }
198         } else {
199                 // Error found!
200                 $MSG = SPONSOR_GET_MESSAGE(SPONSOR_DATA_NOT_SAVED, "failed", $MSGs);
201                 LOAD_TEMPLATE("admin_settings_saved", false, $MSG);
202         }
203
204         // Shall we return the status?
205         if ($RET_STATUS) return $ret;
206 }
207 //
208 function SPONSOR_TRANSLATE_STATUS($status) {
209         switch ($status)
210         {
211         case "UNCONFIRMED":
212                 $ret = ACCOUNT_UNCONFIRMED;
213                 break;
214
215         case "CONFIRMED":
216                 $ret = ACCOUNT_CONFIRMED;
217                 break;
218
219         case "LOCKED":
220                 $ret = ACCOUNT_LOCKED;
221                 break;
222
223         case "PENDING":
224                 $ret = ACCOUNT_PENDING;
225                 break;
226
227         case "EMAIL":
228                 $ret = ACCOUNT_EMAIL;
229                 break;
230
231         default:
232                 DEBUG_LOG(__FUNCTION__, __LINE__, sprintf("Unknown status %s detected.", $status));
233                 $ret = UNKNOWN_STATUS_1.$status.UNKNOWN_STATUS_2;
234                 break;
235         }
236         return $ret;
237 }
238 // Search for an email address in the database
239 function SPONSOR_FOUND_EMAIL_DB ($email) {
240         // Do we already have the provided email address in our DB?
241         $ret = (GET_TOTAL_DATA($email, "sponsor_data", "id", "email", true) == 1);
242
243         // Return result
244         return $ret;
245 }
246 //
247 function SPONSOR_GET_MESSAGE ($msg, $pos, $array) {
248         // Check if the requested message was found in array
249         if (isset($array[$pos])) {
250                 // ... if yes then use it!
251                 $ret = $array[$pos];
252         } else {
253                 // ... else use default message
254                 $ret = $msg;
255         }
256
257         // Return result
258         return $ret;
259 }
260
261 //
262 function IS_SPONSOR () {
263         // Failed...
264         $ret = false;
265         if ((isSessionVariableSet('sponsorid'))) && (isSessionVariableSet('sponsorpass')))) {
266                 // Check cookies against database records...
267                 $result = SQL_QUERY_ESC("SELECT id FROM `{!_MYSQL_PREFIX!}_sponsor_data`
268 WHERE id='%s' AND password='%s' AND `status`='CONFIRMED' LIMIT 1",
269                         array(bigintval(get_session('sponsorid')), get_session('sponsorpass')), __FILE__, __LINE__);
270                 if (SQL_NUMROWS($result) == 1) {
271                         // All is fine
272                         $ret = true;
273                 }
274
275                 // Free memory
276                 SQL_FREERESULT($result);
277         }
278
279         // Return status
280         return $ret;
281 }
282 //
283 function GENERATE_SPONSOR_MENU($current)
284 {
285         $OUT = "";
286         $WHERE = " AND active='Y'";
287         if (IS_ADMIN()) $WHERE = "";
288
289         // Load main menu entries
290         $result_main = SQL_QUERY("SELECT action, title FROM `{!_MYSQL_PREFIX!}_sponsor_menu`
291 WHERE (what='' OR `what` IS NULL) ".$WHERE."
292 ORDER BY `sort`", __FILE__, __LINE__);
293         if (SQL_NUMROWS($result_main) > 0)
294         {
295                 // Load every menu and it's sub menus
296                 while (list($action, $title_main) = SQL_FETCHROW($result_main))
297                 {
298                         // Load sub menus
299                         $result_sub = SQL_QUERY_ESC("SELECT what, title FROM `{!_MYSQL_PREFIX!}_sponsor_menu`
300 WHERE action='%s' AND `what` != '' AND `what` IS NOT NULL ".$WHERE."
301 ORDER BY `sort`", array($action), __FILE__, __LINE__);
302                         if (SQL_NUMROWS($result_sub) > 0)
303                         {
304                                 // Load sub menus
305                                 $SUB = "";
306                                 while (list($what, $title_sub) = SQL_FETCHROW($result_sub))
307                                 {
308                                         // Check if current selected menu is matching the loaded one
309                                         if ($current == $what) $title_sub = "<strong>".$title_sub."</strong>";
310
311                                         // Prepare data for the sub template
312                                         $content = array(
313                                                 'what'  => $what,
314                                                 'title' => $title_sub
315                                         );
316
317                                         // Load row template
318                                         $SUB .= LOAD_TEMPLATE("sponsor_what", true, $content);
319                                 }
320
321                                 // Prepare data for the main template
322                                 $content = array(
323                                         'title' => $title_main,
324                                         'menu'  => $SUB
325                                 );
326
327                                 // Load menu template
328                                 $OUT .= LOAD_TEMPLATE("sponsor_action", true, $content);
329                         }
330                          else
331                         {
332                                 // No sub menus active
333                                 $OUT .= LOAD_TEMPLATE("admin_settings_saved", true, SPONSOR_NO_SUB_MENUS_ACTIVE);
334                         }
335
336                         // Free memory
337                         SQL_FREERESULT($result_sub);
338                 }
339         }
340          else
341         {
342                 // No main menus active
343                 $OUT .= LOAD_TEMPLATE("admin_settings_saved", true, SPONSOR_NO_MAIN_MENUS_ACTIVE);
344         }
345
346         // Free memory
347         SQL_FREERESULT($result_main);
348
349         // Return content
350         return $OUT;
351 }
352 //
353 function GENERATE_SPONSOR_CONTENT($what)
354 {
355         global $_CONFIG;
356         $OUT = "";
357         $INC = sprintf("inc/modules/sponsor/%s.php", $what);
358         if (FILE_READABLE($INC)) {
359                 // Every sponsor action will output nothing directly. It will be written into $OUT!
360                 LOAD_INC_ONCE($INC);
361         } else {
362                 // File not found!
363                 $OUT .= LOAD_TEMPLATE("admin_settings_saved", true, SPONSOR_CONTENT_404_1.$what.SPONSOR_CONTENT_404_2);
364         }
365
366         // Return content
367         return $OUT;
368 }
369 //
370 function UPDATE_SPONSOR_LOGIN () {
371         // Failed by default
372         $login = false;
373
374         // Is sponsor?
375         if (IS_SPONSOR()) {
376                 // Update last online timestamp
377                 SQL_QUERY_ESC("UPDATE `{!_MYSQL_PREFIX!}_sponsor_data`
378 SET last_online=UNIX_TIMESTAMP()
379 WHERE id='%s' AND password='%s' LIMIT 1",
380                         array(bigintval(get_session('sponsorid')), get_session('sponsorpass')), __FILE__, __LINE__);
381
382                 // This update went fine?
383                 $login = (SQL_AFFECTEDROWS() == 1);
384         }
385
386         // Return status
387         return $login;
388 }
389 //
390 function SPONSOR_SAVE_DATA ($POST, $content) {
391         $EMAIL = false;
392
393         // Unsecure data which we don't want
394         $UNSAFE = array('password', 'id', 'remote_addr', 'sponsor_created', 'last_online', 'status', 'ref_count',
395                         'points_amount', 'points_used', 'refid', 'hash', 'last_pay', 'last_curr', 'pass_old',
396                         'ok', 'pass1', 'pass2');
397
398         // Set default message ("not saved")
399         $MSG = SPONSOR_ACCOUNT_DATA_NOT_SAVED;
400
401         // Check for submitted passwords
402         if ((!empty($POST['pass1'])) && (!empty($POST['pass2']))) {
403                 // Are both passwords the same?
404                 if ($POST['pass1'] == $POST['pass2']) {
405                         // Okay, then set password and remove pass1 and pass2
406                         $POST['password'] = md5($POST['pass1']);
407                 }
408         }
409
410         // Remove all (maybe spoofed) unsafe data from array
411         foreach ($UNSAFE as $remove) {
412                 unset($POST[$remove]);
413         }
414
415         // This array is for the submitted data which we will use with the SQL_QUERY_ESC() function to
416         // secure the data
417         $DATA = array();
418
419         // Prepare SQL string
420         $SQL = "UPDATE `{!_MYSQL_PREFIX!}_sponsor_data` SET";
421         foreach ($POST as $key => $value) {
422                 // Mmmmm, too less security here???
423                 $SQL   .= " ".strip_tags($key)."='%s',";
424
425                 // We will secure this later inside the SQL_QUERY_ESC() function
426                 $DATA[] = strip_tags($value);
427
428                 // Compile {SLASH} and so on for the email templates
429                 $POST[$key] = COMPILE_CODE($value);
430         }
431
432         // Check if email has changed
433         if ((!empty($content['email'])) && (!empty($POST['email']))) {
434                 if ($content['email'] != $POST['email']) {
435                         // Change email address
436                         $EMAIL = true;
437
438                         // Okay, has changed then add status with UNCONFIRMED and new hash code
439                         $SQL .= " `status`='EMAIL', hash='%s',";
440
441                         // Generate hash code
442                         $HASH = md5(session_id().":".$POST['email'].":".GET_REMOTE_ADDR().":".GET_USER_AGENT().":".time());
443                         $DATA[] = $HASH;
444                 }
445         }
446
447         // Remove last commata
448         $SQL = substr($SQL, 0, -1);
449
450         // Add SQL tail data
451         $SQL .= " WHERE id='%s' AND password='%s' LIMIT 1";
452         $DATA[] = bigintval(get_session('sponsorid'));
453         $DATA[] = get_session('sponsorpass');
454
455         // Saving data was completed... ufff...
456         switch ($GLOBALS['what'])
457         {
458         case "account": // Change account data
459                 if ($EMAIL) {
460                         $MSG   = SPONSOR_ACCOUNT_EMAIL_CHANGED;
461                         $templ = "admin_sponsor_change_email";
462                         $subj  = ADMIN_SPONSOR_ACC_EMAIL_SUBJ;
463                 }
464                  else
465                 {
466                         $MSG   = SPONSOR_ACCOUNT_DATA_SAVED;
467                         $templ = "admin_sponsor_change_data";
468                         $subj  = ADMIN_SPONSOR_ACC_DATA_SUBJ;
469                 }
470                 break;
471
472         case "settings": // Change settings
473                 // Translate some data
474                 $content['receive']  = TRANSLATE_YESNO($content['receive_warnings']);
475                 $content['interval'] = CREATE_FANCY_TIME($content['warning_interval']);
476
477                 // Set message template and subject for admin
478                 $MSG   = SPONSOR_SETTINGS_SAVED;
479                 $templ = "admin_sponsor_settings";
480                 $subj  = ADMIN_SPONSOR_SETTINGS_SUBJ;
481                 break;
482
483         default: // Unknown sponsor what value!
484                 DEBUG_LOG(__FUNCTION__, __LINE__, sprintf("Unknown sponsor module (what) %s detected.", $GLOBALS['what']));
485                 $MSG = SPONSOR_UNKNOWN_WHAT_1.$GLOBALS['what'].SPONSOR_UNKNOWN_WHAT_2;
486                 $templ = ""; $subj = "";
487                 break;
488         }
489
490         if (SQL_AFFECTEDROWS() == 1)
491         {
492                 if (!empty($templ) && !empty($subj))
493                 {
494                         // Run SQL command and check for success
495                         $result = SQL_QUERY_ESC($SQL, $DATA, __FILE__, __LINE__);
496
497                         // Add all data to content
498                         global $DATA;
499                         $DATA = $POST;
500
501                         // Change some data
502                         if (isset($content['gender'])) $content['gender'] = TRANSLATE_GENDER($content['gender']);
503                         if (isset($DATA['gender']))    $DATA['gender']    = TRANSLATE_GENDER($DATA['gender']);
504                         if (isset($content['receive_warnings'])) $DATA['receive']     = TRANSLATE_YESNO($POST['receive_warnings']);
505                         if (isset($content['warning_interval'])) $DATA['interval']    = CREATE_FANCY_TIME($POST['warning_interval']);
506
507                         // Send email to admins
508                         SEND_ADMIN_NOTIFICATION($subj, $templ, $content);
509
510                         // Shall we send mail to the sponsor's new email address?
511                         if ($content['receive_warnings'] == "Y")
512                         {
513                                 // Okay send email with confirmation link to new address and with no confirmation link
514                                 // to the old address
515
516                                 // First to old address
517                                 switch ($GLOBALS['what'])
518                                 {
519                                 case "account": // Change account data
520                                         $email_msg = LOAD_EMAIL_TEMPLATE("sponsor_change_data", $content);
521                                         SEND_EMAIL($content['email'], SPONSOR_ACC_DATA_SUBJ, $email_msg);
522
523                                         if ($EMAIL)
524                                         {
525                                                 // Add hash code to content array
526                                                 $content['hash'] = $HASH;
527
528                                                 // Second mail goes to the new address
529                                                 $email_msg = LOAD_EMAIL_TEMPLATE("sponsor_change_email", $content);
530                                                 SEND_EMAIL($content['email'], SPONSOR_ACC_EMAIL_SUBJ, $email_msg);
531                                         }
532                                         break;
533
534                                 case "settings": // Change settings
535                                         // Send email
536                                         $email_msg = LOAD_EMAIL_TEMPLATE("sponsor_settings", $content);
537                                         SEND_EMAIL($content['email'], SPONSOR_SETTINGS_SUBJ, $email_msg);
538                                         break;
539                                 }
540                         }
541                 }
542         }
543
544         // Return final message
545         return $MSG;
546 }
547 //
548 ?>