<?php

namespace OfficeBrain\Bundle\UserBundle\Entity;

use Doctrine\ORM\EntityRepository;
use OfficeBrain\Bundle\UserBundle\Service\CustomPasswordEncoder;
use Symfony\Component\Form\FormError;
use Symfony\Component\Translation\Translator;
use Symfony\Component\Translation\MessageSelector;
use Symfony\Component\Translation\Loader\YamlFileLoader;

/**
 * UserRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class UserRepository extends EntityRepository
{

    protected $projectSetting;
    protected $instanceId;
    protected $instanceType;
    protected $cultureId;
    protected $cultureCode;
    protected $userType;
    protected $paginatorService;
    protected $projectSettingService;
    protected $projectUserSetting;
    protected $userApi;
    protected $serviceGroupService;
    protected $jmsSerializer;
    protected $projectId;
    protected $coreManager;

    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - setCoreManager
     * Function created to set core manager
     */
    public function setCoreManager($coreManager)
    {
    	$this->coreManager = $coreManager;
    }

    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - getCoreManager
     * Function created to get core manager
     */
    public function getCoreManager()
    {
    	return $this->coreManager;
    }

    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - setJmsSerializer
     * Function created to set jms serializer
     */
    public function setJmsSerializer($jmsSerializer)
    {
    	$this->jmsSerializer = $jmsSerializer;
    }

    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - getJmsSerializer
     * Function created to get jms serializer
     */
    public function getJmsSerializer()
    {
    	return $this->jmsSerializer;
    }

    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - setUserApi
     * Function created to set User Api
     */
    public function setUserApi($userApi)
    {
    	$this->userApi = $userApi;
    }

    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - getUserApi
     * Function created to get user api
     */
    public function getUserApi()
    {
    	return $this->userApi;
    }


    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - setServiceGroup Service
     * Function created to setServiceGroup  Service
     */
    public function setServiceGroupService($serviceGroupService)
    {
    	$this->serviceGroupService = $serviceGroupService;
    }

    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - getServiceGroup Service
     * Function created to get setServiceGroup Service
     */
    public function getServiceGroupService()
    {
    	return $this->serviceGroupService;
    }


    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - setProjectSetting
     * Function created to set Project Setting
     */
    public function setProjectSetting($projetSetting)
    {
        $this->projectSetting = $projetSetting;
        $this->prepareProjectSettingData();
    }

    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - getProjectSetting
     * Function created to get Project Setting
     */
    public function getProjectSetting()
    {
        return $this->projetSetting;
    }

    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - setProjectUserSetting
     * Function created to set Project User Setting
     */
    public function setProjectUserSetting($projectUserSetting)
    {
        $this->projectUserSetting = $projectUserSetting;
        $this->prepareProjectUserSettingData();
    }

    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - getProjectUserSetting
     * Function created to get Project User Setting
     */
    public function getProjectUserSetting()
    {
        return $this->projectUserSetting;
    }

    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - getPaginatorService
     * Function created to get Project pagination service
     */
    public function getPaginatorService()
    {
    	return $this->paginatorService;
    }

    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - getProjectUserSetting
     * Function created to get Project pagination service
     */
    public function setPaginatorService($paginatorService)
    {
    	$this->paginatorService = $paginatorService;
    }

    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - prepareProjectSettingData
     * Function created to prepare Project Setting Data
     */
    public function prepareProjectSettingData()
    {
    	$this->userId = $this->projectSetting['user_id'];
    	$this->createdUid = $this->upatedUid = $this->userId;
    	$this->projectId = $this->projectSetting['project_id'];
    	$this->instanceId = $this->projectSetting['instance_id'];
    	$this->instanceType = $this->projectSetting['instance_type'];
    	$this->cultureId = $this->projectSetting['culture_id'];
    	$this->cultureCode = $this->projectSetting['culture_code'];
    }


    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - prepareProjectUserSettingData
     * Function created to prepare Project User Setting Data
     */
    public function prepareProjectUserSettingData()
    {
    	$this->user = $this->projectUserSetting['user_data'];
      $this->userType = null;
      if(!empty($this->user) && method_exists($this->user, 'getUserType')) {
    	   $this->userType = $this->user->getUserType();
      }
    }


    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - getAllSuppliers
     * Function created to get getAllSuppliers List
     */
    public function getAllSuppliers($country='')
    {
    	$query=$this->createQueryBuilder('u');
    	if($this->instanceType != "distributor")
    	{
    		$query->where("u.userType='supplier'");
    	}
    	$query->andWhere("u.status='active'");
    	if (isset($this->projectSetting['instance_id'])) {
            $query->andWhere("u.instanceId=:instanceId")->setParameter('instanceId', $this->projectSetting['instance_id']);
        }

        ## New added by 4571 - for greenbook filter
        if($country!=""){
        	$query->andWhere('u.countryId=:countryId');
        	$query->setParameter('countryId', $country);
        }
        ## New added by 4571 - for greenbook filter

    	$query->andWhere("u.locked is NULL");
    	$query->andWhere("u.expired is NULL");
    	$query->andWhere("u.deletedAt is NULL")
    		  ->orderBy('u.firstName', 'ASC'); // add related to pppc issue - 25844  by Emp Id : 4571
    	$result =  $query->getQuery()->getResult();

    	// New Added on 10/1/16 - 4571 need to pass the company name in this service

        $suppArray = array() ;
    	$i = 0;
    	foreach ($result as $cv){
    				$suppArray[$i]['id'] = $cv->getId();
    				$suppArray[$i]['firstName'] = $cv->getFirstName();
    				$suppArray[$i]['lastName']  = $cv->getLastName();
    				$suppArray[$i]['email']  = $cv->getEmail();
    				$suppArray[$i]['obCustomerId']  = $cv->getObCustomerId();
    				$suppArray[$i]['companyName'] = ($cv && $cv->getUserCompany())?$cv->getUserCompany()->getCompanyName():null;
    				$i++;
    	}


    	/*
    	 *4488
    	 *Start Of Sorting Supplier Company Asc order
    	 * */
    	$sortArray = array();
    	foreach ($suppArray as $key => $row)
    	{
    		$sortArray[$key] = $suppArray[$key]['companyName'];
    	}
    	array_multisort($sortArray, SORT_ASC, $suppArray);
		/*
		 * End Of Sorting Supplier Company
		 * */

    	// New Added on 10/1/16 - 4571 need to pass the company name in this service
    	return $suppArray;

    }


    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - getAllDistributors
     * Function created to get getAllDistributors List
     */
    public function getAllDistributors($filters=array(), $searchVal='', $searchBy='', $country='', $state='')
    {
        $query=$this->createQueryBuilder('u');
        $query->innerJoin('u.userCompany','uc');
        $query->where("u.userType='distributor'");
        $query->andWhere("u.status='active'");
        $query->andWhere("u.locked is NULL");
        $query->andWhere("u.expired is NULL");
        $query->andWhere("u.deletedAt is NULL");
        $query->andWhere("uc.companyName is NOT NULL");
       /* if (isset($this->projectSetting['instance_id'])) {
        	$query->andWhere("u.instanceId=:instanceId")->setParameter('instanceId', $this->projectSetting['instance_id']);
        }*/
        /*if (isset($filters['zipcode']) && $filters['zipcode']!='') {
            $query->andWhere("u.zipcode=:zipcode")->setParameter('zipcode', $filters['zipcode']);
        }*/


        if (isset($filters['stateId']) && $filters['stateId']!='') {
            $query->andWhere("u.stateId=:stateId")->setParameter('stateId', $filters['stateId']);
        }

        // added by 4571 --- for managing search filter
        if($searchVal!="" && $searchBy == 'byname'){
        	$query->andWhere('u.firstName LIKE :firstName');
        	$query->setParameter('firstName', '%'.$searchVal.'%');
        }
        if($searchVal!="" && $searchBy == 'bypppc_no'){
        	$query->andWhere('u.membershipNumber5 LIKE :membershipNumber'); // pppc number
        	$query->setParameter('membershipNumber', '%'.$searchVal.'%');
        }
        if($country!=""){
        	$query->andWhere('u.countryId=:countryId');
        	$query->setParameter('countryId', $country);
        }
        if($state!=""){
        	$query->andWhere('u.stateId=:stateId');
        	$query->setParameter('stateId', $state);
        }
        // end here by 4571 --- for managing search filter
        $result =  $query->getQuery()->getResult();


        $disArray = array() ;
        $i = 0;
        foreach ($result as $cv){
        	$disArray[$i]['id'] = $cv->getId();
        	$disArray[$i]['firstName'] = $cv->getFirstName();
        	$disArray[$i]['lastName']  = $cv->getLastName();
        	$disArray[$i]['email']  = $cv->getEmail();
        	if($cv->getObCustomerId() && $cv->getObCustomerId()!=''){
        		$disArray[$i]['obCustomerId']  = $cv->getObCustomerId();
        	}else{
        		$disArray[$i]['obCustomerId']  = '';
        	}
        	if(count($cv->getUserCompany()) >0  && $cv->getUserCompany()->getCompanyName()!=''){
        		$disArray[$i]['companyName'] = $cv->getUserCompany()->getCompanyName();
        	}else{
        		$disArray[$i]['companyName']='';
        	}
        	$disArray[$i]['stateId']  = $cv->getStateId();
        	$i++;
        }
        // New Added on 10/1/16 - 4471 need to pass the company name in this service

        return $disArray;
    }

    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - getAllDistributors
     * Function created to get getAllDistributors List
     */
    public function getAllDistributorsIdNameOnly($filters=array())
    {
    	$query=$this->createQueryBuilder('u');
    	$query->select('u.id as key,CONCAT(u.firstName,\' \',u.lastName) as value');
    	$query->where("u.userType='distributor'");
    	$query->andWhere("u.status='active'");
    	$query->andWhere("u.locked is NULL");
    	$query->andWhere("u.expired is NULL");
    	$query->andWhere("u.deletedAt is NULL");
    	if (isset($this->projectSetting['instance_id'])) {
    		$query->andWhere("u.instanceId=:instanceId")->setParameter('instanceId', $this->projectSetting['instance_id']);
    	}

    	if (isset($filters['stateId']) && $filters['stateId']!='') {
    		$query->andWhere("u.stateId=:stateId")->setParameter('stateId', $filters['stateId']);
    	}

    	$resultarray=$query->getQuery()->getArrayResult();

    	return $resultarray;
    }
    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - getDistributorsState
     * Function created to  get State of Distributors
     */
    public function getDistributorsState()
    {
    	$query=$this->createQueryBuilder('u')->select('u.stateId');
    	$query->where("u.userType='distributor'");
    	$query->andWhere("u.status='active'");
    	$query->andWhere("u.locked is NULL");
    	$query->andWhere("u.expired is NULL");
    	$query->andWhere("u.deletedAt is NULL");
    	$query->groupBy("u.stateId");
    	if (isset($this->projectSetting['instance_id'])) {
    		$query->andWhere("u.instanceId=:instanceId")->setParameter('instanceId', $this->projectSetting['instance_id']);
    	}
    	return $query->getQuery()->getArrayResult();
    	//	return $this->paginatorService->paginate($query, $page, $recordPerPage);
    }

    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - getAllUsers
     * Function created to get AllUsers
     */
    public function getAllUsers()
    {
        $query=$this->createQueryBuilder('u');
        $query->andWhere("u.status='active'");
        $query->andWhere("u.locked is NULL");
        $query->andWhere("u.expired is NULL");
        $resultarray=$query->getQuery()->getArrayResult();

        return $resultarray;
    }

    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - getUserById
     * Function created to get User By Id
     */
    public function getUserById($id, $instanceId=null)
    {
        $query=$this->createQueryBuilder('u');
        /*$query->select('u', 'c');
        $query->leftJoin('u.userCompany', 'c');*/
        $query->where("u.id=:id")->setParameter('id', $id);
        if (isset($this->projectSetting['instance_id'])) {
            $query->andWhere("u.instanceId=:instanceId")->setParameter('instanceId', $this->projectSetting['instance_id']);
        } elseif ($instanceId!=null && $instanceId > 0) {
            $query->andWhere("u.instanceId=:instanceId")->setParameter('instanceId', $instanceId);
        }
        $query->andWhere("u.deletedAt is NULL");
        $result=$query->getQuery()->getOneorNullResult();
        return $result;
    }


    /**
     * @author Employee Id: 410175
     * OB eCommerce Product - getUserById
     * Function created to get User By Id
     */
    public function getUserByIdMobileApi($id, $instanceId=null)
    {
    	$query=$this->createQueryBuilder('u');
    	$query->select('u.email','u.gender','u.firstName','u.middleName','u.lastName','u.photo','u.street1','u.street2','u.countryId','c.countryName','u.stateId','s.stateName','u.cityId','ct.cityName','u.zipcode','u.extension','u.phone','u.mobile','u.fax','u.website','u.locatedDistributorId','u.status','u.membershipNumber1','u.membershipNumber2','u.membershipNumber3','u.membershipNumber4','u.membershipNumber5','u.membershipNumber6','u.membershipNumber7','u.jobTitle','u.companyName','u.newslettersUnsubscription','u.erpId','u.userType');

    	$query->leftJoin('OfficeBrainCoreBundle:Country', 'c', 'WITH', 'u.countryId=c.id');
    	$query->leftJoin('OfficeBrainCoreBundle:State', 's', 'WITH', 'u.stateId=s.id');
    	$query->leftJoin('OfficeBrainCoreBundle:City', 'ct', 'WITH', 'u.cityId=ct.id');

    	$query->where("u.id=:id")->setParameter('id', $id);
    	if (isset($this->projectSetting['instance_id'])) {
    		$query->andWhere("u.instanceId=:instanceId")->setParameter('instanceId', $this->projectSetting['instance_id']);
    	} elseif ($instanceId!=null && $instanceId > 0) {
    		$query->andWhere("u.instanceId=:instanceId")->setParameter('instanceId', $instanceId);
    	}$query->andWhere("u.deletedAt is NULL");
    	$result=$query->getQuery()->getOneorNullResult();
    	return $result;
    }

    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - getUserByEmail
     * Function created to get User By Email
     */
    public function getUserByEmail($email)
    {
        $query=$this->createQueryBuilder('u');
        $query->where("u.email=:email")->setParameter('email', $email);
        $query->andWhere("u.instanceId=:instanceId")->setParameter('instanceId', $this->projectSetting['instance_id']);
        $query->andWhere("u.deletedAt is NULL");
        $resultarray=$query->getQuery()->getOneorNullResult();

        return $resultarray;
    }


    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - editUser
     * Function created for edit user data to records
     */
    public function editUser($form)
    {
        if ($this->checkUserExists($form)) {
            $user = $form->getData();
            $user->setCountryCulture($this->coreManager->getCountryCulture());
            if($user->getServiceGroup() && is_object($user->getServiceGroup()) && $user->getServiceGroup()->getId() != ''){
            	$user->setServiceGroup($user->getServiceGroup());
            } else{
	            $serviceGroupObject = $this->serviceGroupService->getServiceDefaultGroup($user->getCountryId());
	            if($serviceGroupObject && is_object($serviceGroupObject)){
	            	$user->setServiceGroup($serviceGroupObject);
	            }
            }
            if($user->getUserCompany() && is_object($user->getUserCompany())){
            	$user->setIsCompany('1');
            	$user->setIsIndividual(null);
            }
            $em = $this->getEntityManager();
            $em->persist($user);
            $em->flush();
            return $user;
        } else {
            return false;
        }
    }

    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - addUser
     * Function created for add user data to records
     * @param Serializer $serializer
     */
    public function addUser($form, $userType, $userId,$fromerp=0)
    {
    	if ($this->checkUserExists($form)) {

            $user = $form->getData();

            //tmp changes : we have only one coutry. we will work for same.

            $serviceGroupObject = $this->serviceGroupService->getServiceDefaultGroup('233');
            if($serviceGroupObject && is_object($serviceGroupObject)){
            	$user->setServiceGroup($serviceGroupObject);
            }
            if($user->getUserCompany() && is_object($user->getUserCompany())){
            	$user->setIsCompany('1');
            	$user->setIsIndividual(null);
            }
            $user->setInstanceType($this->projectSetting['instance_type']);
            $user->setInstanceId($this->projectSetting['instance_id']);
            $user->setSalt($this->getUserSalt());
            $user->setCountryCulture($this->coreManager->getCountryCulture());
            $user->setUserType($userType);
            $user->setCreatedUid($userId);
			if($fromerp == 0){
			    $user->setPassword($this->setUserPassword($user->getPassword(), $user->getSalt()));
	            $this->signUpApproveConfigrations = $this->getEntityManager()->getRepository('OfficeBrainUserBundle:SignUpForm')->getSignUpForm($userType);
	            if ($this->signUpApproveConfigrations['approval_configuration']['is_auto_approve']->status==1) {
	                $user->setStatus('active');
	            } else { //($this->signUpConfigrations['approval_configuration']['is_auto_approve']['option']['approval_email']==1) {
	            	//$user->setStatus('pending');
                    $user->setStatus('active');
	            	if ($userType=='employee' ||  $userType=='branchEmployee') {
	            		$user->setStatus('inactive');
	            	}
	            }
	            $user->setConfirmationToken($this->getUserConfirmationToken());
			} else {
			    $user->setPassword($this->setUserPassword('123456', $user->getSalt()));
			}

			$em = $this->getEntityManager();
			$em->persist($user);
			$em->flush();
			return $user;
        } else {
            return false;
        }
    }


    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - addGuestUser
     * Function created for add guest user data to records
     */
    public function addGuestUser($form)
    {
    	$checkUserExist = $this->checkGuestUserExists($form);
    	if (is_object($checkUserExist)) {
    		return $checkUserExist;
    	}else{
    		$userType = 'end_user';
    		$userObj = new User();
    		$user = $form->getData();
    		$userObj->setEmail($user['email']);
    		$userObj->setPhone($user['phone']);
    		$userObj->setFirstName($user['firstName']);
    		//$userObj->setUserCompany($user['companyName']);
    		$userObj->setZipcode($user['zipcode']);

    		$userObj->setInstanceType($this->projectSetting['instance_type']);
    		$userObj->setInstanceId($this->projectSetting['instance_id']);
    		$userObj->setSalt($this->getUserSalt());
    		$userObj->setPassword($this->setUserPassword($this->getUserConfirmationToken(6), $userObj->getSalt()));
    		$userObj->setUserType($userType);
    		$userObj->setCreatedUid(($this->createdUid!=0) ? $this->createdUid : 1);

    		$this->signUpApproveConfigrations = $this->getEntityManager()->getRepository('OfficeBrainUserBundle:SignUpForm')->getSignUpForm($userType);

    		if ($this->signUpApproveConfigrations['approval_configuration']['is_auto_approve']->status==1) {
    			$userObj->setStatus('active');
    		} else {
    			//$userObj->setStatus('pending');
                $userObj->setStatus('active');
    			$userObj->setConfirmationToken($this->getUserConfirmationToken());
    		}

    		$em = $this->getEntityManager();
    		$em->persist($userObj);
    		$em->flush();
    		return $userObj;
    	}
    }

    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - getUserByUsername
     * Function created to get User By Username
     */
    public function checkUserExists($form)
    {
        $result = array();
        $translator = new Translator($this->projectSetting['locale_language'], new MessageSelector());
        $translator->addLoader('yaml', new YamlFileLoader());
        $translator->addResource('yaml', __DIR__.'/../Resources/translations/validators.'.$this->projectSetting['locale_language'].'.yml', $this->projectSetting['locale_language']);

        if ($form->has('loginUsername')) {
            $username = $form->getData()->getLoginUsername();

            $query=$this->createQueryBuilder('u');
            $query->where("u.loginUsername=:username")->setParameter('username', $username);
            $query->andWhere("u.instanceId=:instanceId")->setParameter('instanceId', $this->projectSetting['instance_id']);
            $query->andWhere("u.deletedAt is NULL");
            $user1=$query->getQuery()->getOneorNullResult();

            if ($user1 && $user1->getId()!=$form->getData()->getId()) {
                $error = new FormError($translator->trans('user.username.used'));
                $form->get('loginUsername')->addError($error);
                return false;
            }
        }
        if ($form->has('email')) {
            $email = $form->getData()->getEmail();

            $query=$this->createQueryBuilder('u');
            $query->where("u.email=:email")->setParameter('email', $email);
            $query->andWhere("u.instanceId=:instanceId")->setParameter('instanceId', $this->projectSetting['instance_id']);
            $query->andWhere("u.deletedAt is NULL");
            $user2=$query->getQuery()->getOneorNullResult();

            if ($user2 && $user2->getId()!=$form->getData()->getId()) {
                $error = new FormError($translator->trans('user.email.used'));
                $form->get('email')->addError($error);
                return false;
            }
        }
        return true;
    }

    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - getUserByUsername
     * Function created to get User By Username
     */
    public function checkGuestUserExists($form)
    {
    	$result = array();
    	$translator = new Translator($this->projectSetting['locale_language'], new MessageSelector());
    	$translator->addLoader('yaml', new YamlFileLoader());
    	$translator->addResource('yaml', __DIR__.'/../Resources/translations/validators.'.$this->projectSetting['locale_language'].'.yml', $this->projectSetting['locale_language']);

    	if ($form->has('email')) {
    		$email = $form->getData()['email'];

    		$query=$this->createQueryBuilder('u');
    		$query->where("u.email=:email")->setParameter('email', $email);
    		$query->andWhere("u.instanceId=:instanceId")->setParameter('instanceId', $this->projectSetting['instance_id']);
    		$query->andWhere("u.deletedAt is NULL");
    		$user2=$query->getQuery()->getOneorNullResult();

    		if (count($user2) > 0) {
    			return $user2 ;
    		}
    	}
    	return true;
    }

    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - getUserByConfirmationToken
     * Function created to get User By ConfirmationToken
     */
    public function getUserByConfirmationToken($token)
    {
        $query=$this->createQueryBuilder('u');
        $query->where("u.confirmationToken=:token")->setParameter('token', $token);
        $query->andWhere("u.instanceId=:instanceId")->setParameter('instanceId', $this->projectSetting['instance_id']);
        $query->andWhere("u.deletedAt is NULL");
        $resultarray=$query->getQuery()->getOneorNullResult();

        return $resultarray;
    }


    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - approveUserByConfirmationToken
     * Function created to approve User By ConfirmationToken
     */
    public function approveUserByConfirmationToken($user)
    {
        $em = $this->getEntityManager();
        $user->setConfirmationToken(null);
        $user->setStatus(1);
        $em->persist($user);
        $em->flush();

        return true;
    }

    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - getUserById Of Login User
     * Function created to get User By Id Of Login user
     */
    public function getValidUserById($id, $loginId=null)
    {
        $query=$this->createQueryBuilder('u');
        if ($loginId) {
            $query->where("u.parent=:LoginId")->orWhere("u.createdUid=:LoginId")->setParameter('LoginId', $loginId);
        }
        $query->andWhere("u.id=".$id);
        $query->andWhere("u.instanceId=:instanceId")->setParameter('instanceId', $this->projectSetting['instance_id']);
        $query->andWhere("u.deletedAt is NULL");
        $resultarray=$query->getQuery()->getOneorNullResult();

        return $resultarray;
    }


    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - getUserSalt
     * Function created to get User Salt
     */
    public function getUserSalt()
    {
        $key = '';
        $characters = "1234567890abcdefghijklmnopqrstuvwxyz#*_-@";
        for ($i=0;$i<24;$i++) {
            $key .= $characters{rand(0, 40)};
        }
        return $key;
    }

    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - getOBCustomerId
     * Function created to get OB customer Id
     */
    public function getObCustomerId()
    {
    	$key = '';
    	$characters = "1234567890abcdefghijklmnopqrstuvwxyz";
    	for ($i=0;$i<6;$i++) {
    		$key .= $characters{rand(0, 40)};
    	}
    	return 'OB'.$key;
    }


    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - getUserConfirmationToken
     * Function created for generating User Confirmation Token
     */
    public function getUserConfirmationToken($length = 22)
    {

        if (function_exists('openssl_random_pseudo_bytes')) {
            $token = base64_encode(openssl_random_pseudo_bytes($length, $strong));
            if ($strong == true) {
                return strtr(substr($token, 0, $length), '+/=', '-_,');
            } //base64 is about 33% longer, so we need to truncate the result
        }

        //fallback to mt_rand if php < 5.3 or no openssl available
        $characters = '0123456789';
        $characters .= 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz/+';
        $charactersLength = strlen($characters)-1;
        $token = '';

        //select some random characters
        for ($i = 0; $i < $length; $i++) {
            $token .= $characters[mt_rand(0, $charactersLength)];
        }
        $token = $token.time();
        return $token;
    }

    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - setUserPassword
     * Function created to generate encrypted User Password
     */
    public function setUserPassword($encrypt, $key)
    {
        $encoder = new CustomPasswordEncoder();
        $encodedPassword = $encoder->encodePassword($encrypt, $key);

        return $encodedPassword;
    }

    // Decrypt Function
    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - getUserPassword
     * Function created to decrypt User Password
     */
    public function getUserPassword($decrypt, $key)
    {
        $iv = mcrypt_create_iv(32);
        $decoded = trim(mcrypt_decrypt(MCRYPT_RIJNDAEL_256, $key, base64_decode($decrypt), MCRYPT_MODE_ECB, $iv));
        return $decoded;
    }


    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - deleteUser
     * Function created to delete User
     */
    public function deleteUser($userId)
    {
        $user = $this->getUserById($userId);
        if ($user) {
            $em = $this->getEntityManager();
            $em->remove($user);
            $em->flush();

            return array('status'=>'SUCCESS','data'=>$user);
        }
        return array('status'=>'FAIL');
    }

    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - update status
     * Function created to delete User
     */
    public function updateUserStatus($userId, $status,$ObCustomerIdConfirm)
    {
    	$user = $this->getUserById($userId);
    	if ($user) {
	    	if($status == 'approve'){
	    		$status = 'active';
	    		if ($user->getUserType() && $user->getUserType() == 'supplier' || $user->getUserType() == 'distributor' ){
	    			$user->setStatus($status);
	    			/*if(!($user->getObCustomerId())){ $user->setObCustomerId($this->getObCustomerId()); }*/
	    			if($ObCustomerIdConfirm!='' && $ObCustomerIdConfirm == 1){
	    				$userJsonData= $this->jmsSerializer->serialize($user, 'json');
	    				$userFromApi = $this->getObCustomerIdFromProductMaster($userJsonData);
	    				if($userFromApi['success_message'] && $userFromApi['success_message'] == 'Success'){
	    					return $this->saveUserStatus($user);
	    				}else{
	    					return array('status'=>'NOT MATCH');
	    				}
	    			}else{
	    				return $this->saveUserStatus($user);
	    			}
	    		}
	    	}
    		$user->setStatus($status);
    		return $this->saveUserStatus($user);
    	}

        return array('status'=>'FAIL');
    }


    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - update status
     * Function created to delete User
     */
    public function saveUserStatus($user)
    {
    	$em = $this->getEntityManager();
    	$em->persist($user);
    	$em->flush();
    	return array('status'=>'SUCCESS','data'=>$user);
    }


    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - getAdminAllUsers
     * Function created to get AllUsers
    */
    public function getAdminAllUsers($userId,$requestUserStatusTab,$page,$recordPerPage,$searchParameterArray,$usertype,$status,$userCountFlag=0)
    {
    	$userObj = $this->projectUserSetting['user_data'];
        $query = $this->createQueryBuilder('u');
        if($userCountFlag == '1'){
        	$query->select('count(u.id)','u.status');
        } else {
        	$query->select('u', 'c');
        }
        $query->leftJoin('u.userCompany', 'c');
		if ($userObj->getUserType() && $userObj->getUserType()=='employee' || $userObj->getUserType()=='branchEmployee') {
			$userId = $userObj->getParent()->getId();
		}
        $userId = 1;
        $query->where("u.parent =:userId")
              ->orWhere("u.createdUid =:userId")
              ->setParameter('userId', $userId);

        if (isset($searchParameterArray) and $searchParameterArray!='') {
        	foreach ($searchParameterArray as $key=>$val) {
            	if ($key == 'countryId') {
                    $query->leftJoin('OfficeBrainCoreBundle:Country', 'c', 'WITH', 'u.countryId=c.id');
                    $query->andWhere('c.countryName LIKE :country')->setParameter('country', '%'. $val .'%');
                    continue;
                } elseif ($key == 'stateId') {
                    $query->leftJoin('OfficeBrainCoreBundle:State', 's', 'WITH', 'u.stateId=s.id');
                    $query->andWhere('s.stateName LIKE :state')->setParameter('state', '%'. $val .'%');
                    continue;
                } elseif ($key == 'cityId') {
                    $query->leftJoin('OfficeBrainCoreBundle:City', 'ct', 'WITH', 'u.cityId=ct.id');
                    $query->andWhere('ct.cityName LIKE :city')->setParameter('city', '%'. $val .'%');
                    continue;
                } elseif ($key == 'companyId') {
                    $param = 'user'.$key;
                    // $query->andWhere('c.companyName LIKE :'.$param)->setParameter($param, '%'. $val .'%');
                    $query->andWhere('u.companyName LIKE :'.$param)->setParameter($param, '%'. $val .'%');
                } elseif ($key == 'serviceGroup') {
                	$param = 'user'.$key;
                    $query->leftJoin('u.serviceGroup', 'sg');
                    $query->leftJoin('sg.languageMapping', 'sgl');
                    $query->andwhere('sgl.groupName LIKE :'.$param)->setParameter($param, '%'. $val .'%');
                    $query->andwhere('sg.instanceId = :instanceId')->setParameter('instanceId', $this->instanceId);
                    $query->andwhere('sg.instanceType = :instanceType')->setParameter('instanceType', $this->instanceType);
                    $query->andWhere('sg.deletedAt IS NULL');
                } elseif ($key != '') {
                    $param = 'user'.$key;
                    $query->andWhere('u.'.$key.' LIKE :'.$param)->setParameter($param, '%'. $val .'%');
                }
            }
        }

        if ($status != '') {
        	$query->andWhere("u.status =:status")
        		  ->setParameter('status', $status);
        }
        if ($requestUserStatusTab == 'requested') {
        	$query->andWhere("u.status = 'pending' OR u.status = 'unapprove'");
        } elseif ($requestUserStatusTab == 'approved') {
        	$query->andWhere("u.status = 'approve' OR u.status = 'inactive' OR u.status = 'active'");
        }
        if ($usertype != '') {
        	$query->andWhere("u.userType =:userType")
        		  ->setParameter('userType', $usertype);
        }
        if (isset($this->instanceId)) {
        	$query->andWhere("u.instanceId=:instanceId")->setParameter('instanceId', $this->instanceId);
        }
        $query->andWhere("u.locked is NULL")
        	  ->andWhere("u.expired is NULL")
        	  ->andWhere("u.deletedAt is NULL")
        	  ->orderBy("u.id",'DESC')
              ->andWhere($query->expr()->notIn("u.userType", ("'employee','branchEmployee'")));

        if($userCountFlag == '1'){
        	$query->groupBy('u.userType');
        	$query->groupBy('u.status');
        	//echo $query->getQuery()->getSql(); die;
        	return $query->getQuery()->getArrayResult();
        }else{
        	return $this->paginatorService->paginate($query, $page, $recordPerPage);
        }

    }

    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - getCompanyEmployee
     * Function created to get AllEmployee
     */
    public function getCompanyEmployee($companyId,$countFlag,$searchParameterArray,$page,$recordsPerPage,$status)
    {
    	$userType = $this->getEntityManager()->getRepository('OfficeBrainCompanyBundle:Company')->isBranch($companyId);
        $query=$this->createQueryBuilder('u');
        if($countFlag == '1'){
        	$query->select('count(u.id)','u.status');
        } else {
        	$query->select('u', 'c');
        }
		$query->leftJoin('u.userCompany', 'c');
        if (isset($searchParameterArray) and $searchParameterArray!='') {
            foreach ($searchParameterArray as $key=>$val) {
                if ($key == 'countryId') {
                    $query->leftJoin('OfficeBrainCoreBundle:Country', 'c', 'WITH', 'u.countryId=c.id');
                    $query->andWhere('c.countryName LIKE :country')->setParameter('country', '%'. $val .'%');
                    continue;
                } elseif ($key == 'stateId') {
                    $query->leftJoin('OfficeBrainCoreBundle:State', 's', 'WITH', 'u.stateId=s.id');
                    $query->andWhere('s.stateName LIKE :state')->setParameter('state', '%'. $val .'%');
                    continue;
                } elseif ($key == 'cityId') {
                    $query->leftJoin('OfficeBrainCoreBundle:City', 'ct', 'WITH', 'u.cityId=ct.id');
                    $query->andWhere('ct.cityName LIKE :city')->setParameter('city', '%'. $val .'%');
                    continue;
                } elseif ($key != '') {
                    $param = 'user'.$key;
                    $query->andWhere('u.'.$key.' LIKE :'.$param)->setParameter($param, '%'. $val .'%');
                }
            }
        }
        if ($status != '') {
            $query->andWhere("u.status =:status")
            	  ->setParameter('status', $status);
        }
        $query->andWhere("u.userCompany =:companyId")->setParameter('companyId', $companyId);
        $query->andWhere("u.userType =:usertype")->setParameter('usertype', $userType);
        $query->andWhere("u.locked is NULL");
        $query->andWhere("u.expired is NULL");
        $query->andWhere("u.deletedAt is NULL");
        if($countFlag == '1'){
        	$query->groupBy('u.status');
        	return $query->getQuery()->getArrayResult();
        }
        //echo $query->getQuery()->getSql(); die;
        return $this->paginatorService->paginate($query, $page, $recordsPerPage);
    }


    public function getAllUserObject()
    {
        $objUsers = $this->createQueryBuilder("U")
                ->select("U")
                ->andWhere("U.status='active'")
                ->addOrderBy("U.loginUsername", "ASC")
                ->andWhere("U.deletedAt is NULL")
                ->getQuery()// -> getSQL();
                ->getResult();
        return $objUsers;
    }

    public function getAllUserArray()
    {
        $objUsers = $this->createQueryBuilder("U")
                ->select("U")
                ->andWhere("U.status='active'")
                ->addOrderBy("U.loginUsername", "ASC")
                ->andWhere("U.deletedAt is NULL")
                ->getQuery()// -> getSQL();
                ->getArrayResult();
        return $objUsers;
    }

    public function getAllUserObjectByInstanceId($instanceId=0)
    {
    	$objUsers = $this->createQueryBuilder('U')
                ->select('U')
                ->andWhere("U.status in ('active','inactive')")
                ->andWhere('U.instanceId=:instanceId')
                ->setParameter('instanceId', $instanceId)
                ->addOrderBy('U.loginUsername', 'ASC')
                ->andWhere("U.deletedAt is NULL")
                ->getQuery()// -> getSQL();
                ->getResult();
        return $objUsers;
    }

    public function getAllSubUserObjectByUserId($objUser=null)
    {
        $objUsers = $this->createQueryBuilder("U");
        $objUsers=$objUsers->select("U")
                ->andWhere("U.status in ('active','inactive')")
                ->andWhere($objUsers->expr()->eq("U.parent", ":objUser"))
                ->setParameter("objUser", $objUser)
                ->addOrderBy("U.loginUsername", "ASC")
                ->andWhere("U.deletedAt is NULL")
                ->getQuery()// -> getSQL();
                ->getResult();
        return $objUsers;
    }

    public function getAllSubUserObjectOfParentByUserId($objUser=null)
    {
        $objUsers = $this->createQueryBuilder("U");
        $objUsers=$objUsers->select("U")
                ->andWhere("U.status in ('active','inactive')")
                ->andWhere($objUsers->expr()->orX($objUsers->expr()->gte("U.parent", ":objUser"), $objUsers->expr()->eq("U.id", ":objUser")))
                ->setParameter("objUser", $objUser)
                ->addOrderBy("U.loginUsername", "ASC")
                ->andWhere("U.deletedAt is NULL")
                ->getQuery()// -> getSQL();exit;
                ->getResult();
        return $objUsers;
    }

    public function getAllUserObjectByIds($instanceId=0, $arrId)
    {
        $objUsers = $this->createQueryBuilder("U")
                ->select("U")
                ->andWhere("U.status in ('active','inactive')")
                ->andWhere("U.instanceId=:instanceId")
                ->setParameter("instanceId", $instanceId)
                ->andWhere("U.id in(:arrId)")
                ->setParameter("arrId", $arrId)
                ->addOrderBy("U.loginUsername", "ASC")
                ->andWhere("U.deletedAt is NULL")
                ->getQuery()// -> getSQL();
                ->getResult();
        return $objUsers;
    }

    public function getAllSubUserObjectByUser($objUser)
    {
        $objUsers = $this->createQueryBuilder("U");
        $objUsers =$objUsers->select("U")
                    ->andWhere("U.status in ('active','inactive')")
                    ->andWhere($objUsers->expr()->eq("IUAM.user", ":objUser"))
                    ->setParameter("objUser", $objUser)
                    ->addOrderBy("U.loginUsername", "ASC")
                    ->andWhere("U.deletedAt is NULL")
                    ->getQuery()// -> getSQL();
                    ->getResult();
        return $objUsers;
    }

    public function getUserObjectByUser($objUser)
    {
        $objUser = $this->createQueryBuilder("U");
        $objUser = $objUser->select("U")
                ->andWhere("U.status='active'")
                ->andWhere($objUser->expr()->eq("IUAM.user", ":objUser"))
                ->setParameter("objUser", $objUser)
                ->addOrderBy("U.loginUsername", "ASC")
                ->andWhere("U.deletedAt is NULL")
                ->getQuery()// -> getSQL();
                ->getResult();
        return $objUser;
    }

    /**
     * @author Employee Id: 4464
     * OB eCommerce Product - getUserIdByName
     * Function created to get User Id by name
     */
    public function getUserIdByName($searchName)
    {
        $query=$this->createQueryBuilder('u')
            ->select('u.id')
            ->where('u.firstName LIKE :searchName')
            ->orWhere('u.lastName LIKE :searchName')
            ->setParameter('searchName', '%'. $searchName .'%');
        $query->andWhere("u.deletedAt is NULL");
        $resultarray=$query->getQuery()->getResult();

        return $resultarray;
    }

    public function getTotalUserCount()
    {
	    $queryBuilder = $this->createQueryBuilder('u');
	    $queryBuilder->select('count(u.id)');
	    $queryBuilder->where('u.status = :status ANd u.instanceId = :instanceId')
		    ->andWhere("u.deletedAt is NULL")
		    ->setParameter('status', 'active')
		    ->setParameter('instanceId', $this->instanceId);

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

    /**
     * @author Employee Id: 5053
     * OB eCommerce Product - getProjectUserSetting
     * Function created to Count user for User type
     */
    public function getCountTotalUser($userType)
    {
    	$queryBuilder = $this->createQueryBuilder('u');
    	$queryBuilder->select('count(u.id)');
    	$queryBuilder->where('u.status = :status AND u.instanceId = :instanceId AND u.userType =:userType')
			    	->andWhere("u.deletedAt is NULL")
			    	->setParameter('status','active')
			    	->setParameter('instanceId',$this->instanceId)
			    	->setParameter('userType',$userType);

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

    /**
     * @author Employee Id: 4571
     * OB eCommerce Product
     * Function created to find out whether current user is Admin or not
     */
    public function isAdmin()
    {
    	$query = $this->createQueryBuilder('u');

    	$query->select('u');
    	$query->where("u.status='active'");

    	if (isset($this->instanceId)) {
    		$query->andWhere("u.instanceId=:instanceId")->setParameter('instanceId', $this->instanceId);
    	}

    	$query->andWhere("u.locked is NULL");
    	$query->andWhere("u.expired is NULL");
    	$query->andWhere("u.deletedAt is NULL");
    	$query->andWhere('u.parent is NULL');
    	$query->andWhere("u.id=:id")->setParameter('id', $this->userId);

    	return $query->getQuery()->getScalarResult();
    }

    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - update status
     * Function created to delete User
     */
    public function updateUserServiceGroup($serviceGroup,$defaultServiceGroup)
    {
    	return $this->createQueryBuilder('u')
		    	->update()
		    	->set('u.serviceGroup', $defaultServiceGroup->getId())
		    	->where('u.instanceId = :instanceId')
		    	->andWhere("u.serviceGroup = :serviceGroup")
		    	->andWhere('u.instanceType = :instanceType')
		    	->setParameter('serviceGroup', $serviceGroup->getId())
		    	->setParameter(':instanceId', $this->instanceId)
		    	->setParameter(':instanceType', $this->instanceType)
		    	->andWhere("u.deletedAt is NULL")
    			->getQuery()->execute();
    }


    /**
     * @author Employee Id: 4464
     * OB eCommerce Product - getUserIdByName
     * Function created to get User Id by name
     */
    public function supplierIdByObCustomerId($obCustomerId)
    {
    	return $query=$this->createQueryBuilder('u')
    			->select('u.id')
    			->where('u.obCustomerId =:obCustomerId')
    			->setParameter('obCustomerId', $obCustomerId)
    			->andWhere("u.deletedAt is NULL")
    			->getQuery()->getArrayResult();
    }


    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - This function is used to get ob Customer Id from product master table from api
     * Function created to getObCustomerIdFromProductMaster
     */
    public function getObCustomerIdFromProductMaster($userDataJson)
    {
    	$params = array( 'user_data_json' => $userDataJson);

    	$this->userApi->init('get-ob-customer-id');
    	$this->userApi->setCallParameters($params);
    	$this->userApi->doProcess();

    	$returnData = null;
    	if ($this->userApi->isSuccess()) {
    		$returnData = $this->userApi->getResultArray();
    	}
    	unset($params);

    	return $returnData;
    }

    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - This function is used to get ob Customer Id from product master table from api
     * Function created to findObCustomerIdByUserFromApi
     */
    public function findObCustomerIdByUserFromApi($email)
    {
    	$params = array( 'email' => $email);
    	$this->userApi->init('find-ob-customer-id-by-email');
    	$this->userApi->setCallParameters($params);
    	$this->userApi->doProcess();

    	$returnData = null;
    	if ($this->userApi->isSuccess()) {
    		$returnData = $this->userApi->getResultArray();
    	}
    	unset($params);

    	return $returnData;
    }


    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - This function is used to get ob Customer Id from product master table from api
     * Function created to createNewObCustomerIdFromApi
     */
    public function createNewObCustomerIdFromApi($userObject)
    {
    	$userJsonData= $this->jmsSerializer->serialize($userObject, 'json');
    	$params = array( 'user_json' => $userJsonData,'project_id'=>$this->projectId);
    	$this->userApi->init('create-new-ob-customer-id-by-email');
    	$this->userApi->setCallParameters($params);
    	$this->userApi->doProcess();

    	$returnData = null;

    	if ($this->userApi->isSuccess()) {
    		$returnData = $this->userApi->getResultArray();
    	}
    	unset($params);

    	return $returnData;
    }

    /**
     * @author Employee Id: 4273
     * OB eCommerce Product - This function is used to get first user Id from user table
     * Function created to findObCustomerIdByUserFromApi
     */
    public function getFirstUserId($instanceId)
    {
        $query = $this->createQueryBuilder('u');

        $query->select('u.id');

        $query->where('u.status = :status')
                ->setParameter('status', 'active')
                ->andWhere("u.locked is NULL")
                ->andWhere("u.expired is NULL")
                ->andWhere("u.deletedAt is NULL")
                ->andWhere('u.parent is NULL');

        if (!empty($instanceId)) {
            $query->andWhere("u.instanceId=:instanceId")->setParameter('instanceId', $instanceId);
        }

        $query->orderBy("u.id",'ASC')->setMaxResults(1);

        $result = $query->getQuery()->getScalarResult();

        if(isset($result[0]['id']))     return $result[0]['id'];
        else return 1;
    }
    /**
     * created by 4488
     * check Ob Customer Id exist in User
     */
    public function existObCustomerIdUser($obCustomerId)
    {
    	$query = $this->createQueryBuilder('u');
    	$query->where('u.obCustomerId = :obid');
    	$query->setParameter('obid', 'OB'.$obCustomerId);
    	$result=$query->getQuery()->getResult();
    	$flag= (!empty($result))?'OB'.$obCustomerId:'no';
    	return $flag;
    }

    /**
     * @author Employee Id: 4493
     * OB eCommerce Product - getUserById
     * return array
     * Function created to get User By Id
     */
    public function getUserByIdArray($id, $instanceId=null)
    {
    	$query=$this->createQueryBuilder('u');
    	$query->where("u.id=:id")->setParameter('id', $id);
    	if (isset($this->projectSetting['instance_id'])) {
    		$query->andWhere("u.instanceId=:instanceId")->setParameter('instanceId', $this->projectSetting['instance_id']);
    	} elseif ($instanceId!=null && $instanceId > 0) {
    		$query->andWhere("u.instanceId=:instanceId")->setParameter('instanceId', $instanceId);
    	}
    	$resultArray=$query->getQuery()->getArrayResult();
    	if(count($resultArray)>0){
    		return $resultArray[0];
    	}else {
    		return array();
    	}
    }

    /**
     * @author Employee Id: 4494
     * OB eCommerce Product - get Instance User By Id
     * Function created to get Instance User By Id
     */
    public function getInstanceUserById($id, $instanceId)
    {
    	return $this->createQueryBuilder('u')
    				->where("u.id=:id AND u.instanceId=:instanceId AND u.deletedAt is NULL")
    				->setParameter('id', $id)
    				->setParameter('instanceId', $instanceId)
    				->getQuery()->getOneorNullResult();
    }
    /**
     * @author Employee Id: 4494
     * OB eCommerce Product - Clone User data
     * return array
     * Function clone user data
     */
    public function cloneUser($user) {
    	$user = clone $user;
    	$this->_em->detach($user);
    	$this->_em->persist($user);
    	$this->_em->flush();

    	return $user;
    }

    /**
     * @author Employee Id: 4494
     * OB eCommerce Product - Set User Data
     * return array
     * Function set user data
     */
    public function set($user)
    {
    	$this->_em->persist($user);
    	$this->_em->flush();

    	return $user->getId();
    }

    public function updateUserTime($user)
    {
    	$user->setLoginTime(new \DateTime('now'));
    	$this->_em->persist($user);
    	$this->_em->flush();
    }

    public function userListForExport(){

        $userObj = $this->projectUserSetting['user_data'];
        $userId = $userObj->getId();
        $query = $this->createQueryBuilder('u');
        $query->select("u.firstName, u.middleName, u.lastName, u.companyName, u.jobTitle, u.email, u.photo, u.street1 as address, c.countryName, s.stateName, ct.cityName, u.zipcode, u.phone, u.mobile, u.fax, u.membershipNumber1 as ASI, u.membershipNumber2 as UPIC, u.membershipNumber3 as PPAI, u.membershipNumber4 as SAGE, u.membershipNumber5 as PPPC, u.membershipNumber6 as Distributor_Central,u.membershipNumber7 as Other,u.createdAt as RegistrationTimestamp,
            (CASE WHEN u.membershipNumber1 IS NOT NULL THEN 0
                  WHEN u.membershipNumber2 IS NOT NULL THEN 0
                  WHEN u.membershipNumber3 IS NOT NULL THEN 0
                  WHEN u.membershipNumber4 IS NOT NULL THEN 0
                  WHEN u.membershipNumber5 IS NOT NULL THEN 0
                  WHEN u.membershipNumber6 IS NOT NULL THEN 0
                  WHEN u.membershipNumber7 IS NOT NULL THEN 0
                  ELSE 1 END) AS NotAMember,
            u.status");
        //     	$query->addSelect('ei.position, ei.primaryJob, ei.favoriteQuote, ei.fact, ei.favoriteFood');
        //     	$query->leftJoin('u.employeeInfo','ei');
        // 		$query->leftJoin('u.userCompany','uc');
        $query->leftJoin('OfficeBrainCoreBundle:Country', 'c', 'WITH', 'u.countryId=c.id');
        $query->leftJoin('OfficeBrainCoreBundle:State', 's', 'WITH', 'u.stateId=s.id');
        $query->leftJoin('OfficeBrainCoreBundle:City', 'ct', 'WITH', 'u.cityId=ct.id');
        $query->where("u.deletedAt is NULL");
        $query->andWhere($query->expr()->in('u.status', array('active','inactive')));
        $query->where("u.parent =:userId")
        ->orWhere("u.createdUid =:userId")
        ->setParameter('userId', $userId);
        $query->andWhere($query->expr()->notIn('u.userType', array('employee','branchEmployee')));
        $query->andWhere("u.instanceId=:instanceId")->setParameter('instanceId', $this->instanceId);
        $query->orderBy("u.status",'ASC');
        return $query->getQuery()->getResult();
    }

    public function getGuestByEmail($email){
    
        $query= $this->createQueryBuilder('u')
                    ->where("u.email=:email AND u.instanceId=:instanceId AND u.deletedAt is NULL")
                    ->setParameter('email', $email)
                    ->setParameter('instanceId', $this->instanceId);
                    return $query->getQuery()->getOneorNullResult();


    }

    public function AddGuestUserNew($data)
    {

           
            $userType = 'end_user';
            $userObj = new User();
            $userObj->setEmail($data['guest_billing_address']['email']);
            $userObj->setPhone($data['guest_billing_address']['phone']);
            $name=explode(' ',$data['guest_billing_address']['name']);
            $userObj->setFirstName($name[0]);
            $userObj->setLastName($name[1]);
            //$userObj->setUserCompany($user['companyName']);
            $userObj->setZipcode($data['zipcode']);

            $userObj->setInstanceType($this->projectSetting['instance_type']);
            $userObj->setInstanceId($this->projectSetting['instance_id']);

            $userObj->setSalt($this->getUserSalt());
            $userObj->setPassword($this->setUserPassword($this->getUserConfirmationToken(6), $userObj->getSalt()));
            $userObj->setUserType($userType);
            $userObj->setStreet1($data['guest_billing_address']['street1']);
            $userObj->setStreet1($data['guest_billing_address']['street2']);
            $userObj->setCityName($data['guest_billing_address']['cityName']);
            $userObj->setCountryId($data['guest_billing_address']['countryId']);
            $userObj->setStateId($data['guest_billing_address']['stateId']);
            $userObj->setMobile($data['guest_billing_address']['mobile']);
            $userObj->setPhone($data['guest_billing_address']['phone']);
            $userObj->setExtension($data['guest_billing_address']['extension']);
            $userObj->setFax($data['guest_billing_address']['fax']);
            $userObj->setWebsite($data['guest_billing_address']['website']);
            $userObj->setCountryCulture('en_us');
            $userObj->setCreatedUid(($this->createdUid!=0) ? $this->createdUid : 1);

            $userObj->setStatus('active');
            $userObj->setConfirmationToken($this->getUserConfirmationToken());
            

            $em = $this->getEntityManager();
            $em->persist($userObj);
            $em->flush();
            return $userObj;
        }
}