Sparky's Admin Upcoming Domain Renewals V4 template

sparky · May 20, 2009

@cmo: The code you PM'ed me is correct. The dates that you see are the expiry date and the next due date that you have set in whmcs for that particular domain.

The SQL query reads the expirydate and compares it to todays date and works out how many days is between those 2 dates. if that number is equal to or less than the number you have in $range then it will display.

cmo · May 20, 2009

I set for 5 days and it shows from May 2 to May 25.

So, it seems that the domains not renewed keep on showing up till action is taken.

sparky · May 20, 2009

You set it for the next 5 days so it will show anything that will expire within the next 5 days as well as anything that has already expired.

If it has already expired and it wasn't renewed then you should have set the status of that domain to Expired. If you do that then it won't show up in the list.

cmo · May 20, 2009

You set it for the next 5 days so it will show anything that will expire within the next 5 days as well as anything that has already expired.
If it has already expired and it wasn't renewed then you should have set the status of that domain to Expired. If you do that then it won't show up in the list.

That's it, thanks!

djpete · June 2, 2009

This doesn't seem to work for me with the original theme.

Any tips?

I've really been missing these 2 summaries as they used to be in PHPCOIN and were very handy.

djpete · June 2, 2009

bugger it.

For these 2 mods I have gone across to v4 theme.

Awesome Sparky.

THANKS!

djpete · June 4, 2009

Sparky.

Is there any chance you can do a similar script for Upcoming Products/Services?

You see I have a lot of Maintenance contracts for 12 mths like my hosting.

This would then bring them both up.

I am fiddling myself but so far no success.

djpete · June 4, 2009

I think it all hinges on changing this $result = mysql_query("SELECT * FROM `tblhosting

And then adjusting the columns and column titles

sparky · June 4, 2009

Sparky.
Is there any chance you can do a similar script for Upcoming Products/Services?

You see I have a lot of Maintenance contracts for 12 mths like my hosting.

This would then bring them both up.

I am fiddling myself but so far no success.

See post #12 in this thread

djpete · June 4, 2009

Thats just hosting though. :-(

Im after Products/Services.

Then it would show everything as in

Maintenance and Hosting for me.

djpete · June 5, 2009

Really what I would like is a summary of everything for next 90 days on index page.

Something like this:

ID Domain Client Name Product/Service Price Billing Cycle Next Due Date

If it was possible to include everything as in

domains

Products/services

Maybe even billable items

In other words a quick snapshot of expiries coming up.

I would pay for this if that helps.

othellotech · June 5, 2009

Thats just hosting though. :-(
Im after Products/Services.

All products/services are in the tblhosting file

djpete · June 5, 2009

Maybe I will need to play around this area then to get not just hosting show, but products and services too...

echo '<tr bgcolor="#ffffff" style="text-align:center;"><td><a href="clientshosting.php?userid='.$data['userid'].'&id='.$data['id'].'">'.$data['domain'].'</a></td><td>'.$data['billingcycle'].'</td><td>'.$data['paymentmethod'].'</td><td>'.fromMySQLDate($data['nextduedate']).'</td><td>'.formatCurrency($data['amount']).'</td></tr>'; $i=0;

sparky · June 5, 2009

Maybe I will need to play around this area then to get not just hosting show, but products and services too...

echo '<tr bgcolor="#ffffff" style="text-align:center;"><td><a href="clientshosting.php?userid='.$data['userid'].'&id='.$data['id'].'">'.$data['domain'].'</a></td><td>'.$data['billingcycle'].'</td><td>'.$data['paymentmethod'].'</td><td>'.fromMySQLDate($data['nextduedate']).'</td><td>'.formatCurrency($data['amount']).'</td></tr>'; $i=0;

For what you want to do it needs to look up tblhosting and tblproducts to get the package names

djpete · June 5, 2009

Ihave altered to this but I still dont get any products/services due

<!-- START ADMIN UPCOMING PRODUCTS/SERVICES RENEWALS -->

<h3 align="center">Upcoming Products/Services Renewals - Next 90 Days</h3>

<table bgcolor="#cccccc" align="center" style="margin-bottom:5px;width:100%;" cellspacing="1">
<tr bgcolor="#efefef" style="text-align:center;font-weight:bold;"><td>Products/Services</td><td>Billing Cycle</td><td>Payment Method</td><td>Next Due Date</td><td>Amount</td></tr>
{php}
   $i=1; $range = "<= 90";
   $result = mysql_query("SELECT * FROM `tblproducts` WHERE DATEDIFF(`nextduedate`, Now()) $range AND `server` > 0 ORDER BY `nextduedate` ASC");
   while ($data = @mysql_fetch_array ($result)) {
       echo '<tr bgcolor="#ffffff" style="text-align:center;"><td><a href="clientshosting.php?userid='.$data['userid'].'&id='.$data['id'].'">'.$data['products'].'</a></td><td>'.$data['billingcycle'].'</td><td>'.$data['paymentmethod'].'</td><td>'.fromMySQLDate($data['nextduedate']).'</td><td>'.formatCurrency($data['amount']).'</td></tr>'; $i=0;
   }
   if($i) echo '<tr bgcolor="#ffffff" style="text-align:center;"><td colspan="7">No upcoming producs/services renewals</td></tr>';
{/php}
</table>
<br />
<!-- END UPCOMING PRODUCTS/SERVICES RENEWALS -->

sparky · June 6, 2009

Ihave altered to this but I still dont get any products/services due

Sorry to say but no where near it

I have not tested this yet but it should do what you need

<!-- START ADMIN UPCOMING PRODUCTS/SERVICES RENEWALS -->

<h3 align="center">Upcoming Products/Services Renewals - Next 90 Days</h3>

<table bgcolor="#cccccc" align="center" style="margin-bottom:5px;width:100%;" cellspacing="1">
<tr bgcolor="#efefef" style="text-align:center;font-weight:bold;"><td>Product/Service</td><td>Billing Cycle</td><td>Payment Method</td><td>Next Due Date</td><td>Amount</td></tr>
{php}
   $i=1; $range = "<= 90";
   $result = mysql_query("SELECT tblhosting.*,tblproducts.name FROM `tblhosting`,`tblproducts` WHERE DATEDIFF(tblhosting.nextduedate, Now()) $range AND (tblhosting.domainstatus = 'Active' OR tblhosting.domainstatus = 'Suspended') AND tblhosting.packageid = tblproducts.id ORDER BY tblhosting.nextduedate ASC");
   while ($data = @mysql_fetch_array ($result)) {
       echo '<tr bgcolor="#ffffff" style="text-align:center;"><td><a href="clientshosting.php?userid='.$data['userid'].'&id='.$data['id'].'">'.$data['name'].'</a></td><td>'.$data['billingcycle'].'</td><td>'.$data['paymentmethod'].'</td><td>'.fromMySQLDate($data['nextduedate']).'</td><td>'.formatCurrency($data['amount']).'</td></tr>'; $i=0;
   }
   if($i) echo '<tr bgcolor="#ffffff" style="text-align:center;"><td colspan="7">No upcoming producs/services renewals</td></tr>';
{/php}
</table>
<br />
<!-- END UPCOMING PRODUCTS/SERVICES RENEWALS --

djpete · June 6, 2009

ooooooooooooooooooooh yeah.

Now if it could have company name field?

And also can domains be added also so everything is all in the one table?

A small donation will be coming your way Sparky (Done).

Edited June 6, 2009 by djpete

sparky · June 6, 2009

ooooooooooooooooooooh yeah.
Now if it could have company name field?

And also can domains be added also so everything is all in the one table?

A small donation will be coming your way Sparky (Done).

Added the user ID only as some names may make the field too long to fit into the table

Also... Thank you

<!-- START ADMIN UPCOMING PRODUCTS/SERVICES RENEWALS -->

<h3 align="center">Upcoming Products/Services Renewals - Next 90 Days</h3>

<table bgcolor="#cccccc" align="center" style="margin-bottom:5px;width:100%;" cellspacing="1">
<tr bgcolor="#efefef" style="text-align:center;font-weight:bold;"><td>User ID</td><td>Product/Service</td><td>Billing Cycle</td><td>Payment Method</td><td>Next Due Date</td><td>Amount</td></tr>
{php}
   $i=1; $range = "<= 90";
   $result = mysql_query("SELECT tblhosting.*,tblproducts.name FROM `tblhosting`,`tblproducts` WHERE DATEDIFF(tblhosting.nextduedate, Now()) $range AND (tblhosting.domainstatus = 'Active' OR tblhosting.domainstatus = 'Suspended') AND tblhosting.packageid = tblproducts.id ORDER BY tblhosting.nextduedate ASC");
   while ($data = @mysql_fetch_array ($result)) {
       echo '<tr bgcolor="#ffffff" style="text-align:center;"><td><a href="clientssummary.php?userid='.$data['userid'].'">'.$data['userid'].'</a></td><td><a href="clientshosting.php?userid='.$data['userid'].'&id='.$data['id'].'">'.$data['name'].'</a></td><td>'.$data['billingcycle'].'</td><td>'.$data['paymentmethod'].'</td><td>'.fromMySQLDate($data['nextduedate']).'</td><td>'.formatCurrency($data['amount']).'</td></tr>'; $i=0;
   }
   if($i) echo '<tr bgcolor="#ffffff" style="text-align:center;"><td colspan="7">No upcoming producs/services renewals</td></tr>';
{/php}
</table>
<br />
<!-- END UPCOMING PRODUCTS/SERVICES RENEWALS --

djpete · June 6, 2009

Payment method and/or billing cycle can be dropped if needed.

Really need company name.

And any way to add domains?

Maybe headings like this:

Company - Product/Service/Domain - Billing Cycle - Next Due Date - Amount

Impact-John · June 6, 2009

removed cause i didnt read the final post lol @ myself

djpete · June 6, 2009

I managed to get this.

Looks great.

Just wish there was a way to also have domains in the listing.

My life would then be complete.

lol

<!-- START ADMIN UPCOMING PRODUCTS/SERVICES RENEWALS -->

<h3 align="center">Upcoming Products/Services Renewals - Next 90 Days</h3>

<table bgcolor="#cccccc" align="center" style="margin-bottom:5px;width:100%;" cellspacing="1">
<tr bgcolor="#efefef" style="text-align:center;font-weight:bold;"><td>Domain</td><td>Product/Service</td><td>Billing Cycle</td><td>Payment Method</td><td>Next Due Date</td><td>Amount</td></tr>
{php}
   $i=1; $range = "<= 90";
   $result = mysql_query("SELECT tblhosting.*,tblproducts.name FROM `tblhosting`,`tblproducts` WHERE DATEDIFF(tblhosting.nextduedate, Now()) $range AND (tblhosting.domainstatus = 'Active' OR tblhosting.domainstatus = 'Suspended') AND tblhosting.packageid = tblproducts.id ORDER BY tblhosting.nextduedate ASC");
   while ($data = @mysql_fetch_array ($result)) {
       echo '<tr bgcolor="#ffffff" style="text-align:center;"><td><a href="clientssummary.php?userid='.$data['userid'].'">'.$data['domain'].'</a></td><td><a href="clientshosting.php?userid='.$data['userid'].'&id='.$data['id'].'">'.$data['name'].'</a></td><td>'.$data['billingcycle'].'</td><td>'.$data['paymentmethod'].'</td><td>'.fromMySQLDate($data['nextduedate']).'</td><td>'.formatCurrency($data['amount']).'</td></tr>'; $i=0;
   }
   if($i) echo '<tr bgcolor="#ffffff" style="text-align:center;"><td colspan="7">No upcoming producs/services renewals</td></tr>';
{/php}
</table>
<br />
<!-- END UPCOMING PRODUCTS/SERVICES RENEWALS -->

djpete · June 6, 2009

This is as near as I can get to what I want.

Now I can see everything that is upcoming in one (well 2 really) easy table!

Thanks again Sparky. I think it is not possible to merge domains in the same table because the fields are so different.?

<!-- START ADMIN UPCOMING DOMAINS/PRODUCTS/SERVICES RENEWALS -->

<h3 align="center">Upcoming Domains/Products/Services Renewals - Next 90 Days</h3>

<table bgcolor="#cccccc" align="center" style="margin-bottom:5px;width:100%;" cellspacing="1">
<tr bgcolor="#efefef" style="text-align:center;font-weight:bold;"><td>Domain</td><td>Registrar</td><td>Period</td><td>Next Due Date</td><td>Expiration Date</td><td>Amount</td><td>Do Not<br />Renew</td></tr>
{php}
   $x=1; $range = "<= 90";
   $result = mysql_query("SELECT * FROM `tbldomains` WHERE DATEDIFF(`expirydate`, Now()) $range AND `status` = 'Active' ORDER BY `expirydate` ASC");
   while ($data = @mysql_fetch_array ($result)) {
       echo '<tr bgcolor="#ffffff" style="text-align:center;"><td><a href="clientsdomains.php?userid='.$data['userid'].'&id='.$data['id'].'">'.$data['domain'].'</a></td><td>'.$data['registrar'].'</td><td>'.$data['registrationperiod'].'</td><td>'.fromMySQLDate($data['nextduedate']).'</td><td>'.fromMySQLDate($data['expirydate']).'</td><td>'.formatCurrency($data['recurringamount']).'</td><td>'.$data['donotrenew'].'</td></tr>'; $x=0;
   }
   if($x) echo '<tr bgcolor="#ffffff" style="text-align:center;"><td colspan="7">No upcoming domain renewals</td></tr>';
{/php}
</table>
<br />
<table bgcolor="#cccccc" align="center" style="margin-bottom:5px;width:100%;" cellspacing="1">
<tr bgcolor="#efefef" style="text-align:center;font-weight:bold;"><td>Domain</td><td>Product/Service</td><td>Billing Cycle</td><td>Payment Method</td><td>Next Due Date</td><td>Amount</td></tr>
{php}
   $i=1; $range = "<= 90";
   $result = mysql_query("SELECT tblhosting.*,tblproducts.name FROM `tblhosting`,`tblproducts` WHERE DATEDIFF(tblhosting.nextduedate, Now()) $range AND (tblhosting.domainstatus = 'Active' OR tblhosting.domainstatus = 'Suspended') AND tblhosting.packageid = tblproducts.id ORDER BY tblhosting.nextduedate ASC");
   while ($data = @mysql_fetch_array ($result)) {
       echo '<tr bgcolor="#ffffff" style="text-align:center;"><td><a href="clientssummary.php?userid='.$data['userid'].'">'.$data['domain'].'</a></td><td><a href="clientshosting.php?userid='.$data['userid'].'&id='.$data['id'].'">'.$data['name'].'</a></td><td>'.$data['billingcycle'].'</td><td>'.$data['paymentmethod'].'</td><td>'.fromMySQLDate($data['nextduedate']).'</td><td>'.formatCurrency($data['amount']).'</td></tr>'; $i=0;
   }
   if($i) echo '<tr bgcolor="#ffffff" style="text-align:center;"><td colspan="7">No upcoming producs/services renewals</td></tr>';
{/php}
</table>
<br />
<!-- END UPCOMING DOMAINS/PRODUCTS/SERVICES RENEWALS -->

thehost5968 · June 24, 2009

Hi

ref: "Added the user ID only as some names may make the field too long to fit into the table"

why not do a hover over on the user ID to show the domain Name?

arbet · July 8, 2009

Show Upcoming Domain Renewals on the Admin V4 homepage that will expire in the next 90 days.

Change the number in $range = "<= 90"; to the number of days before expiry that you want to display.

Open admin/templates/v4/homepage.tpl

Find:

<div class="errorbox" style="font-size:14px;"><a href="supporttickets.php">{$sidebarstats.tickets.awaitingreply} Ticket(s) Awaiting Reply</a> || <a href="cancelrequests.php">{$stats.cancellations.pending} Pending Cancellation(s)</a> || <a href="todolist.php">{$stats.todoitems.due} To-Do Item(s) Due</a> || <a href="networkissues.php">{$stats.networkissues.open} Open Network Issue(s)</a></div>

<br />

Underneath it insert this code:

<!-- START ADMIN UPCOMING DOMAIN RENEWALS -->

<h3 align="center">Upcoming Domain Renewals</h3>

<table bgcolor="#cccccc" align="center" style="margin-bottom:5px;width:100%;" cellspacing="1">
<tr bgcolor="#efefef" style="text-align:center;font-weight:bold;"><td>Domain</td><td>Registrar</td><td>Period</td><td>Next Due Date</td><td>Expiration Date</td><td>Amount</td><td>Do Not<br />Renew</td></tr>
{php}
   $x=1; $range = "<= 90";
   $result = mysql_query("SELECT * FROM `tbldomains` WHERE DATEDIFF(`expirydate`, Now()) $range AND `status` = 'Active' ORDER BY `expirydate` ASC");
   while ($data = @mysql_fetch_array ($result)) {
       echo '<tr bgcolor="#ffffff" style="text-align:center;"><td><a href="clientsdomains.php?userid='.$data['userid'].'&id='.$data['id'].'">'.$data['domain'].'</a></td><td>'.$data['registrar'].'</td><td>'.$data['registrationperiod'].'</td><td>'.fromMySQLDate($data['nextduedate']).'</td><td>'.fromMySQLDate($data['expirydate']).'</td><td>'.formatCurrency($data['recurringamount']).'</td><td>'.$data['donotrenew'].'</td></tr>'; $x=0;
   }
   if($x) echo '<tr bgcolor="#ffffff" style="text-align:center;"><td colspan="7">No upcoming domain renewals</td></tr>';
{/php}
</table>
<br />
<!-- END ADMIN UPCOMING DOMAIN RENEWALS -->

Thanks sparky! You saved the day!

Lawrence · July 8, 2009

Hi guys,

Just wanted to point out there is one bug on this. The price is not in the proper currency. This is because there is a variable that needs to be declared prior to using the formatCurrency() function which is not present here. I will check it out and post the corrected code later.

Sparky's Admin Upcoming Domain Renewals V4 template

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Posted Images

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Recently Browsing 0 members

Important Information