<?php
class VendedoresModel extends Query{

    public function __construct()
    {
        parent::__construct();
    }

    public function getNombreCliente(int $idcliente)
    {
        $sql = "SELECT codven as codigo,nombre, coord_ultimavisita FROM vendedores WHERE codven = $idcliente";
        $data = $this->select($sql);
        return $data;
    }

    public function getClientes(int $idusuario)
    {
        $sql = "SELECT tipousuario,codigo_vendedor FROM usuarios WHERE id = $idusuario";
        $tipo = $this->select($sql);
        $codigo_vendedor=$tipo['codigo_vendedor'];


        if($tipo['tipousuario'] == 3) {
            $sql = "SELECT *,CONCAT(codven,' - ',nombre) as codigonombre,DATEDIFF(now(),ultimaventa) as diasdiferencia,DATEDIFF(now(),ultimopago) as diasdiferenciapago,DATEDIFF(now(),programacion) as diasdiferenciaprogramacion,DATEDIFF(now(),ultimavisita) as diasdiferenciavisita FROM vendedores WHERE id_vendedor1 = $codigo_vendedor";            
        } else {
            $sql = "SELECT *,CONCAT(codven,' - ',nombre) as codigonombre,DATEDIFF(now(),ultimaventa) as diasdiferencia,DATEDIFF(now(),ultimopago) as diasdiferenciapago,DATEDIFF(now(),programacion) as diasdiferenciaprogramacion,DATEDIFF(now(),ultimavisita) as diasdiferenciavisita FROM vendedores";
        }
        
        $data = $this->selectAll($sql);
        return $data;
    }

    public function guardarVisita($codcliente,$coords,$id_vendedor1)
    {
        $sql = "UPDATE clientes SET ultimavisita=NOW(),coord_ultimavisita=? WHERE codigo=?";
        $datos = array($coords, $codcliente);
        $data = $this->save($sql, $datos);
        
        $sql = "INSERT INTO `visitas` (`coordenadas`,`codigo`,`id_vendedor`,`fecha`) VALUES (?,?,?,NOW())";
        $datos = array($coords, $codcliente, $id_vendedor1);
        $this->save($sql, $datos);

        $sql = "UPDATE vendedores SET ultimavisita=NOW(),coord_ultimavisita=? WHERE codven=?";
        $datos = array($coords, $id_vendedor1);
        $this->save($sql, $datos);

        return $data;

    }

    public function getCategorias()
    {
        $sql = "SELECT TRIM(`categorias`.`cod_linea`) as `cod_linea`,`categorias`.`categoria` FROM `categorias` ORDER BY `categoria`";
        $data = $this->selectAll($sql);
        return $data;
    }

    public function comparacionClienteCategorias(int $anio, int $idcliente)
    {
        $sql = "SELECT `productos`.`idcategoria`, TRIM(`categorias`.`cod_linea`) as `cod_linea`,`categorias`.`categoria`, SUM(`facturadet`.`cantidad`) as cantidad, SUM(`facturadet`.`total`) as ventas FROM `".prefixant."`.`facturadet`
                INNER JOIN `".prefixant."`.`facturaenc` ON `facturaenc`.`numdoc`=`facturadet`.`numdoc`
                INNER JOIN `".prefixant."`.`productos` ON `productos`.`prod_cod`=`facturadet`.`codart`
                INNER JOIN `".db."`.`categorias` ON `categorias`.`id`=`productos`.`idcategoria`
                WHERE trim(`facturaenc`.`codven`)={$idcliente} AND YEAR(`facturaenc`.`fechadoc`)={$anio}
                GROUP BY `productos`.`idcategoria`";

        $data = $this->selectAll($sql);
        return $data;
    }

    public function comparacionClienteProductos(int $anio, int $idcliente)
    {
        $sql = "SELECT `facturadet`.`codart`,`productos`.`idcategoria`, `categorias`.`cod_linea`, `productos`.`prod_descrip`, SUM(`facturadet`.`cantidad`) as cantidad, SUM(`facturadet`.`total`) as ventas
                FROM `".prefixant."`.`facturadet`
                INNER JOIN `".prefixant."`.`facturaenc` ON `facturaenc`.`numdoc`=`facturadet`.`numdoc`
                INNER JOIN `".prefixant."`.`productos` ON `productos`.`prod_cod`=`facturadet`.`codart`
                INNER JOIN `".db."`.`categorias` ON `categorias`.`id`=`productos`.`idcategoria`
                WHERE trim(`facturaenc`.`codven`)={$idcliente}   AND YEAR(`facturaenc`.`fechadoc`)={$anio}
                GROUP BY `facturadet`.`codart`";

        $data = $this->selectAll($sql);
        return $data;
    }

    public function comparacionVentasMes(string $desde, string $hasta, int $idcliente)
    {
        $sql = "SELECT
            COALESCE(SUM(IF(MONTH(`fechadoc`) = 1,  total, 0)),0) AS ene,
            COALESCE(SUM(IF(MONTH(`fechadoc`) = 2,  total, 0)),0) AS feb,
            COALESCE(SUM(IF(MONTH(`fechadoc`) = 3,  total, 0)),0) AS mar,
            COALESCE(SUM(IF(MONTH(`fechadoc`) = 4,  total, 0)),0) AS abr,
            COALESCE(SUM(IF(MONTH(`fechadoc`) = 5,  total, 0)),0) AS may,
            COALESCE(SUM(IF(MONTH(`fechadoc`) = 6,  total, 0)),0) AS jun,
            COALESCE(SUM(IF(MONTH(`fechadoc`) = 7,  total, 0)),0) AS jul,
            COALESCE(SUM(IF(MONTH(`fechadoc`) = 8,  total, 0)),0) AS ago,
            COALESCE(SUM(IF(MONTH(`fechadoc`) = 9,  total, 0)),0) AS sep,
            COALESCE(SUM(IF(MONTH(`fechadoc`) = 10, total, 0)),0) AS oct,
            COALESCE(SUM(IF(MONTH(`fechadoc`) = 11, total, 0)),0) AS nov,
            COALESCE(SUM(IF(MONTH(`fechadoc`) = 12, total, 0)),0) AS dic
        FROM `".prefixant."`.`facturaenc`
        WHERE `fechadoc` BETWEEN '$desde' AND '$hasta' AND codven='$idcliente'";        

        $data = $this->select($sql);
        return $data;
    }

    public function comparacionPagosMes(string $desde, string $hasta, int $idcliente)
    {
        $sql = "SELECT
            COALESCE(SUM(IF(MONTH(rec_fecha) = 1,  rec_abono, 0)),0) AS ene,
            COALESCE(SUM(IF(MONTH(rec_fecha) = 2,  rec_abono, 0)),0) AS feb,
            COALESCE(SUM(IF(MONTH(rec_fecha) = 3,  rec_abono, 0)),0) AS mar,
            COALESCE(SUM(IF(MONTH(rec_fecha) = 4,  rec_abono, 0)),0) AS abr,
            COALESCE(SUM(IF(MONTH(rec_fecha) = 5,  rec_abono, 0)),0) AS may,
            COALESCE(SUM(IF(MONTH(rec_fecha) = 6,  rec_abono, 0)),0) AS jun,
            COALESCE(SUM(IF(MONTH(rec_fecha) = 7,  rec_abono, 0)),0) AS jul,
            COALESCE(SUM(IF(MONTH(rec_fecha) = 8,  rec_abono, 0)),0) AS ago,
            COALESCE(SUM(IF(MONTH(rec_fecha) = 9,  rec_abono, 0)),0) AS sep,
            COALESCE(SUM(IF(MONTH(rec_fecha) = 10, rec_abono, 0)),0) AS oct,
            COALESCE(SUM(IF(MONTH(rec_fecha) = 11, rec_abono, 0)),0) AS nov,
            COALESCE(SUM(IF(MONTH(rec_fecha) = 12, rec_abono, 0)),0) AS dic
        FROM `".prefixant."`.`recibos`
        WHERE rec_fecha BETWEEN '$desde' AND '$hasta' AND rec_codven='$idcliente'";
/*
        $sql = "SELECT
            COALESCE(SUM(IF(MONTH(fecha_abono) = 1,  monto, 0)),0) AS ene,
            COALESCE(SUM(IF(MONTH(fecha_abono) = 2,  monto, 0)),0) AS feb,
            COALESCE(SUM(IF(MONTH(fecha_abono) = 3,  monto, 0)),0) AS mar,
            COALESCE(SUM(IF(MONTH(fecha_abono) = 4,  monto, 0)),0) AS abr,
            COALESCE(SUM(IF(MONTH(fecha_abono) = 5,  monto, 0)),0) AS may,
            COALESCE(SUM(IF(MONTH(fecha_abono) = 6,  monto, 0)),0) AS jun,
            COALESCE(SUM(IF(MONTH(fecha_abono) = 7,  monto, 0)),0) AS jul,
            COALESCE(SUM(IF(MONTH(fecha_abono) = 8,  monto, 0)),0) AS ago,
            COALESCE(SUM(IF(MONTH(fecha_abono) = 9,  monto, 0)),0) AS sep,
            COALESCE(SUM(IF(MONTH(fecha_abono) = 10, monto, 0)),0) AS oct,
            COALESCE(SUM(IF(MONTH(fecha_abono) = 11, monto, 0)),0) AS nov,
            COALESCE(SUM(IF(MONTH(fecha_abono) = 12, monto, 0)),0) AS dic
        FROM abonos
        WHERE fecha_abono BETWEEN '$desde' AND '$hasta' AND id_cliente='$idcliente'";*/        

        $data = $this->select($sql);
        return $data;
    }

    public function getEmpresa()
    {
        $sql = "SELECT * FROM empresa";
        $data = $this->select($sql);
        return $data;
    }

    public function getVendedoresClientes($idusuario)
    {
        $sql = "SELECT tipousuario,codigo_vendedor FROM usuarios WHERE id = $idusuario";
        $tipo = $this->select($sql);
        $codigo_vendedor=$tipo['codigo_vendedor'];

        if($tipo['tipousuario'] == 3) {
            $sql = "SELECT codven as codigo, vendedores.nombre as vendedor FROM `vendedores` WHERE codven = $codigo_vendedor";
            //$sql = "SELECT codigo_vendedor as codigo,usuarios.nombre as nombre, vendedores.nombre as vendedor FROM `usuarios` INNER JOIN `vendedores` ON usuarios.codigo_vendedor=vendedores.codven WHERE codigo_vendedor IN (SELECT `id_vendedor1` FROM `clientes` group by `id_vendedor1`) AND codigo_vendedor = $codigo_vendedor";
        } else {
            //$sql = "SELECT codigo_vendedor as codigo,usuarios.nombre as nombre, vendedores.nombre as vendedor FROM `usuarios` INNER JOIN `vendedores` ON usuarios.codigo_vendedor=vendedores.codven WHERE codigo_vendedor IN (SELECT `id_vendedor1` FROM `clientes` group by `id_vendedor1`)";
            $sql = "SELECT codven as codigo, vendedores.nombre as vendedor FROM `vendedores`";
            //$sql = "SELECT codigo_vendedor as codigo,usuarios.nombre as nombre, vendedores.nombre as vendedor FROM `usuarios` INNER JOIN `vendedores` ON usuarios.codigo_vendedor=vendedores.codven WHERE codigo_vendedor IN (SELECT `id_vendedor1` FROM `clientes` group by `id_vendedor1`)";
        }

        $data = $this->selectAll($sql);
        return $data;
    }
    
    public function getVisitasMes($anio, $cliente)
    {
        $sql = "SELECT * FROM visitas WHERE YEAR(fecha) = $anio AND id_vendedor = $cliente ORDER BY fecha";
        $data = $this->selectAll($sql);
        return $data;

    }
    public function getVisitasMesGroup($anio, $cliente)
    {
        $sql = "SELECT MONTH(fecha) as mes, count(*) as contador FROM visitas WHERE YEAR(fecha) = $anio AND id_vendedor = $cliente GROUP BY MONTH(fecha)";
        $data = $this->selectAll($sql);
        return $data;
    }

    public function getVendedores()
    {
        $sql = "SELECT codven, nombre FROM vendedores";
        $data = $this->selectAll($sql);
        return $data;
    }

    public function getReporteCuentasCobrar($id)
    {
        if((int)$id!==0) {
            /*$sql = "SELECT
                    IFNULL((SELECT nombre FROM `clientes` WHERE clientes.id=creditos.cliente), 'MOSTRADOR') AS CLIENTE,
                    IF(cliente=0, 1, cliente) AS IDCLIENTE,            
                    venta as FACTURA,
                    fecha as FECHA,
                    monto as VALOR,
                    abonado as ABONO,
                    (monto - abonado) as SALDO,
                    DATEDIFF(NOW(), fecha) AS DIAS_DE_ATRASO
                    FROM `creditos`
                    WHERE cliente='".addslashes($id)."'
                    AND (monto - abonado) > 0
                    ORDER BY FACTURA ASC";*/
                $sql = "SELECT
                    IFNULL((SELECT clientes.nombre FROM `".db."`.`clientes` WHERE clientes.id=creditos.codcliente), 'MOSTRADOR') AS CLIENTE,
                    IF(creditos.codcliente=0, 1, creditos.codcliente) AS IDCLIENTE,
                    `creditos`.`numdoc` as FACTURA,
                    DATE(`creditos`.`fechadoc`) as FECHA,
                    `creditos`.`total` as VALOR,
                    `creditos`.`total`-`creditos`.`saldo` as ABONO,
                    `creditos`.`saldo` as SALDO,
                    DATEDIFF(NOW(), DATE(`creditos`.`fechadoc`)) AS DIAS_DE_ATRASO
                    FROM `".prefixant."`.`facturaenc` creditos
                    WHERE creditos.codven='".addslashes($id)."'
                    AND `creditos`.`saldo` > 0
                    ORDER BY FACTURA ASC";

        } else {
            /*$sql = "SELECT 
                    IFNULL((SELECT nombre FROM `clientes` WHERE clientes.id=creditos.cliente), 'MOSTRADOR') AS CLIENTE,
                    IF(cliente=0, 1, cliente) AS IDCLIENTE,            
                    venta as FACTURA,
                    fecha as FECHA,
                    monto as VALOR,
                    abonado as ABONO,
                    (monto - abonado) as SALDO,
                    DATEDIFF(NOW(), fecha) AS DIAS_DE_ATRASO
                    FROM `creditos`
                    WHERE (monto - abonado) > 0
                    ORDER BY IDCLIENTE,FACTURA ASC";*/
                    $sql = "SELECT
                    IFNULL((SELECT clientes.nombre FROM `".db."`.`clientes` WHERE clientes.id=creditos.codcliente), 'MOSTRADOR') AS CLIENTE,
                    IF(creditos.codcliente=0, 1, creditos.codcliente) AS IDCLIENTE,
                    `creditos`.`numdoc` as FACTURA,
                    DATE(`creditos`.`fechadoc`) as FECHA,
                    `creditos`.`total` as VALOR,
                    `creditos`.`total`-`creditos`.`saldo` as ABONO,
                    `creditos`.`saldo` as SALDO,
                    DATEDIFF(NOW(), DATE(`creditos`.`fechadoc`)) AS DIAS_DE_ATRASO
                    FROM `".prefixant."`.`facturaenc` creditos
                    WHERE `creditos`.`saldo` > 0
                    ORDER BY IDCLIENTE,FACTURA ASC";
        }
        $data = $this->selectAll($sql);
        return $data;
    }

    public function getReporteAntiguedadSaldos($id)
    {
        if((int)$id!==0) {
            /*$sql = "SELECT 
                    IFNULL((SELECT nombre FROM `clientes` WHERE clientes.id=creditos.cliente), 'MOSTRADOR') AS CLIENTE,
                    IF(cliente=0, 1, cliente) AS IDCLIENTE,             
                    venta as FACTURA,
                    fecha as FECHA,
                    DATE_ADD(fecha, INTERVAL 30 DAY) as VENCE,
                    monto as VALOR,
                    abonado as ABONO,
                    (monto - abonado) as SALDO,
                    DATEDIFF(NOW(), fecha) AS DIAS_DE_ATRASO,
                    IF(DATEDIFF(NOW(), fecha)<=30, (monto - abonado), 0) as '30',
                    IF(DATEDIFF(NOW(), fecha)>30 && DATEDIFF(NOW(), fecha)<=60, (monto - abonado), 0) as '60',
                    IF(DATEDIFF(NOW(), fecha)>60, (monto - abonado), 0) as '90'
                    FROM `creditos`
                    WHERE cliente='".addslashes($id)."'
                    AND (monto - abonado) > 0
                    ORDER BY FACTURA ASC";*/
                    

                    $sql = "SELECT 
                    IFNULL((SELECT clientes.nombre FROM `".db."`.`clientes` WHERE clientes.id=creditos.codcliente), 'MOSTRADOR') AS CLIENTE,
                    IF(creditos.codcliente=0, 1, creditos.codcliente) AS IDCLIENTE,
                    `creditos`.`numdoc` as FACTURA,
                    DATE(`creditos`.`fechadoc`) as FECHA,
                    DATE_ADD(DATE(`creditos`.`fechadoc`), INTERVAL 30 DAY) as VENCE,
                    `creditos`.`total` as VALOR,
                    `creditos`.`saldo` as SALDO,
                    DATEDIFF(NOW(), DATE(`creditos`.`fechadoc`)) AS DIAS_DE_ATRASO,
                    IF(DATEDIFF(NOW(), DATE(`creditos`.`fechadoc`))<=30, (`creditos`.`saldo`), 0) as '30',
                    IF(DATEDIFF(NOW(), DATE(`creditos`.`fechadoc`))>30 && DATEDIFF(NOW(), DATE(`creditos`.`fechadoc`))<=60, (`creditos`.`saldo`), 0) as '60',
                    IF(DATEDIFF(NOW(), DATE(`creditos`.`fechadoc`))>60, (`creditos`.`saldo`), 0) as '90'
                    FROM `".prefixant."`.`facturaenc` creditos
                    WHERE creditos.codven='".addslashes($id)."'
                    AND `creditos`.`saldo` > 0
                    ORDER BY FACTURA ASC";

        } else {
            /*$sql = "SELECT 
                    IFNULL((SELECT nombre FROM `clientes` WHERE clientes.id=creditos.cliente), 'MOSTRADOR') AS CLIENTE,
                    IF(cliente=0, 1, cliente) AS IDCLIENTE,             
                    venta as FACTURA,
                    fecha as FECHA,
                    DATE_ADD(fecha, INTERVAL 30 DAY) as VENCE,
                    monto as VALOR,
                    abonado as ABONO,
                    (monto - abonado) as SALDO,
                    DATEDIFF(NOW(), fecha) AS DIAS_DE_ATRASO,
                    IF(DATEDIFF(NOW(), fecha)<=30, (monto - abonado), 0) as '30',
                    IF(DATEDIFF(NOW(), fecha)>30 && DATEDIFF(NOW(), fecha)<=60, (monto - abonado), 0) as '60',
                    IF(DATEDIFF(NOW(), fecha)>60, (monto - abonado), 0) as '90'
                    FROM `creditos`
                    WHERE (monto - abonado) > 0
                    ORDER BY IDCLIENTE,FACTURA ASC";*/

                $sql = "SELECT 
                IFNULL((SELECT clientes.nombre FROM `".db."`.`clientes` WHERE clientes.id=creditos.codcliente), 'MOSTRADOR') AS CLIENTE,
                IF(creditos.codcliente=0, 1, creditos.codcliente) AS IDCLIENTE,
                `creditos`.`numdoc` as FACTURA,
                DATE(`creditos`.`fechadoc`) as FECHA,
                DATE_ADD(DATE(`creditos`.`fechadoc`), INTERVAL 30 DAY) as VENCE,
                `creditos`.`total` as VALOR,
                `creditos`.`saldo` as SALDO,
                DATEDIFF(NOW(), DATE(`creditos`.`fechadoc`)) AS DIAS_DE_ATRASO,
                IF(DATEDIFF(NOW(), DATE(`creditos`.`fechadoc`))<=30, (`creditos`.`saldo`), 0) as '30',
                IF(DATEDIFF(NOW(), DATE(`creditos`.`fechadoc`))>30 && DATEDIFF(NOW(), DATE(`creditos`.`fechadoc`))<=60, (`creditos`.`saldo`), 0) as '60',
                IF(DATEDIFF(NOW(), DATE(`creditos`.`fechadoc`))>60, (`creditos`.`saldo`), 0) as '90'
                FROM `".prefixant."`.`facturaenc` creditos
                WHERE `creditos`.`saldo` > 0
                ORDER BY IDCLIENTE,FACTURA ASC";
        }
        $data = $this->selectAll($sql);
        return $data;
    }

    public function getReporteEstadoCuenta($id)
    {
        
        $sql = "(SELECT 
                venta as DOCUMENTO
                ,venta as ORDERDOCUMENTO
                ,fecha as FECHA
                ,monto as CARGO
                ,0 as ABONO
                ,'' as REFERENCIA
                FROM `creditos` WHERE cliente='".addslashes($id)."')
                UNION 
                (SELECT
                (SELECT concat(creditos.venta,' - R.',abonos.id) FROM `creditos` WHERE creditos.id=abonos.id_credito LIMIT 1) as DOCUMENTO
                ,(SELECT venta FROM `creditos` WHERE creditos.id=abonos.id_credito LIMIT 1) as ORDERDOCUMENTO
                ,fecha_abono as FECHA
                ,0 as CARGO
                ,monto * -1 as ABONO
                , CONCAT(metodo_pago,' | ',observaciones,' | ',referencias) as REFERENCIA
                FROM `abonos` WHERE id_cliente='".addslashes($id)."')
                ORDER BY ORDERDOCUMENTO, FECHA";
        /*$sql = "(SELECT 
                venta as DOCUMENTO
                ,fecha as FECHA
                ,monto as CARGO
                ,0 as ABONO
                ,'' as REFERENCIA
                FROM `creditos` WHERE cliente='".addslashes($id)."')
                UNION 
                (SELECT
                (SELECT venta FROM `creditos` WHERE creditos.id=abonos.id_credito LIMIT 1) as DOCUMENTO
                ,fecha_abono as FECHA
                ,0 as CARGO
                ,monto * -1 as ABONO
                , CONCAT(metodo_pago,' | ',observaciones,' | ',referencias) as REFERENCIA
                FROM `abonos` WHERE id_cliente='".addslashes($id)."')
                ORDER BY DOCUMENTO, FECHA";  */              
        $data = $this->selectAll($sql);
        return $data;
    }    


    public function getPaises()
    {
        $sql = "SELECT cod, pais FROM paises";
        $data = $this->selectAll($sql);
        return $data;
    }
    
    public function registrarCliente(string $nombre, int $tipoid,string $nit, string $telefono, string $mensajeria, string $direccion, string $municipio, string $departamento, string $pais, string $coordenadas, float $limite_credito, int $dias_credito, int $id_vendedor1, int $tipocliente, string $contacto, string $telcontacto, string $observaciones)
    {
        $verficar = "SELECT * FROM clientes WHERE nombre = '$nombre'";
        $existe = $this->select($verficar);
        if (empty($existe)) {
            $sql = "INSERT INTO clientes(nombre, tipoid, nit, telefono, mensajeria, direccion, municipio, departamento, pais, coordenadas, limite_credito, dias_credito, id_vendedor1, tipocliente ,contacto, telcontacto, observaciones) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            $datos = array($nombre, $tipoid, $nit, $telefono, $mensajeria, $direccion, $municipio, $departamento, $pais, $coordenadas, $limite_credito, $dias_credito, $id_vendedor1, $tipocliente, $contacto, $telcontacto, $observaciones);
            $data = $this->save($sql, $datos);
            if ($data == 1) {
                $res = "ok";
            }else{
                $res = "error";
            }
        }else{
            $res = "existe";
        }
        return $res;
    }


    public function modificarCliente(string $nombre, int $tipoid, string $nit, string $telefono, string $mensajeria, string $direccion, string $municipio, string $departamento, string $pais, string $coordenadas, float $limite_credito, int $dias_credito, int $id_vendedor1, int $tipocliente, string $contacto, string $telcontacto, string $observaciones, int $id)
    {
        $sql = "UPDATE clientes SET nombre = ?, tipoid = ?, nit = ? ,telefono = ? ,mensajeria = ? ,direccion = ? ,municipio = ? ,departamento = ? ,pais = ? ,coordenadas = ? ,limite_credito = ? ,dias_credito = ?, id_vendedor1 = ?, tipocliente = ?, contacto = ?, telcontacto = ?, observaciones = ? WHERE id = ?";
        $datos = array($nombre, $tipoid, $nit, $telefono ,$mensajeria ,$direccion, $municipio, $departamento, $pais, $coordenadas, $limite_credito, $dias_credito, $id_vendedor1, $tipocliente, $contacto, $telcontacto, $observaciones, $id);
        $data = $this->save($sql, $datos);
        if ($data == 1) {
            $res = "modificado";
        } else {
            $res = "error";
        }
        return $res;
    }

    public function modificarAnotaciones(string $observaciones, int $id)
    {
        $sql = "UPDATE clientes SET observaciones = ? WHERE id = ?";
        $datos = array($observaciones, $id);
        $data = $this->save($sql, $datos);
        if ($data == 1) {
            $res = "modificado";
        } else {
            $res = "error";
        }
        return $res;
    }

    public function modificarProgramacion(string $fecha, int $id,$codcliente,$nomcliente,$id_vendedor1)
    {
        $sql = "UPDATE clientes SET programacion = ? WHERE id = ?";
        $datos = array($fecha, $id);
        $data = $this->save($sql, $datos);
        if ($data == 1) {
            $res = "modificado";
        } else {
            $res = "error";
        }

        $sql = "INSERT INTO `programaciones` (`codigo`,`id_vendedor`,`fecha`,`fecha_asignacion`) VALUES (?,?,?,now())";
        $datos = array($id, $id_vendedor1, $fecha);
        $this->save($sql, $datos);

        $tituloevento='['.$codcliente.']-'.$nomcliente;
        $sql = "INSERT INTO `eventos` (`title`,`descripcion`,`color`,`textColor`,`destinatario`,`start`,`end`) VALUES (?,'Programacion de Visita a Cliente','green','white',?,?,?)";
        $datos = array($tituloevento, $id_vendedor1, $fecha, $fecha);
        $this->save($sql, $datos);


        return $res;

    }

    public function modificarCobro(string $fecha, int $id,$codcliente,$nomcliente,$id_vendedor1)
    {
      /*  $sql = "UPDATE clientes SET programacion = ? WHERE id = ?";
        $datos = array($fecha, $id);
        $data = $this->save($sql, $datos);
        if ($data == 1) {
            $res = "modificado";
        } else {
            $res = "error";
        }

        $sql = "INSERT INTO `programaciones` (`codigo`,`id_vendedor`,`fecha`,`fecha_asignacion`) VALUES (?,?,?,now())";
        $datos = array($id, $id_vendedor1, $fecha);
        $this->save($sql, $datos); */

        $tituloevento='['.$codcliente.']-'.$nomcliente;
        $sql = "INSERT INTO `eventos` (`title`,`descripcion`,`color`,`textColor`,`destinatario`,`start`,`end`) VALUES (?,'Programacion de Cobro a Cliente','orange','white',?,?,?)";
        $datos = array($tituloevento, $id_vendedor1, $fecha, $fecha);
        $this->save($sql, $datos);


        return $res;

    }

    public function modificarLlamada(string $fecha, int $id,$codcliente,$nomcliente,$id_vendedor1)
    {
        /*$sql = "UPDATE clientes SET programacion = ? WHERE id = ?";
        $datos = array($fecha, $id);
        $data = $this->save($sql, $datos);
        if ($data == 1) {
            $res = "modificado";
        } else {
            $res = "error";
        }

        $sql = "INSERT INTO `programaciones` (`codigo`,`id_vendedor`,`fecha`,`fecha_asignacion`) VALUES (?,?,?,now())";
        $datos = array($id, $id_vendedor1, $fecha);
        $this->save($sql, $datos);*/

        $tituloevento='['.$codcliente.']-'.$nomcliente;
        $sql = "INSERT INTO `eventos` (`title`,`descripcion`,`color`,`textColor`,`destinatario`,`start`,`end`) VALUES (?,'Programacion de Llamada a Cliente','HotPink','white',?,?,?)";
        $datos = array($tituloevento, $id_vendedor1, $fecha, $fecha);
        $this->save($sql, $datos);


        return $res;

    }



    public function editarCli(int $id)
    {
        $sql = "SELECT * FROM clientes WHERE id = $id";
        $data = $this->select($sql);
        return $data;
    }

    public function accionCli(int $estado, int $id)
    {
        $sql = "UPDATE clientes SET estado = ? WHERE id = ?";
        $datos = array($estado, $id);
        $data = $this->save($sql, $datos);
        return $data;
    }

    public function buscarCliente(string $nombre)
    {
        $sql = "SELECT * FROM clientes WHERE nombre LIKE '%" . $nombre . "%' AND estado = 1 LIMIT 10";
        $data = $this->selectAll($sql);
        return $data;
    }

    public function getVisitas(string $nombre)
    {
        $sql = "SELECT * FROM visitas";
        $data = $this->selectAll($sql);
        return $data;
    }


}
