Claudia M Posted December 12, 2019 Share Posted December 12, 2019 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 Link to comment Share on other sites More sharing options...
bsmither Posted December 13, 2019 Share Posted December 13, 2019 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 comment Share on other sites More sharing options...
Claudia M Posted December 13, 2019 Author Share Posted December 13, 2019 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 comment Share on other sites More sharing options...
bsmither Posted December 13, 2019 Share Posted December 13, 2019 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 comment Share on other sites More sharing options...
Claudia M Posted December 13, 2019 Author Share Posted December 13, 2019 Thanks Brian Link to comment Share on other sites More sharing options...
bsmither Posted December 13, 2019 Share Posted December 13, 2019 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. Link to comment Share on other sites More sharing options...
Claudia M Posted December 13, 2019 Author Share Posted December 13, 2019 Thanks Brian. I'll implement this ASAP. Can't check it in admin yet. It won't let me log in. Link to comment Share on other sites More sharing options...
bsmither Posted December 13, 2019 Share Posted December 13, 2019 Do you get the log-in page? Anything in the error_log? Link to comment Share on other sites More sharing options...
Claudia M Posted December 13, 2019 Author Share Posted December 13, 2019 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 Link to comment Share on other sites More sharing options...
bsmither Posted December 13, 2019 Share Posted December 13, 2019 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 comment Share on other sites More sharing options...
Claudia M Posted December 13, 2019 Author Share Posted December 13, 2019 Thanks Brian - I din't mean to paste the red line LOL. Hopefully Ian will get back to me soon as he usually does. Link to comment Share on other sites More sharing options...
havenswift-hosting Posted December 13, 2019 Share Posted December 13, 2019 It was a very localised issue with session handling and all fixed. I removed some of the content from your post Claudia as best not to display some of what was there Ian Link to comment Share on other sites More sharing options...
Claudia M Posted December 13, 2019 Author Share Posted December 13, 2019 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 comment Share on other sites More sharing options...
bsmither Posted December 13, 2019 Share Posted December 13, 2019 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 comment Share on other sites More sharing options...
Claudia M Posted December 13, 2019 Author Share Posted December 13, 2019 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 comment Share on other sites More sharing options...
bsmither Posted December 13, 2019 Share Posted December 13, 2019 Refresh this forum page. I added more to the post above. Link to comment Share on other sites More sharing options...
Claudia M Posted December 13, 2019 Author Share Posted December 13, 2019 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 comment Share on other sites More sharing options...
bsmither Posted December 13, 2019 Share Posted December 13, 2019 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 comment Share on other sites More sharing options...
Claudia M Posted December 13, 2019 Author Share Posted December 13, 2019 It's working now!!!! Thank you. Should I do anything from this? And is it complete? This conversation may have some interest for you: https://forums.cubecart.com/topic/53037-orders-to-show-customer-group/ Link to comment Share on other sites More sharing options...
bsmither Posted December 13, 2019 Share Posted December 13, 2019 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. Link to comment Share on other sites More sharing options...
Claudia M Posted December 13, 2019 Author Share Posted December 13, 2019 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 comment Share on other sites More sharing options...
Claudia M Posted June 1, 2020 Author Share Posted June 1, 2020 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 comment Share on other sites More sharing options...
bsmither Posted June 1, 2020 Share Posted June 1, 2020 I suppose. Link to comment Share on other sites More sharing options...
Claudia M Posted June 1, 2020 Author Share Posted June 1, 2020 No big deal Link to comment Share on other sites More sharing options...
bsmither Posted June 3, 2020 Share Posted June 3, 2020 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? Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.