<?php

namespace Swpb\Bundle\CocarBundle\Entity;

use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\ResultSetMapping;

/**
 * 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) {

        $rsm = new ResultSetMapping();
        $rsm->addScalarResult('blackink', 'blackInk');
        $rsm->addScalarResult('coloredink', 'coloredInk');
        $rsm->addScalarResult('enddate', 'endDate');
        $rsm->addScalarResult('printsend', 'printsEnd');
        $rsm->addScalarResult('startdate', 'startDate');
        $rsm->addScalarResult('printsstart', 'printsStart');
        $rsm->addScalarResult('name', 'name');
        $rsm->addScalarResult('description', 'description');
        $rsm->addScalarResult('serie', 'serie');
        $rsm->addScalarResult('local', 'local');
        $rsm->addScalarResult('host', 'host');
        $rsm->addScalarResult('id', 'id');


        $sql = "SELECT printer.id,
                        pc1.blackink,
                        pc1.coloredink,
                        to_timestamp(CASE WHEN max(pc1.date) IS NULL
                          THEN (CASE WHEN (SELECT min(date)
						FROM tb_printer_counter
						WHERE date >= ?
						AND printer_id = printer.id) IS NULL
					THEN (SELECT max(date)
						FROM tb_printer_counter
						WHERE printer_id = printer.id)
					ELSE (SELECT min(date)
						FROM tb_printer_counter
						WHERE date >= ?
						AND printer_id = printer.id)
				END)
                          ELSE max(pc1.date)
                        END) as endDate,

                (CASE WHEN (SELECT pc2.prints
				FROM tb_printer_counter pc2
				WHERE pc2.date = max(pc1.date)
				AND pc2.printer_id = printer.id) IS NULL
                          THEN (CASE WHEN (SELECT pc3.prints
						FROM tb_printer_counter pc3
						WHERE pc3.date = (SELECT min(date)
							FROM tb_printer_counter
							WHERE date >= ?
							AND printer_id = printer.id)
						AND printer_id = printer.id) IS NULL
					THEN (SELECT prints
						FROM tb_printer_counter
						WHERE date = (SELECT max(date)
							FROM tb_printer_counter
							WHERE printer_id = printer.id)
						AND printer_id = printer.id)
					ELSE (SELECT pc3.prints
						FROM tb_printer_counter pc3
						WHERE pc3.date = (SELECT min(date)
							FROM tb_printer_counter
							WHERE date >= ?
							AND printer_id = printer.id)
						AND printer_id = printer.id)
				END)
                          ELSE (SELECT pc2.prints
				FROM tb_printer_counter pc2
				WHERE pc2.date = max(pc1.date)
				AND pc2.printer_id = printer.id)
                        END) as printsEnd,

                (CASE WHEN min(pc1.date) IS NULL
                          THEN (SELECT max(date)
				FROM tb_printer_counter
				WHERE date <= ?
				AND printer_id = printer.id)
                          ELSE min(pc1.date)
                END) as startDate,

                        (CASE WHEN (SELECT pc2.prints
				FROM tb_printer_counter pc2
				WHERE pc2.date = min(pc1.date)
				AND pc2.printer_id = printer.id) IS NULL
                          THEN (SELECT pc5.prints
				FROM tb_printer_counter pc5
				WHERE pc5.date = (SELECT max(date)
					FROM tb_printer_counter
					WHERE date <= ?
					AND printer_id = printer.id)
				AND pc5.printer_id = printer.id)
                          ELSE (SELECT pc4.prints
				FROM tb_printer_counter pc4
				WHERE pc4.date = min(pc1.date)
				AND pc4.printer_id = printer.id)
                END) as printsStart,

                        printer.name,
                        printer.description,
                        printer.serie,
                        printer.local,
                        printer.host
                 FROM tb_printer printer
                 LEFT JOIN tb_printer_counter pc1 ON (pc1.printer_id = printer.id AND pc1.date BETWEEN ? AND ?)
                 GROUP BY printer.id,
                        pc1.blackink,
                        pc1.coloredink,
                        printer.name,
                        printer.description,
                        printer.host,
                        printer.serie,
                        printer.local
                 ORDER BY printer.id ASC
        ";

        $query = $this->getEntityManager()->createNativeQuery($sql, $rsm);

        // Start date parameters
        $query->setParameter(5, ( $start));
        $query->setParameter(6, ( $start));
        $query->setParameter(7, ( $start));

        // End date parameters
        $query->setParameter(1, ( $end ));
        $query->setParameter(2, ( $end ));
        $query->setParameter(3, ( $end ));
        $query->setParameter(4, ( $end ));
        $query->setParameter(8, ( $end ));

        return $query->execute();

    }

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

        $rsm = new ResultSetMapping();
        $rsm->addScalarResult('id', 'id');
        $rsm->addScalarResult('name', 'name');
        $rsm->addScalarResult('host', 'host');
        $rsm->addScalarResult('description', 'description');
        $rsm->addScalarResult('serie', 'serie');
        $rsm->addScalarResult('local', 'local');
        $rsm->addScalarResult('blackink', 'blackInk');
        $rsm->addScalarResult('coloredink', 'coloredInk');
        $rsm->addScalarResult('printsend', 'printsEnd');
        $rsm->addScalarResult('printsstart', 'printsStart');


        $sql = "SELECT printer.id,
                        pc1.blackink,
                        pc1.coloredink,
                        to_timestamp(CASE WHEN max(pc1.date) IS NULL
                          THEN (CASE WHEN (SELECT min(date)
						FROM tb_printer_counter
						WHERE date >= ?
						AND printer_id = printer.id) IS NULL
					THEN (SELECT max(date)
						FROM tb_printer_counter
						WHERE printer_id = printer.id)
					ELSE (SELECT min(date)
						FROM tb_printer_counter
						WHERE date >= ?
						AND printer_id = printer.id)
				END)
                          ELSE max(pc1.date)
                        END) as endDate,

                (CASE WHEN (SELECT pc2.prints
				FROM tb_printer_counter pc2
				WHERE pc2.date = max(pc1.date)
				AND pc2.printer_id = printer.id) IS NULL
                          THEN (CASE WHEN (SELECT pc3.prints
						FROM tb_printer_counter pc3
						WHERE pc3.date = (SELECT min(date)
							FROM tb_printer_counter
							WHERE date >= ?
							AND printer_id = printer.id)
						AND printer_id = printer.id) IS NULL
					THEN (SELECT prints
						FROM tb_printer_counter
						WHERE date = (SELECT max(date)
							FROM tb_printer_counter
							WHERE printer_id = printer.id)
						AND printer_id = printer.id)
					ELSE (SELECT pc3.prints
						FROM tb_printer_counter pc3
						WHERE pc3.date = (SELECT min(date)
							FROM tb_printer_counter
							WHERE date >= ?
							AND printer_id = printer.id)
						AND printer_id = printer.id)
				END)
                          ELSE (SELECT pc2.prints
				FROM tb_printer_counter pc2
				WHERE pc2.date = max(pc1.date)
				AND pc2.printer_id = printer.id)
                        END) as printsEnd,

                (CASE WHEN min(pc1.date) IS NULL
                          THEN (SELECT max(date)
				FROM tb_printer_counter
				WHERE date <= ?
				AND printer_id = printer.id)
                          ELSE min(pc1.date)
                END) as startDate,

                        (CASE WHEN (SELECT pc2.prints
				FROM tb_printer_counter pc2
				WHERE pc2.date = min(pc1.date)
				AND pc2.printer_id = printer.id) IS NULL
                          THEN (SELECT pc5.prints
				FROM tb_printer_counter pc5
				WHERE pc5.date = (SELECT max(date)
					FROM tb_printer_counter
					WHERE date <= ?
					AND printer_id = printer.id)
				AND pc5.printer_id = printer.id)
                          ELSE (SELECT pc4.prints
				FROM tb_printer_counter pc4
				WHERE pc4.date = min(pc1.date)
				AND pc4.printer_id = printer.id)
                END) as printsStart,

                        printer.name,
                        printer.description,
                        printer.serie,
                        printer.local,
                        printer.host
                 FROM tb_printer printer
                 LEFT JOIN tb_printer_counter pc1 ON (pc1.printer_id = printer.id AND pc1.date BETWEEN ? AND ?)
                 GROUP BY printer.id,
                        pc1.blackink,
                        pc1.coloredink,
                        printer.name,
                        printer.description,
                        printer.host,
                        printer.serie,
                        printer.local
                 ORDER BY printer.id ASC
        ";

        $query = $this->getEntityManager()->createNativeQuery($sql, $rsm);

        // Start date parameters
        $query->setParameter(5, ( $start));
        $query->setParameter(6, ( $start));
        $query->setParameter(7, ( $start));

        // End date parameters
        $query->setParameter(1, ( $end ));
        $query->setParameter(2, ( $end ));
        $query->setParameter(3, ( $end ));
        $query->setParameter(4, ( $end ));
        $query->setParameter(8, ( $end ));

        return $query->execute();

    }

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

        $rsm = new ResultSetMapping();
        $rsm->addScalarResult('blackink', 'blackInk');
        $rsm->addScalarResult('coloredink', 'coloredInk');
        $rsm->addScalarResult('enddate', 'endDate');
        $rsm->addScalarResult('printsend', 'printsEnd');
        $rsm->addScalarResult('startdate', 'startDate');
        $rsm->addScalarResult('printsstart', 'printsStart');
        $rsm->addScalarResult('name', 'name');
        $rsm->addScalarResult('description', 'description');
        $rsm->addScalarResult('serie', 'serie');
        $rsm->addScalarResult('local', 'local');
        $rsm->addScalarResult('host', 'host');
        $rsm->addScalarResult('id', 'id');


        $sql = "SELECT printer.id,
                        pc1.blackink,
                        pc1.coloredink,

                (CASE WHEN min(pc1.date) IS NULL
                          THEN (SELECT max(date)
				FROM tb_printer_counter
				WHERE date <= ?
				AND printer_id = printer.id)
                          ELSE min(pc1.date)
                END) as startDate,

                        (CASE WHEN (SELECT pc2.prints
				FROM tb_printer_counter pc2
				WHERE pc2.date = min(pc1.date)
				AND pc2.printer_id = printer.id) IS NULL
                          THEN (SELECT pc5.prints
				FROM tb_printer_counter pc5
				WHERE pc5.date = (SELECT max(date)
					FROM tb_printer_counter
					WHERE date <= ?
					AND printer_id = printer.id)
				AND pc5.printer_id = printer.id)
                          ELSE (SELECT pc4.prints
				FROM tb_printer_counter pc4
				WHERE pc4.date = min(pc1.date)
				AND pc4.printer_id = printer.id)
                END) as printsStart,

                (CASE WHEN max(pc1.date) IS NULL
                          THEN (CASE WHEN (SELECT min(date)
						FROM tb_printer_counter
						WHERE date >= ?
						AND printer_id = printer.id) IS NULL
					THEN (SELECT max(date)
						FROM tb_printer_counter
						WHERE printer_id = printer.id)
					ELSE (SELECT min(date)
						FROM tb_printer_counter
						WHERE date >= ?
						AND printer_id = printer.id)
				END)
                          ELSE max(pc1.date)
                        END) as endDate,

                (CASE WHEN (SELECT pc2.prints
				FROM tb_printer_counter pc2
				WHERE pc2.date = max(pc1.date)
				AND pc2.printer_id = printer.id) IS NULL
                          THEN (CASE WHEN (SELECT pc3.prints
						FROM tb_printer_counter pc3
						WHERE pc3.date = (SELECT min(date)
							FROM tb_printer_counter
							WHERE date >= ?
							AND printer_id = printer.id)
						AND printer_id = printer.id) IS NULL
					THEN (SELECT prints
						FROM tb_printer_counter
						WHERE date = (SELECT max(date)
							FROM tb_printer_counter
							WHERE printer_id = printer.id)
						AND printer_id = printer.id)
					ELSE (SELECT pc3.prints
						FROM tb_printer_counter pc3
						WHERE pc3.date = (SELECT min(date)
							FROM tb_printer_counter
							WHERE date >= ?
							AND printer_id = printer.id)
						AND printer_id = printer.id)
				END)
                          ELSE (SELECT pc2.prints
				FROM tb_printer_counter pc2
				WHERE pc2.date = max(pc1.date)
				AND pc2.printer_id = printer.id)
                        END) as printsEnd,

                        printer.name,
                        printer.description,
                        printer.serie,
                        printer.local,
                        printer.host
                 FROM tb_printer printer
                 LEFT JOIN tb_printer_counter pc1 ON (pc1.printer_id = printer.id AND pc1.date BETWEEN ? AND ?)
                 GROUP BY printer.id,
                        pc1.blackink,
                        pc1.coloredink,
                        printer.name,
                        printer.description,
                        printer.host,
                        printer.serie,
                        printer.local
                 ORDER BY printer.id ASC
        ";

        $query = $this->getEntityManager()->createNativeQuery($sql, $rsm);

        // Start date parameters
        $query->setParameter(5, ( $start));
        $query->setParameter(6, ( $start));
        $query->setParameter(7, ( $start));

        // End date parameters
        $query->setParameter(1, ( $end ));
        $query->setParameter(2, ( $end ));
        $query->setParameter(3, ( $end ));
        $query->setParameter(4, ( $end ));
        $query->setParameter(8, ( $end ));

        return $query->execute();

    }
}