<?php

namespace Market\ZodacoBundle\Entity;

use Doctrine\ORM\EntityRepository;

/**
 * ProductsRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class ProductsRepository extends EntityRepository {

    public function _getAdminCountlist() {
        $q = 'SELECT c FROM MarketZodacoBundle:Products c ';
        $total = $this->getEntityManager()->createQuery($q)->getResult();
        return count($total);
    }

    public function _getAdminProlist($page, $limit) {
        $q = 'SELECT c FROM MarketZodacoBundle:Products c ';
        $q .= ' ORDER BY c.id DESC';
        return $this->getEntityManager()
                        ->createQuery($q)
                        ->setMaxResults($limit)
                        ->setFirstResult(($page - 1) * $limit)
                        ->getResult();
    }

    public function _getCountlist() {
        $q = 'SELECT c FROM MarketZodacoBundle:Products c WHERE c.active = 1';
        $total = $this->getEntityManager()->createQuery($q)->getResult();
        return count($total);
    }

    public function _getProlist($page, $limit) {
        $q = 'SELECT c FROM MarketZodacoBundle:Products c ';
        $q .= ' WHERE c.active = 1 ORDER BY c.id DESC';
        return $this->getEntityManager()
                        ->createQuery($q)
                        ->setMaxResults($limit)
                        ->setFirstResult(($page - 1) * $limit)
                        ->getResult();
    }

    public function _getOneMarket() {
        $q = 'SELECT c FROM MarketZodacoBundle:Products c ';
        $q .= ' ORDER BY c.id DESC';

        return $this->getEntityManager()
                        ->createQuery($q)
                        ->setMaxResults(2)
                        ->setFirstResult(0)
                        ->getResult();
    }

    public function _delId($id) {
        $q = 'DELETE FROM MarketZodacoBundle:Products c WHERE c.id=' . $id;
        return $this->getEntityManager()
                        ->createQuery($q)
                        ->getResult();
    }

    public function _getCountListByUser($user_id) {
        $q = 'SELECT count(c.id) FROM MarketZodacoBundle:Products c';
        $q .= ' WHERE c.active = 1 AND c.u_id = :id';
        $total = $this->getEntityManager()->createQuery($q)->setParameter('id', $user_id)->getResult();
        return $total[0][1];
    }

    public function _getProListByUser($user_id, $page, $limit) {
        $q = 'SELECT c FROM MarketZodacoBundle:Products c ';
        $q .= ' WHERE c.active = 1 AND c.u_id = :id';
        $q .= ' ORDER BY c.id DESC';
        return $this->getEntityManager()
                        ->createQuery($q)
                        ->setMaxResults($limit)
                        ->setFirstResult(($page - 1) * $limit)
                        ->setParameter('id', $user_id)
                        ->getResult();
    }

    public function _getCountListByCat($p_id = 0, $c_id) {
        $q = 'SELECT count(c.id) FROM MarketZodacoBundle:Products c';
        if ($p_id != 0) {
            $q .= ' WHERE c.active = 1 AND c.p_id = :id';
        } else {
            $q .= ' WHERE c.active = 1 AND c.c_id = :id';
        }
        $total = $this->getEntityManager()->createQuery($q)->setParameter('id', $c_id)->getResult();
        return $total[0][1];
    }

    public function _getProListByCat($p_id = 0, $c_id, $page, $limit) {
        $q = 'SELECT c FROM MarketZodacoBundle:Products c ';
        if ($p_id != 0) {
            $q .= ' WHERE c.active = 1 AND c.p_id = :id';
        } else {
            $q .= ' WHERE c.active = 1 AND c.c_id = :id';
        }
        $q .= ' ORDER BY c.id DESC';
        return $this->getEntityManager()
                        ->createQuery($q)
                        ->setMaxResults($limit)
                        ->setFirstResult(($page - 1) * $limit)
                        ->setParameter('id', $c_id)
                        ->getResult();
    }

    ////////////////////////////////

    public function _getFeatureByParent($id, $limit) {
        $q = 'SELECT c FROM MarketZodacoBundle:Products c ';
        $q .= ' WHERE c.active = 1 AND c.p_id =' . $id;
        $q .= ' ORDER BY c.feature DESC, c.total_sale DESC';
        return $this->getEntityManager()
                        ->createQuery($q)
                        ->setMaxResults($limit)
                        ->setFirstResult(0)
                        ->getResult();
    }

    public function _getCountSearch($s) {
        $q = 'SELECT count(c.id) FROM MarketZodacoBundle:Products c';
        $q .= ' WHERE c.active = 1 AND c.title LIKE :title';
        $total = $this->getEntityManager()->createQuery($q)->setParameter('title', '%' . $s . '%')->getResult();
        return $total[0][1];
    }

    public function _getProSearch($s, $page, $limit) {
        $q = 'SELECT c FROM MarketZodacoBundle:Products c';
        $q .= ' WHERE c.active = 1 AND c.title LIKE :title ORDER BY c.id ASC';
        return $this->getEntityManager()->createQuery($q)->setMaxResults($limit)
                        ->setFirstResult(($page - 1) * $limit)
                        ->setParameter('title', '%' . $s . '%')->getResult();
    }

    public function _getCountMyProducts($user_id) {
        $q = 'SELECT count(c.id) FROM MarketZodacoBundle:Products c';
        $q .= ' WHERE c.u_id = :id';
        $total = $this->getEntityManager()->createQuery($q)->setParameter('id', $user_id)->getResult();
        return $total[0][1];
    }

    public function _getProMyProducts($user_id, $page, $limit) {
        $q = 'SELECT c FROM MarketZodacoBundle:Products c ';
        $q .= ' WHERE c.u_id = :id';
        $q .= ' ORDER BY c.id DESC';
        return $this->getEntityManager()
                        ->createQuery($q)
                        ->setMaxResults($limit)
                        ->setFirstResult(($page - 1) * $limit)
                        ->setParameter('id', $user_id)
                        ->getResult();
    }

    /*
     * For Admin
     */

    public function _getCountCondition($new = 0) {
        $q = 'SELECT count(c.id) FROM MarketZodacoBundle:Products c';

        if ($new) {
            $q .= ' WHERE c.active = 0 AND c.status = 0';
        } else {
            $q .= ' WHERE c.active = 1 AND c.status = 1';
        }

        $total = $this->getEntityManager()->createQuery($q)->getResult();
        return $total[0][1];
    }

    public function _getProCondition($new = 0, $page, $limit) {
        $q = 'SELECT c FROM MarketZodacoBundle:Products c ';
        if ($new) {
            $q .= ' WHERE c.active = 0 AND c.status = 0';
            $q .= ' ORDER BY c.id ASC';
        } else {
            $q .= ' WHERE c.active = 1 AND c.status = 1';
            $q .= ' ORDER BY c.versionAt ASC';
        }
        return $this->getEntityManager()
                        ->createQuery($q)
                        ->setMaxResults($limit)
                        ->setFirstResult(($page - 1) * $limit)
                        ->getResult();
    }

}
