Volume.php

<?php

namespace Acude\Volume\Model;

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;
use Zend\Stdlib\Hydrator;

/**
 * @ORM\Entity
 * @ORM\Table(name="acude.volume")
 * @ORM\Entity(repositoryClass="Acude\Volume\Model\VolumeRepository")
 */
class Volume {

    /**
     * @ORM\Id
     * @ORM\Column(name="vol_codigo", type="integer")
     * @ORM\GeneratedValue
     * @var int
     */
    private $codigo;

    /**
     * @ORM\ManyToOne(targetEntity="Acude\Reservatorio\Model\Reservatorio", inversedBy="volume")
     * @ORM\JoinColumn(name="vol_reservatorio", referencedColumnName="res_cod")
     * @var int
     */
    private $reservatorio;

    /**
     * @ORM\Column(name="vol_valor", type="float")
     * @var float
     */
    private $valor;

    /**
     * @ORM\Column(name="vol_data_hora_coleta", type="datetime")
     * @var string
     */
    private $dataColeta;

    /**
     * @ORM\Column(name="vol_data_hora_cadastro", type="datetime")
     * @var string
     */
    private $dataCadastro;

    /**
     * @ORM\Column(name="vol_percentual", type="float")
     * @var float
     */
    private $percentual;

    public function __construct(array $options = array()) {
        $hydrator = new Hydrator\ClassMethods;
        $hydrator->hydrate($options, $this);
        $this->reservatorio = new ArrayCollection();
        $this->setDataCadastro('now');
    }

    public function setCodigo($codigo) {
        $this->codigo = $codigo;
        return $this;
    }

    public function getCodigo() {
        return $this->codigo;
    }

    public function getReservatorio() {
        return $this->reservatorio;
    }

    public function setReservatorio($reservatorio) {
        $this->reservatorio = $reservatorio;
    }

    public function getValor() {
        return $this->valor;
    }

    public function setValor($valor) {
        $this->valor = $valor;
    }

    public function getDataColeta() {
        return $this->dataColeta;
    }

    public function setDataColeta($dataColeta) {
        $this->dataColeta = new \DateTime($dataColeta);
    }

    public function getDataCadastro() {
        return $this->dataCadastro;
    }

    public function setDataCadastro($dataCadastro) {
        $this->dataCadastro = new \DateTime($dataCadastro);
    }

    public function getPercentual() {
        return $this->percentual;
    }

    public function setPercentual($percentual) {
        $this->percentual = $percentual;
    }

    public function toArray() {

        return array(
            'codigo' => $this->getCodigo(),
            'reservatorio' => $this->getReservatorio()->toArray(),
            'valor' => $this->getValor(),
            'dataColeta' => $this->getDataColeta()->format('Y-m-d H:i:s'),
            'dataCadastro' => $this->getDataCadastro()->format('Y-m-d H:i:s'),
            'percentual' => $this->getPercentual()
        );
    }

}

VolumeRepository.php

<?php

namespace Acude\Volume\Model;

use Generic\Model\GenericRepository;

class VolumeRepository extends GenericRepository {

    public function findLastVol() {
        $sql = " SELECT MAX(v.codigo) FROM Acude\Volume\Model\Volume v";
        $q = $this->_em->createQuery($sql);
        return current(current($q->getResult()));
    }

    public function findByVolumeIntercaloData($id, $data) {
        $sql = "SELECT v FROM Acude\Volume\Model\Volume v WHERE v.reservatorio = " . $id->getCodigo() . " AND v.dataColeta >= '" . $data['ini']->format("Y-m-d H:i:s") . "' AND v.dataColeta <= '" . $data['fim']->format("Y-m-d H:i:s") . "' ORDER BY v.dataColeta DESC";
        $q = $this->_em->createQuery($sql)->setMaxResults(1);
        return $q->getResult();
    }

    public function findByReservatorioDatas($param) {
        $sql = "SELECT v FROM Acude\Volume\Model\Volume v WHERE v.reservatorio = " . $param['res_codigo'] . " AND v.dataColeta >= '" . $param['data_ini'] . "' AND v.dataColeta <= '" . $param['data_fim'] . "' ";
        $q = $this->_em->createQuery($sql);
        return $q->getResult();
    }

    public function findByVolumeData($id, $data) {
        $sql = "SELECT v FROM Acude\Volume\Model\Volume v WHERE v.reservatorio = " . $id->getCodigo() . " AND v.dataColeta >= '$data 00:00:00' AND v.dataColeta <= '$data 23:59:59' ";
        $q = $this->_em->createQuery($sql);
        return $q->getResult();
    }

    public function findByDataColeta($data = null) {

        $estados = null;
        $andWhereEstados = "";

        if (is_array($data)) {

            if (isset($data['estados'])) {
                $andWhereEstados = $this->andWhereEstados($data['estados']);
            }

            if (isset($data['data_ini'])) {

                $data_ini = $data['data_ini'];

                if (isset($data['data_fim'])) {
                    $data_fim = $data['data_fim'];
                } else {
                    $data_fim = $data['data_ini'];
                }
            }
        } elseif (!is_null($data) && is_string($data)) {
            $data_ini = $data;
            $data_fim = $data;
        }

        if (!isset($data_ini)) {
            $data_ini = date('Y-m-d');
            $data_fim = date('Y-m-d');
        }



        $sql = " SELECT v FROM Acude\Volume\Model\Volume v
            INNER JOIN Acude\Reservatorio\Model\Reservatorio r
            WHERE  v.dataColeta>='$data_ini 00:00:00' and v.dataColeta<='$data_fim 23:00:00' and v.reservatorio=r.codigo ";


        if (!empty($andWhereEstados)) {
            $sql .= " and ( $andWhereEstados ) ";
        }


        $q = $this->_em->createQuery($sql);

        return $q->getResult();
    }

    public function findByDataColetaReservatorio($data = null, $reservatorio = NULL) {

        if (!$reservatorio || $reservatorio == '') {
            return $this->findByDataColeta($data);
        } else {

            $estados = null;
            $andWhereEstados = "";

            if (is_array($data)) {

                if (isset($data['estados'])) {
                    $andWhereEstados = $this->andWhereEstados($data['estados']);
                }

                if (isset($data['data_ini'])) {

                    $data_ini = $data['data_ini'];

                    if (isset($data['data_fim'])) {
                        $data_fim = $data['data_fim'];
                    } else {
                        $data_fim = $data['data_ini'];
                    }
                }
            } elseif (!is_null($data) && is_string($data)) {
                $data_ini = $data;
                $data_fim = $data;
            }

            if (!isset($data_ini)) {
                $data_ini = date('Y-m-d');
                $data_fim = date('Y-m-d');
            }

            $sql = "SELECT v FROM Acude\Volume\Model\Volume v
                        INNER JOIN Acude\Reservatorio\Model\Reservatorio r
                    WHERE
                        v.dataColeta >= '$data_ini 00:00:00' "
                    . "AND v.dataColeta <= '$data_fim 23:00:00' "
                    . "AND v.reservatorio = r.codigo "
                    . "AND r.codigo = $reservatorio";

            if (!empty($andWhereEstados)) {
                $sql .= " and ( $andWhereEstados ) ";
            }


            $q = $this->_em->createQuery($sql);

            return $q->getResult();
        }
    }

    private function andWhereEstados($estados) {
        $first = true;
        $andWhereEstados = "";

        foreach ($estados as $estado) {
            if ($first) {
                $andWhereEstados .= " r.uf=$estado ";
                $first = false;
            } else {
                $andWhereEstados .= " OR r.uf=$estado ";
            }
        }

        return $andWhereEstados;
    }

}