Sales Model

Revised Sales Model

<?php namespace Kurashicom\Store\Analytics\Sales;

use Illuminate\Database\DatabaseManager as SqlDatabaseManager;
use Kurashicom\Traits\MetaPropertiesTrait;

class Sales implements SalesInterface
{
    use MetaPropertiesTrait;

    /**
     * Variable to hold the instance of the injected dependency.
     *
     * @var Illuminate\Database\DatabaseManager
     */
    protected $sqlDatabaseManager;

    /**
     * Get order ids for current scope.
     *
     * @var array
     */
    protected $orderIds = [];

    /**
     * Offset for queries made by this instance.
     *
     * @var int
     */
    protected $offset = 0;

    /**
     * Limit for queries made by this instance.
     *
     * @var int
     */
    protected $limit = 10;

    /**
     * Sort column for queries made by this instance.
     *
     * @var string
     */
    protected $sort = 'order_count';

    /**
     * Sort direction for queries made by this instance.
     *
     * @var string
     */
    protected $direction = 'desc';

    /**
     * MaxLimit for queries made by this instance.
     *
     * @var int
     */
    protected $maxLimit = 1000;

    /**
     * Create new instances for dependencies.
     *
     * @param  Illuminate\Database\DatabaseManager $sqlDatabaseManager
     * @return void
     */
    public function __construct(SqlDatabaseManager $sqlDatabaseManager)
    {
        $this->sqlDatabaseManager = $sqlDatabaseManager;
    }

    /**
     * Get sales data
     *
     * @param  string $from
     * @param  string $to
     * @return array|null
     */
    public function all($from, $to)
    {
        $orders = $this->getOrders($from, $to);
        if ($orders === null) {
            return null;
        }
        
        foreach ($orders as $index => $order) {
            // array-ify object
            $orders[$index] = (array)$orders[$index];
            
            // get
            $customer = $this->sqlDatabaseManager->table('order_customers')->where('id', $orders[$index]['customer_id'])->first(['name']);
            $customer = $customer === null ? ['name' => ''] : (array)$customer;
            $totals   = (array)$this->sqlDatabaseManager->table('order_totals')->where('id', $order['id'])->first();
            
            // merge
            $orders[$index] = array_merge($orders[$index], $customer, $totals);
            
            // unset
            unset($orders[$index]['customer_id']);
        }

        return $orders;
    }
    
    /**
     * Get monthly breakdown of sales data
     *
     * @param  string $from
     * @param  string $to
     * @return array|null
     */
    public function monthly($from, $to)
    {
        $orders = $this->getMonthlyGroupedOrderIds($from, $to);
        if ($orders === null) {
            return null;
        }
        
        $salesData = [];
        
        foreach ($orders as $order) {
            $orderTotal = (array)$this->sqlDatabaseManager
                                ->table('order_totals')
                                ->select($this->sqlDatabaseManager->raw('
                                    CAST(SUM(`subtotal`) as UNSIGNED) as `subtotal`,
                                    CAST(SUM(`shipping_fee`) as UNSIGNED) as `shipping_fee`,
                                    CAST(SUM(`payment_fee`) as UNSIGNED) as `payment_fee`,
                                    CAST(SUM(`wrapping_total`) as UNSIGNED) as `wrapping_total`,
                                    CAST(SUM(`points_discount`) as UNSIGNED) as `points_discount`,
                                    CAST(SUM(`total`) as UNSIGNED) as `total`
                                '))
                                ->whereIn('order_id', explode(',', $order->order_ids))
                                ->orderBy($this->sort, $this->direction)
                                ->skip($this->offset)
                                ->take($this->limit)
                                ->get();
            
            $orderTotal['date'] = $order->date;
            
            $salesData[] = $orderTotal;
        }
        
        return $salesData;
    }
    ​
    /**
     * Get orders for current scope
     *
     * @param  string $from
     * @param  string $to
     * @return array|null
     */
    protected function getOrders($from, $to)
    {
        $daysBetween = $this->timeToDays($this->timeBetween($from, $to));
        if ($daysBetween > 180) { // 6 months
            return null;
        }

        if (! count($this->orderIds)) {
            $this->orderIds = $this->sqlDatabaseManager
                    ->table('orders')
                    ->where('combined', '<>', 2)
                    ->where('order_date', '>=', ($from . ' 00:00:00'))
                    ->where('order_date', '<=', ($to   . ' 23:59:59'))
                    ->get(['id', 'customer_id', 'order_date']);
        }
        return $this->orderIds;
    }
    
    /**
     * Get order ids for current scope
     *
     * @param  string $from
     * @param  string $to
     * @return array|null
     */
    protected function getMonthlyGroupedOrderIds($from, $to)
    {
        $daysBetween = $this->timeToDays($this->timeBetween($from, $to));
        if ($daysBetween > 180) { // 6 months
            return null;
        }
        
        if (! count($this->orderIds)) {
            $this->orderIds = $this->sqlDatabaseManager
                    ->table('orders')
                    ->select($this->sqlDatabaseManager->raw('
                        DATE_FORMAT(`order_date`, "%Y-%m") as `date`,
                        GROUP_CONCAT(`id`) as `order_ids`
                    '))
                    ->groupBy('date')
                    ->where('combined', '<>', 2)
                    ->where('order_date', '>=', ($from . '-01 00:00:00'))
                    ->where('order_date', '<=', ($to   . '-31 23:59:59'))
                    ->get();
        }
        return $this->orderIds;
    }
    ​
    /**
     * Calculate amount of time between given dates
     *
     * @param  string $from
     * @param  string $to
     * @return int
     */
    protected function timeBetween($from, $to)
    {
        $a = (int)strtotime($from);
        $b = (int)strtotime($to);
    ​
        return floor(abs($a - $b));
    }
    ​
    /**
     * Calculate amount of days for given time
     *
     * @param  int $time
     * @return int
     */
    protected function timeToDays($time)
    {
        return floor($time/(60*60*24));
    }
}