Email to specific expired users

Discussion in 'Customization & add-ons' started by richie, Sep 9, 2008.

  1. richie

    richie New Member

    Joined:
    Aug 22, 2006
    Messages:
    12
    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
  2. richie

    richie New Member

    Joined:
    Aug 22, 2006
    Messages:
    12
    Any ideas on this anyone?
  3. skippybosco

    skippybosco CGI-Central Partner Staff Member

    Joined:
    Aug 22, 2006
    Messages:
    2,526
    The information is there, programmatic wise it is doable..

    Comes down to how often you see yourself emailing them. If just once or twice, perhaps a filtered export is a quick solution?
  4. davidm1

    davidm1 aMember User & Partner

    Joined:
    May 16, 2006
    Messages:
    4,437
    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
  5. deafdavid

    deafdavid Member

    Joined:
    Aug 28, 2006
    Messages:
    153
    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
  6. skippybosco

    skippybosco CGI-Central Partner Staff Member

    Joined:
    Aug 22, 2006
    Messages:
    2,526
    DeafDavid,

    You can export out contacts to a .csv file based on a set of filters that should get you very close to what you want (there is an option to export "expired" users only for all products or specific products and by date range).

    In Outlook you can import the .csv.

    Don't have Outlook handy, but I know that Microsoft Word does support mail merge.
  7. richie

    richie New Member

    Joined:
    Aug 22, 2006
    Messages:
    12
    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) > || count($expired_product_ids) > 0){
                
    $where_active "";
                
    $where_expired "";

  8. davidm1

    davidm1 aMember User & Partner

    Joined:
    May 16, 2006
    Messages:
    4,437
    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
  9. richie

    richie New Member

    Joined:
    Aug 22, 2006
    Messages:
    12
    Hmm... thanks for pointing that out. I'll take another look later today and post some revised code.
  10. richie

    richie New Member

    Joined:
    Aug 22, 2006
    Messages:
    12
    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.
  11. deafdavid

    deafdavid Member

    Joined:
    Aug 28, 2006
    Messages:
    153
    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).
  12. skippybosco

    skippybosco CGI-Central Partner Staff Member

    Joined:
    Aug 22, 2006
    Messages:
    2,526
    It could, if you don't mind the functionality living in a strange location in the Admin-CP.

    When you create a plugin there is what runs for various hooks (new user, new product, login, etc) and what runs on the configuration page for the plugin. In theory you could code a full suite of functionality on the configuration tab to do just about anything you want.

    Where this gets a bit tricky is that there is not the notion of multi-page so you would either need to have everything settable and runnable from that one page or do post backs (ajax) for submitting and fetching without leaving the page.

    Another option may be within the reporting function of the Admin CP. I've not dug to deep into what is possible for developed reports, but if they let you define UIs and controls this may be a more logical place?
  13. richie

    richie New Member

    Joined:
    Aug 22, 2006
    Messages:
    12
    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 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 
                                        ORDER BY amember_payments
    .expire_date DESC LIMIT 1)         
                AND 
    IFNULL(u.unsubscribed,0) = 0

Share This Page