<?php

namespace OfficeBrain\CustomBundle\ProductBundle\Entity;

/**
 * SkuMaskingRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class SkuMaskingRepository extends \Doctrine\ORM\EntityRepository
{
	protected $projectSetting;
	protected $instanceId;
	protected $cultureId;
	protected $cultureCode;
	
	/**
	 * @author Employee Id: 5497
	 * Description : Set project settings
	 */
	public function setProjectSetting($projetSetting)
	{
		$this->projectSetting = $projetSetting;
		$this->prepareProjectSettingData();
	}
	
	/**
	 * @author Employee Id: 5497
	 * Description : Get project settings
	 */
	public function getProjectSetting()
	{
		return $this->projetSetting;
	}
	
	/**
	 * @author Employee Id: 5497
	 * Description : Prepare data from project setting
	 */
	public function prepareProjectSettingData()
	{
		$this->instanceId = $this->projectSetting['instance_id'];
		$this->cultureId = $this->projectSetting['culture_id'];
		$this->cultureCode = $this->projectSetting['culture_code'];
	}
	
	/**
	 * @author Employee Id: 5497
	 * Description : Get sku list
	 */
	public function getList($searchQuery = '')
	{
		$query = $this->createQueryBuilder('sg')
		->select('sg');
		if ($searchQuery != '') {
			$query->andWhere('sg.sku like :searchQuery');
			$query->setParameter(':searchQuery', '%'.$searchQuery.'%');
		}
		$query->orderBy('sg.id', 'DESC');
	
		return $query->getQuery()->getResult();
	}
	
	/**
	 * @author Employee Id: 5497
	 * Description : Get page by sku
	 */
	public function findBySku($sku)
	{
		return $this->createQueryBuilder('p')
		->select('p')
		->where('p.sku = :sku')
		->setParameter(':sku', $sku)
		->getQuery()
		->getResult();
	}
	
	/**
	 * @author Employee Id: 5633
	 * Description : Get page by sku and supplier_id
	 */
	public function findBySkuAndSupplier($sku, $supplier_id)
	{
		return $this->createQueryBuilder('p')
		->select('p')
		->where('p.sku = :sku AND p.supplierId = :supplierId')
		->setParameter(':sku', $sku)
		->setParameter(':supplierId', $supplier_id)
		->getQuery()
		->getResult();
	}
	
	/**
	 * @author Employee Id: 5633
	 * Description : Get page by masking sku
	 */
	public function findByMaskingSku($sku)
	{
		return $this->createQueryBuilder('p')
		->select('p')
		->where('p.maskingSku = :sku')
		->setParameter(':sku', $sku)
		->getQuery()
		->getResult();
	}
	
	/**
	 * @author Employee Id: 5497
	 * Description : Get page by sku
	 */
	public function getSiteSearchList($search)
	{
		return $this->createQueryBuilder('p')
		->select('p')
		    	->where('l.sku LIKE :search OR l.maskingSku LIKE :search OR l.supplierId LIKE :search')
		    	->setParameter(':search',"%".$search."%")
		->getQuery()
		->getResult();
	}
	
	/**
	 * @author Employee Id: 5497
	 * Description : Get sku of specific Id
	 */
	public function getMaskingById($id)
	{
		return $this->createQueryBuilder('cp')
		->select('cp')
		->where('cp.id = :id')
		->setParameter(':id', $id)
		->getQuery()
		->getOneOrNullResult();
	}
	
	/**
	 * @author Employee Id: 5497
	 * Description : Find all data of sku
	 */
	public function findAll()
	{
		return $this->createQueryBuilder('s')
		->select('s')
		->orderBy('s.id', 'DESC')
		->getQuery()
		->getResult();
	}
	
	
	/**
	 * @author Employee Id: 5497
	 * Description : Return count requested sku
	 */
	public function getCountSkuExist($sku)
	{
		$result = $this->createQueryBuilder('s')
		->select('COUNT(s)')
		->where('s.sku = :sku')
		->setParameter(':sku', $sku)
		->getQuery()->getResult();
		if ($result[0][1] > 0) {
			return $result[0][1];
		}
	
		return;
	}
	
	/**
	 * @author Employee Id: 4571
	 * Description : Return orig sku from new requested sku
	 * 
	 */
	public function findOldSkuCount($sku,$suppId)
	{
		$result = $this->createQueryBuilder('s')
		->select('COUNT(s.sku)') 
		->where('s.maskingSku = :maskingSku')
		->andWhere('s.supplierId = :supplierId')
		->setParameter('maskingSku', $sku)
		->setParameter('supplierId', $suppId)
		->getQuery()->getSingleScalarResult(); 		
		return $result; 
	}
	
	/**
	 * @author Employee Id: 5668
	 * Description : Return only masked SKU from SKU
	 *
	 */
	public function findMaskedSku($sku,$suppId)
	{
		$result = $this->createQueryBuilder('s')
		->select('s.maskingSku')
		->where('s.sku = :sku')
		->andWhere('s.supplierId = :supplierId')
		->setParameter('sku', $sku)
		->setParameter('supplierId', $suppId)
		->getQuery()->getSingleScalarResult();
		return $result;
	}
	
	/**
	 * @author Employee Id: 4571
	 * Description : Return orig sku from new requested sku
	 *
	 */
	public function findOldSku($sku,$suppId)
	{
		$result = $this->createQueryBuilder('s')
		->select('s.sku')
		->where('s.maskingSku = :maskingSku')
		->andWhere('s.supplierId = :supplierId')
		->setParameter('maskingSku', $sku)
		->setParameter('supplierId', $suppId)
		->getQuery()->getSingleScalarResult();
		return $result;
	}
	
	
	
	
	public function findByOption($option, $keyword)
	{
		$return = $this->createQueryBuilder('cp')
		->select(['cp']);
	
		if ($option == 'sku') {
			$return->where('cp.sku LIKE :keyword');
		} 
		elseif ($option == 'masking_sku') {
			$return->where('cp.maskingSku LIKE :keyword');
		}
	
		$return->setParameter(':keyword', '%'.$keyword.'%')
		->orderBy('cp.id', 'DESC')
		->getQuery()
		->getResult();
	
		return $return;
	}
	
	/**
	 * @author Employee Id: 5497
	 *
	 * Description : Find single result  based on ID
	 *
	 * @param $id
	 *
	 * @return : Result
	 *
	 * @throws Exception: Null
	 */
	public function findCountBySku($supplierId, $sku)
	{
		return $this->createQueryBuilder('cp')
		->select('COUNT(cp.id)')
		->where('cp.sku = :sku AND cp.supplierId = :supplierId')
		->setParameter(':sku', $sku)
		->setParameter(':supplierId', $supplierId)
		->getQuery()
		->getSingleScalarResult();
	}

	/**
	 * @author Employee Id: 5497
	 *
	 * Description : Find single result  based on ID
	 *
	 * @param $id
	 *
	 * @return : Result
	 *
	 * @throws Exception: Null
	 */
	public function findCount()
	{
		return $this->createQueryBuilder('cp')
		->select('COUNT(cp.id)')
		->getQuery()
		->getSingleScalarResult();
	}
	
	public function set($entity)
	{
		return $this->commit($entity);
	}
	
	private function commit($entity)
	{
		$this->_em->persist($entity);
		$this->_em->flush();
	
		return $entity;
	}
	
	public function removeRecord($entity)
	{
		return $this->remove($entity);
	}
	
	private function remove($entity)
	{
		$this->_em->remove($entity);
		$this->_em->flush();
	}

	public function getMaskingSkuBySupplierId($supplierId, $maskingSku)
	{
		return $this->createQueryBuilder('p')
					->select('p')
					->where('p.sku = :maskingSku AND p.supplierId = :supplierId')
					->setParameter(':maskingSku', $maskingSku)
					->setParameter(':supplierId', $supplierId)
					->getQuery()
					->getResult();
	}
	
	public function getMaskingBySupplierId($supplierId)
	{
		return $this->createQueryBuilder('p')
		->select('p')
		->where('p.supplierId = :supplierId')
		->setParameter(':supplierId', $supplierId)
		->getQuery()
		->getResult();
	}
	
	public function getMaskingByGroupBySupplierId($id)
	{
		return  $this->createQueryBuilder('p')
		//->select('max(p.maskingSku)')
		->select('p.maskingSku')
		->where('p.id = :id')
		->setParameter(':id', $id)
		/*->where('p.supplierId = :supplierId')
		 ->setParameter(':supplierId', $supplierId)
		 ->groupBy("p.supplierId")*/
		->getQuery()
		->getOneOrNullResult();
	}
	
	public function getIdByGroupBySupplierId($supplierId)
	{
		return $this->createQueryBuilder('p')
		->select('max(p.id) as cnt')
		->where('p.supplierId = :supplierId')
		->setParameter(':supplierId', $supplierId)
		->groupBy("p.supplierId")
		->getQuery()
		->getOneOrNullResult();
	}
	
	/**
	 * @author Employee Id: 5412
	 * Description : Get sku of using product id
	 */
	public function getMaskingByproductId($product_id)
	{
		return $this->createQueryBuilder('cp')
		->select('cp')
		->where('cp.productId = :productId')
		->setParameter(':productId', $product_id)
		->getQuery()
		->getOneOrNullResult();
	}
	
	public function getMaskingIdByMaskingSku($prod_id,$masking_sku)
	{
		return $this->createQueryBuilder('p')
		->select('p.id')
		->where('p.productId = :productId And p.maskingSku = :maskingSku')
		->setParameter(':productId', $prod_id)
		->setParameter(':maskingSku', $masking_sku)
		->getQuery()
		->getOneOrNullResult();
	}
}