How to display customer count of a certain price group?

Discussion in 'Templates customization' started by AdamCapriola, Aug 22, 2010.

  1. AdamCapriola

    AdamCapriola New Member

    Joined:
    Jul 10, 2010
    Messages:
    27
    Hey everyone,

    I want to display the customer count of different price group products within my templates.

    For example, on the signup/registration page, I'd like to be able to say "Join #### other members of our community!" and have the #### replaced with the actual number.

    The numbers I'd want are pretty much displayed on the admin CP, so I went into the index.php for that and found this code:

    Code:
    function get_users_report(){
    global $t,$vars,$db,$config;
    $res = array(0 => array(), 1=>array(), 2=>array());
    $q = $db->query("SELECT status,COUNT(*) 
    FROM {$db->config[prefix]}members
        GROUP BY status");
    while (list($s,$c) = mysql_fetch_row($q)){
        $res[$s] = array('count'=>$c);
        $total += $c;
    }
    for ($i=0;$i<=2;$i++) 
        $res[$i]['count'] = intval($res[$i]['count']);
    $active_paid = $db->query_one("SELECT COUNT(DISTINCT member_id) AS active
            FROM {$db->config[prefix]}payments
            WHERE completed > 0 AND begin_date <= NOW() AND expire_date >= NOW() AND amount > 0");
    $active_free  = $res[1]['count'] - $active_paid;
    $res[0]['title'] = 'Pending';
    $res[1]['title'] = '<b>Active (free/paid)</b>';
    $res[1]['count'] = '<b>'.$res[1]['count']." ($active_free/$active_paid)</b>";
    $res[2]['title'] = 'Expired';
    $res[3]['title'] = '<b><a href="users.php?letter=A">Total</a></b>';
    $res[3]['count'] = '<b><a href="users.php?letter=A">'.$total.'</a></b>';
    
    return $res;
}
    However, I don't really know how to call that within the HTML files with Smarty (I guess that's how you'd do it).

    It would also be better if there was a way to get a count by price group rather than how this does it by payment and free.

    Thanks for any help! Even just being able to call the free amount and paid amount within the HTML pages (signup.html) using this code would be a huge help.

    -Adam
    AdamCapriola, Aug 22, 2010
    #1
  2. alexander

    alexander Administrator Staff Member

    Joined:
    Jan 8, 2003
    Messages:
    6,279
    Here is example of how you can get such count in smarty:
    Code:
    {php}
global $db; 
    $active_paid = $db->query_one("SELECT COUNT(DISTINCT member_id) AS active
            FROM {$db->config[prefix]}payments
            WHERE completed > 0 AND begin_date <= NOW() AND expire_date >= NOW() AND amount > 0");
print $active_paid;
{/php}
    Above block will print number of active paid users.
    If you want to separate by price_groups use this:
    Code:
    {php}
global $db; 
    $active_paid = $db->query_one("SELECT COUNT(DISTINCT member_id) AS active
            FROM {$db->config[prefix]}payments
            WHERE completed > 0 AND begin_date <= NOW() AND expire_date >= NOW() AND amount > 0 and product_id in(1,2,3)");
print $active_paid;
{/php}
    where 1,2,3 are all products for each price_group.
    alexander, Aug 23, 2010
    #2

Share This Page