whmcsMysqlService.js

Go to the documentation of this file.

/**
 * WHMCS MySQL Direct Access Service
 * Queries WHMCS database directly for migration
 */
 
const mysql = require('mysql2/promise');
 
/**
 * WHMCS MySQL direct access service.
 * Provides methods to query WHMCS database for migration purposes.
 */
class WHMCSMysqlService {
  /**
   * Initialize WHMCS MySQL service.
   * Creates connection pool with credentials from environment variables.
   */
  constructor() {
    this.config = {
      host: process.env.whmcs_mysql_host,
      port: parseInt(process.env.whmcs_mysql_port || '3306'),
      user: process.env.whmcs_mysql_user,
      password: process.env.whmcs_mysql_password,
      database: process.env.whmcs_mysql_db,
      waitForConnections: true,
      connectionLimit: 10,
      queueLimit: 0
    };
    
    if (!this.config.host || !this.config.user || !this.config.database) {
      throw new Error('WHMCS MySQL credentials not configured in .env');
    }
    
    this.pool = null;
  }
 
  /**
   * Get connection pool.
   * Creates pool on first call and reuses for subsequent requests.
   * @returns {Promise<mysql.Pool>} MySQL connection pool
   */
  async getPool() {
    if (!this.pool) {
      this.pool = mysql.createPool(this.config);
    }
    return this.pool;
  }
 
  /**
   * Execute query.
   * @param {string} sql - SQL query string
   * @param {Array} [params] - Query parameters
   * @returns {Promise<Array>} Query results
   */
  async query(sql, params = []) {
    const pool = await this.getPool();
    const [rows] = await pool.execute(sql, params);
    return rows;
  }
 
  /**
   * Test database connection.
   * @returns {Promise<boolean>} True if connection successful
   */
  async testConnection() {
    try {
      const pool = await this.getPool();
      await pool.query('SELECT 1');
      console.log('[WHMCS MySQL] ✅ Database connection successful');
      return true;
    } catch (error) {
      console.error('[WHMCS MySQL] ❌ Database connection failed:', error.message);
      return false;
    }
  }
 
  /**
   * Get all clients (customers).
   * @param {string} [status] - Filter by status (e.g., 'Active')
   * @returns {Promise<Array>} Client records
   */
  async getClients(status = 'Active') {
    const sql = status 
      ? 'SELECT * FROM tblclients WHERE status = ? ORDER BY id'
      : 'SELECT * FROM tblclients ORDER BY id';
    
    return await this.query(sql, status ? [status] : []);
  }
 
  /**
   * Get client by ID.
   * @param {number} clientId - Client ID
   * @returns {Promise<object|null>} Client record or null
   */
  async getClientById(clientId) {
    const sql = 'SELECT * FROM tblclients WHERE id = ?';
    const rows = await this.query(sql, [clientId]);
    return rows[0] || null;
  }
 
  /**
   * Get all domains.
   * @returns {Promise<Array>} Domain records with client information
   */
  async getDomains() {
    const sql = `
      SELECT 
        d.*,
        c.firstname, c.lastname, c.email, c.companyname
      FROM tbldomains d
      LEFT JOIN tblclients c ON d.userid = c.id
      ORDER BY d.id
    `;
    return await this.query(sql);
  }
 
  /**
   * Get domains for specific client.
   * @param {number} clientId - Client ID
   * @returns {Promise<Array>} Domain records
   */
  async getDomainsByClient(clientId) {
    const sql = 'SELECT * FROM tbldomains WHERE userid = ? ORDER BY id';
    return await this.query(sql, [clientId]);
  }
 
  /**
   * Get all hosting/products.
   * @returns {Promise<Array>} Product records with client and product information
   */
  async getProducts() {
    const sql = `
      SELECT 
        h.*,
        c.firstname, c.lastname, c.email, c.companyname,
        p.name as product_name, p.type as product_type
      FROM tblhosting h
      LEFT JOIN tblclients c ON h.userid = c.id
      LEFT JOIN tblproducts p ON h.packageid = p.id
      ORDER BY h.id
    `;
    return await this.query(sql);
  }
 
  /**
   * Get products for specific client.
   * @param {number} clientId - Client ID
   * @returns {Promise<Array>} Product records with product information
   */
  async getProductsByClient(clientId) {
    const sql = `
      SELECT 
        h.*,
        p.name as product_name, p.type as product_type
      FROM tblhosting h
      LEFT JOIN tblproducts p ON h.packageid = p.id
      WHERE h.userid = ?
      ORDER BY h.id
    `;
    return await this.query(sql, [clientId]);
  }
 
  /**
   * Get all invoices.
   * @returns {Promise<Array>} Invoice records with client information
   */
  async getInvoices() {
    const sql = `
      SELECT 
        i.*,
        c.firstname, c.lastname, c.email, c.companyname
      FROM tblinvoices i
      LEFT JOIN tblclients c ON i.userid = c.id
      ORDER BY i.id
    `;
    return await this.query(sql);
  }
 
  /**
   * Get invoice items.
   * @param {number} invoiceId - Invoice ID
   * @returns {Promise<Array>} Invoice item records
   */
  async getInvoiceItems(invoiceId) {
    const sql = 'SELECT * FROM tblinvoiceitems WHERE invoiceid = ? ORDER BY id';
    return await this.query(sql, [invoiceId]);
  }
 
  /**
   * Get all tickets.
   * @param {string|null} [status] - Filter by status
   * @returns {Promise<Array>} Ticket records
   */
  async getTickets(status = null) {
    const sql = status
      ? 'SELECT * FROM tbltickets WHERE status = ? ORDER BY id'
      : 'SELECT * FROM tbltickets ORDER BY id';
    
    return await this.query(sql, status ? [status] : []);
  }
 
  /**
   * Get ticket replies.
   * @param {number} ticketId - Ticket ID
   * @returns {Promise<Array>} Ticket reply records
   */
  async getTicketReplies(ticketId) {
    const sql = 'SELECT * FROM tblticketreplies WHERE tid = ? ORDER BY id';
    return await this.query(sql, [ticketId]);
  }
 
  /**
   * Get custom fields.
   * @returns {Promise<Array>} Custom field definitions
   */
  async getCustomFields() {
    const sql = 'SELECT * FROM tblcustomfields WHERE type = "client" ORDER BY id';
    return await this.query(sql);
  }
 
  /**
   * Get custom field values for client.
   * @param {number} clientId - Client ID
   * @returns {Promise<Array>} Custom field value records
   */
  async getCustomFieldValues(clientId) {
    const sql = `
      SELECT 
        v.*,
        f.fieldname, f.fieldtype
      FROM tblcustomfieldsvalues v
      LEFT JOIN tblcustomfields f ON v.fieldid = f.id
      WHERE v.relid = ? AND f.type = 'client'
    `;
    return await this.query(sql, [clientId]);
  }
 
  /**
   * Get database statistics.
   * @returns {Promise<object>} Statistics with counts for all entities
   */
  async getStatistics() {
    const stats = {};
    
    stats.clients = await this.query('SELECT COUNT(*) as count FROM tblclients');
    stats.activeClients = await this.query('SELECT COUNT(*) as count FROM tblclients WHERE status = "Active"');
    stats.domains = await this.query('SELECT COUNT(*) as count FROM tbldomains');
    stats.products = await this.query('SELECT COUNT(*) as count FROM tblhosting');
    stats.invoices = await this.query('SELECT COUNT(*) as count FROM tblinvoices');
    stats.tickets = await this.query('SELECT COUNT(*) as count FROM tbltickets');
    
    return {
      totalClients: stats.clients[0].count,
      activeClients: stats.activeClients[0].count,
      totalDomains: stats.domains[0].count,
      totalProducts: stats.products[0].count,
      totalInvoices: stats.invoices[0].count,
      totalTickets: stats.tickets[0].count
    };
  }
 
  /**
   * Export all data for migration.
   * @returns {Promise<object>} Complete export with clients, domains, products, etc.
   */
  async exportAllData() {
    console.log('[WHMCS MySQL] Starting full data export...');
    
    const stats = await this.getStatistics();
    console.log('[WHMCS MySQL] Database Statistics:');
    console.log(`  - Total Clients: ${stats.totalClients}`);
    console.log(`  - Active Clients: ${stats.activeClients}`);
    console.log(`  - Domains: ${stats.totalDomains}`);
    console.log(`  - Products: ${stats.totalProducts}`);
    console.log(`  - Invoices: ${stats.totalInvoices}`);
    console.log(`  - Tickets: ${stats.totalTickets}\n`);
 
    console.log('[WHMCS MySQL] Fetching clients...');
    const clients = await this.getClients('Active');
    console.log(`[WHMCS MySQL] Found ${clients.length} active clients`);
 
    console.log('[WHMCS MySQL] Fetching domains...');
    const domains = await this.getDomains();
    console.log(`[WHMCS MySQL] Found ${domains.length} domains`);
 
    console.log('[WHMCS MySQL] Fetching products...');
    const products = await this.getProducts();
    console.log(`[WHMCS MySQL] Found ${products.length} products`);
 
    console.log('[WHMCS MySQL] Fetching custom fields...');
    const customFields = await this.getCustomFields();
    console.log(`[WHMCS MySQL] Found ${customFields.length} custom fields`);
 
    return {
      clients,
      domains,
      products,
      customFields,
      statistics: stats,
      exportedAt: new Date().toISOString()
    };
  }
 
  /**
   * Close connection pool
   */
  async close() {
    if (this.pool) {
      await this.pool.end();
      this.pool = null;
    }
  }
}
 
module.exports = WHMCSMysqlService;