<?php

namespace Market\ZodacoBundle\Entity;

use Doctrine\ORM\EntityRepository;

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

    public function updateReport($param) {
        $q = "UPDATE MarketZodacoBundle:Reports c  SET $param = $param + 1 WHERE c.id = 1";
        $this->getEntityManager()->createQuery($q)->execute();
    }

    public function getReport() {
        $q = "SELECT c FROM MarketZodacoBundle:Reports c WHERE c.id = 1";
        $return = $this->getEntityManager()->createQuery($q)->getResult();
        return $return ? $return[0] : false;
    }

    public function todayReport() {

        $today = "'" . date("Y-m-d 00:00:00") . "'";

        $q = 'SELECT count(c.id) FROM MarketZodacoBundle:Products c';
        $q .= ' WHERE c.createdAt >= ' . $today;
        $total = $this->getEntityManager()->createQuery($q)->getResult();
        $a["total_products"] = $total[0][1];


        $q = 'SELECT count(c.id) FROM MarketZodacoBundle:Users c';
        $q .= ' WHERE c.createdAt >= ' . $today;
        $total = $this->getEntityManager()->createQuery($q)->getResult();
        $a["total_users"] = $total[0][1];


        $q = 'SELECT count(c.id) FROM MarketZodacoBundle:HistoriesBought c';
        $q .= ' WHERE c.createdAt >= ' . $today;
        $total = $this->getEntityManager()->createQuery($q)->getResult();
        $a["total_sales"] = $total[0][1];


        $q = 'SELECT count(c.id) FROM MarketZodacoBundle:Products c';
        $q .= ' WHERE c.status = 1 AND c.active != 0 AND c.versionAt >= ' . $today;
        $total = $this->getEntityManager()->createQuery($q)->getResult();
        $a["total_update"] = $total[0][1];


        return $a;
    }

}
