EmailSubscriber.php

<?php

namespace MauticPlugin\MauticAdvancedTemplatesBundle\EventListener;
use Mautic\CampaignBundle\Entity\Lead;
use Mautic\CoreBundle\EventListener\CommonSubscriber;
use Mautic\EmailBundle\EmailEvents;
use Mautic\EmailBundle\Event as Events;
use Mautic\EmailBundle\Helper\PlainTextHelper;
use Mautic\CoreBundle\Exception as MauticException;
use MauticPlugin\MauticAdvancedTemplatesBundle\Helper\TemplateProcessor;
use Psr\Log\LoggerInterface;

use Symfony\Component\Dotenv\Dotenv;

/**
 * Class EmailSubscriber.
 */
class EmailSubscriber extends CommonSubscriber
{
    /**
     * @var TokenHelper $tokenHelper ;
     */
    protected $templateProcessor;

    protected $conn = null;
    /**
     * EmailSubscriber constructor.
     *
     * @param TokenHelper $tokenHelper
     */
    public function __construct(TemplateProcessor $templateProcessor)
    {
        $this->templateProcessor = $templateProcessor;
    }
    /**
     * @return array
     */
    public static function getSubscribedEvents()
    {
        return [
            EmailEvents::EMAIL_ON_SEND => ['onEmailGenerate', 0],
            EmailEvents::EMAIL_ON_DISPLAY => ['onEmailGenerate', 0]
        ];
    }

    /**
     * Search and replace tokens with content
     *
     * @param Events\EmailSendEvent $event
     * @throws \Throwable
     * @throws \Twig_Error_Loader
     * @throws \Twig_Error_Syntax
     */
    public function onEmailGenerate(Events\EmailSendEvent $event)
    {
        $this->logger->info('onEmailGenerate MauticAdvancedTemplatesBundle\EmailSubscriber');
        $content = $event->getContent();

        $lead = $this->addLeadData($event);

        $content = $this->templateProcessor->processTemplate($content, $lead);
        $event->setContent($content);
        if ( empty( trim($event->getPlainText()) ) ) {
            $event->setPlainText( (new PlainTextHelper($content))->getText() );
        }
    }

    public function addLeadData($event) {
        $order_id = null;

        $tokens = $event->getTokens();

        $lead = $event->getLead();

        if(!empty($tokens)) {
            if(isset($tokens['{order_id}'])) {
                $order_id = $tokens['{order_id}'];

                if($order_id) {
                    $this->connectHomeperks();

                    $order = $this->getOrder($order_id);
                    $lead['order'] = json_encode($order);

                    if($order) {
                        $lead['order_items'] = $this->getOrderItems($order_id);
                        $lead['order_details'] = $this->getOrderDetails($order_id);
                        $lead['customer'] = $this->getCustomer($order['customer_id']);
                        $lead['address'] = $this->getAddress($order['customer_id']);
                        $lead['phone'] = $this->getPhone($order['customer_id']);
                        $lead['email'] = $this->getEmail($order['customer_id']);
                        $lead['style_cobrand'] = $this->getStyleCobrand($order['cobrand']);
                        $lead['style_providers'] = $this->getStyleProviders();
                    }
                }
            }
        }

        return $lead;

    }

    public function connectHomeperks() {
        $dotenv = new Dotenv();
        $dotenv->load(__DIR__.'/../../../.env');

        $link = mysqli_connect($_ENV['DB_HOST'], $_ENV['DB_USERNAME'], $_ENV['DB_PASSWORD'], $_ENV['DB_DATABASE']);

        if($link)
            $this->conn = $link;
        else
            $this->conn = false;
    }

    public function getOrder($order_id) {
        $order_id = mysqli_real_escape_string($this->conn, $order_id);

        $res = mysqli_query($this->conn, "SELECT * FROM orders WHERE id = " .$order_id);

        $order = mysqli_fetch_assoc($res);

        return $order;
    }

    public function getOrderItems($order_id) {
        $sql = mysqli_query($this->conn, "
        SELECT * FROM order_items
        WHERE order_id = $order_id");

        $order_items = mysqli_fetch_all($sql, MYSQLI_ASSOC);

        return json_encode($order_items);
    }

    public function getOrderDetails($order_id) {
        $sql = mysqli_query($this->conn, "
        SELECT oid.*, CONCAT(oid.message_1, oid.message_2, oid.message_3, oid.message_4, oid.message_5) AS message, CONCAT(oid.summary_1, oid.summary_2, oid.summary_3, oid.summary_4, oid.summary_5) AS summary FROM order_items oi
        LEFT JOIN order_item_details oid
        ON oid.order_item_id = oi.id
        WHERE oi.order_id = " . $order_id);

        $order_item_details = mysqli_fetch_all($sql, MYSQLI_ASSOC);

        return json_encode($order_item_details);
    }

    public function getStyleCobrand($cobrand) {
        $sql = mysqli_query($this->conn, "SELECT * FROM style_cobrands WHERE cobrand = '" . mysqli_real_escape_string($this->conn, $cobrand) . "'");

        $style = mysqli_fetch_assoc($sql);

        return json_encode($style);
    }

    public function getStyleProviders() {
        $result = mysqli_query($this->conn, "SELECT * FROM style_providers");

        $arr = [];
        while($row = mysqli_fetch_assoc($result)) {
            $arr[$row['slug']] = $row;
        }

        return json_encode($arr);
    }

    public function getCustomer($customer_id) {
        $sql = mysqli_query($this->conn, "SELECT * FROM customers WHERE id = " . $customer_id);

        $customer = mysqli_fetch_assoc($sql);

        return json_encode($customer);
    }

    public function getPhone($customer_id) {
        $sql = mysqli_query($this->conn, "SELECT * FROM phones WHERE id = " . $customer_id);

        $phone = mysqli_fetch_assoc($sql);

        return json_encode($phone);
    }

    public function getAddress($customer_id) {
        $sql = mysqli_query($this->conn, "SELECT * FROM addresses WHERE customer_id = " . $customer_id);

        $address = mysqli_fetch_assoc($sql);

        return $address;
    }

    public function getEmail($customer_id) {
        $sql = mysqli_query($this->conn, "SELECT * FROM emails WHERE customer_id = " . $customer_id);

        $email = mysqli_fetch_assoc($sql);

        return json_encode($email);
    }
}