182 lines
9.9 KiB
JavaScript
182 lines
9.9 KiB
JavaScript
import fs from "node:fs/promises";
|
|
import path from "node:path";
|
|
import { SpreadsheetFile, Workbook } from "@oai/artifact-tool";
|
|
|
|
const workspace = "C:/Users/erik1/Documents/IPSExtractor";
|
|
const visibleSample = JSON.parse(await fs.readFile(path.join(workspace, ".codex_tmp_apartments", "page_0.json"), "utf8"));
|
|
const columns = Object.keys(visibleSample[0]);
|
|
const outputDir = path.join(workspace, "outputs", "apartamentos_no_visibles_rentpana_20260830");
|
|
const outputPath = path.join(outputDir, "apartamentos_no_visibles_rentpana.xlsx");
|
|
|
|
const colLetter = (oneBased) => {
|
|
let n = oneBased;
|
|
let result = "";
|
|
while (n > 0) {
|
|
const remainder = (n - 1) % 26;
|
|
result = String.fromCharCode(65 + remainder) + result;
|
|
n = Math.floor((n - 1) / 26);
|
|
}
|
|
return result;
|
|
};
|
|
|
|
const workbook = Workbook.create();
|
|
const summary = workbook.worksheets.add("Resumen");
|
|
const dataSheet = workbook.worksheets.add("Apartamentos");
|
|
const photosSheet = workbook.worksheets.add("Fotos");
|
|
const paginationSheet = workbook.worksheets.add("Paginacion");
|
|
|
|
const navy = "#123047";
|
|
const teal = "#0F766E";
|
|
const cyan = "#DFF4F1";
|
|
const pale = "#F4F7F9";
|
|
const white = "#FFFFFF";
|
|
const ink = "#1F2937";
|
|
const muted = "#60707D";
|
|
const border = "#D8E1E7";
|
|
|
|
for (const sheet of [summary, dataSheet, photosSheet, paginationSheet]) sheet.showGridLines = false;
|
|
|
|
const endCol = colLetter(columns.length);
|
|
dataSheet.getRange(`A1:${endCol}2`).values = [columns, columns.map(() => null)];
|
|
dataSheet.freezePanes.freezeRows(1);
|
|
dataSheet.freezePanes.freezeColumns(2);
|
|
const apartmentTable = dataSheet.tables.add(`A1:${endCol}2`, true, "HiddenApartmentsTable");
|
|
apartmentTable.style = "TableStyleMedium2";
|
|
apartmentTable.showFilterButton = true;
|
|
dataSheet.getRange(`A1:${endCol}1`).format = { fill: navy, font: { bold: true, color: white }, rowHeight: 28 };
|
|
dataSheet.getRange(`A2:${endCol}2`).format = { fill: white, rowHeight: 24 };
|
|
|
|
const widths = {
|
|
id: 36, user_id: 36, user_name: 22, user_phone: 16, address: 34, description: 60,
|
|
bedrooms: 12, bathrooms: 12, area_m2: 12, monthly_rent: 16, available_rooms: 16,
|
|
building_amenities: 38, apartment_features: 44, included_services: 36, photo_urls: 65,
|
|
created_at: 14, updated_at: 14, show_phone: 12, show_profile: 13, is_visible: 12,
|
|
priority: 11, listing_type: 14, city: 17, area: 24, short_id: 13,
|
|
smart_rental_enabled: 20, parent_apartment_id: 36, tenant_user_id: 36,
|
|
tenant_name: 20, tenant_phone: 16, tenant_gender: 15, tenant_age: 12,
|
|
is_paid: 11, seo_slug: 38,
|
|
};
|
|
columns.forEach((key, index) => dataSheet.getRange(`${colLetter(index + 1)}:${colLetter(index + 1)}`).format.columnWidth = widths[key] ?? 16);
|
|
|
|
const photoHeaders = ["apartment_id", "short_id", "city", "area", "address", "photo_index", "photo_url"];
|
|
photosSheet.getRange("A1:G2").values = [photoHeaders, photoHeaders.map(() => null)];
|
|
photosSheet.freezePanes.freezeRows(1);
|
|
photosSheet.freezePanes.freezeColumns(2);
|
|
const photosTable = photosSheet.tables.add("A1:G2", true, "HiddenPhotosTable");
|
|
photosTable.style = "TableStyleMedium2";
|
|
photosTable.showFilterButton = true;
|
|
photosSheet.getRange("A1:G1").format = { fill: navy, font: { bold: true, color: white }, rowHeight: 28 };
|
|
photosSheet.getRange("A:A").format.columnWidth = 36;
|
|
photosSheet.getRange("B:B").format.columnWidth = 13;
|
|
photosSheet.getRange("C:C").format.columnWidth = 16;
|
|
photosSheet.getRange("D:D").format.columnWidth = 24;
|
|
photosSheet.getRange("E:E").format.columnWidth = 36;
|
|
photosSheet.getRange("F:F").format.columnWidth = 12;
|
|
photosSheet.getRange("G:G").format.columnWidth = 90;
|
|
|
|
paginationSheet.getRange("A1:H2").values = [
|
|
["pagina", "offset", "limite", "content_range", "filas", "estado_http", "primer_id", "ultimo_id"],
|
|
[1, 0, 50, "*/0", 0, "200 OK", null, null],
|
|
];
|
|
paginationSheet.getRange("A3:D3").merge();
|
|
paginationSheet.getRange("A3:D3").values = [["Total descargado"]];
|
|
paginationSheet.getRange("E3").formulas = [["=SUM(E2:E2)"]];
|
|
paginationSheet.getRange("F3:H3").merge();
|
|
paginationSheet.getRange("F3:H3").values = [["Validado contra Content-Range: */0"]];
|
|
paginationSheet.getRange("A1:H1").format = { fill: navy, font: { bold: true, color: white }, rowHeight: 28 };
|
|
paginationSheet.getRange("A2:H2").format = { fill: white, font: { color: ink }, rowHeight: 24 };
|
|
paginationSheet.getRange("A3:H3").format = { fill: cyan, font: { bold: true, color: navy }, rowHeight: 26 };
|
|
paginationSheet.getRange("A1:H3").format.borders = { preset: "all", style: "thin", color: border };
|
|
paginationSheet.getRange("A2:F3").format.horizontalAlignment = "center";
|
|
paginationSheet.getRange("A:C").format.columnWidth = 12;
|
|
paginationSheet.getRange("D:D").format.columnWidth = 20;
|
|
paginationSheet.getRange("E:E").format.columnWidth = 12;
|
|
paginationSheet.getRange("F:F").format.columnWidth = 16;
|
|
paginationSheet.getRange("G:H").format.columnWidth = 38;
|
|
paginationSheet.freezePanes.freezeRows(1);
|
|
|
|
summary.getRange("A1:F2").merge();
|
|
summary.getRange("A1:F2").values = [["Apartamentos no visibles · RentPana"]];
|
|
summary.getRange("A1:F2").format = { fill: navy, font: { bold: true, color: white, size: 20 }, verticalAlignment: "center" };
|
|
summary.getRange("A3:F3").merge();
|
|
summary.getRange("A3:F3").values = [["Consulta independiente con is_visible = false"]];
|
|
summary.getRange("A3:F3").format = { fill: navy, font: { color: "#D9EDF2", size: 11 }, rowHeight: 24 };
|
|
|
|
summary.getRange("A5:B5").merge();
|
|
summary.getRange("C5:D5").merge();
|
|
summary.getRange("E5:F5").merge();
|
|
summary.getRange("A5:F5").values = [["Apartamentos", null, "Páginas consultadas", null, "Fotos", null]];
|
|
summary.getRange("A6:B7").merge();
|
|
summary.getRange("A6:B7").formulas = [["=COUNTA('Apartamentos'!$A$2:$A$2)"]];
|
|
summary.getRange("C6:D7").merge();
|
|
summary.getRange("C6:D7").formulas = [["=COUNTA('Paginacion'!$A$2:$A$2)"]];
|
|
summary.getRange("E6:F7").merge();
|
|
summary.getRange("E6:F7").formulas = [["=COUNTA('Fotos'!$A$2:$A$2)"]];
|
|
summary.getRange("A5:F5").format = { fill: teal, font: { bold: true, color: white }, horizontalAlignment: "center", rowHeight: 24 };
|
|
summary.getRange("A6:F7").format = { fill: cyan, font: { bold: true, color: navy, size: 20 }, horizontalAlignment: "center", verticalAlignment: "center" };
|
|
summary.getRange("A5:F7").format.borders = { preset: "outside", style: "thin", color: border };
|
|
|
|
summary.getRange("A9:F9").merge();
|
|
summary.getRange("A9:F9").values = [["Control de extracción"]];
|
|
summary.getRange("A9:F9").format = { fill: teal, font: { bold: true, color: white }, rowHeight: 26 };
|
|
const controlRows = [
|
|
["Fecha y hora (UTC)", new Date()],
|
|
["Origen", "https://srv99857.rentpana.com/rest/v1/apartments"],
|
|
["Filtro", "is_visible = false"],
|
|
["Orden estable", "id ascendente"],
|
|
["Tamaño de página", 50],
|
|
["Total informado por API", 0],
|
|
["IDs únicos validados", 0],
|
|
["Duplicados detectados", 0],
|
|
["Columnas de origen", columns.length],
|
|
["Resultado", "La API devolvió 0 registros para este filtro con la sesión suministrada."],
|
|
["Credenciales incluidas", "No"],
|
|
];
|
|
summary.getRange("A10:F20").values = controlRows.map(([a, b]) => [a, b, null, null, null, null]);
|
|
for (let row = 10; row <= 20; row += 1) summary.getRange(`B${row}:F${row}`).merge();
|
|
summary.getRange("A10:A20").format = { fill: pale, font: { bold: true, color: muted }, rowHeight: 24 };
|
|
summary.getRange("B10:F20").format = { fill: white, font: { color: ink }, rowHeight: 24, wrapText: true, horizontalAlignment: "left" };
|
|
summary.getRange("A10:F20").format.borders = { preset: "outside", style: "thin", color: border };
|
|
summary.getRange("B10").format.numberFormat = "yyyy-mm-dd hh:mm:ss";
|
|
|
|
summary.getRange("A22:F22").merge();
|
|
summary.getRange("A22:F22").values = [["Contenido del archivo"]];
|
|
summary.getRange("A22:F22").format = { fill: teal, font: { bold: true, color: white }, rowHeight: 26 };
|
|
const contents = [
|
|
["Apartamentos", "Estructura de 34 campos, lista para los registros devueltos por la consulta."],
|
|
["Fotos", "Hoja normalizada para enlaces de fotos; actualmente sin filas."],
|
|
["Paginacion", "Consulta, límite, rango, cantidad y estado HTTP documentados."],
|
|
];
|
|
summary.getRange("A23:F25").values = contents.map(([a, b]) => [a, b, null, null, null, null]);
|
|
for (let row = 23; row <= 25; row += 1) summary.getRange(`B${row}:F${row}`).merge();
|
|
summary.getRange("A23:A25").format = { fill: pale, font: { bold: true, color: navy }, rowHeight: 28 };
|
|
summary.getRange("B23:F25").format = { fill: white, font: { color: ink }, wrapText: true, rowHeight: 28 };
|
|
summary.getRange("A23:F25").format.borders = { preset: "outside", style: "thin", color: border };
|
|
summary.getRange("A:A").format.columnWidth = 24;
|
|
summary.getRange("B:F").format.columnWidth = 18;
|
|
summary.getRange("A1:F25").format.font.name = "Aptos";
|
|
summary.freezePanes.freezeRows(3);
|
|
|
|
await fs.mkdir(outputDir, { recursive: true });
|
|
const checks = [
|
|
(await workbook.inspect({ kind: "table", range: "Resumen!A1:F25", include: "values,formulas", tableMaxRows: 25, tableMaxCols: 6, maxChars: 6000 })).ndjson,
|
|
(await workbook.inspect({ kind: "table", range: "Paginacion!A1:H3", include: "values,formulas", tableMaxRows: 5, tableMaxCols: 8, maxChars: 2500 })).ndjson,
|
|
(await workbook.inspect({ kind: "match", searchTerm: "#REF!|#DIV/0!|#VALUE!|#NAME\\?|#N/A", options: { useRegex: true, maxResults: 300 }, summary: "final formula error scan" })).ndjson,
|
|
];
|
|
|
|
const previews = [
|
|
["Resumen", "A1:F25", "preview_resumen.png"],
|
|
["Apartamentos", "A1:J2", "preview_apartamentos_1.png"],
|
|
["Apartamentos", "K1:AH2", "preview_apartamentos_2.png"],
|
|
["Fotos", "A1:G2", "preview_fotos.png"],
|
|
["Paginacion", "A1:H3", "preview_paginacion.png"],
|
|
];
|
|
for (const [sheetName, range, fileName] of previews) {
|
|
const blob = await workbook.render({ sheetName, range, scale: 1.4, format: "png" });
|
|
await fs.writeFile(path.join(outputDir, fileName), new Uint8Array(await blob.arrayBuffer()));
|
|
}
|
|
|
|
const output = await SpreadsheetFile.exportXlsx(workbook);
|
|
await output.save(outputPath);
|
|
console.log(JSON.stringify({ outputPath, rows: 0, pagesConsulted: 1, columns: columns.length, checks, previews: previews.map((item) => path.join(outputDir, item[2])) }, null, 2));
|