diff -ura subscribe-to-comments.o/subscribe-to-comments.php subscribe-to-comments/subscribe-to-comments.php --- subscribe-to-comments.o/subscribe-to-comments.php 2008-10-18 12:15:57.000000000 +0200 +++ subscribe-to-comments/subscribe-to-comments.php 2008-10-20 14:19:20.000000000 +0200 @@ -158,6 +158,16 @@ echo '

  • '; + echo ''; + + echo '
    ' . __('E-Mail Text', 'subscribe-to-comments') . ''; + + echo '
    '; @@ -413,18 +423,129 @@ global $wpdb; $cid = (int) $cid; $id = (int) $id; - $email = strtolower($wpdb->get_var("SELECT comment_author_email FROM $wpdb->comments WHERE comment_ID = '$cid'")); + + $row = $wpdb->get_row("SELECT comment_author_email, comment_post_ID, comment_author_IP from $wpdb->comments WHERE comment_ID = '$cid'"); + + $email = $row->comment_author_email; + $postid = $row->comment_post_ID; + $ip = $row->comment_author_IP; + $email_sql = $wpdb->escape($email); - $postid = $wpdb->get_var("SELECT comment_post_ID from $wpdb->comments WHERE comment_ID = '$cid'"); $previously_subscribed = ( $wpdb->get_var("SELECT comment_subscribe from $wpdb->comments WHERE comment_post_ID = '$postid' AND LCASE(comment_author_email) = '$email_sql' AND comment_subscribe = 'Y' LIMIT 1") || in_array($email, (array) get_post_meta($postid, '_sg_subscribe-to-comments')) ) ? true : false; // If user wants to be notified or has previously subscribed, set the flag on this current comment if (($_POST['subscribe'] == 'subscribe' && is_email($email)) || $previously_subscribed) { - delete_post_meta($postid, '_sg_subscribe-to-comments', $email); - $wpdb->query("UPDATE $wpdb->comments SET comment_subscribe = 'Y' where comment_post_ID = '$postid' AND LCASE(comment_author_email) = '$email'"); - } + delete_post_meta($postid, '_sg_subscribe-to-comments', $email); + + $subscribe = true; + + $sendOptIn = true; + if ($previously_subscribed) { + $sendOptIn = false; + } + + if ($sendOptIn) { + // check if author has subscribed to a comment in the last 3 months + + $sql = "SELECT COUNT(*) + FROM " . $wpdb->comments . " + WHERE DATE_SUB(CURDATE(), INTERVAL 3 MONTH) <= comment_date_gmt + AND LCASE(comment_author_email) = '" . $email_sql . "' + AND comment_subscribe = 'Y'"; + + $result = $wpdb->get_var($sql); + if ($result >= 1) { + $sendOptIn = false; + } + } + + if ($sendOptIn) { + // check if we already sent a mail in the last 24 hours + + $sql = "SELECT COUNT(*) + FROM " . $wpdb->comments . " + WHERE DATE_SUB(CURDATE(), INTERVAL 1 DAY) <= comment_date_gmt + AND LCASE(comment_author_email) = '" . $email_sql . "' + AND comment_subscribe = 'C'"; + + $result = $wpdb->get_var($sql); + if ($result >= 1) { + $sendOptIn = false; + $subscribe = false; + } + } + + if ($sendOptIn && !$this->is_blocked($email)) { + // send double-opt in + $key = md5($cid . $email . $postid . $ip); + + // link + $link = get_option('home') . '/?wp-subscription-manager=1&opt-in=1'; + $link = add_query_arg('cid', $cid, $link); + $link = add_query_arg('key', urlencode($key), $link); + + $message = isset($this->settings['double_opt_in']) ? $this->settings['double_opt_in'] : "Click to confirm:\n[link]"; + + if (function_exists("str_ireplace")) { + $message = str_ireplace( + array("[link]", "[manager_link]"), + array($link, $this->manage_link($email, false, false)), + $message + ); + } else { + $message = str_replace( + array("[link]", "[manager_link]"), + array($link, $this->manage_link($email, false, false)), + $message + ); + } + + $subject = isset($this->settings['double_opt_in_subject']) ? $this->settings['double_opt_in_subject'] : "Confirm the subscription"; + + $this->send_mail($email, $subject, $message); + + $wpdb->query("UPDATE $wpdb->comments SET comment_subscribe = 'C' where comment_post_ID = '$postid' AND LCASE(comment_author_email) = '$email'"); + } + + if (!$sendOptIn && $subscribe) { + $wpdb->query("UPDATE $wpdb->comments SET comment_subscribe = 'Y' where comment_post_ID = '$postid' AND LCASE(comment_author_email) = '$email'"); + } else { + // mail sent but not confirmed, set comment_subscribe = C + $wpdb->query("UPDATE $wpdb->comments SET comment_subscribe = 'C' where comment_post_ID = '$postid' AND LCASE(comment_author_email) = '$email'"); + } + } + return $cid; + } + + function add_opt_in_subscriber($cid, $key) + { + global $wpdb; + + $cid = (int) $cid; + $row = $wpdb->get_row("SELECT comment_author_email, comment_post_ID, comment_author_IP, comment_subscribe from $wpdb->comments WHERE comment_ID = '$cid'"); + + if (!is_object($row)) { + return 1; + } + + if ($row->comment_subscribe == "Y") { + return 2; + } + + $postid = $row->comment_post_ID; + $email = $row->comment_author_email; + + $checkkey = md5($cid . $email . $postid . $row->comment_author_IP); + + if ($key != $checkkey) { + return 3; + } + + $wpdb->query("UPDATE $wpdb->comments SET comment_subscribe = 'Y' where comment_subscribe = 'C' AND LCASE(comment_author_email) = '$email'"); + + return 0; } @@ -725,18 +846,18 @@ delete_option('sg_subscribe_settings'); wp_redirect('http://' . $_SERVER['HTTP_HOST'] . add_query_arg('stcwpbug', '2')); exit; - } + } if ( $update ) $this->update_settings($settings); - - $column_name = 'comment_subscribe'; - foreach ( (array) $wpdb->get_col("DESC $wpdb->comments", 0) as $column ) - if ($column == $column_name) - return true; - - // didn't find it... create it - $wpdb->query("ALTER TABLE $wpdb->comments ADD COLUMN comment_subscribe enum('Y','N') NOT NULL default 'N'"); + + $result = $wpdb->get_row("DESC " . $wpdb->comments . " comment_subscribe"); + + if (!is_object($result)) { + $wpdb->query("ALTER TABLE $wpdb->comments ADD COLUMN comment_subscribe enum('Y','C','N') NOT NULL default 'N'"); + } else if (strpos($result->Type, "C") === false) { + $wpdb->query("ALTER TABLE " . $wpdb->comments . " MODIFY `comment_subscribe` enum('Y','C','N') NOT NULL default 'N'"); + } } @@ -860,9 +981,34 @@ } function sg_subscribe_admin($standalone = false) { - global $wpdb, $sg_subscribe; - - sg_subscribe_start(); + global $wpdb, $sg_subscribe; + + sg_subscribe_start(); + + if (isset($_REQUEST["opt-in"], $_REQUEST["cid"], $_REQUEST["key"])) { + $result = $sg_subscribe->add_opt_in_subscriber($_REQUEST["cid"], $_REQUEST["key"]); + + switch ($result) { + + case 0: + die ( __('Successfully subscribed!', 'subscribe-to-comments') ); + break; + + case 1: + die ( __('Error while fetching db record!', 'subscribe-to-comments') ); + break; + + case 2: + die ( __('Already subscribed!', 'subscribe-to-comments') ); + break; + + case 3: + die ( __('Invalid key!', 'subscribe-to-comments') ); + break; + + } + die ( __('You may not access this page without a valid key.', 'subscribe-to-comments') ); + } if ( $standalone ) { $sg_subscribe->form_action = get_option('home') . '/?wp-subscription-manager=1';