Sample Expiration Warning Queries for PMPro

sample_expiration_warning_queries_for_pmpro.sql

#Check from Core PMPro
SELECT DISTINCT
    mu.user_id,
    mu.membership_id,
    mu.startdate,
    mu.enddate,
    um.meta_value AS notice 			  
FROM wp_pmpro_memberships_users AS mu
  LEFT JOIN wp_usermeta AS um ON um.user_id = mu.user_id
        AND um.meta_key = 'pmpro_expiration_notice'
WHERE ( um.meta_value IS NULL OR DATE_ADD(um.meta_value, INTERVAL 7 DAY) < '2016-11-08 00:00:00' )  
  AND ( mu.status = 'active' )		   
    AND ( mu.enddate IS NOT NULL )
    AND ( mu.enddate <> '0000-00-00 00:00:00' )
    AND ( mu.enddate BETWEEN '2016-11-08 00:00:00' AND '2016-11-15 00:00:00' )		  
    AND ( mu.membership_id <> 0 OR mu.membership_id <> NULL )
ORDER BY mu.enddate;

#30 Day Check from PMPro Extra Expiration Warning Emails
SELECT DISTINCT
    mu.user_id,
    mu.membership_id,
    mu.startdate,
    mu.enddate,
    um.meta_value AS notice 			  
FROM wp_pmpro_memberships_users AS mu
  LEFT JOIN wp_usermeta AS um ON ( um.user_id = mu.user_id )
        AND ( um.meta_key = 'pmpro_expiration_notice_30' )
WHERE ( um.meta_value IS NULL OR DATE_ADD(um.meta_value, INTERVAL 30 DAY) < '2016-11-08 00:00:00' )  
  AND ( mu.status = 'active' )
  AND ( mu.enddate IS NOT NULL )
    AND ( mu.enddate <> '0000-00-00 00:00:00' )
    AND ( mu.enddate BETWEEN '2016-11-08 00:00:00' AND '2016-12-08 00:00:00' )
    AND ( mu.membership_id <> 0 OR mu.membership_id <> NULL )
ORDER BY mu.enddate;

#60 Day Check from PMPro Extra Expiration Warning Emails
SELECT DISTINCT
    mu.user_id,
    mu.membership_id,
    mu.startdate,
    mu.enddate,
    um.meta_value AS notice 			  
FROM wp_pmpro_memberships_users AS mu
  LEFT JOIN wp_usermeta AS um ON ( um.user_id = mu.user_id )
        AND ( um.meta_key = 'pmpro_expiration_notice_60' )
WHERE ( um.meta_value IS NULL OR DATE_ADD(um.meta_value, INTERVAL 60 DAY) < '2016-11-08 00:00:00' )  
  AND ( mu.status = 'active' )
  AND ( mu.enddate IS NOT NULL )
    AND ( mu.enddate <> '0000-00-00 00:00:00' )
    AND ( mu.enddate BETWEEN '2016-12-08 00:00:00' AND '2017-01-07 00:00:00' )
    AND ( mu.membership_id <> 0 OR mu.membership_id <> NULL )
ORDER BY mu.enddate;

#90 Day Check from PMPro Extra Expiration Warning Emails
SELECT DISTINCT
    mu.user_id,
    mu.membership_id,
    mu.startdate,
    mu.enddate,
    um.meta_value AS notice 			  
FROM wp_pmpro_memberships_users AS mu
  LEFT JOIN wp_usermeta AS um ON ( um.user_id = mu.user_id )
        AND ( um.meta_key = 'pmpro_expiration_notice_90' )
WHERE ( um.meta_value IS NULL OR DATE_ADD(um.meta_value, INTERVAL 90 DAY) < '2016-11-08 00:00:00' )  
  AND ( mu.status = 'active' )
  AND ( mu.enddate IS NOT NULL )
    AND ( mu.enddate <> '0000-00-00 00:00:00' )
    AND ( mu.enddate BETWEEN '2017-01-07 00:00:00' AND '2017-02-06 00:00:00' )
    AND ( mu.membership_id <> 0 OR mu.membership_id <> NULL )
ORDER BY mu.enddate;