@php
$PendingStatus = DB::table('projection_orders')
->leftJoin(
'projection_order_confirms',
'projection_orders.id',
'=',
'projection_order_confirms.projection_order_id',
)
->leftJoin('p_i_s', 'projection_orders.id', '=', 'p_i_s.projection_order_id')
->leftJoin(
'supply_chain_entries',
'projection_orders.id',
'=',
'supply_chain_entries.projection_order_id',
)
->leftJoin('yarn_mushuks', 'projection_orders.id', '=', 'yarn_mushuks.projection_order_id')
->leftJoin(
'commercial_master_entries',
'projection_orders.id',
'=',
'commercial_master_entries.projection_order_id',
)
->leftJoin(
'actual_deliveries',
'projection_orders.id',
'=',
'actual_deliveries.projection_order_id',
)
->leftJoin(
'commercial_document_preparations',
'projection_orders.id',
'=',
'commercial_document_preparations.projection_order_id',
)
->leftJoin(
'commercial_invoices',
'projection_orders.id',
'=',
'commercial_invoices.projection_order_id',
)
->leftJoin(
'accountancies',
'projection_orders.id',
'=',
'accountancies.projection_order_id',
)
->leftJoin(
'buyer_acceptances',
'projection_orders.buyer_id',
'=',
'buyer_acceptances.buyer_id',
)
->leftJoin(
DB::raw(
'(SELECT projection_order_id, SUM(delivery_value) as total_delivery_value FROM actual_deliveries GROUP BY projection_order_id) as ad',
),
'projection_orders.id',
'=',
'ad.projection_order_id',
)
->select(
'projection_orders.buyer_name as buyer_name',
'projection_orders.job_no as job_no',
'projection_orders.season as season',
'p_i_s.buyer_export_lc_no as buyer_export_lc_no',
'p_i_s.lc_value as lc_value',
'yarn_mushuks.yarn_lc_no as yarn_lc_no',
'supply_chain_entries.yarn_pi_no as yarn_pi_no',
'actual_deliveries.delivery_qty as delivery_qty',
'actual_deliveries.delivery_value as delivery_value',
'commercial_invoices.invoice_value as invoice_value',
'accountancies.maturity_date as maturity_date_final',
'accountancies.bank_sub_date as bank_sub_date_final',
'commercial_master_entries.up_number as up_number_final',
'commercial_master_entries.ip_rcvd_date as ip_rcvd_date_final',
'accountancies.ldbc_bill_no as ldbc_bill_no_final',
DB::raw(
'CASE WHEN p_i_s.buyer_export_lc_no IS NULL THEN 1 ELSE 0 END as buyer_export_lc_no_status',
),
DB::raw(
'CASE WHEN p_i_s.lc_value IS NULL THEN 1 ELSE 0 END as buyer_export_lc_value_status',
),
DB::raw(
'CASE WHEN supply_chain_entries.yarn_pi_no IS NULL THEN 1 ELSE 0 END as yarn_pi_no_status',
),
DB::raw(
'CASE WHEN yarn_mushuks.yarn_lc_no IS NULL THEN 1 ELSE 0 END as yarn_lc_no_status',
),
DB::raw('CASE WHEN yarn_mushuks.mushok_date IS NULL THEN 1 ELSE 0 END as mushok_date'),
DB::raw(
'CASE WHEN commercial_master_entries.ip_rcvd_date IS NULL THEN 1 ELSE 0 END as ip_rcvd_date',
),
DB::raw(
'CASE WHEN commercial_master_entries.up_number IS NULL THEN 1 ELSE 0 END as up_number',
),
DB::raw(
'CASE WHEN actual_deliveries.delivery_qty IS NULL THEN 1 ELSE 0 END as delivery_qty_status',
),
DB::raw(
'CASE WHEN DATEADD(DAY, CAST(buyer_acceptances.lead_time AS INT), commercial_master_entries.up_issue_date) > GETDATE() THEN 1 ELSE 0 END as buyer_acceptance_status',
),
DB::raw(
'CASE WHEN commercial_invoices.doc_curt_status IS NULL THEN 1 ELSE 0 END as doc_curt_status',
),
DB::raw(
'CASE WHEN accountancies.ldbc_bill_no IS NULL THEN 1 ELSE 0 END as ldbc_bill_no',
),
DB::raw(
'CASE WHEN accountancies.maturity_date IS NULL THEN 1 ELSE 0 END as maturity_date',
),
DB::raw(
'CASE WHEN accountancies.bank_sub_date IS NULL THEN 1 ELSE 0 END as bank_sub_date',
),
DB::raw('p_i_s.lc_value - COALESCE(ad.total_delivery_value, 0) as document_pending'),
)
->groupBy(
'projection_orders.buyer_name',
'projection_orders.job_no',
'projection_orders.season',
'p_i_s.buyer_export_lc_no',
'p_i_s.lc_value',
'yarn_mushuks.yarn_lc_no',
'supply_chain_entries.yarn_pi_no',
'actual_deliveries.delivery_qty',
'actual_deliveries.delivery_value',
'commercial_invoices.invoice_value',
'accountancies.maturity_date',
'accountancies.bank_sub_date',
'commercial_master_entries.up_number',
'commercial_master_entries.ip_rcvd_date',
'accountancies.ldbc_bill_no',
DB::raw('CASE WHEN p_i_s.buyer_export_lc_no IS NULL THEN 1 ELSE 0 END'),
DB::raw('CASE WHEN p_i_s.lc_value IS NULL THEN 1 ELSE 0 END'),
DB::raw('CASE WHEN supply_chain_entries.yarn_pi_no IS NULL THEN 1 ELSE 0 END'),
DB::raw('CASE WHEN yarn_mushuks.yarn_lc_no IS NULL THEN 1 ELSE 0 END'),
DB::raw('CASE WHEN yarn_mushuks.mushok_date IS NULL THEN 1 ELSE 0 END'),
DB::raw('CASE WHEN commercial_master_entries.ip_rcvd_date IS NULL THEN 1 ELSE 0 END'),
DB::raw('CASE WHEN commercial_master_entries.up_number IS NULL THEN 1 ELSE 0 END'),
DB::raw('CASE WHEN actual_deliveries.delivery_qty IS NULL THEN 1 ELSE 0 END'),
DB::raw(
'CASE WHEN DATEADD(DAY, CAST(buyer_acceptances.lead_time AS INT), commercial_master_entries.up_issue_date) > GETDATE() THEN 1 ELSE 0 END',
),
DB::raw('CASE WHEN commercial_invoices.doc_curt_status IS NULL THEN 1 ELSE 0 END'),
DB::raw('CASE WHEN accountancies.ldbc_bill_no IS NULL THEN 1 ELSE 0 END'),
DB::raw('CASE WHEN accountancies.maturity_date IS NULL THEN 1 ELSE 0 END'),
DB::raw('CASE WHEN accountancies.bank_sub_date IS NULL THEN 1 ELSE 0 END'),
'ad.total_delivery_value', // Added to GROUP BY
)
->distinct('projection_orders.id')
->get();
@endphp
| Buyer Name |
Job No |
Season |
Export LC |
Supply Chain Department |
Yearn / Mushuk |
Commercial |
Actual Delivery |
Commercial Invoice |
Accounts |
| Buyer Export LC / SC |
LC / SC Amount |
Yarn PI No |
Yarn LC No |
Yarn Mushok Received |
UD/IP Rcvd Date |
UP Number |
Delivery Start |
Delivary Qty |
Delivary Value |
Invoice Value |
Acceptence Recv Buyer |
Bank Sub Date |
LDBC Bill No |
Maturity Date |
Document Pending |
@forelse ($PendingStatus as $item)
| {{ $item->buyer_name }} |
{{ $item->job_no }} |
{{ $item->season }} |
@if ($item->buyer_export_lc_no_status == 1)
Pending
@else
{{ $item->buyer_export_lc_no }}
@endif
|
@if ($item->buyer_export_lc_value_status == 1)
Pending
@else
{{ $item->lc_value }}
@endif
|
@if ($item->yarn_pi_no_status == 1)
Pending
@else
{{ $item->yarn_pi_no }}
@endif
|
@if ($item->yarn_lc_no_status == 1)
Pending
@else
{{ $item->yarn_lc_no }}
@endif
|
@if ($item->mushok_date == 1)
NO
@else
Yes
@endif
|
@if ($item->ip_rcvd_date == 1)
Pending
@else
{{ $item->ip_rcvd_date_final }}
@endif
|
@if ($item->up_number == 1)
Pending
@else
{{ $item->up_number_final }}
@endif
|
@if ($item->delivery_qty_status == 1)
No
@else
Yes
@endif
|
{{ $item->delivery_qty ?? '0' }} |
{{ $item->delivery_value ?? '0' }} |
@if ($item->doc_curt_status == 1)
Pending
@else
{{ $item->invoice_value }}
@endif
|
@if ($item->buyer_acceptance_status == 1)
Yes
@else
No
@endif
|
@if ($item->bank_sub_date == 1)
Pending
@else
{{ $item->bank_sub_date_final }}
@endif
|
@if ($item->ldbc_bill_no == 1)
Pending
@else
{{ $item->ldbc_bill_no_final }}
@endif
|
@if ($item->maturity_date == 1)
Pending
@else
{{ $item->maturity_date_final }}
@endif
|
{{ $item->document_pending ?? '0' }} |
@empty
| No data available |
@endforelse