Email to specific expired users

Hi,

I would like to be able to send emails to expired users of certain types of subscriptions in the same way that you can send emails to active users of certain types of subs.

I've had a brief look at the code, but if anyone else has done this customization any advice would b e greatly appreciated.

Thanks
Richie

Any ideas on this anyone?

I asked support about this a while back and they sent me a mysql query that didnt work- i think i eventually did it using the aMember export function and importing the names into my mailing list program.

David

I still see a need for this -- perhap a plug in or add-on program.

DavidM -- you exported expired members -- then you filtered who you want to send emails? And then how do I import (or mail-merge) into my Outlook? I don't think there's a mail-merge function for Outlook except buying an add-on program for Outlook.

David

Hi guys... thanks for your suggestions. After going away from the code for a little while, then revisiting it, I've figured it out. I couldn't include the full code for email.php as it was too long, but here is the modified part. I have commented the bits I've added with // ADD THIS to // STOP ADDING HERE and also one line // MODIFY THE NEXT LINE - I hope it makes sense, but if anyone wants an explanation or for me to email the full file, let me know.

PHP:

<?php 
/*
* 
*     Author: Alex Scott
*      Email: alex@cgi-central.net
*        Web: http://www.cgi-central.net
*    Details: Admin Info / PHP
*    FileName $RCSfile$
*    Release: 3.0.8PRO ($Revision: 2372 $)
*
* Please direct bug reports,suggestions or feedback to the cgi-central forums.
* http://www.cgi-central.net/forum/
*                                                                          
* aMember PRO is a commercial software. Any distribution is strictly prohibited.
*
*/
$avoid_timeout = 1;

include "../config.inc.php";
$t = new_smarty();
include "login.inc.php";
ignore_user_abort(true);
@set_time_limit(0);


check_lite();
admin_check_permissions('email');

function get_email_types(){
    global $db, $config;
    $res = array();
    $res['all']      = '* All Users (pending, active and expired)';
    $res['active']     = '* Active Users (paid and not-expired)';
    $res['pending']  = '* Pending Users (never paid)';
    $res['expired']  = '* Expired Users (paid and expired)';
    if ($config['use_affiliates'])
        $res['aff']  = '* Affiliates';
    $products = $db->get_products_list();
    foreach ($products as $p){
        $id = $p['product_id'];
        $n  = $p['title'];
        $res["PRODUCT-$id"] = "Active users of '$n'";
    }
// ADD THIS
    foreach ($products as $p){  
        $id = $p['product_id'];
        $n  = $p['title'];
        $res["EX-PRODUCT-$id"] = "Expired users of '$n'";
    }
// STOP ADDING HERE

    $newsletter_threads = $db->get_newsletter_threads();
    while ( list($id, $n) = each ($newsletter_threads)){
        $res["NEWSLETTER-$id"] = "Newsletter '$n'";
    }
    
    return $res;
}

function get_target_users($start, $count, &$total){
    global $db, $vars;

    $emails = array();
    $output = array();
    $total = 0;
    $skipped = 0;
    $today = date('Y-m-d');
    
    $email_types = array();
    $email_types = array_unique((array)$vars['email_type']);
    
    //newsletter threads
    $threads = array();
    foreach ($email_types as $email_type){
        if (preg_match('/^NEWSLETTER-(\d+)$/', $email_type, $regs)){
            $threads[] = $regs[1];
        }
    }
    $vars['newsletter_thread'] = $threads;

    /*
    if (count($threads) > 0) {
        while ( list(, $thread_id) = each($threads)) {
            $q = $db->query($s = "
                 SELECT blob_available_to AS available_to
                 FROM {$db->config['prefix']}newsletter_thread
                 WHERE thread_id = '$thread_id'
                ");
            $tr = mysql_fetch_assoc($q);
            $available_to = $tr['available_to'];
            $available_to = explode (",", $available_to);
            $email_types = array_merge ($email_types, $available_to);
        }
        $email_types = array_unique($email_types);
     }
     */
    
    // products selected
    $product_ids = array();

    // products active and expired
    $active_product_ids = array();
    $expired_product_ids = array();

    foreach ($email_types as $email_type){
        if (preg_match('/^PRODUCT-(\d+)$/', $email_type, $regs)){
            $product_ids[] = $regs[1];
        }

        if (preg_match('/^active_product-(\d+)$/', $email_type, $regs)){
            $active_product_ids[] = $regs[1];
        }
// MODIFY THE NEXT LINE
        if (preg_match('/^EX-PRODUCT-(\d+)$/', $email_type, $regs)){
            $expired_product_ids[] = $regs[1];
        }
    }
    if ($product_ids){
        $q = $db->query($s = "
            SELECT DISTINCT u.*
            FROM {$db->config['prefix']}members u 
                LEFT JOIN {$db->config['prefix']}payments p 
                ON (p.member_id=u.member_id)
            WHERE p.completed > 0 AND p.product_id IN (".join(',',$product_ids).") 
            AND p.begin_date <= NOW() AND p.expire_date >= NOW()
            AND IFNULL(u.unsubscribed,0) = 0
            GROUP BY u.member_id
        ");
        while ($u = mysql_fetch_assoc($q)){
            if ($emails[$u['email']] || !check_email($u['email'])) continue;
            // make output
            if ($skipped <= $start-1)  {  
                // skip, wait for $start
                $skipped++;
            } elseif (count($output) >= $count) {
                //skip - we had necessary records
            } else {
                $u['data'] = unserialize($u[data]);
                $output[] = $u;
            }
//            print "skipped=$skipped;start=$start;count=$count;co=".count($output).";total=$total<br />";
            $total++;
            $emails[$u['email']]++;
        }
    }
    
    
// ADD THIS
    if ($expired_product_ids){
        $q = $db->query($s = "
            SELECT DISTINCT u.*
            FROM {$db->config['prefix']}members u 
                LEFT JOIN {$db->config['prefix']}payments p 
                ON (p.member_id=u.member_id)
            WHERE p.completed > 0 AND p.product_id IN (".join(',',$expired_product_ids).") 
            AND p.expire_date < NOW()
            AND IFNULL(u.unsubscribed,0) = 0
            GROUP BY u.member_id
        ");
        while ($u = mysql_fetch_assoc($q)){
            if ($emails[$u['email']] || !check_email($u['email'])) continue;
            // make output
            if ($skipped <= $start-1)  {  
                // skip, wait for $start
                $skipped++;
            } elseif (count($output) >= $count) {
                //skip - we had necessary records
            } else {
                $u['data'] = unserialize($u[data]);
                $output[] = $u;
            }
//            print "skipped=$skipped;start=$start;count=$count;co=".count($output).";total=$total<br />";
            $total++;
            $emails[$u['email']]++;
        }
    }
//    STOP ADDING HERE

    if (count($active_product_ids) > 0 || count($expired_product_ids) > 0){
            $where_active = "";
            $where_expired = "";



That looks like a very cool hack.

Tested- but the count seems to be off.
I get a different total when I do a "search users" for a product that I know all have expired on and the count given to me in the "email will be sent to" message.

David

Hmm... thanks for pointing that out. I'll take another look later today and post some revised code.

Hi, I think I know what the issue is... the query is selecting everyone from the database who has an expired subscription of this type, regardless of whether they have renewed either with the same or a different type of subscription. This is fine for my purposes, but I understand that for most people it would be best to isolate just those expired users. I'll have a rethink on the query and come back to you.

Skippybosco and davidm1,

When richie comes out with revised code, is it possible to create a plugin/add-on rather than going into PHP Code file to add/modify the codes? I'm not sure if aMember 3.1.X is flexible with this (I understand aMember 4.x will be much more flexible doing this plugin or add-on).

Hi guy, sorry its taken a while to get back to you all.

I'm having some difficulties with this. I've written a new query, which weirdly works if I use it through a console (Navicat) and brings up the correct number of users, but as soon as I slot it into the email.php page in the expired_product_ids query I get the wrong count... I had to resort to using nested queries and after spending about 4 hours on it yesterday I've now drawn a blank. I'm going to keep trying but if anyone else wants a go, here is the query that worked but didn't!

PHP:

SELECT distinct u.*
            FROM {$db->config['prefix']}members u 
                LEFT JOIN {$db->config['prefix']}payments p 
                ON (p.member_id=u.member_id)
            WHERE p.completed > 0 AND p.product_id IN (".join(',',$expired_product_ids).") 
            AND p.expire_date < now()
            AND p.expire_date = (SELECT expire_date 
                                    FROM {$db->config['prefix']}payments
                                    WHERE amember_payments.member_id=u.member_id 
                                    AND amember_payments.completed > 0 
                                    ORDER BY amember_payments.expire_date DESC LIMIT 1)         
            AND IFNULL(u.unsubscribed,0) = 0