Tul xxx Tul

Viewing: EstadoresultadoModel.php

<?php
class EstadoresultadoModel extends Query
{
    public function __construct()
    {
        parent::__construct();
    }

    public function getRangoFechas($id_sucursal)
    {
        $sql = "SELECT MIN(f) AS desde, MAX(f) AS hasta
                FROM (
                    SELECT fecha AS f FROM ventas WHERE estado = 1 AND id_sucursal = $id_sucursal
                    UNION ALL
                    SELECT fecha AS f FROM compras WHERE estado = 1 AND id_sucursal = $id_sucursal
                    UNION ALL
                    SELECT DATE(fecha) AS f FROM gastos WHERE id_sucursal = $id_sucursal
                    UNION ALL
                    SELECT DATE(d.fecha) AS f
                    FROM detalle_apartado d
                    INNER JOIN apartados ap ON ap.id = d.id_apartado
                    WHERE ap.estado = 0 AND ap.id_sucursal = $id_sucursal
                ) t";
        return $this->select($sql);
    }

    public function getTotalApartados($id_sucursal, $desde, $hasta)
    {
        $sql = "SELECT COALESCE(SUM(d.monto), 0) AS total
                FROM detalle_apartado d
                INNER JOIN apartados ap ON ap.id = d.id_apartado
                WHERE ap.id_sucursal = $id_sucursal
                  AND DATE(d.fecha) BETWEEN '$desde' AND '$hasta'";
        return $this->select($sql);
    }

    public function getTotalVentas($id_sucursal, $desde, $hasta)
    {
        $sql = "SELECT COALESCE(SUM(total), 0) AS total
                FROM (
                    SELECT v.totalConImpuesto AS total
                    FROM ventas v
                    WHERE v.estado = 1 AND v.id_sucursal = $id_sucursal
                      AND v.fecha BETWEEN '$desde' AND '$hasta'

                    UNION ALL

                    SELECT d.monto AS total
                    FROM detalle_apartado d
                    INNER JOIN apartados ap ON ap.id = d.id_apartado
                    WHERE ap.id_sucursal = $id_sucursal
                      AND DATE(d.fecha) BETWEEN '$desde' AND '$hasta'
                ) t";

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

    public function getTotalDescuentos($id_sucursal, $desde, $hasta)
    {
        $sql = "SELECT COALESCE(SUM(descuento), 0) AS total
                FROM ventas
                WHERE estado = 1 AND id_sucursal = $id_sucursal
                  AND fecha BETWEEN '$desde' AND '$hasta'";

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

    public function getTotalCompras($id_sucursal, $desde, $hasta)
    {
        $sql = "SELECT COALESCE(SUM(
                    CASE
                        WHEN c.total_pagar IS NOT NULL AND c.total_pagar > 0 THEN c.total_pagar
                        ELSE (c.total + (c.total * (s.impuesto / 100)))
                    END
                ), 0) AS total
                FROM compras c
                INNER JOIN sucursales s ON s.id = c.id_sucursal
                WHERE c.estado = 1
                  AND c.id_sucursal = $id_sucursal
                  AND c.fecha BETWEEN '$desde' AND '$hasta'";
        return $this->select($sql);
    }

    public function getCostoVentas($id_sucursal, $desde, $hasta)
    {
        $sqlVentas = "SELECT fecha, hora, productos
                      FROM ventas
                      WHERE estado = 1 AND id_sucursal = $id_sucursal
                        AND fecha BETWEEN '$desde' AND '$hasta'

                      UNION ALL

                      SELECT DATE(d.fecha) AS fecha, TIME(d.fecha) AS hora, ap.productos
                      FROM apartados ap
                      INNER JOIN (
                          SELECT id_apartado, MAX(fecha) AS fecha
                          FROM detalle_apartado
                          GROUP BY id_apartado
                      ) d ON d.id_apartado = ap.id
                      WHERE ap.estado = 0 AND ap.id_sucursal = $id_sucursal
                        AND DATE(d.fecha) BETWEEN '$desde' AND '$hasta'

                      ORDER BY fecha ASC, hora ASC";
        $ventasRaw = $this->selectAll($sqlVentas);

        if (empty($ventasRaw)) {
            return ['total' => 0];
        }

        $ventas = [];
        $idsSet = [];
        foreach ($ventasRaw as $venta) {
            if (empty($venta['productos'])) {
                continue;
            }
            $items = json_decode($venta['productos'], true);
            if (!is_array($items)) {
                continue;
            }
            foreach ($items as $it) {
                if (isset($it['id'])) {
                    $idsSet[(int)$it['id']] = true;
                }
            }
            $ventas[] = ['fecha' => $venta['fecha'], 'hora' => $venta['hora'], 'items' => $items];
        }

        if (empty($ventas) || empty($idsSet)) {
            return ['total' => 0];
        }

        $ids = array_keys($idsSet);
        $ids = array_map('intval', $ids);
        $ids = array_filter($ids, function ($id) {
            return $id > 0;
        });

        if (empty($ids)) {
            return ['total' => 0];
        }

        $idsSql = implode(',', $ids);
        $sqlInfo = "SELECT id, precio_compra, servicio FROM productos WHERE id_sucursal = $id_sucursal AND id IN ($idsSql)";
        $productosInfo = $this->selectAll($sqlInfo);
        $info = [];
        foreach ($productosInfo as $p) {
            $pid = (int)$p['id'];
            $info[$pid] = [
                'precio_compra' => (float)$p['precio_compra'],
                'servicio' => (int)$p['servicio'],
            ];
        }

        $sqlCompras = "SELECT fecha, hora, productos FROM compras WHERE estado = 1 AND id_sucursal = $id_sucursal AND fecha <= '$hasta' ORDER BY fecha ASC, hora ASC";
        $compras = $this->selectAll($sqlCompras);

        $lastCost = [];
        $idxCompra = 0;
        $missingQty = [];
        $total = 0;

        foreach ($ventas as $venta) {
            $fechaVenta = $venta['fecha'] . ' ' . ($venta['hora'] ?? '00:00:00');

            while ($idxCompra < count($compras)) {
                $fechaCompra = $compras[$idxCompra]['fecha'] . ' ' . ($compras[$idxCompra]['hora'] ?? '00:00:00');
                if ($fechaCompra > $fechaVenta) {
                    break;
                }
                $compraProductos = $compras[$idxCompra]['productos'] ?? null;
                $idxCompra++;
                if (empty($compraProductos)) {
                    continue;
                }
                $itemsCompra = json_decode($compraProductos, true);
                if (!is_array($itemsCompra)) {
                    continue;
                }
                foreach ($itemsCompra as $item) {
                    if (!isset($item['id']) || !isset($item['precio'])) {
                        continue;
                    }
                    $pid = (int)$item['id'];
                    $precio = (float)$item['precio'];
                    if ($pid > 0 && $precio >= 0) {
                        $lastCost[$pid] = $precio;
                    }
                }
            }

            foreach ($venta['items'] as $item) {
                if (!isset($item['id']) || !isset($item['cantidad'])) {
                    continue;
                }
                $pid = (int)$item['id'];
                $qty = (float)$item['cantidad'];
                if ($pid <= 0 || $qty <= 0) {
                    continue;
                }
                if (!empty($info[$pid]) && (int)$info[$pid]['servicio'] === 1) {
                    continue;
                }

                if (isset($lastCost[$pid])) {
                    $total += ($qty * (float)$lastCost[$pid]);
                } else {
                    if (!isset($missingQty[$pid])) {
                        $missingQty[$pid] = 0;
                    }
                    $missingQty[$pid] += $qty;
                }
            }
        }

        foreach ($missingQty as $pid => $qty) {
            $costoFallback = (!empty($info[$pid])) ? (float)$info[$pid]['precio_compra'] : 0;
            $total += ((float)$qty * (float)$costoFallback);
        }

        return ['total' => $total];
    }

    public function getTotalGastos($id_sucursal, $desde, $hasta)
    {
        $sql = "SELECT SUM(monto) AS total FROM gastos WHERE id_sucursal = $id_sucursal AND DATE(fecha) BETWEEN '$desde' AND '$hasta'";
        return $this->select($sql);
    }

    public function getIvaVentas($id_sucursal, $desde, $hasta, $metodo = null)
    {
        $sql = "SELECT COALESCE(SUM(
                    CASE
                        WHEN v.iva_monto > 0 THEN v.iva_monto
                        WHEN v.totalConImpuesto > v.total THEN (v.totalConImpuesto - v.total)
                        ELSE 0
                    END
                ), 0) AS total
                FROM ventas v
                WHERE v.estado = 1
                  AND v.id_sucursal = $id_sucursal
                  AND v.fecha BETWEEN '$desde' AND '$hasta'";

        if ($metodo === 'CONTADO' || $metodo === 'CREDITO') {
            $sql .= " AND v.metodo = '$metodo'";
        }

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

    public function getIvaCompras($id_sucursal, $desde, $hasta, $metodo = null)
    {
        $sql = "SELECT COALESCE(SUM(
                    CASE
                        WHEN ((c.total_pagar IS NOT NULL AND c.total_pagar > 0)
                              OR c.aplica_iva = 1
                              OR (c.iva_monto IS NOT NULL AND c.iva_monto > 0)
                              OR (c.retencion_monto IS NOT NULL AND c.retencion_monto > 0))
                            THEN COALESCE(c.iva_monto, 0)
                        ELSE (c.total * (s.impuesto / 100))
                    END
                ), 0) AS total
                FROM compras c
                INNER JOIN sucursales s ON s.id = c.id_sucursal
                WHERE c.estado = 1
                  AND c.id_sucursal = $id_sucursal
                  AND c.fecha BETWEEN '$desde' AND '$hasta'";

        if ($metodo === 'CONTADO' || $metodo === 'CREDITO') {
            $sql .= " AND c.metodo = '$metodo'";
        }

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

    public function getIvaGastos($id_sucursal, $desde, $hasta)
    {
        $sql = "SELECT COALESCE(SUM(
                    CASE
                        WHEN g.aplica_iva = 1 OR (g.iva_monto IS NOT NULL AND g.iva_monto > 0)
                            THEN COALESCE(g.iva_monto, 0)
                        ELSE 0
                    END
                ), 0) AS total
                FROM gastos g
                WHERE g.id_sucursal = $id_sucursal
                  AND DATE(g.fecha) BETWEEN '$desde' AND '$hasta'";
        return $this->select($sql);
    }

    public function getRetencionesCompras($id_sucursal, $desde, $hasta, $metodo = null)
    {
        $sql = "SELECT COALESCE(SUM(COALESCE(c.retencion_monto, 0)), 0) AS total
                FROM compras c
                WHERE c.estado = 1
                  AND c.id_sucursal = $id_sucursal
                  AND c.fecha BETWEEN '$desde' AND '$hasta'";

        if ($metodo === 'CONTADO' || $metodo === 'CREDITO') {
            $sql .= " AND c.metodo = '$metodo'";
        }
        return $this->select($sql);
    }

    public function getRetencionesGastos($id_sucursal, $desde, $hasta)
    {
        $sql = "SELECT COALESCE(SUM(COALESCE(g.retencion_monto, 0)), 0) AS total
                FROM gastos g
                WHERE g.id_sucursal = $id_sucursal
                  AND DATE(g.fecha) BETWEEN '$desde' AND '$hasta'";
        return $this->select($sql);
    }

    public function getEmpresa($id_sucursal)
    {
        $sql = "SELECT * FROM sucursales WHERE id = $id_sucursal";
        return $this->select($sql);
    }
}
User / IP	: 216.73.216.159
Host / Server	: 45.84.207.204 / aircan.me
System	: Linux lt-bnk-web1726.main-hosting.eu 5.14.0-611.36.1.el9_7.x86_64 #1 SMP PREEMPT_DYNAMIC Tue Mar 3 11:23:52 EST 2026 x86_64