<?php

namespace OfficeBrain\Bundle\ServiceGroupBundle\Entity;

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

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

        return $this;
    }

    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 getQueryBuilder()
    {
        return $this->getEntityManager()->getRepository('OfficeBrainServiceGroupBundle:ServiceRuleChargeProductPrice')->createQueryBuilder('srpp');
    }

    public function set($ServiceRuleChargeProductPrice)
    {
        $ServiceRuleChargeProductPrice->setInstanceId($this->instanceId);
        $ServiceRuleChargeProductPrice->setInstanceType($this->instanceType);
        $ServiceRuleChargeProductPrice->setCreatedAt(new \DateTime());
        $ServiceRuleChargeProductPrice->setCreatedUid($this->userId);

        $this->_em->persist($ServiceRuleChargeProductPrice);
    }

    public function removeProductByPriority($priority, $chargeType, $productId=null)
    {
        $queryBulder = $this->createQueryBuilder('srpp')
        ->delete('OfficeBrainServiceGroupBundle:ServiceRuleChargeProductPrice', 'srpp');
        $queryBulder
        ->where('srpp.priority = :priority')
        ->setParameter(':priority', $priority)

        ->andWhere('srpp.chargeType = :chargeType')
        ->setParameter(':chargeType', $chargeType);
        
        if ($productId!=null) {
            $queryBulder
            ->andWhere('srpp.productId = :productId')
            ->setParameter(':productId', $productId);
        }

        $queryBulder
        ->andWhere('srpp.instanceId = :instanceId')
        ->setParameter(':instanceId', $this->instanceId)

        ->andWhere('srpp.instanceType = :instanceType')
        ->setParameter(':instanceType', $this->instanceType);

        return $queryBulder->getQuery()->execute();
    }

    public function removeByProductId($productId, $serviceGroupId, $chargeType)
    {
        $queryBulder = $this->createQueryBuilder('srpp')
        ->delete('OfficeBrainServiceGroupBundle:ServiceRuleChargeProductPrice', 'srpp');
        $queryBulder
        ->where('srpp.productId = :productId')
        ->setParameter(':productId', $productId)

        ->andWhere('srpp.serviceGroupId = :serviceGroupId')
        ->setParameter(':serviceGroupId', $serviceGroupId)

        ->andWhere('srpp.chargeType = :chargeType')
        ->setParameter(':chargeType', $chargeType)

        ->andWhere('srpp.instanceId = :instanceId')
        ->setParameter(':instanceId', $this->instanceId)

        ->andWhere('srpp.instanceType = :instanceType')
        ->setParameter(':instanceType', $this->instanceType);

        return $queryBulder->getQuery()->execute();
    }

    public function findByProductId($productId)
    {
        $queryBulder = $this->getQueryBuilder();

        $queryBulder
        ->where('srpp.productId = :productId')
        ->setParameter(':productId', $productId)

        ->andWhere('srpp.instanceId = :instanceId')
        ->setParameter(':instanceId', $this->instanceId)

        ->andWhere('srpp.instanceType = :instanceType')
        ->setParameter(':instanceType', $this->instanceType)
        ->addOrderBy("srpp.serviceGroupId", 'ASC');

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

    public function findByProductIdAndType($productId, $chargeType, $serviceGroupId)
    {
        $queryBulder = $this->getQueryBuilder();

        $queryBulder
        ->where('srpp.productId = :productId')
        ->setParameter(':productId', $productId);

        if (isset($serviceGroupId)) {
            $queryBulder->andWhere('srpp.serviceGroupId = :serviceGroupId')
            ->setParameter(':serviceGroupId', $serviceGroupId);
        }

        $queryBulder->andWhere('srpp.chargeType = :chargeType')
        ->setParameter(':chargeType', $chargeType)

        ->andWhere('srpp.instanceId = :instanceId')
        ->setParameter(':instanceId', $this->instanceId)

        ->andWhere('srpp.instanceType = :instanceType')
        ->setParameter(':instanceType', $this->instanceType)
        ->addOrderBy("srpp.serviceGroupId", 'ASC')
        ->addOrderBy("srpp.priority", 'DESC')
        ->setMaxResults(1);

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

    public function findByProductIdAndGroupId($productId, $serviceGroupId, $instanceId)
    {
        $queryBulder = $this->getQueryBuilder();

        $queryBulder
        ->where('srpp.productId = :productId')
        ->setParameter(':productId', $productId);

        if (isset($serviceGroupId)) {
            $queryBulder->andWhere('srpp.serviceGroupId = :serviceGroupId')
            ->setParameter(':serviceGroupId', $serviceGroupId);
        }

        if ((int) $instanceId <= 0) {
            $instanceId = $this->instanceId;
        }

        $queryBulder->andWhere('srpp.instanceId = :instanceId')
        ->setParameter(':instanceId', $instanceId)

        ->andWhere('srpp.instanceType = :instanceType')
        ->setParameter(':instanceType', $this->instanceType)
        ->addOrderBy("srpp.serviceGroupId", 'ASC');

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

    public function countByProductIdAndGroupLowPriority($productId, $serviceGroupId, $priority, $chargeType)
    {
        $queryBulder = $this->getQueryBuilder();

        $queryBulder
        ->select("count(srpp.id)")
        ->where('srpp.productId = :productId')
        ->setParameter(':productId', $productId)

        ->andWhere('srpp.serviceGroupId = :serviceGroupId')
        ->setParameter(':serviceGroupId', $serviceGroupId)

        ->andWhere('srpp.chargeType = :chargeType')
        ->setParameter(':chargeType', $chargeType)

        ->andWhere('srpp.instanceId = :instanceId')
        ->setParameter(':instanceId', $this->instanceId)

        ->andWhere('srpp.priority <= :priority')
        ->setParameter(':priority', $priority)

        ->andWhere('srpp.instanceType = :instanceType')
        ->setParameter(':instanceType', $this->instanceType)
        ->addOrderBy("srpp.serviceGroupId", 'ASC');

        return $queryBulder->getQuery()->getSingleScalarResult();
    }

    public function countByProductIdAndGroupHeighPriority($productId, $serviceGroupId, $priority, $chargeType)
    {
        $queryBulder = $this->getQueryBuilder();

        $queryBulder
        ->select("count(srpp.id)")
        ->where('srpp.productId = :productId')
        ->setParameter(':productId', $productId)

        ->andWhere('srpp.serviceGroupId = :serviceGroupId')
        ->setParameter(':serviceGroupId', $serviceGroupId)

        ->andWhere('srpp.chargeType = :chargeType')
        ->setParameter(':chargeType', $chargeType)

        ->andWhere('srpp.instanceId = :instanceId')
        ->setParameter(':instanceId', $this->instanceId)

        ->andWhere('srpp.priority > :priority')
        ->setParameter(':priority', $priority)

        ->andWhere('srpp.instanceType = :instanceType')
        ->setParameter(':instanceType', $this->instanceType)
        ->addOrderBy("srpp.serviceGroupId", 'ASC');

        return $queryBulder->getQuery()->getSingleScalarResult();
    }

    public function getAllProductsByRuleMappingPriority($ruleMappingPriority)
    {
        $serviceRuleMapping = $this->createQueryBuilder('srpp')
        ->select('srpp.productId')
        ->where('srpp.priority = :priority')
        ->setParameter(':priority', $priority)
        ->groupBy("srpp.productId");

        $productArrayForMapping =  $serviceRuleMapping->getQuery()->getArrayResult();
        //echo '<pre>'; print_r($productArrayForMapping); die;

        return $this->removeProductByPriority($ruleMappingPriority);
    }

    public function commit()
    {
        $this->_em->flush();
    }

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