<?php

namespace Swpb\Bundle\CocarBundle\Entity;

use Doctrine\ORM\EntityRepository;

/**
 * PrinterCounterRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
//acrescentado serie e local
class PrinterCounterRepository extends EntityRepository
{
    /**
     * Classe do relatório geral de impressão
     *
     * @param $start
     * @param $end
     * @return array
     */
    public function relatorioGeral($start, $end) {


        $_dql = "SELECT printer.id,
                        pc1.blackInk,
                        pc1.coloredInk,
                        max(pc1.date) as endDate,
                        (SELECT pc2.prints FROM CocarBundle:PrinterCounter pc2 WHERE pc2.date = max(pc1.date) AND pc2.printer = printer.id) as printsEnd,
                        min(pc1.date) as startDate,
                        (SELECT pc3.prints FROM CocarBundle:PrinterCounter pc3 WHERE pc3.date = min(pc1.date) AND pc3.printer = printer.id) as printsStart,
                        printer.name,
                        printer.description,
                        printer.serie,
                        printer.local,
                        printer.host
                 FROM CocarBundle:Printer printer
                 LEFT JOIN CocarBundle:PrinterCounter pc1 WITH (pc1.printer = printer.id AND pc1.date BETWEEN :start AND :end)
                 GROUP BY printer.id,
                        pc1.blackInk,
                        pc1.coloredInk,
                        printer.name,
                        printer.description,
                        printer.host,
                        printer.serie,
                        printer.local
                 ORDER BY printer.id ASC";

        return $this->getEntityManager()->createQuery( $_dql )
            ->setParameter('start', $start)
            ->setParameter('end', $end)
            ->getArrayResult();

    }

    /**
     * Classe do relatório geral de impressão no formato CSV
     *
     * @param $start
     * @param $end
     * @return array
     */
    public function relatorioCsvGeral($start, $end) {


        $_dql = "SELECT printer.id,
                        printer.name,
                        printer.host,
                        printer.serie,
                        printer.local,
                        (SELECT pc2.prints FROM CocarBundle:PrinterCounter pc2 WHERE pc2.date = max(pc1.date) AND pc2.printer = printer.id) as printsEnd,
                        (SELECT pc3.prints FROM CocarBundle:PrinterCounter pc3 WHERE pc3.date = min(pc1.date) AND pc3.printer = printer.id) as printsStart
                 FROM CocarBundle:Printer printer
                 LEFT JOIN CocarBundle:PrinterCounter pc1 WITH (pc1.printer = printer.id AND pc1.date BETWEEN :start AND :end)
                 GROUP BY printer.id,
                        printer.name,
                        printer.description,
                        printer.host,
                        printer.serie,
                        printer.local
                 ORDER BY printer.id ASC";

        return $this->getEntityManager()->createQuery( $_dql )
            ->setParameter('start', $start)
            ->setParameter('end', $end)
            ->getArrayResult();

    }

    /**
     * Classe do relatório geral de impressão no formato CSV detalhado
     *
     * @param $start
     * @param $end
     * @return array
     */
    public function relatorioCsvGeralDetalhado($start, $end) {


        $_dql = "SELECT printer.id,
                        max(pc1.date) as endDate,
                        (SELECT pc2.prints FROM CocarBundle:PrinterCounter pc2 WHERE pc2.date = max(pc1.date) AND pc2.printer = printer.id) as printsEnd,
                        min(pc1.date) as startDate,
                        (SELECT pc3.prints FROM CocarBundle:PrinterCounter pc3 WHERE pc3.date = min(pc1.date) AND pc3.printer = printer.id) as printsStart,
                        printer.name,
                        printer.host,
                        printer.serie,
                        printer.local
                 FROM CocarBundle:Printer printer
                 LEFT JOIN CocarBundle:PrinterCounter pc1 WITH (pc1.printer = printer.id AND pc1.date BETWEEN :start AND :end)
                 GROUP BY printer.id,
                        printer.name,
                        printer.description,
                        printer.host,
                        printer.serie,
                        printer.local
                 ORDER BY printer.id ASC";

        return $this->getEntityManager()->createQuery( $_dql )
            ->setParameter('start', $start)
            ->setParameter('end', $end)
            ->getArrayResult();

    }
}