<?php

namespace VGM\WOBundle\Repository;

use Doctrine\ORM\EntityRepository;
use Symfony\Component\Validator\Constraints\DateTime;
use Doctrine\ORM\Query;

/**
 * WorkOrderRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class WorkOrderRepository extends EntityRepository
{
    public function getWorkOrders($max = null, $offset = null)
    {
        $qb = $this->createQueryBuilder('w')
            ->orderBy('w.created_at', 'DESC')
            ->where('w.status <> :status')
            ->setParameter('status', "Current");

        if($max)
        {
            $qb->setMaxResults($max);
        }

        if($offset)
        {
            $qb->setFirstResult($offset);
        }

        $query = $qb->getQuery();

        return $query->getResult();
    }
    public function getArchivedWorkOrders($max = null, $offset = null)
    {
        $qb = $this->createQueryBuilder('w')
            ->orderBy('w.created_at', 'DESC')
            ->where('w.status <> :status')
            ->setParameter('status', "Current");

        if($max)
        {
            $qb->setMaxResults($max);
        }

        if($offset)
        {
            $qb->setFirstResult($offset);
        }

        $query = $qb->getQuery();

        return $query->getResult();
    }

    public function countWorkOrders()
    {
        $qb = $this->createQueryBuilder('c')->select('count(c.id)');

        $query = $qb->getQuery();

        return $query->getSingleScalarResult();
    }

    public function nextWorkOrderCode($first_code)
    {
        $qb = $this->createQueryBuilder('c')->select('count(c.id)')
        ->where("c.number != 9999");
        $query = $qb->getQuery();
        $cant = $query->getSingleScalarResult();

        if($cant > 0)
        {
            $qb = $this->createQueryBuilder('w')->orderBy('w.number');
            $query = $qb->getQuery();
            $list = $query->getResult();

            $int_number = (int)($list[$cant - 1]->getNumber());
            $int_number = $int_number + 1;

            $string_code = (string)$int_number;

            while(strlen($string_code) < 4)
                $string_code = '0'.$string_code;

            return $string_code;
        }

        return $first_code;
    }

    public function getStockModel($stock_code)
    {
        $em = $this->getDoctrine()->getManager('aeroservicios');

        $query = $em->createQuery("SELECT s.Model as model FROM Items WHERE StockNo =:stock")->setParameter('stock', $stock_code);
        $model = $query->getSingleResult();

        if($model)
            return $model;

        return $stock_code;
    }

    public function getWorOrderByNo($WONo)
    {
        if($this->countWorOrdersByNo($WONo) == 1){
            $qb = $this->createQueryBuilder('wo')
                ->where('wo.number = :number')
                ->setParameter('number', $WONo);

            $query = $qb->getQuery();

            return $query->getSingleResult();
        }
        return null;
    }

    public function countWorOrdersByNo($WONo)
    {
        $qb = $this->createQueryBuilder('wo')
            ->select('count(wo.id)')
            ->where('wo.number = :number')
            ->setParameter('number', $WONo);

        $query = $qb->getQuery();

        return $query->getSingleScalarResult();
    }

    public function getWorOrdersBySupervisor($sup_id)
    {
        $qb = $this->createQueryBuilder('wo')
            ->where('wo.supervisor = :sup')
            ->setParameter('sup', $sup_id);

        $query = $qb->getQuery();

        return $query->getResult();
    }

    public function getFilteredWorkOrders($filters, $sorters){
        $previous_where = false;
        $qb = $this->createQueryBuilder("wo")
            ->leftJoin("wo.equipment", "eqt")
            ->leftJoin("wo.client", "clt")
            ->leftJoin("wo.stocks_full", "sf")
            ->leftJoin("wo.worktype", "wt")
            ->leftJoin("sf.stock", "st")
            ->leftJoin("wo.shifts", "ll");

        //Apply Filters
        if($filters['filter_created']){
            $created_from = $filters['filter_created']['sdate'];
            $created_from->setTime(0, 0, 0);
            $created_to = clone $created_from;
            $created_to->modify('+1 day');

            if($filters['filter_created']['edate']){
                $created_to = $filters['filter_created']['edate'];
                $created_to->setTime(0, 0, 0);
                $created_to->modify('+1 day');
            }

            $qb->where('wo.created_at >= :created_from');
            $qb->andWhere('wo.created_at < :created_to');
            $qb->setParameter('created_from', $created_from)
               ->setParameter('created_to', $created_to);

            $previous_where = true;
        }

        if($filters['filter_started']){
            $started_from = $filters['filter_started']['sdate'];
            $started_from->setTime(0, 0, 0);
            $started_to = clone $started_from;
            $started_to->modify('+1 day');

            if($filters['filter_started']['edate']){
                $started_to = $filters['filter_started']['edate'];
                $started_to->setTime(0, 0, 0);
                $started_to->modify('+1 day');
            }

            if($previous_where)
                $qb->andWhere('wo.started_at >= :started_from');
            else
                $qb->where('wo.started_at >= :started_from');

            $qb->andWhere('wo.started_at < :started_to');
            $qb->setParameter('started_from', $started_from)
                ->setParameter('started_to', $started_to);

            $previous_where = true;
        }

        if($filters['filter_wo']){
            if($previous_where)
                $qb->andWhere('wo.number = :wo_number');
            else
                $qb->where('wo.number = :wo_number');

            $qb->setParameter('wo_number', $filters['filter_wo']);

            $previous_where = true;
        }

        if($filters['filter_stock']){
            if($previous_where)
                $qb->andWhere('st.code = :st_code');
            else
                $qb->where('st.code = :st_code');

            $qb->setParameter('st_code', $filters['filter_stock']);

            $previous_where = true;
        }

        if($filters['filter_sup']){
            if($previous_where)
                $qb->andWhere('wo.supervisor = :sup_id');
            else
                $qb->where('wo.supervisor = :sup_id');

            $qb->setParameter('sup_id', $filters['filter_sup']);

            $previous_where = true;
        }

        if($filters['filter_eqt']){
            if($previous_where)
                $qb->andWhere('wo.equipment = :eqt_id');
            else
                $qb->where('wo.equipment = :eqt_id');

            $qb->setParameter('eqt_id', $filters['filter_eqt']);

            $previous_where = true;
        }

        if($filters['filter_wt']){
            if($previous_where)
                $qb->andWhere('wo.worktype = :wt_id');
            else
                $qb->where('wo.worktype = :wt_id');

            $qb->setParameter('wt_id', $filters['filter_wt']);

            $previous_where = true;
        }

        if($filters['filter_clt']){
            if($previous_where)
                $qb->andWhere('clt.name LIKE :clt_name');
            else
                $qb->where('clt.name LIKE :clt_name');

            $qb->setParameter('clt_name', '%'.$filters['filter_clt'].'%');

            $previous_where = true;
        }

        //Apply Sorter
        if($sorters['sorter_created'])
            $qb->orderBy('wo.created_at', $sorters['sorter_created']);
        if($sorters['sorter_started'])
            $qb->orderBy('wo.started_at', $sorters['sorter_started']);
        if($sorters['sorter_wo'])
            $qb->orderBy('wo.number', $sorters['sorter_wo']);
        if($sorters['sorter_sup'])
            $qb->orderBy('wo.supervisor', $sorters['sorter_sup']);
        if($sorters['sorter_eqt'])
            $qb->orderBy('eqt.code', $sorters['sorter_eqt']);
        if($sorters['sorter_wt'])
            $qb->orderBy('wt.work_type', $sorters['sorter_wt']);
        if($sorters['sorter_clt'])
            $qb->orderBy('clt.name', $sorters['sorter_clt']);

        $query = $qb->getQuery();

        return $query->getResult();
    }

    public function countFilteredWorkOrders($filters, $sorters){
        $previous_where = false;
        $qb = $this->createQueryBuilder("wo")
            ->leftJoin("wo.client", "clt")
            ->leftJoin("wo.stocks_full", "sf")
            ->leftJoin("wo.worktype", "wt")
            ->leftJoin("sf.stock", "st")
            ->leftJoin("wo.shifts", "ll");

        //Apply Filters
        if($filters['filter_created']){
            $created_from = $filters['filter_created']['sdate'];
            $created_from->setTime(0, 0, 0);
            $created_to = clone $created_from;
            $created_to->modify('+1 day');

            if($filters['filter_created']['edate']){
                $created_to = $filters['filter_created']['edate'];
                $created_to->setTime(0, 0, 0);
                $created_to->modify('+1 day');
            }

            $qb->where('wo.created_at >= :created_from');
            $qb->andWhere('wo.created_at < :created_to');
            $qb->setParameter('created_from', $created_from)
                ->setParameter('created_to', $created_to);

            $previous_where = true;
        }

        if($filters['filter_started']){
            $started_from = $filters['filter_started']['sdate'];
            $started_from->setTime(0, 0, 0);
            $started_to = clone $started_from;
            $started_to->modify('+1 day');

            if($filters['filter_started']['edate']){
                $started_to = $filters['filter_started']['edate'];
                $started_to->setTime(0, 0, 0);
                $started_to->modify('+1 day');
            }

            if($previous_where)
                $qb->andWhere('wo.started_at >= :started_from');
            else
                $qb->where('wo.started_at >= :started_from');

            $qb->andWhere('wo.started_at < :started_to');
            $qb->setParameter('started_from', $started_from)
                ->setParameter('started_to', $started_to);

            $previous_where = true;
        }

        if($filters['filter_wo']){
            if($previous_where)
                $qb->andWhere('wo.number = :wo_number');
            else
                $qb->where('wo.number = :wo_number');

            $qb->setParameter('wo_number', $filters['filter_wo']);

            $previous_where = true;
        }

        if($filters['filter_stock']){
            if($previous_where)
                $qb->andWhere('st.code = :st_code');
            else
                $qb->where('st.code = :st_code');

            $qb->setParameter('st_code', $filters['filter_stock']);

            $previous_where = true;
        }

        if($filters['filter_sup']){
            if($previous_where)
                $qb->andWhere('wo.supervisor = :sup_id');
            else
                $qb->where('wo.supervisor = :sup_id');

            $qb->setParameter('sup_id', $filters['filter_sup']);

            $previous_where = true;
        }

        if($filters['filter_eqt']){
            if($previous_where)
                $qb->andWhere('wo.equipment = :eqt_id OR st.equipment = :eqt_id');
            else
                $qb->where('wo.equipment = :eqt_id OR st.equipment = :eqt_id');

            $qb->setParameter('eqt_id', $filters['filter_eqt']);

            $previous_where = true;
        }

        if($filters['filter_wt']){
            if($previous_where)
                $qb->andWhere('wo.worktype = :wt_id');
            else
                $qb->where('wo.worktype = :wt_id');

            $qb->setParameter('wt_id', $filters['filter_wt']);

            $previous_where = true;
        }

        if($filters['filter_clt']){
            if($previous_where)
                $qb->andWhere('clt.name LIKE :clt_name');
            else
                $qb->where('clt.name LIKE :clt_name');

            $qb->setParameter('clt_name', '%'.$filters['filter_clt'].'%');

            $previous_where = true;
        }

        $query = $qb->getQuery();

        return count($query->getResult());
    }
}
