public function getGlobals() { if (isset($this->site)) { $siteIds = $this->site->getChildSiteIds(); array_push($siteIds, $this->site->getId()); $connection = $this->container->get('doctrine')->getConnection(); $sql = "SELECT s.name, t.name AS type, d.domain, (SELECT COUNT(l.id) FROM lead l WHERE l.lead_status_id = 1 AND l.site_id=s.id) AS leads, "; $sql .= "(SELECT COUNT(a.id) FROM loan_application a WHERE a.status <= 1 AND a.deleted = 0 AND a.site_id=s.id) AS loans FROM site s "; $sql .= "INNER JOIN domain d ON s.primary_domain_id=d.id "; $sql .= "LEFT JOIN site_type t ON s.site_type_id=t.id "; $sql .= sprintf("WHERE s.id IN (%s) ", implode(',', $siteIds)); $sql .= "GROUP BY s.id"; $stmt = $connection->prepare($sql); //echo $sql; exit; $stmt->execute(); $siteSummary = $stmt->fetchAll(); // get loans $qb = $this->em->createQueryBuilder()->select('count(l.id)')->from('SudouxMortgageBundle:LoanApplication', 'l')->where('l.status = 0 OR l.status = 1')->andWhere('l.site IN (:site_ids)')->andWhere('l.deleted = 0')->setParameter('site_ids', $siteIds); $newLoans = $qb->getQuery()->getSingleScalarResult(); // get docs return array('newLoans' => $newLoans, 'siteSummary' => $siteSummary); } else { return array(); } }
/** * @param Site $site * @param $guid * @return mixed * @throws \Doctrine\ORM\NonUniqueResultException */ public function findOneBySiteAndGuid(Site $site, $guid) { $siteIds = $site->getChildSiteIds(); array_push($siteIds, $site->getId()); $q = $this->createQueryBuilder('q')->leftJoin('q.additional_site', 'a')->where('q.site IN (:site_ids)')->orWhere('a.id = :site_id')->andWhere('q.guid = :guid')->andWhere('q.deleted = 0')->setParameter('guid', $guid)->setParameter('site_ids', $siteIds)->setParameter('site_id', $site->getId())->getQuery(); return $q->getOneOrNullResult(); }
/** * @param Site $site * @param $limit * @param $offset * @return array */ public function findBySiteNotSentToLos(Site $site, $limit, $offset) { $losConnection = $site->getSettings()->getInheritedLos(); $siteIds = $site->getChildSiteIds(); array_push($siteIds, $site->getId()); if (isset($losConnection)) { $q = $this->createQueryBuilder('d')->leftJoin('d.loan', 'l')->where('l.site IN (:site_ids)')->andWhere('d.los_id IS NULL')->andWhere('l.los_id IS NOT NULL')->andWhere('l.deleted = 0')->setParameter('site_ids', $siteIds)->setFirstResult($offset)->setMaxResults($limit)->getQuery(); return $q->getResult(); } else { return; } }
/** * @param Site $site * @return \DateTime */ public function findLastModifiedBySite(Site $site) { $siteIds = array(); $parentSiteIds = $site->getParentSiteIds(); $childSiteIds = $site->getChildSiteIds(); foreach ($parentSiteIds as $siteId) { array_push($siteIds, $siteId); } foreach ($childSiteIds as $siteId) { array_push($siteIds, $siteId); } array_push($siteIds, $site->getId()); $q = $this->createQueryBuilder('q')->select('q.modified')->where('q.site IN (:site_ids)')->andWhere('q.active = 1')->andWhere('q.deleted = 0')->setParameter('site_ids', $siteIds)->orderBy('q.modified', 'DESC')->setMaxResults(1); $results = $q->getQuery()->getScalarResult(); if (count($results) == 0) { return new \DateTime(); } else { return new \DateTime($results[0]['modified']); } }