[SOLVED] Edit Mail Alert module B.O. page

[DARKF3D3]

I'm trying to show list of request on mail alerts configuration page.

 

I have this working query that i tested directly on the DB:

SELECT id_product, COUNT(*) AS "VOLTE"
FROM `ps_mailalert_customer_oos`
GROUP BY id_product
ORDER BY COUNT(*) DESC

 

Now i would like to add it to the module configuration page the problem is that i don't find the right file.

In mailalerts\views\templates\admin folder there's only these files: "form.tpl" and "index.php".

 

Someone could help me to add this query to the module?

 

 

Thanks

Edited September 28, 2014 by DARKF3D3 (see edit history)

[vekia]

you mean that you want to display results of this query?

[DARKF3D3]

Yes, I'm trying to show the result of that query at the bottom of the Mail Alerts configuration page.

[vekia]

to module php file (mailalerts.php) add this function:
 

    public function displayMyQueryResults(){
        $return='<tr><td>'.$this->l('Product').'</td><td>'.$this->l('VOLTE').'</td></tr>';
        foreach (Db::getInstance()->executeS('SELECT id_product, COUNT(*) AS "VOLTE" FROM `ps_mailalert_customer_oos` GROUP BY id_product ORDER BY COUNT(*) DESC') AS $k=>$v){
            $return.='<tr><td>'.$v['id_product'].'</td><td>'.$v['VOLTE'].'</td></tr>';
        }
        return '<table style="width:500px;">'.$return."</table>";
    }

and in function:

public function renderForm()

modify return, instead of original use this one:

return $helper->generateForm(array($fields_form_1, $fields_form_2)).$this->displayMyQueryResults();

[DARKF3D3]

Thanks vekia, it works!
 
I have another question, I'm trying to show the product name instead of the product id, but the query doesn't show the products name.
 
This is my query:

SELECT name, COUNT(*) AS "VOLTE"
FROM `ps_product_lang`
INNER JOIN `ps_mailalert_customer_oos`
ON ps_mailalert_customer_oos.id_product = ps_product_lang.id_product
WHERE ps_product_lang.id_lang = 5
GROUP BY name
ORDER BY COUNT(*) DESC

EDIT:

It's strange because executing the query directly from phpmyadmin it show the products name.

 

This is the customized function:

	public function displayMyQueryResults(){
        $return='<tr style="font-weight:600;"><td>'.$this->l('PRODOTTO').'</td><td style="width:20px"></td><td>'.$this->l('VOLTE').'</td></tr>';
        foreach (Db::getInstance()->executeS('SELECT name, COUNT(*) AS "VOLTE" FROM `ps_product_lang` INNER JOIN `ps_mailalert_customer_oos` ON ps_mailalert_customer_oos.id_product = ps_product_lang.id_product WHERE ps_product_lang.id_lang = 5 GROUP BY name ORDER BY COUNT(*) DESC') AS $k=>$v){
            $return.='<tr><td>'.$v['id_product'].'</td><td></td><td>'.$v['VOLTE'].'</td></tr>';
        }
        return '<div class="panel"><div class="panel-heading"><i class="icon-user"></i> Richieste di notifica per i prodotti esauriti</div><table>'.$return."</table></div>";
    }

Edited September 28, 2014 by DARKF3D3 (see edit history)

[DARKF3D3]

Stupid mistake, i forgot to change $v['id_product'] to $v['name'].

[vekia]

that's right instead of $v['id_product'] is necessary to use $v['name'] so final code looks like:
 

	public function displayMyQueryResults(){
        $return='<tr style="font-weight:600;"><td>'.$this->l('PRODOTTO').'</td><td style="width:20px"></td><td>'.$this->l('VOLTE').'</td></tr>';
        foreach (Db::getInstance()->executeS('SELECT name, COUNT(*) AS "VOLTE" FROM `ps_product_lang` INNER JOIN `ps_mailalert_customer_oos` ON ps_mailalert_customer_oos.id_product = ps_product_lang.id_product WHERE ps_product_lang.id_lang = 5 GROUP BY name ORDER BY COUNT(*) DESC') AS $k=>$v){
            $return.='<tr><td>'.$v['name'].'</td><td></td><td>'.$v['VOLTE'].'</td></tr>';
        }
        return '<div class="panel"><div class="panel-heading"><i class="icon-user"></i> Richieste di notifica per i prodotti esauriti</div><table>'.$return."</table></div>";
    }

[DARKF3D3]

EDIT

Edited December 29, 2014 by DARKF3D3 (see edit history)

[DARKF3D3]

that's right instead of $v['id_product'] is necessary to use $v['name'] so final code looks like:

 

Hi vekia, do you know how can I show the query result in a new tab? (in this way i don't have to open every time the configuration page of the module).

 

Under "ADMINISTRATION >> Tabs" i added a new tab with this settings:

class: displayMyQueryResults (the name of the function i used to show the products list)

module: mailalerts

 

But i think I making it in the wrong way because it say: "controller not found".