<?php

use KupShop\AdminBundle\AdminList\BaseList;

class OrdersOfSuppliersList extends BaseList
{
    protected $tableDef = [
        'id' => 'id_supplier',
        'fields' => [
            'supplier' => ['translate' => true, 'field' => 'name'],
            'numOfItems' => ['translate' => true, 'field' => 'pieces'],
            'totalPrice' => ['translate' => true, 'field' => 'price', 'render' => 'renderPrice'],
            'dateCreated' => ['translate' => true, 'field' => 'date_create'],
        ],
    ];

    public function getQuery()
    {
        $priceBuyQuery = $this->getBuyPriceQuery();
        $qb = sqlQueryBuilder();
        $qb->select('oos.id_supplier, COUNT(*) as count, s.name, SUM(oos.pieces) as pieces, SUM(oos.pieces*COALESCE('.$priceBuyQuery.', pv.price, p.price)) as price, MIN(oos.date_create) as date_create')
            ->from('orders_of_suppliers', 'oos')
            ->leftJoin('oos', 'products', 'p', 'p.id = oos.id_product')
            ->leftJoin('oos', 'products_variations', 'pv', 'pv.id = oos.id_variation')
            ->leftJoin('oos', 'suppliers', 's', 's.id = oos.id_supplier')
            ->groupBy('oos.id_supplier');

        return $qb;
    }

    protected function getBuyPriceQuery()
    {
        $sqlPrice = [];

        if (findModule(\Modules::PRODUCTS_SUPPLIERS)) {
            $sqlPrice[] = 'SELECT pos.price_buy
             FROM products_of_suppliers pos
             WHERE pos.id_supplier=oos.id_supplier 
               AND oos.id_product=pos.id_product 
               AND ((oos.id_variation IS NULL AND pos.id_variation IS NULL) OR oos.id_variation=pos.id_variation) 
               AND pos.price_buy > 0';
        }

        if (findModule(\Modules::PRODUCTS, \Modules::SUB_PRICE_BUY)) {
            $sqlPrice[] = 'SELECT COALESCE(pv.price_buy, p.price_buy)
             FROM products p
             LEFT JOIN products_variations pv ON p.id = pv.id_product
             WHERE oos.id_product=p.id 
               AND ((oos.id_variation IS NULL AND pv.id IS NULL) OR oos.id_variation=pv.id) 
               AND COALESCE(pv.price_buy, p.price_buy) > 0
             LIMIT 1';
        }

        $sqlPrice[] = 'SELECT sii.price
             FROM stock_in_items sii
             LEFT JOIN stock_in si ON si.id = sii.id_stock_in
             WHERE si.id_supplier=oos.id_supplier AND si.id_index="invoice" 
               AND oos.id_product=sii.id_product 
               AND ((oos.id_variation IS NULL AND sii.id_variation IS NULL) OR oos.id_variation=sii.id_variation) 
               AND sii.price > 0
             ORDER BY si.date_issued DESC
             LIMIT 1';

        return 'COALESCE(('.join('),(', $sqlPrice).'))';
    }
}