[SOLVED] Multiple emails being sent as duplicates

[Pedro Lima]

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 December 18, 2019 by Pedro Lima
SOLVED (see edit history)

[EvaF]

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 December 15, 2019 by EvaF (see edit history)

[Pedro Lima]

@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.