<?php
class AdminModel 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 getVendedores()
    {
        $sql = "SELECT `codven` as id, `nombre` FROM `vendedores` WHERE TRIM(`nombre`)!='' AND tipo=2 ORDER BY id";
        $data = $this->selectAll($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 getCategorias()
    {
        $sql = "SELECT id, cod_linea, categoria  FROM `categorias` ORDER BY cod_linea";
        $data = $this->selectAll($sql);
        return $data;
    }


    public function modificarDatos($identidad, $nombre, $telefono, $correo, $direccion,$mensaje, $impuesto, $smtp, $usuario_smtp, $clave_smtp, $puerto_smtp, $color_header, $color_navbar, $id)
    {
        $sql = "UPDATE empresa SET identidad=?, nombre=?, telefono=?, correo=?, direccion=?, mensaje=?, impuesto = ?, smtp=?, usuario_smtp=?, clave_smtp=?, puerto_smtp=?, color_header=?, color_navbar=? WHERE id = ?";
            $datos = array($identidad, $nombre, $telefono, $correo, $direccion,$mensaje, $impuesto, $smtp, $usuario_smtp, $clave_smtp, $puerto_smtp,  $color_header, $color_navbar, $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 ventasAbonos(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;
    }


    // VENTAS POR MES
    public function ingresos(string $table, string $desde, string $hasta, int $id_user)
    {
        $sql = "SELECT
            SUM(IF(MONTH(fechadoc) = 1,  total, 0)) AS ene,
            SUM(IF(MONTH(fechadoc) = 2,  total, 0)) AS feb,
            SUM(IF(MONTH(fechadoc) = 3,  total, 0)) AS mar,
            SUM(IF(MONTH(fechadoc) = 4,  total, 0)) AS abr,
            SUM(IF(MONTH(fechadoc) = 5,  total, 0)) AS may,
            SUM(IF(MONTH(fechadoc) = 6,  total, 0)) AS jun,
            SUM(IF(MONTH(fechadoc) = 7,  total, 0)) AS jul,
            SUM(IF(MONTH(fechadoc) = 8,  total, 0)) AS ago,
            SUM(IF(MONTH(fechadoc) = 9,  total, 0)) AS sep,
            SUM(IF(MONTH(fechadoc) = 10, total, 0)) AS oct,
            SUM(IF(MONTH(fechadoc) = 11, total, 0)) AS nov,
            SUM(IF(MONTH(fechadoc) = 12, total, 0)) AS dic
        FROM `".prefixant."`.`facturaenc`
        WHERE fechadoc BETWEEN '$desde' AND '$hasta' and estado='F'";
        $data = $this->select($sql);
        return $data;
    }


    // PAGOS POR MES
    public function ingresosabonos(string $table, string $desde, string $hasta, int $id_user)
    {
        $sql = "SELECT
            SUM(IF(MONTH(rec_fecha) = 1,  rec_abono, 0)) AS ene,
            SUM(IF(MONTH(rec_fecha) = 2,  rec_abono, 0)) AS feb,
            SUM(IF(MONTH(rec_fecha) = 3,  rec_abono, 0)) AS mar,
            SUM(IF(MONTH(rec_fecha) = 4,  rec_abono, 0)) AS abr,
            SUM(IF(MONTH(rec_fecha) = 5,  rec_abono, 0)) AS may,
            SUM(IF(MONTH(rec_fecha) = 6,  rec_abono, 0)) AS jun,
            SUM(IF(MONTH(rec_fecha) = 7,  rec_abono, 0)) AS jul,
            SUM(IF(MONTH(rec_fecha) = 8,  rec_abono, 0)) AS ago,
            SUM(IF(MONTH(rec_fecha) = 9,  rec_abono, 0)) AS sep,
            SUM(IF(MONTH(rec_fecha) = 10, rec_abono, 0)) AS oct,
            SUM(IF(MONTH(rec_fecha) = 11, rec_abono, 0)) AS nov,
            SUM(IF(MONTH(rec_fecha) = 12, rec_abono, 0)) AS dic
        FROM `".prefixant."`.`recibos`
        WHERE rec_fecha BETWEEN '$desde' AND '$hasta'";
        $data = $this->select($sql);
        return $data;
    }

    // VENTAS POR VENDEDOR    
    public function ingresosVendedor(string $table, string $desde, string $hasta, int $sucursal)
    {
        $categoria=((int)$sucursal>0)?"AND det.codart IN (SELECT prod.`codigo` FROM `".db."`.`productos` prod WHERE prod.`id_categoria`={$sucursal})":"";

        $sql = "
            SELECT 
            CAST(fac.codven AS UNSIGNED) as id
            ,sum(det.total) as total
            ,(SELECT nombre FROM `".db."`.vendedores vend WHERE vend.codven=fac.codven) AS vendedor
            ,(SELECT codven FROM `".db."`.vendedores vend WHERE vend.codven=fac.codven) AS codven
            ,(SELECT tipo   FROM `".db."`.vendedores vend WHERE vend.codven=fac.codven) AS tipovend
            ,(SELECT meta   FROM `".db."`.vendedores vend WHERE vend.codven=fac.codven) AS meta
            FROM `".prefixant."`.`facturadet` det
            INNER JOIN `".prefixant."`.`facturaenc` fac ON fac.numdoc = det.numdoc
            WHERE fac.estado='F' and fac.codven<>0 AND fac.fechadoc BETWEEN '{$desde} 00:00:00' AND '{$hasta} 23:59:59'
            {$categoria}
            GROUP BY codven
            HAVING tipovend=2
            ORDER BY id
        ";
        /*$sql = "SELECT
        CAST(codven AS UNSIGNED) as id,
        (SELECT nombre FROM vendedores WHERE vendedores.codven=`".prefixant."`.`facturaenc`.codven) AS vendedor,
        (SELECT codven FROM vendedores WHERE vendedores.codven=`".prefixant."`.`facturaenc`.codven) AS codven,
        (SELECT tipo   FROM vendedores WHERE vendedores.codven=`".prefixant."`.`facturaenc`.codven) AS tipovend,        
        sum(total) as total
        FROM `".prefixant."`.`facturaenc`
        WHERE codven<>0 AND fechadoc BETWEEN '$desde 00:00:00' AND '$hasta 23:59:59'
        GROUP BY codven
        HAVING tipovend=2
        ORDER BY id";*/
        $data = $this->selectAll($sql);
        return $data;
    }

    // VENTAS POR OPERADOR
    public function ingresosOperador(string $table, string $desde, string $hasta, int $sucursal)
    {

        $categoria=((int)$sucursal>0)?"AND det.codart IN (SELECT prod.`codigo` FROM `".db."`.`productos` prod WHERE prod.`id_categoria`={$sucursal})":"";

        $sql = "
            SELECT 
            CAST(fac.codven AS UNSIGNED) as id
            ,sum(det.total) as total
            ,(SELECT nombre FROM `".db."`.vendedores vend WHERE vend.codven=fac.codven) AS vendedor
            ,(SELECT codven FROM `".db."`.vendedores vend WHERE vend.codven=fac.codven) AS codven
            ,(SELECT tipo   FROM `".db."`.vendedores vend WHERE vend.codven=fac.codven) AS tipovend
            ,(SELECT meta   FROM `".db."`.vendedores vend WHERE vend.codven=fac.codven) AS meta
            FROM `".prefixant."`.`facturadet` det
            INNER JOIN `".prefixant."`.`facturaenc` fac ON fac.numdoc = det.numdoc
            WHERE fac.estado='F' and fac.codven<>0 AND fac.fechadoc BETWEEN '{$desde} 00:00:00' AND '{$hasta} 23:59:59'
            {$categoria}
            GROUP BY codven
            HAVING tipovend=3
            ORDER BY id
        ";        

        /*$sql = "SELECT
        CAST(codven AS UNSIGNED) as id,
        (SELECT nombre FROM vendedores WHERE vendedores.codven=`".prefixant."`.`facturaenc`.codven) AS vendedor,
        (SELECT codven FROM vendedores WHERE vendedores.codven=`".prefixant."`.`facturaenc`.codven) AS codven,
        (SELECT tipo   FROM vendedores WHERE vendedores.codven=`".prefixant."`.`facturaenc`.codven) AS tipovend,                
        sum(total) as total
        FROM `".prefixant."`.`facturaenc`
        WHERE codven<>0 AND fechadoc BETWEEN '$desde' AND '$hasta' 
        GROUP BY codven
        HAVING tipovend=3
        ORDER BY id";*/

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

    // VENTAS POR DEPARTAMENTO
    public function ingresosDepartamento(string $table, string $desde, string $hasta, int $sucursal)
    {
        $categoria=((int)$sucursal>0)?"AND b.codven={$sucursal}":"";

        $sql = "SELECT a.departamento,sum(b.total) as total 
        FROM `clientes` a, `".prefixant."`.`facturaenc` b 
        WHERE b.estado='F' and a.codigo=b.codcliente AND fechadoc BETWEEN '$desde 00:00:00' AND '$hasta 23:59:59'
        {$categoria}
        GROUP BY departamento
        ORDER BY total DESC";

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

    // VENTAS POR CATEGORIA
    public function ingresosCategoria(string $table, string $desde, string $hasta, int $sucursal)
    {
        $categoria=((int)$sucursal>0)?"AND e.codven={$sucursal}":"";

        $sql = "SELECT p.id_categoria, c.cod_linea,c.categoria, sum(f.cantidad) as unidades, sum(f.total) as totalventa
                FROM categorias c,`".prefixant."`.`facturadet` f,`".prefixant."`.`facturaenc` e,productos p
                WHERE e.estado='F' and c.id=p.id_categoria AND f.numdoc=e.numdoc AND f.codart=p.codigo AND e.fechadoc BETWEEN '$desde 00:00:00' AND '$hasta 23:59:59'
                {$categoria}
                GROUP BY p.id_categoria
                ORDER BY totalventa DESC
                ";

        $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 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;
    }
}
