migrate/app/Http/Controllers/ProductUrlsController.php

617 lines
24 KiB
PHP

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
class ProductUrlsController extends Controller
{
protected $magento1Connection = 'magento1';
protected $magento2Connection = 'magento2';
protected $magento1Prefix;
protected $magento2Prefix;
public function __construct()
{
$this->magento1Prefix = config('database.connections.magento1.prefix', '');
$this->magento2Prefix = config('database.connections.magento2.prefix', '');
}
/**
* Show the product URLs comparison page
*/
public function index()
{
return view('product-urls.index');
}
/**
* Get product URLs from Magento 1
*/
public function getMagento1Urls(Request $request)
{
try {
$sku = $request->input('sku');
$limit = $request->input('limit', 100);
$offset = $request->input('offset', 0);
$query = DB::connection($this->magento1Connection)
->table($this->magento1Prefix . 'core_url_rewrite as ur')
->join($this->magento1Prefix . 'catalog_product_entity as p', function($join) {
// Match exactly `product/{id}` or `product/{id}/...` so we don't
// false-match product 1 against product 10/100/1000/etc.
$join->whereRaw("(ur.id_path = CONCAT('product/', p.entity_id) OR ur.id_path LIKE CONCAT('product/', p.entity_id, '/%'))");
})
->select(
'p.entity_id',
'p.sku',
'ur.request_path',
'ur.target_path',
'ur.store_id',
'ur.id_path'
)
->where('ur.id_path', 'like', 'product/%');
if ($sku) {
$query->where('p.sku', 'like', '%' . $sku . '%');
}
$total = $query->count();
$urls = $query->orderBy('p.entity_id')
->orderBy('ur.store_id')
->limit($limit)
->offset($offset)
->get();
return response()->json([
'success' => true,
'urls' => $urls,
'total' => $total,
'limit' => $limit,
'offset' => $offset
]);
} catch (\Exception $e) {
Log::error('Error fetching Magento 1 URLs: ' . $e->getMessage());
return response()->json([
'success' => false,
'message' => 'Failed to fetch Magento 1 URLs: ' . $e->getMessage(),
'urls' => [],
'total' => 0
], 500);
}
}
/**
* Get product URLs from Magento 2
*/
public function getMagento2Urls(Request $request)
{
try {
$sku = $request->input('sku');
$limit = $request->input('limit', 100);
$offset = $request->input('offset', 0);
$query = DB::connection($this->magento2Connection)
->table($this->magento2Prefix . 'url_rewrite as ur')
->join($this->magento2Prefix . 'catalog_product_entity as p', function($join) {
$join->whereRaw("ur.entity_id = p.entity_id AND ur.entity_type = 'product'");
})
->select(
'p.entity_id',
'p.sku',
'ur.request_path',
'ur.target_path',
'ur.store_id',
'ur.entity_id as url_entity_id'
)
->where('ur.entity_type', 'product');
if ($sku) {
$query->where('p.sku', 'like', '%' . $sku . '%');
}
$total = $query->count();
$urls = $query->orderBy('p.entity_id')
->orderBy('ur.store_id')
->limit($limit)
->offset($offset)
->get();
return response()->json([
'success' => true,
'urls' => $urls,
'total' => $total,
'limit' => $limit,
'offset' => $offset
]);
} catch (\Exception $e) {
Log::error('Error fetching Magento 2 URLs: ' . $e->getMessage());
return response()->json([
'success' => false,
'message' => 'Failed to fetch Magento 2 URLs: ' . $e->getMessage(),
'urls' => [],
'total' => 0
], 500);
}
}
/**
* Compare product URLs between Magento 1 and Magento 2.
*
* A product can have multiple rewrites per store (canonical + one per category),
* so the comparison key is (sku, store_id, request_path). We restrict to catalog
* product URLs (target_path LIKE 'catalog/product/view/%') so this aligns with
* what the Fix tool migrates - non-catalog rewrites such as the productquestions
* module are deliberately excluded from migration and from comparison.
*/
public function compareUrls(Request $request)
{
try {
$sku = $request->input('sku');
$limit = (int) $request->input('limit', 100);
$offset = (int) $request->input('offset', 0);
$m1Query = DB::connection($this->magento1Connection)
->table($this->magento1Prefix . 'core_url_rewrite as ur')
->join($this->magento1Prefix . 'catalog_product_entity as p', function ($join) {
$join->whereRaw("(ur.id_path = CONCAT('product/', p.entity_id) OR ur.id_path LIKE CONCAT('product/', p.entity_id, '/%'))");
})
->select(
'p.entity_id as product_id',
'p.sku',
'ur.request_path',
'ur.store_id'
)
->where('ur.target_path', 'like', 'catalog/product/view/%');
if ($sku) {
$m1Query->where('p.sku', 'like', '%' . $sku . '%');
}
$m1Rows = $m1Query->get();
$m2Query = DB::connection($this->magento2Connection)
->table($this->magento2Prefix . 'url_rewrite as ur')
->join($this->magento2Prefix . 'catalog_product_entity as p', function ($join) {
$join->whereRaw("ur.entity_id = p.entity_id AND ur.entity_type = 'product'");
})
->select(
'p.entity_id as product_id',
'p.sku',
'ur.request_path',
'ur.store_id'
)
->where('ur.entity_type', 'product')
->where('ur.target_path', 'like', 'catalog/product/view/%');
if ($sku) {
$m2Query->where('p.sku', 'like', '%' . $sku . '%');
}
$m2Rows = $m2Query->get();
$key = fn($r) => $r->sku . '|' . $r->store_id . '|' . $r->request_path;
$m1Map = [];
foreach ($m1Rows as $r) { $m1Map[$key($r)] = $r; }
$m2Map = [];
foreach ($m2Rows as $r) { $m2Map[$key($r)] = $r; }
// sku -> product_id on each side (any row will do; same product per sku).
$m1Pid = [];
foreach ($m1Rows as $r) { $m1Pid[$r->sku] = $r->product_id; }
$m2Pid = [];
foreach ($m2Rows as $r) { $m2Pid[$r->sku] = $r->product_id; }
$allKeys = array_keys($m1Map + $m2Map);
sort($allKeys);
$comparison = [];
foreach ($allKeys as $k) {
$m1r = $m1Map[$k] ?? null;
$m2r = $m2Map[$k] ?? null;
$row = $m1r ?? $m2r;
if ($m1r && $m2r) { $status = 'match'; }
elseif ($m1r) { $status = 'missing_in_m2'; }
else { $status = 'missing_in_m1'; }
$comparison[] = [
'sku' => $row->sku,
'm1_product_id' => $m1Pid[$row->sku] ?? null,
'm2_product_id' => $m2Pid[$row->sku] ?? null,
'store_id' => $row->store_id,
'm1_url' => $m1r->request_path ?? null,
'm2_url' => $m2r->request_path ?? null,
'status' => $status,
];
}
$total = count($comparison);
$paginated = array_slice($comparison, $offset, $limit);
return response()->json([
'success' => true,
'comparison' => $paginated,
'total' => $total,
'limit' => $limit,
'offset' => $offset,
'summary' => [
'match' => count(array_filter($comparison, fn($c) => $c['status'] === 'match')),
'missing_in_m2' => count(array_filter($comparison, fn($c) => $c['status'] === 'missing_in_m2')),
'missing_in_m1' => count(array_filter($comparison, fn($c) => $c['status'] === 'missing_in_m1')),
'different' => 0,
],
]);
} catch (\Exception $e) {
Log::error('Error comparing URLs: ' . $e->getMessage());
return response()->json([
'success' => false,
'message' => 'Failed to compare URLs: ' . $e->getMessage(),
'comparison' => [],
'total' => 0
], 500);
}
}
/**
* Migrate / fix product URL rewrites from M1 -> M2.
*
* Reads M1 core_url_rewrite rows whose target_path is `catalog/product/view/...`
* (the canonical and category-bound product URLs) and inserts the corresponding
* row into M2 url_rewrite. Non-catalog rewrites such as the productquestions
* module are skipped, the M2 product is looked up by SKU so target_path uses
* the M2 entity_id, and category-bound URLs get metadata={"category_id":"X"}.
*
* Params:
* sku (optional) limit to a single product
* dry_run (optional) preview without inserting
*/
public function fixProductUrls(Request $request)
{
try {
$sku = $request->input('sku');
$dryRun = filter_var($request->input('dry_run', false), FILTER_VALIDATE_BOOLEAN);
$storeMapping = $this->getStoreMapping();
$query = DB::connection($this->magento1Connection)
->table($this->magento1Prefix . 'core_url_rewrite as ur')
->join($this->magento1Prefix . 'catalog_product_entity as p', function ($join) {
$join->whereRaw("(ur.id_path = CONCAT('product/', p.entity_id) OR ur.id_path LIKE CONCAT('product/', p.entity_id, '/%'))");
})
->select(
'p.entity_id as m1_product_id',
'p.sku',
'ur.url_rewrite_id',
'ur.store_id',
'ur.id_path',
'ur.request_path',
'ur.target_path',
'ur.is_system',
'ur.description'
)
->where('ur.target_path', 'like', 'catalog/product/view/%');
if (!empty($sku)) {
$query->where('p.sku', $sku);
}
$m1Rewrites = $query->orderBy('p.entity_id')->orderBy('ur.store_id')->get();
$added = 0;
$skippedExisting = 0;
$skippedNoProduct = 0;
$errors = 0;
$log = [];
$m2ProductCache = [];
foreach ($m1Rewrites as $r) {
try {
$m1StoreId = (int) ($r->store_id ?? 0);
$m2StoreId = $storeMapping[$m1StoreId] ?? $m1StoreId;
if (!isset($m2ProductCache[$r->sku])) {
$m2ProductCache[$r->sku] = DB::connection($this->magento2Connection)
->table($this->magento2Prefix . 'catalog_product_entity')
->where('sku', $r->sku)
->value('entity_id');
}
$m2ProductId = $m2ProductCache[$r->sku];
if (!$m2ProductId) {
$skippedNoProduct++;
$log[] = "SKIP no M2 product for sku '{$r->sku}' (M1 id={$r->m1_product_id}, request_path={$r->request_path})";
continue;
}
// M1 id_path is either `product/{id}` (canonical) or
// `product/{id}/{category_id}` (category-bound).
$idPathParts = explode('/', $r->id_path);
$categoryId = $idPathParts[2] ?? null;
$targetPath = 'catalog/product/view/id/' . $m2ProductId;
$metadata = null;
if (!empty($categoryId) && ctype_digit((string) $categoryId)) {
$targetPath .= '/category/' . $categoryId;
$metadata = json_encode(['category_id' => (string) $categoryId]);
}
// M2 unique key is (request_path, store_id), so check exactly that.
$exists = DB::connection($this->magento2Connection)
->table($this->magento2Prefix . 'url_rewrite')
->where('request_path', $r->request_path)
->where('store_id', $m2StoreId)
->exists();
if ($exists) {
$skippedExisting++;
continue;
}
if ($dryRun) {
$added++;
$log[] = "WOULD ADD [{$r->sku}] store={$m2StoreId} {$r->request_path} -> {$targetPath}";
continue;
}
DB::connection($this->magento2Connection)
->table($this->magento2Prefix . 'url_rewrite')
->insert([
'entity_type' => 'product',
'entity_id' => $m2ProductId,
'request_path' => $r->request_path,
'target_path' => $targetPath,
'redirect_type' => 0,
'store_id' => $m2StoreId,
'description' => $r->description,
'is_autogenerated' => ($r->is_system ?? 0) ? 1 : 0,
'metadata' => $metadata,
]);
$added++;
} catch (\Exception $e) {
$errors++;
$log[] = "ERROR [{$r->sku}] {$r->request_path}: " . $e->getMessage();
Log::error("fixProductUrls error for sku '{$r->sku}', request_path '{$r->request_path}': " . $e->getMessage());
}
}
return response()->json([
'success' => true,
'dry_run' => $dryRun,
'sku' => $sku,
'added' => $added,
'skipped_existing' => $skippedExisting,
'skipped_no_m2_product'=> $skippedNoProduct,
'errors' => $errors,
'total_m1_rewrites' => $m1Rewrites->count(),
'log' => array_slice($log, 0, 500),
]);
} catch (\Exception $e) {
Log::error('fixProductUrls fatal: ' . $e->getMessage());
return response()->json([
'success' => false,
'message' => 'Fix failed: ' . $e->getMessage(),
], 500);
}
}
/**
* Map M1 store_id -> M2 store_id by store code (case-insensitive, trimmed,
* tolerant of M2's "_N" suffix that DDEV/Magento sometimes appends), with
* fall-through to identity mapping when codes don't line up.
*/
protected function getStoreMapping()
{
$m1Stores = DB::connection($this->magento1Connection)
->table($this->magento1Prefix . 'core_store')
->where('store_id', '>', 0)
->get();
$m2Stores = DB::connection($this->magento2Connection)
->table($this->magento2Prefix . 'store')
->where('store_id', '>', 0)
->get();
$m2ByCode = [];
$m2ById = [];
foreach ($m2Stores as $s) {
$m2ById[$s->store_id] = $s;
$code = strtolower(trim($s->code ?? ''));
if ($code !== '') {
$m2ByCode[$code] = $s->store_id;
// M2 sometimes carries a `_N` suffix on the code; index the bare prefix too.
$bare = preg_replace('/_\d+$/', '', $code);
if ($bare !== $code && !isset($m2ByCode[$bare])) {
$m2ByCode[$bare] = $s->store_id;
}
}
}
$mapping = [];
foreach ($m1Stores as $m1) {
$code = strtolower(trim($m1->code ?? ''));
if ($code !== '' && isset($m2ByCode[$code])) {
$mapping[$m1->store_id] = $m2ByCode[$code];
} elseif (isset($m2ById[$m1->store_id])) {
$mapping[$m1->store_id] = $m1->store_id;
}
}
return $mapping;
}
/**
* Compare URLs for a single product SKU
*/
public function compareSingleSku(Request $request)
{
try {
$sku = $request->input('sku');
if (empty($sku)) {
return response()->json([
'success' => false,
'message' => 'SKU is required',
'comparison' => []
], 400);
}
// Get M1 catalog product URLs for this SKU
$m1Urls = DB::connection($this->magento1Connection)
->table($this->magento1Prefix . 'core_url_rewrite as ur')
->join($this->magento1Prefix . 'catalog_product_entity as p', function($join) {
$join->whereRaw("(ur.id_path = CONCAT('product/', p.entity_id) OR ur.id_path LIKE CONCAT('product/', p.entity_id, '/%'))");
})
->select(
'p.entity_id as product_id',
'p.sku',
'ur.request_path as m1_url',
'ur.target_path as m1_target_path',
'ur.store_id as m1_store_id',
'ur.id_path'
)
->where('ur.target_path', 'like', 'catalog/product/view/%')
->where('p.sku', $sku)
->get()
->groupBy('m1_store_id');
// Get M2 catalog product URLs for this SKU
$m2Urls = DB::connection($this->magento2Connection)
->table($this->magento2Prefix . 'url_rewrite as ur')
->join($this->magento2Prefix . 'catalog_product_entity as p', function($join) {
$join->whereRaw("ur.entity_id = p.entity_id AND ur.entity_type = 'product'");
})
->select(
'p.entity_id as product_id',
'p.sku',
'ur.request_path as m2_url',
'ur.target_path as m2_target_path',
'ur.store_id as m2_store_id'
)
->where('ur.entity_type', 'product')
->where('ur.target_path', 'like', 'catalog/product/view/%')
->where('p.sku', $sku)
->get()
->groupBy('m2_store_id');
// Get product info
$m1Product = DB::connection($this->magento1Connection)
->table($this->magento1Prefix . 'catalog_product_entity')
->where('sku', $sku)
->first();
$m2Product = DB::connection($this->magento2Connection)
->table($this->magento2Prefix . 'catalog_product_entity')
->where('sku', $sku)
->first();
// Compare URLs by store - return ALL URLs for each store
$comparison = [];
$allStores = array_unique(array_merge(
$m1Urls->keys()->toArray(),
$m2Urls->keys()->toArray()
));
foreach ($allStores as $storeId) {
$m1StoreUrls = $m1Urls->get($storeId, collect([]));
$m2StoreUrls = $m2Urls->get($storeId, collect([]));
// Get all M1 URLs for this store
$m1UrlsList = $m1StoreUrls->map(function($item) {
return [
'url' => $item->m1_url ?? null,
'target_path' => $item->m1_target_path ?? null,
'id_path' => $item->id_path ?? null
];
})->toArray();
// Get all M2 URLs for this store
$m2UrlsList = $m2StoreUrls->map(function($item) {
return [
'url' => $item->m2_url ?? null,
'target_path' => $item->m2_target_path ?? null
];
})->toArray();
// Per-store status uses set semantics (URL order is not stable, so a
// positional compare would falsely flag rearranged but identical sets
// as 'different'). 'different' here means both sides have URLs but
// the sets aren't equal - i.e. some are missing on one side.
$m1UrlStrings = array_filter(array_column($m1UrlsList, 'url'));
$m2UrlStrings = array_filter(array_column($m2UrlsList, 'url'));
$m1Set = array_unique($m1UrlStrings);
$m2Set = array_unique($m2UrlStrings);
if (empty($m1Set) && empty($m2Set)) {
$status = 'match';
} elseif (!empty($m1Set) && empty($m2Set)) {
$status = 'missing_in_m2';
} elseif (empty($m1Set) && !empty($m2Set)) {
$status = 'missing_in_m1';
} else {
sort($m1Set);
sort($m2Set);
$status = ($m1Set === $m2Set) ? 'match' : 'different';
}
$comparison[] = [
'store_id' => $storeId,
'm1_urls' => $m1UrlsList,
'm2_urls' => $m2UrlsList,
'status' => $status
];
}
// Calculate summary statistics
$summary = [
'match' => 0,
'missing_in_m2' => 0,
'missing_in_m1' => 0,
'different' => 0,
'total_stores' => count($comparison),
'total_m1_urls' => 0,
'total_m2_urls' => 0
];
foreach ($comparison as $store) {
$summary['total_m1_urls'] += count($store['m1_urls']);
$summary['total_m2_urls'] += count($store['m2_urls']);
switch ($store['status']) {
case 'match':
$summary['match']++;
break;
case 'missing_in_m2':
$summary['missing_in_m2']++;
break;
case 'missing_in_m1':
$summary['missing_in_m1']++;
break;
case 'different':
$summary['different']++;
break;
}
}
return response()->json([
'success' => true,
'sku' => $sku,
'm1_product_id' => $m1Product->entity_id ?? null,
'm2_product_id' => $m2Product->entity_id ?? null,
'comparison' => $comparison,
'summary' => $summary
]);
} catch (\Exception $e) {
Log::error('Error comparing single SKU URLs: ' . $e->getMessage());
return response()->json([
'success' => false,
'message' => 'Failed to compare URLs: ' . $e->getMessage(),
'comparison' => []
], 500);
}
}
}