fetch_whmcs_data.js

Go to the documentation of this file.

#!/usr/bin/env node
/**
 * Fetch all customer data from WHMCS API
 * Saves to JSON and CSV formats for import
 */
 
require('dotenv').config();
const fs = require('fs');
const path = require('path');
const WHMCSMysqlService = require('../services/whmcsMysqlService');
 
const EXPORT_DIR = path.join(__dirname, '../exports');
 
// Ensure export directory exists
if (!fs.existsSync(EXPORT_DIR)) {
  fs.mkdirSync(EXPORT_DIR, { recursive: true });
}
 
/**
 * Convert array to CSV
 * @param data
 * @param headers
 */
function arrayToCSV(data, headers) {
  if (!data || data.length === 0) return '';
  
  const csvHeaders = headers.join(',');
  const csvRows = data.map(row => {
    return headers.map(header => {
      const value = row[header] || '';
      // Escape commas and quotes
      const escaped = String(value).replace(/"/g, '""');
      return `"${escaped}"`;
    }).join(',');
  });
  
  return [csvHeaders, ...csvRows].join('\n');
}
 
/**
 * Main export function
 */
async function fetchWHMCSData() {
  try {
    console.log('========================================');
    console.log('WHMCS Data Export (MySQL Direct Access)');
    console.log('========================================\n');
    
    const whmcs = new WHMCSMysqlService();
    
    // Test connection
    console.log('Testing WHMCS MySQL connection...');
    const connected = await whmcs.testConnection();
    if (!connected) {
      throw new Error('Failed to connect to WHMCS database');
    }
    console.log('');
    
    // Fetch all data
    console.log('Fetching data from WHMCS...\n');
    const exportData = await whmcs.exportAllData();
    
    // Save full JSON export
    const jsonPath = path.join(EXPORT_DIR, 'whmcs_export.json');
    fs.writeFileSync(jsonPath, JSON.stringify(exportData, null, 2));
    console.log(`\n✅ Full export saved: ${jsonPath}`);
    
    // Save clients CSV
    if (exportData.clients.length > 0) {
      const clientHeaders = [
        'id', 'firstname', 'lastname', 'companyname', 'email',
        'address1', 'address2', 'city', 'state', 'postcode', 'country',
        'phonenumber', 'status', 'datecreated', 'currency'
      ];
      const clientsCSV = arrayToCSV(exportData.clients, clientHeaders);
      const clientsPath = path.join(EXPORT_DIR, 'whmcs_clients.csv');
      fs.writeFileSync(clientsPath, clientsCSV);
      console.log(`✅ Clients CSV saved: ${clientsPath}`);
    }
    
    // Save domains CSV
    if (exportData.domains.length > 0) {
      const domainHeaders = [
        'id', 'userid', 'domainname', 'registrar', 'registrationdate',
        'expirydate', 'status', 'nextduedate', 'recurringamount'
      ];
      const domainsCSV = arrayToCSV(exportData.domains, domainHeaders);
      const domainsPath = path.join(EXPORT_DIR, 'whmcs_domains.csv');
      fs.writeFileSync(domainsPath, domainsCSV);
      console.log(`✅ Domains CSV saved: ${domainsPath}`);
    }
    
    // Save products CSV
    if (exportData.products.length > 0) {
      const productHeaders = [
        'id', 'clientid', 'orderid', 'pid', 'name', 'domain',
        'regdate', 'nextduedate', 'billingcycle', 'amount', 'status'
      ];
      const productsCSV = arrayToCSV(exportData.products, productHeaders);
      const productsPath = path.join(EXPORT_DIR, 'whmcs_products.csv');
      fs.writeFileSync(productsPath, productsCSV);
      console.log(`✅ Products CSV saved: ${productsPath}`);
    }
    
    // Summary
    console.log('\n========================================');
    console.log('Export Summary');
    console.log('========================================');
    console.log(`Clients:  ${exportData.clients.length}`);
    console.log(`Domains:  ${exportData.domains.length}`);
    console.log(`Products: ${exportData.products.length}`);
    console.log(`Custom Fields: ${exportData.customFields.length}`);
    console.log(`\nExported at: ${exportData.exportedAt}`);
    console.log('========================================\n');
    
    // Show sample data
    if (exportData.clients.length > 0) {
      console.log('Sample Clients (first 3):');
      exportData.clients.slice(0, 3).forEach(client => {
        console.log(`  ${client.id}: ${client.firstname} ${client.lastname} (${client.companyname || 'N/A'}) - ${client.email}`);
      });
      console.log('');
    }
    
    if (exportData.domains.length > 0) {
      console.log('Sample Domains (first 3):');
      exportData.domains.slice(0, 3).forEach(domain => {
        console.log(`  ${domain.id}: ${domain.domainname} - ${domain.status} (expires: ${domain.expirydate})`);
      });
      console.log('');
    }
    
    console.log('✅ Export complete! Files saved to:', EXPORT_DIR);
    console.log('\nNext step: Run import script');
    console.log('  node migration-scripts/import_whmcs_customers.js\n');
    
  } catch (error) {
    console.error('\n❌ Export failed:', error.message);
    console.error(error.stack);
    process.exit(1);
  } finally {
    // Close MySQL connection
    try {
      const WHMCSMysqlService = require('../services/whmcsMysqlService');
      const whmcs = new WHMCSMysqlService();
      await whmcs.close();
    } catch (e) {
      // Ignore close errors
    }
  }
}
 
// Run if called directly
if (require.main === module) {
  fetchWHMCSData();
}
 
module.exports = { fetchWHMCSData };