<?php

namespace OfficeBrain\Bundle\ProductBundle\Entity;

use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\Expr\GroupBy;

/**
 * CultureProductsRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class CultureProductsRepository extends \Doctrine\ORM\EntityRepository{
	
	public function PMEdit($cultureProductData,$productId,$culture){
		$culture_products = $this->findOneBy(array('productId'=>$productId, 'culture'=>$culture, 'deletedAt' => null));
	
		if(!is_object($culture_products)){
			$culture_products = new CultureProducts();
		}
		$culture_products->setTitle($cultureProductData['product_name']);
		$culture_products->setDescription($cultureProductData['description']);
		//$culture_products->setGeneralInformation(isset($cultureProductData['general_information']) ? $cultureProductData['general_information']:'');
		$culture_products->setProductId($productId);
		$culture_products->setCulture($culture);
	
		$this->_em->persist($culture_products);
		$this->_em->flush();
		return $culture_products;
	}
	
	public function productList($status,$searchParam,$searchQuery, $sortBy='updatedAt',$sortType='DESC',$culture_id="",$country_id = "",$supplier_id="",$firstResult = 0,$maxResult=12,$projectSuppiersIds = array(), $brandIds="", $catIds="")
	{
		$query = $this->createQueryBuilder('cp');
		if($culture_id){
			$query->where('cp.culture = :culture')
			->setParameter('culture', $culture_id);
		}
		if($supplier_id != ""){
			$query->andWhere('pm.supplierId = :supplier_id')
			->setParameter('supplier_id', $supplier_id);
		}else{
			$query->andWhere('pm.supplierId IN(:supplier_ids)')
			->setParameter('supplier_ids', array_values($projectSuppiersIds));
		}
	
		if($brandIds != ""){
			$query->andWhere('pm.brand_id IN(:brand_ids)')
			->setParameter('brand_ids', explode(',', $brandIds));
		}
	
		if(isset($searchQuery) && $searchQuery!='') {
			if(isset($searchParam) && $searchParam!=''){
				if (($searchParam=='maskedSKU') || $searchParam=='skuNumber'){
					$query->andWhere(' ILIKE(pm.'.$searchParam.',  :'.$searchParam.') = true ');
				}
				else {
					$query->andWhere(' ILIKE(cp.'.$searchParam.',  :'.$searchParam.') = true ');
				}
				$query->setParameter($searchParam, '%'.$searchQuery.'%');
			}
			else{
				$query->andWhere(' ILIKE(pm.skuNumber, :skuNumber) = true OR ILIKE(pm.maskedSKU, :maskedSKU) = true OR (( ILIKE(cp.title, :title) = true) OR ( ILIKE(cp.description, :description) = true))')
				->setParameter('skuNumber', '%'.$searchQuery.'%')
				->setParameter('maskedSKU', '%'.$searchQuery.'%')
				->setParameter('title', '%'.$searchQuery.'%')
				->setParameter('description', '%'.$searchQuery.'%');
			}
		}
		
		$query->innerJoin('OfficeBrainProductBundle:ProductMaster', 'pm','WITH','cp.productId=pm.id and pm.deletedAt is NULL')
			  ->innerJoin('OfficeBrainProductBundle:CountryProduct', 'cop','WITH','cop.productId = cp.productId');
	
		if($country_id){
			$query->andWhere('cop.countryId = :country')
			->setParameter('country', $country_id);
		}
		if($status != ""){
			$query->andWhere('cop.status = :status')
			->setParameter('status', $status);
		}
	
		if($catIds !== ""){
			$query->innerJoin('OfficeBrainProductBundle:ProductCategoryMapping','pcm', 'WITH','pm.id=pcm.product and pcm.deletedAt is NULL');
			$query->andWhere('pcm.cat IN(:cat_ids)')
			->andWhere('pcm.countryId=:country')
			->setParameter('country', $country_id)
			->setParameter('cat_ids', explode(',', $catIds));
		}
		
		if($sortBy || $sortType){
			if($sortBy == 'maskedSKU' || $sortBy == 'skuNumber') {
				$query->orderBy('pm.'.$sortType, $sortBy );
			} else {
				//$query->addOrderBy('cop.'.$sortBy,$sortType );
				$query->orderBy('pm.'.$sortType, $sortBy );
			}
		}else{
			$query->addOrderBy('cop.updatedAt','DESC');
		}

		$countQuery = $query;
		$count = $countQuery->select('count(distinct cop.id)')->getQuery()->getSingleScalarResult();
		//$query->select('pm.id as product_master_id','cp.slug','cp.id as culture_product_id','IDENTITY(cp.culture) as culture','pm.supplier_id' ,'cp.title','cp.description', 'cop.status as active', 'IDENTITY(cp.defaultImageId) as img','im.localLocation', 'pm.sku_number');
		$query->select('pm.pmsId as product_master_id','pm.id as distributor_product_id','cp.slug','cp.id as culture_product_id','cp.culture','pm.supplierId as supplier_id' ,'cp.title','cp.description', 'cop.status as active', 'pm.skuNumber as sku_number', 'pm.maskedSKU as masked_sku');
		
		if($catIds !== ""){
			$query->groupBy('pm.id ');
		}
	
		$data = $query->setFirstResult( $firstResult )
		->setMaxResults( $maxResult )->getQuery()->getResult();
		
		return array($count,$data);
	
	}
	
	public function getProductNameCulture($productId,$culture_id)
	{
		$queryBuilder = $this->getEntityManager()->createQueryBuilder();
			
		$queryBuilder->select('cp.title')
		->from("OfficeBrainProductBundle:CultureProducts", 'cp')
		//->leftJoin('OfficeBrainCultureBundle:Culture', 'cul','WITH','cul.id=cp.culture and cul.status=TRUE')
		->where('cp.productId = :pid')
		->setParameter('pid', $productId)
		->andWhere('cp.deletedAt is NULL') //For soft delete - ProductMaster
		->andWhere($queryBuilder->expr()->notIn('cp.culture', $culture_id));
		//->andWhere($queryBuilder->expr()->notIn('cul.id', $culture_id));
		return $queryBuilder->getQuery()->getArrayResult();
	}
	
	public function addCultureDataFromSolr($cultureProductData,$productId,$culture){
		$culture_products = new CultureProducts();
		$culture_products->setTitle($cultureProductData['title']);
		$culture_products->setDescription($cultureProductData['description']);
		$culture_products->setProductId($productId);
		$culture_products->setCulture($culture);
		$this->_em->persist($culture_products);
		$this->_em->flush();
		return $culture_products;
	}
}