Fix unmatched due date from products and domain

[nka]

Hello,

 

I'm not a dev, but I made this to help me. Maybe someone would love to have it.

 

What it does it search in the database for annual hosting and search if there's a domain. If yes, it look if both have the same "due date" so the customer won't have two invoice. You can fix that by clicking the "fix" (it will update the due date of the domain to the due date of the products).

 

Maybe there's some more thing we could do, like a "fix all".

 

It's a reports, so just put this file into /modules/reports/ and you'll see it in "reports" into WHMCS Admin.

 

<?php

if (!defined("WHMCS"))
die("This file cannot be accessed directly");

if ($_POST['fixid']) {
$fixid = $_POST['fixid'];
$fixduedate = $_POST['fixduedate'];
$fixquery = "UPDATE tbldomains SET nextduedate=\"". $fixduedate ."\",nextinvoicedate=\"". $fixduedate ."\" WHERE id=\"". $fixid ."\"";
$fixupdate = "Fix Result: ";
$fixresult = mysql_query($fixquery) or $fixupdate = $fixupdate."<FONT COLOR=\"RED\">NOT</FONT> ";
$fixupdate = $fixupdate."UPDATED"; 
} else {
$fixquery="";
$fixupdate="";
$fixresult="";
}


$reportdata["title"] = "Domains not matching products due date";
$reportdata["description"] = $fixupdate;

$query = "SELECT tbldomains.id domainid, tbldomains.domain domaindomain, tbldomains.nextduedate domainnextduedate, tblhosting.nextduedate hostingnextduedate FROM tblhosting INNER JOIN tbldomains ON tblhosting.domain=tbldomains.domain WHERE tblhosting.billingcycle = 'Annually' AND tblhosting.nextduedate != tbldomains.nextduedate";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);

$reportdata["headertext"] = "Total non-matching domain: $num_rows";

$reportdata["tableheadings"] = array("ID","domain","Domain Due Date", "Hosting Due Date","");

while ($data = mysql_fetch_array($result)) {
$id = $data["domainid"];
$domain = $data["domaindomain"];
$domainduedate = $data["domainnextduedate"];
$hostingduedate = $data["hostingnextduedate"];

$fix = "
<form method=\"post\" action=\"reports.php?report='.$report.'\">
<input type=\"hidden\" name=\"fixid\" value=\"". $id ."\" />
<input type=\"hidden\" name=\"fixduedate\" value=\"".$hostingduedate."\" />
<input type=\"submit\" value=\"Fix\" />";	

$reportdata["tablevalues"][] = array("<a href='clientsdomains.php?id=".$id."' target='_blank'>".$id."</a>",$domain,$domainduedate,$hostingduedate,$fix);
}

$data["footertext"]="";
?>

[jin]

Nice I was looking for something like this. It would be good if canceled/terminated domains didn't show in the list.

 

Or if it showed in another column if the account/domain was still active.

 

Thanks for this

[jin]

Another issue I see is if the domain has 3 years until expire and the hosting is per year, if you click the fix it will change the domain to the account which would be wrong, also I think if you have the CRON run it will change the domain date back.

[nka]

For the "Canceled/Terminated", you only need to modify the SQL request and add something like "AND tblhosting.status =! "Canceled"".

 

As for the 3 years... would need some fix for that kind of exception, like reading the number of year of both products and do the math. I didn't go that deep.

 

If someone wish to take and improve it, go for it!