1068 lines
37 KiB
JavaScript
1068 lines
37 KiB
JavaScript
/**
|
||
* Formex.ch web crawler service
|
||
* Scrapes panel/material data and prices from the Formex catalog.
|
||
* Authenticates with a Formex account to access pricing info.
|
||
*/
|
||
import * as cheerio from 'cheerio';
|
||
import fs from 'fs';
|
||
import path from 'path';
|
||
import { fileURLToPath } from 'url';
|
||
import pool, { run } from '../db/index.js';
|
||
|
||
const __dirname = path.dirname(fileURLToPath(import.meta.url));
|
||
const BASE_URL = 'https://www.formex.ch';
|
||
const ASSORTIMENT_URL = `${BASE_URL}/fr/assortiment`;
|
||
const CRAWL_DELAY = parseInt(process.env.FORMEX_CRAWL_DELAY_MS || '500', 10);
|
||
|
||
const IMAGES_DIR = path.join(__dirname, '..', '..', 'client', 'public', 'materials');
|
||
|
||
if (!fs.existsSync(IMAGES_DIR)) {
|
||
fs.mkdirSync(IMAGES_DIR, { recursive: true });
|
||
}
|
||
|
||
function sleep(ms) {
|
||
return new Promise(resolve => setTimeout(resolve, ms));
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Session / cookie management
|
||
// ---------------------------------------------------------------------------
|
||
|
||
class FormexSession {
|
||
constructor() {
|
||
this.cookies = new Map();
|
||
this.loggedIn = false;
|
||
}
|
||
|
||
/** Merge Set-Cookie headers into the jar */
|
||
captureCookies(response) {
|
||
let rawCookies = [];
|
||
|
||
// Preferred: getSetCookie() returns individual Set-Cookie headers (Node 19.7+)
|
||
if (typeof response.headers.getSetCookie === 'function') {
|
||
rawCookies = response.headers.getSetCookie();
|
||
} else {
|
||
// Fallback: get the raw header string. Multiple Set-Cookie values are
|
||
// joined with ", " by the fetch API. Split carefully: cookies never
|
||
// start with a space after the comma, so split on ", " followed by a
|
||
// token and "=" to re-separate them.
|
||
const raw = response.headers.get('set-cookie');
|
||
if (raw) {
|
||
rawCookies = raw.split(/,\s*(?=[A-Za-z0-9_\-]+=)/);
|
||
}
|
||
}
|
||
|
||
for (const header of rawCookies) {
|
||
const [pair] = header.split(';');
|
||
const eqIdx = pair.indexOf('=');
|
||
if (eqIdx > 0) {
|
||
this.cookies.set(pair.slice(0, eqIdx).trim(), pair.slice(eqIdx + 1).trim());
|
||
}
|
||
}
|
||
}
|
||
|
||
cookieHeader() {
|
||
return [...this.cookies.entries()].map(([k, v]) => `${k}=${v}`).join('; ');
|
||
}
|
||
|
||
/** Common headers sent with every request */
|
||
headers(extra = {}) {
|
||
return {
|
||
'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36',
|
||
'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8',
|
||
'Accept-Language': 'fr-CH,fr;q=0.9,en;q=0.5',
|
||
...(this.cookies.size > 0 ? { 'Cookie': this.cookieHeader() } : {}),
|
||
...extra,
|
||
};
|
||
}
|
||
|
||
/**
|
||
* Log in to Formex.ch via the CoreShop login form.
|
||
*
|
||
* Form at /fr/shop/login POSTs to /fr/shop/login-check with fields:
|
||
* _username, _password, _remember_me (optional)
|
||
*/
|
||
async login() {
|
||
const email = process.env.FORMEX_EMAIL;
|
||
const password = process.env.FORMEX_PASSWORD;
|
||
if (!email || !password) {
|
||
console.warn('FORMEX_EMAIL / FORMEX_PASSWORD not set – running without auth (no prices)');
|
||
return false;
|
||
}
|
||
|
||
try {
|
||
const loginPageUrl = `${BASE_URL}/fr/shop/login`;
|
||
const loginCheckUrl = `${BASE_URL}/fr/shop/login-check`;
|
||
|
||
// 1. GET the login page to pick up the session cookie
|
||
const getRes = await fetch(loginPageUrl, {
|
||
headers: this.headers(),
|
||
redirect: 'manual',
|
||
});
|
||
this.captureCookies(getRes);
|
||
|
||
// Follow any initial redirect (e.g. to set session)
|
||
if ([301, 302, 303].includes(getRes.status)) {
|
||
const loc = getRes.headers.get('location');
|
||
if (loc) {
|
||
const r = await fetch(loc.startsWith('http') ? loc : `${BASE_URL}${loc}`, {
|
||
headers: this.headers(),
|
||
redirect: 'manual',
|
||
});
|
||
this.captureCookies(r);
|
||
}
|
||
}
|
||
|
||
// 2. POST credentials to /fr/shop/login-check
|
||
const formData = new URLSearchParams();
|
||
formData.set('_username', email);
|
||
formData.set('_password', password);
|
||
formData.set('_remember_me', '1');
|
||
|
||
const postRes = await fetch(loginCheckUrl, {
|
||
method: 'POST',
|
||
headers: this.headers({
|
||
'Content-Type': 'application/x-www-form-urlencoded',
|
||
'Referer': loginPageUrl,
|
||
'Origin': BASE_URL,
|
||
}),
|
||
body: formData.toString(),
|
||
redirect: 'manual',
|
||
});
|
||
this.captureCookies(postRes);
|
||
|
||
// 3. Follow the redirect chain after login (CoreShop typically redirects 302)
|
||
let location = postRes.headers.get('location') || '';
|
||
let hops = 0;
|
||
while (location && hops < 5) {
|
||
const redirectUrl = location.startsWith('http') ? location : `${BASE_URL}${location}`;
|
||
const redirectRes = await fetch(redirectUrl, {
|
||
headers: this.headers(),
|
||
redirect: 'manual',
|
||
});
|
||
this.captureCookies(redirectRes);
|
||
location = redirectRes.headers.get('location') || '';
|
||
hops++;
|
||
|
||
// If we're redirected back to the login page, credentials were wrong
|
||
if (redirectUrl.includes('/shop/login')) {
|
||
console.error('Formex login failed – redirected back to login page');
|
||
return false;
|
||
}
|
||
}
|
||
|
||
// 4. Verify by fetching the customer profile
|
||
const checkRes = await fetch(`${BASE_URL}/fr/shop/customer/profile`, {
|
||
headers: this.headers(),
|
||
redirect: 'manual',
|
||
});
|
||
this.captureCookies(checkRes);
|
||
|
||
const checkLocation = checkRes.headers.get('location') || '';
|
||
if (checkRes.status >= 300 && checkLocation.includes('login')) {
|
||
console.error('Formex login failed – profile redirected to login');
|
||
return false;
|
||
}
|
||
|
||
// Extra verification: fetch the profile page with redirect:follow and
|
||
// check if we end up on an actual profile (contains the user email) or
|
||
// a login page.
|
||
const profileRes = await fetch(`${BASE_URL}/fr/shop/customer/profile`, {
|
||
headers: this.headers(),
|
||
redirect: 'follow',
|
||
});
|
||
this.captureCookies(profileRes);
|
||
const profileHtml = await profileRes.text();
|
||
const profileUrl = profileRes.url || '';
|
||
|
||
if (profileUrl.includes('login') || profileHtml.includes('_username')) {
|
||
console.error('Formex login failed – profile page is actually login page');
|
||
console.error(` Cookies captured: ${this.cookies.size} (${[...this.cookies.keys()].join(', ')})`);
|
||
return false;
|
||
}
|
||
|
||
this.loggedIn = true;
|
||
console.log(`Formex login successful (${this.cookies.size} cookies: ${[...this.cookies.keys()].join(', ')})`);
|
||
return true;
|
||
} catch (err) {
|
||
console.error('Formex login error:', err.message);
|
||
return false;
|
||
}
|
||
}
|
||
}
|
||
|
||
/** Module-level session, re-created each sync run */
|
||
let session = new FormexSession();
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// HTTP helpers (use session cookies)
|
||
// ---------------------------------------------------------------------------
|
||
|
||
async function fetchPage(url) {
|
||
const response = await fetch(url, {
|
||
headers: session.headers(),
|
||
redirect: 'follow',
|
||
});
|
||
session.captureCookies(response);
|
||
|
||
if (!response.ok) {
|
||
throw new Error(`Failed to fetch ${url}: ${response.status}`);
|
||
}
|
||
return response.text();
|
||
}
|
||
|
||
async function downloadImage(url, filename) {
|
||
try {
|
||
if (!url) return null;
|
||
const fullUrl = url.startsWith('http') ? url : `${BASE_URL}${url}`;
|
||
|
||
const response = await fetch(fullUrl, {
|
||
headers: session.headers({ 'Accept': 'image/*' }),
|
||
});
|
||
session.captureCookies(response);
|
||
|
||
if (!response.ok) {
|
||
console.warn(`Failed to download image ${fullUrl}: ${response.status}`);
|
||
return null;
|
||
}
|
||
|
||
const contentType = response.headers.get('content-type') || '';
|
||
let ext = '.jpg';
|
||
if (contentType.includes('png')) ext = '.png';
|
||
else if (contentType.includes('webp')) ext = '.webp';
|
||
|
||
const finalFilename = filename.replace(/\.[^.]+$/, '') + ext;
|
||
const filepath = path.join(IMAGES_DIR, finalFilename);
|
||
|
||
const buffer = Buffer.from(await response.arrayBuffer());
|
||
fs.writeFileSync(filepath, buffer);
|
||
|
||
return `/materials/${finalFilename}`;
|
||
} catch (err) {
|
||
console.warn(`Error downloading image ${url}:`, err.message);
|
||
return null;
|
||
}
|
||
}
|
||
|
||
/**
|
||
* Extract collection URLs from the assortiment page
|
||
*/
|
||
const SHOP_UTILITY_PATHS = [
|
||
'/fr/shop/cart', '/fr/shop/login', '/fr/shop/register',
|
||
'/fr/shop/watchlist', '/fr/shop/customer', '/fr/shop/checkout',
|
||
'/fr/shop/search', '/fr/shop/special-inquiry',
|
||
];
|
||
|
||
async function getCollections() {
|
||
const html = await fetchPage(ASSORTIMENT_URL);
|
||
const $ = cheerio.load(html);
|
||
const collections = [];
|
||
const seenUrls = new Set();
|
||
|
||
$('a[href*="/fr/shop/"]').each((_, el) => {
|
||
const href = $(el).attr('href');
|
||
const name = $(el).text().trim();
|
||
if (!href || !name) return;
|
||
|
||
const isProduct = /-p\d+$/.test(href);
|
||
const isUtility = SHOP_UTILITY_PATHS.some(u => href.startsWith(u) || href.includes(u));
|
||
const isShopRoot = /\/fr\/shop\/?$/.test(href);
|
||
|
||
if (!isProduct && !isUtility && !isShopRoot && href.includes('/fr/shop/')) {
|
||
const fullUrl = href.startsWith('http') ? href : `${BASE_URL}${href}`;
|
||
if (!seenUrls.has(fullUrl)) {
|
||
seenUrls.add(fullUrl);
|
||
collections.push({ name, url: fullUrl });
|
||
}
|
||
}
|
||
});
|
||
|
||
return collections;
|
||
}
|
||
|
||
/**
|
||
* Convert a value+unit string to millimeters.
|
||
* Supports "mm", "m", "m1", "cm". Defaults to mm if no unit given.
|
||
*/
|
||
function toMm(value, unit) {
|
||
const v = parseFloat(value);
|
||
if (isNaN(v)) return null;
|
||
const u = (unit || 'mm').toLowerCase().replace(/\d/g, ''); // strip digits from units like "m1"
|
||
if (u === 'm') return v * 1000;
|
||
if (u === 'cm') return v * 10;
|
||
return v; // mm or unknown
|
||
}
|
||
|
||
/**
|
||
* Parse dimensions from strings like "4100mm x 1300mm x 0.8mm" or "100m x 43mm x 1mm".
|
||
* Handles mixed units (m, cm, mm) and converts everything to mm.
|
||
*/
|
||
function parseDimensions(dimStr) {
|
||
if (!dimStr) return { width: null, height: null, thickness: null };
|
||
|
||
const m = dimStr.match(/([\d.]+)\s*(m[m1]?|cm)\s*x\s*([\d.]+)\s*(m[m1]?|cm)\s*x\s*([\d.]+)\s*(m[m1]?|cm)/i);
|
||
if (m) {
|
||
return {
|
||
width: toMm(m[1], m[2]),
|
||
height: toMm(m[3], m[4]),
|
||
thickness: toMm(m[5], m[6])
|
||
};
|
||
}
|
||
return { width: null, height: null, thickness: null };
|
||
}
|
||
|
||
/**
|
||
* Extract the CHF price from text like "26.80 CHF", "67.30 CHF", or "CHF 1'234.50"
|
||
* Handles non-breaking spaces, smart quotes, thousand-separators, and varied whitespace.
|
||
*/
|
||
function parsePrice(text) {
|
||
if (!text) return null;
|
||
const clean = text
|
||
.replace(/[\u00a0\u202f\u2009\u200a\u00ad]/g, ' ')
|
||
.replace(/['\u2019\u2018\u2027]/g, '')
|
||
.replace(/\s+/g, ' ')
|
||
.trim();
|
||
|
||
// Pattern: "123.45 CHF", "CHF 123.45", "1234,50 CHF", "1'234.50 CHF"
|
||
const m = clean.match(/([\d]+(?:[.,][\d]+)?)\s*CHF|CHF\s*([\d]+(?:[.,][\d]+)?)/i);
|
||
if (m) {
|
||
const raw = (m[1] || m[2]).replace(',', '.');
|
||
return parseFloat(raw);
|
||
}
|
||
|
||
// Last resort: bare number if the text is very short (e.g. just "456.00")
|
||
if (clean.length < 20) {
|
||
const bare = clean.match(/^([\d]+(?:[.,][\d]+)?)$/);
|
||
if (bare) return parseFloat(bare[1].replace(',', '.'));
|
||
}
|
||
|
||
return null;
|
||
}
|
||
|
||
/**
|
||
* Extract decor code from product name or page content
|
||
* e.g., "Panneaux mélaminés Duplex Plus 10000 Tilia" -> "10000"
|
||
*/
|
||
function extractDecorCode(text) {
|
||
// Try numeric 4-5 digit code first (most common)
|
||
const numMatch = text.match(/\b(\d{4,5})\b/);
|
||
if (numMatch) return numMatch[1];
|
||
|
||
// Also try alphanumeric codes that appear in product names (e.g. "24MP")
|
||
const alphaMatch = text.match(/\b(\d{2,5}[A-Z]{1,4})\b/i);
|
||
return alphaMatch ? alphaMatch[1] : null;
|
||
}
|
||
|
||
/**
|
||
* Map of common color keywords found in Formex product names to representative hex colors
|
||
*/
|
||
const COLOR_KEYWORDS = {
|
||
// Whites and off-whites
|
||
'blanc': '#f5f5f5', 'white': '#f5f5f5', 'weiss': '#f5f5f5', 'bianco': '#f5f5f5',
|
||
'crème': '#fffdd0', 'cream': '#fffdd0', 'ivoire': '#fffff0', 'ivory': '#fffff0',
|
||
'magnolia': '#f8f4f0', 'perle': '#eae0c8',
|
||
// Greys
|
||
'gris': '#909090', 'grey': '#909090', 'gray': '#909090', 'grau': '#909090',
|
||
'anthracite': '#383838', 'anthrazit': '#383838', 'graphite': '#4a4a4a',
|
||
'platine': '#e5e4e2', 'argent': '#c0c0c0', 'silver': '#c0c0c0',
|
||
'titane': '#878681',
|
||
// Blacks
|
||
'noir': '#1a1a1a', 'black': '#1a1a1a', 'schwarz': '#1a1a1a',
|
||
// Browns and woods
|
||
'chêne': '#b5895b', 'oak': '#b5895b', 'eiche': '#b5895b',
|
||
'noyer': '#6f4e37', 'walnut': '#6f4e37', 'nussbaum': '#6f4e37',
|
||
'hêtre': '#c9a46c', 'beech': '#c9a46c', 'buche': '#c9a46c',
|
||
'érable': '#d4b572', 'maple': '#d4b572', 'ahorn': '#d4b572',
|
||
'cerisier': '#9c3b17', 'cherry': '#9c3b17', 'kirsch': '#9c3b17',
|
||
'teck': '#b08444', 'teak': '#b08444',
|
||
'pin': '#dcc8a0', 'pine': '#dcc8a0',
|
||
'orme': '#a07850', 'elm': '#a07850',
|
||
'bouleau': '#e0c99c', 'birch': '#e0c99c',
|
||
'frêne': '#c8b88a', 'ash': '#c8b88a',
|
||
'acacia': '#b89244',
|
||
'wenge': '#4a3728', 'wengé': '#4a3728',
|
||
'mélèze': '#c4a06c', 'larch': '#c4a06c',
|
||
'aulne': '#b07c4c', 'alder': '#b07c4c',
|
||
'brun': '#8b4513', 'brown': '#8b4513', 'braun': '#8b4513',
|
||
'marron': '#6d3e1c', 'chocolat': '#4a2c14',
|
||
'cognac': '#9f381d', 'caramel': '#a67040',
|
||
'noisette': '#ab7746',
|
||
// Beiges and sandstones
|
||
'beige': '#d9c8a9', 'sable': '#c2b280', 'sand': '#c2b280',
|
||
'sahara': '#c4a661', 'champagne': '#f7e7ce',
|
||
'vanille': '#f3e5ab', 'vanilla': '#f3e5ab',
|
||
'cappuccino': '#a0785a', 'moka': '#6f4e37',
|
||
// Reds
|
||
'rouge': '#cc3333', 'red': '#cc3333', 'rot': '#cc3333',
|
||
'bordeaux': '#722f37', 'burgundy': '#722f37',
|
||
'terracotta': '#c65d3e', 'rouille': '#b7410e',
|
||
// Blues
|
||
'bleu': '#4169e1', 'blue': '#4169e1', 'blau': '#4169e1',
|
||
'marine': '#000080', 'navy': '#000080',
|
||
'ciel': '#87ceeb', 'azur': '#007fff',
|
||
'cobalt': '#0047ab', 'indigo': '#4b0082',
|
||
// Greens
|
||
'vert': '#4caf50', 'green': '#4caf50', 'grün': '#4caf50',
|
||
'olive': '#808000', 'sauge': '#9caf88', 'sage': '#9caf88',
|
||
'emeraude': '#50c878', 'mousse': '#738276',
|
||
// Yellows and golds
|
||
'jaune': '#f0c040', 'yellow': '#f0c040',
|
||
'or': '#d4af37', 'gold': '#d4af37',
|
||
'miel': '#cd8032', 'honey': '#cd8032',
|
||
// Stone and concrete
|
||
'béton': '#a0a0a0', 'beton': '#a0a0a0', 'concrete': '#a0a0a0',
|
||
'pierre': '#b0a898', 'stone': '#b0a898', 'stein': '#b0a898',
|
||
'ardoise': '#5a5a5a', 'slate': '#5a5a5a',
|
||
'marbre': '#e0d8d0', 'marble': '#e0d8d0',
|
||
'granit': '#808080', 'granite': '#808080',
|
||
'travertin': '#d1c6b0',
|
||
// Others
|
||
'rouille': '#b7410e', 'rust': '#b7410e',
|
||
'cuivre': '#b87333', 'copper': '#b87333',
|
||
'linen': '#e0d5c0', 'lin': '#e0d5c0',
|
||
'taupe': '#8b7d6b',
|
||
'mauve': '#e0b0ff', 'lilas': '#c8a2c8',
|
||
'rose': '#ffc0cb', 'pink': '#ffc0cb', 'rosa': '#ffc0cb'
|
||
};
|
||
|
||
/**
|
||
* Extract dominant color from product name by matching color keywords
|
||
* @param {string} name - Product name
|
||
* @returns {string|null} - Hex color code or null
|
||
*/
|
||
function extractDominantColor(name) {
|
||
if (!name) return null;
|
||
const lower = name.toLowerCase().normalize('NFD').replace(/[\u0300-\u036f]/g, '');
|
||
const nameLower = name.toLowerCase();
|
||
|
||
// Try exact keyword matching (longest match first for compound words)
|
||
const sortedKeywords = Object.entries(COLOR_KEYWORDS)
|
||
.sort((a, b) => b[0].length - a[0].length);
|
||
|
||
for (const [keyword, hex] of sortedKeywords) {
|
||
// Check the raw name (preserving accents) and the normalized name
|
||
if (nameLower.includes(keyword) || lower.includes(keyword.normalize('NFD').replace(/[\u0300-\u036f]/g, ''))) {
|
||
return hex;
|
||
}
|
||
}
|
||
return null;
|
||
}
|
||
|
||
/**
|
||
* Get product details from a product detail page.
|
||
* Selectors are based on the actual Formex.ch Pimcore/CoreShop markup.
|
||
*/
|
||
async function getProductDetails(productUrl) {
|
||
try {
|
||
const html = await fetchPage(productUrl);
|
||
const $ = cheerio.load(html);
|
||
|
||
// --- Product name ---
|
||
const name = $('h1.product-detail__title').first().text().trim()
|
||
|| $('h1').first().text().trim();
|
||
|
||
// --- Decor code & article number from .product-detail__identifier-item ---
|
||
let decorCode = null;
|
||
let articleNumber = null;
|
||
$('.product-detail__identifier-item span').each((_, el) => {
|
||
const text = $(el).text().trim();
|
||
const decorMatch = text.match(/N°\s*du\s*d[ée]cor\s*:\s*(\w+)/i);
|
||
if (decorMatch) decorCode = decorMatch[1];
|
||
const artMatch = text.match(/No\.\s*art\.\s*:\s*(\w+)/i);
|
||
if (artMatch) articleNumber = artMatch[1];
|
||
});
|
||
if (!decorCode) decorCode = extractDecorCode(name);
|
||
|
||
// --- Price (multiple fallback strategies) ---
|
||
let priceCHF = null;
|
||
let priceUnit = null;
|
||
|
||
// Strategy 1: .price-new element (most common)
|
||
const priceNewEl = $('.price-new').first();
|
||
if (priceNewEl.length) {
|
||
priceCHF = parsePrice(priceNewEl.text());
|
||
}
|
||
|
||
// Strategy 2: search within .product-meta for any CHF amount
|
||
if (priceCHF === null) {
|
||
const metaText = $('.product-meta').first().text();
|
||
priceCHF = parsePrice(metaText);
|
||
}
|
||
|
||
// Strategy 3: look for h5 with font-weight-bold containing CHF
|
||
if (priceCHF === null) {
|
||
$('h5.font-weight-bold').each((_, el) => {
|
||
const t = $(el).text();
|
||
if (t.includes('CHF')) {
|
||
priceCHF = parsePrice(t);
|
||
if (priceCHF !== null) return false;
|
||
}
|
||
});
|
||
}
|
||
|
||
// Strategy 4: any element containing "CHF"
|
||
if (priceCHF === null) {
|
||
$('span, div, p, h5, h4, h3, strong').each((_, el) => {
|
||
const t = $(el).text().trim();
|
||
if (t.includes('CHF') && t.length < 60) {
|
||
const p = parsePrice(t);
|
||
if (p !== null && p > 0) {
|
||
priceCHF = p;
|
||
return false;
|
||
}
|
||
}
|
||
});
|
||
}
|
||
|
||
// Strategy 5: scan the full page text for any number+CHF pattern
|
||
if (priceCHF === null) {
|
||
const fullText = $.text().replace(/\s+/g, ' ');
|
||
const allPrices = [...fullText.matchAll(/([\d]+(?:[.,][\d]+)?)\s*(?:\u00a0\s*)?CHF/gi)];
|
||
if (allPrices.length > 0) {
|
||
priceCHF = parseFloat(allPrices[0][1].replace(',', '.'));
|
||
}
|
||
}
|
||
|
||
const priceHeadEl = $('.price-head').first();
|
||
if (priceHeadEl.length) {
|
||
priceUnit = priceHeadEl.text().trim();
|
||
}
|
||
|
||
// --- Stock ---
|
||
const stockFlag = $('.stock-flag').first().text().trim() || null;
|
||
const inStock = stockFlag ? stockFlag.toLowerCase().includes('stock') : null;
|
||
|
||
// --- Specifications from the sidebar table ---
|
||
const specs = {};
|
||
$('table.table-striped tbody tr').each((_, tr) => {
|
||
const key = $(tr).find('th').text().trim().toLowerCase();
|
||
const val = $(tr).find('td').text().trim();
|
||
if (key && val) specs[key] = val;
|
||
});
|
||
|
||
// --- Category from breadcrumbs or specs ---
|
||
let category = specs['catégorie de produits'] || specs['produktkategorie'] || null;
|
||
if (!category) {
|
||
const crumbs = $('.breadcrumbs a');
|
||
if (crumbs.length >= 2) {
|
||
category = $(crumbs.eq(crumbs.length - 1)).text().trim();
|
||
}
|
||
}
|
||
|
||
// --- Main product image (from the slider) ---
|
||
let imageUrl = null;
|
||
const mainPicSource = $('.product-images picture source').first();
|
||
if (mainPicSource.length) {
|
||
const srcset = mainPicSource.attr('srcset') || '';
|
||
const firstSrc = srcset.split(',')[0]?.split(' ')[0]?.trim();
|
||
if (firstSrc) {
|
||
imageUrl = firstSrc.startsWith('http') ? firstSrc : `${BASE_URL}${firstSrc}`;
|
||
}
|
||
}
|
||
if (!imageUrl) {
|
||
$('.product-images img').each((_, el) => {
|
||
const src = $(el).attr('src') || '';
|
||
if (src.includes('/dekorbilder/') || src.includes('/sortiment/')) {
|
||
imageUrl = src.startsWith('http') ? src : `${BASE_URL}${src}`;
|
||
return false;
|
||
}
|
||
});
|
||
}
|
||
|
||
// --- Dimension variants from .variant-type-AttributeSize ---
|
||
const dimensionVariants = [];
|
||
const seenDimensions = new Set();
|
||
const variantEntries = [];
|
||
$('.variant-type-AttributeSize a.btn, .variant-type-AttributeSize button.btn').each((_, el) => {
|
||
const text = $(el).text().trim();
|
||
const dim = parseDimensions(text);
|
||
if (dim.width !== null) {
|
||
const key = `${dim.width}-${dim.height}-${dim.thickness}`;
|
||
if (!seenDimensions.has(key)) {
|
||
seenDimensions.add(key);
|
||
const isActive = $(el).hasClass('active');
|
||
const href = $(el).attr('href');
|
||
variantEntries.push({
|
||
...dim,
|
||
price_chf: isActive ? priceCHF : null,
|
||
url: href ? (href.startsWith('http') ? href : `${BASE_URL}${href}`) : null,
|
||
isActive,
|
||
});
|
||
}
|
||
}
|
||
});
|
||
|
||
// Fetch individual prices for variants that don't have one yet
|
||
for (const variant of variantEntries) {
|
||
if (variant.price_chf === null && variant.url) {
|
||
try {
|
||
await sleep(CRAWL_DELAY);
|
||
const vHtml = await fetchPage(variant.url);
|
||
const $v = cheerio.load(vHtml);
|
||
const vpEl = $v('.price-new').first();
|
||
if (vpEl.length) variant.price_chf = parsePrice(vpEl.text());
|
||
if (variant.price_chf === null) {
|
||
$v('span, h5, div').each((_, el) => {
|
||
const t = $v(el).text().trim();
|
||
if (t.includes('CHF') && t.length < 60) {
|
||
const p = parsePrice(t);
|
||
if (p !== null && p > 0) { variant.price_chf = p; return false; }
|
||
}
|
||
});
|
||
}
|
||
if (!priceUnit) {
|
||
const vpHead = $v('.price-head').first();
|
||
if (vpHead.length) priceUnit = vpHead.text().trim();
|
||
}
|
||
} catch (e) {
|
||
console.warn(` [price] Failed fetching variant price: ${e.message}`);
|
||
}
|
||
}
|
||
dimensionVariants.push({
|
||
width: variant.width,
|
||
height: variant.height,
|
||
thickness: variant.thickness,
|
||
price_chf: variant.price_chf,
|
||
url: variant.url,
|
||
});
|
||
}
|
||
|
||
// If main price is still null, use the active variant's price (or the first variant with a price)
|
||
if (priceCHF === null && dimensionVariants.length > 0) {
|
||
const activeVar = dimensionVariants.find(v => variantEntries.find(e => e.url === v.url && e.isActive));
|
||
priceCHF = activeVar?.price_chf ?? dimensionVariants.find(v => v.price_chf != null)?.price_chf ?? null;
|
||
}
|
||
|
||
if (priceCHF === null) {
|
||
const bodyText = $.text().toLowerCase();
|
||
const isOnRequest = bodyText.includes('sur demande') || bodyText.includes('auf anfrage');
|
||
if (isOnRequest) {
|
||
console.log(` [price] Price on request at ${productUrl}`);
|
||
} else {
|
||
console.warn(` [price] No price found at ${productUrl}`);
|
||
}
|
||
}
|
||
|
||
// Fallback: parse specs table for dimensions if no variant buttons
|
||
let dimensions = dimensionVariants[0] || { width: null, height: null, thickness: null };
|
||
if (!dimensions.width && (specs['longueur'] || specs['largeur'])) {
|
||
const w = parseFloat(specs['longueur']?.replace(/[^\d.]/g, '')) || null;
|
||
const h = parseFloat(specs['largeur']?.replace(/[^\d.]/g, '')) || null;
|
||
const t = parseFloat((specs['épaisseur'] || specs['epaisseur'])?.replace(/[^\d.]/g, '')) || null;
|
||
dimensions = { width: w, height: h, thickness: t };
|
||
}
|
||
|
||
// --- Color / decor variants from .variant-type-collection-decor ---
|
||
const colorVariants = [];
|
||
const seenVariants = new Set();
|
||
const currentSlug = productUrl.split('/').pop();
|
||
$('.variant-type-collection-decor a[href*="/fr/shop/"]').each((_, el) => {
|
||
const href = $(el).attr('href') || '';
|
||
if (!href.match(/-p\d+$/)) return;
|
||
|
||
const slug = href.split('/').pop();
|
||
if (slug === currentSlug) return;
|
||
|
||
const variantUrl = href.startsWith('http') ? href : `${BASE_URL}${href}`;
|
||
const caption = $(el).find('figcaption').text().trim();
|
||
const variantCode = extractDecorCode(href) || extractDecorCode(caption);
|
||
if (!variantCode || seenVariants.has(variantCode)) return;
|
||
seenVariants.add(variantCode);
|
||
|
||
let variantImage = null;
|
||
const img = $(el).find('img').first();
|
||
if (img.length) {
|
||
variantImage = img.attr('src') || img.attr('data-src');
|
||
if (variantImage && !variantImage.startsWith('http')) {
|
||
variantImage = `${BASE_URL}${variantImage}`;
|
||
}
|
||
}
|
||
|
||
colorVariants.push({
|
||
code: variantCode,
|
||
name: caption || variantCode,
|
||
url: variantUrl,
|
||
imageUrl: variantImage,
|
||
});
|
||
});
|
||
|
||
// --- Download images locally ---
|
||
let localImageUrl = null;
|
||
if (imageUrl && decorCode) {
|
||
localImageUrl = await downloadImage(imageUrl, `${decorCode}.jpg`);
|
||
}
|
||
for (const variant of colorVariants) {
|
||
if (variant.imageUrl && variant.code) {
|
||
const localPath = await downloadImage(variant.imageUrl, `${variant.code}_thumb.jpg`);
|
||
if (localPath) variant.imageUrl = localPath;
|
||
}
|
||
}
|
||
|
||
const dominantColor = extractDominantColor(name);
|
||
|
||
return {
|
||
name,
|
||
decorCode,
|
||
articleNumber,
|
||
dimensions,
|
||
dimensionVariants,
|
||
colorVariants,
|
||
category,
|
||
imageUrl: localImageUrl || imageUrl,
|
||
dominantColor,
|
||
priceCHF,
|
||
priceUnit,
|
||
inStock,
|
||
};
|
||
} catch (err) {
|
||
console.error(`Error fetching product details from ${productUrl}:`, err.message);
|
||
return null;
|
||
}
|
||
}
|
||
|
||
/**
|
||
* Check if a material was already synced today with all critical data (name, price, image).
|
||
* Returns true if the product can be skipped.
|
||
*/
|
||
async function isAlreadySyncedToday(formexUrl) {
|
||
try {
|
||
const { rows } = await pool.query(
|
||
`SELECT id, name, price_chf, image_url, last_synced_at
|
||
FROM formex_materials WHERE formex_url = ?`,
|
||
[formexUrl]
|
||
);
|
||
if (rows.length === 0) return false;
|
||
|
||
const mat = rows[0];
|
||
if (!mat.last_synced_at) return false;
|
||
|
||
const syncedDate = mat.last_synced_at.slice(0, 10); // "YYYY-MM-DD"
|
||
const today = new Date().toISOString().slice(0, 10);
|
||
if (syncedDate !== today) return false;
|
||
|
||
// Synced today — skip only if critical fields are present
|
||
return !!(mat.name && mat.price_chf != null && mat.image_url);
|
||
} catch {
|
||
return false;
|
||
}
|
||
}
|
||
|
||
/**
|
||
* Get products from a collection page
|
||
* @param {string} collectionUrl
|
||
* @param {string} collectionName
|
||
* @param {Function} emit - Optional emitter for progress events
|
||
*/
|
||
async function getProductsFromCollection(collectionUrl, collectionName, emit = () => {}, force = false) {
|
||
const products = [];
|
||
const productLinks = new Set();
|
||
const visitedPages = new Set();
|
||
|
||
// Crawl all paginated pages of this collection
|
||
let pageUrl = collectionUrl;
|
||
while (pageUrl && !visitedPages.has(pageUrl)) {
|
||
visitedPages.add(pageUrl);
|
||
const html = await fetchPage(pageUrl);
|
||
const $ = cheerio.load(html);
|
||
|
||
$('a[href*="/fr/shop/"]').each((_, el) => {
|
||
const href = $(el).attr('href');
|
||
if (href && /-p\d+$/.test(href)) {
|
||
const fullUrl = href.startsWith('http') ? href : `${BASE_URL}${href}`;
|
||
productLinks.add(fullUrl);
|
||
}
|
||
});
|
||
|
||
// Look for a "next page" link in pagination
|
||
let nextPage = null;
|
||
$('a[rel="next"], .pagination a.next, .pagination li.next a, a.page-link[aria-label="Next"]').each((_, el) => {
|
||
const href = $(el).attr('href');
|
||
if (href) {
|
||
nextPage = href.startsWith('http') ? href : `${BASE_URL}${href}`;
|
||
}
|
||
});
|
||
if (!nextPage) {
|
||
// Fallback: look for numbered pagination links with ?page= or /page/
|
||
$('a[href*="page="], a[href*="/page/"]').each((_, el) => {
|
||
const href = $(el).attr('href');
|
||
if (href) {
|
||
const full = href.startsWith('http') ? href : `${BASE_URL}${href}`;
|
||
if (!visitedPages.has(full)) {
|
||
nextPage = nextPage || full;
|
||
}
|
||
}
|
||
});
|
||
}
|
||
|
||
if (nextPage && !visitedPages.has(nextPage)) {
|
||
await sleep(CRAWL_DELAY);
|
||
pageUrl = nextPage;
|
||
} else {
|
||
pageUrl = null;
|
||
}
|
||
}
|
||
|
||
const productCount = productLinks.size;
|
||
console.log(` Found ${productCount} products in ${collectionName}`);
|
||
emit({ type: 'log', message: ` ${productCount} produits trouvés dans ${collectionName}` });
|
||
|
||
let productIndex = 0;
|
||
let skippedCount = 0;
|
||
for (const productUrl of productLinks) {
|
||
productIndex++;
|
||
|
||
// Skip products already fully synced today (unless force override)
|
||
const canSkip = !force && await isAlreadySyncedToday(productUrl);
|
||
if (canSkip) {
|
||
skippedCount++;
|
||
emit({ type: 'log', message: ` [${productIndex}/${productCount}] (déjà à jour, ignoré)` });
|
||
continue;
|
||
}
|
||
|
||
await sleep(CRAWL_DELAY);
|
||
const details = await getProductDetails(productUrl);
|
||
|
||
if (details && details.decorCode) {
|
||
const priceLabel = details.priceCHF != null
|
||
? ` ${details.priceCHF} CHF`
|
||
: ' (pas de prix)';
|
||
products.push({
|
||
formex_code: details.decorCode,
|
||
article_number: details.articleNumber,
|
||
name: details.name,
|
||
collection: collectionName,
|
||
category: details.category || collectionName,
|
||
width_mm: details.dimensions.width,
|
||
height_mm: details.dimensions.height,
|
||
thickness_mm: details.dimensions.thickness,
|
||
price_chf: details.priceCHF,
|
||
price_unit: details.priceUnit,
|
||
image_url: details.imageUrl,
|
||
formex_url: productUrl,
|
||
dominant_color: details.dominantColor,
|
||
in_stock: details.inStock,
|
||
dimensionVariants: details.dimensionVariants || [],
|
||
colorVariants: details.colorVariants || []
|
||
});
|
||
const dims = details.dimensionVariants || [];
|
||
const varCount = details.colorVariants?.length || 0;
|
||
const dimPrices = dims.map(d => {
|
||
const size = `${d.width}x${d.height}x${d.thickness}`;
|
||
return d.price_chf != null ? `${size}=${d.price_chf} CHF` : `${size}=?`;
|
||
}).join(', ');
|
||
const priceInfo = dims.length > 0 ? ` [${dimPrices}]` : priceLabel;
|
||
emit({ type: 'log', message: ` [${productIndex}/${productCount}] ${details.name}${priceInfo} (${varCount} couleurs)` });
|
||
}
|
||
}
|
||
|
||
if (skippedCount > 0) {
|
||
emit({ type: 'log', message: ` ${skippedCount}/${productCount} produits ignorés (déjà à jour)` });
|
||
}
|
||
|
||
return products;
|
||
}
|
||
|
||
/**
|
||
* Upsert a material into the database with prices and enhanced data
|
||
*/
|
||
async function upsertMaterial(material) {
|
||
const { rows } = await pool.query(
|
||
'SELECT id FROM formex_materials WHERE formex_code = ?',
|
||
[material.formex_code]
|
||
);
|
||
|
||
let materialId;
|
||
|
||
if (rows.length > 0) {
|
||
materialId = rows[0].id;
|
||
await run(
|
||
`UPDATE formex_materials
|
||
SET name = ?, collection = ?, category = ?, width_mm = ?, height_mm = ?, thickness_mm = ?,
|
||
price_chf = COALESCE(?, price_chf),
|
||
price_unit = COALESCE(?, price_unit),
|
||
image_url = ?, formex_url = ?,
|
||
dominant_color = COALESCE(?, dominant_color),
|
||
last_synced_at = datetime('now')
|
||
WHERE formex_code = ?`,
|
||
[material.name, material.collection, material.category || null,
|
||
material.width_mm, material.height_mm, material.thickness_mm,
|
||
material.price_chf ?? null,
|
||
material.price_unit || null,
|
||
material.image_url || null, material.formex_url,
|
||
material.dominant_color || null, material.formex_code]
|
||
);
|
||
} else {
|
||
await run(
|
||
`INSERT INTO formex_materials
|
||
(formex_code, name, collection, category, width_mm, height_mm, thickness_mm, price_chf, price_unit, image_url, formex_url, dominant_color)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
|
||
[material.formex_code, material.name, material.collection, material.category || null,
|
||
material.width_mm, material.height_mm, material.thickness_mm,
|
||
material.price_chf ?? null,
|
||
material.price_unit || null,
|
||
material.image_url || null, material.formex_url, material.dominant_color || null]
|
||
);
|
||
|
||
const inserted = await pool.query(
|
||
'SELECT id FROM formex_materials WHERE formex_code = ?',
|
||
[material.formex_code]
|
||
);
|
||
materialId = inserted.rows[0]?.id;
|
||
}
|
||
|
||
// Dimension variants (with per-dimension price)
|
||
if (materialId && material.dimensionVariants && material.dimensionVariants.length > 0) {
|
||
await run('DELETE FROM formex_dimensions WHERE material_id = ?', [materialId]);
|
||
|
||
for (let i = 0; i < material.dimensionVariants.length; i++) {
|
||
const dim = material.dimensionVariants[i];
|
||
await run(
|
||
`INSERT INTO formex_dimensions (material_id, width_mm, height_mm, thickness_mm, price_chf, is_default)
|
||
VALUES (?, ?, ?, ?, ?, ?)`,
|
||
[materialId, dim.width, dim.height, dim.thickness, dim.price_chf ?? null, i === 0 ? 1 : 0]
|
||
);
|
||
}
|
||
}
|
||
|
||
// Color variants
|
||
if (materialId && material.colorVariants && material.colorVariants.length > 0) {
|
||
await run('DELETE FROM formex_variants WHERE material_id = ?', [materialId]);
|
||
|
||
for (const variant of material.colorVariants) {
|
||
await run(
|
||
`INSERT INTO formex_variants (material_id, variant_code, variant_name, variant_url, image_url)
|
||
VALUES (?, ?, ?, ?, ?)`,
|
||
[materialId, variant.code, variant.name, variant.url || null, variant.imageUrl || null]
|
||
);
|
||
}
|
||
}
|
||
|
||
return materialId;
|
||
}
|
||
|
||
/**
|
||
* Log sync result to database
|
||
*/
|
||
async function logSync(source, status, itemsCount, error = null) {
|
||
await run(
|
||
`INSERT INTO sync_logs (source, status, items_count, error) VALUES (?, ?, ?, ?)`,
|
||
[source, status, itemsCount, error]
|
||
);
|
||
}
|
||
|
||
/**
|
||
* Main sync function - crawls Formex and updates database
|
||
* @param {Function} emit - Optional emitter callback for SSE progress events
|
||
* Called with { type: 'log'|'progress'|'complete'|'error', ... }
|
||
* @param {Object} options
|
||
* @param {boolean} options.force - Skip the "already synced today" check
|
||
*/
|
||
export async function sync(emit = () => {}, { force = false } = {}) {
|
||
console.log(`Starting Formex sync...${force ? ' (FORCE)' : ''}`);
|
||
emit({ type: 'log', message: 'Démarrage de la synchronisation Formex...' });
|
||
if (force) {
|
||
emit({ type: 'log', message: 'Mode forcé – tous les produits seront resynchronisés' });
|
||
}
|
||
|
||
const startTime = Date.now();
|
||
let totalProducts = 0;
|
||
|
||
// Fresh session for each sync run
|
||
session = new FormexSession();
|
||
|
||
try {
|
||
// Authenticate to get access to prices
|
||
emit({ type: 'log', message: 'Connexion au compte Formex...' });
|
||
const loggedIn = await session.login();
|
||
if (loggedIn) {
|
||
emit({ type: 'log', message: 'Connecté – les prix seront récupérés' });
|
||
} else {
|
||
emit({ type: 'log', message: 'Non connecté – synchronisation sans prix' });
|
||
}
|
||
|
||
console.log('Fetching collections...');
|
||
emit({ type: 'log', message: 'Récupération des collections...' });
|
||
const collections = await getCollections();
|
||
const totalCollections = collections.length;
|
||
console.log(`Found ${totalCollections} collections`);
|
||
emit({ type: 'log', message: `${totalCollections} collections trouvées` });
|
||
emit({ type: 'progress', current: 0, total: totalCollections, collection: '' });
|
||
|
||
// Process each collection
|
||
for (let i = 0; i < collections.length; i++) {
|
||
const collection = collections[i];
|
||
console.log(`Processing: ${collection.name}`);
|
||
emit({ type: 'log', message: `Traitement: ${collection.name}` });
|
||
emit({ type: 'progress', current: i, total: totalCollections, collection: collection.name });
|
||
|
||
await sleep(CRAWL_DELAY);
|
||
|
||
try {
|
||
const products = await getProductsFromCollection(collection.url, collection.name, emit, force);
|
||
|
||
// Upsert each product
|
||
for (const product of products) {
|
||
await upsertMaterial(product);
|
||
totalProducts++;
|
||
}
|
||
|
||
emit({ type: 'log', message: ` ${products.length} produits enregistrés pour ${collection.name}` });
|
||
} catch (err) {
|
||
console.error(` Error processing ${collection.name}:`, err.message);
|
||
emit({ type: 'log', message: ` Erreur: ${err.message}` });
|
||
}
|
||
}
|
||
|
||
const elapsed = ((Date.now() - startTime) / 1000).toFixed(1);
|
||
console.log(`Sync complete: ${totalProducts} products in ${elapsed}s`);
|
||
|
||
await logSync('formex', 'success', totalProducts);
|
||
|
||
const result = {
|
||
success: true,
|
||
itemsCount: totalProducts,
|
||
duration: elapsed
|
||
};
|
||
|
||
emit({ type: 'progress', current: totalCollections, total: totalCollections, collection: '' });
|
||
emit({ type: 'complete', result });
|
||
|
||
return result;
|
||
|
||
} catch (err) {
|
||
console.error('Sync failed:', err.message);
|
||
await logSync('formex', 'error', totalProducts, err.message);
|
||
|
||
const result = {
|
||
success: false,
|
||
error: err.message,
|
||
itemsCount: totalProducts
|
||
};
|
||
|
||
emit({ type: 'error', error: err.message });
|
||
|
||
return result;
|
||
}
|
||
}
|
||
|
||
/**
|
||
* Get sync logs from database
|
||
*/
|
||
export async function getSyncLogs(limit = 20) {
|
||
const { rows } = await pool.query(
|
||
'SELECT * FROM sync_logs ORDER BY created_at DESC LIMIT ?',
|
||
[limit]
|
||
);
|
||
return rows;
|
||
}
|
||
|
||
/**
|
||
* Get all scraped materials
|
||
*/
|
||
export async function getMaterials() {
|
||
const { rows } = await pool.query(
|
||
'SELECT * FROM formex_materials ORDER BY collection, name'
|
||
);
|
||
return rows;
|
||
}
|