<?php

namespace PortalEmpleo\Repository;

require __DIR__ . '/../../vendor/autoload.php';
require_once __DIR__ . '/../../bootstrap.php';

use Dotenv\Dotenv;
use PortalEmpleo\Entity\DboConvocatoria;
use PortalEmpleo\Entity\DboConvocatoriamerito;
use Doctrine\Common\Collections\Criteria;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\DependencyInjection\ContainerInterface;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;

class DBConvocatoriaMeritoRepository extends AbstractController
{
    private $dotenv;
    private $entityManager;
    
    public function __construct()
    {
    }

    private function loadEnv()
    {
        try{
            $dotenv = Dotenv::createImmutable(__DIR__ . "/../../");
            $dotenv->load();

            $em = getEntityManager();
            
            return $em;
        }
        catch (Exception $ex){
            echo $exception->getMessage();
            return null;
        }
    }

    public function obtenerMeritos($lang, $idConvocatoria)
    {
        try{
            $entityManager = $this->loadEnv();

            if(!isset($idConvocatoria))
            {
                $lmerits = $entityManager->getRepository(DboConvocatoriamerito::class)
                    ->createQueryBuilder('c')
                    ->where('c.baja = false')
                    ->addOrderBy('c.codigoconvocatoria', 'DESC')
                    ->addOrderBy('c.idconvocatoriamerito', 'DESC')
                    ->getQuery()
                    ->execute();
            }
            else
            {
                $lmerits = $entityManager->getRepository(DboConvocatoriamerito::class)
                    ->createQueryBuilder('c')
                    ->where('c.baja = false')
                        ->andWhere('c.codigoconvocatoria = :idconvocatoria')
                    ->setParameter('idconvocatoria', $idConvocatoria)
                    ->getQuery()
                    ->execute();
                
                if (!$lmerits) {
                    return;
                    /*throw $this->createNotFoundException(
                        'No se encuentran meritos para la convocatoria con el id'. $idConvocatoria
                    );*/
                }
            }
			
			$entityManager->getConnection()->close();

            return $lmerits;  //$result; 
        }
        catch (Exception $ex){
            echo $exception->getMessage();
            return null;
        }
    }

    /*private function obtenerListaTraducciones($em, $nombretablatraduccion, $columnaid, $columnatexto, $columna2, $language, $id)
    {
        try{
            $sql = "SELECT T." . $columnaid . " , T." . $columnatexto . " , T." . $columna2 . " , T.CodigoIdioma FROM " . $nombretablatraduccion . " T WHERE CodigoIdioma = '" . $language . "'";

            if(isset($id))
            {
                $sql = $sql . " AND T." . $columnaid . " = " . $id;
            }

            $rs = $em->getConnection()->query($sql)->fetchAll();

            return $rs; 
        }
        catch (Exception $ex){
            echo $exception->getMessage();
            return null;
        }
    }*/

    public function eliminarMerito($cvtMerito)
    {
        try
        {
            $entityManager = $this->loadEnv();
            
            $cvtmerit = $entityManager->getRepository(DboConvocatoriamerito::class)->find($cvtMerito);

            if (!$cvtmerit) {
                throw $this->createNotFoundException(
                    'No se encuentra el convocatoriamerito con el id '. $cvtMerito
                );
            }

            $cvtmerit = $cvtmerit->setBaja(true);

            $entityManager->merge($cvtmerit);
            $entityManager->flush();
            $entityManager->clear();
			$entityManager->getConnection()->close();

            return ($cvtmerit->getCodigoconvocatoria())->getIdconvocatoria();
        }
        catch (Exception $ex)
        {
            echo $exception->getMessage();
            return null;
        }
    }

    public function nuevoMerito($cvtMerito)
    {
        try
        {
            $entityManager = $this->loadEnv();
            
            $cvtmrt = new DboConvocatoriamerito();

            $cvtmrt = $cvtmrt->setMerito($cvtMerito->getMerito());
            $cvtmrt = $cvtmrt->setDescripcion($cvtMerito->getDescripcion());
            $cvtmrt = $cvtmrt->setBaja(false);
            
            $entityManager->persist($cvtmrt);
            $entityManager->flush();

            $entityManager2 = $this->loadEnv();
            $cvt = $entityManager2->getRepository(DboConvocatoria::class)->find(($cvtMerito->getCodigoconvocatoria())->getIdconvocatoria());
            $entityManager2->clear();
            if (!$cvt) {
                throw $this->createNotFoundException(
                    'No se encuentra la convocatoria con el id '.($cvtMerito->getCodigoconvocatoria())->getIdconvocatoria()
                );
            }
            $cvtmrt = $cvtmrt->setCodigoconvocatoria2($cvt);

            $entityManager->merge($cvtmrt);
            $entityManager->flush();
            $entityManager->clear();
			$entityManager->getConnection()->close();
        }
        catch (Exception $ex)
        {
            echo $exception->getMessage();
        }
    }
}

?>