Sparky's Admin Upcoming Domain Renewals V4 template

Impact-John · July 9, 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 -->

Very Nice but why is the clients name or at least id number not listed to the left of the domain address

Impact-John · July 9, 2009

i can get the username to show up by changing $data['domain'] to $data['username'] but i cant get it to display client name is it possible at all . ????

Lawrence · July 9, 2009

Here is the one with the fixed currency support:

<!-- 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");



global $currency;

   while ($data = @mysql_fetch_array ($result)) {
   $domainid = $data['id'];
   # get currency info using the client of this domain name
   # populate global $currency array for formatCurrency function

$sql_defcurrency2 = "SELECT tblcurrencies.code, tblcurrencies.prefix, tblcurrencies.suffix, tblcurrencies.format FROM `tbldomains`, tblclients, tblcurrencies WHERE tbldomains.userid = tblclients.id AND tblcurrencies.id = tblclients.currency AND tbldomains.id = '$domainid' LIMIT 1";
$result_defcurrency2 = mysql_query($sql_defcurrency2);
$currency_code = mysql_result($result_defcurrency2,0,'code');
$currency_prefix = mysql_result($result_defcurrency2,0,'prefix');
$currency_suffix = mysql_result($result_defcurrency2,0,'suffix');
$currency_format = mysql_result($result_defcurrency2,0,'format');
mysql_free_result($result_defcurrency2);

$currency = array("code"=>"$currency_code","prefix"=>"$currency_prefix","suffix"=>"$currency_suffix","format"=>"$currency_format");


       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 -->

othellotech · July 10, 2009

i can get the username to show up by changing $data['domain'] to $data['username'] but i cant get it to display client name is it possible at all . ????

query the tblclients table using the userid column ...

dkent · July 18, 2009

Why does it show up as pricing in Euro?

BAJI26 · July 18, 2009

Why does it show up as pricing in Euro?

Mybe you have you currency default set to Euros! Dunno just guessing.

dkent · July 19, 2009

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

Awesome Sparky.

THANKS!

You can use the same coding for any version of the Admin area, it works perfectly. Actually it looks nicer in the original version, of which I still use. Works for me :lol:

dkent · July 20, 2009

Mybe you have you currency default set to Euros! Dunno just guessing.

Erm no, my default is Krypton Dollar

I won't show the correct currency. I have people that pay in dollar and pound, but the pricing shows up as euro.

Is there a fix?

Lawrence · July 27, 2009

Why does it show up as pricing in Euro?

Because sparky's code did not have the required code for the currencies to be displayed properly. My posted code fixes that.

sparky · July 27, 2009

That is right, the original did not have the code for different currencies as I have multi currency enabled but only show my default currency in admin by forcing the conversion back to my default currency on cart checkout.

Here is an updated version that will display the currency correctly.

<!-- START ADMIN UPCOMING DOMAIN RENEWALS -->
<h3 align="center">Upcoming Domain 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>Payment Method</td><td>Amount</td><td>Do Not<br />Renew</td></tr>
{php}
   $i=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)) {
       $currency=getCurrency($data['userid']);
       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>'.$data['paymentmethod'].'</td><td>'.formatCurrency($data['recurringamount']).'</td><td>'.$data['donotrenew'].'</td></tr>'; $i=0;
   }
   if($i) echo '<tr bgcolor="#ffffff" style="text-align:center;"><td colspan="7">No upcoming domain renewals</td></tr>';
{/php}
</table>
<br />
<!-- END UPCOMING DOMAIN RENEWALS -->

sparky · July 27, 2009

Sorry guys... forgot to add the global in as mine is set at the top of my template.

Use this one

<!-- START ADMIN UPCOMING DOMAIN RENEWALS -->
<h3 align="center">Upcoming Domain 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>Payment Method</td><td>Amount</td><td>Do Not<br />Renew</td></tr>
{php}
   global $currency;$i=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)) {
       $currency=getCurrency($data['userid']);
       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>'.$data['paymentmethod'].'</td><td>'.formatCurrency($data['recurringamount']).'</td><td>'.$data['donotrenew'].'</td></tr>'; $i=0;
   }
   if($i) echo '<tr bgcolor="#ffffff" style="text-align:center;"><td colspan="7">No upcoming domain renewals</td></tr>';
{/php}
</table>
<br />
<!-- END UPCOMING DOMAIN RENEWALS -->

sparky · July 27, 2009

Here is also the products and services one with the currency fix

<!-- 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>Company/User Name</td><td>Product/Service</td><td>Billing Cycle</td><td>Payment Method</td><td>Next Due Date</td><td>Amount</td></tr>
{php}
   global $currency;$i=1; $range = "<= 90";
   $result = mysql_query("SELECT tblhosting.*,tblproducts.name,tblclients.companyname,tblclients.firstname,tblclients.lastname FROM `tblhosting`,`tblproducts`,`tblclients` WHERE DATEDIFF(tblhosting.nextduedate, Now()) $range AND (tblhosting.domainstatus = 'Active' OR tblhosting.domainstatus = 'Suspended') AND tblhosting.packageid = tblproducts.id AND tblhosting.userid = tblclients.id ORDER BY tblhosting.nextduedate ASC");
   while ($data = @mysql_fetch_array ($result)) {
       $usersname = $data['firstname']. ' ' .$data['lastname'];
       $currency=getCurrency($data['userid']);
       echo '<tr bgcolor="#ffffff" style="text-align:center;"><td><a href="clientssummary.php?userid='.$data['userid'].'">'.(($data['companyname'])?$data['companyname']:$usersname).'</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 -->

dkent · August 5, 2009

Hey Sparky, are you sure that works? I just added it and it's still showing up in Euro, when my default currency is KPT (Krypton US Dollar). Thanks.

sparky · August 5, 2009

It doesnt show your default currency...

It shows the currency that is set for that users account.

EDIT: You may want to check your default currency that you have set. I can't find "KPT" in this list.

http://www.xe.com/iso4217.php

or from here which is where whmcs get the currency prices from

http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml

Edited August 5, 2009 by sparky

dkent · August 6, 2009

Haha, KPT was just a joke lol, a made up currency.

You say it shows the currency that is set in the users account, but it doesn't. It's only showing up in Euro, even when the clients account is set in US Dollar.

sparky · August 6, 2009

Haha, KPT was just a joke lol, a made up currency.

You say it shows the currency that is set in the users account, but it doesn't. It's only showing up in Euro, even when the clients account is set in US Dollar.

Open a ticket on my site to provide some login details and I'll take a look at it for you.

dkent · August 6, 2009

But I don't know you from Adam

It's to do with the piece of code you gave me. I did add it in the correct area and did not edit the code myself.

sparky · August 6, 2009

But I don't know you from Adam

It's to do with the piece of code you gave me. I did add it in the correct area and did not edit the code myself.

No Problem... I offered help... you declined, your choice, fair enough. Offer retracted. The code was not given to you directly. If you can't get it to work remove it and move on, Simple.

Something to think about

It does seem strange that you are the only one with that problem...

dkent · August 6, 2009

No need to be bitter about it, Sparky. If that's how you're going to be, I will cancel the orders I were about to make through your website for web design, upgrades and customizations, but I guess I'll take my business elsewhere.

Kinder · August 6, 2009

No need to be bitter about it, Sparky. If that's how you're going to be, I will cancel the orders I were about to make through your website for web design, upgrades and customizations, but I guess I'll take my business elsewhere.

LOL dude get a life.

How many times do you think Sparky or anyone has heard that story?

QOUTE: If that's how you're going to be, I will cancel the orders I were about to make through your website for web design, upgrades and customizations.

I see that Sparky has been trying to help you, but just look at your comments. It is no wonder you will never get respect or help on these forums.

Everytime i see you post, it is something nobody wants to hear.

Thanks

dkent · August 7, 2009

@Kinder - Exactly what was wrong with my comment?

"But I don't know you from Adam It's to do with the piece of code you gave me. I did add it in the correct area and did not edit the code myself."

As you can see... nothing is wrong with it, but then I receive...

"No Problem... I offered help... you declined, your choice, fair enough. Offer retracted. The code was not given to you directly. If you can't get it to work remove it and move on, Simple."

If you still think I was the one being bitter then I think you should see a doctor. Yes, Sparky is trying to help, but as soon as I was concerned about giving information out I got a load of agro. I wanted the addon working but it's obviously a waste of time so I'll leave it.

"How many times do you think Sparky or anyone has heard that story?" - I actually enquired about the services through his website before I posted a comment on here.

sparky · August 7, 2009

@dkent - Sorry if that offended you, I was not being bitter at all, or was not meant to be. I was simply stating a fact.

The reason I would need the login details is simply because nobody else has reported this problem at all so that tends to indicate that the script mod is fine. It would more than likely be something in your multicurrency setup that would be causing it and would be hard to put a finger directly on the problem until I looked at it. Sure being concerned about giving a login, I can understand your point but also I cant try to find the problem without it. Catch 22. Hence the answer, probably I should have explained it a little clearer, it was a bit blunt but thats me. Once again, sorry but if I can't get access to look I can't help. It is really that simple.

RE: the orders you have, sorry I have no idea which or what they are, (maybe you can update with a support ticket to let me know)

Currently I have only 3 very small mod orders(waiting for payment) and 9 module requests waiting in a que for me to get to them which hopefully will be tonight and this weekend.

I only do these mods as a hobby and is not my full time job.

wehostem · August 20, 2009

I know this is a stupid request since most of us isn't on MySQL 4, but datediff function doesn't exist in MySQL 4x and therefore this query will not work. Maybe someone should make this for MySQL 4 users out there.

If I do a query like:

SELECT * FROM tbldomains WHERE DATE_ADD(expirydate, INTERVAL 3 MONTH) >= NOW() AND status = 'Active' ORDER BY expirydate ASC

In phpmyadmin it will return the next 90 days of upcoming renewals for domains. Works fine for MySQL 4x, just like the original query works fine in MySQL 5x

However I can't get this version 4 query to work in Smarty no matter what I do, I just don't know enough about smarty to make it work. I'm guessing syntax is incorrect somewhere?

{php}
$x=1; 
$result = mysql_query("SELECT * FROM `tbldomains` WHERE DATE_ADD(`expirydate`, INTERVAL 3 MONTH) >= NOW() 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}

sparky · August 20, 2009

DATEDIFF() was added in MySQL 4.1.1.

Maybe you should be thinking of upgrading

wehostem · August 21, 2009

I have 4.0.xx and can't upgrade, not an option at this time. I have over 50 sites on that server all running a program I wrote over 6 years ago, all using 4.0 MySQL. All paying customers.

I may just end up moving it to another server eventually I have 13 of them, and out of that only 2 use the older MySQL, the rest use the newest.

It would of just been nice to see it work on the older 4.0.x mysql. I still don't understand why this was removed from WHMCS in the first place. Use to be in the old 3.x series, why remove it, it was a good tool to have. Oh well...

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