public static function update_status($new_status, $params = null)
 {
     global $wpdb;
     $output = "";
     $from_date = isset($params['from_date']) ? $params['from_date'] : null;
     $from_datetime = $from_date ? DateHelper::u2s($from_date) : null;
     $thru_date = isset($params['thru_date']) ? $params['thru_date'] : null;
     $thru_datetime = $thru_date ? DateHelper::u2s($thru_date, 24 * 3600) : null;
     $referral_status = isset($params['referral_status']) ? Affiliates_Utility::verify_referral_status_transition($params['referral_status'], $params['referral_status']) : null;
     $currency_id = isset($params['currency_id']) ? Affiliates_Utility::verify_currency_id($params['currency_id']) : null;
     $orderby = isset($params['orderby']) ? $params['orderby'] : null;
     $order = isset($params['order']) ? $params['order'] : null;
     switch ($orderby) {
         case 'affiliate_id':
         case 'name':
         case 'email':
             $orderby = 'a.' . $orderby;
             break;
         case 'user_login':
             $orderby = 'au.' . $orderby;
             break;
         case 'currency_id':
             $orderby = 'r.' . $orderby;
             break;
         default:
             $orderby = 'a.name';
     }
     switch ($order) {
         case 'asc':
         case 'ASC':
         case 'desc':
         case 'DESC':
             break;
         default:
             $order = 'ASC';
     }
     if (isset($params['tables'])) {
         $output .= "<h1>" . __("Closing referrals", AFFILIATES_PLUGIN_DOMAIN) . "</h1>";
         $output .= "<div class='closing-referrals-overview'>";
         $affiliates_table = $params['tables']['affiliates'];
         $affiliates_users_table = $params['tables']['affiliates_users'];
         $referrals_table = $params['tables']['referrals'];
         $users_table = $params['tables']['users'];
         $filters = array(" 1=%d ");
         $filter_params = array(1);
         if ($from_datetime && $thru_datetime) {
             $filters[] = " r.datetime >= %s AND r.datetime < %s ";
             $filter_params[] = $from_datetime;
             $filter_params[] = $thru_datetime;
         } else {
             if ($from_datetime) {
                 $filters[] = " r.datetime >= %s ";
                 $filter_params[] = $from_datetime;
             } else {
                 if ($thru_datetime) {
                     $filters[] = " r.datetime < %s ";
                     $filter_params[] = $thru_datetime;
                 }
             }
         }
         if ($referral_status) {
             $filters[] = " r.status = %s ";
             $filter_params[] = $referral_status;
         }
         if ($currency_id) {
             $filters[] = " r.currency_id = %s ";
             $filter_params[] = $currency_id;
         }
         if (!empty($filters)) {
             $filters = " WHERE " . implode(" AND ", $filters);
         } else {
             $filters = '';
         }
         $order_by = '';
         if ($orderby && $order) {
             $order_by .= " ORDER BY {$orderby} {$order} ";
         }
         $step = isset($params['step']) ? intval($params['step']) : 1;
         switch ($step) {
             case 1:
                 $results = $wpdb->get_results($wpdb->prepare("\n\t\t\t\t\t\tSELECT a.*, r.*, u.user_login\n\t\t\t\t\t\tFROM {$referrals_table} r\n\t\t\t\t\t\tLEFT JOIN {$affiliates_table} a ON r.affiliate_id = a.affiliate_id\n\t\t\t\t\t\tLEFT JOIN {$affiliates_users_table} au ON a.affiliate_id = au.affiliate_id\n\t\t\t\t\t\tLEFT JOIN {$users_table} u on au.user_id = u.ID\n\t\t\t\t\t\t{$filters}\n\t\t\t\t\t\t{$order_by}\n\t\t\t\t\t\t", $filter_params));
                 $output .= "<div class='manage'>";
                 $output .= "<div class='warning'>";
                 $output .= "<p>";
                 $output .= "<strong>";
                 $output .= __("Please review the list of referrals that will be <em>closed</em>.", AFFILIATES_PLUGIN_DOMAIN);
                 $output .= "</strong>";
                 $output .= "</p>";
                 $output .= "</div>";
                 // .warning
                 $output .= "<p>";
                 $output .= __("Usually only referrals that are <em>accepted</em> and have been paid out should be <em>closed</em>. If there are unwanted or too many referrals shown, restrict your filter settings.", AFFILIATES_PLUGIN_DOMAIN);
                 $output .= "</p>";
                 $output .= "<p>";
                 $output .= __("If these referrals can be closed, click the confirmation button below.", AFFILIATES_PLUGIN_DOMAIN);
                 $output .= "</p>";
                 $output .= "</div>";
                 $output .= '<div id="referrals-overview" class="referrals-overview">';
                 $output .= self::render_results($results);
                 $output .= '</div>';
                 // .referrals-overview
                 if (count($results > 0)) {
                     $mp_params = "";
                     if (!empty($from_date)) {
                         $mp_params .= "&from_date=" . urlencode($from_date);
                     }
                     if (!empty($thru_date)) {
                         $mp_params .= "&thru_date=" . urlencode($thru_date);
                     }
                     if (!empty($referral_status)) {
                         $mp_params .= "&referral_status=" . urlencode($referral_status);
                     }
                     if (!empty($currency_id)) {
                         $mp_params .= "&currency_id=" . urlencode($currency_id);
                     }
                     if (!empty($orderby)) {
                         $mp_params .= "&orderby=" . urlencode($orderby);
                     }
                     if (!empty($order)) {
                         $mp_params .= "&order=" . urlencode($order);
                     }
                     $output .= '<div class="manage confirm">';
                     $current_url = (is_ssl() ? 'https://' : 'http://') . $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'];
                     $current_url = remove_query_arg('paged', $current_url);
                     $current_url = remove_query_arg('action', $current_url);
                     $current_url = remove_query_arg('affiliate_id', $current_url);
                     $output .= '<style type="text/css">';
                     $output .= '.close-referrals img, .close-referrals .label { vertical-align: middle; }';
                     $output .= '</style>';
                     $output .= "<p>";
                     $output .= __("Close these referrals by clicking:", AFFILIATES_PLUGIN_DOMAIN);
                     $output .= "</p>";
                     $output .= "<a title='" . __('Click to close these referrals', AFFILIATES_PLUGIN_DOMAIN) . "' " . "class='close-referrals button' " . "href='" . esc_url($current_url) . "&action=close_referrals&step=2" . $mp_params . "'>" . "<img class='icon' alt='" . __('Close referrals', AFFILIATES_PLUGIN_DOMAIN) . "' src='" . AFFILIATES_PLUGIN_URL . "images/closed.png'/>" . "<span class='label'>" . __('Close Referrals', AFFILIATES_PLUGIN_DOMAIN) . "</span>" . "</a>";
                     $output .= "<div class='warning'>";
                     $output .= "<p>";
                     $output .= "<strong>";
                     $output .= __("This action can not be undone*.", AFFILIATES_PLUGIN_DOMAIN);
                     $output .= "</strong>";
                     $output .= "</p>";
                     $output .= "<p>";
                     $output .= "<span style='font-size:0.8em;'>";
                     $output .= __("*To undo, each referral would have to be set to the desired status individually.", AFFILIATES_PLUGIN_DOMAIN);
                     $output .= "</span>";
                     $output .= "</p>";
                     $output .= "</div>";
                     // .warning
                     $output .= '</div>';
                     // .manage.confirm
                 }
                 break;
                 // step 1 - ask for confirmation confirmation
             // step 1 - ask for confirmation confirmation
             case 2:
                 // try to make the changes
                 $results = $wpdb->get_results($wpdb->prepare("\n\t\t\t\t\t\tSELECT a.*, r.*, u.user_login\n\t\t\t\t\t\tFROM {$referrals_table} r\n\t\t\t\t\t\tLEFT JOIN {$affiliates_table} a ON r.affiliate_id = a.affiliate_id\n\t\t\t\t\t\tLEFT JOIN {$affiliates_users_table} au ON a.affiliate_id = au.affiliate_id\n\t\t\t\t\t\tLEFT JOIN {$users_table} u on au.user_id = u.ID\n\t\t\t\t\t\t{$filters}\n\t\t\t\t\t\t{$order_by}\n\t\t\t\t\t\t", $filter_params));
                 $updated = array();
                 $omitted = array();
                 $failed = array();
                 foreach ($results as $result) {
                     if ($s = Affiliates_Utility::verify_referral_status_transition($result->status, $new_status)) {
                         if ($wpdb->query($wpdb->prepare("UPDATE {$referrals_table} SET status = %s WHERE affiliate_id = %d AND post_id = %d AND datetime = %s ", $s, $result->affiliate_id, $result->post_id, $result->datetime))) {
                             $result->status = $s;
                             $updated[] = $result;
                         } else {
                             $failed[] = $result;
                         }
                     } else {
                         $omitted[] = $result;
                     }
                 }
                 // always show at least the updated table because this will
                 // also give information if no results have been updated
                 $status_descriptions = array(AFFILIATES_REFERRAL_STATUS_ACCEPTED => __('Accepted', AFFILIATES_PLUGIN_DOMAIN), AFFILIATES_REFERRAL_STATUS_CLOSED => __('Closed', AFFILIATES_PLUGIN_DOMAIN), AFFILIATES_REFERRAL_STATUS_PENDING => __('Pending', AFFILIATES_PLUGIN_DOMAIN), AFFILIATES_REFERRAL_STATUS_REJECTED => __('Rejected', AFFILIATES_PLUGIN_DOMAIN));
                 $output .= "<h2>" . __("Updated", AFFILIATES_PLUGIN_DOMAIN) . "</h2>";
                 $output .= "<p>";
                 $output .= sprintf(__("These referrals have been updated to <em>%s</em>.", AFFILIATES_PLUGIN_DOMAIN), isset($status_descriptions[$new_status]) ? $status_descriptions[$new_status] : $new_status);
                 $output .= "</p>";
                 $output .= self::render_results($updated);
                 if (count($omitted) > 0) {
                     $output .= "<h2>" . __("Omitted", AFFILIATES_PLUGIN_DOMAIN) . "</h2>";
                     $output .= "<p>";
                     $output .= sprintf(__("These referrals have been omitted because their status must not be changed to <em>%s</em>.", AFFILIATES_PLUGIN_DOMAIN), isset($status_descriptions[$new_status]) ? $status_descriptions[$new_status] : $new_status);
                     $output .= "</p>";
                     $output .= self::render_results($omitted);
                 }
                 if (count($failed) > 0) {
                     $output .= "<h2>" . __("Failed", AFFILIATES_PLUGIN_DOMAIN) . "</h2>";
                     $output .= "<p>";
                     $output .= sprintf(__("These referrals could not be updated to <em>%s</em>.", AFFILIATES_PLUGIN_DOMAIN), isset($status_descriptions[$new_status]) ? $status_descriptions[$new_status] : $new_status);
                     $output .= "</p>";
                     $output .= self::render_results($failed);
                 }
                 break;
                 // step 2 -commit changes
         }
         $output .= "</div>";
         // .closing-referrals-overview
     }
     return $output;
 }
 /**
  * Save widget options
  * 
  * @see WP_Widget::update()
  */
 function update($new_instance, $old_instance)
 {
     $settings = $old_instance;
     $settings['title'] = strip_tags($new_instance['title']);
     if (!empty($new_instance['amount'])) {
         $settings['amount'] = Affiliates_Utility::verify_referral_amount($new_instance['amount']);
     } else {
         unset($settings['amount']);
     }
     if (!empty($new_instance['currency_id'])) {
         $settings['currency_id'] = Affiliates_Utility::verify_currency_id($new_instance['currency_id']);
     } else {
         unset($settings['currency_id']);
     }
     return $settings;
 }
/**
 * Update the referral.
 * @param array $attributes to update, supports: affiliate_id, post_id, datetime, description, amount, currency_id, status, reference
 * @return array with keys, values and old_values or null if nothing was updated
 */
function affiliates_update_referral($referral_id, $attributes)
{
    global $wpdb;
    $result = null;
    $referral = null;
    $referrals_table = _affiliates_get_tablename('referrals');
    if ($referrals = $wpdb->get_results($wpdb->prepare("SELECT * FROM {$referrals_table} WHERE referral_id = %d", intval($referral_id)))) {
        if (count($referrals) > 0) {
            $referral = $referrals[0];
        }
    }
    if ($referral !== null) {
        $set = array();
        $keys = array();
        $values = array();
        $old_values = array();
        foreach ($attributes as $key => $value) {
            $current_value = isset($referral->{$key}) ? $referral->{$key} : null;
            if ($current_value !== $value) {
                switch ($key) {
                    case 'affiliate_id':
                    case 'post_id':
                        $set[] = " {$key} = %d ";
                        $keys[] = $key;
                        $values[] = intval($value);
                        $old_values[] = $current_value;
                        break;
                    case 'datetime':
                    case 'description':
                    case 'reference':
                        $set[] = " {$key} = %s ";
                        $keys[] = $key;
                        $values[] = $value;
                        $old_values[] = $current_value;
                        break;
                    case 'status':
                        // Just check that this is a valid status:
                        if (!empty($value) && Affiliates_Utility::verify_referral_status_transition($value, $value)) {
                            $set[] = " {$key} = %s ";
                            $keys[] = $key;
                            $values[] = $value;
                            $old_values[] = $current_value;
                        }
                        break;
                    case 'amount':
                        if ($value = Affiliates_Utility::verify_referral_amount($value)) {
                            $set[] = " {$key} = %s ";
                            $keys[] = $key;
                            $values[] = $value;
                            $old_values[] = $current_value;
                        }
                        break;
                    case 'currency_id':
                        if ($value = Affiliates_Utility::verify_currency_id($value)) {
                            $set[] = " {$key} = %s ";
                            $keys[] = $key;
                            $values[] = $value;
                            $old_values[] = $current_value;
                        }
                        break;
                }
            }
        }
        if (count($set) > 0) {
            $set = implode(' , ', $set);
            if ($wpdb->query($wpdb->prepare("UPDATE {$referrals_table} SET {$set} WHERE referral_id = %d", array_merge($values, array(intval($referral_id)))))) {
                $result = array('keys' => $keys, 'values' => $values, 'old_values' => $old_values);
                do_action('affiliates_updated_referral', intval($referral_id), $keys, $values, $old_values);
            }
        }
    }
    return $result;
}