\s*<\/p>/', '', $html); $html = preg_replace('/

\s*<\/ul>/', '', $html); $html = preg_replace('/

\s* \s*<\/p>/', '', $html); $html = preg_replace('/

\s*<\/p>/', '', $html); $html = preg_replace('/<\/ul>\s*\s*

/', '

', $html); // Quita espacios antes y después de etiquetas $html = preg_replace('/>\s+<', $html); return trim($html); } function translateAndFormat($html, $tr) { if (!$html) { return ''; } $translated = $tr->translate($html); $translated = ajustarFormatoHTML($translated); return limpiarHTML($translated); } function shouldAddLineBreak($html) { // Verifica si el HTML termina en una etiqueta de cierre como o

return !preg_match('/<\/(ul|p)>$/', trim($html)); } /** * -------------------------------------------------------------------------------- * CONEXIÓN A BASE DE DATOS * -------------------------------------------------------------------------------- */ $conn = new mysqli($servername, $username, $password, $dbname); if ($conn->connect_error) { die("Error de conexión: " . $conn->connect_error); } $conn->set_charset("utf8"); /** * -------------------------------------------------------------------------------- * LÓGICA PRINCIPAL * -------------------------------------------------------------------------------- * - Se asume que el usuario llenó solamente la columna link en fc_fanatec_links. * - El script obtendrá el SKU de meta[itemprop=sku] y luego lo actualizará. * - Con el SKU, insertará o actualizará el resto de tablas. */ $sqlGetLinks = "SELECT id, sku, link FROM fc_fanatec_links"; $resultLinks = $conn->query($sqlGetLinks); if (!$resultLinks) { die("Error consultando fc_fanatec_links: " . $conn->error); } // Preparar Google Translate $tr = new GoogleTranslate(); $tr->setSource('en'); $tr->setTarget('es'); while ($row = $resultLinks->fetch_assoc()) { $linkId = $row['id']; $currentSKU = $row['sku']; $url = $row['link']; // 1) Intentar leer la página $html = @file_get_html($url); if (!$html) { // Error al leer HTML $sqlUpd = "UPDATE fc_fanatec_links SET status = 'Link no encontrado', last_update = NOW() WHERE id = ?"; $stmt = $conn->prepare($sqlUpd); $stmt->bind_param("i", $linkId); $stmt->execute(); $stmt->close(); echo "Link ID $linkId: No se pudo leer el HTML. Marcado como 'Link no encontrado'.
"; continue; } // 2) Si no tenemos SKU en la tabla, intentar obtenerlo del HTML // Fanatec usa if (empty($currentSKU)) { $skuElement = $html->find('meta[itemprop=sku]', 0); $skuFromPage = $skuElement ? trim($skuElement->content) : ''; if (empty($skuFromPage)) { // No pudimos encontrar SKU en el HTML $sqlUpd = "UPDATE fc_fanatec_links SET status = 'SKU no encontrado', last_update = NOW() WHERE id = ?"; $stmt = $conn->prepare($sqlUpd); $stmt->bind_param("i", $linkId); $stmt->execute(); $stmt->close(); echo "Link ID $linkId: No se encontró SKU en la página. Marcado como 'SKU no encontrado'.
"; continue; } else { // Actualizar la fila para guardar el SKU $currentSKU = $skuFromPage; $sqlUpdSKU = "UPDATE fc_fanatec_links SET sku = ?, status = 'Pendiente', last_update = NOW() WHERE id = ?"; $stmt = $conn->prepare($sqlUpdSKU); $stmt->bind_param("si", $currentSKU, $linkId); $stmt->execute(); $stmt->close(); echo "Link ID $linkId: SKU actualizado en fc_fanatec_links => $currentSKU
"; } } // Ahora $currentSKU debería contener el SKU // 3) Extraer datos que nos interesan (title, price, estado de envío, descripciones, imágenes, etc.) // Title //$titleElement = $html->find('meta[property=og:title]', 0)->content ?? ''; $title = $html->find('meta[property=og:title]', 0)->content ?? ''; // Price $priceElement = $html->find('meta[property=product:price]', 0); $priceRaw = $priceElement ? $priceElement->content : ''; $priceRaw = str_replace(',', '.', $priceRaw); $priceRaw = preg_replace('/[^0-9.]/', '', $priceRaw); $price = (is_numeric($priceRaw)) ? floatval($priceRaw) : 0.00; if ($price > 99999.99) { $price = 99999.99; } // Descripciones $metaDescription = $html->find('meta[name=description]', 0); $descIng = $metaDescription ? $metaDescription->content : ''; if ($descIng == '1') { $descIng = $title; } $secProps = $html->find('section.description-section--properties', 0); $specsIng = $secProps ? $secProps->innertext : ''; $secFeats = $html->find('section.description-section--features', 0); $caractIng = $secFeats ? $secFeats->innertext : ''; $secComp = $html->find('section.description-section--compatibility', 0); $compatIng = $secComp ? $secComp->innertext : ''; $secNotes = $html->find('section.description-section--specialnotes', 0); $notesIng = $secNotes ? $secNotes->innertext : ''; $secPack = $html->find('section.description-section--packaging', 0); $packIng = $secPack ? $secPack->innertext : ''; // Traducir y formatear $descripcion = translateAndFormat($descIng, $tr); $especificaciones = translateAndFormat($specsIng, $tr); $caracteristicas = translateAndFormat($caractIng, $tr); $compatibilidad = translateAndFormat($compatIng, $tr); $notas = translateAndFormat($notesIng, $tr); $packing = translateAndFormat($packIng, $tr); // Construir descripcion_larga si lo deseas $descripcion_larga = $descripcion; if (!empty(trim($caracteristicas))) { if (shouldAddLineBreak($descripcion_larga)) { $descripcion_larga .= '

'; } $descripcion_larga .= 'Ficha Técnica:
' . $caracteristicas; } if (!empty(trim($compatibilidad))) { if (shouldAddLineBreak($descripcion_larga)) { $descripcion_larga .= '
'; } $descripcion_larga .= $compatibilidad; } if (!empty(trim($notas))) { if (shouldAddLineBreak($descripcion_larga)) { $descripcion_larga .= '
'; } $descripcion_larga .= $notas; } if (!empty(trim($packing))) { if (shouldAddLineBreak($descripcion_larga)) { $descripcion_larga .= '
'; } $descripcion_larga .= $packing; } if (!empty(trim($especificaciones))) { if (shouldAddLineBreak($descripcion_larga)) { $descripcion_larga .= '
'; } $descripcion_larga .= $especificaciones; } $descripcion = limpiarHTML($descripcion); $descripcion_larga = limpiarHTML($descripcion_larga); // Estado de envío (shipping status) $deliveryInformation = $html->find('p.delivery--information span.delivery--text', 0); $text_status = 'Estado desconocido'; if ($deliveryInformation) { $statusText = trim($deliveryInformation->plaintext); if (stripos($statusText, 'Sold out') !== false) { $text_status = 'Sold out'; } elseif (stripos($statusText, 'Open for Pre-Orders') !== false) { $text_status = 'Preventa'; } elseif (stripos($statusText, 'Ready to ship') !== false) { $text_status = 'Listo para enviar'; } elseif (stripos($statusText, 'Ships within 4-7 days') !== false) { $text_status = 'Envío en 4-7 días'; } } // Imágenes $images = []; $sliderContainer = $html->find('.image-slider--container', 0); if ($sliderContainer) { $sliderItems = $sliderContainer->find('.image-slider--item'); foreach ($sliderItems as $idx => $item) { $largeImg = $item->find('.image--element', 0)->getAttribute('data-img-large'); $images[$idx] = $largeImg; } } $imagen1 = isset($images[0]) ? $images[0] : null; $imagen2 = isset($images[1]) ? $images[1] : null; $imagen3 = isset($images[2]) ? $images[2] : null; $imagen4 = isset($images[3]) ? $images[3] : null; $imagen5 = isset($images[4]) ? $images[4] : null; $imagen6 = isset($images[5]) ? $images[5] : null; $imagen7 = isset($images[6]) ? $images[6] : null; // -------------------------------------------------------------------------------- // A) fc_fanatec_shipping_status (si no existe, se crea) // -------------------------------------------------------------------------------- $shippingStatusId = null; if (!empty($text_status)) { $checkStatus = $conn->prepare("SELECT id FROM fc_fanatec_shipping_status WHERE status_name = ?"); $checkStatus->bind_param("s", $text_status); $checkStatus->execute(); $resStatus = $checkStatus->get_result(); if ($resStatus->num_rows > 0) { $shippingStatusId = $resStatus->fetch_assoc()['id']; } else { // Insertar $insStatus = $conn->prepare("INSERT INTO fc_fanatec_shipping_status (status_name) VALUES (?)"); $insStatus->bind_param("s", $text_status); $insStatus->execute(); $shippingStatusId = $insStatus->insert_id; $insStatus->close(); } $checkStatus->close(); } // -------------------------------------------------------------------------------- // B) fc_fanatec_descriptions (insertar/actualizar si actualizar=1) // -------------------------------------------------------------------------------- $checkDesc = $conn->prepare("SELECT id, actualizar FROM fc_fanatec_descriptions WHERE sku = ?"); $checkDesc->bind_param("s", $currentSKU); $checkDesc->execute(); $resDesc = $checkDesc->get_result(); if ($resDesc->num_rows > 0) { // Existe $rowDesc = $resDesc->fetch_assoc(); $descId = $rowDesc['id']; $actualizar = $rowDesc['actualizar']; if ($actualizar == 1) { // Update $sqlUpdateDesc = " UPDATE fc_fanatec_descriptions SET descripcion = ?, descripcion_larga = ?, especificaciones = ?, caracteristicas = ?, compatibilidad = ?, notas = ?, packing = ?, last_update = NOW() WHERE id = ? "; $stmtUpdDesc = $conn->prepare($sqlUpdateDesc); $stmtUpdDesc->bind_param( "sssssssi", $descripcion, $descripcion_larga, $especificaciones, $caracteristicas, $compatibilidad, $notas, $packing, $descId ); $stmtUpdDesc->execute(); $stmtUpdDesc->close(); } } else { // Insert $sqlInsDesc = " INSERT INTO fc_fanatec_descriptions ( sku, descripcion, descripcion_larga, especificaciones, caracteristicas, compatibilidad, notas, packing, actualizar, last_update ) VALUES (?,?,?,?,?,?,?,?,1,NOW()) "; $stmtInsDesc = $conn->prepare($sqlInsDesc); $stmtInsDesc->bind_param( "ssssssss", $currentSKU, $descripcion, $descripcion_larga, $especificaciones, $caracteristicas, $compatibilidad, $notas, $packing ); $stmtInsDesc->execute(); $stmtInsDesc->close(); } $checkDesc->close(); // -------------------------------------------------------------------------------- // C) fc_fanatec_prices // -------------------------------------------------------------------------------- $checkPrice = $conn->prepare("SELECT id FROM fc_fanatec_prices WHERE sku = ?"); $checkPrice->bind_param("s", $currentSKU); $checkPrice->execute(); $resPrice = $checkPrice->get_result(); if ($resPrice->num_rows > 0) { // Update $rowPrice = $resPrice->fetch_assoc(); $priceId = $rowPrice['id']; $sqlUpdatePrice = " UPDATE fc_fanatec_prices SET title = ?, price = ?, shipping_status_id = ?, link_producto = ?, date_updated = NOW() WHERE id = ? "; $stmtUpdPrice = $conn->prepare($sqlUpdatePrice); $stmtUpdPrice->bind_param("sdisi", $title, $price, $shippingStatusId, $url, $priceId); $stmtUpdPrice->execute(); $stmtUpdPrice->close(); } else { // Insert $sqlInsPrice = " INSERT INTO fc_fanatec_prices ( sku, title, price, shipping_status_id, link_producto, date_updated ) VALUES (?,?,?,?,?,NOW()) "; $stmtInsPrice = $conn->prepare($sqlInsPrice); $stmtInsPrice->bind_param("sdiss", $currentSKU, $title, $price, $shippingStatusId, $url); $stmtInsPrice->execute(); $stmtInsPrice->close(); } $checkPrice->close(); // -------------------------------------------------------------------------------- // D) fc_fanatec_images // -------------------------------------------------------------------------------- $checkImg = $conn->prepare("SELECT id FROM fc_fanatec_images WHERE sku = ?"); $checkImg->bind_param("s", $currentSKU); $checkImg->execute(); $resImg = $checkImg->get_result(); if ($resImg->num_rows > 0) { // Update $rowImg = $resImg->fetch_assoc(); $imgId = $rowImg['id']; $sqlUpdateImg = " UPDATE fc_fanatec_images SET image1 = ?, image2 = ?, image3 = ?, image4 = ?, image5 = ?, image6 = ?, image7 = ?, updated_at = NOW() WHERE id = ? "; $stmtUpdImg = $conn->prepare($sqlUpdateImg); $stmtUpdImg->bind_param( "sssssssi", $imagen1, $imagen2, $imagen3, $imagen4, $imagen5, $imagen6, $imagen7, $imgId ); $stmtUpdImg->execute(); $stmtUpdImg->close(); } else { // Insert $sqlInsImg = " INSERT INTO fc_fanatec_images ( sku, image1, image2, image3, image4, image5, image6, image7, updated_at ) VALUES (?,?,?,?,?,?,?,?,NOW()) "; $stmtInsImg = $conn->prepare($sqlInsImg); $stmtInsImg->bind_param( "ssssssss", $currentSKU, $imagen1, $imagen2, $imagen3, $imagen4, $imagen5, $imagen6, $imagen7 ); $stmtInsImg->execute(); $stmtInsImg->close(); } $checkImg->close(); // 4) Finalmente, marcamos el link como 'Procesado' $sqlUpLink = "UPDATE fc_fanatec_links SET status='Procesado', last_update=NOW() WHERE id=?"; $stmtUpLink = $conn->prepare($sqlUpLink); $stmtUpLink->bind_param("i", $linkId); $stmtUpLink->execute(); $stmtUpLink->close(); echo "Link ID $linkId (SKU=$currentSKU) => Procesado con éxito.
"; } $conn->close();