[Resolved] Would like stock per Manufacturers Data

Dirty Butter · August 4, 2015

When I started our plush store I had about 500 items listed. I'm now closing in on 4,000. Most of our Categories were created a long time ago, but I feel sure there are categories I need to add to feature brands with large numbers of listings. The trouble is - how to easily determine how many listings there are for each manufacturer. Any ideas?

Edited 6/25/16 - Bsmither provided the code to fix this, but I had trouble editing the code correctly. So he helped me again with this today, and you can jump to the directions I was able to follow correctly HERE:

bsmither · August 4, 2015

In the admin, Manufacturers screen, would a column/tally of the products assigned to each manufacturer be sufficient?

Dirty Butter · August 4, 2015

yes it would, although it would be even nicer if it would sort

bsmither · August 5, 2015

We can work on the sort later.

In products.manufacturers.inc.php:

Find near line 76:
if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers', array('id', 'image'), false, 'name', $per_page, $page)) !== false) {

Change to:
if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers',"`id`, `image`, (SELECT COUNT('product_id') FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_inventory WHERE manufacturer = id) AS manu_prod_count", false, 'name', $per_page, $page)) !== false) {

In the template products.manufacturers.php:

Find near line 19:
		<tr>
		  <td width="250">{$LANG.catalogue.manufacturer}</td>
		  <td>{$LANG.form.action}</td>
		</tr>

Change to:
		<tr>
		  <td width="250">{$LANG.catalogue.manufacturer}</td>
          <td>#Prods</td>
		  <td>{$LANG.form.action}</td>
		</tr>

Find near line 25:
		{foreach from=$MANUFACTURERS item=manufacturer}
		<tr>
		  <td>{$manufacturer.name}</td>
		  <td>

Change to:
		{foreach from=$MANUFACTURERS item=manufacturer}
		<tr>
		  <td>{$manufacturer.name}</td>
          <td align="right">{$manufacturer.manu_prod_count}</td>
		  <td>

Dirty Butter · August 5, 2015

Worked like a charm. Thank you! With close to 400 manufacturers, it would be nice to be able to sort, but certainly not necessary.

Edited August 5, 2015 by Dirty Butter

havenswift-hosting · August 5, 2015

Rosemary - we have an Enhanced Manufacturers plugin just available that gives each manufacturer a front end listing page similar to the category pages and also gives them a page where you can list details of each of them. There are two ways of displaying the manufacturers (dropdown or list or indeed you can even have both with different manufacturers assigned to each style - useful if you wish to highlight one or two specific manufacturers rather than being in a dropdown box). You can see it working on our demo website https://www.cubecart-demo.co.uk although it is currently only in the new "Nautical" skin which you would need to change to. This and the default "RetailTherapy" are two skins we are just completing and will be available soon. Your request for product listing by manufacturer within the admin side was also requested two days ago by one of our first users of this plugin so we are going to be adding that very shortly - we will even include sorting !

Thanks

Ian

bsmither · August 5, 2015

Here is a redeux of the edits with sorting.

In products.manufacturers.inc.php:

Find near line 76:
if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers', array('id', 'image'), false, 'name', $per_page, $page)) !== false) {

Change to:
if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers',"`id`, `image`, (SELECT COUNT('product_id') FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_inventory WHERE manufacturer = id) AS manu_prod_count", false, $_GET['sort'], $per_page, $page)) !== false) {

Find near line 68:
	$GLOBALS['smarty']->assign('DISPLAY_FORM', true);

} else {

Add After:
/* NEW */
	// Sorting
	$current_page = currentPage(array('sort'));
	if (!isset($_GET['sort']) || !is_array($_GET['sort'])) {
		$_GET['sort'] = array('name' => 'DESC');
	}
	$thead_sort = array (
		'name'   => $GLOBALS['db']->column_sort('name', $lang['catalogue']['manufacturer'], 'sort', $current_page, $_GET['sort']),
		'no_prods'   => $GLOBALS['db']->column_sort('manu_prod_count', "#Prods", 'sort', $current_page, $_GET['sort']),
		'action'   => $lang['form']['action'],
	);
	$GLOBALS['smarty']->assign('THEAD', $thead_sort);
/* /NEW */

In the template products.manufacturers.php:

Find near line 19:
		<tr>
		  <td width="250">{$LANG.catalogue.manufacturer}</td>
		  <td>{$LANG.form.action}</td>
		</tr>

Change to:
		<tr>
		  <th nowrap="nowrap" width="250">{$THEAD.name}</th>
          <th nowrap="nowrap">{$THEAD.no_prods}</th>
		  <th nowrap="nowrap">{$THEAD.action}</th>
		</tr>

Find near line 25:
		{foreach from=$MANUFACTURERS item=manufacturer}
		<tr>
		  <td>{$manufacturer.name}</td>
		  <td>

Change to:
		{foreach from=$MANUFACTURERS item=manufacturer}
		<tr>
		  <td>{$manufacturer.name}</td>
          <td align="right">{$manufacturer.manu_prod_count}</td>
		  <td>

Dirty Butter · August 5, 2015

It's not sorting. Knowing me, I probably just got lost trying to mesh the count and sort codes together:

Here's my products.manufacturer.inc.php section:

	$GLOBALS['smarty']->assign('DISPLAY_FORM', true);

} else {
/* BSMITHER SORTED BRAND STOCK LEVEL  */
	// Sorting
	$current_page = currentPage(array('sort'));
	if (!isset($_GET['sort']) || !is_array($_GET['sort'])) {
		$_GET['sort'] = array('name' => 'DESC');
	}
	$thead_sort = array (
		'name'   => $GLOBALS['db']->column_sort('name', $lang['catalogue']['manufacturer'], 'sort', $current_page, $_GET['sort']),
		'no_prods'   => $GLOBALS['db']->column_sort('manu_prod_count', "#Prods", 'sort', $current_page, $_GET['sort']),
		'action'   => $lang['form']['action'],
	);
	$GLOBALS['smarty']->assign('THEAD', $thead_sort);
/* END BSMITHER SORTED BRAND STOCK LEVEL  */
	$GLOBALS['main']->addTabControl($lang['catalogue']['title_manufacturer'], 'manufacturers');
	$GLOBALS['main']->addTabControl($lang['catalogue']['title_manufacturer_add'], 'manu_add');
	$catalogue = Catalogue::getInstance();
	$page  = (isset($_GET['page'])) ? $_GET['page'] : 1;
	$per_page = 10;
//BSMITHER SORTED BRAND STOCK LEVEL 
if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers',"`id`, `image`, (SELECT COUNT('product_id') FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_inventory WHERE manufacturer = id) AS manu_prod_count", false, $_GET['sort'], $per_page, $page)) !== false) {
//END BSMITHER SORTED BRAND STOCK LEVEL

And here's the products.manufacturer.php section:

{* BSMITHER BRAND SORTED STOCK LEVEL *}
<tr>
		  <th nowrap="nowrap" width="250">{$THEAD.name}</th>
          <th nowrap="nowrap">{$THEAD.no_prods}</th>
		  <th nowrap="nowrap">{$THEAD.action}</th>
		</tr>
	  </thead>
	  <tbody>
{foreach from=$MANUFACTURERS item=manufacturer}
		<tr>
		  <td>{$manufacturer.name}</td>
          <td align="right">{$manufacturer.manu_prod_count}</td>
		  <td>{* END BSMITHER BRAND SORTED STOCK LEVEL *}

Thank you for the information about your Enhanced Manufacturers plugin Ian. So many of our manufacturers in the list only have one or two items that putting the list in any way other than major categories on the front end probably wouldn't be useful for our customers. But it sounds like something a lot of sellers would find useful on their stores.

bsmither · August 5, 2015

Looks good to me. Did you clear the skin cache?

Does the URL that shows in your browser's address bar, after you click a sort header, look like, for example:

/admin.php?_g=products&node=manufacturers&sort[manu_prod_count]=DESC

Dirty Butter · August 5, 2015

?_g=products&node=manufacturers&sort[manu_prod_count]=DESC

I've changed the name of admin, but this appears to be the same as what you have. I cleared the cache, and, when that didn't fix it, I manually deleted all cache and rebooted FF.

bsmither · August 5, 2015

And what happened?

Dirty Butter · August 5, 2015

Sorry, it still doesn't work - I should have made that clear.

bsmither · August 5, 2015

Enable CubeCart's debugging. (Be sure to enter your own IP address in the adjacent field.)

When you click on a table header, which should be a link with a sort key/value, and the page gets refetched (it does fetch a new page, right?), the SQL queries in the debug section should have, near the bottom, something very similar to the following:

[16] SHOW COLUMNS FROM CubeCart_manufacturers; -- (0.015625 sec) [NOT CACHED]
[17] SELECT SQL_CALC_FOUND_ROWS `id`, `image`, (SELECT COUNT('product_id') FROM CubeCart_inventory WHERE manufacturer = id) AS manu_prod_count FROM `CubeCart_manufacturers` ORDER BY `name` ASC LIMIT 10 OFFSET 0; -- (0 sec) [NOT CACHED]
[18] SELECT FOUND_ROWS() as Count; -- (0 sec) [NOT CACHED]
[19] SELECT `name`, `URL` FROM `CubeCart_manufacturers` WHERE CubeCart_manufacturers.id = '2' ; -- (0 sec) [NOT CACHED]
[20] SELECT `name`, `URL` FROM `CubeCart_manufacturers` WHERE CubeCart_manufacturers.id = '1' ; -- (0 sec) [NOT CACHED]
[21] SELECT `name`, `URL` FROM `CubeCart_manufacturers` WHERE CubeCart_manufacturers.id = '3' ; -- (0 sec) [NOT CACHED]

The ORDER BY `name` ASC changes depending in the sort key/value.

Dirty Butter · August 5, 2015

[15] SELECT SQL_CALC_FOUND_ROWS `id`, `image`, (SELECT COUNT('product_id') FROM CubeCart_inventory WHERE manufacturer = id) AS manu_prod_count FROM `CubeCart_manufacturers` LIMIT 10 OFFSET 0; -- (0.144041061401 sec) [NOT CACHED]
[16] SELECT FOUND_ROWS() as Count; -- (0.000195980072021 sec) [NOT CACHED]
[17] SELECT `name`, `URL` FROM `CubeCart_manufacturers` WHERE CubeCart_manufacturers.id = '1' ; -- (0.000429153442383 sec) [NOT CACHED]
[18] SELECT `name`, `URL` FROM `CubeCart_manufacturers` WHERE CubeCart_manufacturers.id = '2' ; -- (0.000168085098267 sec) [NOT CACHED]
[19] SELECT `name`, `URL` FROM `CubeCart_manufacturers` WHERE CubeCart_manufacturers.id = '3' ; -- (0.000164985656738 sec) [NOT CACHED]
[20] SELECT `name`, `URL` FROM `CubeCart_manufacturers` WHERE CubeCart_manufacturers.id = '4' ; -- (0.000190019607544 sec) [NOT CACHED]

bsmither · August 5, 2015

This part of your query [15]:

AS manu_prod_count FROM `CubeCart_manufacturers` LIMIT 10 OFFSET 0;

is missing the ORDER BY clause.

The part of the PHP statement of the revised select() call would include $_GET['sort']:

... AS manu_prod_count", false, $_GET['sort'], $per_page, $page)) !== false) {

I have no idea why your database class is not accepting $_GET['sort'] as the 'order_by' argument. It's there because the table header links have it.

Dirty Butter · August 5, 2015

Well if YOU don't know, I SURE don't! But I can scan the list and find the ones I need to make sub-categories for, without the sort. And hopefully the sort will work for anyone else who reads this thread and can make use of your code. Thank you as always for all your help. Without your time and expertise this forum would probably fall apart - I know I would stay lost.

Dirty Butter · August 15, 2015

I have added more products and more manufacturers since adding this code. I noticed that the new manufacturers added were NOT sorting, so I took the sort numbers out of the code you had provided. Of course sorting the numbers never worked for me, so for someone else the lack of sorting newly added manufacturers might not happen.

Dirty Butter · March 16, 2016

Revisiting this for v6.0.11 upgrade, which included some changes to do with the manufactures in some way.

products.manufacturers.inc.php

6.0.10 code:

	foreach ($GLOBALS['hooks']->load('admin.product.manufacturers.save.post_process') as $hook) include $hook;
	httpredir('?_g=products&node=manufacturers#tab_manufacturers');
}
$GLOBALS['gui']->addBreadcrumb($lang['catalogue']['title_manufacturer'], currentPage(array('edit')));

foreach ($GLOBALS['hooks']->load('admin.product.manufacturer.pre_display') as $hook) include $hook;

if (isset($_GET['edit']) && is_numeric($_GET['edit'])) {
	$GLOBALS['main']->addTabControl($lang['catalogue']['title_manufacturer'], false, currentPage(array('edit')));
	$GLOBALS['main']->addTabControl($lang['catalogue']['title_manufacturer_edit'], 'manu_edit');
	if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers', array('name', 'id', 'URL'), array('id' => (int)$_GET['edit']))) !== false) {
		$GLOBALS['smarty']->assign('EDIT', $manufacturers[0]);
	} else {
		$GLOBALS['main']->setACPWarning($lang['catalogue']['error_manufacturer_found']);
		httpredir(currentPage(array('edit')));
	}
	$GLOBALS['smarty']->assign('DISPLAY_FORM', true);

} else {
	$GLOBALS['main']->addTabControl($lang['catalogue']['title_manufacturer'], 'manufacturers');
	$GLOBALS['main']->addTabControl($lang['catalogue']['title_manufacturer_add'], 'manu_add');
	$catalogue = Catalogue::getInstance();
	$page  = (isset($_GET['page'])) ? $_GET['page'] : 1;
	$per_page = 10;
//BSMITHER  BRAND STOCK LEVEL
if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers',"`id`, `image`, (SELECT COUNT('product_id') FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_inventory WHERE manufacturer = id) AS manu_prod_count", false, 'name', $per_page, $page)) !== false) {
//END BSMITHER  BRAND STOCK LEVEL
		$GLOBALS['smarty']->assign('PAGINATION', $GLOBALS['db']->pagination(false, $per_page, $page));
		foreach ($manufacturers as $manufacturer) {
			$manufacturer['name'] = $catalogue->getManufacturer($manufacturer['id']);
			$smarty_data['manufacturers'][] = $manufacturer;
		}
		$GLOBALS['smarty']->assign('MANUFACTURERS', $smarty_data['manufacturers']);

	}
	$GLOBALS['smarty']->assign('DISPLAY_LIST', true);
}
$page_content = $GLOBALS['smarty']->fetch('templates/products.manufacturers.php');

6.0.11 has this line instead of what Bsmither's brand stock level code has:

	 if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers', false, false, 'name', $per_page, $page)) !== false) {

What do I need to change, so I get the brand count again?

Bsmither Brand Stock Level section is currently commented out. With the stock level edit active and the 6.0.11 line commented out, I have no manufacturer names showing at all. Brand counts, edit and delete icons are there, View All works, just no manufacture names.

bsmither · March 16, 2016

Working with the 6011 code:

Find:
if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers', false, false, 'name', $per_page, $page)) !== false) {

Change to:
//BSMITHER  BRAND PRODUCT COUNT
if (($manufacturers = $GLOBALS['db']->select(
'CubeCart_manufacturers',
"*, (SELECT COUNT('product_id') FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_inventory WHERE manufacturer = id) AS manu_prod_count",
false,
'name',
$per_page,
$page)) !== false) {
//END BSMITHER  BRAND PRODUCT COUNT

CC6010 used Catalogue->getManufacturer() to retrieve the 'name' to supplement having only retrieved the 'id' and 'image' from the database record.

In CC6011, the entire record is retrieved, including the name, and the call to getManufacturer() for the missed name is dropped.

Dirty Butter · March 17, 2016

THANK YOU!! I appreciate your help, as always.

Dirty Butter · June 24, 2016

I'm revisiting this, attempting to get Sort to work - mine never did. I'm on 6.0.12.

I'm approaching 5000 product listings now, and being able to sort the count is getting even more important, as I have almost 430 manufacturers listed.

bsmither · June 25, 2016

I say we start over with a fresh products.manufacturers.inc.php, and apply these edits appropriately:

Near line 83, find:
if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers', false, false, 'name', $per_page, $page)) !== false) {

Replace with:
if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers',"*, (SELECT COUNT('product_id') FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_inventory WHERE manufacturer = id) AS manu_prod_count", false, 'name', $per_page, $page)) !== false) {

With a fresh template products.manufacturers.php, apply these edits:

Near line 21, find:
<td>{$LANG.form.action}</td>

Replace with:
<td>#Prods</td><td>{$LANG.form.action}</td>

Near line 28, find:
<td>

Replace with:
<td align="right">{$manufacturer.manu_prod_count}</td><td>

Test.

Now for the sorting. Getting back to the products.manufacturers.inc.php, make these edits:

The edited statement at line 83:
if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers',"*, (SELECT COUNT('product_id') FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_inventory WHERE manufacturer = id) AS manu_prod_count", false, 'name', $per_page, $page)) !== false) {

Replace with:
if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers',"*, (SELECT COUNT('product_id') FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_inventory WHERE manufacturer = id) AS manu_prod_count", false, $sort_order_string, $per_page, $page)) !== false) {

Near line 76, find:
} else {

Add AFTER:
/* NEW */
	// Sorting
	$current_page = currentPage(array('sort'));
	$sort_order_string = 'name ASC';
	$sort_wanted = $_GET['sort'];
	$sortable_columns = array('name', 'manu_prod_count');
	if (!empty($sort_wanted)) {
		$sort_order_string = (in_array(key($sort_wanted), $sortable_columns) ? key($sort_wanted) : 'name') . ' ' . ((current($sort_wanted) == "DESC") ? "DESC" : "ASC" );
	}
	$thead_sort = array (
		'name'   => $GLOBALS['db']->column_sort('name', $lang['catalogue']['manufacturer'], 'sort', $current_page, $_GET['sort']),
		'no_prods'   => $GLOBALS['db']->column_sort('manu_prod_count', "#Prods", 'sort', $current_page, $_GET['sort']),
		'action'   => $lang['form']['action'],
	);
	$GLOBALS['smarty']->assign('THEAD', $thead_sort);
/* /NEW */

Getting back to the template products.manufacturers,php:

Find what is now:
	  <thead>
		<tr>
		  <td width="250">{$LANG.catalogue.manufacturer}</td>
		  <td>#Prods</td><td>{$LANG.form.action}</td>
		</tr>
	  </thead>

Replace with:
		<thead>
			<tr>
				<th nowrap="nowrap" width="250">{$THEAD.name}</th>
				<th nowrap="nowrap">{$THEAD.no_prods}</th>
				<th nowrap="nowrap">{$THEAD.action}</th>
			</tr>
		</thead>

Dirty Butter · June 25, 2016

PERFECT!! I figured I'd made a mess of it, as always. That helped me a LOT! What do you think: should I delete all the old stuff and just keep this part that was clearer to me, and perhaps others??

bsmither · June 25, 2016

I would say no, but its also my opinion that, as the OP, it is up to you.

Dirty Butter · June 25, 2016

Well, I compromised - edited the first post and provided a jump to the new discussion. (I did take all that wrong code I listed yesterday out of it.)

[Resolved] Would like stock per Manufacturers Data

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation