<?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\ViisonSetArticles\Subscribers\Backend;

use Doctrine\DBAL\Query\Expression\CompositeExpression;
use Enlight_Event_EventArgs;
use Shopware\Plugins\ViisonCommon\Classes\Subscribers\Base;
use Shopware\Plugins\ViisonCommon\Classes\Util\Util as ViisonCommonUtil;

/**
 * Manipulates some selections and computations by the PickwareERP Analytics controller
 */
class AnalyticsSubscriber extends Base
{
    /**
     * @inheritdoc
     */
    public static function getSubscribedEvents()
    {
        return [
            'Shopware_Plugins_ViisonPickwareERP_AnalyticsGrossProfit_beforeDataAccess' => 'onBeforeDataAccess',
            'Shopware_Plugins_ViisonPickwareERP_AnalyticsGrossProfit_AddStockTypeFilter' => 'onAddStockTypeFilter',
            'Shopware_Plugins_ViisonPickwareERP_AnalyticsGrossProfit_AddFromDateFilter' => 'onFromDateFilter',
            'Shopware_Plugins_ViisonPickwareERP_AnalyticsGrossProfit_AddToDateFilter' => 'onToDateFilter',
            'Shopware_Plugins_ViisonPickwareERP_AnalyticsGrossProfit_AdditionalJoins' => 'onAdditionalJoins',
            'Shopware_Plugins_ViisonPickwareERP_AnalyticsGrossProfit_AdditionalFilter' => 'onAdditionalFilter',
            'Shopware_Plugins_ViisonPickwareERP_AnalyticsGrossProfit_SalesQuantitySelection' => 'onSalesQuantitySelection',
            'Shopware_Plugins_ViisonPickwareERP_AnalyticsGrossProfit_PurchaseValueGrossSelection' => 'onPurchaseValueGrossSelection',
            'Shopware_Plugins_ViisonPickwareERP_AnalyticsGrossProfit_PurchaseValueNetSelection' => 'onPurchaseValueNetSelection',
        ];
    }

    /**
     * This event is used before the analytics is started. Create a temporary table to decide which set articles and
     * purchases should be used considering the given time window. This way all following restrictions can be reduced to
     * one access of this table.
     *
     * All conditions must be handled in this table.
     *
     * Remark negative changeAmount: Since sales are negative Stockchanges, invert them to create positive values.
     *
     * @param Enlight_Event_EventArgs $args
     */
    public function onBeforeDataAccess(Enlight_Event_EventArgs $args)
    {
        if ($args->get('purchasePriceMode') === 'net') {
            $selectPurchasePriceNetSQL = '(-1 * subArticlesStocks.changeAmount) * subArticlesStocks.purchasePrice';
            $selectPurchasePriceGrossSQL = '(-1 * subArticlesStocks.changeAmount) * (subArticlesStocks.purchasePrice * (1 + (subArticleTax.tax / 100)))';
        } else {
            $selectPurchasePriceNetSQL = '(-1 * subArticlesStocks.changeAmount) * (subArticlesStocks.purchasePrice / (1 + (subArticleTax.tax / 100)))';
            $selectPurchasePriceGrossSQL = '(-1 * subArticlesStocks.changeAmount) * subArticlesStocks.purchasePrice';
        }

        $stockLedgerEntriesTableName = (ViisonCommonUtil::isPluginInstalledAndActive(null, 'ViisonPickwareERP', '5.0.0')) ? 'pickware_erp_stock_ledger_entries' : 's_plugin_pickware_stocks';

        $query = '
            CREATE TEMPORARY TABLE s_plugin_viison_setarticles_tmp_gross_profit_analytics (
                orderDetailId INT,
                isPicked INT,
                shippedQuantity INT,
                purchasePriceSumNet FLOAT,
                purchasePriceSumGross FLOAT,
                PRIMARY KEY (orderDetailId)
            )
            SELECT
                orderDetail.id AS orderDetailId,
                (count(*) > 0) AS isPicked,
                orderDetail.shipped AS shippedQuantity,
                SUM(' . $selectPurchasePriceNetSQL . ') AS purchasePriceSumNet,
                SUM(' . $selectPurchasePriceGrossSQL . ') AS purchasePriceSumGross

            FROM s_order_details orderDetail

            LEFT JOIN s_order_details_attributes orderDetailAttributes
            ON orderDetail.id = orderDetailAttributes.detailID

            LEFT JOIN s_order_details_attributes subArticleOrderDetailAttributes
            ON subArticleOrderDetailAttributes.viison_setarticle_orderid = orderDetail.id

            LEFT JOIN s_order_details subArticleOrderDetail
            ON subArticleOrderDetail.id = subArticleOrderDetailAttributes.detailID

            LEFT JOIN `' . $stockLedgerEntriesTableName . '` AS `subArticlesStocks`
            ON subArticleOrderDetail.id = subArticlesStocks.orderDetailId

            LEFT JOIN s_articles_details subArticleArticleDetail
            ON subArticleArticleDetail.id = subArticlesStocks.articleDetailId

            LEFT JOIN s_articles subArticleArticle
            ON subArticleArticle.id = subArticleArticleDetail.articleID

            LEFT JOIN s_core_tax subArticleTax
            ON subArticleTax.id = subArticleArticle.taxID

            WHERE (orderDetailAttributes.viison_setarticle_orderid = orderDetail.id)
            AND subArticleOrderDetailAttributes.viison_setarticle_orderid = orderDetail.id
            AND (subArticlesStocks.type = ? OR subArticlesStocks.type = ?)';

        // Set parameters and add restrictions if necessary
        $sqlParameters = [
            'sale',
            'return',
        ];
        $fromDate = $args->get('fromDate');
        $toDate = $args->get('toDate');
        if ($fromDate) {
            $query .= 'AND subArticlesStocks.created > ?';
            $sqlParameters[] = $fromDate->format('Y-m-d H:i:s');
        }
        if ($toDate) {
            $query .= 'AND subArticlesStocks.created < ?';
            $sqlParameters[] = $toDate->format('Y-m-d H:i:s');
        }

        // Add group-by and execute SQL to create temporary table
        $query .= 'GROUP BY orderDetail.id';
        Shopware()->Db()->query($query, $sqlParameters);
    }

    /**
     * Manipulate stock entry filter (sell/purchase) and allow set articles that were picked.
     *
     * @param Enlight_Event_EventArgs $args
     * @return CompositeExpression
     */
    public function onAddStockTypeFilter(Enlight_Event_EventArgs $args)
    {
        $builder = $this->get('dbal_connection')->createQueryBuilder();
        $filter = $args->getReturn();

        $filter = $builder->expr()->orX(
            $filter,
            $builder->expr()->eq(
                'SetArticleAnalytics.isPicked',
                1
            )
        );

        return $filter;
    }

    /**
     * Manipulate the from-date filter and allow set articles that were picked.
     *
     * @param Enlight_Event_EventArgs $args
     * @return CompositeExpression
     */
    public function onFromDateFilter(Enlight_Event_EventArgs $args)
    {
        $builder = $this->get('dbal_connection')->createQueryBuilder();
        $filter = $args->getReturn();

        $filter = $builder->expr()->orX(
            $filter,
            $builder->expr()->eq(
                'SetArticleAnalytics.isPicked',
                1
            )
        );

        return $filter;
    }

    /**
     * Manipulate the to-date filter and allow set articles that were picked.
     *
     * @param Enlight_Event_EventArgs $args
     * @return CompositeExpression
     */
    public function onToDateFilter(Enlight_Event_EventArgs $args)
    {
        $builder = $this->get('dbal_connection')->createQueryBuilder();
        $filter = $args->getReturn();

        $filter = $builder->expr()->orX(
            $filter,
            $builder->expr()->eq(
                'SetArticleAnalytics.isPicked',
                1
            )
        );

        return $filter;
    }

    /**
     * Join set/sub article information to the calculate purchase prices afterwards.
     *
     * @param Enlight_Event_EventArgs $args
     * @return array
     */
    public function onAdditionalJoins(Enlight_Event_EventArgs $args)
    {
        $joins = $args->getReturn();

        // Add temporary table to select actual set article information
        $joins[] = [
            'orderDetail',
            's_plugin_viison_setarticles_tmp_gross_profit_analytics',
            'SetArticleAnalytics',
            'SetArticleAnalytics.orderDetailId = orderDetail.id',
        ];

        return $joins;
    }

    /**
     * Add article filter to remove sub articles. This filter only allows regular articles and (main) set articles.
     *
     * @param Enlight_Event_EventArgs $args
     * @return CompositeExpression
     */
    public function onAdditionalFilter(Enlight_Event_EventArgs $args)
    {
        $builder = $this->get('dbal_connection')->createQueryBuilder();
        $filter = $args->getReturn();

        /**
         * Only allow articles that have no SetArticleOrderId (regular articles), or the SetArticleOrderId is
         * equal to the orderDetailId (set article). This removes sub article entries from the query.
         */
        $filter[] = $builder->expr()->orX(
            $builder->expr()->isNull(
                'orderDetailAttribute.viison_setarticle_orderid'
            ),
            $builder->expr()->eq(
                'orderDetailAttribute.viison_setarticle_orderid',
                'orderDetail.id'
            )
        );

        return $filter;
    }

    /**
     * Manipulates the sales amount for sales calculation. Regular articles use the stockchange value. Since set
     * articles do not have stock entries, use the amount of shipped orderDetails instead.
     *
     * @param Enlight_Event_EventArgs $args
     * @return string
     */
    public function onSalesQuantitySelection(Enlight_Event_EventArgs $args)
    {
        $selection = $args->getReturn();

        $selection = 'CASE WHEN SetArticleAnalytics.isPicked = 1
            THEN SetArticleAnalytics.shippedQuantity
            ELSE (' . $selection . ')
            END';

        return $selection;
    }

    /**
     * Since purchase price, quantity and tax selection is rather complicated, we manipulate and return the full "value"
     * selection as a whole. This includes quantity, purchase price and tax.
     *
     * Remark quantity: We do not use a quantity factor since we already summed up all sub articles in the purchase
     * price selection in our temporary table.
     *
     * @param Enlight_Event_EventArgs $args
     * @return string
     */
    public function onPurchaseValueGrossSelection(Enlight_Event_EventArgs $args)
    {
        $originalSelection = $args->getReturn();

        $selection = 'CASE WHEN SetArticleAnalytics.isPicked = 1
            THEN SetArticleAnalytics.purchasePriceSumGross
            ELSE (' . $originalSelection . ')
            END';

        return $selection;
    }

    /**
     * Since purchase price, quantity and tax selection is rather complicated, we manipulate and return the full "value"
     * selection as a whole. This includes quantity, purchase price and tax.
     *
     * Remark quantity: We do not use a quantity factor since we already summed up all sub articles in the purchase
     * price selection in our temporary table.
     *
     * @param Enlight_Event_EventArgs $args
     * @return string
     */
    public function onPurchaseValueNetSelection(Enlight_Event_EventArgs $args)
    {
        $originalSelection = $args->getReturn();

        $selection = 'CASE WHEN SetArticleAnalytics.isPicked = 1
            THEN SetArticleAnalytics.purchasePriceSumNet
            ELSE (' . $originalSelection . ')
            END';

        return $selection;
    }
}
