How to Create Custom Reports Based on Statistics Module in Prestashop

In this article we will explain how to create a statistics module (the module is avaliable at the end of the article).

Such types of modules are available under the tab Bасkend-> Stаts-> Stаts:

This page is generated by the controller “AdminStatsController”, which is inherited from the controller “AdminStatsTabController”. The latter uses the method “protected function getMоdules()” to send the following request to determine which modules are related to the statistics:

SELECT h.`name` AS hook, m.`name`
FROM `ps_module` m
LEFT JOIN `ps_hook_module` hm ON hm.`id_module` = m.`id_module`
LEFT JOIN `ps_hook` h ON hm.`id_hook` = h.`id_hook`
WHERE h.`name` LIKE 'displayAdminStatsModules' 
   AND m.`active` = 1
ROUP BY hm.id_module
ORDER BY hm.`position`

SELECT h.`name` AS hook, m.`name`

FROM `ps_module` m

LEFT JOIN `ps_hook_module` hm ON hm.`id_module` = m.`id_module`

LEFT JOIN `ps_hook` h ON hm.`id_hook` = h.`id_hook`

WHERE h.`name` LIKE 'displayAdminStatsModules'

AND m.`active` = 1

ROUP BY hm.id_module

ORDER BY hm.`position`

Once one of our customers asked to generate the following type of report:

is it possible to make a report in prestashop that shows how many new products are created by category and by employee (they log on with the own login).

That is why to help him we created this module.

There are 2 methods (for standard modules) to implement this type of modules:

Inherit from the class Module
Inherit from the class ModuleGrid

In the second way you should implement the abstract method getData() where to implement the logic to get entries for the statistics. Basically, the second method presupposes that there should be a specific list, while the first method allows us output data just the way we want.

The module with the class MоduleGrid is similar to the admin controller, that with the help of the method ‘public function engine( $params)’ generates HTML with results in the form of a table.

Here is the example of the code of one of our statistics modules:

Partner With Us Let's discuss how to grow your business. Get a Free Quote.

Talk to Andrey

<?php

if (!defined('_PS_VERSION_'))
    exit;

class Belvg_StatsCatalog extends ModuleGrid
{
    private $html;
    private $query;
    private $columns;
    private $default_sort_column;
    private $default_sort_direction;
    private $empty_message;
    private $paging_message;

    public function __construct()
    {
        $this->name = 'belvg_statscatalog';
        $this->tab = 'analytics_stats';
        $this->version = '1.6.1';
        $this->author = 'BelVG';
        $this->need_instance = 0;

        parent::__construct();

        $this->default_sort_column = 'es.`date_add`';
        $this->default_sort_direction = 'DESC';
        $this->empty_message = $this->l('There is npt any records');
        $this->paging_message = sprintf($this->l('Displaying %1$s of %2$s'), '{0} - {1}', '{2}');

        //Columns, that will be displayed in the table
        $this->columns = array(
            array(
                'id' => 'lastname',
                'header' => $this->l('Last Name'),
                'dataIndex' => 'lastname',
                'align' => 'center'
            ),
            array(
                'id' => 'firstname',
                'header' => $this->l('First Name'),
                'dataIndex' => 'firstname',
                'align' => 'center'
            ),
            array(
                'id' => 'email',
                'header' => $this->l('Email'),
                'dataIndex' => 'email',
                'align' => 'center'
            ),
            array(
                'id' => 'totalProducts',
                'header' => $this->l('Products'),
                'dataIndex' => 'totalProducts',
                'align' => 'center'
            ),
            array(
                'id' => 'date_add',
                'header' => $this->l('Last update'),
                'dataIndex' => 'date_add',
                'align' => 'center'
            ),
        );

        $this->displayName = $this->l('Employee report');
        $this->description = $this->l('Make a report in prestashop that shows how many new products are created by category and by employee.');
        $this->ps_versions_compliancy = array('min' => '1.6', 'max' => _PS_VERSION_);

        //variables are required to apply non-standard filters  
        if(!isset($_SESSION)) {
            session_start();
        }
        if (Tools::isSubmit('estat_id_category')) {
            $_SESSION['estat_id_category'] = Tools::getValue('estat_id_category');
        }
        if (Tools::isSubmit('estat_id_employee')) {
            $_SESSION['estat_id_employee'] = Tools::getValue('estat_id_employee');
        }
    }

    public function install()
    {
        return (parent::install() &&
            $this->registerHook('AdminStatsModules') &&
            $this->registerHook('actionObjectProductAddAfter') &&
            $this->installDB());
    }

    protected function installDB()
    {
        $return = TRUE;

        //database table that contains all data for statistics 
        try {
            $return &= Db::getInstance()->execute('
CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'belvg_employees_stat` (
`id_belvg_empoyees_stat` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`id_employee` int(10) NOT NULL,
`id_product` int(10) NOT NULL,
                `date_add` datetime NOT NULL,
                `date_upd` datetime NOT NULL,
PRIMARY KEY (`id_belvg_empoyees_stat`),
                KEY `id_employee` (`id_employee`)
) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=utf8 ;');
        } catch(Exception $e) {

        }

        return $return;
    }

    private function uninstallDB()
    {
        $return = TRUE;

        /*try {
            $return &= Db::getInstance()->execute('
                DROP TABLE `belvg_employees_stat`'
            );
        } catch(Exception $e) {

        }*/

        return $return;
    }

    //this hook outputs data into our new table
    public function hookActionObjectProductAddAfter($params)
    {
        $data = array(
            'id_product' => (int) $params['object']->id,
            'id_employee' => (int) $this->context->cookie->id_employee,
            'date_add' => date('Y-m-d H:i:s'),
            'date_upd' => date('Y-m-d H:i:s'),
        );

        Db::getInstance()->insert('belvg_employees_stat', $data, FALSE, TRUE, Db::REPLACE);
    }

    //the method obtains data of the selected filter
    protected function getEstatCategory()
    {
        if (isset($_SESSION['estat_id_category'])) {
            return $_SESSION['estat_id_category'];
        }

        return FALSE;
    }

    //the method obtains data of the selected filter
    protected function getEstatEmployee()
    {
        if (isset($_SESSION['estat_id_employee'])) {
            return $_SESSION['estat_id_employee'];
        }

        return FALSE;
    }

    //Implementation of the abstract method. Required to obtain data for the table.
    public function getData()
    {
        $_join = '';
        $_where = '';
        $date_between = $this->getDate();
        $id_category = $this->getEstatCategory();
        if ($id_category) {
            //$_where .= 'cp.`id_category` = ' . (int)$id_category;
            $_where .= 'FIND_IN_SET (' . (int)$id_category . ', c.category_path) ';
            $_join .= 'LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
        LEFT JOIN `'._DB_PREFIX_.'category` c ON (c.`id_category` = cp.`id_category`)
        LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (cl.`id_category` = cp.`id_category` AND cl.`id_lang` = '.(int)$this->context->language->id.')';
        }

        $id_employee = $this->getEstatEmployee();
        if ($id_employee) {
            //$_join .= 'JOIN `'._DB_PREFIX_.'belvg_employees_stat` es ON (es.`id_employee` = ' . (int)$id_employee . ')';
            $_where .= 'es.`id_employee` = ' . (int)$id_employee;
        }

        $this->query = '
SELECT e.`firstname`, e.`lastname`, e.`email`, es.`date_add`, COUNT(p.`id_product`) as totalProducts
FROM `'._DB_PREFIX_.'employee` e
        LEFT JOIN `'._DB_PREFIX_.'belvg_employees_stat` es ON (es.`id_employee` = e.`id_employee`)
        LEFT JOIN `'._DB_PREFIX_.'product` p ON (es.`id_product` = p.`id_product`)
'.$_join.'
WHERE es.`date_add` BETWEEN '.$date_between.' ' . (!empty($_where) ? ' AND ' . $_where : '') . '
GROUP BY e.`id_employee`';

        if (($this->_start === 0 || Validate::IsUnsignedInt($this->_start)) && Validate::IsUnsignedInt($this->_limit))
            $this->query .= ' LIMIT '.(int)$this->_start.', '.(int)$this->_limit;

        $this->_values = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($this->query);
        $this->_totalCount = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('SELECT FOUND_ROWS()');
    }

    //The hook is responsible for data output
    public function hookAdminStatsModules($params)
    {

        $this->context->smarty->assign(array(
            'categories' => Category::getCategories($this->context->language->id, TRUE, FALSE),
            'id_category' => (int)$this->getEstatCategory(),
            'id_employee' => (int)$this->getEstatEmployee(),
            'employees' => Employee::getEmployees(),
            'displayName' => $this->displayName,
        ));

        $engine_params = array(
            'id' => 'id_customer',
            'title' => $this->displayName,
            'columns' => $this->columns,
            'defaultSortColumn' => $this->default_sort_column,
            'defaultSortDirection' => $this->default_sort_direction,
            'emptyMessage' => $this->empty_message,
            'pagingMessage' => $this->paging_message
        );

        if (Tools::getValue('export'))
            $this->csvExport($engine_params);

        //pay attention, that the method engine () is used, the one we described in the article
        $this->html = '
<div class="panel-heading">
'.$this->displayName.'
</div>
        '.$this->display(__FILE__, 'AdminStatsModules.tpl').'
'.$this->engine($engine_params).'
<a class="btn btn-default export-csv" href="'.Tools::safeOutput($_SERVER['REQUEST_URI'].'&export=').'1">
<i class="icon-cloud-upload"></i> '.$this->l('CSV Export').'
</a>';

        return $this->html;
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

<?php

if (!defined('_PS_VERSION_'))

exit;

class Belvg_StatsCatalog extends ModuleGrid

{

private $html;

private $query;

private $columns;

private $default_sort_column;

private $default_sort_direction;

private $empty_message;

private $paging_message;

public function __construct()

{

$this->name = 'belvg_statscatalog';

$this->tab = 'analytics_stats';

$this->version = '1.6.1';

$this->author = 'BelVG';

$this->need_instance = 0;

parent::__construct();

$this->default_sort_column = 'es.`date_add`';

$this->default_sort_direction = 'DESC';

$this->empty_message = $this->l('There is npt any records');

$this->paging_message = sprintf($this->l('Displaying %1$s of %2$s'), '{0} - {1}', '{2}');

//Columns, that will be displayed in the table

$this->columns = array(

array(

'id' => 'lastname',

'header' => $this->l('Last Name'),

'dataIndex' => 'lastname',

'align' => 'center'

array(

'id' => 'firstname',

'header' => $this->l('First Name'),

'dataIndex' => 'firstname',

'align' => 'center'

array(

'id' => 'email',

'header' => $this->l('Email'),

'dataIndex' => 'email',

'align' => 'center'

array(

'id' => 'totalProducts',

'header' => $this->l('Products'),

'dataIndex' => 'totalProducts',

'align' => 'center'

array(

'id' => 'date_add',

'header' => $this->l('Last update'),

'dataIndex' => 'date_add',

'align' => 'center'

);

$this->displayName = $this->l('Employee report');

$this->description = $this->l('Make a report in prestashop that shows how many new products are created by category and by employee.');

$this->ps_versions_compliancy = array('min' => '1.6', 'max' => _PS_VERSION_);

//variables are required to apply non-standard filters

if(!isset($_SESSION)) {

session_start();

}

if (Tools::isSubmit('estat_id_category')) {

$_SESSION['estat_id_category'] = Tools::getValue('estat_id_category');

}

if (Tools::isSubmit('estat_id_employee')) {

$_SESSION['estat_id_employee'] = Tools::getValue('estat_id_employee');

}

public function install()

{

return (parent::install() &&

$this->registerHook('AdminStatsModules') &&

$this->registerHook('actionObjectProductAddAfter') &&

$this->installDB());

}

protected function installDB()

{

$return = TRUE;

//database table that contains all data for statistics

try {

$return &= Db::getInstance()->execute('

CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'belvg_employees_stat` (

`id_belvg_empoyees_stat` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,

`id_employee` int(10) NOT NULL,

`id_product` int(10) NOT NULL,

`date_add` datetime NOT NULL,

`date_upd` datetime NOT NULL,

PRIMARY KEY (`id_belvg_empoyees_stat`),

KEY `id_employee` (`id_employee`)

) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=utf8 ;');

} catch(Exception $e) {

}

return $return;

}

private function uninstallDB()

{

$return = TRUE;

/*try {

$return &= Db::getInstance()->execute('

DROP TABLE `belvg_employees_stat`'

);

} catch(Exception $e) {

}*/

return $return;

}

//this hook outputs data into our new table

public function hookActionObjectProductAddAfter($params)

{

$data = array(

'id_product' => (int) $params['object']->id,

'id_employee' => (int) $this->context->cookie->id_employee,

'date_add' => date('Y-m-d H:i:s'),

'date_upd' => date('Y-m-d H:i:s'),

);

Db::getInstance()->insert('belvg_employees_stat', $data, FALSE, TRUE, Db::REPLACE);

}

//the method obtains data of the selected filter

protected function getEstatCategory()

{

if (isset($_SESSION['estat_id_category'])) {

return $_SESSION['estat_id_category'];

}

return FALSE;

}

//the method obtains data of the selected filter

protected function getEstatEmployee()

{

if (isset($_SESSION['estat_id_employee'])) {

return $_SESSION['estat_id_employee'];

}

return FALSE;

}

//Implementation of the abstract method. Required to obtain data for the table.

public function getData()

{

$_join = '';

$_where = '';

$date_between = $this->getDate();

$id_category = $this->getEstatCategory();

if ($id_category) {

//$_where .= 'cp.`id_category` = ' . (int)$id_category;

$_where .= 'FIND_IN_SET (' . (int)$id_category . ', c.category_path) ';

$_join .= 'LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)

LEFT JOIN `'._DB_PREFIX_.'category` c ON (c.`id_category` = cp.`id_category`)

LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (cl.`id_category` = cp.`id_category` AND cl.`id_lang` = '.(int)$this->context->language->id.')';

}

$id_employee = $this->getEstatEmployee();

if ($id_employee) {

//$_join .= 'JOIN `'._DB_PREFIX_.'belvg_employees_stat` es ON (es.`id_employee` = ' . (int)$id_employee . ')';

$_where .= 'es.`id_employee` = ' . (int)$id_employee;

}

$this->query = '

SELECT e.`firstname`, e.`lastname`, e.`email`, es.`date_add`, COUNT(p.`id_product`) as totalProducts

FROM `'._DB_PREFIX_.'employee` e

LEFT JOIN `'._DB_PREFIX_.'belvg_employees_stat` es ON (es.`id_employee` = e.`id_employee`)

LEFT JOIN `'._DB_PREFIX_.'product` p ON (es.`id_product` = p.`id_product`)

'.$_join.'

WHERE es.`date_add` BETWEEN '.$date_between.' ' . (!empty($_where) ? ' AND ' . $_where : '') . '

GROUP BY e.`id_employee`';

if (($this->_start === 0 || Validate::IsUnsignedInt($this->_start)) && Validate::IsUnsignedInt($this->_limit))

$this->query .= ' LIMIT '.(int)$this->_start.', '.(int)$this->_limit;

$this->_values = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($this->query);

$this->_totalCount = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('SELECT FOUND_ROWS()');

}

//The hook is responsible for data output

public function hookAdminStatsModules($params)

{

$this->context->smarty->assign(array(

'categories' => Category::getCategories($this->context->language->id, TRUE, FALSE),

'id_category' => (int)$this->getEstatCategory(),

'id_employee' => (int)$this->getEstatEmployee(),

'employees' => Employee::getEmployees(),

'displayName' => $this->displayName,

));

$engine_params = array(

'id' => 'id_customer',

'title' => $this->displayName,

'columns' => $this->columns,

'defaultSortColumn' => $this->default_sort_column,

'defaultSortDirection' => $this->default_sort_direction,

'emptyMessage' => $this->empty_message,

'pagingMessage' => $this->paging_message

);

if (Tools::getValue('export'))

$this->csvExport($engine_params);

//pay attention, that the method engine () is used, the one we described in the article

$this->html = '

'.$this->displayName.'

</div>

'.$this->display(__FILE__, 'AdminStatsModules.tpl').'

'.$this->engine($engine_params).'

<i class="icon-cloud-upload"></i> '.$this->l('CSV Export').'

</a>';

return $this->html;

}

Download the module

PrestaShop Templates

Take your online store to the next level with BelVG PrestaShop Templates

Visit the page

Partner With Us Looking for a partner to grow your business? We are the right company to bring your webstore to success. Talk to Andrey

Tags:

2 Comments

Alex Simonchik says:

January 16, 2019 at 15:14

Hi, Mike!
Glad that you liked my tutorial. As you can see, it dates back to year 2014, so I or some other author from BelVG will soon write the updated version of this article. Stay tuned!
mike000 says:

January 16, 2019 at 14:27

Hi dude, nice tutorial, but you should relaod the files, use a github or a girlab or something

keers!

How to Create Custom Reports Based on Statistics Module in Prestashop

PrestaShop Templates

Table of contents:

Download Free Site
Audit Template

2 Comments

Post a new comment Cancel

How to Create Custom Reports Based on Statistics Module in Prestashop

Get interesting stuff and updates to your email inbox

PrestaShop Templates

Table of contents:

Download Free Site Audit Template

2 Comments

Post a new comment Cancel

Download Free Site
Audit Template