diff --git a/Http/Controllers/TpsAddStockController.php b/Http/Controllers/TpsAddStockController.php
index 3e9c4a8..3fec07e 100644
--- a/Http/Controllers/TpsAddStockController.php
+++ b/Http/Controllers/TpsAddStockController.php
@@ -107,6 +107,12 @@ class TpsAddStockController extends Controller
         $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', []);
 
 //            //fortesting
@@ -214,13 +220,27 @@ class TpsAddStockController extends Controller
             '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("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){
-                $query->orderByRaw('FIELD(vld.location_id, '.implode(', ', $fromLocationId).')');
+            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');
@@ -360,16 +380,28 @@ class TpsAddStockController extends Controller
                 </div>";
                 return $html;
             })
-            ->addColumn('pvl', function ($row){
-                return $this->getCombinationNumber($row);
-            })
-            ->addColumn('custom_field', function ($row){
-                return $row->custom_field;
+            ->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 '';
             })
-            ->addColumn('pv', function ($row){
-                return $row->id.'-'.$row->variation_id;
+            ->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'])
+            ->rawColumns(['product', 'productEdited', 'purchase_price', 'category', 'from_stock_span', 'stock_transfer', 'actions', 'committed_qty_display'])
             ->make(true);
     }
 
@@ -391,14 +423,14 @@ class TpsAddStockController extends Controller
         $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;
+        // 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:
@@ -472,9 +504,10 @@ class TpsAddStockController extends Controller
             $query->where('products.brand_id', $brandId);
         }
 
-//print_r($query->get());
+// print_r($tsl_query->get());
+// print_r($query->get());
 //print_r($this->getSqlWithBindings($query));
-//exit;
+// exit;
         return json_encode($query->get()->keyBy('pv'));
     }
 
diff --git a/Http/Controllers/TpsPrepareStockController.php b/Http/Controllers/TpsPrepareStockController.php
index b7ee54b..2e22a66 100644
--- a/Http/Controllers/TpsPrepareStockController.php
+++ b/Http/Controllers/TpsPrepareStockController.php
@@ -92,33 +92,32 @@ class TpsPrepareStockController extends Controller
         $query = NstPrepareStock::query()
             ->where('nst_prepare_products.status', 'prepare')
             ->where('nst_prepare_products.business_id', $businessId)
-            ->leftJoin('users','users.id','=','nst_prepare_products.created_by')
-            ->leftJoin('business_locations as from_business_locations','from_business_locations.id','=','nst_prepare_products.from_location_id')
-            ->leftJoin('products','products.id','=','nst_prepare_products.product_id')
+            ->leftJoin('users', 'users.id', '=', 'nst_prepare_products.created_by')
+            ->leftJoin('business_locations as from_business_locations', 'from_business_locations.id', '=', 'nst_prepare_products.from_location_id')
+            ->leftJoin('products', 'products.id', '=', 'nst_prepare_products.product_id')
             ->leftJoin('categories as c1', 'products.category_id', '=', 'c1.id')
             ->leftJoin('categories as c2', 'products.sub_category_id', '=', 'c2.id')
             ->leftJoin('brands', 'products.brand_id', '=', 'brands.id')
             ->join('units', 'products.unit_id', '=', 'units.id')
             ->leftJoin('tax_rates', 'products.tax', '=', 'tax_rates.id')
-            ->leftJoin('variations','variations.id','=','nst_prepare_products.variation_id')
+            ->leftJoin('variations', 'variations.id', '=', 'nst_prepare_products.variation_id')
             ->join('product_variations as pv', 'pv.id', '=', 'variations.product_variation_id')
-            ->leftJoin('purchase_lines','purchase_lines.id','=','nst_prepare_products.purchase_lines_id')
-            ->leftJoin('business_locations as to_business_locations','to_business_locations.id','=','nst_prepare_products.to_location_id')
+            ->leftJoin('purchase_lines', 'purchase_lines.id', '=', 'nst_prepare_products.purchase_lines_id')
+            ->leftJoin('business_locations as to_business_locations', 'to_business_locations.id', '=', 'nst_prepare_products.to_location_id')
             ->whereNull('nst_prepare_products.deleted_at')
-
-            ->leftJoin('variation_location_details as to_vld',function($join) {
-                $join->on('variations.id','=','to_vld.variation_id')
+            ->leftJoin('variation_location_details as to_vld', function ($join) {
+                $join->on('variations.id', '=', 'to_vld.variation_id')
                     ->whereRaw('to_vld.location_id = to_business_locations.id');
             })
-            ->leftJoin('variation_location_details as from_vld',function($join) {
-                $join->on('variations.id','=','from_vld.variation_id')
+            ->leftJoin('variation_location_details as from_vld', function ($join) {
+                $join->on('variations.id', '=', 'from_vld.variation_id')
                     ->whereRaw('from_vld.location_id = from_business_locations.id');
             });
 
         if (!empty($from_location_id) && $from_location_id != 'none') {
             $query->where('nst_prepare_products.from_location_id', '=', $from_location_id);
-        }else{
-	        $query->whereRaw( '1 = 2' );
+        } else {
+            $query->whereRaw('1 = 2');
         }
 
         if (!empty($to_location_id) && $to_location_id != 'none') {
@@ -148,15 +147,15 @@ class TpsPrepareStockController extends Controller
             'purchase_lines.id as purchase_line_id',
             'purchase_lines.lot_number',
             'purchase_lines.purchase_price',
-            'purchase_lines.quantity as purchase_lines_quantity',
+            DB::raw('(purchase_lines.quantity - purchase_lines.quantity_sold - purchase_lines.quantity_adjusted - purchase_lines.quantity_returned + purchase_lines.mfg_quantity_used) as purchase_lines_quantity'),
             'purchase_lines.purchase_price_inc_tax',
             'nst_prepare_products.transfer_quantity',
             'to_location_id',
             'to_business_locations.name as to_location_name',
             'nst_prepare_products.is_important',
             'nst_prepare_products.note',
-	        'nst_prepare_products.created_at',
-	        DB::raw('to_vld.qty_available as to_qty_available'),
+            'nst_prepare_products.created_at',
+            DB::raw('to_vld.qty_available as to_qty_available'),
             DB::raw('from_vld.qty_available as from_qty_available')
         );
         if (!request()->get('order')) {
@@ -195,44 +194,50 @@ class TpsPrepareStockController extends Controller
         }
 
         return Datatables::of($query)
-	        ->editColumn('created_at', '{{@format_datetime($created_at)}}')
-	        ->addColumn('data-href', function ($row){
+            ->editColumn('created_at', '{{@format_datetime($created_at)}}')
+            ->addColumn('data-href', function ($row) {
                 return action('\\App\Http\Controllers\ProductController@view', [$row->product_id]);
             })
-            ->addColumn('row_checkbox', function ($row){
+            ->addColumn('row_checkbox', function ($row) {
                 return "<input type='checkbox' class='product-row-input' value='1' data-id='{$row->id}' data-name='accepted'> ";
             })
-            ->editColumn('from_location_name', function ($row){
+            ->editColumn('from_location_name', function ($row) {
                 $from_qty_available = $row->from_qty_available;
-                // if($row->purchase_line_id){
-                //     $from_qty_available = $row->purchase_lines_quantity;
-                // }
-                return $row->from_location_name .' ('.intval($from_qty_available).')';
+                if($row->purchase_line_id){
+                    $from_qty_available = $row->purchase_lines_quantity;
+                }
+                return $row->from_location_name . ' (' . intval($from_qty_available) . ')';
             })
-            ->editColumn('to_location_name', function ($row){
-                return $row->to_location_name .' ('.intval($row->to_qty_available).')';
+            ->editColumn('to_location_name', function ($row) {
+                return $row->to_location_name . ' (' . intval($row->to_qty_available) . ')';
             })
-            ->addColumn('transfer_quantity_input', function ($row){
+            ->editColumn('from_qty_available', function ($row) {
+                if ($row->purchase_line_id) {
+                    return $row->purchase_lines_quantity;
+                }
+                return $row->from_qty_available;
+            })
+            ->addColumn('transfer_quantity_input', function ($row) {
                 $from_qty_available = $row->from_qty_available;
-                if($row->purchase_line_id){
+                if ($row->purchase_line_id) {
                     $from_qty_available = $row->purchase_lines_quantity;
                 }
 
-                $html = "<input id='tQty_{$row->id}' data-id='{$row->id}' data-name='transfer_quantity' class='form-control product-row-input transfer_quantity row-select' style='width:100%' type='number' min='1' max='".intval($from_qty_available)."' value='".intval($row->transfer_quantity)."'>";
+                $html = "<input id='tQty_{$row->id}' data-id='{$row->id}' data-name='transfer_quantity' class='form-control product-row-input transfer_quantity row-select' style='width:100%' type='number' min='1' max='" . intval($from_qty_available) . "' value='" . intval($row->transfer_quantity) . "'>";
 
                 return $html;
-            })
-            ->addColumn('actions', function ($row){
+            })//from_qty_available, purchase_lines_quantity
+            ->addColumn('actions', function ($row) {
                 $class = is_null($row->note) ? '' : 'btn-success';
                 $html = "
                 <div class='btn-group'>
                     <div class='dropdown actions-list'>
-                        <button class='btn btn-primary {$class} dropdown-toggle' type='button' data-toggle='dropdown' id='actions_btn_{$row->id}'>".__('messages.actions')."</button>
+                        <button class='btn btn-primary {$class} dropdown-toggle' type='button' data-toggle='dropdown' id='actions_btn_{$row->id}'>" . __('messages.actions') . "</button>
                         <div class='dropdown-menu dropdown-menu-right'>
-                        <li><a target='_blank' href='".action('\Modules\TpsTransfer\Http\Controllers\TpsLabelsController@show', ['product_id' => $row->product_id, 'qty' => intval($row->transfer_quantity), 'purchase_line_id' => intval($row->purchase_line_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_{$row->id}' data-id='{$row->id}' class='btn dropdown-item add_notes' type='button'>".__('tpsTransfer::lang.note')."</a></li>";
+                        <li><a target='_blank' href='" . action('\Modules\TpsTransfer\Http\Controllers\TpsLabelsController@show', ['product_id' => $row->product_id, 'qty' => intval($row->transfer_quantity), 'purchase_line_id' => intval($row->purchase_line_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_{$row->id}' data-id='{$row->id}' 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_{$row->id}' data-id='{$row->id}' class='btn dropdown-item delete_row_button' type='button'>".__('messages.delete')."</a></li>";
+                    $html .= "<li><a data-toggle='modal' data-target='#delete_confirm' id='delete_{$row->id}' data-id='{$row->id}' class='btn dropdown-item delete_row_button' type='button'>" . __('messages.delete') . "</a></li>";
                 }
                 $html .= "
                         </div>
@@ -252,7 +257,7 @@ class TpsPrepareStockController extends Controller
                 return '<div style="display: flex;"><img src="' . $row->image_url . '" alt="Product image" class="product-thumbnail-small"></div>';
             })
             ->editColumn('purchase_price', function ($row) use ($businessId) {
-                if($row->purchase_price){
+                if ($row->purchase_price) {
                     return round($row->purchase_price, 2);
                 }
 
@@ -262,12 +267,12 @@ class TpsPrepareStockController extends Controller
 
                 $lotNumbers = $this->transactionService->getLotDataFromVariation($row->variation_id, $businessId, $row->from_location_id, true);
 
-                if($lotNumbers){
+                if ($lotNumbers) {
                     foreach ($lotNumbers as $lotNumber) {
                         $avgPurchasePrice += $lotNumber->purchase_price * $lotNumber->qty_available;
                         $count += $lotNumber->qty_available;
                     }
-                    if($count){
+                    if ($count) {
                         $avgPurchasePrice = $avgPurchasePrice / $count;
                     }
                 }
@@ -278,7 +283,7 @@ class TpsPrepareStockController extends Controller
                 return intval($row->transfer_quantity);
             })
             ->filterColumn('products.sku', function ($query, $keyword) {
-                $query->whereHas('product.variations', function($q) use($keyword){
+                $query->whereHas('product.variations', function ($q) use ($keyword) {
                     $q->where('sub_sku', 'like', "%{$keyword}%");
                 })
                     ->orWhere('products.sku', 'like', "%{$keyword}%");
@@ -292,60 +297,101 @@ class TpsPrepareStockController extends Controller
                         return 'bg-red';
                     }
                     return '';
-                }
+                },
             ])
             ->rawColumns(['actions', 'row_checkbox', 'product', 'selling_price', 'purchase_price', 'category', 'transfer_quantity_input'])
             ->make(true);
     }
 
-    public function store(Request $request){
-        if(!$request->manualTpsData || count($request->manualTpsData) == 0){
+    public function store(Request $request)
+    {
+        if (!auth()->user()->can('tpsTransfer.access_prepare_stock_transfer')) {
+            abort(403, 'Unauthorized action.');
+        }
+
+        if (!$request->manualTpsData || count($request->manualTpsData) == 0) {
             return response()->json(['status' => 'error']);
         }
 
-        DB::beginTransaction();
+        try {
+            DB::beginTransaction();
+
+            $businessId = request()->session()->get('user.business_id');
+            $transactionDate = $this->productUtil->uf_date(request()->get('transaction_date', null), true);
+            $createdBy = auth()->user()->id;
+            $createdAt = now();
+            $updatedAt = now();
+            $isShippingCalculated = false;
+            $nstShippingId = null;
+
+            foreach (array_chunk(request()->get('manualTpsData', null), 500, true) as $chunkedData) {
+                $extraData = [];
+                foreach ($chunkedData as $index => $data) {
+                    $lotNumbers = $this->transactionService->getLotDataFromVariation(
+                        $chunkedData[$index]['variation_id'], $businessId,
+                        $chunkedData[$index]['from_location_id'], true
+                    );
+                    $chunkedData[$index]['business_id'] = $businessId;
+                    $chunkedData[$index]['transaction_date'] = $transactionDate;
+                    $chunkedData[$index]['created_by'] = $createdBy;
+                    $chunkedData[$index]['created_at'] = $createdAt;
+                    $chunkedData[$index]['updated_at'] = $updatedAt;
+                    $chunkedData[$index]['is_shipping_calculated'] = $isShippingCalculated;
+                    $chunkedData[$index]['nst_shipping_id'] = $nstShippingId;
+                    unset($chunkedData[$index]['custom_field']);
+
+                    if ($lotNumbers->count() > 1 && $chunkedData[$index]['transfer_quantity'] > $lotNumbers->first()->qty_available) {
+                        $remainingQuantity = $chunkedData[$index]['transfer_quantity'] - $lotNumbers->first()->qty_available;
+                        $chunkedData[$index]['transfer_quantity'] = $lotNumbers->first()->qty_available;
+                        $chunkedData[$index]['purchase_lines_id'] = $lotNumbers->first()->purchase_line_id;
+
+                        $productData = array_merge([], $chunkedData[$index]);
+                        foreach ($lotNumbers->skip(1) as $lotNumber) {
+                            $qty = min($lotNumber->qty_available, $remainingQuantity);
+                            $extraData[] = array_merge($productData, [
+                                'transfer_quantity' => $qty,
+                                'purchase_lines_id' => $lotNumber->purchase_line_id,
+                            ]);
+                            $remainingQuantity -= $qty;
+                            if ($remainingQuantity <= 0)
+                                break;
+                        }
+                    }
+                }
 
-        $businessId = request()->session()->get('user.business_id');
-        $transactionDate = $this->productUtil->uf_date(request()->get('transaction_date', null), true);
-        $createdBy = auth()->user()->id;
-        $createdAt = now();
-        $updatedAt = now();
-        $isShippingCalculated = false;
-        $nstShippingId = null;
-
-        foreach(array_chunk(request()->get('manualTpsData', null), 500, true) as $chunkedData){
-            foreach($chunkedData as $index => $data){
-                $chunkedData[$index]['business_id'] = $businessId;
-                $chunkedData[$index]['transaction_date'] = $transactionDate;
-                $chunkedData[$index]['created_by'] = $createdBy;
-                $chunkedData[$index]['created_at'] = $createdAt;
-                $chunkedData[$index]['updated_at'] = $updatedAt;
-                $chunkedData[$index]['is_shipping_calculated'] = $isShippingCalculated;
-                $chunkedData[$index]['nst_shipping_id'] = $nstShippingId;
-                unset($chunkedData[$index]['custom_field']);
-            }
 
-            $nstPrepareStockCreation = NstPrepareStock::insert($chunkedData);
+                $nstPrepareStockCreation = NstPrepareStock::insert(array_merge(array_values($chunkedData), $extraData));
 
-            if($nstPrepareStockCreation){
-                continue;
+                if ($nstPrepareStockCreation) {
+                    continue;
+                }
+                DB::rollBack();
+                return response()->json(['status' => 'error']);
             }
+
+            DB::commit();
+            return response()->json(['status' => 'success']);
+
+        } catch (\Exception $e) {
             DB::rollBack();
-            return response()->json(['status' => 'error']);
+            \Log::error('TPS Prepare failed: ' . $e->getMessage());
+            return response()->json(['status' => 'error', 'msg' => __('messages.something_went_wrong')]);
         }
-
-        DB::commit();
-        return response()->json(['status' => 'success']);
     }
 
-    public function update(Request $request){
-        if(!in_array($request->status, ['prepare', 'receive'])){
+    public function update(Request $request)
+    {
+        if (!auth()->user()->can('tpsTransfer.access_prepare_stock_transfer')) {
+            abort(403, 'Unauthorized action.');
+        }
+
+        if (!in_array($request->status, ['prepare', 'receive'])) {
             return false;
         }
 
         $redirect = "/tps-product/{$request->status}-stock";
 
-        foreach($request->prepareDoneTpsData as $id => $data){
+        foreach ($request->prepareDoneTpsData as $id => $data) {
             unset($data['accepted']);
             $data['status'] = $request->status;
 
@@ -354,14 +400,14 @@ class TpsPrepareStockController extends Controller
             // Check the validity of the quantity from Location X to Location Y
             $checkStatus = $this->transactionService->checkQuantityMismatch($nstPrepareStock, \Arr::get($data, 'transfer_quantity'));
 
-            if(!$checkStatus){
+            if (!$checkStatus) {
                 return response()->json(['status' => 'error', 'msg' => 'check quantity mismatch']);
             }
 
             $nstPrepareStockBefore = $nstPrepareStock->replicate();
             $nstPrepareStock->update($data);
 
-            if(!$nstPrepareStock){
+            if (!$nstPrepareStock) {
                 return response()->json(['status' => 'error']);
             }
             $this->util->activityLog($nstPrepareStock, 'edited', $nstPrepareStockBefore);
@@ -369,20 +415,30 @@ class TpsPrepareStockController extends Controller
         return response()->json(['status' => 'success', 'redirect' => $redirect]);
     }
 
-    public function destroy(Request $request){
+    public function destroy(Request $request)
+    {
+        if (!auth()->user()->can('tpsTransfer.access_prepare_stock_transfer')) {
+            abort(403, 'Unauthorized action.');
+        }
+
         $nstPrepareStock = NstPrepareStock::findOrFail($request->get('id'));
-        if($nstPrepareStock->destroy($request->get('id'))){
+        if ($nstPrepareStock->destroy($request->get('id'))) {
             $this->util->activityLog($nstPrepareStock, 'deleted');
             return response()->json(['status' => 'success']);
         }
         return response()->json(['status' => 'error']);
     }
-    
-    public function destroySelected(Request $request){
+
+    public function destroySelected(Request $request)
+    {
+        if (!auth()->user()->can('tpsTransfer.access_prepare_stock_transfer')) {
+            abort(403, 'Unauthorized action.');
+        }
+
         $nstPrepareStocks = NstPrepareStock::whereIn('id', array_keys($request->prepareDoneTpsData))->get();
 
-        foreach($nstPrepareStocks as $nstPrepareStock){
-            if($nstPrepareStock->delete()){
+        foreach ($nstPrepareStocks as $nstPrepareStock) {
+            if ($nstPrepareStock->delete()) {
                 $this->util->activityLog($nstPrepareStock, 'deleted');
                 continue;
             }
@@ -395,7 +451,7 @@ class TpsPrepareStockController extends Controller
     {
         $tpsData = $request->tpsData;
         $businessId = request()->session()->get('user.business_id');
-        $setting = (NstSetting::where('business_id', $businessId)->first())->value ?? [];
+        $setting = NstSetting::where('business_id', $businessId)->first()?->value ?? [];
 
         $payment_types = $this->productUtil->payment_types(null, true, $businessId);
         $payment_line = ['method' => 'cash', 'amount' => 0, 'note' => '', 'card_transaction_number' => '', 'card_number' => '', 'card_type' => '', 'card_holder_name' => '', 'card_month' => '', 'card_year' => '', 'card_security' => '', 'cheque_number' => '', 'bank_account_number' => '', 'is_return' => 0, 'transaction_no' => ''];
@@ -429,12 +485,22 @@ class TpsPrepareStockController extends Controller
                 'business_locations.id As from_location_id',
                 'business_locations.name As from_location_name',
                 'nst_prepare_products.transfer_quantity',
-                'purchase_lines.purchase_price'
+                'purchase_lines.purchase_price',
+                DB::raw("(
+                    SELECT COALESCE(SUM(npp.transfer_quantity), 0)
+                    FROM nst_prepare_products npp
+                    WHERE npp.product_id = nst_prepare_products.product_id
+                      AND npp.variation_id = nst_prepare_products.variation_id
+                      AND npp.from_location_id = nst_prepare_products.from_location_id
+                      AND npp.status IN ('prepare', 'receive')
+                      AND npp.deleted_at IS NULL
+                      AND npp.id != nst_prepare_products.id
+                ) as committed_qty")
             )
-            ->join('variations','nst_prepare_products.variation_id','=','variations.id')
-            ->join('business_locations','nst_prepare_products.from_location_id','=','business_locations.id')
-            ->leftJoin('purchase_lines','nst_prepare_products.purchase_lines_id','=','purchase_lines.id')
-            ->join('products','nst_prepare_products.product_id','=','products.id');
+            ->join('variations', 'nst_prepare_products.variation_id', '=', 'variations.id')
+            ->join('business_locations', 'nst_prepare_products.from_location_id', '=', 'business_locations.id')
+            ->leftJoin('purchase_lines', 'nst_prepare_products.purchase_lines_id', '=', 'purchase_lines.id')
+            ->join('products', 'nst_prepare_products.product_id', '=', 'products.id');
 
         $shipping_method = $request->shipping_method;
         $shipping_charges = $request->shipping_charges;
@@ -445,27 +511,27 @@ class TpsPrepareStockController extends Controller
             ->where('nst_prepare_products.business_id', $businessId)
             ->sum('nst_prepare_products.transfer_quantity');
 
-        if($totalTransferQuantity == 0){
+        if ($totalTransferQuantity == 0) {
             $isDisabled = 'disabled';
-            $shippingValuePerUnit = 0 ;
-        }elseif($shipping_method == 'equal_ratio'){
+            $shippingValuePerUnit = 0;
+        } elseif ($shipping_method == 'equal_ratio') {
             $isDisabled = 'disabled';
-            $shippingValuePerUnit = $shipping_charges / $totalTransferQuantity ;
-        }elseif($shipping_method == 'as_charge'){
+            $shippingValuePerUnit = $shipping_charges / $totalTransferQuantity;
+        } elseif ($shipping_method == 'as_charge') {
             $isDisabled = 'disabled';
             $shippingValuePerUnit = 0;
-        }elseif($shipping_method == 'different_ratio'){
+        } elseif ($shipping_method == 'different_ratio') {
             $isDisabled = '';
             $shippingValuePerUnit = 0;
         }
 
         return Datatables::of($query)
             ->addColumn('total_price_before_shipping', function ($row) use ($businessId, &$avgPurchasePrice) {
-                if($row->purchase_price){
+                if ($row->purchase_price) {
                     return round($row->purchase_price * $row->transfer_quantity, 2);
                 }
 
-                if(!isset($avgPurchasePrice[$row->id])){
+                if (!isset($avgPurchasePrice[$row->id])) {
                     $avgPurchasePrice[$row->id] = [];
                 }
 
@@ -475,27 +541,27 @@ class TpsPrepareStockController extends Controller
 
                 $lotNumbers = $this->transactionService->getLotDataFromVariation($row->variation_id, $businessId, $row->from_location_id, true);
 
-                if($lotNumbers){
+                if ($lotNumbers) {
                     foreach ($lotNumbers as $lotNumber) {
                         $avgPurchasePrice[$row->id]['total_purchase_price'] += $lotNumber->purchase_price * $lotNumber->qty_available;
                         $count += $lotNumber->qty_available;
                     }
-                    if($count){
+                    if ($count) {
                         $avgPurchasePrice[$row->id]['purchase_price'] = round($avgPurchasePrice[$row->id]['total_purchase_price'] / $count, 2);
                     }
                 }
-                return "<span class='total_price_before_shipping' id='total_price_before_shipping_{$row->id}'>(avg) ".($avgPurchasePrice[$row->id]['purchase_price'] * $row->transfer_quantity)."</span>";
+                return "<span class='total_price_before_shipping' id='total_price_before_shipping_{$row->id}'>(avg) " . ($avgPurchasePrice[$row->id]['purchase_price'] * $row->transfer_quantity) . "</span>";
             })
             ->addColumn('shipping_ratio', function ($row) use ($shippingValuePerUnit, $totalTransferQuantity, $isDisabled, &$avgPurchasePrice) {
-                if($row->purchase_price){
-                    $purchasePrice =  round($row->purchase_price, 2);
-                }else{
+                if ($row->purchase_price) {
+                    $purchasePrice = round($row->purchase_price, 2);
+                } else {
                     $purchasePrice = round($avgPurchasePrice[$row->id]['purchase_price'], 2);
                 }
 
                 $shippingRatio = 0;
-                if($shippingValuePerUnit){
-                    $shippingRatio = ($row->transfer_quantity / $totalTransferQuantity) *  100;
+                if ($shippingValuePerUnit) {
+                    $shippingRatio = ($row->transfer_quantity / $totalTransferQuantity) * 100;
                 }
                 return "<input id='shipping_ratio_{$row->id}' class='form-control shipping-inputs shipping_ratio' data-id='{$row->id}' data-transfer-quantity='{$row->transfer_quantity}' data-purchase-price='{$purchasePrice}' data-total-transfer-quantity='{$totalTransferQuantity}' style='width:100%' type='number' min='0' value='{$shippingRatio}' {$isDisabled}>";
             })
@@ -503,32 +569,32 @@ class TpsPrepareStockController extends Controller
                 return "<span class='shipping-inputs shipping_value_per_unit' id='shipping_value_per_unit_{$row->id}'> $shippingValuePerUnit </span>";
             })
             ->editColumn('purchase_price', function ($row) use (&$avgPurchasePrice) {
-                if($row->purchase_price){
+                if ($row->purchase_price) {
                     return round($row->purchase_price, 2);
                 }
-                return '(avg) '.$avgPurchasePrice[$row->id]['purchase_price'];
+                return '(avg) ' . $avgPurchasePrice[$row->id]['purchase_price'];
             })
-            ->addColumn('unit_price_after_shipping', function ($row) use( &$avgPurchasePrice, $shippingValuePerUnit ) {
-                if($row->purchase_price){
-                    $purchasePrice =  round($row->purchase_price, 2);
-                }else{
+            ->addColumn('unit_price_after_shipping', function ($row) use (&$avgPurchasePrice, $shippingValuePerUnit) {
+                if ($row->purchase_price) {
+                    $purchasePrice = round($row->purchase_price, 2);
+                } else {
                     $purchasePrice = round($avgPurchasePrice[$row->id]['purchase_price'], 2);
                 }
 
                 $unitPriceAfterShipping = round(($purchasePrice) + ($shippingValuePerUnit), 2);
                 $avgPurchasePrice[$row->id]['unit_price_after_shipping'] = $unitPriceAfterShipping;
-                return "<span class='unit_price_after_shipping' id='unit_price_after_shipping_{$row->id}'> ". $unitPriceAfterShipping ." </span>";
+                return "<span class='unit_price_after_shipping' id='unit_price_after_shipping_{$row->id}'> " . $unitPriceAfterShipping . " </span>";
             })
             ->addColumn('total_price_after_shipping', function ($row) use (&$avgPurchasePrice, $shippingValuePerUnit) {
-                if($row->purchase_price){
-                    $purchasePrice =  round($row->purchase_price, 2);
-                }else{
+                if ($row->purchase_price) {
+                    $purchasePrice = round($row->purchase_price, 2);
+                } else {
                     $purchasePrice = round($avgPurchasePrice[$row->id]['purchase_price'], 2);
                 }
 
                 $unitPriceAfterShipping = round(($purchasePrice * intval($row->transfer_quantity)) + ($shippingValuePerUnit * intval($row->transfer_quantity)), 2);
                 $avgPurchasePrice[$row->id]['unit_price_after_shipping'] = $unitPriceAfterShipping;
-                return "<span class='total_price_after_shipping' id='total_price_after_shipping_{$row->id}'> ". $unitPriceAfterShipping ." </span>";
+                return "<span class='total_price_after_shipping' id='total_price_after_shipping_{$row->id}'> " . $unitPriceAfterShipping . " </span>";
             })
             ->editColumn('products_name', function ($row) {
                 $name = $row->products_name;
@@ -540,11 +606,25 @@ class TpsPrepareStockController extends Controller
             ->editColumn('transfer_quantity', function ($row) {
                 return intval($row->transfer_quantity);
             })
-            ->rawColumns(['shipping_ratio', 'shipping_value_per_unit', 'shipping_value_per_unit', 'total_price_before_shipping', 'unit_price_after_shipping', 'total_price_after_shipping'])
+            ->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 '';
+            })
+            ->rawColumns(['shipping_ratio', 'shipping_value_per_unit', 'shipping_value_per_unit', 'total_price_before_shipping', 'unit_price_after_shipping', 'total_price_after_shipping', 'committed_qty_display'])
             ->make(true);
     }
 
-    public function storeShipping(Request $request){
+    public function storeShipping(Request $request)
+    {
+        if (!auth()->user()->can('tpsTransfer.access_prepare_stock_transfer')) {
+            abort(403, 'Unauthorized action.');
+        }
+
         $data = [];
         parse_str($request->input('data'), $data);
         $from_location_id = $request->input('from_location_id');
@@ -554,79 +634,92 @@ class TpsPrepareStockController extends Controller
             return response()->json(['status' => 'error', 'msg' => 'main inputs error or mismatch']);
         }
 
-        DB::beginTransaction();
-        $businessId = request()->session()->get('user.business_id');
-        $createdBy = auth()->user()->id;
-
-        $nstShipping = NstShipping::create([
-            'business_id' => $businessId,
-            'shipping_details' => $data['shipping_details'],
-            'shipping_method' => $data['shipping_method'],
-            'shipping_charges' => $data['shipping_charges'],
-            'additional_expense_key_1' => $data['additional_expense_key_1'],
-            'additional_expense_value_1' => $data['additional_expense_value_1'],
-            'additional_expense_key_2' => $data['additional_expense_key_2'],
-            'additional_expense_value_2' => $data['additional_expense_value_2'],
-            'additional_expense_key_3' => $data['additional_expense_key_3'],
-            'additional_expense_value_3' => $data['additional_expense_value_3'],
-            'additional_expense_key_4' => $data['additional_expense_key_4'],
-            'additional_expense_value_4' => $data['additional_expense_value_4'],
-            'created_by' => $createdBy,
-        ]);
-        $nstShipping->ref_num = 'NST' . \Carbon::now()->year . '-' . $nstShipping->id;
-        $nstShipping->save();
-
-        foreach(array_chunk(request()->get('tpsShippingCalculation', null), 500, true) as $chunkedData){
-            foreach($chunkedData as $index => $data){
-                $unitPriceAfterShipping = 0;
-                $nstStock = NstPrepareStock::findOrFail($index);
-                // Validate the location from and to
-                if($nstStock->from_location_id != $from_location_id || $nstStock->to_location_id != $to_location_id ){
-                    DB::rollBack();
-                    return response()->json(['status' => 'error', 'msg' => 'The location must be same selected locations', ]);
-                }
-                if($nstStock->purchase_lines_id){
-                    $purchaseLine = PurchaseLine::findOrFail($nstStock->purchase_lines_id);
-
-                    if($purchaseLine->quantity < $nstStock->transfer_quantity){
+        try {
+            DB::beginTransaction();
+            $businessId = request()->session()->get('user.business_id');
+            $createdBy = auth()->user()->id;
+
+            $nstShipping = NstShipping::create([
+                'business_id' => $businessId,
+                'shipping_details' => $data['shipping_details'],
+                'shipping_method' => $data['shipping_method'],
+                'shipping_charges' => $data['shipping_charges'],
+                'additional_expense_key_1' => $data['additional_expense_key_1'],
+                'additional_expense_value_1' => $data['additional_expense_value_1'],
+                'additional_expense_key_2' => $data['additional_expense_key_2'],
+                'additional_expense_value_2' => $data['additional_expense_value_2'],
+                'additional_expense_key_3' => $data['additional_expense_key_3'],
+                'additional_expense_value_3' => $data['additional_expense_value_3'],
+                'additional_expense_key_4' => $data['additional_expense_key_4'],
+                'additional_expense_value_4' => $data['additional_expense_value_4'],
+                'created_by' => $createdBy,
+            ]);
+            $nstShipping->ref_num = 'NST' . \Carbon::now()->year . '-' . $nstShipping->id;
+            $nstShipping->save();
+
+            foreach (array_chunk(request()->get('tpsShippingCalculation', null), 500, true) as $chunkedData) {
+                foreach ($chunkedData as $index => $data) {
+                    $unitPriceAfterShipping = 0;
+                    $nstStock = NstPrepareStock::findOrFail($index);
+                    // Validate the location from and to
+                    if ($nstStock->from_location_id != $from_location_id || $nstStock->to_location_id != $to_location_id) {
                         DB::rollBack();
-                        return response()->json(['status' => 'error', 'msg' => 'Transfer quantity must be less than lot quantity ', ]);
+                        return response()->json(['status' => 'error', 'msg' => 'The location must be same selected locations',]);
                     }
+                    if ($nstStock->purchase_lines_id) {
+                        $purchaseLine = PurchaseLine::findOrFail($nstStock->purchase_lines_id);
 
-                    $unitPriceAfterShipping = floatval($purchaseLine->purchase_price) + floatval($data['value_per_unit']);
-                }else{
-                    $unitPrices = $this->transactionService->getPurchasePriceFromVariationByQuantity($nstStock->transfer_quantity, $nstStock->variation_id, $businessId, $nstStock->from_location_id);
-                    
-                    foreach ($unitPrices as $unitPrice){
-                        $unitPriceAfterShipping += floatval($unitPrice['purchase_price']) * intval($unitPrice['quantity']);
-                    }
+                        $available_qty = $purchaseLine->quantity
+                            - ($purchaseLine->quantity_sold ?? 0)
+                            - ($purchaseLine->quantity_adjusted ?? 0)
+                            - ($purchaseLine->quantity_returned ?? 0);
 
-                    $unitPriceAfterShipping = ($unitPriceAfterShipping / intval($nstStock->transfer_quantity)) + floatval($data['value_per_unit']);
-                }
+                        if ($available_qty < $nstStock->transfer_quantity) {
+                            DB::rollBack();
+                            return response()->json(['status' => 'error', 'msg' => 'Transfer quantity must be less than available lot quantity ',]);
+                        }
 
-                $nstStock->shipping_ratio = $data['shipping_ratio'];
-                $nstStock->value_per_unit = $data['value_per_unit'];
-                $nstStock->unit_price_after_shipping = $unitPriceAfterShipping;
-                $nstStock->nst_shipping_id = $nstShipping->id;
-                //Check if this NST row is already have shipping calculation
-                if($nstStock->is_shipping_calculated == 1){
-                    DB::rollBack();
-                    return response()->json(['status' => 'error', 'msg' => 'product already have shipping calculation.']);
-                }
-                $nstStock->is_shipping_calculated = true;
-                $nstStock->status = 'receive';
-                $nstStock->save();
-                if(!$nstStock){
-                    DB::rollBack();
-                    return response()->json(['status' => 'error', 'msg' => 'unexpected error.']);
+                        $unitPriceAfterShipping = floatval($purchaseLine->purchase_price) + floatval($data['value_per_unit']);
+                    } else {
+                        $unitPrices = $this->transactionService->getPurchasePriceFromVariationByQuantity($nstStock->transfer_quantity, $nstStock->variation_id, $businessId, $nstStock->from_location_id);
+
+                        foreach ($unitPrices as $unitPrice) {
+                            $unitPriceAfterShipping += floatval($unitPrice['purchase_price']) * intval($unitPrice['quantity']);
+                        }
+
+                        $unitPriceAfterShipping = ($unitPriceAfterShipping / intval($nstStock->transfer_quantity)) + floatval($data['value_per_unit']);
+                    }
+
+                    $nstStock->shipping_ratio = $data['shipping_ratio'];
+                    $nstStock->value_per_unit = $data['value_per_unit'];
+                    $nstStock->unit_price_after_shipping = $unitPriceAfterShipping;
+                    $nstStock->nst_shipping_id = $nstShipping->id;
+                    //Check if this NST row is already have shipping calculation
+                    if ($nstStock->is_shipping_calculated == 1) {
+                        DB::rollBack();
+                        return response()->json(['status' => 'error', 'msg' => 'product already have shipping calculation.']);
+                    }
+                    $nstStock->is_shipping_calculated = true;
+                    $nstStock->status = 'receive';
+                    $nstStock->save();
+                    if (!$nstStock) {
+                        DB::rollBack();
+                        return response()->json(['status' => 'error', 'msg' => 'unexpected error.']);
+                    }
                 }
             }
+            DB::commit();
+            return response()->json(['status' => 'success', 'redirect' => "/tps-product/receive-stock"]);
+
+        } catch (\Exception $e) {
+            DB::rollBack();
+            \Log::error('TPS Prepare Shipping failed: ' . $e->getMessage());
+            return response()->json(['status' => 'error', 'msg' => __('messages.something_went_wrong')]);
         }
-        DB::commit();
-        return response()->json(['status' => 'success', 'redirect' => "/tps-product/receive-stock"]);
     }
 
-    public function getSqlWithBindings($query){
+    public function getSqlWithBindings($query)
+    {
         return vsprintf(str_replace('?', '%s', $query->toSql()), collect($query->getBindings())->map(function ($binding) {
             return is_numeric($binding) ? $binding : "'{$binding}'";
         })->toArray());
