advanced stock managment SOLUTION: Advanced Stock Management with correct quantities for all stores.

[Koen Amant]

I found a solution to display the correct quantities for your products in stock on the Administrator Product List page.

Prestashop takes the quantity from the table ps_stock_available but here is only the stock for the default shop listed. So I modified the controller to get the stock from the table ps_stock, here you get the correct stock by each warehouse.

It's working but there are some things I still need to change..

1. You can't set the product reference on the page because the query gives a ambiguous field for the ps_product.reference with the ps_warehouse.reference. And I can't find where the SELECT query is created... :-(
2. I can't do a search on 'Manufacturer', so I disables the search until I found it...
3. Search on baseprice does not work with decimals...

 

If someone can help me with these things, please post it.

 

 

Copy the file AdminProductsController.php to the folder override/controllers/admin. Search for the text below and copy/paste the code between "// HERE STARTS MY CODE" till "// HERE ENDS MY CODE" into the file. 

$join_category = false;
        if (Validate::isLoadedObject($this->_category) && empty($this->_filter)) {
            $join_category = true;
        }

 

// HERE STARTS MY CODE 

$alias = 'sa';
        $alias_image = 'image_shop';

        $id_shop = Shop::isFeatureActive() && Shop::getContext() == Shop::CONTEXT_SHOP? (int)$this->context->shop->id : 'a.id_shop_default';
                              
        $query = new DbQuery();
        $query->select('id_warehouse');
        $query->from('warehouse_shop');
        $query->where('id_shop = '.(int)$id_shop);
        $id_warehouse = (Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($query));    
        
        if (is_numeric($id_shop)) {
            $this->_join .= 'LEFT JOIN `'._DB_PREFIX_.'stock` sav ON (sav.`id_product` = a.`id_product` AND sav.id_warehouse='.$id_warehouse.')';
        } else {
            $this->_join .= 'LEFT JOIN `'._DB_PREFIX_.'stock` sav ON (sav.`id_product` = a.`id_product`)';    
        }
    
        $this->_join .= ' JOIN `'._DB_PREFIX_.'product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = '.$id_shop.')
                JOIN `'._DB_PREFIX_.'manufacturer` ma ON (a.id_manufacturer = ma.id_manufacturer)
                LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON ('.$alias.'.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = '.$id_shop.')
                LEFT JOIN `'._DB_PREFIX_.'shop` shop ON (shop.id_shop = '.$id_shop.')
                LEFT JOIN `'._DB_PREFIX_.'image_shop` image_shop ON (image_shop.`id_product` = a.`id_product` AND image_shop.`cover` = 1 AND image_shop.id_shop = '.$id_shop.')
                LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_image` = image_shop.`id_image`)
                LEFT JOIN `'._DB_PREFIX_.'product_download` pd ON (pd.`id_product` = a.`id_product` AND pd.`active` = 1)';

        $this->_select .= 'shop.`name` AS `shopname`, a.`id_shop_default`, ';
        $this->_select .= $alias_image.'.`id_image` AS `id_image`, cl.`name` AS `name_category`, ma.name AS manufacturer, '.$alias.'.`price`, 0 AS `price_final`, a.`is_virtual`, pd.`nb_downloadable`,
                IF(SUM(sav.`physical_quantity`) IS NULL,0, SUM(sav.`physical_quantity`)) AS `sav_quantity`, '.$alias.'.`active`, IF((IF(SUM(sav.`physical_quantity`) IS NULL,0, SUM(sav.`physical_quantity`)))<=0, 1, 0) AS `badge_danger`';
        
        if ($join_category) {
            $this->_join .= ' INNER JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = a.`id_product` AND cp.`id_category` = '.(int)$this->_category->id.') ';
            $this->_select .= ' , cp.`position`, ';
        }
        $this->_use_found_rows = false;
        $this->_group = 'GROUP BY a.id_product';

        $this->fields_list = array();
        $this->fields_list['id_product'] = array(
            'title' => $this->l('ID'),
            'align' => 'center',
            'class' => 'fixed-width-xs',
            'type' => 'int'
        );
        $this->fields_list['image'] = array(
            'title' => $this->l('Image'),
            'align' => 'center',
            'image' => 'p',
            'orderby' => false,
            'filter' => false,
            'search' => false
        );
        $this->fields_list['name'] = array(
            'title' => $this->l('Name'),
            'filter_key' => 'b!name'
        );
        
        $this->fields_list['manufacturer'] = array(
            'title' => $this->l('Manufacturer'),
            'align' => 'left',
            'search' => false
        );
        /*

// DO NOT USE THIS PART
        $this->fields_list['reference'] = array(
            'title' => $this->l('Reference'),
            'align' => 'left',
        );
// END OF PART        
        if (Shop::isFeatureActive() && Shop::getContext() != Shop::CONTEXT_SHOP) {
            $this->fields_list['shopname'] = array(
                'title' => $this->l('Default shop'),
                'filter_key' => 'shop!name',
            );
        } else {
            $this->fields_list['name_category'] = array(
                'title' => $this->l('Category'),
                'filter_key' => 'cl!name',
            );
        }
        */
        $this->fields_list['price'] = array(
            'title' => $this->l('Base price'),
            'type' => 'price',
            'align' => 'text-right',
            'filter_key' => 'a!price'
        );
        $this->fields_list['price_final'] = array(
            'title' => $this->l('Final price'),
            'type' => 'price',
            'align' => 'text-right',
            'havingFilter' => true,
            'orderby' => false,
            'search' => false
        );

        if (Configuration::get('PS_STOCK_MANAGEMENT')) {
            $this->fields_list['sav_quantity'] = array(
                'title' => $this->l('Quantity'),
                'type' => 'int',
                'align' => 'text-right',
                'filter_key' => 'sav!quantity',
                'orderby' => true,
                'badge_danger' => true,
                //'hint' => $this->l('This is the quantity available in the current shop/group.'),
            );
        }

        $this->fields_list['active'] = array(
            'title' => $this->l('Status'),
            'active' => 'status',
            'filter_key' => $alias.'!active',
            'align' => 'text-center',
            'type' => 'bool',
            'class' => 'fixed-width-sm',
            'orderby' => false
        );

        if ($join_category && (int)$this->id_current_category) {
            $this->fields_list['position'] = array(
                'title' => $this->l('Position'),
                'filter_key' => 'cp!position',
                'align' => 'center',
                'position' => 'position'
            );
        }
    }

// HERE ENDS MY CODE 

 

    public static function getQuantities($echo, $tr)
    {
        if ((int)$tr['is_virtual'] == 1 && $tr['nb_downloadable'] == 0) {
            return '∞';
        } else {
            return $echo;
        }
    }

Edited March 4, 2018 by Koen Amant
Changed to code reading. (see edit history)

[Koen Amant]

Solved the reference column.

If you want the Reference column back in your backoffice just add the filter_key.

$this->fields_list['reference'] = array(
  'title' => $this->l('Reference'),
  'align' => 'left',
  'filter_key' => 'a!reference',
);

1 down still 2 to Go...