Test Task

mysql,txt

1) Show Stores, that have products with Christmas, Winter Tags

SELECT Store.* FROM Store 
LEFT JOIN Product ON Store.id = Product.store_id
LEFT JOIN TagConnect ON Product.id = TagConnect.product_id
LEFT JOIN Tag ON Tag.id = TagConnect.tag_id
WHERE Tag.tag_name in ('Christmas', 'Winter')
GROUP BY Store.id

2) Show Users, that never bought Product from Store with id == 5

SELECT User.* FROM User 
LEFT JOIN `Order` ON `Order`.customer_id = User.id
LEFT JOIN OrderItem ON OrderItem.order_id = `Order`.id
LEFT JOIN Product ON OrderItem.product_id = Product.id
WHERE Product.store_id!=5
GROUP BY User.id

3) Show Users, that had spent more than $1000 

SELECT User.* FROM User 
LEFT JOIN `Order` ON `Order`.customer_id = User.id
LEFT JOIN OrderItem ON OrderItem.order_id = `Order`.id
LEFT JOIN Product ON OrderItem.product_id = Product.id
GROUP BY User.id
HAVING SUM(`Product`.`price`) > 1000

4) Show Stores, that have not any Sells

SELECT Store.* FROM Store 
LEFT JOIN Product ON Store.id = Product.store_id
LEFT JOIN OrderItem ON OrderItem.product_id = Product.id
GROUP BY Store.id
HAVING COUNT(OrderItem.id) = 0

5) Show Mostly sold Tags

SELECT Tag.*, COUNT(OrderItem.id) AS sells_count FROM Tag 
LEFT JOIN TagConnect ON Tag.id = TagConnect.tag_id
LEFT JOIN Product ON TagConnect.product_id = Product.id
LEFT JOIN OrderItem ON OrderItem.product_id = Product.id
GROUP BY Tag.id
ORDER BY COUNT(OrderItem.id) DESC

6) Show Monthly Store Earnings Statistics 

SELECT year(`Order`.`order_date`) as Year, month(`Order`.`order_date`) AS Month, SUM(Product.price) AS `Total` FROM `Order` 
LEFT JOIN OrderItem ON `Order`.id = OrderItem.order_id
LEFT JOIN Product ON OrderItem.product_id = Product.id
GROUP BY year(`Order`.`order_date`),month(`Order`.`order_date`)
ORDER BY year(`Order`.`order_date`),month(`Order`.`order_date`)

optimization.php

<?php

class StoreManager
{

    /*
    * @var DatabaseManager $dbManager
    */
    protected $dbManager = null;

    /*
    * @param DatabaseManager $dbManager
    */
    public function __construct(DatabaseManager $dbManager)
    {
        $this->dbManager = $dbManager;
    }

    /*
    * Calucalation of store Earnings
    *
    * @param int $storeId
    *
    * @return float
    */
    public function calculateStoreEarnings(int $storeId): float
    {
        $totalAmount = 0;
        /**
         * I added new method getOrderProducts
         * If understood correctly it should work like this
         * But I need a description of the calculation logic 
         * Because some things can be calculated on the MySQL server side 
        **/
        foreach ($this->getOrderProducts($storeId) as $product) {
            $totalAmount += $product['price'];
            $tags = $this->getProductTags($product['id']);
            $tagCount = self::getTotalUniqueTags();

            // I need a description for this part 
            $totalAmount = $totalAmount * (1 + count($tags) / $tagCount);

            foreach ($tags as $tag) {
                if ($tag['tag_name'] = 'Christmas') {
                    $totalAmount = $totalAmount * 1.01;
                }

                if ($tag['tag_name'] == 'Free') {
                    $totalAmount = $totalAmount * 0.5;
                }
            }
        }

        return $totalAmount;
    }

    /*
    * @param int $storeId
    *
    * return array
    */
    protected function getProducts(int $storeId): array
    {
        $query = 'SELECT * FROM Product WHERE store_id = :store';
        return $this->dbManager->getData($query, ['store' => $storeId]);
    }

    /*
    * @param int $productId
    *
    * return array
    */
    protected function getOrderItems(int $productId): array
    {
        $query = 'SELECT * FROM OrderItem WHERE product_id = :product';
        return $this->dbManager->getData($query, ['product' => $productId]);
    }

    /*
    * Get all products from all orders
    *
    * @param int $storeId
    *
    * return array
    */
    protected function getOrderProducts(int $storeId): array
    {
        $query = 'SELECT Product.* FROM Product '.
                 'LEFT JOIN OrderItem ON OrderItem.product_id = Product.id '.
                 'WHERE Product.store_id = :storeId';
        return $this->dbManager->getData($query, ['storeId' => $storeId]);
    }

    /*
    * @param int $productId
    *
    * return array
    */
    protected function getProductTags(int $productId): array
    {
        $query = 'SELECT * FROM Tag WHERE id IN(SELECT tag_id FROM TagConnect WHERE product_id = :product)';
        return $this->dbManager->getData($query, ['product' => $productId]);
    }

    /*
    * return int
    */
    public static function getTotalUniqueTags(): int
    {
        $query = 'SELECT COUNT(DISTINCT tag_name) as count FROM Tag';

        $result = $this->dbManager->getData($query, []);

        return $result[0]['count']; //I need to see DatabaseManager class specs But I think it will looks like this
    }

}

php_oop_task.php

<?php


interface IUser
{
    /**
     * Magic method
     *
     * @return string
     */
    public function __toString():string;
}

/**
 * Customer class
 */
class Customer implements IUser
{

    /**
     * @var int
     */
    protected int $id;

    /**
     * @var string
     */
    protected string $name;

    /**
     * @var float
     */
    protected float $balance;

    /**
     * @var int
     */
    protected int $purchaseCount;

    /**
     * Class constructor
     *
     * @param int $id
     * @param string $name
     * @param float $balance
     * @param int $purchaseCount
     */
    public function __construct(int $id, string $name, float $balance, int $purchaseCount)
    {
        $this->id = $id;
        $this->name = $name;
        $this->balance = $balance;
        $this->purchaseCount = $purchaseCount;
    }

    /**
     * Magic method
     *
     * @return string
     */
    public function __toString():string
    {
        return  'id:'. $this->id. PHP_EOL.
                'name:'. $this->name. PHP_EOL.
                'balance:'. $this->balance. PHP_EOL.
                'purchase-count:'. $this->purchaseCount;
    }
}

/**
 * Seller class
 */
class Seller implements IUser
{

    /**
     * @var int
     */
    protected int $id;

    /**
     * @var string
     */
    protected string $name;

    /**
     * @var float
     */
    protected float $earningsBalance;

    /**
     * @var int
     */
    protected int $productCount;

    /**
     * Class constructor
     *
     * @param int $id
     * @param string $name
     * @param float $earningsBalance
     * @param int $productCount
     */
    public function __construct(int $id, string $name, float $earningsBalance, int $productCount)
    {
        $this->id = $id;
        $this->name = $name;
        $this->earningsBalance = $earningsBalance;
        $this->productCount = $productCount;
    }

    /**
     * Magic method
     *
     * @return string
     */
    public function __toString():string
    {
        return  'id:'. $this->id. PHP_EOL.
                'name:'. $this->name. PHP_EOL.
                'earnings-balance:'. $this->earningsBalance. PHP_EOL.
                'product-count:'. $this->productCount;
    }
}

/**
 * Administrator class
 */
class Administrator implements IUser
{

    /**
     * @var int
     */
    protected int $id;

    /**
     * @var string
     */
    protected string $name;

    /**
     * @var array
     */
    protected array $permissions;

    /**
     * Class constructor
     *
     * @param int $id
     * @param string $name
     * @param string $permissions
     */
    public function __construct(int $id, string $name, string $permissions)
    {
        $this->id = $id;
        $this->name = $name;
        $this->permissions = json_decode($permissions);
    }

    /**
     * Magic method
     *
     * @return string
     */
    public function __toString():string
    {
        return  'id:'. $this->id. PHP_EOL.
                'name:'. $this->name. PHP_EOL.
                'permissions:'.json_encode($this->permissions);
    }
}

/**
 *  User Manager class
 */
class UserManager
{
    /**
     * Show user info
     * 
     * @param IUser $user
     * @return string
     */
    public function getUserInfo(IUser $user):string
    {
        return 'user-type:'.get_class($user) .PHP_EOL.$user;
    }
}