Complex(ish) Query on Customfields

[othellotech]

As part of our own extensions to the admin side of WHMCS, we recently needed to make sure that all the dedicated servers were documented for their power outlets, ready for enabling automated reboots within the clients view product details pages, as well as overall power control as an admin module.

 

I added a Custom Field to each Dedicated Server package with a description of "Power:Unit:Outlet"

and a bit of cut&paste in in our s/sheets to generate the sql to set those values to

MAINS:{bar=id}:{socket-number}

APC:{apc-id}:{outlet-number}

CUSTOMER:

TRACK:{electrak-id}

etc

 

A mysql query then got me a list of all those so that we coudl easily verify which machines had data missing, and could then set a DC technician the "interesting" task of tracing the cables accordingly ...

 

FYI if doing anything similar, as it took me several hours to bed my mind round it ...

 

SELECT 
f.relid as PackageID, p.name as PackageName, 
v.relid as ProductID, h.domain as ServerLabel, 
f.fieldname as Setting, v.value as PowerUnit, 
h.domainstatus as Status
FROM tblcustomfieldsvalues v LEFT JOIN tblcustomfields f ON f.id=v.fieldid  LEFT JOIN tblproducts p ON p.id=f.relid LEFT JOIN tblhosting h ON h.id=v.relid 
WHERE f.fieldname like "Power:%" AND h.domainstatus IN ("Active", "Suspended", "Pending") 
ORDER BY h.domain;

 

Now we can add code to the product-details pages in the client area along the lines of

{psuedo-code}

if ($Setting == "Power:Unit:Outlet")

if (substr($PowerUnit,1,4)=="APC:")

code to add reboot button

else

code to show "raise a ticket link"

{/psuedo-code}

[handsonwebhosting]

Hey Rob - nice addition. While we're not doing much with the reboot switches any more, I'll likely be able to take some of the code you produced to make something similar to allow us to notify our data center (the planet) for similar reboots.

 

Nice work! Too bad This wasn't around a couple of years ago, could have really used it then

[othellotech]

I'll likely be able to take some of the code you produced to make something similar to allow us to notify our data center (the planet) for similar reboots.

 

thats easy

 

{if $type eq "server"}
<a href=mailto: .....
{/if}

just add a mailto link which emails their support system !

[handsonwebhosting]

awww... and I was looking at spending some time working on it too

 

Thanks Rob - much appreciated (AGAIN).