<?php

namespace OfficeBrain\Bundle\BannerBundle\Entity;

use Doctrine\ORM\EntityRepository;

/**
 *
 * @author OfficeBrain 4571 <info@officebrain.com>
 *
 * Decsription: Banner Type Repository class to perform DB operation
 *
 */
/**
 * BannerMasterRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class BannerMasterRepository extends EntityRepository
{
    protected $projectSetting;
    protected $instanceId;
    protected $instanceType;
    protected $cultureId;
    protected $cultureCode;
    protected $paginatorservice;
    
    /**
     * @author Employee Id: 5058
     * OB eCommerce Product - getPaginatorService
     * Function created to get Paginator Service
     */
    public function getPaginatorService()
    {
    	return $this->paginatorservice;
    }
    
    /**
     * @author Employee Id: 5058
     * OB eCommerce Product - setPaginatorService
     * Function created to set Paginator Service
     */
    public function setPaginatorService($paginatorService)
    {
    	$this->paginatorservice = $paginatorService;
    }
    
    /**
     * @author Employee Id: 4571
     *
     * Description : Set Project Setting
     *
     * @param $projetSetting
     *
     * @return null
     *
     * @throws : null
     *
     **/
    public function setProjectSetting($projetSetting)
    {
        $this->projectSetting = $projetSetting;
        $this->prepareProjectSettingData();
    }
    
    /**
     * @author Employee Id: 4571
     *
     * Description : Get Project Setting
     *
     * @param null
     *
     * @return array
     *
     * @throws : null
     *
     **/
    public function getProjectSetting()
    {
        return $this->projetSetting;
    }
    
    /**
     * @author Employee Id: 4571
     *
     * Description : Prepare Project Setting
     *
     * @param null
     *
     * @return result
     *
     * @throws : null
     *
     **/
    public function prepareProjectSettingData()
    {
        $this->instanceId = $this->projectSetting['instance_id'];
        $this->instanceType = $this->projectSetting['instance_type'];
        $this->userId = $this->projectSetting['user_id'];
        $this->cultureId = $this->projectSetting['culture_id'];
        $this->cultureCode = $this->projectSetting['culture_code'];
    }
    
    /**
     * @author Employee Id: 4571
     *
     * Description : Set Entity
     *
     * @param $entity object
     *
     * @return committed entity
     *
     * @throws : null
     *
     **/
    public function set($banner_master)
    {
        return $this->commit($banner_master);
    }
        
    /**
     * @author Employee Id: 4571
     *
     * Description : persist and flush entities
     *
     * @param $entity object
     *
     * @return null
     *
     **/
    private function commit($entity)
    {
        $this->_em->persist($entity);
        $this->_em->flush();
    }
    
    /**
     * @author Employee Id: 4571
     *
     * Description : Get Banner Type Listing
     *
     * @param null
     *
     * @return result
     *
     * @throws : null
     *
     **/
    public function getBannerTypeList($searchWord=null)
    {
        $qb = $this->createQueryBuilder('a')
        ->select('a.id, a.bannerTypeName, a.slug, a.keywords, a.categoryId, a.productId, a.whereToShow, a.bannerDimensions, a.imageSize, a.status, a.createdAt, a.instanceType, a.instanceId') // a.bannerCategoryId, // a.height, a.width
        ->where('a.deletedAt IS NULL')
        ->andwhere('a.instanceId = :instanceId')
        ->andwhere('a.instanceType = :instanceType')
        ->setParameter('instanceId', $this->instanceId)
        ->setParameter('instanceType', $this->instanceType);
        
        if ($searchWord!="") {
            $qb->andwhere('a.bannerTypeName LIKE :searchByName');
            $qb->setParameter('searchByName', '%'.$searchWord.'%');
        }
        
        $result = $qb->orderBy('a.id', 'DESC')
        ->getQuery()->getResult();//die;
        
        return $result;
    }
    
    /**
     * @author Employee Id: 4571
     *
     * Description : Delete Banner Type (Soft Delete)
     *
     * @param id to be deleted, delete by user id
     *
     * @return result
     *
     * @throws : null
     *
     **/
    public function deleteBannerTypes($id, $deleteUid)
    {
        $bannerMaster = $this->findOneBy(array('id' => $id));
        $bannerMaster->setDeletedAt(new \DateTime());
        $bannerMaster->setDeletedUid($deleteUid);
        $this->commit($bannerMaster);
        
        return $bannerMaster;
    }
    
    /**
     * @author Employee Id: 4571
     *
     * Description : Get Banner Type Dimensions
     *
     * @param int id
     *
     * @return result
     *
     * @throws : null
     *
     **/
    public function getDimensions($id)
    {
        return $this->createQueryBuilder('a')
        ->select('a.bannerDimensions, a.imageSize') // a.height, a.width
        ->where('a.deletedAt IS NULL')
        ->andwhere('a.instanceId = :instanceId')
        ->andwhere('a.instanceType = :instanceType')
        ->andwhere('a.id = :id')
        ->setParameter('instanceId', $this->instanceId)
        ->setParameter('instanceType', $this->instanceType)
        ->setParameter('id', $id)
        ->orderBy('a.id', 'DESC')
        ->getQuery()
        ->getResult();
    }
    
    /**
     * @author Employee Id: 4571
     *
     * Description : Get all active banners by banner category
     *
     * @param 1 : int country id
     * @param 2 : int languageid
     * @param 3 : string banner category
     * @param 4 : string search value
     * @param 5 : int Category id
     *
     * @return record set
     *
     * @throws : null
     *
     **/
    public function findBannerByCategory($countryId, $languageId, $category = 'Home', $searchVal = '', $catId = 0, $slug = null)
    {
    	$visibility = array('1');
    	
    	$visibility[] = $this->userId == 0 ? 2 : 3;
    	
    	
    	//check is moudle is active or not
        //@author Employee Id: 4589
        
        if ($this->projectSetting['access_rule']['site_management']['option']['banner']['status'] != 1 || $this->projectSetting['access_rule']['site_management']['option']['banner']['option']['banner']['status'] != 1) {
            return array();
        }
        
        $result =$this->createQueryBuilder('bm')
                      ->select('b.id,bm.bannerDimensions, bm.imageSize,b.effect,b.bannerHtml,b.html1effect,b.html2effect,b.html3effect,b.html4effect,b.html5effect, b.scheduleStartDate, b.scheduleEndDate, b.bannerName, b.uploadedBanner, b.bannerUrl,b.urlStatus,b.htmlId, bl.altTags, bl.bannerTitle, b.bannerDescription, bl.bannerHtml1, bl.bannerHtml2, bl.bannerHtml3, bl.bannerHtml4, bl.bannerHtml5') // bm.height, bm.width
            		  ->where('bm.bannerCategory = :category AND bm.status = 1 AND bm.deletedAt IS NULL');
          
         
        if ($searchVal != '' && (count($catId) > 0)) {
            if (is_array($catId) && count($catId) > 0) {
                $result->andWhere('FIND_IN_SET(:searchVal, bm.keywords) != 0 OR bm.categoryId IN (:catId)')
                ->setParameter(':searchVal', $searchVal)
                    	->setParameter(':catId', implode(",", $catId));
            }
            else if($catId) {
                $result->andWhere('FIND_IN_SET(:searchVal, bm.keywords) != 0 OR bm.categoryId IN (:catId)')
                ->setParameter(':searchVal', $searchVal)
                    	->setParameter(':catId', $catId);            	
            }
        }
        else
        {
            if ($searchVal != '') {
                $result->andWhere('FIND_IN_SET(:searchVal, bm.keywords) != 0')
                ->setParameter(':searchVal', $searchVal);
            }
            if (is_array($catId) && count($catId) > 0) {
                $result->andWhere('bm.categoryId IN (:catId)')->setParameter(':catId', implode(",", $catId));
            }
            else if($catId) {
            	$result->andWhere('bm.categoryId = :catId')->setParameter(':catId', $catId);
            }
            if ($category == 'Search' && $searchVal == '') {
            	$result->andWhere('bm.keywords is NULL');
            }
        }
        
        if($slug != null){
            $result->andWhere('bm.slug LIKE :slug')
                            ->setParameter(':slug', $slug );
        }
        
        $startWhere = 'b.scheduleStartDate <= CURRENT_TIMESTAMP() OR b.scheduleStartDate IS NULL';
        $endWhere = 'b.scheduleEndDate >= :now OR b.scheduleEndDate IS NULL';        
        
        $result =    $result->innerJoin('OfficeBrainBannerBundle:Banner', 'b', 'WITH', 'b.bannerTypeId = bm.id AND b.countryId = :countryId AND b.status = 1 and b.deletedAt is NULL AND b.instanceId = :instanceId AND b.instanceType = :instanceType AND b.visibility IN(:visibility)')
        ->andWhere($startWhere)
        ->andWhere($endWhere)
        ->innerJoin('OfficeBrainBannerBundle:BannerLanguage', 'bl', 'WITH', 'bl.bannerId = b.id AND bl.languageId = :languageId')
        ->setParameter('category', $category)
        ->setParameter('countryId', $countryId)
        ->setParameter('languageId', $languageId)
        ->setParameter('instanceId', $this->instanceId)
        ->setParameter("now", new \DateTime(date('Y-m-d 00:00:00'))) // new
        ->setParameter('instanceType', $this->instanceType)
        ->setParameter('visibility', $visibility)
        ->orderBy('b.sortPosition', 'ASC')
        //->orderBy('b.createdAt', 'DESC')
        ->getQuery() //->getSql();
        ->getResult();
         
        return $result;
    }
    
    /**
     * @author Employee Id: 4571
     *
     * Description : Get active banner list
     *
     * @param 1 : int country id
     * @param 2 : int languageid
     * @param 3 : string banner category
     * @param 4 : string search value
     * @param 5 : int Category id
     *
     * @return record set
     *
     * @throws : null
     *
     **/
    public function findActiveBannerList($countryId, $languageId, $search = null, $startDate=null, $endDate=null, $page, $recordPerPage)
    {
    	$count = $this->createQueryBuilder('banner')->select('COUNT(banner)')->getQuery()->getSingleScalarResult();
    	 
        $query = $this->createQueryBuilder('bm');
        $query->select(['bm', 'sg', 'sgl']);
        $query->leftJoin('bm.banner', 'sg');
        $query->leftJoin('sg.bannerLanguage', 'sgl');
        
        $query->where('bm.status = 1 AND bm.deletedAt IS NULL')
              ->andWhere('sg.instanceId = :instanceId')
              ->andwhere('sg.instanceType = :instanceType')
              ->setParameter('instanceId', $this->instanceId)
              ->setParameter('instanceType', $this->instanceType);
        
        if ($countryId) {
            $query->andwhere('sg.countryId = :countryId');
            $query->setParameter('countryId', $countryId);
        }
        
        if ($languageId) {
            $query->andwhere('sgl.languageId = :languageId');
            $query->setParameter('languageId', $languageId);
        }
        
        if ($startDate!="") {
            $query->andwhere('sg.scheduleStartDate >= :startDate')
            ->setParameter('startDate', $startDate);
        }
        
        if ($endDate!="") {
            $query->andwhere('sg.scheduleEndDate <= :endDate')
            ->setParameter('endDate', $endDate);
        }
        
        if ($search) {
            $query->andwhere('sg.bannerName LIKE :searchByName');
            $query->setParameter('searchByName', '%'.$search.'%');
        }
        
        $query->andWhere('sg.deletedAt IS NULL');
        //$query->getQuery()->getArrayResult();
        $query->getQuery()->setHint('knp_paginator.count', $count);
		$result = $this->paginatorservice->paginate($query, $page, $recordPerPage);
        
        return $result;
    }
    
    public function changeStatus($id)
    {
    	$bannerMaster = $this->findOneBy(array('id' => $id));
       	if ($bannerMaster) {
    		$em = $this->getEntityManager();
       		if ($bannerMaster->getStatus() == 1) {
    			$bannerMaster->setStatus(0);
    		} else {
    			$bannerMaster->setStatus(1);
    		}
    		$em->persist($bannerMaster);
    		$em->flush();
       		return true;
    	}
    
    	return false;
    }
    
    public function getBannerType($instanceId,$instanceType,$bannerTypeName,$bannerCategory,$bannerDimensions)
    {
    	return $this->createQueryBuilder('a')
    	->select('a.id') // a.height, a.width
    	->where('a.deletedAt IS NULL')
    	->andwhere('a.instanceId = :instanceId')
    	->andwhere('a.instanceType = :instanceType')
    	->andwhere('a.bannerTypeName = :bannerTypeName')
    	->andwhere('a.bannerCategory = :bannerCategory')
    	->andwhere('a.bannerDimensions = :bannerDimensions')
    	->setParameter('instanceId', $instanceId)
    	->setParameter('instanceType', $instanceType)
    	->setParameter('bannerTypeName', $bannerTypeName)
    	->setParameter('bannerCategory', $bannerCategory)
    	->setParameter('bannerDimensions', $bannerDimensions)
    	->orderBy('a.id', 'DESC')
    	->getQuery()
    	->getResult();
    }
    
	/**
     * @author Employee Id: 410175
     *
     * Description : Get active banner list
     *
     * @param 1 : int country id
     * @param 2 : int languageid
     * @param 3 : string banner category
     * @param 4 : string search value
     * @param 5 : int Category id
     *
     * @return record set
     *
     * @throws : null
     *
     **/
    public function getActiveBannerListMobileAPI($bannerTypeId,$countryId=null, $languageId=null, $search = null, $startDate=null, $endDate=null)
    {
    	$mobileTypeId = (int)$bannerTypeId;
        $query = $this->createQueryBuilder('bm');
        $query->select('DISTINCT(sg.id) id,sg.uploadedBanner,sg.effect,sg.scheduleStartDate,sg.scheduleEndDate,sg.bannerName,sg.mobileAppSetting,sg.countryId');
        $query->innerJoin('bm.banner', 'sg');
        $query->leftJoin('sg.bannerLanguage', 'sgl');
        
        $query->where('bm.status = 1 AND bm.deletedAt IS NULL')
              ->andWhere('sg.instanceId = :instanceId')
              ->andwhere('sg.instanceType = :instanceType')
              ->setParameter('instanceId', $this->instanceId)
              ->setParameter('instanceType', $this->instanceType);
        
        if ($countryId) {
            $query->andwhere('sg.countryId = :countryId');
            $query->setParameter('countryId', $countryId);
        }
        
        if ($languageId) {
            $query->andwhere('sgl.languageId = :languageId');
            $query->setParameter('languageId', $languageId);
        }
        
        if ($startDate!="") {
            $query->andwhere('sg.scheduleStartDate >= :startDate')
            ->setParameter('startDate', $startDate);
        }
        
        if ($endDate!="") {
            $query->andwhere('sg.scheduleEndDate <= :endDate')
            ->setParameter('endDate', $endDate);
        }
        
        if ($search) {
            $query->andwhere('sg.bannerName LIKE :searchByName');
            $query->setParameter('searchByName', '%'.$search.'%');
        }
        
        $query->andWhere('sg.deletedAt IS NULL');
        $query->orderBy('sg.sortPosition');
        $query->andWhere('bm.id = :mobileTypeId') ->setParameter('mobileTypeId', $mobileTypeId);
        
        return $query->getQuery()->getArrayResult();
    }
}