101 lines
3.8 KiB
PHP
101 lines
3.8 KiB
PHP
<?php
|
|
|
|
namespace App\Services;
|
|
|
|
use App\Models\CatalogItem;
|
|
use App\Models\SaleDetail;
|
|
use Illuminate\Support\Facades\DB;
|
|
|
|
class ReportService
|
|
{
|
|
/**
|
|
* Obtener el producto más vendido
|
|
*
|
|
* @param string|null $fromDate Fecha inicial (formato: Y-m-d)
|
|
* @param string|null $toDate Fecha final (formato: Y-m-d)
|
|
* @return array|null Retorna el producto más vendido o null si no hay ventas
|
|
*/
|
|
public function getTopSellingProduct(?string $fromDate = null, ?string $toDate = null): ?array
|
|
{
|
|
$query = SaleDetail::query()
|
|
->selectRaw('
|
|
catalog_items.id,
|
|
catalog_items.name,
|
|
catalog_items.sku,
|
|
categories.name as category_name,
|
|
SUM(sale_details.quantity) as total_quantity_sold,
|
|
SUM(sale_details.subtotal) as total_revenue,
|
|
COUNT(DISTINCT sale_details.sale_id) as times_sold,
|
|
MAX(sales.created_at) as last_sale_date,
|
|
catalog_items.created_at as added_date
|
|
')
|
|
->join('catalog_items', 'sale_details.catalog_item_id', '=', 'catalog_items.id')
|
|
->join('categories', 'catalog_items.category_id', '=', 'categories.id')
|
|
->join('sales', 'sale_details.sale_id', '=', 'sales.id')
|
|
->where('sales.status', 'completed')
|
|
->whereNull('sales.deleted_at');
|
|
|
|
// Aplicar filtro de fechas si se proporcionan ambas
|
|
if ($fromDate && $toDate) {
|
|
$query->whereBetween(DB::raw('DATE(sales.created_at)'), [$fromDate, $toDate]);
|
|
}
|
|
|
|
$result = $query
|
|
->groupBy('catalog_items.id', 'catalog_items.name', 'catalog_items.sku',
|
|
'categories.name', 'catalog_items.created_at')
|
|
->orderByDesc('total_quantity_sold')
|
|
->first();
|
|
|
|
return $result ? $result->toArray() : null;
|
|
}
|
|
|
|
/**
|
|
* Obtener productos sin movimiento
|
|
*
|
|
* @param int $daysThreshold Días mínimos sin movimiento (default: 30)
|
|
* @param bool $includeStockValue Incluir valor del inventario (default: true)
|
|
* @return array Lista de productos sin ventas
|
|
*/
|
|
public function getProductsWithoutMovement(bool $includeStockValue = true, ?string $fromDate = null, ?string $toDate = null): array
|
|
{
|
|
// Obtener IDs de productos que SÍ tienen ventas
|
|
$inventoriesWithSales = SaleDetail::query()
|
|
->join('sales', 'sale_details.sale_id', '=', 'sales.id')
|
|
->where('sales.status', 'completed')
|
|
->whereNull('sales.deleted_at')
|
|
->whereBetween(DB::raw('DATE(sales.created_at)'), [$fromDate, $toDate])
|
|
->distinct()
|
|
->pluck('sale_details.catalog_item_id')
|
|
->toArray();
|
|
|
|
// Construir query para productos SIN ventas
|
|
$query = CatalogItem::query()
|
|
->selectRaw('
|
|
catalog_items.id,
|
|
catalog_items.name,
|
|
catalog_items.sku,
|
|
catalog_items.stock,
|
|
categories.name as category_name,
|
|
catalog_items.created_at as date_added,
|
|
prices.retail_price
|
|
');
|
|
|
|
// Agregar valor del inventario si se solicita
|
|
if ($includeStockValue) {
|
|
$query->addSelect(
|
|
DB::raw('(catalog_items.stock * COALESCE(prices.cost, 0)) as inventory_value')
|
|
);
|
|
}
|
|
|
|
return $query
|
|
->leftJoin('categories', 'catalog_items.category_id', '=', 'categories.id')
|
|
->leftJoin('prices', 'catalog_items.id', '=', 'prices.catalog_item_id')
|
|
->where('catalog_items.is_active', true)
|
|
->whereNull('catalog_items.deleted_at')
|
|
->whereNotIn('catalog_items.id', $inventoriesWithSales)
|
|
->orderBy('catalog_items.created_at')
|
|
->get()
|
|
->toArray();
|
|
}
|
|
}
|