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 ActivityMonitoringController extends Controller
{
    public function index(Request $request)
    {
        $user = Auth::user();
        $role = $user->role;
        $userSiteId = $user->site_name;

// Get distinct blocks for dropdown
        $blocksQuery = DB::table('activity_monitoring');
        if ($role != 1) {
            $blocksQuery->where('site_id', $userSiteId);
        }
        $blocks = $blocksQuery->select('block_name')->distinct()->get();

// Get site list from master_sites
        $sites = DB::table('master_sites')->pluck('site_name', 'id');

// Start main query
        $query = DB::table('activity_monitoring');

// Site filtering
        $selectedSiteId = null;
        if ($role != 1) {
            $selectedSiteId = $userSiteId;
            $query->where('site_id', $userSiteId);
        } elseif ($request->filled('site_id')) {
            $selectedSiteId = $request->site_id;
            $query->where('site_id', $selectedSiteId);
        }

// Block filtering
        if ($request->filled('block_name')) {
            $query->where('block_name', $request->block_name);
        }

// Search filtering
        if ($request->filled('search')) {
            $search = $request->search;
            $query->where(function ($q) use ($search) {
                $q->where('plot_name', 'like', "%$search%")
                  ->orWhere('activity_stage', 'like', "%$search%")
                  ->orWhere('leaf_condition', 'like', "%$search%");
            });
        }

// Get paginated result
        $activities = $query->orderBy('id', 'desc')->paginate(10)->withQueryString();
        
        // 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;
    }
}

// Send to view
        return view('activity_monitoring.index', compact(
            'blocks',
            'sites',
            'activities',
            'selectedSiteId',
            'role',
            'userSiteId'
        ));
    }
    
    public function indexhh(Request $request)
{
    $user = Auth::user();
    $role = $user->role;
    $userSiteId = $user->site_name;

// Get distinct blocks for dropdown
    $blocksQuery = DB::table('harvest_store_manage');
    if ($role != 1) {
        $blocksQuery->where('site_id', $userSiteId);
    }
    $blocks = $blocksQuery->select('block_name')->distinct()->get();

// Get site list from master_sites
    $sites = DB::table('master_sites')->pluck('site_name', 'id');

// Main Query for harvest data with sales integration
    $query = DB::table('harvest_store_manage')
        ->leftJoin('harvest_sale_records', 'harvest_store_manage.id', '=', 'harvest_sale_records.harvest_store_id')
        ->select(
            'harvest_store_manage.id',
            'harvest_store_manage.product_id',
            'harvest_store_manage.site_id',
            'harvest_store_manage.block_name',
            'harvest_store_manage.plot_name',
            'harvest_store_manage.seed_name',
            'harvest_store_manage.seed_id',
            'harvest_store_manage.date',
            'harvest_store_manage.yield_mt',
            'harvest_store_manage.total_mt',
            'harvest_store_manage.mt_price',
            'harvest_store_manage.sale_price',
            'harvest_store_manage.quantity',
            'harvest_store_manage.created_at',
            DB::raw('COALESCE(SUM(harvest_sale_records.sold_mt), 0) as sold_mt'),
            DB::raw('COALESCE(SUM(harvest_sale_records.total_price), 0) as total_sale')
        )
        ->groupBy(
            'harvest_store_manage.id',
            'harvest_store_manage.product_id',
            'harvest_store_manage.site_id',
            'harvest_store_manage.block_name',
            'harvest_store_manage.plot_name',
            'harvest_store_manage.seed_name',
            'harvest_store_manage.seed_id',
            'harvest_store_manage.date',
            'harvest_store_manage.yield_mt',
            'harvest_store_manage.total_mt',
            'harvest_store_manage.mt_price',
            'harvest_store_manage.sale_price',
            'harvest_store_manage.quantity',
            'harvest_store_manage.created_at'
        );

// Site filtering
    $selectedSiteId = null;
    if ($role != 1) {
        $selectedSiteId = $userSiteId;
        $query->where('harvest_store_manage.site_id', $userSiteId);
    } elseif ($request->filled('site_id')) {
        $selectedSiteId = $request->site_id;
        $query->where('harvest_store_manage.site_id', $selectedSiteId);
    }

// Block filtering
    if ($request->filled('block_name')) {
        $query->where('harvest_store_manage.block_name', $request->block_name);
    }

// Search filtering (on plot_name and seed_name)
    if ($request->filled('search')) {
        $search = $request->search;
        $query->where(function ($q) use ($search) {
            $q->where('harvest_store_manage.plot_name', 'like', "%$search%")
              ->orWhere('harvest_store_manage.seed_name', 'like', "%$search%");
        });
    }

// Filter by record_type based on product_id
    if ($request->filled('record_type')) {
        $recordTypeFilter = $request->record_type;
        $query->where(function ($q) use ($recordTypeFilter) {
            if ($recordTypeFilter == 'Harvest') {
                $q->where('harvest_store_manage.product_id', 1);
            } elseif ($recordTypeFilter == 'Hay') {
                $q->where('harvest_store_manage.product_id', 2);
            } elseif ($recordTypeFilter == 'Silage') {
                $q->where('harvest_store_manage.product_id', 3);
            }
        });
    }

$harvests = $query->orderBy('harvest_store_manage.id', 'asc')
                      ->paginate(10)
                      ->through(function ($item) {
                          if ($item->product_id == 1) {
                              $item->record_type = 'Harvest';
                          } elseif ($item->product_id == 2) {
                              $item->record_type = 'Hay';
                          } elseif ($item->product_id == 3) {
                              $item->record_type = 'Silage';
                          } else {
                              $item->record_type = 'Unknown';
                          }
                          return $item;
                      })
                      ->withQueryString();

// Fetch all summaries for the main page display using product_id
    $silageSummary = $this->calculateProductionSummary(3, $selectedSiteId);
    $haySummary = $this->calculateProductionSummary(2, $selectedSiteId);
    $harvestSummary = $this->calculateProductionSummary(1, $selectedSiteId);
    
            // 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;
    }
}

return view('harvest_store_manage.index', compact(
        'blocks',
        'sites',
        'harvests',
        'selectedSiteId',
        'role',
        'userSiteId',
        'silageSummary',
        'haySummary',
        'harvestSummary'
    ));
}

private function calculateProductionSummary($productId, $selectedSiteId = null)
{
    // First, get the basic harvest data without JOIN to avoid duplication
    $baseQuery = DB::table('harvest_store_manage')
        ->where('harvest_store_manage.product_id', $productId);

if ($selectedSiteId) {
        $baseQuery->where('harvest_store_manage.site_id', $selectedSiteId);
    }

// Get yield summary by seed_name (without sales data to avoid duplication)
    $yieldSummary = $baseQuery->select(
        'harvest_store_manage.seed_name',
        DB::raw('SUM(harvest_store_manage.yield_mt) as total_yield_mt'),
        DB::raw('AVG(harvest_store_manage.mt_price) as avg_price')
    )->groupBy('harvest_store_manage.seed_name')
     ->get();

// Get sales data separately to avoid yield duplication
    $salesQuery = DB::table('harvest_store_manage')
        ->leftJoin('harvest_sale_records', 'harvest_store_manage.id', '=', 'harvest_sale_records.harvest_store_id')
        ->where('harvest_store_manage.product_id', $productId);

if ($selectedSiteId) {
        $salesQuery->where('harvest_store_manage.site_id', $selectedSiteId);
    }

$salesSummary = $salesQuery->select(
        'harvest_store_manage.seed_name',
        DB::raw('SUM(COALESCE(harvest_sale_records.sold_mt, 0)) as total_sold_mt'),
        DB::raw('SUM(COALESCE(harvest_sale_records.total_price, 0)) as total_sale_for_seed'),
        DB::raw('AVG(COALESCE(harvest_sale_records.sale_price_per_mt, harvest_store_manage.mt_price)) as avg_sale_price')
    )->groupBy('harvest_store_manage.seed_name')
     ->get()
     ->keyBy('seed_name');

// Combine yield and sales data
    $summary = $yieldSummary->map(function ($item) use ($salesSummary) {
        $salesData = $salesSummary->get($item->seed_name);
        
        $item->total_sold_mt = $salesData ? $salesData->total_sold_mt : 0;
        $item->remaining_mt = $item->total_yield_mt - $item->total_sold_mt;
        $item->total_sale_for_seed = $salesData ? $salesData->total_sale_for_seed : 0;
        $item->avg_sale_price = $salesData ? $salesData->avg_sale_price : $item->avg_price;
        
        return $item;
    });

// Calculate grand totals from the summary data to ensure consistency
    $grandTotalYield = $summary->sum('total_yield_mt');
    $grandTotalSold = $summary->sum('total_sold_mt');
    $grandTotalRemaining = $summary->sum('remaining_mt');
    $grandTotalSale = $summary->sum('total_sale_for_seed');

return [
        'summary' => $summary,
        'grand_total_yield_mt' => $grandTotalYield,
        'grand_total_sold_mt' => $grandTotalSold,
        'grand_total_remaining_mt' => $grandTotalRemaining,
        'grand_total_sale' => $grandTotalSale
    ];
}
///Production Summaries End
    public function updateSalePrice(Request $request, $id)
    {
        $request->validate([
            'sale_price' => 'required|numeric|min:0',
        ]);

DB::table('harvest_store_manage')->where('id', $id)->update([
            'sale_price' => $request->sale_price,
            'updated_at' => now(),
        ]);

return redirect()->back()->with('success', 'Sale Price updated successfully! 🎉');
    }
}