Add "Upcoming Domain Renewals" to client area

[NH-Andrew]

Can we change the way the date of expiry is displayed? Currently it's in the form YYYY-MM-DD I'd prefer DD/MM/YYYY as that's how we have it in the rest of the client area.

 

Thanks.

[othellotech]

To do 30 days and 60 days just change the function

from "m"+1/2 to "d"+30/60

 

not showing domains already invoiced for the renewal of is a little trickier, but a worthwhile mod to this useful contribution

[othellotech]

actually if it used the date from the WHMCS config rather than coded to be 30 ...

[Si]

To do 30 days and 60 days just change the function

from "m"+1/2 to "d"+30/60

 

 

 

Hi,

Intrigued by your post, but can't get it to work. Could you quote exactly what these 2 lines should be changed to for your suggestion to work please?

 

$date30 = date("Ymd",mktime(0,0,0,date("m")+1,date("d"),date("Y")));
$date60 = date("Ymd",mktime(0,0,0,date("m")+2,date("d"),date("Y")));

 

I tried:

 

$date30 = date("Ymd",mktime(0,0,0,date("d")+30,date("d"),date("Y")));
$date60 = date("Ymd",mktime(0,0,0,date("d")+60,date("d"),date("Y")));

 

with no success

 

Thanks

 

Si

[othellotech]

Oh I expected you to do a *liitle* of the work

 

try ...

$date30 = date("Ymd",mktime(0,0,0,date("m"),date("d")+30,date("Y")));
$date60 = date("Ymd",mktime(0,0,0,date("m"),date("d")+60,date("Y")));

[Dominic]

Has nobody heard of the excellent MySQL date functions :(

 

...AND DATEDIFF(expirydate, NOW()) <= X

Where X is the number of days

 

[ask21900]

Has nobody heard of the excellent MySQL date functions :(

 

...AND DATEDIFF(expirydate, NOW()) <= X

Where X is the number of days

 

 

I'm pretty sure that is a sql date function (for sql datatypes).

 

If you were using sql dates that would be great, but we are using php (unix) time.

[Dominic]

I'm pretty sure that is a sql date function (for sql datatypes).

 

If you were using sql dates that would be great, but we are using php (unix) time.

 

`expirydate` date default NULL

 

mysql> select * from tbldomains where DATEDIFF(expirydate, NOW()) <= 45;
...
146 rows in set (0.00 sec)

[Dominic]

not showing domains already invoiced for the renewal of is a little trickier, but a worthwhile mod to this useful contribution

SELECT *
FROM tbldomains
WHERE DATEDIFF(expirydate, NOW()) <= 45
AND id NOT IN (SELECT relid FROM tblinvoiceitems t1 INNER JOIN tblinvoices t2 ON t1.invoiceid = t2.id WHERE t1.type = 'Domain' AND t2.status != 'Paid')

Briefly tested only but seems to do the job

[Dominic]

I'm not sure about returning within a certain date range (such as between 30 and 60 days). Maybe someone can post a modified version of the SQL to return domains within a specific range.

select * from tbldomains where DATEDIFF(expirydate, NOW()) BETWEEN 30 AND 60;

[RPS]

Dominic,

 

Thanks!

 

Here's the new script using Dominic's code for the date. I have added comments that set the range variable. So if you want to use a range between 30 and 60, uncomment the line that says

//$range = "BETWEEN 30 AND 60";

 

{php}
$display_domain=0;
$result = mysql_query("SELECT *  FROM tbldomains WHERE userid = ".$_SESSION['uid']." AND status = 'Active' LIMIT 1");
while($data = mysql_fetch_array($result))
{
   $display_domain=1;
}

if($display_domain){
{/php}
<p><strong>Upcoming Domain Renewals</strong></p>
<table align="center" style="width:90%" class="clientareatable" cellspacing="1">
<tr class="clientareatableheading"><td>Domain</td><td>Expiration Date</td><td></td></tr>
{php}
$i=1;
$range = "<= 30";
//$range = "<= 45";
//$range = "<= 60";
//$range = "BETWEEN 15 AND 60";
//$range = "BETWEEN 30 AND 60";
$result = mysql_query("SELECT * FROM tbldomains WHERE userid='".$_SESSION['uid']."' AND DATEDIFF(expirydate, NOW()) ".$range." AND status='Active' ORDER BY `expirydate` ASC ");
while($data = mysql_fetch_array($result))
{
   echo '<tr class="clientareatableactive"><td>'.$data['domain'].'</td><td>'.$data['expirydate'].'</td><td><form method="post" action="clientarea.php?action=domainrenew"><input name="domainid" value="'.$data['id'].'" type="hidden"><input value="Renew Domain" class="button" type="submit"></form></td></tr>';
   $i=0;
}

if($i)echo '<tr class="clientareatableactive"><td colspan="2">No upcoming domain renewals</td><td><a href="clientarea.php?action=domains">View Domains</a></td></tr>';
{/php}
</table>
{php}
}
{/php}

[ask21900]

`expirydate` date default NULL

 

mysql> select * from tbldomains where DATEDIFF(expirydate, NOW()) <= 45;
...
146 rows in set (0.00 sec)

 

I guess that is what I get for not looking at the DB... I was just going off the code that I originally modified...

[NH-Andrew]

Can we change the way the date of expiry is displayed? Currently it's in the form YYYY-MM-DD I'd prefer DD/MM/YYYY as that's how we have it in the rest of the client area.

 

Thanks.

 

No one?

[RPS]

This code will display the date correctly, as per the setting in WHMCS.

 

{php}
$display_domain=0;
$result = mysql_query("SELECT *  FROM tbldomains WHERE userid = ".$_SESSION['uid']." AND status = 'Active' LIMIT 1");
while($data = mysql_fetch_array($result))
{
   $display_domain=1;
}

if($display_domain){
{/php}
<p><strong>Upcoming Domain Renewals</strong></p>
<table align="center" style="width:90%" class="clientareatable" cellspacing="1">
<tr class="clientareatableheading"><td>Domain</td><td>Expiration Date</td><td></td></tr>
{php}
$i=1;
$range = "<= 30";
//$range = "<= 45";
//$range = "<= 60";
//$range = "BETWEEN 15 AND 60";
//$range = "BETWEEN 30 AND 60";
$result = mysql_query("SELECT * FROM tbldomains WHERE userid='".$_SESSION['uid']."' AND DATEDIFF(expirydate, NOW()) ".$range." AND status='Active' ORDER BY `expirydate` ASC ");
while($data = mysql_fetch_array($result))
{
   echo '<tr class="clientareatableactive"><td>'.$data['domain'].'</td><td>'.fromMySQLDate($data['expirydate']).'</td><td><form method="post" action="clientarea.php?action=domainrenew"><input name="domainid" value="'.$data['id'].'" type="hidden"><input value="Renew Domain" class="button" type="submit"></form></td></tr>';
   $i=0;
}

if($i)echo '<tr class="clientareatableactive"><td colspan="2">No upcoming domain renewals</td><td><a href="clientarea.php?action=domains">View Domains</a></td></tr>';
{/php}
</table>
{php}
}
{/php}

[HostRW]

Hello RPS an thank you for coming up with this great addition! Also thank you to the others that have contributed to it.

I have added this to my template files and it seems to be working great and it looks really good.

There is only one problem. Not sure if anyone thought of that... The text is in English only as it is not integrated into the language file. In other languages this box shows in English.

RPS, could you suggest some way to add this into the language file? Not sure if that would have to be done in cooperation with Matt though to avoid later problems...

[RPS]

I think it is better leaving it out of the language file. If enough people use this, Matt will simply include it into WHMCS.

 

You may want to submit a ticket into WHMCS and ask them how to add a language variable to a page on your template. Don't specifically mention this mod, just ask in general.

[ask21900]

All you have to do is add another key->value to the array, and call it

[NH-Andrew]

This code will display the date correctly, as per the setting in WHMCS.

 

{php}
$display_domain=0;
$result = mysql_query("SELECT *  FROM tbldomains WHERE userid = ".$_SESSION['uid']." AND status = 'Active' LIMIT 1");
while($data = mysql_fetch_array($result))
{
   $display_domain=1;
}

if($display_domain){
{/php}
<p><strong>Upcoming Domain Renewals</strong></p>
<table align="center" style="width:90%" class="clientareatable" cellspacing="1">
<tr class="clientareatableheading"><td>Domain</td><td>Expiration Date</td><td></td></tr>
{php}
$i=1;
$range = "<= 30";
//$range = "<= 45";
//$range = "<= 60";
//$range = "BETWEEN 15 AND 60";
//$range = "BETWEEN 30 AND 60";
$result = mysql_query("SELECT * FROM tbldomains WHERE userid='".$_SESSION['uid']."' AND DATEDIFF(expirydate, NOW()) ".$range." AND status='Active' ORDER BY `expirydate` ASC ");
while($data = mysql_fetch_array($result))
{
   echo '<tr class="clientareatableactive"><td>'.$data['domain'].'</td><td>'.fromMySQLDate($data['expirydate']).'</td><td><form method="post" action="clientarea.php?action=domainrenew"><input name="domainid" value="'.$data['id'].'" type="hidden"><input value="Renew Domain" class="button" type="submit"></form></td></tr>';
   $i=0;
}

if($i)echo '<tr class="clientareatableactive"><td colspan="2">No upcoming domain renewals</td><td><a href="clientarea.php?action=domains">View Domains</a></td></tr>';
{/php}
</table>
{php}
}
{/php}

 

Wonderful, thank you very much!

[vinbase]

Beautiful script! Thank You

[rldev]

Here's the problem..

 

When clients receive an e-mail to renew their domain (ours is set to e-mail 45 days prior to expiration), the link in the e-mail takes the client directly to the client area.

 

The client then needs to navigate to the domain renewal button by clicking "My Domains" -> "View Details" next to the domain name -> Scroll down to bottom and click "Renew Domain"

 

Pretty easy for someone who knows where they are looking, but incredibly frustrating for a client that barely knows how to login.

 

The following code will display on the Client Area homepage, right below where the unpaid invoice section appears.

 

Open:

whmcs/templates/default/clientareahome.tpl

whmcs/templates/portal/clientareahome.tpl

 

At the end of the file, add:

 

<p><strong>Upcoming Domain Renewals</strong></p>
<table align="center" style="width:90%" class="clientareatable" cellspacing="1">
<tr class="clientareatableheading"><td>Domain</td><td>Expiration Date</td><td></td></tr>
{php}
$year=date("Y");
$month=date("m")+2;
$day=date("d");
if($month>="12"){$month="02";$year+=1;}
if($month!="12"){$month="0".$month;}
$date=$year."-".$month."-".$day;

$i=1;
$result = mysql_query("SELECT * FROM tbldomains WHERE userid='".$_SESSION['uid']."' AND expirydate<'".$date."' AND status='Active' ORDER BY `expirydate` ASC ");
while($data = mysql_fetch_array($result))
{
echo '<tr class="clientareatableactive"><td>'.$data['domain'].'</td><td>'.$data['expirydate'].'</td><td><form method="post" action="clientarea.php?action=domainrenew"><input name="domainid" value="'.$data['id'].'" type="hidden"><input value="Renew Domain" class="button" type="submit"></form></td></tr>';
$i=0;
}
{/php}
{php}
if($i)echo '<tr class="clientareatableactive"><td colspan="2">No upcoming domain renewals</td><td><a href="clientarea.php?action=domains">View Domains</a></td></tr>';
{/php}
</table>

 

The script will return all domains that are due to expire within 2 months. Now when a client follows the link in their domain renewal email notice, they will be presented with a page that contains all of the domains that they can renew.

Is this based on the domain expiration date or next due date?

[RPS]

Uses the expiry date.

[rldev]

It is a great addition. Thank you.

[iPwebGroup]

Thank you very much for your efforts. This mod is a great addition and much appreciated.

Best regards to all.

Andreas

[scarson]

Thanks for the fabulous contribution. What a life saver!

[pooprscooper]

Thank you RPS.