whmcs-pricesync-addon/domain_tool.php

<?php
if (!defined("WHMCS")) {
    die("This file cannot be accessed directly");
}

function domain_tool_config() {
    return [
        'name' => 'Active Domains List',
        'description' => 'An addon to list all active domains with client name, domain name, renewal period, recurring amount, and renewal price.',
        'version' => '1.0',
        'author' => 'Your Name',
        'language' => 'english',
        'fields' => []
    ];
}

function domain_tool_activate() {
    return [
        'status' => 'success',
        'description' => 'The Active Domains List module has been activated successfully.'
    ];
}

function domain_tool_deactivate() {
    return [
        'status' => 'success',
        'description' => 'The Active Domains List module has been deactivated successfully.'
    ];
}

function domain_tool_output($vars) {
    $query = "
SELECT 
    tblclients.id AS client_id,
    tblclients.firstname, 
    tblclients.lastname, 
    tbldomains.id AS domain_id,
    tbldomains.domain, 
    tbldomains.registrationperiod, 
    tbldomains.recurringamount, 
    rp.renewalprice,
    (rp.renewalprice - tbldomains.recurringamount) AS pricedifference
FROM 
    tbldomains 
JOIN 
    tblclients ON tblclients.id = tbldomains.userid 
JOIN (
    SELECT 
        tbldomains.id AS domain_id,
        CASE tbldomains.registrationperiod 
            WHEN 1 THEN tblpricing.msetupfee 
            WHEN 2 THEN tblpricing.qsetupfee 
            WHEN 3 THEN tblpricing.ssetupfee 
            WHEN 4 THEN tblpricing.asetupfee 
            WHEN 5 THEN tblpricing.bsetupfee 
            WHEN 6 THEN tblpricing.monthly 
            WHEN 7 THEN tblpricing.quarterly 
            WHEN 8 THEN tblpricing.semiannually 
            WHEN 9 THEN tblpricing.annually 
            ELSE 0 
        END AS renewalprice
    FROM tbldomains
    JOIN tbldomainpricing ON tbldomainpricing.extension = CONCAT('.', SUBSTRING_INDEX(tbldomains.domain, '.', -1))
    JOIN tblpricing ON tblpricing.relid = tbldomainpricing.id 
    WHERE tblpricing.type = 'domainrenew' 
      AND tblpricing.currency = 1 
) rp ON rp.domain_id = tbldomains.id
WHERE 
    tbldomains.status = 'Active'
    AND tbldomains.recurringamount > 0
    AND (rp.renewalprice - tbldomains.recurringamount) != 0;
    ";

    // Handling the update action
    if (isset($_POST['update_recurringamount']) && isset($_POST['domain_id'])) {
        $domainId = (int) $_POST['domain_id'];
        $price = (float) $_POST['price'];

        $updateQuery = "UPDATE tbldomains 
                        SET recurringamount = {$price}
                        -- SET recurringamount = (SELECT renewalprice FROM tbldomains 
                        --                         JOIN tblpricing ON tblpricing.relid = tbldomains.id
                        --                         WHERE tbldomains.id = {$domainId} LIMIT 1)
                        WHERE id = {$domainId}";

        // Execute the query to update the recurring amount
        full_query($updateQuery);

        // Optional: show a message indicating success
        echo "<div class='alert alert-success'>Recurring amount updated successfully!</div>";
    }

    $result = full_query($query);

    echo '<table class="table table-bordered table-striped">';
    echo '<thead><tr>
            <th>ID</th>
            <th>Domain</th>
            <th>Client Name</th>
            <th>Renewal Period</th>
            <th>Recurring Amount</th>
            <th>Renewal Price</th>
            <th>Price Difference</th>
            <th>Actions</th>
          </tr></thead><tbody>';

    while ($data = mysql_fetch_array($result)) {
        $client_id = $data['client_id'];
        $clientName = $data['firstname'] . ' ' . $data['lastname'];
        $domain_id = $data['domain_id'];
        $domainName = $data['domain'];
        $renewalPeriod = $data['registrationperiod'] . ' Year(s)';
        $recurringAmount = '$' . number_format($data['recurringamount'], 2);
        $renewalPrice = '$' . number_format($data['renewalprice'], 2);
        $priceDifference = '$' . number_format($data['pricedifference'], 2);
        $domainId = $data['domain_id'];
        $price = $data['renewalprice'];

        // Adding the action column with an "Update" button
        echo "<tr>
                <td><a href=\"clientsdomains.php?id={$domain_id}\">{$domain_id}</a></td>
                <td><a href=\"clientsdomains.php?id={$domain_id}\">{$domainName}</a></td>
                <td><a href=\"clientssummary.php?userid={$client_id}\">{$clientName}</td>
                <td>{$renewalPeriod}</td>
                <td>{$recurringAmount}</td>
                <td>{$renewalPrice}</td>
                <td>{$priceDifference}</td>
                <td>
                    <form method='post' action=''>
                        <input type='hidden' name='domain_id' value='{$domainId}' />
                        <input type='hidden' name='price' value='{$price}' />
                        <button type='submit' name='update_recurringamount' class='btn btn-primary'>Update Recurring Amount</button>
                    </form>
                </td>
              </tr>";
    }

    echo '</tbody></table>';
}