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']);
}
}
