Gecko [ thecigaretteboxes.com ]

Name	Size	Permission
AgentSaleExport.php	7.75 KB	-rw-r--r--
DailyLeadsExport.php	473 B	-rw-r--r--
DealExport.php	10.96 KB	-rw-r--r--
EstimationsExport.php	4.11 KB	-rw-r--r--
MarketingExport.php	11.3 KB	-rw-r--r--
MonthlyReportExport.php	2.38 KB	-rw-r--r--
OrderPNLExport.php	8.58 KB	-rw-r--r--
ProductionExport.php	8.54 KB	-rw-r--r--
ReordersExport.php	7.02 KB	-rw-r--r--
SalariesExport.php	8.01 KB	-rw-r--r--
ShippingExport.php	10.33 KB	-rw-r--r--

Code Editor : MonthlyReportExport.php

<?php
namespace App\Exports;

use Illuminate\Contracts\View\View;
use Maatwebsite\Excel\Concerns\FromView;
use Maatwebsite\Excel\Concerns\WithTitle;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class MonthlyReportExport implements FromView, WithTitle
{
    public function view(): View
    {
        // Set your start and end dates for the report
        $startDate = Carbon::now()->startOfMonth()->format('Y-m-d');
        $endDate = Carbon::now()->endOfMonth()->format('Y-m-d');

// Fetch the data from the database
        $reportData = DB::table('deals')
            ->join('deal_products', 'deals.id', '=', 'deal_products.deal_id')
            ->join('deal_product_quantity_and_estimation_amounts', 'deal_products.id', '=', 'deal_product_quantity_and_estimation_amounts.deal_product_id')
            ->join('products', 'deal_products.product_id', '=', 'products.id')
            ->select(
                'products.name as product_name',
                DB::raw('DATE(deals.open_date) as order_date'),
                DB::raw('COUNT(DISTINCT deals.id) as order_count'), // Count distinct deals for order count
                DB::raw('SUM(CASE WHEN deal_product_quantity_and_estimation_amounts.checked_value = 1 THEN deal_product_quantity_and_estimation_amounts.deal_product_quantity ELSE 0 END) as total_quantity') // Sum only where checked_value is 1
            )
            ->whereBetween('deals.open_date', [$startDate, $endDate])
            ->groupBy('products.name', 'order_date')
            ->orderBy('products.name')
            ->orderBy('order_date')
            ->get();

// Transform data for display
        $formattedData = $reportData->groupBy('product_name')->map(function ($item) {
            return $item->mapWithKeys(function ($dateData) {
                return [$dateData->order_date => [
                    'order_count' => $dateData->order_count,
                    'total_quantity' => $dateData->total_quantity,
                ]];
            })->toArray();
        })->toArray();

// Get all dates in the report period
        $dates = collect($formattedData)->flatMap(fn($data) => array_keys($data))->unique()->sort()->values();

return view('admin.deal.monthly_report_export', [
            'formattedData' => $formattedData,
            'dates' => $dates,
        ]);
    }

public function title(): string
    {
        return 'Monthly Report';
    }
}

${this.title}

Code Editor : MonthlyReportExport.php