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 $losId
* @param $groupLosId
* @return mixed
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function findOneMilestoneByLosId(Site $site, $losId, $groupLosId)
{
//$siteIds = $site->getParentSiteIds();
//array_push($siteIds, $site->getId());
$q = $this->createQueryBuilder('m')->innerJoin('m.milestone_group', 'g')->where('m.los_id = :los_id')->andWhere('g.site = :site_id')->andWhere('g.los_id = :group_los_id')->setParameter('group_los_id', $groupLosId)->setParameter('site_id', $site->getId())->setParameter('los_id', $losId)->getQuery();
return $q->getOneOrNullResult();
}
/**
* @param Site $site
* @return array
*/
public function findAllByParentSites(Site $site)
{
$siteIds = $site->getParentSiteIds();
array_push($siteIds, $site->getId());
$q = $this->createQueryBuilder('q')->where('q.site IN (:site_ids)')->setParameter('site_ids', $siteIds)->getQuery();
$results = $q->getResult();
return $results;
}
/**
* @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 mixed
*/
public function countBySite(Site $site)
{
$siteIds = $site->getParentSiteIds();
array_push($siteIds, $site->getId());
$q = $this->createQueryBuilder('o')->select('count(o.id)')->where('o.site IN (:site_ids)')->setParameter('site_ids', $siteIds)->getQuery();
return $q->getSingleScalarResult();
}
/**
* @param Site $site
* @param User $user
* @return array
*/
public function findNewMessagesCountByUserQuery(Site $site, User $user)
{
$q = $this->createQueryBuilder('l')->select('count(t.id) as messages')->join('l.message_thread', 't')->join('t.message', 'm')->where('l.site = :site')->andWhere('m.user != :user_id')->andWhere('l.user = :user_id')->andWhere('l.deleted = 0')->andWhere('m.status = 0')->setParameter('user_id', $user->getId())->setParameter('site', $site->getId());
//echo $q->getDQL(); exit;
return $q->getQuery()->getScalarResult();
}
