/**
* @param CtoUser $ctoUser
* @param StatisticsMastersFilterDTO $filterMastersDTO
* @return array
*/
public function getStatisticsWithMastersFilters(CtoUser $ctoUser, StatisticsMastersFilterDTO $filterMastersDTO)
{
$qb = $this->createQueryBuilder("p")->select("p, masterName, job")->leftJoin("p.master", "masterName")->leftJoin("p.carJob", "job")->where("job.cto = :cto")->setParameter("cto", $ctoUser);
if (count($filterMastersDTO->getMasters())) {
$qb->andWhere("p.master IN (:master)")->setParameter("master", array_values($filterMastersDTO->getMasters()->getValues()));
}
if ($filterMastersDTO->getDateFrom()) {
$qb->andWhere("job.jobDate >= :fromDate")->setParameter("fromDate", Carbon::createFromFormat("d.m.Y", $filterMastersDTO->getDateFrom())->startOfDay());
}
if ($filterMastersDTO->getDateTo()) {
$qb->andWhere("job.jobDate <= :toDate")->setParameter("toDate", Carbon::createFromFormat("d.m.Y", $filterMastersDTO->getDateTo())->startOfDay());
}
$qb->orderBy("job.jobDate", "DESC")->orderBy("job.id", "DESC");
return $qb->getQuery()->getResult();
}
/**
* @param CtoUser $ctoUser
* @param StatisticsMastersFilterDTO $filterMastersDTO
* @return mixed
*/
public function getStatisticsCostAndPaidSumByPaidMasters(CtoUser $ctoUser, StatisticsMastersFilterDTO $filterMastersDTO)
{
$qb = $this->createQueryBuilder("j")->select("distinct j, sum(j.totalCost) as cost, sum(j.totalSpend) as spend")->where("j.cto = :cto")->setParameter("cto", $ctoUser);
if (count($filterMastersDTO->getMasters())) {
$qb->leftJoin("j.paidSalaryJob", "paidSal")->andWhere("paidSal.master IN (:master)")->setParameter("master", array_values($filterMastersDTO->getMasters()->getValues()));
}
if ($filterMastersDTO->getDateFrom()) {
$qb->andWhere("j.jobDate >= :fromDate")->setParameter("fromDate", Carbon::createFromFormat("d.m.Y", $filterMastersDTO->getDateFrom())->startOfDay());
}
if ($filterMastersDTO->getDateTo()) {
$qb->andWhere("j.jobDate <= :toDate")->setParameter("toDate", Carbon::createFromFormat("d.m.Y", $filterMastersDTO->getDateTo())->startOfDay());
}
return $qb->getQuery()->getSingleResult();
}