<?php
// Copyright (c) Pickware GmbH. All rights reserved.
// This file is part of software that is released under a proprietary license.
// You must not copy, modify, distribute, make publicly available, or execute
// its contents or parts thereof without express permission by the copyright
// holder, unless otherwise permitted by law.

namespace Shopware\Plugins\ViisonShippingCommon;

use Doctrine\ORM\EntityManager;
use Doctrine\ORM\Query;
use Doctrine\ORM\Query\Expr\Join;
use Shopware\Components\Model\ModelManager;
use Shopware\Components\Model\ModelRepository;
use Shopware\Models\Shop\Shop;
use Shopware\Plugins\ViisonCommon\Components\FileStorage\FileStorage;
use Shopware\Plugins\ViisonShippingCommon\Classes\PluginInfo;
use VIISON\AddressSplitter\AddressSplitter;
use VIISON\AddressSplitter\Exceptions\SplittingException;

/**
 * Contains utility methods and values which are useful for multiple dispatch service provider plugins.
 */
abstract class Util
{
    /**
     * @var PluginInfo
     */
    protected $pluginInfo;

    private static $euCountryCodes = array(
        'BE',
        'BG',
        'CZ',
        'DK',
        'DE',
        'EE',
        'IE',
        'EL',
        'ES',
        'FR',
        'HR',
        'IT',
        'CY',
        'LV',
        'LT',
        'LU',
        'HU',
        'MT',
        'NL',
        'AT',
        'PL',
        'PT',
        'RO',
        'SI',
        'SK',
        'FI',
        'SE',
        'UK',
        'GB' // The Official iso code is UK or GB
    );

    /**
     * @var array An associative array containing the product IDs as keys and the respective product data (array) as
     *            values of the implementing plugin.
     */
    protected $products;

    /**
     * @var array[] An array of all product mappings of the implementing plugin.
     */
    protected $productMappings;

    /**
     * @param PluginInfo $pluginInfo Provides plugin specific information.
     */
    protected function __construct(PluginInfo $pluginInfo)
    {
        $this->pluginInfo = $pluginInfo;
    }

    /**
     * Returns the default shipment weight for the given product.
     *
     * @param int $shopId
     * @param array $product
     * @return float
     */
    abstract public function getDefaultShipmentWeight($shopId, $product);

    /**
     * Returns the default shipment weight if no product exists for the order.
     * @param int $shopId
     * @return float
     */
    public function getDefaultShipmentWeightWithoutProduct($shopId)
    {
        return 1.0;
    }

    /**
     * Returns an array containing the plugin specific product fields and their respective values for the given product table row.
     *
     * @param int $shopId
     * @param array $product
     * @return array
     */
    abstract protected function getExtraProductFields($shopId, $product);

    /**
     * Returns the string to be used for password encryption.
     *
     * @return string|null
     */
    abstract protected function getKey();

    /**
     * Returns an array containing all config fields that are always needed for label creation
     *
     * @return array
     */
    abstract protected function getMandatoryConfigFields();

    /**
     * Returns if the config contains all fields necessary to create labels for the given order.
     *
     * @param int $orderId
     * @param array $config
     * @return bool
     */
    abstract protected function hasOrderSpecificValidConfig($orderId, $config);

    /**
     * @return string
     */
    public function getEncryptionKey()
    {
        return $this->getKey();
    }

    /**
     * Returns the plugin specific information that was passed to the class in the constructor.
     *
     * @return PluginInfo
     */
    public function getPluginInfo()
    {
        return $this->pluginInfo;
    }

    /**
     * If no products have been loaded before, the plugin info's product table is fetched and the results are stored
     * in an associative array to speed up repeaded calls to this method. Finally the stored products are returned.
     *
     * @return array
     */
    public function getProducts()
    {
        if ($this->products === null) {
            $productTable = new \Zend_Db_Table($this->pluginInfo->getProductTableName());
            $products = Shopware()->Db()->fetchAll(
                $productTable->select()
            );
            $this->products = array();
            foreach ($products as $product) {
                $this->products[$product['id']] = $product;
            }
        }

        return $this->products;
    }

    /**
     * If no product mappings have been loaded before, the plugin info's product mapping table is fetched and the
     * results are stored in an array to speed up repeaded calls to this method. Finally the stored product mappings are
     * returned.
     *
     * @return array[]
     */
    public function getProductMappings()
    {
        if ($this->productMappings === null) {
            $productMappingTable = new \Zend_Db_Table($this->pluginInfo->getProductMappingTableName());
            $this->productMappings = Shopware()->Db()->fetchAll(
                $productMappingTable->select()
            );
        }

        return $this->productMappings;
    }

    /**
     * Tries to find and return a product mapping for the given $dispatchId.
     *
     * @param int $dispatchId
     * @return array|null
     */
    public function findProductMapping($dispatchId)
    {
        return array_shift(array_filter($this->getProductMappings(), function ($mapping) use ($dispatchId) {
            return $mapping['dispatchId'] == $dispatchId;
        }));
    }

    /**
     * Returns the configuration value set for the given key from the configuration of the shop
     * with the given id.
     *
     * @param int $shopId The id of the shop from whose configuration the value shall be extracted.
     * @param string $key The key of the configuration value to extract.
     * @param bool $noException Determine if you want to raise a exception if config key no exist
     * @return string|null The shops configuration value set for the given key or the default, if none matched.
     */
    public function config($shopId, $key = null, $noException = false)
    {
        $modelManager = Shopware()->Container()->get('models');
        $shop = $modelManager->find('Shopware\Models\Shop\Shop', $shopId);
        if ($shop === null) {
            throw new \RuntimeException('Configuration for shopId ' . $shopId . ' could not be loaded');
        }
        $shopWithValidConfiguration = $this->findShopWithValidConfiguration($shop);
        if (!$shopWithValidConfiguration) {
            // No fallback available
            return null;
        }

        $table = new \Zend_Db_Table($this->pluginInfo->getConfigTableName());
        $config = $table->fetchRow($table->select()->where(
            'shopId = ?',
            $shopWithValidConfiguration->getId()
        ))->toArray();
        if (!array_key_exists($key, $config)) {
            if ($noException) {
                return false;
            } else {
                throw new \InvalidArgumentException(
                    'Configuration for shopId ' . $shopWithValidConfiguration->getId() .
                    ' does not contain the key \'' . $key . '\''
                );
            }
        }

        return $config[$key];
    }

    /**
     * Fetches the complete configuration for the originating shop of the given order id
     * and checks, if all mandatory fields ar not empty.
     *
     * @param int $orderId The id of the order whose configuration shall be checked.
     * @return bool True, if all mandatory configuration fields are not empty. Otherwise false.
     * @throws \Exception
     */
    public function hasValidConfig($orderId)
    {
        $shopId = $this->originatingShop($orderId);
        if ($shopId === null) {
            return false;
        }
        $modelManager = Shopware()->Container()->get('models');
        $shop = $modelManager->find('Shopware\Models\Shop\Shop', $shopId);
        if (!$shop) {
            return false;
        }
        $shopWithValidConfiguration = $this->findShopWithValidConfiguration($shop);
        if (!$shopWithValidConfiguration) {
            $missingConfigFields = $this->shopHasValidConfig($shopId, true);
            $exceptionMessage = $this->composeErrorMessageForNoValidConf($shopId, $missingConfigFields);

            throw new \Exception($exceptionMessage);
        }

        $table = new \Zend_Db_Table($this->pluginInfo->getConfigTableName());
        $config = $table->fetchRow($table->select()->where('shopId = ?', $shopWithValidConfiguration->getId()));

        return $this->hasOrderSpecificValidConfig($orderId, $config->toArray());
    }

    /**
     * Returns the active language of the shop
     *
     * @return string Active language, can be 'de', 'ch' or 'at'
     */
    public function getShopLanguage()
    {
        $germanLanguage = array('de', 'at', 'ch');

        return in_array(Shopware()->Container()->get('locale')->getLanguage(), $germanLanguage) ? 'de' : 'eng';
    }

    public function getStateNameViaStateIso($stateIso)
    {
        if (empty($stateIso)) {
            return '';
        }

        try {
            /** @var \Shopware\Models\Country\State $state */
            $state = Shopware()->Container()->get('models')->getRepository(
                'Shopware\Models\Country\State'
            )->findOneByShortCode($stateIso);

            return $state->getName() ?: '';

            // Don't throw a exception so the User can create a Label,
            // but write the error in the Log file
        } catch (\Exception $e) {
            $this->log($e->getMessage());
        }
    }

    /**
     * Returns the id of the default subshop:
     *
     * @return int|null The default subshop id or null, if none matched.
     */
    public function defaultShop()
    {
        /** @var \Shopware\Models\Shop $shop */
        $shop = Shopware()->Container()->get('models')->getRepository(
            'Shopware\Models\Shop\Shop'
        )->findOneByDefault(true);

        if (empty($shop)) {
            return null;
        }

        return $shop->getId();
    }

    /**
     * Returns the id of the subshop, where the order with the given id was made. If $orderId is
     * zero or null, the default subshop is returned.
     *
     * @param int $orderId The id of the order whose originating subshop id shall be determined.
     * @return int|null The found subshop id or null, if none matched.
     */
    public function originatingShop($orderId)
    {
        if (is_null($orderId) || $orderId == 0) {
            return $this->defaultShop();
        }

        /** @var \Shopware\Models\Order\Order $order */
        $order = Shopware()->Container()->get('models')->getRepository(
            'Shopware\Models\Order\Order'
        )->findOneById($orderId);

        if (empty($order)) {
            return null;
        }

        return $order->getShop()->getId();
    }

    /**
     *  Return Sub Shop name of the given Order ID. if Name is not found return empty string.
     *
     * @param string $orderId
     * @return string
     */
    public function getOriginatingShopName($orderId)
    {
        $subShopId = $this->originatingShop($orderId);

        if (is_null($subShopId) || $subShopId == 0) {
            return '';
        }

        return $this->getShopNameFromId($subShopId);
    }

    /**
     * Get shop name from ID
     *
     * @param null $shopId
     * @return string
     */
    public function getShopNameFromId($shopId = null)
    {
        if ($shopId === null) {
            $this->log('Invalid shop ID');

            return '';
        }

        /** @var \Shopware\Models\Shop $shop */
        $shop = Shopware()->Container()->get('models')->getRepository(
            'Shopware\Models\Shop\Shop'
        )->find($shopId);

        return ($shop) ? $shop->getName() : '';
    }

    /**
     * Get snippet manager from namespace
     *
     * Namespace example: backend/viison_plugin_something/exception
     *
     * @param null $namespace
     * @return mixed
     */
    public function getSnippetHandlerFromNamespace($namespace = null)
    {
        return Shopware()->Container()->get('snippets')->getNamespace($namespace);
    }


    /**
     * Returns the id of the subshop, where the order corresponding to the given tracking code was made.
     *
     * @param string $trackingCode The tracking code of the shipment.
     * @return int|null The found subshop id or null, if none matched.
     */
    public function originatingShopOfTrackingCode($trackingCode)
    {
        // TODO will be refactored with the adapter implementation
        $orderTable = new \Zend_Db_Table($this->pluginInfo->getOrderTableName());
        $order = $orderTable->fetchRow($orderTable->select()->from($orderTable, 'orderId')->where('trackingcode LIKE ?', $trackingCode));

        if (empty($order)) {
            return null;
        }

        return $this->originatingShop($order->orderId);
    }

    /**
     * Returns the id of the subshop, where the order corresponding to the given tracking code was made.
     *
     * @param string $documentIdentifier The tracking code of the shipment.
     * @return int|null The found subshop id or null, if none matched.
     */
    public function originatingShopOfDocumentIdentifier($documentIdentifier)
    {
        // TODO will be removed after DHL refactroing
        $orderTable = new \Zend_Db_Table($this->pluginInfo->getOrderTableName());
        $order = $orderTable->fetchRow($orderTable->select()->from($orderTable, 'orderId')->where('url LIKE ? ', '%' . $documentIdentifier));

        if (empty($order)) {
            // Try as Fallback with tracking code
            $orderId = $this->originatingShopOfTrackingCode($documentIdentifier);

            return $orderId;
        }

        return $this->originatingShop($order->orderId);
    }

    /**
     * Gets and returns the order with the given id from the database.
     *
     * @param int $orderId
     * @return array|null The order corresponding to the given id or null, if none matched.
     * @throws \Exception
     */
    public function getOrder($orderId)
    {
        $result = Shopware()->Db()->fetchAll(
            'SELECT
                s_order.trackingcode AS trackingcode,
                s_order_shippingaddress.company AS company,
                s_order_shippingaddress.department AS department,
                s_order_shippingaddress.salutation AS salutation,
                s_order_shippingaddress.firstname AS firstname,
                s_order_shippingaddress.lastname AS lastname,
                s_order_shippingaddress.street AS street,
                s_order_shippingaddress.zipcode AS zipcode,
                s_order_shippingaddress.city AS city,
                s_core_countries.countryiso AS countryiso,
                s_order_billingaddress.phone AS phone,
                s_order_billingaddress.ustid AS vatNumber,
                s_order.invoice_amount AS amount,
                s_order.currency AS currency,
                s_user.email AS email,
                DATE_FORMAT(s_order.ordertime, \'%Y-%m-%d\') AS orderDate,
                s_order.ordernumber AS orderNumber,
                s_order.invoice_shipping AS shippingCosts,
                s_order.customercomment AS customerComment,
                s_order_billingaddress.id AS orderBillingAddressId,
                s_order_shippingaddress.id AS orderShippingAddressId,
                s_order_shippingaddress.stateID AS stateID,
                s_core_countries_states.shortcode AS stateshortcode,
                s_core_countries.id AS countryId,
                s_user.id AS userId,
                s_user_billingaddress.id AS userBillingId
            FROM s_order
            LEFT JOIN s_order_shippingaddress
                ON s_order.id = s_order_shippingaddress.orderID
            LEFT JOIN s_core_countries_states
                ON s_order_shippingaddress.stateID = s_core_countries_states.id
            LEFT JOIN s_core_countries
                ON s_order_shippingaddress.countryID = s_core_countries.id
            LEFT JOIN s_order_billingaddress
                ON s_order.id = s_order_billingaddress.orderID
            LEFT JOIN s_user
                ON s_order.userID = s_user.id
            LEFT JOIN s_user_billingaddress
                ON s_order.userID = s_user_billingaddress.userID
            WHERE
                s_order.id = ?',
            array(
                $orderId
            )
        );
        if (count($result) !== 1) {
            return null;
        }
        $order = $result[0];
        if (is_null($order['orderBillingAddressId'])) {
            throw new \Exception(sprintf('Rechnungsadresse zu Bestellung %s fehlt', $order['orderNumber']));
        } elseif (is_null($order['orderShippingAddressId'])) {
            throw new \Exception(sprintf('Versandadresse zu Bestellung %s fehlt', $order['orderNumber']));
        } elseif (!is_null($order['stateId']) && is_null($order['stateshortcode'])) { /* State is optional, but if a non-zero id is given, it should exist */
            throw new \Exception(sprintf('Das in der Versandadresse von Bestellung %s hinterlegte Bundesland ist ungültig', $order['orderNumber']));
        } elseif (is_null($order['countryId'])) {
            throw new \Exception(sprintf('Das in der Versandadresse von Bestellung %s hinterlegte Land ist ungültig', $order['orderNumber']));
        } elseif ($order['userId'] === null) {
            $order['email'] = '';
        }

        // For Shopware versions starting with version 5, there is no street number field anymore in the database
        if ($this->assertMinimumVersion('5')) {
            $result = Shopware()->Db()->fetchAll(
                'SELECT
                    s_order_shippingaddress.additional_address_line1,
                    s_order_shippingaddress.additional_address_line2
                FROM s_order
                INNER JOIN s_order_shippingaddress
                    ON s_order.id = s_order_shippingaddress.orderID
                WHERE
                    s_order.id = ?',
                array(
                    $orderId
                )
            );
            if (count($result) !== 1) {
                return null;
            }

            $additionalAddressData = array(
                $result[0]['additional_address_line1'],
                $result[0]['additional_address_line2']
            );

            $order['addressline1'] = $order['street'];
            $order['addressline2'] = $result[0]['additional_address_line1'];
            $order['addressline3'] = $result[0]['additional_address_line2'];

            try {
                $address = AddressSplitter::splitAddress($order['street']);
                $order['street'] = $address['streetName'];
                $order['streetnumber'] = $address['houseNumber'];
                $order['streetnumberbase'] = $address['houseNumberParts']['base'];
                $order['streetnumberextension'] = $address['houseNumberParts']['extension'];
                $additionalAddressData[] = $address['additionToAddress1'];
                $additionalAddressData[] = $address['additionToAddress2'];
            } catch (SplittingException $e) {
                $order['streetnumber'] = '';
                $order['streetnumberbase'] = '';
                $order['streetnumberextension'] = '';
            }

            $order['additionaladdressline'] = join(', ', array_filter($additionalAddressData));
        } else { /* Shopware 4 */
            $result = Shopware()->Db()->fetchAll(
                'SELECT
                    s_order_shippingaddress.streetnumber AS streetnumber
                FROM s_order
                INNER JOIN s_order_shippingaddress
                    ON s_order.id = s_order_shippingaddress.orderID
                WHERE
                    s_order.id = ?',
                array(
                    $orderId
                )
            );
            if (count($result) !== 1) {
                return null;
            }
            $order['streetnumber'] = $result[0]['streetnumber'];
            $order['addressline1'] = $order['street'] . ' ' . $order['streetnumber'];
            $order['addressline2'] = '';
            $order['addressline3'] = '';

            try {
                $houseNumberParts = AddressSplitter::splitHouseNumber($order['streetnumber']);
                $order['streetnumberbase'] = $houseNumberParts['base'];
                $order['streetnumberextension'] = $houseNumberParts['extension'];
            } catch (SplittingException $e) {
                $order['streetnumberbase'] = '';
                $order['streetnumberextension'] = '';
            }
        }

        if ($order['userId'] !== null) {
            $order['customerNumber'] = $this->getCustomerNumberForOrder($orderId);
        } else {
            $order['customerNumber'] = null;
        }

        return $order;
    }

    /**
     * Gets and returns the billing address for the given order id from the database.
     *
     * @param int $orderId
     * @return array|null The billing address corresponding to the given id or null, if no order with the given id exists.
     * @throws \Exception
     */
    public function getBillingAddress($orderId)
    {
        $result = Shopware()->Db()->fetchAll(
            'SELECT
                s_order_billingaddress.company AS company,
                s_order_billingaddress.department AS department,
                s_order_billingaddress.salutation AS salutation,
                s_order_billingaddress.firstname AS firstname,
                s_order_billingaddress.lastname AS lastname,
                s_order_billingaddress.street AS street,
                s_order_billingaddress.zipcode AS zipcode,
                s_order_billingaddress.city AS city,
                s_core_countries.countryiso AS countryiso,
                s_order_billingaddress.phone AS phone,
                s_order_billingaddress.ustid as vatNumber,
                s_user.email AS email,
                s_order.ordernumber AS orderNumber,
                s_order_billingaddress.id AS orderBillingAddressId,
                s_core_countries.id AS countryId,
                s_user.id AS userId
             FROM s_order
             INNER JOIN s_order_billingaddress
                ON s_order_billingaddress.orderID = s_order.id
             INNER JOIN s_core_countries
                ON s_order_billingaddress.countryID = s_core_countries.id
             INNER JOIN s_user
                ON s_order.userID = s_user.id
             WHERE
                s_order.id = ?',
            array(
                $orderId
            )
        );
        if (count($result) !== 1) {
            return null;
        }
        $billingAddress = $result[0];

        if (is_null($billingAddress['orderBillingAddressId'])) {
            throw new \Exception(sprintf('Rechnungsadresse zu Bestellung %s fehlt', $billingAddress['orderNumber']));
        } elseif (is_null($billingAddress['countryId'])) {
            throw new \Exception(sprintf('Das in der Versandadresse von Bestellung %s hinterlegte Land ist ungültig', $billingAddress['orderNumber']));
        } elseif ($billingAddress['userId'] === null) {
            $billingAddress['email'] = '';
        }

        // For Shopware versions starting with version 5, there is no street number field anymore in the database
        if ($this->assertMinimumVersion('5')) {
            $result = Shopware()->Db()->fetchAll(
                'SELECT
                    s_order_billingaddress.additional_address_line1,
                    s_order_billingaddress.additional_address_line2
                FROM s_order
                INNER JOIN s_order_billingaddress
                    ON s_order.id = s_order_billingaddress.orderID
                WHERE
                    s_order.id = ?',
                array(
                    $orderId
                )
            );
            if (count($result) !== 1) {
                return null;
            }

            $additionalAddressData = array(
                $result[0]['additional_address_line1'],
                $result[0]['additional_address_line2']
            );

            $billingAddress['addressline1'] = $billingAddress['street'];
            $billingAddress['addressline2'] = $result[0]['additional_address_line1'];
            $billingAddress['addressline3'] = $result[0]['additional_address_line2'];

            try {
                $address = AddressSplitter::splitAddress($billingAddress['street']);
                $billingAddress['street'] = $address['streetName'];
                $billingAddress['streetnumber'] = $address['houseNumber'];
                $billingAddress['houseNumberParts'] = $address['houseNumberParts'];
                $additionalAddressData[] = $address['additionToAddress1'];
                $additionalAddressData[] = $address['additionToAddress2'];
            } catch (SplittingException $e) {
                $billingAddress['streetnumber'] = '';
            }

            $billingAddress['additionaladdressline'] = join(', ', array_filter($additionalAddressData));
        } else { /* Shopware 4 */
            $result = Shopware()->Db()->fetchAll(
                'SELECT
                    s_order_billingaddress.streetnumber AS streetnumber
                FROM s_order
                INNER JOIN s_order_billingaddress
                    ON s_order.id = s_order_billingaddress.orderID
                WHERE s_order.id = ?',
                array(
                    $orderId
                )
            );
            if (count($result) !== 1) {
                return null;
            }
            $billingAddress['streetnumber'] = $result[0]['streetnumber'];
            $billingAddress['addressline1'] = $billingAddress['street'] . ' ' . $billingAddress['streetnumber'];
            $billingAddress['addressline2'] = '';
            $billingAddress['addressline3'] = '';
        }

        return $billingAddress;
    }

    /**
     * Regex matcher, extract the Identifier from the given URL
     *
     * NOTE: Identifier can be Tracking Code, ...
     *
     * @param string $url
     * @return string
     */
    public function getIdentifierFromLabelUrl($url)
    {
        $matches = array();
        preg_match('/.*:(.*?)$/', $url, $matches);

        return $matches[1];
    }

    /**
     * Determines whether the order with the given id contains at least one item, whose weight
     * is not defined in the database.
     *
     * @param int $orderId The id of the order, whose items' weight shall be checked.
     * @return bool True, if at least one item of the order has no defined weight, otherwise false.
     */
    public function hasOrderItemsWithoutWeight($orderId)
    {
        $builder = Shopware()->Container()->get('models')->createQueryBuilder();

        $builder->select(array('o'))
            ->from('Shopware\Models\Order\Detail', 'o')
            ->leftJoin(
                'Shopware\Models\Article\Detail',
                'a',
                Join::WITH,
                'o.articleId = a.articleId AND o.articleNumber = a.number'
            )
            ->where('o.orderId = :orderId')
            ->andWhere('a.weight IS NULL') // If the weight is not defined inside s_articles (like Discount)
            ->setParameter('orderId', $orderId);

        $result = $builder->getQuery()->getResult();

        return !empty($result);
    }

    /**
     * Tries to find the ISO-2 country code for the given country id or name.
     *
     * @param int|string $countryIdOrName The id or name of the country whose ISO-2 shall be found.
     * @return string|null The ISO-2 code of the given country or null, if none matched.
     */
    public function findCountryISO($countryIdOrName)
    {
        $builder = Shopware()->Container()->get('models')->createQueryBuilder();

        $builder->select(array('country'))
            ->from('Shopware\Models\Country\Country', 'country')
            ->where('country.id = :countryIdOrName')
            ->orWhere('country.name = :countryName')
            ->orWhere('country.isoName = :countryEn')
            ->setParameters(array(
                'countryIdOrName' => $countryIdOrName,
                'countryName' => $countryIdOrName,
                'countryEn' => $countryIdOrName
            ));

        $result = $builder->getQuery()->getResult();

        if (!empty($result)) {
            return $result[0]->getIso();
        }

        return null;
    }

    /**
     * Get Country name by ISO code ord db ID.
     *
     * @param $countryIsoOrId
     * @return string
     * @throws \Exception
     */
    public function getCountryNameByIsoOrId($countryIsoOrId)
    {
        try {
            $countriesTable = new \Zend_Db_Table('s_core_countries');
            $selectBy = ($this->getShopLanguage() === 'de') ? 'countryname' : 'countryen';

            $countryName = $countriesTable->fetchRow($countriesTable->select()
                ->from($countriesTable, $selectBy)
                ->where('countryiso = ?', $countryIsoOrId)
                ->orWhere('id = ?', $countryIsoOrId));

            $countryName = ($this->getShopLanguage() === 'de') ? $countryName->countryname :  $countryName->countryen;

            return $countryName ?: '';
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage());
        }
    }

    /**
     * Logs the given message to the 'log.txt' file.
     *
     * @param string $message The message which should be logged.
     */
    public function log($message)
    {
        try {
            // Open file
            $file = dirname(__FILE__) . '/log.txt';
            $handler = fopen($file, 'a');
            // Write message
            $message = '[' . date('Y-m-d H:i:s') . '] ' . $message . "\n";
            fwrite($handler, $message);
            // Close file
            fclose($handler);
        } catch (\Exception $e) {
            // Ignore
        }
    }

    /**
     * Encrypts the given text using the defined method and key. The default method 'rijndael256' is deprecated and
     * should not be used anymore. It is only provided for backwards compatibility.
     *
     * @param string $text
     * @return string
     */
    public function encrypt($text, $method = 'rijndael256')
    {
        if (strtolower($method) == 'rijndael256') {
            return trim(base64_encode(mcrypt_encrypt(MCRYPT_RIJNDAEL_256, $this->getKey(), $text, MCRYPT_MODE_ECB, mcrypt_create_iv(mcrypt_get_iv_size(MCRYPT_RIJNDAEL_256, MCRYPT_MODE_ECB), MCRYPT_RAND))));
        } else {
            $iv = openssl_random_pseudo_bytes(openssl_cipher_iv_length($method));

            return base64_encode(openssl_encrypt($text, $method, $this->getKey(), 0, $iv) . ':' . $iv);
        }
    }

    /**
     * @deprecated
     * Decrypts the given text using the defined method and key. The default method 'rijndael256' is deprecated and
     * should not be used anymore. It is only provided for backwards compatibility.
     *
     * @param string $base64text
     * @param string $method
     * @return string
     * @throws \Exception
     */
    public function decrypt($base64text, $method = 'rijndael256')
    {
        if (strtolower($method) == 'rijndael256') {
            return trim(mcrypt_decrypt(MCRYPT_RIJNDAEL_256, $this->getKey(), base64_decode($base64text), MCRYPT_MODE_ECB, mcrypt_create_iv(mcrypt_get_iv_size(MCRYPT_RIJNDAEL_256, MCRYPT_MODE_ECB), MCRYPT_RAND)));
        } else {
            $parts = preg_split('/:/', base64_decode($base64text), 2);
            if (count($parts) != 2) {
                throw new \Exception(sprintf('Failed to identify initialization vector in encrypted config value \'%s\'', $base64text));
            }
            list($text, $iv) = $parts;
            $result = openssl_decrypt($text, $method, $this->getKey(), 0, $iv);
            if ($result === false) {
                throw new \Exception(sprintf('Decryption of encrypted config value \'%s\' with encryption method \'%s\' failed', $base64text, $method));
            }

            return $result;
        }
    }

    /**
     * Decrypts the config field with the given name according to the encryption method of the respective config row.
     *
     * @param int $shopId
     * @param string $key
     * @return string
     */
    public function decryptConfigField($shopId, $key)
    {
        $encryptedValue = $this->config($shopId, $key);
        if (empty($encryptedValue)) {
            return $encryptedValue;
        }

        // Save as array as workaround, so we can use the $this->getDecryptionMethod() method
        $config = array();
        if ($this->hasConfigField('encryptionMethod')) {
            $config['encryptionMethod'] = $this->config($shopId, 'encryptionMethod');
        }

        return $this->decrypt($encryptedValue, $this->getDecryptionMethod($config));
    }

    /**
     * Constructs a path to a file in the Shopware documents directory using the given tracking code and type.
     *
     * @deprecated Use `getDocumentFileName()` instead.
     *
     * @param string $documentIdentifier The tracking code used to construct the path.
     * @param string $docType The document type used to construct the path.
     * @param string|null $customPrefix A custom prefix that can be send instead of the default one from plugin info.
     * @return string The file path, where the document with the given tracking code and type will be stored.
     */
    public function createFilePath($documentIdentifier, $docType, $customPrefix = null)
    {
        return Shopware()->DocPath('files_' . 'documents') . ($customPrefix ?: $this->pluginInfo->getFilePrefix()) . '-' . $documentIdentifier . '-' . $docType . '.pdf';
    }

    /**
     * @param string $documentIdentifier
     * @param string $docType
     * @param string|null $customPrefix
     * @return string
     */
    public function getDocumentFileName($documentIdentifier, $docType, $customPrefix = null)
    {
        return ($customPrefix ?: $this->pluginInfo->getFilePrefix()) . '-' . $documentIdentifier . '-' . $docType . '.pdf';
    }

    /**
     * Creates an backend URL relative to the shopware base URL, which will return the
     * document with the given identifier.
     *
     * @param string $identifier The identifier of the document file, for which a URL is generated.
     * @return string The generated URL.
     */
    public function createDocumentURL($identifier)
    {
        return 'backend/' . $this->pluginInfo->getPluginName() . 'Order/getDocument/' . $identifier;
    }

    /**
     * @deprecated
     * Determines the correct product for a shipment based on the mapping created in the shipment methods
     * configuration. The result also includes dispatch level settings such as if an export document should
     * be generated (also see method getDispatchMethodLevelSettings).
     *
     * TODO Refactor code so that there are not two methods performing similar tasks. This method should probably
     * only return the product, not any settings. This would be a backwards compatibility break, because historically
     * this method provided the `exportDocumentRequired` setting.
     *
     * @param int $orderId The id of the order, whose product type should be determined.
     * @return array|null An array containing product information or null, if no valid product mapping exists.
     */
    public function getProduct($orderId)
    {
        // Determine the order's dispatch method ID
        $dispatchId = Shopware()->Db()->fetchOne(
            'SELECT dispatchID
            FROM s_order
            WHERE id = :orderId',
            array(
                'orderId' => $orderId
            )
        );
        if (!$dispatchId) {
            return null;
        }

        // Try to find a mapped product
        $productMapping = $this->findProductMapping($dispatchId);
        if (!$productMapping) {
            return null;
        }
        $allProducts = $this->getProducts();
        $product = $allProducts[$productMapping['productId']];
        if (!$product || empty($product['code'])) {
            return null;
        }

        // Determine the extra product fields
        $shopId = $this->originatingShop($orderId);
        $productResult = $this->getProductFields($shopId, $product, (bool)$productMapping['exportDocumentRequired']);

        // Remove items that aren't settings from the $productMapping result and merge them with the product data
        $result = array_merge($productResult, array_diff_key($productMapping, array_flip(
            array('id', 'dispatchId', 'productId')
        )));

        return $result;
    }

    /**
     * Loads the dispatch service provider settings made on the dispatch method level (e.g. if an export document
     * should be generated or higher insurance in the UPS adapter) for the dispatch method of the given order.
     *
     * @param $orderId
     * @return array|null
     */
    public function getDispatchMethodLevelSettings($orderId)
    {
        // Determine the order's dispatch method ID
        $dispatchId = Shopware()->Db()->fetchOne(
            'SELECT dispatchID
            FROM s_order
            WHERE id = :orderId',
            array(
                'orderId' => $orderId
            )
        );
        if (!$dispatchId) {
            return null;
        }

        // Try to find a product mapping
        $productMapping = $this->findProductMapping($dispatchId);
        if (!$productMapping) {
            return null;
        }

        // Remove items that aren't settings from the result
        $dispatchMethodLevelSettings = array_diff_key($productMapping, array_flip(
            array('id', 'dispatchId')
        ));

        foreach ($dispatchMethodLevelSettings as $key => &$value) {
            // Check if $value is an int (potentially in a string) and transform it to a real int
            if (filter_var($value, FILTER_VALIDATE_INT) !== false) {
                $value = (int) $value;
            }
        }

        return $dispatchMethodLevelSettings;
    }

    /**
     * Loads the product data for a specific product id.
     *
     * @param int $shopId The id of the order, whose product type should be determined.
     * @param int $productId The id of the product, that has been chosen.
     * @return array An array containing product information or null, if no valid product mapping exists.
     */
    public function getProductFromId($shopId, $productId)
    {
        $allProducts = $this->getProducts();
        $product = $allProducts[$productId];
        if (!$product || empty($product['code'])) {
            return null;
        }

        return $this->getProductFields($shopId, $product, null);
    }

    /**
     * Localizes the given salutation based on the given country ISO code. That is,
     * If the ISO code is referenced with a german speaking country, the salutation
     * will be translated to the same. Furthermore will all 'company' salutations be deleted.
     *
     * @param string $salutation The salutation to localize.
     * @param string $countryISO The ISO (2 chars, like DE, AT, CH) code of the destination country.
     * @param string $shopId The shop id of the active shop
     * @return string The localized salutation.
     */
    public function localizeSalutation($salutation, $countryISO, $shopId = null)
    {
        // Check if Salutation is disabled inside the Adapters
        $shopId = !is_null($shopId) ? $shopId : Shopware()->Container()->get('models')->getRepository('Shopware\Models\Shop\Shop')->getActiveDefault()->getId();
        if (!$this->config($shopId, 'isSalutationRequired', true)) {
            return '';
        }

        return self::localizeSalutationIgnoringShopConfig($salutation, $countryISO);
    }

    /**
     * Localizes a salutation
     *
     * This function does not set the salutation to an empty string when the adapter configuration is configured to do so (compare Util::localizeSalutation())
     *
     * @param $salutation
     * @param $countryISO
     * @return string
     */
    public static function localizeSalutationIgnoringShopConfig($salutation, $countryISO)
    {
        if ($salutation === 'company') {
            // Companies haven't got salutations
            return '';
        } elseif (!in_array($countryISO, array('DE', 'AT', 'CH'))) {
            // Leave salutation untouched
            return ucfirst($salutation);
        }

        // Translate salutation to german
        if ($salutation === 'mr') {
            return 'Herr';
        } elseif ($salutation === 'ms') {
            return 'Frau';
        }

        return '';
    }

    /**
     * Checks if a new label can be added to the given order.
     *
     * @param array $order
     * @param int $orderId
     * @throws Exception
     */
    public function checkIfLabelCreationIsPossible($order, $orderId)
    {
        if (empty($order)) {
            $this->log('Failed to create label.');
            $this->log('The order with id ' . $orderId . ' does not exist.');
            throw new \Exception('Die Bestellung mit der ID ' . $orderId . ' existiert nicht.');
        }

        // Check order
        if (!empty($order['trackingcode'])) {
            $codes = explode(',', $order['trackingcode']);
            if (count($codes) >= $this->pluginInfo->getMaxLabels()) {
                // Exceeded maximum number of labels per order
                $this->log('Failed to create label.');
                $this->log('Maximum number of ' . $this->pluginInfo->getMaxLabels() . ' labels exceeded.');
                throw new \Exception('Die maximal mögliche Anzahl an Etiketten für diese Bestellung ist bereits erreicht.');
            }
        }
    }

    /**
     * Concatenates the given customer information into a single string to be saved in the plugin-specific
     * order table when creating a free form label.
     * @param array $shippingDetails
     * @return string
     */
    public function buildCustomerAddress($shippingDetails)
    {
        $salutation = $this->localizeSalutation($shippingDetails['salutation'], $shippingDetails['countryiso']);
        $fullName = join(' ', array_filter(array($salutation, $shippingDetails['firstname'], $shippingDetails['lastname'])));
        $company = $shippingDetails['company'];
        $street = join(' ', array_filter(array($shippingDetails['street'], $shippingDetails['streetnumber'])));
        $city = join(' ', array_filter(array($shippingDetails['zipcode'], $shippingDetails['city'])));
        $country = $shippingDetails['countryiso'];

        return join(', ', array_filter(array($fullName, $company, $street, $city, $country)));
    }

    /**
     * Adds the country ISO and state short code to the given shipping details.
     *
     * @param array $shippingDetails A pointer to an array containing the address data.
     * @return array The updated shipping details array.
     * @throws Exception it the 'stateid' does not exist
     */
    public function addCountryISOAndStateShortCode($shippingDetails)
    {
        // Lookup country ISO code
        $shippingDetails['countryiso'] = $this->findCountryISO(intval($shippingDetails['countryid']));

        // Lookup state short code
        $shippingDetails['stateshortcode'] = null;
        if ($shippingDetails['stateid'] != 0) {
            $state = Shopware()->Container()->get('models')->getRepository('Shopware\Models\Country\State')->find($shippingDetails['stateid']);
            if ($state === null) {
                throw new \Exception(sprintf('Invalid state id %d', $shippingDetails['stateid']));
            }
            $shippingDetails['stateshortcode'] = $state->getShortCode();
        }

        return $shippingDetails;
    }

    /**
     * @deprecated
     * Determines whether the order with the given id has 'cash on delivery' as its payment method.
     * @param int $orderId The id of the order whose payment method shall be checked.
     * @return bool True, if the name of the payment method of the order contains the string 'cash'. Otherwise false.
     */
    public static function isCashOnDelivery($orderId)
    {
        $paymentName = Shopware()->Db()->fetchRow(
            'SELECT p.name
             FROM s_order o
             INNER JOIN s_core_paymentmeans p
             ON o.paymentID = p.id
             WHERE o.id = ?',
            array(
                $orderId
            )
        );
        if ($paymentName === false) {
            return false;
        }

        return strpos(strtolower($paymentName['name']), 'cash') !== false;
    }

    /**
     * Determines whether the order with the given id has 'cash on delivery' as its payment method.
     *
     * @param int $orderId The id of the order whose payment method shall be checked.
     * @return bool True, if the name of the payment method of the order contains the info from cashOnDeliveryPaymentMeansIds. Otherwise false.
     */
    public function hasCashOnDeliveryPaymentMeans($orderId)
    {
        try {
            $shopId = $this->originatingShop($orderId);
            $paymentMeansIDs = $this->config($shopId, 'cashOnDeliveryPaymentMeansIds', true);

            if ($paymentMeansIDs === false || $orderId === null) {
                return false;
            }

            $paymentMeansIDs = explode(',', $paymentMeansIDs);
            $select = Shopware()->Db()->select()
                ->from(array('o' => 's_order'))
                ->join(array('p' => 's_core_paymentmeans'), 'p.id = o.paymentID')
                ->where('o.id IN (?)', $orderId)
                ->where('p.id IN (?)', $paymentMeansIDs);

            $paymentName = Shopware()->Db()->fetchRow($select);

            return $paymentName !== false;

            // Don't throw exception just log the error message
            // and let the User continue with the order,
            // because this is a Nice To Have Feature
        } catch (Exception $e) {
            $this->log($e->getMessage());

            return false;
        }
    }

    /**
     * Check if a given version is greater or equal to
     * the currently installed shopware version.
     *
     * Attention: If your target shopware version may
     * include a version less than 4.1.3 you have to
     * use assertVersionGreaterThen().
     *
     * @since 4.1.3 introduced assertMinimumVersion($requiredVersion)
     *
     * @param  string $requiredVersion string Format: 3.5.4 or 3.5.4.21111
     *
     * @return bool
     */
    public static function assertMinimumVersion($requiredVersion)
    {
        $version = Shopware()->Config()->version;

        if ($version === '___VERSION___') {
            return true;
        }

        return version_compare($version, $requiredVersion, '>=');
    }

    /**
     * @param int $orderId
     * @return array
     */
    public function getDefaultPackageDimensions($orderId)
    {
        $dimensions = array(
            'length' => null,
            'width' => null,
            'height' => null
        );

        // Only return actual package dimensions if they are required
        if ($this->packageDimensionsRequiredForOrder($orderId)) {
            $shopId = $this->originatingShop($orderId);
            $dimensions = $this->getDefaultPackageDimensionsForShopId($shopId);
        }

        return $dimensions;
    }

    /**
     * @param int $shopId
     * @return array
     */
    public function getDefaultPackageDimensionsForShopId($shopId)
    {
        $dimensions = [
            'length' => $this->config($shopId, 'defaultPackageLength'),
            'width' => $this->config($shopId, 'defaultPackageWidth'),
            'height' => $this->config($shopId, 'defaultPackageHeight'),
        ];

        return array_map(function ($value) {
            return ($value !== null) ? floatval($value) : null;
        }, $dimensions);
    }

    /**
     * Returns if package dimensions are required for the order with the given id.
     * Plugins should override this method if they require package dimensions to be
     * given for some orders.
     *
     * @param int $orderId
     * @return bool
     */
    public function packageDimensionsRequiredForOrder($orderId)
    {
        return false;
    }

    /**
     * Checks if a database column with the given name exists in the configuration table.
     *
     * @param string $fieldName
     * @return bool
     */
    public function hasConfigField($fieldName)
    {
        $sqlHelper = new \Shopware\Plugins\ViisonCommon\Classes\Installation\SQLHelper(Shopware()->Db());

        return $sqlHelper->doesColumnExist($this->pluginInfo->getConfigTableName(), $fieldName);
    }

    /**
     * Fetches the column info from the config table and converts the info
     * to field descriptions that conform to the ExtJS model field syntax.
     *
     * @param array $columnNames Column names to fetch data
     * @param string $shopId The sub shop id
     * @return array Returns a Ext JS like model mapping
     */
    public function getSpecificConfigFieldDescriptionForShopId(array $columnNames, $shopId = null)
    {
        if (!is_null($columnNames) || !is_null($shopId)) {
            throw new \InvalidArgumentException('Column names or ShopId is not defined.');
        }

        /** @var array $columnDefinitions Config column definitions */
        $columnDefinitions = Shopware()->Container()->get('db')->fetchAll(
            "SELECT :columnNames
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = (SELECT DATABASE()) AND shopId = :shopId
            AND TABLE_NAME = :tableName",
            array(
                'columnNames' => join(',', $columnNames),
                'shopId' => $shopId,
                'tableName' => $this->pluginInfo->getConfigTableName()
            )
        );

        return $this->convertSqlModelToExtJsModelDescription($columnDefinitions);
    }

    /**
     * Fetches the column info from the config table and converts the info
     * to field descriptions that conform to the ExtJS model field syntax.
     *
     * Note: Some columns should never leave the backend and thus
     * should not be included in the Extjs model.
     *
     * @return array Returns a Ext JS like model mapping
     */
    public function getConfigFieldDescriptions()
    {
        $excludedColumns = "'" . implode("', '", $this->getExcludedConfigColumns()) . "'";
        /** @var array $columnDefinitions All config column definitions */
        $columnDefinitions = Shopware()->Container()->get('db')->fetchAll(
            'SELECT *
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = (SELECT DATABASE())
            AND TABLE_NAME = :tableName
            AND column_name NOT IN (' . $excludedColumns . ')',
            array(
                'tableName' => $this->pluginInfo->getConfigTableName()
            )
        );

        return $this->convertSqlModelToExtJsModelDescription($columnDefinitions);
    }

    /**
     * Returns the columns which should not be mapped to Extjs.
     *
     * @return string[]
     */
    protected function getExcludedConfigColumns()
    {
        return array('encryptionMethod');
    }

    /**
     * @param string $sqlType
     * @return string
     */
    public function sqlTypeToExtJSType($sqlType)
    {
        switch (strtolower($sqlType)) {
            case 'char':
            case 'varchar':
            case 'tinytext':
            case 'text':
            case 'mediumtext':
            case 'longtext':
                return 'string';
            case 'smallint':
            case 'mediumint':
            case 'int':
            case 'bigint':
                return 'int';
            case 'float':
            case 'double':
            case 'decimal':
                return 'float';
            case 'tinyint':
            case 'bool':
            case 'boolean':
                return 'boolean';
            case 'date':
            case 'datetime':
            case 'timestamp':
                return 'date';
            default:
                return 'auto';
        }
    }

    /**
     * @param string $extJSType
     * @param string $sqlDefault
     * @return mixed
     */
    public function sqlDefaultToExtJSDefault($extJSType, $sqlDefault)
    {
        // MariaDB special treatment in this function:
        // * MariaDB wraps string default values in single quotes, MySQL does not, so a trim is applied to strings
        // * The string NULL means an actual null in MariaDB

        if ($sqlDefault === null || $sqlDefault === 'NULL') {
            return null;
        }

        switch ($extJSType) {
            case 'int':
                return intval($sqlDefault);
            case 'float':
                return floatval($sqlDefault);
            case 'boolean':
                return boolval($sqlDefault);
            case 'date':
                return new \DateTime(trim($sqlDefault, "'"));
            case 'auto':
            case 'string':
            default:
                return trim($sqlDefault, "'");
        }
    }

    /**
     * Determines, which encryption method is the most desirable. Encrypted values in the configuration will be changed
     * automatically to the method given here when the user opens the configuration in the Shopware backend.
     *
     * @return string
     */
    public function getOptimalEncryptionMethod()
    {
        $supportsOpenSSL = $this->hasConfigField('encryptionMethod') && extension_loaded('openssl');

        return $supportsOpenSSL ? 'aes-256-cbc' : 'rijndael256';
    }

    /**
     * Determines the encryption method used for the given configuration row. If no encryptionMethod column exists,
     * the previously used 'rijndael256' method is used for backwards compatibility.
     *
     * @param $configRow
     * @return string
     */
    public function getDecryptionMethod($configRow)
    {
        if (array_key_exists('encryptionMethod', $configRow)) {
            return $configRow['encryptionMethod'];
        } else {
            return 'rijndael256';
        }
    }

    /**
     * Formats snippet to add additional params
     *
     * Note: The snippet needs to contain the character '%s' so the replacement works
     *
     * @deprecated Use sprintf or vsprintf instead
     * @param string $snippet
     * @param array $additionalParam
     * @return string
     */
    public function addAdditionalTextToSnippet($snippet, $additionalParam)
    {
        return vsprintf($snippet, $additionalParam);
    }

    /**
     * Get the document from the URL.
     *
     * @param $url
     * @return string The given pdf from the URL.
     * @throws \Exception
     */
    public function getDocumentFromUrl($url)
    {
        if (empty($url)) {
            throw new \Exception('ShippingCommon :: Please provide URL to the downloadDocuments method.');
        }

        $exceptionMessagesNamespace = $this->getSnippetHandlerFromNamespace('backend/viison_shipping_common_exceptions/exceptions');

        // Download the respective document
        $pdf = file_get_contents($url);
        if ($pdf === false) {
            // Download failed
            $this->log('ShippingCommon :: Failed to download document from "' . $url . '".');
            throw new \Exception(
                $this->addAdditionalTextToSnippet(
                    $exceptionMessagesNamespace->get('exception/download_document'),
                    $url
                )
            );
        }

        return $pdf;
    }

    /**
     * Download the respective document from to the given Path.
     *
     * @deprecated The Document model is responsible for download
     *
     * @param string $pdf The PDF.
     * @param string $path The Path where to save the document,
     * @return string The saved PDF,
     * @throws \Exception
     */
    public function downloadDocumentToGivenPath($pdf, $path)
    {
        if (empty($pdf) || empty($path)) {
            throw new \Exception('ShippingCommon :: Please provide URL and PATH to the downloadDocuments method.');
        }

        $exceptionMessagesNamespace = $this->getSnippetHandlerFromNamespace('backend/viison_shipping_common_exceptions/exceptions');

        // Check if write to directory is possible
        if (!is_writable(dirname($path))) {
            throw new \Exception($this->addAdditionalTextToSnippet($exceptionMessagesNamespace->get('exception/write_document'), $path));
        }

        // Save the content of the binary buffer in a new PDF file in the Shopware documents directory
        $writeSuccess = file_put_contents($path, $pdf);
        if ($writeSuccess === false) {
            // Save failed
            $this->log('ShippingCommon :: Failed to load document.');
            throw new \Exception($this->addAdditionalTextToSnippet($exceptionMessagesNamespace->get('exception/write_document'), $path));
        }

        return $pdf;
    }

    /**
     * Persists the supplied document with the given file name.
     *
     * @deprecated The Document model is responsible for download
     *
     * @param string $pdf The PDF.
     * @param string $fileName The file name of the document to save
     * @return string The saved PDF
     */
    public function persistDocument($pdf, $fileName)
    {
        /** @var FileStorage $fileStorageService */
        $fileStorageService = Shopware()->Container()->get('viison_common.document_file_storage_service');

        // Save the content of the binary buffer in a new PDF file in the Shopware documents directory
        $fileStorageService->writeFileContents($fileName, $pdf);

        return $pdf;
    }

    /**
     * Get country iso3 code
     *
     * @param string $countryIso The country iso2 code
     * @return string The country iso3code
     * @throws \Exception
     */
    public function getIso3CodeFromCountryIsoOrName($countryIso)
    {
        /** @var Shopware/Db */
        $database = Shopware()->Db();
        /** @var \Zend_Db_Table */
        $countriesTable = new \Zend_Db_Table('s_core_countries');

        if (empty($countriesTable) || empty($countryIso)) {
            return '';
        }

        $data = $database->fetchOne(
            $countriesTable->select()
                ->from(
                    $countriesTable,
                    array('iso3')
                )
                ->where('countryiso = ?', $countryIso)
        );

        if (!isset($data)) {
            throw new \Exception('Countries table result is empty.');
        }

        return $data;
    }

    /**
     * Extract the tracking code via the document identifier
     *
     * @param string $documentIdentifier
     * @return null|string
     * @throws \Exception
     */
    public function getTrackingCodeViaDocumentIdentifier($documentIdentifier)
    {
        if (!isset($documentIdentifier)) {
            throw new \Exception('Document identifier is missing');
        }

        try {
            /** @var Shopware/Db */
            $database = Shopware()->Db();
            /** @var \Zend_Db_Table */
            $orderTable = new \Zend_Db_Table($this->pluginInfo->getOrderTableName());

            $trackingCode = $database->fetchOne(
                $orderTable->select()
                    ->from($orderTable, array('trackingcode'))
                    ->where('url LIKE ?', '%' . $documentIdentifier)
                    ->orWhere('id = ? ', $documentIdentifier)
                    ->orWhere('trackingCode = ?', $documentIdentifier)
            );

            return empty($trackingCode) ? null : $trackingCode;
        } catch (\Zend_Db_Exception $e) {
            throw new \Zend_Db_Exception($e->getMessage());
        } catch (\Zend_Db_Statement_Exception $e) {
            throw new \Zend_Db_Statement_Exception($e->getMessage());
        } catch (\Zend_Db_Table_Select_Exception $e) {
            throw new \Zend_Db_Table_Select_Exception($e->getMessage());
        } catch (\Zend_Exception $e) {
            throw new \Zend_Exception($e->getMessage());
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage());
        }
    }


    /**
     * Get the Identifier position.
     *
     * @param string $identifier
     * @return bool|int The Position if the identifier has been found or false.
     */
    public function getIdentifierPosition($identifier)
    {
        $matches = array();
        preg_match(
            '/.*(' . PluginInfo::LABEL_IDENTIFIER_SEPARATOR . '|:)(.*?)$/',
            $identifier,
            $matches,
            PREG_OFFSET_CAPTURE
        );

        return $matches[1][1] ?: false;
    }

    /**
     * Check if the country iso is a EU country.
     *
     * @param string $countryIso
     * @return bool
     */
    public function isEuCountry($countryIso)
    {
        return in_array($countryIso, self::$euCountryCodes);
    }

    /**
     * Fetches the complete configuration for the originating shop of the given order id
     * and checks, if all mandatory fields ar not empty.
     *
     * @param int $shopId The id of the shop, whose configuration shall be checked.
     * @param bool $getMissingMandatoryFields Option field that indicates if we want the list of the missing fields
     * @return boolean | array
     */
    protected function shopHasValidConfig($shopId, $getMissingMandatoryFields = false)
    {
        // Get complete config
        $configTable = new \Zend_Db_Table($this->pluginInfo->getConfigTableName());
        $config = $configTable->fetchRow($configTable->select()->where('shopId = ?', $shopId));
        if (empty($config)) {
            return false;
        }

        // Check all mandatory values
        $isValidConfig = true;
        $missingFieldNames = array();
        $config = $config->toArray();
        foreach ($this->getMandatoryConfigFields() as $field) {
            // Avoid using "empty" here so that the string "0" is not interpreted as a missing config value
            $isMandatoryFieldMissing = !(isset($config[$field]) && strlen(trim($config[$field])) > 0);
            $isValidConfig = $isValidConfig && !$isMandatoryFieldMissing;
            if ($getMissingMandatoryFields && $isMandatoryFieldMissing) {
                // Get missing field name
                $missingFieldNames[] = $field;
            }
        }

        return ($getMissingMandatoryFields === true) ? $missingFieldNames : $isValidConfig;
    }

    /**
     * Returns an array describing a product using the raw data loaded from product and product mapping tables.
     *
     * @param int $shopId
     * @param array $product
     * @param bool|null $exportDocumentRequired
     * @return array
     */
    protected function getProductFields($shopId, $product, $exportDocumentRequired)
    {
        return array_merge(array(
            'productId' => intval($product['id']),
            'productCode' => $product['code'],
            'packageDimensionsRequired' => (bool)$product['packageDimensionsRequired'],
            'exportDocumentRequired' => $exportDocumentRequired
        ), $this->getExtraProductFields($shopId, $product));
    }

    /**
     * Compose error message for invalid configuration file
     *
     * @param int $shopId
     * @param array $missingConfigFields
     * @return string
     */
    private function composeErrorMessageForNoValidConf($shopId, $missingConfigFields)
    {
        $pluginDisplayName = ($this->pluginInfo && $this->pluginInfo->getPluginDisplayName()) ? $this->pluginInfo->getPluginDisplayName() : 'Adapter';
        $shop = Shopware()->Container()->get('models')->find('Shopware\Models\Shop\Shop', $shopId);
        $missingConfigFields = implode(', ', $missingConfigFields);
        if ($this->getShopLanguage() == 'de') {
            $errorConfSubShopName = 'Kunden -> ' . $pluginDisplayName . ' -> Konfiguration ' . (($shop) ? '(Subshop: ' . $shop->getName() . ')' : '') . '.';
            $errorMessage =  'Die Konfiguration des '. $pluginDisplayName .' ist nicht korrekt. Bitte lesen Sie gegebenenfalls in der ' . $pluginDisplayName . ' Dokumentation nach und überprüfen Sie die Konfiguration im ' . $errorConfSubShopName . ' ( ' . $missingConfigFields . ' )';
        } else {
            $errorConfSubShopName = 'Customer -> ' . $pluginDisplayName . ' -> Configuration ' . (($shop) ? '(Subshop: ' . $shop->getName() . ')' : '') . '.';
            $errorMessage = 'The configuration of the '. $pluginDisplayName .' is not correct. Please refer to the ' . $pluginDisplayName . ' documentation if necessary and check the configuration in the ' . $errorConfSubShopName . ' ( ' . $missingConfigFields . ' )';
        }

        return $errorMessage;
    }

    /**
     * Convert Sql Data to Ext JS model description
     *
     * @param array $columnDefinitions
     * @return array Returns a Ext JS like model mapping
     */
    private function convertSqlModelToExtJsModelDescription(array $columnDefinitions)
    {
        // Convert column definitions to field descriptions
        $me = $this;
        $fieldDescriptions = array_map(function ($definition) use ($me) {
            $extJSType = $me->sqlTypeToExtJSType($definition['DATA_TYPE']);
            $defaultValue = $me->sqlDefaultToExtJSDefault($extJSType, $definition['COLUMN_DEFAULT']);
            $description = array(
                'name' => $definition['COLUMN_NAME'],
                'type' => $extJSType,
                'useNull' => $definition['IS_NULLABLE'] === 'YES'
            );
            if ($description['useNull'] ||  $defaultValue !== null) {
                $description['defaultValue'] = $defaultValue;
            }

            return $description;
        }, $columnDefinitions);

        return $fieldDescriptions;
    }

    /**
     * Check if the provided shop has a valid configuration, if not try the default\main shop as fallback.
     *
     * @param Shop $shop
     * @return Shop|null
     */
    public function findShopWithValidConfiguration(Shop $shop)
    {
        if ($this->shopHasValidConfig($shop->getId())) {
            return $shop;
        }

        if ($shop->getMain()) {
            return $this->findShopWithValidConfiguration($shop->getMain());
        }

        if ($shop->getDefault()) {
            return null;
        }

        /** @var ModelManager $modelManager */
        $modelManager = Shopware()->Container()->get('models');
        $defaultShop = $modelManager->getRepository('Shopware\Models\Shop\Shop')->getDefault();

        return $this->findShopWithValidConfiguration($defaultShop);
    }

    /**
     * @param string $orderId
     * @param string $orderNumber
     * @return string
     * @throws \Exception
     */
    private function getCustomerNumberForOrder($orderId)
    {
        $result = null;
        // Starting with Shopware 5.2, the customer number is part of the user instead of the user billing address model.
        if ($this->assertMinimumVersion('5.2')) {
            $result = Shopware()->Db()->fetchAll(
                'SELECT
                    s_user.customernumber AS customerNumber
                FROM s_order
                LEFT JOIN s_user
                    ON s_order.userID = s_user.id
                WHERE
                    s_order.id = ?',
                array(
                    $orderId
                )
            );
        } else {
            $result = Shopware()->Db()->fetchAll(
                'SELECT
                    s_user_billingaddress.id AS userBillingId,
                    s_user_billingaddress.customernumber AS customerNumber,
                    s_order.ordernumber AS orderNumber
                FROM s_order
                LEFT JOIN s_user_billingaddress
                    ON s_order.userID = s_user_billingaddress.userID
                WHERE
                    s_order.id = ?',
                array(
                    $orderId
                )
            );
            if ($result[0]['userBillingId'] === null) {
                throw new \Exception(
                    sprintf('Beim Kunden der Bestellung %s fehlt die Rechnungsadresse', $result[0]['orderNumber'])
                );
            }
        }

        return $result[0]['customerNumber'];
    }
}
