578 lines
20 KiB
PHP
578 lines
20 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;
|
|
|
|
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,
|
|
];
|
|
}
|
|
|
|
// MARK CHECK: strict - collect ALL marks + unmatched rows
|
|
$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));
|
|
|
|
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;
|
|
}
|
|
|
|
$unmatchedRowsData[] = [
|
|
'excel_row' => $headerRowIndex + 1 + $offset,
|
|
'mark_no' => $rowMark,
|
|
'data' => $rowData,
|
|
];
|
|
}
|
|
|
|
return back()
|
|
->withErrors(['excel_file' => 'Some mark numbers are not found in Mark List. Container not created.'])
|
|
->withInput()
|
|
->with('unmatched_rows', $unmatchedRowsData);
|
|
}
|
|
|
|
// 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->invoice_number = $this->generateInvoiceNumber();
|
|
$invoice->invoice_date = now()->toDateString();
|
|
$invoice->due_date = null;
|
|
|
|
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;
|
|
|
|
$invoice->customer_email = null;
|
|
$invoice->customer_address = null;
|
|
$invoice->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'];
|
|
|
|
$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;
|
|
|
|
InvoiceItem::create([
|
|
'invoice_id' => $invoice->id,
|
|
'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;
|
|
|
|
$data = $row->data ?? [];
|
|
|
|
foreach ($cols as $colHeader => $value) {
|
|
$data[$colHeader] = $value;
|
|
}
|
|
|
|
$row->update([
|
|
'data' => $data,
|
|
]);
|
|
}
|
|
|
|
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:pending,in-progress,completed,cancelled']);
|
|
|
|
$container->update(['status' => $request->status]);
|
|
|
|
return redirect()->route('containers.index')->with('success', 'Status updated.');
|
|
}
|
|
|
|
public function destroy(Container $container)
|
|
{
|
|
$container->delete();
|
|
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);
|
|
}
|
|
}
|