FM / Controllers

/home/techb158/dev.balacoffee.com/app/Http/Controllers/

/home/techb158/dev.balacoffee.com/app/Http/Controllers

Name	Size	Mode	Actions
Auth/	-	0755	rm
hrm/	-	0755	rm
AdjustmentController.php	31935	0644	edit dl rm
AuthController.php	1761	0644	edit dl rm
BaseController.php	3183	0644	edit dl rm
BrandsController.php	5804	0644	edit dl rm
CategorieController.php	3625	0644	edit dl rm
CategoryExpenseController.php	5449	0644	edit dl rm
ClientController.php	8467	0644	edit dl rm
Controller.php	374	0644	edit dl rm
CurrencyController.php	4035	0644	edit dl rm
ExpensesController.php	12670	0644	edit dl rm
HomeController.php	495	0644	edit dl rm
PasswordResetController.php	3895	0644	edit dl rm
PaymentPurchaseReturnsController.php	14650	0644	edit dl rm
PaymentPurchasesController.php	14138	0644	edit dl rm
PaymentSaleReturnsController.php	14303	0644	edit dl rm
PaymentSalesController.php	21799	0644	edit dl rm
PermissionsController.php	6531	0644	edit dl rm
PosController.php	16651	0644	edit dl rm
ProductsController.php	48520	0644	edit dl rm
ProvidersController.php	7279	0644	edit dl rm
PurchasesController.php	47279	0644	edit dl rm
PurchasesReturnController.php	48366	0644	edit dl rm
QuotationsController.php	31462	0644	edit dl rm
ReportController.php	149323	0644	edit dl rm
SalesController.php	64420	0644	edit dl rm
SalesReturnController.php	47094	0644	edit dl rm
SettingsController.php	12354	0644	edit dl rm
SetupController.php	8770	0644	edit dl rm
ShipmentController.php	7633	0644	edit dl rm
TestDbController.php	3156	0644	edit dl rm
TransferController.php	54574	0644	edit dl rm
UnitsController.php	6162	0644	edit dl rm
UpdateController.php	2064	0644	edit dl rm
UserController.php	13167	0644	edit dl rm
WarehouseController.php	6427	0644	edit dl rm

Edit: /home/techb158/dev.balacoffee.com/app/Http/Controllers/ReportController.php (149323B)

<?php

namespace App\Http\Controllers;

use App\Models\Client;
use App\Models\Expense;
use App\Models\PaymentPurchase;
use App\Models\PaymentPurchaseReturns;
use App\Models\PaymentSale;
use App\Models\PaymentSaleReturns;
use App\Models\Product;
use App\Models\Transfer;
use App\Models\TransferDetail;
use App\Models\Adjustment;
use App\Models\AdjustmentDetail;
use App\Models\ProductVariant;
use App\Models\product_warehouse;
use App\Models\Provider;
use App\Models\Purchase;
use App\Models\Setting;
use App\Models\PurchaseDetail;
use App\Models\PurchaseReturn;
use App\Models\PurchaseReturnDetails;
use App\Models\Quotation;
use App\Models\QuotationDetail;
use App\Models\Role;
use App\Models\Sale;
use App\Models\SaleDetail;
use App\Models\SaleReturn;
use App\Models\SaleReturnDetails;
use App\Models\User;
use App\Models\UserWarehouse;
use App\Models\Warehouse;
use App\utils\helpers;
use Carbon\Carbon;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Storage;
use DB;

class ReportController extends BaseController
{

//----------------- Sales Chart js -----------------------\\

public function SalesChart()
    {
        $role = Auth::user()->roles()->first();
        $view_records = Role::findOrFail($role->id)->inRole('record_view');

// Build an array of the dates we want to show, oldest first
        $dates = collect();
        foreach (range(-6, 0) as $i) {
            $date = Carbon::now()->addDays($i)->format('Y-m-d');
            $dates->put($date, 0);
        }

$date_range = \Carbon\Carbon::today()->subDays(6);
        // Get the sales counts
        $sales = Sale::where('date', '>=', $date_range)
            ->where('deleted_at', '=', null)
            ->where(function ($query) use ($view_records) {
                if (!$view_records) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            ->groupBy(DB::raw("DATE_FORMAT(date,'%Y-%m-%d')"))
            ->orderBy('date', 'asc')
            ->get([
                DB::raw(DB::raw("DATE_FORMAT(date,'%Y-%m-%d') as date")),
                DB::raw('SUM(GrandTotal) AS count'),
            ])
            ->pluck('count', 'date');

// Merge the two collections;
        $dates = $dates->merge($sales);

$data = [];
        $days = [];
        foreach ($dates as $key => $value) {
            $data[] = $value;
            $days[] = $key;
        }

return response()->json(['data' => $data, 'days' => $days]);

}

//----------------- Purchases Chart -----------------------\\

public function PurchasesChart()
    {

$role = Auth::user()->roles()->first();
        $view_records = Role::findOrFail($role->id)->inRole('record_view');

$date_range = \Carbon\Carbon::today()->subDays(6);

// Get the purchases counts
        $purchases = Purchase::where('date', '>=', $date_range)
            ->where('deleted_at', '=', null)
            ->where(function ($query) use ($view_records) {
                if (!$view_records) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            ->groupBy(DB::raw("DATE_FORMAT(date,'%Y-%m-%d')"))
            ->orderBy('date', 'asc')
            ->get([
                DB::raw(DB::raw("DATE_FORMAT(date,'%Y-%m-%d') as date")),
                DB::raw('SUM(GrandTotal) AS count'),
            ])
            ->pluck('count', 'date');

// Merge the two collections;
        $dates = $dates->merge($purchases);

$data = [];
        $days = [];
        foreach ($dates as $key => $value) {
            $data[] = $value;
            $days[] = $key;
        }

return response()->json(['data' => $data, 'days' => $days]);

}

//-------------------- Get Top 5 Customers -------------\\

public function TopCustomers()
    {
        $role = Auth::user()->roles()->first();
        $view_records = Role::findOrFail($role->id)->inRole('record_view');

$data = Sale::whereBetween('date', [
            Carbon::now()->startOfMonth(),
            Carbon::now()->endOfMonth(),
        ])->where('sales.deleted_at', '=', null)
            ->where(function ($query) use ($view_records) {
                if (!$view_records) {
                    return $query->where('sales.user_id', '=', Auth::user()->id);
                }
            })

->join('clients', 'sales.client_id', '=', 'clients.id')
            ->select(DB::raw('clients.name'), DB::raw("count(*) as value"))
            ->groupBy('clients.name')
            ->orderBy('value', 'desc')
            ->take(5)
            ->get();

return response()->json($data);
    }

//----------------- report dashboard with_echart -----------------------\\

public function report_with_echart()
    {
        $dataSales = $this->SalesChart();
        $datapurchases = $this->PurchasesChart();
        $Payment_chart = $this->Payment_chart();
        $TopCustomers = $this->TopCustomers();
        $Top_Products_Year = $this->Top_Products_Year();
        $report_dashboard = $this->report_dashboard();

return response()->json([
            'sales' => $dataSales,
            'purchases' => $datapurchases,
            'payments' => $Payment_chart,
            'customers' => $TopCustomers,
            'product_report' => $Top_Products_Year,
            'report_dashboard' => $report_dashboard,
        ]);

}

//----------------- Payment Chart js -----------------------\\

public function Payment_chart()
    {

$role = Auth::user()->roles()->first();
        $view_records = Role::findOrFail($role->id)->inRole('record_view');

$date_range = \Carbon\Carbon::today()->subDays(6);
        // Get the sales counts
        $Payment_Sale = PaymentSale::where('date', '>=', $date_range)
            ->where(function ($query) use ($view_records) {
                if (!$view_records) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            ->groupBy(DB::raw("DATE_FORMAT(date,'%Y-%m-%d')"))
            ->orderBy('date', 'asc')
            ->get([
                DB::raw(DB::raw("DATE_FORMAT(date,'%Y-%m-%d') as date")),
                DB::raw('SUM(montant) AS count'),
            ])
            ->pluck('count', 'date');

$Payment_Sale_Returns = PaymentSaleReturns::where('date', '>=', $date_range)
            ->where(function ($query) use ($view_records) {
                if (!$view_records) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            ->groupBy(DB::raw("DATE_FORMAT(date,'%Y-%m-%d')"))
            ->orderBy('date', 'asc')
            ->get([
                DB::raw(DB::raw("DATE_FORMAT(date,'%Y-%m-%d') as date")),
                DB::raw('SUM(montant) AS count'),
            ])
            ->pluck('count', 'date');

$Payment_Purchases = PaymentPurchase::where('date', '>=', $date_range)
            ->where(function ($query) use ($view_records) {
                if (!$view_records) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            ->groupBy(DB::raw("DATE_FORMAT(date,'%Y-%m-%d')"))
            ->orderBy('date', 'asc')
            ->get([
                DB::raw(DB::raw("DATE_FORMAT(date,'%Y-%m-%d') as date")),
                DB::raw('SUM(montant) AS count'),
            ])
            ->pluck('count', 'date');

$Payment_Purchase_Returns = PaymentPurchaseReturns::where('date', '>=', $date_range)
            ->where(function ($query) use ($view_records) {
                if (!$view_records) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            ->groupBy(DB::raw("DATE_FORMAT(date,'%Y-%m-%d')"))
            ->orderBy('date', 'asc')
            ->get([
                DB::raw(DB::raw("DATE_FORMAT(date,'%Y-%m-%d') as date")),
                DB::raw('SUM(montant) AS count'),
            ])
            ->pluck('count', 'date');

$Payment_Expense = Expense::where('date', '>=', $date_range)
            ->where(function ($query) use ($view_records) {
                if (!$view_records) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            ->groupBy(DB::raw("DATE_FORMAT(date,'%Y-%m-%d')"))
            ->orderBy('date', 'asc')
            ->get([
                DB::raw(DB::raw("DATE_FORMAT(date,'%Y-%m-%d') as date")),
                DB::raw('SUM(amount) AS count'),
            ])
            ->pluck('count', 'date');

$paymen_recieved = $this->array_merge_numeric_values($Payment_Sale, $Payment_Purchase_Returns);
        $payment_sent = $this->array_merge_numeric_values($Payment_Purchases, $Payment_Sale_Returns, $Payment_Expense);

$dates_recieved = $dates->merge($paymen_recieved);
        $dates_sent = $dates->merge($payment_sent);

$data_recieved = [];
        $data_sent = [];
        $days = [];
        foreach ($dates_recieved as $key => $value) {
            $data_recieved[] = $value;
            $days[] = $key;
        }

foreach ($dates_sent as $key => $value) {
            $data_sent[] = $value;
        }

return response()->json([
            'payment_sent' => $data_sent,
            'payment_received' => $data_recieved,
            'days' => $days,
        ]);

}

//----------------- array merge -----------------------\\

public function array_merge_numeric_values()
    {
        $arrays = func_get_args();
        $merged = array();
        foreach ($arrays as $array) {
            foreach ($array as $key => $value) {
                if (!is_numeric($value)) {
                    continue;
                }
                if (!isset($merged[$key])) {
                    $merged[$key] = $value;
                } else {
                    $merged[$key] += $value;
                }
            }
        }
        return $merged;
    }

//----------- Get Last 5 Sales --------------\\

public function Get_last_Sales()
    {

$Role = Auth::user()->roles()->first();
        $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');

$Sales = Sale::with('details', 'client', 'facture')->where('deleted_at', '=', null)
            ->where(function ($query) use ($ShowRecord) {
                if (!$ShowRecord) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            ->orderBy('id', 'desc')
            ->take(5)
            ->get();

foreach ($Sales as $Sale) {

$item['Ref'] = $Sale['Ref'];
            $item['statut'] = $Sale['statut'];
            $item['client_name'] = $Sale['client']['name'];
            $item['GrandTotal'] = $Sale['GrandTotal'];
            $item['paid_amount'] = $Sale['paid_amount'];
            $item['due'] = $Sale['GrandTotal'] - $Sale['paid_amount'];
            $item['payment_status'] = $Sale['payment_statut'];

$data[] = $item;
        }

return response()->json($data);
    }

//-------------------- Get Top 5 Products This YEAR -------------\\

public function Top_Products_Year()
    {

$role = Auth::user()->roles()->first();
        $view_records = Role::findOrFail($role->id)->inRole('record_view');

$products = SaleDetail::join('sales', 'sale_details.sale_id', '=', 'sales.id')
            ->join('products', 'sale_details.product_id', '=', 'products.id')
            ->whereBetween('sale_details.date', [
                Carbon::now()->startOfYear(),
                Carbon::now()->endOfYear(),
            ])
            ->where(function ($query) use ($view_records) {
                if (!$view_records) {
                    return $query->where('sales.user_id', '=', Auth::user()->id);
                }
            })
            ->select(
                DB::raw('products.name as name'),
                DB::raw('sum(sale_details.total) as value'),
            )
            ->groupBy('products.name')
            ->orderBy('value', 'desc')
            ->take(5)
            ->get();

// return $products;

return response()->json($products);
    }

//-------------------- General Report dashboard -------------\\

public function report_dashboard()
    {

$Role = Auth::user()->roles()->first();
        $view_records = Role::findOrFail($Role->id)->inRole('record_view');

// top selling product this month
        $products = SaleDetail::join('sales', 'sale_details.sale_id', '=', 'sales.id')
            ->join('products', 'sale_details.product_id', '=', 'products.id')
            ->join('units', 'products.unit_sale_id', '=', 'units.id')
            ->whereBetween('sale_details.date', [
                Carbon::now()->startOfMonth(),
                Carbon::now()->endOfMonth(),
            ])
            ->where(function ($query) use ($view_records) {
                if (!$view_records) {
                    return $query->where('sales.user_id', '=', Auth::user()->id);
                }
            })
            ->select(
                DB::raw('products.name as name'),
                DB::raw('units.ShortName as unit_product'),
                DB::raw('count(*) as count'),
                DB::raw('sum(total) as total'),
                DB::raw('sum(quantity) as quantity'),
            )
            ->groupBy('products.name')
            ->orderBy('count', 'desc')
            ->take(5)
            ->get();

// Stock Alerts
        $product_warehouse_data = product_warehouse::with('warehouse', 'product' ,'productVariant')
        ->join('products', 'product_warehouse.product_id', '=', 'products.id')
        ->whereRaw('qte <= stock_alert')
        ->where('product_warehouse.deleted_at', null)
        ->take('5')->get();

$stock_alert = [];
        if ($product_warehouse_data->isNotEmpty()) {

foreach ($product_warehouse_data as $product_warehouse) {
                if ($product_warehouse->qte <= $product_warehouse['product']->stock_alert) {
                    if ($product_warehouse->product_variant_id !== null) {
                        $item['code'] = $product_warehouse['productVariant']->name . '-' . $product_warehouse['product']->code;
                    } else {
                        $item['code'] = $product_warehouse['product']->code;
                    }
                    $item['quantity'] = $product_warehouse->qte;
                    $item['name'] = $product_warehouse['product']->name;
                    $item['warehouse'] = $product_warehouse['warehouse']->name;
                    $item['stock_alert'] = $product_warehouse['product']->stock_alert;
                    $stock_alert[] = $item;
                }
            }

}

$data['today_sales'] = Sale::where('deleted_at', '=', null)
        ->where('date', \Carbon\Carbon::today())
        ->where(function ($query) use ($view_records) {
            if (!$view_records) {
                return $query->where('user_id', '=', Auth::user()->id);
            }
        })
        ->get(DB::raw('SUM(GrandTotal)  As sum'))
        ->first()->sum;
        $data['today_sales'] = number_format($data['today_sales'], 2, '.', ',');

$data['return_sales'] = SaleReturn::where('deleted_at', '=', null)
        ->where('date', \Carbon\Carbon::today())
        ->where(function ($query) use ($view_records) {
            if (!$view_records) {
                return $query->where('user_id', '=', Auth::user()->id);
            }
        })
        ->get(DB::raw('SUM(GrandTotal)  As sum'))
        ->first()->sum; 
        $data['return_sales'] = number_format($data['return_sales'], 2, '.', ',');

$data['today_purchases'] = Purchase::where('deleted_at', '=', null)
        ->where('date', \Carbon\Carbon::today())
        ->where(function ($query) use ($view_records) {
            if (!$view_records) {
                return $query->where('user_id', '=', Auth::user()->id);
            }
        })
        ->get(DB::raw('SUM(GrandTotal)  As sum'))
        ->first()->sum;
        $data['today_purchases'] = number_format($data['today_purchases'], 2, '.', ',');

$data['return_purchases'] = PurchaseReturn::where('deleted_at', '=', null)
        ->where('date', \Carbon\Carbon::today())
        ->where(function ($query) use ($view_records) {
            if (!$view_records) {
                return $query->where('user_id', '=', Auth::user()->id);
            }
        })
        ->get(DB::raw('SUM(GrandTotal)  As sum'))
        ->first()->sum;
        $data['return_purchases'] = number_format($data['return_purchases'], 2, '.', ',');

$last_sales = [];

//last sales
        $Sales = Sale::with('details', 'client', 'facture')->where('deleted_at', '=', null)
            ->where(function ($query) use ($view_records) {
                if (!$view_records) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            ->orderBy('id', 'desc')
            ->take(5)
            ->get();

foreach ($Sales as $Sale) {

$item_sale['Ref'] = $Sale['Ref'];
            $item_sale['statut'] = $Sale['statut'];
            $item_sale['client_name'] = $Sale['client']['name'];
            $item_sale['GrandTotal'] = $Sale['GrandTotal'];
            $item_sale['paid_amount'] = $Sale['paid_amount'];
            $item_sale['due'] = $Sale['GrandTotal'] - $Sale['paid_amount'];
            $item_sale['payment_status'] = $Sale['payment_statut'];

$last_sales[] = $item_sale;
        }

return response()->json([
            'products' => $products,
            'stock_alert' => $stock_alert,
            'report' => $data,
            'last_sales' => $last_sales,
        ]);

}

//----------------- Customers Report -----------------------\\

public function Client_Report(request $request)
    {

$this->authorizeForUser($request->user('api'), 'Reports_customers', Client::class);

// How many items do you want to display.
        $perPage = $request->limit;
        $pageStart = \Request::get('page', 1);
        // Start displaying items from this number;
        $offSet = ($pageStart * $perPage) - $perPage;
        $order = $request->SortField;
        $dir = $request->SortType;
        $data = array();

$clients = Client::where('deleted_at', '=', null)
        // Search With Multiple Param
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('name', 'LIKE', "%{$request->search}%")
                        ->orWhere('code', 'LIKE', "%{$request->search}%")
                        ->orWhere('phone', 'LIKE', "%{$request->search}%");
                });
            });

$totalRows = $clients->count();
        if($perPage == "-1"){
            $perPage = $totalRows;
        }
        $clients = $clients->offset($offSet)
            ->limit($perPage)
            ->orderBy($order, $dir)
            ->get();

foreach ($clients as $client) {
            $item['total_sales'] = DB::table('sales')
                ->where('deleted_at', '=', null)
                ->where('client_id', $client->id)
                ->count();

$item['total_amount'] = DB::table('sales')
                ->where('deleted_at', '=', null)
                ->where('client_id', $client->id)
                ->sum('GrandTotal');

$item['total_paid'] = DB::table('sales')
                ->leftjoin('payment_sales', 'sales.id', '=', 'payment_sales.sale_id')
                ->where('sales.deleted_at', '=', null)
                ->where('sales.client_id', $client->id)
                ->sum('payment_sales.montant');

$item['due'] = $item['total_amount'] - $item['total_paid'];
            $item['name'] = $client->name;
            $item['phone'] = $client->phone;
            $item['code'] = $client->code;
            $item['id'] = $client->id;
            $data[] = $item;
        }

return response()->json([
            'report' => $data,
            'totalRows' => $totalRows,
        ]);

}

//----------------- Customers Report By ID-----------------------\\

public function Client_Report_detail(request $request, $id)
    {

$this->authorizeForUser($request->user('api'), 'Reports_customers', Client::class);

$client = Client::where('deleted_at', '=', null)->findOrFail($id);

$data['total_sales'] = DB::table('sales')->where('deleted_at', '=', null)->where('client_id', $id)->count();

$data['total_amount'] = DB::table('sales')->where('deleted_at', '=', null)->where('client_id', $id)
            ->sum('GrandTotal');

$data['total_paid'] = DB::table('sales')
            ->where('sales.deleted_at', '=', null)
            ->join('payment_sales', 'sales.id', '=', 'payment_sales.sale_id')
            ->where('payment_sales.deleted_at', '=', null)
            ->where('sales.client_id', $client->id)->sum('payment_sales.montant');

$data['due'] = $data['total_amount'] - $data['total_paid'];

return response()->json(['report' => $data]);
    }

//----------------- Provider Report By ID-----------------------\\

public function Provider_Report_detail(request $request, $id)
    {

$this->authorizeForUser($request->user('api'), 'Reports_suppliers', Provider::class);

$provider = Provider::where('deleted_at', '=', null)->findOrFail($id);

$data['total_purchase'] = DB::table('purchases')->where('deleted_at', '=', null)->where('provider_id', $id)->count();

$data['total_amount'] = DB::table('purchases')->where('deleted_at', '=', null)->where('provider_id', $id)
            ->sum('GrandTotal');

$data['total_paid'] = DB::table('purchases')
            ->where('purchases.deleted_at', '=', null)
            ->join('payment_purchases', 'purchases.id', '=', 'payment_purchases.purchase_id')
            ->where('payment_purchases.deleted_at', '=', null)
            ->where('purchases.provider_id', $id)->sum('payment_purchases.montant');

$data['due'] = $data['total_amount'] - $data['total_paid'];

return response()->json(['report' => $data]);

}

//-------------------- Get Sales By Clients -------------\\

public function Sales_Client(request $request)
    {

$this->authorizeForUser($request->user('api'), 'Reports_customers', Client::class);
        // How many items do you want to display.
        $perPage = $request->limit;
        $pageStart = \Request::get('page', 1);
        // Start displaying items from this number;
        $offSet = ($pageStart * $perPage) - $perPage;

$Role = Auth::user()->roles()->first();
        $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');

$sales = Sale::where('deleted_at', '=', null)->with('client','warehouse')
            ->where(function ($query) use ($ShowRecord) {
                if (!$ShowRecord) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            ->where('client_id', $request->id)
             // Search With Multiple Param
             ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('Ref', 'LIKE', "%{$request->search}%")
                        ->orWhere('statut', 'LIKE', "%{$request->search}%")
                        ->orWhere('payment_statut', 'like', "%{$request->search}%")
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('warehouse', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        })
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('client', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        });
                });
            });

$totalRows = $sales->count();
        if($perPage == "-1"){
            $perPage = $totalRows;
        }
        $sales = $sales->offset($offSet)
            ->limit($perPage)
            ->orderBy('id', 'desc')
            ->get();

$data = [];
        foreach ($sales as $sale) {
            $item['id'] = $sale->id;
            $item['date'] = $sale->date;
            $item['Ref'] = $sale->Ref;
            $item['warehouse_name'] = $sale['warehouse']->name;
            $item['client_name'] = $sale['client']->name;
            $item['statut'] = $sale->statut;
            $item['GrandTotal'] = $sale->GrandTotal;
            $item['paid_amount'] = $sale->paid_amount;
            $item['due'] = $sale->GrandTotal - $sale->paid_amount;
            $item['payment_status'] = $sale->payment_statut;
            $item['shipping_status'] = $sale->shipping_status;
            
            $data[] = $item;
        }
        return response()->json([
            'totalRows' => $totalRows,
            'sales' => $data,
        ]);

}

//-------------------- Get Payments By Clients -------------\\

public function Payments_Client(request $request)
    {

$Role = Auth::user()->roles()->first();
        $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');

$payments = DB::table('payment_sales')
            ->where(function ($query) use ($ShowRecord) {
                if (!$ShowRecord) {
                    return $query->where('payment_sales.user_id', '=', Auth::user()->id);
                }
            })
            ->where('payment_sales.deleted_at', '=', null)
            ->join('sales', 'payment_sales.sale_id', '=', 'sales.id')
            ->where('sales.client_id', $request->id)
             // Search With Multiple Param
             ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('payment_sales.Ref', 'LIKE', "%{$request->search}%")
                        ->orWhere('payment_sales.date', 'LIKE', "%{$request->search}%")
                        ->orWhere('payment_sales.Reglement', 'LIKE', "%{$request->search}%");
                });
            })
            ->select(
                'payment_sales.date', 'payment_sales.Ref AS Ref', 'sales.Ref AS Sale_Ref',
                'payment_sales.Reglement', 'payment_sales.montant'
            );

$totalRows = $payments->count();
        if($perPage == "-1"){
            $perPage = $totalRows;
        }
        $payments = $payments->offset($offSet)
            ->limit($perPage)
            ->orderBy('payment_sales.id', 'desc')
            ->get();

return response()->json([
            'payments' => $payments,
            'totalRows' => $totalRows,
        ]);

}

//-------------------- Get Quotations By Clients -------------\\

public function Quotations_Client(request $request)
    {

$Role = Auth::user()->roles()->first();
        $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');
        $data = array();
        
        $Quotations = Quotation::with('client', 'warehouse')
            ->where('deleted_at', '=', null)
            ->where('client_id', $request->id)
            ->where(function ($query) use ($ShowRecord) {
                if (!$ShowRecord) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            //Search With Multiple Param
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('Ref', 'LIKE', "%{$request->search}%")
                        ->orWhere('statut', 'LIKE', "%{$request->search}%")
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('warehouse', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        })
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('client', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        });
                });
            });

$totalRows = $Quotations->count();
        if($perPage == "-1"){
            $perPage = $totalRows;
        }
        $Quotations = $Quotations->offset($offSet)
            ->limit($perPage)
            ->orderBy('id', 'desc')
            ->get();

foreach ($Quotations as $Quotation) {

$item['id'] = $Quotation->id;
            $item['date'] = $Quotation->date;
            $item['Ref'] = $Quotation->Ref;
            $item['statut'] = $Quotation->statut;
            $item['warehouse_name'] = $Quotation['warehouse']->name;
            $item['client_name'] = $Quotation['client']->name;
            $item['GrandTotal'] = $Quotation->GrandTotal;

$data[] = $item;
        }

return response()->json([
            'quotations' => $data,
            'totalRows' => $totalRows,
        ]);
    }

//-------------------- Get Returns By Client -------------\\

public function Returns_Client(request $request)
    {

//  Check If User Has Permission Show All Records
        $Role = Auth::user()->roles()->first();
        $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');

$SaleReturn = SaleReturn::where('deleted_at', '=', null)->with('client','warehouse')
            ->where('client_id', $request->id)
            ->where(function ($query) use ($ShowRecord) {
                if (!$ShowRecord) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
             // Search With Multiple Param
             ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('Ref', 'LIKE', "%{$request->search}%")
                        ->orWhere('statut', 'LIKE', "%{$request->search}%")
                        ->orWhere('payment_statut', 'like', "$request->search")
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('client', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        })
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('warehouse', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        });
                });
            });

$totalRows = $SaleReturn->count();
        if($perPage == "-1"){
            $perPage = $totalRows;
        }
        $SaleReturn = $SaleReturn->offset($offSet)
            ->limit($perPage)
            ->orderBy('id', 'desc')
            ->get();

foreach ($SaleReturn as $Sale_Return) {
            $item['id'] = $Sale_Return->id;
            $item['Ref'] = $Sale_Return->Ref;
            $item['statut'] = $Sale_Return->statut;
            $item['client_name'] = $Sale_Return['client']->name;
            $item['warehouse_name'] = $Sale_Return['warehouse']->name;
            $item['GrandTotal'] = $Sale_Return->GrandTotal;
            $item['paid_amount'] = $Sale_Return->paid_amount;
            $item['due'] = $Sale_Return->GrandTotal - $Sale_Return->paid_amount;
            $item['payment_status'] = $Sale_Return->payment_statut;

$data[] = $item;
        }

return response()->json([
            'totalRows' => $totalRows,
            'returns_customer' => $data,
        ]);
    }

//------------- Show Report Purchases ----------\\

public function Report_Purchases(request $request)
    {
        $this->authorizeForUser($request->user('api'), 'ReportPurchases', Purchase::class);
        // How many items do you want to display.
        $perPage = $request->limit;
        $pageStart = \Request::get('page', 1);
        // Start displaying items from this number;
        $offSet = ($pageStart * $perPage) - $perPage;
        $order = $request->SortField;
        $dir = $request->SortType;
        $helpers = new helpers();
        // Filter fields With Params to retrieve
        $param = array(0 => 'like', 1 => 'like', 2 => '=', 3 => 'like');
        $columns = array(0 => 'Ref', 1 => 'statut', 2 => 'provider_id', 3 => 'payment_statut');
        $data = array();
        $total = 0;

$Purchases = Purchase::select('purchases.*')
            ->with('facture', 'provider', 'warehouse')
            ->join('providers', 'purchases.provider_id', '=', 'providers.id')
            ->where('purchases.deleted_at', '=', null)
            ->whereBetween('purchases.date', array($request->from, $request->to));
            
        //  Check If User Has Permission Show All Records
        $Purchases = $helpers->Show_Records($Purchases);
        //Multiple Filter
        $Filtred = $helpers->filter($Purchases, $columns, $param, $request)
        // Search With Multiple Param
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('purchases.Ref', 'LIKE', "%{$request->search}%")
                        ->orWhere('purchases.statut', 'LIKE', "%{$request->search}%")
                        ->orWhere('purchases.GrandTotal', $request->search)
                        ->orWhere('purchases.payment_statut', 'like', "$request->search")
                        ->orWhere('providers.name', 'LIKE', "%{$request->search}%");
                });
            });

$totalRows = $Filtred->count();
        if($perPage == "-1"){
            $perPage = $totalRows;
        }
        $Purchases = $Filtred->offset($offSet)
            ->limit($perPage)
            ->orderBy('purchases.' . $order, $dir)
            ->get();

foreach ($Purchases as $Purchase) {

$item['id'] = $Purchase->id;
            $item['date'] = $Purchase->date;
            $item['Ref'] = $Purchase->Ref;
            $item['warehouse_name'] = $Purchase['warehouse']->name;
            $item['discount'] = $Purchase->discount;
            $item['shipping'] = $Purchase->shipping;
            $item['statut'] = $Purchase->statut;
            $item['provider_name'] = $Purchase['provider']->name;
            $item['provider_email'] = $Purchase['provider']->email;
            $item['provider_tele'] = $Purchase['provider']->phone;
            $item['provider_code'] = $Purchase['provider']->code;
            $item['provider_adr'] = $Purchase['provider']->adresse;
            $item['GrandTotal'] = $Purchase['GrandTotal'];
            $item['paid_amount'] = $Purchase['paid_amount'];
            $item['due'] = $Purchase['GrandTotal'] - $Purchase['paid_amount'];
            $item['payment_status'] = $Purchase['payment_statut'];

$data[] = $item;
        }

$suppliers = provider::where('deleted_at', '=', null)->get(['id', 'name']);
        return response()->json([
            'totalRows' => $totalRows,
            'purchases' => $data,
            'suppliers' => $suppliers,
        ]);
    }

//------------- Show Report SALES -----------\\

public function Report_Sales(request $request)
    {
        $this->authorizeForUser($request->user('api'), 'Reports_sales', Sale::class);
        // How many items do you want to display.
        $perPage = $request->limit;
        $pageStart = \Request::get('page', 1);
        // Start displaying items from this number;
        $offSet = ($pageStart * $perPage) - $perPage;
        $order = $request->SortField;
        $dir = $request->SortType;
        $helpers = new helpers();
        // Filter fields With Params to retrieve
        $param = array(0 => 'like', 1 => 'like', 2 => '=', 3 => 'like');
        $columns = array(0 => 'Ref', 1 => 'statut', 2 => 'client_id', 3 => 'payment_statut');
        $data = array();

$Sales = Sale::select('sales.*')
            ->with('facture', 'client', 'warehouse')
            ->join('clients', 'sales.client_id', '=', 'clients.id')
            ->where('sales.deleted_at', '=', null)
            ->whereBetween('sales.date', array($request->from, $request->to));

//  Check If User Has Permission Show All Records
        $Sales = $helpers->Show_Records($Sales);
        //Multiple Filter
        $Filtred = $helpers->filter($Sales, $columns, $param, $request)
        // Search With Multiple Param
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('sales.Ref', 'LIKE', "%{$request->search}%")
                        ->orWhere('sales.statut', 'LIKE', "%{$request->search}%")
                        ->orWhere('sales.GrandTotal', $request->search)
                        ->orWhere('sales.payment_statut', 'like', "$request->search")
                        ->orWhere('clients.name', 'LIKE', "%{$request->search}%");
                });
            });

$totalRows = $Filtred->count();
        if($perPage == "-1"){
            $perPage = $totalRows;
        }
        $Sales = $Filtred->offset($offSet)
            ->limit($perPage)
            ->orderBy('sales.' . $order, $dir)
            ->get();

foreach ($Sales as $Sale) {

$item['id'] = $Sale['id'];
            $item['date'] = $Sale['date'];
            $item['Ref'] = $Sale['Ref'];
            $item['statut'] = $Sale['statut'];
            $item['discount'] = $Sale['discount'];
            $item['shipping'] = $Sale['shipping'];
            $item['warehouse_name'] = $Sale['warehouse']['name'];
            $item['client_name'] = $Sale['client']['name'];
            $item['client_email'] = $Sale['client']['email'];
            $item['client_tele'] = $Sale['client']['phone'];
            $item['client_code'] = $Sale['client']['code'];
            $item['client_adr'] = $Sale['client']['adresse'];
            $item['GrandTotal'] = $Sale['GrandTotal'];
            $item['paid_amount'] = $Sale['paid_amount'];
            $item['due'] = $Sale['GrandTotal'] - $Sale['paid_amount'];
            $item['payment_status'] = $Sale['payment_statut'];

$data[] = $item;
        }

$customers = client::where('deleted_at', '=', null)->get(['id', 'name']);
        return response()->json(['totalRows' => $totalRows, 'sales' => $data, 'customers' => $customers]);
    }

//----------------- Providers Report -----------------------\\

public function Providers_Report(request $request)
    {

$this->authorizeForUser($request->user('api'), 'Reports_suppliers', Provider::class);
        // How many items do you want to display.
        $perPage = $request->limit;
        $pageStart = \Request::get('page', 1);
        // Start displaying items from this number;
        $offSet = ($pageStart * $perPage) - $perPage;
        $order = $request->SortField;
        $dir = $request->SortType;
        $data = array();

$providers = Provider::where('deleted_at', '=', null)
        // Search With Multiple Param
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('name', 'LIKE', "%{$request->search}%")
                        ->orWhere('code', 'LIKE', "%{$request->search}%")
                        ->orWhere('phone', 'LIKE', "%{$request->search}%");
                });
            });

$totalRows = $providers->count();
        if($perPage == "-1"){
            $perPage = $totalRows;
        }
        $providers = $providers->offset($offSet)
            ->limit($perPage)
            ->orderBy($order, $dir)
            ->get();

foreach ($providers as $provider) {
            $item['total_purchase'] = DB::table('purchases')
                ->where('deleted_at', '=', null)
                ->where('provider_id', $provider->id)
                ->count();

$item['total_amount'] = DB::table('purchases')
                ->where('deleted_at', '=', null)
                ->where('provider_id', $provider->id)
                ->sum('GrandTotal');

$item['total_paid'] = DB::table('purchases')
                ->leftjoin('payment_purchases', 'purchases.id', '=', 'payment_purchases.purchase_id')
                ->where('purchases.provider_id', $provider->id)
                ->where('purchases.deleted_at', '=', null)
                ->sum('payment_purchases.montant');

$item['due'] = $item['total_amount'] - $item['total_paid'];
            $item['name'] = $provider->name;
            $item['phone'] = $provider->phone;
            $item['code'] = $provider->code;
            $item['id'] = $provider->id;
            $data[] = $item;
        }

return response()->json([
            'report' => $data,
            'totalRows' => $totalRows,
        ]);

}

//-------------------- Get Purchases By Provider -------------\\

public function Purchases_Provider(request $request)
    {

$Role = Auth::user()->roles()->first();
        $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');

$purchases = Purchase::where('deleted_at', '=', null)
            ->with('provider','warehouse')
            ->where('provider_id', $request->id)
            ->where(function ($query) use ($ShowRecord) {
                if (!$ShowRecord) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
             // Search With Multiple Param
             ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('Ref', 'LIKE', "%{$request->search}%")
                        ->orWhere('statut', 'LIKE', "%{$request->search}%")
                        ->orWhere('payment_statut', 'like', "$request->search")
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('provider', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        })
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('warehouse', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        });
                });
            });

$totalRows = $purchases->count();
        if($perPage == "-1"){
            $perPage = $totalRows;
        }
        $purchases = $purchases->offset($offSet)
            ->limit($perPage)
            ->orderBy('id', 'desc')
            ->get();

foreach ($purchases as $purchase) {
            $item['id'] = $purchase->id;
            $item['Ref'] = $purchase->Ref;
            $item['warehouse_name'] = $purchase['warehouse']->name;
            $item['provider_name'] = $purchase['provider']->name;
            $item['statut'] = $purchase->statut;
            $item['GrandTotal'] = $purchase->GrandTotal;
            $item['paid_amount'] = $purchase->paid_amount;
            $item['due'] = $purchase->GrandTotal - $purchase->paid_amount;
            $item['payment_status'] = $purchase->payment_statut;

$data[] = $item;
        }

return response()->json([
            'totalRows' => $totalRows,
            'purchases' => $data,
        ]);

}

//-------------------- Get Payments By Provider -------------\\

public function Payments_Provider(request $request)
    {

$this->authorizeForUser($request->user('api'), 'Reports_suppliers', Provider::class);

$Role = Auth::user()->roles()->first();
        $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');

$payments = DB::table('payment_purchases')
            ->where(function ($query) use ($ShowRecord) {
                if (!$ShowRecord) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            ->where('payment_purchases.deleted_at', '=', null)
            ->join('purchases', 'payment_purchases.purchase_id', '=', 'purchases.id')
            ->where('purchases.provider_id', $request->id)
             // Search With Multiple Param
             ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('payment_purchases.Ref', 'LIKE', "%{$request->search}%")
                        ->orWhere('payment_purchases.date', 'LIKE', "%{$request->search}%")
                        ->orWhere('payment_purchases.Reglement', 'LIKE', "%{$request->search}%");
                });
            })
            ->select(
                'payment_purchases.date', 'payment_purchases.Ref AS Ref', 'purchases.Ref AS purchase_Ref',
                'payment_purchases.Reglement', 'payment_purchases.montant'
            );

$totalRows = $payments->count();
        if($perPage == "-1"){
            $perPage = $totalRows;
        }
        $payments = $payments->offset($offSet)
            ->limit($perPage)
            ->orderBy('payment_purchases.id', 'desc')
            ->get();

return response()->json([
            'payments' => $payments,
            'totalRows' => $totalRows,
        ]);
    }

//-------------------- Get Returns By Providers -------------\\

public function Returns_Provider(request $request)
    {

$this->authorizeForUser($request->user('api'), 'Reports_suppliers', Provider::class);

$Role = Auth::user()->roles()->first();
        $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');

$PurchaseReturn = PurchaseReturn::where('deleted_at', '=', null)
            ->with('provider','warehouse')
            ->where('provider_id', $request->id)
            ->where(function ($query) use ($ShowRecord) {
                if (!$ShowRecord) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            // Search With Multiple Param
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('Ref', 'LIKE', "%{$request->search}%")
                        ->orWhere('statut', 'LIKE', "%{$request->search}%")
                        ->orWhere('payment_statut', 'like', "$request->search")
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('provider', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        })
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('warehouse', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        });
                });
            });

$totalRows = $PurchaseReturn->count();
        if($perPage == "-1"){
            $perPage = $totalRows;
        }
        $PurchaseReturn = $PurchaseReturn->offset($offSet)
            ->limit($perPage)
            ->orderBy('id', 'desc')
            ->get();

foreach ($PurchaseReturn as $Purchase_Return) {
            $item['id'] = $Purchase_Return->id;
            $item['Ref'] = $Purchase_Return->Ref;
            $item['statut'] = $Purchase_Return->statut;
            $item['provider_name'] = $Purchase_Return['provider']->name;
            $item['warehouse_name'] = $Purchase_Return['warehouse']->name;
            $item['GrandTotal'] = $Purchase_Return->GrandTotal;
            $item['paid_amount'] = $Purchase_Return->paid_amount;
            $item['due'] = $Purchase_Return->GrandTotal - $Purchase_Return->paid_amount;
            $item['payment_status'] = $Purchase_Return->payment_statut;

$data[] = $item;
        }

return response()->json([
            'totalRows' => $totalRows,
            'returns_supplier' => $data,
        ]);

}

//-------------------- Top 5 Suppliers -------------\\

public function ToProviders(Request $request)
    {

$this->authorizeForUser($request->user('api'), 'Reports_suppliers', Provider::class);

$results = DB::table('purchases')->where('purchases.deleted_at', '=', null)
            ->join('providers', 'purchases.provider_id', '=', 'providers.id')
            ->select(DB::raw('providers.name'), DB::raw('count(*) as count'))
            ->groupBy('providers.name')
            ->orderBy('count', 'desc')
            ->take(5)
            ->get();

$data = [];
        $providers = [];
        foreach ($results as $result) {
            $providers[] = $result->name;
            $data[] = $result->count;
        }
        $data[] = 0;
        return response()->json(['providers' => $providers, 'data' => $data]);
    }

//----------------- Warehouse Report By ID-----------------------\\

public function Warehouse_Report(request $request)
    {

$this->authorizeForUser($request->user('api'), 'WarehouseStock', Product::class);

$data['sales'] = Sale::where('deleted_at', '=', null)
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('warehouse_id'), function ($query) use ($request) {
                    return $query->where('warehouse_id', $request->warehouse_id);
                });
            })->count();

$data['purchases'] = Purchase::where('deleted_at', '=', null)
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('warehouse_id'), function ($query) use ($request) {
                    return $query->where('warehouse_id', $request->warehouse_id);
                });
            })->count();

$data['ReturnPurchase'] = PurchaseReturn::where('deleted_at', '=', null)
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('warehouse_id'), function ($query) use ($request) {
                    return $query->where('warehouse_id', $request->warehouse_id);
                });
            })->count();

$data['ReturnSale'] = SaleReturn::where('deleted_at', '=', null)
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('warehouse_id'), function ($query) use ($request) {
                    return $query->where('warehouse_id', $request->warehouse_id);
                });
            })->count();

//get warehouses assigned to user
        $user_auth = auth()->user();
        if($user_auth->is_all_warehouses){
            $warehouses = Warehouse::where('deleted_at', '=', null)->get(['id', 'name']);
        }else{
            $warehouses_id = UserWarehouse::where('user_id', $user_auth->id)->pluck('warehouse_id')->toArray();
            $warehouses = Warehouse::where('deleted_at', '=', null)->whereIn('id', $warehouses_id)->get(['id', 'name']);
        }

return response()->json([
            'data' => $data,
            'warehouses' => $warehouses,
        ], 200);

}

//-------------------- Get Sales By Warehouse -------------\\

public function Sales_Warehouse(request $request)
    {

$this->authorizeForUser($request->user('api'), 'WarehouseStock', Product::class);
        // How many items do you want to display.
        $perPage = $request->limit;
        $pageStart = \Request::get('page', 1);
        // Start displaying items from this number;
        $offSet = ($pageStart * $perPage) - $perPage;
        $data = [];

$Role = Auth::user()->roles()->first();
        $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');

$sales = Sale::where('deleted_at', '=', null)->with('client','warehouse')
            ->where(function ($query) use ($ShowRecord) {
                if (!$ShowRecord) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('warehouse_id'), function ($query) use ($request) {
                    return $query->where('warehouse_id', $request->warehouse_id);
                });
            })
        // Search With Multiple Param
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('Ref', 'LIKE', "%{$request->search}%")
                        ->orWhere('statut', 'LIKE', "%{$request->search}%")
                        ->orWhere('GrandTotal', $request->search)
                        ->orWhere('payment_statut', 'like', "$request->search")
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('client', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        });
                });
            });

foreach ($sales as $sale) {
            $item['id'] = $sale->id;
            $item['date'] = $sale->date;
            $item['Ref'] = $sale->Ref;
            $item['client_name'] = $sale['client']->name;
            $item['warehouse_name'] = $sale['warehouse']->name;
            $item['statut'] = $sale->statut;
            $item['GrandTotal'] = $sale->GrandTotal;
            $item['paid_amount'] = $sale->paid_amount;
            $item['due'] = $sale->GrandTotal - $sale->paid_amount;
            $item['payment_status'] = $sale->payment_statut;
            $item['shipping_status'] = $sale->shipping_status;

$data[] = $item;
        }
        return response()->json([
            'totalRows' => $totalRows,
            'sales' => $data,
        ]);

}

//-------------------- Get Quotations By Warehouse -------------\\

public function Quotations_Warehouse(request $request)
    {

$Role = Auth::user()->roles()->first();
        $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');

$Quotations = Quotation::where('deleted_at', '=', null)
            ->with('client','warehouse')
            ->where(function ($query) use ($ShowRecord) {
                if (!$ShowRecord) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('warehouse_id'), function ($query) use ($request) {
                    return $query->where('warehouse_id', $request->warehouse_id);
                });
            })
        //Search With Multiple Param
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('Ref', 'LIKE', "%{$request->search}%")
                        ->orWhere('statut', 'LIKE', "%{$request->search}%")
                        ->orWhere('GrandTotal', $request->search)
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('client', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        });
                });
            });
        $totalRows = $Quotations->count();
        if($perPage == "-1"){
            $perPage = $totalRows;
        }
        $Quotations = $Quotations->offset($offSet)
            ->limit($perPage)
            ->orderBy('id', 'desc')
            ->get();

foreach ($Quotations as $Quotation) {
            $item['id'] = $Quotation->id;
            $item['date'] = $Quotation->date;
            $item['Ref'] = $Quotation->Ref;
            $item['warehouse_name'] = $Quotation['warehouse']->name;
            $item['client_name'] = $Quotation['client']->name;
            $item['statut'] = $Quotation->statut;
            $item['GrandTotal'] = $Quotation->GrandTotal;

$data[] = $item;
        }

return response()->json([
            'quotations' => $data,
            'totalRows' => $totalRows,
        ]);
    }

//-------------------- Get Returns Sale By Warehouse -------------\\

public function Returns_Sale_Warehouse(request $request)
    {

//  Check If User Has Permission Show All Records
        $Role = Auth::user()->roles()->first();
        $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');

$SaleReturn = SaleReturn::where('deleted_at', '=', null)
            ->with('client','warehouse')
            ->where(function ($query) use ($ShowRecord) {
                if (!$ShowRecord) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('warehouse_id'), function ($query) use ($request) {
                    return $query->where('warehouse_id', $request->warehouse_id);
                });
            })
        //Search With Multiple Param
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('Ref', 'LIKE', "%{$request->search}%")
                        ->orWhere('statut', 'LIKE', "%{$request->search}%")
                        ->orWhere('GrandTotal', $request->search)
                        ->orWhere('payment_statut', 'like', "$request->search")
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('client', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        });
                });
            });

foreach ($SaleReturn as $Sale_Return) {
            $item['id'] = $Sale_Return->id;
            $item['warehouse_name'] = $Sale_Return['warehouse']->name;
            $item['Ref'] = $Sale_Return->Ref;
            $item['statut'] = $Sale_Return->statut;
            $item['client_name'] = $Sale_Return['client']->name;
            $item['GrandTotal'] = $Sale_Return->GrandTotal;
            $item['paid_amount'] = $Sale_Return->paid_amount;
            $item['due'] = $Sale_Return->GrandTotal - $Sale_Return->paid_amount;
            $item['payment_status'] = $Sale_Return->payment_statut;

$data[] = $item;
        }

return response()->json([
            'totalRows' => $totalRows,
            'returns_sale' => $data,
        ]);
    }

//-------------------- Get Returns Purchase By Warehouse -------------\\

public function Returns_Purchase_Warehouse(request $request)
    {

//  Check If User Has Permission Show All Records
        $Role = Auth::user()->roles()->first();
        $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');

$PurchaseReturn = PurchaseReturn::where('deleted_at', '=', null)
            ->with('provider','warehouse')
            ->where(function ($query) use ($ShowRecord) {
                if (!$ShowRecord) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('warehouse_id'), function ($query) use ($request) {
                    return $query->where('warehouse_id', $request->warehouse_id);
                });
            })
        //Search With Multiple Param
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('Ref', 'LIKE', "%{$request->search}%")
                        ->orWhere('statut', 'LIKE', "%{$request->search}%")
                        ->orWhere('GrandTotal', $request->search)
                        ->orWhere('payment_statut', 'like', "$request->search")
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('provider', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        });
                });
            });

foreach ($PurchaseReturn as $Purchase_Return) {
            $item['id'] = $Purchase_Return->id;
            $item['Ref'] = $Purchase_Return->Ref;
            $item['statut'] = $Purchase_Return->statut;
            $item['warehouse_name'] = $Purchase_Return['warehouse']->name;
            $item['provider_name'] = $Purchase_Return['provider']->name;
            $item['GrandTotal'] = $Purchase_Return->GrandTotal;
            $item['paid_amount'] = $Purchase_Return->paid_amount;
            $item['due'] = $Purchase_Return->GrandTotal - $Purchase_Return->paid_amount;
            $item['payment_status'] = $Purchase_Return->payment_statut;

$data[] = $item;
        }

return response()->json([
            'totalRows' => $totalRows,
            'returns_purchase' => $data,
        ]);
    }

//-------------------- Get Expenses By Warehouse -------------\\

public function Expenses_Warehouse(request $request)
    {

//  Check If User Has Permission Show All Records
        $Role = Auth::user()->roles()->first();
        $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');

$Expenses = Expense::where('deleted_at', '=', null)
            ->with('expense_category','warehouse')
            ->where(function ($query) use ($ShowRecord) {
                if (!$ShowRecord) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('warehouse_id'), function ($query) use ($request) {
                    return $query->where('warehouse_id', $request->warehouse_id);
                });
            })
        //Search With Multiple Param
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('Ref', 'LIKE', "%{$request->search}%")
                        ->orWhere('date', 'LIKE', "%{$request->search}%")
                        ->orWhere('details', 'LIKE', "%{$request->search}%")
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('expense_category', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        });
                });
            });

$totalRows = $Expenses->count();
        if($perPage == "-1"){
            $perPage = $totalRows;
        }
        $Expenses = $Expenses->offset($offSet)
            ->limit($perPage)
            ->orderBy('id', 'desc')
            ->get();

foreach ($Expenses as $Expense) {

$item['date'] = $Expense->date;
            $item['Ref'] = $Expense->Ref;
            $item['details'] = $Expense->details;
            $item['amount'] = $Expense->amount;
            $item['warehouse_name'] = $Expense['warehouse']->name;
            $item['category_name'] = $Expense['expense_category']->name;
            $data[] = $item;
        }

return response()->json([
            'totalRows' => $totalRows,
            'expenses' => $data,
        ]);
    }

//----------------- Warhouse Count Stock -----------------------\\

public function Warhouse_Count_Stock(Request $request)
    {
        $this->authorizeForUser($request->user('api'), 'WarehouseStock', Product::class);

$stock_count = product_warehouse::join('products', 'product_warehouse.product_id', '=', 'products.id')
            ->join('warehouses', 'product_warehouse.warehouse_id', '=', 'warehouses.id')
            ->where('product_warehouse.deleted_at', '=', null)
            ->select(
                DB::raw("count(DISTINCT products.id) as value"),
                DB::raw("warehouses.name as name"),
                DB::raw('(IFNULL(SUM(qte),0)) AS value1'),
            )
            ->where('qte', '>', 0)
            ->groupBy('warehouses.name')
            ->get();

$stock_value = product_warehouse::join('products', 'product_warehouse.product_id', '=', 'products.id')
            ->join('warehouses', 'product_warehouse.warehouse_id', '=', 'warehouses.id')
            ->where('product_warehouse.deleted_at', '=', null)
            ->select(
                DB::raw("SUM(products.price) as price"),
                DB::raw("SUM(products.cost) as cost"),
                DB::raw("warehouses.name as name"),
            )
            ->where('qte', '>', 0)
            ->groupBy('warehouses.name')
            ->get();

$data = [];
        foreach ($stock_value as $key => $value) {
            $item['name'] = $value->name;
            $item['value'] = $value->price;
            $item['value1'] = $value->cost;
            // $item['value2'] = $value->price - $value->cost;
            $data[] = $item;
        }

return response()->json([
            'stock_count' => $stock_count,
            'stock_value' => $data,
            'warehouses' => $warehouses,
        ]);

}

//-------------- Count  Product Quantity Alerts ---------------\\

public function count_quantity_alert(request $request)
    {

$products_alerts = product_warehouse::join('products', 'product_warehouse.product_id', '=', 'products.id')
            ->whereRaw('qte <= stock_alert')
            ->count();

return response()->json($products_alerts);
    }

//-----------------Profit And Loss ---------------------------\\

public function ProfitAndLoss(request $request)
    {

$this->authorizeForUser($request->user('api'), 'Reports_profit', Client::class);

$role = Auth::user()->roles()->first();
        $view_records = Role::findOrFail($role->id)->inRole('record_view');

$data = [];

$item['sales'] = Sale::where('deleted_at', '=', null)->whereBetween('date', array($request->from, $request->to))
            ->select(
                DB::raw('SUM(GrandTotal) AS sum'),
                DB::raw("count(*) as nmbr")
            )->first();

$item['purchases'] = Purchase::where('deleted_at', '=', null)->whereBetween('date', array($request->from, $request->to))
            ->select(
                DB::raw('SUM(GrandTotal) AS sum'),
                DB::raw("count(*) as nmbr")
            )->first();

$item['returns_sales'] = SaleReturn::where('deleted_at', '=', null)->whereBetween('date', array($request->from, $request->to))
            ->select(
                DB::raw('SUM(GrandTotal) AS sum'),
                DB::raw("count(*) as nmbr")
            )->first();

$item['returns_purchases'] = PurchaseReturn::where('deleted_at', '=', null)->whereBetween('date', array($request->from, $request->to))
            ->select(
                DB::raw('SUM(GrandTotal) AS sum'),
                DB::raw("count(*) as nmbr")
            )->first();

$item['paiement_sales'] = PaymentSale::whereBetween('date', array($request->from, $request->to))
            ->select(
                DB::raw('SUM(montant) AS sum')
            )->first();

$item['PaymentSaleReturns'] = PaymentSaleReturns::whereBetween('date', array($request->from, $request->to))
            ->select(
                DB::raw('SUM(montant) AS sum')
            )->first();

$item['PaymentPurchaseReturns'] = PaymentPurchaseReturns::whereBetween('date', array($request->from, $request->to))
            ->select(
                DB::raw('SUM(montant) AS sum')
            )->first();

$item['paiement_purchases'] = PaymentPurchase::whereBetween('date', array($request->from, $request->to))
            ->select(
                DB::raw('SUM(montant) AS sum')
            )->first();

$item['expenses'] = Expense::whereBetween('date', array($request->from, $request->to))
            ->where('deleted_at', '=', null)
            ->select(
                DB::raw('SUM(amount) AS sum')
            )->first();

$item['return_sales'] = SaleReturn::where('deleted_at', '=', null)
            ->whereBetween('date', array($request->from, $request->to))
            ->where(function ($query) use ($view_records) {
                if (!$view_records) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            ->get(DB::raw('SUM(GrandTotal)  As sum'))
            ->first()->sum; 
        
        $item['today_purchases'] = Purchase::where('deleted_at', '=', null)
            ->whereBetween('date', array($request->from, $request->to))
            ->where(function ($query) use ($view_records) {
                if (!$view_records) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            ->get(DB::raw('SUM(GrandTotal)  As sum'))
            ->first()->sum;
    
        $item['purchases_return'] = PurchaseReturn::where('deleted_at', '=', null)
            ->whereBetween('date', array($request->from, $request->to))
            ->where(function ($query) use ($view_records) {
                if (!$view_records) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            ->get(DB::raw('SUM(GrandTotal)  As sum'))
            ->first()->sum;

//calcul profit
        $product_sale_data = Sale::join('sale_details' , 'sales.id', '=', 'sale_details.sale_id')
        ->select(DB::raw('sale_details.product_id , sum(sale_details.quantity) as sold_qty , sum(sale_details.total) as sold_amount'))
        ->where('sales.deleted_at', '=', null)
        ->where(function ($query) use ($view_records) {
            if (!$view_records) {
                return $query->where('sales.user_id', '=', Auth::user()->id);
            }
        })
        ->whereBetween('sales.date', array($request->from, $request->to))
        ->groupBy('sale_details.product_id')
        ->get();

$product_revenue = 0;
        $product_cost = 0;
        $profit = 0;

foreach($product_sale_data as $key => $product_sale){

$product_purchase_data = PurchaseDetail::where('product_id' , $product_sale->product_id)->get();
            $product_cost_default = Product::findOrFail($product_sale->product_id);

$purchased_qty = 0;
            $purchased_amount = 0;  
            $qty_adjust = 0;   
            $sold_qty = $product_sale->sold_qty;
            $product_revenue += $product_sale->sold_amount;

foreach ($product_purchase_data as $key => $product_purchase) {
                $purchased_qty += $product_purchase->quantity;
                $purchased_amount += $product_purchase->total;
                if($purchased_qty >= $sold_qty){
                    $qty_diff = $purchased_qty - $sold_qty;
                    $unit_cost = $product_purchase->total / $product_purchase->quantity;
                    $purchased_amount -= ($qty_diff * $unit_cost);
                    break;

}
            }

if ($product_purchase_data->isEmpty()) {
                $unit_cost = ($sold_qty * $product_cost_default->cost) / $sold_qty;
                $purchased_amount += ($sold_qty * $product_cost_default->cost);
            }
            
            $product_cost += $purchased_amount;

}

$item['product_cost'] = $product_cost;
        $item['profit'] = $item['sales']['sum'] - $product_cost;
        $item['payment_received'] = $item['paiement_sales']['sum'] + $item['PaymentPurchaseReturns']['sum'];
        $item['payment_sent'] = $item['paiement_purchases']['sum'] + $item['PaymentSaleReturns']['sum'] + $item['expenses']['sum'];
        $item['paiement_net'] = $item['payment_received'] - $item['payment_sent'];

return response()->json(['data' => $item]);
        
    }

//-------------------- Fomrmat units -------------\\

public function formatSizeUnits($bytes)
    {
        if ($bytes >= 1073741824) {
            $bytes = number_format($bytes / 1073741824, 2) . ' GB';
        } elseif ($bytes >= 1048576) {
            $bytes = number_format($bytes / 1048576, 2) . ' MB';
        } elseif ($bytes >= 1024) {
            $bytes = number_format($bytes / 1024, 2) . ' KB';
        } elseif ($bytes > 1) {
            $bytes = $bytes . ' bytes';
        } elseif ($bytes == 1) {
            $bytes = $bytes . ' byte';
        } else {
            $bytes = '0 bytes';
        }

return $bytes;
    }

//-------------------- Backup Databse -------------\\

public function GetBackup(Request $request)
    {

$this->authorizeForUser($request->user('api'), 'backup', User::class);

$data = [];
        $id = 0;
        foreach (glob(storage_path() . '/app/public/backup/*') as $filename) {
            $item['id'] = $id += 1;
            $item['date'] = basename($filename);
            $size = $this->formatSizeUnits(filesize($filename));
            $item['size'] = $size;

$data[] = $item;
        }
        $totalRows = sizeof($data);

return response()->json([
            'backups' => $data,
            'totalRows' => $totalRows,
        ]);

}

//-------------------- Generate Databse -------------\\

public function GenerateBackup(Request $request)
    {

$this->authorizeForUser($request->user('api'), 'backup', User::class);

Artisan::call('database:backup');

return response()->json('Generate complete success');
    }

//-------------------- Delete Databse -------------\\

public function DeleteBackup(Request $request, $name)
    {

$this->authorizeForUser($request->user('api'), 'backup', User::class);

foreach (glob(storage_path() . '/app/public/backup/*') as $filename) {
            $path = storage_path() . '/app/public/backup/' . basename($name);
            if (file_exists($path)) {
                @unlink($path);
            }
        }
    }

//-------------------- report_top_products -------------\\

public function report_top_products(request $request)
     {
 
        $this->authorizeForUser($request->user('api'), 'Top_products', Product::class);

$Role = Auth::user()->roles()->first();
        $view_records = Role::findOrFail($Role->id)->inRole('record_view');
        // How many items do you want to display.
        $perPage = $request->limit;
        $pageStart = \Request::get('page', 1);
        // Start displaying items from this number;
        $offSet = ($pageStart * $perPage) - $perPage;

// top selling product
        $products_count = SaleDetail::join('sales', 'sale_details.sale_id', '=', 'sales.id')
            ->join('products', 'sale_details.product_id', '=', 'products.id')
            ->join('units', 'products.unit_sale_id', '=', 'units.id')
            ->where(function ($query) use ($view_records) {
                if (!$view_records) {
                    return $query->where('sales.user_id', '=', Auth::user()->id);
                }
            })
            ->whereBetween('sale_details.date', array($request->from, $request->to))
            ->select(
                DB::raw('products.name as name'),
            )
            ->groupBy('products.name')->get();

$totalRows = $products_count->count();
            if($perPage == "-1"){
                $perPage = $totalRows;
            }

$products_data = SaleDetail::join('sales', 'sale_details.sale_id', '=', 'sales.id')
            ->join('products', 'sale_details.product_id', '=', 'products.id')
            ->join('units', 'products.unit_sale_id', '=', 'units.id')
            ->where(function ($query) use ($view_records) {
                if (!$view_records) {
                    return $query->where('sales.user_id', '=', Auth::user()->id);
                }
            })
            ->whereBetween('sale_details.date', array($request->from, $request->to))
            ->select(
                DB::raw('products.name as name'),
                DB::raw('products.code as code'),
                DB::raw('units.ShortName as unit_product'),
                DB::raw('count(*) as total_sales'),
                DB::raw('sum(total) as total'),
                DB::raw('sale_details.price as price'),
                DB::raw('sum(quantity) as quantity'),
            )
            ->groupBy('products.name');
            
            $products = $products_data->offset($offSet)
            ->limit($perPage)
            ->orderBy('total_sales', 'desc')
            ->get();

return response()->json([
                'products' => $products,
                'totalRows' => $totalRows,
            ]);

}

//-------------------- report_top_customers -------------\\

public function report_top_customers(request $request)
    {

$this->authorizeForUser($request->user('api'), 'Top_customers', Client::class);

$role = Auth::user()->roles()->first();
        $view_records = Role::findOrFail($role->id)->inRole('record_view');
        // How many items do you want to display.
        $perPage = $request->limit;
        $pageStart = \Request::get('page', 1);
        // Start displaying items from this number;
        $offSet = ($pageStart * $perPage) - $perPage;

$customers_count = Sale::where('sales.deleted_at', '=', null)
        ->where(function ($query) use ($view_records) {
            if (!$view_records) {
                return $query->where('sales.user_id', '=', Auth::user()->id);
            }
        })

->join('clients', 'sales.client_id', '=', 'clients.id')
        ->select(DB::raw('clients.name'), DB::raw("count(*) as total_sales"))
        ->groupBy('clients.name')->get();

$totalRows = $customers_count->count();
        if($perPage == "-1"){
            $perPage = $totalRows;
        }

$customers_data = Sale::where('sales.deleted_at', '=', null)
        ->where(function ($query) use ($view_records) {
            if (!$view_records) {
                return $query->where('sales.user_id', '=', Auth::user()->id);
            }
        })

->join('clients', 'sales.client_id', '=', 'clients.id')
        ->select(
            DB::raw('clients.name as name'), 
            DB::raw('clients.phone as phone'), 
            DB::raw('clients.email as email'), 
            DB::raw("count(*) as total_sales"),
            DB::raw('sum(GrandTotal) as total'),
        )
        ->groupBy('clients.name');

$customers = $customers_data->offset($offSet)
            ->limit($perPage)
            ->orderBy('total_sales', 'desc')
            ->get();

return response()->json([
            'customers' => $customers,
            'totalRows' => $totalRows,
        ]);

}

//----------------- Users Report -----------------------\\

public function users_Report(request $request)
     {
 
         $this->authorizeForUser($request->user('api'), 'users_report', User::class);
 
         // How many items do you want to display.
         $perPage = $request->limit;
         $pageStart = \Request::get('page', 1);
         // Start displaying items from this number;
         $offSet = ($pageStart * $perPage) - $perPage;
         $order = $request->SortField;
         $dir = $request->SortType;
         $data = array();
 
         $users = User::where(function ($query) use ($request) {
            return $query->when($request->filled('search'), function ($query) use ($request) {
                return $query->where('username', 'LIKE', "%{$request->search}%");
                });
            });
 
         $totalRows = $users->count();
         if($perPage == "-1"){
             $perPage = $totalRows;
         }
         $users = $users->offset($offSet)
             ->limit($perPage)
             ->orderBy($order, $dir)
             ->get();
 
         foreach ($users as $user) {
            $item['total_sales'] = DB::table('sales')
                 ->where('deleted_at', '=', null)
                 ->where('user_id', $user->id)
                 ->count();

$item['total_purchases'] = DB::table('purchases')
                 ->where('deleted_at', '=', null)
                 ->where('user_id', $user->id)
                 ->count();

$item['total_quotations'] = DB::table('quotations')
                 ->where('deleted_at', '=', null)
                 ->where('user_id', $user->id)
                 ->count();

$item['total_return_sales'] = DB::table('sale_returns')
                 ->where('deleted_at', '=', null)
                 ->where('user_id', $user->id)
                 ->count();

$item['total_return_purchases'] = DB::table('purchase_returns')
                 ->where('deleted_at', '=', null)
                 ->where('user_id', $user->id)
                 ->count();

$item['total_transfers'] = DB::table('transfers')
                 ->where('deleted_at', '=', null)
                 ->where('user_id', $user->id)
                 ->count();

$item['total_adjustments'] = DB::table('adjustments')
                 ->where('deleted_at', '=', null)
                 ->where('user_id', $user->id)
                 ->count();
 
             $item['id'] = $user->id;
             $item['username'] = $user->username;
             $data[] = $item;
         }
 
         return response()->json([
             'report' => $data,
             'totalRows' => $totalRows,
         ]);
 
     }

//-------------------- Get Sales By user -------------\\

public function get_sales_by_user(request $request)
    {

$this->authorizeForUser($request->user('api'), 'users_report', User::class);
        // How many items do you want to display.
        $perPage = $request->limit;
        $pageStart = \Request::get('page', 1);
        // Start displaying items from this number;
        $offSet = ($pageStart * $perPage) - $perPage;

$Role = Auth::user()->roles()->first();
        $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');

$sales = Sale::where('deleted_at', '=', null)->with('user','client','warehouse')
            ->where(function ($query) use ($ShowRecord) {
                if (!$ShowRecord) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            ->where('user_id', $request->id)
             // Search With Multiple Param
             ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('Ref', 'LIKE', "%{$request->search}%")
                        ->orWhere('statut', 'LIKE', "%{$request->search}%")
                        ->orWhere('payment_statut', 'like', "%{$request->search}%")
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('client', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        })
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('warehouse', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        });
                });
            });

$data = [];
        foreach ($sales as $sale) {
            $item['username'] = $sale['user']->username;
            $item['client_name'] = $sale['client']->name;
            $item['warehouse_name'] = $sale['warehouse']->name;
            $item['date'] = $sale->date;
            $item['Ref'] = $sale->Ref;
            $item['sale_id'] = $sale->id;
            $item['statut'] = $sale->statut;
            $item['GrandTotal'] = $sale->GrandTotal;
            $item['paid_amount'] = $sale->paid_amount;
            $item['due'] = $sale->GrandTotal - $sale->paid_amount;
            $item['payment_status'] = $sale->payment_statut;
            $item['shipping_status'] = $sale->shipping_status;

$data[] = $item;
        }
        return response()->json([
            'totalRows' => $totalRows,
            'sales' => $data,
        ]);

}

//-------------------- Get Quotations By user -------------\\

public function get_quotations_by_user(request $request)
     {
 
        $this->authorizeForUser($request->user('api'), 'users_report', User::class);
         // How many items do you want to display.
         $perPage = $request->limit;
         $pageStart = \Request::get('page', 1);
         // Start displaying items from this number;
         $offSet = ($pageStart * $perPage) - $perPage;
 
         $Role = Auth::user()->roles()->first();
         $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');
         $data = array();

$Quotations = Quotation::with('client', 'warehouse','user')
            ->where('deleted_at', '=', null)
             ->where('user_id', $request->id)
             ->where(function ($query) use ($ShowRecord) {
                 if (!$ShowRecord) {
                     return $query->where('user_id', '=', Auth::user()->id);
                 }
             })
              //Search With Multiple Param
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('Ref', 'LIKE', "%{$request->search}%")
                        ->orWhere('statut', 'LIKE', "%{$request->search}%")
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('client', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        })
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('warehouse', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        });
                });
            });

$totalRows = $Quotations->count();
         if($perPage == "-1"){
             $perPage = $totalRows;
         }
         $Quotations = $Quotations->offset($offSet)
             ->limit($perPage)
             ->orderBy('id', 'desc')
             ->get();

foreach ($Quotations as $Quotation) {

$item['id'] = $Quotation->id;
                $item['date'] = $Quotation->date;
                $item['Ref'] = $Quotation->Ref;
                $item['statut'] = $Quotation->statut;
                $item['username'] = $Quotation['user']->username;
                $item['warehouse_name'] = $Quotation['warehouse']->name;
                $item['client_name'] = $Quotation['client']->name;
                $item['GrandTotal'] = $Quotation->GrandTotal;

$data[] = $item;
            }
 
         return response()->json([
             'quotations' => $data,
             'totalRows' => $totalRows,
         ]);
     }

//-------------------- Get Purchases By user -------------\\

public function get_purchases_by_user(request $request)
    {

$Role = Auth::user()->roles()->first();
        $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');

$purchases = Purchase::where('deleted_at', '=', null)
            ->with('user','provider','warehouse')
            ->where('user_id', $request->id)
            ->where(function ($query) use ($ShowRecord) {
                if (!$ShowRecord) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
            // Search With Multiple Param
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('Ref', 'LIKE', "%{$request->search}%")
                        ->orWhere('statut', 'LIKE', "%{$request->search}%")
                        ->orWhere('payment_statut', 'like', "$request->search")
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('provider', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        })
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('warehouse', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        });
                });
            });

foreach ($purchases as $purchase) {
            $item['Ref'] = $purchase->Ref;
            $item['purchase_id'] = $purchase->id;
            $item['username'] = $purchase['user']->username;
            $item['provider_name'] = $purchase['provider']->name;
            $item['warehouse_name'] = $purchase['warehouse']->name;
            $item['statut'] = $purchase->statut;
            $item['GrandTotal'] = $purchase->GrandTotal;
            $item['paid_amount'] = $purchase->paid_amount;
            $item['due'] = $purchase->GrandTotal - $purchase->paid_amount;
            $item['payment_status'] = $purchase->payment_statut;

$data[] = $item;
        }

return response()->json([
            'totalRows' => $totalRows,
            'purchases' => $data,
        ]);

}

//-------------------- Get sale Returns By user -------------\\

public function get_sales_return_by_user(request $request)
     {
 
        $this->authorizeForUser($request->user('api'), 'users_report', User::class);
         // How many items do you want to display.
         $perPage = $request->limit;
         $pageStart = \Request::get('page', 1);
         // Start displaying items from this number;
         $offSet = ($pageStart * $perPage) - $perPage;
         $data = array();
 
         //  Check If User Has Permission Show All Records
         $Role = Auth::user()->roles()->first();
         $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');
 
         $SaleReturn = SaleReturn::where('deleted_at', '=', null)->with('user','client','warehouse')
             ->where('user_id', $request->id)
             ->where(function ($query) use ($ShowRecord) {
                 if (!$ShowRecord) {
                     return $query->where('user_id', '=', Auth::user()->id);
                 }
             })
             // Search With Multiple Param
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('Ref', 'LIKE', "%{$request->search}%")
                        ->orWhere('statut', 'LIKE', "%{$request->search}%")
                        ->orWhere('payment_statut', 'like', "$request->search")
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('client', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        })
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('warehouse', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        });
                });
            });
 
         $totalRows = $SaleReturn->count();
         if($perPage == "-1"){
             $perPage = $totalRows;
         }
         $SaleReturn = $SaleReturn->offset($offSet)
             ->limit($perPage)
             ->orderBy('id', 'desc')
             ->get();
 
         foreach ($SaleReturn as $Sale_Return) {
             $item['Ref'] = $Sale_Return->Ref;
             $item['return_sale_id'] = $Sale_Return->id;
             $item['statut'] = $Sale_Return->statut;
             $item['username'] = $Sale_Return['user']->username;
             $item['client_name'] = $Sale_Return['client']->name;
             $item['warehouse_name'] = $Sale_Return['warehouse']->name;
             $item['GrandTotal'] = $Sale_Return->GrandTotal;
             $item['paid_amount'] = $Sale_Return->paid_amount;
             $item['due'] = $Sale_Return->GrandTotal - $Sale_Return->paid_amount;
             $item['payment_status'] = $Sale_Return->payment_statut;
 
             $data[] = $item;
         }
 
         return response()->json([
             'totalRows' => $totalRows,
             'sales_return' => $data,
         ]);
     }

//-------------------- Get purchase Returns By user -------------\\

public function get_purchase_return_by_user(request $request)
    {

$this->authorizeForUser($request->user('api'), 'users_report', User::class);

$Role = Auth::user()->roles()->first();
        $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');

$PurchaseReturn = PurchaseReturn::where('deleted_at', '=', null)
            ->with('user','provider','warehouse')
            ->where('user_id', $request->id)
            ->where(function ($query) use ($ShowRecord) {
                if (!$ShowRecord) {
                    return $query->where('user_id', '=', Auth::user()->id);
                }
            })
             // Search With Multiple Param
             ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('Ref', 'LIKE', "%{$request->search}%")
                        ->orWhere('statut', 'LIKE', "%{$request->search}%")
                        ->orWhere('payment_statut', 'like', "$request->search")
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('provider', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        })
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('warehouse', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        });
                });
            });

foreach ($PurchaseReturn as $Purchase_Return) {
            $item['Ref'] = $Purchase_Return->Ref;
            $item['return_purchase_id'] = $Purchase_Return->id;
            $item['statut'] = $Purchase_Return->statut;
            $item['username'] = $Purchase_Return['user']->username;
            $item['provider_name'] = $Purchase_Return['provider']->name;
            $item['warehouse_name'] = $Purchase_Return['warehouse']->name;
            $item['GrandTotal'] = $Purchase_Return->GrandTotal;
            $item['paid_amount'] = $Purchase_Return->paid_amount;
            $item['due'] = $Purchase_Return->GrandTotal - $Purchase_Return->paid_amount;
            $item['payment_status'] = $Purchase_Return->payment_statut;

$data[] = $item;
        }

return response()->json([
            'totalRows' => $totalRows,
            'purchases_return' => $data,
        ]);

}

//-------------------- Get transfers By user -------------\\

public function get_transfer_by_user(request $request)
     {
 
         $this->authorizeForUser($request->user('api'), 'users_report', User::class);
 
         // How many items do you want to display.
         $perPage = $request->limit;
         $pageStart = \Request::get('page', 1);
         // Start displaying items from this number;
         $offSet = ($pageStart * $perPage) - $perPage;
         $data = array();
 
         $Role = Auth::user()->roles()->first();
         $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');
 
         $transfers = Transfer::with('from_warehouse', 'to_warehouse')
             ->with('user')
             ->where('user_id', $request->id)
             ->where(function ($query) use ($ShowRecord) {
                 if (!$ShowRecord) {
                     return $query->where('user_id', '=', Auth::user()->id);
                 }
             })
             // Search With Multiple Param
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('Ref', 'LIKE', "%{$request->search}%")
                        ->orWhere('statut', 'LIKE', "%{$request->search}%")
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('from_warehouse', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        })
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('to_warehouse', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        });
                });
            });
 
         $totalRows = $transfers->count();
         if($perPage == "-1"){
             $perPage = $totalRows;
         }
         $transfers = $transfers->offset($offSet)
             ->limit($perPage)
             ->orderBy('id', 'desc')
             ->get();
 
        foreach ($transfers as $transfer) {
                $item['id'] = $transfer->id;
                $item['date'] = $transfer->date;
                $item['Ref'] = $transfer->Ref;
                $item['username'] = $transfer['user']->username;
                $item['from_warehouse'] = $transfer['from_warehouse']->name;
                $item['to_warehouse'] = $transfer['to_warehouse']->name;
                $item['GrandTotal'] = $transfer->GrandTotal;
                $item['items'] = $transfer->items;
                $item['statut'] = $transfer->statut;

$data[] = $item;
        }
         return response()->json([
             'totalRows' => $totalRows,
             'transfers' => $data,
         ]);
 
     }

//-------------------- Get adjustment By user -------------\\

public function get_adjustment_by_user(request $request)
      {
  
          $this->authorizeForUser($request->user('api'), 'users_report', User::class);
  
          // How many items do you want to display.
          $perPage = $request->limit;
          $pageStart = \Request::get('page', 1);
          // Start displaying items from this number;
          $offSet = ($pageStart * $perPage) - $perPage;
          $data = array();
  
          $Role = Auth::user()->roles()->first();
          $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');
  
          $Adjustments = Adjustment::with('warehouse')
              ->with('user')
              ->where('user_id', $request->id)
              ->where(function ($query) use ($ShowRecord) {
                  if (!$ShowRecord) {
                      return $query->where('user_id', '=', Auth::user()->id);
                  }
              })
              // Search With Multiple Param
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('Ref', 'LIKE', "%{$request->search}%")
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('warehouse', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        });
                });
            });
  
          $totalRows = $Adjustments->count();
          if($perPage == "-1"){
              $perPage = $totalRows;
          }
          $Adjustments = $Adjustments->offset($offSet)
              ->limit($perPage)
              ->orderBy('id', 'desc')
              ->get();
  
        foreach ($Adjustments as $Adjustment) {
                $item['id'] = $Adjustment->id;
                $item['username'] = $Adjustment['user']->username;
                $item['date'] = $Adjustment->date;
                $item['Ref'] = $Adjustment->Ref;
                $item['warehouse_name'] = $Adjustment['warehouse']->name;
                $item['items'] = $Adjustment->items;
                $data[] = $item;
            }

return response()->json([
              'totalRows' => $totalRows,
              'adjustments' => $data,
          ]);
  
      }

//----------------- stock Report -----------------------\\

public function stock_Report(request $request)
     {
 
         $this->authorizeForUser($request->user('api'), 'stock_report', Product::class);
 
         // How many items do you want to display.
         $perPage = $request->limit;
         $pageStart = \Request::get('page', 1);
         // Start displaying items from this number;
         $offSet = ($pageStart * $perPage) - $perPage;
         $order = $request->SortField;
         $dir = $request->SortType;
         $data = array();

$products_data = Product::with('unit', 'category', 'brand')
            ->where('deleted_at', '=', null)
            // Search With Multiple Param
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('products.name', 'LIKE', "%{$request->search}%")
                        ->orWhere('products.code', 'LIKE', "%{$request->search}%")
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('category', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        });
                });
            });
 
            $totalRows = $products_data->count();
            if($perPage == "-1"){
                $perPage = $totalRows;
            }
            $products = $products_data->offset($offSet)
                ->limit($perPage)
                ->orderBy($order, $dir)
                ->get();
    
            foreach ($products as $product) {
                $item['id'] = $product->id;
                $item['code'] = $product->code;
                $item['name'] = $product->name;
                $item['category'] = $product['category']->name;
                // $item['unit'] = $product['unit']->ShortName;
                $item['price'] = $product->price;
                $item['cost'] = $product->cost;

$product_warehouse_data = product_warehouse::where('product_id', $product->id)
                    ->where('deleted_at', '=', null)
                    ->get();
                $total_qty = 0;
                foreach ($product_warehouse_data as $product_warehouse) {
                    $total_qty += $product_warehouse->qte;
                    $item['quantity'] = $total_qty .' '.$product['unit']->ShortName;
                }
    
                $data[] = $item;
            }
 
         return response()->json([
             'report' => $data,
             'totalRows' => $totalRows,
         ]);
 
     }

//-------------------- Get Sales By product -------------\\

public function get_sales_by_product(request $request)
    {

$this->authorizeForUser($request->user('api'), 'stock_report', Product::class);
        // How many items do you want to display.
        $perPage = $request->limit;
        $pageStart = \Request::get('page', 1);
        // Start displaying items from this number;
        $offSet = ($pageStart * $perPage) - $perPage;

$Role = Auth::user()->roles()->first();
        $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');

$sale_details_data = SaleDetail::with('product','sale','sale.client','sale.warehouse')
            ->where(function ($query) use ($ShowRecord) {
                if (!$ShowRecord) {
                    return $query->whereHas('sale', function ($q) use ($request) {
                        $q->where('user_id', '=', Auth::user()->id);
                    });
                }
            })
            ->where('product_id', $request->id)
             // Search With Multiple Param
             ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where(function ($query) use ($request) {
                            return $query->whereHas('sale.client', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        })
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('sale.warehouse', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        })
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('sale', function ($q) use ($request) {
                                $q->where('Ref', 'LIKE', "%{$request->search}%");
                            });
                        })
                        ->orWhere(function ($query) use ($request) {
                            return $query->whereHas('product', function ($q) use ($request) {
                                $q->where('name', 'LIKE', "%{$request->search}%");
                            });
                        });
                });
            });

$totalRows = $sale_details_data->count();
        if($perPage == "-1"){
            $perPage = $totalRows;
        }
        $sale_details = $sale_details_data->offset($offSet)
            ->limit($perPage)
            ->orderBy('id', 'desc')
            ->get();

$data = [];
        foreach ($sale_details as $detail) {

if($detail->product_variant_id){
                $productsVariants = ProductVariant::where('product_id', $detail->product_id)
                ->where('id', $detail->product_variant_id)->first();

$product_name = $productsVariants->name . '-' . $detail['product']['name'];

}else{
                $product_name = $detail['product']['name'];
            }

$item['date'] = $detail->date;
            $item['Ref'] = $detail['sale']->Ref;
            $item['sale_id'] = $detail['sale']->id;
            $item['client_name'] = $detail['sale']['client']->name;
            $item['warehouse_name'] = $detail['sale']['warehouse']->name;
            $item['quantity'] = $detail->quantity;
            $item['total'] = $detail->total;
            $item['product_name'] = $product_name;

$data[] = $item;
        }
        return response()->json([
            'totalRows' => $totalRows,
            'sales' => $data,
        ]);

}

//-------------------- Get quotations By product -------------\\

public function get_quotations_by_product(request $request)
     {
 
         $this->authorizeForUser($request->user('api'), 'stock_report', Product::class);
         // How many items do you want to display.
         $perPage = $request->limit;
         $pageStart = \Request::get('page', 1);
         // Start displaying items from this number;
         $offSet = ($pageStart * $perPage) - $perPage;
 
         $Role = Auth::user()->roles()->first();
         $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');
 
         $quotation_details_data = QuotationDetail::with('product','quotation','quotation.client','quotation.warehouse')
             ->where(function ($query) use ($ShowRecord) {
                 if (!$ShowRecord) {
                     return $query->whereHas('quotation', function ($q) use ($request) {
                         $q->where('user_id', '=', Auth::user()->id);
                     });
                 }
             })
             ->where('product_id', $request->id)
              // Search With Multiple Param
              ->where(function ($query) use ($request) {
                 return $query->when($request->filled('search'), function ($query) use ($request) {
                     return $query->where(function ($query) use ($request) {
                             return $query->whereHas('quotation.client', function ($q) use ($request) {
                                 $q->where('name', 'LIKE', "%{$request->search}%");
                             });
                         })
                         ->orWhere(function ($query) use ($request) {
                             return $query->whereHas('quotation.warehouse', function ($q) use ($request) {
                                 $q->where('name', 'LIKE', "%{$request->search}%");
                             });
                         })
                         ->orWhere(function ($query) use ($request) {
                             return $query->whereHas('quotation', function ($q) use ($request) {
                                 $q->where('Ref', 'LIKE', "%{$request->search}%");
                             });
                         })
                         ->orWhere(function ($query) use ($request) {
                             return $query->whereHas('product', function ($q) use ($request) {
                                 $q->where('name', 'LIKE', "%{$request->search}%");
                             });
                         });
                 });
             });
 
         $totalRows = $quotation_details_data->count();
         if($perPage == "-1"){
             $perPage = $totalRows;
         }
         $quotation_details = $quotation_details_data->offset($offSet)
             ->limit($perPage)
             ->orderBy('id', 'desc')
             ->get();
 
         $data = [];
         foreach ($quotation_details as $detail) {
 
             if($detail->product_variant_id){
                 $productsVariants = ProductVariant::where('product_id', $detail->product_id)
                 ->where('id', $detail->product_variant_id)->first();
 
                 $product_name = $productsVariants->name . '-' . $detail['product']['name'];
 
             }else{
                 $product_name = $detail['product']['name'];
             }
 
             $item['date'] = $detail['quotation']->date;
             $item['Ref'] = $detail['quotation']->Ref;
             $item['quotation_id'] = $detail['quotation']->id;
             $item['client_name'] = $detail['quotation']['client']->name;
             $item['warehouse_name'] = $detail['quotation']['warehouse']->name;
             $item['quantity'] = $detail->quantity;
             $item['total'] = $detail->total;
             $item['product_name'] = $product_name;
 
             $data[] = $item;
         }
         return response()->json([
             'totalRows' => $totalRows,
             'quotations' => $data,
         ]);
 
     }

//-------------------- Get purchases By product -------------\\

public function get_purchases_by_product(request $request)
       {
   
           $this->authorizeForUser($request->user('api'), 'stock_report', Product::class);
           // How many items do you want to display.
           $perPage = $request->limit;
           $pageStart = \Request::get('page', 1);
           // Start displaying items from this number;
           $offSet = ($pageStart * $perPage) - $perPage;
   
           $Role = Auth::user()->roles()->first();
           $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');
   
           $purchase_details_data = PurchaseDetail::with('product','purchase','purchase.provider','purchase.warehouse')
               ->where(function ($query) use ($ShowRecord) {
                   if (!$ShowRecord) {
                       return $query->whereHas('purchase', function ($q) use ($request) {
                           $q->where('user_id', '=', Auth::user()->id);
                       });
                   }
               })
               ->where('product_id', $request->id)
                // Search With Multiple Param
                ->where(function ($query) use ($request) {
                   return $query->when($request->filled('search'), function ($query) use ($request) {
                       return $query->where(function ($query) use ($request) {
                               return $query->whereHas('purchase.provider', function ($q) use ($request) {
                                   $q->where('name', 'LIKE', "%{$request->search}%");
                               });
                           })
                           ->orWhere(function ($query) use ($request) {
                               return $query->whereHas('purchase.warehouse', function ($q) use ($request) {
                                   $q->where('name', 'LIKE', "%{$request->search}%");
                               });
                           })
                           ->orWhere(function ($query) use ($request) {
                               return $query->whereHas('purchase', function ($q) use ($request) {
                                   $q->where('Ref', 'LIKE', "%{$request->search}%");
                               });
                           })
                           ->orWhere(function ($query) use ($request) {
                               return $query->whereHas('product', function ($q) use ($request) {
                                   $q->where('name', 'LIKE', "%{$request->search}%");
                               });
                           });
                   });
               });
   
           $totalRows = $purchase_details_data->count();
           if($perPage == "-1"){
               $perPage = $totalRows;
           }
           $purchase_details = $purchase_details_data->offset($offSet)
               ->limit($perPage)
               ->orderBy('id', 'desc')
               ->get();
   
           $data = [];
           foreach ($purchase_details as $detail) {
   
               if($detail->product_variant_id){
                   $productsVariants = ProductVariant::where('product_id', $detail->product_id)
                   ->where('id', $detail->product_variant_id)->first();
   
                   $product_name = $productsVariants->name . '-' . $detail['product']['name'];
   
               }else{
                   $product_name = $detail['product']['name'];
               }
   
               $item['date'] = $detail['purchase']->date;
               $item['Ref'] = $detail['purchase']->Ref;
               $item['purchase_id'] = $detail['purchase']->id;
               $item['provider_name'] = $detail['purchase']['provider']->name;
               $item['warehouse_name'] = $detail['purchase']['warehouse']->name;
               $item['quantity'] = $detail->quantity;
               $item['total'] = $detail->total;
               $item['product_name'] = $product_name;
   
               $data[] = $item;
           }
           return response()->json([
               'totalRows' => $totalRows,
               'purchases' => $data,
           ]);
   
       }

//-------------------- Get purchases return By product -------------\\

public function get_purchase_return_by_product(request $request)
         {
     
             $this->authorizeForUser($request->user('api'), 'stock_report', Product::class);
             // How many items do you want to display.
             $perPage = $request->limit;
             $pageStart = \Request::get('page', 1);
             // Start displaying items from this number;
             $offSet = ($pageStart * $perPage) - $perPage;
     
             $Role = Auth::user()->roles()->first();
             $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');
     
             $purchase_return_details_data = PurchaseReturnDetails::with('product','PurchaseReturn','PurchaseReturn.provider','PurchaseReturn.warehouse')
                 ->where(function ($query) use ($ShowRecord) {
                     if (!$ShowRecord) {
                         return $query->whereHas('PurchaseReturn', function ($q) use ($request) {
                             $q->where('user_id', '=', Auth::user()->id);
                         });
                     }
                 })
                 ->where('product_id', $request->id)
                  // Search With Multiple Param
                  ->where(function ($query) use ($request) {
                     return $query->when($request->filled('search'), function ($query) use ($request) {
                         return $query->where(function ($query) use ($request) {
                                 return $query->whereHas('PurchaseReturn.provider', function ($q) use ($request) {
                                     $q->where('name', 'LIKE', "%{$request->search}%");
                                 });
                             })
                             ->orWhere(function ($query) use ($request) {
                                 return $query->whereHas('PurchaseReturn.warehouse', function ($q) use ($request) {
                                     $q->where('name', 'LIKE', "%{$request->search}%");
                                 });
                             })
                             ->orWhere(function ($query) use ($request) {
                                 return $query->whereHas('PurchaseReturn', function ($q) use ($request) {
                                     $q->where('Ref', 'LIKE', "%{$request->search}%");
                                 });
                             })
                             ->orWhere(function ($query) use ($request) {
                                 return $query->whereHas('product', function ($q) use ($request) {
                                     $q->where('name', 'LIKE', "%{$request->search}%");
                                 });
                             });
                     });
                 });
     
             $totalRows = $purchase_return_details_data->count();
             if($perPage == "-1"){
                 $perPage = $totalRows;
             }
             $purchase_return_details = $purchase_return_details_data->offset($offSet)
                 ->limit($perPage)
                 ->orderBy('id', 'desc')
                 ->get();
     
             $data = [];
             foreach ($purchase_return_details as $detail) {
     
                 if($detail->product_variant_id){
                     $productsVariants = ProductVariant::where('product_id', $detail->product_id)
                     ->where('id', $detail->product_variant_id)->first();
     
                     $product_name = $productsVariants->name . '-' . $detail['product']['name'];
     
                 }else{
                     $product_name = $detail['product']['name'];
                 }
     
                 $item['date'] = $detail['PurchaseReturn']->date;
                 $item['Ref'] = $detail['PurchaseReturn']->Ref;
                 $item['return_purchase_id'] = $detail['PurchaseReturn']->id;
                 $item['provider_name'] = $detail['PurchaseReturn']['provider']->name;
                 $item['warehouse_name'] = $detail['PurchaseReturn']['warehouse']->name;
                 $item['quantity'] = $detail->quantity;
                 $item['total'] = $detail->total;
                 $item['product_name'] = $product_name;
     
                 $data[] = $item;
             }
             return response()->json([
                 'totalRows' => $totalRows,
                 'purchases_return' => $data,
             ]);
     
         }

//-------------------- Get sales return By product -------------\\

public function get_sales_return_by_product(request $request)
     {
 
         $this->authorizeForUser($request->user('api'), 'stock_report', Product::class);
         // How many items do you want to display.
         $perPage = $request->limit;
         $pageStart = \Request::get('page', 1);
         // Start displaying items from this number;
         $offSet = ($pageStart * $perPage) - $perPage;
 
         $Role = Auth::user()->roles()->first();
         $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');
 
         $Sale_Return_details_data = SaleReturnDetails::with('product','SaleReturn','SaleReturn.client','SaleReturn.warehouse')
             ->where(function ($query) use ($ShowRecord) {
                 if (!$ShowRecord) {
                     return $query->whereHas('SaleReturn', function ($q) use ($request) {
                         $q->where('user_id', '=', Auth::user()->id);
                     });
                 }
             })
             ->where('product_id', $request->id)
              // Search With Multiple Param
              ->where(function ($query) use ($request) {
                 return $query->when($request->filled('search'), function ($query) use ($request) {
                     return $query->where(function ($query) use ($request) {
                             return $query->whereHas('SaleReturn.client', function ($q) use ($request) {
                                 $q->where('name', 'LIKE', "%{$request->search}%");
                             });
                         })
                         ->orWhere(function ($query) use ($request) {
                             return $query->whereHas('SaleReturn.warehouse', function ($q) use ($request) {
                                 $q->where('name', 'LIKE', "%{$request->search}%");
                             });
                         })
                         ->orWhere(function ($query) use ($request) {
                             return $query->whereHas('SaleReturn', function ($q) use ($request) {
                                 $q->where('Ref', 'LIKE', "%{$request->search}%");
                             });
                         })
                         ->orWhere(function ($query) use ($request) {
                             return $query->whereHas('product', function ($q) use ($request) {
                                 $q->where('name', 'LIKE', "%{$request->search}%");
                             });
                         });
                 });
             });
 
         $totalRows = $Sale_Return_details_data->count();
         if($perPage == "-1"){
             $perPage = $totalRows;
         }
         $Sale_Return_details = $Sale_Return_details_data->offset($offSet)
             ->limit($perPage)
             ->orderBy('id', 'desc')
             ->get();
 
         $data = [];
         foreach ($Sale_Return_details as $detail) {
 
             if($detail->product_variant_id){
                 $productsVariants = ProductVariant::where('product_id', $detail->product_id)
                 ->where('id', $detail->product_variant_id)->first();
 
                 $product_name = $productsVariants->name . '-' . $detail['product']['name'];
 
             }else{
                 $product_name = $detail['product']['name'];
             }
 
             $item['date'] = $detail['SaleReturn']->date;
             $item['Ref'] = $detail['SaleReturn']->Ref;
             $item['return_sale_id'] = $detail['SaleReturn']->id;
             $item['client_name'] = $detail['SaleReturn']['client']->name;
             $item['warehouse_name'] = $detail['SaleReturn']['warehouse']->name;
             $item['quantity'] = $detail->quantity;
             $item['total'] = $detail->total;
             $item['product_name'] = $product_name;
 
             $data[] = $item;
         }
         return response()->json([
             'totalRows' => $totalRows,
             'sales_return' => $data,
         ]);
 
     }

//-------------------- Get transfers By product -------------\\

public function get_transfer_by_product(request $request)
      {
  
          $this->authorizeForUser($request->user('api'), 'stock_report', Product::class);
          // How many items do you want to display.
          $perPage = $request->limit;
          $pageStart = \Request::get('page', 1);
          // Start displaying items from this number;
          $offSet = ($pageStart * $perPage) - $perPage;
  
          $Role = Auth::user()->roles()->first();
          $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');
  
          $transfer_details_data = TransferDetail::with('product','transfer','transfer.from_warehouse','transfer.to_warehouse')
              ->where(function ($query) use ($ShowRecord) {
                  if (!$ShowRecord) {
                      return $query->whereHas('transfer', function ($q) use ($request) {
                          $q->where('user_id', '=', Auth::user()->id);
                      });
                  }
              })
              ->where('product_id', $request->id)
               // Search With Multiple Param
               ->where(function ($query) use ($request) {
                  return $query->when($request->filled('search'), function ($query) use ($request) {
                      return $query->where(function ($query) use ($request) {
                              return $query->whereHas('transfer.from_warehouse', function ($q) use ($request) {
                                  $q->where('name', 'LIKE', "%{$request->search}%");
                              });
                          })
                          ->orWhere(function ($query) use ($request) {
                              return $query->whereHas('transfer.to_warehouse', function ($q) use ($request) {
                                  $q->where('name', 'LIKE', "%{$request->search}%");
                              });
                          })
                          ->orWhere(function ($query) use ($request) {
                              return $query->whereHas('transfer', function ($q) use ($request) {
                                  $q->where('Ref', 'LIKE', "%{$request->search}%");
                              });
                          })
                          ->orWhere(function ($query) use ($request) {
                              return $query->whereHas('product', function ($q) use ($request) {
                                  $q->where('name', 'LIKE', "%{$request->search}%");
                              });
                          });
                  });
              });
  
          $totalRows = $transfer_details_data->count();
          if($perPage == "-1"){
              $perPage = $totalRows;
          }
          $transfer_details = $transfer_details_data->offset($offSet)
              ->limit($perPage)
              ->orderBy('id', 'desc')
              ->get();
  
          $data = [];
          foreach ($transfer_details as $detail) {
  
              if($detail->product_variant_id){
                  $productsVariants = ProductVariant::where('product_id', $detail->product_id)
                  ->where('id', $detail->product_variant_id)->first();
  
                  $product_name = $productsVariants->name . '-' . $detail['product']['name'];
  
              }else{
                  $product_name = $detail['product']['name'];
              }
  
              $item['date'] = $detail['transfer']->date;
              $item['Ref'] = $detail['transfer']->Ref;
              $item['from_warehouse'] = $detail['transfer']['from_warehouse']->name;
              $item['to_warehouse'] = $detail['transfer']['to_warehouse']->name;
              $item['product_name'] = $product_name;
  
              $data[] = $item;
          }
          return response()->json([
              'totalRows' => $totalRows,
              'transfers' => $data,
          ]);
  
      }

//-------------------- Get adjustments By product -------------\\

public function get_adjustment_by_product(request $request)
        {
    
            $this->authorizeForUser($request->user('api'), 'stock_report', Product::class);
            // How many items do you want to display.
            $perPage = $request->limit;
            $pageStart = \Request::get('page', 1);
            // Start displaying items from this number;
            $offSet = ($pageStart * $perPage) - $perPage;
    
            $Role = Auth::user()->roles()->first();
            $ShowRecord = Role::findOrFail($Role->id)->inRole('record_view');
    
            $adjustment_details_data = AdjustmentDetail::with('product','adjustment','adjustment.warehouse')
                ->where(function ($query) use ($ShowRecord) {
                    if (!$ShowRecord) {
                        return $query->whereHas('adjustment', function ($q) use ($request) {
                            $q->where('user_id', '=', Auth::user()->id);
                        });
                    }
                })
                ->where('product_id', $request->id)
                 // Search With Multiple Param
                 ->where(function ($query) use ($request) {
                    return $query->when($request->filled('search'), function ($query) use ($request) {
                        return $query->where(function ($query) use ($request) {
                                return $query->whereHas('adjustment.warehouse', function ($q) use ($request) {
                                    $q->where('name', 'LIKE', "%{$request->search}%");
                                });
                            })
                            ->orWhere(function ($query) use ($request) {
                                return $query->whereHas('adjustment', function ($q) use ($request) {
                                    $q->where('Ref', 'LIKE', "%{$request->search}%");
                                });
                            })
                            ->orWhere(function ($query) use ($request) {
                                return $query->whereHas('product', function ($q) use ($request) {
                                    $q->where('name', 'LIKE', "%{$request->search}%");
                                });
                            });
                    });
                });
    
            $totalRows = $adjustment_details_data->count();
            if($perPage == "-1"){
                $perPage = $totalRows;
            }
            $adjustment_details = $adjustment_details_data->offset($offSet)
                ->limit($perPage)
                ->orderBy('id', 'desc')
                ->get();
    
            $data = [];
            foreach ($adjustment_details as $detail) {
    
                if($detail->product_variant_id){
                    $productsVariants = ProductVariant::where('product_id', $detail->product_id)
                    ->where('id', $detail->product_variant_id)->first();
    
                    $product_name = $productsVariants->name . '-' . $detail['product']['name'];
    
                }else{
                    $product_name = $detail['product']['name'];
                }
    
                $item['date'] = $detail['adjustment']->date;
                $item['Ref'] = $detail['adjustment']->Ref;
                $item['warehouse_name'] = $detail['adjustment']['warehouse']->name;
                $item['product_name'] = $product_name;
    
                $data[] = $item;
            }
            return response()->json([
                'totalRows' => $totalRows,
                'adjustments' => $data,
            ]);
    
        }

//------------- download_report_client_pdf -----------\\

public function download_report_client_pdf(Request $request, $id)
    {

$this->authorizeForUser($request->user('api'), 'Reports_customers', Client::class);

$helpers = new helpers();
        $client = Client::where('deleted_at', '=', null)->findOrFail($id);

$Sales = Sale::where('deleted_at', '=', null)
        ->where('payment_statut', '!=', 'paid')
        ->where('client_id', $id)
        ->get();

$sales_details = [];

foreach ($Sales as $Sale) {
            
            $item_sale['date'] = $Sale['date'];
            $item_sale['Ref'] = $Sale['Ref'];
            $item_sale['GrandTotal'] = number_format($Sale['GrandTotal'], 2, '.', '');
            $item_sale['paid_amount'] = number_format($Sale['paid_amount'], 2, '.', '');
            $item_sale['due'] = number_format($item_sale['GrandTotal'] - $item_sale['paid_amount'], 2, '.', '');
            $item_sale['payment_status'] = $Sale['payment_statut'];
            
            $sales_details[] = $item_sale;
        }

$data['client_name'] = $client->name;
        $data['phone'] = $client->phone;

$data['total_sales'] = DB::table('sales')->where('deleted_at', '=', null)->where('client_id', $id)->count();

$data['total_amount'] = DB::table('sales')->where('deleted_at', '=', null)->where('client_id', $id)
            ->sum('GrandTotal');

$data['due'] = $data['total_amount'] - $data['total_paid'];
     
        $symbol = $helpers->Get_Currency();
        $settings = Setting::where('deleted_at', '=', null)->first();

$pdf = \PDF::loadView('pdf.report_client_pdf', [
            'symbol' => $symbol,
            'client' => $data,
            'sales' => $sales_details,
            'setting' => $settings,
        ]);

return $pdf->download('report_client.pdf');

}

//------------- download_report_provider_pdf -----------\\

public function download_report_provider_pdf(Request $request, $id)
     {
 
        $this->authorizeForUser($request->user('api'), 'Reports_suppliers', Provider::class);
 
         $helpers = new helpers();
         $provider = Provider::where('deleted_at', '=', null)->findOrFail($id);
 
         $purchases = Purchase::where('deleted_at', '=', null)
         ->where('payment_statut', '!=', 'paid')
         ->where('provider_id', $id)
         ->get();

$purchases_details = [];
 
         foreach ($purchases as $purchase) {
             
             $item_purchase['date'] = $purchase['date'];
             $item_purchase['Ref'] = $purchase['Ref'];
             $item_purchase['GrandTotal'] = number_format($purchase['GrandTotal'], 2, '.', '');
             $item_purchase['paid_amount'] = number_format($purchase['paid_amount'], 2, '.', '');
             $item_purchase['due'] = number_format($item_purchase['GrandTotal'] - $item_purchase['paid_amount'], 2, '.', '');
             $item_purchase['payment_status'] = $purchase['payment_statut'];
             
             $purchases_details[] = $item_purchase;
         }
 
         $data['provider_name'] = $provider->name;
         $data['phone'] = $provider->phone;
 
        $data['total_purchase'] = DB::table('purchases')->where('deleted_at', '=', null)->where('provider_id', $id)->count();

$data['total_amount'] = DB::table('purchases')->where('deleted_at', '=', null)->where('provider_id', $id)
            ->sum('GrandTotal');

$data['due'] = $data['total_amount'] - $data['total_paid'];
      
         $symbol = $helpers->Get_Currency();
         $settings = Setting::where('deleted_at', '=', null)->first();
 
         $pdf = \PDF::loadView('pdf.report_provider_pdf', [
             'symbol' => $symbol,
             'provider' => $data,
             'purchases' => $purchases_details,
             'setting' => $settings,
         ]);
 
         return $pdf->download('report_provider.pdf');
 
     }

}

cmd: