Jump to content

Recommended Posts

In admin dashboard/Orders and dashboard/Customer List Is there any way to add a column for Customer Group and be able to sort by it? Also in the Reports/Sales/Results can a filter be added to sort by Customer Group?

I think I can do this myself .  I just want to know if it will mess anything up.

 

Thanks for any and all help

Claudia

Edited by Claudia M
Link to post
Share on other sites

This conversation may have some interest for you:
https://forums.cubecart.com/topic/53037-orders-to-show-customer-group/

In the Customer List, since CubeCart provides the Customer Group Memberships as part of each customer in the list, it is a small edit to the admin skin code.

However, sorting on the group name is much more involved.

 

Link to post
Share on other sites

Dang, I thought I could do the sorting!  Would it be too time consuming for your to help me with that?  I looked at topic 53037.  I didn't want Cubecart to send emails to my customer groups (actually the customer groups are Etsy and eBay so I see their sales in Cubecart).  I added a new field in the order.summary database for realemail and added the code to admin.skins.default.orders.index to reflect this addition.  This way CC doesn't send them an email.  When I sell something on ebay I create a customer in CC and assign them to the ebay customer group.  Since the item is already in CC, it's simple to then create an order for this customer.  That's the reason for this post and how important the sorting is.

Link to post
Share on other sites

I can work on it, and it will be a worthwhile exercise.

Currently, CubeCart paginates the list of customers in chunks of 20. That is, only 20 records are returned from the query at a time (using an OFFSET). Then, as each of these 20 records is looked at, additional data specific to the customer is added - customer group names, for example. So, attempting to re-sort thew list on the customer group name will only sort that group of 20 records. That's a fail.

An entirely new query will need to be constructed - one that JOINs the CubeCart_customer_membership table to the initial CubeCart_customer table, so that the database engine can do the sort before sending the group of 20 records.

Then there is the issue that a customer can be associated with more than one customer group. That means there could be duplicate rows referencing the same customer, but at least the list of customers will be sorted by the group name.

Be back shortly.

Link to post
Share on other sites

For adding a sort to the Customer List, make the following edits in these two files:

In admin /sources/customers.index.inc.php, near line 491, find:

    if (($customer_count = $GLOBALS['db']->select('CubeCart_customer', array('customer_id'), $where)) !== false) {
        $count = count($customer_count);
        $GLOBALS['smarty']->assign('PAGINATION', $GLOBALS['db']->pagination($count, $per_page, $page));
    }

On the blank line after, add:

    if ($key[0] == "member_groups") $order_by = " ORDER BY `group_name` ".$_GET['sort'][$key[0]];

Then, the next line should be one that starts with:
    if (($customers = $GLOBALS['db']->select

Split that line between the two opening parenthesis.

Between those two lines, insert the following:

($customers = $GLOBALS['db']->query(
sprintf("SELECT *, CONCAT(`last_name`, `first_name`) AS `customer`
FROM `%1\$sCubeCart_customer` AS C
LEFT JOIN(
SELECT `customer_id` AS XCID, `group_name`
FROM `%1\$sCubeCart_customer_membership` AS M
INNER JOIN `%1\$sCubeCart_customer_group` AS G
WHERE G.`group_id` = M.`group_id`) AS X
ON XCID = C.`customer_id`", $GLOBALS['config']->get('config', 'dbprefix'))
.$where
.$order_by
, $per_page
, $page
, false)) !==false
||

In the same file, near line 474, find:

    $thead_sort = array(

On a new blank line after that, add:

        'membership'   => $GLOBALS['db']->column_sort('member_groups', $lang['customer']['title_groups_membership'], 'sort', $current_page, $_GET['sort']),


In the admin skin template customers.index.php, near line 27, find:

      <td>{$THEAD.customer}</td>

On a new blank line after that, add:

      <td>{$THEAD.membership}</td>

About ten lines after, find:

      <td><a href="{$customer.edit}">{$customer.last_name}, {$customer.first_name}</a> {if !empty($customer.groups)}({$customer.groups}){/if}</td>

You can change this IF YOU WISH by removing the groups names:

      <td><a href="{$customer.edit}">{$customer.last_name}, {$customer.first_name}</a></td>

On a new blank line after that, add:

      <td>{$customer.groups}</td>

This is not a filter. Customers not assigned to any customer group will still be listed.

Also, customers assigned to more than one group will be listed for as many groups of which they belong - but sorted by group. I mention this because pagination may be off in such cases - more records than the total shown. But I think this is irrelevant in this case.

Do not fail to notice the double-pipe at the bottom of the new query being inserted.

Edited by bsmither
Link to post
Share on other sites

No I didn't. Tried to reset password even though I knew it was good. They sent me the reset password email and I got this though I know the email is correct. I've placed a ticket with Ian but haven't heard back.  Looked at the database and all looks good.

Is this how the code should look now

On the blank line after, add:

 

 

 

    if ($key[0] == "member_groups") $order_by = " ORDER BY `group_name` ".$_GET['sort'][$key[0]];

 

 

Then, the next line should be one that starts with:

    if (

($customers = $GLOBALS['db']->query(

sprintf("SELECT *, CONCAT(`last_name`, `first_name`) AS `customer`

FROM `%1\$sCubeCart_customer` AS C

LEFT JOIN(

SELECT `customer_id` AS XCID, `group_name`

FROM `%1\$sCubeCart_customer_membership` AS M

INNER JOIN `%1\$sCubeCart_customer_group` AS G

WHERE G.`group_id` = M.`group_id`) AS X

ON XCID = C.`customer_id`", $GLOBALS['config']->get('config', 'dbprefix'))

.$where

.$order_by

, $per_page

, $page

, false)) !==false

||

($customers = $GLOBALS['db']->select

 

Edited by havenswift-hosting
Link to post
Share on other sites

Permission Denied on creating a session is very much a server environment issue. (I can log in on a store I have with Havenswift - it must be very localized.)

The code is correct - but to remove some ambiguity, this is not part of the code:

Then, the next line should be one that starts with:

Link to post
Share on other sites

Hey Brian,

When I implemented your code it didn't show any customers in the customer list, but it did show the pagination.  I cleared the cache.  I'm going to check what I did again.

I can sort my group membership but if I try to sort by any other field it doesn't show any customers.

Link to post
Share on other sites

In CubeCart's admin, Store Settings, Advanced tab, enable debugging and enter your IP address in the next field (www.whatismyip.com).

Call up the Customer List. Sort on email by clicking on the Email column header.

In the debug area at the bottom of the page, find the query that returns the list of customers. It may be close to the 14th query. It starts with (after the time):

SELECT SQL_CALC_FOUND_ROWS *, CONCAT(`last_name`, `first_name`) AS `customer` FROM `CubeCart_customer` ORDER BY `email` ASC LIMIT 20 OFFSET 0; -- (0 sec) [NOT CACHED]

 

Please make a change to this line:

($customers = $GLOBALS['db']->query(

To:

($key[0] == "member_groups") && ($customers = $GLOBALS['db']->query(

 

Link to post
Share on other sites

Is this what you are asking for?

[14] SELECT `customer_id` FROM `CubeCart_customer` ; -- (0.00044393539428711 sec) [NOT CACHED]
[15] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [customers.index.inc.php] Line: [510] \"SELECT *, CONCAT(`last_name`, `first_name`) AS `customer`\nFROM `CubeCart_customer` AS C\nLEFT JOIN(\nSELECT `customer_id` AS XCID, `group_name`\nFROM `CubeCart_customer_membership` AS M\nINNER JOIN `CubeCart_customer_group` AS G\nWHERE G.`group_id` = M.`group_id`) AS X\nON XCID = C.`customer_id``email` ASC LIMIT 20 OFFSET 0\" - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'ASC LIMIT 20 OFFSET 0\' at line 8','1576268918'); -- (0.0011091232299805 sec) [ERROR - NOT CACHED]
[16] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [customers.index.inc.php] Line: [510] \"SELECT *, CONCAT(`last_name`, `first_name`) AS `customer`\nFROM `CubeCart_customer` AS C\nLEFT JOIN(\nSELECT `customer_id` AS XCID, `group_name`\nFROM `CubeCart_customer_membership` AS M\nINNER JOIN `CubeCart_customer_group` AS G\nWHERE G.`group_id` = M.`group_id`) AS X\nON XCID = C.`customer_id``email` ASC LIMIT 20 OFFSET 0\" - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'ASC LIMIT 20 OFFSET 0\' at line 8','1576268918'); -- (0.0011091232299805 sec) [ERROR - NOT CACHED]
[17] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('[<strong>Warning</strong>] /home/claudias/public_html/admin_Gsvj4z/sources/customers.index.inc.php:516 - Invalid argument supplied for foreach()','1576268918'); -- (0.00023198127746582 sec) [ERROR - NOT CACHED]
[18] DELETE FROM `CubeCart_system_error_log` WHERE time < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) ; -- (0.00028085708618164 sec) [NOT CACHED]
[19] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('[<strong>Notice</strong>] /home/claudias/public_html/admin_Gsvj4z/sources/customers.index.inc.php:537 - Undefined variable: customer_list','1576268918'); -- (0.00022196769714355 sec) [ERROR - NOT CACHED]

Link to post
Share on other sites

Made the change now I can sort by everything but the Customer Group.  Doesn't show any records when I click on group memberships.  Here's my code

$order_by = '`'.$key[0].'` '.$_GET['sort'][$key[0]];
    if (
($key[0] == "member_groups") && ($customers = $GLOBALS['db']->query(
sprintf("SELECT *, CONCAT(`last_name`, `first_name`) AS `customer`
FROM `%1\$sCubeCart_customer` AS C
LEFT JOIN(
SELECT `customer_id` AS XCID, `group_name`
FROM `%1\$sCubeCart_customer_membership` AS M
INNER JOIN `%1\$sCubeCart_customer_group` AS G
WHERE G.`group_id` = M.`group_id`) AS X
ON XCID = C.`customer_id`", $GLOBALS['config']->get('config', 'dbprefix'))
.$where
.$order_by
, $per_page
, $page
, false)) !==false
||
($customers = $GLOBALS['db']->select('CubeCart_customer', '*, CONCAT(`last_name`, `first_name`) AS `customer`', $where, $order_by, $per_page, $page)) !== false) {

Link to post
Share on other sites

There seems to be missing code in what was pasted in the post above.

This is what the code, up to the most recently changed line, should look like:

Starting at about line 484:

    foreach ($GLOBALS['hooks']->load('admin.customer.table_head_sort') as $hook) {
        include $hook;
    }

    $GLOBALS['smarty']->assign('THEAD', $thead_sort);
    $key = array_keys($_GET['sort']);
    $order_by = '`'.$key[0].'` '.$_GET['sort'][$key[0]];

    if (($customer_count = $GLOBALS['db']->select('CubeCart_customer', array('customer_id'), $where)) !== false) {
        $count = count($customer_count);
        $GLOBALS['smarty']->assign('PAGINATION', $GLOBALS['db']->pagination($count, $per_page, $page));
    }
    if ($key[0] == "member_groups") $order_by = " ORDER BY `group_name` ".$_GET['sort'][$key[0]];
    if (
($key[0] == "member_groups") && ($customers = $GLOBALS['db']->query(

 

Link to post
Share on other sites
21 minutes ago, bsmither said:

Please keep aware of that other conversation. It completely solved the needs of the person who requested it, but of course, that person's needs regarding customer group names  were different.

Ok I'll just leave it as is.  Thank you so much!

Link to post
Share on other sites
  • 5 months later...

I've been cleaning the files on my computer.  Lots of duplicate content and some not up to date.  Anyway, I came across this.  It shows a sortable Group Membership column in the Customer List.  Can the same be done to show a sortable Group Membership column in the Orders tab?

Thanks in advance!

Claudia

Link to post
Share on other sites
Posted (edited)

There is this:
https://forums.cubecart.com/topic/53037-orders-to-show-customer-group/

What are your thoughts on how to deal with the possibility that a customer can belong to more than one group? Even though we are dealing with a list of Orders, each order does relate to a customer.

My initial thought was to log somewhere which customer group CubeCart eventually decided to apply (price schedule, other perks, ???). Still, there may be cases where more than one group gets involved in any given order.

I realize your situation may have very narrow requirements, but a solution must be able to handle the current general environment. Even though the solution for the original problem that started this thread sorts (somehow) by the group name and assumes each customer belongs to only one group.

Thoughts?

 

Edited by bsmither
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...