<?php
require_once 'CRM.php';
require_once 'Campo.php';
require_once 'CRMDatos.php';
require_once 'CategoriaDatos.php';
require_once RUTA_APP . 'Conexion.php';
class CampoDatos
{
    public static function getCampoById(int $id): ?Campo
    {
        $ret = null;
        $con = getConnection();
        $query = "SELECT id, Categoria, Tipo, Edicion, Nombre, Valor, Busqueda,Requerido FROM dialvox_crm_campo WHERE id = '" . limpia($id) . "' LIMIT 1";
        if ($result = sv_mysqli_query($con, $query)) {
            if ($row = sv_mysqli_fetch_array($result)) {
                $ret = new Campo($row["id"], CategoriaDatos::getCategoriaById($row["Categoria"]), $row["Tipo"], $row["Edicion"], $row["Nombre"], $row["Valor"], $row["Busqueda"], true, $row["Requerido"]);
            }
        }
        sv_mysqli_close($con);
        return $ret;
    }
    public static function getCamposByCRM(CRM $crm): array
    {
        $ret = array();
        $con = getConnection();
        $query = "SELECT id FROM dialvox_crm_campo WHERE Categoria IN (SELECT id FROM dialvox_crm_categoria WHERE CRM= '" . limpia($crm->getId()) . "') AND Visible = TRUE ORDER BY Tipo";
        if ($result = sv_mysqli_query($con, $query)) {
            while ($row = sv_mysqli_fetch_array($result)) {
                $ret[] = self::getCampoById($row["id"]);
            }
        }
        sv_mysqli_close($con);
        return $ret;
    }
    public static function countCamposByCRM(CRM $crm): int
    {
        $ret = 0;
        $con = getConnection();
        $query = "SELECT COUNT(id) AS CUENTA FROM dialvox_crm_campo WHERE Categoria IN (SELECT id FROM dialvox_crm_categoria WHERE CRM= '" . limpia($crm->getId()) . "') AND Visible = TRUE ORDER BY Tipo";
        if ($result = sv_mysqli_query($con, $query)) {
            if ($row = sv_mysqli_fetch_array($result)) {
                $ret = $row["CUENTA"];
            }
        } else {
            $ret = mysqli_errno($con);
        }
        sv_mysqli_close($con);
        return $ret;
    }
    public static function addCampo(Campo $campo): int
    {
        $ret = 0;
        $con = getConnection();
        $query = "INSERT INTO dialvox_crm_campo (Categoria,Tipo,Edicion,Nombre,Valor,Busqueda,Requerido) VALUES ('" . limpia($campo->getCategoria()->getId()) . "','" . limpia($campo->getTipo()) . "','" . limpia($campo->getEdicion()) . "','" . limpia($campo->getNombre()) . "','" . limpia($campo->getValor()) . "','" . limpia($campo->getBusqueda()) . "'," . limpia($campo->getRequerido() ? 1 : 0) . ")";
        if (sv_mysqli_query($con, $query)) {
            $ret = mysqli_insert_id($con);
        } else {
            $ret = mysqli_errno($con) * -1;
        }
        sv_mysqli_close($con);
        return $ret;
    }
    public static function updateCampo(Campo $campo): int
    {
        $ret = 0;
        $con = getConnection();
        $query = "UPDATE dialvox_crm_campo SET Categoria = '" . limpia($campo->getCategoria()->getId()) . "',Tipo = '" . limpia($campo->getTipo()) . "',Edicion = '" . limpia($campo->getEdicion()) . "',Nombre = '" . limpia($campo->getNombre()) . "',Valor = '" . limpia($campo->getValor()) . "',Busqueda = '" . limpia($campo->getBusqueda()) . "',Requerido='" . limpia($campo->getRequerido() == true ? 1 : 0) . "' WHERE id = '" . limpia($campo->getId()) . "'";
        if (sv_mysqli_query($con, $query)) {
            $ret = mysqli_affected_rows($con);
        } else {
            $ret = mysqli_errno($con) * -1;
        }
        sv_mysqli_close($con);
        return $ret;
    }
    public static function deleteCampo(Campo $campo): int
    {
        $ret = 0;
        $con = getConnection();
        $query = "UPDATE dialvox_crm_campo SET Visible = FALSE WHERE id = '" . limpia($campo->getId()) . "'";
        if (sv_mysqli_query($con, $query)) {
            $ret = 1;
        } else {
            $ret = mysqli_errno($con) * -1;
        }
        sv_mysqli_close($con);
        return $ret;
    }
    public function listCamposByBusquedaAndCRM(CRM $crm): array
    {
        $resp = array();
        $con = getConnection();
        $query = "SELECT id FROM dialvox_crm_campo WHERE  Categoria IN (SELECT id FROM dialvox_crm_categoria WHERE CRM= '" . limpia($crm->getId()) . "') AND Busqueda = TRUE AND Visible = TRUE ORDER BY Tipo";
        if ($result = sv_mysqli_query($con, $query)) {
            while ($row = sv_mysqli_fetch_array($result)) {
                $resp[] = self::getCampoById($row["id"]);
            }
        }
        sv_mysqli_close($con);
        return $resp;
    }
}