Jump to content

Connection to external database


AntonioAT

Recommended Posts

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

 

post-1128598-0-98462600-1446741310_thumb.png

 

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 by AntonioAT (see edit history)
Link to comment
Share on other sites

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!

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...