<?php
/**
 * Created by PhpStorm.
 * User: Olyanren
 * Date: 30.10.2016
 * Time: 18:56
 */

namespace AppBundle\Utils;


use AppBundle\Entity\Product;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\ORM\EntityManager;
use Doctrine\ORM\Tools\Pagination\Paginator;

class DoctrineUtil
{
    private $entityManager;

    public function __construct(EntityManager $entityManager)
    {
        $this->entityManager = $entityManager;
    }

    public function getTotalCount($entityName)
    {
        $dql = "SELECT count(i.id) as itemCount FROM AppBundle:$entityName i";
        $query = $this->entityManager->createQuery($dql);
        return $query->getResult()[0]["itemCount"];
    }

    public function getMacId($value)
    {
        $dql = "SELECT m.id as id FROM AppBundle:Mac m WHERE m.value=:value";
        $query = $this->entityManager->createQuery($dql)
            ->setParameter('value', $value);
        $result = $query->getResult();
        if (count($result) == 0)
            return null;
        return $result[0]["id"];
    }
    public function getMac($value)
    {
        $dql = "SELECT m FROM AppBundle:Mac m WHERE m.value=:value";
        $query = $this->entityManager->createQuery($dql)
            ->setParameter('value', $value);
        $result = $query->getResult();
        if (count($result) == 0)
            return null;
        return $result[0];
    }
    public function getEntityPagination($entity,$startIndex, $endIndex)
    {
        $dql = "SELECT t FROM AppBundle:".$entity . " t order by t.id DESC";
        $query = $this->entityManager->createQuery($dql)
            ->setFirstResult($startIndex)
            ->setMaxResults($endIndex - $startIndex);
        return $query->getResult();
    }
    public function getProductSearchResult($q)
    {
        $dql = "SELECT t FROM AppBundle:Product t INNER JOIN t.person p WHERE p.email LIKE :email" .
            " order by t.id DESC";
        $query = $this->entityManager->createQuery($dql)
            ->setParameter('email', '%'.$q.'%');
        return $query->getResult();
    }
    public function getProduct($mac_value)
    {
        $dql = "SELECT p FROM AppBundle:Product p INNER JOIN p.mac m WHERE m.value=:value";
        $query = $this->entityManager->createQuery($dql)
            ->setParameter('value', $mac_value);
        $result = $query->getResult();
        if (count($result) == 0)
            return new Product();
        return $result[0];
    }
    public function getDefaultProductGroup(){
        $dql = "SELECT pg FROM AppBundle:ProductGroup pg WHERE pg.name=:name";
        $query = $this->entityManager->createQuery($dql)
            ->setParameter('name', 'Default');
        $result = $query->getResult();
        if (count($result) == 0)
            return null;
        return $result[0];
    }
    /**
     * @param $productId
     * @return mixed
     */
    public function getLastPrice($productId)
    {
        $query = $this->entityManager->createQuery("SELECT ph.price FROM AppBundle:ProductHistory ph 
INNER JOIN ph.product p WHERE p.id=:productId ORDER BY ph.id DESC")
            ->setParameter('productId', $productId)
            ->setMaxResults(1);
        return $query->getResult()[0]["price"];
    }

    /**
     * @param $faturaNo
     * @return mixed
     */
    public function getInvoiceViaFaturaNo($faturaNo)
    {
        $query = $this->entityManager->createQuery("SELECT '1' FROM AppBundle:Invoice ph WHERE ph.no=:faturaNo")
            ->setParameter('faturaNo', $faturaNo)
            ->setMaxResults(1);
        return count($query->getResult()) > 0;
    }

    /**
     * @param $locationId
     * @param $productgroup
     * @param $kdv
     * @param $measure
     * @param $minBakiye
     * @return ArrayCollection
     * @internal param $keyword
     * @internal param $startDate
     * @internal param $endDate
     * @internal param $pageNumber
     * @internal param $location
     */
    public function getProducts($locationId, $productgroup, $kdv, $measure, $minBakiye)
    {
        if (empty($productgroup)) $productgroup = null;
        if (empty($kdv)) $kdv = null;
        if (empty($measure)) $measure = null;
        if (empty($locationId)) return new ArrayCollection();
        if (empty($minBakiye)) $minBakiye = null;

        $dql = "SELECT p,ph,dh,invoice FROM AppBundle:Product p INNER JOIN p.productHistories ph INNER JOIN p.deliveryHistories dh";
        if ($productgroup != null) $dql .= " INNER JOIN p.productGroup pg";
        if ($kdv != null) $dql .= " INNER JOIN p.kdv kdv";
        if ($measure != null) $dql .= " INNER JOIN p.measure m";
        if ($locationId != null) $dql .= "  INNER JOIN ph.invoice invoice INNER JOIN invoice.location invloc INNER JOIN dh.delivery d INNER JOIN d.location loc INNER JOIN d.fromLocation fLocation";

        if ($productgroup != null || $kdv != null || $measure != null || $minBakiye != null || $locationId != null) $dql .= " WHERE";
        if ($productgroup != null) {
            $dql .= " pg.id =:productgroup";
        }
        if ($kdv != null) {
            if ($productgroup != null) $dql .= " AND";
            $dql .= " kdv.id =:kdv";
        }
        if ($measure != null) {
            if ($productgroup != null || $kdv != null) $dql .= " AND";
            $dql .= " m.id =:measure";
        }
        if ($locationId != null) {
            if ($productgroup != null || $kdv != null || $measure != null) $dql .= " AND";
            $dql .= " loc.id =:locationId OR";
            $dql .= " fLocation.id =:locationId OR";
            $dql .= " invloc.id =:locationId";
        }
        $dql .= " ORDER BY p.id DESC";
        $query = $this->entityManager->createQuery($dql);
        if ($productgroup != null) $query->setParameter('productgroup', $productgroup);
        if ($kdv != null) $query->setParameter('kdv', $kdv);
        if ($measure != null) $query->setParameter('measure', $measure);
        if ($locationId != null) $query->setParameter('locationId', $locationId);

        $paginator = new Paginator($query, $fetchJoinCollection = true);
        $products = new ArrayCollection();
        foreach ($paginator as $product) {
            $lastPrice = $this->getLastPrice($product->getId());
            if ($lastPrice < $minBakiye)
                continue;
            $product->setPrice($lastPrice);
            $this->setAmounts($locationId, $product);
            $products->add($product);
        }
        return $products;
    }

    /**
     * @param $sevkVeFirma
     * @param $keyword
     * @param $startDate
     * @param $endDate
     * @param $companyId
     * @param null $locationId
     * @return ArrayCollection
     * @internal param $location
     */
    public function getProductWithInvoiceAndDeliveries($sevkVeFirma, $keyword, $startDate, $endDate, $companyId, $locationId = null)
    {
        if (empty($sevkVeFirma)) $sevkVeFirma[] = "1";
        if (empty($keyword)) $keyword = null;
        if (empty($startDate)) $startDate = null;
        if (empty($endDate)) $endDate = null;
        if (empty($companyId)) $companyId = null;
        $isSevk = in_array("1", $sevkVeFirma);
        $isFatura = in_array("2", $sevkVeFirma);
        $isLocationSelected = $isSevk && $locationId != null;
        $isCompanySelected = $isFatura && $companyId != null;

        $dql = "SELECT prod";
        if ($isSevk) $dql .= ",dh";
        if ($isFatura) $dql .= ",ph";

        $dql .= " FROM AppBundle:Product prod";
        if ($isSevk) $dql .= " INNER JOIN prod.deliveryHistories dh";
        if ($isFatura) $dql .= " INNER JOIN prod.productHistories ph";

        if ($isCompanySelected) $dql .= " INNER JOIN ph.invoice i INNER JOIN i.company c";

        if ($isLocationSelected) $dql .= " INNER JOIN dh.delivery d INNER JOIN d.location loc";
        if ($isLocationSelected || $keyword != null || $startDate != null) $dql .= " WHERE";

        if ($keyword != null) $dql .= " prod.name =:prodName";

        if ($isLocationSelected) {
            if ($keyword != null) $dql .= " AND";
            $dql .= " loc.id =:locationId";
        }
        if ($isCompanySelected) {
            if ($keyword != null || $isLocationSelected) $dql .= " AND";
            $dql .= " c.id =:companyId";
        }
        if ($startDate != null) {
            if ($keyword != null || $isLocationSelected || $isCompanySelected) $dql .= " AND";
            if ($isSevk)
                $dql .= " dh.creationDate >= :startDate";
            else if ($isFatura)
                $dql .= " ph.creationDate >= :startDate";
        }
        if ($endDate != null) {
            if ($keyword != null || $startDate != null || $isLocationSelected || $isCompanySelected) $dql .= " AND";
            if ($isSevk)
                $dql .= " dh.creationDate <= :endDate";
            else if ($isFatura)
                $dql .= " ph.creationDate <= :endDate";
        }
        if ($isSevk)
            $dql .= " ORDER BY dh.creationDate";
        else if ($isFatura)
            $dql .= " ORDER BY ph.creationDate";

        $query = $this->entityManager->createQuery($dql);
        if ($keyword != null) $query->setParameter('prodName', $keyword);
        if ($isLocationSelected) $query->setParameter('locationId', $locationId);

        if ($isCompanySelected) $query->setParameter('companyId', $companyId);

        if ($startDate != null) $query->setParameter('startDate', date_create_from_format('d.m.Y', $startDate));
        if ($endDate != null) $query->setParameter('endDate', date_create_from_format('d.m.Y', $endDate));

        /* $query->setFirstResult(($pageNumber - 1) * 5)
             ->setMaxResults(($pageNumber) * 5);
         */
        $paginator = new Paginator($query, $fetchJoinCollection = true);
        $products = new ArrayCollection();
        foreach ($paginator as $product) {
            //$invoice->setPrice($this->getLastPrice($invoice->getId()));
            $products->add($product);
        }
        return $products[0];
    }

    /**
     * @param $deliveryId
     * @return mixed
     */
    public function getTotalDeliveryPrice($deliveryId)
    {
        $query = $this->entityManager->createQuery('SELECT dh FROM AppBundle:DeliveryHistory dh 
INNER JOIN dh.delivery d WHERE d.id=:deliveryId')
            ->setParameter('deliveryId', $deliveryId);
        $sum = 0;
        foreach ($query->getResult() as $deliveryHistory) {
            $lastPrice = $this->getLastPrice($deliveryHistory->getProduct()->getId());
            $sum += $lastPrice * $deliveryHistory->getAmount();
        }
        return $sum;
    }

    /**
     * @param $locationId
     * @param $product
     */
    public function setAmounts($locationId, $product)
    {
        $amount = 0;
        $inputAmount = 0;
        $outputAmount = 0;
        foreach ($product->getDeliveryHistories() as $deliveryHistory) {
            $delivery = $deliveryHistory->getDelivery();
            if ($delivery->getFromLocation() != null && $delivery->getFromLocation()->getId() == $locationId) {
                $amount -= $deliveryHistory->getAmount();
                $outputAmount += $deliveryHistory->getAmount();
            }
            if ($delivery->getLocation() != null && $delivery->getLocation()->getId() == $locationId) {
                $amount += $deliveryHistory->getAmount();
                $inputAmount += $deliveryHistory->getAmount();
            }
        }
        foreach ($product->getProductHistories() as $productHistory) {
            $invoice = $productHistory->getInvoice();
            if ($invoice != null && $invoice->getLocation()->getId() == $locationId) {
                $amount += $productHistory->getAmount();
                $inputAmount += $productHistory->getAmount();
            }
        }
        $product->setAmount($amount);
        $product->setInputAmount($inputAmount);
        $product->setOutputAmount($outputAmount);

    }

}