Finding all active orders for a specific product

[jonhubbard]

Hi Everyone, 

I'm a newbie to WHMCS Dev so please be gentle!

I'm trying to work out how I can get a list of all active orders for a specific product in the internal API, or simply just get such a list anyway.

looking at things I'm thinking I'm going to have to perform some pretty recursive searches which don't seem very efficient and wondered if anyone could point me to a simple method for getting this information.

Many thanks

Jon

[twhiting9275]

It's possible to do . A query like this will do it:

$productid = "INSERT PRODUCT ID HERE";

foreach (Capsule::table('tblhosting')->WHERE('packageid', '=', $productid)-> WHERE('domainstatus', '=', 'Active')->get() as $orderlist) {
                        $userid = $orderlist->userid;
                        $server = $orderlist->server;
                        $domain = $orderlist->domain;
                        $paymentmethod = $orderlist->paymentmethod;
                        $billingcycle = $orderlist->billingcycle;
                        $nextinvoicedate = $orderlist->nextinvoicedate;
                        $amount = $orderlist->amount;
                        print ("$userid: $server : $domain: $paymentmethod: $billingcycle: $nextinvoice: $amount<br />");

}

Implementation is going to be on you, but this will get you started

Edited February 15, 2023 by twhiting9275

[swinggraphics]

I created a simple module to display a table of active product info.

<?php
/**
 * List Active Products Addon Module
 *
 * @see https://developers.whmcs.com/addon-modules/
 */

if ( ! defined( 'WHMCS' ) ) {
    die( 'This file cannot be accessed directly' );
}

use WHMCS\Database\Capsule;

/**
 * Define addon module configuration parameters.
 *
 * @return array
 */
function list_active_products_config() {
    return array(
        'name' => 'List Active Products', // Display name for your module
        'description' => '.',
        'author' => 'Greg Perham', // Module author name
        'language' => 'english', // Default language
        'version' => '1.0', // Version number
        'fields' => array(),
    );
}

/**
 * Activate.
 *
 * This function is optional.
 *
 * @return array Optional success/failure message
 */
function list_active_products_activate() {
    return array(
        'status' => 'success', // Supported values here include: success, error or info
        'description' => 'List Active Products addon enabled.',
    );
}

/**
 * Deactivate.
 *
 * This function is optional.
 *
 * @return array Optional success/failure message
 */
function list_active_products_deactivate() {
    return array(
        'status' => 'success', // Supported values here include: success, error or info
        'description' => 'List Active Products addon disabled.',
    );
}

/**
 * Admin Area Output.
 *
 * Called when the addon module is accessed via the admin area.
 * Should print (not return) output.
 */
function list_active_products_output( $vars ) {
    global $CONFIG, $customadminpath;
    $adminurl = $CONFIG['SystemURL'] . '/' . $customadminpath . '/';

    // Get common module parameters
    $modulelink = $vars['modulelink']; // eg. addonmodules.php?m=list_active_products
    $version = $vars['version']; // eg. 1.0
    $_lang = $vars['_lang']; // an array of the currently loaded language variables

    // Get product info
    $_products = [];
    $products = Capsule::table('tblproducts')->select('id', 'name')->get();
    foreach ($products as $product) {
        $_products[$product->id] = $product->name;
    }

    $html = '';
    $productid = 0;
    $orders = [];
    if ( isset( $_GET['productid'] ) && is_numeric( $_GET['productid'] ) ) {
        $productid = $_GET['productid'];
        $orders = Capsule::table('tblhosting')->where('packageid', '=', $productid)->where('domainstatus', '=', 'Active')->get();
        $heading = "Displaying product ID $productid: {$_products[$product->id]}";
    } else {
        $orders = Capsule::table('tblhosting')->where('domainstatus', '=', 'Active')->orderby('packageid', 'asc')->get();
        $heading = "Displaying all products";
    }
    // echo '<pre>'; var_dump( $orders ); echo '</pre>'; die;
    foreach ($orders as $orderlist) {
        $productid = $orderlist->packageid;
        $productname = $_products[$productid];
        $userid = $orderlist->userid;
        $orderid = $orderlist->id;
        $domain = $orderlist->domain ?: 'None specified.';
        $server = $orderlist->server;
        $paymentmethod = $orderlist->paymentmethod;
        $billingcycle = $orderlist->billingcycle;
        $nextduedate = $orderlist->nextduedate;
        $nextinvoicedate = $orderlist->nextinvoicedate;
        $amount = $orderlist->amount;
        $tablerows .= ("<tr>
            <td><a href='{$modulelink}&productid=$productid'>$productid</a></td>
            <td>$productname</td>
            <td><a href='{$adminurl}clientssummary.php?userid=$userid'>$userid</a></td>
            <td><a href='{$adminurl}clientsservices.php?userid={$userid}&productselect={$orderid}'>$domain</a></td>
            <td>$server</td>
            <td>$paymentmethod</td>
            <td>$billingcycle</td>
            <td>$nextduedate</td>
            <td>$nextinvoicedate</td>
            <td>$amount</td>
        </tr>");
    }
    ?>
    <h2><?php echo $heading; ?></h2>
    <style>#product-list td a {display: block;}</style>
    <table id="product-list" class="datatable" width="100%" border="0" cellspacing="1" cellpadding="3">
        <thead>
            <tr>
                <th>Product ID</th>
                <th>Product Name</th>
                <th>User ID</th>
                <th>Domain</th>
                <th>Server</th>
                <th>Payment Method</th>
                <th>Billing Cycle</th>
                <th>Next Invoice</th>
                <th>Next Due</th>
                <th>Amount</th>
            </tr>
        </thead>
        <tbody>
            <?php echo $tablerows; ?>
        </tbody>
    </table>
    <?php
}