<?php

namespace OfficeBrain\Bundle\CmsBundle\Entity;

use Doctrine\ORM\EntityRepository;

/**
 * CmsPageMasterRepository.
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class CmsPageMasterRepository extends EntityRepository
{
    protected $projectSetting;
    protected $instanceId;
    protected $instanceType;
    protected $cultureId;
    protected $cultureCode;

    /**
     * @author Employee Id: 4795
     * Description : Set project settings
     */
    public function setProjectSetting($projetSetting)
    {
        $this->projectSetting = $projetSetting;
        $this->prepareProjectSettingData();
    }

    /**
     * @author Employee Id: 4795
     * Description : Get project settings
     */
    public function getProjectSetting()
    {
        return $this->projetSetting;
    }

    /**
     * @author Employee Id: 4795
     * Description : Prepare data from project setting
     */
    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'];
    }

    public function findByCountryId($countryId)
    {
    	
        return $this->createQueryBuilder('cp')
                    ->select('cp')
                    ->where('cp.countryId = :countryId AND cp.deletedAt IS NULL AND cp.instanceId = :instanceId')
                    ->setParameter(':countryId', $countryId)
                    ->setParameter(':instanceId', $this->instanceId)
                    ->orderBy('cp.id', 'DESC')
                    ->getQuery()
                    ->getResult();
    }

    public function findActiveByCountryId($countryId)
    {
        return $this->createQueryBuilder('cp')
                    ->select('cp')
                    ->where('cp.countryId = :countryId AND cp.status = 1 AND cp.deletedAt IS NULL AND cp.instanceId = :instanceId')
                    ->setParameter(':countryId', $countryId)
                    ->setParameter(':instanceId', $this->instanceId)
                    ->orderBy('cp.id', 'DESC')
                    ->getQuery()
                    ->getResult();
    }

    /**
     * @author Employee Id: 4795
     * Description : Get page list by country and language
     */
    public function getList($countryId, $languageId = null, $searchQuery = '')
    {
        $query = $this->createQueryBuilder('sg')
        ->select(['sg', 'sgl'])
        ->leftJoin('sg.languageMapping', 'sgl');
        if ($countryId) {
            $query->where('sg.countryId = :countryId');
            $query->setParameter('countryId', $countryId);
        }
        if ($languageId) {
            $query->andwhere('sgl.languageId = :languageId');
            $query->setParameter('languageId', $languageId);
        }

        if ($searchQuery != '') {
            $query->andWhere('sg.slug like :searchQuery');
            $query->andWhere('sgl.pageTitle like :searchQuery');
            $query->setParameter(':searchQuery', '%'.$searchQuery.'%');
        }

        $query->andWhere('sg.deletedAt IS NULL');

        $query->orderBy('sg.id', 'DESC');

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

    /**
     * @author Employee Id: 4795
     * Description : Get page by slug
     */
    public function getPageBySlug($slug, $countryId, $languageId = null)
    {
    	return $this->createQueryBuilder('p')
                    ->select('p.id, p.slug', 'p.status', 'p.templateSlug', 'l.pageTitle', 'l.pageContent', 'l.metaTitle', 'l.metaKeyword', 'l.metaDescription', 'p.css', 'p.createdUid')
                    ->leftJoin('OfficeBrainCmsBundle:CmsPageLanguage', 'l', 'WITH', 'p.id = l.pageId')
                    ->where('p.status = :statusId and p.deletedAt IS NULL AND p.slug = :slug AND p.countryId = :countryId AND p.countryId = :countryId AND l.languageId = :languageId AND p.instanceId = :instanceId')
                    ->setParameter(':statusId', 1)
                    ->setParameter('slug', $slug)
                    ->setParameter('countryId', $countryId)
                    ->setParameter('languageId', $languageId)
                    ->setParameter(':instanceId', $this->instanceId)
                    ->getQuery()
                    ->getResult();
    }
    
    /**
     * @author Employee Id: 4795
     * Description : Get page by slug
     */
    public function getSiteSearchList($search, $countryId, $languageId = null)
    {
    	return $this->createQueryBuilder('p')
    	->select('p.slug', 'p.status', 'p.templateSlug', 'l.pageTitle', 'l.pageContent', 'l.metaTitle', 'l.metaKeyword', 'l.metaDescription', 'p.css', 'p.createdUid')
    	->leftJoin('OfficeBrainCmsBundle:CmsPageLanguage', 'l', 'WITH', 'p.id = l.pageId')
    	->where('p.status = :statusId and p.deletedAt IS NULL AND p.countryId = :countryId AND l.languageId = :languageId AND p.instanceId = :instanceId AND (l.pageTitle LIKE :search OR l.metaTitle LIKE :search OR l.metaDescription LIKE :search OR l.metaKeyword LIKE :search)')
    	->setParameter(':statusId', 1)
    	->setParameter('countryId', $countryId)
    	->setParameter('languageId', $languageId)
    	->setParameter(':instanceId', $this->instanceId)
    	->setParameter(':search',"%".$search."%")
    	->getQuery()
    	->getResult();
    }

    /**
     * @author Employee Id: 4795
     * Description : Get page of specific Id
     */
    public function getPageById($id)
    {
        return $this->createQueryBuilder('p')
                    ->select('p.id', 'p.slug', 'p.templateSlug', 'p.status', 'l.pageTitle', 'l.pageContent')
                    ->leftJoin('OfficeBrainCmsBundle:CmsPageLanguage', 'l', 'WITH', 'p.id = l.pageId')
                    ->where('p.status = :statusId and p.deletedAt IS NULL and p.id = :pageId')
                    ->setParameter(':statusId', 1)
                    ->setParameter('pageId', $id)
                    ->orderBy('p.id', 'DESC')
                    ->getQuery()
                    ->getResult();
    }

    /**
     * @author Employee Id: 4795
     * Description : Find page from Id
     */
    public function find($id, $lockMode = NULL, $lockVersion = NULL)
    {
    	return $this->createQueryBuilder('cp')
                    ->select('cp')
                    ->where('cp.id = :id AND cp.deletedAt IS NULL AND cp.instanceId = :instanceId')
                    ->setParameter(':instanceId', $this->instanceId)
                    ->setParameter(':id', $id)
                    ->getQuery()
                    ->getOneOrNullResult();
    }

    /**
     * @author Employee Id: 4795
     * Description : Find page data from Id
     */
    public function findResultArrayById($id)
    {
        return $this->createQueryBuilder('cpm')
                ->select('cpm', 'cpl')
                ->leftJoin('cpm.languageMapping', 'cpl')
                ->where('cpm.id = :pageId')
                ->andWhere('cpm.id = :id')
                ->andWhere('cpm.instanceId = :instanceId')
                ->setParameter(':instanceId', $this->instanceId)
                ->setParameter(':pageId', $id)
                ->setParameter(':id', $id)
                ->getQuery()
                ->getArrayResult();
    }

    /**
     * @author Employee Id: 4795
     * Description : Find all data of Page
     */
    public function findAll()
    {
        return $this->createQueryBuilder('s')
        ->select('s')
        ->orderBy('s.id', 'DESC')
        ->getQuery()
        ->getResult();
    }

    /**
     * @author Employee Id: 4795
     * Description : Find all page by status
     */
    public function findAllByStatus($statusId)
    {
        return $this->createQueryBuilder('s')
        ->select('s')
        ->where('s.status = :statusId and s.deletedAt IS NULL')
        ->setParameter(':statusId', $statusId)
        ->orderBy('s.id', 'DESC')
        ->getQuery()
        ->getResult();
    }

    /**
     * @author Employee Id: 4795
     * Description : Find specific fields by status
     */
    public function findFieldByStatus($field, $statusId)
    {
        return $this->createQueryBuilder('s')
        ->select('s.'.$field)
        ->where('s.status = :statusId and s.deletedAt IS NULL')
        ->setParameter(':statusId', $statusId)
        ->orderBy('s.id', 'DESC')
        ->getQuery()
        ->getResult();
    }

    /**
     * @author Employee Id: 4795
     * Description : Return count requested slug
     */
    public function getCountSlugExist($slug)
    {
        $result = $this->createQueryBuilder('s')
                ->select('COUNT(s)')
                ->where('s.slug = :slug')
                ->setParameter(':slug', $slug)
                ->getQuery()->getResult();
        if ($result[0][1] > 0) {
            return $result[0][1];
        }

        return;
    }

    public function findByOption($countryId, $option, $keyword)
    {
    	
        $return = $this->createQueryBuilder('cp')
                        ->select(['cp']);

        if ($option == 'slug') {
            $return->where('cp.slug LIKE :keyword ANd cp.deletedAt IS NULL AND cp.instanceId  = :instanceId AND cp.countryId = :countryId');
        } elseif ($option == 'title') {
            /*$return = $this->createQueryBuilder('cpll')
    						->select(['cp'])
    						->from('OfficeBrainCmsBundle:CmsPageLanguage', 'cpl')
    			   			->innerJoin('OfficeBrainCmsBundle:CmsPageMaster', 'cp', 'WITH', 'cp.id = cpl.pageId')
    						->where('cpl.pageTitle LIKE :keyword AND cp.deletedAt IS NULL AND cp.instanceId  = :instanceId AND cp.countryId = :countryId');
    						*/
        }

        $return->setParameter(':keyword', '%'.$keyword.'%')
               ->setParameter(':instanceId', $this->instanceId)
               ->setParameter(':countryId', $countryId)
               ->orderBy('cp.id', 'DESC')
               ->getQuery()
               ->getResult();

        return $return;
    }

    /**
     * @author Employee Id: 4589
     * Description : Find page from slug
     */
    public function findBySlugAndCountry($slug, $countryId)
    {
        return $this->createQueryBuilder('cp')
                   ->select('cp')
                   ->where('cp.slug = :slug AND cp.countryId = :countryId AND cp.deletedAt IS NULL AND cp.instanceId = :instanceId')
                   ->setParameter(':instanceId', $this->instanceId)
                   ->setParameter(':slug', $slug)
                   ->setParameter(':countryId', $countryId)
                   ->getQuery()
                   ->getOneOrNullResult();
    }
    
    /**
     * @author Employee Id: 5058
     *
     * Description : Change Status of CMS Page
     *
     * @param $id
     *
     * @return : boolean
     *
     * @throws Exception: Null
     */
    public function changeStatus($id)
    {
    	$cmsPageMaster = $this->findOneBy(array('id' => $id));
    	if ($cmsPageMaster) {
    		if ($cmsPageMaster->getStatus() == 1) {
    			$cmsPageMaster->setStatus(0);
    		} else {
    			$cmsPageMaster->setStatus(1);
    		}
    		$this->commit($cmsPageMaster);
    		return true;
    	}
    	return false;
    }
    
    /**
     * @author Employee Id: 4571
     *
     * Description : Find single result  based on ID
     *
     * @param $id
     *
     * @return : Result
     *
     * @throws Exception: Null
     */
    public function findCount($slug, $countryId)
    {
        return $this->createQueryBuilder('cp')
                    ->select('COUNT(cp.id)')
                    ->where('cp.slug = :slug AND cp.countryId = :countryId AND cp.deletedAt IS NULL AND cp.instanceId = :instanceId')
                    ->setParameter(':instanceId', $this->instanceId)
                    ->setParameter(':slug', $slug)
                    ->setParameter(':countryId', $countryId)
                    ->getQuery()
                    ->getSingleScalarResult();
    }

    public function set($entity)
    {
        return $this->commit($entity);
    }

    private function commit($entity)
    {
        $this->_em->persist($entity);
        $this->_em->flush();

        return $entity;
    }
    
    /**
     * @author Employee Id: 4465
     * Description : Find page from Id
     */
    public function findPage($id)
    {
    	return $this->createQueryBuilder('cp')
    	->select('cp')
    	->where('cp.id = :id AND cp.deletedAt IS NULL')
    	->setParameter(':id', $id)
    	->getQuery()
    	->getOneOrNullResult();
    }
}