<?php


namespace App\Http\Controllers\report;


use App\Fun\Fun;
use App\Http\Controllers\Controller;
use App\Model\AdjustmentDetailModel;
use App\Model\product\ProductUnitModel;
use App\Model\stock_inventory\PurchaseDetailsModel;
use App\Model\pos\SaleDetailsModel;
use App\Model\stock_inventory\StockModel;
use App\Model\stock_inventory\TransferStockDetailModel;
use App\Model\setup\WarehouseModel;
use Auth;
use DataTables;
use DB;
use Illuminate\Http\Request;

class StockReportController extends Controller

{

    function __construct()
    {
//        $this->middleware('permission:Report Stock List', ['only' => ['index']]);
        Fun::lang();
    }

    public function laolin_stock_item(Request $request)
    {
        $warehouses = getWarehouse('item')->pluck('warehouse_name', 'id');
        if ($request->ajax()) {
            $search = $request->input('search.value');
            $wh = $request->warehouse;
            $product_id = $request->product_id;
            $branch = WarehouseModel::where('branch_id', Auth::user()->branch_id)->pluck('id');
            $date = $request->date;


            $count_total = StockModel::whereIn('warehouse_id', $branch)->where('nso007_stocks.type', 'product')->count();
            $balances = StockModel::select('nso007_products.product_name', 'nso007_stocks.*')
                ->join('nso007_products', 'nso007_products.id', 'nso007_stocks.product_id')
//                ->whereIn('warehouse_id', $branch)
                ->where('nso007_stocks.type', 'product')
                ->where(function ($q) use ($wh) {
                    if ($wh) {
                        $q->where('nso007_stocks.warehouse_id', $wh);
                    }
                })
                ->where(function ($q) use ($product_id) {
                    if ($product_id) {
                        $q->where('nso007_products.id', $product_id);
                    }
                })
                ->where(function ($q) use ($search) {
                    if ($search) {
                        $q->orwhere('nso007_stocks.barcode', 'like', '%' . $search . '%');
                        $q->orwhere('nso007_products.product_name', 'like', '%' . $search . '%');
                        $q->orwhere('nso007_stocks.type', 'like', '%' . $search . '%');
                    }
                })
                ->orderBy('nso007_stocks.barcode', 'ASC')
                ->orderBy('nso007_stocks.created_at', 'DESC');
            $count_filter = $balances->count();

            return Datatables::of($balances->take(20))
                ->with([
                    "recordsTotal" => $count_total,
                    "recordsFiltered" => $count_filter,
                ])
                ->addIndexColumn()
                ->editColumn('product_name', function ($balances) {
                    $pro_unit = findProductUnitByCode($balances->barcode, 'product');
                    $size = findSize($pro_unit->size_code)->size ?? '';
                    $color = findColor($pro_unit->color_code)->name ?? '';
                    return $balances->product_name . '(' . $size . ',' . $color . ')';
                })
                ->addColumn('warehouse', function ($balances) {
                    return $balances->warehouse_name();
                })
                ->addColumn('barcode', function ($balances) {
                    return $balances->barcode;
                })
                ->addColumn('old_stock', function ($balances) use ($date) {
                    $balances->old_stock = $balances->old_stock($date);
                    return $balances->old_stock;
                })
                ->addColumn('in_qty', function ($balances) use ($date) {
                    $balances->in_qty = $balances->in_stock_today($date);
                    return $balances->in_qty;
                })
                ->addColumn('out_qty', function ($balances) use ($date) {
                    $balances->out_qty = $balances->out_stock_today($date);

                    return $balances->out_qty;
                })
                ->addColumn('stock_qty', function ($balances) use ($date) {
                    $pro = getProductById($balances->product_id);
                    $unit = findUnit($pro ? $pro->unit_id : 0);
                    if ($date) {
                        $new_qty = floatval($balances->old_stock) + floatval($balances->in_qty) - floatval($balances->out_qty);
                    } else {
                        $new_qty = floatval($balances->in_qty) - floatval($balances->out_qty);
                    }
                    return $new_qty . ' ' . ($unit ? $unit->unit : '');
                })
                ->rawColumns(['in_qty', 'old_stock', 'out_qty', 'product_price', 'transfer_stock', 'action', 'return_qty', 'production_qty', 'stock_qty', 'production_out', 'sale_return'])
                ->make(true);
        }
        return view('administrator.report-stock.laolin-stock-item', compact('warehouses'));
    }

    public function report_stock_item(Request $request)
    {
        $warehouses = getWarehouse('item')->pluck('warehouse_name', 'id');
        if ($request->ajax()) {
            $search = $request->input('search.value');
            $wh = $request->warehouse;
            $qty_alert = $request->qty_alert;
            $product_id = $request->product_id;
            $branch = WarehouseModel::where(function ($q) {
                $main = myBranch()->main;
                if (!$main) {
                    $q->where('branch_id', Auth::user()->branch_id);
                }
            })->pluck('id');
            $balances = StockModel::select('nso007_products.product_name', 'nso007_products.alert_qty', 'nso007_stocks.*', 'nso007_products.unit_id')
                ->join('nso007_products', 'nso007_products.id', 'nso007_stocks.product_id')
                ->where('nso007_stocks.type', 'product')
                ->where('nso007_products.type', 'product')
                ->where(function ($q) use ($wh, $branch) {
                    if ($branch) {
                        $q->whereIn('nso007_stocks.warehouse_id', $branch);
                    } else {
                        if ($wh) {
                            $q->where('nso007_stocks.warehouse_id', $wh);
                        }
                    }
                })
                ->where(function ($q) use ($wh) {
                    if ($wh) {
                        $q->where('nso007_stocks.warehouse_id', $wh);
                    }
                })
                ->where(function ($q) use ($product_id) {
                    if ($product_id) {
                        $q->where('nso007_products.id', $product_id);
                    }
                })
                ->where(function ($q) use ($qty_alert) {
                    if ($qty_alert) {
                        $q->whereColumn('nso007_stocks.stock_qty', '<=', 'nso007_products.alert_qty');
                    }
                })
                ->where(function ($q) use ($search) {
                    if ($search) {
                        $q->where('nso007_stocks.barcode', 'like', '%' . $search . '%');
                        $q->orwhere('nso007_products.product_name', 'like', '%' . $search . '%');
                    }
                })
                ->orderBy('nso007_stocks.barcode', 'ASC')
                ->orderBy('nso007_stocks.created_at', 'DESC');
            return Datatables::of($balances)
                ->addIndexColumn()
                ->editColumn('product_name', function ($balances) {
                    return $balances->product_name . '  (' . ($balances->getSize->size ?? '') . ',' . ($balances->getColor->name ?? '') . ')';
                })
                ->addColumn('stock_qty', function ($balances) {
                    $alert = '';
                    if ($balances->stock_qty <= $balances->alert_qty) {
                        $alert = '<span class="fa fa-circle text-warning"></span>';
                    }
                    $balances->unit = findUnit($balances->unit_id)->unit ?? '';
                    return "$balances->stock_qty $balances->unit $alert";
                })
                ->addColumn('warehouse', function ($balances) {
                    return $balances->warehouse_name();
                })
                ->addColumn('barcode', function ($balances) {
                    return $balances->barcode;
                })
                ->addColumn('out_qty', function ($balances) {
                    $qty = $balances->out_stock();
                    if ($qty)
                        return '<a class="under-line cursor text-primary show-stock-in" data-url="' . route('get-item-stock-sale-history') . '?prcode=' . $balances->barcode . '&warehouse_hide=' . $balances->warehouse_id . '">' . "$qty $balances->unit" . '</a>';
                })
                ->addColumn('in_qty', function ($balances) {
                    return '<a class="under-line cursor text-primary show-stock-in" data-url="' . route('get-item-stock-purchase-history') . '?prcode=' . $balances->barcode . '&warehouse_hide=' . $balances->warehouse_id . '">' . $balances->in_stock() . '</a>';
                })
                ->addColumn('transfer_stock', function ($balances) {
                    if ($balances->transfer_stock_in()) {
                        $btn_in = '<a class="under-line cursor show-stock-in"
                     data-url="' . route('get-item-transfer-stock-in-out-history') . '?prcode=' . $balances->barcode . '&warehouse_hide=' . $balances->warehouse_id . '&type_hidden=in"
                     >' . $balances->transfer_stock_in() . '</a>';
                    } else {
                        $btn_in = '';
                    }
                    if ($balances->transfer_stock_out()) {
                        $btn_out = '<a class="under-line cursor show-stock-in"
                     data-url="' . route('get-item-transfer-stock-in-out-history') . '?prcode=' . $balances->barcode . '&warehouse_hide=' . $balances->warehouse_id . '&type_hidden=out"
                     >' . $balances->transfer_stock_out() . '</a>';
                    } else {
                        $btn_out = '';
                    }
                    return $btn_in . ($btn_in && $btn_out ? ' | ' : '') . $btn_out;
                })
                ->addColumn('request_stock', function ($balances) {
                    return $balances->request_stock();
                })
                ->addColumn('return_qty', function ($balances) {
                    return $balances->return_stock();
                })
                ->addColumn('sale_return', function ($balances) {
                    $return = $balances->return_stock();
                    return $return ? "$return $balances->unit" : '';
                })
                ->addColumn('production', function ($balances) {
                    return $balances->production_qty();
                })
                ->addColumn('total_cost', function ($balances) {
                    $pro = ProductUnitModel::where(['barcode'=>$balances->barcode,'product_id'=>$balances->product_id])->first();
                    if($pro&&auth()->user()->can('Stock Total Cost')){
                        $cost_riel = number_format($pro->cost_riel*$balances->stock_qty,2);
                        $cost_usd = number_format($pro->cost_usd*$balances->stock_qty,2);
                        return "<span class='cost_riel'>$cost_riel</span> ៛<br><span class='cost_usd'>$cost_usd</span> $";
                    }
                })
                ->addColumn('adjustment', function ($balances) {

                    $minus_qty = $this->adj($balances, '-');
                    $add_qty = $this->adj($balances, '+');
                    $minus_text = '';
                    $add_text = '';
                    if ($add_qty) {
                        $add_text = "<b class='text-success' data-qty='$add_qty'>ថែម: </b>$add_qty $balances->unit";
                    }
                    if ($minus_qty) {
                        $minus_text = "<b class='text-danger' data-qty='$minus_qty'>ថយ: </b>$minus_qty $balances->unit";
                    }
                    return $add_text . ($add_text && $minus_text ? ' | ' : '') . $minus_text;
                })
                ->rawColumns(['adjustment', 'in_qty','total_cost', 'request_stock', 'out_qty', 'product_price', 'transfer_stock', 'action', 'return_qty', 'production_qty', 'stock_qty', 'production_out', 'sale_return'])
                ->make(true);
        }
        return view('administrator.report-stock.stock-item', compact('warehouses'));
    }

    function adj($balances, $op)
    {
        return AdjustmentDetailModel::join('nso007_adjustment', 'nso007_adjustment_details.adjustment_id', 'nso007_adjustment.id')
            ->where(['nso007_adjustment.warehouse_id' => $balances->warehouse_id, 'nso007_adjustment_details.barcode' => $balances->barcode, 'nso007_adjustment_details.operator' => $op])->sum('qty');
    }

    public function stock_by_branch(Request $request)
    {
        return view('administrator.report-stock.new-stock-report.stock-in-item-by-branch');
    }

    public function stock_out_by_branch(Request $request)
    {
        return view('administrator.report-stock.new-stock-report.stock-out-item-by-branch');
    }

    public function stock_in_daily(Request $request)
    {
        return view('administrator.report-stock.new-stock-report.stock-in-daily');
    }

    public function stock_out_daily(Request $request)
    {
        return view('administrator.report-stock.new-stock-report.stock-out-daily');
    }

    public function report_stock_material(Request $request)
    {
        $warehouses = StockModel::where('type', 'material')->groupBy('warehouse_id')->pluck('warehouse_id');
        $warehouses = WarehouseModel::whereIn('id', $warehouses)->pluck('warehouse_name', 'id');
        if ($request->ajax()) {
            $search = $request->input('search.value');
            $wh = $request->warehouse;
            $balances = StockModel::select('nso007_products.product_name', 'nso007_stocks.*')
                ->join('nso007_products', 'nso007_products.id', 'nso007_stocks.product_id')
                ->where('nso007_stocks.type', 'material')
                ->where(function ($q) use ($wh) {
                    if ($wh) {
                        $q->where('nso007_stocks.warehouse_id', $wh);
                    }
                })
                ->where(function ($q) use ($search) {
                    if ($search) {
                        $q->where('nso007_stocks.barcode', 'like', '%' . $search . '%');
                        $q->orwhere('nso007_products.product_name', 'like', '%' . $search . '%');
                        $q->orwhere('nso007_stocks.type', 'like', '%' . $search . '%');
                    }
                })
                ->orderBy('nso007_stocks.created_at', 'DESC');
            return Datatables::of($balances)
                ->addIndexColumn()
                ->editColumn('product_name', function ($balances) {
                    return $balances->product_name;
                })
                ->addColumn('warehouse', function ($balances) {
                    return $balances->warehouse_name();
                })
                ->addColumn('barcode', function ($balances) {
                    return getProductById($balances->product_id)->barcode;
                })
                ->addColumn('stock_qty', function ($balances) {
                    return getStockQty($balances->product_id, $balances->stock_qty);
                })
                ->addColumn('in_qty', function ($balances) {
                    return $balances->in_stock_material();
                })
                ->addColumn('production_out', function ($balances) {
                    return $balances->production_out();
                })
                ->addColumn('transfer', function ($balances) {
                    $transfer = TransferStockDetailModel::join('nso007_transfer_stock', 'nso007_transfer_stock_detail.transfer_id', 'nso007_transfer_stock.id')
                            ->join('nso007_product_unit', 'nso007_transfer_stock_detail.product_id', 'nso007_product_unit.product_id')
                            ->join('nso007_products', 'nso007_transfer_stock_detail.product_id', 'nso007_products.id')
                            ->where('nso007_transfer_stock.from_warehouse_id', $balances->warehouse_id)
                            ->where('nso007_transfer_stock_detail.product_id', $balances->product_id)
                            ->where('nso007_transfer_stock.type', 'material')
                            ->where('nso007_products.type', 'material')
                            ->selectRaw('SUM(nso007_transfer_stock_detail.qty*nso007_product_unit.qty_per_unit) as transfer_stock')
                            ->first()->transfer_stock ?? 0;
                    $product_unit = ProductUnitModel::where(['product_id' => $balances->product_id])->get();
                    $qty = '';
                    foreach ($product_unit as $row) {
                        $q = (int)($transfer / $row->qty_per_unit);
                        $unit = findUnit($row->unit_id)->unit ?? '';
                        $qty .= "$q $unit,";
                    }
                    return rtrim($qty, ',');
                })
                ->rawColumns(['product_price', 'action', 'return_qty', 'production_qty', 'stock_qty', 'production_out', 'transfer'])
                ->make(true);
        }
        return view('administrator.report-stock.stock-material', compact('warehouses'));

    }

    public function report_stock_fabric(Request $request)
    {
        $warehouses = getWarehouse('fabric', 'tailor')->pluck('warehouse_name', 'id');
        if ($request->ajax()) {
            $search = $request->input('search.value');
            $wh = $request->warehouse;
            $branch = WarehouseModel::where('branch_id', Auth::user()->branch_id)->pluck('id');
            $balances = StockModel::select('nso007_products.product_name', 'nso007_stocks.*')
                ->join('nso007_products', 'nso007_products.id', 'nso007_stocks.product_id')
                ->whereIn('warehouse_id', $branch)
                ->where('nso007_stocks.type', 'fabric')
                ->where(function ($q) use ($wh) {
                    if ($wh) {
                        $q->where('nso007_stocks.warehouse_id', $wh);
                    }
                })
                ->where(function ($q) use ($search) {
                    if ($search) {
                        $q->where('nso007_products.product_name', 'like', '%' . $search . '%');
                        $q->orwhere('nso007_stocks.barcode', 'like', $search . '%');
                        $q->orwhere('nso007_stocks.type', 'like', $search . '%');
                    }
                })
                ->orderBy('nso007_stocks.barcode', 'ASC')
                ->orderBy('nso007_stocks.created_at', 'DESC');
            return Datatables::of($balances)
                ->addIndexColumn()
                ->editColumn('product_name', function ($balances) {
                    $balances->product = getProductById($balances->product_id);
                    $unit = findUnit($balances->product->child_unit);
                    return '<span style="white-space: nowrap">' . $balances->product->product_name . ' (' . ($balances->qty_per_unit . ' ' . ($unit ? $unit->unit : '')) . ')</span>';
                })
                ->addColumn('warehouse', function ($balances) {
                    return $balances->warehouse_name();
                })
                ->addColumn('barcode', function ($balances) {
                    return $balances->barcode;
                })
                ->addColumn('stock_qty', function ($balances) {
                    $stock =  $balances->stock_qty;
                    $uom =  $balances->qty_per_unit;

                    $qty = (float)($stock / $uom);
                    $c_qty = (float)($stock % $uom);
                    $unit = findUnit($balances->product->unit_id ?? 0)->unit ?? '';
                    $cunit = findUnit($balances->product->child_unit ?? 0)->unit ?? '';

                    $text = $qty ? number_format($qty)." $unit , " : '';
                    $text .= $c_qty ? "$c_qty $cunit , " : '';

                    $stock_qty = $stock ? "<b>($stock $cunit)</b>" : '';

                    return rtrim($text, ' , ') . " $stock_qty";
                })
                ->addColumn('in_qty', function ($balances) {
                    return $balances->in_stock();
                })
                ->addColumn('cutting', function ($balances) {
                    return $balances->cutting();
                })
                ->addColumn('color', function ($balances) {
                    $color = findColor($balances->product->color_code);
                    if ($color) {
                        $name = $color->name ?? '';
                        $color = $color->color ?? 'transparent';
                        return "<i class='fa fa-stop border' style='border:1px solid rgba(1,1,1,0.4);color: $color'></i> $name";
                    }
                })
                ->addColumn('transfer', function ($balances) {
                    $product = $balances->product;
                    $unit = findUnit($product->unit_id ?? '');
                    $unit = $unit->unit ?? '';
                    $tran_in = TransferStockDetailModel::join('nso007_transfer_stock', 'nso007_transfer_stock_detail.transfer_id', 'nso007_transfer_stock.id')
                        ->where([
                            'nso007_transfer_stock_detail.product_id' => $balances->product_id,
                            'nso007_transfer_stock.to_warehouse_id' => $balances->warehouse_id,
                            'nso007_transfer_stock.type' => 'fabric',
                        ])
                        ->whereBetween('nso007_transfer_stock_detail.qty_per_unit',[$balances->qty_per_unit-0.01,$balances->qty_per_unit+0.01])
                        ->sum('nso007_transfer_stock_detail.qty');
                    $tran_out = TransferStockDetailModel::join('nso007_transfer_stock', 'nso007_transfer_stock_detail.transfer_id', 'nso007_transfer_stock.id')
                        ->where([
                            'nso007_transfer_stock_detail.product_id' => $balances->product_id,
                            'nso007_transfer_stock.from_warehouse_id' => $balances->warehouse_id,
                            'nso007_transfer_stock.type' => 'fabric',
                        ])
                        ->whereBetween('nso007_transfer_stock_detail.qty_per_unit',[$balances->qty_per_unit-0.01,$balances->qty_per_unit+0.01])
                        ->sum('nso007_transfer_stock_detail.qty');
                    $tran_in = $tran_in ? "<b class='text-success'>ចូល</b> : $tran_in $unit , " : '';
                    $tran_out = $tran_out ? "<b class='text-danger'>ចេញ</b> : $tran_out $unit" : '';
                    return rtrim("$tran_in $tran_out", ' , ');
                })
                ->rawColumns(['product_price', 'transfer', 'product_name', 'stock_qty', 'color'])
                ->make(true);
        }
        return view('administrator.report-stock.stock-fabric', compact('warehouses'));

    }

    public function report_stock_cut_piece(Request $request)
    {
        $warehouses = getWarehouse('tailor', 'cut_piece')->pluck('warehouse_name', 'id');
        if ($request->ajax()) {
            $search = $request->input('search.value');
            $wh = $request->warehouse;
            $branch = WarehouseModel::where('branch_id', Auth::user()->branch_id)->pluck('id');
            $count_total = StockModel::whereIn('warehouse_id', $branch)->where('type', 'cut_piece')->count();
            $balances = StockModel::select('nso007_products.product_name', 'nso007_stocks.*')
                ->join('nso007_products', 'nso007_products.id', 'nso007_stocks.product_id')
                ->whereIn('warehouse_id', $branch)
                ->where('nso007_stocks.type', 'cut_piece')
                ->where(function ($q) use ($wh) {
                    if ($wh) {
                        $q->where('nso007_stocks.warehouse_id', $wh);
                    }
                })
                ->where(function ($q) use ($search) {
                    if ($search) {
                        $q->orwhere('nso007_stocks.barcode', 'like', '%' . $search . '%');
                        $q->orwhere('nso007_products.product_name', 'like', '%' . $search . '%');
                        $q->orwhere('nso007_stocks.type', 'like', '%' . $search . '%');
                    }
                })
                ->orderBy('nso007_stocks.product_id', 'ASC');
            $count_filter = $balances->count();
            return Datatables::of($balances->take(20))
                ->with([
                    "recordsTotal" => $count_total,
                    "recordsFiltered" => $count_filter,
                ])
                ->addIndexColumn()
                ->editColumn('product_name', function ($balances) {
                    $p_unit = findProductUnitByCode($balances->barcode, 'cut_piece');
                    if ($p_unit) {
                        $size = findSize($p_unit->size_code);
                    } else {
                        $size = null;
                    }
                    return $balances->product_name . ' (' . __('administrator.size') . ' : ' . ($size ? $size->size : '') . ')';
                })
                ->addColumn('warehouse', function ($balances) {
                    return $balances->warehouse_name();
                })
                ->addColumn('barcode', function ($balances) {
                    return $balances->barcode;
                })
                ->addColumn('stock_qty', function ($balances) {
                    $pro = getProductById($balances->product_id);
                    $qty_alert = ($pro->qty_alert > 0 ? $pro->qty_alert : 0);
                    $color = 'black';
                    if ($balances->stock_qty <= $qty_alert && $balances->stock_qty > 0) {
                        $color = '#f1c40f';
                    }
                    if ($balances->stock_qty == 0) {
                        $color = '#c0392b';
                    }
                    return ' <span style = "color:' . $color . ';text-shadow:0px 0px 1px rgba(1,1,1,0.3)"><b>' . $balances->balance_qty() . '</b></span>';
                })
                ->addColumn('in_qty', function ($balances) {
                    return $balances->cutting_qty() ? ($balances->cutting_qty() . ' ' . __('administrator.pieces')) : '';
                })
                ->addColumn('production_out', function ($balances) {
                    $qty = $balances->use_cut_piece();
                    return $qty ? ($qty . ' ' . __('administrator.pieces')) : '';
                })
                ->rawColumns(['product_price' => 'product_price', 'action' => 'action', 'return_qty' => 'return_qty', 'production_qty' => 'production_qty', 'stock_qty' => 'stock_qty', 'production_out' => 'production_out', 'other_qty' => 'other_qty'])
                ->make(true);
        }
        return view('administrator.report-stock.stock-cut-piece', compact('warehouses'));

    }

    public function get_item_detail(Request $request)
    {
        $code = $request->prcode;
        $warehouse_hide = $request->warehouse_hide;
        $data = PurchaseDetailsModel::select('nso007_purchases.invoice_number', 'nso007_purchases.supplier_id',
            'nso007_purchases.purchase_date', 'nso007_purchases.type', 'nso007_purchase_details.*', 'nso007_purchases.warehouse_id')
            ->join('nso007_purchases', 'nso007_purchases.id', 'nso007_purchase_details.purchase_id')
            ->where(['nso007_purchases.status' => 1, 'nso007_purchases.type' => 'product', 'nso007_purchases.warehouse_id' => $warehouse_hide])
            ->where(function ($query) use ($code) {
                if ($code) {
                    $query->where('nso007_purchase_details.barcode', $code);
                }
            })
            ->latest()
            ->paginate(20);
        return view('administrator.report-stock.view-item-detail', compact('data'));
    }

    public function get_sale_detail(Request $request)
    {
        $code = $request->prcode;
        $warehouse_hide = $request->warehouse_hide;
        $data = SaleDetailsModel::select('nso007_sales.invoice_number', 'nso007_sales.customer_id',
            'nso007_sales.sale_date', 'nso007_sale_details.*', 'nso007_sales.warehouse_id')
            ->join('nso007_sales', 'nso007_sales.id', 'nso007_sale_details.sale_id')
            ->where('nso007_sales.warehouse_id', $warehouse_hide)
            ->where(function ($query) {
                $query->where('nso007_sales.status', '!=', 0);
//                $query->where('nso007_sales.delivery_status', 1);
//                $query->orwhere('nso007_sales.delivery_status', 3);
            })
            ->where(function ($query) use ($code) {
                if ($code) {
                    $query->where('nso007_sale_details.barcode', $code);
                }
            })
            ->latest()->paginate(15);
        return view('administrator.report-stock.view-item-sale-detail', compact('data'));
    }

    public function get_transfer_stock(Request $request)
    {
        $code = $request->prcode;
        $warehouse_hide = $request->warehouse_hide;
        $type_hidden = $request->type_hidden;
//        dd($request->all());
        $fname = '';
        if ($type_hidden === "in") {
            $fname = 'nso007_transfer_stock.to_warehouse_id';
        }
        if ($type_hidden === "out") {
            $fname = 'nso007_transfer_stock.from_warehouse_id';
        }
        $data = TransferStockDetailModel::selectRaw('nso007_transfer_stock_detail.barcode,nso007_transfer_stock_detail.qty,nso007_transfer_stock.*, "' . $type_hidden . '" as tr_type')
            ->join('nso007_transfer_stock', 'nso007_transfer_stock.id', 'nso007_transfer_stock_detail.transfer_id')
            ->where($fname, $warehouse_hide)
            ->where('nso007_transfer_stock_detail.barcode', $code)
            ->where('nso007_transfer_stock.type', 'item')
            ->where('nso007_transfer_stock.confirm_status', 1)
            ->latest()
            ->paginate(15);
        return view('administrator.report-stock.view-stock-transfer', compact('data'));
    }

    public function stock_report_by_date(Request $request)
    {

        $warehouses = getWarehouse('item')->pluck('warehouse_name', 'id');
        if ($request->ajax()) {
            $from_date = date('Y-m-d', strtotime(\Carbon\Carbon::now()->startOfMonth()->toDateString()));
            $to_date = date('Y-m-d', strtotime(\Carbon\Carbon::now()->EndOfMonth()->toDateString()));

            $warehouse = $request->warehouse;
            $product_id = $request->product_id;
            $count_total = StockModel::
            where(['type' => 'product'])
                ->count();
            $data = StockModel:: selectRaw('SUM(stock_qty) as balance ,warehouse_id,barcode,product_id')
                ->where(function ($query) use ($warehouse) {
                    if ($warehouse) {
                        $query->where('warehouse_id', $warehouse);
                    }
                })
                ->where(function ($query) use ($product_id) {
                    if ($product_id) {
                        $query->where('product_id', $product_id);
                    }
                })
                ->where(['type' => 'product'])
                ->groupBy('barcode')
                ->groupBy('warehouse_id')
                ->orderBy('barcode', 'ASC');
            $count_filter = $data->get()->count();
            return Datatables::of($data->take(20))
                ->with([
                    "recordsTotal" => $count_total,
                    "recordsFiltered" => $count_filter,
                ])
                ->editColumn('warehouse_id', function ($sale_detail) {
                    return findWarehouse($sale_detail->warehouse_id)->warehouse_name;
                })
                ->editColumn('product_id', function ($stock) {
                    $pro = findProductUnitByCode($stock->barcode, 'product');

                    $size = findSize($pro->size_code)->size ?? '';
                    $color = findColor($pro->color_code)->name ?? '';
                    return (getProductById($stock->product_id)->product_name ?? '') . ' ( ' . $size . ',' . $color . ' )';
                })
                ->editColumn('sale_date', function ($stock) use ($from_date) {
                    return date('F', strtotime($from_date));
                })
                ->addColumn('balance', function ($stock) {

                })
                ->addColumn('old_stock', function ($stock) use ($from_date, $warehouse) {
                    $old_stock = PurchaseDetailsModel::join('nso007_purchases', 'nso007_purchases.id', 'nso007_purchase_details.purchase_id')
                        ->selectRaw('sum(nso007_purchase_details.qty) as stock_qty')
                        ->where('nso007_purchases.type', 'product')
                        ->where('nso007_purchase_details.barcode', $stock->barcode)
                        ->whereDate('nso007_purchases.purchase_date', '<', $from_date)
                        ->where(function ($query) use ($stock) {
                            if ($stock->warehouse_id) {
                                $query->where('nso007_purchases.warehouse_id', $stock->warehouse_id);
                            }
                        })
                        ->first();
                    return ($old_stock->stock_qty ? ($old_stock->stock_qty . ' ' . $stock->unit) : 0);
                })
                ->addColumn('import', function ($stock) use ($from_date, $to_date) {
                    $saleD = PurchaseDetailsModel::selectRaw('SUM(qty) as in_qty')
                        ->join('nso007_purchases', 'nso007_purchases.id', 'nso007_purchase_details.purchase_id')
                        ->where(function ($query) use ($stock) {
                            if ($stock->warehouse_id) {
                                $query->where('nso007_purchases.warehouse_id', $stock->warehouse_id);
                            }
                        })
                        ->where('nso007_purchase_details.barcode', $stock->barcode)
                        ->where('nso007_purchase_details.status', 1)
                        ->where('nso007_purchases.status', 1)
                        ->where(function ($query) use ($from_date, $to_date) {
                            $query->whereDate('nso007_purchases.purchase_date', '>=', $from_date ? date('Y-m-d', strtotime($from_date)) : date('Y-m-d'));
                            $query->whereDate('nso007_purchases.purchase_date', '<=', $to_date ? date('Y-m-d', strtotime($to_date)) : date('Y-m-d'));
                        })
                        ->groupBy(['nso007_purchases.warehouse_id', 'nso007_purchase_details.barcode'])
                        ->orderBy('nso007_purchases.id', 'DESC')->first();
                    return ($saleD ? ($saleD->in_qty . ' ' . $stock->unit) : 0);
                })
                ->addColumn('export', function ($stock) use ($from_date, $to_date) {
                    $export = SaleDetailsModel::selectRaw('SUM(nso007_sale_details.qty) as sale_qty')
                        ->leftjoin('nso007_sales', 'nso007_sales.id', 'nso007_sale_details.sale_id')
                        ->where(['nso007_sales.status' => 1, 'nso007_sale_details.status' => 1])
                        ->where(function ($query) use ($stock) {
                            if ($stock->warehouse_id) {
                                $query->where('nso007_sales.warehouse_id', $stock->warehouse_id);
                            }
                        })
                        ->where(function ($query) use ($from_date, $to_date) {
                            $query->whereBetween('nso007_sales.sale_date', [$from_date . ' 00:00:00', $to_date . ' 23:59:59']);
                        })
                        ->where('barcode', $stock->barcode)
                        ->groupBy('nso007_sale_details.barcode')
                        ->first();
                    return $export ? ($export->sale_qty . ' ' . ($stock->unit)) : '';
                })
                ->addColumn('transfer', function ($stock) use ($from_date, $to_date) {
                    $tr_out = $stock->transfer_stock('out', $from_date, $to_date);
                    $tr_in = $stock->transfer_stock('in', $from_date, $to_date);
                    if ($tr_in) {
                        $btn_in = '<a class="under-line cursor show-stock-in"
                     data-url="' . route('get-item-transfer-stock-in-out-history') . '?prcode=' . $stock->barcode . '&warehouse_hide=' . $stock->warehouse_id . '&type_hidden=in"
                     >' . $tr_in . '</a>';
                    } else {
                        $btn_in = '';
                    }
                    if ($tr_out) {
                        $btn_out = '<a class="under-line cursor show-stock-in"
                     data-url="' . route('get-item-transfer-stock-in-out-history') . '?prcode=' . $stock->barcode . '&warehouse_hide=' . $stock->warehouse_id . '&type_hidden=out"
                     >' . $tr_out . '</a>';
                    } else {
                        $btn_out = '';
                    }
                    return $btn_in . ($btn_in && $btn_out ? ' | ' : '') . $btn_out;

                })
                ->addIndexColumn()
                ->rawColumns(['invoice_number' => 'invoice_number', 'import' => 'import', 'export' => 'export', 'transfer' => 'transfer', 'balance' => 'balance'])
                ->make(true);
        }
        return view('administrator.report-stock.stock-item-by-date', compact('warehouses'));

    }

}

