<?php
class CrmModel extends Query
{
    public function __construct()
    {
        parent::__construct();
    }
    public function getEmpresa()
    {
        $sql = "SELECT * FROM empresa";
        $data = $this->select($sql);
        return $data;
    }
    public function getData(string $table)
    {
        $sql = "SELECT COUNT(*) as $table FROM $table WHERE estado = 1";
        $data = $this->select($sql);
        return $data;
    }

    public function getSucursales()
    {
        $sql = "SELECT iduser as id, user as nombre  FROM `ws_users` ORDER BY iduser";
        $data = $this->selectAll($sql);
        return $data;
    }


    public function getVendedores()
    {
        $sql = "SELECT id,tipo,codven,nombre,coord_ultimavisita FROM `vendedores` WHERE tipo = 2 ORDER BY id";
        $data = $this->selectAll($sql);
        return $data;
    }

    public function getVisita($id)
    {
        $sql = "SELECT visitas.*, clientes.nombre as nombre FROM `visitas` 
                INNER JOIN `clientes` ON clientes.codigo = visitas.codigo
                WHERE visitas.id = $id";
        $data = $this->select($sql);
        return $data;
    }

    public function getActividad($fecha_desde,$fecha_hasta)
    {
        $sql = "(SELECT 0 as id, 'P' as sigla,'Programación' as actividad,'' as enc_estado, nombre, codigo as codcli, programacion as detalles,  programacion as fecha, id_vendedor1 as codven, 0 as total FROM `clientes` WHERE programacion between '{$fecha_desde} 00:00:00' and '{$fecha_hasta} 23:59:59')
                UNION
                (SELECT visitas.id as id,'V' as sigla, 'Visita' as actividad , '' as enc_estado,  clientes.nombre as nombre, visitas.codigo as codcli, fecha as detalles, fecha as fecha, id_vendedor as codven, 0 as total FROM `visitas` INNER JOIN `clientes` ON clientes.codigo = visitas.codigo WHERE fecha between '{$fecha_desde} 00:00:00' and '{$fecha_hasta} 23:59:59' )
                UNION
                (SELECT enc_num as id,'VE' as sigla, 'Pedido' as actividad,
                CASE
                    WHEN enc_estado='I' THEN 'Impreso'
                    WHEN enc_estado='D' THEN 'Despachado'                    
                    WHEN enc_estado='F' THEN 'Facturado'                                        
                    WHEN enc_estado='A' THEN 'Autorizado'                                        
                    ELSE 'Sin Estado'
                END as enc_estado,
                enc_nom as nombre, enc_cliente as codcli, enc_num as detalles, enc_fecha as fecha, enc_vend as codven, enc_total as total FROM `".prefixant."`.`docsenc` WHERE `enc_estado`!='X' AND `enc_estado`!='P' AND `enc_estado`!='C' AND `enc_fecha` between '{$fecha_desde} 00:00:00' and '{$fecha_hasta} 23:59:59')
                UNION
                (SELECT enc_num as id,'PE' as sigla, 'Pendiente' as actividad,enc_estado, enc_nom as nombre, enc_cliente as codcli, enc_num as detalles, enc_fecha as fecha, enc_vend as codven, enc_total as total FROM `".prefixant."`.`docsenc` WHERE `enc_estado`='P' AND `enc_fecha` between '{$fecha_desde} 00:00:00' and '{$fecha_hasta} 23:59:59')
                UNION
                (SELECT enc_num as id,'CT' as sigla, 'Cotizacion' as actividad,enc_estado, enc_nom as nombre, enc_cliente as codcli, enc_num as detalles, enc_fecha as fecha, enc_vend as codven, enc_total as total FROM `".prefixant."`.`docsenc` WHERE `enc_estado`='C' AND `enc_fecha` between '{$fecha_desde} 00:00:00' and '{$fecha_hasta} 23:59:59')
                UNION
                (SELECT
                rec_id as id,
                'C' as sigla,
                'Cobro' as actividad,
                '' as enc_estado,
                clientes.nombre as nombre,
                rec_cod as codcli,
                rec_numero as detalles,
                rec_fecha as fecha,
                rec_codven as codven,
                rec_abono as total
                FROM `".prefixant."`.`recibos`
                INNER JOIN `clientes` ON `clientes`.`codigo` = `recibos`.`rec_cod`
                WHERE `rec_fecha` between '{$fecha_desde} 00:00:00' and '{$fecha_hasta} 23:59:59')
                ORDER BY SUBSTRING(codven,2,2),sigla,fecha";

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

    public function getOperadores()
    {
        $sql = "SELECT id,tipo,codven,nombre FROM `vendedores` WHERE tipo = 3 ORDER BY id";
        $data = $this->selectAll($sql);
        return $data;
    }



    public function modificarDatos($identidad, $nombre, $telefono, $correo, $direccion,$mensaje, $impuesto, $smtp, $usuario_smtp, $clave_smtp, $puerto_smtp, $id)
    {
        $sql = "UPDATE empresa SET identidad=?, nombre=?, telefono=?, correo=?, direccion=?, mensaje=?, impuesto = ?, smtp=?, usuario_smtp=?, clave_smtp=?, puerto_smtp=? WHERE id = ?";
            $datos = array($identidad, $nombre, $telefono, $correo, $direccion,$mensaje, $impuesto, $smtp, $usuario_smtp, $clave_smtp, $puerto_smtp, $id);
            $data = $this->save($sql, $datos);
            if ($data == 1) {
                $res = "ok";
            } else {
                $res = "error";
            }
        return $res;
    }
    public function selectStockM()
    {
        $sql = "SELECT descripcion, cantidad FROM productos WHERE stock_minimo > cantidad AND cantidad > 0 AND estado = 1 ORDER BY cantidad ASC LIMIT 10";
        $res = $this->selectAll($sql);
        return $res;
    }
    public function selectProductos()
    {
        $sql = "SELECT p.descripcion, SUM(d.cantidad) as total FROM detalle_ventas d INNER JOIN productos p ON p.id = d.id_producto group by d.id_producto, p.descripcion ORDER BY total DESC LIMIT 10";
        $res = $this->selectAll($sql);
        return $res;
    }
    public function ventasCompras(string $table, int $id_user)
    {
        $sql = "SELECT COUNT(*) as total_dia FROM $table WHERE fecha = CURDATE() AND id_usuario = $id_user";
        $res = $this->selecT($sql);
        return $res;
    }
    public function ingresos(string $table, string $desde, string $hasta, int $id_user)
    {
        $sql = "SELECT
            SUM(IF(MONTH(fecha) = 1,  total, 0)) AS ene,
            SUM(IF(MONTH(fecha) = 2,  total, 0)) AS feb,
            SUM(IF(MONTH(fecha) = 3,  total, 0)) AS mar,
            SUM(IF(MONTH(fecha) = 4,  total, 0)) AS abr,
            SUM(IF(MONTH(fecha) = 5,  total, 0)) AS may,
            SUM(IF(MONTH(fecha) = 6,  total, 0)) AS jun,
            SUM(IF(MONTH(fecha) = 7,  total, 0)) AS jul,
            SUM(IF(MONTH(fecha) = 8,  total, 0)) AS ago,
            SUM(IF(MONTH(fecha) = 9,  total, 0)) AS sep,
            SUM(IF(MONTH(fecha) = 10, total, 0)) AS oct,
            SUM(IF(MONTH(fecha) = 11, total, 0)) AS nov,
            SUM(IF(MONTH(fecha) = 12, total, 0)) AS dic
        FROM $table
        WHERE fecha BETWEEN '$desde' AND '$hasta'";
        $data = $this->select($sql);
        return $data;
    }

    public function ingresosSucursal(string $table, string $desde, string $hasta, int $sucursal)
    {
        $sql = "SELECT
            serie,
            (SELECT user FROM ws_users WHERE ws_users.iduser=$table.serie) AS sucursal,
            (SELECT color FROM ws_users WHERE ws_users.iduser=$table.serie) AS color,
            SUM(IF(MONTH(fecha) = 1,  total, 0)) AS ene,
            SUM(IF(MONTH(fecha) = 2,  total, 0)) AS feb,
            SUM(IF(MONTH(fecha) = 3,  total, 0)) AS mar,
            SUM(IF(MONTH(fecha) = 4,  total, 0)) AS abr,
            SUM(IF(MONTH(fecha) = 5,  total, 0)) AS may,
            SUM(IF(MONTH(fecha) = 6,  total, 0)) AS jun,
            SUM(IF(MONTH(fecha) = 7,  total, 0)) AS jul,
            SUM(IF(MONTH(fecha) = 8,  total, 0)) AS ago,
            SUM(IF(MONTH(fecha) = 9,  total, 0)) AS sep,
            SUM(IF(MONTH(fecha) = 10, total, 0)) AS oct,
            SUM(IF(MONTH(fecha) = 11, total, 0)) AS nov,
            SUM(IF(MONTH(fecha) = 12, total, 0)) AS dic
        FROM $table
        WHERE fecha BETWEEN '$desde' AND '$hasta'
        GROUP BY serie
        ORDER BY serie";
        $data = $this->selectAll($sql);
        return $data;
    }

    public function ingresosSucursalMes(string $table, string $desde, string $hasta, int $sucursal)
    {
        $sql = "SELECT
            serie,
            (SELECT user FROM ws_users WHERE ws_users.iduser=$table.serie) AS sucursal,
            (SELECT color FROM ws_users WHERE ws_users.iduser=$table.serie) AS color,
            SUM(total) AS total
        FROM $table
        WHERE fecha BETWEEN '$desde' AND '$hasta'
        GROUP BY serie
        ORDER BY serie";
        $data = $this->selectAll($sql);
        return $data;
    }

    public function ingresosVendedor(string $table, string $desde, string $hasta, int $sucursal)
    {
        $sql = "SELECT
        id_vendedor as id,
        (SELECT nombre FROM vendedores WHERE vendedores.codven=$table.id_vendedor) AS vendedor,
        sum(total) as total
        FROM $table
        WHERE id_vendedor<>'' AND fecha BETWEEN '$desde' AND '$hasta'
        GROUP BY id_vendedor";
        
        $data = $this->selectAll($sql);
        return $data;
    }

    public function ingresos_user(string $table, string $desde, string $hasta, int $id_user)
    {
        $sql = "SELECT
            SUM(IF(MONTH(fecha) = 1,  total, 0)) AS ene,
            SUM(IF(MONTH(fecha) = 2,  total, 0)) AS feb,
            SUM(IF(MONTH(fecha) = 3,  total, 0)) AS mar,
            SUM(IF(MONTH(fecha) = 4,  total, 0)) AS abr,
            SUM(IF(MONTH(fecha) = 5,  total, 0)) AS may,
            SUM(IF(MONTH(fecha) = 6,  total, 0)) AS jun,
            SUM(IF(MONTH(fecha) = 7,  total, 0)) AS jul,
            SUM(IF(MONTH(fecha) = 8,  total, 0)) AS ago,
            SUM(IF(MONTH(fecha) = 9,  total, 0)) AS sep,
            SUM(IF(MONTH(fecha) = 10, total, 0)) AS oct,
            SUM(IF(MONTH(fecha) = 11, total, 0)) AS nov,
            SUM(IF(MONTH(fecha) = 12, total, 0)) AS dic
        FROM $table
        WHERE fecha BETWEEN '$desde' AND '$hasta' AND id_usuario = $id_user";
        $data = $this->select($sql);
        return $data;
    }

    public function ingresosDia(string $table, string $dia, int $id_user)
    {
        $sql = "SELECT SUM(total) AS total FROM $table WHERE fecha = '$dia' AND id_usuario = $id_user";
        $data = $this->select($sql);
        return $data;
    }
    public function getMontoCaja(int $id_user)
    {
        $sql = "SELECT SUM(total) AS total FROM ventas WHERE id_usuario = $id_user AND estado = 1 AND forma_pago = 1 AND apertura = 1";
        $data = $this->select($sql);
        return $data;
    }
    public function getMontoCredito(int $id_user)
    {
        $sql = "SELECT SUM(monto) AS total FROM abonos WHERE id_usuario = $id_user AND apertura = 1";
        $data = $this->select($sql);
        return $data;
    }
    public function getMontoInicial(int $id_user)
    {
        $sql = "SELECT id, monto_inicial FROM cajas_cierre WHERE id_usuario = $id_user AND estado = 1";
        $data = $this->select($sql);
        return $data;
    }
    public function getAcciones(string $table, int $id)
    {
        $sql = "SELECT SUM(monto) AS total FROM $table WHERE id_usuario = $id AND estado = 1";
        $data = $this->select($sql);
        return $data;
    }
    public function getApartados(int $id)
    {
        $sql = "SELECT SUM(monto_abono) AS total FROM apartados WHERE id_usuario = $id AND estado = 1";
        $data = $this->select($sql);
        return $data;
    }
}
