<?php

namespace OfficeBrain\Bundle\CoreBundle\Entity;

use Doctrine\ORM\EntityRepository;

/**
 * CityRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class CityRepository extends EntityRepository
{
    public function getCity($stateCode='', $countryCode='')
    {
        if ($stateCode!='' || $countryCode!='') {
            $queryCommand=$this->createQueryBuilder('ct');
            $queryCommand->select('ct.id', 'ct.cityName');

            if ($stateCode!='') {
                $queryCommand->innerJoin("OfficeBrainCoreBundle:State", "st", "WITH", "ct.adminCode1 = st.stateCode");
            }
            if ($countryCode!='') {
                $queryCommand->innerJoin("OfficeBrainCoreBundle:Country", "cn", "WITH", "ct.countryCode = cn.countryCode");
            }
            if ($stateCode!='') {
                $queryCommand->where($queryCommand->expr()->in('st.id', array($stateCode)));
            }
            if ($stateCode=='' && $countryCode!='') {
                $queryCommand->where($queryCommand->expr()->in('cn.id', array($countryCode)));
            } elseif ($countryCode!='') {
                $queryCommand->andWhere($queryCommand->expr()->in('cn.id', array($countryCode)));
            }
            $queryCommand->orderBy('ct.cityName',' ASC')->groupBy('ct.cityName');

            $cityData=$queryCommand->getQuery()->getArrayResult();
        } else {
            $cityData=array();
        }
        return $cityData;
    }

    public function getCityNameById($cityId)
    {
        $queryCommand=$this->createQueryBuilder('ct');
        $queryCommand->select('ct.cityName')
        ->where('ct.id =:cityId')
        ->setParameter('cityId', $cityId);
        $cityData=$queryCommand->getQuery()->getArrayResult();
        return $cityData;
    }
}