<?php

namespace PortalEmpleo\Repository;

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

use Dotenv\Dotenv;
use PortalEmpleo\Entity\DboCombobox;
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 DBUtilRepository 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 cargaComboBox($nombreCombo, $lang)
    {
        try{
            $entityManager = $this->loadEnv();

            $lcombobox = $entityManager->getRepository(DboCombobox::class)
                ->createQueryBuilder('u')
                ->where('u.nombrecombo = :nombre')
                    ->andWhere('u.baja = false')
                ->setParameter('nombre', $nombreCombo)
                ->getQuery()
                ->execute();
             
            if (!$lcombobox) {
                return ;
            }

            $lcombobox = reset($lcombobox);

            $lvalores = $this->obtenerListaValoresCombo($entityManager, $lcombobox->getNombretabla(), $lcombobox->getColumnavalue(), $lcombobox->getColumnaoption(), $lcombobox->getNombretablatraduccion(), $lcombobox->getColumnavaluetraduccion(), $lang);
            
            $result = array("NombreCombo" => $lcombobox->getNombreCombo(),"Valores" => $lvalores, "ValorSelected" => $lcombobox->getValorselected());
            
			$entityManager->getConnection()->close();
			
            return $result; 
        }
        catch (Exception $ex){
            echo $exception->getMessage();
            return null;
        }
    }

    private function obtenerListaValoresCombo($em, $nombretabla, $columnavalue, $columnaoption, $nombretablatraduccion, $columnavaluetraduccion, $language)
    {
        try{

            if($nombretablatraduccion && $columnavaluetraduccion && $language){
                $sql = "SELECT T." . $columnavalue . " , TT." . $columnaoption . " , TT.CodigoIdioma , T." . $columnaoption . " AS Original FROM " . $nombretabla . " T INNER JOIN " . $nombretablatraduccion . " TT ON T." . $columnavalue . " = TT." . $columnavaluetraduccion . " WHERE CodigoIdioma = '" . $language . "'";
            }
            else {
                $sql = "SELECT " . $columnavalue . " , " . $columnaoption . " FROM " . $nombretabla;
            }

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

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

?>