gistfile1.sql

DROP PROCEDURE IF EXISTS mailing_prevent_resend;

DELIMITER //

CREATE PROCEDURE mailing_prevent_resend
(target_mailing_id INT, all_audience_user_id INT, prevent_resend BOOLEAN, target_promotion_id INT, force_reconfirm BOOLEAN)

BEGIN

DROP TEMPORARY TABLE IF EXISTS resulting;

IF all_audience_user_id IS NULL THEN

	DROP TEMPORARY TABLE IF EXISTS promotion_attempt_emails;
	CREATE TEMPORARY TABLE promotion_attempt_emails
	SELECT audience_members.email, audience_members.id AS audience_member_id
	FROM audience_members
	INNER JOIN promotion_attempts ON promotion_attempts.audience_member_id = audience_members.id
	WHERE (force_reconfirm = 0 OR (force_reconfirm = 1 AND (audience_members.confirmed IS NULL OR audience_members.confirmed = 2)))
	AND (audience_members.suppressed IS NULL OR audience_members.suppressed = 0)
	AND promotion_attempts.mailing_id = target_mailing_id;

	DROP TEMPORARY TABLE IF EXISTS audience_list_emails;
	CREATE TEMPORARY TABLE audience_list_emails
	SELECT audience_members.email, audience_members.id AS audience_member_id
	FROM audience_members
	INNER JOIN memberships ON memberships.audience_member_id = audience_members.id
	INNER JOIN audience_lists_mailings ON audience_lists_mailings.audience_list_id = memberships.audience_list_id
  WHERE (force_reconfirm = 0 OR (force_reconfirm = 1 AND (audience_members.confirmed IS NULL OR audience_members.confirmed = 2)))
	AND (audience_members.suppressed IS NULL OR audience_members.suppressed = 0)
	AND audience_lists_mailings.mailing_id = target_mailing_id;

	CREATE TEMPORARY TABLE resulting
	SELECT email, audience_member_id FROM promotion_attempt_emails;

	INSERT INTO resulting
	SELECT email, audience_member_id FROM audience_list_emails
	ON DUPLICATE KEY UPDATE resulting.email = audience_list_emails.email;

ELSE

	CREATE TEMPORARY TABLE resulting
	SELECT email, id AS audience_member_id
    FROM audience_members 
	  WHERE (force_reconfirm = 0 OR (force_reconfirm = 1 AND (audience_members.confirmed IS NULL OR audience_members.confirmed = 2)))
    AND (suppressed IS NULL OR suppressed = 0)
    AND user_id = all_audience_user_id;

END IF;

IF prevent_resend = 1 THEN

	DROP TEMPORARY TABLE IF EXISTS excluded_emails;
	CREATE TEMPORARY TABLE excluded_emails
	SELECT promotion_attempts.email
	FROM promotion_attempts
	INNER JOIN mailings ON mailings.id = promotion_attempts.mailing_id
	WHERE promotion_attempts.state_id IN (20, 30, 40, 50, 60, 70, 80, 90, 100, 110)
	AND mailings.promotion_id = target_promotion_id;

	CREATE INDEX email_on_excluded ON excluded_emails(email);
	CREATE INDEX email_on_resulting ON resulting(email);
	
	DELETE resulting FROM resulting, excluded_emails WHERE excluded_emails.email = resulting.email;
END IF;

-- callers can now use the 'resulting' table

END;
//

DELIMITER ;