952 lines
35 KiB
PHP
952 lines
35 KiB
PHP
<?php
|
||
|
||
namespace App\Http\Controllers;
|
||
|
||
use App\Models\Container;
|
||
use App\Models\ContainerRow;
|
||
use App\Models\MarkList;
|
||
use App\Models\Invoice;
|
||
use App\Models\InvoiceItem;
|
||
use Illuminate\Http\Request;
|
||
use Maatwebsite\Excel\Facades\Excel;
|
||
use Carbon\Carbon;
|
||
use Barryvdh\DomPDF\Facade\Pdf;
|
||
use Illuminate\Support\Facades\Storage; // <-- added for Excel download
|
||
|
||
class ContainerController extends Controller
|
||
{
|
||
public function index()
|
||
{
|
||
$containers = Container::with('rows')->latest()->get();
|
||
|
||
$containers->each(function ($container) {
|
||
$rows = $container->rows;
|
||
|
||
$totalCtn = 0;
|
||
$totalQty = 0;
|
||
$totalCbm = 0;
|
||
$totalKg = 0;
|
||
|
||
$ctnKeys = ['CTN', 'CTNS'];
|
||
$qtyKeys = ['ITLQTY', 'TOTALQTY', 'TTLQTY', 'QTY', 'PCS', 'PIECES'];
|
||
$cbmKeys = ['TOTALCBM', 'TTLCBM', 'ITLCBM', 'CBM'];
|
||
$kgKeys = ['TOTALKG', 'TTKG', 'KG', 'WEIGHT'];
|
||
|
||
$getFirstNumeric = function (array $data, array $possibleKeys) {
|
||
$normalizedMap = [];
|
||
|
||
foreach ($data as $key => $value) {
|
||
if ($key === null || $key === '') continue;
|
||
|
||
$normKey = strtoupper((string)$key);
|
||
$normKey = str_replace([' ', '/', '-', '.'], '', $normKey);
|
||
$normalizedMap[$normKey] = $value;
|
||
}
|
||
|
||
foreach ($possibleKeys as $search) {
|
||
$normSearch = strtoupper($search);
|
||
$normSearch = str_replace([' ', '/', '-', '.'], '', $normSearch);
|
||
|
||
foreach ($normalizedMap as $nKey => $value) {
|
||
if (
|
||
strpos($nKey, $normSearch) !== false &&
|
||
(is_numeric($value) || (is_string($value) && is_numeric(trim($value))))
|
||
) {
|
||
return (float) trim($value);
|
||
}
|
||
}
|
||
}
|
||
|
||
return 0;
|
||
};
|
||
|
||
foreach ($rows as $row) {
|
||
$data = $row->data ?? [];
|
||
|
||
$totalCtn += $getFirstNumeric($data, $ctnKeys);
|
||
$totalQty += $getFirstNumeric($data, $qtyKeys);
|
||
$totalCbm += $getFirstNumeric($data, $cbmKeys);
|
||
$totalKg += $getFirstNumeric($data, $kgKeys);
|
||
}
|
||
|
||
$container->summary = [
|
||
'total_ctn' => round($totalCtn, 2),
|
||
'total_qty' => round($totalQty, 2),
|
||
'total_cbm' => round($totalCbm, 3),
|
||
'total_kg' => round($totalKg, 2),
|
||
];
|
||
});
|
||
|
||
return view('admin.container', compact('containers'));
|
||
}
|
||
|
||
public function create()
|
||
{
|
||
return view('admin.container_create');
|
||
}
|
||
|
||
private function isValidExcelFormat($rows, $header)
|
||
{
|
||
if (empty($header) || count($rows) < 2) return false;
|
||
|
||
$validKeywords = [
|
||
'MARK', 'DESCRIPTION', 'DESC', 'CTN', 'CTNS', 'QTY', 'TOTALQTY', 'ITLQTY', 'ITL QTY',
|
||
'UNIT', 'CBM', 'TOTAL CBM', 'KG', 'TOTAL KG',
|
||
'METAL BUCKLE', 'WATCH MOVEMENT', 'STEEL BOTTLE',
|
||
'MEHULPAID', 'ITEM NO', 'ITEM NO.', 'SAHILPAID', 'PINAKIN', 'GST',
|
||
'MOON LAMP', 'TRANSPARENT BOTTLE', 'PLASTIC FONDANT',
|
||
];
|
||
|
||
$headerText = implode(' ', array_map('strtoupper', $header));
|
||
$requiredHeaders = ['CTN', 'QTY', 'DESCRIPTION', 'DESC'];
|
||
|
||
$hasValidHeaders = false;
|
||
foreach ($requiredHeaders as $key) {
|
||
if (stripos($headerText, $key) !== false) {
|
||
$hasValidHeaders = true;
|
||
break;
|
||
}
|
||
}
|
||
|
||
if (!$hasValidHeaders) return false;
|
||
|
||
$dataPreview = '';
|
||
for ($i = 0; $i < min(5, count($rows)); $i++) {
|
||
$rowText = implode(' ', array_slice($rows[$i], 0, 10));
|
||
$dataPreview .= ' ' . strtoupper((string)$rowText);
|
||
}
|
||
|
||
$validMatches = 0;
|
||
foreach ($validKeywords as $keyword) {
|
||
if (stripos($headerText . $dataPreview, strtoupper($keyword)) !== false) {
|
||
$validMatches++;
|
||
}
|
||
}
|
||
|
||
return $validMatches >= 3;
|
||
}
|
||
|
||
private function normalizeKey($value): string
|
||
{
|
||
$norm = strtoupper((string)$value);
|
||
return str_replace([' ', '/', '-', '.'], '', $norm);
|
||
}
|
||
|
||
public function store(Request $request)
|
||
{
|
||
$request->validate([
|
||
'container_name' => 'required|string',
|
||
'container_number' => 'required|string|unique:containers,container_number',
|
||
'container_date' => 'required|date',
|
||
'excel_file' => 'required|file|mimes:xls,xlsx',
|
||
]);
|
||
|
||
$file = $request->file('excel_file');
|
||
$sheets = Excel::toArray([], $file);
|
||
$rows = $sheets[0] ?? [];
|
||
|
||
if (count($rows) < 2) {
|
||
return back()
|
||
->withErrors(['excel_file' => 'Excel file is empty.'])
|
||
->withInput();
|
||
}
|
||
|
||
// HEADER DETECTION
|
||
$headerRowIndex = null;
|
||
$header = [];
|
||
|
||
foreach ($rows as $i => $row) {
|
||
$trimmed = array_map(fn($v) => trim((string)$v), $row);
|
||
$nonEmpty = array_filter($trimmed, fn($v) => $v !== '');
|
||
if (empty($nonEmpty)) continue;
|
||
|
||
if (count($nonEmpty) >= 4) {
|
||
$headerRowIndex = $i;
|
||
$header = $trimmed;
|
||
break;
|
||
}
|
||
}
|
||
|
||
if ($headerRowIndex === null) {
|
||
return back()
|
||
->withErrors(['excel_file' => 'Header row not found in Excel.'])
|
||
->withInput();
|
||
}
|
||
|
||
if (!$this->isValidExcelFormat($rows, $header)) {
|
||
return back()
|
||
->withErrors(['excel_file' => 'Only MEHUL / SAHIL / PINAKIN / GST loading list formats allowed.'])
|
||
->withInput();
|
||
}
|
||
|
||
// COLUMN INDEXES
|
||
$essentialColumns = [
|
||
'desc_col' => null,
|
||
'ctn_col' => null,
|
||
'qty_col' => null,
|
||
'totalqty_col' => null,
|
||
'unit_col' => null,
|
||
'price_col' => null,
|
||
'amount_col' => null,
|
||
'cbm_col' => null,
|
||
'totalcbm_col' => null,
|
||
'kg_col' => null,
|
||
'totalkg_col' => null,
|
||
'itemno_col' => null,
|
||
];
|
||
|
||
foreach ($header as $colIndex => $headingText) {
|
||
if (empty($headingText)) continue;
|
||
|
||
$normalized = $this->normalizeKey($headingText);
|
||
|
||
if (strpos($normalized, 'DESCRIPTION') !== false || strpos($normalized, 'DESC') !== false) {
|
||
$essentialColumns['desc_col'] = $colIndex;
|
||
} elseif (strpos($normalized, 'CTN') !== false || strpos($normalized, 'CTNS') !== false) {
|
||
$essentialColumns['ctn_col'] = $colIndex;
|
||
} elseif (
|
||
strpos($normalized, 'ITLQTY') !== false ||
|
||
strpos($normalized, 'TOTALQTY') !== false ||
|
||
strpos($normalized, 'TTLQTY') !== false
|
||
) {
|
||
$essentialColumns['totalqty_col'] = $colIndex;
|
||
} elseif (strpos($normalized, 'QTY') !== false) {
|
||
$essentialColumns['qty_col'] = $colIndex;
|
||
} elseif (strpos($normalized, 'UNIT') !== false) {
|
||
$essentialColumns['unit_col'] = $colIndex;
|
||
} elseif (strpos($normalized, 'PRICE') !== false) {
|
||
$essentialColumns['price_col'] = $colIndex;
|
||
} elseif (strpos($normalized, 'AMOUNT') !== false) {
|
||
$essentialColumns['amount_col'] = $colIndex;
|
||
} elseif (strpos($normalized, 'TOTALCBM') !== false || strpos($normalized, 'ITLCBM') !== false) {
|
||
$essentialColumns['totalcbm_col'] = $colIndex;
|
||
} elseif (strpos($normalized, 'CBM') !== false) {
|
||
$essentialColumns['cbm_col'] = $colIndex;
|
||
} elseif (strpos($normalized, 'TOTALKG') !== false || strpos($normalized, 'TTKG') !== false) {
|
||
$essentialColumns['totalkg_col'] = $colIndex;
|
||
} elseif (strpos($normalized, 'KG') !== false) {
|
||
$essentialColumns['kg_col'] = $colIndex;
|
||
} elseif (
|
||
strpos($normalized, 'MARKNO') !== false ||
|
||
strpos($normalized, 'MARK') !== false ||
|
||
strpos($normalized, 'ITEMNO') !== false ||
|
||
strpos($normalized, 'ITEM') !== false
|
||
) {
|
||
$essentialColumns['itemno_col'] = $colIndex;
|
||
}
|
||
}
|
||
|
||
if (is_null($essentialColumns['itemno_col'])) {
|
||
return back()
|
||
->withErrors(['excel_file' => 'Mark / Item column not found in Excel (expected headers like MARK NO / Mark_No / Item_No).'])
|
||
->withInput();
|
||
}
|
||
|
||
// ROWS CLEANING
|
||
$dataRows = array_slice($rows, $headerRowIndex + 1);
|
||
$cleanedRows = [];
|
||
$unmatchedRowsData = [];
|
||
|
||
foreach ($dataRows as $offset => $row) {
|
||
$trimmedRow = array_map(fn($v) => trim((string)$v), $row);
|
||
$nonEmptyCells = array_filter($trimmedRow, fn($v) => $v !== '');
|
||
if (count($nonEmptyCells) < 2) continue;
|
||
|
||
$rowText = strtoupper(implode(' ', $trimmedRow));
|
||
if (
|
||
stripos($rowText, 'TOTAL') !== false ||
|
||
stripos($rowText, 'TTL') !== false ||
|
||
stripos($rowText, 'GRAND') !== false
|
||
) {
|
||
continue;
|
||
}
|
||
|
||
$descValue = '';
|
||
if ($essentialColumns['desc_col'] !== null) {
|
||
$descValue = trim($row[$essentialColumns['desc_col']] ?? '');
|
||
}
|
||
|
||
if ($essentialColumns['desc_col'] !== null && $descValue === '' && count($nonEmptyCells) >= 1) {
|
||
continue;
|
||
}
|
||
|
||
$cleanedRows[] = [
|
||
'row' => $row,
|
||
'offset' => $offset,
|
||
];
|
||
}
|
||
|
||
if (empty($cleanedRows)) {
|
||
return back()
|
||
->withErrors(['excel_file' => 'No valid item rows found in Excel.'])
|
||
->withInput();
|
||
}
|
||
|
||
// FORMULA CHECK
|
||
$cleanNumber = function ($value) {
|
||
if (is_string($value)) {
|
||
$value = str_replace(',', '', trim($value));
|
||
}
|
||
return is_numeric($value) ? (float)$value : 0;
|
||
};
|
||
|
||
$formulaErrors = [];
|
||
|
||
foreach ($cleanedRows as $item) {
|
||
$row = $item['row'];
|
||
$offset = $item['offset'];
|
||
|
||
$ctn = $essentialColumns['ctn_col'] !== null ? $cleanNumber($row[$essentialColumns['ctn_col']] ?? 0) : 0;
|
||
$qty = $essentialColumns['qty_col'] !== null ? $cleanNumber($row[$essentialColumns['qty_col']] ?? 0) : 0;
|
||
$ttlQ = $essentialColumns['totalqty_col'] !== null ? $cleanNumber($row[$essentialColumns['totalqty_col']] ?? 0) : 0;
|
||
$cbm = $essentialColumns['cbm_col'] !== null ? $cleanNumber($row[$essentialColumns['cbm_col']] ?? 0) : 0;
|
||
$ttlC = $essentialColumns['totalcbm_col'] !== null ? $cleanNumber($row[$essentialColumns['totalcbm_col']] ?? 0) : 0;
|
||
$kg = $essentialColumns['kg_col'] !== null ? $cleanNumber($row[$essentialColumns['kg_col']] ?? 0) : 0;
|
||
$ttlK = $essentialColumns['totalkg_col'] !== null ? $cleanNumber($row[$essentialColumns['totalkg_col']] ?? 0) : 0;
|
||
|
||
$price = $essentialColumns['price_col'] !== null ? $cleanNumber($row[$essentialColumns['price_col']] ?? 0) : 0;
|
||
$ttlAmount = $essentialColumns['amount_col'] !== null ? $cleanNumber($row[$essentialColumns['amount_col']] ?? 0) : 0;
|
||
|
||
$desc = $essentialColumns['desc_col'] !== null ? (string)($row[$essentialColumns['desc_col']] ?? '') : '';
|
||
$mark = $essentialColumns['itemno_col'] !== null ? (string)($row[$essentialColumns['itemno_col']] ?? '') : '';
|
||
|
||
$expTtlQty = $qty * $ctn;
|
||
$expTtlCbm = $cbm * $ctn;
|
||
$expTtlKg = $kg * $ctn;
|
||
$expTtlAmount = ($qty * $ctn) * $price;
|
||
|
||
$rowErrors = [];
|
||
|
||
if (abs($ttlQ - $expTtlQty) > 0.01) {
|
||
$rowErrors['TOTAL QTY'] = [
|
||
'actual' => $ttlQ,
|
||
'expected' => $expTtlQty,
|
||
];
|
||
}
|
||
|
||
if (abs($ttlC - $expTtlCbm) > 0.0005) {
|
||
$rowErrors['TOTAL CBM'] = [
|
||
'actual' => $ttlC,
|
||
'expected' => $expTtlCbm,
|
||
];
|
||
}
|
||
|
||
if (abs($ttlK - $expTtlKg) > 0.01) {
|
||
$rowErrors['TOTAL KG'] = [
|
||
'actual' => $ttlK,
|
||
'expected' => $expTtlKg,
|
||
];
|
||
}
|
||
|
||
if ($essentialColumns['amount_col'] !== null && $essentialColumns['price_col'] !== null) {
|
||
if (abs($ttlAmount - $expTtlAmount) > 0.01) {
|
||
$rowErrors['TOTAL AMOUNT'] = [
|
||
'actual' => $ttlAmount,
|
||
'expected' => $expTtlAmount,
|
||
];
|
||
}
|
||
}
|
||
|
||
if (!empty($rowErrors)) {
|
||
$rowData = [];
|
||
foreach ($header as $colIndex => $headingText) {
|
||
$value = $row[$colIndex] ?? null;
|
||
if (is_string($value)) $value = trim($value);
|
||
$rowData[$headingText] = $value;
|
||
}
|
||
|
||
$formulaErrors[] = [
|
||
'excel_row' => $headerRowIndex + 1 + $offset,
|
||
'mark_no' => $mark,
|
||
'description' => $desc,
|
||
'errors' => $rowErrors,
|
||
'data' => $rowData,
|
||
];
|
||
}
|
||
}
|
||
|
||
// MARK CHECK
|
||
$marksFromExcel = [];
|
||
foreach ($cleanedRows as $item) {
|
||
$row = $item['row'];
|
||
$rawMark = $row[$essentialColumns['itemno_col']] ?? null;
|
||
$mark = trim((string)($rawMark ?? ''));
|
||
if ($mark !== '') {
|
||
$marksFromExcel[] = $mark;
|
||
}
|
||
}
|
||
|
||
$marksFromExcel = array_values(array_unique($marksFromExcel));
|
||
|
||
if (empty($marksFromExcel)) {
|
||
return back()
|
||
->withErrors(['excel_file' => 'No mark numbers found in Excel file.'])
|
||
->withInput();
|
||
}
|
||
|
||
$validMarks = MarkList::whereIn('mark_no', $marksFromExcel)
|
||
->where('status', 'active')
|
||
->pluck('mark_no')
|
||
->toArray();
|
||
|
||
$unmatchedMarks = array_values(array_diff($marksFromExcel, $validMarks));
|
||
|
||
$markErrors = [];
|
||
|
||
if (!empty($unmatchedMarks)) {
|
||
foreach ($cleanedRows as $item) {
|
||
$row = $item['row'];
|
||
$offset = $item['offset'];
|
||
$rowMark = trim((string)($row[$essentialColumns['itemno_col']] ?? ''));
|
||
|
||
if ($rowMark === '' || !in_array($rowMark, $unmatchedMarks)) {
|
||
continue;
|
||
}
|
||
|
||
$rowData = [];
|
||
foreach ($header as $colIndex => $headingText) {
|
||
$value = $row[$colIndex] ?? null;
|
||
if (is_string($value)) $value = trim($value);
|
||
$rowData[$headingText] = $value;
|
||
}
|
||
|
||
$markErrors[] = [
|
||
'excel_row' => $headerRowIndex + 1 + $offset,
|
||
'mark_no' => $rowMark,
|
||
'data' => $rowData,
|
||
];
|
||
}
|
||
}
|
||
|
||
if (!empty($formulaErrors) || !empty($markErrors)) {
|
||
return back()
|
||
->withInput()
|
||
->with([
|
||
'formula_errors' => $formulaErrors,
|
||
'mark_errors' => $markErrors,
|
||
]);
|
||
}
|
||
|
||
// STEP 1: Marks → customers mapping + grouping
|
||
$markRecords = MarkList::whereIn('mark_no', $marksFromExcel)
|
||
->where('status', 'active')
|
||
->get();
|
||
|
||
$markToCustomerId = [];
|
||
$markToSnapshot = [];
|
||
|
||
foreach ($markRecords as $mr) {
|
||
$markToCustomerId[$mr->mark_no] = $mr->customer_id;
|
||
|
||
$markToSnapshot[$mr->mark_no] = [
|
||
'customer_name' => $mr->customer_name,
|
||
'company_name' => $mr->company_name,
|
||
'mobile_no' => $mr->mobile_no,
|
||
];
|
||
}
|
||
|
||
$groupedByCustomer = [];
|
||
|
||
foreach ($cleanedRows as $item) {
|
||
$row = $item['row'];
|
||
$offset = $item['offset'];
|
||
|
||
$rawMark = $row[$essentialColumns['itemno_col']] ?? null;
|
||
$mark = trim((string)($rawMark ?? ''));
|
||
|
||
if ($mark === '') {
|
||
continue;
|
||
}
|
||
|
||
$customerId = $markToCustomerId[$mark] ?? null;
|
||
if (!$customerId) {
|
||
continue;
|
||
}
|
||
|
||
if (!isset($groupedByCustomer[$customerId])) {
|
||
$groupedByCustomer[$customerId] = [];
|
||
}
|
||
|
||
$groupedByCustomer[$customerId][] = [
|
||
'row' => $row,
|
||
'offset' => $offset,
|
||
'mark' => $mark,
|
||
];
|
||
}
|
||
|
||
// STEP 2: Container + ContainerRows save
|
||
$container = Container::create([
|
||
'container_name' => $request->container_name,
|
||
'container_number' => $request->container_number,
|
||
'container_date' => $request->container_date,
|
||
'status' => 'pending',
|
||
]);
|
||
|
||
$path = $file->store('containers');
|
||
$container->update(['excel_file' => $path]);
|
||
|
||
$savedCount = 0;
|
||
|
||
foreach ($cleanedRows as $item) {
|
||
$row = $item['row'];
|
||
$offset = $item['offset'];
|
||
|
||
$data = [];
|
||
foreach ($header as $colIndex => $headingText) {
|
||
$value = $row[$colIndex] ?? null;
|
||
if (is_string($value)) $value = trim($value);
|
||
$data[$headingText] = $value;
|
||
}
|
||
|
||
ContainerRow::create([
|
||
'container_id' => $container->id,
|
||
'row_index' => $headerRowIndex + 1 + $offset,
|
||
'data' => $data,
|
||
]);
|
||
|
||
$savedCount++;
|
||
}
|
||
|
||
// STEP 3: per-customer invoices + invoice items
|
||
$invoiceCount = 0;
|
||
|
||
foreach ($groupedByCustomer as $customerId => $rowsForCustomer) {
|
||
if (empty($rowsForCustomer)) {
|
||
continue;
|
||
}
|
||
|
||
$firstMark = $rowsForCustomer[0]['mark'];
|
||
$snap = $markToSnapshot[$firstMark] ?? null;
|
||
|
||
$invoice = new Invoice();
|
||
$invoice->container_id = $container->id;
|
||
$invoice->customer_id = $customerId;
|
||
$invoice->mark_no = $firstMark;
|
||
|
||
$invoice->invoice_number = $this->generateInvoiceNumber();
|
||
|
||
// invoice_date = container_date
|
||
$invoice->invoice_date = $container->container_date;
|
||
|
||
// due_date = container_date + 10 days
|
||
$invoice->due_date = Carbon::parse($invoice->invoice_date)
|
||
->addDays(10)
|
||
->format('Y-m-d');
|
||
|
||
// Customer User model वरून fetch करा (customer_id string आहे जसे CID-2025-000001)
|
||
$customerUser = \App\Models\User::where('customer_id', $customerId)->first();
|
||
|
||
$invoice->container_id = $container->id;
|
||
$invoice->customer_id = $customerUser->id ?? null; // ✅ integer id
|
||
$invoice->mark_no = $firstMark;
|
||
|
||
if ($snap) {
|
||
$invoice->customer_name = $snap['customer_name'] ?? null;
|
||
$invoice->company_name = $snap['company_name'] ?? null;
|
||
$invoice->customer_mobile = $snap['mobile_no'] ?? null;
|
||
}
|
||
|
||
$invoice->final_amount = 0;
|
||
$invoice->gst_percent = 0;
|
||
$invoice->gst_amount = 0;
|
||
$invoice->final_amount_with_gst = 0;
|
||
|
||
// ✅ User model वरून email, address, pincode घ्या
|
||
$invoice->customer_email = $customerUser->email ?? null;
|
||
$invoice->customer_address = $customerUser->address ?? null;
|
||
$invoice->pincode = $customerUser->pincode ?? null;
|
||
|
||
$uniqueMarks = array_unique(array_column($rowsForCustomer, 'mark'));
|
||
$invoice->notes = 'Auto-created from Container ' . $container->container_number
|
||
. ' for Mark(s): ' . implode(', ', $uniqueMarks);
|
||
$invoice->pdf_path = null;
|
||
$invoice->status = 'pending';
|
||
|
||
$invoice->save();
|
||
$invoiceCount++;
|
||
|
||
$totalAmount = 0;
|
||
|
||
foreach ($rowsForCustomer as $item) {
|
||
$row = $item['row'];
|
||
$offset = $item['offset'];
|
||
|
||
$description = $essentialColumns['desc_col'] !== null ? ($row[$essentialColumns['desc_col']] ?? null) : null;
|
||
$ctn = $essentialColumns['ctn_col'] !== null ? (int) ($row[$essentialColumns['ctn_col']] ?? 0) : 0;
|
||
$qty = $essentialColumns['qty_col'] !== null ? (int) ($row[$essentialColumns['qty_col']] ?? 0) : 0;
|
||
$ttlQty = $essentialColumns['totalqty_col'] !== null ? (int) ($row[$essentialColumns['totalqty_col']] ?? 0) : $qty;
|
||
$unit = $essentialColumns['unit_col'] !== null ? ($row[$essentialColumns['unit_col']] ?? null) : null;
|
||
$price = $essentialColumns['price_col'] !== null ? (float) ($row[$essentialColumns['price_col']] ?? 0) : 0;
|
||
$ttlAmount = $essentialColumns['amount_col'] !== null ? (float) ($row[$essentialColumns['amount_col']] ?? 0) : 0;
|
||
$cbm = $essentialColumns['cbm_col'] !== null ? (float) ($row[$essentialColumns['cbm_col']] ?? 0) : 0;
|
||
$ttlCbm = $essentialColumns['totalcbm_col'] !== null ? (float) ($row[$essentialColumns['totalcbm_col']] ?? $cbm) : $cbm;
|
||
$kg = $essentialColumns['kg_col'] !== null ? (float) ($row[$essentialColumns['kg_col']] ?? 0) : 0;
|
||
$ttlKg = $essentialColumns['totalkg_col'] !== null ? (float) ($row[$essentialColumns['totalkg_col']] ?? $kg) : $kg;
|
||
|
||
$rowIndex = $headerRowIndex + 1 + $offset;
|
||
|
||
InvoiceItem::create([
|
||
'invoice_id' => $invoice->id,
|
||
'container_id' => $container->id,
|
||
'container_row_index' => $rowIndex,
|
||
'description' => $description,
|
||
'ctn' => $ctn,
|
||
'qty' => $qty,
|
||
'ttl_qty' => $ttlQty,
|
||
'unit' => $unit,
|
||
'price' => $price,
|
||
'ttl_amount' => $ttlAmount,
|
||
'cbm' => $cbm,
|
||
'ttl_cbm' => $ttlCbm,
|
||
'kg' => $kg,
|
||
'ttl_kg' => $ttlKg,
|
||
'shop_no' => null,
|
||
]);
|
||
|
||
$totalAmount += $ttlAmount;
|
||
}
|
||
|
||
$invoice->final_amount = $totalAmount;
|
||
$invoice->gst_percent = 0;
|
||
$invoice->gst_amount = 0;
|
||
$invoice->final_amount_with_gst = $totalAmount;
|
||
|
||
$invoice->save();
|
||
}
|
||
|
||
$msg = "Container '{$container->container_number}' created with {$savedCount} rows and {$invoiceCount} customer invoice(s).";
|
||
return redirect()->route('containers.index')->with('success', $msg);
|
||
}
|
||
|
||
public function show(Container $container)
|
||
{
|
||
$container->load('rows');
|
||
return view('admin.container_show', compact('container'));
|
||
}
|
||
|
||
public function updateRows(Request $request, Container $container)
|
||
{
|
||
$rowsInput = $request->input('rows', []);
|
||
|
||
foreach ($rowsInput as $rowId => $cols) {
|
||
$row = ContainerRow::where('container_id', $container->id)
|
||
->where('id', $rowId)
|
||
->first();
|
||
|
||
if (!$row) {
|
||
continue;
|
||
}
|
||
|
||
// 1) update container_rows.data
|
||
$data = $row->data ?? [];
|
||
foreach ($cols as $colHeader => $value) {
|
||
$data[$colHeader] = $value;
|
||
}
|
||
$row->update(['data' => $data]);
|
||
|
||
// 2) normalize keys
|
||
$normalizedMap = [];
|
||
foreach ($data as $key => $value) {
|
||
if ($key === null || $key === '') {
|
||
continue;
|
||
}
|
||
|
||
$normKey = strtoupper((string)$key);
|
||
$normKey = str_replace([' ', '/', '-', '.'], '', $normKey);
|
||
$normalizedMap[$normKey] = $value;
|
||
}
|
||
|
||
// helper: get first numeric value from given keys
|
||
$getFirstNumeric = function (array $map, array $possibleKeys) {
|
||
foreach ($possibleKeys as $search) {
|
||
$normSearch = strtoupper($search);
|
||
$normSearch = str_replace([' ', '/', '-', '.'], '', $normSearch);
|
||
|
||
foreach ($map as $nKey => $value) {
|
||
if (strpos($nKey, $normSearch) !== false) {
|
||
if (is_numeric($value)) {
|
||
return (float)$value;
|
||
}
|
||
if (is_string($value) && is_numeric(trim($value))) {
|
||
return (float)trim($value);
|
||
}
|
||
}
|
||
}
|
||
}
|
||
return 0;
|
||
};
|
||
|
||
// 3) read values – QTY vs TTLQTY separately
|
||
$ctnKeys = ['CTN', 'CTNS'];
|
||
$qtyKeys = ['QTY', 'PCS', 'PIECES']; // per-carton qty
|
||
$ttlQtyKeys = ['ITLQTY', 'TOTALQTY', 'TTLQTY']; // total qty
|
||
$cbmKeys = ['TOTALCBM', 'TTLCBM', 'ITLCBM', 'CBM'];
|
||
$kgKeys = ['TOTALKG', 'TTKG', 'KG', 'WEIGHT'];
|
||
$amountKeys = ['AMOUNT', 'TTLAMOUNT', 'TOTALAMOUNT'];
|
||
|
||
$ctn = $getFirstNumeric($normalizedMap, $ctnKeys);
|
||
|
||
// per carton qty
|
||
$qty = $getFirstNumeric($normalizedMap, $qtyKeys);
|
||
|
||
// total qty direct from TOTALQTY/TTLQTY/ITLQTY
|
||
$ttlQ = $getFirstNumeric($normalizedMap, $ttlQtyKeys);
|
||
|
||
// if total column is 0 then compute ctn * qty
|
||
if ($ttlQ == 0 && $ctn && $qty) {
|
||
$ttlQ = $ctn * $qty;
|
||
}
|
||
|
||
$cbm = $getFirstNumeric($normalizedMap, ['CBM']);
|
||
$ttlC = $getFirstNumeric($normalizedMap, ['TOTALCBM', 'TTLCBM', 'ITLCBM']);
|
||
if ($ttlC == 0 && $cbm && $ctn) {
|
||
$ttlC = $cbm * $ctn;
|
||
}
|
||
|
||
$kg = $getFirstNumeric($normalizedMap, ['KG', 'WEIGHT']);
|
||
$ttlK = $getFirstNumeric($normalizedMap, ['TOTALKG', 'TTKG']);
|
||
if ($ttlK == 0 && $kg && $ctn) {
|
||
$ttlK = $kg * $ctn;
|
||
}
|
||
|
||
$price = $getFirstNumeric($normalizedMap, ['PRICE', 'RATE']);
|
||
$amount = $getFirstNumeric($normalizedMap, $amountKeys);
|
||
if ($amount == 0 && $price && $ttlQ) {
|
||
$amount = $price * $ttlQ;
|
||
}
|
||
|
||
// 4) get description
|
||
$desc = null;
|
||
foreach (['DESCRIPTION', 'DESC'] as $dKey) {
|
||
$normD = str_replace([' ', '/', '-', '.'], '', strtoupper($dKey));
|
||
foreach ($normalizedMap as $nKey => $v) {
|
||
if (strpos($nKey, $normD) !== false) {
|
||
$desc = is_string($v) ? trim($v) : $v;
|
||
break 2;
|
||
}
|
||
}
|
||
}
|
||
|
||
$rowIndex = $row->row_index;
|
||
|
||
// 5) find linked invoice_items
|
||
$items = InvoiceItem::where('container_id', $container->id)
|
||
->where('container_row_index', $rowIndex)
|
||
->get();
|
||
|
||
if ($items->isEmpty() && $desc) {
|
||
$items = InvoiceItem::where('container_id', $container->id)
|
||
->whereNull('container_row_index')
|
||
->where('description', $desc)
|
||
->get();
|
||
}
|
||
|
||
// 6) update invoice_items + recalc invoice totals
|
||
foreach ($items as $item) {
|
||
$item->description = $desc;
|
||
$item->ctn = $ctn;
|
||
$item->qty = $qty; // per carton
|
||
$item->ttl_qty = $ttlQ; // total
|
||
$item->price = $price;
|
||
$item->ttl_amount = $amount;
|
||
$item->cbm = $cbm;
|
||
$item->ttl_cbm = $ttlC;
|
||
$item->kg = $kg;
|
||
$item->ttl_kg = $ttlK;
|
||
$item->save();
|
||
|
||
$invoice = $item->invoice;
|
||
if ($invoice) {
|
||
$newBaseAmount = InvoiceItem::where('invoice_id', $invoice->id)
|
||
->sum('ttl_amount');
|
||
|
||
$taxType = $invoice->tax_type;
|
||
$cgstPercent = (float) ($invoice->cgst_percent ?? 0);
|
||
$sgstPercent = (float) ($invoice->sgst_percent ?? 0);
|
||
$igstPercent = (float) ($invoice->igst_percent ?? 0);
|
||
|
||
$gstPercent = 0;
|
||
if ($taxType === 'gst') {
|
||
$gstPercent = $cgstPercent + $sgstPercent;
|
||
} elseif ($taxType === 'igst') {
|
||
$gstPercent = $igstPercent;
|
||
}
|
||
|
||
$gstAmount = $newBaseAmount * $gstPercent / 100;
|
||
$finalWithGst = $newBaseAmount + $gstAmount;
|
||
|
||
$invoice->final_amount = $newBaseAmount;
|
||
$invoice->gst_amount = $gstAmount;
|
||
$invoice->final_amount_with_gst = $finalWithGst;
|
||
$invoice->gst_percent = $gstPercent;
|
||
$invoice->save();
|
||
}
|
||
}
|
||
}
|
||
|
||
return redirect()
|
||
->route('containers.show', $container->id)
|
||
->with('success', 'Excel rows updated successfully.');
|
||
}
|
||
|
||
public function updateStatus(Request $request, Container $container)
|
||
{
|
||
$request->validate([
|
||
'status' => 'required|in:container-ready,export-custom,international-transit,arrived-at-india,import-custom,warehouse,domestic-distribution,out-for-delivery,delivered',
|
||
]);
|
||
|
||
$container->status = $request->status;
|
||
$container->save();
|
||
|
||
if ($request->wantsJson() || $request->ajax()) {
|
||
return response()->json([
|
||
'success' => true,
|
||
'status' => $container->status,
|
||
]);
|
||
}
|
||
|
||
return back()->with('success', 'Container status updated.');
|
||
}
|
||
|
||
public function destroy(Container $container)
|
||
{
|
||
$container->delete();
|
||
|
||
if (request()->wantsJson() || request()->ajax()) {
|
||
return response()->json([
|
||
'success' => true,
|
||
'message' => 'Container deleted',
|
||
]);
|
||
}
|
||
|
||
return redirect()
|
||
->route('containers.index')
|
||
->with('success', 'Container deleted.');
|
||
}
|
||
|
||
private function generateInvoiceNumber(): string
|
||
{
|
||
$year = now()->format('Y');
|
||
|
||
$last = Invoice::whereYear('created_at', $year)
|
||
->orderBy('id', 'desc')
|
||
->first();
|
||
|
||
if ($last) {
|
||
$parts = explode('-', $last->invoice_number);
|
||
$seq = 0;
|
||
|
||
if (count($parts) === 3) {
|
||
$seq = (int) $parts[2];
|
||
}
|
||
|
||
$nextSeq = $seq + 1;
|
||
} else {
|
||
$nextSeq = 1;
|
||
}
|
||
|
||
return 'INV-' . $year . '-' . str_pad($nextSeq, 6, '0', STR_PAD_LEFT);
|
||
}
|
||
|
||
public function downloadPdf(Container $container)
|
||
{
|
||
$container->load('rows');
|
||
|
||
$pdf = Pdf::loadView('admin.container_pdf', [
|
||
'container' => $container,
|
||
])->setPaper('a4', 'landscape');
|
||
|
||
$fileName = 'container-'.$container->container_number.'.pdf';
|
||
|
||
return $pdf->download($fileName);
|
||
}
|
||
|
||
public function downloadExcel(Container $container)
|
||
{
|
||
if (!$container->excel_file) {
|
||
abort(404, 'Excel file not found on record.');
|
||
}
|
||
|
||
// Stored path like "containers/abc.xlsx"
|
||
$path = $container->excel_file;
|
||
|
||
if (!Storage::exists($path)) {
|
||
abort(404, 'Excel file missing on server.');
|
||
}
|
||
|
||
$fileName = 'container-'.$container->container_number.'.xlsx';
|
||
|
||
return Storage::download($path, $fileName);
|
||
}
|
||
|
||
|
||
public function popupPopup(Container $container)
|
||
{
|
||
// existing show सारखाच data वापरू
|
||
$container->load('rows');
|
||
|
||
// summary आधीपासून index() मध्ये जसा काढतोस तसाच logic reuse
|
||
$rows = $container->rows ?? collect();
|
||
|
||
$totalCtn = 0;
|
||
$totalQty = 0;
|
||
$totalCbm = 0;
|
||
$totalKg = 0;
|
||
|
||
$ctnKeys = ['CTN', 'CTNS'];
|
||
$qtyKeys = ['ITLQTY', 'TOTALQTY', 'TTLQTY', 'QTY', 'PCS', 'PIECES'];
|
||
$cbmKeys = ['TOTALCBM', 'TTLCBM', 'ITLCBM', 'CBM'];
|
||
$kgKeys = ['TOTALKG', 'TTKG', 'KG', 'WEIGHT'];
|
||
|
||
$getFirstNumeric = function (array $data, array $possibleKeys) {
|
||
$normalizedMap = [];
|
||
foreach ($data as $key => $value) {
|
||
if ($key === null) continue;
|
||
$normKey = strtoupper((string)$key);
|
||
$normKey = str_replace([' ', ',', '-', '.', "\n", "\r", "\t"], '', $normKey);
|
||
$normalizedMap[$normKey] = $value;
|
||
}
|
||
|
||
foreach ($possibleKeys as $search) {
|
||
$normSearch = strtoupper($search);
|
||
$normSearch = str_replace([' ', ',', '-', '.', "\n", "\r", "\t"], '', $normSearch);
|
||
|
||
foreach ($normalizedMap as $nKey => $value) {
|
||
if (strpos($nKey, $normSearch) !== false) {
|
||
if (is_numeric($value)) {
|
||
return (float)$value;
|
||
}
|
||
if (is_string($value) && is_numeric(trim($value))) {
|
||
return (float)trim($value);
|
||
}
|
||
}
|
||
}
|
||
}
|
||
return 0;
|
||
};
|
||
|
||
foreach ($rows as $row) {
|
||
$data = $row->data ?? [];
|
||
if (!is_array($data)) continue;
|
||
|
||
$totalCtn += $getFirstNumeric($data, $ctnKeys);
|
||
$totalQty += $getFirstNumeric($data, $qtyKeys);
|
||
$totalCbm += $getFirstNumeric($data, $cbmKeys);
|
||
$totalKg += $getFirstNumeric($data, $kgKeys);
|
||
}
|
||
|
||
$summary = [
|
||
'total_ctn' => round($totalCtn, 2),
|
||
'total_qty' => round($totalQty, 2),
|
||
'total_cbm' => round($totalCbm, 3),
|
||
'total_kg' => round($totalKg, 2),
|
||
];
|
||
|
||
return view('admin.partials.container_popup_readonly', [
|
||
'container' => $container,
|
||
'summary' => $summary,
|
||
]);
|
||
}
|
||
|
||
}
|