<?php

namespace Modules\AdvancedTransfer\Http\Controllers;

use App\Brands;
use App\Category;
use App\Http\Controllers\Controller;
use App\Product;
use App\SellingPriceGroup;
use App\TaxRate;
use App\Unit;
use App\Utils\ModuleUtil;
use App\Utils\ProductUtil;
use App\VariationLocationDetails;
use Illuminate\Http\Request;
use Illuminate\Http\Response;
use Illuminate\Support\Facades\DB;
use Modules\AdvancedTransfer\Entities\NstSetting;
use Modules\AdvancedTransfer\Entities\NstVariationLocationDetails;
use Modules\AdvancedTransfer\Entities\Services\CategoryService;
use Modules\AdvancedTransfer\Entities\Services\LocationsService;
use Modules\AdvancedTransfer\Entities\Services\TransactionService;
use Yajra\DataTables\Facades\DataTables;

class TpsAddStockController extends Controller
{
    protected $moduleUtil;
    protected $locationsService;
    protected $categoryService;
    protected $transactionService;
    /**
     * @var int
     */
    private $allQtyAvailable;
    /**
     * @var float|int
     */
    private $avgPurchasePrice;

    public function __construct(TransactionService $transactionService, ProductUtil $productUtil, ModuleUtil $moduleUtil, LocationsService $locationsService, CategoryService $categoryService)
    {
        $this->transactionService = $transactionService;
        $this->categoryService = $categoryService;
        $this->locationsService = $locationsService;
        $this->productUtil = $productUtil;
        $this->moduleUtil = $moduleUtil;
    }

    /**
     * Add stock index.
     * @return Response
     */
    public function index()
    {
        if (!auth()->user()->can('tpsTransfer.access_add_stock_transfer')) {
            abort(403, 'Unauthorized action.');
        }

        $businessId = request()->session()->get('user.business_id');
        $setting = NstSetting::where('business_id', $businessId)->first();
        $setting = $setting ? $setting->value : [];
        $selling_price_group_count = SellingPriceGroup::countSellingPriceGroups($businessId);
        $is_woocommerce = $this->moduleUtil->isModuleInstalled('Woocommerce');

        $rack_enabled = (request()->session()->get('business.enable_racks') || request()->session()->get('business.enable_row') || request()->session()->get('business.enable_position'));

        $categories = Category::forDropdown($businessId, 'product');
        $brands = Brands::forDropdown($businessId);
        $brands->prepend(__('lang_v1.all_brands'), 'all');
        $units = Unit::forDropdown($businessId);
        $tax_dropdown = TaxRate::forBusinessDropdown($businessId, false);
        $taxes = $tax_dropdown['tax_rates'];

        $fromBusinessLocations = $this->locationsService->accessLocationsForUserforDropdown($businessId, 'from', false, false, 'add');
        $toBusinessLocations = $this->locationsService->accessLocationsForUserforDropdown($businessId, 'to', false, false, 'add');

        if ($this->moduleUtil->isModuleInstalled('Manufacturing') && (auth()->user()->can('superadmin') || $this->moduleUtil->hasThePermissionInSubscription($businessId, 'manufacturing_module'))) {
            $show_manufacturing_data = true;
        } else {
            $show_manufacturing_data = false;
        }

        return view('tpsTransfer::add')->with(compact(
            'rack_enabled',
            'categories',
            'brands',
            'units',
            'taxes',
            'fromBusinessLocations',
            'toBusinessLocations',
            'show_manufacturing_data',
            'setting'
        ));
    }

    /**
     * index datatable query and modification
     * @return mixed
     * @throws \Exception
     */
    public function table(){
        if (!auth()->user()->can('tpsTransfer.access_add_stock_transfer')) {
            abort(403, 'Unauthorized action.');
        }
        $businessId = request()->session()->get('user.business_id');

        $fromLocationId = request()->get('from_location_id', null);
        $toLocationId = request()->get('to_location_id', null);

        // Cast location IDs to integers to prevent SQL injection
        if ($fromLocationId) {
            $fromLocationId = array_map('intval', (array) $fromLocationId);
            $fromLocationId = array_filter($fromLocationId); // remove zeros
        }

        $requestPvids = request()->get('pvid', []);
        if (is_string($requestPvids)) {
            $requestPvids = json_decode($requestPvids, true) ?: [];
        }

//            //fortesting
//            $fromLocationId = [
//                1 => 2,
//                2 => 3,
//                3 => 4,
//                4 => 5,
//                5 => 6,
//            ];
//            $toLocationId = 1;
//            $requestPvids = ['2825-2825', '245-245', '301-301', '445-445', '1205-1205', '1271-1271', '1278-1278', '1295-1295', '1-1', '782-782', '742-742', '521-521', '7213-7213', '702-702', '8265-8265', '362-362', '1537-1537', '137-137', '7456-7456', '135-135', '136-136', '351-351', '138-138', '5124-5124', '104-104', '4303-4303', '356-356', '359-359', '4304-4304', '5983-5983', '5925-5925', '1781-1781', '5179-5179', '351-351', '9246-9245'];
        //fortesting

        $pvid = [];
        foreach ($requestPvids as $value){
            $ids = explode("-", $value);
            $array = [];
            $array['product_id'] = $ids[0];
            $array['variationId'] = $ids[1];
            $pvid[] = $array;
        }

        $query = Product::with(['media'])
            ->where('products.business_id', $businessId)
            ->whereIn('products.id', array_column($pvid, 'product_id'))
            ->leftJoin('brands', 'products.brand_id', '=', 'brands.id')
            ->join('units', 'products.unit_id', '=', 'units.id')
            ->leftJoin('categories as c1', 'products.category_id', '=', 'c1.id')
            ->leftJoin('categories as c2', 'products.sub_category_id', '=', 'c2.id')
            ->join('variations as v', 'v.product_id', '=', 'products.id')
            ->join('product_variations as pv', 'pv.id', '=', 'v.product_variation_id')
            ->leftjoin('purchase_lines as pl', 'v.id', '=', 'pl.variation_id')
            ->whereIn('v.id', array_column($pvid, 'variationId'))
            ->leftJoin('variation_location_details as vld', 'vld.variation_id', '=', 'v.id')
            ->leftJoin('variation_location_details as to_vld',function($join) use ($toLocationId) {
                $join->on('vld.variation_id','=','to_vld.variation_id');
                if($toLocationId){
	                $join->whereRaw("to_vld.location_id = {$toLocationId}");
                }
            })
            ->leftjoin('business_locations as bl', 'vld.location_id', '=', 'bl.id' )
            ->where('vld.qty_available', '>', 0);

        //start - Add product table filters
        //Filter by location
        $location_id = request()->get('location_id', null);
        $permitted_locations = auth()->user()->permitted_locations();
        if (!empty($location_id) && $location_id != 'none') {
            if ($permitted_locations == 'all' || in_array($location_id, $permitted_locations)) {
                $query->whereHas('product_locations', function ($query) use ($location_id) {
                    $query->where('product_locations.location_id', '=', $location_id);
                });
            }
        } elseif ($location_id == 'none') {
            $query->doesntHave('product_locations');
        } else {
            if ($permitted_locations != 'all') {
                $query->whereHas('product_locations', function ($query) use ($permitted_locations) {
                    $query->whereIn('product_locations.location_id', $permitted_locations);
                });
            } else {
                $query->with('product_locations');
            }
        }
        //end - Add product table filters


        if ($fromLocationId && is_array($fromLocationId)) {
            $query->whereIn('vld.location_id', $fromLocationId);
        }

        $filterFromLocationId = request()->get('filter_from_location_id', null);
        if ($filterFromLocationId && is_array($filterFromLocationId)) {
            $query->whereIn('vld.location_id', $filterFromLocationId);
        }

        if ($toLocationId) {
            $query->whereHas('product_locations', function ($query) use ($toLocationId) {
                $query->where('product_locations.location_id', $toLocationId);
            });
        }

//        $query->where('to_vld.location_id', $toLocationId);

        $products = $query->select(
            'products.id',
            'products.name as product',
            'v.name as variation_name',
            'pv.name as product_variation_name',
            'products.type',
            'c1.name as category',
            'c2.name as sub_category',
            'units.actual_name as unit',
            'brands.name as brand',
            'products.id as product_id',
            'products.sku',
            'products.image',
            'products.product_custom_field1',
            'products.product_custom_field2',
            'products.product_custom_field3',
            'products.product_custom_field4',
            'vld.variation_id',
            'bl.id as bl_id',
            'bl.name as bl_name',
            DB::raw('vld.qty_available as from_stock'),
            DB::raw('ifnull(to_vld.qty_available,0) as to_stock'),
            DB::raw("IfNull(bl.custom_field1, 1) as custom_field"),
            DB::raw("(
                SELECT COALESCE(SUM(npp.transfer_quantity), 0)
                FROM nst_prepare_products npp
                WHERE npp.product_id = products.id
                  AND npp.variation_id = v.id
                  AND npp.from_location_id = vld.location_id
                  AND npp.status IN ('prepare', 'receive')
                  AND npp.deleted_at IS NULL
            ) as committed_qty")
        );

        if (!request()->get('order')) {
            $products->orderBy('to_stock', 'ASC')->orderBy('products.id', 'ASC');
            if ($fromLocationId) {
                $locationIds = array_filter(array_map('intval', $fromLocationId), function ($id) {
                    return $id > 0;
                });
                if (!empty($locationIds)) {
                    $products->orderByRaw('FIELD(vld.location_id, '.implode(', ', $locationIds).')');
                }
            }
        }
        $products->groupBy('vld.id');

        //start - Add product table filters
        $type = request()->get('type', null);
        if (!empty($type)) {
            $products->where('products.type', $type);
        }

        $category_id = request()->get('category_id', null);
        if (!empty($category_id)) {
            $products->where('products.category_id', $category_id);
        }

        $sub_category_id = request()->get('sub_category_id', null);
        if (!empty($sub_category_id)) {
            $products->where('products.sub_category_id', $sub_category_id);
        }

        $brand_id = request()->get('brand_id', null);
        if (!empty($brand_id)) {
            $products->where('products.brand_id', $brand_id);
        }

        //end - Add product table filters

        //workaround to get the vld qty to avoid mismatch qty between lots result and the main query
        $productToLocationStock = VariationLocationDetails::whereIn('product_id', array_column($pvid, 'product_id'))->whereIn('variation_id', array_column($pvid, 'variationId'))->where('location_id', $toLocationId)->select('product_id','variation_id','qty_available')->get()->toArray();

        if(count($productToLocationStock) > 0){
            foreach($productToLocationStock as $stock){
                $stockQty[$stock['product_id'].'-'.$stock['variation_id']] = intval($stock['qty_available']);
            }
        }else{
            $stockQty = [];
        }

        $lotDataBatch = collect();
        // NOTE: pluck() does not override existing SELECT columns set via select(),
        // so we must use the exact key names produced by the SELECT aliases:
        //   - 'vld.variation_id'  -> result key 'variation_id'
        //   - 'bl.id as bl_id'    -> result key 'bl_id'
        $variationIds = (clone $products)->pluck('variation_id')->unique()->filter()->toArray();
        $locationIds = (clone $products)->pluck('bl_id')->unique()->filter()->toArray();
        if (!empty($variationIds) && !empty($locationIds)) {
            $lotDataBatch = $this->transactionService->getLotDataBatch($variationIds, $businessId, $locationIds);
        }

        return Datatables::of($products)
            ->editColumn('category', '{{$category}} @if(!empty($sub_category))<br/> -- {{$sub_category}}@endif')
            ->addColumn('pvl', function ($row) {
                return $this->getCombinationNumber($row);
            })
            ->addColumn('pv', function ($row) {
                return $row->id . '-' . $row->variation_id;
            })
            ->addColumn('data-href', function ($row){
                return action('\\App\Http\Controllers\ProductController@view', [$row->product_id]);
            })
            ->addColumn('productEdited', function ($row) use ($businessId, $lotDataBatch) {
                $name = $row->product;
                if ($row->variation_name && $row->variation_name != 'DUMMY') {
                    $name .= ' (' . $row->variation_name . ')';
                }

                $pvl = $this->getCombinationNumber($row);
                //add lot select list
                $this->allQtyAvailable = 0;
                $this->avgPurchasePrice = 0;
                $avgPurchasePrice = 0;
                $count = 0;
                $select = "<br><select class='form-control select2 lot-numbers product-row-input' id='lot_number[{$pvl}]' data-name='purchase_lines_id' name='data[{$pvl}][purchase_lines_id]' data-pvl='{$pvl}'>";
                $options = "";
                $lotNumbers = $lotDataBatch->get($row->variation_id . '_' . $row->bl_id, collect());

                if ($lotNumbers->isEmpty()) {
                    // Fall back to the physical location stock so the user can still enter a transfer quantity
                    $this->allQtyAvailable = intval($row->from_stock);
                    return $name;
                }
                foreach ($lotNumbers as $lotNumber){
                    $lotNumberValue = $lotNumber->lot_number ? $lotNumber->lot_number : '(P) '.$lotNumber->purchase_price ;
                    $options .= " <option value='{$lotNumberValue}' data-lot-id='{$lotNumber->purchase_line_id}' data-purchase-price='{$lotNumber->purchase_price}' data-qty-available='{$lotNumber->qty_available}' >{$lotNumberValue}</option>";
                    $this->allQtyAvailable += $lotNumber->qty_available;
                    $avgPurchasePrice += $lotNumber->purchase_price * $lotNumber->qty_available;
                    $count += $lotNumber->qty_available;
                }
                $this->avgPurchasePrice = $count > 0 ? round($avgPurchasePrice / $count, 2) : 0;
                $options = "<option selected value='all' data-lot-id='' data-purchase-price='{$this->avgPurchasePrice}' data-qty-available='{$this->allQtyAvailable}'>".__('lang_v1.lot_n_expiry')."</option>" . $options;
                $select = $select . $options ."</select>";

                return $name.$select;
            })
            ->editColumn('to_stock', '{{@number_format($to_stock)}}')
            ->editColumn('image', function ($row) {
                return '<div style="display: flex;"><img src="' . $row->image_url . '" alt="Product image" class="product-thumbnail-small"></div>';
            })
            ->addColumn('purchase_price', function ($row) use($businessId) {
                return "<span class='purchase-price' id='purchase-price-".$this->getCombinationNumber($row)."'>".($this->avgPurchasePrice)."</span>";
            })
            ->addColumn('from_stock_span', function ($row) use($businessId) {
                return "<span class='current-stock-qty' id='qty-".$this->getCombinationNumber($row)."'>".intval($row->from_stock)."</span>";
            })
            ->editColumn('type', '@lang("lang_v1." . $type)')
            ->editColumn('stock_transfer', function ($row){
                $pvl = $this->getCombinationNumber($row);
                $html = "<input id='transfer_quantity[{$pvl}]' data-pvl='{$pvl}' data-name='transfer_quantity' class='form-control product-row-input transfer_quantity' style='width:100%' type='number' min='1' max='".intval($this->allQtyAvailable)."' value='0'>";
                $html .= "<input type='hidden' name='data[{$pvl}][product_id]' value='{$row->id}'>";
                $html .= "<input type='hidden' name='data[{$pvl}][variation_id]' value='{$row->variation_id}'>";
                $html .= "<input type='hidden' name='data[{$pvl}][location_id]' value='{$row->bl_id}'>";

                return $html;
            })
            ->filterColumn('products.sku', function ($query, $keyword) {
                $query->whereHas('variations', function($q) use($keyword){
                    $q->where('sub_sku', 'like', "%{$keyword}%");
                })
                    ->orWhere('products.sku', 'like', "%{$keyword}%");
            })
            ->setRowId(function ($row){
                return 'tr-'.$this->getCombinationNumber($row);
            })
            ->setRowAttr([
                'data-href' => function ($row) {
                    if (auth()->user()->can("product.view")) {
                        return  action('\\App\Http\Controllers\ProductController@view', [$row->id]) ;
                    } else {
                        return '';
                    }
                },
                'class' => function ($row) use ($stockQty){
                        if(\Arr::get($stockQty, $row->id.'-'.$row->variation_id, 0) == 0){
                        return 'bg-red';
                    }
                    return '';
                }
            ])
            ->addColumn('actions', function ($row){
                $pvl = $this->getCombinationNumber($row);
                $html = "
                <div class='btn-group'>
                    <div class='dropdown actions-list'>
                        <button class='btn btn-primary dropdown-toggle' type='button' data-toggle='dropdown'  id='actions_btn_{$pvl}'>".__('messages.actions')."</button>
                        <div class='dropdown-menu dropdown-menu-right'>
                            <label for='important_{$pvl}' class='dropdown-item'><input type='checkbox' class='product-row-input' value='1' id='important_{$pvl}' data-pvl='{$pvl}' data-name='is_important'  name='data[{$pvl}][is_important]'>  Important</label>
                            <div class='dropdown-divider'></div>
                        <li><a target='_blank' href='".action('\Modules\AdvancedTransfer\Http\Controllers\TpsLabelsController@show', ['product_id' => $row->product_id]) ."' data-toggle='tooltip' title='" . __('lang_v1.label_help') . "'><i class='fa fa-barcode'></i> " . __('barcode.labels') . "</a></li>
                            <li><a data-toggle='modal' data-target='#add_notes' id='note_{$pvl}' data-pvl='{$pvl}' class='btn dropdown-item add_notes' type='button'>".__('tpsTransfer::lang.note')."</a></li>
                            ";
                if (auth()->user()->can('tpsTransfer.can_delete_nst_row')) {
                    $html .= "<li><a data-toggle='modal' data-target='#delete_confirm' id='delete_{$pvl}' data-pvl='{$pvl}' class='btn dropdown-item delete_row_button' type='button'>".__('messages.delete')."</a></li>";
                }
                $html .= "
                        </div>
                    </div>
                </div>";
                return $html;
            })
            ->addColumn('committed_qty_display', function ($row) {
                if (isset($row->committed_qty) && $row->committed_qty > 0) {
                    return '<span class="label label-warning" title="' . __('tpsTransfer::lang.committed_to_other_transfers') . '">'
                        . __('tpsTransfer::lang.in_transit') . ': '
                        . $row->committed_qty
                        . '</span>';
                }
                return '';
            })
            ->editColumn('from_stock_span', function ($row) {
                $committed = $row->committed_qty ?? 0;
                $effective = $row->from_stock - $committed;

                if ($committed > 0) {
                    return '<span title="' . __('tpsTransfer::lang.effective_available') . '">'
                        . $effective
                        . ' <small class="text-muted">(' . $row->from_stock . ' - ' . $committed . ')</small>'
                        . '</span>';
                }
                return "<span class='current-stock-qty' id='qty-".$this->getCombinationNumber($row)."'>".intval($row->from_stock)."</span>";
            })
            ->rawColumns(['product', 'productEdited', 'purchase_price', 'category', 'from_stock_span', 'stock_transfer', 'actions', 'committed_qty_display'])
            ->make(true);
    }

    /**
     * get temporary products from auto add method
     * @return false|string
     */
    public function autoTempProduct(){
        if (!auth()->user()->can('tpsTransfer.can_access_auto_add_products')) {
            return false;
        }
        $businessId = request()->session()->get('user.business_id');

        $toLocationId = request()->get('to_location_id', null); // location Y
        $toLocationIdInt = intval($toLocationId); // safe int for raw subquery interpolation
        $categoryId = request()->get('category_id', null);
        $subCategoryId = request()->get('sub_category_id', null);
        $brandId = request()->get('brand_id', null);
        $sellingPeriod = request()->get('selling_period', 0);
        $sellingPeriodInMinutes = intval($sellingPeriod) * 1440;
        $futurePeriod = request()->get('future_period', 0);

        // var_dump($businessId);
        // var_dump($toLocationId);
        // var_dump($categoryId);
        // var_dump($subCategoryId);
        // var_dump($brandId);
        // var_dump($sellingPeriod);
        // var_dump($futurePeriod);
        // exit;

        /**
         * The main purpose of this query is to get the products:
         *
         * 1. Products must be in the product_locations table for a specific location ($toLocationId)
         * 2. Amount of product exists from vld table for a specific location ($toLocationId) 0 if not exists
         * 3. Products sold in a specified period ($salePeriod) for a specific location ($toLocationId)
         *    and get 0 sold quantity if not sold
         */

        /**
         * transaction_sell_lines query to get total_sold
         */
        $tsl_query =
            DB::raw("(Select
                Coalesce(Sum(transaction_sell_lines.quantity - transaction_sell_lines.quantity_returned), 0) As total_sold,
                transactions.location_id As location_id,
                transaction_sell_lines.product_id,
                transaction_sell_lines.variation_id
            From
                transaction_sell_lines Inner Join
                transactions On transaction_sell_lines.transaction_id = transactions.id
            Where
                transactions.transaction_date BETWEEN Date_Sub(CurDate(), Interval $sellingPeriodInMinutes Minute) AND addtime(CurDate(), '23:59:59')
            Group By
                transactions.location_id,
                transaction_sell_lines.product_id,
                transaction_sell_lines.variation_id) AS tsl");

        $query = DB::table('product_locations')
            ->select(
                'variations.product_id',
                'variations.id as variation_id',
                DB::raw("Concat(product_locations.product_id, '-', variations.id) As pv"),
                DB::raw("Coalesce(total_sold, 0) as total_sold"), 'product_locations.location_id',
                DB::raw("Coalesce(variation_location_details.qty_available, 0) As to_stock"),
                DB::raw("IfNull(business_locations.custom_field1, 1) as custom_field"),
                DB::raw("(
                    SELECT COALESCE(SUM(npp.transfer_quantity), 0)
                    FROM nst_prepare_products npp
                    WHERE npp.product_id = product_locations.product_id
                      AND npp.variation_id = variations.id
                      AND npp.to_location_id = {$toLocationIdInt}
                      AND npp.status IN ('prepare', 'receive')
                      AND npp.deleted_at IS NULL
                ) as in_transit_qty"),
                "product_locations.location_id"
            )
            ->addSelect(DB::raw("{$sellingPeriod} as sellingPeriod"))
            ->addSelect(DB::raw("{$futurePeriod} as futurePeriod"))
            ->join('variations', 'product_locations.product_id', '=', 'variations.product_id')
            ->join('products', 'variations.product_id', '=', 'products.id')
            ->join('business_locations', 'product_locations.location_id', '=', 'business_locations.id')
            ->leftJoin($tsl_query, function ($join) {
                $join
                    ->on('tsl.location_id', '=', 'product_locations.location_id')
                    ->on('tsl.product_id', '=', 'product_locations.product_id')
                    ->on('tsl.variation_id', '=', 'variations.id');
            })
            ->leftJoin('variation_location_details', function ($join) {
                $join
                    ->on('variation_location_details.variation_id', '=', 'variations.id')
                    ->on('variation_location_details.product_id', '=', 'product_locations.product_id')
                    ->on('variation_location_details.location_id', '=', 'product_locations.location_id');
            })
	        ->where('products.type', '!=', 'modifier')
	        ->where('product_locations.location_id', $toLocationId)
            ->where('products.business_id', $businessId);

        if ($categoryId && $categoryId != 'all') {
            if ($subCategoryId) {
                $query->where('products.sub_category_id', $subCategoryId);
            }
            if ($categoryId) {
                $query->where('products.category_id', $categoryId);
            }
        }

        if ($brandId && $brandId != 'all') {
            $query->where('products.brand_id', $brandId);
        }

// print_r($tsl_query->get());
// print_r($query->get());
//print_r($this->getSqlWithBindings($query));
// exit;
        return json_encode($query->get()->keyBy('pv'));
    }

    /**
     * get temporary products from manual add method modal
     * @param Request $request
     * @return \Illuminate\Contracts\Foundation\Application|\Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
    public function manualTempProduct(Request $request){
        $businessId = request()->session()->get('user.business_id');
        $categories = Category::forDropdown($businessId, 'product');
        $fromLocation = request()->get('from_location', null);
        $toLocation = request()->get('to_location', null);
        $brands = Brands::forDropdown($businessId);
        $brands->prepend(__('lang_v1.all_brands'), 'all');

        return view('tpsTransfer::add_product')->with(compact(
            'categories',
            'brands',
            'fromLocation',
            'toLocation'
        ));
    }

    /**
     * get temporary products from manual add method table
     * @return mixed
     * @throws \Exception
     */
    public function manualTempProductTable(){
        $businessId = request()->session()->get('user.business_id');
        $fromLocationId = request()->get('from_location_id', []) ?: [];
	    $toLocationId = request()->get('to_location_id', null);
        $categoryId = request()->get('category_id', null);
        $subCategoryId = request()->get('sub_category_id', null);
        $brandId = request()->get('brand_id', null);

        $query = NstVariationLocationDetails::query()
		    ->join('variations',function($join) {
			    $join->on('variations.id', '=', 'variation_location_details.variation_id')->whereRaw('variations.product_id = variation_location_details.product_id');
		    })
		    ->join('product_variations as pv', 'variations.product_variation_id', '=', 'pv.id')
		    ->join('products','products.id','=','variations.product_id')
            ->where('products.business_id', $businessId)
            ->leftJoin('variation_location_details as vld_to',function($join) use ($toLocationId){
                $join
	                ->on('variations.id', '=', 'vld_to.variation_id')
                     ->whereRaw('variations.product_id = vld_to.product_id')
                     ->where('vld_to.location_id', '=', $toLocationId)
                ;
            })
            ->whereNull('variations.deleted_at')
		    ->leftJoin('units','products.unit_id','=','units.id')
		    ->leftJoin('brands', 'products.brand_id', '=', 'brands.id')
		    ->leftJoin('categories as c1', 'products.category_id', '=', 'c1.id')
		    ->leftJoin('categories as c2', 'products.sub_category_id', '=', 'c2.id')
            ->join('product_locations',function($join) {
                $join->on('variation_location_details.product_id', '=', 'product_locations.product_id')->whereRaw('product_locations.location_id = variation_location_details.location_id');
            })
            ->where('products.type', '!=', 'modifier')
            ->whereIn('variation_location_details.location_id',$fromLocationId)
            ->where('variation_location_details.qty_available','>',0)
            ->groupBy(['vld_to.qty_available','products.id','variation_location_details.product_id','products.name','products.type','variations.id','variations.name'])
			->select(
			    'products.id',
			    'products.name as product',
			    'products.type',
			    'variations.sub_sku as sku',
			    'products.image',
			    'products.enable_stock',
			    'products.is_inactive',
			    'products.not_for_selling',
			    'products.product_custom_field1',
			    'products.product_custom_field2',
			    'products.product_custom_field3',
			    'products.product_custom_field4',
			    'c1.name as category',
			    'c2.name as sub_category',
			    'units.actual_name as unit',
			    'brands.name as brand',
			    'variations.id as variation_id',
			    'variations.name as variation',
			    'pv.name as product_variation',
				DB::raw( 'Sum(variation_location_details.qty_available) As current_stock'),
                'vld_to.qty_available As to_current_stock'
            );

        if ($categoryId && $categoryId != 'all') {
            if ($subCategoryId) {
                $query->where('products.sub_category_id', $subCategoryId);
            }
            if ($categoryId) {
                $query->where('products.category_id', $categoryId);
            }
        }

        if ($brandId && $brandId != 'all') {
            $query->where('products.brand_id', $brandId);
        }

//        print_r(self::getSqlWithBindings($query));
//        exit;
        return Datatables::of($query)
            ->addColumn('checkbox', function ($row){
                return '<input data-pvid="'.$row->id.'-'.$row->variation_id.'" class="pvidCheckbox" type="checkbox">';
            })
            ->editColumn('product', function ($row){
                $name = $row->product;
                if ($row->type == 'variable') {
                    $name .= ' - ' . $row->product_variation . '-' . $row->variation;
                }
                return $name;
            })
            ->editColumn('category', '{{$category}} @if(!empty($sub_category))<br/> -- {{$sub_category}}@endif')
            ->editColumn('current_stock', '@if($enable_stock == 1) {{@number_format($current_stock)}} @else -- @endif {{$unit}}')
            ->editColumn('to_current_stock', '{{@number_format($to_current_stock)}} {{$unit}}')
            ->filterColumn('products.sku', function ($query, $keyword) {
                $query->whereHas('product.variations', function($q) use($keyword){
                    $q->where('sub_sku', 'like', "%{$keyword}%");
                })
                    ->orWhere('products.sku', 'like', "%{$keyword}%");
            })
            ->rawColumns(['checkbox', 'action', 'image', 'mass_delete', 'category'])
            ->make(true);
    }

    private function getCombinationNumber($row)
    {
        return $row->id . '-' . $row->variation_id . '-' . $row->bl_id;
    }

    public static function getSqlWithBindings($query)
    {
        return vsprintf(str_replace('?', '%s', $query->toSql()), collect($query->getBindings())->map(function ($binding) {
            return is_numeric($binding) ? $binding : "'{$binding}'";
        })->toArray());
    }

    public function showCategory()
    {
        $categories = DB::table('categories')->where('parent_id',0)->get();
        return $categories;
    }

    public function showSubCategory()
    {
        $sub_categories = DB::table('categories')->where('parent_id','<>',0)->get();
        return $sub_categories;
    }

}
