<?php

namespace App\Http\Controllers;

use App\Models\Clients;
use App\Models\ColumnPermission;
use App\Models\RowPermissions;
use App\Models\Stock;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class StockController extends Controller
{
    function viewStockReport()
    {
        $shopID = session('LoggedUser')['userid'];

        $data = [

            'clientname' => Clients::where('ClientID', '=', $shopID)->pluck('ClientName'),

            'tablehead' => ColumnPermission::select('*')
                ->where('slug_url', '=', 'STKRPT')
                ->where('client_id', '=', $shopID)
                ->where('column_status', '=', 1)
                ->get(),

                'tablerow' => RowPermissions::select('*')
                ->where('slug_url', '=', 'STKRPT')
                ->where('client_id', '=', $shopID)
                ->where('row_status', '=', 1)
                ->get(),

            'totalValues' => Stock::select(
                DB::raw(" ROUND(SUM(Unit_Rate * (Purchase_quantity-Sales_quantity)),2) as totalstockvalue "),
                DB::raw(" ROUND(SUM(`Unit_Rate` * (`Purchase_quantity`-`Sales_quantity`) ),2) as totalpurchasevalue "),
                DB::raw(" ROUND(SUM(`Sales_Rate` * (`Purchase_quantity`-`Sales_quantity`) ),2) as totalsalevalue "),
                )->get(),

            'stock' => Stock::select(
                'Product_Name',
                'Batch',
                'manufacturer',

                DB::raw("SUBSTRING(Expiry,3, 5) as Expiry"),
                'uPack',
                DB::raw("purchase_quantity-sales_quantity as stock"),
                DB::raw("ROUND(Unit_Rate * uPack,2) as purchaserate"),
                DB::raw("ROUND( (Unit_Rate * uPack) * (purchase_quantity-sales_quantity),2 ) as purchasetotal"),
                DB::raw("  ROUND((MRP * uPack) / uPack ,2) as MRP"),
                DB::raw("((MRP * uPack) / uPack ) * (Purchase_quantity-sales_quantity) as mrptotal"),
                DB::raw("MRP * uPack as mrpperpack"),
                DB::raw("ROUND(Sales_Rate, 2) as Sales_Rate"),
                DB::raw(" ROUND(Sales_Rate * (Purchase_quantity-sales_quantity),2) as saleratetotal"),
                DB::raw("ROUND(Sales_Rate * uPack,2) as salerateperpack"),
            )
                ->where('ClientID', '=', $shopID)
                ->where(DB::raw("purchase_quantity-sales_quantity"), '>', 0)

                ->get()
        ];

        return view('Admin.viewStockReport', $data);
    }
}
