From e70440e1164db8bbeeccd90df305bfa0efb641e1 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Roland=20H=C3=A4der?= Date: Tue, 22 Jan 2013 22:12:18 +0000 Subject: [PATCH] Rewrote a lot parts to extract 'payments' table (+ handling) into own extension: - Tried to avoid JOINs where possible to instead load data with getters from cache - Renamed many array elements for payment data to match with database columns - Used more EL code for payment data - TODOs.txt updated --- DOCS/TODOs.txt | 19 ++--- inc/libs/rallye_functions.php | 6 +- inc/loader/load- | 3 - inc/loader/load-imprint.php | 3 - inc/loader/load-payments.php | 3 - inc/modules/admin/what-del_email.php | 11 +-- inc/modules/admin/what-list_unconfirmed.php | 6 +- inc/modules/admin/what-payments.php | 77 +++++++++++++++--- inc/modules/admin/what-unlock_emails.php | 20 +++-- inc/modules/member/what-order.php | 10 +-- inc/modules/member/what-unconfirmed.php | 78 +++++++++---------- inc/modules/order.php | 2 +- inc/mysql-manager.php | 6 ++ inc/pool/pool-user.php | 2 +- inc/purge/purge-general.php | 20 ++--- .../member_list_unconfirmed_nopoints_row.tpl | 4 +- .../member/member_list_unconfirmed_row.tpl | 6 +- .../html/member/member_mail_bonus_deleted.tpl | 2 +- .../de/html/member/member_mail_bonus_new.tpl | 2 +- .../html/member/member_mail_bonus_queue.tpl | 2 +- .../de/html/member/member_mail_bonus_send.tpl | 2 +- .../html/member/member_mail_normal_active.tpl | 2 +- .../html/member/member_mail_normal_admin.tpl | 2 +- .../member/member_mail_normal_deleted.tpl | 2 +- .../de/html/member/member_mail_normal_new.tpl | 2 +- .../html/member/member_mail_normal_send.tpl | 2 +- .../html/member/member_mail_normal_temp.tpl | 2 +- .../de/html/member/member_order_page2.tpl | 2 +- 28 files changed, 175 insertions(+), 123 deletions(-) diff --git a/DOCS/TODOs.txt b/DOCS/TODOs.txt index 821c7cc5fa..2d24e2bd77 100644 --- a/DOCS/TODOs.txt +++ b/DOCS/TODOs.txt @@ -144,7 +144,7 @@ ./inc/modules/admin/what-bonus.php:46:// @TODO Unused at the moment ./inc/modules/admin/what-config_points.php:90: // @TODO Rewrite these two lines for new user_points columns (e.g. locked_order_points) with a filter ./inc/modules/admin/what-config_register.php:79: // @TODO Move this HTML code into a template -./inc/modules/admin/what-del_email.php:61: // @TODO Unused: cat_id, payment_id +./inc/modules/admin/what-del_email.php:57: // @TODO Unused: cat_id ./inc/modules/admin/what-edit_user.php:18: * @TODO Add support for ext-country * ./inc/modules/admin/what-email_stats.php:46:// @TODO Unused at the moment ./inc/modules/admin/what-extensions.php:366: // @TODO Rewrite this to a filter @@ -173,7 +173,7 @@ ./inc/modules/admin/what-mem_add.php:124: // @TODO This can be somehow rewritten to a function ./inc/modules/admin/what-mem_add.php:63: // @TODO Cant this be rewritten? ./inc/modules/admin/what-mem_add.php:79: // @TODO This can be somehow rewritten to a function -./inc/modules/admin/what-unlock_emails.php:95: // @TODO Rewrite these if-blocks to a filter +./inc/modules/admin/what-unlock_emails.php:93: // @TODO Rewrite these if-blocks to a filter ./inc/modules/admin/what-usage.php:89: // @TODO This code is double, see loadTemplate() and loadEmailTemplate() in functions.php ./inc/modules/admin/what-usr_online.php:49: // @TODO Add a filter for sponsor ./inc/modules/frametester.php:95: // @TODO Rewrite this somehow @@ -196,15 +196,16 @@ ./inc/modules/member/what-rallyes.php:99: // @TODO Reactivate this: $content['admin'] = '' . $content['login'] . ''; ./inc/modules/member/what-reflinks.php:52:// @TODO Move this into a filter ./inc/modules/member/what-transfer.php:128: // @TODO Rewrite this to a filter -./inc/modules/member/what-unconfirmed.php:178: // @TODO Try to rewrite this to $content = SQL_FETCHARRAY() +./inc/modules/member/what-unconfirmed.php:177: // @TODO Try to rewrite this to $content = SQL_FETCHARRAY() +./inc/modules/member/what-unconfirmed.php:244: // @TODO cat_id is unused ./inc/modules/member/what-unconfirmed.php:247: // @TODO This 'userid' cannot be saved because of encapsulated EL code ./inc/modules/order.php:78: // @TODO Unused: 2,4 -./inc/mysql-manager.php:1492: // @TODO Rewrite these lines to a filter -./inc/mysql-manager.php:1516: // @TODO Rewrite this to a filter -./inc/mysql-manager.php:1832: // @TODO Rewrite this to a filter -./inc/mysql-manager.php:2000: // @TODO If we can rewrite the EL sub-system to support more than one parameter, this call_user_func_array() can be avoided -./inc/mysql-manager.php:2123: // @TODO Used generic 'userid' here -./inc/mysql-manager.php:2344:// @TODO cacheFiles is not yet supported +./inc/mysql-manager.php:1498: // @TODO Rewrite these lines to a filter +./inc/mysql-manager.php:1522: // @TODO Rewrite this to a filter +./inc/mysql-manager.php:1838: // @TODO Rewrite this to a filter +./inc/mysql-manager.php:2006: // @TODO If we can rewrite the EL sub-system to support more than one parameter, this call_user_func_array() can be avoided +./inc/mysql-manager.php:2129: // @TODO Used generic 'userid' here +./inc/mysql-manager.php:2350:// @TODO cacheFiles is not yet supported ./inc/mysql-manager.php:408: // @TODO Try to rewrite this to one or more functions ./inc/mysql-manager.php:44:// @TODO Can we cache this? ./inc/revision-functions.php:168:// @TODO This function does also set and get in 'cache_array' diff --git a/inc/libs/rallye_functions.php b/inc/libs/rallye_functions.php index 1d697914c0..b599a0f1cd 100644 --- a/inc/libs/rallye_functions.php +++ b/inc/libs/rallye_functions.php @@ -972,10 +972,10 @@ FROM LEFT JOIN `{?_MYSQL_PREFIX?}_refdepths` AS `d` ON - s.`level`=`d`.`level` + `s`.`level`=`d`.`level` WHERE - s.`userid`=%s AND - s.`level`=1", + `s`.`userid`=%s AND + `s`.`level`=1", array( bigintval($userid) ), __FUNCTION__, __LINE__); diff --git a/inc/loader/load- b/inc/loader/load- index aeedfc5dbe..7be34fe1e7 100644 --- a/inc/loader/load- +++ b/inc/loader/load- @@ -51,9 +51,6 @@ if (($GLOBALS['cache_instance']->loadCacheFile('foo')) && ($GLOBALS['cache_insta // Create cache file $GLOBALS['cache_instance']->init(); - // Load every data from DB to cache file - //$add = runFilterChain('sql_admin_extra_data'); - // Query the database about this $result = SQL_QUERY('SELECT * FROM `{?_MYSQL_PREFIX?}_foos` ORDER BY `some_bar` ASC', __FILE__, __LINE__); while ($dummy = SQL_FETCHARRAY($result)) { diff --git a/inc/loader/load-imprint.php b/inc/loader/load-imprint.php index eb1d818d81..05031b4e3b 100644 --- a/inc/loader/load-imprint.php +++ b/inc/loader/load-imprint.php @@ -54,9 +54,6 @@ if (($GLOBALS['cache_instance']->loadCacheFile('imprint')) && ($GLOBALS['cache_i // Create cache file $GLOBALS['cache_instance']->init(); - // Load every data from DB to cache file - $add = runFilterChain('sql_admin_extra_data'); - // Query the database about this $result = SQL_QUERY('SELECT * FROM `{?_MYSQL_PREFIX?}_imprint_data` ORDER BY `imprint_id` ASC', __FILE__, __LINE__); while ($row = SQL_FETCHARRAY($result)) { diff --git a/inc/loader/load-payments.php b/inc/loader/load-payments.php index 6a6ce9d68c..c861e22df2 100644 --- a/inc/loader/load-payments.php +++ b/inc/loader/load-payments.php @@ -70,9 +70,6 @@ if (($GLOBALS['cache_instance']->loadCacheFile('payments')) && ($GLOBALS['cache_ // Create cache file $GLOBALS['cache_instance']->init(); - // Load every data from DB to cache file - //$add = runFilterChain('sql_admin_extra_data'); - // Query the database about this $result = SQL_QUERY('SELECT * FROM `{?_MYSQL_PREFIX?}_payments` ORDER BY `id` ASC', __FILE__, __LINE__); while ($dummy = SQL_FETCHARRAY($result)) { diff --git a/inc/modules/admin/what-del_email.php b/inc/modules/admin/what-del_email.php index 1ffcee889c..60705d4ea6 100644 --- a/inc/modules/admin/what-del_email.php +++ b/inc/modules/admin/what-del_email.php @@ -54,12 +54,9 @@ if ((isPostRequestElementSet('id')) && (isGetRequestElementSet('type')) && (post // Delete mail only once if (SQL_NUMROWS($result) == 1) { // Load data + // @TODO Unused: cat_id $content = SQL_FETCHARRAY($result); - // Get points we shall pay back per mail - $content['price'] = getPaymentData($content['payment_id'], 'price'); - // @TODO Unused: cat_id, payment_id - // Prepare data for the template $content['timestamp'] = generateDateTime($content['timestamp'], 0); @@ -72,15 +69,15 @@ if ((isPostRequestElementSet('id')) && (isGetRequestElementSet('type')) && (post // Fetch right stats_id from pool $result_pool = SQL_QUERY_ESC("SELECT - s.`id` + `s`.`id` FROM `{?_MYSQL_PREFIX?}_user_stats` AS `s` LEFT JOIN `{?_MYSQL_PREFIX?}_pool` AS `p` ON - s.`pool_id`=`p`.`id` + `s`.`pool_id`=`p`.`id` WHERE - s.`pool_id`=%s + `s`.`pool_id`=%s LIMIT 1", array(bigintval(getRequestElement('id'))), __FILE__, __LINE__); if (SQL_NUMROWS($result_pool) == 1) { diff --git a/inc/modules/admin/what-list_unconfirmed.php b/inc/modules/admin/what-list_unconfirmed.php index 4a87666826..aec2a6935d 100644 --- a/inc/modules/admin/what-list_unconfirmed.php +++ b/inc/modules/admin/what-list_unconfirmed.php @@ -58,19 +58,19 @@ $sql = ''; if ((isPostRequestElementSet('id')) && (isGetRequestElementSet('type')) && (postRequestElement('type') == 'normal')) { // SQL query for mail data $sql = sprintf("SELECT - s.`id`, + `s`.`id`, `p`.`sender`, `p`.`subject`, `p`.`text`, `p`.`url`, `p`.`timestamp`, - s.`max_rec` + `s`.`max_rec` FROM `{?_MYSQL_PREFIX?}_pool` AS `p` LEFT JOIN `{?_MYSQL_PREFIX?}_user_stats` AS `s` ON - `p`.`id`=s.`pool_id` + `p`.`id`=`s`.`pool_id` WHERE `p`.`id`=%s LIMIT 1", diff --git a/inc/modules/admin/what-payments.php b/inc/modules/admin/what-payments.php index 0f11f5fed2..7a1def3cf1 100644 --- a/inc/modules/admin/what-payments.php +++ b/inc/modules/admin/what-payments.php @@ -47,25 +47,55 @@ if (((!isPostRequestElementSet('t_wait')) || (!isPostRequestElementSet('payment' unsetPostRequestElement('ok'); } // END - if +// Init SQL array +nitSqls(); + if (isFormSent()) { switch (getRequestElement('do')) { case 'add': - addSql("INSERT INTO `{?_MYSQL_PREFIX?}_payments` (`time`, `payment`, `mail_title`, `price`) VALUES ('".postRequestElement('t_wait')."','".postRequestElement('payment')."','".postRequestElement('title')."','".postRequestElement('price')."')"); - if (countSumTotalData(postRequestElement('t_wait'), 'payments', 'id', 'time', TRUE) == 1) { - // Re-init the array here - initSqls(); + if (countSumTotalData(postRequestElement('t_wait'), 'payments', 'id', 'time', TRUE) == 0) { + addSql("INSERT INTO + `{?_MYSQL_PREFIX?}_payments` +( + `time`, + `payment`, + `mail_title`, + `price` +) VALUES ( + '" . postRequestElement('t_wait') . "', + '" . postRequestElement('payment') . "', + '" . postRequestElement('title') . "', + '" . postRequestElement('price') . "' +)"); } // END - if break; case 'edit': foreach (postRequestElement('time') as $id => $value) { - addSql("UPDATE `{?_MYSQL_PREFIX?}_payments` SET `time`='" . $value . "',`payment`='".postRequestElement('payment', $id)."', price='".postRequestElement('price', $id)."', mail_title='".postRequestElement('mail_title', $id)."' WHERE `id`='".$id."' LIMIT 1"); + // Secure id + $id = bigintval($id); + + // Add UPDATE + addSql("UPDATE + `{?_MYSQL_PREFIX?}_payments` +SET + `time`='" . $value . "', + `payment`='" . postRequestElement('payment', $id) . "', + `price`='" . postRequestElement('price', $id) . "', + `mail_title`='" . postRequestElement('mail_title', $id) . "' +WHERE + `id`='" . $id . "' +LIMIT 1"); } // END - foreach break; case 'delete': foreach (postRequestElement('id') as $id => $value) { - addSql("DELETE LOW_PRIORITY FROM `{?_MYSQL_PREFIX?}_payments` WHERE `id`=" . bigintval($id) . " LIMIT 1"); + // Secure id + $id = bigintval($id); + + // Add DELETE + addSql("DELETE LOW_PRIORITY FROM `{?_MYSQL_PREFIX?}_payments` WHERE `id`=" . $id . " LIMIT 1"); } // END - foreach break; } // END - switch @@ -91,7 +121,17 @@ if (isFormSent()) { // Delete entries here $OUT = ''; foreach (postRequestElement('sel') as $id => $value) { - $result = SQL_QUERY_ESC("SELECT `id`, `time`, `mail_title` FROM `{?_MYSQL_PREFIX?}_payments` WHERE `id`=%s LIMIT 1", + $result = SQL_QUERY_ESC("SELECT + `id`, + `time`, + `payment`, + `mail_title`, + `price` +FROM + `{?_MYSQL_PREFIX?}_payments` +WHERE + `id`=%s +LIMIT 1", array(bigintval($id)), __FILE__, __LINE__); $content = SQL_FETCHARRAY($result); @@ -108,7 +148,17 @@ if (isFormSent()) { // Edit entries $OUT = ''; foreach (postRequestElement('sel') as $id => $value) { - $result = SQL_QUERY_ESC("SELECT `id`, `time`, `payment`, `mail_title`, `price` FROM `{?_MYSQL_PREFIX?}_payments` WHERE `id`=%s LIMIT 1", + $result = SQL_QUERY_ESC("SELECT + `id`, + `time`, + `payment`, + `mail_title`, + `price` +FROM + `{?_MYSQL_PREFIX?}_payments` +WHERE + `id`=%s +LIMIT 1", array(bigintval($id)), __FILE__, __LINE__); $content = SQL_FETCHARRAY($result); @@ -123,7 +173,16 @@ if (isFormSent()) { loadTemplate('admin_edit_payments', FALSE, $OUT); } else { // Referral levels - $result = SQL_QUERY("SELECT `id`, `time`, `payment`, `mail_title`, `price` FROM `{?_MYSQL_PREFIX?}_payments` ORDER BY `time` ASC", __FILE__, __LINE__); + $result = SQL_QUERY("SELECT + `id`, + `time`, + `payment`, + `mail_title`, + `price` +FROM + `{?_MYSQL_PREFIX?}_payments` +ORDER BY + `time` ASC", __FILE__, __LINE__); if (!SQL_HASZERONUMS($result)) { // Make referral levels editable and deletable $OUT = ''; diff --git a/inc/modules/admin/what-unlock_emails.php b/inc/modules/admin/what-unlock_emails.php index f23946d3ba..980ee0cae4 100644 --- a/inc/modules/admin/what-unlock_emails.php +++ b/inc/modules/admin/what-unlock_emails.php @@ -70,19 +70,14 @@ if ((!SQL_HASZERONUMS($result_main)) || (isFormSent('lock'))) { // Order placed in queue... $result = SQL_QUERY_ESC('SELECT - po.`url`, - po.`subject`, - po.`sender`, - pay.`payment`, - po.`payment_id` + `url`, + `subject`, + `sender`, + `payment_id` FROM - `{?_MYSQL_PREFIX?}_pool` AS `po` -INNER JOIN - `{?_MYSQL_PREFIX?}_payments` AS `pay` -ON - po.`payment_id`=pay.`id` + `{?_MYSQL_PREFIX?}_pool` WHERE - po.`id`=%s + `id`=%s LIMIT 1', array($id), __FILE__, __LINE__); @@ -91,6 +86,9 @@ LIMIT 1', // Load data $content = SQL_FETCHARRAY($result); + // Get payment from getter to avoid JOIN + $content['payment'] = getPaymentData($content['payment_id', 'payment'); + // Is the surfbar installed? // @TODO Rewrite these if-blocks to a filter if ((isExtensionActive('surfbar')) && (getConfig('surfbar_migrate_order') == 'Y')) { diff --git a/inc/modules/member/what-order.php b/inc/modules/member/what-order.php index a1f7a2a08f..0cc9a78a8c 100644 --- a/inc/modules/member/what-order.php +++ b/inc/modules/member/what-order.php @@ -245,7 +245,7 @@ LIMIT 1", } // END - if // Calculate used points - $usedPoints = $content['target_send'] * getPaymentData(bigintval(postRequestElement('mail_type'))); + $usedPoints = $content['target_send'] * getPaymentPrice(bigintval(postRequestElement('mail_type'))); // Fix empty zip code if (!isPostRequestElementSet('zip')) { @@ -636,13 +636,13 @@ LIMIT 1", $content['category_selection'] = generateCategoryOptionsList(((isExtensionActive('html_mail')) && (isPostRequestElementSet('html'))) ? postRequestElement('html') : 'N', getMemberId()); // Mail type - $content['type_selection'] = ''; + $content['payment_selection'] = ''; foreach ($payments as $key => $value) { if (is_array($value)) { // Output option line - $content['type_selection'] .= '