<?php

namespace VGM\WOBundle\Repository;

use Doctrine\ORM\EntityRepository;

/**
 * LaborLoopRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class LaborLoopRepository extends EntityRepository
{
    public function getFilteredLoopsByWorkOrder($work_order_id, $filters = null, $sorters = null){
        $qb = $this->createQueryBuilder("loop")
            ->leftJoin('loop.worker', 'worker')
            ->leftJoin('loop.department', 'dep')
            ->where('loop.workorder = :work_order_id')
            ->setParameter('work_order_id', $work_order_id);

        //Apply Filters
        if($filters != null){
            if($filters['filter_shift_date']){
                $date_from = $filters['filter_shift_date']['sdate'];
                $date_from->setTime(0, 0, 0);
                $date_to = clone $date_from;
                $date_to->modify('+1 day');

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

                $qb->andwhere('loop.date >= :started_from');
                $qb->andWhere('loop.date < :started_to');
                $qb->setParameter('started_from', $date_from)
                    ->setParameter('started_to', $date_to);
            }

            if($filters['filter_shift_dep']){
                $qb->andWhere('loop.department = :dep_id');
                $qb->setParameter('dep_id', $filters['filter_shift_dep']);
            }

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

            if($filters['filter_shift_description']){
                $qb->andWhere('loop.description LIKE :description');
                $qb->setParameter('description', '%'.$filters['filter_shift_description'].'%');
            }

            if($filters['filter_shift_asn']){
                $qb->andWhere('loop.assigned_stock_no = :asn');
                $qb->setParameter('asn', $filters['filter_shift_asn']);
            }

            if($filters['filter_shift_area']){
            $qb->andWhere('loop.area LIKE :area');
            $qb->setParameter('area', '%'.$filters['filter_shift_area'].'%');
        }
        }
        //Apply Sorter
        if($sorters != null){
            if($sorters['sorter_shift_dep'])
                $qb->orderBy('dep.name', $sorters['sorter_shift_dep']);
            if($sorters['sorter_shift_worker'])
                $qb->orderBy('worker.name', $sorters['sorter_shift_worker']);
            if($sorters['sorter_shift_description'])
                $qb->orderBy('loop.description', $sorters['sorter_shift_description']);
            if($sorters['sorter_shift_date'])
                $qb->orderBy('loop.date', $sorters['sorter_shift_date']);
            if($sorters['sorter_shift_asn'])
                $qb->orderBy('loop.assigned_stock_no', $sorters['sorter_shift_asn']);
            if($sorters['sorter_shift_area'])
                $qb->orderBy('loop.area', $sorters['sorter_shift_area']);
        }

        $query = $qb->getQuery();

        $loops = $query->getResult();

        return $loops;
    }

    public function getUnfinishedLoopsByWorker_Date($worker_id, $date = null){
        $qb = $this->createQueryBuilder("loop")
            ->leftJoin('loop.workorder', 'workorder')
            ->where('loop.worker = :worker_id')
            ->andWhere('loop.finished_at is NULL')
            ->orderBy("workorder.number")
            ->addOrderBy("loop.assigned_stock_no")
            ->setParameter('worker_id', $worker_id);

        //Apply Filters
        if($date != null){
            $date_from = $date;
            $date_from->setTime(0, 0, 0);
            $date_to = clone $date_from;
            $date_to->modify('+1 day');

            $qb->andwhere('loop.date >= :started_from');
            $qb->andWhere('loop.date < :started_to');
            $qb->setParameter('started_from', $date_from)
                ->setParameter('started_to', $date_to);
        }

        $query = $qb->getQuery();

        $loops = $query->getResult();

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

    public function countFilteredLoopsByWorkOrder($work_order_id, $filters = null){
        $qb = $this->createQueryBuilder("loop")
            ->leftJoin('loop.worker', 'worker')
            ->leftJoin('loop.department', 'dep')
            ->where('loop.workorder = :work_order_id')
            ->setParameter('work_order_id', $work_order_id);

        //Apply Filters
        if($filters != null){
            if($filters['filter_shift_date']){
                $date_from = $filters['filter_shift_date']['sdate'];
                $date_from->setTime(0, 0, 0);
                $date_to = clone $date_from;
                $date_to->modify('+1 day');

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

                $qb->where('loop.started_at >= :started_from');
                $qb->andWhere('loop.started_at < :started_to');
                $qb->setParameter('started_from', $date_from)
                    ->setParameter('started_to', $date_to);

                $previous_where = true;
            }

            if($filters['filter_shift_dep']){
                if($previous_where)
                    $qb->andWhere('loop.department = :dep_id');
                else
                    $qb->where('loop.department = :dep_id');

                $qb->setParameter('dep_id', $filters['filter_shift_dep']);

                $previous_where = true;
            }

            if($filters['filter_shift_worker']){
                if($previous_where)
                    $qb->andWhere('worker.name LIKE :name OR worker.lastname LIKE :name');
                else
                    $qb->where('worker.name LIKE :name OR worker.lastname LIKE :name');

                $qb->setParameter('name', '%'.$filters['filter_shift_worker'].'%');

                $previous_where = true;
            }

            if($filters['filter_shift_description']){
                if($previous_where)
                    $qb->andWhere('loop.description LIKE :description');
                else
                    $qb->where('loop.description LIKE :description');

                $qb->setParameter('description', '%'.$filters['filter_shift_description'].'%');

                $previous_where = true;
            }

            if($filters['filter_shift_asn']){
                if($previous_where)
                    $qb->andWhere('loop.assigned_stock_no = :asn');
                else
                    $qb->where('loop.assigned_stock_no = :asn');

                $qb->setParameter('asn', '%'.$filters['filter_shift_asn'].'%');

                $previous_where = true;
            }

            if($filters['filter_shift_area']){
            if($previous_where)
                $qb->andWhere('loop.area LIKE :area');
            else
                $qb->where('loop.area LIKE :area');

            $qb->setParameter('area', '%'.$filters['filter_shift_area'].'%');

            $previous_where = true;
        }
        }

        $query = $qb->getQuery();

        $loops = $query->getResult();

        return count($loops);
    }

    public function getFilteredLoopsByWorker($worker_id, $filters = null, $sorters = null){
        $qb = $this->createQueryBuilder("loop")
            ->leftJoin('loop.worker', 'worker')
            ->leftJoin('loop.department', 'dep')
            ->leftJoin('loop.workorder', 'workorder')
            ->where('loop.worker = :worker_id')
            ->setParameter('worker_id', $worker_id);

        //Apply Filters
        if($filters != null){
            if($filters['filter_shift_date']){
                $date_from = $filters['filter_shift_date']['sdate'];
                $date_from->setTime(0, 0, 0);
                $date_to = clone $date_from;
                $date_to->modify('+1 day');

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

                $qb->andwhere('loop.date >= :started_from');
                $qb->andWhere('loop.date < :started_to');
                $qb->setParameter('started_from', $date_from)
                    ->setParameter('started_to', $date_to);
            }

            if($filters['filter_shift_dep']){
                $qb->andWhere('loop.department = :dep_id');
                $qb->setParameter('dep_id', $filters['filter_shift_dep']);
            }

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

            if($filters['filter_shift_description']){
                $qb->andWhere('loop.description LIKE :description');
                $qb->setParameter('description', '%'.$filters['filter_shift_description'].'%');
            }

            if($filters['filter_shift_asn']){
                $qb->andWhere('loop.assigned_stock_no = :asn');
                $qb->setParameter('asn', $filters['filter_shift_asn']);
            }

            if($filters['filter_shift_area']){
                $qb->andWhere('loop.area LIKE :area');
                $qb->setParameter('area', '%'.$filters['filter_shift_area'].'%');
            }

            if($filters['filter_shift_workorder']){
            $qb->andWhere('workorder.number = :workorder');
            $qb->setParameter('workorder', $filters['filter_shift_workorder']);
        }
        }

        //Apply Sorter
        if($sorters != null){
            if($sorters['sorter_shift_dep'])
                $qb->orderBy('dep.name', $sorters['sorter_shift_dep']);
            if($sorters['sorter_shift_worker'])
                $qb->orderBy('worker.name', $sorters['sorter_shift_worker']);
            if($sorters['sorter_shift_description'])
                $qb->orderBy('loop.description', $sorters['sorter_shift_description']);
            if($sorters['sorter_shift_date']){
                $qb->orderBy('loop.date', $sorters['sorter_shift_date']);
                $qb->addOrderBy('loop.started_at', 'ASC');
            }
            if($sorters['sorter_shift_asn'])
                $qb->orderBy('loop.assigned_stock_no', $sorters['sorter_shift_asn']);
            if($sorters['sorter_shift_area'])
                $qb->orderBy('loop.area', $sorters['sorter_shift_area']);
            if($sorters['sorter_shift_workorder'])
                $qb->orderBy('workorder.number', $sorters['sorter_shift_workorder']);
        }

        $query = $qb->getQuery();

        $loops = $query->getResult();

        return $loops;
    }

    public function getFilteredLoopsByWorkers($workers_ids, $filters = null, $sorters = null){
        $qb = $this->createQueryBuilder("loop")
            ->leftJoin('loop.worker', 'worker')
            ->leftJoin('loop.department', 'dep')
            ->leftJoin('loop.workorder', 'workorder')
            ->where('loop.worker IN (:workers_ids)')
            ->setParameter('workers_ids', $workers_ids);

        //Apply Filters
        if($filters != null){
            if($filters['filter_shift_date']){
                $date_from = $filters['filter_shift_date']['sdate'];
                $date_from->setTime(0, 0, 0);
                $date_to = clone $date_from;
                $date_to->modify('+1 day');

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

                $qb->andwhere('loop.date >= :started_from');
                $qb->andWhere('loop.date < :started_to');
                $qb->setParameter('started_from', $date_from)
                    ->setParameter('started_to', $date_to);
            }

            if($filters['filter_shift_dep']){
                $qb->andWhere('loop.department = :dep_id');
                $qb->setParameter('dep_id', $filters['filter_shift_dep']);
            }

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

            if($filters['filter_shift_description']){
                $qb->andWhere('loop.description LIKE :description');
                $qb->setParameter('description', '%'.$filters['filter_shift_description'].'%');
            }

            if($filters['filter_shift_asn']){
                $qb->andWhere('loop.assigned_stock_no = :asn');
                $qb->setParameter('asn', $filters['filter_shift_asn']);
            }

            if($filters['filter_shift_area']){
                $qb->andWhere('loop.area LIKE :area');
                $qb->setParameter('area', '%'.$filters['filter_shift_area'].'%');
            }

            if($filters['filter_shift_workorder']){
            $qb->andWhere('workorder.number = :workorder');
            $qb->setParameter('workorder', $filters['filter_shift_workorder']);
        }
        }
        //Apply Sorter
        if($filters != null){
            if($sorters['sorter_shift_dep'])
                $qb->orderBy('dep.name', $sorters['sorter_shift_dep']);
            if($sorters['sorter_shift_worker'])
                $qb->orderBy('worker.name', $sorters['sorter_shift_worker']);
            if($sorters['sorter_shift_description'])
                $qb->orderBy('loop.description', $sorters['sorter_shift_description']);
            if($sorters['sorter_shift_date']){
                $qb->orderBy('loop.date', $sorters['sorter_shift_date']);
                $qb->addOrderBy('loop.started_at', 'ASC');
            }
            if($sorters['sorter_shift_asn'])
                $qb->orderBy('loop.assigned_stock_no', $sorters['sorter_shift_asn']);
            if($sorters['sorter_shift_area'])
                $qb->orderBy('loop.area', $sorters['sorter_shift_area']);
            if($sorters['sorter_shift_workorder'])
                $qb->orderBy('workorder.number', $sorters['sorter_shift_workorder']);
        }

        $query = $qb->getQuery();

        $loops = $query->getResult();

        return $loops;
    }

    public function countFilteredLoopsByWorker($worker_id, $filters = null){
        $qb = $this->createQueryBuilder("loop")
            ->leftJoin('loop.worker', 'worker')
            ->leftJoin('loop.department', 'dep')
            ->where('loop.workorder = :worker_id')
            ->setParameter('worker_id', $worker_id);

        //Apply Filters
        if($filters != null){
            if($filters['filter_shift_date']){
                $date_from = $filters['filter_shift_date']['sdate'];
                $date_from->setTime(0, 0, 0);
                $date_to = clone $date_from;
                $date_to->modify('+1 day');

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

                $qb->where('loop.started_at >= :started_from');
                $qb->andWhere('loop.started_at < :started_to');
                $qb->setParameter('started_from', $date_from)
                    ->setParameter('started_to', $date_to);

                $previous_where = true;
            }

            if($filters['filter_shift_dep']){
                if($previous_where)
                    $qb->andWhere('loop.department = :dep_id');
                else
                    $qb->where('loop.department = :dep_id');

                $qb->setParameter('dep_id', $filters['filter_shift_dep']);

                $previous_where = true;
            }

            if($filters['filter_shift_worker']){
                if($previous_where)
                    $qb->andWhere('worker.name LIKE :name OR worker.lastname LIKE :name');
                else
                    $qb->where('worker.name LIKE :name OR worker.lastname LIKE :name');

                $qb->setParameter('name', '%'.$filters['filter_shift_worker'].'%');

                $previous_where = true;
            }

            if($filters['filter_shift_description']){
                if($previous_where)
                    $qb->andWhere('loop.description LIKE :description');
                else
                    $qb->where('loop.description LIKE :description');

                $qb->setParameter('description', '%'.$filters['filter_shift_description'].'%');

                $previous_where = true;
            }

            if($filters['filter_shift_asn']){
                if($previous_where)
                    $qb->andWhere('loop.assigned_stock_no = :asn');
                else
                    $qb->where('loop.assigned_stock_no = :asn');

                $qb->setParameter('asn', '%'.$filters['filter_shift_asn'].'%');

                $previous_where = true;
            }

            if($filters['filter_shift_area']){
                if($previous_where)
                    $qb->andWhere('loop.area LIKE :area');
                else
                    $qb->where('loop.area LIKE :area');

                $qb->setParameter('area', '%'.$filters['filter_shift_area'].'%');

                $previous_where = true;
            }

            if($filters['filter_shift_workorder']){
            $qb->andWhere('workorder.number = :workorder');
            $qb->setParameter('workorder', $filters['filter_shift_workorder']);
        }
        }

        $query = $qb->getQuery();

        $loops = $query->getResult();

        return count($loops);
    }

    public function getOneLoopPerDayWorker($worker_id){
        $qb = $this->createQueryBuilder("loop")
            ->where('loop.worker = :worker_id')
            ->setParameter('worker_id', $worker_id)
            ->groupBy('loop.date');

        $qb->orderBy('loop.date')
            ->addOrderBy('loop.started_at');

        $query = $qb->getQuery();

        return $query->getResult();
    }

    public function getOneLoopPerDayWorkerBetweenDates($worker_id, $date1, $date2){
        $qb = $this->createQueryBuilder("loop")
            ->where('loop.worker = :worker_id')
            ->setParameter('worker_id', $worker_id)
            ->groupBy('loop.date');

        //Apply Filters
        $date_from = $date1;
        $date_from->setTime(0, 0, 0);

        $date_to = $date2;

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

        $qb->andWhere('loop.date >= :started_from');
        $qb->andWhere('loop.date < :started_to');
        $qb->setParameter('started_from', $date_from)
            ->setParameter('started_to', $date_to);

        $qb->orderBy('loop.date')
            ->addOrderBy('loop.started_at');

        $query = $qb->getQuery();

        return $query->getResult();
    }

    public function getLoopsByWorkerDate($worker_id, $date){
        $qb = $this->createQueryBuilder("loop")
            ->where('loop.worker = :worker_id')
            ->setParameter('worker_id', $worker_id);

        //Apply Filters
        if($date){
            $date_from = $date;
            $date_from->setTime(0, 0, 0);
            $date_to = clone $date_from;
            $date_to->modify('+1 day')->modify('-1 minute');

            $qb->andWhere('loop.date >= :started_from');
            $qb->andWhere('loop.date < :started_to');
            $qb->setParameter('started_from', $date_from)
                ->setParameter('started_to', $date_to);
        }

        $qb->orderBy('loop.date')
           ->addOrderBy('loop.started_at');

        $query = $qb->getQuery();

        return $query->getResult();
    }

    public function getLoopsSumByWorkerDate($worker_id, $date){
        $qb = $this->createQueryBuilder("loop")
            ->select('SUM(loop.total_time)')
            ->where('loop.worker = :worker_id')
            ->setParameter('worker_id', $worker_id);

        //Apply Filters
        if($date){
            $date_from = $date;
            $date_from->setTime(0, 0, 0);
            $date_to = clone $date_from;
            $date_to->modify('+1 day');

            $qb->andWhere('loop.date >= :started_from');
            $qb->andWhere('loop.date < :started_to');
            $qb->setParameter('started_from', $date_from)
                ->setParameter('started_to', $date_to);
        }

        $query = $qb->getQuery();

        return $query->getSingleScalarResult();
    }

    public function getIfWeekIsClosed($worker_id, $date1, $date2){
        $qb = $this->createQueryBuilder("loop")
            ->where('loop.worker = :worker_id')
            ->setParameter('worker_id', $worker_id);

        //Apply Filters
        if($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->andWhere('loop.date >= :started_from');
            $qb->andWhere('loop.date < :started_to');
            $qb->setParameter('started_from', $date_from)
                ->setParameter('started_to', $date_to);
        }

        $qb->andWhere('loop.week_closed = 0');

        $qb->orderBy('loop.date')
            ->addOrderBy('loop.started_at');

        $query = $qb->getQuery();

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

    public function getLoopsByWorkerWeek($worker_id, $date1, $date2){
        $qb = $this->createQueryBuilder("loop")
            ->where('loop.worker = :worker_id')
            ->setParameter('worker_id', $worker_id);

        //Apply Filters
        if($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->andWhere('loop.date >= :started_from');
            $qb->andWhere('loop.date < :started_to');
            $qb->setParameter('started_from', $date_from)
                ->setParameter('started_to', $date_to);
        }

        $qb->orderBy('loop.date')
            ->addOrderBy('loop.started_at');

        $query = $qb->getQuery();

        return $query->getResult();
    }

    public function getLoopsSumByWorkerWeek($worker_id, $date1, $date2){
        $qb = $this->createQueryBuilder("loop")
            ->select('SUM(loop.total_time)')
            ->where('loop.worker = :worker_id')
            ->setParameter('worker_id', $worker_id);

        //Apply Filters

        $date_from = $date1;
        $date_from->setTime(0, 0, 0);


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


        $qb->andWhere('loop.date >= :started_from');
        $qb->andWhere('loop.date < :started_to');
        $qb->setParameter('started_from', $date_from)
            ->setParameter('started_to', $date_to);


        $query = $qb->getQuery();

        return $query->getSingleScalarResult();
    }

    public function getLoopsWithPOvertime($worker_id, $date1, $date2){
        $qb = $this->createQueryBuilder("loop")
            ->where('loop.worker = :worker_id')
            ->setParameter('worker_id', $worker_id);

        //Apply Filters

        $date_from = $date1;
        $date_from->setTime(0, 0, 0);

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

        $qb->andWhere('loop.date >= :started_from');
        $qb->andWhere('loop.date < :started_to');
        $qb->setParameter('started_from', $date_from)
            ->setParameter('started_to', $date_to);

        $qb->andWhere('loop.p_overtime > 0');
        $qb->andWhere('loop.p_overtime is not null');

        $qb->orderBy('loop.date')
            ->addOrderBy('loop.started_at');

        $query = $qb->getQuery();

        return $query->getResult();
    }

    public function getLoopsWithUOvertime($worker_id, $date1, $date2){
        $qb = $this->createQueryBuilder("loop")
            ->where('loop.worker = :worker_id')
            ->setParameter('worker_id', $worker_id);

        //Apply Filters

        $date_from = $date1;
        $date_from->setTime(0, 0, 0);

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

        $qb->andWhere('loop.date >= :started_from');
        $qb->andWhere('loop.date < :started_to');
        $qb->setParameter('started_from', $date_from)
            ->setParameter('started_to', $date_to);

        $qb->andWhere('loop.p_overtime < 0');
        $qb->andWhere('loop.p_overtime is not null');

        $qb->orderBy('loop.date')
            ->addOrderBy('loop.started_at');

        $query = $qb->getQuery();

        return $query->getResult();
    }
}
