000

<?php

namespace App\Http\Controllers\Admin;

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

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

$selectedSiteId = $request->get('site_id', $defaultSiteId);

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

$blocksQuery = DB::table('inter_culture');
        if ($role == 1 && $request->filled('site_id')) {
            $blocksQuery->where('site_id', $selectedSiteId);
        } else {
            $blocksQuery->where('site_id', $defaultSiteId);
        }

$blocks = $blocksQuery->select('block_name')->distinct()->pluck('block_name');
        $block = $request->get('block_name');

$machines = DB::table('master_machine')->pluck('machine_name', 'id');
        $tractors = DB::table('master_tractors')->pluck('tractor_name', 'id');

$query = DB::table('inter_culture')
            ->leftJoin('users', 'inter_culture.user_id', '=', 'users.id')
            ->select('inter_culture.*', 'users.name as user_name');

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

if ($block) {
            $query->where('inter_culture.block_name', $block);
        }
        if ($request->filled('session')) {
            $session = $request->session;
            switch ($session) {
                case 'kharif':
                    $query->whereMonth('inter_culture.date', '>=', 6)
                          ->whereMonth('inter_culture.date', '<=', 10);
                    break;
                case 'rabi':
                    $query->where(function($q) {
                        $q->whereMonth('inter_culture.date', '>=', 11)
                          ->orWhereMonth('inter_culture.date', '<=', 3);
                    });
                    break;
                case 'zaid':
                    $query->whereMonth('inter_culture.date', '>=', 4)
                          ->whereMonth('inter_culture.date', '<=', 6);
                    break;
            }
        }

$query = $query->orderBy('inter_culture.id', 'DESC')->paginate(10)->appends($request->all());

return view('inter_culture.index', compact(
            'query',
            'blocks',
            'block',
            'machines',
            'tractors',
            'sites',
            'selectedSiteId',
            'role'
        ));
    }

public function search(Request $request)
    {
        $user = Auth::user();
        $siteId = $user->site_name;
        $role = $user->role;

$selectedSiteId = $request->get('site_id', $siteId);
        $block = $request->get('block_name');
        $keyword = $request->get('keyword');

$machines = DB::table('master_machine')->pluck('machine_name', 'id');
        $tractors = DB::table('master_tractors')->pluck('tractor_name', 'id');

$query = DB::table('inter_culture')
            ->leftJoin('users', 'inter_culture.user_id', '=', 'users.id')
            ->select('inter_culture.*', 'users.name as user_name');

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

if ($block) {
            $query->where('inter_culture.block_name', $block);
        }

if ($keyword) {
            $query->where(function ($q) use ($keyword) {
                $q->where('inter_culture.plot_name', 'like', "%$keyword%")
                    ->orWhere('inter_culture.activity_type', 'like', "%$keyword%")
                    ->orWhere('inter_culture.date_activity', 'like', "%$keyword%");
            });
        }

$query = $query->orderBy('inter_culture.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;
            }
        }

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

//=================hARVEST DATA SHOW IN TABLES

// public function harvestList(Request $request)
// {
//     $user = Auth::user();
//     $role = $user->role;
//     $defaultSiteId = $user->site_name;

//     // Selected site from request or user's site
//     $selectedSiteId = $request->get('site_id', $defaultSiteId);

//     // Get sites for dropdown (only admin)
//     $sites = ($role == 1)
//         ? DB::table('master_sites')->pluck('site_name', 'id')
//         : DB::table('master_sites')->where('id', $defaultSiteId)->pluck('site_name', 'id');

//     // Base query with user join
//     $query = DB::table('harvesting_update')
//         ->select('harvesting_update.*', 'users.name as user_name')
//         ->leftJoin('users', 'harvesting_update.user_id', '=', 'users.id');

//     // Site filter
//     if ($role == 1 && $request->filled('site_id')) {
//         $query->where('harvesting_update.site_id', $selectedSiteId);
//     } else {
//         $query->where('harvesting_update.site_id', $defaultSiteId);
//     }

//     // Block filter
//     if ($request->block_name) {
//         $query->where('harvesting_update.block_name', $request->block_name);
//     }

//     // Search filter
//     if ($request->search) {
//         $query->where(function ($q) use ($request) {
//             $q->where('harvesting_update.plot_name', 'like', '%' . $request->search . '%')
//               ->orWhere('harvesting_update.harvest_method', 'like', '%' . $request->search . '%')
//               ->orWhere('harvesting_update.harvest_purpose', 'like', '%' . $request->search . '%');
//         });
//     }

//     // Paginate results
//     $harvests = $query->orderBy('harvesting_update.id', 'desc')
//                       ->paginate(10)
//                       ->appends($request->all());

//     // Transform results
//     $harvests->getCollection()->transform(function ($item) {
//         $machineIds = explode(',', $item->machine_id);
//         $machines = DB::table('master_machine')
//             ->whereIn('id', $machineIds)
//             ->pluck('machine_name')
//             ->toArray();
//         $item->machine_names = implode(', ', $machines);

//         $tractorIds = explode(',', $item->tractor_id);
//         $tractors = DB::table('master_tractors')
//             ->whereIn('id', $tractorIds)
//             ->pluck('tractor_name')
//             ->toArray();
//         $item->tractor_names = implode(', ', $tractors);

//         $maintenanceData = json_decode($item->major_maintenance, true);
//         $item->major_maintenance_text = is_array($maintenanceData)
//             ? implode(', ', array_column($maintenanceData, 'spare_part'))
//             : '-';

//         return $item;
//     });

//     // Get blocks for dropdown
//     $blockQuery = DB::table('harvesting_update')->select('block_name')->distinct();
//     if ($role != 1) {
//         $blockQuery->where('site_id', $defaultSiteId);
//     } elseif ($request->filled('site_id')) {
//         $blockQuery->where('site_id', $selectedSiteId);
//     }
//     $blocks = $blockQuery->get();

//     return view('harvest.index', compact(
//         'harvests',
//         'blocks',
//         'sites',
//         'selectedSiteId',
//         'role'
//     ));
// }

public function harvestList(Request $request)
{
    $user = Auth::user();
    $role = $user->role;
    $defaultSiteId = $user->site_name;

// Selected site from request or user's site
    $selectedSiteId = $request->get('site_id', $defaultSiteId);

// Get sites for dropdown (only admin)
    $sites = ($role == 1)
        ? DB::table('master_sites')->pluck('site_name', 'id')
        : DB::table('master_sites')->where('id', $defaultSiteId)->pluck('site_name', 'id');

// Base query with user join
    $query = DB::table('harvesting_update')
        ->select('harvesting_update.*', 'users.name as user_name')
        ->leftJoin('users', 'harvesting_update.user_id', '=', 'users.id');

// Apply filters
    if ($role == 1 && $request->filled('site_id')) {
        $query->where('harvesting_update.site_id', $selectedSiteId);
    } else {
        $query->where('harvesting_update.site_id', $defaultSiteId);
    }

if ($request->block_name) {
        $query->where('harvesting_update.block_name', $request->block_name);
    }

if ($request->search) {
        $query->where(function ($q) use ($request) {
            $q->where('harvesting_update.plot_name', 'like', '%' . $request->search . '%')
              ->orWhere('harvesting_update.harvest_method', 'like', '%' . $request->search . '%')
              ->orWhere('harvesting_update.harvest_purpose', 'like', '%' . $request->search . '%');
        });
    }

// Clone query to calculate total yield before pagination
    $totalYieldAll = $query->clone()->sum('yield_mt');

// Paginate the results
    $harvests = $query->orderBy('harvesting_update.id', 'desc')
                      ->paginate(10)
                      ->appends($request->all());

// Transform results
    $harvests->getCollection()->transform(function ($item) {
        $machineIds = explode(',', $item->machine_id);
        $machines = DB::table('master_machine')
            ->whereIn('id', $machineIds)
            ->pluck('machine_name')
            ->toArray();
        $item->machine_names = implode(', ', $machines);

$tractorIds = explode(',', $item->tractor_id);
        $tractors = DB::table('master_tractors')
            ->whereIn('id', $tractorIds)
            ->pluck('tractor_name')
            ->toArray();
        $item->tractor_names = implode(', ', $tractors);

$maintenanceData = json_decode($item->major_maintenance, true);
        $item->major_maintenance_text = is_array($maintenanceData)
            ? implode(', ', array_column($maintenanceData, 'spare_part'))
            : '-';

return $item;
    });

// Block dropdown
    $blockQuery = DB::table('harvesting_update')->select('block_name')->distinct();
    if ($role != 1) {
        $blockQuery->where('site_id', $defaultSiteId);
    } elseif ($request->filled('site_id')) {
        $blockQuery->where('site_id', $selectedSiteId);
    }
    $blocks = $blockQuery->get();

return view('harvest.index', compact(
        'harvests',
        'blocks',
        'sites',
        'selectedSiteId',
        'role',
        'totalYieldAll'
    ));
}

}