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

use Shopware\Plugins\ViisonCommon\Classes\Controllers\Backend\AbstractAnalyticsController;

/**
 * An analytics controller to report pick- and pack-times of each user (worker).
 */
class Shopware_Controllers_Backend_ViisonPickwareMobileAnalyticsPickTimes extends AbstractAnalyticsController
{
    /**
     * Main action to get pick and pack times analytics
     */
    public function getPickTimesReportAction()
    {
        // Build temporary table with pick times and counts only once.
        $this->buildTemporaryPickTimeTable();
        $this->buildTemporaryPickCountTable();

        // Get query builder for the selected page of the store (limited result)
        $pickTimesQueryBuilder = $this->getPaginatedPickTimesQuery();
        $pickTimes = $pickTimesQueryBuilder->execute()->fetchAll(PDO::FETCH_ASSOC);
        $totalNumberOfRows = $this->get('db')->fetchOne('SELECT FOUND_ROWS()');

        // Send results
        $this->send($pickTimes, $totalNumberOfRows);

        // Get query builder for full data to calculate summary (unlimited result)
        $totalPickTimesQueryBuilder = $this->getPickTimesQuery();
        $totalPickTimes = $totalPickTimesQueryBuilder->execute()->fetchAll(PDO::FETCH_ASSOC);
        $this->View()->assign([
            'summary' => $this->getSummary($totalPickTimes),
        ]);
    }

    /**
     * Calculates summary row of the total results of the query and returns it as an array.
     *
     * @param array $totalPickTimes
     * @return array
     */
    private function getSummary(array $totalPickTimes)
    {
        $sumOfPickedOrders = array_sum(array_column($totalPickTimes, 'pickedOrders'));
        $sumOfPackedOrders = array_sum(array_column($totalPickTimes, 'packedOrders'));
        $sumOfPickTimes = array_sum(array_column($totalPickTimes, 'pickTimeInSeconds'));
        $workdays = array_sum(array_column($totalPickTimes, 'workdays'));

        return [
            'workdays' => $workdays,
            'pickedOrders' => $sumOfPickedOrders,
            'packedOrders' => $sumOfPackedOrders,
            'pickTimeInSeconds' => $sumOfPickTimes,
            'averagePickTimeInSeconds' => $sumOfPickedOrders ? round($sumOfPickTimes / $sumOfPickedOrders) : null,
            'picksPerWorkday' => $workdays ? round($sumOfPickedOrders / $workdays, 1) : null,
            'packsPerWorkday' => $workdays ? round($sumOfPackedOrders / $workdays, 1) : null,
        ];
    }

    /**
     * Fetches the basic pick-times query builder and adds limits and sorting.
     *
     * @return mixed
     */
    private function getPaginatedPickTimesQuery()
    {
        $builder = $this->getPickTimesQuery();

        // Add sorting
        foreach ($this->getSorting() as $sorting) {
            $builder->addOrderBy($sorting['property'], $sorting['direction']);
        }

        // Add limits
        if ($this->getStart()) {
            $builder->setFirstResult($this->getStart());
        }
        if ($this->getLimit()) {
            $builder->setMaxResults($this->getLimit());
        }

        return $builder;
    }

    /**
     * Creates and returns the dbal querybuilder to fetch pick- and pack times of each worker.
     * This query is not limited.
     *
     * @return mixed
     */
    private function getPickTimesQuery()
    {
        $builder = $this->get('dbal_connection')->createQueryBuilder();
        $builder
            ->select(
                'SQL_CALC_FOUND_ROWS pickAndPackEvents.userID AS id',
                'backendUser.name AS name',
                'pickAndPackEvents.workdays AS workdays',
                'pickAndPackEvents.pickCount AS pickedOrders',
                'pickAndPackEvents.packCount As packedOrders',
                'pickTimes.pickTime AS pickTimeInSeconds',
                'ROUND(pickTimes.pickTime / pickAndPackEvents.pickCount) AS averagePickTimeInSeconds',
                'ROUND(pickAndPackEvents.pickCount / pickAndPackEvents.workdays, 1) AS picksPerWorkday',
                'ROUND(pickAndPackEvents.packCount / pickAndPackEvents.workdays, 1) AS packsPerWorkday'
            )
            ->from('s_plugin_viison_pickware_mobile_analytics_pick_pack_events_temp', 'pickAndPackEvents')
            ->leftJoin('pickAndPackEvents', 's_plugin_viison_pickware_mobile_analytics_pick_times_temp', 'pickTimes', 'pickTimes.userID = pickAndPackEvents.userID')
            ->leftJoin('pickAndPackEvents', 's_core_auth', 'backendUser', 'backendUser.id = pickAndPackEvents.userID')
            ->andWHere('pickAndPackEvents.pickCount + pickAndPackEvents.packCount > 0');

        return $builder;
    }

    /**
     * Fetches, calculates and returns an array of all all pick times (count, pick time and average pick time) per user.
     * That is the time between status changes from "in-process" to "complete" (1 to 2) OR "in process" to "partially
     * complete" (1 to 3).
     * Creates a temporary table with the results.
     *
     * Remark short/1-s pick times:
     * We simple calculate the time between "in-process" and "(partially) complete" order status changes. If a user wants
     * to pick a 1-Article-Order, he may not put the order in "in process" and "starts walking" but rather gets the
     * necessary articles and starts and finishes in one take. These 1-Second pick times are still considered in this
     * analytics. We could filter them by implementing a threshold of a few seconds.
     */
    private function buildTemporaryPickTimeTable()
    {
        $query = '
            CREATE TEMPORARY TABLE IF NOT EXISTS s_plugin_viison_pickware_mobile_analytics_pick_times_temp (
                userID INT,
                pickTime INT
            )
            SELECT
            userID,
            SUM(picktimeInSeconds) as pickTime
            FROM (
                SELECT
                    openingHistoryChange.userID as userID,
                    TIME_TO_SEC(TIMEDIFF(MIN(closingHistoryChange.change_date), openingHistoryChange.change_date)) AS picktimeInSeconds
                FROM
                    s_order_history openingHistoryChange
                LEFT JOIN
                    s_order_history closingHistoryChange
                    ON openingHistoryChange.orderID = closingHistoryChange.orderID AND
                    openingHistoryChange.userID = closingHistoryChange.userID AND
                    openingHistoryChange.id < closingHistoryChange.id AND
                    closingHistoryChange.order_status_id != closingHistoryChange.previous_order_status_id AND
                    closingHistoryChange.previous_order_status_id = 1 AND
                    closingHistoryChange.order_status_id IN (2,3)  
                LEFT JOIN
                    s_order shopwareOrder
                    ON openingHistoryChange.orderID = shopwareOrder.id
                WHERE
                    openingHistoryChange.userID IS NOT NULL AND
                    openingHistoryChange.order_status_id = 1 AND
                    openingHistoryChange.order_status_id != openingHistoryChange.previous_order_status_id AND
                    closingHistoryChange.id IS NOT NULL ';

        // Add filters
        $sqlParameters = [];
        $additionalWhereClauses = '';
        if ($this->getFromDate()) {
            $additionalWhereClauses .= ' AND openingHistoryChange.change_date >= ?';
            $sqlParameters[] = $this->getFromDate()->format('Y-m-d H:i:s');
            $additionalWhereClauses .= ' AND closingHistoryChange.change_date >= ?';
            $sqlParameters[] = $this->getFromDate()->format('Y-m-d H:i:s');
        }
        if ($this->getToDate()) {
            $additionalWhereClauses .= ' AND openingHistoryChange.change_date <= ?';
            $sqlParameters[] = $this->getToDate()->format('Y-m-d H:i:s');
            $additionalWhereClauses .= ' AND closingHistoryChange.change_date <= ?';
            $sqlParameters[] = $this->getToDate()->format('Y-m-d H:i:s');
        }
        if ($this->getSelectedShopIds()) {
            $additionalWhereClauses .= ' AND shopwareOrder.subshopID IN (' . implode(', ', $this->getSelectedShopIds()) . ')';
        }

        $query .= $additionalWhereClauses .
            'GROUP BY openingHistoryChange.id
            ) as pickTimes
            GROUP BY userID';

        $this->get('db')->query($query, $sqlParameters);
    }

    /**
     * Calculates pick counts and workdays and creates a temporary table with this information. We defined the counts as
     * follows:
     * Pick count as a status change from: 1 ("in progress") to [2 ("completed"), 3 ("partially completed")]
     * Pack count as a status change from: 5 ("ready for delivery") to [6 ("partially delivered"), 7 ("completely delivered")]
     */
    private function buildTemporaryPickCountTable()
    {
        $isPickCondition = '(orderHistory.previous_order_status_id = 1 AND orderHistory.order_status_id IN (2, 3))';
        $isPackConditiion = '(orderHistory.previous_order_status_id = 5 AND orderHistory.order_status_id IN (6, 7))';
        $query = '
            CREATE TEMPORARY TABLE IF NOT EXISTS s_plugin_viison_pickware_mobile_analytics_pick_pack_events_temp (
                userID INT,
                pickCount INT,
                packCount INT,
                workdays INT
            )
            SELECT
                orderHistory.userID,
                SUM(CASE WHEN ' . $isPickCondition . ' THEN 1 ELSE 0 END) AS pickCount,
                SUM(CASE WHEN ' . $isPackConditiion . ' THEN 1 ELSE 0 END) AS packCount,
                COUNT(DISTINCT
                  CASE WHEN (' . $isPickCondition . ' OR ' . $isPackConditiion . ')
                  THEN CAST(orderHistory.change_date AS DATE)
                  ELSE NULL
                  END
                ) AS workdays
            FROM
                s_order_history orderHistory
            LEFT JOIN
                s_order shopwareOrder
                ON orderHistory.orderID = shopwareOrder.id
            WHERE
                orderHistory.userID IS NOT NULL';
        $sqlParameters = [];
        if ($this->getFromDate()) {
            $query .= ' AND orderHistory.change_date >= ?';
            $sqlParameters[] = $this->getFromDate()->format('Y-m-d H:i:s');
        }
        if ($this->getToDate()) {
            $query .= ' AND orderHistory.change_date <= ?';
            $sqlParameters[] = $this->getToDate()->format('Y-m-d H:i:s');
        }
        if ($this->getSelectedShopIds()) {
            $query .= ' AND shopwareOrder.subshopID IN (' . implode(', ', $this->getSelectedShopIds()) . ')';
        }
        $query .= ' GROUP BY orderHistory.userID';
        $this->get('db')->query($query, $sqlParameters);
    }

    /**
     * @inheritdoc
     */
    protected function getDefaultSorting()
    {
        return [
            [
                'property' => 'pickTimeInSeconds',
                'direction' => 'DESC',
            ],
        ];
    }
}
