Executing query from Prestashop to external database

Question

I'm trying to connect from Prestashop to external database (ERP) to get the order history from it.

I've cloned the History controller and named it "residui".

I've created ResiduiController.php that contains:

class ResiduiControllerCore extends FrontController {
public $auth = true;
public $php_self = 'residui';
public $authRedirection = 'residui';
public $ssl = true;

public function setMedia() {
    parent::setMedia();
    $this->addCSS(array(
        _THEME_CSS_DIR_.'residui.css',
    ));
    $this->addJS(array(
        _THEME_JS_DIR_.'history.js',
        _THEME_JS_DIR_.'tools.js' // retro compat themes 1.5
    ));
    $this->addJqueryPlugin('footable');
    $this->addJqueryPlugin('footable-sort');
    $this->addJqueryPlugin('scrollTo'); } 

public function initContent() {
    parent::initContent();

    $residui = Order::getCustomerResidui($this->context->customer->id);

    $this->context->smarty->assign(array(
        'residui' => $residui
    ));

    $this->setTemplate(_PS_THEME_DIR_.'residui.tpl');   }   }

I've inserted the class getCustomerResidui in Order.php:

public static function getCustomerResidui($id_customer, $showHiddenStatus = false, Context $context = null) {
    if (!$context)
        $context = Context::getContext();
    $evadi = 'S';
    $stato = 'GENERATO';
    $resi = Db::getFromGazie()->executeS("
    SELECT *
    FROM "._GAZ_PREFIX_."tesbro
    WHERE id_cli_presta = '".(int)$id_customer."' AND status =  '".$stato."'
    ORDER BY id_tes DESC");
    if (!$resi)
        return array();

    foreach ($resi as $key => $val) {
        $resi2 = Db::getFromGazie()->executeS("
            SELECT *
            FROM "._GAZ_PREFIX_."rigbro
            WHERE id_doc = '".$val['numdoc']."' AND evadi <> '".$evadi."'
            ORDER BY codart DESC LIMIT 1");

        if ($resi2)
            $resi[$key] = array_merge($resi[$key], $resi2[0]);  }
    return $resi;   }   }

I've added the getFromGazie instance in DB.php and all connection parameters to the external DB in settings.inc.php, such as GAZ_PREFIX, etc.

DB.php:

public static function getFromGazie($master = true) {
    static $id = 0;

    // This MUST not be declared with the class members because some defines (like _DB_SERVER_) may not exist yet (the constructor can be called directly with params)
    if (!self::$_servers)
        self::$_servers = array(
            array('gaz_server' => _GAZ_SERVER_, 'gaz_user' => _GAZ_USER_, 'gaz_password' => _GAZ_PASSWD_, 'gaz_database' => _GAZ_NAME_), /* MySQL Master server */
        );

    Db::loadSlaveServers();

    $total_servers = count(self::$_servers);
    if ($master || $total_servers == 1)
        $id_server = 0;
    else {
        $id++;
        $id_server = ($total_servers > 2 && ($id % $total_servers) != 0) ? $id % $total_servers : 1;    }

    if (!isset(self::$instance[$id_server]))    {
        $class = Db::getClass();
        self::$instance[$id_server] = new $class(
            self::$_servers[$id_server]['gaz_server'],
            self::$_servers[$id_server]['gaz_user'],
            self::$_servers[$id_server]['gaz_password'],
            self::$_servers[$id_server]['gaz_database']);   }

    return self::$instance[$id_server]; }

The template, residui.tpl:

<div class="block-center" id="block-history">
    <table id="order-list" class="table table-bordered footab">
        <thead>
            <tr>
                <th class="first_item" data-sort-ignore="true">{l s='Order reference'}</th>
                <th class="item">{l s='Date'}</th>

            </tr>
        </thead>
        <tbody>
            {foreach from=$residui item=residuo name=myLoop}
                <tr class="{if $smarty.foreach.myLoop.first}first_item{elseif $smarty.foreach.myLoop.last}last_item{else}item{/if} {if $smarty.foreach.myLoop.index % 2}alternate_item{/if}">
                    <td class="history_link bold">
                        <p class="color-myaccount">
                            {$residuo['numdoc']}
                        </p>
                    </td>
                    <td class="history_date bold">
                    {$residuo['datemi']}
                    </td>

                </tr>
            {/foreach}
        </tbody>
    </table>
    <div id="block-order-detail" class="unvisible">&nbsp;</div>

The problem is that I don't get any line displayed (I also tested the query manually in PhpMyAdmin).

I tried for hours but I can't see the mistake (and I'm sure I did one or more).

Can you tell me something? Thanks...

Answer 1

Got it!!!!

First of all thanks to Sergii P that suggests me _PS_MODE_DEV_ that I didn't know...

The problem was that it was always trying to execute the query on the same DB. To solve that, i added _GAZ_NAME_ before _GAZ_PREFIX_, like this:

public static function getCustomerResidui($id_customer, $showHiddenStatus = false, Context $context = null)
{
    if (!$context)
        $context = Context::getContext();
    $evadi = 'S';
    $stato = 'GENERATO';
    $resi = Db::getFromGazie()->executeS("
    SELECT *
    FROM "._GAZ_NAME_."."._GAZ_PREFIX_."tesbro
    WHERE id_cli_presta = '".(int)$id_customer."' AND status =  '".$stato."'
    ORDER BY id_tes DESC");
    if (!$resi)
        return array();

    foreach ($resi as $key => $val)
    {
        $resi2 = Db::getFromGazie()->executeS("
            SELECT *
            FROM "._GAZ_NAME_."."._GAZ_PREFIX_."rigbro
            WHERE id_doc = '".$val['numdoc']."' AND evadi <> '".$evadi."'
            ORDER BY codart DESC LIMIT 1");

        if ($resi2)
            $resi[$key] = array_merge($resi[$key], $resi2[0]);

    }
    return $resi;
}

Et voilà, everything works fine!