Kryptronic Software Support Forum

You are viewing this forum as a guest. Login to an existing account, or create a new account, to reply to topics and to create new topics.

Pages: 1

 

#1 08-15-2018 15:10:31

dskowron
Member
Registered: 11-26-2008
Posts: 142

Advanced Reports - Top 20 Customers, and Loyalty Points

Where would I find the sql code which pulls this information from the database? I need to alter it to return the top 30, 40 and 50 customers. As it stands now it returns the top 20.

I also want to write a sql statement that can go into our database and assign a number of loyalty points to everyone in our database. The quantity of loyalty points would be a percentage of their total purchases. We have 15,000 + customers and I don't really want to do this by hand. :-)

Thanks in advance Nick. I appreciate your help as always.

:-)

Offline

 

#2 08-16-2018 07:56:57

webmaster
Administrator
From: York, PA
Registered: 04-20-2001
Posts: 19832
Website

Re: Advanced Reports - Top 20 Customers, and Loyalty Points

To bump the top customers report from 20 to 50, adjust the count for the SQL statement in the file {private}/apps/ecom/ECOM/includes/reportsadv_customersordtotalstop20.php:

Code:

'count'  => 20

As far as updating customers with loyalty points totals matching their order totals, you'll need to update a bunch of fields.  Something like this (would issue 10% of order totals as loyalty points on completed orders):

Code:

UPDATE core_users, (SELECT email, SUM(ordertotal) AS grandtotal FROM ecom_orders WHERE status='C' GROUP BY email) AS orders
SET core_users.loyaltypoints=(orders.grandtotal*.10) WHERE core_users.id=orders.email
Nick Hendler

Offline

 

#3 08-16-2018 08:52:47

dskowron
Member
Registered: 11-26-2008
Posts: 142

Re: Advanced Reports - Top 20 Customers, and Loyalty Points

Thank you Sir!

Offline

 

 

Pages: 1

Board footer