@include('new_layouts.header') @php $profile = \App\Models\Utility::get_file('uploads/avatar/'); @endphp

{{ __('Tender RFQ Dashboard') }}

{{-- Add Tender --}} Back

{{--
@forelse ($tenders as $tender) @php $mainTenderIds = $tender['main_tender_ids']; $subTenderIds = $tender['sub_division_ids']; $categories = DB::table('trade_category') ->whereIn('id', $mainTenderIds) ->get(); $divisions = DB::table('tender_divisions') ->whereIn('trade_id', $mainTenderIds) ->whereIn('id', $subTenderIds) ->get(); // Get RFQ counts $rfqCounts = DB::table('tender_subcon_allocated AS t') ->select([ DB::raw('TRIM(BOTH \'"\' FROM j.trade_id) as main_trade_id'), DB::raw('COUNT(DISTINCT t.subcon_id) as number_of_subcontractors') ]) ->where('tender_id', $tender['id']) ->where('t.subcon_id', '!=', 0) ->crossJoin(DB::raw('JSON_TABLE( CONCAT(\'[\', REPLACE(SUBSTRING(main_trade, 2, LENGTH(main_trade) - 2), \',\', \',\'), \']\'), \'$[*]\' COLUMNS (trade_id VARCHAR(10) PATH \'$\') ) as j')) ->groupBy('j.trade_id') ->orderBy('j.trade_id') ->get() ->keyBy('main_trade_id'); // Get received quotation counts $receivedCounts = DB::table('tender_subcon_allocated AS t') ->select([ DB::raw('TRIM(BOTH \'"\' FROM j.trade_id) as main_trade_id'), DB::raw('COUNT(DISTINCT CASE WHEN t.subcon_file_id IS NOT NULL THEN t.subcon_id END) as received_count') ]) ->where('tender_id', $tender['id']) ->where('t.subcon_id', '!=', 0) ->crossJoin(DB::raw('JSON_TABLE( CONCAT(\'[\', REPLACE(SUBSTRING(main_trade, 2, LENGTH(main_trade) - 2), \',\', \',\'), \']\'), \'$[*]\' COLUMNS (trade_id VARCHAR(10) PATH \'$\') ) as j')) ->groupBy('j.trade_id') ->orderBy('j.trade_id') ->get() ->keyBy('main_trade_id'); // Calculate project totals $project_total_rfq = $rfqCounts->sum('number_of_subcontractors'); $project_total_received = $receivedCounts->sum('received_count'); $project_total_pending = $project_total_rfq - $project_total_received; @endphp @if ($categories->count() > 0 || $divisions->count() > 0) @foreach ($categories as $cat) @php $count_total = isset($rfqCounts[$cat->id]) ? $rfqCounts[$cat->id]->number_of_subcontractors : 0; $count_total_received = isset($receivedCounts[$cat->id]) ? $receivedCounts[$cat->id]->received_count : 0; $count_pending = $count_total - $count_total_received; @endphp @endforeach @endif @empty @endforelse
Project Name Cut-off Date Subcontractor RFQ Trade RFQ Issued Quotation Received Quotation Pending
{{ $tender['project_name'] }} {{ Utility::site_date_format($tender['dead_line'], \Auth::user()->id) }} @foreach ($tender['sub_con_details'] as $sub_con) {{ DB::table('users')->where('id', $sub_con)->value('name') }} @endforeach
All Trade RFQ Total @if ($project_total_rfq > 0) {{ $project_total_rfq }} @else - @endif @if ($project_total_received > 0) {{ $project_total_received }} @else - @endif @if ($project_total_pending > 0) {{ $project_total_pending }} @else - @endif
{{ $cat->trade_name }} @if ($count_total > 0) {{ $count_total }} @else - @endif @if ($count_total_received > 0) {{ $count_total_received }} @else - @endif @if ($count_pending > 0) {{ $count_pending }} @else - @endif
No data available
--}}
@forelse ($tenders as $tender) @php $mainTenderIds = $tender['main_tender_ids']; $subTenderIds = $tender['sub_division_ids']; $categories = DB::table('trade_category') ->whereIn('id', $mainTenderIds) ->get(); $divisions = DB::table('tender_divisions') ->whereIn('trade_id', $mainTenderIds) ->whereIn('id', $subTenderIds) ->get(); $subdivision = DB::table('tender_category_save') ->whereIn('tra_id', $mainTenderIds) ->whereIn('subdiv_id', $subTenderIds) ->get(); // Get RFQ counts grouped by main_trade, sub_division, and cat_id $rfqCountsDetailed = DB::table( 'tender_subcon_allocated AS t', ) ->select([ 't.main_trade', 't.sub_division', 't.cat_id', DB::raw( 'COUNT(DISTINCT t.subcon_id) as number_of_subcontractors', ), ]) ->where('tender_id', $tender['id']) ->where('t.subcon_id', '!=', 0) ->groupBy( 't.main_trade', 't.sub_division', 't.cat_id', ) ->get(); // Get received quotation counts $receivedCountsDetailed = DB::table( 'tender_subcon_allocated AS t', ) ->select([ 't.main_trade', 't.sub_division', 't.cat_id', DB::raw('COUNT(DISTINCT CASE WHEN (t.method_type = 1 AND t.subcon_file_id IS NOT NULL) OR (t.method_type = 0 AND t.subcon_file_id IS NOT NULL) THEN t.subcon_id END) as received_count'), ]) ->where('tender_id', $tender['id']) ->where('t.subcon_id', '!=', 0) ->groupBy( 't.main_trade', 't.sub_division', 't.cat_id', ) ->get(); // Create lookup arrays for faster access $rfqLookup = []; $receivedLookup = []; foreach ($rfqCountsDetailed as $rfq) { $key = $rfq->main_trade . '|' . $rfq->sub_division . '|' . $rfq->cat_id; $rfqLookup[$key] = $rfq->number_of_subcontractors; } foreach ($receivedCountsDetailed as $received) { $key = $received->main_trade . '|' . $received->sub_division . '|' . $received->cat_id; $receivedLookup[$key] = $received->received_count; } // Calculate project totals $project_total_rfq = 0; $project_total_received = 0; foreach ($subdivision as $cat) { $possibleKeys = [ '[' . $cat->tra_id . ']|[' . $cat->subdiv_id . ']|' . $cat->id, $cat->tra_id . '|' . $cat->subdiv_id . '|' . $cat->id, ]; foreach ($possibleKeys as $key) { if (isset($rfqLookup[$key])) { $project_total_rfq += $rfqLookup[$key]; $project_total_received += $receivedLookup[$key] ?? 0; break; } } } $project_total_pending = $project_total_rfq - $project_total_received; // Calculate total rows needed for rowspan $totalRows = $subdivision->count() + 1; // +1 for the summary row @endphp @if ($categories->count() > 0 || $divisions->count() > 0 || $subdivision->count() > 0) {{-- First row: Project info + Summary totals --}} {{-- Individual Subdivision Rows --}} @foreach ($subdivision as $cat) @php // Initialize counts $count_total = 0; $count_total_received = 0; // Create possible lookup keys based on the data structure $possibleKeys = [ '[' . $cat->tra_id . ']|[' . $cat->subdiv_id . ']|' . $cat->id, '[' . $cat->tra_id . ']|[' . $cat->subdiv_id . ']|' . $cat->tra_id, '[' . $cat->tra_id . ']|[' . $cat->subdiv_id . ']|' . $cat->subdiv_id, $cat->tra_id . '|' . $cat->subdiv_id . '|' . $cat->id, $cat->tra_id . '|' . $cat->subdiv_id . '|' . $cat->tra_id, $cat->tra_id . '|' . $cat->subdiv_id . '|' . $cat->subdiv_id, ]; // Try to find matching counts using the lookup keys foreach ($possibleKeys as $key) { if (isset($rfqLookup[$key])) { $count_total = $rfqLookup[$key]; $count_total_received = $receivedLookup[$key] ?? 0; break; } } // Fallback: Use direct database query if lookup fails if ($count_total == 0) { $directRfqCount = DB::table( 'tender_subcon_allocated', ) ->where( 'tender_id', $tender['id'], ) ->where('subcon_id', '!=', 0) ->where(function ($query) use ( $cat, ) { $query ->where( 'main_trade', 'LIKE', '%' . $cat->tra_id . '%', ) ->where( 'sub_division', 'LIKE', '%' . $cat->subdiv_id . '%', ) ->where( 'cat_id', $cat->id, ); }) ->distinct('subcon_id') ->count(); $directReceivedCount = DB::table( 'tender_subcon_allocated', ) ->where( 'tender_id', $tender['id'], ) ->where('subcon_id', '!=', 0) ->where(function ($query) use ( $cat, ) { $query ->where( 'main_trade', 'LIKE', '%' . $cat->tra_id . '%', ) ->where( 'sub_division', 'LIKE', '%' . $cat->subdiv_id . '%', ) ->where( 'cat_id', $cat->id, ); }) ->where(function ($query) { $query ->where(function ($q) { $q->where( 'method_type', 1, )->whereNotNull( 'subcon_file_id', ); }) ->orWhere(function ( $q, ) { $q->where( 'method_type', 0, )->whereNotNull( 'subcon_file_id', ); }); }) ->distinct('subcon_id') ->count(); $count_total = $directRfqCount; $count_total_received = $directReceivedCount; } $count_pending = $count_total - $count_total_received; @endphp @endforeach @endif @empty @endforelse
Project Name Cut-off Date Subcontractor Subdivision RFQ Issued Quotation Received Quotation Pending
{{ $tender['project_name'] }} {{ $tender['dead_line'] ? Utility::site_date_format($tender['dead_line'], \Auth::user()->id) : '-' }} @foreach ($tender['sub_con_details'] as $sub_con) @php $subconName = DB::table('users') ->where('id', $sub_con) ->value('name'); @endphp @if ($subconName) {{ $subconName }} @endif @endforeach All Subdivision RFQ Total @if ($project_total_rfq > 0) {{ $project_total_rfq }} @else - @endif @if ($project_total_received > 0) {{ $project_total_received }} @else - @endif @if ($project_total_pending > 0) {{ $project_total_pending }} @else - @endif
{{ $cat->category_name }} @if ($count_total > 0) {{ $count_total }} @else - @endif @if ($count_total_received > 0) {{ $count_total_received }} @else - @endif @if ($count_pending > 0) {{ $count_pending }} @else - @endif
No data available
@include('new_layouts.footer')