Your account is limited to posting only in the Pre-Sales Questions forum.
Support forum access is limited to members with a purchase history.

If you are working on behalf of another member, please contact us with the name of the license holder and we will upgrade your account.
   
 
Top Customers
Posted: 23 March 2012 07:04 AM   [ Ignore ]
Is a Really Great Dancer
RankRank
Total Posts:  72
Joined  2010-08-18

An addition to the {order_totals} tag where by you can display a list of top customers, based on total orders (count) and total orders amount (£).

Profile
 
 
Posted: 23 March 2012 12:12 PM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRank
Total Posts:  3541
Joined  2009-05-29

You can achieve this with a few custom queries:

Swap out the XX with your orders channel id

Top Customers (most orders)
{exp:query sql="SELECT COUNT(*) AS orders, screen_name
FROM exp_channel_titles AS t
JOIN exp_channel_data AS d
ON d.entry_id = t.entry_id
JOIN exp_members AS m
ON t.author_id = m.member_id
WHERE t.channel_id = XX
AND t.status = 'open'
GROUP BY author_id
ORDER BY count DESC" 
limit="10"}
   
<li>{screen_name} ({orders})</li>
{/exp:query} 

Swap out the XX with your orders channel id, and YY with your order_total field id

Top Customers (most orders)
{exp:query sql="SELECT SUM(field_id_YY) AS total, username
FROM exp_channel_titles AS t
JOIN exp_channel_data AS d
ON d.entry_id = t.entry_id
JOIN exp_members AS m
ON t.author_id = m.member_id
WHERE t.channel_id = XX
AND t.status = 'open'
GROUP BY author_id
ORDER BY total DESC" 
limit="10"}
   
<li>{screen_name} ({exp:cartthrob:view_formatted_number number="{total}"})</li>
{/exp:query} 
[ Edited: 26 March 2012 09:28 AM by Rob Sanchez ]
Profile
 
 
Posted: 26 March 2012 12:12 AM   [ Ignore ]   [ # 2 ]
Is a Really Great Dancer
RankRank
Total Posts:  72
Joined  2010-08-18

Wow, that’s excellent Rob thanks for those. I did have a stab at doing this with the query module, but didn’t get very far. Think I need to brush up on some query skills as it looks like you can do some pretty powerful stuff!

Profile