Connection to external database

[AntonioAT]

Hi guys,

 

I'm trying to connect 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');
	}

	/**
	 * Assign template vars related to page content
	 * @see FrontController::initContent()
	 */
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, such as GAZ_PREFIX, etc.

 

The template, residui.tpl:

{capture name=path}
	<a href="{$link->getPageLink('my-account', true)|escape:'html':'UTF-8'}">
		{l s='My account'}
	</a>
	<span class="navigation-pipe">{$navigationPipe}</span>
	<span class="navigation_page">{l s='Order history'}</span>
{/capture}
{include file="$tpl_dir./errors.tpl"}
<h1 class="page-heading bottom-indent">{l s='Order history'}</h1>
<p class="info-title">{l s='Here are the orders you\'ve placed since your account was created.'}</p>
{if $slowValidation}
	<p class="alert alert-warning">{l s='If you have just placed an order, it may take a few minutes for it to be validated. Please refresh this page if your order is missing.'}</p>
{/if}
<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>
					<th data-hide="phone" class="item">{l s='Total price'}</th>
					<th data-sort-ignore="true" data-hide="phone,tablet" class="item">{l s='Payment'}</th>
					<th class="item">{l s='Status'}</th>
					<th data-sort-ignore="true" data-hide="phone,tablet" class="last_item"> </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">
							<a class="color-myaccount" href="javascript:showOrder(1, {$residuo.id_order|intval}, '{$link->getPageLink('order-detail', true)|escape:'html':'UTF-8'}');">
								{$residuo['numdoc']}
							</a>
						</td>
						<td class="history_date bold">
						{$residuo['datemi']}
						</td>
						
					</tr>
				{/foreach}
			</tbody>
		</table>
		<div id="block-order-detail" class="unvisible"> </div>

</div>
<ul class="footer_links clearfix">
	<li>
		<a class="btn btn-default button button-small" href="{$link->getPageLink('my-account', true)|escape:'html':'UTF-8'}">
			<span>
				<i class="fa fa-chevron-left"></i> {l s='Back to Your Account'}
			</span>
		</a>
	</li>
	<li>
		<a class="btn btn-default button button-small" href="{$base_dir}">
			<span><i class="fa fa-home"></i> {l s='Home'}</span>
		</a>
	</li>
</ul>

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...

 

 

Edited November 5, 2015 by AntonioAT (see edit history)

[AntonioAT]

Ok, I solved the problem.

 

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!

[Mikelito]

Ciao Antonio,

 

io avrei bisogno di collegare prestashop al gestionale del magazzino. Mi serve che prestashop mi legga in automatico prezzo e quantità dal database del gestionale. E' possibile ?