<?php

namespace VGM\WOBundle\Repository;

use Doctrine\ORM\EntityRepository;

/**
 * WorkOrderStockAssociationRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class WorkOrderStockAssociationRepository extends EntityRepository
{
    public function getAssociationByWoStock($wo_id, $stock_id)
    {
        if($this->countAssociationByWoStock($wo_id, $stock_id) == 1){
            $qb = $this->createQueryBuilder('ass')
                ->where('ass.workorder = :wo_id and ass.stock = :stock_id')
                ->setParameter('wo_id', $wo_id)
                ->setParameter('stock_id', $stock_id);

            $query = $qb->getQuery();

            return $query->getSingleResult();
        }
        return null;
    }
    public function getAssociationByWoAsNumber($wo_id, $as_number)
    {
        if($this->countAssociationByWoAsNumber($wo_id, $as_number) == 1){
            $qb = $this->createQueryBuilder('ass')
                ->where('ass.workorder = :wo_id and ass.as_number = :as_number')
                ->setParameter('wo_id', $wo_id)
                ->setParameter('as_number', $as_number);

            $query = $qb->getQuery();

            return $query->getSingleResult();
        }
        return null;
    }
    public function countAssociationByWoStock($wo_id, $stock_id)
    {
        $qb = $this->createQueryBuilder('ass')->select('count(ass.id)')
                ->where('ass.workorder = :wo_id and ass.stock = :stock_id')
                ->setParameter('wo_id', $wo_id)
                ->setParameter('stock_id', $stock_id);

        $query = $qb->getQuery();

        return $query->getSingleScalarResult();
    }
    public function countAssociationByWoAsNumber($wo_id, $as_number)
    {
        $qb = $this->createQueryBuilder('ass')->select('count(ass.id)')
            ->where('ass.workorder = :wo_id and ass.as_number = :as_number')
            ->setParameter('wo_id', $wo_id)
            ->setParameter('as_number', $as_number);

        $query = $qb->getQuery();

        return $query->getSingleScalarResult();
    }
    public function getAssociationByWoNumber($wo_number)
    {
        $qb = $this->createQueryBuilder('ass')
            ->leftJoin('ass.workorder', 'wo')
            ->where('wo.number = :wo_number')
            ->setParameter('wo_number', $wo_number)
            ->orderBy('ass.as_number');

        $query = $qb->getQuery();

        return $query->getResult();
    }
    public function getFilteredAssociations($filters, $sorters){
        $previous_where = false;
        $qb = $this->createQueryBuilder("association")
            ->leftJoin("association.workorder", "workorder")
            ->leftJoin("association.stock", "stock")
            ->leftJoin("association.client", "client")
            ->leftJoin("workorder.worktype", "worktype")
            ->leftJoin("stock.equipment", "equipment");

        //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('workorder.created_at >= :created_from');
            $qb->andWhere('workorder.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('workorder.started_at >= :started_from');
            else
                $qb->where('workorder.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('workorder.number = :wo_number');
            else
                $qb->where('workorder.number = :wo_number');

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

            $previous_where = true;
        }

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

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

            $previous_where = true;
        }

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

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

            $previous_where = true;
        }

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

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

            $previous_where = true;
        }

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

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

            $previous_where = true;
        }

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

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

            $previous_where = true;
        }

        if($filters['filter_make']){
            if($previous_where)
                $qb->andWhere('stock.brand LIKE :st_brand');
            else
                $qb->where('stock.brand LIKE :st_brand');

            $qb->setParameter('st_brand', '%'.$filters['filter_make'].'%');

            $previous_where = true;
        }

        if($filters['filter_model']){
            if($previous_where)
                $qb->andWhere('stock.model LIKE :st_model');
            else
                $qb->where('stock.model LIKE :st_model');

            $qb->setParameter('st_model', '%'.$filters['filter_model'].'%');

            $previous_where = true;
        }

        //Apply Sorter
        if($sorters['sorter_created'])
            $qb->orderBy('workorder.created_at', $sorters['sorter_created']);
        if($sorters['sorter_started'])
            $qb->orderBy('workorder.started_at', $sorters['sorter_started']);
        if($sorters['sorter_wo'])
            $qb->orderBy('workorder.number', $sorters['sorter_wo']);
        if($sorters['sorter_sup'])
            $qb->orderBy('workorder.supervisor', $sorters['sorter_sup']);
        if($sorters['sorter_eqt'])
            $qb->orderBy('equipment.code', $sorters['sorter_eqt']);
        if($sorters['sorter_wt'])
            $qb->orderBy('worktype.work_type', $sorters['sorter_wt']);
        if($sorters['sorter_clt'])
            $qb->orderBy('client.name', $sorters['sorter_clt']);
        if($sorters['sorter_make'])
            $qb->orderBy('stock.brand', $sorters['sorter_make']);
        if($sorters['sorter_model'])
            $qb->orderBy('stock.model', $sorters['sorter_model']);

        $query = $qb->getQuery();

        return $query->getResult();
    }
    public function countFilteredAssociations($filters, $sorters){
        $previous_where = false;
        $qb = $this->createQueryBuilder("association")
            ->leftJoin("association.workorder", "workorder")
            ->leftJoin("association.stock", "stock")
            ->leftJoin("association.client", "client")
            ->leftJoin("workorder.worktype", "worktype")
            ->leftJoin("stock.equipment", "equipment");

        //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('workorder.created_at >= :created_from');
            $qb->andWhere('workorder.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('workorder.started_at >= :started_from');
            else
                $qb->where('workorder.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('workorder.number = :wo_number');
            else
                $qb->where('workorder.number = :wo_number');

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

            $previous_where = true;
        }

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

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

            $previous_where = true;
        }

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

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

            $previous_where = true;
        }

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

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

            $previous_where = true;
        }

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

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

            $previous_where = true;
        }

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

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

            $previous_where = true;
        }

        if($filters['filter_make']){
            if($previous_where)
                $qb->andWhere('stock.brand LIKE :st_brand');
            else
                $qb->where('stock.brand LIKE :st_brand');

            $qb->setParameter('st_brand', '%'.$filters['filter_make'].'%');

            $previous_where = true;
        }

        if($filters['filter_model']){
            if($previous_where)
                $qb->andWhere('stock.model LIKE :st_model');
            else
                $qb->where('stock.model LIKE :st_model');

            $qb->setParameter('st_model', '%'.$filters['filter_model'].'%');

            $previous_where = true;
        }

        $query = $qb->getQuery();

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