';
        print_r($query->getQuery()->getArrayResult()); die;*/
        //$query = $queryBuilder->getQuery();//->useResultCache(true)->setResultCacheLifetime(120)->setResultCacheId('evans-active_banner_list');

        return $this->paginatorService->paginate($query, $page, $recordPerPage);
        //$this->totalProduct = $this->paginatorRecordSet->getTotalItemCount();

        //return  $query->getQuery()->getArrayResult();
    }
	/**
     * @author Employee Id: 4493
     * OB eCommerce Product - Contact Book
     * Function created for get contact Book with search
     */

    public function getContactBookMobileApi($userId, $search, $isAddress, $page, $recordPerPage, $addressType=null)
    {
        if($isAddress == null || $isAddress == '' ){
            $isAddress = 0;
        }
        $qb = $this->createQueryBuilder('cb');
        
        $query = $qb->select('cb.id','cb.name','a.id as addressType','a.name as addressTypeLabel','cb.email','cb.street1','cb.street2','cb.countryId','co.countryName','cb.stateId','s.stateName','cb.cityId','ct.cityName','cb.zipcode','cb.extension','cb.phone','cb.mobile','cb.fax','cb.website','cb.erpId');
        
        $query->innerJoin('cb.adressType', 'a')
                    ->innerJoin('cb.user', 'u')
                    ->leftJoin('u.userCompany', 'c')
                    ->where('cb.user =:userId')
                    ->setParameter('userId', $userId)
                    ->leftJoin('OfficeBrainCoreBundle:Country', 'co', 'WITH', 'cb.countryId=co.id')
                    ->leftJoin('OfficeBrainCoreBundle:State', 's', 'WITH', 'cb.stateId=s.id')
                    ->leftJoin('OfficeBrainCoreBundle:City', 'ct', 'WITH', 'cb.cityId=ct.id');
        if (isset($search) and $search != '') {
            foreach ($search as $key=>$val) {
                if ($key == 'countryId') {                    
                    $query->andWhere('co.countryName LIKE :country')->setParameter('country', '%'. $val .'%');
                    continue;
                } elseif ($key == 'stateId') {                    
                    $query->andWhere('s.stateName LIKE :state')->setParameter('state', '%'. $val .'%');
                    continue;
                } elseif ($key == 'cityId') {                    
                    $query->andWhere('ct.cityName LIKE :city')->setParameter('city', '%'. $val .'%');
                    continue;
                } elseif ($key == 'adressType') {
                    $param = 'contact'.$key;
                    $query->andWhere('a.name LIKE :'.$param)->setParameter($param, '%'. $val .'%');
                    continue;
                } elseif ($key == 'user') {
                    $param = 'contact'.$key;
                    $query->andWhere('u.firstName LIKE :'.$param)->setParameter($param, '%'. $val .'%');
                    $query->orWhere('u.lastName LIKE :'.$param)->setParameter($param, '%'. $val .'%');
                    continue;
                } elseif ($key != '') {
                    $param = 'contact'.$key;
                    $query->andWhere('cb.'.$key.' LIKE :'.$param)->setParameter($param, '%'. $val .'%');
                }
            }
        }
        if ($isAddress == 0 || $isAddress == 1) {
            $query->andWhere('cb.isAddress =:isaddress')
                    ->setParameter('isaddress', $isAddress);
        }  
        if ($addressType != null && $addressType > 0) {
            $query->andWhere('a.id =:addressType')
                    ->setParameter('addressType', $addressType);
        }
        $query->andWhere('cb.deletedAt IS NULL');
        return $this->paginatorService->paginate($query, ((int)$page), $recordPerPage);        
    }

    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - getContactBookById
     * Function created to get Contact Book By Id
     */
    public function getContactBookByEmailSingle($email,$userId)
    {
        $query=$this->createQueryBuilder('cb');
        $query->select('cb.id','cb.name','a.id as addressType', 'a.name as addressTypeLabel','cb.email','cb.street1','cb.street2','cb.countryId','co.countryName','cb.stateId','s.stateName','cb.cityId','ct.cityName','cb.zipcode','cb.extension','cb.phone','cb.mobile','cb.fax','cb.website','cb.erpId');
        $query ->innerJoin('cb.adressType', 'a')
                    ->innerJoin('cb.user', 'u')
                    ->leftJoin('OfficeBrainCoreBundle:Country', 'co', 'WITH', 'cb.countryId=co.id')
                    ->leftJoin('OfficeBrainCoreBundle:State', 's', 'WITH', 'cb.stateId=s.id')
                    ->leftJoin('OfficeBrainCoreBundle:City', 'ct', 'WITH', 'cb.cityId=ct.id')
                    ->where('cb.user =:userId')
                    ->andWhere("cb.email = :email")
                    ->setParameter('email', $email)
                    ->setParameter('userId', $userId)
                    ;

        $query->andWhere('a.id =:id')->setParameter('id', 2);
        $query->andWhere("cb.deletedAt IS NULL");
        $query->setMaxResults(1);
        //$sql = $query->getQuery()->getSQL();echo $sql; die;
        $result=$query->getQuery()->getOneOrNullResult();
        return $result;
    }


    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - getContactBookById
     * Function created to get Contact Book By Id
     */
    public function getContactBookByUserIdSingle($userId)
    {
        $query=$this->createQueryBuilder('cb');
        $query->select('cb.id','cb.name','a.id as addressType', 'a.name as addressTypeLabel','cb.email','cb.street1','cb.street2','cb.countryId','co.countryName','cb.stateId','s.stateName','cb.cityId','ct.cityName','cb.zipcode','cb.extension','cb.phone','cb.mobile','cb.fax','cb.website','cb.erpId');
        $query ->innerJoin('cb.adressType', 'a')
                    ->innerJoin('cb.user', 'u')
                    ->leftJoin('OfficeBrainCoreBundle:Country', 'co', 'WITH', 'cb.countryId=co.id')
                    ->leftJoin('OfficeBrainCoreBundle:State', 's', 'WITH', 'cb.stateId=s.id')
                    ->leftJoin('OfficeBrainCoreBundle:City', 'ct', 'WITH', 'cb.cityId=ct.id')
                    ->where('cb.user =:userId')
                    ->setParameter('userId', $userId)
                    ;

        $query->andWhere('a.id =:id')->setParameter('id', 1);
        $query->andWhere("cb.deletedAt IS NULL");
        $query->setMaxResults(1);
        //$sql = $query->getQuery()->getSQL();echo $sql; die;
        $result=$query->getQuery()->getOneOrNullResult();
        return $result;
    }


    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - All Address
     * Function created for get all shipping and billing address (address book)(only for quote)
     */

    public function getAllAddressBook($userId, $addressType, $searchIn=array(), $isAllField=true)
    {
    	//example :$searchIn =array('email'=>'test','name'=>'test');
    	$allowedFields = array('email','name','companyName','zipcode');
    	$qb = $this->createQueryBuilder('cb');

    	if( $isAllField === true ){
    		$query = $qb->select('cb','a');
    	}else{
    		$query = $qb->select('cb.id,CONCAT(cb.name, \' (\', cb.email, \')\') as label ');
    	}
    	$query ->innerJoin('cb.adressType', 'a')
    				->innerJoin('cb.user', 'u')
    				->where('cb.user =:userId')
    				->setParameter('userId', $userId)
    				->andWhere('cb.isAddress =:isaddress')
    				->setParameter('isaddress', 1);

    	if(is_array($searchIn)){
    		$orWhere = array();
	    	foreach ($searchIn as $field => $searchKeyword) {
	    		if(in_array($field,$allowedFields)){
	    			$param = 'P'.$field;
	    			$query->setParameter($param, '%'. $searchKeyword .'%');
	    			$orWhere[] = 'cb.'.$field.' LIKE :'.$param;
	    		}
	    	}
	    	if(count($orWhere)>0){
	    		$query->andWhere(implode(' OR ',$orWhere));
	    	}
    	}
    	if($addressType && $addressType != ''){
    			$query->andWhere('a.id =:id')
    				  ->setParameter('id', $addressType);
    	}
    	$query->andWhere('cb.deletedAt IS NULL');
    	return  $query->getQuery()->getArrayResult();
    }


    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - All Address
     * Function created for get all shipping and billing address (Contact book)(only for quote)
     */

    public function getAllContactBook($userId, $addressType , $searchIn=array(), $isAllField=true)
    {

    	$allowedFields = array('email','name','companyName');
    	$qb = $this->createQueryBuilder('cb');

    	if( $isAllField === true ){
    		$query = $qb->select('cb','a');
    	}else{
    		$query = $qb->select('cb.id,CONCAT(cb.name, \' (\', cb.email, \')\') as label ');
    	}

    		$query ->innerJoin('cb.adressType', 'a')
    				->innerJoin('cb.user', 'u')
    				->where('cb.user =:userId')
    				->setParameter('userId', $userId)
					->andWhere('cb.isAddress =:isaddress')
    				->setParameter('isaddress', 0);

   		if(is_array($searchIn)){
    		$orWhere = array();
	    	foreach ($searchIn as $field => $searchKeyword) {
	    		if(in_array($field,$allowedFields)){
	    			$param = 'P'.$field;
	    			$query->setParameter($param, '%'. $searchKeyword .'%');
	    			$orWhere[] = 'cb.'.$field.' LIKE :'.$param;
	    		}
	    	}
	    	if(count($orWhere)>0){
	    		$query->andWhere(implode(' OR ',$orWhere));
	    	}
    	}
    	if($addressType && $addressType != ''){
    			$query->andWhere('a.id =:id')
    				->setParameter('id', $addressType);
    	}
    	$query->andWhere('cb.deletedAt IS NULL');
    	return  $query->getQuery()->getArrayResult();
    }


    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - Contact Book
     * Function created for delete contactBooko (hard delete)
     */
    public function deleteContactBook($contactBookId)
    {
        $queryBuilder = $this->getQueryBudler();
        $queryBuilder->delete();
        $queryBuilder->where('cb.id = :contactBookId')
        ->setParameter('contactBookId', $contactBookId);

        return $queryBuilder->getQuery()->execute();
    }

    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - update status
     * Function created to update status
     */

    public function updateIsDefault($contactBookId)
    {
	    $contactBookObj = $this->getContactBookById($contactBookId);
	    $userId = $contactBookObj->getUser()->getId();
	    $isAddress = $contactBookObj->getIsAddress();
	    $addressType = $contactBookObj->getAdressType()->getId();

	    $contactBookDefaultObj = $this->isDefaultAddress($userId, $addressType, $isAddress);

	    if (count($contactBookDefaultObj) > 0) {
	    	$contactBookDefaultObj->setIsDefault(0);
	    	$resultarrayOld = $this->updateDefaultAddress($contactBookDefaultObj);
	    }
	    $contactBookObj->setIsDefault(1);
	    return $this->updateDefaultAddress($contactBookObj);

    }


    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - update status
     * Function created to update status
     */
    public function updateDefaultAddress($contactBookObj)
    {
    	if($contactBookObj){
	    	$this->_em->persist($contactBookObj);
	    	$this->_em->flush();
	    	return array('status'=>'SUCCESS','data'=>$contactBookObj);
    	}
    	return array('status'=>'FAIL');
    }

    /**
     * @author Employee Id: 4160
     * OB eCommerce Product - getAddressBookofUser
     * Function created to get address book of User
     */
    public function getAddressBookofUser($userId, $addressType)
    {
        if($userId){
            $query=$this->createQueryBuilder('cb');
            $query->where("cb.user = :user_id")
                    ->setParameter('user_id', $userId)
                    ->andWhere('cb.isAddress = :isaddress')
                    ->setParameter('isaddress', 1)
                    ->andWhere('cb.adressType = :adressType')
                    ->setParameter('adressType', $addressType)
                    ->andWhere('cb.deletedAt IS NULL')
                    ->addOrderBy('cb.id', 'ASC');
            $result=$query->getQuery()->getOneOrNullResult();
            return $result;
        }
    }

    public function getSearchPopAddressList($userId,$option,$keywords,$page, $recordsPerPage,$addressType){
    	$ob_city_dropdown = $this->projectSetting['configuration_rule']['site_management']['option']['address']['option']['city']['option']['dropdown']['status'];
    	$ob_city_textbox = $this->projectSetting['configuration_rule']['site_management']['option']['address']['option']['city']['option']['textbox']['status'];
    	$ob_state_dropdown = $this->projectSetting['configuration_rule']['site_management']['option']['address']['option']['state']['option']['dropdown']['status'];
    	$ob_state_textbox = $this->projectSetting['configuration_rule']['site_management']['option']['address']['option']['state']['option']['textbox']['status'];
    	
        $qb = $this->createQueryBuilder('cb');
        if($addressType=='shipping_book' or $addressType=='my-billing'){
            $type=2;

        }else{
            $type=1;
        }
        $query = $qb->select('cb','s.stateName as state_name','ct.cityName as city_name','co.countryName as country_name');
        $query->where('cb.user =:userId')
                    ->setParameter('userId', $userId)
                    ->leftJoin('OfficeBrainCoreBundle:State', 's', 'WITH', 'cb.stateId=s.id')
                    ->leftJoin('OfficeBrainCoreBundle:Country', 'co', 'WITH', 'cb.countryId=co.id')
                    ->leftJoin('OfficeBrainCoreBundle:City', 'ct', 'WITH', 'cb.cityId=ct.id');
        if($option=='name'){
            $query->AndWhere('cb.name LIKE :name')->setParameter('name', '%'. $keywords .'%');

        }elseif($option=='email'){
          $query->AndWhere('cb.email LIKE :email')->setParameter('email', '%'. $keywords .'%');
        }elseif($option=='zip'){ 
           $query->AndWhere('cb.zipcode LIKE :zipcode')->setParameter('zipcode', '%'. $keywords .'%');  
        }elseif($option=='state'){
        	if($ob_state_dropdown){
        		$query->andWhere('s.stateName LIKE :state')->setParameter('state', '%'. $keywords .'%');
        	}
        	if($ob_state_textbox){
        		$query->andWhere('cb.stateName LIKE :state')->setParameter('state', '%'. $keywords .'%');
        	}
          
        }elseif($option=='city'){
          if($ob_city_dropdown){
        	$query->andWhere('ct.cityName LIKE :city')->setParameter('city', '%'. $keywords .'%');
          }
          if($ob_city_textbox){
          	$query->andWhere('cb.cityName LIKE :city')->setParameter('city', '%'. $keywords .'%');
          }
        }
        $query->andWhere('cb.adressType = :adressType')
             ->setParameter('adressType', $type);
        $query->andWhere('cb.deletedAt IS NULL');
        return  $query->getQuery()->getArrayResult();            

    }

    public function getCompanySearchPopupAddressList($userId,$keywords,$page, $recordsPerPage,$addressType){
        if($keywords!=''){ 
            $qb = $this->createQueryBuilder('cb');
            if($addressType=='shipping_book' or $addressType=='my-billing'){
            $type=2;

            }else{
                $type=1;
            }
            $query = $qb->select('cb','s.stateName as state_name','ct.cityName as city_name','co.countryName as country_name');
            $query->where('cb.user =:userId')
                        ->setParameter('userId', $userId)
                        ->leftJoin('OfficeBrainCoreBundle:State', 's', 'WITH', 'cb.stateId=s.id')
                        ->leftJoin('OfficeBrainCoreBundle:Country', 'co', 'WITH', 'cb.countryId=co.id')
                        ->leftJoin('OfficeBrainCoreBundle:City', 'ct', 'WITH', 'cb.cityId=ct.id')
                        ->andWhere('cb.adressType = :adressType')
                        ->setParameter('adressType', $type)
                        ->andWhere('cb.name LIKE :name')->setParameter('name', $keywords .'%');
                $query->andWhere('cb.deletedAt IS NULL');
        return  $query->getQuery()->getArrayResult();          
        } else{
            return 0;
        }           
                   

    }
    
    /**
     * @author Employee Id: 5497
     * OB eCommerce Product - getContactBookByEmail
     * Function created to get Contact Book By Email
     */
    public function getContactBookByEmail($email, $addressType = null)
    {
    	$query=$this->createQueryBuilder('cb');
    	$query->where("cb.email = :email")->setParameter('email', $email);
    	$query->andWhere("cb.user = :user")->setParameter('user', $this->userId);

    	if ($addressType > 0) {
    		$query->andWhere("cb.adressType = :adressType")->setParameter('adressType', $addressType);
    	}

    	$query->andWhere("cb.deletedAt IS NULL");
    	$result=$query->getQuery()->getResult();
    	return $result;
    }


    public function addNewGuestBillingAddress($BillingData,$userobj)
    {

        $contactBookObj = new ContactBook();
        $contactBookObj->setName($BillingData['guest_billing_address']['name']);
        $contactBookObj->setEmail($BillingData['guest_billing_address']['email']);
        $contactBookObj->setStreet1($BillingData['guest_billing_address']['street1']);
        $contactBookObj->setStreet2($BillingData['guest_billing_address']['street2']);
        $contactBookObj->setCountryId($BillingData['guest_billing_address']['countryId']);
        $contactBookObj->setStateId($BillingData['guest_billing_address']['stateId']);
        $contactBookObj->setCityName($BillingData['guest_billing_address']['cityName']);
        $contactBookObj->setZipCode($BillingData['guest_billing_address']['zipcode']);
        $contactBookObj->setExtension($BillingData['guest_billing_address']['extension']);
        $contactBookObj->setUser($userobj);
        $contactBookObj->setPhone($BillingData['guest_billing_address']['phone']);
        $contactBookObj->setFax($BillingData['guest_billing_address']['fax']);
        $contactBookObj->setWebsite($BillingData['guest_billing_address']['website']);
        $contactBookObj->setAdressType($this->getEntityManager()->getRepository("OfficeBrainCoreBundle:AddressType")->getAddressTypeObjById(1));
        $contactBookObj->setCreatedUid($userobj->getId());
        $this->getEntityManager()->persist($contactBookObj);
        $this->getEntityManager()->flush();
        return $contactBookObj;

    }


    public function addNewGuestShippingAddress($shippingData,$userobj)
    {

        foreach($shippingData as $val){
        $contactBookObj = new ContactBook();
        $contactBookObj->setName($val['standard'][0]['name']);
        $contactBookObj->setEmail($val['standard'][0]['email']);
        $contactBookObj->setStreet1($val['standard'][0]['address1']);
        $contactBookObj->setStreet2($val['standard'][0]['address2']);
        $contactBookObj->setCountryId($val['standard'][0]['countryId']);
        $contactBookObj->setStateId($val['standard'][0]['stateId']);
        //$contactBookObj->setCityId($val['standard'][0]['cityId']);
        $contactBookObj->setCityName($val['standard'][0]['cityId']);
        $contactBookObj->setZipCode($val['standard'][0]['zipcode']);
        $contactBookObj->setExtension($val['standard'][0]['extension']);
        $contactBookObj->setUser($userobj);
        $contactBookObj->setPhone($val['standard'][0]['phone']);
        $contactBookObj->setFax($val['standard'][0]['fax']);
        $contactBookObj->setWebsite($val['standard'][0]['website']);
        $contactBookObj->setAdressType($this->getEntityManager()->getRepository("OfficeBrainCoreBundle:AddressType")->getAddressTypeObjById(2));
        $contactBookObj->setCreatedUid($userobj->getId());
        $this->getEntityManager()->persist($contactBookObj);
        $this->getEntityManager()->flush();
        return $contactBookObj;

        }

    }
}