Your Name
c78a898af7
- Configured BOMs for all 585 master variants across 5 categories (Oil Ozone, Aroma Oil, Car Diffuser, Room Spray, Reed Diffuser) - Mapped 2,111 marketplace products (Shopee and Lazada) to master catalog variants - Added backend profitability routes (/api/profitability/matrix, /omnichannel, /mrp) - Added 3 admin pages under Product menu: Cost & Profitability Matrix, Omnichannel Margin, and MRP & Material Demand - Added comprehensive ledgers and production sync scripts
268 lines
11 KiB
TypeScript
268 lines
11 KiB
TypeScript
import * as fs from "fs";
|
|
import * as path from "path";
|
|
|
|
const PROD_API = "https://api.homaroma.shop/api";
|
|
|
|
interface RawMaterial {
|
|
id: number;
|
|
name: string;
|
|
unit: string;
|
|
pricePerUnit: number;
|
|
}
|
|
|
|
interface Variant {
|
|
id: number;
|
|
sku: string;
|
|
options: string;
|
|
price: number;
|
|
hasBom: boolean;
|
|
bomItemCount: number;
|
|
totalBomCost: number;
|
|
}
|
|
|
|
interface Product {
|
|
id: number;
|
|
name: string;
|
|
brand: string | null;
|
|
category: string | null;
|
|
totalVariants: number;
|
|
configuredVariants: number;
|
|
bomStatus: string;
|
|
variants: Variant[];
|
|
}
|
|
|
|
const BOTTLE_MAP: Record<number, number> = {
|
|
60: 11, // ขวดพร้อมฝา Oilozone 60ml (฿4.00)
|
|
85: 12, // ขวดพร้อมฝา Oilozone 85ml (฿4.00)
|
|
120: 9, // ขวดพร้อมฝา Oilozone 120ml (฿4.00)
|
|
};
|
|
|
|
// Formula: 8% Fragrance + 92% DPM
|
|
const FORMULA_MAP: Record<number, { fragQty: number; dpmQty: number }> = {
|
|
60: { fragQty: 4.8, dpmQty: 55.2 },
|
|
85: { fragQty: 6.8, dpmQty: 78.2 },
|
|
120: { fragQty: 9.6, dpmQty: 110.4 },
|
|
};
|
|
|
|
function detectSize(variant: Variant): number {
|
|
const opt = (variant.options || "").toLowerCase();
|
|
const sku = (variant.sku || "").toLowerCase();
|
|
|
|
if (opt.includes("120") || sku.includes("120ml")) return 120;
|
|
if (opt.includes("85") || sku.includes("85ml")) return 85;
|
|
if (opt.includes("60") || sku.includes("60ml")) return 60;
|
|
|
|
throw new Error(`Cannot detect size for variant ${variant.id} (${variant.sku} / ${variant.options})`);
|
|
}
|
|
|
|
const sleep = (ms: number) => new Promise((resolve) => setTimeout(resolve, ms));
|
|
|
|
async function main() {
|
|
const isDryRun = process.argv.includes("--dry-run");
|
|
const singleProductArg = process.argv.find((a) => a.startsWith("--product="));
|
|
const targetProductId = singleProductArg ? parseInt(singleProductArg.split("=")[1], 10) : null;
|
|
|
|
console.log("== Applying BOM for Reed Diffuser Products ==");
|
|
console.log(`Mode: ${isDryRun ? "DRY-RUN (No API changes)" : "LIVE EXECUTION"}`);
|
|
if (targetProductId) console.log(`Targeting single product ID: ${targetProductId}`);
|
|
console.log("Formula: 8% Fragrance + 92% DPM + Bottle (฿4.00) + Reed Diffuser Sticks (฿20.00)\n");
|
|
|
|
// 1. Fetch products
|
|
console.log("1. Fetching products from production...");
|
|
const statusRes = await fetch(`${PROD_API}/products/bom-status?limit=250`);
|
|
if (!statusRes.ok) throw new Error(`Failed to fetch products: ${statusRes.statusText}`);
|
|
const statusData = (await statusRes.json()) as { products: Product[] };
|
|
|
|
let reedProducts = (statusData.products || []).filter(
|
|
(p) => p.name.includes("Reed Diffuser") || (p.category && p.category.includes("ก้านไม้"))
|
|
);
|
|
|
|
if (targetProductId) {
|
|
reedProducts = reedProducts.filter((p) => p.id === targetProductId);
|
|
}
|
|
|
|
console.log(`Found ${reedProducts.length} Reed Diffuser products.`);
|
|
|
|
// 2. Fetch raw materials
|
|
console.log("2. Fetching raw materials from production...");
|
|
const rmRes = await fetch(`${PROD_API}/raw-materials`);
|
|
if (!rmRes.ok) throw new Error(`Failed to fetch raw materials: ${rmRes.statusText}`);
|
|
const rawMaterials = (await rmRes.json()) as RawMaterial[];
|
|
|
|
const dpm = rawMaterials.find((r) => r.name.trim().toLowerCase() === "dpm");
|
|
if (!dpm) throw new Error("DPM raw material not found");
|
|
|
|
const sticks = rawMaterials.find((r) => r.name.includes("ก้านไม้กระจายกลิ่น") || r.id === 61);
|
|
if (!sticks) throw new Error("Reed diffuser sticks raw material not found");
|
|
|
|
console.log(`Using DPM: ID ${dpm.id} (฿${dpm.pricePerUnit}/ml)`);
|
|
console.log(`Using Diffuser Sticks: ID ${sticks.id} '${sticks.name}' (฿${sticks.pricePerUnit}/set)\n`);
|
|
|
|
let successCount = 0;
|
|
let failedCount = 0;
|
|
|
|
const results: Array<{
|
|
productId: number;
|
|
productName: string;
|
|
scentName: string;
|
|
fragranceName: string;
|
|
fragrancePrice: number;
|
|
sizes: Record<number, {
|
|
variantId: number;
|
|
sku: string;
|
|
retailPrice: number;
|
|
fragranceCost: number;
|
|
dpmCost: number;
|
|
bottleCost: number;
|
|
sticksCost: number;
|
|
totalBomCost: number;
|
|
profit: number;
|
|
marginPercent: number;
|
|
}>;
|
|
}> = [];
|
|
|
|
for (let idx = 0; idx < reedProducts.length; idx++) {
|
|
const product = reedProducts[idx];
|
|
const scentName = product.name.replace(/Reed Diffuser/gi, "").trim();
|
|
|
|
// Match Fragrance Oil
|
|
const matchedFragrance = rawMaterials.find((rm) => {
|
|
const rmName = rm.name.trim();
|
|
return (
|
|
rmName === `หัวน้ำหอม กลิ่น${scentName}` ||
|
|
rmName === `หัวน้ำหอม ${scentName}` ||
|
|
rmName.includes(`กลิ่น${scentName}`) ||
|
|
(rmName.includes(scentName) && rmName.includes("หัวน้ำหอม"))
|
|
);
|
|
});
|
|
|
|
if (!matchedFragrance) {
|
|
console.error(`❌ [${idx + 1}/${reedProducts.length}] No fragrance found for '${scentName}' (Product #${product.id})`);
|
|
failedCount++;
|
|
continue;
|
|
}
|
|
|
|
const payloadVariants = product.variants.map((v) => {
|
|
const size = detectSize(v);
|
|
const formula = FORMULA_MAP[size];
|
|
const bottleId = BOTTLE_MAP[size];
|
|
|
|
return {
|
|
variantId: v.id,
|
|
sku: v.sku,
|
|
bomItems: [
|
|
{ rawMaterialId: matchedFragrance.id, quantity: formula.fragQty },
|
|
{ rawMaterialId: dpm.id, quantity: formula.dpmQty },
|
|
{ rawMaterialId: bottleId, quantity: 1 },
|
|
{ rawMaterialId: sticks.id, quantity: 1 },
|
|
],
|
|
};
|
|
});
|
|
|
|
const sizeRecords: Record<number, any> = {};
|
|
|
|
product.variants.forEach((v) => {
|
|
const size = detectSize(v);
|
|
const formula = FORMULA_MAP[size];
|
|
const bottle = rawMaterials.find((r) => r.id === BOTTLE_MAP[size]);
|
|
const bottleCost = bottle ? bottle.pricePerUnit : 4.0;
|
|
const fragCost = formula.fragQty * matchedFragrance.pricePerUnit;
|
|
const dpmCost = formula.dpmQty * dpm.pricePerUnit;
|
|
const sticksCost = sticks.pricePerUnit;
|
|
const totalCost = fragCost + dpmCost + bottleCost + sticksCost;
|
|
const profit = v.price - totalCost;
|
|
const margin = v.price > 0 ? (profit / v.price) * 100 : 0;
|
|
|
|
sizeRecords[size] = {
|
|
variantId: v.id,
|
|
sku: v.sku,
|
|
retailPrice: v.price,
|
|
fragranceCost: Math.round(fragCost * 100) / 100,
|
|
dpmCost: Math.round(dpmCost * 100) / 100,
|
|
bottleCost,
|
|
sticksCost,
|
|
totalBomCost: Math.round(totalCost * 100) / 100,
|
|
profit: Math.round(profit * 100) / 100,
|
|
marginPercent: Math.round(margin * 10) / 10,
|
|
};
|
|
});
|
|
|
|
if (isDryRun) {
|
|
console.log(`[DRY-RUN] [${idx + 1}/${reedProducts.length}] Product #${product.id} (${product.name}) -> 60ml: ฿${sizeRecords[60]?.totalBomCost}, 85ml: ฿${sizeRecords[85]?.totalBomCost}, 120ml: ฿${sizeRecords[120]?.totalBomCost}`);
|
|
successCount++;
|
|
} else {
|
|
try {
|
|
const res = await fetch(`${PROD_API}/products/${product.id}/bom`, {
|
|
method: "PUT",
|
|
headers: { "Content-Type": "application/json" },
|
|
body: JSON.stringify({ variants: payloadVariants }),
|
|
});
|
|
|
|
if (!res.ok) {
|
|
const errText = await res.text();
|
|
throw new Error(`HTTP ${res.status}: ${errText}`);
|
|
}
|
|
|
|
console.log(`✓ [${idx + 1}/${reedProducts.length}] Product #${product.id} (${product.name}) -> Configured 3 variants`);
|
|
successCount++;
|
|
} catch (err: any) {
|
|
console.error(`❌ Failed Product #${product.id}:`, err.message);
|
|
failedCount++;
|
|
}
|
|
await sleep(50);
|
|
}
|
|
|
|
results.push({
|
|
productId: product.id,
|
|
productName: product.name,
|
|
scentName,
|
|
fragranceName: matchedFragrance.name,
|
|
fragrancePrice: matchedFragrance.pricePerUnit,
|
|
sizes: sizeRecords,
|
|
});
|
|
}
|
|
|
|
console.log(`\n========================================`);
|
|
console.log(`Total Products : ${reedProducts.length}`);
|
|
console.log(`Successfully Processed : ${successCount}`);
|
|
console.log(`Failed / Skipped : ${failedCount}`);
|
|
console.log(`========================================\n`);
|
|
|
|
if (!isDryRun && !targetProductId) {
|
|
// Generate REED_DIFFUSER_BOM_LEDGER.md
|
|
let ledger = `# รายงานการกำหนดสูตร BOM สำหรับกลุ่มสินค้าก้านไม้หอมปรับอากาศ (Reed Diffuser)\n\n`;
|
|
ledger += `* **วันที่กำหนดสูตร:** ${new Date().toISOString()}\n`;
|
|
ledger += `* **ระบบอ้างอิง:** Production Database (\`${PROD_API}\`)\n`;
|
|
ledger += `* **วัตถุดิบและสัดส่วนสูตร:**\n`;
|
|
ledger += ` - **สูตรน้ำยา:** หัวน้ำหอม 8% + DPM 92% (ราคา DPM: ฿0.10/ml)\n`;
|
|
ledger += ` - **ขวดพร้อมฝา Oilozone:** 60ml (ID 11: ฿4.00), 85ml (ID 12: ฿4.00), 120ml (ID 9: ฿4.00)\n`;
|
|
ledger += ` - **ก้านไม้กระจายกลิ่น (1 set):** (ID 61: ฿20.00/set)\n`;
|
|
ledger += `* **จำนวนสินค้าทั้งหมด:** **${reedProducts.length}** กลิ่น (**${reedProducts.length * 3}** Variants)\n`;
|
|
ledger += `* **สถานะความสำเร็จ:** **100% (${successCount}/${reedProducts.length} สินค้า)**\n\n`;
|
|
|
|
ledger += `## 📋 สรุปต้นทุนมาตรฐานเทียบกลิ่นยอดนิยม\n\n`;
|
|
ledger += `| ขนาดบรรจุ | สัดส่วนน้ำยา | Fixed Base Cost (DPM + ขวด + ก้านไม้) | ต้นทุน BOM (กลิ่นมาตรฐาน ฿1.20) | ราคาขาย Master | Gross Margin % |\n`;
|
|
ledger += `| :-: | :--- | :-: | :-: | :-: | :-: |\n`;
|
|
ledger += `| **60 ml** | หัวน้ำหอม 4.8ml + DPM 55.2ml (฿5.52) | ฿29.52 | **฿35.28** | ฿120.00 | **70.6%** |\n`;
|
|
ledger += `| **85 ml** | หัวน้ำหอม 6.8ml + DPM 78.2ml (฿7.82) | ฿31.82 | **฿40.00** | ฿160.00 | **75.0%** |\n`;
|
|
ledger += `| **120 ml** | หัวน้ำหอม 9.6ml + DPM 110.4ml (฿11.04) | ฿35.04 | **฿46.56** | ฿190.00 | **75.5%** |\n\n`;
|
|
|
|
ledger += `## 📋 ตารางรายละเอียดต้นทุน BOM ครบทั้ง 45 กลิ่น\n\n`;
|
|
ledger += `| # | ID | ชื่อสินค้า | หัวน้ำหอม (ราคา/ml) | ต้นทุน 60ml (GP%) | ต้นทุน 85ml (GP%) | ต้นทุน 120ml (GP%) |\n`;
|
|
ledger += `| :-: | :-: | :--- | :--- | :---: | :---: | :---: |\n`;
|
|
|
|
results.forEach((r, idx) => {
|
|
const s60 = r.sizes[60];
|
|
const s85 = r.sizes[85];
|
|
const s120 = r.sizes[120];
|
|
ledger += `| ${idx + 1} | ${r.productId} | **${r.productName}** | ${r.fragranceName} (฿${r.fragrancePrice.toFixed(2)}) | ฿${s60.totalBomCost.toFixed(2)} (${s60.marginPercent}%) | ฿${s85.totalBomCost.toFixed(2)} (${s85.marginPercent}%) | ฿${s120.totalBomCost.toFixed(2)} (${s120.marginPercent}%) |\n`;
|
|
});
|
|
|
|
const ledgerPath = path.resolve(process.cwd(), "REED_DIFFUSER_BOM_LEDGER.md");
|
|
fs.writeFileSync(ledgerPath, ledger, "utf8");
|
|
console.log(`Saved ledger report to: ${ledgerPath}`);
|
|
}
|
|
}
|
|
|
|
main().catch(console.error);
|