Admin Home Page Widget for Upcoming Hosting Renewals v6 template

[championc]

Sorry to revive an old topic.  I've implemented this and it's absolutely brilliant.  I modified it to simply show the User, Billing Cycle, Next Due Date and the Amount, just to give myself a brief overview of the expected income in the weeks ahead.

Would it be difficult to create a similar SQL Query to list the email sent over the past week or so ?  Sometimes it's good to have visibility on what reminders have been sent.  Is there a KB article on the Database Structure that would help me to formulate my own SQL Query ?

[brian!]

20 minutes ago, championc said:

Would it be difficult to create a similar SQL Query to list the email sent over the past week or so ? 

no - for system emails, it's just a basic query to tblemails, reverse sort by date, and give it two dates (or I suppose a period of time and use today and today minus that given period, e.g week) - it's basically what the Email System Log currently does - except you can't filter that by date; for domain reminders emails dates, they're stored in tbldomainreminders.

24 minutes ago, championc said:

Sometimes it's good to have visibility on what reminders have been sent. 

you can see which domain reminders have been sent, for any given period, by running the "Domain Renewal Reminder Emails" report.

33 minutes ago, championc said:

Is there a KB article on the Database Structure that would help me to formulate my own SQL Query ?

detailed documentation with WHMCS... I fear not. .. the above report isn't encrypted, so that might give you some clue to the reminder query...  last year I posted a homepage panel hook to show a client's email history to them and I posted the basic Capsule query...

in your case, you don't need the first where (as you want all clients) and you just need to add a date range... many ways to do that, e.g whereBetween using 2 given dates; you could use Carbon to generate the date values etc...

$clientemails = Capsule::table('tblemails')->select('id','subject','date')->orderBy('date','desc')->limit(100)->get();

the above would give you the last 100 emails sent by the system.... won't include domain reminders (which never even occurred to me when writing the above panel, so i'll fix that one day).

that gives you an array, then it's just a case of looping through it to output it...

[championc]

That's perfect, thanks a million @brian!

I don't use the system for Domains so I can happily work with what you have provided to me.

[Mazhosting]

I have added the code, but I can't find any access to the widget... does the code still work?

[Steffan Cline]

This widget is amazing however it needs to be updated for the latest version. The code gets modified by the JS.

<div id="panelupcomingrenewals<spanstyle='font-size:0.8em;'>(next365days)</span>" data-widget="upcomingrenewals<spanstyle='font-size:0.8em;'>(next365days)</span>" class="dashboard-panel-item dashboard-panel-item-columns-1" style="position: absolute; left: 0%; top: 1293.02px;">
                            <div class="panel panel-default widget-upcomingrenewals<spanstyle='font-size:0.8em;'>(next365days)</span>" data-widget="upcomingrenewals<spanstyle='font-size:0.8em;'>(next365days)</span>">
                    <div class="panel-heading">
                        <div class="widget-tools">
                            <a href="/admin/#" class="widget-refresh"><i class="fas fa-sync"></i></a>
                            <a href="/admin/#" class="widget-minimise"><i class="fas fa-chevron-up"></i></a>
                            <a href="/admin/#" class="widget-hide"><i class="fas fa-times"></i></a>
                        </div>
                        <h3 class="panel-title">Upcoming Renewals <span style="font-size:0.8em;">(Next 365 Days)</span></h3>
                    </div>
                    <div class="panel-body">
            
            
<div id="upcoming-renewals">
   <ul id="tabs" class="nav nav-tabs" data-tabs="tabs">
       <li class="active"><a href="/admin/#services" data-toggle="tab">Products/Services</a></li>
       <li><a href="/admin/#domains" data-toggle="tab">Domains</a></li>
       <li><a href="/admin/#addons" data-toggle="tab">Product Addons</a></li>
   </ul>
   <div class="tab-content" style="padding-top:5px;">
       <div class="tab-pane active" id="services">
           <table class="table table-condensed" style="font-size:0.9em;">
               <tbody><tr bgcolor="#efefef" style="text-align:left;font-weight:bold;">
                   <td>Domain</td>
                   <td>Product/Service</td>
                   <td style="text-align:right;">Next Due Date</td>
                   <td style="text-align:right;">Amount</td>
               </tr>
           </tbody></table>
       </div>
       <div class="tab-pane" id="domains">
           <table class="table table-condensed" style="font-size:0.9em;">
               <tbody><tr bgcolor="#efefef" style="text-align:left;font-weight:bold;">
                   <td>Domain</td>
                   <td style="text-align:right;">Next Due Date</td>
                   <td style="text-align:right;">Expiry Date</td>
                   <td style="text-align:right;">Amount</td>
               </tr>
               <tr style="text-align:center;">
                   <td colspan="4">No Upcoming Domains</td>
               </tr>
           </tbody></table>
       </div>
       <div class="tab-pane" id="addons">
           <table class="table table-condensed" style="font-size:0.9em;">
               <tbody><tr bgcolor="#efefef" style="text-align:left;font-weight:bold;">
                   <td>Domain</td>
                   <td>Product/Service</td>
                   <td>Addon</td>
                   <td style="text-align:right;">Next Due Date</td>
                   <td style="text-align:right;">Amount</td>
               </tr>
               <tr style="text-align:center;">
                   <td colspan="5">No Upcoming Product Addons</td>
               </tr>
           </tbody></table>
       </div>
   </div>
</div>

                                </div>
                </div>
                    </div>