<?php
    require_once 'CRM.php';
    require_once RUTA_APP.'Conexion.php';
    require_once RUTA_APP.'application/controllers/Clases/ClienteDatos.php';
    require_once RUTA_APP.'application/controllers/Clases/CampanaDatos.php';    
    class CRMDatos{

        public static function getCRMByLista($list_id):? CRM {
            $ret = null;
            $con = getConnection();
            $queryCRM = "SELECT CRM FROM dialvox_crm_lista WHERE Lista = '".$list_id."' LIMIT 1";
            if($resultCRM = sv_mysqli_query($con,$queryCRM)){
                if($rowCRM = sv_mysqli_fetch_array($resultCRM)){
                    $ret = CRMDatos::getCRMById($rowCRM["CRM"]);
                }
            }
            sv_mysqli_close($con);
            return $ret;
        }

        public static function getCRMById(int $id):?CRM{
            $ret = null;
            $con = getConnection();
            $query = "SELECT * FROM dialvox_crm WHERE id = '".limpia($id)."' LIMIT 1";
            if($result = sv_mysqli_query($con,$query)){
                if($row = sv_mysqli_fetch_array($result)){
                    $ret = new CRM($row["id"],ClienteDatos::getClienteById($row["Cliente"]),$row["Nombre"],$row["Color"], $row["OcultarInfo"], true);
                }
            }
            sv_mysqli_close($con);
            return $ret;
        }
        public static function getCRMSByCliente(Cliente $cliente, int $limite, int $pagina, string $texto):array{
            $ret = array();
            $con = getConnection();
            $query = "SELECT id FROM dialvox_crm WHERE Cliente = '".$cliente->getId()."' AND Visible = TRUE AND Nombre LIKE '%".limpia($texto)."%' LIMIT ".($limite*$pagina).",".$limite;
            if($result = sv_mysqli_query($con,$query)){
                while($row = sv_mysqli_fetch_array($result)){
                    $ret[] = self::getCRMById($row["id"]);                    
                }
            }
            sv_mysqli_close($con);
            return $ret;
        }
        public static function countCRMSByCliente(Cliente $cliente, string $texto):int{
            $ret = 0;            
            $con = getConnection();
            $query = "SELECT COUNT(id) AS CUENTA FROM dialvox_crm WHERE Cliente = '".limpia($cliente->getId())."' AND Visible = TRUE AND Nombre LIKE '%".limpia($texto)."%'";
            if($result = sv_mysqli_query($con,$query)){
                if($row = sv_mysqli_fetch_array($result)){
                    $ret = $row["CUENTA"];
                }else{
                    $ret = 0;
                }
            }else{
                $ret = mysqli_errno($con)*-1;
            }
            sv_mysqli_close($con);
            return $ret;
        }
        public static function getCRMS(int $limite, int $pagina, string $texto):array{
            $ret = array();
            $con = getConnection();
            $query = "SELECT id FROM dialvox_crm WHERE Nombre LIKE '%".limpia($texto)."%' LIMIT ".($limite*$pagina).",".$limite;
            if($result = sv_mysqli_query($con,$query)){
                while($row = sv_mysqli_fetch_array($result)){
                    $ret[] = self::getCRMById($row["id"]);                    
                }
            }
            sv_mysqli_close($con);
            return $ret;
        }
        public static function countCRMS(string $texto):int{
            $ret = 0;            
            $con = getConnection();
            $query = "SELECT COUNT(id) AS CUENTA FROM dialvox_crm WHERE Visible = TRUE AND Nombre LIKE '%".limpia($texto)."%'";
            if($result = sv_mysqli_query($con,$query)){
                if($row = sv_mysqli_fetch_array($result)){
                    $ret = $row["CUENTA"];
                }else{
                    $ret = 0;
                }
            }else{
                $ret = mysqli_errno($con)*-1;
            }
            sv_mysqli_close($con);
            return $ret;
        }
        public static function listCRMs():array{
            $ret = array();
            $con = getConnection();
            $query = "SELECT id FROM dialvox_crm WHERE Visible=TRUE";
            if($result = sv_mysqli_query($con,$query)){
                while($row = sv_mysqli_fetch_array($result)){
                    $ret[] = self::getCRMById($row["id"]);                    
                }
            }
            sv_mysqli_close($con);
            return $ret;
        }
        public static function listCRMsByCliente(Cliente $cliente):array{
            $ret = array();
            $con = getConnection();
            $query = "SELECT id FROM dialvox_crm WHERE Cliente  = '".limpia($cliente->getId())."' AND Visible=TRUE";
            if($result = sv_mysqli_query($con,$query)){
                while($row = sv_mysqli_fetch_array($result)){
                    $ret[] = self::getCRMById($row["id"]);                    
                }
            }
            sv_mysqli_close($con);
            return $ret;
        }
        public static function addCRM(CRM $crm):int{
            $ret = 0;
            $con = getConnection();
            $query = "INSERT INTO dialvox_crm(Cliente, Nombre, Color,Visible) VALUES ('".limpia($crm->getCliente()->getId())."','".limpia($crm->getNombre())."','".limpia($crm->getColor())."','".limpia($crm->isVisible())."')";
            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 updateCRM(CRM $crm):int{
            $ret = 0;
            $con = getConnection();
            $query = "UPDATE dialvox_crm SET Nombre = '".limpia($crm->getNombre())."',Color = '".limpia($crm->getColor())."',OcultarInfo = ".($crm->getOcultarInfo() ? "true" : "false")." WHERE id = '".limpia($crm->getId())."' LIMIT 1";
            if(sv_mysqli_query($con,$query)){
                $ret = mysqli_affected_rows($con);            
            }else{
                $ret = mysqli_errno($con)*-1;
            }
            echo mysqli_error($con);
            sv_mysqli_close($con);
            return $ret;
        }
        public static function deleteCRM(CRM $crm):int{
            $ret = 0;
            $con = getConnection();
            $query = "UPDATE dialvox_crm SET Visible = FALSE WHERE id = '".limpia($crm->getId())."' LIMIT 1";
            if(sv_mysqli_query($con,$query)){
                $ret = 1;
            }else{
                $ret = mysqli_errno($con);
            }
            sv_mysqli_close($con);
            return $ret;
        }
    }
