<?php

namespace VGM\WOBundle\Repository;

use Doctrine\ORM\EntityRepository;

/**
 * WorkerRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class WorkerRepository extends EntityRepository
{
    public function getWorkers($max = null, $offset = null)
    {
        $qb = $this->createQueryBuilder('w')->orderBy('w.name, w.lastname');

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

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

        $query = $qb->getQuery();

        return $query->getResult();
    }

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

        $query = $qb->getQuery();

        return $query->getSingleScalarResult();
    }

    public function getWorkersByDepartment($id = null)
    {
        if($id){
            $qb = $this->createQueryBuilder('w')
                ->where('w.department = :id')
                ->setParameter('id', $id);

            $query = $qb->getQuery();

            $workers = $query->getResult();

            return count($workers) > 0  ? $workers : null;
        }
        return null;
    }

    public function getWorkersByNameCode($name_code = null)
    {
        if($name_code){
            $qb = $this->createQueryBuilder('w')
                ->where('w.name LIKE :name_code OR w.lastname LIKE :name_code OR w.worker_code LIKE :name_code')
                ->setParameter('name_code', '%'.$name_code.'%');

            $query = $qb->getQuery();

            $workers = $query->getResult();

            return count($workers) > 0 ? $workers : null;
        }
        return null;
    }

    public function getWorkersByName($name = null)
    {
        if($name){
            $qb = $this->createQueryBuilder('w')
                ->where('w.name LIKE :name OR w.lastname LIKE :name')
                ->setParameter('name', '%'.$name.'%');

            $query = $qb->getQuery();

            $workers = $query->getResult();

            return count($workers) > 0 ? $workers : null;
        }
        return null;
    }

    public function getWorkerByCode($code = null)
    {
        if($code){
            $qb = $this->createQueryBuilder('w')
                ->where('w.worker_code = :code')
                ->setParameter('code', $code);

            $query = $qb->getQuery();

            $workers = $query->getResult();
            if(count($workers) == 1)
                return $workers[0];
            return null;
        }
        return null;
    }

    public function getWorkersByCode($code = null)
    {
        if($code){
            $qb = $this->createQueryBuilder('w')
                ->where('w.worker_code LIKE :code')
                ->setParameter('code', '%'.$code.'%');

            $query = $qb->getQuery();

            $workers = $query->getResult();

            return count($workers) > 0 ? $workers : null;
        }
        return null;
    }

    public function getWorkersByDepNameCode($dep = null, $name_code = null)
    {
        if($dep && $name_code){
            $qb = $this->createQueryBuilder('w')
                ->where('w.department = :id AND (w.worker_code LIKE :code OR w.name LIKE :code OR w.lastname LIKE :code)')
                ->setParameter('id', $dep)
                ->setParameter('code', '%'.$name_code.'%');

            $query = $qb->getQuery();

            $workers = $query->getResult();

            return count($workers) > 0 ? $workers : null;
        }
        return null;
    }

    public function getWorkersByDepCode($dep = null, $code = null)
    {
        if($code){
            $qb = $this->createQueryBuilder('w')
                ->where('w.worker_code LIKE :code')
                ->setParameter('code', '%'.$code.'%');

            $query = $qb->getQuery();

            $workers = $query->getResult();

            return count($workers) > 0 ? $workers : null;
        }
        return null;
    }

    public function getSupervisors()
    {
        $qb = $this->createQueryBuilder('sw')
            ->where('sw.supervisor = :sup')
            ->setParameter('sup', true)
            ->addOrderBy('sw.name', 'ASC')
            ->addOrderBy('sw.lastname', 'ASC');

        $query = $qb->getQuery();

        $workers = $query->getResult();

        return count($workers) > 0 ? $workers : null;
    }

    public function getFilteredActiveWorkers($filters, $sorters, $max = null, $offset = null){
        $previous_where = false;
        $qb = $this->createQueryBuilder("wo")
            ->leftJoin("wo.department", "dep")
            ->leftJoin("wo.unit", "unit")
            ->where("wo.status_active = :active")
            ->setParameter("active", true);

        //Apply Filters
        if($filters['filter_code']){
            $qb->andWhere('wo.worker_code = :wo_code');

            $qb->setParameter('wo_code', $filters['filter_code']);
        }

        if($filters['filter_dep']){
            $qb->andWhere('dep.id IN (:deps)');
            $qb->setParameter('deps', $filters['filter_dep']);
        }

        if($filters['filter_name']){
            $qb->andWhere('wo.name LIKE :name OR wo.lastname LIKE :name');
            $qb->setParameter('name', '%'.$filters['filter_name'].'%');
        }

        if($filters['filter_units']){
            $qb->andWhere('unit.id IN (:units)');
            $qb->setParameter('units', $filters['filter_units']);
        }

        //Apply Sorter
        if($sorters['sorter_code'])
            $qb->orderBy('wo.worker_code', $sorters['sorter_code']);
        if($sorters['sorter_name'])
            $qb->orderBy('wo.name', $sorters['sorter_name']);
        if($sorters['sorter_dep'])
            $qb->orderBy('dep.name', $sorters['sorter_dep']);

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

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

        $query = $qb->getQuery();

        return $query->getResult();
    }

    public function countFilteredWorkers($filters, $sorters){
        $previous_where = false;
        $qb = $this->createQueryBuilder("wo")
            ->leftJoin("wo.department", "dep")
            ->select("count(wo.id)")
            ->where("wo.status_active = :active")
            ->setParameter("active", true);

        //Apply Filters
        if($filters['filter_code']){
            $qb->where('wo.worker_code = :wo_code');
            $qb->setParameter('wo_code', $filters['filter_code']);
        }

        if($filters['filter_dep']){
            $qb->andWhere('dep.id IN (:deps)');
            $qb->setParameter('deps', $filters['filter_dep']);
        }

        if($filters['filter_name']){
            $qb->andWhere('wo.name LIKE :name OR wo.lastname LIKE :name');
            $qb->setParameter('name', '%'.$filters['filter_name'].'%');
        }

        $query = $qb->getQuery();

        return $query->getSingleScalarResult();
    }

    public function getWorkerWithOvertimeByDate($date){
        $date_from = $date;
        $date_from->setTime(0, 0, 0);
        $date_to = clone $date_from;
        $date_to->modify('+1 day');

        $qb = $this->createQueryBuilder("w")
            ->innerJoin("w.loops", "ll")
            ->where("ll.date >= :date_from")
            ->andwhere("ll.date < :date_to")
            ->having("(sum(ll.total_time)) > 8")
            ->groupBy("ll.worker")
            ->addOrderBy("ll.date")
            ->setParameter('date_from', $date_from)
            ->setParameter('date_to', $date_to);

        $query = $qb->getQuery();

        return $query->getResult();
    }
    public function getWorkerWithOvertimeByDateRange($date1, $date2){
        $date_from = $date1;
        $date_from->setTime(0, 0, 0);

        $date_to = $date2;
        $date_to->setTime(0, 0, 0);
        $date_to->modify('+1 day');

        $qb = $this->createQueryBuilder("w")
            ->innerJoin("w.shifts", "ll")
            ->where("ll.date >= :date_from")
            ->andwhere("ll.date < :date_to")
            ->having("(sum(ll.total_time)) <= 17")
            ->groupBy("ll.worker")
            ->setParameter('date_from', $date_from)
            ->setParameter('date_to', $date_to);

        $query = $qb->getQuery();

        return $query->getResult();
    }
}
