000

<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use DB;
use Illuminate\Support\Facades\Auth;

class FertilizerRecordController extends Controller
{
   
     public function index(Request $request)
    {
        $user = Auth::user();
        $siteId = $user->site_name;
        $role = $user->role;

$selectedSiteId = ($role == 1) ? $request->get('site_id', '') : $siteId;

$blocksQuery = DB::table('fertilizer_soil_record');
        if ($selectedSiteId) {
            $blocksQuery->where('site_id', $selectedSiteId);
        }
        $blocks = $blocksQuery->select('block_name')->distinct()->orderBy('block_name')->get();
        $selectedBlock = $request->input('block_name', null);

if (!$selectedBlock && $blocks->isNotEmpty()) {
            $selectedBlock = $blocks->first()->block_name;
        }

$search = $request->input('search', '');

$query = DB::table('fertilizer_soil_record as f')
            // REMOVE THIS LINE: ->leftJoin('master_manpower as mp', 'f.category_id', '=', 'mp.id')
            ->leftJoin('users as u', 'f.user_id', '=', 'u.id')
            // Change 'mp.category as manpower_type' to 'f.manpower_type'
            ->select('f.*', 'f.manpower_type', 'u.name as user_name');

if ($role == 1 && $request->filled('site_id')) {
            $query->where('f.site_id', $selectedSiteId);
        } else {
            $query->where('f.site_id', $siteId);
        }

if ($selectedBlock) {
            $query->where('f.block_name', $selectedBlock);
        }

if ($search) {
            $query->where(function ($q) use ($search) {
                $q->where('f.plot_name', 'like', "%$search%")
                    ->orWhere('u.name', 'like', "%$search%");
            });
        }

$records = $query->orderBy('f.id', 'DESC')
            ->paginate(10)
            ->appends($request->all());
            
            // Session filter
    if ($request->filled('session')) {
        $session = $request->session;
        switch ($session) {
            case 'kharif':
                $query->whereMonth('pre.date', '>=', 6)
                      ->whereMonth('pre.date', '<=', 10);
                break;
            case 'rabi':
                $query->where(function($q) {
                    $q->whereMonth('pre.date', '>=', 11)
                      ->orWhereMonth('pre.date', '<=', 3);
                });
                break;
            case 'zaid':
                $query->whereMonth('pre.date', '>=', 4)
                      ->whereMonth('pre.date', '<=', 6);
                break;
        }
    }

$allMachineIds = collect();
        $allTractorIds = collect();
        $allFertilizerIds = collect();

foreach ($records as $record) {
            if ($record->machine_id) {
                $allMachineIds = $allMachineIds->merge(explode(',', $record->machine_id));
            }
            
            if ($record->tractor_id) {
                $allTractorIds = $allTractorIds->merge(explode(',', $record->tractor_id));
            }

$fertilizerArray = json_decode($record->fertilizer_id, true);
            if (is_array($fertilizerArray)) {
                foreach ($fertilizerArray as $item) {
                    if (isset($item['id'])) {
                        $allFertilizerIds->push($item['id']);
                    }
                }
            } elseif ($record->fertilizer_id) {
                $allFertilizerIds->push($record->fertilizer_id);
            }
        }

$machines = DB::table('master_machine')
            ->whereIn('id', $allMachineIds->unique()->filter())
            ->pluck('machine_name', 'id')
            ->toArray();

$tractors = DB::table('master_tractors')
            ->whereIn('id', $allTractorIds->unique()->filter())
            ->pluck('tractor_name', 'id')
            ->toArray();

$fertilizersMaster = DB::table('master_fertilizer')
            ->whereIn('id', $allFertilizerIds->unique()->filter())
            ->pluck('fertilizer_name', 'id')
            ->toArray();

foreach ($records as $record) {
            $record->formatted_fertilizers = [];
            $fertilizerArray = json_decode($record->fertilizer_id, true);
            if (is_array($fertilizerArray)) {
                foreach ($fertilizerArray as $item) {
                    if (isset($item['id']) && isset($fertilizersMaster[$item['id']])) {
                        $record->formatted_fertilizers[] = [
                            'name' => $fertilizersMaster[$item['id']],
                            'quantity' => $item['quantity'] ?? 'N/A',
                            'uom' => $item['uom'] ?? 'kg',
                        ];
                    }
                }
            } elseif ($record->fertilizer_id && isset($fertilizersMaster[$record->fertilizer_id])) {
                $record->formatted_fertilizers[] = [
                    'name' => $fertilizersMaster[$record->fertilizer_id],
                    'quantity' => $record->fertilizer_quantity ?? 'N/A',
                    'uom' => $record->uom ?? 'kg',
                ];
            }

$record->formatted_machines = [];
            if ($record->machine_id) {
                $machineIds = explode(',', $record->machine_id);
                foreach ($machineIds as $machId) {
                    $machId = trim($machId);
                    if (isset($machines[$machId])) {
                        $record->formatted_machines[] = $machines[$machId];
                    }
                }
            }

$record->formatted_tractors = [];
            if ($record->tractor_id) {
                $tractorIds = explode(',', $record->tractor_id);
                foreach ($tractorIds as $trId) {
                    $trId = trim($trId);
                    if (isset($tractors[$trId])) {
                        $record->formatted_tractors[] = $tractors[$trId];
                    }
                }
            }
            
            if ($record->major_maintenance) {
                try {
                    $maintenanceData = json_decode($record->major_maintenance, true);
                    if (is_array($maintenanceData)) {
                        $record->major_maintenance_parsed = $maintenanceData;
                    } else {
                        $record->major_maintenance_parsed = [
                            ['spare_part' => 'Maintenance', 'value' => $maintenanceData]
                        ];
                    }
                } catch (\Exception $e) {
                    $record->major_maintenance_parsed = [];
                }
            } else {
                $record->major_maintenance_parsed = [];
            }

if (!isset($record->activity_type)) {
                $record->activity_type = 'N/A';
            }
        }

$sites = ($role == 1)
            ? DB::table('master_sites')->pluck('site_name', 'id')->toArray()
            : DB::table('master_sites')->where('id', $siteId)->pluck('site_name', 'id')->toArray();

return view('fertilizer_soil_record.index', compact(
            'blocks',
            'selectedBlock',
            'search',
            'records',
            'sites',
            'selectedSiteId',
            'role'
        ));
    }
}