pull-iphone-data.js

pull-iphone-data.js

const { execSync } = require("child_process");
var XLSX = require("xlsx");

const startTime = new Date();

// Function to calculate and format elapsed time
const getElapsedTime = () => {
    const elapsed = new Date() - startTime;
    const minutes = Math.floor(elapsed / 60000).toString().padStart(2, "0");
    const seconds = Math.floor((elapsed % 60000) / 1000).toString().padStart(2, "0");
    const milliseconds = (elapsed % 1000).toString().padStart(3, "0");
    return `${minutes}:${seconds}:${milliseconds}`;
};

// Function to log messages with elapsed time
const logWithElapsedTime = (message) => {
    console.log(`[${getElapsedTime()}] ${message}`);
};

// Define the expected headers
const expectedHeaders = ["phoneNumber", "serialNumber", "IMEI"];

// Function to sanitize phone numbers
const sanitizePhoneNumber = (phoneNumber) => {
    logWithElapsedTime(`Sanitizing phone number: ${phoneNumber}`);
    if (phoneNumber.startsWith("+")) {
        phoneNumber = phoneNumber.replace(/^\+\d+\s*/, "");
    } else if (phoneNumber.startsWith("Error")) {
        phoneNumber = "MISSING"
    }
    const sanitized = phoneNumber.replace(/\D/g, "");
    logWithElapsedTime(`Sanitized phone number: ${sanitized}`);
    return sanitized;
};

// Function to get the file path using macOS file picker
const getFilePath = () => {
    try {
        logWithElapsedTime("Selecting the Excel file...");
        const script = `
            set chosenFile to choose file with prompt "Select the Excel file to update:"
            POSIX path of chosenFile
        `;
        const filePath = execSync(`osascript -e '${script}'`, { encoding: "utf-8" }).trim();
        logWithElapsedTime(`File selected: ${filePath}`);
        return filePath;
    } catch (error) {
        logWithElapsedTime(`Error selecting the file. Exiting... ${error.message}`);
        process.exit(1);
    }
};

// Function to get the sheet name using buttons for selection
const getSheetName = (workbook) => {
    const sheetNames = workbook.SheetNames;
    logWithElapsedTime(`Retrieving sheet names: ${sheetNames.join(", ")}`);
    const recommendedSheets = sheetNames.filter((sheetName) => {
        const sheet = workbook.Sheets[sheetName];
        const headers = XLSX.utils.sheet_to_json(sheet, { header: 1 })[0] || [];
        return expectedHeaders.every((header) => headers.includes(header));
    });

    const sheetOptions = sheetNames.map((sheetName) =>
        recommendedSheets.includes(sheetName) ? `${sheetName} (Recommended)` : sheetName
    );

    const buttonScript = `
        display dialog "Select the sheet to update:" buttons {${sheetOptions
            .map((option) => `"${option}"`)
            .join(", ")}} default button 1
        return button returned of result
    `;

    try {
        const selectedOption = execSync(`osascript -e '${buttonScript}'`, { encoding: "utf-8" }).trim();
        const sheetName = selectedOption.replace(" (Recommended)", "");
        logWithElapsedTime(`Sheet selected: ${sheetName}`);
        return sheetName;
    } catch (error) {
        logWithElapsedTime(`Error selecting the sheet. Exiting... ${error.message}`);
        process.exit(1);
    }
};

// Function to run cfgutil list to check the number of devices
const getDeviceCount = () => {
    try {
        logWithElapsedTime("Checking connected devices with cfgutil list...");
        const output = execSync("cfgutil list", { encoding: "utf-8" });
        const lines = output.trim().split("\n");
        const deviceCount = lines.filter((line) => line && !line.includes("ECID")).length;
        logWithElapsedTime(`Number of connected devices: ${deviceCount}`);
        return deviceCount;
    } catch (error) {
        logWithElapsedTime(`Failed to retrieve device list. Exiting... ${error.message}`);
        process.exit(1);
    }
};

// Function to run cfgutil and fetch device data
const fetchDeviceData = () => {
    const maxRetries = 5;
    const retryDelay = 10000; // 10 seconds
    const timeoutDuration = 500000; // 300 seconds

    for (let attempt = 1; attempt <= maxRetries; attempt++) {
        try {
            logWithElapsedTime(`Attempt ${attempt} of ${maxRetries}: Fetching device data...`);
            const commandOutput = execSync(
                "cfgutil --foreach get phoneNumber serialNumber IMEI",
                { encoding: "utf-8", timeout: timeoutDuration }
            );
            logWithElapsedTime("Device data retrieved successfully.");
            logWithElapsedTime(commandOutput);
            return commandOutput;
        } catch (error) {
            logWithElapsedTime(
                `Attempt ${attempt}: Error fetching device data. Retrying in ${retryDelay / 1000} seconds... ${error.message}`
            );
            if (attempt < maxRetries) {
                setTimeout(() => {}, retryDelay);
            } else {
                logWithElapsedTime(`Failed to fetch device data after multiple attempts. Exiting... ${error.message}`);
                process.exit(1);
            }
        }
    }
};

// Function to process the cfgutil output into structured data
const processDeviceData = (commandOutput) => {
    logWithElapsedTime("Processing device data...");
    const deviceData = [];
    const lines = commandOutput.trim().split("\n");
    let singleDevice = {};
    let currentCategory = null;

    // Check if the output is for multiple devices (contains ECID)
    const isMultipleDevices = lines.some((line) => line.includes("ECID:"));

    if (isMultipleDevices) {
        logWithElapsedTime("Detected multiple devices in cfgutil output.");

        // Parse multiple devices
        const devicesByCategory = {}; // Map categories (phoneNumber, serialNumber, IMEI) to their values

        lines.forEach((line) => {
            line = line.trim();
            if (line.endsWith(":") && !line.includes("ECID")) {
                // This is a category line without ECID
                currentCategory = line.slice(0, -1);
                devicesByCategory[currentCategory] = {};
            } else if (currentCategory && line.includes("ECID")) {
                // This is a device entry with ECID
                const [deviceInfo, value] = line.split("):").map((part) => part.trim());
                const ecidMatch = deviceInfo.match(/ECID: (\S+)/);
                if (ecidMatch) {
                    const ecid = ecidMatch[1];
                    devicesByCategory[currentCategory][ecid] = value || "MISSING";
                }
            }
        });

        // Combine device data across categories
        const ecids = Object.keys(devicesByCategory.phoneNumber || {});
        ecids.forEach((ecid) => {
            const device = {
                phoneNumber: devicesByCategory.phoneNumber[ecid] || "MISSING",
                serialNumber: devicesByCategory.serialNumber[ecid] || "MISSING",
                IMEI: devicesByCategory.IMEI[ecid] || "MISSING",
            };
            deviceData.push(device);
            logWithElapsedTime(`Parsed device: ${JSON.stringify(device)}`);
        });
    } else {
        logWithElapsedTime("Detected a single device in cfgutil output.");

        // Parse single device
        lines.forEach((line) => {
            line = line.trim();
            if (line.endsWith(":")) {
                // This is a category line
                currentCategory = line.slice(0, -1);
            } else if (currentCategory) {
                // This is the value for the last category
                const value = line.includes("Error: The value is missing.") ? "MISSING" : line;
                singleDevice[currentCategory] = value;
                logWithElapsedTime(`Added data: ${currentCategory} = ${value}`);
                currentCategory = null; // Reset for the next key-value pair
            }
        });

        if (Object.keys(singleDevice).length > 0) {
            logWithElapsedTime(`Single device data parsed: ${JSON.stringify(singleDevice)}`);
            deviceData.push({
                phoneNumber: singleDevice.phoneNumber || "MISSING",
                serialNumber: singleDevice.serialNumber || "MISSING",
                IMEI: singleDevice.IMEI || "MISSING",
            });
        }
    }

    // Sanitize phone numbers
    deviceData.forEach((device) => {
        if (device.phoneNumber) {
            device.phoneNumber = sanitizePhoneNumber(device.phoneNumber);
        }
    });

    logWithElapsedTime("Device data processed successfully.");
    return deviceData;
};


// Function to update the specified sheet in the Excel file
const updateExcelFile = (filePath, sheetName, deviceData) => {
    try {
        logWithElapsedTime(`Updating the Excel file: ${filePath}`);
        const workbook = XLSX.readFile(filePath);
        const sheet = workbook.Sheets[sheetName];

        if (!sheet) {
            throw new Error(`Sheet "${sheetName}" does not exist in the file.`);
        }

        const existingData = XLSX.utils.sheet_to_json(sheet, { header: 1 });
        const headers = existingData[0] || [];
        const finalHeaders = Array.from(new Set([...headers, ...expectedHeaders]));

        if (headers.length === 0) {
            throw new Error(`Sheet "${sheetName}" is empty or does not have headers.`);
        }

        const existingEntries = new Set(
            existingData.slice(1).flatMap((row) => [row[finalHeaders.indexOf("IMEI")], row[finalHeaders.indexOf("serialNumber")]])
        );

        deviceData.forEach((data) => {
            const imei = data.IMEI || "null";
            const serialNumber = data.serialNumber || "null";

            if (existingEntries.has(imei) || existingEntries.has(serialNumber)) {
                logWithElapsedTime(`Skipping entry with IMEI: ${imei}, serialNumber: ${serialNumber} (already exists).`);
                return;
            }

            const row = finalHeaders.map((header) => data[header] || "null");
            existingData.push(row);
            logWithElapsedTime(`Added row to Excel: ${row}`);
        });

        const updatedSheet = XLSX.utils.aoa_to_sheet(existingData);
        workbook.Sheets[sheetName] = updatedSheet;

        XLSX.writeFile(workbook, filePath);
        logWithElapsedTime("Excel file updated successfully.");
    } catch (error) {
        logWithElapsedTime(`Failed to update the Excel file or sheet. Exiting... ${error.message}`);
        process.exit(1);
    }
};

// Main script execution
const main = async () => {
    logWithElapsedTime("Starting script...");
    const commandOutput = fetchDeviceData();
    const deviceData = processDeviceData(commandOutput);
    const filePath = getFilePath();

    let workbook;
    try {
        workbook = XLSX.readFile(filePath);
        logWithElapsedTime("Excel file loaded successfully.");
    } catch (error) {
        logWithElapsedTime(`Error reading the file. Exiting... ${error.message}`);
        process.exit(1);
    }

    const sheetName = getSheetName(workbook);
    updateExcelFile(filePath, sheetName, deviceData);
    logWithElapsedTime("Task completed successfully.");
};

main();