<?php

namespace OfficeBrain\Bundle\CouponBundle\Entity;

use Doctrine\ORM\EntityRepository;

/**
 * @author OfficeBrain 4835 <info@officebrain.com>
 * CouponRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class CouponRepository extends EntityRepository
{
    protected $projectSetting;
    protected $instanceId;
    protected $instanceType;
    protected $cultureId;
    protected $cultureCode;

    public function setProjectSetting($projetSetting)
    {
        $this->projectSetting = $projetSetting;
        $this->prepareProjectSettingData();
    }

    public function getProjectSetting()
    {
        return $this->projetSetting;
    }

    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 setData($coupon)
    {
        $this->_em->persist($coupon);

        return $coupon;
    }

    public function chkCodeExsitorNot($code)
    {
        $query = $this->createQueryBuilder('c')->select('c')
                ->where('c.couponCode =:couponCode')
                ->setParameter('couponCode', $code)
                ->getQuery();

        return $products = count($query->getResult());
    }

    public function findFromCouponCode($code)
    {
        $query = $this->createQueryBuilder('c')->select('c')
                        ->where('c.couponCode =:couponCode')
                        ->setParameter('couponCode', $code)
                        ->getQuery()->getOneOrNullResult();

        return $query;
    }

    public function findCouponList($country_id, $coupon_type = null, $search_query = null)
    {
        $em = $this->getEntityManager();
        $qb = $em->createQueryBuilder();
        $qb->select('c');
        $qb->from('OfficeBrainCouponBundle:Coupon', 'c');
        //$qb->leftjoin('c.languageMapping', 'cl');
        $qb->where('c.deletedAt is NULL');
        $qb->andWhere('c.instanceId  = :instanceId')->setParameter(':instanceId', $this->instanceId);
        //$qb->andwhere('c.isCsv = :isCsv')->setParameter('isCsv', 'N');
        $qb->andwhere('c.countryId = :countryId')->setParameter('countryId', $country_id);

        if ($coupon_type != '') {
            $qb->andwhere('c.couponType = :coupon_type')->setParameter('coupon_type', $coupon_type);
        }
        if ($search_query != '') {
            $qb->andwhere('c.couponCode  like :search_query')->setParameter('search_query', '%'.$search_query.'%');
        }

        $qb->orderBy('c.id', 'desc');

        $couponObj = $qb->getQuery()->getSQL();

        return $couponObj = $qb->getQuery()->getResult();
    }

    public function setsmartData($coupon)
    {
        $this->_em->persist($coupon);
        $this->_em->flush();

        return $coupon;
    }

    /**
     * Commit entity.
     */
    public function commit()
    {
        $this->_em->flush();

        return;
    }

    public function smartcommit()
    {
        $this->_em->clear();

        return;
    }

    public function findCouponCsv($ids)
    {
        // print_r($ids);die;
        //  echo "qqqqqqqqq".$ids;
        $em = $this->getEntityManager();
        $qb = $em->createQueryBuilder();
        $qb->select('c.id', 'group_concat(DISTINCT cl.couponTitle) as title', "group_concat(DISTINCT cm.categoryId SEPARATOR '|') as category", "group_concat(DISTINCT pm.productSku SEPARATOR '|') as product", "group_concat(DISTINCT pn.productSku SEPARATOR '|') as exproduct", 'c.countryId', 'c.couponCode', 'c.isBeforeTax', 'c.isFreeShipping', 'c.isSaleItem', 'c.couponAmount', 'c.couponType', 'c.usageLimit', 'c.usageCount', 'c.startDate', 'c.endDate', 'c.status', 'c.userEmail', 'c.userGroups');
        $qb->from('OfficeBrainCouponBundle:Coupon', 'c');
        $qb->leftjoin('OfficeBrainCouponBundle:CouponCategoryMapping', 'cm', 'WITH', 'cm.couponId=c.id');
        $qb->leftjoin('OfficeBrainCouponBundle:CouponProductMapping', 'pm', 'WITH', "pm.couponId=c.id and pm.isAllowed='Y'");
        $qb->leftjoin('OfficeBrainCouponBundle:CouponProductMapping', 'pn', 'WITH', "pn.couponId=c.id and pn.isAllowed='N'");
        $qb->leftjoin('OfficeBrainCouponBundle:CouponLanguage', 'cl', 'WITH', 'cl.couponId=c.id');
        $qb->add('where', $qb->expr()->in('c.id', '?1'))
                ->setParameter(1, $ids);
        $qb->andwhere('c.deletedAt is NULL');

        $qb->andWhere('c.instanceId  = :instanceId')->setParameter(':instanceId', $this->instanceId);
        $qb->groupBy('c.id');

        $couponObj = $qb->getQuery()->getArrayResult();
        //  echo "<pre>";print_r($couponObj);die;

        return $couponObj = $qb->getQuery()->getArrayResult();
    }

    public function findCouponCsvLanguage($ids)
    {
        $em = $this->getEntityManager();
        $qb = $em->createQueryBuilder();
        $qb->select('c.id', 'group_concat(cl.couponTitle) as title', 'cl.languageId');
        $qb->from('OfficeBrainCouponBundle:Coupon', 'c');
        $qb->leftjoin('OfficeBrainCouponBundle:CouponLanguage', 'cl', 'WITH', 'cl.couponId=c.id');
        $qb->add('where', $qb->expr()->in('c.id', '?1'))
                ->setParameter(1, $ids);
        $qb->andwhere('c.deletedAt is NULL');
        $qb->andWhere('c.instanceId  = :instanceId')->setParameter(':instanceId', $this->instanceId);
        $qb->groupBy('c.id');

        return $couponObj = $qb->getQuery()->getArrayResult();
    }

    public function findCouponCategories($ids)
    {
        $em = $this->getEntityManager();
        $qb = $em->createQueryBuilder();
        $qb->select('c.id', "group_concat(cm.categoryId SEPARATOR '|') as category");
        $qb->from('OfficeBrainCouponBundle:Coupon', 'c');
        $qb->leftjoin('OfficeBrainCouponBundle:CouponCategoryMapping', 'cm', 'WITH', 'cm.couponId=c.id');
        $qb->add('where', $qb->expr()->in('c.id', '?1'))
                ->setParameter(1, $ids);
        $qb->andwhere('c.deletedAt is NULL');
        $qb->andWhere('c.instanceId  = :instanceId')->setParameter(':instanceId', $this->instanceId);
        $qb->groupBy('c.id');

        return $couponObj = $qb->getQuery()->getArrayResult();
    }
    public function findAll()
    {
        return $this->createQueryBuilder('s')
                        ->select('s')
                        ->orderBy('s.id', 'DESC')
                        ->getQuery()
                        ->getResult();
    }

    public function findCoupon($id)
    {
        $em = $this->getEntityManager();
        $qb = $em->createQueryBuilder();
        $qb->select(['c', 'cl']);
        $qb->from('OfficeBrainCouponBundle:Coupon', 'c');
        $qb->innerjoin('c.languageMapping', 'cl');
        $qb->Where('c.id  = :id')->setParameter(':id', $id);
        $qb->andwhere('c.deletedAt is NULL');
       // $qb->andWhere('c.instanceId  = :instanceId')->setParameter(':instanceId', $this->instanceId);
        $qb->groupBy('c.id');
        $couponObj = $qb->getQuery()->getResult();

        return $couponObj;
    }

    public function find($id, $lockMode = NULL, $lockVersion = NULL)
    {
        $query = $this->createQueryBuilder('s')
                        ->select('s')
                        ->where('s.deletedAt IS NULL')
                        ->andWhere('s.id  = :id')
                        ->andWhere('s.instanceId  = :instanceId')
                        ->setParameter(':instanceId', $this->instanceId)
                        ->setParameter(':id', $id)
                        ->getQuery()
                        ->getSingleResult();

        return $query;
    }

    public function findByStatus($status_id)
    {
        return $this->createQueryBuilder('s')
                        ->select('s')
                        ->where('s.status = :status_id and s.deletedAt IS NULL')
                        ->setParameter(':status_id', $status_id)
                        ->andWhere('s.instanceId  = :instanceId')
                        ->setParameter(':instanceId', $this->instanceId)
                        ->orderBy('s.id', 'DESC')
                        ->getQuery()
                        ->getResult();
    }

    public function findByDelete()
    {
        return $this->createQueryBuilder('s')
                        ->select('s')
                        ->where('s.deletedAt IS NOT NULL')
                        ->andWhere('s.instanceId  = :instanceId')
                        ->setParameter(':instanceId', $this->instanceId)
                        ->orderBy('s.id', 'DESC')
                        ->getQuery()
                        ->getResult();
    }
    
     public function findCouponView($id)
    {
        $em = $this->getEntityManager();
        $qb = $em->createQueryBuilder();
        $qb->select('c');
        $qb->from('OfficeBrainCouponBundle:Coupon', 'c');
        $qb->Where('c.id  = :id')->setParameter(':id', $id);
        $qb->andwhere('c.deletedAt is NULL');
        $qb->andWhere('c.instanceId  = :instanceId')->setParameter(':instanceId', $this->instanceId);
        
        return $couponObj = $qb->getQuery()->getResult();
    }
}