Jump to content

[SOLVED] Multiple emails being sent as duplicates


Pedro Lima

Recommended Posts

So I have this code that, for some reason, happens to send repeated emails. Sometimes it sends out 2, other times 3, 4, 5 repeated emails at once for the very same customer, I simply don't know what's happening.

I have already tried so many things and nothing seems to work out. :(

Can someone figure out something here?

private function cancelledCart($count = false)
    {
        $CemailControl = 0;
        $emailControl = 0;
        $email_logs = $this->getLogsEmail(1);
        $sql = 'SELECT c.id_cart, c.id_lang, cu.id_customer, c.id_shop, cu.firstname, cu.lastname, cu.email, ad.phone_mobile
        FROM '._DB_PREFIX_.'cart c
        LEFT JOIN '._DB_PREFIX_.'orders o ON (o.id_cart = c.id_cart)
        LEFT JOIN '._DB_PREFIX_.'order_history oh ON (oh.id_order = o.id_order)
		LEFT JOIN '._DB_PREFIX_.'address ad ON (c.id_customer = ad.id_customer)
        RIGHT JOIN '._DB_PREFIX_.'customer cu ON (cu.id_customer = c.id_customer)
        RIGHT JOIN '._DB_PREFIX_.'cart_product cp ON (cp.id_cart = c.id_cart)
            WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) <= c.date_add
            AND o.valid = 0
            AND oh.id_order_state = "6"
            AND cu.newsletter = 1';

        $sql .= Shop::addSqlRestriction(Shop::SHARE_CUSTOMER, 'c');

        if (!empty($email_logs))
            $sql .= ' AND c.id_cart NOT IN ('.join(',', $email_logs).') GROUP BY cu.email';

        $emails = Db::getInstance()->executeS($sql);

        $conf = Configuration::getMultiple(array('REMARKETING_AMOUNT_1', 'REMARKETING_DAYS_1', 'REMARKETING_ENABLE_11', 'REMARKETING_CUSTOMER_ID_1', 'REMARKETING_EMAIL_TYPE_MANUAL'));

        if ($conf['REMARKETING_ENABLE_11'] && $conf['REMARKETING_EMAIL_TYPE_MANUAL'] == 'cancelled') {

        	$cids = $conf['REMARKETING_CUSTOMER_ID_1'];
        	$sql = 'SELECT c.id_customer, c.firstname, c.lastname, c.email, c.id_lang, ad.phone_mobile
				FROM '._DB_PREFIX_.'customer c
				LEFT JOIN '._DB_PREFIX_.'address ad ON (c.id_customer = ad.id_customer)
				WHERE c.id_customer IN ('.$cids.')';
        	$customers = Db::getInstance()->executeS($sql);

        	foreach ($customers as $customer)
        	{
                if ($CemailControl !== $customer['email']) {
                    $voucher = $this->createDiscount(1, (float)$conf['REMARKETING_AMOUNT_1'], (int)$customer['id_customer'], strftime('%Y-%m-%d', strtotime('+'.(int)$conf['REMARKETING_DAYS_1'].' day')), $this->l('Discount for cancelled orders'));
                    if ($voucher !== false)
                    {
                        $CemailControl = $customer['email'];
                        $baseURL = Context::getContext()->shop->getBaseURL(true);
                        $template_vars = array(
                            '{base_url}'    => $baseURL,
                            '{email}'       => $customer['email'],
                            '{lastname}'    => $customer['lastname'],
                            '{firstname}'   => $customer['firstname'],
                            '{amount}'      => $conf['REMARKETING_AMOUNT_1'],
                            '{days}'        => $conf['REMARKETING_DAYS_1'],
                            '{voucher_num}' => $voucher->code
                        );
                        Mail::Send((int)$customer['id_lang'], 'remarketing_1', Mail::l('Discount coupon for your next order', (int)$customer['id_lang']), $template_vars, $customer['email'], $customer['firstname'].' '.$customer['lastname'], null, null, null, null, dirname(__FILE__).'/mails/');
                        if ((int)Configuration::get('REMARKETING_ENABLE_0') == '1' && $customer['phone_mobile'] != '')
                            $this->send_message(Configuration::get('REMARKETING_SMS_COUNTRY_CODE').$customer['phone_mobile'], Tools::substr(Configuration::get('PS_SHOP_NAME'), 0, 40), sprintf($this->l('Use this coupon on your next order: %d'), $voucher->code));
                    }
                }
        	}
        }

        if ($count || !count($emails))
            return count($emails);

        foreach ($emails as $email)
        {
            if ($email['order_canceled'] == '6' && $emailControl !== $email['email'])
            {
                $emailControl = $email['email'];
                $voucher = $this->createDiscount(1, (float)$conf['REMARKETING_AMOUNT_1'], (int)$email['id_customer'], strftime('%Y-%m-%d', strtotime('+'.(int)$conf['REMARKETING_DAYS_1'].' day')), $this->l('Discount for cancelled orders'));
                if ($voucher !== false)
                {
                    $baseURL = Context::getContext()->shop->getBaseURL(true);
        			$template_vars = array(
                        '{base_url}'    => $baseURL,
                        '{email}'       => $email['email'],
                        '{lastname}'    => $email['lastname'],
                        '{firstname}'   => $email['firstname'],
                        '{amount}'      => $conf['REMARKETING_AMOUNT_1'],
                        '{days}'        => $conf['REMARKETING_DAYS_1'],
                        '{voucher_num}' => $voucher->code
                    );
                    Mail::Send((int)$email['id_lang'], 'remarketing_1', Mail::l('Discount coupon for your next order', (int)$email['id_lang']), $template_vars, $email['email'], $email['firstname'].' '.$email['lastname'], null, null, null, null, dirname(__FILE__).'/mails/');
                    $this->logEmail(1, (int)$voucher->id, (int)$email['id_customer'], (int)$email['id_cart'], 0);
                    if ((int)Configuration::get('REMARKETING_ENABLE_0') == '1' && $email['phone_mobile'] != '')
                    	$this->send_message(Configuration::get('REMARKETING_SMS_COUNTRY_CODE').$email['phone_mobile'], Tools::substr(Configuration::get('PS_SHOP_NAME'), 0, 40), sprintf($this->l('Use this coupon on your next order: %d'), $voucher->code));
                }
            }
        }
    }

Thank you in advance for any help given.

Edited by Pedro Lima
SOLVED (see edit history)
Link to comment
Share on other sites

I think that the potencial problem could be in your sql. I would modify query like as
(added DISTINCT and removed ps_cart_product join)

 $sql = 'SELECT DISTINCT c.id_cart, c.id_lang, cu.id_customer, c.id_shop, cu.firstname, cu.lastname, cu.email, ad.phone_mobile
        FROM '._DB_PREFIX_.'cart c
        LEFT JOIN '._DB_PREFIX_.'orders o ON (o.id_cart = c.id_cart)
        LEFT JOIN '._DB_PREFIX_.'order_history oh ON (oh.id_order = o.id_order)
		LEFT JOIN '._DB_PREFIX_.'address ad ON (c.id_customer = ad.id_customer)
        RIGHT JOIN '._DB_PREFIX_.'customer cu ON (cu.id_customer = c.id_customer)
//        RIGHT JOIN '._DB_PREFIX_.'cart_product cp ON (cp.id_cart = c.id_cart) 
//        Why?? cp is not used in SELECT neither in WHERE
            WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) <= c.date_add
            AND o.valid = 0
            AND oh.id_order_state = "6"
            AND cu.newsletter = 1';

 

Edited by EvaF (see edit history)
  • Like 1
Link to comment
Share on other sites

@EvaF thank you for your kind help.

Unfortunately, this is not the problem because if this was the problem, it would be "blocked" by the verification of the control cariable $emailControl that I created to avoid multiple emails being sent on one iteration inside foreach loop.

So even if there was more than one repeated email associated to those variables, it would actually never enter inside that "if" condition and would simply "jump" that one.

BUT... and this is the big question actually!

Repeated emails WOULD go inside the loop hence they are not ordered. This means that if the same email was on result 1 and 3 but not in 2, it would send it anyway because the control variable is not an array of emails but instead a simple variable.

So the solution here was actually quite simple to avoid this problem: "ORDER BY cu.email" right in the end of the query. :)

Anyway, thank you for your input once again because thanks to you, I was forced to think outside of what I had here and found the possible solution for the problem.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...