Jump to content

Kian

GearHead
  • Content Count

    1743
  • Joined

  • Last visited

  • Days Won

    57

Kian last won the day on May 25

Kian had the most liked content!

Community Reputation

261 Excellent

About Kian

Recent Profile Visitors

8705 profile views
  1. Got it. There was an unnecessary filter by date <= current date on "Terminated" products/domains. I updated the script. It should work now. Edit: I realized now that I was also counting "Free" products/services. I'm going to change the script so that they're not part of the report.
  2. Yes, DecryptPassword always returns the original password. On the other hand EncryptPassword always returns a different value. $results = localAPI('EncryptPassword', array('password2' => 'aaa')); echo "<pre>"; print_r($results); echo "</pre>"; $results = localAPI('DecryptPassword', array('password2' => $results['password'])); echo "<pre>"; print_r($results); echo "</pre>"; If you run the above script 3 times you'll get the following. 1st run Array ( [result] => success [password] => 56Caa8prWCL/Y5rAie/0bfOAlMDoYMk= ) Array ( [result] => success [password] => aaa ) 2nd run Array ( [result] => success [password] => 9z1gjm1s8mjLhVNzMoTh42QaAoj4/TM= ) Array ( [result] => success [password] => aaa ) 3rd run Array ( [result] => success [password] => xYg2vuJuYMdMcYShOPKO/4wKXrzKSGU= ) Array ( [result] => success [password] => aaa ) That's why I suspect @hogava is referring to EncryptPassword.
  3. Probably you are running the code on every page load.
  4. It was It was harder than I thought 🥶 I don't want to annoy you with details so let's get straight to the point. Can you confirm me that the following numbers are correct? Anyway you can also test it on your WHMCS. I updated the script on Github.
  5. I noticed more than one problem. The core of the issue is that I'm starting the year with a wrong calculation. I'll fix it as soon as I can.
  6. Done. Here's the preview (click to enlarge). There should be everything. Here is the code but since this community doesn't allow me to edit posts after X hours, you can also find it on Github. I'm still making small changes. I don't like color scheme 😑 <?php /** * Churn Rate * * @writtenby Kian * */ use WHMCS\Carbon; use WHMCS\Database\Capsule; if (!defined("WHMCS")) { die("This file cannot be accessed directly"); } $dateFilter = Carbon::create($year, $month, 1); $startOfMonth = $dateFilter->startOfMonth()->toDateTimeString(); $endOfMonth = $dateFilter->endOfMonth()->toDateTimeString(); $reportdata["title"] = 'Churn Rate for ' . $year; $reportdata["description"] = "Rate at which customers stop doing business with you."; $reportdata["yearspagination"] = true; $reportdata["tableheadings"] = array( 'Date', 'Products', '<strong class="text-success"><i class="far fa-plus-square"></i></strong>', '<strong class="text-danger"><i class="far fa-minus-square"></i></strong>', '<strong><i class="fas fa-percentage"></i></strong>', 'Domains', '<strong class="text-success"><i class="far fa-plus-square"></i></strong>', '<strong class="text-danger"><i class="far fa-minus-square"></i></strong>', '<strong><i class="fas fa-percentage"></i></strong>', 'Overall', '<strong class="text-success"><i class="far fa-plus-square"></i></strong>', '<strong class="text-danger"><i class="far fa-minus-square"></i></strong>', '<strong><i class="fas fa-percentage"></i></strong>', ); $reportvalues = array(); // Products/Services $groupBy = Capsule::raw('date_format(`regdate`, "%Y-%c")'); $reportvalues['productsNew'] = Capsule::table('tblhosting')->whereYear('regdate', '=', $year)->where('domainstatus', 'Active')->groupBy($groupBy)->orderBy('regdate')->pluck(Capsule::raw('count(id) as total'), Capsule::raw('date_format(`regdate`, "%Y-%c") as month')); $groupBy = Capsule::raw('date_format(`nextduedate`, "%Y-%c")'); $reportvalues['productsTerminated'] = Capsule::table('tblhosting')->whereYear('nextduedate', '=', $year)->where('nextduedate', '<=', $dateFilter->format('Y-m-d'))->whereNotIn('billingcycle', ['One Time', 'Completed'])->groupBy($groupBy)->orderBy('nextduedate')->pluck(Capsule::raw('count(id) as total'), Capsule::raw('date_format(`nextduedate`, "%Y-%c") as month')); $activeProducts = Capsule::table('tblhosting')->where('domainstatus', 'Active')->pluck(Capsule::raw('count(id) as total'))[0]; // Domains $groupBy = Capsule::raw('date_format(`registrationdate`, "%Y-%c")'); $reportvalues['domainsNew'] = Capsule::table('tbldomains')->where('status', 'Active')->whereYear('registrationdate', '=', $year)->groupBy($groupBy)->orderBy('registrationdate')->pluck(Capsule::raw('count(id) as total'), Capsule::raw('date_format(`registrationdate`, "%Y-%c") as month')); $groupBy = Capsule::raw('date_format(`nextduedate`, "%Y-%c")'); $reportvalues['domainsTerminated'] = Capsule::table('tbldomains')->whereYear('nextduedate', '=', $year)->where('nextduedate', '<=', $dateFilter->format('Y-m-d'))->groupBy($groupBy)->orderBy('nextduedate')->pluck(Capsule::raw('count(id) as total'), Capsule::raw('date_format(`nextduedate`, "%Y-%c") as month')); $activeDomains = Capsule::table('tbldomains')->where('status', 'Active')->pluck(Capsule::raw('count(id) as total'))[0]; for ($tmonth = 1; $tmonth <= 12; $tmonth++) { if (date('Y') == $year AND $tmonth > str_replace('0', '', $month)): continue; endif; $date = Carbon::create($year, $tmonth, 1); $dateMonthYear = $date->format('M Y'); $dateMonth = $date->format('M'); $key = $year . '-' . $tmonth; // Products $activeProducts = $activeProducts + $reportvalues['productsNew'][$key]; $productStart[$tmonth] = $activeProducts; $reportvalues['productsCumulative'][$key] = $activeProducts; $productsNew = isset($reportvalues['productsNew'][$key]) ? $reportvalues['productsNew'][$key] : '0'; $productsTerminated = isset($reportvalues['productsTerminated'][$key]) ? $reportvalues['productsTerminated'][$key] : '0'; $productsCumulative = isset($reportvalues['productsCumulative'][$key]) ? $reportvalues['productsCumulative'][$key] : '0'; $productVariation = $productsNew - $productsTerminated; $productChurnRate = number_format(($productsTerminated / $productStart[($tmonth == '1' ? '1' : $tmonth - 1)]) * 100, 1, '.', '') + 0; // Domains $activeDomains = $activeDomains + $reportvalues['domainsNew'][$key]; $domainStart[$tmonth] = $activeDomains; $reportvalues['domainsCumulative'][$key] = $activeDomains; $domainsNew = isset($reportvalues['domainsNew'][$key]) ? $reportvalues['domainsNew'][$key] : '0'; $domainsTerminated = isset($reportvalues['domainsTerminated'][$key]) ? $reportvalues['domainsTerminated'][$key] : '0'; $domainsCumulative = isset($reportvalues['domainsCumulative'][$key]) ? $reportvalues['domainsCumulative'][$key] : '0'; $domainVariation = $domainsNew - $domainsTerminated; $domainChurnRate = number_format(($domainsTerminated / $domainStart[($tmonth == '1' ? '1' : $tmonth - 1)]) * 100, 1, '.', '') + 0; // Overall $activeOverall = $activeProducts + $activeDomains; $overallStart[$tmonth] = $activeOverall; $reportvalues['overallCumulative'][$key] = $activeOverall; $overallNew = $productsNew + $domainsNew; $overallTerminated = $productsTerminated + $domainsTerminated; $overallCumulative = $productsCumulative + $domainsCumulative; $overallVariation = $productVariation + $domainVariation; $overallChurnRate = $productChurnRate + $domainChurnRate; $reportdata['tablevalues'][] = array( $dateMonthYear, formatCell(array('col' => 'products=', 'variation' => $productVariation, 'start' => $productStart[($tmonth == '1' ? '1' : $tmonth - 1)], 'end' => $productStart[($tmonth == '1' ? '1' : $tmonth - 1)] + $productVariation)), formatCell(array('col' => 'products+', 'increase' => $productsNew)), formatCell(array('col' => 'products-', 'decrease' => $productsTerminated)), formatCell(array('col' => 'products%', 'churnRate' => $productChurnRate)), formatCell(array('col' => 'domains=', 'variation' => $domainVariation, 'start' => $domainStart[($tmonth == '1' ? '1' : $tmonth - 1)], 'end' => $domainStart[($tmonth == '1' ? '1' : $tmonth - 1)] + $domainVariation)), formatCell(array('col' => 'domains+', 'increase' => $domainsNew)), formatCell(array('col' => 'domains-', 'decrease' => $domainsTerminated)), formatCell(array('col' => 'domains%', 'churnRate' => $domainChurnRate)), formatCell(array('col' => 'overall=', 'variation' => $overallVariation, 'start' => $overallStart[($tmonth == '1' ? '1' : $tmonth - 1)], 'end' => $overallStart[($tmonth == '1' ? '1' : $tmonth - 1)] + $overallVariation)), formatCell(array('col' => 'overall+', 'increase' => $overallNew)), formatCell(array('col' => 'overall-', 'decrease' => $overallTerminated)), formatCell(array('col' => 'overall%', 'churnRate' => $overallChurnRate)), ); $chartdata['rows'][] = array( 'c'=>array( array('v' => $dateMonth), array('v' => (int)$productStart[($tmonth == '1' ? '1' : $tmonth - 1)] + $productVariation), array('v' => (int)$domainStart[($tmonth == '1' ? '1' : $tmonth - 1)] + $domainVariation), array('v' => (int)$overallStart[($tmonth == '1' ? '1' : $tmonth - 1)] + $overallVariation), ) ); } function formatCell($data) { /** * @param string $col Column type * @param string $variation Monthly change * @param string $increase New purchases * @param string $decrease New terminations * @param string $start No. of customers (at the start of the period) * @param string $end No. of customers (at the end of the period) * @param string $churnRate Churn Rate * @return string Formatted HTML cell */ $data['variation'] = ($data['variation'] ? $data['variation'] : '0'); $data['increase'] = ($data['increase'] ? $data['increase'] : '0'); $data['decrease'] = ($data['decrease'] ? $data['decrease'] : '0'); $data['start'] = ($data['start'] ? $data['start'] : '0'); $data['end'] = ($data['end'] ? $data['end'] : '0'); $data['churnRate'] = ($data['churnRate'] ? $data['churnRate'] : false); if (in_array($data['col'], array('products+', 'domains+', 'overall+'))) { if ($data['increase']) { return '<span>' . $data['increase'] . '</span>'; } else { return '-'; } } elseif (in_array($data['col'], array('products-', 'domains-', 'overall-'))) { if ($data['decrease']) { return '<span>' . $data['decrease'] . '</span>'; } else { return '-'; } } elseif (in_array($data['col'], array('products=', 'domains=', 'overall='))) { if ($data['variation'] > '0') { $variation = '<small class="pull-right" style="opacity:0.8;">' . abs($data['variation']) . '<i class="fad fa-angle-double-up fa-fw text-success"></i></span>'; } elseif ($data['variation'] < '0') { $variation = '<small class="pull-right" style="opacity:0.8;">' . abs($data['variation']) . '<i class="fad fa-angle-double-down fa-fw text-danger"></i></span>'; } if ($data['start'] != $data['end']) { return $data['start'] . ' <i class="fas fa-angle-right fa-fw"></i> ' . $data['end'] . $variation; } else { return $data['start']; } } elseif (in_array($data['col'], array('products%', 'domains%', 'overall%'))) { if ($data['churnRate'] > 0) { return '<span class="label label-danger">' . $data['churnRate'] . '%</span>'; } else { return '-'; } } } $chartdata['cols'][] = array('label'=>'Day','type'=>'string'); $chartdata['cols'][] = array('label'=>'Products','type'=>'number'); $chartdata['cols'][] = array('label'=>'Domains','type'=>'number'); $chartdata['cols'][] = array('label'=>'Overall','type'=>'number'); $args = array(); $args['legendpos'] = 'right'; $reportdata["headertext"] = $chart->drawChart('Area',$chartdata,$args,'400px');
  7. Please wait. I decided that the best way to calculate & display churn rate is via WHMCS report. I'm coding one so that you can see that directly from WHMCS (graphs included). Edit: Okay, I've almost finished the script. Below there's a preview. I need you to confirm me that numbers make sense but first let me explain what every digit value means. Let's take Products Jan 2020 as example: -1 previous month variation 8.3% churn rate 1++ new purchase 2-- new terminations 23 No. of products (at the end of the month)
  8. Meta keywords tag has been deprecated more than a decade ago by all search engines. Here is a video from Google uploaded 10 years ago. For titles and meta description you can take a look at this post. That said keep in mind that meta description is NOT a ranking factor and that WHMCS doesn't allow to get any significant benefit on SERP. Simply put WHMCS and SEO are on two different planets so don't waste too much time with titles and stuff link that.
  9. Okay 🤔 One question just to make sure I got it right. I order the following services on Jan: Product A Product B Product C I terminate them as follows: Product A Feb Product B Feb Product C Mar Bonus: I order Product D on Feb. What result are you expecting to see?
  10. There's no need to join the same table. Just count by CASE. Try with this one. SELECT (CONCAT(YEAR(regdate), "-", MONTH(regdate))) AS period, COUNT(CASE WHEN domainstatus = "Terminated" THEN 1 ELSE NULL END) AS terminatedAccounts, COUNT(CASE WHEN domainstatus = 'Active' THEN 1 ELSE NULL END) AS activeAccounts, (COUNT(CASE WHEN domainstatus = "Terminated" THEN 1 ELSE NULL END) / COUNT(CASE WHEN domainstatus = 'Active' THEN 1 ELSE NULL END) * 100) AS churnRate FROM tblhosting GROUP BY YEAR(regdate), MONTH(regdate) DESC Here's a sample result.
  11. <?php use WHMCS\Database\Capsule; add_hook('FraudOrder', 1, function($vars) { $admins = Capsule::table('tbladmins')->where('disabled', '=', '0')->pluck('username'); foreach ($admins as $username) { localAPI('SendAdminEmail', array('type' => 'system', 'customsubject' => 'Fraud Order Detected', 'custommessage' => 'Order #' . $vars['orderid'] . ' detected as Fraudulent'), $username); } }); It should work. It sends the notification to all active administrators.
  12. I think you can use FraudOrder hook point and send emails to administrators via SendEmail API.
  13. Okay, I evolved the hook to support 3 different types of alerts. And I coded everything from scratch to add two new features regarding "one-off" products: First-timer tollerance. New customers are allowed to order everything they want. Once they register the fun is over An option to restrict purchases even further. Here's an example. Let's say that Bronze and Silver two "one-off" products off - Customer can purchase Bronze and Silver then they can no longer purchase them on - As soon as customer orders Bronze, he can't order Silver (and another Bronze of course) I'm still perfecting and changing the code so you can find it on github. I'm looking for bugs at the moment 🙏
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated