admin.dukany.io/app/Exports/ItemSalesExport.php

<?php

namespace App\Exports;

use App\Helpers\CommonMethods;
use App\Models\Recipes;
use App\Models\StaticReports;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithHeadings;

class ItemSalesExport implements FromCollection, WithHeadings, ShouldAutoSize
{
    /**
    * @return \Illuminate\Support\Collection
    */
    private $data = null;
    private $logo = "";

    public function __construct($data){
        $this->data = $data;
    }
    public function collection()
    {
        $start_date = $this->data['start_date'];
        $end_date = $this->data['end_date'];
        $file_name = $this->data['file_name'];
        $resto_id = CommonMethods::getRestuarantID();
      //  dd($start_date,$end_date);
        $items = Recipes::select('name','arabic_name','price')->withCount('items_sales')->whereHas('items_sales',function($q) use ($start_date,$end_date){
            $q->whereBetween(DB::raw('DATE(created_at)'),[$start_date,$end_date]);
        })->where('resto_id',$resto_id)->orderBy('items_sales_count','DESC')->get();
       // dd($items);

//        $status = 'Initial';
//        $items = DB::table('tb_dm_order_items')->select(DB::raw('count(recipe_id) as recipe_count'),'recipe_id')
//            ->whereIn('order_id',function($query) use($start_date,$end_date,$resto_id,$status){
//                $query->select('id')->from('tb_dm_orders')
//                    ->whereBetween(DB::raw('DATE(created_at)'),[$start_date,$end_date])
//                    ->where('resto_id',$resto_id)
//               ->where('status','!=',"'".$status."'");
//            })->groupBy('recipe_id')->orderBy('recipe_count','DESC');
//        Log::info("Query: ".$items->toSql());
//        Log::info("Binding: ".json_encode($items->getBindings()));
//
//        $items = $items->get();
        //$query = \DB::getQueryLog();
        //dd(($query));



        $item_sales = [];

      //  dd($items[0]->items_sales_count);
        if(isset($items) && $items->count() > 0){

            foreach($items as $k=>$item){

                if($item->items_sales_count > 0){

                    Log::info("Quantity: ".json_encode($item->items_sales));
                 //   dump($item);


                $item_sales[] = array(
                    //'id'=>$item->id,
                    'name'=> $item->name,
                    'arabic_name'=>$item->arabic_name,
                    'price'=>$item->price,
                    'item_sale_orders'=>$item->items_sales_count,
                    'item_sold_quantity'=> $item->items_sales->sum('qty'),
                    'item_sold_amount'=>number_format($item->items_sales->sum('price')),

            );
                }
            }

        }
     //   dd($item_sales);

        $r_item_sales = array(
            'file_name'=>$file_name.'.xlsx',
            'report_type'=>'items',
            'start_date'=>$start_date,
            'end_date'=>$end_date,
            'is_data_found'=>count($item_sales) > 0?"Yes":"No",
            'status'=>'active',
            'searched_by'=>Auth::id(),
            'resto_id'=>$resto_id
        );

        $r = StaticReports::where('start_date',$start_date)->whereNull('deleted_at')->where('end_date',$end_date)->where('report_type','items')->where('resto_id',$resto_id)->first();
        if(!$r){
            $report = StaticReports::insert($r_item_sales);
        }

        return collect($item_sales);

    }
     public function headings(): array
     {
         return [
             'Item Name',
             'Item Arabic Name',
             'Item Price',
             'Item Total Orders',
             'Item Total Sold Quantity',
             'Item Total Sold Amount'

         ];
     }
}